sfc_monthsumqrybcppj_c.pas

一个MRPII系统源代码版本

unit Sfc_MonthSumQrybcppj_C;

Interface

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

Type
  TFrm_Sfc_MonthSumQrybcppj_C = Class(TFrm_Base_Condition)
    Label1: TLabel;
    Label2: TLabel;
    MEdt_Month: TMaskEdit;
    ExtEdt_Dept: TExtEdit;
    Lbl_Dept: TLabel;
    AdoQry_tmp1: TAdoQuery;
    CheckBox1: TCheckBox;
    procedure btn_okClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frm_Sfc_MonthSumQrybcppj_C: TFrm_Sfc_MonthSumQrybcppj_C;

implementation

uses Sys_Global, Sfc_MonthSumQrybcppj;

{$R *.DFM}

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

  inherited;
  AdoQry_tmp1.Connection:=AdoQry_Tmp.Connection;
  PriorMonth:=FormatDateTime('yyyy.mm',IncMonth(StrToDateTime(
    MEdt_Month.Text+'.01'),-1));
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Delete #MonthSumQry';
  AdoQry_Tmp.ExecSQL;
  AdoQry_tmp.Close;
  AdoQry_tmp.SQL.Text:='drop table #Temp01,Temp02';
  try
     AdoQry_Tmp.ExecSQL;
  except
  end;
  if ExtEdt_Dept.Text<>'' then
    DeptStr:=' And InvOutBill.DeptCode like '''+ExtEdt_Dept.Text+'%''';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select InvOutBillLine.ItemCode'
    +',IsNull(InvOutBill.DeptCode,'''')'
    +',InvOutBill.whCode'
    +',0'
    +',0'
    +',Sum(InvOutBillLine.InvBillQty)'
    +',Sum(InvOutBillLine.InvBillnotaxAmount)'
    +',0,0,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;
  AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select ItemCode,'
    +' DeptCode'
    +',null'
    +',wipInvQty'
    +',wipAmount'
    +',0'
    +',0'
    +',0'
    +',0'
    +',0'
    +',0'
    +',null'
    +' From WipInv'
    +' Where ItemType=3 and WipInvMonth='''+PriorMonth+''''         //3:配件车间原材料
    +DeptStr;
  AdoQry_Tmp.ExecSQL;
//对上期结存有数量但是没有金额的物料，其价格从车间月末价格定义中找(wipPrice)
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='update #MonthSumQry '
    +' set InvLMAmount=InvLMQty*wipPrice.Itemprice '
    +' from wipPrice where #MonthSumQry.ItemCode=wipPrice.ItemCode and '
    +' #MonthSumQry.Invlmqty<>0 and #MonthSumQry.InvlmAmount=0 ';
  AdoQry_Tmp.ExecSQL;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select ItemCode,'
    +' DeptCode'
    +',null'
    +',0'
    +',0'
    +',0'
    +',0'
    +',0'
    +',0'
    +',wipInvQty'
    +',0'
    +',null'
    +' From WipInv'
    +' Where ItemType=3 and WipInvMonth='''+MEdt_Month.Text+''''
    +DeptStr;
  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,0,0,'
      +' 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  '
      +' #MonthSumQry1.ItemCode=wipPrice.ItemCode '
      //计算原材料的本月耗用
      +' update #MonthSumQry1 set InvoutAmount=InvlmAmount+InvInAmount-InvblncAmount, '
      +' Invoutqty=Invlmqty+InvInqty-Invblncqty';
    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,0,0,'
      +' 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  '
      +' #MonthSumQry1.ItemCode=wipPrice.ItemCode '
      //计算原材料的本月耗用
      +' update #MonthSumQry1 set InvoutAmount=InvlmAmount+InvInAmount-InvblncAmount, '
      +' Invoutqty=Invlmqty+InvInqty-Invblncqty';
    AdoQry_Tmp.ExecSQL;
  end;

  ////计算配件车间半成品的期初、本月耗用及结存----本月生产是抵减出来的
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Delete #MonthSumQry';
  AdoQry_Tmp.ExecSQL;
   //计算本月耗用数量即生产入库数量
  if ExtEdt_Dept.Text<>'' then
    DeptStr:=' And i.DeptCode like '''+ExtEdt_Dept.Text+'%''';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry(ItemCode,DeptCode,Invoutqty)'
    +' Select il.ItemCode'
    +',IsNull(i.DeptCode,'''')'
    +',sum(il.InvBillqty)'
    +' From InvInBill i,InvInBillline il '
    +' Where i.InvBillMonth='''+MEdt_Month.Text+''''
    +' and i.InvBillid=il.InvBillid '
    +DeptStr
    +' And ((I.InvBillWHChck=1'
    +' And I.BillTypeCode=''0104''))'
    +' Group By il.ItemCode,i.DeptCode ';
  AdoQry_Tmp.ExecSQL;

   //计算上月结存
  if ExtEdt_Dept.Text<>'' then
    DeptStr:=' And DeptCode like '''+ExtEdt_Dept.Text+'%''';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select ItemCode,'
    +' DeptCode'
    +',null'
    +',wipInvQty'
    +',wipAmount'
    +',0'
    +',0'
    +',0'
    +',0'
    +',0'
    +',0'
    +',null'
    +' From WipInv'
    +' Where ItemType=4 and WipInvMonth='''+PriorMonth+''''   //4:配件车间半成品
    +DeptStr;
  AdoQry_Tmp.ExecSQL;

  //对上期结存有数量但是没有金额的物料，其价格从车间月末价格定义中找(wipPrice)
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='update #MonthSumQry '
    +' set InvLMAmount=InvLMQty*wipPrice.Itemprice '
    +' from wipPrice where #MonthSumQry.ItemCode=wipPrice.ItemCode and '
    +' #MonthSumQry.Invlmqty<>0 and #MonthSumQry.InvlmAmount=0 ';
  AdoQry_Tmp.ExecSQL;
   //计算本月结存
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select ItemCode,'
    +' DeptCode'
    +',null'
    +',0'
    +',0'
    +',0'
    +',0'
    +',0'
    +',0'
    +',wipInvQty'
    +',0'
    +',null'
    +' From WipInv'
    +' Where ItemType=4 and WipInvMonth='''+MEdt_Month.Text+''''
    +DeptStr;
  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 #MonthSumQry2 '