[Python] 纯文本查看 复制代码
# coding:'utf-8'"""
flag:
127:开户充值 (7F)
128:现金充值 (80)
129:批量充值 (81)
7.水控转帐 (7)
6:补贴清零 (6)
5:补贴机正常补贴记录 (5)
4:出纳机正常充值记录 (4)
3:消费机菜单编号正常消费记录 (3)
2:消费机分段定值正常消费记录 (2)
1:消费机计次消费正常消费记录 (1)
0:消费机普通不定额正常消费记录 (0)
excel格式：join_id emp_id card_id money
思路：差额为负值，进行数据库操作：充值；
insert into mealrecords() values();涉及emp_id，card_id，join_id，cardindex,times_money
falg（128：现金充值，4：出纳机充值）,temp_time,sign_time,clock_id,type(0：考勤，1，门禁，2，考勤门禁，3，消费)
差额为正值：进行数据库操作：消费,差额为负值：进行数据库操作：充值
最终的目的是将账面金额做平
"""
import random
import time
import xlrd
import pymssql
# 运行开始时间
s_time = time.time()
print('数据处理中..........')
def ramdon_time():
"""随机生成时间"""
a1 = (2022, 1, 1, 0, 0, 0, 0, 0, 0)
a2 = (2022, 5, 1, 0, 0, 0, 0, 0, 0)
# 将时间元组转换为秒
start_time = time.mktime(a1)
end_time = time.mktime(a2)
randomtime = random.randint(start_time, end_time)
localtime = time.localtime(randomtime)
fmttime = time.strftime("%Y-%m-%d %H:%M:%S", localtime)
return fmttime
def get_valid_cardindex(*alist):
"""
*alist：传入流水号列表，
返回值：从缺失流水号中随机返回一个可用的流水号,类型为int
"""
alist_cardindex = sorted((set(*alist)))
res = [] # 存缺失的流水号
for m, n in enumerate(alist_cardindex):
# print(m, n)
"""设置查找缺失流水号最大间隔为9"""
for i in range(1, 10):
if m + 1 < len(alist_cardindex) and (n + i < alist_cardindex[m + 1]):
res.append(n + i)
"""随机取一个值"""
# 从缺失的流水号中随机取一个值
if res:
return random.choice(res)
else:
# 如果流水号是连续的，则返回-1作为流水号
return -1
# 文件路径
file_path = r"C:\Users\Administrator\Desktop\balance.xlsx"
def get_card_data():
"""返回值为列表"""
data = xlrd.open_workbook(file_path)
sheet_data = data.sheets()[0]
row_data = sheet_data.nrows
cardid = []
for i in range(1, row_data):
data_join_id = sheet_data.row_values(i)[0]
data_emp_id = sheet_data.row_values(i)[1]
data_card_id = sheet_data.row_values(i)[2]
data_money = sheet_data.row_values(i)[3]
cardid.append([data_join_id, data_emp_id, data_card_id, data_money])
return cardid
"""数据库配置"""
server = 'localhost'
user = 'sa'
password = 'sa12345678'
database = 'zz'
port = '1433'
def get_join(i):
"""从excel中读取join_id"""
join_id = get_card_data()[i][0]
return int(join_id)
def get_emp_id(i):
"""从exce读取emp_id"""
emp_id = get_card_data()[i][1]
emp_id_int = int(emp_id)
return '%06d' % emp_id_int
def get_card_id(i):
"""从exce读取card_id"""
card_id = get_card_data()[i][2]
card_id_int = int(card_id)#str转int
return '%06d' % card_id_int #格式化，补齐为6位
def get_money(i):
"""从exce读取money"""
money = get_card_data()[i][3]
return money
#计数
meal = 0
pay = 0
for i in range(len(get_card_data())):
#用with pymssql.connect的作用等同于with open('filename','w') as f
with pymssql.connect(server, user, password, database, port) as conn:
# print('数据库连接成功')
sql = "select cardindex from mealrecords where join_id=%r" % get_join(i)
query_cardidnex = []
with conn.cursor() as cursor:
cursor.execute(sql)
for n in cursor:
# append(n)的结果是元组，n[0]解包
query_cardidnex.append(n[0])
# print(query_cardidnex)
avalid_cardindex = get_valid_cardindex(query_cardidnex)
# print(avalid_cardindex)
with pymssql.connect(server, user, password, database, port) as conn:
# tips:发现lambda可以无参数！！！emo
f = lambda: 2 if get_money(i) > 0 else 128 #flag
f_clock = lambda: 1688 if get_money(i) > 0 else 1690
sql_1 = "insert into mealrecords(clock_id,type,flag,join_id,emp_id,card_id,times_money,sign_time,cardindex) values(%d,3,%d,%r,%r,%r,%r,%r,%r)" % (f_clock(),
f(), get_join(i), get_emp_id(i), get_card_id(i),abs(get_money(i)), ramdon_time(), avalid_cardindex)
#计数
if f_clock() == 1688:
meal += 1
else:
pay += 1
with conn.cursor() as cursor:
#为了方便移植，将执行预计写文本，必须用a；w：先清空 ，再写，切记！
with open('meal.txt','a') as f:
f.write(sql_1+'\n')
cursor.execute(sql_1)
conn.commit()
with pymssql.connect(server, user, password, database, port) as conn:
"""匹配时间temp_time=sign_time"""
with conn.cursor() as cursor:
sql_update = "update mealrecords set temp_time=sign_time where temp_time is null"
with open('meal.txt', 'a') as f:
f.write(sql_update + '\n')
cursor.execute(sql_update)
conn.commit()
# 运行结束时间
e_time = time.time()
print('充值%d条，消费 %d条'%(pay,meal))
print('%f秒' % (e_time - s_time)) # 计时
发表于 2022-7-4 20:13
|
发表于 2022-7-5 11:54
！
