unit unitRMOcc;

interface

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

type
  TfrmRMOcc = class(TForm)
    Label1: TLabel;
    lvRMOcc: TListView;
    PopupMenu1: TPopupMenu;
    SetLeader1: TMenuItem;
    procedure lvRMOccCustomDrawItem(Sender: TCustomListView;
      Item: TListItem; State: TCustomDrawState; var DefaultDraw: Boolean);
    procedure SetLeader1Click(Sender: TObject);
  private
    { Private declarations }
  public
    primaryKey : Integer;
    building : String;
    rmNo : Integer;
    procedure loadOccupants(RM_NO : Integer);
    procedure remove(roomNum : Integer);
    procedure setLeader;
    function existingLeader():boolean;
  end;

var
  frmRMOcc: TfrmRMOcc;

implementation

uses DM;

{$R *.dfm}

procedure TfrmRMOcc.loadOccupants(RM_NO : Integer);
var newstate : TListItem;
begin
      if DataModule1.tr.InTransaction then
           DataModule1.tr.Commit;

              DataModule1.query.SQL.Clear;
              DataModule1.query.SQL.Add('SELECT a.BUIL_ID, c.DESCRIPTION, a.RM_NO, a.OCC_ID, b.app_lname, b.app_fname, a.OCC_POSITION, a.OCC_STATUS FROM OCCUPANTS a, STUDENTPROF b, BUILDING C WHERE a.RM_NO = :a and a.OCC_STATUS = :B AND b.APPID = a.APPID AND c.BUIL_ID = a.BUIL_ID');
              DataModule1.query.Params.AsString[0] := IntToStr(RM_NO);
              DataModule1.query.Params.AsString[1] := '1';
              DataModule1.query.Prepare;
              DataModule1.query.Open();
              lvRMOcc.Items.BeginUpdate;
              lvRMOcc.Items.Clear;

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

                      newState := lvRMOcc.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]);
                      DataModule1.query.Next;
                  end;
                      if DataModule1.tr.InTransaction then
                        DataModule1.tr.Commit;
                          lvRMOcc.Items.EndUpdate;
end;

procedure TfrmRMOcc.remove(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;
         loadOccupants(StrToInt(lvRMOcc.ItemFocused.Caption));

end;

function TfrmRMOcc.existingLeader() : boolean;
begin
      building := lvRMOcc.ItemFocused.Caption;
      rmNo := (StrToInt(lvRMOcc.ItemFocused.SubItems[1]));

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

        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT COUNT(a.OCC_ID) FROM OCCUPANTS a, BUILDING b');
        DataModule1.query.SQL.Add('where a.OCC_POSITION = :a and a.BUIL_ID = b.BUIL_ID And B.DESCRIPTION = :b and a.RM_NO = :c ');
        DataModule1.query.Params.AsString[0] := 'Leader';
        DataModule1.query.Params.AsString[1] := lvRMOcc.ItemFocused.SubItems[0];
        DataModule1.query.Params.AsInteger[2] := StrToInt(lvRMOcc.ItemFocused.SubItems[1]);
        DataModule1.query.Prepare;
        DataModule1.query.Open();
            if (DataModule1.query.Fields.AsInteger[0] <> 0)  then
            begin
                existingLeader := true;
                    MessageDlg('Sorry, there is already an existing leader',mtError,[mbOK],0);
            end
              else
                existingLeader := false;
end;

procedure TfrmRMOcc.setLeader;
begin
      if MessageDlg('Are you sure you want to set a leader?',mtConfirmation,[mbYes,mbNo],0) = mryes then
      begin
          if (existingLeader = true) then
            exit
        else begin

          primaryKey := StrToInt(lvRMOcc.ItemFocused.Caption);

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

            DataModule1.query.SQL.Clear;
            DataModule1.query.SQL.Add('UPDATE OCCUPANTS SET OCC_POSITION = :A WHERE OCC_ID = :B');
            DataModule1.query.Params.AsString[0] := 'Leader';
            DataModule1.query.Params.AsInteger[1] := StrToInt(lvRMOcc.ItemFocused.SubItems[2]);
            DataModule1.query.Prepare;
            DataModule1.query.Open();

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

              ShowMessage ('Successfully Updated!');
      end
      end
      else
end;


procedure TfrmRMOcc.lvRMOccCustomDrawItem(Sender: TCustomListView;
  Item: TListItem; State: TCustomDrawState; var DefaultDraw: Boolean);
begin
     if (Item.SubItems[5] = 'Leader') then
        lvRMOcc.Canvas.Font.Color := clBlue;
end;

procedure TfrmRMOcc.SetLeader1Click(Sender: TObject);
begin
      setLeader;
end;

end.
