def del_permission_by_features(self, account, features):
connection = MyHelper()
# 已有的权限
_features = self.query_permission(account)
for feature in features:
if (feature,) in _features:
connection.executeUpdate("delete from Permission where feature=%s and account = %s",
[feature, account])
return
def add_permission_by_features(self, account, features):
connection = MyHelper()
# 已有的权限
_features = self.query_permission(account)
# print(_features)
for feature in features:
# print(features)
if (feature,) not in _features:
connection.executeUpdate("insert into Permission (feature, account) VALUES (%s,%s)",
[feature, account])
return
def insert_voucher_attachment(self, data):
"""
插入凭证附件记录
:param data: 插入的数据，必需字段有
voucher_no: 凭证号
attachment_url: 附件url
非必需字段
for_voucher: 是否为凭证预览图
:return: tuple类型, 插入是否成功，以及插入成功后查询的数据或出错信息
"""
if not all([data.get('voucher_no', 'attachment_url')]):
return False, '缺少必要参数：凭证号或附件的url'
conn = MyHelper()
params = [data.get('voucher_no'), data.get('attachment_url')]
if data.get('for_voucher') is not None:
sql = "insert into voucher_attachment(voucher_no, attachment_url, for_voucher) values (%s, %s, %s)"
params.append(data.get('for_voucher'))
else:
sql = "insert into voucher_attachment(voucher_no, attachment_url) values (%s, %s)"
rows = conn.executeUpdate(sql, params)
if rows:
return True, self.voucher_attachment_to_dict(
self.query_voucher_attachments(data))[0]
else:
return False, '添加凭证附件失败，信息错误'
def add(self, id, name, site, companyId):
connection = MyHelper()
row = connection.executeUpdate(
'insert into Warehouse(id, \
name, site,companyId) \
values (%s,%s,%s,%s)', [id, name, site, companyId])
return row
def delete_subject_balance(self, cond={}):
"""
删除科目余额信息（一般用于删除为空的科目余额信息）
:param cond: dict类型，作为删除余额信息的查询条件，可选字段有——
time: 科目期数
subject_code: 科目代码
:return: tuple类型，第一个元素为操作是否成功，第二个参数为所删除的数据
"""
cond = cond or {}
bals = self.query_subject_balance(cond)
conn = MyHelper()
if len(bals):
sql = "delete from accounting_subjects_balance where 1=1"
param = []
if cond.get('time'):
sql += " and time = %s"
param.append(cond.get('time'))
if cond.get('subject_code'):
sql += " and subject_code = %s"
param.append(cond.get('subject_code'))
rows = conn.executeUpdate(sql, param)
if rows:
return True, bals
else:
return False, '删除科目余额信息失败'
else:
return False, '找不到相关科目余额信息'
def insert_subject(self, data):
"""
新增明细科目
:param data: 字典类型，明细科目的必需数据——subject_code, name,
superior_subject_code, type（最后两个其中一个可为空），type_detail（可无）
:return: True, ——插入成功
False, errMsg——插入失败，错误信息
"""
conn = MyHelper()
if not all([data.get('subject_code'), data.get('name'), data.get('superior_subject_code')])\
and not all([data.get('subject_code'), data.get('name'), data.get('type')]):
return False, '科目信息不完整'
if data.get('superior_subject_code'):
# 若新增明细科目
# 根据上级科目的科目代码获取该明细科目的类别和具体类别
subject = self.query_subject({'subject_code': data.get('superior_subject_code')})
if subject and len(subject):
subject_dict = self.accounting_subject_to_dict(subject[:1])[0]
data['type'] = subject_dict.get('type')
data['type_detail'] = subject_dict.get('type_detail')
else:
return False, '上级科目不存在'
if conn.executeUpdate(
sql="insert into accounting_subjects(subject_code, name, superior_subject_code, type, type_detail)"
" values(%s, %s, %s, %s, %s)",
param=[data.get('subject_code'), data.get('name'), data.get('superior_subject_code'), data.get('type'),
data.get('type_detail')]
):
return True, data
else:
return False, '科目代码重复或其他信息不正确'
def delete_voucher_attachment(self, voucher_no, cond={}):
"""
删除凭证附件信息
:param voucher_no: 附件对应的凭证的编号
:param cond: 删除的限制条件，可有字段为 attachment_url和for_voucher
:return: 是否删除成功以及所删除的数据或失败信息
"""
conn = MyHelper()
cond_query = cond.copy()
cond_query['voucher_no'] = voucher_no
old = self.query_voucher_attachments(cond_query)
if not old:
return False, '无相关附件信息'
old = self.voucher_attachment_to_dict(old)
sql = "delete from voucher_attachment where voucher_no = %s"
params = [voucher_no]
if cond.get('attachment_url'):
sql = sql + ' and attachment_url = %s'
params.append(cond.get('attachment_url'))
if cond.get('for_voucher') is not None:
sql = sql + ' and for_voucher = %s'
params.append(cond.get('for_voucher'))
rows = conn.executeUpdate(sql, params)
if rows:
return True, old
else:
return False, '删除附件信息失败'
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 add(self, name, sellprice, companyId, type, unitInfo, barcode):
id = uuid.uuid3(uuid.NAMESPACE_OID, str(time()))
connection = MyHelper()
row = connection.executeUpdate(
"insert into Goods (id, name, sellprice, companyId, type, unitInfo,barcode) VALUES (%s,%s,%s,%s,%s,%s,%s)",
[str(id), name, sellprice, companyId, type, unitInfo, barcode])
res = {"row": row, "id": id.__str__()}
return res
def addKeys(self, account, userId=None, description=None):
keys = BigchainUtils.gen_random_keypair()
print(keys)
connection = MyHelper()
return connection.executeUpdate(
"insert into UserKeys (account, privateKey, publicKey,userId,description)"
" VALUES (%s,%s,%s,%s,%s)",
[account, keys.private_key, keys.public_key, userId, description])
def add(self, id, goodId, goodName, supplierId, companyId, number,
purchasePrice, date, status):
connection = MyHelper()
row = connection.executeUpdate(
"insert into Purchase (id,goodId, goodName, supplierId, companyId, number, purchasePrice, date,status) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)",
[
id, goodId, goodName, supplierId, companyId, number,
purchasePrice, date, status
])
return row
def add(self, id, customerId, goodsId, companyId, number, sumprice, date,
customerName, goodsName, unitInfo):
connection = MyHelper()
row = connection.executeUpdate(
"insert into Sell (id,customerId, goodsId, companyId, number, sumprice,date,customerName,goodsName,unitInfo) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
[
id, customerId, goodsId, companyId, number, sumprice, date,
customerName, goodsName, unitInfo
])
return row
def add(self, id, name, phone, site, taxpayerNumber, bankaccount, bankname,
companyId):
conn = MyHelper()
row = conn.executeUpdate(
"insert into Supplier (id, name, phone,site,taxpayerNumber,bankaccount,bankname,companyId) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)",
[
id, name, phone, site, taxpayerNumber, bankaccount, bankname,
companyId
])
return row
def add_receive(self, sellId, amount, date, bank_name, clear_form):
connection = MyHelper()
_id = str(uuid.uuid3(uuid.NAMESPACE_OID, str(time.time())))
rows = connection.executeQuery(
"select companyId from Sell where id = %s", [sellId])
row = connection.executeUpdate(
"insert into Receive (id, sellId, receive, date, companyId,bankName,clearForm)"
" values (%s,%s,%s,%s,%s,%s,%s)",
[_id, sellId, amount, date, rows[0][0], bank_name, clear_form])
res = {"row": row, "id": _id.__str__()}
return res
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 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 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 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 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 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 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 add(self, id, name, place):
conn = MyHelper()
row = conn.executeUpdate(
"insert into Company (id, name, place) VALUES (%s,%s,%s)",
[id, name, place])
return row
def add(self, yesterMoney, changeExplain, nowMoney, changeAmount, date):
conn = MyHelper()
row = conn.executeUpdate(
"insert into Dailyfund (yesterMoney, changeExplain, nowMoney,changeAmount,date) VALUES (%s,%s,%s,%s,%s)",
[yesterMoney, changeExplain, nowMoney, changeAmount, date])
return row
def InsertToday(self, changeExplain, nowMoney, changeAmount, date):
conn = MyHelper()
row = conn.executeUpdate("update Dailyfund set changeExplain=%s,nowMoney=%s,changeAmount=%s where date =%s",
[changeExplain, nowMoney, changeAmount, date])
return row
def add(self, id, name, phone, credit, companyId, bankName, bankAccount):
conn = MyHelper()
row = conn.executeUpdate(
"insert into Customer (ID, name, phone,credit,companyId,bankName,bankAccount) VALUES (%s,%s,%s,%s,%s,%s,%s)",
[id, name, phone, credit, companyId, bankName, bankAccount])
return row
def update_info(self, _id, name, sellprice, _type, unitInfo, barcode):
connection = MyHelper()
row = connection.executeUpdate(
"update Goods set name = %s, sellprice=%s,type=%s,unitInfo=%s,barcode=%s "
"where id = %s", [name, sellprice, _type, unitInfo, barcode, _id])
return row
def update_photo(self, _id, photo):
connection = MyHelper()
row = connection.executeUpdate(
"update Goods set photo = %s where id = %s", [photo, _id])
return row
def check_receive(self, _id):
connection = MyHelper()
connection.executeUpdate("update Receive set status = 1 where id=%s",
[_id])
return self.query_receive(_id)
def check_payment(self, _id):
connection = MyHelper()
connection.executeUpdate("update Payment set status = 1 where id=%s",
[_id])
return self.query_payment(_id)
