inv_billlistqry_c.pas

一个MRPII系统源代码版本

unit Inv_BillListQry_C;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Condition, StdCtrls, Db, AdODB, CheckLst, ExtEdit, Mask;

Type
  TFrm_Inv_BillListQry_C = Class(TFrm_Base_Condition)
    Label2: TLabel;
    Label3: TLabel;
    Label6: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    MEdt_StArtDate: TMaskEdit;
    MEdt_EndDate: TMaskEdit;
    ExtEdt_ItemCode: TExtEdit;
    Lbl_ItemName: TLabel;
    CmBx_Warehouse: TComboBox;
    Label1: TLabel;
    Lbl_UomName: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label7: TLabel;
    CmBx_Object: TComboBox;
    ExtEdt_Object: TExtEdit;
    Lbl_Object: TLabel;
    Label10: TLabel;
    CmBx_OutIn: TComboBox;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    procedure btn_okClick(Sender: TObject);
    procedure FindItemCode(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure CmBx_ObjectChange(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_Inv_BillListQry_C: TFrm_Inv_BillListQry_C;

implementation

uses Sys_Global;

{$R *.DFM}
procedure TFrm_Inv_BillListQry_C.btn_okClick(Sender: TObject);
var
  StrTemp1,StrTemp2,StrTemp3,StrTemp4,StrTemp5,WHCodeStr,ObjectStr:String;
begin
  inherited;
  Condition:='';
  if CheckBox1.Checked then
  begin
    StrTemp4:=' And InvInBill.realBillflag=1';
  end
  else
    StrTemp4:='';
    strTemp4:=strTemp4+iifstring(checkbox2.checked,' and InvInBill.printtimes<1 ','');
  if checkbox2.Checked then 
    strTemp5:='  and InvOutBill.printtimes<1 '
  else StrTemp5:='';
  if ExtEdt_ItemCode.Text<>'' then
  begin
    StrTemp1:=' And InvInBillLine.ItemCode='''+ExtEdt_ItemCode.Text+'''';
    StrTemp2:=' And InvOutBillLine.ItemCode='''+ExtEdt_ItemCode.Text+'''';
    StrTemp3:=' And OnCheckBillLine.ItemCode='''+ExtEdt_ItemCode.Text+'''';

    ConditionHint:='仓库： '+CmBx_Warehouse.Text+'/物料： '
      +ExtEdt_ItemCode.Text+' '+Lbl_ItemName.Caption+' '+Lbl_UomName.Caption;
  end
  else
  begin
    StrTemp1:='';
    StrTemp2:='';
    StrTemp3:='';

    ConditionHint:='仓库： '+CmBx_Warehouse.Text;
  end;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Delete #BillListQry';
  AdoQry_Tmp.ExecSQL;

  //把入库单数据插入临时表
  if CmBx_Warehouse.Text<>'全部仓库' then
    WHCodeStr:=' And InvInBill.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
  else
    WHCodeStr:='';
  
  if CmBx_Object.Text='全部' then
  begin
    ObjectStr:='';
  end
  else if CmBx_Object.Text='供应商' then
  begin
    ObjectStr:=' And InvInBill.VendorCode='''+ExtEdt_Object.Text+'''';
  end
  else if CmBx_Object.Text='部门' then
  begin
    ObjectStr:=' And InvInBill.DeptCode='''+ExtEdt_Object.Text+'''';
  end
  else if CmBx_Object.Text='客户' then
  begin
    ObjectStr:=' And InvInBill.CustomerCode='''+ExtEdt_Object.Text+'''';
  end;
  if ExtEdt_Object.Text='' then
  begin
    ObjectStr:='';
  end;

  ///专门处理ConditionHint----wxp
  ConditionHint:='查询日期:'+MEdt_StArtDate.Text+'-'+MEdt_EndDate.Text
                +',仓库:'+CmBx_Warehouse.Text;
  if ExtEdt_ItemCode.Text<>'' then
    ConditionHint:=ConditionHint+',物料:'+ExtEdt_ItemCode.text+' '+Lbl_ItemName.Caption
                +',单位:'+Lbl_UomName.Caption;
  ConditionHint:=ConditionHint+',出入库对象:'+CmBx_Object.Text;
  if ExtEdt_Object.Text<>''then
  ConditionHint:=ConditionHint+',对象标识:'+CmBx_Object.Text+' '+Lbl_Object.Caption;
  if CmBx_OutIn.Text<>'' then
  ConditionHint:=ConditionHint+',出入库方向:'+CmBx_OutIn.Text;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #BillListQry'
    +' Select convert(varchAr(10),InvInBill.InvBillDate,102) As DateSting'
    +',InvInBill.BillTypeCode As BillTypeCode'
    +',InvInBill.InvBillNo As InvBillNo'

    +',Case When InvInBill.moNo is Null Then '''' Else InvInBill.moNo end+'
    +' Case When InvInBill.PoNo is Null Then '''' Else InvInBill.PoNo end+'' ''+'
    +' Case When InvInBillLine.MoLineNo is Null Then '''' Else Str(InvInBillLine.MoLineNo,2) end+'
    +' Case When InvInBillLine.PoLineNo is Null Then '''' Else Str(InvInBillLine.PoLineNo,2) end'
    +' As MoPoNo'
    {
    +',Case When InvInBill.OPBill=1 Then ''无订单委外入库'''
    +' Else BillType.BillTypeName end+'' ''+(Case When InvInBill.VendorCode is Null'
    +' Then '''' Else InvInBill.VendorCode+'' ''+Vendor.VendorName end)'
    +'+(Case When InvInBill.CustomerCode is Null'
    +' Then '''' Else InvInBill.CustomerCode+'' ''+Customer.CustomerName end)'
    +'+(Case When InvInBill.DeptCode is Null'
    +' Then '''' Else InvInBill.DeptCode+'' ''+Dept.DeptName end)'
    }
    +',Case When InvInBill.OPBill=1 Then ''无订单委外入库,送货单号:''+InvInBill.VendorBillno '
    +' Else BillType.BillTypeName+'',送货单号:''+InvInBill.VendorBillno end '
    +' As InvBillRemArk'
    +',InvInBillLine.InvBillQty As InvBillQty'
    +',Null As OInvBillQty'
    +',InvInBill.InvBillCreateTime As InvBillCreateTime'

    +',InvInBillLine.ItemCode As ItemCode'
    +',Item.ItemName As ItemName'
    +',InvInBillLine.ItemCode+Item.ItemName As ItemCodeName'
    +',InvInBill.WHCode As WHCode'
    +',InvInBillLine.InvBillNoTaxPrice'
    +',InvInBillLine.InvBillNoTaxAmount'
    +',InvInBillLine.InvBillPrice'
    +',InvInBillLine.InvBillAmount'
    +' From InvInBillLine join InvInBill on InvInBillLine.InvBillId=InvInBill.InvBillId'
    +' join Item on InvInBillLine.ItemCode=Item.ItemCode'
    +' join BillType on InvInBill.BillTypeCode=BillType.BillTypeCode'
    +' Left join Vendor on InvInBill.VendorCode=Vendor.VendorCode'
    +' Left join Customer on InvInBill.CustomerCode=Customer.CustomerCode'
    +' Left join Dept on InvInBill.DeptCode=Dept.DeptCode'
    +' Where InvInBill.InitData=0'
    +WHCodeStr
    +StrTemp1
    +StrTemp4
    +ObjectStr
    +' And ((InvInBill.BillTypeCode in (''0101'',''0102'',''0199''))'
    +' Or ((InvInBill.BillTypeCode in (''0104'',''0105'',''0103''))'
    +' And (InvInBill.InvBillWHChck=1)))'

    +' And InvInBill.InvBillDate>='''+MEdt_StArtDate.Text+''''
    +' And InvInBill.InvBillDate<='''+MEdt_EndDate.Text+'''';
  if CmBx_OutIn.Text<>'出库' then
    AdoQry_Tmp.ExecSQL;

  if CmBx_Warehouse.Text<>'全部仓库' then
    WHCodeStr:=' And InvOutBill.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
  else
    WHCodeStr:='';
  if CmBx_Object.Text='全部' then
  begin
    ObjectStr:='';
  end
  else if CmBx_Object.Text='供应商' then
  begin
    ObjectStr:=' And InvOutBill.VendorCode='''+ExtEdt_Object.Text+'''';
  end
  else if CmBx_Object.Text='部门' then
  begin
    ObjectStr:=' And InvOutBill.DeptCode='''+ExtEdt_Object.Text+'''';
  end
  else if CmBx_Object.Text='客户' then
  begin
    ObjectStr:=' And InvOutBill.CustomerCode='''+ExtEdt_Object.Text+'''';
  end;
  if ExtEdt_Object.Text='' then
  begin
    ObjectStr:='';
  end;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #BillListQry'
    +' Select convert(varchAr(10),InvOutBill.InvBillDate,102) As DateSting'
    +',InvOutBill.BillTypeCode As BillTypeCode'
    +',InvOutBill.InvBillNo As InvBillNo'

    +',Case When InvOutBill.moNo is Null Then '''' Else InvOutBill.moNo end+'
    +' Case When InvOutBill.PoNo is Null Then '''' Else InvOutBill.PoNo end+'' ''+'
    +' Case When InvOutBillLine.MoLineNo is Null Then '''' Else Str(InvOutBillLine.MoLineNo,2) end+'
    +' Case When InvOutBillLine.PoLineNo is Null Then '''' Else Str(InvOutBillLine.PoLineNo,2) end'
    +' As MoPoNo'
{
    +',Case When InvOutBill.OPBill=1 Then ''无订单委外发料'''
    +' Else BillType.BillTypeName end+'' ''+(Case When InvOutBill.VendorCode is Null'
    +' Then '''' Else InvOutBill.VendorCode+'' ''+Vendor.VendorName end)'
    +'+(Case When InvOutBill.CustomerCode is Null'
    +' Then '''' Else InvOutBill.CustomerCode+'' ''+Customer.CustomerName end)'
    +'+(Case When InvOutBill.DeptCode is Null'
    +' Then '''' Else InvOutBill.DeptCode+'' ''+Dept.DeptName end)'
 }
    +',Case When InvOutBill.OPBill=1 Then ''无订单委外发料'''
    +' Else BillType.BillTypeName end '
    +' As InvBillRemArk'
    +',Null As InvBillQty'
    +',InvOutBillLine.InvBillQty As OInvBillQty'
    +',InvOutBill.InvBillCreateTime As InvBillCreateTime'
    +',InvOutBillLine.ItemCode As ItemCode'
    +',Item.ItemName As ItemName'
    +',InvOutBillLine.ItemCode+Item.ItemName As ItemCodeName'
    +',InvOutBill.WHCode As WHCode'
    +',InvOutBillLine.InvBillNoTaxPrice'
    +',InvOutBillLine.InvBillNoTaxAmount'
    +',InvOutBillLine.InvBillPrice'
    +',InvOutBillLine.InvBillAmount'
    +' From InvOutBillLine join InvOutBill on InvOutBillLine.InvBillId=InvOutBill.InvBillId'
    +' join Item on InvOutBillLine.ItemCode=Item.ItemCode'
    +' join BillType on InvOutBill.BillTypeCode=BillType.BillTypeCode'
    +' Left join Vendor on InvOutBill.VendorCode=Vendor.VendorCode'
    +' Left join Customer on InvOutBill.CustomerCode=Customer.CustomerCode'
    +' Left join Dept on InvOutBill.DeptCode=Dept.DeptCode'

    +' Where InvOutBill.InvBillDate>='''+MEdt_StArtDate.Text+''''
    +' And InvOutBill.InvBillDate<='''+MEdt_EndDate.Text+''''
    +WHCodeStr
    +StrTemp2
    +StrTemp5
    +ObjectStr
    +' And ((InvOutBill.BillTypeCode in (''0202'',''0299''))'
    +' Or ((InvOutBill.BillTypeCode in (''0201'',''0203'',''0204''))'
    +' And (InvOutBill.InvBillWHChck=1)))';
  if CmBx_OutIn.Text<>'入库' then
    AdoQry_Tmp.ExecSQL;

  if CmBx_Warehouse.Text<>'全部仓库' then
    WHCodeStr:=' And OnCheckBill.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
  else
    WHCodeStr:='';
  if CmBx_Object.Text='全部' then
  begin
    ObjectStr:='';
  end
  else if CmBx_Object.Text='供应商' then
  begin
    ObjectStr:=' And OnCheckBill.VendorCode='''+ExtEdt_Object.Text+'''';
  end
  else if CmBx_Object.Text='部门' then
  begin
    ObjectStr:='None';
  end
  else if CmBx_Object.Text='客户' then
  begin
    ObjectStr:='None';
  end;
  if ExtEdt_Object.Text='' then
  begin
    ObjectStr:='';
  end;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #BillListQry'
    +' Select convert(varchAr(10),OnCheckBill.OnCheckDate,102) As DateSting'
    +',''1102'' As BillTypeCode'
    +',OnCheckBill.OnCheckBillNo As InvBillNo'
    +',Case When OnCheckBill.PoNo is Null Then '''' Else OnCheckBill.PoNo end+'' ''+'
    +' Case When OnCheckBillLine.PoLineNo is Null Then '''' Else Str(OnCheckBillLine.PoLineNo,2) end'
    +' As MoPoNo'
    +',BillType.BillTypeName '
//    +'' ''+OnCheckBill.VendorCode+'' ''+Vendor.VendorName
    +' As InvBillRemArk'
    +',OnCheckBillLine.OnCheckQty As InvBillQty'
    +',Null As OInvBillQty'
    +',OnCheckBill.InvBillCreateTime As InvBillCreateTime'

    +',OnCheckBillLine.ItemCode As ItemCode'
    +',Item.ItemName As ItemName'
    +',OnCheckBillLine.ItemCode+Item.ItemName As ItemCodeName'
    +',OnCheckBill.WHCode As WHCode'
    +',null'
    +',null'
    +',null'
    +',null'
    +' From OnCheckBillLine join OnCheckBill on OnCheckBillLine.OnCheckBillId=OnCheckBill.OnCheckBillId'
    +' join Item on OnCheckBillLine.ItemCode=Item.ItemCode'
    +' join BillType on OnCheckBill.BillTypeCode=BillType.BillTypeCode'
    +' join Vendor on OnCheckBill.VendorCode=Vendor.VendorCode'

    +' Where OnCheckBillLine.OnCheckStatus=0'
    +WHCodeStr
    +StrTemp3
    +ObjectStr
    +' And OnCheckBill.OnCheckDate>='''+MEdt_StArtDate.Text+''''
    +' And OnCheckBill.OnCheckDate<='''+MEdt_EndDate.Text+'''';
  if CmBx_OutIn.Text<>'出库' then
  begin
    if ObjectStr<>'None' then
      AdoQry_Tmp.ExecSQL;
  end;
  ModalResult:=mrOk;
end;

procedure TFrm_Inv_BillListQry_C.FindItemCode(Sender: TObject);
begin
  inherited;
  if(ActiveControl.Name='btn_Cancel')then
    exit;
  with AdoQry_Tmp do
  begin
    Close;
    SQL.clear;
    SQL.Add('Select Uom.UomName From Item Join Uom On Item.UomCode=Uom.UomCode '+
            'Where Item.ItemCode='''+TExtEdit(Sender).Text+'''');
    Open;
    Lbl_UomName.Caption:=fieldbyname('UomName').AsString;
  end;
end;

procedure TFrm_Inv_BillListQry_C.FormActivate(Sender: TObject);
begin
  inherited;
  if CmBx_Warehouse.Items.Count=0 then
  begin
    MEdt_StArtDate.Text:=FormatDateTime('yyyy.mm.dd',IncMonth(Now,-1));
    MEdt_EndDate.Text:=FormatDateTime('yyyy.mm.dd',Now);
    InitUsableWHCmBx(AdoQry_Tmp,UserCode,CmBx_Warehouse,False);
    CmBx_Warehouse.Items.Insert(0,'全部仓库');
    CmBx_Warehouse.ItemIndex:=0;
    CmBx_Object.ItemIndex:=0;
    CmBx_OutIn.ItemIndex:=0;
  end;
end;

procedure TFrm_Inv_BillListQry_C.CmBx_ObjectChange(Sender: TObject);
begin
  inherited;
  ExtEdt_Object.Text:='';
  if CmBx_Object.Text='全部' then
  begin
    ExtEdt_Object.OnKeyDown:=nil;
    ExtEdt_Object.OnExit:=nil;
  end
  else if CmBx_Object.Text='供应商' then
  begin
    ExtEdt_Object.OnKeyDown:=VendorHint;
    ExtEdt_Object.OnExit:=GetVendorName;
  end
  else if CmBx_Object.Text='部门' then
  begin
    ExtEdt_Object.OnKeyDown:=DeptHint;
    ExtEdt_Object.OnExit:=GetDeptName;
  end
  else if CmBx_Object.Text='客户' then
  begin
    ExtEdt_Object.OnKeyDown:=CustomerHint;
    ExtEdt_Object.OnExit:=GetCustomerName;
  end;
end;

end.