sfc_monthsumqrybcp_c.pas

一个MRPII系统源代码版本

unit Sfc_MonthSumQrybcp_C;

Interface

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

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

var
  Frm_Sfc_MonthSumQrybcp_C: TFrm_Sfc_MonthSumQrybcp_C;

implementation

uses Sys_Global;

{$R *.DFM}

procedure TFrm_Sfc_MonthSumQrybcp_C.btn_okClick(Sender: TObject);
var
  PriorMonth,DeptStr:String;
  AllBomqty:real;
begin
  if ExtEdt_Dept.Text='' then
  begin
    DispInfo('请输入部门代码',1);
    Abort;
  end;
  try
    AdoQry_tmp.Close;
    AdoQry_tmp.sql.text:='drop table #sonqty';
    AdoQry_tmp.ExecSQL;
  except
  end;
  inherited;
  AdoQry_tmp1.Connection:=AdoQry_Tmp.Connection;
  AdoQry_yin.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,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';
  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=1 and WipInvMonth='''+PriorMonth+''''
    +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=1 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;

  //计算外壳的金额
  if ExtEdt_Dept.Text<>'' then
    DeptStr:=' And wipProduction.DeptCode like '''+ExtEdt_Dept.Text+'%''';

  with AdoQry_tmp do
  begin
    Close;
    sql.text:='create table #sonqty(ItemCode varchAr(16),sonallqty decimal(20,8) default 0)';
    ExecSQL;
    Close;
    sql.text:='insert #sonqty(ItemCode,sonallqty) '+
              'select sonItemCode,sum(wipqty*sonqty*(1+sonScrAp/100)) '+
              'from wipProduction '+
              'where WiPMonth='+quotedstr(MEdt_Month.Text)+DeptStr+
              ' group by sonItemCode Order by sonItemCode ' +
              ' delete from #sonqty where sonallqty=0 or ItemCode is null ';
    ExecSQL;
    Close;
    sql.text:='update wipProduction set wipProduction.wipAmount='+
              '(wipProduction.wipqty*wipProduction.sonqty*(1+wipProduction.sonScrAp/100)/#sonqty.sonallqty)*#MonthSumQry1.InvoutAmount '+
              ' from #sonqty,#MonthSumQry1 '+
              ' where wipProduction.sonItemCode=#sonqty.ItemCode'+
              '     and wipProduction.sonItemCode=#MonthSumQry1.ItemCode '+
              '     and  wipProduction.WiPMonth='+quotedstr(MEdt_Month.Text)+DeptStr;
    ExecSQL;

  end;
{
  //那些印刷钢板没有相应的外壳
  with AdoQry_yin do
  begin
    Close;
    sql.text:='select * into Temp01 from #MonthSumQry1 '+
              'select * into Temp02 from #sonqty ';
    ExecSQL;
    Close;
    sql.text:='select #MonthSumQry1.ItemCode,Item.ItemName,#MonthSumQry1.Invoutqty, '+
              '#MonthSumQry1.InvoutAmount into aa from #MonthSumQry1 '+
              'left join Item on Item.ItemCode=#MonthSumQry1.ItemCode '+
              'where #MonthSumQry1.ItemCode not in (select ItemCode from #sonqty)';
    ExecSQL;
  end;
}
  ////计算外壳的期初、本月生产及结存----本月耗用是抵减出来的
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Delete #MonthSumQry';
  AdoQry_Tmp.ExecSQL;
   //计算本月生产数量及金额
  if ExtEdt_Dept.Text<>'' then
    DeptStr:=' And wipProduction.DeptCode like '''+ExtEdt_Dept.Text+'%''';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.Text:='Insert #MonthSumQry'
    +' Select wipProduction.ItemCode'
    +',IsNull(wipProduction.DeptCode,'''')'
    +','''' '
    +',0'
    +',0'
    +',sum(wipProduction.wipqty)'
    +',sum(wipProduction.wipAmount)'
    +',0'
    +',0'
    +',0,0,null'
    +' From wipProduction '
    +' Where wipProduction.wiPMonth='''+MEdt_Month.Text+''''
    +DeptStr
    +' Group By wipProduction.ItemCode,wipProduction.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'