ipcontroller

<?php

namespace App\Http\Controllers;

use App\Models\BookInfo;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;


class IplocationController extends Controller
{



//机票导出订单数据
/*
* 2016/1/1 1451577600
* 2016/12/31 1483113600
*
* 2017/1/1 1483200000
* 2017/12/31 1514649600
*
* 2018/1/1 1514736000
* 2018/12/31 1546185600
*/
public function flight(Request $request){
$iscom = 0;
switch ($request->input('date')){
case '2016' :
$file = '机票2016';
$begin = '1451577600';
$end = '1483113600';
break;
case '2017' :
$file = '机票2017';
$begin = '1483200000';
$end = '1514649600'; break;
case '2018' :
$file = '机票2018';
$begin = '1514736000';
$end = '1546185600'; break;
case 'all' :
$file = '机票全部';
$begin = '1451577600';
$end = '1546185600'; break;
case 'company' :
$iscom = 1;
$file = '公司订单大于5';
$begin = '1451577600';
$end = '1546185600'; break;
}
// dd($iscom,$file,$begin,$end);
$datas = DB::select("SELECT i.id,i.email,i.comname,i.phonenum,sum(m.pricenum) AS sum FROM ticket_info AS i LEFT JOIN
ticket_man AS m ON m.iid = i.id WHERE i.ifcom !=0 AND m.pricenum > 0 AND i.jointime BETWEEN $begin AND $end GROUP BY m.iid;");
$datas = json_decode(json_encode($datas), true);

// dd($file,$datas);
if ($iscom){
$comnames = array_column($datas, 'comname');

//数组一列trim
$comnames = array_map(function ($v){
return trim($v);
},$comnames);

//计算出现的次数
$comname_count = array_count_values($comnames);
//小于5次返回空后面用去控制剔除
$comname_count = array_map(function ($v){
if ($v < 5){
return '';
}
return $v;
},$comname_count);
//去空格
$comnames = array_filter($comname_count);
//获取键和值
$comnames_key = array_keys($comnames);
$comnames_val = array_values($comnames);
//组装成二维
for ($i=0;$i<count($comnames);$i++){
$new[$i]['comname'] = $comnames_key[$i];
$new[$i]['count'] = $comnames_val[$i];
}
//
Excel::create($file,function($excel) use ($new){
$excel->sheet('score', function($sheet) use ($new){
$sheet->rows($new);
});
})->export('xls');
}else{
// dd($file,$datas);
Excel::create($file,function($excel) use ($datas){
$excel->sheet('score', function($sheet) use ($datas){
$sheet->rows($datas);
});
})->export('xls');
}

}


/*
* @todo 更新book_info memip 更新citycountry
* 2017/10/11 cyt
*/
public function index(Request $request){
ignore_user_abort(1); //链接中断 脚本继续执行
ini_set('max_execution_time', '0'); // 设置脚本执行时间为无限长
ini_set('memory_limit', '1024M');// 设置脚本临时内存,防止溢出
$db = new \IP2Location\Database(storage_path().'/IP-COUNTRY-REGION-CITY.BIN', \IP2Location\Database::FILE_IO);
$ipadds = [];$datas = [];
$te = strtotime($request->input('start')); //1451606400
$end = strtotime($request->input('end'));//1483228799
$ips = DB::select('select id,memip from book_info where memip != "" AND _boarding_date BETWEEN '.$te.' and '.$end);
foreach ($ips as $ip){
if ($ip->memip != ''){
$ipadds["$ip->id"] = $ip->memip;
}
}
foreach ($ipadds as $ipadd=>$val){
$records = $db->lookup($val, \IP2Location\Database::ALL);
$datas["$ipadd"]['city'] = $records['cityName'];
$datas["$ipadd"]['country'] = $records['countryName'];
}

$fl = $this->batchUpdate('id','city','country',$datas);
dd($fl);
// $records = $db->lookup('58.23.37.36', \IP2Location\Database::ALL);
//
// echo '<pre>';
// echo 'IP Number : ' . $records['ipNumber'] . "\n";
// echo 'IP Version : ' . $records['ipVersion'] . "\n";
// echo 'IP Address : ' . $records['ipAddress'] . "\n";
// echo 'Country Code : ' . $records['countryCode'] . "\n";
// echo 'Country Name : ' . $records['countryName'] . "\n";
// echo 'Region Name : ' . $records['regionName'] . "\n";
// echo 'City Name : ' . $records['cityName'] . "\n";
// echo 'Latitude : ' . $records['latitude'] . "\n";
// echo 'Longitude : ' . $records['longitude'] . "\n";
}

/*
* @todo 获取订单客户信息
* 参数1:时间起始 ;
* 参数2:时间结束 ;
* 参数3:文件类型
*/
public function getBookManInfo(Request $request){
$start_ = $request->input('start');
$end_ = $request->input('end');
$type = $request->input('type');
if (empty($start_) || empty($end_) || empty($type) || !in_array($type,['man','city'])){
dd('检查参数是否完整,type=mancity;示例: /getBookManInfo?start=2008/1/1&end=2008/12/31&type=man');
}
$start = strtotime($start_);
$end = strtotime($end_);
switch ($type){
case 'city' :
$filename = $start_.'-'.$end_.'顾客城市信息';
$headlist = ['国家','城市','ip','上团日期']; // 1 0
$datas = DB::select('select country,city,memip,_boarding_date from book_info where memip != "" AND _boarding_confirm = "Y" AND _boarding_date BETWEEN '.$start.' and '.$end);
foreach ($datas as $key=>$val){
$val->_boarding_date = date('Y-m-d H:m:s',$val->_boarding_date);
}
break;
case 'man' :
$filename = $start_.'-'.$end_.'顾客订单信息';
$headlist = ['订单id','性别','出生日期']; // 1 0
$datas = DB::select('Select bid, sex, birth from book_man where bid IN( SELECT id FROM `book_info` where memip != "" AND `_boarding_confirm` = "Y" AND `_boarding_date` BETWEEN '.$start.' AND '.$end.' );');
foreach ($datas as $key=>$val){
switch ($val->sex){
case '0':
$val->sex = '';
break;
case '1':
$val->sex = '';
break;
case '2':
$val->sex = '未知';
break;
}
}
break;
}
$this->csv_export($datas,$headlist,$filename);
}


/*
* 批量更新book_info的城市信息
*/
public function batchUpdate($id, $city,$country, $datas)
{

$table = 'book_info';//返回完整表名,自己在项目中定义
$sql = 'update ' . $table . ' set '. $city .' = case ' .$id;

foreach ($datas as $key => $condition) {
if(strpos($condition['city'],"'") !== false) {
$condition['city'] = str_replace("'",'_',$condition['city']);
}
$sql .= ' when ' . $key . ' then ' .'\''.$condition['city'].'\'' ;
}

$sql .= 'end,'.$country.' = case '.$id;

foreach ($datas as $key => $condition) {
if(strpos($condition['country'],"'") !== false) {
$condition['country'] = str_replace("'",'_',$condition['country']);
}
$sql .= ' when ' . $key . ' then ' .'\''.$condition['country'].'\'' ;
}

$sql .= " end where id between 1 and 200000 ";
return DB::update($sql);//项目中需要引入DB facade

}


public function getcsv(){
$headlist = ['订单id','性别','出生日期']; // 1 0
$headlist = ['国家','城市','ip','上团日期']; // 1 0

// 1-1
// $data = DB::select('Select bid, sex, birth from book_man where bid IN( SELECT id FROM `book_info` where `_boarding_date` <= 1504108800 AND `_boarding_date` >= 1472572800 AND `_boarding_confirm` = \'Y\');');
// foreach ($data as $key=>$val){
// switch ($val->sex){
// case '0':
// $val->sex = '';
// break;
// case '1':
// $val->sex = '';
// break;
// case '2':
// $val->sex = '未知';
// break;
// }
// }

// $data = DB::select('SELECT country, city, memip, `_boarding_date` FROM `book_info` where `_boarding_date` <= 1504108800 AND `_boarding_date` >= 1472572800 AND `_boarding_confirm` = \'Y\';');
// foreach ($data as $key=>$val){
// $val->_boarding_date = date('Y-m-d H:m:s',$val->_boarding_date);
// }

$data = DB::select('Select bid, sex, birth from book_man where bid IN( SELECT id FROM `book_info` where `_boarding_date` <= 1472572800 AND `_boarding_date` >= 1440950400 AND `_boarding_confirm` = \'Y\');');
foreach ($data as $key=>$val){
switch ($val->sex){
case '0':
$val->sex = '';
break;
case '1':
$val->sex = '';
break;
case '2':
$val->sex = '未知';
break;
}
}

$data = DB::select('SELECT country, city, memip, `_boarding_date` FROM `book_info` where `_boarding_date` <= 1472572800 AND `_boarding_date` >= 1440950400 AND `_boarding_confirm` = \'Y\';');
foreach ($data as $key=>$val){
$val->_boarding_date = date('Y-m-d H:m:s',$val->_boarding_date);
}

$this->csv_export($data,$headlist,'test4');
}

/**
* 导出excel(csv)
* @data 导出数据
* @headlist 第一行,列名
* @fileName 输出Excel文件名
*/
public function csv_export($data = array(), $headlist = array(), $fileName) {

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$fileName.'.csv"');
header('Cache-Control: max-age=0');

//打开PHP文件句柄,php://output 表示直接输出到浏览器
$fp = fopen('php://output', 'a');

//输出Excel列名信息
foreach ($headlist as $key => $value) {
//CSVExcel支持GBK编码,一定要转换,否则乱码
$headlist[$key] = iconv('utf-8', 'gbk', $value);
}

//将数据通过fputcsv写到文件句柄
fputcsv($fp, $headlist);

//计数器
$num = 0;

//每隔$limit行,刷新一下输出buffer,不要太大,也不要太小
$limit = 100000;

//逐行取出数据,不浪费内存
$count = count($data);

foreach ($data as $item){
$num++;

//刷新一下输出buffer,防止由于数据过多造成问题
if ($limit == $num) {
ob_flush();
flush();
$num = 0;
}

//二维数组下是对象
$file = [];
foreach ($item as $key=>$val){
// dd($item,$key,$val);
if (strpos($val,"adult")){
$val = str_replace("adult",' ',$val);
$val = trim($val);
}

//麻烦 记录的时候能不能规范个格式啊啊啊啊、、
if(strpos($val,"") !== false) {
$val = str_replace("",'.',$val);
}
if(strpos($val,"") !== false) {
$val = str_replace("",'.',$val);
}
if(strpos($val,"") !== false) {
$val = str_replace("",'.',$val);
}
// $val = substr($val, 0, 10);
if(strpos($val,"/") !== false) {
$val = str_replace("/",'.',$val);
}
if(strpos($val,"-") !== false) {
$val = str_replace("-",'.',$val);
}
if(strpos($val," ") !== false) {
$val = str_replace(" ",'.',$val);
}
if ( !$val){
$file[] = iconv('utf-8', 'gbk', '');
}else{
$file[] = iconv('utf-8', 'GBK', $val);
// $file[] = mb_convert_encoding($val, "UTF-8", "GBK");
}
}
fputcsv($fp, $file);
}

// for ($i = 0; $i < $count; $i++) {
//
// $num++;
//
// //刷新一下输出buffer,防止由于数据过多造成问题
// if ($limit == $num) {
// ob_flush();
// flush();
// $num = 0;
// }
//
// $row = $data[$i];
// foreach ($row as $key => $value) {
// $row[$key] = iconv('utf-8', 'gbk', $value);
// }
//
// fputcsv($fp, $row);
// }
}



}
最后修改:2020-04-10 00:50:39
0