ProjectMapper.java 9.76 KB
Newer Older
1 2 3 4 5 6
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;
gary's avatar
gary committed
7
import org.springframework.stereotype.Repository;
8
import pwc.taxtech.atms.MyMapper;
9
import pwc.taxtech.atms.dpo.ProjectDisplayDto;
10 11 12
import pwc.taxtech.atms.entity.Project;
import pwc.taxtech.atms.entity.ProjectExample;
import pwc.taxtech.atms.entity.ProjectStatusManage;
13

14 15
import java.util.List;

16
@Mapper
gary's avatar
gary committed
17
@Repository
18 19 20
public interface ProjectMapper extends MyMapper {
    /**
     * This method was generated by MyBatis Generator.
21
     * This method corresponds to the database table TAX_ADMIN.PROJECT
22 23 24 25 26 27 28
     *
     * @mbg.generated
     */
    long countByExample(ProjectExample example);

    /**
     * This method was generated by MyBatis Generator.
29
     * This method corresponds to the database table TAX_ADMIN.PROJECT
30 31 32 33 34 35 36
     *
     * @mbg.generated
     */
    int deleteByExample(ProjectExample example);

    /**
     * This method was generated by MyBatis Generator.
37
     * This method corresponds to the database table TAX_ADMIN.PROJECT
38 39 40
     *
     * @mbg.generated
     */
41
    int deleteByPrimaryKey(String id);
42 43 44

    /**
     * This method was generated by MyBatis Generator.
45
     * This method corresponds to the database table TAX_ADMIN.PROJECT
46 47 48 49 50 51 52
     *
     * @mbg.generated
     */
    int insert(Project record);

    /**
     * This method was generated by MyBatis Generator.
53
     * This method corresponds to the database table TAX_ADMIN.PROJECT
54 55 56 57 58 59 60
     *
     * @mbg.generated
     */
    int insertSelective(Project record);

    /**
     * This method was generated by MyBatis Generator.
61
     * This method corresponds to the database table TAX_ADMIN.PROJECT
62 63 64 65 66 67 68
     *
     * @mbg.generated
     */
    List<Project> selectByExampleWithRowbounds(ProjectExample example, RowBounds rowBounds);

    /**
     * This method was generated by MyBatis Generator.
69
     * This method corresponds to the database table TAX_ADMIN.PROJECT
70 71 72 73 74 75 76
     *
     * @mbg.generated
     */
    List<Project> selectByExample(ProjectExample example);

    /**
     * This method was generated by MyBatis Generator.
77
     * This method corresponds to the database table TAX_ADMIN.PROJECT
78 79 80
     *
     * @mbg.generated
     */
81
    Project selectByPrimaryKey(String id);
82 83 84

    /**
     * This method was generated by MyBatis Generator.
85
     * This method corresponds to the database table TAX_ADMIN.PROJECT
86 87 88 89 90 91 92
     *
     * @mbg.generated
     */
    int updateByExampleSelective(@Param("record") Project record, @Param("example") ProjectExample example);

    /**
     * This method was generated by MyBatis Generator.
93
     * This method corresponds to the database table TAX_ADMIN.PROJECT
94 95 96 97 98 99 100
     *
     * @mbg.generated
     */
    int updateByExample(@Param("record") Project record, @Param("example") ProjectExample example);

    /**
     * This method was generated by MyBatis Generator.
101
     * This method corresponds to the database table TAX_ADMIN.PROJECT
102 103 104 105 106 107 108
     *
     * @mbg.generated
     */
    int updateByPrimaryKeySelective(Project record);

    /**
     * This method was generated by MyBatis Generator.
109
     * This method corresponds to the database table TAX_ADMIN.PROJECT
110 111 112 113 114 115
     *
     * @mbg.generated
     */
    int updateByPrimaryKey(Project record);

    @Select("<script>" +
116
            "SELECT DISTINCT " +
gary's avatar
gary committed
117 118 119 120 121
            "    p.create_time AS CreateTime, " +
            "    p.is_active AS IsActive, " +
            "    p.organization_id AS OrganizationID, " +
            "    p.name AS Name, " +
            "    p.code AS Code, " +
122
            "    p.id AS id, " +
gary's avatar
gary committed
123 124 125 126 127 128 129 130 131 132 133 134 135 136
            "    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, " +
137
            "    1 AS HaveCreateProject, " +
gary's avatar
gary committed
138 139 140
            "    p.enterprise_account_set_id AS EnterpriseAccountSetID, " +
            "    p.start_period AS StartPeriod, " +
            "    p.end_period AS EndPeriod " +
141
            "FROM " +
gary's avatar
gary committed
142
            "    project p " +
143
            "        JOIN " +
gary's avatar
gary committed
144
            "    organization org ON p.organization_id = org.id " +
145
            "        JOIN " +
gary's avatar
gary committed
146
            "    project_service_type projectServiceType ON p.id = projectServiceType.project_id " +
147
            "        JOIN " +
gary's avatar
gary committed
148
            "    organization organization ON p.organization_id = organization.id " +
149
            "        JOIN " +
gary's avatar
gary committed
150
            "    service_type serviceType ON projectServiceType.service_type_id = serviceType.id " +
151
            "        JOIN " +
gary's avatar
gary committed
152
            "    industry industry ON p.industry_id = industry.id " +
153
            "        JOIN " +
gary's avatar
gary committed
154
            "    template_group templateGroup ON projectServiceType.template_group_id = templateGroup.id " +
155
            "        JOIN " +
gary's avatar
gary committed
156
            "    region region ON p.region_id = region.id " +
157
            "        JOIN " +
gary's avatar
gary committed
158
            "    enterprise_account_set_org enterOrg ON p.enterprise_account_set_id = enterOrg.enterprise_account_set_id" +
159
            "   <where>" +
gary's avatar
gary committed
160
            "       p.is_active=1 and serviceType.is_active=1 and p.organization_id = enterOrg.organization_id" +
neo's avatar
neo committed
161
            "       <if test=\"orgId != null and orgId !='' \">AND org.id=#{orgId}</if>" +
frank.xa.zhang's avatar
frank.xa.zhang committed
162
//            "       <if test=\"serverId != null and serverId !='' \">AND p.serviceType=#{serverId}</if>" +
163 164 165
            "       <if test=\"projectYear != null\">AND p.Year=#{projectYear}</if>" +
            "   </where>" +
            "</script>")
166
    List<ProjectDisplayDto> getProjectList(@Param("orgId") String orgId, @Param("serverId") String serviceId,
167
                                           @Param("projectYear") Integer projectYear);
168 169

    @Select("SELECT  " +
gary's avatar
gary committed
170
            "    p.period_id AS periodId, p.status AS status " +
171
            "FROM " +
gary's avatar
gary committed
172
            "    project_status_manage p " +
173
            "WHERE " +
gary's avatar
gary committed
174 175
            "    p.project_id = #{projectId} " +
            "ORDER BY period_id , status")
176
    List<ProjectStatusManage> getStatusesByProjectId(String projectId);
177 178

    @Select("<script>" +
179
            "SELECT   " +
gary's avatar
gary committed
180
            "    SYSDATE() AS CreateTime,  " +
181 182
            "    1 AS IsActive,  " +
            "    org.id AS OrganizationID,  " +
gary's avatar
gary committed
183 184 185 186
            "    org.name AS Name,  " +
            "    org.code AS Code,  " +
            "    UUID() AS id,  " +
            "    org.industry_id AS IndustryID,  " +
187
            "    0 AS Year,  " +
gary's avatar
gary committed
188
            "    org.region_id AS RegionID,  " +
189 190
            "    0 AS RuleType,  " +
            "    st.id AS ServiceTypeID,  " +
gary's avatar
gary committed
191 192 193 194 195 196 197 198 199
            "    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,  " +
200
            "    0 AS HaveCreateProject,  " +
gary's avatar
gary committed
201
            "    ea.enterprise_account_set_id AS EnterpriseAccountSetID,  " +
202 203
            "    1 AS StartPeriod,  " +
            "    12 AS EndPeriod,  " +
gary's avatar
gary committed
204 205
            "    ea.effective_date AS EffectiveDate,  " +
            "    ea.expired_date AS ExpiredDate  " +
206
            "FROM  " +
gary's avatar
gary committed
207
            "    organization org  " +
208
            "        JOIN  " +
gary's avatar
gary committed
209
            "    enterprise_account_set_org ea ON org.id = ea.organization_id  " +
210
            "        JOIN  " +
gary's avatar
gary committed
211
            "    org_service_template_group ostg ON org.id = ostg.organization_id  " +
212
            "        JOIN  " +
gary's avatar
gary committed
213
            "    service_type st ON ostg.service_type_id = st.id  " +
214
            "        JOIN  " +
gary's avatar
gary committed
215
            "    industry ind ON org.industry_id = ind.id  " +
216
            "        JOIN  " +
gary's avatar
gary committed
217
            "    region reg ON org.region_id = reg.id " +
218 219
            "WHERE " +
            "  1=1" +
220
            "       <if test=\"orgId != null and orgId !='' \">AND org.id=#{orgId}</if>" +
gary's avatar
gary committed
221 222 223 224
            "        AND org.is_active = 1 " +
            "        AND st.is_active = 1 " +
            "       <if test=\"serviceId != null and serviceId !='' \">AND ostg.ServiceTypeID=#{serviceId}</if>" +
            "ORDER BY ea.effective_date,org.code" +
225
            "</script>")
neo's avatar
neo committed
226
    List<ProjectDisplayDto> getProjectFromEnterpriseAccountSetOrg(@Param("orgId") String orgId, @Param("serviceId") String serviceId);
227 228

    @Select("SELECT  " +
gary's avatar
gary committed
229
            "    period_id AS periodId, status AS status " +
230
            "FROM " +
gary's avatar
gary committed
231
            "    project_status_manage p " +
232
            "WHERE " +
gary's avatar
gary committed
233 234
            "    p.project_id = #{projectId} " +
            "ORDER BY period_id , status")
235
    List<ProjectStatusManage> selectProjectAllStatus(String projectId);
236

gary's avatar
gary committed
237
    @Select("SELECT max(db_name) as db_name FROM project WHERE year=#{year}")
238
    String maxDbName(Integer year);
239

240
    @Select("SELECT " +
gary's avatar
gary committed
241 242 243 244 245 246
            "  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}")
247
    Long getTemplateGroupIdByProject(@Param("projectId") String projectId, @Param("serviceType") Integer serviceType);
248
}