bas_batchlist_qry.pas

一个MRPII系统源代码版本

unit Bas_BatchList_Qry;

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, jpeg;

Type
  TFrm_Bas_BatchList_Qry = Class(TFrm_Base_Qry)
    AdoQry_tmp1: TAdoQuery;
    Label1: TLabel;
    DBText1: TDBText;
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainBatchno: TStringField;
    AdoQry_MainBatchName: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainBackflush: TIntegerField;
    AdoQry_MainusefulCtrl: TIntegerField;
    AdoQry_Mainusefuldate: TIntegerField;
    AdoQry_MainBatchstatus: TIntegerField;
    AdoQry_MaInvendorCode: TStringField;
    AdoQry_MaInvendorBatchno: TStringField;
    AdoQry_MainBatchlineremArk: TStringField;
    AdoQry_MainFirstindate: TDateTimeField;
    AdoQry_MainCurrentindate: TDateTimeField;
    AdoQry_MainFirstoutdate: TDateTimeField;
    AdoQry_MainCurrentoutdate: TDateTimeField;
    AdoQry_MainBatchInv: TFloatField;
    AdoQry_Mainusedate: TIntegerField;
    AdoQry_MainInvQty: TFloatField;
    procedure Act_FilterExecute(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure AdoQry_MainCalcFields(DataSet: TDataSet);
  private
    { Private declarations }
  public
    acondition:string;
    aVendorCode:string;
    procedure initform(AdOConnection:TAdOConnection;ReadOnly:boolean);Override;
    { Public declarations }
  end;

var
  Frm_Bas_BatchList_Qry: TFrm_Bas_BatchList_Qry;

implementation

uses Bas_BatchList_Qry_C;
{$R *.DFM}


procedure TFrm_Bas_BatchList_Qry.initform(AdOConnection:TAdOConnection;ReadOnly:boolean);
begin
  inherited;
  acondition:='';
//  PriceFields:='InvLMAmount,InvInAmount,InvOutAmount,InvBlncAmount,InvBillNoTaxAmount';
  SelectFromSQL:='select  Batch.ItemCode+'''+' '+'''+Item.ItemName ItemCode,Batchline.Batchno'+
           ',Batchline.BatchName,Item.UomName,Batch.Backflush,'+
           'Batchline.usefulCtrl,Batchline.usefuldate,Batchline.Batchstatus,'+
           'Batchline.VendorCode+'''+' '+'''+Vendor.VendorName VendorCode,'+
           'Batchline.VendorBatchno,Batchline.BatchlineremArk,'+
           'Batchline.Firstindate,Currentindate,Firstoutdate,'+
           'Currentoutdate,BatchInv,BatchCurrentInv.InvQty '+
           'from Batch join Batchline on Batch.Batchid=Batchline.Batchid '+
           'left join (select Batchno,sum(InvQty) InvQty from BatchCurrentInv group by Batchno) BatchCurrentInv'+
           ' on BatchCurrentInv.Batchno=Batchline.Batchno '+
              'join Vendor on Batchline.VendorCode=Vendor.VendorCode '+
              'join (select Item.*,Uom.UomName from Item join Uom on '+
              'Item.UomCode=Uom.UomCode) Item on Item.ItemCode=Batch.ItemCode ';

  OrderByFields:='ItemCode';
  lbl_Order.Caption:='物料标识';
  Frm_Sys_Condition:=TFrm_Bas_BatchList_Qry_C.Create(Self);
  TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).InitForm(AdoQry_Main.Connection,UserCode,LoginDate);
  getdata;
//  Act_Filter.Execute;
//  lbl_Month.Caption:=TFrm_Inv_OpVendorSum_C(Frm_Sys_Condition).medt_Month.text;
end;

procedure TFrm_Bas_BatchList_Qry.Act_FilterExecute(Sender: TObject);
var
  condstr:string;
begin
  if Frm_Sys_Condition.ShowModal=mrOk then
  begin
    if (TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).cmbx_Batchstatus.Text='打开') then
      begin
      condstr:='where Batchline.Batchstatus=0 ';
      if (TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).Extedt_ItemCode.Text<>'') then
          condstr:=condstr+'and Item.ItemCode='''+TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).Extedt_ItemCode.Text+'''';
      if (TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).edt_Batchno.Text<>'') then
          condstr:=condstr+' and Batchline.Batchno='''+TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).edt_Batchno.Text+'''';
      end;
    if TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).cmbx_Batchstatus.Text='关闭' then
      begin
      condstr:='where Batchline.Batchstatus=1 ';
      if (TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).Extedt_ItemCode.Text<>'') then
          condstr:=condstr+'and Item.ItemCode='''+TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).Extedt_ItemCode.Text+'''';
      if (TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).edt_Batchno.Text<>'') then
          condstr:=condstr+' and Batchline.Batchno='''+TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).edt_Batchno.Text+'''';
      end;
    if TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).cmbx_Batchstatus.Text='全部' then
      begin
      condstr:='where (Batchline.Batchstatus=0 or Batchline.Batchstatus=1) ';
      if (TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).Extedt_ItemCode.Text<>'') then
          condstr:=condstr+'and Item.ItemCode='''+TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).Extedt_ItemCode.Text+'''';
      if (TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).edt_Batchno.Text<>'') then
          condstr:=condstr+' and Batchline.Batchno='''+TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).edt_Batchno.Text+'''';
      end;
    if TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).cmbx_Batchstatus.Text='' then
      begin
      condstr:='where (Batchline.Batchstatus=0 or Batchline.Batchstatus=1) ';
      if (TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).Extedt_ItemCode.Text<>'') then
          condstr:=condstr+'and Item.ItemCode='''+TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).Extedt_ItemCode.Text+'''';
      if (TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).edt_Batchno.Text<>'') then
          condstr:=condstr+' and Batchline.Batchno='''+TFrm_Bas_BatchList_Qry_C(Frm_Sys_Condition).edt_Batchno.Text+'''';
      end;
    Lbl_Condition.Caption:=Frm_Sys_Condition.ConditionHint;
    AdoQry_Main.DisableControls;
    AdoQry_Main.Close;
    AdoQry_Main.SQL.clear;
    SelectFromSQL:='select  Batch.ItemCode+'''+' '+'''+Item.ItemName ItemCode,Batchline.Batchno'+
           ',Batchline.BatchName,Item.UomName,Batch.Backflush,'+
           'Batchline.usefulCtrl,Batchline.usefuldate,Batchline.Batchstatus,'+
           'Batchline.VendorCode+'''+' '+'''+Vendor.VendorName VendorCode,'+
           'Batchline.VendorBatchno,Batchline.BatchlineremArk,'+
           'Batchline.Firstindate,Currentindate,Firstoutdate,'+
           'Currentoutdate,BatchInv,BatchCurrentInv.InvQty '+
           'from Batch join Batchline on Batch.Batchid=Batchline.Batchid '+
           'left join (select Batchno,sum(InvQty) InvQty from BatchCurrentInv group by Batchno) BatchCurrentInv '+
           ' on BatchCurrentInv.Batchno=Batchline.Batchno '+
             'join Vendor on Batchline.VendorCode=Vendor.VendorCode '+
             'join (select Item.*,Uom.UomName from Item join Uom on '+
             'Item.UomCode=Uom.UomCode) Item on Item.ItemCode=Batch.ItemCode ';

    AdoQry_Main.SQL.Text:=SelectFromSQL+condstr;
    AdoQry_Main.Open;
    AdoQry_Main.Sort:=OrderByFields;
    AdoQry_Main.EnableControls;
  end;
end;

procedure TFrm_Bas_BatchList_Qry.DBGridEhGetCellParams(Sender: TObject;
  Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin
  inherited;
  {if (TDBGridEh(Sender).DataSource.DataSet.fieldbyname('Orderfield').asstring='1') and
      (column.FieldName='ItemCode1') then
     afont.Color :=clred; }

end;

procedure TFrm_Bas_BatchList_Qry.AdoQry_MainCalcFields(DataSet: TDataSet);
begin
  inherited;
  if (AdoQry_Main.fieldbyname('usefulCtrl').asstring='1') and
     (not AdoQry_Main.fieldbyname('Firstindate').IsNull) then
  AdoQry_Main.fieldbyname('usedate').asinteger:=DateTimeToTimeStamp(now).date-DateTimeToTimeStamp(AdoQry_Main.fieldbyname('Firstindate').asdatetime).date;
end;

end.