unit unitChangeRoom;

interface

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

type
  TfrmChangeRoom = class(TForm)
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    txtLname: TEdit;
    cmbRoom: TComboBox;
    BitBtn1: TBitBtn;
    bntSave: TBitBtn;
    txtFname: TEdit;
    Label5: TLabel;
    cmbBuil: TComboBox;
    StatusBar1: TStatusBar;
    Label8: TLabel;
    Label7: TLabel;
    txtBuilding: TEdit;
    txtRoom: TEdit;
    Label9: TLabel;
    Label10: TLabel;
    txtOccid: TEdit;
    Label6: TLabel;
    procedure cmbBuilChange(Sender: TObject);
    procedure bntSaveClick(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure decRoom(roomnum : integer);
    procedure incRoom(roomnum : Integer);
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
        occid : Integer;
        primary : Integer;
        rmnum : Integer;
        room : Integer;
        rn : Integer;
        bldg : Integer;
        roomnum: Integer;
        historyID: Integer;
        procedure loadBuil;
        procedure loadRoom;
        procedure getBuilId;
        procedure save;
        procedure UpdateBuil;
        function add(bldg,rn:Integer):boolean;
  end;

var
  frmChangeRoom: TfrmChangeRoom;
  systemDate : TDateTime;
  DateTimeformat : string;

implementation

uses DM, unitOcc;

{$R *.dfm}

procedure TfrmChangeRoom.loadBuil;
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 TfrmChangeRoom.loadRoom;
begin

   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 ');
          DataModule1.query.SQL.Add('AND b.DESCRIPTION = :a');
          DataModule1.query.Params.AsString[0] := trim(cmbBuil.Text);
          DataModule1.query.Prepare;
          DataModule1.query.Open();
           primary:= DataModule1.query.Fields.AsInteger[0];

          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;

procedure TfrmChangeRoom.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();
          ///Label7.Caption := IntTostr(DataModule1.query.Fields.AsInteger[0]);
          bldg := DataModule1.query.Fields.AsInteger[0];

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

end;

procedure TfrmChangeRoom.UpdateBuil;
begin
        if DataModule1.tr.Intransaction then
          DataModule1.tr.Commit;

          DataModule1.query.SQL.Clear;
          DataModule1.query.SQL.Add('UPDATE OCCUPANTS SET BUIL_ID = :A WHERE OCC_ID = :B');
          //DataModule1.query.Params.AsString[0] := Label7.Caption;
          DataModule1.query.Params.AsInteger[0] := bldg;
          DataModule1.query.Params.AsString[1] := label6.Caption;
          DataModule1.query.Prepare;
          DataModule1.query.Execute;

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

procedure TfrmChangeRoom.save;
//var count : integer;
begin
    getBuilId;
    if DataModule1.tr.InTransaction then
      DataModule1.tr.Commit;

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('UPDATE OCCUPANTS SET RM_NO = :A WHERE OCC_ID = :b');
        DataModule1.query.Params.AsString[0] := cmbRoom.Text;
        DataModule1.query.Params.AsString[1] := Label6.Caption;
        DataModule1.query.Prepare;
        DataModule1.query.Execute;

            if DataModule1.tr.InTransaction then
              DataModule1.tr.Commit;
               UpdateBuil;
               ShowMessage('Successfully change occupants room number.');
                Close();

                   DataModule1.query.SQL.Clear;
                   DataModule1.query.SQL.Add('INSERT INTO RM_HIST(OCC_ID,BUIL_ID,RM_NO,DATECHANGED)VALUES(:a, :b, :c, :d)');
                   DataModule1.query.Params.AsString[0] := txtOccid.Text;
                   DataModule1.query.Params.AsInteger[1] := bldg;
                   DataModule1.query.Params.AsString[2] := txtRoom.Text;
                   DataModule1.query.Params.AsString[3] := DateToStr(Now());
                   DataModule1.query.Prepare;
                   DataModule1.query.Execute;



end;

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

         DataModule1.query.SQL.Clear;
         DataModule1.query.SQL.Add('SELECT CURRENTNO FROM ROOM WHERE 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 TfrmChangeRoom.incRoom(roomnum : integer);
var count : integer;
begin
       If DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;

         DataModule1.query.SQL.Clear;
         DataModule1.query.SQL.Add('SELECT  CURRENTNO FROM ROOM WHERE 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 TfrmChangeRoom.cmbBuilChange(Sender: TObject);
begin
  getBuilId;
   loadRoom;
end;

function TfrmChangeRoom.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 TfrmChangeRoom.bntSaveClick(Sender: TObject);
var
room : Integer;
rn : Integer;
begin
     rn:= StrToInt(cmbRoom.Text);
         if (add(bldg,rn) = false) then
              exit
          else
            save;
            room := StrToInt(cmbRoom.Text);
        // decRoom(room);
            incRoom(room);


             Close;
end;

procedure TfrmChangeRoom.FormActivate(Sender: TObject);
begin
      DateTimeToString(DateTimeformat, 'dddddd', now());
      Label8.Caption := DateTimeformat;
      StatusBar1.Panels.Items[1].Text := 'Date -- ' + Label8.Caption;
end;

procedure TfrmChangeRoom.FormShow(Sender: TObject);
begin
      loadBuil;
end;

end.
