Search options (Page 14 of 42)

Export('SELECT lastname, firstname, salary, dateofbirth, isSmoke, comment FROM employees')+#13#10+
       ('SELECT coDate, coName, coActive FROM Company', 'ExportedData-' + FormatDateTime('dd-mm-yyyy hh-nn-ss', now)+'.csv');

var SaveDialog: TSaveDialog;
    OpenDialog: TOpenDialog;
    sFileName: string;
    Results: TDataSet;
    i,c: integer;
    sValue, sValues: string;

Procedure ExportToSl (SQL: string; Sl: TstringList);
begin
SQLQuery(sql, Results);
c := Results.FieldCount-1;

  while not Results.eof do
  begin
  sValues := '';
  for i := 0 to c do
      begin
      sValue := Results.Fields.Fields[i].AsString;
      if (Results.Fields.Fields[i].DataType = ftFloat) or (Results.Fields.Fields[i].DataType = ftLargeint) then
          sValue := ReplaceStr(sValue, ',', '.')
          else
          begin
          sValue := ReplaceStr(sValue, ',', ',');
          sValue := ReplaceStr(sValue, #13#10, '|');
          sValue := '''' + escape_special_characters(sValue) + '''';
          end;

          sValues := sValues + sValue+',';
        end;

  if sValues <> '' then SetLength(sValues, Length(sValues)-1);
  sl.Add(sValues);
  Results.next;
  end;
Results.Close;
end;


// EXPORT ///////////////////////////////////////////////////////////////////////////////////////////////////////////
procedure Form1_bExport_OnClick (Sender: string; var Cancel: boolean);
var Sl : TstringList;
begin
Sl := TstringList.Create;
sFileName := 'ExportedData-' + FormatDateTime('dd-mm-yyyy hh-nn-ss', now)+'.csv';
SaveDialog := TSaveDialog.Create(Form1);
SaveDialog.FileName := sFileName;
SaveDialog.DefaultExt := 'csv';
SaveDialog.Filter := 'Database|*.csv|Any file|*.*';
SaveDialog.Options := ofOverwritePrompt+ofHideReadOnly+ofEnableSizing;

if SaveDialog.Execute then
   begin
       ExportToSl('SELECT lastname, firstname, salary, dateofbirth, isSmoke, comment FROM employees', Sl);
       ExportToSl('SELECT coDate,  coName, coActive FROM Company', Sl);
       Sl.SavetoFile('.\ExportedData.csv' + FormatDateTime('dd-mm-yyyy hh-nn-ss', now)+'.csv');

       ShowMessage('Data has been exported in to the specified folder');
   end;
SaveDialog.Free;
Sl.Free;
end;


// IMPORT //////////////////////////////////////////////////////////////////////////////////////////////////////////
procedure Form1_bImport_OnClick (Sender: string; var Cancel: boolean);
begin
sFileName := 'ExportedData-' + FormatDateTime('dd-mm-yyyy hh-nn-ss', now)+'.csv';
OpenDialog := TOpenDialog.Create(Form1);
OpenDialog.FileName := sFileName;
OpenDialog.DefaultExt := 'csv';
OpenDialog.Filter := 'Database|*.csv|Any file|*.*';
OpenDialog.Options := ofOverwritePrompt+ofHideReadOnly+ofEnableSizing;

if OpenDialog.Execute then
   begin
   Import('employees', 'lastname,firstname,salary,dateofbirth,isSmoke,comment'+#13#10+
          'Company', 'coDate, coLogo, coName, coActive', 'ExportedData-' + FormatDateTime('dd-mm-yyyy hh-nn-ss', now)+'.csv');

   CopyFile(ExtractFilePath(Application.ExeName)+'ExportedData.csv' + FormatDateTime('dd-mm-yyyy hh-nn-ss', now)+'.csv', OpenDialog.FileName);
   ShowMessage('Data has been imported.');
   end;
OpenDialog.Free;
end; 

BEGIN TRANSACTION;

CREATE TABLE issueFilter
(
    id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
    issueFilter TEXT
);

-- Creating table Issues from scratch (simple ALTER TABLE is not enough)

CREATE TABLE temp_Issues_1848543519
(
    id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
    issueDate TEXT,
    issueName TEXT NOT NULL,
    issueReoprted TEXT,
    issueDesc TEXT,
    id_Application INTEGER,
    id_issueFilter INTEGER,
    issueFilterValue TEXT,
    FOREIGN KEY (id_Application) REFERENCES Application(id) ON DELETE CASCADE,
    FOREIGN KEY (id_issueFilter) REFERENCES issueFilter(id)
);

-- Copying rows from original table to the new table

INSERT INTO temp_Issues_1848543519 (id,issueDate,issueName,issueReoprted,issueDesc,id_Application,id_issueFilter,issueFilterValue) SELECT id,issueDate,issueName,issueReoprted,issueDesc,id_Application,id_issueFilter,issueFilterValue FROM Issues;

-- Droping the original table and renaming the temporary table

DROP TABLE Issues;
ALTER TABLE temp_Issues_1848543519 RENAME TO Issues;

COMMIT TRANSACTION;

procedure Form1_Button7_OnClick (Sender: TObject; var Cancel: boolean);
var SqliteTr: TDBXSqliteTransaction;
begin
    //Initiate the transaction
    SqliteTr := BeginSQLiteTransaction;

    //SQL command 1
    SQLExecute('CREATE TABLE issueFilter'+
    '('+
    'issueFilter TEXT'+
    ')');

    //SQL command 2
    SQLExecute('CREATE TABLE temp_Issues_1848543519'+
    '('+
    'id INTEGER PRIMARY KEY ASC AUTOINCREMENT,'+
    'issueDate TEXT,'+
    'issueName TEXT NOT NULL,'+
    'issueReoprted TEXT,'+
    'issueDesc TEXT,'+
    'id_Application INTEGER,'+
    'id_issueFilter INTEGER,'+
    'issueFilterValue TEXT,'+
    'FOREIGN KEY (id_Application) REFERENCES Application(id) ON DELETE CASCADE,'+
    'FOREIGN KEY (id_issueFilter) REFERENCES issueFilter(id'+
    '))');

    //SQL command 3
    SQLExecute('INSERT INTO temp_Issues_1848543519 (id,issueDate,issueName,issueReoprted,issueDesc,id_Application,id_issueFilter,issueFilterValue) '
              +'SELECT id,issueDate,issueName,issueReoprted,issueDesc,id_Application,id_issueFilter,issueFilterValue FROM Issues;');

    //SQL command 4
    SQLExecute('DROP TABLE Issues;');

    //SQL command 5
    SQLExecute('ALTER TABLE temp_Issues_1848543519 RENAME TO Issues;');

    //Commit the SQLTransaction
    CommitSQLiteTransaction(SqliteTr);
end;

SQLExecute('CREATE TABLE issueFilter'+
    '('+
    'issueFilter TEXT'+
    ')');

 { SQLite Compare script::::
BEGIN TRANSACTION;
-- Adding missing table columns

ALTER TABLE company ADD COLUMN coPhone TEXT;

CREATE TABLE products
(
    id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
    pName TEXT,
    pPrice REAL,
    id_company INTEGER,
    FOREIGN KEY (id_company) REFERENCES company(id) ON DELETE CASCADE
);
COMMIT TRANSACTION;
   }

//////////////////////////////////////////////////////////////////////////

procedure Form1_Button8_OnClick (Sender: TObject; var Cancel: boolean);
var SqliteTr: TDBXSqliteTransaction;
begin
//Initiate the transaction
SqliteTr := BeginSQLiteTransaction;

SQLExecute('ALTER TABLE company ADD COLUMN coPhone TEXT;');

SQLExecute('CREATE TABLE products'+
    '('+
'id INTEGER PRIMARY KEY ASC AUTOINCREMENT,'+
'pName TEXT,'+
'pPrice REAL,'+
'id_company INTEGER REFERENCES company(id) ON DELETE CASCADE,'+
')');

//Commit the SQLTransaction
CommitSQLiteTransaction(SqliteTr);
end;

procedure Form1_btnHideLeftPanel_OnClick (Sender: TObject; var Cancel: boolean); // Hide <<
begin
//Form1.Width := - Form1.Panel1.Width;
end;

procedure Form1_btnShowLeftPanel_OnClick (Sender: TObject; var Cancel: boolean); // Show >>
begin
//Form1.Width := + Form1.Panel1.Width;
end;