unit uDataBaseUnit;
{< this unit central in work with DB,
contains FDConnection, FDTable and all FDQueries
needed in other units
}

interface

uses
  System.SysUtils, System.Classes, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Error, FireDAC.UI.Intf,
  FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.VCLUI.Wait,
  FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, FireDAC.Comp.Client, Data.DB, FireDAC.Comp.DataSet,
  FireDAC.Comp.UI, FireDAC.Phys.MySQL, FireDAC.Phys.MySQLDef,Vcl.DBGrids;

type
  TDataBaseUnit = class(TDataModule)
    qSelectWhereIdParent: TFDQuery;
    qInsertFileOrFolder: TFDQuery;
    qDelete: TFDQuery;
    qUpdateIdParent: TFDQuery;
    qGetUserRootD: TFDQuery;
    qIsFolder: TFDQuery;
    qSelectParentOfParent: TFDQuery;
    qExactSearch: TFDQuery;
    qEntrySearch: TFDQuery;
    qCaseSensitiveSearch: TFDQuery;
    qSelectFileTypeWhere: TFDQuery;
    qSelectFromFoldersAndFilesWhereID: TFDQuery;
    qIsFileAttachedTests: TFDQuery;
    qIsFileAttachedQuestionaires: TFDQuery;
    qIsFileAttachedCourses: TFDQuery;
    qSelectFromTestsWhereID: TFDQuery;
    qSelectFromQuestionairesWhere: TFDQuery;
    qSelectCourses: TFDQuery;
    qSelectChildrenFromFoldersAndFiles: TFDQuery;
    qSelectFileExtensionsByType: TFDQuery;
    procedure DataModuleCreate(Sender: TObject);
    procedure FDTableCalcFields(DataSet: TDataSet);



  private


    FFDConnection:TFDConnection;

    FIDOfRootFolderOfUser:integer;


    FIDCurrentFolder:integer;









    { Private declarations }



  public
    { Public declarations }


    constructor Create(AOwner:TComponent); override;

    procedure ConnectQueriesAndFDTable;
    {< just connects all queries and FDTable}

    function IsFolder(Aid: integer):boolean;
    {< checks if IsFolder by id in database}

    procedure OpenFolder(idParent,idUser:Integer);
    {< opens folder with idParent for idUser}

    procedure OpenFilesOfUser(idUser: Integer);
    {< opens all files of user in FDTable }


    procedure AdjustDBGrid;
    {< adjusting coloumn width and etcetra}

    function GetIDOfRootFolderOfUser(idUser: integer): Integer;
    {< finds IDOfRootFolder of User}

    procedure SortByCreationDate;
    {< Sorts by Creation Date}

    function GetIDOfFileType(FileType:string):integer;
    {< will get ID of one of fileTypes
      -Picture
      -Video
      -Audio
      -Other
    }

    function IsThisPictureByID(AID:integer):boolean;
    {< checks if this Picture by ID}

    procedure SelectByFileType(AFileTypeValue: String);
    {< selects by filetype}


function IsFileAttached(

 AID: Integer;

  var AFileName:String;

   var AAttachedToTestsSL:TStringList;
     var AAttachedToQuestionairesSL:TStringList;
       var AAttachedToCoursesSL:TStringList


           ): boolean;
 {< this function checks by id if File Is Attached and if so
  - records names of tests,questionaires and files to AAttachedToTestsSL,AAttachedToQuestionairesSL,AAttachedToCoursesSL}

    //--------PUBLIC PROPERTIES

    property FDConnection:TFDConnection
    read FFDConnection write FFDConnection;

    property IDOfRootFolderOfUser:integer read FIDOfRootFolderOfUser
    write FIDOfRootFolderOfUser;

    property IDCurrentFolder:integer read FIDCurrentFolder
    write FIDCurrentFolder;

  end;

//var
//  DataBaseUnit: TDataBaseUnit;

implementation

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}

uses uVisualFrame;

var VisualFrame_UnitVar:TVisualFrame_HTTP;

constructor TDataBaseUnit.Create(AOwner: TComponent);
begin
  inherited;

if (AOwner is TVisualFrame_HTTP) then
VisualFrame_UnitVar:=(AOwner as TVisualFrame_HTTP);




end;

procedure TDataBaseUnit.DataModuleCreate(Sender: TObject);
begin
//  ConnectQueriesAndFDTable;

end;



procedure TDataBaseUnit.FDTableCalcFields(DataSet: TDataSet);
begin

//

//FDTableLink123.DataSet:=FDTable;
//FDTableLink123.Value:='SomeValue';

//DataSet.FieldByName('Link123').AsString:='123';

end;


procedure TDataBaseUnit.SortByCreationDate;
begin
//
with VisualFrame_UnitVar do begin


FDTable.IndexFieldNames:='creationDateTime:D';
FDTable.Close;
FDTable.Open;

end;


end;



function TDataBaseUnit.GetIDOfFileType(FileType: string): integer;
begin

Result:=-2;

 with qSelectFileTypeWhere do begin

 Params.ParamValues['FileType']:=FileType;
 Open();

  Result:=FieldByName('id').AsInteger;

 Close();
 end;

end;

function TDataBaseUnit.GetIDOfRootFolderOfUser(idUser:integer):Integer;




begin

Result:=-1;


with qGetUserRootD do begin


 Params.ParamValues['IDUser']:=idUser.ToString;
 Open();
 Result:=FieldByName('ClientRootID').AsInteger;
 FIDOfRootFolderOfUser:=Result;
 Close();

end;

end;





function TDataBaseUnit.IsFileAttached(

 AID: Integer;

  var AFileName:String;

   var AAttachedToTestsSL:TStringList;
     var AAttachedToQuestionairesSL:TStringList;
       var AAttachedToCoursesSL:TStringList

           ): boolean;

var
  foldersandfilesonserver_id: integer;

  AttachedToTestsSL_ID:TStringList;
  AttachedToQuestionaires_ID:TStringList;
  AttachedToCourses_ID:TStringList;
  i: integer;

begin

//


AttachedToTestsSL_ID:=TStringList.Create;
AttachedToQuestionaires_ID:=TStringList.Create;
AttachedToCourses_ID:=TStringList.Create;


try

 Result:=False;

    //------ checking is FIle attached to Tests

    with qIsFileAttachedTests do begin

    Params.ParamValues['foldersandfilesonserver_id']:=AID;

    Open();

    if not IsEmpty then

           begin

             Result:=true;

              // Getting FileName from foldersandfilesonserver table

             foldersandfilesonserver_id:=FieldByName('foldersandfilesonserver_id').AsInteger;


                   with qSelectFromFoldersAndFilesWhereID do begin

                     Params.ParamValues['id']:=foldersandfilesonserver_id;

                     Open();

                     AFileName:=FieldByName('name').AsString;

                     Close();

                   end;



             //To which tests is file  attached?


                   //first - get IDList of this Tests

                    while not EOF do begin

                     AttachedToTestsSL_ID.Add( FieldByName('tests_id').AsInteger.ToString );

                     Next();
                    end;


                   //Now get name of those tests from tests table

                   for i := 0 to AttachedToTestsSL_ID.Count-1 do begin

                       with qSelectFromTestsWhereID do begin

                       Params.ParamValues['id']:=AttachedToTestsSL_ID[i].ToInteger();

                       Open();

                        AAttachedToTestsSL.Add(

                        FieldByName('name').AsString

                        );


                       Close();

                     end;



                   end;




           end;


    Close();

    end;



    //----- checking is FIle attached to questionaires

    with qIsFileAttachedQuestionaires do begin

    Params.ParamValues['foldersandfilesonserver_id']:=AID;

    Open();

        if not IsEmpty then begin

        Result:=true;

         foldersandfilesonserver_id:=FieldByName('foldersandfilesonserver_id').AsInteger;

                       // Getting FIleName
                       with qSelectFromFoldersAndFilesWhereID do begin

                         Params.ParamValues['id']:=foldersandfilesonserver_id;

                         Open();

                         AFileName:=FieldByName('name').AsString;

                         Close();

                       end;



              //To which Questionaires is file  attached?

                   //first - get IDList of this Questionaires

                    while not EOF do begin

                     AttachedToQuestionaires_ID.Add( FieldByName('questionaires_id').AsInteger.ToString );

                     Next();
                    end;


                   //Now get name of those Questionaires from Questionaires table

                   for i := 0 to AttachedToTestsSL_ID.Count-1 do begin

                       with qSelectFromQuestionairesWhere do begin

                       Params.ParamValues['id']:=AttachedToQuestionaires_ID[i].ToInteger();

                       Open();

                        AAttachedToQuestionairesSL.Add(

                        FieldByName('name').AsString

                        );


                       Close();

                     end;



                   end;







        end;



    Close();

    end;

       // checking is FIle attached to Courses

    with qIsFileAttachedCourses do begin

    Params.ParamValues['foldersandfilesonserver_id']:=AID;

    Open();

    if not IsEmpty then begin

           Result:=true;

           foldersandfilesonserver_id:=FieldByName('foldersandfilesonserver_id').AsInteger;

                   // Getting FIleName
                   with qSelectFromFoldersAndFilesWhereID do begin

                     Params.ParamValues['id']:=foldersandfilesonserver_id;

                     Open();

                     AFileName:=FieldByName('name').AsString;

                     Close();

                   end;




              //To which Courses is file  attached?

                   //first - get IDList of this Questionaires

                    while not EOF do begin

                     AttachedToCourses_ID.Add( FieldByName('courses_id').AsInteger.ToString );

                     Next();
                    end;


                   //Now get name of those courses from courses table

                   for i := 0 to AttachedToTestsSL_ID.Count-1 do begin

                       with qSelectCourses do begin

                       Params.ParamValues['id']:=AttachedToCourses_ID[i].ToInteger();

                       Open();

                        AAttachedToCoursesSL.Add(

                        FieldByName('name').AsString

                        );


                       Close();

                     end;



                   end;







          end;




    Close();

    end;



finally

AttachedToTestsSL_ID.Free;
AttachedToQuestionaires_ID.Free;
AttachedToCourses_ID.Free;

end;


end;



function TDataBaseUnit.IsFolder(Aid:integer):boolean;
begin



with qIsFolder do begin

  Params.ParamValues['id']:=Aid;

  Open();
  Result:=FieldByName('isFolder').AsBoolean;
  Close();

end;

end;



function TDataBaseUnit.IsThisPictureByID(AID: integer): boolean;

var IDPictureType:Integer;

begin


try
Result:=False;
IDPictureType:=GetIDOfFileType('Picture');

with qSelectFromFoldersAndFilesWhereID do begin

Params.ParamValues['id']:=AID;

 Open();

 if FieldByName('FileType_id').AsInteger=IDPictureType then
 Result:=True;


 Close();

end;


except

on E:Exception do
raise Exception.Create('Error Message '+E.Message);


end;


end;



procedure TDataBaseUnit.OpenFolder(idParent, idUser: Integer);


begin


if IsFolder(idParent) then

 begin

   FIDCurrentFolder:=idParent;

    VisualFrame_UnitVar.FDTable.Filter:=

        '`idParent`='+idParent.ToString+
        ' and '+'`users_id`='+idUser.ToString;
    //    +' and '+'`isFolder`=''1''';

    VisualFrame_UnitVar.FDTable.Filtered:=true;


    //StatusBar
 with VisualFrame_UnitVar do begin

  if ( Assigned(FDTable) )
  and Assigned( StatusBar.Panels[0] )

   then
  StatusBar.Panels[0].Text:=
  'Всего элементов '+FDTable.RecordCount.ToString;


 end;


 end;


end;



procedure TDataBaseUnit.OpenFilesOfUser(idUser: Integer);

begin



    VisualFrame_UnitVar.FDTable.Filter:=

//        '`idParent`='+idParent.ToString+
//        ' and '+
        '`users_id`='+idUser.ToString

        +' and '+'`isFolder`=''0''';

    VisualFrame_UnitVar.FDTable.Filtered:=true;


    //Sorting by Creation Date

    with VisualFrame_UnitVar do begin

    FDTable.IndexFieldNames:='creationDateTime:D';
                   FDTable.Close;
                   FDTable.Open;


    end;




end;




procedure TDataBaseUnit.SelectByFileType(AFileTypeValue:String);


begin


//if IsFolder(idParent) then

 begin

         with VisualFrame_UnitVar do begin


           if IsAllFilesMode then

           begin

            FDTable.Filter:=

                '`users_id`='+HTTP_ConnectionParams.IDUser+
                ' and '+'`FileType_id`='+GetIDOfFileType(AFileTypeValue).ToString;

                ;

            FDTable.Filtered:=true;

           end else

            begin

                FDTable.Filter:=

                '`idParent`='+FIDCurrentFolder.ToString+
                ' and '+'`users_id`='+HTTP_ConnectionParams.IDUser+
                ' and '+
                '`FileType_id`='+GetIDOfFileType(AFileTypeValue).ToString;

                ;

             //and (FileType_id is NULL  or FileType_id=37)
            FDTable.Filtered:=true;

            end;


          end;
 end;


end;







procedure TDataBaseUnit.AdjustDBGrid;

var n:integer; // Start Index

begin
//

with VisualFrame_UnitVar.DBGrid do begin

// name
Columns[2].Width:=20;
Columns[2].Title.Caption:='';
Columns[2].Title.Alignment:=taCenter;
Columns[2].Alignment:=taCenter;


 n:=3;

// name  3
Columns[n].Width:=400;
Columns[n].Title.Caption:='Имя';
Columns[n].Title.Alignment:=taCenter;
Columns[n].Alignment:=taLeftJustify;

//Link 4
Columns[n+1].Width:=400;
Columns[n+1].Title.Caption:='Ссылка';
Columns[n+1].Title.Alignment:=taCenter;
Columns[n+1].Alignment:=taLeftJustify;


//fileSize 5
Columns[n+2].Width:=80;
Columns[n+2].Title.Caption:='Размер';
Columns[n+2].Title.Alignment:=taCenter;
Columns[n+2].Alignment:=taCenter;


// Creation DateTime
Columns[n+5].Width:=170;
Columns[n+5].Title.Caption:='Дата создания';
Columns[n+5].Title.Alignment:=taCenter;
Columns[n+5].Alignment:=taCenter;


// changesDateTime
Columns[n+6].Visible:=true;
Columns[n+6].Width:=170;
Columns[n+6].Title.Caption:='Дата изменения';
Columns[n+6].Title.Alignment:=taCenter;
Columns[n+6].Alignment:=taCenter;


// lastAccessDateTime
Columns[n+7].Visible:=true;
Columns[n+7].Width:=170;
Columns[n+7].Title.Caption:='Последний доступ';
Columns[n+7].Title.Alignment:=taCenter;
Columns[n+7].Alignment:=taCenter;


{

// name
Columns[1].Width:=300;
Columns[1].Title.Caption:='fileSize';
Columns[1].Title.Alignment:=taCenter;
Columns[1].Alignment:=taCenter;



// link
Columns[2].Width:=300;
Columns[2].Title.Caption:='Link';
Columns[2].Title.Alignment:=taCenter;
Columns[2].Alignment:=taCenter;

}

{
// id
Columns[0].Visible:=false;

// idParent
Columns[1].Visible:=false;

// name
Columns[2].Width:=300;
Columns[2].Title.Caption:='Имя';
Columns[2].Title.Alignment:=taCenter;
Columns[2].Alignment:=taCenter;

// filesize
Columns[3].Width:=150;
Columns[3].Title.Caption:='Размер';
Columns[3].Title.Alignment:=taCenter;
Columns[3].Alignment:=taCenter;

//Link
Columns[4].Width:=150;
Columns[4].Title.Caption:='Ссылка';
Columns[4].Title.Alignment:=taCenter;
Columns[4].Alignment:=taCenter;

// relPath
Columns[5].Visible:=false;
Columns[5].Width:=150;
Columns[5].Title.Caption:='Ссылка';
Columns[5].Title.Alignment:=taCenter;
Columns[5].Alignment:=taCenter;


// absPath
Columns[6].Visible:=false;
Columns[6].Width:=170;
Columns[6].Title.Caption:='';
Columns[6].Title.Alignment:=taCenter;
Columns[6].Alignment:=taCenter;

// creationDateTIme
Columns[7].Width:=170;
Columns[7].Title.Caption:='Дата создания';
Columns[7].Title.Alignment:=taCenter;
Columns[7].Alignment:=taCenter;

// changesDateTime
Columns[8].Visible:=true;
Columns[8].Width:=170;
Columns[8].Title.Caption:='Дата изменения';
Columns[8].Title.Alignment:=taCenter;
Columns[8].Alignment:=taCenter;


// lastAccessDateTime
Columns[9].Visible:=true;
Columns[9].Width:=170;
Columns[9].Title.Caption:='Последний доступ';
Columns[9].Title.Alignment:=taCenter;
Columns[9].Alignment:=taCenter;


// isFolder
Columns[10].Visible:=false;


// users_id
Columns[11].Visible:=false;
Columns[11].Width:=120;
Columns[11].Title.Caption:='';
Columns[11].Title.Alignment:=taCenter;
Columns[11].Alignment:=taCenter;

}
end;


end;

procedure TDataBaseUnit.ConnectQueriesAndFDTable;
var
  I: Integer;
begin

 if ( Assigned(VisualFrame_UnitVar.FDConnection) )
 and (VisualFrame_UnitVar.FDConnection.Connected) then

  begin
  //Connecting FDTable
  for I := 0 to Self.ComponentCount - 1 do
    if (Self.Components[i] is TFDQuery) then
      (Self.Components[i] as TFDQuery).Connection := VisualFrame_UnitVar.FDConnection;
  {
  //Connecting FDTable
  for I := 0 to Self.ComponentCount - 1 do
    if (Self.Components[i] is TFDTable) then
      (Self.Components[i] as TFDTable).Connection := FFDConnection;
  }
  end;


end;

end.
