FormulaAdminMapper.java 7.27 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 pwc.taxtech.atms.MyMapper;
7 8
import pwc.taxtech.atms.dpo.CellTemplatePerGroupDto;
import pwc.taxtech.atms.dpo.GroupId;
9
import pwc.taxtech.atms.entity.Project;
10

11
import java.math.BigDecimal;
12 13 14
import java.util.List;

@Mapper
15
public interface FormulaAdminMapper extends MyMapper {
16

17 18
    @Select("" +
            "SELECT  " +
gary's avatar
gary committed
19 20
            "  cell_template.cell_template_id AS cellTemplateID,  " +
            "  template.template_id AS reportTemplateID,  " +
21 22 23
            "  template.template_group_id AS reportTemplateGroupID,  " +
            "  cell_template.row_index AS rowIndex,  " +
            "  cell_template.column_index AS columnIndex,  " +
gary's avatar
gary committed
24
            "  template.code AS reportCode,  " +
25 26 27 28 29
            "  cell_template.column_name AS columnName,  " +
            "  cell_template.row_name AS rowName,  " +
            "  cell_template.data_type AS resultType   " +
            "FROM  " +
            "  period_template template  " +
gary's avatar
gary committed
30
            "  JOIN period_cell_template cell_template ON template.template_id = cell_template.report_template_id   " +
31
            "WHERE  " +
gary's avatar
gary committed
32 33 34 35 36 37 38 39
            "  template.template_id IN ( SELECT template_id FROM period_template WHERE template_group_id = #{templateGroupId} " +
            "  AND is_active_association = 1 AND code = #{code} )   " +
            "  AND row_index = #{rowIndex}   " +
            "  AND column_index = #{columnIndex}   " +
            "  AND template.project_id = #{projectId} " +
            "  AND template.period = #{period}   " +
            "  AND cell_template.project_id = #{projectId}   " +
            "  AND cell_template.period = #{period}" +
40 41 42 43 44 45 46
            "")
    List<CellTemplatePerGroupDto> getCellTemplatePerGroupDto(@Param("templateGroupId") Long templateGroupId,
                                                             @Param("code") String code,
                                                             @Param("rowIndex") Integer rowIndex,
                                                             @Param("columnIndex") Integer columnIndex,
                                                             @Param("projectId") String projectId,
                                                             @Param("period") Integer period);
47

48

49 50 51
    @Select("SELECT  " +
            "    rule.template_group_id as groupId, rule.is_default as isDefault " +
            "FROM " +
gary's avatar
gary committed
52
            "    project p, " +
53 54
            "    tax_payer_report_rule rule " +
            "WHERE " +
gary's avatar
gary committed
55
            "    (p.organization_id = rule.organization_id " +
56
            "        OR rule.is_default = 1) " +
57
            "        AND p.id = #{projectId}")
58
    List<GroupId> getTemplateGroupId(@Param("projectId") String projectId);
59

gary's avatar
gary committed
60 61


neo's avatar
neo committed
62
    @Select("SELECT  " +
gary's avatar
gary committed
63
            "    p.id " +
neo's avatar
neo committed
64
            "FROM " +
gary's avatar
gary committed
65
            "    project p " +
neo's avatar
neo committed
66
            "        JOIN " +
gary's avatar
gary committed
67
            "    project_service_type ps ON p.id = ps.project_id " +
neo's avatar
neo committed
68
            "WHERE " +
gary's avatar
gary committed
69 70 71 72 73 74 75
            "    ps.service_type_id IN (\"2\" , \"3\") " +
            "        AND p.year = #{year} " +
            "        AND p.organization_id = #{orgId} " +
            "        AND p.is_active = 1 " +
            // todo 19/12/2018 gary 需修改rownum
//            " AND ROWNUM=1"+
            "ORDER BY ps.service_type_id ")
76
    String getPastProjectId(@Param("year") int year, @Param("orgId") String organizationId);
77 78 79

    @Select("" +
            "SELECT  " +
gary's avatar
gary committed
80 81 82 83
            "  p.id AS id,  " +
            "  p.code AS code,  " +
            "  p.name AS name,  " +
            "  p.organization_id AS organizationId   " +
84
            "FROM  " +
gary's avatar
gary committed
85 86 87
            "  project p  " +
            "  JOIN ( SELECT code, organization_id FROM project WHERE id = #{projectId}  ) t ON p.code = t.code   " +
            "  AND p.organization_id = t.organization_id AND p.year = #{year}" +
88 89
            "")
    Project getFixedProject(@Param("projectId") String projectId, @Param("year") Integer year);
chase's avatar
chase committed
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


    @Select("select " +
            " detail.*, " +
            " source_cell.cell_template_id AS cellTemplateID," +
            " source_cell.column_index     AS columnIndex," +
            " source_cell.column_name      AS columnName," +
            " source_cell.row_name         AS rowName," +
            " source_cell.data_type        AS resultType " +
            "from (" +
                    " select " +
                    " template.template_id           AS reportTemplateID," +
                    " template.template_group_id     AS reportTemplateGroupID," +
                    " template.code                  AS reportCode," +
                    " cell_template.row_index        AS rowIndex " +
                    " from period_template as template " +
                    " inner join period_cell_template as cell_template" +
                    " on template.template_id = cell_template.report_template_id" +
                    " where template.template_group_id = #{templateGroupId}" +
                    " and template.is_active_association = 1" +
                    " and template.code = #{code}" +
                    " and template.project_id = #{projectId}" +
                    " and template.period = #{period}" +
                    " and cell_template.project_id = #{projectId}" +
                    " and cell_template.period = #{period}" +
                    " and cell_template.column_index = #{rowColumnIndex}" +
                    " and cell_template.row_name = #{rowName}) as detail," +
            " period_cell_template as source_cell" +
            " where source_cell.project_id = #{projectId}" +
            " and source_cell.period = #{period}" +
            " and detail.reportTemplateID = source_cell.report_template_id" +
            " and detail.rowIndex = source_cell.row_index" +
            " and source_cell.column_index = #{columnIndex}"
            )
    List<CellTemplatePerGroupDto> getCellTemplateByNameAndIndex(
                                                             @Param("templateGroupId") Long templateGroupId,
                                                             @Param("code") String code,
                                                             @Param("rowColumnIndex") Integer rowColumnIndex,
                                                             @Param("rowName") String rowName,
                                                             @Param("columnIndex") Integer columnIndex,
                                                             @Param("projectId") String projectId,
                                                             @Param("period") Integer period);
132 133


134
       List<CellTemplatePerGroupDto> getCellTemplateByTypeAndIndex(
135 136 137 138 139 140 141 142 143 144 145 146
            @Param("templateGroupId") Long templateGroupId,
            @Param("projectId") String projectId,
            @Param("code") String code,
            @Param("rowColumnIndex") Integer rowColumnIndex,
            @Param("taxRate") BigDecimal taxRate,
            @Param("revenueType") Integer revenueType,
            @Param("taxType") Integer taxType,
            @Param("columnIndex") Integer columnIndex,
            @Param("period") Integer period,
            @Param("orgId") String orgId,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
147
}