522 lines
27 KiB
PHP
522 lines
27 KiB
PHP
<?php
|
||
/**
|
||
* 云贝餐饮连锁V2版权说明：
|
||
|
||
******************************************************************
|
||
|
||
《云贝餐饮连锁V2》是武汉云贝网络科技有限公司历经2余年时间独立创作开发而成，且在不断的进行升级开发中，《云贝餐饮连锁V2 》的源代码、布局、界面设计、电子文档等均已提交国家版权局登记备案，并已获得著作权审批 和销售许可 （软著登字第 6839058号 登记号 2021SR0114741）。
|
||
|
||
官方网址为：www.b-ke.cn
|
||
|
||
官方联系方式：153-0719-3890（王经理）
|
||
|
||
******************************************************************
|
||
|
||
注意：本软件非开源系统，版权归武汉云贝网络科技有限公司依法所有，并受到法律的严格保护；未经过我司授权，不得擅自二开、破解、倒卖等任何的侵权、盗版行为均将追究其法律责任。
|
||
*/
|
||
namespace app\models\common;
|
||
use yii\db\Expression;
|
||
use yii\db\ActiveRecord;
|
||
class Financial extends ActiveRecord{
|
||
//获取营业额
|
||
public static function getTurnover($startTime,$endTime,$uniacid,$storeId,$origin=[1],$payMode=[]){
|
||
$table=(new \yii\db\Query())
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt=0 || refundAt is null)',[':uniacid'=>$uniacid]);
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($startTime){
|
||
$table->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
if($payMode){
|
||
$table->andWhere(['in','payMode',$payMode]);
|
||
}
|
||
|
||
$data=$table->sum('money')?:0;
|
||
return $data;
|
||
}
|
||
//获取外卖订单详细金额
|
||
public static function getOrderInfoMoney($startTime,$endTime,$uniacid,$storeId){
|
||
$table=(new \yii\db\Query())
|
||
->select(['ifnull(b.deliveryMoney,0) as deliveryMoney','ifnull(b.boxMoney,0) as boxMoney','ifnull(b.money-(b.boxMoney+b.deliveryMoney),0) as goodsMoney'])
|
||
->from('{{%ybwm_bill}} as a')
|
||
->leftJoin('{{%ybwm_takeout_order}} b','a.orderId=b.id AND a.origin=1')
|
||
->where('a.uniacid=:uniacid AND (a.refundAt=0 || a.refundAt is null)',[':uniacid'=>$uniacid]);
|
||
if($startTime){
|
||
$table->andWhere('a.createdAt>=:startTime AND a.createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('a.storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$data=$table->one();
|
||
return $data;
|
||
}
|
||
//获取店内订单详细金额
|
||
public static function getInStoreOrderInfoMoney($startTime,$endTime,$uniacid,$storeId){
|
||
$table=(new \yii\db\Query())
|
||
->select(['ifnull(b.tablewareMoney,0) as boxMoney','ifnull(b.money-b.tablewareMoney,0) as goodsMoney'])
|
||
->from('{{%ybwm_bill}} as a')
|
||
->leftJoin('{{%ybwm_instore_order}} b','a.orderId=b.id AND a.origin in(6,7)')
|
||
->where('a.uniacid=:uniacid AND (a.refundAt=0 || a.refundAt is null)',[':uniacid'=>$uniacid]);
|
||
if($startTime){
|
||
$table->andWhere('a.createdAt>=:startTime AND a.createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('a.storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$data=$table->one();
|
||
return $data;
|
||
}
|
||
//获取补贴
|
||
public static function getSubsidy($startTime,$endTime,$uniacid,$storeId,$origin=[1]){
|
||
$table=(new \yii\db\Query())
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt=0 || refundAt is null)',[':uniacid'=>$uniacid]);
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($startTime){
|
||
$table->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$data=$table->sum('subsidy')?:0;
|
||
return $data;
|
||
}
|
||
//获取服务费
|
||
public static function getService($startTime,$endTime,$uniacid,$storeId,$origin=[1]){
|
||
$table=(new \yii\db\Query())
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt=0 || refundAt is null)',[':uniacid'=>$uniacid]);
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($startTime){
|
||
$table->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$data=$table->sum('money-storeActualMoney+subsidy')?:0;
|
||
return $data;
|
||
}
|
||
//获取预计收入
|
||
public static function getIncome($startTime,$endTime,$uniacid,$storeId,$origin=[1]){
|
||
$table=(new \yii\db\Query())
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt=0 || refundAt is null)',[':uniacid'=>$uniacid]);
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($startTime){
|
||
$table->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
|
||
$data=$table->sum('storeActualMoney')?:0;
|
||
return $data;
|
||
}
|
||
//获取有效订单
|
||
public static function getEffective($startTime,$endTime,$uniacid,$storeId,$origin=[1]){
|
||
$table=(new \yii\db\Query())
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt=0 || refundAt is null)',[':uniacid'=>$uniacid]);
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($startTime){
|
||
$table->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$data=$table->count()?:0;
|
||
return $data;
|
||
}
|
||
//获取无效订单
|
||
public static function getInvalidOrder($startTime,$endTime,$uniacid,$storeId,$origin=[1]){
|
||
$table=(new \yii\db\Query())
|
||
->select('count(id)count,sum(refundMoney)money')
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt>0 || refundAt is not null)',[':uniacid'=>$uniacid]);
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($startTime){
|
||
$table->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$data=$table->one();
|
||
return $data;
|
||
}
|
||
//获取客单价
|
||
public static function getUnitPrice($startTime,$endTime,$uniacid,$storeId,$origin=[1]){
|
||
$money=self::getTurnover($startTime,$endTime,$uniacid,$storeId,$origin);
|
||
$num=self::getEffective($startTime,$endTime,$uniacid,$storeId,$origin);
|
||
return bcdiv($money,$num,2);
|
||
}
|
||
//获取营业额统计
|
||
public static function getTurnoverArr($startTime,$endTime,$uniacid,$storeId,$origin=[1]){
|
||
$table= (new \yii\db\Query())
|
||
->select(new Expression("from_unixtime(createdAt,'%Y-%m-%d') as hours,sum(money)money"))
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt=0 || refundAt is null)',[':uniacid'=>$uniacid])
|
||
->andWhere('createdAt>=:starTime AND createdAt<=:endTime',[':starTime'=>$startTime,':endTime'=>$endTime]);
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$moneyArr=$table->groupBy(new Expression("from_unixtime(createdAt,'%Y-%m-%d')"))->all();
|
||
return dataByTime($moneyArr,date("Y-m-d",$endTime));
|
||
}
|
||
//获取预计收入统计
|
||
public static function getIncomeArr($startTime,$endTime,$uniacid,$storeId,$origin=[1]){
|
||
$table= (new \yii\db\Query())
|
||
->select(new Expression("from_unixtime(createdAt,'%Y-%m-%d') as hours,sum(storeActualMoney)money"))
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt=0 || refundAt is null)',[':uniacid'=>$uniacid])
|
||
->andWhere('createdAt>=:starTime AND createdAt<=:endTime',[':starTime'=>$startTime,':endTime'=>$endTime]);
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$moneyArr=$table->groupBy(new Expression("from_unixtime(createdAt,'%Y-%m-%d')"))->all();
|
||
return dataByTime($moneyArr,date("Y-m-d",$endTime));
|
||
}
|
||
//获取有效订单统计
|
||
public static function getEffectiveArr($startTime,$endTime,$uniacid,$storeId,$origin=[1]){
|
||
$table= (new \yii\db\Query())
|
||
->select(new Expression("from_unixtime(createdAt,'%Y-%m-%d') as hours,count(id)money"))
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt=0 || refundAt is null)',[':uniacid'=>$uniacid])
|
||
->andWhere('createdAt>=:starTime AND createdAt<=:endTime',[':starTime'=>$startTime,':endTime'=>$endTime]);
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$moneyArr=$table->groupBy(new Expression("from_unixtime(createdAt,'%Y-%m-%d')"))->all();
|
||
return dataByTime($moneyArr,date("Y-m-d",$endTime));
|
||
}
|
||
//获取无效订单统计
|
||
public static function getInvalidOrderArr($startTime,$endTime,$uniacid,$storeId,$origin=[1]){
|
||
$table= (new \yii\db\Query())
|
||
->select(new Expression("from_unixtime(createdAt,'%Y-%m-%d') as hours,count(id)money"))
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt>0 || refundAt is not null)',[':uniacid'=>$uniacid])
|
||
->andWhere('createdAt>=:starTime AND createdAt<=:endTime',[':starTime'=>$startTime,':endTime'=>$endTime]);
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$moneyArr=$table->groupBy(new Expression("from_unixtime(createdAt,'%Y-%m-%d')"))->all();
|
||
return dataByTime($moneyArr,date("Y-m-d",$endTime));
|
||
}
|
||
|
||
|
||
//获取有效订单渠道
|
||
public static function getEffectiveChannel($startTime,$endTime,$uniacid,$storeId,$origin=0,$payMode=0,$deliveryMode=0,$money=2){
|
||
$table=(new \yii\db\Query())
|
||
->from('{{%ybwm_bill}} a')
|
||
->leftJoin('{{%ybwm_takeout_order}} b','a.orderId=b.id')
|
||
->where('a.uniacid=:uniacid AND (a.refundAt=0 || a.refundAt is null) AND a.origin=1',[':uniacid'=>$uniacid]);
|
||
if($startTime){
|
||
$table->andWhere('a.createdAt>=:startTime AND a.createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('a.storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
if($origin){
|
||
//订单来源(1.微信小程序3支付宝)
|
||
$table->andWhere('b.origin=:origin',[':origin'=>$origin]);
|
||
}
|
||
if($payMode){
|
||
//支付方式
|
||
$table->andWhere('b.payMode=:payMode',[':payMode'=>$payMode]);
|
||
}
|
||
if($deliveryMode==1){
|
||
//配送方式(1.外卖10自提)
|
||
$table->andWhere('b.deliveryMode!=:deliveryMode',[':deliveryMode'=>10]);
|
||
}
|
||
if($deliveryMode==2){
|
||
//配送方式(1.外卖10自提)
|
||
$table->andWhere('b.deliveryMode=:deliveryMode',[':deliveryMode'=>10]);
|
||
}
|
||
if($money==1){
|
||
$data=$table->sum('a.money')?:0;
|
||
}else{
|
||
$data=$table->count()?:0;
|
||
}
|
||
|
||
return $data;
|
||
}
|
||
//获取有效订单渠道(店内)
|
||
public static function getEffectiveInStoreChannel($startTime,$endTime,$uniacid,$storeId,$item=0,$origin=0,$payMode=0){
|
||
$table=(new \yii\db\Query())
|
||
->from('{{%ybwm_bill}} a')
|
||
->leftJoin('{{%ybwm_instore_order}} b','a.orderId=b.id')
|
||
->where('a.uniacid=:uniacid AND (a.refundAt=0 || a.refundAt is null) AND a.origin in (6,7)',[':uniacid'=>$uniacid]);
|
||
if($startTime){
|
||
$table->andWhere('a.createdAt>=:startTime AND a.createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('a.storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
if($item){
|
||
//6快餐7店内
|
||
$table->andWhere('a.origin=:origin',[':origin'=>$item]);
|
||
}
|
||
if($origin){
|
||
//订单来源(1.微信小程序3支付宝5收银台)
|
||
$table->andWhere('b.origin=:origin',[':origin'=>$origin]);
|
||
}
|
||
if($payMode){
|
||
//支付方式1微信支付2支付宝支付5.余额支付6线下结账 大于6 自有
|
||
if($payMode>6){
|
||
$table->andWhere('a.payMode>:payMode',[':payMode'=>$payMode]);
|
||
}else{
|
||
$table->andWhere('a.payMode=:payMode',[':payMode'=>$payMode]);
|
||
}
|
||
}
|
||
$data=$table->count()?:0;
|
||
return $data;
|
||
}
|
||
|
||
|
||
|
||
//获取所有统计
|
||
public static function getAllArr($timeArr,$uniacid,$storeId,$type=1){
|
||
$arr=[];
|
||
$timeArr=array_reverse($timeArr);
|
||
for($i=0;$i<count($timeArr);$i++){
|
||
$startTime=strtotime($timeArr[$i].'00:00:00');
|
||
$endTime=strtotime($timeArr[$i].'23:59:59');
|
||
if($type==1) {
|
||
$origin=[1];
|
||
$table = (new \yii\db\Query())
|
||
->select(['ifnull(sum(if(b.deliveryMode=10,1,0)),0) as selfCount','ifnull(sum(if(b.deliveryMode!=10,1,0)),0) as takeOutCount','ifnull(sum(b.originMoney+b.boxMoney+b.deliveryMoney+b.deliveryPreferential),0) as originMoney','ifnull(sum((b.originMoney+b.boxMoney+b.deliveryMoney+b.deliveryPreferential)-b.money),0) as discountMoney','count(a.id) as count', 'ifnull(sum(If(a.type=2,1,0)),0) as completeCount', 'ifnull(count(distinct a.userId),0) as people',
|
||
'ifnull(sum(a.money),0) as money', 'ifnull(sum(a.storeActualMoney),0) as storeActualMoney', 'ifnull(sum(a.subsidy),0) as subsidy',
|
||
'ifnull(sum(a.money-a.storeActualMoney+a.subsidy),0) as serviceMoney', 'ifnull(sum(a.storeDeliveryMoney),0) as storeDeliveryMoney','ifnull(sum(a.storeBoxMoney),0) as storeBoxMoney'])
|
||
->from('{{%ybwm_bill}} as a')
|
||
->leftJoin('{{%ybwm_takeout_order}} b','a.outTradeNo=b.outTradeNo')
|
||
->where('a.uniacid=:uniacid AND (a.refundAt=0 || a.refundAt is null)', [':uniacid' => $uniacid]);
|
||
}
|
||
if($type==2) {
|
||
$origin=[6,7];
|
||
$table = (new \yii\db\Query())
|
||
->select(['sum(if(b.orderMode=1,1,0)) as inStoreCount','sum(if(b.orderMode=2,1,0)) as fastCount','ifnull(sum(b.originMoney+b.tablewareMoney),0) as originMoney','ifnull(sum((b.originMoney+b.tablewareMoney)-b.money),0) as discountMoney','count(a.id) as count', 'ifnull(sum(If(a.type=2,1,0)),0) as completeCount', 'ifnull(count(distinct a.userId),0) as people',
|
||
'ifnull(sum(a.money),0) as money', 'ifnull(sum(a.storeActualMoney),0) as storeActualMoney', 'ifnull(sum(a.subsidy),0) as subsidy',
|
||
'ifnull(sum(a.money-a.storeActualMoney+a.subsidy),0) as serviceMoney', 'ifnull(sum(a.storeDeliveryMoney),0) as storeDeliveryMoney'])
|
||
->from('{{%ybwm_bill}} as a')
|
||
->leftJoin('{{%ybwm_instore_order}} b','a.outTradeNo=b.outTradeNo')
|
||
->where('a.uniacid=:uniacid AND (a.refundAt=0 || a.refundAt is null)', [':uniacid' => $uniacid]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('a.storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
if($startTime){
|
||
$table->andWhere('a.createdAt>=:startTime AND a.createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($origin){
|
||
$table->andWhere(['in','a.origin',$origin]);
|
||
}
|
||
$data=$table->one();
|
||
$data['refundMoney']=self::getInvalidOrder($startTime,$endTime,$uniacid,$storeId,$origin)['money']?:0;
|
||
$data['time']=$timeArr[$i];
|
||
$arr[]=$data;
|
||
}
|
||
return $arr;
|
||
|
||
}
|
||
//获取支付方式统计
|
||
public static function getPayModeArr($startTime,$endTime,$uniacid,$storeId,$origin=[1],$payMode=[1]){
|
||
$table= (new \yii\db\Query())
|
||
->select(new Expression("from_unixtime(createdAt,'%Y-%m-%d') as hours,sum(money)money"))
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt=0 || refundAt is null)',[':uniacid'=>$uniacid])
|
||
->andWhere('createdAt>=:starTime AND createdAt<=:endTime',[':starTime'=>$startTime,':endTime'=>$endTime]);
|
||
if($payMode){
|
||
$table->andWhere(['in','payMode',$payMode]);
|
||
}
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
$moneyArr=$table->groupBy(new Expression("from_unixtime(createdAt,'%Y-%m-%d')"))->all();
|
||
return dataByTime($moneyArr,date("Y-m-d",$endTime));
|
||
}
|
||
|
||
//获取订单所有统计(店内)
|
||
public static function getOrderAllArr($timeArr,$uniacid,$storeId,$origin=[1]){
|
||
$arr=[];
|
||
$timeArr=array_reverse($timeArr);
|
||
for($i=0;$i<count($timeArr);$i++){
|
||
$startTime=strtotime($timeArr[$i].'00:00:00');
|
||
$endTime=strtotime($timeArr[$i].'23:59:59');
|
||
$table=(new \yii\db\Query())
|
||
->select(['ifnull(sum(If(origin=7,1,0)),0) as inStoreCount','ifnull(sum(If(origin=6,1,0)),0) as fastCount', 'ifnull(sum(money),0) as money',
|
||
'count(id) as count','ifnull(sum(if(type=2,1,0)),0) as completeCount','ifnull(sum(If(payMode=1,money,0)),0) as weChatPayMoney','ifnull(sum(If(payMode=1,1,0)),0) as weChatPayCount',
|
||
'ifnull(sum(If(payMode=2,money,0)),0) as aliPayMoney','ifnull(sum(If(payMode=2,1,0)),0) as aliCount',
|
||
'ifnull(sum(If(payMode=5,money,0)),0) as balancePayMoney','ifnull(sum(If(payMode=5,1,0)),0) as balancePayCount',
|
||
'ifnull(sum(If(payMode=6,money,0)),0) as cashMoney','ifnull(sum(If(payMode=6,1,0)),0) as cashCount',
|
||
'ifnull(sum(If(payMode in (7,8,9),money,0)),0) as haveMoney','ifnull(sum(If(payMode in (7,8,9),1,0)),0) as haveCount',
|
||
'ifnull(sum(If(payMode=10,money,0)),0) as onDeliveryMoney','ifnull(sum(If(payMode=10,1,0)),0) as onDeliveryCount'])
|
||
->from('{{%ybwm_bill}}')
|
||
->where('uniacid=:uniacid AND (refundAt=0 || refundAt is null)',[':uniacid'=>$uniacid]);
|
||
if($storeId){
|
||
$table->andWhere('storeId=:storeId',[':storeId'=>$storeId]);
|
||
}
|
||
if($startTime){
|
||
$table->andWhere('createdAt>=:startTime AND createdAt<=:endTime',[':startTime'=>$startTime,':endTime'=>$endTime]);
|
||
}
|
||
if($origin){
|
||
$table->andWhere(['in','origin',$origin]);
|
||
}
|
||
$data=$table->one();
|
||
$data['time']=$timeArr[$i];
|
||
$data['self']=self::getEffectiveChannel($startTime,$endTime,$uniacid,$storeId,0,0,2);//自提
|
||
$data['takeOutCount']=self::getEffectiveChannel($startTime,$endTime,$uniacid,$storeId,0,0,1);//自提
|
||
$arr[]=$data;
|
||
}
|
||
return $arr;
|
||
|
||
}
|
||
|
||
//数据----商品概况
|
||
public function getGoodArr($uniacid,$storeId,$type,$goodsType,$stratTime=null,$endTime=null){
|
||
$res=(new \yii\db\Query())
|
||
->from('{{%ybwm_store}}')
|
||
->where(['id'=>$storeId])
|
||
->one();
|
||
if($res['isMain']==1||$res['goodsModel']==3){
|
||
$table=(new \yii\db\Query())
|
||
->from('{{%ybwm_core_goods}}')
|
||
->where(['uniacid'=>$uniacid,'storeId'=>$storeId,'goodsType'=>$goodsType,'deleteAt'=>0]);
|
||
|
||
$data['allGoods']=(clone $table)->count();
|
||
$data['onGoods']=(clone $table)->andWhere(['display'=>1])->count();
|
||
$data['downGoods']=(clone $table)->andWhere(['display'=>2])->count();
|
||
}else{
|
||
$table=(new \yii\db\Query())
|
||
->from('{{%ybwm_store_goods}} a')
|
||
->leftJoin('{{%ybwm_core_goods}} b','a.goodsId=b.id')
|
||
->where(['a.uniacid'=>$uniacid,'a.storeId'=>$storeId,'b.deleteAt'=>0,'b.goodsType'=>$goodsType]);
|
||
$data['allGoods']=(clone $table)->count();
|
||
$data['onGoods']=(clone $table)->andWhere(['a.display'=>1])->count();
|
||
$data['downGoods']=(clone $table)->andWhere(['a.display'=>2])->count();
|
||
}
|
||
|
||
|
||
if($goodsType==1){
|
||
$query=(new \yii\db\Query())
|
||
->select('b.icon,b.name,a.money oldMoney,sum(a.num)num,(sum(a.num)*(a.money))money,d.name categoryName')
|
||
->from('{{%ybwm_order_goods}} as a')
|
||
->leftJoin('{{%ybwm_core_goods}} as b','a.goodsId=b.id')
|
||
->leftJoin('{{%ybwm_takeout_order}} as c','a.orderId=c.id')
|
||
->leftJoin('{{%ybwm_core_category}} as d','b.typePid=d.id')
|
||
->where("a.uniacid=$uniacid AND a.storeId=$storeId")
|
||
->andwhere('a.createdAt>=:startTime AND a.createdAt<=:endTime',['startTime'=>$stratTime,'endTime'=>$endTime])
|
||
->andWhere([">",'b.id',0])
|
||
->andWhere(['in','c.state',[2,3,4,5,6,9]])
|
||
->groupBy('a.goodsId');
|
||
$query->andWhere(['a.item'=>1]);
|
||
}else{
|
||
$query=(new \yii\db\Query())
|
||
->select('b.icon,b.name,a.money oldMoney,sum(a.num)num,(sum(a.num)*(a.money))money,d.name categoryName')
|
||
->from('{{%ybwm_order_goods}} as a')
|
||
->leftJoin('{{%ybwm_core_goods}} as b','a.goodsId=b.id')
|
||
->leftJoin('{{%ybwm_instore_order}} as c','a.orderId=c.id')
|
||
->leftJoin('{{%ybwm_core_category}} as d','b.typePid=d.id')
|
||
->where("a.uniacid=$uniacid AND a.storeId=$storeId")
|
||
->andwhere('a.createdAt>=:startTime AND a.createdAt<=:endTime',['startTime'=>$stratTime,'endTime'=>$endTime])
|
||
//->andWhere(['in','c.state',[2,3]])
|
||
->andWhere(['or',('a.item=2 and c.state in (3,4)'),('a.item=3 and c.state in (2,3,4,6,8)')])
|
||
->andWhere([">",'b.id',0])
|
||
->groupBy('a.goodsId');
|
||
//$query->andWhere(['in','a.item',[2,3]]);
|
||
}
|
||
if($type==1){
|
||
$query->orderBy('num desc');
|
||
}
|
||
if($type==2){
|
||
$query->orderBy('money desc');
|
||
}
|
||
//var_dump(ddSql($query));die;
|
||
$data['payTop']=$query->limit(100)->all();
|
||
return $data;
|
||
|
||
}
|
||
|
||
//当面付概况
|
||
public function getCashierData($uniacid,$storeId,$stratTime=null,$endTime=null){
|
||
$query=(new \yii\db\Query())
|
||
->from('{{%ybwm_cashier_order}}')->where(['uniacid'=>$uniacid,'storeId'=>$storeId,'state'=>2]);
|
||
//收款
|
||
$data['money']=(clone $query)->andWhere(['>=','createdAt',strtotime(date("Y-m-d"))])->andWhere(['<=','createdAt',strtotime(date("Y-m-d 23:59:59"))])->sum('money');
|
||
$data['count']=(clone $query)->andWhere(['>=','createdAt',$stratTime])->andWhere(['<=','createdAt',$endTime])->count();
|
||
//预计收入
|
||
$data['storeActualMoney']=(new \yii\db\Query())
|
||
->from('{{%ybwm_cashier_order}} a')
|
||
->leftJoin('{{%ybwm_bill}} b','a.outTradeNo=b.outTradeNo')
|
||
->where(['a.uniacid'=>$uniacid,'a.storeId'=>$storeId])
|
||
->andWhere(['>=','a.createdAt',$stratTime])
|
||
->andWhere(['<=','a.createdAt',$endTime])
|
||
->sum('storeActualMoney');
|
||
;
|
||
//总收款
|
||
$data['sumMoney']=(clone $query)->sum('money');
|
||
$data['sumCount']=(clone $query)->count();
|
||
$data['payUserNum']=(clone $query)->groupBy('userId')->count();
|
||
|
||
$data['estimate']=self::getIncome($stratTime,$endTime,$uniacid,$storeId,[5]);//预计收入
|
||
$data['serviceMoney']=self::getService($stratTime,$endTime,$uniacid,$storeId,[5]);//服务费
|
||
|
||
//微信小程序
|
||
$data['wxCount']=(clone $query)->andWhere(['>=','createdAt',$stratTime])->andWhere(['<=','createdAt',$endTime])->andWhere(['origin'=>1])->count()?:0;
|
||
//支付宝小程序
|
||
$data['aliCount']=(clone $query)->andWhere(['>=','createdAt',$stratTime])->andWhere(['<=','createdAt',$endTime])->andWhere(['origin'=>3])->count()?:0;
|
||
//微信小程序
|
||
$data['wxMoney']=(clone $query)->andWhere(['>=','createdAt',$stratTime])->andWhere(['<=','createdAt',$endTime])->andWhere(['payMode'=>1])->sum('money')?:0;
|
||
//支付宝小程序
|
||
$data['aliMoney']=(clone $query)->andWhere(['>=','createdAt',$stratTime])->andWhere(['<=','createdAt',$endTime])->andWhere(['payMode'=>2])->sum('money')?:0;
|
||
//余额程序
|
||
$data['balanceMoney']=(clone $query)->andWhere(['>=','createdAt',$stratTime])->andWhere(['<=','createdAt',$endTime])->andWhere(['payMode'=>5])->sum('money')?:0;
|
||
$moneyChar= (new \yii\db\Query())
|
||
->select(new Expression("from_unixtime(createdAt,'%Y-%m-%d') as hours,sum(money)money"))
|
||
->from('{{%ybwm_cashier_order}}')
|
||
->where(['uniacid'=>$uniacid,'storeId'=>$storeId,'state'=>2])
|
||
->andWhere(['>=','createdAt',$stratTime])->andWhere(['<=','createdAt',$endTime])
|
||
->groupBy(new Expression("from_unixtime(createdAt,'%Y-%m-%d')"))
|
||
->all();
|
||
$data['moneyChar']=dataByTime($moneyChar,date('Y-m-d H:i:s',$endTime));
|
||
$countChar= (new \yii\db\Query())
|
||
->select(new Expression("from_unixtime(createdAt,'%Y-%m-%d') as hours,count(*)money"))
|
||
->from('{{%ybwm_cashier_order}}')
|
||
->where(['uniacid'=>$uniacid,'storeId'=>$storeId,'state'=>2])
|
||
->andWhere(['>=','createdAt',$stratTime])->andWhere(['<=','createdAt',$endTime])
|
||
->groupBy(new Expression("from_unixtime(createdAt,'%Y-%m-%d')"))
|
||
->all();
|
||
$data['countChar']=dataByTime($countChar,date('Y-m-d H:i:s',$endTime));
|
||
return $data;
|
||
}
|
||
|
||
}
|