inv_opjournalqry_c.pas

一个MRPII系统源代码版本

    +',''委外结存调整 ''+opAdjustBill.VendorCode+'' ''+Vendor.VendorName As InvBillRemArk'

    +',opAdjustBillLine.opBillQty As InvBillQty'
    +',Null As OInvBillQty'
    +',Null As InvBlncQty'

    +',null as InvBillPrice'
    +',Null As OInvBillPrice'
    +',Null As InvBlncPrice'

    +',opAdjustBillLine.opBillNoTaxAmount As InvBillAmount'
    +',Null As OInvBillAmount'
    +',Null As InvBlncAmount'

    +',opAdjustBill.opBillCreateTime As InvBillCreateTime'
    +','''' As WHCode'
    +' From opAdjustBillLine'
    +' Join opAdjustBill On opAdjustBillLine.opBillId=opAdjustBill.opBillId'
    +' Join Vendor On opAdjustBill.VendorCode=Vendor.VendorCode'
    +' Where opAdjustBillLine.ItemCode='''+ExtEdt_ItemCode.Text+''''
    +' And opAdjustBill.opBillMonth>='''+StArtMonth+''''
    +' And opAdjustBill.opBillMonth<='''+endMonth+''''
    +' And opAdjustBill.VendorCode='''+ExtEdt_VendorCode.Text+'''';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Select DateSting'
    +',MonthString'
    +',BillTypeCode'
    +',InvBillNo'
    +',MoPoNo'
    +',InvBillRemArk'
    +',InvBillQty'
    +',OInvBillQty'
    +',InvBlncQty'
    +',InvBillPrice'
    +',OInvBillPrice'
    +',InvBlncPrice'
    +',InvBillAmount'
    +',OInvBillAmount'
    +',InvBlncAmount'
    +',InvBillCreateTime'
    +' From #OPJournalQry'
    +' Order By MonthString,InvBillCreateTime';
  AdoQry_Tmp.Open;




  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('MonthString').AsString:=NowMonth+'_';
      AdoQry_Tmp.fieldbyname('BillTypeCode').AsString:='9999';
      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('InvBillPrice').AsString:='0';
      AdoQry_Tmp.fieldbyname('OInvBillPrice').AsString:='0';
      AdoQry_Tmp.fieldbyname('InvBlncPrice').AsString:='0';
      AdoQry_Tmp.fieldbyname('InvBillAmount').AsString:='0';
      AdoQry_Tmp.fieldbyname('OInvBillAmount').AsString:='0';
      AdoQry_Tmp.fieldbyname('InvBlncAmount').AsString:='0';
      AdoQry_Tmp.fieldbyname('InvBillCreateTime').AsDateTime:=Now;
      AdoQry_Tmp.Post;
    end;
  end;
  AdoQry_Tmp.Sort := 'MonthString,InvBillCreateTime';
  AmountIn:=0;
  AmountOut:=0;
  AmountTotal:=0;
  AmountInm:=0;
  AmountOutm:=0;
  AmountTotalm:=0;
  InvBlncPrice:=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:=0;//AmountIn;
        AdoQry_Tmp.fieldbyname('OInvBillQty').AsFloat:=0;//AmountOut;
        AdoQry_Tmp.fieldbyname('InvBlncAmount').AsFloat:=AmountTotalm;
        AdoQry_Tmp.fieldbyname('InvBillAmount').AsFloat:=0;//AmountInm;
        AdoQry_Tmp.fieldbyname('OInvBillAmount').AsFloat:=0;//AmountOutm;
        if AmountTotal<>0 then
           AdoQry_Tmp.fieldbyname('InvBlncPrice').AsFloat:= AmountTotalm/AmountTotal
        else
          AdoQry_Tmp.fieldbyname('InvBlncPrice').AsFloat:=0;
        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;
          AmountTotalm:=AmountTotalm+AdoQry_Tmp.fieldbyname('InvBillAmount').AsFloat
            -AdoQry_Tmp.fieldbyname('oInvBillAmount').AsFloat;
          AdoQry_Tmp.fieldbyname('InvBlncAmount').AsFloat:=AmountTotalm;
          if (AmountTotal=0 ) or (AmountTotalm=0) then
            AdoQry_Tmp.fieldbyname('InvBlncPrice').AsFloat:=0
          else
          AdoQry_Tmp.fieldbyname('InvBlncPrice').AsFloat:=AmountTotalm/AmountTotal;
          AmountInm:=AmountInm+AdoQry_Tmp.fieldbyname('InvBillAmount').AsFloat;
          AmountOutm:=AmountOutm+AdoQry_Tmp.fieldbyname('OInvBillAmount').AsFloat;
          InvBlncPrice:=AdoQry_Tmp.fieldbyname('InvBlncPrice').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;
      AmountTotalm:=AdoQry_Tmp.fieldbyname('InvBlncAmount').AsFloat;
      //InvBlncPrice:=AdoQry_Tmp.fieldbyname('InvBlncPrice').AsFloat ;
      AmountInm:=0;//AdoQry_Tmp.fieldbyname('InvBillQty').AsFloat;
      AmountOutm:=0;//AdoQry_Tmp.fieldbyname('OInvBillQty').AsFloat;

    end;
    AdoQry_Tmp.Next;
  end;

  ConditionHint:='供应商：'+ExtEdt_VendorCode.Text+' '+Lbl_VendorName.Caption+'/'
    +'物料：'+ExtEdt_ItemCode.Text+' '+Lbl_ItemName.Caption+' '
    +Lbl_UomName.Caption;

  Condition:='MonthString>='''+StArtMonth+'_'''
    +' And MonthString<='''+endMonth+'_''';

  ModalResult:=mrOk;
end;

procedure TFrm_Inv_OpJournalQry_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_OpJournalQry_C.ItemCodeCheck(Sender: TObject);
begin
  inherited;
  if(ActiveControl.Name='btn_Cancel')then
    Abort;
  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_OpJournalQry_C.ExtEdt_ItemCodeKeyDown(Sender: TObject;
  var Key: Word; Shift: TShiftState);
begin
  inherited;
  if Key=120 then
  begin
    Frm_Sys_Hint:=TFrm_Sys_Hint.Create(nil);
    Frm_Sys_Hint.InitForm(DBConnect,
      'Select Distinct(OPItemList.ItemCode) As ''物料代码'''
      +',Item.ItemName As ''物料描述'''
      +' From OPItemList'
      +' Join Item On OPItemList.ItemCode=Item.ItemCode'
      +' Order By OPItemList.ItemCode'
      );
    if Frm_Sys_Hint.ShowModal=mrOk then
      TEdit(Sender).Text:=Frm_Sys_Hint.GetValueByFieldIndex(0);
    Frm_Sys_Hint.Release;
  end;
end;

procedure TFrm_Inv_OpJournalQry_C.SetFormParam(FrmParam1, FrmParam2,
  FrmParam3, FrmParam4, FrmParam5, FrmParam6: String);
begin
  inherited;
  MEdt_StArtDate.Text:=FrmParam1;
  MEdt_EndDate.Text:=FrmParam2;
  ExtEdt_VendorCode.Text:=FrmParam3;
  ExtEdt_ItemCode.Text:=FrmParam4;
  btn_okClick(btn_ok);
end;

end.