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
