/** * Displays the requested report * */ public function actionReport() { //throw(new Exception('just die')); if ((isset($_POST['ShowReport']) || isset($_POST['DownloadPDF']) || isset($_POST['DownloadExcel'])) && isset($_POST['reportId'])) { $this->_model = Report::model()->findbyPk($_POST['reportId']); if ($this->_model === null) { throw new CHttpException(404, 'The requested page does not exist.'); } //delete all the old report parameter values ReportUserLastUsedParams::model()->dbConnection->createCommand('DELETE FROM ReportUserLastUsedParams WHERE reportId=' . $this->_model->id . ' AND userId=' . Yii::app()->user->id)->execute(); //build the Sql sats $aliases = array(); $replacements = array(); $repParams = $this->_model->reportparameters; $parameterValues = array(); $graphicFilename = ""; $title = ""; if (isset($repParams) && count($repParams) > 0) { foreach ($repParams as $n => $repParam) { //get the new parameter values if ($repParam->dataType == 'HIDDEN_NO_SHOW_HEAD' || $repParam->dataType == 'HIDDEN_SHOW_HEAD') { if (strlen($repParam->phpSecondaryInfo) > 0) { eval($repParam->phpSecondaryInfo); } else { $defaultValue = $repParam->defaultValue; if ($repParam->isDefaultPhp) { eval($defaultValue); } } $parameterValues[$n] = $defaultValue; } else { if (isset($_POST[$n])) { if ($repParam->dataType == 'DATE') { $parameterValues[$n] = User::parseDate($_POST[$n]); } else { $parameterValues[$n] = $_POST[$n]; } if (strlen($title) == 0) { $title = $_POST[$n]; } $negateGraphicValues = false; if ($repParam->name == 'Negate values for graphics') { $negateGraphicValues = (int) $_POST[$n]; } //save the parameter values $saveLast = new ReportUserLastUsedParams(); $saveLast->reportId = $this->_model->id; $saveLast->userId = Yii::app()->user->id; $saveLast->paramId = $n; $saveLast->LastUsedValue = $parameterValues[$n]; $saveLast->save(); } } //get the aliases if (strlen($repParam->alias) > 0) { $aliases[] = $repParam->alias; if ($repParam->dataType == 'HIDDEN_NO_SHOW_HEAD' || $repParam->dataType == 'HIDDEN_SHOW_HEAD') { $defaultValue = $repParam->defaultValue; if ($repParam->isDefaultPhp) { eval($defaultValue); } $replacements[] = $defaultValue; } else { if ($repParam->dataType == 'DATE') { $replacements[] = User::parseDate($_POST[$n]); } else { $replacements[] = $_POST[$n]; } } if ($repParam->alias == '{ShowGraph}' && strlen($_POST[$n]) > 0) { $graphicFilename = $_POST[$n]; } } } } $sqlSelect = str_replace($aliases, $replacements, $this->_model->selectSql); // var_dump($aliases); // var_dump($replacements); //echo $sqlSelect.'<br><br>'; // echo $sqlSelect; die(); //execute the Sql sats //Yii::log($sqlSelect, CLogger::LEVEL_INFO, 'system.web.CController'); $command = Report::model()->dbConnection->createCommand($sqlSelect); try { $reader = $command->query(); //foreach($reader as $row)print_r($row); //die(); } catch (Exception $e) { echo '<h2>Died on Sql execution</h2>' . $sqlSelect; throw $e; } $imageFileNames = array(); $imageFilePaths = array(); if (strlen($graphicFilename) > 0) { $images = $this->readXmlGraphicFile($graphicFilename, false, $reader, $title, $negateGraphicValues, $imageFileNames); $imageFileNames = $images[1]; $imageFilePaths = $images[0]; //must do this again because the reader can only go forwards $reader = $command->query(); } //update last used report for printout $printoutview = ""; if (isset($_POST['printoutview'])) { $printoutview = $_POST['printoutview']; } $option = Options::model()->find('name=\'isReportForPrintout\' AND companyId=0 AND userId=' . Yii::app()->user->id); $option->datavalue = $printoutview == 1 ? 'true' : 'false'; $option->save(); Yii::app()->user->setState('isReportForPrintout', $printoutview); //update last used black and white $option = Options::model()->find('name=\'isReportBlackAndWhite\' AND companyId=0 AND userId=' . Yii::app()->user->id); $option->datavalue = false; $option->datavalue = isset($_POST['blackandwhite']) && $_POST['blackandwhite'] == 1 ? 'true' : 'false'; $option->save(); Yii::app()->user->setState('isReportBlackAndWhite', isset($_POST['blackandwhite']) && $_POST['blackandwhite'] == 1 ? 1 : 0); if (isset($_POST['blackandwhite']) && $_POST['blackandwhite'] == 1) { Yii::app()->user->setState('reportCssFile', $this->_model->cssBwFileName); } else { Yii::app()->user->setState('reportCssFile', $this->_model->cssColorFileName); } //get the number and date formats $cLoc = CLocale::getInstance('en'); $numberFormatter = $cLoc->getNumberFormatter(); $dateFormatter = $cLoc->getDateFormatter(); // echo "<pre>"; // var_dump($dateFormatter); // echo "</pre>"; // die(); $numberFormat = User::getNumberFormat(); $pdf_name = ""; $cron_job = FALSE; // if from cron job if (isset($_POST['cron'])) { $pdf_name = "reports/lazy8report_" . date('Y-m-d_H.i.s'); $cron_job = TRUE; } if (isset($_POST['DownloadExcel'])) { Yii::import('application.controllers.excel.*'); require_once "exportExcel.php"; new exportExcel($this->_model, $reader, $pdf_name, $numberFormatter, $dateFormatter, $numberFormat, $printoutview, $parameterValues); die; } else { if (isset($_POST['DownloadPDF'])) { // var_dump($numberFormat); // die(); Yii::import('application.controllers.tcpdf.*'); require_once "lazy8tcpdf.php"; new lazy8tcpdf($this->_model, $reader, $pdf_name, $cron_job, $numberFormatter, $dateFormatter, $numberFormat, $printoutview, $parameterValues, $imageFileNames, isset($_POST['blackandwhite']) && $_POST['blackandwhite'] == 1 ? 1 : 0, strpos(Yii::app()->user->getState('reportCssFile'), "wide") === false); // if cron job was run then email user with the pdf attatchment if (isset($_POST['cron'])) { $accountID = $_POST['accountID']; $sql = "SELECT Account.code AS accountcode, Account.name AS accountname, Account.email AS email, Account.balance_threshold AS threshold, Account.days AS days\n\t\t\t\t\t\t\t\tFROM Account\n\t\t\t\t\t\t\t\tWHERE Account.code = '{$accountID}'"; $account = Yii::app()->db->createCommand($sql)->queryAll(); $to = $account['email']; $subject = 'Weekly Report'; $headers = "From: Accounting@thaiconnections.org \r\n"; $headers .= "Reply-To: no-reply@thaiconnections.org\r\n"; $headers .= "MIME-Version: 1.0\r\n"; $headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n"; $message = '<html><body>'; $message .= '<p>Hello,</p>'; $message .= '<p>This a automated notification from Thaiconnections Accounting website that your weekly report for: <strong>' . $account['accountname'] . ' </strong>is ready to be downloaded. Please click the link to view your report.<p>'; $message .= '<phttp://accounting.thaiconnections.org/' . $pdf_name . '.pdf<p>'; $message .= '<p>Kindest Regards,<br>'; $message .= 'Thaiconnections Accounting</p>'; $message .= '</body></html>'; if (mail($to, $subject, $message, $headers)) { echo "Email sent...<br>"; } else { echo "Mail did not send. Error occurred"; } } } else { //show the report $this->render('report', array('model' => $this->_model, 'reader' => $reader, 'numberFormatter' => $numberFormatter, 'dateFormatter' => $dateFormatter, 'numberFormat' => $numberFormat, 'printoutview' => $printoutview, 'parameterValues' => $parameterValues, 'imageFileNames' => $imageFilePaths)); } } } else { //show the report parameter selection page. $noActivityLogSelectionString = ""; if (!Yii::app()->user->getState('allowChangeLog')) { $noActivityLogSelectionString = " AND name<>'Activity log'"; } $allRepModels = Report::model()->findAll(array('select' => 'id,name', 'order' => 'sortOrder', 'condition' => '(companyId=' . Yii::app()->user->getState('selectedCompanyId') . ' OR companyId=0)' . $noActivityLogSelectionString)); $reports = CHtml::listData($allRepModels, 'id', 'name'); foreach ($reports as $n => $reportsTrans) { $reports[$n] = Yii::t('lazy8', $reportsTrans); } if (isset($_POST['reportId']) && $_POST['reportId']) { $model = Report::model()->findbyPk($_POST['reportId']); //update last used report $option = Options::model()->find('name=\'lastPrintedReportId\' AND companyId=0 AND userId=' . Yii::app()->user->id); $option->datavalue = $_POST['reportId']; $option->save(); Yii::app()->user->setState('lastPrintedReportId', $_POST['reportId']); } else { //always try to select any report $selectReport = Yii::app()->user->getState('lastPrintedReportId'); $model = Report::model()->findbyPk($selectReport); if (isset($model)) { $_POST['reportId'] = $selectReport; } elseif (isset($allRepModels) && count($allRepModels) > 0) { $_POST['reportId'] = $allRepModels[0]->id; $model = $allRepModels[0]; } } //preselect the values to what was done before... $repParams = $model->reportparameters; if (isset($repParams) && count($repParams) > 0) { foreach ($repParams as $n => $repParam) { if ($repParam->dataType == 'FREE_TEXT' || $repParam->dataType == 'DROP_DOWN' || $repParam->dataType == 'DATE' || $repParam->dataType == 'BOOLEAN') { $lastChosen = ReportUserLastUsedParams::model()->find('reportId=' . $model->id . ' AND userId=' . Yii::app()->user->id . ' AND paramId=' . $n); if (isset($lastChosen)) { $defaultValue = $lastChosen->LastUsedValue; } else { $defaultValue = $repParam->defaultValue; if ($repParam->isDefaultPhp) { eval($defaultValue); } } if ($repParam->dataType == 'DATE') { $_POST[$n] = User::getDateFormatted($defaultValue); } else { $_POST[$n] = $defaultValue; } } } } $_POST['blackandwhite'] = Yii::app()->user->getState('isReportBlackAndWhite') ? 1 : 0; $_POST['printoutview'] = Yii::app()->user->getState('isReportForPrintout') ? 1 : 0; $this->render('reportparams', array('reports' => $reports, 'model' => $model)); } }
private function getFromTempTrans() { $trans = TempTrans::model()->findAll(array('condition' => 'userId=' . Yii::app()->user->id, 'order' => 'rownum')); if ($trans !== null) { $cLoc = CLocale::getInstance('en'); $numberFormatter = $cLoc->getNumberFormatter(); $dateformatter = $cLoc->getDateFormatter(); $numberFormat = User::getNumberFormat(); $models = array(); //make sure the data is current $comp = Company::model()->findbyPk(Yii::app()->user->getState('selectedCompanyId')); $per = Period::model()->findbyPk(Yii::app()->user->getState('selectedPeriodId')); foreach ($trans as $transrow) { //see if there is new data to be added.. if (isset($_POST['TempTrans'][$transrow->rownum]) && $_POST['TempTrans'][$transrow->rownum]) { //make sure only one of debit or credit are filled $olddebit = $this->parseNumber($transrow->amountdebit, $cLoc); $transrow->attributes = $_POST['TempTrans'][$transrow->rownum]; $amountdebit = $this->parseNumber($transrow->amountdebit, $cLoc); $amountcredit = $this->parseNumber($transrow->amountcredit, $cLoc); $tmpDate = date_create_from_format('', $transrow->invDate); if ($tmpDate) { $tempdate = date_format($tmpDate, 'Y-m-d'); } else { if (!date_create_from_format('Y-m-d', $transrow->invDate)) { $tempdate = date('Y-m-d'); } else { $tempdate = $transrow->invDate; } } $transrow->invDate = $tempdate; // $transrow->invDate= User::parseDate($transrow->invDate,$cLoc); if ($amountdebit != 0 and $amountcredit != 0) { //keep only the newly added amount if ($olddebit != '') { $transrow->amountdebit = ''; } else { $transrow->amountcredit = ''; } } } $amountdebit = $this->parseNumber($transrow->amountdebit, $cLoc); $amountcredit = $this->parseNumber($transrow->amountcredit, $cLoc); //amount must be positive at this stage if ($amountdebit < 0) { $amountdebit = -$amountdebit; } if ($amountcredit < 0) { $amountcredit = -$amountcredit; } //this gets rid of extra zeros if (strlen($amountcredit) > 0) { $amountcredit += 0.0; } if (strlen($amountdebit) > 0) { $amountdebit += 0.0; } if (!isset($_GET['id'])) { $transrow->regDate = date('Y-m-d'); $transrow->periodNum = $per->lastPeriodTransNum + 1; $transrow->companyNum = $comp->lastAbsTransNum + 1; } $transrow->amountdebit = $amountdebit; $transrow->amountcredit = $amountcredit; $transrow->save(); $transrow->amountdebit = $this->formatNumber($transrow->amountdebit, $numberFormatter, $numberFormat); $transrow->amountcredit = $this->formatNumber($transrow->amountcredit, $numberFormatter, $numberFormat); $transrow->invDate = User::getDateFormatted($transrow->invDate, $cLoc, $dateformatter); $transrow->regDate = User::getDateFormatted($transrow->regDate, $cLoc, $dateformatter); $transrow->dateChanged = User::getDateFormatted($transrow->dateChanged, $cLoc, $dateformatter); } //return what was found in the temptrans table. $models = $trans; } else { //we really should never come here, but just in case... $models = $this->getBlankTempTrans(0); } return $models; }
private function getFromTempTrans($template, $rowcount) { $trans = null; if (isset($_POST['TempTrans'])) { $trans = TempTrans::model()->findAll(array('condition' => 'userId=' . Yii::app()->user->id, 'order' => 'rownum')); } if ($trans !== null) { $cLoc = CLocale::getInstance('en'); $numberFormatter = $cLoc->getNumberFormatter(); $dateformatter = $cLoc->getDateFormatter(); $numberFormat = User::getNumberFormat(); $models = array(); //make sure the data is current $comp = Company::model()->findbyPk(Yii::app()->user->getState('selectedCompanyId')); $per = Period::model()->findbyPk(Yii::app()->user->getState('selectedPeriodId')); $multipleRowNumber = 0; foreach ($trans as $n => $transrow) { if ($n < count($template->templateRows) && $template->templateRows[$n]->allowRepeatThisRow) { $multipleRowNumber = $n; } $templateRowNum = $n; if ($n >= count($template->templateRows)) { $templateRowNum = $multipleRowNumber; } //see if there is new data to be added.. if (isset($_POST['TempTrans'][$transrow->rownum]) && $_POST['TempTrans'][$transrow->rownum]) { //make sure only one of debit or credit are filled $olddebit = TransController::parseNumber($transrow->amountdebit, $cLoc); $transrow->attributes = $_POST['TempTrans'][$transrow->rownum]; $amountdebit = TransController::parseNumber($transrow->amountdebit, $cLoc); $amountcredit = TransController::parseNumber($transrow->amountcredit, $cLoc); $transrow->invDate = User::parseDate($transrow->invDate, $cLoc); if ($amountdebit != 0 and $amountcredit != 0) { //keep only the newly added amount if ($olddebit != '') { $transrow->amountdebit = ''; } else { $transrow->amountcredit = ''; } } } $amountdebit = TransController::parseNumber($transrow->amountdebit, $cLoc); $amountcredit = TransController::parseNumber($transrow->amountcredit, $cLoc); //echo $transrow->amountdebit;die(); if ($template->templateRows[$templateRowNum]->allowMinus && bccomp($amountcredit, 0, 5) < 0) { $amountdebit = -$amountcredit; $amountcredit = 0; } if ($template->templateRows[$templateRowNum]->allowMinus && bccomp($amountdebit, 0, 5) < 0) { $amountcredit = -$amountdebit; $amountdebit = 0; } //amount must be positive at this stage if ($amountdebit < 0) { $amountdebit = -$amountdebit; } if ($amountcredit < 0) { $amountcredit = -$amountcredit; } //this gets rid of extra zeros if (strlen($amountcredit) > 0) { $amountcredit += 0.0; } if (strlen($amountdebit) > 0) { $amountdebit += 0.0; } if (!isset($_GET['id'])) { $transrow->regDate = date('Y-m-d'); $transrow->periodNum = $per->lastPeriodTransNum + 1; $transrow->companyNum = $comp->lastAbsTransNum + 1; } if ($template->forceDateToday) { $transrow->invDate = date('Y-m-d'); } $transrow->amountdebit = $amountdebit; $transrow->amountcredit = $amountcredit; $transrow->save(); $transrow->amountdebit = TransController::formatNumber($transrow->amountdebit, $numberFormatter, $numberFormat); $transrow->amountcredit = TransController::formatNumber($transrow->amountcredit, $numberFormatter, $numberFormat); $transrow->invDate = User::getDateFormatted($transrow->invDate, $cLoc, $dateformatter); $transrow->regDate = User::getDateFormatted($transrow->regDate, $cLoc, $dateformatter); $transrow->dateChanged = User::getDateFormatted($transrow->dateChanged, $cLoc, $dateformatter); } //return what was found in the temptrans table. $models = $trans; } else { $this->getBlankTempTrans($models, $rowcount); } return $models; }