-- 这里的CREATE OR REPLACE FUNCTION 为固定写法： "public"."function_info_a1" 这个为函数名
CREATE OR REPLACE FUNCTION "public"."function_info_a1"(d1 varchar, d2 varchar, procuct varchar)
RETURNS SETOF "pg_catalog"."record" AS $BODY$
declare
rec record;
BEGIN
--创建临时表
CREATE TEMP table flagtable(product_id int,rq timestamp,doctype varchar,docname varchar,qc float,rk float,ck float,jc float) on commit drop;
--on commit drop表示提交后会删除
--插入数据
insert into flagtable(product_id,rq,doctype,docname,qc,rk,ck,jc)
--查找数据
select t1.id,d1::timestamp,'期初','',0,0,0,0 from product_product t1 left join product_template t0 on t1.product_tmpl_id=t0.id
where t0.active='t' and t1.active='t' and t0.categ_id=6;
--所有成品的开始日期之前结存（期初）
--更新数据
update flagtable t3 set qc=(SELECT
COALESCE(SUM(CASE WHEN location_id =get_warehouse_id('成品仓') THEN - 1 * COALESCE(product_qty,0) ELSE COALESCE(product_qty,0) END),0) qc
FROM stock_move ts
WHERE STATE = 'done' AND DATE< d1::timestamp
AND (location_id =get_warehouse_id('成品仓') OR location_dest_id =get_warehouse_id('成品仓')) and ts.product_id=t3.product_id );
--最初的期初和结存一样的
update flagtable set jc=qc;
--插入时间段内的数据
insert into flagtable(product_id,rq,doctype,docname,qc,rk,ck,jc)
select product_id,ts1.date,case when ts3.name='Internal Transfers' then '调拨单' when ts3.name is null then '盘点单' else ts3.name end,
case when ts2.name is null then ts1.name else ts2.name end,0,COALESCE (CASE WHEN location_dest_id =get_warehouse_id('成品仓') THEN product_qty ELSE 0 END,0),
COALESCE (CASE WHEN location_id =get_warehouse_id('成品仓') THEN product_qty ELSE 0 END,0),0
FROM stock_move ts1 left join stock_picking ts2 on ts1.picking_id=ts2.id left join stock_picking_type ts3 on ts2.picking_type_id=ts3.id
where ts1.STATE = 'done' AND ts1.DATE >=d1::timestamp and ts1.DATE <=d2::timestamp AND (ts1.location_id =get_warehouse_id('成品仓') OR ts1.location_dest_id =get_warehouse_id('成品仓'))
order by ts1.product_id;
--计算每条记录的期初
update flagtable t4 set qc=coalesce(case when qc=0 then (select sum(jc+rk-ck) from flagtable t5 where t5.product_id=t4.product_id and t5.rq<t4.rq) else qc end,0);
--计算每个结存
update flagtable set jc=(qc+rk-ck)
where jc=0;
for rec in select rq,name_template,th,spec,doctype,docname,qc ,rk,ck,jc from (select product_id,a0.rq rq,a1.name_template,(select khwl_code from product_custo_info where a2.id=product_tmpl_id order by id limit 1) th,a2.spec,a0.doctype,a0.docname ,a0.qc ,a0.rk,a0.ck,a0.jc
from flagtable a0 left join product_product a1 on a0.product_id=a1.id left join product_template a2 on a1.product_tmpl_id=a2.id
where (a0.qc<>0 or a0.rk<>0 or a0.ck<>0 or a0.jc<>0))t where (t.th like '%'||procuct||'%' or name_template like '%'||procuct||'%'or spec like '%'||procuct||'%' )
order by product_id,rq
loop
RETURN next rec;
end loop;
return;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100
ROWS 1000
;