Attribute VB_Name = "Adjustment"
Option Compare Database
Public dB As Database
Public Inp, Out, InpSystPer As Recordset
Public OutQry As QueryDef
Public Loc, Empt, TxtPar, Func, Oscis, MinPer, MaxPer, LastPer, SystPer As String
Public i, j As Integer
Public Dovce As Single
Public UpdFuture As Boolean

Public Sub UpdateMthlyDovce()

Set dB = CurrentDb()
Set Inp = dB.OpenRecordset("00_01DovceMthlyUpdateQ", dbOpenDynaset)

Rem DBEngine.SetOption dbMaxLocksPerFile, 1000000
Rem Set Out = db.OpenRecordset("SELECT loc, oscis, cicin, obd, dovce from DovceMthly WHERE left(obd,4)=" & "'" & Left(DLookup("ValText", "Parameters", "[Parameter]= " & "'" & "ReportPeriod" & "'"), 4) & "'", dbOpenDynaset)
Rem Do Until Out.EOF
Rem    Out.Delete
Rem    Out.MoveNext
Rem Loop
CurrentDb.Execute "DELETE loc, oscis, cicin, obd, dovce from DovceMthly WHERE left(obd,4)=" & "'" & Left(DLookup("ValText", "Parameters", "[Parameter]= " & "'" & "ReportPeriod" & "'"), 4) & "'"

Set Out = dB.OpenRecordset("DovceMthly", dbOpenDynaset)
TxtPar = ""
Do Until Inp.EOF
        
        If Inp(0).Value & Inp(1).Value & Inp(2).Value & Left(Inp(3).Value, 4) <> TxtPar Then Dovce = 0
        
        Out.AddNew
        Out(0).Value = Inp(0).Value
        Out(1).Value = Inp(1).Value
        Out(2).Value = Inp(2).Value
        Out(3).Value = Inp(3).Value
        Out(4).Value = Inp(4).Value - Dovce
        Out.Update
        
        Dovce = Inp(4).Value
        TxtPar = Inp(0).Value & Inp(1).Value & Inp(2).Value & Left(Inp(3).Value, 4)
        
    Inp.MoveNext
Loop

Set Inp = Nothing
Set Out = Nothing
Set dB = Nothing

End Sub

Public Sub PrepareBudgetFile()

Forms!MainForm.BgtPrepInfo = "Zpracovávám dotaz ... krok 1/2"

Set dB = CurrentDb()
Set Inp = dB.OpenRecordset("03_60BgtInputFinQ", dbOpenDynaset)
Set Out = dB.OpenRecordset("BgtMasterFile", dbOpenDynaset)

Do Until Out.EOF
    Out.Delete
    Out.MoveNext
Loop

Forms!MainForm.BgtPrepInfo = "Kopíruji data ... krok 2/2"
Do Until Inp.EOF
        
    Out.AddNew
    For i = 0 To Inp.Fields.Count - 1
        Out(i).Value = Inp(i).Value
    Next i
    Out.Update
        
    Inp.MoveNext

Loop

Set Inp = Nothing
Set Out = Nothing
Set dB = Nothing

Forms!MainForm.BgtPrepInfo = ""
MsgBox ("Hotovo.")

End Sub

Public Sub Unhash()

Set dB = CurrentDb()
Set Inp = dB.OpenRecordset("Dotaz1", dbOpenDynaset)

Do Until Inp.EOF
    Inp.Edit
        Inp(1).Value = Inp(2).Value
    Inp.Update
    Inp.MoveNext
Loop

Set dB = Nothing
Set Inp = Nothing

End Sub

Public Sub SystemizaceMonthly()

MinPer = Val(Left(DLookup("ValText", "Parameters", "[Parameter]= " & "'ReportPeriod'"), 4) & "01")
MaxPer = Val(DLookup("ValText", "Parameters", "[Parameter]= " & "'ReportPeriod'"))

CurrentDb.Execute "DELETE obd, ns, kateg, volume FROM SystemizaceMthly WHERE left(obd,4)=" & "'" & Left(MinPer, 4) & "'"

Set dB = CurrentDb()
Set InpSystPer = dB.OpenRecordset("SystPeriodGroupQ", dbOpenDynaset)
Set Out = dB.OpenRecordset("SELECT obd, ns, kateg, volume FROM SystemizaceMthly", dbOpenDynaset)

For i = MinPer To MaxPer
    
    SystPer = ""
    InpSystPer.MoveFirst
    Do Until InpSystPer.EOF
        If InpSystPer(0).Value >= CStr(i) Then
            SystPer = InpSystPer(0).Value
            GoTo FoundSystPer
        End If
        InpSystPer.MoveNext
    Loop

FoundSystPer:

    Set OutQry = dB.QueryDefs("30_30SystSelPerUtvSumQ")
    OutQry.Sql = "SELECT Systemizace.utvar, Systemizace.kateg, Sum(Systemizace.volume) AS volume, IIf(Not IsNull([CisPracNS].[pracv]),'NS',IIf(Not IsNull([CisUtvPrim].[utv]),'Utv','Neex')) AS PracLevel" & _
    " FROM (Systemizace LEFT JOIN CisPracNS ON Systemizace.utvar = CisPracNS.pracv) LEFT JOIN CisUtvPrim ON Systemizace.utvar = CisUtvPrim.utv WHERE (Systemizace.Obd) = " & "'" & SystPer & "'" & _
    " GROUP BY Systemizace.utvar, Systemizace.kateg, IIf(Not IsNull([CisPracNS].[pracv]),'NS',IIf(Not IsNull([CisUtvPrim].[utv]),'Utv','Neex')) HAVING Sum(Systemizace.volume) <> 0"
    
    Set OutQry = dB.QueryDefs("30_31ActSelPerNsUtvSumQ")
    OutQry.Sql = "SELECT DetailAbs.pracv, CisPracNS.utv, DetailAbs.kateg, Sum(DetailAbs.prevp) AS prevp, DetailAbs.obd FROM DetailAbs LEFT JOIN CisPracNS ON DetailAbs.pracv = CisPracNS.pracv" & _
    " GROUP BY DetailAbs.pracv, CisPracNS.utv, DetailAbs.kateg, DetailAbs.obd HAVING ((Sum(DetailAbs.prevp))<>0) AND ((DetailAbs.obd)= " & "'" & CStr(i) & "')"
        
    Set OutQry = dB.QueryDefs("30_32ActSelPerNsSumQ")
    OutQry.Sql = "SELECT DetailAbs.pracv, DetailAbs.kateg, Sum(DetailAbs.prevp) AS prevp, DetailAbs.obd From DetailAbs GROUP BY DetailAbs.pracv, DetailAbs.kateg, DetailAbs.obd" & _
    " HAVING ((Sum(DetailAbs.prevp))<>0) AND ((DetailAbs.obd)= " & "'" & CStr(i) & "')"

    Set OutQry = dB.QueryDefs("30_33ActSelPerUtvSumQ")
    OutQry.Sql = "SELECT CisPracNS.utv, DetailAbs.kateg, Sum(DetailAbs.prevp) AS prevp, DetailAbs.obd FROM DetailAbs LEFT JOIN CisPracNS ON DetailAbs.pracv = CisPracNS.pracv" & _
    " GROUP BY CisPracNS.utv, DetailAbs.kateg, DetailAbs.obd HAVING ((Sum(DetailAbs.prevp))<>0) AND ((DetailAbs.obd)= " & "'" & CStr(i) & "')"

    
    For j = 1 To 3
        
        If j = 1 Then Set Inp = dB.OpenRecordset("SELECT Null as obd, pracv, kateg, AllocSyst FROM 30_38SystUtvAllocRegularQ WHERE (pracv) Is Not Null", dbOpenDynaset)
        If j = 2 Then Set Inp = dB.OpenRecordset("SELECT Null as obd, pracv, kateg, AllocSyst FROM 30_39SystUtvAllocNotregulQ", dbOpenDynaset)
        If j = 3 Then Set Inp = dB.OpenRecordset("SELECT Null as obd, utvar, kateg, UtvSyst FROM 30_40SystNSallocAllQ", dbOpenDynaset)
    
        Do Until Inp.EOF
            
            Out.AddNew
            Out(0).Value = CStr(i)
            Out(1).Value = Inp(1).Value
            Out(2).Value = Inp(2).Value
            Out(3).Value = Inp(3).Value
            Out.Update
            
            Inp.MoveNext
        
        Loop
    
    Next j

Next i

Set dB = Nothing
Set InpSystPer = Nothing
Set Out = Nothing
Set OutQry = Nothing
Set Inp = Nothing

MsgBox ("Hotovo.")

End Sub