class JPPub(QObject):
MainForm = None
UserSaveData = pyqtSignal(str)
SingnalPopMessage = pyqtSignal(str)
def __init__(self):
if self.MainForm is None:
super().__init__()
self.user = JPUser()
self.db = JPDb()
self.__ConfigData = None
self.INITCustomer()
self.INITSupplier()
self.INITEnum()
sql = """
SELECT fNMID, fMenuText, fParentId, fCommand, fObjectName, fIcon,
cast(fIsCommandButton AS SIGNED) AS fIsCommandButton
FROM sysnavigationmenus
WHERE fEnabled=1 AND fNMID>1
ORDER BY fDispIndex
"""
self.__sysNavigationMenusDict = self.db.getDict(sql)
def INITEnum(self):
def getEnumDict() -> dict:
sql = '''select fTypeID,fTitle,fItemID,fSpare1,
fSpare2,fNote from t_enumeration'''
rows = self.db.getDataList(sql)
return {
k: [row1[1:] for row1 in rows if row1[0] == k]
for k in set(row[0] for row in rows)
}
self.__EnumDict = getEnumDict()
def INITCustomer(self):
sql = '''select fCustomerName,fCustomerID,
fNUIT,fCity,fContato,fTaxRegCer from t_customer'''
self.__allCustomerList = self.db.getDataList(sql)
def INITSupplier(self):
sql = '''select fSupplierName,fSupplierID,
fNUIT,fCity,fContato,fTaxRegCer from t_supplier'''
self.__allSupplieList = self.db.getDataList(sql)
def getEnumList(self, enum_type_id: int):
self.INITEnum()
return self.__EnumDict[enum_type_id]
def getSupplierList(self):
self.INITSupplier()
return self.__allSupplierList
def getCustomerList(self):
self.INITCustomer()
return self.__allCustomerList
def getSysNavigationMenusDict(self):
return self.__sysNavigationMenusDict
def getConfigData(self) -> dict:
sql = "select fValue from sysconfig where fName='configValue'"
conn = JPDb().currentConn
cur = conn.cursor()
cur.execute(sql)
conn.commit()
mydic = loads(b64decode(cur._result.rows[0][0]))
# 防止没有设置信息时，给定初始值
# copys
copys = [
'BillCopys_Order', 'BillCopys_PrintingOrder',
'BillCopys_OutboundOrder', 'BillCopys_WarehouseRreceipt',
'BillCopys_QuotationOrder', 'BillCopys_QuotationPrintingOrder'
]
for item in copys:
if item not in mydic.keys():
mydic[item] = 'atendimento;1;producao;0;cliente;1;caixa;1'
if 'TaxRegCerPath' not in mydic.keys():
mydic['TaxRegCerPath'] = ''
txt = '您还没有设置客户税务登记证件位置\n'
txt = txt + 'You haven\'t set up the location of the customer\'s tax registration certificate yet'
QMessageBox.information(self.MainForm, '提示', txt)
else:
if not ospath.exists(mydic['TaxRegCerPath']):
txt = '您设置的客户税务登记证件位置不存在\n'
txt = txt + 'The location of the customer tax registration certificate you set does not exist'
QMessageBox.information(self.MainForm, '提示', txt)
return mydic
def getCopysInfo(self, billname: str):
try:
info_str = self.getConfigData()[billname]
info_lst = info_str.split(';')
mydic = []
for i in range(len(info_lst)):
if i % 2 == 0:
mydic.append({
'title':
info_lst[i],
'flag':
True if info_lst[i + 1] == '1' else False
})
return mydic
except Exception as e:
msg = '读取单据联次信息出现错误，信息如下:\n'
msg = msg + str(e)
QMessageBox.warning(self, '提示', msg, QMessageBox.Yes,
QMessageBox.Yes)
def saveConfigData(self, data: dict):
sql = "update sysconfig set fValue=%s where fName='configValue'"
conn = JPDb().currentConn
cur = conn.cursor()
cur.execute(sql, b64encode(dumps(data)))
conn.commit()
# 保存后刷新当前配置文件
self.__ConfigData = data
def ConfigData(self, RefResh=False):
if RefResh or self.__ConfigData is None:
self.__ConfigData = self.getConfigData()
return self.__ConfigData
def broadcastMessage(self, tablename=None, action=None, PK=None):
s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
s.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
PORT = 1060
network = '<broadcast>'
act = {
'confirmation': '确认',
'Submit': '提交',
'edit': '修改',
'new': '新增',
'delete': '删除',
'createOrder': '由报价单生成新订单'
}
tn = {
't_order': '订单表',
'sysusers': '用户表',
't_receivables': '收款表',
't_customer': '客户表',
't_quotation': '报价单表',
't_product_information': '产品信息表',
't_product_outbound_order': '出库单表',
't_product_warehousereceipt_order': '入库单表',
't_supplier': '供应商表'
}
curtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
curtab = tn[tablename]
curact = act[action]
curpk = PK
obj_user = JPUser()
uid = obj_user.currentUserID()
curuser = [v[1] for v in obj_user.getAllUserList() if v[0] == uid][0]
txt = f'{curtime}:\n用户[{curuser}]操作;\n[{curtab}]有新的记录被用户[{curact}],\n编号为[{curpk}]'
msg = json.dumps((tablename, txt))
s.sendto(msg.encode('utf-8'), (network, PORT))
s.close()
def receiveMessage(self, app):
# 收发消息类
class MyThreadRec(QThread):
def __init__(self, pub, notify, rec, *args, **kwargs):
super().__init__(*args, **kwargs)
self.rec = rec
self.pub = pub
self.notify = notify
self.currentIP = None
def run(self):
while True:
data, address = self.rec.recvfrom(65535)
txt = json.loads(data.decode('utf-8'))
# 发送信息方是本机时不处理
if address[0] != self.currentIP:
if self.pub.ConfigData()['AutoRefreshWhenDataChange']:
self.pub.UserSaveData.emit(txt[0])
if self.pub.ConfigData()['BubbleTipsWhenDataChange']:
self.pub.SingnalPopMessage.emit(txt[1])
time.sleep(1)
self.rec = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
self.rec.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
PORT = 1060
self.rec.bind(('', PORT))
self.rec.setblocking(True)
self.notify = WindowNotify(app=app)
self.ThreadRec = MyThreadRec(self, self.notify, self.rec)
myname = socket.getfqdn(socket.gethostname())
self.ThreadRec.currentIP = socket.gethostbyname(myname)
self.SingnalPopMessage.connect(self.popMessage)
self.ThreadRec.start()
def popMessage(self, txt):
self.notify.show(content=txt)
self.notify.showAnimation()
class JPPub(QObject):
MainForm = None
UserSaveData = pyqtSignal(str)
SingnalPopMessage = pyqtSignal(str)
def __init__(self):
if self.MainForm is None:
super().__init__()
self.user = JPUser()
self.db = JPDb()
self.__ConfigData = None
sql = ('SELECT fNMID, fMenuText, fParentId, '
'fCommand, fObjectName, fIcon,'
'cast(fIsCommandButton AS SIGNED) AS fIsCommandButton '
'FROM sysnavigationmenus '
'WHERE fEnabled=1 AND fNMID>1 '
'ORDER BY fDispIndex')
self.__sysNavigationMenusDict = self.db.getDict(sql)
@lazy_Property
def _getEnumDict(self) -> dict:
sql = ('select fTypeID,fTitle,fItemID,fSpare1,'
'fSpare2,fNote from t_enumeration')
rows = self.db.getDataList(sql)
return {
k: [row1[1:] for row1 in rows if row1[0] == k]
for k in set(row[0] for row in rows)
}
def getEnumList(self, enum_type_id: int):
return self._getEnumDict[enum_type_id]
def getSysNavigationMenusDict(self):
return self.__sysNavigationMenusDict
def getConfigData(self) -> dict:
sql = "select fValue from sysconfig where fName='configValue'"
conn = JPDb().currentConn
cur = conn.cursor()
cur.execute(sql)
conn.commit()
mydic = loads(b64decode(cur._result.rows[0][0]))
# 防止没有设置信息时，给定初始值
# copys
copys = [
'BillCopys_Order', 'BillCopys_PrintingOrder',
'BillCopys_OutboundOrder', 'BillCopys_WarehouseRreceipt',
'BillCopys_QuotationOrder', 'BillCopys_QuotationPrintingOrder'
]
for item in copys:
if item not in mydic.keys():
mydic[item] = 'atendimento;1;producao;0;cliente;1;caixa;1'
if 'archives_path' not in mydic.keys():
mydic['archives_path'] = ''
txt = '您还没有设置附件文件存放位置'
QMessageBox.information(self.MainForm, '提示', txt)
else:
if not ospath.exists(mydic['archives_path']):
txt = '您设置的设置附件文件存放位置不存在'
QMessageBox.information(self.MainForm, '提示', txt)
return mydic
def getCopysInfo(self, billname: str):
try:
info_str = self.getConfigData()[billname]
info_lst = info_str.split(';')
mydic = []
for i in range(len(info_lst)):
if i % 2 == 0:
mydic.append({
'title':
info_lst[i],
'flag':
True if info_lst[i + 1] == '1' else False
})
return mydic
except Exception as e:
msg = '读取单据联次信息出现错误，信息如下:\n'
msg = msg + str(e)
QMessageBox.warning(self, '提示', msg, QMessageBox.Yes,
QMessageBox.Yes)
def saveConfigData(self, data: dict):
sql = "update sysconfig set fValue=%s where fName='configValue'"
conn = JPDb().currentConn
cur = conn.cursor()
cur.execute(sql, b64encode(dumps(data)))
conn.commit()
# 保存后刷新当前配置文件
self.__ConfigData = data
def ConfigData(self, RefResh=False):
if RefResh or self.__ConfigData is None:
self.__ConfigData = self.getConfigData()
return self.__ConfigData
def broadcastMessage(self, tablename=None, action=None, PK=None):
s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
s.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
PORT = 1060
network = '<broadcast>'
act = {
'confirmation': '确认',
'Submit': '提交',
'edit': '修改',
'new': '新增',
'delete': '删除',
'createOrder': '由报价单生成新订单'
}
tn = {
't_order': '订单表',
'sysusers': '用户表',
't_receivables': '收款表',
't_customer': '客户表',
't_quotation': '报价单表',
't_product_information': '产品信息表',
't_product_outbound_order': '出库单表',
't_product_warehousereceipt_order': '入库单表',
't_supplier': '供应商表'
}
curtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
curtab = tn[tablename]
curact = act[action]
curpk = PK
obj_user = JPUser()
uid = obj_user.currentUserID()
curuser = [v[1] for v in obj_user.getAllUserList() if v[0] == uid][0]
txt = ('{curtime}:\n用户[{curuser}]操作;\n'
'[{curtab}]有新的记录被用户[{curact}],'
'\n编号为[{curpk}]')
txt = txt.format(curtime=curtime,
curuser=curuser,
curtab=curtab,
curpk=curp,
curact=curact)
msg = json.dumps((tablename, txt))
s.sendto(msg.encode('utf-8'), (network, PORT))
s.close()
def receiveMessage(self, app):
# 收发消息类
class MyThreadRec(QThread):
def __init__(self, pub, notify, rec, *args, **kwargs):
super().__init__(*args, **kwargs)
self.rec = rec
self.pub = pub
self.notify = notify
self.currentIP = None
def run(self):
while True:
data, address = self.rec.recvfrom(65535)
txt = json.loads(data.decode('utf-8'))
# 发送信息方是本机时不处理
if address[0] != self.currentIP:
if self.pub.ConfigData()['AutoRefreshWhenDataChange']:
self.pub.UserSaveData.emit(txt[0])
if self.pub.ConfigData()['BubbleTipsWhenDataChange']:
self.pub.SingnalPopMessage.emit(txt[1])
time.sleep(1)
self.rec = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
self.rec.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
PORT = 1060
self.rec.bind(('', PORT))
self.rec.setblocking(True)
self.notify = WindowNotify(app=app)
self.ThreadRec = MyThreadRec(self, self.notify, self.rec)
myname = socket.getfqdn(socket.gethostname())
self.ThreadRec.currentIP = socket.gethostbyname(myname)
self.SingnalPopMessage.connect(self.popMessage)
self.ThreadRec.start()
def popMessage(self, txt):
self.notify.show(content=txt)
self.notify.showAnimation()
def getOrSetlastOpenDir(self, path: str = None):
"""返回或设置最后打开文件夹，如果path给一个文件名或路径名，则设置，不给值则返回一个值"""
subKey = r'Software\project_m'
key = winreg.HKEY_CURRENT_USER
try:
newkey = winreg.OpenKey(key, subKey)
except WindowsError:
newkey = winreg.CreateKey(key, subKey)
winreg.SetValue(newkey, "recentPath", winreg.REG_SZ, getcwd())
if not path:
return winreg.QueryValue(key, subKey + r'\recentPath')
else:
if ospath.isfile(path):
path = ospath.dirname(path)
winreg.SetValue(newkey, "recentPath", winreg.REG_SZ, path)
def __checkFileExist(self, path):
return path
def getIcoPath(self, fileName: str):
p = getcwd() + "\\res\\ico\\{}".format(fileName)
return self.__checkFileExist(p)
def getLogoPath(self, fileName: str):
p = getcwd() + "\\res\\{}".format(fileName)
return self.__checkFileExist(p)
def reFreshAllUser(self):
db = JPDb()
sql = """select fUserID,fUsername from sysusers where fUserID=1 or fEnabled=1"""
self.__AllUser = db.getDataList(sql)
def __init__(self, mainform):
super().__init__()
self.ui = Ui_Form()
self.ui.setupUi(self)
self.MainForm = mainform
mainform.addForm(self)
mainform.addOneButtonIcon(self.ui.CmdPrint, 'print.png')
mainform.addOneButtonIcon(self.ui.CmdPDF, 'pdf.png')
mainform.addOneButtonIcon(self.ui.CmdExportToExcel,
'exportToexcel.png')
year_sql = """
select year(fOrderDate) as y
from t_order union select year(fReceiptDate)
as y from t_receivables
"""
self.sql_none = """
SELECT Null AS `Day\Month`,
Null as M1, Null as M2, Null as M3, Null as M4,
Null as M5, Null as M6, Null as M7, Null as M8,
Null as M9, Null as M10, Null as M11, Null as M12
from t_order {}
"""
sql_receivables = """
SELECT IF(ISNULL(Q3.d), 'Sum', Q3.d) AS `Day\Month`
, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10
, M11, M12
FROM (
SELECT Q1.d
, sum(IF(Q1.m = 1, Q1.j1, NULL)) AS M1
, sum(IF(Q1.m = 2, Q1.j1, NULL)) AS M2
, sum(IF(Q1.m = 3, Q1.j1, NULL)) AS M3
, sum(IF(Q1.m = 4, Q1.j1, NULL)) AS M4
, sum(IF(Q1.m = 5, Q1.j1, NULL)) AS M5
, sum(IF(Q1.m = 6, Q1.j1, NULL)) AS M6
, sum(IF(Q1.m = 7, Q1.j1, NULL)) AS M7
, sum(IF(Q1.m = 8, Q1.j1, NULL)) AS M8
, sum(IF(Q1.m = 9, Q1.j1, NULL)) AS M9
, sum(IF(Q1.m = 10, Q1.j1, NULL)) AS M10
, sum(IF(Q1.m = 11, Q1.j1, NULL)) AS M11
, sum(IF(Q1.m = 12, Q1.j1, NULL)) AS M12
FROM (
SELECT MONTH(fReceiptDate) AS m, DAY(fReceiptDate) AS d
, SUM(fAmountCollected) AS j1
FROM t_receivables
WHERE YEAR(fReceiptDate) = {}
GROUP BY MONTH(fReceiptDate), DAY(fReceiptDate)
) Q1
GROUP BY Q1.d WITH ROLLUP
) Q3
"""
sql_payment = """
SELECT if(isnull(Q3.d), 'Sum', Q3.d) AS `Day\Month`
, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10
, M11, M12
FROM (
SELECT Q1.d
, sum(IF(Q1.m = 1, Q1.j1, NULL)) AS M1
, sum(IF(Q1.m = 2, Q1.j1, NULL)) AS M2
, sum(IF(Q1.m = 3, Q1.j1, NULL)) AS M3
, sum(IF(Q1.m = 4, Q1.j1, NULL)) AS M4
, sum(IF(Q1.m = 5, Q1.j1, NULL)) AS M5
, sum(IF(Q1.m = 6, Q1.j1, NULL)) AS M6
, sum(IF(Q1.m = 7, Q1.j1, NULL)) AS M7
, sum(IF(Q1.m = 8, Q1.j1, NULL)) AS M8
, sum(IF(Q1.m = 9, Q1.j1, NULL)) AS M9
, sum(IF(Q1.m = 10, Q1.j1, NULL)) AS M10
, sum(IF(Q1.m = 11, Q1.j1, NULL)) AS M11
, sum(IF(Q1.m = 12, Q1.j1, NULL)) AS M12
FROM (
SELECT MONTH(fOrderDate) AS m, DAY(fOrderDate) AS d
, SUM(fPayable) AS j1
FROM t_order
WHERE (Year(fOrderDate) = {}
AND fCanceled = 0
AND fSubmited = 1
AND fConfirmed = 1)
GROUP BY MONTH(fOrderDate), DAY(fOrderDate)
) Q1
GROUP BY Q1.d WITH ROLLUP
) Q3 """
self.cbo_base = self.ui.cbo_base
self.cbo_year = self.ui.cbo_year
self.tableView = self.ui.tableView
self.cbo_base.addItem('Payment', sql_payment)
self.cbo_base.addItem('Receivables', sql_receivables)
db = JPDb()
year_list = db.getDataList(year_sql)
year_list = [str(y[0]) for y in year_list if y[0]]
for y in year_list:
self.cbo_year.addItem(y)
cur_year = str(QDate.currentDate().year())
if cur_year in year_list:
self.cbo_year.setCurrentText(cur_year)
else:
self.cbo_year.setCurrentIndex(-1)
self.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
self.cbo_base.currentTextChanged.connect(self._search)
self.cbo_year.currentTextChanged.connect(self._search)
#self.butPrint.clicked.connect(self.onbutPrint)
self._search()
