Search options (Page 1 of 5)

procedure frmOverview_bExpCSV_OnClick (Sender: TObject; var Cancel: boolean);
var
   filename: string;
begin
    frm_aviso.Show;
    Form1_bExport; //aciona rotina exportação de lancamento_all_view para arquivo CSV, que será utilizado no qlikview
    filename := 'dashboard_orcamento_jrga.qvw';
    OpenFile(filename);
    frm_aviso.Close;
end;

procedure Form1_bExport;
var Sl : TstringList;
  folder: string;
  TableNames: TDataSet;
  TableCols: TDataSet;
  TableName,ColName,Cols,SelectStmt,ColType: string;
begin
   ProgressBar := TProgressBar.Create(frmMyNotas);
   ProgressBar.Parent := frmMyNotas.pnProgressBar;
   ProgressBar.Width := frmMyNotas.pnProgressBar.Width;
   ProgressBar.Height := frmMyNotas.pnProgressBar.Height;
   ProgressBar.Position := 0;
   ProgressBar.Max := 10;
   Sl := TstringList.Create;
   //SelectDirectory('Select Folder to Export File', './', folder, True);  //Dialog to select folder for which the exported file will be created
   //If Folder = '' then Exit;             // If no folder is selected then cancel the export
   frmMyNotas.pnProgressBar.Visible := True;
   frmMyNotas.lblImpExp.Caption := 'Exporting Database';
   application.processmessages;
   SqlQuery('SELECT name FROM sqlite_master WHERE type="view" and name = "lancamento_all_view"',TableNames); // Retrieve all tablenames from database and load into a dataset
   while not TableNames.Eof do   // Run through each tablename dataset
      begin
        Cols := '';
        SelectStmt := '';
        TableName := TableNames.FieldByName('name').AsString;
        SqlQuery('pragma table_info(' + TableName + ')',Tablecols); // Retrieve column (fields) names for each table
        While not TableCols.Eof do   // Go through each column (field) and concatenate together with comma separation
          begin
             ColName := TableCols.FieldByName('name').AsString;
             ColType := Tablecols.FieldByName('type').AsString;
             If ColType <> 'BLOB' then Cols := Cols + ColName + ','; // Do not select images for exporting
             TableCols.Next;
          end;
        Delete(Cols, Length(Cols), 1);   // Remove ending comma from column list
        ProgressBar.Position := ProgressBar.Position + 10;
        ExportToSl(TableName,Cols,Sl);  // Run Export procedure - pass the tablename and the list of columns to the procedure
        TableNames.Next;  // Go to next table name in dataset
      end;
   Sl.SavetoFile(path_atual+'ExportedData.csv'); // Save the stringlist to a csv file into the specified folder
   ProgressBar.Free;
   frmMyNotas.pnProgressBar.Visible := False;
   frmMyNotas.lblImpExp.Caption := '';
   //ShowMessage('Dados foram exportados para CSV');
   Sl.Free;
   TableNames.Free;
   TableCols.Free;
end;

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';
SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

Directory;
Lancamentos:
LOAD TableName, 
     data as "Data",
     tipo_conta as "Iipo Conta", 
     conta as "Conta", 
     categoria as "Categoria", 
     subcategoria as "Subcategoria", 
     categoria &' : '& subcategoria as "Categ.e subcat.",
     cheque as "Nºcheque", 
     grupo as "Grupo fornecedor", 
     fornecedor as "Fornecedor", 
     if(id_tipo_operacao='1','Entrada','Saída') as "Tipo operação", 
     Replace(entrada,'.',',') as "Vlr entrada",
     Replace(saida,'.',',')*-1 as "Vlr saida",
     (Replace(entrada,'.',',') + Replace(saida,'.',',')*-1) as "Valor", 
     Replace(meta_anual,'.',',') as "Meta anual",
     notas as "Notas",
     id_lancamento as "ID lancamento", 
     id_tipo_conta as "ID tipo conta", 
     id_conta as "ID conta", 
     id_categoria as "ID categoria", 
     id_tipo_operacao,
     id_subcategoria as "ID subcategoria", 
     id_grupo as "ID grupo", 
     id_fornecedor as "ID fornecedor",
     checado as "Conciliado" 
FROM
ExportedData.csv 
(txt, utf8, embedded labels, delimiter is ',', msq) 
WHERE len(categoria) <>0;


LOAD data as "Data"
    , year([data]) as Ano
    , month([data]) as Mes
    , day([data]) as Dia
FROM
ExportedData.csv
(txt, utf8, embedded labels, delimiter is ',', msq);