public function ambilDataPoinMember() { $periode = MemberPeriodePoin::model()->findByPk($this->periodeId); $command = Yii::app()->db->createCommand()->select('profil_id, p.nomor, p.nama, sum(poin) poin, p.alamat1, p.alamat2, p.alamat3, p.telp, p.hp, p.surel, p.identitas, p.tanggal_lahir')->from(PenjualanMember::model()->tableName() . ' t')->join(Profil::model()->tableName() . ' p', 't.profil_id = p.id')->where('year(t.updated_at)=:tahun and month(t.updated_at) between :awal and :akhir')->group('profil_id'); if ($this->sortBy == self::SORT_BY_POIN_ASC) { $command->order('sum(poin), p.nama'); } elseif ($this->sortBy == self::SORT_BY_POIN_DSC) { $command->order('sum(poin) desc, p.nama'); } $command->bindValues([':tahun' => $this->tahun, ':awal' => $periode->awal, ':akhir' => $periode->akhir]); return $command->queryAll(); }
/** * Ambil total poin yang sudah didapat * @return int total Poin Periode Berjalan */ public function getTotalPoinPeriodeBerjalan() { $profil = Profil::model()->findByPk($this->profil_id); if ($profil->isMember()) { $periodePoin = MemberPeriodePoin::model()->find('awal<=month(now()) and month(now())<=akhir'); $poin = false; if (!is_null($periodePoin)) { $poin = Yii::app()->db->createCommand()->select('sum(poin) total')->from(PenjualanMember::model()->tableName() . ' tpm')->where('YEAR(updated_at) = YEAR(NOW()) AND MONTH(updated_at) BETWEEN :awal AND :akhir AND profil_id=:profilId')->bindValues(array(':awal' => $periodePoin->awal, ':akhir' => $periodePoin->akhir, ':profilId' => $profil->id))->queryRow(); } else { /* Berarti periode lintas tahun (awal > akhir ) */ $periodePoinL = MemberPeriodePoin::model()->find('awal <= month(now()) OR month(now()) <= akhir AND awal > akhir'); if (!is_null($periodePoinL)) { $queryPoin = Yii::app()->db->createCommand()->select('sum(poin) total')->from(PenjualanMember::model()->tableName() . ' tpm')->where('profil_id=:profilId'); $curMonth = date('n'); if ($curMonth >= $periodePoinL->akhir) { $queryPoin->andWhere('YEAR(updated_at) = YEAR(NOW()) AND MONTH(updated_at) BETWEEN :awal AND :akhir'); $queryPoin->bindValues([]); } $queryPoin->bindValues(array(':awal' => $periodePoinL->awal, ':akhir' => $periodePoinL->akhir, ':profilId' => $profil->id)); $poin = $queryPoin->queryRow(); } } return $poin ? $poin['total'] : 0; } else { return 0; } }
/** * Returns the data model based on the primary key given in the GET variable. * If the data model is not found, an HTTP exception will be raised. * @param integer $id the ID of the model to be loaded * @return MemberPeriodePoin the loaded model * @throws CHttpException */ public function loadModel($id) { $model = MemberPeriodePoin::model()->findByPk($id); if ($model === null) { throw new CHttpException(404, 'The requested page does not exist.'); } return $model; }