def post(self):
logger.info("Adding a new feature")
content = request.get_json ()
# print (content)
username = content['user']
campaignname = content['campaign'][0]
featurename = content['newFeature']
# language = content['language']
conn = mysql.connect ()
cur = conn.cursor ()
# Inserting new feature in feature table
cur.execute ("INSERT INTO feature(feature_name,feature_createdate) VALUES(%s,CURDATE())",
(featurename))
conn.commit ()
# Updateing campaign_feature_xref table
# selecting campaign id
cur.execute (
"SELECT c.campaign_id from user u join user_campaign_xref uc on u.user_id=uc.user_user_id join campaign c on uc.campaign_campaign_id=c.campaign_id where u.email='" + username + "' AND c.campaign_name='" + campaignname + "' ")
campaignid = cur.fetchall ()
# selecting feature id for last added feature
cur.execute ("SELECT feature_id FROM feature ORDER BY feature_id DESC LIMIT 1")
featureid = cur.fetchall ()
cur.execute ("INSERT INTO campaign_feature_xref(campaign_campaign_id,feature_feature_id) VALUES (%s,%s)",
(campaignid[0], featureid[0]))
conn.commit ()
logger.info("Feature %s is added",featurename)
def query_persona_and_rule(username, campaignname=None, campaignid=None):
cur = mysql.connect().cursor()
if campaignname == None and campaignid == None:
logger.error(
"Neither campaignname nor campaignid provided to query_persona_rule_all"
)
return ""
elif campaignid == None:
cur.execute(
"SELECT p.persona_rule, l.language, l.rule FROM user u JOIN user_campaign_xref us ON u.user_id=us.user_user_id JOIN campaign s ON us.campaign_campaign_id=s.campaign_id JOIN campaign_persona_xref cp ON s.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id JOIN persona_feature_xref pf ON p.persona_id=pf.persona_persona_id JOIN feature g ON pf.feature_feature_id=g.feature_id JOIN feature_language_xref fl ON g.feature_id=fl.feature_feature_id JOIN language l ON fl.language_language_id=l.language_id WHERE u.email='"
+ username + "' AND s.campaign_name='" + campaignname + "'")
else:
cur.execute(
"SELECT p.persona_rule, l.language, l.rule FROM user u JOIN user_campaign_xref us ON u.user_id=us.user_user_id JOIN campaign s ON us.campaign_campaign_id=s.campaign_id JOIN campaign_persona_xref cp ON s.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id JOIN persona_feature_xref pf ON p.persona_id=pf.persona_persona_id JOIN feature g ON pf.feature_feature_id=g.feature_id JOIN feature_language_xref fl ON g.feature_id=fl.feature_feature_id JOIN language l ON fl.language_language_id=l.language_id WHERE u.email='"
+ username + "' AND s.campaign_id='" + campaignid + "'")
rv = cur.fetchall()
rv = list(rv)
for index, item in enumerate(rv):
rv[index] = list(item)
for index1, item in enumerate(rv):
# feature_reference accepts tuple of tuples as the default argument.
content_list = [[]]
content_list[0].append(item[2])
#print(content_list[0][0])
rv[index1][2] = feature_reference(content_list, username, campaignname,
item[1], campaignid)[0][0]
#print("rv is:", rv)
return rv
def delete(self):
try:
logger.info("start Deleting table process")
content = request.get_json()
username = content['user']
data = content['table'][0]
campaignname = data['campaign']
table = data['table']
status = data['status']
logger.info("Deleting table info for table %s", table)
conn = mysql.connect()
cur = conn.cursor()
# Deleting Table info from exportinfo table
cur.execute("DELETE FROM exportinfo WHERE user='******' and table_name='" + table + "' and campaign = '" +
campaignname + "' and status = '" + status + "'")
conn.commit()
# Deleting Table if delete entry status is not as duplicate entry
if status != 'Failed(Table already exists)':
st = "DROP TABLE " + table
cur.execute(st)
conn.commit()
logger.info("Deleted table info for %s", table)
except Exception as e:
logger.error(e.__str__())
def post(self):
logger.info("Collecting data for given state")
content = request.get_json()
print("this is content \n", content)
campaign = content['campaign'][0]
feature = content['exportFeature'][0]
logger.info(
"Getting saved version data for campaign %s and feature %s ",
campaign, feature)
conn = mysql.connect()
cur = conn.cursor()
cur.execute(
"SELECT user,language,CAST(create_date as char),comment FROM versioninfo WHERE campaign = '"
+ campaign + "' AND feature = '" + feature +
"' ORDER BY create_date DESC ")
rv = cur.fetchall()
data = []
for a in rv:
data.append({
"user": a[0],
"language": a[1],
"time": a[2],
"comment": a[3]
})
return data
def post(self):
logger.info("Remove mapping process is started")
content = request.get_json ()
username = content['user']
campaignname = content['campaign'][0]
persona = content['removePersona']
feature = content['removeFeature']
logger.info("Removing mapping for Persona '%s' and Feature '%s'",persona,feature)
conn = mysql.connect ()
cur = conn.cursor ()
cur.execute(
"SELECT pf.persona_persona_id, pf.feature_feature_id FROM user u JOIN user_campaign_xref uc ON u.user_id=uc.user_user_id JOIN campaign c ON uc.campaign_campaign_id=c.campaign_id JOIN campaign_persona_xref cp ON c.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id JOIN persona_feature_xref pf ON p.persona_id=pf.persona_persona_id JOIN feature f ON pf.feature_feature_id=f.feature_id WHERE u.email='" + username + "' and c.campaign_name='"+campaignname+"' and p.persona_name='"+persona+"' and f.feature_name='"+feature+"'")
ids=cur.fetchall()
# print("ids",ids)
personaid=ids[0][0]
# print("personaid",personaid)
featureid=ids[0][1]
# print("featureid",featureid)
delquery="DELETE FROM `persona_feature_xref` where persona_persona_id = " + str(personaid) + " and feature_feature_id = " + str(featureid)
cur.execute(delquery)
# print(cur.rowcount)
conn.commit ()
logger.info("Mapping is removed")
def delete(self):
content = request.get_json()
state_user = content['stateUser']
campaign = content['campaign'][0]
feature = content['feature'][0]
language = content['language']
time = content['time']
logger.info(
"Deleting saved data for feature %s ,language %s, and time %s",
feature, language, time)
conn = mysql.connect()
cur = conn.cursor()
cur.execute("DELETE FROM versioninfo WHERE user = '******' AND campaign = '" + campaign + "' AND feature = '" +
feature + "' AND language = '" + language +
"' AND create_date = '" + time + "' ")
conn.commit()
logger.info("Data deleted successfuly")
message = {"message": "true"}
return message
def updateUser(username,updateusername,campaignname,new_role):
conn = mysql.connect ()
cur = conn.cursor ()
cur.execute(
"SELECT c.role from user u join user_campaign_xref uc on u.user_id=uc.user_user_id join campaign c on uc.campaign_campaign_id=c.campaign_id where c.campaign_name ='"+campaignname+"' and u.email = '"+updateusername+"'")
rv = cur.fetchall()
old_role = rv[0][0]
print("here is old_role",old_role)
# Checking if new role and old role is same
if old_role != new_role:
# Getting user id and campaign id for user with old role
cur.execute(
"SELECT u.user_id, c.campaign_id FROM user u JOIN user_campaign_xref uc ON u.user_id=uc.user_user_id JOIN campaign c ON uc.campaign_campaign_id=c.campaign_id WHERE u.email = '"+updateusername+"' AND c.campaign_name = '"+campaignname+"' AND c.role = '"+old_role+"'")
ids = cur.fetchall ()
uid = ids[0][0]
cid = ids[0][1]
# Deleting old access for user
cur.execute (
"DELETE FROM user_campaign_xref WHERE user_user_id = %s AND campaign_campaign_id = %s ",(uid,cid))
conn.commit ()
if old_role == 'Reviewer':
# Deleting review role from campaign table
cur.execute (
"DELETE FROM campaign WHERE campaign_id = %s ",cid)
conn.commit ()
# Getting campaign id to give editor access
cur.execute(
"SELECT c.campaign_id from user u join user_campaign_xref uc on u.user_id=uc.user_user_id join campaign c on uc.campaign_campaign_id=c.campaign_id where c.campaign_name ='"+campaignname+"' and u.email = '"+username+"'")
access_cid = cur.fetchall ()
cur.execute (
"INSERT INTO `user_campaign_xref` VALUES(%s,%s)",(uid,access_cid))
conn.commit ()
elif old_role == 'Editor':
cur.execute (
"INSERT INTO campaign(campaign_name,role) VALUES(%s,%s) ",(campaignname,new_role))
conn.commit ()
cur.execute (
"SELECT campaign_id FROM campaign ORDER BY campaign_id DESC LIMIT 1")
cid = cur.fetchall ()
cur.execute (
"INSERT INTO `user_campaign_xref` VALUES (%s,%s) ",(uid,cid))
conn.commit ()
message = {"message":"User role is successfully updated "}
return message
def query_rule_all(username, campaignname, language):
cur = mysql.connect().cursor()
cur.execute(
"SELECT l.rule from user u join user_campaign_xref us on u.user_id=us.user_user_id join campaign s on us.campaign_campaign_id=s.campaign_id join campaign_feature_xref cf on s.campaign_id=cf.campaign_campaign_id join feature g on cf.feature_feature_id=g.feature_id join feature_language_xref fl on g.feature_id=fl.feature_feature_id join language l on fl.language_language_id=l.language_id where u.email='"
+ username + "' AND s.campaign_name='" + campaignname +
"' AND l.language='" + language + "'")
rv = cur.fetchall()
return rv
def query_persona_name(username, campaignname, personarule):
cur = mysql.connect().cursor()
cur.execute(
"SELECT p.persona_name FROM user u JOIN user_campaign_xref us ON u.user_id=us.user_user_id JOIN campaign s ON us.campaign_campaign_id=s.campaign_id JOIN campaign_persona_xref cp ON s.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id WHERE u.email='"
+ username + "' AND s.campaign_name='" + campaignname +
"' AND p.persona_rule='" + personarule + "'")
rv = cur.fetchall()
return rv
def process(self, df, row, NarrGen, filename, campaignname):
t1 = time.time()
rowData = df.iloc[row].to_dict()
rowNarr = NarrGen.GenNarrTag(data=rowData, CampaignName=campaignname)
conn = mysql.connect()
try:
getSQLInsert(dict=rowNarr, table=filename, conn=conn)
except Exception as e:
logger.error(e.__str__())
return True
def query_rule_p_f_no_fr(username, campaignname, personaname, featurename,
language):
cur = mysql.connect().cursor()
cur.execute(
"SELECT l.rule FROM user u JOIN user_campaign_xref uc ON u.user_id=uc.user_user_id JOIN campaign c ON uc.campaign_campaign_id=c.campaign_id JOIN campaign_persona_xref cp ON c.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id JOIN persona_feature_xref pf ON p.persona_id=pf.persona_persona_id JOIN feature f ON pf.feature_feature_id=f.feature_id JOIN feature_language_xref fl ON f.feature_id=fl.feature_feature_id JOIN language l ON fl.language_language_id=l.language_id WHERE u.email='"
+ username + "' AND c.campaign_name='" + campaignname +
"' AND p.persona_name='" + personaname + "' AND f.feature_name='" +
featurename + "' AND l.language='" + language + "'")
rv = cur.fetchall()
return rv
def query_rule_p(username, campaignname, personaname, language):
cur = mysql.connect().cursor()
cur.execute(
"SELECT l.rule FROM user u JOIN user_campaign_xref us ON u.user_id=us.user_user_id JOIN campaign s ON us.campaign_campaign_id=s.campaign_id JOIN campaign_persona_xref cp ON s.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id JOIN persona_feature_xref pf ON p.persona_id=pf.persona_persona_id JOIN feature g ON pf.feature_feature_id=g.feature_id JOIN feature_language_xref fl ON g.feature_id=fl.feature_feature_id JOIN language l ON fl.language_language_id=l.language_id WHERE u.email='"
+ username + "' AND s.campaign_name='" + campaignname +
"' AND p.persona_name='" + personaname + "' AND l.language='" +
language + "'")
rv = cur.fetchall()
rv = feature_reference(rv, username, campaignname, language)
return rv
def delete(self):
logger.info("Deleting feature process started")
content = request.get_json ()
username = content['user']
campaignname = content['campaign'][0]
featurename = content['deleteFeature'][0]
# print("featurename",featurename)
conn = mysql.connect ()
cur = conn.cursor ()
# Getting feature id
cur.execute (
"SELECT f.feature_id from user u join user_campaign_xref uc on u.user_id=uc.user_user_id join campaign c on uc.campaign_campaign_id=c.campaign_id join campaign_feature_xref cf on c.campaign_id=cf.campaign_campaign_id join feature f on cf.feature_feature_id=f.feature_id where u.email='" + username + "' AND c.campaign_name='" + campaignname + "' AND f.feature_name='" + featurename + "'")
rv = cur.fetchall ()
featureids = str (sum (rv, ()))
featureids_1 = featureids.replace ("L,)", ")").replace ("L)", ")")
featureids = featureids_1.replace (",)", ")")
# print("a2",featureids)
# Chekcing if there is any language and rule associated with that feature
cur.execute (
"SELECT count(*) FROM feature_language_xref WHERE feature_feature_id IN " + str (featureids))
ret = cur.fetchall ()
ret = ret[0][0]
# print(ret)
# Deleting campaign and persona mapping with feature
cur.execute ("DELETE FROM campaign_feature_xref WHERE feature_feature_id IN " + str (featureids))
conn.commit ()
cur.execute ("DELETE FROM persona_feature_xref WHERE feature_feature_id IN " + str (featureids))
conn.commit ()
# deleting feature
cur.execute ("DELETE FROM feature WHERE feature_id IN " + str (featureids))
conn.commit ()
# if there are language and rule associated with feature deleting them
if ret != 0 :
cur.execute (
"SELECT language_language_id FROM feature_language_xref WHERE feature_feature_id IN " + str (featureids))
rv = cur.fetchall ()
# print("comiing language",rv)
languageids = str (sum (rv, ()))
# print("bl",languageids)
languageids = languageids.replace (",)", ")")
# print("a",languageids)
cur.execute ("DELETE FROM feature_language_xref WHERE feature_feature_id IN " + str (featureids))
conn.commit ()
cur.execute ("DELETE FROM language WHERE language_id IN " + str (languageids))
conn.commit ()
logger.info("Feature %s is deleted",featurename)
def post(self):
logger.info("Getting mapping data")
content = request.get_json ()
username = content['user']
campaignname = content['campaign'][0]
cur = mysql.connect ().cursor ()
# Getting all features mapped with a persona
cur.execute(
"SELECT p.persona_name FROM user u JOIN user_campaign_xref uc ON u.user_id=uc.user_user_id JOIN campaign c ON uc.campaign_campaign_id=c.campaign_id JOIN campaign_persona_xref cp ON c.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id JOIN persona_feature_xref pf ON p.persona_id=pf.persona_persona_id WHERE u.email='" + username + "' AND c.campaign_name='" + campaignname + "'")
rv=cur.fetchall()
pfm={}
for a in rv:
personaname=a[0]
cur.execute(
"SELECT f.feature_name FROM user u JOIN user_campaign_xref uc ON u.user_id=uc.user_user_id JOIN campaign c ON uc.campaign_campaign_id=c.campaign_id JOIN campaign_persona_xref cp ON c.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id JOIN persona_feature_xref pf ON p.persona_id=pf.persona_persona_id JOIN feature f ON pf.feature_feature_id=f.feature_id WHERE u.email='" + username + "' AND c.campaign_name='" + campaignname + "' AND p.persona_name='" + personaname + "' ")
fname = cur.fetchall ()
features = []
for b in fname:
b = b[0]
features.append (b)
pfm[personaname] = features
# Getting all personas mapped to a feature
cur.execute(
"SELECT f.feature_name FROM user u JOIN user_campaign_xref uc ON u.user_id=uc.user_user_id JOIN campaign c ON uc.campaign_campaign_id=c.campaign_id JOIN campaign_persona_xref cp ON c.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id JOIN persona_feature_xref pf ON p.persona_id=pf.persona_persona_id JOIN feature f ON pf.feature_feature_id=f.feature_id WHERE u.email='" + username + "' and c.campaign_name='"+campaignname+"' ")
featurename=cur.fetchall()
fpm={}
for b in featurename:
f_name=b[0]
cur.execute(
"SELECT p.persona_name FROM user u JOIN user_campaign_xref uc ON u.user_id=uc.user_user_id JOIN campaign c ON uc.campaign_campaign_id=c.campaign_id JOIN campaign_persona_xref cp ON c.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id JOIN persona_feature_xref pf ON p.persona_id=pf.persona_persona_id JOIN feature f ON pf.feature_feature_id=f.feature_id WHERE u.email='" + username + "' and c.campaign_name='"+campaignname+"' AND f.feature_name='"+f_name+"' ")
pname = cur.fetchall()
personas=[]
for c in pname:
c=c[0]
personas.append(c)
fpm[f_name]=personas
data={"personafeature":pfm,"featurepersona":fpm}
logger.info("Mapping data is collected")
return data
def query_rule(username, campaignname, features, language):
cur = mysql.connect().cursor()
feature_str = "'" + "','".join(ite for item in features
for ite in item) + "'"
cur.execute(
"SELECT l.rule from user u join user_campaign_xref us on u.user_id=us.user_user_id join campaign s on us.campaign_campaign_id=s.campaign_id join campaign_feature_xref cf on s.campaign_id=cf.campaign_campaign_id join feature g on cf.feature_feature_id=g.feature_id join feature_language_xref fl on g.feature_id=fl.feature_feature_id join language l on fl.language_language_id=l.language_id where u.email='"
+ username + "' AND s.campaign_name='" + campaignname +
"' AND g.feature_name IN (" + feature_str + ") AND l.language='" +
language + "'")
rv = cur.fetchall()
rv = feature_reference(rv, username, campaignname, language)
return rv
def post(self):
try:
logger.info("Started saving data for given state")
content = request.get_json()
username = content['user']
state_user = content['stateUser']
campaign = content['campaign'][0]
personaname = content['previewPersona'][0]
featurename = content['previewFeature'][0]
language = content['language']
time = content['time']
conn = mysql.connect()
cur = conn.cursor()
cur.execute(
"SELECT l.language_id from user u join user_campaign_xref us on u.user_id=us.user_user_id join campaign s on us.campaign_campaign_id=s.campaign_id join campaign_feature_xref cf on s.campaign_id=cf.campaign_campaign_id join feature g on cf.feature_feature_id=g.feature_id join feature_language_xref fl on g.feature_id=fl.feature_feature_id join language l on fl.language_language_id=l.language_id where u.email='"
+ username + "' AND s.campaign_name='" + campaign +
"' AND g.feature_name = '" + featurename +
"' AND l.language='" + language + "'")
lid = cur.fetchall()
print("current language_id", lid)
cur.execute("SELECT rule FROM versioninfo WHERE user = '******' AND campaign = '" + campaign +
"' AND feature = '" + featurename +
"' AND language = '" + language +
"' AND create_date = '" + time + "' ")
rule = cur.fetchall()
cur.execute(
"UPDATE language SET rule = %s WHERE language_id = %s",
(rule, lid))
conn.commit()
logger.info(
"State is successfuly revert back for feature %s ,language %s, and time %s",
featurename, language, time)
data = feature_narrative(username, campaign, personaname,
featurename, language)
except Exception as e:
logger.error(e)
data = ({'error': e.__str__()})
return data
def post(self):
content=request.get_json()
username=content['user']
campaignname=content['campaign'][0]
cur = mysql.connect ().cursor ()
# Getting user access for selected campaign by joining user and campiagn table
cur.execute(
"SELECT u.email,c.role from user u join user_campaign_xref uc on u.user_id=uc.user_user_id join campaign c on uc.campaign_campaign_id=c.campaign_id where c.campaign_name ='"+campaignname+"' and u.email != '"+username+"' order by u.email asc")
rv = cur.fetchall ()
data = {"email":[x[0] for x in rv], "role":[x[1] for x in rv]}
print("data",data)
return data
def post(self):
logger.info("Create mappings process is started")
content = request.get_json ()
username = content['user']
campaignname = content['campaign'][0]
personas = content['personas']
# print (personas)
features = content['features']
# print (features)
conn = mysql.connect ()
cur = conn.cursor ()
for a in personas:
personaname=a
cur.execute(
"SELECT persona_id FROM user u JOIN user_campaign_xref uc ON u.user_id=uc.user_user_id JOIN campaign c ON uc.campaign_campaign_id=c.campaign_id JOIN campaign_persona_xref cp ON c.campaign_id=cp.campaign_campaign_id JOIN persona p ON cp.persona_persona_id=p.persona_id WHERE u.email='" + username + "' and c.campaign_name='"+campaignname+"' and p.persona_name='"+personaname+"'")
pid=cur.fetchall()
# print("pid",pid)
pid=pid[0][0]
# print("pid2",pid)
for b in features:
featurename=b
cur.execute(
"SELECT f.feature_id FROM user u JOIN user_campaign_xref uc ON u.user_id=uc.user_user_id JOIN campaign c ON uc.campaign_campaign_id=c.campaign_id JOIN campaign_feature_xref cf ON c.campaign_id=cf.campaign_campaign_id JOIN feature f ON cf.feature_feature_id=f.feature_id WHERE u.email='" + username + "' and c.campaign_name='"+campaignname+"' and f.feature_name='"+featurename+"'")
fid=cur.fetchall()
# print("fid",fid)
fid=fid[0][0]
# print("fid2",fid)
cur.execute(
"SELECT COUNT(*) FROM persona_feature_xref where persona_persona_id="+ str(pid) +" AND feature_feature_id="+ str(fid) +" ")
ret=cur.fetchall()
ret=ret[0][0]
if ret == 0:
logger.info("Creating mapping for Persona '%s' and Feature '%s'",personaname,featurename)
cur.execute("INSERT INTO persona_feature_xref VALUES (%s,%s)",(pid,fid))
conn.commit()
else:
logger.info("Mapping for Persona '%s' and Feature '%s' is already exists",personaname,featurename)
logger.info("Mapping is successfuly created")
def post(self):
try:
logger.info("Getting exporting table information")
content = request.get_json()
username = content['user']
index = content['index']
logger.info("Collecting Data for user %s", username)
if index == 0:
offset = 0
else:
offset = (index - 1) * 5
conn = mysql.connect()
cur = conn.cursor()
cur.execute(
"SELECT campaign,table_name,status,CAST(create_date as char) FROM exportinfo WHERE user = '******' ORDER BY table_id DESC LIMIT 5 OFFSET %s",
offset)
rv = cur.fetchall()
cur.execute(" SELECT count(*) from exportinfo WHERE user = '******'")
ct = cur.fetchall()
cur.execute("SHOW TABLES")
tables = cur.fetchall()
total_rows = ct[0][0]
data = {
"total_rows": total_rows,
"data": [x for x in rv],
"tables": [x[0] for x in tables]
}
logger.info(
"Data successfuly collected for export table information")
except Exception as e:
logger.error(e.__str__())
data = {"total_rows": 0, "data": []}
return data
def post(self):
content = request.get_json()
username = content['user']
cur = mysql.connect().cursor()
# checking if user exists in database
cur.execute("SELECT COUNT(*) FROM user WHERE email = '" + username +
"'")
ret = cur.fetchall()
# print (ret)
ret = ret[0][0]
if ret == 0:
data = {"message": "false"}
else:
data = {"message": "true"}
return data
def post(self):
try:
content = request.get_json()
print(content)
campaignname = content['campaign'][0]
profile = content['profile'][0]
language = content['language']
logger.info("Approving Profile %s", profile)
conn = mysql.connect()
cur = conn.cursor()
cur.execute(
"UPDATE reviewinfo SET status='Approved', notification = 'Approved' WHERE review_campaign = '"
+ campaignname + "' AND review_profile = '" + profile +
"' AND language = '" + language + "'")
conn.commit()
cur.execute(
"SELECT rule FROM reviewinfo WHERE review_campaign = '" +
campaignname + "' AND review_profile = '" + profile +
"' AND language = '" + language + "'")
rule = cur.fetchall()
# print("dakdskds",rule[0])
cur.execute(
"SELECT approved_id FROM approvedrule WHERE campaign = '" +
campaignname + "' AND profile = '" + profile +
"' AND language = '" + language + "'")
rule_id = cur.fetchall()
if (rule_id == ()):
cur.execute(
"INSERT INTO approvedrule(campaign,profile,language,approved_date,rule) VALUES (%s,%s,%s,NOW(),%s)",
(campaignname, profile, language, rule[0][0]))
conn.commit()
else:
cur.execute("UPDATE approvedrule SET rule = '" + rule[0][0] +
"' WHERE campaign = '" + campaignname +
"' AND profile = '" + profile +
"' AND language = '" + language + "'")
conn.commit()
except Exception as e:
logger.error(e)
def post(self):
try:
logger.info("Uploading Html in database")
content = request.get_json()
# print("content \n\n\n\n\n",content)
campaignname = content['campaign'][0]
username = content['user']
template_name = content['fileName']
given_name = content['fileCustomName']
template = content['fileText']
conn = mysql.connect()
cur = conn.cursor()
cur.execute(
"SELECT COUNT(*) FROM htmltemplate WHERE campaign_name= '" +
campaignname + "'")
rv = cur.fetchall()
if rv[0][0] == 0:
cur.execute(
"INSERT INTO htmltemplate(user_name,campaign_name,template_name,upload_date,template) values(%s,%s,%s,NOW(),%s) ",
(username, campaignname, template_name, template))
conn.commit()
else:
cur.execute(
"UPDATE htmltemplate SET upload_date = NOW() , template = %s WHERE campaign_name = '"
+ campaignname + "'", (template))
conn.commit()
message = {"message": "Html is successfuly stored in database"}
logger.info("Html is successfuly stored in database")
except Exception as e:
logger.error(e)
message = {"message": "Fail"}
return message
def query_persona_rule_all(username, campaignname=None, campaignid=None):
cur = mysql.connect().cursor()
if campaignname == None and campaignid == None:
logger.error(
"Neither campaignname nor campaignid provided to query_persona_rule_all"
)
return ""
elif campaignid == None:
cur.execute(
"SELECT p.persona_rule from user u join user_campaign_xref us on u.user_id=us.user_user_id join campaign s on us.campaign_campaign_id=s.campaign_id join campaign_persona_xref cp on s.campaign_id=cp.campaign_campaign_id join persona p on cp.persona_persona_id=p.persona_id where u.email='"
+ username + "' AND s.campaign_name='" + campaignname + "'")
elif campaignname == None:
cur.execute(
"SELECT p.persona_rule from user u join user_campaign_xref us on u.user_id=us.user_user_id join campaign s on us.campaign_campaign_id=s.campaign_id join campaign_persona_xref cp on s.campaign_id=cp.campaign_campaign_id join persona p on cp.persona_persona_id=p.persona_id where u.email='"
+ username + "' AND s.campaign_id='" + campaignid + "'")
rv = cur.fetchall()
return (rv)
def delete(self):
content=request.get_json()
username = content['updateUser']
campaignname = content['campaign'][0]
print(content)
conn = mysql.connect ()
cur = conn.cursor ()
cur.execute(
"SELECT u.user_id,c.campaign_id FROM user u JOIN user_campaign_xref uc ON u.user_id=uc.user_user_id JOIN campaign c ON uc.campaign_campaign_id=c.campaign_id WHERE u.email = '"+username+"' AND c.campaign_name = '"+campaignname+"'")
ids = cur.fetchall ()
for a in ids :
uid = a[0]
cid = a[1]
cur.execute (
"DELETE FROM user_campaign_xref WHERE user_user_id = %s AND campaign_campaign_id = %s ",(uid,cid))
conn.commit ()
def post(self):
try:
logger.info("adding comment")
content = request.get_json()
print(content)
username = content['user']
campaignname = content['campaign'][0]
profile = content['profile'][0]
comment = content["comment"]
conn = mysql.connect()
cur = conn.cursor()
cur.execute(
"INSERT INTO commentinfo(comment_user,comment,create_date,notification) VALUES(%s,%s,NOW(),'Comment')",
(username, comment))
conn.commit()
cur.execute(
"SELECT comment_id from commentinfo ORDER BY comment_id DESC LIMIT 1"
)
cid = cur.fetchall()
cid = cid[0][0]
print("this is cid", cid)
cur.execute(
"SELECT review_id from reviewinfo WHERE review_campaign = '" +
campaignname + "' and review_profile = '" + profile + "'")
rv = cur.fetchall()
for rid in rv:
print("this is reviewvid", rid)
cur.execute("INSERT INTO review_comment_xref VALUES(%s,%s)",
(rid[0], cid))
conn.commit()
logger.info("Comment is successfuly added")
except Exception as e:
logger.error(e)
return e
def post(self):
try:
logger.info("Started saving data for given state")
content = request.get_json()
username = content['user']
campaign = content['campaign'][0]
feature = content['exportFeature'][0]
language = content['language']
commit_msg = content['versionName']
print(content)
logger.info("Saving state for campaign %s and feature %s ",
campaign, feature)
conn = mysql.connect()
cur = conn.cursor()
cur.execute(
"SELECT l.rule from user u join user_campaign_xref us on u.user_id=us.user_user_id join campaign s on us.campaign_campaign_id=s.campaign_id join campaign_feature_xref cf on s.campaign_id=cf.campaign_campaign_id join feature g on cf.feature_feature_id=g.feature_id join feature_language_xref fl on g.feature_id=fl.feature_feature_id join language l on fl.language_language_id=l.language_id where u.email='"
+ username + "' AND s.campaign_name='" + campaign +
"' AND g.feature_name = '" + feature + "' AND l.language='" +
language + "'")
rv = cur.fetchall()
print("here is rule \n %s", rv)
cur.execute(
"INSERT INTO versioninfo(user,campaign,feature,language ,comment,create_date,rule) VALUES (%s,%s,%s,%s,%s,NOW(),%s)",
(username, campaign, feature, language, commit_msg, rv))
conn.commit()
logger.info("State is saved successfuly")
message = {"message": "true"}
except Exception as e:
logger.error(e)
message = {"message": "false"}
return message
def post(self):
content=request.get_json()
username=content['user']
campaignname=content['campaign'][0]
cur = mysql.connect ().cursor ()
cur.execute(
"SELECT email FROM user WHERE email != '"+username+"'")
ulist = cur.fetchall ()
print("from user",ulist)
# Getting list of all users
cur.execute(
"SELECT DISTINCT u.email from user u join user_campaign_xref uc on u.user_id=uc.user_user_id join campaign c on uc.campaign_campaign_id=c.campaign_id where u.email != '"+username+"' and c.campaign_name = '"+campaignname+"'")
rv = cur.fetchall ()
sameuser = [x[0] for x in rv]
cur.execute(
"SELECT DISTINCT u.email from user u join user_campaign_xref uc on u.user_id=uc.user_user_id join campaign c on uc.campaign_campaign_id=c.campaign_id where u.email != '"+username+"' and c.campaign_name != '"+campaignname+"'")
unames = cur.fetchall ()
unames = [x[0] for x in unames]
users = []
for a in unames:
if a not in sameuser:
users.append(a)
for a in ulist :
if a[0] not in sameuser:
if a[0] not in unames:
users.append(a[0])
return {"users": users}
def post(self):
logger.info("Getting Rule process is started")
content = request.get_json()
username = content['user']
campaignname = content['campaign'][0]
features = content['exportFeature']
language = content['language']
# Return empty rule if feature list is empty
if features == []:
data = {"rule": ""}
else:
logger.info(
"Selecting Rule for Campaign '%s' , Feature %s and Language '%s'",
campaignname, features, language)
cur = mysql.connect().cursor()
feature_str = "'" + "','".join([a for a in features]) + "'"
# select rule for features
cur.execute(
"SELECT l.rule from user u join user_campaign_xref us on u.user_id=us.user_user_id join campaign s on us.campaign_campaign_id=s.campaign_id join campaign_feature_xref cf on s.campaign_id=cf.campaign_campaign_id join feature g on cf.feature_feature_id=g.feature_id join feature_language_xref fl on g.feature_id=fl.feature_feature_id join language l on fl.language_language_id=l.language_id where u.email='"
+ username + "' AND s.campaign_name='" + campaignname +
"' AND g.feature_name IN (" + feature_str +
") AND l.language='" + language + "'")
rv = cur.fetchall()
# Combining all rules
return_str = "\n".join(ite for item in rv for ite in item)
data = {"rule": return_str}
logger.info("Getting rule is successfull")
return data
def post(self):
content = request.get_json ()
email = content['email']
password = content['password']
cur = mysql.connect ().cursor ()
cur.execute ("SELECT COUNT(*) FROM user WHERE email = '" + email + "'")
ret = cur.fetchall ()
# print (ret)
ret = ret[0][0]
if ret == 1:
cur.execute ("SELECT password FROM user WHERE email = '" + email + "'")
hashed = cur.fetchall ()
hashed = hashed[0][0]
ret = CheckLogin (plain_text_password=str (password),
hashed_password_from_db=str (hashed))
em = False
passw = ret
if (ret):
logger.info("User %s is successfully logged in",email)
final = {"success": True}
else:
logger.info("User %s is trying to login with wrong password ",email)
final = {"errors": {"password": True}}
else:
ret = False
em = True
passw = False
final = {"errors": {"email": True}}
logger.info("User %s is not found in database",email)
return final
def post(self):
content=request.get_json()
username=content['user']
cur = mysql.connect ().cursor ()
#Getting campaign name ,user role and discription for user
cur.execute(
"SELECT c.campaign_name, c.role, c.campaign_description from user u join user_campaign_xref uc on u.user_id=uc.user_user_id join campaign c on uc.campaign_campaign_id=c.campaign_id where u.email = '"+username+"' ")
rv = cur.fetchall ()
# Get right campaign id (can not select directly because reviewer role campaign id is different)
campaign_data = {}
for campaign in rv:
cur.execute("SELECT c.campaign_id FROM campaign c WHERE c.campaign_name = '"+campaign[0]+"' AND c.role = 'Editor'")
cid = cur.fetchall ()
campaign_data[cid[0][0]] = campaign
data = {"campaign":[x[0] for x in campaign_data.values()], "role":[x[1] for x in campaign_data.values()],
"description":[x[2] for x in campaign_data.values()],"campaign_ids":[k for k in campaign_data.keys()]}
return data
