inv_vendorinsumqry.pas

一个MRPII系统源代码版本

unit Inv_VendorInSumQry;
//供应商到货查询主单元
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;

Type
  TFrm_Inv_VendorInSumQry = Class(TFrm_Base_Qry)
    Label1: TLabel;
    DBText1: TDBText;
    Label2: TLabel;
    DBText2: TDBText;
    AdODataSet1: TAdODataSet;
    Label3: TLabel;
    edt_Amount: TEdit;
    AdoQry_MaInvendorFlag: TStringField;
    AdoQry_MaInvendorName: TStringField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MaInvendorCode: TStringField;
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainItemFlag: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainQty: TFloatField;
    AdoQry_MainNotaxAmount: TFloatField;
    AdoQry_MainPrice: TFloatField;
    AdoQry_MainNoTaxPrice: TFloatField;
    Label4: TLabel;
    edt_Amount2: TEdit;
    procedure Act_LookExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure Act_FilterExecute(Sender: TObject);
    procedure AdODataSet1AfterOpen(DataSet: TDataSet);
  private
    Fshow:Boolean;
    SumAmount:real;
    SumAmount2:real;
    { Private declarations }
  public
    SendCondition:string;
    Fcondition,FconditionHint,FVendor:string;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    procedure initReport;Override;
    { Public declarations }
  end;

var
  Frm_Inv_VendorInSumQry: TFrm_Inv_VendorInSumQry;

implementation

uses Inv_VendorInSumQry_C,Sys_Global,Inv_Global,Inv_VendorInSumQry_D,Inv_BillListPrint_C;

{$R *.DFM}

{ TFrm_Inv_VendorInSumQry }

procedure TFrm_Inv_VendorInSumQry.InitForm(AdOConnection: TAdOConnection;
  ShowExtendColumn: Boolean);
var
  I:integer;
begin
  //inherited;
  AdoQry_Tmp.Connection:=AdOConnection;
  AdODataSet1.Connection:=AdOConnection;
  fshow:=True;
  //含税金额、未税金额的初始化
  sumAmount:=0;
  sumAmount2:=0;
  //扩展字段的处理
  if(not ShowExtendColumn)then
  begin
    DBGridEh.Columns.Delete(5);
    DBGridEh.Columns.Delete(4);
    Label3.Visible:=Fshow;
    edt_Amount.Visible:=Fshow;
  end
  else
  begin
    PriceFields:='Price,';
    AmountFields:='Amount,';
    Label3.Visible:=Fshow;
    edt_Amount.Visible:=Fshow;
  end;
  //Qry的SQL　的设定
  SelectFromSQL:='select B.VendorCode+'' ''+V.VendorName VendorFlag,'+
                 '       V.VendorName,I.ItemName,B.VendorCode,Bl.ItemCode,'+
                 '       Bl.ItemCode+'' ''+I.ItemName ItemFlag,'+
                 '       U.UomName,sum(BL.InvBillqty) Qty,sum(Bl.InvBillNoTaxAmount) NoTaxAmount,'+
                 '       sum(Bl.InvBillAmount) Amount,'+
                 '       case when sum(Bl.InvBillQty)=0 then 0 '+
                 '       else sum(Bl.InvBillAmount)/sum(Bl.InvBillQty) end Price,'+
                 '       case when sum(Bl.InvBillQty)=0 then 0'+
                 '       else sum(Bl.InvBillNoTaxAmount)/sum(BL.InvBillqty) end NoTaxPrice'+
                 '  from InvInBill B'+
                 '  join InvInBillLine Bl'+
                 '    on B.InvBillId=Bl.InvBillId'+
                 '  Join Vendor V'+
                 '    on V.VendorCode=B.VendorCode'+
                 '  Left Join Item I'+
                 '    On I.ItemCode=BL.ItemCode'+
                 '  Left Join Uom U'+
                 '    On I.UomCode=U.UomCode';
  //排序字段、江总字段的初始化
  OrderByFields:='VendorFlag,ItemFlag';
  GroupByFields:='B.VendorCode,Bl.ItemCode,U.UomName,V.VendorName,I.ItemName';

  Frm_Sys_Condition:=TFrm_Inv_VendorInSumQry_C.Create(self);
  TFrm_Inv_VendorInSumQry_C(Frm_Sys_Condition).InitForm(AdODataSet1.Connection,UserCode);
  Act_Filter.Execute;
end;

procedure TFrm_Inv_VendorInSumQry.Act_LookExecute(Sender: TObject);
begin
  //inherited;
  with AdODataSet1 do
  begin
  Frm_Inv_VendorInSumQry_D:=TFrm_Inv_VendorInSumQry_D.Create(Application);
  Frm_Inv_VendorInSumQry_D.SetParam(UserCode,fieldbyname('VendorCode').asstring,
                                    fieldbyname('VendorName').asstring,
                                    fieldbyname('ItemCode').asstring,
                                    fieldbyname('ItemName').asstring,
                                    SendCondition,
                                    FconditionHint+'|');
  Frm_Inv_VendorInSumQry_D.SetSysParam(UserCode,'Inv','1234','2001.06.21');
  Frm_Inv_VendorInSumQry_D.InitForm(Connection,FShow);
  //Frm_Inv_VendorInSumQry_D.ShowModal;
  end;
end;

procedure TFrm_Inv_VendorInSumQry.Act_PrintExecute(Sender: TObject);
var
  WHCode_InvBillNo:String;
begin
  with TFrm_Inv_BillListPrint_C.Create(Self) do   //新建的Form
  begin
    if ShowModal=mrOk then
    begin
      if RdBtn_PrintList.Checked then    //打印列表时，用基类提供的打印功能
        inherited
      else         //否则打印当前列表中的所有单据
      begin
        AdoQry_Main.First;
        while not AdoQry_Main.Eof do
        begin
          if WHCode_InvBillNo<>AdoQry_Main.fieldbyname('WHCode').AsString
            +AdoQry_Main.fieldbyname('InvBillNo').AsString then           //列表中列出的不是单据头，而是单据行
          begin
            WHCode_InvBillNo:=AdoQry_Main.fieldbyname('WHCode').AsString
              +AdoQry_Main.fieldbyname('InvBillNo').AsString;
            BillPrint(DBConnect,AdoQry_Main.fieldbyname('WHCode').AsString
              ,AdoQry_Main.fieldbyname('InvBillNo').AsString
              ,'0299',ModuleCode,False,False,True,'');
           //注意Param1是当前查询的单据类型，我们不要用参数，直接书写单据类型代码，例如“其它出库单据列表查询”，就是 0299
          end;
          AdoQry_Main.Next;
        end;
      end;
    end;
    Release;
  end;
end;

procedure TFrm_Inv_VendorInSumQry.Act_FilterExecute(Sender: TObject);
var
  sql_txt:string;
  i:integer;
begin
  //inherited;
  if Frm_Sys_Condition<>nil then
  begin
    if Frm_Sys_Condition.Showmodal=mrOk then
    begin
      with AdODataSet1 do
      begin
        AdODataSet1.Close;
        CommandTimeout:=0;
        sql_txt:=SelectFromSQL+' where '+Fcondition+' group by '+GroupByFields+
                     ' Order by '+OrderByfields;
        CommandText:=sql_txt;
        open;
      end;
    end;
  end;
end;

procedure TFrm_Inv_VendorInSumQry.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;
  i:=ExtPrintReport.Headers.indexof('Amount');
  if i>=0 then
  begin
    ExtPrintReport.Headers[i].sum:=True;
    ExtPrintReport.Headers[i].style:=dSconcise;
  end;
  i:=ExtPrintReport.Headers.indexof('NotaxAmount');
  if i>=0 then
  begin
    ExtPrintReport.Headers[i].sum:=True;
    ExtPrintReport.Headers[i].style:=dSconcise;
  end;
  ExtprintReport.Foot1:='制　表：'+userName+'        审核：';

end;
procedure TFrm_Inv_VendorInSumQry.AdODataSet1AfterOpen(DataSet: TDataSet);
var
  sql_txt:string;
  i:integer;
begin
  inherited;
  sumAmount:=0;
  sumAmount2:=0;
  //计算AdODATASET的含税金额、未税金额
  If Not AdODataSet1.IsEmpty then
  begin
    AdODataSet1.First;
    for I:=0 to AdODataSet1.RecordCount-1 do
    begin
      SumAmount:=SumAmount+AdODataSet1.fieldbyname('Amount').asFloat;
      SumAmount2:=SumAmount2+AdODataSet1.fieldbyname('NotaxAmount').asFloat;
      AdODataSet1.next;
    end;
    AdODataSet1.First;
  end;
  Lbl_Condition.Caption:=FConditionHint;
  Lbl_Condition.Caption:=Lbl_Condition.Caption+'|'+FVendor;
  edt_Amount.text:=FormatFloat('#.##',SumAmount);
  edt_Amount2.text:=FormatFloat('#.##',SumAmount2);
end;

end.