inv_spadjust_c.pas

一个MRPII系统源代码版本

unit Inv_SPAdjust_C;

Interface

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

Type
  TFrm_Inv_SPAdjust_C = Class(TFrm_Base_Condition)
    medt_Month: TMaskEdit;
    Label1: TLabel;
    procedure MonthCheck(Sender: TObject);
    procedure btn_okClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
  private
    Period_Month,Next_Month,Last_Month:string;
    procedure initMonth;
    { Private declarations }
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;UserCode:String);
  end;

var
  Frm_Inv_SPAdjust_C: TFrm_Inv_SPAdjust_C;

implementation

{$R *.DFM}

uses Sys_Global;

procedure TFrm_Inv_SPAdjust_C.InitForm(AdOConnection:TAdOConnection;UserCode:String);
begin
  AdoQry_tmp.Connection:=AdOConnection;
  initMonth;
  Medt_Month.text:=Next_Month;
end;


procedure TFrm_Inv_SPAdjust_C.initMonth;
begin
  DateSeparator:='.';
  Shortdateformat:='yyyy.mm.dd';
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.text:=
      ' select left(convert(varchAr,Invstatus),7) as InvStatus '+
      '  from Invstatus '+
      ' where InvStatusName=''clsperiod'' ';
    open;
    period_Month:=fieldbyname('Invstatus').asstring;
    Next_Month:=copy(datetostr(incMonth(strtodatetime(Trim(fieldbyname('Invstatus').asstring)+'.01'),2)-1),1,7);
  end;
end;

procedure TFrm_Inv_SPAdjust_C.MonthCheck(Sender: TObject);
begin
  inherited;
  //录入的月份的上一个月
  Last_Month:=copy(datetostr(incMonth(strtodatetime(Trim(Medt_Month.text)+'.01'),-1)),1,7);
  if Medt_Month.text>Next_Month then
  begin
    DispInfo(Last_Month+'月还未结帐，不允许进行计划价差异调整！',1);
    twincontrol(sender).setfocus;
    abort;
  end;
  if Medt_Month.text<=Period_Month then
  begin
    DispInfo(Period_Month+'月已经结帐，只能查询计划价差异调整数据！',1);
  end;
  selectnext(activecontrol,True,True);
  btn_ok.ControlStyle:=[cSclickEvents];
  btn_ok.ControlState:=[cSclicked];
  btn_ok.SetFocus ;
end;

procedure TFrm_Inv_SPAdjust_C.btn_okClick(Sender: TObject);
var
  sqltext:string;
begin
  inherited;
  sqltext:=
    ' create table #tmp1(whCode varchAr(10), '+
                         'ItemCode varchAr(16),'+
                         'lastMonthPrice float default(0),'+
                         'lastMonthqty float default(0),'+
                         'CurrentMonthPrice float default(0),'+
                         'Difference float default(0),'+
                         'BillDifference float default(0)) '+
        ' create table #tmp2(whCode varchAr(10), '+
                         'ItemCode varchAr(16),'+
                         'lastMonthPrice float default(0),'+
                         'lastMonthqty float default(0),'+
                         'CurrentMonthPrice float default(0),'+
                         'Difference float default(0),'+
                         'BillDifference float default(0)) '+
    //取InvInBill
    ' insert #tmp1(whCode,ItemCode,BillDifference) '+
    '  select i.whCode,il.ItemCode,il.InvBillNoTaxAmount '+
    '   from InvInBill i '+
    '    join InvInBillline il '+
        '  on i.InvBillid=il.InvBillid '+
    '  where i.SysBill=1 '+
    '    and i.InvBillMonth='''+medt_Month.text+''''+
    '    and i.InvBillremArk like ''%计划价%'' '+
    //取InvMonthSum中上一个月数量
    ' insert #tmp1(whCode,ItemCode,lastMonthqty) '+
    '   select ims.whCode,ims.ItemCode,ims.Invblncqty '+
    '     from InvMonthSum ims,Warehouse wh'+
    '    Where ims.InvMonth='''+last_Month+''''+
    '      and ims.whCode=wh.whCode '+
    '      and wh.PriceType=1 '+
    //取STANDArEPRICE上一个月价格
    ' insert #tmp1(whCode,ItemCode,lastMonthPrice) '+
    '   select ims.whCode,st.ItemCode,st.spPrice '+
    '     from InvMonthSum ims '+
    '     join  StandardPrice st '+
           '   on ims.ItemCode=st.ItemCode '+
           '  and st.spstArtMonth='''+last_Month+''''+
    '     join Warehouse wh '+
    '          on ims.whCode=wh.whCode '+
             ' and wh.PriceType=1 '+
    '  where ims.InvMonth='''+last_Month+''''+
      '  and ims.Invblncqty<>0 '+
    //取StandardPRICE当前月价格
    ' insert #tmp1(whCode,ItemCode,CurrentMonthPrice) '+
    '   select ims.whCode,st.ItemCode,st.spPrice '+
    '     from InvMonthSum ims '+
    '     join  StandardPrice st '+
           '   on ims.ItemCode=st.ItemCode '+
           '  and st.spstArtMonth='''+medt_Month.text+''''+
    '     join Warehouse wh '+
    '          on ims.whCode=wh.whCode '+
             ' and wh.PriceType=1 '+
     '  where ims.InvMonth='''+last_Month+''''+
       '  and ims.Invblncqty<>0 '+
    //对#TMP1每一列汇总后得到正确数据放到汇进表#tmp2
    ' insert #tmp2(whCode,ItemCode,lastMonthPrice,lastMonthqty,CurrentMonthPrice,BillDifference) '+
    '  select whCode,ItemCode,sum(lastMonthPrice),sum(lastMonthqty),sum(CurrentMonthPrice),sum(BillDifference) '+
    '   from #tmp1 '+
    '   group by whCode,ItemCode '+
    //计算差异及单据差异
    ' update #tmp2 set '+
    ' Difference=(CurrentMonthPrice-lastMonthPrice)*lastMonthqty '+
    ' select t2.whCode, '+
           ' t2.whCode+'' ''+w.whName as whName, '+
           ' t2.ItemCode,'+
           ' t2.ItemCode+'' ''+i.ItemName as ItemName,'+
           ' u.UomName, '+
           ' t2.lastMonthPrice,'+
           ' t2.lastMonthqty,'+
           ' t2.CurrentMonthPrice,'+
           ' t2.Difference,'+
           ' t2.BillDifference '+
     ' from #tmp2 t2,Item i,Warehouse w,Uom u '+
     '  where ((t2.Difference<>0) or (t2.Difference<>t2.BillDifference)) '+
     '    and t2.whCode=w.whCode '+
     '    and t2.ItemCode=i.ItemCode '+
     '    and i.UomCode=u.UomCode '+
     ' drop table #tmp1 '+
     ' drop table #tmp2 ';
  condition:=sqltext;
  FatherForm.SetFormParam(medt_Month.text,last_Month,Period_Month,'','','');
  modalResult:=mrok;
end;

procedure TFrm_Inv_SPAdjust_C.FormActivate(Sender: TObject);
begin
  inherited;
  medt_Month.SetFocus ;
end;

end.