inv_priceaudit_poblnc_b.pas

一个MRPII系统源代码版本

unit Inv_PriceAudit_PoBlnc_B;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Entry_Body, StdCtrls, ExtPrintReport, Db, ActnList, AdODB, Grids,
  DBGridEh, ExtCtrls, ComCtrls, ToolWin;

Type
  TFrm_Inv_PriceAudit_PoBlnc_B = Class(TFrm_Base_Entry_Body)
    Label1: TLabel;
    lbl_Pono: TLabel;
    Label2: TLabel;
    lbl_Vendor: TLabel;
    Label3: TLabel;
    lbl_Currency: TLabel;
    Label4: TLabel;
    lbl_taxrate: TLabel;
    DBGridEh1: TDBGridEh;
    AdoQry_Detail: TAdoQuery;
    DataSource1: TDataSource;
    procedure Act_ModifyExecute(Sender: TObject);
    procedure DataSourceDataChange(Sender: TObject; Field: TField);
    procedure FormActivate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure AdoQry_BodyAfterPost(DataSet: TDataSet);
  private
    noChange:integer;
    { Private declarations }
  public
    procedure InitControls;Override;
    procedure SaveData; Override;//把表头控件写入缓存,不要Post
    { Public declarations }
  end;

var
  Frm_Inv_PriceAudit_PoBlnc_B: TFrm_Inv_PriceAudit_PoBlnc_B;

implementation
uses Sys_Global,Inv_PriceAudit_PoBlnc_D;
{$R *.DFM}

procedure TFrm_Inv_PriceAudit_PoBlnc_B.InitControls;
var
  body_Sqltext,Detail_Sqltext:string;
begin
  inherited;
  lbl_Pono.Caption:=AdoQry_Head.fieldbyname('pono').asstring;
  lbl_Vendor.Caption:=AdoQry_Head.fieldbyname('VendorCode').asstring+' '+
                        AdoQry_Head.fieldbyname('VendorName').asstring;


  AdoQry_Detail.Connection :=AdoQry_tmp.Connection;
  //把当前订单的所有产品列出来，即订单行
  body_Sqltext:='select poline.pono,'+
                       'poline.polineno,'+
                       'poline.ItemCode,'+
                       'poline.ItemCode+'+''' '''+'+Item.ItemName as ItemName,'+
                       'Item.UomCode+'+''' '''+'+Uom.UomName as UomName,'+
                       'poline.poreferencedPrice,'+
                       'poline.poqty,'+
                       'poline.potaxPrice,'+
                       'poline.potaxAmount,'+
                       'poline.realpotaxPrice,'+
                       'poline.realpotaxAmount,'+
                       'poline.realponotaxPrice,'+
                       'poline.realponotaxAmount'+
                ' from poline '+
                '  left join Item on poline.ItemCode=Item.ItemCode '+
                '  left join Uom on Item.UomCode=Uom.UomCode '+
                ' where poline.pono='''+AdoQry_Head.fieldbyname('pono').asstring+''''+
                '  Order by poline.pono,polineno ';
   //对每一产品所需要的材料（物料）列出来，由上面DBGRID滚动而动态刷新下面DBGRID显示
   //不是委外加工材料费及委外加工差异单据且为实在单据
  Detail_Sqltext:='select i.InvBillStkchck,'+
                         'i.InvBillfinchck,'+
                         'i.PriceAudittimes,'+
                         'i.InvBillno,'+
                         'i.InvBilldate,'+
                         'il.pono,'+
                         'il.polineno,'+
                         'il.InvBillqty,'+
                         'il.InvBillPrice,'+
                         'il.InvBillAmount '+
                  'from InvInBill i '+
                  ' left join InvInBillline il '+
                  '   on i.InvBillid=il.InvBillid '+
                  ' where i.pono='''+AdoQry_Head.fieldbyname('pono').asstring+''''+
                  '   and i.BillTypeCode not in (''1201'',''1202'') '+
                  '   and i.RealBillFlag=1 ';
  try
    with AdoQry_tmp do
    begin
      //根据当前定单号取出对应的币种及供应商对应的税率
      Close;
      sql.clear;
      sql.Add('select c.currencyCode,c.currencyName '+
              ' from po p '+
              '  left join currency c on p.currencyCode=c.currencyCode '+
              ' where pono='''+AdoQry_Head.fieldbyname('pono').asstring+'''');
      open;
      lbl_Currency.Caption:=fieldbyname('currencyCode').asstring+' '+
                            fieldbyname('currencyName').asstring;
      Close;
      sql.clear;
      sql.Add('select POTaxRate_Percent '+
              ' from po  '+
              '  where pono='''+AdoQry_Head.fieldbyname('pono').asstring+'''');
      open;
      lbl_taxrate.Caption:=fieldbyname('POTaxRate_Percent').asstring;
    end;
    with AdoQry_Body do
    begin
      Close;
      sql.clear;
      sql.Add(body_Sqltext);
      open;
      First;
      while not eof do
      begin
        //在数据库表中还没有核价的其实际含税价，实际含税金额默认为-1，显示时，把实际含税单价，实际金额显示为含税价，含税金额
        if fieldbyname('realpotaxPrice').asfloat=-1 then
        begin
          edit;
          fieldbyname('realpotaxPrice').asfloat:=fieldbyname('potaxPrice').asfloat;
          fieldbyname('realpotaxAmount').asfloat:=fieldbyname('potaxAmount').asfloat;
          if lbl_taxrate.Caption<>'0' then
          begin
            fieldbyname('realponotaxPrice').asfloat:=
              fieldbyname('potaxPrice').asfloat/(1+strtofloat(lbl_taxrate.Caption)/100);
            fieldbyname('realponotaxAmount').asfloat:=
              fieldbyname('potaxAmount').asfloat/(1+strtofloat(lbl_taxrate.Caption)/100);
          end
          else
          begin
            fieldbyname('realponotaxPrice').asfloat:=fieldbyname('potaxPrice').asfloat;
            fieldbyname('realponotaxAmount').asfloat:=fieldbyname('potaxAmount').asfloat;
          end;
        end;
        next;
      end;
    end;
    with AdoQry_Detail do
    begin
      Close;
      sql.clear;
      sql.Add(Detail_Sqltext);
      open;
    end;
  except
    begin
      DispInfo('数据连接失败，请重试！',1);
      abort;
    end;
  end;
  Frm_Entry_Detail:=TFrm_Inv_PriceAudit_PoBlnc_D.Create(Self);//设置弹出Detail
end;

procedure TFrm_Inv_PriceAudit_PoBlnc_B.SaveData;
var
  AdoQry_InvInBill:tAdoQuery;
  I,remArk,PriceAudittimes:integer;
  oldAmount,Amount,oldrealpotaxAmount:real;
  chck_Month,Current_Date,tmp_Date,tmp_Head_Date,tmp_memo,Billno,Billid,blue_Billid:string;
begin
  inherited;
  //如果单据己经开发票的，不能整张单核价，只能用单独核价程序Inv_PRINCEAudit_H，
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Text:=
      ' select Count(*) as record '+
      ' from InvInBill  '+
      ' where pono='''+lbl_Pono.Caption+''''+
      '   and BillTypeCode not in (''1201'',''1202'') '+
      '   and InvBillfinchck=1 ';
    open;
    if fieldbyname('record').asinteger=1 then
    begin
      DispInfo('已经有单据与发票进行过匹配，不能进行订单核价！'+#13+'请对单据单独进行核价',1);
      abort;
    end;
  end;
  AdoQry_InvInBill:=tAdoQuery.Create(self);
  AdoQry_InvInBill.Connection :=AdoQry_tmp.Connection;
  //控制小数位
  AdoQry_InvInBill.EnableBCD:=False;
  AdoQry_Body.Connection.beginTrans;
  try
  AdoQry_Body.First;
  for I:=0 to  AdoQry_Body.RecordCount-1 do
  begin
    //更改实际含税单价,实际含税金额,实际未税价,实际未税金额
    with AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add('update poline '+
               '  set realpotaxPrice='+AdoQry_Body.fieldbyname('realpotaxPrice').asstring+','+
                   '   realpotaxAmount='+AdoQry_Body.fieldbyname('realpotaxAmount').asstring+','+
                       'realponotaxPrice='+AdoQry_Body.fieldbyname('realponotaxPrice').asstring+','+
                       'realponotaxAmount='+AdoQry_Body.fieldbyname('realponotaxAmount').asstring+
               ' where pono='''+lbl_Pono.Caption+''''+
               '  and polineno='+AdoQry_Body.fieldbyname('polineno').asstring);
      execsql;
    end;
    AdoQry_Body.next;
  end;
    //取旧数据用于更改应收应付
    //定单行所有单据
  with AdoQry_InvInBill do
  begin
    Close;
    sql.clear;
    sql.Add('select InvBillid,'+
                   'whCode,'+
                   'InvBilldate,'+
                   'PriceAudittimes,'+
                   'InvBillStkchck'+
            ' from InvInBill '+
            ' where pono='''+lbl_Pono.Caption+''''+
            '   and BillTypeCode not in (''1201'',''1202'') '+
            '   and realBillflag=1 ');
    open;
    First;
    while not eof do
    begin
      remArk:=1;
      PriceAudittimes:=fieldbyname('PriceAudittimes').asinteger+1;
      //订单行与入库单行对应的物料的实际含税金额比较,如果改变,则产生红,蓝单
      with AdoQry_tmp do
      begin
        Close;
        sql.clear;
        sql.Add('select Count(*) as record '+
                ' from InvInBillline bl,poline pl,InvInBill b '+
                ' where bl.InvBillid='+AdoQry_InvInBill.fieldbyname('InvBillid').asstring+
                '   and bl.pono=pl.pono '+
                '   and bl.polineno=pl.polineno '+
                '   and bl.InvBillAmount<>pl.realpotaxAmount ');
        open;
        //单据有改变
        if fieldbyname('record').asinteger>0 then
          remArk:=0;
      end;
      if (remArk=1) and (fieldbyname('InvBillStkchck').asinteger=1) then
      begin
      end
      else
      begin
         //更改旧单据的核价状态,把如单是暂估价单变成不是暂估价单据
        with AdoQry_tmp do
        begin
            Close;
            sql.clear;
            sql.Add('update InvInBill '+
                  '   set InvBillValuation=0,'+
                  '       RealBillFlag='+inttostr(remArk)+','+
                  '       InvBillStkchck=1,'+
                  '       PriceAuditTimes='+inttostr(PriceAuditTimes)+','+
                  '       Stk_EmployeeCode='''+userCode+''' '+
                  ' where InvBillid='+AdoQry_InvInBill.fieldbyname('InvBillid').asstring+
                  ' update InvInBillline '+
                  '    set Valuation=0  '+
                  ' where InvBillid='+AdoQry_InvInBill.fieldbyname('InvBillid').asstring );
            execsql;
        end;

        //取出所有的旧金额
        AdoQry_tmp.Close;
        AdoQry_tmp.sql.clear;
        AdoQry_tmp.sql.Add('select sum(InvBillAmount) as oldAmount '+
                  ' from InvInBillline '+
                  '  where InvBillid='+fieldbyname('InvBillid').asstring);
        AdoQry_tmp.open;
        oldAmount:=AdoQry_tmp.fieldbyname('oldAmount').asfloat;

        //如果是己经核过价的，则要减旧的金额，再把新的金额加上来
        //由于此处处理比较特殊
        if fieldbyname('PriceAudittimes').asinteger>0 then
           Amount:=-oldAmount
        else
        begin
           //未核，单据发生改变
           if remArk=0 then Amount:=0
           else Amount:=oldAmount;
        end;
        //更改当前供应商应收应付
        with AdoQry_tmp do
        begin
          Close;
          sql.clear;
          sql.Add(' if not exists (select VendorCode from CurrentAp '+
                  ' where VendorCode='''+getCode(lbl_Vendor.Caption)+''' and '+
                  '       currencyCode='''+getCode(lbl_Currency.Caption)+''') '+
                  '  insert CurrentAp(VendorCode,currencyCode,ApNoInvoice) '+
                  '  Values('''+getCode(lbl_Vendor.Caption)+''','+
                            ''''+getCode(lbl_Currency.Caption)+''','+
                            floattostr(round(Amount*100)/100)+') '+
                  ' else '+
                  ' update CurrentAp '+
                  '    set ApNoInvoice=ApNoInvoice+('+floattostr(round(Amount*100)/100)+')'+
                  ' where VendorCode='''+getCode(lbl_Vendor.Caption)+''' and '+
                  '       currencyCode='''+getCode(lbl_Currency.Caption)+'''');
          execsql;
        end;
      end;

      if remArk=0 then
      begin
        //tmp_Date己结帐的下一个月份
        //   如果单据月份<=结帐月份+1个月，则=结帐月份+1个月
        //否则单据月份=单据月份
        //获得服务器日期，即当前日期
        //如果单据月份<当前月份，则单据日期=单据月份的最后一天
        //否则单据日期=当前日期
        with AdoQry_tmp do
        begin
          Close;
          sql.clear;
          sql.text:=
           ' select left(convert(varchAr,Invstatus),7) as InvStatus '+
           '  from Invstatus '+
           '  where InvStatusName=''clsperiod'' ';
          open;
          tmp_Date:=datetostr(incMonth(strtodatetime(Trim(fieldbyname('Invstatus').asstring)+copy(LoginDate,8,3)),1));
        end;
        //单据头日期
        tmp_Head_Date:=Trim(AdoQry_InvInBill.fieldbyname('InvBilldate').asstring);
        if copy(tmp_Head_Date,1,7)<=copy(tmp_Date,1,7) then
           chck_Month:=copy(tmp_Date,1,7)
        else
           chck_Month:=copy(tmp_Head_Date,1,7);
        with AdoQry_tmp do