def __init__(self, mainform):
super().__init__()
self.CurrentTypeID = None
ui = Ui_Form_list()
ui.setupUi(self)
sql1 = """SELECT fTypeID AS 'TypeID 类别ID',
fTypeName AS 'TypeName 名称',
fNote AS 'Note 说明'
FROM t_enumeration_type
ORDER BY fTypeID
"""
self.ui = ui
mainform.addOneButtonIcon(ui.butNew, "new.png")
self.tab1 = ui.tabelViewType
self.tab2 = ui.tabelViewEnum
self.mainform = mainform
self.tab1.setSelectionMode(QAbstractItemView.SingleSelection)
self.tab2.setSelectionMode(QAbstractItemView.SingleSelection)
self.tab1.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tab2.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tabinfo1 = JPQueryFieldInfo(sql1)
self.mod1 = _myReadOnlyMod(self.tab1, self.tabinfo1)
self.tab1.setModel(self.mod1)
self.tab1.resizeColumnsToContents()
mainform.addForm(self)
self.tab1.selectionModel().currentRowChanged.connect(
self.type_selected)
self.setTab2Column()
self.refreshTabEnum()
self.ui.butNew.clicked.connect(self.but_New)
def setNumberNeedControl(self, arg=None):
obj_begin = self.ui.fNumerBegin
obj_end = self.ui.fNumerEnd
self.NumberControl = True if arg else False
if not arg:
obj_begin.refreshValueNotRaiseEvent(None, True)
obj_end.refreshValueNotRaiseEvent(None, True)
sql = self.sql_base + JPDb().getOnlyStrcFilter()
tab = JPQueryFieldInfo(sql)
mod = myHistoryView(self.ui.listPrintingOrder, tab)
self.ui.listPrintingOrder.setModel(mod)
self.ui.listPrintingOrder.resizeColumnsToContents()
obj_begin.setEnabled(False)
self.ui.listPrintingOrder.setEnabled(False)
else:
tab = JPQueryFieldInfo(arg)
mod = myHistoryView(self.ui.listPrintingOrder, tab)
self.ui.listPrintingOrder.setModel(mod)
self.ui.listPrintingOrder.resizeColumnsToContents()
self.ui.listPrintingOrder.setEnabled(
len(tab) > 0 and self.isEditMode)
beginNum = tab.getOnlyData([0, 4]) if len(tab.DataRows) > 0 else 0
beginNum += 1
# 编辑状态时，不更新起始值，只修改编辑状态
# 编辑和新增加状态时，设定起始、结束值的验证器
if self.EditMode == JPEditFormDataMode.New:
obj_begin.refreshValueNotRaiseEvent(beginNum, True)
obj_begin.setIntValidator(beginNum, 999999999999)
obj_begin.setEnabled(True)
def __init__(self, parent=None):
super().__init__(parent=parent)
self.SQL = """
SELECT fMenuText, fEnabled, fDefault, fIcon, fNodeBackvolor
, fNodeForeColor, fNodeFontBold, fExpanded, fNMID, fDispIndex
, fParentId, fIsCommandButton, fCommand, fObjectName, fFormMode
, fArg, fDescription, fLevel
FROM sysnavigationmenus
ORDER BY fDispIndex
"""
self.tableInfo = JPQueryFieldInfo(self.SQL)
def on_CmdDelete_clicked(self):
pid = self.getCurrentSelectPKValue()
if pid is None:
return
sql0 = f"""
select fProductID from
t_product_outbound_order_detail
where fProductID={pid} Limit 1
union all
select fProductID
from t_product_warehousereceipt_order_detail
where fProductID={pid} Limit 1 """
tab = JPQueryFieldInfo(sql0)
if len(tab):
txt = '该产品已经存在订单，无法删除!\n'
txt = txt + "The product already has an order and can not delete it!"
QMessageBox.warning(self, '提示', txt, QMessageBox.Cancel,
QMessageBox.Cancel)
return
del_txt = '确认要删除此产品？\n'
del_txt = del_txt + 'Are you sure you want to delete this product?'
sql = f"DELETE FROM t_product_information WHERE fID = {pid}"
if QMessageBox.question(self, '提示', del_txt,
(QMessageBox.Yes | QMessageBox.No),
QMessageBox.Yes) == QMessageBox.Yes:
JPDb().executeTransaction(sql)
self.refreshTable()
def on_CmdDelete_clicked(self):
uid = self.getCurrentSelectPKValue()
if uid is None:
return
sql0 = """
SELECT fCustomerID
FROM (
SELECT fCustomerID
FROM v_order
UNION ALL
SELECT fCustomerID
FROM v_quotation
) Q
WHERE Q.fCustomerID = {}
LIMIT 1"""
tab = JPQueryFieldInfo(sql0.format(uid))
if len(tab):
txt = '该客户已经存在订单，无法删除!\n'
txt = txt + "The customer already has an order and can not delete it!"
QMessageBox.warning(self, '提示', txt, QMessageBox.Cancel,
QMessageBox.Cancel)
return
del_txt = '确认要删除此客户？\n'
del_txt = del_txt + 'Are you sure you want to delete this customer?'
sql = "DELETE FROM t_customer WHERE fCustomerID = {}"
if QMessageBox.question(self, '提示', del_txt,
(QMessageBox.Yes | QMessageBox.No),
QMessageBox.Yes) == QMessageBox.Yes:
JPDb().executeTransaction(sql.format(uid))
self.refreshTable()
def __getCr(self, d, n, isView=False):
if isView:
sql = 'SHOW CREATE VIEW `{}`.`{}`'.format(d, n)
else:
sql = 'SHOW CREATE TABLE `{}`.`{}`'.format(d, n)
tab = JPQueryFieldInfo(sql)
return tab.DataRows[0].Datas[1] + '\n'
def __readSubData(self):
tv = self.ui.tableView
em = self.EditMode
JFDM = JPEditFormDataMode
if em is None:
raise ValueError("没有指定子窗体的编辑模式！")
# 建立子窗体模型
ModelClass = self.onGetModelClass()
if self.isNewMode:
tfi = JPTabelFieldInfo(self.subSQL, False)
if len(tfi.DeleteRows) == 0:
tfi.addRow()
if self.isReadOnlyMode:
tfi = JPQueryFieldInfo(self.subSQL.format(self.PKValue))
if self.isEditMode:
tfi = JPTabelFieldInfo(self.subSQL.format(self.PKValue))
tfi.addRow()
self.subModel = ModelClass(tv, tfi)
if self.isNewMode or self.isEditMode:
self.subModel.firstHasDirty.connect(self.onFirstHasDirty)
# 检查第一列是不是子表主键
if tfi.Fields[0].IsPrimarykey is False:
errtxt = "setSQL()方法第二个参数中Sql语句，第一列必须是主键字段。\n{}"
errtxt = errtxt.format(self.subSQL)
QMessageBox.warning(self, "错误", errtxt)
self.subTableFieldsInfo = tfi
smd = self.subModel
tv.setModel(smd)
smd.dataChanged.connect(self._emitDataChange)
# 设置子窗体可编辑状态
self.setEditState(em != JFDM.ReadOnly)
# 设置子窗体的输入委托控件及格式等
self.__readOnlyColumns = self.onGetReadOnlyColumns()
smd.setColumnsDetegate()
for col in self.__readOnlyColumns:
tv.setItemDelegateForColumn(col, myDe.JPDelegate_ReadOnly(tv))
self.__hideColumns = self.onGetHiddenColumns()
# 设置隐藏列，第0列不隐藏（将显示删除按钮）
for col in [c for c in self.__hideColumns if c > 0]:
tv.setColumnHidden(col, True)
self.__columnWidths = self.onGetColumnWidths()
for i, w in enumerate(self.__columnWidths):
tv.setColumnWidth(i, w)
self.__columnsRowSources = self.onGetColumnRowSources()
for field_key, data, bind_col in self.__columnsRowSources:
smd.TabelFieldInfo.Fields[field_key].BindingColumn = bind_col
smd.TabelFieldInfo.setFieldsRowSource(field_key, data)
# 设置字段计算公式
for i, f in self.onGetColumnFormulas():
smd.TabelFieldInfo.Fields[i].Formula = f
temp = self.afterSetDataBeforeInsterRowEvent
smd.afterSetDataBeforeInsterRowEvent = temp
temp1 = self.afterInsterRowEvent
smd.afterInsterRowEvent = temp1
self.subModel.deleteRowDelegate = MyButtonDelegate(tv)
tv.setItemDelegateForColumn(0, self.subModel.deleteRowDelegate)
def refreshOrder(self):
sql = """
SELECT *
FROM (SELECT forderid AS 订单号码OrderID,
forderdate AS 日期OrderDate,
fprice AS '单价P. Unitario',
fquant AS '数量Qtd',
famount AS 金额SubTotal,
fdesconto AS 折扣Desconto,
ftax AS 税金IVA,
fpayable AS `应付金额Valor a Pagar`
FROM v_all_sales
WHERE fcustomerid = {uid}
UNION ALL
SELECT '' AS 订单号码OrderID,
'合计Sum' AS 日期OrderDate,
'' AS '单价P. Unitario',
'' AS '数量Qtd',
Sum(famount) AS 金额SubTotal,
Sum(fdesconto) AS 折扣Desconto,
Sum(ftax) AS 税金IVA,
Sum(fpayable) AS `应付金额Valor a Pagar`
FROM v_all_sales
WHERE fcustomerid = {uid}
) AS Q1
ORDER BY Q1.日期orderdate DESC
"""
tv = self.ui.tableView_order
sql = sql.format(uid=self.__getUID())
self.dataInfo_order = JPQueryFieldInfo(sql)
self.mod_order = myJPTableViewModelReadOnly(tv, self.dataInfo_order)
tv.setModel(self.mod_order)
tv.resizeColumnsToContents()
def refreshRec(self):
sql = """SELECT *
FROM (SELECT fid AS 流水号ID,
freceiptdate AS 收款日期ReceiptDate,
e.ftitle AS 收款方式ModoPago,
famountcollected AS 收款额AmountCollected,
u.fusername AS 收款人fPayee
FROM t_receivables r
LEFT JOIN t_enumeration e
ON r.fpaymentmethodid = e.fitemid
LEFT JOIN sysusers u
ON r.fpayeeid = u.fuserid
WHERE r.fcustomerid = {uid}
UNION ALL
SELECT '' AS 流水号ID,
'合计Sum' AS 收款日期ReceiptDate,
'' AS 收款方式ModoPago,
Sum(famountcollected) AS 收款额AmountCollected,
'' AS 收款人fPayee
FROM t_receivables r
WHERE r.fcustomerid = {uid}) Q1
ORDER BY Q1.收款日期receiptdate DESC """
tv = self.ui.tableView_rec
sql = sql.format(uid=self.__getUID())
self.dataInfo_rec = JPQueryFieldInfo(sql)
self.mod_rec = myJPTableViewModelReadOnly(tv, self.dataInfo_rec)
tv.setModel(self.mod_rec)
tv.resizeColumnsToContents()
class TreeModel(QAbstractItemModel):
def __init__(self, parent=None):
super().__init__(parent=parent)
self.SQL = """
SELECT fMenuText, fEnabled, fDefault, fIcon, fNodeBackvolor
, fNodeForeColor, fNodeFontBold, fExpanded, fNMID, fDispIndex
, fParentId, fIsCommandButton, fCommand, fObjectName, fFormMode
, fArg, fDescription, fLevel
FROM sysnavigationmenus
ORDER BY fDispIndex
"""
self.tableInfo = JPQueryFieldInfo(self.SQL)
def rowCount(self, parent=QModelIndex()):
return len(self.tableInfo)
def columnCount(self, parent=QModelIndex()):
return len(self.tableInfo.Fields)
def data(self, Index, role=Qt.DisplayRole):
r = Index.row()
c = Index.column()
if role == Qt.DisplayRole:
return self.tableInfo.getOnlyData([r, c])
def setData(self, QModelIndex, Any, role=Qt.EditRole):
return super().setData(QModelIndex, Any, role=role)
def parent(self, QModelIndex):
return super().parent(QModelIndex)
def headerData(self, int, QtOrientation, role=Qt.DisplayRole):
if role == Qt.DisplayRole and QtOrientation == Qt.Horizontal:
return 'Col' + str(int)
def dispDetail(self):
pid = -1
tv = self.ui.tableView
index = tv.selectionModel().currentIndex()
if index.isValid():
pid = self.dataInfo.getOnlyData([index.row(), 0])
d1 = JPDateConver(self.ui.dateBegin.date(), str)
d2 = JPDateConver(self.ui.dateEditEnd.date(), str)
self.sql_detail = f"""
SELECT q.fOrderDate AS 日期OrderDate,
q.fOrderID AS 单据号码OrderID,
ksmc AS 客商Merchants,
rk AS 入库In ,
ck AS 出库Out
FROM
(SELECT o.fOrderDate,
o.fOrderID,
s.fSupplierName AS ksmc,
d.fQuant AS rk,
null AS ck,
d.TS
FROM t_product_warehousereceipt_order_detail AS d
LEFT JOIN t_product_warehousereceipt_order AS o
ON d.fOrderID=o.fOrderID
LEFT JOIN t_supplier AS s
ON o.fSupplierID=s.fSupplierID
WHERE o.fOrderDate
BETWEEN '{d1}'
AND '{d2}'
AND fProductID={pid}
AND o.fSubmited=1
UNION all
SELECT o.fOrderDate,
o.fOrderID,
s.fCustomerName AS ksmc,
NULL AS rk,
d.fQuant AS ck,
d.TS
FROM t_product_outbound_order_detail AS d
LEFT JOIN t_product_outbound_order AS o
ON d.fOrderID=o.fOrderID
LEFT JOIN t_customer AS s
ON o.fCustomerID=s.fCustomerID
WHERE o.fOrderDate
BETWEEN '{d1}'
AND '{d2}'
AND fProductID={pid}
AND o.fSubmited=1
) AS q
ORDER BY q.Ts DESC
"""
self.dataInfo_detail = JPQueryFieldInfo(self.sql_detail)
self.mod3 = JPTableViewModelReadOnly(self.ui.tableView_rec,
self.dataInfo_detail)
self.ui.tableView_rec.setModel(self.mod3)
self.ui.tableView_rec.resizeColumnsToContents()
bz = (len(self.dataInfo_detail) > 0)
self.ui.CmdExportToExcel_Detail.setEnabled(bz)
self.ui.CmdPrint_Detail.setEnabled(bz)
def actionClick(self, where_sql=None):
d1 = JPDateConver(self.ui.dateEdit_begin.date(), str)
d2 = JPDateConver(self.ui.dateEdit_end.date(), str)
tv = self.ui.tableView
self.dataInfo = JPQueryFieldInfo(self.list_sql.format(d1=d1, d2=d2))
self.mod = myJPTableViewModelReadOnly(tv, self.dataInfo)
tv.setModel(self.mod)
#tv.setItemDelegateForColumn(9, de)
tv.resizeColumnsToContents()
def dateChanged(self, s_data):
str_date = JPDateConver(self.ui.SelectDate.date(), str)
# 设置当前日收款记录（左上）
sql = self.SQLCurrentDayRec.format(dateString=str_date)
self.tabinfoCurrentDayRec = JPQueryFieldInfo(sql)
self.modCurrentDayRec = JPTableViewModelReadOnly(
self.ui.tabCurrentDayRec, self.tabinfoCurrentDayRec)
self.ui.tabCurrentDayRec.setModel(self.modCurrentDayRec)
self.ui.tabCurrentDayRec.resizeColumnsToContents()
self.ui.tabCurrentDayRec.selectionModel().currentRowChanged[
QModelIndex, QModelIndex].connect(self.currentCustomerChanged)
# 设置当前日收款方式统计（右上）
self.tabinfoFangShiTongJi = JPQueryFieldInfo(
self.SQLSumPaymentMethod.format(dateString=str_date))
self.modFangShiTongJi = JPTableViewModelReadOnly(
self.ui.SumPaymentMethod, self.tabinfoFangShiTongJi)
self.ui.SumPaymentMethod.setModel(self.modFangShiTongJi)
self.ui.SumPaymentMethod.resizeColumnsToContents()
def _search(self):
db = JPDb()
sql = self.cbo_base.currentData() if (
self.cbo_base.currentIndex() != -1
and self.cbo_year.currentIndex() != -1) else self.sql_none.format(
db.getOnlyStrcFilter())
tab = JPQueryFieldInfo(sql.format(self.cbo_year.currentText()))
self.__changeTitle(tab)
self.queryInfo = tab
self.mod = _myMod(self.tableView, self.queryInfo)
self.tableView.setModel(self.mod)
def currentCustomerChanged(self):
id = -1
index = self.ui.tabCurrentDayRec.selectionModel().currentIndex()
if index.isValid():
id = self.modCurrentDayRec.TabelFieldInfo.getOnlyData(
[index.row(), 1])
# 刷新左下
self.tableinfoCustomerRecorder = JPQueryFieldInfo(
self.SQLCustomerRecorder.format(CustomerID=id))
self.modCustomerRecorder = JPTableViewModelReadOnly(
self.ui.tabCustomerRecorder, self.tableinfoCustomerRecorder)
self.ui.tabCustomerRecorder.setModel(self.modCustomerRecorder)
self.ui.tabCustomerRecorder.resizeColumnsToContents()
# 刷新右下
self.tableinfoCustomerArrearsList = JPQueryFieldInfo(
self.SQLCustomerArrearsList.format(CustomerID=id))
self.modCustomerArrearsList = JPTableViewModelReadOnly(
self.ui.tabCustomerArrearsList, self.tableinfoCustomerArrearsList)
self.ui.tabCustomerArrearsList.setModel(self.modCustomerArrearsList)
self.ui.tabCustomerArrearsList.resizeColumnsToContents()
def __readSQL(self, sql):
info = JPQueryFieldInfo(sql)
self.currentSQL = sql
self.MainForm.ProgressBar.show()
self.MainForm.Label.setText('Reading')
self.model = self.onGetModelClass()(self.ui.tableView, info)
self.model.readingRow.connect(self.__refreshProcessBar)
self.MainForm.ProgressBar.setRange(0, len(info))
self.ui.tableView.setModel(self.model)
self.MainForm.Label.setText('')
self.MainForm.ProgressBar.hide()
self.ui.tableView.selectionModel().currentRowChanged[
QModelIndex, QModelIndex].connect(self.onCurrentRowChanged)
self.ui.tableView.resizeColumnsToContents()
self.TableInfo = info
def on_CmdExportToExcel_clicked(self):
sql = """
SELECT fOrderID,
fQuant AS '数量Qtd',
fProductName AS '名称Descrição',
fLength AS '长Comp.',
fWidth AS '宽Larg.',
fPrice AS '单价P. Unitario',
fAmount AS '金额Total'
FROM t_order_detail
WHERE fOrderID IN (
SELECT 订单号码OrderID FROM ({cur_sql}) Q)"""
sql = sql.format(cur_sql=self.currentSQL)
tab = JPQueryFieldInfo(sql)
exp = JPExpExcelFromTabelFieldInfo(self.model.TabelFieldInfo,
self.MainForm)
exp.setSubQueryFieldInfo(tab, 0, 0)
exp.run()
def actionClick(self):
sql = """
SELECT c.fCustomerID AS `ID`, c.fCustomerName AS `客户名称Cliente`, c.fNUIT AS `税号NUIT`, c.fCity AS `城市City`
, if(isnull(QDD.dd), NULL, QDD.dd) AS 订单应付金额OrderPayable
, if(isnull(QSK.sk), NULL, QSK.sk) AS Aeceivables收款
, if(if(isnull(QDD.dd), 0, QDD.dd) - if(isnull(QSK.sk), 0, QSK.sk) = 0, NULL, if(isnull(QDD.dd), 0, QDD.dd) - if(isnull(QSK.sk), 0, QSK.sk)) AS Arrears欠款
FROM t_customer c
LEFT JOIN (
SELECT fCustomerID, SUM(fAmountCollected) AS sk
FROM t_receivables
GROUP BY fCustomerID
) QSK
ON QSK.fCustomerID = c.fCustomerID
LEFT JOIN (
SELECT fCustomerID, SUM(fPayable) AS dd
FROM v_all_sales as Q_1
GROUP BY fCustomerID
) QDD
ON QDD.fCustomerID = c.fCustomerID
WHERE NOT (isnull(QDD.dd)
AND isnull(QSK.sk)) AND {wherestring}"""
wherestring = """(
fCustomerName like '%{key}%' or
fNUIT like '%{key}%'
)"""
txt = self.ui.lineEdit.text()
txt = txt if txt else ''
wherestring = wherestring.format(key=txt)
sql = sql.format(wherestring=wherestring)
tv = self.ui.tableView
self.dataInfo = JPQueryFieldInfo(sql)
self.mod = JPTableViewModelReadOnly(tv, self.dataInfo)
tv.setModel(self.mod)
tv.resizeColumnsToContents()
tv.selectionModel().currentRowChanged[QModelIndex,
QModelIndex].connect(
self.refreshRec)
tv.selectionModel().currentRowChanged[QModelIndex,
QModelIndex].connect(
self.refreshOrder)
def actionClick(self):
key = self.ui.lineEdit.text()
key = key if key else ''
sql = f"""
select
fID as `序号NO.`,
fProductName as `产品名称Descrição do produto`,
fCurrentQuantity as 当前库存Quantidade ,
fSpesc as `规格Especificação`,
fWidth as 宽Largura,
fLength as 长Longo,
fUint 单位Unidade,
fNote as 备注Observações
from t_product_information
where fCancel=0 and fProductName like '%{key}%'
order by fID
"""
self.tab = JPQueryFieldInfo(sql)
self.mod = JPTableViewModelReadOnly(self.ui.tableView, self.tab)
self.ui.tableView.setModel(self.mod)
self.ui.tableView.resizeColumnsToContents()
def __customerIDChanged(self):
# 刷新客户有关信息
sql = '''
SELECT fNUIT, fCity, fEndereco, fCelular, fContato
, fTelefone, Q0.fAmountPayable , Q1.fAmountPaid,
Q0.fAmountPayable-Q1.fAmountPaid as fArrears,-1 as fPaymentMethodID
FROM t_customer c
LEFT JOIN (
SELECT fCustomerID, SUM(fPayable) AS fAmountPayable
FROM v_all_sales
WHERE fCustomerID = {CustomerID}
) Q0
ON c.fCustomerID = Q0.fCustomerID
LEFT JOIN (
SELECT fCustomerID, SUM(fAmountCollected) AS fAmountPaid
FROM t_receivables
WHERE fCustomerID = {CustomerID}
) Q1
ON c.fCustomerID = Q1.fCustomerID
WHERE c.fCustomerID = {CustomerID}
'''
sql = sql.format(CustomerID=self.ui.fCustomerID.Value())
tab = JPQueryFieldInfo(sql)
obj_name = [
'fNUIT', 'fCity', 'fEndereco', 'fCelular', 'fContato', 'fTelefone',
'fAmountPayable', 'fAmountPaid', 'fArrears'
]
tup = (JPWidgets.QLineEdit, JPWidgets.QDateEdit, JPWidgets.QComboBox,
JPWidgets.QTextEdit, JPWidgets.QCheckBox)
fld_dict = tab.getRowFieldsInfoDict(0)
for i, nm in enumerate(obj_name):
obj = self.findChild(tup, nm)
if obj:
obj.setRowsData(tab.DataRows[0])
obj.setMainModel(self)
obj.setFieldInfo(fld_dict[nm])
obj.refreshValueNotRaiseEvent(tab.getOnlyData([0, i]), True)
# 刷新该客户名下当日单据号码
sql_orderID = """
select 'DIBOTO' as fOrderID
union all
select 'Prepaid' as fOrderID
union all
select fOrderID from t_order
where fCustomerID={CustomerID}
and fOrderDate=STR_TO_DATE('{dateString}', '%Y-%m-%d')
and fConfirmed=1
and fSubmited=1
and fCanceled=0
union all
select fOrderID from t_product_outbound_order
where fCustomerID={CustomerID}
and fOrderDate=STR_TO_DATE('{dateString}', '%Y-%m-%d')
and fSubmited=1
and fCanceled=0
"""
sql_orderID = sql_orderID.format(
CustomerID=self.ui.fCustomerID.Value(),
dateString=JPDateConver(self._currentDate, str))
tab = JPQueryFieldInfo(sql_orderID)
fld = self.ui.fOrderID.FieldInfo
fld.RowSource = [[r.Datas[0]] for r in tab.DataRows]
fld.BindingColumn = 0
fld.Value = None
fld.NotNull = True
self.ui.fOrderID.setFieldInfo(fld, False)
self.ui.fOrderID.setEnabled(True)
def __init__(self,
curdate,
cur_tab,
tongji_tab,
dateString,
PaperSize=QPrinter.A4,
Orientation=QPrinter.Orientation(0)):
super().__init__(PaperSize, Orientation)
self.configData = JPPub().getConfigData()
self.font_YaHei = QFont("Microsoft YaHei")
self.font_YaHei_8 = QFont(self.font_YaHei)
self.font_YaHei_8.setPointSize(8)
self.font_YaHei_10 = QFont(self.font_YaHei)
self.font_YaHei_10.setPointSize(20)
self.font_YaHei_10.setBold(True)
self.BackColor = JPPub().getConfigData(
)['PrintHighlightBackgroundColor']
rpt = self
rpt.logo = JPPub().MainForm.logoPixmap
rpt.ReportHeader.AddItemRect(2, (0, 0, 274, 50), rpt.logo)
rpt.ReportHeader.AddItemRect(1, (274, 0, 446, 60),
'de vendas diárias 收款日报表',
Bolder=False,
AlignmentFlag=(Qt.AlignCenter),
Font=self.font_YaHei_10)
rpt.ReportHeader.AddItemRect(1, (0, 50, 720, 20),
'Date:{}'.format(curdate),
Bolder=False,
AlignmentFlag=(Qt.AlignRight),
Font=self.font_YaHei_8)
title = [
'序号\nID', '客户名\nCliente', '收款额\nAmount', '收款人\nfPayee',
'收款方式\nModoPago', '单据号\nOrderID', '备注\nNote'
]
fns = [
'fID', 'fCustomerName', 'fAmountCollected', 'fPayee',
'fPaymentMethod', 'fOrderID', 'fNote'
]
cols = 7
al_c = Qt.AlignCenter
al_r = (Qt.AlignVCenter | Qt.AlignRight)
al_l = (Qt.AlignVCenter | Qt.AlignLeft)
rpt.SetMargins(30, 30, 30, 30)
rpt.ReportHeader.AddPrintLables(0,
72,
40,
Texts=title,
Widths=[40, 210, 80, 80, 100, 120, 90],
Aligns=[al_c] * cols)
rpt.Detail.addPrintRowCountItem(0,
0,
40,
20,
AlignmentFlag=al_c,
Font=self.font_YaHei_8)
rpt.Detail.AddItem(
3,
40,
0,
210,
20,
fns[1],
FormatString=' {}',
AlignmentFlag=al_l,
# 超出长度省略
AutoShrinkFont=self.configData['AutoShrinkFonts'],
AutoEllipsis=self.configData['AutoEllipsis'],
Font=self.font_YaHei_8)
rpt.Detail.AddItemRect(3, (250, 0, 80, 20),
fns[2],
AlignmentFlag=al_r,
FormatString='{:,.2f} ',
Font=self.font_YaHei_8)
rpt.Detail.AddItemRect(3, (330, 0, 80, 20),
fns[3],
AlignmentFlag=al_c,
Font=self.font_YaHei_8)
rpt.Detail.AddItemRect(3, (410, 0, 100, 20),
fns[4],
AlignmentFlag=al_c,
Font=self.font_YaHei_8)
rpt.Detail.AddItemRect(3, (510, 0, 120, 20),
fns[5],
AlignmentFlag=al_l,
FormatString=' {}',
Font=self.font_YaHei_8)
rpt.Detail.AddItemRect(3, (630, 0, 90, 20),
fns[6],
AlignmentFlag=al_l,
FormatString=' {}',
Font=self.font_YaHei_8)
sum_j = 0
for i in range(len(cur_tab)):
sum_j += cur_tab.getOnlyData([i, 4])
rpt.ReportFooter.AddPrintLables(
0,
0,
20,
Texts=["合计Sum", JPGetDisplayText(sum_j), " "],
Widths=[250, 80, 390],
Aligns=[al_c, al_r, al_c],
FillColor=self.BackColor,
Font=self.font_YaHei_8)
sql_payable = f"""
SELECT SUM(fPayable) AS sumPayable,
COUNT(fOrderID) AS countOrderID
FROM v_all_sales
WHERE (fOrderDate = STR_TO_DATE('{dateString}', '%Y-%m-%d'))
"""
sql_SKFS = f"""
select if(isnull(Q.fPaymentMethod),'Sum合计',Q.fPaymentMethod) as skfs,
Q.今日收款,Q.今日收款笔数,Q.DIBOTO,Q.DIBOTO笔数,Q.Prepaid,Q.Prepaid笔数,Q.小计Subtotal,Q.笔数小计Subcount
from (
SELECT fPaymentMethod
, SUM(if(fOrderID = 'DIBOTO' or fOrderID = 'Prepaid', NULL, fAmountCollected)) AS 今日收款
, COUNT(if(fOrderID = 'DIBOTO' or fOrderID = 'Prepaid', NULL, fAmountCollected)) AS 今日收款笔数
, SUM(if(fOrderID = 'DIBOTO', fAmountCollected, NULL)) AS DIBOTO
, COUNT(if(fOrderID = 'DIBOTO', fAmountCollected, NULL)) AS DIBOTO笔数
, SUM(if(fOrderID = 'Prepaid', fAmountCollected, NULL)) AS Prepaid
, COUNT(if(fOrderID = 'Prepaid', fAmountCollected, NULL)) AS Prepaid笔数
, SUM(fAmountCollected) AS 小计Subtotal, COUNT(fAmountCollected) AS 笔数小计Subcount
FROM v_receivables
WHERE fReceiptDate=STR_TO_DATE('{dateString}', '%Y-%m-%d')
GROUP BY fPaymentMethod WITH ROLLUP) as Q
"""
title_height = 20
rpt.ReportFooter.AddItem(1,
0,
title_height,
720,
30,
"本日结算方式统计Today's settlement statistics",
Bolder=False,
AlignmentFlag=al_c)
title_height += 30
title = [
'方式PM', "收当日订单Today's Order Rec", '收欠款DIBOTO', '预付款Prepaid',
'小计SubTotle'
]
rpt.ReportFooter.AddPrintLables(0,
title_height,
25,
title,
Widths=[120, 150, 150, 150, 150],
Aligns=[al_c] * 5,
Font=self.font_YaHei_8)
tongji_tab = JPQueryFieldInfo(sql_SKFS)
title_height += 25
for r in range(len(tongji_tab)):
FillColor = QColor(255, 255,
255) if r < (len(tongji_tab) - 1) else QColor(
194, 194, 194)
rpt.ReportFooter.AddItem(1,
0,
title_height + r * 20,
120,
20,
tongji_tab.getDispText([r, 0]),
FormatString=' {}',
AlignmentFlag=al_l,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
120,
title_height + r * 20,
100,
20,
tongji_tab.getDispText([r, 1]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
220,
title_height + r * 20,
50,
20,
tongji_tab.getDispText([r, 2]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
270,
title_height + r * 20,
100,
20,
tongji_tab.getDispText([r, 3]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
370,
title_height + r * 20,
50,
20,
tongji_tab.getDispText([r, 4]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
420,
title_height + r * 20,
100,
20,
tongji_tab.getDispText([r, 5]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
520,
title_height + r * 20,
50,
20,
tongji_tab.getDispText([r, 6]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
570,
title_height + r * 20,
100,
20,
tongji_tab.getDispText([r, 7]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
rpt.ReportFooter.AddItem(1,
670,
title_height + r * 20,
50,
20,
tongji_tab.getDispText([r, 8]),
FormatString='{} ',
AlignmentFlag=al_r,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
# 总结部分
title_height += (len(tongji_tab) - 1) * 20
title_height += 40
title = [
"当日订单应付Today's Order Payable",
"收当日订单Today's Order Rec",
'欠款Arrears',
]
rpt.ReportFooter.AddPrintLables(120,
title_height,
25,
title,
Widths=[200, 200, 200],
Aligns=[al_c] * 3,
Font=self.font_YaHei_8)
rpt.ReportFooter.AddItem(1,
0,
title_height,
120,
45,
'总结\nsummary',
FormatString='{} ',
AlignmentFlag=al_c,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
title_height += 25
payable_tab = JPQueryFieldInfo(sql_payable)
v1 = payable_tab.getOnlyData([0, 0])
v2 = tongji_tab.getOnlyData([len(tongji_tab) - 1, 1])
v3 = '{:,.2f}'.format((v1 if v1 else 0) - (v2 if v2 else 0))
ShouDangRiDingDan = tongji_tab.getDispText([len(tongji_tab) - 1, 1
]) + " " if v2 else "0 "
txt = [
payable_tab.getDispText([0, 0]) + " ",
JPGetDisplayText(len(payable_tab), str) + " ", ShouDangRiDingDan,
tongji_tab.getDispText([len(tongji_tab) - 1, 2]) + " ", v3 + " "
]
rpt.ReportFooter.AddPrintLables(120,
title_height,
20,
txt,
Widths=[150, 50, 150, 50, 200],
Aligns=[al_r] * 5,
Font=self.font_YaHei_8,
FillColor=self.BackColor)
# 页脚
self.PageFooter.AddItemRect(4, (10, 0, 100, 20),
'',
FormatString='Page: {Page}/{Pages}',
Bolder=False,
AlignmentFlag=Qt.AlignLeft,
Font=self.font_YaHei_8)
self.PageFooter.AddItemRect(
5, (0, 0, 720, 20),
'',
FormatString="PrintTime: %Y-%m-%d %H:%M:%S",
Bolder=False,
AlignmentFlag=Qt.AlignRight,
Font=self.font_YaHei_8)
self.DataSource = [
cur_tab.getRowValueDict(i) for i in range(len(cur_tab))
]
class Form_EnumManger(QWidget):
def __init__(self, mainform):
super().__init__()
self.CurrentTypeID = None
ui = Ui_Form_list()
ui.setupUi(self)
sql1 = """SELECT fTypeID AS 'TypeID 类别ID',
fTypeName AS 'TypeName 名称',
fNote AS 'Note 说明'
FROM t_enumeration_type
ORDER BY fTypeID
"""
self.ui = ui
mainform.addOneButtonIcon(ui.butNew, "new.png")
self.tab1 = ui.tabelViewType
self.tab2 = ui.tabelViewEnum
self.mainform = mainform
self.tab1.setSelectionMode(QAbstractItemView.SingleSelection)
self.tab2.setSelectionMode(QAbstractItemView.SingleSelection)
self.tab1.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tab2.setSelectionBehavior(QAbstractItemView.SelectRows)
self.tabinfo1 = JPQueryFieldInfo(sql1)
self.mod1 = _myReadOnlyMod(self.tab1, self.tabinfo1)
self.tab1.setModel(self.mod1)
self.tab1.resizeColumnsToContents()
mainform.addForm(self)
self.tab1.selectionModel().currentRowChanged.connect(
self.type_selected)
self.setTab2Column()
self.refreshTabEnum()
self.ui.butNew.clicked.connect(self.but_New)
def type_selected(self, index1, index2):
self.CurrentTypeID = self.tabinfo1.getOnlyData([index1.row(), 0])
self.refreshTabEnum(self.CurrentTypeID)
def refreshTabEnum(self, type_id: int = -1):
sql2 = """
SELECT fItemID, fTypeID,
fTitle AS 'text条目文本',
fSpare1 AS 'Value1值1',
fSpare2 AS 'Value2值2',
fNote AS 'Note说明'
FROM t_enumeration
WHERE fTypeID = {}
""".format(type_id)
self.tabinfo2 = JPTabelFieldInfo(sql2)
self.mod2 = JPTableViewModelReadOnly(self.tab2, self.tabinfo2)
self.tab2.setModel(self.mod2)
self.setTab2Column()
def but_New(self):
tid = self.CurrentTypeID
if tid is None:
return
sql = """select fItemID,fTypeID,
fTitle AS 'text条目文本',
fSpare1 AS 'Value1值1',
fSpare2 AS 'Value2值2',
fNote AS 'Note说明'
from t_enumeration where fItemID='{}'"""
frm = EditForm_Enum(sql, None, JPEditFormDataMode.New, tid)
frm.afterSaveData.connect(self.refreshsub)
frm.exec_()
def refreshsub(self, ID):
self.refreshTabEnum(self.CurrentTypeID)
self._locationRow(ID)
def setTab2Column(self):
self.tab2.setColumnHidden(0, True)
self.tab2.setColumnHidden(1, True)
self.tab2.setColumnWidth(2, 300)
self.tab2.setColumnWidth(3, 100)
self.tab2.setColumnWidth(4, 100)
self.tab2.setColumnWidth(5, 300)
def _locationRow(self, id):
tab = self.tabinfo2
c = tab.PrimarykeyFieldIndex
id = int(id)
target = [
i for i, r in enumerate(tab.DataRows)
if tab.getOnlyData([i, c]) == id
]
if target:
index = self.mod2.createIndex(target[0], c)
self.ui.tabelViewEnum.setCurrentIndex(index)
return