unit unitOccupantRep;

interface

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

type
  TfrmOccRep = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    lvOccupants: TListView;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    txtSearch: TEdit;
    btnSearch: TBitBtn;
    cmbSearch: TComboBox;
    BitBtn1: TBitBtn;
    TabSheet2: TTabSheet;
    GroupBox2: TGroupBox;
    Label2: TLabel;
    txtSearch2: TEdit;
    btnPrint2: TBitBtn;
    btnGo: TBitBtn;
    cboSearch: TComboBox;
    lvbOcc: TListView;
    PopupMenu1: TPopupMenu;
    UPDATE1: TMenuItem;
    ViewProfile1: TMenuItem;
    AddClassSchedule1: TMenuItem;
    ChangeRoom1: TMenuItem;
    ViewPreviousRoom1: TMenuItem;
    CancelWithdrawal1: TMenuItem;
    DiactivateOccupants1: TMenuItem;
    PopupMenu2: TPopupMenu;
    UpdateOccupant1: TMenuItem;
    ViewProfile2: TMenuItem;
    AddClassSchedule2: TMenuItem;
    ViewClassSchedule2: TMenuItem;
    CancelWithdrawal2: TMenuItem;
    ChangeRoom2: TMenuItem;
    ViewPreviousRoom2: TMenuItem;
    procedure btnSearchClick(Sender: TObject);
    procedure btnGoClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure btnPrint2Click(Sender: TObject);
  private
    { Private declarations }
  public
      procedure loadFirstSem;
      procedure loadSecondSem;
      procedure loadFsem;
      procedure loadSsem;
      function occStatus(status : Integer) : String;
      procedure loadLv;
      procedure loadBoccupants;
    { Public declarations }
  end;

var
  frmOccRep: TfrmOccRep;

implementation

uses DM;

{$R *.dfm}

procedure TfrmOccRep.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 TfrmOccRep.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 TfrmOccRep.btnSearchClick(Sender: TObject);
begin
   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;

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

procedure TfrmOccRep.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 TfrmOccRep.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 TfrmOccRep.btnGoClick(Sender: TObject);
begin
              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 TfrmOccRep.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 TfrmOccRep.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]);
                  //  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 TfrmOccRep.FormShow(Sender: TObject);
begin
     loadLv;
     loadBoccupants;
end;

procedure TfrmOccRep.BitBtn1Click(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 TfrmOccRep.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;

end.
