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)