Exemplo n.º 1
0
        def process():
            try:
                pythoncom.CoInitialize()
                ex = Easyexcel(filePath,
                               visible=False,
                               access_password=openPassword,
                               write_res_password=editPassword)
                dm = DataManager()  # 子线程要创建一个单独的数据管理类
                for i, sheet in enumerate(
                        sheet_info):  # sheet_info格式为[(Sheet名, 数据表类型, 数据表名称)]
                    sheet_name, table_type, table_name = sheet
                    self.progressSignal.emit(
                        i, f"正在导入工作簿'{sheet_name}'({i}/{len(sheet_info)})")
                    header_dict, sheet_data = ex.get_sheet(sheet_name)
                    flag, msg = ExcelCheck.characters_check(sheet_data)
                    if not flag:
                        ex.close()
                        raise Exception(msg)
                    if table_type == '售后员提成明细':
                        sheet_data = ExcelCheck.formatted_after_sales(
                            header_dict, sheet_data)
                        dm.create_table(table_type, table_name,
                                        ExcelCheck.headers["售后员提成明细"])
                        dm.insert_data(table_name,
                                       ExcelCheck.headers["售后员提成明细"],
                                       sheet_data)
                    else:
                        # 过滤掉列名中的特殊字符以防导致SQL命令格式错误
                        f_header_dict = {
                            k.replace('\n', ''): v
                            for k, v in header_dict.items()
                        }
                        columns = sorted(
                            f_header_dict.keys(),
                            key=lambda x: f_header_dict[x])  # 与Excel表格列顺序相同
                        dm.create_table(table_type, table_name, columns)
                        # 此处将表头按照顺序排列。如果header_dict和sheet_data不匹配的话可能会出问题
                        dm.insert_data(table_name, columns, sheet_data)
                ex.close()
                self.progressSignal.emit(len(sheet_info), "导入完成")

            except Exception as err:
                logging.exception(err)
                self.errorSignal.emit(err)
Exemplo n.º 2
0
    def generateCmsTableWork(self):
        """子线程函数:生成提成表单并写入数据库"""
        try:
            dm = DataManager()
            src_dict, src_data = dm.get_table(
                self.dataSourceComboBox.currentText())
            rul_dict, rul_data = dm.get_table(self.ruleComboBox.currentText())
            calc_ratio = CalcRatio(rul_dict, rul_data)
            clt_dict, clt_data = dm.get_table(
                self.clientComboBox.currentText())
            client_dict = {}  # 映射关系:客户编号->该客户对应行
            for row in clt_data:
                client_dict[row[clt_dict['客户编号']]] = row
            sht2_head, sht2 = dm.get_table(self.priceComboBox.currentText())
            price = []
            for row in sht2:
                price.append([
                    row[sht2_head['编号']], row[sht2_head['指导单价(未税)\n元/KG']],
                    row[sht2_head['备注']], row[sht2_head['出货开始时间']],
                    row[sht2_head['出货结束时间']]
                ])
            sht4_head, sht4 = dm.get_table(self.managerComboBox.currentText())
            slr_dict, slr_data = dm.get_table(
                self.aftersalesComboBox.currentText())
            places = []  # 售后员表中的地点名
            for row in slr_data:
                if row[1] != 'None':
                    places.append([row[1], row[5], row[6]])
            self.genProgressBar.setValue(10)

            # 每次增加的进度=剩余进度(90)/每个表的阶段数(3)/要生成的表数
            piece = 30 / len([
                x
                for x in [self.cmsLineEdit.text(),
                          self.asCmsLineEdit.text()] if x != ''
            ])
            if self.cmsLineEdit.text() != '':
                bs = Bonus(price)
                h1, r1, r2 = bs.calc_commission(src_dict, src_data, clt_dict,
                                                client_dict, rul_dict,
                                                rul_data, places, sht4)
                self.genProgressBar.setValue(self.genProgressBar.value() +
                                             piece)
                # 下面的h1应该是与ExcelCheck.headers["业务员提成明细"]完全相同的
                dm.create_table("业务员提成明细", self.cmsLineEdit.text(), h1)
                self.genProgressBar.setValue(self.genProgressBar.value() +
                                             piece)
                dm.insert_data(self.cmsLineEdit.text(), h1, r1)
                self.genProgressBar.setValue(self.genProgressBar.value() +
                                             piece)
                self.generateCmsSignal.emit(self.cmsLineEdit.text())
                self.cmsLineEdit.setText("")  # 清空表名
            if self.asCmsLineEdit.text() != '':
                after_sales = AfterSales(slr_dict, slr_data)
                as_header, as_content = after_sales.calc_commission(
                    src_dict, src_data, clt_dict, client_dict, calc_ratio)
                self.genProgressBar.setValue(self.genProgressBar.value() +
                                             piece)
                # 下面的as_header应该是与ExcelCheck.headers["售后员提成明细"]完全相同的
                dm.create_table("售后员提成明细", self.asCmsLineEdit.text(),
                                as_header)
                self.genProgressBar.setValue(self.genProgressBar.value() +
                                             piece)
                dm.insert_data(self.asCmsLineEdit.text(), as_header,
                               as_content)
                self.genProgressBar.setValue(self.genProgressBar.value() +
                                             piece)
                self.generateCmsSignal.emit(self.asCmsLineEdit.text())
                self.asCmsLineEdit.setText("")  # 清空表名
            self.cmsTableGenPushButton.setEnabled(True)
            self.tableManagePushButton.setEnabled(True)
            self.tableViewPushButton.setEnabled(True)
        except Exception as e:
            logging.exception(e)
            QMessageBox.warning(self, "生成出错", str(e))