unit uSQLQueries;

//Здесь будут храниться шаблоны запросов SQL
//Они будут изменяться из ObjectInspector



interface

uses
  System.SysUtils, System.Classes,vcl.dialogs, 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.Phys.MySQLDef, FireDAC.VCLUI.Wait, FireDAC.Comp.UI,
  FireDAC.Phys.MySQL, Data.DB, FireDAC.Comp.Client,uDBConnectionBPL;

type TSQLQueries=class(TComponent)

private
             {Собственно SQL запросы дерева находятся в этих полях}
{Их можно было поправить из ObjectInspector, в поздних версиях убрал эту возможность}

FInsertFolderFile:TstringList;
FinsertNode:TStringList;
FMaxID:TStringList;
FSelectIDisFolder:TStringList;
FSelectAllChildren:TStringList; // <<Неполный запрос, остальная часть проставляется в конкретной
// процедуре




//Удаление узлов из БД
//Модуль C:\Users\YellowFriend\Desktop\DBTReeViewSVN2\uCopyCutDelete\uDeleteThread.pas
FDeleteNodesFromDB:TStringList;

//Копирование и вставка узла
FCopyRecordInDB:TStringList;

//Обновление родителя
FUpdateIdParent:TstringList;

//Обновление галочек
FUpdateChecked:TStringList;

//Select по id
FSelectByID:TStringList;


//Проверка на папку
FCheckIsFolder:TStringList;


//Поиск родителя
FSelectParent:TStringlist;

//Поиск по имени
FSearch:TStringList;

//Редактирование названия узла
FEdit:TStringList;

//Обновление индекса
FUpdateIndex:TStringlist;

//Получение id
FSelectID:TStringList;

//Обновление родителя в базе
FEditParentKey:TStringList;


//Параметры соединения, для получения имени базы

FDBConnectionParams:TDBConnectionParams;
FDBTable:TDBTable;


//Выборка только тех, кто с галочками
FSelectAllChecked:TStringList;

//Удаление всех галочек из базы
FClearAllCheckBoxes:TStringList;

//FSelectAll
FSelectAll:TstringList;

//Геттеры

function GetInsertFolderFile:TstringList;
function GetInsertNode:TStringList;
function GetMaxID:TStringList;
function GetSelectIDisFolder:TStringList;
function GetSelectAllChildren:TStringList;
function GetUpdateChecked:TStringList;
function GetDeleteNodesFromDB:TStringList;
function GetCopyRecordInDB:TStringList;
function GetUpdateIdParent:TstringList;
function GetSelectByID:TStringList;
function GetCheckIsFolder:TStringList;
function GetSelectParent:TStringlist;
function GetSearch:TStringList;
function GetEdit:TStringList;
function GetUpdateIndex:TStringList;
function GetSelectID:TStringList;
function GetEditParentKey:TStringList;
function GetClearAllCheckBoxes:TStringList;
function GetSelectAllChecked:TStringList;
function GetSelectAll:TstringList;


//Сеттеры

procedure SetInsertFolderFile(Value:TstringList);
procedure SetInsertNode(Value:TStringList);
procedure SetMaxID(Value:TStringList);
procedure SetSelectIDisFolder(Value:TStringList);
procedure SetSelectAllChildren(Value:TstringList);
procedure SetUpdateChecked(Value:TstringList);
procedure SetDeleteNodesFromDB(Value:TStringList);
procedure SetCopyRecordInDB(Value:TStringList);
procedure SetUpdateIdParent(Value:TstringList);
procedure SetSelectByID(Value:TStringList);
procedure SetCheckIsFolder(Value:TStringList);
procedure SetSelectParent(Value:TStringlist);
procedure SetSearch(Value:TStringList);
procedure SetEdit(Value:TStringList);
procedure SetUpdateIndex(Value:TStringList);
procedure SetSelectID(Value:TStringList);
procedure SetEditParentKey(Value:TStringList);
procedure SetClearAllCheckBoxes(Value:TStringList);
procedure SetSelectAllChecked(Value:TStringList);
procedure SetSelectAll(Value:TStringList);

public

constructor Create(AOwner:TComponent); override;
destructor Destroy; override;

published // << Убираем из published, так как менять будем через
//DBTreeViewConnParams и DBTReeViewTable в ObjectInspector

property InsertFolderFile:TstringList read GetInsertFolderFile write SetInsertFolderFile;
property InsertNode:TStringList read GetInsertNode write SetInsertNode;
property MaxID:TStringList read GetMaxID write SetMaxId;
property SelectIDisFolder:Tstringlist read GetSelectIDisFolder write SetSelectIDisFolder;
property SelectAllChildren:TStringList read GetSelectAllChildren write SetSelectAllChildren;
property UpdateChecked:TStringList read GetUpdateChecked write SetUpdateChecked;
property DeleteNodesFromDB:TStringList read GetDeleteNodesFromDB write SetDeleteNodesFromDB;
property CopyRecordInDB:TStringList read GetCopyRecordInDB write SetCopyRecordInDB;
property UpdateIdParent:TstringList read GetUpdateIdParent write SetUpdateIdParent;
property SelectByID:TStringList read GetSelectByID write SetSelectByID;
property CheckIsFolder:TStringList read GetCheckIsFolder write SetCheckIsFolder;
property SelectParent:TStringList read GetSelectParent write SetSelectParent;
property Search:TStringList read GetSearch write SetSearch;
property Edit:TStringList read GetEdit write SetEdit;
property UpdateIndex:TStringList read GetUpdateIndex write SetUpdateIndex;
property SelectID:TStringList read GetSelectID write SetSelectID;
property EditParentKey:TStringList read GetEditParentKey write SetEditParentKey;
property ClearAllCheckBoxes:TStringList read GetClearAllCheckBoxes
         write SetClearAllCheckBoxes;
property SelectAllChecked:TstringList read GetSelectAllChecked write SetSelectAllChecked;
property SelectAll:TStringLIst read FSelectAll write FSelectAll;

property DBConnectionParams:TDBConnectionParams read FDBConnectionParams write FDBConnectionParams;
property DBTableName:TDBTable read FDBTable write FDBTable;



end;



implementation

uses uTreeView;

                                    { TSQLQueries }

constructor TSQLQueries.Create(AOwner: TComponent);

var DBName:string; TableName:string; DBNamePlusTable:string;

//`IdParent`, `Name`, `Index`,`isChecked`,`Level`,`IsFolder`

idField,idParent,NameField,IndexField,isChecked,Level,IsFolder,IsExpanded:string;

begin
  inherited;

  Self.Name:='SQLQueries';

//------------------------------------------------------------------------------
// Переменные для подстановки в запросы, их значения берем из ObjectInspector
//------------------------------------------------------------------------------

  DBNamePlusTable:=
  '`'
  +DBTreeViewGlobal.DBTreeViewConnParams.DataBase
  +'`'+'.'+'`'
  +DBTreeViewGlobal.DBTreeViewTable.TableName+'`';


//Поля таблицы из ObjectInspector


idField:=DBTreeViewGlobal.DBTreeViewTable.id;
idParent:=DBTreeViewGlobal.DBTreeViewTable.idParent;
NameField:=DBTreeViewGlobal.DBTreeViewTable.NodeName;
IndexField:=DBTreeViewGlobal.DBTreeViewTable.IndexField;
isChecked:=DBTreeViewGlobal.DBTreeViewTable.IsChecked;
Level:=DBTreeViewGlobal.DBTreeViewTable.Level;
IsFolder:=DBTreeViewGlobal.DBTreeViewTable.IsFolder;
IsExpanded:=DBTreeViewGlobal.DBTreeViewTable.IsExpanded;

idField:='`'+idField+'`';
idParent:='`'+idParent+'`';
NameField:='`'+NameField+'`';
IndexField:='`'+IndexField+'`';
isChecked:='`'+isChecked+'`';
Level:='`'+Level+'`';
IsFolder:='`'+IsFolder+'`';
IsExpanded:='`'+IsExpanded+'`';

//Вставка папки или файла
//  InsertFolderFile:=TStringList.Create;

  FInsertFolderFile:=TStringlist.Create;
  FInsertFolderFile.Clear;



  FInsertFolderFile.Text:=
 'INSERT INTO '+DBNamePlusTable+' ('+IdParent+','+NameField+', '+IndexField+
 ', '+isChecked+', '+Level+','+IsFolder+')'+
 ' VALUES (:idParent, :name, :index,:isChecked,:Level,:IsFolder);';

{ //Без параметров
  FInsertFolderFile.Text:=
 'INSERT INTO '+DBNamePlusTable+' (`idParent`,`Name`,`Index`,`isChecked`,`Level`,`IsFolder`) '+
 ' VALUES (:idParent, :name, :index,:isChecked,:Level,:IsFolder);';
}


 //Вставка произвольного узла

 // InsertNode:=TStringList.Create;
 FInsertNode:=Tstringlist.Create;
 FInsertNode.Clear;


 FInsertNode.Text:=

  'INSERT INTO '+DBNamePlusTable+' ('+IdParent+','+NameField+', '+IndexField+
 ', '+isChecked+', '+Level+')'+
 ' VALUES (:idParent, :name, :index,:isChecked,:Level);';

{ // Без параметров
 (

 'INSERT INTO '+DBNamePlusTable+' (`IdParent`, `Name`, `Index`,`isChecked`,`Level`)'+
 'VALUES (:idParent, :name, :index,:isChecked,:Level);'

 );

}


                   {Получение максимального ID}
FMaxID:=TstringList.Create;
FMaxID.Clear;
FMaxID.Text:='SELECT Max('+idField+') as newkey FROM '+DBNamePlusTable+';';

// Без параметров
{FMaxID.Text:='SELECT Max(id) as newkey FROM '+DBNamePlusTable+';';}


                      {Проверка папка это или файл по базе}

FSelectIDisFolder:=TStringList.Create;
FSelectIDisFolder.Clear;
FSelectIDisFolder.Text:='SELECT '+idField+','+IsFolder+' FROM  '+DBNamePlusTable+';';


// Без параметров
{FSelectIDisFolder.Text:='SELECT id,IsFolder FROM  '+DBNamePlusTable+';';}

                               {Получение всех детей узла}

//Внимание! Запрос неполный - остальная часть проставляется в конкретной процедуре, например
{SQL.Text:='SELECT * FROM treeview_db.tree where IdParent='+NodeID.ToString;}

FSelectAllChildren:=TStringList.Create;
FSelectAllChildren.Clear;
FSelectAllChildren.Text:='SELECT * FROM  '+DBNamePlusTable+' where '+IdParent+'=';

// Без параметров
{'SELECT * FROM treeview_db.tree where IdParent=}

                                 {Поиск родителя}
FSelectParent:=TstringList.Create;
FSelectParent.Clear;
FSelectParent.Text:='Select * from  '+DBNamePlusTable+' where '+idField+'=';



                                {Обновление галочек}
FUpdateChecked:=TStringList.Create;
FUpdateChecked.Clear;
FUpdateChecked.Text:='UPDATE  '+DBNamePlusTable+' SET '+IsChecked+'=:IsChecked WHERE '+idField+'=:id;';

                                 {Удаление узла из БД}
FDeleteNodesFromDB:=TstringList.Create;
FDeleteNodesFromDB.Clear;
FDeleteNodesFromDB.Text:='DELETE FROM  '+DBNamePlusTable+' WHERE '+idField+' =:id;';

                     {Копирование и вставка узла в БД INSERT SELECT}

FCopyRecordInDB:=TstringList.Create;
FCopyRecordInDB.Clear;

FCopyRecordInDB.Text:=

 'INSERT INTO  '+DBNamePlusTable+

 '('+idParent+','+
  NameField+','+
  IndexField+','+
  isChecked+','+
  IsExpanded+','+
  Level+','+
  IsFolder+')'+

 ' SELECT '+

 IdParent+','+
 NameField+','+
 IndexField+','+
 isChecked+','+
 IsExpanded+','+
 Level+','+
 IsFolder+

 ' from '
 +DBNamePlusTable+' where '+idField+'=:id' ;


{ // Без параметров
FCopyRecordInDB.Text:=

 'INSERT INTO  '+DBNamePlusTable+

 '(`IdParent`,'+
  '`Name`,'+
  '`Index`,'+
  '`isChecked`,'+
  '`IsExpanded`,'+
  '`Level`,'+
  '`IsFolder`)'+

 'SELECT'+

 '`IdParent`,'+
 '`Name`,'+
 '`Index`,'+
 '`isChecked`,'+
 '`IsExpanded`,'+
 '`Level`,'+
 '`IsFolder`'+

 ' from '
 +DBNamePlusTable+' where id=:id' ;



}


 {Обновление родителя}
 // UPDATE `treeview_db`.`tree` SET `IdParent`=:idParent WHERE `id`=:id;
FUpdateIdParent:=TstringList.Create;
FUpdateIdParent.Clear;
FUpdateIdParent.Text:='UPDATE  '+DBNamePlusTable+' SET '+idParent+'=:idParent WHERE '+idField+'=:id;';

// Без параметров
{FUpdateIdParent.Text:='UPDATE  '+DBNamePlusTable+' SET `IdParent`=:idParent WHERE `id`=:id;';}


                           {Select by ID}
FSelectByID:=TstringList.Create;
FSelectByID.Clear;
FSelectByID.Text:= 'SELECT * FROM  '+DBNamePlusTable+' where '+idField+'=:id;';

                            {Проверка на папку}
FCheckIsFolder:=Tstringlist.Create;
FCheckIsFolder.Clear;
FCheckIsFolder.Text:='Select * from  '+DBNamePlusTable+' where '+idField+'=';


                            {Поиск по имени}
FSearch:=TstringList.Create;
FSearch.Clear;
FSearch.Text:='SELECT '+NameField+' FROM  '+DBNamePlusTable+' where '+NameField+' like ';

// Без параметров
////'SELECT name FROM treeview_db.tree where Name like '+'''%'+name+'%''';



FEdit:=TStringList.Create;
FEdit.Clear;
FEdit.Text:='UPDATE  '+DBNamePlusTable+' SET '+NameField+' =:Name WHERE '+idField+'=:id';


                          {Обновление индекса}

FUpdateIndex:=TstringList.Create;
FUpdateIndex.Clear;
FUpdateIndex.Text:='UPDATE  '+DBNamePlusTable+' SET '+indexField+'=:Index WHERE '+IdField+'=:id;';



                           {SelectID}
FSelectID:=TStringLIst.Create;
FSelectID.Clear;
FSelectID.Text:='SELECT '+idField+' FROM  '+DBNamePlusTable+';';


                                             {SelectALL}

FSelectAll:=TStringList.Create;
FSelectAll.Clear;
FSelectAll.Text:='SELECT*FROM  '+DBNamePlusTable+' order by '+indexField+';';


                          {EditParentKey}
FEditParentKey:=TstringList.Create;
FEditParentKey.Clear;
FEditParentKey.Text:='UPDATE  '+DBNamePlusTable+' SET '+idParent+'=:idParent WHERE '+idField+'=:id;';


                   {Выборка всех отмеченных галочками}

FSelectAllChecked:=TStringList.Create;
FSelectAllChecked.Clear;
FSelectAllChecked.Text:='SELECT * FROM '+DBNamePlusTable+'  where '+IsChecked+'=''1'';';

                          {ClearAllCheckBoxes}

FClearAllCheckBoxes:=TStringList.Create;
FClearAllCheckBoxes.Clear;
FClearAllCheckBoxes.Text:='UPDATE  '+DBNamePlusTable+' SET '+isChecked+'=''0'' where '+idField+'=:id;'
{UPDATE `treeview_db`.`tree` SET `IsChecked`='0' WHERE `id`='87078';
}

end;



destructor TSQLQueries.Destroy;
begin

  FreeAndNil(FInsertFolderFile);
  FreeAndNil(FInsertNode);
  FreeAndNil(FMaxID);
  FreeAndNil(FSelectIDisFolder);
  FreeAndNil(FSelectAllChildren);
  FreeAndNil(FUpdateChecked);
  FreeAndNil(FDeleteNodesFromDB);
  FreeAndNil(FCopyRecordInDB);
  FreeAndNil(FUpdateIdParent);
  FreeAndNIl(FSelectByID);
  FreeAndNil(FCheckIsFolder);
  FreeAndNIl(FSelectParent);
  FreeAndNil(FSearch);
  FreeAndNil(FEdit);
  FreeAndNil(FUpdateIndex);
  FreeAndNil(FSelectID);
  FreeAndNil(FEditParentKey);
  FreeAndNil(FClearAllCheckBoxes);
  FreeAndNil(FSelectAllChecked);
  FreeAndNil(FSelectAll);

  inherited;
end;

//-----------------Геттеры----------------------------

function TSQLQueries.GetSearch: TStringList;
begin
Result:=FSearch;
end;

function TSQLQueries.GetSelectAll: TstringList;
begin
Result:=FSelectAll;
end;

function TSQLQueries.GetSelectAllChecked: TStringList;
begin
Result:=FSelectAllChecked;
end;

function TSQLQueries.GetSelectAllChildren: TStringList;
begin
Result:=FSelectAllChildren;
end;

function TSQLQueries.GetSelectByID: TStringList;
begin
Result:=FSelectByID;
end;

function TSQLQueries.GetSelectID: TStringList;
begin
Result:=FSelectID;
end;

function TSQLQueries.GetSelectIDisFolder: TStringList;
begin
Result:=FSelectIDisFolder;
end;

function TSQLQueries.GetSelectParent: TStringlist;
begin
Result:=FSelectParent;
end;

function TSQLQueries.GetUpdateChecked: TStringList;
begin
Result:=FUpdateChecked;
end;

function TSQLQueries.GetUpdateIdParent: TstringList;
begin
Result:=FUpdateIdParent;
end;

function TSQLQueries.GetUpdateIndex: TStringList;
begin
Result:=FUpdateIndex;
end;

function TSQLQueries.GetCheckIsFolder: TStringList;
begin
Result:=FCheckIsFolder;
end;

function TSQLQueries.GetClearAllCheckBoxes: TStringList;
begin
Result:=FClearAllCheckBoxes;
end;

function TSQLQueries.GetCopyRecordInDB: TStringList;
begin
Result:=FCopyRecordInDB;
end;

function TSQLQueries.GetDeleteNodesFromDB: TStringList;
begin
Result:=FDeleteNodesFromDB;
end;

function TSQLQueries.GetEdit: TStringList;
begin
Result:=FEdit;
end;

function TSQLQueries.GetEditParentKey: TStringList;
begin
Result:=FEditParentKey;
end;

function TSQLQueries.GetInsertFolderFile: TstringList;
begin
Result:=FInsertFolderFile;
end;


function TSQLQueries.GetInsertNode: TStringList;
begin

Result:=FInsertNode;

end;

function TSQLQueries.GetMaxID: TStringList;
begin
Result:=FMaxID;
end;

//-----------------Сеттеры------------------------------

procedure TSQLQueries.SetSearch(Value: TStringList);
begin
if not Assigned(Value) then exit;
FSearch.Assign(Value);
end;

procedure TSQLQueries.SetSelectAll(Value: TStringList);
begin
if not Assigned(Value) then exit;
FSelectAll.Assign(Value);
end;

procedure TSQLQueries.SetSelectAllChecked(Value: TStringList);
begin
if not Assigned(Value) then exit;
FSelectAllChecked.Assign(Value);
end;

procedure TSQLQueries.SetSelectAllChildren(Value: TstringList);
begin
if not Assigned(Value) then exit;
FSelectAllChildren.Assign(Value);
end;

procedure TSQLQueries.SetSelectByID(Value: TStringList);
begin
if not Assigned(Value) then exit;
FSelectByID.Assign(Value);
end;

procedure TSQLQueries.SetSelectID(Value: TStringList);
begin
if not Assigned(Value) then exit;
FSelectID.Assign(Value);
end;

procedure TSQLQueries.SetSelectIDisFolder(Value: TStringList);
begin
if not Assigned(Value) then exit;
FSelectIDisFolder.Assign(Value);
end;

procedure TSQLQueries.SetSelectParent(Value: TStringlist);
begin
if not Assigned(Value) then exit;
FSelectParent.Assign(Value);
end;

procedure TSQLQueries.SetUpdateChecked(Value: TstringList);
begin
if not Assigned(Value) then exit;
FUpdateChecked.Assign(Value);
end;

procedure TSQLQueries.SetUpdateIdParent(Value: TstringList);
begin
if not Assigned(Value) then exit;
FUpdateIdParent.Assign(Value);
end;

procedure TSQLQueries.SetUpdateIndex(Value: TStringList);
begin
if not Assigned(Value) then exit;
FUpdateIndex.Assign(Value);
end;

procedure TSQLQueries.SetCheckIsFolder(Value: TStringList);
begin
if not Assigned(Value) then exit;
FCheckIsFolder.Assign(Value);
end;

procedure TSQLQueries.SetClearAllCheckBoxes(Value: TStringList);
begin
if not Assigned(Value) then exit;
FClearAllCheckBoxes.Assign(Value);
end;

procedure TSQLQueries.SetCopyRecordInDB(Value: TStringList);
begin
if not Assigned(Value) then exit;
FCopyRecordInDB.Assign(Value);
end;

procedure TSQLQueries.SetDeleteNodesFromDB(Value: TStringList);
begin
if not Assigned(Value) then exit;
FDeleteNodesFromDB.Assign(Value);
end;

procedure TSQLQueries.SetEdit(Value: TStringList);
begin
if not Assigned(Value) then exit;
FEdit.Assign(Value);
end;

procedure TSQLQueries.SetEditParentKey(Value: TStringList);
begin
if not Assigned(Value) then exit;
FEditParentKey.Assign(Value);
end;

procedure TSQLQueries.SetInsertFolderFile(Value: TstringList);
begin
//FInsertFolderFile:=Value; // << Так неправильно - затирание ссылки

if not Assigned(Value) then exit;
FInsertFolderFile.Assign(Value);

end;

procedure TSQLQueries.SetInsertNode(Value: TStringList);
begin

if not Assigned(Value) then exit;
FInsertNode.Assign(Value);

end;

procedure TSQLQueries.SetMaxID(Value: TStringList);
begin
if not Assigned(Value) then exit;
FMaxID.Assign(Value);
end;

end.
