<? include('includes/main.php'); ?>
<script language="JavaScript">
var calDateFormat='yyyy-MM-DD';
</script>
<script language="JavaScript" src="js/calendar.js"></script>
<? //arinvoicesum.php
echo texttitle('Invoice Summary - '.$companyname);
if ($report) { //show summary
unset($openstr);
if ($open) $openstr=' and arinvoice.status<2';
unset($datestr);
if ($bgdate) $datestr.=' and arinvoice.invoicedate>='.sqlprep($bgdate);
if ($eddate) $datestr.=' and arinvoice.invoicedate<='.sqlprep($eddate);
if ($order==1) { //invoice number
$orderstr.=' order by arinvoice.invoicenumber';
} elseif($order==2) { //customer
$orderstr.=' order by company.companyname, arinvoice.invoicenumber';
} elseif($order==3) { //salesman
$orderstr.=' order by arinvoice.salesmanid, arinvoice.invoicenumber';
};
$recordSet = $conn->Execute("select arinvoice.invoicenumber,count(arinvoicedetail.id),sum(arinvoicedetailcost.cost),sum(arinvoicedetail.totalprice),sum(arinvoicetaxdetail.taxamount),arinvoice.shipcost,arinvoice.invoicetotal,sum(arinvoicepaymentdetail.amount),arinvoice.duedate,arinvoice.status,company.companyname,salescomp.companyname,customer.id,salescomp.id from arinvoice cross join arinvoicedetail left join arinvoicedetailcost on arinvoice.id=arinvoicedetailcost.invoiceid left join arinvoicetaxdetail on arinvoice.id=arinvoicetaxdetail.invoiceid left join arinvoicepaymentdetail on arinvoicepaymentdetail.invoiceid=arinvoice.id left join company on company.id=arinvoice.orderbycompanyid left join salesman on arinvoice.salesmanid=salesman.id left join company as salescomp on salesman.companyid=salescomp.id left join customer on customer.companyid=arinvoice.orderbycompanyid where arinvoice.id=arinvoicedetail.invoiceid and arinvoice.cancel=0 and arinvoice.gencompanyid=".sqlprep($active_company).$openstr.$datestr." group by arinvoice.id,arinvoice.invoicenumber,arinvoice.shipcost,arinvoice.invoicetotal,arinvoice.duedate,arinvoice.status,company.companyname,salescomp.companyname,customer.id,salescomp.id".$orderstr);
if (!$recordSet||$recordSet->EOF) die(texterror('No matching invoices found.'));
echo '<table border="1">';
if ($order==3) {
echo '<tr><th colspan="12">Salesman</th></tr>';
} else {
echo '<tr><td></td><th colspan="11">Customer</th></tr>';
};
echo '<tr><th>Invoice #</th><th># Line Items</th><th>Cost</th><th>Subtotal</th><th>Tax</th><th>Shipping</th><th>Total</th><th>Paid</th><th>Balance</th><th>Profit</th><th>Due Date</th><th>Status</th></tr>';
if ($report>1) {
echo '<tr><td colspan="2"></td><th colspan="3">Description</th><th colspan="2">Qty</th><th>Price</th><th>Sales GL Account</th><th colspan="4"></th></tr>';
$recordSet2 = $conn->Execute("select arinvoice.invoicenumber,arinvoicedetail.description, arinvoicedetail.qty, unitname.unitname, arinvoicedetail.qty/arinvoicedetail.qtyunitperpriceunit*arinvoicedetail.priceach, glaccount.name, glaccount.description from arinvoice,arinvoicedetail left join unitname on unitname.id=arinvoicedetail.qtyunitnameid left join glaccount on glaccount.id=arinvoicedetail.glaccountid left join company on company.id=arinvoice.orderbycompanyid where arinvoice.id=arinvoicedetail.invoiceid and arinvoice.cancel=0 and arinvoice.gencompanyid=".sqlprep($active_company).$openstr.$datestr." group by arinvoice.id, arinvoicedetail.id".$orderstr);
};
if ($report>2) {
echo '<tr><th colspan="2"></th><th>Cost</th><th colspan="4">Cost GL Account</th><th colspan="5"></th></tr>';
$recordSet3 = $conn->Execute("select arinvoice.invoicenumber,arinvoicedetailcost.cost, glaccount.name, glaccount.description from arinvoice,arinvoicedetailcost left join glaccount on arinvoicedetailcost.costglaccountid=glaccount.id left join company on company.id=arinvoice.orderbycompanyid where arinvoice.id=arinvoicedetailcost.invoiceid and arinvoice.cancel=0 and arinvoice.gencompanyid=".sqlprep($active_company).$openstr.$datestr." group by arinvoice.id, arinvoicedetailcost.id ".$orderstr.", arinvoicedetailcost.cost");
};
while (!$recordSet->EOF) {
if ($order==3&&($recordSet->fields[13]<>$oldsalesmanid||!$oldsalesmanid)) {
if ($firsttime==1&&$recordSet->fields[13]<>$oldsalesmanid) {
if ($salescost==0) {
$properc='100.0';
} else {
$properc=num_format(($salesst/$salescost)*100,1);
};
$oldsales=$oldsalesman;
if (trim($oldsales)=="") {
$oldsales="??unknown??" ;
};
echo '<tr><td>'.$oldsales.' Total</td><td>'.$saleslineitem.'</td><td>'.CURRENCY_SYMBOL.num_format($salescost,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salesst,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salestax,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salesship,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salesal,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salespd,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salesal-$salespd,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salesst-$salescost,PREFERRED_DECIMAL_PLACES).' ('.$properc.'%)</td><td colspan="2"></td></tr>';
unset($saleslineitem);
unset($salescost);
unset($salesst);
unset($salestax);
unset($salesship);
unset($salesal);
unset($salespd);
};
if ($recordSet->fields[13]<>$oldsalesmanid||!$firsttime) {
$sales=$recordSet->fields[11];
if (trim($sales)=="") $sales="??unknown??";
echo '<tr><th colspan="12">'.$sales.'</th></tr>';
$oldsalesman=$recordSet->fields[11];
$oldsalesmanid=$recordSet->fields[13];
$firsttime=1;
};
};
if ($order==3) {
$saleslineitem+=$recordSet->fields[1];
$salescost+=$recordSet->fields[2];
$salesst+=$recordSet->fields[3];
$salestax+=$recordSet->fields[4];
$salesship+=$recordSet->fields[5];
$salesal+=$recordSet->fields[6];
$salespd+=$recordSet->fields[7];
};
if ($order==2&&$recordSet->fields[12]<>$oldcustomerid) {
if (isset($oldcustomerid)) {
if ($custcost==0) {
$properc='100.0';
} else {
$properc=num_format(($custst/$custcost)*100,1);
};
$oldcust=$oldcustomer;
if (trim($oldcust)=="") $oldcust="??unknown??";
echo '<tr><td>'.$oldcust.' Total</td><td>'.$custlineitem.'</td><td>'.CURRENCY_SYMBOL.num_format($custcost,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custst,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custtax,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custship,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custal,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custpd,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custal-$custpd,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custst-$custcost,PREFERRED_DECIMAL_PLACES).' ('.$properc.'%)</td><td colspan="2"></td></tr>';
unset($custlineitem);
unset($custcost);
unset($custst);
unset($custtax);
unset($custship);
unset($custal);
unset($custpd);
};
};
if ($order==2) {
$custlineitem+=$recordSet->fields[1];
$custcost+=$recordSet->fields[2];
$custst+=$recordSet->fields[3];
$custtax+=$recordSet->fields[4];
$custship+=$recordSet->fields[5];
$custal+=$recordSet->fields[6];
$custpd+=$recordSet->fields[7];
};
if ($order<>3&&$recordSet->fields[12]<>$oldcustomerid) {
$custurl=$recordSet->fields[12];
$cust=$recordSet->fields[10];
if (trim($cust)=="") $cust="??unknown??";
echo '<tr><td></td><th colspan="11"><a href="arinvoicerepcust.php?customerid='.$custurl.'">'.$cust.'</a></th></tr>';
$oldcustomer=$recordSet->fields[10];
$oldcustomerid=$recordSet->fields[12];
};
if ($recordSet->fields[2]==0) {
$properc='100.0';
} else {
$properc=num_format(($recordSet->fields[3]/$recordSet->fields[2])*100,1);
};
echo '<tr><th><a href="arinvoiceupd.php?invoicenumber='.$recordSet->fields[0].'">'.$recordSet->fields[0].'</a></th><td>'.$recordSet->fields[1].'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[2],PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[3],PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[4],PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[5],PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[6],PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[7],PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[6]-$recordSet->fields[7],PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet->fields[3]-$recordSet->fields[2],PREFERRED_DECIMAL_PLACES).' ('.$properc.'%)</td><td>'.$recordSet->fields[8].'</td><td>'.shstatus($recordSet->fields[9]).'</td></tr>';
$totinv++;
$totlineitem+=$recordSet->fields[1];
$totcost+=$recordSet->fields[2];
$totst+=$recordSet->fields[3];
$tottax+=$recordSet->fields[4];
$totship+=$recordSet->fields[5];
$total+=$recordSet->fields[6];
$totpd+=$recordSet->fields[7];
if ($report>1) {
while (!$recordSet2->EOF&&$recordSet2&&$recordSet2->fields[0]==$recordSet->fields[0]) {
echo '<tr><td colspan="2"></td><td colspan="3">'.$recordSet2->fields[1].'</td><td>'.$recordSet2->fields[2].'</td><td>'.$recordSet2->fields[3].'</td><td>'.CURRENCY_SYMBOL.num_format($recordSet2->fields[4],PREFERRED_DECIMAL_PLACES).'</td><td>'.$recordSet2->fields[5].' - '.$recordSet2->fields[6].'</td><td colspan="4"></td></tr>';
$recordSet2->MoveNext();
};
};
if ($report>2) {
while (!$recordSet3->EOF&&$recordSet3&&$recordSet3->fields[0]==$recordSet->fields[0]) {
echo '<tr><td colspan="2"></td><td>'.CURRENCY_SYMBOL.num_format($recordSet3->fields[1],PREFERRED_DECIMAL_PLACES).'</td><td colspan="4">'.$recordSet3->fields[2].' - '.$recordSet3->fields[3].'</td><td colspan="5"></td></tr>';
$recordSet3->MoveNext();
};
};
$recordSet->MoveNext();
};
if ($order==3) {
if ($salescost==0) {
$properc='100.0';
} else {
$properc=num_format(($salesst/$salescost)*100,1);
};
$oldsales=$oldsalesman;
if (trim($oldsales)=="") {
$oldsales="??unknown??" ;
};
echo '<tr><td>'.$oldsales.' Total</td><td>'.$saleslineitem.'</td><td>'.CURRENCY_SYMBOL.num_format($salescost,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salesst,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salestax,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salesship,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salesal,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salespd,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salesal-$salespd,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($salesst-$salescost,PREFERRED_DECIMAL_PLACES).' ('.$properc.'%)</td><td colspan="2"></td></tr>';
};
if ($order==2) {
if ($custcost==0) {
$properc='100.0';
} else {
$properc=num_format(($custst/$custcost)*100,1);
};
$oldcust=$oldcustomer;
if (trim($oldcust)=="") {
$oldcust="??unknown??" ;
};
echo '<tr><td>'.$oldcust.' Total</td><td>'.$custlineitem.'</td><td>'.CURRENCY_SYMBOL.num_format($custcost,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custst,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custtax,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custship,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custal,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custpd,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custal-$custpd,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($custst-$custcost,PREFERRED_DECIMAL_PLACES).' ('.$properc.'%)</td><td colspan="2"></td></tr>';
};
echo '<tr><td colspan="13"> </td></tr>';
if ($totcost==0) {
$properc='100.0';
} else {
$properc=num_format(($totst/$totcost)*100,1);
};
echo '<tr><td>Totals: '.$totinv.'</td><td>'.$totlineitem.'</td><td>'.CURRENCY_SYMBOL.num_format($totcost,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($totst,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($tottax,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($totship,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($total,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($totpd,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($total-$totpd,PREFERRED_DECIMAL_PLACES).'</td><td>'.CURRENCY_SYMBOL.num_format($totst-$totcost,PREFERRED_DECIMAL_PLACES).' ('.$properc.'%)</td><td colspan="2"></td></tr>';
echo '</table>';
} else {
$timestamp = time();
$date_time_array = getdate($timestamp);
$hours = $date_time_array["hours"];
$minutes = $date_time_array["minutes"];
$seconds = $date_time_array["seconds"];
$month = $date_time_array["mon"];
$day = $date_time_array["mday"];
$year = $date_time_array["year"];
$timestamp = mktime($hour, $minute, $second, $month, $day, $year);
$eddate=date("Y-m-d", $timestamp);
$timestamp = mktime($hour, $minute, $second, $month-1, $day, $year);
$bgdate=date("Y-m-d", $timestamp);
echo '<form action="arinvoicesum.php" method="post" name="mainform"><table>';
echo '<tr><td align="'.TABLE_LEFT_SIDE_ALIGN.'">Begin Period:</td><td><input type="text" name="bgdate" onchange="formatDate(this)" value="'.$bgdate.'" size="30"'.INC_TEXTBOX.'><a href="javascript:doNothing()" onclick="setDateField(document.mainform.begindate); top.newWin = window.open(\'calendar.html\',\'cal\',\'dependent=yes,width=210,height=230,screenX=200,screenY=300,titlebar=yes\')"><img src="'.IMAGE_DATE_LOOKUP.'" border="0" alt="Display Calendar"></a></td></tr>';
echo '<tr><td align="'.TABLE_LEFT_SIDE_ALIGN.'">End Period:</td><td><input type="text" name="eddate" onchange="formatDate(this)" value="'.$eddate.'" size="30"'.INC_TEXTBOX.'><a href="javascript:doNothing()" onclick="setDateField(document.mainform.enddate); top.newWin = window.open(\'calendar.html\',\'cal\',\'dependent=yes,width=210,height=230,screenX=200,screenY=300,titlebar=yes\')"><img src="'.IMAGE_DATE_LOOKUP.'" border="0" alt="Display Calendar"></a></td></tr>';
echo '<tr><td align="'.TABLE_LEFT_SIDE_ALIGN.'">Show Invoices:</td><td><select name="open"'.INC_TEXTBOX.'><option value="0">All<option value="1">Open</select></td></tr>';
echo '<tr><td align="'.TABLE_LEFT_SIDE_ALIGN.'">Report:</td><td><select name="report"'.INC_TEXTBOX.'><option value="1">Summary Totals By Invoice<option value="2">Line Item Detail<option value="3">GL Accounts/Amounts Detail</select></td></tr>';
echo '<tr><td align="'.TABLE_LEFT_SIDE_ALIGN.'">Order By:</td><td><select name="order"'.INC_TEXTBOX.'><option value="1">Invoice #<option value="2">Customer<option value="3">Salesperson</select></td></tr>';
// echo '<tr><td align="'.TABLE_LEFT_SIDE_ALIGN.'">Show Cost Analysis:</td><td><input type="checkbox" name="cost" value="1"'.INC_TEXTBOX.'></td></tr>';
echo '</table><input type="submit" value="Continue"></form>';
};
function shstatus($stat) {
switch ($stat) {
case 0:
return 'Unposted';
break;
case 1:
return 'Unpaid';
break;
case 2:
return 'Paid';
break;
};
};
?>
<? include('includes/footer.php'); ?>