main_u.pas

unit main_u;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, StdCtrls, ExtCtrls, XPMan, DB, DBTables, Mask,
  DBCtrls, Grids, DBGrids, Contnrs, Data.DbxSqlite, Data.FMTBcd,
  DataSnap.DBClient, Vcl.Bind.Grid, System.Rtti, System.Bindings.Outputs,
  Vcl.Bind.Editors, Data.Bind.EngExt, Vcl.Bind.DBEngExt, Data.Bind.Components,
  Data.Bind.Grid, Data.Bind.DBScope, Data.Bind.DBXScope, Data.SqlExpr,
  Vcl.Buttons, Vcl.Bind.Navigator, Datasnap.Provider,
  Vcl.PlatformDefaultStyleActnCtrls, System.Actions, Vcl.ActnList, Vcl.ActnMan,
  Vcl.DBLookup, TTimeTable, frxClass, frxDBSet, export_dlg, frxExportXLS,
  exceptns;

type
  TDim = (Teacher, Room, Group);

  TMainForm = class(TForm)
    btn_Generate: TButton;
    pgc_MainPages: TPageControl;
    ts_Viewer: TTabSheet;
    grp_Filters: TGroupBox;
    ts_Avialability: TTabSheet;
    grp_AvType: TGroupBox;
    lbl_AvOwner: TLabel;
    lbl1: TLabel;
    lbl2: TLabel;
    lbl3: TLabel;
    cbb_AvType: TComboBox;
    pnl_Av: TPanel;
    GridPanel5: TGridPanel;
    Panel5: TPanel;
    Label6: TLabel;
    Panel6: TPanel;
    Label7: TLabel;
    ts_Load: TTabSheet;
    ts_Catalogs: TTabSheet;
    SQLConnection: TSQLConnection;
    DBGrid_Catalogs: TDBGrid;
    Panel4: TPanel;
    Commit: TButton;
    dbnav_catalogs: TDBNavigator;
    FlowPanel1: TFlowPanel;
    btn_CatRoom: TButton;
    btn_CatTeacher: TButton;
    btn_CatGroup: TButton;
    btn_CatSubject: TButton;
    btn_CatSubjType: TButton;
    ActionManager1: TActionManager;
    act_OpenCatRoom: TAction;
    act_OpenCatTeacher: TAction;
    act_OpenCatGroup: TAction;
    act_OpenCatSubject: TAction;
    act_OpenCatSubjectType: TAction;
    Panel3: TPanel;
    DBNavigator2: TDBNavigator;
    Button2: TButton;
    ds_Load: TSQLDataSet;
    ds_Teacher: TSQLDataSet;
    ds_Teachertecher_id: TLargeintField;
    ds_Teacherteacher_name: TWideStringField;
    ds_Loadload_id: TLargeintField;
    ds_Loadteacher_id: TLargeintField;
    ds_Loadsubject_id: TLargeintField;
    ds_Loadgroup_id: TLargeintField;
    ds_Loadsubjecttype_id: TLargeintField;
    ds_Loadnum_hours: TLargeintField;
    dsp_Teacher: TDataSetProvider;
    cds_Teacher: TClientDataSet;
    dsrc_Teacher: TDataSource;
    cds_Teachertecher_id: TLargeintField;
    cds_Teacherteacher_name: TWideStringField;
    dsrc_Room: TDataSource;
    dsrc_Group: TDataSource;
    dsrc_Subject: TDataSource;
    dsrc_SubjectType: TDataSource;
    ds_Room: TSQLDataSet;
    ds_Group: TSQLDataSet;
    ds_Subject: TSQLDataSet;
    ds_SubjectType: TSQLDataSet;
    dsp_Room: TDataSetProvider;
    dsp_Group: TDataSetProvider;
    dsp_Subject: TDataSetProvider;
    dsp_SubjectType: TDataSetProvider;
    ds_Roomroom_id: TLargeintField;
    ds_Roomroom_name: TWideStringField;
    ds_Roomroom_capacity: TLargeintField;
    ds_Roomroom_pc_flag: TLargeintField;
    cds_Room: TClientDataSet;
    cds_Group: TClientDataSet;
    cds_Subject: TClientDataSet;
    cds_SubjectType: TClientDataSet;
    ds_Groupgroup_id: TLargeintField;
    ds_Groupgroup_name: TWideStringField;
    ds_Groupgroup_size: TLargeintField;
    cds_Roomroom_id: TLargeintField;
    cds_Roomroom_name2: TWideStringField;
    cds_Roomroom_capacity2: TLargeintField;
    cds_Roomroom_pc_flag2: TLargeintField;
    cds_Groupgroup_id: TLargeintField;
    cds_Groupgroup_name: TWideStringField;
    cds_Groupgroup_size: TLargeintField;
    ds_Subjectsubject_id: TLargeintField;
    ds_Subjectsubject_name: TWideStringField;
    cds_SubjectTypesubjecttype_id: TLargeintField;
    cds_SubjectTypesubjecttype_name: TWideStringField;
    cds_Subjectsubject_id: TLargeintField;
    cds_Subjectsubject_name: TWideStringField;
    ds_SubjectTypesubjecttype_id: TLargeintField;
    ds_SubjectTypesubjecttype_name: TWideStringField;
    avcb_Teacher: TDBLookupComboBox;
    avcb_Group: TDBLookupComboBox;
    avcb_Room: TDBLookupComboBox;
    av_week1_grid: TGridPanel;
    Label18: TLabel;
    Label19: TLabel;
    Label20: TLabel;
    Label21: TLabel;
    Label22: TLabel;
    Label23: TLabel;
    av_week2_grid: TGridPanel;
    Label24: TLabel;
    Label25: TLabel;
    Label26: TLabel;
    Label27: TLabel;
    Label28: TLabel;
    Label29: TLabel;
    act_chk_avlbl: TAction;
    ds_AvlBlt: TSQLDataSet;
    ds_LoadNeedPc: TWideMemoField;
    DBGrid_Load: TDBGrid;
    dsp_Load: TDataSetProvider;
    cds_Load: TClientDataSet;
    dsrc_Load: TDataSource;
    cds_Loadload_id: TLargeintField;
    cds_Loadteacher_id: TLargeintField;
    cds_Loadsubject_id: TLargeintField;
    cds_Loadgroup_id: TLargeintField;
    cds_Loadsubjecttype_id: TLargeintField;
    cds_Loadnum_hours: TLargeintField;
    cds_LoadNeedPc: TIntegerField;
    cds_LoadTeacher: TStringField;
    cds_LoadGroup: TStringField;
    cds_LoadSubject: TStringField;
    cds_LoadLessontype: TStringField;
    upd_Av_Button: TButton;
    cb_Teacher: TCheckBox;
    cb_Group: TCheckBox;
    cb_Room: TCheckBox;
    cb_Subject: TCheckBox;
    lcb_vwTeacher: TDBLookupComboBox;
    lcb_vwGroup: TDBLookupComboBox;
    lcb_vwRoom: TDBLookupComboBox;
    lcb_Subject: TDBLookupComboBox;
    Button3: TButton;
    GridPanel1: TGridPanel;
    Panel1: TPanel;
    Label2: TLabel;
    Panel2: TPanel;
    Label3: TLabel;
    ds_TimeTable: TSQLDataSet;
    ds_TimeTableteacher_id: TIntegerField;
    ds_TimeTableTeacher: TStringField;
    ds_TimeTabletime_id: TLargeintField;
    ds_TimeTableload_id: TLargeintField;
    ds_TimeTableroom_id: TWideMemoField;
    ds_TimeTablegroup_id: TIntegerField;
    ds_TimeTableGroup: TStringField;
    ds_TimeTablesubject_id: TIntegerField;
    ds_TimeTableSubject: TStringField;
    ds_TimeTablesubject_type_id: TIntegerField;
    ds_TimeTableSubjectType: TStringField;
    ListView1: TListView;
    ListView2: TListView;
    GroupBox1: TGroupBox;
    ds_TimeTableWeek: TIntegerField;
    ds_DimTIme: TSQLDataSet;
    ds_DimTImetime_id: TLargeintField;
    ds_DimTImeweek_num: TLargeintField;
    ds_DimTImeweekday_num: TLargeintField;
    ds_DimTImeweek_day_name: TWideStringField;
    ds_DimTImelesson_num: TLargeintField;
    ds_TimeTableDay: TStringField;
    ds_TimeTableLesson: TIntegerField;
    ds_ReportTeacher: TSQLDataSet;
    frds_ReportTeacher: TfrxDBDataset;
    ds_ReportTeacherweek_num: TLargeintField;
    ds_ReportTeacherlesson_num: TLargeintField;
    ds_ReportTeacherteacher_name: TWideStringField;
    ds_ReportTeacherColumn3: TWideMemoField;
    ds_ReportTeacherColumn4: TWideMemoField;
    ds_ReportTeacherColumn5: TWideMemoField;
    ds_ReportTeacherColumn6: TWideMemoField;
    ds_ReportTeacherColumn7: TWideMemoField;
    ds_ReportTeacherColumn8: TWideMemoField;
    frxReportTeacher: TfrxReport;
    ds_ReportGroup: TSQLDataSet;
    ds_ReportGroupweek_num: TLargeintField;
    ds_ReportGrouplesson_num: TLargeintField;
    ds_ReportGroupgroup_name: TWideStringField;
    ds_ReportGroupColumn3: TWideMemoField;
    ds_ReportGroupColumn4: TWideMemoField;
    ds_ReportGroupColumn5: TWideMemoField;
    ds_ReportGroupColumn6: TWideMemoField;
    ds_ReportGroupColumn7: TWideMemoField;
    ds_ReportGroupColumn8: TWideMemoField;
    frx_ReportGroup: TfrxDBDataset;
    Button1: TButton;
    Button4: TButton;
    frxReportGroup: TfrxReport;
    Button5: TButton;
    Button6: TButton;
    ds_ReportRoom: TSQLDataSet;
    LargeintField1: TLargeintField;
    LargeintField2: TLargeintField;
    WideStringField1: TWideStringField;
    WideMemoField1: TWideMemoField;
    WideMemoField2: TWideMemoField;
    WideMemoField3: TWideMemoField;
    WideMemoField4: TWideMemoField;
    WideMemoField5: TWideMemoField;
    WideMemoField6: TWideMemoField;
    frx_ReportRoom: TfrxDBDataset;
    frxReportRoom: TfrxReport;
    procedure FormResize(Sender: TObject);
    procedure cbb_AvTypeChange(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure DBGrid_CatalogsDrawDataCell(Sender: TObject; const Rect: TRect;
      Field: TField; State: TGridDrawState);
    procedure CommitClick(Sender: TObject);
    procedure DBGrid_CatalogsDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DBGrid_CatalogsColEnter(Sender: TObject);
    procedure fill_Aviability;
    procedure act_OpenCatRoomExecute(Sender: TObject);
    procedure act_OpenCatTeacherExecute(Sender: TObject);
    procedure act_OpenCatSubjectExecute(Sender: TObject);
    procedure act_OpenCatSubjectTypeExecute(Sender: TObject);
    procedure DBGrid_CatalogsCellClick(Column: TColumn);
    procedure act_OpenCatGroupExecute(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure avcb_TeacherCloseUp(Sender: TObject);
    procedure act_chk_avlblExecute(Sender: TObject);
    procedure av_cbClick(Sender: TObject);
    procedure avcb_GroupCloseUp(Sender: TObject);
    procedure avcb_RoomCloseUp(Sender: TObject);
    procedure DBGrid_LoadColEnter(Sender: TObject);
    procedure dsrc_LoadDataChange(Sender: TObject; Field: TField);
    procedure DBGrid_LoadDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DBGrid_LoadCellClick(Column: TColumn);
    procedure upd_Av_ButtonClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure cb_TeacherClick(Sender: TObject);
    procedure cb_GroupClick(Sender: TObject);
    procedure cb_RoomClick(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure cb_SubjectClick(Sender: TObject);
    procedure btn_GenerateClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);

  private

  public
    procedure LoadGetData;
    procedure av_LoadCheckBoxValues;
    procedure av_enable_Chbxs(state : boolean = true);

    procedure drawTimeTable;
    procedure initialize_viewer;
    procedure clean_viewer;
    procedure save_generatedToDb(time_table : T_TimeTable);
    { Public declarations }
  end;

var
  MainForm: TMainForm;
  vw_Labels    : array of array of array of TLabel;
  ViewerWeekLabels : TObjectList;

  w_count : Integer;
  d_count : Integer;
  l_count : Integer;

  av_cbxes : array of array of array of TCheckBox;   //Avialability checkbox controls matrix
  GAv_Type : TDim; GAv_index : Integer; //Current selected

  Av_Type : TDim;
  Av_Index : Integer;

  temp : T_TimeTable;

implementation

{$R *.dfm}

// CATALOGS
// Group table
procedure TMainForm.act_chk_avlblExecute(Sender: TObject);
  var flag_value : Integer;
begin
    if (Sender AS TCheckBox).Checked then flag_value := 1
    else flag_value := 0;

    ShowMessage(INTToStr((Sender AS TCheckBox).Tag) + ' ' + IntToStr(flag_value));
end;

procedure TMainForm.act_OpenCatGroupExecute(Sender: TObject);
begin
    DBGrid_Catalogs.DataSource := dsrc_Group;
    dbnav_catalogs.DataSource := dsrc_Group;
end;

//Room table
procedure TMainForm.act_OpenCatRoomExecute(Sender: TObject);
begin
    DBGrid_Catalogs.DataSource := dsrc_Room;
    dbnav_catalogs.DataSource := dsrc_Room;
end;

//Teacher table
procedure TMainForm.act_OpenCatTeacherExecute(Sender: TObject);
begin
    DBGrid_Catalogs.DataSource := dsrc_Teacher;
    dbnav_catalogs.DataSource := dsrc_Teacher;
end;

procedure TMainForm.Button1Click(Sender: TObject);
var
 ed : TexportDlg;
begin
  frxReportTeacher.ShowReport(false);
end;

procedure TMainForm.Button2Click(Sender: TObject);
var
  X, Y: Integer;
begin
  // Client data set has provider do update.
  (DBGrid_Load.DataSource.DataSet AS TClientDataSet).ApplyUpdates(-1);
  Y := Self.Top + 30;
  X := Self.Left + 30;

  // Ask user if data should be refreshed in grid display.
  if MessageDlgPos('Refresh?',mtCustom, [mbYes, mbNo], 0, X, Y) = mrYes then
  begin
      DBGrid_Load.DataSource.DataSet.Close;
      DBGrid_Load.DataSource.DataSet.Open;
  end;
end;

// Subject table
procedure TMainForm.act_OpenCatSubjectExecute(Sender: TObject);
begin
    DBGrid_Catalogs.DataSource := dsrc_Subject;
    dbnav_catalogs.DataSource := dsrc_Subject;
end;

// Lesson type table
procedure TMainForm.act_OpenCatSubjectTypeExecute(Sender: TObject);
begin
    DBGrid_Catalogs.DataSource := dsrc_SubjectType;
    dbnav_catalogs.DataSource := dsrc_SubjectType;
end;
// END Catalogs Navigation

// Handing of checkboxes inside DB GRID
// Checkbox render
procedure DrawGridCheckBox(Canvas: TCanvas; Rect: TRect; Checked: boolean);
var
  DrawFlags: Integer;
begin
  Canvas.TextRect(Rect, Rect.Left + 1, Rect.Top + 1, ' ');
  DrawFrameControl(Canvas.Handle, Rect, DFC_BUTTON, DFCS_BUTTONPUSH or DFCS_ADJUSTRECT);
  DrawFlags := DFCS_BUTTONCHECK or DFCS_ADJUSTRECT;// DFCS_BUTTONCHECK
  if Checked then
    DrawFlags := DrawFlags or DFCS_CHECKED;
  DrawFrameControl(Canvas.Handle, Rect, DFC_BUTTON, DrawFlags);
end;

//Putting inside grid
procedure TMainForm.DBGrid_CatalogsDrawColumnCell(Sender: TObject; const Rect: TRect;
  DataCol: Integer; Column: TColumn; State: TGridDrawState);
BEGIN
  if Column.FieldName = 'room_pc_flag' then
    if Column.Field.AsInteger = 1 then
      DrawGridCheckBox(DBGrid_Catalogs.Canvas, Rect, true)
    else
      DrawGridCheckBox(DBGrid_Catalogs.Canvas, Rect, false)
end;

//Change checkbox grid value
procedure TMainForm.DBGrid_CatalogsCellClick(Column: TColumn);
begin
  if Column.Field.FieldName = 'room_pc_flag'  then
  begin
    cds_Room.Edit;
    if (Column.Field.Value = 1) then Column.Field.Value := 0
    else Column.Field.Value := 1;
  end;
end;

//Disabling of classical editing
procedure TMainForm.DBGrid_CatalogsColEnter(Sender: TObject);
begin
  with TDBGrid(Sender) do
    if SelectedField.FieldName = 'room_pc_flag' then
      Options := Options - [dgEditing]
    else
      Options := Options + [dgEditing]
end;
// END of check boxes in grid

// HANDLING OF UNKNOWN DATA
procedure TMainForm.DBGrid_CatalogsDrawDataCell(Sender: TObject; const Rect: TRect;
  Field: TField; State: TGridDrawState);
var
  Grid : TStringGrid;
  Texto : String;
  Rectangulo : TRect;
begin
  Rectangulo:=Rect;
  Grid := TStringGrid(Sender);
  if Field.IsBlob then begin
    Grid.Canvas.FillRect(Rect);
    Texto := Field.AsString;
    DrawText( Grid.Canvas.Handle,
                      PChar(Texto),
                      StrLen(PChar(Texto)),
                      Rectangulo,
                      DT_WORDBREAK);
    end;
end;

procedure TMainForm.DBGrid_LoadCellClick(Column: TColumn);
begin
  if Column.Field.FieldName = 'NeedPc'  then
  begin
  if not (cds_Load.State in [dsEdit, dsInsert]) then
    begin
      cds_Load.Edit;
    end;

    if Column.Field.IsNull then Column.Field.Value := 0;
    if (Column.Field.Value = 1) then Column.Field.Value := 0
    else Column.Field.Value := 1;
  end;
end;

procedure TMainForm.DBGrid_LoadColEnter(Sender: TObject);
begin
  with TDBGrid(Sender) do
    if SelectedField.FieldName = 'NeedPc' then
      Options := Options - [dgEditing]
    else
      Options := Options + [dgEditing]
end;


procedure TMainForm.DBGrid_LoadDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
begin
  if Column.FieldName = 'NeedPc' then
    if not Column.Field.IsNull then
    begin
      if Column.Field.AsInteger = 1 then
        DrawGridCheckBox(DBGrid_Load.Canvas, Rect, true)
      else
        DrawGridCheckBox(DBGrid_Load.Canvas, Rect, false);
    end
    else
      DrawGridCheckBox(DBGrid_Load.Canvas, Rect, false);
end;

procedure TMainForm.drawTimeTable;
begin

end;

procedure TMainForm.dsrc_LoadDataChange(Sender: TObject; Field: TField);
begin

end;

// END HANDING OF UNKNOWN DATA

//Commit button
procedure TMainForm.CommitClick(Sender: TObject);
var
  X, Y: Integer;
begin
  // Client data set has provider do update.
  (DBGrid_Catalogs.DataSource.DataSet AS TClientDataSet).ApplyUpdates(-1);
  Y := Self.Top + 30;
  X := Self.Left + 30;

  // Ask user if data should be refreshed in grid display.
  if MessageDlgPos('Refresh?',mtCustom, [mbYes, mbNo], 0, X, Y) = mrYes then
  begin
      DBGrid_Catalogs.DataSource.DataSet.Close;
      DBGrid_Catalogs.DataSource.DataSet.Open;
  end;
end;
// END OF CATALOGS

// LOAD PAGE LOGIC
procedure TMainForm.LoadGetData;
begin
    cds_Teacher.Open;
    cds_Load.Open;
end;
procedure TMainForm.save_generatedToDb(time_table : T_TimeTable);
var
  ClearQuery : String;
  InsertQuery : String;
  w, d, l, x : integer;
  temp_lesson : TLesson;
  i, room_ID: Integer;
begin
    ClearQuery := 'DELETE FROM f_timeTable;';
    InsertQuery := 'INSERT INTO f_timeTable ("time_id", "load_id", "room_id") VALUES ';

    for i := 0 to pred(time_table.AllLessons.Count) do
    begin
        temp_lesson := time_table.AllLessons.Items[i] AS TLesson;

        if (temp_lesson.Room <> nil) then room_id := temp_lesson.Room.RoomID
        else room_ID := -1;


        InsertQuery := InsertQuery + ' ('
                + IntToStr(temp_lesson.Parent.TimeID) + ', '
                + IntToStr(temp_lesson.LoadItem.LoadID) + ', '
                + IntToStr(room_ID)
                + '),';
                x := x + 1;
    end;

    InsertQuery[Length(InsertQuery)] := ';';
    SQLConnection.ExecuteDirect(ClearQuery);
    if x > 0 then SQLConnection.ExecuteDirect(InsertQuery);

end;

procedure TMainForm.upd_Av_ButtonClick(Sender: TObject);
var
  w, d, l: Integer;
  Query : String;
begin
  Av_Type := TDim(cbb_AvType.ItemIndex);

  case Av_Type of
    Teacher:
      begin
            Query := 'INSERT OR REPLACE INTO "av_Teacher" ("time_id", "Teacher_id", "flag") VALUES ' ;
            for w := 0 to pred(w_count) do
              for d := 0 to pred(d_count) do
                for l := 0 to pred(l_count) do
                begin
                    Query := Query  + '('
                    + IntToStr( av_cbxes[w,d,l].Tag ) + ', '
                    + IntToStr(Av_index) + ', '
                    + IntToStr(Integer(av_cbxes[w,d,l].Checked)) + ')';
                    if ((w = pred(w_count)) AND (d = pred(d_count)) AND (l = pred(l_count))) then
                      Query := Query + ';'
                    else Query := Query + ',';
                end;
      end;
    Room:
      begin
         // Av_Index := avcb_Room.KeyValue;
          Query := 'INSERT OR REPLACE INTO "av_Room" ("time_id", "Room_id", "flag") VALUES ';
            for w := 0 to pred(w_count) do
              for d := 0 to pred(d_count) do
                for l := 0 to pred(l_count) do
                begin
                    Query := Query  + '('
                    + IntToStr( av_cbxes[w,d,l].Tag ) + ', '
                    + IntToStr(Av_index) + ', '
                    + IntToStr(Integer(av_cbxes[w,d,l].Checked)) + ')';
                    if ((w = pred(w_count)) AND (d = pred(d_count)) AND (l = pred(l_count))) then
                      Query := Query + ';'
                    else Query := Query + ',';
                end;
      end;
    Group:
      begin
          //Av_Index := avcb_Group.KeyValue;
          Query := 'INSERT OR REPLACE INTO "av_Group" ("time_id", "Group_id", "flag") VALUES ';
            for w := 0 to pred(w_count) do
              for d := 0 to pred(d_count) do
                for l := 0 to pred(l_count) do
                begin
                    Query := Query  + '('
                    + IntToStr( av_cbxes[w,d,l].Tag ) + ', '
                    + IntToStr(Av_index) + ', '
                    + IntToStr(Integer(av_cbxes[w,d,l].Checked)) + ')';
                    if ((w = pred(w_count)) AND (d = pred(d_count)) AND (l = pred(l_count))) then
                      Query := Query + ';'
                    else Query := Query + ',';
                end;
      end;
  end;
  SQLConnection.ExecuteDirect(Query);
  (Sender AS TButton).Enabled := false;
end;

// END OF LOAD PAGE


//AVIALABILITY

//Initialize check boxes
procedure TMainForm.fill_Aviability;
  var i, j, k, x  : Integer;
      temp_cb : TCheckBox;
      temp_b : TButton;
begin
     SetLength(av_cbxes, w_count, d_count, l_count);
      for j := 0 to pred(l_count) do
      begin
        for k := 0 to pred(d_count) do
        begin
          temp_cb := TCheckBox.Create(Self);
          temp_cb.Tag := 1*100 + (k + 1)*10 + (j + 1) ;
          temp_cb.Width := 15;
          temp_cb.OnClick := av_cbClick;
          temp_cb.Caption := ''; //IntToStr(temp_cb.tag);
          temp_cb.Parent := av_week1_grid;
          //av_week1_grid.ControlCollection.AddControl(temp_cb);
          temp_cb.Enabled := False;
          av_cbxes[0,k,j] := temp_cb;
        end;
      end;
      for j := 0 to pred(l_count) do
      begin
        for k := 0 to pred(d_count) do
        begin
          temp_cb := TCheckBox.Create(Self);
          temp_cb.Tag := 2*100 + (k + 1)*10 + (j + 1) ;
          temp_cb.Width := 15;
          temp_cb.OnClick := av_cbClick;
          temp_cb.Caption := ''; //IntToStr(temp_cb.tag);
          temp_cb.Parent := av_week2_grid;
          //av_week2_grid.ControlCollection.AddControl(temp_cb);
          av_cbxes[1,k,j] := temp_cb;
          temp_cb.Enabled := False;
        end;
      end;
end;
// END initialization

//ACTIVATE CHECKBOXES
procedure TMainForm.av_enable_Chbxs( state : boolean = true );
var
  I, J, K: Integer;
begin
  for I := 0 to pred(w_count) do
    for J := 0 to pred(d_count) do
     for K := 0 to pred(l_count) do
      begin
        av_cbxes[I,J,K].Enabled := state;
        av_cbxes[I,J,K].Checked := False;
      end;
end;
// END Activate



//LOADING Data according to an attribute
procedure TMainForm.av_LoadCheckBoxValues;
  var Query : String;
      time_Id, w, d, l : integer;
begin
  if (ds_Avlblt.Active) then ds_Avlblt.Close;
  Av_Type := TDim(cbb_AvType.ItemIndex);

  case Av_Type of
    Teacher:
      begin
          if VarIsNull(avcb_Teacher.KeyValue) then exit;
          Av_Index := avcb_Teacher.KeyValue;
          Query := 'SELECT "time_id", "flag" FROM "av_Teacher" WHERE "Teacher_id" = ' + IntToStr(Av_index) + ';';
      end;
    Room:
      begin
          if VarIsNull(avcb_Room.KeyValue) then exit;
          Av_Index := avcb_Room.KeyValue;
          Query := 'SELECT "time_id", "flag" FROM "av_Room" WHERE "Room_id" = ' + IntToStr(Av_index) + ';';
      end;
    Group:
      begin
          if VarIsNull(avcb_Group.KeyValue) then exit;
          Av_Index := avcb_Group.KeyValue;
          Query := 'SELECT "time_id", "flag" FROM "av_Group" WHERE "Group_id" = ' + IntToStr(Av_index) + ';';
      end;
  end;

  ds_Avlblt.CommandType := ctQuery;
  ds_Avlblt.CommandText := Query;
  ds_Avlblt.Open;
  av_enable_Chbxs;

  ds_Avlblt.First;
  while not ds_Avlblt.Eof do
  begin
         time_id := ds_Avlblt.Fields[0].Value;
         if ds_Avlblt.Fields[1].Value = 1 then
          begin
             w := time_Id div 100;
             d := (time_id - w*100) div 10;
             l := time_id - w*100 - d*10;
             av_cbxes[w - 1,d - 1,l - 1].Checked := true;
          end;
       ds_Avlblt.Next;
  end;
end;
procedure TMainForm.btn_GenerateClick(Sender: TObject);

begin
    temp := T_TimeTable.Create(ds_Teacher, ds_Group, ds_Room, ds_Subject, ds_AvlBlt, ds_Load);
    save_generatedToDb(temp);
    pgc_MainPages.ActivePageIndex := 0;
end;

// END data loading

//PUSING Aviability data to the DB
// Commit button
procedure TMainForm.av_CbClick(Sender: TObject);
var Av_Type : TDim;
   // Av_Index : Integer;
    Query : String;
begin
  upd_Av_Button.Enabled := True;
end;

//END Pushing

// SELECTING Attributes
//Categories
procedure TMainForm.cbb_AvTypeChange(Sender: TObject);
begin
    avcb_Teacher.Enabled := false;
    avcb_Group.Enabled := False;
    avcb_Room.Enabled := False;

    av_enable_Chbxs(False);

    upd_Av_Button.Enabled := false;

    case cbb_AvType.ItemIndex of
      0 :
        begin
          avcb_Teacher.Enabled := true;
        end;
      1 :
        begin
          avcb_Room.Enabled := true;
        end;
      2 :
        begin
          avcb_Group.Enabled := true;
        end;
    end;
end;

procedure TMainForm.cb_GroupClick(Sender: TObject);
begin
  lcb_vwGroup.Enabled := True;
  if (Sender AS TCheckBox).Checked then
  begin
    if (cb_Teacher.Checked AND cb_Room.Checked)
    then
      begin
        ShowMessage('Cannot display data in third dimension. Please select certain teacher or room.');
        (Sender AS TCheckBox).Checked := false;
      end
    else lcb_vwGroup.Enabled := False;
  end;
end;

procedure TMainForm.cb_RoomClick(Sender: TObject);
begin
  lcb_vwRoom.Enabled := True;
  if (Sender AS TCheckBox).Checked then
  begin
    if (cb_Teacher.Checked AND cb_Group.Checked)
    then
      begin
        ShowMessage('Cannot display data in third dimension. Please select certain teacher or Group.');
        (Sender AS TCheckBox).Checked := false;
      end
    else lcb_vwRoom.Enabled := False;
  end;
end;

procedure TMainForm.cb_SubjectClick(Sender: TObject);
begin
  lcb_Subject.Enabled := NOT (Sender AS TCheckBox).Checked;
end;

procedure TMainForm.cb_TeacherClick(Sender: TObject);
begin
  lcb_vwTeacher.Enabled := True;
  if (Sender AS TCheckBox).Checked then
  begin
    if (cb_Group.Checked AND cb_Room.Checked)
    then
      begin
        ShowMessage('Cannot display data in third dimension. Please select certain group or room.');
        (Sender AS TCheckBox).Checked := false;
      end
    else lcb_vwTeacher.Enabled := False;
  end;
end;



procedure TMainForm.avcb_GroupCloseUp(Sender: TObject);
begin
    av_LoadCheckBoxValues;
end;

procedure TMainForm.avcb_RoomCloseUp(Sender: TObject);
begin
      av_LoadCheckBoxValues;
end;

procedure TMainForm.avcb_TeacherCloseUp(Sender: TObject);
begin
    av_LoadCheckBoxValues;
end;


// END OF AVAIALABILITY

// VIEWER IMPLEMENTATION
procedure TMainForm.Button3Click(Sender: TObject);
var
  w, d, l : Integer;
  Teacher, Group,
  SubjectType,
  Subject, Room : String;
  Query : String;
  SQLEng : TSQLQuery;
  time_id : String;
  disp_value : String;

begin

  if (not (cb_Teacher.Checked) AND lcb_vwTeacher.KeyValue = NULL) then
  begin
       showMessage('Please select teacher.');
       exit;
  end;

  if (not (cb_Group.Checked) AND lcb_vwGroup.KeyValue = NULL) then
  begin
       showMessage('Please select group.');
       exit;
  end;

  if (not (cb_Room.Checked) AND lcb_vwRoom.KeyValue = NULL) then
  begin
       showMessage('Please select room.');
       exit;
  end;

  if (not (cb_Subject.Checked) AND lcb_Subject.KeyValue = NULL) then
  begin
       showMessage('Please select subject.');
       exit;
  end;

  clean_viewer;

  Query := 'SELECT tt.time_id, t.teacher_name, g.[group_name], IFNULL(r.room_name, ''<Unkn>'') , s.subject_name, st.[subjecttype_name] '
          + 'FROM f_timeTable tt '
          + 'INNER JOIN f_load l ON l.load_id = tt.load_id '
          + 'INNER JOIN l_group g ON g.[group_id] = l.group_id '
          + 'INNER JOIN l_teacher t ON t.techer_id = l.teacher_id '
          + 'INNER JOIN l_subject s ON s.subject_id = l.subject_id '
          + 'LEFT JOIN l_room r ON r.room_id = tt.room_id '
          + 'INNER JOIN l_subjectType st ON st.subjecttype_id = l.subjectType_id WHERE ';

  IF  not cb_Teacher.Checked then Query := Query + ' t.Techer_ID = ' + IntToStr(lcb_vwTeacher.KeyValue ) + ' AND';
  IF  not cb_Group.Checked then Query :=  Query + ' g.Group_ID = ' + IntToStr(lcb_vwGroup.KeyValue) + ' AND';
  IF  not cb_Room.Checked then Query :=  Query + ' r.Room_ID = ' + IntToStr(lcb_vwRoom.KeyValue) + ' AND';
  IF  not cb_Subject.Checked then Query :=  Query + ' s.Subject_ID = ' + IntToStr(lcb_Subject.KeyValue) + ' AND';
  SetLength(Query, Length(Query) - 3);

  SQLEng := TSQLQuery.Create(self);
  SQLEng.SQLConnection := SQLConnection;
  SQLEng.SQL.Text := Query;
  SQLEng.Open;
  SQLEng.First;

  while not SQLEng.Eof do
  begin
    time_id := IntToStr(SQLEng.Fields[0].Value);
    w := StrToInt(time_id[1]);
    d := StrToInt(time_id[2]);
    l := StrToInt(time_id[3]);

    disp_value := '';
    if cb_Teacher.Checked then disp_value := SQLEng.Fields[1].Value + ' | ';
    if cb_Group.Checked then disp_value := disp_value + SQLEng.Fields[2].Value + ' | ';
    if cb_Room.Checked then disp_value := disp_value + SQLEng.Fields[3].Value + ' | ';
    if cb_Subject.Checked then disp_value := disp_value + SQLEng.Fields[4].Value + ' | ';
    if cb_Subject.Checked then disp_value := disp_value + SQLEng.Fields[5].Value + ' | ';

    SetLength(disp_value, length(disp_value) - 3);

    IF (w = 1) then
      ListView1.Items[l - 1].SubItems[d - 1] :=  disp_value;
    if (w = 2) then
      ListView2.Items[l - 1].SubItems[d - 1] :=  disp_value;

    SQLEng.Next;
  end;
end;
procedure TMainForm.Button4Click(Sender: TObject);
begin
  frxReportGroup.ShowReport(False);
end;

procedure TMainForm.Button5Click(Sender: TObject);
begin
    frxReportRoom.ShowReport(false);
end;

procedure TMainForm.Button6Click(Sender: TObject);
var
  NFD : TExportDlg;
begin
     NFD := TExportDlg.Create(self);
     NFD.SetData(temp, cds_Load);
     NFD.ShowModal;
end;

// END VIEWER

// FORM FEATURES
procedure TMainForm.FormCreate(Sender: TObject);
begin
    w_count := 2;
    d_count := 6;
    l_count := 6;

    //Connecting to database
    SQLConnection.Params.Add('Database=.\DB\schedule.db');
    SQLConnection.Open;

    //Loading datasets
    cds_Room.Open;
    cds_Teacher.Open;
    cds_Group.Open;
    cds_Subject.Open;
    cds_SubjectType.Open;
    cds_Load.Open;

    fill_Aviability;
    initialize_viewer;
end;

procedure TMainForm.FormDestroy(Sender: TObject);
begin
    SQLConnection.CloseDataSets;
    SQLConnection.Close;
end;

procedure TMainForm.FormResize(Sender: TObject);
var
    placehold, i : Integer;
    scItmW, scItmH : Integer;
begin
    btn_Generate.Left := Self.Width - btn_Generate.Width - 17;
     ListView1.Columns[0].Width := 37;
    for I := 1 to 6 do
    ListView1.Columns[i].Width := ((ListView1.Width - 40 )div 6);

    ListView2.Columns[0].Width := 37;
    for I := 1 to 6 do
    ListView2.Columns[i].Width := ((ListView1.Width - 40 )div 6);
end;


procedure TMainForm.initialize_viewer;
var i, j, k : integer;
    temp_label : TLabel;
    Li : TListItem;
begin

      for j := 0 to pred(l_count) do
      begin
        Li := ListView1.Items.Add;
        Li.Caption := IntToStr(j + 1);
        for k := 0 to pred(d_count) do
        begin
          Li.SubItems.Add('');
        end;
      end;

      for j := 0 to pred(l_count) do
      begin
        Li := ListView2.Items.Add;
        Li.Caption := IntToStr(j + 1);
        for k := 0 to pred(d_count) do
        begin
          Li.SubItems.Add('');
        end;
      end;
end;

procedure TMainForm.clean_viewer;
var i, j, k : integer;
begin
      for j := 0 to pred(l_count) do
      begin
        for k := 0 to pred(d_count) do
        begin
          ListView1.Items[j].SubItems[k] := '';
          ListView2.Items[j].SubItems[k] := '';
        end;
      end;
end;

end.