yii 框架实现按天,月,年,自定义时间段统计数据的方法分析
本文实例讲述了yii框架实现按天,月,年,自定义时间段统计数据的方法。分享给大家供大家参考,具体如下:
天(day):格式Y-m-d
月(month):格式Y-m
年(year):格式Y
时间段(range):格式Y-m-d
首先计算时间
天0-23小时
$rangeTime=range(0,23);
月:1-月底
//$days=cal_days_in_month(CAL_GREGORIAN,$month,$year); $days=date("t",strtotime($year.'-'.$month)); //生成1-days的天 $rangeTime=range(1,$days);
年:1-12月
$rangeTime=range(1,12);
时间段;开始时间-结束时间
$stimestamp=strtotime($time); $etimestamp=strtotime($time2); //计算日期段内有多少天 $days=($etimestamp-$stimestamp)/86400+1; //保存每天日期 for($i=0;$i<$days;$i++){ $newTimeStamp=$stimestamp+(86400*$i); $rangeTime[]=date('Y-m-d',$newTimeStamp); $labels[]=date('d',$newTimeStamp).Yii::t('backend','day'); }
封装一下
/** *获取label和时间段 *type:day,month,year,range *time:日期;day为具体的天y-m-d,month为具体的月y-m,year为具体的年y *time2日期,时间段的第二个时间 */ publicfunctiongetLabelAndRangeTime($type,$time,$time2){ if(empty($time)){ $time=date('Y-m-d',time()); } $labels=[]; $rangeTime=[]; if($type=='day'){ //生成1-24小时 $rangeTime=range(0,23); foreach($rangeTimeas$key=>$val){ $label=$val.Yii::t('backend','hour'); $labels[]=$label; } }elseif($type=='month'){ $dateArr=explode('-',$time); if(count($dateArr>1)){ $year=$dateArr[0]; $month=$dateArr[1]; $time=$year; $time2=$month; //获取当前年月的天数 //$days=cal_days_in_month(CAL_GREGORIAN,$month,$year); $days=date("t",strtotime($year.'-'.$month)); //生成1-days的天 $rangeTime=range(1,$days); foreach($rangeTimeas$key=>$val){ $label=$val.Yii::t('backend','day'); $labels[]=$label; } } }elseif($type=='year'){ //生成1-12月 $rangeTime=range(1,12); foreach($rangeTimeas$key=>$val){ $label=$val.Yii::t('backend','month'); $labels[]=$label; } }elseif($type=='range'){ $stimestamp=strtotime($time); $etimestamp=strtotime($time2); //计算日期段内有多少天 $days=($etimestamp-$stimestamp)/86400+1; //保存每天日期 for($i=0;$i<$days;$i++){ $newTimeStamp=$stimestamp+(86400*$i); $rangeTime[]=date('Y-m-d',$newTimeStamp); $labels[]=date('d',$newTimeStamp).Yii::t('backend','day'); } } return[ 'type'=>$type, 'time'=>$time, 'time2'=>$time2, 'rangeTime'=>$rangeTime, 'labels'=>$labels ]; }
然后查询数据库
$query=Order::find(); if($type=='day'){ $query=$query->select(['FROM_UNIXTIME(pay_at,"%Y-%m-%d%H")aschar_time','COUNT(id)astotal_order','SUM(pay_amount)astotal_order_amount']) ->where(['FROM_UNIXTIME(pay_at,"%Y-%m-%d")'=>$time]); }elseif($type=='month'){ $query=$query->select(['FROM_UNIXTIME(pay_at,"%Y-%m-%d")aschar_time','COUNT(id)astotal_order','SUM(pay_amount)astotal_order_amount']) ->where(['FROM_UNIXTIME(pay_at,"%Y-%m")'=>($time.'-'.$time2)]); }elseif($type=='year'){ $query=$query->select(['FROM_UNIXTIME(pay_at,"%Y-%m")aschar_time','COUNT(id)astotal_order','SUM(pay_amount)astotal_order_amount']) ->where(['FROM_UNIXTIME(pay_at,"%Y")'=>$time]); }elseif($type=='range'){ $query=$query->select(['FROM_UNIXTIME(pay_at,"%Y-%m-%d")aschar_time','COUNT(id)astotal_order','SUM(pay_amount)astotal_order_amount']) ->where(['between','FROM_UNIXTIME(pay_at,"%Y-%m-%d")',$time,$time2]); } $data=$query->andWhere(['pay_status'=>2])->groupBy('char_time')->all();
按时间排列下
$dataArr=[]; foreach($dataas$allKey=>$allVal){ $dataArr[$allVal->char_time]['char_time']=$allVal->char_time; $dataArr[$allVal->char_time]['total_order']=$allVal->total_order; $dataArr[$allVal->char_time]['total_order_amount']=bcdiv($allVal->total_order_amount,100,2); }
再按时间获取对应数据
foreach($rangeTimeas$key=>$val){ if($type=='range'){ if(array_key_exists($val,$dataArr)){ $charCountDatas[]=$dataArr[$val]['total_order']; $charAmountDatas[]=$dataArr[$val]['total_order_amount']; }else{ $charCountDatas[]=0; $charAmountDatas[]=0; } }else{ $theNow=strlen($val)==2?$val:'0'.$val; if($type=='day'){ $theTime=$time.''.$theNow; }elseif($type=='month'){ $theTime=$time.'-'.$time2.'-'.$theNow; }elseif($type=='year'){ $theTime=$time.'-'.$theNow; } if(array_key_exists($theTime,$dataArr)){ $charCountDatas[]=$dataArr[$theTime]['total_order']; $charAmountDatas[]=$dataArr[$theTime]['total_order_amount']; }else{ $charCountDatas[]=0; $charAmountDatas[]=0; } } }
封装下
/** *时间段内支付订单量及金额 *type类型:day,month,year *time:时间,day:选择的时间;month:表示年;year:表示年;range:第一个时间 *time2:时间:day:'';month:表示月;year:'';range:第二个时间 *rangeTime时间段day:1-24小时;month:1-30天;year:1-12月,range:time和time2之间的天 */ publicfunctiongetDayOrderPayChar($type,$time,$time2,$rangeTime){ $query=Order::find(); if($type=='day'){ $query=$query->select(['FROM_UNIXTIME(pay_at,"%Y-%m-%d%H")aschar_time','COUNT(id)astotal_order','SUM(pay_amount)astotal_order_amount']) ->where(['FROM_UNIXTIME(pay_at,"%Y-%m-%d")'=>$time]); }elseif($type=='month'){ $query=$query->select(['FROM_UNIXTIME(pay_at,"%Y-%m-%d")aschar_time','COUNT(id)astotal_order','SUM(pay_amount)astotal_order_amount']) ->where(['FROM_UNIXTIME(pay_at,"%Y-%m")'=>($time.'-'.$time2)]); }elseif($type=='year'){ $query=$query->select(['FROM_UNIXTIME(pay_at,"%Y-%m")aschar_time','COUNT(id)astotal_order','SUM(pay_amount)astotal_order_amount']) ->where(['FROM_UNIXTIME(pay_at,"%Y")'=>$time]); }elseif($type=='range'){ $query=$query->select(['FROM_UNIXTIME(pay_at,"%Y-%m-%d")aschar_time','COUNT(id)astotal_order','SUM(pay_amount)astotal_order_amount']) ->where(['>=','FROM_UNIXTIME(pay_at,"%Y-%m-%d")',$time]) ->andWhere(['<=','FROM_UNIXTIME(pay_at,"%Y-%m-%d")',$time2]); } $data=$query->andWhere(['pay_status'=>2])->groupBy('char_time')->all(); $dataArr=[]; foreach($dataas$allKey=>$allVal){ $dataArr[$allVal->char_time]['char_time']=$allVal->char_time; $dataArr[$allVal->char_time]['total_order']=$allVal->total_order; $dataArr[$allVal->char_time]['total_order_amount']=bcdiv($allVal->total_order_amount,100,2); } $charCountDatas=[]; $charAmountDatas=[]; foreach($rangeTimeas$key=>$val){ if($type=='range'){ if(array_key_exists($val,$dataArr)){ $charCountDatas[]=$dataArr[$val]['total_order']; $charAmountDatas[]=$dataArr[$val]['total_order_amount']; }else{ $charCountDatas[]=0; $charAmountDatas[]=0; } }else{ $theNow=strlen($val)==2?$val:'0'.$val; if($type=='day'){ $theTime=$time.''.$theNow; }elseif($type=='month'){ $theTime=$time.'-'.$time2.'-'.$theNow; }elseif($type=='year'){ $theTime=$time.'-'.$theNow; } if(array_key_exists($theTime,$dataArr)){ $charCountDatas[]=$dataArr[$theTime]['total_order']; $charAmountDatas[]=$dataArr[$theTime]['total_order_amount']; }else{ $charCountDatas[]=0; $charAmountDatas[]=0; } } } $res=[ 'count'=>[ 'name'=>Yii::t('backend','hour_order_pay_count_title'), 'color'=>'#99CC33', 'charData'=>$charCountDatas ], 'amount'=>[ 'name'=>Yii::t('backend','hour_order_pay_amount_title'), 'color'=>'#99CC33', 'charData'=>$charAmountDatas ] ]; return$res; }
前端
=Html::dropDownList('day_type',$type,['day'=>Yii::t('backend','day'),'month'=>Yii::t('backend','month'),'year'=>Yii::t('backend','year'),'range'=>Yii::t('backend','range_time')],['class'=>'typedashboard-time-type'])?>