Solved

SQL quiry assistance needed

Posted on 2007-11-23
8
219 Views
Last Modified: 2013-12-12
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


Thanks in advance,

Roman



0
Comment
Question by:dizel247
  • 5
  • 2
8 Comments
 
LVL 17

Expert Comment

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

Author Comment

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

Thanks in advance,

Roman
0
 
LVL 17

Expert Comment

by:nplib
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);

Open in new window

0
 
LVL 17

Expert Comment

by:nplib
ID: 20343966
place this after your connection statment to your db.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:dizel247
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>

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
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)

Open in new window

0
 
LVL 17

Expert Comment

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

Accepted Solution

by:
nplib earned 500 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>

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now