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, '凭证信息错误或重复,更新无效'