def addPurchase():
conn = MyHelper()
_json = request.json
params = []
sqls = []
companyId = _json.get('companyId')
purchases = _json.get('purchases')
provideNo = _json.get('supplierId')
date = _json.get('date')
id = str(uuid.uuid3(uuid.NAMESPACE_OID, str(time.time())))
print(purchases)
for puchase in purchases:
goodsId = puchase['id']
print(goodsId)
goodsName = puchase['name']
print(goodsName)
number = puchase['buyNum']
print(number)
purchasePrice = puchase['price']
print(purchasePrice)
params.append([id, goodsId, goodsName, provideNo, companyId, number, purchasePrice, date, "运"])
sqls.append(
"insert into Purchase (id,goodId, goodName, supplierId, companyId, number, purchasePrice, date,status) VALUES (%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 insert_voucher(self, data):
"""
插入新的凭证，包括其各个分录
:param data: 字典类型，date: 凭证日期，voucher_no：凭证编号, attachments_number：凭证附件数, entries: 凭证分录数组
entries每一个元素仍为字典, abstract: 分录摘要, subject_code: 分录科目代码, credit_debit：分录金额为"借"/"贷"
total: 分录总金额
:return: tuple类型, 第一个元素表示是否插入成功, 若成功第二个元素则返回所有数据, 否则第二个元素返回错误信息
"""
conn = MyHelper()
if (not all([
data.get('date'),
data.get('voucher_no'),
data.get('attachments_number') is not None,
data.get('entries')
])) or (data.get('entries') and len(data.get('entries')) == 0):
return False, '凭证信息不全'
sqls = [
"insert into general_voucher (date, voucher_no, attachments_number) "
"values (%s, %s, %s)"
]
params = [[
data.get('date'),
data.get('voucher_no'),
data.get('attachments_number')
]]
for entry in data.get('entries'):
if not all([
entry.get('abstract'),
entry.get('subject_code'),
entry.get('credit_debit'),
entry.get('total')
]):
return False, '分录信息不全'
sqls.append(
"insert into voucher_entry (voucher_no, abstract, subject_code, credit_debit, total) "
"values (%s, %s, %s, %s, %s)")
params.append([
data.get("voucher_no"),
entry.get('abstract'),
entry.get('subject_code'),
entry.get('credit_debit'),
entry.get('total')
])
rows = conn.executeUpdateTransaction(sqls=sqls, params=params)
if rows:
new_data = self.general_voucher_to_dict(
self.query_voucher({'voucher_no': data.get('voucher_no')}))[0]
new_data.update({
'entries':
self.voucher_entry_to_dict(
self.query_voucher_entries(
{'voucher_no': data.get('voucher_no')}) or [])
})
return True, new_data
else:
return False, '凭证编号重复或凭证及分录信息有误'
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 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_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 update_voucher(self, voucher_no, data):
"""
更新凭证信息
:param voucher_no: 所更新的凭证的凭证编号
:param data: 同insert_voucher的参数，general_voucher所有字段选填；entries字段若存在则表示
更新后的凭证的所有分录
:return: tuple类型，第一个元素表示是否更新成功。若成功，则第二、第三个元素分别代表更新前的信息和更新后的信息；
否则，第二个元素返回出错信息
"""
if not voucher_no:
return False, "缺少凭证号参数"
conn = MyHelper()
voucher = self.query_voucher({'voucher_no': voucher_no})
if not voucher:
return False, '凭证号错误，找不到该凭证'
old_data = self.general_voucher_to_dict(voucher)[0]
# new_data = old_data.copy()
entries = self.query_voucher_entries({'voucher_no': voucher_no})
if not entries:
entries = []
old_data['entries'] = self.voucher_entry_to_dict(entries)
sqls = []
params = []
# 若数据中关于凭证的其他字段不为空，则更新凭证
if not (data.get('voucher_no') is None and data.get('date') is None and data.get('record_date') is None \
and data.get('attachments_number') is None and data.get('checked') is None):
sql = "update general_voucher set "
param = []
if data.get('voucher_no') is not None:
sql += "voucher_no = %s, "
param.append(data.get('voucher_no'))
# new_data['voucher_no'] = data.get('voucher_no')
voucher_no = data.get('voucher_no')
if data.get('date') is not None:
sql += "date = %s, "
param.append(data.get('date'))
# new_data['date'] = data.get('date')
if data.get('record_date') is not None:
sql += "record_date = %s, "
param.append(data.get('record_date'))
# new_data["record_date"] = data.get('record_date')
if data.get('attachments_number') is not None:
sql += "attachments_number = %s, "
param.append(data.get('attachments_number'))
# new_data['attachments_number'] = data.get('attachments_number')
if data.get('checked') is not None:
sql += "checked = %s, "
param.append(data.get('checked'))
# new_data['checked'] = data.get('checked')
sql += "voucher_no = voucher_no where voucher_no = %s"
param.append(voucher_no)
sqls.append(sql)
params.append(param)
# 若数据中含有分录字段，则更新分录，采用先全部删除，后进行添加的方式
if data.get('entries') and len(data.get('entries')):
sqls.append("delete from voucher_entry where voucher_no = %s")
params.append([voucher_no])
for entry in data.get('entries'):
if not all([
entry.get('abstract'),
entry.get('subject_code'),
entry.get('credit_debit'),
entry.get('total')
]):
return False, '分录信息不全'
sqls.append(
"insert into voucher_entry (voucher_no, abstract, subject_code, credit_debit, total) "
"values (%s, %s, %s, %s, %s)")
params.append([
voucher_no,
entry.get('abstract'),
entry.get('subject_code'),
entry.get('credit_debit'),
entry.get('total')
])
# new_data['entries'] = data.get('entries')
rows = conn.executeUpdateTransaction(sqls=sqls, params=params)
if rows:
new_data = self.general_voucher_to_dict(
self.query_voucher({'voucher_no': voucher_no}))[0]
new_data.update({
'entries':
self.voucher_entry_to_dict(
self.query_voucher_entries({'voucher_no': voucher_no})
or [])
})
return True, old_data, new_data
else:
return False, '凭证信息错误或重复，更新无效'
