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