basevoucheredita.pas

企业ERP管理系统

        +' where F.RecordState<>'+Quotedstr('删除')
        +' ) as PCclear Order By Date Desc  ' );
end;

procedure TBaseVoucherEditAForm.CashBalanceExecute(Sender: TObject);
begin
  inherited;
  ShowQueryForm(CashBalance.Caption,CashBalance.Hint,
        ' select  '
        +' a.AccountsID as [帐户序号],b.name as [帐户名称] , '
        +' sum(isnull(AmountD,0)) as  [收入金额], '
        +' sum(isnull(AmountC,0)) as  [支出金额],            '
        +' sum(isnull(AmountD,0))- sum(isnull(AmountC,0)) as [差额/余额]  '
        +' from                                               '
        +' (select Date,a.code,billmode,brief,b.name as Client,a.RecordState,AccountsID,  AmountD, AmountC  '
        +' from FNclearslMaster a left outer join  daclient b on b.id=a.clientid                            '
        +' UNION ALL                                                                                            '
        +' select Date,a.code,billmode,brief,b.name as Client,a.RecordState,AccountsID,  AmountD, AmountC   '
        +' from FNclearPCMaster a left outer join  daclient b on b.id=a.clientid                            '
        +' UNION ALL                                                                                            '
        +' select Date,a.code,billmode,brief,b.name as Client,a.RecordState,AccountsID,  AmountD, AmountC   '
        +' from FNcashinMaster a left outer join  MSEmployee b on b.id=a.clientid                           '
        +' UNION ALL                                                                                            '
        +' select Date,a.code,billmode,brief,b.name as Client,a.RecordState,AccountsID,  AmountD, AmountC   '
        +' from FNcashoutMaster a left outer join  MSEmployee b on b.id=a.clientid                          '
        +' UNION ALL                                                                                            '
        +' select Date,a.code,billmode,brief,b.name as Client,a.RecordState,AccountsID,  0.00, AmountC      '
        +' from FNExpenseMaster a left outer join  MSEmployee b on b.id=a.clientid                          '
        +' UNION ALL                                                                                            '
        +' select Date,a.code,billmode,brief,b.name as Client,a.RecordState,AccountsID,  AmountD, AmountC   '
        +' from FNCashoutInMaster a left outer join  FNAccounts b on b.id=a.clientid     '  //调入帐户记录
        +' UNION ALL                                                                                            '
        +' select Date,a.code,billmode,brief,b.name as Client,a.RecordState,clientid,  AmountC, AmountD   '
        +' from FNCashoutInMaster a left outer join  FNAccounts b on b.id=a.AccountsID ) '  //调出帐户记录
        +' as a left outer join FNAccounts b on b.ID=a.AccountsID                                           '
        +' where a.RecordState<>'+Quotedstr('删除')+'  and b.AccountType not like '+Quotedstr('%业主资金%')
        +' and a.AccountsID<>0 group by a.AccountsID,b.name' );
end;

procedure TBaseVoucherEditAForm.PCPurchaseLegerExecute(Sender: TObject);
begin
  inherited;
  ShowQueryForm(SLsaleLeger.Caption,SLsaleLeger.Hint,
        ' select a.Code as [编号],a.Date as [日期],    '
        +' a.BillMode [业务类别],                      '
        +' a.ClearDate [交货日期],                              '
        +' a.Deliver [交货方式], c.name as [厂商名称] ,         '
        +' E.name as [商品名称],f.name as [包装单位],           '
        +' b.Quantity*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1) as [数量],'
        +' g.name as  [标准单位],      '
        +' b.GoalQuantity*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1) as [标准数量], '
        +' b.PriceBase as [单价],                               '
        +' b.Amount*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1) as [商品金额] ,            '
        +' b.TaxAmount*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1) as [税金] ,             '
        +' b.SundryFee*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1) as [附加费用],          '
        +' b.Discount*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1) as [折扣金额],           '
        +' d.name as [经手人],                                  '
        +' a.RecordState as [凭单状态]                          '
        +' from PCPurchaseDetail  b                                '
        +' left outer join PCPurchaseMaster a on  a.id=b.masterID    '
        +' left outer join  DAClient c     on c. ID=a.ClientID   '
        +' left outer join  MSEmployee  d  on d.id=a.EmployeeID '
        +' left outer join  DAGoods   e   on e.id=b.GoodsID    '
        +' left outer join  MSunit   f   on f.id=b.PackUnitID '
        +' left outer join  MSunit   g   on g.id=b.GoalUnitID'
        +' WHERE  A.RECORDSTATE<>'+Quotedstr('删除')
        +' order by [日期] DESC');
end;

procedure TBaseVoucherEditAForm.ActualStockExecute(Sender: TObject);
var year,month,day :word;
    datestr :string;
begin
  inherited;
  DecodeDate(Date,year, month, day);
  datestr:=datetoSTR(Date);
  ShowQueryForm(ActualStock.Caption,ActualStock.Hint, 'select '
        +' w.name as [仓库名称] ,                           '
        +' E.name as [商品名称],f.name as [包装单位],       '
        +' sum(isnull(b.Quantity,0))  as [数量],            '
        +' g.name as  [标准单位],                           '
        +' sum(isnull(b.GoalQuantity,0))  as [标准数量]     '

        +' FROM ('
        +' select b.recordstate,b.date ,b.warehouseID,GoodsID,PackunitID,'
        +' GoalUnitID, Isnull(ModeDC,1)*Isnull(ModeC,1)*Quantity*(-1) as Quantity,   '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*GoalQuantity*(-1) AS GoalQuantity, '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*Amount*(-1) as Amount from  SLGoodsOutDetail a    '
        +' left outer join SLGoodsOutMaster b on b.ID=a.MasterID   '  //销售出库表
        +' UNION ALL '
        +' select b.recordstate,b.date ,b.warehouseID,GoodsID,PackunitID,'
        +' GoalUnitID, Isnull(ModeDC,1)*Isnull(ModeC,1)*Quantity as Quantity,   '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*GoalQuantity AS GoalQuantity,   '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*Amount AS Amount from  PCGoodsInDetail a  '
        +' left outer join PCGoodsInMaster b on b.ID=a.MasterID   '   //采购入库表
        +' UNION ALL '
        +' select b.recordstate,b.date ,b.warehouseID,GoodsID,PackunitID,'
        +' GoalUnitID, Isnull(ModeDC,1)*Isnull(ModeC,1)*Quantity*(-1) as Quantity,   '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*GoalQuantity*(-1) AS GoalQuantity,  '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*Amount*(-1) AS Amount from  YDGoodsOutDetail a  '
        +' left outer join YDGoodsOutMaster b on b.ID=a.MasterID   ' //生产领料表
        +' UNION ALL '
        +' select b.recordstate,b.date ,b.warehouseID,GoodsID,PackunitID,'
        +' GoalUnitID, Isnull(ModeDC,1)*Isnull(ModeC,1)*Quantity as Quantity,   '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*GoalQuantity AS GoalQuantity,     '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*Amount AS Amount from  YDGoodsInDetail a    '
        +' left outer join YDGoodsInMaster b on b.ID=a.MasterID   '  //生产入库表
        +' UNION ALL '
        +' select b.recordstate,b.date ,b.ClientID as warehouseID,GoodsID,PackunitID,'
        +' GoalUnitID, Isnull(ModeDC,1)*Isnull(ModeC,1)*Quantity*(-1) as Quantity,   '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*GoalQuantity*(-1) AS GoalQuantity,     '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*Amount*(-1) AS Amount from  STGoodsOutInDetail a   '
        +' left outer join STGoodsOutInMaster b on b.ID=a.MasterID   ' //库存变动表 (出货仓库)
        +' UNION ALL '
        +' select b.recordstate,b.date ,warehouseID,GoodsID,PackunitID,'
        +' GoalUnitID, Isnull(ModeDC,1)*Isnull(ModeC,1)*Quantity as Quantity,   '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*GoalQuantity AS GoalQuantity,      '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*Amount AS Amount from  STGoodsOutInDetail a  '
        +' left outer join STGoodsOutInMaster b on b.ID=a.MasterID   ' //库存变动表 （入货仓库）
        //
        +' UNION ALL '
        +' select b.recordstate,b.date ,b.ClientID as warehouseID,GoodsID,PackunitID,'
        +' GoalUnitID, Isnull(ModeDC,1)*Isnull(ModeC,1)*Quantity*(-1) as Quantity,   '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*GoalQuantity*(-1) AS GoalQuantity,     '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*Amount*(-1) AS Amount from  STGoodsCountOffDetail a   '
        +' left outer join STGoodsCountOffMaster b on b.ID=a.MasterID   '
        +' where b.BillMode<>'+Quotedstr('库存盘盈') //库存盘点表 (盘亏\生产损耗仓库)
        +' UNION ALL '
        +' select b.recordstate,b.date ,warehouseID,GoodsID,PackunitID,'
        +' GoalUnitID, Isnull(ModeDC,1)*Isnull(ModeC,1)*Quantity as Quantity,   '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*GoalQuantity AS GoalQuantity,      '
        +' Isnull(ModeDC,1)*Isnull(ModeC,1)*Amount AS Amount from  STGoodsCountOffDetail a  '
        +' left outer join STGoodsCountOffMaster b on b.ID=a.MasterID   '
        +' where b.BillMode='+Quotedstr('库存盘盈')  //库存盘点表 （盘盈入货仓库）
        +'   ) as b '
        +' left outer join  DAGoods   e   on e.id=b.GoodsID       '
        +' left outer join  MSunit   f   on f.id=b.PackUnitID     '
        +' left outer join  MSunit   g   on g.id=b.GoalUnitID     '
        +' left outer join  STWarehouse w   on w.id=b.WarehouseID '
        +' WHERE b.DATE<='+Quotedstr(datestr)+' and b.recordstate<>'+Quotedstr('删除')
        +' group by  w.name , E.name ,f.name ,g.name  ');
end;

procedure TBaseVoucherEditAForm.ReceiptPayableExecute(Sender: TObject);
begin
  inherited;
//
end;

procedure TBaseVoucherEditAForm.EmployeeLendExecute(Sender: TObject);
begin
  inherited;
  ShowQueryForm(EmployeeLend.Caption,EmployeeLend.Hint, 'select b.name '
      +' as [职员姓名], '
      +' Sum(Isnull(a.AmountD,0))-sum(Isnull(a.AmountC,0)) as [其它应收余额]'
      +' from (                                                '
      +' select Code,date,ClientID, BillMode,0.00 as  AmountD,'
      +' AmountD+AmountRed as  AmountC,recordstate from FnCashinmaster'
      +' UNION ALL '
      +' select Code,date,ClientID, BillMode,AmountC+AmountRed  as'
      +' AmountD, 0.00 as  AmountC,recordstate from FnCashOutmaster'
      +' UNION ALL  '
      +' select Code,date,ClientID, BillMode,0.00  as  AmountD, '
      +' AmountRed as  AmountC,recordstate from FnExpensemaster ) as a '
      +' left outer join MsEmployee b on b.ID=A.ClientID  '
      +' where a.Recordstate<>'+QUOTEDSTR('删除')
      +' Group by a.ClientID, b.name' );
end;

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

procedure TBaseVoucherEditAForm.StockConsignExecute(Sender: TObject);
begin
  inherited;
  ShowQueryForm(StockConsign.Caption,StockConsign.Hint, ' select Client as [客户名称] , '
        +' Goods  as [库存商品名称],PackUnit as [包装单位], '
        +' sum(isnull(Quantity,0)) as [库存数量],                          '
        +' GoalUnit as  [标准单位],                         '
        +' sum(isnull(GoalQuantity,0))  as [库存标准数量],                 '
        +' Employee as [经手人]                             '
        +' from  (                                          '
        +' select c.name as Client ,                        '
        +' E.name as Goods,f.name as Packunit,              '
        +' b.Quantity*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1)*(-1) as Quantity,    '
        +' g.name as  GoalUnit,                             '
        +' b.GoalQuantity*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1)*(-1) as GoalQuantity, '
        +' d.name as Employee                                    '
        +' from SLSaleClientDetail  b                            '
        +' left outer join SLSaleClientMaster a on  a.id=b.masterID '
        +' left outer join  DAClient c     on c. ID=a.ClientID      '
        +' left outer join  MSEmployee  d  on d.id=a.EmployeeID     '
        +' left outer join  DAGoods   e   on e.id=b.GoodsID         '
        +' left outer join  MSunit   f   on f.id=b.PackUnitID       '
        +' left outer join  MSunit   g   on g.id=b.GoalUnitID       '
        +' WHERE  A.RECORDSTATE<>'+Quotedstr('删除')+' and Quantity<>0'
        +' UNION ALL                                                    '
        +' select c.name as Client ,                                '
        +' E.name as Goods,f.name as Packunit,                      '
        +' b.Quantity*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1) as Quantity,                 '
        +' g.name as  GoalUnit,                                     '
        +' b.GoalQuantity*Isnull(a.ModeDC,1)*Isnull(a.ModeC,1) as GoalQuantity,         '
        +' d.name as Employee                                        '
        +' from SLGoodsOutDetail  b                                  '
        +' left outer join SLGoodsOutMaster a on  a.id=b.masterID    '
        +' left outer join  DAClient c     on c. ID=a.ClientID       '
        +' left outer join  MSEmployee  d  on d.id=a.EmployeeID      '
        +' left outer join  DAGoods   e   on e.id=b.GoodsID          '
        +' left outer join  MSunit   f   on f.id=b.PackUnitID        '
        +' left outer join  MSunit   g   on g.id=b.GoalUnitID        '
        +' WHERE  A.RECORDSTATE<>'+Quotedstr('删除') +' and Quantity<>0'
        +' and a.ClientID in ( select distinct ClientID from '
        +' SLSaleClientMaster WHERE  RECORDSTATE<>'+Quotedstr('删除')
        +' )  ) as a group by Client,Goods,PackUnit,GoalUnit,Employee ' );
end;

procedure TBaseVoucherEditAForm.UpdateDBGrid;
var  I: Integer;
begin
  with DBGrid do
  begin
    FooterRowCount := 0;
    Columns[0].Footer.ValueType := fvtStaticText;
    Columns[0].Footer.Value := '合计:';
    Columns[0].Footer.Alignment := taCenter;
    Columns[0].Title.Alignment:= taCenter;
    for I := 1 to Columns.Count - 1 do
    begin
      Columns[i].Width :=90;
      if Pos('日',Columns[I].FieldName)>0 then Columns[i].Width :=70;
      if Pos('编',Columns[I].FieldName)>0 then Columns[i].Width :=70;
      Columns[i].Title.Alignment:= taCenter;
      if Columns[I].Field is TNumericField then
      if Pos('Price',Columns[I].FieldName)<=0 then
        Columns[I].Footer.ValueType := fvtSum;
    end;
    FooterRowCount := 1;
  end;
end;

procedure TBaseVoucherEditAForm.FormShow(Sender: TObject);
begin
  inherited;
//  UpdateDBGrid;
end;

procedure TBaseVoucherEditAForm.YDPurchasePlanExecute(Sender: TObject);
begin
  inherited;
  ShowQueryForm(YDPurchasePlan.Caption,YDPurchasePlan.Hint,
    ' select  '
   +' b.CODE [采购计划编号] ,b.date [编制日期], '
   +' b.ClearDate [到货日期],   '
   +' f.name [商品名称],        '
   +' d.Name [单位名称],        '
   +' a.PriceBase [计划单价],   '
   +' a.GoalQuantity [数量],    '
   +' a.Amount [成本金额],      '
   +' a.Memo   [生产计划编号],  '
   +' b.memo [备注],            '
   +' b.Recordstate [采购状态]  '
   +' from YDPurchasePlanDetail a  '
   +' left outer join  YDPurchasePlanMaster b on a.MasterID=b.ID '
   +' left outer join  MSEmployee c on b.EmployeeID =c.ID        '
   +' left outer join  MSUnit d on a.GoalUnitID=d.ID             '
   +' left outer join  MSEmployee e on b.ClientID =e.ID          '
   +' left outer join  DAGoods f on a.GoodsID =f.ID ' );

end;

end.