async def index(*, keyword=None, rangeDate=None, moneyStatus=None,invStatus=None, mediaType=None, isExport=None, isSearch=None, year=None, page=1, pageSize=50):
page = int(page)
pageSize = int(pageSize)
# 合计金额, 只统计当前页面显示数据
totalMoney = 0
where = baseWhere = await addAffDateWhere(rangeDate, isSearch, 'i.is_delete')
if keyword:
where = "{} and (income_id like '%%{}%%' or c.name like '%%{}%%')".format(where, keyword, keyword)
if moneyStatus and moneyStatus.isdigit():
where = "{} and money_status = {}".format(where, moneyStatus)
if invStatus and invStatus.isdigit():
where = "{} and inv_status = {}".format(where, invStatus)
if mediaType and mediaType.isdigit():
where = "{} and media_type = {}".format(where, mediaType)
sql = "SELECT count(*) c FROM income i INNER JOIN `client` c ON i.`client_id` = c.`id` where {}".format(where)
rs = await Income.query(sql)
total, limit, p = totalLimitP(rs, page, pageSize)
if total == 0:
return dict(total = total, page = p, list = (), other = {
'moneyStatusMap': moneyStatusMap,
'invStatusMap': invStatusMap,
'mediaTypeMap': mediaTypeMap,
'totalMoney': round(totalMoney, 2)
})
sql = "SELECT i.*,c.name company_name FROM income i INNER JOIN `client` c ON i.`client_id` = c.`id` where %s order by %s" % (where, 'income_id desc')
if not isExport or int(isExport) != 1:
sql = '%s limit %s' % (sql, limit)
lists = await Income.query(sql)
# 将获得数据中的日期转换为字符串
lists = obj2str(lists)
for item in lists:
item['money_status_text'] = moneyStatusMap[item['money_status']]
item['inv_status_text'] = invStatusMap[item['inv_status']]
item['media_type_text'] = mediaTypeMap[item['media_type']]
totalMoney += item['money']
if isExport and int(isExport) == 1:
return await export(lists)
return {
'total': total,
'page': p,
'list': lists,
'other': {
'moneyStatusMap': moneyStatusMap,
'invStatusMap': invStatusMap,
'mediaTypeMap': mediaTypeMap,
'totalMoney': round(totalMoney, 2)
}
}
async def index(*, keyword=None, status=None, page=1, pageSize=10):
page = int(page)
pageSize = int(pageSize)
# 当前日期
currDate = time.strftime('%Y-%m-%d')
where = '1 = 1'
if keyword:
where = "name like '%%{}%%'".format(keyword)
if status and status.isdigit():
if int(status) == 0:
where = "%s and indate_end < '%s'" % (where, currDate)
else:
where = "%s and indate_end >= '%s'" % (where, currDate)
total = await Client.findNumber('count(id)', where)
total, limit, p = totalLimitP(total, page, pageSize, True)
if total == 0:
return dict(total=total, page=p, list=())
clients = await Client.findAll(orderBy='indate_end',
where=where,
limit=limit)
# 将获得数据中的日期转换为字符串
clients = obj2str(clients)
# 获得每个客户下的投放数和回款数
selectField = "count(*) tfCount, sum(money) tfMoney"
for item in clients:
# 投放数
where = 'is_delete = 0 and client_id=%s' % item.id
info = await Income.findOne(selectField, where)
item['tfCount'] = round(info['tfCount'], 2) if info['tfCount'] else 0
# 投放金额
item['tfMoney'] = round(info['tfMoney'], 2) if info['tfMoney'] else 0
# 回款金额
where = "%s and money_status = 1" % where
field = "count(*) hkCount, sum(money) hkMoney"
info = await Income.findOne(field, where)
item['hkMoney'] = round(info['hkMoney'], 2) if info['hkMoney'] else 0
# 回款数
item['hkCount'] = round(info['hkCount'], 2) if info['hkCount'] else 0
item['invoice'] = replLineBreak(item['invoice'])
# 合同是否有效
item['indate_status'] = item['indate_end'] >= currDate
timedelta = datetime.datetime.strptime(
item['indate_end'], '%Y-%m-%d') - datetime.datetime.strptime(
datetime.datetime.now().strftime('%Y-%m-%d'), '%Y-%m-%d')
item['timedelta'] = timedelta.days
# print(item['sortFlag']) datetime.datetime.strptime(detester,’%Y-%m-%d') item['sortFlag'] =
res = sortFunc(*clients)
return {'total': total, 'page': p, 'list': res}
async def index(*, keyword=None, operate=None, module=None, page=1, pageSize=10):
page = int(page)
pageSize = int(pageSize)
email = configs.user.name
user_info = await Users.findOne(where="email='%s'" % email)
ids = user_info['read_log_ids']
# 当前日期
# currDate = time.strftime('%Y-%m-%d')
where = "s.is_delete =0"
if keyword:
where = "{} and (s.username like '%%{}%%' or i.income_id like '%%{}%%')".format(where, keyword, keyword)
if operate and operate in operateMaps.keys():
where = "%s and operate = '%s'" % (where, operate)
if module and module in moduleMaps.keys():
where = "%s and module = '%s'" % (where, module)
sql = sqlTpl.format('count(*) c', where)
rs = await Syslogs.query(sql)
total, limit, p = totalLimitP(rs, page, pageSize)
if total == 0:
return dict(total = total, page = p, list = (), other={
'operateMaps': operateMaps,
'moduleMaps': moduleMaps
})
where = "%s order by %s limit %s" % (where, 's.id desc', limit)
sql = sqlTpl.format(selectField, where)
lists = await Syslogs.query(sql)
# 将获得数据中的日期转换为字符串
lists = obj2str(lists)
# 获得每个客户下的投放数和回款数
for item in lists:
if ids and item['id'] and str(item['id']) in ids:
item['is_read'] = 1
else:
item['is_read'] = 0
item['operate_text'] = operateMaps[item['operate']]
item['module_text'] = moduleMaps[item['module']]
item['money_status_text'] = moneyStatusMap[item['money_status']]
item['inv_status_text'] = invStatusMap[item['inv_status']]
return {
'total': total,
'page': p,
'list': lists,
'other': {
'operateMaps': operateMaps,
'moduleMaps': moduleMaps
}
}
async def index(*,
keyword=None,
media_type=None,
rangeDate=None,
isSearch=0,
page=1,
pageSize=10):
page = int(page)
pageSize = int(pageSize)
where = await addAffDateWhere(rangeDate, isSearch)
if keyword and keyword.strip() != '':
where = "{} and `business_type` like '%%{}%%'".format(where, keyword)
if media_type is not None and media_type.isdigit():
where = "{} and `media_type` = {}".format(where, media_type)
baseWhere = where
# 查询数据总数
groupBy = 'aff_date,business_type'
sql = 'SELECT COUNT(*) c FROM ( \
SELECT id FROM `income` where %s GROUP BY %s \
) t' % (where, groupBy)
rs = await Income.query(sql)
total, limit, p = totalLimitP(rs, page, pageSize, True)
# 获得所有业务类型
types = await Business.findAll()
if total == 0:
return dict(total=total, page=p, list=(), other={'types': types})
# 查询数据列表
field = 'client_id, income_id, business_type type,aff_date,count(*) tfCount, sum(money) tfMoney'
lists = await Income.findAll(field=field,
orderBy="aff_date desc",
groupBy=groupBy,
where=where,
limit=limit)
for item in lists:
# 查询回款数
where = "%s and business_type = '%s' and money_status = 1" % (
baseWhere, item.type)
item['hkCount'] = await Income.findNumber('count(id)', where)
# 回款金额
hkMoney = await Income.findNumber('sum(money)', where)
item['hkMoney'] = round(hkMoney, 2) if hkMoney else 0
# 投放金额
item['tfMoney'] = round(item['tfMoney'], 2)
return {
'total': total,
'page': p,
'list': lists,
'other': {
'types': types
}
}
async def index(*,
keyword=None,
rangeDate=None,
status=None,
isSearch=None,
page=1,
pageSize=10):
page = int(page)
pageSize = int(pageSize)
# 合计收入金额
totalMoney = 0
# 合计结算金额
totalBalance = 0
where = "s.is_delete = 0"
if keyword:
where = "{} and i.income_id like '%%{}%%' or c.name like '%%{}%%'".format(
where, keyword, keyword)
if status and status.isdigit():
where = "{} and s.status = {}".format(where, status)
if rangeDate:
startDate, endDate = rangeDate.split(' - ')
if startDate and endDate:
where = "{} and aff_date >= '{}' and aff_date < '{}'".format(
where, startDate, endDate)
# 获得总条数和分页数
sql = sqlTpl.format('count(*) c', where)
rs = await Settlement.query(sql)
total, limit, p = totalLimitP(rs, page, pageSize)
if total == 0:
return dict(total=total,
page=p,
list=(),
other={
'statusMap': statusMap,
'totalMoney': 0,
'totalBalance': 0
})
# 查询列表数据
where = " %s order by %s limit %s" % (where, 's.status asc, s.id desc',
limit)
sql = sqlTpl.format(selectField, where)
lists = await Settlement.query(sql)
# 将获得数据中的日期转换为字符串
lists = obj2str(lists)
for item in lists:
statusDate = item['add_date']
item['status_text'] = statusMap[item['status']]
if int(item['status']) == 1:
statusDate = item['finished_time']
item['status_text'] = "%s<br/>%s" % (item['status_text'], statusDate)
item['invoice'] = replLineBreak(item['invoice'])
item['stype_text'] = stypeMap[item['stype']]
totalMoney += item['money']
totalBalance += item['balance']
return {
'total': total,
'page': p,
'list': lists,
'other': {
'statusMap': statusMap,
'totalMoney': round(totalMoney, 2),
'totalBalance': round(totalBalance, 2),
}
}
async def index(*,
keyword=None,
rangeDate=None,
status=None,
page=1,
pageSize=10):
page = int(page)
pageSize = int(pageSize)
year = time.strftime('%Y')
# 合计金额
totalMoney = 0
where = "inv.is_delete = 0"
if keyword:
where = "{} and (i.income_id like '%%{}%%' or c.name like '%%{}%%')".format(
where, keyword, keyword)
if status and status.isdigit():
where = "{} and finished = {}".format(where, status)
if rangeDate:
startDate, endDate = rangeDate.split(' - ')
if startDate and endDate:
where = "{} and aff_date >= '{}' and aff_date < '{}'".format(
where, startDate, endDate)
sql = sqlTpl.format('count(*) c', where)
rs = await Invoice.query(sql)
total, limit, p = totalLimitP(rs, page, pageSize)
if total == 0:
return dict(total=total,
page=p,
list=(),
other={
'statusMap': statusMap,
'totalMoney': round(totalMoney, 2)
})
where = " %s order by %s limit %s" % (
where, 'inv.finished asc, inv.id desc', limit)
sql = sqlTpl.format(selectField, where)
lists = await Invoice.query(sql)
# 将获得数据中的日期转换为字符串
lists = obj2str(lists)
for item in lists:
statusDate = item['add_date']
item['status_text'] = statusMap[item['finished']]
if int(item['finished']) == 1:
statusDate = item['finished_time']
item['status_text'] = "%s<br/>%s" % (item['status_text'], statusDate)
item['info'] = replLineBreak(item['info'])
item['comments'] = replLineBreak(item['comments'])
totalMoney += item['money']
if item['in_id'].find(',') > 0:
income_ids = await Income.findCols('income_id',
"id in (%s)" % item['in_id'])
if income_ids:
item['income_id'] = ','.join(income_ids)
return {
'total': total,
'page': p,
'list': lists,
'other': {
'statusMap': statusMap,
'totalMoney': round(totalMoney, 2)
}
}