<?php

	include "library.php";

	$cxn = start_code();

    $generate = val( $_GET, "generate" );
    
    
    if ($generate == "no")
    {
        echo "<br>Invoice generation cancelled.<br><br>";
    }
    else
    {	
        $today_date = date( "Y-m-d" );
        
        
        $today_date_obj = date_create( date("Y-m-d") );
        								
        $today_plus_14_days = clone $today_date_obj;
        	
        date_add( $today_plus_14_days, date_interval_create_from_date_string( '14 days' ));
        
        $today_plus_14_days_str = $today_plus_14_days->format( "Y-m-d" );		
        
        
        page_header( "", $cxn );			
        
        
            // for each practice
        
        $query = "SELECT * FROM practice ORDER BY display_name";
        
        $result = run_query( $query, $cxn );
        
        
        for ($i=0; $i < query_num_rows( $result ); $i++)
        {
            $data = get_row( $result );
            
            $practice = $data['display_name'];
            
        
            $invoice_created = false;
            
            $total_ex_gst = 0;
        
        
                // retrieve tasks due and not already invoiced
        
            $query2 = "SELECT * FROM task WHERE practice = '".$practice."' and status = 'completed' and id not in (SELECT DISTINCT task_id FROM invoice_line)";
        
            $result2 = run_query( $query2, $cxn );
            
            if (query_num_rows( $result2 ) > 0)
            {
                for ($j=0; $j < query_num_rows( $result2 ); $j++)
                {
                    $data2 = get_row( $result2 );
                    
                    $task_id = $data2['id'];
                    
                    $amount = $data2['quoted_price'];
        
                    $total_ex_gst += $amount;
                    
                    
                    if ($data2['task_type'] == "soa")                
                        $description = prep_for_sql( "Statement of Advice for client\\'s ".$data2['client_names'].", Adviser ".$data2['adviser1'] );
                    else
                    if ($data2['task_type'] == "roa")                
                        $description = prep_for_sql( "Record of Advice for client\\s ".$data2['client_names'].", Adviser ".$data2['adviser1'] );
                    else
                        $description = prep_for_sql( "Task for client\\'s ".$data2['client_names'].", Adviser ".$data2['adviser1'] );
                    
                    if (! $invoice_created)
                    {
                        $query3 = "INSERT INTO invoice (effective_date, due_date, practice, amount_received, status) VALUES ('".$today_date."', '".$today_plus_14_days_str."', '".$practice."', 0, 'not_sent')";
        
                        $result3 = run_query( $query3, $cxn );
        
        
                        $query3 = "SELECT id FROM invoice ORDER BY id DESC LIMIT 1";
        
                        $result3 = run_query( $query3, $cxn );
        
        
                        $data3 = get_row( $result3 );
                        
                        $invoice_id = $data3['id'];
                        
                        $invoice_created = true;
                    }
                    
        	    	$query4 = "INSERT INTO invoice_line (invoice_id, task_id, credit_note_id, description, amount) VALUES (".$invoice_id.", ".$task_id.", 0, '".$description."', ".$amount.")";
        
                    $result4 = run_query( $query4, $cxn );
                    
                        // update task with invoice id
                        
                }
        
        
            	$query6 = "SELECT * FROM credit_note WHERE practice = '".$practice."' AND amount_paid < amount ORDER BY effective_date";
        
                $result6 = run_query( $query6, $cxn );
        
        
                for ($k=0; $k < query_num_rows( $result6 ); $k++)
                {
                    $data6 = get_row( $result6 );
        
                    if ($total_ex_gst > 0)
                    {
                        $available_credit = $data6['amount'] - $data6['amount_paid'];
                        
                        if ($available_credit < $total_ex_gst)
                        {
                	    	$query7 = "UPDATE credit_note SET amount_paid = amount WHERE id = ".$data6['id'];
                
                            $result7 = run_query( $query7, $cxn );
                            
                            $amount = $available_credit;
                            
                            $total_ex_gst -= $available_credit;
                        }
                        else
                        {
                	    	$query7 = "UPDATE credit_note SET amount_paid = amount_paid + ".$total_ex_gst." WHERE id = ".$data6['id'];
                
                            $result7 = run_query( $query7, $cxn );
        
                            $amount = $total_ex_gst;
                            
                            $total_ex_gst = 0;
                        }
                        
                        $description = $data6['comments'];
                        
        		    	$query4 = "INSERT INTO invoice_line (invoice_id, task_id, credit_note_id, description, amount) VALUES (".$invoice_id.", 0, ".$data6['id'].", '".$description."', -".$amount.")";
        		    	
                        $result4 = run_query( $query4, $cxn );
        		    }
                }
        
                if ($invoice_created)
                {
        //                if ($total_ex_gst == 0)
        //                    $query5 = "UPDATE invoice SET status = 'paid_in_full' WHERE id = ".$invoice_id;
                    
                    $gst = round( $total_ex_gst * 0.1, 2 );
                    
                    $total_due = $total_ex_gst + $gst;
        
                    $query5 = "UPDATE invoice SET amount_ex_gst = ".$total_ex_gst.", gst = ".$gst.", total_due = ".$total_due." WHERE id = ".$invoice_id;
        
                    $result5 = run_query( $query5, $cxn );
                }
            }
        }    
        
        echo black( "<br>Finished<br>" );
    }
    
?>