<?php
define( "ROOT_PATH", dirname(dirname(__FILE__)) );
require_once ROOT_PATH.'/DB_PDO.class.php';
require_once ROOT_PATH.'/confv2.class.php';
require_once ROOT_PATH.'/PHPExcel-1.8/Classes/PHPExcel.php';
require_once ROOT_PATH.'/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';
require_once ROOT_PATH.'/PHPExcel-1.8/Classes/PHPExcel/Reader/Excel5.php';
require_once ROOT_PATH.'/PHPMailer/ClassPhpMailer.php';

/* 月底猎豆预估报表发给财务 */
date_default_timezone_set('PRC');
$_PDO=DB_PDO::getInstance( conf::$DB_CONF );

$month = date('Y-m');

$stime = date('Y-m-01', strtotime($month));
$etime = date('Y-m-d 23:59:59', strtotime($month.' +1 month -1 day'));

$sql = " select left(`create_time`, 10) as day,sum(`pub_share_pre_fee`)  as money from  `liedou_taoke_orders` where trade_id in(
        select order_id from `order_rebate` where status>0 and order_create_at>='{$stime}' and order_create_at<'{$etime}' and `maintain_power`<3
    ) group by day order by day ";
$stmt = $_PDO->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
foreach ($result as $k => &$v) {
	$v['money'] = $v['money'] * 100; //转成分
    #佣金
    $v['rebate'] = get_rebate( $v['day'] );
    $v['rebate_profit'] = get_rebate_profit( $v['day'] );
    $v['remain'] = $v['money'] - $v['rebate'] - $v['rebate_profit'];
    $v['remain_rate'] = $v['remain'] / $v['money'];
}
$m = date('m', strtotime($month));
$indexKey = ['day', 'money', 'rebate', 'rebate_profit', 'remain', 'remain_rate'];
$title = ['日期', '总佣金(分)', '分配佣金(分)', '分配津贴(分)', '系统留存(分)', '留存占比'];
$filename = '猎豆'. $m .'月份预估佣金统计_'.date('Y-m-d').'.xlsx';
export_excel($result, $filename, $indexKey, $title);

$to = 'hao.sun@kuxuan-inc.com';
$subject = '猎豆'.$m.'月份预估佣金明细';
$body = '猎豆'.$m.'月份预估佣金明细统计Excel报表，详情请查看附件。';
$attachment = './'.$filename;
$re = sendMail($to, $subject, $body, $attachment); 
var_dump($re);

function get_rebate( $day ){
    $eday = $day . ' 23:59:59';
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $sql = " select sum(rebate) as rebate from order_rebate where status>0 and order_create_at>='{$day}' and order_create_at<='{$eday}' and maintain_power<3 ";
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $res = $stmt->fetch(PDO::FETCH_ASSOC);
    if( empty($res) ){
        return false;
    }
    return $res['rebate'];
}

function get_rebate_profit( $day ){
    $eday = $day . ' 23:59:59';
    $_PDO=DB_PDO::getInstance( conf::$DB_CONF );
    $sql = " select sum(rebate) as rebate from order_rebate_profit where status>0 and order_create_at>='{$day}' and order_create_at<='{$eday}' and maintain_power<3 ";
    $stmt = $_PDO->prepare($sql);
    $stmt->execute();
    $res = $stmt->fetch(PDO::FETCH_ASSOC);
    if( empty($res) ){
        return false;
    }
    return $res['rebate'];
}

/**
 * 导出excel
 * @param $data
 * @param string 
 */
function export_excel($data, $filename = '未命名.xlsx', $indexKey, $title) {

    if( !is_array($indexKey)) return false;            
    $header_arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK');  
    //初始化PHPExcel()  
    $objPHPExcel = new PHPExcel();            
    $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);    
        
    //接下来就是写数据到表格里面去  
    $objActSheet = $objPHPExcel->getActiveSheet();  
    foreach($title as $k=>$item){
        $objActSheet->setCellValue($header_arr[$k].'1',$item);
    }
    $startRow = 2;  
    foreach ($data as $row) {  
        foreach ($indexKey as $key => $value){  
            //这里是设置单元格的内容  
            $objActSheet->setCellValue($header_arr[$key].$startRow,$row[$value]);  
        }  
        $startRow++;  
    } 

    $objWriter->save('./'.$filename);  
    return;
}

/**
 * @method sendMail 发送邮件
 * @param $to
 * @param $subject
 * @param string $body
 * @param null $attachment
 * @return bool|string
 * @throws \phpmailerException
 */
function sendMail($to, $subject, $body = '', $attachment = null) { //$to 收件者 $subject主题 $body 内容  $attachment附件
    //邮件服务器配置
    $detail = array(
        "smpt" => "smtp.exmail.qq.com",
        "account" => "ldscript@kuxuan-inc.com",
        "pwd" => "7W2nJ2n3yDxFtDsM",
    );

    $title = "";
    $mail = new PHPMailer(); //PHPMailer对象
    $mail->CharSet = 'utf-8'; //设定邮件编码，默认ISO-8859-1，如果发中文此项必须设置，否则乱码
    $mail->Encoding = "base64";
    $mail->IsSMTP();  // 设定使用SMTP服务
    $mail->SMTPDebug = 0;                     // 关闭SMTP调试功能
    $mail->SMTPAuth = true;                  // 启用 SMTP 验证功能
    $mail->SMTPSecure = 'ssl';                 // 使用安全协议
    $mail->Host = $detail['smpt'];  // SMTP 服务器
    $mail->Port = "465";  // SMTP服务器的端口号
    $mail->Username = $detail['account'];  // SMTP服务器用户名
    $mail->Password = $detail['pwd'];  // SMTP服务器密码
    $mail->Subject = $subject; //邮件标题
    $mail->SetFrom($detail['account'], $title);
    $mail->MsgHTML($body);
    $mail->AddAddress($to);
    $mail->AddAddress("xiaohua.hou@kuxuan-inc.com");
    $mail->AddAddress("song.shen@kuxuan-inc.com");
//    $mail->AddAddress("wanlu.zhang@kuxuan-inc.com");
    $mail->AddAddress("zhaozhao@kuxuan-inc.com");
    $mail->AddAttachment($attachment);
echo "\n发送...";
    $rs = $mail->Send() ? true : $mail->ErrorInfo;
var_dump($rs);
    return $rs;
}