[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# SQL quiry assistance needed

Posted on 2007-11-23
Medium Priority
235 Views
Hello,

I am trying to create a small program that will calculate commission using php mysql.

I have a user import a data to the mysql database and I need to display acutall commission as a result.
I have a table commission.
Table has 1 column: Company Name, Profit for the month, Month.
Calculation as follows.
(Current Month Profit - Last Month profit) * 0.2

Sample Data Bellow
account_id      company_name      minutes      profit      revenue      cost      time_frame
983348      Company 1      1731.5      14.43      150.4      135.97      10
983349      Company 2      108744.92      701.65      7304.63      6602.98      10
983350      Company 3      168625.77      762.69      6851.39      6088.7      10
983358      Company 1      1783.02      14.53      153.02      138.49      11
983359      Company 2      277170.65      1375.36      7944      6568.65      11
983360      Company 3      286652.4      1428.12      24341.75      22913.63      11

Result Need to see:
Month November
Company Name   Commision
Company 1          0.02
Company 2          134.742
Company 3           133.086

Roman

0
Question by:dizel247
• 5
• 2

LVL 17

Expert Comment

ID: 20342766
is time_frame the month aka 1-12?
0

Author Comment

ID: 20343255
yes but i not sure if its right. I need to have month and a year so we can distinguish between them.

Roman
0

LVL 17

Expert Comment

ID: 20343960
Here is the code.

``````function getCommission(\$currentMonth) {
\$months = array(\$currentMonth-1, \$currentMonth); // this array contians previous month and current month
switch (\$months['1']) {
case 1: \$month = "January"; break;
case 2: \$month = "February"; break;
case 3: \$month = "March"; break;
case 4: \$month = "April"; break;
case 5: \$month = "May"; break;
case 6: \$month = "June"; break;
case 7: \$month = "July"; break;
case 8: \$month = "August"; break;
case 9: \$month = "September"; break;
case 10: \$month = "October"; break;
case 11: \$month = "November"; break;
case 12: \$month = "December"; break;
}

\$results = array("Company" => array(), "Current" => array(), "Previous" => array());
foreach (\$months as \$key => \$m) {
\$sql = "SELECT company_name, profit FROM sample1 where time_frame = \$m ORDER BY company_name;";
\$rs = mysql_query(\$sql) or die(mysql_error());
\$company = array();
\$profits = array();
while (\$row = mysql_fetch_array(\$rs)) {
//\$results[\$month][] = \$row['profit'];
//\$results[\$month][] = \$row['company_name'];
\$profits[] = \$row['profit'];
\$company[] = \$row['company_name'];
}
if (\$key == 0) {
\$results['Previous'] = \$profits;
} else {
\$results['Current'] = \$profits;
}
}
\$results['Company'] = \$company;
\$html = "<table>";
\$html .= "<tr><td colspan='2'>Month \$month</td></tr>";
foreach (\$results['Company'] as \$key => \$value) {
\$commission = number_format(((\$results['Current'][\$key] - \$results['Previous'][\$key])*0.2), 2);
\$html .= "<tr>";
\$html .= "<td>\$value</td><td>\$commission</td>";
\$html .= "</tr>";
}
\$html .= "</table>";
return \$html;
}
echo getCommission(11);
``````
0

LVL 17

Expert Comment

ID: 20343966
0

Author Comment

ID: 20344917
hello,

Thanks for a wonderful description. For some reason I am not getting any result. All I see  month  November.

1. Can you verify how time_frame - what format I need to have my values 1-12 or ex November, December etc.

2. I am already running a quire that shows me result for other columns. I would like to be able to plug your code for the Commission column and see the result. Attaching code bellow. I am using dremweaver to generate some of my code.
``````<?php require_once('Connections/connTT.php'); ?>
//This is sample quiry I am running. I will modify it later to get request from previous page to show result based on user imput. For now I am trying to have generic result.
mysql_select_db(\$database_connTT, \$connTT);
\$query_rsCommision = "SELECT tb_accounts.minutes, tb_accounts.time_frame  	tb_accounts.profit,  	tb_accounts.revenue,  	tb_accounts.cost,  	carriers.carrier_name,  	tb_account_report.report_name,  	carriers.manag_customers,  	carriers.manag_venodrs,  	carriers.house_account,  	carriers.carrier_status FROM tb_accounts INNER JOIN carriers ON tb_accounts.carrier_code = carriers.carrier_code 	 INNER JOIN tb_account_report ON tb_accounts.report_id = tb_account_report.report_id";
\$rsCommision = mysql_query(\$query_rsCommision, \$connTT) or die(mysql_error());
\$row_rsCommision = mysql_fetch_assoc(\$rsCommision);
\$totalRows_rsCommision = mysql_num_rows(\$rsCommision);
//////////////

<table width="90%" border="1" align="center" cellpadding="5" cellspacing="0" bordercolor="#000000">
<tr>
<td colspan="7"><div align="center" class="topheader"><?php echo \$row_rsManagers['user_firstname']; ?>'s Commision for <?php echo \$row_rsReportName['report_name']; ?></div></td>
</tr>
<tr>
<td bgcolor="#999933"><div align="center">Carrier Name</div></td>
<td bgcolor="#999933"><div align="center">Minutes</div></td>
<td bgcolor="#999933"><div align="center">Profit</div></td>
<td bgcolor="#999933"><div align="center">Revenue</div></td>
<td bgcolor="#999933"><div align="center">Cost</div></td>
<td bgcolor="#999933"><div align="center">Margin</div></td>
<td bgcolor="#999933"><div align="center">Commision</div></td>
</tr>
<?php do { ?>
<tr>
<td><?php echo \$row_rsCommision['carrier_name']; ?></td>
<td><div align="right"><?php echo \$row_rsCommision['minutes']; ?></div></td>
<td><div align="right">\$<?php echo \$row_rsCommision['profit']; ?></div></td>
<td><div align="right">\$<?php echo \$row_rsCommision['revenue']; ?></div></td>
<td><div align="right">\$<?php echo \$row_rsCommision['cost']; ?></div></td>
<td><div align="right">                <?php
\$cost = \$row_rsCommision['cost'];
\$revenue = \$row_rsCommision['revenue'];
\$margin = 100 - ( \$cost * 100 / \$revenue );
echo round(\$margin,2) . "%"; ?></div></td>
<td><div align="right">
<?php
/////////// CODE NEEDED HERE *****************************
?>

</div></td>
</tr>
<?php } while (\$row_rsCommision = mysql_fetch_assoc(\$rsCommision)); ?>
<tr>
<td colspan="2">Total:</td>
<td><div align="center">\$<?php echo \$row_rsTotatlProfit['Total_profit']; ?> </div></td>
<td><div align="center">\$<?php echo \$row_rsTotalRevenue['Total_revenue']; ?></div></td>
<td><div align="center">\$<?php echo \$row_rsTotal_Cost['Total_cost']; ?></div></td>
<td colspan="2"><div align="center">Total Comision Due: \$327.11</div></td>
</tr>
</table>
``````
0

LVL 25

Expert Comment

ID: 20346546
you really need another column, year to be able to go from 2007/12 to 2008/1.  I'll assume that  you will add that to your table
``````select (thismonth.profit - lastmonth.profit) * 0.2
from comm thismonth
inner join comm lastmonth on thismonth.company_name = lastmonth.company_name and
(thismonth.year*12+thismonth.time_frame) = (lastmonth.year*12+lastmonth.time_frame+1)
``````
0

LVL 17

Expert Comment

ID: 20360023
I did it with the columns you provided, I will edit your code to comply.
0

LVL 17

Accepted Solution

nplib earned 2000 total points
ID: 20360100
try that.

``````<?php
require_once('Connections/connTT.php');
function getCommission(\$currentMonth) {
\$months = array(\$currentMonth-1, \$currentMonth); // this array contians previous month and current month
switch (\$months['1']) {
case 1: \$month = "January"; break;
case 2: \$month = "February"; break;
case 3: \$month = "March"; break;
case 4: \$month = "April"; break;
case 5: \$month = "May"; break;
case 6: \$month = "June"; break;
case 7: \$month = "July"; break;
case 8: \$month = "August"; break;
case 9: \$month = "September"; break;
case 10: \$month = "October"; break;
case 11: \$month = "November"; break;
case 12: \$month = "December"; break;
}

\$results = array("Company" => array(), "Current" => array(), "Previous" => array());
foreach (\$months as \$key => \$m) {
\$sql = "SELECT company_name, profit FROM sample1 where time_frame = \$m ORDER BY company_name;";
\$rs = mysql_query(\$sql) or die(mysql_error());
\$company = array();
\$profits = array();
while (\$row = mysql_fetch_array(\$rs)) {
//\$results[\$month][] = \$row['profit'];
//\$results[\$month][] = \$row['company_name'];
\$profits[] = \$row['profit'];
\$company[] = \$row['company_name'];
}
if (\$key == 0) {
\$results['Previous'] = \$profits;
} else {
\$results['Current'] = \$profits;
}
}
\$results['Company'] = \$company;
\$html = "<table>";
\$html .= "<tr><td colspan='2'>Month \$month</td></tr>";
foreach (\$results['Company'] as \$key => \$value) {
\$commission = number_format(((\$results['Current'][\$key] - \$results['Previous'][\$key])*0.2), 2);
\$html .= "<tr>";
\$html .= "<td>\$value</td><td>\$commission</td>";
\$html .= "</tr>";
}
\$html .= "</table>";
return \$html;
}

//This is sample quiry I am running. I will modify it later to get request from previous page to show result based on user imput. For now I am trying to have generic result.
mysql_select_db(\$database_connTT, \$connTT);
\$query_rsCommision = "SELECT tb_accounts.minutes, tb_accounts.time_frame        tb_accounts.profit,     tb_accounts.revenue,    tb_accounts.cost,       carriers.carrier_name,          tb_account_report.report_name,          carriers.manag_customers,       carriers.manag_venodrs,         carriers.house_account,         carriers.carrier_status FROM tb_accounts INNER JOIN carriers ON tb_accounts.carrier_code = carriers.carrier_code         INNER JOIN tb_account_report ON tb_accounts.report_id = tb_account_report.report_id";
\$rsCommision = mysql_query(\$query_rsCommision, \$connTT) or die(mysql_error());
\$row_rsCommision = mysql_fetch_assoc(\$rsCommision);
\$totalRows_rsCommision = mysql_num_rows(\$rsCommision);
//////////////
?>
<table width="90%" border="1" align="center" cellpadding="5" cellspacing="0" bordercolor="#000000">
<tr>
<td colspan="7"><div align="center" class="topheader"><?php echo \$row_rsManagers['user_firstname']; ?>'s Commision for <?php echo \$row_rsReportName['report_name']; ?></div></td>
</tr>
<tr>
<td bgcolor="#999933"><div align="center">Carrier Name</div></td>
<td bgcolor="#999933"><div align="center">Minutes</div></td>
<td bgcolor="#999933"><div align="center">Profit</div></td>
<td bgcolor="#999933"><div align="center">Revenue</div></td>
<td bgcolor="#999933"><div align="center">Cost</div></td>
<td bgcolor="#999933"><div align="center">Margin</div></td>
<td bgcolor="#999933"><div align="center">Commision</div></td>
</tr>
<?php do { ?>
<tr>
<td><?php echo \$row_rsCommision['carrier_name']; ?></td>
<td><div align="right"><?php echo \$row_rsCommision['minutes']; ?></div></td>
<td><div align="right">\$<?php echo \$row_rsCommision['profit']; ?></div></td>
<td><div align="right">\$<?php echo \$row_rsCommision['revenue']; ?></div></td>
<td><div align="right">\$<?php echo \$row_rsCommision['cost']; ?></div></td>
<td><div align="right">                <?php
\$cost = \$row_rsCommision['cost'];
\$revenue = \$row_rsCommision['revenue'];
\$margin = 100 - ( \$cost * 100 / \$revenue );
echo round(\$margin,2) . "%"; ?></div></td>
<td><div align="right">
<?php
echo getCommission(intval(\$row_rsCommision['time_frame']));
?>
</div></td>
</tr>
<?php } while (\$row_rsCommision = mysql_fetch_assoc(\$rsCommision)); ?>
<tr>
<td colspan="2">Total:</td>
<td><div align="center">\$<?php echo \$row_rsTotatlProfit['Total_profit']; ?> </div></td>
<td><div align="center">\$<?php echo \$row_rsTotalRevenue['Total_revenue']; ?></div></td>
<td><div align="center">\$<?php echo \$row_rsTotal_Cost['Total_cost']; ?></div></td>
<td colspan="2"><div align="center">Total Comision Due: \$327.11</div></td>
</tr>
</table>
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question