389 lines
11 KiB
ObjectPascal
389 lines
11 KiB
ObjectPascal
unit U_ADOFunc;
|
||
interface
|
||
uses
|
||
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
|
||
Dialogs, ComCtrls, ToolWin, StdCtrls,DBGrids, DB, cxDBData, ADODB,StrUtils,
|
||
Midas, ExtCtrls, Buttons,DBClient,FTComboBox,BtnEdit;
|
||
|
||
|
||
function ISNotOnly(ADOQry:TADOQuery;fieldname: string;tablename: string; str_vari:string): boolean;
|
||
function WriteLog(ADOQry:TADOQuery;mModel:string;mAction:string;OpEvent:string):Boolean;
|
||
procedure GetERPSetQryTimeValue(ADOQry:TADOQuery);
|
||
function DelData(ADOQueryCmd:TADOQuery;mDelStr:String;mInt:Integer):Boolean;
|
||
procedure Setsavedata(ADOQueryCmd:TADOQuery;MyTable:string;
|
||
Myparent:TWinControl;MyTag:integer);
|
||
function GetFieldValue(sqlStr:String;mcaption:string;ADOQuerytmp:TADOQuery):Boolean;
|
||
procedure CreateCDSChg(SADOQry:TADOQuery; mClientDataset:TclientDataSet; AColCount: Integer);
|
||
function ADORowChgToCol(fromADoQry:TADOQuery;toClientSet:TclientDataSet;
|
||
colCount:integer;GroupFields:string;chnField:string):Boolean;
|
||
procedure GetYearPeriod(AdoQueryTemp:TADOQuery;FDate:TDateTime);
|
||
function ADOColToRow(fromADoQry:TADOQuery;toClientSet:TclientDataSet;
|
||
chgFields:string;chgCaptions:string;
|
||
TocolCount:integer):boolean;
|
||
implementation
|
||
uses
|
||
U_global,U_formPas,U_commFunc;
|
||
////判断输入的字符型数据是否已经存在
|
||
function ISNotOnly(ADOQry:TADOQuery;fieldname: string;tablename: string; str_vari:string): boolean;
|
||
begin
|
||
result:=true;
|
||
try
|
||
with ADOQry do
|
||
begin
|
||
close;
|
||
sql.Clear;
|
||
sql.Add('select '+fieldname+' from '+tablename);
|
||
sql.Add('where '+fieldname+'='''+str_vari+'''' );
|
||
open;
|
||
if (recordcount>0) and (not fields[0].isnull) then
|
||
result:=false;
|
||
close;
|
||
end;
|
||
except
|
||
end;
|
||
end;
|
||
//////////////////////////////////////////////////////
|
||
function WriteLog(ADOQry:TADOQuery;mModel:string;mAction:string;OpEvent:string):Boolean;
|
||
begin
|
||
///////////////////////////////////
|
||
//写日志
|
||
try
|
||
with ADOQry do
|
||
begin
|
||
close;
|
||
sql.Clear ;
|
||
sql.Add('select * from xs_sysLog');
|
||
sql.Add('where 1<>1');
|
||
Open;
|
||
|
||
Append;
|
||
fieldByName('Operor').Value :=gUserName;
|
||
fieldByName('OperTime').Value :=gserverDate;
|
||
fieldByName('Model').Value :=mModel;
|
||
fieldByName('acction').Value :=mAction;
|
||
fieldByName('result').Value :='成功';
|
||
fieldByName('OpEvent').Value :=trim(OpEvent);
|
||
Post;
|
||
end;
|
||
result:=true;
|
||
except
|
||
result:=false;
|
||
end;
|
||
end;
|
||
///////////////////////////////////////////////////////
|
||
//
|
||
///////////////////////////////////////////////////////
|
||
procedure GetERPSetQryTimeValue(ADOQry:TADOQuery);
|
||
begin
|
||
try
|
||
with ADOQry do
|
||
begin
|
||
close;
|
||
sql.Clear ;
|
||
sql.Add('select * from XS_SysParam');
|
||
Open;
|
||
if RecordCount>0 then
|
||
begin
|
||
gQryBegTime:=fieldByName('QryBegTime').AsString ;
|
||
gQryEndTime:=fieldByName('QryEndTime').AsString ;
|
||
end
|
||
else
|
||
begin
|
||
gQryBegTime:='00:00';
|
||
gQryEndTime:='00:00';
|
||
end;
|
||
end;
|
||
except
|
||
end;
|
||
end;
|
||
//////////////////////////////////////////////
|
||
//////函数功能：根据SQL语句删除数据
|
||
//////////////////////////////////////////////
|
||
function DelData(ADOQueryCmd:TADOQuery;mDelStr:String;mInt:Integer):Boolean;
|
||
begin
|
||
try
|
||
result:=False;
|
||
with ADOQueryCmd do
|
||
begin
|
||
Close;
|
||
SQL.Clear;
|
||
SQL.Add(mDelStr);
|
||
ExecSQL;
|
||
end;
|
||
result:=True;
|
||
except
|
||
result:=False;
|
||
Application.MessageBox('数据删除失败！','提示',0);
|
||
end;
|
||
end;
|
||
/////////////////////////////////////////////////////
|
||
//保存数据时字段赋值
|
||
/////////////////////////////////////////////////////
|
||
procedure Setsavedata(ADOQueryCmd:TADOQuery;MyTable:string;
|
||
Myparent:TWinControl;MyTag:integer);
|
||
var
|
||
i:Integer;
|
||
begin
|
||
with Myparent do
|
||
begin
|
||
for i:=0 to ControlCount-1 do
|
||
begin
|
||
if Controls[i].Tag=MyTag then
|
||
begin
|
||
if Controls[i] is TEdit then
|
||
begin
|
||
ADOQueryCmd.FieldByName(Controls[i].Name).Value:=Trim(TEdit(Controls[i]).Text);
|
||
end else
|
||
if Controls[i] is TRichEdit then
|
||
begin
|
||
ADOQueryCmd.FieldByName(Controls[i].Name).Value:=TRichEdit(Controls[i]).Text;
|
||
end else
|
||
if Controls[i] is TComboBox then
|
||
begin
|
||
ADOQueryCmd.FieldByName(Controls[i].Name).Value:=Trim(TComboBox(Controls[i]).Text);
|
||
end else
|
||
if Controls[i] is TDateTimePicker then
|
||
begin
|
||
if TDateTimePicker(Controls[i]).ShowCheckbox then
|
||
begin
|
||
if TDateTimePicker(Controls[i]).Checked then
|
||
ADOQueryCmd.FieldByName(Controls[i].Name).Value:=TDateTimePicker(Controls[i]).DateTime;
|
||
end else
|
||
ADOQueryCmd.FieldByName(Controls[i].Name).Value:=TDateTimePicker(Controls[i]).DateTime;
|
||
end else
|
||
if Controls[i] is TBtnEditA then
|
||
begin
|
||
ADOQueryCmd.FieldByName(Controls[i].Name).Value:=Trim(TBtnEditA(Controls[i]).TxtCode);
|
||
end;
|
||
end;
|
||
end;
|
||
end;
|
||
end;
|
||
////////////////////////////////////////////////////////
|
||
//
|
||
//////////////////////////////////////////////////////////
|
||
function GetFieldValue(sqlStr:String;mcaption:string;ADOQuerytmp:TADOQuery):Boolean;
|
||
begin
|
||
result:=false;
|
||
try
|
||
with ADOQuerytmp do
|
||
begin
|
||
close ;
|
||
sql.Clear ;
|
||
sql.Add(sqlStr);
|
||
Open;
|
||
end;
|
||
result:=true;
|
||
except
|
||
application.MessageBox(pchar('获取【'+mcaption+'】字段值出错!'),'警告信息',0);
|
||
end;
|
||
end;
|
||
//////////////////////////////////////////////////////////////////
|
||
procedure GetYearPeriod(AdoQueryTemp:TADOQuery;FDate:TDateTime);
|
||
var
|
||
fsj:string;
|
||
begin
|
||
fsj:=FormatDateTime('yyyy-MM-dd',FDate);
|
||
with AdoQueryTemp do
|
||
begin
|
||
Close;
|
||
SQL.Clear;
|
||
SQL.Add('select * from gy_kjrlb where Qsrq<='''+fsj+''' ');
|
||
SQL.Add(' and Zzrq>='''+fsj+'''');
|
||
Open;
|
||
end;
|
||
with AdoQueryTemp do
|
||
begin
|
||
if IsEmpty then
|
||
begin
|
||
Application.MessageBox('还未设置该日期的会计期间！','提示',0);
|
||
Exit;
|
||
end;
|
||
gkjyear:=fieldbyname('kjYear').AsInteger;
|
||
gperiod:=fieldbyname('period').AsInteger;
|
||
end;
|
||
end;
|
||
///////////////////////////////////////////////////////////
|
||
//mClientDataset:TclientDataSet
|
||
///////////////////////////////////////////////////////////
|
||
function ADORowChgToCol(fromADoQry:TADOQuery;toClientSet:TclientDataSet;
|
||
colCount:integer;GroupFields:string;chnField:string):Boolean;
|
||
var
|
||
i:integer;
|
||
k:integer;
|
||
idx:integer;
|
||
begin
|
||
if colCount<=0 then exit;
|
||
if GroupFields='' then exit;
|
||
if chnField='' then exit;
|
||
|
||
CreateCDSChg(fromADoQry, toClientSet, colCount);
|
||
|
||
with fromADoQry do
|
||
begin
|
||
first;
|
||
while not eof do
|
||
begin
|
||
toClientSet.Last;
|
||
if Trim(toClientSet.FieldByName(GroupFields).AsString) = Trim(fieldByName(GroupFields).AsString) then
|
||
// if toClientSet.locate(GroupFields,VarArrayOf([trim(fieldByName(GroupFields).AsString)]),[]) then
|
||
begin
|
||
idx:=toClientSet.fieldByName('curIndex').asInteger;
|
||
//是否已满要求列数
|
||
if (idx >= 1) and (idx < colCount) then
|
||
begin
|
||
toClientSet.Edit;
|
||
toClientSet.fieldByName('chgField'+intTostr(idx + 1)).value:=fieldByName(chnField).asfloat;
|
||
toClientSet.fieldByName('curIndex').value:=toClientSet.fieldByName('curIndex').AsInteger+1;
|
||
toClientSet.Post;
|
||
end
|
||
//多行换行
|
||
else
|
||
begin
|
||
toClientSet.append;
|
||
for k:=0 to fields.count-1 do
|
||
begin
|
||
toClientSet.fields[k].value:= fields[k].value;
|
||
end;
|
||
for i:=1 to colcount do
|
||
begin
|
||
toClientSet.FieldByName('chgField'+intToStr(i)).Value := 0;
|
||
end;
|
||
toClientSet.fieldByName('chgField1').value:=fieldByName(chnField).asfloat;
|
||
toClientSet.fieldByName('curIndex').value:= 1;
|
||
toClientSet.Post;
|
||
end;
|
||
end
|
||
else
|
||
begin
|
||
toClientSet.append;
|
||
for k:=0 to fields.count-1 do
|
||
begin
|
||
toClientSet.fields[k].value:= fields[k].value;
|
||
end;
|
||
for i:=1 to colcount do
|
||
begin
|
||
toClientSet.FieldByName('chgField'+intToStr(i)).Value := 0;
|
||
end;
|
||
toClientSet.fieldByName('chgField1').value:=fieldByName(chnField).asfloat;
|
||
toClientSet.fieldByName('curIndex').value:= 1;
|
||
toClientSet.Post;
|
||
end;
|
||
|
||
next;
|
||
end;
|
||
end;
|
||
end;
|
||
|
||
procedure CreateCDSChg(SADOQry:TADOQuery; mClientDataset:TclientDataSet; AColCount: Integer);
|
||
var
|
||
i: Integer;
|
||
mFieldName: String;
|
||
mSize: Integer;
|
||
begin
|
||
mFieldName := '';
|
||
|
||
mClientDataset.Close;
|
||
mClientDataset.FieldDefs.Clear;
|
||
with SADOQry do
|
||
begin
|
||
for i := 0 to FieldCount - 1 do //
|
||
begin
|
||
if (Fields[i].DataType = ftString) and (Fields[i].Size = 0) then
|
||
begin
|
||
mSize := 1;
|
||
end
|
||
else
|
||
begin
|
||
mSize := Fields[i].Size;
|
||
end;
|
||
mFieldName := Trim(Fields[i].FieldName);
|
||
mClientDataset.FieldDefs.Add(mFieldName, Fields[i].DataType, mSize);
|
||
end;
|
||
end;
|
||
for i := 1 to AColCount do
|
||
begin
|
||
mClientDataset.FieldDefs.Add('chgField' + IntToStr(i), ftFloat, 0);
|
||
end;
|
||
mClientDataset.FieldDefs.Add('curIndex', ftInteger, 0);
|
||
|
||
mClientDataset.CreateDataSet;
|
||
end;
|
||
/////////////////////////////////////////////////////
|
||
//函数功能：将数据集列转为行
|
||
//新数据集的第一列为行表头
|
||
/////////////////////////////////////////////////////
|
||
function ADOColToRow(fromADoQry:TADOQuery;toClientSet:TclientDataSet;
|
||
chgFields:string;chgCaptions:string;
|
||
TocolCount:integer):boolean;
|
||
var
|
||
mField:string;
|
||
fieldCount:integer;
|
||
i,j,k:integer;
|
||
chgFieldsList:Tstringlist;
|
||
chgCaptionsList:TstringList;
|
||
begin
|
||
fromADoQry.DisableControls;
|
||
toClientSet.DisableControls;
|
||
try
|
||
chgFieldsList:=TStringList.Create;
|
||
chgCaptionsList:=TStringList.Create;
|
||
chgFieldsList:=split(chgFields,',');
|
||
chgCaptionsList:=split(chgCaptions,',');
|
||
/////////////////////////////////////////
|
||
toClientSet.Close;
|
||
toClientSet.FieldDefs.Clear;
|
||
//创建新的数据集
|
||
for i:=0 to TocolCount do //
|
||
begin
|
||
toClientSet.FieldDefs.Add('def'+intTostr(i),
|
||
ftString,1000);
|
||
end;
|
||
toClientSet.Close;
|
||
toClientSet.CreateDataSet;
|
||
////////////////////////////////////////////
|
||
|
||
///////////////////////////
|
||
//将数据转换到新的数据集中
|
||
for i:=0 to chgCaptionsList.Count-1 do
|
||
begin
|
||
with toClientSet do
|
||
begin
|
||
Append;
|
||
//行标头字段内容
|
||
fieldByName('def0').Value := chgCaptionsList.Strings[i];
|
||
//列值到行值
|
||
//获取元数据集中对应的列字段
|
||
mField:=chgFieldsList.Strings[i];
|
||
fromADoQry.first;
|
||
for j:=1 to TocolCount do
|
||
begin
|
||
//附行值
|
||
K:=0;
|
||
while not fromADoQry.Eof do
|
||
begin
|
||
fieldByName('def'+intTostr(k+1)).value:=fromADoQry.fieldByName(mField).Value ;
|
||
K:=k+1;
|
||
fromADoQry.next;
|
||
end;
|
||
end;
|
||
Post;
|
||
end;
|
||
end;
|
||
chgFieldsList.Free;
|
||
chgCaptionsList.Free ;
|
||
fromADoQry.EnableControls;
|
||
toClientSet.EnableControls;
|
||
result:=true;
|
||
except
|
||
fromADoQry.EnableControls;
|
||
toClientSet.EnableControls;
|
||
chgFieldsList.Free;
|
||
chgCaptionsList.Free ;
|
||
result:=false;
|
||
end;
|
||
end;
|
||
|
||
|
||
end.
|