public function GetChequeDetailsByProvIdJson($provId, $dateFilter, $liqDate)
{
$passingLimit = 2;
//max checks that can be send to same provider.
if ($liqDate) {
//format for mysql
list($d, $m, $y) = preg_split('/\\//', $liqDate);
$liqDateSQL = sprintf('%4d%02d%02d', $y, $m, $d);
} else {
$liqDateSQL = date("Y-m-d");
}
//set as today
$table = $this->getDbTable();
$db = Zend_Db_Table::getDefaultAdapter();
$dateFilterWhere = '';
//add amount of days of checks search
if ($dateFilter) {
$posDate = mktime(0, 0, 0, date("m"), date("d") + $dateFilter, date("Y"));
$posDate = date("Y-m-d", $posDate) . '';
$dateFilterWhere = "AND cheques.date < '{$posDate}'";
}
$sql = "\n \tSELECT\n clients.id as 'client_id',\n cheques.id as 'cheque_id',\n cheques.status as 'cheque_state_id',\n cheques_status.status_list,\n cheques.operation_id,\n cheques.date as 'cheque_date',\n cheques.check_n,\n cheques.amount,\n cheques.bank_account_id,\n clients.first_name,\n clients.last_name,\n clients.business,\n bank_accounts.location_capital,\n bank_accounts.zip_code\n\tFROM\n cheques\n\tINNER JOIN\n operations ON operations.id = cheques.operation_id\n\tINNER JOIN\n bank_accounts ON cheques.bank_account_id = bank_accounts.id\n\tINNER JOIN\n cheques_status ON cheques.status = cheques_status.id\n\tINNER JOIN\n clients ON operations.client_id = clients.id\n\tWHERE\n cheques.local = true\n AND\n cheques.date >= '{$liqDateSQL}'\n {$dateFilterWhere}\n AND\n cheques.status = 1\n ORDER BY\n cheques.date\n \t";
$stmt = $db->query($sql);
$resultSet = $stmt->fetchAll();
$clientRejChecksArr = $this->getClientsWithRejectedChecks($provId);
$clientsMaxChecksArr = $this->getClientsWithMaxPassedChecks($provId, $passingLimit);
//in this case the liq is always in not-committed status so we get the prov values from prov.
$pMapper = new Gyuser_Model_ProvidersDataMapper();
$provData = $pMapper->GetProviderByIdSimple($provId);
$entries = array();
$entries['passingLimit'] = $passingLimit;
foreach ($resultSet as $row) {
$rejChecks = false;
if (in_array($row['client_id'], $clientRejChecksArr)) {
$rejChecks = true;
}
$maxChecks = false;
if (in_array($row['client_id'], $clientsMaxChecksArr)) {
$maxChecks = true;
}
$checkDetails = $this->calculateCheckDetails((double) $row['amount'], date("d/m/Y", strtotime($row['cheque_date'])), (int) $row['location_capital'], $provData, $liqDate);
$entry = array('client_id' => $row['client_id'], 'cheque_id' => $row['cheque_id'], 'operation_id' => $row['operation_id'], 'date' => date("d/m/Y", strtotime($row['cheque_date'])), 'check_n' => $row['check_n'], 'amount' => $row['amount'], 'today_value' => $checkDetails['todayValue'], 'cheque_status' => $row['status_list'], 'first_name' => $row['first_name'], 'last_name' => $row['last_name'], 'cheque_state_id' => $row['cheque_state_id'], 'status_list' => $row['status_list'], 'business' => $row['business'], 'location_capital' => $row['location_capital'], 'zip_code' => $row['zip_code'], 'days' => $checkDetails['days'], 'ac_date' => $checkDetails['acreditationDate'], 'ac_hrs' => $checkDetails['acreditationHrs'], 'descuento' => $checkDetails['daysDiscountFee'], 'imp_al_cheque' => $checkDetails['impuestoAlCheque'], 'gastos' => $checkDetails['gastos'], 'gastos_otros' => $checkDetails['gastosOtros'], 'rejChecks' => $rejChecks, 'maxChecks' => $maxChecks);
if ($rejChecks || $maxChecks) {
//client is no good
$entries['checksNoGood'][] = $entry;
} else {
$entries['chequesEnCartera'][] = $entry;
}
}
return $entries;
}
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;
}
}
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');
}
}