public function actionGetcolumn()
{
/*$model = new Customer();
print_r($model->attributeLabels());*/
$model = new Regional();
$regional = $model->find()->distinct('FIELD2')->all();
print_r($regional);
}
public function actionIndex()
{
$regions = Regional::find()->asArray()->all();
$data = array();
$datasms = array();
$datafacebook = array();
$datatwitter = array();
$dataemail = array();
foreach ($regions as $rows) {
$regional = $rows['FIELD1'];
$email = Blastemail::find()->where(['FIELD7' => $regional])->asArray()->count();
$sms = Blastsms::find()->where(['FIELD7' => $regional])->asArray()->count();
$facebook = Blastfacebook::find()->where(['FIELD7' => $regional])->asArray()->count();
$twitter = Blasttwitter::find()->where(['FIELD7' => $regional])->asArray()->count();
$smssent = Blastsms::find()->where(['FIELD7' => $regional])->andWhere(['FIELD6' => 3])->asArray()->count();
$smsrespon = Blastsms::find()->where(['FIELD7' => $regional])->andWhere(['FIELD6' => 5])->asArray()->count();
$emailsent = Blastemail::find()->where(['FIELD7' => $regional])->andWhere(['FIELD6' => 3])->asArray()->count();
$emailrespon = Blastemail::find()->where(['FIELD7' => $regional])->andWhere(['FIELD6' => 5])->asArray()->count();
$facebooksent = Blastfacebook::find()->where(['FIELD7' => $regional])->andWhere(['FIELD6' => 3])->asArray()->count();
$facebookrespon = Blastfacebook::find()->where(['FIELD7' => $regional])->andWhere(['FIELD6' => 5])->asArray()->count();
$twittersent = Blasttwitter::find()->where(['FIELD7' => $regional])->andWhere(['FIELD6' => 3])->asArray()->count();
$twitterrespon = Blasttwitter::find()->where(['FIELD7' => $regional])->andWhere(['FIELD6' => 5])->asArray()->count();
array_push($datasms, array('regional' => "REGIONAL " . $rows['FIELD1'], 'total' => $sms, 'sent' => $smssent, 'respon' => $smsrespon));
array_push($datafacebook, array('regional' => "REGIONAL " . $rows['FIELD1'], 'total' => $facebook, 'sent' => $facebooksent, 'respon' => $facebookrespon));
array_push($datatwitter, array('regional' => "REGIONAL " . $rows['FIELD1'], 'total' => $twitter, 'sent' => $twittersent, 'respon' => $twitterrespon));
array_push($dataemail, array('regional' => "REGIONAL " . $rows['FIELD1'], 'total' => $email, 'sent' => $emailsent, 'respon' => $emailrespon));
}
array_push($data, array('sms' => $datasms, 'facebook' => $datafacebook, 'twitter' => $datatwitter, 'email' => $dataemail));
//print_r($data);
return $this->render('index', ['data' => $data, 'regional' => $regions]);
}
public function actionIndex()
{
$model = new Prospekdata();
$regional = Regional::find()->asArray()->all();
$data = array();
foreach ($regional as $row) {
$total = $model->find()->where(['FIELD4' => $row['FIELD1']])->asArray()->count();
$produktif = $model->find()->where(['FIELD4' => $row['FIELD1']])->andwhere(['FIELD8' => 1])->asArray()->count();
$nonproduktif = $model->find()->where(['FIELD4' => $row['FIELD1']])->andwhere(['FIELD8' => 0])->asArray()->count();
$offering = $model->find()->where(['FIELD4' => $row['FIELD1']])->andWhere(['FIELD9' => 0])->asArray()->count();
array_push($data, array('regional' => $row['FIELD1'], 'jumlah' => $total, 'produktif' => $produktif, 'nonproduktif' => $nonproduktif, 'offering' => $offering));
}
return $this->render('index', ['data' => $data, 'regional' => $regional]);
}
public function actionProspek()
{
$regional = Regional::find()->all();
$query = new Query();
$data = array();
foreach ($regional as $rows) {
$query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE3.FIELD3 IS NOT NULL')->all();
$handphone = $query->createCommand()->queryScalar();
$query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE3.FIELD6 IS NOT NULL')->all();
$email = $query->createCommand()->queryScalar();
$query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->join('LEFT OUTER JOIN', 'TABLE2', 'TABLE2.FIELD1 = TABLE3.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE2.FIELD7 IS NOT NULL')->all();
$phone = $query->createCommand()->queryScalar();
array_push($data, array('regional' => $rows->FIELD1, 'handphone' => $handphone, 'email' => $email, 'phone' => $phone));
}
return $this->render('prospek', ['data' => $data, 'regional' => $regional]);
}
public function actionCreatejsonregional()
{
$mcustomer = new Customer();
$mregional = new Regional();
$regional = $mregional->find()->all();
$total = $mcustomer->find()->count();
$labels = $mcustomer->attributeLabels();
$array = array();
$file = "uploads/json/customer/attributeregional.json";
fopen($file, "w");
foreach ($regional as $row) {
$reg = $row->FIELD1;
foreach ($labels as $key => $rows) {
$label = strtoupper($rows);
$filled = $mcustomer->find()->where($key . ' IS NOT NULL')->count();
$notfilled = $mcustomer->find()->where($key . ' IS NULL')->count();
array_push($array, array('regional' => $reg, 'attribute' => array('labels' => $label, 'count' => $total, 'filled' => $filled, 'notfilled' => $notfilled)));
}
}
$data = json_encode($array, JSON_PRETTY_PRINT);
file_put_contents($file, $data);
}
