php_script/liedou_order.../estimate_fin...

<?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', strtotime('-1 month'));
# 收件人
//$email_list = ["song.shen@kuxuan-inc.com"];
$email_list = ['song.shen@kuxuan-inc.com', 'zhaozhao@kuxuan-inc.com'];

$stime = date('Y-m-01', strtotime($month));
$etime = date('Y-m-01', strtotime($month.' +1 month'));

# 佣金
$sql = " select  left(`order_balance_at`, 10) as day,sum(rebate) as rebate from `order_rebate` where status=2  and "
    ."order_balance_at>='{$stime}' and order_balance_at<'{$etime}' and `maintain_power` <3 group by day order by day;";
$stmt = $_PDO->prepare($sql);
$stmt->execute();
$rebateResult = $stmt->fetchAll(PDO::FETCH_ASSOC);
$rebateSum =  array_sum(array_column($rebateResult, 'rebate'));

# 津贴
$sql = " select  left(`order_balance_at`, 10) as day,sum(rebate) as rebate_profit from `order_rebate_profit` where status=2  and "
    ."order_balance_at>='{$stime}' and order_balance_at<'{$etime}' and `maintain_power` <3 group by day order by day;";
$stmt = $_PDO->prepare($sql);
$stmt->execute();
$rebateProfitResult = $stmt->fetchAll(PDO::FETCH_ASSOC);
$rebateProfitResult = array_column($rebateProfitResult, null, 'day');
$rebateProfitSum = array_sum(array_column($rebateProfitResult, 'rebate_profit'));

foreach ($rebateResult as $k => &$v) {
    $v['rebate_profit'] = isset($rebateProfitResult[$v['day']]['rebate_profit']) ? $rebateProfitResult[$v['day']]['rebate_profit'] : 0;
    $v['remark'] = '';
}

$rebateResult[] = [
    'day'           => '',
    'rebate'        => $rebateSum,
    'rebate_profit' => $rebateProfitSum,
    'remark'        => '单位：分',
];
$rebateResult[] = [
    'day'           => '',
    'rebate'        => $rebateSum/100,
    'rebate_profit' => $rebateProfitSum/100,
    'remark'        => '单位：元',
];
$rebateResult[] = [
    'day'           => '合计',
    'rebate'        => ($rebateSum/100 + $rebateProfitSum/100),
    'rebate_profit' => '',
    'remark'        => '',
];

$m = date('m', strtotime($month));
$indexKey = ['day', 'rebate', 'rebate_profit', 'remark'];
$title = ['日期', '佣金(分)', '津贴(分)', '备注'];
$filename = '猎豆'. $m .'月份预估佣金结算明细_'.date('Y-m-d').'.xlsx';
export_excel($rebateResult, $filename, $indexKey, $title);

$subject = '猎豆'.$m.'月份预估佣金结算明细';
$body = '猎豆'.$m.'月份预估佣金结算明细统计Excel报表，详情请查看附件。';
$attachment = './'.$filename;
$re = sendMail($email_list, $subject, $body, $attachment);
var_dump($re);

/**
 * 导出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);
    foreach ($to as $emailAddress) {
        $mail->AddAddress($emailAddress);
    }
    $mail->AddAttachment($attachment);
    echo "\n发送...";
    $rs = $mail->Send() ? true : $mail->ErrorInfo;
    var_dump($rs);
    return $rs;
}