1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
package common.repository;
import common.model.Contract;
import common.model.Role;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigInteger;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Map;
@Transactional
public interface ContractRepository extends JpaRepository<Contract, Long> {
@Query(value = "select count(*) from contract where ds = ?1", nativeQuery = true)
BigInteger countNumByDs(String ds);
@Query(value = "select count(*) from contract where email = ?1 and platform = ?2", nativeQuery = true)
BigInteger countNumByEmail(String email, String platfrom);
@Query(value = "select * from contract where start_date >= ?1 and start_date <= ?2 and platform = ?3 and status!='del' order by ds desc", nativeQuery = true)
List<Contract> findByDs(String startDate, String endDate, String platfrom);
@Query(value = "select * from contract where start_date >= ?1 and start_date <= ?2 and platform = ?3 and create_account in ?4 and status!='del' order by ds desc", nativeQuery = true)
List<Contract> findByDsAndRoile(String startDate, String endDate, String platfrom, List<Long> userids);
@Query(value = "select * from contract where contract_code = ?1", nativeQuery = true)
Contract findByCode(String contract_code);
@Query(value = "SELECT * from contract where platform = ?1 and type = 'main'", nativeQuery = true)
List<Contract> findByPlatform(String platform);
@Query(value = "SELECT * from contract where platform = ?1 and email = ?2 and type = ?3", nativeQuery = true)
List<Contract> findByPlatformAndEmail(String platform, String email, String type);
@Query(value = "SELECT * from contract where platform = ?1 and email = ?2 and type = ?3 order by ds,end_date desc limit 1", nativeQuery = true)
Contract findByPlatformAndEmailLimit1(String platform, String email, String type);
@Query(value = "SELECT * from contract where platform = ?1 and email = ?2 and type = ?3 and price_level = ?4 order by ds desc limit 1", nativeQuery = true)
Contract findByPlatformAndEmailAndPricelevelLimit1(String platform, String email, String type, Long priceLevel);
@Query(value = "SELECT * from contract where platform = ?1 and email = ?2 and (status = 'wait' or status = 'executing') and type = 'main' order by ds desc limit 1", nativeQuery = true)
Contract findByPlatformAndEmailLimitVaild(String platform, String email);
@Query(value = "SELECT * from contract where start_date = ?1 and status <> 'cancel'", nativeQuery = true)
List<Contract> findByStartDate(String startDate);
@Query(value = "SELECT * from contract where end_date <= ?1 and (status <> 'end' or status <> 'cancel')", nativeQuery = true)
List<Contract> findByEndDate(String endDate);
@Query(value = "select count(*) from contract where my_body_code = ?1", nativeQuery = true)
BigInteger findcountContract(String platform);
@Query(value = "select new map( c.id as id, c.contractCode as contractCode ) from Contract c where c.platform in ?1 and c.contractCode is not null and c.contractCode <> 'null' and c.status <>'del'")
List<Map> contractCodePlatform(List<String> platform);
@Query(value = "select * from contract where platform = ?2 and contract_code = ?1", nativeQuery = true)
Contract findByCodePlatform(String contractCode, String platform);
@Query(value = "select * from contract where start_date >= ?1 and start_date <= ?2 and platform in ?3 and id in ?4 and status!='del' order by ds desc", nativeQuery = true)
List<Contract> findByDsRelation(String startDate, String endDate, List<String> platform, List<String> contractIds);
@Query(value = "select * from contract where start_date >= ?1 and start_date <= ?2 and platform in ?3 and create_account in ?4 and id in ?5 and status!='del' order by ds desc", nativeQuery = true)
List<Contract> findByDsAndRoileRelation(String startDate, String endDate, List<String> platform, List<Long> create_account, List<String> contractIds);
@Query(value = "select new map( c.id as id, c.contractCode as contractCode ) from Contract c where c.platform in ?1 and c.contractCode is not null and c.contractCode <> 'null' ")
List<Contract> contractCodePlatformNot(List<String> plats, List<Long> ids);
@Query(value = "select contract_code from contract where my_body_code = ?1 and product=?2 order by code_num desc limit 1", nativeQuery = true)
String findLastCode(String code, String platform);
@Query(value = "select * from contract where id = ?1 union all select * from contract where id = ?2", nativeQuery = true)
List<Contract> findByTwoIds(Long relationContract, Long relationContract1);
@Query(value = "SELECT * from contract where platform =?1 and email = ?2 and (status <> 'end' or status <> 'cancel')", nativeQuery = true)
List<Contract> findByPlatformAndEmailStatus(String platgorm, String email);
@Query(value = "select * from contract where contract_code =?2 and platform = ?1 ", nativeQuery = true)
Contract findOneByCode(String platform, String contractId);
@Query(value = "select * from contract where start_date >= ?1 and start_date <= ?2 and platform in ?3 and my_body_code in ?4 and id in ?5 and status!='del' order by ds desc", nativeQuery = true)
List<Contract> findByDsContractBody(String startDate, String endDate, List<String> platforms, List bodyids, List<String> ids);
@Query(value = "select * from contract where start_date >= ?1 and start_date <= ?2 and platform = ?3 and my_body_code in ?4 and status!='del' order by ds desc", nativeQuery = true)
List<Contract> findByDsContractBody(String startDate, String endDate, String platform, List bodyids);
@Query(value = "select * from contract where start_date >= ?1 and start_date <= ?2 and platform = ?3 and sale in ?4 and status!='del' order by ds desc", nativeQuery = true)
List<Contract> findByDsContractSalse(String startDate, String endDate, String platform, List salseid);
@Query(value = "select * from contract where start_date >= ?1 and start_date <= ?2 and platform in ?3 and sale in ?4 and id in ?5 and status!='del' order by ds desc", nativeQuery = true)
List<Contract> findByDsContractSalse(String startDate, String endDate, List<String> platforms, List salseid, List<String> ids);
@Query(value = "select contract_code,my_body_code,sale,platform from contract where contract_code in ?1", nativeQuery = true)
List<Object[]> findByContractCode(List<String> codesList);
@Query(value = "select * from contract where ((start_date <= ?1 and end_date >= ?1) or (start_date <= ?2 and end_date >= ?2) or (start_date >= ?1 and end_date <= ?2) or (start_date <= ?1 and end_date >= ?2)) and platform=?3 and status!='del' and (share_sign = 1 or share_sign is null) ", nativeQuery = true)
List<Contract> findShareContranctByDate3(String startDate, String endDate, String platform);
@Query(value = "select * from contract where ((start_date <= ?1 and end_date >= ?1) or (start_date <= ?2 and end_date >= ?2) or (start_date >= ?1 and end_date <= ?2) or (start_date <= ?1 and end_date >= ?2)) and platform=?3 and my_body_code = ?4 and status!='del' and (share_sign = 1 or share_sign is null) ", nativeQuery = true)
List<Contract> findShareContranctByDate2(String startDate, String endDate, String platform, String bodyCode);
@Query(value = "select * from contract where ((start_date <= ?1 and end_date >= ?1) or (start_date <= ?2 and end_date >= ?2) or (start_date >= ?1 and end_date <= ?2) or (start_date <= ?1 and end_date >= ?2)) and platform=?3 and (my_body_name like %?4% or customer_body like %?4% or contract_code like %?4%) and status!='del' and (share_sign = 1 or share_sign is null) ", nativeQuery = true)
List<Contract> findShareContranctByDateSerche(String startDate, String endDate, String platform, String serchName);
@Query(value = "select * from contract where ((start_date <= ?1 and end_date >= ?1) or (start_date <= ?2 and end_date >= ?2) or (start_date >= ?1 and end_date <= ?2) or (start_date <= ?1 and end_date >= ?2)) and platform=?3 and my_body_code = ?4 and (my_body_name like %?5% or customer_body like %?5% or contract_code like %?5%) and status!='del' and (share_sign = 1 or share_sign is null) ", nativeQuery = true)
List<Contract> findShareContranctByDate1(String startDate, String endDate, String platform, String bodyCode, String serchName);
@Query(value = "select * from contract where ((start_date <= ?1 and end_date >= ?1) or (start_date <= ?2 and end_date >= ?2) or (start_date >= ?1 and end_date <= ?2) or (start_date <= ?1 and end_date >= ?2)) and platform=?3 and (my_body_name like %?4% or customer_body like %?4% or contract_code like %?5%) and my_body_code in ?5 and status!='del' and (share_sign = 1 or share_sign is null) ", nativeQuery = true)
List<Contract> findShareContranctByDateSercheFinace(String startDate, String endDate, String platform, String serchName, List<String> financeBodies);
@Query(value = "select * from contract where ((start_date <= ?1 and end_date >= ?1) or (start_date <= ?2 and end_date >= ?2) or (start_date >= ?1 and end_date <= ?2) or (start_date <= ?1 and end_date >= ?2)) and platform=?3 and my_body_code in ?4 and status!='del' and (share_sign = 1 or share_sign is null) ", nativeQuery = true)
List<Contract> findShareContranctByDateFinace(String startDate, String endDate, String platform, List<String> financeBodies);
@Query(value = "select contract_code from contract where customer_body =?1 and contract_code <> ?2 and status in ('backfirst','normal','late') limit 3 ", nativeQuery = true)
List<String> findContractBodyNames(String customerBody, String contractCode);
@Query(value = "select * from contract where contract_code=?1 limit 1 ", nativeQuery = true)
Contract checkByCode(String code);
@Query(value = "select distinct email from contract where platform = ?1", nativeQuery = true)
List<String> findDistinctEmailByPlatform(String platform);
@Query(value = "SELECT * from contract where platform = ?1 and email = ?2", nativeQuery = true)
List<Contract> findByPlatformAndEmail(String platform, String email);
@Query(value = "select id from contract where (email is null or email='null') and ( end_date >= ?1 and end_date <= ?2 )", nativeQuery = true)
List<Long> findAllEmailIsNull(String startDate, String endDate);
@Query(value = "SELECT relation_contract FROM contract where contract_type=2 and status!='del'", nativeQuery = true)
List<Long> findRelationContractBySideAgre();
@Query(value = "SELECT * from contract where contract_code = ?1 UNION SELECT * from contract where relation_code = ?1 and create_time <= ?2 and id<> ?3 and contract_type = '2'", nativeQuery = true)
List<Contract> findByCreateTimeAndCodeNotOwn(String contractCode, String createTime,Long id);
@Query(value = "select * from contract where next_signed_contract_code=?1 limit 1 ",nativeQuery = true)
Contract findByNextSignedContractCode(String contractCode);
}