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?:[]; } }