Dear Experts,
I use the following program to generate the account report and the output is as follows:
Month Revenue estimated cost estimated GP
1 2005-11 1,600.00 1,260.00 340.00
2 2005-12 4,080.00 2,280.00 1,800.00
3 2006-01 2,640.00 660.00 1,980.00
Total: 8,320.00 4,200.00 4,200.00
There is a table cost_table which store the actual cost per month and the structure is as follows:
mysql> describe cost_table;
+-----------+-------------
+------+--
---+------
---+------
----------
| Field | Type | Null | Key | Default | Extra
+-----------+-------------
+------+--
---+------
---+------
----------
| id | int(11) | NO | PRI | NULL | auto_increment
| date | date | NO | | |
| item | varchar(30) | NO | | |
| costtype | varchar(30) | NO | | |
| amount | float | NO | | |
| remark | varchar(30) | NO | | |
| receiptno | varchar(30) | NO | | |
| bank | varchar(30) | NO | | |
| bankno | varchar(30) | NO | | |
| tutorname | varchar(30) | NO | | |
| test | float | NO | | |
| chequeno | varchar(30) | NO | | |
Please help to modify the following program to use the field amount from table cost_table above to replace the field salary from table course. I want the new program below the original one and create some more calculation as follows:
Month Revenue actual cost actual GP
1 2005-11 1,600.00 1,260.00 340.00
2 2005-12 4,080.00 2,280.00 1,800.00
3 2006-01 2,640.00 660.00 1,980.00
Total: 8,320.00 4,200.00 4,200.00
Actual monthly GP: XXXX
(it should be equal to total actual GP divide by no. of month. In the above case, it is $4200 / 3 = $1400).
GP(%): XXXXX
(it should be the percentage total actual GP with total Month Revenue. In the above case, it is 4200/8320 = 50.4%)
Here is the program:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Futurelink program</title>
</head>
<body>
<?php
include_once "coolmenu.php";
?>
<br>
<form name="form1" method="post" action="checkallmonthreven
ue.php">
<p>please input month range to list the revenue generated for the month
}<br>
From<input type="text" name="udatefrom">
To<input type="text" name="udateto">
<input type="submit" name="Submit" value="Submit">
<input name="Reset" type="reset" id="Reset" value="Reset">
<p> Remark: The input format should be like: 2005-12 2006-01, 2006-01 2006-04,or...<br>
</form>
<?php
//configue for connect to database
$host="localhost";
$user="franco";
$pass="1234";
$database="futurelink";
$table="course";
$udatefrom=isset($_POST["u
datefrom"]
)?$_POST["
udatefrom"
]:"";
$udateto=isset($_POST["uda
teto"])?$_
POST["udat
eto"]:"";
if(isset($_POST['Submit'])
) {
if(empty($udatefrom) || empty($udateto)) {
die("Both From and To Dates Are Required");
}
/* added date validn */
if (date("Y-m",strtotime($uda
tefrom)) > date("Y-m",strtotime($udat
eto))){
die("From date Cannot Be greater than To Date");
}
echo "<font size=3><b>The result is:</b></font><hr>";
//When there is content that we could get from the list
//Connect to mysql
$link=mysql_connect($host,
$user,$pas
s)or die ("Error ".mysql_query());
mysql_query("SET NAMES utf8");
//judge the connection is ok or not
if($link != False)
{
mysql_select_db($database)
or die ("Error ".mysql_query());
//setup search command
//get data from course that match with the condition
$qsum="Select sum(revenue) as Revenue ,sum(salary) as Salary, sum(gp) as Gp ,date_format(lesson1,'%Y-%
m') as Month From course where DATE_FORMAT(lesson1,'%Y-%m
') between '$udatefrom' and '$udateto' group by date_format(lesson1,'%Y-%m
')";
$result=mysql_db_query($da
tabase,$qs
um,$link);
$rows=mysql_num_rows($resu
lt) or die ("Error ".mysql_query());
if($rows > 0)
{
$rowcnt =1;
$revenue_sum=0;
$salary_sum=0;
$gp_sum=0;
echo "<table border=2 bgcolor='yellow'>";
echo "<tr><td>Id</td><td>Month<
/td><td>Re
venue</td>
<td>estima
ted cost</td><td>estimated GP</td>";
while($row = mysql_fetch_array($result)
){
?>
<tr>
<td><?php echo $rowcnt ;?></td>
<td><?php echo $row[3];?></td>
<td><?php echo number_format($row[0],2);?
></td>
<td><?php echo number_format($row[1],2);?
></td>
<td><?php echo number_format($row[2],2);?
></td>
</tr>
<?php
$rowcnt++;
$revenue_sum=$revenue_sum+
$row[0];
$salary_sum=$salary_sum+$r
ow[1];
$gp_sum=$gp_sum+$row[1];
}
echo "<tr style='font-weight:bold'><
td colspan=2>Total:</td><td>"
.number_fo
rmat($reve
nue_sum, 2)." </td>
<td>".number_format($salar
y_sum, 2)." </td>
<td>".number_format($gp_su
m, 2)." </td>
</tr>";
echo "</table>";
}else{
echo "No Records Available";
}
}else{
echo "Cannot connect to MySQL";
}
}
?>
</body>
</html>
