sfc_monthsumqry_c.pas

一个MRPII系统源代码版本

unit Sfc_MonthSumQry_C;

Interface

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

Type
  TFrm_Sfc_MonthSumQry_C = Class(TFrm_Base_Condition)
    Label1: TLabel;
    Label2: TLabel;
    MEdt_Month: TMaskEdit;
    ExtEdt_Dept: TExtEdit;
    Lbl_Dept: TLabel;
    AdoQry_tmp1: TAdoQuery;
    procedure btn_okClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
    ItemType:string;
  public
    { Public declarations }
    procedure SetUserParam(Param1,Param2,Param3,Param4,Param5,//设置入口参数
      Param6:String);Override;
  end;

var
  Frm_Sfc_MonthSumQry_C: TFrm_Sfc_MonthSumQry_C;

implementation

uses Sys_Global;

{$R *.DFM}

procedure TFrm_Sfc_MonthSumQry_C.btn_okClick(Sender: TObject);
var
  PriorMonth,DeptStr:String;
begin
  if ExtEdt_Dept.Text='' then
  begin
    DispInfo('请输入部门代码',1);
    Abort;
  end;

  inherited;
  PriorMonth:=FormatDateTime('yyyy.mm',IncMonth(StrToDateTime(
    MEdt_Month.Text+'.01'),-1));
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Delete #MonthSumQry';
  AdoQry_Tmp.ExecSQL;
  if ExtEdt_Dept.Text<>'' then
    DeptStr:=' And InvOutBill.DeptCode like '''+ExtEdt_Dept.Text+'%''';
  AdoQry_Tmp.Close;
  if ItemType='z01' then
    AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select InvOutBillLine.ItemCode'
    +',IsNull(InvOutBill.DeptCode,'''')'
    +',InvOutBill.whCode'
    +',0'
    +',0'
    +',Sum(InvOutBillLine.InvBillQty)'
    +',Sum(InvOutBillLine.InvBillnotaxAmount)'
    +',0,0,null'
    +' From InvOutBillLine,InvOutBill,Item '
    +' Where InvOutBillLine.InvBillId=InvOutBill.InvBillId and InvOutBill.InvBillMonth='''+MEdt_Month.Text+''''
    +DeptStr
    +' And ((InvOutBill.InvBillWHChck=1'
    +' And InvOutBill.BillTypeCode=''0201'')'// or (InvOutBill.BillTypeCode=''0299'' and InvOutBill.AmountAdjust=1)
    +' or (InvOutBill.BillTypeCode=''0299'' and InvOutBill.BillType2Code=''130''))'   //其他出库到车间  ：注意130是用户定义的
 //   +' and InvOutBill.WipWaster<>1 '  //料费
    +' and Item.ItemCode=InvOutBillline.ItemCode and Item.ClassCode<>''310004'' ' //310004表示印刷钢板，其单独统计。
    +' Group By InvOutBillLine.ItemCode,InvOutBill.DeptCode,InvOutBill.whCode';
  if ItemType='z02' then
    AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select InvOutBillLine.ItemCode'
    +',IsNull(InvOutBill.DeptCode,'''')'
    +',InvOutBill.whCode'
    +',0'
    +',0'
    +',Sum(InvOutBillLine.InvBillQty)'
    +',Sum(InvOutBillLine.InvBillnotaxAmount)'
    +',0,0,null'
    +' From InvOutBillLine,InvOutBill,Item '
    +' Where InvOutBillLine.InvBillId=InvOutBill.InvBillId and InvOutBill.InvBillMonth='''+MEdt_Month.Text+''''
    +DeptStr
    +' And ((InvOutBill.InvBillWHChck=1'
    +' And InvOutBill.BillTypeCode=''0201'')' //or (InvOutBill.BillTypeCode=''0299'' and InvOutBill.AmountAdjust=1)
    +' or (InvOutBill.BillTypeCode=''0299'' and InvOutBill.BillType2Code=''130''))'   //其他出库到车间  ：注意130是用户定义的
 //   +' and InvOutBill.WipWaster<>1 '  //料费
    +' and Item.ItemCode=InvOutBillline.ItemCode and Item.ClassCode=''310004'' ' //310004表示印刷钢板，其单独统计。
    +' Group By InvOutBillLine.ItemCode,InvOutBill.DeptCode,InvOutBill.whCode';
  if ItemType='p01' then
    AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select InvOutBillLine.ItemCode'
    +',IsNull(InvOutBill.DeptCode,'''')'
    +',InvOutBill.whCode'
    +',0'
    +',0'
    +',Sum(InvOutBillLine.InvBillQty)'
    +',Sum(InvOutBillLine.InvBillnotaxAmount)'
    +',0,0,null'
    +' From InvOutBillLine,InvOutBill '
    +' Where InvOutBillLine.InvBillId=InvOutBill.InvBillId and InvOutBill.InvBillMonth='''+MEdt_Month.Text+''''
    +DeptStr
    +' And ((InvOutBill.InvBillWHChck=1'
    +' And InvOutBill.BillTypeCode=''0201'')'// or (InvOutBill.BillTypeCode=''0299'' and InvOutBill.AmountAdjust=1)
    +' or (InvOutBill.BillTypeCode=''0299'' and InvOutBill.BillType2Code=''130''))'   //其他出库到车间  ：注意130是用户定义的
//    +' and InvOutBill.WipWaster<>1 '  //料费
    +' Group By InvOutBillLine.ItemCode,InvOutBill.DeptCode,InvOutBill.whCode';

  AdoQry_Tmp.ExecSQL;

  if ExtEdt_Dept.Text<>'' then
    DeptStr:=' And DeptCode like '''+ExtEdt_Dept.Text+'%''';

  AdoQry_Tmp.Close;
  if ItemType='z01' then
    AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select ItemCode,'
    +' DeptCode'
    +',null'
    +',wipInvQty'
    +',wipAmount'
    +',0'
    +',0'
    +',0'
    +',0'
    +',null'
    +' From WipInv'
    +' Where ItemType=0 and WipInvMonth='''+PriorMonth+''''
    +DeptStr;
  if ItemType='z02' then
    AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select ItemCode,'
    +' DeptCode'
    +',null'
    +',wipInvQty'
    +',wipAmount'
    +',0'
    +',0'
    +',0'
    +',0'
    +',null'
    +' From WipInv'
    +' Where ItemType=1 and WipInvMonth='''+PriorMonth+''''
    +DeptStr;
  if ItemType='p01' then
    AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select ItemCode,'
    +' DeptCode'
    +',null'
    +',wipInvQty'
    +',wipAmount'
    +',0'
    +',0'
    +',0'
    +',0'
    +',null'
    +' From WipInv'
    +' Where ItemType=3 and WipInvMonth='''+PriorMonth+''''
    +DeptStr;
  AdoQry_Tmp.ExecSQL;
//对上期结存有数量但是没有金额的物料，其价格从车间月末价格定义中找(wipPrice)
  if ExtEdt_Dept.Text<>'' then
  begin
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='update #MonthSumQry '
      +' set InvLMAmount=InvLMQty*wipPrice.Itemprice '
      +' from wipPrice where #MonthSumQry.ItemCode=wipPrice.ItemCode and '
      +' wipPrice.DeptCode='+QuotedStr(ExtEdt_Dept.Text)
      +' and wipPrice.wiPMonth='+QuotedStr(PriorMonth)
      +' and #MonthSumQry.Invlmqty<>0 and #MonthSumQry.InvlmAmount=0 ';
    AdoQry_Tmp.ExecSQL;
  end
  else
  begin
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='update #MonthSumQry '
      +' set InvLMAmount=InvLMQty*wipPrice.Itemprice '
      +' from wipPrice where #MonthSumQry.ItemCode=wipPrice.ItemCode and '
      +' wipPrice.DeptCode=#MonthSumQry.DeptCode '
      +' and wipPrice.wiPMonth='+QuotedStr(PriorMonth)
      +' and #MonthSumQry.Invlmqty<>0 and #MonthSumQry.InvlmAmount=0 ';
    AdoQry_Tmp.ExecSQL;
  end;

  AdoQry_Tmp.Close;
  if ItemType='z01' then
    AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select ItemCode,'
    +' DeptCode'
    +',null'
    +',0'
    +',0'
    +',0'
    +',0'
    +',wipInvQty'
    +',0'
    +',null'
    +' From WipInv'
    +' Where ItemType=0 and WipInvMonth='''+MEdt_Month.Text+''''
    +DeptStr;
  if ItemType='z02' then
    AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select ItemCode,'
    +' DeptCode'
    +',null'
    +',0'
    +',0'
    +',0'
    +',0'
    +',wipInvQty'
    +',0'
    +',null'
    +' From WipInv'
    +' Where ItemType=1 and WipInvMonth='''+MEdt_Month.Text+''''
    +DeptStr;
  if ItemType='p01' then
  begin
    AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry(ItemCode,DeptCode,Invblncqty) '
    +' Select ItemCode,'
    +' DeptCode'
    +',wipInvQty'
    +' From WipInv'
    +' Where ItemType=3 and WipInvMonth='''+MEdt_Month.Text+''''
    +DeptStr;
  end;
  AdoQry_Tmp.ExecSQL;

  if ExtEdt_Dept.Text<>'' then
  begin
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Update #MonthSumQry'
      +' Set #MonthSumQry.Dept='+quotedstr(ExtEdt_Dept.Text)
      +' delete from #MonthSumQry1 '
      +' insert #MonthSumQry1 select Dept as DeptCode,ItemCode,sum(Invlmqty) as Invlmqty,'
      +' sum(InvlmAmount) as InvlmAmount,sum(InvInqty) as InvInqty,'
      +' sum(InvInAmount) as InvInAmount,'
      +' sum(Invblncqty) as Invblncqty,0 as InvblncAmount  '
      +' from #MonthSumQry group by Dept,ItemCode'
      +' update #MonthSumQry1 set InvblncAmount=Invblncqty*((InvInAmount+InvlmAmount)/(Invlmqty+InvInqty)) '
      +' where (Invlmqty+InvInqty)<>0 and Invblncqty<>0 '
      //对于上期没有结存，本期没有领入，但本期有结存的物料，其价格取wipPrice中的价格
      +' update #MonthSumQry1 set InvblncAmount=Invblncqty*wipPrice.Itemprice from wipPrice '
      +' where (InvInqty+Invlmqty)=0 and Invblncqty<>0 and  '
      +' wipPrice.DeptCode='+QuotedStr(ExtEdt_Dept.Text)
      +' and wipPrice.wiPMonth='+QuotedStr(MEdt_Month.Text)
      +' and #MonthSumQry1.ItemCode=wipPrice.ItemCode ';
    AdoQry_Tmp.ExecSQL;

  end
  else
  begin
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=' delete from #MonthSumQry1 '
      +' insert #MonthSumQry1 select DeptCode as DeptCode,ItemCode,sum(Invlmqty) as Invlmqty,'
      +' sum(InvlmAmount) as InvlmAmount,sum(InvInqty) as InvInqty,'
      +' sum(InvInAmount) as InvInAmount,'
      +' sum(Invblncqty) as Invblncqty,null as InvblncAmount  '
      +' from #MonthSumQry group by DeptCode,ItemCode '
      +' update #MonthSumQry1 set InvblncAmount=Invblncqty*((InvInAmount+InvlmAmount)/(Invlmqty+InvInqty)) '
      +' where (Invlmqty+InvInqty)<>0 and Invblncqty<>0 '
      //对于上期没有结存，本期没有领入，但本期有结存的物料，其价格取wipPrice中的价格
      +' update #MonthSumQry1 set InvblncAmount=Invblncqty*wipPrice.Itemprice from wipPrice '
      +' where (InvInqty+Invlmqty)=0 and Invblncqty<>0 and  '
      +' wipPrice.DeptCode=#MonthSumQry1.DeptCode '
      +' and wipPrice.wiPMonth='+QuotedStr(PriorMonth)
      +' and #MonthSumQry1.ItemCode=wipPrice.ItemCode ' ;
    AdoQry_Tmp.ExecSQL;
  end;
  with AdoQry_Tmp do
  begin
    Close;
    sql.text:=' delete from #MonthSumQry1 where Invlmqty=0 and InvlmAmount=0 '
             +' and InvInqty=0 and InvInAmount=0 and Invblncqty=0 and InvblncAmount=0 ';
    ExecSQL;
  end;

    Condition:='Select #MonthSumQry1.ItemCode'
    +',Item.ItemName'
    +',#MonthSumQry1.ItemCode+'' ''+Item.ItemName As ItemCodeName'
    +',#MonthSumQry1.DeptCode+'' ''+Dept.DeptName As DeptCodeName'
    +',Uom.UomName'
    +',case when #MonthSumQry1.InvLMQty is null or #MonthSumQry1.InvLMQty=0 then null '
    +' else #MonthSumQry1.InvLMQty end as InvLMQty'
    +',case when #MonthSumQry1.InvInQty is null or #MonthSumQry1.InvInQty=0 then null '
    +' else #MonthSumQry1.InvInQty end as InvInQty'
    +',case when IsNull(#MonthSumQry1.InvLMQty,0)+IsNull(#MonthSumQry1.InvInQty,0)'
    +'-IsNull(#MonthSumQry1.InvBlncQty,0)=0 then null '
    +' else IsNull(#MonthSumQry1.InvLMQty,0)+IsNull(#MonthSumQry1.InvInQty,0)'
    +'-IsNull(#MonthSumQry1.InvBlncQty,0) end  As InvOutQty'
    +',case when #MonthSumQry1.InvBlncQty is null or #MonthSumQry1.InvBlncQty=0 then null'
    +' else #MonthSumQry1.InvBlncQty end as InvBlncQty'

    +',Case When isnull(#MonthSumQry1.InvLMQty,0)=0 or isnull(#MonthSumQry1.InvLMAmount,0)=0 then null'
    +' else #MonthSumQry1.InvLMAmount/#MonthSumQry1.InvLMQty end As Price1'
    +',Case When isnull(#MonthSumQry1.InvInQty,0)=0 or isnull(#MonthSumQry1.InvInAmount,0)=0 then null'
    +' else #MonthSumQry1.InvInAmount/#MonthSumQry1.InvInqty end As Price2'        //车间的入即仓库的出
    +',Case When (IsNull(#MonthSumQry1.InvLMQty,0)+IsNull(#MonthSumQry1.InvInQty,0)'
    +'-IsNull(#MonthSumQry1.InvBlncQty,0))=0 or (IsNull(#MonthSumQry1.InvLMAmount,0)'
    +'+IsNull(#MonthSumQry1.InvInAmount,0)'
    +'-IsNull(#MonthSumQry1.InvBlncAmount,0))=0 Then null'
    +' Else (IsNull(#MonthSumQry1.InvLMAmount,0)'
    +'+IsNull(#MonthSumQry1.InvInAmount,0)'
    +'-IsNull(#MonthSumQry1.InvBlncAmount,0))'
    +'/(IsNull(#MonthSumQry1.InvLMQty,0)+IsNull(#MonthSumQry1.InvInQty,0)'
    +'-IsNull(#MonthSumQry1.InvBlncQty,0)) end As Price3'

    +',Case When #MonthSumQry1.InvBlncQty is null or #MonthSumQry1.InvBlncQty=0 '
    +' or #MonthSumQry1.InvBlncAmount is null or #MonthSumQry1.InvBlncAmount=0 then null'
    +' else #MonthSumQry1.InvBlncAmount/#MonthSumQry1.InvBlncQty end As Price4'

    +',case when #MonthSumQry1.InvLMAmount is null or #MonthSumQry1.InvLMAmount=0 then null'
    +' else #MonthSumQry1.InvLMAmount end as aInvLMQty'
    +',case when #MonthSumQry1.InvInAmount is null or #MonthSumQry1.InvInAmount=0 then null'
    +' else #MonthSumQry1.InvInAmount end as aInvInQty'
    +',case when (IsNull(#MonthSumQry1.InvLMAmount,0)'
    +'+IsNull(#MonthSumQry1.InvInAmount,0)'
    +'-IsNull(#MonthSumQry1.InvBlncAmount,0))=0 then null '
    +' else (IsNull(#MonthSumQry1.InvLMAmount,0)'
    +'+IsNull(#MonthSumQry1.InvInAmount,0)'
    +'-IsNull(#MonthSumQry1.InvBlncAmount,0)) end As aInvOutQty'
    +',case when #MonthSumQry1.InvBlncAmount is null or #MonthSumQry1.InvBlncAmount=0 then null'
    +' else #MonthSumQry1.InvBlncAmount end as aInvBlncQty'
    +' From #MonthSumQry1'
    +' Left Join Item On #MonthSumQry1.ItemCode=Item.ItemCode'
    +' Left Join Dept On #MonthSumQry1.DeptCode=Dept.DeptCode'
    +' Left Join Uom On Item.UomCode=Uom.UomCode';
    AdoQry_tmp1.Connection:=AdoQry_Tmp.Connection;
    with AdoQry_Tmp do
    begin
      Close;
      sql.text:=Condition;
      open;
      First;
      while not eof do
      begin
        if fieldbyname('aInvBlncQty').AsFloat<>0 then
        begin
          AdoQry_tmp1.Close;
          AdoQry_tmp1.sql.Text:='update wipInv set wipAmount='+AdoQry_tmp.fieldbyname('aInvBlncQty').AsString
                               +' where wipInv.DeptCode='+QuotedStr(ExtEdt_Dept.Text)
                               +' and wipInvMonth='+QuotedStr(MEdt_Month.Text)
                               +' and wipInv.ItemCode='+QuotedStr(AdoQry_tmp.fieldbyname('ItemCode').AsString);
          AdoQry_tmp1.ExecSQL;
        end;
        Next;
      end;
    end;

    ConditionHint:='部门：'+Lbl_Dept.Caption+'/月份： '+MEdt_Month.Text;
    ModalResult:=mrOk;
end;

procedure TFrm_Sfc_MonthSumQry_C.FormCreate(Sender: TObject);
begin
  inherited;
  MEdt_Month.Text:=FormatDateTime('yyyy.mm',IncMonth(Now,-1));
end;
procedure TFrm_Sfc_MonthSumQry_C.SetUserParam(Param1,Param2,Param3,Param4,Param5,//设置入口参数
      Param6:String);
begin
  ItemType:=Param1;
end;

end.