unit unitOcc;

interface

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

type
  TfrmOcc = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    lvOccupants: TListView;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    txtSearch: TEdit;
    btnSearch: TBitBtn;
    btnCancel: TBitBtn;
    cmbSearch: TComboBox;
    TabSheet2: TTabSheet;
    GroupBox2: TGroupBox;
    Label2: TLabel;
    txtSearch2: TEdit;
    btnWidraw: TBitBtn;
    btnGo: TBitBtn;
    cboSearch: TComboBox;
    lvbOcc: TListView;
    PopupMenu1: TPopupMenu;
    ViewProfile1: TMenuItem;
    AddClassSchedule1: TMenuItem;
    ChangeRoom1: TMenuItem;
    ViewPreviousRoom1: TMenuItem;
    DiactivateOccupants1: TMenuItem;
    PopupMenu2: TPopupMenu;
    ViewProfile2: TMenuItem;
    AddClassSchedule2: TMenuItem;
    ChangeRoom2: TMenuItem;
    ViewPreviousRoom2: TMenuItem;
    DiactivateOccupants2: TMenuItem;
    GeneratePassword1: TMenuItem;
    txtpass: TEdit;
    edit1: TEdit;
    txtchar: TEdit;
    procedure FormShow(Sender: TObject);
    procedure btnSearchClick(Sender: TObject);
    procedure AddClassSchedule1Click(Sender: TObject);
    procedure ChangeRoom1Click(Sender: TObject);
    procedure DiactivateOccupants1Click(Sender: TObject);
    procedure btnCancelClick(Sender: TObject);
    procedure ViewPreviousRoom1Click(Sender: TObject);
    procedure UPDATE1Click(Sender: TObject);
    procedure btnGoClick(Sender: TObject);
    procedure ViewProfile1Click(Sender: TObject);
    procedure ViewProfile2Click(Sender: TObject);
    procedure UpdateOccupant1Click(Sender: TObject);
    procedure btnPrinClick(Sender: TObject);
    procedure btnPrint2Click(Sender: TObject);
    procedure btnWidrawClick(Sender: TObject);
    procedure DiactivateOccupants2Click(Sender: TObject);
    procedure ChangeRoom2Click(Sender: TObject);
    procedure ViewPreviousRoom2Click(Sender: TObject);
    procedure GeneratePassword1Click(Sender: TObject);
  private
    { Private declarations }
  public
     primary : String;
     load : Integer;
     IDNumber : String;
     LastName : String;
     FirstName : String;
     rn : Integer;
     room : Integer;
     procedure loadLv;
     procedure displayID;
     procedure rmOcc;
     procedure loadbuilding;
     procedure withdrawn;
     procedure withdrawn2;
     procedure updateRoom(roomnum : integer);
     procedure updateBRoom(roomnum : integer);
     procedure decRoom(roomnum : integer);
     procedure cancelwid;
     procedure cancelWid2;
     procedure loadAcadYear;
     procedure loadBoccupants;
     procedure search;
     procedure boySearch;
     procedure loadFname;
     procedure loadLname;
     procedure loadboysLname;
     procedure loadboysFname;
     procedure loadFirstSem;
     procedure loadSecondSem;
     procedure loadFsem;
     procedure loadSsem;
     procedure genPass;
     procedure genCode;
     function occStatus(status : Integer) : String;
  end;

var
  frmOcc: TfrmOcc;

implementation

uses DM, unitCreateSched, unitDiactivate, unitUpdateOcc, unitStudentProf,
  unitPrevRoom, unitChangeRoom;

{$R *.dfm}

procedure TfrmOcc.FormShow(Sender: TObject);
begin
      loadLv;
      loadBoccupants;
end;

procedure TfrmOcc.btnSearchClick(Sender: TObject);
begin
      if (cmbSearch.Text = 'Firstname') then
     begin
        loadFname;
     end
     else if (cmbSearch.Text = 'Lastname') then
     begin
        loadLname;
     end
     else if (cmbSearch.Text = 'First Semester') then
     begin
          loadFirstSem;
     end
     else if (cmbSearch.Text = 'Second Semester') then
     begin
          loadSecondSem;
     end
     else if Length(txtSearch.Text) > 0 then
     begin
          search;
     end
     else
        begin
            MessageDlg('Please input first the textbox when searching.',mtInformation,[mbOK],0);
        end;
end;

procedure TfrmOcc.AddClassSchedule1Click(Sender: TObject);
begin
       if (lvOccupants.ItemFocused.SubItems[11] = 'Inactive') then
        begin
            MessageDlg('Occupants already inactive.',mtError,[mbOK],0);
        exit
        end;
    frmCreateSched.Label3.Caption := lvOccupants.ItemFocused.Caption;
    frmCreateSched.ShowModal;
end;

procedure TfrmOcc.ChangeRoom1Click(Sender: TObject);
begin
      if MessageDlg('Are you sure you want to change occupants room?.', mtConfirmation,[mbYes,mbNo],0) = mrYes then
      begin
          frmChangeRoom.Label6.Caption := lvOccupants.ItemFocused.Caption;
          frmChangeRoom.txtOccid.Text := lvOccupants.ItemFocused.Caption;
          frmChangeRoom.txtLname.Text := lvOccupants.ItemFocused.SubItems[2];
          frmChangeRoom.txtFname.Text := lvOccupants.ItemFocused.SubItems[3];
          frmChangeRoom.txtBuilding.Text := lvOccupants.ItemFocused.SubItems[4];
          frmChangeRoom.txtRoom.Text := lvOccupants.ItemFocused.SubItems[5];
          frmChangeRoom.ShowModal;
      end 
end;

procedure TfrmOcc.DiactivateOccupants1Click(Sender: TObject);
begin
      if (lvOccupants.ItemFocused.SubItems[7] = 'Inactive') then
           begin
                MessageDlg('Occupants already inactive.',mtError,[mbOK],0);
              exit
           end;
              frmDiactivate.Label2.Caption := lvOccupants.ItemFocused.Caption;
              frmDiactivate.txtID.Text := lvOccupants.ItemFocused.SubItems[1];
              frmDiactivate.txtBuil.Text := lvOccupants.ItemFocused.SubItems[4];
              frmDiactivate.txtRMNo.Text := lvOccupants.ItemFocused.SubItems[5];
              frmDiactivate.Show;
end;

procedure TfrmOcc.btnCancelClick(Sender: TObject);
begin
      if MessageDlg('Do you want to withdraw the occupants', mtConfirmation,[mbYes,mbNo],0) = mrYes then
    begin
        rn := StrToInt(lvOccupants.ItemFocused.SubItems[7]);
        updateRoom(rn);
        withdrawn;
    end
      else
        exit;
end;
procedure TfrmOcc.ViewPreviousRoom1Click(Sender: TObject);
begin
      frmPreviousRoom.loadChangeRoom(StrToInt(lvOccupants.ItemFocused.Caption));
      frmPreviousRoom.ShowModal;
end;

function TfrmOcc.occStatus(status : Integer) : String;
begin
     case status of
          1 : occStatus := 'Active';
          0 : occStatus := 'Inactive';
     end;
end;

procedure TfrmOcc.loadLv;
var
  newState : TListItem;
  occupantStat : Integer;
  status : String;
begin

    if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION, a.OCC_STATUS,c.ACADYR, c.SEM,a.ADDEDBY,a.DATEADDED, a.DATE_WID, c.APP_STATUS');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE a.BUIL_ID = b.BUIL_ID And c.APPID = a.APPID AND c.GENDER = :A AND c.APP_STATUS = :B ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := 'Female';
        DataModule1.query.Params.AsString[1] := 'Admit';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvOccupants.Items.BeginUpdate;
        lvOccupants.Items.Clear;

              if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                  occupantStat:=StrToInt(DataModule1.query.Fields.AsString[8]);
                   status:= occStatus(occupantStat);

                    newState := lvOccupants.Items.Add;
                    newState.Caption := DataModule1.query.Fields.AsString[0];
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
                    newState.SubItems.Add(status);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[14]);
                    DataModule1.query.Next;
                    end;

                        if DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;
                            lvOccupants.Items.EndUpdate;
end;

procedure TfrmOcc.loadBoccupants;
var
  newState : TListItem;
  occupantStat : Integer;
  status : String;
begin

    if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,a.OCC_STATUS,c.ACADYR, c.SEM,a.ADDEDBY, a.DATEADDED,a.DATE_WID, c.APP_STATUS, a.ADDEDBY, a.DATEADDED');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE a.BUIL_ID = b.BUIL_ID And c.APPID = a.APPID AND c.GENDER = :A and c.APP_STATUS = :B ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := 'Male';
        DataModule1.query.Params.AsString[1] := 'Admit';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvbOcc.Items.BeginUpdate;
        lvbOcc.Items.Clear;

              if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                    occupantStat:=StrToInt(DataModule1.query.Fields.AsString[8]);
                    status:= occStatus(occupantStat);

                    newState := lvbOcc.Items.Add;
                    newState.Caption := DataModule1.query.Fields.AsString[0];
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
                    newState.SubItems.Add(status);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[14]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[15]);
                    DataModule1.query.Next;

                    end;

                        if DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;
                            lvbOcc.Items.EndUpdate;
end;

procedure TfrmOcc.search;
var
  newState : TListItem;
  occupantStat : Integer;
  status : String;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,a.OCC_STATUS,c.ACADYR, c.SEM,a.DATE_WID, c.APP_STATUS');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE b.BUIL_ID = a.BUIL_ID And c.APPID = a.APPID AND c.STUDENT_ID =:A and c.APP_STATUS =:D ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := trim(txtSearch.Text);
        DataModule1.query.Params.AsString[1] := 'Admit';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvOccupants.Items.BeginUpdate;
        lvOccupants.Items.Clear;

        if DataModule1.query.Fields.IsNull[0] then Exit;
          while not DataModule1.query.Eof do Begin

          occupantStat := StrToInt(DataModule1.query.Fields.AsString[8]);
          status := occStatus(occupantStat);

          newState := lvOccupants.Items.Add;
          newState.Caption := DataModule1.query.Fields.AsString[0];
          newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
          newState.SubItems.Add(status);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);

          DataModule1.query.Next;
      end;

          if DataModule1.tr.InTransaction then
             DataModule1.tr.Commit;
             lvOccupants.Items.EndUpdate;
end;

procedure TfrmOcc.boySearch;
var
  newState : TListItem;
  occupantStat : Integer;
  status : String;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,b.STUDENT_ID,b.APP_LNAME,b.APP_FNAME,c.DESCRIPTION,a.RM_NO,a.OCC_POSITION, a.OCC_STATUS,b.ACADYR, b.SEM,a.DATE_WID, b.APP_STATUS');
        DataModule1.query.SQL.Add('FROM OCCUPANTS a, APPLICANTS b, BUILDING c');
        DataModule1.query.SQL.Add('WHERE c.BUIL_ID = a.BUIL_ID And b.APPID = a.APPID AND b.STUDENT_ID =:A AND b.APP_STATUS =:D');
        DataModule1.query.Params.AsString[0] := trim(txtSearch2.Text);
        DataModule1.query.Params.AsString[1] := 'Admit';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvbOcc.Items.BeginUpdate;
        lvbOcc.Items.Clear;

        if DataModule1.query.Fields.IsNull[0] then Exit;
          while not DataModule1.query.Eof do Begin

          occupantStat := StrToInt(DataModule1.query.Fields.AsString[8]);
          status := occStatus(occupantStat);

          newState := lvbOcc.Items.Add;
          newState.Caption := DataModule1.query.Fields.AsString[0];
          newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
          newState.SubItems.Add(status);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);

          DataModule1.query.Next;
      end;

          if DataModule1.tr.InTransaction then
             DataModule1.tr.Commit;
             lvbOcc.Items.EndUpdate;
end;

procedure TfrmOcc.loadFname;
var
  newState : TListItem;
  occupantStat : Integer;
  status : String;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,a.OCC_STATUS,c.ACADYR, c.SEM,a.ADDEDBY, a.DATEADDED, a.DATE_WID, c.APP_STATUS');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE b.BUIL_ID = a.BUIL_ID And c.APPID = a.APPID AND c.APP_FNAME LIKE :C AND c.APP_STATUS =:D ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := trim(txtSearch.Text);
        DataModule1.query.Params.AsString[1] := 'Admit';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvOccupants.Items.BeginUpdate;
        lvOccupants.Items.Clear;

        if DataModule1.query.Fields.IsNull[0] then Exit;
          while not DataModule1.query.Eof do Begin

          occupantStat := StrToInt(DataModule1.query.Fields.AsString[8]);
          status := occStatus(occupantStat);

          newState := lvOccupants.Items.Add;
          newState.Caption := DataModule1.query.Fields.AsString[0];
          newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
          newState.SubItems.Add(status);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[14]);
          DataModule1.query.Next;
      end;

          if DataModule1.tr.InTransaction then
             DataModule1.tr.Commit;
             lvOccupants.Items.EndUpdate;
end;

procedure TfrmOcc.loadLname;
var
  newState : TListItem;
  occupantStat : Integer;
  status : String;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;

        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,a.OCC_STATUS,c.ACADYR, c.SEM,a.ADDEDBY, a.DATEADDED, a.DATE_WID, c.APP_STATUS');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE b.BUIL_ID = a.BUIL_ID And c.APPID = a.APPID AND c.APP_LNAME LIKE :C AND c.APP_STATUS =:D ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := trim(txtSearch.Text);
        DataModule1.query.Params.AsString[1] := 'Admit';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvOccupants.Items.BeginUpdate;
        lvOccupants.Items.Clear;

        if DataModule1.query.Fields.IsNull[0] then Exit;
          while not DataModule1.query.Eof do Begin

          occupantStat := StrToInt(DataModule1.query.Fields.AsString[8]);
          status := occStatus(occupantStat);

          newState := lvOccupants.Items.Add;
          newState.Caption := DataModule1.query.Fields.AsString[0];
          newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
          newState.SubItems.Add(status);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[14]);
          DataModule1.query.Next;
      end;

          if DataModule1.tr.InTransaction then
             DataModule1.tr.Commit;
             lvOccupants.Items.EndUpdate;
end;

procedure TfrmOcc.loadboysLname;
var
  newState : TListItem;
  occupantStat : Integer;
  status : String;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,a.OCC_STATUS,c.ACADYR, c.SEM, a.ADDEDBY, a.DATEADDED, a.DATE_WID, c.APP_STATUS');
        DataModule1.query.SQL.Add('FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE b.BUIL_ID = a.BUIL_ID And c.APPID = a.APPID AND c.APP_LNAME LIKE :C AND c.APP_STATUS =:D ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := trim(txtSearch2.Text);
        DataModule1.query.Params.AsString[1] := 'Admit';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvbOcc.Items.BeginUpdate;
        lvbOcc.Items.Clear;

        if DataModule1.query.Fields.IsNull[0] then Exit;
          while not DataModule1.query.Eof do Begin

          occupantStat := StrToInt(DataModule1.query.Fields.AsString[8]);
          status := occStatus(occupantStat);

          newState := lvbOcc.Items.Add;
          newState.Caption := DataModule1.query.Fields.AsString[0];
          newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
          newState.SubItems.Add(status);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[14]);
          DataModule1.query.Next;
      end;

          if DataModule1.tr.InTransaction then
             DataModule1.tr.Commit;
             lvbOcc.Items.EndUpdate;
end;

procedure TfrmOcc.loadboysFname;
var
  newState : TListItem;
  occupantStat : Integer;
  status : String;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;

        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,a.OCC_STATUS,c.ACADYR, c.SEM, a.ADDEDBY, a.DATEADDED, a.DATE_WID, c.APP_STATUS, a.ADDEDBY, a.DATEADDED');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE b.BUIL_ID = a.BUIL_ID And c.APPID = a.APPID AND c.APP_FNAME LIKE :C AND c.APP_STATUS =:D ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := trim(txtSearch2.Text);
        DataModule1.query.Params.AsString[1] := 'Admit';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvbOcc.Items.BeginUpdate;
        lvbOcc.Items.Clear;

        if DataModule1.query.Fields.IsNull[0] then Exit;
          while not DataModule1.query.Eof do Begin

          occupantStat := StrToInt(DataModule1.query.Fields.AsString[8]);
          status := occStatus(occupantStat);

          newState := lvbOcc.Items.Add;
          newState.Caption := DataModule1.query.Fields.AsString[0];
          newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
          newState.SubItems.Add(status);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
          newState.SubItems.Add(DataModule1.query.Fields.AsString[14]);
          DataModule1.query.Next;
      end;

          if DataModule1.tr.InTransaction then
             DataModule1.tr.Commit;
             lvbOcc.Items.EndUpdate;
end;

procedure TfrmOcc.loadFirstSem;
var newState : TListItem;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,a.OCC_STATUS,c.ACADYR, c.SEM,a.ADDEDBY, a.DATEADDED, a.DATE_WID, c.APP_STATUS, c.GENDER');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE b.BUIL_ID = a.BUIL_ID And c.APPID = a.APPID AND c.ACADYR = :C AND c.SEM =:D AND c.GENDER = :E ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := trim(txtSearch.Text);
        DataModule1.query.Params.AsString[1] := 'First Semester';
        DataModule1.query.Params.AsString[2] := 'Female';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvOccupants.Items.BeginUpdate;
        lvOccupants.Items.Clear;

          if DataModule1.query.Fields.IsNull[0] then Exit;
            while not DataModule1.query.Eof do begin

            newState := lvOccupants.Items.Add;
            newState.Caption := DataModule1.query.Fields.AsString[0];
            newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[8]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
            DataModule1.query.Next;
end;
            if DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;
            lvOccupants.Items.EndUpdate;
end;

procedure TfrmOcc.loadSecondSem;
var
  newState : TListItem;
  occupantStat : Integer;
  status : String;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,a.OCC_STATUS,c.ACADYR, c.SEM,a.ADDEDBY, a.DATEADDED, a.DATE_WID, c.APP_STATUS, c.GENDER');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE b.BUIL_ID = a.BUIL_ID And c.APPID = a.APPID AND c.ACADYR = :C AND c.SEM =:D AND c.GENDER = :E ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := trim(txtSearch.Text);
        DataModule1.query.Params.AsString[1] := 'Second Semester';
        DataModule1.query.Params.AsString[2] := 'Female';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvOccupants.Items.BeginUpdate;
        lvOccupants.Items.Clear;

          if DataModule1.query.Fields.IsNull[0] then Exit;
            while not DataModule1.query.Eof do begin

            occupantStat := StrToInt(DataModule1.query.Fields.AsString[8]);
            status := occStatus(occupantStat);

            newState := lvOccupants.Items.Add;
            newState.Caption := DataModule1.query.Fields.AsString[0];
            newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
            newState.SubItems.Add(status);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[14]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[15]);
            DataModule1.query.Next;
end;
            if DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;
            lvOccupants.Items.EndUpdate;
end;

procedure TfrmOcc.loadFsem;
var
    newState : TListItem;
    occupantStat : Integer;
    status : String;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,a.OCC_STATUS,c.ACADYR, c.SEM,a.ADDEDBY, a.DATEADDED, a.DATE_WID, c.APP_STATUS, c.GENDER');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE b.BUIL_ID = a.BUIL_ID And c.APPID = a.APPID AND c.ACADYR = :C AND c.SEM =:D AND c.GENDER = :E ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := trim(txtSearch2.Text);
        DataModule1.query.Params.AsString[1] := 'First Semester';
        DataModule1.query.Params.AsString[2] := 'Male';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvbOcc.Items.BeginUpdate;
        lvbOcc.Items.Clear;

          if DataModule1.query.Fields.IsNull[0] then Exit;
            while not DataModule1.query.Eof do begin

            occupantStat:=StrToInt(DataModule1.query.Fields.AsString[8]);
            status:= occStatus(occupantStat);

            newState := lvbOcc.Items.Add;
            newState.Caption := DataModule1.query.Fields.AsString[0];
            newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
            newState.SubItems.Add(status);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[14]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[15]);
            DataModule1.query.Next;
end;
            if DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;
            lvbOcc.Items.EndUpdate;
end;

procedure TfrmOcc.loadSsem;
var
    newState : TListItem;
    occupantStat : Integer;
    status : String;
begin
      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,a.OCC_STATUS,c.ACADYR, c.SEM,a.ADDEDBY, a.DATEADDED,a.DATE_WID, c.APP_STATUS, c.GENDER');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE b.BUIL_ID = a.BUIL_ID And c.APPID = a.APPID AND c.ACADYR = :C AND c.SEM =:D AND c.GENDER = :E ORDER BY a.OCC_ID');
        DataModule1.query.Params.AsString[0] := trim(txtSearch2.Text);
        DataModule1.query.Params.AsString[1] := 'Second Semester';
        DataModule1.query.Params.AsString[2] := 'Male';
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvbOcc.Items.BeginUpdate;
        lvbOcc.Items.Clear;

          if DataModule1.query.Fields.IsNull[0] then Exit;
            while not DataModule1.query.Eof do begin

            occupantStat:=StrToInt(DataModule1.query.Fields.AsString[8]);
            status:= occStatus(occupantStat);

            newState := lvbOcc.Items.Add;
            newState.Caption := DataModule1.query.Fields.AsString[0];
            newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
            newState.SubItems.Add(status);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[14]);
            newState.SubItems.Add(DataModule1.query.Fields.AsString[15]);
            DataModule1.query.Next;
end;
            if DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;
            lvbOcc.Items.EndUpdate;
end;

procedure TfrmOcc.displayID;
var newState : TListItem;
begin
    if DataModule1.tr.InTransaction then
      DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,c.YEAR_COURSE,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,c.APP_SCHOLARSHIP, c.ACADYEAR, c.SEMESTER, a.OCC_STATUS,a.ISWIDRAWN');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE a.BUIL_ID = b.BUIL_ID And c.APPID = a.APPID and c.STUDENT_ID = :A');
        DataModule1.query.Params.AsString[0] := Trim(txtSearch.Text);
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvOccupants.Items.BeginUpdate;
        lvOccupants.Items.Clear;

              if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                    newState := lvOccupants.Items.Add;
                    newState.Caption := DataModule1.query.Fields.AsString[0];
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[8]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);

                    DataModule1.query.Next;
                    end;

                        if DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;
                            lvOccupants.Items.EndUpdate;
                              if (txtSearch.Text <> IDNumber) then
                                begin
                                    MessageDlg('ID Number not found',mtInformation,[mbOK],0);
                                        txtSearch.Text := '';
                                        txtSearch.SetFocus;
                                end
end;


procedure TfrmOcc.rmOcc;
var newState : TListItem;
begin
    if DataModule1.tr.InTransaction then
      DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,c.YEAR_COURSE,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,c.APP_SCHOLARSHIP,c.ACADYEAR,c.SEMESTER,a.OCC_STATUS');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE a.BUIL_ID = b.BUIL_ID And c.APPID = a.APPID AND a.RM_NO = :A');
        DataModule1.query.Params.AsString[0] := Trim(txtSearch.Text);
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvOccupants.Items.BeginUpdate;
        lvOccupants.Items.Clear;

              if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                    newState := lvOccupants.Items.Add;
                    newState.Caption := DataModule1.query.Fields.AsString[0];
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[8]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);

                    DataModule1.query.Next;
                    end;

                        if DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;
                            lvOccupants.Items.EndUpdate;

end;


procedure TfrmOcc.loadbuilding;
var newState : TListItem;
begin
    if DataModule1.tr.InTransaction then
      DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,c.YEAR_COURSE,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,c.APP_SCHOLARSHIP, c.ACADYEAR, c.SEMESTER, a.OCC_STATUS');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE a.BUIL_ID = b.BUIL_ID And c.APPID = a.APPID AND b.ACRONYM = :A');
        DataModule1.query.Params.AsString[0] := Trim(txtSearch.Text);
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvOccupants.Items.BeginUpdate;
        lvOccupants.Items.Clear;

              if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                    newState := lvOccupants.Items.Add;
                    newState.Caption := DataModule1.query.Fields.AsString[0];
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[8]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);

                    DataModule1.query.Next;
                    end;

                        if DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;
                            lvOccupants.Items.EndUpdate;

end;


procedure TfrmOcc.loadAcadYear;
var newState : TListItem;
begin
    if DataModule1.tr.InTransaction then
      DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT a.OCC_ID,a.APPID,c.STUDENT_ID,c.APP_LNAME,c.APP_FNAME,c.YEAR_COURSE,b.DESCRIPTION,a.RM_NO,a.OCC_POSITION,c.APP_SCHOLARSHIP, c.ACADYEAR, c.SEMESTER, a.OCC_STATUS, a.ADDEDBY, a.DATEADDED');
        DataModule1.query.SQL.Add(' FROM OCCUPANTS a, BUILDING b, APPLICANTS c WHERE a.BUIL_ID = b.BUIL_ID And c.APPID = a.APPID AND c.ACADYEAR = :A');
        DataModule1.query.Params.AsString[0] := Trim(txtSearch.Text);
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        lvOccupants.Items.BeginUpdate;
        lvOccupants.Items.Clear;

              if DataModule1.query.Fields.IsNull[0] then Exit;
                while not DataModule1.query.Eof do begin

                    newState := lvOccupants.Items.Add;
                    newState.Caption := DataModule1.query.Fields.AsString[0];
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[1]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[2]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[3]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[4]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[5]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[6]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[7]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[8]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[9]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[10]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[11]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[12]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[13]);
                    newState.SubItems.Add(DataModule1.query.Fields.AsString[14]);
                    DataModule1.query.Next;
                    end;

                        if DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;
                            lvOccupants.Items.EndUpdate;

end;

procedure TfrmOcc.withdrawn;
begin
    if DataModule1.tr.InTransaction then
      DataModule1.tr.Commit;

      DataModule1.query.SQL.Clear;
      DataModule1.query.SQL.Add('UPDATE OCCUPANTS SET OCC_STATUS = :A, DATE_WID = :B WHERE OCC_ID = :C');
      DataModule1.query.Params.AsString[0] := '0';
      DataModule1.query.Params.AsString[1] := DateToStr(Now());
      DataModule1.query.Params.AsInteger[2] := StrToInt(lvOccupants.ItemFocused.Caption);

      DataModule1.query.Prepare;
      DataModule1.query.Open();

        if DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;
            ShowMessage('The Occupants has been successfully withdrawn.');
              loadLv;


end;

procedure TfrmOcc.withdrawn2;
begin
    if DataModule1.tr.InTransaction then
      DataModule1.tr.Commit;

      DataModule1.query.SQL.Clear;
      DataModule1.query.SQL.Add('UPDATE OCCUPANTS SET OCC_STATUS = :A, DATE_WID = :B WHERE OCC_ID = :C');
      DataModule1.query.Params.AsString[0] := '0';
      DataModule1.query.Params.AsString[1] := DateToStr(Now());
      DataModule1.query.Params.AsInteger[2] := StrToInt(lvbOcc.ItemFocused.Caption);

      DataModule1.query.Prepare;
      DataModule1.query.Open();

        if DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;
            ShowMessage('The Occupants has been successfully withdrawn.');
              loadLv;


end;

procedure TfrmOcc.updateRoom(roomnum : integer);
var count : integer;
begin
       If DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;

         DataModule1.query.SQL.Clear;
         DataModule1.query.SQL.Add('SELECT  a.CURRENTNO FROM ROOM a WHERE a.RM_NO = :a');
         DataModule1.query.Params.AsInteger[0] := roomnum;
         DataModule1.query.prepare;
         DataModule1.query.Open;
         count := DataModule1.query.Fields.AsInteger[0];
         count := count - 1;

         If DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;

         DataModule1.query.SQL.Clear;
         DataModule1.query.SQL.Add('update room set currentno =:b where rm_no = :c');
         DataModule1.query.Params.AsInteger[0] := count;
         DataModule1.query.Params.AsInteger[1] := roomnum;
         DataModule1.query.prepare;
         DataModule1.query.Execute;

end;

procedure TfrmOcc.updateBRoom(roomnum : integer);
var count : integer;
begin
       If DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;

         DataModule1.query.SQL.Clear;
         DataModule1.query.SQL.Add('SELECT  a.CURRENTNO FROM ROOM a WHERE a.RM_NO = :a');
         DataModule1.query.Params.AsInteger[0] := roomnum;
         DataModule1.query.prepare;
         DataModule1.query.Open;
         count := DataModule1.query.Fields.AsInteger[0];
         count := count - 1;

         If DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;

         DataModule1.query.SQL.Clear;
         DataModule1.query.SQL.Add('update room set currentno =:b where rm_no = :c');
         DataModule1.query.Params.AsInteger[0] := count;
         DataModule1.query.Params.AsInteger[1] := roomnum;
         DataModule1.query.prepare;
         DataModule1.query.Execute;

end;

procedure TfrmOcc.decRoom(roomnum : integer);
var count : integer;
begin
       If DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;

         DataModule1.query.SQL.Clear;
         DataModule1.query.SQL.Add('SELECT  a.CURRENTNO FROM ROOM a WHERE a.RM_NO = :a');
         DataModule1.query.Params.AsInteger[0] := roomnum;
         DataModule1.query.prepare;
         DataModule1.query.Open;
         count := DataModule1.query.Fields.AsInteger[0];
         count := count + 1;

         If DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;

         DataModule1.query.SQL.Clear;
         DataModule1.query.SQL.Add('update room set currentno =:b where rm_no = :c');
         DataModule1.query.Params.AsInteger[0] := count;
         DataModule1.query.Params.AsInteger[1] := roomnum;
         DataModule1.query.prepare;
         DataModule1.query.Execute;

end;


procedure TfrmOcc.cancelwid;
begin
    if DataModule1.tr.InTransaction then
      DataModule1.tr.Commit;

      DataModule1.query.SQL.Clear;
      DataModule1.query.SQL.Add('UPDATE OCCUPANTS SET OCC_STATUS = :A WHERE OCC_ID = :C');
      DataModule1.query.Params.AsString[0] := '1';
      DataModule1.query.Params.AsInteger[1] := StrToInt(lvOccupants.ItemFocused.Caption);
      DataModule1.query.Prepare;
      DataModule1.query.Execute;

      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

end;

procedure TfrmOcc.cancelWid2;
begin
    if DataModule1.tr.InTransaction then
      DataModule1.tr.Commit;

      DataModule1.query.SQL.Clear;
      DataModule1.query.SQL.Add('UPDATE OCCUPANTS SET OCC_STATUS = :A WHERE OCC_ID = :C');
      DataModule1.query.Params.AsString[0] := '1';
      DataModule1.query.Params.AsInteger[1] := StrToInt(lvbOcc.ItemFocused.Caption);
      DataModule1.query.Prepare;
      DataModule1.query.Execute;

      if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

end;
procedure TfrmOcc.UPDATE1Click(Sender: TObject);
begin
      frmUpdate.txtoccid.Text := lvOccupants.ItemFocused.Caption;
      frmUpdate.txtID.Text := lvOccupants.ItemFocused.SubItems[1];
      frmUpdate.txtLname.Text := lvOccupants.ItemFocused.SubItems[2];
      frmUpdate.txtFname.Text := lvOccupants.ItemFocused.SubItems[3];
      frmUpdate.txtgen.Text := lvOccupants.ItemFocused.SubItems[4];
      frmUpdate.ShowModal;
end;

procedure TfrmOcc.btnGoClick(Sender: TObject);
begin
      if (cboSearch.Text = 'Lastname') then
      begin
            loadboysLname;
      end
          else if (cboSearch.Text = 'Firstname') then
            begin
                  loadboysFname;
            end
                else if (cboSearch.Text = 'First Semester') then
                   begin
                        loadFsem;
                    end
                      else if (cboSearch.Text = 'Second Semester') then
                        begin
                              loadSsem;
                        end
                          else if Length(txtSearch2.Text) > 0 then
                            begin
                              boySearch;
                            end
                              else  begin
                                  MessageDlg('Please input first the textbox when searching.',mtInformation,[mbOK],0);
                              end;
end;

procedure TfrmOcc.ViewProfile1Click(Sender: TObject);

begin
     frmStudentProf.loadSched(StrToInt(lvOccupants.ItemFocused.Caption));
     frmStudentProf.txtappid.Text := lvOccupants.ItemFocused.Caption;
     frmStudentProf.txtoccid.Text := lvOccupants.ItemFocused.Caption;
     frmStudentProf.txtpk.Text := lvOccupants.ItemFocused.Caption;
     frmStudentProf.txtidapp.Text := lvOccupants.ItemFocused.SubItems[0];
     frmStudentProf.ShowModal;
end;

procedure TfrmOcc.ViewProfile2Click(Sender: TObject);
begin
      frmStudentProf.loadSched(StrToInt(lvbOcc.ItemFocused.Caption));
     frmStudentProf.txtappid.Text := lvbOcc.ItemFocused.Caption;
     frmStudentProf.txtoccid.Text := lvbOcc.ItemFocused.Caption;
     frmStudentProf.txtpk.Text := lvbOcc.ItemFocused.Caption;
     frmStudentProf.txtidapp.Text := lvbOcc.ItemFocused.SubItems[0];
     frmStudentProf.ShowModal;
end;

procedure TfrmOcc.UpdateOccupant1Click(Sender: TObject);
begin
    frmUpdate.txtoccid.Text := lvbOcc.ItemFocused.Caption;
      frmUpdate.txtID.Text := lvbOcc.ItemFocused.SubItems[1];
      frmUpdate.txtLname.Text := lvbOcc.ItemFocused.SubItems[2];
      frmUpdate.txtFname.Text := lvbOcc.ItemFocused.SubItems[3];
      frmUpdate.txtgen.Text := lvbOcc.ItemFocused.SubItems[4];
      frmUpdate.ShowModal;
end;

procedure TfrmOcc.btnPrinClick(Sender: TObject);
begin
       DataModule1.rep.Filename := 'C:\Users\janine\Documents\DOMS\Reports\OccupantReport.rep';
    DataModule1.rep.Report.Params.Items[0].Value := txtSearch.Text;
    DataModule1.rep.Report.Params.Items[1].Value := cmbSearch.Text;
    DataModule1.rep.Report.Params.Items[2].Value := 'Female';
    DataModule1.rep.Preview := True;
    DataModule1.rep.Execute();
end;

procedure TfrmOcc.btnPrint2Click(Sender: TObject);
begin
    DataModule1.rep.Filename := 'C:\Users\janine\Documents\DOMS\Reports\BoysOccRep.rep';
    DataModule1.rep.Report.Params.Items[0].Value := txtSearch2.Text;
    DataModule1.rep.Report.Params.Items[1].Value := cboSearch.Text;
    DataModule1.rep.Report.Params.Items[2].Value := 'Male';
    DataModule1.rep.Preview := True;
    DataModule1.rep.Execute();
end;

procedure TfrmOcc.btnWidrawClick(Sender: TObject);
begin
 if MessageDlg('Do you want to withdraw the occupants', mtConfirmation,[mbYes,mbNo],0) = mrYes then
    begin
       rn := StrToInt(lvbOcc.ItemFocused.SubItems[7]);
        updateBRoom(rn);
        withdrawn2;
    end
      else
        exit;
end;

procedure TfrmOcc.DiactivateOccupants2Click(Sender: TObject);
begin
      if (lvbOcc.ItemFocused.SubItems[7] = 'Inactive') then
           begin
                MessageDlg('Occupants already inactive.',mtError,[mbOK],0);
              exit
           end;
              frmDiactivate.Label2.Caption := lvbOcc.ItemFocused.Caption;
              frmDiactivate.txtID.Text := lvbOcc.ItemFocused.SubItems[1];
              frmDiactivate.txtBuil.Text := lvbOcc.ItemFocused.SubItems[4];
              frmDiactivate.txtRMNo.Text := lvbOcc.ItemFocused.SubItems[5];
              frmDiactivate.ShowModal;
end;

procedure TfrmOcc.ChangeRoom2Click(Sender: TObject);
begin
    if MessageDlg('Are you sure you want to change occupants room?.', mtConfirmation,[mbYes,mbNo],0) = mrYes then
      begin
          frmChangeRoom.Label6.Caption := lvbOcc.ItemFocused.Caption;
          frmChangeRoom.txtOccid.Text := lvbOcc.ItemFocused.Caption;
          frmChangeRoom.txtLname.Text := lvbOcc.ItemFocused.SubItems[2];
          frmChangeRoom.txtFname.Text := lvbOcc.ItemFocused.SubItems[3];
          frmChangeRoom.txtBuilding.Text := lvbOcc.ItemFocused.SubItems[4];
          frmChangeRoom.txtRoom.Text := lvbOcc.ItemFocused.SubItems[5];
          frmChangeRoom.ShowModal;
      end
end;

procedure TfrmOcc.ViewPreviousRoom2Click(Sender: TObject);
begin
      frmPreviousRoom.ShowModal;
end;

procedure TfrmOcc.genCode;
begin
    if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('UPDATE OCCUPANTS SET PASS = :a WHERE OCC_ID = :B');
        DataModule1.query.Params.AsString[0] := txtpass.text;
       DataModule1.query.Params.AsString[1] := lvOccupants.ItemFocused.Caption;
        DataModule1.query.Open();
        DataModule1.query.Prepare;

        if DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;
          ShowMessage('Password generated');


end;

procedure TfrmOcc.genPass;
var a,x,c:byte; // X Password Lenght..
var  ch: char;
begin
txtpass.text:='';
c:=0;
 randomize;
 x:=strtoint(edit1.text); // X := Lenght..

   repeat
 if (txtChar.Text = '') then
  begin   //Random 4 Alpha & 0-9.
   a:=random(3);
   if a=0 then ch:=chr(48+random(10));   //Random 4 0-9..
   if a=1 then ch:=chr(65+random(26));   //--||-- 4 A-Z..
   if a=2 then ch:=chr(97+random(26));   //--||-- 4 a-z..
  end;
   txtPass.text:=txtPass.text+ch;
   c:=c+1;    //C , Counter..
  until c=x;
 end;
procedure TfrmOcc.GeneratePassword1Click(Sender: TObject);
 begin
 genPass;
  genCode;
 end;

end.
