1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
<?
require("inc/configure.php");
require("inc/global.php");
require("inc/checklogin.php");
if ($logged_in == 0) {
//echo 'Not logged in. <a href="login.php">Login</a>';
header('Window-target: _parent');
header('Location: login.php');
exit();
}
if (checkObject(21) <> 1){
die("Unauthorized Access");
exit();
}
?>
<?php
$frdate = $_POST['frdate'];
$todate = $_POST['todate'];
?>
<?php
header("Content-Type: application/vnd.ms-excel");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Disposition:filename=SalesProductReport.xls");
?>
<html>
<head>
<title><?=$systitle?></title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<!--<link rel="stylesheet" href="main.css" type="text/css">-->
</head>
<body>
<?php
$sql = "select
custnameeng
, majcatname
, subcatname
, b.prodno
, c.prodname
, username
, sum(qty) as qty
, sum(qty*b.cost) as cost
, sum(amount) as amount
from OM_INVOICE_HDR a
, OM_INVOICE_DTL b
, INV_PRODUCT c
, INV_CATEGORY_SUB d
, INV_CATEGORY_MAJ e
, SYS_USER f
where a.invoiceno = b.invoiceno
and b.prodid = c.prodid
and c.subcatid = d.subcatid
and d.majcatid = e.majcatid
and a.salesid = f.userid
and orderdate >= '$frdate'
and orderdate <= '$todate'
and a.status != 3
group by
custnameeng, majcatname, subcatname, username, b.prodno, c.prodname
order by custnameeng, username, majcatname, subcatname, b.prodid, c.prodname";
$result = mysql_query($sql);
?>
<table>
<tr><td class="content" colspan="8">Product Sales Report</td></tr>
<tr><td class="content">From date:</td><td class="content"><?=$frdate?></td><td colspan='6'></td></tr>
<tr><td class="content">To date:</td><td class="content"><?=$todate?></td><td colspan='6'></td></tr>
<tr><td class="content">Customer name</td>
<td class="content">Salesman</td>
<td class="content">Major category</td>
<td class="content">Sub category</td>
<td class="content">Product code</td>
<td class="content">Product name</td>
<td class="content">Qty</td>
<td class="content">Cost</td>
<td class="content">Amount</td></tr>
<?php
$tot = 0;
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
?>
<tr><td class="content"><?=$row{'custnameeng'}?></td>
<td class="content"><?=$row{'username'}?></td>
<td class="content"><?=$row{'majcatname'}?></td>
<td class="content"><?=$row{'subcatname'}?></td>
<td class="content"><?=$row{'prodno'}?></td>
<td class="content"><?=$row{'prodname'}?></td>
<td class="content"><?=$row{'qty'}?></td>
<td class="content"><?=$row{'cost'}?></td>
<td class="content"><?=$row{'amount'}?></td></tr>
<?php
$tot += $row['amount'];
}
?>
<tr><td class="content"></td>
<td class="content"></td>
<td class="content"></td>
<td class="content"></td>
<td class="content"></td>
<td class="content"></td>
<td class="content"></td>
<td class="content">Total:</td>
<td class="content"><?php echo $tot; ?></td></tr>
<?php
echo "</table>";
?>
</body>
</html>
|