1<?php 2 3 4include('includes/session.php'); 5$Title = _('Sales By Category By Item Inquiry'); 6include('includes/header.php'); 7 8echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/transactions.png" title="' . _('Sales Report') . '" alt="" />' . ' ' . _('Sales By Category By Item Inquiry') . '</p>'; 9echo '<div class="page_help_text">' . _('Select the parameters for the inquiry') . '</div><br />'; 10 11if (!isset($_POST['DateRange'])){ 12 /* then assume report is for This Month - maybe wrong to do this but hey better than reporting an error?*/ 13 $_POST['DateRange']='ThisMonth'; 14} 15 16echo '<form id="form1" action="' . htmlspecialchars($_SERVER['PHP_SELF'],ENT_QUOTES,'UTF-8') . '" method="post">'; 17echo '<div>'; 18echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; 19// stock category selection 20 $SQL="SELECT categoryid, 21 categorydescription 22 FROM stockcategory 23 ORDER BY categorydescription"; 24 $result1 = DB_query($SQL); 25 26echo '<table cellpadding="2" class="selection"> 27 <tr> 28 <td style="width:150px">' . _('In Stock Category') . ':</td> 29 <td><select name="StockCat">'; 30if (!isset($_POST['StockCat'])){ 31 $_POST['StockCat']='All'; 32} 33if ($_POST['StockCat']=='All'){ 34 echo '<option selected="selected" value="All">' . _('All') . '</option>'; 35} else { 36 echo '<option value="All">' . _('All') . '</option>'; 37} 38while ($myrow1 = DB_fetch_array($result1)) { 39 if ($myrow1['categoryid']==$_POST['StockCat']){ 40 echo '<option selected="selected" value="' . $myrow1['categoryid'] . '">' . $myrow1['categorydescription'] . '</option>'; 41 } else { 42 echo '<option value="' . $myrow1['categoryid'] . '">' . $myrow1['categorydescription'] . '</option>'; 43 } 44} 45echo '</select></td> 46 </tr> 47 <tr> 48 <th colspan="2" class="centre">' . _('Date Selection') . '</th> 49 </tr>'; 50 51if (!isset($_POST['FromDate'])){ 52 unset($_POST['ShowSales']); 53 $_POST['FromDate'] = Date($_SESSION['DefaultDateFormat'],mktime(1,1,1,Date('m')-12,Date('d')+1,Date('Y'))); 54 $_POST['ToDate'] = Date($_SESSION['DefaultDateFormat']); 55} 56echo '<tr> 57 <td>' . _('Date From') . ':</td> 58 <td><input type="text" class="date" name="FromDate" maxlength="10" size="11" value="' . $_POST['FromDate'] . '" /></td> 59 </tr>'; 60echo '<tr> 61 <td>' . _('Date To') . ':</td> 62 <td><input type="text" class="date" name="ToDate" maxlength="10" size="11" value="' . $_POST['ToDate'] . '" /></td> 63 </tr> 64</table> 65<br /> 66<div class="centre"> 67 <input tabindex="4" type="submit" name="ShowSales" value="' . _('Show Sales') . '" /> 68</div> 69</div> 70</form> 71<br />'; 72 73 74if (isset($_POST['ShowSales'])){ 75 $InputError=0; //assume no input errors now test for errors 76 if (!Is_Date($_POST['FromDate'])){ 77 $InputError = 1; 78 prnMsg(_('The date entered for the from date is not in the appropriate format. Dates must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error'); 79 } 80 if (!Is_Date($_POST['ToDate'])){ 81 $InputError = 1; 82 prnMsg(_('The date entered for the to date is not in the appropriate format. Dates must be entered in the format') . ' ' . $_SESSION['DefaultDateFormat'], 'error'); 83 } 84 if (Date1GreaterThanDate2($_POST['FromDate'],$_POST['ToDate'])){ 85 $InputError = 1; 86 prnMsg(_('The from date is expected to be a date prior to the to date. Please review the selected date range'),'error'); 87 } 88 $FromDate = FormatDateForSQL($_POST['FromDate']); 89 $ToDate = FormatDateForSQL($_POST['ToDate']); 90 91 $sql = "SELECT stockmaster.categoryid, 92 stockcategory.categorydescription, 93 stockmaster.stockid, 94 stockmaster.description, 95 SUM(price*(1-discountpercent)* -qty) as salesvalue, 96 SUM(-qty) as quantitysold, 97 SUM(standardcost * -qty) as cogs 98 FROM stockmoves INNER JOIN stockmaster 99 ON stockmoves.stockid=stockmaster.stockid 100 INNER JOIN stockcategory 101 ON stockmaster.categoryid=stockcategory.categoryid 102 WHERE (stockmoves.type=10 OR stockmoves.type=11) 103 AND show_on_inv_crds =1 104 AND trandate>='" . $FromDate . "' 105 AND trandate<='" . $ToDate . "' 106 GROUP BY stockmaster.categoryid, 107 stockcategory.categorydescription, 108 stockmaster.stockid, 109 stockmaster.description 110 ORDER BY stockmaster.categoryid, 111 salesvalue DESC"; 112 113 $ErrMsg = _('The sales data could not be retrieved because') . ' - ' . DB_error_msg(); 114 $SalesResult = DB_query($sql,$ErrMsg); 115 116 echo '<table cellpadding="2" class="selection">'; 117 118 echo'<tr> 119 <th>' . _('Item Code') . '</th> 120 <th>' . _('Item Description') . '</th> 121 <th>' . _('Qty Sold') . '</td> 122 <th>' . _('Sales Revenue') . '</th> 123 <th>' . _('COGS') . '</th> 124 <th>' . _('Gross Margin') . '</th> 125 <th>' . _('Avg Unit') . '<br/>' . _('Sale Price') . '</th> 126 <th>' . _('Avg Unit') . '<br/>' . _('Cost') . '</th> 127 <th>' . _('Margin %') . '</th> 128 </tr>'; 129 130 $CumulativeTotalSales = 0; 131 $CumulativeTotalQty = 0; 132 $CumulativeTotalCOGS = 0; 133 $CategorySales = 0; 134 $CategoryQty = 0; 135 $CategoryCOGS = 0; 136 $CategoryID =''; 137 138 while ($SalesRow=DB_fetch_array($SalesResult)) { 139 if ($CategoryID != $SalesRow['categoryid']) { 140 if ($CategoryID !='') { 141 //print out the previous category totals 142 echo '<tr> 143 <td colspan="2" class="number">' . _('Category Total') . '</td> 144 <td class="number">' . locale_number_format($CategoryQty,$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 145 <td class="number">' . locale_number_format($CategorySales,$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 146 <td class="number">' . locale_number_format($CategoryCOGS,$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 147 <td class="number">' . locale_number_format($CategorySales - $CategoryCOGS,$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 148 <td colspan="2"></td>'; 149 if ($CumulativeTotalSales !=0) { 150 echo '<td class="number">' . locale_number_format(($CategorySales-$CategoryCOGS)*100/$CategorySales,$_SESSION['CompanyRecord']['decimalplaces']) . '%</td>'; 151 } else { 152 echo '<td>' . _('N/A') . '</td>'; 153 } 154 echo '</tr>'; 155 156 //reset the totals 157 $CategorySales = 0; 158 $CategoryQty = 0; 159 $CategoryCOGS = 0; 160 161 } 162 echo '<tr> 163 <th colspan="9">' . _('Stock Category') . ': ' . $SalesRow['categoryid'] . ' - ' . $SalesRow['categorydescription'] . '</th> 164 </tr>'; 165 $CategoryID = $SalesRow['categoryid']; 166 } 167 168 echo '<tr class="striped_row"> 169 <td>' . $SalesRow['stockid'] . '</td> 170 <td>' . $SalesRow['description'] . '</td> 171 <td class="number">' . locale_number_format($SalesRow['quantitysold'],$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 172 <td class="number">' . locale_number_format($SalesRow['salesvalue'],$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 173 <td class="number">' . locale_number_format($SalesRow['cogs'],$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 174 <td class="number">' . locale_number_format($SalesRow['salesvalue']-$SalesRow['cogs'],$_SESSION['CompanyRecord']['decimalplaces']) . '</td>'; 175 if ($SalesRow['quantitysold']!=0) { 176 echo '<td class="number">' . locale_number_format(($SalesRow['salesvalue']/$SalesRow['quantitysold']),$_SESSION['CompanyRecord']['decimalplaces']) . '</td>'; 177 echo '<td class="number">' . locale_number_format(($SalesRow['cogs']/$SalesRow['quantitysold']),$_SESSION['CompanyRecord']['decimalplaces']) . '</td>'; 178 } else { 179 echo '<td>' . _('N/A') . '</td> 180 <td>' . _('N/A') . '</td>'; 181 } 182 if ($SalesRow['salesvalue']!=0) { 183 echo '<td class="number">' . locale_number_format((($SalesRow['salesvalue']-$SalesRow['cogs'])*100/$SalesRow['salesvalue']),$_SESSION['CompanyRecord']['decimalplaces']) . '%</td>'; 184 } else { 185 echo '<td>' . _('N/A') . '</td>'; 186 } 187 echo '</tr>'; 188 189 $CumulativeTotalSales += $SalesRow['salesvalue']; 190 $CumulativeTotalCOGS += $SalesRow['cogs']; 191 $CumulativeTotalQty += $SalesRow['quantitysold']; 192 $CategorySales += $SalesRow['salesvalue']; 193 $CategoryQty += $SalesRow['quantitysold']; 194 $CategoryCOGS += $SalesRow['cogs']; 195 196 } //loop around category sales for the period 197//print out the previous category totals 198 echo '<tr> 199 <td colspan="2" class="number">' . _('Category Total') . '</td> 200 <td class="number">' . locale_number_format($CategoryQty,$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 201 <td class="number">' . locale_number_format($CategorySales,$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 202 <td class="number">' . locale_number_format($CategoryCOGS,$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 203 <td class="number">' . locale_number_format($CategorySales - $CategoryCOGS,$_SESSION['CompanyRecord']['decimalplaces']) . '</td> 204 <td colspan="2"></td>'; 205 if ($CumulativeTotalSales !=0) { 206 echo '<td class="number">' . locale_number_format(($CategorySales-$CategoryCOGS)*100/$CategorySales,$_SESSION['CompanyRecord']['decimalplaces']) . '%</td>'; 207 } else { 208 echo '<td>' . _('N/A') . '</td>'; 209 } 210 echo '</tr> 211 <tr> 212 <th colspan="2" class="number">' . _('GRAND Total') . '</th> 213 <th class="number">' . locale_number_format($CumulativeTotalQty,$_SESSION['CompanyRecord']['decimalplaces']) . '</th> 214 <th class="number">' . locale_number_format($CumulativeTotalSales,$_SESSION['CompanyRecord']['decimalplaces']) . '</th> 215 <th class="number">' . locale_number_format($CumulativeTotalCOGS,$_SESSION['CompanyRecord']['decimalplaces']) . '</th> 216 <th class="number">' . locale_number_format($CumulativeTotalSales - $CumulativeTotalCOGS,$_SESSION['CompanyRecord']['decimalplaces']) . '</th> 217 <th colspan="2"></td>'; 218 if ($CumulativeTotalSales !=0) { 219 echo '<th class="number">' . locale_number_format(($CumulativeTotalSales-$CumulativeTotalCOGS)*100/$CumulativeTotalSales,$_SESSION['CompanyRecord']['decimalplaces']) . '%</th>'; 220 } else { 221 echo '<th>' . _('N/A') . '</th>'; 222 } 223 echo '</tr> 224 </table>'; 225 226} //end of if user hit show sales 227include('includes/footer.php'); 228?>
