579 lines
27 KiB
PHP
579 lines
27 KiB
PHP
<?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?:[];
|
||
}
|
||
|
||
|
||
}
|