📄 inv_monthsumqry.pas

📁 一个MRPII系统源代码版本
💻 PAS
字号:
unit Inv_MonthSumQry;


Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin, DBCtrls,pr_PrintReportType, jpeg;

Type
  TFrm_Inv_MonthSumQry = Class(TFrm_Base_Qry)
    Label1: TLabel;
    DBText1: TDBText;
    Label2: TLabel;
    Edit1: TEdit;
    Label3: TLabel;
    Edit2: TEdit;
    Label4: TLabel;
    Edit3: TEdit;
    Label5: TLabel;
    Edit4: TEdit;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure Act_LookExecute(Sender: TObject);
    procedure AdoQueryAfterOpen(DataSet: TDataSet);
  private
    { Private declarations }

  public
    { Public declarations }
    procedure initReport;Override;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    procedure GetData; Override;
  end;

var                                    
  Frm_Inv_MonthSumQry: TFrm_Inv_MonthSumQry;

implementation

uses Inv_MonthSumQry_C,Inv_OpVendorMonthSumQry_C,Inv_OpMonthSumQry_C,Inv_MonthSumQry_D;

{$R *.DFM}

{ TFrm_Inv_MonthSumQry }

procedure TFrm_Inv_MonthSumQry.GetData;
begin
//  Lbl_Condition.Caption:=Frm_Sys_Condition.ConditionHint;
  AdoQry_Main.Close;
  AdoQry_Main.SQL.Text:=Condition;
  AdoQry_Main.Open;
end;

procedure TFrm_Inv_MonthSumQry.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
begin
  inherited;
  AmountFields:='InvLMAmount,InvInAmount,InvOutAmount,InvtzAmount,InvBlncAmount,';
  PriceFields:='InvLMPrice,InvInPrice,InvOutPrice,InvBlncPrice,';
  OrderByFields:='ItemCodeName';
  if Param2='InvMonthSum' then
  begin
    Caption:='物料收发存汇总表';
    Pnl_Title.Caption:='物料收发存汇总表';
    DBGridEh.Columns.Delete(14);
    DBGridEh.Columns.Delete(13);
    Frm_Sys_Condition:=TFrm_Inv_MonthSumQry_C.Create(Self);
    try
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Drop Table #InvMonthSum';
      AdoQry_Tmp.ExecSQL;
    except
    end;
    try
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Drop Table #InvMonthSum1';
      AdoQry_Tmp.ExecSQL;
    except
    end;
    try
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Drop Table #InvMonthSum2';
      AdoQry_Tmp.ExecSQL;
    except
    end;
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Create Table [dbo].[#InvMonthSum1]('
      +' [ClassCode] [varchAr] (16)'
      +')';
    AdoQry_Tmp.ExecSQL;
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Create Table [dbo].[#InvMonthSum2]('
      +' [ClassCode] [varchAr] (16)'
      +')';
    AdoQry_Tmp.ExecSQL;
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Create Table [dbo].[#InvMonthSum]('
      +' [ItemCode] [varchAr] (16)'
      +',[ItemName] [varchAr] (80)'
      +',[ItemCodeName] [varchAr] (96)'
      +',[flag] [varchAr] (1)'
      +',[ClassCodeOrder] [varchAr] (240)'
      +',[ClassCode] [varchAr] (16)'
      +',[OrderInt] [Int]'
      +',[UomName] [varchAr] (10)'
      +',[InvLmAmount] [decimal](14, 2) default 0'
      +',[InvInAmount] [decimal](14, 2) default 0'
      +',[InvOutAmount] [decimal](14, 2) default 0'
      +',[InvtzAmount] [decimal](14, 2) default 0'
      +',[InvBlncAmount] [decimal](14, 2) default 0'
      +',[InvLmQty] [decimal](15,2 ) default 0'
      +',[InvInQty] [decimal](15,2) default 0'
      +',[InvOutQty] [decimal](15,2) default 0'
      +',[InvtzQty] [decimal](15,2) default 0'
      +',[InvBlncQty] [decimal](15,2) default 0'
      +',[InvLmPrice] [decimal](20, 6) default 0'
      +',[InvInPrice] [decimal](20, 6) default 0'
      +',[InvOutPrice] [decimal](20, 6) default 0'
      +',[InvBlncPrice] [decimal](20, 6) default 0'
      +')';
    AdoQry_Tmp.ExecSQL;
  end
  else if Param2='OpMonthSum' then
  begin
    dbgrideh.Columns[2].Width:=155;
    dbgrideh.Columns[4].Width:=52;
    dbgrideh.Columns[7].Width:=52;
    dbgrideh.Columns[10].Width:=52;
    dbgrideh.Columns[15].Width:=52;
    dbgrideh.Columns[6].Width:=67;
    dbgrideh.Columns[9].Width:=67;
    dbgrideh.Columns[12].Width:=67;
    dbgrideh.Columns[17].Width:=67;
    if Param3='Vendor' then
    begin
      Caption:='委外加工材料分供应商收发存汇总表';
      Pnl_Title.Caption:='委外加工材料分供应商收发存汇总表';
      Frm_Sys_Condition:=TFrm_Inv_OpVendorMonthSumQry_C.Create(Self);
    end
    else
    begin
      Caption:='委外加工材料收发存汇总表';
      Pnl_Title.Caption:='委外加工材料收发存汇总表';
      Frm_Sys_Condition:=TFrm_Inv_OpMonthSumQry_C.Create(Self);
      ExtPrintReport.ReportFont.Size:=10;
      try
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Drop Table #InvOpMonthSum';
        AdoQry_Tmp.ExecSQL;
      except
      end;
      try
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Drop Table #InvOpMonthSum1';
        AdoQry_Tmp.ExecSQL;
      except
      end;
      try
        AdoQry_Tmp.Close;
        AdoQry_Tmp.SQL.Text:='Drop Table #InvOpMonthSum2';
        AdoQry_Tmp.ExecSQL;
      except
      end;
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Create Table [dbo].[#InvOpMonthSum1]('
        +' [ClassCode] [varchAr] (16)'
        +')';
      AdoQry_Tmp.ExecSQL;
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Create Table [dbo].[#InvOpMonthSum2]('
        +' [ClassCode] [varchAr] (16)'
        +')';
      AdoQry_Tmp.ExecSQL;
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.Text:='Create Table [dbo].[#InvOpMonthSum]('
        +' [ItemCode] [varchAr] (16)'
        +',[ItemName] [varchAr] (80)'
        +',[ItemCodeName] [varchAr] (96)'
        +',[flag] [varchAr] (1)'
        +',[ClassCodeOrder] [varchAr] (240)'
        +',[ClassCode] [varchAr] (16)'
        +',[OrderInt] [Int]'
        +',[UomName] [varchAr] (10)'
        +',[InvLmAmount] [decimal](14, 2) default 0'
        +',[InvInAmount] [decimal](14, 2) default 0'
        +',[InvOutAmount] [decimal](14, 2) default 0'
        +',[InvtzAmount] [decimal](14, 2) default 0'
        +',[InvBlncAmount] [decimal](14, 2) default 0'
        +',[InvLmQty] [decimal](15,2) default 0'
        +',[InvInQty] [decimal](15,2) default 0'
        +',[InvOutQty] [decimal](15,2) default 0'
        +',[InvtzQty] [decimal](15,2) default 0'
        +',[InvBlncQty] [decimal](15,2) default 0'
        +',[InvLmPrice] [decimal](20, 6) default 0'
        +',[InvInPrice] [decimal](20, 6) default 0'
        +',[InvOutPrice] [decimal](20, 6) default 0'
        +',[InvBlncPrice] [decimal](20, 6) default 0'
        +')';
      AdoQry_Tmp.ExecSQL;

    end;
  end;
  if Param1='Inv' then
  begin
    if Param2='InvMonthSum' then
    begin
      DBGridEh.Columns.Delete(15);
      DBGridEh.Columns.Delete(14);
      DBGridEh.Columns.Delete(12);
      DBGridEh.Columns.Delete(11);
      DBGridEh.Columns.Delete(9);
      DBGridEh.Columns.Delete(8);
      DBGridEh.Columns.Delete(6);
      DBGridEh.Columns.Delete(5);
    end
    else
    begin
      DBGridEh.Columns.Delete(17);
      DBGridEh.Columns.Delete(16);
      DBGridEh.Columns.Delete(14);
      DBGridEh.Columns.Delete(12);
      DBGridEh.Columns.Delete(11);
      DBGridEh.Columns.Delete(9);
      DBGridEh.Columns.Delete(8);
      DBGridEh.Columns.Delete(6);
      DBGridEh.Columns.Delete(5);
    end;
  end;
  Act_Filter.Execute;
end;

procedure TFrm_Inv_MonthSumQry.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=' Drop Table #InvMonthSum';
    AdoQry_Tmp.ExecSQL;
  except
  end;
  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Drop Table #InvMonthSum1';
    AdoQry_Tmp.ExecSQL;
  except
  end;
  try
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Drop Table #InvMonthSum2';
    AdoQry_Tmp.ExecSQL;
  except
  end;
  try
    with AdoQry_Tmp do
    begin
      Close;
      //sql.text:='drop table #InvOpMonthSum  drop table #InvOpMonthSum1  drop table #InvOpMonthSum2';
      sql.text:='drop table #InvOpMonthSum ';
      execsql;
    end;
  except
  end;
  inherited;
end;

procedure TFrm_Inv_MonthSumQry.DBGridEhGetCellParams(Sender: TObject;
  Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin
  inherited;
  if (gdSelected in State)or(gdFocused in State) then
  begin
    Background:=clNavy;
    AFont.Color:=clWindow;
  end
  else
  begin
    if AdoQry_Main.fieldbyname('UomName').AsString='' then
    begin
      AFont.Color:=clBlue;
    end
    else
      AFont.Color:=clBlack;
  end;
end;

procedure TFrm_Inv_MonthSumQry.Act_LookExecute(Sender: TObject);
begin
  inherited;
  if AdoQry_Main.active then
  begin
    with TFrm_Inv_MonthSumQry_D.Create(Self) do
    begin
      SetUserParam(Param1,Param2,Param3,Param4,Param5,Param6);
      SetSysParam(UserCode,ModuleCode,MenuId,LoginDate);
      InitForm(DBConnect,'ReadOnly',AdoQry_Main);
      ShowModal;
      Release;
    end;
  end;
end;

procedure TFrm_Inv_MonthSumQry.initReport;
var
   i:integer;
   userName:string;
begin
  inherited;
  with AdoQry_tmp do
  begin
    Close;
    sql.Text:='select * from Employee where EmployeeCode='''+userCode+'''';
    open;
    userName:=fieldbyname('EmployeeName').asstring;
  end;
  //ExtprintReport.PAperSet:=ptA3;
  //ExtprintReport.PAperOrientation :=poLandScApe;

  ExtprintReport.Foot1:='制表：'+userName+'        审核';
  if Param1<>'Inv' then
  begin
    ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvlmAmount')].sum:=True;
//    ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvlmAmount')].Style:=dSconcise;
    ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvInAmount')].sum:=True;
    ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvOutAmount')].sum:=True;
    ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvBlncAmount')].sum:=True;
    if Param2='OpMonthSum' then
    ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvtzAmount')].sum:=True;
  end;
//  ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvlmQty')].sum:=True;
//  ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvInQty')].sum:=True;
//  ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvOutqty')].sum:=True;
//  ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvBlncQty')].sum:=True;
//  if Param2='OpMonthSum' then
//  ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('Invtzqty')].sum:=True;
end;

procedure TFrm_Inv_MonthSumQry.AdoQueryAfterOpen(DataSet: TDataSet);
var
  sql_txt:string;
  i:integer;
  sumAmounts,sumAmountr,sumAmountc,sumAmountb:real;
begin
  inherited;
  sumAmounts:=0;
  sumAmountr:=0;
  sumAmountc:=0;
  sumAmountb:=0;
  //汇总金额
  If Not AdoQry_Main.IsEmpty then
  begin
    AdoQry_Main.DisableControls;
    AdoQry_Main.First;
    for I:=0 to AdoQry_Main.RecordCount-1 do
    begin
      if copy(AdoQry_Main.fieldbyname('ItemCodeName').asstring,
             length(AdoQry_Main.fieldbyname('ItemCodeName').asstring)-3,4)<>'合计' then
      begin
        SumAmounts:=SumAmounts+AdoQry_Main.fieldbyname('InvlmAmount').asFloat;
        SumAmountr:=SumAmountr+AdoQry_Main.fieldbyname('InvInAmount').asFloat;
        SumAmountc:=SumAmountc+AdoQry_Main.fieldbyname('InvoutAmount').asFloat;
        SumAmountb:=SumAmountb+AdoQry_Main.fieldbyname('InvblncAmount').asFloat;
      end;
      AdoQry_Main.next;
    end;
    AdoQry_Main.First;
    AdoQry_Main.enableControls;
  end;
  edit1.text:=FormatFloat('#.##',SumAmounts);
  edit2.text:=FormatFloat('#.##',SumAmountr);
  edit3.text:=FormatFloat('#.##',SumAmountc);
  edit4.text:=FormatFloat('#.##',SumAmountb);

end;

end.
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -