python tkinter核销应收
读取系统导出excel文件，自动进行逐笔核销
项目组织：
################################
main.py #系统进入函数
window文件夹 #附属窗口组织文件
---->check_ctrip.py 核销携程应收窗口
---->check_meituan.py 核销美团窗口
operate.py 存放功能函数
data_manage.py 数据管理的文件
###############################
代码如下：
main.py
import tkinter as tk
import window
from window.check_ctrip import CHECK_CTRIP
from window.check_meituan import CHECK_MEITUAN
class MAIN:
def __init__(self, data_name):
self.data_name = data_name
self.window = tk.Tk()
self.window.title('应收管理系统')
winw = 1400
winh = 700
scrw = self.window.winfo_screenwidth()
scrh = self.window.winfo_screenheight()
x = (scrw-winw)/2
y = (scrh-winh)/2
self.window.geometry('%dx%d+%d+%d'%(winw, winh, x, y))
menu1 = tk.Menu(self.window)
menu2_1 = tk.Menu(menu1, tearoff=False)
menu1.add_cascade(label='核销中介', menu=menu2_1)
menu2_1.add_command(label='线下携程', command=self.check_ctrip)
menu2_1.add_command(label='线下美团', command=self.check_meituan)
self.window.config(menu=menu1)
self.init_frame = tk.Frame(self.window, width = winw, height=winh)
self.init_frame.grid(row=0, column=0)
self.window.mainloop()
def _init_window(self):
try:
self.init_frame.destroy()
except:
pass
self.init_frame = tk.Frame(self.window, width=1400, height=700)
self.init_frame.grid(row=0, column=0)
return self.init_frame
def check_ctrip(self):
CHECK_CTRIP(master=self._init_window(), data_name=self.data_name)
def check_meituan(self):
CHECK_MEITUAN(master=self._init_window(), data_name=self.data_name)
if __name__ == '__main__':
data_name = 'test.db'
MAIN(data_name=data_name)
check_ctrip.py
import tkinter as tk
import tkinter.ttk as ttk
import operate
from tkinter.filedialog import *
from decimal import Decimal
import time
class CHECK_CTRIP:
def __init__(self, master, data_name):
self.operate = operate.CHECK_CTRIP(data_name=data_name)
self.master = master
self.operate_frame = tk.Frame(self.master, width=1400, height=100, bg='#00FFFF')
self.show_frame = tk.Frame(self.master, width=1400, height=600, bg='#F0FFFF')
self.operate_frame.grid(row=0, column=0, pady=5)
self.show_frame.grid(row=1, column=0, pady=5)
tk.Label(self.operate_frame, text='携程核销', width='50', height='2', font='6') \
.grid(row=0, column=1, columnspan=2, pady=5)
tk.Button(self.operate_frame, text='导入支付数据', width='25', height='2', font='1', command=self.get_pay_data) \
.grid(row=1, column=0, padx=10)
tk.Button(self.operate_frame, text='导入到账数据', width='25', height='2', font='1', command=self.get_rec_data) \
.grid(row=1, column=1, padx=10)
tk.Button(self.operate_frame, text='核销数据', width='25', height='2', font='1', command=self.check_data) \
.grid(row=1, column=2, padx=10)
tk.Button(self.operate_frame, text='导出数据', width='25', height='2', font='1', command=self.get_res_data) \
.grid(row=1, column=3, padx=10)
def _init_show_frame(self):
try:
self.show_frame.destroy()
except:
pass
self.show_frame = tk.Frame(self.master, width=1400, height=600, bg='#F0FFFF')
self.show_frame.grid(row=1, column=0, pady=5)
return self.show_frame
def get_pay_data(self):
GET_PAY_DATA(master=self._init_show_frame(), operate=self.operate)
def get_rec_data(self):
GET_REC_DATA(master=self._init_show_frame(), operate=self.operate)
def check_data(self):
CHECK_DATA(master=self._init_show_frame(), operate=self.operate)
def get_res_data(self):
GET_RES_DATA(master=self._init_show_frame(), operate=self.operate)
class GET_PAY_DATA:
def __init__(self, master, operate):
self.master = tk.Frame(master, width=1400, height=650, bg='#F0FFFF')
self.master.grid(row=0, column=0)
self.operate = operate
self.total_account_types = []
self.choice_tags = []
self.choice_pay_types = []
self.total_data_num = 1
tk.Button(self.master, text='打开存放pms数据的文件夹', command=self.open_pms_files_path).grid(row=0,column=0,pady=10)
def open_pms_files_path(self):
pms_files_path = askdirectory(title='打开存放pms数据的文件夹')
self.operate.pms_path=pms_files_path
self.total_account_types = self.operate.get_total_account_types()
num = len(self.total_account_types)
for i in range(num):
v = tk.IntVar()
checkbox = tk.Checkbutton(self.master,
text=self.total_account_types[i],
variable=v,
selectcolor='#00ffff',
padx=10)
checkbox.grid(row=int(i / 6)+1, column=int(i % 6), sticky=tk.W)
self.choice_tags.append(v)
btn1 = tk.Button(self.master, text='选择支付方式', font='28', bg='#EFB4DE', command=self.get_type)
btn2 = tk.Button(self.master, text='获取支付数据', font='28', bg='#EFB4DE', command=self.get_data)
btn3 = tk.Button(self.master, text='完成获取数据', font='28', bg='#EFB4DE', command=self.finish)
btn1.grid(row=int(num / 6) + 2,
column=0,
pady=10)
btn2.grid(row=int(num / 6) + 2,
column=1,
pady=10)
btn3.grid(row=int(num / 6) + 2,
column=2,
pady=10)
self.text = tk.Text(self.master, width=135, height=5)
self.text.grid(row=int(num / 6) + 3, column=0, columnspan=6)
tk.Label(self.master, text='获取数据进度', font='28').grid(row=int(num / 6) + 4, column=0, pady=10)
self.rectangle_width = 800
self.canvas = tk.Canvas(self.master, width=self.rectangle_width, height=22, bg='white')
self.canvas.grid(row=int(num / 6) + 4, column=1, columnspan=5)
def get_type(self):
self.choice_pay_types = []
for i in range(len(self.choice_tags)):
if self.choice_tags[i].get() == 1:
self.choice_pay_types.append(self.total_account_types[i])
insert_str = ""
for item in self.choice_pay_types:
insert_str = insert_str + str(item) + '\n\n'
self.text.delete(1.0, tk.END)
self.text.insert(tk.INSERT, insert_str)
fill_line = self.canvas.create_rectangle(1.5, 1.5, 0, 23, width=0, fill="white")
x = self.total_data_num # 未知变量，可更改
n = self.rectangle_width / x # 465是矩形填充满的次数
for t in range(x):
n = n + self.rectangle_width / x
# 以矩形的长度作为变量值更新
self.canvas.coords(fill_line, (0, 0, n, 60))
self.master.update()
time.sleep(0) # 时间为0，即飞速清空进度条
def get_data(self):
data = self.operate.get_check_pms_data(choice_pay_types=self.choice_pay_types)
self.total_data_num = len(data)
if self.total_data_num == 0:
pass
else:
fill_line = self.canvas.create_rectangle(1.5, 1.5, 0, 23, width=0, fill="green")
x = self.total_data_num # 未知变量，可更改
n = self.rectangle_width / x
for i in range(x):
row = data[i]
self.operate.insert_check_pms_row_data(row)
n = n + self.rectangle_width / x
self.canvas.coords(fill_line, (0, 0, n, 60))
self.master.update()
time.sleep(0.1)
def finish(self):
self.master.destroy()
class GET_REC_DATA:
def __init__(self,master, operate):
self.master = tk.Frame(master)
self.master.grid(row=0, column=0)
self.operate = operate
self.entry1 = tk.Entry(self.master, width=20)
self.entry2 = tk.Entry(self.master, width=20)
self.entry3 = tk.Entry(self.master, width=20)
self.entry4 = tk.Entry(self.master, width=20)
self.entry5 = tk.Entry(self.master, width=20)
self.entry6 = tk.Entry(self.master, width=20)
self.entry7 = tk.Entry(self.master, width=20)
self.tree = ttk.Treeview(self.master, columns=('col1', 'col2', 'col3'), show='headings', height=20)
self.tree.heading('col1', text='代码', anchor='w')
self.tree.heading('col2', text='行标', anchor='w')
self.tree.heading('col3', text='首行数据', anchor='w')
self.tree.column('col1', width=100)
self.tree.column('col2', width=150)
self.tree.column('col3', width=200)
self.tree.grid(row=1, column=0, rowspan=10)
tk.Label(self.master, text='输入左侧对应代码', width=50).grid(row=0, column=1, columnspan=2)
tk.Label(self.master, text='到账日期（例：8月22日）：', width=30).grid(row=1, column=1, sticky=tk.E)
tk.Label(self.master, text='订单号：', width=30).grid(row=2, column=1, sticky=tk.E)
tk.Label(self.master, text='客人姓名', width=30).grid(row=3, column=1, sticky=tk.E)
tk.Label(self.master, text='入住日期', width=30).grid(row=4, column=1, sticky=tk.E)
tk.Label(self.master, text='离店日期', width=30).grid(row=5, column=1, sticky=tk.E)
tk.Label(self.master, text='底价（结算价）', width=30).grid(row=6, column=1, sticky=tk.E)
tk.Label(self.master, text='面价（售价）', width=30).grid(row=7, column=1, sticky=tk.E)
self.entry1.grid(row=1, column=2, sticky=tk.W, padx=5)
self.entry2.grid(row=2, column=2, sticky=tk.W, padx=5)
self.entry3.grid(row=3, column=2, sticky=tk.W, padx=5)
self.entry4.grid(row=4, column=2, sticky=tk.W, padx=5)
self.entry5.grid(row=5, column=2, sticky=tk.W, padx=5)
self.entry6.grid(row=6, column=2, sticky=tk.W, padx=5)
self.entry7.grid(row=7, column=2, sticky=tk.W, padx=5)
tk.Button(self.master, text='获取到账数据', width=25, fg='red', command=self.get_data).grid(row=8, column=1)
tk.Button(self.master, text='完成获取', width=25, fg='red', command=self.finish).grid(row=8, column=2)
self.rectangle_width = 350
self.canvas = tk.Canvas(self.master, width=self.rectangle_width, height=22, bg='white')
self.canvas.grid(row=9, column=1, columnspan=2)
tk.Button(self.master, text='打开到账数据的Exce文件', command=self.open_ctrip_file).grid(row=0,column=0,pady=10)
def open_ctrip_file(self):
ctrip_file_name = askopenfilename(title = '打开携程导出的Excel表格文件', filetypes = [('Excel文件', '.xls')])
self.operate.ctrip_file_name =ctrip_file_name
rec_data_heading = self.operate.get_rec_data_heading()
for item in self.tree.get_children():
self.tree.delete(item)
for i in range(len(rec_data_heading[0])):
self.tree.insert('', tk.END, values=(str(i), rec_data_heading[0][i], rec_data_heading[1][i]))
def get_data(self):
choice_cols = [
eval(self.entry2.get()),
eval(self.entry3.get()),
eval(self.entry4.get()),
eval(self.entry5.get()),
eval(self.entry6.get()),
eval(self.entry7.get())
]
data = self.operate.get_rec_data(choice_cols=choice_cols)
rec_date = self.entry1.get()
self.total_data_num = len(data)
fill_line = self.canvas.create_rectangle(1.5, 1.5, 0, 23, width=0, fill="green")
x = self.total_data_num # 未知变量，可更改
n = self.rectangle_width / x
for i in range(len(data)):
temp_row = [rec_date, ] + data[i]
self.operate.insert_check_ctrip_row_data(row=temp_row)
n = n + self.rectangle_width / x
self.canvas.coords(fill_line, (0, 0, n, 60))
self.master.update()
time.sleep(0.1)
def finish(self):
self.master.destroy()
class CHECK_DATA:
def __init__(self, master, operate):
self.master = tk.Frame(master, width=1400, height=650, bg='#F0FFFF')
self.master.grid(row=0, column=0)
self.operate = operate
self.frame1 = tk.Frame(self.master, width=650, height=300)
self.frame2 = tk.Frame(self.master, width=650, height=300)
self.frame3 = tk.Frame(self.master, width=650, height=300)
self.frame4 = tk.Frame(self.master, width=650, height=300)
self.frame5 = tk.Frame(self.master, width=90, heigh=300)
self.frame6 = tk.Frame(self.master, width=90, height=300)
self.frame1.grid(row=0, column=0)
self.frame2.grid(row=1, column=0)
self.frame3.grid(row=0, column=1)
self.frame4.grid(row=1, column=1)
self.frame5.grid(row=0, column=2)
self.frame6.grid(row=1, column=2)
# 四个表格滚动条
self.scr1 = tk.Scrollbar(self.frame1, orient=tk.VERTICAL)
self.scr2 = tk.Scrollbar(self.frame2, orient=tk.VERTICAL)
self.scr3 = tk.Scrollbar(self.frame3, orient=tk.VERTICAL)
self.scr4 = tk.Scrollbar(self.frame4, orient=tk.VERTICAL)
self.scr1.grid(row=1, column=1, sticky=tk.N + tk.S)
self.scr2.grid(row=1, column=1, sticky=tk.N + tk.S)
self.scr3.grid(row=1, column=1, sticky=tk.N + tk.S)
self.scr4.grid(row=1, column=1, sticky=tk.N + tk.S)
# Frame1的内容
tk.Label(self.frame1, text='已经核销的pms支付数据', width='20', height=2).grid(row=0, column=0, pady=2, columnspan=2)
self.tree1 = ttk.Treeview(
self.frame1,
column=('detail_num', 'room', 'name', 'operator', 'account_type', 'money', 'create', 'note'),
show='headings',
height=10,
yscrollcommand=self.scr1.set,
)
self.tree1.heading('detail_num', text='账目明细')
self.tree1.heading('room', text='房号')
self.tree1.heading('name', text='姓名')
self.tree1.heading('operator', text='操作员')
self.tree1.heading('account_type', text='账目类型')
self.tree1.heading('money', text='金额')
self.tree1.heading('create', text='操作时间')
self.tree1.heading('note', text='备注')
self.tree1.column("detail_num", minwidth=0, width=140)
self.tree1.column("room", minwidth=0, width=50)
self.tree1.column("name", minwidth=0, width=70)
self.tree1.column("operator", minwidth=0, width=70)
self.tree1.column("account_type", minwidth=0, width=100)
self.tree1.column("money", minwidth=0, width=50)
self.tree1.column("create", minwidth=0, width=140)
self.tree1.column("note", minwidth=0, width=100)
self.tree1.grid(row=1, column=0, pady=2)
# Frame2的内容
self.frame2_head = tk.Frame(self.frame2, width=650, height=10)
self.frame2_head.grid(row=0, column=0, pady=2, columnspan=2, sticky=tk.W + tk.E)
tk.Label(self.frame2_head, text='未核销的pms支付数据', width='60', height=2).grid(row=0, column=0, pady=2, sticky=tk.W)
tk.Label(self.frame2_head, text='入住人姓名：', width='10', height=2).grid(row=0, column=1, pady=2, sticky=tk.W)
self.entry1 = tk.Entry(self.frame2_head, width=10)
self.entry1.grid(row=0, column=2, sticky=tk.W)
tk.Button(self.frame2_head, text='查找', width='10', height=1, command=self.select_in_name).grid(row=0, column=3,
sticky=tk.W,
padx=3)
self.tree2 = ttk.Treeview(
self.frame2,
column=('detail_num', 'room', 'name', 'operator', 'account_type', 'money', 'create', 'note'),
show='headings',
height=10,
yscrollcommand=self.scr2.set,
)
self.tree2.heading('detail_num', text='账目明细')
self.tree2.heading('room', text='房号')
self.tree2.heading('name', text='姓名')
self.tree2.heading('operator', text='操作员')
self.tree2.heading('account_type', text='账目类型')
self.tree2.heading('money', text='金额')
self.tree2.heading('create', text='操作时间')
self.tree2.heading('note', text='备注')
self.tree2.column("detail_num", minwidth=0, width=140)
self.tree2.column("room", minwidth=0, width=50)
self.tree2.column("name", minwidth=0, width=70)
self.tree2.column("operator", minwidth=0, width=70)
self.tree2.column("account_type", minwidth=0, width=100)
self.tree2.column("money", minwidth=0, width=50)
self.tree2.column("create", minwidth=0, width=140)
self.tree2.column("note", minwidth=0, width=100)
self.tree2.grid(row=1, column=0, pady=2)
# Frame3的内容
tk.Label(self.frame3, text='已核销核销的携程到账数据', width='20', height=2).grid(row=0, column=0, pady=2, columnspan=2)
self.tree3 = ttk.Treeview(self.frame3,
column=(
'rec_date', 'bill_no', 'rec_name', 'indate', 'outdate', 'rec_money', 'tra_money'),
show='headings',
height=10,
yscrollcommand=self.scr3.set, )
self.tree3.heading('rec_date', text='到账日期')
self.tree3.heading('bill_no', text='订单号')
self.tree3.heading('rec_name', text='客人姓名')
self.tree3.heading('indate', text='入住日期')
self.tree3.heading('outdate', text='离店日期')
self.tree3.heading('rec_money', text='底价')
self.tree3.heading('tra_money', text='面价')
self.tree3.column('rec_date', minwidth=0, width=75)
self.tree3.column("bill_no", minwidth=0, width=100)
self.tree3.column("rec_name", minwidth=0, width=100)
self.tree3.column("indate", minwidth=0, width=80)
self.tree3.column("outdate", minwidth=0, width=80)
self.tree3.column("rec_money", minwidth=0, width=60)
self.tree3.column("tra_money", minwidth=0, width=60)
self.tree3.grid(row=1, column=0)
# Frame4的内容
self.frame4_head = tk.Frame(self.frame4, width=650, height=10)
self.frame4_head.grid(row=0, column=0, pady=2, columnspan=2, sticky=tk.W + tk.E)
tk.Label(self.frame4_head, text='未核销的携程到账数据', width='40', height=2).grid(row=0, column=0, pady=2, sticky=tk.W)
tk.Label(self.frame4_head, text='预订人姓名：', width='10', height=2).grid(row=0, column=1, pady=2, sticky=tk.W)
self.entry2 = tk.Entry(self.frame4_head, width=10)
self.entry2.grid(row=0, column=2, sticky=tk.W)
tk.Button(self.frame4_head, text='查找', width='10', height=1, command=self.select_book_name).grid(row=0,
column=3,
sticky=tk.W,
padx=3)
self.tree4 = ttk.Treeview(self.frame4,
column=(
'rec_date', 'bill_no', 'rec_name', 'indate', 'outdate', 'rec_money', 'tra_money'),
show='headings',
height=10,
yscrollcommand=self.scr4.set, )
self.tree4.heading('rec_date', text='到账日期')
self.tree4.heading('bill_no', text='订单号')
self.tree4.heading('rec_name', text='客人姓名')
self.tree4.heading('indate', text='入住日期')
self.tree4.heading('outdate', text='离店日期')
self.tree4.heading('rec_money', text='底价')
self.tree4.heading('tra_money', text='面价')
self.tree4.column('rec_date', minwidth=0, width=75)
self.tree4.column("bill_no", minwidth=0, width=100)
self.tree4.column("rec_name", minwidth=0, width=100)
self.tree4.column("indate", minwidth=0, width=80)
self.tree4.column("outdate", minwidth=0, width=80)
self.tree4.column("rec_money", minwidth=0, width=60)
self.tree4.column("tra_money", minwidth=0, width=60)
self.tree4.grid(row=1, column=0)
# Frame5的内容
tk.Button(self.frame5, text='开始核销', width='10', height=2, command=self.start_check).grid(row=0, column=0,
pady=5)
tk.Button(self.frame5, text='核验核销', width='10', height=2, command=self.select_rec_data).grid(row=1, column=0,
pady=5)
tk.Button(self.frame5, text='上传核销', width='10', height=2, command=self.up_auto_check_data).grid(row=2, column=0,
pady=5)
# Frame6的内容
tk.Button(self.frame6, text='手工核销', width='10', height=2, command=self.per_check).grid(row=0, column=0, pady=5)
tk.Button(self.frame6, text='更改支付方式', width='10', height=2, command=self.change_pms_rec_type).grid(row=1,
column=0,
pady=5)
tk.Button(self.frame6, text='完成核销', fg='red', width='10', height=2, command=self.end_check).grid(row=2,
column=0,
pady=5)
self.scr1.config(command=self.tree1.yview)
self.scr2.config(command=self.tree2.yview)
self.scr3.config(command=self.tree3.yview)
self.scr4.config(command=self.tree4.yview)
self.v = tk.IntVar()
self.pms_choice_data = []
self.pms_choice_item = None
def start_check(self):
global check_result
check_result = self.operate.check()
pms_received = check_result[0]
for row in pms_received:
self.tree1.insert('', tk.END, values=row[1:])
ctrip_check = check_result[1]
for row in ctrip_check:
self.tree3.insert('', tk.END, values=row[1:])
pms_unreceived = check_result[2]
for row in pms_unreceived:
self.tree2.insert('', tk.END, values=row[1:])
ctrip_uncheck = check_result[3]
for row in ctrip_uncheck:
self.tree4.insert('', tk.END, values=row[1:])
def end_check(self):
#self.up_unrec_pms_data()
#self.up_ctrip_relay_data()
self.frame1.destroy()
self.frame2.destroy()
self.frame3.destroy()
self.frame4.destroy()
self.frame5.destroy()
self.frame6.destroy()
def select_rec_data(self):
pms_choice_item = self.tree1.selection()
pms_choice_data = []
ctrip_items = self.tree3.get_children()
if pms_choice_item == ():
print('选择待查询的支付数据')
else:
pms_received = check_result[0]
pms_choice_data_dict = self.tree1.set(pms_choice_item)
for row in pms_received:
if row[1] == pms_choice_data_dict['detail_num']:
pms_choice_data = row
break
for item in ctrip_items:
ctrip_data_row = self.tree3.set(item)
if ctrip_data_row['bill_no'] == pms_choice_data[11]:
self.tree3.selection_toggle(item)
break
def up_auto_check_data(self):
pms_items = self.tree1.get_children()
pms_data = []
ctrip_items = self.tree3.get_children()
ctrip_data = []
for item in pms_items:
pms_data_row_dict = self.tree1.set(item)
self.tree1.delete(item)
temp_row = []
for k in pms_data_row_dict.keys():
temp_row.append(pms_data_row_dict[k])
pms_data.append(temp_row)
for item in ctrip_items:
ctrip_data_row_dict = self.tree3.set(item)
self.tree3.delete(item)
temp_row = []
for k in ctrip_data_row_dict.keys():
temp_row.append(ctrip_data_row_dict[k])
ctrip_data.append(temp_row)
for n in range(len(pms_data)):
insert_row = pms_data[n] + ['携程'] + ctrip_data[n]
self.operate.insert_pms_db_row_data(insert_row)
def select_in_name(self):
in_name = self.entry1.get()
pms_items = self.tree2.get_children()
for item in pms_items:
pms_data_row_dict = self.tree2.set(item)
if in_name in pms_data_row_dict['name']:
self.tree2.selection_toggle(item)
def select_book_name(self):
book_name = self.entry2.get()
ctrip_items = self.tree4.get_children()
for item in ctrip_items:
ctrip_data_row_dict = self.tree4.set(item)
if book_name in ctrip_data_row_dict['rec_name']:
self.tree4.selection_toggle(item)
def per_check(self):
pms_choice_items = self.tree2.selection()
pms_choice_data = []
ctrip_choice_items = self.tree4.selection()
ctrip_choice_data = []
if pms_choice_items == () or ctrip_choice_items == ():
pass
else:
for item in pms_choice_items:
pms_row_dict = self.tree2.set(item)
self.tree2.delete(item)
temp_row = []
for k in pms_row_dict.keys():
temp_row.append(pms_row_dict[k])
pms_choice_data.append(temp_row)
for item in ctrip_choice_items:
ctrip_row_dict = self.tree4.set(item)
self.tree4.delete(item)
temp_row = []
for k in ctrip_row_dict.keys():
temp_row.append(ctrip_row_dict[k])
ctrip_choice_data.append(temp_row)
unexist_ctrip_row = [ctrip_choice_data[0][0], ctrip_choice_data[0][1], '', '', '', '', '']
unexist_ctrip_data = []
for n in range(len(pms_choice_data) - len(ctrip_choice_data)):
unexist_ctrip_data.append(unexist_ctrip_row)
if len(pms_choice_data) == len(ctrip_choice_data):
pass
else:
ctrip_choice_data.extend(unexist_ctrip_data)
for n in range(len(pms_choice_data)):
insert_row = pms_choice_data[n] + ['携程'] + ctrip_choice_data[n]
self.operate.insert_pms_db_row_data(insert_row=insert_row)
"""def up_ctrip_relay_data(self):
self.operate.delete_check_ctrip_db()
ctrip_items = self.tree4.get_children()
ctrip_data = []
for item in ctrip_items:
temp_row = []
ctrip_data_dict = self.tree4.set(item)
self.tree4.delete(item)
for k in ctrip_data_dict.keys():
temp_row.append(ctrip_data_dict[k])
ctrip_data.append(temp_row)
self.operate.insert_check_ctrip_row_data(row=temp_row)
"""
"""def up_unrec_pms_data(self):
self.operate.delete_check_pms_db()
pms_items = self.tree2.get_children()
pms_data = []
for item in pms_items:
pms_row_dict = self.tree2.set(item)
self.tree2.delete(item)
temp_row = []
for k in pms_row_dict.keys():
temp_row.append(pms_row_dict[k])
pms_data.append(temp_row)
unexist_ctrip_row = ['', '', '', '', '', '', '']
for row in pms_data:
insert_row = row + ['携程'] + unexist_ctrip_row
self.operate.insert_pms_db_row_data(insert_row)"""
def up_data(self):
self.tree2.delete(self.pms_choice_item)
choice_value = self.v.get()
self.operate.change_rec_type(choice_value=choice_value, insert_row=self.pms_choice_data)
top_win.destroy()
def change_pms_rec_type(self):
global top_win
global aim_label
self.pms_choice_data = []
self.pms_choice_item = self.tree2.selection()
pms_choice_data_dict = self.tree2.set(self.pms_choice_item)
for k in pms_choice_data_dict.keys():
self.pms_choice_data.append(pms_choice_data_dict[k])
top_win = tk.Toplevel()
top_win.geometry('400x250+800+400')
top_win.title('更改核销方式')
top_win.configure(bg='#D8EBB8')
tk.Label(top_win, text='选择实际的核销方式').grid(row=0, column=0)
meituan = tk.Radiobutton(top_win, text='更改为美团', variable=self.v, value=1, selectcolor='#F1D4C9')
xianjin = tk.Radiobutton(top_win, text='更改为现金', variable=self.v, value=2, selectcolor='#F1D4C9')
yinlian = tk.Radiobutton(top_win, text='更改为银联pos机', variable=self.v, value=3, selectcolor='#F1D4C9')
jiqiren = tk.Radiobutton(top_win, text='更改为机器人', variable=self.v, value=4, selectcolor='#F1D4C9')
meituan.grid(row=1, column=0, sticky=tk.W)
xianjin.grid(row=2, column=0, sticky=tk.W)
yinlian.grid(row=3, column=0, sticky=tk.W)
jiqiren.grid(row=4, column=0, sticky=tk.W)
btn = tk.Button(top_win, text='提交', bg='#F1C57E', width=10, relief='groove', command=self.up_data)
btn.grid(row=5, column=0, sticky=tk.W)
class GET_RES_DATA:
def __init__(self, master, operate):
self.master = tk.Frame(master, width=1400, height=650, bg='#F0FFFF')
self.master.grid(row=0, column=0)
self.operate = operate
self.frame1 = tk.Frame(self.master, width=1400, height=100)
self.frame2 = tk.Frame(self.master, width=1400, height=550)
self.frame1.grid(row=0, column=0,pady=10)
self.frame2.grid(row=1, column=0,pady=10)
label_width=20
tk.Button(self.frame1, text='导\n出\n汇\n总\n数', width=5, height=7).grid(row=0, column=0, rowspan=3)
tk.Button(self.frame1,text='携程到账', width=label_width, height=2, command=self.ctrip_look).grid(row=0,column=1)
tk.Button(self.frame1, text='美团到账', width=label_width, height=2, command=self.meituan_look).grid(row=0, column=2)
tk.Button(self.frame1, text='银联pos', width=label_width, height=2, command=self.pos_look).grid(row=0, column=3)
tk.Button(self.frame1, text='现金', width=label_width, height=2, command=self.xianjin_look).grid(row=0, column=4)
tk.Button(self.frame1, text='机器人', width=label_width, height=2, command=self.jiqiren_look).grid(row=0, column=5)
tk.Button(self.frame1,text='携程未到账', width=label_width,height=2, command=self.ctrip_unrec_look).grid(row=1,column=1)
tk.Button(self.frame1, text='美团未到账', width=label_width, height=2, command=self.meituan_unrec_look).grid(row=1, column=2)
tk.Button(self.frame1, text='导出明细', width=label_width, height=2, command=self.export_data).grid(row=2,column=1)
tk.Button(self.frame1, text='导出明细', width=label_width, height=2, command=self.export_data).grid(row=2, column=2)
tk.Button(self.frame1, text='导出明细', width=label_width, height=2, command=self.export_data).grid(row=2, column=3)
tk.Button(self.frame1, text='导出明细', width=label_width, height=2, command=self.export_data).grid(row=2, column=4)
tk.Button(self.frame1, text='导出明细', width=label_width, height=2, command=self.export_data).grid(row=2, column=5)
tk.Button(self.frame1, text='完\n成\n导\n出',width=5,height=7, command=self.end_look).grid(row=0,column=6,rowspan=3)
self.label = tk.Label(self.frame2,width=50,height=2)
self.label.grid(row=0,column=0, columnspan=2)
self.scr1 = tk.Scrollbar(self.frame2, orient=tk.VERTICAL)
self.scr1.grid(row=1, column=1, sticky=tk.N + tk.S)
self.tree1 = ttk.Treeview(
self.frame2,
column=('detail_num', 'room', 'name', 'operator', 'account_type', 'money', 'create', 'note', 'different', 'tra_money', 'rec_money', 'bill_no', 'rec_date'),
show='headings',
height=15,
yscrollcommand=self.scr1.set,
)
self.tree1.heading('detail_num', text='账目明细')
self.tree1.heading('room', text='房号')
self.tree1.heading('name', text='姓名')
self.tree1.heading('operator', text='操作员')
self.tree1.heading('account_type', text='账目类型')
self.tree1.heading('money', text='金额')
self.tree1.heading('create', text='操作时间')
self.tree1.heading('note', text='备注')
self.tree1.heading('different', text='差额')
self.tree1.heading('tra_money', text='中介后台金额')
self.tree1.heading('rec_money', text='实际到账额')
self.tree1.heading('bill_no', text='预订单号')
self.tree1.heading('rec_date', text='到账日期')
self.tree1.column("detail_num", minwidth=0, width=200)
self.tree1.column("room", minwidth=0, width=50)
self.tree1.column("name", minwidth=0, width=80)
self.tree1.column("operator", minwidth=0, width=80)
self.tree1.column("account_type", minwidth=0, width=100)
self.tree1.column("money", minwidth=0, width=70)
self.tree1.column("create", minwidth=0, width=160)
self.tree1.column("note", minwidth=0, width=140)
self.tree1.column("different", minwidth=0, width=70)
self.tree1.column('tra_money', minwidth=0, width=80)
self.tree1.column("rec_money", minwidth=0, width=70)
self.tree1.column('bill_no', minwidth=0, width=100)
self.tree1.column('rec_date', minwidth=0, width=100)
self.tree1.grid(row=1, column=0, pady=2)
self.scr1.config(command=self.tree1.yview)
def _get_different(self, val1, val2):
if val1 == '':
val1 = Decimal('0')
else:
val1 = Decimal(str(val1))
if val2 == '':
val2 = Decimal('0')
else:
val2 = Decimal(str(val2))
return str(val1-val2)
def ctrip_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '携程到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_ctrip_data()
for row in data:
row = list(row)
insert_row = row[1:9] + [self._get_different(val1=row[6], val2=row[16])] + [row[16]]+[row[15]]+[row[11]]+[row[10]]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def ctrip_unrec_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '携程未到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_un_rec_pms_db(table_name='pms_ctrip_uncheck')
for row in data:
row = list(row)
insert_row = row[1:9]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def export_data(self):
label_text = self.label['text']
file_name = label_text + '.xls'
ctrip_data = []
ctrip_items = self.tree1.get_children()
for item in ctrip_items:
temp_row = []
ctrip_row_dict = self.tree1.set(item)
for k in ctrip_row_dict.keys():
temp_row.append(ctrip_row_dict[k])
ctrip_data.append(temp_row)
self.operate.write_data_list(data=ctrip_data, file_name=file_name)
def meituan_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '美团到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_meituan_data()
for row in data:
row = list(row)
insert_row = row[1:9] + [self._get_different(val1=row[6], val2=row[16])] + [row[16]]+[row[15]]+[row[11]]+[row[10]]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def meituan_unrec_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '美团未到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_un_rec_pms_db(table_name='pms_meituan_uncheck')
for row in data:
row = list(row)
insert_row = row[1:9]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def pos_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '银联pos到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_pos_data()
for row in data:
row = list(row)
insert_row = row[1:9]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def xianjin_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '现金到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_xianjin_data()
for row in data:
row = list(row)
insert_row = row[1:9]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def jiqiren_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '机器人到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_jiqiren_data()
for row in data:
row = list(row)
insert_row = row[1:9]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def end_look(self):
self.master.destroy()
check_meituan.py
import tkinter as tk
import tkinter.ttk as ttk
import operate
from tkinter.filedialog import *
from decimal import Decimal
import time
class CHECK_MEITUAN:
def __init__(self, master, data_name):
self.operate = operate.CHECK_MEITUAN(data_name=data_name)
self.master = master
self.operate_frame = tk.Frame(self.master, width=1400, height=100, bg='#00FFFF')
self.show_frame = tk.Frame(self.master, width=1400, height=600, bg='#F0FFFF')
self.operate_frame.grid(row=0, column=0, pady=5)
self.show_frame.grid(row=1, column=0, pady=5)
tk.Label(self.operate_frame, text='美团核销', width='50', height='2', font='6') \
.grid(row=0, column=1, columnspan=2, pady=5)
tk.Button(self.operate_frame, text='导入支付数据', width='25', height='2', font='1', command=self.get_pay_data) \
.grid(row=1, column=0, padx=10)
tk.Button(self.operate_frame, text='导入到账数据', width='25', height='2', font='1', command=self.get_rec_data) \
.grid(row=1, column=1, padx=10)
tk.Button(self.operate_frame, text='核销数据', width='25', height='2', font='1', command=self.check_data) \
.grid(row=1, column=2, padx=10)
tk.Button(self.operate_frame, text='导出数据', width='25', height='2', font='1', command=self.get_res_data) \
.grid(row=1, column=3, padx=10)
def _init_show_frame(self):
try:
self.show_frame.destroy()
except:
pass
self.show_frame = tk.Frame(self.master, width=1400, height=600, bg='#F0FFFF')
self.show_frame.grid(row=1, column=0, pady=5)
return self.show_frame
def get_pay_data(self):
GET_PAY_DATA(master=self._init_show_frame(), operate=self.operate)
def get_rec_data(self):
GET_REC_DATA(master=self._init_show_frame(), operate=self.operate)
def check_data(self):
CHECK_DATA(master=self._init_show_frame(), operate=self.operate)
def get_res_data(self):
GET_RES_DATA(master=self._init_show_frame(), operate=self.operate)
class GET_PAY_DATA:
def __init__(self, master, operate):
self.master = tk.Frame(master, width=1400, height=650, bg='#F0FFFF')
self.master.grid(row=0, column=0)
self.operate = operate
self.total_account_types = []
self.choice_tags = []
self.choice_pay_types = []
self.total_data_num = 1
tk.Button(self.master, text='打开存放pms数据的文件夹', command=self.open_pms_files_path).grid(row=0,column=0,pady=10)
def open_pms_files_path(self):
pms_files_path = askdirectory(title='打开存放pms数据的文件夹')
self.operate.pms_path=pms_files_path
self.total_account_types = self.operate.get_total_account_types()
num = len(self.total_account_types)
for i in range(num):
v = tk.IntVar()
checkbox = tk.Checkbutton(self.master,
text=self.total_account_types[i],
variable=v,
selectcolor='#00ffff',
padx=10)
checkbox.grid(row=int(i / 6)+1, column=int(i % 6), sticky=tk.W)
self.choice_tags.append(v)
btn1 = tk.Button(self.master, text='选择支付方式', font='28', bg='#EFB4DE', command=self.get_type)
btn2 = tk.Button(self.master, text='获取支付数据', font='28', bg='#EFB4DE', command=self.get_data)
btn3 = tk.Button(self.master, text='完成获取数据', font='28', bg='#EFB4DE', command=self.finish)
btn1.grid(row=int(num / 6) + 2,
column=0,
pady=10)
btn2.grid(row=int(num / 6) + 2,
column=1,
pady=10)
btn3.grid(row=int(num / 6) + 2,
column=2,
pady=10)
self.text = tk.Text(self.master, width=135, height=5)
self.text.grid(row=int(num / 6) + 3, column=0, columnspan=6)
tk.Label(self.master, text='获取数据进度', font='28').grid(row=int(num / 6) + 4, column=0, pady=10)
self.rectangle_width = 800
self.canvas = tk.Canvas(self.master, width=self.rectangle_width, height=22, bg='white')
self.canvas.grid(row=int(num / 6) + 4, column=1, columnspan=5)
def get_type(self):
self.choice_pay_types = []
for i in range(len(self.choice_tags)):
if self.choice_tags[i].get() == 1:
self.choice_pay_types.append(self.total_account_types[i])
insert_str = ""
for item in self.choice_pay_types:
insert_str = insert_str + str(item) + '\n\n'
self.text.delete(1.0, tk.END)
self.text.insert(tk.INSERT, insert_str)
fill_line = self.canvas.create_rectangle(1.5, 1.5, 0, 23, width=0, fill="white")
x = self.total_data_num # 未知变量，可更改
n = self.rectangle_width / x # 465是矩形填充满的次数
for t in range(x):
n = n + self.rectangle_width / x
# 以矩形的长度作为变量值更新
self.canvas.coords(fill_line, (0, 0, n, 60))
self.master.update()
time.sleep(0) # 时间为0，即飞速清空进度条
def get_data(self):
data = self.operate.get_check_pms_data(choice_pay_types=self.choice_pay_types)
self.total_data_num = len(data)
if self.total_data_num == 0:
pass
else:
fill_line = self.canvas.create_rectangle(1.5, 1.5, 0, 23, width=0, fill="green")
x = self.total_data_num # 未知变量，可更改
n = self.rectangle_width / x
for i in range(x):
row = data[i]
self.operate.insert_check_pms_row_data(row)
n = n + self.rectangle_width / x
self.canvas.coords(fill_line, (0, 0, n, 60))
self.master.update()
time.sleep(0.1)
def finish(self):
self.master.destroy()
class GET_REC_DATA:
def __init__(self,master, operate):
self.master = tk.Frame(master)
self.master.grid(row=0, column=0)
self.operate = operate
self.entry1 = tk.Entry(self.master, width=20)
self.entry2 = tk.Entry(self.master, width=20)
self.entry3 = tk.Entry(self.master, width=20)
self.entry4 = tk.Entry(self.master, width=20)
self.entry5 = tk.Entry(self.master, width=20)
self.entry6 = tk.Entry(self.master, width=20)
self.entry7 = tk.Entry(self.master, width=20)
self.entry8 = tk.Entry(self.master, width=20)
self.entry9 = tk.Entry(self.master, width=20)
self.entry10 = tk.Entry(self.master, width=20)
self.entry11 = tk.Entry(self.master, width=20)
self.tree = ttk.Treeview(self.master, columns=('col1', 'col2', 'col3'), show='headings', height=20)
self.tree.heading('col1', text='代码', anchor='w')
self.tree.heading('col2', text='行标', anchor='w')
self.tree.heading('col3', text='首行数据', anchor='w')
self.tree.column('col1', width=100)
self.tree.column('col2', width=150)
self.tree.column('col3', width=200)
self.tree.grid(row=1, column=0, rowspan=13)
tk.Label(self.master, text='输入左侧对应代码', width=50).grid(row=0, column=1, columnspan=2)
tk.Label(self.master, text='到账日期（例：8月22日）：', width=30).grid(row=1, column=1, sticky=tk.E)
tk.Label(self.master, text='订单号：', width=30).grid(row=2, column=1, sticky=tk.E)
tk.Label(self.master, text='客人姓名', width=30).grid(row=3, column=1, sticky=tk.E)
tk.Label(self.master, text='入住日期', width=30).grid(row=4, column=1, sticky=tk.E)
tk.Label(self.master, text='离店日期', width=30).grid(row=5, column=1, sticky=tk.E)
tk.Label(self.master, text='底价（结算价）', width=30).grid(row=6, column=1, sticky=tk.E)
tk.Label(self.master, text='订单底价', width=30).grid(row=7, column=1, sticky=tk.E)
tk.Label(self.master, text='商家自促销金额', width=30).grid(row=8, column=1, sticky=tk.E)
tk.Label(self.master, text='退款金额', width=30).grid(row=9, column=1, sticky=tk.E)
tk.Label(self.master, text='技术服务费', width=30).grid(row=10, column=1, sticky=tk.E)
tk.Label(self.master, text='退技术服务费', width=30).grid(row=11, column=1, sticky=tk.E)
self.entry1.grid(row=1, column=2, sticky=tk.W, padx=5)
self.entry2.grid(row=2, column=2, sticky=tk.W, padx=5)
self.entry3.grid(row=3, column=2, sticky=tk.W, padx=5)
self.entry4.grid(row=4, column=2, sticky=tk.W, padx=5)
self.entry5.grid(row=5, column=2, sticky=tk.W, padx=5)
self.entry6.grid(row=6, column=2, sticky=tk.W, padx=5)
self.entry7.grid(row=7, column=2, sticky=tk.W, padx=5)
self.entry8.grid(row=8, column=2, sticky=tk.W, padx=5)
self.entry9.grid(row=9, column=2, sticky=tk.W, padx=5)
self.entry10.grid(row=10, column=2, sticky=tk.W, padx=5)
self.entry11.grid(row=11, column=2, sticky=tk.W, padx=5)
tk.Button(self.master, text='获取到账数据', width=25, fg='red', command=self.get_data).grid(row=12, column=1)
tk.Button(self.master, text='完成获取', width=25, fg='red', command=self.finish).grid(row=12, column=2)
self.rectangle_width = 350
self.canvas = tk.Canvas(self.master, width=self.rectangle_width, height=22, bg='white')
self.canvas.grid(row=13, column=1, columnspan=2)
tk.Button(self.master, text='打开到账数据的Exce文件', command=self.open_ctrip_file).grid(row=0,column=0,pady=10)
def open_ctrip_file(self):
meituan_file_name = askopenfilename(title = '打开美团导出的Excel表格文件', filetypes = [('Excel文件', '.xlsx')])
self.operate.meituan_file_name =meituan_file_name
rec_data_heading = self.operate.get_rec_data_heading()
for item in self.tree.get_children():
self.tree.delete(item)
for i in range(len(rec_data_heading[0])):
self.tree.insert('', tk.END, values=(str(i), rec_data_heading[0][i], rec_data_heading[1][i]))
def get_row_sum(self, row):
s = Decimal('0')
for item in row:
s += Decimal(str(item))
return str(s)
def get_data(self):
choice_cols = [
eval(self.entry2.get()),
eval(self.entry3.get()),
eval(self.entry4.get()),
eval(self.entry5.get()),
eval(self.entry6.get()),
eval(self.entry7.get()),
eval(self.entry8.get()),
eval(self.entry9.get()),
eval(self.entry10.get()),
eval(self.entry11.get())
]
data = self.operate.get_rec_data(choice_cols=choice_cols)
rec_date = self.entry1.get()
self.total_data_num = len(data)
fill_line = self.canvas.create_rectangle(1.5, 1.5, 0, 23, width=0, fill="green")
x = self.total_data_num # 未知变量，可更改
n = self.rectangle_width / x
for i in range(len(data)):
temp_row = [rec_date, ] + data[i][:5] +[self.get_row_sum(data[i][5:])]
self.operate.insert_check_meituan_row_data(row=temp_row)
n = n + self.rectangle_width / x
self.canvas.coords(fill_line, (0, 0, n, 60))
self.master.update()
time.sleep(0.1)
def finish(self):
self.master.destroy()
class CHECK_DATA:
def __init__(self, master, operate):
self.master = tk.Frame(master, width=1400, height=650, bg='#F0FFFF')
self.master.grid(row=0, column=0)
self.operate = operate
self.frame1 = tk.Frame(self.master, width=650, height=300)
self.frame2 = tk.Frame(self.master, width=650, height=300)
self.frame3 = tk.Frame(self.master, width=650, height=300)
self.frame4 = tk.Frame(self.master, width=650, height=300)
self.frame5 = tk.Frame(self.master, width=90, heigh=300)
self.frame6 = tk.Frame(self.master, width=90, height=300)
self.frame1.grid(row=0, column=0)
self.frame2.grid(row=1, column=0)
self.frame3.grid(row=0, column=1)
self.frame4.grid(row=1, column=1)
self.frame5.grid(row=0, column=2)
self.frame6.grid(row=1, column=2)
# 四个表格滚动条
self.scr1 = tk.Scrollbar(self.frame1, orient=tk.VERTICAL)
self.scr2 = tk.Scrollbar(self.frame2, orient=tk.VERTICAL)
self.scr3 = tk.Scrollbar(self.frame3, orient=tk.VERTICAL)
self.scr4 = tk.Scrollbar(self.frame4, orient=tk.VERTICAL)
self.scr1.grid(row=1, column=1, sticky=tk.N + tk.S)
self.scr2.grid(row=1, column=1, sticky=tk.N + tk.S)
self.scr3.grid(row=1, column=1, sticky=tk.N + tk.S)
self.scr4.grid(row=1, column=1, sticky=tk.N + tk.S)
# Frame1的内容
tk.Label(self.frame1, text='已经核销的pms支付数据', width='20', height=2).grid(row=0, column=0, pady=2, columnspan=2)
self.tree1 = ttk.Treeview(
self.frame1,
column=('detail_num', 'room', 'name', 'operator', 'account_type', 'money', 'create', 'note'),
show='headings',
height=10,
yscrollcommand=self.scr1.set,
)
self.tree1.heading('detail_num', text='账目明细')
self.tree1.heading('room', text='房号')
self.tree1.heading('name', text='姓名')
self.tree1.heading('operator', text='操作员')
self.tree1.heading('account_type', text='账目类型')
self.tree1.heading('money', text='金额')
self.tree1.heading('create', text='操作时间')
self.tree1.heading('note', text='备注')
self.tree1.column("detail_num", minwidth=0, width=140)
self.tree1.column("room", minwidth=0, width=50)
self.tree1.column("name", minwidth=0, width=70)
self.tree1.column("operator", minwidth=0, width=70)
self.tree1.column("account_type", minwidth=0, width=100)
self.tree1.column("money", minwidth=0, width=50)
self.tree1.column("create", minwidth=0, width=140)
self.tree1.column("note", minwidth=0, width=100)
self.tree1.grid(row=1, column=0, pady=2)
# Frame2的内容
self.frame2_head = tk.Frame(self.frame2, width=650, height=10)
self.frame2_head.grid(row=0, column=0, pady=2, columnspan=2, sticky=tk.W + tk.E)
tk.Label(self.frame2_head, text='未核销的pms支付数据', width='60', height=2).grid(row=0, column=0, pady=2, sticky=tk.W)
tk.Label(self.frame2_head, text='入住人姓名：', width='10', height=2).grid(row=0, column=1, pady=2, sticky=tk.W)
self.entry1 = tk.Entry(self.frame2_head, width=10)
self.entry1.grid(row=0, column=2, sticky=tk.W)
tk.Button(self.frame2_head, text='查找', width='10', height=1, command=self.select_in_name).grid(row=0, column=3,
sticky=tk.W,
padx=3)
self.tree2 = ttk.Treeview(
self.frame2,
column=('detail_num', 'room', 'name', 'operator', 'account_type', 'money', 'create', 'note'),
show='headings',
height=10,
yscrollcommand=self.scr2.set,
)
self.tree2.heading('detail_num', text='账目明细')
self.tree2.heading('room', text='房号')
self.tree2.heading('name', text='姓名')
self.tree2.heading('operator', text='操作员')
self.tree2.heading('account_type', text='账目类型')
self.tree2.heading('money', text='金额')
self.tree2.heading('create', text='操作时间')
self.tree2.heading('note', text='备注')
self.tree2.column("detail_num", minwidth=0, width=140)
self.tree2.column("room", minwidth=0, width=50)
self.tree2.column("name", minwidth=0, width=70)
self.tree2.column("operator", minwidth=0, width=70)
self.tree2.column("account_type", minwidth=0, width=100)
self.tree2.column("money", minwidth=0, width=50)
self.tree2.column("create", minwidth=0, width=140)
self.tree2.column("note", minwidth=0, width=100)
self.tree2.grid(row=1, column=0, pady=2)
# Frame3的内容
tk.Label(self.frame3, text='已核销核销的携程到账数据', width='20', height=2).grid(row=0, column=0, pady=2, columnspan=2)
self.tree3 = ttk.Treeview(self.frame3,
column=(
'rec_date', 'bill_no', 'rec_name', 'indate', 'outdate', 'rec_money', 'tra_money'),
show='headings',
height=10,
yscrollcommand=self.scr3.set, )
self.tree3.heading('rec_date', text='到账日期')
self.tree3.heading('bill_no', text='订单号')
self.tree3.heading('rec_name', text='客人姓名')
self.tree3.heading('indate', text='入住日期')
self.tree3.heading('outdate', text='离店日期')
self.tree3.heading('rec_money', text='底价')
self.tree3.heading('tra_money', text='面价')
self.tree3.column('rec_date', minwidth=0, width=75)
self.tree3.column("bill_no", minwidth=0, width=100)
self.tree3.column("rec_name", minwidth=0, width=100)
self.tree3.column("indate", minwidth=0, width=80)
self.tree3.column("outdate", minwidth=0, width=80)
self.tree3.column("rec_money", minwidth=0, width=60)
self.tree3.column("tra_money", minwidth=0, width=60)
self.tree3.grid(row=1, column=0)
# Frame4的内容
self.frame4_head = tk.Frame(self.frame4, width=650, height=10)
self.frame4_head.grid(row=0, column=0, pady=2, columnspan=2, sticky=tk.W + tk.E)
tk.Label(self.frame4_head, text='未核销的携程到账数据', width='40', height=2).grid(row=0, column=0, pady=2, sticky=tk.W)
tk.Label(self.frame4_head, text='预订人姓名：', width='10', height=2).grid(row=0, column=1, pady=2, sticky=tk.W)
self.entry2 = tk.Entry(self.frame4_head, width=10)
self.entry2.grid(row=0, column=2, sticky=tk.W)
tk.Button(self.frame4_head, text='查找', width='10', height=1, command=self.select_book_name).grid(row=0,
column=3,
sticky=tk.W,
padx=3)
self.tree4 = ttk.Treeview(self.frame4,
column=(
'rec_date', 'bill_no', 'rec_name', 'indate', 'outdate', 'rec_money', 'tra_money'),
show='headings',
height=10,
yscrollcommand=self.scr4.set, )
self.tree4.heading('rec_date', text='到账日期')
self.tree4.heading('bill_no', text='订单号')
self.tree4.heading('rec_name', text='客人姓名')
self.tree4.heading('indate', text='入住日期')
self.tree4.heading('outdate', text='离店日期')
self.tree4.heading('rec_money', text='底价')
self.tree4.heading('tra_money', text='面价')
self.tree4.column('rec_date', minwidth=0, width=75)
self.tree4.column("bill_no", minwidth=0, width=100)
self.tree4.column("rec_name", minwidth=0, width=100)
self.tree4.column("indate", minwidth=0, width=80)
self.tree4.column("outdate", minwidth=0, width=80)
self.tree4.column("rec_money", minwidth=0, width=60)
self.tree4.column("tra_money", minwidth=0, width=60)
self.tree4.grid(row=1, column=0)
# Frame5的内容
tk.Button(self.frame5, text='开始核销', width='10', height=2, command=self.start_check).grid(row=0, column=0,
pady=5)
tk.Button(self.frame5, text='核验核销', width='10', height=2, command=self.select_rec_data).grid(row=1, column=0,
pady=5)
tk.Button(self.frame5, text='上传核销', width='10', height=2, command=self.up_auto_check_data).grid(row=2, column=0,
pady=5)
# Frame6的内容
tk.Button(self.frame6, text='手工核销', width='10', height=2, command=self.per_check).grid(row=0, column=0, pady=5)
tk.Button(self.frame6, text='更改支付方式', width='10', height=2, command=self.change_pms_rec_type).grid(row=1,
column=0,
pady=5)
tk.Button(self.frame6, text='完成核销', fg='red', width='10', height=2, command=self.end_check).grid(row=2,
column=0,
pady=5)
self.scr1.config(command=self.tree1.yview)
self.scr2.config(command=self.tree2.yview)
self.scr3.config(command=self.tree3.yview)
self.scr4.config(command=self.tree4.yview)
self.v = tk.IntVar()
self.pms_choice_data = []
self.pms_choice_item = None
def start_check(self):
global check_result
check_result = self.operate.check()
pms_received = check_result[0]
for row in pms_received:
self.tree1.insert('', tk.END, values=row[1:])
ctrip_check = check_result[1]
for row in ctrip_check:
self.tree3.insert('', tk.END, values=row[1:])
pms_unreceived = check_result[2]
for row in pms_unreceived:
self.tree2.insert('', tk.END, values=row[1:])
ctrip_uncheck = check_result[3]
for row in ctrip_uncheck:
self.tree4.insert('', tk.END, values=row[1:])
def end_check(self):
#self.up_unrec_pms_data()
#self.up_ctrip_relay_data()
self.frame1.destroy()
self.frame2.destroy()
self.frame3.destroy()
self.frame4.destroy()
self.frame5.destroy()
self.frame6.destroy()
def select_rec_data(self):
pms_choice_item = self.tree1.selection()
pms_choice_data = []
ctrip_items = self.tree3.get_children()
if pms_choice_item == ():
print('选择待查询的支付数据')
else:
pms_received = check_result[0]
pms_choice_data_dict = self.tree1.set(pms_choice_item)
for row in pms_received:
if row[1] == pms_choice_data_dict['detail_num']:
pms_choice_data = row
break
for item in ctrip_items:
ctrip_data_row = self.tree3.set(item)
if ctrip_data_row['bill_no'] == pms_choice_data[11]:
self.tree3.selection_toggle(item)
break
def up_auto_check_data(self):
pms_items = self.tree1.get_children()
pms_data = []
ctrip_items = self.tree3.get_children()
ctrip_data = []
for item in pms_items:
pms_data_row_dict = self.tree1.set(item)
self.tree1.delete(item)
temp_row = []
for k in pms_data_row_dict.keys():
temp_row.append(pms_data_row_dict[k])
pms_data.append(temp_row)
for item in ctrip_items:
ctrip_data_row_dict = self.tree3.set(item)
self.tree3.delete(item)
temp_row = []
for k in ctrip_data_row_dict.keys():
temp_row.append(ctrip_data_row_dict[k])
ctrip_data.append(temp_row)
for n in range(len(pms_data)):
insert_row = pms_data[n] + ['美团'] + ctrip_data[n]
self.operate.insert_pms_db_row_data(insert_row)
def select_in_name(self):
in_name = self.entry1.get()
pms_items = self.tree2.get_children()
for item in pms_items:
pms_data_row_dict = self.tree2.set(item)
if in_name in pms_data_row_dict['name']:
self.tree2.selection_toggle(item)
def select_book_name(self):
book_name = self.entry2.get()
ctrip_items = self.tree4.get_children()
for item in ctrip_items:
ctrip_data_row_dict = self.tree4.set(item)
if book_name in ctrip_data_row_dict['rec_name']:
self.tree4.selection_toggle(item)
def per_check(self):
pms_choice_items = self.tree2.selection()
pms_choice_data = []
ctrip_choice_items = self.tree4.selection()
ctrip_choice_data = []
if pms_choice_items == () or ctrip_choice_items == ():
pass
else:
for item in pms_choice_items:
pms_row_dict = self.tree2.set(item)
self.tree2.delete(item)
temp_row = []
for k in pms_row_dict.keys():
temp_row.append(pms_row_dict[k])
pms_choice_data.append(temp_row)
for item in ctrip_choice_items:
ctrip_row_dict = self.tree4.set(item)
self.tree4.delete(item)
temp_row = []
for k in ctrip_row_dict.keys():
temp_row.append(ctrip_row_dict[k])
ctrip_choice_data.append(temp_row)
unexist_ctrip_row = [ctrip_choice_data[0][0], ctrip_choice_data[0][1], '', '', '', '', '']
unexist_ctrip_data = []
for n in range(len(pms_choice_data) - len(ctrip_choice_data)):
unexist_ctrip_data.append(unexist_ctrip_row)
if len(pms_choice_data) == len(ctrip_choice_data):
pass
else:
ctrip_choice_data.extend(unexist_ctrip_data)
for n in range(len(pms_choice_data)):
insert_row = pms_choice_data[n] + ['携程'] + ctrip_choice_data[n]
self.operate.insert_pms_db_row_data(insert_row=insert_row)
"""def up_ctrip_relay_data(self):
self.operate.delete_check_ctrip_db()
ctrip_items = self.tree4.get_children()
ctrip_data = []
for item in ctrip_items:
temp_row = []
ctrip_data_dict = self.tree4.set(item)
self.tree4.delete(item)
for k in ctrip_data_dict.keys():
temp_row.append(ctrip_data_dict[k])
ctrip_data.append(temp_row)
self.operate.insert_check_ctrip_row_data(row=temp_row)
"""
"""def up_unrec_pms_data(self):
self.operate.delete_check_pms_db()
pms_items = self.tree2.get_children()
pms_data = []
for item in pms_items:
pms_row_dict = self.tree2.set(item)
self.tree2.delete(item)
temp_row = []
for k in pms_row_dict.keys():
temp_row.append(pms_row_dict[k])
pms_data.append(temp_row)
unexist_ctrip_row = ['', '', '', '', '', '', '']
for row in pms_data:
insert_row = row + ['携程'] + unexist_ctrip_row
self.operate.insert_pms_db_row_data(insert_row)"""
def up_data(self):
self.tree2.delete(self.pms_choice_item)
choice_value = self.v.get()
self.operate.change_rec_type(choice_value=choice_value, insert_row=self.pms_choice_data)
top_win.destroy()
def change_pms_rec_type(self):
global top_win
global aim_label
self.pms_choice_data = []
self.pms_choice_item = self.tree2.selection()
pms_choice_data_dict = self.tree2.set(self.pms_choice_item)
for k in pms_choice_data_dict.keys():
self.pms_choice_data.append(pms_choice_data_dict[k])
top_win = tk.Toplevel()
top_win.geometry('400x250+800+400')
top_win.title('更改核销方式')
top_win.configure(bg='#D8EBB8')
tk.Label(top_win, text='选择实际的核销方式').grid(row=0, column=0)
ctrip = tk.Radiobutton(top_win, text='更改为携程', variable=self.v, value=1, selectcolor='#F1D4C9')
xianjin = tk.Radiobutton(top_win, text='更改为现金', variable=self.v, value=2, selectcolor='#F1D4C9')
yinlian = tk.Radiobutton(top_win, text='更改为银联pos机', variable=self.v, value=3, selectcolor='#F1D4C9')
jiqiren = tk.Radiobutton(top_win, text='更改为机器人', variable=self.v, value=4, selectcolor='#F1D4C9')
ctrip.grid(row=1, column=0, sticky=tk.W)
xianjin.grid(row=2, column=0, sticky=tk.W)
yinlian.grid(row=3, column=0, sticky=tk.W)
jiqiren.grid(row=4, column=0, sticky=tk.W)
btn = tk.Button(top_win, text='提交', bg='#F1C57E', width=10, relief='groove', command=self.up_data)
btn.grid(row=5, column=0, sticky=tk.W)
class GET_RES_DATA:
def __init__(self, master, operate):
self.master = tk.Frame(master, width=1400, height=650, bg='#F0FFFF')
self.master.grid(row=0, column=0)
self.operate = operate
self.frame1 = tk.Frame(self.master, width=1400, height=100)
self.frame2 = tk.Frame(self.master, width=1400, height=550)
self.frame1.grid(row=0, column=0,pady=10)
self.frame2.grid(row=1, column=0,pady=10)
label_width=20
tk.Button(self.frame1, text='导\n出\n汇\n总\n数', width=5, height=7).grid(row=0, column=0, rowspan=3)
tk.Button(self.frame1,text='携程到账', width=label_width, height=2, command=self.ctrip_look).grid(row=0,column=1)
tk.Button(self.frame1, text='美团到账', width=label_width, height=2, command=self.meituan_look).grid(row=0, column=2)
tk.Button(self.frame1, text='银联pos', width=label_width, height=2, command=self.pos_look).grid(row=0, column=3)
tk.Button(self.frame1, text='现金', width=label_width, height=2, command=self.xianjin_look).grid(row=0, column=4)
tk.Button(self.frame1, text='机器人', width=label_width, height=2, command=self.jiqiren_look).grid(row=0, column=5)
tk.Button(self.frame1,text='携程未到账', width=label_width,height=2, command=self.ctrip_unrec_look).grid(row=1,column=1)
tk.Button(self.frame1, text='美团未到账', width=label_width, height=2, command=self.meituan_unrec_look).grid(row=1, column=2)
tk.Button(self.frame1, text='导出明细', width=label_width, height=2, command=self.export_data).grid(row=2,column=1)
tk.Button(self.frame1, text='导出明细', width=label_width, height=2, command=self.export_data).grid(row=2, column=2)
tk.Button(self.frame1, text='导出明细', width=label_width, height=2, command=self.export_data).grid(row=2, column=3)
tk.Button(self.frame1, text='导出明细', width=label_width, height=2, command=self.export_data).grid(row=2, column=4)
tk.Button(self.frame1, text='导出明细', width=label_width, height=2, command=self.export_data).grid(row=2, column=5)
tk.Button(self.frame1, text='完\n成\n导\n出',width=5,height=7, command=self.end_look).grid(row=0,column=6,rowspan=3)
self.label = tk.Label(self.frame2,width=50,height=2)
self.label.grid(row=0,column=0, columnspan=2)
self.scr1 = tk.Scrollbar(self.frame2, orient=tk.VERTICAL)
self.scr1.grid(row=1, column=1, sticky=tk.N + tk.S)
self.tree1 = ttk.Treeview(
self.frame2,
column=('detail_num', 'room', 'name', 'operator', 'account_type', 'money', 'create', 'note', 'different', 'tra_money', 'rec_money', 'bill_no', 'rec_date'),
show='headings',
height=15,
yscrollcommand=self.scr1.set,
)
self.tree1.heading('detail_num', text='账目明细')
self.tree1.heading('room', text='房号')
self.tree1.heading('name', text='姓名')
self.tree1.heading('operator', text='操作员')
self.tree1.heading('account_type', text='账目类型')
self.tree1.heading('money', text='金额')
self.tree1.heading('create', text='操作时间')
self.tree1.heading('note', text='备注')
self.tree1.heading('different', text='差额')
self.tree1.heading('tra_money', text='中介后台金额')
self.tree1.heading('rec_money', text='实际到账额')
self.tree1.heading('bill_no', text='预订单号')
self.tree1.heading('rec_date', text='到账日期')
self.tree1.column("detail_num", minwidth=0, width=200)
self.tree1.column("room", minwidth=0, width=50)
self.tree1.column("name", minwidth=0, width=80)
self.tree1.column("operator", minwidth=0, width=80)
self.tree1.column("account_type", minwidth=0, width=100)
self.tree1.column("money", minwidth=0, width=70)
self.tree1.column("create", minwidth=0, width=160)
self.tree1.column("note", minwidth=0, width=140)
self.tree1.column("different", minwidth=0, width=70)
self.tree1.column('tra_money', minwidth=0, width=80)
self.tree1.column("rec_money", minwidth=0, width=70)
self.tree1.column('bill_no', minwidth=0, width=100)
self.tree1.column('rec_date', minwidth=0, width=100)
self.tree1.grid(row=1, column=0, pady=2)
self.scr1.config(command=self.tree1.yview)
def _get_different(self, val1, val2):
if val1 == '':
val1 = Decimal('0')
else:
val1 = Decimal(str(val1))
if val2 == '':
val2 = Decimal('0')
else:
val2 = Decimal(str(val2))
return str(val1-val2)
def ctrip_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '携程到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_ctrip_data()
for row in data:
row = list(row)
insert_row = row[1:9] + [self._get_different(val1=row[6], val2=row[16])] + [row[16]]+[row[15]]+[row[11]]+[row[10]]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def ctrip_unrec_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '携程未到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_un_rec_pms_db(table_name='pms_ctrip_uncheck')
for row in data:
row = list(row)
insert_row = row[1:9]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def export_data(self):
label_text = self.label['text']
file_name = label_text + '.xls'
ctrip_data = []
ctrip_items = self.tree1.get_children()
for item in ctrip_items:
temp_row = []
ctrip_row_dict = self.tree1.set(item)
for k in ctrip_row_dict.keys():
temp_row.append(ctrip_row_dict[k])
ctrip_data.append(temp_row)
self.operate.write_data_list(data=ctrip_data, file_name=file_name)
def meituan_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '美团到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_meituan_data()
for row in data:
row = list(row)
insert_row = row[1:9] + [self._get_different(val1=row[6], val2=row[16])] + [row[16]]+[row[15]]+[row[11]]+[row[10]]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def meituan_unrec_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '美团未到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_un_rec_pms_db(table_name='pms_meituan_uncheck')
for row in data:
row = list(row)
insert_row = row[1:9]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def pos_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '银联pos到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_pos_data()
for row in data:
row = list(row)
insert_row = row[1:9]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def xianjin_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '现金到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_xianjin_data()
for row in data:
row = list(row)
insert_row = row[1:9]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def jiqiren_look(self):
show_table_items = self.tree1.get_children()
if show_table_items == ():
pass
else:
for item in show_table_items:
self.tree1.delete(item)
label_text = '机器人到账明细表'
self.label.configure(text=label_text)
insert_data = []
data = self.operate.get_jiqiren_data()
for row in data:
row = list(row)
insert_row = row[1:9]
insert_data.append(insert_row)
for row in insert_data:
self.tree1.insert('', tk.END, values=row)
def end_look(self):
self.master.destroy()
operate.py
import data_manage
from decimal import Decimal
import xlwt
class CHECK_CTRIP:
def __init__(self, data_name):
self.data_name = data_name
self.pms_unck = data_manage.PMS_UNCK(data_name=data_name, table_name='pms_ctrip_uncheck')
self.check_ctrip = data_manage.CHECK_OTA(data_name=data_name, table='check_ctrip')
self.pms_ctrip_checked = data_manage.PMS_CK(data_name=data_name, table_name='pms_ctrip_checked')
self.pms_path = ''
self.ctrip_file_name = ''
def get_total_account_types(self):
total_account_types = []
pms_data_dict = data_manage.PMS(pms_path=self.pms_path).get_pms_data_dict()
for account_type in pms_data_dict.keys():
total_account_types.append(str(account_type))
return total_account_types
def get_check_pms_data(self, choice_pay_types):
new_check_pms_data = []
pms_data_dict = data_manage.PMS(pms_path=self.pms_path).get_pms_data_dict()
for choice_type in choice_pay_types:
new_check_pms_data.extend(pms_data_dict[choice_type])
return new_check_pms_data
def insert_check_pms_row_data(self, row):
self.pms_unck.insert_row_data(row)
def get_rec_data_heading(self):
ctrip = data_manage.CTRIP(self.ctrip_file_name)
data_heading = ctrip.get_total_heading()
return data_heading
def get_rec_data(self,choice_cols):
ctrip = data_manage.CTRIP(self.ctrip_file_name)
ctrip_data = ctrip.get_data_list(choice_cols=choice_cols)
return ctrip_data
def insert_check_ctrip_row_data(self,row):
self.check_ctrip.insert_row_data(row=row)
def _value_name(self, p_name, c_name):
if p_name in c_name.split(','):
return True
else:
return False
def _value_date(self, p_date, c_date):
p_date = p_date.split(' ')[0]
return p_date == c_date[1]
def _value_money(self, p_money, c_money):
res = Decimal(str(p_money)) - Decimal(str(c_money))
return res == Decimal('0')
def _value_pms_ctrip_row(self, pms_row, ctrip_row):
p_name, c_name = pms_row[3], ctrip_row[3]
p_date, c_date = pms_row[7], ctrip_row[4:6]
p_money, c_money = pms_row[6], ctrip_row[7]
value_result = self._value_name(p_name, c_name) and \
self._value_date(p_date, c_date) and \
self._value_money(p_money, c_money)
return value_result
def check(self):
pms_data = self.pms_unck.get_total_data()
ctrip_data = self.check_ctrip.get_total_data()
pms_received = []
pms_unrecived = []
ctrip_check = []
ctrip_uncheck = []
for pms_row in pms_data:
rec_tag = False
for ctrip_row in ctrip_data:
if self._value_pms_ctrip_row(pms_row, ctrip_row):
rec_tag = True
pms_row = list(pms_row)
pms_row.extend(list(ctrip_row))
ctrip_check.append(list(ctrip_row))
ctrip_data.remove(ctrip_row)
break
if rec_tag == True:
pms_received.append(pms_row)
else:
pms_unrecived.append(list(pms_row))
for row in ctrip_data:
ctrip_uncheck.append(list(row))
return [pms_received, ctrip_check, pms_unrecived, ctrip_uncheck]
def insert_pms_db_row_data(self, insert_row):
self.pms_ctrip_checked.insert_row_data(insert_row)
self.check_ctrip.delete_checked_data(ota_bill_no=insert_row[10])
self.pms_unck.delete_checked_data(row=insert_row[:8])
def _change_type_insert_row(self, table_name, insert_row):
aim_pms_db = data_manage.PMS_UNCK(data_name=self.data_name, table_name=table_name)
aim_pms_db.insert_row_data(row=insert_row)
self.pms_unck.delete_checked_data(row=insert_row[:8])
def change_rec_type(self, choice_value, insert_row):
insert_row = insert_row[:8]
insert_row.extend(['', '', '', '', '', '', '', ''])
if choice_value == 1:
self._change_type_insert_row(table_name='pms_meituan_uncheck',insert_row=insert_row)
elif choice_value == 2:
self._change_type_insert_row(table_name='pms_xianjin', insert_row=insert_row)
elif choice_value == 3:
self._change_type_insert_row(table_name='pms_pos', insert_row=insert_row)
elif choice_value == 4:
self._change_type_insert_row(table_name='pms_jiqiren', insert_row=insert_row)
else:
pass
def _get_pms_db_data(self,table_name):
aim_pms_db = data_manage.PMS_CK(data_name=self.data_name, table_name=table_name)
pms_data = aim_pms_db.get_total_data()
return pms_data
def get_un_rec_pms_db(self, table_name):
aim_pms_db = data_manage.PMS_UNCK(data_name=self.data_name, table_name=table_name)
pms_data = aim_pms_db.get_total_data()
return pms_data
def get_meituan_data(self):
data = self._get_pms_db_data(table_name='pms_meituan_checked')
return data
def get_ctrip_data(self):
data = self._get_pms_db_data(table_name='pms_ctrip_checked')
return data
def get_pos_data(self):
data = self._get_pms_db_data(table_name='pms_pos')
return data
def get_xianjin_data(self):
data = self._get_pms_db_data(table_name='pms_xianjin')
return data
def get_jiqiren_data(self):
data = self._get_pms_db_data(table_name='pms_jiqiren')
return data
def write_data_list(self, data, file_name):
wb = xlwt.Workbook()
sh = wb.add_sheet('0', cell_overwrite_ok=True)
for i in range(len(data)):
for j in range(len(data[i])):
sh.write(i, j, data[i][j])
wb.save(file_name)
class CHECK_MEITUAN:
def __init__(self, data_name):
self.data_name = data_name
self.pms_unck = data_manage.PMS_UNCK(data_name=data_name, table_name='pms_meituan_uncheck')
self.check_meituan = data_manage.CHECK_OTA(data_name=data_name, table='check_meituan')
self.pms_meituan_checked = data_manage.PMS_CK(data_name=data_name, table_name='pms_meituan_checked')
self.pms_path = ''
self.meituan_file_name = ''
def get_total_account_types(self):
total_account_types = []
pms_data_dict = data_manage.PMS(pms_path=self.pms_path).get_pms_data_dict()
for account_type in pms_data_dict.keys():
total_account_types.append(str(account_type))
return total_account_types
def get_check_pms_data(self, choice_pay_types):
new_check_pms_data = []
pms_data_dict = data_manage.PMS(pms_path=self.pms_path).get_pms_data_dict()
for choice_type in choice_pay_types:
new_check_pms_data.extend(pms_data_dict[choice_type])
return new_check_pms_data
def insert_check_pms_row_data(self, row):
self.pms_unck.insert_row_data(row)
#################################################################################
def get_rec_data_heading(self):
meituan = data_manage.MEITUAN(self.meituan_file_name)
data_heading = meituan.get_total_heading()
return data_heading
def get_rec_data(self,choice_cols):
meituan = data_manage.MEITUAN(self.meituan_file_name)
meituan_data = meituan.get_data_list(choice_cols=choice_cols)
return meituan_data
def insert_check_meituan_row_data(self,row):
self.check_meituan.insert_row_data(row=row)
###################################################################################
def _value_name(self, p_name, c_name):
if p_name in c_name.split(','):
return True
else:
return False
def _value_date(self, p_date, c_date):
p_date = p_date.split(' ')[0]
return p_date == c_date[1]
def _value_money(self, p_money, c_money):
res = Decimal(str(p_money)) - Decimal(str(c_money))
return res == Decimal('0')
def _value_pms_ctrip_row(self, pms_row, ctrip_row):
p_name, c_name = pms_row[3], ctrip_row[3]
p_date, c_date = pms_row[7], ctrip_row[4:6]
p_money, c_money = pms_row[6], ctrip_row[7]
value_result = self._value_name(p_name, c_name) and \
self._value_date(p_date, c_date) and \
self._value_money(p_money, c_money)
return value_result
def check(self):
pms_data = self.pms_unck.get_total_data()
meituan_data = self.check_meituan.get_total_data()
pms_received = []
pms_unrecived = []
meituan_check = []
meituan_uncheck = []
for pms_row in pms_data:
rec_tag = False
for meituan_row in meituan_data:
if self._value_pms_ctrip_row(pms_row, meituan_row):
rec_tag = True
pms_row = list(pms_row)
pms_row.extend(list(meituan_row))
meituan_check.append(list(meituan_row))
meituan_data.remove(meituan_row)
break
if rec_tag == True:
pms_received.append(pms_row)
else:
pms_unrecived.append(list(pms_row))
for row in meituan_data:
meituan_uncheck.append(list(row))
return [pms_received, meituan_check, pms_unrecived, meituan_uncheck]
def insert_pms_db_row_data(self, insert_row):
self.pms_meituan_checked.insert_row_data(insert_row)
self.check_meituan.delete_checked_data(ota_bill_no=insert_row[10])
self.pms_unck.delete_checked_data(row=insert_row[:8])
def _change_type_insert_row(self, table_name, insert_row):
aim_pms_db = data_manage.PMS_UNCK(data_name=self.data_name, table_name=table_name)
aim_pms_db.insert_row_data(row=insert_row)
self.pms_unck.delete_checked_data(row=insert_row[:8])
def change_rec_type(self, choice_value, insert_row):
insert_row = insert_row[:8]
insert_row.extend(['', '', '', '', '', '', '', ''])
if choice_value == 1:
self._change_type_insert_row(table_name='pms_ctrip_uncheck',insert_row=insert_row)
elif choice_value == 2:
self._change_type_insert_row(table_name='pms_xianjin', insert_row=insert_row)
elif choice_value == 3:
self._change_type_insert_row(table_name='pms_pos', insert_row=insert_row)
elif choice_value == 4:
self._change_type_insert_row(table_name='pms_jiqiren', insert_row=insert_row)
else:
pass
def _get_pms_db_data(self,table_name):
aim_pms_db = data_manage.PMS_CK(data_name=self.data_name, table_name=table_name)
pms_data = aim_pms_db.get_total_data()
return pms_data
def get_un_rec_pms_db(self, table_name):
aim_pms_db = data_manage.PMS_UNCK(data_name=self.data_name, table_name=table_name)
pms_data = aim_pms_db.get_total_data()
return pms_data
def get_meituan_data(self):
data = self._get_pms_db_data(table_name='pms_meituan_checked')
return data
def get_ctrip_data(self):
data = self._get_pms_db_data(table_name='pms_ctrip_checked')
return data
def get_pos_data(self):
data = self._get_pms_db_data(table_name='pms_pos')
return data
def get_xianjin_data(self):
data = self._get_pms_db_data(table_name='pms_xianjin')
return data
def get_jiqiren_data(self):
data = self._get_pms_db_data(table_name='pms_jiqiren')
return data
def write_data_list(self, data, file_name):
wb = xlwt.Workbook()
sh = wb.add_sheet('0', cell_overwrite_ok=True)
for i in range(len(data)):
for j in range(len(data[i])):
sh.write(i, j, data[i][j])
wb.save(file_name)
data_manage
import sqlite3
import os
from bs4 import BeautifulSoup
import xlrd
class PMS_CK:
def __init__(self, data_name, table_name):
self.data_name = data_name
self.table_name = table_name
create_sql = """
create table {}
(
identify text primary key,
detail_num text,
room text,
name text,
operator text,
account_type text,
money text,
create_time text,
note text,
rec_type text,
rec_date text,
bill_no text,
order_name text,
in_date text,
out_date text,
rec_money text,
tra_money text
)
""".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(create_sql)
except Exception as err:
print(err)
con.commit()
con.close()
def _insert_row_data(self, detail_num, room, name, operator, account_type, money,create_time,note,\
rec_type, rec_date, bill_no,order_name, in_date, out_date, rec_money, tra_money):
identify = detail_num+room+name+operator+account_type+money+create_time+note+\
rec_type+rec_date+bill_no+order_name+in_date+out_date+rec_money+tra_money
insert_sql = """
insert into {}
(identify, detail_num, room, name, operator, account_type, money,create_time,note,\
rec_type, rec_date, bill_no,order_name, in_date, out_date, rec_money, tra_money)
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
""".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(insert_sql, (identify,detail_num, room, name, operator, account_type, money,create_time,note,\
rec_type, rec_date, bill_no,order_name, in_date, out_date, rec_money, tra_money))
except Exception as err:
print(err)
con.commit()
con.close()
def _value_cell_none(self, cell_value):
if cell_value == None:
cell_value = ''
else:
cell_value = str(cell_value)
return cell_value
def insert_row_data(self, insert_row):
self._insert_row_data(
self._value_cell_none(cell_value=insert_row[0]),
self._value_cell_none(cell_value=insert_row[1]),
self._value_cell_none(cell_value=insert_row[2]),
self._value_cell_none(cell_value=insert_row[3]),
self._value_cell_none(cell_value=insert_row[4]),
self._value_cell_none(cell_value=insert_row[5]),
self._value_cell_none(cell_value=insert_row[6]),
self._value_cell_none(cell_value=insert_row[7]),
self._value_cell_none(cell_value=insert_row[8]),
self._value_cell_none(cell_value=insert_row[9]),
self._value_cell_none(cell_value=insert_row[10]),
self._value_cell_none(cell_value=insert_row[11]),
self._value_cell_none(cell_value=insert_row[12]),
self._value_cell_none(cell_value=insert_row[13]),
self._value_cell_none(cell_value=insert_row[14]),
self._value_cell_none(cell_value=insert_row[15]),
)
def get_total_data(self):
total_data = []
select_sql = "select * from {}".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(select_sql)
total_data = cursor.fetchall()
except Exception as err:
print(err)
con.commit()
con.close()
return total_data
def get_unrec_data(self):
rec_date = ''
unrec_data = []
select_sql = "select * from {} where rec_date = ?".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(select_sql, (rec_date,))
for row in cursor.fetchall():
unrec_data.append(list(row))
except Exception as err:
print(err)
con.commit()
con.close()
return unrec_data
def delete_unrec_data(self):
rec_date = ''
delete_sql = """delete from {} where rec_date = ?""".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(delete_sql, (rec_date,))
except Exception as err:
print(err)
con.commit()
con.close()
class PMS_UNCK:
def __init__(self, data_name,table_name):
self.data_name = data_name
self.table_name = table_name
create_sql = """
create table {}
(
identify text primary key,
detail_num text,
room text,
name text,
operator text,
account_type text,
money text,
create_time text,
note text
)""".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(create_sql)
except Exception as err:
print(err)
con.commit()
con.close()
def _value_cell_none(self, cell_value):
if cell_value == None:
cell_value = ''
else:
cell_value = str(cell_value)
return cell_value
def _insert_row_data(self, detail_num, room, name, operator,account_type, money, create_time,note):
identify = detail_num+room+name+operator+account_type+money+create_time+note
insert_sql = """
insert into {}
(identify, detail_num, room, name, operator,account_type, money, create_time,note)
values(?, ?, ?, ?, ?, ?, ?, ?, ?)""".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(insert_sql, (identify, detail_num, room, name, operator,account_type, money, create_time,note))
except Exception as err:
print(err)
con.commit()
con.close()
def insert_row_data(self, row):
self._insert_row_data(
self._value_cell_none(row[0]),
self._value_cell_none(row[1]),
self._value_cell_none(row[2]),
self._value_cell_none(row[3]),
self._value_cell_none(row[4]),
self._value_cell_none(row[5]),
self._value_cell_none(row[6]),
self._value_cell_none(row[7])
)
def get_total_data(self):
select_sql = """select * from {}""".format(self.table_name)
total_data = []
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(select_sql)
total_data = cursor.fetchall()
except Exception as err:
print(err)
con.commit()
con.close()
return total_data
def delete_total_data(self):
delete_sql = """delete from {}""".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(delete_sql)
except Exception as err:
print(err)
con.commit()
con.close()
def delete_checked_data(self, row):
identify = ''
for item in row:
identify += item
delete_sql = """delete from {} where identify = ?""".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(delete_sql, (identify,))
except Exception as err:
print(err)
con.commit()
con.close()
class CHECK_OTA:
def __init__(self, data_name, table):
self.data_name = data_name
self.table_name = table
create_sql = """
create table {}
(
identify text primary key,
rec_date text,
bill_no text,
order_name text,
in_date text,
out_date text,
rec_money text,
tra_money text
)""".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(create_sql)
except Exception as err:
print(err)
con.commit()
con.close()
def _value_cell_none(self, cell_value):
if cell_value == None:
cell_value=''
else:
cell_value = str(cell_value)
return cell_value
def _insert_row_data(self, rec_date, bill_no, order_name,in_date, out_date, rec_money, tra_money):
identify = bill_no+order_name+in_date+out_date+rec_money+tra_money
insert_sql = """
insert into {}
(identify, rec_date, bill_no, order_name,in_date, out_date, rec_money, tra_money)
values(?, ?, ?, ?, ?, ?, ?, ?)
""".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(insert_sql, (identify, rec_date, bill_no, order_name,in_date, out_date, rec_money, tra_money))
except Exception as err:
print(err)
con.commit()
con.close()
def insert_row_data(self, row):
self._insert_row_data(
self._value_cell_none(row[0]),
self._value_cell_none(row[1]),
self._value_cell_none(row[2]),
self._value_cell_none(row[3]),
self._value_cell_none(row[4]),
self._value_cell_none(row[5]),
self._value_cell_none(row[6]),
)
def get_total_data(self):
select_sql = """select * from {}""".format(self.table_name)
total_data = []
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(select_sql)
for item in cursor.fetchall():
total_data.append(list(item))
except Exception as err:
print(err)
con.commit()
con.close()
return total_data
def delete_total_data(self):
delete_sql = """delete from {} """.format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(delete_sql)
except Exception as err:
print(err)
con.commit()
con.close()
def delete_checked_data(self, ota_bill_no):
delete_sql = """delete from {} where bill_no = ?""".format(self.table_name)
con = sqlite3.connect(self.data_name)
cursor = con.cursor()
try:
cursor.execute(delete_sql, (ota_bill_no,))
except Exception as err:
print(err)
con.commit()
con.close()
#处理pms数据的类PMS
class PMS:
def __init__(self, pms_path):
self.path = pms_path
def get_pms_file_names(self):
pms_file_names = []
for root, dirs, files in os.walk(self.path):
for file in files:
pms_file_names.append(root + '\\' + file)
return pms_file_names
def get_one_file_data_dict(self, file_name):
one_file_data_dict = {}
with open(file=file_name, mode='r', encoding='utf-8') as f:
html_text = f.read()
soup = BeautifulSoup(html_text, 'lxml')
tables = soup.body.find_all('div', {'style': 'margin-bottom:10px;'})
for table in tables:
table_title = table.p.string
if table_title not in one_file_data_dict.keys():
one_file_data_dict[table_title] = []
else:
pass
for table in tables:
table_title = table.p.string
table_data_data = []
table_data_html = table.find('table', {'class': 'table-list'}).tbody
for tr in table_data_html.find_all('tr'):
table_row_data = []
for td in tr.find_all('td'):
if td.a == None:
table_row_data.append(td.string)
else:
table_row_data.append(td.a.string)
table_data_data.append(table_row_data)
one_file_data_dict[table_title].extend(table_data_data)
return one_file_data_dict
def get_pms_data_dict(self):
pms_data_dict = {}
file_names = self.get_pms_file_names()
for file_name in file_names:
one_file_pms_dict = self.get_one_file_data_dict(file_name)
for title in one_file_pms_dict.keys():
if title not in pms_data_dict.keys():
pms_data_dict[title] = []
else:
pass
for file_name in file_names:
one_file_pms_dict = self.get_one_file_data_dict(file_name)
for title in one_file_pms_dict.keys():
pms_data_dict[title].extend(one_file_pms_dict[title][:-1])
return pms_data_dict
class CTRIP:
def __init__(self, file_name):
self.file_name =file_name
def get_one_file_data(self, file_name):
wb = xlrd.open_workbook(file_name)
sh = wb.sheet_by_index(0)
result = []
for n in range(1, sh.nrows):
result.append(sh.row_values(n))
return result
def get_total_heading(self):
wb = xlrd.open_workbook(self.file_name)
sh = wb.sheet_by_index(0)
row0 = sh.row_values(0)
row1 = sh.row_values(1)
return [row0, row1]
def get_data_list(self, choice_cols):
result = []
result0 = self.get_one_file_data(self.file_name)
for row in result0:
temp_row = []
for c in choice_cols:
temp_row.append(row[c])
result.append(temp_row)
return result
class MEITUAN:
def __init__(self, file_name):
self.file_name =file_name
def get_one_file_data(self, file_name):
wb = xlrd.open_workbook(file_name)
sh = wb.sheet_by_index(1)
result = []
for n in range(1, sh.nrows):
result.append(sh.row_values(n))
return result
def get_total_heading(self):
wb = xlrd.open_workbook(self.file_name)
sh = wb.sheet_by_index(1)
row0 = sh.row_values(0)
row1 = sh.row_values(1)
return [row0, row1]
def get_data_list(self, choice_cols):
result = []
result0 = self.get_one_file_data(self.file_name)
for row in result0:
temp_row = []
for c in choice_cols:
temp_row.append(row[c])
result.append(temp_row)
return result
运行效果截图
