inv_spadjust.pas

来自「一个MRPII系统源代码版本」· PAS 代码 · 共 341 行

unit Inv_SPAdjust;

Interface

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

Type
  TFrm_Inv_SPAdjust = Class(TFrm_Base_Qry)
    lbl_InvBillno: TLabel;
    Label1: TLabel;
    lbl_Month: TLabel;
    Label2: TLabel;
    DBText1: TDBText;
    Label3: TLabel;
    procedure Act_SaveExecute(Sender: TObject);
  private
    old_InvBillid,period_Month,last_Month:string;
    { Private declarations }
  public
    procedure SetFormParam(FrmParam1,FrmParam2,FrmParam3,FrmParam4,FrmParam5,FrmParam6:String);Override;
    procedure getData;Override;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Inv_SPAdjust: TFrm_Inv_SPAdjust;

implementation

uses Inv_SPAdjust_C,Sys_Global;
{$R *.DFM}

procedure TFrm_Inv_SPAdjust.getData;
begin
  try
    with AdoQry_tmp do
    begin
      Close;
      sql.clear;
      sql.Add('drop table #tmp1  drop table #tmp2');
      execsql;
    end;
  except
  end;

  try
    AdoQry_Main.Close;
    AdoQry_Main.sql.clear;
    AdoQry_Main.sql.text:=condition;
    AdoQry_Main.Open;
    AdoQry_Main.Sort:='whCode,ItemCode';
  except
    begin
      DispInfo('数据连接失败！请重试！',3);
    end;
  end;
  with DBGridEh do
  begin
    columns.clear;
    columns.Add;
    columns[0].FieldName:='whName';
    columns[0].Title.Caption:='仓库标识';
    columns[0].Width:=130;
    columns[0].title.alignment:=taCenter;
    columns.Add;
    columns[1].FieldName:='ItemName';
    columns[1].Title.Caption:='物料标识';
    columns[1].Width:=200;
    columns[1].title.alignment:=taCenter;
    columns.Add;
    columns[2].FieldName:='UomName';
    columns[2].Title.Caption:='计量单位';
    columns[2].Width:=60;
    columns[2].title.alignment:=taCenter;
    columns[2].alignment:=taCenter;
    columns.Add;
    columns[3].FieldName:='lastMonthPrice';
    columns[3].Title.Caption:=last_Month+'月|计划价';
    columns[3].title.alignment:=taCenter;
    columns[3].Width:=100;
    columns.Add;
    columns[4].FieldName:='lastMonthqty';
    columns[4].Title.Caption:=last_Month+'月|结存数量';
    columns[4].title.alignment:=taCenter;
    columns[4].Width:=100;
    columns.Add;
    columns[5].FieldName:='CurrentMonthPrice';
    columns[5].Title.Caption:=lbl_Month.Caption+'月计划价';
    columns[5].title.alignment:=taCenter;
    columns[5].Width:=100;
    columns.Add;
    columns[6].FieldName:='Difference';
    columns[6].Title.Caption:='差异';
    columns[6].title.alignment:=taCenter;
    columns[6].Width:=80;
    columns.Add;
    columns[7].FieldName:='BillDifference';
    columns[7].Title.Caption:='单据中的差异';
    columns[7].title.alignment:=taCenter;
    columns[7].Width:=90;
  end;

  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.text:=
      'select InvBillid,InvBillno from InvInBill where SysBill=1 and InvBillMonth='''+lbl_Month.Caption+''' and  InvBillremArk like ''%计划价%'' ';
    open;
    First;
    old_InvBillid:='';
    lbl_InvBillno.Caption:='';
    while not eof do
    begin
      if lbl_InvBillno.Caption='' then
      begin
         lbl_InvBillno.Caption:=lbl_InvBillno.Caption+fieldbyname('InvBillno').asstring;
         old_InvBillid:=old_InvBillid+fieldbyname('InvBillid').asstring
      end
      else
      begin
         lbl_InvBillno.Caption:=lbl_InvBillno.Caption+','+fieldbyname('InvBillno').asstring;
         old_InvBillid:=old_InvBillid+','+fieldbyname('InvBillid').asstring;
      end;
      next;
    end;
  end;

end;

procedure TFrm_Inv_SPAdjust.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
begin
  inherited;
  PriceFields:='lastMonthPrice,CurrentMonthPrice,';
  AmountFields:='Difference,BillDifference,';

  Frm_Sys_Condition:=TFrm_Inv_SPAdjust_C.Create(Self);
  TFrm_Inv_SPAdjust_C(Frm_Sys_Condition).InitForm(AdoQry_tmp.Connection,UserCode);
  Act_Filter.Execute;


end;

procedure TFrm_Inv_SPAdjust.SetFormParam(FrmParam1,FrmParam2,FrmParam3,FrmParam4,FrmParam5,FrmParam6:String);
begin
  inherited;
  //把参数从CONDITION窗体中传过来
  lbl_Month.Caption:=FrmParam1;
  last_Month:=FrmParam2;
  period_Month:=frmParam3;
  TlBtn_Sum.Action:=act_Save;
  if period_Month>=lbl_Month.Caption then
     act_Save.enabled:=False
  else
     act_Save.enabled:=True;
end;

procedure TFrm_Inv_SPAdjust.Act_SaveExecute(Sender: TObject);
var
  tmp_Billno:string;
  saveplace:tBookmArk;
  AdoQry_tmp1:tAdoQuery;
  procedure savedata;
  var
    tmp_InvInBilllinetext,tmp_WhpositonCode,Billno,Billid:string;
    M,I:integer;
  begin
    saveplace:=AdoQry_Main.GetBookmArk;
    AdoQry_tmp1:=tAdoQuery.Create(nil);
    AdoQry_tmp1.Connection:=AdoQry_tmp.Connection;
    with AdoQry_tmp1 do
    begin
      Close;
      sql.clear;
      sql.text:=
        '   select distinct ims.whCode '+
      '     from InvMonthSum ims,Warehouse wh'+
      '    Where ims.InvMonth='''+last_Month+''''+
      '      and ims.whCode=wh.whCode '+
      '      and wh.PriceType=1 '+
      '  Order by ims.whCode ';
      open;
      First;
      tmp_Billno:='';
      for I:=0 to recordCount-1 do
      begin
        AdoQry_Main.First;
        if AdoQry_Main.Locate('whCode',fieldbyname('whCode').asstring,[]) then
        begin
          Billno:=getno(AdoQry_tmp.Connection,fieldbyname('whCode').asstring+'R'+copy(lbl_Month.Caption,3,2)+copy(lbl_Month.Caption,6,2),'InvBill');
          if tmp_Billno='' then
             tmp_Billno:=tmp_Billno+Billno
          else
             tmp_Billno:=tmp_Billno+','+Billno;
           //取货位
          AdoQry_tmp.Close;
          AdoQry_tmp.sql.clear;
          AdoQry_tmp.sql.text:=
           ' select WhPositionCode '+
           '  from WhPosition '+
           '  where whCode='''+fieldbyname('whCode').asstring+''''+
           '    and WhPositionType=0 ';
          AdoQry_tmp.open;
          tmp_WhpositonCode:=AdoQry_tmp.fieldbyname('WhPositionCode').asstring;
          //增加单据头
          AdoQry_tmp.Close;
          AdoQry_tmp.sql.clear;
          AdoQry_tmp.sql.text:=
            ' insert InvInBill '+
               '(InvBillno,'+
               'whCode,'+
               'InvBilldate,'+
               'InvBillMonth,'+
               'BillTypeCode,'+
               'Stk_EmployeeCode,'+
               'EmployeeCode,'+
               'InvBillStkchck,'+
               'WhPositionCode,'+
               'AmountAdjust,'+
               'SysBill,'+
               'InvBillremArk)'+
             ' Values '+
               ' ('''+Billno+''','+
               ''''+fieldbyname('whCode').asstring+''','+
               ''''+lbl_Month.Caption+'.01'+''','+
               ''''+lbl_Month.Caption+''','+
               '''0199'','+
               ''''+userCode+''','+
               ''''+userCode+''','+
               '1,'+
               ''''+tmp_WhpositonCode+''','+
               '1,'+
               '1,'+
               '''调整计划价差异'''+')';
          AdoQry_tmp.execsql;
          //取BillID
          AdoQry_tmp.Close;
          AdoQry_tmp.sql.clear;
          AdoQry_tmp.sql.Add(' select @@IDENTITY as Billid ');
          AdoQry_tmp.open;
          Billid:=AdoQry_tmp.fieldbyname('Billid').asstring;

          //循环生成表体的语句
          AdoQry_Main.First;
          M:=1;
          tmp_InvInBilllinetext:='';
          while not AdoQry_Main.eof do
          begin
            if AdoQry_Main.fieldbyname('whCode').asstring=fieldbyname('whCode').asstring then
            begin
              tmp_InvInBilllinetext:=tmp_InvInBilllinetext+
               ' insert into InvInBillline '+
                '  (InvBillid,'+
                '   InvBilllineno,'+
                '   ItemCode,'+
                'InvBillnotaxAmount)'+
                ' Values '+
                ' ('+Billid+','+
                   inttostr(M)+','+
                ''''+AdoQry_Main.fieldbyname('ItemCode').asstring+''','+
                AdoQry_Main.fieldbyname('Difference').asstring+')';
                M:=M+1;
            end;
            AdoQry_Main.Next;
          end;
          //增加InvInBillLINE
          AdoQry_tmp.Close;
          AdoQry_tmp.sql.clear;
          AdoQry_tmp.sql.text:=tmp_InvInBilllinetext;
          AdoQry_tmp.execsql;

          AdoQry_tmp.Close;
          AdoQry_tmp.sql.clear;
          AdoQry_tmp.sql.text:=
            'update InvInBill '+
            ' set InvBillcreatetime='''+lbl_Month.Caption+'.01 00:00:00'+''''+
            ' where InvBillid='+Billid;
          AdoQry_tmp.execsql;
        end;
        next;
      end;
    end;
  end;
begin
  inherited;
  if lbl_InvBillno.Caption<>'' then
  begin
    if DispInfo('差异单据已经产生，将删除原来产生的单据'+chAr(13)+'再次产生新的单据是否继续？',2)='y' then
    begin
      AdoQry_Main.DisableControls;
      AdoQry_tmp.Connection.beginTrans;
      try
        with AdoQry_tmp do
        begin
          Close;
          sql.clear;
          sql.text:=
            ' delete from InvInBillline where InvBillid in ('+old_InvBillid+')'+
            ' delete from InvInBill where InvBillid in ('+old_InvBillid+')';
          execsql;
        end;
        savedata;
        AdoQry_tmp.Connection.CommitTrans;
        lbl_InvBillno.Caption:=tmp_Billno;
        DispInfo('单据保存成功',3);
      except
        begin
          AdoQry_tmp.Connection.rollBacktrans;
          DispInfo(' 存盘不成功，请稍后再试！ ',1);
        end;
      end;
      AdoQry_Main.GotoBookmArk(saveplace);
      AdoQry_Main.FreeBookmArk(saveplace);
      AdoQry_Main.EnableControls;
      AdoQry_tmp1.Free;
    end;
  end
  else
  begin
    AdoQry_Main.DisableControls;
    AdoQry_tmp.Connection.beginTrans;
    try
      savedata;
      AdoQry_tmp.Connection.CommitTrans;
      lbl_InvBillno.Caption:=tmp_Billno;
      DispInfo('单据保存成功',3);
    except
      begin
        AdoQry_tmp.Connection.rollBacktrans;
        DispInfo(' 存盘不成功，请稍后再试！ ',1);
      end;
    end;
  end;
end;

end.