EnterpriseAccountDao.java 7.87 KB
Newer Older
1
package pwc.taxtech.atms.dao;
eddie.woo's avatar
eddie.woo committed
2 3 4 5 6 7 8 9 10

import com.google.common.collect.Lists;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import pwc.taxtech.atms.constant.AccountMappingConstant;
import pwc.taxtech.atms.constant.EnterpriseAccountConstant;
11
import pwc.taxtech.atms.dpo.EnterpriseAccountDto;
12 13
import pwc.taxtech.atms.entity.EnterpriseAccount;
import pwc.taxtech.atms.entity.EnterpriseAccountExample;
eddie.woo's avatar
eddie.woo committed
14 15 16 17 18 19 20 21 22 23 24 25 26

import java.util.List;
import java.util.Map;
import java.util.Optional;

@Service
public class EnterpriseAccountDao {
    @Autowired
    private EnterpriseAccountMapper enterpriseAccountMapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;

eddie.woo's avatar
eddie.woo committed
27 28 29 30 31 32 33 34
    public List<EnterpriseAccount> getAll() {
        EnterpriseAccountExample example = new EnterpriseAccountExample();
        return enterpriseAccountMapper.selectByExample(example);
    }

    public List<EnterpriseAccount> getByEpAccountSetId(String epAccountSetId) {
        EnterpriseAccountExample example = new EnterpriseAccountExample();
        EnterpriseAccountExample.Criteria criteria = example.createCriteria();
35
        criteria.andEnterpriseAccountSetIdEqualTo(epAccountSetId);
eddie.woo's avatar
eddie.woo committed
36 37 38 39
        return enterpriseAccountMapper.selectByExample(example);
    }

    public List<EnterpriseAccount> getEpAccountBy(String parentCode, String epAccountSetId) {
eddie.woo's avatar
eddie.woo committed
40 41
        EnterpriseAccountExample example = new EnterpriseAccountExample();
        EnterpriseAccountExample.Criteria criteria = example.createCriteria();
eddie.woo's avatar
eddie.woo committed
42
        criteria.andParentCodeEqualTo(parentCode);
43
        criteria.andEnterpriseAccountSetIdEqualTo(epAccountSetId);
eddie.woo's avatar
eddie.woo committed
44
        return enterpriseAccountMapper.selectByExample(example);
eddie.woo's avatar
eddie.woo committed
45 46 47 48 49 50
    }

    public Optional<EnterpriseAccount> getEpAccount(String epAccountCode, String epAccountSetId) {
        EnterpriseAccountExample example = new EnterpriseAccountExample();
        EnterpriseAccountExample.Criteria criteria = example.createCriteria();
        criteria.andCodeEqualTo(epAccountCode);
51
        criteria.andEnterpriseAccountSetIdEqualTo(epAccountSetId);
eddie.woo's avatar
eddie.woo committed
52 53 54
        return enterpriseAccountMapper.selectByExample(example).stream().findFirst();
    }

55
    public String getEpAccountId(String epAccountCode, String epAccountSetId) {
eddie.woo's avatar
eddie.woo committed
56
        Optional<EnterpriseAccount> optional = getEpAccount(epAccountCode, epAccountSetId);
57
        return optional.isPresent() ? optional.get().getId() : StringUtils.EMPTY;
eddie.woo's avatar
eddie.woo committed
58 59 60 61
    }

    public List<EnterpriseAccountDto> getAccountMappingResult(String accountSetId, int ruleId, String industryId) {
        //amk.IndustryId倒序排列可以保证在结果集中,同一个COA,本行业的匹配结果在通用关键字匹配结果之前出现
gary's avatar
gary committed
62 63 64 65 66 67 68
        // todo 19/12/2018 待修改
        String sql = "SELECT a.code as Code, a.is_leaf as Is_Leaf, sa.code StdCode,a.full_name as AccountFullName, amk.full_name as MappingFullName,amk.industry_id as Industry_Id"
                + " FROM (select * from account_mapping_keyword where industry_id=?) amk "
                + " INNER JOIN (select * from standard_account where industry_id=?) sa ON amk.standard_code = sa.code"
                + " INNER JOIN enterprise_account a on a.full_name like amk.full_name"
                + " WHERE amk.rule_type = ? AND sa.is_leaf = 1 AND a.enterprise_account_set_id=?"
                + " ORDER BY a.code,amk.industry_id DESC";
eddie.woo's avatar
eddie.woo committed
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

        List<Map<String, Object>> queryList = jdbcTemplate.queryForList(sql, industryId, industryId, ruleId, accountSetId);
        List<EnterpriseAccountDto> list = Lists.newArrayList();
        for (Map<String, Object> obj : queryList) {
            EnterpriseAccountDto account = new EnterpriseAccountDto();
            account.setCode(MapUtils.getString(obj, "Code"));
            account.setIsLeaf(StringUtils.equals(MapUtils.getString(obj, "isLeaf"), "1"));
            account.setStdCode(MapUtils.getString(obj, "StdCode"));
            account.setFullName(MapUtils.getString(obj, "AccountFullName"));
            account.setMappingFullName(MapUtils.getString(obj, "MappingFullName"));

            list.add(account);
        }

        return list;
    }

86
    public List<Map<String, Object>> selectAccounts(String industryId, String accountSetId, String organizationId, int iAcctProp,
eddie.woo's avatar
eddie.woo committed
87 88 89 90 91 92 93 94 95 96
                                                    int iIsMapped, int iLevel, Boolean bIsActive) {
        //In admin module, enterprise account has IsInVoucher to identify vouchers
        //However web admin is not requried voucher, so the field is not available in web admin,
        //so set acctVoucherHasNull=true at this moment
        //But in the web admin, balance is not required at this stage, so set balanceIsNull=true
        //string sql = "select count(1) from EnterpriseAccount where IsInVoucher is null";
        //bool balanceIsNull = true;
        //bool acctVoucherHasNull = true;
        List<String> whereList = Lists.newArrayList();

gary's avatar
gary committed
97 98

        // todo 19/12/2018 待修改
eddie.woo's avatar
eddie.woo committed
99
        String sql = "SELECT * FROM ( " +
frank.xa.zhang's avatar
frank.xa.zhang committed
100
                "SELECT CASE Is_Leaf WHEN 0 THEN '+' ELSE '' END IsEx,Code, Name, Full_Name, " +
eddie.woo's avatar
eddie.woo committed
101
                "CASE Direction WHEN 1 THEN N'借方' WHEN -1 THEN N'贷方' END Direction, " +
frank.xa.zhang's avatar
frank.xa.zhang committed
102 103 104 105 106 107 108
                "CASE Std_Account_Code WHEN '0000' THEN '-' ELSE Std_Account_Code END Std_Code," +
                "CASE Std_Account_Code WHEN '0000' THEN '-' ELSE Std_Name END Std_Name, " +
                "CASE Std_Account_Code WHEN '0000' THEN '-' ELSE CONCAT(CONCAT(STD_ACCOUNT_CODE,'/'), STD_FULL_NAME) END Std_Code_Name, " +
                "CASE Std_Account_Code WHEN '0000' THEN '-' ELSE CASE Std_Direction WHEN 1 THEN '借方' WHEN -1 THEN '贷方' END END Std_Direction, " +
                "CASE WHEN Acct_Prop<>Std_Acct_Prop THEN 1 ELSE 0 END Is_Acct_Prop_Diff," +
                "CASE WHEN Std_Account_Code IS NULL THEN 0 ELSE 1 END Mapping_Status, " +
                "CASE WHEN Std_Account_Code IS NULL THEN Acct_Prop ELSE Std_Acct_Prop END Acct_Prop, Parent_Code, Acct_Level, Std_Account_Code Acct_Std_Code, Is_Leaf " +
gary's avatar
gary committed
109 110 111
                "        FROM (SELECT id as id,code as Code,name as NAME,full_name as Full_Name,direction as Direction,acct_prop as Acct_Prop,parent_code as Parent_Code,acct_level as Acct_Level,is_leaf as Is_Leaf FROM enterprise_account WHERE enterprise_account_set_id=?)  ea " +
                "LEFT JOIN (SELECT enterprise_account_code as Enterprise_Account_Code,standard_account_code as Standard_Account_Code FROM account_mapping WHERE organization_id=? AND industry_id=?) am ON ea.Code=am.Enterprise_Account_Code " +
                "LEFT JOIN (SELECT id  as Std_Id, code as Std_Account_Code, name as Std_Name, full_name as Std_Full_Name,direction as Std_Direction,acct_prop as Std_Acct_Prop FROM standard_account WHERE industry_id=?) sa ON am.Standard_Account_Code=sa.Std_Account_Code ) T";
eddie.woo's avatar
eddie.woo committed
112

113
        List<Object> params = Lists.newArrayList(accountSetId, organizationId, industryId, industryId);
eddie.woo's avatar
eddie.woo committed
114
        if (iAcctProp != EnterpriseAccountConstant.InvalidId) {
gary's avatar
gary committed
115
            sql += "acct_prop = ?";
eddie.woo's avatar
eddie.woo committed
116 117 118
            params.add(iAcctProp);
        }
        if (iIsMapped == AccountMappingConstant.Status.UNMAPPED.getCode()) {
frank.xa.zhang's avatar
frank.xa.zhang committed
119
            sql += "Std_Code IS NULL";
eddie.woo's avatar
eddie.woo committed
120
        } else if (iIsMapped == AccountMappingConstant.Status.MAPPED.getCode()) {
frank.xa.zhang's avatar
frank.xa.zhang committed
121
            sql += "Std_Code IS NOT NULL";
eddie.woo's avatar
eddie.woo committed
122
        } else if (iIsMapped == AccountMappingConstant.Status.DIRECTION_DIFFERENT.getCode()) {
frank.xa.zhang's avatar
frank.xa.zhang committed
123
            sql += "Std_Code IS NOT NULL AND Direction<>Std_Direction";
eddie.woo's avatar
eddie.woo committed
124
        } else if (iIsMapped == AccountMappingConstant.Status.ACCOUNT_TYPE_DIFFERENT.getCode()) {
frank.xa.zhang's avatar
frank.xa.zhang committed
125
            sql += "Std_Code IS NOT NULL AND Is_Acct_Prop_Diff=1";
eddie.woo's avatar
eddie.woo committed
126 127
        }
        if (iLevel != EnterpriseAccountConstant.InvalidId) {
gary's avatar
gary committed
128
            sql += "acct_level = ?";
eddie.woo's avatar
eddie.woo committed
129 130 131
            params.add(iLevel);
        }

gary's avatar
gary committed
132
        sql += " ORDER BY code";
eddie.woo's avatar
eddie.woo committed
133 134 135 136 137
        return jdbcTemplate.queryForList(sql, params.toArray());
    }


}