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 ")
