def BillAreOrder(self,billno):
fname = 'log'+str(datetime.date.today()) + '.txt'
sDir = cur_file_dir() + '\\log\\'+fname
sbillno = str(billno.encode('gbk'))
sSQL = 'select count(1) as count from web_billmain where billno = ? '
with open(sDir, 'a') as f:
f.write(sSQL + ':' +sbillno +'\n')
Fexist = oracle_db.select(sSQL,sbillno)
if Fexist[0]['COUNT'] != 0:
return 'BillNo already Order a ticket !'
return '0'
def GetTicket(self):
sSQL = "select ticketmodelcode,ticketmodelname from sys_ticketmodel where ticketmodelgroupcode = " + str(sTicketGroup)
fname = 'log'+str(datetime.date.today()) + '.txt'
sDir = cur_file_dir() + '\\log\\'+fname
with open(sDir, 'a') as f:
f.write(sSQL + '\n')
tickets = oracle_db.select(sSQL)
if len(tickets) ==0:
return 'No Date'
for ticket in tickets:
# ticketname = ticket['TICKETMODELNAME'].encode('raw_unicode_escape')
ticketname = ticket['TICKETMODELNAME'].decode('gbk')
# ticketname = ticketname.decode('gbk')
ticket['TICKETMODELNAME'] = ticketname
data_string = json.dumps(tickets, ensure_ascii=False)
return data_string
def TestDBConnet(LabMess, isconnet=False):
global Fconnet
Fconnet = False
if varDBType.get() == '0':
oracle_db.engine = None
sSQL = 'select 1 as Count from dual '
oracle_db.create_engine(user=varUser.get(),
password=varPaswd.get(),
database=varDateBase.get(),
host=varIP.get())
try:
LabMess['text'] = '数据库连接失败。'
user = oracle_db.select(sSQL)
if user[0]['COUNT'] == 1:
LabMess['text'] = '数据库连接成功。'
Fconnet = True
else:
LabMess['text'] = '数据库连接失败。'
except ImportError:
root.update()
if varDBType.get() == '1':
DbIntf.engine = None
sSQL = 'select 1 as Count '
DbIntf.create_engine(user=varUser.get(),
password=varPaswd.get(),
database=varDateBase.get(),
host=varIP.get())
try:
LabMess['text'] = '数据库连接失败。'
user = DbIntf.select(sSQL)
if user[0]['COUNT'] == 1:
LabMess['text'] = '数据库连接成功。'
Fconnet = True
else:
LabMess['text'] = '数据库连接失败。'
except ImportError:
root.update()
def StartUpLoad(LabMess):
global Fconnet
if Fconnet == False:
LabMess['text'] = '请先连接数据库。。'
fname = 'log' + str(datetime.date.today()) + '.txt'
sDir = cur_file_dir() + '\\log\\' + fname
while Fconnet == True:
LabMess['text'] = '等待60秒..'
time.sleep(2)
LabMess['text'] = '开始上传..'
try:
for option, value in config.items('infoCode'):
if (option <> '1001') and (option[3:4]
== '1') and (value <> ''):
if varDBType.get() == '0':
with open(sDir, 'a') as f:
f.write('select ' + value + ' from dual ' + '\n')
else:
with open(sDir, 'a') as f:
f.write('DECLARE @RetMess varchar '
'exec ' + value + ' '
'@RetMess OUTPUT '
'SELECT @RetMess ' + '\n')
if varDBType.get() == '0':
RES = oracle_db.select('select ' + value +
' as SQL from dual ')
SQL = RES[0]['SQL']
RES = oracle_db.exec_sp(SQL)
else:
RES = DbIntf.exec_sp('DECLARE @RetMess varchar '
'exec ' + value + ' '
'@RetMess OUTPUT '
'SELECT @RetMess ')
Tradeid = RES[0]['TRADEID']
Xdetail = RES
# {"infoCode":str(option),"scenicCode" :str(varParkCode.get()),"content":Xdetail}
vjson = eval(sFormatJson)
values = json.dumps(vjson,
ensure_ascii=False,
encoding='gbk')
vjson = json.dumps(vjson,
ensure_ascii=True,
encoding='gbk')
with open(sDir, 'a') as f:
f.write('UpJosn:' + str(values.encode('gbk')) + '\n')
#如果想传过去中文不是Unicode使用以下
vjson = eval(vjson)
data = urllib.urlencode(vjson)
request = urllib2.Request(varWebSerIp.get(),
data) #构造Requset
response = urllib2.urlopen(request)
data_string = response.read()
obj_json = json.loads(data_string)
Amsg = obj_json['msg']
Asucc = obj_json['success']
#记录返回
with open(sDir, 'a') as f:
f.write('BackTrans:' + str(option) + ':' +
str(obj_json) + '\n')
if (Amsg == option[:3] + '2') and (Asucc == True):
#成功后更新
ProcName = config.get('infoCode', str(Amsg))
if varDBType.get() == '0':
with open(sDir, 'a') as f:
f.write('select ' + ProcName + '(' +
str(Tradeid) + ') from dual ' + '\n')
else:
with open(sDir, 'a') as f:
f.write('DECLARE @RetMess varchar '
'exec ' + ProcName + ' "' +
str(Tradeid) + '" '
'@RetMess OUTPUT '
'SELECT @RetMess ' + '\n')
if varDBType.get() == '0':
RES = oracle_db.select('select ' + ProcName + '(' +
str(Tradeid) +
') as SQL from dual ')
SQL = RES[0]['SQL']
RES = oracle_db.update(SQL)
else:
RES = DbIntf.exec_sp('DECLARE @RetMess varchar '
'exec ' + ProcName + ' "' +
str(Tradeid) + '" '
'@RetMess OUTPUT '
'SELECT @RetMess ')
if RES == '1':
LabMess['text'] = str(Tradeid) + u'：更新成功。'
with open(sDir, 'a') as f:
f.write(
str(Tradeid) + u'：更新成功。'.encode('gbk') +
'\n')
else:
LabMess['text'] = str(Tradeid) + u'：更新失败。'
with open(sDir, 'a') as f:
f.write(
str(Tradeid) + u'更新失败'.encode('gbk') +
'\n')
else:
LabMess['text'] = option + u'：传输失败。'
Fconnet = False
with open(sDir, 'a') as f:
f.write(
str(option) + u'：传输失败。交易号：'.encode('gbk') +
str(Tradeid) + '\n')
except ImportError:
Fconnet = False
return '数据提交失败'
def StartUpLoad(LabMess):
global Fconnet
if Fconnet == False:
LabMess['text'] = '请先连接数据库。。'
while Fconnet == True:
LabMess['text'] = '等待' + str(sSleepTime) + '秒..'
time.sleep(int(sSleepTime))
LabMess['text'] = '开始上传..'
fname = 'log' + str(datetime.date.today()) + '.txt'
sDir = cur_file_dir() + '\\log\\' + fname
try:
for option, value in config.items('infoCode'):
if (option <> '1001') and (option[3:4]
== '1') and (value <> ''):
if varDBType.get() == '0':
with open(sDir, 'a') as f:
f.write('select ' + value + ' from dual ' + '\n')
else:
with open(sDir, 'a') as f:
f.write('DECLARE @RetMess varchar '
'exec ' + value + ' '
'@RetMess OUTPUT '
'SELECT @RetMess ' + '\n')
if varDBType.get() == '0':
RES = oracle_db.select('select ' + value +
' as SQL from dual ')
SQL = RES[0]['SQL']
with open(sDir, 'a') as f:
f.write('SQL: ' + SQL + '\n')
SQL = SQL.replace("\n", " ")
RES = oracle_db.select(SQL)
else:
RES = DbIntf.exec_sp('DECLARE @RetMess varchar '
'exec ' + value + ' '
'@RetMess OUTPUT '
'SELECT @RetMess ')
SQL = RES[0]['SQL']
with open(sDir, 'a') as f:
f.write('SQL: ' + SQL + '\n')
SQL = SQL.replace("\n", " ")
RES = DbIntf.select(SQL)
if len(RES) > 0:
Tradeid = RES[0]['TRADEID']
Xdetail = RES
# {"infoCode":str(option),"scenicCode" :str(varParkCode.get()),"content":Xdetail}
vjson = eval(sFormatJson)
values = json.dumps(vjson,
ensure_ascii=False,
encoding='gbk')
if varDBType.get() == '0':
vjson = json.dumps(vjson,
ensure_ascii=True,
encoding='gbk')
with open(sDir, 'a') as f:
f.write('UpJosn:' + str(values.encode('gbk')) +
'\n')
else:
vjson = values.encode('raw_unicode_escape')
vjson = eval(
json.dumps(vjson,
ensure_ascii=True,
encoding='gbk'))
with open(sDir, 'a') as f:
f.write(
'UpJosn:' +
str(values.encode('raw_unicode_escape')) +
'\n')
LabMess['text'] = '正在上传:' + str(option) + '...'
time.sleep(0.5)
vjson = eval(vjson)
data = urllib.urlencode(vjson)
request = urllib2.Request(varWebSerIp.get(),
data) #构造Requset
response = urllib2.urlopen(request)
data_string = response.read()
obj_json = json.loads(data_string)
Amsg = obj_json['msg']
Asucc = obj_json['success']
#记录返回
with open(sDir, 'a') as f:
f.write('BackTrans:' + str(option) + ':' +
str(obj_json) + '\n')
if (Amsg == option[:3] + '2') and (Asucc == True):
#成功后更新
if config.has_option('infoCode',
str(Amsg)) == True:
ProcName = config.get('infoCode', str(Amsg))
if varDBType.get() == '0':
with open(sDir, 'a') as f:
f.write('select ' + ProcName + '(' +
str(Tradeid) + ') from dual ' +
'\n')
else:
with open(sDir, 'a') as f:
f.write('DECLARE @RetMess varchar '
'exec ' + ProcName + ' "' +
str(Tradeid) + '" '
'@RetMess OUTPUT '
'SELECT @RetMess ' + '\n')
if varDBType.get() == '0':
RES = oracle_db.select(
'select ' + ProcName + '(' +
repr(Tradeid) + ') as SQL from dual ')
SQL = RES[0]['SQL']
with open(sDir, 'a') as f:
f.write('SQL: ' + SQL + '\n')
SQL = SQL.replace("\n", " ")
RES = oracle_db.update(SQL)
else:
RES = DbIntf.exec_sp(
'DECLARE @RetMess varchar '
'exec ' + ProcName + ' "' +
str(Tradeid) + '",'
'@RetMess OUTPUT '
'SELECT @RetMess ')
SQL = RES[0]['SQL']
with open(sDir, 'a') as f:
f.write('SQL: ' + SQL + '\n')
SQL = SQL.replace("\n", " ")
RES = DbIntf.update(SQL)
if RES >= 1:
LabMess['text'] = str(Tradeid) + '：更新成功。'
time.sleep(0.5)
with open(sDir, 'a') as f:
f.write(
str(Tradeid) +
u'：更新成功。'.encode('gbk') + '\n')
else:
LabMess['text'] = str(Tradeid) + '：更新失败。'
time.sleep(0.5)
with open(sDir, 'a') as f:
f.write(
str(Tradeid) +
u'更新失败'.encode('gbk') + '\n')
else:
LabMess['text'] = option + '：传输失败。'
time.sleep(0.5)
# Fconnet = False
with open(sDir, 'a') as f:
f.write(
str(option) + u'：传输失败。交易号：'.encode('gbk') +
str(Tradeid) + '\n')
continue
else:
LabMess['text'] = option + '：没有数据需要更新。'
time.sleep(0.3)
except:
continue
def add_bill(jsonobj):
strbillno = jsonobj['billno']
sclientcode = jsonobj['clientcode']
sclientname = jsonobj['clientname']
sareacode = jsonobj['areacode']
nticketcount = jsonobj['ticketcount']
cpaysum = jsonobj['paysum']
susername = jsonobj['username']
stelno = jsonobj['telno']
scertno = jsonobj['certno']
# 主表
# sSQL = "insert into web_billmain(id,billno,billoutno,traveldate,billdate,billstatus,billtype,webbillstatus,"\
# "clientCode,clientname,areacode,ticketcount,paysum,paytype,payflag,username,"\
# "telno,certtype,certno,create_time,modified_time,deleted) "\
# "values (seq_webbillid.nextval,'" + strbillno + "','"+ strbillno +"',trunc(sysdate),sysdate,0,'pdc','valid',"\
# "'"+sclientcode +"','" + sclientname + "','" + sareacode + "'," + str(nticketcount) + "," + str(cpaysum) + ","\
# "'07',1, '"+susername+"','" +stelno +"','01','"+ scertno + "',sysdate,sysdate,0)"
sSQL_Exc = "insert into web_billmain(id,billno,billoutno,traveldate,billdate,billstatus,billtype,webbillstatus,"\
"clientCode,clientname,areacode,ticketcount,paysum,paytype,payflag,username,"\
"telno,certtype,certno,create_time,modified_time,deleted) "\
"values (seq_webbillid.nextval,?,?,trunc(sysdate),sysdate,0,'pdc','valid',"\
" ?,?,?,?,?,"\
"'07',1, ?,?,'01',?,sysdate,sysdate,0)"
sSQL_Exc = sql_web_billmain
with open(sDir, 'a') as f:
f.write(str(sSQL_Exc.encode('gbk')) +'\n')
oracle_db.update(str(sSQL_Exc.encode('gbk')),strbillno,strbillno,sclientcode,sclientname,sareacode,str(nticketcount),
str(cpaysum),susername,stelno,scertno)
# 明细
for name in jsonobj['Billdetail']:
sticketmodelname,sticketmodelcode = name['ticketmodelname'],name['ticketmodelcode']
cprice,ncount = name['price'],name['count']
ctotal = cprice * ncount
startdate,enddate = name['startdate'],name['enddate']
# sSQLDetail = "insert into web_billdetail(id,billno,billdate,traveldatetime,invalidatetime,billstatus,webbillstatus,"\
# "billtype,tickettype,ticketmodelcode,ticketmodelname,ticketcount,ticketprice,paysum,payflag,create_time,"\
# "deleted) values(seq_webbillid.nextval,'" +strbillno + "',sysdate,trunc(sysdate),date '"+enddate+"',0,'valid',"\
# "'pdc',100001,'"+sticketmodelcode+"','"+sticketmodelname+"','"+str(ncount)+"','"+str(cprice)+"','"+str(ctotal)+"',1,sysdate,0)"
sSQLDetail_exc = "insert into web_billdetail(id,billno,billdate,traveldatetime,invalidatetime,billstatus,webbillstatus,"\
"billtype,tickettype,ticketmodelcode,ticketmodelname,ticketcount,ticketprice,paysum,payflag,create_time,"\
"deleted) values(seq_webbillid.nextval,?,sysdate,trunc(sysdate),to_date(?,'yyyy-mm-dd'),0,'valid',"\
"'pdc',100001,?,?,?,?,?,1,sysdate,0)"
sSQLDetail_exc = sql_web_billdetail
with open(sDir, 'a') as f:
f.write(str(sSQLDetail_exc.encode('gbk')) +'\n')
oracle_db.update(str(sSQLDetail_exc.encode('gbk')),strbillno,enddate,sticketmodelcode,sticketmodelname,str(ncount),str(cprice),str(ctotal))
#判断
sSQLTmp = "select count(1) as count from sys_ticketmodel where ticketmodelcode = ? "
Fexist = oracle_db.select(sSQLTmp,sticketmodelcode)
if Fexist[0]['COUNT'] == 0:
oracle_db._db_ctx.connection.rollback()
return '没有对应票型! will cause rollback...'
#检票表
sSQLCk = "select b.* from sys_ticketmodeldetail a left join sys_ticket b on a.ticketid = b.id "\
"where ticketmodelcode = ? "
ticketlist = oracle_db.select(sSQLCk,sticketmodelcode)
if len(ticketlist) ==0:
oracle_db._db_ctx.connection.rollback()
return'没有对应单票! will cause rollback...'
for aticket in ticketlist:
ticketcode = aticket['TICKETCODE']
ticketname = aticket['TICKETFULLNAME'].decode('gbk')
ticketprice = aticket['PRICE']
ticketkind = aticket['TICKETKIND']
seasontype = aticket['SEASONTYPE']
parkcode = aticket['PARKCODE']
# 准备条码
# 客户代码
sSQLTmp = ' select trim(a.paramvalue) as khdm from sys_param a where a.paramcode = 0004'
kh = oracle_db.select(sSQLTmp)
khdm = kh[0]['KHDM']
sSQLTmp = "select lpad(Seq_BarCode.nextVal,8,'0') as seqbarcode from dual "
Seq_BarCode = oracle_db.select(sSQLTmp)
sq_barcode = Seq_BarCode[0]['SEQBARCODE']
barcode = 'WT' + str(datetime.datetime.now().strftime('%Y%m%d')) + khdm + sq_barcode
sSQLCk = "insert into web_checkdetail(id,billno,billdate,billtype,barcode,certno,clienttype,clientname,parkcode, "\
"parkprice,ticketcode,ticketname,ticketmodel,ticketmodelname,ticketmodelprice,tickettype,ticketkindcode,"\
"seasontype,usercount,useflag,leftcount,begindate,invalidate) values ( "\
"seq_webbillid.nextval,'" +strbillno + "',sysdate,'pdc','" +barcode + "','" +scertno + "','02', "\
"'" +sclientname + "','" +parkcode + "','" +str(ticketprice) + "','" +ticketcode + "','" +ticketname + "', '"\
+sticketmodelcode + "','" +sticketmodelname + "','" +str(cprice) + "',100001,'" + ticketkind + "', '"\
+str(seasontype) + "','" + str(ncount) + "',0,'" + str(ncount) + "',date '"+startdate+"',date '"+enddate+"')"
sSQLCk_exc = "insert into web_checkdetail(id,billno,billdate,billtype,barcode,certno,clienttype,clientname,parkcode, "\
"parkprice,ticketcode,ticketname,ticketmodel,ticketmodelname,ticketmodelprice,tickettype,ticketkindcode,"\
"seasontype,usercount,useflag,leftcount,begindate,invalidate) values ( "\
"seq_webbillid.nextval,?,sysdate,'pdc',?,?,'02', "\
"?,?,?,?,?,?,?,? "\
",100001,?,? "\
",?,0,?,to_date(?,'yyyy-mm-dd'),to_date(?,'yyyy-mm-dd'))"
sSQLCk_exc = sql_web_checkdetail
with open(sDir, 'a') as f:
f.write(str(sSQLCk_exc.encode('gbk')) +'\n')
oracle_db.update(str(sSQLCk_exc.encode('gbk')),strbillno,barcode,scertno,sclientname,parkcode,str(ticketprice),ticketcode,ticketname,
sticketmodelcode,sticketmodelname,str(cprice),ticketkind,str(seasontype),str(ncount),str(ncount),startdate,enddate)
return 'success'