在帆软报表中的函数写法:
多个语句查询:
CREATE OR REPLACE FUNCTION "public"."mrp_routing_workcenter_price1"(flagcompany_id int4, flagprice int4)
RETURNS SETOF "pg_catalog"."record" AS $BODY$
declare str varchar;
declare
rec record;
begin
--首先执行此语句,用于查询是否有新的报工工序产生
if exists(select lprocedure from mrp_production_report_line a where lstate=2 and not exists(select id from routing_workcenter_price b where a.lprocedure=b.workcenter_proid and a.company_id=b.company_id and a.lemployee=b.user_id)) then
begin
insert into routing_workcenter_price(workcenter_proid,company_id,user_id,cost,price)
select DISTINCT lprocedure,company_id,lemployee,0.0,0.0 from mrp_production_report_line a
where lstate=2 and not exists(select id from routing_workcenter_price b where a.lprocedure=b.workcenter_proid and a.company_id=b.company_id and a.lemployee=b.user_id);
end;
end if;
if flagprice=1 then
for rec in EXECUTE '
select t0.id ,t1.id as cpdm,t2.name as pm,t4.material,t4.cust_spec,t1.sequence,t1.name as gxm,t1.produce_description,t0.price,t0.cost,t5.login,t6.name,t0.remark from routing_workcenter_price t0
left join mrp_routing_workcenter t1 on t0.workcenter_proid=t1.id
left join mrp_routing t2 on t1.routing_id=t2.id
left join product_product t3 on t3.name_template=t2.name
left join product_template t4 on t4.id=t3.product_tmpl_id
left join res_users t5 on t5.id=t0.user_id
left join res_partner t6 on t5.partner_id=t6.id
where t0.company_id='||flagcompany_id||' and t4.active=''t'' and t3.active=''t'' and t0.price>0.0
order by t2.name,t1.sequence;'loop
RETURN next rec;
end loop;
return;
ELSEIF( flagprice=2) then
for rec in EXECUTE '
select t0.id ,t1.id as cpdm,t2.name as pm,t4.material,t4.cust_spec,t1.sequence,t1.name as gxm,t1.produce_description,t0.price,t0.cost,t5.login,t6.name,t0.remark from routing_workcenter_price t0
left join mrp_routing_workcenter t1 on t0.workcenter_proid=t1.id
left join mrp_routing t2 on t1.routing_id=t2.id
left join product_product t3 on t3.name_template=t2.name
left join product_template t4 on t4.id=t3.product_tmpl_id
left join res_users t5 on t5.id=t0.user_id
left join res_partner t6 on t5.partner_id=t6.id
where t0.company_id='||flagcompany_id||' and t4.active=''t'' and t3.active=''t'' and t0.price<=0.0
order by t2.name,t1.sequence;'loop
RETURN next rec;
end loop;
return;
ELSE
for rec in EXECUTE '
select t0.id ,t1.id as cpdm,t2.name as pm,t4.material,t4.cust_spec,t1.sequence,t1.name as gxm,t1.produce_description,t0.price,t0.cost,t5.login,t6.name,t0.remark from routing_workcenter_price t0
left join mrp_routing_workcenter t1 on t0.workcenter_proid=t1.id
left join mrp_routing t2 on t1.routing_id=t2.id
left join product_product t3 on t3.name_template=t2.name
left join product_template t4 on t4.id=t3.product_tmpl_id
left join res_users t5 on t5.id=t0.user_id
left join res_partner t6 on t5.partner_id=t6.id
where t0.company_id='||flagcompany_id||' and t4.active=''t'' and t3.active=''t''
order by t2.name,t1.sequence;'loop
RETURN next rec;
end loop;
return;
end if;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100
ROWS 1000
;