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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
package pwc.taxtech.atms.dao;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.session.RowBounds;
import pwc.taxtech.atms.MyMapper;
import pwc.taxtech.atms.dpo.ProjectDisplayDto;
import pwc.taxtech.atms.entity.Project;
import pwc.taxtech.atms.entity.ProjectExample;
import pwc.taxtech.atms.entity.ProjectStatusManage;
import java.util.List;
@Mapper
public interface ProjectMapper extends MyMapper {
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
long countByExample(ProjectExample example);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
int deleteByExample(ProjectExample example);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
int deleteByPrimaryKey(String id);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
int insert(Project record);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
int insertSelective(Project record);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
List<Project> selectByExampleWithRowbounds(ProjectExample example, RowBounds rowBounds);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
List<Project> selectByExample(ProjectExample example);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
Project selectByPrimaryKey(String id);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
int updateByExampleSelective(@Param("record") Project record, @Param("example") ProjectExample example);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
int updateByExample(@Param("record") Project record, @Param("example") ProjectExample example);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
int updateByPrimaryKeySelective(Project record);
/**
* This method was generated by MyBatis Generator.
* This method corresponds to the database table TAX_ADMIN.PROJECT
*
* @mbg.generated
*/
int updateByPrimaryKey(Project record);
@Select("<script>" +
"SELECT DISTINCT " +
" p.CREATE_TIME AS CreateTime, " +
" p.IS_ACTIVE AS IsActive, " +
" p.ORGANIZATION_ID AS OrganizationID, " +
" p.Name AS Name, " +
" p.Code AS Code, " +
" p.id AS id, " +
" p.INDUSTRY_ID AS IndustryID, " +
" p.Year AS Year, " +
" p.REGION_ID AS RegionID, " +
" region.SHORT_NAME AS RegionName, " +
" p.RULE_TYPE AS RuleType, " +
" projectServiceType.SERVICE_TYPE_ID AS ServiceTypeID, " +
" p.UPDATE_TIME AS UpdateTime, " +
" serviceType.Name AS ServiceTypeName, " +
" organization.Name AS OrganizationName, " +
" industry.Name AS IndustryName, " +
" projectServiceType.Template_Group_ID AS TemplateGroupID, " +
" templateGroup.Name AS TemplateGroupName, " +
" p.CLIENT_CODE AS ClientCode, " +
" p.DB_NAME AS DbName, " +
" 1 AS HaveCreateProject, " +
" p.ENTERPRISE_ACCOUNT_SET_ID AS EnterpriseAccountSetID, " +
" p.START_PERIOD AS StartPeriod, " +
" p.END_PERIOD AS EndPeriod " +
"FROM " +
" PROJECT p " +
" JOIN " +
" ORGANIZATION org ON p.ORGANIZATION_ID = org.id " +
" JOIN " +
" PROJECT_SERVICE_TYPE projectServiceType ON p.id = projectServiceType.PROJECT_ID " +
" JOIN " +
" Organization organization ON p.ORGANIZATION_ID = organization.id " +
" JOIN " +
" SERVICE_TYPE serviceType ON projectServiceType.SERVICE_TYPE_ID = serviceType.id " +
" JOIN " +
" Industry industry ON p.INDUSTRY_ID = industry.id " +
" JOIN " +
" TEMPLATE_GROUP templateGroup ON projectServiceType.TEMPLATE_GROUP_ID = templateGroup.id " +
" JOIN " +
" Region region ON p.REGION_ID = region.id " +
" JOIN " +
" ENTERPRISE_ACCOUNT_SET_ORG enterOrg ON p.ENTERPRISE_ACCOUNT_SET_ID = enterOrg.ENTERPRISE_ACCOUNT_SET_ID" +
" <where>" +
" p.IS_ACTIVE=1 and serviceType.IS_ACTIVE=1 and p.ORGANIZATION_ID = enterOrg.ORGANIZATION_ID" +
" <if test=\"orgId != null and orgId !='' \">AND org.id=#{orgId}</if>" +
// " <if test=\"serverId != null and serverId !='' \">AND p.serviceType=#{serverId}</if>" +
" <if test=\"projectYear != null\">AND p.Year=#{projectYear}</if>" +
" </where>" +
"</script>")
List<ProjectDisplayDto> getProjectList(@Param("orgId") String orgId, @Param("serverId") String serviceId,
@Param("projectYear") Integer projectYear);
@Select("SELECT " +
" p.PERIOD_ID AS periodId, p.STATUS AS status " +
"FROM " +
" PROJECT_STATUS_MANAGE p " +
"WHERE " +
" p.PROJECT_ID = #{projectId} " +
"ORDER BY PERIOD_ID , STATUS")
List<ProjectStatusManage> getStatusesByProjectId(String projectId);
@Select("<script>" +
"SELECT " +
" SYSDATE AS CreateTime, " +
" 1 AS IsActive, " +
" org.id AS OrganizationID, " +
" org.Name AS Name, " +
" org.Code AS Code, " +
" SYS_GUID() AS id, " +
" org.Industry_ID AS IndustryID, " +
" 0 AS Year, " +
" org.REGION_ID AS RegionID, " +
" 0 AS RuleType, " +
" st.id AS ServiceTypeID, " +
" reg.Short_Name AS RegionName, " +
" SYSDATE AS UpdateTime, " +
" st.Name AS ServiceTypeName, " +
" org.Name AS OrganizationName, " +
" ind.Name AS IndustryName, " +
" ostg.TEMPLATE_GROUP_ID AS TemplateGroupID, " +
" '' AS TemplateGroupName, " +
" org.Client_Code AS ClientCode, " +
" '' AS DbName, " +
" 0 AS HaveCreateProject, " +
" ea.Enterprise_Account_Set_ID AS EnterpriseAccountSetID, " +
" 1 AS StartPeriod, " +
" 12 AS EndPeriod, " +
" ea.EFFECTIVE_DATE AS EffectiveDate, " +
" ea.EXPIRED_DATE AS ExpiredDate " +
"FROM " +
" ORGANIZATION org " +
" JOIN " +
" ENTERPRISE_ACCOUNT_SET_ORG ea ON org.id = ea.Organization_ID " +
" JOIN " +
" ORG_SERVICE_TEMPLATE_GROUP ostg ON org.id = ostg.ORGANIZATION_ID " +
" JOIN " +
" SERVICE_TYPE st ON ostg.SERVICE_TYPE_ID = st.id " +
" JOIN " +
" INDUSTRY ind ON org.Industry_ID = ind.id " +
" JOIN " +
" REGION reg ON org.Region_ID = reg.id " +
"WHERE " +
" 1=1" +
" <if test=\"orgId != null and orgId !='' \">AND org.id=#{orgId}</if>" +
" AND org.Is_Active = 1 " +
" AND st.Is_Active = 1 " +
" <if test=\"serviceId != null and serviceId !='' \">AND ostg.Service_Type_ID=#{serviceId}</if>" +
"ORDER BY ea.EFFECTIVE_DATE,org.Code" +
"</script>")
List<ProjectDisplayDto> getProjectFromEnterpriseAccountSetOrg(@Param("orgId") String orgId, @Param("serviceId") String serviceId);
@Select("SELECT " +
" PERIOD_ID AS periodId, STATUS AS status " +
"FROM " +
" PROJECT_STATUS_MANAGE p " +
"WHERE " +
" p.PROJECT_ID = #{projectId} " +
"ORDER BY PERIOD_ID , STATUS")
List<ProjectStatusManage> selectProjectAllStatus(String projectId);
@Select("SELECT max(DB_NAME) as DbName FROM Project WHERE Year=#{year}")
String maxDbName(Integer year);
@Select("SELECT " +
" TEMPLATE_GROUP_ID " +
"FROM PROJECT a " +
" JOIN ORG_SERVICE_TEMPLATE_GROUP b " +
" ON a.ORGANIZATION_ID = b.ORGANIZATION_ID " +
"WHERE a.ID = #{projectId} " +
" AND b.SERVICE_TYPE_ID = #{serviceType}")
Long getTemplateGroupIdByProject(@Param("projectId") String projectId, @Param("serviceType") Integer serviceType);
}