unit uSvc_KmenZbozi;

interface
{$I 'GlobalDefs.inc'}

uses
  System.Generics.Collections,
  System.SysUtils,
  FireDAC.Stan.Option,
  FireDAC.Comp.Client,
  FireDAC.Stan.Param,
  JsonDataObjects,
  uSvc_Base,
  uCommons,
  uHeoObj_Base;

const
{$I globalConsts.inc}

 sqlSelKmen = 'ID, SkupZbo, RegCis, Nazev1, Nazev2, Nazev3, Nazev4, MJEvidence, Dilec, Montaz, Naradi, Material, Sluzba, SKP, PLUKod'
            + ', KontrolaVyrC, Poznamka, Blokovano, Hmotnost, Vyska, Sirka, Hloubka, Vykres, KmenoveStredisko, CelniNomenklatura, IdSortiment'
            + ', IdVarianta, VychoziMnozstvi, BlokovaniEditoru';


 selKmenSpec = '';

type
  TSkupinaZboziService = class(TServiceBase)
   public
    function GetAll: TObjectList<TSkupinaKaret>; virtual;
    function GetByID (const AID: Integer; params: TDictionary<string, string>=nil): TSkupinaKaret; virtual;
    function GetByParams (params: TDictionary<string, string>): TObjectList<TSkupinaKaret>; virtual;
    function GetMeta: TJSONObject; virtual;
  end;




  TKmenZboziService = class(TServiceBase)
   public
    function GetAll: TObjectList<TKmenZbozi>; virtual;
    function GetByID (const AID: Integer; params: TDictionary<string, string>=nil; AConn: TFDConnection = nil): TKmenZbozi; virtual;
    function GetByParams (params: TDictionary<string, string>): TObjectList<TKmenZbozi>; virtual;
    function GetMeta: TJSONObject; virtual;
    function GetPrepocetMJ (idKmen: integer): TObjectList<TPrepocetMJ>; virtual;
  end;



implementation

uses
  System.StrUtils,
  System.Variants,
  Quick.Logger,
  MVCFramework.Commons,
  MVCFramework.FireDAC.Utils,
  MVCFramework.DataSet.Utils,
  MVCFramework.Serializer.Commons,
  helTabsBIDs;





 { TSkupinaKaretService }

 function TSkupinaZboziService.GetMeta: TJsonObject;
  var lSQL: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   sqlConnX:= TFDConnection.Create (nil);
   sqlConnX.ConnectionDefName:= sqlPoolName;

   lSQL:= 'SELECT ' + GetTabCols('dbo', tblSZ) + ' FROM ' + tblSZ + ' WHERE 1=0';
   lQry:= TFDQuery.Create (nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     lQry.Open(lSQL);
     result:= lQry.MetadataAsJSONObject();
    finally
   end;
   lQry.Free;
   sqlConnX.Close;
   sqlConnX.Free;
  end;




 function TSkupinaZboziService.GetByID (const AID: Integer; params: TDictionary<string, string>=nil): TSkupinaKaret;
  var lSQL: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   result:= nil;

   sqlConnX:= TFDConnection.Create (nil);
   sqlConnX.ConnectionDefName:= sqlPoolName;

   lSQL:= 'SELECT ' + GetTabCols('dbo', tblSZ) + ' FROM ' + tblSZ + ' WHERE ID=:ID';
   lQry:= TFDQuery.Create (nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     lQry.Open (lSQL, [AID]);
     result:= lQry.AsObject<TSkupinaKaret>;
    finally
   end;
   lQry.Free;
   sqlConnX.Close;
   sqlConnX.Free;
  end;




 function TSkupinaZboziService.GetAll: TObjectList<TSkupinaKaret>;
  var lSQL: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   result:= nil;

   sqlConnX:= TFDConnection.Create (nil);
   sqlConnX.ConnectionDefName:= sqlPoolName;

   lSQL:= 'SELECT ' + GetTabCols('dbo', tblSZ) + ' FROM ' + tblSZ + ' ORDER BY SkupZbo';
   lQry:= TFDQuery.Create (nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     lQry.Open(lSQL);
     result:= lQry.AsObjectList<TSkupinaKaret>;
    finally
   end;
   lQry.Free;
   sqlConnX.Close;
   sqlConnX.Free;
  end;




 function TSkupinaZboziService.GetByParams (params: TDictionary<string, string>): TObjectList<TSkupinaKaret>;
  var lSQL, where: string;
      aoV: TArray<Variant>;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   result:= nil;

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

   where:= '';

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

   if (params.ContainsKey('sz')) then
    if (params.Items['sz']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'SkupZbo LIKE N' + (params.Items['sz'] + '%').QuotedString;

   if (params.ContainsKey('nazev')) then
    if (params.Items['nazev']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'Nazev LIKE N' + ('%' + params.Items['nazev'] + '%').QuotedString;


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



   sqlConnX:= TFDConnection.Create (nil);
   sqlConnX.ConnectionDefName:= sqlPoolName;

   lQry:= TFDQuery.Create (nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     lQry.Open(lSQL);
     result:= lQry.AsObjectList<TSkupinaKaret>;
    finally
   end;

   lQry.Free;
   sqlConnX.Close;
   sqlConnX.Free;
  end;







{ TKmenZboziService }

 function TKmenZboziService.GetAll: TObjectList<TKmenZbozi>;
  var lSQL: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   result:= nil;

   sqlConnX:= TFDConnection.Create (nil);
   sqlConnX.ConnectionDefName:= sqlPoolName;


   lSQL:= 'SELECT ' + sqlSelKmen + ' FROM ' + tblKZ + ' ORDER BY ID';

   lQry:= TFDQuery.Create (nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     lQry.Open (lSQL);
     result:= lQry.AsObjectList<TKmenZbozi>;
    finally
     lQry.Free;
   end;

   sqlConnX.Free;
  end;





 function TKmenZboziService.GetByID (const AID: Integer; params: TDictionary<string, string>=nil; AConn: TFDConnection = nil): TKmenZbozi;
{$IFDEF CUSTOM_CTRL_Rootvin}
  const rtnASOLTrideni = '_ASOL_IdentifTrideni';
{$ENDIF}
  var lSQL, extInfoStr: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
      lokalniConnection: boolean;
  begin
   result := nil;

   lokalniConnection:= (AConn = nil);
   if (lokalniConnection) then
     begin
      sqlConnX:= TFDConnection.Create (nil);
      sqlConnX.ConnectionDefName:= sqlPoolName;
     end
    else
     sqlConnX:= AConn;

   extInfoStr:= '';
   if (SQLTableExists ('dbo', tblKZe)) then
    begin
     lSQL:= 'SELECT ' + GetTabCols ('dbo', tblKZe, '', true, 'ID', 0, false) + ' FROM ' + tblKZe + ' WHERE ID=:ID';
     lQry:= TFDQuery.Create (nil);
     lQry.Connection:= sqlConnX;
     try
       lQry.Open (lSQL, [AID]);
       if (lQry.RecordCount>0) then
        begin
         extInfoStr:= lQry.AsJSONObject;
{$IFDEF CUSTOM_CTRL_Rootvin}
         if (SQLColumnExists('dbo', tblKZe, rtnASOLTrideni)) then
          if not(lQry.FieldByName(rtnASOLTrideni).IsNull) then
           begin
            if (lQry.FieldByName(rtnASOLTrideni).AsString='1') then
             result.TypDilce:= 'surovina';
            if (lQry.FieldByName(rtnASOLTrideni).AsString='2') then
             result.TypDilce:= 'polotovar';
            if (lQry.FieldByName(rtnASOLTrideni).AsString='3') then
             result.TypDilce:= 'obal';
           end;
{$ENDIF}
        end;
      finally
       lQry.Free;
     end;
    end;


   lSQL:= 'SELECT ' + GetTabCols ('dbo', tblKZ, 'main') + IfThen(selKmenSpec<>'', ',' + selKmenSpec, '') + ' FROM ' + tblKZ + ' main WHERE main.ID=:ID';
   lQry:= TFDQuery.Create (nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     try
       lQry.Open (lSQL, [AID]);
       result:= lQry.AsObject<TKmenZbozi>;
       if (extInfoStr<>'') then
        result.ExtInfo:= ExtInfoStr;
      except on E:Exception do
       raise EServiceException.Create ('Chyba nacitani kmenove karty: ' + E.Message);
     end;
    finally
     lQry.Free;
   end;

   if (lokalniConnection) then
    sqlConnX.Free;

  end;




 function TKmenZboziService.GetByParams (params: TDictionary<string, string>): TObjectList<TKmenZbozi>;
  var lSQL, where, errMsg: string;
      AID, cnt, rNo: integer;
      aoV: TArray<Variant>;
      resList: TObjectList<TKmenZbozi>;
      resObj: TKmenZbozi;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   result:= nil;

   resList:= TObjectList<TKmenZbozi>.Create;

   sqlConnX:= TFDConnection.Create(nil);
   sqlConnX.ConnectionDefName:= sqlPoolName;

   lSQL:= 'SELECT ID, SkupZbo, RegCis FROM ' + tblKZ;

   where:= '';

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

   if (params.ContainsKey('sz')) then
    if (params.Items['sz']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'SkupZbo LIKE N' + (params.Items['sz'] + '%').QuotedString;

   if (params.ContainsKey('sk')) then
    if (params.Items['sk']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'SkupZbo LIKE N' + (params.Items['sk'] + '%').QuotedString;

   if (params.ContainsKey('rc')) then
    if (params.Items['rc']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'RegCis LIKE N' + (params.Items['rc'] + '%').QuotedString;

   if (params.ContainsKey('cisloZbozi')) then
    if (params.Items['cisloZbozi']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'CisloZbozi LIKE N' + (params.Items['cisloZbozi'] + '%').QuotedString;

   if (params.ContainsKey('nazev1')) then
    if (params.Items['nazev1']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'Nazev1 LIKE N' + ('%' + params.Items['nazev1'] + '%').QuotedString;

   if (params.ContainsKey('barcode')) then
    if (params.Items['barcode']<>'') then
     where:= where + IfThen(where<>'', ' AND ', '') + 'ID IN (SELECT IDKmenZbo FROM ' + tblBCode + ' WHERE Barcode=N' + params.Items['barcode'].QuotedString + ')';



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


   lQry:= TFDQuery.Create(nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     lQry.Open (lSQL);
     cnt:= lQry.RecordCount;
     if (cnt>0) then
       begin
        try
          resObj:= TKmenZbozi.Create;
          resList:= TObjectList<TKmenZbozi>.Create;
          try
            rNo:= 1;
            lQry.First;
            while (rNo<=cnt) do // nouzove reseni, lQry se po nacteni GetbyID sama uzavre !!
             begin
              lQry.RecNo:= rNo;
              AID:= lQry.FieldByName('ID').AsInteger;
              resObj:= self.GetByID (AID);
              resList.Add (resObj);
              if not(lQry.Active) then
               lQry.Open;
              Inc(rNo);
             end;
            result:= resList;
           except on E:Exception do
            begin
             errMsg:= E.Message;
             raise EServiceException.Create ('Chyba nacitani kmenove karty: ' + errMsg);
            end;
          end;
  //        FreeAndNil(resList);
         finally
        end;
       end
      else
       raise EServiceException.Create ('Vybranym podminkam neodpovida zadna kmenova karta');
    finally

   end;
   lQry.Free;
   sqlConnX.Close;
   sqlConnX.Free;

  end;




 function TKmenZboziService.GetMeta: TJSONObject;
  var lSQL: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   sqlConnX:= TFDConnection.Create (nil);
   sqlConnX.ConnectionDefName:= sqlPoolName;

   lSQL:= 'SELECT ' + GetTabCols('dbo', tblKZ, '', false) + ' FROM ' + tblKZ + ' WHERE 1=0';
   lQry:= TFDQuery.Create(nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     lQry.Open (lSQL);
     result:= lQry.MetadataAsJSONObject();
    finally
   end;
   lQry.Free;
   sqlConnX.Close;
   sqlConnX.Free;
  end;




 function TKmenZboziService.GetPrepocetMJ (idKmen: integer): TObjectList<TPrepocetMJ>;
  var lSQL: string;
      lQry: TFDQuery;
      sqlConnX: TFDConnection;
  begin
   sqlConnX:= TFDConnection.Create (nil);
   sqlConnX.ConnectionDefName:= sqlPoolName;

   lSQL:= 'SELECT ID, KodMJ1, KodMJ2, PocetHlavni, PocetOdvozene, DatPorizeni FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=:ID';
   lQry:= TFDQuery.Create(nil);
   try
     lQry.FetchOptions.Mode:= fmAll;
     lQry.Connection:= sqlConnX;
     lQry.Open (lSQL, [idKmen]);
     result:= lQry.AsObjectList<TPrepocetMJ>;
    finally
   end;
   lQry.Free;
   sqlConnX.Close;
   sqlConnX.Free;
  end;


end.