dbconn.~pas

采用Delphi7+Oracle. 该软件是由交警人员对各个路口闯红灯违法照片进行有效保存

unit dbConn;

interface

uses inifiles,adodb,Windows,SysUtils,Classes,Graphics,Forms,Controls,Menus,
      StdCtrls,Dialogs,Buttons,Messages,ExtCtrls,ComCtrls,StdActns,
      ActnList,ToolWin,ImgList,DBGrids,Grids,TeEngine,StrUtils,Math,ComObj,Registry,db,Jpeg;

var cn_public:TADOConnection;     //通用数据库连接
    cn_photo:TADOConnection;      //图片数据库专用
        //存储过程  （操作日志的）
    rs:TADODataset;
    rsPhoto:TADODataset;  //专门为照片显示声明的
    qry:TADOQuery;

    var
   FirstLogin:boolean;   //标记是否为第一次登陆
   CurrentUser:String;   //当前操作员
   CurrentUserID:String;  //当前操作员ID
   CurrentByOperator:String; //当前被操作的用户
   CurrentByOperatorID:String; //当前被操作的用户ID
   CurrentOperateItem:String;  //当前操作的项目
   CurDir:string;
var  //---->>>有关文件命名将要使用到的信息
  fileModeStart,fileModeCount : Integer;        //设备
  fileEquipmentStart,fileEquipmentCount:Integer; //机器编号
  fileYearStart,fileYearCount:Integer;  //year
  fileMonthStart,fileMonthCount:Integer; //month
  fileDayStart,fileDayCount:Integer;     //day
  fileHourStart,fileHourCount:Integer;   //hour
  fileMinuteStart,fileMinuteCount:integer; //minute
  fileSecondStart,fileSecondCount:Integer; //second
  fileRedLightStart,fileRedLightCount:Integer; //redLight
  fileRunDirStart,fileRunDirCount:Integer; //行驶方向
  filePicSeqStart,filePicSeqCount:Integer; //图片序号
  fileMemoBitStart,fileMemoBitCount:Integer; //备注位
  fileSpeedStart,fileSpeedCount:Integer;   //速度，也可不设置

type
  PICINFO=record
    zpsj: TDateTime;   //抓拍时间
    redLater:string;      //红灯过后多少时间（秒）
    serialNo:string;      //设备编号
    EquipID:string;       //机器编号
    zpSerial: string;      //照片序列：01,02,03
    LoopNo: string;      //线圈编号
    onlyID: string;      //唯一记录标记 （由：sbbh+jqbh+zpsj+hdgh+xqbh 组成）
    wholename: string;   //文件的全部名称
end;
   
function ConnInitialize():boolean;    //初始化工作，连接Oracle数据库
function Execute_SQL(sql:String):Boolean;  //专门执行SQL语句的
Procedure LoadCombobox(cbo : TCombobox;const dmlb : string);  //加载数据到combobox
procedure loadCombox(cbo:TCombobox;sql:String;bz:boolean);    //加载数据到combobox
procedure loadComboxEquip(cbo:TCombobox;sql:String;format:Integer);  //加载数据到combobox:机器
function getmc(Txt : String) : String;  //得到名称
function Getdm(Txt : String) : String;  //得到代码
procedure DisplayCombox(Txt: String; Comb:TComboBox;BZ : String);   //用于combobox显示数据
procedure SaveOperateLog(spLog:TADOStoredProc;userid:String;username:String;operatedate:TDateTime;operateItem:String); //保存操作日志
function GetPicInfo(fn:String):PICINFO;  //获得图片文件信息（根据实际设定的情况而进行得到照片信息）
function formatstr(Strtime: string):string;    //格式化时间字符串
function SavePicInfoToDB(pic:PICINFO):string;   //将图片信息保存到数据库中p_picinfo
function SavePicPhotoToDB(fullFilePath :String;PhotoXH : String): boolean;  //图片保存到数据库
function DisplayPicPhoto(onlyID:String;photoXH:String;img1,img2,img3:TImage;Delete:String):String;  //将显示照片
function CompareSaveList(sql:String):Boolean;   //验证是否为保护名单车辆
function DeletePhotoFromDB(onlyID:String;Serial:String;img1,img2,img3:TImage):String;  //删除照片操作
function DeleteAllPhoto(selSql:String):String;  //根据selSql将符合要求的图片全部删除
function ExportPhotoFromDB(onlyID:String;selSql:String;savePath:String):boolean;  //将照片导出

implementation

uses MyThread, PlateCompareDelete;

function ConnInitialize():boolean;
begin
    try
      cn_public:=TADOConnection.Create(nil);  //通用数据库连接
      cn_public.CursorLocation :=clUseClient;
      cn_public.ConnectionString :='Provider=MSDAORA.1;Password=photocheck;User ID=photocheck;Data Source=photocheck;Persist Security Info=True';
      cn_public.LoginPrompt := false;
      cn_public.Open;

      cn_photo:=TADOConnection.Create(nil);    //图片数据库专用
      cn_photo.CursorLocation :=clUseClient;
      cn_photo.ConnectionString :='Provider=OraOLEDB.Oracle.1;Password=photocheck;User ID=photocheck;Data Source=photocheck;Persist Security Info=True;Extended Properties=""';
      cn_photo.LoginPrompt := false;
      cn_photo.Open;
    except
      ShowMessage('打开数据库失败!');
      application.Terminate;
    end;

end;
function GetPicInfo(fn:String):PICINFO;  //获得图片文件信息
var
  picInformation:PICINFO; //结构体
  sjCount:Integer;   //时间个数
  onlyCount:integer;   //作为唯一标识的
begin
       try
              sjCount:=fileYearCount+ fileMonthCount+fileDayCount+ fileHourCount+fileMinuteCount+ fileSecondCount;
              onlyCount:= fileModeCount+fileEquipmentCount+fileYearCount+ fileMonthCount+fileDayCount+ fileHourCount+fileMinuteCount+ fileSecondCount+fileRedLightCount;
              //其实没什么具体的意义 ，这么做
              picInformation.zpsj := strtodatetime(formatstr(copy(fn,fileYearStart,sjCount)));
              picInformation.redLater := copy(fn,fileRedLightStart,fileRedLightCount);  //红灯过后时间
              picInformation.serialNo := copy(fn,fileModeStart,fileModeCount);          //设备编号
              picInformation.EquipID := copy(fn,fileEquipmentStart,fileEquipmentCount);  //机器编号
              picInformation.zpSerial := copy(fn,filePicSeqStart,filePicSeqCount);       //照片序列
              picInformation.LoopNo := copy(fn,fileMemoBitStart,fileMemoBitCount);       //线圈编号（备注位）
              picInformation.onlyID := copy(fn,fileModeStart,onlyCount)+copy(fn,fileMemoBitStart,fileMemoBitCount);   //作为区分
              picInformation.wholename := fn;   //保存全部名称
            //picInformation.zpsj := strtodatetime(formatstr(copy(fn,5,14)));  //抓拍时间
            //picInformation.redLater := copy(fn,19,3);   //红灯过后时间
            //picInformation.serialNo := copy(fn,1,1);  //设备编号
            //picInformation.EquipID := copy(fn,2,3);   //机器编号
            //picInformation.zpSerial := copy(fn,24,2);  //照片序列
            //picInformation.LoopNo := copy(fn,26,3);    //线圈编号--->>属于备注部分
            //picInformation.onlyID := copy(fn,1,21)+copy(fn,26,3);   //作为区分
            //picInformation.wholename := fn;   //保存全部名称
                result:= picInformation;
                exit;
        except
          on exception do
                result:=picInformation;
        end;
end;
//------------------------------------------------------------------------------
function formatstr(Strtime: string):string;
var
  yy,mm,dd,hh,ff,tt: integer;
begin
//20070421082426
  yy:=strtoint(copy(strtime,1,4));
  mm:=strtoint(copy(strtime,5,2));
  dd:=strtoint(copy(strtime,7,2));
  hh:=strtoint(copy(strtime,9,2));
  ff:=strtoint(copy(strtime,11,2));
  tt:=strtoint(copy(strtime,13,20));
  result:=inttostr(yy)+'-'+inttostr(mm)+'-'+inttostr(dd)+' '+inttostr(hh)+':'+inttostr(ff)+':'+inttostr(tt);
end;
//------------------------------------------------------------------------------

function Execute_SQL(sql:String):Boolean;  //专门执行SQL语句的
begin
    try
        qry:=TADOQuery.Create(nil);
        qry.Connection := cn_public;
        qry.SQL.Clear ;
        qry.SQL.Add(sql);
        qry.ExecSQL ;
        qry.Close ;
        result:=true;
    except
        qry.Close ;
        result:=false;
        ShowMessage('操作未被成功执行');
    end;
end;
//--------------------------------------------------------------------------
Procedure LoadCombobox(cbo : TCombobox;const dmlb : string);
begin
    try
      cbo.Items.Clear;
      rs:=TADODataSet.Create(nil);
      rs.Connection := cn_public;
      rs.CommandText := 'select dmz,dmsm from photocheck.p_code where dmlb = ''' + dmlb + '''';
      rs.Open;
      rs.Recordset.MoveFirst;
      while not rs.Recordset.EOF  do
      begin
         cbo.Items.Add(rs.Recordset.Fields[0].Value+':'+rs.Recordset.Fields[1].Value);
         rs.Recordset.MoveNext;
      end;
    finally
      rs.Recordset.Close;
      rs.Free ;
    end;
end;
//--------------------------------------------------------------------------
procedure loadComboxEquip(cbo:TCombobox;sql:String;format:Integer);
begin
    try
      cbo.Items.Clear;
      rs:=TADODataSet.Create(nil);
      rs.Connection := cn_public;
      rs.CommandText := sql;
      rs.Open;
      rs.Recordset.MoveFirst;
      while not rs.Recordset.EOF  do
      begin
         //FormatFloat(format,rs.Recordset.Fields[0].Value )
         cbo.Items.Add(FormatFloat('000',rs.Recordset.Fields[0].Value)+':'+rs.Recordset.Fields[1].Value);
         rs.Recordset.MoveNext;
      end;
    finally
      rs.Recordset.Close;
      rs.Free ;
    end;
end;
//--------------------------------------------------------------------------
procedure loadCombox(cbo:TCombobox;sql:String;bz:boolean);
begin
  try
      cbo.Items.Clear;
      rs:=TADODataSet.Create(nil);
      rs.Connection := cn_public;
      rs.CommandText := sql;
      rs.Open;
      rs.Recordset.MoveFirst;
      while not rs.Recordset.EOF  do
      begin
          if bz=false then
          begin                 // rs.Recordset.Fields[0].Value
             cbo.Items.Add(IntToStr(rs.Recordset.Fields[0].Value)+':'+rs.Recordset.Fields[1].Value);
             rs.Recordset.MoveNext;
          end;
          if bz=true then
          begin
              cbo.Items.Add(rs.Recordset.Fields[0].Value);
              rs.Recordset.MoveNext ;
          end;
      end;
    finally
      rs.Recordset.Close;
      rs.Destroy ;
    end;
end;
//--------------------------------------------------------------------------
function Getdm(Txt : String) : String;
begin
    If Pos(':', Txt) - 1 > 0 Then
        result := copy(Txt, 1, Pos(':', Txt) - 1)
    Else
        result := Txt;
End;

//--------------------------------------------------------------------------
function getmc(Txt : String) : String;
begin
    If Pos(':', Txt) - 1 > 0 Then
        getmc := copy(Txt, Pos(':', Txt) + 1, length(txt) )
    Else
        getmc := Txt;
End;
//--------------------------------------------------------------------------
procedure DisplayCombox(Txt: String; Comb:TComboBox;BZ : String);
var i:Integer;
begin
try
    For i:= 0 To Comb.Items.Count - 1 do
    begin
        If BZ = '1' Then begin
            If Txt=getmc(Comb.Items.Strings[i]) Then
            begin
                Comb.Itemindex:= i;
                Exit;
            End;
        end
        Else begin
            If Txt=getdm(Comb.Items.Strings[i]) Then
            begin
                Comb.ItemIndex:=i;
                Exit;
            End;
        end;
    end;
finally
    Comb.Text:= '';
end;
End;
//--------------------------------------------------------------------------
//保存操作日志
procedure SaveOperateLog(spLog: TADOStoredProc;userid:String;username:String;operatedate:TDateTime;operateItem:String);
begin
 try
    spLog.Parameters.Clear ;
    spLog.Connection := cn_public;
    spLog.ProcedureName := 'photocheck.OPERATELOG';  //操作日志
    spLog.Parameters.CreateParameter('PUSERID',ftString,pdinput,50,'');
    spLog.Parameters.CreateParameter('PUSERNAME',ftString,pdinput,50,'');
    spLog.Parameters.CreateParameter('POPERATEDATE',ftDateTime,pdinput,0,OleVariant(0));
    spLog.Parameters.CreateParameter('POPERATEITEM',ftstring,pdinput,55,'');
    spLog.Parameters[0].Value := userid;
    spLog.Parameters[1].Value := username;
    spLog.Parameters[2].Value := operatedate;
    spLog.Parameters[3].Value := operateItem;
    spLog.ExecProc ;
 finally
    //spLog.Close ;
    //spLog.Free ;
 end;
end;
//--------------------------------------------------------------------------
function SavePicInfoToDB(pic:PICINFO):string;   //将图片信息保存到数据库中p_picinfo
var
  sql:String;
begin
      try
          sql:='';
          sql:=''''+pic.onlyID+''',';
          sql:= sql + ''''+pic.serialNo +''',';
          sql:= sql + ''''+pic.EquipID +''',';
          sql:= sql + ''''+ DateTimeToStr(pic.zpsj)+''',';
          sql:= sql + ''''+pic.redLater +''',';
          sql:= sql + ''''+pic.zpSerial +''',';
          sql:= sql + ''''+pic.LoopNo +''',';
          sql:= sql + ''''+pic.wholename +'''';
          sql:='{call photocheck.PACK_PHOTO.getPhotoXh('+sql+',{resultset 1,photoxh}}';
          qry:=TADOQuery.Create(nil);
          qry.Connection := cn_public;
          qry.SQL.Clear ;
          qry.SQL.Add(sql);
          qry.Open ;
          if not qry.EOF then
          begin
            if not qry.Fields[0].IsNull then
            begin
              if qry.Fields[0].AsInteger<>0 then
                  result:= qry.Fields[0].AsString
              Else
              begin
                MessageBox (0,'得到图片序号出错','',16);
                Exit;
              End;
            end
            else
            begin
                MessageBox (0,'执行图片信息保存出错','',16);
                Exit;
            end;
          end
          else
          begin
              MessageBox (0,'执行图片信息保存出错','',16);
              Exit;
          end;
      except
        on exception do
        result:='';
      end;
end;