='{$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; }