unit DataModuleU;
//Download by http://www.okbase.net
interface

uses
  SysUtils, Classes, DB, ADODB, uWLFtp, Dialogs, IniFiles,Forms,Windows,
  ImgList, Controls, cxGraphics,cxGrid,cxGridExportLink, WinSkinStore,
  WinSkinData;

const
  DataBaseFileName = 'VehicleDB.db';
  DataBasePassWord = '83027110';
    
type
  TDmFrm = class(TDataModule)
    AdoCon: TADOConnection;
    ADOQuery1: TADOQuery;
    AQ: TADOQuery;
    AE: TADOQuery;
    il_MainTool: TImageList;
    il_BtnPic: TcxImageList;
    AdoQry_ChangShu: TADOQuery;
    ds_ChangShu: TDataSource;
    AQ_Log: TADOCommand;
    cxImageList1: TcxImageList;
    ADOConn: TADOConnection;
    AQAccess: TADOQuery;
    cxtmp: TADOQuery;
    sysinfo: TADOTable;
    allpic: TImageList;
    zhms: TADOQuery;
    yhxx: TADOQuery;
    sygn: TADOQuery;
    bmxx: TADOQuery;
    zgxx: TADOQuery;
    systime: TADOQuery;
    simplejczl: TADOTable;
    djgz: TADOTable;
    xlsxx: TADOQuery;
    zclb: TADOQuery;
    zcxx: TADOQuery;
    yjxx: TADOQuery;
    yzbm: TADOQuery;
    gdzccon: TADOConnection;
    cxtmp2: TADOQuery;
    wxxx: TADOQuery;
    xlsconnect: TADOConnection;
    gys: TADOQuery;
    griddisp: TADOQuery;
    Ds_zcxx: TDataSource;
    ds_wxxx: TDataSource;
    DS_yjxx: TDataSource;
    DS_fssb: TDataSource;
    DS_fspic: TDataSource;
    DS_fswj: TDataSource;
    fssb: TADOQuery;
    fspic: TADOQuery;
    fswj: TADOQuery;
    DS_gys: TDataSource;
    repcent: TADOQuery;
    DS_rep: TDataSource;
    yjxx2: TADOQuery;
    DS_yjxx2: TDataSource;
    reminder: TADOQuery;
    adoprn: TADOQuery;
    DS_zgxx: TDataSource;
    conn_num: TADOQuery;
    SkinData1: TSkinData;
    SkinStore1: TSkinStore;
    ds_SysTime: TDataSource;
    procedure DataModuleCreate(Sender: TObject);
    procedure DataModuleDestroy(Sender: TObject);
  private
    { Private declarations }
    BlackHashList: THashedStringList;
  public
    LogUserID :Integer;
    UserRuleID :Integer;
    LogUser, LogPass,UserDLMC : string;
    b_HighLimit :Boolean; ///最高权限,admin

    AllFunction: THashedStringList; ///所有的权限列表
    UserFunction: THashedStringList; ///操作员具有的权限

    MyFtp: TWLFtp;
    function OpenSql: Boolean;
    function OpenFtp: Boolean;
    function FiletoFile(FileName: string): string;

    procedure LoadAllFunction;
    procedure LoadUserFunction(RuleID: Integer);
    function HaveFunction(sFunctionID: string; bShowMSG: Boolean =
      True; m_Message: string = ''): Boolean;
    ///打开指定的adoquery必须指定使用的ADoquery控件
    function OpenAdoquery(TheAqry :TADOQuery;m_Sql:string):Boolean;overload;
    ///打开指定的adoquery不需要指定Adoquery参数,默认使用数据模块中的AQ
    function OpenAdoquery(m_Sql:string):Boolean;overload;
    ///使用指定的adoquery执行SQL语句,必须指定使用的ADoquery控件
    function ExecAdoquery(TheAqry :TADOQuery;m_Sql:string):Boolean;overload;
    ///用指定的adoquery执行Sql语句不需要指定Adoquery参数,默认使用数据模块中的AE
    function ExecAdoquery(m_Sql:string):Boolean;overload;
    procedure ClearBlackVeh;
    ///检查一下是否已经建立了黑名单表
    function CheckDBforBlackList: Boolean;
    ///验证车牌号码是否在黑名单里面
    function CheckBlackNum(m_CardNum: string): Boolean;
    ///装载所有黑名单到哈希列表中
    procedure LoadBlackNum2HashList;
    ///增加黑名单号码到号码列表
    procedure AddBlackNum(m_CardNum: string);
    ///删除黑名单号码到号码列表
    procedure DeleteBlackNum(m_CardNum: string);
    ///获取新的GUID,用于插入数据
    function GetNewGuid:string;
    procedure ReopenAdoquery(theAdoquery :TADOQuery);
    function AutoCheckCarNum(strtxt,defaulttxt:string):string;
    procedure LoadChangShu(m_SL: TStrings; m_TableName, m_FieldName
      : string; StrAll: string = '');
    procedure LoadOperator(m_SL :TStrings;StrAll :string = '');
    procedure OutputcxGrid(mycxGrid :TcxGrid);
    {*------------------------------------------------------------------------------
      工作路径
      @return 返回exe的路径,以‘\’结束的
    -------------------------------------------------------------------------------}
    function GetPath: string;
    ///记录操作日志函数,参数 操作模块,操作细节
    procedure WriteOperatLog(m_Oper_module, m_Oper_DetailInfo: string);
    { Public declarations }
    ///创建并打开Access数据库
    function CreateAndOpenDataBase: Boolean;
    procedure updateDB20111206;
    ///更新数据库增加三个字段违章代码WZDM,罚款金额FKJE;处理标识CLBZ
    ///增加两个图片字段Pic1,Pic2;FileName1,FileName2
    procedure UpdateDB20120316;
    function GetDataBaseFileName:string;  
  end;

var
  DmFrm: TDmFrm;

implementation

uses FunctionPas, LoadConFig;

{$R *.dfm}

{ TDmFrm }

function TDmFrm.OpenFtp: Boolean;
begin
  Result := False;
  try
    MyFtp := TWLFtp.Create;
    MyFtp.Host := ConfigValue.FtpServer;
    MyFtp.Port := StrToInt(ConfigValue.FtpPort);
    MyFtp.UserName := ConfigValue.FtpUser;
    MyFtp.Password := UncrypKey(ConfigValue.FtpPass);
  except
    Exit;
  end;
  if MyFtp.Connect then
    Result := True;
end;

function TDmFrm.OpenSql: Boolean;
begin
  Result := False;
  AdoCon.Close;
  gdzccon.Close;
  AdoCon.ConnectionString := 'Provider=SQLOLEDB.1;Password=' +
    UncrypKey(ConfigValue.DbPass) +
    ';Persist Security Info=True;User ID=' +
    ConfigValue.DbUser + ';Initial Catalog=' +
    ConfigValue.DbBase + ';Data Source=' +
    ConfigValue.DbServer;
  gdzccon.ConnectionString := 'Provider=SQLOLEDB.1;Password=' +
    UncrypKey(ConfigValue.DbPass) +
    ';Persist Security Info=True;User ID=' +
    ConfigValue.DbUser + ';Initial Catalog=' +
    ConfigValue.DbBase + ';Data Source=' +
    ConfigValue.DbServer;
  try
    AdoCon.Open;
    gdzccon.Open;
  except
    Exit;
  end;
  Result := True;
end;


function TDmFrm.FiletoFile(FileName: string): string;
var
  NewFile: string;
begin
  NewFile := Copy(FileName, 13, 4) + '/' +
    Copy(FileName, 17, 2) + '/' +
    Copy(FileName, 19, 2) + '/';
//  ShowMessage(NewFile);
  Result := NewFile;
end;

procedure TDmFrm.ClearBlackVeh;
begin
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Text := 'Delete from vehicle Where HPHM In ' +
    ' (Select Hphm From TSCL)';
  ADOQuery1.ExecSQL;
end;

function TDmFrm.CheckBlackNum(m_CardNum: string): Boolean;
var
  i: Integer;
  m_BlackNum: string;
begin
  Result := False;
  for i := 0 to BlackHashList.Count - 1 do
  begin
    m_BlackNum := BlackHashList.Strings[i];
    if Pos(m_BlackNum, m_CardNum) > 0 then
    begin
      Result := True;
      Break;
    end;
  end;
end;

procedure TDmFrm.LoadBlackNum2HashList;
begin
  AQ.Close;
  AQ.SQL.Clear;
  AQ.SQL.Add('select id,BlackNum from BlackList ');
  AQ.Open;
  BlackHashList.Clear;
  while not AQ.Eof do
  begin
    BlackHashList.Add(AQ.FieldByName('BlackNum').AsString);
    AQ.Next;
  end;
end;

function TDmFrm.CheckDBforBlackList: Boolean;
begin
  try
    AQ.Close;
    AQ.SQL.Clear;
    AQ.SQL.Add('IF not EXISTS (SELECT id FROM dbo.sysobjects WHERE id = ' +
      'OBJECT_ID(N''[dbo].[BlackList]'') AND OBJECTPROPERTY(id, ' +
      'N''IsUserTable'') = 1)' + #13#10 +
      'CREATE TABLE [dbo].[BlackList](' + #13#10 +
      '	[id] [int] IDENTITY(1,1) NOT NULL,' + #13#10 +
      '	[BlackNum] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,' + #13#10 +
      '	[Operator] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,' + #13#10 +
      '	[CreateTime] [datetime] NOT NULL CONSTRAINT [DF_BlackList_CreateTime]  ' +
      'DEFAULT (getdate())' + #13#10 +
      ') ON [PRIMARY]');
    AQ.ExecSQL;
    Result := True;
  except
    Result := False;
  end;
end;

procedure TDmFrm.DataModuleCreate(Sender: TObject);
begin
  BlackHashList := THashedStringList.Create;
  AllFunction := THashedStringList.Create;
  UserFunction := THashedStringList.Create;
    
end;

procedure TDmFrm.DataModuleDestroy(Sender: TObject);
begin
  BlackHashList.Free;
end;

procedure TDmFrm.AddBlackNum(m_CardNum: string);
begin
  if BlackHashList.IndexOf(m_CardNum) < 0 then
    BlackHashList.Add(m_CardNum);
end;

procedure TDmFrm.DeleteBlackNum(m_CardNum: string);
begin
  if BlackHashList.IndexOf(m_CardNum) >= 0 then
    BlackHashList.Delete(BlackHashList.IndexOf(m_CardNum));
end;

function TDmFrm.OpenAdoquery(TheAqry: TADOQuery; m_Sql: string): Boolean;
begin
  try
    with TheAqry do
    begin
      Close;
      SQL.Clear;
      SQL.Add(m_Sql);
      Open;
      First;
    end;
    Result := True;
  except
    Result := False;
  end;    
end;

function TDmFrm.OpenAdoquery(m_Sql: string): Boolean;
begin
  try
    with AQ do
    begin
      Close;
      SQL.Clear;
      SQL.Add(m_Sql);
      Open;
      First;
    end;
    Result := True;
  except
    Result := False;
  end;    
end;

function TDmFrm.HaveFunction(sFunctionID: string; bShowMSG: Boolean;
  m_Message: string): Boolean;
begin
  Result := True;
  if (LowerCase(UserDLMC) = 'admin') or (LogUser = '系统管理员') then
    Exit;
  if UserFunction.IndexOf(sFunctionID) < 0 then
  begin
    Result := False;
    if bShowMSG then
    begin
      if m_Message <> '' then
        Application.MessageBox(PChar('你没有[' + m_Message +
          ']功能的操作权限,请于管理员联系!'), '提示', MB_OK +
          MB_ICONINFORMATION)
      else
      begin
        Application.MessageBox(PChar('你没有[' + AllFunction.Values[
          sFunctionID] + ']功能的操作权限,请与管理员联系!'), '提示', MB_OK +
            MB_ICONINFORMATION);
      end;
    end;
  end;
end;

procedure TDmFrm.LoadAllFunction;
begin
  OpenAdoquery(AQ, 'select FunctionID + ''='' + FunctionName as funidname ' +
    'from T_GH_Function order by FUNCTIONID');
  AllFunction.Clear;
  while not AQ.Eof do
  begin
    AllFunction.Add(AQ.FieldByName('funidname').AsString);
    AQ.Next;
  end;
  AQ.Close;
end;

procedure TDmFrm.LoadUserFunction(RuleID: Integer);
var
  m_FunctionID: string;
begin
  OpenAdoquery(AQ, 'select FunctionID from T_GH_RuleAndFunction ' +
    ' where RuleID = ' + IntToStr(RuleID));
  UserFunction.Clear;
  if AQ.IsEmpty then
    Exit;
  while not AQ.Eof do
  begin
    m_FunctionID := Trim(AQ.FieldByName('FunctionID').AsString);
    while Length(m_FunctionID) > 1 do
    begin
      if UserFunction.IndexOf(m_FunctionID) < 0 then
        UserFunction.Add(m_FunctionID);
      m_FunctionID := Copy(m_FunctionID, 1, Length(m_FunctionID) - 3);
    end;
    AQ.Next;
  end;
  AQ.Close;
end;

function TDmFrm.ExecAdoquery(TheAqry: TADOQuery; m_Sql: string): Boolean;
begin
  try
    with TheAqry do
    begin
      Close;
      SQL.Clear;
      SQL.Add(m_Sql);
      ExecSQL;
      Result := True;
    end;
  except
    Result := False;
  end;  
end;

function TDmFrm.ExecAdoquery(m_Sql: string): Boolean;
begin
  try
    with AE do
    begin
      Close;
      SQL.Clear;
      SQL.Add(m_Sql);
      ExecSQL;
      Result := True;
    end;
  except
    Result := False;
  end;  
end;

function TDmFrm.GetNewGuid: string;
begin
  OpenAdoquery('select NewID() as NewGuid');
  Result := AQ.FieldByName('NewGuid').AsString;
end;

procedure TDmFrm.ReopenAdoquery(theAdoquery: TADOQuery);
var
  curRecNo :Integer;
begin
  curRecNo := theAdoquery.RecNo;
  theAdoquery.Requery();
  if curRecNo >= theAdoquery.RecordCount then
    theAdoquery.Last
  else
    theAdoquery.MoveBy(curRecNo);
end;

function TDmFrm.AutoCheckCarNum(strtxt, defaulttxt: string): string;
var
  i :Integer;
  m_tmp,m_strtxt :string;
  m_Result :WideString;
begin
  m_Result := '';
  if Pos('闯红灯',strtxt) > 0 then
  begin
    m_strtxt := Copy(strtxt,Pos('P',strtxt) + 1,Length(strtxt));
  end
  else
  begin
    m_strtxt := strtxt;
  end ;
  for i := 1 to Length(m_strtxt) do
  begin
    if ByteType(m_strtxt,i) = mbLeadByte then
    begin
      if copy(m_strtxt,i,2) = '(' then
        Continue;
      m_tmp := Copy(m_strtxt,i,14);
      m_Result := m_tmp;
      while Length(m_Result) <> 7 do
      begin
        m_tmp := Copy(m_tmp,1,Length(m_tmp) -1);
        m_Result := m_tmp;
      end;
      Break;
    end;
  end;
  if m_Result = '' then
    m_Result := defaulttxt;
  Result := m_Result;
end;

procedure TDmFrm.LoadChangShu(m_SL: TStrings; m_TableName, m_FieldName,
  StrAll: string);
var
  m_Sql: string;
begin
  m_Sql := 'select id,' + m_FieldName + ' from ' + m_TableName + ' order by id';
  OpenAdoquery(AQ, m_Sql);
  m_SL.Clear;
  if StrAll <> '' then
  begin
    m_SL.Add(StrAll);
  end;
  while not AQ.Eof do
  begin
    m_SL.Add(AQ.fieldbyname(m_FieldName).AsString);
    AQ.Next;
  end;
  AQ.Close;
end;

procedure TDmFrm.LoadOperator(m_SL: TStrings; StrAll: string);
var
  m_Sql :string;
begin
  m_Sql := 'select id,YHXM from User_Table';
  OpenAdoquery(AQ,m_Sql);
  m_SL.Clear;
  if StrAll <> '' then
    m_SL.Add(StrAll);
  while not AQ.Eof do
  begin
    m_SL.Add(AQ.FieldByName('YHXM').AsString);
    AQ.Next;
  end;
  AQ.Close;
end;

procedure TDmFrm.OutputcxGrid(mycxGrid: TcxGrid);
var
  SFD: TSaveDialog;
  FileName, FileExt: string;
begin
  SFD := TSaveDialog.Create(nil);
  try
    SFD.Filter := 'Excel文件 (*.xls)|*.xls|XML文件 (*.xml)|*.xml|文本文件 (*.txt)|*.txt|网页文件 (*.html)|*.html';
    SFD.Title := '导出为';
    SFD.DefaultExt := '.XLS';
    SFD.Options := [ofOverwritePrompt, ofEnableSizing];
    if not SFD.Execute then Exit;
    FileName := SFD.FileName;
    FileExt := UpperCase(ExtractFileExt(FileName));
    if FileExt = '.XLS' then
      ExportGridToExcel(FileName, mycxGrid, True, True)
    else
      if FileExt = '.XML' then
        ExportGridToHTML(FileName, mycxGrid, True, True)
      else
        if FileExt = '.TXT' then
          ExportGridToText(FileName, mycxGrid, True, True)
        else
          if FileExt = '.HTML' then
            ExportGridToHTML(FileName, mycxGrid, True, True)
          else
          begin
            Application.MessageBox('不支持的导出格式', 'xx');
            Exit;
          end;
    Application.MessageBox('导出数据成功!', '提示');
  finally
    SFD.Free;
  end;
end;

function TDmFrm.GetPath: string;
begin
  Result := ExtractFilePath(ParamStr(0));
  if Result[Length(Result)] <> '\' then
    Result := Result + '\';
end;

procedure TDmFrm.WriteOperatLog(m_Oper_module, m_Oper_DetailInfo: string);
var
  m_sql: string;
begin
  try
    m_sql := 'insert into T_Operat_Log(Oper_UseriD,Oper_UserName,Oper_module,'
      + 'Oper_DetailInfo,Oper_LoginName,Oper_DateTime) values(' +
      ':Oper_UseriD,:Oper_UserName,:Oper_module,'
      + ':Oper_DetailInfo,:Oper_LoginName,GetDate()) ';
    AQ_Log.CommandText := m_sql;
    AQ_Log.Parameters.ParamByName('Oper_UseriD').Value := LogUserID;
    AQ_Log.Parameters.ParamByName('Oper_UserName').Value := LogUser;
    AQ_Log.Parameters.ParamByName('Oper_module').Value := m_Oper_module;
    AQ_Log.Parameters.ParamByName('Oper_DetailInfo').Value := m_Oper_DetailInfo;
    AQ_Log.Parameters.ParamByName('Oper_LoginName').Value := LogUser;
    AQ_Log.Execute;
  except

  end;
end;

function TDmFrm.CreateAndOpenDataBase: Boolean;
var
  CreateAccess: OleVariant;
  StrSQL, AConnectionString: string;
begin
  try
    Result := false;
    if not FileExists(GetPath + DataBaseFileName) then
    begin
      if FileExists(GetPath + 'sjk\' + DataBaseFileName) then
        CopyFile(PChar(GetPath + 'sjk\' + DataBaseFileName),
          PChar(GetPath + DataBaseFileName),False);
      Sleep(100);
    end;
    
    if ADOConn.Connected then ADOConn.Connected := false;
    AConnectionString := 'provider=Microsoft.Jet.OLEDB.4.0;user ID=Admin;Data Source='
      + GetPath + DataBaseFileName + ';'
      + 'Mode=Share Deny None;Extended Properties="";'
      + 'Jet OLEDB:System database="";'
      + 'Jet OLEDB:Registry Path="";'
      + 'Jet OLEDB:Database Password="' + DataBasePassWord + '";' //设定密码为123
      + 'Jet OLEDB:Engine Type=5;'
      + 'Jet OLEDB:Database Locking Mode=1;'
      + 'Jet OLEDB:Global Partial Bulk Ops=2;'
      + 'Jet OLEDB:Global Bulk Transactions=1;'
      + 'Jet OLEDB:New Database Password="";'
      + 'Jet OLEDB:Create System Database=False;'
      + 'Jet OLEDB:Encrypt Database=False;'
      + 'Jet OLEDB:Don' + '''' + 't Copy Locale on Compact=False;'
      + 'Jet OLEDB:Compact Without Replica Repair=False;'
      + 'Jet OLEDB:SFP=False';
    ADOConn.ConnectionString := AConnectionString;
    ADOConn.Connected := True;
    Result := True;
  except
    on E: Exception do
    begin
      MessageDlg('连接数据库失败:' + E.Message, mtError, mbOKCancel, 0);
    end;
  end;
end;

procedure TDmFrm.updateDB20111206;
var
  m_Sql :string;
begin
  m_Sql := 'if not Exists(select FunctionID from T_GH_Function '
    + ' where FunctionID = ''003001007'')'
    + ' insert into T_GH_Function(FunctionID,FunctionName,ParentID,Remark,'
    + ' Reason)'
    + ' values(''003001007'',''导出数据(稽查使用)'',''003001'','''','''')';
  ExecAdoquery(m_Sql);
end;

function TDmFrm.GetDataBaseFileName: string;
begin
  Result := DataBaseFileName;
end;

///更新数据库增加三个字段违章代码WZDM,罚款金额FKJE;处理标识CLBZ
///增加两个图片字段Pic1,Pic2;FileName1,FileName2
procedure TDmFrm.UpdateDB20120316;
var
  m_Sql :string;
begin
  m_Sql := 'if not exists(select a.id from sysobjects a inner join '
    + ' syscolumns b on b.id = a.id where a.[name] = ''T_VehiclePic'''
    + ' and b.[name] = ''pic1'')'#13#10
    + ' ALTER TABLE T_VehiclePic ADD Pic1 image NULL,Pic2 image NULL,'
    + ' FileName1 varchar(100) NULL,FileName2 varchar(100) NULL'#13#10
    + ' if not exists(select a.id from sysobjects a inner join syscolumns '
    + ' b on b.id = a.id where a.[name] = ''T_VehicleInfo'''
    + ' and b.[name] = ''FKJE'')'#13#10
    + ' ALTER TABLE T_VehicleInfo ADD '#13#10
    + ' FKJE money NOT NULL CONSTRAINT DF_T_VehicleInfo_FKJE DEFAULT 0,'#13#10
    + ' WFDM varchar(10) NOT NULL CONSTRAINT DF_T_VehicleInfo_WFDM '
    + ' DEFAULT '''',CLBZ varchar(10) NOT NULL CONSTRAINT '
    + ' DF_T_VehicleInfo_CLBZ DEFAULT ''未处理'','
	  + ' OraXH varchar(20) NOT NULL CONSTRAINT DF_T_VehicleInfo_OraXH '
    + ' DEFAULT '''' '#13#10
    + ' IF  not EXISTS (SELECT id FROM dbo.sysindexes '
    + ' WHERE id = OBJECT_ID(N''[T_VehicleInfo]'') AND name = ' 
    + ' N''IDX_T_VehicleInfo_HPHM'')'#13#10
    + ' CREATE NONCLUSTERED INDEX [IX_T_VehicleInfo_OraXH] ON [ T_VehicleInfo]'#13#10
    + ' ([OraXH] ASC) ON [PRIMARY]';
  ExecAdoquery(m_Sql);
end;

end.