uncdm.pas

本产品是开发LISTVIEW相关功能的可定制产品

{**********************************************************************
                 显示及打印定制需要的数据库操作
代码名称:   显示及打印定制需要的数据库操作
编译工具：  Delphi 6.0，适用于SQL SERVER数据库
作者：      邓普德
版权：      成都四方信息技术有限公司
定义时间：  2006-08-02
修改时间：  2006-08-09
//添加数据库连接断开后的自动重连接功能
**********************************************************************}
unit unCDM;

interface

uses
  SysUtils, Classes, DB, DBTables,Forms,ComCtrls,Grids,StdCtrls,QrPrntr,
  ExtCtrls,Controls,DateUtils;
type
  TCDM = class(TDataModule)
    dbData: TDatabase;
    sqlExecute: TQuery;
    Session1: TSession;
    Query: TQuery;
    LogDayTimer: TTimer;
    QueryTime: TQuery;
    procedure DataModuleCreate(Sender: TObject);
    procedure LogDayTimerTimer(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);
  private
    { Private declarations }
    FIndex: Integer;
    bNoticeDestroy:Boolean;//是否要退出系统

  public
    function ConnectDataBase(const DatabaseName, ServerName, UserName,
      Password: string):Boolean;//连接数据库系统
    procedure GetShowColor;
    function SelSQL(Str: String): TQuery;
    procedure doNewPriview(const sCustomshow, sFormCaption, sBname,
      UserName, sPringType: string; cClass: TForm; flist: Tlist;
      flistview: TlistView; sgNormal: TStringGrid; mMemo: TMemo);
    function GetReportName(sFromCaption: String): String;
    function IsStaffCustomShow(fStaffID: String): Boolean;
    function GetDBTime: TDateTime;

    { Public declarations }
  end;

var
  CDM: TCDM;

implementation
uses unCDefine,unMakeReportDetail;

{$R *.dfm}

procedure TCDM.DataModuleCreate(Sender: TObject);
begin
  FIndex := 0;
  bNoticeDestroy:=False;
  CMemo:=nil;
  CreateLog; //调用去本地可执行文件所在目录，并如果不存在日志目录则创建
end;

//建立与数据库的连接
Function TCDM.ConnectDataBase(const DatabaseName,ServerName,UserName,Password:string):Boolean;
begin
   try
   begin
      Result:=False;
      dbData.Params.Clear;
      dbData.Params.Values['SERVER NAME'] :=ServerName;
      dbData.Params.Values['DATABASE NAME'] :=DatabaseName;
      dbData.Params.Values['USER NAME'] :=UserName;
      dbData.Params.Values['PASSWORD'] :=Password;
      dbData.Connected:=True;
      if dbData.Connected=True then
         Result:=True
      else
         WriteLog(CMemo,'数据库重连失败(TCDM.GetDBTime)，系统将每30秒钟自动进行连接1次，直到连接成功为止');
   end;
   except
      WriteLog(CMemo,'数据库重连异常(TCDM.GetDBTime)，请检查数据库连接参数输入正常');
      LogDayTimer.Enabled:=False;
   end;
end;

procedure TCDM.GetShowColor;
begin
    if(sUserID<>'')then
    begin
       sqlExecute.Close;
       sqlExecute.Sql.Clear;
       sqlExecute.sql.Add('SELECT COLORCASE,BPLAYSOUND,BCUSTOMLOGIN,WAITNUM FROM SFRJSHOWCOLOR WHERE  STAFFID='''+sUserID+'''');
       Try
           sqlExecute.Open;
       except
           sqlExecute.Close;
       end;
       sqlExecute.First;
       if not sqlExecute.Eof then
       begin
           ColorCase:=sqlExecute.FieldByName('COLORCASE').AsInteger;
           bPlaySound:=sqlExecute.FieldByName('BPLAYSOUND').AsInteger;
           bCustomLogin:=sqlExecute.FieldByName('BCUSTOMLOGIN').AsInteger;
           nWaitNum:=sqlExecute.FieldByName('WAITNUM').AsInteger;
       end;
       sqlExecute.Close;
    end;
end;

function TCDM.SelSQL(Str: String): TQuery;
var
  i:integer ;
  str1 : string ;
begin
  result := nil;
  result := TQuery.Create(nil);
  result.DatabaseName := dbData.DatabaseName;
  result.SessionName := dbData.SessionName;
  //为防止sql语句过长，将sql语句拆分
  if length(str)>1000 then
  begin
    while Pos('or ',str)<>0 do
    begin
      if Pos('or ',str)=pos('order',str)then break
      else begin
        i:=Pos('or ',str) ;
        str1 := copy(str,1,i+2);
        result.sql.Add(str1);
        str := copy(str,i+3,length(str)) ;
      end;
    end;
    result.SQL.Add(str);
  end else
  result.SQL.Add(str) ;
  try
    result.open ;
  except
    try
      dbData.Close ;
      dbData.Open ;
    except
      ;
    end;
    result.close;
    result.sql.Clear;
  end;
end;

//通过窗体名称取报表标题
function TCDM.GetReportName(sFromCaption: String): String;
var
  strsql : string;
  Temquery : Tquery ;
begin
  Result:='';
  strsql := 'select REPORTNAME from SFRJREPORTNAME where FORMCAPTION='''+sFromCaption+'''';
  Temquery := SelSql(strsql);
  try
    Temquery.First ;
  except
    ;
  end;
  if Temquery.RecordCount>0 then
     Result:=Temquery.fieldbyname('REPORTNAME').asstring;
end;
//sCustomshow:是否需要定制打印
//sFormCaption:窗体名称
//sBname:
//UserName:登录用户名称
//cClass:所在窗体
//bAllPreviewNew:是否打印统计明细
//flist:所在窗体Listview
//sgNormal:所在窗体Tstringlist
//mMemo:所在窗体备注信息
procedure TCDM.doNewPriview(Const sCustomshow,sFormCaption,sBname,UserName,sPringType:string;
   cClass:TForm;flist:Tlist;flistview:TlistView;sgNormal: TStringGrid;mMemo:TMemo);
var sReprotName:string;
begin
  begin
    //自定义报表打印
    sReprotName:=CDm.GetReportName(sFormCaption);
    MakeReportDetail:=TMakeReportDetail.Create;
    if sPringType='DoAllPreviewNew' then
    begin
      if sCustomshow='1' then
        MakeReportDetail.DoAllPreviewNew(cClass,flist,sReprotName,
          '','',sFormCaption,sBname,sReprotName,UserName,
          Date(),TQRPaperSize(9),mMemo,sgNormal)
      else
        MakeReportDetail.DoAllPreview(cClass,flistview,sReprotName,
          '','',sFormCaption,sBname,sReprotName,UserName,
          Date(),TQRPaperSize(9),mMemo,sgNormal);
    end
    else if sPringType='DoOnlyTotalPreview' then
      MakeReportDetail.DoOnlyTotalPreview(cClass,sReprotName,
        sFormCaption,sBname,sReprotName,UserName,
         DATE(),TQRPaperSize(9),sgNormal)
    else
    begin
       if sCustomshow='1' then
          MakeReportDetail.DoOnlyDetailPreviewNew(cClass,flist,sReprotName,
          '','',sFormCaption,sBname,sReprotName,UserName,
          Date(),TQRPaperSize(9),mMemo)
       else
          MakeReportDetail.DoOnlyDetailPreview(cClass,flistview,sReprotName,
          '','',sFormCaption,sBname,sReprotName,UserName,
          Date(),TQRPaperSize(9),mMemo);
    end;
    MakeReportDetail.Free;
  end;
end;

//判断指定工号员工是否有定制显示要求
function TCDM.IsStaffCustomShow(fStaffID: String): Boolean;
var
  SQL:String;
begin
  Result := false;
  SQL:='SELECT BCUSTOMSHOW FROM SFRJSHOWCOLOR WHERE STAFFID='''+fStaffID+'''';
  try
    if Query.Active then
      Query.close;
    Query.Sql.Clear;
    Query.Sql.Add(SQL);
    Query.Open;
    if NOT Query.Eof then
    begin
      Query.First;
      Result := (Query.FieldByName('BCUSTOMSHOW').AsInteger = 1);
    end;
    Query.close;
  except
  end;
end;

//获取数据库服务器系统时间，，如果发现连接断开，则需要在5秒钟后进行自动重连
function TCDM.GetDBTime: TDateTime;
var
  sql: string;
  i:integer;
begin
  sql := 'SELECT GETDATE() As SELF_DATE';
  try
    if QueryTime.Active then
      QueryTime.close;
    QueryTime.Sql.Clear;
    QueryTime.Sql.Add(sql);
    QueryTime.Open;
    Result := QueryTime.FieldByName('SELF_DATE').AsDateTime;
    LogDayTimer.Enabled:=False; //如果发生数据库连接正常，则需要将定时器触发时间调大
    LogDayTimer.Interval:=120000;
    LogDayTimer.Enabled:=True;

  except
    WriteLog(CMemo,'从数据库取时间失败(TCDM.GetDBTime)，重新连接');
    LogDayTimer.Enabled:=False; //如果发生数据库连接失败，则为进行及时的数据库连接，将数据库连接时间调小
    LogDayTimer.Interval:=25000;
    LogDayTimer.Enabled:=True;
    Result := Now();
    dbData.Connected := False;
    Application.ProcessMessages;
    i:=0;
    while(i<100) do
    begin
        if bNoticeDestroy=True then exit;
        i:=i+1;
        Application.ProcessMessages;
        sleep(50);
    end;
    try
       dbData.Connected := True;
       if dbData.Connected = False then
          WriteLog(CMemo,'数据库重连失败(TCDM.GetDBTime)');
    except
       WriteLog(CMemo,'数据库重连发生错误(TCDM.GetDBTime)');
    end;
    Exit;
  end;    
end;

procedure TCDM.LogDayTimerTimer(Sender: TObject);
begin
  MaintenanceLogDay;//调用自动维护最近30天的日志函数
  GetDBTime;//通过取数据库服务器时间检查数据库连接是否正常，若不正常则需要进行自动连接
end;

procedure TCDM.DataModuleDestroy(Sender: TObject);
begin
    try
       bNoticeDestroy:=True;
       LogDayTimer.Enabled:=False;
       dbData.Connected := False;
       WriteLog(CMemo,'退出数据库连接(TCDM.DataModuleDestroy)');
    except
       WriteLog(CMemo,'退出数据库连接(TCDM.DataModuleDestroy)');
    end;
end;

end.