def add_sell_receive(self, sellId, reason): connection = MyHelper() rows = connection.executeQuery( "select customerId,sumprice,companyId,Sell.date" " from Sell where id = %s", [sellId]) total = 0 for row in rows: total = total + row[1] return connection.executeUpdate( "insert into SellReceive (customerId, sellId, total, reason, date, companyId)" "values (%s,%s,%s,%s,%s,%s)", [rows[0][0], sellId, total, reason, rows[0][3], rows[0][2]])
def add_purchase_pay(self, purchaseId, reason): connection = MyHelper() rows = connection.executeQuery( "select Purchase.supplierId,Purchase.companyId,Purchase.purchasePrice,Purchase.number,Purchase.date " "from Purchase where Purchase.id = %s", [purchaseId]) total = 0 for row in rows: total = total + row[2] * row[3] return connection.executeUpdate( "insert into PurchasePayment (supplierId, purchaseId, total, reason, date, companyId)" "values (%s,%s,%s,%s,%s,%s)", [rows[0][0], purchaseId, total, reason, rows[0][4], rows[0][1]])
def add_payment(self, purchaseId, amount, date, bank_name, clear_form): connection = MyHelper() _id = str(uuid.uuid3(uuid.NAMESPACE_OID, str(time.time()))) rows = connection.executeQuery( "select companyId from Purchase where id = %s", [purchaseId]) row = connection.executeUpdate( "insert into Payment (id, purchaseId, pay, date, companyId,bankName,clearForm)" " values (%s,%s,%s,%s,%s,%s,%s)", [_id, purchaseId, amount, date, rows[0][0], bank_name, clear_form]) res = {"row": row, "id": _id.__str__()} return res
def test8(self): conn = MyHelper() name = "嘉" companyId = "5" input = '%' + name + '%' # and name like / % " + name + " / % " result = conn.executeQuery( "select * from Customer where companyId = %s and name LIKE %s ", [companyId, input]) size = len(result) print(size) print(result)
def CompanyRegister(): if request.method == 'GET': return render_template("RegisterCompany.html") else: companyname = request.form.get("companyname") place = request.form.get("place") helper = MyHelper() id = str(uuid.uuid3(uuid.NAMESPACE_OID, companyname)) row = helper.executeUpdate("insert into Company (id, name, place) values (%s,%s,%s)", [id, companyname, place]) if row == 1: return render_template("RegisterCompany.html") else: return False
def query_purchase_pay_remain(self, purchaseId): connection = MyHelper() rows = connection.executeQuery( "select pay from Payment where purchaseId = %s", [purchaseId]) total = 0 if len(rows) == 0: pass else: for row in rows: total = total + row[0] rows = connection.executeQuery( "select total from PurchasePayment where purchaseId = %s", [purchaseId]) return rows[0][0] - total
def query_by_companyId(self, companyId, name, type, id): _param = [companyId] _sql = "select * from Goods where companyId = %s" if id: _sql += " and id= %s" _param.append(id) if name: _sql += " and name like %s" _param.append('%' + name + '%') if type: _sql += " and type = %s" _param.append(type) connection = MyHelper() return connection.executeQuery(_sql, _param)
def query_sell_receive_remain(self, sellId): connection = MyHelper() # 目前收到的金额 rows = connection.executeQuery( "select receive from Receive where sellId = %s", [sellId]) total = 0 if len(rows) == 0: pass else: for row in rows: total = total + row[0] # 期望收到的金额 rows = connection.executeQuery( "select total from SellReceive where sellId = %s", [sellId]) return rows[0][0] - total
def query_times(self, year=None): """ 查询最小和最大的期 :param year: 如果传入年份则查询某一年最小和最大的期 :return: tuple, 第一个元素为最小的期,第二个为最大的期 """ conn = MyHelper() sql = "select MIN(time), MAX(time) from accounting_subjects_balance" params = [] if year: sql += " where FIND_IN_SET(%s, time) = 0" params.append(str(year)) rows = conn.executeQuery(sql, params) if rows: return rows[0][0], rows[0][1]
def out(self, company_id, sell_id, out_list): connection = MyHelper() _sql = [] _params = [] for goods in out_list: goods_id = goods.get('goodsId') warehouse_id = goods.get('wareHouseId') number = goods.get('number') _sql.append( 'update GoodsStore set number = number - %s where companyId = %s ' 'and wareId = %s and goodsId=%s') _params.append([number, company_id, warehouse_id, goods_id]) _sql.append('update Sell set status = 1 where id = %s') _params.append([sell_id]) return connection.executeUpdateTransaction(_sql, params=_params)
def query_all_types(self): """ 查询所有科目类别 :return: list类型,包含所有类别(字符串) """ conn = MyHelper() rows = conn.executeQuery( "select type from accounting_subjects " "group by type " "order by min(subject_code)" ) if rows: result = [] for row in rows: result.append(row[0]) return result
def insert_subject_balance(self, data): """ 插入科目余额记录 :param data: dict类型,time subject_code必填, opening_balance credit debit选填 opening_balance不填默认为上一期期末余额或0, credit debit 不填默认0 :return: tuple类型,第一个元素为是否插入成功,第二个元素为错误时的错误信息或成功时插入的数据 """ if not all([data.get('time'), data.get('subject_code')]): return False, "科目余额添加失败:缺少必要参数:期数和科目代码" conn = MyHelper() sql = "insert into accounting_subjects_balance(time, subject_code, opening_balance, credit, debit) " \ "values(%s, %s, %s, %s, %s)" params = [] if data.get('opening_balance') is None: last = int(data.get('time')) - 1 if last % 100 == 0: last = last - 100 + 12 last_balance = self.query_subject_balance_by_time_range({ 'subject_code': data.get('subject_code')}, up=str(last)) if not len(last_balance): # 不存在前一期的科目余额 data['opening_balance'] = 0 else: # 将期初余额设置为前一期的期末余额 last_balance = last_balance[0] data['opening_balance'] = last_balance.get('closing_balance') params.append(data.get('time')) params.append(data.get('subject_code')) params.append(data.get('opening_balance')) params.append(data.get('credit') or 0) params.append(data.get('debit') or 0) rows = conn.executeUpdate(sql, params) if rows: new_data = self.query_subject_balance({'time': data.get('time'), 'subject_code': data.get('subject_code')})[0] # 更新该期之后每一期的期初余额 conn.executeUpdate( sql="update accounting_subjects_balance " "set opening_balance = opening_balance + %s " "where subject_code = %s and time > %s", param=[new_data.get('closing_balance')-new_data.get('opening_balance'), data.get('subject_code'), data.get('time')] ) return True, new_data else: return False, '科目余额添加失败:信息有误'
def update_subject(self, subject_code, data): """ 更新科目信息 :param subject_code: 所更新的科目的科目代码 :param data: 用于更新的新数据,字段同accounting_subjects的字段(除superior_subject_code),均为选填 :return: tuple类型,第一个返回值为是否更新成功。若成功,则第二、三个返回值分别为更新前的数据和更新后的数据; 若失败,第二个返回值返回错误信息 """ if not subject_code: return "缺少科目代码参数" conn = MyHelper() subject = self.query_subject({'subject_code': subject_code}) if not subject: return False, "科目代码出错,找不到该科目" old_data = self.accounting_subject_to_dict(subject)[0] new_data = old_data.copy() if not all([not data.get('subject_code'), not data.get('name'), not data.get('type'), not data.get('type_detail')]): sql = "update accounting_subjects set " param = [] if data.get('subject_code'): sql += "subject_code = %s " param.append(data.get('subject_code')) new_data['subject_code'] = data.get('subject_code') if data.get('name'): sql += "name = %s " param.append(data.get('name')) new_data['name'] = data.get('name') if data.get('type'): sql += "type = %s " param.append(data.get('type')) new_data['type'] = data.get('type') if data.get('type_detail'): sql += "type_detail = %s " param.append(data.get('type_detail')) new_data['type_detail'] = data.get('type_detail') sql += "where subject_code = %s" param.append(subject_code) row = conn.executeUpdate(sql, param) if row: return True, old_data, new_data else: return False, '科目信息错误,更新失败' else: return False, '缺少科目更新后的数据'
def query_backorder_goods(self, limit, offset): print(limit, offset) return MyHelper().executeQuery( "select g.name, gs.number, g.photo" " from Goods as g, GoodsStore as gs" " where g.id = gs.goodsId and gs.wareId = 1" " ORDER BY gs.number" " limit %s offset %s", param=[limit, offset])
def query_sell_well_goods(self, low, high, limit, offset): return MyHelper().executeQuery( "select Goods.name, sells.num, Goods.photo from" " (select goodsId, SUM(number) as num from Sell" " where date >= %s and date <= %s" " group by goodsId" " order by num desc" " limit %s offset %s) sells, Goods" " where Goods.id = sells.goodsId", param=[low, high, limit, offset])
def delete_subject(self, subject_code): """ 删除科目 :param subject_code: 索要删除科目的科目代码 :return: tuple类型,第一个结果返回是否删除成功。成功时第二个返回所删除的科目信息;失败时返回错误信息 """ row = self.query_subject({'subject_code': subject_code}) if row: conn = MyHelper() res = conn.executeUpdate( sql="delete from accounting_subjects where subject_code = %s", param=[subject_code] ) if res: return True, row else: return False, '科目已有明细科目,不能直接删除' else: return False, '找不到该科目'
def addSell(): conn = MyHelper() queryCustomer = CustomerDao() queryGoods = GoodsDao() _json = request.json params = [] sqls = [] companyId = _json.get('companyId') customerId = _json.get('customerId') result = queryCustomer.query_byId(customerId) if len(result) == 1: customerName = result[0][1] else: customerName = "" date = _json.get('date') goodsList = _json.get('goodsList') id = str(uuid.uuid3(uuid.NAMESPACE_OID, str(time.time()))) # print(goodsList) for puchase in goodsList: sumprice = puchase['sumprice'] goodsId = puchase['goodsId'] number = puchase['number'] goodsResult = queryGoods.query_byId(goodsId) if len(goodsResult) == 1: goodsName = goodsResult[0][1] goodsUnit = goodsResult[0][5] else: goodsName = "" params.append([ id, customerId, goodsId, companyId, number, sumprice, date, customerName, goodsName, goodsUnit ]) sqls.append( "insert into Sell (id,customerId, goodsId, companyId, number, sumprice,date,customerName,goodsName,unitInfo) " "values (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s)") rows = conn.executeUpdateTransaction(sqls=sqls, params=params) if rows: return json.dumps(return_success(id)) else: return json.dumps(return_unsuccess('Error: Add failed'))
def update_voucher_attachment(self, voucher_no, attachment_url, data): """ 更新凭证附件的信息 :param voucher_no: 需要更新的凭证的凭证号 :param attachment_url: 需要更新附件的原url :param data: 更新的数据 :return: tuple类型,更新是否成功,以及更新前和更新后的数据或出错信息 """ conn = MyHelper() old = self.query_voucher_attachments({ 'voucher_no': voucher_no, 'attachment_url': attachment_url }) if not old: return False, '无相关附件信息' old = self.voucher_attachment_to_dict(old)[0] sql = "update voucher_attachment set" params = [] if data.get('attachment_url'): sql = sql + " attachment_url = %s," params.append(data.get('attachment_url')) if data.get('for_voucher'): sql = sql + ' for_voucher = %s,' params.append(data.get('for_voucher')) sql = sql + ' voucher_no = voucher_no where voucher_no = %s and attachment_url = %s' params.extend([voucher_no, attachment_url]) rows = conn.executeUpdate(sql, params) if rows: return True, old, self.voucher_attachment_to_dict( self.query_voucher_attachments({ 'voucher_no': voucher_no, 'attachment_url': attachment_url }))[0] else: return False, '附件信息更新失败,信息有误'
def query_voucher_attachments(self, cond={}): """ 查询凭证附件信息 :param cond: 查询条件 字段attachment_url: 附件url 字段for_voucher: 是否为凭证的预览图 其余字段与general_voucher表中相同 :return: list类型,每一个元素为两表连接后的所有字段形成的字典 """ cond = cond or {} conn = MyHelper() sql = "select * from voucher_attachment natural join general_voucher where 1=1" params = [] if cond.get('date'): sql += " and date = %s" params.append(cond.get('date')) if cond.get('record_date'): sql += " and record_date = %s" params.append(cond.get('record_date')) if cond.get('voucher_no'): sql += " and voucher_no = %s" params.append(cond.get('voucher_no')) if cond.get('attachments_number'): sql += " and attachments_number = %s" params.append(cond.get('attachments_number')) if cond.get('checked'): sql += " and checked = %s" params.append(cond.get('checked')) if cond.get('attachment_url'): sql += " and attachment_url = %s" params.append(cond.get('attachment_url')) if cond.get('for_voucher'): sql += " and for_voucher = %s" params.append(cond.get('for_voucher')) sql = sql + " order by date desc, voucher_no asc, for_voucher desc" return conn.executeQuery(sql, params)
def query_voucher_by_date_range(self, cond, low=None, up=None): """ 根据日期范围查询凭证 :param cond: 其他限制条件 :param low: 日期下界 >= :param up: 日期上界 <= :return: 同query_voucher """ conn = MyHelper() params = [] if low and up and low > up: return sql = "select * from general_voucher where 1 = 1" cond = cond or {} if cond.get('record_date'): sql += " and record_date = %s" params.append(cond.get('record_date')) if cond.get('voucher_no'): sql += " and voucher_no = %s" params.append(cond.get('voucher_no')) if cond.get('attachments_number'): sql += " and attachments_number = %s" params.append(cond.get('attachments_number')) if cond.get('checked'): sql += " and checked = %s" params.append(cond.get('checked')) if low: sql += " and date >= %s" params.append(low) if up: sql += " and date <= %s" params.append(up) sql += ' order by date desc, voucher_no asc' return conn.executeQuery(sql=sql, param=params)
def query_by_warehouse(self, companyId, wareHouseId=None, name=None, _type=None): connection = MyHelper() _sql = "select Goods.id, Goods.name, Goods.type, Goods.sellprice," \ "Goods.unitInfo,sum(GoodsStore.number), Goods.photo " \ "from Goods, GoodsStore " \ "where Goods.id=GoodsStore.goodsId and Goods.companyId = %s" _param = [companyId] if name: _sql += " and Goods.name like %s" _param.append('%' + name + '%') if _type: _sql += " and Goods.type like %s" _param.append('%' + _type + '%') if wareHouseId: _sql += " and GoodsStore.wareId = %s" _param.append(wareHouseId) _sql += " group by Goods.id" return connection.executeQuery(_sql, _param)
def query_purchase_pay(self, purchaseId, days): _param = [] _sql = "select * from PurchasePayment where 1 = 1 " if purchaseId: _sql += " and purchaseId = %s" _param.append(purchaseId) if days: delta = datetime.timedelta(days=days) now = datetime.datetime.now() _date = now - delta _sql += " and date >= %s and date <=%s" _param.append(_date) _param.append(now) _sql += ' order by date DESC' connection = MyHelper() rows = connection.executeQuery(_sql, _param) res = [] for row in rows: remain = self.query_purchase_pay_remain(row[1]) lis = list(row) lis.append(remain) res.append(lis) return res
def query_sell_receive(self, sellId, days): _param = [] _sql = "select * from SellReceive where 1 = 1 " if sellId: _sql += " and sellId = %s" _param.append(sellId) if days: delta = datetime.timedelta(days=days) now = datetime.datetime.now() _date = now - delta _sql += " and date >= %s and date <=%s" _param.append(_date) _param.append(now) _sql += ' order by date DESC' connection = MyHelper() rows = connection.executeQuery(_sql, _param) res = [] for row in rows: remain = self.query_sell_receive_remain(row[1]) lis = list(row) lis.append(remain) res.append(lis) return res
def delete_voucher(self, voucher_no): """ 删除凭证及其分录 :param voucher_no: 所删除的凭证的凭证编号 :return: tuple类型,第一个参数为操作是否正确,第二个参数在正确时表为所删除的数据,失败时为错误信息 """ voucher = self.query_voucher({'voucher_no': voucher_no}) if not voucher: return False, '凭证号错误,找不到该凭证' old_data = self.general_voucher_to_dict(voucher)[0] entries = self.query_voucher_entries({'voucher_no': voucher_no}) if not entries: entries = [] old_data['entries'] = self.voucher_entry_to_dict(entries) # 数据库中已定义 on delete cascade,分录无需重复删除 conn = MyHelper() if conn.executeUpdate( sql='delete from general_voucher where voucher_no = %s', param=[voucher_no]): return True, old_data else: return False, '出现未知错误,找不到该凭证'
def storage(self, companyId, purchaseId, wareHouseId): connection = MyHelper() try: in_goods = connection.executeQuery( "select * from Purchase where companyId = %s and id = %s", [companyId, purchaseId]) connection.executeUpdate( "update Purchase set status = '到' where companyId = %s and id = %s", [companyId, purchaseId]) for in_good in in_goods: connection.executeUpdate( "insert into GoodsStore (goodsId, wareId, companyId, number) VALUES (%s,%s,%s,%s) " "on duplicate key update " "number = number + %s", [ in_good[1], wareHouseId, companyId, in_good[5], in_goods[5] ]) return True except Exception as e: print(e) return False
def query_receive(self, _id, sellId=None, days=None): _param = [] _sql = "select * from Receive where 1 = 1" if _id: _sql += " and id = %s" _param.append(_id) connection = MyHelper() return connection.executeQuery(_sql, _param) if sellId: _sql += " and purchaseId = %s" _param.append(sellId) if days: delta = datetime.timedelta(days=days) now = datetime.datetime.now() _date = now - delta _sql += " and date >= %s and date <=%s" _param.append(_date) _param.append(now) _sql += ' order by date DESC' connection = MyHelper() return connection.executeQuery(_sql, _param)
def query_public_key(self, _id): connection = MyHelper() return connection.executeQuery( "select * from UserKeys where account = %s", [_id])
def update_subject_balance(self, data): """ 更新科目余额信息 :param data: list 类型,每个元素为包含查询条件以及更新的数据的字典,字段组成: time *: 期, subject_code *: 科目代码, opening_balance: 期初余额, { way, value } credit: 借方金额变动{ way, value }, debit: 贷方金额变动{ way, value }, way: set / update,表示设定或更新 :return: tuple, 第一个元素表示是否成功,第二个元素是成功时的更新前数据或失败时的信息,第三个元素是新数据 """ sqls = [] params = [] conn = MyHelper() olds = [] news = [] for d in data: if not all([d.get('time'), d.get('subject_code')]): return False, "科目余额更新失败:缺少必要参数:期数和科目代码" old = self.query_subject_balance(cond={'subject_code': d.get('subject_code'), 'time': d.get('time')}) if not len(old): return False, "科目({})余额更新失败:查不到该科目余额信息".format(d.get('subject_code')) # 原科目余额的期末余额 closing_delta = 0 sql_update_cur = "update accounting_subjects_balance set " params_cur = [] credit = d.get('credit') debit = d.get('debit') opening_balance = d.get('opening_balance') # 更新相应字段并重新计算期末余额 if credit: if credit.get('way') == 'set': sql_update_cur += "credit = %s, " params_cur.append(credit.get('value')) if old[0].get('credit_debit') == '借': closing_delta += credit.get('value') - old[0].get('credit') else: closing_delta -= credit.get('value') - old[0].get('credit') else: sql_update_cur += "credit = credit + %s, " params_cur.append(credit.get('value')) if old[0].get('credit_debit') == '借': closing_delta += credit.get('value') else: closing_delta -= credit.get('value') if debit: if debit.get('way') == 'set': sql_update_cur += "debit = %s, " params_cur.append(debit.get('value')) if old[0].get('credit_debit') == '贷': closing_delta += debit.get('value') - old[0].get('debit') else: closing_delta -= debit.get('value') - old[0].get('debit') else: sql_update_cur += "debit = debit + %s, " params_cur.append(debit.get('value')) if old[0].get('credit_debit') == '贷': closing_delta += debit.get('value') else: closing_delta -= debit.get('value') if opening_balance: if opening_balance.get('way') == 'set': sql_update_cur += "opening_balance = %s, " params_cur.append(opening_balance.get('value')) closing_delta += opening_balance.get('value') - old[0].get('opening_balance') else: sql_update_cur += "opening_balance = opening_balance + %s, " params_cur.append(opening_balance.get('value')) closing_delta += opening_balance.get('value') sql_update_cur += "time = time where time = %s and subject_code = %s" params_cur.append(d.get('time')) params_cur.append(d.get('subject_code')) olds.append(old[0]) # 用于更新当前期之后所有期的期初余额的sql语句 sql_update_later = "update accounting_subjects_balance set opening_balance = opening_balance + %s " \ "where subject_code = %s and time > %s" param_later = [closing_delta, d.get('subject_code'), d.get('time')] sqls.extend([sql_update_cur, sql_update_later]) params.extend([params_cur, param_later]) rows = conn.executeUpdateTransaction(sqls=sqls, params=params) if rows: for bal in olds: news.extend(self.query_subject_balance({ 'subject_code': bal.get('subject_code'), 'time': bal.get('time') })) return True, olds, news else: return False, '科目余额更新失败:信息有误'
def query_subject_balance_by_time_range(self, cond={}, low=None, up=None): """ 根据时间范围查询科目余额 :param cond: 查询科目的限定条件 :param low: 时间下界(大于等于) :param up: 时间上界(小于等于) :return: dict类型,同query_subject_balance """ if not low and not up: return self.query_subject_balance(cond) if low and up and low > up: return rows = self.query_subject(cond) if rows: # 所查询科目存在 conn = MyHelper() cond = cond or {} subjects = self.accounting_subject_to_dict(rows) res = [] for subject in subjects: rows_sub = self.query_subject({'superior_subject_code': subject.get('subject_code')}) if rows_sub: # 科目有子科目 # 将数额类值均初始化为0 subject['opening_balance'] = 0 subject['credit'] = 0 subject['debit'] = 0 subject['closing_balance'] = 0 sub_balance = self.query_subject_balance_by_time_range({ 'superior_subject_code': subject.get('subject_code')}, low, up) # 时间字典 time_dict = {} for bal in sub_balance: subject_time = time_dict.get(bal.get('time')) if subject_time is None: # 复制一份添加到字典中 subject_time = time_dict[bal.get('time')] = subject.copy() subject_time['credit_debit'] = bal.get('credit_debit') subject_time['time'] = bal.get('time') subject_time['opening_balance'] += bal.get('opening_balance') subject_time['credit'] += bal.get('credit') subject_time['debit'] += bal.get('debit') subject_time['closing_balance'] += bal.get('closing_balance') # 将字典的值加入返回数据 res.extend(list(time_dict.values())) else: # 科目无子科目 sql = "select * from accounting_subjects natural join accounting_subjects_balance " \ "where subject_code = %s" params = [subject.get('subject_code')] if low: sql += " and time >= %s" params.append(low) if up: sql += " and time <= %s" params.append(up) sql += " order by time desc" res.extend(self.subject_balance_to_dict(list(conn.executeQuery(sql, params) or []))) return res
def query_subject_balance(self, cond): """ 查询科目余额 :param cond: dict类型. 字段time——查询某一期的余额,subject_code——查询科目代码指定的特定科目的余额, 字段name——查询特定名称科目的余额,字段superior_subject_code——查询特定科目的下级科目的余额 字段type——拆线呢特定类别的科目的余额,字段type_detail——查询特定详情类别科目的余额 :return: list of dict. 字段time——期(时间段),subject_code——科目代码,opening_balance——期初余额, credit——期间借方发生金额,debit——期间贷方发生金额,closing_balance——期末余额 """ rows = self.query_subject(cond) if rows: # 所查询科目存在 conn = MyHelper() cond = cond or {} subjects = self.accounting_subject_to_dict(rows) res = [] for subject in subjects: rows_sub = self.query_subject({'superior_subject_code': subject.get('subject_code')}) if rows_sub: # 科目有子科目 # 将数额类值均初始化为0 subject['opening_balance'] = 0 subject['credit'] = 0 subject['debit'] = 0 subject['closing_balance'] = 0 if cond.get('time'): # 限定了期数,则通过递归调用,将所有的子科目余额叠加作为上级科目的科目余额 sub_balance = self.query_subject_balance({'time': cond.get('time'), 'superior_subject_code': subject.get('subject_code')}) if len(sub_balance): subject['time'] = cond.get('time') subject['credit_debit'] = sub_balance[0].get('credit_debit') for bal in sub_balance: subject['opening_balance'] += bal.get('opening_balance') subject['credit'] += bal.get('credit') subject['debit'] += bal.get('debit') subject['closing_balance'] += bal.get('closing_balance') res.append(subject) else: # 未限制期数,存在多期,需要复制多份 sub_balance = self.query_subject_balance({'superior_subject_code': subject.get('subject_code')}) # 时间字典 time_dict = {} for bal in sub_balance: subject_time = time_dict.get(bal.get('time')) if subject_time is None: # 复制一份添加到字典中 subject_time = subject.copy() time_dict[bal.get('time')] = subject_time subject_time['credit_debit'] = bal.get('credit_debit') subject_time['time'] = bal.get('time') subject_time['opening_balance'] += bal.get('opening_balance') subject_time['credit'] += bal.get('credit') subject_time['debit'] += bal.get('debit') subject_time['closing_balance'] += bal.get('closing_balance') # 将字典的值加入返回数据 res.extend(list(time_dict.values())) else: # 科目无子科目 sql = "select * from accounting_subjects natural join accounting_subjects_balance " \ "where subject_code = %s" params = [subject.get('subject_code')] if cond.get('time'): sql += " and time = %s" params.append(cond.get('time')) sql += " order by time desc" res.extend(self.subject_balance_to_dict(list(conn.executeQuery(sql, params) or []))) return res