'') { $y=substr($starttime,0,4); $m=substr($starttime,5,2); $d=substr($starttime,8,2); //print "DAy $d Month $m Year $y
"; $csvoutput=""; $starttimeuni=mktime(1,1,1,$m,$d,$y); $y=substr($finishtime,0,4); $m=substr($finishtime,5,2); $d=substr($finishtime,8,2); //print "Month $m DAy $d Year $y
"; $endtimeuni=mktime(1,1,1,$m,$d+1,$y); } else { $starttimeuni=0; $endtimeuni=$nowis; } $displaytimeframe=date("d M Y",$starttimeuni)." - ".date("d M Y",$endtimeuni); if ($group=='') { $stock_ref=safetext2(isset($_GET['stkref']) ? $_GET['stkref'] : ''); $hostcompanyid=safetext2(isset($_GET['hostcompanyid']) ? $_GET['hostcompanyid'] : ''); if ($stock_ref<>'' and $hostcompanyid<>'' ) { $group = "SINGLE ITEM $stock_ref Host ".$global_company_name[$hostcompanyid].""; $sql = "SELECT groupcode FROM stockgroup WHERE groupcode=? LIMIT 1"; $stmt = pdoquery($sql, array($group)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); if (count($rows) == 0) { $i = "INSERT INTO stockgroup ( stk_ref , groupcode , owner , hostcompanyid) VALUES (?,?,?,?)"; pdoquery($i, array($stock_ref, $group, 'AutoGenerated', $hostcompanyid)); } } } // Prep Work, $type='Issue'; $type2='Return'; if ($starttime=='') { // No Start time set, // print "$s
"; if ($starttime=='') { $starttime=0; } } // Memory fix: pull only the 3 columns actually used, and stream rows instead of fetchAll. $stkdesc = array(); $lastrev = array(); $sql = "SELECT stock_ref, OperaDescription, custrepreview FROM stocklist_tarriffs WHERE hostcompany_id=?"; $stmt = pdoquery($sql, array($hostcompanyid)); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $stkref = $row['stock_ref']; $stkdesc[$stkref] = $row['OperaDescription']; $lastrev[$stkref] = $row['custrepreview']; } $stmt->closeCursor(); unset($stmt); $sql="SELECT viamed_customer_stock_references.companyid , viamed_customer_stock_references.stock_ref , viamed_customer_stock_references.customer_ref , viamed_customer_stock_references.customerdescription FROM viamed_customer_stock_references , stockgroup where viamed_customer_stock_references.hostcompanyid=? and viamed_customer_stock_references.status='2' and viamed_customer_stock_references.stock_ref=stockgroup.stk_ref and stockgroup.groupcode=? "; $stmt=pdoquery($sql, array($hostcompanyid, $group)); $rows=$stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $companyid=$row['companyid']; $cid=$companyid; $companyidlist[$companyid]=$companyid; $companyidcount[$companyid] = (isset($companyidcount[$companyid]) ? $companyidcount[$companyid] : 0) + 1; $stkref=$row['stock_ref']; $companyidstocklink=$companyid."companyid".$stkref; $supplierreference[$companyidstocklink]=$row['customer_ref']; $supplierdescription[$companyidstocklink]=$row['customerdescription']; $supplierdescriptiontemp=str_replace(","," ",substr($row['customerdescription'],0,48)); $supplierdescriptioncsv[$companyidstocklink]=$supplierdescriptiontemp; $supplierreference[$stkref]=$row['customer_ref']; $supplierdescription[$stkref]=$row['customerdescription']; if ($lastrev[$stkref] < $nowis-2628288) { $u="UPDATE stocklist_tarriffs set custrepreview=? where hostcompany_id=? and stock_ref=?"; pdoquery($u, array($nowis, $hostcompanyid, $stkref)); } } $wehavesupplier=sizeof($companyidlist); if ($wehavesupplier >1 and $usecid==''){ foreach($companyidlist as $ccomp) { $selectsupplier .= "
"; } } if ($usecid<>'') { $companyid=$usecid; $wehavesupplier=1; } if ($companyid=='') { $sql="SELECT companyid FROM stockgroup where groupcode=? and companyid<>'0' limit 0,1"; //print "$sql
"; $stmt=pdoquery($sql, array($group)); $rows=$stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $wehavesupplier=1; $companyid=$row['companyid']; //print "$companyid
"; } } // lets build a checklist of stock items $invdetailstksearch = " ( 1=2 "; $serialnumberstockseacrch = " ( 1=2 "; $sql = "SELECT stk_ref FROM stockgroup WHERE groupcode=? GROUP BY stk_ref"; $stmt = pdoquery($sql, array($group)); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $stkref = $row['stk_ref']; $ingroup[$stkref] = 1; // invoice detail search $invdetailstksearch .= "OR viamedquote_orders_invoices_details.stockref='$stkref' "; $serialnumberstockseacrch .= "OR Serialnumbers.Stock_ref='$stkref' "; } $stmt->closeCursor(); unset($stmt); $invdetailstksearch .= " ) "; $serialnumberstockseacrch .= " ) "; if ($supplierview==1) { //$s="SELECT * FROM viamed_customer_repairs_clean_faults, stockgroup } // calculate THE CLEANCODES $sql="SELECT viamed_customer_repairs_clean_faults.actual_fault as actual_fault, viamed_customer_repairs_clean_faults.stock_ref as stock_ref, viamed_customer_repairs_clean_faults.clean_fault as clean_fault, viamed_customer_repairs_clean_faults.true_fault as true_fault FROM viamed_customer_repairs_clean_faults,stockgroup where stockgroup.stk_ref=viamed_customer_repairs_clean_faults.stock_ref and stockgroup.groupcode=?"; $stmt=pdoquery($sql, array($group)); //print "$sql"; $rows=$stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $stkref=$row['stock_ref']; $actual_fault=md5($row['actual_fault']); $cleanraw=trim(ucwords($row['clean_fault'])); $cleanlink[$actual_fault]=$cleanraw; $faulttype[$actual_fault]=$row['true_fault']; $faulttypeclean[$cleanraw]=$row['true_fault']; $clean=md5($cleanraw); $cleanstocklink=md5($stkref."ZXZZ".$cleanraw); //print "$stkref $clean $row[clean_fault] $cleanstocklink
"; } // Lets search for the sales qtys $sql="SELECT viamedquote_orders_invoices_details.deliveryid, viamedquote_orders_invoices_details.stockref FROM viamedquote_orders_invoices_details, viamedquote_orders_invoices WHERE viamedquote_orders_invoices.invoiceref=viamedquote_orders_invoices_details.invoiceref and viamedquote_orders_invoices.invoicecommitedon >? and viamedquote_orders_invoices.invoicecommitedon fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $stkref=$row['stockref']; if ($row['deliveryid'] >0) { $qtysold[$stkref] = (isset($qtysold[$stkref]) ? $qtysold[$stkref] : 0) + 1; } else { $qtysold[$stkref] = (isset($qtysold[$stkref]) ? $qtysold[$stkref] : 0) - 1; } } foreach ($qtysold as $stkref => $value) { $salesdata .= "Total $stkref Units in System $displaytimeframe : $value
"; $csvsalesdata .= "Total $stkref Units in System $displaytimeframe,$value \n"; $mdsalesdata .= "- Total **$stkref** Units in System $displaytimeframe: **$value**\n"; $totalunitssold=$totalunitssold+$value; } $stock_ref = isset ( $stock_ref ) ? $stock_ref : ''; if ($stock_ref=='') { $salesdata .= "Total Units in System $displaytimeframe : $totalunitssold
"; $csvsalesdata .= "Total Units in System $displaytimeframe,$totalunitssold \n"; $mdsalesdata .= "- **Total Units in System** $displaytimeframe: **$totalunitssold**\n"; } $sql="SELECT Serialnumbers.id,Serialnumbers.Porref,Serialnumbers.Datebookedin FROM Serialnumbers where $serialnumberstockseacrch AND Serialnumbers.Datebookedin >? and Serialnumbers.Datebookedin "; $stmt=pdoquery($sql, array($starttimeuni, $endtimeuni)); $rows=$stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $barcode=$row['id']; $Datebookedin=$row['Datebookedin']; $batchin[$Datebookedin] = (isset($batchin[$Datebookedin]) ? $batchin[$Datebookedin] : 0) + 1; } // Memory fix: this previously joined the WHOLE viamed_customer_repairs_details table // to Serialnumbers with no filter. Restrict to barcodes we actually booked in during the // time-frame ($batchin keys come from the previous Serialnumbers query). $barcodelist = array(); $barcodewarr = array(); $faultcodebarcode = array(); if (!empty($batchin)) { // Build IN-list of Datebookedin keys -> we instead constrain on Serialnumbers.Datebookedin range. $sql = "SELECT viamed_customer_repairs_details.fault_code AS fault_code, viamed_customer_repairs_details.barcode AS barcode, viamed_customer_repairs_details.warranty_new AS warranty_new, Serialnumbers.Datebookedin AS Datebookedin FROM viamed_customer_repairs_details INNER JOIN Serialnumbers ON viamed_customer_repairs_details.barcode = Serialnumbers.id WHERE Serialnumbers.Datebookedin > ? AND Serialnumbers.Datebookedin < ? GROUP BY Serialnumbers.id"; $stmt = pdoquery($sql, array($starttimeuni, $endtimeuni)); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $barcode = $row['barcode']; $barcodelist[$barcode] = $row['Datebookedin']; $barcodewarr[$barcode] = $row['warranty_new']; $faultcodebarcode[$barcode] = $row['fault_code']; } $stmt->closeCursor(); unset($stmt); } foreach ($barcodelist as $barcode => $Datebookedin) { if ($barcodewarr[$barcode]=='') { $Datebookedincountreturn[$Datebookedin] = (isset($Datebookedincountreturn[$Datebookedin]) ? $Datebookedincountreturn[$Datebookedin] : 0) + 1; } else { $Datebookedincountreturnwarr[$Datebookedin] = (isset($Datebookedincountreturnwarr[$Datebookedin]) ? $Datebookedincountreturnwarr[$Datebookedin] : 0) + 1; } } $recieptstable = ""; $recieptstablecsv = " Date Recieved in, Reference Code, Qty Booked in, Customer Returns, In Warranty, Total\n "; $mdrecieptstable = "\n## Receipts\n\n| Date Received in | Reference Code | Qty Booked in | Out Warranty | In Warranty | Total |\n|---|---|---:|---:|---:|---:|\n"; $grand_total=0; foreach ($batchin as $Datebookedin => $total) { $displaydate = date("d M Y",$Datebookedin); $reproto = (isset($Datebookedincountreturn[$Datebookedin]) ? $Datebookedincountreturn[$Datebookedin] : 0) + (isset($Datebookedincountreturnwarr[$Datebookedin]) ? $Datebookedincountreturnwarr[$Datebookedin] : 0); if ($reproto==0) { $reproto=''; } $reprotogrand = $reprotogrand + (int)$reproto; if ($grand_total > 0) { $reptopercent = ($reprotogrand / $grand_total) * 100; } else { $reptopercent = 0; } $reptopercent=number_format($reptopercent,2); $recieptstable .= ""; $dbcr = isset($Datebookedincountreturn[$Datebookedin]) ? $Datebookedincountreturn[$Datebookedin] : 0; $dbcrw = isset($Datebookedincountreturnwarr[$Datebookedin]) ? $Datebookedincountreturnwarr[$Datebookedin] : 0; $recieptstablecsv .= " $displaydate, $Datebookedin, $total, $dbcr, $dbcrw, $reproto\n "; $mdrecieptstable .= "| $displaydate | $Datebookedin | $total | $dbcr | $dbcrw | " . ($reproto === '' ? '' : $reproto) . " |\n"; $csvsalesdata .= "$Datebookedin $displaydate $total Retruns \n"; $grand_total = $grand_total + $total; } $salesdata .= "Grand Total $grand_total
"; $csvsalesdata .= "Grand Total $grand_total \n"; $mdsalesdata .= "- **Grand Total**: **$grand_total**\n"; $recieptstable .= "
Date Recieved in Reference Code Qty Booked in Out Warranty In Warranty Total
$displaydate $Datebookedin $total " . (isset($Datebookedincountreturn[$Datebookedin]) ? $Datebookedincountreturn[$Datebookedin] : 0) . " " . (isset($Datebookedincountreturnwarr[$Datebookedin]) ? $Datebookedincountreturnwarr[$Datebookedin] : 0) . " $reproto
Total $grand_total $reprotogrand ($reptopercent %)
"; $recieptstablecsv .= ",Total,$grand_total,,,\n\n"; $mdrecieptstable .= "| | **Total** | **$grand_total** | | | **$reprotogrand ($reptopercent %)** |\n"; $sql="SELECT viamed_customer_repairs_details.id as id , viamed_customer_repairs_details.fault_code as actual_fault , viamed_customer_repairs_details.stock_ref as stock_ref, viamed_customer_repairs_details.status as status FROM viamed_customer_repairs_details, stockgroup where stockgroup.groupcode=? and viamed_customer_repairs_details.stock_ref=stockgroup.stk_ref and viamed_customer_repairs_details.stock_ref<>'' and viamed_customer_repairs_details.status<>'' and viamed_customer_repairs_details.status<>'Quote Sent' and viamed_customer_repairs_details.status<>'Unfinished' and viamed_customer_repairs_details.status_date>? and viamed_customer_repairs_details.status_datefetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $stkref=$row['stock_ref']; $actual_fault_link=md5($row['actual_fault']); $uselink=$actual_fault_link.$stkref; $liststock[$stkref]=1; } // Memory fix: only need id + bookedin; stream rather than fetchAll. $datereturned = array(); $sql = "SELECT id, bookedin FROM viamed_customer_repairs_header"; $stmt = pdoquery($sql); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $datereturned[$row['id']] = $row['bookedin']; } $stmt->closeCursor(); unset($stmt); $csvoutputbiglist = "\nBreak Down Parts To Fault Code - NOT Time Limited \"Stock Ref\""; $biglist = "

Break Down Parts To Fault Code - NOT Time Limited

"; $mdoutputbiglist = "\n## Break Down Parts To Fault Code (NOT Time Limited)\n\n"; if ($wehavesupplier==1) { $biglist .= ""; $csvoutputbiglist .=",Supplier Ref"; $mdoutputbiglist .= "| Stock Ref | Supplier Ref | Description | Fault Code | Cleaned Fault Code | True Fault? | Qty |\n|---|---|---|---|---|---|---:|\n"; } else { $mdoutputbiglist .= "| Stock Ref | Description | Fault Code | Cleaned Fault Code | True Fault? | Qty |\n|---|---|---|---|---|---:|\n"; } $biglist .=""; $csvoutputbiglist .=",Description,Fault Code,Cleaned Fault Code,True Fault?,Qty\n "; $sql="SELECT count(*) as total, viamed_customer_repairs_details.id as id , viamed_customer_repairs_details.repair_header_id as repair_header_id , viamed_customer_repairs_details.fault_code as actual_fault , viamed_customer_repairs_details.stock_ref as stock_ref, viamed_customer_repairs_details.status as status, viamed_customer_repairs_details.status_date as status_date FROM viamed_customer_repairs_details, stockgroup where stockgroup.groupcode=? and viamed_customer_repairs_details.stock_ref=stockgroup.stk_ref and viamed_customer_repairs_details.stock_ref<>'' and viamed_customer_repairs_details.status<>'' and viamed_customer_repairs_details.status<>'Quote Sent' and viamed_customer_repairs_details.status<>'Unfinished' and viamed_customer_repairs_details.status_date>? and viamed_customer_repairs_details.status_date"; //print "$sql
"; $stmt=pdoquery($sql, array($group, $starttimeuni, $endtimeuni)); $rows=$stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $headerid=$row['repair_header_id']; $statusdate=$datereturned[$headerid]; if ( $statusdate > $starttimeuni and $statusdate < $endtimeuni and $statusdate<>'' and $statusdate<>0) { $stkref=$row['stock_ref']; $actual_fault=$row['actual_fault']; $actual_fault_link=md5($actual_fault); $cleanfault = isset($cleanlink[$actual_fault_link]) ? $cleanlink[$actual_fault_link] : ''; $cleanstocklink = md5($stkref."ZXZZ".$cleanfault); $countstockclean[$cleanstocklink] = (isset($countstockclean[$cleanstocklink]) ? $countstockclean[$cleanstocklink] : 0) + $row['total']; if (isset($faulttypeclean[$cleanfault]) && $faulttypeclean[$cleanfault]<>'') { $biglist .= ""; $mdoutputbiglist .= "| ".AI_md_cell_escape($stkref)." | ".AI_md_cell_escape($supRefVal)." | ".AI_md_cell_escape($supDescVal)." "; } else { $biglist .= "$stkref"; $csvoutputbiglist .= "\"$stkref\",$stkdescVal"; $mdoutputbiglist .= "| ".AI_md_cell_escape($stkref)." | ".AI_md_cell_escape($stkdescVal)." "; } if ($totalunitssold > 0) { $dpercfail=number_format((($row['total']/$totalunitssold)*100), 2, '.', ''); } else { $dpercfail = 'N/A'; } $biglist .= ""; $csvoutputbiglist .=",$actual_fault,$cleanfault," . (isset($faulttypeclean[$cleanfault]) ? $faulttypeclean[$cleanfault] : '') . ",".$row['total']."\n"; $mdoutputbiglist .= "| ".AI_md_cell_escape($actual_fault)." | ".AI_md_cell_escape($cleanfault)." | ".AI_md_cell_escape(isset($faulttypeclean[$cleanfault]) ? $faulttypeclean[$cleanfault] : '')." | ".$row['total']." |\n"; } } } $biglist .= "
Stock RefSupplier RefDescriptionFault CodeCleaned Fault CodeTrue Fault?Qty
"; $stkdescVal = isset($stkdesc[$stkref]) ? $stkdesc[$stkref] : ''; $supDescVal = (isset($supplierdescription[$stkref]) && $supplierdescription[$stkref] != '') ? $supplierdescription[$stkref] : $stkdescVal; $supDescCsvVal = (isset($supplierdescriptioncsv[$stkref]) && $supplierdescriptioncsv[$stkref] != '') ? $supplierdescriptioncsv[$stkref] : $stkdescVal; $supRefVal = isset($supplierreference[$stkref]) ? $supplierreference[$stkref] : ''; if ($wehavesupplier==1) { $csvoutputbiglist .= "\"$stkref\",$supRefVal,$supDescCsvVal"; $biglist .= "$stkref$supRefVal$supDescVal$stkdescVal$actual_fault$cleanfault" . (isset($faulttypeclean[$cleanfault]) ? $faulttypeclean[$cleanfault] : '') . "".$row['total']."
"; $csvoutputbiglistuniquefaults = "\n\nUnique Faults QA Demo & Customer $displaytimeframe\nCleaned Fault"; $uniquefaults = "

Unique Faults QA , Demo & Customer $displaytimeframe

"; $mdoutputbiglistuniquefaults = "\n## Unique Faults QA, Demo & Customer ($displaytimeframe)\n\n| Cleaned Fault"; $mdoutputbiglistuniquefaults_sep = "|---"; foreach ($liststock as $stkref=>$value) { $csvoutputbiglistuniquefaults .= ",$stkref " . (isset($liststock[$stkref]) ? $liststock[$stkref] : 0); $uniquefaults .= ""; $mdoutputbiglistuniquefaults .= " | ".AI_md_cell_escape($stkref); $mdoutputbiglistuniquefaults_sep .= "|---:"; } $csvoutputbiglistuniquefaults .= "\n"; $uniquefaults .= ""; $mdoutputbiglistuniquefaults .= " |\n".$mdoutputbiglistuniquefaults_sep."|\n"; asort($cleanlink); foreach ($cleanlink as $key => $value) { $totalline=0; $clean=md5($value); $uniquefaultsline=''; $csvoutputbiglistuniquefaultsline=''; $mdoutputbiglistuniquefaultsline=''; if ((!isset($haveshow[$clean]) || $haveshow[$clean]=='') && (isset($faulttypeclean[$value]) && $faulttypeclean[$value]=='Fault')) { $uniquefaultsline .= ""; $uniquefaultsline .= ""; foreach ($liststock as $stkref=>$value2) { $cleanstocklink=md5($stkref."ZXZZ".$value); $csvoutputbiglistuniquefaultsline .= ","; $uniquefaultsline .= ""; $mdoutputbiglistuniquefaultsline .= " | ".$cellval; } $uniquefaultsline .= ""; $csvoutputbiglistuniquefaultsline .= "\n"; $mdoutputbiglistuniquefaultsline .= " |\n"; if ($totalline > 0 ) { $uniquefaults .= $uniquefaultsline; $csvoutputbiglistuniquefaults .= $csvoutputbiglistuniquefaultsline; $mdoutputbiglistuniquefaults .= $mdoutputbiglistuniquefaultsline; $haveshow[$clean]=1; } } } $uniquefaults .= "
Cleaned Fault$stkref
"; $uniquefaultsline .= "
"; $csvoutputbiglistuniquefaultsline .= "$value"; $mdoutputbiglistuniquefaultsline .= "| ".AI_md_cell_escape($value); $uniquefaultsline .= "		$value"; $countstockclean[$cleanstocklink] = isset ( $countstockclean[$cleanstocklink] ) ? $countstockclean[$cleanstocklink] : 0 ; $cellval = ''; if ($countstockclean[$cleanstocklink] > 0 ) { $totalline=$totalline+$countstockclean[$cleanstocklink]; $csvoutputbiglistuniquefaultsline .= (isset($countstockclean[$cleanstocklink]) ? $countstockclean[$cleanstocklink] : 0); if ($totalunitssold > 0) { $dpecnum = (($countstockclean[$cleanstocklink] / $totalunitssold) * 100); $dpercfail = number_format($dpecnum, 2, '.', ''); } else { $dpercfail = 0; } $cellval = (isset($countstockclean[$cleanstocklink]) ? $countstockclean[$cleanstocklink] : 0); $uniquefaultsline .= $cellval . " "; } $uniquefaultsline .= "
"; $csvoutputbiglistuniquefaults .= "\n"; // Memory fix: only need id + bookedin; stream rather than fetchAll. $datebookedin = array(); $sql = "SELECT id, bookedin FROM viamed_customer_repairs_header"; $stmt = pdoquery($sql); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $datebookedin[$row['id']] = $row['bookedin']; } $stmt->closeCursor(); unset($stmt); $sql="SELECT count(*) as total, viamed_customer_repairs_details.id as id , viamed_customer_repairs_details.barcode as barcode , viamed_customer_repairs_details.repair_header_id as repair_header_id , viamed_customer_repairs_details.fault_code as actual_fault , viamed_customer_repairs_details.stock_ref as stock_ref, viamed_customer_repairs_details.status as status FROM viamed_customer_repairs_details, stockgroup where stockgroup.groupcode=? and viamed_customer_repairs_details.stock_ref=stockgroup.stk_ref and viamed_customer_repairs_details.stock_ref<>'' and viamed_customer_repairs_details.status<>'' and viamed_customer_repairs_details.status<>'Quote Sent' and viamed_customer_repairs_details.status<>'Unfinished' and viamed_customer_repairs_details.status<>'End Of Life' and viamed_customer_repairs_details.status_date>? and viamed_customer_repairs_details.status_date Search Valid Barcodes :
$sql
"; $stmt=pdoquery($sql, array($group, $starttimeuni, $endtimeuni)); $rows=$stmt->fetchAll(PDO::FETCH_ASSOC); $barcodeserialsearch = "(1=0 "; $barcodesupretsearch = "(1=0 "; $barcodeanswersearch = "(1=0 "; foreach ($rows as $row) { $stkref=$row['stock_ref']; $repair_header_id=$row['repair_header_id']; $actual_fault=$row['actual_fault']; $barcode=$row['barcode']; if ($barcode<>'') { $barcodeserialsearch .= " OR id='$barcode'"; $barcodesupretsearch .= " OR serialid='$barcode'"; $barcodeanswersearch .= " OR serialnumber_answers.barcode='$barcode'"; } $actual_fault_link=md5($actual_fault); $cleanfault = isset($cleanlink[$actual_fault_link]) ? $cleanlink[$actual_fault_link] : ''; $barcodefault[$barcode]=$cleanfault; //print "REPD $stkref $barcode $actual_fault $cleanfault
"; $barcodereturned[$barcode]=$datebookedin[$repair_header_id]; // $datebookedin[$repair_header_id] $datereturnedmonthyear=$datebookedin[$repair_header_id]; $datereturnedmonthyearcleanfaultlink=$cleanfault."ZXZ".$datereturnedmonthyear; $qtyreturned[$datereturnedmonthyearcleanfaultlink] = (isset($qtyreturned[$datereturnedmonthyearcleanfaultlink]) ? $qtyreturned[$datereturnedmonthyearcleanfaultlink] : 0) + 1; } $barcodeserialsearch .= " ) "; $barcodesupretsearch .= " ) "; $barcodeanswersearch .= " ) "; // Originally: SELECT * FROM supplier_returns_details ... — the loop body was empty/dead. // Replaced with a count-only existence check to avoid loading every column. $sql = "SELECT COUNT(*) AS tot FROM supplier_returns_details WHERE $barcodesupretsearch"; $stmt = pdoquery($sql); $tmprow = $stmt->fetch(PDO::FETCH_ASSOC); $numsen = isset($tmprow['tot']) ? (int)$tmprow['tot'] : 0; $stmt->closeCursor(); unset($stmt); //$testreport .= "
Barcode Search:
$barcodeserialsearch
"; $sql = "SELECT id, Stock_ref, Dateout, Datebookedin FROM Serialnumbers WHERE $barcodeserialsearch AND Invoicenumber NOT LIKE 'Used%' AND Ordernumber <> 'o' AND Dateout > '1000'"; $stmt = pdoquery($sql); $numsen = 0; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $numsen++; $barcode = $row['id']; $dateoutinvoice[$barcode] = date("D M Y", $row['Dateout']); $stk = $row['Stock_ref']; $countofstk[$stk] = (isset($countofstk[$stk]) ? $countofstk[$stk] : 0) + 1; // is it invoiced or was it found in QA? if ($row['Dateout'] > 0) { $startdate=$row['Dateout']; } else { $startdate=$row['Datebookedin']; } $cleancode = isset($barcodefault[$barcode]) ? $barcodefault[$barcode] : ''; $breturned = isset($barcodereturned[$barcode]) ? $barcodereturned[$barcode] : 0; if (isset($faulttypeclean[$cleancode]) && $faulttypeclean[$cleancode]=='Fault') { $year1 = date('Y', $startdate); $year2 = date('Y', $breturned); $month1 = date('m', $startdate); $month2 = date('m', $breturned); $diff = (($year2 - $year1) * 12) + ($month2 - $month1); if ($diff > 48) { $diff='48+'; //$testreport .= "$row[id] $startdate $barcode $barcodereturned[$barcode]
"; } $monthslist[$diff]=1; $stklist[$stk]=$stk; $diffandcleancodelink=$diff."DIF".$cleancode; $diffandstklink=$diff."DsF".$stk; $stkcount[$diffandstklink] = (isset($stkcount[$diffandstklink]) ? $stkcount[$diffandstklink] : 0) + 1; $cleantypecountmonths[$diffandcleancodelink] = (isset($cleantypecountmonths[$diffandcleancodelink]) ? $cleantypecountmonths[$diffandcleancodelink] : 0) + 1; // unremark and display dates and barcodes //$cleantypecountmonthsid[$diffandcleancodelink] .= "$row[id] $dateoutinvoice[$barcode]
"; //print "$barcode - $barcodefault[$barcode]
"; } //print "$barcode - $barcodefault[$barcode] Invoice out $startdate Returned $barcodereturned[$barcode] invoice Approx Months $diff
"; } $csvoutputmonthtofailure="\n"; $monthtofailure = ""; $mdoutputmonthtofailure = "\n## Months to Failure From Invoice (Customers) — $displaytimeframe\n\nFrom Invoice to Customer to Booked into Returns system (Customers)\n\n"; $csvoutputmonthtofailure .= "Months to Failure From Invoice Customers \n$displaytimeframe From Invoice to Customer to Booked into Returns system (Customers)\n"; $monthtofailure .= "

Months to Failure From Invoice Customers
$displaytimeframe

From Invoice to Customer to Booked into Returns system (Customers)"; ksort($monthslist); $monthtofailure .= ""; $csvoutputmonthtofailure .= "Clean Code"; $mdoutputmonthtofailure .= "| Clean Code"; $mdoutputmonthtofailure_sep = "|---"; foreach ($monthslist as $months=>$value) { if ($months>-1) { $monthtofailure .= ""; $csvoutputmonthtofailure .= ",$months Months"; $mdoutputmonthtofailure .= " | $months Months"; $mdoutputmonthtofailure_sep .= "|---:"; } } $csvoutputmonthtofailure .= "\n"; $monthtofailure .= ""; $mdoutputmonthtofailure .= " |\n".$mdoutputmonthtofailure_sep."|\n"; ksort($cleanlink); foreach ($cleanlink as $key => $cleancode) { $csvoutputmonthtofailureline=""; $monthtofailureline=""; $mdoutputmonthtofailureline=""; $tempcount=0; if ((isset($faulttypeclean[$cleancode]) && $faulttypeclean[$cleancode]=='Fault') && (!isset($haveshow2[$cleancode]) || $haveshow2[$cleancode]=='')) { $monthtofailureline .= ""; $csvoutputmonthtofailureline .= "$cleancode"; $mdoutputmonthtofailureline .= "| ".AI_md_cell_escape($cleancode); $monthtofailureline .= ""; foreach ($monthslist as $months=>$value2) { if ($months>-1) { $diffandcleancodelink=$months."DIF".$cleancode; $monthtofailureline .= ""; $tempcount = $tempcount + (isset($cleantypecountmonths[$diffandcleancodelink]) ? $cleantypecountmonths[$diffandcleancodelink] : 0); $csvoutputmonthtofailureline .= "," . (isset($cleantypecountmonths[$diffandcleancodelink]) ? $cleantypecountmonths[$diffandcleancodelink] : 0); $mdoutputmonthtofailureline .= " | " . (isset($cleantypecountmonths[$diffandcleancodelink]) ? $cleantypecountmonths[$diffandcleancodelink] : 0); } } $csvoutputmonthtofailureline .= "\n"; $monthtofailureline .= ""; $mdoutputmonthtofailureline .= " |\n"; if ($tempcount > 0 ) { $csvoutputmonthtofailure .= $csvoutputmonthtofailureline; $monthtofailure .= $monthtofailureline; $mdoutputmonthtofailure .= $mdoutputmonthtofailureline; $haveshow2[$cleancode]=1; } } } $csvoutputmonthtofailurestk = "\nStock References Months to Failure From Invoice Customers \n$displaytimeframe From Invoice to Customer to Booked into Returns system (Customers)\n"; $mdoutputmonthtofailurestk = "\n## Stock References — Months to Failure From Invoice (Customers) — $displaytimeframe\n\nFrom Invoice to Customer to Booked into Returns system (Customers)\n\n"; $monthtofailurestk .= "

Stock References Months to Failure From Invoice Customers
$displaytimeframe

From Invoice to Customer to Booked into Returns system (Customers)"; // END Of calculations lets print to screen maybe $monthtofailurestk .= "
Clean Code$months Months
$cleancode" . (isset($cleantypecountmonths[$diffandcleancodelink]) ? $cleantypecountmonths[$diffandcleancodelink] : 0) . " " . (isset($cleantypecountmonthsid[$diffandcleancodelink]) ? $cleantypecountmonthsid[$diffandcleancodelink] : '') . "
"; $csvoutputmonthtofailurestk .= "Stock Reference"; $mdoutputmonthtofailurestk .= "| Stock Reference"; $mdoutputmonthtofailurestk_sep = "|---"; foreach ($monthslist as $months=>$value) { if ($months>-1) { $monthtofailurestk .= ""; $csvoutputmonthtofailurestk .= ",$months Months"; $mdoutputmonthtofailurestk .= " | $months Months"; $mdoutputmonthtofailurestk_sep .= "|---:"; } } $monthtofailurestk .= ""; $csvoutputmonthtofailurestk .= "\n"; $mdoutputmonthtofailurestk .= " |\n".$mdoutputmonthtofailurestk_sep."|\n"; foreach ($stklist as $stk) { $monthtofailurestk .= ""; $csvoutputmonthtofailurestk .= "$stk"; $mdoutputmonthtofailurestk .= "| ".AI_md_cell_escape($stk); foreach ($monthslist as $months=>$value2) { $diffandstklink=$months."DsF".$stk; $monthtofailurestk .= ""; $csvoutputmonthtofailurestk .= "," . (isset($stkcount[$diffandstklink]) ? $stkcount[$diffandstklink] : 0); $mdoutputmonthtofailurestk .= " | " . (isset($stkcount[$diffandstklink]) ? $stkcount[$diffandstklink] : 0); } $csvoutputmonthtofailurestk .= "\n"; $monthtofailurestk .= ""; $mdoutputmonthtofailurestk .= " |\n"; } $monthtofailurestk .= "
Reference$months Months
$stk" . (isset($stkcount[$diffandstklink]) ? $stkcount[$diffandstklink] : 0) . "
"; // FOLLOWING NOT WORKING YET $question_search = "(1=0 "; $sql = "SELECT serialnumber_questions.id FROM serialnumber_questions, stockgroup WHERE serialnumber_questions.stock_ref = stockgroup.stk_ref AND serialnumber_questions.question_type = 'QA' AND serialnumber_questions.special_function = 'Yesno' AND serialnumber_questions.question_text = 'Pass' AND stockgroup.groupcode = ?"; $stmt = pdoquery($sql, array($group)); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $questionid = $row['id']; $question_search .= " OR questionid='$questionid'"; } $stmt->closeCursor(); unset($stmt); $question_search .= ") "; $sql="SELECT count(*) as tot,stock_ref FROM serialnumber_answers where $barcodeanswersearch and $question_search and answer='No' group by barcode"; //print "$sql
"; $stmt=pdoquery($sql); $rows=$stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $row) { $ansstk=$row['stock_ref']; $totalfailqa[$ansstk] = $row['tot']; } $qafailhtml = ''; $qafailcsv = ''; $qafailmd = ''; foreach ($totalfailqa as $stkref => $totalfailed) { $qafailhtml .= "$stkref Failed $totalfailed
"; $qafailcsv .= "$stkref,Failed,$totalfailed\n"; $qafailmd .= "- **".AI_md_cell_escape($stkref)."** Failed: $totalfailed\n"; } if (!empty($qafailmd)) { $qafailmd = "\n## QA Fails\n\n".$qafailmd; } if ($csv=='md') { // Markdown report mode (csv=md or md=1). $mdfilename = "groupstats_".preg_replace('/[^A-Za-z0-9_\-]+/', '_', $group).".md"; header("Content-Type: text/plain; charset=utf-8"); header("Content-Disposition: inline; filename=\"".$mdfilename."\""); header("Pragma: no-cache"); header("Expires: 0"); print "# ".$group." — Returns Statistics\n\n"; print "_Window: ".$displaytimeframe."_\n\n"; if ($wehavesupplier==1) { // Try to surface the supplier name when we have one resolved. if (function_exists('getcompanyname') && $companyid != '') { print "**Supplier:** ".getcompanyname($companyid)."\n\n"; } } else { print "**Suppliers:** Multiple\n\n"; } print "## Sales Summary\n\n"; print $mdsalesdata."\n"; print $mdrecieptstable."\n"; print $mdoutputbiglistuniquefaults."\n"; print $mdoutputmonthtofailure."\n"; print $mdoutputmonthtofailurestk."\n"; print $mdoutputbiglist."\n"; if (!empty($qafailmd)) { print $qafailmd."\n"; } print "\n_End Of Report_\n"; } elseif ($csv=='') { print "
"; if ($wehavesupplier==1) { include ("../crm/showaddress.php"); //print "We Know the Supplier
"; } else { print "Multiple Suppliers
"; } // rethinking what We have Header DAta HEre print "

$group

"; //print "Total Units Sold $totalunitssold
"; $monthtofailure .= ""; $monthtofailure .= "
"; //print $testreport ; print $selectsupplier; print $salesdata; print $recieptstable; print $uniquefaults; print $monthtofailure; print $monthtofailurestk; print $biglist; print $qafailhtml; } else { $csvoutput .= " End Of Report"; header("Content-Type: application/csv"); header("Content-Disposition: attachment; filename=Statistics.csv"); header("Pragma: no-cache"); header("Expires: 0"); print $csvsalesdata; print $recieptstablecsv; print $csvoutputbiglistuniquefaults; print $csvoutputmonthtofailure; print $csvoutputmonthtofailurestk; print $csvoutputbiglist; print $qafailcsv; //print $csvoutput; } ?>