second transform - edit mode, again temp=0.2; prompt "Write a function above prOpenSQL procedure, which outputs SQL conditions, when given SQL column name, date & time GUI controls as input parameters."
建立於 差異永不過期
function fnGetSqlConditions(const ASqlColumnName: String;
const ADatePicker: TDatePickerFrame;
const ATimePickerFrom, ATimePickerTo: TTimePickerFrame): String;
begin
Result := '';
// filtering DATA_INS by creation date range
if (ADatePicker.DateFrom <> 0) then begin
if (not ATimePickerFrom.fnIsEmpty()) then
Result := Result + Format(' and %s >= %s', [ASqlColumnName, QuotedStr(fnDateToSqlStr(ADatePicker.DateFrom + ATimePickerFrom.Value))])
else
Result := Result + Format(' and %s >= %s', [ASqlColumnName, QuotedStr(fnDateToSqlStr(ADatePicker.DateFrom))]);
end;
if (ADatePicker.DateTo <> 0) then begin
if (not ATimePickerTo.fnIsEmpty()) then
Result := Result + Format(' and %s <= %s', [ASqlColumnName, QuotedStr(fnDateToSqlStr(ADatePicker.DateTo + ATimePickerTo.Value))])
else
Result := Result + Format(' and %s <= %s', [ASqlColumnName, QuotedStr(fnDateToSqlStr(ADatePicker.DateTo))]);
end;
end;
procedure TfmWebLogForm.prOpenSQL;
procedure TfmWebLogForm.prOpenSQL;
const
const
C_SQL = 'select hdl_d11, hdl_d02_d11, hdl_007_d11, hdl_d11_d11, event_d11, '
C_SQL = 'select hdl_d11, hdl_d02_d11, hdl_007_d11, hdl_d11_d11, event_d11, '
+ ' time_d11, '
+ ' time_d11, '
+ ' remote_ip_d11, user_d11, session_id_d11, url_d11, content_type_d11, content_d11'
+ ' remote_ip_d11, user_d11, session_id_d11, url_d11, content_type_d11, content_d11'
+ ' from tweb_srv_log_d11 d11 where'
+ ' from tweb_srv_log_d11 d11 where'
+ ' d11.hdl_d11_d11 is null';
+ ' d11.hdl_d11_d11 is null';
begin
begin
frmSQLGridFrame1.SQLText := C_SQL;
frmSQLGridFrame1.SQLText := C_SQL;
// add query filters for:
// add query filters for:
// - filtering HDL_D02
// - filtering HDL_D02
// - filtering DATA_INS by creation date range (incl time)
// - filtering DATA_INS by creation date range (incl time)
// - filtering TIME by execution date range (incl time)
// - filtering TIME by execution date range (incl time)
// immediately after that, open query
// immediately after that, open query
// use QuotedStr and fnDateToSqlStr(TDateTime) to format TDateTime parameters for the query
// use QuotedStr and fnDateToSqlStr(TDateTime) to format TDateTime parameters for the query
// if time is not empty, use property Value to extract it from time picker
// if time is not empty, use property Value to extract it from time picker
// time must be added to date before casting to string
// time must be added to date before casting to string
//filtering HDL_D02
//filtering HDL_D02
if (HdlD02 <> 0) then
if (HdlD02 <> 0) then
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + Format(' and HDL_D02_D11 = %d', [HdlD02]);
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + Format(' and HDL_D02_D11 = %d', [HdlD02]);
// filtering DATA_INS by creation date range
// filtering DATA_INS by creation date range, filtering TIME by execution date range
if (dpCreated.DateFrom <> 0) then begin
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + fnGetSqlConditions('DATA_INS_D11', dpCreated, edtTimeCreatedFrom, edtTimeCreatedTo) + fnGetSqlConditions('TIME_D11', dpExecuted, edtTimeExecutedFrom, edtTimeExecutedTo);
if (not edtTimeCreatedFrom.fnIsEmpty()) then
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + Format(' and DATA_INS_D11 >= %s', [QuotedStr(fnDateToSqlStr(dpCreated.DateFrom + edtTimeCreatedFrom.Value))])
else
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + Format(' and DATA_INS_D11 >= %s', [QuotedStr(fnDateToSqlStr(dpCreated.DateFrom))]);
end;
if (dpCreated.DateTo <> 0) then begin
if (not edtTimeCreatedTo.fnIsEmpty()) then
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + Format(' and DATA_INS_D11 <= %s', [QuotedStr(fnDateToSqlStr(dpCreated.DateTo + edtTimeCreatedTo.Value))])
else
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + Format(' and DATA_INS_D11 <= %s', [QuotedStr(fnDateToSqlStr(dpCreated.DateTo))]);
end;
// filtering TIME by execution date range
if (dpExecuted.DateFrom <> 0) then begin
if (not edtTimeExecutedFrom.fnIsEmpty()) then
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + Format(' and TIME_D11 >= %s', [QuotedStr(fnDateToSqlStr(dpExecuted.DateFrom + edtTimeExecutedFrom.Value))])
else
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + Format(' and TIME_D11 >= %s', [QuotedStr(fnDateToSqlStr(dpExecuted.DateFrom))]);
end;
if (dpExecuted.DateTo <> 0) then begin
if (not edtTimeExecutedTo.fnIsEmpty()) then
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + Format(' and TIME_D11 <= %s', [QuotedStr(fnDateToSqlStr(dpExecuted.DateTo + edtTimeExecutedTo.Value))])
else
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + Format(' and TIME_D11 <= %s', [QuotedStr(fnDateToSqlStr(dpExecuted.DateTo))]);
end;
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + ' order by DATA_INS_D11 desc';
frmSQLGridFrame1.SQLText := frmSQLGridFrame1.SQLText + ' order by DATA_INS_D11 desc';
frmSQLGridFrame1.prOpenQuery;
frmSQLGridFrame1.prOpenQuery;
frmSQLGridFrame1.OnRowRefresh := prRowInfo;
frmSQLGridFrame1.OnRowRefresh := prRowInfo;
frmSQLGridFrame1.VisibleColumnsList.Values['DATA_INS_D11'] := 'Created';
frmSQLGridFrame1.VisibleColumnsList.Values['DATA_INS_D11'] := 'Created';
end;
end;