{"id":970,"date":"2020-02-09T13:16:00","date_gmt":"2020-02-09T12:16:00","guid":{"rendered":"http:\/\/wollef.org\/?p=970"},"modified":"2020-02-09T13:16:00","modified_gmt":"2020-02-09T12:16:00","slug":"le-logiciel-raspberry-car-tache-denvoi-des-donnees-a-salesforce-4-4","status":"publish","type":"post","link":"https:\/\/wollef.org\/blog\/le-logiciel-raspberry-car-tache-denvoi-des-donnees-a-salesforce-4-4\/","title":{"rendered":"Le Logiciel Raspberry Car \u2013 T\u00e2che d\u2019envoi des donn\u00e9es \u00e0 Salesforce (4\/5)"},"content":{"rendered":"\n<p>En utilisant les techniques de la partie pr\u00e9c\u00e9dente, le code de la tache d&#8217;envoi des donn\u00e9es \u00e0 Salesforce effectuant le travail suivant\u00a0:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Essaye de se connecter \u00e0 Salesforce<\/li><li>Si cela r\u00e9ussit&nbsp;:&nbsp;<ul><li>Poser le verrou DB&nbsp;: bloque l\u2019acc\u00e8s \u00e0 la base SQ<ul><li>Action s\u00e9curis\u00e9e&nbsp;: Il lit tout ce qui est\ndans la base SQL pour \u00eatre envoy\u00e9<\/li><\/ul><\/li><\/ul><ul><li>D\u00e9bloquer le verrou DB <em>(cela a dur\u00e9 presque 1 seconde,\ncar on \u00e9crit sur le disque)<\/em><\/li><\/ul><ul><li>S\u2019il y a des nouvelles donn\u00e9es \u00e0 envoyer \u00e0 Salesforce<ul><li>Envoyer les informations \u00e0 Salesforce. <em>Cela\npeut durer plusieurs secondes&nbsp;: temps de r\u00e9action de Salesforce, la\nvitesse du r\u00e9seau, etc.<\/em><\/li><\/ul><ul><li>Poser le verrou\nDB&nbsp;: bloque l\u2019acc\u00e8s \u00e0 la base SQ<ul><li>Action s\u00e9curis\u00e9e&nbsp;: Il marque ou supprime\nles donn\u00e9es d\u00e9j\u00e0 envoy\u00e9es<\/li><\/ul><\/li><\/ul><ul><li>D\u00e9bloquer le\nverrou DB (cela a dur\u00e9 presque 1 seconde, car on \u00e9crit sur le disque)<\/li><\/ul><\/li><\/ul><\/li><li>Il attend les nouvelles donn\u00e9es (pause)\nproduites par autres programmes.<\/li><\/ul>\n\n\n\n<p>Ressemble \u00e0 <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>def send_data_to_sf(delay):\n\n    # if mode shared DB : cree la connexion une fois\n    if MODE_SHARED_DB_CONNECTION == True:\n        permanentDBConnectionA = sqlite3.connect(CAR_DB, isolation_level=None);\n\n    while True:\n        # on demande l'acces exclusif \u00e0 la DB\n        threadDbLock.acquire()\n        print(C_SF+\"SF : Got DB lock\", ENDC);\n\n        try:\n            # if mode shared DB : cr\u00e9e connexion \u00e0 chaque fois\n            if MODE_SHARED_DB_CONNECTION == True:\n                conn = permanentDBConnectionA\n            else :\n                print(C_SF+\"SF : Connect to DB\", ENDC);\n                conn = sqlite3.connect(CAR_DB, isolation_level=None);\n\n            print(C_SF+\"SF : Look for new Status\", ENDC);\n\n            # on demande un curseur sur la DB\n            cursor = conn.cursor()\n            # on lit les lignes qu'on n'a pas encore envoy\u00e9e (  where sent != 1)\n            cursor.execute(\"SELECT rowid, statusdate, gps_longitude , gps_latitude, gps_speed, gps_elevation, obd_speed, obd_fuellevel, obd_rpm,  gps_measure_time, obd_measure_time, gps_time FROM car_status where sent != 1 order by statusdate asc\")\n            rows = cursor.fetchall()\n            # on referme le curseur\n            cursor.close()\n\n            # on regarde combien de mesure on a eu\n            count = 0;\n            for row in rows:\n                count = count + 1\n\n            # si il y a des lignes, on va les mettre dans SF\n            if count>0 :\n                print(C_SF+\"SF : Status to transfer to SF :\" , count, ENDC);\n\n                print(C_SF+'SF : Connect to SF', ENDC)\n\n                #on se connecte \u00e0 SF\n                sf = Salesforce(\n\n                );\n\n                # on cree :\n                # - un tableau new_positions avec toutes les positions au format SF\n                # - un tableau rowsToMark avec les lignes \u00e0 marquer comme lues\n                print(C_SF+\"SF : Prepare new position objects\", ENDC);\n                new_positions = []\n                last_positions = []\n                rowsToMark = []\n                for row in rows:\n                    # conversion de format pour les dates\n                    datestring = datetime.utcfromtimestamp(row[1]).strftime('%Y-%m-%dT%H:%M:%SZ')\n                    gmtime = datetime.utcfromtimestamp(row[9]).strftime('%Y-%m-%dT%H:%M:%SZ')\n                    omtime = datetime.utcfromtimestamp(row[10]).strftime('%Y-%m-%dT%H:%M:%SZ')\n                    gtime = datetime.utcfromtimestamp(row[11]).strftime('%Y-%m-%dT%H:%M:%SZ')\n\n                    new_positions.append({\n                        'Tracker_ID__c': RASPBERRY_NUMBER,\n                        'Requested_On__c': datestring,\n                        'GPS_Longitude__c' : row[2],\n                        'GPS_Latitude__c' : row[3],\n                        'GPS_Speed__c' : row[4],\n                        'GPS_Elevation__c' : row[5],\n                        'OBD_Speed__c' : row[6],\n                        'OBD_Fuel_Level__c' : row[7],\n                        'OBD_RPM__c' : row[8],\n                        'GPS_Measure_Time__c' : gmtime,\n                        'OBD_Measure_Time__c' : omtime,\n                        'GPS_Time__c' : gtime,\n\n                    })\n\n                    rowsToMark.append((1,row[0]));\n\n                # on demande \u00e0 SF d'inserer toutes les nouvelles lignes\n                print(C_SF+'SF : Send Big Object Positions', ENDC)\n                print(C_SF+'SF : All Positions are : ',new_positions, ENDC)\n                createdPositions1 = sf.bulk.Car_Monitoring_Data__b.insert(new_positions)\n\n                # on dit \u00e0 la DB que toutes ces lignes ont \u00e9te utilis\u00e9es\n                print(C_SF+'SF : Update database records', ENDC)\n                print(C_SF+'SF : Records to update are : ',rowsToMark, ENDC)\n                cursor = conn.cursor()\n                cursor.executemany('UPDATE car_status SET sent=? WHERE rowid=?', (rowsToMark) )\n                cursor.close()\n\n                # on demande \u00e0 la DB de valider la sauvearde des lignes\n                print(C_SF+'SF : Commit changes in DB', ENDC)\n                conn.commit\n\n                print(C_SF+'SF : Transfer to SF done', ENDC)\n            else :\n                print(C_SF+'SF : Nothing to transfer to SF', ENDC)\n\n            # close DB connection si one est en mode de connection non permanente\n            if MODE_SHARED_DB_CONNECTION == False:\n                conn.close()\n                print(\"SF : DB Connection closed\");\n\n        except Exception as e:\n            print(C_ERROR+\"SF : Error: unable to send data to SF\", ENDC)\n            print(C_ERROR+str(e), ENDC)\n\n        # on libere l'acc\u00e8s \u00e0 la DB pour qu'elle puisse etre mise \u00e0 jour par les autres taches\n        threadDbLock.release()\n        print(C_SF+\"SF : DB lock released\", ENDC);\n\n        # on attend le delai demand\u00e9\n        print(C_SF+'SF : Wait', ENDC)\n        time.sleep(delay)\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>En utilisant les techniques de la partie pr\u00e9c\u00e9dente, le code de la tache d&#8217;envoi des donn\u00e9es \u00e0 Salesforce effectuant le travail suivant\u00a0: Essaye de se connecter \u00e0 Salesforce Si cela r\u00e9ussit&nbsp;:&nbsp; Poser le verrou DB&nbsp;: bloque l\u2019acc\u00e8s \u00e0 la base SQ Action s\u00e9curis\u00e9e&nbsp;: Il lit tout ce qui est dans la base SQL pour \u00eatre <a class=\"read-more\" href=\"https:\/\/wollef.org\/blog\/le-logiciel-raspberry-car-tache-denvoi-des-donnees-a-salesforce-4-4\/\">Continue Reading<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,1],"tags":[53],"class_list":["post-970","post","type-post","status-publish","format-standard","hentry","category-raspberry-cars","category-non-classe","tag-2020-raspberry-cars"],"_links":{"self":[{"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/posts\/970","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/comments?post=970"}],"version-history":[{"count":0,"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/posts\/970\/revisions"}],"wp:attachment":[{"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/media?parent=970"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/categories?post=970"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wollef.org\/blog\/wp-json\/wp\/v2\/tags?post=970"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}