unit uSvc_QMS;

interface

uses
  System.Generics.Collections,
  System.SysUtils,
  JsonDataObjects,
  uSvc_Base,
  uCommons,
  uHeoObj_Base;

const
{$I globalConsts.inc}

type
  TQMSObecneService = class(TServiceBase)
   public
    function GetCislenikQMS (typ, agenda: integer): TObjectList<TQMSCiselnik>; virtual;
  end;



  TQMSUdrzbaStrojuAZarizeniService = class(TServiceBase)
   public
    function GetAll: TObjectList<TQMSUdrzbaStrojuAZarizeni>; virtual;
    function GetByID (const AID: integer): TQMSUdrzbaStrojuAZarizeni; virtual;
    function GetByParams (params: TDictionary<string, string>): TObjectList<TQMSUdrzbaStrojuAZarizeni>; virtual;
    function GetMeta: TJSONObject; virtual;
    function GetByFindParams (SearchTerm: string): TObjectList<TQMSUdrzbaStrojuAZarizeni>;
  end;

implementation

uses
  System.StrUtils,
  FireDAC.Stan.Option,
  FireDAC.Comp.Client,
  FireDAC.Stan.Param,
  MVCFramework.FireDAC.Utils,
  MVCFramework.DataSet.Utils,
  MVCFramework.Serializer.Commons,
  helTabsBIDs;

const
  selSloupce = 'SELECT ID, CisloOrg, Nazev, ICO, DIC, Ulice, PopCislo, OrCislo, Misto, PSC, PravniForma, Stav';





{ TQMSObecneService }
 function TQMSObecneService.GetCislenikQMS (typ, agenda: integer): TObjectList<TQMSCiselnik>;
  var lSQL, where: string;
      lQry: TFDQuery;
  begin
   result:= nil;

   lSQL:= 'SELECT ' + GetTabCols ('dbo', tblQMSCis, '', false, '') + ' FROM ' + tblQMSCis;
   lSQL:= lSQL + ' WHERE Ciselnik=' + typ.ToString + ' AND IDQMS' + IfThen(agenda<0, ' IS NULL', '=' + agenda.ToString);
   lSQL:= lSQL + ' ORDER BY Cislo';

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= FDM.sqlConn;
     try
       lQry.Open(lSQL);
       result:= lQry.AsObjectList<TQMSCiselnik>;
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání číselníku údržby stroje: ' + E.Message);
     end;
    finally
     lQry.Free;
   end;
  end;







{ TQMSUdrzbaStrojuAZarizeniService }

 function TQMSUdrzbaStrojuAZarizeniService.GetByParams (params: TDictionary<string, string>): TObjectList<TQMSUdrzbaStrojuAZarizeni>;
  var lSQL, where: string;
      lQry: TFDQuery;
  begin
   result:= nil;

   lSQL:= 'SELECT ' + GetTabCols ('dbo', tblKJ, '', false, '') + ' FROM ' + tblKJ;

   where:= 'Kategorie IN (SELECT Cislo FROM ' + tblKategKJ + ' WHERE QMSAgenda=1)';

   if (params.ContainsKey('id')) then
    if (params.Items['id']<>'') then
     if (params.Items['id']<>'0') then
      where:= where + ' AND ID=' + params.Items['id'];

   if (params.ContainsKey('utvar')) then
    where:= where + IfThen(where<>'', ' AND ', '') + 'Utvar=N' + params.Items['utvar'].QuotedString;

   if (params.ContainsKey('stav')) then
    where:= where + IfThen(where<>'', ' AND ', '') + 'IDCis1=(SELECT ID FROM ' + tblQMSCis + ' WHERE Cislo=N' + params.Items['stav'].QuotedString + ' AND Ciselnik=1 AND IDQMS=1)';

   if (params.ContainsKey('druh')) then
    where:= where + IfThen(where<>'', ' AND ', '') + 'IDCis2=(SELECT ID FROM ' + tblQMSCis + ' WHERE Cislo=N' + params.Items['druh'].QuotedString + ' AND Ciselnik=2 AND IDQMS=1)';


   if (where<>'') then
    lSQL:= lSQL + ' WHERE ' + where;
   lSQL:= lSQL + ' ORDER BY Kategorie, PoradoveCislo';


   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= FDM.sqlConn;
     try
       lQry.Open(lSQL);
       result:= lQry.AsObjectList<TQMSUdrzbaStrojuAZarizeni>;
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání údržby stroje: ' + E.Message);
     end;
    finally
     lQry.Free;
   end;
  end;




 function TQMSUdrzbaStrojuAZarizeniService.GetAll: TObjectList<TQMSUdrzbaStrojuAZarizeni>;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   result:= nil;

   lSQL:= 'SELECT ' + GetTabCols ('dbo', tblKJ, '', false, '') + ' FROM ' + tblKJ + ' WHERE Kategorie IN (SELECT Cislo FROM ' + tblKategKJ;
   lSQL:= lSQL + ' WHERE QMSAgenda=1) ORDER BY Kategorie, PoradoveCislo';

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= FDM.sqlConn;
     try
       lQry.Open(lSQL);
       result:= lQry.AsObjectList<TQMSUdrzbaStrojuAZarizeni>;
      except on E:Exception do
       raise EServiceException.Create('Chyba načítání údržby stroje: ' + E.Message);
     end;
    finally
     lQry.Free;
   end;
  end;




 function TQMSUdrzbaStrojuAZarizeniService.GetByID (const AID: Integer): TQMSUdrzbaStrojuAZarizeni;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   result:= nil;

   lSQL:= 'SELECT ' + GetTabCols ('dbo', tblKJ, '', false, '') + ' FROM ' + tblKJ + ' WHERE Kategorie IN (SELECT Cislo FROM ' + tblKategKJ;
   lSQL:= lSQL + ' WHERE QMSAgenda=1) AND ID=:ID';

   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= FDM.sqlConn;
     lQry.Open(lSQL, [AID]);
     if not(lQry.EOF) then
       result:= lQry.AsObject<TQMSUdrzbaStrojuAZarizeni>
     else
       raise EServiceException.Create('Údržba stroje s ID ' + AID.ToString + ' nebyla nalezena.');
    finally
     lQry.Free;
   end;
  end;



 function TQMSUdrzbaStrojuAZarizeniService.GetByFindParams (SearchTerm: string): TObjectList<TQMSUdrzbaStrojuAZarizeni>;
  var lSQL: string;
      lQry: TFDQuery;
      AID: integer;
  begin
   result:= GetAll;
{
   if not SearchTerm.IsEmpty then
     for var i:= Result.Count - 1 downto 0 do
       if not Result[I].MovieName.ToUpper.Contains(SearchTerm.ToUpper) then
         Result.Delete(I);;
}
  end;



 function TQMSUdrzbaStrojuAZarizeniService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
  begin
   lSQL:= 'SELECT ' + GetTabCols ('dbo', tblKJ, '', false, '') + ' FROM ' + tblKJ + ' WHERE 1=0';
   lQry:= TFDQuery.Create(nil);
   try
     lQry.Connection:= FDM.sqlConn;
     lQry.Open(lSQL);
     Result := lQry.MetadataAsJSONObject();
    finally
     lQry.Free;
   end;
  end;


end.