| | |
| | | <!--获取指定时间段水卡使用情况记录数量--> |
| | | <select id="getCardUsagesCount" resultType="com.dy.pipIrrGlobal.voSt.VoCardUsage"> |
| | | SELECT |
| | | cli.name AS clientName, |
| | | card.cardNum, |
| | | (SELECT ROUND(SUM(rch.amount),2) FROM se_recharge_history rch WHERE rch.cardId = card.id AND rch.operate_valid = 2 AND rch.operateDt BETWEEN #{timeStart} AND #{timeStop}) AS totalRecharge, |
| | | (SELECT ROUND(SUM(his.cl_this_money),2) FROM rm_open_close_valve_history his WHERE his.cl_ic_card_no = card.cardNum AND his.cl_dt BETWEEN #{timeStart} AND #{timeStop}) AS totalConsumption, |
| | | ROUND(card.money,2) AS balance |
| | | FROM se_client_card card |
| | | INNER JOIN se_client cli ON cli.id = card.clientId |
| | | <!-- INNER JOIN se_recharge_history rch ON rch.cardId = card.id--> |
| | | card.id as cardId, |
| | | cli.name AS clientName, |
| | | card.cardNum, |
| | | a.cl_ic_card_no, |
| | | card.money AS balance, |
| | | a.totalConsumption, |
| | | b.totalRecharge |
| | | FROM se_client_card card |
| | | INNER JOIN se_client cli ON cli.id = card.clientId |
| | | LEFT JOIN ( |
| | | SELECT his.cl_ic_card_no, SUM(his.cl_this_money) AS totalConsumption |
| | | FROM rm_open_close_valve_history his |
| | | WHERE his.cl_dt BETWEEN #{timeStart} AND #{timeStop} |
| | | GROUP BY his.cl_ic_card_no |
| | | ) a ON a.cl_ic_card_no = CAST(card.cardNum AS CHAR) |
| | | LEFT JOIN ( |
| | | SELECT rch.cardId,ROUND(SUM(rch.amount),2) as totalRecharge |
| | | FROM se_recharge_history rch |
| | | WHERE rch.operate_valid = 2 AND rch.operateDt BETWEEN #{timeStart} AND #{timeStop} |
| | | GROUP BY rch.cardId |
| | | ) b ON b.cardId = card.id |
| | | <where> |
| | | <if test="clientName != null and clientName != ''"> |
| | | AND cli.name like CONCAT('%', #{clientName}, '%') |
| | |
| | | <!--获取指定时间段水卡使用情况:充值合计、消费合计、余额--> |
| | | <select id="getCardUsages" resultType="com.dy.pipIrrGlobal.voSt.VoCardUsage"> |
| | | SELECT |
| | | cli.name AS clientName, |
| | | card.cardNum, |
| | | (SELECT ROUND(SUM(rch.amount),2) FROM se_recharge_history rch WHERE rch.cardId = card.id AND rch.operate_valid = 2 AND rch.operateDt BETWEEN #{timeStart} AND #{timeStop}) AS totalRecharge, |
| | | (SELECT ROUND(SUM(his.cl_this_money),2) FROM rm_open_close_valve_history his WHERE his.cl_ic_card_no = card.cardNum AND his.cl_dt BETWEEN #{timeStart} AND #{timeStop}) AS totalConsumption, |
| | | ROUND(card.money,2) AS balance |
| | | FROM se_client_card card |
| | | INNER JOIN se_client cli ON cli.id = card.clientId |
| | | <!-- INNER JOIN se_recharge_history rch ON rch.cardId = card.id--> |
| | | card.id as cardId, |
| | | cli.name AS clientName, |
| | | card.cardNum, |
| | | a.cl_ic_card_no, |
| | | card.money AS balance, |
| | | a.totalConsumption, |
| | | b.totalRecharge |
| | | FROM se_client_card card |
| | | INNER JOIN se_client cli ON cli.id = card.clientId |
| | | LEFT JOIN ( |
| | | SELECT his.cl_ic_card_no, SUM(his.cl_this_money) AS totalConsumption |
| | | FROM rm_open_close_valve_history his |
| | | WHERE his.cl_dt BETWEEN #{timeStart} AND #{timeStop} |
| | | GROUP BY his.cl_ic_card_no |
| | | ) a ON a.cl_ic_card_no = CAST(card.cardNum AS CHAR) |
| | | LEFT JOIN ( |
| | | SELECT rch.cardId,ROUND(SUM(rch.amount),2) as totalRecharge |
| | | FROM se_recharge_history rch |
| | | WHERE rch.operate_valid = 2 AND rch.operateDt BETWEEN #{timeStart} AND #{timeStop} |
| | | GROUP BY rch.cardId |
| | | ) b ON b.cardId = card.id |
| | | <where> |
| | | <if test="clientName != null and clientName != ''"> |
| | | AND cli.name like CONCAT('%', #{clientName}, '%') |