User:Rocarobin

From Lazarus wiki
Revision as of 10:37, 17 October 2010 by Rocarobin (talk | contribs)
Jump to navigationJump to search

unit Unit1;

{$mode objfpc}{$H+}

interface

uses Classes, SysUtils, sqlite3conn, sqldb, pqconnection, OracleConnection, mysql50conn, IBConnection, db, SdfData, dbf, memds, FileUtil, LResources, Forms, Controls, Graphics, Dialogs, DBGrids, ComCtrls, StdCtrls, DbCtrls, ExtCtrls, Grids, PrintersDlgs, Printers;

type

 { TForm1 }
 TForm1 = class(TForm)
   Button6: TButton;
   Button5: TButton;
   Button3: TButton;
   Button2: TButton;
   Button1: TButton;
   Button7: TButton;
   Button4: TButton;
   Button8: TButton;
   Button9: TButton;
   DBEdit1: TDBEdit;
   DBEdit2: TDBEdit;
   DBEdit3: TDBEdit;
   DBEdit4: TDBEdit;
   DBGrid6: TDBGrid;
   DBGrid5: TDBGrid;
   DBGrid1: TDBGrid;
   DBGrid2: TDBGrid;
   DBGrid3: TDBGrid;
   DBGrid4: TDBGrid;
   DBMemo1: TDBMemo;
   DBMemo2: TDBMemo;
   ImageList1: TImageList;
   Label1: TLabel;
   Label13: TLabel;
   Label2: TLabel;
   Label3: TLabel;
   Label4: TLabel;
   Label5: TLabel;
   Label6: TLabel;
   Label7: TLabel;
   Label8: TLabel;
   Label9: TLabel;
   Label10: TLabel;
   Label12: TLabel;
   PageControl1: TPageControl;
   PrintDialog1: TPrintDialog;
   Shape1: TShape;
   TabLeaders: TTabSheet;
   TabMembers: TTabSheet;
   TabPrints: TTabSheet;
   SQLTransaction1: TSQLTransaction;
   SQLQuery1: TSQLQuery;
   SQLQuery2: TSQLQuery;
   SQLQuery3: TSQLQuery;
   Datasource1: TDatasource;
   Datasource2: TDatasource;
   Datasource3: TDatasource;


   //CHOOSE YOUR SQL_CONNECTION
   SQLite3Connection1: TSQLite3Connection;
   SQLConnector1: TSQLConnector;
   MySQL50Connection1: TMySQL50Connection;
   OracleConnection1: TOracleConnection;
   IBConnection1: TIBConnection;
   PQConnection1: TPQConnection;


   procedure Button8Click(Sender: TObject);
   procedure Button4Click(Sender: TObject);
   procedure Button6Click(Sender: TObject);
   procedure Button5Click(Sender: TObject);
   procedure Button7Click(Sender: TObject);
   procedure Button1Click(Sender: TObject);
   procedure Button3Click(Sender: TObject);
   procedure Button2Click(Sender: TObject);
   procedure Button9Click(Sender: TObject);
   procedure DBGrid3CellClick(Column: TColumn);
   procedure DBGrid3TitleClick(Column: TColumn);
   procedure DBGrid4TitleClick(Column: TColumn);
   procedure FormShow(Sender: TObject);
   procedure TabLeadersShow(Sender: TObject);
   procedure TabMembersShow(Sender: TObject);
   procedure TabPrintsShow(Sender: TObject);


   procedure SQLQuery1AfterPost(DataSet: TDataSet);
   procedure SQLQuery1BeforePost(DataSet: TDataSet);
   procedure SQLQuery3AfterPost(DataSet: TDataSet);
   procedure SQLQuery3BeforePost(DataSet: TDataSet);


 private
   { private declarations }
   function DBGridToggleSort(AFieldName: String; dsGrid: TSQLQuery): boolean;
   function RecExists(tdb:TDatabase; selectfield:string; fromtable: string; whereequals:variant):boolean;
   procedure PrintDbGrid(dbGrid:TdbGrid);
 public
   { public declarations }
 end; 

var

 Form1: TForm1;


implementation

{ TForm1 }


//PRINT dbgrid

function MulDiv(nNumber, nNumerator, nDenominator: Integer): Integer; begin

 Result:=Round(int64(nNumber)*int64(nNumerator)/nDenominator);

end; procedure TForm1.PrintDbGrid(dbGrid:TdbGrid); const

 LeftMargin = 0.05;
 TopMargin = 0.05;
 BottomMargin = 0.05;

var

 i: integer;
 x,y: integer;

begin

 if PrintDialog1.Execute then
   begin
   Printer.BeginDoc;
   Printer.Canvas.Font.Height := 72;
   //PLEASE CHANGE THE X AND Y / and DBGrid1(not set)
   Printer.Canvas.TextOut(1000,100,DBGrid1.SelectedField.Text);
   y := Round(TopMargin*Printer.PageHeight);
   dbGrid.DataSource.DataSet.First;
   while not dbGrid.DataSource.DataSet.Eof do
     begin
     x := Round(LeftMargin*Printer.PageWidth);
     for i := 0 to dbGrid.DataSource.DataSet.FieldCount-1 do
       begin
       printer.Canvas.TextOut(x,y,dbGrid.DataSource.DataSet.Fields[i].AsString);
       x := x + MulDiv(dbGrid.Columns[i].Width,72, dbGrid.Width);
       end;
     dbGrid.DataSource.DataSet.Next;
     y := y + printer.Canvas.TextHeight('A');
     if y > (1-TopMargin-BottomMargin)* Printer.PageHeight then
       begin
       y := Round(TopMargin*Printer.PageHeight);
       Printer.NewPage;
       end;
     end;
   Printer.EndDoc;
   end
   else
   label2.caption := 'NO PRINTER INSTALLED';

end;

//Sorting DBGrid Contents

function TForm1.DBGridToggleSort(AFieldName: String; dsGrid: TSQLQuery): boolean; var

 ix: TIndexDef;

begin

if dsGrid.IndexFieldNames <>  then
begin
  dsGrid.IndexFieldNames := ;
  ix := TIndexDef.Create(dsGrid.IndexDefs, AFieldName, AFieldName, [ixDescending]);
  dsGrid.IndexName := ix.Name;
end
else
begin
  dsGrid.IndexName := ;
  dsGrid.IndexFieldNames := AFieldName;
end;

end;


//check record exist


function TForm1.RecExists(tdb:TDatabase; selectfield:string; fromtable: string; whereequals:variant):boolean;

var qr:TSQLQuery; begin qr:=TSQLQuery.Create(self); qr.DataBase := SQLite3Connection1; qr.Params.CreateParam(ftUnknown,'we',ptUnknown); qr.SQL.Text := 'select ' + selectfield + ' from ' + fromtable + ' where ' + selectfield + ' = :we'; qr.Params.ParamByName('we').AsString := whereequals; qr.open; if NOT qr.IsEmpty then result:=true else result:=false; qr.Params.Clear; qr.Close; end;


procedure TForm1.SQLQuery1BeforePost(DataSet: TDataSet);

begin if RecExists(SQLite3Connection1, 'LEADER_ALIAS_NAME', 'LEADERS', DataSet.FieldValues['LEADER_ALIAS_NAME']) then DataSet.Tag := 1 else DataSet.Tag := 0; end;


procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet);

begin if DataSet.Tag = 0 then begin Sqlquery1.ApplyUpdates; SQLTransaction1.CommitRetaining; DataSet.Tag := 1; Label2.caption := 'NEW LEADER RECORDED'; end else begin Sqlquery1.CancelUpdates; SQLTransaction1.RollbackRetaining; DataSet.Tag := 0; Label2.caption := 'LEADER ALREADY EXISTS'; end; end;


procedure TForm1.SQLQuery3BeforePost(DataSet: TDataSet);

begin if RecExists(SQLite3Connection1, 'MEMBER_NAME', 'MEMBERS', DataSet.FieldValues['MEMBER_NAME']) then DataSet.Tag := 1 else DataSet.Tag := 0; end;

procedure TForm1.SQLQuery3AfterPost(DataSet: TDataSet); begin if DataSet.Tag = 0 then begin Sqlquery3.ApplyUpdates; SQLTransaction1.CommitRetaining; DataSet.Tag := 1; Label2.caption := 'NEW MEMBER RECORDED'; end else begin Sqlquery3.CancelUpdates; SQLTransaction1.RollbackRetaining; DataSet.Tag := 0; Label2.caption := 'MEMBER ALREADY EXISTS'; end; end;


//delete leader procedure TForm1.Button2Click(Sender: TObject); begin

 if SQLQuery1.RecordCount>0 then
 begin
 SQLQuery1.Delete;
 SQLQuery1.ApplyUpdates;
 SQLTransaction1.CommitRetaining;
 end;

end;


//update leader procedure TForm1.Button3Click(Sender: TObject); begin

if SQLQuery1.RecordCount>0 then
 begin
  SQLQuery1.Edit;
  SQLQuery1.Post;
  end;

end;


//newsave leaders procedure TForm1.Button1Click(Sender: TObject); begin if Button1.Caption = 'New' then begin SQLQuery1.Insert; Button1.Caption := 'Save'; Button2.Enabled := false; Button3.Enabled := false; DBGrid1.ReadOnly := True; end else begin if (DBEdit1.Text = ) or (DBEdit2.Text = ) then begin SQLQuery1.Cancel; end; if SQLQuery1.State = dsInsert then begin SQLQuery1.Post; end; Button1.Caption := 'New'; Button2.Enabled := true; Button3.Enabled := true; DBGrid1.ReadOnly := False; end; end;


//newsave member procedure TForm1.Button5Click(Sender: TObject); begin if Button5.Caption = 'New' then begin SQLQuery3.Insert; Button5.Caption := 'Save'; Button6.Enabled := false; Button7.Enabled := false; DBGrid4.ReadOnly := True; end else begin if (DBEdit3.Text = ) or (DBEdit4.Text = ) then begin SQLQuery3.Cancel; end; if SQLQuery3.State = dsInsert then begin SQLQuery3.Post; end; Button5.Caption := 'New'; Button6.Enabled := true; Button7.Enabled := true; DBGrid4.ReadOnly := False; end; end;


//delete a member procedure TForm1.Button6Click(Sender: TObject); begin

 if SQLQuery3.RecordCount>0 then
 begin
 SQLQuery3.Delete;
 SQLQuery3.ApplyUpdates;
 SQLTransaction1.CommitRetaining;
 end;

end;


//update a member procedure TForm1.Button7Click(Sender: TObject); begin

if SQLQuery3.RecordCount>0 then
 begin
  SQLQuery3.Edit;
  SQLQuery3.Post;
 end;

end;


procedure TForm1.DBGrid3CellClick(Column: TColumn); begin DbEdit4.Text := DBGrid3.SelectedField.Text; end;


procedure TForm1.Button4Click(Sender: TObject); begin

 TabMembers.Show;

end;

procedure TForm1.Button8Click(Sender: TObject); begin

 TabLeaders.Show;

end;


procedure TForm1.TabLeadersShow(Sender: TObject); begin label2.Caption := 'Query for Leaders'; end;


procedure TForm1.TabMembersShow(Sender: TObject); begin

 label2.Caption := 'Query for Members';

end;


procedure TForm1.TabPrintsShow(Sender: TObject); begin

  label2.Caption := 'Print Members according to Leader';

end;


procedure TForm1.DBGrid3TitleClick(Column: TColumn); begin

 DBGridToggleSort(Column.FieldName, (Column.Field.DataSet as TSQLQuery));

end;


procedure TForm1.DBGrid4TitleClick(Column: TColumn); begin

 DBGridToggleSort(Column.FieldName, (Column.Field.DataSet as TSQLQuery));

end;


procedure TForm1.Button9Click(Sender: TObject); begin PrintDbGrid(DBGrid6); end;


//(master-detail relationship)

{

   CHOOSE YOUR SQL_CONNECTION
   SQLite3Connection1 for SQLITE
   SQLConnector1 for MSSQL
   MySQL50Connection1 for MYSQL5
   OracleConnection1 for ORACLE
   IBConnection1 for INTERBASE
   PQConnection1 for POSTGRE
   Just replace  < SQLite3Connection1 > to any SQL con you would like
   eq., SQLite3Connection1 to MySQL50Connection1

}


procedure TForm1.FormShow(Sender: TObject); begin

 SQLite3Connection1.Directory := ;
 SQLite3Connection1.DatabaseName := 'candid.db';
 SQLTransaction1.DataBase := SQLite3Connection1;
 SQLTransaction1.Action := caCommit;
 SQLTransaction1.Active := True;
 SQLite3Connection1.Transaction := SQLTransaction1;
 SQLite3Connection1.Connected := True;
 SQLQuery1.DataBase := SQLite3Connection1;
 SQLQuery1.SQL.Text := 'select * from leaders';
 SQLQuery1.Transaction := SQLTransaction1;
 Datasource1.DataSet := SQLQuery1;
 DbEdit1.DataField  := 'LEADER_ALIAS_NAME';
 DbEdit2.DataField  := 'PRECINCT_NUMBER';
 DbMemo1.DataField  := 'LEADER_NOTE';
 DbEdit1.DataSource := Datasource1;
 DbEdit2.DataSource := Datasource1;
 DbMemo1.DataSource := Datasource1;
 DBGrid1.DataSource := Datasource1;
 DBGrid3.DataSource := Datasource1;
 DBGrid5.DataSource := Datasource1;
 SQLQuery1.Open;


 SQLQuery2.DataBase := SQLite3Connection1;
 SQLQuery2.DataSource := Datasource1;
 SQLQuery2.Transaction := SQLTransaction1;
 SQLQuery2.SQL.Text := 'select MEMBER_NAME from MEMBERS where MEMBER_LEADERS_NAME = :LEADER_ALIAS_NAME';
 Datasource2.DataSet := SQLQuery2;
 DBGrid2.DataSource := Datasource2;
 DBGrid6.DataSource := Datasource2;
 SQLQuery2.Open;
 SQLQuery3.DataBase := SQLite3Connection1;
 SQLQuery3.Transaction := SQLTransaction1;
 SQLQuery3.SQL.Text := 'select * from MEMBERS';
 Datasource3.DataSet := SQLQuery3;
 DBGrid4.DataSource := Datasource3;
 DbEdit3.DataField  := 'MEMBER_NAME';
 DbEdit4.DataField  := 'MEMBER_LEADERS_NAME';
 DbMemo2.DataField  := 'MEMBER_NOTE';
 DbEdit3.DataSource := Datasource3;
 DbEdit4.DataSource := Datasource3;
 DbMemo2.DataSource := Datasource3;
 SQLQuery3.Open;

end;



initialization

 {$I unit1.lrs}


end.


THE SOURCE CODE: [1]