unit History;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, StdCtrls, Menus, Buttons, ExtCtrls;

type
  TfrmHistory = class(TForm)
    GroupBox1: TGroupBox;
    cmbSearch: TComboBox;
    txtSearch: TEdit;
    lvSearch: TListView;
    btnClear: TBitBtn;
    Panel1: TPanel;
    dtpSearchDate: TDateTimePicker;
    Timer1: TTimer;

    procedure btnCancelClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure loadHistory();
    procedure vwDetailsClick(Sender: TObject);
    procedure lvSearchDblClick(Sender: TObject);
    procedure View();
    procedure selectCombo();
    procedure Go();
    procedure Clear();
    procedure btnGoClick(Sender: TObject);
    procedure btnClearClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure txtSearchKeyPress(Sender: TObject; var Key: Char);
    procedure cmbSearchClick(Sender: TObject);
    procedure Timer1Timer(Sender: TObject);
    procedure countColumns();
    procedure txtSearchChange(Sender: TObject);
    procedure dtpSearchDateChange(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
     age :string;
     gender :string;
     civilstatus :string;
     address :string;
     remarks :string;
     purpose :string;
     fk:String;
     primary:string;
     count:Integer;
  end;

var
  frmHistory: TfrmHistory;

implementation

//uses DM, Unit1;
uses DM, CollectionDetails, Services, TaxPayerDetails;
{$R *.dfm}

procedure TfrmHistory.btnCancelClick(Sender: TObject);
begin
    close;
end;

procedure TfrmHistory.lvSearchDblClick(Sender: TObject);
begin
       View();
       frmTaxPayerDetails.lbPrimary.Caption:=primary;
end;
procedure TfrmHistory.btnGoClick(Sender: TObject);
begin
      Go();
end;

procedure TfrmHistory.btnClearClick(Sender: TObject);
begin
      loadHistory();
end;

procedure TfrmHistory.vwDetailsClick(Sender: TObject);
begin
      View();
      frmTaxPayerDetails.lbPrimary.Caption:=primary;
end;

procedure TfrmHistory.FormActivate(Sender: TObject);
begin
      //StatusBar1.Panels.Items[0].Text:='Columns: '+IntToStr(count);
end;



//.........................dirty works here
procedure TfrmHistory.countColumns();
begin
      if DModule.TR.InTransaction then
        DModule.TR.Commit;

          DModule.QR.SQL.Clear;
          DModule.QR.SQL.Add('SELECT COUNT(COLID)');
          DModule.QR.SQL.Add('FROM COLLECTIONS');
          DModule.QR.Prepare;
          DModule.QR.Open();
            count:=DModule.QR.Fields.AsInteger[0];
          //StatusBar1.Panels.Items[0].Text:='Columns: '+IntToStr(count);
      if DModule.TR.InTransaction then
        DModule.TR.Commit;
end;

procedure TfrmHistory.Clear();
begin
        cmbSearch.Text:='';
        txtSearch.Text:='';
end;
procedure TfrmHistory.loadHistory();
var newState : TListItem;
begin

    if DModule.TR.InTransaction then
        DModule.TR.Commit;

    DModule.TR.StartTransaction;
    DModule.QR.SQL.Clear;
    DModule.QR.SQL.Add('SELECT a.ColID,a.AmountPaid,a.Tracking_ORNo,a.IssuedDate,b.Description,c.Name,a.UI_ID');
    DModule.QR.SQL.Add('FROM Collections a, Particulars b, TaxPayer c WHERE a.P_NUM=b.P_Num AND a.TP_ID=c.TP_ID ');
    DModule.QR.Prepare;

    DModule.QR.Open();
    lvSearch.Items.BeginUpdate;
    lvSearch.Items.Clear;
    if DModule.QR.Fields.IsNull[0] then Exit;
    while not DModule.QR.Eof do begin
        newState := lvSearch.Items.Add;
        newState.Caption := DModule.QR.Fields.AsString[0];
        newState.SubItems.Add(DModule.QR.Fields.AsString[1]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[2]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[3]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[4]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[5]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[6]);
        DModule.QR.Next;
    end;
    if DModule.TR.InTransaction then
        DModule.TR.Commit;
    lvSearch.Items.EndUpdate;
end;

procedure TfrmHistory.View();
begin
     if lvSearch.ItemFocused <> nil then begin

            //frmTaxPayerDetails.lbpkHide.Caption := lvSearch.ItemFocused.Caption;
            frmTaxPayerDetails.txtName.Text := lvSearch.ItemFocused.SubItems[1];
            if(lvSearch.ItemFocused.SubItems[0] = 'BARANGAY CLEARANCE') then begin
            fk:= lvSearch.ItemFocused.Caption;
                DModule.TR.StartTransaction;
                DModule.QR.SQL.Clear;

                DModule.QR.SQL.Add('SELECT Age,Gender,CivilStatus,Address,Remarks,Purpose,COLID');
                DModule.QR.SQL.Add(' FROM ClearanceDetails where ColID=:A');

                DModule.QR.Params.AsString[0] := fk;

                DModule.QR.Prepare;
                DModule.QR.Open();

                frmTaxPayerDetails.txtAge.Text:= DModule.QR.Fields.AsString[0];
                frmTaxPayerDetails.txtGender.Text := DModule.QR.Fields.AsString[1];
                frmTaxPayerDetails.txtCivilStat.Text := DModule.QR.Fields.AsString[2];
                frmTaxPayerDetails.txtAddress.Text := DModule.QR.Fields.AsString[3];
                frmTaxPayerDetails.txtRemarks.Text := DModule.QR.Fields.AsString[4];
                frmTaxPayerDetails.txtPurpose.Text := DModule.QR.Fields.AsString[5];

                if DModule.TR.InTransaction then
                    DModule.TR.Commit;

                frmTaxPayerDetails.Show;
            end
            else begin
              MessageDlg('No details',mtInformation,[mbOK],0);
            end
    end
    else begin
            MessageDlg('Nothing click',mtInformation,[mbOK],0);
    end
end;

procedure TfrmHistory.selectCombo();
begin
    if (cmbSearch.Text='Date') then begin
      txtSearch.Visible:=False;
      dtpSearchDate.Visible:=True;
      dtpSearchDate.Top:=28;
    end
    else begin
      txtSearch.Visible:=True;
      txtSearch.Top:=28;
      dtpSearchDate.Visible:=False;
    end;
end;

procedure TfrmHistory.Go();
          var newState : TListItem;
begin
     if DModule.TR.InTransaction then
            DModule.TR.Commit;

    if(cmbSearch.Text='Tax Payer') then begin
      DModule.TR.StartTransaction;
      DModule.QR.SQL.Clear;
      DModule.QR.SQL.Add('SELECT a.COLID,a.AMOUNTPAID,a.TRACKING_ORNO,a.ISSUEDDATE,b.Description,c.NAME,a.UI_ID');
      DModule.QR.SQL.Add('FROM COLLECTIONS a,PARTICULARS b, TAXPAYER c WHERE a.P_NUM=b.P_NUM and a.TP_ID=c.TP_ID and c.NAME LIKE (:a)');
      DModule.QR.Params.AsString[0] :=Trim('%'+UpperCase(txtSearch.Text)+'%');
      DModule.QR.Prepare;
      DModule.QR.Open();
        lvSearch.Items.BeginUpdate;
        lvSearch.Items.Clear;

    if DModule.QR.Fields.IsNull[0] then Exit;

   if DModule.QR.Eof then begin
      MessageDlg('No record(s) found',mtError,[mbOK],0);
   end;

    while not DModule.QR.Eof do begin
        newState := lvSearch.Items.Add;
        newState.Caption := DModule.QR.Fields.AsString[0];
        newState.SubItems.Add(DModule.QR.Fields.AsString[1]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[2]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[3]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[4]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[5]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[6]);
        DModule.QR.Next;
    end;
         if DModule.TR.InTransaction then
            DModule.TR.Commit;
        lvSearch.Items.EndUpdate;
    end
//---------------------------------------------------
    else if(cmbSearch.Text='Particulars') then begin
    DModule.TR.StartTransaction;
    DModule.QR.SQL.Clear;
    DModule.QR.SQL.Add('SELECT a.COLID,a.AMOUNTPAID,a.TRACKING_ORNO,a.ISSUEDDATE,b.Description,c.NAME,a.UI_ID');
    DModule.QR.SQL.Add('FROM COLLECTIONS a,PARTICULARS b, TAXPAYER c WHERE a.P_NUM=b.P_NUM and a.TP_ID=c.TP_ID and b.Description LIKE (:a)');
    DModule.QR.Params.AsString[0] :=Trim('%'+UpperCase(txtSearch.Text)+'%');
    DModule.QR.Prepare;
    DModule.QR.Open();
    lvSearch.Items.BeginUpdate;
    lvSearch.Items.Clear;

    if DModule.QR.Fields.IsNull[0] then Exit;
    if DModule.QR.Eof then begin
      MessageDlg('No record(s) found',mtError,[mbOK],0);
   end;

    while not DModule.QR.Eof do begin
        newState := lvSearch.Items.Add;
        newState.Caption := DModule.QR.Fields.AsString[0];
        newState.SubItems.Add(DModule.QR.Fields.AsString[1]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[2]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[3]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[4]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[5]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[6]);
        DModule.QR.Next;
    end;
         if DModule.TR.InTransaction then
            DModule.TR.Commit;
        lvSearch.Items.EndUpdate;
    end
//------------------------------------------------------
    else if(cmbSearch.Text='Tracking No.') then begin
    DModule.TR.StartTransaction;
    DModule.QR.SQL.Clear;
    DModule.QR.SQL.Add('SELECT a.COLID,a.AMOUNTPAID,a.TRACKING_ORNO,a.ISSUEDDATE,b.Description,c.NAME,a.UI_ID');
      DModule.QR.SQL.Add('FROM COLLECTIONS a,PARTICULARS b, TAXPAYER c WHERE a.P_NUM=b.P_NUM and a.TP_ID=c.TP_ID and a.TRACKING_ORNO LIKE (:a)');
    DModule.QR.Params.AsString[0] :=Trim('%'+UpperCase(txtSearch.Text)+'%');
    DModule.QR.Prepare;
    DModule.QR.Open();
    lvSearch.Items.BeginUpdate;
    lvSearch.Items.Clear;

    if DModule.QR.Fields.IsNull[0] then Exit;
    if DModule.QR.Eof then begin
      MessageDlg('No record(s) found',mtError,[mbOK],0);
   end;

    while not DModule.QR.Eof do begin
        newState := lvSearch.Items.Add;
        newState.Caption := DModule.QR.Fields.AsString[0];
        newState.SubItems.Add(DModule.QR.Fields.AsString[1]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[2]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[3]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[4]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[5]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[6]);
        DModule.QR.Next;
    end;
         if DModule.TR.InTransaction then
            DModule.TR.Commit;
        lvSearch.Items.EndUpdate;
    end
{---------------------------------------------------
    else if(cmbSearch.Text='OR No.') then begin
    DModule.TR.StartTransaction;
    DModule.QR.SQL.Clear;
    DModule.QR.SQL.Add('SELECT a.COLID,a.AMOUNTPAID,a.TRACKING_ORNO,a.ISSUEDDATE,b.PARTNAME,c.NAME,a.UI_ID');
      DModule.QR.SQL.Add('FROM COLLECTIONS a,PARTICULARS b, TAXPAYER c WHERE TRACKING_ORNO LIKE (:a)');
    DModule.QR.Params.AsString[0] :=Trim('%'+UpperCase(txtSearch.Text)+'%');
    DModule.QR.Prepare;
    DModule.QR.Open();
    lvSearch.Items.BeginUpdate;
    lvSearch.Items.Clear;

    if DModule.QR.Fields.IsNull[0] then Exit;
    if DModule.QR.Eof then begin
      MessageDlg('Search not found',mtError,[mbOK],0);
   end;

    while not DModule.QR.Eof do begin
        newState := lvSearch.Items.Add;
        newState.Caption := DModule.QR.Fields.AsString[0];
        newState.SubItems.Add(DModule.QR.Fields.AsString[1]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[2]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[3]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[4]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[5]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[6]);
        DModule.QR.Next;
    end;
         if DModule.TR.InTransaction then
            DModule.TR.Commit;
        lvSearch.Items.EndUpdate;
    end }
//-----------------------------------------------
    else if(cmbSearch.Text='Date') then begin

    DModule.TR.StartTransaction;
    DModule.QR.SQL.Clear;
    DModule.QR.SQL.Add('SELECT a.COLID,a.AMOUNTPAID,a.TRACKING_ORNO,a.ISSUEDDATE,b.Description,c.NAME,a.UI_ID');
    DModule.QR.SQL.Add('FROM COLLECTIONS a,PARTICULARS b, TAXPAYER c WHERE a.P_NUM=b.P_NUM and a.TP_ID=c.TP_ID and a.ISSUEDDATE =:a');
    DModule.QR.Params.AsString[0] := DateToStr(dtpSearchDate.DateTime);
    DModule.QR.Prepare;
    DModule.QR.Open();

      lvSearch.Items.BeginUpdate;
      lvSearch.Items.Clear;
          if DModule.QR.Fields.IsNull[0] then Exit;
          if DModule.QR.Eof then begin
              MessageDlg('No record(s) found',mtError,[mbOK],0);
          end;
            while not DModule.QR.Eof do begin
              newState := lvSearch.Items.Add;
              newState.Caption := DModule.QR.Fields.AsString[0];
              newState.SubItems.Add(DModule.QR.Fields.AsString[1]);
              newState.SubItems.Add(DModule.QR.Fields.AsString[2]);
              newState.SubItems.Add(DModule.QR.Fields.AsString[3]);
              newState.SubItems.Add(DModule.QR.Fields.AsString[4]);
              newState.SubItems.Add(DModule.QR.Fields.AsString[5]);
              newState.SubItems.Add(DModule.QR.Fields.AsString[6]);
              DModule.QR.Next;
           end;
         if DModule.TR.InTransaction then
            DModule.TR.Commit;
             lvSearch.Items.EndUpdate;
    end
//--------------------------------------------------
    else if(cmbSearch.Text='All') then begin
    loadHistory();
    {DModule.TR.StartTransaction;
    DModule.QR.SQL.Clear;
    DModule.QR.SQL.Add('SELECT COLID, PARTNAME,NAME, AMOUNTPAID,TRACKING_ORNO,ISSUEDDATE, ISSUEDBY');
    DModule.QR.SQL.Add('FROM COLLECTIONS PARTNAME OR NAME OR TRACKING_ORNO LIKE (:a)');
    DModule.QR.Params.AsString[0] :=Trim('%'+UpperCase(txtSearch.Text)+'%');
    DModule.QR.Prepare;
    DModule.QR.Open();
    lvSearch.Items.BeginUpdate;
    lvSearch.Items.Clear;

    if DModule.QR.Fields.IsNull[0] then Exit;
    while not DModule.QR.Eof do begin
        newState := lvSearch.Items.Add;
        newState.Caption := DModule.QR.Fields.AsString[0];
        newState.SubItems.Add(DModule.QR.Fields.AsString[1]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[2]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[3]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[4]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[5]);
        newState.SubItems.Add(DModule.QR.Fields.AsString[6]);
        DModule.QR.Next;
    end;
         if DModule.TR.InTransaction then
            DModule.TR.Commit;
        lvSearch.Items.EndUpdate;   }
    end
end;

procedure TfrmHistory.FormShow(Sender: TObject);
begin
      //loadHistory();
      dtpSearchDate.Visible:=False;
      dtpSearchDate.DateTime:=Now;
      //StatusBar1.Panels.Items[0].Text:='Columns: '+IntToStr(count);
      //StatusBar1.Panels.Items[2].Text:='Time: '+TimeToStr(Now);
end;

procedure TfrmHistory.txtSearchKeyPress(Sender: TObject; var Key: Char);
begin
      {if Key = #13 then begin
        SelectNext(Sender as tWinControl, True, True );
      if Sender = txtSearch then
        btnGo.Click; // let the button click itself
        Key := #0;
      end;}
end;

procedure TfrmHistory.cmbSearchClick(Sender: TObject);
begin
    selectCombo();
end;

procedure TfrmHistory.Timer1Timer(Sender: TObject);
begin
    //StatusBar1.Panels.Items[2].Text:='Time: '+TimeToStr(Now);
end;

procedure TfrmHistory.txtSearchChange(Sender: TObject);
begin
      Go();
end;

procedure TfrmHistory.dtpSearchDateChange(Sender: TObject);
begin
      Go();
end;

end.
