unit Services;

interface

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

type
  TfrmServices = class(TForm)
    gbFront: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    txtAmount: TEdit;
    Label3: TLabel;
    txtORNum: TEdit;
    Label4: TLabel;
    txtTaxPayer: TEdit;
    cmbParticular: TComboBox;
    gbCTC: TGroupBox;
    txtCTCNum: TEdit;
    Label5: TLabel;
    btnCancel: TBitBtn;
    btnProceed: TBitBtn;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    txtIssuedDate: TEdit;
    Label9: TLabel;
    mmoIssuedAt: TMemo;
    Label10: TLabel;
    mmoAddress: TMemo;
    tbxDateMonth: TEdit;
    tbxYear: TEdit;
    txtCTCExpiryDate: TEdit;
    txtYear: TEdit;
    lb_tpID: TLabel;
    lb_part_p_num: TLabel;

    procedure Proceed();
    procedure Save();
    procedure Clear();
    procedure PartID();
    procedure combobox();
    procedure FormActivate(Sender: TObject);
    procedure cmbParticularClick(Sender: TObject);
    procedure comboParticular();
    procedure AutoPrime();
    procedure displayAmount();
    procedure displayTName();
    procedure displayTrackNum();
    procedure SaveCTC();
    procedure CheckCTC();
    procedure CheckExistingTrackNo();
    procedure CheckExpiry();
    procedure ReportPrint();
    procedure txtTaxPayerKeyPress(Sender: TObject; var Key: Char);
    procedure cmbParticularKeyPress(Sender: TObject; var Key: Char);
    procedure txtAmountKeyPress(Sender: TObject; var Key: Char);
    procedure txtORNumKeyPress(Sender: TObject; var Key: Char);
    procedure txtCTCNumKeyPress(Sender: TObject; var Key: Char);
    procedure btnCancelClick(Sender: TObject);
    procedure btnProceedClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnEditNumClick(Sender: TObject);
    procedure UpdateTrackNum();
    procedure txtTaxPayerChange(Sender: TObject);
    procedure GetTPName();
    procedure txtTaxPayerKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure SearchTaxPayer();
    procedure cmbParticularChange(Sender: TObject);

  private
    { Private declarations }
  public
    { Public declarations }
    name1:string;
    amount:string; //tag pila ang particulars
    primary:string; //primary key sa taxpayer nga ng.lukat
    name:string;//name sa taxpayer
    sample:integer;
    autoIncrementTrackNum:Integer;
    ctc:string;
    pk:string;
    CheckTracNo:string;
    PID:string;
    UID:string;
    Pnum:string;
    CTCExpiry:string;
     day:string;
     month:string;
     year:string;
     dayofweek:string;
     date:string;
  	expiry : TDate;
	  yearnow : String;
    track_from: Integer;
    track_to:Integer;
    tracking_fromIncre:Integer;
    track_type:String;
    taxpayer:string;
    taxpayerID:string;
    taxpayerGender:string;
    taxpayerCStatus:String;
    taxpayerAge:Integer;
    taxpayerAddress:String;
    part_p_num:integer;
  end;

var
  frmServices: TfrmServices;
  tname :String;


implementation

uses CollectionDetails, DM, LogIn, Main, jvuiblib, Math, SetTracking,
  taxPayerForm;

{$R *.dfm}

procedure TfrmServices.btnCancelClick(Sender: TObject);
begin
      if MessageDlg('Are you sure you want to cancel?',mtConfirmation,[mbYes,mbNo], 0)= mrYes then begin
        Close;
        Clear();
      end
end;

procedure TfrmServices.btnProceedClick(Sender: TObject);
begin
        if(btnProceed.Caption='&Proceed') then begin
            Proceed();
        end
        else if(btnProceed.Caption='&Save') then begin
           Save();
        end
        else if(btnProceed.Caption='&Save CTC') then begin
           SaveCTC();
        end;

end;

procedure TfrmServices.FormActivate(Sender: TObject);
var s: String;
    d: TDate;

begin
    tbxDateMonth.ReadOnly := True;
    txtORNum.Enabled := True;
    d:=Now;
    s:=FormatDateTime('yyyy',d);
    tbxYear.text := s;
end;


procedure TfrmServices.cmbParticularClick(Sender: TObject);
begin
    comboParticular();
end;


//.............dity works here
procedure TfrmServices.Proceed();
  var yr:String;
begin
  yr:=FormatDateTime('yyyy',Now);
  PartID();
  CheckExistingTrackNo();
  CheckCTC();
  AutoPrime();
  displayTName();
  SearchTaxPayer();
  displayTrackNum();

        if(txtCTCNum.Text ='') then begin
          MessageDlg('CTC number required',mtError,[mbOK],0);
          txtCTCNum.SetFocus;
        end
        else if(trim(UpperCase(txtCTCNum.Text))<>ctc)  then begin
            MessageDlg('Invalid CTC number or number does not exist.'+#13#10+'Please check it again.',mtError,[mbOK],0);
            txtCTCNum.SetFocus;
        end
        else if(StrToInt(yr) > StrToInt(txtYear.Text) ) then begin
            MessageDlg('CTC had already expired.'+#13#10+'Please acquire again.',mtError,[mbOK],0);
        end
        else if(txtTaxPayer.Text='') then begin
           MessageDlg('Field(s) required',mtInformation,[mbOK],0);
           txtTaxPayer.SetFocus;
        end
        else if(cmbParticular.Text='') then begin
           MessageDlg('Field(s) required',mtInformation,[mbOK],0);
           cmbParticular.SetFocus;
        end
        else if(txtORNum.Text='')then begin
              MessageDlg('Field(s) required',mtInformation,[mbOK],0);
              txtORNum.SetFocus;
        end
        else if(trim(UpperCase(txtORNum.Text)) = CheckTracNo)then begin
            MessageDlg('Tracking number already exist',mtError,[mbOK],0);
            txtORNum.SetFocus;
        end
        else if(mmoAddress.Text='') then begin
           MessageDlg('Field(s) required',mtInformation,[mbOK],0);
           mmoAddress.SetFocus;
        end
        else if(taxpayer <> Trim(UpperCase(txtTaxPayer.Text)))then begin
           MessageDlg('Name not found in the database.'+#13#10+'Please register his/her name.',mtInformation,[mbOK],0);
           frmtaxPayer.ShowModal;
        end
        else if(track_to < StrToInt(Trim(UpperCase(txtORNum.Text))))then begin
           MessageDlg('Tracking number already cunsumed.'+#13#10+'Please update the tracking number.',mtInformation,[mbOK],0);
           txtORNum.SetFocus;
        end
        
    else begin

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

        DModule.QR.SQL.Clear();
        DModule.QR.SQL.Add('INSERT INTO Collections');
        DModule.QR.SQL.Add('(ColID,Tracking_ORNo,AmountPaid,IssuedDate,ISSUEDAT,P_Num,UI_ID,TP_ID )');
        DModule.QR.SQL.Add(' VALUES (:a, :b, :c, :d, :e, :f, :g, :h) ');

        DModule.QR.Params.asInteger[0] :=sample;
        DModule.QR.Params.AsString[1] := trim(UpperCase(txtORNum.Text));
        DModule.QR.Params.AsInteger[2] := StrToInt(txtAmount.Text);
        DModule.QR.Params.AsString[3] := DateToStr(now());
        DModule.QR.Params.AsString[4] := Trim(UpperCase(mmoAddress.Text));
        DModule.QR.Params.AsString[5] := Pnum;
        DModule.QR.Params.AsString[6] := frmMain.pk;
        DModule.QR.Params.AsString[7] := lb_tpID.Caption;
        DModule.QR.Prepare;
        DModule.QR.open();

           if DModule.TR.InTransaction then
          DModule.TR.Commit;
            UpdateTrackNum();
            name1:=txtTaxPayer.Text;
            //kuhaon ang id number sa taxpayer
            frmTPDetails.txtName.Text:= name1;
            frmTPDetails.lb_tpID.Caption:=lb_tpID.Caption;
            frmTPDetails.lb_ctcnum.Caption:=txtCTCNum.Text;
            frmTPDetails.txtAge.Text:=IntToStr(taxpayerAge);
            frmTPDetails.mmoAddress.Text:=taxpayerAddress;
            frmTPDetails.cmbGender.Text:=taxpayerGender;
            frmTPDetails.cmbCivilStatus.Text:=taxpayerCStatus;
            frmTPDetails.ShowModal;
       end
end;

procedure TfrmServices.SaveCTC();
begin
  PartID();
  CheckExistingTrackNo();
  AutoPrime();
  SearchTaxPayer();
  displayTrackNum();

        if(txtTaxPayer.Text='') then begin
           MessageDlg('Field(s) required',mtError,[mbOK],0);
           txtTaxPayer.SetFocus;
        end
        else if(cmbParticular.Text='') then begin
            MessageDlg('Field(s) required',mtError,[mbOK],0);
            cmbParticular.SetFocus;
        end
        else if(txtORNum.Text='')then begin
              MessageDlg('Field(s) required',mtError,[mbOK],0);
              txtORNum.SetFocus;
        end
        else if(CheckTracNo = UpperCase(trim(txtORNum.Text)))then begin
              MessageDlg('Tracking number already exist',mtError,[mbOK],0);
               txtORNum.SetFocus;
        end
        else if(mmoAddress.Text='')then begin
              MessageDlg('Field(s) required',mtError,[mbOK],0);
              mmoAddress.SetFocus;
        end
        else if(taxpayer <> Trim(UpperCase(txtTaxPayer.Text)))then begin
           MessageDlg('Name not found in the database.'+#13#10+'Please register his/her name.',mtInformation,[mbOK],0);
           frmtaxPayer.ShowModal;
        end
        else if(track_to < StrToInt(Trim(UpperCase(txtORNum.Text))))then begin
           MessageDlg('Tracking number already cunsumed.'+#13#10+'Please update the tracking number.',mtInformation,[mbOK],0);
           txtORNum.SetFocus;
        end

    else begin

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

        DModule.QR.SQL.Clear();
        DModule.QR.SQL.Add('INSERT INTO Collections');
        DModule.QR.SQL.Add('(ColID,Tracking_ORNo,AmountPaid,IssuedDate,ISSUEDAT,CTCEXPIRYDATE,EXPIRYYEAR,P_NUM,UI_ID,TP_ID )');
        DModule.QR.SQL.Add(' VALUES (:a, :b, :c, :d, :e, :f, :g, :h, :i, :j) ');

        DModule.QR.Params.AsInteger[0] :=sample;
        DModule.QR.Params.AsString[1] := trim(UpperCase(txtORNum.Text));
        DModule.QR.Params.AsString[2] := trim(txtAmount.Text);
        DModule.QR.Params.AsString[3] := DateToStr(now());
        DModule.QR.Params.AsString[4] := Trim(UpperCase(mmoAddress.Text));
        DModule.QR.Params.AsString[5] := Trim(tbxDateMonth.Text);
        DModule.QR.Params.AsString[6] := Trim(tbxYear.Text);
        DModule.QR.Params.AsString[7] := Pnum;
        DModule.QR.Params.AsString[8] := frmMain.pk;
        DModule.QR.Params.AsString[9] := lb_tpID.Caption;
        DModule.QR.Prepare;
        DModule.QR.open();

           if DModule.TR.InTransaction then
          DModule.TR.Commit;
         MessageDlg('Successfully saved',mtConfirmation,[mbOK],0);
          Clear;

          if MessageDlg('Do you want to add transaction?',mtConfirmation,[mbYes,mbNo],0)=mrYes then begin
          end
          else
              Close;
       end
end;

procedure TfrmServices.Save();
begin

PartID();
CheckExistingTrackNo();
AutoPrime();
SearchTaxPayer();
displayTrackNum();

        if(cmbParticular.Text='') then begin
            MessageDlg('Field(s) required',mtError,[mbOK],0);
            cmbParticular.SetFocus;
        end

        else if(txtORNum.Text='')then begin
              MessageDlg('Field(s) required',mtError,[mbOK],0);
              txtORNum.SetFocus;
        end
        else if(CheckTracNo = Trim(UpperCase(txtORNum.Text)))then begin
              MessageDlg('Tracking number already exist',mtError,[mbOK],0);
              txtORNum.SetFocus;
        end
        else if(mmoAddress.Text = '')then begin
              MessageDlg('Field(s) required',mtError,[mbOK],0);
              mmoAddress.SetFocus;
        end
        else if(taxpayer <> Trim(UpperCase(txtTaxPayer.Text)))then begin
           MessageDlg('Name not found in the database.'+#13#10+'Please register his/her name.',mtInformation,[mbOK],0);
           frmtaxPayer.ShowModal;
        end
        else if(track_to < StrToInt(Trim(UpperCase(txtORNum.Text))))then begin
           MessageDlg('Tracking number already cunsumed.'+#13#10+'Please update the tracking number.',mtInformation,[mbOK],0);
           txtORNum.SetFocus;
        end

    else begin
       if MessageDlg('Do you want to save transaction?',mtConfirmation,[mbYes,mbNo],0)=mrYes then begin
         if DModule.TR.InTransaction then
          DModule.TR.Commit;

        DModule.QR.SQL.Clear();
        DModule.QR.SQL.Add('INSERT INTO Collections');
        DModule.QR.SQL.Add('(ColID,Tracking_ORNo,AmountPaid,IssuedDate,ISSUEDAT,P_NUM,UI_ID,TP_ID )');
        DModule.QR.SQL.Add(' VALUES (:a, :b, :c, :d, :e, :f, :g, :h) ');

        DModule.QR.Params.AsInteger[0] :=sample;
        DModule.QR.Params.AsString[1] := trim(UpperCase(txtORNum.Text));
        DModule.QR.Params.AsString[2] := trim(txtAmount.Text);
        DModule.QR.Params.AsString[3] := DateToStr(now());
        DModule.QR.Params.AsString[4] := Trim(UpperCase(mmoAddress.Text));
        DModule.QR.Params.AsString[5] := lb_part_p_num.Caption;
        DModule.QR.Params.AsString[6] := frmMain.pk;
        DModule.QR.Params.AsString[7] := lb_tpID.Caption;
        DModule.QR.Prepare;
        DModule.QR.open();

        if DModule.TR.InTransaction then
          DModule.TR.Commit;
              MessageDlg('Successfully saved',mtConfirmation,[mbOK],0);
              UpdateTrackNum();
          if (cmbParticular.Text = 'DOCUMENTARY STAMP TAX')then begin
              txtORNum.Clear;
          end
          else
            Clear;
           if MessageDlg('Do you want to add transaction?',mtConfirmation,[mbYes,mbNo],0)=mrYes then begin
          end
          else
              Close;
        end
    end
end;

procedure TfrmServices.UpdateTrackNum();
begin
         if DModule.TR.InTransaction then
          DModule.TR.Commit;

        DModule.QR.SQL.Clear;
            DModule.QR.SQL.Add('UPDATE SETTRACKINGNUM a SET TRACKNUM_FROM=:A WHERE P_NUM=:c');
            DModule.QR.Params.AsString[0] := Trim(txtORNum.Text);
            DModule.QR.Params.AsString[1] := lb_part_p_num.Caption;
            DModule.QR.Prepare;
            DModule.QR.Open();

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

procedure TfrmServices.Clear();
begin
    txtTaxPayer.Clear;
    cmbParticular.ItemIndex:=-1;
    txtAmount.text:='';
    txtORNum.Clear;
    txtCTCNum.Clear;
    txtIssuedDate.Clear;
    mmoIssuedAt.Clear;
end;

procedure TfrmServices.CheckCTC();
begin
     If DModule.TR.InTransaction then
       DModule.TR.Commit;

        DModule.TR.StartTransaction;
        DModule.QR.SQL.Clear();
        DModule.QR.SQL.Add('SELECT a.Tracking_ORNO,a.ISSUEDDATE,a.ISSUEDAT,a.CTCEXPIRYDATE,a.EXPIRYYEAR,b.TP_ID,b.NAME');
        DModule.QR.SQL.Add('FROM Collections a, Taxpayer b where a.TP_ID=b.TP_ID AND Tracking_ORNo=:a');
           DModule.QR.Params.AsString[0]:=trim(UpperCase(txtCTCNum.Text));
        DModule.QR.Prepare;
        DModule.QR.Open();

          if DModule.QR.Fields.IsNull[0] then Exit;
          while not DModule.QR.Eof do begin
                  ctc:= DModule.QR.Fields.AsString[1];
                  DModule.QR.Next;
              end;

          ctc:= DModule.QR.Fields.AsString[0];
          txtIssuedDate.Text := DModule.QR.Fields.AsString[1];
          mmoIssuedAt.Text := DModule.QR.Fields.AsString[2];
          txtCTCExpiryDate.Text := DModule.QR.Fields.AsString[3];
          txtYear.Text := DModule.QR.Fields.AsString[4];
           lb_tpID.Caption:=DModule.QR.Fields.AsString[5];
          txtTaxPayer.Text:=DModule.QR.Fields.AsString[6];
        if DModule.TR.InTransaction then
          DModule.TR.Commit;
end;

procedure TfrmServices.CheckExistingTrackNo();
begin
    If DModule.TR.InTransaction then
       DModule.TR.Commit;

        DModule.QR.SQL.Clear();
        DModule.QR.SQL.Add(' SELECT * ');
        DModule.QR.SQL.Add('FROM COLLECTIONS where TRACKING_ORNO=:a');
            DModule.QR.Params.AsString[0]:=trim(UpperCase(txtORNum.Text));
        DModule.QR.Prepare;
        DModule.QR.open();

       CheckTracNo := DModule.QR.Fields.AsString[1];
        if DModule.TR.InTransaction then
          DModule.TR.Commit;
end;

procedure TfrmServices.combobox();
begin

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

    DModule.TR.StartTransaction;
    DModule.QR.SQL.Clear();
    DModule.QR.SQL.Add('SELECT DESCRIPTION');
    DModule.QR.SQL.Add('FROM PARTICULARS');
    DModule.QR.Prepare;
    DModule.QR.Open();

    cmbParticular.Items.BeginUpdate;
    cmbParticular.Items.Clear;
    if DModule.QR.Fields.IsNull[0] then Exit;
    while not DModule.QR.Eof do begin
        cmbParticular.Items.Add(DModule.QR.Fields.AsString[0]);
        DModule.QR.Next;
        end;
    if DModule.TR.InTransaction then
        DModule.TR.Commit;
    cmbParticular.Items.EndUpdate;

     PartID();
     displayAmount();
     displayTrackNum();
end;

procedure TfrmServices.PartID();
begin
      if DModule.TR.InTransaction then
        DModule.TR.Commit;

        DModule.QR.SQL.Clear();
        DModule.QR.SQL.Add('SELECT *');
        DModule.QR.SQL.Add('FROM Particulars where Description=:a');
           DModule.QR.Params.AsString[0]:=cmbParticular.Text;
        DModule.QR.Prepare;
        DModule.QR.open();

        Pnum:= DModule.QR.Fields.AsString[0];

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

procedure TfrmServices.displayTrackNum();
begin
      if DModule.TR.InTransaction then
        DModule.TR.Commit;

          DModule.QR.SQL.Clear();
          DModule.QR.SQL.Add(' SELECT * ');
          DModule.QR.SQL.Add('FROM SETTRACKINGNUM a, PARTICULARS b where a.P_NUM=b.P_NUM and Description=:a');
            DModule.QR.Params.AsString[0]:=cmbParticular.Text;
          DModule.QR.Prepare;
          DModule.QR.open();

          track_from:=DModule.QR.Fields.AsInteger[2];
          track_to:=DModule.QR.Fields.AsInteger[3];
          txtORNum.Text :=IntToStr(track_from + 1);

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

procedure TfrmServices.comboParticular();
begin
       if(cmbParticular.Text='BARANGAY CLEARANCE') then begin
          frmServices.Height:=550;
          frmServices.Top:=75;
          btnProceed.Caption:='&Proceed';
          Label3.Caption := 'Tracking Number';
          Label3.Top:=166;
          Label6.Visible:=False;
          Label10.Top:=220;
          mmoAddress.Top:=215;
          tbxDateMonth.Visible:=False;
          tbxYear.Visible:=False;
          gbCTC.Enabled:=True;
          txtCTCNum.Enabled:=true;
          txtORNum.Top:=160;
          btnProceed.Top := 465;
          btnCancel.Top :=465;
          gbFront.top :=152;
          gbFront.Height := 300;
          gbCTC.Top :=8;
          gbCTC.Visible := True;
          displayAmount();
          displayTrackNum();
        end
        else if (cmbParticular.Text='CEDULA') then begin
          displayAmount();
            frmServices.Height:=430;
            Label3.Caption := 'CTC Number:';
            Label6.Visible:=True;
            label6.Top:=166;
            tbxDateMonth.Visible:=True;
            tbxYear.Visible:=True;
            tbxDateMonth.Top:=160;
            tbxYear.Top:=160;
            Label3.Top:=215;
            txtORNum.Top:=205;
            Label10.Top:=264;
            mmoAddress.Top:=248;
            txtORNum.ReadOnly:=False;
            btnProceed.Caption:='&Save CTC';
            gbFront.Top:=8;
            gbFront.Height:=321;
            btnProceed.Top := 340;
            btnCancel.Top := 340;

        end
        else begin
            btnProceed.Caption:='&Save';
            frmServices.Height:=400;
            gbCTC.Top:=8;
            gbFront.BringToFront;
            gbFront.Top:=8;
            gbFront.Height:=290;
            btnCancel.Top:= 310;
            btnProceed.Top:=310;
            Label6.Visible:=False;
            tbxDateMonth.Visible:=False;
            tbxYear.Visible:=False;
            label3.Top:=166;
            txtORNum.Top:=160;
            label10.Top:=216;
            mmoAddress.Top:=208;
            displayAmount();
            displayTrackNum();
        end ;
       PartID();
end;


procedure TfrmServices.displayAmount() ;
begin
      If DModule.TR.InTransaction then
       DModule.TR.Commit;

        DModule.QR.SQL.Clear();
        DModule.QR.SQL.Add(' SELECT * ');
        DModule.QR.SQL.Add('FROM Particulars where Description=:a');
            DModule.QR.Params.AsString[0]:=cmbParticular.Text;
        DModule.QR.Prepare;
        DModule.QR.open();

       txtAmount.Text := DModule.QR.Fields.AsString[2];
        if DModule.TR.InTransaction then
          DModule.TR.Commit;
end;


procedure TfrmServices.displayTName();
begin
AutoPrime();
    if DModule.TR.InTransaction then
    DModule.TR.Commit;

        DModule.QR.SQL.Clear();
        DModule.QR.SQL.Add('SELECT *');
        DModule.QR.SQL.Add('FROM Collections where Tracking_ORNo=:a');
        DModule.QR.Params.AsString[0]:= trim(UpperCase(txtORNum.Text));
        DModule.QR.Prepare;
        DModule.QR.Open();
            pk := DModule.QR.Fields.AsString[0];
            //txtTaxpayer.text:=DModule.QR.Fields.AsString[];
        if DModule.TR.InTransaction then
           DModule.TR.Commit;
end;

procedure TfrmServices.CheckExpiry();
begin
       if DModule.TR.InTransaction then
            DModule.TR.Commit;

    DModule.TR.StartTransaction;
    DModule.QR.SQL.Clear;
    DModule.QR.SQL.Add('SELECT * ');
    DModule.QR.SQL.Add('FROM COLLECTIONS WHERE CTCEXPIRYDATE =:a');
        DModule.QR.Params.AsString[0] := trim(UpperCase(txtCTCNum.Text));
    DModule.QR.Prepare;
    DModule.QR.Open();

    if DModule.QR.Fields.IsNull[0] then Exit;
              while not DModule.QR.Eof do begin
              ctc:= DModule.QR.Fields.AsString[5];
              DModule.QR.Next;
          end;
end;

procedure TfrmServices.ReportPrint();
begin
     DModule.rep.Filename := 'reports\brgyclearance.rep';
     DModule.rep.Preview := True;
     DModule.rep.Execute();
end;

procedure TfrmServices.AutoPrime();
    var a:integer;
begin
a:=1;
 if DModule.TR.InTransaction then
        DModule.TR.Commit;
    DModule.QR.SQL.Clear();
    DModule.QR.SQL.Add('SELECT ColID');
    DModule.QR.SQL.Add('FROM COLLECTIONS');
    DModule.QR.Prepare;
    DModule.QR.Open();
   if DModule.QR.Fields.Eof then
    sample:=a
      else begin
       DModule.QR.SQL.Clear;
        DModule.QR.SQL.Add('SELECT MAX(ColID) FROM COLLECTIONS');
        DModule.QR.Prepare;
        DModule.QR.Open();
        sample := DModule.QR.Fields.AsInteger[0] + 1;
        end;
         if DModule.TR.InTransaction then
       DModule.TR.Commit;
end;


procedure TfrmServices.txtTaxPayerKeyPress(Sender: TObject; var Key: Char);
begin
       if Key = #13 then begin
        SelectNext(Sender as tWinControl, True, True );
      if Sender = txtTaxPayer then
        btnProceed.Click ; // let the button click itself
        Key := #0;
      end;
end;

procedure TfrmServices.cmbParticularKeyPress(Sender: TObject;
  var Key: Char);
begin
      if Key = #13 then begin
        SelectNext(Sender as tWinControl, True, True );
      if Sender = cmbParticular then
        btnProceed.Click; // let the button click itself
        Key := #0;
      end;
end;

procedure TfrmServices.txtAmountKeyPress(Sender: TObject; var Key: Char);
begin
      if Key = #13 then begin
        SelectNext(Sender as tWinControl, True, True );
      if Sender = txtAmount then
        btnProceed.Click; // let the button click itself
        Key := #0;
      end;

      if key in['0'..'9']+['+']+['-'..'.']+[#8]then
        begin
          //dec point
          if key = #46 then
            if Pos('.',txtAmount.text)<> 0 then begin
              key:=#0;
              windows.Beep(1000,200);
        end;
      
      if key =#8 then begin
          txtAmount.Text:=Copy(txtAmount.Text,1,length(txtAmount.Text)-1);
          txtAmount.selStart:=length(txtAmount.Text);
          key:=#0;
          end;
      end                                                                                                                   
      else begin
          key:=#0;
          windows.Beep(1000,200);
      end;
end;

procedure TfrmServices.txtORNumKeyPress(Sender: TObject; var Key: Char);
begin
      if Key = #13 then begin
        SelectNext(Sender as tWinControl, True, True );
      if Sender = txtORNum then
        btnProceed.Click; // let the button click itself
        Key := #0;
      end;
end;

procedure TfrmServices.txtCTCNumKeyPress(Sender: TObject; var Key: Char);
begin
      if Key = #13 then begin
        SelectNext(Sender as tWinControl, True, True );
      if Sender = txtCTCNum then
        btnProceed.Click; // let the button click itself
        Key := #0;
      end;
end;



procedure TfrmServices.FormShow(Sender: TObject);
begin
    frmMain.connectDb;
      btnProceed.Caption:='&Save';
      frmServices.Height:=400;
      gbCTC.Top:=8;
      gbFront.BringToFront;
      gbFront.Top:=8;
      gbFront.Height:=290;
      btnCancel.Top:= 310;
      btnProceed.Top:=310;
      Label6.Visible:=False;
      tbxDateMonth.Visible:=False;
      tbxYear.Visible:=False;
      label3.Top:=166;
      txtORNum.Top:=160;
      label10.Top:=216;
      mmoAddress.Top:=208;

    combobox;
    Refresh();
      Clear;
end;

procedure TfrmServices.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
      Clear();
end;

procedure TfrmServices.btnEditNumClick(Sender: TObject);
begin
    if(txtORNum.Enabled = False) then begin
    txtORNum.Enabled := True;
    end
    else begin
    txtORNum.Enabled := False;
    end
end;

procedure TfrmServices.txtTaxPayerChange(Sender: TObject);
begin
      GetTPName();
end;
procedure TfrmServices.GetTPName();
begin
      //Get Taxpayer'name
     { if DModule.TR.InTransaction then
                DModule.TR.Commit;
       DModule.TR.InTransaction;
       DModule.QR.SQL.Clear();
       DModule.QR.SQL.Add('SELECT * FROM TAXPAYER where NAME like(:a)');
          DModule.QR.Params.AsString[0] :=Trim('%'+UpperCase(txtTaxPayer.Text)+'%');
       DModule.QR.Prepare;
       DModule.QR.Open();

    txtTaxPayer.Text.BeginUpdate;
    txtTaxPayer.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
        txtTaxPayer.Text(DModule.QR.Fields.AsString[0])
        DModule.QR.Next;
    end;
    if DModule.TR.InTransaction then
        DModule.TR.Commit;
    txtTaxPayer.Items.EndUpdate;
  end }
end;

procedure TfrmServices.txtTaxPayerKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
         GetTPName();
end;

procedure TfrmServices.SearchTaxPayer();
begin
     if DModule.TR.InTransaction then
        DModule.TR.Commit;

        DModule.QR.SQL.Clear();
        DModule.QR.SQL.Add('SELECT *');
        DModule.QR.SQL.Add('FROM TaxPayer where Name=:a');
        DModule.QR.Params.AsString[0]:= trim(UpperCase(txtTaxPayer.Text));
        DModule.QR.Prepare;
        DModule.QR.Open();
            taxpayerID := DModule.QR.Fields.AsString[0];
            taxpayer := DModule.QR.Fields.AsString[1];
            taxpayerGender:=DModule.QR.Fields.AsString[2];
            taxpayerCStatus:=DModule.QR.Fields.AsString[3];
            taxpayerAge:=DModule.QR.Fields.AsInteger[5];
            taxpayerAddress:=DModule.QR.Fields.AsString[6];


            lb_tpID.Caption:=taxpayerID;
    if DModule.TR.InTransaction then
           DModule.TR.Commit;
end;

procedure TfrmServices.cmbParticularChange(Sender: TObject);
begin
      if DModule.TR.InTransaction then
        DModule.TR.Commit;

        DModule.QR.SQL.Clear();
        DModule.QR.SQL.Add('SELECT P_NUM');
        DModule.QR.SQL.Add('FROM Particulars where Description=:a');
           DModule.QR.Params.AsString[0]:=cmbParticular.Text;
        DModule.QR.Prepare;
        DModule.QR.open();
          part_p_num:= DModule.QR.Fields.AsInteger[0];
          lb_part_p_num.Caption:=IntToStr(part_p_num);
      if DModule.TR.InTransaction then
          DModule.TR.Commit;
end;

end.



