unit datMod2;

interface

uses
  System.SysUtils, System.Classes,
  ddPlugin_TLB, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error,
  FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf, Data.DB, FireDAC.Comp.DataSet,
  FireDAC.Comp.Client,
  frmPlan, frmRamcovyPlan, Vcl.BaseImageCollection, Vcl.ImageCollection, System.ImageList, Vcl.ImgList,
  Vcl.VirtualImageList, Vcl.Controls;

{$I iConsts.inc}

type
  TGridColWidthKoefs = record
   fieldName: string;
   widthKoef: Extended;
  end;

  TDavkaPrikazOdv = record
   cislo: integer;
   idPrikaz: integer;
   idDavka: integer;
   mnozOdvPrikaz: extended;
   mnozZapsane: Extended;
   mnozOdvPredchozi: extended;
  end;


  Tdm2 = class(TDataModule)
    vtRozpis: TFDMemTable;
    dsRozpis: TDataSource;
    dsKalendar: TDataSource;
    vtKalendar: TFDMemTable;
    vtRozpiscolDatum: TDateField;
    vtRozpiscolID: TIntegerField;
    vtRozpiscolHodOd: TSmallintField;
    vtRozpiscolHodDo: TSmallintField;
    vtRozpiscolSkupZbo: TStringField;
    vtRozpiscolRegCis: TStringField;
    vtRozpiscolNazev: TStringField;
    vtRozpiscolPrikaz: TStringField;
    vtRozpiscolMnoz: TSmallintField;
    vtRozpiscolMnozHotovo: TSmallintField;
    vtKalendarcolIDStroj: TIntegerField;
    vtKalendarcolStroj: TStringField;
    vtKalendarcolNe: TSmallintField;
    vtKalendarcolPo: TSmallintField;
    vtKalendarcolUt: TSmallintField;
    vtKalendarcolSt: TSmallintField;
    vtKalendarcolCt: TSmallintField;
    vtKalendarcolPa: TSmallintField;
    vtKalendarcolSo: TSmallintField;
    vtKalendarcolKodStroj: TStringField;
    dsKmen: TDataSource;
    vtKmen: TFDMemTable;
    vtKmencolID: TIntegerField;
    vtKmencolSZ: TStringField;
    vtKmencolRegCis: TStringField;
    vtKmencolNazev1: TStringField;
    vtKmencolMJEvid: TStringField;
    vtKmencolDilec: TBooleanField;
    vtKmencolMaterial: TBooleanField;
    vtKmencolSluzba: TBooleanField;
    vtKmencolSKP: TStringField;
    imgCol: TImageCollection;
    imgList: TVirtualImageList;
    ImageList1: TImageList;
    vtKmenIDZaklad: TIntegerField;
    vtKmencolZaklad: TStringField;
    vtKmencolIDStroj: TIntegerField;
    vtKmencolStroj: TStringField;
    vtKmencolKSvKA: TIntegerField;
    vtKmencolKAnaPAL: TIntegerField;
    vtKmencolKSnaPAL: TIntegerField;
    vtKmencolIDZakModif: TIntegerField;
    dsZavady: TDataSource;
    vtZavady: TFDMemTable;
    vtZavadycolID: TIntegerField;
    vtZavadycolKod: TStringField;
    vtZavadycolTyp: TIntegerField;
    vtZavadycolNazev: TStringField;
    vtRozpiscolIDDavkaAdvPlan: TIntegerField;
    vtRozpiscolIdKmen: TIntegerField;
    vtRozpiscolIdPrikaz: TIntegerField;
    vtVyrobniTyden: TFDMemTable;
    dsVyrobniTyden: TDataSource;
    vtVyrobniTydencolTyden: TIntegerField;
    vtVyrobniTydencolDatumOd: TDateField;
    vtVyrobniTydencolDatumDo: TDateField;
    vtVyrobniTydencolRok: TIntegerField;
    dsRamcovyPlan: TDataSource;
    vtRamcovyPlan: TFDMemTable;
    vtRamcovyPlanID: TIntegerField;
    vtRamcovyPlanIDKmen: TIntegerField;
    vtRamcovyPlanSZ: TStringField;
    vtRamcovyPlanRegCis: TStringField;
    vtRamcovyPlanNazev1: TStringField;
    vtRamcovyPlanMJ: TStringField;
    vtRamcovyPlanKs: TIntegerField;
    vtRamcovyPlanKA: TIntegerField;
    vtRamcovyPlanCisloOrg: TIntegerField;
    vtRamcovyPlanM01: TIntegerField;
    vtRamcovyPlanM02: TIntegerField;
    vtRamcovyPlanM03: TIntegerField;
    vtRamcovyPlanM04: TIntegerField;
    vtRamcovyPlanM05: TIntegerField;
    vtRamcovyPlanM06: TIntegerField;
    vtRamcovyPlanM07: TIntegerField;
    vtRamcovyPlanM08: TIntegerField;
    vtRamcovyPlanM09: TIntegerField;
    vtRamcovyPlanM10: TIntegerField;
    vtRamcovyPlanM11: TIntegerField;
    vtRamcovyPlanM12: TIntegerField;
    vtRamcovyPlanSumace: TIntegerField;
    vtRamcovyPlanVyroba: TIntegerField;
    vtRamcovyPlanZbyva: TIntegerField;
    vtRamcovyPlanSklRoo: TIntegerField;
    vtRamcovyPlanSklTvin: TIntegerField;
    vtRamcovyPlanSklAlter: TIntegerField;
    vtRamcovyPlanSklMimoPlan: TBooleanField;
    dsRPlanAlter: TDataSource;
    vtRPlanAlter: TFDMemTable;
    vtRPlanAlterID: TIntegerField;
    vtRPlanAlterKalibr: TStringField;
    vtRPlanAlterDelkaJm: TFloatField;
    vtRPlanAlterStroj: TStringField;
    vtRPlanAlterIDStroj: TIntegerField;
    dsPlanPL: TDataSource;
    vtPlanPL: TFDMemTable;
    vtPlanPLIDVCPrikaz: TIntegerField;
    vtPlanPLCisloPL: TStringField;
    vtPlanPLDatVyrobaZadano: TDateTimeField;
    vtPlanPLDatVyrobaStart: TDateTimeField;
    vtPlanPLDatExpedice: TDateTimeField;
    vtPlanPLKAnaPal: TFloatField;
    vtPlanPLKusu: TIntegerField;
    vtPlanPLIDPrikaz: TIntegerField;
    vtPlanPLDatExpirace: TDateTimeField;
    vtRozpiscolIDPlan: TIntegerField;
    vtPlanPLCisloExtZak: TStringField;
    vtRozpiscolJeUdrzba: TSmallintField;
    vtZavadycolIDPrikaz: TIntegerField;
    procedure DataModuleCreate (Sender: TObject);
    procedure vtKmenCalcFields(DataSet: TDataSet);
   private
   public
    Helios: IHelios;
    function VratPrepocetMJ (idKmen: integer; hlavniMJ, odvozenaMJ: string; sloupec: string='PocetOdvozene'): Extended;
    procedure VymazRozpis (f: TformPlan); safecall;
    procedure NactiRozpis (f: TformPlan; prazdny: boolean=false); safecall;
    procedure NactiDavkyPlanu (f: TformPlan; idStroj: Integer=0); safecall;
    procedure GenerujRozpis (odData: TDateTime; pocetDnu: integer=14); safecall;
    procedure NactiKalendar (f: TformPlan; idStroj: integer=0); safecall;
    procedure NactiKmenZbozi (podm: string=''); safecall;
    procedure NactiUdrzbu (kodModif: string=''; podm: string=''); safecall;
    procedure NactiVyrobniTydny (rok: integer);
    procedure PlanPalListyNactiSarzePrikazu (idPrikaz, ksVKA: integer; var externiZak: string);
  end;

var
  dm2: Tdm2;
  dmCreated, apiMod: boolean;
  apiServer, phServer, url, sqlLang: string;
  idAdvKPl, bidPalety: integer;

implementation
{%CLASSGROUP 'Vcl.Controls.TControl'}
uses System.Variants, System.StrUtils, Winapi.Windows, System.DateUtils,
     helTabsBIDs, helUtils;
{$R *.dfm}


 procedure Tdm2.DataModuleCreate (Sender: TObject);
  begin
   dmCreated:= true;
  end;




 function Tdm2.VratPrepocetMJ (idKmen: integer; hlavniMJ, odvozenaMJ: string; sloupec: string='PocetOdvozene'): Extended;
  var lSQL: string;
  begin
   result:= 0;
   sloupec:= sqlSanitize (sloupec);
   lSQL:= 'SELECT ' + sloupec + ' FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKmen.ToString + ' AND LOWER(KodMJ1)=N' + hlavniMJ.ToLower.QuotedString
        + ' AND LOWER(KodMJ2)=N' + odvozenaMJ.ToLower.QuotedString;
   try
     result:= helUtils.getHeliosFloatVal (Helios, 0, lSQL);
    finally
   end;
  end;




 procedure Tdm2.NactiUdrzbu (kodModif: string=''; podm: string='');
  var lSQL, kod: string;
      idPrPost: Integer;
  begin
   if not(vtZavady.Active) then
    vtZavady.Open;

// lehka sanitizace SQL
   podm:= podm.Replace(';', '').Replace('--','');

   lSQL:= 'SELECT TOP(1) pp.ID, pp.Nazev, pp.IDPrikaz, pp.operace FROM ' + tblPrPost + ' pp INNER JOIN ' + tblVPr + ' vp ON (vp.ID=pp.IDPrikaz AND pp.IDOdchylkyDo IS NULL AND pp.priorita=0)'
        + ' INNER JOIN ' + tblZakazModif + ' m ON (m.ID=vp.IDZakazModif) INNER JOIN ' + tblPrikazRada + ' vpr ON (vpr.Rada=vp.Rada) WHERE pp.Typ=0 AND vp.StavPrikazu=30 AND m.Kod=N'
        + kodModif.QuotedString + ' AND vpr.nazev LIKE N''%údržb%''';

{   lSQL:= 'SELECT ID, Kod, Nazev, Typ FROM ' + tblCZavad;
   if (podm<>'') then
    lSQL:= lSQL + ' WHERE ' + podm;
}
   lSQL:= lSQL + ' ORDER BY pp.Nazev';
   try
     with Helios.OpenSQL (lSQL) do
      begin
       First;
       while not(EOF) do
        begin
         vtZavady.Append;
          idPrPost:= VarToStr(FieldByNameValues('ID')).ToInteger;
          vtZavady.FieldByName('colID').AsInteger:= idPrPost;
          vtZavady.FieldByName('colNazev').AsString:= VarToStr(FieldByNameValues('Nazev'));
          vtZavady.FieldByName('colIDPrikaz').AsInteger:= VarToStr(FieldByNameValues('IDPrikaz')).ToInteger;
          vtZavady.FieldByName('colKod').AsString:= VarToStr(FieldByNameValues('operace')).Trim;
//          vtZavady.FieldByName('colTyp').AsInteger:= VarToStr(FieldByNameValues('Typ')).ToInteger;
         vtZavady.Post;
         Next;
        end;
      end;
    finally
   end;

  end;




 procedure Tdm2.NactiKmenZbozi (podm: string='');
  var lSQL, mjEvid: string;
      idKZ, idZakazModif, idZakazModifDilce: Integer;
  begin
   if not(vtKmen.Active) then
    vtKmen.Open;

// lehka sanitizace SQL
   podm:= podm.Replace(';', '').Replace('--','');

   lSQL:= 'SELECT k.ID, k.SkupZbo, k.RegCis, k.Nazev1, k.SKP, k.MJEvidence, k.Material, k.Dilec, k.Sluzba, zm.Nazev AS NazevModif, zmd.ID AS IdZMD, zm.ID AS IdZM'
        + ', ISNULL( (SELECT TOP(1) kN.Nazev1 FROM ' + tblKVaz + ' v INNER JOIN ' + tblKZ + ' kN ON (kN.ID=v.nizsi) WHERE v.vyssi=k.ID'
        + ' AND v.ZmenaDo IS NULL AND v.IDZakazModif=zm.ID AND kN.SkupZbo=N''702''), N'''') AS Zaklad'
        + ' FROM ' + tblZakazModifDilce + ' zmd INNER JOIN ' + tblZakazModif + ' zm ON (zm.ID=zmd.IDZakazModif) INNER JOIN ' + tblKZ
        + ' k ON (k.ID=zmd.IDKmenZbozi)';
   if (podm<>'') then
    lSQL:= lSQL + ' WHERE ' + podm;
   lSQL:= lSQL + ' ORDER BY k.SkupZbo, k.RegCis, zm.Nazev';
   try
     with Helios.OpenSQL (lSQL) do
      begin
       First;
       while not(EOF) do
        begin
         vtKmen.Append;
          idKZ:= VarToStr(FieldByNameValues('ID')).ToInteger;
          vtKmen.FieldByName('colID').AsInteger:= idKZ;
          vtKmen.FieldByName('colSZ').AsString:= VarToStr(FieldByNameValues('SkupZbo'));
          vtKmen.FieldByName('colRegCis').AsString:= VarToStr(FieldByNameValues('RegCis'));
          vtKmen.FieldByName('colNazev1').AsString:= VarToStr(FieldByNameValues('Nazev1'));
          vtKmen.FieldByName('colSKP').AsString:= VarToStr(FieldByNameValues('SKP'));
          mjEvid:= VarToStr(FieldByNameValues('MJEvidence'));
          vtKmen.FieldByName('colMJEvid').AsString:= mjEvid;
          vtKmen.FieldByName('colMaterial').AsBoolean:= FieldByNameValues('Material');
          vtKmen.FieldByName('colDilec').AsString:= FieldByNameValues('Dilec'); // TrueBoolStrs
          vtKmen.FieldByName('colSluzba').AsBoolean:= FieldByNameValues('Sluzba');
          vtKmen.FieldByName('colZaklad').AsString:= VarToStr(FieldByNameValues('Zaklad'));

          if (mjEvid='KA') then
           begin
            lSQL:= 'SELECT PocetOdvozene FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKZ.ToString + ' AND KodMJ1=N''KA'' AND KodMJ2=N''ks''';
            vtKmen.FieldByName('colKSvKA').AsInteger:= helUtils.getHeliosIntVal (Helios, 0, lSQL);

            lSQL:= 'SELECT PocetHlavni FROM ' + tblMJZbo + ' WHERE IDKmenZbozi=' + idKZ.ToString + ' AND KodMJ1=N''KA'' AND KodMJ2=N''Pal''';
            vtKmen.FieldByName('colKAnaPAL').AsInteger:= helUtils.getHeliosIntVal (Helios, 0, lSQL);
           end;

          vtKmen.FieldByName('colIDZakModifDilce').AsInteger:= VarToStr(FieldByNameValues('IdZMD')).ToInteger;
          vtKmen.FieldByName('colIDZakModif').AsInteger:= VarToStr(FieldByNameValues('IdZM')).ToInteger;
          vtKmen.FieldByName('colZakModif').AsString:= VarToStr(FieldByNameValues('NazevModif'));

         vtKmen.Post;
         Next;
        end;
      end;
    finally
   end;
  end;




 procedure Tdm2.NactiKalendar (f: TformPlan; idStroj: Integer=0);
  var lSQL: string;
      dStart, dAkt, dNedele, dTesty, dNow, d1: TDateTime;
      datumNe, datumTesty: string;
      dAktD, dAktM, dAktR, dTestD, dTestM, dTestR: integer;
      i, cnt, cntNe, cntPo, cntUt, cntSt, cntCt, cntPa, cntSo: integer;
  begin
   if not(vtKalendar.Active) then
    vtKalendar.Open;
   vtKalendar.EmptyDataSet;

   idAdvKPl:= helUtils.getHeliosIntVal (Helios, 0, 'SELECT TOP(1) ID FROM ' + tblAdvKPlan + ' WHERE Kod=N''99''');

   dNedele:= 0;
   dNow:= Now;
   d1:= dNow;
   if (f.sgCas.Cells[2,1]<>'') then // datumOd
    begin
     d1:= StrToDateTime(f.sgCas.Cells[2,1]);
     dNedele:= d1;
    end;

   dStart:= StartOfTheWeek (d1);
   if (dNow=d1) and (dNedele=0) then
    dNedele:= IncDay (dStart, -1);
   dAkt:= d1;
   dAktD:= DayOf (dAkt);
   dAktM:= MonthOf (dAkt);
   dAktR:= YearOf (dAkt);

   lSQL:= 'SELECT cs.ID, cs.Kod, cs.Nazev FROM ' + tblCStroju + ' cs LEFT JOIN ' + tblCStrojuE + ' cse ON (cse.ID=cs.ID) WHERE cs.Blokovano=0';
   lSQL:= lSQL + IfThen(idStroj>0, ' AND cs.ID=' + idStroj.ToString, '') + ' AND ISNULL(cse._Planovani,0)=1 ORDER BY cs.Nazev';
   try
     with Helios.OpenSQL (lSQL) do
      begin
       First;
       while not(EOF) do
        begin
         dTesty:= dNedele;
         vtKalendar.Append;
          idStroj:= VarToStr(FieldByNameValues('ID')).ToInteger;
          vtKalendar.FieldByName('colIDStroj').AsInteger:= idStroj;
          vtKalendar.FieldByName('colKodStroj').AsString:= VarToStr(FieldByNameValues('Kod'));
          vtKalendar.FieldByName('colStroj').AsString:= VarToStr(FieldByNameValues('Nazev'));

          for i:=0 to 6 do
           begin
//            datumTesty:= FormatDateTime ('dd.MM.yyyy', dTesty);
            dTestD:= DayOf (dTesty);
            dTestM:= MonthOf (dTesty);
            dTestR:= YearOf (dTesty);

            lSQL:= 'SELECT COUNT(d.ID) FROM ' + tblAdvKPlanDavky + ' d INNER JOIN ' + tblVPr + ' p ON (p.ID=d.IDPrikaz) WHERE d.IdAdvKapacPlan=' + idAdvKPl.ToString
                 + IfThen(idStroj=0, '', ' AND d.IDStroje=' + idStroj.ToString) + ' AND p.StavPrikazu IN (30,40) AND d.CasOd_D=' + dTestD.ToString
                 + ' AND d.CasOd_M=' + dTestM.ToString + ' AND d.CasOd_Y=' + dTestR.ToString;
            cnt:= helUtils.getHeliosIntVal (Helios, 0, lSQL);
            case i of
             0: begin vtKalendar.FieldByName('colNe').AsInteger:= cnt; cntNe:= cnt; end;
             1: begin vtKalendar.FieldByName('colPo').AsInteger:= cnt; cntPo:= cnt; end;
             2: begin vtKalendar.FieldByName('colUt').AsInteger:= cnt; cntUt:= cnt; end;
             3: begin vtKalendar.FieldByName('colSt').AsInteger:= cnt; cntSt:= cnt; end;
             4: begin vtKalendar.FieldByName('colCt').AsInteger:= cnt; cntCt:= cnt; end;
             5: begin vtKalendar.FieldByName('colPa').AsInteger:= cnt; cntPa:= cnt; end;
             6: begin vtKalendar.FieldByName('colSo').AsInteger:= cnt; cntSo:= cnt; end;
            end;
            dTesty:= IncDay (dTesty, 1);
           end;

         vtKalendar.Post;
         Next;
        end;
      end;
    finally
   end;
  end;




 procedure Tdm2.VymazRozpis (f: TformPlan);
  var i: integer;
      d: string;
      h: integer;
  begin
   if not(vtRozpis.Active) then
    vtRozpis.Open;

   d:= FormatDateTime ('dd.MM.yyyy', Now);
   h:= HourOf(Now);

   vtRozpis.DisableControls;
   vtRozpis.First;
   while not(vtRozpis.Eof) do
    begin
     vtRozpis.Edit;
      vtRozpis.FieldByName('colID').AsInteger:= 0;
      vtRozpis.FieldByName('colIDDavkaAdvPlan').AsInteger:= 0;
      vtRozpis.FieldByName('colPrikaz').AsString:= '';
      vtRozpis.FieldByName('colSkupZbo').AsString:= '';
      vtRozpis.FieldByName('colRegCis').AsString:= '';
      vtRozpis.FieldByName('colNazev').AsString:= '';
      vtRozpis.FieldByName('colMnoz').AsInteger:= 0;
     vtRozpis.Post;
     vtRozpis.Next;
    end;
   vtRozpis.EnableControls;
   vtRozpis.Locate ('colDatum;colHodOd', VarArrayOf ([d, h]), []);
  end;





 procedure Tdm2.NactiDavkyPlanu (f: TformPlan; idStroj: Integer = 0);
  var lSQL: string;
      datum, hodiny, radaPrikaz, prikaz, mj, sz, regCis, nazev1, altPrP, nazevOper: string;
      prvniDatum, prvniHod: string;
      colID, idVPr, idKmen, idVPrOld, casOd, doklPrP, doklPrPOld, jeUdrzba: integer;
      cislo, iTemp: integer;
      mnoz, kusyCisteVPr, mnozOdv, mnozProZapis, mnozZapsane, ksVKA: extended;
      fnd: boolean;
      d, d2: TDateTime;
      aDavkaVPrOdv: TArray<TDavkaPrikazOdv>;

    function SumaZapsanehoMnozstviPred (a: TArray<TDavkaPrikazOdv>; idPrikaz: integer; predCislem: integer=0): extended;
     var i, c: integer;
     begin
      result:= 0;
      for i:=0 to Length(a)-1 do
       begin
        if (a[i].idPrikaz=idPrikaz) then
         if (a[i].cislo<predCislem) then
          result:= result + a[i].mnozZapsane;
       end;
     end;


  begin
   idVPrOld:= 0;
   doklPrPOld:= 0;
   mnozZapsane:= 0;
   iTemp:= 0;

   d:= 0;
   if (f.sgCas.Cells[2,1]<>'') then
    d:= StrToDate(f.sgCas.Cells[2,1]);
   GenerujRozpis (d, 7);
   vtRozpis.EmptyDataSet;

   prvniDatum:= '';
   prvniHod:= '';

   lSQL:= 'SELECT MIN(da.CasOd) FROM ' + tblAdvKPlanDavky + ' da INNER JOIN ' + tblAdvKPlan + ' kp ON (kp.ID=da.IDAdvKapacPlan) INNER JOIN '
        + tblVPr + ' vp ON (vp.ID=da.IDPrikaz) WHERE kp.Kod=N''99'' AND da.IDStroje=' + idStroj.ToString + ' AND vp.StavPrikazu IN (30,40)';
   if (f.sgCas.Cells[2,1]<>'') then
    lSQL:= lSQL + ' AND dbo.hf_TruncDate(da.CasOd)>=dbo.hf_TruncDate(CONVERT(datetime, N' + f.sgCas.Cells[2,1].QuotedString + ',104))';
   if (f.sgCas.Cells[3,1]<>'') then
    lSQL:= lSQL + ' AND dbo.hf_TruncDate(da.CasOd)<=dbo.hf_TruncDate(CONVERT(datetime, N' + f.sgCas.Cells[3,1].QuotedString + ',104))';
   datum:= helUtils.getHeliosDateTimeVal (Helios, 0, lSQL).ToString;
   if (datum<>'30.12.1899') then
    begin
     if (datum.IndexOf(' ')>-1) then
       begin
        prvniDatum:= LeftStr(datum, datum.IndexOf(' '));
        prvniHod:= MidStr(datum, datum.IndexOf(' ')+1, 255);
        if (prvniHod.IndexOf(':')>-1) then
         prvniHod:= LeftStr(prvniHod, prvniHod.IndexOf(':')).Trim;
       end
      else
       begin
        prvniDatum:= datum;
        prvniHod:= '0';
       end;
     if (datum.IndexOf(' ')>-1) then
      datum:= LeftStr(datum, datum.IndexOf(' '));
     GenerujRozpis (StrToDateTime(datum), 7);
     vtRozpis.IndexFieldNames:= 'colDatum;colHodOd';
     vtRozpis.Refresh;
     NactiRozpis (f);
     vtRozpis.Refresh;
    end;


   lSQL:= 'SELECT da.ID, da.CasOd, da.CasDo, ISNULL(dae._MnozstviKs,0) AS MnozstviKs, vp.RadaPrikaz, vp.Prikaz, vp.ID AS IDPrikaz, k.ID AS IDKmen'
        + ', k.SkupZbo, k.RegCis, k.Nazev1, ISNULL(vp.IDPlan,0) AS IDPlan, da.DokladPrPostup, da.AltPrPostup'
        + ', CONVERT(int, CASE WHEN vpr.nazev LIKE N''%údržb%'' THEN 1 ELSE 0 END) AS JeUdrzba'
        + ' FROM ' + tblAdvKPlanDavky + ' da LEFT JOIN ' + tblAdvKPlanDavky_E
        + ' dae ON (dae.ID=da.ID) INNER JOIN ' + tblAdvKPlan + ' kp ON (kp.ID=da.IDAdvKapacPlan) INNER JOIN ' + tblVPr + ' vp ON (vp.ID=da.IDPrikaz) INNER JOIN '
        + tblPrikazRada + ' vpr ON (vpr.Rada=vp.Rada) INNER JOIN '
        + tblKZ + ' k ON (k.ID=vp.IDTabKmen) WHERE kp.Kod=N''99'' AND da.IDStroje=' + idStroj.ToString + ' AND vp.StavPrikazu IN (30,40)';
   if (f.sgCas.Cells[2,1]<>'') then
    lSQL:= lSQL + ' AND dbo.hf_TruncDate(da.CasOd)>=dbo.hf_TruncDate(CONVERT(datetime, N' + f.sgCas.Cells[2,1].QuotedString + ',104))';
   if (f.sgCas.Cells[3,1]<>'') then
    lSQL:= lSQL + ' AND dbo.hf_TruncDate(da.CasOd)<=dbo.hf_TruncDate(CONVERT(datetime, N' + f.sgCas.Cells[3,1].QuotedString + ',104))';
   lSQL:= lSQL + ' ORDER BY da.CasOd';
   with Helios.OpenSQL (lSQL) do
    begin
     First;
     while not(EOF) do
      begin
       colID:= FieldByNameValues('ID');
       datum:= VarToStr(FieldByNameValues('CasOd'));

       idVPr:= VarToStr(FieldByNameValues('IDPrikaz')).ToInteger;
       doklPrP:= VarToStr(FieldByNameValues('DokladPrPostup')).ToInteger;
       altPrP:= VarToStr(FieldByNameValues('AltPrPostup'));

       lSQL:= 'SELECT TOP(1) nazev FROM ' + tblPrPost + ' WHERE IDPrikaz=' + idVpr.ToString + ' AND Doklad=' + doklPrP.ToString + ' AND Alt=N' + altPrP.QuotedString
            + ' AND IDOdchylkyDo IS NULL AND Priorita=0';
       nazevOper:= helUtils.getHeliosStrVal(Helios, '', lSQL);


       Inc(cislo);
       if (idVPr<>idVPrOld) then
        begin
         cislo:= 1;
         idKmen:= helUtils.getHeliosIntVal (Helios, 0, 'SELECT IDTabKmen FROM ' + tblVPr + ' WHERE ID=' + idVPr.ToString);
         mj:= helUtils.getHeliosStrVal (Helios, '', 'SELECT MJEvidence FROM ' + tblKZ + ' WHERE ID=' + idKmen.ToString);
         ksVKA:= helUtils.getHeliosFloatVal (Helios, 0, 'SELECT dbo.ef_Kmen_VratPocetKsVKartonu (' + idKmen.ToString + ')');

         kusyCisteVPr:= helUtils.getHeliosFloatVal (Helios, 0, 'SELECT kusy_ciste FROM ' + tblVPr + ' WHERE ID=' + idVPr.ToString);
         lSQL:= 'SELECT SUM(kusy_odv+kusy_zmet_opr+kusy_zmet_neopr) FROM ' + tblPMZ + ' WHERE IDPrikaz=' + idVPr.ToString + ' AND DokladPrPostup=' + doklPrP.ToString;
         mnozOdv:= helUtils.getHeliosFloatVal (Helios, 0, lSQL);
         if (mj.ToUpper='KA') then
          begin
           kusyCisteVPr:= kusyCisteVPr * ksVKA;
           mnozOdv:= mnozOdv * ksVKA;
          end;
         idVPrOld:= idVPr;
        end;

//       idVPrOld:= idVPr;


{
       mnoz:= FieldByNameValues('MnzstviKs');
       radaPrikaz:= FieldByNameValues('RadaPrikaz');
       prikaz:= FieldByNameValues('Prikaz');
       regCis:= FieldByNameValues('RegCis');
       nazev1:= FieldByNameValues('Nazev1');
}
       if (datum<>'') then
        begin
         if (datum.IndexOf(' ')=-1) then
          datum:= datum + ' 00:00:00';
         if (datum.IndexOf(' ')>-1) then
          begin
           hodiny:= MidStr(datum, datum.IndexOf(' ')+1, 255).Trim;
           casOd:= LeftStr(hodiny, hodiny.IndexOf(':')).ToInteger;
           datum:= LeftStr(datum, datum.IndexOf(' '));

           mnozProZapis:= VarToStr(FieldByNameValues('MnozstviKs')).ToExtended;

           SetLength (aDavkaVPrOdv, iTemp+1);
           aDavkaVPrOdv[iTemp].idPrikaz:= idVPr;
           aDavkaVPrOdv[iTemp].cislo:= cislo;
           aDavkaVPrOdv[iTemp].idDavka:= colID;


           fnd:= vtRozpis.Locate ('colIDDavkaAdvPlan', VarArrayOf ([colID]), []); // colID / colDatum / colHodOd
           if not(fnd) then
            begin
             fnd:= vtRozpis.Locate ('colDatum;colHodOd', VarArrayOf ([datum, casOd]), []);
             if (fnd) then
              begin
               vtRozpis.Edit;
                vtRozpis.FieldByName('colID').AsInteger:= colID;
                vtRozpis.FieldByName('colIDDavkaAdvPlan').AsInteger:= colID;
                vtRozpis.FieldByName('colIdKmen').AsString:= VarToStr (FieldByNameValues('IDKmen'));
                vtRozpis.FieldByName('colPrikaz').AsString:= VarToStr (FieldByNameValues('RadaPrikaz'));
                vtRozpis.FieldByName('colIdPrikaz').AsString:= VarToStr (FieldByNameValues('IDPrikaz'));
                vtRozpis.FieldByName('colSkupZbo').AsString:= VarToStr (FieldByNameValues('SkupZbo'));
                vtRozpis.FieldByName('colRegCis').AsString:= VarToStr (FieldByNameValues('RegCis'));
                vtRozpis.FieldByName('colNazev').AsString:= VarToStr (FieldByNameValues('Nazev1'));
                vtRozpis.FieldByName('colMnoz').AsExtended:= mnozProZapis;
                vtRozpis.FieldByName('colIDPlan').AsInteger:= VarToStr (FieldByNameValues('IDPlan')).ToInteger;

                jeUdrzba:= VarToStr(FieldByNameValues('JeUdrzba')).ToInteger;
                vtRozpis.FieldByName('colJeUdrzba').AsInteger:= jeUdrzba;
                if (jeUdrzba=1) then
                 vtRozpis.FieldByName('colNazev').AsString:= nazevOper;


                if (mnozOdv>0) then
                 begin
                  mnozZapsane:= SumaZapsanehoMnozstviPred (aDavkaVPrOdv, idVPr, cislo);
                  mnozOdv:= mnozOdv - mnozZapsane;
                  if (mnozOdv>mnozProZapis) then
                    begin
                     vtRozpis.FieldByName('colMnozHotovo').AsExtended:= mnozProZapis;
                     aDavkaVPrOdv[iTemp].cislo:= cislo;
                     aDavkaVPrOdv[iTemp].idPrikaz:= idVPr;
                     aDavkaVPrOdv[iTemp].mnozZapsane:= mnozProZapis;
                    end
                   else
                    begin
                     vtRozpis.FieldByName('colMnozHotovo').AsExtended:= mnozOdv;
                     aDavkaVPrOdv[iTemp].cislo:= cislo;
                     aDavkaVPrOdv[iTemp].idPrikaz:= idVPr;
                     aDavkaVPrOdv[iTemp].mnozZapsane:= mnozOdv;
                     mnozOdv:= 0;
                    end;
                  Inc (iTemp);
                 end;

               vtRozpis.Post;
              end;
            end;
          end;
        end;

       Next;
      end;
    end;

   if (vtRozpis.RecordCount=0) then
    NactiRozpis (f, true);

   vtRozpis.Locate ('colDatum;colHodOd', VarArrayOf ([FormatDateTime ('dd.MM.yyyy', d), HourOf(d)]), []);

   if (prvniDatum<>'') and (prvniHod<>'') then
    vtRozpis.Locate('colDatum;colHodOd', VarArrayOf ([prvniDatum, prvniHod]), []);

  end;




 procedure Tdm2.NactiVyrobniTydny (rok: integer);
  var i: integer;
      d: TDateTime;
      den1ledna: byte;
  begin
   den1ledna:= DayOfWeek(EncodeDate(rok, 1, 1));

   if not(vtVyrobniTyden.Active) then
    vtVyrobniTyden.Open;
   for i:=1 to WeeksInAYear(rok) do
    begin
     vtVyrobniTyden.Append;
      vtVyrobniTyden.FieldByName('colRok').AsInteger:= rok;
      vtVyrobniTyden.FieldByName('colTyden').AsInteger:= i;
      d:= IncDay(StartOfAWeek (rok, i, 1), -1);
      vtVyrobniTyden.FieldByName('colDatumOd').AsDateTime:= d;
      vtVyrobniTyden.FieldByName('colDatumDo').AsDateTime:= IncDay(d, 6);
     vtVyrobniTyden.Post;
    end;
  end;




 procedure Tdm2.NactiRozpis (f: TformPlan; prazdny: boolean=false);
  var lSQL, where, datum: string;
      hDo, casOd: integer;
      fnd: boolean;
  begin
   if not(vtRozpis.Active) then
    vtRozpis.Open;

//   lSQL:= 'SELECT Datum FROM ' + tblPlanRozpad + ' WHERE Datum>=DATEADD(day, -2, dbo.hf_TruncDate(GETDATE())) ORDER BY Datum';
   where:= '';

   lSQL:= 'SELECT Datum, CasOd FROM ' + tblPlanRozpad;
   if not(prazdny) then
    begin
     where:= 'Datum>=dbo.hf_TruncDate( ISNULL( (SELECT MIN(d.CasOd) FROM ' + tblAdvKPlanDavky + ' d INNER JOIN ' + tblVPr;
     where:= where + ' p ON (p.ID=d.IDPrikaz) WHERE p.StavPrikazu IN (30,40) AND d.IdAdvKapacPlan=' + idAdvKPl.ToString + '), DATEADD(day, -2, dbo.hf_TruncDate(GETDATE()))) )';
    end;

   if (f.sgCas.Cells[2,1]<>'') then
    where:= where + IfThen(where<>'', ' AND ', '') + 'dbo.hf_TruncDate(Datum)>=dbo.hf_TruncDate(CONVERT(datetime, N' + f.sgCas.Cells[2,1].QuotedString + ',104))';
   if (f.sgCas.Cells[3,1]<>'') then
    where:= where + IfThen(where<>'', ' AND ', '') + 'dbo.hf_TruncDate(Datum)<=dbo.hf_TruncDate(CONVERT(datetime, N' + f.sgCas.Cells[3,1].QuotedString + ',104))';

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

   try
     with Helios.OpenSQL (lSQL) do
      begin
       First;
       while not(EOF) do
        begin
         datum:= VarToStr(FieldByNameValues('Datum'));
         if (datum.IndexOf(' ')>-1) then
          datum:= LeftStr(datum, datum.IndexOf(' '));
         casOd:= VarToStr(FieldByNameValues('CasOd')).ToInteger;
         fnd:= vtRozpis.Locate ('colDatum;colHodOd', VarArrayOf ([datum, casOd]), []);
         if not(fnd) then
          begin
           vtRozpis.Append;
            vtRozpis.FieldByName('colDatum').AsString:= datum; // FormatDateTime('dd.mm.yyyy', FieldByNameValues('Datum'));
            vtRozpis.FieldByName('colHodOd').AsInteger:= casOd; // FormatDateTime('h', FieldByNameValues('Datum'));
//            hDo:= vtRozpis.FieldByName('colHodOd').AsInteger + 1;
            hDo:= casOd + 1;
//            if (hDo>=24) then hDo:= 0;
            vtRozpis.FieldByName('colHodDo').AsInteger:= hDo;
           vtRozpis.Post;
          end;
         Next;
        end;
      end;
    finally
   end;

  end;




 procedure Tdm2.vtKmenCalcFields (DataSet: TDataSet);
  var ksVKar, karNaPal: integer;
  begin
   ksVKar:= 0;
   if not (DataSet.FieldByName('colKSvKA').IsNull) then
    ksVKar:= DataSet.FieldByName('colKSvKA').AsInteger;

   karNaPal:= 0;
   if not (DataSet.FieldByName('colKAnaPAL').IsNull) then
    karNaPal:= DataSet.FieldByName('colKAnaPAL').AsInteger;

   DataSet.FieldByName('colKSnaPAL').AsInteger:= ksVKar * karNaPal;
  end;




 procedure Tdm2.GenerujRozpis (odData: TDateTime; pocetDnu: integer=14);
  var lSQL, d: string;
      c: integer;
  begin
   if (pocetDnu<0) or (pocetDnu>14) then
    pocetDnu:= 14;

   if (helUtils.SQLObjectExists (Helios, 'dbo.ef_Vyroba_GenerujRozpadCasu'))
      and (helUtils.SQLObjectExists (Helios, tblPlanRozpad)) then
    begin
// rozpad na 14 dnu dopredu, po 60 minutach
     lSQL:= 'DECLARE @d DATETIME=NULL' + CRLF + 'DROP TABLE IF EXISTS #Tab' + CRLF + 'CREATE TABLE #Tab (Datum DATETIME)' + CRLF
          + IfThen(odData>0, 'SET @d = CONVERT(datetime, N' + FormatDateTime ('dd.mm.yyyy', odData).QuotedString + ', 104)', '') + CRLF
          + 'INSERT #Tab (Datum) SELECT * FROM dbo.ef_Vyroba_GenerujRozpadCasu (@d, ' + pocetDnu.ToString + ', 60)' + CRLF
          + 'SELECT CAST(Datum AS DATETIME) AS Datum FROM #Tab ORDER BY Datum';
     try
       with Helios.OpenSQL (lSQL) do
        if (RecordCount>0) then
         begin
          First;
          while not(EOF) do
           begin
            d:= VarToStr(FieldByNameValues('Datum'));
            if not(d.Contains('00:00')) then
             d:= d + ' 00:00:00';
            c:= MidStr(d, d.IndexOf(' ')+1, 20).Length;
            if (c=8) then
             d:= d.Replace(' ', ' 0');
            lSQL:= 'IF NOT EXISTS (SELECT 1 FROM ' + tblPlanRozpad + ' WHERE Datum=CONVERT(datetime, N' + d.QuotedString + ', 104) )' + CRLF
                 + 'INSERT ' + tblPlanRozpad + ' (Datum) SELECT CONVERT(datetime, N' + d.QuotedString + ', 104)';
            Helios.ExecSQL (lSQL);
            Next;
           end;
         end;
      finally
     end;
    end;

  end;




 procedure Tdm2.PlanPalListyNactiSarzePrikazu (idPrikaz, ksVKA: Integer; var externiZak: string);
  var lSQL: string;
      idVC: integer;

  begin
   if not(vtPlanPL.Active) then
    vtPlanPL.Open;
   vtPlanPL.EmptyDataSet;

   lSQL:= 'SELECT ID, VyrCislo, Popis, DatExpirace, Mnozstvi FROM ' + tblPrikazVC + ' WHERE IDPrikaz=' + idPrikaz.ToString + ' ORDER BY VyrCislo';
   try
     with Helios.OpenSQL (lSQL) do
      if (RecordCount>0) then
       begin
        First;
        while not(EOF) do
         begin
          vtPlanPL.Append;
           vtPlanPL.FieldByName('idPrikaz').AsInteger:= idPrikaz;
           idVC:= VarToStr(FieldByNameValues('ID')).ToInteger;
           vtPlanPL.FieldByName('IDVCPrikaz').AsInteger:= idVC;
           vtPlanPL.FieldByName('CisloPL').AsString:= VarToStr(FieldByNameValues('VyrCislo'));
           vtPlanPL.FieldByName('KAnaPal').AsInteger:= VarToStr(FieldByNameValues('Mnozstvi')).ToInteger;
           vtPlanPL.FieldByName('kusu').AsInteger:= vtPlanPL.FieldByName('KAnaPal').AsInteger * ksVKA;
           vtPlanPL.FieldByName('DatExpirace').AsString:= VarToStr(FieldByNameValues('DatExpirace'));
           externiZak:= VarToStr(FieldByNameValues('Popis'));

           with Helios.OpenSQL('SELECT DatZahajeni FROM ' + tblPalety + ' WHERE IDVyrCisPrikaz=' + idVC.ToString) do
            if (RecordCount=1) then
             begin
              vtPlanPL.FieldByName('DatVyrobaZadano').AsString:= VarToStr(FieldByNameValues('DatZahajeni'));
             end;

          vtPlanPL.Post;
          Next;
         end;
       end;
    finally

   end;

  end;


end.