unit unitAddAssignment;

interface

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

type
  TfrmAddCleaner = class(TForm)
    StatusBar1: TStatusBar;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    dtpDate: TDateTimePicker;
    cmbArea: TComboBox;
    txtLeader: TEdit;
    cmbRoom: TComboBox;
    cmbBuil: TComboBox;
    btnSave: TBitBtn;
    btnCancel: TBitBtn;
    procedure cmbBuilChange(Sender: TObject);
    procedure cmbRoomChange(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
       foreignBuilID : Integer;
       pkAssID : Integer;
      procedure getBuilID;
      procedure addAss;
      procedure getPK;
      procedure loadBuilding;
    { Public declarations }
  end;

var
  frmAddCleaner: TfrmAddCleaner;

implementation

uses DM, unitLogin, unitCleanerAss;

{$R *.dfm}

procedure TfrmAddCleaner.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();
          foreignBuilID := DataModule1.query.Fields.AsInteger[0];

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

procedure TfrmAddCleaner.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 ');
          DataModule1.query.SQL.Add('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;

procedure TfrmAddCleaner.cmbRoomChange(Sender: TObject);
begin
     if DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;

          DataModule1.query.SQL.Clear;
          DataModule1.query.SQL.Add('SELECT a.FULLNAME') ;
          DataModule1.query.SQL.Add('FROM STUDENTPROF a,OCCUPANTS b');
          DataModule1.query.SQL.Add('WHERE b.RM_NO = :a And b.OCC_POSITION = :b and a.APPID = b.APPID');
          DataModule1.query.Params.AsString[0] := cmbRoom.Text;
          DataModule1.query.Params.AsString[1] := 'Leader';
          DataModule1.query.Prepare;
          DataModule1.query.Open();
          txtLeader.Text := DataModule1.query.Fields.AsString[0];

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

procedure TfrmAddCleaner.getPK;
var rm_assID : Integer;
begin
      rm_assID :=1;
        if DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;
          DataModule1.query.SQL.Clear();
          DataModule1.query.SQL.Add('SELECT RM_ASSID');
          DataModule1.query.SQL.Add('FROM ROOM_ASSIGNMENT');
          DataModule1.query.Prepare;
          DataModule1.query.Open();

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

end;
procedure TfrmAddCleaner.addAss;
begin
      if (cmbBuil.Text = '') then
      begin
          MessageDlg('Please select building.',mtError,[mbOK],0);
          cmbBuil.SetFocus;
      end
      else if (txtLeader.Text = '') then
      begin
            MessageDlg('Please select a room leader.',mtError,[mbOK],0);
            cmbRoom.SetFocus;
      end
      else if (cmbRoom.Text = '') then
      begin
            MessageDlg('Please select a room that will be assign in the cleanings.',mtError,[mbOK],0);
            cmbRoom.SetFocus;
      end
      else if (cmbArea.Text = '') then
      begin
            MessageDlg('Please select their assignment area.',mtError,[mbOK],0);
            cmbArea.SetFocus;
      end
          else begin
      getPK;
      if DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;

          DataModule1.query.SQL.Clear;
          DataModule1.query.SQL.Add('INSERT INTO ROOM_ASSIGNMENT (RM_ASSID, BUIL_ID, RM_NO, NAME, DATE_CLEANED, DESIGN_AREA, DATEADD, CREATEDBY) VALUES(:a, :b, :c, :d, :e, :f, :g, :h)');
          DataModule1.query.Params.AsInteger[0] := pkAssID;
          DataModule1.query.Params.AsInteger[1] := foreignBuilID;
          DataModule1.query.Params.AsString[2] := cmbRoom.Text;
          DataModule1.query.Params.AsString[3] := txtLeader.Text;
          DataModule1.query.Params.AsString[4] := DateToStr(dtpDate.Date);
          DataModule1.query.Params.AsString[5] := cmbArea.Text;
          DataModule1.query.Params.AsString[6] := DateToStr(Now());
          DataModule1.query.Params.AsString[7] := frmLogin.fullname;

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

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

              ShowMessage('Successfully added');
              frmCleanerAssign.loadAss;
              Close();
              end
end;

procedure TfrmAddCleaner.btnSaveClick(Sender: TObject);
begin
       addAss;
end;

procedure TfrmAddCleaner.loadBuilding;
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 TfrmAddCleaner.FormShow(Sender: TObject);
begin
    loadBuilding;
        if DataModule1.tr.InTransaction then
          DataModule1.tr.Commit;

        DataModule1.tr.StartTransaction;
        DataModule1.query.SQL.Clear;
        DataModule1.query.SQL.Add('SELECT RM_NO FROM ROOM');
        DataModule1.query.Prepare;
        DataModule1.query.Open();
        cmbRoom.Items.BeginUpdate;
        cmbRoom.Clear;

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

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

end.
