inv_opjournalqry_c.pas

一个MRPII系统源代码版本

unit Inv_OpJournalQry_C;

Interface

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

Type
  TFrm_Inv_OpJournalQry_C = Class(TFrm_Base_Condition)
    Label2: TLabel;
    Label3: TLabel;
    Label8: TLabel;
    MEdt_StArtDate: TMaskEdit;
    MEdt_EndDate: TMaskEdit;
    ExtEdt_ItemCode: TExtEdit;
    Lbl_ItemName: TLabel;
    Label1: TLabel;
    Lbl_UomName: TLabel;
    Label4: TLabel;
    Lbl_VendorName: TLabel;
    ExtEdt_VendorCode: TExtEdit;
    procedure btn_okClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ItemCodeCheck(Sender: TObject);
    procedure ExtEdt_ItemCodeKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure SetFormParam(FrmParam1,FrmParam2,FrmParam3,FrmParam4,
      FrmParam5,FrmParam6:String);Override;
  end;

var
  Frm_Inv_OpJournalQry_C: TFrm_Inv_OpJournalQry_C;

implementation

uses Sys_Global, Sys_Hint;

{$R *.DFM}
procedure TFrm_Inv_OpJournalQry_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 #OPJournalQry';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #OPJournalQry'
    +' Select convert(varchAr(10),InvInBill.InvBillDate,102) As DateSting'
    +',InvInBill.InvBillMonth As MonthString'
    +',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'

    +',BillType.BillTypeName+'' ''+InvInBill.VendorCode+'' ''+Vendor.VendorName As InvBillRemArk'

    +',Null As InvBillQty'
    +',OPBill.OPBillQty As OInvBillQty'
    +',Null As InvBlncQty'

    +',Null As InvBillPrice'
    +',OPBill.OPBillNoTaxPrice As OInvBillPrice'
    +',Null As InvBlncPrice'

    +',Null As InvBillAmount'
    +',OPBill.OPBillNoTaxAmount As OInvBillAmount'
    +',Null As InvBlncAmount'

    +',InvInBill.InvBillCreateTime As InvBillCreateTime'
    +',InvInBill.WHCode As WHCode'
    +' From OPBill'
    +' Join InvInBill On OPBill.InvBillId=InvInBill.InvBillId'
    +' Join InvInBillLine On OPBill.InvBillId=InvInBillLine.InvBillId'
    +' And OPBill.InvBillLineNo=InvInBillLine.InvBillLineNo'
    +' Join BillType On InvInBill.BillTypeCode=BillType.BillTypeCode'
    +' Join Vendor On InvInBill.VendorCode=Vendor.VendorCode'
    +' Where OPBill.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And InvInBill.InvBillMonth>='''+StArtMonth+''''
    +' And InvInBill.InvBillMonth<='''+endMonth+''''
    +' And InvInBill.VendorCode='''+ExtEdt_VendorCode.Text+''''
    +' And InvInBill.InitData=0';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #OPJournalQry'
    +' Select OpMonthSum.InvMonth As DateSting'
    +',OpMonthSum.InvMonth+''_'' As MonthString'
    +',''9999'' As BillTypeCode'
    +',Null As InvBillNo'
    +',Null As MoPoNo'
    +',''本月合计'' As InvBillRemArk'
    +',OpMonthSum.InvInQty As InvBillQty'
    +',OpMonthSum.InvOutQty As OInvBillQty'
    +',OpMonthSum.InvBlncQty As InvBlncQty'

    +',OpMonthSum.InvInPrice As InvBillPrice'
    +',OpMonthSum.InvOutPrice As OInvBillPrice'
    +',OpMonthSum.InvBlncPrice As InvBlncPrice'

    +',OpMonthSum.InvInAmount As InvBillAmount'
    +',OpMonthSum.InvOutAmount As OInvBillAmount'
    +',OpMonthSum.InvBlncAmount As InvBlncAmount'
    +',Null As InvBillCreateTime'
    +',Null As WHCode'
    +' From OpMonthSum'
    +' Where OpMonthSum.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And OpMonthSum.InvMonth>='''+StArtMonth+''''
    +' And OpMonthSum.InvMonth<='''+StrMonth+''''
    +' And OpMonthSum.VendorCode='''+ExtEdt_VendorCode.Text+'''';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #OPJournalQry'
    +' Select OpMonthSum.InvMonth As DateSting'
    +',OpMonthSum.InvMonth+''_'' As MonthString'
    +',''9999'' As BillTypeCode'
    +',Null As InvBillNo'
    +',Null As MoPoNo'
    +',''本月合计(未结帐)'' As InvBillRemArk'
    +',OpMonthSum.InvInQty As InvBillQty'
    +',OpMonthSum.InvOutQty As OInvBillQty'
    +',OpMonthSum.InvBlncQty As InvBlncQty'

    +',OpMonthSum.InvInPrice As InvBillPrice'
    +',OpMonthSum.InvOutPrice As OInvBillPrice'
    +',OpMonthSum.InvBlncPrice As InvBlncPrice'

    +',OpMonthSum.InvInAmount As InvBillAmount'
    +',OpMonthSum.InvOutAmount As OInvBillAmount'
    +',OpMonthSum.InvBlncAmount As InvBlncAmount'
    +',Null As InvBillCreateTime'
    +',Null As WHCode'
    +' From OpMonthSum'
    +' Where OpMonthSum.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And OpMonthSum.InvMonth>'''+StrMonth+''''
    +' And OpMonthSum.InvMonth<='''+endMonth+''''
    +' And OpMonthSum.VendorCode='''+ExtEdt_VendorCode.Text+'''';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #OPJournalQry'
    +' Select Convert(varchAr(10),InvOutBill.InvBillDate,102) As DateSting'
    +',InvOutBill.InvBillMonth As MonthString'
    +',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'
    +',BillType.BillTypeName+'' ''+InvOutBill.VendorCode+'' ''+Vendor.VendorName As InvBillRemArk'

    +',InvOutBillLine.InvBillQty As InvBillQty'
    +',Null As OInvBillQty'
    +',Null As InvBlncQty'

    +',InvOutBillLine.InvBillNoTaxPrice As InvBillPrice'
    +',Null As OInvBillPrice'
    +',Null As InvBlncPrice'

    +',InvOutBillLine.InvBillNoTaxAmount As InvBillAmount'
    +',Null As OInvBillAmount'
    +',Null As InvBlncAmount'

    +',InvOutBill.InvBillCreateTime As InvBillCreateTime'
    +',InvOutBill.WHCode As WHCode'
    +' From InvOutBillLine'
    +' Join InvOutBill On InvOutBillLine.InvBillId=InvOutBill.InvBillId'
    +' Join BillType On InvOutBill.BillTypeCode=BillType.BillTypeCode'
    +' Join Vendor On InvOutBill.VendorCode=Vendor.VendorCode'
    +' Where InvOutBillLine.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And ((InvOutBill.BillTypeCode=''0202'')'
    +' Or((InvOutBill.BillTypeCode=''0299'')'
    +' And(InvOutBill.OPBill=1)))'
    +' And InvOutBill.InvBillMonth>='''+StArtMonth+''''
    +' And InvOutBill.InvBillMonth<='''+endMonth+''''
    +' And InvOutBill.VendorCode='''+ExtEdt_VendorCode.Text+'''';
  AdoQry_Tmp.ExecSQL;
  //加入委外结存调整单据
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #OPJournalQry'
    +' Select Convert(varchAr(10),opAdjustBill.opBillDate,102) As DateSting'
    +',opAdjustBill.opBillMonth As MonthString'
    +','''' As BillTypeCode'
    +',opAdjustBill.opBillNo+'' ''+Str(opAdjustBillLine.opBillLineNo,3) As InvBillNo'
    +','''' As MoPoNo'