📄 inv_monthsumqry_c.pas
字号:
unit Inv_MonthSumQry_C;
Interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
Base_Condition, Db, AdODB, StdCtrls, Mask;
Type
TFrm_Inv_MonthSumQry_C = Class(TFrm_Base_Condition)
Label1: TLabel;
CmBx_Warehouse: TComboBox;
Label2: TLabel;
MEdt_Month: TMaskEdit;
CkBx_ItemClass: TCheckBox;
procedure btn_okClick(Sender: TObject);
procedure FormActivate(Sender: TObject);
procedure MonthCheck(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Frm_Inv_MonthSumQry_C: TFrm_Inv_MonthSumQry_C;
implementation
uses Sys_Global,Inv_Global;
{$R *.DFM}
procedure TFrm_Inv_MonthSumQry_C.btn_okClick(Sender: TObject);
var
i:Integer;
allwhCode,zzz:string;
begin
inherited;
zzz:='zzzzzzzzzzzzzzzzzzzz';
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;
}
if Trim(CmBx_Warehouse.Text)='全部仓库' then
allwhCode:=' where InvMonthSum.InvMonth='''+MEdt_Month.Text+''''
else
allwhCode:=' Where InvMonthSum.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
+' And InvMonthSum.InvMonth='''+MEdt_Month.Text+'''';
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Delete #InvMonthSum';
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #InvMonthSum'
+' Select InvMonthSum.ItemCode'
+',Item.ItemName'
+',InvMonthSum.ItemCode+'' ''+Item.ItemName As ItemCodeName,0'
+','''''
+',Item.ClassCode'
+',0'
+',Uom.UomName'
+',Sum(InvMonthSum.InvLmAmount)'
+',Sum(InvMonthSum.InvInAmount)'
+',Sum(InvMonthSum.InvOutAmount)'
+',0'
+',Sum(InvMonthSum.InvBlncAmount)'
+',Sum(InvMonthSum.InvLmQty)'
+',Sum(InvMonthSum.InvInQty)'
+',Sum(InvMonthSum.InvOutQty)'
+',0'
+',Sum(InvMonthSum.InvBlncQty)'
+',case when Sum(InvMonthSum.InvLmQty)<>0 then '
+' Sum(InvMonthSum.InvLmAmount)/Sum(InvMonthSum.InvLmQty) else 0 end '
+',case when Sum(InvMonthSum.InvInQty)<>0 then '
+' Sum(InvMonthSum.InvInAmount)/Sum(InvMonthSum.InvInQty) else 0 end '
+',case when Sum(InvMonthSum.InvOutQty)<>0 then '
+' Sum(InvMonthSum.InvOutAmount)/Sum(InvMonthSum.InvOutQty) else 0 end '
+',case when Sum(InvMonthSum.InvBlncQty)<>0 then '
+' Sum(InvMonthSum.InvBlncAmount)/Sum(InvMonthSum.InvBlncQty) else 0 end '
+' From InvMonthSum Join Item On InvMonthSum.ItemCode=Item.ItemCode'
+' and Item.ClassCode is not null and Item.ClassCode<>'''''
+' Join Uom On Item.UomCode=Uom.UomCode'
+allwhCode
+' group by InvMonthSum.ItemCode,Item.ItemName,Item.ClassCode,Uom.UomName Order by InvMonthSum.ItemCode ';;
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #InvMonthSum'
+' Select #InvMonthSum.ClassCode'
+',ItemClass.ClassName'
+',#InvMonthSum.ClassCode+'' ''+ItemClass.ClassName+'' 合计'' As ClassCodeName,1'
+','''''
+',#InvMonthSum.ClassCode'
+',1'
+','''''
+',Sum(#InvMonthSum.InvLmAmount)'
+',Sum(#InvMonthSum.InvInAmount)'
+',Sum(#InvMonthSum.InvOutAmount)'
+',0'
+',Sum(#InvMonthSum.InvBlncAmount)'
+',Sum(#InvMonthSum.InvLmQty)'
+',Sum(#InvMonthSum.InvInQty)'
+',Sum(#InvMonthSum.InvOutQty)'
+',0'
+',Sum(#InvMonthSum.InvBlncQty)'
+',case when Sum(#InvMonthSum.InvLmQty)<>0 then '
+' Sum(#InvMonthSum.InvLmAmount)/Sum(#InvMonthSum.InvLmQty) else 0 end '
+',case when Sum(#InvMonthSum.InvInQty)<>0 then '
+' Sum(#InvMonthSum.InvInAmount)/Sum(#InvMonthSum.InvInQty) else 0 end '
+',case when Sum(#InvMonthSum.InvOutQty)<>0 then '
+' Sum(#InvMonthSum.InvOutAmount)/Sum(#InvMonthSum.InvOutQty) else 0 end'
+',case when Sum(#InvMonthSum.InvBlncQty)<>0 then '
+' Sum(#InvMonthSum.InvBlncAmount)/Sum(#InvMonthSum.InvBlncQty) else 0 end '
+' From #InvMonthSum Join ItemClass On #InvMonthSum.ClassCode=ItemClass.ClassCode'
+' Where #InvMonthSum.OrderInt=0 '
+' Group By #InvMonthSum.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 #InvMonthSum.ClassCode'
+' From #InvMonthSum'
+' Where #InvMonthSum.OrderInt=1)';
AdoQry_Tmp.Open;
i:=2;
while not AdoQry_Tmp.IsEmpty do
begin
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='delete from #InvMonthSum where ItemCodeName in '
+' (Select ItemClass.PClassCode+'' ''+p.ClassName+'' 合计'' As ClassCodeName '
+' from ItemClass join #InvMonthSum on #InvMonthSum.ClassCode=ItemClass.ClassCode '
+' and #InvMonthSum.OrderInt='+IntToStr(i-1)
+' Join ItemClass p On ItemClass.PClassCode=p.ClassCode '
+' where isnull(ItemClass.PClassCode,'''')<>'''' ) ' ;
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #InvMonthSum'
+' Select ItemClass.PClassCode'
+',p.ClassName'
+',ItemClass.PClassCode+'' ''+p.ClassName+'' 合计'' As ClassCodeName,1'
+','''''
+',ItemClass.PClassCode'
+','+IntToStr(i)
+','''''
+',Sum(#InvMonthSum.InvLmAmount)'
+',Sum(#InvMonthSum.InvInAmount)'
+',Sum(#InvMonthSum.InvOutAmount)'
+',0'
+',Sum(#InvMonthSum.InvBlncAmount)'
+',Sum(#InvMonthSum.InvLmQty)'
+',Sum(#InvMonthSum.InvInQty)'
+',Sum(#InvMonthSum.InvOutQty)'
+',0'
+',Sum(#InvMonthSum.InvBlncQty)'
+',case when Sum(#InvMonthSum.InvLmQty)<>0 then '
+' Sum(#InvMonthSum.InvLmAmount)/Sum(#InvMonthSum.InvLmQty) else 0 end '
+',case when Sum(#InvMonthSum.InvInQty)<>0 then '
+' Sum(#InvMonthSum.InvInAmount)/Sum(#InvMonthSum.InvInQty) else 0 end '
+',case when Sum(#InvMonthSum.InvOutQty)<>0 then '
+' Sum(#InvMonthSum.InvOutAmount)/Sum(#InvMonthSum.InvOutQty) else 0 end '
+',case when Sum(#InvMonthSum.InvBlncQty)<>0 then '
+' Sum(#InvMonthSum.InvBlncAmount)/Sum(#InvMonthSum.InvBlncQty) else 0 end'
+' From #InvMonthSum Join ItemClass On #InvMonthSum.ClassCode=ItemClass.ClassCode'
+' Join ItemClass p On ItemClass.PClassCode=p.ClassCode'
+' Where #InvMonthSum.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 #InvMonthSum Set'
+' #InvMonthSum.ClassCodeOrder=ItemClass.PClassCode+#InvMonthSum.ClassCodeOrder'
+' From #InvMonthSum Join ItemClass On #InvMonthSum.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 #InvMonthSum.ClassCode'
+' From #InvMonthSum'
+' Where #InvMonthSum.OrderInt='+IntToStr(i)+')';
AdoQry_Tmp.Open;
Inc(i);
end;
i:=1;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='update #InvMonthSum 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 #InvMonthSum.ClassCode'
+' From #InvMonthSum)';
AdoQry_Tmp.open;
while not AdoQry_Tmp.IsEmpty do
begin
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Update #InvMonthSum Set'
+' #InvMonthSum.ClassCodeOrder=#InvMonthSum.ClassCodeOrder+left('+quotedstr(zzz)+',Orderint)'
+' From ItemClass where #InvMonthSum.ClassCode=ItemClass.ClassCode '
+' and (ItemClass.pClassCode='''' or ItemClass.pClassCode is null) '
+' and (#InvMonthSum.ClassCode<>'''' or #InvMonthSum.ClassCode is not null)';
AdoQry_Tmp.ExecSQL;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Update #InvMonthSum Set'
+' #InvMonthSum.ClassCodeOrder=ItemClass.PClassCode+left('+quotedstr(zzz)+',1)+#InvMonthSum.ClassCodeOrder+left('+quotedstr(zzz)+',Orderint)'
+' From ItemClass where #InvMonthSum.ClassCode=ItemClass.ClassCode '
+' and ItemClass.pClassCode<>'''' and ItemClass.pClassCode is not null'
+' and #InvMonthSum.ClassCode<>'''' and #InvMonthSum.ClassCode is not null';
AdoQry_Tmp.ExecSQL;
{
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='drop table aaaa'
+' select * into aaaa From #InvMonthSum ';
AdoQry_Tmp.execsql;
}
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Update #InvMonthSum Set'
+' #InvMonthSum.ClassCode=ItemClass.PClassCode '
+' From ItemClass where #InvMonthSum.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 #InvMonthSum.ClassCode'
+' From #InvMonthSum where ClassCode<>'''' and ClassCode is not null )';
AdoQry_Tmp.open;
end;
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='Insert #InvMonthSum'
+' Select InvMonthSum.ItemCode'
+',Item.ItemName'
+',InvMonthSum.ItemCode+'' ''+Item.ItemName As ItemCodeName,0'
+',''zzzzzzzzzzzzzzz'''
+',Item.ClassCode'
+',0'
+',Uom.UomName'
+',Sum(InvMonthSum.InvLmAmount)'
+',Sum(InvMonthSum.InvInAmount)'
+',Sum(InvMonthSum.InvOutAmount)'
+',0'
+',Sum(InvMonthSum.InvBlncAmount)'
+',Sum(InvMonthSum.InvLmQty)'
+',Sum(InvMonthSum.InvInQty)'
+',Sum(InvMonthSum.InvOutQty)'
+',0'
+',Sum(InvMonthSum.InvBlncQty)'
+',case when Sum(InvMonthSum.InvLmQty)<>0 then '
+' Sum(InvMonthSum.InvLmAmount)/Sum(InvMonthSum.InvLmQty) else 0 end '
+',case when Sum(InvMonthSum.InvInQty)<>0 then '
+' Sum(InvMonthSum.InvInAmount)/Sum(InvMonthSum.InvInQty) else 0 end '
+',case when Sum(InvMonthSum.InvOutQty)<>0 then '
+' Sum(InvMonthSum.InvOutAmount)/Sum(InvMonthSum.InvOutQty) else 0 end '
+',case when Sum(InvMonthSum.InvBlncQty)<>0 then '
+' Sum(InvMonthSum.InvBlncAmount)/Sum(InvMonthSum.InvBlncQty) else 0 end'
+' From InvMonthSum Join Item On InvMonthSum.ItemCode=Item.ItemCode'
+' and (Item.ClassCode is null or Item.ClassCode='''')'
+' Join Uom On Item.UomCode=Uom.UomCode'
+allwhCode
+' group by InvMonthSum.ItemCode,Item.ItemName,Item.ClassCode,Uom.UomName Order by InvMonthSum.ItemCode ';
AdoQry_Tmp.ExecSQL;
{
AdoQry_Tmp.Close;
AdoQry_Tmp.SQL.Text:='drop table aaaa'
+' select * into aaaa From #InvMonthSum ';
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 #InvMonthSum'
+' 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,ItemCode ';
end
else
begin
Condition:='Select InvMonthSum.ItemCode'
+',Item.ItemName'
+',InvMonthSum.ItemCode+'' ''+Item.ItemName As ItemCodeName,0 flag '
+',Uom.UomName'
+',InvMonthSum.InvLMQty'
+',InvMonthSum.InvLMPrice'
+',InvMonthSum.InvLMAmount'
+',InvMonthSum.InvInQty'
+',InvMonthSum.InvInPrice'
+',InvMonthSum.InvInAmount'
+',InvMonthSum.InvOutQty'
+',InvMonthSum.InvOutPrice'
+',InvMonthSum.InvOutAmount'
+',0 as InvtzQty'
+',0 as InvtzAmount'
+',InvMonthSum.InvBlncQty'
+',InvMonthSum.InvBlncPrice'
+',InvMonthSum.InvBlncAmount'
+' From InvMonthSum Join Item On InvMonthSum.ItemCode=Item.ItemCode'
+' Join Uom On Item.UomCode=Uom.UomCode'
+' where (InvLMQty<>0 or InvLMAmount<>0 or InvInQty<>0 or InvInAmount<>0 '
+' or InvOutQty<>0 or InvOutAmount<>0 or InvBlncQty<>0 or InvBlncAmount<>0)';
if Trim(CmBx_Warehouse.Text)='全部仓库' then
Condition:=Condition+' and InvMonthSum.InvMonth='''+MEdt_Month.Text+''''+
' Order by InvMonthSum.ItemCode'
else
Condition:=Condition+' and InvMonthSum.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
+' And InvMonthSum.InvMonth='''+MEdt_Month.Text+''''+
' Order by InvMonthSum.ItemCode';
end;
ConditionHint:='仓库:'+CmBx_Warehouse.Text+', 月份:'+MEdt_Month.Text;
ModalResult:=mrOk;
end;
procedure TFrm_Inv_MonthSumQry_C.FormActivate(Sender: TObject);
begin
inherited;
if CmBx_Warehouse.Items.Count=0 then
begin
InitUsableWHCmBx(AdoQry_Tmp,UserCode,CmBx_Warehouse,False);
CmBx_Warehouse.Items.insert(0,'全部仓库');
CmBx_Warehouse.Refresh ;
CmBx_Warehouse.ItemIndex:=0;
end;
end;
procedure TFrm_Inv_MonthSumQry_C.MonthCheck(Sender: TObject);
begin
inherited;
if MEdt_Month.Modified then
begin
InvDataChangeCheck(AdoQry_Tmp,TEdit(Sender).Text);
MEdt_Month.Modified:=False;
MEdt_Month.SetFocus;
abort;
end;
end;
procedure TFrm_Inv_MonthSumQry_C.FormCreate(Sender: TObject);
begin
inherited;
MEdt_Month.Text:=FormatDateTime('yyyy.mm',IncMonth(Now,-1));
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -