Title: Store and Restore Objects properties into database
Question: how to stor objects properties into database
Answer:
Table Structure
DROP TABLE IF EXISTS `consolidatetaxe`.`storereports`;
CREATE TABLE storereports` (
`id_report` int(11) NOT NULL auto_increment,
`id_user` varchar(255) character set latin1 NOT NULL default '',
`id_object` int(11) NOT NULL default '0',
`obj_name` varchar(20) character set latin1 NOT NULL default '',
`obj_propertie` varchar(50) character set latin1 NOT NULL default '',
`obj_propertie_value` varchar(255) NOT NULL,
`id_type` int(10) unsigned NOT NULL default '0',
`rpt_name` varchar(255) character set latin1 NOT NULL default '',
`rpt_create_date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id_report`),
KEY `Index` (`id_report`,`id_user`,`id_object`,`obj_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
{*------------------------------------------------------------------------------
Store the report config into database
@Param Sender is the TObject class
-------------------------------------------------------------------------------}
procedure TfrmReportGenerator.Store(aFilename: string);
var
I,iIndex,iCheck,iTotal,iType: Integer;
Temp: TComponent;
obj_name,
obj_propertie,
obj_propertie_value:string;
id_object:string;
sql:string;
doSql:Boolean;
begin
zqyStoreReport.Close;
zqyStoreReport.SQL.Clear;
doSQL:=False;
For i:=0 to High(CompCbss) do
begin
obj_name := EmptyStr;
obj_propertie := EmptyStr;
obj_propertie_value := EmptyStr;
id_object := EmptyStr;
iType := pcPanel.ActivePageIndex;
Case pcPanel.ActivePageIndex of
0:Temp := FindComponent(CompCbss[i]);
1:Temp := FindComponent(CompConso[i]);
2:Temp := FindComponent(CompNIBS[i]);
3:begin
Temp := FindComponent(CompNM1[i]);
iType := StrToInt(IntToStr(pcPanel.ActivePageIndex) + IntToStr(bxppSubPanel.ActivePageIndex));
end;
4:Temp := FindComponent(CompSAP[i]);
end;
if(Temp is TRadioGroup) then
begin
id_object := IntToStr(Temp.ComponentIndex);
obj_name := Temp.Name;
obj_propertie := 'ItemIndex';
obj_propertie_value := IntToStr((Temp as TRadioGroup).ItemIndex);
doSQL:=True;
end;
if(Temp is TCheckBox) then
begin
id_object := IntToStr(Temp.ComponentIndex);
obj_name := Temp.Name;
obj_propertie := 'Checked';
obj_propertie_value := BoolToStr((Temp as TCheckBox).Checked);
doSQL:=True;
end;
if(Temp is TCheckListBox) then
begin
id_object := IntToStr(Temp.ComponentIndex);
obj_name := Temp.Name;
obj_propertie := 'Checked';
iTotal := 0;
iCheck := 0;
for iIndex:=0 to (Temp as TCheckListBox).Count -1 do
begin
if (Temp as TCheckListBox).Checked[iIndex] then
begin
obj_propertie_value := obj_propertie_value + IntToStr(iIndex)+'|';
Inc(iCheck);
end;
Inc(iTotal);
end;
if ((iCheck) = iTotal) then obj_propertie_value := 'ALL';
doSQL:=True;
end;
if(Temp is TDateTimePicker) then
begin
id_object := IntToStr(Temp.ComponentIndex);
obj_name := Temp.Name;
obj_propertie := 'Date';
obj_propertie_value := DateTimeToStr((Temp as TDateTimePicker).Date);
doSQL:=True;
end;
if(Temp is TComboBox) then
begin
id_object := IntToStr(Temp.ComponentIndex);
obj_name := Temp.Name;
obj_propertie := 'Text';
obj_propertie_value := (Temp as TComboBox).Text;
doSQL:=True;
end;
if doSql then
begin
sql:='Insert Into storereports(id_user,id_object,obj_name,obj_propertie,obj_propertie_value,id_type, rpt_name, rpt_create_date) '+
'values("'+zcBell.User+'",'+id_object+',"'+obj_name+'","'+obj_propertie+'","'+obj_propertie_value+'",'+IntToStr(iType)+',"'+aFilename+'",current_timestamp());';
zqyStoreReport.Close;
zqyStoreReport.SQL.Text := sql;
zqyStoreReport.ExecSQL;
end;
doSQL:=False;
end;
frmStoredReport := TfrmStoredReport.Create(Self);
frmStoredReport.zqySelectStoredReport.ParamByName('vUser').AsString := zcBell.User;
frmStoredReport.ShowModal;
if frmStoredReport.bLoad then
Restore(frmStoredReport.LoadName);
frmStoredReport.Free;
end;
use:
Store('StoreAsName');
--------------------------------------------------------------
Table Structure
CREATE TABLE storereports` (
`id_report` int(11) NOT NULL auto_increment,
`id_user` varchar(255) character set latin1 NOT NULL default '',
`id_object` int(11) NOT NULL default '0',
`obj_name` varchar(20) character set latin1 NOT NULL default '',
`obj_propertie` varchar(50) character set latin1 NOT NULL default '',
`obj_propertie_value` varchar(255) NOT NULL,
`id_type` int(10) unsigned NOT NULL default '0',
`rpt_name` varchar(255) character set latin1 NOT NULL default '',
`rpt_create_date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id_report`),
KEY `Index` (`id_report`,`id_user`,`id_object`,`obj_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
{*------------------------------------------------------------------------------
Restore the report config from database
@Param Sender is the TObject class
-------------------------------------------------------------------------------}
procedure TfrmReportGenerator.Restore(aFilename: string);
var
I,iIndex: Integer;
Temp: TComponent;
obj_name,
obj_propertie,
obj_propertie_value:string;
id_object:string;
sql:string;
doSql:Boolean;
begin
bFocused := False;
bLoadReport := True;
rgConsoROutputFormat.ItemIndex := -1;
for iIndex:=0 to rgConsoReport.Count -1 do
rgConsoReport.Checked[iIndex]:=False;
iIndex:=0;
clearListbox([clbSubMarket, clbLOB, clbCurrency, clbProvince, clbCharger, clbTaxType, clbDebitCredit, clbLegacy, clbTaxCategory, clbWriteOffRecovery, clbBan, clbConsoFields, clbConsoGroup, clbConsoOrder]);
zqyStoreReport.Close;
sql:='SELECT id_report, id_user, id_object, obj_name, obj_propertie, obj_propertie_value, id_type, rpt_name, rpt_create_date '+
'FROM storereports WHERE rpt_name = "'+aFilename+'" and id_user = "'+zcBell.User+'";';
zqyStoreReport.SQL.Text := sql;
zqyStoreReport.Open;
zqyStoreReport.First;
While Not zqyStoreReport.Eof do
begin
obj_name := EmptyStr;
obj_propertie := EmptyStr;
obj_propertie_value := EmptyStr;
id_object := EmptyStr;
if not (zqyStoreReport.FieldByName('id_type').AsInteger in[30,31]) then
begin
pcPanel.ActivePageIndex := zqyStoreReport.FieldByName('id_type').AsInteger;
pcPanelChange(nil);
end
else
begin
pcPanel.ActivePageIndex := StrToInt(zqyStoreReport.FieldByName('id_type').AsString[1]);
pcPanelChange(nil);
bxppSubPanel.ActivePageIndex := StrToInt(zqyStoreReport.FieldByName('id_type').AsString[2]);
end;
Temp := FindComponent(zqyStoreReport.FieldByName('obj_name').AsString);
if(Temp is TRadioGroup) then
begin
if (Temp.ComponentIndex = zqyStoreReport.FieldByName('id_object').AsInteger) and
(Temp.Name = zqyStoreReport.FieldByName('obj_name').AsString) then
(Temp as TRadioGroup).ItemIndex := zqyStoreReport.FieldByName('obj_propertie_value').AsInteger;
// if Temp.Name = 'rgConsoROutputFormat' then iConsoROutputFormat := (Temp as TRadioGroup).ItemIndex;
end;
if(Temp is TCheckListBox) then
begin
if (Temp.ComponentIndex = zqyStoreReport.FieldByName('id_object').AsInteger) and
(Temp.Name = zqyStoreReport.FieldByName('obj_name').AsString) then
begin
obj_propertie_value := zqyStoreReport.FieldByName('obj_propertie_value').AsString;
for iIndex:=0 to (Temp as TCheckListBox).Count -1 do
if obj_propertie_value 'ALL' then
begin
if Pos(IntToStr(iIndex),obj_propertie_value)0 then
(Temp as TCheckListBox).Checked[iIndex]:=True;
end
else
(Temp as TCheckListBox).Checked[iIndex]:=True;
end;
if ((pcPanel.ActivePageIndex = 1) and (Temp.Name = 'rgConsoReport'))
then rgConsoReportClickCheck(nil);
if ((pcPanel.ActivePageIndex = 3) and (Temp.Name = 'rgNM1Report'))
then rgNM1ReportClickCheck(nil);
if ((pcPanel.ActivePageIndex = 4) and (Temp.Name = 'clbSAPCompany'))
then clbSAPCompanyClickCheck(nil);
end;
if(Temp is TCheckBox) then
begin
if (Temp.ComponentIndex = zqyStoreReport.FieldByName('id_object').AsInteger) and
(Temp.Name = zqyStoreReport.FieldByName('obj_name').AsString) then
(Temp as TCheckBox).Checked := StrToBool(zqyStoreReport.FieldByName('obj_propertie_value').AsString);
end;
if(Temp is TDateTimePicker) then
begin
if (Temp.ComponentIndex = zqyStoreReport.FieldByName('id_object').AsInteger) and
(Temp.Name = zqyStoreReport.FieldByName('obj_name').AsString) then
(Temp as TDateTimePicker).Date := zqyStoreReport.FieldByName('obj_propertie_value').AsDateTime;
end;
if(Temp is TComboBox) then
begin
if (Temp.ComponentIndex = zqyStoreReport.FieldByName('id_object').AsInteger) and
(Temp.Name = zqyStoreReport.FieldByName('obj_name').AsString) then
(Temp as TComboBox).Text := zqyStoreReport.FieldByName('obj_propertie_value').AsString;
end;
zqyStoreReport.Next;
end;
end;
use:
Restore('StoreAsName')