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_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_voucher(self, cond={}): """ 查询凭证信息(仅限于general_voucher表中信息,不包含凭证分录) :param cond: 查询条件,可以放入任意general_voucher表中已有的字段 :return: tuple,返回地查询结果 """ conn = MyHelper() params = [] sql = "select * from general_voucher where 1 = 1" cond = cond or {} 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')) sql += ' order by date desc, voucher_no asc' return conn.executeQuery(sql=sql, param=params)
def query_subject(self, cond={}): """ 查询科目信息,使用条件严格查询 :param cond: 字典类型,为查询条件, 与返回值字段组成相同,每个字段均可为空 :return: tuple类型,字段名按accounting_subject表的构成排列 """ conn = MyHelper() params = [] sql = "select * from accounting_subjects where 1 = 1" cond = cond or {} if cond.get('subject_code'): sql += " and subject_code = %s" params.append(cond.get('subject_code')) if cond.get('name'): sql += " and name = %s" params.append(cond.get('name')) if cond.get('superior_subject_code'): sql += " and superior_subject_code = %s" params.append(cond.get('superior_subject_code')) if cond.get('type'): sql += " and type = %s" params.append(cond.get('type')) if cond.get('type_detail'): sql += " and type_detail = %s" params.append(cond.get('type_detail')) sql += ' order by subject_code asc' return conn.executeQuery(sql=sql, param=params)
def query_subject_type_rate(self, subject_code): """ 查询科目的类别和其借贷对应为增长或减少 :param subject_code: 科目代码 :return: { 'type': 科目种类, 'credit': 科目借代表的增加或减少 'debit': 科目贷代表的增加或减少 } """ conn = MyHelper() rows = conn.executeQuery( sql="select type, type_detail from accounting_subjects " "where subject_code = %s", param=[subject_code] ) if len(rows): _type = rows[0][0] type_detail = rows[0][1] rate = AccountingSubjectDao.rate_for_subjects_of_types.get(_type) if all([rate, rate.get(type_detail)]): result = {'type': _type, 'rate': rate.get(type_detail)} elif rate: result = {'type': _type, 'rate': rate} return result
def query_voucher_entries(self, cond={}): """ 查询凭证的分录信息 :param cond: 查询条件,可以放入任意voucher_entry表中已有的字段 :return: tuple类型, 查询voucher_entry表链接accounting_subject中直接返回的结果 """ conn = MyHelper() params = [] sql = "select * from voucher_entry natural join accounting_subjects where 1 = 1" if cond.get('voucher_no'): sql += " and voucher_no = %s" params.append(cond.get('voucher_no')) if cond.get('abstract'): sql += " and abstract = %s" params.append(cond.get('abstract')) if cond.get('subject_code'): sql += " and subject_code = %s" params.append(cond.get('subject_code')) if cond.get('credit_debit'): sql += " and credit_debit = %s" params.append(cond.get('credit_debit')) if cond.get('total'): sql += " and total = %s" params.append(cond.get('total')) sql += ' order by voucher_no, subject_code asc' return conn.executeQuery(sql=sql, param=params)
def sellRecommendByUserGoods(self, uid, gid): connection = MyHelper() return connection.executeQuery( """ select customerId,customerName,goodsId,goodsName,date,photo from Sell left join Goods on Sell.goodsId = Goods.id where customerId = %s and goodsId = %s order by date desc limit 1 """, [uid, gid])
def query_store_by_goods_id(self, company_id, goods_id): connection = MyHelper() _sql = "select GoodsStore.wareId,Warehouse.name, GoodsStore.number from GoodsStore,Warehouse" \ " where Warehouse.id=GoodsStore.wareId and goodsId = %s and %s in " \ "(select GoodsStore.goodsId from GoodsStore where GoodsStore.companyId = %s) " \ "and GoodsStore.companyId = %s" _param = [goods_id, goods_id, company_id, company_id] return connection.executeQuery(_sql, _param)
def sellRecommendUser(self): connection = MyHelper() return connection.executeQuery( """select customerId,goodsId,min(t) as T from ( SELECT a.customerId,a.customerName,a.goodsId,a.goodsName,TIMESTAMPDIFF(DAY,b.date,a.date) as t From Sell as a left join Sell as b on a.customerId = b.customerId where DATE (a.date) > DATE(b.date) AND a.goodsName = b.goodsName ) as Temp group by customerId,goodsId """)
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 query_by_openid_account(self, account, openid): _param = [] _sql = "select * from User where 1=1" if account: _sql += " and account = %s" _param.append(account) if openid: _sql += " and openid = %s" _param.append(openid) helper = MyHelper() return helper.executeQuery(_sql, _param)
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 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 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 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_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 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 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_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_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 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_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
def query_by_SD(self, start, end): connection = MyHelper() return connection.executeQuery("select * from Dailyfund where date >= %s and date <%s order by date ", [start, end])
def query_by_date(self, date): connection = MyHelper() return connection.executeQuery("select * from Dailyfund where date = %s", [date])
def query_public_key(self, _id): connection = MyHelper() return connection.executeQuery( "select * from UserKeys where account = %s", [_id])
def queryAllDaily(self): conn = MyHelper() return conn.executeQuery("select * from Dailyfund order by date ")