unit U_QRCodeToDB;
interface
uses
Classes,IniFiles,SysUtils,StrUtils,ADODB,activex;
type
TQRCodeToDB = class(TThread)
private
{ Private declarations }
Function GetSetDirectory(): string; //取得INI文件设置的扫描结果目录
procedure FindFile(Dir: String); //遍历查找文件
procedure ReadFile(FileName : String); //读取文件内容
function GetLastTime(Part_no:string):string; //取得最晚的时间
procedure WriteToDB(sTime,QRCode,Result:string); //将数据写入DB中
var WorkID : string;
protected
procedure Execute; override;
end;
implementation
{ QRCodeToDB }
procedure TQRCodeToDB.Execute;
begin
{ Place thread code here }
while not Terminated do
begin
FindFile(GetSetDirectory()+'\');
sleep(10000);//定时
end;
end;
procedure TQRCodeToDB.FindFile(Dir: String);
var
SearchRec: TSearchRec;
find: Integer;
begin
//查找目录所有文件
find:= FindFirst(Dir + '*.*', faAnyFile, SearchRec);
while find = 0 do
begin
if(SearchRec.Name <> '.') and (SearchRec.Name <> '..') then
if(Searchrec.Attr <> faDirectory) then
//过滤后缀名文件,采用字符串处理函数SameText和截取函数RightStr
begin
IF samestr(LeftStr(SearchRec.Name, 6) , FormatDateTime('YYMMDD',Now())) Then
begin
ReadFile(Dir+SearchRec.Name);
end;
end
else
begin
//递归查找子目录的文件
FindFile(Dir + SearchRec.Name + '\');
end;
find:= FindNext(SearchRec);
end;
//释放资源
FindClose(SearchRec);
end;
function TQRCodeToDB.GetLastTime(Part_no: string): string;
var
myQry : TADOQuery;
maxTime : string;
myConnect : TADOConnection;
begin
try
CoInitialize(nil); //初始化COM空间
myConnect := TADOConnection.Create(nil);
myQry := TADOQuery.Create(nil);
myConnect.ConnectionTimeout := 15;
myConnect.ConnectOptions := coConnectUnspecified;
myConnect.ConnectionString := 'Provider=SQLOLEDB.1;Password=336699;Persist Security Info=True;User ID=sa;Initial Catalog=XX_SCAN;Data Source=192.168.118.50';
try
myConnect.Connected := True;
except
myConnect.Connected := False;
maxTime := '24:00:01';
end;
if myConnect.Connected then
begin
myQry.Connection := myConnect;
myQry.SQL.Add('Select max(sTime) From DK_FunctionTest_Tab Where sDate = convert(char(6),getdate(),12) ');
myQry.SQL.Add(' and left(QRCode,9)= :vQRCode and WorkID = :vWorkID');
myQry.Parameters.ParamByName('vQRCode').Value := part_no;
myQry.Parameters.ParamByName('vWorkID').Value := WorkID;
myQry.Open;
maxTime := myQry.Fields[0].AsString;
end;
finally
FreeAndNil(myQry);
FreeAndNil(myConnect);
CoUninitialize();
Result := maxTime;
end;
end;
function TQRCodeToDB.GetSetDirectory: string;
var
MyINIFile : TIniFile;
MyIniPath : string;
LoadDirectory : string;
begin
try
MyIniPath := ExtractFilePath(ParamStr(0)) + 'QRCode.INI';
MyINIFile := TIniFile.Create(MyIniPath);
LoadDirectory := MyINIFile.ReadString('Directory','LoadDirectory','');
WorkID := MyINIFile.ReadString('Directory','WorkID','');
finally
FreeAndNil(MyINIFile);
Result := LoadDirectory;
end;
end;
procedure TQRCodeToDB.ReadFile(FileName: String);
var
sFile,sLine: TStrings;
r:Integer;
sTime,QRCode,Result: string;
maxTime : string;
FileStream: TFileStream;
begin
try
sFile := TStringList.Create;
FileStream:=TFileStream.Create(FileName,fmShareDenyNone);
sFile.LoadFromStream(FileStream);
sLine := TStringList.Create;
for r:=1 to sFile.Count-1 do //从第二行起,第一行为标题
begin
sLine.CommaText := sFile[r];
sTime := sLine[0];
QRCode := sLine[1];
Result := sLine[2];
if r = 1 then
maxTime := GetLastTime(copy(QRCode,1,9));
if ( (maxTime < sTime) or (maxTime = '') ) And (Length(QRCode)> 9) then
WriteToDB(sTime, QRCode, Result); //存入DB中
end;
finally
FreeAndNil(FileStream);
FreeAndNil(sLine);
FreeAndNil(sFile);
end;
end;
procedure TQRCodeToDB.WriteToDB(sTime, QRCode, Result: string);
var
myQry : TADOQuery;
myConnect : TADOConnection;
begin
try
CoInitialize(nil); //初始化COM空间
myConnect := TADOConnection.Create(nil);
myQry := TADOQuery.Create(nil);
myConnect.ConnectionTimeout := 15;
myConnect.ConnectOptions := coConnectUnspecified;
myConnect.ConnectionString := 'Provider=SQLOLEDB.1;Password=密码;Persist Security Info=True;User ID=用户;Initial Catalog=库;Data Source=地址';
try
myConnect.Connected := True;
except
myConnect.Connected := False;
end;
if myConnect.Connected then
begin
myQry.Connection := myConnect;
myQry.SQL.Add('INSERT INTO DK_FunctionTest_Tab(sTime,QRCode,sResult,WorkID,sDate) ');
myQry.SQL.Add('values( :vTime,:vCode,:vResult,:vWorkID, convert(char(6),getdate(),12) ) ');
myQry.Parameters.ParamByName('vTime').Value := sTime;
myQry.Parameters.ParamByName('vCode').Value := QRCode;
myQry.Parameters.ParamByName('vResult').Value := Result;
myQry.Parameters.ParamByName('vWorkID').Value := WorkID;
myQry.ExecSQL;
end;
finally
freeAndNil(myQry);
freeAndNil(myConnect);
CoUninitialize();
end;
end;
end.