Italian community of Lazarus and Free Pascal

{Libreria realizzata da Sammarco Francesco - www.lazaruspascal.it}

unit ExtZQuery;

{$mode delphi}

interface

uses
  SysUtils, Classes, ZDataset,
{$IFDEF UseRuntime}
  Ext, ExtPascal, ExtPascalUtils, ExtForm,
  ExtData, ExtGrid, ExtUtil, ExtAir, ExtDd,
  ExtLayout, ExtMenu, ExtDirect, ExtState, ExtTree,
  ExtUxForm;

type
  {$M+}
  TExtPanel_Tab = TExtPanel;
  TExtFormTextField_Grid = TExtFormTextField;
  TExtFormNumberField_Grid = TExtFormNumberField;
  TExtFormDateField_Grid = TExtFormDateField;
  TExtFormTimeField_Grid = TExtFormTimeField;
  TExtFormCheckbox_Grid = TExtFormCheckbox;
  TExtFormComboBox_Grid = TExtFormComboBox;
  {$M-}

{$ELSE}
  ExtP_Design_Ctrls, ExtP_Design_Grid, ZConnection;
{$ENDIF}

type
  TExtZQuery = class(TZQuery)
      DataStore:TExtDataArrayStore;
  public
      MyErr: string;
      constructor Create(Owner : TComponent); override;
      destructor Destroy; override;
      function TransformsDatasetToDataStore(): boolean;
      function GetNumCols(): integer;
      function GetNumRows(): integer;
  private
         NumCols: integer;
         NumRows: integer;
  published
  end;

implementation

uses
    StrUtils, DateUtils, ZConnection;

constructor TExtZQuery.Create(Owner : TComponent);
begin
  inherited;
  Connection := TZConnection.Create(nil);
  with Connection do
  begin
    HostName := 'localhost';
    Database := 'c:\db_grid.s3db';
    User     := '';
    Password := '';
    Protocol := 'sqlite-3';
    Connect;
  end;
  DataStore:=TExtDataArrayStore.Create;
end;

destructor TExtZQuery.Destroy;
begin
  Close;
  Connection.Disconnect;
  Connection.Free;
  inherited;
end;

function TExtZQuery.TransformsDatasetToDataStore(): boolean;
var
   ret: boolean;
   i: integer;
   app: string;
begin
     try
        try
           NumCols:=0;
           NumRows:=0;
           MyErr:='';
           ret:=TRUE;
           With DataStore do
           begin
                //imposto le colonne
                if FieldDefs.Count>0 then
                begin
                     for i:=0 to FieldDefs.Count-1 do
                     begin
                          TExtDataField.AddTo(Fields).Name := FieldDefs.Items[i].Name;
                     end;
                     NumCols:=FieldDefs.Count;
                end;
                //inserisco le righe
                if not EOF then
                begin
                   First;
                   while not EOF do
                   begin
                        app:=app + '[';
                        for i:=0 to FieldDefs.Count-1 do
                        begin
                             app:= app + '"' + FieldByName(FieldDefs.Items[i].Name).asString + '"';
                             if i0 then
        app:=app;
     GetNumCols:=app;
end;

function TExtZQuery.GetNumRows(): integer;
var
   app: integer;
begin
     app:=NumRows;
     if app>0 then
        app:=app-1;
     GetNumRows:=app;
end;

end.

unit Unit1; 

interface

uses
  SysUtils, Classes, ExtZQuery,
{$IFDEF UseRuntime}
  Ext, ExtPascal, ExtPascalUtils, ExtForm, 
  ExtData, ExtGrid, ExtUtil, ExtAir, ExtDd, 
  ExtLayout, ExtMenu, ExtDirect, ExtState, ExtTree, 
  ExtUxForm; 

type
  {$M+}
  TExtPanel_Tab = TExtPanel; 
  TExtFormTextField_Grid = TExtFormTextField; 
  TExtFormNumberField_Grid = TExtFormNumberField; 
  TExtFormDateField_Grid = TExtFormDateField; 
  TExtFormTimeField_Grid = TExtFormTimeField; 
  TExtFormCheckbox_Grid = TExtFormCheckbox; 
  TExtFormComboBox_Grid = TExtFormComboBox; 
  {$M-}

{$ELSE}
       ExtP_Design_Ctrls, ExtP_Design_Grid, ZConnection, StdCtrls;
{$ENDIF}

type

  { TMyForm }

  TMyForm = class(TExtWindow)
    ExtButton1: TExtButton;
    ExtButton2: TExtButton;
    ExtFormLabel3: TExtFormLabel;
    ExtFormLabel4: TExtFormLabel;
    ExtFormLabel5: TExtFormLabel;
    ExtFormLabel6: TExtFormLabel;
    Txt_Id_Old: TExtFormTextField;
    Txt_Row: TExtFormTextField;
    Txt_Row_Old: TExtFormTextField;
    Txt_App2: TExtFormTextField;
    RowSelect : TExtGridRowSelectionModel;
    Btn_Ins: TExtButton;
    Btn_Del: TExtButton;
    ExtFormLabel1: TExtFormLabel;
    ExtFormLabel2: TExtFormLabel;
    Txt_TotRow: TExtFormTextField;
    Txt_TotCol: TExtFormTextField;
    MyId_Field: TExtFormTextField_Grid;
    MyValue_Field: TExtFormTextField_Grid;
    Txt_Id: TExtFormTextField;
    MiaConn: TExtZQuery;
    MyGrid: TExtGridEditorGridPanel;
    procedure Btn_DelClick;
    procedure ExtButton1Click;
    procedure ExtButton2Click;
    procedure MyGridRowClick(This : TExtGridGridPanel; RowIndex : Integer; E : TExtEventObjectSingleton);
    procedure Btn_InsClick;
    procedure CaricaGriglia;
    procedure MyVersionOfGetCell();
    procedure MyGetCell;
    procedure MyDelete;
    procedure RowSelectOnRowselect(This : TExtGridRowSelectionModel; RowIndex : Integer; R : TExtDataRecord);
    procedure GestisciRigaSelezionata();
    procedure StampaValore;
    procedure MyValue_FieldOnFocus(This : TExtFormField);
    procedure AggiornaValoriRiga();
  public
    constructor Create; 
    procedure Show;
  private
    FlagEvitaLoop: integer;
  end;
implementation

uses
  AppThread; 

procedure TMyForm.Btn_InsClick;
var
   MiaExec: TExtZQuery;
begin
     MiaExec:=TExtZQuery.Create(nil);
     MiaExec.SQL.Text:='insert into MyTable(MyValue)VALUES("");'; //inserisco una riga vuota
     MiaExec.ExecSQL;
     MiaExec.Destroy;
     CaricaGriglia;
     CurrentThread.NumRows:=MiaConn.GetNumRows();
     CurrentThread.NumCols:=MiaConn.GetNumCols();
     Txt_TotRow.SetValue(IntToStr(CurrentThread.NumRows));
     Txt_TotCol.SetValue(IntToStr(CurrentThread.NumCols));
end;

procedure TMyForm.CaricaGriglia;
begin
     {inizio righe di codice per vedere nella griglia quello che ho nel db}
     MiaConn:=TExtZQuery.Create(nil);
     MiaConn.SQL.Text:='select Id, MyValue from MyTable;';
     MiaConn.Open;
     MiaConn.TransformsDatasetToDataStore();
     MyGrid.Reconfigure(MiaConn.DataStore, TExtGridColumnModel(MyGrid.ColModel));
     {fine righe di codice per vedere nella griglia quello che ho nel db}
end;

procedure TMyForm.AggiornaValoriRiga();
var
   MiaExec: TExtZQuery;
begin
     MiaExec:=TExtZQuery.Create(nil);
     MiaExec.SQL.Text:='UPDATE MyTable SET MyValue="' + CurrentThread.Query['NewValue'] + '" WHERE Id=' + CurrentThread.Query['IdRecord'] + ';';
     MiaExec.ExecSQL;
     MiaExec.Destroy;
end;
procedure TMyForm.MyGridRowClick(This : TExtGridGridPanel; RowIndex : Integer; E : TExtEventObjectSingleton);
begin
     //se clicco nella cella
     Ajax(MyVersionOfGetCell,['row',RowIndex,'col','Id']);
end;

procedure TMyForm.Btn_DelClick;
var
   ShowConfig:TExtShowConfig;
begin
     ShowConfig:=TExtShowConfig.Create;
     with ShowConfig do
     begin
          Title:='ATTENZIONE';
          Msg:='Vuoi cancellare la riga selezionata?';
          Icon:=ExtMessageBox.Question;
          Buttons:=ExtMessageBox.YesNoCancel;
          AnimEl:='Id';
          Fn:=Ajax(MyDelete,['ButtonId','%0','IdDaCancellare',Txt_Id.GetValue]);
     end;
     ExtMessageBox.Show(ShowConfig);
     ShowConfig.Free;
end;


procedure TMyForm.ExtButton1Click;
begin
     //stampo l'indice della colonna che voglio leggere ovvero la colonna MyValue
     Ajax(StampaValore,['valore',TExtGridColumnModel(MyGrid.ColModel).FindColumnIndex('MyValue')]);
end;

procedure TMyForm.ExtButton2Click;
var
   DataStore: TExtDataStore;
   DataRecord: TextDataRecord;
begin
     with MyGrid do
     begin
          DataStore:=TExtDataStore.Create();
          DataStore:=TExtDataStore(GetStore);
          DataRecord:=TextDataRecord(DataStore.GetAt(0));

          Ajax(StampaValore,['valore',DataRecord.Get('MyValue')]);
     end;
end;

procedure TMyForm.StampaValore;
begin
     ExtMessageBox.Alert('valore',CurrentThread.Query['valore']);
end;

procedure TMyForm.MyDelete;
var
   MiaExec: TExtZQuery;
   Risultato: string;
begin
     Risultato:=CurrentThread.Query['ButtonId'];

     if StrToInt(CurrentThread.Query['IdDaCancellare'])=0 then
       begin
            with MyGrid do
            begin
                 DataStoreNewValue:=TExtDataStore.Create();
                 DataStoreNewValue:=TExtDataStore(GetStore);
                 DataRecordNewValue:=TextDataRecord(DataStoreNewValue.GetAt(StrToInt(CurrentThread.Query['RowIndexOld'])));

                 Ajax(AggiornaValoriRiga,['Idrecord',CurrentThread.Query['MyDataSetIdOld'],'NewValue',DataRecordNewValue.Get('MyValue')]);
            end;
       end;
end;

procedure TMyForm.Show;
begin
  inherited Show;
  CaricaGriglia;
  CurrentThread.NumRows:=MiaConn.GetNumRows();
  CurrentThread.NumCols:=MiaConn.GetNumCols();
  Txt_TotRow.SetValue(IntToStr(CurrentThread.NumRows));
  Txt_TotCol.SetValue(IntToStr(CurrentThread.NumCols));
  Txt_Id.SetValue('-1');
  Txt_Id_Old.SetValue('-1');
  Txt_Row.SetValue('-1');
  Txt_Row_Old.SetValue('-1');
  Txt_App2.SetValue('-1');
end; 

end.

constructor TMyForm.Create;
begin
  inherited; 
{$IFDEF UseRuntime}
 {$I *.inc}
{$ENDIF}
        {creo l'oggetto TExtGridRowSelectionModel che mi servirà a recuperare le righe selezionate}
        //RowSelect := TExtGridRowSelectionModel.Create(JSObject('singleSelect:true')); {permetto che venga selezionata una sola riga per volta}
        RowSelect := TExtGridRowSelectionModel.Create;
        RowSelect.SingleSelect := True;
        RowSelect.OnRowselect := RowSelectOnRowselect; {assegno l'evento apposito sulla selezione di una riga}

        MyGrid.SelModel := RowSelect; //assegno alla griglia l'oggetto che mi permetterà di lavorare con i dati selezionati
        MyGrid.onRowClick:=MyGridRowClick; //se clicco su una cella della griglia
        MyValue_Field.OnFocus:=MyValue_FieldOnFocus;
end;

procedure TMyForm.Show;
begin
  inherited Show;
  CaricaGriglia;
  CurrentThread.NumRows:=MiaConn.GetNumRows();
  CurrentThread.NumCols:=MiaConn.GetNumCols();
  Txt_TotRow.SetValue(IntToStr(CurrentThread.NumRows));
  Txt_TotCol.SetValue(IntToStr(CurrentThread.NumCols));
  Txt_Id.SetValue('-1');
  Txt_Id_Old.SetValue('-1');
  Txt_Row.SetValue('-1');
  Txt_Row_Old.SetValue('-1');
  Txt_App2.SetValue('-1');
end; 

procedure TMyForm.CaricaGriglia;
begin
     {inizio righe di codice per vedere nella griglia quello che ho nel db}
     MiaConn:=TExtZQuery.Create(nil);
     MiaConn.SQL.Text:='select Id, MyValue from MyTable;';
     MiaConn.Open;
     MiaConn.TransformsDatasetToDataStore();
     MyGrid.Reconfigure(MiaConn.DataStore, TExtGridColumnModel(MyGrid.ColModel));
     {fine righe di codice per vedere nella griglia quello che ho nel db}
end;

Ajax(NomeProcedura,['nomecampo1',valore1,'nomecampo2',valore2,'nomecampoN',valoreN]);
.
.
.
procedure NomeClasse.NomeProcedura();
var
   app1,app2,appN: string;
begin
	app1:=CurrentThread.Query['nomecampo1'];
	app2:=CurrentThread.Query['nomecampo2'];
	appN:=CurrentThread.Query['nomecampoN'];	
	
	.
	.
	.
end;