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)
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 __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)
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
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)) ]