📄 egmaterialchange.pas
字号:
unit EGMaterialChange;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, WSVoucherBrowse, DB, ActnList, Grids,WSEdit, DBGrids, QLDBGrid,
ComCtrls, ExtCtrls, ToolWin,DateUtils, ADODB, StdCtrls, Buttons, GEdit,
DBCtrls, Menus, WNADOCQuery,TypInfo, scExcelExport;
type
TEGMaterialChangeForm = class(TWSVoucherBrowseForm)
ToolButton4: TToolButton;
ToolButton5: TToolButton;
ToolButton6: TToolButton;
ToolButton7: TToolButton;
ToolButton8: TToolButton;
ToolButton9: TToolButton;
ToolButton11: TToolButton;
ToolButton12: TToolButton;
ToolButton13: TToolButton;
adsMaster: TADODataSet;
Panel2: TPanel;
ADOQuery: TADOQuery;
ADOQuery2: TADOQuery;
Panel3: TPanel;
Panel5: TPanel;
Button3: TButton;
PopupMenu1: TPopupMenu;
N1: TMenuItem;
TempAds: TADODataSet;
WNADOCQuery1: TWNADOCQuery;
Panel4: TPanel;
GroupBox3: TGroupBox;
Memo1: TMemo;
Button2: TButton;
Panel6: TPanel;
Panel7: TPanel;
GroupBox1: TGroupBox;
CheckBox1: TCheckBox;
CheckBox2: TCheckBox;
GroupBox2: TGroupBox;
ShowCheckBox1: TCheckBox;
ShowCheckBox2: TCheckBox;
ShowCheckBox3: TCheckBox;
CheckBox3: TCheckBox;
DateTimePicker1: TDateTimePicker;
DateTimePicker2: TDateTimePicker;
Label1: TLabel;
Label2: TLabel;
CheckBox4: TCheckBox;
procedure UpdateDBGrid;
procedure DBGridTitleClick(Column: TColumn);
procedure FormActivate(Sender: TObject);
procedure N1Click(Sender: TObject);
procedure DBGridDblClick(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure CheckBox1Click(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure adsMasterBeforeOpen(DataSet: TDataSet);
procedure ExportActionExecute(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure DateTimePicker1Change(Sender: TObject);
private
{ Private declarations }
WhereStr :string;
public
{ Public declarations }
protected
function CreateEditForm: TWSEditForm; override;
end;
var
EGMaterialChangeForm: TEGMaterialChangeForm;
implementation
uses CommonDM,QLDBFlt;
{$R *.dfm}
function TEGMaterialChangeForm.CreateEditForm: TWSEditForm;
begin
// Result := TSLEdClearBillAForm.Create(Self);
end;
procedure TEGMaterialChangeForm.UpdateDBGrid;
var I: Integer;
begin
with DBGrid do
begin
FooterRowCount := 0;
Columns[0].Footer.ValueType := fvtStaticText;
Columns[0].Footer.Value := '合计:';
Columns[0].Footer.Alignment := taCenter;
Columns[0].Title.Alignment:= taCenter;
Columns[0].Width :=90;
if Pos('日',Columns[0].FieldName)>0 then Columns[0].Width :=70;
for I := 1 to Columns.Count - 1 do
begin
Columns[i].Title.Alignment:= taCenter;
Columns[i].Width :=90;
if Pos('日',Columns[I].FieldName)>0 then Columns[i].Width :=70;
if Pos('编',Columns[I].FieldName)>0 then Columns[i].Width :=70;
if Columns[I].Field is TNumericField then
begin
SetStrProp(Fields[I], 'DisplayFormat','#,#.00') ;
if (Pos('金',Columns[I].FieldName)>0) then SetOrdProp(adsMaster.Fields[I], 'currency',1) ;
if Pos('单',Columns[I].FieldName)<=0 then Columns[I].Footer.ValueType := fvtSum;
if (not ShowCheckBox1.Checked) and (Pos('金',Columns[I].FieldName)>0) then Columns[I].Visible :=False;
if (not ShowCheckBox2.Checked) and (Pos('数',Columns[I].FieldName)>0) then Columns[I].Visible :=False;
if (not ShowCheckBox3.Checked) and (Pos('单',Columns[I].FieldName)>0) then Columns[I].Visible :=False;
end;
end;
FooterRowCount := 1;
end;
end;
procedure TEGMaterialChangeForm.DBGridTitleClick(Column: TColumn);
begin
inherited;
UpdateDBGrid;
end;
procedure TEGMaterialChangeForm.FormActivate(Sender: TObject);
begin
inherited;
ADOQuery.Close;
ADOQuery.SQL.Text :=' IF EXISTS( SELECT * FROM tempdb..sysobjects '
+' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#EGMaterialBalanceTtl0')
+' )) DROP TABLE #EGMaterialBalanceTtl0 ' ;
ADOQuery.ExecSQL;
ADOQuery.Close;
ADOQuery.SQL.Text :=' CREATE TABLE #EGMaterialBalanceTtl0 ('
+' [id] [int] IDENTITY (1, 1) NOT NULL ,'
+' [BillMode] [varchar] (16) , '
+' [WareHouse] [varchar] (30) NULL , '
+' [Goods] [varchar] (60) NULL , '
+' [GoodsClass] [varchar] (60) NULL , '
+' [Unit] [varchar] (30) NULL , '
+' [AmountBegin] [float] NULL , '
+' [PriceBegin] [float] NULL , '
+' [GoalQuantityBegin] [float] NULL ,'
+' [Amount] [float] NULL , '
+' [Price] [float] NULL , '
+' [GoalQuantity] [float] NULL ,'
+' [AmountOut] [float] NULL , '
+' [PriceOut] [float] NULL , '
+' [GoalQuantityOut] [float] NULL ,'
+' [AmountEnd] [float] NULL , '
+' [PriceEnd] [float] NULL , '
+' [GoalQuantityEnd] [float] NULL ) ' ;
ADOQuery.ExecSQL;
ADOQuery.Close;
ADOQuery.SQL.Text :=' IF EXISTS( SELECT * FROM tempdb..sysobjects '
+' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#EGMaterialBalanceTtl')
+' )) DROP TABLE #EGMaterialBalanceTtl ' ;
ADOQuery.ExecSQL;
ADOQuery.Close;
ADOQuery.SQL.Text :=' CREATE TABLE #EGMaterialBalanceTtl ('
+' [id] [int] IDENTITY (1, 1) NOT NULL ,'
+' [BillMode] [varchar] (16) , '
+' [WareHouse] [varchar] (30) NULL , '
+' [Goods] [varchar] (60) NULL , '
+' [GoodsClass] [varchar] (60) NULL , '
+' [Unit] [varchar] (30) NULL , '
+' [AmountBegin] [float] NULL , '
+' [PriceBegin] [float] NULL , '
+' [GoalQuantityBegin] [float] NULL ,'
+' [Amount] [float] NULL , '
+' [Price] [float] NULL , '
+' [GoalQuantity] [float] NULL ,'
+' [AmountOut] [float] NULL , '
+' [PriceOut] [float] NULL , '
+' [GoalQuantityOut] [float] NULL ,'
+' [AmountEnd] [float] NULL , '
+' [PriceEnd] [float] NULL , '
+' [GoalQuantityEnd] [float] NULL ) ' ;
ADOQuery.ExecSQL;
ADOQuery.Close;
ADOQuery.SQL.Text :=' IF EXISTS( SELECT * FROM tempdb..sysobjects '
+' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#EGMaterialBalanceDM')
+' )) DROP TABLE #EGMaterialBalanceDM ' ;
ADOQuery.ExecSQL;
ADOQuery.Close;
ADOQuery.SQL.Text :=' CREATE TABLE #EGMaterialBalanceDM ('
+' [pid] [int] IDENTITY (1, 1) NOT NULL ,'
+' [id] [int] NULL ,'
+' [Date] [datetime] NULL ,[Code] [varchar] (20) , '
+' [ClientID] [int] NULL , [EmployeeID] [int] NULL , '
+' [BillMode] [varchar] (16) ,[ModeDC] [int] NULL , '
+' [BillModeOut] [varchar] (16) ,'
+' [ModeC] [int] NULL ,[Brief] [varchar] (30) , '
+' [GoodsID] [int] NULL ,[GoodsSpec] [varchar] (30) , '
+' [Quantity] [float] NULL ,[QuantityPcs] [float] NULL, '
+' [Discount] [float] NULL , [PriceCost] [float] NULL , '
+' [PackUnitID] [int] NULL ,[PriceBase] [money] NULL , '
+' [Amount] [float] NULL ,[GoalUnitID] [int] NULL , '
+' [GoalQuantity] [float] NULL ,[Client] [varchar] (50) NULL , '
+' [GoalQuantityOut] [float] NULL ,'
+' [AmountOut] [float] NULL ,'
+' [Employee] [varchar] (30) NULL, [Goods] [varchar] (30) NULL , '
+' [GoodsClass] [varchar] (30) NULL ,[DAArea] [varchar] (30) NULL , '
+' [Unit] [varchar] (20) NULL , '
+' [WareHouse] [varchar] (30) NULL , '
+' [ProjectName] [varchar] (30) NULL , '
+' [GrossProfit ] [float] NULL ) ' ;
ADOQuery.ExecSQL;
ADOQuery.Close;
ADOQuery.SQL.Text :=' TRUNCATE TABLE #EGMaterialBalanceDM';
ADOQuery.ExecSQL;
ADOQuery.Close; // 进料记录
ADOQuery.SQL.Text :=' INSERT INTO #EGMaterialBalanceDM ('
+' ID,Date,Code,ClientID,EmployeeID, '
+' BillMode,ModeDC,ModeC,Brief, '
+' GoodsID,GoodsSpec, Quantity,QuantityPcs,PackUnitID, '
+' PriceBase,Amount,GoalUnitID,GoalQuantity,Discount,PriceCost,'
+' [Client],[Employee],[Goods],[GoodsClass],[DAArea], '
+' [Unit],[WareHouse],GoalQuantityOut )'
+' select b.id,b.Date,b.Code,b.ClientID,b.EmployeeID, '
+' b.BillMode,b.ModeDC,b.ModeC,b.Brief, '
+' a.GoodsID,a.GoodsSpec, a.Quantity,a.QuantityPcs,a.PackUnitID, '
+' a.PriceBase,a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1),'
+' a.GoalUnitID,a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) , Discount,PriceCost,'
+' c.name Client,d.Name Employee,e.Name Goods,f.Name GoodsClass, '
+' g.Name DAArea,h.name Unit ,j.name WareHouse,0.00000000001'
+' from EGMaterialInDetail AS a '
+' LEFT OUTER JOIN EGMaterialInmaster as b on a.MasterID=b.id '
+' LEFT OUTER JOIN DAClient as c on b.ClientID=c.id '
+' LEFT OUTER JOIN MSEmployee as d on b.EmployeeID=d.id '
+' LEFT OUTER JOIN DAGoods as e on a.GoodsID=e.id '
+' LEFT OUTER JOIN DAGoodsClass as f on e.GoodsClassID=f.id '
+' LEFT OUTER JOIN DAarea as g on c.AreaID=g.id '
+' LEFT OUTER JOIN MSUnit as h on E.UnitID=h.id '
+' LEFT OUTER JOIN STWareHouse as j on b.WareHouseID=j.id '
+' where b.Recordstate<>'+Quotedstr('删除')
+' and GoodsID<>0 and not GoodsID is null'
+' and GoalQuantity<>0 ' ;
ADOQuery.ExecSQL;
ADOQuery.Close; // 调入记录
ADOQuery.SQL.Text :=' INSERT INTO #EGMaterialBalanceDM ('
+' ID,Date,Code,ClientID,EmployeeID, '
+' BillMode,ModeDC,ModeC,Brief, '
+' GoodsID,GoodsSpec, Quantity,QuantityPcs,PackUnitID, '
+' PriceBase,Amount,GoalUnitID,GoalQuantity,Discount,PriceCost ,'
+' [Client],[Employee],[Goods],[GoodsClass],[DAArea], '
+' [Unit],[WareHouse],GoalQuantityOut )'
+' select b.id,b.Date,b.Code,b.ClientID,b.EmployeeID, '
+' b.BillMode,b.ModeDC,b.ModeC,b.Brief, '
+' a.GoodsID,a.GoodsSpec, a.Quantity,a.QuantityPcs,a.PackUnitID, '
+' a.PriceBase,a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1),'
+' a.GoalUnitID,a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) , Discount,0,'
+' c.name Client,d.Name Employee,e.Name Goods,f.Name GoodsClass, '
+' g.Name DAArea,h.name Unit ,j.name WareHouse,0.00000000001'
+' from STGoodsOutInDetail AS a '
+' LEFT OUTER JOIN STGoodsOutInmaster as b on a.MasterID=b.id '
+' LEFT OUTER JOIN STWareHouse as c on b.ClientID=c.id '
+' LEFT OUTER JOIN MSEmployee as d on b.EmployeeID=d.id '
+' LEFT OUTER JOIN DAGoods as e on a.GoodsID=e.id '
+' LEFT OUTER JOIN DAGoodsClass as f on e.GoodsClassID=f.id '
+' LEFT OUTER JOIN DAarea as g on c.ID=g.id '
+' LEFT OUTER JOIN MSUnit as h on E.UnitID=h.id '
+' LEFT OUTER JOIN STWareHouse as j on b.WareHouseID=j.id '
+' where b.Recordstate<>'+Quotedstr('删除')
+' and GoodsID<>0 and not GoodsID is null'
+' and GoalQuantity<>0 ' ;
ADOQuery.ExecSQL;
ADOQuery.Close; // 领料记录
ADOQuery.SQL.Text :=' INSERT INTO #EGMaterialBalanceDM ('
+' ID,Date,Code,ClientID,EmployeeID, '
+' BillMode,ModeDC,ModeC,Brief, '
+' GoodsID,GoodsSpec, Quantity,QuantityPcs,PackUnitID, '
+' PriceBase,AmountOut,GoalUnitID,GoalQuantityOut,Discount,PriceCost,'
+' [ProjectName],[Employee],[Goods],[GoodsClass],[DAArea], '
+' [Unit],[WareHouse],GoalQuantity )'
+' select b.id,b.Date,b.Code,b.ClientID,b.EmployeeID, '
+' b.BillMode,b.ModeDC,b.ModeC,b.Brief, '
+' a.GoodsID,a.GoodsSpec, a.Quantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1), a.QuantityPcs,a.PackUnitID, '
+' a.PriceBase,a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1),'
+' a.GoalUnitID,a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1) , Discount,PriceCost,'
+' c.name Client,d.Name Employee,e.Name Goods,f.Name GoodsClass, '
+' g.Name DAArea,h.name Unit ,j.name WareHouse,0.00000000001'
+' from EGMaterialOutDetail AS a '
+' LEFT OUTER JOIN EGMaterialOutmaster as b on a.MasterID=b.id '
+' LEFT OUTER JOIN DAProject as c on b.ClientID=c.id '
+' LEFT OUTER JOIN MSEmployee as d on b.EmployeeID=d.id '
+' LEFT OUTER JOIN DAGoods as e on a.GoodsID=e.id '
+' LEFT OUTER JOIN DAGoodsClass as f on e.GoodsClassID=f.id '
+' LEFT OUTER JOIN DAarea as g on c.ProjectClassID=g.id '
+' LEFT OUTER JOIN MSUnit as h on E.UnitID=h.id '
+' LEFT OUTER JOIN STWareHouse as j on b.WareHouseID=j.id '
+' where b.Recordstate<>'+Quotedstr('删除')
+' and GoodsID<>0 and not GoodsID is null'
+' and GoalQuantity<>0 ' ;
ADOQuery.ExecSQL;
ADOQuery.Close; // 调出记录
ADOQuery.SQL.Text :=' INSERT INTO #EGMaterialBalanceDM ('
+' ID,Date,Code,ClientID,EmployeeID, '
+' BillMode,ModeDC,ModeC,Brief, '
+' GoodsID,GoodsSpec, Quantity,QuantityPcs,PackUnitID, '
+' PriceBase,AmountOut,GoalUnitID,GoalQuantityOut,Discount,PriceCost,'
+' [ProjectName],[Employee],[Goods],[GoodsClass],[DAArea], '
+' [Unit],[WareHouse],GoalQuantity )'
+' select b.id,b.Date,b.Code,b.WareHouseID,b.EmployeeID, '
+' b.BillMode,b.ModeDC,b.ModeC,b.Brief, '
+' a.GoodsID,a.GoodsSpec, a.Quantity,a.QuantityPcs,a.PackUnitID, '
+' a.PriceBase,a.Amount*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1),'
+' a.GoalUnitID,a.GoalQuantity*Isnull(b.ModeDC,1)*Isnull(b.ModeC,1), Discount,0,'
+' c.name Client,d.Name Employee,e.Name Goods,f.Name GoodsClass, '
+' g.Name DAArea,h.name Unit ,j.name WareHouse,0.00000000001'
+' from STGoodsOutInDetail AS a '
+' LEFT OUTER JOIN STGoodsOutInmaster as b on a.MasterID=b.id '
+' LEFT OUTER JOIN STWareHouse as c on b.WareHouseID=c.id '
+' LEFT OUTER JOIN MSEmployee as d on b.EmployeeID=d.id '
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -