sledsaledsctgtp.pas

详细的ERP设计资料

      +' where RecordState<>'+Quotedstr('删除')
      +' and RecordState<>'+Quotedstr('作废')
      +' and ID='+inttostr(SGoodsID);
    TempAds.Open;
  end;

  if (Guarder.UseDiscountSLFlag='是') then
  begin
    TempActualCost.Close;   //取当前客户,当前商品的折扣率
    TempActualCost.CommandText :=' SELECT b.Date,a.PriceGoal '
      +' FROM SLSaleDiscountDetail a  '
      +' LEFT JOIN SLSaleDiscountMaster  b on b.ID=a.MasterID '
      +' where b.RecordState<>'+Quotedstr('删除')
      +' and b.RecordState<>'+Quotedstr('作废')
      +' and   a.GoodsID='+inttostr(SGoodsID)
      +' and b.ClientID='+inttostr(SClientID)
      +'  order by b.Date DESC ';
    TempActualCost.Open;
    adsDetail.FieldByName('PriceStandard').AsFloat := TempAds.FieldByName('PriceGoal').AsFloat  ;
    if TempActualCost.FieldByName('PriceGoal').AsFloat=0 then
      adsDetail.FieldByName('PriceRate').AsFloat :=100
      else
      adsDetail.FieldByName('PriceRate').AsFloat :=TempActualCost.FieldByName('PriceGoal').AsFloat  ;

    adsDetail.FieldByName('PriceGoal').AsFloat :=
      TempAds.FieldByName('PriceGoal').AsFloat*
        adsDetail.FieldByName('PriceRate').AsFloat/100  ;
    adsDetail.FieldByName('PriceBase').AsFloat :=
      TempAds.FieldByName('PriceGoal').AsFloat*
        adsDetail.FieldByName('PriceRate').AsFloat/100  ;
    adsDetail.FieldByName('DiscountAmount').AsFloat :=
      adsDetail.FieldByName('GoalQuantity').AsFloat*(
       100-adsDetail.FieldByName('PriceRate').AsFloat);
  end else
  begin
    adsDetail.FieldByName('PriceGoal').AsFloat := TempAds.FieldByName('PriceGoal').AsFloat  ;
    adsDetail.FieldByName('PriceBase').AsFloat := TempAds.FieldByName('PriceGoal').AsFloat  ;
    adsDetail.FieldByName('PriceRate').AsFloat :=100;
    adsDetail.FieldByName('PriceStandard').AsFloat := TempAds.FieldByName('PriceGoal').AsFloat  ;
    adsDetail.FieldByName('DiscountAmount').AsFloat := 0;
  end;
end;

procedure TSLEdSaleDsctGTPForm.ClientQLDBLookupEnter(Sender: TObject);
begin
  inherited;
  GEdit1.Enabled :=true;
  GEdit1.Text :='0';
  GEdit1.Enabled :=false;
end;

procedure TSLEdSaleDsctGTPForm.ClientQLDBLookupExit(Sender: TObject);
var  adoTemp: TADOQuery;
     FstDate:Tdatetime;
begin
  inherited;
  if   (adsMaster.fieldbyname('ClientID').IsNull) or
          (adsMaster.fieldbyname('ClientID').AsInteger=0)  then exit;
  adoTemp := TADOQuery.Create(nil);
  adoTemp.Connection := CommonData.acnConnection;
  with adoTemp do
  begin
    close;
    sql.Text :=' select sum(isnull(AmountD,0)) as Balance from '
    +' (  select (isnull(a.amount,0)-isnull(a.Discount,0)+isnull(a.TaxAmount,0) '
    +' +isnull(a.SundryFee,0) )*Isnull(ModeDC,1)*Isnull(ModeC,1) as amountD                         '
    +' from  SLSaleDetail a                                                     '
    +' left outer join  SLSaleMaster b on b.ID=a.MasterID                       '
    +' where b.RecordState<>'+Quotedstr('删除')+' and b.ClientID='
    + adsMaster.fieldbyname('ClientID').AsString
    +' UNION ALL                                                   '
    +' select (isnull(a.amount,0)-isnull(a.Discount,0)+isnull(a.TaxAmount,0) '
    +' +isnull(a.SundryFee,0) )*Isnull(ModeDC,1)*Isnull(ModeC,1)*(-1) as amountD                 '
    +' from  PCPurchaseDetail a                                              '
    +' left outer join  PCPurchaseMaster b on b.ID=a.MasterID                '
    +' where b.RecordState<>'+Quotedstr('删除')+' and b.ClientID='
    + adsMaster.fieldbyname('ClientID').AsString
    +' UNION ALL                                                             '
    +' select (Isnull(AmountD,0)+Isnull(AmountRed,0) )*Isnull(ModeDC,1)*Isnull(ModeC,1)*(-1)     '
    +' as AmountD                                                            '
    +' from FNClearSLMaster                                                  '
    +' where RecordState<>'+Quotedstr('删除')+' and ClientID='
    + adsMaster.fieldbyname('ClientID').AsString
    +' UNION ALL                                                             '
    +' select (Isnull(AmountC,0)+Isnull(AmountRed,0) )*Isnull(ModeDC,1)*Isnull(ModeC,1)          '
    +' as AmountD                                                            '
    +' from FNClearPCMaster                                                  '
    +' where RecordState<>'+Quotedstr('删除')+' and ClientID='
    + adsMaster.fieldbyname('ClientID').AsString +'  ) as a ';
    open;
    GEdit1.Enabled :=true;
    if  adoTemp.IsEmpty then     GEdit1.Text :='0'
      else   GEdit1.Text :=fieldbyname('Balance').asstring;
    GEdit1.Enabled :=False;
    close;
    sql.Text :=' select QuotaAmountMin from SLCredit where '
        +' RecordState<>'+ Quotedstr('删除')
        +' and ClientID='+ adsMaster.fieldbyname('ClientID').AsString ;
    open;

    if (NewOrEditFlag='新增状态') and not (fieldbyname('QuotaAmountMin').IsNull) then
    begin
      adsMaster.Edit;
      adsMaster.fieldbyname('ClearDate').AsDateTime :=
          adsMaster.fieldbyname('Date').AsDateTime+ fieldbyname('QuotaAmountMin').AsInteger
    end;
  end;
end;

procedure TSLEdSaleDsctGTPForm.FormActivate(Sender: TObject);
var  adoTemp: TADOQuery;
begin
  inherited;
  adoTemp := TADOQuery.Create(nil);
  adoTemp.Connection := CommonData.acnConnection;
  adoTemp.Close;
  adoTemp.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#TpCostPrice')
        +' )) DROP TABLE #TpCostPrice ' ;
  adoTemp.ExecSQL;
  
  adoTemp.Close;
  adoTemp.SQL.Text :=' create table #TpCostPrice ('
      +'	ID [int] IDENTITY (1, 1) NOT NULL ,'
      +'	GoodsID [int] NULL ,               '
      +'	GoalQuantity [float] NULL ,   '
      +'	CostPrice [float] NULL ,           '
      +'	Amount [float] NULL)   '   ;
  adoTemp.ExecSQL;

  adoTemp.Close;
  adoTemp.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#TempPackUnit12')
        +' )) DROP TABLE #TempPackUnit12 ' ;
  adoTemp.ExecSQL;
  adoTemp.Close;
  adoTemp.SQL.Text :=' create table #TempPackUnit12 ('
      +'	PID [int] IDENTITY (1, 1) NOT NULL ,'
      +'	ID [int] NULL ,'
      +'	[CreateDate] [datetime] NULL ,       '
      +'	[CreateUserID] [int] NULL ,          '
      +'	[RecordState] [varchar] (12)  NULL , '
      +'	[Name] [varchar] (20)   NULL ,'
      +'	[ExchangeRate] [decimal](9, 4) NULL ,'
      +'	[GoalUnitID] [int] NULL ,            '
      +'	[IsGoalUnit] [bit] NOT NULL    )     ';
  adoTemp.ExecSQL;


  with adoTemp do
  begin
    close;     //计算销售产品的临时成本单价
    Sql.Text :=' truncate Table #TpCostPrice'  ;
    ExecSQL;
    close;
    Sql.Text :=' insert into  #TpCostPrice ('
      +' 	GoodsID, GoalQuantity,Amount )'
      +' select goodsid,sum(isnull(GoalQuantity,0)),sum(isnull(Amount,0)) '
      +' from (   '
      +' select GoodsID,GoalQuantity*Isnull(ModeDC,1)*Isnull(ModeC,1) as GoalQuantity,Amount*Isnull(ModeDC,1)*Isnull(ModeC,1) '
      +'  as Amount,recordstate  from PCgoodsIndetail a  '
      +' left outer join PCgoodsInMaster b on b.id=a.MasterID WHERE Amount<>0'
      +' UNION ALL                                                 '
      +' select GoodsID,GoalQuantity*Isnull(ModeDC,1)*Isnull(ModeC,1) as GoalQuantity,Amount*Isnull(ModeDC,1)*Isnull(ModeC,1)'
      +'  as Amount,recordstate from YDgoodsIndetail a                                  '
      +' left outer join YDgoodsInMaster b on b.id=a.MasterID Where Amount<>0'
      +' UNION ALL                                                                          '
      +' select GoodsID,GoalQuantity*Isnull(ModeDC,1)*Isnull(ModeC,1) as GoalQuantity,Amount*Isnull(ModeDC,1)*Isnull(ModeC,1)           '
      +'  as Amount,recordstate  from YDgoodsIndetail a                                 '
      +' left outer join STgoodsCountOffMaster b on b.id=a.MasterID Where Amount<>0'
      +' and BillMode like '+Quotedstr('%库存盘盈%' )+ ' ) as a  '
      +' where RecordState<>'+QuotedStr('删除')
      +' group by GoodsID  ' ;
    ExecSQL;
    close;
    Sql.Text :=' Update  #TpCostPrice  set CostPrice=abs(Amount/ '
        +' GoalQuantity) where GoalQuantity<>0';
    ExecSQL;
    close;
    Sql.Text :=' insert into  #TpCostPrice (GoodsID, CostPrice )'
        +' select ID,abs(PricePurchase) from DAgoods where ID not in '
        +' (select distinct GoodsID from #TpCostPrice)' ;
    ExecSQL;
  end;
  ClientQLDBLookup.SetFocus;
  if Guarder.FillInPrintFlag='是' then
  begin
    ToolButton11.Visible :=True;
    FillInPrintMenu.Visible :=True;
  end else
  begin
    ToolButton11.Visible :=False ;
    FillInPrintMenu.Visible :=False ;
  end;
  if Guarder.RepeatPrintFlag='是' then
  begin
    ToolButton12.Visible :=True ;
    RepeatPrintMenu.Visible :=True ;
  end else
  begin
    ToolButton12.Visible :=False ;
    RepeatPrintMenu.Visible :=False ;
  end;
end;

procedure TSLEdSaleDsctGTPForm.FormDeactivate(Sender: TObject);
var  adoTemp: TADOQuery;
begin
  inherited;
  adoTemp := TADOQuery.Create(nil);
  adoTemp.Connection := CommonData.acnConnection;
  adoTemp.Close;
  adoTemp.SQL.Text :=' IF EXISTS(  SELECT * FROM tempdb..sysobjects '
        +' WHERE ID = OBJECT_ID('+Quotedstr('tempdb..#TpCostPrice')
        +' )) DROP TABLE #TpCostPrice ' ;
  adoTemp.ExecSQL;
end;


function TSLEdSaleDsctGTPForm.CreateReport: TQuickRep;
begin
  Result := TSLSaleVoucherReport.Create(Self);
  TBaseVoucherReport(Result).SetMasterDataSet(MasterDataSet);
end;

procedure TSLEdSaleDsctGTPForm.adsDetailGoalQuantityChange(Sender: TField);
var ExChRate :real;
begin
  PackUnit.Locate('ID', adsDetail.fieldbyname('PackUnitID').AsString, []);
  if PackUnit.fieldbyname('ExchangeRate').IsNull then ExChRate :=1
     else ExChRate :=PackUnit.fieldbyname('ExchangeRate').AsFloat;
  adsDetail.FieldByName('Amount').AsFloat :=
    adsDetail.fieldbyname('GoalQuantity').asfloat*adsDetail.fieldbyname('PriceGoal').AsFloat;

  adsDetail.FieldByName('Quantity').AsFloat :=
    adsDetail.fieldbyname('GoalQuantity').AsFloat/ExChRate;

  if adsDetail.FieldByName('Quantity').AsFloat<>0 then
  adsDetail.FieldByName('PriceBase').AsFloat :=
    adsDetail.fieldbyname('Amount').AsFloat/adsDetail.FieldByName('Quantity').AsFloat;
end;

procedure TSLEdSaleDsctGTPForm.LookupPackUintEnter(Sender: TObject);
var GoodsIDstr :string;
begin
  if adsDetail.FieldByName('GoodsID').IsNull then Exit;
  GoodsIDstr :=adsDetail.fieldbyname('GoodsID').asstring;
  if Trim(GoodsIDstr)='' then  exit;
  LookupPackUint.ListSource := PackUintFltDS;
  PackUintFltAQ.Close;
  PackUintFltAQ.CommandText :=' select ID, Name, ExchangeRate, GoalUnitID,'
      +' IsGoalUnit from MSUnit where RecordState<>'+Quotedstr('删除')
      +' and  GoalUnitID in (select UnitID from DaGoods where ID='
      +GoodsIDstr +' ) order by GoalUnitID,ExchangeRate' ;
  PackUintFltAQ.Open;
end;

procedure TSLEdSaleDsctGTPForm.FillInPrintActionExecute(Sender: TObject);

  function CurrencyUpperCaseHitch(Value: Currency): string;

    function OnesPlace(X: Double): Integer;
    begin
      Result := Trunc(X);
      Result := Result - Result div 10 * 10;
    end;

  var
    I, N: Integer;
    S: string;
  begin
    Result := '';
    Value := Round(Value * Power(10, 2)) / Power(10, 2);
    for I := 7 downto 0 do
    begin
      N := OnesPlace(Value / Power(10, I - 2));
      S := NumberToHZ(N, 1);
      if S = '' then S := '零';
      if Result = '' then Result := S
      else Result := Result + '     ' + S;
    end;
  end;

var
  ExePath: string;
  S1, S2, S3, S4, S5, S6, S7: string;
begin
  inherited;
  ExePath := ExtractFilePath(Application.ExeName);
  with DetailDataSet do
  begin
    First;
    while not Eof do
    begin
      S1 := S1 + #13#10 + FieldByName('GoodsName').DisplayText;
      S2 := S2 + #13#10 + FieldByName('GoodsSpec').DisplayText;
      S3 := S3 + #13#10 + FieldByName('GoalUnit').DisplayText;
      S4 := S4 + #13#10 + FieldByName('GoalQuantity').DisplayText;
      S5 := S5 + #13#10 + FieldByName('PriceGoal').DisplayText;
      S6 := S6 + #13#10 + FieldByName('Amount').DisplayText;
      S7 := S7 + #13#10 + FieldByName('Memo').DisplayText;
      Next;
    end;
  end;
  with TReportRuntime.Create(Self) do
  begin
    ReportFile := ExePath + 'Reports\Sale.ept';
    SetDataSet('Master', MasterDataSet);
    SetDataSet('Detail', DetailDataSet);
    SetVarValue('S1', S1);
    SetVarValue('S2', S2);