Forum Domoticz en français

Code : Tout sélectionner

Public Sub ftRetrieve()
    ' the file ftp_eco.bat must be in the same folder as the excel file, and contains this:
    '    ftp -s:"ftp_Eco.txt"
    '
    ' the file ftp_Eco.txt must be in the same folder as the excel file, and contains this:
    '    open 192.168.0.9
    '    admin
    '    Password
    '    get H1.BIN
    '    get H2.BIN
    '    get H3.BIN
    '    get H4.BIN
    '    get H5.BIN
    '    get H6.BIN
    '    get H7.BIN
    '    get H8.BIN
    '    get H9.BIN
    '    get H10.BIN
    '    get H11.BIN
    '    get H12.BIN
    '    bye
    '
    
strFile = "ftp_eco.bat"
strWndStyle = vbNormalFocus
   Shell "cmd /k cd D:\Documents\EcoCompteur && " & strFile, strWndStyle
   
End Sub

Code : Tout sélectionner

Sub CopyCsv()
Set shtProcessed = ThisWorkbook.Worksheets("ProcessedLog2")

'refresh the Query to the EcoCompteur
'Set shtRaw = ThisWorkbook.Worksheets("RawLog2")
'    Sheets("RawLog2").Select
'    Range("A1").Select
'    Selection.ListObject.TableObject.Refresh

NbRowProcessed = shtProcessed.UsedRange.Rows.Count
'------------------------------
' File structure :
' Day 1..31
'   Channel 0..11
'      Hour 0..23
'           data = energy in kWh (As floating point, on 32 bits, little endian, i.e. "Single" in VBA)
    
    Dim intFileNum, bytTemp As Byte, intCellRow
    Dim fltTemp As Single
    Dim fltOther(24) As Single
    Dim prix(24) As Single
    
    
    ' Call the .bat that reads the BIN files from EcoCompteur
    '--------------------------------------------------------

    Call ftRetrieve
    
    intFileNum = FreeFile
    intCellRow = 2
    
    ThisWorkbook.Worksheets("ProcessedLog2").Activate
 '   shtProcessed.Activate
    
    iCurrentYear = Year(Date)
    
    For iMonth = 1 To 12 'loop through all the months available
       
        iHour = 0
        iDay = 1
        iChannel = 0
        
        strFileName = Application.ActiveWorkbook.Path + "\H" & iMonth & ".BIN"
        Open strFileName For Binary Access Read As intFileNum Len = 4
        Do While Not (EOF(intFileNum))
            
            ' test the date : if it's after today, I assume it's from last year (the data files are probably fifo buffer)
            '-------------------------------
            mystr = iDay & "/" & iMonth & "/" & iCurrentYear
            If Not IsDate(mystr) Then
                Exit Do
            End If
            ddate = DateValue(mystr)
            If ddate > Date Then
                iYear = iCurrentYear - 1
                ddate = DateValue(iDay & "/" & iMonth & "/" & iYear)
            Else
                iYear = iCurrentYear
            End If
            
            
            ' Read One float from the file
            '-------------------------------
            Get intFileNum, , fltTemp
            fltTemp = fltTemp / 1000    ' use KWH rather than Wh
            
            
            ' Process the data
            '-------------------------------
            If iChannel = 0 Then                    ' this channel is used for the total
                                                    ' Channel 0 is the EDF channel (I don't use)
                                                    ' let's initialize the row when dealing with this channel
                myDateTime = CDate(iDay & "/" & iMonth & "/" & iYear & " " & iHour & ":01:00")
                Cells(intCellRow + iHour, 1) = ddate 'iDay
                Cells(intCellRow + iHour, 2) = iMonth
                Cells(intCellRow + iHour, 3) = iYear
                Cells(intCellRow + iHour, 4) = iHour
                Cells(intCellRow + iHour, 5) = myDateTime 'Minutes -- actually the whole date
                
                'fltOther(iHour) = fltTemp                  ' initialize the 'other' vaue with the total
                                                            ' I coomment it out because my total comes from chhannel 1 instead of EDF
            
                ' rechercher dans la feuille Tempo le jour de cette ligne ==> Couleur
                With Worksheets("Tempo").Range("a3:a1000")
                    Set c = .Find(ddate, LookIn:=xlValues)
                    If Not c Is Nothing Then
                        couleurJour = c.Offset(0, 1)
                        couleurVeille = c.Offset(-1, 1)
                        If iHour < 6 Then
                            combine = couleurVeille & "_HC"
                        Else
                            If iHour >= 22 Then
                                combine = couleurJour & "_HC"
                            Else
                                combine = couleurJour & "_HP"
                            End If
                        End If
                    Else
                        combine = "Non renseigné"
                        If fltTemp = 0 Then
                        Else
                            MsgBox ("la couleur du jour " & ddate & " n'est pas renseignée dans la feuille Tempo. Mise à jour arrêtée")
                            Exit Sub
                        End If
                    End If
                End With
                ' rechercher dans la feuille Tempo la combinaison Couleur et HC/HP de cette ligne ==> Prix
                With Worksheets("Tempo").Range("g3:g10")
                    Set c = .Find(combine, LookIn:=xlValues)
                    If Not c Is Nothing Then
                        prix(iHour) = c.Offset(0, 1)
                    End If
                End With
                    
                'mettre dans la colonne T le tarif
                Cells(intCellRow + iHour, 20) = combine
            
            Else
                If iChannel = 11 Then                   ' if the last channel is reached
                    Cells(intCellRow + iHour, 6) = fltOther(iHour)
                    Cells(intCellRow + iHour, 7) = fltOther(iHour) * prix(iHour)
                Else
                    If iChannel < 6 Then                ' ignore the channels 6 and above (I don't use)
                        If iChannel = 1 Then
                            fltOther(iHour) = fltTemp   ' initialize the 'other' value with the total
                        Else
                            fltOther(iHour) = fltOther(iHour) - fltTemp       'substract the channel's energy from the remianing (other)
                        End If
                        Cells(intCellRow + iHour, 6 + 2 * iChannel) = fltTemp                   'Energy of the channel
                        Cells(intCellRow + iHour, 6 + 2 * iChannel + 1) = fltTemp * prix(iHour) 'price of the channel
                    End If
                End If
                        
            End If
            
                    
            '------------------------------------------------
            
            iHour = iHour + 1           ' next hour for same channel
            If iHour > 23 Then          ' next channel for same day
                iHour = 0
                iChannel = iChannel + 1
                If iChannel > 11 Then   ' next day
                    iChannel = 0
                    iDay = iDay + 1
                    intCellRow = intCellRow + 24
                End If
            End If
        Loop
        Close intFileNum
    
    Next iMonth 'end of loop through all months
    
    ' update all pivot tables
    '-------------------------------------------------
    Sheets("TCD_prix").PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
    Sheets("TCD_KWh").PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    Sheets("TCD_PrixTarif").PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    Sheets("Graphiques").Select

End Sub

Code : Tout sélectionner

-- Ce script permet de récupérer les postes de dépenses EcoCompteur
 
----------------------------------------------
------          Configuration          ------
----------------------------------------------

local url = 'http://192.168.XXX.XXX/inst.json'
local C1 = 'Chauffe eau'
local C2 = 'Chauffage phase 1'
local C3 = 'Chauffage phase 2'
local C4 = 'Chauffage phase 3'
local C5 = 'Lave linge'
local RAZ = 'RAZ'

----------------------------------------------
--       Fin de la partie configuration       --
----------------------------------------------

commandArray={}
json = (loadfile "/home/pi/domoticz/scripts/lua/JSON.lua")()

local config=assert(io.popen("curl '"..url.."'"))
local location = config:read('*all')
config:close()
local jsonLocation = json:decode(location)

local data1=jsonLocation.data1      -- Chauffe eau
local data2=jsonLocation.data2      -- Chauffage phase 1
local data3=jsonLocation.data3      -- Chauffage phase 2
local data4=jsonLocation.data4      -- Chauffage phase 3
local data5=jsonLocation.data5      -- Lave linge
local heure=jsonLocation.heure      -- Heure de la mesure instantanée
local minute=jsonLocation.minute    -- Minute de la mesure instantanée

p1, totalP1 = string.match(otherdevices_svalues[C1], "(%d+%.*%d*);(%d+%.*%d*)")
p1 = tonumber(data1)
    if (otherdevices[RAZ] == 'On') then
    totalP1 = 0
    else
    totalP1 = tonumber(totalP1 + (p1 / 60))
    end 
commandArray[1]={ ['UpdateDevice']= 1266 .. "|0|" .. p1 .. ';' .. totalP1 }
 
p2, totalP2 = string.match(otherdevices_svalues[C2], "(%d+%.*%d*);(%d+%.*%d*)")
p2 = tonumber(data2)
    if (otherdevices[RAZ] == 'On') then
    totalP2 = 0
    else
    totalP2 = tonumber(totalP2 + (p2 / 60))
    end 
commandArray[2]={ ['UpdateDevice']= 1267 .. "|0|" .. p2 .. ';' .. totalP2 }

p3, totalP3 = string.match(otherdevices_svalues[C3], "(%d+%.*%d*);(%d+%.*%d*)")
p3 = tonumber(data3)
    if (otherdevices[RAZ] == 'On') then
    totalP3 = 0
    else
    totalP3 = tonumber(totalP3 + (p3 / 60))
    end 
commandArray[3]={ ['UpdateDevice']= 1268 .. "|0|" .. p3 .. ';' .. totalP3 }

p4, totalP4 = string.match(otherdevices_svalues[C4], "(%d+%.*%d*);(%d+%.*%d*)")
p4 = tonumber(data4)
    if (otherdevices[RAZ] == 'On') then
    totalP4 = 0
    else
    totalP4 = tonumber(totalP4 + (p4 / 60))
    end 
commandArray[4]={ ['UpdateDevice']= 1269 .. "|0|" .. p4 .. ';' .. totalP4 }

p5, totalP5 = string.match(otherdevices_svalues[C5], "(%d+%.*%d*);(%d+%.*%d*)")
p5 = tonumber(data5)
    if (otherdevices[RAZ] == 'On') then
    totalP5 = 0
    else
    totalP5 = tonumber(totalP5 + (p5 / 60))
    end 
commandArray[5]={ ['UpdateDevice']= 1270 .. "|0|" .. p5 .. ';' .. totalP5 }

print('**************** Ecocompteur ************')
print(data1)
print(data2)
print(data3)
print(data4)
print(data5)
print(heure)
print(minute)
print('****************')

return commandArray