Attribute VB_Name = "DataUpdate"
Option Compare Database
Public dB As Database
Public Inp, Out As Recordset
Public PerMin, PerMax, PerMinNext, InputTable, OutputTable As String

Public Sub UpdateOLAPstructures()

CurrentDb.Execute "DELETE * FROM OLAP_Stru_CENTRE"
CurrentDb.Execute "INSERT INTO OLAP_Stru_CENTRE SELECT Parent, Child, ChildText FROM 31_11StruCentreFinQ"

CurrentDb.Execute "DELETE * FROM OLAP_Stru_STAFFCAT"
CurrentDb.Execute "INSERT INTO OLAP_Stru_STAFFCAT SELECT Parent, Child, ChildText FROM 31_02StruStaffcatFinQ"

MsgBox ("Hotovo.")

End Sub

Public Sub UpdateOLAPimportFileAct()

PerMin = Left(DLookup("ValText", "Parameters", "[Parameter]= " & "'" & "ReportPeriod" & "'"), 4) & "01"
PerMax = Left(DLookup("ValText", "Parameters", "[Parameter]= " & "'" & "ReportPeriod" & "'"), 4) & "12"

CurrentDb.Execute "DELETE * FROM OLAP_ActData_STAFF WHERE Obd >= " & "'" & PerMin & "' AND Obd <= " & "'" & PerMax & "'"
CurrentDb.Execute "INSERT INTO OLAP_ActData_STAFF SELECT * FROM 30_30ImpOlapStaffActFluctQ WHERE Obd >= " & "'" & PerMin & "' AND Obd <= " & "'" & PerMax & "'"
CurrentDb.Execute "INSERT INTO OLAP_ActData_STAFF SELECT * FROM 30_30ImpOLAPsystMissKombQ WHERE Obd >= " & "'" & PerMin & "' AND Obd <= " & "'" & PerMax & "'"

MsgBox ("Hotovo.")

End Sub

Public Sub UpdateOLAPimportFileBgt()

PerMin = Left(DLookup("ValText", "Parameters", "[Parameter]= " & "'" & "ReportPeriod" & "'"), 4) & "01"
PerMax = Left(DLookup("ValText", "Parameters", "[Parameter]= " & "'" & "ReportPeriod" & "'"), 4) & "12"
PerMinNext = Val(Left(DLookup("ValText", "Parameters", "[Parameter]= " & "'" & "ReportPeriod" & "'"), 4)) + 1 & "01"

CurrentDb.Execute "DELETE * FROM OLAP_BgtData_STAFF WHERE Version = 'fcst_pers' AND Obd >= " & "'" & PerMin & "' AND Obd <= " & "'" & PerMax & "'"
CurrentDb.Execute "DELETE * FROM OLAP_BgtData_STAFF WHERE Version = 'bgt_pers' AND Obd >= " & "'" & PerMinNext & "'"

CurrentDb.Execute "DELETE * FROM OLAP_BgtData_FINAL WHERE Version = 'fcst_pers' AND Obd >= " & "'" & PerMin & "' AND Obd <= " & "'" & PerMax & "'"
CurrentDb.Execute "DELETE * FROM OLAP_BgtData_FINAL WHERE Version = 'bgt_pers' AND Obd >= " & "'" & PerMinNext & "'"

CurrentDb.Execute "INSERT INTO OLAP_BgtData_STAFF SELECT * FROM 30_11ImpOlapStaffBgtFinQ"

CurrentDb.Execute "INSERT INTO OLAP_BgtData_FINAL SELECT * FROM 30_16ImpOlapFinalBgtKalendQ"

MsgBox ("Hotovo.")

End Sub

Public Sub UpdateBgtMasterArchive()

CurrentDb.Execute "INSERT INTO BgtMasterArchive SELECT Loc, Type, Version, BgtYear, Prvek, CC, ICO, M1 as [01], M2 as [02], M3 as [03], M4 as [04], M5 as [05], M6 as [06], M7 as [07], " & _
"M8 as [08], M9 as [09], M10 as [10], M11 as [11], M12 as [12] FROM 30_19ImpBgtArchiveQ"

MsgBox ("Hotovo.")

End Sub

Public Sub SystemizacePeriodUpdate()

Set dB = CurrentDb()
Set Inp = dB.OpenRecordset("SystPeriodGroupQ", dbOpenDynaset)
Set Out = dB.OpenRecordset("SELECT Parameter, ValText FROM [Parameters] WHERE Parameter = 'ActSystPeriod'", dbOpenDynaset)
PerMax = DLookup("ValText", "Parameters", "[Parameter]= " & "'" & "ReportPeriod" & "'")

Do Until Inp.EOF
    
    If Inp(0).Value >= PerMax Then
        Out.Edit
        Out(1).Value = Inp(0).Value
        Out.Update
        GoTo SkipProc
    End If
    
    Inp.MoveNext
Loop

SkipProc:
Set dB = Nothing
Set Inp = Nothing
Set Out = Nothing

End Sub

Public Sub UpdateMySQL()
InputTable = "30_72MySQLLastPerRCpocetQ"
OutputTable = "pers_RC_pocty"

DataUpdate.DeleteMySQLtable
DataUpdate.CreateMySQLtable

End Sub

Private Sub DeleteMySQLtable()
        
SQL_dotaz = "DROP TABLE [ODBC;DSN=MySQLSefima;DATABASE=fnol_data;Uid=medic;PWD=medic;_ Trusted_Connection=Yes;]." & OutputTable

DoCmd.SetWarnings False
DoCmd.RunSQL SQL_dotaz
DoCmd.SetWarnings True
        
End Sub

Private Sub CreateMySQLtable()
                 
SQL_dotaz = "SELECT * INTO [ODBC;DSN=MySQLSefima;DATABASE=fnol_data;Uid=medic;PWD=medic;_ Trusted_Connection=Yes;]." & OutputTable & " FROM " & InputTable

DoCmd.SetWarnings False
DoCmd.RunSQL SQL_dotaz
DoCmd.SetWarnings True
                
End Sub