def insertOrUpdateRecord(jsondata,tblName):
for i in range(len(jsondata)):
Id = jsondata[i]['Id']
query ="delete from "+tblName+" where Id='"+str(Id)+"'"
c.execute(query)
if "BoxID" in jsondata[i].keys() and "ReferenceID" in jsondata[i].keys():
query ="delete from "+tblName+" where BoxID='"+str(jsondata[i]['BoxID'])+"'" + " and ReferenceID='"+str(jsondata[i]['ReferenceID'])+"'"
print('Record deleted for '+tblName+' :'+str(Id))
c.execute(query)
query ='insert into '+tblName+'('
cols = ''
vals = ''
try:
for key in jsondata[i].keys():
try:
key = key.encode('UTF-8')
except Exception,e:
key = key
value =jsondata[i][key]
try:
value = value.encode('UTF-8')
except Exception,e:
value = value
try:
value = value.replace('"','')
except Exception,e:
value = value
#Check if it is a file to download and download it.
if "IsActive" in jsondata[i].keys():
if jsondata[i]["IsActive"]==1:
FileMgr.CheckAndDownloadFile(key,value,0,Id)
else:
FileMgr.CheckAndDownloadFile(key,value,0,Id)
if 'Id' in key:
print('Record Added/Updated for '+tblName+' :'+str(value))
if 'AlbumDetails' in key:
insertOrUpdateRecord(value,'AlbumDetails')
else:
if 'BoxMappings' in key:
insertOrUpdateRecord(value,"ArticleBoxMapping")
else:
if value is not None:
cols = cols + key + ','
vals = vals +'"'+ str(value)+ '",'
def insertOrUpdateRecord(jsondata, tblName, ModuleId):
for i in range(len(jsondata)):
Id = jsondata[i]['Id']
query = "delete from " + tblName + " where Id='" + str(Id) + "'"
c.execute(query)
# print query
query = 'insert into ' + tblName + '('
cols = ''
vals = ''
try:
for key in jsondata[i].keys():
try:
key = key.encode('UTF-8')
except Exception, e:
key = key
value = jsondata[i][key]
try:
value = value.encode('UTF-8')
except Exception, e:
value = value
try:
value = value.replace('"', '')
except Exception, e:
value = value
if value is not None:
cols = cols + key + ','
vals = vals + '"' + str(value) + '",'
def insertOrUpdateRecord(jsondata, tblName):
for i in range(len(jsondata)):
Id = jsondata[i]["Id"]
query = "delete from " + tblName + " where Id='" + str(Id) + "'"
c.execute(query)
query = "insert into " + tblName + "("
cols = ""
vals = ""
try:
for key in jsondata[i].keys():
try:
key = key.encode("UTF-8")
except Exception, e:
key = key
value = jsondata[i][key]
try:
value = value.encode("UTF-8")
except Exception, e:
value = value
try:
value = value.replace('"', "")
except Exception, e:
value = value
if value is not None:
cols = cols + key + ","
vals = vals + '"' + str(value) + '",'
def insertOrUpdateRecord(jsondata,tblName):
for i in range(len(jsondata)):
Id = jsondata[i]['Id']
query ="delete from "+tblName+" where Id='"+str(Id)+"'"
c.execute(query)
query ='insert into '+tblName+'('
cols = ''
vals = ''
try:
for key in jsondata[i].keys():
try:
key = key.encode('UTF-8')
except Exception,e:
key = key
value =jsondata[i][key]
try:
value = value.encode('UTF-8')
except Exception,e:
value = value
try:
value = value.replace('"','')
except Exception,e:
value = value
if 'Id' in key:
print('Record Added/Updated for '+tblName+' :'+str(value))
if 'AlbumDetails' in key:
insertOrUpdateRecord(value,'AlbumDetails')
else:
if 'BoxMappings' in key:
insertOrUpdateRecord(value,"ArticleBoxMapping")
else:
if value is not None:
cols = cols + key + ','
vals = vals +'"'+ str(value)+ '",'
def insertOrUpdateRecord(jsondata,tblName):
for i in range(len(jsondata)):
Id = jsondata[i]['Id']
query ="delete from "+tblName+" where Id='"+str(Id)+"'"
c.execute(query)
if "BoxID" in jsondata[i].keys() and "ReferenceID" in jsondata[i].keys():
query ="delete from "+tblName+" where BoxID='"+str(jsondata[i]['BoxID'])+"'" + " and ReferenceID='"+str(jsondata[i]['ReferenceID'])+"'"
print('Record deleted for '+tblName+' :'+str(Id))
c.execute(query)
query ='insert into '+tblName+'('
cols = ''
vals = ''
try:
for key in jsondata[i].keys():
try:
key = key.encode('UTF-8')
except Exception,e:
key = key
value =jsondata[i][key]
try:
value = value.encode('UTF-8')
except Exception,e:
value = value
try:
value = value.replace('"','')
except Exception,e:
value = value
if 'Id' in key:
print('Record Added/Updated for '+tblName+' :'+str(value))
if value is not None:
cols = cols + key + ','
vals = vals +'"'+ str(value)+ '",'
if 'SurveyMapping' in key:
insertOrUpdateRecord(value,'SurveyBoxMapping',Id)
else:
if 'SurveyQuestions' in key and not 'SurveyQuestionsOptions' in key:
insertOrUpdateRecord(value,"SurveyQuestions",Id)
else:
if 'SurveyQuestionsOptions' in key:
insertOrUpdateRecord(value,"SurveyQuestionOptions",Id)
else:
if value is not None:
cols = cols + key + ','
vals = vals +'"'+ str(value)+ '",'
query += cols[:-1] +') values('+vals[:-1]+');'
c.execute(query)
except Exception,e:
print 'Insert Error for ['+ tblName +'] id=' + str(Id) +' details:'+ str(e)+'\nQuery:'+query
continue
imgpath = GetCMSData.GetData('Surveys')
insertOrUpdateRecord(imgpath,"Survey",None)
con = sqlite3.connect(ConfReader.GetSyncDBPath())
con.isolation_level = None
c = con.cursor()
c.execute("update Survey set ProvinceId = -1 where ProvinceId is Null;")
con.close()
if 'Id' in key:
print('Record Added/Updated for '+tblName+' :'+str(value))
if 'AlbumDetails' in key:
insertOrUpdateRecord(value,'AlbumDetails')
else:
if 'BoxMappings' in key:
insertOrUpdateRecord(value,"ArticleBoxMapping")
else:
if value is not None:
cols = cols + key + ','
vals = vals +'"'+ str(value)+ '",'
query += cols[:-1] +') values('+vals[:-1]+');'
c.execute(query)
#print(query)
except Exception,e:
print 'Insert Error for ['+ tblName +'] id=' + str(Id) +' details:'+ str(e)
continue
#db.commit()
#def LoadDB():
#imgpath = GetCMSData.GetData('Adverts')
#insertOrUpdateRecord(imgpath,"Language")
def insertOrUpdateRecord(jsondata, tblName):
for i in range(len(jsondata)):
Id = jsondata[i]["Id"]
query = "delete from " + tblName + " where Id='" + str(Id) + "'"
c.execute(query)
if "BoxID" in jsondata[i].keys() and "ReferenceID" in jsondata[i].keys():
query = (
"delete from "
+ tblName
+ " where BoxID='"
+ str(jsondata[i]["BoxID"])
+ "'"
+ " and ReferenceID='"
+ str(jsondata[i]["ReferenceID"])
+ "'"
)
print ("Record deleted for " + tblName + " :" + str(Id))
c.execute(query)
query = "insert into " + tblName + "("
cols = ""
vals = ""
try:
for key in jsondata[i].keys():
try:
key = key.encode("UTF-8")
except Exception, e:
key = key
value = jsondata[i][key]
try:
value = value.encode("UTF-8")
except Exception, e:
value = value
try:
value = value.replace('"', "")
except Exception, e:
value = value
# Check if it is a file to download and download it.
if "IsActive" in jsondata[i].keys():
if jsondata[i]["IsActive"] == 1:
FileMgr.CheckAndDownloadFile(key, value, 0, Id)
else:
FileMgr.CheckAndDownloadFile(key, value, 0, Id)
if "Id" in key:
print ("Record Added/Updated for " + tblName + " :" + str(value))
if "AlbumDetails" in key:
insertOrUpdateRecord(value, "AlbumDetails")
else:
if "BoxMappings" in key:
insertOrUpdateRecord(value, "ArticleBoxMapping")
else:
if value is not None:
cols = cols + key + ","
vals = vals + '"' + str(value) + '",'
def insertOrUpdateRecord(jsondata,tblName,ParentID):
if 'CompetitionBoxMapping' in tblName and ParentID is not None:
query = "delete from " + tblName + " where CompetitionID='" + str(ParentID) + "'"
print('Records deleted for ' + tblName + ' for CompetitionID :' + str(ParentID))
c.execute(query)
for i in range(len(jsondata)):
Id = jsondata[i]['Id']
query ="delete from "+tblName+" where Id='"+str(Id)+"'"
c.execute(query)
if "BoxID" in jsondata[i].keys() and "CompetitionID" in jsondata[i].keys():
query ="delete from "+tblName+" where BoxID='"+str(jsondata[i]['BoxID'])+"'" + " and CompetitionID='"+str(jsondata[i]['CompetitionID'])+"'"
print('Record deleted for '+tblName+' :'+str(Id))
c.execute(query)
query ='insert into '+tblName+'('
cols = ''
vals = ''
try:
for key in jsondata[i].keys():
try:
key = key.encode('UTF-8')
except Exception,e:
key = key
value =jsondata[i][key]
try:
value = value.encode('UTF-8')
except Exception,e:
value = value
try:
value = value.replace('"','')
except Exception,e:
value = value
#Check if it is a file to download and download it.
if "IsActive" in jsondata[i].keys():
if jsondata[i]["IsActive"]==1:
FileMgr.CheckAndDownloadFile(key,value,4,Id)
else:
FileMgr.CheckAndDownloadFile(key,value,4,Id)
if 'Id' in key:
print('Record Added/Updated for '+tblName+' :'+str(value))
if 'CompetitionMapping' in key:
insertOrUpdateRecord(value,'CompetitionBoxMapping',Id)
else:
if 'CompetitionQuestions' in key and not 'CompetitionQuestionsOptions' in key:
insertOrUpdateRecord(value,'CompetitionQuestions',Id)
else:
if 'CompetitionQuestionsOptions' in key:
insertOrUpdateRecord(value,"CompetitionQuestionOptions",Id)
else:
if 'CompetitionWinners' in key:
insertOrUpdateRecord(value,"CompetitionWinner",Id)
else:
if value is not None:
cols = cols + key + ','
vals = vals +'"'+ str(value)+ '",'
