$group_bde_ratio = WEBPAGE::$dbh->getAll(sprintf("SELECT u.id,concat(u.first,' ',u.last) as asesor,(sum(cp.group_b)/ sum(cp.group_bg)) as group_bde_ratio,cp.date\r\nFROM tblClientPortfolio cp,\r\n tblUsers u\r\nWHERE u.id = cp.advisor_id\r\n AND cp.date ='%s'\r\n GROUP BY u.id;", $end_date));
$_html = "<h1>Ratio Grupo/BDE [{$start_date} --- {$end_date}]</h1>";
$_html .= count($group_bde_ratio) ? WEBPAGE::printchart($group_bde_ratio, array_keys($group_bde_ratio[0])) : $_LABELS['noData'];
print $_html;
}
if ($_GET['report'] == "11") {
$client_portfolio = WEBPAGE::$dbh->getAll(sprintf("SELECT u.id,concat(u.first,' ',u.last) as asesor,sum(cp.clients) as cartera_clientes,cp.date\r\nFROM tblClientPortfolio cp,\r\n tblUsers u\r\nWHERE u.id = cp.advisor_id\r\n AND cp.date ='%s'\r\n GROUP BY u.id ORDER BY cartera_clientes DESC;", $end_date));
$_html = "<h1>Cartera Clientes [{$start_date} --- {$end_date}]</h1>";
$_html .= count($client_portfolio) ? WEBPAGE::printchart($client_portfolio, array_keys($client_portfolio[0])) : $_LABELS['noData'];
print $_html;
}
// Assessing active loans within the date range
if ($_GET['report'] == "07") {
$women_percent = WEBPAGE::$dbh->getAll(sprintf("select u.id,cp.date,u.id,concat(u.first,' ',u.last) as asesor,sum(cp.female) as female,sum(cp.clients) as clients,\r\nround((sum(cp.female)/sum(cp.clients))*100,2) as female_percent\r\n from tblClientPortfolio cp,tblUsers u where\r\n u.id = cp.advisor_id and\r\n cp.date='%s' group by u.id order by female desc;", $end_date));
$_html .= "<h1>% de Mujeres [{$start_date} --- {$end_date}]</h1>";
$_html .= count($women_percent) ? WEBPAGE::printchart($women_percent, array_keys($women_percent[0])) : $_LABELS['noData'];
print $_html;
}
// print "<pre>";
// // foreach ($loan_summary as $key=>$value) {
// // foreach ($value as $key_inner=>$value_inner) {
// // print "$key_inner => $value_inner" . "\n";
// // }
// // }
// print count($loan_summary);
// print "</pre>";
// trace(array_keys($women_percent[0]));
// $output.="<table>";
// $output.="<tr>";
//
// foreach (array_keys($women_percent[0]) as $key1=>$value1) {
// // Number of Children
$head = array('fact' => "Esperanza International - Dominican Republic - PMR", 'value' => " ");
$ldata = array();
$row = 0;
foreach ($data as $header => $parameter) {
$ldata[$row][$header . '_label'] = sprintf('<table><tr><td><h1>%s</h1></td></tr></table>', str_replace('_', ' ', $header));
$ldata[$row][$header . '_html'] = " ";
$row++;
foreach ($parameter as $label => $value) {
$ldata[$row][$label . '_label'] = str_replace('_', ' ', $label);
if ($value == "-") {
$ldata[$row][$label] = "-";
} elseif (is_float($value)) {
$ldata[$row][$label] = number_format($value, 2, '.', ',');
} else {
$ldata[$row][$label] = number_format($value, 2, '.', ',');
}
$row++;
}
}
$_html .= count($data) ? WEBPAGE::printchart($ldata, $head) : $_LABELS['noData'];
print $_html;
/*
Accounting data
================
*/
?>
<p>* Monetary values in dominican pesos (DOP) unless otherwise specified</p>
</body>
</html>
WEBPAGE::$lang = $_GET['lang'];
} else {
WEBPAGE::$lang = 'esp';
}
$_LABELS = WEBPAGE::getCacheData(sprintf(WEBPAGE::_APP_LABELS_FILE, WEBPAGE::$lang));
$_CONF = WEBPAGE::getCacheData(sprintf(WEBPAGE::_APP_CONF_FILE, WEBPAGE::$lang));
$data = WEBPAGE::$dbh->getAll(sprintf("select\r\n lmd.master_id,\r\n l.xp_cancel_date,\r\n l.xp_num_pmt,\r\n lt.payment_frequency,\r\n lcd.xp_pmt_date,\r\n lcd.loan_id,\r\n lcd.xp_pmt,\r\n lcd.cn_delay,\r\n lcd.cn_penalties,\r\n lm.program_id, prg.program,\r\n lm.zone_id, z.short_name,\r\n concat(c.first,' ',c.last) cliente,\r\n c.code govID,\r\n s.name grupo,\r\n u.username,\r\n count(p.id)+1 cuota,\r\n fa.p_pmt_receipt_flag_a pmt_receipt_flag_a,\r\n fb.p_pmt_receipt_flag_b pmt_receipt_flag_b\r\nfrom\r\n (\r\n tblLoansCurrentData lcd,\r\n tblLoansMasterDetails lmd,\r\n tblLoansMaster lm,\r\n tblLoans l,\r\n tblLoanTypes lt,\r\n tblClients c,\r\n tblPrograms prg,\r\n tblZones z,\r\n tblUsers u\r\n )\r\nleft join\r\n tblSocieties s on s.id = c.society_id\r\nleft join\r\n tblPayments p on p.loan_id = lcd.loan_id and p.special = 0\r\nleft join\r\n (select fap.loan_id p_loan_id, count(far.flag_a) p_pmt_receipt_flag_a FROM tblPayments fap, tblReceipts far, tblLinkReceiptsPayments falrp, tblLoansCurrentData falcd where falcd.loan_id = fap.loan_id and falcd.xp_pmt_date <= date_add(now(), interval 7 day) and far.flag_a = '1' and falrp.payment_id = fap.id and falrp.receipt_id = far.id group by fap.loan_id) fa on fa.p_loan_id = lcd.loan_id\r\nleft join\r\n (select fbp.loan_id p_loan_id, count(fbr.flag_b) p_pmt_receipt_flag_b FROM tblPayments fbp, tblReceipts fbr, tblLinkReceiptsPayments fblrp, tblLoansCurrentData fblcd where fblcd.loan_id = fbp.loan_id and fblcd.xp_pmt_date <= date_add(now(), interval 7 day) and fbr.flag_b = '1' and fblrp.payment_id = fbp.id and fblrp.receipt_id = fbr.id group by fbp.loan_id) fb on fb.p_loan_id = lcd.loan_id\r\nwhere\r\n lcd.xp_pmt_date <= date_add(now(), interval 1 month)\r\n and lmd.loan_id = lcd.loan_id\r\n and lm.id = lmd.master_id\r\n and l.id = lcd.loan_id\r\n and lt.id = l.loan_type_id\r\n and c.id = l.client_id\r\n and prg.id = lm.program_id\r\n and z.id = lm.zone_id\r\n and u.id = l.advisor_id\r\ngroup by\r\n lcd.loan_id\r\norder by\r\n lcd.loan_id"));
$head = array('id' => 'ID', 'client' => 'Cliente', 'num_pmt' => 'No cuota', 'xp_num_pmt' => 'Total de cuotas', 'payment_frequency' => 'Frec. Pagos', 'xp_pmt_date' => 'Fecha pago', 'xp_cancel_date' => 'Fecha vencimiento', 'xp_pmt' => 'Monto cuota', 'cn_penalties' => 'Punitorios', 'pmt_receipt_flag_a' => $_CONF['pmt_receipt_flag_a'] ? $_CONF['pmt_receipt_flag_a'] : '-', 'pmt_receipt_flag_b' => $_CONF['pmt_receipt_flag_b'] ? $_CONF['pmt_receipt_flag_b'] : '-', 'username' => 'Asesor', 'program' => 'Programa', 'branch' => 'Sucursal', 'print' => 'Imprimir');
foreach ($data as $key => $val) {
$master[$val['master_id']]['id'] = $val['master_id'];
$master[$val['master_id']]['client'] = $counter[$val['master_id']] ? $val['grupo'] : $val['cliente'] . ' : ' . $val['govID'];
// $master[$val['master_id']]['client'] = $val['grupo'] ? $val['grupo'] : $val['cliente'];
$master[$val['master_id']]['num_pmt'] = $val['cuota'];
$master[$val['master_id']]['xp_num_pmt'] = $val['xp_num_pmt'];
$master[$val['master_id']]['payment_frequency'] = $val['payment_frequency'];
$master[$val['master_id']]['xp_pmt_date'] = $val['xp_pmt_date'];
$master[$val['master_id']]['xp_cancel_date'] = $val['xp_cancel_date'];
$master[$val['master_id']]['xp_pmt'] += $val['xp_pmt'];
$master[$val['master_id']]['cn_penalties'] += $val['cn_penalties'];
$master[$val['master_id']]['pmt_receipt_flag_a'] = $_CONF['pmt_receipt_flag_a'] ? $val['pmt_receipt_flag_a'] ? $val['pmt_receipt_flag_a'] : 0 : '-';
$master[$val['master_id']]['pmt_receipt_flag_b'] = $_CONF['pmt_receipt_flag_b'] ? $val['pmt_receipt_flag_b'] ? $val['pmt_receipt_flag_b'] : 0 : '-';
$master[$val['master_id']]['username'] = $val['username'];
$master[$val['master_id']]['program'] = $val['program'];
$master[$val['master_id']]['branch'] = $val['short_name'];
$master[$val['master_id']]['print'] = sprintf('<a href="index.receipt.php?advisor=%s&client=%s&xp_pmt_date=%s&num_pmt=%s&xp_num_pmt=%s&xp_pmt=%s">Imprimir</a>', $master[$val['master_id']]['username'], $master[$val['master_id']]['client'], $master[$val['master_id']]['xp_pmt_date'], $master[$val['master_id']]['num_pmt'], $master[$val['master_id']]['xp_num_pmt'], $master[$val['master_id']]['xp_pmt']);
$counter[$val['master_id']]++;
}
$tpl = new HTML_Template_ITX('.');
$tpl->loadTemplateFile('index.compactCS.tpl');
$tpl->setVariable('chart', count($master) ? WEBPAGE::printchart($master, $head) : $_LABELS['noData']);
$tpl->show();
