public function index()
{
$map = $this->com_search();
//定义条件
$where = ' is_paid = 1 ';
//日期期间使用in形式，以确保能正常使用到索引
if (isset($map['start_time']) && $map['start_time'] != '' && isset($map['end_time']) && $map['end_time'] != '') {
$where .= " and pay_date in (" . date_in($map['start_time'], $map['end_time']) . ")";
}
$sql = "select payment_id,pay_date, sum(money) as money from " . DB_PREFIX . "payment_notice where " . $where . " group by payment_id,pay_date";
$money_list = $GLOBALS['db']->getAll($sql);
$sql = "select id,name from " . DB_PREFIX . "payment where is_effect = 1 or total_amount > 0";
$payment_list = $GLOBALS['db']->getAll($sql);
$date_list = explode(",", date_in($map['start_time'], $map['end_time'], false));
//print_r($money_list);
//print_r($date_list);
$list = array();
foreach ($date_list as $k => $v) {
$row = array();
$row['pay_date'] = $v;
foreach ($payment_list as $pk => $pv) {
//$payment_list[$pk]['money'] = 0;
//$row[$pv['id'].'_id'] = $pv['id'];
$money = array();
$money['pay_id'] = $pv['id'];
$money['pay_name'] = $pv['name'];
$money['money'] = format_price($this->getMoney($pv['id'], $v, $money_list));
$money['url'] = U("PaymentNotice/index", array("is_paid" => 1, 'start_time' => $v, 'end_time' => $v, 'payment_id' => $pv['id']));
$row['pay_money'][] = $money;
}
$list[] = $row;
}
//print_r($list);
//$this->assign("default_map",$map);
$this->assign("list", $list);
parent::index();
}
public function export_csv_user_total($page = 1)
{
set_time_limit(0);
$limit = ($page - 1) * intval(app_conf("BATCH_PAGE_SIZE")) . "," . intval(app_conf("BATCH_PAGE_SIZE"));
$map = $this->com_search();
foreach ($map as $key => $val) {
//dump($key);
if (!is_array($val) && $val != '') {
$parameter .= "{$key}=" . urlencode($val) . "&";
}
}
$sql_str = "select FROM_UNIXTIME(create_time , '%Y-%m-%d') as sj,count(*) as zcrs, sum(if(is_investor=0,1,0)) as pthy, sum(if(is_investor=1,1,0)) as tzr, sum(if(is_investor=2,1,0)) as tzjgr\n\t\tfrom " . DB_PREFIX . "user where is_effect = 1 ";
//日期期间使用in形式,以确保能正常使用到索引
if (isset($map['start_time']) && $map['start_time'] != '' && isset($map['end_time']) && $map['end_time'] != '') {
$sql_str .= " and FROM_UNIXTIME(create_time , '%Y-%m-%d') in (" . date_in($map['start_time'], $map['end_time']) . ")";
}
$sql_str .= " group by FROM_UNIXTIME(create_time , '%Y-%m-%d') ";
$list = array();
$list = $GLOBALS['db']->getAll($sql_str);
if ($list) {
$total_value = array('sj' => '""', 'zcrs' => '""', 'pthy' => '""', 'tzr' => '""', 'tzjgr' => '""');
if ($page == 1) {
$content_total = iconv("utf-8", "gbk", "注册人数,普通会员,投资人,投资机构人");
}
if ($page == 1) {
$content_total = $content_total . "\n";
}
foreach ($list as $k => $v) {
$total_value = array();
$total_value['sj'] = iconv('utf-8', 'gbk', '"' . $v['sj'] . '"');
$total_value['zcrs'] = iconv('utf-8', 'gbk', '"' . $v['zcrs'] . '"');
$total_value['pthy'] = iconv('utf-8', 'gbk', '"' . $v['pthy'] . '"');
$total_value['tzr'] = iconv('utf-8', 'gbk', '"' . $v['tzr'] . '"');
$total_value['tzjgr'] = iconv('utf-8', 'gbk', '"' . $v['tzjgr'] . '"');
$content_total .= implode(",", $total_value) . "\n";
}
header("Content-Disposition: attachment; filename=total_list.csv");
echo $content_total;
} else {
if ($page == 1) {
$this->error(L("NO_RESULT"));
}
}
}
public function export_csv_loan_usernum_total($page = 1)
{
set_time_limit(0);
$limit = ($page - 1) * intval(app_conf("BATCH_PAGE_SIZE")) . "," . intval(app_conf("BATCH_PAGE_SIZE"));
$map = $this->com_search();
foreach ($map as $key => $val) {
//dump($key);
if (!is_array($val) && $val != '') {
$parameter .= "{$key}=" . urlencode($val) . "&";
}
}
$sql_str = "select \n\t\tstart_date as time,\n\t\tcount(user_id) as\tjkyhs\n\t\t from " . DB_PREFIX . "deal where deal_status > 0 ";
//日期期间使用in形式，以确保能正常使用到索引
if (isset($map['start_time']) && $map['start_time'] != '' && isset($map['end_time']) && $map['end_time'] != '') {
$sql_str .= " and start_date in (" . date_in($map['start_time'], $map['end_time']) . ")";
}
$sql_str .= " group by start_date limit {$limit} ";
$list = array();
$list = $GLOBALS['db']->getAll($sql_str);
// echo $sql_str;
// exit;
// var_dump($list);exit;
if ($list) {
register_shutdown_function(array(&$this, 'export_csv_loan_usernum_total'), $page + 1);
$loan_usernum_total_value = array('time' => '""', 'jkyhs' => '""');
if ($page == 1) {
$content_loan_usernum_total = iconv("utf-8", "gbk", "时间,借款用户数");
}
if ($page == 1) {
$content_loan_usernum_total = $content_loan_usernum_total . "\n";
}
foreach ($list as $k => $v) {
$loan_usernum_total_value = array();
$loan_usernum_total_value['time'] = iconv('utf-8', 'gbk', '"' . $v['time'] . '"');
$loan_usernum_total_value['jkyhs'] = iconv('utf-8', 'gbk', '"' . $v['jkyhs'] . '"');
$content_loan_usernum_total .= implode(",", $loan_usernum_total_value) . "\n";
}
header("Content-Disposition: attachment; filename=loan_usernum_total_list.csv");
echo $content_loan_usernum_total;
} else {
if ($page == 1) {
$this->error(L("NO_RESULT"));
}
}
}
public function website_advance_total()
{
$map = $this->com_search();
foreach ($map as $key => $val) {
//dump($key);
if (!is_array($val) && $val != '') {
$parameter .= "{$key}=" . urlencode($val) . "&";
}
}
$sql_str = "select \n\t\tg.create_date as 时间,\n\t\tsum(repay_money) as 代还本息总额,\n\t\tsum(manage_money) as 代还管理费总额,\n\t\tsum(impose_money) as 代还罚息总额,\n\t\tsum(manage_impose_money) as 代还逾期管理费总额\n\t\tfrom " . DB_PREFIX . "generation_repay as g ";
//日期期间使用in形式，以确保能正常使用到索引
if (isset($map['start_time']) && $map['start_time'] != '' && isset($map['end_time']) && $map['end_time'] != '') {
$sql_str .= " where g.create_date in (" . date_in($map['start_time'], $map['end_time']) . ")";
}
$sql_str .= " group by g.create_date ";
$model = D();
$voList = $this->_Sql_list($model, $sql_str, "&" . $parameter, '时间', false);
require './admin/Tpl/default/Common/js/flash/php-ofc-library/open-flash-chart.php';
$total_array = array(array(array('代还本息总额', '时间', '代还本息总额'), array('代还管理费总额', '时间', '代还管理费总额'), array('代还罚息总额', '时间', '代还罚息总额'), array('代还逾期管理费总额', '时间', '代还逾期管理费总额')));
//echo $sql_str;
$chart_list = $this->get_jx_json_all($voList, $total_array);
$this->assign("chart_list", $chart_list);
$this->display();
}
public function change_account_total()
{
$map = $this->com_search();
foreach ($map as $key => $val) {
//dump($key);
if (!is_array($val) && $val != '') {
$parameter .= "{$key}=" . urlencode($val) . "&";
}
}
$date_list = explode(",", date_in($map['start_time'], $map['end_time'], false));
$sql = "select create_date as time,count(*) as transfernum,sum(transfer_amount) as transfermoney from " . DB_PREFIX . "deal_load_transfer where create_date in (" . date_in($map['start_time'], $map['end_time']) . ") group by create_date ";
$transfer_list = $GLOBALS['db']->getAll($sql);
$sql = "select transfer_date as time,count(*) as successnum,sum(transfer_amount) as successmoney from " . DB_PREFIX . "deal_load_transfer where transfer_date in (" . date_in($map['start_time'], $map['end_time']) . ") group by transfer_date";
$success_list = $GLOBALS['db']->getAll($sql);
//echo $sql;
$list = array();
foreach ($date_list as $k => $v) {
$row = array();
$row['date'] = $v;
$row['债权转让笔数'] = 0;
$row['债权转让金额'] = 0;
$row['成功转让笔数'] = 0;
$row['成功转让金额'] = 0;
foreach ($transfer_list as $tk => $tv) {
if ($tv['time'] == $v) {
$row['债权转让笔数'] = $tv['transfernum'];
$row['债权转让金额'] = $tv['transfermoney'];
break;
}
}
foreach ($success_list as $sk => $sv) {
if ($sv['time'] == $v) {
$row['成功转让笔数'] = $sv['successnum'];
$row['成功转让金额'] = $sv['successmoney'];
break;
}
}
$list[] = $row;
}
$this->assign("list", $list);
//print_r($list);
require './admin/Tpl/default/Common/js/flash/php-ofc-library/open-flash-chart.php';
$total_array = array(array(array('债权转让笔数', 'date', '债权转让笔数'), array('债权转让金额', 'date', '债权转让金额'), array('成功转让笔数', 'date', '成功转让笔数'), array('成功转让金额', 'date', '成功转让金额')));
$chart_list = $this->get_jx_json_all($list, $total_array);
$this->assign("chart_list", $chart_list);
//dump($chart_list);
$this->display();
}
public function loan_borrow_type()
{
$map = $this->com_search();
foreach ($map as $key => $val) {
//dump($key);
if (!is_array($val) && $val != '') {
$parameter .= "{$key}=" . urlencode($val) . "&";
}
}
$cate_list = $GLOBALS['db']->getAll("select id,name from " . DB_PREFIX . "deal_cate where is_effect = 1 and is_delete = 0 order by id");
$sql_str = "select a.repay_start_date as 时间,";
$item_array = array();
foreach ($cate_list as $key => $val) {
$sql_str .= "sum(if( a.cate_id = " . $val['id'] . ", borrow_amount, 0)) as " . $val['name'] . ",";
$item_array[] = array($val['name'], '时间', $val['name']);
}
$item_array[] = array('成功次数', '时间', '成功次数');
$total_array = array($item_array);
$sql_str .= " count(*) as 成功次数 \n\t\tfrom " . DB_PREFIX . "deal a where a.is_has_loans = 1 ";
//日期期间使用in形式，以确保能正常使用到索引
if (isset($map['start_time']) && $map['start_time'] != '' && isset($map['end_time']) && $map['end_time'] != '') {
$sql_str .= " and a.repay_start_date in (" . date_in($map['start_time'], $map['end_time']) . ")";
}
$sql_str .= " group by a.repay_start_date";
$model = D();
//echo $sql_str;
$voList = $this->_Sql_list($model, $sql_str, "&" . $parameter, '时间', false);
require './admin/Tpl/default/Common/js/flash/php-ofc-library/open-flash-chart.php';
$chart_list = $this->get_jx_json_all($voList, $total_array);
$this->assign("chart_list", $chart_list);
//dump($chart_list);
$this->display();
}
public function export_csv_advance_total($page = 1)
{
set_time_limit(0);
$limit = ($page - 1) * intval(app_conf("BATCH_PAGE_SIZE")) . "," . intval(app_conf("BATCH_PAGE_SIZE"));
$map = $this->com_search();
foreach ($map as $key => $val) {
//dump($key);
if (!is_array($val) && $val != '') {
$parameter .= "{$key}=" . urlencode($val) . "&";
}
}
$sql_str = "select \r\n\t\tg.create_date as time,\r\n\t\tsum(repay_money) as dhbxze,\r\n\t\tsum(manage_money) as dhglfze,\r\n\t\tsum(impose_money) as dhfxze,\r\n\t\tsum(manage_impose_money) as dhyqglfze\r\n\t\tfrom " . DB_PREFIX . "generation_repay as g ";
//日期期间使用in形式，以确保能正常使用到索引
if (isset($map['start_time']) && $map['start_time'] != '' && isset($map['end_time']) && $map['end_time'] != '') {
$sql_str .= " where g.create_date in (" . date_in($map['start_time'], $map['end_time']) . ")";
}
$sql_str .= " group by g.create_date limit {$limit} ";
$list = array();
$list = $GLOBALS['db']->getAll($sql_str);
// echo $sql_str;
// exit;
// var_dump($list);exit;
if ($list) {
register_shutdown_function(array(&$this, 'export_csv_advance_total'), $page + 1);
$advance_total_value = array('time' => '""', 'dhbxze' => '""', 'dhglfze' => '""', 'dhfxze' => '""', 'dhyqglfze' => '""');
if ($page == 1) {
$content_advance_total = iconv("utf-8", "gbk", "时间,代还本息总额,代还管理费总额,代还罚息总额,代还逾期管理费总额");
}
if ($page == 1) {
$content_advance_total = $content_advance_total . "\n";
}
foreach ($list as $k => $v) {
$advance_total_value = array();
$advance_total_value['time'] = iconv('utf-8', 'gbk', '"' . $v['time'] . '"');
$advance_total_value['dhbxze'] = iconv('utf-8', 'gbk', '"' . number_format($v['dhbxze'], 2) . '"');
$advance_total_value['dhglfze'] = iconv('utf-8', 'gbk', '"' . number_format($v['dhglfze'], 2) . '"');
$advance_total_value['dhfxze'] = iconv('utf-8', 'gbk', '"' . number_format($v['dhfxze'], 2) . '"');
$advance_total_value['dhyqglfze'] = iconv('utf-8', 'gbk', '"' . number_format($v['dhyqglfze'], 2) . '"');
$content_advance_total .= implode(",", $advance_total_value) . "\n";
}
header("Content-Disposition: attachment; filename=advance_total_list.csv");
echo $content_advance_total;
} else {
if ($page == 1) {
$this->error(L("NO_RESULT"));
}
}
}
public function export_csv_account_ratio($page = 1)
{
set_time_limit(0);
$limit = ($page - 1) * intval(app_conf("BATCH_PAGE_SIZE")) . "," . intval(app_conf("BATCH_PAGE_SIZE"));
$map = $this->com_search();
foreach ($map as $key => $val) {
//dump($key);
if (!is_array($val) && $val != '') {
$parameter .= "{$key}=" . urlencode($val) . "&";
}
}
$sql_str = "select create_date as time,\n\t\tsum(if(money < 5000, 1, 0)) as ftu,\n\t\tsum(if(money >= 5000 and money < 10000, 1, 0)) as ftom,\n\t\tsum(if(money >= 10000 and money < 50000, 1, 0)) as omtm,\n\t\tsum(if(money >= 50000 and money < 100000, 1, 0)) as fmtm,\n\t\tsum(if(money >= 100000 and money < 200000, 1, 0)) as tmtm,\n\t\tsum(if(money >= 200000 and money < 500000, 1, 0)) as tmfm,\n\t\tsum(if(money >= 500000, 1, 0)) as fmo,\n\t\tcount(*) as cgzrc\n\t\tfrom " . DB_PREFIX . "deal_load where is_has_loans = 1 ";
//日期期间使用in形式，以确保能正常使用到索引
if (isset($map['start_time']) && $map['start_time'] != '' && isset($map['end_time']) && $map['end_time'] != '') {
$sql_str .= " and create_date in (" . date_in($map['start_time'], $map['end_time']) . ")";
}
$sql_str .= " GROUP BY create_date limit {$limit} ";
$list = array();
$list = $GLOBALS['db']->getAll($sql_str);
// echo $sql_str;
// exit;
// var_dump($list);exit;
if ($list) {
register_shutdown_function(array(&$this, 'export_csv_account_ratio'), $page + 1);
$account_ratio_value = array('time' => '""', 'ftu' => '""', 'ftom' => '""', 'omtm' => '""', 'fmtm' => '""', 'tmtm' => '""', 'tmfm' => '""', 'fmo' => '""', 'cgzrc' => '""');
if ($page == 1) {
$content_account_ratio = iconv("utf-8", "gbk", "时间,5千以下,5千至1万,1至5万,5至10万,10至20万,20至50万,50万以上,成功总人次");
}
if ($page == 1) {
$content_account_ratio = $content_account_ratio . "\n";
}
foreach ($list as $k => $v) {
$account_ratio_value = array();
$account_ratio_value['time'] = iconv('utf-8', 'gbk', '"' . $v['time'] . '"');
$account_ratio_value['ftu'] = iconv('utf-8', 'gbk', '"' . $v['ftu'] . '"');
$account_ratio_value['ftom'] = iconv('utf-8', 'gbk', '"' . $v['ftom'] . '"');
$account_ratio_value['omtm'] = iconv('utf-8', 'gbk', '"' . $v['omtm'] . '"');
$account_ratio_value['fmtm'] = iconv('utf-8', 'gbk', '"' . $v['fmtm'] . '"');
$account_ratio_value['tmtm'] = iconv('utf-8', 'gbk', '"' . $v['tmtm'] . '"');
$account_ratio_value['tmfm'] = iconv('utf-8', 'gbk', '"' . $v['tmfm'] . '"');
$account_ratio_value['fmo'] = iconv('utf-8', 'gbk', '"' . $v['fmo'] . '"');
$account_ratio_value['cgzrc'] = iconv('utf-8', 'gbk', '"' . $v['cgzrc'] . '"');
$content_account_ratio .= implode(",", $account_ratio_value) . "\n";
}
header("Content-Disposition: attachment; filename=account_ratio_list.csv");
echo $content_account_ratio;
} else {
if ($page == 1) {
$this->error(L("NO_RESULT"));
}
}
}
public function tender_account_ratio()
{
$map = $this->com_search();
foreach ($map as $key => $val) {
//dump($key);
if (!is_array($val) && $val != '') {
$parameter .= "{$key}=" . urlencode($val) . "&";
}
}
$sql_str = "select create_date as 时间,\n\t\tsum(if(money < 5000, 1, 0)) as 5千以下,\n\t\tsum(if(money >= 5000 and money < 10000, 1, 0)) as 5千至1万,\n\t\tsum(if(money >= 10000 and money < 50000, 1, 0)) as 1至5万,\n\t\tsum(if(money >= 50000 and money < 100000, 1, 0)) as 5至10万,\n\t\tsum(if(money >= 100000 and money < 200000, 1, 0)) as 10至20万,\n\t\tsum(if(money >= 200000 and money < 500000, 1, 0)) as 20至50万,\n\t\tsum(if(money >= 500000, 1, 0)) as 50万以上,\n\t\tcount(*) as 成功总人次\n\t\tfrom " . DB_PREFIX . "deal_load where is_has_loans = 1 ";
//日期期间使用in形式，以确保能正常使用到索引
if (isset($map['start_time']) && $map['start_time'] != '' && isset($map['end_time']) && $map['end_time'] != '') {
$sql_str .= " and create_date in (" . date_in($map['start_time'], $map['end_time']) . ")";
}
$sql_str .= " GROUP BY create_date ";
$model = D();
//echo $sql_str;
$voList = $this->_Sql_list($model, $sql_str, "&" . $parameter, '时间', false);
require './admin/Tpl/default/Common/js/flash/php-ofc-library/open-flash-chart.php';
$total_array = array(array(array('5千以下', '时间', '5千以下'), array('5千至1万', '时间', '5千至1万'), array('1至5万', '时间', '1至5万'), array('5至10万', '时间', '5至10万'), array('10至20万', '时间', '10至20万'), array('20至50万', '时间', '20至50万'), array('50万以上', '时间', '50万以上'), array('成功总人次', '时间', '成功总人次')));
$chart_list = $this->get_jx_json_all($voList, $total_array);
$this->assign("chart_list", $chart_list);
//dump($chart_list);
$this->display();
}