def __init__(self, sett):
self.sett = sett
self.db = MSSQL(self.sett.serverHost, self.sett.serverUser,
self.sett.serverPwd, self.sett.serverDb)
if self.sett.clientCode == "YDHDY":
from billBoli600 import BillBoli600
self.interClient = BillBoli600(sett)
self._initData()
def __init__(self, sett):
"""
实例化
:param sett:
"""
super().__init__(sett)
self.interName = "鸿商系统"
self.db = MSSQL(self.interConn["host"], self.interConn["user"],
self.interConn["password"], self.interConn["database"])
self.interInit()
def __init__(self, sett, endType):
"""
实例化
:param sett:
:param endType:
"""
super().__init__(sett, endType)
self.interType = "db"
self.interBase = "in"
self.interItems = {"item", "saleBill"}
self.db = MSSQL(self.interConn["host"], self.interConn["user"], self.interConn["password"], self.interConn["database"])
def __init__(self, sett):
"""
接口控制类
"""
self.sett = sett
# 业务前端定义
if self.sett.frontCode == "KMHCM":
from frontKemaiHcmDb import FrontKemaiHcmDb
self.front = FrontKemaiHcmDb(sett, "front")
elif self.sett.frontCode == "KMTTYS":
from frontKemaiTtysDb import FrontKemaiTtysDb
self.front = FrontKemaiTtysDb(sett, "front")
else:
raise Exception("非法的前端营业系统代码[{frontCode}]".format(
frontCode=self.sett.frontCode))
# 业务后端定义
if self.sett.backCode == "Testdb":
from backTestDb import BackTestDb
self.back = BackTestDb(sett, "back")
elif self.sett.backCode == "Kingdee":
from backKingdeeWeb import BackKingdeeWeb
self.back = BackKingdeeWeb(sett, "back")
else:
raise Exception(
"非法的后端营业系统代码[{backCode}]".format(backCode=self.sett.backCode))
# 对接内容定义：{"item", "saleBill", "accBill"}
self.interItemsBase = {"item"}
self.interItemsBusi = {"saleBill", "accBill"}
self.interConn = {}
self.interConn["host"] = self.sett.controlHost
self.interConn["user"] = self.sett.controlUser
self.interConn["password"] = self.sett.controlPwd
self.interConn["database"] = self.sett.controlDb
self.db = MSSQL(self.interConn["host"], self.interConn["user"],
self.interConn["password"], self.interConn["database"])
self.sett.logger.info("数据对象初始化成功")
class InterControl():
def __init__(self, sett):
"""
接口控制类
"""
self.sett = sett
# 业务前端定义
if self.sett.frontCode == "KMHCM":
from frontKemaiHcmDb import FrontKemaiHcmDb
self.front = FrontKemaiHcmDb(sett, "front")
elif self.sett.frontCode == "KMTTYS":
from frontKemaiTtysDb import FrontKemaiTtysDb
self.front = FrontKemaiTtysDb(sett, "front")
else:
raise Exception("非法的前端营业系统代码[{frontCode}]".format(
frontCode=self.sett.frontCode))
# 业务后端定义
if self.sett.backCode == "Testdb":
from backTestDb import BackTestDb
self.back = BackTestDb(sett, "back")
elif self.sett.backCode == "Kingdee":
from backKingdeeWeb import BackKingdeeWeb
self.back = BackKingdeeWeb(sett, "back")
else:
raise Exception(
"非法的后端营业系统代码[{backCode}]".format(backCode=self.sett.backCode))
# 对接内容定义：{"item", "saleBill", "accBill"}
self.interItemsBase = {"item"}
self.interItemsBusi = {"saleBill", "accBill"}
self.interConn = {}
self.interConn["host"] = self.sett.controlHost
self.interConn["user"] = self.sett.controlUser
self.interConn["password"] = self.sett.controlPwd
self.interConn["database"] = self.sett.controlDb
self.db = MSSQL(self.interConn["host"], self.interConn["user"],
self.interConn["password"], self.interConn["database"])
self.sett.logger.info("数据对象初始化成功")
def interInit(self):
"""
控制初始化
:return:
"""
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception(
"初始化失败：控制数据库[{db}]连接失败".format(db=self.interConn["database"]))
# 创建标志表
lsSql = r"select 1 from sysobjects where xtype = 'U' and id = OBJECT_ID('InterControl')"
cur.execute(lsSql)
rs = cur.fetchall()
if len(rs) == 0:
# 创建门店控制表
lsSql = r"create table InterControl ( " \
r" Id varchar(100) not null, " \
r" Name varchar(100) not null, " \
r" Value varchar(100) not null, " \
r" Description varchar(255) null, " \
r" primary key ( Id ) ) "
cur.execute(lsSql)
# 对接日期
lsSql = r"select Value from InterControl where Id = 'dInter'"
cur.execute(lsSql)
rs = cur.fetchall()
if len(rs) == 0:
# 插入参数
initDate = datetime.strftime(datetime.today(), "%Y-%m-%d")
lsSql = r"insert into InterControl ( Id, Name, Value, Description ) " \
r"values ( 'dInter', 'Kemai-Kingdee接口启动日期', '{curDate}', '用于Kingdee接口' )".format(curDate=initDate)
cur.execute(lsSql)
# 条码控制（金蝶接口强制要求必须有6位条码，在此以自增数字代替）
lsSql = r"select Value from InterControl where Id = 'maxBarcode'"
cur.execute(lsSql)
rs = cur.fetchall()
if len(rs) == 0:
# 插入参数
lsSql = r"insert into InterControl ( Id, Name, Value, Description ) " \
r"values ( 'maxBarcode', '条码控制', '0', '用于适应接口对条码的强制要求' )"
cur.execute(lsSql)
self.sett.maxBarcode = 0
else:
self.sett.maxBarcode = int(rs[0][0])
# 创建接口门店表
lsSql = r"select 1 from sysobjects where xtype = 'U' and id = OBJECT_ID('InterBranch')"
cur.execute(lsSql)
rs = cur.fetchall()
if len(rs) == 0:
# 创建门店控制表
lsSql = r"create table InterBranch ( " \
r" branchNo varchar(20) not null, " \
r" branchName varchar(60) not null, " \
r" interDate varchar(10) not null, " \
r" status int not null, " \
r" primary key ( branchNo ) ) "
cur.execute(lsSql)
# 创建对接数据控制表
lsSql = r"select 1 from sysobjects where xtype = 'U' and id = OBJECT_ID('InterCompleted')"
cur.execute(lsSql)
rs = cur.fetchall()
if len(rs) == 0:
# 创建门店控制表
lsSql = r"create table InterCompleted ( " \
r" dataID int not null IDENTITY(1,1), " \
r" dataType varchar(50) not null, " \
r" sNumber varchar(60) null, " \
r" iNumber bigint null, " \
r" sName varchar(100) not null, " \
r" sBranch varchar(20) not null, " \
r" sDate varchar(10) not null, " \
r" primary key ( dataID ) ) "
cur.execute(lsSql)
# 提交事务，关闭连接
conn.commit()
conn.close()
# 前后端接口初始化
self.front.interInit()
self.back.interInit()
def getDataCompleted(self, sItem, sBranch, sFrom, sTo):
"""
获取已对接的数据
:param sItem: # 数据项
:param sBranch: # 门店
:param sFrom: # 开始日期
:param sTo: # 截至日期
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": { # 表体集
sItem: { # 表体代码
sItem: []
}
}
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception(
"初始化失败：控制数据库[{db}]连接失败".format(db=self.interConn["database"]))
# 获取数据
lsSql = r"select sNumber, " \
r" iNumber, " \
r" sName, " \
r" sBranch, " \
r" sDate " \
r"from InterCompleted " \
r"where dataType = '{sItem}' " \
r"and sBranch = '{sBranch}' " \
r"and sDate >= '{sFrom}' " \
r"and sDate <= '{sTo}' " \
"".format(
sItem = sItem,
sBranch = sBranch,
sFrom = sFrom,
sTo = sTo
)
cur.execute(lsSql)
rsData = cur.fetchall()
rtnData["result"] = True
for i in rsData:
if i[0]:
rtnData["entities"][sItem][sItem].append(
(i[0], i[2], i[3], i[4]))
else:
rtnData["entities"][sItem][sItem].append(
(i[1], i[2], i[3], i[4]))
return rtnData
def putDataCompleted(self, putData):
"""
更新已对接的数据
:param putData:
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {}
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception(
"初始化失败：控制数据库[{db}]连接失败".format(db=self.interConn["database"]))
for i in putData["entities"]:
rtnData["entities"][i] = {}
for j in putData["entities"][i]:
rtnData["entities"][i][j] = []
for k in putData["entities"][i][j]:
if type(k[0]) == type("aa"):
sNumber = k[0]
iNumber = "null"
else:
sNumber = ""
iNumber = k[0]
lsSql = r"insert into InterCompleted ( dataType, sNumber, iNumber, sName, sBranch, sDate ) " \
r"values ( '{dataType}', '{sNumber}', {iNumber}, '{sName}', '{sBranch}', '{sDate}' ) " \
"".format(
dataType=i,
sNumber=sNumber,
iNumber=iNumber,
sName=k[1],
sBranch=k[2],
sDate=k[3]
)
cur.execute(lsSql)
rtnData["entities"][i][j].append(k)
# 金蝶菜品接口特殊处理
if self.back.interCode == "Kingdee" and "item" in putData["entities"]:
lsSql = r"update InterControl set Value = '{maxBarcode}' where Id = 'maxBarcode'".format(
maxBarcode=self.sett.maxBarcode)
cur.execute(lsSql)
# 关闭连接
conn.commit()
conn.close()
rtnData["result"] = True
return rtnData
def interBranchs(self):
"""
更新门店信息
:return:
"""
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("基础数据获取失败：控制数据库[{db}]连接失败".format(
db=self.interConn["database"]))
# 获取全部门店信息
rsBranchs = self.front.getBranchs()["entities"]["branch"]["branch"]
sBranchsID = set([i[0] for i in rsBranchs])
# 获取已存在门店信息
lsSql = r"select branchNo, branchName from InterBranch where status = 1"
cur.execute(lsSql)
rsExists = cur.fetchall()
sExistsID = set([i[0] for i in rsExists])
# 更新门店信息状态
sDel = sExistsID - sBranchsID
for i in sDel:
lsSql = r"update InterBranch set status = 0 where branchNo = '{ID}'".format(
ID=i)
cur.execute(lsSql)
# 新增门店
sNew = sBranchsID - sExistsID
lastDate = datetime.strftime(datetime.today() + timedelta(days=-1),
"%Y-%m-%d")
sInfo = ""
for i in sNew:
branchName = ""
for j in rsBranchs:
if i == j[0]:
branchName = j[1]
break
lsSql = r"insert into InterBranch ( branchNo, branchName, interDate, status ) " \
r"values( '{ID}', '{Name}', '{sDate}', 1 ) " \
r"".format(ID=i, Name=branchName, sDate=lastDate)
cur.execute(lsSql)
sInfo += "," + i
if sInfo != "":
sInfo = sInfo[1:]
self.sett.logger.info("增加{n}个门店：{s}".format(n=len(sNew), s=sInfo))
# 关闭连接
conn.commit()
conn.close()
def interBaseData(self):
"""
基础资料对接
:return:
"""
# 获取数据范围
itemsRange = self.interItemsBase & self.front.interItems & self.back.interItems
# 计算需要对接的数据
for key in itemsRange:
# 取出数据
if self.front.interBase == "out":
getData = self.front.getBaseData(key)
else:
getData = self.back.getBaseData(key)
# 获取已对接的数据
dataCompleted = self.getDataCompleted(key, "", "", "")
if key in dataCompleted["entities"] and key in getData["entities"]:
for i in dataCompleted["entities"][key][key]:
for j in getData["entities"][key][key]:
if i[0] == j[0]:
getData["entities"][key][key].remove(j)
break
# 保存数据
if len(getData["entities"][key][key]) > 0:
self.sett.logger.info("准备导入{item}数据{num}条……".format(
item=key, num=len(getData["entities"][key][key])))
if self.back.interBase == "in":
putData = self.back.putBaseData(getData)
else:
putData = self.front.putBaseData(getData)
# 更新历史记录
self.putDataCompleted(putData)
# 显示日志
for i in putData["entities"]["item"]:
if i == "item":
name = "菜品档案"
else:
name = i
self.sett.logger.info("成功导入[{item}]{num}条.".format(
item=i, num=len(putData["entities"]["item"][i])))
def interBusiData(self):
"""
销售单据对接
对接对象：{"saleBill","accBill"}
:return:
"""
self.sett.logger.info("开始导入业务数据……")
# 获取数据范围
itemsRange = self.interItemsBusi & self.front.interItems & self.back.interItems
# 更新门店信息
self.interBranchs()
# 基础信息同步
self.interBaseData()
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("业务数据对接失败：控制数据库[{db}]连接失败".format(
db=self.interConn["database"]))
# 获取处理最大日期
dPoint = datetime.today() + timedelta(days=self.sett.timingBusiDay *
-1)
sTo = datetime.strftime(dPoint, "%Y-%m-%d")
# 各门店循环处理
lsSql = r"select branchNo, branchName, interDate from InterBranch " \
r"where interDate < '{processDate}' " \
r"order by branchNo ".format(processDate=sTo)
cur.execute(lsSql)
rsBranch = cur.fetchall()
for i in rsBranch:
if i[0] in self.sett.org:
orgID = self.sett.org[i[0]]
else:
# raise Exception("请在参数中配置[{front}]机构[{branch}]的应的[{back}]机构ID.".format(front=self.sett.frontName, branch=i[1], back=self.sett.backName))
# 没有配置对应关系的门店不对接，继续执行其他门店
self.sett.logger.info("[{end}]的门店[{branch}]没有配置对应关系.".format(
end=self.sett.frontName, branch=i[0]))
continue
# 初始化对接日期
interDate = ""
# 初始化对接结果
interResult = True
# 获取开始日期
sFrom = datetime.strftime(
datetime.strptime(i[2], "%Y-%m-%d") + timedelta(days=1),
"%Y-%m-%d")
# 获得新增的数据
for key in itemsRange:
# 取出数据
getData = self.front.getBusiData(key, i[0], sFrom, sTo)
# 获取已对接的数据
dataCompleted = self.getDataCompleted(key, i[0], sFrom, sTo)
if key in dataCompleted["entities"] and key in getData[
"entities"]:
for l in dataCompleted["entities"][key][key]:
for m in getData["entities"][key]["bill"]:
if m[0] == l[0]:
getData["entities"][key]["bill"].remove(m)
list1 = []
for n in getData["entities"][key]["item"]:
if n[0] == l[0]:
list1.append(n)
for n in list1:
getData["entities"][key]["item"].remove(n)
list2 = []
for o in getData["entities"][key]["pay"]:
if o[0] == l[0]:
list2.append(o)
for o in list2:
getData["entities"][key]["pay"].remove(o)
break
if len(getData["entities"][key]["bill"]) > 0:
# 保存数据
putData = self.back.putBusiData(orgID, getData)
if not putData["result"]:
interResult = False
if putData["result"] and getData["dataString"] > interDate:
interDate = getData["dataString"]
# 记录对接历史
self.putDataCompleted(putData)
# 记录日志
for itemType in putData["entities"]:
if itemType == "saleBill":
name = "菜品销售单"
elif itemType == "accBill":
name = "财务记账单"
else:
name = itemType
self.sett.logger.info("成功导入[{item}]{num}条.".format(
item=name,
num=len(putData["entities"][itemType]["bill"])))
# 记录断点
if interResult and interDate != "":
lsSql = r"update InterBranch " \
r"set interDate = '{updateDate}' " \
r"where branchNo = '{branchno}' " \
r"".format(updateDate=interDate, branchno=i[0])
cur.execute(lsSql)
conn.commit()
# 关闭连接
conn.close()
self.sett.logger.info("业务数据导入结束.")
def __init__(self, sett):
self.sett = sett
self.dbApp = MSSQL(sett.app_host, sett.app_user, sett.app_pwd,
sett.app_db)
self.dbErp = MSSQL(sett.erp_host, sett.erp_user, sett.erp_pwd,
sett.erp_db)
class InterData():
def __init__(self, sett):
self.sett = sett
self.db = MSSQL(self.sett.serverHost, self.sett.serverUser,
self.sett.serverPwd, self.sett.serverDb)
if self.sett.clientCode == "YDHDY":
from billBoli600 import BillBoli600
self.interClient = BillBoli600(sett)
self._initData()
def _initData(self):
"""
数据初始化
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
#初始化基站状态
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
rtnData["info"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
lsSql = "select 1 from sys_t_system where sys_var_id = 'dcb_stationList'"
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) == 0:
lsSql = "insert into sys_t_system ( sys_var_id, sys_var_type, sys_var_name, sys_var_value, display_flag, change_flag, sys_var_remark ) values ('dcb_stationList', '接口', 'dcb点菜接口', '', 0, 1, '')"
cur.execute(lsSql)
lsSql = "update sys_t_system set sys_var_value = '[1,2,3,4,5,6,7,8]' where sys_var_id = 'dcb_stationList'"
cur.execute(lsSql)
conn.commit()
conn.close()
def userLogin(self, data):
"""
登录
:param data:{
"terminal":"", # 开台终端号（3位）
"factory":"", # 出厂号（10位）
"user":"", # 工号（4位）
"password":"" # 密码（8位）
}
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {
"order": {}
}
}
if self.sett.terminal == "boli6.00":
rtnData = self.interClient.userLogin(data)
else:
rtnData["dataString"] = "无效的客户端类型[{code}].".format(
code=self.sett.clientCode)
return rtnData
def basicDataGet(self, sType):
"""
获取基础资料
:param sType:
dishCategory 菜品类别
dishCategory 菜品类别
dishInfo 菜品信息
dishItem 菜品
dishSize 例牌
sizeItem 例牌详情
dishSuit 套餐
FoodSuitItem 套餐项
FoodSuitExchange 套餐替换项
dishPrice 价格
dishReasonReturn 退菜原因
madeInfo
MadeCls 做法类别
Made 做法
FoodMade 菜品做法
MadeClsFoodCls 做法类别和菜品小类关系
deskInfo
HallFloor 桌台区域
DeskFlie 桌台
operator 操作员
waiter 服务员
soldOut 沽清信息
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
ldItem = {}
ldKey = {}
if sType == 'dishCategory':
ldItem[
"dishCategory"] = r"select cLitCls_C, cLitCls_N from c_t_food_litCls order by nNum"
ldKey["dishCategory"] = ["cLitCls_C", "cLitCls_N"]
elif sType == 'dishInfo':
ldItem[
"dishItem"] = r"select cFood_C, cFood_N, cLitCls_C, sNameFast, sUnit, bSuitFood, eSuitType, bMultiUnit, bTimePrc, nPrc, nPrcRoom, nPrcRoom2, nPrcRoom3, nPrcRoom4, nPrcVip1, nPrcVip2, nPrcVip3, nFee, isnull(nPrcRoom4, 0) from c_t_food where bUse = 1 and bSaleClose = 0 and isnull(bInHandpro, 0) = 1 order by isnull(nNum, 9999) asc, cFood_C asc"
ldKey["dishItem"] = [
"cFood_C", "cFood_N", "cLitCls_C", "sNameFast", "sUnit",
"bSuitFood", "eSuitType", "bMultiUnit", "bTimePrc", "nPrc",
"nPrcRoom", "nPrcRoom2", "nPrcRoom3", "nPrcRoom4", "nPrcVip1",
"nPrcVip2", "nPrcVip3", "nFee", "nPrcRoom4"
]
ldItem[
"dishSize"] = r"select cFood_C, cFoodSize, nPrc, nPrcRoom, nPrcRoom2, nPrcRoom3, nPrcRoom4, nPrcVIP1, nPrcVIP2, nPrcVIP3 from c_t_food_size order by cFood_C, cFoodSize"
ldKey["dishSize"] = [
"cFood_C", "cFoodSize", "nPrc", "nPrcRoom", "nPrcRoom2",
"nPrcRoom3", "nPrcRoom4", "nPrcVIP1", "nPrcVIP2", "nPrcVIP3"
]
elif sType == "dishSuit":
ldItem[
"FoodSuitItem"] = r"select cSuit_C, cFood_C, cFood_N, nPrc, nQty, sSelectType, sortid, sunit from c_t_food_suit order by cSuit_C, sortid"
ldKey["FoodSuitItem"] = [
"cSuit_C", "cFood_C", "cFood_N", "nPrc", "nQty", "sSelectType",
"sortid", "sunit"
]
ldItem[
"FoodSuitExchange"] = r"select cSuit_C, cFood_C, cFood_N, cExchange_C, cExchange_N, nPrice, sunit from c_t_food_suitexchange order by cSuit_C, cFood_C, cSuitNo"
ldKey["FoodSuitExchange"] = [
"cSuit_C", "cFood_C", "cFood_N", "cExchange_C", "cExchange_N",
"nPrice", "sunit"
]
elif sType == "dishPrice":
rtnData["dataString"] = "暂不提供菜品特价"
elif sType == "dishReasonReturn":
ldItem[
"dishReasonReturn"] = r"select cDict_C, cDict_N, cDictRemark from sa_t_dict where cType = '退品原因' and cDict_C > '00' order by cDict_C"
ldKey["dishReasonReturn"] = ["cDict_C", "cDict_N", "cDictRemark"]
elif sType == "madeInfo":
ldItem[
"MadeCls"] = r"select cMadeCls_C, cMadeCls_N, bBillRemark from f_t_madecls order by cMadeCls_C"
ldKey["MadeCls"] = ["cMadeCls_C", "cMadeCls_N", "bBillRemark"]
ldItem[
"Made"] = r"select cMade_C, cMade_N, nExtPrice, cMadeCls, bNumPrc from f_t_made order by iSortID"
ldKey["Made"] = [
"cMade_C", "cMade_N", "nExtPrice", "cMadeCls", "bNumPrc"
]
ldItem[
"FoodMade"] = r"select cFood_C, cMade_C from c_t_food_madeCls order by cFood_C, cMade_C"
ldKey["FoodMade"] = ["cFood_C", "cMade_C"]
ldItem[
"MadeClsFoodCls"] = r"select cMadeCls_C, cCls_C from f_t_madecls_cls order by cMadeCls_C, cCls_C"
ldKey["MadeClsFoodCls"] = ["cMadeCls_C", "cCls_C"]
elif sType == "deskInfo":
ldItem[
"HallFloor"] = r"select cFloor_C, cFloor_N, bRoomPrice from f_t_floor order by cFloor_C"
ldKey["HallFloor"] = ["cFloor_C", "cFloor_N", "bRoomPrice"]
ldItem[
"DeskFlie"] = r"select cTable_C, cTable_N, cFloor_C, bEnabled, iSeatNum from f_t_table order by iSort"
ldKey["DeskFlie"] = [
"cTable_C", "cTable_N", "cFloor_C", "bEnabled", "iSeatNum"
]
elif sType == "operator":
ldItem[
"operator"] = r"select oper_id, oper_name, log_pw from sa_t_operator_info where log_pos = 1 and oper_status = 1 order by oper_id"
ldKey["operator"] = ["oper_id", "oper_name", "log_pw"]
elif sType == "waiter":
ldItem[
"waiter"] = r"select cEmp_C, cEmp_N from f_t_employee where bServiceFlag = 1 order by cEmp_C"
ldKey["waiter"] = ["cEmp_C", "cEmp_N"]
elif sType == "soldOut":
ldItem[
"soldOut"] = r"select cFood_C, cFood_N, nStock from d_t_item_stock where sClearType = '数量沽清' order by cFood_C"
ldKey["soldOut"] = ["cFood_C", "cFood_N", "nStock"]
else:
rtnData["dataString"] = "非法的数据类型参数:{sType}".format(sType=sType)
if len(rtnData["dataString"]) == 0:
try:
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
rtnData[
"dataString"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
for lsItem in ldItem:
lsSql = ldItem[lsItem]
cur.execute(lsSql)
rsData = cur.fetchall()
rsData = [[
(col.rstrip() if isinstance(col, str) else col)
for col in line
] for line in rsData]
rtnData["entities"][lsItem] = []
for line in rsData:
rtnData["entities"][lsItem].append(
dict(zip(ldKey[lsItem], line)))
rtnData["result"] = True
except Exception as e:
rtnData["dataString"] = str(e)
finally:
conn.close()
return rtnData
def basicDataFoodPic(self, sFood):
"""
获取菜品图片
:return:图片
"""
rtnData = {"result": False, "info": ""}
sPic = ""
lsSql = r"select isnull(sPicName, '') from c_t_food where cFood_C = '{food}'".format(
food=sFood)
try:
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
rtnData["info"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) > 0:
sPic = rsData[0][0].strip()
if len(sPic) > 0:
rtnData["result"] = True
else:
rtnData["info"] = "菜品{food}没有设置图片".format(food=sFood)
else:
rtnData["info"] = "查无此菜品{food}".format(food=sFood)
except Exception as e:
rtnData["dataString"] = str(e)
finally:
conn.close()
if rtnData["result"]:
file = os.path.join(self.sett.foodPicPath, sPic)
if not os.path.exists(file):
return json.dumps({
"result": False,
"info": "查无此二维码"
},
cls=MyJSONEncoder,
ensure_ascii=False)
with open(file, "rb") as f:
image = f.read()
from flask import Response
return Response(image, mimetype="image/jpeg")
else:
return json.dumps(rtnData, cls=MyJSONEncoder, ensure_ascii=False)
def busiTableStatus(self, sTable):
"""
获取桌台状态
:param sTable:
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {
"tableStatus": []
}
}
if len(sTable) > 0:
lsSql = "select cTable_C, eStatus, cBill_C, nOughtAmt from d_t_food_Bill0 where eStatus <> '结账' and cTable_C = '{table}' order by cTable_C".format(
table=sTable)
else:
lsSql = "select cTable_C, eStatus, cBill_C, nOughtAmt from d_t_food_Bill0 where eStatus <> '结账' order by cTable_C"
try:
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
rtnData["dataString"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
cur.execute(lsSql)
rsData = cur.fetchall()
for line in rsData:
rtnData["entities"]["tableStatus"].append(
dict(
zip([
"cTable_C", "eStatus", "cBill_C", "nOughtAmt"
], line)))
rtnData["result"] = True
except Exception as e:
rtnData["dataString"] = str(e)
finally:
conn.close()
return rtnData
def busiBillOpen(self, data):
"""
开台
:param data:{
"terminal":"01", # 开台终端号
"table":"", # 桌台号
"waiter":"", # 服务员号
"guestNum":0 # 客人数量
}
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {
"order": {}
}
}
if self.sett.terminal == "boli6.00":
rtnData = self.interClient.billOpen(data)
else:
rtnData["dataString"] = "无效的客户端类型[{code}].".format(
code=self.sett.clientCode)
return rtnData
def busiBillPut(self, data):
"""
下单
:param data:
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {
"order": {},
"dishes": []
}
}
if self.sett.terminal == "boli6.00":
rtnData = self.interClient.billPut(data)
else:
rtnData["dataString"] = "无效的客户端类型[{code}].".format(
code=self.sett.clientCode)
return rtnData
def busiBillGet(self, sBill):
"""
获取单据
:param sBill:
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {
"order": [],
"dishes": [],
"mades": []
}
}
if sBill.upper() == "ALL":
lsSqlBill = "select cBill_C, cTable_C, iGuestNum, stbremark, dtBillTime, eStatus, dtSettleTime from d_t_food_Bill0 order by cBill_C"
lsSqlItem = "select cBill_C, cFoodBill, cFood_C, cFood_N, cLitCls_C, nPrcBill, nQty, eRetSendFlag, eSuitFlag, cSuitBill from d_t_food_bills0 order by cBill_C, cFoodBill"
lsSqlMade = "select cBill_C, madeID, cFoodBill, cFood_C, cMade_C, cMade_n, nExtQty, nExtPrice, nExtAmt, bNumPrc from d_t_food_Billsmade0 order by cBill_C, cFoodBill, madeID"
else:
lsSqlBill = "select cBill_C, cTable_C, iGuestNum, stbremark, dtBillTime, eStatus, dtSettleTime from d_t_food_Bill0 where cBill_C = '{billID}'".format(
billID=sBill)
lsSqlItem = "select cBill_C, cFoodBill, cFood_C, cFood_N, cLitCls_C, nPrcBill, nQty, eRetSendFlag, eSuitFlag, cSuitBill from d_t_food_bills0 where cBill_C = '{billID}' order by cFoodBill".format(
billID=sBill)
lsSqlMade = "select cBill_C, madeID, cFoodBill, cFood_C, cMade_C, cMade_n, nExtQty, nExtPrice, nExtAmt, bNumPrc from d_t_food_Billsmade0 where cBill_C = '{billID}' order by cFoodBill, madeID".format(
billID=sBill)
lsKeyBill = [
"cBill_C", "cTable_C", "iGuestNum", "stbremark", "dtBillTime",
"eStatus", "dtSettleTime"
]
lsKeyItem = [
"cBill_C", "cFoodBill", "cFood_C", "cFood_N", "cLitCls_C",
"nPrcBill", "nQty", "eRetSendFlag", "eSuitFlag", "cSuitBill"
]
lsKeyMade = [
"cBill_C", "madeID", "cFoodBill", "cFood_C", "cMade_C", "cMade_n",
"nExtQty", "nExtPrice", "nExtAmt", "bNumPrc"
]
try:
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
rtnData["dataString"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
cur.execute(lsSqlBill)
rsData = cur.fetchall()
for line in rsData:
rtnData["entities"]["order"].append(
dict(zip(lsKeyBill, line)))
cur.execute(lsSqlItem)
rsData = cur.fetchall()
for line in rsData:
rtnData["entities"]["dishes"].append(
dict(zip(lsKeyItem, line)))
cur.execute(lsSqlMade)
rsData = cur.fetchall()
for line in rsData:
rtnData["entities"]["mades"].append(
dict(zip(lsKeyMade, line)))
rtnData["result"] = True
except Exception as e:
rtnData["dataString"] = str(e)
finally:
conn.close()
return rtnData
def busiSettleCode(self, sTable):
"""
获取结帐二维码
:param sTable:
:return:
"""
file = os.path.join(self.sett.tableQRPath,
"{tableID}.jpg".format(tableID=sTable))
if not os.path.exists(file):
return json.dumps({
"result": False,
"info": "查无此二维码"
},
cls=MyJSONEncoder,
ensure_ascii=False)
with open(file, "rb") as f:
image = f.read()
from flask import Response
return Response(image, mimetype="image/jpeg")
def __init__(self, sett):
self.sett = sett
self.db = MSSQL(self.sett.serverHost, self.sett.serverUser,
self.sett.serverPwd, self.sett.serverDb)
class InterProcess:
def __init__(self, sett):
self.sett = sett
self.dbApp = MSSQL(sett.app_host, sett.app_user, sett.app_pwd,
sett.app_db)
self.dbErp = MSSQL(sett.erp_host, sett.erp_user, sett.erp_pwd,
sett.erp_db)
def interInit(self, bRestart):
"""
初始化接口
:return:
"""
# 获取数据库连接
connErp = self.dbErp.GetConnect()
curErp = connErp.cursor()
if not curErp:
raise Exception("Erp端数据库连接失败")
connApp = self.dbApp.GetConnect()
curApp = connApp.cursor()
if not curApp:
raise Exception("App端数据库连接失败")
# 初始化标志
lsSql = r"select isnull(sys_var_value, '') from sys_t_system where sys_var_id = 'usewx'"
curErp.execute(lsSql)
res = curErp.fetchall()
if len(res) == 0:
lsSql = r"insert into sys_t_system ( sys_var_id, sys_var_type, sys_var_name, sys_var_value, display_flag ) " \
r"values ( 'usewx', '微信接口', '是否启用微信接口', '1', '0' )"
elif res[0][0] != "1":
lsSql = r"update sys_t_system set sys_var_value = '1' where sys_var_id = 'usewx'"
else:
lsSql = ""
if len(lsSql) > 0:
curErp.execute(lsSql)
lsSql = r"select isnull(sys_var_value, '') from sys_t_system where sys_var_id = 'UseCRM'"
curErp.execute(lsSql)
res = curErp.fetchall()
if len(res) == 0:
lsSql = r"insert into sys_t_system ( sys_var_id, sys_var_type, sys_var_name, sys_var_value, display_flag ) " \
r"values ( 'UseCRM', '前台参数', '是否CRM服务', '1', '1' )"
elif res[0][0] != "1":
lsSql = r"update sys_t_system set sys_var_value = '1' where sys_var_id = 'UseCRM'"
else:
lsSql = ""
if len(lsSql) > 0:
curErp.execute(lsSql)
# 数据同步日期
bInit = False
lsSql = r"select isnull(sys_var_value, '') from sys_t_system where sys_var_id = 'dInterBase'"
curErp.execute(lsSql)
res = curErp.fetchall()
if len(res) == 0:
lsSql = r"insert into sys_t_system ( sys_var_id, sys_var_type, sys_var_name, sys_var_value ) values ( 'dInterBase', '系统信息', '外部点餐对接日期', '' )"
curErp.execute(lsSql)
lsInterBase = "1900-01-01"
bInit = True
else:
lsInterBase = res[0][0]
if not lsInterBase or lsInterBase.strip() == "":
lsInterBase = "1900-01-01"
# 获取当前营业日期
lsSql = r"select isnull(sys_var_value, '') from sys_t_system where sys_var_id = 'dBusiness'"
curErp.execute(lsSql)
res = curErp.fetchall()
if len(res) == 0:
raise Exception("当前营业日期无效")
self.sBusiness = res[0][0]
# 建立临时表
lsSql = r"select 1 from sysobjects where id=object_id('interTmp')"
curApp.execute(lsSql)
res = curApp.fetchall()
if len(res) == 0:
lsSql = r"create table interTmp ( " \
r" Dine_ID varchar(50) not null, " \
r" busiDate varchar(10) not null, " \
r" app_billno varchar(50) not null, " \
r" primary key ( Dine_ID, busiDate, app_billno ) ) "
curApp.execute(lsSql)
if bInit:
lsSql = r"insert into interTmp ( Dine_ID, busiDate, app_billno ) " \
r"select DINE_ID, convert(char(10), BEGINDate, 120), salesid from Dine_sales " \
r"where Dine_ID = '{Dine_ID}'".format(Dine_ID=self.sett.Dine_ID)
curApp.execute(lsSql)
lsSql = r"select 1 from sysobjects where id=object_id('interTmp0')"
curApp.execute(lsSql)
res = curApp.fetchall()
if len(res) == 0:
lsSql = r"create table interTmp0 ( " \
r" Dine_ID varchar(50) not null, " \
r" busiDate varchar(10) not null, " \
r" app_billno varchar(50) not null, " \
r" primary key ( Dine_ID, busiDate, app_billno ) ) "
curApp.execute(lsSql)
if bInit:
lsSql = r"insert into interTmp0 ( Dine_ID, busiDate, app_billno ) " \
r"select DINE_ID, convert(char(10), BEGINDate, 120), salesid from Dine_sales " \
r"where DINE_ID = '{Dine_ID}' " \
r"and convert(char(10), BEGINDate, 120) = '{sDate}'".format(Dine_ID=self.sett.Dine_ID, sDate=lsInterBase)
curApp.execute(lsSql)
# 初始化单据
if self.sBusiness != lsInterBase:
lsSql = r"delete from interTmp0 where Dine_ID = '{Dine_ID}' ".format(
Dine_ID=self.sett.Dine_ID)
curApp.execute(lsSql)
lsSql = r"insert into interTmp0 ( Dine_ID, busiDate, app_billno ) " \
r"select Dine_ID, busiDate, app_billno from interTmp " \
r"where Dine_ID = '{Dine_ID}' " \
r"and busiDate = '{sDate}' ".format(Dine_ID=self.sett.Dine_ID, sDate=self.sBusiness)
curApp.execute(lsSql)
lsSql = r"update sys_t_system set sys_var_value = '{sDate}' where sys_var_id = 'dInterBase'".format(
sDate=self.sBusiness)
curErp.execute(lsSql)
# 初始化菜品等资料
if self.sBusiness != lsInterBase or bRestart:
self.interToApp()
# 提交事务，关闭连接
connErp.commit()
connErp.close()
connApp.commit()
connApp.close()
def interToApp(self):
"""
向App写入菜品等资料，提交事务
:return:
"""
# 获取数据库连接
connErp = self.dbErp.GetConnect()
curErp = connErp.cursor()
if not curErp:
raise Exception("Erp端数据库连接失败")
connApp = self.dbApp.GetConnect()
curApp = connApp.cursor()
if not curApp:
raise Exception("App端数据库连接失败")
# 查询Erp数据
lsSql = r"select bigcls.cBigCls_C, " \
r" litcls.cLitCls_C, " \
r" litcls.cLitCls_N " \
r"from c_t_food_bigCls bigcls, " \
r" c_t_food_litCls litcls " \
r"where bigcls.cBigCls_C = litcls.cBigCls_C "
curErp.execute(lsSql)
resCls = curErp.fetchall()
lsSql = r"select food.cFood_C, " \
r" food.sNameFast, " \
r" food.cFood_N, " \
r" food.sUnit, " \
r" food.nPrc, " \
r" bigcls.cBigCls_C, " \
r" litcls.cLitCls_C " \
r"from c_t_food food, " \
r" c_t_food_litCls litcls, " \
r" c_t_food_bigCls bigcls " \
r"where food.cLitCls_C = litcls.cLitCls_C " \
r"and litcls.cBigCls_C = bigcls.cBigCls_C " \
r"and food.bUse = 1 "
curErp.execute(lsSql)
resItem = curErp.fetchall()
if len(resCls) > 0 and len(resItem) > 0:
# 类别数据同步
lsSql = r"delete from Dine_ClassSub where Dine_ID = '{Dine_ID}'".format(
Dine_ID=self.sett.Dine_ID)
curApp.execute(lsSql)
for i in resCls:
lsSql = r"insert into Dine_ClassSub ( CLASSID, Dine_ID, CLASSNAME ) values ( '{cBig_C}_{cLit_C}', '{Dine_ID}', '{sName}' )".format(
cBig_C=i[0],
cLit_C=i[1] + 'kmi#' + self.sett.branch_no,
Dine_ID=self.sett.Dine_ID,
sName=i[2])
curApp.execute(lsSql)
# 菜品数据同步
lsSql = r"update Dine_Goods set Status = 0 where Dine_ID = '{Dine_ID}'".format(
Dine_ID=self.sett.Dine_ID)
curApp.execute(lsSql)
for i in resItem:
lsSql = r"select 1 from Dine_Goods where goodsid = '{goodsid}' and Dine_ID = '{Dine_ID}'".format(
goodsid=i[0] + "kmi#" + self.sett.branch_no,
Dine_ID=self.sett.Dine_ID)
curApp.execute(lsSql)
res = curApp.fetchall()
if len(res) == 0:
lsSql = r"insert into Dine_Goods ( goodsid, Dine_ID, goodscode, goodsname, unit, price, CLASSID, property, Status ) values ( '{goodsid}', '{Dine_ID}', '{goodscode}', '{goodsname}', '{unit}', {price}, '{cBig_C}_{cLit_C}', 0, 1 ) ".format(
goodsid=i[0] + "kmi#" + self.sett.branch_no,
Dine_ID=self.sett.Dine_ID,
goodscode=i[1],
goodsname=i[2],
unit=i[3],
price=i[4],
cBig_C=i[5],
cLit_C=i[6] + "kmi#" + self.sett.branch_no)
else:
lsSql = r"update Dine_Goods set goodscode='{goodscode}', goodsname='{goodsname}', unit='{unit}', price={price}, CLASSID='{cBig_C}_{cLit_C}', Status = 1 where goodsid = '{goodsid}' and Dine_ID = '{Dine_ID}' ".format(
goodsid=i[0] + "kmi#" + self.sett.branch_no,
Dine_ID=self.sett.Dine_ID,
goodscode=i[1],
goodsname=i[2],
unit=i[3],
price=i[4],
cBig_C=i[5],
cLit_C=i[6] + "kmi#" + self.sett.branch_no)
curApp.execute(lsSql)
if 1 == 2:
# 菜品数据同步：因删除了图片、说明等字段，改用更新的方式同步数据，如上
lsSql = r"delete from Dine_Goods where Dine_ID = '{Dine_ID}'".format(
Dine_ID=self.sett.Dine_ID)
curApp.execute(lsSql)
for i in resItem:
lsSql = r"insert into Dine_Goods ( goodsid, Dine_ID, goodscode, goodsname, unit, price, CLASSID, property, Status ) values ( '{goodsid}', '{Dine_ID}', '{goodscode}', '{goodsname}', '{unit}', {price}, '{cBig_C}_{cLit_C}', 0, 1 ) ".format(
goodsid=i[0],
Dine_ID=self.sett.Dine_ID,
goodscode=i[1],
goodsname=i[2],
unit=i[3],
price=i[4],
cBig_C=i[5],
cLit_C=i[6])
curApp.execute(lsSql)
# 套餐数据同步
lsSql = r"delete from Dine_GOODSBOM"
curApp.execute(lsSql)
lsSql = r"select suit.cSuit_C, " \
r" suit.cFood_C, " \
r" suit.nQty, " \
r" suit.sunit " \
r"from c_t_food food, " \
r" c_t_food_suit suit " \
r"where food.cFood_C = suit.cSuit_C " \
r"and food.bUse = 1 " \
r"order by suit.cSuit_C asc, " \
r" suit.cFood_C asc "
curErp.execute(lsSql)
resSuit = curErp.fetchall()
iTmp = 0
sSuit = ""
for i in resSuit:
iTmp += 1
lsSql = r"insert into Dine_GOODSBOM ( TableID, goodsid, GOODSIDS, Qty, Unit ) values ( '{TableID}', '{goodsid}', '{GOODSIDS}', {Qty}, '{Unit}' ) ".format(
TableID=("0" + str(iTmp))[-2:],
goodsid=i[0] + "kmi#" + self.sett.branch_no,
GOODSIDS=i[1] + "kmi#" + self.sett.branch_no,
Qty=i[2],
Unit=i[3])
curApp.execute(lsSql)
if i[0] != sSuit:
lsSql = r"update Dine_Goods set property = 1 where goodsid = '{goodsid}' ".format(
goodsid=i[0] + "kmi#" + self.sett.branch_no)
curApp.execute(lsSql)
sSuit = i[0]
# 口味数据同步
lsSql = r"delete from Dine_cooknote where Dine_ID = '{Dine_ID}'".format(
Dine_ID=self.sett.Dine_ID)
curApp.execute(lsSql)
lsSql = r"select cMade_C, cMade_N, cMadeCls from f_t_made"
curErp.execute(lsSql)
resMadeGroup = curErp.fetchall()
for i in resMadeGroup:
lsSql = r"insert into Dine_cooknote ( COOKNOTEID, Dine_ID, COOKNOTENAME, COOKNOTEGID ) values ( '{COOKNOTEID}', '{Dine_ID}', '{COOKNOTENAME}', '{COOKNOTEGID}' ) ".format(
COOKNOTEID=i[0] + "kmi#" + self.sett.branch_no,
Dine_ID=self.sett.Dine_ID,
COOKNOTENAME=i[1],
COOKNOTEGID=i[2])
curApp.execute(lsSql)
# 菜品口味数据同步
lsSql = r"delete from Dine_GoodsCookNote"
curApp.execute(lsSql)
lsSql = r"select cFood_C, cMade from c_t_food_made"
curErp.execute(lsSql)
resMadeGroup = curErp.fetchall()
iTmp = 0
for i in resMadeGroup:
iTmp += 1
lsSql = r"insert into Dine_GoodsCookNote ( TableID, GoodsID, COOKNOTEID ) values ( '{TableID}', '{GoodsID}', '{COOKNOTEID}' ) ".format(
TableID=("0000" + str(iTmp))[-4:],
GoodsID=i[0] + "kmi#" + self.sett.branch_no,
COOKNOTEID=i[1] + "kmi#" + self.sett.branch_no)
curApp.execute(lsSql)
# 提交事务，关闭连接
connErp.close()
connApp.commit()
connApp.close()
def interToErp(self):
"""
向Erp写入账单数据，提交事务
:return:
"""
# 获取数据库连接
connErp = self.dbErp.GetConnect()
curErp = connErp.cursor()
if not curErp:
raise Exception("Erp端数据库连接失败")
connApp = self.dbApp.GetConnect()
curApp = connApp.cursor()
if not curApp:
raise Exception("App端数据库连接失败")
# 获取App单据号数组，判断条件：App单据日期=Erp当前营业日期，且没有传输过
lsSql = r"select Dine_ID, " \
r" SALESID, " \
r" PEOPLE, " \
r" TEL, " \
r" convert(char(10), BEGINDate, 120) + right(convert(char(19), BEGINTIME, 120), 9), " \
r" convert(char(19), CREATED_TIME, 120), " \
r" AMTGOODS, " \
r" AMTAR, " \
r" SALESCODE " \
r"from Dine_sales " \
r"where Dine_ID = '{Dine_ID}' " \
r"and convert(char(10), BEGINDate, 120) = '{sDate}' " \
r"and status = 1 " \
r"and SALESID not in ( select app_billno from interTmp0 where Dine_ID = '{Dine_ID}' and busiDate = '{sDate}' ) " \
r" ".format(Dine_ID=self.sett.Dine_ID, sDate=self.sBusiness)
curApp.execute(lsSql)
resBill = curApp.fetchall()
if len(resBill) > 0:
# 逐单插入
for i in resBill:
# 插入主表
lsSql = r"insert into wx_t_order_master0 ( branch_no, dBusiness, orderid, status, tabnumber, people, mobile, storeid, diningtime, createtime, " \
r"totalprice, realprice, contact, promotioninfo, sitem, cServiceMan, ordertype, sPayType, nServiceFee, nDisRate, nDisAmt, DoggyBoxPrice, autoTurnBill, autosettle, nflag, bVipPrc, billSource, thirdOrderNo ) " \
r"values( '{branch_no}', '{dBusiness}', '{orderid}', 10, '{tabnumber}', {people}, '{mobile}', '{storeid}', '{diningtime}', '{createtime}', " \
r"{totalprice}, {realprice}, '0', '', '请查看日志', '', 4, '6', 0.00, {nDisRate}, {nDisAmt}, 0.00, 1, 1, 0, 0, '', '{thirdOrderNo}' ) ".format(
branch_no=self.sett.branch_no,
dBusiness=self.sBusiness,
orderid=i[1],
tabnumber=self.sett.tabnumber,
people=i[2],
mobile=i[3],
storeid=self.sett.branch_no,
diningtime=i[4],
createtime=i[5],
totalprice=i[6],
realprice=i[7],
nDisRate=round(i[7]/i[6] if i[6] != 0 else 1.00, 2),
nDisAmt=(i[6] - i[7]),
thirdOrderNo=i[8]
)
curErp.execute(lsSql)
# 插入支付表
lsSql = r"insert into d_t_bill_pay_alipay0 ( dBusiness, cbill_c, cbill_guid, paytype, nRequestAmt, nRealPayAmt, Result, BranchNo, paymode, autoSettle ) " \
r"values ( '{dBusiness}', '{cbill_c}', '01', '6', {nRequestAmt}, {nRealPayAmt}, 1, '{branch_no}', 0, 1 ) ".format(
dBusiness=self.sBusiness,
cbill_c=i[1],
nRequestAmt=i[7],
nRealPayAmt=i[7],
branch_no=self.sett.branch_no
)
curErp.execute(lsSql)
# 插入明细表
lsSql = r"select Dine_salesdd.GOODSNO, " \
r" Dine_salesdd.goodsid, " \
r" Dine_salesdd.GOODSNAME, " \
r" Dine_salesdd.QTY, " \
r" Dine_salesdd.UNIT, " \
r" Dine_salesdd.NOTE, " \
r" Dine_salesdd.PRICE, " \
r" Dine_salesdd.MEALSETFLAG " \
r"from Dine_sales, " \
r" Dine_salesdd " \
r"where Dine_sales.SALESID = Dine_salesdd.SALESID " \
r"and Dine_sales.SALESID = '{billno}' " \
r"and Dine_sales.status = 1 " \
r"and Dine_salesdd.status = 1 " \
r"order by Dine_salesdd.Detail_ID asc " \
r" ".format(billno=i[1])
curApp.execute(lsSql)
resDetail = curApp.fetchall()
iTmp = -1
for j in resDetail:
if j[7] == -1:
iTmp = 0
sSuitFlag = "套餐"
sSuitBill = ""
elif j[7] > 0:
iTmp += 1
sSuitFlag = "子项"
sSuitBill = ("0" + str(iTmp))[-2:]
else:
sSuitFlag = "单品"
sSuitBill = ""
lsSql = r"insert into wx_t_order_detail0 ( dBusiness, orderid, cfoodbill, cfood_c, cfood_n, eSuitFlag, eSuitBill, nQty, sunit, sMade, nPrc, bDelete, bAdd, nExPrc ) " \
r"values ( '{dBusiness}', '{orderid}', '{cfoodbill}', '{cfood_c}', '{cfood_n}', '{eSuitFlag}', '{eSuitBill}', {nQty}, '{sunit}', '{sMade}', {nPrc}, 0, 0, 0.00 ) " \
r" ".format(
dBusiness=self.sBusiness,
orderid=i[1],
cfoodbill=("0" + str(j[0]))[-2:],
cfood_c=j[1][:-6],
cfood_n=j[2],
eSuitFlag=sSuitFlag,
eSuitBill=sSuitBill,
nQty=j[3],
sunit=j[4],
sMade=j[5],
nPrc=j[6]
)
curErp.execute(lsSql)
# 标记已传输单据
lsSql = r"insert into interTmp ( Dine_ID, busiDate, app_billno ) values ( '{Dine_ID}', '{sDate}', '{app_billno}' ) ".format(
Dine_ID=self.sett.Dine_ID,
sDate=self.sBusiness,
app_billno=i[1])
curApp.execute(lsSql)
lsSql = r"insert into interTmp0 ( Dine_ID, busiDate, app_billno ) values ( '{Dine_ID}', '{sDate}', '{app_billno}' ) ".format(
Dine_ID=self.sett.Dine_ID,
sDate=self.sBusiness,
app_billno=i[1])
curApp.execute(lsSql)
# 提交事务，关闭连接
connErp.commit()
connErp.close()
connApp.commit()
connApp.close()
class FrontKemaiHcmDb(BusiProcess):
"""
Kemai《好参谋V11》作为营业前端，直连数据库：
菜品资料：导出
销售单据：导出
"""
def __init__(self, sett, endType):
"""
实例化
:param sett:
:param endType:
"""
super().__init__(sett, endType)
self.interType = "db"
self.interBase = "out"
self.interItems = {"item", "saleBill", "accBill"}
self.db = MSSQL(self.interConn["host"], self.interConn["user"],
self.interConn["password"], self.interConn["database"])
def getBranchs(self):
"""
获取需要对接的门店列表
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {} # 表体集
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.interName, db=self.interConn["database"]))
lsSql = r"select Id, Name " \
r"from Branch " \
r"where Property in (1,7,8) "
cur.execute(lsSql)
rsBranchs = cur.fetchall()
rsBranchs = [(i[0].rstrip(), i[1].rstrip()) for i in rsBranchs]
rtnData["entities"]["branch"] = {}
rtnData["entities"]["branch"]["branch"] = rsBranchs
# 关闭连接
conn.close()
return rtnData
def getBaseData(self, item):
"""
基础资料导出
:param item: 数据项
:return: 基础资料字典
item: 商品资料
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {} # 表体集
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.interName, db=self.interConn["database"]))
if item == "item":
# 同步菜品数据：编码、名称
lsSql = r"select FoodId, FoodName, bigClsId, litClsId, unitId, salePrice from Food"
cur.execute(lsSql)
rsItem = cur.fetchall()
rtnData["entities"][item] = {}
rtnData["entities"][item][item] = rsItem
# 关闭连接
conn.close()
return rtnData
def getBusiData(self, item, branch, sFrom, sTo):
"""
销售单据导出
:param item: 数据项
:param branch: 门店
:param sFrom: 开始日期
:param sTo: 截至日期
:return: 导出的销售单据
maxDate: 本次同步的最大日期
bill: 主单表集合
item: 商品表集合
pay: 付款表集合
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {} # 表体集
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("业务数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.interName, db=self.interConn["database"]))
if item in {"saleBill", "accBill"}:
lsSql = r"select rtrim(newbillId), " \
r" rtrim(branchId), " \
r" business, " \
r" BillTime, " \
r" SettleTime, " \
r" FoodAmt, " \
r" ServiceFee, " \
r" MinPayFill, " \
r" DisAmt, " \
r" RoundAmt, " \
r" OughtAmt, " \
r" PayAmt, " \
r" statuss, " \
r" Remark " \
r"from foodbill " \
r"where branchid = '{branchno}' " \
r"and business >= '{sOld}' " \
r"and business <= '{sDate}' " \
r"".format(branchno=branch, sOld=sFrom, sDate=sTo)
cur.execute(lsSql)
rsBill = cur.fetchall()
rtnData["entities"][item] = {}
rtnData["entities"][item]["bill"] = rsBill
lsSql = r"select rtrim(newbillId), " \
r" rtrim(branchId), " \
r" business, " \
r" foodbill, " \
r" foodid, " \
r" foodname, " \
r" unit, " \
r" isnull(prcOld, Prc), " \
r" Prc, " \
r" qty, " \
r" extPrc, " \
r" disAmt, " \
r" serviceFees, " \
r" amt, " \
r" SuitFlag, " \
r" RetSendFlag, " \
r" made " \
r"from foodbillEntity " \
r"where branchid = '{branchno}' " \
r"and business >= '{sOld}' " \
r"and business <= '{sDate}' " \
r"order by foodbill asc " \
r"".format(branchno=branch, sOld=sFrom, sDate=sTo)
cur.execute(lsSql)
rsItem = cur.fetchall()
rtnData["entities"][item]["item"] = rsItem
lsSql = r"select rtrim(newbillId), " \
r" rtrim(branchId), " \
r" business, " \
r" billNum, " \
r" PayId, " \
r" PayName, " \
r" PayType, " \
r" unit, " \
r" OldAmt, " \
r" ExchRate, " \
r" PayAmt " \
r"from foodbillpay " \
r"where branchid = '{branchno}' " \
r"and business >= '{sOld}' " \
r"and business <= '{sDate}' " \
r"order by billnum asc " \
r"".format(branchno=branch, sOld=sFrom, sDate=sTo)
cur.execute(lsSql)
rsPay = cur.fetchall()
rtnData["entities"][item]["pay"] = rsPay
maxDate = ""
for i in rsBill:
sBillDate = i[2].strftime("%Y-%m-%d")
if sBillDate > maxDate:
maxDate = sBillDate
rtnData["dataString"] = maxDate
# 关闭连接
conn.close()
return rtnData
class InterData():
def __init__(self, sett):
self.sett = sett
self.db = MSSQL(self.sett.serverHost, self.sett.serverUser,
self.sett.serverPwd, self.sett.serverDb)
def securityVerify(self, headers, parab):
import hashlib
rtnData = {"status": 0, "message": ""}
try:
# 安全校验
paras = {}
if "appid" in headers:
paras["appid"] = headers["appid"]
else:
raise Exception("请提供请求appid")
if "timestamp" in headers:
paras["timestamp"] = headers["timestamp"]
else:
raise Exception("请提供请求时间戳timestamp")
if "sign" in headers:
sSign1 = headers["sign"]
else:
raise Exception("请提供验证签名sign")
if paras["appid"] == "inter_km_HLGVip":
sSecret = "km200915"
else:
raise Exception("appid错误")
iTimestamp = int(time.time())
if abs(iTimestamp - int(paras["timestamp"])) > 5 * 60:
raise Exception("请求时间与服务器时间不一致")
# 生成签名
paras.update(parab)
lPara = sorted(paras.items(), key=lambda x: x[0])
sSource = sSecret
for gitem in lPara:
sSource += str(gitem[0]) + str(gitem[1])
sSource += sSecret
sSign2 = hashlib.md5(sSource.encode(encoding='UTF-8')).hexdigest()
if sSign2 != sSign1:
self.sett.logger.error(
"时间：{timestr}，参数字符串：{paras}，签名：{sign}".format(
timestr=datetime.datetime.strftime(
datetime.datetime.now(), "%Y-%m-%d %H:%M:%S"),
paras=sSource,
sign=sSign2))
raise Exception("签名验证失败")
rtnData["status"] = 1
except Exception as e:
self.sett.logger.error(str(e))
rtnData["message"] = str(e)
return rtnData
def setVIPInfo(self, data):
"""
推送会员注册
"""
rtnData = {"status": 0, "message": ""}
ibConnected = False
try:
conn = self.db.GetConnect()
ibConnected = True
cur = conn.cursor()
if not cur:
raise Exception("同步会员信息失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb))
# 存入新表：门店、营业日期、单据号、手机号、积分标志
sDate = datetime.datetime.strftime(datetime.datetime.now(),
"%Y-%m-%d")
lsSql = r"select count(*) from Vip where Id='{vipId}'".format(
vipId=data["Id"])
cur.execute(lsSql)
rsTmp = cur.fetchall()
if rsTmp[0][0] == 0:
data[
"validEndTime"] = "2099-08-01 23:59:59" # 有效期由线上生成二维码时判断，线上不会把有效期更新推送到线下
if len(data["cardNo"]) == 18:
if data["cardNo"][6:14].isdigit():
sBirthday = data["cardNo"][6:10] + "-" + data[
"cardNo"][10:12] + "-" + data["cardNo"][
12:14] + " 00:00:00.000"
else:
sBirthday = '1990-01-01 00:00:00.000'
else:
sBirthday = '1990-01-01 00:00:00.000'
lsSql = r"insert into Vip ( Id, VipClsId, CardFlag, OriginalId, IdentityCardId, Status, BeginDate, EndDate, BranchId, SendDate, Name, Sex, Mobile, Email, Birthday, Deposit, Dues, TotalConsTimes, TotalConsAmt, TotalIntegral, Integral, RechargingAmt, Balance, EncryptBalance, IntegralFlag, OperId, OperDate, SendMan, CardSendFlag, BackFlag, CardMakeFlag, IsSync ) " \
r"values ( '{vipId}', '{VipClsId}', 'O', '{vipId}', '{IdentityCardId}', '0', '{BeginDate}', '{EndDate}', '{branchno}', '{sDate}', '{vipName}', '男', '{Mobile}', '{Mobile}', '{Birthday}', 0.00, 0.00, 0, 0.00, 0.00, 0.00, 0.00, 0.00, 'CJ;=D538CL;=', '1', '0000', '{sDate}', '0000', '1', '1', '1', '1' )" \
r"".format(
vipId=data["Id"],
VipClsId=self.sett.vipClass,
Mobile=data["Phone"],
Birthday=sBirthday,
IdentityCardId=data["cardNo"],
vipName=data["custName"],
BeginDate=data["validBegTime"],
EndDate=data["validEndTime"],
branchno=self.sett.branchNo,
sDate=sDate
)
cur.execute(lsSql)
conn.commit()
rtnData["status"] = 1
rtnData["message"] = "ok"
except Exception as e:
rtnData["message"] = str(e)
finally:
if (ibConnected):
conn.close()
return rtnData
def setVIPCode(self, data):
"""
推送会员二维码
"""
rtnData = {"status": 0, "message": ""}
ibConnected = False
try:
conn = self.db.GetConnect()
ibConnected = True
cur = conn.cursor()
if not cur:
raise Exception("同步会员动态二维码失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb))
# 存入新表：门店、营业日期、单据号、手机号、积分标志
lsSql = r"update Vip set Mobile='{Qrcode}' where Id='{vipId}'".format(
vipId=data["Id"], Qrcode=data["Qrcode"])
cur.execute(lsSql)
conn.commit()
rtnData["status"] = "1"
rtnData["message"] = "ok"
except Exception as e:
rtnData["message"] = str(e)
finally:
if (ibConnected):
conn.close()
return rtnData
def queryBills(self, data):
"""
查询会员消费记录
{
"title":"欢乐谷出口小商店消费",
"price":100.00,
"date":"2020-08-01 12:15:12",
"sale":10.01
}
"""
rtnData = {"status": 0, "message": "", "data": []}
ibConnected = False
try:
conn = self.db.GetConnect()
ibConnected = True
cur = conn.cursor()
if not cur:
rtnData["message"] = "查询会员消费失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
lsSql = r"select '{title}', CardCost, operDate, ActualPayamt from VipCostList " \
r"where CardId = '{cardId}' and convert(char(10), OperDate, 120) between '{startDate}' and '{endDate}' " \
r"and CardWay in ('充值','消费','退单','预扣','预扣还款','设定金额','挂账付款')".format(
title=self.sett.rptTitle,
cardId=data["Id"],
startDate=data["startDate"],
endDate=data["endDate"]
)
lsSql = r"select '{title}', master.FoodAmt, master.SettleTime, master.FoodAmt - master.PayAmt from foodbill master, FoodBillEntity detail " \
r"where master.NewBillId = detail.NewBillId and master.VipId = '{cardId}' " \
r"and CONVERT(char(10), master.Business, 120) between '{startDate}' and '{endDate}'".format(
title=self.sett.rptTitle,
cardId=data["Id"],
startDate=data["startDate"],
endDate=data["endDate"]
)
ldCol = ["title", "price", "date", "sale"]
cur.execute(lsSql)
rsTmp = cur.fetchall()
rtnData["data"] = [dict(zip(ldCol, line)) for line in rsTmp]
conn.commit()
rtnData["status"] = "1"
rtnData["message"] = "ok"
except Exception as e:
rtnData["message"] = str(e)
finally:
if (ibConnected):
conn.close()
return rtnData
# -*- coding:utf-8 -*-
"""
"""
__author__ = "Cliff.wang"
from interMssql import MSSQL
from datetime import date, datetime
if __name__ == "__main__":
if 1 == 2:
dbTest = MSSQL("192.168.0.101", "sa", "0Wangle?", "kmcy_v8")
conn = dbTest.GetConnect()
cur = conn.cursor()
lsSql = r"select max(dBusiness) from d_t_food_bill where cBranch_C = '11'"
cur.execute(lsSql)
res = cur.fetchall()
print(res)
if 1 == 2:
import schedule
import time
def dosomething():
print("I'm working...")
schedule.every(10).seconds.do(dosomething)
while True:
schedule.run_pending()
time.sleep(1)
class FrontKemaiTtysDb(BusiProcess):
"""
Kemai《天天饮食V8》作为营业前端，直连数据库：
菜品资料：导出
销售单据：导出
"""
def __init__(self, sett, endType):
"""
实例化
:param sett:
:param endType:
"""
super().__init__(sett, endType)
self.interType = "db"
self.interBase = "out"
self.interItems = {"item", "saleBill", "accBill"}
self.db = MSSQL(self.interConn["host"], self.interConn["user"], self.interConn["password"], self.interConn["database"])
def getBranchs(self):
"""
获取需要对接的门店列表
:return:
"""
rtnData = {
"result":True, # 逻辑控制 True/False
"dataString":"", # 字符串
"dataNumber":1, # 数字
"info":"", # 信息
"entities": {} # 表体集
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("基础数据获取失败：{name}数据库[{db}]连接失败".format(name=self.interName, db=self.interConn["database"]))
lsSql = r"select branch_no, branch_name " \
r"from bi_t_branch_info " \
r"where Property in (4,5) "
cur.execute(lsSql)
rsBranchs = cur.fetchall()
rsBranchs = [(i[0].rstrip(), i[1].rstrip()) for i in rsBranchs]
rtnData["entities"]["branch"] = {}
rtnData["entities"]["branch"]["branch"] = rsBranchs
# 关闭连接
conn.close()
return rtnData
def getBaseData(self, item):
"""
基础资料导出
:param item: 数据项
:return: 基础资料字典
item: 商品资料
"""
rtnData = {
"result":True, # 逻辑控制 True/False
"dataString":"", # 字符串
"dataNumber":1, # 数字
"info":"", # 信息
"entities": {} # 表体集
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("基础数据获取失败：{name}数据库[{db}]连接失败".format(name=self.interName, db=self.interConn["database"]))
if item == "item":
# 同步菜品数据：编码、名称
lsSql = r"select food.cFood_C, food.cFood_N, cls.cBigCls_C, food.cLitCls_C, food.sUnit, food.nPrc from c_t_food food, c_t_food_litCls cls where food.cLitCls_C = cls.cLitCls_C"
cur.execute(lsSql)
rsItem = cur.fetchall()
rtnData["entities"][item] = {}
rtnData["entities"][item][item] = rsItem
# 关闭连接
conn.close()
return rtnData
def getBusiData(self, item, branch, sFrom, sTo):
"""
销售单据导出
:param item: 数据项
:param branch: 门店
:param sFrom: 开始日期
:param sTo: 截至日期
:return: 导出的销售单据
maxDate: 本次同步的最大日期
bill: 主单表集合
item: 商品表集合
pay: 付款表集合
"""
rtnData = {
"result":True, # 逻辑控制 True/False
"dataString":"", # 字符串
"dataNumber":1, # 数字
"info":"", # 信息
"entities": {} # 表体集
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("业务数据获取失败：{name}数据库[{db}]连接失败".format(name=self.interName, db=self.interConn["database"]))
if item in {"saleBill", "accBill"}:
lsSql = r"select cBill_C, " \
r" cBranch_C, " \
r" dBusiness, " \
r" dtBillTime, " \
r" dtSettleTime, " \
r" nFoodAmt, " \
r" nServiceFee, " \
r" nMinPay, " \
r" nDisAmt, " \
r" nRoundAmt, " \
r" nOughtAmt, " \
r" nPayAmt, " \
r" eStatus, " \
r" remark " \
r"from d_t_food_bill " \
r"where cBranch_C = '{branchno}' " \
r"and dBusiness >= '{sOld}' " \
r"and dBusiness <= '{sDate}' " \
r"".format(branchno=branch, sOld=sFrom, sDate=sTo)
cur.execute(lsSql)
rsBill = cur.fetchall()
rtnData["entities"][item] = {}
rtnData["entities"][item]["bill"] = rsBill
lsSql = r"select bills.cBill_C, " \
r" bill.cBranch_C, " \
r" bills.dBusiness, " \
r" bills.cFoodBill, " \
r" bills.cFood_C, " \
r" bills.cFood_N, " \
r" bills.sUnit, " \
r" bills.nPrcOld, " \
r" bills.nPrc, " \
r" bills.nQty, " \
r" bills.nExtPrc, " \
r" bills.nDisAmt, " \
r" bills.nServiceFees, " \
r" bills.nAmt, " \
r" bills.eSuitFlag, " \
r" bills.eRetSendFlag, " \
r" bills.sMade " \
r"from d_t_food_bills bills, d_t_food_bill bill " \
r"where bills.cBill_C = bill.cBill_C " \
r"and bill.cBranch_C = '{branchno}' " \
r"and bills.dBusiness >= '{sOld}' " \
r"and bills.dBusiness <= '{sDate}' " \
r"order by bills.cFoodBill asc " \
r"".format(branchno=branch, sOld=sFrom, sDate=sTo)
cur.execute(lsSql)
rsItem = cur.fetchall()
rtnData["entities"][item]["item"] = rsItem
lsSql = r"select pay.cBill_C, " \
r" bill.cBranch_C, " \
r" pay.dBusiness, " \
r" pay.cBillNum, " \
r" pay.cPay_C, " \
r" pay.cPay_N, " \
r" pay.ePayType, " \
r" pay.sUnit, " \
r" pay.nOldAmt, " \
r" pay.nExchRate, " \
r" pay.nPayAmt " \
r"from d_t_bill_pay pay, d_t_food_bill bill " \
r"where pay.cBill_C = bill.cBill_C " \
r"and bill.cBranch_C = '{branchno}' " \
r"and pay.dBusiness >= '{sOld}' " \
r"and pay.dBusiness <= '{sDate}' " \
r"order by pay.cBillNum asc " \
r"".format(branchno=branch, sOld=sFrom, sDate=sTo)
cur.execute(lsSql)
rsPay = cur.fetchall()
rtnData["entities"][item]["pay"] = rsPay
maxDate = ""
for i in rsBill:
sBillDate = i[2].strftime("%Y-%m-%d")
if sBillDate > maxDate:
maxDate = sBillDate
rtnData["dataString"] = maxDate
# 关闭连接
conn.close()
return rtnData
def __init__(self, sett):
self.sett = sett
self.db = MSSQL(self.sett.serverHost, self.sett.serverUser,
self.sett.serverPwd, self.sett.serverDb)
rtn = self.initDb()
print(rtn)
class InterData():
def __init__(self, sett):
self.sett = sett
self.db = MSSQL(self.sett.serverHost, self.sett.serverUser,
self.sett.serverPwd, self.sett.serverDb)
rtn = self.initDb()
print(rtn)
def initDb(self):
"""
数据库初始化
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
ibConnected = False
try:
# 创建手机号码记录表：自等列、单据号、手机号码、是否已处理
conn = self.db.GetConnect()
ibConnected = True
cur = conn.cursor()
if not cur:
raise Exception("初始化失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb))
lsSql = r"select 1 from sysobjects where id=object_id('tmpPhone')"
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) == 0:
lsSql = r"CREATE TABLE tmpPhone(" \
r" sID int IDENTITY(1,1) NOT NULL," \
r" sBill varbinary(50) NOT NULL," \
r" sPhone varbinary(50) NOT NULL," \
r" primary key ( sID ) )"
cur.execute(lsSql)
conn.commit()
rtnData["result"] = True
rtnData["info"] = "建表完成"
else:
rtnData["info"] = "已存在临时表"
except Exception as e:
rtnData["info"] += str(e)
print(str(e))
finally:
if (ibConnected):
conn.close()
return rtnData
def handleScore(self):
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
return rtnData
def queryToken(self):
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
lsSql = r"select isnull(sys_var_value, '') from sys_t_system where sys_var_id = 'kmmicro_token'"
ibConnected = False
try:
conn = self.db.GetConnect()
ibConnected = True
cur = conn.cursor()
if not cur:
rtnData[
"info"] = "查询access token失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) == 0:
rtnData["info"] = "查询access token失败：参数没有初始化"
else:
rtnData["dataString"] = rsData[0][0]
rtnData["result"] = True
except Exception as e:
rtnData["info"] = str(e)
finally:
if (ibConnected):
conn.close()
return rtnData
def queryBillList(self, iStatus=2):
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
if iStatus == 0:
lsSql = r"select cBill_C, cTable_C, cTable_N, iGuestNum, nOughtAmt, bSettle, isnull(OpenId, '') from d_t_food_bill0 bill left join kmmicro_t_vip_salesend micro on bill.cBill_C = micro.orderid where bSettle = 0"
elif iStatus == 1:
lsSql = r"select cBill_C, cTable_C, cTable_N, iGuestNum, nOughtAmt, bSettle, isnull(OpenId, '') from d_t_food_bill0 bill left join kmmicro_t_vip_salesend micro on bill.cBill_C = micro.orderid where bSettle = 1"
else:
lsSql = r"select cBill_C, cTable_C, cTable_N, iGuestNum, nOughtAmt, bSettle, isnull(OpenId, '') from d_t_food_bill0 bill left join kmmicro_t_vip_salesend micro on bill.cBill_C = micro.orderid"
llKey = [
"cBill_C", "cTable_C", "cTable_N", "iGuestNum", "nOughtAmt",
"status", "openID"
]
ibConnected = False
try:
conn = self.db.GetConnect()
ibConnected = True
cur = conn.cursor()
if not cur:
rtnData["info"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
cur.execute(lsSql)
rsData = cur.fetchall()
rsData = [[(col.rstrip() if isinstance(col, str) else col)
for col in line] for line in rsData]
rtnData["entities"]["orderBill"] = []
for line in rsData:
rtnData["entities"]["orderBill"].append(
dict(zip(llKey, line)))
rtnData["result"] = True
except Exception as e:
rtnData["info"] = str(e)
finally:
if (ibConnected):
conn.close()
return rtnData
def callSoundTaking(self, sBill):
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
ibConnected = False
try:
conn = self.db.GetConnect()
ibConnected = True
cur = conn.cursor()
if not cur:
rtnData[
"info"] = "查询access token失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
lsSql = r"select convert(char(10), dBusiness, 121), bankcard_no from d_t_food_bill0 where cBill_C = '{sBill}'".format(
sBill=sBill)
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) == 0:
raise Exception('单据号[{sBill}]无效.'.format(sBill=sBill))
else:
sBusiness = rsData[0][0]
sNumber = rsData[0][1]
lsSql = r"insert into sa_t_voice_queue ( voice_type , voice_value , dbusiness , bill_no , oper_id , oper_date , oper_computer , call_flag , call_time ) " \
r"values ( '取餐' , '{snumber}' , '{dBusiness}' , '{sBill}' , '0000' , getdate ( ) , '{computer}' , 0 , null ) " \
r"".format(snumber=sNumber, dBusiness=sBusiness, sBill=sBill, computer=self.sett.callComputer)
cur.execute(lsSql)
conn.commit()
rtnData["result"] = True
rtnData["info"] = "请求呼叫成功."
except Exception as e:
rtnData["info"] = str(e)
print(str(e))
finally:
if (ibConnected):
conn.close()
return rtnData
def callWXTaking(self, sBill):
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
ibConnected = False
try:
if len(sBill) == 0:
raise Exception("请传入订单号")
conn = self.db.GetConnect()
ibConnected = True
cur = conn.cursor()
if not cur:
raise Exception("查询km服务地址失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb))
if self.sett.kmHttpSrv == "" or self.sett.kmHttpPort == "":
lsSql = r"select isnull(sys_var_value, '') from sys_t_system where sys_var_id = 'kmmicro_ip'"
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) == 0:
raise Exception("科脉通讯助手没有正确配置.")
self.sett.kmHttpSrv = rsData[0][0]
lsSql = r"select isnull(sys_var_value, '') from sys_t_system where sys_var_id = 'kmmicro_port'"
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) == 0:
raise Exception("科脉通讯助手没有正确配置.")
self.sett.kmHttpPort = rsData[0][0]
if self.sett.kmHttpSrv == "" or self.sett.kmHttpPort == "":
raise Exception("微信通讯助手参数无效.")
lsSql = r"select ISNULL(orderid, '') from d_t_food_bill0 where cBill_C = '{bill}'".format(
bill=sBill)
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) == 0:
raise Exception("查无此订单[{bill}]".format(bill=sBill))
lsOrderID = rsData[0][0]
lsRequest = "http://{srvPath}:{srvPort}/microservices/UpdateOrderGainMealFlag/{bill}".format(
srvPath=self.sett.kmHttpSrv,
srvPort=self.sett.kmHttpPort,
bill=lsOrderID)
import urllib
rtnData["info"] = "微信通讯助手响应超时."
response = urllib.request.urlopen(lsRequest)
rtnData["info"] = ""
lsResponse = response.read().decode("gbk")
if len(lsResponse) == 0:
rtnData["info"] = "未知的错误"
else:
if lsResponse[0] == "1":
rtnData["result"] = True
rtnData["info"] = lsResponse[2:]
except Exception as e:
rtnData["info"] += str(e)
finally:
if (ibConnected):
conn.close()
return rtnData
def setVipPhone(self, sBill, sPhone):
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
ibConnected = False
try:
if len(sBill) == 0:
raise Exception("请传入订单号")
if len(sPhone) == 0:
raise Exception("请传入手机号")
conn = self.db.GetConnect()
ibConnected = True
cur = conn.cursor()
if not cur:
raise Exception("设置会员手机号失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb))
lsSql = r"select 1 from d_t_food_bill0 where cBill_C = '{bill}'".format(
bill=sBill)
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) == 0:
raise Exception("查无此订单[{bill}]".format(bill=sBill))
# 存入新表：门店、营业日期、单据号、手机号、积分标志
lsSql = r"insert into tmpPhone ( sBill, sPhone ) values ( '{sBill}', '{sPhone}' )".format(
sBill=sBill, sPhone=sPhone)
cur.execute(lsSql)
conn.commit()
rtnData["result"] = True
rtnData["info"] = "会员手机号码提交成功"
except Exception as e:
rtnData["info"] += str(e)
finally:
if (ibConnected):
conn.close()
return rtnData
def queryMealNumber(self):
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
ibConnected = False
try:
conn = self.db.GetConnect()
ibConnected = True
cur = conn.cursor()
if not cur:
raise Exception("查询取餐号失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb))
lsSql = r"select bill.cBill_C, bill.cTable_N, iGuestNum, nOughtAmt, num.cgetfood_num from d_t_food_bill0 bill, d_t_foodbill num where bill.cBill_C = num.cbill_c"
cur.execute(lsSql)
rsData = cur.fetchall()
llKey = [
"cBill_C", "cTable_N", "iGuestNum", "nOughtAmt", "cgetfood_num"
]
rsData = [[(col.rstrip() if isinstance(col, str) else col)
for col in line] for line in rsData]
rtnData["entities"]["orderBill"] = []
for line in rsData:
rtnData["entities"]["orderBill"].append(dict(zip(llKey, line)))
rtnData["result"] = True
except Exception as e:
rtnData["info"] = str(e)
finally:
if (ibConnected):
conn.close()
return rtnData
class BillBoli600(SuperBillDCB):
def __init__(self, sett):
super().__init__(sett)
self.station = [1, 2, 3, 4, 5, 6, 7, 8] # 可用基站
self.db = MSSQL(self.sett.serverHost, self.sett.serverUser,
self.sett.serverPwd, self.sett.serverDb)
def _getStation(self):
"""
获取基站号
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
try:
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
rtnData["info"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
lsSql = "select sys_var_value from sys_t_system where sys_var_id = 'dcb_stationList'"
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) > 0:
staStr = rsData[0][0]
else:
staStr = "[]"
staList = json.loads(staStr)
if len(staList) == 0:
rtnData["info"] = "基站繁忙，请稍后再试"
else:
rtnData["dataNumber"] = staList.pop(0)
lsSql = "update sys_t_system set sys_var_value = '{value}' where sys_var_id = 'dcb_stationList'".format(
value=json.dumps(staList))
cur.execute(lsSql)
conn.commit()
rtnData["result"] = True
except Exception as e:
rtnData["dataNumber"] = 0
rtnData["info"] = str(e)
finally:
conn.close()
return rtnData
def _putStation(self, station):
"""
释放基站号
:param station:
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
try:
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
rtnData["info"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
lsSql = "select sys_var_value from sys_t_system where sys_var_id = 'dcb_stationList'"
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) > 0:
staStr = rsData[0][0]
staList = json.loads(staStr)
staList.append(station)
staList = list(set(staList))
staList.sort()
lsSql = "update sys_t_system set sys_var_value = '{value}' where sys_var_id = 'dcb_stationList'".format(
value=json.dumps(staList))
cur.execute(lsSql)
conn.commit()
rtnData["result"] = True
else:
rtnData["info"] = "获取基站参数失败"
except Exception as e:
rtnData["info"] = str(e)
finally:
conn.close()
return rtnData
def userLogin(self, data):
"""
登录
:param data:{
"terminal":"", # 开台终端号（3位）
"factory":"", # 出厂号（10位）
"user":"", # 工号（4位）
"password":"" # 密码（8位）
}
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 获取基站
rtnData = self._getStation()
if rtnData["result"]:
iStation = rtnData["dataNumber"]
try:
# 参数检查
if len(self.sett.softNumber) > 0:
data["terminal"] = self.sett.softNumber
elif "terminal" not in data:
raise Exception("请传入参数：点菜宝编号")
sTerminal = (data["terminal"] + chr(32) * 3)[:3]
if len(self.sett.serialNumber) > 0:
data["factory"] = self.sett.serialNumber
elif "factory" not in data:
raise Exception("请传入参数：点菜宝序列号")
sFactory = ("0" * 10 + data["factory"])[-10:]
if len(self.sett.loginUser) > 0:
data["user"] = self.sett.loginUser
data["password"] = self.sett.loginPassword
elif "user" not in data:
raise Exception("请传入参数：用户及密码")
sUser = (data["user"] + chr(32) * 5)[:4]
sPassword = (data["password"] + chr(32) * 8)[:8]
# 生成开台请求数据
sCon = []
sCon.append("DL " + chr(32) + sTerminal)
sCon.append(sFactory + chr(32) + sUser + chr(32) + sPassword)
# sCon.append(sUser + chr(32) + sPassword)
# 开台请求写入文件，并通知餐饮服务
if rtnData["result"]:
rtnData = self._writeBusiData(iStation, sCon)
# 获取执行结果
if rtnData["result"]:
rtnData = self._readRtnData(iStation, "登录", sCon, 0, "", 1)
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 释放基站
if "iStation" in locals():
self._putStation(iStation)
# 返回执行结果
return rtnData
def billOpen(self, data):
"""
开台
:param data:{
"terminal":"", # 开台终端号（3位）
"table":"", # 桌台号（4位）
"waiter":"", # 服务员号（5位）
"guestNum":0, # 客人数量（2位）
"factory":"" # 出厂号（后7/10位）
}
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 获取基站
rtnData = self._getStation()
if rtnData["result"]:
iStation = rtnData["dataNumber"]
try:
# 参数检查
if len(self.sett.softNumber) > 0:
data["terminal"] = self.sett.softNumber
elif "terminal" not in data:
raise Exception("请传入参数：点菜宝编号")
sTerminal = (data["terminal"] + chr(32) * 3)[:3]
if "table" in data:
sTable = (data["table"] + chr(32) * 4)[:4]
else:
rtnData["result"] = False
rtnData["info"] = "请传入桌台号"
if "waiter" in data:
sWaiter = (data["waiter"] + chr(32) * 5)[:5]
else:
sWaiter = chr(32) * 5
if "guestNum" in data:
sGuestNum = ("0" + str(int(data["guestNum"])))[-2:]
else:
sGuestNum = "01"
if len(self.sett.serialNumber) > 0:
data["factory"] = self.sett.serialNumber
elif "factory" not in data:
raise Exception("请传入参数：点菜宝序列号")
sFactory = ("0" * 10 + data["factory"])
# 生成开台请求数据
if rtnData["result"]:
sCon = []
sCon.append("KT " + chr(32) + sTerminal)
sCon.append(sTable + chr(32) + sGuestNum + chr(32) + sWaiter +
chr(32) + sFactory[-7:] + chr(32) +
time.strftime("%H:%M:%S"))
# 开台请求，并获取反馈
if rtnData["result"]:
rtnData = self._writeBusiData(iStation, sCon)
# 获取执行结果
if rtnData["result"]:
rtnData = self._readRtnData(iStation, "开台", sCon, 1, "开台成功", 1)
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 释放基站
if "iStation" in locals():
self._putStation(iStation)
# 返回执行结果
return rtnData
def billPut(self, data):
"""
点菜
:param data:{
"terminal":"", # 开台终端号（3位）
"table":"", # 桌台号+账单流水号（4+3=7位）
"factory":"", # 出厂号（4+4+2=10位）
"remark":"", # 整单备注（12位）
"item":[{
"food":"", # 菜品号（5位）
"qty":1, # 数量（4位）
"made":"", # 做法（12位）
"suit":"", # 套餐号（2位）
"waitUp":0 # 等叫标志（1位）
}]
}
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 获取基站
rtnData = self._getStation()
if rtnData["result"]:
iStation = rtnData["dataNumber"]
try:
# 参数检查
if len(self.sett.softNumber) > 0:
data["terminal"] = self.sett.softNumber
elif "terminal" not in data:
raise Exception("请传入参数：点菜宝编号")
sTerminal = (data["terminal"] + chr(32) * 3)[:3]
if "table" in data:
sTable = (data["table"] + chr(32) * 7)[:7]
else:
rtnData["result"] = False
rtnData["info"] = "请传入桌台号"
if len(self.sett.serialNumber) > 0:
data["factory"] = self.sett.serialNumber
elif "factory" not in data:
raise Exception("请传入参数：点菜宝序列号")
sFactory = ("0" * 10 + data["factory"])
if "remark" in data:
sRemark = (data["remark"] + chr(32) * 12)[:12]
else:
sRemark = chr(32) * 12
sFlow = time.strftime("%H:%M:%S")
# 生成开台请求数据
if rtnData["result"]:
sCon = []
sCon.append("DC " + chr(32) + sTerminal)
sCon.append(sTable + chr(32) + sFactory[:4] + chr(32) +
chr(32) * 6 + sRemark + chr(32) + chr(32) * 4 +
sFlow + chr(32) + sFactory[4:8] + chr(32) +
sFactory[8:10])
for line in data["item"]:
sFood = (line["food"] + chr(32) * 5)[:5]
sQty = (chr(32) * 4 + str(line["qty"]))[-4:]
if "made" in line:
sMade = (line["made"] + chr(32) * 12)[:12]
else:
sMade = chr(32) * 12
if "suit" in line:
suit = (line["suit"] + chr(32) * 2)[:2]
else:
suit = chr(32) * 2
if "waitUp" in line:
waitUp = (str(line["waitUp"]) + "0")[-1:]
else:
waitUp = "0"
sCon.append(sTable + chr(32) + sFood + chr(32) + sQty +
chr(32) + sMade + chr(32) + suit + chr(32) +
waitUp)
# 开台请求写入文件，并通知餐饮服务
if rtnData["result"]:
rtnData = self._writeBusiData(iStation, sCon)
# 获取执行结果
if rtnData["result"]:
rtnData = self._readRtnData(iStation, "点菜", sCon, 1, "点菜成功", 1)
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 释放基站
if "iStation" in locals():
self._putStation(iStation)
# 返回执行结果
return rtnData
def __init__(self, sett):
super().__init__(sett)
self.station = [1, 2, 3, 4, 5, 6, 7, 8] # 可用基站
self.db = MSSQL(self.sett.serverHost, self.sett.serverUser,
self.sett.serverPwd, self.sett.serverDb)
class BackTestDb(BusiProcess):
"""
自建测试数据库作为业务后端，直连数据库：
菜品资料：导入
销售单据：导入
"""
def __init__(self, sett, endType):
"""
实例化
:param sett:
:param endType:
"""
super().__init__(sett, endType)
self.interType = "db"
self.interBase = "in"
self.interItems = {"item", "saleBill"}
self.db = MSSQL(self.interConn["host"], self.interConn["user"], self.interConn["password"], self.interConn["database"])
def interInit(self):
"""
初始化
:return:
"""
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("后端初始化失败：{name}数据库[{db}]连接失败".format(name=self.interName, db=self.interConn["database"]))
# 创建菜品表
lsSql = r"select 1 from sysobjects where xtype = 'U' and id = OBJECT_ID('newFood')"
cur.execute(lsSql)
rs = cur.fetchall()
if len(rs) == 0:
# 创建目标菜品表
lsSql = r"create table newFood ( " \
r" foodid varchar(20) not null, " \
r" foodname varchar(60) not null, " \
r" classid varchar(20) not null, " \
r" price numeric(10,2) not null, " \
r" primary key ( foodid ) ) "
cur.execute(lsSql)
# 创建单据主表
lsSql = r"select 1 from sysobjects where xtype = 'U' and id = OBJECT_ID('billMaster')"
cur.execute(lsSql)
rs = cur.fetchall()
if len(rs) == 0:
# 创建目标单据主表
lsSql = r"create table billMaster ( " \
r" billId varchar(50) not null, " \
r" branchId varchar(20) not null, " \
r" busiDate char(10) not null, " \
r" tableId varchar(20) not null, " \
r" periodId varchar(20) not null, " \
r" shiftId varchar(20) null, " \
r" guestNum int null, " \
r" createTime datetime null, " \
r" settleTime datetime null, " \
r" foodAmt numeric(9,2) not null, " \
r" serviceFee numeric(9,2) not null, " \
r" minfillFee numeric(9,2) not null, " \
r" disAmt numeric(9,2) not null, " \
r" roundAmt numeric(9,2) not null, " \
r" oughtPay numeric(9,2) not null, " \
r" paid numeric(9,2) not null, " \
r" status varchar(20) not null, " \
r" primary key ( billId ) ) "
cur.execute(lsSql)
# 创建单据菜品表
lsSql = r"select 1 from sysobjects where xtype = 'U' and id = OBJECT_ID('billItem')"
cur.execute(lsSql)
rs = cur.fetchall()
if len(rs) == 0:
# 创建目标单据商品表
lsSql = r"create table billItem ( " \
r" billId varchar(50) not null, " \
r" branchId varchar(20) not null, " \
r" busiDate char(10) not null, " \
r" batchNum int not null, " \
r" orderNum int not null, " \
r" ItemId varchar(20) not null, " \
r" ItemName varchar(80) not null, " \
r" unit varchar(20) not null, " \
r" prcOld numeric(9,2) not null, " \
r" prc numeric(9,2) not null, " \
r" qty numeric(9,2) not null, " \
r" amtExt numeric(9,2) not null, " \
r" amtDis numeric(9,2) not null, " \
r" serviceFee numeric(9,2) not null, " \
r" amt numeric(9,2) not null, " \
r" suitFlag varchar(20) not null, " \
r" itemFlag varchar(20) not null, " \
r" made varchar(255) not null, " \
r" primary key ( billId, batchNum, orderNum ) ) "
cur.execute(lsSql)
# 创建单据支付表
lsSql = r"select 1 from sysobjects where xtype = 'U' and id = OBJECT_ID('billPay')"
cur.execute(lsSql)
rs = cur.fetchall()
if len(rs) == 0:
# 创建目标单据支付表
lsSql = r"create table billPay ( " \
r" billId varchar(50) not null, " \
r" branchId varchar(20) not null, " \
r" busiDate char(10) not null, " \
r" batchNum int not null, " \
r" payId varchar(20) not null, " \
r" payName varchar(80) not null, " \
r" payType varchar(80) not null, " \
r" unit varchar(20) not null, " \
r" payOrigi numeric(9,2) not null, " \
r" exchRate numeric(9,5) not null, " \
r" payAmt numeric(9,2) not null, " \
r" primary key ( billId, batchNum ) ) "
cur.execute(lsSql)
# 提交事务，关闭连接
conn.commit()
conn.close()
def putBaseData(self, putData):
"""
基础数据导入
:param putData:
:return:
"""
rtnData = {
"result":True, # 逻辑控制 True/False
"dataString":"", # 字符串
"dataNumber":1, # 数字
"info":"", # 信息
"entities": { # 表体集
"item": { # 表体代码
"item":[] # 菜品表
}
}
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("基础数据写入失败：{name}数据库[{db}]连接失败".format(name=self.interName, db=self.interConn["database"]))
for i in putData["entities"]["item"]["item"]:
lsSql = r"insert into newFood ( foodId, foodName, classId, price ) " \
r"values ( '{foodid}', '{foodname}', '{classid}', {price} ) " \
r"".format(foodid=i[0], foodname=i[1], classid=i[2], price=i[3])
cur.execute(lsSql)
rtnData["entities"]["item"]["item"].append((i[0], i[1], "", ""))
# 提交事务，关闭连接
conn.commit()
conn.close()
return rtnData
def putBusiData(self, branch, putData):
"""
销售单据导入
:param branch: 门店
:param putData: 传入的销售单据
:return:
"""
rtnData = {
"result":True, # 逻辑控制 True/False
"dataString":"", # 字符串
"dataNumber":1, # 数字
"info":"", # 信息
"entities": { # 表体集
"saleBill": { # 表体代码
"saleBill":[] # 销售单
}
}
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("业务数据写入失败：{name}数据库[{db}]连接失败".format(name=self.interName, db=self.interConn["database"]))
# 插入主单
# business, newbillid, BillTime, SettleTime, FoodAmt, ServiceFee, MinPayFill, DisAmt, RoundAmt, OughtAmt, PayAmt, statuss
for i in putData["entities"]["saleBill"]["bill"]:
lsSql = r"insert into billMaster ( billId, branchId, busiDate, tableId, periodId, shiftId, guestNum, " \
r"createTime, settleTime, foodAmt, serviceFee, minfillFee, disAmt, roundAmt, oughtPay, paid, status ) " \
r"values ('{billId}', '{branchId}', '{busiDate}', '{tableId}', '{periodId}', '{shiftId}', {guestNum}, " \
r"'{createTime}', '{settleTime}', {foodAmt}, {serviceFee}, {minfillFee}, {disAmt}, {roundAmt}, {oughtPay}, {paid}, '{status}' ) " \
r"".format(
billId=i[0].rstrip(),
branchId=i[1],
busiDate=datetime.strftime(i[2], "%Y-%m-%d"),
tableId=self.sett.tableNo,
periodId=self.sett.periodNo,
shiftId=self.sett.shiftNo,
guestNum=1,
createTime=datetime.strftime(i[3], "%Y-%m-%d %H:%M:%S"),
settleTime=datetime.strftime(i[4], "%Y-%m-%d %H:%M:%S"),
foodAmt=i[5],
serviceFee=i[6],
minfillFee=i[7],
disAmt=i[8],
roundAmt=i[9],
oughtPay=i[10],
paid=i[11],
status=i[12]
)
cur.execute(lsSql)
rtnData["entities"]["saleBill"]["saleBill"].append((i[0].rstrip(), "", i[1], datetime.strftime(i[2], "%Y-%m-%d")))
# 插入项目表
# newbillid, business, foodbill, foodid, foodname, unit, prcOld, Prc, qty, extPrc, disAmt, serviceFees, amt, SuitFlag, RetSendFlag, made
for i in putData["entities"]["saleBill"]["item"]:
lsSql = r"insert into billItem ( billId, branchId, busiDate, batchNum, orderNum, itemId, itemName, " \
r"unit, prcOld, prc, qty, amtExt, amtDis, serviceFee, amt, suitFlag, itemFlag, made ) " \
r"values ( '{billId}', '{branchId}', '{busiDate}', {batchNum}, {orderNum}, '{itemId}', '{itemName}', " \
r"'{unit}', {prcOld}, {prc}, {qty}, {amtExt}, {amtDis}, {serviceFee}, {amt}, '{suitFlag}', '{itemFlag}', '{made}' ) " \
r"".format(
billId=i[0].rstrip(),
branchId=i[1],
busiDate=datetime.strftime(i[2], "%Y-%m-%d"),
batchNum=int(i[3][0:2]),
orderNum=int(i[3][2:]),
itemId=i[4].rstrip(),
itemName=i[5].rstrip(),
unit=i[6].rstrip(),
prcOld=i[7],
prc=i[8],
qty=i[9],
amtExt=i[10],
amtDis=i[11],
serviceFee=i[12],
amt=i[13],
suitFlag=i[14],
itemFlag=i[15],
made=i[16]
)
cur.execute(lsSql)
# 插入支付表
# newbillid, business, billNum, PayId, PayName, PayType, unit, OldAmt, ExchRate, PayAmt
for i in putData["entities"]["saleBill"]["pay"]:
lsSql = r"insert into billPay ( billId, branchId, busiDate, batchNum, payId, payName, payType, unit, payOrigi, exchRate, payAmt ) " \
r"values ( '{billId}', '{branchId}', '{busiDate}', {batchNum}, '{payId}', '{payName}', '{payType}', '{unit}', {payOrigi}, {exchRate}, {payAmt} ) " \
r"".format(
billId=i[0].rstrip(),
branchId=i[1],
busiDate=datetime.strftime(i[2], "%Y-%m-%d"),
batchNum=int(i[3]),
payId=i[4].rstrip(),
payName=i[5].rstrip(),
payType=i[6].rstrip(),
unit=i[7].rstrip(),
payOrigi=i[8],
exchRate=i[9],
payAmt=i[10]
)
cur.execute(lsSql)
# 提交事务，关闭连接
conn.commit()
conn.close()
return rtnData
class FrontHongshang(BusiProcess):
"""
鸿商作为营业前端，直连数据库：
"""
def __init__(self, sett):
"""
实例化
:param sett:
"""
super().__init__(sett)
self.interName = "鸿商系统"
self.db = MSSQL(self.interConn["host"], self.interConn["user"],
self.interConn["password"], self.interConn["database"])
self.interInit()
def _getOrderNewNo(self, cursor):
"""
获取新单据号
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {} # 表体集
}
iYear = datetime.datetime.strftime(datetime.datetime.today(), "%y")
sYear = str(iYear).rjust(2, "0")
lsSql = "select max(RIGHT(orderid, 8)) from orderselfapp where orderid like \'00{year}%\'".format(
year=sYear)
cursor.execute(lsSql)
ds = cursor.fetchall()
if ds[0][0]:
iNum = int(ds[0][0])
else:
iNum = 0
iNum += 1
rtnData["dataString"] = "00" + sYear + str(iNum).rjust(8, "0")
return rtnData
def interInit(self):
"""
初始化
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {}
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception(
"初始化失败：线下数据库[{db}]连接失败".format(db=self.interConn["database"]))
# 创建参数表
lsSql = r"select 1 from sysobjects where xtype = 'U' and id = OBJECT_ID('pluOnline')"
cur.execute(lsSql)
rs = cur.fetchall()
if len(rs) == 0:
lsSql = r"create table pluOnline ( " \
r" cID int not null IDENTITY(1,1), " \
r" comid char(8) null, " \
r" barcode varchar(20) null, " \
r" relatedCode varchar(50) null, " \
r" status int not null default 1, " \
r" soon int not null default 0, " \
r" primary key ( cID ) ) "
cur.execute(lsSql)
return rtnData
def getItems(self, itemNo):
"""
获取商品列表
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {} # 表体集
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.interName, db=self.interConn["database"]))
# itemNo = "00021509" # 购物袋小
# itemNo = "00036967" # 一毛钱的糖果
lsSql = r"select plu.comid '第三方商品id', " \
r"plu.comname '商品名称', " \
r"'' '商品商标', " \
r"'' '商品主图', " \
r"'' '商品图片列表', " \
r"'' '图文详情', " \
r"plu.remark '商品描述', " \
r"0 '库存', " \
r"0 '虚拟销量', " \
r"0 '限量销售', " \
r"0 '单次限量销售', " \
r"'' '扩展参数', " \
r"0 '成本', " \
r"0 '基本邮费', " \
r"1 '邮费计算方式', " \
r"plu.unit '单位', " \
r"plu.saleprice '销售价', " \
r"plu.saleprice '原价', " \
r"1 '上门自提', " \
r"'{pickupDelay}' '自提延时时间', " \
r"'' '自提开始时间', " \
r"'' '自提结束时间', " \
r"0 '排序', " \
r"'2020-01-01' '开始可用时间', " \
r"'2222-01-01' '结束可用时间', " \
r"pluOnline.status '上架状态' " \
r"from plu, " \
r" pluOnline " \
r"where plu.comid=pluOnline.comid " \
r"and pluOnline.status=1 " \
r"and plu.lowsalesum >= 0.0 " \
r"and plu.status in ('A','B','C','') ".format(pickupDelay=self.sett.pickupDelay)
if itemNo:
lsSql += r" and pluOnline.comid = '{itemNo}'".format(itemNo=itemNo)
colName = [
"out_goods_id", "goods_name", "logo", "master_picture", "pictures",
"description", "comment", "stock", "virtual_sales", "limited_sale",
"limited_single", "extra", "cost", "postage", "postage_type",
"unit", "price", "original_price", "pickup", "pickup_delay_time",
"pickup_start_time", "pickup_end_time", "sort", "start_time",
"end_time", "status"
]
cur.execute(lsSql)
rsItems = cur.fetchall()
rsItems = [[(col.rstrip() if isinstance(col, str) else col)
for col in line] for line in rsItems]
rtnData["entities"]["item"] = []
for line in rsItems:
rtnData["entities"]["item"].append(dict(zip(colName, line)))
# 关闭连接
conn.close()
return rtnData
def getItemsChanged(self):
"""
获取商品列表
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {} # 表体集
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.interName, db=self.interConn["database"]))
# itemNo = "00021509" # 购物袋小
# itemNo = "00036967" # 一毛钱的糖果
lsSql = r"select plu.comid '第三方商品id', " \
r"plu.comname '商品名称', " \
r"'' '商品商标', " \
r"'' '商品主图', " \
r"'' '商品图片列表', " \
r"'' '图文详情', " \
r"plu.remark '商品描述', " \
r"0 '库存', " \
r"0 '虚拟销量', " \
r"0 '限量销售', " \
r"0 '单次限量销售', " \
r"'' '扩展参数', " \
r"0 '成本', " \
r"0 '基本邮费', " \
r"1 '邮费计算方式', " \
r"plu.unit '单位', " \
r"plu.saleprice '销售价', " \
r"plu.saleprice '原价', " \
r"1 '上门自提', " \
r"'{pickupDelay}' '自提延时时间', " \
r"'' '自提开始时间', " \
r"'' '自提结束时间', " \
r"0 '排序', " \
r"'2020-01-01' '开始可用时间', " \
r"'2222-01-01' '结束可用时间', " \
r"pluOnline.status '上架状态' " \
r"from plu, " \
r" pluOnline " \
r"where plu.comid=pluOnline.comid " \
r"and pluOnline.soon=1 ".format(pickupDelay=self.sett.pickupDelay)
colName = [
"out_goods_id", "goods_name", "logo", "master_picture", "pictures",
"description", "comment", "stock", "virtual_sales", "limited_sale",
"limited_single", "extra", "cost", "postage", "postage_type",
"unit", "price", "original_price", "pickup", "pickup_delay_time",
"pickup_start_time", "pickup_end_time", "sort", "start_time",
"end_time", "status"
]
cur.execute(lsSql)
rsItems = cur.fetchall()
rsItems = [[(col.rstrip() if isinstance(col, str) else col)
for col in line] for line in rsItems]
rtnData["entities"]["item"] = []
for line in rsItems:
rtnData["entities"]["item"].append(dict(zip(colName, line)))
# 关闭连接
conn.close()
return rtnData
def updateItemsChanged(self, itemList):
"""
重置商品更新状态
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {
"item": []
} # 表体集
}
try:
# 获取数据库连接
bConnected = False
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.interName, db=self.interConn["database"]))
bConnected = True
for item in itemList:
lsSql = r"update pluOnline set soon=0 where comid='{comid}' and soon=1".format(
comid=item["code"])
cur.execute(lsSql)
conn.commit()
rtnData["entities"]["item"].append(item["code"])
except Exception as e:
# 关闭连接
if bConnected:
conn.rollback()
conn.close()
rtnData["result"] = False
rtnData["info"] = str(e)
return rtnData
def putOrders(self, dsOrder):
"""
写入订单
:param dsOrder: 新增订单列表
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {
"order": []
} # 表体集
}
# 获取数据库连接
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
raise Exception("业务数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.interName, db=self.interConn["database"]))
for bill in dsOrder["entities"]["order"]:
try:
lsSql = r"select count(*) from orderselfapp where outtradeno='{outtradeno}'".format(
outtradeno=bill["order_id"])
cur.execute(lsSql)
rsData = cur.fetchall()
if rsData[0][0]:
continue
rtnTmp = self._getOrderNewNo(cur)
if rtnTmp["result"]:
sBill = rtnTmp["dataString"]
else:
raise Exception(rtnTmp["info"])
lsSql = r"insert into orderselfapp (orderid, deptid, deptname, vipid, status, totalfee, disfee, discountfee, payfee, codeid, createtime, paytime, payedfee, outtradeno, paytype)" \
r"values ({orderid}, {deptid}, {deptname}, {vipid}, {status}, {totalfee}, {disfee}, {discountfee}, {payfee}, {codeid}, {createtime}, {paytime}, {payedfee}, {outtradeno}, {paytype})" \
r"".format(
orderid="'"+sBill+"'",
deptid="'"+self.sett.defaultOrgNo+"'",
deptname="'"+self.sett.defaultOrgName+"'",
vipid="''",
status="'CHECKING'",
totalfee=bill["amount"] / 100,
disfee=0.00 / 100,
discountfee=0.00 / 100,
payfee=bill["amount"] / 100,
codeid="'"+bill["pickup_code"]+"'",
createtime=("'"+bill["create_time"]+"'") if bill["create_time"] else "NULL",
paytime=("'"+bill["create_time"]+"'") if bill["create_time"] else "NULL",
payedfee=bill["amount"] / 100,
outtradeno="'"+bill["order_id"]+"'",
paytype="'微信支付'" if bill["pay_type"] == "wechat_miniprogram" else "NULL"
)
cur.execute(lsSql)
iNum = 0
for item in bill["goodses"]:
iNum += 1
dPrice = round(item["amount"] / item["quantity"] / 100, 2)
dAmt = round(item["amount"] / 100, 2)
lsSql = r"insert into ordercomselfapp (orderid, deptid, no, comid, barcode, comname, saleprice, quantity, totalfee, weight, vendorid)" \
r"values ({orderid}, {deptid}, {no}, {comid}, {barcode}, {comname}, {saleprice}, {quantity}, {totalfee}, {weight}, {vendorid})" \
r"".format(
orderid="'"+sBill+"'",
deptid="'"+self.sett.defaultOrgNo+"'",
no=iNum,
comid="'"+item["related"]+"'",
barcode="''",
comname="'"+item["goods_name"]+"'",
saleprice=dPrice,
quantity=item["quantity"],
totalfee=dAmt,
weight="'0'",
vendorid="'2999'"
)
cur.execute(lsSql)
conn.commit()
# 记录最大的【发货开始】时间，以此为下次取数据的七点
if bill["deliver_start_time"] > rtnData["dataString"]:
rtnData["dataString"] = bill["deliver_start_time"]
rtnData["entities"]["order"].append({
"type": "order",
"code": bill["order_id"],
"related": sBill,
"name": "",
"time": bill["create_time"],
"status": 0
})
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
conn.rollback()
# 关闭连接
conn.close()
return rtnData
def getPickupOrder(self, dsOrder):
"""
获取已核销的订单列表
:param dsOrder: 还未核销的订单号列表
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {
"order": []
} # 表体集
}
bContinue = True
if len(dsOrder) == 0:
bContinue = False
# 获取数据库连接
bConn = False
if bContinue:
conn = self.db.GetConnect()
bConn = True
cur = conn.cursor()
if not cur:
rtnData["result"] = False
rtnData["info"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.interName, db=self.interConn["database"])
# 获取新核销的订单列表
if bContinue:
orderlist = ""
for line in dsOrder:
if len(orderlist) > 0:
orderlist += r", '" + line[0] + "'"
else:
orderlist += r"'" + line[0] + "'"
lsSql = r"select orderid, codeid from orderselfapp where status = 'SUCCESS' and orderid in ({orderlist})".format(
orderlist=orderlist)
cur.execute(lsSql)
rtnData["entities"]["order"] = cur.fetchall()
# 关闭连接
if bConn:
conn.close()
return rtnData
def getStock(self):
"""
获取商品库存
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 1, # 数字
"info": "", # 信息
"entities": {} # 表体集
}
bContinue = True
# 获取数据库连接
bConn = False
if bContinue:
conn = self.db.GetConnect()
bConn = True
cur = conn.cursor()
if not cur:
rtnData["result"] = False
rtnData["info"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.interName, db=self.interConn["database"])
# 获取新核销的订单列表
if bContinue:
lsSql = r"select pluOnline.comid, " \
r" plu.comname, " \
r" isnull(sum(stock.quantity), 0) " \
r"from pluOnline " \
r"inner join plu on pluOnline.comid = plu.comid " \
r"left join stock on pluOnline.comid = stock.comid " \
r"where pluOnline.status=1 " \
r"and plu.status in ('A', 'B', 'C', '') " \
r"group by pluOnline.comid, " \
r" plu.comname "
colName = ["comid", "comname", "quantity"]
cur.execute(lsSql)
dsStock = cur.fetchall()
rtnData["entities"]["item"] = []
for line in dsStock:
rtnData["entities"]["item"].append(dict(zip(colName, line)))
# 关闭连接
if bConn:
conn.close()
return rtnData
class BillBoli600(SuperBillDCB):
def __init__(self, sett):
super().__init__(sett)
self.station = [1, 2, 3, 4, 5, 6, 7, 8] # 可用基站
self.db = MSSQL(self.sett.serverHost, self.sett.serverUser,
self.sett.serverPwd, self.sett.serverDb)
def _getStation(self):
"""
获取基站号
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
try:
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
rtnData["info"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
lsSql = "select sys_var_value from sys_t_system where sys_var_id = 'dcb_stationList'"
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) > 0:
staStr = rsData[0][0]
else:
staStr = "[]"
staList = json.loads(staStr)
if len(staList) == 0:
rtnData["info"] = "基站繁忙，请稍后再试"
else:
rtnData["dataNumber"] = staList.pop(0)
lsSql = "update sys_t_system set sys_var_value = '{value}' where sys_var_id = 'dcb_stationList'".format(
value=json.dumps(staList))
cur.execute(lsSql)
conn.commit()
rtnData["result"] = True
except Exception as e:
rtnData["dataNumber"] = 0
rtnData["info"] = str(e)
finally:
conn.close()
return rtnData
def _putStation(self, station):
"""
释放基站号
:param station:
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
try:
conn = self.db.GetConnect()
cur = conn.cursor()
if not cur:
rtnData["info"] = "基础数据获取失败：{name}数据库[{db}]连接失败".format(
name=self.sett.serverName, db=self.sett.serverDb)
else:
lsSql = "select sys_var_value from sys_t_system where sys_var_id = 'dcb_stationList'"
cur.execute(lsSql)
rsData = cur.fetchall()
if len(rsData) > 0:
staStr = rsData[0][0]
staList = json.loads(staStr)
staList.append(station)
staList = list(set(staList))
staList.sort()
lsSql = "update sys_t_system set sys_var_value = '{value}' where sys_var_id = 'dcb_stationList'".format(
value=json.dumps(staList))
cur.execute(lsSql)
conn.commit()
rtnData["result"] = True
else:
rtnData["info"] = "获取基站参数失败"
except Exception as e:
rtnData["info"] = str(e)
finally:
conn.close()
return rtnData
def basicDataRefresh(self):
"""
获取基础资料：请求刷新，才会生成基础资料的文件
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
try:
# 获取基础数据请求写入文件，并通知餐饮服务
if rtnData["result"]:
rtnData = self._socketRequest(11)
# 获取执行结果
if rtnData["result"]:
if rtnData["dataNumber"] == 11:
# 读取数据
rtnData["info"] = "数据刷新成功"
else:
rtnData["result"] = False
rtnData["info"] = "数据返回标志异常"
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 返回执行结果
return rtnData
def basicDataGet(self, sType):
"""
读取基础资料文件
:param sType:
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 文件名
sType = sType.strip()
ldItem = {}
ldKey = {}
if sType == "dishCategory":
ldItem["dishCategory"] = "菜品类别表.TXT"
ldKey["dishCategory"] = ["cLitCls_C", "cLitCls_N"]
elif sType == "dishInfo":
ldItem["dishItem"] = "菜品表.TXT"
ldKey["dishItem"] = [
"cFood_C", "cFood_N", "cLitCls_C", "sNameFast", "sUnit",
"bSuitFood", "eSuitType", "bMultiUnit", "bTimePrc", "nPrc",
"nPrcRoom", "nPrcRoom2", "nPrcRoom3", "nPrcRoom4", "nPrcVip1",
"nPrcVip2", "nPrcVip3", "nFee"
]
ldItem["dishSize"] = ""
ldKey["dishSize"] = [
"cFood_C", "cFoodSize", "nPrc", "nPrcRoom", "nPrcRoom2",
"nPrcRoom3", "nPrcRoom4", "nPrcVIP1", "nPrcVIP2", "nPrcVIP3"
]
elif sType == "dishSuit":
ldItem["FoodSuitItem"] = "菜品套餐内容表.TXT"
ldKey["FoodSuitItem"] = [
"cSuit_C", "cFood_C", "cFood_N", "nPrc", "nQty", "sSelectType",
"sortid", "sunit"
]
ldItem["FoodSuitExchange"] = "菜品套餐内容表.TXT"
ldKey["FoodSuitExchange"] = [
"cSuit_C", "cFood_C", "cFood_N", "cExchange_C", "cExchange_N",
"nPrice", "sunit"
]
elif sType == "dishPrice":
rtnData["result"] = False
rtnData["info"] = "暂不提供菜品特价"
elif sType == "dishReasonReturn":
ldItem["dishReasonReturn"] = "退菜理由表.TXT"
ldKey["dishReasonReturn"] = ["cDict_C", "cDict_N", "cDictRemark"]
elif sType == "madeInfo":
ldItem["MadeCls"] = ""
ldKey["MadeCls"] = ["cMadeCls_C", "cMadeCls_N", "bBillRemark"]
ldItem["Made"] = "客户要求表.TXT"
ldKey["Made"] = [
"cMade_C", "cMade_N", "nExtPrice", "cMadeCls", "bNumPrc"
]
ldItem["FoodMade"] = ""
ldKey["FoodMade"] = ["cFood_C", "cMade_C"]
ldItem["MadeClsFoodCls"] = ""
ldKey["MadeClsFoodCls"] = ["cMadeCls_C", "cCls_C"]
elif sType == "deskInfo":
ldItem["HallFloor"] = ""
ldKey["HallFloor"] = ["cFloor_C", "cFloor_N", "bRoomPrice"]
ldItem["DeskFlie"] = "包房名称表.TXT"
ldKey["DeskFlie"] = [
"cTable_C", "cTable_N", "cFloor_C", "bEnabled", "iSeatNum"
]
elif sType == "operator":
ldItem["operator"] = ""
ldKey["operator"] = ["oper_id", "oper_name", "log_pw"]
elif sType == "waiter":
ldItem["waiter"] = ""
ldKey["waiter"] = ["cEmp_C", "cEmp_N"]
elif sType == "soldOut":
# 估清有单独的接口
ldItem["soldOut"] = ""
ldKey["soldOut"] = ["cFood_C", "cFood_N", "nStock"]
else:
rtnData["result"] = False
rtnData["info"] = "非法的数据类型参数:{sType}".format(sType=sType)
# 读文件
data = {}
if rtnData["result"]:
for item in ldItem:
if len(ldItem[item]) > 0:
data[item] = []
filename = path.join(self.sett.dataPath, ldItem[item])
with open(filename, "r", encoding="ANSI") as f:
for line in f.readlines():
rec = []
line = line.rstrip().encode("GBK")
if len(line) > 0:
if item == "dishCategory":
rec.append(
line[:2].decode("GBK").strip()) # 编码
rec.append(
line[2:].decode("GBK").strip()) # 名称
elif item == "dishItem":
rec.append(
line[:5].decode("GBK").strip()) # 编码
rec.append(
line[7:27].decode("GBK").strip()) # 名称
rec.append(
line[5:7].decode("GBK").strip()) # 类别
rec.append(line[90:100].decode(
"GBK").strip()) # 助记码
rec.append(line[36:40].decode(
"GBK").strip()) # 单位
rec.append(0) # 是否套餐
rec.append("") # 套餐类型
rec.append(0) # 是否多单位
rec.append(0) # 是否时价
rec.append(
float(line[27:36].decode(
"GBK").strip())) # 单价
rec.append(
float(line[27:36].decode(
"GBK").strip())) # 包房价1
rec.append(
float(line[27:36].decode(
"GBK").strip())) # 包房价2
rec.append(1) # 包房是否启用 >0：启用 0：不启用
rec.append(0) # 限售数量 >0：数量 0：不限售
rec.append(
float(line[27:36].decode(
"GBK").strip())) # 会员价1
rec.append(
float(line[27:36].decode(
"GBK").strip())) # 会员价2
rec.append(
float(line[27:36].decode(
"GBK").strip())) # 会员价3
rec.append(0) # 服务费
elif item == "FoodSuitItem":
if line[29:30] == "1":
rec.append(line[:2].decode(
"GBK").strip()) # 套餐号
rec.append(line[2:7].decode(
"GBK").strip()) # 菜品编号
rec.append(line[30:32].decode(
"GBK").strip()) # 菜品名称，用分组号代替
rec.append(
float(line[16:25].decode(
"GBK").strip())) # 价格
rec.append(
float(line[7:16].decode(
"GBK").strip())) # 数量
rec.append("必选项") # 选择类型
rec.append(1) # 排序号
rec.append(line[25:29].decode(
"GBK").strip()) # 单位
elif item == "FoodSuitExchange":
if "FoodSuitItem" in data:
if line[29:30] == "0":
defaultFood = ""
for findsuit in data[
"FoodSuitItem"]:
if findsuit[2] == line[
30:32].decode(
"GBK").strip():
defaultFood = findsuit[1]
break
rec.append(line[:2].decode(
"GBK").strip()) # 套餐号
rec.append(line[2:7].decode(
"GBK").strip()) # 菜品编号
rec.append(line[30:32].decode(
"GBK").strip()) # 菜品名称，用分组号代替
rec.append(defaultFood) # 替换菜品编号
rec.append("") # 替换菜品名称
rec.append(
float(line[16:25].decode(
"GBK").strip())) # 价格
rec.append(line[25:29].decode(
"GBK").strip()) # 单位
elif item == "dishReasonReturn":
rec.append(
line[:2].decode("GBK").strip()) # 编码
rec.append(
line[2:22].decode("GBK").strip()) # 名称
rec.append("") # 备注
elif item == "Made":
rec.append(
line[:3].decode("GBK").strip()) # 编码
rec.append(
line[3:19].decode("GBK").strip()) # 名称
rec.append(0) # 加价
rec.append("") # 类别
rec.append(0) # 是否计数
elif item == "DeskFlie":
rec.append(
line[:4].decode("GBK").strip()) # 编码
rec.append(
line[4:14].decode("GBK").strip()) # 名称
rec.append("") # 楼层
rec.append(1) # 是否有效
rec.append(1) # 座位数
data[item].append(rec)
if sType == "dishInfo":
with open(path.join(self.sett.dataPath, "菜品套餐表.TXT"),
"r",
encoding="ANSI") as f:
for line in f.readlines():
rec = []
line = line.rstrip().encode("GBK")
if len(line) > 0:
rec.append(
line[:2].decode("GBK").strip()) # 编码
rec.append(
line[2:].decode("GBK").strip()) # 名称
rec.append("") # 类别
rec.append("") # 助记码
rec.append("") # 单位
rec.append(1) # 是否套餐
rec.append("可变价") # 价格类型
rec.append(0) # 是否多单位
rec.append(0) # 是否时价
rec.append(-1) # 单价（需要由套餐子项累加）
rec.append(-1) # 包房价1
rec.append(-1) # 包房价2
rec.append(1) # 包房是否启用 >0：启用 0：不启用
rec.append(0) # 限售数量 >0：数量 0：不限售
rec.append(-1) # 会员价1
rec.append(-1) # 会员价2
rec.append(-1) # 会员价3
rec.append(0) # 服务费
data[item].append(rec)
# 数据格式调整
for item in data:
rtnData["entities"][item] = []
for line in data[item]:
rtnData["entities"][item].append(dict(zip(ldKey[item], line)))
# 返回数据
return rtnData
def basicDataSoldout(self, data):
"""
获取估清数据
:param data:{
"terminal":"", # 开台终端号（3位）
"factory":"" # 出厂号（后7/10位）
}
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 获取基站
rtnData = self._getStation()
if rtnData["result"]:
iStation = rtnData["dataNumber"]
try:
# 参数检查
if len(self.sett.softNumber) > 0:
data["terminal"] = self.sett.softNumber
elif "terminal" not in data:
raise Exception("请传入参数：点菜宝编号")
sTerminal = (data["terminal"] + chr(32) * 3)[:3]
if len(self.sett.serialNumber) > 0:
data["factory"] = self.sett.serialNumber
elif "factory" not in data:
raise Exception("请传入参数：点菜宝序列号")
sFactory = ("0" * 10 + data["factory"])
# 生成沽清列表请求数据
if rtnData["result"]:
sCon = []
sCon.append("GQLB" + chr(32) + sTerminal)
sCon.append(sFactory[-7:])
# 获取估清列表请求，并获取反馈
if rtnData["result"]:
rtnData = self._writeBusiData(iStation, sCon)
# 获取执行结果
if rtnData["result"]:
rtnData = self._readRtnData(iStation, "估清列表", sCon, 1, "", 1)
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 释放基站
if "iStation" in locals():
self._putStation(iStation)
# 返回执行结果
return rtnData
def userLogin(self, data):
"""
登录
:param data:{
"terminal":"", # 开台终端号（3位）
"factory":"", # 出厂号（10位）
"user":"", # 工号（4位）
"password":"" # 密码（8位）
}
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 获取基站
rtnData = self._getStation()
if rtnData["result"]:
iStation = rtnData["dataNumber"]
try:
# 参数检查
if len(self.sett.softNumber) > 0:
data["terminal"] = self.sett.softNumber
elif "terminal" not in data:
raise Exception("请传入参数：点菜宝编号")
sTerminal = (data["terminal"] + chr(32) * 3)[:3]
if len(self.sett.serialNumber) > 0:
data["factory"] = self.sett.serialNumber
elif "factory" not in data:
raise Exception("请传入参数：点菜宝序列号")
sFactory = ("0" * 10 + data["factory"])[-10:]
if len(self.sett.loginUser) > 0:
data["user"] = self.sett.loginUser
data["password"] = self.sett.loginPassword
elif "user" not in data:
raise Exception("请传入参数：用户及密码")
sUser = (data["user"] + chr(32) * 5)[:4]
sPassword = (data["password"] + chr(32) * 8)[:8]
# 生成开台请求数据
sCon = []
sCon.append("DL " + chr(32) + sTerminal)
sCon.append(sFactory + chr(32) + sUser + chr(32) + sPassword)
# sCon.append(sUser + chr(32) + sPassword)
# 开台请求写入文件，并通知餐饮服务
if rtnData["result"]:
rtnData = self._writeBusiData(iStation, sCon)
# 获取执行结果
if rtnData["result"]:
rtnData = self._readRtnData(iStation, "登录", sCon, 0, "", 1)
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 释放基站
if "iStation" in locals():
self._putStation(iStation)
# 返回执行结果
return rtnData
def busiTableStatusAll(self, data):
"""
查询空闲桌台
:param data:{
"terminal":"", # 开台终端号（3位）
"factory":"" # 出厂号（后7/10位）
}
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 获取基站
rtnData = self._getStation()
if rtnData["result"]:
iStation = rtnData["dataNumber"]
try:
# 参数检查
if len(self.sett.softNumber) > 0:
data["terminal"] = self.sett.softNumber
elif "terminal" not in data:
raise Exception("请传入参数：点菜宝编号")
sTerminal = (data["terminal"] + chr(32) * 3)[:3]
if len(self.sett.serialNumber) > 0:
data["factory"] = self.sett.serialNumber
elif "factory" not in data:
raise Exception("请传入参数：点菜宝序列号")
sFactory = ("0" * 10 + data["factory"])
# 生成开台请求数据
if rtnData["result"]:
sCon = []
sCon.append("KXHZ" + chr(32) + sTerminal)
sCon.append(sFactory[-7:])
# 获取估清列表请求，并获取反馈
if rtnData["result"]:
rtnData = self._writeBusiData(iStation, sCon)
# 获取执行结果
if rtnData["result"]:
rtnData = self._readRtnData(iStation, "空闲桌台汇总", sCon, 1, "", 1)
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 释放基站
if "iStation" in locals():
self._putStation(iStation)
# 返回执行结果
return rtnData
def busiTableStatusSingle(self, data):
"""
查询空闲桌台
:param data:{
"terminal":"", # 开台终端号（3位）
"table":"", # 桌台号（4位）
"factory":"" # 出厂号（后7/10位）
}
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 获取基站
rtnData = self._getStation()
if rtnData["result"]:
iStation = rtnData["dataNumber"]
try:
# 参数检查
if len(self.sett.softNumber) > 0:
data["terminal"] = self.sett.softNumber
elif "terminal" not in data:
raise Exception("请传入参数：点菜宝编号")
sTerminal = (data["terminal"] + chr(32) * 3)[:3]
if "table" in data:
sTable = (data["table"] + chr(32) * 4)[:4]
else:
raise Exception("请传入桌台号")
if len(self.sett.serialNumber) > 0:
data["factory"] = self.sett.serialNumber
elif "factory" not in data:
raise Exception("请传入参数：点菜宝序列号")
sFactory = ("0" * 10 + data["factory"])
# 生成开台请求数据
if rtnData["result"]:
sCon = []
sCon.append("KXHZ" + chr(32) + sTerminal)
sCon.append(sTable + chr(32) + sFactory[-7:])
# 获取估清列表请求，并获取反馈
if rtnData["result"]:
rtnData = self._writeBusiData(iStation, sCon)
# 获取执行结果
if rtnData["result"]:
rtnData = self._readRtnData(iStation, "查桌台状态", sCon, 1, "", 1)
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 释放基站
if "iStation" in locals():
self._putStation(iStation)
# 返回执行结果
return rtnData
def billOpen(self, data):
"""
开台
:param data:{
"terminal":"", # 开台终端号（3位）
"table":"", # 桌台号（4位）
"waiter":"", # 服务员号（5位）
"guestNum":0, # 客人数量（2位）
"factory":"" # 出厂号（后7/10位）
}
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 获取基站
rtnData = self._getStation()
if rtnData["result"]:
iStation = rtnData["dataNumber"]
try:
# 参数检查
if len(self.sett.softNumber) > 0:
data["terminal"] = self.sett.softNumber
elif "terminal" not in data:
raise Exception("请传入参数：点菜宝编号")
sTerminal = (data["terminal"] + chr(32) * 3)[:3]
if "table" in data:
sTable = (data["table"] + chr(32) * 4)[:4]
else:
rtnData["result"] = False
rtnData["info"] = "请传入桌台号"
if "waiter" in data:
sWaiter = (data["waiter"] + chr(32) * 5)[:5]
else:
sWaiter = chr(32) * 5
if "guestNum" in data:
sGuestNum = ("0" + str(int(data["guestNum"])))[-2:]
else:
sGuestNum = "01"
if len(self.sett.serialNumber) > 0:
data["factory"] = self.sett.serialNumber
elif "factory" not in data:
raise Exception("请传入参数：点菜宝序列号")
sFactory = ("0" * 10 + data["factory"])
# 生成开台请求数据
if rtnData["result"]:
sCon = []
sCon.append("KT " + chr(32) + sTerminal)
sCon.append(sTable + chr(32) + sGuestNum + chr(32) + sWaiter +
chr(32) + sFactory[-7:] + chr(32) +
time.strftime("%H:%M:%S"))
# 开台请求，并获取反馈
if rtnData["result"]:
rtnData = self._writeBusiData(iStation, sCon)
# 获取执行结果
if rtnData["result"]:
rtnData = self._readRtnData(iStation, "开台", sCon, 1, "开台成功", 1)
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 释放基站
if "iStation" in locals():
self._putStation(iStation)
# 返回执行结果
return rtnData
def billPut(self, data):
"""
点菜
:param data:{
"terminal":"", # 开台终端号（3位）
"table":"", # 桌台号+账单流水号（4+3=7位）
"factory":"", # 出厂号（4+4+2=10位）
"remark":"", # 整单备注（12位）
"item":[{
"food":"", # 菜品号（5位）
"qty":1, # 数量（4位）
"made":"", # 做法（12位）
"suit":"", # 套餐号（2位）
"waitUp":0 # 等叫标志（1位）
}]
}
:return:
"""
rtnData = {
"result": False, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 获取基站
rtnData = self._getStation()
if rtnData["result"]:
iStation = rtnData["dataNumber"]
try:
# 参数检查
if len(self.sett.softNumber) > 0:
data["terminal"] = self.sett.softNumber
elif "terminal" not in data:
raise Exception("请传入参数：点菜宝编号")
sTerminal = (data["terminal"] + chr(32) * 3)[:3]
if "table" in data:
sTable = (data["table"] + chr(32) * 7)[:7]
else:
rtnData["result"] = False
rtnData["info"] = "请传入桌台号"
if len(self.sett.serialNumber) > 0:
data["factory"] = self.sett.serialNumber
elif "factory" not in data:
raise Exception("请传入参数：点菜宝序列号")
sFactory = ("0" * 10 + data["factory"])
if "remark" in data:
sRemark = (data["remark"] + chr(32) * 12)[:12]
else:
sRemark = chr(32) * 12
sFlow = time.strftime("%H:%M:%S")
# 生成开台请求数据
if rtnData["result"]:
sCon = []
sCon.append("DC " + chr(32) + sTerminal)
sCon.append(sTable + chr(32) + sFactory[:4] + chr(32) +
chr(32) * 6 + sRemark + chr(32) + chr(32) * 4 +
sFlow + chr(32) + sFactory[4:8] + chr(32) +
sFactory[8:10])
for line in data["item"]:
sFood = (line["food"] + chr(32) * 5)[:5]
sQty = (chr(32) * 4 + str(line["qty"]))[-4:]
if "made" in line:
sMade = (line["made"] + chr(32) * 12)[:12]
else:
sMade = chr(32) * 12
if "suit" in line:
suit = (line["suit"] + chr(32) * 2)[:2]
else:
suit = chr(32) * 2
if "waitUp" in line:
waitUp = (str(line["waitUp"]) + "0")[-1:]
else:
waitUp = "0"
sCon.append(sTable + chr(32) + sFood + chr(32) + sQty +
chr(32) + sMade + chr(32) + suit + chr(32) +
waitUp)
# 开台请求写入文件，并通知餐饮服务
if rtnData["result"]:
rtnData = self._writeBusiData(iStation, sCon)
# 获取执行结果
if rtnData["result"]:
rtnData = self._readRtnData(iStation, "点菜", sCon, 1, "点菜成功", 1)
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 释放基站
if "iStation" in locals():
self._putStation(iStation)
# 返回执行结果
return rtnData
def billGet(self, data):
"""
账单查询
:param data:{
"terminal":"", # 开台终端号（3位）
"table":"", # 桌台号（4位）
"factory":"" # 出厂号（后7/10位）
}
:return:
"""
rtnData = {
"result": True, # 逻辑控制 True/False
"dataString": "", # 字符串
"dataNumber": 0, # 数字
"info": "", # 信息
"entities": {}
}
# 获取基站
rtnData = self._getStation()
if rtnData["result"]:
iStation = rtnData["dataNumber"]
try:
# 参数检查
if len(self.sett.softNumber) > 0:
data["terminal"] = self.sett.softNumber
elif "terminal" not in data:
raise Exception("请传入参数：点菜宝编号")
sTerminal = (data["terminal"] + chr(32) * 3)[:3]
if "table" not in data:
raise Exception("请传入桌台号")
sTable = (data["table"] + chr(32) * 4)[:4]
if len(self.sett.serialNumber) > 0:
data["factory"] = self.sett.serialNumber
elif "factory" not in data:
raise Exception("请传入参数：点菜宝序列号")
sFactory = ("0" * 10 + data["factory"])
# 生成账单查询请求数据
if rtnData["result"]:
sCon = []
sCon.append("ZDCX" + chr(32) + sTerminal)
sCon.append(chr(32) * 3 + sTable + chr(32) + sFactory[-7:])
# 获取账单查询请求，并获取反馈
if rtnData["result"]:
rtnData = self._writeBusiData(iStation, sCon)
# 获取执行结果
if rtnData["result"]:
rtnData = self._readRtnData(iStation, "账单查询", sCon, 1, "", 1)
except Exception as e:
rtnData["result"] = False
rtnData["info"] = str(e)
finally:
# 释放基站
if "iStation" in locals():
self._putStation(iStation)
# 返回执行结果
return rtnData