unit AssignRoom;

interface

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

type
  TfrmRMAss = class(TForm)
    Label7: TLabel;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label4: TLabel;
    Label6: TLabel;
    Label3: TLabel;
    txtID: TEdit;
    txtLname: TEdit;
    cmbBuil: TComboBox;
    txtFname: TEdit;
    cmbRoom: TComboBox;
    StatusBar1: TStatusBar;
    btnAdd: TBitBtn;
    BibtnCancel: TBitBtn;
    txtOccStat: TEdit;
    txtAppid: TEdit;
    Label5: TLabel;
    Label8: TLabel;
    txtacad: TEdit;
    txtsem: TEdit;
    procedure btnAddClick(Sender: TObject);
    procedure BibtnCancelClick(Sender: TObject);
    procedure incrementRoomOccupants(roomnum : integer);
    procedure cmbBuilChange(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
      occupants : Integer;
      occid : Integer;
      rm_no : Integer;
      primaryOccid : Integer;
      currentOccupants : Integer;
      appid : Integer;
      id : String;
      room : Integer;
      appidPK:Integer;
      acadYear : String;
      sem : String;
      procedure building;
      procedure addOcc;
      procedure getBuilID;
      procedure getPrimary;
      procedure updatestat;
   //   function existOcc() : Boolean;
      function add(bldg,rn:Integer):boolean;
  end;

var
  frmRMAss: TfrmRMAss;
  systemDate : TDateTime;
  DateTimeformat : string;
implementation

uses DM, unitLogin;

{$R *.dfm}

procedure TfrmRMAss.btnAddClick(Sender: TObject);
var
room : Integer;
rn : Integer;
begin
  rn:= StrToInt(cmbRoom.Text);
         if (add(occid,rn) = false) then
              exit
          else
            addOcc;
            room := StrToInt(cmbRoom.Text);
            incrementRoomOccupants(room);
            Close
end;

procedure TfrmRMAss.BibtnCancelClick(Sender: TObject);
begin
      Close;
end;

procedure TfrmRMAss.cmbBuilChange(Sender: TObject);
begin
      getBuilID;

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

          DataModule1.query.SQL.Clear;
          DataModule1.query.SQL.Add('SELECT a.RM_NO ');
          DataModule1.query.SQL.Add('FROM ROOM a, BUILDING b ');
          DataModule1.query.SQL.Add('WHERE a.BUIL_ID = b.BUIL_ID AND b.DESCRIPTION = :a');
          DataModule1.query.Params.AsString[0] := trim(cmbBuil.Text);
          DataModule1.query.Prepare;
          DataModule1.query.Open();

          cmbRoom.Items.Clear;
            while not DataModule1.query.Eof do begin
              cmbRoom.AddItem(DataModule1.query.Fields.AsString[0],cmbRoom);
                  DataModule1.query.Next;
          end;

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

{function TfrmRMAss.existOcc() : boolean;
begin
      acadYear := txtacad.Text;
      sem := txtSem.Text;

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

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT COUNT(occ_id) FROM OCCLIST');
        DataModule1.query.SQL.Add('where ACADYR = :A and SEM = :B');
        DataModule1.query.Params.AsString[0] := txtacad.Text;
        DataModule1.query.Params.AsString[1] := txtSem.Text;
        DataModule1.query.Prepare;
        DataModule1.query.Open();
            if (DataModule1.query.Fields.AsInteger[0] <> 0)  then
            begin
                existOcc := true;
                    MessageDlg('Sorry, you have already added him on a room',mtError,[mbOK],0);
            end
              else
                existOcc := false;
end; }

procedure TfrmRMAss.incrementRoomOccupants(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;

function TfrmRMAss.add(bldg,rn:Integer):boolean;
var
  ret : boolean;
  maxOcc : integer;
  curOcc : integer;
  m : String;
begin
       If DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;
         m := 'Bldg: ' + IntToStr(bldg) + ' | Room: ' + IntToStr(rn);
         DataModule1.query.SQL.Clear;
         DataModule1.query.SQL.Add('SELECT a.MAXNOOCC, a.CURRENTNO FROM ROOM a WHERE a.RM_NO = :a and BUIL_ID = :B');
         DataModule1.query.Params.AsInteger[0] := rn;
         DataModule1.query.Params.AsInteger[1] := bldg;
         DataModule1.query.prepare;
         DataModule1.query.Open;

         maxOcc := DataModule1.query.Fields.AsInteger[0];
         curOcc := DataModule1.query.Fields.AsInteger[1];
         m := m +  ' Current: ' + IntToStr(curOcc) + ' | Max: ' + IntToStr(maxOcc);
         ShowMessage(m);
         if (curOcc >= maxOcc) then
         begin
              ret := false;

              ShowMessage('Selected room already full. Please select another room.');
         end
            else
            ret := true;

         add := ret;
end;

procedure TfrmRMAss.building;
begin
    If DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

         DataModule1.tr.StartTransaction;
         DataModule1.query.SQL.Clear;
         DataModule1.query.SQL.Add('Select DESCRIPTION');
         DataModule1.query.SQL.Add('From BUILDING');
         DataModule1.query.prepare;
         DataModule1.query.Open;
         cmbBuil.Items.BeginUpdate;
         cmbBuil.Clear;

         If DataModule1.query.Fields.IsNull[0] then Exit
            else begin
            while not DataModule1.query.Eof do begin
            cmbBuil.Items.Add(DataModule1.query.Fields.AsString[0]);
            DataModule1.query.Next;
         end;
        end;

            If DataModule1.tr.InTransaction then
              DataModule1.tr.Commit;
              cmbBuil.Items.EndUpdate;
end;

procedure TfrmRMAss.getPrimary;
var primaryKey : Integer;
begin
      primaryKey :=1;
        if DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;
          DataModule1.query.SQL.Clear();
          DataModule1.query.SQL.Add('SELECT OCC_ID');
          DataModule1.query.SQL.Add('FROM OCCUPANTS');
          DataModule1.query.Prepare;
          DataModule1.query.Open();

              if DataModule1.query.Fields.Eof then
                  primaryOccid := primaryKey
                    else begin
                        DataModule1.query.SQL.Clear;
                        DataModule1.query.SQL.Add('SELECT MAX(OCC_ID) FROM OCCUPANTS');
                        DataModule1.query.Prepare;
                        DataModule1.query.Open();
                        primaryOccid := DataModule1.query.Fields.AsInteger[0] + 1;
                end;
                      if  DataModule1.tr.InTransaction then
                          DataModule1.tr.Commit;


end;

procedure TfrmRMAss.updatestat;
begin
 if DataModule1.tr.InTransaction then
        DataModule1.tr.Commit;

            DataModule1.query.SQL.Clear;
            DataModule1.query.SQL.Add('UPDATE APPLICANTS');
            DataModule1.query.SQL.Add('SET APP_STATUS = :A WHERE APPID = :O');
            DataModule1.query.Params.AsString[0] := 'Admit';
            DataModule1.query.Params.AsString[1] := txtAppid.Text;
            DataModule1.query.Prepare;
            DataModule1.query.Open();

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

procedure TfrmRMAss.addOcc;
begin
     //  if (existOcc = true) then
        //    exit
         //   else
            
           getPrimary;
          if DataModule1.tr.InTransaction then
            DataModule1.tr.Commit;

            DataModule1.query.SQL.Clear;
            DataModule1.query.SQL.Add('INSERT INTO OCCUPANTS');
            DataModule1.query.SQL.Add('(APPID,BUIL_ID,RM_NO, OCC_STATUS,DATEADDED,ADDEDBY)');
            DataModule1.query.SQL.Add('VALUES(:a, :b, :c, :d, :e, :f)');
            DataModule1.query.Params.AsString[0] := txtAppid.Text;
            DataModule1.query.Params.AsInteger[1] := occid;
            DataModule1.query.Params.AsString[2] := cmbRoom.Text;
            DataModule1.query.Params.AsString[3] := txtOccStat.Text;
            DataModule1.query.Params.AsString[4] := DateToStr(Now());
            DataModule1.query.Params.AsString[5] := frmLogin.fullname;
            DataModule1.query.Prepare;
            DataModule1.query.Execute;

            if DataModule1.tr.InTransaction then
                  DataModule1.tr.Commit;
                      ShowMessage('Record successfully added');
                       updatestat;
end;



procedure TfrmRMAss.FormShow(Sender: TObject);
begin
      building;
      DateTimeToString(DateTimeformat, 'dddddd', now());
      Label7.Caption := DateTimeformat;
      StatusBar1.Panels.Items[1].Text := 'Date --' + label7.Caption;
end;

procedure TfrmRMAss.getBuilID;
begin
    if DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;

          DataModule1.query.SQL.Clear;
          DataModule1.query.SQL.Add('SELECT BUIL_ID FROM BUILDING WHERE DESCRIPTION = :a') ;
          DataModule1.query.Params.AsString[0] := trim(cmbBuil.Text);
          DataModule1.query.Prepare;
          DataModule1.query.Open();
          occid := DataModule1.query.Fields.AsInteger[0];

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

end.
