def __init__(self, parent=None, flags=Qt.WindowFlags()): super().__init__(parent=parent, flags=flags) self.ui = Ui_Dialog() self.ui.setupUi(self) #self.ui.treeView.setModel(TreeModel()) # self.ui.treeWidget.setItemDelegateForColumn() self.ui.treeWidget.setColumnCount(18) self.ui.treeWidget.setHeaderLabels([ '显示文本', '显示顺序', '图标', '默认权限', '前景色', '背景色', 'fObjectName', 'fFormMode', 'fArg', 'fIcon', 'fDefault', 'fNodeBackvolor', 'fNodeForeColor', 'fNodeFontBold', 'fExpanded', 'fDescription', 'fLevel', 'fIsCommandButton' ]) self.SQL = """ SELECT fNMID, fDispIndex, fParentId, fEnabled, fMenuText , fCommand, fObjectName, fFormMode, fArg, fIcon , fDefault, fNodeBackvolor, fNodeForeColor, fNodeFontBold, fExpanded , fDescription, fLevel, fIsCommandButton FROM sysnavigationmenus ORDER BY fDispIndex """ db = JPDb() lst = db.getDict(self.SQL) loadTreeview(self.ui.treeWidget, lst)
def __refreshCurrentUserRight(self): db = JPDb() uid = self.currentUserID() sql_1 = """ SELECT 1, n.fNMID, n.fDispIndex, n.fParentId, n.fMenuText, n.fObjectName, n.fIcon, n.fIsCommandButton+0 AS fIsCommandButton, 1 as fHasRight FROM sysnavigationmenus n where n.fNMID in (1,11,13,135,136,137) or n.fDefault=1 """ sql_else = """ SELECT ur.fUserID, n.fNMID, n.fDispIndex, n.fParentId, n.fMenuText, n.fObjectName, n.fIcon, n.fIsCommandButton+0 AS fIsCommandButton, ur.fHasRight+0 as fHasRight FROM sysnavigationmenus n left JOIN ( SELECT * FROM sysuserright WHERE fUserID = {} ) ur ON n.fNMID = ur.fRightID WHERE (n.fEnabled = 1 and ur.fHasRight=1) or n.fDefault=1 ORDER BY n.fParentId, n.fDispIndex """.format(uid) exesql = sql_1 if uid == 1 else sql_else re = db.getDict(exesql) menu = [r for r in re if r['fIsCommandButton'] == 0] for r in menu: r['btns'] = [ b for b in re if b['fParentId'] == r['fNMID'] and b['fIsCommandButton'] == 1 ] self.__CurrentUserRight = menu
def init_data(self, OrderID: str): SQL = f""" SELECT o.fOrderID, o.fOrderDate, o.fWarehousingDate , o.fSupplierName, o.fNUIT , o.fCity, o.fEndereco, o.fEmail , o.fContato, o.fCelular , o.fTelefone, o.fAmount , o.fTax, o.fPayable, o.fDesconto , o.fPurchaser , d.fQuant, d.fProductID, d.fPrice , d.fAmount as fAmount_detail, if(isnull(o.fNote),' ',o.fNote) AS fNote1 FROM v_product_warehousereceipt_order o RIGHT JOIN t_product_warehousereceipt_order_detail d ON o.fOrderID = d.fOrderID WHERE d.fOrderID='{OrderID}' """ db = JPDb() data = db.getDict(SQL) data.sort(key=lambda x: (x['fSupplierName'], x['fCity'], x['fAmount'] is None, x['fAmount'])) self.DataSource = data
def on_CmdSubmit_clicked(self): cu_id = self.getCurrentSelectPKValue() if not cu_id: return db = JPDb() info = self.model.TabelFieldInfo submitted = info.getOnlyData([ self.tableView.selectionModel().currentIndex().row(), self.fSubmited_column ]) if submitted == 1: msg = '记录【{cu_id}】已经提交,不能重复提交!\nThe order [{cu_id}] ' msg = msg + 'has been submitted, can not be repeated submission!' msg = msg.replace("{cu_id}", str(cu_id)) QMessageBox.warning(self, '提示', msg, QMessageBox.Ok, QMessageBox.Ok) return msg = '提交后出库单将不能修改!确定继续提交记录【{cu_id}】吗?\n' msg = msg + 'The order "{cu_id}" will not be modified after submission. ' msg = msg + 'Click OK to continue submitting?' msg = msg.replace("{cu_id}", str(cu_id)) reply = QMessageBox.question(self, '确认', msg, QMessageBox.Yes | QMessageBox.No, QMessageBox.No) if reply == QMessageBox.Yes: sql0 = f"""UPDATE t_product_outbound_order set fSubmited=1 where fOrderID='{cu_id}';""" sql2 = "select '{cu_id}';" db.executeTransaction([sql0, sql2]) JPPub().broadcastMessage(tablename="t_product_outbound_order", PK=cu_id, action='Submit') self.refreshListForm()
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 __init__(self, sql: str, noData: bool = False): '''根据一个Sql或表名返回一个JPTabelFieldInfo对象\n JPTabelFieldInfo(sql:str, noData:bool=False) ''' db = JPDb() self.PrimarykeyFieldName = None self.PrimarykeyFieldIndex = None self.TableName = None self.DeleteRows = [] sql = re.sub(r'^\s', '', re.sub(r'\s+', ' ', re.sub(r'\n', '', sql))) sel_p = r"^SELECT\s+.*from\s(\S+)[$|\s].*" mt = re.match(sel_p, sql, flags=(re.I | re.M)) self.TableName = mt.groups()[0] if mt else sql s_filter = db.getOnlyStrcFilter() if noData: # 找出不包含条件的SQL语句 p_s = r"^(SELECT\s+.*from\s(\S+)[$|\s](as\s\S+)*)" mt1 = re.match(p_s, sql, flags=(re.I | re.M)) sql = mt1.groups( )[0] + " " + s_filter if mt else 'Select * from {} {}'.format( self.TableName, s_filter) if mt else sql else: sql = sql if mt else 'Select * from {} {}'.format(sql, s_filter) super().__init__(sql) # 检查查询结果中是否包含主键, for i, fld in enumerate(self.Fields): if fld.IsPrimarykey is True: self.PrimarykeyFieldName = fld.FieldName self.PrimarykeyFieldIndex = i if self.PrimarykeyFieldIndex is None: raise ValueError('查询语句:\n"{}"中未包含主键字段!'.format(sql))
def on_CmdSubmit_clicked(self): cu_id = self.getCurrentSelectPKValue() if not cu_id: return db = JPDb() info = self.model.TabelFieldInfo submitted = info.getOnlyData([ self.tableView.selectionModel().currentIndex().row(), self.fSubmited_column ]) if submitted == 1: msg = '记录【{cu_id}】已经提交,不能重复提交!\nThe order [{cu_id}] ' msg = msg + 'has been submitted, can not be repeated submission!' msg = msg.replace("{cu_id}", str(cu_id)) QMessageBox.warning(self, '提示', msg, QMessageBox.Ok, QMessageBox.Ok) return msg = '提交后订单将不能修改!确定继续提交记录【{cu_id}】吗?\n' msg = msg + 'The order "{cu_id}" will not be modified after submission. ' msg = msg + 'Click OK to continue submitting?' msg = msg.replace("{cu_id}", str(cu_id)) reply = QMessageBox.question(self, '确认', msg, QMessageBox.Yes | QMessageBox.No, QMessageBox.No) if reply == QMessageBox.Yes: sql = "update {tn} set fSubmited=1 where {pk_n}='{pk_v}';" sql1 = "select '{pk_v}';" sql = sql.format(tn=self.EditFormMainTableName, pk_n=self.EditFormPrimarykeyFieldName, pk_v=cu_id) db.executeTransaction([sql, sql1.format(pk_v=cu_id)]) JPPub().broadcastMessage(tablename="t_order", PK=cu_id, action='Submit') self.refreshListForm()
def on_tableView_currentChanged(self, index1, index2): if index2.row() != -1: if self.checkDirty(): self.saveRight(index2) uid = self.dataInfo.DataRows[index1.row()].Data(0) ins_sql = """ INSERT INTO sysuserright (fUserID, fRightID, fHasRight) SELECT {uid}, fNMID, fDefault FROM sysnavigationmenus WHERE fEnabled = 1 AND NOT fNMID IN ( SELECT fRightID FROM sysuserright WHERE fUserID = {uid})""" sql = """ SELECT u.fUsername, m.*, ord(ur.fHasRight) AS fHasRight FROM sysnavigationmenus m LEFT JOIN sysuserright ur ON m.fNMID = ur.fRightID LEFT JOIN sysusers u ON ur.fUserID = u.fUserID WHERE ur.fUserID = {} AND ord(m.fEnabled) = 1 ORDER BY fDispIndex """ db = JPDb() db.executeTransaction(ins_sql.format(uid=uid)) items = db.getDict(sql.format(uid)) self.ui.treeWidget.itemChanged.disconnect(self.onItemChanged) loadTreeview(self.ui.treeWidget, items) self.ui.treeWidget.itemChanged.connect(self.onItemChanged)
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 accept(self): def msgbox(msg: str): QMessageBox.warning(self, '提示', msg, QMessageBox.Yes, QMessageBox.Yes) old_pw = self.ui.OldPassword.text() new_pw = self.ui.NewPassowrd.text() new_pw2 = self.ui.ConfirmPassword.text() uid = JPUser().currentUserID() if not all((old_pw, new_pw, new_pw2)): msgbox('请完整输入!\nPlease complete the form') return False sql = """ select fUserID from sysusers where fUserID='{uid}' and fPassword='******' """.format(uid=uid, pwd=md5_passwd(old_pw)) if not JPDb().getDataList(sql): msgbox('密码错误!\nPassword error!') return False if new_pw != new_pw2: msgbox( '两次输入新密码不一致!\nYour confirmed password and new password do not match' ) return False sql = """ update sysusers set fPassword='******' where fUserID='{uid}'""".format(uid=uid, pwd=md5_passwd(new_pw)) result = JPDb().executeTransaction(sql) if result: msgbox('密码修改完成。\nSuccessful password modification.') self.close()
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 __init__(self, sql): '''返回一个只读的查询的数据信息,数据不可更改''' db = JPDb() flds, data = db.getFeildsInfoAndData(sql) # self.__isMain = True self.Fields = flds self.DataRows = [JPTabelRowData(row) for row in data] self.FieldsDict = {fld.FieldName: fld for fld in flds} for i in range(len(self.Fields)): self.Fields[i]._index = i
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 __init__(self, dataBaseType: JPDbType = JPDbType.MySQL, *args, **kwargs): super(JPMainWindow, self).__init__(*args, **kwargs) try: db = JPDb() db.setDatabaseType(dataBaseType) JPPub().MainForm = self except Exception as e: QMessageBox.warning(self, "提示", str(e)) self.ui = Ui_MainWindow() self.ui.setupUi(self) self.ui.label_Title.setText("") self.commandDict = {} self.logoPixmap = None self.addOneButtonIcon(self.ui.ChangeUser, "changeuser.png") self.addOneButtonIcon(self.ui.ChangePassword, "changepassword.png") # self.addLogoToLabel(self.ui.label_logo) # 用户及密码修改功能 objUser = JPUser() objUser.INIT() # 程序开始时只初始化一次 objUser.userChange.connect(self.onUserChanged) objUser.currentUserID() self.ui.ChangeUser.clicked.connect(objUser.changeUser) self.ui.ChangePassword.clicked.connect(objUser.changePassword) # 堆叠布局 self.ui.stackedWidget.removeWidget(self.ui.page) self.ui.stackedWidget.removeWidget(self.ui.page_2) # 隐藏树标题 self.ui.label_FunPath.setText('') self.ui.treeWidget.setHeaderHidden(True) # 设置状态条中的进度条及标签 self.Label = QLabel(" ") self.ProgressBar = QProgressBar() self.statusBar = self.statusBar() self.statusBar.addPermanentWidget(self.Label) self.statusBar.addPermanentWidget(self.ProgressBar) self.ProgressBar.setGeometry(0, 0, 100, 5) self.ProgressBar.hide() self.statusBar.hide() self.ui.splitter.setStretchFactor(0, 2) self.ui.splitter.setStretchFactor(1, 11) # 连接点击了功能树中的节点到函数 self.ui.treeWidget.itemClicked[QTreeWidgetItem, int].connect(self.treeViewItemClicked)
def getSqls(self, pk_role: int = None): """返回主表的SQL语句,如果有检查空值错误,则引发一个错误,错误信息中包含字段名""" sqls = [] nm_lst = [] v_lst = [] mti = self.mainTableFieldsInfo ds = mti.DataRows[0].Datas st = self.EditMode TN = mti.TableName sql_i = 'INSERT INTO ' + TN + ' ({}) VALUES ({});\n' sql_u = 'UPDATE ' + TN + ' SET {} WHERE {}={};\n' row_st = mti.DataRows[0].State if (row_st == JPTabelRowData.New_None or row_st == JPTabelRowData.OriginalValue): if self.isNewMode: raise KeyError('您没有输入有效数据!No valid data was entered!') if st == JPEditFormDataMode.New: selectPkSQL = [] for fld in mti.Fields: if fld.IsPrimarykey: if fld.Auto_Increment: selectPkSQL = [JPDb().LAST_INSERT_ID_SQL()] continue else: nm_lst.append(fld.FieldName) v_lst.append('@PK') else: nm_lst.append(fld.FieldName) v_lst.append( self.ObjectDict()[fld.FieldName].getSqlValue()) sqls.append(sql_i.format(",".join(nm_lst), ",".join(v_lst))) if pk_role: newPKSQL = JPDb().NewPkSQL(pk_role) sqls = newPKSQL[0:2] + sqls + newPKSQL[2:] # 自动加上返回PK语句 return sqls + selectPkSQL if st == JPEditFormDataMode.Edit: selectPkSQL = [] for fld in mti.Fields: if fld.IsPrimarykey: r_pk_name = fld.FieldName r_pk_v = fld.sqlValue(ds[fld._index]) selectPkSQL = [f'select {r_pk_v};'] else: nm_lst.append(fld.FieldName) v_lst.append( self.ObjectDict()[fld.FieldName].getSqlValue()) temp = ['{}={}'.format(n, v) for n, v in zip(nm_lst, v_lst)] sqls.append(sql_u.format(",".join(temp), r_pk_name, r_pk_v)) # 自动加上返回PK语句 return sqls + selectPkSQL
def init_data(self, OrderID: str): SQL = """ SELECT o.*, if(not isnull(fNumerBegin) and not isnull(fNumerBegin), concat(fNumerBegin , ' VIE ' ,fNumerEnd),'') AS Numeracao, if(isnull(fNote),' ',fNote) as fNote1 FROM v_order o WHERE o.fOrderID ='{}'""" db = JPDb() data = db.getDict(SQL.format(OrderID)) data.sort(key=lambda x: (x['fCustomerName'], x['fCity'], x['fAmount'] is None, x['fAmount'])) self.DataSource = data
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)
def init_data(self, OrderID: str): SQL = """SELECT o.* , if(isnull(fNote), ' ', fNote) AS fNote1 , d.fQuant, d.fProductName, d.fLength, d.fWidth, d.fPrice , d.fAmount AS fAmountDetail FROM v_quotation o RIGHT JOIN t_quotation_detail d ON o.fOrderID = d.fOrderID WHERE d.fOrderID = '{}'""" db = JPDb() data = db.getDict(SQL.format(OrderID)) data.sort(key=lambda x: (x['fCustomerName'], x['fCity'], x['fAmount'] is None, x['fAmount'])) self.DataSource = data
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 accept(self): def msgbox(msg: str): QMessageBox.warning(self, '提示', msg, QMessageBox.Yes, QMessageBox.Yes) uid = self.ui.User.currentText() pwd = self.ui.Password.text() sql0 = """ select fUserID from sysusers where fUserName='******' and fPassword='******'""" sql1 = """ select fUserID from sysusers where fUserID='{uid}' and fPassword='******' and ord(fEnabled)=1""" if all((pwd, uid)): isAdmin = 1 if uid.upper() == 'ADMIN' else 0 if isAdmin: sql = sql0.format(pwd=md5_passwd(pwd), username=uid) else: sql = sql1.format(pwd=md5_passwd(pwd), uid=self.ui.User.currentData()) lst = JPDb().getDict(sql) if lst: JPUser().setCurrentUserID(lst[0]['fUserID']) #self.doFadeClose() self.hide() else: self.doShake() msgbox('用户名或密码错误!\nUsername or password incorrect!') else: self.doShake() msgbox('用名或密码没有输入!')
def reject(self): if not self.isLogin: self.hide() else: # 退出程序 JPDb().close() exit()
def __getProductInfo(self): sql = """ select fID,fProductName, fCurrentQuantity , fSpesc,fWidth,fLength,fUint from t_product_information where fCancel=0 """ lst = JPDb().getDataList(sql) return {r[0]: r[1:] for r in lst}
def on_CmdDelete_clicked(self): cu_id = self.getCurrentSelectPKValue() if not cu_id: return if self.beforeDeleteRow(cu_id): msg = "确认要删除记录【{}】吗?".format(cu_id) if QMessageBox.question(self, '确认', msg, QMessageBox.Yes, QMessageBox.Yes) == QMessageBox.Yes: db = JPDb() info = self.model.TabelFieldInfo sql = "delete from {tn} where {pk_n}='{pk_v}'" sql = sql.format(tn=self.EditFormMainTableName, pk_n=self.EditFormPrimarykeyFieldName, pk_v=cu_id) if db.executeTransaction(sql): del_i = ( self.tableView.selectionModel().currentIndex().row()) info.deleteRow(del_i)
def onGetFieldsRowSources(self): sql = '''select fSupplierName,fSupplierID, fNUIT,fCity,fContato,fTaxRegCer from t_supplier''' lst_supplier = JPDb().getDataList(sql) u_lst = [[item[1], item[0]] for item in JPUser().getAllUserList()] return [('fSupplierID', lst_supplier, 1), ('fPurchaserID', JPPub().getEnumList(10), 1), ('fEntryID', u_lst, 1)]
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 getSql(self, tablename): sql = "select * from {fn}".format(fn=tablename) con = JPDb().currentConn cur = con.cursor() cur.execute(sql) con.commit() tab = cur._result.rows if len(tab) == 0: return c_col = len(cur._result.fields) fns = ['`{}`'.format(r.name) for r in cur._result.fields] fg = ", " sql_ins = 'INSERT INTO `{tn}` ({f}) VALUES \n{v};' values = [] vs = [] for r in range(len(tab)): d = tab[r] vs = [] for col in range(c_col): v = d[col] if v is None: vs.append('Null') continue elif isinstance(v, str): vs.append("'{}'".format(v.replace("'", "\\'"))) elif isinstance(v, (int, float, Decimal)): vs.append('{}'.format(v)) elif isinstance(v, bytes): v = 0 if v == bytes([0]) else 1 vs.append('{}'.format(v)) elif isinstance(v, (datetime.datetime, datetime.date)): vs.append("'{}'".format(JPDateConver(v), str)) else: raise TypeError("没有该类型的转换器【{v}】={t}".format(v=fns[col], t=type(v))) vs = [str(r) for r in vs] values.append('(' + fg.join(vs) + ')') self.exportOneRecord.emit() sql_ins = sql_ins.format(tn=tablename, f=fg.join(fns), v=(fg + '\n\t').join(values)) return sql_ins
def on_CmdDelete_clicked(self): uid = self.getCurrentUID() if uid is None: return sql = "update sysusers set fEnabled=0 where fUserID={}" if QMessageBox.question(self, '提示', "确认要删除此用户?", (QMessageBox.Yes | QMessageBox.No), QMessageBox.Yes) == QMessageBox.Yes: JPDb().executeTransaction(sql.format(uid)) JPPub().broadcastMessage(tablename="sysusers", action='delete', PK=uid) self.mod.removeRow( self.ui.tableView.selectionModel().currentIndex().row())
def init_data(self, OrderID: str): SQL = """ SELECT o.fOrderID, o.fOrderDate, o.fRequiredDeliveryDate , o.fCustomerName, o.fNUIT , o.fCity, o.fEndereco, o.fEmail , o.fContato, o.fCelular , o.fTelefone, o.fAmount , o.fTax, o.fPayable, o.fDesconto , o.fVendedor , if(isnull(fNote), ' ', fNote) AS fNote1 , d.fQuant, d.fProductName, d.fLength, d.fWidth, d.fPrice , d.fAmount as fAmount_detail , if(isnull(fNote), ' ', fNote) AS fNote1 FROM v_order o RIGHT JOIN t_order_detail d ON o.fOrderID = d.fOrderID WHERE d.fOrderID = '{}' """ db = JPDb() data = db.getDict(SQL.format(OrderID)) # data.sort(key=lambda x: (x['fCustomerName'], x['fCity'], x['fAmount'] # is None, x['fAmount'])) self.DataSource = data
def _run(self): if not self.savePath: fileName_choose, filetype = QtWidgets.QFileDialog.getSaveFileName( JPPub().MainForm, "Export To SQL File Name", getcwd(), # 起始路径 "SQL Files (*.sql)") else: fileName_choose = self.savePath if not fileName_choose: return file_ = open(fileName_choose, 'w', encoding='utf-8') # 取所有表名 config = ConfigParser() config.read("config.ini", encoding="utf-8") dbn = dict(config._sections["database"])["database"] bases_sql = "SHOW TABLE STATUS FROM `{}`".format(dbn) tab = JPDb().getDict(bases_sql) tns = [r['Name'] for r in tab if r['Engine']] tns = [r for r in tns if not r in ['syssql', 'syslanguage']] views = [r['Name'] for r in tab if r['Comment'] == 'VIEW'] recs = 0 for r in tab: recs += r['Rows'] if r['Rows'] else 0 self.ui.progressBar.setRange(0, recs) exp = CreateSQL_MySQL() exp.exportOneRecord.connect(self.refreshProgressBar) for tn in tns: file_.write('-- 导出 表 {}.{} 结构'.format(dbn, tn)) file_.write('\n') file_.write('DROP TABLE IF EXISTS `{}`;'.format(tn)) file_.write('\n') file_.write(self.__getCr(dbn, tn, False) + ";") file_.write('\n') tempSQL = exp.getSql(tn) if tempSQL: file_.write(tempSQL) file_.write('\n') for vn in views: file_.write('-- 导出 视图 {}.{} 结构'.format(dbn, vn)) file_.write('\n') file_.write('DROP View IF EXISTS `{}`;'.format(vn)) file_.write('\n') file_.write(self.__getCr(dbn, vn, True) + ";") file_.write('\n') self.ui.progressBar.hide() file_.close() QtWidgets.QMessageBox.information(self, "提示", "导数据完成!") self.close()
def __customerIDChanged(self): c_id = self.ui.fCustomerID.Value() sql = f'''select fNUIT,fCity,fContato,fAreaCode, fCelular,fTelefone,fEndereco,fEmail, fWeb,fFax,fNote ,fTaxRegCer from t_customer where fCustomerID={c_id}''' dic = JPDb().getDict(sql)[0] if self.isNewMode: self.ui.fCelular.refreshValueNotRaiseEvent(dic['fCelular'], True) self.ui.fContato.refreshValueNotRaiseEvent(dic['fContato'], True) self.ui.fTelefone.refreshValueNotRaiseEvent(dic['fTelefone'], True) self.ui.fNUIT.setText(dic['fNUIT']) self.ui.fCity.setText(dic['fCity']) self.ui.fEndereco.setText(dic['fEndereco']) self.ui.fEmail.setText(dic['fEmail']) self.ui.fTelefone.setText(dic['fTelefone'])