inv_journalqry_c.pas

一个MRPII系统源代码版本

unit Inv_JournalQry_C;

Interface

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

Type
  TFrm_Inv_JournalQry_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 FormActivate(Sender: TObject);
    procedure ItemCodeCheck(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure SetFormParam(FrmParam1,FrmParam2,FrmParam3,FrmParam4,
      FrmParam5,FrmParam6:String);Override;
  end;

var
  Frm_Inv_JournalQry_C: TFrm_Inv_JournalQry_C;

implementation

uses Sys_Global;

{$R *.DFM}
procedure TFrm_Inv_JournalQry_C.btn_okClick(Sender: TObject);
var
  StrMonth,StArtMonth,endMonth,NowMonth:String;
  AmountTotal,AmountIn,AmountOut:Double;
  AmountTotalm,AmountInm,AmountOutm ,InvBlncPrice: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);
  StArtMonth:=FormatDateTime('yyyy.mm',StrToDateTime(MEdt_StArtDate.Text+'.01')-1);
  endMonth:=MEdt_EndDate.Text;
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Delete #JournalQry';
  AdoQry_Tmp.ExecSQL;
  AdoQry_Tmp.Close;


  AdoQry_Tmp.SQL.Text:='Insert #JournalQry'
    +' 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+'' '''
    +' +case '
    +'   when EXISTS ( select InvInBill.InvBillno from InvInBill where InvInBill.InvBillid'
    +'   in (select SourceInvInBill.InvBillid from ( select * from InvInBill '
    +' where InvInBill.InvBillid in (select ParentBillid from InvInBill where BillTypeCode=''1202'''
    +'     and InvInBill.ParentBillid<>0 )) as SourceInvInBill ) )'
    +'   then isnull(SourceInvInBill.InvBillno ,'''')'
    +'  else ''''  end+'' '''

   { +' +case when EXISTS ( select InvInBill.InvBillno from InvInBill where InvInBill.InvBillid in (select ParentBillid from InvInBill where BillTypeCode=''1202'') ) '
    +'   then InvInBill.InvBillno '
    +'  else '' ''  end+'' '''}
    +'+IsNull(InvInBill.VendorCode+'' ''+Vendor.VendorName,'''')'
    +'+isnull('',送货单号:''+InvInBill.VendorBillno,'''')'
    +'+IsNull(InvInBill.CustomerCode+'' ''+Customer.CustomerName,'''')'
    +'+IsNull(InvInBill.DeptCode+'' ''+Dept.DeptName,'''')'
    +' As InvBillRemArk'

    +',InvInBillLine.InvBillQty As InvBillQty'
    +',Null As OInvBillQty'
    +',Null As InvBlncQty'
    +',InvInBillLine.InvBillNoTaxPrice As InvBillPrice'
    +',Null As OInvBillPrice'
    +',Null As InvBlncPrice'
    +',InvInBillLine.InvBillNoTaxAmount As InvBillAmount'
    +',Null As OInvBillAmount'
    +',Null As InvBlncAmount'
    +',InvInBill.InvBillCreateTime As InvBillCreateTime'
    +',InvInBillLine.Valuation As Valuation'
    +',InvInBill.WHCode As WHCode'
    +' From InvInBillLine'
    +' Join InvInBill On InvInBillLine.InvBillId=InvInBill.InvBillId'
    +' left join  ( select * from InvInBill '
    +' where InvInBill.InvBillid in (select ParentBillid from InvInBill where BillTypeCode=''1202'' '
    +' and InvInBill.ParentBillid<>0 )) as SourceInvInBill '
    +' on InvInBill.ParentBillid=SourceInvInBill.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.InvBillMonth>='''+StArtMonth+''''
    +' And InvInBill.InvBillMonth<='''+endMonth+''''
    +' And InvInBill.InitData=0'
    +' And InvInBill.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
    +' And InvInBillLine.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And InvInBill.BillTypeCode not in (''0205'',''0206'') ' // 02.11.19 by zks;
    +' And ((InvInBill.BillTypeCode In (''1201'',''1202'',''0101'',''0102'',''0199''))'
    +' Or ((InvInBill.BillTypeCode In (''0104'',''0105'',''0103'')) And (InvInBill.InvBillWHChck=1)))';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #JournalQry'
    +' 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'
    +',InvMonthSum.InvInPrice As InvBillPrice'
    +',InvMonthSum.InvOutPrice As OInvBillPrice'
    +',InvMonthSum.InvBlncPrice As InvBlncPrice'
    +',InvMonthSum.InvInAmount As InvBillAmount'
    +',InvMonthSum.InvOutAmount As OInvBillAmount'
    +',InvMonthSum.InvBlncAmount As InvBlncAmount'
    +',NULL As InvBillCreateTime'
    +',NULL As Valuation'
    +',null As WHCode'
    +' From InvMonthSum'
    +' Where InvMonthSum.InvMonth>='''+StArtMonth+''''
    +' And InvMonthSum.InvMonth<='''+StrMonth+''''
    +' And InvMonthSum.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
    +' And InvMonthSum.ItemCode='''+ExtEdt_ItemCode.Text+'''';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #JournalQry'
    +' 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'
    +',InvMonthSum.InvInPrice As InvBillPrice'
    +',InvMonthSum.InvOutPrice As OInvBillPrice'
    +',InvMonthSum.InvBlncPrice As InvBlncPrice'
    +',InvMonthSum.InvInAmount As InvBillAmount'
    +',InvMonthSum.InvOutAmount As OInvBillAmount'
    +',InvMonthSum.InvBlncAmount As InvBlncAmount'
    +',NULL As InvBillCreateTime'
    +',NULL As Valuation'
    +',null As WHCode'
    +' From InvMonthSum'
    +' Where InvMonthSum.InvMonth>'''+StrMonth+''''
    +' And InvMonthSum.InvMonth<='''+endMonth+''''
    +' And InvMonthSum.WHCode='''+GetCode(CmBx_Warehouse.Text)+''''
    +' And InvMonthSum.ItemCode='''+ExtEdt_ItemCode.Text+'''';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #JournalQry'
    +' 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 and InvOutBill.AmountAdjust=0) Then ''无订单委外发料'''
    +' when (InvOutBill.OPBill=1 and InvOutBill.AmountAdjust=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'
    +',Null As InvBillNoTaxPrice'
    +',InvOutBillLine.InvBillNoTaxPrice As OInvBillNoTaxPrice'
    +',Null As InvBlncPrice'
    +',Null As InvBillNoTaxAmount'
    +',InvOutBillLine.InvBillNoTaxAmount As OInvBillNoTaxAmount'
    +',Null As InvBlncAmount'
    +',InvOutBill.InvBillCreateTime As InvBillCreateTime'
    +',NULL As Valuation'
    +',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'