updated
how display 1 more column next u2 called cumulative total
should display total number of students total payable total paid , total due based on counsellors.
consider have c1,c2,c3,c4 counsellors , u1,u2 universities c1 has 5 student in each university in case cumulative total column should show total number of students column [c1][no of students]=10, [c1][payable]=some value, [c1][paid]=some value, [c1][balence]=some value
please check following code , let me know there way write select query inside sum aggregate function or alternate solution because want wll_invoice.total_payable should group customer_id.
<?php define('db_main', 'localhost|user|passowd|database'); class my_db{ private static $databases; private $connection; public function __construct($conndetails){ if(!is_object(self::$databases[$conndetails])){ list($host, $user, $pass, $dbname) = explode('|', $conndetails); $dsn = "mysql:host=$host;dbname=$dbname"; self::$databases[$conndetails] = new pdo($dsn, $user, $pass); } $this->connection = self::$databases[$conndetails]; } public function fetchall($sql){ $args = func_get_args(); array_shift($args); $statement = $this->connection->prepare($sql); $statement->execute($args); return $statement->fetchall(pdo::fetch_obj); } } $db = new my_db(db_main); $universities = $db->fetchall('select distinct customer_university wll_customer'); $counselors = $db->fetchall('select distinct customer_counselor wll_customer'); $payments_ = $db->fetchall('select customer_counselor, customer_university, count(distinct customer_name) \'no of students\', sum(distinct wll_invoice.total_payable) payable,**//i want make total_payable should group customer_id** sum(wll_invoice.total_pay) paid, sum(wll_invoice.due) balance wll_customer left join wll_invoice on wll_invoice.customer_id = wll_customer.customer_id group customer_counselor,customer_university;'); $payments = []; foreach ($payments_ $payment) $payments[$payment->customer_counselor][$payment->customer_university] = $payment; ?> <table id="table_id" class='display table-bordered'> <thead> <tr> <td rowspan="2">sl</td> <td rowspan="2" >counselor</td> <?php foreach ($universities $key => $university){ ?> <td colspan="4" ><?=$university->customer_university ?> </td> <?php } ?> </tr> <tr> <?php foreach ( $universities $university){?> <td>no of students</td> <td>payable</td> <td>paid</td> <td>balance</td> <?php } ?> </tr> </thead> <tbody> <tr> <?php foreach ( $counselors $counselor){?> <?php foreach ( $universities $key => $university){ $payment = $payments[$counselor->customer_counselor][$university->customer_university]; ?> <?php if(!$key){?> <td></td> <td><?=$counselor->customer_counselor?></td> <?php } ?> <td><?=(int)$payment->{'no of students'}?></td> <td><?=number_format($payment->payable,0,',','')?></td> <td><?=number_format($payment->paid,0,',','')?></td> <td><?=number_format($payment->balance,0,',','')?></td> <?php } ?> </tr> <?php } ?> </tbody> </table>
i hope code looking after:
<?php define('db_main', 'localhost|user|password|database'); class my_db{ private static $databases; private $connection; public function __construct($conndetails){ if(!is_object(self::$databases[$conndetails])){ list($host, $user, $pass, $dbname) = explode('|', $conndetails); $dsn = "mysql:host=$host;dbname=$dbname"; self::$databases[$conndetails] = new pdo($dsn, $user, $pass); } $this->connection = self::$databases[$conndetails]; } public function fetchall($sql){ $args = func_get_args(); array_shift($args); $statement = $this->connection->prepare($sql); $statement->execute($args); return $statement->fetchall(pdo::fetch_obj); } } $db = new my_db(db_main); $universities = $db->fetchall('select distinct customer_university wll_customer order customer_university'); /** * adding cummulative university */ $cumulativeuniversity = new stdclass(); $cumulativeuniversity->customer_university = "cumulative total"; $universities[] = $cumulativeuniversity; $counselors = $db->fetchall('select distinct customer_counselor wll_customer order customer_counselor'); $payments_ = $db->fetchall('(select customer_counselor, customer_university, count(distinct wll_invoice.customer_id) \'no of students\', sum(wll_invoice.total_payable) payable, sum(wll_invoice.total_pay) paid, sum(wll_invoice.due) balance wll_customer left join wll_invoice on wll_invoice.customer_id = wll_customer.customer_id group customer_counselor, customer_university order `customer_counselor`, `customer_name`) union (select customer_counselor, "cumulative total" university, count(distinct wll_invoice.customer_id) \'no of students\', sum(wll_invoice.total_payable) payable, sum(wll_invoice.total_pay) paid, sum(wll_invoice.due) balance wll_customer left join wll_invoice on wll_invoice.customer_id = wll_customer.customer_id group customer_counselor order `customer_counselor`)'); $payments = []; foreach ($payments_ $payment) $payments[$payment->customer_counselor][$payment->customer_university] = $payment; ?> <table id="table_id" class='display table-bordered' border="1"> <thead> <tr> <td rowspan="2" >counselor</td> <?php foreach ($universities $key => $university): ?> <td colspan="4" ><?=$university->customer_university ?> </td> <?php endforeach ?> </tr> <tr> <?php foreach ( $universities $university): ?> <td>no of students</td> <td>payable</td> <td>paid</td> <td>balance</td> <?php endforeach ?> </tr> <?php foreach ( $counselors $counselor):?> <tr> <td> <?php echo $counselor->customer_counselor;?> </td> <?php foreach ( $universities $key => $university): $payment = isset($payments[$counselor->customer_counselor][$university->customer_university]) ? $payments[$counselor->customer_counselor][$university->customer_university] : null; if($payment):?> <td><?=(int)$payment->{'no of students'}?></td> <td><?=number_format($payment->payable,0,',','')?></td> <td><?=number_format($payment->paid,0,',','')?></td> <td><?=number_format($payment->balance,0,',','')?></td> <?php else:?> <td colspan="4"></td> <?php endif?> <?php endforeach; ?> </tr> <?php endforeach; ?> </thead> </table> i have used following query using union append overall data counsellor looking for. if have noticed in code, there have appended cumulative university object university list process same loop.
(select customer_counselor, customer_university, count(distinct wll_invoice.customer_id) 'no of students', sum(wll_invoice.total_payable) payable, sum(wll_invoice.total_pay) paid, sum(wll_invoice.due) balance wll_customer left join wll_invoice on wll_invoice.customer_id = wll_customer.customer_id group customer_counselor, customer_university order `customer_counselor`, `customer_name`) union (select customer_counselor, "cumulative total" university, count(distinct wll_invoice.customer_id) 'no of students', sum(wll_invoice.total_payable) payable, sum(wll_invoice.total_pay) paid, sum(wll_invoice.due) balance wll_customer left join wll_invoice on wll_invoice.customer_id = wll_customer.customer_id group customer_counselor order `customer_counselor`) try using query distinct value, need update schema. temporary solution:
(select customer_counselor, customer_university, count(distinct wll_invoice.customer_id) 'no of students', sum(wll_invoice.total_payable) payable, sum(final_pay) paid, sum(wll_invoice.total_payable - final_pay) balance wll_customer left join (select max(id) max_id, customer_id, sum(total_pay) final_pay `wll_invoice` group customer_id, `total_payable`) wll_unique on wll_unique.customer_id = wll_customer.`customer_id` left join wll_invoice on wll_invoice.customer_id = wll_unique.customer_id , `wll_invoice`.id = wll_unique.max_id group customer_counselor, customer_university order `customer_counselor`, `customer_name`) union (select customer_counselor, "cumulative total" university, count(distinct wll_invoice.customer_id) 'no of students', sum(wll_invoice.total_payable) payable, sum(final_pay) paid, sum(wll_invoice.total_payable - final_pay) balance wll_customer left join (select max(id) max_id, customer_id, sum(total_pay) final_pay `wll_invoice` group customer_id, `total_payable`) wll_unique on wll_unique.customer_id = wll_customer.`customer_id` left join wll_invoice on wll_invoice.customer_id = wll_unique.customer_id , `wll_invoice`.id = wll_unique.max_id group customer_counselor order `customer_counselor`) 
Comments
Post a Comment