inv_opvendorsum.pas

一个MRPII系统源代码版本

unit Inv_OpVendorSum;

Interface

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

Type
  TFrm_Inv_OpVendorSum = Class(TFrm_Base_Qry)
    Label4: TLabel;
    lbl_Month: TLabel;
    AdoQry_tmp1: TAdoQuery;
    Label1: TLabel;
    DBText2: TDBText;
    Label2: TLabel;
    edt_Sum1: TEdit;
    edt_Sum2: TEdit;
    edt_Sum3: TEdit;
    edt_Sum4: TEdit;
    edt_Sum5: TEdit;
    procedure Act_FilterExecute(Sender: TObject);
    procedure AdoQueryAfterOpen(DataSet: TDataSet);
  private
    { Private declarations }
    sum1,sum2,sum3,sum4,sum5:double;
  public
    acondition:string;
    procedure initform(AdOConnection:TAdOConnection;ReadOnly:boolean);Override;
    procedure InitReport;Override;
    { Public declarations }
  end;

var
  Frm_Inv_OpVendorSum: TFrm_Inv_OpVendorSum;

implementation

uses Inv_OpVendorSum_C,Sys_Global;
{$R *.DFM}

procedure TFrm_Inv_OpVendorSum.InitReport;
var
  userName:string;
  i:integer;
begin
  inherited;
  i:=ExtPrintReport.Headers.indexof('InvLMAmount');
  if i>=0 then
  begin
    ExtPrintReport.Headers[i].sum:=True;
    ExtPrintReport.Headers[i].style:=dSconcise;
  end;
  i:=ExtPrintReport.Headers.indexof('InvInAmount');
  if i>=0 then
  begin
    ExtPrintReport.Headers[i].sum:=True;
    ExtPrintReport.Headers[i].style:=dSconcise;
  end;
  i:=ExtPrintReport.Headers.indexof('InvOutAmount');
  if i>=0 then
  begin
    ExtPrintReport.Headers[i].sum:=True;
    ExtPrintReport.Headers[i].style:=dSconcise;
  end;
  i:=ExtPrintReport.Headers.indexof('InvBillNoTaxAmount');
  if i>=0 then
  begin
    ExtPrintReport.Headers[i].sum:=True;
    ExtPrintReport.Headers[i].style:=dSconcise;
  end;
  i:=ExtPrintReport.Headers.indexof('InvBlncAmount');
  if i>=0 then
  begin
    ExtPrintReport.Headers[i].sum:=True;
    ExtPrintReport.Headers[i].style:=dSconcise;
  end;
  with AdoQry_tmp do
  begin
    Close;
    sql.Text:='select * from Employee where EmployeeCode='''+userCode+'''';
    open;
    userName:=fieldbyname('EmployeeName').asstring;
  end;
  ExtprintReport.subtitle1:='统计月份：'+lbl_Month.Caption ;
  ExtprintReport.Foot1:='       制　表：'+userName+'      　审核：   ';

  ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvBlncAmount')].sum:=True;
  ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvLMAmount')].sum:=True;
  ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvOutAmount')].sum:=True;
  ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvBillNoTaxAmount')].sum:=True;
  ExtprintReport.Headers.Items[ExtprintReport.Headers.indexof('InvInAmount')].sum:=True;
end;



procedure TFrm_Inv_OpVendorSum.initform(AdOConnection:TAdOConnection;ReadOnly:boolean);
begin
  inherited;
  acondition:='';
  AmountFields:='InvLMAmount,InvInAmount,InvOutAmount,InvBlncAmount,InvBillNoTaxAmount,';
//  PriceFields:='InvLMAmount,InvInAmount,InvOutAmount,InvBlncAmount,';
  SelectFromSQL:=
    'select Bill.InvLMAmount,Bill.InvInAmount,Bill.InvOutAmount,Bill.InvBlncAmount,Bill.VendorCode+'''+' '+'''+Vendor.VendorName VendorCode,BillS.InvBillNoTaxAmount from '+
    '(select VendorCode,sum(InvLMAmount) InvLMAmount,sum(InvInAmount) InvInAmount,'+
    'sum(InvOutAmount) InvOutAmount,sum(InvBlncAmount) InvBlncAmount '+
    'from OpMonthSum '+
    ' group by VendorCode) Bill '+
    ' left join '+
    '(select InvInBill.VendorCode,sum(InvInBillLine.InvBillNoTaxAmount) InvBillNoTaxAmount '+
    ' from InvInBill '+
    ' join InvInBillLine on InvInBill.InvBillId=InvInBillLine.InvBillId '+
    ' where ((InvInBill.BillTypeCode=''0103'' and InvInBill.InvBillWHChck<>0) or (InvInBill.BillTypeCode=''0199'' and opBill=1))'+
//    ' and InvInBill.InvBillWHChck<>0'+
    ' group by InvInBill.VendorCode ) BillS  on Bill.VendorCode=BillS.VendorCode '+
    ' join Vendor on Vendor.VendorCode=Bill.VendorCode';

  OrderByFields:='VendorCode';
  lbl_Order.Caption:='供应商标识';

  Frm_Sys_Condition:=TFrm_Inv_OpVendorSum_C.Create(Self);
  TFrm_Inv_OpVendorSum_C(Frm_Sys_Condition).InitForm(AdoQry_Main.Connection,UserCode,LoginDate);
  Act_Filter.Execute;
//  lbl_Month.Caption:=TFrm_Inv_OpVendorSum_C(Frm_Sys_Condition).medt_Month.text;
end;

procedure TFrm_Inv_OpVendorSum.Act_FilterExecute(Sender: TObject);
var
   tmp_Cursor:tcursor;
begin
  if Frm_Sys_Condition.ShowModal=mrOk then
  begin
    try
      AdoQry_tmp.Close;
      AdoQry_tmp.sql.text:='drop table #tmp01';
      AdoQry_tmp.ExecSQL;
    except
    end;
    tmp_Cursor:=Screen.Cursor ;
    Screen.Cursor:=crHourGlass	;
    Lbl_Condition.Caption:=Frm_Sys_Condition.ConditionHint;
    lbl_Month.Caption:=TFrm_Inv_OpVendorSum_C(Frm_Sys_Condition).medt_Month.text;
    aCondition:=lbl_Month.Caption;
    with AdoQry_tmp do
    begin
      Close;
      sql.text:='select VendorCode,sum(InvLMAmount) InvLMAmount,sum(InvInAmount) InvInAmount,'+
                'sum(InvOutAmount) InvOutAmount,sum(InvBlncAmount) InvBlncAmount '+
                'into #tmp01 from OpMonthSum where InvMonth='''+acondition+''''+
                ' group by VendorCode ';
      ExecSQL;
      Close;
      sql.text:='insert #tmp01(VendorCode) select distinct InvInBill.VendorCode '+
                ' from InvInBill '+
                ' where ((InvInBill.BillTypeCode=''0103'' and InvInBill.InvBillWHChck<>0) or (InvInBill.BillTypeCode=''0199'' and opBill=1)) and InvInBill.InvBillMonth='''+acondition+''''+
                ' and InvInBill.VendorCode not in (select VendorCode from #tmp01)';
      ExecSQL;
    end;
    AdoQry_Main.DisableControls;
    AdoQry_Main.Close;
    AdoQry_Main.SQL.clear;
  SelectFromSQL:=
    'select Bill.InvLMAmount,Bill.InvInAmount,Bill.InvOutAmount,Bill.InvBlncAmount,Bill.VendorCode+'+''' '''+'+Vendor.VendorName VendorCode,BillS.InvBillNoTaxAmount from '+
    '(select VendorCode,InvLMAmount,InvInAmount,'+
    'InvOutAmount,InvBlncAmount '+
    'from #tmp01) Bill '+
    ' left join '+
    '(select InvInBill.VendorCode,sum(InvInBillLine.InvBillNoTaxAmount) InvBillNoTaxAmount '+
    ' from InvInBill '+
    ' join InvInBillLine on InvInBill.InvBillId=InvInBillLine.InvBillId '+
    ' where ((InvInBill.BillTypeCode=''0103'' and InvInBill.InvBillWHChck<>0) or (InvInBill.BillTypeCode=''0199'' and opBill=1)) and InvInBill.InvBillMonth='''+acondition+''''+
//    ' and InvInBill.InvBillWHChck<>0'+
    ' group by InvInBill.VendorCode ) BillS  on Bill.VendorCode=BillS.VendorCode '+
    ' join Vendor on Vendor.VendorCode=Bill.VendorCode ' +
    ' where (Bill.InvLMAmount<>0 or Bill.InvInAmount<>0 '+
    ' or Bill.InvOutAmount<>0 or Bill.InvBlncAmount<>0 or Bills.InvBillNoTaxAmount<>0 )';
    AdoQry_Main.SQL.Text:=SelectFromSQL;
    AdoQry_Main.Open;
    AdoQry_Main.Sort:=OrderByFields;
    AdoQry_Main.EnableControls;
    Screen.Cursor :=tmp_Cursor;
  end;
end;

procedure TFrm_Inv_OpVendorSum.AdoQueryAfterOpen(DataSet: TDataSet);
begin
  inherited;
  sum1:=0;
  sum2:=0;
  sum3:=0;
  sum4:=0;
  sum5:=0;
  AdoQry_Main.DisableControls ;
  with AdoQry_Main do
  begin
    First;
    while not eof do
    begin
      sum1:=sum1+fieldbyname('InvLMAmount').asfloat;
      sum2:=sum2+fieldbyname('InvInAmount').asfloat;
      sum3:=sum3+fieldbyname('InvOutAmount').asfloat;
      sum4:=sum4+fieldbyname('InvBillNotaxAmount').asfloat;
      sum5:=sum5+fieldbyname('InvBLncAmount').asfloat;
      next;
    end;
  end;
  AdoQry_Main.EnableControls ;
  edt_Sum1.Text :=floattostr(sum1);
  edt_Sum2.text :=floattostr(sum2);
  edt_Sum3.text :=floattostr(sum3);
  edt_Sum4.text :=floattostr(sum4);
  edt_Sum5.text :=floattostr(sum5);
end;

end.