📄 sal_qry_saleplanofitemanalyse.pas
字号:
var
Frm_Sal_Qry_SalePlanOfItemAnalyse: TFrm_Sal_Qry_SalePlanOfItemAnalyse;
implementation
uses Sys_Global,Sal_Qry_SalePlanOfItemAnalyse_C;
{$R *.DFM}
procedure TFrm_Sal_Qry_SalePlanOfItemAnalyse.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
Year:string;
ItemCode1,ItemCode2:string;
begin
Application.ProcessMessages;
inherited;
Year:='0';
ItemCode1:='';
ItemCode2:='';
with TFrm_Sal_Qry_SalePlanOfItemAnalyse_C.Create(self) do
begin
try
AdoQry_tmp.Connection:=dbconnect;
showmodal;
if modalResult=mrok then
begin
Year:=edt_Year.text;
ItemCode1:=edt_DeptCode1.Text;
ItemCode2:=edt_DeptCode2.Text;
end;
finally
Frm_Sal_Qry_SalePlanOfItemAnalyse_C.Free;
Frm_Sal_Qry_SalePlanOfItemAnalyse_C:=nil;
end;
end;
sqltext:='select sa_ShipOrder.Createdate,sa_ShipOrderline.* '
+' into #sa_ShipOrder '
+' from sa_ShipOrderline '
+' join sa_ShipOrder on sa_ShipOrderline.Shipno=sa_ShipOrder.Shipno '
+' and datepArt(yy,createdate)='+Year+' and assess=1 '
+' where ItemCode between '+quotedstr(ItemCode1)+' and '+quotedstr(ItemCode2)
+' select sa_BackShipOrder.Createdate,sa_BackShipOrderline.* '
+' into #sa_BackShipOrder '
+' from sa_BackShipOrderline '
+' join sa_BackShipOrder on sa_BackShipOrderline.BackShipno=sa_BackShipOrder.BackShipno '
+' and datepArt(yy,createdate)='+Year+' and assess=1 '
+' where ItemCode between '+quotedstr(ItemCode1)+' and '+quotedstr(ItemCode2);
Executesql(AdoQry_tmp,sqltext,1);
sqltext:=' select PlanYear, '
+' Sal_SalePlan_Item.ItemCode, '
+' Item.ItemName, '
+' Sal_SalePlan_Item.ItemCode+'' ''+Item.ItemName as Itemflag, '
+' '
+' Plansum_Year, '
+' rationsum_Year, '
+' sum(isnull(#sa_ShipOrder.Shipqty,0)) '
+' -sum(isnull(#sa_BackShipOrder.Shipqty,0)) as realSaleqty, '
+' sum(isnull(#sa_ShipOrder.taxAmount,0)) '
+' -sum(isnull(#sa_BackShipOrder.taxAmount,0)) as realSaleAmount, '
+' sum(isnull(#sa_ShipOrder.Shipqty,0) ) '
+' -sum(isnull(#sa_BackShipOrder.Shipqty,0) ) '
+' -rationsum_Year as DiffrationAmount, '
+' case when rationsum_Year=0 then 0 '
+' else (sum(isnull(#sa_ShipOrder.Shipqty,0) ) '
+' -sum(isnull(#sa_BackShipOrder.Shipqty,0) ) '
+' )/rationsum_Year *100 end as rateration, '
+' '
+' sum(isnull(#sa_ShipOrder.taxAmount,0) ) '
+' -sum(isnull(#sa_BackShipOrder.taxAmount,0) ) '
+' -Plansum_Year as DiffAmount, '
+' case when Plansum_Year=0 then 0 '
+' else (sum(isnull(#sa_ShipOrder.taxAmount,0) ) '
+' -sum(isnull(#sa_BackShipOrder.taxAmount,0) ) '
+' )/Plansum_Year *100 end as ratePlan, '
+' '
+' Plansum_1, '
+' rationsum_1, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=1 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=1 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount1, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=1 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=1 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty1, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=1 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=1 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' -Plansum_1 as DiffAmount1, '
+' case when Plansum_1=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=1 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=1 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_1 *100 end as ratePlan1, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=1 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=1 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' -rationsum_1 as DiffrationAmount1, '
+' case when rationsum_1=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=1 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=1 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_1 *100 end as rateration1, '
+' '
+' Plansum_2, '
+' rationsum_2, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=2 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=2 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount2, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=2 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=2 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty2, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=2 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=2 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' -Plansum_2 as DiffAmount2, '
+' case when Plansum_2=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=2 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=2 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_2 *100 end as ratePlan2, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=2 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=2 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' -rationsum_2 as DiffrationAmount2, '
+' case when rationsum_2=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=2 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=2 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) '
+' )/rationsum_2 *100 end as rateration2, '
+' '
+' Plansum_3, '
+' rationsum_3, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=3 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=3 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as realSaleAmount3, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=3 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=3 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as realSaleqty3, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=3 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=3 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' -Plansum_3 as DiffAmount3, '
+' case when Plansum_3=0 then 0 '
+' else (sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=3 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end ) '
+' -sum(case when datepArt(mm,#sa_BackShipOrder.Createdate)=3 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) '
+' )/Plansum_3 *100 end as ratePlan3, '
+' sum(case when datepArt(mm,#sa_ShipOrder.Createdate)=3 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end ) '
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -