unit STEdYDGoodsInCost;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, BaseVoucherEditA, Menus, ActnList, DB, ComCtrls, StdCtrls, Mask,
  DBCtrls, ExtCtrls, ToolWin, Grids, DBGrids, QLDBGrid, ADODB, GEdit,
  QLDBLkp, Buttons, Types,QuickRpt;



type
  TSTEdYDGoodsInCostForm = class(TBaseVoucherEditAForm)
    Label2: TLabel;
    Label3: TLabel;
    ClientName: TADODataSet;
    EmployeeName: TADODataSet;
    GoodName: TADODataSet;
    Label5: TLabel;
    PackUnit: TADODataSet;
    dsPackUnit: TDataSource;
    DSClientName: TDataSource;
    DSEmployeeName: TDataSource;
    LookupEmployee: TQLDBLookupComboBox;
    TempAds: TADODataSet;
    adsMaster: TADODataSet;
    adsDetail: TADODataSet;
    adsDetailID: TAutoIncField;
    adsDetailMasterID: TIntegerField;
    adsDetailGoodsID: TIntegerField;
    adsDetailQuantity: TBCDField;
    adsDetailPackUnitID: TIntegerField;
    adsDetailPriceBase: TBCDField;
    adsDetailAmount: TBCDField;
    adsDetailGoalQuantity: TBCDField;
    adsDetailGoalUnitID: TIntegerField;
    adsMasterID: TAutoIncField;
    adsMasterCreateDate: TDateTimeField;
    adsMasterCreateUserID: TIntegerField;
    adsMasterRecordState: TStringField;
    adsMasterDate: TDateTimeField;
    adsMasterCode: TStringField;
    adsMasterClientID: TIntegerField;
    adsMasterBillMode: TStringField;
    adsMasterPeriodID: TIntegerField;
    adsMasterClearDate: TDateTimeField;
    adsMasterMemo: TStringField;
    adsMasterSundryFee: TBCDField;
    Label8: TLabel;
    adsMasterClientName: TStringField;
    DiscountMode: TAction;
    adsMasterEmployeeID: TIntegerField;
    adsMasterApportion: TStringField;
    adsMasterDeliver: TStringField;
    adsMasterOriginID: TIntegerField;
    adsMasterOriginTable: TStringField;
    adsMasterBillAffix: TBytesField;
    adsDetailMemo: TStringField;
    adsMasterBrief: TStringField;
    BriefComboBox: TDBComboBox;
    Label9: TLabel;
    adsDetailGoodsSpec: TStringField;
    adsGoodsSpec: TADODataSet;
    dsGoodsSpec: TDataSource;
    adsMasterEmployee: TStringField;
    StockQuerry: TAction;
    adsMasterWarehouseID: TIntegerField;
    adsWarehouse: TADODataSet;
    dsWarehouse: TDataSource;
    ClientQLDBLookup: TQLDBLookupComboBox;
    adsMasterModeDC: TIntegerField;
    adsMasterModeC: TIntegerField;
    DBEdit2: TDBEdit;
    Label1: TLabel;
    adsDetailQuantityPcs: TBCDField;
    adsDetailDiscount: TBCDField;
    adsDetailTaxAmount: TBCDField;
    adsDetailSundryFee: TBCDField;
    adsDetailPackunit: TStringField;
    FeeAportPopupMenu: TPopupMenu;
    MenuItem1: TMenuItem;
    MenuItem2: TMenuItem;
    GoalUnit: TADODataSet;
    dsGoalUnit: TDataSource;
    adsDetailGoalUnit: TStringField;
    adsDetailGoodsName: TStringField;
    Label4: TLabel;
    DBEdit3: TDBEdit;
    ADOQuery: TADOQuery;
    Label7: TLabel;
    DBEdit5: TDBEdit;
    Label6: TLabel;
    DBEdit4: TDBEdit;
    SpeedButton1: TSpeedButton;
    TpcPanel: TPanel;
    BitBtn1: TBitBtn;
    BitBtn3: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn4: TBitBtn;
    SpeedButton2: TSpeedButton;
    DBEdit1: TDBEdit;
    TempQry: TADOQuery;
    ClearSundryFeeAct: TMenuItem;
    adsDetailPriceCost: TBCDField;
    Label10: TLabel;
    DBEdit6: TDBEdit;
    adsMasterLastCostDate: TDateTimeField;
    adsMasterIndirectFee: TBCDField;
    adsDetailPriceGoal: TBCDField;
    adsDetailOriginID: TIntegerField;
    adsDetailOriginTable: TStringField;
    SpeedButton3: TSpeedButton;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure MenuItem1Click(Sender: TObject);
    procedure MenuItem2Click(Sender: TObject);
    procedure ClearSundryFeeActClick(Sender: TObject);
    procedure adsDetailSundryFeeChange(Sender: TField);
    procedure SaveActionExecute(Sender: TObject);
    procedure adsDetailPriceGoalChange(Sender: TField);
    procedure DBEdit4Exit(Sender: TObject);

  private
    { Private declarations }
   LastDate :TDatetime;
  public
    { Public declarations }
    procedure Open(VoucherID: Integer); override;
    procedure New; override;
  end;

var
  STEdYDGoodsInCostForm: TSTEdYDGoodsInCostForm;

implementation

uses CommonDM, WSUtils, WSSecurity, QLDBAgg;
{$R *.dfm}
procedure TSTEdYDGoodsInCostForm.New;
begin
  inherited;
  TempQry.Close;
  TempQry.sql.text :='select max(ClearDate) as ClearDate from '
    +' STYDGoodsInCostMaster  where RecordState<>'
    +Quotedstr('刪除')+' and RecordState<>'+Quotedstr('作廢');
  TempQry.Open;
  if TempQry.FieldByName('ClearDate').IsNull  then   LastDate := EncodeDate(2001,01,01)
    else LastDate :=TempQry.FieldByName('ClearDate').AsDateTime ;
  TempQry.Close;
  TempQry.sql.text :='select Apportion from '
    +' STYDGoodsInCostMaster  where ClearDate='
    + Quotedstr(Datetostr(LastDate))
    +' and RecordState<>'+Quotedstr('刪除')
    +' and RecordState<>'+Quotedstr('作廢');
  TempQry.Open;
  if TempQry.IsEmpty then adsMaster.FieldByName('Apportion').AsString :='加權(quán)平均'
   else adsMaster.FieldByName('Apportion').AsString :=TempQry.FieldByName('Apportion').AsString;
  adsMaster.FieldByName('LastCostDate').AsDateTime :=LastDate;
  adsMaster.FieldByName('Date').AsDateTime :=Date;
  adsMaster.FieldByName('ClearDate').AsDateTime :=Date;
  adsMaster.FieldByName('Code').AsString:=GetMaxCode('Code','STYDGoodsInCostMaster',number);
  adsMaster.FieldByName('CreateUserID').AsInteger :=Guarder.UserID;
  adsMaster.FieldByName('BillMode').AsString:='生產(chǎn)成本核算';
  adsMaster.FieldByName('ModeDC').AsInteger :=1;
  adsMaster.FieldByName('ModeC').AsInteger :=1;
  adsMaster.FieldByName('OriginTable').AsString:='STYDGoodsInCostMaster';
  adsMaster.FieldByName('Deliver').AsString:='--';
  SpeedButton1.Enabled :=True;
end;

procedure TSTEdYDGoodsInCostForm.Open(VoucherID: Integer);
begin
  inherited Open(VoucherID);
end;


procedure TSTEdYDGoodsInCostForm.FormClose(Sender: TObject;
var Action: TCloseAction);
begin
  inherited;
  adsMaster.Close;
  adsDetail.Close;
  ClientName.Close;
  EmployeeName.Close;
  GoodName.Close;
  PackUnit.Close;
  adsWarehouse.Close;
  adsGoodsSpec.Close;
end;

procedure TSTEdYDGoodsInCostForm.FormCreate(Sender: TObject);
begin
  inherited;
  VoucherTableName := 'STYDGoodsInCost';
  ClientName.Open;
  EmployeeName.Open;
  GoodName.Open;
  PackUnit.Open;
  adsWarehouse.Open;
  TempAds.Close;
  TempAds.CommandText :=' select Distinct brief from STYDGoodsInCostMaster';
  TempAds.Open;
  TempAds.First;
  while not TempAds.Eof do
  begin
    BriefComboBox.Items.Add(TempAds.FieldByName('brief').AsString);
    TempAds.Next;
  end;
  if  TempAds.IsEmpty then  BriefComboBox.Items.Add('生產(chǎn)成本核算');
end;


procedure TSTEdYDGoodsInCostForm.FormActivate(Sender: TObject);
begin
  inherited;
  ClientQLDBLookup.SetFocus;
end;


procedure TSTEdYDGoodsInCostForm.BitBtn1Click(Sender: TObject);
begin
  TpcPanel.Visible :=true;
  TpcPanel.Repaint;
  BitBtn1.Enabled :=False;
  adsMaster.Edit;
  adsMaster.FieldByName('Apportion').AsString :=BitBtn1.Caption;
  DetailDataSource.DataSet :=nil;
  TempQry.Close;
  TempQry.SQL.Text :='select * from #YDGoodsInCostPrice ';
  TempQry.open;
  adsDetail.First;
  while not adsDetail.Eof do
  begin
    adsDetail.Edit ;
    TempQry.Locate('GoodsID',adsDetail.fieldbyname('GoodsID').AsInteger,[]);
    adsDetail.FieldByName('PriceGoal').AsFloat :=TempQry.fieldbyname('PriceCost').AsFloat;
    adsDetail.Next;
  end;
  DetailDataSource.DataSet :=adsDetail;
  BitBtn1.Enabled :=true;
  TpcPanel.Visible :=False;
end;

procedure TSTEdYDGoodsInCostForm.BitBtn3Click(Sender: TObject);
  procedure FindNewPrice ;
  var TotalGoalQuantity,TotalAmount :real;
  begin
    ADOQuery.Close;
    ADOQuery.SQL.Text :='select sum(Isnull(GoalQuantity,0)*Isnull(ModeDC,1)*'
     +' Isnull(ModeC,1)) '
     +' as GoalQuantity from '
     +' YDGoodsInDetail a '
     +' Left outer join YDGoodsInMaster b on b.ID=a.MasterID '
     +' where b.Recordstate <>'+Quotedstr('刪除')
     +' and b.Recordstate <>'+Quotedstr('作廢')
     +' and GoodsID='+adsDetail.fieldbyname('GoodsID').AsString;
    ADOQuery.Open;
    TotalGoalQuantity :=ADOQuery.fieldbyname('GoalQuantity').AsFloat;
    ADOQuery.Close;
    ADOQuery.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
          +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#TempCostPrice')
          +' )) DROP TABLE #TempCostPrice ' ;
    ADOQuery.ExecSQL;
    ADOQuery.Close;
    ADOQuery.SQL.Text :='select a.ID,GoodsID, '
     +' Isnull(GoalQuantity,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) as GoalQuantity ,'
     +' PriceGoal,'
     +' Isnull(Amount,0)*Isnull(ModeDC,1)*Isnull(ModeC,1) as Amount '
     +' into #TempCostPrice '
     +' from '
     +' PCGoodsInDetail  a '
     +' Left outer join PCGoodsInMaster b on b.ID=a.MasterID '
     +' where GoodsID='+adsDetail.fieldbyname('GoodsID').AsString
     +' and b.Recordstate <>'+Quotedstr('刪除')
     +' and b.Recordstate <>'+Quotedstr('作廢')
     +' Order By a.ID Desc ' ;
    ADOQuery.ExecSQL;
    ADOQuery.Close;
    ADOQuery.SQL.Text :='select * from #TempCostPrice ';
    ADOQuery.open;
    ADOQuery.First;
    while not ADOQuery.Eof do
    begin
      if TotalGoalQuantity>ADOQuery.fieldbyname('GoalQuantity').AsFloat then
      begin
        TotalGoalQuantity :=TotalGoalQuantity-ADOQuery.fieldbyname('GoalQuantity').AsFloat ;
        ADOQuery.Delete ;
      end else
      begin
        ADOQuery.edit;
        ADOQuery.fieldbyname('GoalQuantity').AsFloat :=
           ADOQuery.fieldbyname('GoalQuantity').AsFloat-TotalGoalQuantity;
        Break;
      end;
    end;
    TotalGoalQuantity :=adsDetail.FieldByName('GoalQuantity').AsFloat ;
    TotalAmount :=0;
    ADOQuery.First;
    while not ADOQuery.Eof do
    begin
      if TotalGoalQuantity>adsDetail.FieldByName('GoalQuantity').AsFloat then
      begin
        TotalAmount :=TotalAmount+adsDetail.FieldByName('GoalQuantity').AsFloat*
          ADOQuery.FieldByName('PriceGoal').AsFloat;
        TotalGoalQuantity :=TotalGoalQuantity-adsDetail.FieldByName('GoalQuantity').AsFloat;
      end else
      begin
        TotalAmount :=TotalGoalQuantity*ADOQuery.FieldByName('PriceGoal').AsFloat;
        Break;
      end;
    end;
  adsDetail.Edit;
  adsDetail.FieldByName('Amount').AsFloat := TotalAmount;
  adsDetail.FieldByName('PriceGoal').AsFloat :=TotalAmount/adsDetail.FieldByName('GoalQuantity').AsFloat;
  end;
begin
  TpcPanel.Visible :=true;
  TpcPanel.Repaint;
  BitBtn3.Enabled :=False;
  adsMaster.Edit;
  adsMaster.FieldByName('Apportion').AsString :=BitBtn3.Caption;

  DetailDataSource.DataSet :=nil;
  adsDetail.First;
  while not adsDetail.Eof do
  begin
    FindNewPrice;
    adsDetail.Next;
  end;
  DetailDataSource.DataSet :=adsDetail;
  BitBtn3.Enabled :=true;
  TpcPanel.Visible :=False;
end;

procedure TSTEdYDGoodsInCostForm.BitBtn2Click(Sender: TObject);
  procedure FindNewPrice ;
  var TotalGoalQuantity,TotalAmount :real;