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

522 lines
27 KiB
PHP
Raw Permalink Normal View History

init
2024-11-01 16:07:54 +08:00
<?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;
}
}
Reference in New Issue Copy Permalink