dianliang/canyin-project
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
canyin-project/ybcy/models/common/Statistics.php

579 lines
27 KiB
PHP
Raw Permalink Normal View History

init
2024-11-01 16:07:54 +08:00
<?php
namespace app\models\common;
use yii\db\ActiveRecord;
use app\models\common\Config;
use yii\db\Expression;
class Statistics extends ActiveRecord{
public static function origin() {
return [1];
// return [1,4,5,6,7];
}
public static function state() {
return [1,2];
}
public static function inStoreOrigin(){
return [5,6,7];
}
//门店排行
public static function shopPayAsc($uniacid){
$data=(new \yii\db\Query())
->select('a.storeId,name,sum(money)money,count(*)count')
->from('{{%ybwm_bill}} a')
->leftJoin('{{%ybwm_store}} b','a.storeId=b.id')
->where('a.uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()])
->andWhere('refundAt=0 or refundAt is null')
->andWhere(['>','name',''])
->groupBy('storeId')
->all();
return $data;
}
//计算营业额每日折线图
public static function getShopMoneyDayChart($uniacid,$storeId=null,$startTime=null,$endTime=null){
$money=(new \yii\db\Query())
->select(new Expression("from_unixtime(statisticsAt,'%Y-%m-%d') as hours,sum(money)money"))
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$data=$money->groupBy(new Expression("from_unixtime(statisticsAt,'%Y-%m-%d')"))->all();
return $data;
}
//计算营业额每日折线图
public static function getShopMoneyChart($uniacid,$storeId=null,$startTime=null,$endTime=null){
$money=(new \yii\db\Query())
->select(new Expression("from_unixtime(statisticsAt,'%H') as hours,sum(money)money"))
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$data=$money->groupBy(new Expression("from_unixtime(statisticsAt,'%H')"))->all();
//var_dump(ddSql($data));die;
return $data;
}
//计算营业额
public static function getShopMoney($uniacid,$storeId=null,$startTime=null,$endTime=null){
$money=(new \yii\db\Query())
->select('FROM_UNIXTIME(createdAt)hours,sum(money)money')
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$data=$money->groupBy('money')->all();
return $data;
}
//计算下单金额
public static function getOrderMoney($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->select(['sum(money) as money'])
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$data=$money->one();
return $data['money'];
}
//计算支付金额
public static function getPayMoney($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->select(['sum(money) as money'])
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$money->andWhere('refundAt=0 or refundAt is null');
$data=$money->one();
return $data['money'];
}
//待结算金额
public static function getMoneyType($uniacid,$storeId=null,$type=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->select(['sum(money) as money'])
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($startTime&&$endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$data=$money->one();
return $data['money'];
}
//计算支付订单数
public static function getShopPayDayChart($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->select(new Expression("from_unixtime(statisticsAt,'%Y-%m-%d') as hours,count(*)money"))
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($startTime&&$endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$data=$money->groupBy(new Expression("from_unixtime(statisticsAt,'%Y-%m-%d')"))->all();
return $data?:0;
}
//计算支付订单数
public static function getShopPayChart($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->select(new Expression("from_unixtime(statisticsAt,'%H') as hours,count(*)money"))
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($startTime&&$endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$data=$money->groupBy(new Expression("from_unixtime(statisticsAt,'%H')"))->all();
return $data?:0;
}
//计算支付订单数
public static function getShopPay($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->select('FROM_UNIXTIME(createdAt)hours,count(*)money')
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($startTime&&$endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$money->andWhere('refundAt>0 or refundAt is not null');
$data=$money->groupBy('money')->all();
return $data?:0;
}
//计算支付订单数
public static function getPayNum($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid]);
//->andWhere(['in','origin',self::origin()]);
if($endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$money->andWhere('refundAt=0 or refundAt is null');
$data=$money->count();
return $data?:0;
}
//计算成功退款金额
public static function getrefundMoney($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($startTime&&$endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
}
$data=$money->sum('refundMoney');
//dd(ddSql($data));die;
return $data?:0;
}
//计算下单人数
public static function getPayOrderNum($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
}
$data=$money->groupby('userId')->count();
//dd(ddSql($data));die;
return $data?:0;
}
//计算支付人数
public static function getPayPeople($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid]);
//->andWhere(['in','origin',self::origin()]);
if($endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
}
$money->andWhere('refundMoney=0 or refundMoney is null');
$data=$money->groupby('userId')->count();
//dd(ddSql($data));die;
return $data?:0;
}
//计算客单价
public static function getUnitPrice($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=self::getPayMoney($uniacid,$storeId,$startTime,$endTime);
$num=self::getPayNum($uniacid,$storeId,$startTime,$endTime);
//dd($money); dd($num);die;
return bcdiv($money,$num,2);
}
//计算浏览量
public static function getViews($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->from('{{%ybwm_visit_list}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid]);
if($endTime){
$money->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
}
$data=$money->count();
return $data?:0;
}
//计算访客数
public static function getVisitCount($uniacid,$storeId=null,$startTime=null,$endTime=null,$type=1) {
$money=(new \yii\db\Query())
->from('{{%ybwm_visit_list}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid]);
if($startTime&&$endTime){
$money->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
}
if($type){
$money->andWhere('type=:type',[':type'=>$type]);
}
//var_dump(ddSql($money->groupBy('userId')));die;
$data=$money->groupBy('userId')->count();
return $data?:0;
}
//计算转换率 (到店访客数/下单人数)× 100%
public static function getConversion($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$visitCount=self::getVisitCount($uniacid,$storeId,$startTime,$endTime,1);
$payNum=self::getPayPeople($uniacid,$storeId,$startTime,$endTime);
return bcmul(bcdiv($payNum,$visitCount,2),100,2).'%';
}
/**查看时间段
* @param $time[时间]
*/
public static function getTimeArr($time) {
$num=24;
$data=[];
for($i=1;$i<=$num;$i++){
$startTime=strtotime($time)+($i-1)*60*60;
$endTime=strtotime($time)+$i*60*60;
if($num==24){
$endTime=$endTime-1;
}
$data[]=array(
'number'=>$i,
'startTime'=>$startTime,
'endTime'=>$endTime,
);
}
return $data;
}
/**查看天数段
* @param $month[月份]
*/
public static function getDayArr($month) {
$day=date('t', strtotime($month));
$numArr=explode('-',$month);
$timeArr=mFristAndLast($numArr[0],$numArr[1]);
$data=[];
for($i=1;$i<=$day;$i++){
$startTime=$timeArr['startTime']+86400*($i-1);
$endTime=$timeArr['startTime']+86399+86400*($i-1);
$data[]=array(
'number'=>$i,
'startTime'=>$startTime,
'endTime'=>$endTime,
);
}
return $data;
}
//计算预计收入
public function revenue($uniacid,$storeId=null,$startTime=null,$endTime=null){
$money=(new \yii\db\Query())
->select(['sum(storeActualMoney) as money'])
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($startTime&&$endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$data=$money->one();
// var_dump(ddSql($data));
return $data['money']?:0.00;
}
//计算有效订单数
public function payOkNum($uniacid,$storeId=null,$startTime=null,$endTime=null){
$money=(new \yii\db\Query())
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid and (refundMoney=0 or refundMoney is null)',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::origin()]);
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
if($startTime&&$endTime){
$money->andWhere('statisticsAt>=:startTime AND statisticsAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
$data=$money->count();
return $data?:0;
}
//已提现金额
public static function getUseMoney($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$money=(new \yii\db\Query())
->select(['sum(money) as money'])
->from('{{%ybwm_store_finance}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','state',self::state()]);
if($startTime&&$endTime){
$money->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$money->andWhere('storeId=:storeId',['storeId'=>$storeId]);
}
$data=$money->one();
return $data['money'];
}
//店内营业额
public static function inStoreMoney($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$table=(new \yii\db\Query())
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid AND (refundMoney=0 or refundMoney is null)',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::inStoreOrigin()]);
if($startTime&&$endTime){
$table->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
}
$money=$table->sum('money');
return $money?:0;
}
//店内有效订单数
public static function inStoreNum($uniacid,$storeId=null,$startTime=null,$endTime=null) {
$table=(new \yii\db\Query())
->from('{{%ybwm_bill}}')
->where('uniacid=:uniacid AND (refundMoney=0 or refundMoney is null)',[':uniacid'=>$uniacid])
->andWhere(['in','origin',self::inStoreOrigin()]);
if($startTime&&$endTime){
$table->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
if($storeId){
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
}
$count=$table->count();
return $count?:0;
}
//店内桌子数
public static function inStoreTableNum($uniacid,$storeId=null) {
$table=(new \yii\db\Query())
->from('{{%ybwm_table}}')
->where('uniacid=:uniacid AND deleteAt=0',[':uniacid'=>$uniacid]);
if($storeId){
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
}
$count=$table->count();
return $count?:0;
}
//店内打印机数
public static function inStorePrintNum($uniacid,$storeId=null) {
$table=(new \yii\db\Query())
->from('{{%ybwm_print}}')
->where('uniacid=:uniacid AND deleteAt=0 ',[':uniacid'=>$uniacid])
->andWhere(['<>','support', 1]);
if($storeId){
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
}
$count=$table->count();
return $count?:0;
}
//店内概况
public static function inStoreSurvey($uniacid,$storeId,$startTime=null,$endTime=null) {
$instoreTable=(new \yii\db\Query())
->from('{{%ybwm_instore_order}} a')
->leftJoin('{{%ybwm_bill}} b','a.id=b.orderId')
->where(['a.uniacid'=>$uniacid,'orderMode'=>1,'b.origin'=>7])
->andWhere(['a.storeId'=>$storeId])
->andWhere(['in','state',[3]]);
if($startTime){
$instoreTable=$instoreTable->andWhere('payAt>=:startTime AND payAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
}
$data['instorePay']=(clone $instoreTable)->sum('a.money')?:0;//堂食营业额
$data['instoreNum']=(clone $instoreTable)->count()?:0;//订单数
$data['instoreActualMoney']=(clone $instoreTable)->sum('storeActualMoney')?:0;//预计收入
$data['instoreWxPaySum']=(clone $instoreTable)->andWhere(['a.origin'=>1])->count()?:0;//堂食微信营业额
$data['instoreAliPaySum']=(clone $instoreTable)->andWhere(['a.origin'=>3])->count()?:0;//堂食支付宝营业额
$data['instoreCashierPaySum']=(new \yii\db\Query())
->from('{{%ybwm_instore_order}} a')
->leftJoin('{{%ybwm_bill}} b','a.id=b.orderId')
->where(['a.uniacid'=>$uniacid,'orderMode'=>1])
->andWhere(['a.storeId'=>$storeId])
->andWhere(['in','state',[3]])->andWhere(['a.origin'=>5])->count()?:0;//堂食收银台营业额
$data['instoreWxPay']=(clone $instoreTable)->andWhere(['a.payMode'=>1])->sum('a.money')?:0;//堂食收银台营业额
$data['instoreAliPay']=(clone $instoreTable)->andWhere(['a.payMode'=>2])->sum('a.money')?:0;//堂食收银台营业额
$data['instoreBalancePay']=(clone $instoreTable)->andWhere(['a.payMode'=>5])->sum('a.money')?:0;//堂食收银台营业额
$data['instoreMoneyPay']=(clone $instoreTable)->andWhere(['a.payMode'=>6])->sum('a.money')?:0;//堂食收银台营业额
$data['instoreMyselfPay']=(clone $instoreTable)->andWhere(['in','a.payMode',[7,8,9]])->sum('a.money')?:0;//堂食收银台营业额
$fastTable = (new \yii\db\Query())
->from('{{%ybwm_instore_order}} a')
->leftJoin('{{%ybwm_bill}} b','a.id=b.orderId')
->where(['a.uniacid'=>$uniacid,'orderMode'=>2,'b.origin'=>6])
->andWhere(['in', 'state', [2, 3, 4, 6]])
->andWhere(['a.storeId'=>$storeId]);
if($startTime){
$fastTable ->andWhere('payAt>=:startTime AND payAt<=:endTime', [':startTime' => $startTime, ':endTime' => $endTime]);
}
$data['fastPay'] = (clone $fastTable)->sum('a.money') ?: 0;//快餐金额
$data['fastNum']=(clone $fastTable)->count()?:0;//订单数
$data['fastActualMoney']=(clone $fastTable)->sum('storeActualMoney')?:0;//预计收入
$data['fastWxPaySum']=(clone $fastTable)->andWhere(['a.origin'=>1])->count()?:0;//堂食微信营业额
$data['fastAliPaySum']=(clone $fastTable)->andWhere(['a.origin'=>3])->count()?:0;//堂食支付宝营业额
$data['fastCashierPaySum']=(new \yii\db\Query())
->from('{{%ybwm_instore_order}} a')
->leftJoin('{{%ybwm_bill}} b','a.id=b.orderId')
->where(['a.uniacid'=>$uniacid,'orderMode'=>2])
->andWhere(['in', 'state', [2, 3, 4, 6]])
->andWhere(['a.storeId'=>$storeId])->andWhere(['a.origin'=>5])->count()?:0;//堂食收银台营业额
$data['fastWxPay']=(clone $fastTable)->andWhere(['a.payMode'=>1])->sum('a.money')?:0;//堂食收银台营业额
$data['fastAliPay']=(clone $fastTable)->andWhere(['a.payMode'=>2])->sum('a.money')?:0;//堂食收银台营业额
$data['fastBalancePay']=(clone $fastTable)->andWhere(['a.payMode'=>5])->sum('a.money')?:0;//堂食收银台营业额
$data['fastMoneyPay']=(clone $fastTable)->andWhere(['a.payMode'=>6])->sum('a.money')?:0;//堂食收银台营业额
$data['fastMyselfPay']=(clone $fastTable)->andWhere(['in','a.payMode',[7,8,9]])->sum('a.money')?:0;//堂食收银台营业额
return $data?:[];
}
//店内数据
public static function inStoreData($uniacid,$origin,$storeId=null,$startTime=null,$endTime=null) {
if($origin==7){
$instoreTable=(new \yii\db\Query())
->from('{{%ybwm_instore_order}}')
->where('uniacid=:uniacid AND orderMode=1',[':uniacid'=>$uniacid])
->andWhere(['in','state',[3]])
->andWhere('payAt>=:startTime AND payAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime])
->andWhere('storeId=:storeId',['storeId'=>$storeId]);
$data['instorePay']=(clone $instoreTable)->sum('money')?:0;//堂食营业额
$data['instoreCount']=(clone $instoreTable)->andWhere('payMode!=6')->count()?:0;//堂食订单数
$data['instoreActualPay']=(clone $instoreTable)->andWhere('payMode!=6')->sum('money')?:0;//堂食实际金额
$data['instoreWxPay']=(clone $instoreTable)->andWhere('payMode=1')->sum('money')?:0;//堂食微信支付
$data['instoreBalancePay']=(clone $instoreTable)->andWhere('payMode=5')->sum('money')?:0;//堂食余额支付
$data['instoreBalanceOfflinePay']=(clone $instoreTable)->andWhere('payMode=6')->sum('money')?:0;//堂食线下订单金额
$data['instoreBalanceOfflineCount']=(clone $instoreTable)->andWhere('payMode=6')->count()?:0;//堂食线下订单数
return $data?:[];
}
if($origin==6) {
$fastTable = (new \yii\db\Query())
->from('{{%ybwm_instore_order}}')
->where('uniacid=:uniacid AND orderMode=2', [':uniacid' => $uniacid])
->andWhere(['in', 'state', [2, 3, 4, 6]])
->andWhere('payAt>=:startTime AND payAt<=:endTime', [':startTime' => $startTime, ':endTime' => $endTime])
->andWhere('storeId=:storeId', ['storeId' => $storeId]);
$data['fastPay'] = (clone $fastTable)->sum('money') ?: 0;//快餐金额
$data['fastBalancePay'] = (clone $fastTable)->andWhere('payMode=5')->sum('money') ?: 0;//快餐余额支付
$data['fastWxPay'] = (clone $fastTable)->andWhere('payMode=1')->sum('money') ?: 0;//快餐微信支付
$data['fastCount'] = (clone $fastTable)->count() ?: 0;//快餐订单数
$data['fastUser'] = (clone $fastTable)->groupBy('userId')->count() ?: 0;//快餐支付人数
}
if($origin==5) {
$cashierTable=(new \yii\db\Query())
->from('{{%ybwm_cashier_order}}')
->where('uniacid=:uniacid',[':uniacid'=>$uniacid])
->andWhere(['in','state',[2]])
->andWhere('payAt>=:startTime AND payAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime])
->andWhere('storeId=:storeId',['storeId'=>$storeId]);
$data['cashierCount']=(clone $cashierTable)->count()?:0;//当面付订单数
$data['cashierUser']=(clone $cashierTable)->groupBy('userId')->count()?:0;//当面付支付人数
$data['cashierPay']=(clone $cashierTable)->sum('money')?:0;//当面付金额
$data['cashierWxPay']=(clone $cashierTable)->andWhere('payMode=1')->sum('money')?:0;//当面付微信支付
$data['cashierBalancePay']=(clone $cashierTable)->andWhere('payMode=5')->sum('money')?:0;//当面付微信支付
}
return $data?:[];
}
//店内商品排行
public static function inStoreGoodsData($uniacid,$item,$storeId=null,$startTime=null,$endTime=null) {
$table=(new \yii\db\Query())
->select('a.name,sum(a.num)num,sum(a.money*a.num) money')
->from('{{%ybwm_order_goods}} a')
// ->leftJoin('{{%ybwm_core_goods}} b','a.goodsId=b.id')
->leftJoin('{{%ybwm_instore_order}} as c','a.orderId=c.id')
->where('a.uniacid=:uniacid AND a.item=:item AND a.addType!=3',[':uniacid'=>$uniacid,':item'=>$item]);
if($item==2){
$table->andWhere(['in','c.state',[2,3,4,6]]);
}
if($item==3){
$table->andWhere('(c.eatType=1 AND c.state=3) or (c.eatType=2 AND c.state in (1,2,3,4))');
}
if($storeId){
$table->andWhere('a.storeId=:storeId',[':storeId'=>$storeId]);
}
if($startTime&&$endTime){
$table->andWhere(['<','a.createdAt',$startTime])
->andWhere(['>','a.createdAt',$endTime]);
}
$goodsData=$table->groupBy('goodsId')
->orderBy('num desc')
->limit(10)->all()?:[];
foreach ($goodsData as $key=>$v){
$goodsData[$key]['id']=$key+1;
}
return $goodsData?:[];
}
}
Reference in New Issue Copy Permalink