<?php

	include "library.php";

	$cxn = start_code();
	
    $cxn_common = db_common_login();
	

	$reporttype = val( $_GET, "reporttype" );

	$start_date = val( $_POST, "start_date" );

	$end_date = val( $_POST, "end_date" );
	
	$readonly = val( $_GET, "readonly" );
	
	
		/* start the page  */

		// don't put this before a jump_to_page()
		

	$report_start_date = convert_date_of_birth( $start_date, "dd/mm/yyyy", $cxn );
	
	$report_end_date = convert_date_of_birth( $end_date, "dd/mm/yyyy", $cxn );

	
	if ($reporttype == "profitability")
		page_header( "Profitability Report", $cxn, $cxn_common );
	else
	if ($reporttype == "completednotinvoiced")
		page_header( "Tasks Completed Not Invoiced", $cxn, $cxn_common );
	else
	if ($reporttype == "tasksinprogress")
		page_header( "Tasks in Progress", $cxn, $cxn_common );
	else
	if ($reporttype == "notassigned")
		page_header( "Tasks Not Assigned", $cxn, $cxn_common );
	else
	if ($reporttype == "allcompletedinperiod")
		page_header( "All Tasks Completed In Period", $cxn, $cxn_common );
	else
	if ($reporttype == "claimatask")
		page_header( "Claim a Task", $cxn, $cxn_common );
	else
	if ($reporttype == "unquotedrequests")
		page_header( "Unquoted Requests", $cxn, $cxn_common );
		

	if ($reporttype == "profitability")
		$query = "SELECT * FROM task WHERE status = 'completed' and date_completed >= '".$report_start_date."' AND date_completed <= '".$report_end_date."' ORDER BY id";	
	else
	if ($reporttype == "completednotinvoiced")
		$query = "SELECT * FROM task WHERE (status = 'completed' or date_completed <> '0000-00-00') AND (invoice_number is NULL OR invoice_number = '') ORDER BY id";
	else
	if ($reporttype == "tasksinprogress")
		$query = "SELECT * FROM task WHERE status <> 'completed' AND status <> 'quote_declined' AND status <> 'cancelled' ORDER BY id";
	else
	if ($reporttype == "notassigned")
		$query = "SELECT * FROM task WHERE (paraplanner is NULL OR paraplanner = '') AND status <> 'completed' AND status <> 'quote_declined' AND status <> 'cancelled' ORDER BY id";		
	else
	if ($reporttype == "claimatask")
		$query = "SELECT * FROM task WHERE (paraplanner is NULL OR paraplanner = '') AND status = 'quote_accepted' ORDER BY id";	
	else
	if ($reporttype == "allcompletedinperiod")
		$query = "SELECT * FROM task WHERE status = 'completed' and date_completed >= '".$report_start_date."' AND date_completed <= '".$report_end_date."' ORDER BY id";	
	else
	if ($reporttype == "unquotedrequests")
		$query = "SELECT * FROM task WHERE status = 'received' ORDER BY id";


	$result = run_query( $query, $cxn );
	
	
	if ($reporttype == "profitability")
        profitability_report( $result, $report_start_date, $report_end_date, $cxn );
    else
    {    	
        if (query_num_rows( $result ) == 0)
        {
        	echo black( "There are no tasks listed in this report.<br><br>" );
        	
        	echo show_home_link();
        	
        	exit();
        }
        else
        {
        	echo "<br><br>";
        
        	echo "<style type='text/css'>\n";
        	echo "td ";
        	echo " { ";
        	echo " padding:0 8px 0 8px; ";
        	echo " } ";
        	echo "</style>";
        	
        	echo "<table>";
        	
        	$total_income = 0;
        	
        	echo "<tr>";
        	echo "<td valign='top' align='center'>".black( "<b>Task ID</b>" )."</td>";

        	echo "<td valign='top' align='center'>".black( "<b>Request ID</b>" )."</td>";
        	
        	if ($reporttype != "claimatask")									
        	{
            	echo "<td valign='top' align='center'>".black( "<b>Received</b>" )."</td>";
        
            	if ($reporttype == "completednotinvoiced" || $reporttype == "allcompletedinperiod")
        	    	echo "<td valign='top' align='center'>".black( "<b>Completed</b>" )."</td>";
        	}
        	
        	echo "<td valign='top' align='center'>".black( "<b>Wrk days<br>on list</b>" )."</td>";
        	echo "<td valign='top' align='center'>".black( "<b>Task Type</b>" )."</td>";
        	echo "<td valign='top' align='center'>".black( "<b>Practice Name</b>" )."</td>";
        	echo "<td valign='top' align='center'>".black( "<b>End Client Name(s)</b>" )."</td>";
        	
        	if ($reporttype != "claimatask")							
        		echo "<td valign='top' align='center'>".black( "<b>Completed By</b>" )."</td>";
        		
        	echo "<td valign='top' align='center'>".black( "<b>Number of<br>strategies</b>" )."</td>";
        	echo "<td valign='top' align='center'>".black( "<b>Est Hours</b>" )."</td>";
        	
        	if ($reporttype != "claimatask")									
        		echo "<td valign='top' align='center'>".black( "<b>Completed<br>Hours</b>" )."</td>";
        	
        	if ($reporttype != "claimatask")		
        		echo "<td valign='top' align='center'>".black( "<b>Quoted Price<br>(Excl. GST)</b>" )."</td>";
        	
        	echo "<td valign='top' align='center'>".black( "<b>Paraplanner<br>Commission<br>(Excl. GST)</b>" )."</td>";
        			
        	if ($reporttype == "claimatask")
        		echo "<td valign='top' align='center'>".black( "<b>Claim this<br>Task</b>" )."</td>";
        
        	echo "</tr>";
        	
        	for ($i=0; $i < query_num_rows( $result ); $i++)
        	{
        		$data = get_row( $result );
        		
        		$this_working_days = working_days( $data['date_quote_accepted'], $data['date_completed'] );
        		
        		if ($this_working_days == -1)
        			$this_working_days = "";
        			
        		if ($data['date_received'] == "")
        			$date_received = "";
        		else
        			$date_received = ad_format_date( substr($data['date_received'], 0, 10), "dd/mm/yyyy" );
        
        		if ($data['date_completed'] == "")
        			$date_completed = "";
        		else
        			$date_completed = ad_format_date( substr($data['date_completed'], 0, 10), "dd/mm/yyyy" );
        		
        		if ($data['task_type'] == "soa")
        			$task_type_text = "SOA";
        		else
        		if ($data['task_type'] == "roa")
        			$task_type_text = "ROA";
        		else
        		if ($data['task_type'] == "soariskonly")
        			$task_type_text = "Risk Only SOA";
        		else
        		    $task_type_text = "";
        		    
        		echo "<tr>";
        		echo "<td>".black( $data['id'] )."</td>";

        		echo "<td>".black( $data['request_id'] )."</td>";
        
        		if ($reporttype != "claimatask")										
        	    {
        		    echo "<td>".black( $date_received )."</td>";
        
            	    if ($reporttype == "completednotinvoiced" || $reporttype == "allcompletedinperiod")			    
        				echo "<td>".black( $date_completed )."</td>";
        	    }
        
        		echo "<td align='right'>".black( $this_working_days )."</td>";
        		echo "<td>".black( $task_type_text )."</td>";
        		echo "<td>".black( $data['practice'] )."</td>";
        		echo "<td>".black( $data['client_names'] )."</td>";
        		
        		if ($reporttype != "claimatask")								
        			echo "<td>".black( $data['paraplanner'] )."</td>";
        			
        		echo "<td align='right'>".black( $data['number_of_strategies'] )."</td>";
        		echo "<td align='right'>".black( $data['estimated_hours'] )."</td>";
        		
        		if ($reporttype != "claimatask")										
        			echo "<td align='right'>".black( $data['completed_hours'] )."</td>";
        		
        		if ($reporttype != "claimatask")					
        			echo "<td align='right'>".black( $data['quoted_price'] )."</td>";
        			
        		echo "<td align='right'>".black( $data['paraplanner_commission'] )."</td>";
        		
        		if ($reporttype == "claimatask")
        			echo "<td valign='top' align='center'>".show_link( "claim", "claim_task.php", "taskid", $data['id'] )."</td>";
        		
        		
                echo "<td>".show_link( "Edit", "edit_all_data.php", "formname", "task", "tabletitle", "Task", "newrecord", "false", "id", $data['id'], "readonly", $readonly )."</td>";
                
                
        		echo "</tr>";
        		
        		
        		$total_income = $total_income + $data['quoted_price'];
        	}
        	
        	echo "<tr><td>&nbsp;</td></tr>";
        	
        //		if ($reporttype != "claimatask")		
        //		{
        //			echo "<tr>";
        //			
        //			if ($reporttype == "completednotinvoiced" || $reporttype == "allcompletedinperiod")			    
        //			    echo "<td></td>";
        //			    
        //			echo "<td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td align='right'>".black( number_format( $total_income, 0 ))."</td></tr>";
        //		}
        	
        	echo "</table>";
        }			
    }
    
	echo "<br><br><br>";
	
	echo show_home_link();



    function profitability_report( $result, $report_start_date, $report_end_date, $cxn )
    {
		$query2 = "SELECT * FROM salaries_accumulated WHERE date_accumulated >= '".$report_start_date."' AND date_accumulated <= '".$report_end_date."' ORDER BY id";	

    	$result2 = run_query( $query2, $cxn );

        $num_entries_salaries_accumulated = query_num_rows( $result2 );
	
	
		for ($i=0; $i < $num_entries_salaries_accumulated; $i++)
			$salaries_accumulated[$i] = get_row( $result2 );
    
    
        $number_entries = query_num_rows( $result );
        
		for ($i=0; $i < $number_entries; $i++)
			$data[$i] = get_row( $result );
    	

		$start_date = gregoriantojd( substr( $report_start_date, 5, 2), substr( $report_start_date, 8, 2), substr( $report_start_date, 0, 4) );

		$end_date = gregoriantojd( substr( $report_end_date, 5, 2), substr( $report_end_date, 8, 2), substr( $report_end_date, 0, 4) );


        $this_salaries_accumulated = 0;

        $hours_worked = 0;

        $soas_completed = 0;
        
        $billed_cost = 0;

        $paraplanner_commission = 0;

        $completed_hours = 0;

        echo "<table>";

        echo "<tr>";
        
		echo "<td align='right' width=110><b>".black( "Week Ending" )."</b></td>";

		echo "<td align='right' width=110><b>".black( "Hours<br>Worked" )."</b></td>";

		echo "<td align='right' width=110><b>".black( "SOA's<br>Completed" )."</b></td>";

		echo "<td align='right' width=110><b>".black( "Completed<br>Hours" )."</b></td>";
	
		echo "<td align='right' width=110><b>".black( "Billed" )."</b></td>";
		
		echo "<td align='right' width=110><b>".black( "Paraplanner<br>Commission" )."</b></td>";

		echo "<td align='right' width=110><b>".black( "Salaries<br>Accumulated" )."</b></td>";

		echo "<td align='right' width=110><b>".black( "Net Income" )."</b></td>";

        echo "</tr>";
        
		for ($day = $start_date; $day <= $end_date; $day++)
		{
					// 0 = sunday, 6 = saturday
		    
			if (jddayofweek( $day ) == 6)
		    {
                $this_date = jdtogregorian( $day - 1 );
                
                $this_month = "";
                $this_day = "";
                $this_year = "";
                
                list( $this_month, $this_day, $this_year ) = explode( "/", $this_date );
                
                echo "<tr>";
                
                echo "<td align='right'>".black( $this_day."/".$this_month."/".$this_year )."</td>";

                echo "<td align='right'>".black( $hours_worked )."</td>";

                echo "<td align='right'>".black( $soas_completed )."</td>";
                                
                echo "<td align='right'>".black( $completed_hours )."</td>";
                
                echo "<td align='right'>".black( $billed_cost )."</td>";
                
                echo "<td align='right'>".black( $paraplanner_commission )."</td>";
        
                echo "<td align='right'>".black( $this_salaries_accumulated )."</td>";
                
                echo "<td align='right'>".black( $billed_cost - $paraplanner_commission - $this_salaries_accumulated )."</td>";
                
                echo "</tr>";
                
                $this_salaries_accumulated = 0;

                $hours_worked = 0;
                
                $soas_completed = 0;
                
                $billed_cost = 0;
                
                $paraplanner_commission = 0;
                
                $completed_hours = 0;
		    }	


	        for ($i=0; $i < $num_entries_salaries_accumulated; $i++)
	        {
		        $date_accumulated = $salaries_accumulated[$i]['date_accumulated'];
		        
        		$j_date_accumulated = gregoriantojd( substr( $date_accumulated, 5, 2), substr( $date_accumulated, 8, 2), substr( $date_accumulated, 0, 4) );

                if ($j_date_accumulated == $day)
                {
			        $this_salaries_accumulated += $salaries_accumulated[$i]['amount'];
			        
			        $hours_worked += $salaries_accumulated[$i]['hours_worked'];
                }
	        }
	        
		    for ($i=0; $i < $number_entries; $i++)
		    {
		        $completed_date = $data[$i]['date_completed'];
		        
        		$j_completed_date = gregoriantojd( substr( $completed_date, 5, 2), substr( $completed_date, 8, 2), substr( $completed_date, 0, 4) );

                if ($j_completed_date == $day)
                {
                    if ($data[$i]['task_type'] == "soa")
                        $soas_completed++;
                        
                    $billed_cost += $data[$i]['quoted_price'];

                    $paraplanner_commission += $data[$i]['paraplanner_commission'];

                    $completed_hours += $data[$i]['completed_hours'];
                }
		    }
		}
		
        echo "</table>";
    }


	function working_days( $date_quote_accepted, $date_completed )
	{
		$working_days = -1;

		if ($date_quote_accepted != "" && substr( $date_quote_accepted, 0, 10) != "0000-00-00")
		{
            $working_days = 0;
            
			$start_date = gregoriantojd( substr( $date_quote_accepted, 5, 2), substr( $date_quote_accepted, 8, 2), substr( $date_quote_accepted, 0, 4) );

			if ($date_completed != "0000-00-00")
				$end_date_full = substr( $date_completed, 0, 10 );
			else
				$end_date_full = date("Y-m-d");

			$end_date = gregoriantojd( substr( $end_date_full, 5, 2), substr( $end_date_full, 8, 2), substr( $end_date_full, 0, 4) );
				
			for ($day = $start_date; $day <= $end_date; $day++)
			{
					// 0 = sunday, 6 = saturday
					
				if (jddayofweek( $day ) != 0 && jddayofweek( $day ) != 6)
					$working_days++;
			}
		}

	    return ($working_days);
    }
    
?>

