def updateBox(self, event=None):
self.DBs = dbu.getDBs()
self.dbbox['values'] = self.DBs['Database'].unique().tolist()
self.confidence['values'] = [
"All" if x is None else x
for x in self.DBs['ExpertConfidence'].loc[
self.DBs['Database'] == self.dbbox.get()].tolist()
]
try:
self.confidence.current(self.DBs['ExpertConfidence'].loc[
self.DBs['Database'] == self.dbbox.get()].tolist().index(
"High"))
except:
self.confidence.current(self.DBs['ExpertConfidence'].loc[
self.DBs['Database'] == self.dbbox.get()].tolist().index(None))
self.lAll.delete(0, END)
self.pms = dbu.getAll(Database=self.dbbox.get())
self.pmTargets = []
for item in sorted([
"{}/{} ({})".format(pm.StandardName, pm.SystematicName,
pm.MotifID) for pm in self.pms
if pm.EC == self.confidence.get()
or self.confidence.get() == "All"
]):
self.lAll.insert(END, item)
def get_report_score_by_cplist_paper(cplist, paper):
query = Report.objects.filter(respondent=paper.respondent)
if len(query) == 0:
return None
else:
report = query[0]
cps = []
for cp in cplist:
if cp.name == 'Total':
cps.append('total')
else:
cps.append(cp.name)
cps_str = ','.join(cps)
sql = 'select %s from mc_reportdata where id=%s;' % (cps_str, report.id)
try:
c, con = DbUtils.cursor()
c.execute(sql)
ret = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
if ret:
return [score or 0 for score in ret]
return None
def get_brand_report_sub_score(report, sub_checkpoint_list):
project = report.project
report_id = report.id
columstr = ','.join (sub_checkpoint_list)
if project.id == constant.competition_project_id:
columstr = columstr.replace(',B12,', ',S1,')
columstr = columstr.replace(',B14a,', ',S2,')
columstr = columstr.replace(',D32,', ',S3,')
columstr = columstr.replace(',D33,', ',S4,')
columstr = columstr.replace(',D41a,', ',S5,')
columstr = columstr.replace(',D40a,', ',S6,')
elif project.id == constant.current_mini_project_id:
columstr = columstr.replace(',D63,', ',T1,')
columstr = columstr.replace(',D40a,', ',T2,')
columstr = columstr.replace(',G51,', ',T3,')
sql = 'select %s from mc_reportdata where id=%s;' % (columstr, report_id)
try:
c, con = DbUtils.cursor()
c.execute(sql)
cur = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
return cur
def removeTarget(self):
try:
selection = self.ltargets.curselection()
self.ltargets.delete(selection[0])
targets = []
for item in self.vtargets.get():
target = int(item[item.find("(") + 1:item.find(")")])
targets.append(target)
self.lparalogs.config(text='')
paralogs = [
x for x in dbu.getParalogs(targets,
type="Order",
EC=self.confidence.get(),
Database=self.dbbox.get())
if x != None
]
aux = []
if (paralogs != []):
if (any(isinstance(i, list) for i in paralogs)):
paralogs = [
item for sublist in paralogs for item in sublist
]
for item in set(paralogs):
if item not in targets:
aux.append(item)
self.lparalogs.config(text="Paralogs: {" + ', '.join(sorted(aux)) +
"}")
except:
pass
def add_reportdata_column(project_id):
checkpoint_list = []
project = Project.objects.get(id=project_id)
sql = 'select * from mc_otherreportdata where id = 1'
try:
c, con = DbUtils.cursor()
c.execute(sql)
column_names = [d[0] for d in c.description]
cp_list = CheckPoint.objects.filter(project=project)
for cp in cp_list:
if cp.name not in column_names:
checkpoint_list.append(cp.name)
print checkpoint_list
for cid in checkpoint_list:
sql = 'alter table mc_otherreportdata add column \'%s\' float;' % cid
c.execute(sql)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def get_reportdata_by_paper(paper):
cp_list = _checkpoint.get_checkpoint_list_by_project(paper.project)
cp_str_list = ','.join([str(cp.name) for cp in cp_list])
report = Report.objects.filter(respondent=paper.respondent)[0]
if report:
report_id = '%s' % report.id
sql = 'select %(cp_str_list)s from mc_reportdata where id = %(report_id)s;' % vars()
try:
c, con = DbUtils.cursor()
c.execute(sql)
cp_score_list = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
results = []
for cp_score in cp_score_list:
if cp_score:
results.append(cp_score)
else:
results.append(u'不适用')
return results
else:
return None
def get_answer(self, respondent_id):
rid = respondent_id
column_list = self.get_column_list()
import DbUtils
try:
c, con = DbUtils.cursor()
if len(column_list) == 2:
sql = 'select %s,%s from survey_respondentdata where id=%s' % (column_list[0]['name'], column_list[1]['name'], respondent_id)
c.execute(sql)
aid, alt_open = c.fetchone()
if not aid:
return (None, None)
cid = get_alt_cid_by_id(aid)
return (cid, alt_open)
else:
sql = 'select %s from survey_respondentdata where id=%s' % (column_list[0]['name'], respondent_id)
c.execute(sql)
aid = c.fetchone()[0]
if not aid:
return (None, None)
cid = get_alt_cid_by_id(aid)
return (cid, '')
finally:
if c:
c.close()
if con:
con.close()
def auto_add_respondentdata(sender, **kwargs):
respondent = kwargs['instance']
created = kwargs['created']
import DbUtils
c, db = DbUtils.cursor()
if created:
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute('insert into survey_respondentdata (id) values(%s);' % respondent.id)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def auto_add_respondentdata(sender, **kwargs):
respondent = kwargs['instance']
created = kwargs['created']
import DbUtils
c, db = DbUtils.cursor()
if created:
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute('insert into survey_respondentdata (id) values(%s);' %
respondent.id)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def get_otherreport_sub_score(report_id, project_id):
sql = 'select %s from mc_otherreportdata where id=%s;' % (get_sub_score_column_str(project_id), report_id)
try:
c, con = DbUtils.cursor()
c.execute(sql)
cur = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
return cur
def get_report_sub_score(report_id):
sql = 'select %s from mc_reportdata where id=%s;' % (get_sub_score_column_str(), report_id)
try:
c, con = DbUtils.cursor()
c.execute(sql)
ret = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
return ret
def calculate_paper_total_score(paper):
#该project下检查点的选项(id,得分)字典
cp_alt_score_dict = _alternative.get_project_cp_alt_score_dict(paper.project)
#样卷答案字典，以resp_col为key
respond_dict = survey_utils.get_respondentdata_dict_by_paper(paper)
print respond_dict
sub_cp_list = _checkpoint.get_project_sub_cp_list(paper.project)
paper_score_dict = {}
cp_group_id_map = {}
cal_cp_list = [] #有效检查点
for cp in sub_cp_list:
value = respond_dict.get(cp.resp_col) #respondent_data，列有� 此值为alternative_id
if value is None or value == '':
continue
alternative = []
if isinstance(value, (int, long, float)):
alternative = Alternative.objects.filter(id=value, question=cp.question)
#打分题特殊处理（老题号G51，新题号G50--即name_abbr）
if cp.name == 'G51':
cal_cp_list.append(cp)
elif len(alternative) > 0:
#剔除选择'不适用'的检查点
if alternative[0].cid == 'A3':
paper_score_dict[cp.name] = 'null'
continue
cal_cp_list.append(cp)
if cp.name == 'G51':
#G51打分题目
paper_score_dict[cp.name] = value
else:
paper_score_dict[cp.name] = map_score(value, cp_alt_score_dict)
if cp.name == 'T3':
paper_score_dict[cp.name] = '"%s"' % value
#生成环节及检查点结构
for cp in cal_cp_list:
grp = cp_group_id_map.get(cp.parent.id, None)
if grp is None:
grp = cp.parent
grp.child_lists = []
cp_group_id_map[cp.parent.id] = grp
grp.child_lists.append(cp)
checkpoint_group = get_avg_for_checkpoint_group(paper, paper_score_dict, cp_group_id_map.values())
paper_score_dict.update(checkpoint_group)
paper.score = checkpoint_group.get('total')
try:
c, con = DbUtils.cursor()
update_one_project_papers_report(paper, paper_score_dict, c, con, True) #生成report及report_data,更新paper.score
finally:
if c:
c.close()
if con:
con.close()
def __inner():
u'''仅取有检查点的问题的选项id,得分字字典（检查点中可能还有题目不计分，这里先不管'''
sql = 'select sa.id, sa.score from survey_checkpoint sc, survey_alternative sa, survey_question sq where sa.question_id = sq.id and sc.question_id = sq.id and sq.project_id=%d and sa.score is not null ' % project.id
try:
c, con = DbUtils.cursor()
c.execute(sql)
results = c.fetchall()
finally:
if c:
c.close()
if con:
con.close()
return dict([(data[0], data[1]) for data in results])
def __inner():
u'''仅取有检查点的问题的选项id,得分字字典（检查点中可能还有题目不计分，这里先不管'''
sql = 'select sa.id, sa.score from survey_checkpoint sc, survey_alternative sa, survey_question sq where sa.question_id = sq.id and sc.question_id = sq.id and sq.project_id=%d and sa.score is not null ' % project.id
try:
c, con = DbUtils.cursor()
c.execute(sql)
results = c.fetchall()
finally:
if c:
c.close()
if con:
con.close()
return dict([(data[0], data[1]) for data in results])
def get_report_score(report_id):
sql = 'select %s from mc_reportdata where id=%s;' % (get_main_score_column_str(), report_id)
try:
c, con = DbUtils.cursor()
c.execute(sql)
ret = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
if ret:
return [score or 0 for score in ret]
return None
def create_otherreportdata_table(project_id):
field_list = get_other_report_field_list(project_id)
field_str = ','.join(str(field) for field in field_list)
sql = 'create table if not exists mc_otherreportdata (%s);' % (field_str)
try:
c, con = DbUtils.cursor()
c.execute(sql)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def create_respodentdata_table(project_id):
field_list = get_respondent_field_list(project_id)
field_str = ','.join(str(field) for field in field_list)
sql = 'create table if not exists survey_respondentdata (%s);' % (field_str)
# print sql
try:
c, con = DbUtils.cursor()
c.execute(sql)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def get_bmw_top_score(term, report, checkpoint=None):
column = checkpoint.name if checkpoint else 'total'
term_id = term.id
dealertype_id = report.dealer.dealertype.id
sql = 'select max(data.%(column)s) from mc_report r,mc_reportdata data where r.term_id=%(term_id)s and r.id=data.id and r.dealertype_id=%(dealertype_id)s;' % vars()
try:
c, con = DbUtils.cursor()
c.execute(sql)
cur = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
return cur[0]
def get_answer(self, respondent_id):
rid = respondent_id
column_list = self.get_column_list()
sql = 'select %s from survey_respondentdata where id=%s' % (column_list[0]['name'], respondent_id)
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
result = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
return result[0]
def get_otherreport_group_score_0(report_id, cp_group_list_str):
sql = 'select %s from mc_otherreportdata where id=%s;' % (cp_group_list_str, report_id)
try:
c, con = DbUtils.cursor()
c.execute(sql)
ret = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
if ret:
return [score or 0 for score in ret]
return None
def create_respodentdata_table(project_id):
field_list = get_respondent_field_list(project_id)
field_str = ','.join(str(field) for field in field_list)
sql = 'create table if not exists survey_respondentdata (%s);' % (
field_str)
# print sql
try:
c, con = DbUtils.cursor()
c.execute(sql)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def get_answer(self, respondent_id):
rid = respondent_id
column_list = self.get_column_list()
sql = 'select %s from survey_respondentdata where id=%s' % (
column_list[0]['name'], respondent_id)
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
result = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
return result[0]
def get_bmw_top_score(term, checkpoint=None):
column = checkpoint.name if checkpoint else 'total'
term_id = term.id
dealertype_id = get_dealertype_BMW().id
sql = 'select max(data.%(column)s) from mc_report r,mc_reportdata data where r.term_id=%(term_id)s and r.id=data.id and r.dealertype_id=%(dealertype_id)s;' % vars()
try:
c, con = DbUtils.cursor()
c.execute(sql)
ret = c.fetchone()[0]
finally:
if c:
c.close()
if con:
con.close()
return ret
def get_data(self, column_name):
sql = 'select %s from survey_respondentdata where id=%s;' % (column_name, self.id)
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
result = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
if not result:
return None
else:
return result[0]
def get_data(self, column_name):
sql = 'select %s from survey_respondentdata where id=%s;' % (
column_name, self.id)
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
result = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
if not result:
return None
else:
return result[0]
def set_answer(self, respondent_id, answer):
rid = respondent_id
answer = self.parse_answer(answer)
column_list = self.get_column_list()
name_value_str = "%s='%s'" % (column_list[0]['name'], answer)
sql = 'update survey_respondentdata set %(name_value_str)s where id = %(rid)s;' % vars()
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
if con:
con.commit();
finally:
if c:
c.close()
if con:
con.close()
def get_respondentdata_dict_by_paper(paper):
respondentdata_id = paper.respondent.id
sql = 'select * from survey_respondentdata where id = %s' % respondentdata_id
try:
c, con = DbUtils.cursor()
c.execute(sql)
column_names = [d[0] for d in c.description]
list = []
row = c.fetchone()
for index, r in enumerate(row):
list.append((column_names[index], r))
finally:
if c:
c.close()
if con:
con.close()
return dict(list)
def get_respondentdata_dict_by_paper(paper):
respondentdata_id = paper.respondent.id
sql = 'select * from survey_respondentdata where id = %s' % respondentdata_id
try:
c, con = DbUtils.cursor()
c.execute(sql)
column_names = [d[0] for d in c.description]
list = []
row = c.fetchone()
for index, r in enumerate(row):
list.append((column_names[index], r))
finally:
if c:
c.close()
if con:
con.close()
return dict(list)
def aggreate_otherreportdata(resp_id_list, project, term, other_report, create):
total_cp_name_list = _checkpoint.get_project_cp_name_list_with_total(project, False)
#total_cp_name_list_str = ','.join(total_cp_name_list)
cp_avg_list_str = ','.join(['avg(%s)' % name for name in total_cp_name_list])
resp_id_list_str = ','.join(map(str, resp_id_list))
project_id = project.id
paper_type = enums.BMW_PAPER_TYPE
if project.id == constant.competition_project_id:
paper_type = enums.FW_PAPER_TYPE
try:
c, con = DbUtils.cursor()
term_id = term.id
#只取bmw终审得分或竞品取fw问卷
sql = 'select min(total), max(total), %(cp_avg_list_str)s from mc_reportdata data,mc_report report where report.project_id=%(project_id)s and report.paper_type=\'%(paper_type)s\' and report.term_id=%(term_id)s and data.id=report.id and report.respondent_id in (%(resp_id_list_str)s);' % vars()
c.execute(sql)
data_list = c.fetchone()
score_list = map(handle_sql_data, data_list)
paper_score_dict = {}
# paper_score_dict['min_total'] = score_list[0]
# paper_score_dict['max_total'] = score_list[1]
for i, score in enumerate(score_list[2:]):
paper_score_dict[total_cp_name_list[i]] = score
if term.is_finished():
if create:
#新建的otherreport，主动插入条mc_otherreportdata,仅有id, 以下全只要更新列
insert_reportdt_sql = 'insert into mc_otherreportdata (id) values(%s)' % str(other_report.id)
c.execute(insert_reportdt_sql)
if con:
con.commit()
#print u'%d-%d-%s: %s >> %s' % (dealer.id, dealer.level, dealertype.name_en, report.score, dealer_id_list_str)
sql_str = ','.join(['%s=%%(%s)s' % (key, key) for key in paper_score_dict])
sql = "update mc_otherreportdata set %s where id=%s" % (sql_str, other_report.id)
sql = sql % paper_score_dict
c.execute(sql)
if con:
con.commit()
else:
#print u'%s 尚未结束, 不予统计平均� % term.name
pass
finally:
if c:
c.close()
if con:
con.close()
def set_answer(self, respondent_id, answer):
rid = respondent_id
answer = self.parse_answer(answer)
column_list = self.get_column_list()
name_value_str = "%s='%s'" % (column_list[0]['name'], answer)
sql = 'update survey_respondentdata set %(name_value_str)s where id = %(rid)s;' % vars(
)
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def createDB(self):
dbname = self.newdb.get().strip()
if (dbname != "" and dbname != None):
if (dbname not in dbu.getDBs()):
folder = filedialog.askdirectory()
if (folder != "" and folder != None):
names = dbc.files2DB('TF.db',
dbc.SearchFiles(folder, dbname),
New=False)
#criar as outras tabelas
dbc.EmptyInfo('TF.db', names)
self.updateBox()
self.dialog("Database created!")
else:
self.dialog("Nome já existente.")
else:
self.dialog("Nome vazio.")
def get_reportdata_dict_by_report(report):
if report:
sql = 'select * from mc_reportdata where id = %s' % report.id
try:
c, con = DbUtils.cursor()
c.execute(sql)
column_names = [d[0] for d in c.description]
list = []
row = c.fetchone()
for index, r in enumerate(row):
list.append((column_names[index], r))
finally:
if c:
c.close()
if con:
con.close()
return dict(list)
else:
return None
def _get_field_list():
no_fs_list = ['term_id', 'dealer_code', 'appraiser_code', 'customer_code', 'id']
sql = 'select * from survey_respondentdata where id =1;'
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
ret = c.fetchone()
fields = []
for i in c.description:
f = i[0]
if f not in no_fs_list:
fields.append(f)
finally:
if c:
c.close()
if con:
con.close()
return fields
def get_reportdata_dict_by_report(report):
if report:
sql = 'select * from mc_reportdata where id = %s' % report.id
try:
c, con = DbUtils.cursor()
c.execute(sql)
column_names = [d[0] for d in c.description]
list = []
row = c.fetchone()
for index, r in enumerate(row):
list.append((column_names[index], r))
finally:
if c:
c.close()
if con:
con.close()
return dict(list)
else:
return None
def changeInput(self):
if (self.fcontainer.index(self.fcontainer.select()) == 0):
self.fsac.pack(side=TOP, fill=X)
if (self.genome == None):
#carregar reguladores + None
self.regulators = [None] + dbu.getRegulators()
self.regulator['values'] = self.regulators
self.regulator.current(self.regulators.index(None))
#carregar fasta
genome = Utils.getGenome(
"Genome/GCF_000146045.2_R64_genomic.fna")
#carregar gff
gff = Utils.getAnnotation(
"Genome/GCF_000146045.2_R64_genomic.gff")
self.genome = [genome, gff]
self.updateGenes()
self.searchGenes()
def get_detailed_fieldwork_status(dealer_type_tuple, term_id):
'''提供某一种或多种类型的经销商执行进度的查询接口，返回值为（经销商代码，城市，省份，经销商名称，访问状态，访问日期，报告）类型的queryset
@param dealer_type_tuple:经销商类型ID值组成的tuple
@param term_id:期数ID值
'''
try:
c, con = DbUtils.cursor()
if len(dealer_type_tuple) == 1:
dealer_type_tuple = dealer_type_tuple[0]
c.execute('SELECT mc_dealer.name, mc_dealer.city_cn, mc_dealer.city_en, mc_dealer.province_cn, mc_dealer.province_en, mc_dealer.name_cn, mc_dealer.name_en, mc_report.status, survey_respondent.start_time, mc_report.survey_code FROM mc_dealer LEFT JOIN mc_report ON mc_dealer.id = mc_report.dealer_id LEFT JOIN survey_respondent ON mc_report.respondent_id = survey_respondent.id AND mc_report.term_id= %s WHERE mc_dealer.dealertype_id = %s' % (term_id, dealer_type_tuple))
else:
c.execute('SELECT mc_dealer.name, mc_dealer.city_cn, mc_dealer.city_en, mc_dealer.province_cn, mc_dealer.province_en, mc_dealer.name_cn, mc_dealer.name_en, mc_report.status, survey_respondent.start_time, mc_report.survey_code FROM mc_dealer LEFT JOIN mc_report ON mc_dealer.id = mc_report.dealer_id LEFT JOIN survey_respondent ON mc_report.respondent_id = survey_respondent.id AND mc_report.term_id= %s WHERE mc_dealer.dealertype_id IN %s' % (term_id, dealer_type_tuple))
result = c.fetchall()
finally:
if c:
c.close()
if con:
con.close()
return result
def addTF(self, orientation):
try:
item = self.lAll.get(self.lAll.curselection())
x = item.split(" ")
exist = False
targets = []
for target in self.vtargets.get():
z = target.split(" ")
if (x[0] == z[0] and x[1] == z[1] and orientation == z[2]):
exist = True
if (not exist):
aux = item[item.find("(") + 1:item.find(")")]
targets.append(
int(aux) if aux != "None" else item.split("/")[0])
if (orientation == "+"):
self.ltargets.insert(END, item + " +")
elif (orientation == "-"):
self.ltargets.insert(END, item + " -")
self.lparalogs.config(text='')
paralogs = [
x for x in dbu.getParalogs(targets,
type="Order",
EC=self.confidence.get(),
Database=self.dbbox.get())
if x != None
]
aux = []
if (paralogs != []):
if (any(isinstance(i, list) for i in paralogs)):
paralogs = [
item for sublist in paralogs for item in sublist
]
for item in set(paralogs):
if item not in targets:
aux.append(item)
self.lparalogs.config(text="Paralogs: {" + ', '.join(sorted(aux)) +
"}")
except:
pass
def get_report_score(report_id, project_id, column_list=None):
project = _project.get_project_by_id(project_id)
if not column_list:
column_str = get_main_score_column_str(project)
else:
column_str = ','.join(column_list)
sql = 'select %s from mc_reportdata where id=%s;' % (column_str, report_id)
try:
c, con = DbUtils.cursor()
c.execute(sql)
ret = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
if ret:
return [score or 0 for score in ret]
return None
def _get_field_list():
no_fs_list = ['term_id', 'dealer_code', 'appraiser_code', 'customer_code', 'id']
sql = 'select * from survey_respondentdata;'
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
con.commit()
ret = c.fetchone()
fields = []
for i in c.description:
f = i[0]
if f not in no_fs_list:
fields.append(f)
finally:
if c:
c.close()
if con:
con.close()
return fields
def _get_dealer_data(dealer, term, fs):
paper = mc.get_paper(dealer=dealer, term=term)
if paper is None:
return []
resp = paper.respondent
fs_str = ','.join(fs)
sql = 'select %s from survey_respondentdata where id=%d;' % (fs_str, resp.id)
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
con.commit()
ret = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
return ret
def generate_report_by_paper(papers):
u"""更新指定dealer的report数据"""
project_dict = {} #同种类型的问卷字典分
for p in papers:
project = p.project
paper_list = project_dict.get(project)
if paper_list is None:
paper_list = []
paper_list.append(p)
project_dict[project] = paper_list
try:
c, con = DbUtils.cursor()
for project in project_dict.keys():
papers = project_dict.get(project)
gen_papers_score(project, papers, c, con)
finally:
if c:
c.close()
if con:
con.close()
set_dirty()
def get_dealer_score_info(dealer, curr_term, cp, paper):
try:
c, con = DbUtils.cursor()
sql = 'select %s from survey_respondentdata where id=%d;' % (cp.name, paper.respondent_id)
c.execute(sql)
ret = c.fetchone()
finally:
if c:
c.close()
if con:
con.close()
aid = ret[0]
a = Alternative.objects.get(id=aid)
#print 'a.title',a.title
cp.dealer_score = a.title
if cp.dealer_score == u'是':
cp.zero_reason = ''
cp.zero_reason_en = ''
else:
cp.zero_reason, cp.zero_reason_en = get_zero_reason(curr_term, dealer, cp)
def set_answer(self, respondent_id, answer):
rid = respondent_id
answer = self.parse_answer(answer)
column_list = self.get_column_list()
name_value_list = []
for index, column in enumerate(column_list):
name_value_list.append("%s='%s'" % (column['name'], answer[index]))
name_value_str = ','.join(name_value_list)
sql = 'update survey_respondentdata set %(name_value_str)s where id = %(rid)s;' % vars()
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def set_answer(self, respondent_id, answer):
rid = respondent_id
answer = self.parse_answer(answer)
column_list = self.get_column_list()
name_value_list = []
for index, column in enumerate(column_list):
name_value_list.append("%s=%s" % (column['name'], answer[index]))
name_value_str = ','.join(name_value_list)
sql = 'update survey_respondentdata set %(name_value_str)s where id = %(rid)s;' % vars(
)
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def get_dealer_reports(dealer_type_tuple, regional, term_id):
'''提供某一种或多种类型的经销商访问情况报告的查询接口，返回值为（经销商代码，城市，省份，经销商名称，经销商本期最终得分，报告）类型的queryset
@param dealer_type_tuple:经销商类型ID值组成的tuple
@param regional:区域对象
@param term_id:期数ID值
'''
try:
c, con = DbUtils.cursor()
dealer_id_list = utils.get_sub_leaf_dealer_id_list(regional)
dealer_id_list = tuple(dealer_id_list)
if len(dealer_type_tuple) == 1:
dealer_type_tuple = dealer_type_tuple[0]
c.execute('SELECT mc_dealer.name, mc_dealer.city_cn, mc_dealer.city_en, mc_dealer.province_cn, mc_dealer.province_en, mc_dealer.name_cn, mc_dealer.name_en, mc_reportdata.total, mc_report.survey_code FROM mc_dealer LEFT JOIN mc_report ON mc_dealer.id = mc_report.dealer_id LEFT JOIN mc_reportdata ON mc_report.id = mc_reportdata.id AND mc_report.term_id= %s WHERE mc_dealer.id in %s AND mc_dealer.dealertype_id = %s' % (term_id, dealer_id_list, dealer_type_tuple))
else:
c.execute('SELECT mc_dealer.name, mc_dealer.city_cn, mc_dealer.city_en, mc_dealer.province_cn, mc_dealer.province_en, mc_dealer.name_cn, mc_dealer.name_en, mc_reportdata.total, mc_report.survey_code FROM mc_dealer LEFT JOIN mc_report ON mc_dealer.id = mc_report.dealer_id LEFT JOIN mc_reportdata ON mc_report.id = mc_reportdata.id AND mc_report.term_id= %s WHERE mc_dealer.id in %s AND mc_dealer.dealertype_id in %s' % (term_id, dealer_id_list, dealer_type_tuple))
result = c.fetchall()
finally:
if c:
c.close()
if con:
con.close()
return result
def Score(self, event=None):
# create new elements
targets = []
for item in self.vtargets.get():
aux = item[item.find("(") + 1:item.find(")")]
targets.append(int(aux) if aux != "None" else item.split("/")[0])
self.targets = {}
if (targets != []):
self.targets = {i: None for i in targets}
for item in self.vtargets.get():
x = item.split(" ")
key = x[1][1:-1]
key = int(key) if key != "None" else x[0].split("/")[0]
if (self.targets[key] == None):
self.targets[key] = (0 if x[2] == "-" else 1)
else:
self.targets[key] = 2
self.maximize = list(self.targets.values())
self.pmTargets = dbu.getAll(list(
self.targets.keys()))[:len(self.targets)]
targetNames = [
"{}/{} ({})".format(pm.StandardName, pm.SystematicName,
str(pm.MotifID)) for pm in self.pmTargets
]
self.pmNonTargets = [
pm for pm in self.pms
if ("{}/{} ({})".format(pm.StandardName, pm.SystematicName,
str(pm.MotifID)) not in targetNames and (
pm.EC == self.confidence.get()
or self.confidence.get() == "All"))
]
if (self.fcontOptions.index(self.fcontOptions.select()) == 0):
self.SingleScore()
elif (self.fcontOptions.index(self.fcontOptions.select()) == 1):
self.MultiScore()
def export():
sqls = 'select distinct mp.id, md.name as dealercode, md.name_cn," B18" as " B18" , sa1.title , sa1.score ,B21a__open, " D39" as " D39" , sa2.title , sa2.score ,D63__open from survey_respondentdata srd, mc_paper mp, mc_dealer md, mc_term_dealers mtd LEFT JOIN survey_alternative sa1 ON sa1.id = B21a LEFT JOIN survey_alternative sa2 ON sa2.id = D63 where mp.respondent_id = srd.id and mp.project_id = 2 and mp.paper_type in ("GFK") and md.id = mp.dealer_id and mtd.dealer_id = mp.dealer_id and mtd.term_id = mp.term_id'
c, db = DbUtils.cursor()
c.execute(sqls)
result = c.fetchall()
data_list = []
b18 = Question.objects.get(cid='B21a', project__id=2)
d39 = Question.objects.get(cid='D63', project__id=2)
index = 0
for data in result:
item = Item()
item.dealer_code = data[1]
item.dealer_name = data[2]
item.B18_name = data[3]
item.B18_value = data[4]
item.B18_score = data[5]
B18_open = data[6]
if B18_open is not None and B18_open != '':
B18_open = restore_result(b18, B18_open)
B18_open = B18_open.replace('<br>', '')
else:
B18_open = ''
item.D39_name = data[7]
item.D39_value = data[8]
item.D39_score = data[9]
D39_open = data[10]
if D39_open is not None and D39_open != '':
D39_open = restore_result(d39, D39_open)
D39_open = D39_open.replace('<br>', '')
else:
D39_open = ''
#print data[1], ',', data[2], ',', data[3], ',', data[4], ',', data[5], ',', B18_open, ',', data[7], ',', data[8], ',', data[9], ',', D39_open
msg = 'UserProfile and LoginLogout will be deleted. Sure? (y/n):'
# if index % 100 == 0 and'y' == raw_input(msg):
# continue
index += 1
def export():
sqls = 'select distinct mp.id, md.name as dealercode, md.name_cn," B18" as " B18" , sa1.title , sa1.score ,B21a__open, " D39" as " D39" , sa2.title , sa2.score ,D63__open from survey_respondentdata srd, mc_paper mp, mc_dealer md, mc_term_dealers mtd LEFT JOIN survey_alternative sa1 ON sa1.id = B21a LEFT JOIN survey_alternative sa2 ON sa2.id = D63 where mp.respondent_id = srd.id and mp.project_id = 2 and mp.paper_type in ("GFK") and md.id = mp.dealer_id and mtd.dealer_id = mp.dealer_id and mtd.term_id = mp.term_id'
c, db = DbUtils.cursor()
c.execute(sqls)
result = c.fetchall()
data_list = []
b18 = Question.objects.get(cid='B21a', project__id=2)
d39 = Question.objects.get(cid='D63', project__id=2)
index = 0
for data in result:
item = Item()
item.dealer_code = data[1]
item.dealer_name = data[2]
item.B18_name = data[3]
item.B18_value = data[4]
item.B18_score = data[5]
B18_open = data[6]
if B18_open is not None and B18_open != '':
B18_open = restore_result(b18, B18_open)
B18_open = B18_open.replace('<br>', '')
else:
B18_open = ''
item.D39_name = data[7]
item.D39_value = data[8]
item.D39_score = data[9]
D39_open = data[10]
if D39_open is not None and D39_open != '':
D39_open = restore_result(d39, D39_open)
D39_open = D39_open.replace('<br>', '')
else:
D39_open = ''
#print data[1], ',', data[2], ',', data[3], ',', data[4], ',', data[5], ',', B18_open, ',', data[7], ',', data[8], ',', data[9], ',', D39_open
msg = 'UserProfile and LoginLogout will be deleted. Sure? (y/n):'
# if index % 100 == 0 and'y' == raw_input(msg):
# continue
index += 1
def add_respondentdata_column(project_id):
project = Project.objects.get(id=project_id)
sql = 'select * from survey_respondentdata where id = 1'
try:
c, con = DbUtils.cursor()
c.execute(sql)
column_names = [d[0] for d in c.description]
q_list = Question.objects.filter(project=project)
for q in q_list:
if q.cid not in column_names:
#屏蔽F49题的添加--原数据库中存在
if 'F49' not in q.cid:
question_list.append(q.cid)
print question_list
for cid in question_list:
if 'other' in cid or 'T3' or 'visitor_numb' == cid:
sql = 'alter table survey_respondentdata add column \'%s\' varchar(1000);' % cid
c.execute(sql)
if con:
con.commit()
else:
sql = 'alter table survey_respondentdata add column \'%s\' integer;' % cid
c.execute(sql)
if con:
con.commit()
sql = 'alter table survey_respondentdata add column \'%s__open\' varchar(1000);' % cid
c.execute(sql)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def updateAll(self, event=None):
targets = []
for item in self.vtargets.get():
aux = item[item.find("(") + 1:item.find(")")]
targets.append(int(aux) if aux != "None" else item.split("/")[0])
self.updateSBAll()
self.lparalogs.config(text='')
paralogs = [
x for x in dbu.getParalogs(targets,
type="Order",
EC=self.confidence.get(),
Database=self.dbbox.get()) if x != None
]
aux = []
if (paralogs != []):
if (any(isinstance(i, list) for i in paralogs)):
paralogs = [item for sublist in paralogs for item in sublist]
for item in set(paralogs):
if item not in targets:
aux.append(item)
self.lparalogs.config(text="Paralogs: {" + ', '.join(sorted(aux)) +
"}")
def add_respondentdata_column(project_id):
project = Project.objects.get(id=project_id)
sql = 'select * from survey_respondentdata where id = 1'
try:
c, con = DbUtils.cursor()
c.execute(sql)
column_names = [d[0] for d in c.description]
q_list = Question.objects.filter(project=project)
for q in q_list:
if q.cid not in column_names:
#屏蔽F49题的添加--原数据库中存在
if 'F49' not in q.cid:
question_list.append(q.cid)
print question_list
for cid in question_list:
if 'other' in cid or 'T3' or 'visitor_numb' == cid:
sql = 'alter table survey_respondentdata add column \'%s\' varchar(1000);' % cid
c.execute(sql)
if con:
con.commit()
else:
sql = 'alter table survey_respondentdata add column \'%s\' integer;' % cid
c.execute(sql)
if con:
con.commit()
sql = 'alter table survey_respondentdata add column \'%s__open\' varchar(1000);' % cid
c.execute(sql)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def add_reportdata_column(project_id):
project = Project.objects.get(id=project_id)
sql = 'select * from mc_reportdata where id = 1'
try:
c, con = DbUtils.cursor()
c.execute(sql)
column_names = [d[0] for d in c.description]
cp_list = CheckPoint.objects.filter(project=project)
for cp in cp_list:
if cp.name not in column_names:
checkpoint_list.append(cp.name)
print checkpoint_list
for cid in checkpoint_list:
sql = 'alter table mc_reportdata add column \'%s\' float;' % cid
c.execute(sql)
if con:
con.commit()
finally:
if c:
c.close()
if con:
con.close()
def SingleScore(self, event=None, name=None):
# remove old widgets
if self.figure:
self.figure.destroy()
if self.toolbar:
self.toolbar.destroy()
for widget in self.fcSequence.winfo_children():
widget.destroy()
self.name = None
self.seq = None
try:
if (self.fcontainer.index(self.fcontainer.select()) == 1):
self.seq = self.sequence.get(1.0, END).rstrip(
) #rstrip remove quebras de linha #usar RE para verificar integridade da sequência
else:
tree, genes = [self.sactree, self.genes] if (
self.fcontainer.index(self.fcontainer.select())
== 0) else [self.seqtree, self.genesF]
if (len(tree.get_children()) > 0):
if (self.fcontOptions.index(
self.fcontOptions.select()) == 0):
self.name = str(
tree.item(tree.selection())['values'][1])
else:
self.name = name
self.fcontOptions.select(0)
self.updateGenes()
self.searchGenes()
self.seq = list(genes.loc[genes['gene'] == self.name,
'sequence'])[0]
except:
self.dialog("Please, select a sequence.")
upstream,downstream=[int(self.supstream.get().strip()), -1 if(self.sfgState.get()) else int(self.sdownstream.get().strip())] if(self.fcontainer.index(self.fcontainer.select())==0) else \
([int(self.upstream.get().strip()), -1 if(self.fgState.get()) else int(self.downstream.get().strip())] if(self.fcontainer.index(self.fcontainer.select())==2) else [len(self.seq),0])
if (self.seq is not None):
cs.canvasSequence(self.seq,
self.fcSequence,
width=15,
height=9,
targets=[self.targets, self.pmTargets],
outOfRange=self.foorState.get(),
normalized=self.normalizedState.get(),
threshold=float(self.threshold.get()),
upstream=upstream,
downstream=downstream,
margin=60)
paralogs = dbu.getParalogs(list(self.targets.keys()),
type="MotifID",
EC=self.confidence.get(),
Database=self.dbbox.get()) if (
self.paralogState.get()) else []
paralogs = list(self.flatten(paralogs))
self.master.update()
square = min(
self.master.winfo_height() -
(self.fimg.winfo_rooty() - self.master.winfo_rooty()),
self.master.winfo_width() -
(self.fimg.winfo_rootx() - self.master.winfo_rootx()))
plt = Utils.PlotAllScores(
self.seq,
outOfRange=self.foorState.get(),
tfs=self.pmTargets + self.pmNonTargets,
principalOnly=not (self.allLablesState.get()),
save=False,
normalized=self.normalizedState.get(),
paralogs=paralogs,
maximize=self.maximize,
name=self.name,
dpi=self.master.winfo_fpixels('1i'),
square=square)
canvas = FigureCanvasTkAgg(plt, self.fimg)
#self.toolbar = NavigationToolbar2Tk(canvas, self.fimg)
self.figure = canvas.get_tk_widget()
self.figure.pack(fill=None)
def searchGenes(self, event=None):
if (self.fcontainer.index(self.fcontainer.select()) == 2
and self.genesF is not None):
search = self.seqSearch.get().strip().upper()
for row in self.seqtree.get_children():
self.seqtree.delete(row)
k = 0
genesTemp = []
for i in range(len(self.genesF)):
name = self.genesF.iloc[i, 0]
sequence = self.genesF.iloc[i, 1]
up = self.genesF.iloc[i, 2]
down = self.genesF.iloc[i, 3]
check = self.genesF.iloc[i, 4]
if (search in name.upper() or search == ""):
self.seqtree.insert(
"",
i,
iid=str(k),
values=(('☑' if (check) else '☐'), name,
(sequence[:30] +
"..." if len(sequence) > 33 else sequence)))
genesTemp.append([name, sequence, up, down, check])
k += 1
self.genesTempF = pd.DataFrame(genesTemp,
columns=[
"gene", "sequence", "upstream",
"downstream", "check"
])
if (k >= 1):
self.seqtree.selection_set("0")
self.lgenesF['text'] = str(k) + " genes found"
total = len(self.genesTempF.loc[self.genesTempF['check'] == True])
newStatus = "☑" if (total == len(
self.genesTempF)) else ("☐" if total == 0 else "☒")
self.seqtree.heading('check', text=newStatus)
if (self.fcontainer.index(self.fcontainer.select()) == 0):
search = self.sacSearch.get().strip().upper()
targets = None if (self.regulator.get()
== 'None') else dbu.getTargets(
self.regulator.get().split("/")[1])
for row in self.sactree.get_children():
self.sactree.delete(row)
k = 0
genesTemp = []
for i in range(len(self.genes)):
name = self.genes.iloc[i, 0]
sequence = self.genes.iloc[i, 1]
up = self.genes.iloc[i, 2]
down = self.genes.iloc[i, 3]
check = self.genes.iloc[i, 4]
if(targets is None or name in targets['TargetSys'].tolist() or name in targets['TargetStd'].tolist()) \
and (search in name.upper() or search==""): # or search in sequence):
#self.sactree.insert("", i, iid=str(k), values=(name,(sequence[:30]+"..."+" ☐ ☑ ☒ " if len(sequence)>33 else sequence)))
self.sactree.insert(
"",
i,
iid=str(k),
values=(('☑' if (check) else '☐'), name,
(sequence[:30] +
"..." if len(sequence) > 33 else sequence)))
genesTemp.append([name, sequence, up, down, check])
k += 1
self.genesTemp = pd.DataFrame(genesTemp,
columns=[
"gene", "sequence", "upstream",
"downstream", "check"
])
if (k >= 1):
self.sactree.selection_set("0")
self.lgenes['text'] = str(k) + " genes found"
total = len(self.genesTemp.loc[self.genesTemp['check'] == True])
newStatus = "☑" if (total == len(
self.genesTemp)) else ("☐" if total == 0 else "☒")
self.sactree.heading('check', text=newStatus)
def super_report(request):
from django.db import connection
def get_dealer_id_list(dealer):
"""
得到指定dealer能看到的所有dealer的id
"""
id_list = []
if dealer.parent:
id_list.extend(mc.utils.get_parent_dealer_id_list(dealer.parent))
id_list.extend(mc.utils.get_sub_dealer_id_list(dealer))
return id_list
def float_format(afloat):
if afloat is None:
return ' '
return '%.2f' % afloat
#~ if not userpro.has_all_page_perm(request.user):
#~ raise Http404()
current_term = _term.get_cur_term()
dealer = _user.get_dealer_by_user(request.user)
if not dealer:
return HttpResponseRedirect('/')
total_cp_list = mc.get_total_cp_list()
dealer_id_list = get_dealer_id_list(dealer)
show_dealer_search_box = True
LEAF_DEALER_ID_SET = set(mc.get_leaf_dealer_id_for_bm())
#~ if user.role.name == 'customer_1': #全国但不显示经销商
#~ dealer_id_list = [id for id in dealer_id_list if id not in LEAF_DEALER_ID_SET]
#~ show_dealer_search_box = False
where_list = ['dealer_id in (%s)' % (','.join([str(id) for id in dealer_id_list]))]
term_list = mc.get_terms()
total_term_id_list = [t.id for t in term_list]
term_id_list = [t for t in request.POST.getlist('term_list') if t]
terms = None
if not term_id_list:
terms = request.REQUEST.get('terms')
if terms:
term_id_list = [int(id) for id in terms.split(',')]
if term_id_list:
if not terms:
terms = ','.join([str(id) for id in term_id_list])
where_list.append('(term_id in (%s) or term_id is null)' % (','.join([str(id) for id in term_id_list])))
fixed_head_list = ['dealer_id', 'dealer_name', 'term_id', 'term_name', 'Total']
head_name_list = [h for h in request.POST.getlist('head_name_list') if h]
if not head_name_list:
heads = request.REQUEST.get('heads')
if heads:
if heads == 'all':
head_list = total_cp_list
else:
tmp_name_set = set(heads.split(','))
head_list = [cp for cp in total_cp_list if cp.name in tmp_name_set]
else:
head_list = [cp for cp in total_cp_list if cp.has_child]
else:
tmp_name_set = set(head_name_list)
head_list = [cp for cp in total_cp_list if cp.name in tmp_name_set]
if head_list:
heads = ','.join([cp.name for cp in head_list])
total_head_list = fixed_head_list + [cp.name for cp in head_list]
else:
total_head_list = fixed_head_list
sql = 'select %s from mc_report r,mc_reportdata rd,mc_dealer d where %s and r.dealer_id=d.id and r.id=rd.id order by d.listorder,r.term_id;' % (','.join(total_head_list), ' and '.join(where_list))
sql = """select %s
from mc_report r
inner join mc_dealer d
on %s and r.dealer_id=d.id
left join mc_reportdata rd
on rd.id=r.id
order by d.listorder,r.term_id;""" % (','.join(total_head_list), ' and '.join(where_list))
#print sql
import DbUtils
try:
c, con = DbUtils.cursor()
c.execute(sql)
data_list = c.fetchall()
finally:
if c:
c.close()
if con:
con.close()
row_list = []
country_area_id_set = set([dealer.id for dealer in mc.get_regionals()])
root_dealer = mc.get_root_dealer()
country_area_id_set.add(root_dealer.id)
DEALER_PARENT_DICT = mc.get_dealer_parent_dict()
for data in data_list:
dealer_id = data[0]
term_id = data[2]
dealer_disp = unicode(data[1] or '')
leaf = ''
term_str = ''
if term_id:
if dealer_id in LEAF_DEALER_ID_SET:
leaf = ' leaf'
dealer_disp = '<a href="/report/detail_report/%s/%s/" target="_blank">%s</a>' % (dealer_id, term_id, dealer_disp)
td_dealer = '<td><input type="checkbox" name="dealer_%(dealer_id)s_%(term_id)s" id="dealer_%(dealer_id)s_%(term_id)s" class="cb_dealer"/></td><td class="dealer%(leaf)s nowrap">%(dealer_disp)s</td>' % vars()
term_str = '_%s' % term_id
else:
td_dealer = '<td></td><td class="dealer" nowrap>%(dealer_disp)s<span class="fold closed">+</span></td>' % vars()
td_term = '<td class="term" nowrap>%s</td>' % unicode(data[3] or '')
td_score_list = '</td><td>'.join(map(float_format, data[4:]))
dealer_parent = DEALER_PARENT_DICT.get(dealer_id, '')
if dealer_id in country_area_id_set:
display_class = ''
else:
display_class = ' hide'
row_list.append('<tr id="row_%(dealer_id)s%(term_str)s" class="sub_%(dealer_parent)s%(leaf)s%(display_class)s data">%(td_dealer)s%(td_term)s<td>%(td_score_list)s</td></tr>' % vars())
cp_group_list = mc.get_checkpoint_group_list()
#~ #判断是否显示雷达图按钮
#~ if not settings.USE_FUSION_CHART:
#~ c['show_radar'] = True
return locals()
