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 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 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 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 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()
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