<?require_once('functions.php');



//Now that we've created such a nice heading for our html table, lets create a heading for our csv table
$csv_hdr=" KARIOBANGI SYSTEM \n";
$csv_hdr=" Trial Balance As At $date \n";
    $csv_hdr .= "Account, Debit, Credit \n";

//Quickly create a variable for our output that'll go into the CSV file (we'll make it blank to start).
    $csv_output="";
	
// Ok, we're done with the table heading, lets connect to the database
  //  $database="test";
    	$dbc=mysqli_connect("localhost","zeqrbruu_kariobangishop","@@karishop@@","zeqrbruu_shop");
    mysqli_set_charset($dbc,('utf8'));
    mysqli_query($dbc,('SET NAMES UTF-8'));

// Lets say we wanted a table with all orders, their products and totals...a summary report of sorts
$assets=mysqli_query($dbc,("select sum(debit)-sum(credit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='4' 
and date<='$date'
 group by id
 
 having debit>0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
           
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .= "\n";  
           

        } 
		$assets=mysqli_query($dbc,("select sum(debit)-sum(credit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='4'
and date<='$date' group by id
 having debit<0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
            $csv_output .=  ", ";
            $csv_output .= ($row['debit']*-1) . ", ";
         
         $csv_output .= "\n";  
           

        }
		$assets=mysqli_query($dbc,("select sum(debit)-sum(credit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='10'   group by id
 having debit>0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
           
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .= "\n";  
           

        } 
		$assets=mysqli_query($dbc,("select sum(debit)-sum(credit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='10' 
and date<='$date' group by id
 having debit<0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
            $csv_output .=  ", ";
            $csv_output .= ($row['debit']*-1) . ", ";
         
         $csv_output .= "\n";  
           

        }
		$assets=mysqli_query($dbc,("select sum(debit)-sum(credit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='1'
and date<='$date' group by id
 having debit>0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
           
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .="\n";  
           

        }
			$assets=mysqli_query($dbc,("select sum(debit)-sum(credit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='7'
and date<='$date' group by id
 having debit>0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
           
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .="\n";  
           

        }
			
		$assets=mysqli_query($dbc,("select *,SUM(debit)-(datediff('$date',ledgers.date)/365)*(dep/100*SUM(debit)) as debit,accounts.id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
inner join accounts on accounts.id=ledgers.account_id
where group_id='6'
and date<='$date'
GROUP BY ledgers.account_id
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
           
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .= "\n";  
           

        }		
		$assets=mysqli_query($dbc,("select *,SUM(debit)-(datediff('$date',ledgers.date)/365)*(dep/100*SUM(debit)) as debit,accounts.id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
inner join accounts on accounts.id=ledgers.account_id
where group_id='6'
and date<='$date'
GROUP BY ledgers.account_id
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
           
           $dep+=$row['debit'];

        }
		$name='DEPRECIATION';
		  $csv_output .= $name. ", ";
           
            $csv_output .= $dep . ", ";
         
         $csv_output .= "\n"; 
		 
	
			
	
						$assts=mysqli_query($dbc,("select sum(credit)-sum(debit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='3'   group by ledgers.id
 and date<='$date'
 having debit>0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assts)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
            $csv_output .=  ", ";
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .= "\n";  
           

        }
		
		$assets=mysqli_query($dbc,("select sum(credit)-sum(debit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='2'
and date<='$date' group by id
 having debit>0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
            $csv_output .=  ", ";
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .= "\n";  
           

        } 
		$assets=mysqli_query($dbc,("select sum(credit)-sum(debit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='2'
and date<='$date' group by id
 having debit<0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
			 $csv_output .= ($row['debit']*-1) . ", ";
            $csv_output .=  ", ";
           
         
         $csv_output .= "\n";  
           

        }
			$assets=mysqli_query($dbc,("select sum(credit)-sum(debit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='11' 
and date<='$date' group by id
 having debit>0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
           
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .= "\n";  
           

        } 
		$assets=mysqli_query($dbc,("select sum(credit)-sum(debit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='11'
and date<='$date' group by id
 having debit<0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
            $csv_output .=  ", ";
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .= "\n";  
           

        }	$assets=mysqli_query($dbc,("select sum(credit)-sum(debit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='12'
and date<='$date' group by id
 having debit>0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
           
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .= "\n";  
           

        } 
		$assets=mysqli_query($dbc,("select sum(credit)-sum(debit)as debit,ledgers.account_id as id from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='12' 
and date<='$date' group by id
 having debit<0
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
            $csv_output .= Legers($row['id']). ", ";
            $csv_output .=  ", ";
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .= "\n";  
           

        }
		
				$assets=mysqli_query($dbc,("select sum(credit)-sum(debit)as debit,ledgers.account_id as id  from ledgers
inner join group_accounts on group_accounts.account_id=ledgers.account_id
 where group_id='5'
and date<='$date' group by id
"));

// If our query has some assetss, lets store them in array of rows.
   
    
        //While our rows array has stuff in it...meaning it has column data, lets print it to each of the cells in our table
        while ($row = mysqli_fetch_assoc($assets)) {
//here we are displaying the contents of the field or column in our rows array for a particular row.
            //while we're at it we might as well store the data in comma separated values (csv) format in the csv_output variable for later use.
          $csv_output .= Legers($row['id']). ", ";
            $csv_output .=  ", ";
            $csv_output .= ($row['debit']) . ", ";
         
         $csv_output .= "\n";      
           

        }
		
		//closing while loop
     //closing if stmnt
	unset($_SESSION['date1']);
unset($_SESSION['date2']);
unset($_SESSION['id']);
mysqli_close($dbc);
?>

