复杂查询与数据处理

20191203数据查询数据统计与数据格式处理

复杂查询

<select id="queryCTransInfoPayList" resultType="com.xingyi.bos.model.CTransInfoPayQueryResult">  
        SELECT  
        t1.pkno,t1.cuser,t1.ctime,t1.muser,t1.mtime,t1.trans_id,t1.pay_type,t1.pay_type_detail,t1.pay_platform,t1.payable_amount,t1.preferential_amount,  
        t1.final_amount,t1.pay_status,case t1.pay_status when '0' then '待支付' when '1' then '支付成功' when '2' then '支付失败' else '支付撤销' end payStatusName,t1.payment_reason,  
        case t1.card_pay_status when '0' then '否' else '是' end card_pay_status,t1.card_no,t1.customer_id,case t1.sell_type when '0' then '加油' else '非油' end sell_type,t1.ccb_pay_type,  
        case t1.pay_index when '1' then '按量' else '按金额' end pay_index, t2.field_text AS payTypeName,t3.field_text AS payPlatformName, t5.employee_no AS employeeNo  
        FROM c_transinfo_pay t1  
        LEFT JOIN s_dictionary t2 ON t2.field_value = t1.pay_type AND t2.field_name = 'pay_type' AND t2.table_name = 'c_transinfo_pay'  
        LEFT JOIN s_dictionary t3 ON t3.field_value = t1.pay_platform AND t3.field_name = 'pay_platform' AND t3.table_name = 'c_transinfo_pay'  
        LEFT JOIN c_transinfo t4 ON t1.sell_type = 0 AND t1.trans_id = t4.trans_id  
        LEFT JOIN b_employee t5 ON t4.employee_id = t5.pkno  
        <where>  
            <if test="payType != null and payType != ''">  
                and t1.pay_type = #{payType}  
            </if>  
            <if test="sellType != null and sellType != ''">  
                and t1.sell_type = #{sellType}  
            </if>  
            <if test="payPlatform != null and payPlatform != ''">  
                and t1.pay_platform = #{payPlatform}  
            </if>  
            <if test="payStatus != null and payStatus!=''">  
                and t1.pay_status = #{payStatus}  
            </if>  
            <if test="customerId != null and customerId != ''">  
                and t1.customer_id like "%"#{customerId}"%"  
            </if>  
            <if test="employeeNo != null and employeeNo != ''">  
                and t5.employee_no like "%"#{employeeNo}"%"  
            </if>  
        </where>  
        order by  
        <if test="sortList!=null and sortList!=''">  
            ${sortList},  
        </if>  
        t1.ctime desc  
    </select> 

统计查询

<select id="countAmountBySellType" parameterType="hashmap" resultType="hashmap">
        SELECT case temp.sell_type WHEN '0' THEN '加油' ELSE '非油' END AS sellType,IFNULL(SUM(final_amount),0) amount,temp.customer_id,temp.sell_type
        FROM (SELECT t.final_amount,t.customer_id,t.sell_type FROM c_transinfo_pay t WHERE t.pay_status = '1' AND t.customer_id is not null
        <where>
            <if test="bctime != null and bctime != ''">
                AND date_format(t.ctime, '%Y-%m-%d') >= date_format(#{bctime}, '%Y-%m-%d')
            </if>
            <if test="ectime != null and ectime != ''">
                and date_format(t.ctime, '%Y-%m-%d') &lt;= date_format(#{ectime}, '%Y-%m-%d')
            </if>
            <if test="customerId != null and customerId != ''">
                and t.customer_id = #{customerId}
            </if>
            <if test="sellType != null and sellType != ''">
                and t.sell_type = #{sellType}
            </if>
        </where>) temp
        GROUP BY
        temp.customer_id,temp.sell_type
        ORDER BY
        temp.sell_type
    </select>

将同项合并，处理成新的list后再返回给前端

public List<List<HashMap<String, Object>>> mergeMap(String merge, List<HashMap<String, Object>> dataList) {
        Map<String, List<HashMap<String, Object>>> mm = new HashMap<String, List<HashMap<String, Object>>>();
        for (HashMap<String, Object> temp : dataList) {
            if (mm.containsKey(temp.get(merge))) {
                mm.get(temp.get(merge)).add(temp);
            } else {
                List<HashMap<String, Object>> ll = new ArrayList<>();
                ll.add(temp);
                mm.put((String) temp.get(merge), ll);
            }
        }
        List<List<HashMap<String, Object>>> res = new ArrayList<>();
        for (Map.Entry<String, List<HashMap<String, Object>>> et : mm.entrySet()) {
            res.add(et.getValue());
        }
        return res;
    }

拿到数据后，根据customer_id为分组依据，相同的id合并为一个数组

public ResponseHashResult countAmountByPayPlatform(CommonQuery commonQuery) {
        if (commonQuery == null) {
            return new ResponseHashResult(new ResponseStatus(ResultCode.ILLEGALINPUT));
        }

        String condList = commonQuery.getCondList();
        String sortList = commonQuery.getSortList();
        HashMap<String, Object> queryMap = Maps.newHashMap();

        if (!StringUtils.isEmpty(condList)) {
            queryMap = JSON.parseObject(condList, HashMap.class);
        }
        if (!StringUtils.isEmpty(sortList)) {
            sortList = SortListUtil.parseSortList(sortList);
        }
        queryMap.put("sortList", sortList);

        List<HashMap<String, Object>> listPre = cTransinfoPayQueryMapper.countAmountByPayPlatform(queryMap);
        List<List<HashMap<String, Object>>> list = mergeMap("customer_id", listPre);
        Map<String, Object> map = Maps.newHashMap();

        map.put("list", list);

        return new ResponseHashResult(new ResponseStatus(ResultCode.SUCCESS), map);
    }