Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- //Error handler for handling exceptions.
- error_reporting(0);
- function __construct()
- {
- }
- $stocktake_id = $Navigation->getParam('stocktake_id');
- $filename = 'Reports'.$stocktake_id.'\\' ;
- function download_file_to_client($file, $mime = 'application/zip')
- {
- $url = "".$file;
- // header('Content-Type: '.$mime.';charset=utf-8');
- header($http[$num]);
- header ('Location: '.$url.'');
- flush();
- }
- if (file_exists($filename)) {
- } else {
- mkdir($filename, 0777, true);
- }
- $x = 0;
- if ($x==0){
- //Department Report
- class Report1{
- function departmentReportContent($stocktake_id, $sections){
- //get all the departments for the given stocktake
- $dep_qry = "SELECT TRIM(UPPER(IFNULL(department_code,''))) AS department_code, TRIM(UPPER(department_description)) AS department_description, SUM(total_cost) AS total_cost, SUM(total_retail) AS total_retail, SUM(qty) AS qty FROM (
- SELECT
- IFNULL(sp.department_code,'') AS department_code, sp.department_description AS department_description, SUM(sp.unit_cost_price * ss.quantity) AS total_cost, SUM(sp.unit_retail_price * ss.quantity) AS total_retail, sum(ss.quantity) AS qty
- FROM stocktake_scans ss
- INNER JOIN stocktake_products sp ON ss.stocktake_product_id = sp.stocktake_product_id ";
- if ($sections)
- {
- $dep_qry.= " INNER JOIN stocktake_areas sa ON ss.stocktake_area_id = sa.stocktake_area_id ";
- }
- $dep_qry .= " WHERE ss.stocktake_id = '{$stocktake_id}' ";
- if ($sections)
- {
- $dep_qry.= " AND sa.section IN ({$sections}) ";
- }
- $dep_qry .= " GROUP BY TRIM(UPPER(IFNULL(sp.department_code,''))), TRIM(UPPER(sp.department_description))
- UNION ALL
- SELECT '' AS department_code, TRIM(UPPER(department_description)) AS department_description, SUM(unit_cost_price * quantity) AS total_cost, SUM(unit_retail_price * quantity) AS total_retail, sum(quantity) AS qty
- FROM stocktake_items
- WHERE stocktake_id = '{$stocktake_id}' ";
- if ($sections)
- {
- $dep_qry.= " AND section IN ({$sections}) ";
- }
- $dep_qry .= " GROUP BY TRIM(UPPER(department_description))) a
- GROUP BY TRIM(UPPER(IFNULL(department_code,0))), TRIM(UPPER(department_description))";
- //echo $dep_qry;
- $dep_res = db::c()->query($dep_qry);
- $cur_symbol = Stocktake::getCurrencySymbol($stocktake_id);
- //if (strcmp($cur_res, 'EUR') == 0) setlocale(LC_ALL, 'de_DE@euro', 'de_DE', 'de', 'ge');
- //$rep_table = self::reportHeader("Department Stock Take Report");
- //$rep_table .= self::stocktakeDetails($stocktake_id);
- $rep_table .= "
- <table id='department_report' cellspacing='0' style='margin-top:10px;'>
- <tr>
- <td colspan='2' class='top'><div class='nosplit'></div></td>
- <td colspan='2' class='top'><div class='nosplit'>Stock Value At</div></td>
- </tr>
- <tr>
- <td class='top' style='width:55%;'><div class='nosplit'>Departments</div></td>
- <td class='top' style='width:15%;'><div class='nosplit'>Total Count</div></td>
- <td class='top' style='width:15%;'><div class='nosplit'>Cost</div></td>
- <td class='top' style='width:15%;'><div class='nosplit'>Retail</div></td>
- </tr>";
- $sum_total_cost = 0;
- $sum_total_retail = 0;
- $sum_total_qty = 0;
- while ($row = $dep_res->fetch(PDO::FETCH_ASSOC))
- {
- $total_cost = $row['total_cost'];//$row['unit_cost_price']*$row['qty'];
- $total_retail = $row['total_retail'];//*$row['qty'];
- $sum_total_cost += $total_cost;
- $sum_total_retail += $total_retail;
- $sum_total_qty += $row['qty'];
- $tc = number_format($total_cost, 2);
- $tr = number_format($total_retail, 2);
- $rep_table .= "
- <tr>
- <td><div class='nosplit'>". $row['department_description'];
- if($row['department_code']) $rep_table.= ' (' .$row['department_code']. ')';
- $rounded_qty = (floor($row['qty']) != $row['qty']) ? $row['qty'] : round($row['qty']);
- $rep_table .= "</div></td>
- <td style='text-align:right;'><div class='nosplit'>".$rounded_qty."</div></td>
- <td style='text-align:right;'><div class='nosplit'>".$cur_symbol.$tc."</div></td>
- <td style='text-align:right;'><div class='nosplit'>".$cur_symbol.$tr."</div></td>
- </tr>";
- }
- $stc = number_format($sum_total_cost, 2);
- $str = number_format($sum_total_retail, 2);
- $rep_table .= "<tr>
- <td class='bottom'><div class='nosplit'>Total:</div></td>
- <td class='bottom'><div class='nosplit'>".$sum_total_qty."</div></td>
- <td class='bottom'><div class='nosplit'>".$cur_symbol.$stc."</div></td>
- <td class='bottom'><div class='nosplit'>".$cur_symbol.$str."</div></td>
- </tr>
- </table>";
- $name = "Department Report";
- return $rep_table;
- }
- function reportHeader($title){
- $header = "<table class='report_header' cellspacing='0' cellpadding='0'>
- <tr >
- <td style='width:50%; text-align:left;'>
- <strong>Stocktaking.ie</strong><br/>
- Lakeview Point<br/>
- Claregalway Corporate Park<br/>
- Claregalway, Galway
- </td>
- <td style='text-align:right;'>
- <img src='/images/stocktaking_s.gif' alt='Stocktaking' height='69' width='211'/>
- </td>
- </tr>
- <tr>
- <td colspan='2'><h2 style='color:black;font-size:20px;font-weight:bold;margin-top:10px;'>".$title."</h2></td>
- </tr>
- </table>";
- return $header;
- }
- function stocktakeDetails($stocktake_id){
- $stocktake_qry = "
- SELECT
- s.stocktake_date, s.customer_name, s.store_name, s.store_address, s.store_post_code, s.store_town, s.store_county_name,
- c.country_name, s.date_created
- FROM stocktakes s LEFT JOIN country c
- ON c.country_code_alpha2 = s.customer_country_code
- WHERE stocktake_id = '{$stocktake_id}';";
- $stocktake_res = db::c()->query($stocktake_qry);
- $stocktake_row = $stocktake_res->fetch(PDO::FETCH_ASSOC);
- $date_created = $stocktake_row['date_created'];
- $stocktake_date = l::date($stocktake_row['stocktake_date']);
- $stocktake_details = "<table class='st_det_rep'>
- <tr>
- <td style='width:40%; text-align:left;'>
- <table style='font-size:12px;float:left;'>
- <tr>
- <td style='text-align:left;'>Customer:</td>
- <td style='text-align:left;'><strong>".$stocktake_row['customer_name']."</strong></td>
- </tr>
- <tr>
- <td style='width:120px; text-align:left;'>Stocktake Number:</td>
- <td style='text-align:left;'>".$stocktake_id."</td>
- </tr>
- <tr>
- <td style='text-align:left;'>Stocktake Date:</td>
- <td style='text-align:left;'>".$stocktake_date."</td>
- </tr>
- </table>
- </td>
- <td style='width:60%; text-align:left;'>
- <table style='font-size:12px;float:right;'>
- <tr>
- <td style='width:120px;text-align:right;font-weight:bold;padding-right:20px;'>Store:</td>
- <td style='text-align:right;font-weight:bold;'>".$stocktake_row['store_name']."</td>
- </tr>
- <tr>
- <td style='text-align:left;'></td>
- <td style='text-align:right;'>".$stocktake_row['store_address']."<br/>
- ".$stocktake_row['store_town'].", ".$stocktake_row['store_post_code']."<br/>
- ".$stocktake_row['store_county_name'].", ".$stocktake_row['country_name']."
- </td>
- </tr>
- </table>
- </td>
- </tr>
- </table>";
- return $stocktake_details;
- }
- }
- $report = $Navigation->getParam('report');
- $stocktake_id = $Navigation->getParam('stocktake_id');
- $section[] = $_POST['section'];
- $part = $Navigation->getParam('part');
- $html_begin = '<html><head>
- <link rel="stylesheet" type="text/css" href="http://sttest.aptvision.com/styles/main_style.css" />
- <script>
- function subst() {
- var vars={};
- var x=document.location.search.substring(1).split("&");
- for(var i in x) {var z=x[i].split("=",2);vars[z[0]] = unescape(z[1]);}
- var x=["topage","page"];
- for(var i in x) {
- var y = document.getElementsByClassName(x[i]);
- for(var j=0; j<y.length; ++j) y[j].textContent = vars[x[i]];
- }
- }
- </script>
- </head><body onload="subst()">';
- $html_end = '</body></html>';
- if (!$part)
- {
- //update stocktake
- $res = db::c()->query("SELECT stocktake_product_id FROM stocktake_scans WHERE stocktake_id = 304");
- while ($row = $res->fetch())
- {
- $qty = db::c()->query("SELECT SUM(quantity) FROM stocktake_scans WHERE stocktake_product_id = '{$row['stocktake_product_id']}';")->fetchColumn(0);
- if (!$qty)
- $qty = '0';
- db::c()->query("UPDATE stocktake_products SET quantity_scanned = '{$qty}' WHERE stocktake_product_id = '{$row['stocktake_product_id']}';");
- }
- $params = array();
- $params['security_off'] = true;
- $params['stocktake_id'] = $stocktake_id;
- $params['report'] = $report;
- $params['section'] = $Page->getPOSTVar('section');
- $params['sdep'] = $Page->getPOSTVar('sel_rep_department');
- $params['start'] = $Page->getPOSTVar('start_code1');
- $params['finish'] = $Page->getPOSTVar('finish_code1');
- if ($Page->getGETVar('a_ids'))
- {
- $params['a_ids'] = $Page->getGETVar('a_ids');
- }
- unset($content_url);
- $params['part'] = 'content1';
- $content_url = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'header1';
- $header_url = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'footer1';
- $footer_url = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $wkpdf = new WKPDF($content_url);
- $wkpdf->footerUrl = $footer_url;
- $wkpdf->headerUrl = $header_url;
- $wkpdf->marginTop = '35';
- $wkpdf->marginBottom = '20';
- $wkpdf->output('S', 'Reports'.$stocktake_id .'\DepartmentSummaryReport_' . $stocktake_id .'.pdf');
- //echo "<a href='{$content_url}' target='_blank'>Content</a><br>";
- //echo "<a href='{$header_url}' target='_blank'>Header</a><br>";
- //echo "<a href='{$footer_url}' target='_blank'>Footer</a><br>";
- }
- else if ($part == 'content1'){
- $all_sections = $Page->getPOSTVar('all_sections');
- if ($all_sections) {
- $section = null;
- }
- else {
- $secarr = $Navigation->getParam('section');
- $section = '';
- if ($secarr && is_array($secarr) && count($secarr) > 0)
- {
- foreach($secarr as $s)
- {
- $section.= db::c()->quote($s) . ',';
- }
- $section = substr($section, 0, -1);
- }
- else
- {
- $section = null;
- }
- $sdep = $Navigation->getParam('sdep');
- $start = $Navigation->getParam('start');
- $finish = $Navigation->getParam('finish');
- if (!$sdep || $sdep == '00all')
- {
- $sdep = null;
- }
- echo $html_begin;
- echo Report1::stocktakeDetails($stocktake_id);
- echo Report1::departmentReportContent($stocktake_id, $sections);
- echo $html_end;
- }}
- else if ($part == 'header1')
- {
- echo $html_begin;
- echo Report1::reportHeader('Department Report');
- echo $html_end;
- }
- else if ($part == 'footer1')
- {
- echo $html_begin;
- echo "<div class='page_numbers'> – Page <span class='page'></span> of <span class='topage'></span> – </div>";
- echo $html_end;
- }
- //Sub Department Report
- class Report2{
- function subDepartmentReportContent($stocktake_id, $sections)
- {
- //get currency code for the stocktake
- $cur_symbol = Stocktake::getCurrencySymbol($stocktake_id);
- // $report = Report2::reportHeader("Sub-Department Stock Take Report");
- // $report .= Report2::stocktakeDetails($stocktake_id);
- $report = " <table id='sub_department_report' cellspacing='0'>
- <tr>
- <td colspan='3' class='top'><div class='nosplit'></div></td>
- <td colspan='2' class='top'><div class='nosplit'>Stock Value At</div></td>
- </tr>
- <tr>
- <td class='top' style='width:30%;'><div class='nosplit'>Departments</div></td>
- <td class='top' style='width:30%;'><div class='nosplit'>Sub Departments</div></td>
- <td class='top' style='width:10%;'><div class='nosplit'>Total Count</div></td>
- <td class='top' style='width:15%;'><div class='nosplit'>Cost</div></td>
- <td class='top' style='width:15%;'><div class='nosplit'>Retail</div></td>
- </tr>";
- $dep_qry = "
- SELECT
- (CASE WHEN TRIM(IFNULL(sp.department_description, '')) = '' THEN 'N/A' ELSE sp.department_description END) AS department_description,
- (CASE WHEN TRIM(IFNULL(sp.sub_department_description, '')) = '' THEN 'N/A' ELSE sp.sub_department_description END) AS sub_department_description,
- SUM(sp.unit_cost_price * ss.quantity) AS total_cost, SUM(sp.unit_retail_price * ss.quantity) AS total_retail, sum(ss.quantity) AS qty
- FROM stocktake_scans ss
- INNER JOIN stocktake_products sp ON ss.stocktake_product_id = sp.stocktake_product_id ";
- if ($sections)
- {
- $dep_qry.= " INNER JOIN stocktake_areas sa ON ss.stocktake_area_id = sa.stocktake_area_id ";
- }
- $dep_qry.= " WHERE ss.stocktake_id = '{$stocktake_id}' ";
- if ($sections)
- {
- $dep_qry.= " AND sa.section IN ({$sections}) ";
- }
- $dep_qry.= " GROUP BY department_description, sub_department_description
- UNION ALL
- SELECT
- (CASE WHEN TRIM(IFNULL(department_description, '')) = '' THEN 'N/A' ELSE department_description END) AS department_description,
- 'N/A',
- SUM(unit_cost_price * quantity) AS total_cost, SUM(unit_retail_price * quantity) AS total_retail, sum(quantity) AS qty
- FROM stocktake_items
- WHERE stocktake_id = '{$stocktake_id}' ";
- if ($sections)
- {
- $dep_qry.= " AND section IN ({$sections}) ";
- }
- $dep_qry.= " GROUP BY department_description;";
- $dep_res = db::c()->query($dep_qry);
- $prev_dep_code = null;
- $prev_dep_desc = null;
- while ($row = $dep_res->fetch(PDO::FETCH_ASSOC))
- {
- if ($prev_dep_desc !== $row['department_description'])
- {
- if ($prev_dep_desc !== null)
- {
- $stc = number_format($sum_total_cost, 2);
- $str = number_format($sum_total_retail, 2);
- $rounded_tq = (floor($sum_total_qty) != $sum_total_qty) ? $sum_total_qty : round($sum_total_qty);
- $report .= "<tr>
- <td class='bottom' colspan='2' style='text-transform:uppercase'><div class='nosplit'>(".$dep_title.")</div></td>
- <td class='bottom'><div class='nosplit'>".$rounded_tq."</div></td>
- <td class='bottom'><div class='nosplit'>".$cur_symbol.$stc."</div></td>
- <td class='bottom'><div class='nosplit'>".$cur_symbol.$str."</div></td>
- </tr>";
- }
- // get all subdepartments for the given department
- $dep_des = $row['department_description'];
- $dep_code = $row['department_code'];
- $dep_title = $dep_des;
- $report .= "<tr>
- <td colspan='5' class='title'><div class='nosplit'> ".$dep_title."</div></td>
- </tr>";
- $sum_total_cost = 0;
- $sum_total_retail = 0;
- $sum_total_qty = 0;
- }
- $total_cost = $row['total_cost'];$row['unit_cost_price']*$row['qty'];
- $total_retail = $row['total_retail'];$row['unit_retail_price']*$row['qty'];
- $sum_total_cost += $total_cost;
- $sum_total_retail += $total_retail;
- $sum_total_qty += $row['qty'];
- $tc = number_format($total_cost, 2);
- $tr = number_format($total_retail, 2);
- $rounded_qty = (floor($row['qty']) != $row['qty']) ? $row['qty'] : round($row['qty']);
- $report .= "
- <tr>
- <td style='border-right:0px;'><div class='nosplit'> </div></td>
- <td><div class='nosplit'>". $row['sub_department_description'];
- if($row['sub_department_code']) $report.= ' (' .$row['sub_department_code']. ')';
- $report .= " </div></td>
- <td style='text-align:right;'><div class='nosplit'>".$rounded_qty."</div></td>
- <td style='text-align:right;'><div class='nosplit'>".$cur_symbol.$tc."</div></td>
- <td style='text-align:right;'><div class='nosplit'>".$cur_symbol.$tr."</div></td>
- </tr>";
- $prev_dep_code = $row['department_code'];
- $prev_dep_desc = $row['department_description'];
- }
- $stc = number_format($sum_total_cost, 2);
- $str = number_format($sum_total_retail, 2);
- $rounded_tq = (floor($sum_total_qty) != $sum_total_qty) ? $sum_total_qty : round($sum_total_qty);
- $report .= "<tr>
- <td class='bottom' colspan='2' style='text-transform:uppercase'><div class='nosplit'>(".$dep_title.")</div></td>
- <td class='bottom'><div class='nosplit'>".$rounded_tq."</div></td>
- <td class='bottom'><div class='nosplit'>".$cur_symbol.$stc."</div></td>
- <td class='bottom'><div class='nosplit'>".$cur_symbol.$str."</div></td>
- </tr>";
- $report .= "</table>";
- return $report;
- }
- function reportHeader($title){
- $header = "<table class='report_header' cellspacing='0' cellpadding='0'>
- <tr >
- <td style='width:50%; text-align:left;'>
- <strong>Stocktaking.ie</strong><br/>
- Lakeview Point<br/>
- Claregalway Corporate Park<br/>
- Claregalway, Galway
- </td>
- <td style='text-align:right;'>
- <img src='/images/stocktaking_s.gif' alt='Stocktaking' height='69' width='211'/>
- </td>
- </tr>
- <tr>
- <td colspan='2'><h2 style='color:black;font-size:20px;font-weight:bold;margin-top:10px;'>".$title."</h2></td>
- </tr>
- </table>";
- return $header;
- }
- function stocktakeDetails($stocktake_id){
- $stocktake_qry = "
- SELECT
- s.stocktake_date, s.customer_name, s.store_name, s.store_address, s.store_post_code, s.store_town, s.store_county_name,
- c.country_name, s.date_created
- FROM stocktakes s LEFT JOIN country c
- ON c.country_code_alpha2 = s.customer_country_code
- WHERE stocktake_id = '{$stocktake_id}';";
- $stocktake_res = db::c()->query($stocktake_qry);
- $stocktake_row = $stocktake_res->fetch(PDO::FETCH_ASSOC);
- $date_created = $stocktake_row['date_created'];
- $stocktake_date = l::date($stocktake_row['stocktake_date']);
- $stocktake_details = "<table class='st_det_rep'>
- <tr>
- <td style='width:40%; text-align:left;'>
- <table style='font-size:12px;float:left;'>
- <tr>
- <td style='text-align:left;'>Customer:</td>
- <td style='text-align:left;'><strong>".$stocktake_row['customer_name']."</strong></td>
- </tr>
- <tr>
- <td style='width:120px; text-align:left;'>Stocktake Number:</td>
- <td style='text-align:left;'>".$stocktake_id."</td>
- </tr>
- <tr>
- <td style='text-align:left;'>Stocktake Date:</td>
- <td style='text-align:left;'>".$stocktake_date."</td>
- </tr>
- </table>
- </td>
- <td style='width:60%; text-align:left;'>
- <table style='font-size:12px;float:right;'>
- <tr>
- <td style='width:120px;text-align:right;font-weight:bold;padding-right:20px;'>Store:</td>
- <td style='text-align:right;font-weight:bold;'>".$stocktake_row['store_name']."</td>
- </tr>
- <tr>
- <td style='text-align:left;'></td>
- <td style='text-align:right;'>".$stocktake_row['store_address']."<br/>
- ".$stocktake_row['store_town'].", ".$stocktake_row['store_post_code']."<br/>
- ".$stocktake_row['store_county_name'].", ".$stocktake_row['country_name']."
- </td>
- </tr>
- </table>
- </td>
- </tr>
- </table>";
- return $stocktake_details;
- }
- }
- $report = $Navigation->getParam('report');
- $stocktake_id = $Navigation->getParam('stocktake_id');
- $section[] = $_POST['section'];
- $part = $Navigation->getParam('part');
- $html_begin = '<html><head>
- <link rel="stylesheet" type="text/css" href="http://sttest.aptvision.com/styles/main_style.css" />
- <script>
- function subst() {
- var vars={};
- var x=document.location.search.substring(1).split("&");
- for(var i in x) {var z=x[i].split("=",2);vars[z[0]] = unescape(z[1]);}
- var x=["topage","page"];
- for(var i in x) {
- var y = document.getElementsByClassName(x[i]);
- for(var j=0; j<y.length; ++j) y[j].textContent = vars[x[i]];
- }
- }
- </script>
- </head><body onload="subst()">';
- $html_end = '</body></html>';
- if (!$part)
- {
- $content_url1 = '';
- //update stocktake
- $res = db::c()->query("SELECT stocktake_product_id FROM stocktake_scans WHERE stocktake_id = 304");
- while ($row = $res->fetch())
- {
- $qty = db::c()->query("SELECT SUM(quantity) FROM stocktake_scans WHERE stocktake_product_id = '{$row['stocktake_product_id']}';")->fetchColumn(0);
- if (!$qty)
- $qty = '0';
- db::c()->query("UPDATE stocktake_products SET quantity_scanned = '{$qty}' WHERE stocktake_product_id = '{$row['stocktake_product_id']}';");
- }
- $params = array();
- $params['security_off'] = true;
- $params['stocktake_id'] = $stocktake_id;
- $params['report'] = $report;
- $params['section'] = $Page->getPOSTVar('section');
- $params['sdep'] = $Page->getPOSTVar('sel_rep_department');
- $params['start'] = $Page->getPOSTVar('start_code1');
- $params['finish'] = $Page->getPOSTVar('finish_code1');
- if ($Page->getGETVar('a_ids'))
- {
- $params['a_ids'] = $Page->getGETVar('a_ids');
- }
- unset($content_url);
- $params['part'] = 'content';
- $content_url1 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'header';
- $header_url1 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'footer';
- $footer_url1 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $wkpdf = new WKPDF($content_url1);
- $wkpdf->footerUrl = $footer_url1;
- $wkpdf->headerUrl = $header_url1;
- $wkpdf->marginTop = '35';
- $wkpdf->marginBottom = '20';
- $wkpdf->output('S', 'Reports'.$stocktake_id .'\SubDepartmentSummaryReport__' . $stocktake_id .'.pdf');
- }
- else if ($part == 'content')
- {
- $all_sections = $Page->getPOSTVar('all_sections');
- if ($all_sections) {
- $section = null;
- }
- else {
- $secarr = $Navigation->getParam('section');
- $section = '';
- if ($secarr && is_array($secarr) && count($secarr) > 0)
- {
- foreach($secarr as $s)
- {
- $section.= db::c()->quote($s) . ',';
- }
- $section = substr($section, 0, -1);
- }
- else
- {
- $section = null;
- }
- }
- $sdep = $Navigation->getParam('sdep');
- $start = $Navigation->getParam('start');
- $finish = $Navigation->getParam('finish');
- if (!$sdep || $sdep == '00all')
- {
- $sdep = null;
- }
- echo $html_begin;
- echo Report2::stocktakeDetails($stocktake_id);
- echo Report2::subDepartmentReportContent($stocktake_id, $sections);
- echo $html_end;
- }
- else if ($part == 'header')
- {
- echo $html_begin;
- echo Report2::reportHeader('Sub Department Report');
- echo $html_end;
- }
- else if ($part == 'footer')
- {
- echo $html_begin;
- echo "<div class='page_numbers'> – Page <span class='page'></span> of <span class='topage'></span> – </div>";
- echo $html_end;
- }
- //Stock Detail Report
- class Report3{
- function stockDetailReportContent($stocktake_id, $sections, $sdep){
- if ($sdep) $sdep = db::c()->quote($sdep);
- //get all the departments for the given stocktake
- $dep_qry = "SELECT a.department_description, a.product_name, a.product_description, a.barcode,
- SUM(a.quantity) AS quantity, SUM(a.cost) AS cost, SUM(a.retail) AS retail, a.unit_cost_price_estimated, a.unit_retail_price_estimated
- FROM
- (
- SELECT
- sp.department_description AS department_description, sp.product_name, sp.product_description AS product_description,
- ss.barcode AS barcode, sum(ss.quantity) AS quantity, sp.unit_cost_price*sum(ss.quantity) AS cost, sp.unit_retail_price*sum(ss.quantity) AS retail,
- sp.unit_cost_price_estimated AS unit_cost_price_estimated, sp.unit_retail_price_estimated AS unit_retail_price_estimated
- FROM stocktake_scans ss
- INNER JOIN stocktake_products sp ON ss.stocktake_product_id = sp.stocktake_product_id ";
- if ($sections)
- {
- $dep_qry.= " INNER JOIN stocktake_areas sa ON ss.stocktake_area_id = sa.stocktake_area_id ";
- }
- $dep_qry.= " WHERE ss.stocktake_id = '{$stocktake_id}' ";
- if ($sdep)
- {
- $dep_qry.= " AND STRCMP(sp.department_description, {$sdep}) = 0 ";
- }
- if ($sections)
- {
- $dep_qry.= " AND sa.section IN ({$sections}) ";
- }
- $dep_qry.= " GROUP BY sp.department_description, sp.product_description, sp.product_name, ss.barcode
- UNION ALL
- SELECT
- si.department_description AS department_description, si.product_name AS product_name, si.product_name AS product_description,
- 'N/A' AS barcode, si.quantity AS quantity, si.unit_cost_price*si.quantity AS cost, si.unit_retail_price*si.quantity AS retail,
- 'N/A' AS unit_cost_price_estimated, 'N/A' AS unit_retail_price_estimated
- FROM stocktake_items si
- WHERE si.stocktake_id = '{$stocktake_id}' ";
- if ($sdep)
- {
- $dep_qry.= " AND STRCMP(si.department_description, {$sdep}) = 0 ";
- }
- if ($sections)
- {
- $dep_qry.= " AND section IN ({$sections}) ";
- }
- $dep_qry.= "
- GROUP BY si.department_description, si.product_name
- ) a
- GROUP BY a.department_description, a.product_name, a.product_description, a.barcode
- ORDER BY a.department_description ASC, a.product_name ASC, a.product_description ASC;";
- $dep_res = db::c()->query($dep_qry);
- //get currency code for the stocktake
- $cur_symbol = Stocktake::getCurrencySymbol($stocktake_id);
- //if (strcmp($cur_res, 'EUR') == 0) setlocale(LC_ALL, 'de_DE@euro', 'de_DE', 'de', 'ge');
- //$report = self::reportHeader("Stock Detail Report");
- //$report .= self::stocktakeDetails($stocktake_id);
- $report .= "
- <table id='stock_detail_report' cellspacing='0'>
- <tr>
- <td class='top' style='text-align:left;width:50%;'><div class='nosplit'>Description</div></td>
- <td class='top' style='text-align:left;width:20%;'><div class='nosplit'>Barcode</div></td>
- <td class='top' style='text-align:center;width:10%;'><div class='nosplit'>Quantity</div></td>
- <td class='top' style='text-align:right;width:10%;'><div class='nosplit'>Cost</div></td>
- <td class='top' style='text-align:right;width:10%;'><div class='nosplit'>Retail</div></td>
- </tr>";
- $curdep = null;
- $gtq = 0;
- $gtc = 0;
- $gtr = 0;
- while ($row = $dep_res->fetch(PDO::FETCH_ASSOC))
- {
- $dep = $row['department_description'];
- if (!$dep) $dep = 'N/A';
- else $fix = '';
- if ($curdep === null)
- {
- $report .= "<tr>
- <td colspan='5' class='title'> ".$fix.$dep."</td>
- </tr>";
- }
- if ($curdep !== null && $curdep != $dep)
- {
- $stc = number_format($sum_total_cost, 2);
- $str = number_format($sum_total_retail, 2);
- $rounded_tq = (floor($sum_total_qty) != $sum_total_qty) ? $sum_total_qty : round($sum_total_qty);
- $report .= "
- <tr>
- <td class='bottom' colspan='2' style='text-align:right;'><div class='nosplit'>Total:</div></td>
- <td class='bottom' style='text-align:center;'><div class='nosplit'>".$rounded_tq."</div></td>
- <td class='bottom' style='text-align:right;'><div class='nosplit'>".$cur_symbol.$stc."</div></td>
- <td class='bottom' style='text-align:right;'><div class='nosplit'>".$cur_symbol.$str."</div></td>
- </tr>";
- $gtq += $sum_total_qty;
- $gtc += $sum_total_cost;
- $gtr += $sum_total_retail;
- $sum_total_cost = 0;
- $sum_total_retail = 0;
- $sum_total_qty = 0;
- $report .= "
- <tr>
- <td colspan='5' class='title'> ".$fix.$dep."</td>
- </tr>";
- }
- $total_cost = $row['cost'];
- $total_retail = $row['retail'];
- $sum_total_cost += $total_cost;
- $sum_total_retail += $total_retail;
- $sum_total_qty += $row['quantity'];
- $tc = number_format($total_cost, 2);
- $tr = number_format($total_retail, 2);
- $cost_estimated = $row['unit_cost_price_estimated'] ? ' (e)' : '';
- $retail_estimated = $row['unit_retail_price_estimated'] ? ' (e)' : '';
- $rounded_qty = (floor($row['quantity']) != $row['quantity']) ? $row['quantity'] : round($row['quantity']);
- $report .= "
- <tr>
- <td><div class='nosplit'>". ($row['product_name'] ? $row['product_name'] : $row['product_description'])."</div></td>
- <td><div class='nosplit'>". $row['barcode']."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".$rounded_qty."</div></td>
- <td style='text-align:right;'><div class='nosplit'>".$cur_symbol.$tc.$cost_estimated."</div></td>
- <td style='text-align:right;'><div class='nosplit'>".$cur_symbol.$tr.$retail_estimated."</div></td>
- </tr>";
- $curdep = $dep;
- }
- $stc = number_format($sum_total_cost, 2);
- $str = number_format($sum_total_retail, 2);
- $rounded_tq = (floor($sum_total_qty) != $sum_total_qty) ? $sum_total_qty : round($sum_total_qty);
- $report .= "
- <tr>
- <td class='bottom' colspan='2'><div class='nosplit'>Total:</div></td>
- <td class='bottom' style='text-align:center;'><div class='nosplit'>".$rounded_tq."</div></td>
- <td class='bottom'><div class='nosplit'>".$cur_symbol.$stc."</div></td>
- <td class='bottom'><div class='nosplit'>".$cur_symbol.$str."</div></td>
- </tr>";
- $report .= "
- <tr>
- <td class='bottom' colspan='5'><div class='nosplit'></div></td>
- </tr>";
- $gtq += $sum_total_qty;
- $gtc += $sum_total_cost;
- $gtr += $sum_total_retail;
- $gtc = number_format($gtc, 2);
- $gtr = number_format($gtr, 2);
- $report .= "
- <tr>
- <td class='bottom' colspan='2'><div class='nosplit'>Grand Total:</div></td>
- <td class='bottom' style='text-align:center;'><div class='nosplit'>".$gtq."</div></td>
- <td class='bottom'><div class='nosplit'>".$cur_symbol.$gtc."</div></td>
- <td class='bottom'><div class='nosplit'>".$cur_symbol.$gtr."</div></td>
- </tr>";
- $report .= "</table>";
- return $report;
- }
- function reportHeader($title){
- $header = "<table class='report_header' cellspacing='0' cellpadding='0'>
- <tr >
- <td style='width:50%; text-align:left;'>
- <strong>Stocktaking.ie</strong><br/>
- Lakeview Point<br/>
- Claregalway Corporate Park<br/>
- Claregalway, Galway
- </td>
- <td style='text-align:right;'>
- <img src='/images/stocktaking_s.gif' alt='Stocktaking' height='69' width='211'/>
- </td>
- </tr>
- <tr>
- <td colspan='2'><h2 style='color:black;font-size:20px;font-weight:bold;margin-top:10px;'>".$title."</h2></td>
- </tr>
- </table>";
- return $header;
- }
- function stocktakeDetails($stocktake_id){
- $stocktake_qry = "
- SELECT
- s.stocktake_date, s.customer_name, s.store_name, s.store_address, s.store_post_code, s.store_town, s.store_county_name,
- c.country_name, s.date_created
- FROM stocktakes s LEFT JOIN country c
- ON c.country_code_alpha2 = s.customer_country_code
- WHERE stocktake_id = '{$stocktake_id}';";
- $stocktake_res = db::c()->query($stocktake_qry);
- $stocktake_row = $stocktake_res->fetch(PDO::FETCH_ASSOC);
- $date_created = $stocktake_row['date_created'];
- $stocktake_date = l::date($stocktake_row['stocktake_date']);
- $stocktake_details = "<table class='st_det_rep'>
- <tr>
- <td style='width:40%; text-align:left;'>
- <table style='font-size:12px;float:left;'>
- <tr>
- <td style='text-align:left;'>Customer:</td>
- <td style='text-align:left;'><strong>".$stocktake_row['customer_name']."</strong></td>
- </tr>
- <tr>
- <td style='width:120px; text-align:left;'>Stocktake Number:</td>
- <td style='text-align:left;'>".$stocktake_id."</td>
- </tr>
- <tr>
- <td style='text-align:left;'>Stocktake Date:</td>
- <td style='text-align:left;'>".$stocktake_date."</td>
- </tr>
- </table>
- </td>
- <td style='width:60%; text-align:left;'>
- <table style='font-size:12px;float:right;'>
- <tr>
- <td style='width:120px;text-align:right;font-weight:bold;padding-right:20px;'>Store:</td>
- <td style='text-align:right;font-weight:bold;'>".$stocktake_row['store_name']."</td>
- </tr>
- <tr>
- <td style='text-align:left;'></td>
- <td style='text-align:right;'>".$stocktake_row['store_address']."<br/>
- ".$stocktake_row['store_town'].", ".$stocktake_row['store_post_code']."<br/>
- ".$stocktake_row['store_county_name'].", ".$stocktake_row['country_name']."
- </td>
- </tr>
- </table>
- </td>
- </tr>
- </table>";
- return $stocktake_details;
- }
- }
- $report = $Navigation->getParam('report');
- $stocktake_id = $Navigation->getParam('stocktake_id');
- $section[] = $_POST['section'];
- $part = $Navigation->getParam('part');
- $html_begin = '<html><head>
- <link rel="stylesheet" type="text/css" href="http://sttest.aptvision.com/styles/main_style.css" />
- <script>
- function subst() {
- var vars={};
- var x=document.location.search.substring(1).split("&");
- for(var i in x) {var z=x[i].split("=",2);vars[z[0]] = unescape(z[1]);}
- var x=["topage","page"];
- for(var i in x) {
- var y = document.getElementsByClassName(x[i]);
- for(var j=0; j<y.length; ++j) y[j].textContent = vars[x[i]];
- }
- }
- </script>
- </head><body onload="subst()">';
- $html_end = '</body></html>';
- if (!$part)
- {
- $content_url1 = '';
- //update stocktake
- $res = db::c()->query("SELECT stocktake_product_id FROM stocktake_scans WHERE stocktake_id = 304");
- while ($row = $res->fetch())
- {
- $qty = db::c()->query("SELECT SUM(quantity) FROM stocktake_scans WHERE stocktake_product_id = '{$row['stocktake_product_id']}';")->fetchColumn(0);
- if (!$qty)
- $qty = '0';
- db::c()->query("UPDATE stocktake_products SET quantity_scanned = '{$qty}' WHERE stocktake_product_id = '{$row['stocktake_product_id']}';");
- }
- $params = array();
- $params['security_off'] = true;
- $params['stocktake_id'] = $stocktake_id;
- $params['report'] = $report;
- $params['section'] = $Page->getPOSTVar('section');
- $params['sdep'] = $Page->getPOSTVar('sel_rep_department');
- $params['start'] = $Page->getPOSTVar('start_code1');
- $params['finish'] = $Page->getPOSTVar('finish_code1');
- if ($Page->getGETVar('a_ids'))
- {
- $params['a_ids'] = $Page->getGETVar('a_ids');
- }
- unset($content_url);
- $params['part'] = 'content2';
- $content_url2 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'header2';
- $header_url2 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'footer2';
- $footer_url2 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $wkpdf = new WKPDF($content_url2);
- $wkpdf->footerUrl = $footer_url2;
- $wkpdf->headerUrl = $header_url2;
- $wkpdf->marginTop = '35';
- $wkpdf->marginBottom = '20';
- $wkpdf->output('S', 'Reports'.$stocktake_id .'\StockDetailReport__' . $stocktake_id .'.pdf');
- }
- else if ($part == 'content2')
- {
- $all_sections = $Page->getPOSTVar('all_sections');
- if ($all_sections) {
- $section = null;
- }
- else {
- $secarr = $Navigation->getParam('section');
- $section = '';
- if ($secarr && is_array($secarr) && count($secarr) > 0)
- {
- foreach($secarr as $s)
- {
- $section.= db::c()->quote($s) . ',';
- }
- $section = substr($section, 0, -1);
- }
- else
- {
- $section = null;
- }
- }
- $sdep = $Navigation->getParam('sdep');
- $start = $Navigation->getParam('start');
- $finish = $Navigation->getParam('finish');
- if (!$sdep || $sdep == '00all')
- {
- $sdep = null;
- }
- echo $html_begin;
- echo Report3::stocktakeDetails($stocktake_id);
- echo Report3::stockDetailReportContent($stocktake_id, $sections, $sdep);
- echo $html_end;
- }
- else if ($part == 'header2')
- {
- echo $html_begin;
- echo Report3::reportHeader('Stock Detail Report');
- echo $html_end;
- }
- else if ($part == 'footer2')
- {
- echo $html_begin;
- echo "<div class='page_numbers'> – Page <span class='page'></span> of <span class='topage'></span> – </div>";
- echo $html_end;
- }
- //Audit trail report
- class Report4{
- function areaAuditTrailReport2Content($stocktake_id, $area_ids = null, $sections){
- //get currency code for the stocktake
- $cur_symbol = Stocktake::getCurrencySymbol($stocktake_id);
- //get all areas for the given stocktake for which there are valid scans
- $area_qry = "
- SELECT * FROM
- (( SELECT
- sa.stocktake_area_id, sa.area_name
- FROM stocktake_areas sa
- INNER JOIN stocktake_scans ss
- ON sa.stocktake_area_id = ss.stocktake_area_id
- WHERE sa.stocktake_id = '{$stocktake_id}' AND ss.stocktake_id = '{$stocktake_id}' AND ss.quantity > 0 ";
- if ($area_ids) {
- $area_qry.= " AND sa.stocktake_area_id IN ($area_ids) ";
- }
- $area_qry.= "GROUP BY stocktake_area_id, area_name )
- UNION ALL
- ( SELECT
- sa.stocktake_area_id, sa.area_name
- FROM stocktake_areas sa
- INNER JOIN stocktake_items si
- ON sa.stocktake_area_id = si.stocktake_area_id
- WHERE sa.stocktake_id = '{$stocktake_id}' AND si.stocktake_id = '{$stocktake_id}' AND si.quantity > 0 ";
- if ($area_ids) {
- $area_qry.= " AND sa.stocktake_area_id IN ($area_ids) ";
- }
- $area_qry.= " GROUP BY stocktake_area_id, area_name )
- ) a ";
- $area_qry.= "
- ORDER BY area_name ASC " ;
- //echo $area_qry;
- //die();
- try{
- $area_res = db::c()->query($area_qry);
- }
- catch (Exception $e){
- echo $e;
- }
- $report = '';
- $grand_total_count = 0;
- $grand_total_cost = 0;
- $grand_total_ret = 0;
- //select all products or items which have no area_id but belong to this stocktake
- $noarea_qry = "
- SELECT * FROM
- ( (SELECT
- ss.barcode AS barcode, sp.product_name AS product_name, sp.product_description AS product_description,
- sp.unit_cost_price AS unit_cost_price, sp.unit_retail_price AS unit_retail_price, sp.product_source AS product_source,
- sp.unit_cost_price_estimated, sp.unit_retail_price_estimated,
- ss.quantity AS quantity, ss.scan_date AS scan_date, u.stocktake_staff_name AS stocktake_staff_name,
- UNIX_TIMESTAMP(ss.scan_date) AS scan_date_ts
- FROM stocktake_scans ss
- LEFT JOIN stocktake_products sp ON sp.stocktake_product_id = ss.stocktake_product_id
- LEFT JOIN stocktake_staff u ON u.stocktake_staff_id = ss.stocktake_staff_id
- LEFT JOIN stocktake_errors se ON se.barcode = ss.barcode AND se.stocktake_id = '{$stocktake_id}'
- WHERE
- ss.stocktake_id = '{$stocktake_id}'
- AND IFNULL(ss.stocktake_area_id, 0) = 0
- AND (
- se.resolution_status IS NULL OR (se.resolution_status <> 'WRONG BARCODE' AND se.resolution_status <> 'IGNORED')
- ) )
- UNION ALL
- ( SELECT
- 'N/A' AS barcode, si.product_name AS product_name, 'N/A' AS product_description,
- si.unit_cost_price AS unit_cost_price, si.unit_retail_price AS unit_retail_price, 'N/A' AS product_source,
- 'N/A' AS unit_cost_price_estimated, 'N/A' AS unit_retail_price_estimated,
- si.quantity AS quantity, si.date_created AS scan_date,
- 'N/A' AS stocktake_staff_name, UNIX_TIMESTAMP(si.date_created) AS scan_date_ts
- FROM
- stocktake_items si
- WHERE si.stocktake_id = '{$stocktake_id}'
- AND IFNULL(si.stocktake_area_id, 0) = 0 ) ) a
- ORDER BY scan_date_ts ASC; ";
- //echo $scan_qry;
- //die();
- $noarea_res = db::c()->query($noarea_qry);
- if ($noarea_res->rowCount())
- {
- $report .= "<div>
- <table cellspacing='0' class='at_report'>
- <thead>
- <tr><th colspan='8'>Area Not Specified</th></tr>
- </thead>
- <tr>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>No.</div></td>
- <td class='simple_header' style='width:8%;'><div class='nosplit'>Barcode</div></td>
- <td class='simple_header' style='width:40%;'><div class='nosplit'>Product</div></td>
- <td class='simple_header' style='width:5%;'><div class='nosplit'>Qty</div></td>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>Cost Value</div></td>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>Retail Value</div></td>
- <td class='simple_header' style='width:16%;'><div class='nosplit'>Created By</div></td>
- <td class='simple_header' style='width:10%;'><div class='nosplit'>Time</div></td>
- </tr>";
- $total_count = 0;
- $total_cost = 0;
- $total_ret = 0;
- $cost_e = false;
- $retail_e = false;
- $n = 1;
- $row_counter = 1;
- while ($nrow = $noarea_res->fetch(PDO::FETCH_ASSOC))
- {
- //print_r($nrow);
- //die();
- $cost = $nrow['quantity']*$nrow['unit_cost_price'];
- $ret = $nrow['quantity']*$nrow['unit_retail_price'];
- $cn = number_format($cost, 2);
- $rn = number_format($ret, 2);
- $total_count += $nrow['quantity'];
- $total_cost += $cost;
- $total_ret += $ret;
- $cost_estimated = $nrow['unit_cost_price_estimated'] ? ' (e)' : '';
- $retail_estimated = $nrow['unit_retail_price_estimated'] ? ' (e)' : '';
- if($nrow['unit_cost_price_estimated']) $cost_e = true;
- if($nrow['unit_retail_price_estimated']) $retail_e = true;
- $dt = new DateTime($nrow['scan_date']);
- $row_style ='';
- if ($nrow['product_source'] == 'M') $row_style = 'style="background:#ffdddd;"';
- $rounded_qty = (floor($nrow['quantity']) != $nrow['quantity']) ? $nrow['quantity'] : round($nrow['quantity']);
- //$time_string = date("H:i:s", $nrow['scan_date_ts']);
- //date_format($nrow['scan_date'], 'g:i A');
- $product_nd = $nrow['product_name'] ? $nrow['product_name'] : $nrow['product_description'];
- if (strlen($product_nd)> 40)
- $product_nd_short = substr($product_nd, 0, 40)."...";
- else
- $product_nd_short = $product_nd;
- if (strlen($product_nd)> 40)
- $product_nd_short = substr($product_nd, 0, 40)."...";
- else
- $product_nd_short = $product_nd;
- $report .= "<tr ".$row_style.">
- <td><div class='nosplit'>".$n."</div></td>
- <td><div class='nosplit'>".$nrow['barcode']."</div></td>
- <td><div class='nosplit'>".$product_nd_short."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".$rounded_qty."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".$cur_symbol.$cn.$cost_estimated."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".$cur_symbol.$rn.$retail_estimated."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".((strlen($nrow['stocktake_staff_name'])>16)? substr($nrow['stocktake_staff_name'], 0, 15)."..." : $nrow['stocktake_staff_name'])."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".$dt->format('H:i:s')."</div></td>
- </tr>";
- if ($row_counter%44 == 0) $report .= "</table> </div>
- <div class='at_report_div'>
- <table cellspacing='0' class='at_report'>
- <thead>
- <tr><th colspan='8'>Area Not Specified</th></tr>
- </thead>
- <tr>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>No.</div></td>
- <td class='simple_header' style='width:8%;'><div class='nosplit'>Barcode</div></td>
- <td class='simple_header' style='width:40%;'><div class='nosplit'>Product</div></td>
- <td class='simple_header' style='width:5%;'><div class='nosplit'>Qty</div></td>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>Cost Value</div></td>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>Retail Value</div></td>
- <td class='simple_header' style='width:16%;'><div class='nosplit'>Created By</div></td>
- <td class='simple_header' style='width:10%;'><div class='nosplit'>Time</div></td>
- </tr>";
- $n++;
- $row_counter++;
- }
- $tc = number_format($total_cost, 2);
- $tr = number_format($total_ret, 2);
- $rounded_tc = (floor($total_count) != $total_count) ? $total_count : round($total_count);
- $report .= "<tr>
- <td class='simple_footer' colspan='3' style='text-align:right;'><div class='nosplit'>Total:</div></td>
- <td class='simple_footer' style='text-align:center;'><div class='nosplit'>".$rounded_tc."</div></td>
- <td class='simple_footer' style='text-align:right;'><div class='nosplit'>".$cur_symbol.$tc."</div></td>
- <td class='simple_footer' style='text-align:right;'><div class='nosplit'>".$cur_symbol.$tr."</div></td>
- <td class='simple_footer'><div class='nosplit'> </div></td>
- <td class='simple_footer'><div class='nosplit'> </div></td>
- </tr>";
- $report .= "</table><br/><br/>
- </div>";
- $grand_total_count += $total_count;
- $grand_total_cost += $total_cost;
- $grand_total_ret += $total_ret;
- }
- while ($row = $area_res->fetch(PDO::FETCH_ASSOC)){
- $total_count = 0;
- $total_cost = 0;
- $total_ret = 0;
- $row_counter = 1;
- $report .= "<div ";
- if ($n != 1) $report .= "class='at_report_div' ";
- else $report .= "style='page-break-inside: avoid;'";
- $report .= ">
- <table cellspacing='0' class='at_report'>
- <thead>
- <tr><th colspan='8'>Area ".$row['area_name']."</th></tr>
- </thead>
- <tr>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>No.</div></td>
- <td class='simple_header' style='width:8%;'><div class='nosplit'>Barcode</div></td>
- <td class='simple_header' style='width:40%;'><div class='nosplit'>Product</div></td>
- <td class='simple_header' style='width:5%;'><div class='nosplit'>Qty</div></td>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>Cost Value</div></td>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>Retail Value</div></td>
- <td class='simple_header' style='width:16%;'><div class='nosplit'>Created By</div></td>
- <td class='simple_header' style='width:10%;'><div class='nosplit'>Time</div></td>
- </tr>";
- $area_id = $row['stocktake_area_id'];
- //select all the scans for the current area
- $scan_qry = "
- SELECT * FROM
- ( (SELECT
- ss.barcode AS barcode, sp.product_name AS product_name, sp.product_description AS product_description,
- sp.unit_cost_price AS unit_cost_price, sp.unit_retail_price AS unit_retail_price, sp.product_source AS product_source,
- sp.unit_cost_price_estimated AS unit_cost_price_estimated, sp.unit_retail_price_estimated AS unit_retail_price_estimated,
- ss.quantity AS quantity, ss.scan_date AS scan_date, u.stocktake_staff_name AS stocktake_staff_name,
- UNIX_TIMESTAMP(ss.scan_date) AS scan_date_ts
- FROM stocktake_scans ss
- LEFT JOIN stocktake_products sp ON sp.stocktake_product_id = ss.stocktake_product_id
- LEFT JOIN stocktake_staff u ON u.stocktake_staff_id = ss.stocktake_staff_id
- LEFT JOIN stocktake_errors se ON se.barcode = ss.barcode AND se.stocktake_id = '{$stocktake_id}'";
- if ($sections)
- {
- $scan_qry .= " LEFT JOIN stocktake_areas sa ON ss.stocktake_area_id = sa.stocktake_area_id ";
- }
- $scan_qry .= "
- WHERE
- ss.stocktake_id = '{$stocktake_id}'
- AND ss.stocktake_area_id = '{$area_id}'
- AND (
- se.resolution_status IS NULL OR (se.resolution_status <> 'WRONG BARCODE' AND se.resolution_status <> 'IGNORED')
- ) ";
- if ($sections)
- {
- $scan_qry .= " AND sa.section IN ({$sections}) ";
- }
- $scan_qry .= " )
- UNION ALL
- ( SELECT
- 'N/A' AS barcode, si.product_name AS product_name, 'N/A' AS product_description, 'N/A' AS product_source,
- si.unit_cost_price AS unit_cost_price, si.unit_retail_price AS unit_retail_price,
- 'N/A' AS unit_cost_price_estimated, 'N/A' AS unit_retail_price_estimated,
- si.quantity AS quantity, si.date_created AS scan_date,
- 'N/A' AS stocktake_staff_name, UNIX_TIMESTAMP(si.date_created) AS scan_date_ts
- FROM
- stocktake_items si ";
- if ($sections)
- {
- $scan_qry .= " LEFT JOIN stocktake_areas sa ON si.stocktake_area_id = sa.stocktake_area_id ";
- }
- $scan_qry .= " WHERE si.stocktake_id = '{$stocktake_id}'
- AND si.stocktake_area_id = '{$area_id}' ";
- if ($sections)
- {
- $scan_qry .= " AND sa.section IN ({$sections}) ";
- }
- $scan_qry .= " ) ) a
- ORDER BY scan_date_ts ASC; ";
- //echo $scan_qry;
- //die();
- $scan_res = db::c()->query($scan_qry);
- $i = 1;
- while ($srow = $scan_res->fetch(PDO::FETCH_ASSOC)){
- //print_r($srow);
- //die();
- if ($srow['quantity'] > 0)
- {
- $cost = $srow['quantity']*$srow['unit_cost_price'];
- $ret = $srow['quantity']*$srow['unit_retail_price'];
- $cn = number_format($cost, 2);
- $rn = number_format($ret, 2);
- $total_count += $srow['quantity'];
- $total_cost += $cost;
- $total_ret += $ret;
- $mcost_estimated = $srow['unit_cost_price_estimated'] ? ' (e)' : '';
- $mretail_estimated = $srow['unit_retail_price_estimated'] ? ' (e)' : '';
- $dt = new DateTime($srow['scan_date']);
- $row_style ='';
- if ($srow['product_source'] == 'M') $row_style = 'style="background:#ffdddd;"';
- $rounded_qty = (floor($srow['quantity']) != $srow['quantity']) ? $srow['quantity'] : round($srow['quantity']);
- $product_nd = $srow['product_name'] ? $srow['product_name'] : $srow['product_description'];
- if (strlen($product_nd) > 40)
- $product_nd_short = substr($product_nd, 0, 40)."...";
- else
- $product_nd_short = $product_nd;
- //$time_string = date("H:i:s", $srow['scan_date_ts']);
- //date_format($srow['scan_date'], 'g:i A');
- $report .= "<tr ".$row_style.">
- <td><div class='nosplit'>".$n."</div></td>
- <td><div class='nosplit'>".$srow['barcode']."</div></td>
- <td><div class='nosplit'>".$product_nd_short."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".$rounded_qty."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".$cur_symbol.$cn.$mcost_estimated."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".$cur_symbol.$rn.$mretail_estimated."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".((strlen($srow['stocktake_staff_name'])>16)? substr($srow['stocktake_staff_name'], 0, 16)."..." : $srow['stocktake_staff_name'])."</div></td>
- <td style='text-align:center;'><div class='nosplit'>".$dt->format('H:i:s')."</div></td>
- </tr>";
- if ($row_counter%44 == 0) $report .= "</table><br/><br/></div>
- <div class='at_report_div'>
- <table cellspacing='0' class='at_report'>
- <thead>
- <tr><th colspan='8'>Area ".$row['area_name']."</th></tr>
- </thead>
- <tr>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>No.</div></td>
- <td class='simple_header' style='width:8%;'><div class='nosplit'>Barcode</div></td>
- <td class='simple_header' style='width:40%;'><div class='nosplit'>Product</div></td>
- <td class='simple_header' style='width:5%;'><div class='nosplit'>Qty</div></td>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>Cost Value</div></td>
- <td class='simple_header' style='width:7%;'><div class='nosplit'>Retail Value</div></td>
- <td class='simple_header' style='width:16%;'><div class='nosplit'>Created By</div></td>
- <td class='simple_header' style='width:10%;'><div class='nosplit'>Time</div></td>
- </tr>";
- $row_counter++;
- $n++;
- }
- }
- $i++;
- $tc = number_format($total_cost, 2);
- $tr = number_format($total_ret, 2);
- $rounded_tc = (floor($total_count) != $total_count) ? $total_count : round($total_count);
- $report .= "<tr>
- <td class='simple_footer' colspan='3' style='text-align:right;'><div class='nosplit'>Total:</div></td>
- <td class='simple_footer' style='text-align:center;'><div class='nosplit'>".$rounded_tc."</div></td>
- <td class='simple_footer' style='text-align:right;'><div class='nosplit'>".$cur_symbol.$tc."</div></td>
- <td class='simple_footer' style='text-align:right;'><div class='nosplit'>".$cur_symbol.$tr."</div></td>
- <td class='simple_footer'><div class='nosplit'> </div></td>
- <td class='simple_footer'><div class='nosplit'> </div></td>
- </tr>";
- $report .= "</table><br/><br/>
- </div>";
- $grand_total_count += $total_count;
- $grand_total_cost += $total_cost;
- $grand_total_ret += $total_ret;
- }
- $gtc = number_format($grand_total_cost, 2);
- $gtr = number_format($grand_total_ret, 2);
- $grand_rounded_tc = (floor($grand_total_count) != $grand_total_count) ? $grand_total_count : round($grand_total_count);
- $report .= "<table cellspacing='0' class='at_report'>
- <tr>
- <td class='simple_footer' style='text-align:right;width:55%;'><div class='nosplit'>Grand Total:</div></td>
- <td class='simple_footer' style='text-align:center;width:5%;'><div class='nosplit'>".$grand_rounded_tc."</div></td>
- <td class='simple_footer' style='text-align:right;width:7%;'><div class='nosplit'>".$cur_symbol.$gtc."</div></td>
- <td class='simple_footer' style='text-align:right;width:7%;'><div class='nosplit'>".$cur_symbol.$gtr."</div></td>
- <td class='simple_footer' style='width:16%;'><div class='nosplit'> </div></td>
- <td class='simple_footer' style='width:10%;'><div class='nosplit'> </div></td>
- </tr>
- </table>
- </div><br/><br/>";
- return $report;
- }
- function reportHeader($title){
- $header = "<table class='report_header' cellspacing='0' cellpadding='0'>
- <tr >
- <td style='width:50%; text-align:left;'>
- <strong>Stocktaking.ie</strong><br/>
- Lakeview Point<br/>
- Claregalway Corporate Park<br/>
- Claregalway, Galway
- </td>
- <td style='text-align:right;'>
- <img src='/images/stocktaking_s.gif' alt='Stocktaking' height='69' width='211'/>
- </td>
- </tr>
- <tr>
- <td colspan='2'><h2 style='color:black;font-size:20px;font-weight:bold;margin-top:10px;'>".$title."</h2></td>
- </tr>
- </table>";
- return $header;
- }
- function stocktakeDetails($stocktake_id){
- $stocktake_qry = "
- SELECT
- s.stocktake_date, s.customer_name, s.store_name, s.store_address, s.store_post_code, s.store_town, s.store_county_name,
- c.country_name, s.date_created
- FROM stocktakes s LEFT JOIN country c
- ON c.country_code_alpha2 = s.customer_country_code
- WHERE stocktake_id = '{$stocktake_id}';";
- $stocktake_res = db::c()->query($stocktake_qry);
- $stocktake_row = $stocktake_res->fetch(PDO::FETCH_ASSOC);
- $date_created = $stocktake_row['date_created'];
- $stocktake_date = l::date($stocktake_row['stocktake_date']);
- $stocktake_details = "<table class='st_det_rep'>
- <tr>
- <td style='width:40%; text-align:left;'>
- <table style='font-size:12px;float:left;'>
- <tr>
- <td style='text-align:left;'>Customer:</td>
- <td style='text-align:left;'><strong>".$stocktake_row['customer_name']."</strong></td>
- </tr>
- <tr>
- <td style='width:120px; text-align:left;'>Stocktake Number:</td>
- <td style='text-align:left;'>".$stocktake_id."</td>
- </tr>
- <tr>
- <td style='text-align:left;'>Stocktake Date:</td>
- <td style='text-align:left;'>".$stocktake_date."</td>
- </tr>
- </table>
- </td>
- <td style='width:60%; text-align:left;'>
- <table style='font-size:12px;float:right;'>
- <tr>
- <td style='width:120px;text-align:right;font-weight:bold;padding-right:20px;'>Store:</td>
- <td style='text-align:right;font-weight:bold;'>".$stocktake_row['store_name']."</td>
- </tr>
- <tr>
- <td style='text-align:left;'></td>
- <td style='text-align:right;'>".$stocktake_row['store_address']."<br/>
- ".$stocktake_row['store_town'].", ".$stocktake_row['store_post_code']."<br/>
- ".$stocktake_row['store_county_name'].", ".$stocktake_row['country_name']."
- </td>
- </tr>
- </table>
- </td>
- </tr>
- </table>";
- return $stocktake_details;
- }
- }
- $report = $Navigation->getParam('report');
- $stocktake_id = $Navigation->getParam('stocktake_id');
- $section[] = $_POST['section'];
- $part = $Navigation->getParam('part');
- $html_begin = '<html><head>
- <link rel="stylesheet" type="text/css" href="http://sttest.aptvision.com/styles/main_style.css" />
- <script>
- function subst() {
- var vars={};
- var x=document.location.search.substring(1).split("&");
- for(var i in x) {var z=x[i].split("=",2);vars[z[0]] = unescape(z[1]);}
- var x=["topage","page"];
- for(var i in x) {
- var y = document.getElementsByClassName(x[i]);
- for(var j=0; j<y.length; ++j) y[j].textContent = vars[x[i]];
- }
- }
- </script>
- </head><body onload="subst()">';
- $html_end = '</body></html>';
- if (!$part)
- {
- //update stocktake
- $res = db::c()->query("SELECT stocktake_product_id FROM stocktake_scans WHERE stocktake_id = 304");
- while ($row = $res->fetch())
- {
- $qty = db::c()->query("SELECT SUM(quantity) FROM stocktake_scans WHERE stocktake_product_id = '{$row['stocktake_product_id']}';")->fetchColumn(0);
- if (!$qty)
- $qty = '0';
- db::c()->query("UPDATE stocktake_products SET quantity_scanned = '{$qty}' WHERE stocktake_product_id = '{$row['stocktake_product_id']}';");
- }
- $params = array();
- $params['security_off'] = true;
- $params['stocktake_id'] = $stocktake_id;
- $params['report'] = $report;
- $params['section'] = $Page->getPOSTVar('section');
- $params['sdep'] = $Page->getPOSTVar('sel_rep_department');
- $params['start'] = $Page->getPOSTVar('start_code1');
- $params['finish'] = $Page->getPOSTVar('finish_code1');
- if ($Page->getGETVar('a_ids'))
- {
- $params['a_ids'] = $Page->getGETVar('a_ids');
- }
- unset($content_url);
- $params['part'] = 'content3';
- $content_url3 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'header3';
- $header_url3 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'footer3';
- $footer_url3 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $wkpdf = new WKPDF($content_url3);
- $wkpdf->footerUrl = $footer_url3;
- $wkpdf->headerUrl = $header_url3;
- $wkpdf->marginTop = '35';
- $wkpdf->marginBottom = '20';
- $wkpdf->output('S', 'Reports'.$stocktake_id .'\AuditTrailReport__' . $stocktake_id .'.pdf');
- }
- else if ($part == 'content3')
- {
- $all_sections = $Page->getPOSTVar('all_sections');
- if ($all_sections) {
- $section = null;
- }
- else {
- $secarr = $Navigation->getParam('section');
- $section = '';
- if ($secarr && is_array($secarr) && count($secarr) > 0)
- {
- foreach($secarr as $s)
- {
- $section.= db::c()->quote($s) . ',';
- }
- $section = substr($section, 0, -1);
- }
- else
- {
- $section = null;
- }
- }
- $sdep = $Navigation->getParam('sdep');
- $start = $Navigation->getParam('start');
- $finish = $Navigation->getParam('finish');
- if (!$sdep || $sdep == '00all')
- {
- $sdep = null;
- }
- echo $html_begin;
- echo Report4::stocktakeDetails($stocktake_id);
- echo Report4::areaAuditTrailReport2Content($stocktake_id, $area_ids = null, $sections);
- echo $html_end;
- }
- else if ($part == 'header3')
- {
- echo $html_begin;
- echo Report4::reportHeader('Audit Trail Report');
- echo $html_end;
- }
- else if ($part == 'footer3')
- {
- echo $html_begin;
- echo "<div class='page_numbers'> – Page <span class='page'></span> of <span class='topage'></span> – </div>";
- echo $html_end;
- }
- //Valuation Certificate
- class Report5{
- function valuationCertificateContent($stocktake_id, $sections){
- //get currency code for the stocktake
- $cur_symbol = Stocktake::getCurrencySymbol($stocktake_id);
- // $report = "<table class='report_header' style='border:0px;'>
- // <tr>
- // <td style='width:50%; text-align:left;'>
- // <strong>Stocktaking.ie</strong><br/>
- // Lakeview Point<br/>
- // Claregalway Corporate Park<br/>
- // Claregalway<br/>
- // Galway<br/><br/><br/><br/><br/>
- // </td>
- // <td style='text-align:right;'>
- // <img src='/images/stocktaking.gif' alt='Stocktaking' height='92' width='211'/>
- // </td>
- // </tr>
- // <tr>
- // <td colspan='2' style='border-top:1px solid #cccccc;border-bottom:1px solid #cccccc;padding:5px;'>
- // <div style='padding:6px;color:black;font-size:20px;font-weight:bold;background:#eeeeee;'>
- // Valuation Certificate
- // </div>
- // </td>
- // </tr>
- // </table>";
- $stocktake_qry = "
- SELECT
- s.store_name, s.store_address, s.store_post_code, s.store_town, s.store_county_name,
- c.country_name, s.stocktake_date AS date_created
- FROM stocktakes s LEFT JOIN country c
- ON c.country_code_alpha2 = s.store_country_code
- WHERE stocktake_id = '{$stocktake_id}';";
- $stocktake_res = db::c()->query($stocktake_qry);
- $stocktake_row = $stocktake_res->fetch(PDO::FETCH_ASSOC);
- $date_created = $stocktake_row['date_created'];
- $stocktake_date = l::date($date_created);
- $report = "<table class='st_det_rep'>
- <tr>
- <td style='width:60%; text-align:left;'>
- <strong>".$stocktake_row['store_name']."</strong><br/>
- ".$stocktake_row['store_address']."<br/>
- ".$stocktake_row['store_post_code']."<br/>
- ".$stocktake_row['store_town'].", ".$stocktake_row['store_county_name']."<br/>
- ".$stocktake_row['country_name']."
- </td>
- <td>
- <table style='width:100%;'>
- <tr>
- <td style='text-align:right;font-weight:bold;'>Valuation Date:</td>
- </tr>
- <tr>
- <td style='text-align:right;'>".$stocktake_date."</td>
- </tr>
- </table>
- </td>
- </tr>
- </table>";
- //do a query to calculate total values
- $total_qry = "
- SELECT SUM( sp.unit_cost_price * ss.quantity) AS cost, SUM( sp.unit_retail_price * ss.quantity ) AS ret
- FROM stocktake_scans ss
- LEFT JOIN stocktake_products sp ON sp.stocktake_product_id = ss.stocktake_product_id ";
- if ($sections)
- {
- $total_qry .= " INNER JOIN stocktake_areas sa ON ss.stocktake_area_id = sa.stocktake_area_id ";
- }
- $total_qry .= " WHERE ss.stocktake_id = '{$stocktake_id}' ";
- if ($sections)
- {
- $total_qry .= " AND sa.section IN ({$sections}) ";
- }
- $total_qry .= " ;";
- $total_res = db::c()->query($total_qry)->fetch(PDO::FETCH_ASSOC);
- $nonbc_total_qry = "
- SELECT SUM( si.unit_cost_price * si.quantity ) AS cost, SUM( si.unit_retail_price * si.quantity ) AS ret
- FROM stocktake_items si
- WHERE
- si.stocktake_id = '{$stocktake_id}'";
- if ($sections)
- {
- $nonbc_total_qry .= " AND si.section IN ({$sections}) ";
- }
- $nonbc_total_res = db::c()->query($nonbc_total_qry)->fetch(PDO::FETCH_ASSOC);
- $total_cost = $total_res['cost'] + $nonbc_total_res['cost'];
- $total_retail = $total_res['ret'] + $nonbc_total_res['ret'];
- $tc = number_format($total_cost, 2);
- $tr = number_format($total_retail, 2);
- $report .= "<div style='width:100%;background:white;padding:30px 10px;'>
- <table cellspacing='0' id='val_report'>
- <tr>
- <td class='simple_header' colspan='2'>".$stocktake_row['store_name']."</td>
- </tr>
- <tr>
- <td class='title'>Value at Cost</td>
- <td class='title'>Value at Retail</td>
- </tr>
- <tr>
- <td>".$cur_symbol.$tc."</td>
- <td>".$cur_symbol.$tr."</td>
- </tr>
- </table>
- </div>";
- $report .= "<div style='width:100%;background:white;padding:10px;text-align:left;vertical-align:top;font-size:12px;'>
- <div class='simple_title'>Valuation Carried Out By:</div>";
- //get all the staff that worked on the stocktake
- $staff_qry = "
- SELECT
- stocktake_staff_name
- FROM stocktake_staff
- WHERE stocktake_id = ". $stocktake_id ."
- ORDER BY stocktake_staff_id ASC;";
- $staff_res = db::c()->query($staff_qry);
- while ($staff_row = $staff_res->fetch(PDO::FETCH_ASSOC))
- {
- $report .= $staff_row['stocktake_staff_name'];
- $report .= "<br/>";
- }
- $report .= "<br/><br/></div>";
- return $report;
- }
- function reportHeader($title){
- $header = "<table class='report_header' cellspacing='0' cellpadding='0'>
- <tr >
- <td style='width:50%; text-align:left;'>
- <strong>Stocktaking.ie</strong><br/>
- Lakeview Point<br/>
- Claregalway Corporate Park<br/>
- Claregalway, Galway
- </td>
- <td style='text-align:right;'>
- <img src='/images/stocktaking_s.gif' alt='Stocktaking' height='69' width='211'/>
- </td>
- </tr>
- <tr>
- <td colspan='2'><h2 style='color:black;font-size:20px;font-weight:bold;margin-top:10px;'>".$title."</h2></td>
- </tr>
- </table>";
- return $header;
- }
- function stocktakeDetails($stocktake_id){
- $stocktake_qry = "
- SELECT
- s.stocktake_date, s.customer_name, s.store_name, s.store_address, s.store_post_code, s.store_town, s.store_county_name,
- c.country_name, s.date_created
- FROM stocktakes s LEFT JOIN country c
- ON c.country_code_alpha2 = s.customer_country_code
- WHERE stocktake_id = '{$stocktake_id}';";
- $stocktake_res = db::c()->query($stocktake_qry);
- $stocktake_row = $stocktake_res->fetch(PDO::FETCH_ASSOC);
- $date_created = $stocktake_row['date_created'];
- $stocktake_date = l::date($stocktake_row['stocktake_date']);
- $stocktake_details = "<table class='st_det_rep'>
- <tr>
- <td style='width:40%; text-align:left;'>
- <table style='font-size:12px;float:left;'>
- <tr>
- <td style='text-align:left;'>Customer:</td>
- <td style='text-align:left;'><strong>".$stocktake_row['customer_name']."</strong></td>
- </tr>
- <tr>
- <td style='width:120px; text-align:left;'>Stocktake Number:</td>
- <td style='text-align:left;'>".$stocktake_id."</td>
- </tr>
- <tr>
- <td style='text-align:left;'>Stocktake Date:</td>
- <td style='text-align:left;'>".$stocktake_date."</td>
- </tr>
- </table>
- </td>
- <td style='width:60%; text-align:left;'>
- <table style='font-size:12px;float:right;'>
- <tr>
- <td style='width:120px;text-align:right;font-weight:bold;padding-right:20px;'>Store:</td>
- <td style='text-align:right;font-weight:bold;'>".$stocktake_row['store_name']."</td>
- </tr>
- <tr>
- <td style='text-align:left;'></td>
- <td style='text-align:right;'>".$stocktake_row['store_address']."<br/>
- ".$stocktake_row['store_town'].", ".$stocktake_row['store_post_code']."<br/>
- ".$stocktake_row['store_county_name'].", ".$stocktake_row['country_name']."
- </td>
- </tr>
- </table>
- </td>
- </tr>
- </table>";
- return $stocktake_details;
- }
- }
- $report = $Navigation->getParam('report');
- $stocktake_id = $Navigation->getParam('stocktake_id');
- $section[] = $_POST['section'];
- $part = $Navigation->getParam('part');
- $html_begin = '<html><head>
- <link rel="stylesheet" type="text/css" href="http://sttest.aptvision.com/styles/main_style.css" />
- <script>
- function subst() {
- var vars={};
- var x=document.location.search.substring(1).split("&");
- for(var i in x) {var z=x[i].split("=",2);vars[z[0]] = unescape(z[1]);}
- var x=["topage","page"];
- for(var i in x) {
- var y = document.getElementsByClassName(x[i]);
- for(var j=0; j<y.length; ++j) y[j].textContent = vars[x[i]];
- }
- }
- </script>
- </head><body onload="subst()">';
- $html_end = '</body></html>';
- if (!$part)
- {
- //update stocktake
- $res = db::c()->query("SELECT stocktake_product_id FROM stocktake_scans WHERE stocktake_id = 304");
- while ($row = $res->fetch())
- {
- $qty = db::c()->query("SELECT SUM(quantity) FROM stocktake_scans WHERE stocktake_product_id = '{$row['stocktake_product_id']}';")->fetchColumn(0);
- if (!$qty)
- $qty = '0';
- db::c()->query("UPDATE stocktake_products SET quantity_scanned = '{$qty}' WHERE stocktake_product_id = '{$row['stocktake_product_id']}';");
- }
- $params = array();
- $params['security_off'] = true;
- $params['stocktake_id'] = $stocktake_id;
- $params['report'] = $report;
- $params['section'] = $Page->getPOSTVar('section');
- $params['sdep'] = $Page->getPOSTVar('sel_rep_department');
- $params['start'] = $Page->getPOSTVar('start_code1');
- $params['finish'] = $Page->getPOSTVar('finish_code1');
- if ($Page->getGETVar('a_ids'))
- {
- $params['a_ids'] = $Page->getGETVar('a_ids');
- }
- unset($content_url);
- $params['part'] = 'content4';
- $content_url4 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'header4';
- $header_url4 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'footer4';
- $footer_url4 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $wkpdf = new WKPDF($content_url4);
- $wkpdf->footerUrl = $footer_url4;
- $wkpdf->headerUrl = $header_url4;
- $wkpdf->marginTop = '35';
- $wkpdf->marginBottom = '20';
- $wkpdf->output('S', 'Reports'.$stocktake_id .'\ValuationCertificate__' . $stocktake_id .'.pdf');
- }
- else if ($part == 'content4')
- {
- $all_sections = $Page->getPOSTVar('all_sections');
- if ($all_sections) {
- $section = null;
- }
- else {
- $secarr = $Navigation->getParam('section');
- $section = '';
- if ($secarr && is_array($secarr) && count($secarr) > 0)
- {
- foreach($secarr as $s)
- {
- $section.= db::c()->quote($s) . ',';
- }
- $section = substr($section, 0, -1);
- }
- else
- {
- $section = null;
- }
- }
- $sdep = $Navigation->getParam('sdep');
- $start = $Navigation->getParam('start');
- $finish = $Navigation->getParam('finish');
- if (!$sdep || $sdep == '00all')
- {
- $sdep = null;
- }
- echo $html_begin;
- echo Report5::stocktakeDetails($stocktake_id);
- echo Report5::valuationCertificateContent($stocktake_id, $sections);
- echo $html_end;
- }
- else if ($part == 'header4')
- {
- echo $html_begin;
- echo Report5::reportHeader('Valuation Certificate');
- echo $html_end;
- }
- else if ($part == 'footer4')
- {
- echo $html_begin;
- echo "<div class='page_numbers'> – Page <span class='page'></span> of <span class='topage'></span> – </div>";
- echo $html_end;
- }
- //Non scan list report
- class Report6{
- function nonScanListReportContent($stocktake_id, $sections){
- //get currency code for the stocktake
- $cur_symbol = Stocktake::getCurrencySymbol($stocktake_id);
- //$report = self::reportHeader("Non Scan Locations Report");
- //$report .= self::stocktakeDetails($stocktake_id);
- //get all the errors from the database
- $error_qry = " SELECT
- s.stocktake_scan_id, s.barcode, s.stocktake_area_id, a.area_name, p.unit_retail_price,p.unit_cost_price, p.product_name,
- p.product_description, p.department_description,
- st.stocktake_staff_name, s.scan_date, SUM(s.quantity) AS quantity
- FROM stocktake_scans s
- LEFT JOIN stocktake_areas a ON s.stocktake_area_id = a.stocktake_area_id
- LEFT JOIN stocktake_staff st ON s.stocktake_staff_id = st.stocktake_staff_id
- LEFT JOIN stocktake_products p ON p.stocktake_product_id = s.stocktake_product_id
- WHERE s.stocktake_id = '{$stocktake_id}' AND p.product_source = 'M'
- GROUP BY s.barcode";
- if ($sections)
- {
- $error_qry .= " AND a.section IN ({$sections}) ";
- }
- $error_qry .= " ORDER BY s.scan_date DESC, s.stocktake_scan_id DESC;";
- try{
- $error_res = db::c()->query($error_qry);
- }
- catch (Exception $e){
- echo "MySQL exception: $e ";
- }
- $report .= "
- <table id='nsloc_report' cellspacing='0'>
- <tr>
- <th style='width:10%;'><div class='nosplit'>Barcode</div></th>
- <th style='width:10%;'><div class='nosplit'>Quantity</div></th>
- <th style='width:8%;'><div class='nosplit'>Cost Price</div></th>
- <th style='width:8%;'><div class='nosplit'>Retail Price</div></th>
- <th style='width:26%;'><div class='nosplit'>Product</div></th>
- <th style='width:16%;'><div class='nosplit'>Department</div></th>
- <th style='width:10%;'><div class='nosplit'>Area</div></th>
- <th style='width:12%;'><div class='nosplit'>Staff member</div></th>
- <th style='width:8%;'><div class='nosplit'>Time</div></th>
- </tr>";
- while ($error_row = $error_res->fetch(PDO::FETCH_ASSOC))
- {
- //$date = date_create($error_row['date_time']);
- $area_id = $error_row['stocktake_area_id'];
- $date = new DateTime($error_row['scan_date']);
- $pname = empty($error_row['product_name']) ? $error_row['product_description'] : $error_row['product_name'];
- $report .= "<tr>
- <td class='topborder'><div class='nosplit'>".$error_row['barcode']."</div></td>
- <td class='topborder'><div class='nosplit'>".$error_row['quantity']."</div></td>
- <td class='topborder'><div class='nosplit'>".$error_row['unit_cost_price']."</div></td>
- <td class='topborder'><div class='nosplit'>".$error_row['unit_retail_price']."</div></td>
- <td class='topborder'><div class='nosplit'>".$pname."</div></td>
- <td class='topborder'><div class='nosplit'>".$error_row['department_description']."</div></td>
- <td class='topborder'><div class='nosplit'>".$error_row['area_name']."</div></td>
- <td class='topborder'><div class='nosplit'>".$error_row['stocktake_staff_name']."</div></td>
- <td class='topborder'><div class='nosplit'>".$date->format('H:i:s')."</div></td>
- </tr>";
- }
- $report .= "</table>";
- return $report;
- }
- function reportHeader($title){
- $header = "<table class='report_header' cellspacing='0' cellpadding='0'>
- <tr >
- <td style='width:50%; text-align:left;'>
- <strong>Stocktaking.ie</strong><br/>
- Lakeview Point<br/>
- Claregalway Corporate Park<br/>
- Claregalway, Galway
- </td>
- <td style='text-align:right;'>
- <img src='/images/stocktaking_s.gif' alt='Stocktaking' height='69' width='211'/>
- </td>
- </tr>
- <tr>
- <td colspan='2'><h2 style='color:black;font-size:20px;font-weight:bold;margin-top:10px;'>".$title."</h2></td>
- </tr>
- </table>";
- return $header;
- }
- function stocktakeDetails($stocktake_id){
- $stocktake_qry = "
- SELECT
- s.stocktake_date, s.customer_name, s.store_name, s.store_address, s.store_post_code, s.store_town, s.store_county_name,
- c.country_name, s.date_created
- FROM stocktakes s LEFT JOIN country c
- ON c.country_code_alpha2 = s.customer_country_code
- WHERE stocktake_id = '{$stocktake_id}';";
- $stocktake_res = db::c()->query($stocktake_qry);
- $stocktake_row = $stocktake_res->fetch(PDO::FETCH_ASSOC);
- $date_created = $stocktake_row['date_created'];
- $stocktake_date = l::date($stocktake_row['stocktake_date']);
- $stocktake_details = "<table class='st_det_rep'>
- <tr>
- <td style='width:40%; text-align:left;'>
- <table style='font-size:12px;float:left;'>
- <tr>
- <td style='text-align:left;'>Customer:</td>
- <td style='text-align:left;'><strong>".$stocktake_row['customer_name']."</strong></td>
- </tr>
- <tr>
- <td style='width:120px; text-align:left;'>Stocktake Number:</td>
- <td style='text-align:left;'>".$stocktake_id."</td>
- </tr>
- <tr>
- <td style='text-align:left;'>Stocktake Date:</td>
- <td style='text-align:left;'>".$stocktake_date."</td>
- </tr>
- </table>
- </td>
- <td style='width:60%; text-align:left;'>
- <table style='font-size:12px;float:right;'>
- <tr>
- <td style='width:120px;text-align:right;font-weight:bold;padding-right:20px;'>Store:</td>
- <td style='text-align:right;font-weight:bold;'>".$stocktake_row['store_name']."</td>
- </tr>
- <tr>
- <td style='text-align:left;'></td>
- <td style='text-align:right;'>".$stocktake_row['store_address']."<br/>
- ".$stocktake_row['store_town'].", ".$stocktake_row['store_post_code']."<br/>
- ".$stocktake_row['store_county_name'].", ".$stocktake_row['country_name']."
- </td>
- </tr>
- </table>
- </td>
- </tr>
- </table>";
- return $stocktake_details;
- }
- }
- $report = $Navigation->getParam('report');
- $stocktake_id = $Navigation->getParam('stocktake_id');
- $section[] = $_POST['section'];
- $part = $Navigation->getParam('part');
- $html_begin = '<html><head>
- <link rel="stylesheet" type="text/css" href="http://sttest.aptvision.com/styles/main_style.css" />
- <script>
- function subst() {
- var vars={};
- var x=document.location.search.substring(1).split("&");
- for(var i in x) {var z=x[i].split("=",2);vars[z[0]] = unescape(z[1]);}
- var x=["topage","page"];
- for(var i in x) {
- var y = document.getElementsByClassName(x[i]);
- for(var j=0; j<y.length; ++j) y[j].textContent = vars[x[i]];
- }
- }
- </script>
- </head><body onload="subst()">';
- $html_end = '</body></html>';
- if (!$part)
- {
- //update stocktake
- $res = db::c()->query("SELECT stocktake_product_id FROM stocktake_scans WHERE stocktake_id = 304");
- while ($row = $res->fetch())
- {
- $qty = db::c()->query("SELECT SUM(quantity) FROM stocktake_scans WHERE stocktake_product_id = '{$row['stocktake_product_id']}';")->fetchColumn(0);
- if (!$qty)
- $qty = '0';
- db::c()->query("UPDATE stocktake_products SET quantity_scanned = '{$qty}' WHERE stocktake_product_id = '{$row['stocktake_product_id']}';");
- }
- $params = array();
- $params['security_off'] = true;
- $params['stocktake_id'] = $stocktake_id;
- $params['report'] = $report;
- $params['section'] = $Page->getPOSTVar('section');
- $params['sdep'] = $Page->getPOSTVar('sel_rep_department');
- $params['start'] = $Page->getPOSTVar('start_code1');
- $params['finish'] = $Page->getPOSTVar('finish_code1');
- if ($Page->getGETVar('a_ids'))
- {
- $params['a_ids'] = $Page->getGETVar('a_ids');
- }
- unset($content_url);
- $params['part'] = 'content5';
- $content_url5 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'header5';
- $header_url5 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $params['part'] = 'footer5';
- $footer_url5 = Navigation::gUrl('/users/admin/reportspack.php', $params);
- $wkpdf = new WKPDF($content_url5);
- $wkpdf->footerUrl = $footer_url5;
- $wkpdf->headerUrl = $header_url5;
- $wkpdf->marginTop = '35';
- $wkpdf->marginBottom = '20';
- $wkpdf->output('S', 'Reports'.$stocktake_id .'\NonScanListReport__' . $stocktake_id .'.pdf');
- $content_url5= '';
- }
- else if ($part == 'content5')
- {
- $all_sections = $Page->getPOSTVar('all_sections');
- if ($all_sections) {
- $section = null;
- }
- else {
- $secarr = $Navigation->getParam('section');
- $section = '';
- if ($secarr && is_array($secarr) && count($secarr) > 0)
- {
- foreach($secarr as $s)
- {
- $section.= db::c()->quote($s) . ',';
- }
- $section = substr($section, 0, -1);
- }
- else
- {
- $section = null;
- }
- }
- $sdep = $Navigation->getParam('sdep');
- $start = $Navigation->getParam('start');
- $finish = $Navigation->getParam('finish');
- if (!$sdep || $sdep == '00all')
- {
- $sdep = null;
- }
- echo $html_begin;
- echo Report6::stocktakeDetails($stocktake_id);
- echo Report6::nonScanListReportContent($stocktake_id, $sections);
- echo $html_end;
- }
- else if ($part == 'header5')
- {
- echo $html_begin;
- echo Report6::reportHeader('Non Scan List Report');
- echo $html_end;
- }
- else if ($part == 'footer5')
- {
- echo $html_begin;
- echo "<div class='page_numbers'> – Page <span class='page'></span> of <span class='topage'></span> – </div>";
- echo $html_end;
- }
- $x++;
- }
- if ($x==1) {
- $dir = 'Reports'.$stocktake_id;
- $archive = 'C:\Temp\Reports'.$stocktake_id.'.zip';
- $zip = new ZipArchive;
- $zip->open($archive, ZipArchive::CREATE);
- $files = scandir($dir);
- unset($files[0], $files[1]);
- foreach ($files as $file) {
- $zip->addFile($dir.'/'.$file);
- }
- $zip->close();
- // $saved_file = $archive;
- // header("Refresh: 2;url=Reports".$stocktake_id.".zip");
- // flush();
- }
- require_once "/swiftmailer/swiftmailer/lib/swift_required.php";
- //require_once "mime.php";
- $query = "SELECT s.store_name AS store_name, s.supervisor_name AS supervisor_name ,DATE_FORMAT(s.stocktake_date, '%d/%l/%Y') AS stocktake_date, u.username AS username, u.password AS password
- FROM stocktakes s
- INNER JOIN users u ON u.company_id = s.sms_customer_id
- WHERE stocktake_id = '{$this->stocktake_id}'
- ";
- //echo $query;
- //die();
- $result = $this->dbClient->query($query);;
- $row = $result->fetch(PDO::FETCH_ASSOC);
- $file = 'C:\Temp\Reports'.$this->stocktake_id.'.zip';
- $transport = Swift_SmtpTransport::newInstance('lh233.dnsireland.com', 465, 'ssl')
- ->setUsername('arturl@stocktaking.ie')
- ->setPassword('leonowicz100%');
- $from = 'arturl@stocktaking.ie';
- $to = $emailAddress;
- //echo $to;
- //die();
- //$subject ='Test stocktake summary reports' ;
- $body = "Hello,\n\nPlease find attached reports for your most recent stocktake carried out at " . $row['store_name'] ."\n\n These reports have also been uploaded to the customer portal and can be accessed by logging in to http://reports.stocktaking.ie/ using the following credentials:\n\nusername:".$row['username']." \n\npassword:".$row['password']."\n\nShould you have any queries, please do not hesitate to contact me.\n\nKind Regards,\n\n ". $row['supervisor_name'] ."\n\n <<HOLDER FOR STOCKTAKING.IE SIGNATURE>>";
- $mailer = Swift_Mailer::newInstance($transport);
- $message = Swift_Message::newInstance("".$row['store_name'].",Stocktake Summary Reports, ".$row['stocktake_date']."")
- ->setFrom(array($from => $row['supervisor_name']))
- ->setTo(array($to, $to => $row['store_name']))
- ->setBody($body)
- ;
- $message->attach(Swift_Attachment::fromPath($file));
- //echo $message;
- $result = $mailer->send($message);
- echo $result;
- if ($result != 1) {
- echo('<p>Error when sending the e-mail.</p>');
- } else {
- echo('<h2>Message successfully sent!</h2>');
- }
- ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement