📄 inv_opmonthsumqry_c.pas
字号:
unit Inv_OpMonthSumQry_C;
Interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Base_Condition, Db, AdODB, StdCtrls, Mask, ExtEdit;
Type
TFrm_Inv_OpMonthSumQry_C = Class(TFrm_Base_Condition)
Label2: TLabel;
MEdt_Month: TMaskEdit;
CkBx_ItemClass: TCheckBox;
procedure btn_okClick(Sender: TObject);
procedure MonthCheck(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Frm_Inv_OpMonthSumQry_C: TFrm_Inv_OpMonthSumQry_C;
implementation
uses Sys_Global,Inv_Global;
{$R *.DFM}
procedure TFrm_Inv_OpMonthSumQry_C.btn_okClick(Sender: TObject);
var
i:integer;
zzz:string;
begin
inherited;
zzz:='zzzzzzzzzzzzzzzzzzzz';
// 按月份、物料代码汇总 数量、金额，再计算出单价（当数量<>0时，否则单价=0）
if CkBx_ItemClass.Checked then
begin
{
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:=' select ItemCode,ItemName from Item '
+' where ClassCode Is Null or ClassCode=''''';
AdoQry_Tmp.open;
if not AdoQry_Tmp.eof then
begin
DispInfo('物料：'+AdoQry_Tmp.fieldbyname('ItemCode').asstring+' '+
AdoQry_Tmp.fieldbyname('ItemName').asstring+' 没有进行存货分类定义！',1);
exit;
end;
}
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Delete #InvOpMonthSum';
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #InvOpMonthSum'
+' Select OpMonthSum.ItemCode'
+',Item.ItemName'
+',OpMonthSum.ItemCode+'' ''+Item.ItemName As ItemCodeName,0 flag'
+','''''
+',Item.ClassCode'
+',0'
+',Uom.UomName'
+',OpMonthSum.InvLmAmount'
+',OpMonthSum.InvInAmount'
+',OpMonthSum.InvOutAmount'
+',OpMonthSum.InvtzAmount'
+',OpMonthSum.InvBlncAmount'
+',OpMonthSum.InvLmQty'
+',OpMonthSum.InvInQty'
+',OpMonthSum.InvOutQty'
+',OpMonthSum.InvtzQty'
+',OpMonthSum.InvBlncQty'
+',OpMonthSum.InvLmPrice'
+',OpMonthSum.InvInPrice'
+',OpMonthSum.InvOutPrice'
+',OpMonthSum.InvBlncPrice'
+' From OpMonthSum Join Item On OpMonthSum.ItemCode=Item.ItemCode'
+' and Item.ClassCode is not null and Item.ClassCode<>'''''
+' Join Uom On Item.UomCode=Uom.UomCode'
+' Where OpMonthSum.InvMonth='''+MEdt_Month.Text+'''';
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #InvOpMonthSum'
+' Select #InvOpMonthSum.ClassCode'
+',ItemClass.ClassName'
+',#InvOpMonthSum.ClassCode+'' ''+ItemClass.ClassName+'' 合计'' As ClassCodeName,1 flag'
+','''''
+',#InvOpMonthSum.ClassCode'
+',1'
+','''''
+',Sum(#InvOpMonthSum.InvLmAmount)'
+',Sum(#InvOpMonthSum.InvInAmount)'
+',Sum(#InvOpMonthSum.InvOutAmount)'
+',Sum(#InvOpMonthSum.InvtzAmount)'
+',Sum(#InvOpMonthSum.InvBlncAmount)'
+',Sum(#InvOpMonthSum.InvLmQty)'
+',Sum(#InvOpMonthSum.InvInQty)'
+',Sum(#InvOpMonthSum.InvOutQty)'
+',Sum(#InvOpMonthSum.InvtzQty)'
+',Sum(#InvOpMonthSum.InvBlncQty)'
+',case when Sum(#InvOpMonthSum.InvLmQty)<>0 then '
+' Sum(#InvOpMonthSum.InvLmAmount)/Sum(#InvOpMonthSum.InvLmQty) else 0 end '
+',case when Sum(#InvOpMonthSum.InvInQty)<>0 then '
+' Sum(#InvOpMonthSum.InvInAmount)/Sum(#InvOpMonthSum.InvInQty) else 0 end '
+',case when Sum(#InvOpMonthSum.InvOutQty)<>0 then '
+' Sum(#InvOpMonthSum.InvOutAmount)/Sum(#InvOpMonthSum.InvOutQty) else 0 end '
+',case when Sum(#InvOpMonthSum.InvBlncQty)<>0 then '
+' Sum(#InvOpMonthSum.InvBlncAmount)/Sum(#InvOpMonthSum.InvBlncQty) else 0 end '
+' From #InvOpMonthSum Join ItemClass On #InvOpMonthSum.ClassCode=ItemClass.ClassCode'
+' Where #InvOpMonthSum.OrderInt=0'
+' Group By #InvOpMonthSum.ClassCode,ItemClass.ClassName';
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Select ItemClass.PClassCode'
+' From ItemClass'
+' Where ItemClass.PClassCode<>'''''
+' And ItemClass.ClassCode In'
+' (Select #InvOpMonthSum.ClassCode'
+' From #InvOpMonthSum'
+' Where #InvOpMonthSum.OrderInt=1)';
AdoQry_Tmp.Open;
i:=2;
while not AdoQry_Tmp.IsEmpty do
begin
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #InvOpMonthSum'
+' Select ItemClass.PClassCode'
+',p.ClassName'
+',ItemClass.PClassCode+'' ''+p.ClassName+'' 合计'' As ClassCodeName,1 flag'
+','''''
+',ItemClass.PClassCode'
+','+IntToStr(i)
+','''''
+',Sum(#InvOpMonthSum.InvLmAmount)'
+',Sum(#InvOpMonthSum.InvInAmount)'
+',Sum(#InvOpMonthSum.InvOutAmount)'
+',Sum(#InvOpMonthSum.InvtzAmount)'
+',Sum(#InvOpMonthSum.InvBlncAmount)'
+',Sum(#InvOpMonthSum.InvLmQty)'
+',Sum(#InvOpMonthSum.InvInQty)'
+',Sum(#InvOpMonthSum.InvOutQty)'
+',Sum(#InvOpMonthSum.InvtzQty)'
+',Sum(#InvOpMonthSum.InvBlncQty)'
+',Sum(#InvOpMonthSum.InvLmPrice)'
+',Sum(#InvOpMonthSum.InvInPrice)'
+',Sum(#InvOpMonthSum.InvOutPrice)'
+',Sum(#InvOpMonthSum.InvBlncPrice)'
+' From #InvOpMonthSum Join ItemClass On #InvOpMonthSum.ClassCode=ItemClass.ClassCode'
+' Join ItemClass p On ItemClass.PClassCode=p.ClassCode'
+' Where #InvOpMonthSum.OrderInt='+IntToStr(i-1)
+' and ItemClass.PClassCode<>'''' and ItemClass.PClassCode is not null'
+' Group By ItemClass.PClassCode,p.ClassName';
AdoQry_Tmp.ExecSQL;
{
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Update #InvOpMonthSum Set'
+' #InvOpMonthSum.ClassCodeOrder=ItemClass.PClassCode+#InvOpMonthSum.ClassCodeOrder'
+' From #InvOpMonthSum Join ItemClass On #InvOpMonthSum.ClassCode=ItemClass.ClassCode';
AdoQry_Tmp.ExecSQL;
}
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Select ItemClass.PClassCode'
+' From ItemClass'
+' Where ItemClass.PClassCode<>'''' and ItemClass.pClassCode is not null'
+' And ItemClass.ClassCode In'
+' (Select #InvOpMonthSum.ClassCode'
+' From #InvOpMonthSum'
+' Where #InvOpMonthSum.OrderInt='+IntToStr(i)+')';
AdoQry_Tmp.Open;
Inc(i);
end;
i:=1;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='update #InvOpMonthSum set ClassCodeOrder=ClassCode';
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Select ItemClass.PClassCode'
+' From ItemClass'
+' Where ItemClass.PClassCode<>'''' and ItemClass.pClassCode is not null'
+' And ItemClass.ClassCode In'
+' (Select #InvOpMonthSum.ClassCode'
+' From #InvOpMonthSum)';
AdoQry_Tmp.open;
while not AdoQry_Tmp.IsEmpty do
begin
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Update #InvOpMonthSum Set'
+' #InvOpMonthSum.ClassCodeOrder=#InvOpMonthSum.ClassCodeOrder+left('+quotedstr(zzz)+',Orderint)'
+' From ItemClass where #InvOpMonthSum.ClassCode=ItemClass.ClassCode '
+' and (ItemClass.pClassCode='''' or ItemClass.pClassCode is null) '
+' and (#InvOpMonthSum.ClassCode<>'''' or #InvOpMonthSum.ClassCode is not null)';
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Update #InvOpMonthSum Set'
+' #InvOpMonthSum.ClassCodeOrder=ItemClass.PClassCode+left('+quotedstr(zzz)+',1)+#InvOpMonthSum.ClassCodeOrder+left('+quotedstr(zzz)+',Orderint)'
+' From ItemClass where #InvOpMonthSum.ClassCode=ItemClass.ClassCode'
+' and ItemClass.pClassCode<>'''' and ItemClass.pClassCode is not null '
+' and #InvOpMonthSum.ClassCode<>'''' and #InvOpMonthSum.ClassCode is not null';
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Update #InvOpMonthSum Set'
+' #InvOpMonthSum.ClassCode=ItemClass.PClassCode '
+' From ItemClass where #InvOpMonthSum.ClassCode=ItemClass.ClassCode ';
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Select ItemClass.PClassCode'
+' From ItemClass'
+' Where ItemClass.PClassCode<>'''' and ItemClass.PClassCode is not null '
+' And ItemClass.ClassCode In'
+' (Select #InvOpMonthSum.ClassCode'
+' From #InvOpMonthSum where ClassCode<>'''' and ClassCode is not null)';
AdoQry_Tmp.open;
end;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #InvOpMonthSum'
+' Select OpMonthSum.ItemCode'
+',Item.ItemName'
+',OpMonthSum.ItemCode+'' ''+Item.ItemName As ItemCodeName,0 flag'
+',''zzzzzzzzzzzzzzz'''
+',Item.ClassCode'
+',0'
+',Uom.UomName'
+',OpMonthSum.InvLmAmount'
+',OpMonthSum.InvInAmount'
+',OpMonthSum.InvOutAmount'
+',OpMonthSum.InvtzAmount'
+',OpMonthSum.InvBlncAmount'
+',OpMonthSum.InvLmQty'
+',OpMonthSum.InvInQty'
+',OpMonthSum.InvOutQty'
+',OpMonthSum.InvtzQty'
+',OpMonthSum.InvBlncQty'
+',OpMonthSum.InvLmPrice'
+',OpMonthSum.InvInPrice'
+',OpMonthSum.InvOutPrice'
+',OpMonthSum.InvBlncPrice'
+' From OpMonthSum Join Item On OpMonthSum.ItemCode=Item.ItemCode'
+' and (Item.ClassCode is null or Item.ClassCode='''')'
+' Join Uom On Item.UomCode=Uom.UomCode'
+' Where OpMonthSum.InvMonth='''+MEdt_Month.Text+'''';
AdoQry_Tmp.ExecSQL;
Condition:='Select ClassCodeOrder'
+',ItemCode'
+',ItemName'
+',ItemCodeName,flag'
+',UomName'
+',InvLMQty'
+',InvLMPrice'
+',InvLMAmount'
+',InvInQty'
+',InvInPrice'
+',InvInAmount'
+',InvOutQty'
+',InvOutPrice'
+',InvOutAmount'
+',InvtzQty'
+',InvtzAmount'
+',InvBlncQty'
+',InvBlncPrice'
+',InvBlncAmount'
+' From #InvOpMonthSum'
+' where (InvLMQty<>0 or InvLMAmount<>0 or InvInQty<>0 or InvInAmount<>0 '
+' or InvOutQty<>0 or InvOutAmount<>0 or InvBlncQty<>0 or InvBlncAmount<>0)'
+' Order By ClassCodeOrder';
end
else
begin
Condition:='select * from(Select OpMonthSum.ItemCode'
+',Item.ItemName'
+',OpMonthSum.ItemCode+'' ''+Item.ItemName As ItemCodeName,0 flag'
+',Uom.UomName'
+',Sum(OpMonthSum.InvLMQty) As InvLMQty'
+',case when Sum(OpMonthSum.InvLMQty)<>0 '
+' then Sum(OpMonthSum.InvLMAmount)/Sum(OpMonthSum.InvLMQty) end As InvLMPrice'
+',Sum(OpMonthSum.InvLMAmount) As InvLMAmount'
+',Sum(OpMonthSum.InvInQty) As InvInQty'
+',case when Sum(OpMonthSum.InvInQty)<>0 '
+' then Sum(OpMonthSum.InvInAmount)/Sum(OpMonthSum.InvInQty) end As InvInPrice'
+',Sum(OpMonthSum.InvInAmount) As InvInAmount'
+',Sum(OpMonthSum.InvOutQty) As InvOutQty'
+',case when Sum(OpMonthSum.InvOutQty)<>0 '
+' then Sum(OpMonthSum.InvOutAmount)/Sum(OpMonthSum.InvOutQty) end As InvOutPrice'
+',Sum(OpMonthSum.InvOutAmount) As InvOutAmount'
+',Sum(OpMonthSum.InvtzQty) As InvtzQty'
+',Sum(OpMonthSum.InvtzAmount) As InvtzAmount'
+',Sum(OpMonthSum.InvBlncQty) As InvBlncQty'
+',case when Sum(OpMonthSum.InvBlncQty)<>0 '
+' then Sum(OpMonthSum.InvBlncAmount)/Sum(OpMonthSum.InvBlncQty) end As InvBlncPrice'
+',Sum(OpMonthSum.InvBlncAmount) As InvBlncAmount'
+' From OpMonthSum Join Item On OpMonthSum.ItemCode=Item.ItemCode'
+' Join Uom On Item.UomCode=Uom.UomCode'
+' where (InvLMQty<>0 or InvLMAmount<>0 or InvInQty<>0 or InvInAmount<>0 or Invtzqty<>0 '
+' or InvtzAmount<>0 or InvOutQty<>0 or InvOutAmount<>0 or InvBlncQty<>0 or InvBlncAmount<>0) '
+' and OpMonthSum.InvMonth='''+MEdt_Month.Text+''''
+' Group By OpMonthSum.ItemCode,Item.ItemName,Uom.UomName ) a Order by ItemCode' ;
// +' where Invlmqty<>0 or InvInqty<>0 or Invoutqty<>0 or Invblncqty<>0 ';
end;
ConditionHint:='月份:'+MEdt_Month.Text;
ModalResult:=mrOk;
end;
procedure TFrm_Inv_OpMonthSumQry_C.MonthCheck(Sender: TObject);
begin
inherited;
InvDataChangeCheck(AdoQry_Tmp,TEdit(Sender).Text);
end;
procedure TFrm_Inv_OpMonthSumQry_C.FormCreate(Sender: TObject);
begin
inherited;
MEdt_Month.Text:=FormatDateTime('yyyy.mm',Now);
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -