/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = Custom::find();
$query->joinWith('openidBindMobile');
$query->leftJoin('wx_user', 'wx_user.gh_id = wx_openid_bind_mobile.gh_id AND wx_user.openid = wx_openid_bind_mobile.openid');
$dataProvider = new ActiveDataProvider(['query' => $query]);
if (!Yii::$app->user->getIsAdmin()) {
$this->office_id = Yii::$app->user->identity->office_id;
$query->andFilterWhere(['office_id' => $this->office_id]);
}
if (!($this->load($params) && $this->validate())) {
return $dataProvider;
}
if ($this->is_bind !== '') {
if ($this->is_bind == 1) {
//$query->andWhere('wx_openid_bind_mobile.mobile is not null');
$query->andWhere(['not', ['wx_openid_bind_mobile.mobile' => null]]);
} else {
$query->andWhere(['wx_openid_bind_mobile.mobile' => null]);
}
}
$query->andFilterWhere(['custom_id' => $this->custom_id, 'is_vip' => $this->is_vip, 'office_id' => $this->office_id, 'vip_level_id' => $this->vip_level_id]);
$query->andFilterWhere(['like', 'wx_custom.mobile', $this->mobile])->andFilterWhere(['like', 'name', $this->name]);
if (trim($this->subscribe_time_start) !== '') {
$query->andWhere('date(wx_user.create_time)>=:subscribe_time_start', [':subscribe_time_start' => $this->subscribe_time_start]);
}
if (trim($this->subscribe_time_end) !== '') {
$query->andWhere('date(wx_user.create_time)<=:subscribe_time_end', [':subscribe_time_end' => $this->subscribe_time_end]);
}
return $dataProvider;
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = \app\models\Custom::find()->join('INNER JOIN', 'wx_openid_bind_mobile', 'wx_openid_bind_mobile.mobile = wx_custom.mobile')->join('INNER JOIN', 'wx_user', 'wx_openid_bind_mobile.gh_id = wx_user.gh_id and wx_openid_bind_mobile.openid = wx_user.openid')->where(['wx_user.subscribe' => 1])->orderBy(['wx_user.create_time' => SORT_DESC]);
$dataProvider = new \yii\data\ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 10]]);
if (!($this->load($params) && $this->validate())) {
return $dataProvider;
}
if (!empty($this->page)) {
$dataProvider->pagination->page = $this->page;
}
if (trim($this->gh_id) !== '') {
$query->andWhere(['wx_user.gh_id' => $this->gh_id]);
}
$this->addCondition($query, 'office_id');
if (!empty($this->searchStr)) {
$query->andWhere(['or', ['like', 'wx_custom.mobile', $this->searchStr], ['like', 'wx_custom.name', $this->searchStr]]);
}
return $dataProvider;
}
public static function getNotBindVipCustoms($in_office)
{
$mobiles = OpenidBindMobile::getMobiles();
if ($in_office == '1') {
$customs = Custom::find()->where(['mobile' => $mobiles])->andWhere("office_id > 0 AND is_vip = 1")->all();
} else {
$customs = Custom::find()->where(['mobile' => $mobiles])->andWhere("office_id = 0 AND is_vip = 1")->all();
}
return $customs;
}
public function actionOfficecustomstat()
{
$offices = MOffice::findAll(['gh_id' => 'gh_03a74ac96138']);
$rows = [];
$custom_count = [];
foreach ($offices as $office) {
//$row = [];
$row['office_id'] = $office->office_id;
$row['office_title'] = $office->title;
$custom = Custom::findOne(['office_id' => $office->office_id]);
if ($custom !== null) {
$custom_counts = Custom::find()->select('*, count(*) as c')->where('office_id=:office_id', [':office_id' => $office->office_id])->groupBy(['office_id'])->orderBy('office_id')->asArray()->all();
foreach ($custom_counts as $custom_count) {
$row['custom_count'] = $custom_count['c'];
}
} else {
continue;
}
$rows[] = $row;
}
//print_r($custom_count);
//U::W("####################################");
//U::W($rows);
//U::W($custom_count);
$filter = new \app\models\FiltersForm();
$filter->unsetAttributes();
if (isset($_GET['FiltersForm'])) {
$filter->setAttributes($_GET['FiltersForm'], false);
}
$rows = $filter->filterArrayData($rows);
$dataProvider = new ArrayDataProvider(['allModels' => $rows, 'sort' => ['attributes' => ['office_id', 'office_title', 'custom_count'], 'defaultOrder' => ['custom_count' => SORT_DESC]], 'pagination' => ['pageSize' => 50]]);
return $this->render('officecustomstat', ['dataProvider' => $dataProvider, 'filter' => $filter]);
}
public function actionSelfOwnedOutlets($date = null, $is_selfOperated = 1, $filename = 'self-owned-outlets.csv')
{
$file = Yii::$app->getRuntimePath() . DIRECTORY_SEPARATOR . 'exported_data' . DIRECTORY_SEPARATOR . $filename;
$offices = \app\models\MOffice::findAll(['is_selfOperated' => $is_selfOperated]);
if ($date == null) {
$date = date('Y-m-d');
}
$lastmonth_start = date('Y-m-01', strtotime('-1 month', strtotime($date))) . " 00:00:00";
$lastmonth_end = date('Y-m-d', strtotime('-1 month', strtotime($date))) . " 23:59:59";
$thismonth_start = date('Y-m-01', strtotime($date)) . " 00:00:00";
$thismonth_end = date('Y-m-d', strtotime($date)) . " 23:59:59";
// $lastmonth_start = \app\models\U::getFirstDayOfLastMonth();
// $lastmonth_end = date('Y-m-d', strtotime('-1 month', strtotime('yesterday')))." 23:59:59";;
// $thismonth_start = \app\models\U::getFirstDate(date('Y'), date('m'));
// $thismonth_end = date('Y-m-d', strtotime('yesterday'))." 23:59:59";
$fh = fopen($file, "w");
//fprintf($fh, "自营厅名称,粉丝总数量,绑定手机粉丝总数量,上月（%s）同期发展粉丝数量,上月（%s）同期发展绑定手机粉丝数量,本月（%s）发展粉丝数量,本月（%s）发展绑定手机粉丝数量,归属客户总数量,已微信关联客户数量,上月（%s）同期关联客户数量,本月（%s）关联客户数量\n",
//绑定手机粉丝总数量 == 会员总数量
fprintf($fh, "自营厅名称,累计粉丝量（从发展到现在）,会员总数量, 本月新增会员, 上月（%s）同期发展粉丝数量,(%s)新用户发展同比,%s月新增粉丝量,(%s)新用户发展量,归属客户总数量,已微信关联客户数量,(%s)维系用户同比,%s维系用户发展量, 发展业务量\n", date('Y-m', strtotime($lastmonth_start)), date('Y-m', strtotime($lastmonth_start)), date('Y-m-d', strtotime($thismonth_start)) . "至" . date('Y-m-d', strtotime($thismonth_end)), date('Y-m-d', strtotime($thismonth_start)) . "至" . date('Y-m-d', strtotime($thismonth_end)), date('Y-m', strtotime($lastmonth_start)), date('Y-m-d', strtotime($thismonth_start)) . "至" . date('Y-m-d', strtotime($thismonth_end)));
foreach ($offices as $office) {
// $wx_count = \app\models\MUser::find()->where(['scene_pid' => $office->getSceneids(), 'subscribe' => 1])->count();
// $wx_bound_count = \app\models\MUser::find()->joinWith('openidBindMobiles')
// ->where(['scene_pid' => $office->getSceneids(), 'subscribe' => 1])
// ->andWhere(['wx_openid_bind_mobile.mobile' => null])
// ->count();
$wx_count = \app\models\MUser::find()->where(['belongto' => $office->office_id, 'subscribe' => 1])->count();
$order_count = \app\models\MOrder::find()->where(['office_id' => $office->office_id])->andWhere(['>=', 'create_time', $thismonth_start])->andWhere(['<=', 'create_time', $thismonth_end])->count();
$wx_bound_count = \app\models\MUser::find()->joinWith('openidBindMobiles')->where(['belongto' => $office->office_id, 'subscribe' => 1])->andWhere(['wx_openid_bind_mobile.mobile' => null])->count();
$wx_bound_count = $wx_count - $wx_bound_count;
$wx_bound_thismonth_count = \app\models\MUser::find()->joinWith('openidBindMobiles')->where(['belongto' => $office->office_id, 'subscribe' => 1])->andWhere(['not', ['wx_openid_bind_mobile.mobile' => null]])->andWhere(['>', 'subscribe_time', strtotime($thismonth_start)])->andWhere(['<', 'subscribe_time', strtotime($thismonth_end)])->groupBy(['gh_id', 'openid'])->count();
// $wx_lastmonth_count = \app\models\MUser::find()->andWhere(['scene_pid' => $office->getSceneids(), 'subscribe' => 1])
// ->andWhere(['>=', 'create_time', $lastmonth_start])
// ->andWhere(['<=', 'create_time', $lastmonth_end])
// ->count();
// $wx_lastmonth_bound_count = \app\models\MUser::find()->joinWith('openidBindMobiles')
// ->andWhere(['scene_pid' => $office->getSceneids(), 'subscribe' => 1])
// ->andWhere(['>=', 'wx_user.create_time', $lastmonth_start])
// ->andWhere(['<=', 'wx_user.create_time', $lastmonth_end])
// ->andWhere(['wx_openid_bind_mobile.mobile' => null])
// ->count();
$wx_lastmonth_count = \app\models\MUser::find()->andWhere(['belongto' => $office->office_id, 'subscribe' => 1])->andWhere(['>=', 'create_time', $lastmonth_start])->andWhere(['<=', 'create_time', $lastmonth_end])->count();
$wx_lastmonth_bound_count = \app\models\MUser::find()->joinWith('openidBindMobiles')->andWhere(['belongto' => $office->office_id, 'subscribe' => 1])->andWhere(['>=', 'wx_user.create_time', $lastmonth_start])->andWhere(['<=', 'wx_user.create_time', $lastmonth_end])->andWhere(['wx_openid_bind_mobile.mobile' => null])->count();
$wx_lastmonth_bound_count = $wx_lastmonth_count - $wx_lastmonth_bound_count;
// $wx_lastweek_count = \app\models\MUser::find()->andWhere(['scene_pid' => $office->getSceneids(), 'subscribe' => 1])
// ->andWhere(['>=', 'wx_user.create_time', $lastweek_start])
// ->andWhere(['<=', 'wx_user.create_time', $lastweek_end])
// ->count();
// $wx_lastweek_bound_count = \app\models\MUser::find()->joinWith('openidBindMobiles')
// ->andWhere(['scene_pid' => $office->getSceneids(), 'subscribe' => 1])
// ->andWhere(['>=', 'wx_user.create_time', $lastweek_start])
// ->andWhere(['<=', 'wx_user.create_time', $lastweek_end])
// ->andWhere(['wx_openid_bind_mobile.mobile' => null])
// ->count();
$wx_lastweek_count = \app\models\MUser::find()->andWhere(['belongto' => $office->office_id, 'subscribe' => 1])->andWhere(['>=', 'wx_user.create_time', $thismonth_start])->andWhere(['<=', 'wx_user.create_time', $thismonth_end])->count();
$wx_lastweek_bound_count = \app\models\MUser::find()->joinWith('openidBindMobiles')->andWhere(['belongto' => $office->office_id, 'subscribe' => 1])->andWhere(['>=', 'wx_user.create_time', $thismonth_start])->andWhere(['<=', 'wx_user.create_time', $thismonth_end])->andWhere(['wx_openid_bind_mobile.mobile' => null])->count();
$wx_lastweek_bound_count = $wx_lastweek_count - $wx_lastweek_bound_count;
$customer_count = \app\models\Custom::find()->where(['office_id' => $office->office_id])->count();
$customer_bound_count = \app\models\Custom::find()->joinWith('openidBindMobile')->where(['office_id' => $office->office_id])->andWhere(['wx_openid_bind_mobile.mobile' => null])->count();
$customer_bound_count = $customer_count - $customer_bound_count;
$customer_lastmonth_bound_count = \app\models\Custom::find()->joinWith('openidBindMobile.user')->where(['office_id' => $office->office_id])->andWhere(['not', ['wx_openid_bind_mobile.mobile' => null]])->andWhere(['>=', 'wx_user.create_time', $lastmonth_start])->andWhere(['<=', 'wx_user.create_time', $lastmonth_end])->count();
$customer_lastweek_bound_count = \app\models\Custom::find()->joinWith('openidBindMobile.user')->where(['office_id' => $office->office_id])->andWhere(['not', ['wx_openid_bind_mobile.mobile' => null]])->andWhere(['>=', 'wx_user.create_time', $thismonth_start])->andWhere(['<=', 'wx_user.create_time', $thismonth_end])->count();
fprintf($fh, "%s", $office->title . ", " . $wx_count . ", " . $wx_bound_count . ", " . $wx_bound_thismonth_count . ", " . $wx_lastmonth_count . ", " . $wx_lastmonth_bound_count . ", " . $wx_lastweek_count . ", " . $wx_lastweek_bound_count . ", " . $customer_count . ", " . $customer_bound_count . ", " . $customer_lastmonth_bound_count . ", " . $customer_lastweek_bound_count . ", " . $order_count . PHP_EOL);
}
fclose($fh);
}
