public function getTotal($code)
{
$totalRealization = 0;
$totalRestMoney = 0;
$usingRate = 0;
// Calculate total realization for each package
$models = Realization::model()->findAllByAttributes(array('package_code' => "{$code}"));
if ($models) {
foreach ($models as $model) {
$totalRealization += $model->total_spm;
}
}
// Calculate total realization for each package
//Get Package Limit
$packageAccounts = PackageAccount::model()->findAllByAttributes(array('package_code' => "{$code}"));
$limit = 0;
if ($packageAccounts) {
foreach ($packageAccounts as $data) {
$limit += $data->limit;
}
}
//Get Package Limit
$totalRestMoney = $limit - $totalRealization;
$rate = $totalRealization / $limit;
$usingRate = round($rate, 4);
return array('limit' => $limit, 'realization' => $totalRealization, 'restMoney' => $totalRestMoney, 'rate' => $usingRate);
}
public function actionTableReport()
{
$data = Satker::model()->findByAttributes(array('code' => '622280'));
$ppk = array();
$limit = array();
$realization = array();
$rate = array();
$countData = array();
$ratePpk = array();
$limitPpk = array();
$totalRealPpk = array();
if ($data) {
$packageAccount = PackageAccount::model()->findAllByAttributes(array('satker_code' => "{$data->code}"));
$countData[$data->code] = 0;
$limit[$data->code] = 0;
$realization[$data->code] = 0;
$rate[$data->code] = 0;
if ($packageAccount) {
$countData[$data->code] = count($packageAccount);
foreach ($packageAccount as $pa) {
$limit[$data->code] += $pa->limit;
$realData = Realization::model()->findAllByAttributes(array('packageAccount_code' => "{$data->code}"));
$totalRealization = 0;
if ($realData) {
foreach ($realData as $r) {
$totalRealization += $r->total_spm;
}
}
$realization[$data->code] += $totalRealization;
}
}
if ($limit[$data->code] != 0) {
$rate[$data->code] = $realization[$data->code] / $limit[$data->code] * 100;
}
$ppks = Ppk::model()->findAll();
if ($ppks) {
foreach ($ppks as $ppk) {
$paPpk = PackageAccount::model()->findAllByAttributes(array('ppk_code' => "{$ppk->code}"));
$limitPpk[$ppk->code] = 0;
$ratePpk[$ppk->code] = 0;
$totalRealPpk[$ppk->code] = 0;
if ($paPpk) {
foreach ($paPpk as $p) {
$limitPpk[$ppk->code] += $p->limit;
$realPpk = Realization::model()->findAllByAttributes(array('packageAccount_code' => "{$p->code}"));
$realizationPpk = 0;
if ($realPpk) {
foreach ($realPpk as $dataReal) {
$realizationPpk += $dataReal->total_spm;
}
}
$totalRealPpk[$ppk->code] += $realizationPpk;
}
if ($limitPpk != 0) {
$ratePpk[$ppk->code] = $totalRealPpk[$ppk->code] / $limitPpk[$ppk->code] * 100;
}
}
}
}
}
echo $data->name . "</br>";
echo $limit[$data->code] . "</br>";
echo $realization[$data->code] . "</br>";
// $this->title = 'Tabel Penggunaan Anggaran';
// $say = 'Ini adalah page report penggunaan anggaran';
// $this->render('tableReport', array(
// 'say' => $say,
// ));
}
/**
* get overlimit budget temporary
* @return array
*/
public function getOverlimitBudget()
{
$overlimitBudget = array();
$budgets = BudgetTemp::model()->findAll();
if ($budgets) {
foreach ($budgets as $data) {
$total = 0;
$realizations = Realization::model()->findAllByAttributes(array('packageAccount_code' => "{$data->code}"));
if ($realizations) {
foreach ($realizations as $rData) {
$total += $rData->total_spm;
}
}
if ($data->total_budget_limit < $total) {
array_push($overlimitBudget, $data);
}
}
}
return $overlimitBudget;
}
private function validateRemovedData($filePath, $fields = array(), $worksheet, $highestRow, $startingRow = 2)
{
/* ===== (IMPORT DATA CHECKER) check if RKAKL data component already removed, but app master data already realize === */
//clear ComponentError table if exists
$exist = ComponentError::model()->exists();
if ($exist) {
Yii::app()->db->createCommand()->truncateTable(ComponentError::model()->tableName());
}
$componentCodeFromExcel = [];
for ($row = $startingRow; $row <= $highestRow; ++$row) {
$attributes = array();
for ($col = 0; $col < count($fields); ++$col) {
$val = $worksheet->getCellByColumnAndRow($fields[$col]['col'], $row)->getValue();
$attributes[$fields[$col]['name']] = $val;
}
//(IMPORT DATA CHECKER) get all excel component
$code = $attributes['satker_code'] . "." . $attributes['activity_code'] . "." . $attributes['output_code'] . "." . $attributes['suboutput_code'] . "." . $attributes['code'];
array_push($componentCodeFromExcel, $code);
}
// (IMPORT DATA CHECKER) compare excel.component with current data component removed
$criteria = new CDbCriteria();
$criteria->addNotInCondition('code', $componentCodeFromExcel);
$removedComponents = Component::model()->findAll($criteria);
// //(IMPORT DATA CHECKER) compare $removedComponents with realization.package_code
// $removedComponentsArray = [];
// foreach ($removedComponents as $r) {
// array_push($removedComponentsArray, $r->code);
// }
if (count($removedComponents) > 0) {
$criteria = new CDbCriteria();
// $criteria->addInCondition('package_code', $removedComponentsArray);
foreach ($removedComponents as $r) {
$criteria->addSearchCondition('package_code', $r->code, true, 'OR');
}
$realizationsError = Realization::model()->findAll($criteria);
//insert to ComponentError table
$whereInComponent = [];
foreach ($realizationsError as $r) {
$codeArray = explode(".", $r->package_code);
$code = $codeArray[0] . "." . $codeArray[1] . "." . $codeArray[2] . "." . $codeArray[3] . "." . $codeArray[4];
//get only package code till latest 4 dot (.)
array_push($whereInComponent, $code);
}
$criteria = new CDbCriteria();
$criteria->addInCondition('code', $whereInComponent);
$componentsError = Component::model()->findAll($criteria);
foreach ($componentsError as $s) {
$model = new ComponentError();
$model->attributes = ['code' => $s->code, 'satker_code' => $s->satker_code, 'activity_code' => $s->activity_code, 'output_code' => $s->output_code, 'suboutput_code' => $s->suboutput_code, 'name' => $s->name];
$model->save();
}
if (count($componentsError) > 0) {
unlink($filePath);
Yii::app()->user->setFlash('error', "Terdapat data terhapus di RKAKL, dimana data tsb sudah terealisasi.");
$this->redirect(array('import'));
}
}
/* ===== EOF (IMPORT DATA CHECKER) check if RKAKL data component already removed, but app master data already realize === */
}
public function importExcelToMysql($filePath, $fields = array(), $model = null, $startingRow = 2)
{
$objPHPExcel = PHPExcel_IOFactory::load($filePath);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
//field validation
$fieldValidate = array();
$validated = FALSE;
for ($col = 0; $col < count($fields); ++$col) {
$val = $worksheet->getCellByColumnAndRow($fields[$col]['col'], 1)->getValue();
$fieldValidate[$fields[$col]['name']] = $val;
}
if ($fieldValidate['packageAccount_code'] == 'Kode Akun Paket' and $fieldValidate['total_spm'] == 'Realisasi sampai saat ini') {
$validated = TRUE;
}
//end of validation
if ($validated == TRUE) {
$highestRow = $worksheet->getHighestRow();
// e.g. 10
$highestColumn = $worksheet->getHighestColumn();
//Doing import data
for ($row = $startingRow; $row <= $highestRow; ++$row) {
$attributes = array();
for ($col = 0; $col < count($fields); ++$col) {
$val = $worksheet->getCellByColumnAndRow($fields[$col]['col'], $row)->getCalculatedValue();
$attributes[$fields[$col]['name']] = $val;
}
if ($attributes['packageAccount_code'] != NULL and $attributes['total_spm'] != NULL and $attributes['total_spm'] != 0) {
$packageAccountCode = isset($attributes['packageAccount_code']) ? trim($attributes['packageAccount_code'], " \t\n\r\v") : NULL;
$overlimitValidate = PackageAccount::model()->overlimit($packageAccountCode, $attributes['total_spm']);
if ($overlimitValidate == FALSE) {
$model = new Realization();
$model->packageAccount_code = $packageAccountCode;
$pA = PackageAccount::model()->findByAttributes(array('code' => "{$packageAccountCode}"));
$packageCode = NULL;
if ($pA) {
$packageCode = $pA->package_code;
}
$model->package_code = $packageCode;
$model->total_spm = $attributes['total_spm'];
$model->up_ls = $attributes['up_ls'];
$model->ppn = $attributes['ppn'];
$model->pph = $attributes['pph'];
$model->receiver = $attributes['receiver'];
$model->nrs = $attributes['nrs'];
$model->nrk = $attributes['nrk'];
$model->spm_number = $attributes['spm_number'];
$model->spm_date = $attributes['spm_date'];
$model->save();
} else {
$errorModel = new ErrorRealization();
$errorModel->packageAccount_code = $packageAccountCode;
$pA = PackageAccount::model()->findByAttributes(array('code' => "{$packageAccountCode}"));
$packageCode = NULL;
if ($pA) {
$packageCode = $pA->package_code;
}
$errorModel->package_code = $packageCode;
$errorModel->total_spm = $attributes['total_spm'];
$errorModel->up_ls = $attributes['up_ls'];
$errorModel->ppn = $attributes['ppn'];
$errorModel->pph = $attributes['pph'];
$errorModel->receiver = $attributes['receiver'];
$errorModel->nrs = $attributes['nrs'];
$errorModel->nrk = $attributes['nrk'];
$errorModel->spm_number = $attributes['spm_number'];
$errorModel->spm_date = $attributes['spm_date'];
$errorModel->description = 'Terjadi pagu minus.';
$errorModel->save();
}
}
}
//Action when import process done
unlink($filePath);
Yii::app()->user->setFlash('success', "Data realisasi berhasil diinput.");
$this->redirect(array('index'));
} else {
unlink($filePath);
Yii::app()->user->setFlash('error', "Pastikan file yang Anda upload sudah benar!");
$this->redirect(array('entry'));
}
}
}
public function overlimit($code, $newRealization)
{
$overlimit = FALSE;
$realized = 0;
$limit = 0;
$packageAccount = PackageAccount::model()->findByAttributes(array('code' => "{$code}"));
if ($packageAccount) {
$limit = $packageAccount->limit;
}
$realization = Realization::model()->findAllByAttributes(array('packageAccount_code' => "{$code}"));
if ($realization) {
foreach ($realization as $data) {
$realized += $data->total_spm;
}
}
$temporaySummary = $realized + $newRealization;
if ($temporaySummary > $limit) {
$overlimit = TRUE;
}
return $overlimit;
}
public function getTotal($number)
{
// $totalBalance = 0;
$models = UP::model()->findByAttributes(array('number_of_letter' => $number));
// foreach ($models as $model) {
$totalBalance = $models->total_up;
// }
$realizations = Realization::model()->findAllByAttributes(array('use_up' => 'UP', 'number_of_letter' => "{$number}"));
$totalRealization = 0;
if ($realizations) {
foreach ($realizations as $realization) {
$totalRealization += $realization->total_spm;
}
}
$totalCurrentBalance = $totalBalance - $totalRealization;
$rate = $totalRealization / $totalBalance;
$rate = round($rate, 4);
return array('realization' => $totalRealization, 'restUp' => $totalCurrentBalance, 'rateUsingUp' => $rate);
}
public function getTotal($code)
{
$totalRealization = 0;
$totalRestMoney = 0;
$usingRate = 0;
$models = Realization::model()->findAllByAttributes(array('package_code' => "{$code}", 'use_up' => 'UP'));
foreach ($models as $model) {
$totalRealization += $model->total_spm;
}
$detail = UpDetail::model()->findByAttributes(array('package_name' => "{$code}"), array('order' => 'id DESC'));
$limit = $detail->limit;
$totalRestMoney = $limit - $totalRealization;
$rate = $totalRealization / $limit;
$usingRate = round($rate, 4);
return array('realization' => $totalRealization, 'restUp' => $totalRestMoney, 'rateUpUsing' => $usingRate);
}
/**
* Updates a particular model.
* If update is successful, the browser will be redirected to the 'view' page.
* @param integer $id the ID of the model to be updated
*/
public function actionUpdate($id)
{
$this->title = 'Update UP';
$model = $this->loadModel($id);
$modelNumber = $model->number_of_letter;
$currentUp = UpDetail::model()->findAllByAttributes(array('up_number_of_letter' => $model->number_of_letter));
$detail = false;
if ($currentUp) {
$detail = TRUE;
}
$latestUP = Up::model()->find(array('order' => 'id DESC'));
if ($model->number_of_letter != $latestUP->number_of_letter) {
Yii::app()->user->setFlash('error', 'Sudah ada UP baru. Tidak dapat mengedit UP lama.');
$this->redirect(array('admin'));
}
// Uncomment the following line if AJAX validation is needed
$this->performAjaxValidation($model);
if (isset($_POST['Up'])) {
$numberLetter = $_POST['Up']['number_of_letter'];
// $totalUp = $_POST['Up']['total_up'];
// if ($model->total_up != $totalUp) {
// Yii::app()->user->setFlash('error', 'Tidak dapat merubah Pagu UP.');
// } else {
$model->attributes = $_POST['Up'];
if ($model->update()) {
$details = UpDetail::model()->findAllByAttributes(array('up_number_of_letter' => $modelNumber));
foreach ($details as $detail) {
$detail->up_number_of_letter = $numberLetter;
$detail->update();
}
$realizations = Realization::model()->findAllByAttributes(array('number_of_letter' => $modelNumber));
foreach ($realizations as $realization) {
$realization->up_number_of_letter = $numberLetter;
$realization->update();
}
Yii::app()->user->setFlash('success', 'Data berhasil diupdate');
$this->redirect(array('view', 'id' => $model->id));
}
// }
}
$this->render('update', array('model' => $model, 'detail' => $detail));
}
