inv_opoutbilllistqry.pas

一个MRPII系统源代码版本

unit Inv_OpOutBillListQry;

Interface

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

Type
  TFrm_Inv_OpOutBillListQry = Class(TFrm_Base_Qry)
    Label1: TLabel;
    DBText1: TDBText;
    tmp_grid: TDBGridEh;
    Label2: TLabel;
    Edit1: TEdit;
    procedure Act_LookExecute(Sender: TObject);
    procedure DBGridEhDblClick(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure Act_FilterExecute(Sender: TObject);
    procedure Act_LocateExecute(Sender: TObject);
    procedure Act_OrderExecute(Sender: TObject);
    procedure AdoQueryAfterOpen(DataSet: TDataSet);
    procedure FormDestroy(Sender: TObject);

  private
    { Private declarations }
    FShowExtendColum:BooLean;
    SelectFromSQL1,SelectFromSQL2:string;
    procedure SendDataToForm;
  public
    isop:boolean;
    BillType:string;
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    procedure getdata;Override;
  end;

var
  Frm_Inv_OpOutBillListQry: TFrm_Inv_OpOutBillListQry;

implementation

uses Inv_OpOutBillListQry_C, Inv_OpOutBillQry, Sys_Global, Inv_BillListPrint_C,
  Inv_Global;

{$R *.DFM}

procedure TFrm_Inv_OpOutBillListQry.getdata ;
begin
  SelectFromSQL:='';
  if not isop then
  begin
    SelectFromSQL:=SelectFromSQL2;
  end
  else
  begin
    SelectFromSQL:=SelectFromSQL1;
  end;
  inherited;
end;
procedure TFrm_Inv_OpOutBillListQry.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
begin
  inherited;
  BillType:=Param1;
  FShowExtendColum:=ShowExtendColumn;
  if(not ShowExtendColumn)then
  begin
    DBGridEh.Columns.Delete(11);
    DBGridEh.Columns.Delete(10);
  end
  else
  begin
    PriceFields:='InvBillNoTaxPrice,';
    AmountFields:='InvBillNoTaxAmount,';
    label2.Visible:=True;
    edit1.Visible:=True;
  end;

  SelectFromSQL1:=' select '
                +' InvOutBill.InvBillDate,InvOutBill.InvBillNo,'//--日期,单据号
                +' InvOutBill.VendorCode+'' ''+Vendor.VendorName As VendorName,'//--供应商
                +' InvOutBill.PoNO As MONo,InvOutBillLine.POLineNo As MoLineNo,'//--采购订单号,采购订单行号
                +' InvOutBill.whCode+'' ''+Warehouse.whName WhCodeName,'
                +' InvOutBill.whCode,'
                +' InvOutBill.OverPlan,'
                +' InvOutBill.WhPositionCode+'' ''+WhPosition.WhPositionName WhPosition, '
                +' InvOutBillLine.ItemCode+'' ''+Item.ItemName ItemFlag,'//--物料代码,物料描述
                +' InvOutBillLine.ItemCode,'
                +' Item.ItemName,'
                +' Uom.UomName,' //--计量单位
                +' InvOutBillLine.InvBillQty,'//--数量  InvOutBill.WhCode,//--仓库编码
                +' Warehouse.PriceType,'//--计价类型
                +' case when Warehouse.PriceType=''1'' then'
                +'   StandardPrice.SPPrice '   //--按计划核算 单价
                +' else '
                +'  InvOutBillLine.InvBillNoTaxPrice '//--移动加权平均--单价-根据仓库编码取值
                +' end InvBillNoTaxPrice, '
                +' case when Warehouse.PriceType=''1''  then '
                +'  InvOutBillLine.InvBillqty*StandardPrice.SPPrice '
                +' else '
                +'   InvOutBillLine.InvBillNoTaxAmount '//--,金额
                +' end InvBillNoTaxAmount,InvOutBill.wh_EmployeeCode+'' ''+Employee.EmployeeName as EmployeeCodeName, '// --录入员
                +' InvOutBillLine.BatchNo,Warehouse.whCode as whCode,  '//--批次号
                +' InvOutBillLine.BillLineReMArk '
                +' From InvOutBillLine '
                +' join InvOutBill on InvOutBillLine.InvBillId=InvOutBill.InvBillId '
                +      ' And (InvOutBill.BillTypeCode='''+Param1+''') '
                + 'left join StandardPrice on InvOutBill.InvBillMonth=StandardPrice.spstArtMonth '
                +   ' And  InvOutBillline.ItemCode=StandardPrice.ItemCode '
                +' left outer join Vendor on InvOutBill.VendorCode=Vendor.VendorCode  '
                +' left outer join Employee on InvOutBill.wh_EmployeeCode=Employee.EmployeeCode  '
                +' left outer join Warehouse on InvOutBill.whCode=Warehouse.whCode  '
                +' left outer join Item on InvOutBillLine.ItemCode=Item.ItemCode '
                +' left outer join Uom  on Item.UomCode=Uom.UomCode '
                +' Left Outer Join WhPosition on WhPosition.WhPositionCode=InvOutBill.WhPositionCode and WhPosition.whCode=InvOutBill.whCode';

   SelectFromSQL2:=' select  InvOutBill.InvBillDate,InvOutBill.InvBillNo,'+
                   '         InvOutBill.VendorCode+'' ''+Vendor.VendorName VendorName,'+
                   '         InvOutBill.PoNO As MONo,'+
                   '         InvOutBill.ItemCode+'' ''+Item.ItemName ItemFlag,'+//--物料代码,物料描述
                   '         InvOutBill.ItemCode,'+
                   '         Uom.UomName,Item.ItemName,'+ //--计量单位
                   '         InvOutBill.whCode+'' ''+Warehouse.whName WhCodeName,'+
                   '         InvOutBill.whCode,'+
                   '         InvOutBill.OverPlan,'+
                   '         InvOutBill.WhPositionCode +'' ''+WhPosition.WhPositionName WhPosition, '+
                   '         InvOutBill.InvBillQty,'+
                   '         InvOutBill.wh_EmployeeCode+'' ''+Employee.EmployeeName as EmployeeCodeName'+
                 ' from (select InvBilldate,ItemCode,InvBillMonth,InvBillid,InvBillno,sum(InvBillqty) InvBillqty,VendorCode,pono,whCode,wh_EmployeeCode,WhPositionCode,OverPlan from '+
                 '   (select  i1.InvBillid,i1.ItemCode,InvBillno,i1.polineno,i1.InvBillqty,i2.InvBilldate,i2.InvBillMonth,i2.VendorCode,i2.pono,i2.whCode,i2.wh_EmployeeCode,i2.WhPositionCode,i2.OverPlan'+
                 '      from InvOutBillline i1 join InvOutBill i2 on i1.InvBillid=i2.InvBillid where i2.BillTypeCode='''+Param1+''') Billout'+
                 '    group by InvBilldate,ItemCode,InvBillMonth,InvBillid,InvBillno,VendorCode,pono,whCode,wh_EmployeeCode,WhPositionCode,OverPlan) InvOutBill'+
                 '    left Outer join Warehouse on InvOutBill.whCode=Warehouse.whCode'+
                 '    left Outer join Vendor on InvOutBill.VendorCode=Vendor.VendorCode'+
                 ' left outer join Item on InvOutBill.ItemCode=Item.ItemCode '+
                 ' left outer join Uom  on Item.UomCode=Uom.UomCode '+
                 '    left Outer join Employee on InvOutBill.wh_EmployeeCode=Employee.EmployeeCode'+
                 '    Left Outer Join WhPosition on WhPosition.WhPositionCode=InvOutBill.WhPositionCode and WhPosition.whCode=InvOutBill.whCode';

  OrderByFields:='InvBillDate,InvBillNo';
  Frm_Sys_Condition:=TFrm_Inv_OpOutBillListQry_C.Create(Self);
  TFrm_Inv_OpOutBillListQry_C(Frm_Sys_Condition).InitForm(DBConnect,UserCode);
  Act_Filter.Execute;
end;

procedure TFrm_Inv_OpOutBillListQry.SendDataToForm;
var
 InvBillNo:string;
 WHCode:string;
 InvBillTypeCode:string;
begin
   if (AdoQry_Main.eof) and (AdoQry_Main.Bof)  then
     DispInfo('没有可用的单据!   ',3)
   else
     begin
       //创建窗体
       Application.CreateForm(TFrm_Inv_OpOutBillQry,Frm_Inv_OpOutBillQry);
       //传递变量
       InvBillno:=AdoQry_Main.fieldbyname('InvBillNo').ASSTRING;  //单据号
       WHCode:=AdoQry_Main.fieldbyname('WHCode').ASSTRING;        //仓库代码
       InvBillTypeCode:=Param1;
       Frm_Inv_OpOutBillQry.Getvar(InvBillno,WHCode,InvBillTypeCode);
       //显示信息
       Frm_Inv_OpOutBillQry.InitForm(AdoQry_Main.Connection,FShowExtendColum);
     end;
 end;
procedure TFrm_Inv_OpOutBillListQry.Act_LookExecute(Sender: TObject);
begin
  inherited;
  SendDataToForm;
end;

procedure TFrm_Inv_OpOutBillListQry.DBGridEhDblClick(Sender: TObject);
begin
  inherited;
  SendDataToForm;
end;

procedure TFrm_Inv_OpOutBillListQry.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
              ,'0202',ModuleCode,False,False,True,'');
           //注意Param1是当前查询的单据类型，我们不要用参数，直接书写单据类型代码，例如“其它出库单据列表查询”，就是 0299
          end;
          AdoQry_Main.Next;
        end;
      end;
    end;
    Release;
  end;
end;

procedure TFrm_Inv_OpOutBillListQry.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('OverPlan').asstring='1' then
      AFont.color:=clBlue
    else
      AFont.Color:=clBlack;
  end;
end;

procedure TFrm_Inv_OpOutBillListQry.Act_FilterExecute(Sender: TObject);
var
  i,j:integer;
begin
  inherited;

  if not isop then
  begin
    for i:=0 to dbgrideh.FieldCount-1 do
    begin
      if dbgrideh.Columns[i].FieldName='MoLineNo' then
         dbgrideh.Columns[i].Visible :=False;
      if dbgrideh.Columns[i].FieldName='BatchNo' then
         dbgrideh.Columns[i].Visible :=False;
      if dbgrideh.Columns[i].FieldName='BillLineReMArk' then
         dbgrideh.Columns[i].Visible :=False;
      //if (FShowExtendColum)and(dbgrideh.Columns[i].FieldName='InvBillNoTaxPrice')then
      if (dbgrideh.Columns[i].FieldName='InvBillNoTaxPrice')then
         dbgrideh.Columns[i].Visible :=False;
      //if (FShowExtendColum)and(dbgrideh.Columns[i].FieldName='InvBillNoTaxAmount')then
      if (dbgrideh.Columns[i].FieldName='InvBillNoTaxAmount')then
         dbgrideh.Columns[i].Visible :=False;
      if dbgrideh.Columns[i].FieldName='WhCodeName' then
         dbgrideh.Columns[i].Width:=100;
      if dbgrideh.Columns[i].FieldName='WhPosition' then
         dbgrideh.Columns[i].Width:=120;
    end;
  end
  else
  begin
    for i:=0 to dbgrideh.FieldCount-1 do
    begin
      if dbgrideh.Columns[i].FieldName='MoLineNo' then
         dbgrideh.Columns[i].Visible :=True;
      if dbgrideh.Columns[i].FieldName='BatchNo' then
         dbgrideh.Columns[i].Visible :=True;
      if dbgrideh.Columns[i].FieldName='BillLineReMArk' then
         dbgrideh.Columns[i].Visible :=True;
      //if (FShowExtendColum)and(dbgrideh.Columns[i].FieldName='InvBillNoTaxPrice')then
      if (dbgrideh.Columns[i].FieldName='InvBillNoTaxPrice')then
         dbgrideh.Columns[i].Visible :=True;
      //if (FShowExtendColum)and(dbgrideh.Columns[i].FieldName='InvBillNoTaxAmount')then         
      if (dbgrideh.Columns[i].FieldName='InvBillNoTaxAmount')then
         dbgrideh.Columns[i].Visible :=True;
      if dbgrideh.Columns[i].FieldName='WhCodeName' then
         dbgrideh.Columns[i].Width:=60;
      if dbgrideh.Columns[i].FieldName='WhPosition' then
         dbgrideh.Columns[i].Width:=60;
    end;
  end;
  j:=0;
  tmp_grid.Columns.clear;
  for i:=0 to dbgrideh.Columns.Count-1 do
  begin
    if dbgrideh.Columns[i].Visible then
    begin
       tmp_grid.Columns.Add;
       tmp_grid.Columns[j].Assign(dbgrideh.columns[i]);
       j:=j+1;
    end;
  end;
end;

procedure TFrm_Inv_OpOutBillListQry.Act_LocateExecute(Sender: TObject);
begin
  if isop then
     Frm_Sys_QuickLocate.SetSrcGrid(dbgrideh)
  else
     Frm_Sys_QuickLocate.SetSrcGrid(tmp_grid);
  Frm_Sys_QuickLocate.ShowModal;
  if Frm_Sys_QuickLocate.ModalResult=mrok then
  begin
    AdoQry_Main.Locate(Frm_Sys_QuickLocate.LocateFieldName,Frm_Sys_QuickLocate.LocateFieldValue,[loPArtialKey]);
  end;

end;

procedure TFrm_Inv_OpOutBillListQry.Act_OrderExecute(Sender: TObject);
begin
  if not isop then
    dbgrideh:=tmp_grid;
  inherited;
  {if isop then
     Frm_Sys_QuickLocate.SetSrcGrid(dbgrideh)
  else
     Frm_Sys_QuickLocate.SetSrcGrid(tmp_grid);
  Frm_Sys_QuickLocate.Showmodal;
  if Frm_Sys_QuickLocate.ModalResult=mrok then
  begin
    DBGridEh.DataSource.DataSet.Locate(Frm_Sys_QuickLocate.LocateFieldName,Frm_Sys_QuickLocate.LocateFieldValue,[loPArtialKey]);
  end;}
end;

procedure TFrm_Inv_OpOutBillListQry.AdoQueryAfterOpen(DataSet: TDataSet);
var
  //sql_txt:string;
  i:integer;
  sumAmount:real;
begin
  inherited;
  sumAmount:=0;
  //汇总金额
  If Not AdoQry_Main.IsEmpty then
  begin
    AdoQry_Main.First;
    for I:=0 to AdoQry_Main.RecordCount-1 do
    begin
      SumAmount:=SumAmount+AdoQry_Main.fieldbyname('InvBillnotaxAmount').asFloat;
      AdoQry_Main.next;
    end;
    AdoQry_Main.First;
    edit1.text:=FormatFloat('#.##',SumAmount);
  end;
end;


procedure TFrm_Inv_OpOutBillListQry.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Inv_OpOutBillListQry:=nil;
end;

end.