public function getchequestotalsajaxAction()
{
try {
$request = $this->getRequest();
if ($this->getRequest()->isPost()) {
$this->_helper->layout->disableLayout();
$this->_helper->viewRenderer->setNoRender();
$provId = (int) $request->provId;
$checksList = json_decode($request->checks);
if ($provId) {
$pMapper = new Gyuser_Model_ProvidersDataMapper();
$provData = $pMapper->GetProviderByIdSimple($provId);
$liqDate = date('Y-m-d');
$cMapper = new Gyuser_Model_ChequesDataMapper();
$result = $cMapper->GetChequesTotalsJson($provId, $checksList, $provData, $liqDate);
if ($result) {
echo json_encode($result);
} else {
echo 'f';
}
}
}
} catch (Exception $e) {
echo $e;
}
}
public function dashboardAction()
{
try {
$sessionNamespace = new Zend_Session_Namespace();
if ($sessionNamespace->loginAuth == true) {
$authDetail = $sessionNamespace->authDetail;
$oprType = $authDetail->getType();
$this->view->oprType = $oprType;
$this->view->oprId = $authDetail->getId();
if ($oprType == 1) {
/*
$cMapper = new Gyuser_Model_OtherCavesDataMapper();
$cObj = new Gyuser_Model_OtherCaves();
$cObj->setId(1);
$cList = $cMapper->GetCaveById($cObj);
$this->view->cList = $cList;
$sMapper = new Gyuser_Model_SupplierOperationsDataMapper();
$sList = $sMapper->GetAllSuppliers();
*/
$pMapper = new Gyuser_Model_ProvidersDataMapper();
$this->view->pList = $pMapper->getProviders();
$cMapper = new Gyuser_Model_ChequesDataMapper();
$cObj = new Gyuser_Model_Cheques();
$cObj->setStatus(1);
$this->view->ctAmount = $cMapper->GetTotalAmountByStats($cObj);
$cObj->setStatus(4);
$this->view->cpAmount = $cMapper->GetTotalAmountByStats($cObj);
$sMapper = new Gyuser_Model_OperationsDataMapper();
$current_date = date('Y-m-d');
/*************************************** Operaciones widget values *****************************/
$this->view->CurrentMonthHalfCave = $sMapper->GetOperationsAmount(findFirstAndLastDay($current_date), true);
$this->view->CurrentMonth = $sMapper->GetOperationsAmount(findFirstAndLastDay($current_date), true);
$this->view->CurrentQuarter = $sMapper->GetOperationsAmount(findFirstAndLastDatesOfCurrentQuater($current_date), true);
$this->view->CurrentSemester = $sMapper->GetOperationsAmount(findFirstAndLastDatesOfCurrentSemester($current_date), true);
$this->view->CurrentYear = $sMapper->GetOperationsAmount(findFirstAndLastDatesOfCurrentYear($current_date), true);
$this->view->PrevMonthToDate = $sMapper->GetOperationsAmount(prevFirstAndCurrentDay($current_date), true);
$this->view->PrevQuaterToDate = $sMapper->GetOperationsAmount(findFirstAndLastCurrentDatesOfPrevQuater($current_date), true);
$this->view->PrevSemesterToDate = $sMapper->GetOperationsAmount(findFirstAndLastCurrentDatesOfPrevSemester($current_date), true);
$this->view->PrevYearToDate = $sMapper->GetOperationsAmount(findFirstAndLastCurrentDatesOfPrevYear($current_date), true);
$PrevMonthTotal = $sMapper->GetOperationsAmount(findPrevMonthFirstAndLastDay($current_date), true, true);
$PrevQuaterTotal = $sMapper->GetOperationsAmount(findFirstAndLastDatesOfPrevQuater($current_date), true, true);
$PrevSemesterTotal = $sMapper->GetOperationsAmount(findFirstAndLastDatesOfPrevSemester($current_date), true, true);
$PrevYearTotal = $sMapper->GetOperationsAmount(findFirstAndLastDatesOfPrevYear($current_date), true, true);
$this->view->PrevMonthTotal = $PrevMonthTotal;
$this->view->PrevQuaterTotal = $PrevQuaterTotal;
$this->view->PrevSemesterTotal = $PrevSemesterTotal;
$this->view->PrevYearTotal = $PrevYearTotal;
$this->view->PrevMonthToDateAvg = $sMapper->GetOperationsAmountAvg(findPrevMonthFirstAndLastDay($current_date), findFirstAndLastDay($current_date), $PrevMonthTotal);
$this->view->PrevQuaterToDateAvg = $sMapper->GetOperationsAmountAvg(findFirstAndLastDatesOfPrevQuater($current_date), findFirstAndLastDatesOfCurrentQuater($current_date), $PrevQuaterTotal);
$this->view->PrevSemesterToDateAvg = $sMapper->GetOperationsAmountAvg(findFirstAndLastDatesOfPrevSemester($current_date), findFirstAndLastDatesOfCurrentSemester($current_date), $PrevSemesterTotal);
$this->view->PrevYearToDateAvg = $sMapper->GetOperationsAmountAvg(findFirstAndLastDatesOfPrevYear($current_date), findFirstAndLastDatesOfCurrentYear($current_date), $PrevYearTotal);
$OperationsAmountByPrevMonths = $sMapper->GetOperationsAmountByPrevMonths($current_date, 4);
$this->view->OperationsAmountByPrevMonths = json_encode($OperationsAmountByPrevMonths, JSON_FORCE_OBJECT);
/************************************ EOF Operaciones widget values ***********************************/
$AmounByPayedCheques = $cMapper->GetAmounByPayedCheques();
$AmounByPayedCheques0To30Days = $cMapper->GetAmounByPayedCheques0To30Days();
$AmounByPayedCheques30To60Days = $cMapper->GetAmounByPayedCheques30To60Days();
$AmounByPayedCheques60To90Days = $cMapper->GetAmounByPayedCheques60To90Days();
$AmounByPayedCheques120Days = $cMapper->GetAmounByPayedCheques120Days();
$this->view->AmounByPayedCheques = $AmounByPayedCheques;
$this->view->AmounByPayedCheques0To30Days = $AmounByPayedCheques0To30Days;
$this->view->AmounByPayedCheques30To60Days = $AmounByPayedCheques30To60Days;
$this->view->AmounByPayedCheques60To90Days = $AmounByPayedCheques60To90Days;
$this->view->AmounByPayedCheques120Days = $AmounByPayedCheques120Days;
$AmounByChequesStatusAcredited = $cMapper->GetAmounByChequesStatusAcredited($current_date);
$ChequeAmountByPrevMonths = $cMapper->GetChequeAmountByPrevMonths($current_date, 4);
$this->view->AmounByChequesStatusAcredited = $AmounByChequesStatusAcredited;
$this->view->ChequeAmountByPrevMonths = $ChequeAmountByPrevMonths;
$RejectedChequesBalanceByCal = $cMapper->GetRejectedChequesBalanceByCal();
$PrecOfRejectedChequesByPassed = $cMapper->GetPrecOfRejectedChequesByPassed();
$RejectedChequesBalanceByCaves = $cMapper->getRejectedChequesBalance();
//GetRejectedChequesBalanceByCaves();
$RejectedChequesBalanceByCavesAndMonths = $cMapper->GetRejectedChequesBalanceByCavesAndMonths($current_date, 4);
/*
$AmounByPayedCheques0To30DaysByCaves = $cMapper->GetAmounByPayedCheques0To30DaysByCaves();
$AmounByPayedCheques30To60DaysByCaves = $cMapper->GetAmounByPayedCheques30To60DaysByCaves();
$AmounByPayedCheques60To90DaysByCaves = $cMapper->GetAmounByPayedCheques60To90DaysByCaves();
$AmounByPayedChequesAfter90DaysByCaves = $cMapper->GetAmounByPayedChequesAfter90DaysByCaves();
$this->view->AmounByPayedCheques0To30DaysByCaves = $AmounByPayedCheques0To30DaysByCaves;
$this->view->AmounByPayedCheques30To60DaysByCaves = $AmounByPayedCheques30To60DaysByCaves;
$this->view->AmounByPayedCheques60To90DaysByCaves = $AmounByPayedCheques60To90DaysByCaves;
$this->view->AmounByPayedChequesAfter90DaysByCaves = $AmounByPayedChequesAfter90DaysByCaves;
*/
$this->view->amountsPassedByDates = $cMapper->getEachAmountPassedByDates();
$this->view->RejectedChequesBalanceByCal = $RejectedChequesBalanceByCal;
$this->view->PrecOfRejectedChequesByPassed = $PrecOfRejectedChequesByPassed;
$this->view->RejectedChequesBalanceByCaves = $RejectedChequesBalanceByCaves;
$this->view->RejectedChequesBalanceByCavesAndMonths = $RejectedChequesBalanceByCavesAndMonths;
}
/* notification tasks starts */
$tasksModel = new Gyuser_Model_CRMTasks();
$this->view->myTasks = $tasksModel->getMyTasks();
//$this->view->pending = $tasksModel->getMyPendingTasks();
if ($oprType == 1) {
$this->view->adminPending = $tasksModel->getMyPendingTasks(true);
$this->view->adminMonitor = $tasksModel->getAdminMonitor();
$this->view->completedTasks = FALSE;
} else {
$this->view->adminPending = FALSE;
$this->view->adminMonitor = FALSE;
$this->view->completedTasks = $tasksModel->getCompletedTasks();
}
$this->view->oprType = $oprType;
/* notification tasks ends */
} else {
$this->_helper->redirector('login', 'index', 'gyuser');
}
} catch (Exception $e) {
echo $e;
}
}
private function displayLiqTable($liqId, &$filename = null, &$provEmail = null, &$provName = null)
{
$pre_val = 0;
$liqId = (int) $liqId;
if ($liqId) {
$lMapper = new Gyuser_Model_LiquidacionesDataMapper();
$lObj = new Gyuser_Model_Liquidaciones();
$lObj->setId($liqId);
$lqList = $lMapper->GetLiquidacionesById($lObj);
$provId = $lqList['provider_id'];
$liqDate = $lqList['date'];
$html = '
<table cellpadding="0" cellspacing="0" border="1" class="liq-table">
<thead>
<tr>
<td colspan="6" class="liq-header">OPERACIONES COMPARTIDAS</td>
</tr>
<tr class="liq-header2">
<th>FECHA OP.</th>
<th>NOMBRE</th>
<th>TOTAL</th>
<th>GY</th>
<th>COMISIONES</th>
<th>SALDO</th>
</tr>
</thead>
<tbody class="gridtbody">';
$oMapper = new Gyuser_Model_OperationsDataMapper();
$oObj = new Gyuser_Model_Operations();
$oObj->setLiquidacion_id($liqId);
$opertationsList = $oMapper->GetOperationsByLiquidacionIdJson($oObj);
$pArr = $opertationsList;
$strBld = '';
$pay_final_amount = 0;
if (count($pArr)) {
foreach ($pArr as $pRow) {
$pay_final_amount += $pRow['prov_payment'];
$halfAmt = number_format($pRow['amount'] / 2, 2, '.', '');
$html .= "<tr >\n <td>{$pRow['date']}</td>\n <td>{$pRow['first_name']} {$pRow['last_name']}</td>\n <td>{$this->formatMoney($pRow['amount'])}</td>\n <td>{$this->formatMoney($halfAmt)}</td>\n <td>{$this->formatMoney($pRow['comision_amt'])}</td>\n <td>{$this->formatMoney($pRow['prov_payment'])}</td>\n </tr>";
}
}
$html .= '</tbody>
<tfoot>
<tr class="liq-footer"><td colspan="4"> </td><td>TOTAL</td><td><span class="operationsTotal_span">' . $this->formatMoney($pay_final_amount) . '</span></td></tr>
</tfoot>
</table><br />';
$html .= '<table cellpadding="0" cellspacing="0" border="1" class="liq-table">
<thead>
<tr>
<td colspan="6" class="liq-header">CHEQUES RECHAZADOS</td>
</tr>
<tr class="liq-header2">
<th>TITULAR</th>
<th>FECHA</th>
<th>N. CHEQUE</th>
<th>RECHAZOS</th>
<th>GASTOS</th>
<th>SALDO</th>
</tr>
</thead>
<tbody class="gridtbody">';
$cMapper = new Gyuser_Model_ChequesDataMapper();
$cObj = new Gyuser_Model_Cheques();
$cObj->setLiquidacion_id($liqId);
$cObj->setProvider_id($provId);
$pArr = $cMapper->RejectedChequeByLiquidacionesIdForProv($cObj);
$strBld = '';
$sName = '';
$rejectedTotal = 0;
//$rejectedChequesFee = number_format($rejectedChequesFee,2,'.','');
if (count($pArr)) {
foreach ($pArr as $pRow) {
$cObj->setRejected_type($pRow['rejected_type']);
$cMapper = new Gyuser_Model_ChequesDataMapper();
$rejectedChequesFee = (int) $cMapper->GetRejectionCostForProv($cObj);
$amount = $pRow['amount'];
$rChequeWithFee = $amount + $rejectedChequesFee;
$rChequeWithFee = number_format($rChequeWithFee, 2, '.', '');
$rejectedTotal += $rChequeWithFee;
$pay_final_amount += $rChequeWithFee;
$rejectedChequesFee = number_format($rejectedChequesFee, 2, '.', '');
$strBld .= <<<EOT
<tr id="RejectedCheque_{$pRow['id']}">
<td class="user_operation_id">{$pRow['first_name']} {$pRow['last_name']}</td>
<td class="user_check_n">{$pRow['date']}</td>
<td class="user_bank_name">{$pRow['check_n']}</td>
<td class="user_amount">{$this->formatMoney($amount)}</td>
<td class="user_rejected_cheque_fee">{$this->formatMoney($rejectedChequesFee)}</td>
<td class="pay_amount_cls">{$this->formatMoney($rChequeWithFee)}</td>
</tr>
EOT;
}
}
$pay_final_amountInv = $pay_final_amount * -1;
$pre_val = $pre_val * -1;
$html .= $strBld;
$html .= ' </tbody>
<tfoot>
<tr class="liq-footer"><td colspan="4"> </td><td>TOTAL</td><td>' . $this->formatMoney($rejectedTotal) . '</td></tr>
</tfoot>
</table><br />';
$html .= '
<table cellpadding="0" cellspacing="0" border="1" class="liq-table">
<thead>
<tr>
<td colspan="9" class="liq-header">CALCULO DE DESCUENTO</td>
</tr>
<tr class="liq-header2">
<th>TITULAR</th>
<th>EMPRESA</th>
<th>FECHA</th>
<th>NUMERO</th>
<th>IMPORTE</th>
<th>CANT DIAS</th>
<th>IMP. AL CHEQUE</th>
<th>DESCUENTO</th>
<th>SALDO A HOY</th>
</tr>
</thead>
<tbody class="gridtbody">';
if ($lqList['committed'] == 1 || $lqList['committed'] == 2) {
$provData = $lMapper->getProvData($lqList['id']);
} else {
//not committed. Get values from provider today values
$pMapper = new Gyuser_Model_ProvidersDataMapper();
$provData = $pMapper->GetProviderByIdSimple($provId);
}
$checksList = $cMapper->GetChequeDetailsByLiquidacionIdJson($liqId, $provData, $liqDate);
$totalFinalAmount = 0;
foreach ($checksList as $item) {
$html .= "<tr>\n <td>{$item['first_name']} {$item['last_name']}</td>\n <td>{$item['business']}</td>\n <td>{$item['date']}</td>\n <td>{$item['check_n']}</td>\n <td>{$this->formatMoney($item['amount'])}</td>\n <td>{$item['days']}</td>\n <td>{$this->formatMoney($item['imp_al_cheque'])}</td>\n <td>{$this->formatMoney($item['descuento'])}</td>\n <td>{$this->formatMoney($item['today_value'])}</td>\n </tr>";
}
$html .= "</tbody>";
$cTotals = $cMapper->GetChequesTotalsJson($provId, $checksList, $provData, $liqDate);
$html .= "<tfoot>\n <tr class='liq-header3'><th>N. CHEQUES</th><th>DIAS PROM.</th><th>BRUTO</th><th>IMP. AL CHEQUE</th><th>INTERESES</th><th>GASTOS INT.</th><th>GASTOS CAP.</th><th>GASTOS OTROS</th><th>SUBTOTAL</th></tr>\n <tr>\n <td>{$cTotals['chequeChkCount']}</td>\n <td>{$cTotals['dayAvg']}</td>\n <td>{$this->formatMoney($cTotals['bruto'])}</td>\n <td>{$this->formatMoney($cTotals['impuestoAlCheque'])}</td>\n <td>{$this->formatMoney($cTotals['intereses'])}</td>\n <td>{$this->formatMoney($cTotals['gastosInterior'])}</td>\n <td>{$this->formatMoney($cTotals['gastosGeneral'])}</td>\n <td>{$this->formatMoney($cTotals['gastosOtros'])}</td>\n <td>{$this->formatMoney($cTotals['payingAmount'])}</td>\n </tr>\n </tfoot></table>";
$html_style = <<<EOT
<style type="text/css">
.liq-table {
border:1px solid #777;
}
.liq-table td, .liq-table th {
border-top: 0px;
border-right: 1px solid #e5e5e5;
border-bottom: 1px solid #e5e5e5;
border-left: 0px;
border:1px solid #ccc;
padding:3px 7px;
font:12px Geneva, sans-serif;
background-color:#fff;
white-space:nowrap;
}
td.liq-header {
background-color:#777;
text-align:center;
color:#fff;
font:bold 13px Geneva, sans-serif;
text-transform:uppercase;
border-width:0;
white-space:nowrap;
}
.liq-header2 td, .liq-header2 th{
background-color:#f7f7f7;
}
.liq-header3 td, .liq-header3 th{
background-color:#e3e3e3;
}
.liq-header2 td, .liq-header2 th, .liq-header3 td, .liq-header3 th{
font:bold 12px Geneva, arial, sans-serif;
text-transform: uppercase
white-space:nowrap;
}
.liq-footer td{
background-color:#e3e3e3;
font-weight:bold;
border-top:1px solid #333;
white-space:nowrap;
}
</style>
EOT;
$html_header = "<h1 style='font:bold 26px georgia, geneva'>Liquidacion {$lqList['id']}</h1>\n <table cellpadding='0' cellspacing='0' border='1' class='liq-table'>\n <tr><th style='text-align:right'>PROVEEDOR:</th><td>{$provData->getName()}</td></tr>\n <tr><th style='text-align:right'>FECHA:</th><td>{$lqList['date']}</td></tr>\n <tr><th style='text-align:right'>ACREDITACIONES:</th><td>{$this->formatMoney($lqList['acreditacion'])}</td></tr>\n <tr><th style='text-align:right'>TOTAL A LIQUIDAR</th><td>{$this->formatMoney($lqList['amount_debt'])}</td></tr>\n <tr><th style='text-align:right'>IMPORTE PAGADO:</th><td>{$this->formatMoney($lqList['amount_payed'])}</td></tr>\n <tr><th style='text-align:right'>SALDO:</th><td>{$this->formatMoney($lqList['current_account_balance'])}</td></tr>\n </table><br /><br />";
$provEmail = $provData->getEmail();
$provName = str_replace(' ', '-', $provData->getName());
$liqDate = str_replace('/', '-', $liqDate);
$filename = 'Liq' . $liqId . '_' . $provName . '_' . $liqDate;
return $html_style . $html_header . $html;
} else {
throw new Exception('liqId missing in display pdf generator -> displayTable function');
}
}
public function getEachAmountPassedByDates()
{
$provMapper = new Gyuser_Model_ProvidersDataMapper();
$provs = $provMapper->GetAllProviders();
$result = array();
$db = Zend_Db_Table::getDefaultAdapter();
$provNames = array();
$amounts1 = array();
$amounts2 = array();
$amounts3 = array();
$amounts4 = array();
foreach ($provs as $prov) {
$query = "SELECT SUM(amount) AS sum_amount FROM cheques WHERE status = 4 AND provider_id = {$prov->id} AND date > date_add(CURRENT_DATE, INTERVAL 90 DAY)";
$stmt = $db->query($query);
$row1 = $stmt->fetchAll();
$query = "SELECT SUM(amount) AS sum_amount FROM cheques WHERE status = 4 AND provider_id = {$prov->id} AND date > date_add(CURRENT_DATE, INTERVAL 60 DAY) AND date < date_add(CURRENT_DATE, INTERVAL 90 DAY)";
$stmt = $db->query($query);
$row2 = $stmt->fetchAll();
$query = "SELECT SUM(amount) AS sum_amount FROM cheques WHERE status = 4 AND provider_id = {$prov->id} AND date > date_add(CURRENT_DATE, INTERVAL 30 DAY) AND date < date_add(CURRENT_DATE, INTERVAL 60 DAY)";
$stmt = $db->query($query);
$row3 = $stmt->fetchAll();
$query = "SELECT SUM(amount) AS sum_amount FROM cheques WHERE status = 4 AND provider_id = {$prov->id} AND date > CURRENT_DATE AND date < date_add(CURRENT_DATE, INTERVAL 30 DAY)";
$stmt = $db->query($query);
$row4 = $stmt->fetchAll();
$provNames[] = $prov->name;
$amounts1[] = floatval($row1[0]['sum_amount']);
$amounts2[] = floatval($row2[0]['sum_amount']);
$amounts3[] = floatval($row3[0]['sum_amount']);
$amounts4[] = floatval($row4[0]['sum_amount']);
}
$result['names'] = $provNames;
$result['amounts1'] = $amounts1;
$result['amounts2'] = $amounts2;
$result['amounts3'] = $amounts3;
$result['amounts4'] = $amounts4;
/*
$query = 'select SUM(amount) as sum_amount from cheques where status = 4 and credit_provider_id = 1 and date > date_add(CURRENT_DATE, INTERVAL 90 DAY)';
$result = array();
$db = Zend_Db_Table::getDefaultAdapter();
$stmt = $db->query($query);
$row = $stmt->fetchAll();
$result[0] = floatval($row[0]['sum_amount']);
$query = 'select SUM(amount) as sum_amount from cheques where status = 4 and credit_provider_id = 2 and date > date_add(CURRENT_DATE, INTERVAL 90 DAY)';
$db = Zend_Db_Table::getDefaultAdapter();
$stmt = $db->query($query);
$row = $stmt->fetchAll();
$result[1] = floatval($row[0]['sum_amount']);
$query = 'select SUM(amount) as sum_amount from cheques where status = 4 and credit_provider_id IS NULL and date > date_add(CURRENT_DATE, INTERVAL 90 DAY)';
$db = Zend_Db_Table::getDefaultAdapter();
$stmt = $db->query($query);
$row = $stmt->fetchAll();
$result[2] = floatval($row[0]['sum_amount']);
*/
return $result;
}
public function GetOperationsByLiquidacionIdJson(Gyuser_Model_Operations $obj)
{
$table = $this->getDbTable();
$select = $table->select();
$select->setIntegrityCheck(false);
$select->from(array('opr' => 'operations'), array('id', 'client_id', 'date', 'amount', 'observations', 'report', 'date_added', 'state_order_id', 'cave_id'));
$select->joinLeft(array('prov' => 'providers'), 'opr.cave_id = prov.id', array('name as prov_name', 'comision_rate'));
$select->joinLeft(array('cls' => 'clients'), 'opr.client_id = cls.id', array('first_name', 'last_name'));
$select->where('opr.liquidacion_id =?', $obj->getLiquidacion_id());
$select->order('opr.id ASC');
$resultSet = $table->fetchAll($select);
if ($resultSet) {
$pMapper = new Gyuser_Model_ProvidersDataMapper();
$entries = array();
foreach ($resultSet as $row) {
$operationid = $row->id;
$comisionAmt = $pMapper->calculateComisionAmt($row->amount, $row->comision_rate);
$provPayment = $pMapper->calculateProvPayment($row->amount, $comisionAmt);
$entry = array('stateid' => $row->state_order_id, 'operationid' => $operationid, 'amount' => $row->amount, 'date' => date("d/m/Y", strtotime($row->date)), 'cave_id' => $row->cave_id, 'prov_name' => $row->prov_name, 'first_name' => $row->first_name, 'last_name' => $row->last_name, 'comision_rate' => $row->comision_rate, 'comision_amt' => $comisionAmt, 'prov_payment' => $provPayment);
$entries[$operationid] = $entry;
}
return $entries;
} else {
return null;
}
}