FormulaAdminMapper.java 9.18 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

neo's avatar
neo committed
61
    @Select("SELECT  " +
gary's avatar
gary committed
62
            "    p.id " +
neo's avatar
neo committed
63
            "FROM " +
gary's avatar
gary committed
64
            "    project p " +
neo's avatar
neo committed
65
            "        JOIN " +
gary's avatar
gary committed
66
            "    project_service_type ps ON p.id = ps.project_id " +
neo's avatar
neo committed
67
            "WHERE " +
gary's avatar
gary committed
68 69 70 71 72 73 74
            "    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 ")
75
    String getPastProjectId(@Param("year") int year, @Param("orgId") String organizationId);
76 77 78

    @Select("" +
            "SELECT  " +
gary's avatar
gary committed
79 80 81 82
            "  p.id AS id,  " +
            "  p.code AS code,  " +
            "  p.name AS name,  " +
            "  p.organization_id AS organizationId   " +
83
            "FROM  " +
gary's avatar
gary committed
84 85 86
            "  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}" +
87 88
            "")
    Project getFixedProject(@Param("projectId") String projectId, @Param("year") Integer year);
chase's avatar
chase committed
89 90 91 92 93 94 95 96 97 98


    @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 (" +
99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115
            " 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," +
chase's avatar
chase committed
116 117 118 119 120 121
            " 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}"
122
    )
chase's avatar
chase committed
123
    List<CellTemplatePerGroupDto> getCellTemplateByNameAndIndex(
124 125 126 127 128 129 130
            @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);
131 132


133
    List<CellTemplatePerGroupDto> getCellTemplateByTypeAndIndex(
134 135 136 137 138 139 140 141 142 143 144 145
            @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);
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


    @Select("" +
            "SELECT  " +
            "  cell_template.cell_template_id AS cellTemplateID,  " +
            "  template.template_id AS reportTemplateID,  " +
            "  template.template_group_id AS reportTemplateGroupID,  " +
            "  cell_template.row_index AS rowIndex,  " +
            "  cell_template.column_index AS columnIndex,  " +
            "  template.code AS reportCode,  " +
            "  cell_template.column_name AS columnName,  " +
            "  cell_template.row_name AS rowName,  " +
            "  cell_template.data_type AS resultType   " +
            "d.data AS content" +
            "FROM  " +
            "  period_template template  " +
            "  LEFT JOIN period_cell_template cell_template ON template.template_id = cell_template.report_template_id   " +
            "  LEFT JOIN period_cell_data  d on d.cell_template_id = cell_template.cell_template_id" +
            "WHERE  " +
            "  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 = #{cellRow}   " +
            "  AND column_index = #{cellCol}   " +
            "  AND template.project_id = #{projectId} " +
            "  AND template.period = #{period}   " +
            "  AND cell_template.project_id = #{projectId}   " +
            "  AND cell_template.period = #{period}" +
            "")
    List<CellTemplatePerGroupDto> getCellDataByPos(@Param("templateId") Long templateId, @Param("period") Integer period, @Param("cellRow") String cellRow, @Param("cellCol") String cellCol, @Param("projectId") String projectId);


    @Select("" +
            "SELECT  " +
            "${getField} as data" +
            "FROM ${tableName}  " +
            "inner join project pro on pro.organization_id = t.organization_id   " +
            " and ${selectFilter}" +
            " and pro.year = #{year}" +
            "")
    List<CellTemplatePerGroupDto> getTableData(@Param("tableName") String tableName,
                                               @Param("getField") String getField,
                                               @Param("selectFilter") String selectFilter,
                                               @Param("year") String year,
                                               @Param("selectPeriod") String selectPeriod);
190
}