#
#
#
#
#
#
#
#
#
#
require "../settings.php";
require "../libs/ext.lib.php";
if (div_isset("DEBT_AGE", "mon")) {
$OUTPUT = printAgeAge();
} elseif (isset($_POST["xls"])) {
$OUTPUT = excel($_POST);
} else {
$OUTPUT = printAgeInv($_POST);
}
require "../template.php";
# Age analysis by date
function printAgeInv($_POST, $pure = TRUE)
{
extract($_POST);
# Set up table to display in
global $PRDMON;
$from = getMonthName($PRDMON[1]) . " " . getYearOfFinMon($PRDMON[1]);
$to = getMonthName($PRDMON[12]) . " " . getYearOfFinMon($PRDMON[12]);
$sel1 = "";
$sel2 = "";
$sel3 = "";
public function _excel()
{
$where = $this->_getWhere();
$model = M()->table('zj_loan_order_salesman s')->join('zj_loan_order o on o.id=s.order_id');
$model->join('zj_user u on u.uid=s.uid');
$model->join('zj_university_campus c on c.id=o.campus_id');
$fields = 's.*,o.seller_name,o.realname order_realname,o.created,o.status,o.loan_money,o.type,o.title,o.realname,o.seller_name,u.realname,u.mobile,c.city,c.university_name,c.name campus_name';
$list = $model->where($where)->order('lend_time')->field($fields)->select();
$orderModel = new \Loan\Model\OrderModel();
int_to_string($list, ['type' => $orderModel->getType(), 'status' => $orderModel->getStatus()]);
foreach ($list as $key => $vo) {
$list[$key]['created'] = time_format($vo['created']);
$list[$key]['lend_time'] = time_format($vo['lend_time']);
}
excel('业绩数据', $list, ['城市' => 'city', '类型' => 'type_text', '学校' => 'university_name', '校区' => 'campus_name', '订单号' => 'order_id', '放款时间' => 'lend_time', '下单时间' => 'created', '学生姓名' => 'order_realname', '订单标题' => 'title', '订单状态' => 'status_text', '贷款金额' => 'loan_money', '业务员姓名' => 'realname', '业务员手机号' => 'mobile', '商家店名' => 'seller_name']);
}
<?php
require "settings.php";
if (isset($_REQUEST["button"])) {
list($button) = array_keys($_REQUEST["button"]);
switch ($button) {
case "excel":
$OUTPUT = excel();
break;
}
} elseif (isset($_REQUEST["key"])) {
switch ($_REQUEST["key"]) {
case "select":
$OUTPUT = select();
break;
case "display":
$OUTPUT = display();
break;
}
} else {
$OUTPUT = display();
}
require "template.php";
function select()
{
extract($_REQUEST);
// Retrieve stores
$sql = "SELECT whid, whno, whname FROM exten.warehouses ORDER BY whno ASC";
$stores_rslt = db_exec($sql) or errDie("Unable to retrieve stores.");
$stores_sel = "<select name='whid' style='width: 100%' />";
while ($stores_data = pg_fetch_array($stores_rslt)) {
public function stat()
{
$type = I('get.type');
$cash_id = '39,83';
$where_config = ['cash' => ['o.goods_id' => ['in', $cash_id]], 'offline' => ['o.goods_id' => 0], 'online' => ['o.goods_id' => ['not in', '0,' . $cash_id]]];
// 放款统计
if ($type == 'lend') {
function model()
{
return M()->table('zj_loan_money m')->join('zj_loan_order o on o.id=m.order_id');
}
$total = $today = [];
$where = ['m.type' => -1];
$where_today = ['m.created' => ['between', strtotime(I('get.date', date('Y-m-d')) . ' 00:00:00') . ',' . strtotime(I('get.date', date('Y-m-d')) . ' 23:59:59')]];
foreach ($where_config as $key => $where_type) {
$total[$key] = model()->where($where + $where_type)->sum('m.money') ?: '0.00';
$total[$key . '_order'] = model()->where($where + $where_type)->count('distinct(o.id)') ?: 0;
$today[$key] = model()->where($where + $where_type + $where_today)->sum('m.money') ?: '0.00';
$today[$key . '_order'] = model()->where($where + $where_type + $where_today)->count('distinct(o.id)') ?: 0;
}
$this->assign('today', $today);
$this->assign('total', $total);
$this->assign('meta_title', '放款统计');
$this->display('stat_lend');
exit;
}
// 回款统计
if ($type == 'income') {
$start = I('get.start', date('Y-m-01'));
$end = I('get.end', date('Y-m-d'));
$this->assign('start', $start);
$this->assign('end', $end);
function model()
{
return M()->table('zj_loan_money m')->join('zj_loan_order o on o.id=m.order_id');
}
$total = $today = [];
$where = ['type' => ['in', '1,2,3']];
$where_today = ['created' => ['between', strtotime($start . ' 00:00:00') . ',' . strtotime($end . ' 23:59:59')]];
$total = M('loan_money')->where($where)->group('type')->getField('type,sum(money)');
$today = M('loan_money')->where($where + $where_today)->group('type')->getField('type,sum(money)');
for ($i = 1; $i <= 3; $i++) {
$total[$i] = isset($total[$i]) ? $total[$i] : '0.00';
$today[$i] = isset($today[$i]) ? $today[$i] : '0.00';
}
debug($today);
$this->assign('today', $today);
$this->assign('total', $total);
$this->assign('meta_title', '回款统计');
$this->display('stat_income');
exit;
}
// 还款统计
if ($type == 'repay') {
$method = I('get.method');
$field = 'u.uid,u.realname,university_name,campus_name,city,total_money_loan,total_money_return';
$model = M()->field($field)->table('zj_loan_user u')->join('zj_loan_user_contract c on c.uid=u.uid');
if ($method == 'excel') {
$users = $model->field($field)->where('total_money_loan>0')->order('u.created desc')->select();
} else {
$users = $this->lists($model, 'total_money_loan>0', 'u.created desc', $field);
}
$uids = array_column($users, 'uid');
// 用户的金额统计
$moneys = [];
foreach ($where_config as $key => $where) {
$model = M()->table('zj_loan_order_finance f')->join('zj_loan_order o on o.id=f.order_id');
$model->where($where)->field('uid,sum(lend_money-return_money) money')->group('o.uid');
$moneys[$key] = array_column($model->select(), 'money', 'uid');
}
// 用户的订单数统计
$orders = [];
foreach ($where_config as $key => $where) {
$model = M()->table('zj_loan_order_finance f')->join('zj_loan_order o on o.id=f.order_id');
$model->where($where)->field('uid,count(1) total')->group('o.uid');
$orders[$key] = array_column($model->select(), 'total', 'uid');
}
foreach ($users as $key => $user) {
$uid = $user['uid'];
foreach ($where_config as $type => $where) {
$users[$key][$type] = isset($moneys[$type][$uid]) ? $moneys[$type][$uid] : '0.00';
$users[$key][$type . '_order'] = isset($orders[$type][$uid]) ? $orders[$type][$uid] : 0;
}
}
if ($method == 'excel') {
foreach ($users as $key => $vo) {
$users[$key]['campus'] = $vo['university_name'] . ' ' . $vo['campus_name'];
$users[$key]['total_money_no_return'] = $vo['total_money_loan'] - $vo['total_money_return'];
}
$config = ['学生姓名' => 'realname', '所属校区' => 'campus', '所属城市' => 'city', '应急包' => ['field' => 'cash', 'type' => 'f'], '线下购物' => ['field' => 'offline', 'type' => 'f'], '线上购物' => ['field' => 'online', 'type' => 'f'], '小计' => ['field' => 'total_money_loan', 'type' => 'f'], '已回款金额' => ['field' => 'total_money_return', 'type' => 'f'], '未回款金额' => ['field' => 'total_money_no_return', 'type' => 'f'], '应急包签约数' => ['field' => 'cash_order', 'type' => 'i'], '线下购物签约数' => ['field' => 'offline_order', 'type' => 'i'], '线上购物签约数' => ['field' => 'online_order', 'type' => 'i']];
excel('收款统计', $users, $config);
}
$this->assign('_list', $users);
$this->assign('meta_title', '收款统计');
$this->display('stat_repay');
exit;
}
$this->error('未知统计类型！');
}
public function apply()
{
$job_id = I('id', 0, 'intval');
$this->assign('job_id', $job_id);
$status = I('status', -5, 'intval');
$model = M()->table('zj_job_apply a')->join('zj_user u on u.uid=a.uid', 'left')->join('zj_user_info i on i.uid=a.uid', 'left');
$model->join('zj_loan_user_contract luc on luc.uid=a.uid', 'left')->join('zj_job j on j.id=a.job_id', 'left');
// 状态查找
$fieldMap = array('status' => array(-5 => '全部', -2 => '已取消', -1 => '审核不通过', 1 => '审核通过', 0 => '待审核'));
$status_name = $fieldMap['status'][$status];
$where_map = [];
if ($job_id) {
$where_map['a.job_id'] = $job_id;
}
if ($status_name != '全部') {
$where_map['a.status'] = $status;
}
// 城市查找
$city_map = $this->_getRegion();
$_region = [0 => '不限城市'] + $city_map;
$city = I('city', 0, 'intval');
isset($_region[$city]) or $this->error('无此城市信息：' . $city);
$this->assign('_region', $_region);
$this->assign('_region_name', $_region[$city]);
if ($city > 0) {
// 判断是省还是区
$is_city_id = strpos($_region[$city], ' - ') === 0;
$where_map['j.city_id'] = ['exp', ' in (select id from zj_common_region where ' . ($is_city_id ? 'id' : 'parent_id') . '=' . $city . ')'];
}
// 学校查找
$university_map = M('university')->where('status=1')->order('sort DESC')->getField('id,name');
$_university = [0 => '不限城市'] + $university_map;
$university = I('university', 0, 'intval');
isset($_university[$university]) or $this->error('无此学校信息：' . $university);
$this->assign('_university', $_university);
$this->assign('_university_name', $_university[$university]);
if ($university > 0) {
$where_map['i.university_id'] = $university;
}
// 姓名
$uname = I('uname', '');
if ($uname) {
$where_map['a.uname'] = ['like', '%' . $uname . '%'];
}
$fields = 'a.*,j.category_name,j.title,u.mobile,i.university_name,i.campus_name,luc.realname';
if (I('method') == 'excel') {
$list = $model->where($where_map)->order('id')->field($fields)->select();
foreach ($list as $key => $vo) {
$list[$key]['created'] = date('Y-m-d H:i:s', $vo['created']);
$list[$key]['is_have_order'] = $vo['is_have_order'] > 0 ? '是' : '否';
}
excel('兼职报名名单', $list, ['编号' => 'id', '兼职分类' => 'category_name', '兼职标题' => 'title', '报名时间' => 'created', '真实姓名' => 'realname', '手机号' => 'mobile', '有兼职换购' => 'is_have_order', '学校' => 'university_name', '校区' => 'campus_name']);
}
$list = $this->lists($model, $where_map, 'id DESC', $fields);
if ($list) {
// 判断是否有兼职换购的订单
$orders = M('loan_order')->where(['uid' => ['in', array_column($list, 'uid')], 'status' => ['not in', [OrderModel::STATUS_CHECK_FAILURE, OrderModel::STATUS_BREACH_CONTRACT, OrderModel::STATUS_FINISH_SUCCESS, OrderModel::STATUS_CLOSED]], 'is_support_job' => 1])->getField('uid', true);
foreach ($list as $key => $vo) {
$list[$key]['is_have_order'] = in_array($vo['uid'], $orders);
}
}
$this->assign('_status', $fieldMap['status']);
$this->assign('status_name', $status_name);
$this->assign('status', $status);
int_to_string($list, $fieldMap);
$this->assign('_list', $list);
$this->meta_title = '待审核报名信息';
$this->display();
}
public function export_stat()
{
$sql_total_order = M('loan_order')->where(['company_id' => ['exp', '=c.id'], 'status' => ['neq', \Loan\Model\OrderModel::STATUS_CLOSED]])->field('count(1)')->buildSql();
$sql_total_order_money = M('loan_order')->where(['company_id' => ['exp', '=c.id'], 'status' => ['neq', \Loan\Model\OrderModel::STATUS_CLOSED]])->field('sum(money)')->buildSql();
$sql_total_order_breach = M('loan_order')->where(['company_id' => ['exp', '=c.id'], 'status' => \Loan\Model\OrderModel::STATUS_BREACH_CONTRACT])->field('count(1)')->buildSql();
$field = ['id', 'uid', 'name', 'city_name', 'type', 'contact_user', $sql_total_order . ' AS total_order', $sql_total_order_money . ' AS total_order_money', $sql_total_order_breach . ' AS total_order_breach'];
$model = M('loan_company')->alias('c')->order('c.id');
$list = $model->field($field)->select();
int_to_string($list, ['type' => $this->_type]);
$list = array_map(function ($item) {
// 使用intval,floatval直接过滤null
$item['total_order_money'] = floatval($item['total_order_money']);
$item['total_order'] = intval($item['total_order']);
$item['total_order_breach'] = intval($item['total_order_breach']);
// 违约率
$item['total_order_breach_rate'] = floatval($item['total_order_breach'] / $item['total_order']);
return $item;
}, $list);
excel('商铺业绩', $list, ['城市' => 'city_name', '类型' => 'type_text', '商铺名称' => 'name', '对接人（招商专员）' => 'contact_user', '销售额' => 'total_order_money', '出单笔数' => 'total_order', '违约笔数' => 'total_order_breach', '违约率（违约笔数/出单量）' => 'total_order_breach_rate']);
}
