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