inv_journalqry_inv_c.pas

一个MRPII系统源代码版本

unit Inv_JournalQry_Inv_C;

Interface

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

Type
  TFrm_Inv_JournalQry_Inv_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;
    procedure btn_okClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure ItemCodeCheck(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure defaultwhCode(AdOConnection:TAdOConnection;userCode:string;ItemCode:string);
  end;

var
  Frm_Inv_JournalQry_Inv_C: TFrm_Inv_JournalQry_Inv_C;

implementation

uses Sys_Global;

{$R *.DFM}
procedure TFrm_Inv_JournalQry_Inv_C.btn_okClick(Sender: TObject);
var
  StrTemp1,StrMonth,StArtMonth,endMonth,NowMonth:String;
  AmountTotal,AmountIn,AmountOut:Double;
begin
  inherited;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select InvStatus From InvStatus'
    +' Where InvStatusName=''clsperiod''';
  AdoQry_Tmp.Open;
  StrMonth:=FormatFloat('0000.00',AdoQry_Tmp.fieldbyname('InvStatus').AsFloat);
  if(StrToDateTime(MEdt_StArtDate.Text+'.01')-1>=StrToDateTime(StrMonth+'.01')) then
    StArtMonth:=FormatDateTime('yyyy.mm',StrToDateTime(StrMonth+'.01'))
  else
    StArtMonth:=FormatDateTime('yyyy.mm',StrToDateTime(MEdt_StArtDate.Text+'.01')-1);
  endMonth:=MEdt_EndDate.Text;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Delete #JournalQryInv';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #JournalQryInv'
    +' Select convert(varchAr(10),InvInBill.InvBillDate,102) As DateSting'
    +',InvInBill.InvBillMonth As MonthSting'
    +',InvInBill.BillTypeCode As BillTypeCode'
    +',InvInBill.InvBillNo+'' ''+Str(InvInBillLine.InvBillLineNo,3) As InvBillNo'

    +',IsNull(InvInBill.MoNo,'''')+IsNull(InvInBill.PoNo,'''')+'' '''
    +'+IsNull(Str(InvInBillLine.MoLineNo,3),'''')'
    +'+IsNull(Str(InvInBillLine.PoLineNo,3),'''')'
    +' As MoPoNo'

    +',Case When InvInBill.OPBill=1 Then ''无订单委外入库 '''
    +' Else BillType.BillTypeName end+'' '''
    +'+IsNull(InvInBill.VendorCode+'' ''+Vendor.VendorName+'',送货单号:''+InvInBill.VendorBillno,'''')'
    +'+IsNull(InvInBill.CustomerCode+'' ''+Customer.CustomerName,'''')'
    +'+IsNull(InvInBill.DeptCode+'' ''+Dept.DeptName,'''')'
    +' As InvBillRemArk'

    +',InvInBillLine.InvBillQty As InvBillQty'
    +',Null As OInvBillQty'
    +',Null As InvBlncQty'
    +',InvInBill.InvBillCreateTime As InvBillCreateTime'
    +',InvInBill.WHCode As WHCode'
    +' From InvInBillLine'
    +' Join InvInBill on InvInBillLine.InvBillId=InvInBill.InvBillId'
    +' 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.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
    +' And InvInBill.ParentBillNo='''''
    +' And InvInBillLine.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And ((InvInBill.BillTypeCode In (''0101'',''0102'',''0199''))'
    +' Or ((InvInBill.BillTypeCode In (''0104'',''0105'',''0103''))'
    +' And (InvInBill.InvBillWHChck=1)))'
    +' And InvInBill.InvBillMonth>='''+StArtMonth+''''
    +' And InvInBill.InvBillMonth<='''+MEdt_EndDate.Text+''''
    +' And InvInBill.InitData=0';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #JournalQryInv'
    +' Select InvMonthSum.InvMonth As DateSting'
    +',InvMonthSum.InvMonth+''_'' As MonthSting'
    +',''0000'' As BillTypeCode'
    +',Null As InvBillNo'
    +',Null As MoPoNo'
    +',''本月合计'' As InvBillRemArk'
    +',InvMonthSum.InvInQty As InvBillQty'
    +',InvMonthSum.InvOutQty As OInvBillQty'
    +',InvMonthSum.InvBlncQty As InvBlncQty'
    +',Null As InvBillCreateTime'
    +',Null As WHCode'
    +' From InvMonthSum'
    +' Where InvMonthSum.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
    +' And InvMonthSum.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And InvMonthSum.InvMonth>='''+StArtMonth+''''
    +' And InvMonthSum.InvMonth<='''+StrMonth+'''';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #JournalQryInv'
    +' Select InvMonthSum.InvMonth As DateSting'
    +',InvMonthSum.InvMonth+''_'' As MonthSting'
    +',''0000'' As BillTypeCode'
    +',Null As InvBillNo'
    +',Null As MoPoNo'
    +',''本月合计(未结帐)'' As InvBillRemArk'
    +',InvMonthSum.InvInQty As InvBillQty'
    +',InvMonthSum.InvOutQty As OInvBillQty'
    +',InvMonthSum.InvBlncQty As InvBlncQty'
    +',Null As InvBillCreateTime'
    +',Null As WHCode'
    +' From InvMonthSum'
    +' Where InvMonthSum.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
    +' And InvMonthSum.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And InvMonthSum.InvMonth>'''+StrMonth+''''
    +' And InvMonthSum.InvMonth<='''+endMonth+'''';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #JournalQryInv'
    +' Select convert(varchAr(10),InvOutBill.InvBillDate,102) As DateSting'
    +',InvOutBill.InvBillMonth As MonthSting'
    +',InvOutBill.BillTypeCode As BillTypeCode'
    +',InvOutBill.InvBillNo+'' ''+Str(InvOutBillLine.InvBillLineNo,3) As InvBillNo'

    +',IsNull(InvOutBill.MoNo,'''')+IsNull(InvOutBill.PoNo,'''')'
    +'+IsNull(Str(InvOutBillLine.MoLineNo,3),'''')'
    +'+IsNull(Str(InvOutBillLine.PoLineNo,3),'''')'
    +' As MoPoNo'

    +',Case When InvOutBill.OPBill=1 Then ''无订单委外发料'''
    +' Else BillType.BillTypeName end+'' '''
    +'+IsNull(InvOutBill.VendorCode+'' ''+Vendor.VendorName,'''')'
    +'+IsNull(InvOutBill.CustomerCode+'' ''+Customer.CustomerName,'''')'
    +'+IsNull(InvOutBill.DeptCode+'' ''+Dept.DeptName,'''')'
    +' As InvBillRemArk'

    +',Null As InvBillQty'
    +',InvOutBillLine.InvBillQty As OInvBillQty'
    +',Null As InvBlncQty'
    +',InvOutBill.InvBillCreateTime As InvBillCreateTime'
    +',InvOutBill.WHCode As WHCode'
    +' From InvOutBillLine'
    +' Join InvOutBill On InvOutBillLine.InvBillId=InvOutBill.InvBillId'
    +' 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.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
    +' And InvOutBillLine.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And ((InvOutBill.BillTypeCode In (''0202''))'
    +' Or ((InvOutBill.BillTypeCode In (''0201'',''0203'',''0204'',''0299''))'
    +' And (InvOutBill.InvBillWHChck=1)))'
    +' And InvOutBill.InvBillMonth>='''+StArtMonth+''''
    +' And InvOutBill.InvBillMonth<='''+MEdt_EndDate.Text+'''';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #JournalQryInv'
    +' Select convert(varchAr(10),OnCheckBill.OnCheckDate,102) As DateSting'
    +',OnCheckBill.OnCheckMonth As MonthSting'
    +',''1102'' As BillTypeCode'
    +',OnCheckBill.OnCheckBillNo+'' ''+Str(OnCheckBillLine.OnCheckBillLineNo,3) As InvBillNo'
    +',IsNull(OnCheckBill.PoNo,'''')+'' '''
    +'+IsNull(Str(OnCheckBillLine.PoLineNo,3),'''')'
    +' As MoPoNo'
    +',BillType.BillTypeName+'' ''+OnCheckBill.VendorCode+'' ''+Vendor.VendorName+'' 送货单号:''+oncheckBill.VendorBillno As InvBillRemArk'
    +',OnCheckBillLine.OnCheckQty As InvBillQty'
    +',Null As OInvBillQty'
    +',Null As InvBlncQty'
    +',OnCheckBill.InvBillCreateTime As InvBillCreateTime'
    +',OnCheckBill.WHCode As WHCode'
    +' From OnCheckBillLine'
    +' Join OnCheckBill On OnCheckBillLine.OnCheckBillId=OnCheckBill.OnCheckBillId'
    +' Join BillType On OnCheckBill.BillTypeCode=BillType.BillTypeCode'
    +' Join Vendor On OnCheckBill.VendorCode=Vendor.VendorCode'
    +' Where OnCheckBill.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
    +' And OnCheckBillLine.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And OnCheckBillLine.OnCheckStatus=0'
    +' And OnCheckBill.OnCheckMonth>='''+StArtMonth+''''
    +' And OnCheckBill.OnCheckMonth<='''+MEdt_EndDate.Text+'''';
  AdoQry_Tmp.ExecSQL;

  StrTemp1:=FormatDateTime('yyyy.mm',StrToDateTime(MEdt_StArtDate.Text+'.01')-1);
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select JournalQryId,DateSting'
    +',MonthSting'
    +',BillTypeCode'
    +',InvBillNo'
    +',MoPoNo'
    +',InvBillRemArk'
    +',InvBillQty'
    +',OInvBillQty'
    +',InvBlncQty'
    +',InvBillCreateTime'
    +' From #JournalQryInv'
    +' Where MonthSting>='''+StrTemp1+'_'''
    +' And MonthSting<='''+MEdt_EndDate.Text+'_''';
  AdoQry_Tmp.Open;
  AdoQry_Tmp.Sort:='MonthSting,InvBillCreateTime';

  NowMonth:=FormatDateTime('yyyy.mm',IncMonth(StrToDateTime(StrMonth+'.01'),1));
  if NowMonth<>endMonth then
  begin
    if not AdoQry_Tmp.Locate('DateSting',NowMonth,[]) then
    begin
      AdoQry_Tmp.Append;
      AdoQry_Tmp.fieldbyname('DateSting').AsString:=NowMonth;
      AdoQry_Tmp.fieldbyname('MonthSting').AsString:=NowMonth+'_';
      AdoQry_Tmp.fieldbyname('BillTypeCode').AsString:='';
      AdoQry_Tmp.fieldbyname('InvBillNo').AsString:='';
      AdoQry_Tmp.fieldbyname('MoPoNo').AsString:='';
      AdoQry_Tmp.fieldbyname('InvBillRemArk').AsString:='本月合计(未结帐)';
      AdoQry_Tmp.fieldbyname('InvBillQty').AsString:='0';
      AdoQry_Tmp.fieldbyname('OInvBillQty').AsString:='0';
      AdoQry_Tmp.fieldbyname('InvBlncQty').AsString:='0';
      AdoQry_Tmp.fieldbyname('InvBillCreateTime').AsDateTime:=Now;
      AdoQry_Tmp.Post;
    end;
  end;
  if not AdoQry_Tmp.Locate('MonthSting',StrMonth+'_',[]) then
  begin
    AdoQry_Tmp.Append;
    AdoQry_Tmp.fieldbyname('DateSting').AsString:=StrMonth;
    AdoQry_Tmp.fieldbyname('MonthSting').AsString:=StrMonth+'_';
    AdoQry_Tmp.fieldbyname('BillTypeCode').AsString:='';
    AdoQry_Tmp.fieldbyname('InvBillNo').AsString:='';
    AdoQry_Tmp.fieldbyname('MoPoNo').AsString:='';
    AdoQry_Tmp.fieldbyname('InvBillRemArk').AsString:='本月合计';
    AdoQry_Tmp.fieldbyname('InvBillQty').AsString:='0';
    AdoQry_Tmp.fieldbyname('OInvBillQty').AsString:='0';
    AdoQry_Tmp.fieldbyname('InvBlncQty').AsString:='0';
    AdoQry_Tmp.fieldbyname('InvBillCreateTime').AsDateTime:=Now;
    AdoQry_Tmp.Post;
  end;
  AmountIn:=0;
  AmountOut:=0;
  AmountTotal:=0;
  AdoQry_Tmp.First;
  while not AdoQry_Tmp.Eof do
  begin
    if(AdoQry_Tmp.fieldbyname('InvBillRemArk').AsString<>'本月合计')then
    begin
      if(AdoQry_Tmp.fieldbyname('InvBillRemArk').AsString='本月合计(未结帐)')then
      begin
        AdoQry_Tmp.Edit;
        AdoQry_Tmp.fieldbyname('InvBlncQty').AsFloat:=AmountTotal;
        AdoQry_Tmp.fieldbyname('InvBillQty').AsFloat:=AmountIn;
        AdoQry_Tmp.fieldbyname('OInvBillQty').AsFloat:=AmountOut;
        AdoQry_Tmp.Post;
      end
      else
      begin
        if (AdoQry_Tmp.fieldbyname('BillTypeCode').AsString<>'0000') and
           (AdoQry_Tmp.fieldbyname('BillTypeCode').AsString<>'1102') then
        begin
          AdoQry_Tmp.Edit;
          AmountTotal:=AmountTotal+AdoQry_Tmp.fieldbyname('InvBillQty').AsFloat
            -AdoQry_Tmp.fieldbyname('OInvBillQty').AsFloat;
          AdoQry_Tmp.fieldbyname('InvBlncQty').AsFloat:=AmountTotal;
          AmountIn:=AmountIn+AdoQry_Tmp.fieldbyname('InvBillQty').AsFloat;
          AmountOut:=AmountOut+AdoQry_Tmp.fieldbyname('OInvBillQty').AsFloat;
          AdoQry_Tmp.Post;
        end;
      end;
    end
    else
    begin
      AmountTotal:=AdoQry_Tmp.fieldbyname('InvBlncQty').AsFloat;
      AmountIn:=0;//AdoQry_Tmp.fieldbyname('InvBillQty').AsFloat;
      AmountOut:=0;//AdoQry_Tmp.fieldbyname('OInvBillQty').AsFloat;
    end;
    AdoQry_Tmp.Next;
  end;

  ConditionHint:='仓库： '+CmBx_Warehouse.Text+'/物料： '
    +ExtEdt_ItemCode.Text+' '+Lbl_ItemName.Caption+' '+Lbl_UomName.Caption;
    
  Condition:='MonthSting>='''+StrTemp1+'_'''
    +' And MonthSting<='''+MEdt_EndDate.Text+'_''';
    
  ModalResult:=mrOk;
end;

procedure TFrm_Inv_JournalQry_Inv_C.FormCreate(Sender: TObject);
begin
  inherited;
  MEdt_StArtDate.Text:=FormatDateTime('yyyy.mm',IncMonth(Now,-1));
  MEdt_EndDate.Text:=FormatDateTime('yyyy.mm',Now);
end;

procedure TFrm_Inv_JournalQry_Inv_C.FormActivate(Sender: TObject);
begin
  inherited;
  if CmBx_Warehouse.Items.Count=0 then
    InitUsableWHCmBx(AdoQry_Tmp,UserCode,CmBx_Warehouse,False);
end;

procedure TFrm_Inv_JournalQry_Inv_C.ItemCodeCheck(Sender: TObject);
begin
  inherited;
  if(ActiveControl.Name='btn_Cancel')then
    Exit;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select Uom.UomName From Item'
    +' Join Uom On Item.UomCode=Uom.UomCode'
    +' Where Item.ItemCode='''+TExtEdit(Sender).Text+'''';
  AdoQry_Tmp.Open;
  Lbl_UomName.Caption:=AdoQry_Tmp.fieldbyname('UomName').AsString;
end;

procedure TFrm_Inv_JournalQry_Inv_C.defaultwhCode(AdOConnection:TAdOConnection;userCode:string;ItemCode:string);
var
  whCodeName,ItemName,UomName:string;
  i,j:integer;
begin
  AdoQry_tmp.Connection:=AdOConnection;
  with AdoQry_tmp do
  begin
    Close;
    sql.text:='select Item.ItemName,Warehouse.whCode as whName,Uom.UomName from Warehouse,Item '+
              'left join Uom on Item.UomCode=Uom.UomCode '+
              'where Item.ItemCode='+QuotedStr(ItemCode)+
              ' and Item.whCode=Warehouse.whCode';
    open;
    whCodeName:=fieldbyname('whName').asstring;
    ItemName:=fieldbyname('ItemName').asstring;
    UomName:=fieldbyname('UomName').asstring;
  end;
  i:=0;
  j:=0;
  CmBx_Warehouse.clear;
  with AdoQry_tmp do
  begin
    Close;
    SQL.clear;
    SQL.Text:='select a.WHCode,w.WHName from WHAccessCtrl a,Warehouse w where a.EmployeeCode='''+userCode+''' and a.WHCode=w.WHCode Order by w.WHCode ';
    Open;
    First;
    if eof then
      CmBx_Warehouse.Items.Add('')
    else
    begin
      while not Eof do
      begin
        CmBx_Warehouse.Items.Add(fieldbyname('whCode').AsString+' '+fieldbyname('whName').asstring);
        if fieldbyname('whCode').AsString= whCodeName then i:=j;
        j:=j+1;
        Next;
      end;
    end;
  end;
  cmbx_Warehouse.ItemIndex:=i;
  Extedt_ItemCode.text:=ItemCode;
  lbl_ItemName.Caption:=ItemName;
  Lbl_UomName.Caption:=UomName;
end;

end.