package pwc.taxtech.atms.dao; 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; import pwc.taxtech.atms.dpo.EnterpriseAccountDto; import pwc.taxtech.atms.entity.EnterpriseAccount; import pwc.taxtech.atms.entity.EnterpriseAccountExample; import java.util.List; import java.util.Map; import java.util.Optional; @Service public class EnterpriseAccountDao { @Autowired private EnterpriseAccountMapper enterpriseAccountMapper; @Autowired private JdbcTemplate jdbcTemplate; 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(); criteria.andEnterpriseAccountSetIdEqualTo(epAccountSetId); return enterpriseAccountMapper.selectByExample(example); } public List<EnterpriseAccount> getEpAccountBy(String parentCode, String epAccountSetId) { EnterpriseAccountExample example = new EnterpriseAccountExample(); EnterpriseAccountExample.Criteria criteria = example.createCriteria(); criteria.andParentCodeEqualTo(parentCode); criteria.andEnterpriseAccountSetIdEqualTo(epAccountSetId); return enterpriseAccountMapper.selectByExample(example); } public Optional<EnterpriseAccount> getEpAccount(String epAccountCode, String epAccountSetId) { EnterpriseAccountExample example = new EnterpriseAccountExample(); EnterpriseAccountExample.Criteria criteria = example.createCriteria(); criteria.andCodeEqualTo(epAccountCode); criteria.andEnterpriseAccountSetIdEqualTo(epAccountSetId); return enterpriseAccountMapper.selectByExample(example).stream().findFirst(); } public String getEpAccountId(String epAccountCode, String epAccountSetId) { Optional<EnterpriseAccount> optional = getEpAccount(epAccountCode, epAccountSetId); return optional.isPresent() ? optional.get().getId() : StringUtils.EMPTY; } public List<EnterpriseAccountDto> getAccountMappingResult(String accountSetId, int ruleId, String industryId) { //amk.IndustryId倒序排列可以保证在结果集中,同一个COA,本行业的匹配结果在通用关键字匹配结果之前出现 // 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"; 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; } public List<Map<String, Object>> selectAccounts(String industryId, String accountSetId, String organizationId, int iAcctProp, 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(); // todo 19/12/2018 待修改 String sql = "SELECT * FROM ( " + "SELECT CASE Is_Leaf WHEN 0 THEN '+' ELSE '' END IsEx,Code, Name, Full_Name, " + "CASE Direction WHEN 1 THEN N'借方' WHEN -1 THEN N'贷方' END Direction, " + "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 " + " 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"; List<Object> params = Lists.newArrayList(accountSetId, organizationId, industryId, industryId); if (iAcctProp != EnterpriseAccountConstant.InvalidId) { sql += "acct_prop = ?"; params.add(iAcctProp); } if (iIsMapped == AccountMappingConstant.Status.UNMAPPED.getCode()) { sql += "Std_Code IS NULL"; } else if (iIsMapped == AccountMappingConstant.Status.MAPPED.getCode()) { sql += "Std_Code IS NOT NULL"; } else if (iIsMapped == AccountMappingConstant.Status.DIRECTION_DIFFERENT.getCode()) { sql += "Std_Code IS NOT NULL AND Direction<>Std_Direction"; } else if (iIsMapped == AccountMappingConstant.Status.ACCOUNT_TYPE_DIFFERENT.getCode()) { sql += "Std_Code IS NOT NULL AND Is_Acct_Prop_Diff=1"; } if (iLevel != EnterpriseAccountConstant.InvalidId) { sql += "acct_level = ?"; params.add(iLevel); } sql += " ORDER BY code"; return jdbcTemplate.queryForList(sql, params.toArray()); } }