被服统计(裘程磊)
## 需求
![image.png](https://cos.easydoc.net/82367260/files/l39rw04a.png)
省局:统计全省监狱被服库存金额、采购金额、发放金额
监狱:统计本监狱被服库存金额、采购金额、发放金额
![image.png](https://cos.easydoc.net/82367260/files/l39rxb7l.png)
省局:查看全省各个监狱被服库存、采购、发放金额详情
## 数据来源
|库|表名|说明|
|-|-|-|
|ds_cloth|t_cl_info|被服基础数据|
|ds_cloth|t_cl_inventory|被服库存表|
|ds_cloth|t_cl_inventory_record|出入库记录|
## sql语句
-- 库存金额
SELECT ci.JYDW jydw,cl.cl_name,sum(cl.cl_price * ci.clinv_num_new) amount
FROM t_cl_inventory ci LEFT JOIN t_cl_info cl on cl.cl_uuid = ci.clinv_cl_uuid
group by ci.JYDW
-- 出入库金额
select cir.JYDW jydw,cir.clinvr_operate clinvrOperate,SUM(ci.cl_price * clinvr_num) amount
FROM t_cl_inventory_record cir LEFT JOIN t_cl_info ci on cir.clinvr_cl_uuid = ci.cl_uuid
where cir.is_delete = 0 and clinvr_cdate <= '2022-05-01 00:00:00'
GROUP BY cir.JYDW,cir.clinvr_operate