u_jsmx.pas

材料租赁

unit U_jsmx;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, StdCtrls, Buttons, ComCtrls, FR_Dock, Grids, DBGridEh,
  DB, ADODB, //RM_class, RMD_RptExp, RMD_ADO, RMD_BDE, RM_Desgn,RM_dbset, RM_dset,
  PrnDbgeh;

type
  TfrmJsmx = class(TForm)
    Panel1: TPanel;
    frTBPanel1: TfrTBPanel;
    Shape1: TShape;
    dtEnd: TDateTimePicker;
    dtStar: TDateTimePicker;
    frTBPanel2: TfrTBPanel;
    btnClose: TSpeedButton;
    btnQuery: TSpeedButton;
    btnPrint: TSpeedButton;
    frTBPanel3: TfrTBPanel;
    Label3: TLabel;
    cmbBm: TComboBox;
    StatusBar1: TStatusBar;
    DBGridEh1: TDBGridEh;
    adofc: TADOQuery;
    adosh: TADOQuery;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    qyado: TADOQuery;
    pShwomessage: TPanel;
    PrintDBGridEh1: TPrintDBGridEh;
    ADOUpdate: TADOQuery;
    Label1: TLabel;
    cmbCl: TComboBox;
    Panel2: TPanel;
    chkYq: TCheckBox;
   //Table1Reportdata: TBlobField;
    procedure FormShow(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnQueryClick(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
    procedure btnPrintClick(Sender: TObject);
    procedure DBGridEh1ColWidthsChanged(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
  private
  procedure DrowData(const TableName:string;ado:TADOQuery); //显示数据
  procedure makedata(ADO:TADOQuery);
  procedure ExecSQL(const sSql,para1,para2:string;ADO:TADOQuery;Excute:boolean);  //执行SQL语句
//  procedure submitdata(const tablename:string;ado:TADOQuery;dtstar:TdateTime);
    { Private declarations }
  public
  FilterControlList: TStringList;
    { Public declarations }
  end;

var
  frmJsmx: TfrmJsmx;
  sSql,s_value:string;
  const IndicatorWidth=11;
implementation

 USES MAIN,U_DM,u_public,common,utLeasehold,u_PrintSelect,repJsmx ;

{$R *.dfm}
function tzje(const spbh,bmbh:string;ado:TADOQuery;dtstar,dtEnd:TDateTime):double;
begin
sSql:='SELECT money, bmbh, spbh,sdate FROM vW_cl_tz';
sSql:=sSql+' Where bmbh='+Quotedstr(bmbh)+' and spbh='+QuotedStr(spbh);
sSql:=sSql+' and (sDate>=:@dtstar and sDate<=:@dtEnd)';
ado.Close ;
ado.SQL.Clear ;
ado.SQL.Add(sSql);
ado.Parameters.ParamByName('@dtStar').Value :=datetimetostr(dtstar);
ado.Parameters.ParamByName('@dtEnd').Value :=datetimetostr(dtEnd);
ado.Prepared :=true;
ado.Open ;
ado.First ;
if ado.RecordCount >0 then
 result:=ado.Fields[0].AsFloat
else
 result:=0;
end;
{procedure sumjs(const TableName:string;ado,qytmp:TADOQuery;dtstar,dtEnd:TDateTime); //求结数
var
  day:integer;
  dtpr,dtnext:TDatetime;//定义日期变量
  Fmoney,
  Fjmoney,
  Nmoney,
  Njmoney,
  Emoney,
  Ejmoney,
  Total:double;
begin


end; }
 //求上期数
procedure submitdata(const tablename,tablename1:string;ado:TADOQuery;dtstar:TdateTime);
var
DsubJsje:double;
begin
sSql:='SELECT JSJE FROM  '+TableName+' WHERE Sdate<:@sdate ORDER BY Sdate  Desc' ;
with ado do
  begin
  close;
  sql.Clear ;
  sql.Add(sSql);
  Parameters.ParamByName('@sdate').Value :=dtstar;
  Prepared;
  Open;
  DsubJsje:=FieldByName('jsje').AsFloat ;
  close;
  //插入上期数据
  sSql:='INSERT INTO '+ TableName1+'(SPMC,jsje)';
  sSql:=sSql+' VALUES(:SPMC,:JSJE)';
  sql.Clear ;
  sql.Add(sSql);
  Parameters.ParamByName('JSJE').Value :=DsubJsje;
  Parameters.ParamByName('SPMC').Value :='上期结存数：';
  Prepared;
  ExecSQL ;
  close;
end;

end;
//求预期值
procedure prognosticate(const TableName:string;ado:TADOQuery;tMoney:double;dtEnd:TDateTime);
begin
sSql:='INSERT INTO '+ TableName+'(SPMC,jsje,sdate)';
sSql:=sSql+' VALUES(:SPMC,:JSJE,:sdate)';
with ado do
  begin
  close;
  sql.Clear ;
  sql.Add(sSql);
  Parameters.ParamByName('JSJE').Value :=tMoney;
  Parameters.ParamByName('sdate').Value :=dtEnd;
  Parameters.ParamByName('SPMC').Value :='预测金额：';
  Prepared;
  ExecSQL ;
  close;
  end;
end;
//求积数
procedure cacalfcmass(const TableName:string;ado,qytmp:TADOQuery;dtstar,dtEnd:TDateTime;tzje:double); //求积数
var
  day,n:integer;
  dtpr,dtnext:TDatetime;//定义日期变量
//  dj,
  Fsl,
  Nsl,
  Fmoney,
  Nmoney,
  jmoney,
  Dtotal,
  DJsje,
  Njsje,
  tmp,
  qCje,
  dCe,
  FJmoney:Double;
  fSfno,nSfno:string;
  iRecount:integer;
  blnYnqc,nYnqc,nYntz,ynsf :boolean;
begin
  day:=0;
  sSql:='SELECT bmbh, spbh, ynsf, sdate, fcsl, hssl, money, jsje, jMoney, ynqc, spmc, ggxh, jldw,spdj,zlts,SL,ce';
  sSql:=sSql+'  FROM ' +  TableName +' where money <>0 order by sdate';    //从表中查出所需要的数据
  with qytmp do
  begin
    close;
    sql.Clear ;
    sql.Add(sSql);
    Prepared ;
    open;
    first;
    iRecount:= RecordCount;
    if RecordCount >0 then
    begin
      fSfno:=trim(FieldByName('sDATE').value);
       dtpr:=FieldByName('sdate').AsDateTime ;
       Fsl:=FieldByName('jsje').AsFloat ;
       FMoney:=FieldByName('jsje').AsFloat ;
       FjMoney:=FieldByName('money').AsFloat ;
       NMoney:=FieldByName('jmoney').AsFloat;
       dtnext :=dtpr;
       blnYnqc:=FieldByName('ynqc').AsBoolean ;
       Djsje:=FieldByName('jsje').AsFloat ;
       ynsf:=FieldByName('ynsf').AsBoolean ;
   if not blnYnqc then
       MoveBy(0)
   else
      MoveBy(1);
    while not Eof do
    begin
       n:=qytmp.RecNo ;
       nSfno:=trim(FieldByName('sdate').value);
       dtnext:=FieldByName('sdate').AsDateTime ;
       NMoney:=FieldByName('jmoney').AsFloat;
       jMoney:=FieldByName('money').AsFloat ;
       nYnqc:=FieldByName('ynqc').AsBoolean ;
       njsje:=FieldByName('jsje').AsFloat ;
       ynsf:=FieldByName('ynsf').AsBoolean ;
       dCe:=FieldByName('ce').AsFloat ;
       day:=Trunc(dtnext)-Trunc(dtpr);


   if blnYnqc then
   begin
     Fsl:=Fsl+FjMoney*DAY;
     blnYnqc:=false;
   end;

     if day=1 then Dtotal:=Nmoney*day ;
     if day>1 then Dtotal:=tmp;//+nMoney;
     if DAY=0 then Dtotal:=Fmoney ;
     if not nYnqc then
      begin
      if day =1 then Dtotal:=FMoney+Dtotal;
      if day>1 then
          if not ynsf then
             Dtotal:=fsl+Dtotal*day+jmoney
          else
            Dtotal:=fsl+Dtotal*day-jmoney+dce;
      if day=0 then
        begin
          if not ynsf then
            dtotal:=Dtotal+jmoney
          else
            dtotal:=Dtotal-jmoney ;
        end;
       end  
     else
       begin
          Dtotal:=NMoney+Djsje;
          qCje:= Djsje;
       end;
   if iRecount=n then
      Dtotal:=Dtotal+tzje;
       //更新金额
       sSql:='UPDATE '+ TableName+' SET jsje =:@money  WHERE SDATE =:sfno AND YNSF=:YNSF';
      with ado do
       begin
         close;
         sql.Clear ;
         sql.Add(sSql);
         Parameters.ParamByName('@money').Value :=Dtotal;
         Parameters.ParamByName('sfno').Value :=nSfno;
         Parameters.ParamByName('YNSF').Value :=YNSF;
         Prepared;
         ExecSQL ;
       end;
       fSfno:=nSfno;
       dtpr:=dtnext;
       Fmoney:=Dtotal;
       blnYnqc:=nYnqc;
        tmp:=nmoney;
      fsl:=Dtotal;
    next;
    end;
   end;
  end;

  if iRecount=1  then
  NMoney:=FMoney;   //第一条记录并且是期初数
  if (iRecount=1) and (blnYnqc) then
  begin
  NMoney:=FjMoney;   //第一条记录并且是结存数
  Dtotal:=FMoney;   //第一条记录并且是期初数
  end;
  day:=Trunc(dtend)-Trunc(dtnext);
  if day>0 then
  begin
  Dtotal:=Dtotal+NMoney*DAY ;
  prognosticate(TableName,ado,Dtotal,dtend);
  end;
end;
//
procedure Tfrmjsmx.ExecSQL(const sSql,para1,para2:string;ADO:TADOQuery;Excute:boolean);  //执行SQL语句
begin
 with  ado do
 begin
 close;
 sql.clear;
 sql.add(sSql);
 Parameters.ParamByName(para1).Value :=dtStar.DateTime ;
 Parameters.ParamByName(para2).Value :=dtEnd.DateTime ;
 Prepared;
 if not Excute then
  Open
 else
  ExecSQL; 
 end;
end;
//合并数据
procedure Tfrmjsmx.makedata(ADO:TADOQuery);
var
  sfno:string;
  i,j,x:integer;
begin
//生成临时表FCSH
{SELECT dbo.JSMXFC.sfno, dbo.JSMXFC.sdate, dbo.JSMXFC.spdj, dbo.JSMXFC.sl,
      dbo.JSMXFC.zlts, dbo.JSMXFC.money, dbo.JSMXFC.spmc, dbo.JSMXFC.jldw,
      dbo.JSMXSH.sdate AS ssdate, dbo.JSMXSH.spdj AS sspdj, dbo.JSMXSH.sl AS ssl,
      dbo.JSMXSH.zlts AS szlts, dbo.JSMXSH.money AS smoney,
      dbo.JSMXSH.spmc AS sspmc, dbo.JSMXSH.jldw AS sjldw
FROM dbo.JSMXFC LEFT OUTER JOIN   dbo.JSMXSH ON dbo.JSMXFC.sfno = dbo.JSMXSH.sfno}
if CheckTable('fcsh',ADO) then
   repairTable('fcsh',ADO) ;
sSql:='SELECT JSMXFC.sfno, JSMXFC.sdate, JSMXFC.spdj, JSMXFC.sl,';
sSql:=sSql+'JSMXFC.zlts, JSMXFC.jsje, JSMXFC.spmc, JSMXFC.jldw,';
sSql:=sSql+'JSMXSH.sdate AS ssdate, JSMXSH.spdj AS sspdj, JSMXSH.sl AS ssl,';
sSql:=sSql+'JSMXSH.zlts AS szlts, JSMXSH.money AS sjsje,JSMXSH.spmc AS sspmc, JSMXSH.jldw AS sjldw';
sSql:=sSql+' INTO fcsh FROM JSMXFC LEFT OUTER JOIN JSMXSH ON JSMXFC.sfno = JSMXSH.sfno';
sSql:=sSql+' WHERE JSMXFC.sdate BETWEEN :DTSTART AND :DTEND ORDER BY JSMXFC.sdate';    //从表中查出所需要的数据
ExecSQL(sSql,'DTSTART','DTEND',ADO,true);
sSql:='UPDATE FCSH SET SSDATE=NULL,sspdj=null,ssl=null,sjsje=null,sjldw=null,sspmc=null,szlts=null';
ExecuteSQL(sSql,ADO,true);
sSql:='SELECT * FROM JSMXFC ';
sSql:=sSql+' WHERE SDATE BETWEEN :DTSTART AND :DTEND order by sdate';    //从表中查出所需要的数据
ExecSQL(sSql,'DTSTART','DTEND',frmmain.adoqytmp ,false);
sSql:='SELECT * FROM JSMXSH ';