def findInvalidPhoneName():
'''
检查机型名称，看是否全部都在 phone_info 表中定义了。
'''
phoneList = []
allPhoneInfo = getAllPhoneInfo()
cx = getConn()
cu = cx.cursor()
strSql = "select c9 from temp_data WHERE data_type='fld' and use_flag='0'"
print strSql
cu.execute(strSql)
for sqlrow in cu:
phoneList.append(sqlrow[0])
cu.close()
cx.close()
print phoneList
invalidPhoneName = []
for phone in phoneList:
if not allPhoneInfo.has_key(phone):
invalidPhoneName.append(phone)
return invalidPhoneName
def findInvalidPhoneName():
'''
检查机型名称，看是否全部都在 phone_info 表中定义了。
'''
phoneList = []
allPhoneInfo = getAllPhoneInfo()
cx = getConn()
cu = cx.cursor()
strSql = "select c9 from temp_data WHERE data_type='fld' and use_flag='0'"
print strSql
cu.execute(strSql)
for sqlrow in cu:
phoneList.append( sqlrow[0] )
cu.close()
cx.close()
print phoneList
invalidPhoneName = []
for phone in phoneList:
if not allPhoneInfo.has_key(phone) :
invalidPhoneName.append(phone)
return invalidPhoneName
def GET(self):
flag = True
runData = {'runFlag':True,'showMsg':'已经删除所有未处理的发料单。'}
cx = getConn()
cu = cx.cursor()
strSql = "delete from temp_data WHERE data_type='fld' and use_flag='0'"
print strSql
cu.execute(strSql)
cx.commit()
cu.close()
cx.close()
return render.msg(runData)
def GET(self):
flag = True
runData = {'runFlag': True, 'showMsg': '已经删除所有未处理的发料单。'}
cx = getConn()
cu = cx.cursor()
strSql = "delete from temp_data WHERE data_type='fld' and use_flag='0'"
print strSql
cu.execute(strSql)
cx.commit()
cu.close()
cx.close()
return render.msg(runData)
def getAllPhoneInfo():
'''
从 phone_info 表中读出全部的机型名称和机型编码，放入 dict 中返回。
'''
allPhoneName = {}
cx = getConn()
cu = cx.cursor()
strSql = "select phone_name, phone_no from phone_info"
print strSql
cu.execute(strSql)
for sqlrow in cu:
allPhoneName[sqlrow[0]] = sqlrow[1]
cu.close()
cx.close()
print allPhoneName
return allPhoneName
def getAllPhoneInfo():
'''
从 phone_info 表中读出全部的机型名称和机型编码，放入 dict 中返回。
'''
allPhoneName = {}
cx = getConn()
cu = cx.cursor()
strSql = "select phone_name, phone_no from phone_info"
print strSql
cu.execute(strSql)
for sqlrow in cu:
allPhoneName[ sqlrow[0] ] = sqlrow[1]
cu.close()
cx.close()
print allPhoneName
return allPhoneName
def POST(self):
flag = True
runData = {'runFlag':True,'showMsg':'OK'}
req = web.input()
# 检查必填项是否都有值
mustName = ['c0_1',]
runData = lazy.checkInput( req, mustName, runData)
cx = lazy.getConn()
cu = cx.cursor()
strUpdate = "UPDATE temp_data SET use_flag='2' where data_no='" + req['c0_1'] + "'"
print strUpdate
cu.execute(strUpdate)
cx.commit()
cu.close()
cx.close
raise web.redirect('/')
def POST(self):
flag = True
runData = {'runFlag': True, 'showMsg': 'OK'}
req = web.input()
# 检查必填项是否都有值
mustName = [
'c0_1',
]
runData = lazy.checkInput(req, mustName, runData)
cx = lazy.getConn()
cu = cx.cursor()
strUpdate = "UPDATE temp_data SET use_flag='2' where data_no='" + req[
'c0_1'] + "'"
print strUpdate
cu.execute(strUpdate)
cx.commit()
cu.close()
cx.close
raise web.redirect('/')
def POST(self):
flag = True
msg = "lost : "
renderData = {'runFlag':True,'showMsg':'OK'}
req = web.input()
# 检查必填项是否都有值
mustName = ['fld_no','c0_1','c1_1','c2_1','c3_1','c4_1','c5_1','c6_1' \
,'c7_1','c8_1','c9_1','c9_1','c10_1','c11_1' \
]
renderData = lazy.checkInput( req, mustName, renderData)
req['fld_no'] = lazy.getFldNo()
if not renderData['runFlag']:
msg = "输入参数不正确，请按照正常操作流程使用系统。"
renderData['showMsg'] = msg + renderData['showMsg']
return render.err(renderData)
cx = lazy.getConn()
cu = cx.cursor()
strInsert = "INSERT INTO fld (fld_no, khmc, khdz, lxdh, ssds, xqdh, lsh, " \
+ "zdrq, ckrq, flck, jedx, jehj ) VALUES ('" \
+ req['fld_no'] + "','" + req['c1_1'] + "','" + req['c2_1'] + "','" + req['c3_1'] \
+ "','" + req['c4_1'] + "','" + req['c5_1'] + "','" + req['fld_no'] + "','" + req['c6_1'] \
+ "','" + req['c7_1'] + "','" + req['c8_1'] + "','" + req['c1_1'] + "','" + req['c1_1'] + "')"
print strInsert
try:
cu.execute(strInsert)
except:
renderData['runFlag'] = False
msg = "被禁止的数据库操作，请按照正常操作流程使用系统。"
renderData['showMsg'] = msg + renderData['showMsg']
return render.err(renderData)
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','1','" + req['c9_1'] + "','" + req['c10_1'] + "','" + req['c11_1'] \
+ "','" + req['c12_1'] + "','" + req['c13_1'] + "','"+ req['c14_1'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req['c0_1'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_2'], '')!=0 :
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','2','" + req['c9_2'] + "','" + req['c10_2'] + "','" + req['c11_2'] \
+ "','" + req['c12_2'] + "','" + req['c13_2'] + "','"+ req['c14_2'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req['c0_2'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_3'], '')!=0 :
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','3','" + req['c9_3'] + "','" + req['c10_3'] + "','" + req['c11_3'] \
+ "','" + req['c12_3'] + "','" + req['c13_3'] + "','"+ req['c14_3'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req['c0_3'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_4'], '')!=0 :
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','4','" + req['c9_4'] + "','" + req['c10_4'] + "','" + req['c11_4'] \
+ "','" + req['c12_4'] + "','" + req['c13_4'] + "','"+ req['c14_4'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req['c0_4'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_5'], '')!=0 :
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','5','" + req['c9_5'] + "','" + req['c10_5'] + "','" + req['c11_5'] \
+ "','" + req['c12_5'] + "','" + req['c13_5'] + "','"+ req['c14_5'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req['c0_5'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_6'], '')!=0 :
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','6','" + req['c9_6'] + "','" + req['c10_6'] + "','" + req['c11_6'] \
+ "','" + req['c12_6'] + "','" + req['c13_6'] + "','"+ req['c14_6'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req['c0_6'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_7'], '')!=0 :
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','7','" + req['c9_7'] + "','" + req['c10_7'] + "','" + req['c11_7'] \
+ "','" + req['c12_7'] + "','" + req['c13_7'] + "','"+ req['c14_7'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req['c0_7'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_8'], '')!=0 :
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','8','" + req['c9_8'] + "','" + req['c10_8'] + "','" + req['c11_8'] \
+ "','" + req['c12_8'] + "','" + req['c13_8'] + "','"+ req['c14_8'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req['c0_8'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_9'], '')!=0 :
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','9','" + req['c9_9'] + "','" + req['c10_9'] + "','" + req['c11_9'] \
+ "','" + req['c12_9'] + "','" + req['c13_9'] + "','"+ req['c14_9'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req['c0_9'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_10'], '')!=0 :
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','10','" + req['c9_10'] + "','" + req['c10_10'] + "','" + req['c11_10'] \
+ "','" + req['c12_10'] + "','" + req['c13_10'] + "','"+ req['c14_10'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req['c0_10'] + "'"
print strUpdate
cu.execute(strUpdate)
cx.commit()
cu.close()
cx.close()
#return "success"
host = web.ctx.get('host', '192.168.1.101')
tempIndex = host.find(':')
if tempIndex < 1 :
renderData['runFlag'] = False
print host[:tempIndex]
ip = host[:tempIndex]
if req.has_key('automode') :
web.setcookie('automode', 'y', 3600000)
#raise web.redirect('/makeFld')
else:
web.setcookie('automode', 'n', 3600000)
raise web.redirect('/showReport?type=fld&id=' + req['fld_no'] )
def POST(self):
flag = True
msg = "lost : "
renderData = {'runFlag': True, 'showMsg': 'OK'}
req = web.input()
# 检查必填项是否都有值
mustName = ['fld_no','c0_1','c1_1','c2_1','c3_1','c4_1','c5_1','c6_1' \
,'c7_1','c8_1','c9_1','c9_1','c10_1','c11_1' \
]
renderData = lazy.checkInput(req, mustName, renderData)
req['fld_no'] = lazy.getFldNo()
if not renderData['runFlag']:
msg = "输入参数不正确，请按照正常操作流程使用系统。"
renderData['showMsg'] = msg + renderData['showMsg']
return render.err(renderData)
cx = lazy.getConn()
cu = cx.cursor()
strInsert = "INSERT INTO fld (fld_no, khmc, khdz, lxdh, ssds, xqdh, lsh, " \
+ "zdrq, ckrq, flck, jedx, jehj ) VALUES ('" \
+ req['fld_no'] + "','" + req['c1_1'] + "','" + req['c2_1'] + "','" + req['c3_1'] \
+ "','" + req['c4_1'] + "','" + req['c5_1'] + "','" + req['fld_no'] + "','" + req['c6_1'] \
+ "','" + req['c7_1'] + "','" + req['c8_1'] + "','" + req['c1_1'] + "','" + req['c1_1'] + "')"
print strInsert
try:
cu.execute(strInsert)
except:
renderData['runFlag'] = False
msg = "被禁止的数据库操作，请按照正常操作流程使用系统。"
renderData['showMsg'] = msg + renderData['showMsg']
return render.err(renderData)
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','1','" + req['c9_1'] + "','" + req['c10_1'] + "','" + req['c11_1'] \
+ "','" + req['c12_1'] + "','" + req['c13_1'] + "','"+ req['c14_1'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req[
'c0_1'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_2'], '') != 0:
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','2','" + req['c9_2'] + "','" + req['c10_2'] + "','" + req['c11_2'] \
+ "','" + req['c12_2'] + "','" + req['c13_2'] + "','"+ req['c14_2'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req[
'c0_2'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_3'], '') != 0:
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','3','" + req['c9_3'] + "','" + req['c10_3'] + "','" + req['c11_3'] \
+ "','" + req['c12_3'] + "','" + req['c13_3'] + "','"+ req['c14_3'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req[
'c0_3'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_4'], '') != 0:
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','4','" + req['c9_4'] + "','" + req['c10_4'] + "','" + req['c11_4'] \
+ "','" + req['c12_4'] + "','" + req['c13_4'] + "','"+ req['c14_4'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req[
'c0_4'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_5'], '') != 0:
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','5','" + req['c9_5'] + "','" + req['c10_5'] + "','" + req['c11_5'] \
+ "','" + req['c12_5'] + "','" + req['c13_5'] + "','"+ req['c14_5'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req[
'c0_5'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_6'], '') != 0:
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','6','" + req['c9_6'] + "','" + req['c10_6'] + "','" + req['c11_6'] \
+ "','" + req['c12_6'] + "','" + req['c13_6'] + "','"+ req['c14_6'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req[
'c0_6'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_7'], '') != 0:
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','7','" + req['c9_7'] + "','" + req['c10_7'] + "','" + req['c11_7'] \
+ "','" + req['c12_7'] + "','" + req['c13_7'] + "','"+ req['c14_7'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req[
'c0_7'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_8'], '') != 0:
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','8','" + req['c9_8'] + "','" + req['c10_8'] + "','" + req['c11_8'] \
+ "','" + req['c12_8'] + "','" + req['c13_8'] + "','"+ req['c14_8'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req[
'c0_8'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_9'], '') != 0:
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','9','" + req['c9_9'] + "','" + req['c10_9'] + "','" + req['c11_9'] \
+ "','" + req['c12_9'] + "','" + req['c13_9'] + "','"+ req['c14_9'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req[
'c0_9'] + "'"
print strUpdate
cu.execute(strUpdate)
if cmp(req['c0_10'], '') != 0:
strInsert = "INSERT INTO fld_list (fld_no, bh, mcjgg, jxdm, jldw, yfsl, sfsl, bz ) VALUES ('" \
+ req['fld_no'] + "','10','" + req['c9_10'] + "','" + req['c10_10'] + "','" + req['c11_10'] \
+ "','" + req['c12_10'] + "','" + req['c13_10'] + "','"+ req['c14_10'] +"')"
print strInsert
cu.execute(strInsert)
strUpdate = "UPDATE temp_data SET use_flag='1' where data_no='" + req[
'c0_10'] + "'"
print strUpdate
cu.execute(strUpdate)
cx.commit()
cu.close()
cx.close()
#return "success"
host = web.ctx.get('host', '192.168.1.101')
tempIndex = host.find(':')
if tempIndex < 1:
renderData['runFlag'] = False
print host[:tempIndex]
ip = host[:tempIndex]
if req.has_key('automode'):
web.setcookie('automode', 'y', 3600000)
#raise web.redirect('/makeFld')
else:
web.setcookie('automode', 'n', 3600000)
raise web.redirect('/showReport?type=fld&id=' + req['fld_no'])
def POST(self):
flag = True
runData = {'runFlag': True, 'showMsg': 'OK'}
'''
先上传文件，再检查文件，
包括格式、内容、以及是否和数据库中已有内容冲突，
如果不正确，报错，要求重新上传正确文件；
如果正确，把内容灌入DB。
灌入数据完成后，将全部机型代码显示在页面上。
'''
# 上传文件
x = web.input(myfile={})
filedir = "static/upload"
if 'myfile' in x:
# replaces the windows-style slashes with linux ones.
filepath = x.myfile.filename.replace('\\', '/')
# splits the and chooses the last part (the filename with extension)
filename = filepath.split('/')[-1]
# creates the file where the uploaded file should be stored
fullpath = filedir + '/' + getYmdhms() + '.txt'
print fullpath
fout = open(fullpath, 'wb') #使用 wb 是为了在windows下能正确的上传文件。
# writes the uploaded file to the newly created file.
fout.write(x.myfile.file.read())
fout.close()
else:
runData['showMsg'] = '上传文件出错。'
return render.err(runData)
# 存放合法数据的列表
infos = []
# 检查数据
phoneInfoFile = open(fullpath, 'r')
phoneInfoLine = phoneInfoFile.readlines()
phoneInfoFile.close()
for i, line in enumerate(phoneInfoLine):
line = line.strip()
if len(line) == 0:
continue #空行直接跳过
else:
info = line.split(None, 1) # 用第一个空格分成两个部分
if len(info) < 2:
# 格式不对，报错
runData['showMsg'] = '文件格式错误。在第 %d 行:%s' % (i, line)
return render.err(runData)
else:
infos.append(info)
con = getConn()
cur = con.cursor()
# 检查是否和DB中已有数据冲突。
for i, info in enumerate(infos):
strSql = "SELECT * from phone_info WHERE phone_name='%s'" % info[1]
print strSql
cur.execute(strSql)
if cur.fetchone():
# 机型代码和DB中已有数据冲突，报错
runData['showMsg'] = '数据错误，机型名称已经存在，在第 %d 条数据: %s %s' % (
i + 1, info[0], info[1])
cur.close()
con.close()
return render.err(runData)
# 内容灌入DB
for info in infos:
strSql = "insert into phone_info (phone_no, phone_name) values ('%s','%s') " % (
info[0], info[1])
print strSql
cur.execute(strSql)
con.commit()
# 将DB中的全部机型信息取出
allPhoneInfo = []
# strSql = "select phone_no, phone_name from phone_info order by phone_no "
strSql = "select phone_no, phone_name from phone_info order by rowid DESC "
print strSql
cur.execute(strSql)
for record in cur:
phone = dict()
phone['phone_no'] = record[0]
phone['phone_name'] = record[1]
allPhoneInfo.append(phone)
cur.close()
con.close()
runData['AllPhoneInfo'] = allPhoneInfo
runData['showMsg'] = '增加机型信息成功'
return render.phoneInfo(runData)
def POST(self):
flag = True
runData = {"runFlag": True, "showMsg": "OK"}
req = web.input()
# 检查必填项是否都有值
mustName = ["report_type", "filename"]
for name in mustName:
if req.has_key(name):
flag = True
else:
flag = False
if not flag:
msg = "输入参数不正确，请按照正常流程使用系统。"
runData["showMsg"] = msg
return render.err(runData)
# 取出所有的excel表中的列和数据库中的field的对应关系
fld_data_col = []
fld_data_col.append(req["fld_data_col_1"])
fld_data_col.append(req["fld_data_col_2"])
fld_data_col.append(req["fld_data_col_3"])
fld_data_col.append(req["fld_data_col_4"])
fld_data_col.append(req["fld_data_col_5"])
fld_data_col.append(req["fld_data_col_6"])
fld_data_col.append(req["fld_data_col_7"])
fld_data_col.append(req["fld_data_col_8"])
fld_data_col.append(req["fld_data_col_9"])
fld_data_col.append(req["fld_data_col_10"])
fld_data_col.append(req["fld_data_col_11"])
fld_data_col.append(req["fld_data_col_12"])
fld_data_col.append(req["fld_data_col_13"])
fld_data_col.append(req["fld_data_col_14"])
print (fld_data_col)
report_type = req["report_type"]
# 读出excel文件中的数据，并取出第一个sheet
filename = req["filename"]
data = xlsUtil.getExcelData(filename)
tables = data["tables"]
table1 = tables[0]
sheet1 = table1["t_data"]
strInsertTail = ""
iCount = 0
cx = getConn()
cu = cx.cursor()
for row in sheet1:
""" 第一条数据是标题，不需要导入，所以把第 0 条跳过 """
if iCount > 0:
strInsertTail = ""
for strIndex in fld_data_col:
if strIndex == u"":
strInsertTail = strInsertTail + ",''"
else:
iIndex = int(strIndex)
strText = row[iIndex - 1]
strInsertTail = strInsertTail + ",'" + unicode(strText) + "'"
data_no = getYmdhms() + "_" + str(iCount)
strInsertHead = (
"INSERT INTO temp_data (data_no, data_type, use_flag, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14 ) VALUES ( '"
+ data_no
+ "', '"
+ report_type
+ "', '0'"
)
strSql = strInsertHead + strInsertTail + ")"
print strSql
cu.execute(strSql)
iCount = iCount + 1
cx.commit()
cu.close()
cx.close()
# 检查机型名称能否对应到机型代码，如果不能，去机型名称出错页面。
invalidPhone = findInvalidPhoneName()
if len(invalidPhone) == 0:
print "机型名称检查合格。"
else:
runData["showMsg"] = "某些机型名称无法找到对应的机型代码，请先检查上传文件是否正确，或者维护好机型信息再进行制单操作。"
runData["invalidPhone"] = invalidPhone
return render.phoneNameLost(runData)
reportType = req["report_type"]
if reportType == "fld":
saveFld()
elif reportType == "sld":
saveSld()
elif reportType == "jld":
saveJld()
elif reportType == "lyd":
saveLyd()
elif reportType == "nbdbd":
saveNbdbd()
elif reportType == "rkd":
saveRkd()
else:
msg = "报表类型不正确"
runData["showMsg"] = msg
return render.err(runData)
msg = "导入操作执行成功，共导入数据" + str(iCount) + "条。"
runData["showMsg"] = msg
return render.msg(runData)
def POST(self):
flag = True
runData = {'runFlag':True,'showMsg':'OK'}
req = web.input()
# 检查必填项是否都有值
mustName = ['report_type','filename']
for name in mustName:
if req.has_key(name) :
flag = True
else:
flag = False
if not flag :
msg = "输入参数不正确，请按照正常流程使用系统。"
runData['showMsg'] = msg
return render.err(runData)
# 取出所有的excel表中的列和数据库中的field的对应关系
fld_data_col = []
fld_data_col.append( req['fld_data_col_1'])
fld_data_col.append( req['fld_data_col_2'])
fld_data_col.append( req['fld_data_col_3'])
fld_data_col.append( req['fld_data_col_4'])
fld_data_col.append( req['fld_data_col_5'])
fld_data_col.append( req['fld_data_col_6'])
fld_data_col.append( req['fld_data_col_7'])
fld_data_col.append( req['fld_data_col_8'])
fld_data_col.append( req['fld_data_col_9'])
fld_data_col.append( req['fld_data_col_10'])
fld_data_col.append( req['fld_data_col_11'])
fld_data_col.append( req['fld_data_col_12'])
fld_data_col.append( req['fld_data_col_13'])
fld_data_col.append( req['fld_data_col_14'])
print( fld_data_col )
report_type = req["report_type"]
# 读出excel文件中的数据，并取出第一个sheet
filename = req["filename"]
data = xlsUtil.getExcelData(filename)
tables = data['tables']
table1 = tables[0]
sheet1 = table1['t_data']
strInsertTail = ''
iCount = 0
cx = getConn()
cu = cx.cursor()
for row in sheet1:
""" 第一条数据是标题，不需要导入，所以把第 0 条跳过 """
if iCount > 0 :
strInsertTail = ''
for strIndex in fld_data_col :
if strIndex == u'' :
strInsertTail = strInsertTail + ",''"
else:
iIndex = int(strIndex)
strText = row[iIndex-1]
strInsertTail = strInsertTail + ",'" + unicode(strText) + "'"
data_no = getYmdhms() + "_" + str(iCount)
strInsertHead = "INSERT INTO temp_data (data_no, data_type, use_flag, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14 ) VALUES ( '" + data_no + "', '" + report_type + "', '0'"
strSql = strInsertHead + strInsertTail + ")"
print strSql
cu.execute(strSql)
iCount = iCount + 1
cx.commit()
cu.close()
cx.close()
#检查机型名称能否对应到机型代码，如果不能，去机型名称出错页面。
invalidPhone = findInvalidPhoneName();
if len(invalidPhone) == 0 :
print '机型名称检查合格。'
else:
runData['showMsg']='某些机型名称无法找到对应的机型代码，请先检查上传文件是否正确，或者维护好机型信息再进行制单操作。'
runData['invalidPhone']=invalidPhone
return render.phoneNameLost(runData)
reportType = req["report_type"]
if reportType == "fld" :
saveFld()
elif reportType == "sld" :
saveSld()
elif reportType == "jld" :
saveJld()
elif reportType == "lyd" :
saveLyd()
elif reportType == "nbdbd" :
saveNbdbd()
elif reportType == "rkd" :
saveRkd()
else :
msg = "报表类型不正确"
runData['showMsg'] = msg
return render.err(runData)
msg = "导入操作执行成功，共导入数据" + str(iCount) + "条。"
runData['showMsg'] = msg
return render.msg(runData)
def GET(self):
flag = True
runData = {'runFlag':True,'showMsg':'OK'}
req = web.input()
#检查机型名称能否对应到机型代码，如果不能，去机型名称出错页面。
invalidPhone = findInvalidPhoneName();
if len(invalidPhone) == 0 :
#print '机型名称检查合格。'
allPhoneInfo = getAllPhoneInfo()
else:
runData['showMsg']='某些机型名称无法找到对应的机型代码，请先检查上传文件是否正确，或者维护好机型信息再进行制单操作。'
runData['invalidPhone']=invalidPhone
return render.phoneNameLost(runData)
cx = getConn()
cu = cx.cursor()
strSql = "SELECT data_no, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14 from temp_data WHERE data_type='fld' and use_flag='0' ORDER BY c1,c5"
print strSql
cu.execute(strSql)
dataCount = 0
prevNo = 'abcdefghijklmn'
thisNo = ''
for sqlrow in cu:
dataCount = dataCount + 1
runData['c0_' + str(dataCount) ] = sqlrow[0]
runData['c1_' + str(dataCount) ] = sqlrow[1]
runData['c2_' + str(dataCount) ] = sqlrow[2]
runData['c3_' + str(dataCount) ] = sqlrow[3]
runData['c4_' + str(dataCount) ] = sqlrow[4]
thisNo = sqlrow[5]
runData['c5_' + str(dataCount) ] = thisNo
runData['c6_' + str(dataCount) ] = sqlrow[6]
runData['c7_' + str(dataCount) ] = sqlrow[7]
runData['c8_' + str(dataCount) ] = sqlrow[8]
# 机型代码通过机型名称查表得到
phoneName = sqlrow[9]
runData['c9_' + str(dataCount) ] = phoneName
#runData['c10_'+ str(dataCount) ] = sqlrow[10]
runData['c10_'+ str(dataCount) ] = allPhoneInfo[phoneName]
runData['c11_'+ str(dataCount) ] = sqlrow[11]
runData['c12_'+ str(dataCount) ] = sqlrow[12]
runData['c13_'+ str(dataCount) ] = sqlrow[13]
runData['c14_'+ str(dataCount) ] = sqlrow[14]
#print "thisNo=" + thisNo
#print "prevNo=" + prevNo
if cmp(prevNo, 'abcdefghijklmn')==0:
prevNo = thisNo
elif cmp(prevNo, thisNo) != 0 :
dataCount = dataCount - 1
break
elif dataCount == 10 :
break
cu.close()
cx.close()
if dataCount < 10 :
for loopCount in range(dataCount+1,11):
#print "tian chong kong bai hang"
runData['c0_'+ str(loopCount) ] = ''
runData['c9_'+ str(loopCount) ] = ''
runData['c10_'+ str(loopCount) ] = ''
runData['c11_'+ str(loopCount) ] = ''
runData['c12_'+ str(loopCount) ] = ''
runData['c13_'+ str(loopCount) ] = ''
runData['c14_'+ str(loopCount) ] = ''
if dataCount == 0 :
runData['showMsg'] = "太好了，所有发料单都已经生成了。"
return render.msg(runData)
#runData['fld_no'] = getFldNo()
runData['fld_no'] = 'Auto'
#print runData
pageCookie = web.cookies(automode='n')
if cmp(pageCookie.automode, 'y') == 0:
runData['automodeChecked'] = 'checked'
else:
runData['automodeChecked'] = ''
return render.makeFld(runData)
def POST(self):
flag = True
runData = {'runFlag':True,'showMsg':'OK'}
'''
先上传文件，再检查文件，
包括格式、内容、以及是否和数据库中已有内容冲突，
如果不正确，报错，要求重新上传正确文件；
如果正确，把内容灌入DB。
灌入数据完成后，将全部机型代码显示在页面上。
'''
# 上传文件
x = web.input(myfile={})
filedir = "static/upload"
if 'myfile' in x:
# replaces the windows-style slashes with linux ones.
filepath=x.myfile.filename.replace('\\','/')
# splits the and chooses the last part (the filename with extension)
filename=filepath.split('/')[-1]
# creates the file where the uploaded file should be stored
fullpath = filedir +'/'+ getYmdhms() + '.txt'
print fullpath
fout = open( fullpath,'wb') #使用 wb 是为了在windows下能正确的上传文件。
# writes the uploaded file to the newly created file.
fout.write(x.myfile.file.read())
fout.close()
else:
runData['showMsg'] = '上传文件出错。'
return render.err(runData)
# 存放合法数据的列表
infos = [ ]
# 检查数据
phoneInfoFile = open( fullpath, 'r' )
phoneInfoLine = phoneInfoFile.readlines();
phoneInfoFile.close()
for i,line in enumerate(phoneInfoLine):
line = line.strip()
if len(line)==0 :
continue #空行直接跳过
else :
info = line.split(None,1) # 用第一个空格分成两个部分
if len(info) < 2 :
# 格式不对，报错
runData['showMsg'] = '文件格式错误。在第 %d 行:%s' % (i,line)
return render.err(runData)
else:
infos.append(info)
con = getConn()
cur = con.cursor()
# 检查是否和DB中已有数据冲突。
for i,info in enumerate(infos):
strSql = "SELECT * from phone_info WHERE phone_name='%s'" % info[1]
print strSql
cur.execute(strSql)
if cur.fetchone() :
# 机型代码和DB中已有数据冲突，报错
runData['showMsg'] = '数据错误，机型名称已经存在，在第 %d 条数据: %s %s' % ( i+1,info[0],info[1])
cur.close()
con.close()
return render.err(runData)
# 内容灌入DB
for info in infos:
strSql = "insert into phone_info (phone_no, phone_name) values ('%s','%s') " % (info[0],info[1])
print strSql
cur.execute(strSql)
con.commit()
# 将DB中的全部机型信息取出
allPhoneInfo = []
# strSql = "select phone_no, phone_name from phone_info order by phone_no "
strSql = "select phone_no, phone_name from phone_info order by rowid DESC "
print strSql
cur.execute(strSql)
for record in cur:
phone = dict()
phone['phone_no'] = record[0]
phone['phone_name'] = record[1]
allPhoneInfo.append(phone)
cur.close()
con.close()
runData['AllPhoneInfo'] = allPhoneInfo
runData['showMsg'] = '增加机型信息成功'
return render.phoneInfo(runData)
def GET(self):
flag = True
runData = {'runFlag': True, 'showMsg': 'OK'}
req = web.input()
#检查机型名称能否对应到机型代码，如果不能，去机型名称出错页面。
invalidPhone = findInvalidPhoneName()
if len(invalidPhone) == 0:
#print '机型名称检查合格。'
allPhoneInfo = getAllPhoneInfo()
else:
runData[
'showMsg'] = '某些机型名称无法找到对应的机型代码，请先检查上传文件是否正确，或者维护好机型信息再进行制单操作。'
runData['invalidPhone'] = invalidPhone
return render.phoneNameLost(runData)
cx = getConn()
cu = cx.cursor()
strSql = "SELECT data_no, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14 from temp_data WHERE data_type='fld' and use_flag='0' ORDER BY c1,c5"
print strSql
cu.execute(strSql)
dataCount = 0
prevNo = 'abcdefghijklmn'
thisNo = ''
for sqlrow in cu:
dataCount = dataCount + 1
runData['c0_' + str(dataCount)] = sqlrow[0]
runData['c1_' + str(dataCount)] = sqlrow[1]
runData['c2_' + str(dataCount)] = sqlrow[2]
runData['c3_' + str(dataCount)] = sqlrow[3]
runData['c4_' + str(dataCount)] = sqlrow[4]
thisNo = sqlrow[5]
runData['c5_' + str(dataCount)] = thisNo
runData['c6_' + str(dataCount)] = sqlrow[6]
runData['c7_' + str(dataCount)] = sqlrow[7]
runData['c8_' + str(dataCount)] = sqlrow[8]
# 机型代码通过机型名称查表得到
phoneName = sqlrow[9]
runData['c9_' + str(dataCount)] = phoneName
#runData['c10_'+ str(dataCount) ] = sqlrow[10]
runData['c10_' + str(dataCount)] = allPhoneInfo[phoneName]
runData['c11_' + str(dataCount)] = sqlrow[11]
runData['c12_' + str(dataCount)] = sqlrow[12]
runData['c13_' + str(dataCount)] = sqlrow[13]
runData['c14_' + str(dataCount)] = sqlrow[14]
#print "thisNo=" + thisNo
#print "prevNo=" + prevNo
if cmp(prevNo, 'abcdefghijklmn') == 0:
prevNo = thisNo
elif cmp(prevNo, thisNo) != 0:
dataCount = dataCount - 1
break
elif dataCount == 10:
break
cu.close()
cx.close()
if dataCount < 10:
for loopCount in range(dataCount + 1, 11):
#print "tian chong kong bai hang"
runData['c0_' + str(loopCount)] = ''
runData['c9_' + str(loopCount)] = ''
runData['c10_' + str(loopCount)] = ''
runData['c11_' + str(loopCount)] = ''
runData['c12_' + str(loopCount)] = ''
runData['c13_' + str(loopCount)] = ''
runData['c14_' + str(loopCount)] = ''
if dataCount == 0:
runData['showMsg'] = "太好了，所有发料单都已经生成了。"
return render.msg(runData)
#runData['fld_no'] = getFldNo()
runData['fld_no'] = 'Auto'
#print runData
pageCookie = web.cookies(automode='n')
if cmp(pageCookie.automode, 'y') == 0:
runData['automodeChecked'] = 'checked'
else:
runData['automodeChecked'] = ''
return render.makeFld(runData)