def count_dealer(sheet_index):
dt_bmw = add_dealertype('BMW')
dt_mini = add_dealertype('MINI')
file_name = u'系统权限明细 Q3 2014 账户&密码 -20140918.xlsx'
xls_file = os.path.join(DOC_ROOT, file_name)
print xls_file
excel = easyExcel(xls_file)
try:
for row in range(4, 58):
name = excel.getRangeVal(sheet_index, 'C%s' % row)
if not name:
continue
if isinstance(name, (int, float)):
xq_name = int(name)
xq_dealer_list = Dealer.objects.filter(name=xq_name)
if xq_dealer_list:
xq_dealer = xq_dealer_list[0]
bmw_count = Dealer.objects.filter(xq_parent=xq_dealer, dealertype=dt_bmw, has_child=False).count()
mini_count = Dealer.objects.filter(xq_parent=xq_dealer, dealertype=dt_mini, has_child=False).count()
count = Dealer.objects.filter(xq_parent=xq_dealer, has_child=False).count()
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row, bmw_count)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row, mini_count)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row, count)
else:
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row, 0)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row, 0)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row, 0)
else:
if name == 'North':
name_cn = u'北区'
if name == 'South':
name_cn = u'南区'
if name == 'East':
name_cn = u'东区'
if name == 'West':
name_cn = u'西区'
if name == 'SouthEast':
name_cn = u'东南区'
region_dealer = Dealer.objects.get(name_cn=name_cn)
bmw_count = Dealer.objects.filter(xq_parent__xq_parent=region_dealer, dealertype=dt_bmw, has_child=False).count()
mini_count = Dealer.objects.filter(xq_parent__xq_parent=region_dealer, dealertype=dt_mini, has_child=False).count()
count = Dealer.objects.filter(xq_parent__xq_parent=region_dealer, has_child=False).count()
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row, bmw_count)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row, mini_count)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row, count)
finally:
excel.save()
excel.close()
def gen_region_hnf_report(term_id):
source_file_name = u'hnf/region_hnf.xls'
source_file_name = os.path.join(settings.RESOURCES_ROOT, source_file_name)
excel = easyExcel(source_file_name)
term_list = map(copy.copy, _term.get_all_terms().filter(id__lte=term_id))
#除去品牌的report,采用全国的数据
reports = Report.objects.filter(term__id=term_id, dealer__has_child=True).exclude(score=None).exclude(dealer__level=7)
kind_dict = constant.data_compare_kind_dict
kind_dict = dict(zip(kind_dict.values(), kind_dict.keys()))
try:
sheet = 'sheet1'
for index, report in enumerate(reports):
term = report.term
dealer = report.dealer
dealertype = report.dealertype
project = report.project
paper_type = report.paper_type
level = 7 #全国没有对比，做为品牌对比
if report.dealer.level != 0:
level = report.dealer.level
kind = kind_dict[level]
item_name = constant.data_compare_dict[kind]
item_name_en = constant.data_compare_en_dict[kind]
filename = u'%s_%s_%s_%s_历史现在未来报告.xls' % (item_name, term.name, dealer.name_cn, dealertype.name_cn)
target_file_name = os.path.join(settings.MEDIA_ROOT, 'hnf_report', filename)
excel.setSheetName(sheet, u'%s历史现在未来报告' % item_name)
sheet = u'%s历史现在未来报告' % item_name
ans = u'2012年%s售后服务评估结果 %s After-sales Service Evaluation Result' % (item_name, item_name_en)
excel.setRangeVal(sheet, 'A1', ans)
data_sheet = 'datasheet'
ans = u'%s数据对比和历史/现在/未来 %s Data Comparison And History/Current/Future' % (item_name, item_name_en)
excel.setRangeVal(data_sheet, 'A1', ans)
ans = u'%s名称：' % item_name
excel.setRangeVal(data_sheet, 'A2', ans)
excel.setRangeVal(data_sheet, 'A3', item_name_en)
ans = dealer.name_cn
if level == 0:
ans = dealertype.name_cn
excel.setRangeVal(data_sheet, 'B2', ans)
ans = dealer.name_en
if level == 0:
ans = dealertype.name_en
excel.setRangeVal(data_sheet, 'B3', ans)
excel.setRangeVal(data_sheet, 'D2', u'本期总得分（%s）：' % term.name_cn)
excel.setRangeVal(data_sheet, 'D3', u'Overall score of %s wave' % term.name_en)
excel.setRangeVal(data_sheet, 'E2', round(report.score, 1))
insert_paper_data(excel, data_sheet, term_list, project, dealer, dealertype, paper_type)
excel.save(target_file_name)
finally:
excel.close()
def write_data(term, showTran):
tmpl_file = os.path.join(settings.RESOURCES_ROOT, 'temp.xls')
needs = 'need'
if showTran:
needs = 'all'
xls_file = os.path.join(settings.RESOURCES_ROOT,
u'%s_tran_%s.xls' % (term.name_cn, needs))
shutil.copy(tmpl_file, xls_file)
xls_file = xls_file.replace('/', '\\')
excel = easyExcel(xls_file)
BMW_cp = CheckPoint.objects.filter(project__id=2, has_child=False)
MINI_cp = CheckPoint.objects.filter(project__id=3, has_child=False)
COM_cp = CheckPoint.objects.filter(project__id=4, has_child=False)
startline = 3
sh2_line = 3
BMW_pds = PaperDiff.objects.filter(
final_paper__dealer__dealertype__id=1,
final_paper__term=term).exclude(status=3)
BMW_papers = []
for pd in BMW_pds:
BMW_papers.append(pd.final_paper)
MINI_pds = PaperDiff.objects.filter(
final_paper__dealer__dealertype__id=5,
final_paper__term=term).exclude(status=3)
MINI_papers = []
for pd in MINI_pds:
MINI_papers.append(pd.final_paper)
COM_pds = Paper.objects.filter(project__id=4,
term=term,
paper_type='GFK',
status=enums.PAPER_STATUS_FINISH,
dealer__dealertype__id__in=[2, 3, 4])
COM_papers = COM_pds
try:
for paper in BMW_papers:
startline, sh2_line = gen_excel_sheet1(paper, excel, startline,
sh2_line, BMW_cp, showTran)
for paper in MINI_papers:
startline, sh2_line = gen_excel_sheet1(paper, excel, startline,
sh2_line, MINI_cp, showTran)
for paper in COM_papers:
startline, sh2_line = gen_excel_sheet1(paper, excel, startline,
sh2_line, COM_cp, showTran)
finally:
excel.save()
excel.close()
return xls_file
def gen_dealer_hnf_report(term_id):
source_file_name = u'hnf/dealer_hnf.xls'
source_file_name = os.path.join(settings.RESOURCES_ROOT, source_file_name)
dealertypes = _dealer.get_dealer_types()
term_list = map(copy.copy, _term.get_all_terms().filter(id__lte=term_id))
excel = easyExcel(source_file_name)
try:
sheet = 'sheet1'
for dt in dealertypes:
project_id = constant.dealertype_id_to_project_id(dt.id)
project = _project.get_project_by_id(project_id)
papers = _paper.get_papers_by_term_project_Dealer(
term_id, project_id, dt.id)
for index, paper in enumerate(papers):
term = paper.term
dealer = paper.dealer
filename = u'%s_%s_%s_历史现在未来报告.xls' % (
term.name, dealer.name_cn, dealer.name)
target_file_name = os.path.join(settings.MEDIA_ROOT,
'hnf_report', filename)
if paper.visit_end and paper.visit_begin:
paper.visit_minutes = (paper.visit_end -
paper.visit_begin).seconds / 60
else:
paper.visit_minutes = 0
excel.setSheetName(sheet, u'%s历史现在未来报告' % dealer.name_cn)
sheet = u'%s历史现在未来报告' % dealer.name_cn
data_sheet = 'datasheet'
excel.setRangeVal(data_sheet, 'B2', dealer.name_cn)
excel.setRangeVal(data_sheet, 'B3', dealer.name_en)
excel.setRangeVal(data_sheet, 'E2', dealer.name)
excel.setRangeVal(data_sheet, 'H2', dealer.city_cn)
excel.setRangeVal(data_sheet, 'H3', dealer.city_en)
excel.setRangeVal(data_sheet, 'K2', dealer.province_cn)
excel.setRangeVal(data_sheet, 'K3', dealer.province_en)
excel.setRangeVal(data_sheet, 'N2', dealer.region.name_cn)
excel.setRangeVal(data_sheet, 'N3', dealer.region.name_en)
excel.setRangeVal(data_sheet, 'P2',
u'本期总得分（%s）：' % term.name_cn)
excel.setRangeVal(data_sheet, 'P3',
u'Overall score of %s wave' % term.name_en)
excel.setRangeVal(data_sheet, 'Q2', round(paper.score, 1))
excel.setRangeVal(data_sheet, 'T2', paper.visit_begin)
excel.setRangeVal(data_sheet, 'W2', paper.visit_end)
excel.setRangeVal(data_sheet, 'Z2', paper.visit_minutes)
insert_paper_data(excel, data_sheet, term_list, project,
dealer, dealer.dealertype, paper.paper_type)
excel.save(target_file_name)
finally:
excel.close()
def gen_dealer_hnf_report(term_id):
source_file_name = u'hnf/dealer_hnf.xls'
source_file_name = os.path.join(settings.RESOURCES_ROOT, source_file_name)
dealertypes = _dealer.get_dealer_types()
term_list = map(copy.copy, _term.get_all_terms().filter(id__lte=term_id))
excel = easyExcel(source_file_name)
try:
sheet = 'sheet1'
for dt in dealertypes:
project_id = constant.dealertype_id_to_project_id(dt.id)
project = _project.get_project_by_id(project_id)
papers = _paper.get_papers_by_term_project_Dealer(term_id, project_id, dt.id)
for index, paper in enumerate(papers):
term = paper.term
dealer = paper.dealer
filename = u'%s_%s_%s_历史现在未来报告.xls' % (term.name, dealer.name_cn, dealer.name)
target_file_name = os.path.join(settings.MEDIA_ROOT, 'hnf_report', filename)
if paper.visit_end and paper.visit_begin:
paper.visit_minutes = (paper.visit_end - paper.visit_begin).seconds / 60
else:
paper.visit_minutes = 0
excel.setSheetName(sheet, u'%s历史现在未来报告' % dealer.name_cn)
sheet = u'%s历史现在未来报告' % dealer.name_cn
data_sheet = 'datasheet'
excel.setRangeVal(data_sheet, 'B2', dealer.name_cn)
excel.setRangeVal(data_sheet, 'B3', dealer.name_en)
excel.setRangeVal(data_sheet, 'E2', dealer.name)
excel.setRangeVal(data_sheet, 'H2', dealer.city_cn)
excel.setRangeVal(data_sheet, 'H3', dealer.city_en)
excel.setRangeVal(data_sheet, 'K2', dealer.province_cn)
excel.setRangeVal(data_sheet, 'K3', dealer.province_en)
excel.setRangeVal(data_sheet, 'N2', dealer.region.name_cn)
excel.setRangeVal(data_sheet, 'N3', dealer.region.name_en)
excel.setRangeVal(data_sheet, 'P2', u'本期总得分（%s）：' % term.name_cn)
excel.setRangeVal(data_sheet, 'P3', u'Overall score of %s wave' % term.name_en)
excel.setRangeVal(data_sheet, 'Q2', round(paper.score, 1))
excel.setRangeVal(data_sheet, 'T2', paper.visit_begin)
excel.setRangeVal(data_sheet, 'W2', paper.visit_end)
excel.setRangeVal(data_sheet, 'Z2', paper.visit_minutes)
insert_paper_data(excel, data_sheet, term_list, project, dealer, dealer.dealertype, paper.paper_type)
excel.save(target_file_name)
finally:
excel.close()
def insert_questionqa(sheet_index):
dt_bmw = add_dealertype('BMW')
dt_mini = add_dealertype('MINI')
file_name = u'系统权限明细 Q1 2014 账户&密码 -to 爱调研.xlsx'
xls_file = os.path.join(DOC_ROOT, file_name)
excel = easyExcel(xls_file)
try:
for row in range(4, 46):
name = excel.getRangeVal(sheet_index, 'C%s' % row)
if not name:
continue
if isinstance(name,(int,float)):
xq_name = int(name)
xq_dealer_list = Dealer.objects.filter(name = xq_name)
if xq_dealer_list:
xq_dealer = xq_dealer_list[0]
bmw_count = Dealer.objects.filter(xq_parent = xq_dealer,dealertype = dt_bmw,has_child = False).count()
mini_count = Dealer.objects.filter(xq_parent = xq_dealer,dealertype = dt_mini,has_child = False).count()
count = Dealer.objects.filter(xq_parent = xq_dealer,has_child = False).count()
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row, bmw_count)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row, mini_count)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row, count)
else:
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row, 0)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row, 0)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row, 0)
else:
region_dealer = Dealer.objects.get(name_en = name)
bmw_count = Dealer.objects.filter(xq_parent__xq_parent = region_dealer,dealertype = dt_bmw,has_child = False).count()
mini_count = Dealer.objects.filter(xq_parent__xq_parent = region_dealer,dealertype = dt_mini,has_child = False).count()
count = Dealer.objects.filter(xq_parent__xq_parent = region_dealer,has_child = False).count()
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row, bmw_count)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row, mini_count)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row, count)
finally:
excel.save()
excel.close()
def write_data(term, showTran):
tmpl_file = os.path.join(settings.RESOURCES_ROOT, 'temp.xls')
needs = 'need'
if showTran:
needs = 'all'
xls_file = os.path.join(settings.RESOURCES_ROOT, u'%s_tran_%s.xls' % (term.name_cn, needs))
shutil.copy(tmpl_file, xls_file)
xls_file = xls_file.replace('/', '\\')
excel = easyExcel(xls_file)
BMW_cp = CheckPoint.objects.filter(project__id=2, has_child=False)
MINI_cp = CheckPoint.objects.filter(project__id=3, has_child=False)
COM_cp = CheckPoint.objects.filter(project__id=4, has_child=False)
startline = 3
sh2_line = 3
BMW_pds = PaperDiff.objects.filter(final_paper__dealer__dealertype__id=1, final_paper__term=term).exclude(status=3)
BMW_papers = []
for pd in BMW_pds:
BMW_papers.append(pd.final_paper)
MINI_pds = PaperDiff.objects.filter(final_paper__dealer__dealertype__id=5, final_paper__term=term).exclude(status=3)
MINI_papers = []
for pd in MINI_pds:
MINI_papers.append(pd.final_paper)
COM_pds = Paper.objects.filter(project__id=4, term=term, paper_type='GFK', status=enums.PAPER_STATUS_FINISH, dealer__dealertype__id__in=[2, 3, 4])
COM_papers = COM_pds
try:
for paper in BMW_papers:
startline, sh2_line = gen_excel_sheet1(paper, excel, startline, sh2_line, BMW_cp, showTran)
for paper in MINI_papers:
startline, sh2_line = gen_excel_sheet1(paper, excel, startline, sh2_line, MINI_cp, showTran)
for paper in COM_papers:
startline, sh2_line = gen_excel_sheet1(paper, excel, startline, sh2_line, COM_cp, showTran)
finally:
excel.save()
excel.close()
return xls_file
def insert_questionqa(sheet_index):
dt_bmw = add_dealertype('BMW')
dt_mini = add_dealertype('MINI')
file_name = u'系统权限明细 Q1 2014 账户&密码 -to 爱调研.xlsx'
xls_file = os.path.join(DOC_ROOT, file_name)
excel = easyExcel(xls_file)
try:
for row in range(4, 46):
name = excel.getRangeVal(sheet_index, 'C%s' % row)
if not name:
continue
if isinstance(name, (int, float)):
xq_name = int(name)
xq_dealer_list = Dealer.objects.filter(name=xq_name)
if xq_dealer_list:
xq_dealer = xq_dealer_list[0]
bmw_count = Dealer.objects.filter(xq_parent=xq_dealer,
dealertype=dt_bmw,
has_child=False).count()
mini_count = Dealer.objects.filter(
xq_parent=xq_dealer,
dealertype=dt_mini,
has_child=False).count()
count = Dealer.objects.filter(xq_parent=xq_dealer,
has_child=False).count()
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row,
bmw_count)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row,
mini_count)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row,
count)
else:
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row, 0)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row, 0)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row, 0)
else:
region_dealer = Dealer.objects.get(name_en=name)
bmw_count = Dealer.objects.filter(
xq_parent__xq_parent=region_dealer,
dealertype=dt_bmw,
has_child=False).count()
mini_count = Dealer.objects.filter(
xq_parent__xq_parent=region_dealer,
dealertype=dt_mini,
has_child=False).count()
count = Dealer.objects.filter(
xq_parent__xq_parent=region_dealer,
has_child=False).count()
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row,
bmw_count)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row,
mini_count)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row, count)
finally:
excel.save()
excel.close()
def insert_questionqa(brand, sheet_index):
file_name = u'2015 APQ Questionnaire_20150108-final.xlsx'
xls_file = os.path.join(DOC_ROOT, file_name)
wb = easyExcel(xls_file)
ascii_digital = 64
part_name = ''
part_name_en = ''
part_letter = ''
part_parent = ''
q_parent = ''
q_number = ''
q_color = QuestionQAColor.objects.get(name=u'流程质量部分')
green_list = [
'3', '7a', '7b', '7c', '12a', '12b', '12c', '12d', '12e', '12f', '12g',
'12h', '12i', '13a', '13b', '13c', '13d', '21', '34a', '34b', '34c',
'44a', '44b', '44c', '47', '49', '53', '55', '56', '60', '61', '62',
'63', '59'
]
for col in range(4, 299):
en_sheet_index = sheet_index - 1
row_a = wb.getRangeVal(sheet_index, 'A%s' % col)
row_b = wb.getRangeVal(sheet_index, 'B%s' % col)
row_c = wb.getRangeVal(sheet_index, 'C%s' % col)
row_d = wb.getRangeVal(sheet_index, 'D%s' % col)
row_e = wb.getRangeVal(sheet_index, 'E%s' % col)
row_a_en = wb.getRangeVal(en_sheet_index, 'A%s' % col)
row_b_en = wb.getRangeVal(en_sheet_index, 'B%s' % col)
row_c_en = wb.getRangeVal(en_sheet_index, 'C%s' % col)
row_e_en = wb.getRangeVal(en_sheet_index, 'E%s' % col)
if row_b:
row_b.strip()
if row_c:
row_c.strip()
if row_d != None:
try:
row_d = str(int(row_d)).strip()
except:
row_d = str(row_d).strip()
if row_e:
row_e.strip()
row_e = row_e.replace(u'\n', u'<br />')
if row_b_en:
row_b_en.strip()
if row_c_en:
row_c_en.strip()
if row_e_en:
row_e_en.strip()
row_e_en = row_e_en.replace(u'\n', u'<br />')
if row_a != None:
if row_b == None:
part_name = row_a
if hasattr(part_name, 'strip'):
part_name = part_name.strip()
part_name_en = row_a_en
if hasattr(part_name_en, 'strip'):
part_name_en = part_name_en.strip()
ascii_digital += 1
part_letter = chr(ascii_digital)
print brand, part_letter, part_name
part_parent, create = QuestionQANew.objects.get_or_create(
brand=brand,
part=part_letter,
name_cn=part_name,
name_en=part_name_en,
has_child=True)
if row_b != None:
try:
q_number = str(int(row_a)).strip()
except:
q_number = str(row_a).strip()
print '\t', part_letter, q_number
print '\t\t', row_c, row_d
if q_number in green_list:
q_color = QuestionQAColor.objects.get(name=u'客户对待部分')
else:
q_color = QuestionQAColor.objects.get(name=u'流程质量部分')
q_parent, create = QuestionQANew.objects.get_or_create(
number=q_number,
question_cn=row_b,
question_en=row_b_en,
desc_cn=row_e,
desc_en=row_e_en,
color=q_color,
parent=part_parent,
has_child=True)
q_parent.save()
QuestionQANew.objects.get_or_create(option_cn=row_c,
option_en=row_c_en,
point=row_d,
parent=q_parent)
else:
if row_b == None:
print '\t\t', row_c, row_d
QuestionQANew.objects.get_or_create(option_cn=row_c,
option_en=row_c_en,
point=row_d,
parent=q_parent)
wb.save()
wb.close()
def make_excel(term_id):
term = _term.get_term_by_id(term_id)
target_file = os.path.join(settings.RESOURCES_ROOT, u'data/%s_option_data.xls' % term.name_cn)
#默认保存2007的格式，需手动转换
projects = _project.get_2012_projects()
excel = easyExcel()
try:
for index, project in enumerate(projects):
sheetname = project.name
excel.setSheetName('sheet%d' % (index + 1), sheetname)
gen_col_dict(project, excel, sheetname)
papers = _paper.get_papers(project=project, paper_type=enums.FW_PAPER_TYPE, term=term)
for i, paper in enumerate(papers):
print paper.id
ans_dict = get_respondentdata_dict_by_paper(paper)
dealername = ''
dealername_cn = ''
dealer = paper.dealer
if dealer:
dealername = paper.dealer.name
dealername_cn = paper.dealer.name_cn
# print paper.id, paper.survey_code, dealername, dealername_cn
cellrow = i + 2
excel.setRangeVal(sheetname, '%s%s' % (get_char(0), cellrow), dealername)
excel.setRangeVal(sheetname, '%s%s' % (get_char(1), cellrow), dealername_cn)
excel.setRangeVal(sheetname, '%s%s' % (get_char(2), cellrow), paper.id)
excel.setRangeVal(sheetname, '%s%s' % (get_char(3), cellrow), ans_dict['customer_name'])#
excel.setRangeVal(sheetname, '%s%s' % (get_char(4), cellrow), ans_dict['customer_address'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(5), cellrow), paper.visitor_num)
excel.setRangeVal(sheetname, '%s%s' % (get_char(6), cellrow), ans_dict['appraiser_code'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(7), cellrow), '%.2f' % paper.score)
excel.setRangeVal(sheetname, '%s%s' % (get_char(8), cellrow), ans_dict['booking_date'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(9), cellrow), ans_dict['visit_date'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(10), cellrow), ans_dict['visit_begin_time'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(11), cellrow), ans_dict['visit_end_time'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(12), cellrow), ans_dict['estimate_finish_date'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(13), cellrow), ans_dict['estimate_finish_time'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(14), cellrow), ans_dict['repare_finish_date'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(15), cellrow), ans_dict['repare_finish_time'])
base_ans = ans_dict['service_type']
alt = Alternative.objects.get(id=base_ans)
excel.setRangeVal(sheetname, '%s%s' % (get_char(16), cellrow), alt.title)
base_ans = ans_dict['car_type']
alt = Alternative.objects.get(id=base_ans)
cartypes = alt.title
car_type__open = ans_dict['car_type__open']
if car_type__open:
cartypes = '%s %s' % (cartypes, car_type__open)
excel.setRangeVal(sheetname, '%s%s' % (get_char(17), cellrow), cartypes)
base_ans = ans_dict['at_mt']
alt = Alternative.objects.get(id=base_ans)
excel.setRangeVal(sheetname, '%s%s' % (get_char(18), cellrow), alt.title)
excel.setRangeVal(sheetname, '%s%s' % (get_char(19), cellrow), ans_dict['consultant_name'])
base_ans = ans_dict['consultant_sex']
alt = Alternative.objects.get(id=base_ans)
excel.setRangeVal(sheetname, '%s%s' % (get_char(20), cellrow), alt.title)
qc1 = PaperAudit.objects.filter(paper=paper, new_status=enums.FW_PAPER_STATUS_WAIT_AUDIT_2)
if qc1:
excel.setRangeVal(sheetname, '%s%s' % (get_char(21), cellrow), qc1[0].user.username)
qc2 = PaperAudit.objects.filter(paper=paper, new_status=enums.FW_PAPER_STATUS_WAIT_AUDIT_3)
if qc2:
excel.setRangeVal(sheetname, '%s%s' % (get_char(22), cellrow), qc2[0].user.username)
qc3 = PaperAudit.objects.filter(paper=paper, new_status=enums.FW_PAPER_STATUS_WAIT_AUDIT_4)
if qc3:
excel.setRangeVal(sheetname, '%s%s' % (get_char(23), cellrow), qc3[0].user.username)
for key in COL_DICT.keys():
if '_' not in key:
qid = key
qcid = Question.objects.get(id=qid).cid
base_ans = ans_dict[qcid]
if base_ans is None:
continue
#print paper.id ,qid, base_ans
alt = Alternative.objects.get(id=base_ans)
cell = '%s%s' % (COL_DICT[qid], i + 2)
if u'是' in alt.title:
excel.setRangeVal(sheetname, cell, u'是')
else:
excel.setRangeVal(sheetname, cell, alt.title)
continue
keys = key.split('_')
qid = keys[0]
ans_cid = keys[1]
# if 'A' in ans_cid:
# if '1' not in ans_cid:
# continue
qcid = Question.objects.get(id=qid).cid
qkey = '%s__open' % qcid
base_ans = ans_dict[qcid]
if base_ans is None:
continue
#print paper.id ,qid, base_ans
alt = Alternative.objects.get(id=base_ans)
# cell = '%s%s' % (COL_DICT[key], i + 2)
# if 'A' in ans_cid:
# if u'是' in alt.title:
# excel.setRangeVal(sheetname, cell, u'是')
# else:
# excel.setRangeVal(sheetname, cell, alt.title)
if u'否' not in alt.title:
continue
#print paper.id, ans_dict
ans = ans_dict[qkey]
ans_list = []
if not ans:
continue
if '^-^' in ans:
ans_list = ans.split('^-^')
if ans_list:
for a in ans_list:
cell = '%s%s' % (COL_DICT[key], i + 2)
try:
alt_cid = int(a)
if int(ans_cid) == alt_cid:
excel.setRangeVal(sheetname, cell, alt_cid)
except:
if ans_cid == 'open':
excel.setRangeVal(sheetname, cell, a)
excel.save(target_file)
finally:
excel.close()
return target_file
def count_dealer(sheet_index):
dt_bmw = add_dealertype('BMW')
dt_mini = add_dealertype('MINI')
file_name = u'系统权限明细 Q3 2014 账户&密码 -20140918.xlsx'
xls_file = os.path.join(DOC_ROOT, file_name)
print xls_file
excel = easyExcel(xls_file)
try:
for row in range(4, 58):
name = excel.getRangeVal(sheet_index, 'C%s' % row)
if not name:
continue
if isinstance(name, (int, float)):
xq_name = int(name)
xq_dealer_list = Dealer.objects.filter(name=xq_name)
if xq_dealer_list:
xq_dealer = xq_dealer_list[0]
bmw_count = Dealer.objects.filter(xq_parent=xq_dealer,
dealertype=dt_bmw,
has_child=False).count()
mini_count = Dealer.objects.filter(
xq_parent=xq_dealer,
dealertype=dt_mini,
has_child=False).count()
count = Dealer.objects.filter(xq_parent=xq_dealer,
has_child=False).count()
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row,
bmw_count)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row,
mini_count)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row,
count)
else:
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row, 0)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row, 0)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row, 0)
else:
if name == 'North':
name_cn = u'北区'
if name == 'South':
name_cn = u'南区'
if name == 'East':
name_cn = u'东区'
if name == 'West':
name_cn = u'西区'
if name == 'SouthEast':
name_cn = u'东南区'
region_dealer = Dealer.objects.get(name_cn=name_cn)
bmw_count = Dealer.objects.filter(
xq_parent__xq_parent=region_dealer,
dealertype=dt_bmw,
has_child=False).count()
mini_count = Dealer.objects.filter(
xq_parent__xq_parent=region_dealer,
dealertype=dt_mini,
has_child=False).count()
count = Dealer.objects.filter(
xq_parent__xq_parent=region_dealer,
has_child=False).count()
excel.setRangeValBySheetIndex(sheet_index, 'H%s' % row,
bmw_count)
excel.setRangeValBySheetIndex(sheet_index, 'I%s' % row,
mini_count)
excel.setRangeValBySheetIndex(sheet_index, 'J%s' % row, count)
finally:
excel.save()
excel.close()
def write_data(term, template=u'big_data/2012年第1期数据大表_template.xls'):
#tmpl_file = os.path.join(settings.SITE_ROOT, u'file/big_data/2012数据大表_Q1_0404.xls')
tmpl_file = os.path.join(settings.RESOURCES_ROOT, template)
xls_file = os.path.join(settings.RESOURCES_ROOT,
u'big_data/%s_数据大表.xls' % term.name_cn)
shutil.copy(tmpl_file, xls_file)
xls_file = xls_file.replace('/', '\\')
excel = easyExcel(xls_file)
BMW_startline = 5
MINI_startline = 5
# BMW_daqu_line_dict = {'East':5, 'North':66, 'South':112, 'West':152}
# MINI_daqu_line_dict = {'East':5, 'North':10, 'South':13, 'West':16}
daqu_dealers = Dealer.objects.filter(level=1).order_by(
'id') # East, North, South, West
try:
for daqu in daqu_dealers:
BMW_dealers = term.dealers.filter(dealertype__id=1,
level=3,
parent__parent=daqu)
print 'BMW', daqu.name, 'startline:', BMW_startline, 'count:', len(
BMW_dealers)
for dealer in BMW_dealers:
BMW_startline = gen_excel_sheet1(dealer, excel, BMW_startline,
daqu, 2, BMW_q_dict, 'GFK',
term)
BMW_startline += 1
MINI_dealers = term.dealers.filter(dealertype__id=5,
level=3,
parent__parent=daqu)
print 'MINI', daqu.name, 'startline:', MINI_startline, 'count:', len(
MINI_dealers)
for dealer in MINI_dealers:
MINI_startline = gen_excel_sheet1(dealer, excel,
MINI_startline, daqu, 3,
MINI_q_dict, 'GFK', term)
MINI_startline += 1
audi_dealers = term.dealers.filter(dealertype__id=3,
level=3).order_by('name')
audi_startline = 5
print 'AUDI startline:', audi_startline, 'count:', len(audi_dealers)
for dealer in audi_dealers:
papers = Paper.objects.filter(dealer=dealer,
project__id=4,
paper_type='GFK',
term=term,
status=100)
# if papers and len(papers) > 0:
# dealers = []
# dealers.extend(term.dealers.all())
# dealers.append(dealer)
# term.dealers = dealers
# term.save()
audi_startline = gen_excel_sheet_com(dealer, excel, audi_startline,
4, Com_q_dict, 'GFK', term)
lexus_dealers = term.dealers.filter(dealertype__id=4,
level=3).order_by('name')
lexus_startline = audi_startline + 1
print 'LEXUS startline:', lexus_startline, 'count:', len(lexus_dealers)
for dealer in lexus_dealers:
papers = Paper.objects.filter(dealer=dealer,
project__id=4,
paper_type='GFK',
term=term,
status=100)
# if papers and len(papers) > 0:
# dealers = []
# dealers.extend(term.dealers.all())
# dealers.append(dealer)
# term.dealers = dealers
# term.save()
lexus_startline = gen_excel_sheet_com(dealer, excel,
lexus_startline, 4,
Com_q_dict, 'GFK', term)
benz_dealers = term.dealers.filter(dealertype__id=2,
level=3).order_by('name')
benz_startline = lexus_startline + 1
print 'BENZ startline:', benz_startline, 'count:', len(benz_dealers)
for dealer in benz_dealers:
papers = Paper.objects.filter(dealer=dealer,
project__id=4,
paper_type='GFK',
term=term,
status=100)
# if papers and len(papers) > 0:
# dealers = []
# dealers.extend(term.dealers.all())
# dealers.append(dealer)
# term.dealers = dealers
# term.save()
benz_startline = gen_excel_sheet_com(dealer, excel, benz_startline,
4, Com_q_dict, 'GFK', term)
finally:
excel.save()
excel.close()
return xls_file
def gen_region_hnf_report(term_id):
source_file_name = u'hnf/region_hnf.xls'
source_file_name = os.path.join(settings.RESOURCES_ROOT, source_file_name)
excel = easyExcel(source_file_name)
term_list = map(copy.copy, _term.get_all_terms().filter(id__lte=term_id))
#除去品牌的report,采用全国的数据
reports = Report.objects.filter(
term__id=term_id,
dealer__has_child=True).exclude(score=None).exclude(dealer__level=7)
kind_dict = constant.data_compare_kind_dict
kind_dict = dict(zip(kind_dict.values(), kind_dict.keys()))
try:
sheet = 'sheet1'
for index, report in enumerate(reports):
term = report.term
dealer = report.dealer
dealertype = report.dealertype
project = report.project
paper_type = report.paper_type
level = 7 #全国没有对比，做为品牌对比
if report.dealer.level != 0:
level = report.dealer.level
kind = kind_dict[level]
item_name = constant.data_compare_dict[kind]
item_name_en = constant.data_compare_en_dict[kind]
filename = u'%s_%s_%s_%s_历史现在未来报告.xls' % (
item_name, term.name, dealer.name_cn, dealertype.name_cn)
target_file_name = os.path.join(settings.MEDIA_ROOT, 'hnf_report',
filename)
excel.setSheetName(sheet, u'%s历史现在未来报告' % item_name)
sheet = u'%s历史现在未来报告' % item_name
ans = u'2012年%s售后服务评估结果 %s After-sales Service Evaluation Result' % (
item_name, item_name_en)
excel.setRangeVal(sheet, 'A1', ans)
data_sheet = 'datasheet'
ans = u'%s数据对比和历史/现在/未来 %s Data Comparison And History/Current/Future' % (
item_name, item_name_en)
excel.setRangeVal(data_sheet, 'A1', ans)
ans = u'%s名称：' % item_name
excel.setRangeVal(data_sheet, 'A2', ans)
excel.setRangeVal(data_sheet, 'A3', item_name_en)
ans = dealer.name_cn
if level == 0:
ans = dealertype.name_cn
excel.setRangeVal(data_sheet, 'B2', ans)
ans = dealer.name_en
if level == 0:
ans = dealertype.name_en
excel.setRangeVal(data_sheet, 'B3', ans)
excel.setRangeVal(data_sheet, 'D2', u'本期总得分（%s）：' % term.name_cn)
excel.setRangeVal(data_sheet, 'D3',
u'Overall score of %s wave' % term.name_en)
excel.setRangeVal(data_sheet, 'E2', round(report.score, 1))
insert_paper_data(excel, data_sheet, term_list, project, dealer,
dealertype, paper_type)
excel.save(target_file_name)
finally:
excel.close()
def write_data(term, template=u'big_data/2012年第1期数据大表_template.xls'):
#tmpl_file = os.path.join(settings.SITE_ROOT, u'file/big_data/2012数据大表_Q1_0404.xls')
tmpl_file = os.path.join(settings.RESOURCES_ROOT, template)
xls_file = os.path.join(settings.RESOURCES_ROOT, u'big_data/%s_数据大表.xls' % term.name_cn)
shutil.copy(tmpl_file, xls_file)
xls_file = xls_file.replace('/', '\\')
excel = easyExcel(xls_file)
BMW_startline = 5
MINI_startline = 5
# BMW_daqu_line_dict = {'East':5, 'North':66, 'South':112, 'West':152}
# MINI_daqu_line_dict = {'East':5, 'North':10, 'South':13, 'West':16}
daqu_dealers = Dealer.objects.filter(level=1).order_by('id') # East, North, South, West
try:
for daqu in daqu_dealers:
BMW_dealers = term.dealers.filter(dealertype__id=1, level=3, parent__parent=daqu)
print 'BMW', daqu.name, 'startline:', BMW_startline, 'count:', len(BMW_dealers)
for dealer in BMW_dealers:
BMW_startline = gen_excel_sheet1(dealer, excel, BMW_startline, daqu, 2, BMW_q_dict, 'GFK', term)
BMW_startline += 1
MINI_dealers = term.dealers.filter(dealertype__id=5, level=3, parent__parent=daqu)
print 'MINI', daqu.name, 'startline:', MINI_startline, 'count:', len(MINI_dealers)
for dealer in MINI_dealers:
MINI_startline = gen_excel_sheet1(dealer, excel, MINI_startline, daqu, 3, MINI_q_dict, 'GFK', term)
MINI_startline += 1
audi_dealers = term.dealers.filter(dealertype__id=3, level=3).order_by('name')
audi_startline = 5
print 'AUDI startline:', audi_startline, 'count:', len(audi_dealers)
for dealer in audi_dealers:
papers = Paper.objects.filter(dealer=dealer, project__id=4, paper_type='GFK', term=term, status=100)
# if papers and len(papers) > 0:
# dealers = []
# dealers.extend(term.dealers.all())
# dealers.append(dealer)
# term.dealers = dealers
# term.save()
audi_startline = gen_excel_sheet_com(dealer, excel, audi_startline, 4, Com_q_dict, 'GFK', term)
lexus_dealers = term.dealers.filter(dealertype__id=4, level=3).order_by('name')
lexus_startline = audi_startline + 1
print 'LEXUS startline:', lexus_startline, 'count:', len(lexus_dealers)
for dealer in lexus_dealers:
papers = Paper.objects.filter(dealer=dealer, project__id=4, paper_type='GFK', term=term, status=100)
# if papers and len(papers) > 0:
# dealers = []
# dealers.extend(term.dealers.all())
# dealers.append(dealer)
# term.dealers = dealers
# term.save()
lexus_startline = gen_excel_sheet_com(dealer, excel, lexus_startline, 4, Com_q_dict, 'GFK', term)
benz_dealers = term.dealers.filter(dealertype__id=2, level=3).order_by('name')
benz_startline = lexus_startline + 1
print 'BENZ startline:', benz_startline, 'count:', len(benz_dealers)
for dealer in benz_dealers:
papers = Paper.objects.filter(dealer=dealer, project__id=4, paper_type='GFK', term=term, status=100)
# if papers and len(papers) > 0:
# dealers = []
# dealers.extend(term.dealers.all())
# dealers.append(dealer)
# term.dealers = dealers
# term.save()
benz_startline = gen_excel_sheet_com(dealer, excel, benz_startline, 4, Com_q_dict, 'GFK', term)
finally:
excel.save()
excel.close()
return xls_file
def make_excel(term_id):
term = _term.get_term_by_id(term_id)
target_file = os.path.join(settings.RESOURCES_ROOT,
u'data/%s_option_data.xls' % term.name_cn)
#默认保存2007的格式，需手动转换
projects = _project.get_2012_projects()
excel = easyExcel()
try:
for index, project in enumerate(projects):
sheetname = project.name
excel.setSheetName('sheet%d' % (index + 1), sheetname)
gen_col_dict(project, excel, sheetname)
papers = _paper.get_papers(project=project,
paper_type=enums.FW_PAPER_TYPE,
term=term)
for i, paper in enumerate(papers):
print paper.id
ans_dict = get_respondentdata_dict_by_paper(paper)
dealername = ''
dealername_cn = ''
dealer = paper.dealer
if dealer:
dealername = paper.dealer.name
dealername_cn = paper.dealer.name_cn
# print paper.id, paper.survey_code, dealername, dealername_cn
cellrow = i + 2
excel.setRangeVal(sheetname, '%s%s' % (get_char(0), cellrow),
dealername)
excel.setRangeVal(sheetname, '%s%s' % (get_char(1), cellrow),
dealername_cn)
excel.setRangeVal(sheetname, '%s%s' % (get_char(2), cellrow),
paper.id)
excel.setRangeVal(sheetname, '%s%s' % (get_char(3), cellrow),
ans_dict['customer_name']) #
excel.setRangeVal(sheetname, '%s%s' % (get_char(4), cellrow),
ans_dict['customer_address'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(5), cellrow),
paper.visitor_num)
excel.setRangeVal(sheetname, '%s%s' % (get_char(6), cellrow),
ans_dict['appraiser_code'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(7), cellrow),
'%.2f' % paper.score)
excel.setRangeVal(sheetname, '%s%s' % (get_char(8), cellrow),
ans_dict['booking_date'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(9), cellrow),
ans_dict['visit_date'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(10), cellrow),
ans_dict['visit_begin_time'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(11), cellrow),
ans_dict['visit_end_time'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(12), cellrow),
ans_dict['estimate_finish_date'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(13), cellrow),
ans_dict['estimate_finish_time'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(14), cellrow),
ans_dict['repare_finish_date'])
excel.setRangeVal(sheetname, '%s%s' % (get_char(15), cellrow),
ans_dict['repare_finish_time'])
base_ans = ans_dict['service_type']
alt = Alternative.objects.get(id=base_ans)
excel.setRangeVal(sheetname, '%s%s' % (get_char(16), cellrow),
alt.title)
base_ans = ans_dict['car_type']
alt = Alternative.objects.get(id=base_ans)
cartypes = alt.title
car_type__open = ans_dict['car_type__open']
if car_type__open:
cartypes = '%s %s' % (cartypes, car_type__open)
excel.setRangeVal(sheetname, '%s%s' % (get_char(17), cellrow),
cartypes)
base_ans = ans_dict['at_mt']
alt = Alternative.objects.get(id=base_ans)
excel.setRangeVal(sheetname, '%s%s' % (get_char(18), cellrow),
alt.title)
excel.setRangeVal(sheetname, '%s%s' % (get_char(19), cellrow),
ans_dict['consultant_name'])
base_ans = ans_dict['consultant_sex']
alt = Alternative.objects.get(id=base_ans)
excel.setRangeVal(sheetname, '%s%s' % (get_char(20), cellrow),
alt.title)
qc1 = PaperAudit.objects.filter(
paper=paper, new_status=enums.FW_PAPER_STATUS_WAIT_AUDIT_2)
if qc1:
excel.setRangeVal(sheetname,
'%s%s' % (get_char(21), cellrow),
qc1[0].user.username)
qc2 = PaperAudit.objects.filter(
paper=paper, new_status=enums.FW_PAPER_STATUS_WAIT_AUDIT_3)
if qc2:
excel.setRangeVal(sheetname,
'%s%s' % (get_char(22), cellrow),
qc2[0].user.username)
qc3 = PaperAudit.objects.filter(
paper=paper, new_status=enums.FW_PAPER_STATUS_WAIT_AUDIT_4)
if qc3:
excel.setRangeVal(sheetname,
'%s%s' % (get_char(23), cellrow),
qc3[0].user.username)
for key in COL_DICT.keys():
if '_' not in key:
qid = key
qcid = Question.objects.get(id=qid).cid
base_ans = ans_dict[qcid]
if base_ans is None:
continue
#print paper.id ,qid, base_ans
alt = Alternative.objects.get(id=base_ans)
cell = '%s%s' % (COL_DICT[qid], i + 2)
if u'是' in alt.title:
excel.setRangeVal(sheetname, cell, u'是')
else:
excel.setRangeVal(sheetname, cell, alt.title)
continue
keys = key.split('_')
qid = keys[0]
ans_cid = keys[1]
# if 'A' in ans_cid:
# if '1' not in ans_cid:
# continue
qcid = Question.objects.get(id=qid).cid
qkey = '%s__open' % qcid
base_ans = ans_dict[qcid]
if base_ans is None:
continue
#print paper.id ,qid, base_ans
alt = Alternative.objects.get(id=base_ans)
# cell = '%s%s' % (COL_DICT[key], i + 2)
# if 'A' in ans_cid:
# if u'是' in alt.title:
# excel.setRangeVal(sheetname, cell, u'是')
# else:
# excel.setRangeVal(sheetname, cell, alt.title)
if u'否' not in alt.title:
continue
#print paper.id, ans_dict
ans = ans_dict[qkey]
ans_list = []
if not ans:
continue
if '^-^' in ans:
ans_list = ans.split('^-^')
if ans_list:
for a in ans_list:
cell = '%s%s' % (COL_DICT[key], i + 2)
try:
alt_cid = int(a)
if int(ans_cid) == alt_cid:
excel.setRangeVal(sheetname, cell, alt_cid)
except:
if ans_cid == 'open':
excel.setRangeVal(sheetname, cell, a)
excel.save(target_file)
finally:
excel.close()
return target_file
def insert_questionqa(brand, sheet_index):
file_name = u'2015 APQ Questionnaire_20150108-final.xlsx'
xls_file = os.path.join(DOC_ROOT, file_name)
wb = easyExcel(xls_file)
ascii_digital = 64
part_name = ''
part_name_en = ''
part_letter = ''
part_parent = ''
q_parent = ''
q_number = ''
q_color = QuestionQAColor.objects.get(name=u'流程质量部分')
green_list = ['3',
'7a', '7b', '7c',
'12a', '12b', '12c', '12d', '12e', '12f', '12g', '12h', '12i',
'13a', '13b', '13c', '13d',
'21',
'34a', '34b', '34c',
'44a', '44b', '44c',
'47', '49', '53', '55', '56', '60', '61', '62', '63', '59']
for col in range(4, 299):
en_sheet_index = sheet_index - 1
row_a = wb.getRangeVal(sheet_index, 'A%s' % col)
row_b = wb.getRangeVal(sheet_index, 'B%s' % col)
row_c = wb.getRangeVal(sheet_index, 'C%s' % col)
row_d = wb.getRangeVal(sheet_index, 'D%s' % col)
row_e = wb.getRangeVal(sheet_index, 'E%s' % col)
row_a_en = wb.getRangeVal(en_sheet_index, 'A%s' % col)
row_b_en = wb.getRangeVal(en_sheet_index, 'B%s' % col)
row_c_en = wb.getRangeVal(en_sheet_index, 'C%s' % col)
row_e_en = wb.getRangeVal(en_sheet_index, 'E%s' % col)
if row_b:
row_b.strip()
if row_c:
row_c.strip()
if row_d != None:
try:
row_d = str(int(row_d)).strip()
except:
row_d = str(row_d).strip()
if row_e:
row_e.strip()
row_e = row_e.replace(u'\n', u'<br />')
if row_b_en:
row_b_en.strip()
if row_c_en:
row_c_en.strip()
if row_e_en:
row_e_en.strip()
row_e_en = row_e_en.replace(u'\n', u'<br />')
if row_a != None:
if row_b == None:
part_name = row_a
if hasattr(part_name, 'strip'):
part_name = part_name.strip()
part_name_en = row_a_en
if hasattr(part_name_en, 'strip'):
part_name_en = part_name_en.strip()
ascii_digital += 1
part_letter = chr(ascii_digital)
print brand, part_letter, part_name
part_parent, create = QuestionQANew.objects.get_or_create(brand=brand,
part=part_letter,
name_cn=part_name,
name_en=part_name_en,
has_child=True)
if row_b != None:
try:
q_number = str(int(row_a)).strip()
except:
q_number = str(row_a).strip()
print '\t', part_letter, q_number
print '\t\t', row_c, row_d
if q_number in green_list:
q_color = QuestionQAColor.objects.get(name=u'客户对待部分')
else:
q_color = QuestionQAColor.objects.get(name=u'流程质量部分')
q_parent, create = QuestionQANew.objects.get_or_create(
number=q_number,
question_cn=row_b,
question_en=row_b_en,
desc_cn=row_e,
desc_en=row_e_en,
color=q_color,
parent=part_parent,
has_child=True)
q_parent.save()
QuestionQANew.objects.get_or_create(
option_cn=row_c,
option_en=row_c_en,
point=row_d,
parent=q_parent)
else:
if row_b == None:
print '\t\t', row_c, row_d
QuestionQANew.objects.get_or_create(
option_cn=row_c,
option_en=row_c_en,
point=row_d,
parent=q_parent)
wb.save()
wb.close()