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;
}
}
