unit UMemtableToSql;

interface

uses SysUtils, Classes, DB, kbmMemTable, Variants, Dialogs, SuperObject;

function GenerateDataToInsertSQL(memtbale: TkbmMemTable; UpdateTableName: AnsiString; NoUpdateFields: TStrings): TStringList; //插入数据
//不需要更新的字段描述 [NoUpdateFields]
function GenerateDataToModifySQL(memtbale: TkbmMemTable; UpdateTableName: AnsiString; NoUpdateFields: TStrings; sWhereFiled: string): TStringList; //编辑数据

function GenerateDataToDeleteSQL(memtbale: TkbmMemTable; UpdateTableName: AnsiString; KeyFields: TStrings; sWhereFiled: string): TStringList; //删除数据

function ConvertStringsToJSON(ss: TStrings): WideString;

function ApplyInsertMemtable(memtbale: TkbmMemTable; UpdateTableName: AnsiString):AnsiString;

implementation

uses UBizServerModule;
function ExistString(subString: string;
sList: TStrings): boolean;
var
i: integer;
begin
result := false;
for i := 0 to sList.Count - 1 do
begin
if UpperCase(subString) = UpperCase(sList[i]) then
result := true;
end;

end;

procedure CustomSysdate(var ss: string;
const sField, DateTimeType: string);
begin
if (trim(DateTimeType) = '') or (UpperCase(DateTimeType) = 'ORACLE') then
ss := ss + sField + ' sysdate , ';

if UpperCase(DateTimeType) = 'MYSQL' then
ss := ss + sField + ' sysdate() , ';

if UpperCase(DateTimeType) = 'SQLSERVER' then
ss := ss + sField + ' GetDate() , ';

if UpperCase(DateTimeType) = 'SQLITE' then
ss := ss + sField + ' datetime(''now'') , ';

end;

procedure CustomTo_date(var sSet: string;
const sField, DateTimeType, vNew: string);
begin
if (trim(DateTimeType) = '') or (UpperCase(DateTimeType) = 'ORACLE') then
sSet := sSet + sField + ' to_date(''' + (vNew) + ''' , ''yyyy-MM-dd HH24:mi:ss''), ';
// sSet := sSet + sField + uHHConst.HHUniDateStr(vNew) ;

if UpperCase(DateTimeType) = 'MYSQL' then
sSet := sSet + sField + ' ''' + (vNew) + ''' , ';

if UpperCase(DateTimeType) = 'SQLITE' then
sSet := sSet + sField + ' ''' + (vNew) + ''' , ';

if UpperCase(DateTimeType) = 'SQLSERVER' then
sSet := sSet + sField + ' ''' + (vNew) + ''' , ';

end;

function CustomBoolean(vNew: string;
const sField, DateTimeType: string): string;
begin
Result := vNew;
if (trim(DateTimeType) = '') or (UpperCase(DateTimeType) = 'ORACLE') or (
UpperCase(DateTimeType) = 'MYSQL') then
begin
if UpperCase(vNew) = 'TRUE' then Result := '''1''';
if UpperCase(vNew) = 'FALSE' then Result := '''0''';
end;

{ ' ''' + VarToStr(vNew) + '''
if DateTimeType = 'MYSQL' then
begin
if vNew = 'true' then Result := '''1''' ;
if vNew = 'false' then Result := '''0''';
end;

if DateTimeType = 'SQLSERVER' then
ss := ss + sField + ' GetDate() , ';
}

end;

function GenerateDataToInsertSQL(memtbale: TkbmMemTable; UpdateTableName: AnsiString; NoUpdateFields: TStrings): TStringList;
var
i: integer;
s, sWhere, sFieldNames, sValues: string;
FDataToInsertSQL: TStringList;
vNew: variant;
DateTimeType: string;
begin
DateTimeType := 'MYSQL';
FDataToInsertSQL := TStringList.Create;
FDataToInsertSQL.Clear;
Result := nil;

if UpdateTableName = '' then
begin
ShowMessage('UpdateTableName 不能为空！');
exit;
end;
with memtbale do
begin
First;

while not EOF do
begin
s := 'insert into ' + UpdateTableName;
sWhere := ' ';
sFieldNames := ' (';
sValues := ' VALUES (';

for i := 0 to FieldCount - 1 do
begin
if NoUpdateFields.IndexOf(Fields[i].FieldName) < 0 then
if (not (Fields[i].DataType in kbmBinaryTypes)) or (Fields[i].DataType = ftMemo) then
begin
if UpperCase(Fields[i].FieldName) = 'ID_AUTO' then continue;
if UpperCase(Fields[i].FieldName) = 'ID' then continue;

vNew := Fields[i].Value;
if not (VarIsNull(vNew)) then
begin
sFieldNames := sFieldNames + Fields[i].FieldName + ', ';
if (Fields[i].DataType = ftBoolean) then
begin
//if vNew = 'true' then vNew := '1';
// if vNew = 'false' then vNew := '0';
vNew := CustomBoolean(vNew, '', DateTimeType);
sValues := sValues + VarToStr(vNew) + ', '
end
else if (Fields[i].DataType = ftDateTime) then
begin
if (Fields[i].FieldName = 'OPT_UPDATE_TIME') or
(Fields[i].FieldName = 'STOCK_OUT_TIME') or
(Fields[i].FieldName = 'STOCK_IN_TIME')
then
begin
{if DateTimeType = 'MYSQL' then
sValues := sValues + ' sysdate() , ';

if (trim(DateTimeType) = '') or (DateTimeType = 'ORACLE') then
sValues := sValues + ' sysdate , '; }
CustomSysdate(sValues, '', DateTimeType);
end
else
if (Fields[i].FieldName = 'update_time') or (Fields[i].FieldName = 'sample_time') then
begin
{if (trim(DateTimeType) = '') or (DateTimeType = 'ORACLE') then
sValues := sValues + ' sysdate , ';

if DateTimeType = 'MYSQL' then
sValues := sValues + ' sysdate() , ';

if DateTimeType = 'SQLSERVER' then
sValues := sValues + ' GetDate() , '; }
CustomSysdate(sValues, '', DateTimeType);
end
else
begin
{if (trim(DateTimeType) = '') or (DateTimeType = 'ORACLE') then
sValues := sValues + ' to_date(''' + VarToStr(vNew) + ''' , ''yyyy-MM-dd HH24:mi:ss''), ';

if DateTimeType = 'MYSQL' then
sValues := sValues + '''' + VarToStr(vNew) + ''' , ';

if DateTimeType = 'SQLSERVER' then
sValues := sValues + '''' + VarToStr(vNew) + ''' , ';
}
CustomTo_date(sValues, '', DateTimeType, VarToStr(vNew));
end;
end
else if (Fields[i].DataType = ftInteger) or (Fields[i].DataType = ftCurrency) or (Fields[i].DataType = ftFloat) then
begin
sValues := sValues + VarToStr(vNew) + ', ';
end
else
sValues := sValues + ' ''' + VarToStr(vNew) + ''', '
end;

{//一般不会用到这个，因为SID一般不能为空
if (VarIsNull(vNew)) then
begin
if Fields[i].FieldName = 'SID' then
begin
//处理Sequence
sFieldNames := sFieldNames + Fields[i].FieldName + ', ';
sValues := sValues + (DataSet as TkbmMemTableHH).UpdateTableName + '_SEQ.nextval, ';
end;
end; }
end;

end;

if sFieldNames = '' then exit;
if sValues = '' then exit;

if sWhere <> '' then sWhere := ' where ' + sWhere;
if sFieldNames <> '' then
begin
sFieldNames := Copy(sFieldNames, 1, Length(sFieldNames) - 2) + ') ';
end;
if sValues <> '' then
begin
sValues := Copy(sValues, 1, Length(sValues) - 2) + ') ';
end;

FDataToInsertSQL.Add(s + sFieldNames + sValues);
Next;
end;
end;

Result := FDataToInsertSQL;

end;

function GenerateDataToModifySQL(memtbale: TkbmMemTable; UpdateTableName: AnsiString; NoUpdateFields: TStrings; sWhereFiled: string): TStringList;
var
i: integer;
s, sWhere, sSet: string;
FDataToModifySQL: TStringList;
vNew: variant;
DateTimeType: string;
begin
Result := nil;
DateTimeType := 'MYSQL';
if UpdateTableName = '' then
begin
ShowMessage('UpdateTableName 不能为空！');
exit;
end;

FDataToModifySQL := TStringList.Create;
FDataToModifySQL.Clear;
with memtbale do
begin
First;
while not EOF do
begin
s := 'Update ' + UpdateTableName;
sWhere := ' ';
sSet := ' ';

for i := 0 to FieldCount - 1 do
begin
if NoUpdateFields.IndexOf(Fields[i].FieldName) < 0 then
if (not (Fields[i].DataType in kbmBinaryTypes)) or (Fields[i].DataType = ftMemo) then
begin
if UpperCase(Fields[i].FieldName) = 'ROWID' then continue;
if UpperCase(Fields[i].FieldName) = 'ID_AUTO' then continue;
if UpperCase(Fields[i].FieldName) = 'ID' then continue;

vNew := Fields[i].Value;
//vOld := OrigValues[i];

if (not (VarIsNull(vNew))) then
//if vNew <> vOld then
begin
//if not ExistString(Fields[i].FieldName, (DataSet as TkbmMemTableHH).KeyFields) then
begin
if (Fields[i].DataType = ftBoolean) then
begin
// if vNew = 'true' then vNew := '1';
//if vNew = 'false' then vNew := '0';
vNew := CustomBoolean(vNew, '', DateTimeType);
sSet := sSet + Fields[i].FieldName + ' = ' + VarToStr(vNew) + ', '
end
else if (Fields[i].DataType = ftDateTime) then
begin
if Fields[i].FieldName = 'OPT_UPDATE_TIME' then
//sSet := sSet + Fields[i].FieldName + ' = sysdate , '
CustomSysdate(sSet, Fields[i].FieldName + ' = ',
DateTimeType)
else
if (Fields[i].FieldName = 'update_time') or (Fields[i].FieldName = 'sample_time') then
begin
if DateTimeType <> 'SQLSERVER' then
sSet := sSet + Fields[i].FieldName + ' = GetDate() , ';

CustomSysdate(sSet, Fields[i].FieldName + ' = ', DateTimeType);
end
else
begin
CustomTo_date(sSet, Fields[i].FieldName + ' = ', DateTimeType, VarToStr(vNew));
end;

end
else if (Fields[i].DataType = ftInteger) or (Fields[i].DataType = ftCurrency) or (Fields[i].DataType = ftFloat) then
begin
sSet := sSet + Fields[i].FieldName + ' = ' + VarToStr(vNew) + ', '
end
else
sSet := sSet + Fields[i].FieldName + ' = ''' + VarToStr(vNew) + ''', '
end;
end;

end;

{if ExistString(Fields[i].FieldName, KeyFields) then
begin
if trim(sWhere) <> '' then
sWhere := sWhere + ' and ' + Fields[i].FieldName + ' = ' + VarToStr(vOld)
else
sWhere := sWhere + Fields[i].FieldName + ' = ' + VarToStr(vOld);

end; }

sWhere := ' ' + sWhereFiled + ' = ' + FieldByName('' + sWhereFiled + '').AsString;
end;

if trim(sSet) = '' then exit;
if trim(sWhere) = '' then
begin
ShowMessage('sWhere 为空！');
exit;
end;

if sWhere <> '' then sWhere := ' where ' + sWhere;
if sSet <> '' then
begin
sSet := ' Set ' + Copy(sSet, 1, Length(sSet) - 2); ;
end;

FDataToModifySQL.Add(s + sSet + sWhere);
Next;

end;
end;
//(DataSet as TkbmMemTableHH).ModifySQL := s + sSet + sWhere;
Result := FDataToModifySQL;

end;

function GenerateDataToDeleteSQL(memtbale: TkbmMemTable; UpdateTableName: AnsiString; KeyFields: TStrings; sWhereFiled: string): TStringList;
var
i: integer;
s, sWhere: string;
v: variant;
FDataToDeleteSQL: TStringList;
begin
FDataToDeleteSQL := TStringList.Create;
FDataToDeleteSQL.Clear;
Result := nil;

if UpdateTableName = '' then
begin
ShowMessage('UpdateTableName 不能为空！');
exit;
end;
with memtbale do
begin
First;

while not EOF do
begin
s := 'Delete from ' + UpdateTableName;
sWhere := '';

for i := 0 to FieldCount - 1 do
begin
v := Fields[i].Value;

if UpperCase(Fields[i].FieldName) = 'ROWID' then continue;
if UpperCase(Fields[i].FieldName) = 'ID_AUTO' then continue;
if UpperCase(Fields[i].FieldName) = 'ID' then continue;

if ExistString(Fields[i].FieldName, KeyFields) then
sWhere := sWhere + Fields[i].FieldName + ' = ' + VarToStr(v) + ' and ';
end;

if sWhere <> '' then sWhere := ' where ' + Copy(sWhere, 1, Length(sWhere) - 4);
// (DataSet as TkbmMemTableHH).DeleteSQL := s + sWhere;

if sWhere = '' then sWhere := ' where ' + sWhereFiled + ' = ' + FieldByName(sWhereFiled).AsString;
FDataToDeleteSQL.Add(s + sWhere);
Next;
end;
end;

Result := FDataToDeleteSQL;
end;

// 转换成json
function ConvertStringsToJSON(ss: TStrings): WideString;
var
jo: ISuperObject;
i: Integer;
begin
jo := TSuperObject.Create;

for i := 0 to ss.Count - 1 do
jo.S['YHYSQL' + IntToStr(i)] := ss[i];

Result := jo.AsString;

end;

//提交数据到服务器，进行操作
function ApplyInsertMemtable(memtbale: TkbmMemTable; UpdateTableName: AnsiString):AnsiString;
var sList:TStringList;
vjson:string;
begin
sList:=TStringList.Create;
sList:=GenerateDataToInsertSQL(memtbale,UpdateTableName,sList);
vjson:=ConvertStringsToJSON(sList);
Result:=fBizServerModule.getBaseService.ExecJsonSqls(vjson);
sList:=nil;
end;

end.