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 actionClick(self):
txt = self.ui.lineEdit.text()
txt = txt if txt else ''
sql = self.list_sql.format(key=txt)
tv = self.ui.tableView
self.dataInfo = JPTabelFieldInfo(sql)
self.mod = myJPTableViewModelReadOnly(tv, self.dataInfo)
tv.setModel(self.mod)
# de = MyButtonDelegate(tv, self.dataInfo)
# tv.setItemDelegateForColumn(9, de)
tv.resizeColumnsToContents()
self.ui.tableView.selectionModel().currentRowChanged[
QModelIndex, QModelIndex].connect(self.dispDetail)
self.dispDetail()
def refreshTable(self):
self.SQL = """
select
fUserID as `编号 ID`,
fUsername as `用户名Name`,
fNickname as `昵称Nickname`,
fDepartment as `部门Department`,
fNotes as `备注Note `,
case fEnabled when 1 then '' else 'Non' end as 可用Enabled
from sysusers
where fUserID > 1
"""
tb = self.ui.tableView
self.dataInfo = JPTabelFieldInfo(self.SQL)
self.mod = myJPTableViewModelReadOnly(tb, self.dataInfo)
tb.setModel(self.mod)
tb.resizeColumnsToContents()
self.SQL_EditForm_Main = """
select fUserID as `编号 ID`,
fUsername as `用户名Name` ,
fNickname as `昵称Nickname`,
fDepartment as `部门Department`,
fPassword as 密码Password,
fNotes as `备注Note` , fEnabled
from sysusers WHERE fUserID = '{}'"""
tb.selectionModel().currentRowChanged[
QModelIndex, QModelIndex].connect(self.on_tableView_currentChanged)
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 dispAlertStock(self):
self.sql_low = """select
fID as `序号NO.`,
fProductName as `产品名称Descrição do produto`,
fCurrentQuantity as 当前库存Quantidade ,
fMinimumStock as 最低库存MinimumStock
from t_product_information
where fCancel=0 and fCurrentQuantity<fMinimumStock
order by fID
"""
tv = self.ui.tableView_low
self.dataInfo_low = JPTabelFieldInfo(self.sql_low)
self.mod_low = myJPTableViewModelReadOnly(tv, self.dataInfo_low)
tv.setModel(self.mod_low)
tv.resizeColumnsToContents()
bz = (len(self.dataInfo_low) > 0)
self.ui.CmdExportToExcel_Low.setEnabled(bz)
self.ui.CmdPrint_Low.setEnabled(bz)
def actionClick(self, where_sql=None):
wherestring = """where (
o.org_name like '%{key}%' or
o.org_parent like '%{key}%' or
o.org_simple_name like '%{key}%' or
o.statistician like '%{key}%'
) """
txt = self.ui.lineEdit.text()
txt = txt if txt else ''
wherestring = wherestring.format(key=txt)
sql = where_sql if where_sql else self.list_sql.format(
wherestring=wherestring)
tv = self.ui.tableView
self.dataInfo = JPTabelFieldInfo(sql)
self.mod = myJPTableViewModelReadOnly(tv, self.dataInfo)
tv.setModel(self.mod)
#de = MyButtonDelegate(tv, self.dataInfo)
#tv.setItemDelegateForColumn(9, de)
tv.resizeColumnsToContents()
def readData(self):
self._loadDdata = True
self.mainTableFieldsInfo = JPTabelFieldInfo(
self.mainSQL.format(self.PKValue),
(self.EditMode == JPEditFormDataMode.New))
tf = self.mainTableFieldsInfo
# 如果是新增模式，添加一行数据
if self.EditMode == JPEditFormDataMode.New:
tf.addRow()
# 设置字段行来源
temp = self.onGetFieldsRowSources()
if isinstance(temp, (list, tuple)):
for item in temp:
tf.setFieldsRowSource(*item)
fld_dict = tf.getRowFieldsInfoDict(0)
if fld_dict:
for k, v in self.ObjectDict().items():
if k in fld_dict:
v.setRowsData(tf.DataRows[0])
v.setMainModel(self)
conf = JPPub().ConfigData(False)
v.Null_prompt_bac_color = conf['Null_prompt_bac_color']
v.setFieldInfo(fld_dict[k])
def actionClick(self, where_sql=None):
wherestring = """where (
fSupplierName like '%{key}%' or
fEndereco like '%{key}%' or
fNUIT like '%{key}%' or
fCity like '%{key}%' or
fContato like '%{key}%' or
fCelular like '%{key}%' or
fEmail like '%{key}%' or
fFax like '%{key}%'
)"""
txt = self.ui.lineEdit.text()
txt = txt if txt else ''
wherestring = wherestring.format(key=txt)
sql = where_sql if where_sql else self.list_sql.format(
wherestring=wherestring)
tv = self.ui.tableView
self.dataInfo = JPTabelFieldInfo(sql)
self.mod = myJPTableViewModelReadOnly(tv, self.dataInfo)
tv.setModel(self.mod)
de = MyButtonDelegate(tv, self.dataInfo)
tv.setItemDelegateForColumn(9, de)
tv.resizeColumnsToContents()
db = JPDb()
db.setDatabaseType(1)
app = QApplication(sys.argv)
sql_0 = """
SELECT fOrderID as 订单号码OrderID,
fOrderDate as 日期OrderDate,
fCustomerName as 客户名Cliente,
fCity as 城市City,
fSubmited1 as 提交Submited,
fSubmit_Name as 提交人Submitter,
fRequiredDeliveryDate as 交货日期RequiredDeliveryDate,
fAmount as 金额SubTotal,
fDesconto as 折扣Desconto,
fTax as 税金IVA,
fPayable as `应付金额Valor a Pagar`,
fContato as 联系人Contato,
fCelular as 手机Celular,
fSubmited AS fSubmited,
fEntry_Name as 录入Entry
FROM v_order AS o"""
aaa = JPTabelFieldInfo(sql_0)
s = Form_Search(JPTabelFieldInfo(sql_0, True), sql_0)
s.exec_()
db = JPDb()
db.setDatabaseType(1)
sql = "select '' as Guanxi,'' as ZuoKuoHao, '' as Ziduan, '' as YunSuan,'' as Zhi1,'' as Zhi2, '' as YouKuoHao,fID from sysconfig Limit 0 "
tab = JPTabelFieldInfo(sql, True)
del tab.Fields[0]
a = 1
class Form_ProductList(QWidget):
def __init__(self, mainform):
super().__init__()
self.ui = UiFormProductList()
self.ui.setupUi(self)
self.MainForm = mainform
mainform.addForm(self)
self.list_sql = """
select
fID as `序号NO.`,
fProductName as `产品名称Descrição do produto`,
fCurrentQuantity as 当前库存Quantidade ,
fMinimumStock as 最低库存MinimumStock,
fSpesc as `规格Especificação`,
fWidth as 宽Largura,
fLength as 长Longo,
fUint 单位Unidade,
fNote as 备注Observações,
fProductPic as Pic
from t_product_information
where fCancel=0 and fProductName like '%{key}%'
order by fID
"""
medit_sql = """
select fID,fProductName as 产品名称ProductName,
fSpesc,fWidth,fLength,fUint,fNote,
fMinimumStock,fProductPic from t_product_information where fID={}"""
self.dataInfo_low = None
self.dataInfo_detail = None
self.sql_detail = None
icon = QIcon(JPPub().MainForm.icoPath.format("search.png"))
action = self.ui.lineEdit.addAction(icon, QLineEdit.TrailingPosition)
self.ui.lineEdit.returnPressed.connect(self.actionClick)
self.ui.lineEdit.setAttribute(Qt.WA_InputMethodEnabled, False)
action.triggered.connect(self.actionClick)
self.SQL_EditForm_Main = medit_sql
self.actionClick()
self.dispAlertStock()
self.pub = JPPub()
self.ui.dateBegin.setDate(QDate(QDate.currentDate().year(), 1, 1))
self.ui.dateEditEnd.setDate(QDate().currentDate())
self.ui.dateBegin.dateChanged.connect(self.dispDetail)
self.ui.dateEditEnd.dateChanged.connect(self.dispDetail)
#self.pub.UserSaveData.connect(self.UserSaveData)
mainform.addOneButtonIcon(self.ui.CmdPrint_Low, 'print.png')
mainform.addOneButtonIcon(self.ui.CmdExportToExcel_Low,
'exportToexcel.png')
mainform.addOneButtonIcon(self.ui.CmdPrint_Detail, 'print.png')
mainform.addOneButtonIcon(self.ui.CmdExportToExcel_Detail,
'exportToexcel.png')
de = MyButtonDelegate(self.ui.tableView, self.dataInfo)
self.ui.tableView.setItemDelegateForColumn(9, de)
def cellButtonClicked(self):
r = self.ui.tableView.currentIndex()
fn = self.dataInfo.DataRows[r.row()].Datas[9]
Form_ViewPic(self, fn)
def actionClick(self):
txt = self.ui.lineEdit.text()
txt = txt if txt else ''
sql = self.list_sql.format(key=txt)
tv = self.ui.tableView
self.dataInfo = JPTabelFieldInfo(sql)
self.mod = myJPTableViewModelReadOnly(tv, self.dataInfo)
tv.setModel(self.mod)
# de = MyButtonDelegate(tv, self.dataInfo)
# tv.setItemDelegateForColumn(9, de)
tv.resizeColumnsToContents()
self.ui.tableView.selectionModel().currentRowChanged[
QModelIndex, QModelIndex].connect(self.dispDetail)
self.dispDetail()
def dispAlertStock(self):
self.sql_low = """select
fID as `序号NO.`,
fProductName as `产品名称Descrição do produto`,
fCurrentQuantity as 当前库存Quantidade ,
fMinimumStock as 最低库存MinimumStock
from t_product_information
where fCancel=0 and fCurrentQuantity<fMinimumStock
order by fID
"""
tv = self.ui.tableView_low
self.dataInfo_low = JPTabelFieldInfo(self.sql_low)
self.mod_low = myJPTableViewModelReadOnly(tv, self.dataInfo_low)
tv.setModel(self.mod_low)
tv.resizeColumnsToContents()
bz = (len(self.dataInfo_low) > 0)
self.ui.CmdExportToExcel_Low.setEnabled(bz)
self.ui.CmdPrint_Low.setEnabled(bz)
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 getCurrentSelectPKValue(self):
index = self.ui.tableView.selectionModel().currentIndex()
if index.isValid():
return self.mod.TabelFieldInfo.getOnlyData([index.row(), 0])
def _locationRow(self, id):
tab = self.dataInfo
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.mod.createIndex(target[0], c)
self.ui.tableView.setCurrentIndex(index)
return
def refreshTable(self, ID=None):
self.ui.lineEdit.setText(None)
self.actionClick()
if ID:
self._locationRow(ID)
def getEditForm(self, sql_main, edit_mode, sql_sub, PKValue):
frm = EditForm_Product(sql_main=sql_main,
edit_mode=edit_mode,
PKValue=PKValue)
frm.afterSaveData.connect(self.refreshTable)
frm.setListForm(self)
return frm
@pyqtSlot()
def on_CmdExportToExcel_clicked(self):
exp = JPExpExcelFromTabelFieldInfo(self.mod.TabelFieldInfo,
self.MainForm)
exp.run()
@pyqtSlot()
def on_CmdExportToExcel_Low_clicked(self):
if self.dataInfo_low:
exp = JPExpExcelFromTabelFieldInfo(self.dataInfo_low,
self.MainForm)
exp.run()
@pyqtSlot()
def on_CmdExportToExcel_Detail_clicked(self):
exp = JPExpExcelFromTabelFieldInfo(self.dataInfo_detail, self.MainForm)
exp.run()
@pyqtSlot()
def on_CmdSearch_clicked(self):
frm = Form_Search(self.dataInfo, self.list_sql.format(wherestring=''))
frm.whereStringCreated.connect(self.actionClick)
frm.exec_()
@pyqtSlot()
def on_CmdPrint_clicked(self):
rpt = FormReport_ProductInfo()
rpt.initItem()
rpt.BeginPrint()
@pyqtSlot()
def on_CmdPrint_Low_clicked(self):
rpt = FormReport_ProductInfo_low()
rpt.initItem()
rpt.BeginPrint()
@pyqtSlot()
def on_CmdPrint_Detail_clicked(self):
if self.sql_detail:
rpt = FormReport_ProductInfo_Detail()
rpt.sql = self.sql_detail
rpt.beginDate = self.ui.dateBegin.date()
rpt.endDate = self.ui.dateEditEnd.date()
rpt.initItem()
rpt.BeginPrint()
@pyqtSlot()
def on_CmdRefresh_clicked(self):
self.actionClick()
@pyqtSlot()
def on_CmdNew_clicked(self):
frm = self.getEditForm(sql_main=self.SQL_EditForm_Main,
sql_sub=None,
edit_mode=JPEditFormDataMode.New,
PKValue=None)
frm.setListForm(self)
frm.afterSaveData.connect(self.refreshTable)
self.__EditForm = frm
frm.exec_()
@pyqtSlot()
def on_CmdEdit_clicked(self):
cu_id = self.getCurrentSelectPKValue()
if not cu_id:
return
frm = self.getEditForm(sql_main=self.SQL_EditForm_Main,
sql_sub=None,
edit_mode=JPEditFormDataMode.Edit,
PKValue=cu_id)
frm.setListForm(self)
frm.afterSaveData.connect(self.refreshTable)
self.__EditForm = frm
frm.exec_()
@pyqtSlot()
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 setTabelFieldInfo(self, tabelFieldInfo: JPTabelFieldInfo):
tabelFieldInfo.Data = [
JPTabelRowData(item) for item in tabelFieldInfo.Data
]
self.TabelFieldInfo = tabelFieldInfo