<?php
	/** FINANCIAL_MAX_ITERATIONS */
    define('FINANCIAL_MAX_ITERATIONS', 128);
    /** FINANCIAL_PRECISION */
	define('FINANCIAL_PRECISION', 1.0e-08);
	define("prop", "prop");

	if(isset($_POST['function2call']) && !empty($_POST['function2call'])) {
		$function2call = $_POST['function2call'];
		switch($function2call) {
			case 'setup' : setup($_POST['property']);break;
			case 'setup' : createdata($_POST['property']);break;
		}
	}

    class PHPExcel_Calculation_Financial{
        public static function IRR($values, $guess = 0.1){
            if (!is_array($values)) {
                return '#VALUE!';
            }
            $values = self::flattenArray($values);
            
            // create an initial range, with a root somewhere between 0 and guess
            $x1 = 0.0;
            $x2 = $guess;
            $f1 = self::NPV($x1, $values);
            $f2 = self::NPV($x2, $values);
            for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
                if (($f1 * $f2) < 0.0) {
                    break;
                }
                if (abs($f1) < abs($f2)) {
                    $f1 = self::NPV($x1 += 1.6 * ($x1 - $x2), $values);
                } else {
                    $f2 = self::NPV($x2 += 1.6 * ($x2 - $x1), $values);
                }
            }
            if (($f1 * $f2) > 0.0) {
                return '#VALUE!';
            }
            $f = self::NPV($x1, $values);
            if ($f < 0.0) {
                $rtb = $x1;
                $dx = $x2 - $x1;
            } else {
                $rtb = $x2;
                $dx = $x1 - $x2;
            }
            for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
                $dx *= 0.5;
                $x_mid = $rtb + $dx;
                $f_mid = self::NPV($x_mid, $values);
                if ($f_mid <= 0.0) {
                    $rtb = $x_mid;
                }
                if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION)) {
                    return $x_mid;
                }
            }
            return '#VALUE!';
        }
        public static function NPV(){
            // Return value
            $returnValue = 0;
            // Loop through arguments
            $aArgs = self::flattenArray(func_get_args());
            // Calculate
            $rate = array_shift($aArgs);
            for ($i = 1; $i <= count($aArgs); ++$i) {
                // Is it a numeric value?
                if (is_numeric($aArgs[$i - 1])) {
                    $returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i);
                }
            }
            // Return
            return $returnValue;
        }
        public static function flattenArray($array){
            if (!is_array($array)) {
                return (array) $array;
            }
            $arrayValues = array();
            foreach ($array as $value) {
                if (is_array($value)) {
                    foreach ($value as $val) {
                        if (is_array($val)) {
                            foreach ($val as $v) {
                                $arrayValues[] = $v;
                            }
                        } else {
                            $arrayValues[] = $val;
                        }
                    }
                } else {
                    $arrayValues[] = $value;
                }
            }
            return $arrayValues;
		}
	}
	
//	session_start();
	//public static function setup(){
		$fullarray=array();
	
		$mysqli = new mysqli('localhost:8889','root','root','Ginkgo');
		if($mysqli->connect_errno){
			echo ('Error connecting to MySQL server. '.$mysqli->connect_errno .' '.$mysqli->connect_error);
		}
//#region get variables related to calendar
		//get calendar list
		$sql = "SELECT * FROM cal";
		$result = mysqli_query($mysqli,$sql) or die('Error querying cal database.');
		$caldate = array();
		$caldate[0]=0;
		$i=1;
		while ($row = mysqli_fetch_assoc($result)) {
			$caldate[$i]=$row['caldate'];
			$i=$i+1;
		}

		//get current date index
		$curdate = (12*(date('Y')-2015))+date('n');
//#endregion

//#region create trans and capex table
		//drop trans
		$sql = "TRUNCATE trans; ";
		mysqli_query($mysqli,$sql) or die('Error dropping ExpTrans database.');
		//insert expanded 0250
		$sql = "INSERT INTO trans (`ID0250`,`Date`,`Acct`,`OAcct`,`Category`,`Category2`,`Counterparty`,`RentDate`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN`,`caldate`) (SELECT `id`,`Date`,`Acct`,`Acct`,`Category`,`Category2`,`Counterparty`,`RentDate`,`Description`,`Location`,`Dep`,`DepConv`,`Date`,`Amount`,`ST`,`EN`,`caldate` FROM T0250 t1 left JOIN cal On cal.caldate >= date(concat(year(t1.ST),'-',month(t1.ST),'-1')) and cal.caldate <= t1.EN);";
		mysqli_query($mysqli,$sql) or die('Error expanding ExpTrans database.');
		//insert expanded 2586
		$sql = "INSERT INTO trans (`ID2586`,`Date`,`Acct`,`OAcct`,`Category`,`Category2`,`Counterparty`,`RentDate`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN`,`caldate`) (SELECT `id`,`Date`,`Acct`,`Acct`,`Category`,`Category2`,`Counterparty`,`RentDate`,`Description`,`Location`,`Dep`,`DepConv`,`Date`,`Amount`,`ST`,`EN`,`caldate` FROM T2586 t1 left JOIN cal On cal.caldate >= date(concat(year(t1.ST),'-',month(t1.ST),'-1')) and cal.caldate <= t1.EN);";
		mysqli_query($mysqli,$sql) or die('Error expanding ExpTrans database.');
		//insert expanded 7503
		$sql = "INSERT INTO trans (`ID7503`,`Date`,`Acct`,`OAcct`,`Category`,`Category2`,`Counterparty`,`RentDate`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN`,`caldate`) (SELECT `id`,`Date`,`Acct`,`Acct`,`Category`,`Category2`,`Counterparty`,`RentDate`,`Description`,`Location`,`Dep`,`DepConv`,`Date`,`Amount`,`ST`,`EN`,`caldate` FROM T7503EW t1 left JOIN cal On cal.caldate >= date(concat(year(t1.ST),'-',month(t1.ST),'-1')) and cal.caldate <= t1.EN);";
		mysqli_query($mysqli,$sql) or die('Error expanding ExpTrans database.');
		//insert expanded 8526
		$sql = "INSERT INTO trans (`ID8526`,`Date`,`Acct`,`OAcct`,`Category`,`Category2`,`Counterparty`,`RentDate`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN`,`caldate`) (SELECT `id`,`Date`,`Acct`,`Acct`,`Category`,`Category2`,`Counterparty`,`RentDate`,`Description`,`Location`,`Dep`,`DepConv`,`Date`,`Amount`,`ST`,`EN`,`caldate` FROM T8526 t1 left JOIN cal On cal.caldate >= date(concat(year(t1.ST),'-',month(t1.ST),'-1')) and cal.caldate <= t1.EN);";
		mysqli_query($mysqli,$sql) or die('Error expanding ExpTrans database.');
		//insert expanded Mtg
		$sql = "INSERT INTO trans (`IDMtg`,`Date`,`Acct`,`OAcct`,`Category`,`Category2`,`Counterparty`,`RentDate`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN`,`caldate`) (SELECT `id`,`Date`,`Acct`,`Acct`,`Category`,`Category2`,`Counterparty`,`RentDate`,`Description`,`Location`,`Dep`,`DepConv`,`Date`,`Amount`,`ST`,`EN`,`caldate` FROM TFU t1 left JOIN cal On cal.caldate >= date(concat(year(t1.ST),'-',month(t1.ST),'-1')) and cal.caldate <= t1.EN);";
		mysqli_query($mysqli,$sql) or die('Error expanding ExpTrans database.');
		//delete transfer records
		$sql = "DELETE FROM trans WHERE `Category` = 'Transfer';";
		mysqli_query($mysqli,$sql) or die('Error deleting Transfer from  ExpTrans database.');
		//add final date and if caldate is empty then use original date, otherwise, use caldate
		$sql = "UPDATE trans SET FDT = case when caldate is NULL then Date when caldate <= ST then ST else caldate end;";
		mysqli_query($mysqli,$sql) or die('Error calculating FDT in ExpTrans database.');
		//add final amount and calculate out the monthly amount, no capex
		$sql = "UPDATE trans set FAmt = if((ST is NULL),Amount, if(((month(EN) = month(caldate) and year(EN) = year(caldate))),(Amount/(datediff(EN,ST)+1))*(day(EN)-day(FDT)+1),(Amount/(datediff(EN,ST)+1))*(day(last_day(FDT))-day(FDT)+1)));";
		mysqli_query($mysqli,$sql) or die('Error calculating FAmt in ExpTrans database.');
		//insert market value from propvalue
		$sql = "INSERT INTO trans (`Category`,`FDT`,`FAmt`,`Counterparty`,`Acct`,`Location`) SELECT 'Value',caldate,Price,NULL,NULL,Location FROM propvalue as t1 cross JOIN cal as t2 WHERE t2.caldate >= t1.ST and t2.caldate <= t1.EN;";
		mysqli_query($mysqli,$sql) or die('Error adding propvalue into ExpTrans database.');
		//insert market rent from propvalue
		$sql = "INSERT INTO trans (`Category`,`FDT`,`FAmt`,`Counterparty`,`Acct`,`Location`) SELECT 'MktRent',caldate,Rent,NULL,NULL,Location FROM propvalue as t1 cross JOIN cal as t2 WHERE t2.caldate >= t1.ST and t2.caldate <= t1.EN;";
		mysqli_query($mysqli,$sql) or die('Error adding mkt rent into ExpTrans database.');
		//insert rent roll
		$sql = "INSERT INTO trans (`Category`,`Counterparty`,`FDT`,`FAmt`,`Acct`,`Location`) SELECT 'Tenant',Tenant,caldate,Rent,NULL,Location FROM rentroll t1 CROSS JOIN cal as t2 WHERE t2.caldate >= t1.ST and t2.caldate <= t1.EN;";
		mysqli_query($mysqli,$sql) or die('Error adding rent roll to ExpTrans database.');
		//calculate monthly depreciation
		$sql = "SELECT * FROM trans WHERE Category = 'Capital Improvement';";
		$result = mysqli_query($mysqli,$sql) or die('Error querying trans database.');
		$capex = array();
		$capex[0]=0;
		$i=1;
		while ($row = mysqli_fetch_assoc($result)) {
			$capex[$i]=array($row['FDT'],$row['Dep'],$row['DepConv'],$row['Amount'],$row['Actual'],$row['id']);
			$i=$i+1;
		}
//#endregion
//#region depreciation matrices
		$hy[3]=array(33.33,44.45,14.81,7.41);//half yr - 3 yr
		$hy[5]=array(20,32,19.20,11.52,11.52,5.76);//half yr - 5 yr
		$hy[7] = array(14.29,24.49,17.49,12.49,8.93,8.92,8.93,4.46);//half yr - 7 yr
		$hy[10] = array(10,18,14.4,11.52,9.22,7.37,6.55,6.55,6.56,6.55,3.28);//10 yr
		$hy[15]=array(5,9.5,8.55,7.7,6.93,6.23,5.9,5.9,5.91,5.9,5.91,5.9,5.91,5.9,5.91,2.95);//15 yr
		$hy[20] = array(3.75,7.219,6.677,6.177,5.713,5.285,4.888,4.522,4.462,4.461,4.462,4.461,4.462,4.461,4.462,4.461,4.462,4.461,4.462,4.461,2.231);
		$mq[1][3] = array(58.33,27.78,12.35,1.54);//mid quarter - 1st qtr 3 yr
		$mq[1][5] = array(35,26,15.6,11.01,11.01,1.38);
		$mq[1][7] = array(25,21.43,15.31,10.93,8.75,8.74,8.75,1.09);
		$mq[1][10]=array(17.5,16.5,13.2,10.56,8.45,6.76,6.55,6.55,6.56,6.55,0.82);
		$mq[1][15]=array(8.75,9.13,8.21,7.39,6.65,5.99,5.90,5.91,5.90,5.91,5.9,5.91,5.9,5.91,5.9,0.74);
		$mq[1][20]=array(6.563,7,6.482,5.996,5.546,5.13,4.746,4.459,4.459,4.459,4.459,4.46,4.459,4.46,4.459,4.46,4.459,4.46,4.459,4.46,0.565);
		$mq[2][3] = array(41.67,38.89,14.14,5.30);
		$mq[2][5] = array(25,30,18,11.37,11.37,4.26);
		$mq[2][7] = array(17.85,23.47,16.76,11.97,8.87,8.87,8.87,3.34);
		$mq[2][10]=array(12.5,17.5,14,11.2,8.96,7.17,6.55,6.55,6.56,6.55,2.46);
		$mq[2][15]=array(6.25,9.38,8.44,7.59,6.83,6.15,5.91,5.9,5.91,5.9,5.91,5.9,5.91,5.9,5.91,2.21);
		$mq[2][20]=array(4.688,7.148,6.612,6.116,5.658,5.233,4.841,4.478,4.463,4.463,4.463,4.463,4.463,4.463,4.462,4.463,4.462,4.463,4.462,4.463,1.673);
		$mq[3][3] = array(25,50,1.167,8.33);
		$mq[3][5] = array(15,34,20.4,12.24,11.3,7.06);
		$mq[3][7] = array(10.71,25.51,18.22,13.02,9.3,8.85,8.86,5.53);
		$mq[3][10]=array(7.5,18.5,14.8,11.84,9.47,7.58,6.55,6.55,6.56,6.55,4.10);
		$mq[3][15]=array(3.75,9.63,8.66,7.8,7.02,6.31,5.9,5.9,5.91,5.9,5.91,5.9,5.91,5.9,5.91,3.69);
		$mq[3][20]=array(2.813,7.289,6.742,6.237,5.769,5.336,4.936,4.566,4.46,4.46,4.46,4.46,4.461,4.46,4.461,4.46,4.461,4.46,4.461,4.46,2.788);
		$mq[4][3] = array(8.33,61.11,20.37,10.19);
		$mq[4][5] = array(5,38,22.8,13.68,10.94,9.58);
		$mq[4][7] = array(3.57,27.55,19.68,14.06,10.04,8.73,8.73,7.64);
		$mq[4][10]=array(2.5,19.5,15.6,12.48,9.98,7.99,6.55,6.55,6.56,6.55,5.74);
		$mq[4][15]=array(1.25,9.88,8.89,8,7.2,6.48,5.9,5.9,5.9,5.91,5.9,5.91,5.9,5.91,5.9,5.17);
		$mq[4][20]=array(0.938,7.43,6.872,6.357,5.88,5.439,5.031,4.654,4.458,4.458,4.458,4.458,4.458,4.458,4.458,4.458,4.458,4.459,4.458,4.459,3.901);
		$res[1] = array(3.485,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,1.97);
		$res[2] = array(3.182,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,2.273);
		$res[3] = array(2.879,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,2.576);
		$res[4] = array(2.576,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,2.879);
		$res[5] = array(2.273,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.182);
		$res[6] = array(1.97,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.485);
		$res[7] = array(1.667,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,0.152);
		$res[8] = array(1.364,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,0.455);
		$res[9] = array(1.061,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,0.758);
		$res[10] = array(0.758,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,1.061);
		$res[11] = array(0.455,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,1.364);
		$res[12] = array(0.152,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,3.637,3.636,1.667);
//#endregion

//#region create trans and capex table
		for($n=1;$n<count($capex);$n++){
			if($capex[$n][1]==27.5){
				$sql = "UPDATE trans SET FAmt = ".round(-$capex[$n][3]/100*($res[date("n",strtotime($capex[$n][4]))][date("Y",strtotime($capex[$n][0]))-date("Y",strtotime($capex[$n][4]))]),2)." WHERE id=".$capex[$n][5].";";
				mysqli_query($mysqli,$sql) or die('Error calculating FAmt in ExpTrans database.');
			}
			else if (strpos($capex[$n][2],'D') !== FALSE){ //if it is de minimis
				$sql = "UPDATE trans SET FAmt = ".(-$capex[$n][3])." WHERE id=".$capex[$n][5].";";
				mysqli_query($mysqli,$sql) or die('Error calculating FAmt in ExpTrans database.');
			}
			else{
				if(strpos($capex[$n][2],'Q') !== FALSE){  //if it is mid quarter
					//determine quarter
					if(date("n",strtotime($capex[$n][4]))%3==0)
						$qt = date("n",strtotime($capex[$n][4]))/3;
					else
						$qt = floor(date("n",strtotime($capex[$n][4]))/3)+1;
					$sql = "UPDATE trans SET FAmt = ".round(-$capex[$n][3]/100*($mq[$qt][floor($capex[$n][1])][date("Y",strtotime($capex[$n][0]))-date("Y",strtotime($capex[$n][4]))]),2)." WHERE id=".$capex[$n][5].";";
					mysqli_query($mysqli,$sql) or die('Error calculating FAmt in ExpTrans database.');
				}
				else { //if it is mid year
					$sql = "UPDATE trans SET FAmt = ".round(-$capex[$n][3]/100*($hy[floor($capex[$n][1])][date("Y",strtotime($capex[$n][0]))-date("Y",strtotime($capex[$n][4]))]),2)." WHERE id=".$capex[$n][5].";";
					mysqli_query($mysqli,$sql) or die('Error calculating FAmt in ExpTrans database.');
				}
			}
		}

		//drop capex
		$sql = "TRUNCATE capex; ";
		mysqli_query($mysqli,$sql) or die('Error dropping capex database.');
		//insert orig capex from 0250 into capex
		$sql = "INSERT INTO capex (`origid`,`Date`,`Category2`,`Counterparty`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN`) (SELECT `id`,`Date`,`Category2`,`Counterparty`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN` FROM trans t1 WHERE t1.`Category`='Capital Improvement' and `ID0250` is not NULL group by `ID0250`);";
		mysqli_query($mysqli,$sql) or die('Error adding capex.');
		//insert orig capex from 2586 into capex
		$sql = "INSERT INTO capex (`origid`,`Date`,`Category2`,`Counterparty`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN`) (SELECT `id`,`Date`,`Category2`,`Counterparty`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN` FROM trans t1 WHERE t1.`Category`='Capital Improvement' and `ID2586` is not NULL group by `ID2586`);	";
		mysqli_query($mysqli,$sql) or die('Error adding capex.');
		//insert orig capex from 7503 into capex
		$sql = "INSERT INTO capex (`origid`,`Date`,`Category2`,`Counterparty`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN`) (SELECT `id`,`Date`,`Category2`,`Counterparty`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN` FROM trans t1 WHERE t1.`Category`='Capital Improvement' and `ID7503` is not NULL group by `ID7503`);";
		mysqli_query($mysqli,$sql) or die('Error adding capex.');
		//insert orig capex from Mtg into capex
		$sql = "INSERT INTO capex (`origid`,`Date`,`Category2`,`Counterparty`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN`) (SELECT `id`,`Date`,`Category2`,`Counterparty`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN` FROM trans t1 WHERE t1.`Category`='Capital Improvement' and `IDMtg` is not NULL group by `IDMtg`);";
		mysqli_query($mysqli,$sql) or die('Error adding capex.');
		//insert orig capex from 8526 into capex
		$sql = "INSERT INTO capex (`origid`,`Date`,`Category2`,`Counterparty`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN`) (SELECT `id`,`Date`,`Category2`,`Counterparty`,`Description`,`Location`,`Dep`,`DepConv`,`Actual`,`Amount`,`ST`,`EN` FROM trans t1 WHERE t1.`Category`='Capital Improvement' and `ID8526` is not NULL group by `ID8526`);";
		mysqli_query($mysqli,$sql) or die('Error adding capex.');

//#endregion
//		}
		
	
	/*	
	//get the index of the current quarter
	$lastqt = 0;
	if(($curdate-1)%3==0)
		$lastqt=$curdate-2;
	else
		$lastqt = ($curdate-1)-($curdate-1)%3+1;
	
	//get the current year
	$lastyr = date("Y");
	*/

	
//#region get all properties and create arrays
	$numprops=1;
	$sql = "SELECT * FROM deets;";
	$result = mysqli_query($mysqli, $sql) or die('Error querying deet database.');

	while ($row = mysqli_fetch_assoc($result)) {
		${prop.$numprops}=array();
		${prop.$numprops}[0]=$curdate;
		${prop.$numprops}[1]=$row['Location'];
		${prop.$numprops}[2]=$row['City'];
		${prop.$numprops}[3]=$row['State'];
		${prop.$numprops}[4]=$row['Zip'];
		${prop.$numprops}[5]=$row['Yr'];
		${prop.$numprops}[6]=$row['SqFt'];
		${prop.$numprops}[7]=$row['Bd'];
		${prop.$numprops}[8]=$row['Subdivision'];
		${prop.$numprops}[9]=$row['Owner'];
		${prop.$numprops}[10]=$row['HOA'];
		${prop.$numprops}[11]=$row['HOAMgmt'];
		${prop.$numprops}[12]=$row['HOAAcct'];
		${prop.$numprops}[13]=$row['InsAcct'];
		${prop.$numprops}[14]=$row['ZPID'];
		${prop.$numprops}[15]=$row['RFID'];
		${prop.$numprops}[16]=$row['Page'];
		${prop.$numprops}[17]=$row['PurchDate'];
		${prop.$numprops}[18]=$row['Units'];
		${prop.$numprops}[19]=$row['UnitType'];
		${prop.$numprops}[20]=$row['Seller Broker Fee'];
		${prop.$numprops}[21]=$row['Seller Broker Pct'];
		${prop.$numprops}[22]=$row['Seller Settlement'];
		for($z=23;$z<=38;$z++)
			${prop.$numprops}[$z]=0;
		
		$numprops=$numprops+1;
	}
//#endregion

//#region for each property create property table
	for ($k=1; $k<$numprops; $k++){
		//get a list of unit numbers
		$unittype = ${prop.$k}[19];
		if($unittype == null){
			$units=array();
		}
		else{
			for($m=0; $m<${prop.$k}[18];$m++){
				$units[$m]=$unittype++;
			}
		}
		$i=1;

		//empty property table and add back in rows
		$sql = "TRUNCATE ".${prop.$k}[16]."; ";
		mysqli_query($mysqli,$sql) or die('Error dropping property database.');
		if(count($units)==0){
			$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Market Rent'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$i++;
		}
		else{
			for($m=0;$m<${prop.$k}[18];$m++){
			$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Market Rent ".$units[$m]."'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$i++;
			}
		}
		$gprrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Gross Potential Rent'); ";
		mysqli_query($mysqli,$sql) or die('Add gpr values.');
		$i++;
		$vacrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Vacancy'); ";
		mysqli_query($mysqli,$sql) or die('Add vacancy.');
		$i++;
		$rentrow=$i;
		if(count($units)==0){
			$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Rent'); ";
			mysqli_query($mysqli,$sql) or die('Add rent values.');
			$i++;
		}
		else{
			for($m=0;$m<${prop.$k}[18];$m++){
				$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Rent ".$units[$m]."'); ";
				mysqli_query($mysqli,$sql) or die('Add rent values.');
				$i++;
			}
		}
		$oirow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(+) Other Income'); ";
		mysqli_query($mysqli,$sql) or die('Add other income values.');
		$i++;
		$opexrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Operating Expenses'); ";
		mysqli_query($mysqli,$sql) or die('Add opex.');
		$i++;
		$taxrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Property Tax'); ";
		mysqli_query($mysqli,$sql) or die('Add prop tax.');
		$i++;
		$insrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Insurance'); ";
		mysqli_query($mysqli,$sql) or die('Add ins.');
		$i++;
		$hoarow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) HOA'); ";
		mysqli_query($mysqli,$sql) or die('Add hoa.');
		$i++;
		$mgmtrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Property Management'); ";
		mysqli_query($mysqli,$sql) or die('Add prop mgmt.');
		$i++;
		$utilrow=$i;
		if($unittype != null){
			for($m=0; $m<${prop.$k}[18];$m++){
				$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Utilities ".$units[$m]."'); ";
				mysqli_query($mysqli,$sql) or die('Add utilities.');
				$i++;
			}
		}
		$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Utilities'); ";
		mysqli_query($mysqli,$sql) or die('Add utilities.');
		$i++;
		$maintrow=$i;
		if($unittype != null){
			for($m=0; $m<${prop.$k}[18];$m++){
				$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Maintenance and Repair ".$units[$m]."'); ";
				mysqli_query($mysqli,$sql) or die('Add maint.');
				$i++;
			}
		}
		$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Maintenance and Repair'); ";
		mysqli_query($mysqli,$sql) or die('Add maint.');
		$i++;
		$noirow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'NOI'); ";
		mysqli_query($mysqli,$sql) or die('Add noi.');
		$i++;
		$tirow=$i;
		if(count($units)==0){
			$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Tenant Improvements'); ";
			mysqli_query($mysqli,$sql) or die('Add ti.');
			$i++;
		}
		else{
			for($m=0;$m<${prop.$k}[18];$m++){
				$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Tenant Improvements ".$units[$m]."'); ";
				mysqli_query($mysqli,$sql) or die('Add ti.');
				$i++;
			}
		}
		$lcrow=$i;
		if(count($units)==0){
			$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Leasing Commissions'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$i++;
		}
		else{
			for($m=0;$m<${prop.$k}[18];$m++){
				$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Leasing Commissions ".$units[$m]."'); ";
				mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
				$i++;
			}
		}
		$resrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Reserves'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$acqrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Acquisition'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$unlevrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Unlevered Cash Flow'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$loanrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(+) Loan Draw'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$loancostrow=$i;
		$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(+) Cost'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$pmtrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Principal'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$introw=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Interest'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$loanbalrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Loan Balance'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$levrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Levered Cash Flow'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$cirow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'(-) Capital Improvements'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$btcfrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Before Tax Cash Flow'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$deprow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Depreciation'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$baserow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Tax Basis'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		$i++;
		$mktrow=$i;
    	$sql = "INSERT INTO ".${prop.$k}[16]." (id,Category) VALUES (".$i.",'Market Value'); ";
		mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		
		$miniarray=array();
	
		//create miniarray with total number of rows
		$numrows = $i;
		//for($m=1;$m<=$numrows;$m++)
		//	$miniarray[$m]=0;
//#endregion
	
//#region for each property get transactions from trans, zero out quarter and annual arrays
		//get all transactions
		$sql = "SELECT * FROM trans WHERE Location LIKE '%".${prop.$k}[1]."%';";
		$result = mysqli_query($mysqli, $sql) or die('Error querying trans location database.');

		$total = array();
		$temp = array();
		$quarter = array();
		$annual = array();
		
		//zero out quarter and annual arrays
		for ($m=1;$m<=intdiv(86,3);$m++){
			for ($i=1;$i<=$numrows;$i++){
				$quarter[$i][$m] = 0;
			}
		}

		for ($m=1;$m<=8;$m++){
			for ($i=1;$i<=$numrows;$i++){
				$annual[$i][$m] = 0;
			}
		}
		$n=1;
		$o=1;
		$p=1;
	
		while ($row = mysqli_fetch_assoc($result)) {
			$total[]=array($row['FDT'],$row['Category'],$row['Category2'],$row['RentDate'],$row['FAmt'],$row['Location'],$row['Description'],$row['id']);
		}
		//initialize temp
		for ($m=1;$m<=$numrows;$m++){
			for($i=0;$i<count($caldate);$i++)
				$temp[$m][$i]=0;
		}
//#endregion

//#region fill out temp
		for ($j=0; $j<count($total);$j++){
			$i = (12*(date('Y',strtotime($total[$j][0]))-2015))+date('n',strtotime($total[$j][0]));
			switch ($total[$j][1]){
				case 'Acquisition':{
					switch($total[$j][2]){
						case 'Deposit':{
							${prop.$k}[32] = ${prop.$k}[32] - $total[$j][4];
							break;}
						case 'Credit':{
							${prop.$k}[33] = ${prop.$k}[33] - $total[$j][4];
							break;}
						case 'Option Money':{
							${prop.$k}[34] = ${prop.$k}[34] - $total[$j][4];
							break;}
						case 'Broker Commission':{
							${prop.$k}[36] = ${prop.$k}[36] - $total[$j][4];
							break;}
						case 'Title Company':{
							${prop.$k}[37] = ${prop.$k}[37] - $total[$j][4];
							break;}
						case 'Attorney':{
							${prop.$k}[23] = ${prop.$k}[23] - $total[$j][4];
							break;}
						case 'Title Insurance':{
							${prop.$k}[24] = ${prop.$k}[24] - $total[$j][4];
							break;}
						case 'Recording Fees':{
							${prop.$k}[25] = ${prop.$k}[25] - $total[$j][4];
							break;}
						case 'Survey':{
							${prop.$k}[26] = ${prop.$k}[26] - $total[$j][4];
							break;}
						case 'Inspection':{
							${prop.$k}[28] = ${prop.$k}[28] - $total[$j][4];
							break;}
						case 'Wire Fees':{
							${prop.$k}[29] = ${prop.$k}[29] - $total[$j][4];
							break;}
						case 'Other':{
							${prop.$k}[30] = ${prop.$k}[30] - $total[$j][4];
							break;}
						case 'Purchase Price':{
							${prop.$k}[31] = ${prop.$k}[31] - $total[$j][4];
							break;}
						}
					break;}
				case 'Tenant':{
					$temp[$gprrow][$i] = $temp[$gprrow][$i]+$total[$j][4];
					break;}
				case 'Other Income':{
					$temp[$oirow][$i] = $temp[$oirow][$i] + $total[$j][4];
					break;}
				case 'Property Tax':{
					$temp[$taxrow][$i] = $temp[$taxrow][$i] + $total[$j][4];
					if($total[$j][2]=="Acquisition"){
						${prop.$k}[35] = ${prop.$k}[35] - $total[$j][4];
					}
					break;}
				case 'Insurance':{
					$temp[$insrow][$i] = $temp[$insrow][$i] + $total[$j][4];
					break;}
				case 'HOA':{
					$temp[$hoarow][$i] = $temp[$hoarow][$i] + $total[$j][4];
					if($total[$j][2]=="Acquisition"){
						${prop.$k}[27] = ${prop.$k}[27] - $total[$j][4];
					}
					break;}
				case 'Property Management':{
					$temp[$mgmtrow][$i] = $temp[$mgmtrow][$i] + $total[$j][4];
					break;}
				case 'Utilities':{
					if($total[$j][5]==${prop.$k}[1]){
						$temp[$utilrow+count($units)][$i] = $temp[$utilrow+count($units)][$i] + $total[$j][4];
					}
					for($m=0;$m<count($units);$m++){
						if($total[$j][5]==(${prop.$k}[1]." ".$units[$m])){
							$temp[$utilrow+$m][$i] = $temp[$utilrow+$m][$i] + $total[$j][4];
						}
					}
					if($total[$j][2]=="Renovation")
						${prop.$k}[38] = ${prop.$k}[38] - $total[$j][4];
					break;}	
				case 'Maintenance and Repair':{
					if($total[$j][5]==${prop.$k}[1]){
						$temp[$maintrow+count($units)][$i] = $temp[$maintrow+count($units)][$i] + $total[$j][4];
					}
					for($m=0;$m<count($units);$m++){
						if($total[$j][5]==(${prop.$k}[1]." ".$units[$m])){
							$temp[$maintrow+$m][$i] = $temp[$maintrow+$m][$i] + $total[$j][4];
						}
					}
					if($total[$j][2]=="Renovation")
						${prop.$k}[38] = ${prop.$k}[38] - $total[$j][4];
					break;}
				case 'Tenant Improvement':{
					if($total[$j][5]==${prop.$k}[1]){
						$temp[$tirow+count($units)][$i] = $temp[$tirow+count($units)][$i] + $total[$j][4];
					}
					for($m=0;$m<count($units);$m++){
						if($total[$j][5]==(${prop.$k}[1]." ".$units[$m])){
							$temp[$tirow+$m][$i] = $temp[$tirow+$m][$i] + $total[$j][4];
						}
					}
					break;}
				case 'Leasing Commission':{
					if($total[$j][5]==${prop.$k}[1]){
						$temp[$lcrow+count($units)][$i] = $temp[$lcrow+count($units)][$i] + $total[$j][4];
					}
					for($m=0;$m<count($units);$m++){
						if($total[$j][5]==(${prop.$k}[1]." ".$units[$m])){
							$temp[$lcrow+$m][$i] = $temp[$lcrow+$m][$i] + $total[$j][4];
						}
					}
					break;}
				case 'Reserves':{
					$temp[$resrow][$i] = $temp[$resrow][$i] + $total[$j][4];
					break;}
				case 'Capital Improvement':{ //capital improv from trans is depreciation
					$temp[$deprow][$i] = $temp[$deprow][$i] + $total[$j][4];
					if($total[$j][2]=="Renovation")
						${prop.$k}[38] = ${prop.$k}[38] - $total[$j][4];
					break;}
				case 'Loan Draw':{
					if($total[$j][4]<=0)
						$temp[$loanrow][$i] = $temp[$loanrow][$i]-$total[$j][4];
					else
						$temp[$loanrow][$i] = $temp[$loanrow][$i]+$total[$j][4];
					break;}
				case 'Loan Cost':{
					if($total[$j][4]<=0)
						$temp[$loancostrow][$i] = $temp[$loancostrow][$i]-$total[$j][4];
					else
						$temp[$loancostrow][$i] = $temp[$loancostrow][$i]+$total[$j][4];
					break;}
				case 'Loan Interest':{
					$temp[$introw][$i] = $temp[$introw][$i] + $total[$j][4];
					break;}
				case 'Loan Principal':{
					if($total[$j][4]>=0)
						$temp[$pmtrow][$i] = $temp[$pmtrow][$i] - $total[$j][4];
					else
						$temp[$pmtrow][$i] = $temp[$pmtrow][$i] + $total[$j][4];
					break;}
				case 'Value':{
					$temp[$mktrow][$i] = $total[$j][4];
					break;}
				case 'MktRent':{
					if(count($units)==0){
						$temp[1][$i] =  $total[$j][4];
					}
					else{
						for($m=0;$m<count($units);$m++){
							if($total[$j][5]==(${prop.$k}[1]." ".$units[$m])){
								$temp[$m+1][$i] =  $total[$j][4];
							}
						}
					}
					break;}
				case 'Rent':{
					$l = (12*(date('Y',strtotime($total[$j][3]))-2015))+date('n',strtotime($total[$j][3]));
					if(count($units)==0){
						$temp[$rentrow][$l] = $temp[$rentrow][$l] + $total[$j][4];
					}
					else{
						for($m=0;$m<count($units);$m++){
							if($total[$j][5]==(${prop.$k}[1]." ".$units[$m])){
								$temp[$rentrow+$m][$l] = $temp[$rentrow+$m][$l] + $total[$j][4];
							}
						}
					}
				}
			}
		}

//		echo "att:".${prop.$k}[23]." ti:".${prop.$k}[24]." rec:".${prop.$k}[25]." sur:".${prop.$k}[26]." hoa:".${prop.$k}[27]." ins:".${prop.$k}[28]." wir:".${prop.$k}[29]." oth:".${prop.$k}[30]." pur:".${prop.$k}[31]." dep:".${prop.$k}[32]." cre:".${prop.$k}[33]." opt:".${prop.$k}[34]." tax:".${prop.$k}[35]." com:".${prop.$k}[36]." tc:".${prop.$k}[37]." ren:".${prop.$k}[38]."<br>";
		//set purchase price (does not include HOA and tax)
		$i = (12*(date('Y',strtotime(${prop.$k}[17]))-2015))+date('n',strtotime(${prop.$k}[17]));
		$purch = ${prop.$k}[23]+${prop.$k}[24]+${prop.$k}[25]+${prop.$k}[26]+${prop.$k}[28]+${prop.$k}[29]+${prop.$k}[30]+${prop.$k}[31]+${prop.$k}[32]+${prop.$k}[33]+${prop.$k}[34]+${prop.$k}[36]+${prop.$k}[37];
		$temp[$acqrow][$i] = -$purch;

		//add capex values in temp
		$sql = "SELECT * FROM capex WHERE Location LIKE '%".${prop.$k}[1]."%';";
		$result = mysqli_query($mysqli, $sql) or die('Error querying capex location database.');
		while ($row = mysqli_fetch_assoc($result)) {
			$origcapex[]=array($row['Date'],$row['Amount']);
		}

		for($j=0;$j<count($origcapex);$j++){
			$i = (12*(date('Y',strtotime($origcapex[$j][0]))-2015))+date('n',strtotime($origcapex[$j][0]));
			$temp[$cirow][$i] = $temp[$cirow][$i]+$origcapex[$j][1];
		}

		//calculate sums and fill out formulaic values
		for($j=1; $j<count($temp[1]); $j++){
			//for single unit properties
			if(count($units)==0){
				$temp[$vacrow][$j] = $temp[$rentrow][$j] - $temp[$gprrow][$j];
				$temp[$opexrow][$j]=$temp[$taxrow][$j]+$temp[$insrow][$j]+$temp[$hoarow][$j]+$temp[$mgmtrow][$j]+$temp[$utilrow][$j]+$temp[$maintrow][$j];
				$temp[$noirow][$j] = $temp[$rentrow][$j]+$temp[$oirow][$j]+$temp[$opexrow][$j];
				$temp[$unlevrow][$j] = $temp[$noirow][$j]+$temp[$tirow][$j]+$temp[$lcrow][$j]+$temp[$resrow][$j]+$temp[$acqrow][$j];
				$temp[$rentrow][0] = $temp[$rentrow][0]+$temp[$rentrow][$j];
				$temp[$utilrow][0] = $temp[$utilrow][0]+$temp[$utilrow][$j];
				$temp[$maintrow][0] = $temp[$maintrow][0]+$temp[$maintrow][$j];
				$temp[$tirow][0] = $temp[$tirow][0]+$temp[$tirow][$j];
				$temp[$lcrow][0] = $temp[$lcrow][0]+$temp[$lcrow][$j];
				$temp[1][0] = $temp[1][$curdate];
			}
			//for multi-unit properties
			else{
				for($m=0;$m<count($units);$m++){
					$temp[$m+1][0] = $temp[$m+1][$curdate];
					$rent = $temp[$rentrow+$m][$j]+$rent;
					$util = $temp[$utilrow+$m][$j]+$util;
					$maint = $temp[$maintrow+$m][$j]+$maint;
					$ti = $temp[$tirow+$m][$j]+$ti;
					$lc = $temp[$lcrow+$m][$j]+$lc;
					$temp[$rentrow+$m][0] = $temp[$rentrow+$m][0]+$temp[$rentrow+$m][$j];
					$temp[$utilrow+$m][0] = $temp[$utilrow+$m][0]+$temp[$utilrow+$m][$j];
					$temp[$maintrow+$m][0] = $temp[$maintrow+$m][0]+$temp[$maintrow+$m][$j];
					$temp[$tirow+$m][0] = $temp[$tirow+$m][0]+$temp[$tirow+$m][$j];
					$temp[$lcrow+$m][0] = $temp[$lcrow+$m][0] +$temp[$lcrow+$m][$j];
					$temp[$m+1][0] = $temp[$m+1][$curdate];
				}
				$temp[$vacrow][$j] = $rent - $temp[$gprrow][$j];
				$temp[$opexrow][$j]=$temp[$taxrow][$j]+$temp[$insrow][$j]+$temp[$hoarow][$j]+$temp[$mgmtrow][$j]+$util+$maint+$temp[$utilrow+count($units)][$j]+$temp[$maintrow+count($units)][$j];
				$temp[$noirow][$j] = $rent+$temp[$oirow][$j]+$temp[$opexrow][$j];
				$temp[$unlevrow][$j] = $temp[$noirow][$j]+$ti+$lc+$temp[$resrow][$j]+$temp[$acqrow][$j];
				$temp[$utilrow+count($units)][0] = $temp[$utilrow+count($units)][0]+$temp[$utilrow+count($units)][$j];
				$temp[$maintrow+count($units)][0] = $temp[$maintrow+count($units)][0]+$temp[$maintrow+count($units)][$j];
			}
			$rent=0;
			$util=0;
			$maint=0;
			$ti=0;
			$lc=0;
			$temp[$loanbalrow][$j] = round($temp[$loanbalrow][$j-1]+$temp[$loanrow][$j]+$temp[$loancostrow][$j]+$temp[$pmtrow][$j],2);
			$temp[$levrow][$j]=$temp[$unlevrow][$j]+$temp[$loanrow][$j]+$temp[$loancostrow][$j]+$temp[$pmtrow][$j]+$temp[$introw][$j];
			$temp[$btcfrow][$j]=$temp[$levrow][$j]+$temp[$cirow][$j];
			$temp[$baserow][$j] = $temp[$baserow][$j-1]-$temp[$acqrow][$j]+$temp[$deprow][$j];
			$temp[$gprrow][0] = $temp[$gprrow][0]+$temp[$gprrow][$j];
			$temp[$vacrow][0] = $temp[$vacrow][0]+$temp[$vacrow][$j];
			$temp[$oirow][0] = $temp[$oirow][0]+$temp[$oirow][$j];
			$temp[$opexrow][0] = $temp[$opexrow][0]+$temp[$opexrow][$j];
			$temp[$taxrow][0] = $temp[$taxrow][0]+$temp[$taxrow][$j];
			$temp[$insrow][0] = $temp[$insrow][0]+$temp[$insrow][$j];
			$temp[$mgmtrow][0] = $temp[$mgmtrow][0]+$temp[$mgmtrow][$j];
			$temp[$hoarow][0] = $temp[$hoarow][0]+$temp[$hoarow][$j];
			$temp[$noirow][0] = $temp[$noirow][0]+$temp[$noirow][$j];
			$temp[$resrow][0] = $temp[$resrow][0]+$temp[$resrow][$j];
			$temp[$acqrow][0] = $temp[$acqrow][0]+$temp[$acqrow][$j];
			$temp[$unlevrow][0]=$temp[$unlevrow][0]+$temp[$unlevrow][$j];
			$temp[$loanrow][0] = $temp[$loanrow][0]+$temp[$loanrow][$j];
			$temp[$loancostrow][0] = $temp[$loancostrow][0]+$temp[$loancostrow][$j];
			$temp[$pmtrow][0] = $temp[$pmtrow][0]+$temp[$pmtrow][$j];
			$temp[$introw][0] = $temp[$introw][0]+$temp[$introw][$j];
			$temp[$loanbalrow][0] = $temp[$loanbalrow][$curdate];
			$temp[$levrow][0]=$temp[$levrow][0]+$temp[$levrow][$j];
			$temp[$cirow][0]=$temp[$cirow][0]+$temp[$cirow][$j];
			$temp[$deprow][0]=$temp[$deprow][0]+$temp[$deprow][$j];
			$temp[$btcfrow][0]=$temp[$btcfrow][0]+$temp[$btcfrow][$j];
			$temp[$baserow][0] = $temp[$baserow][$curdate];
			$temp[$mktrow][0] = $temp[$mktrow][$curdate];

			//fill out annual and quarterly arrays
			for ($z=1;$z<=$numrows;$z++){
				if($o<=count($quarter[1]))
					$quarter[$z][$o] = $quarter[$z][$o] + $temp[$z][$j];
				$annual[$z][$p] = $annual[$z][$p] + $temp[$z][$j];
			}
			if($j%3==0){
				$quarter[$loanbalrow][$o] = $temp[$loanbalrow][$j];
				$quarter[$baserow][$o] = $temp[$baserow][$j];
				$quarter[$mktrow][$o] = $temp[$mktrow][$j];
			}
			if($j%12==0){
				$annual[$loanbalrow][$p] = $temp[$loanbalrow][$j];
				$annual[$baserow][$p] = $temp[$baserow][$j];
				$annual[$mktrow][$p] = $temp[$mktrow][$j];
			}
			if($n%3==0)
				$o=$o+1;
			if($n%12==0)
				$p=$p+1;
			$n = $n+1;
		}

		for($j=1;$j<=count($temp);$j++){
			for($m=0;$m<count($temp[$j]);$m++){
				if($m==0){
					$sql = "UPDATE ".${prop.$k}[16]." SET Sum=".$temp[$j][$m]." WHERE id =".$j.";";
					mysqli_query($mysqli,$sql) or die('Error sum');
					$miniarray[0][$j]=$temp[$j][$m];
				}
				else{
					$sql = "UPDATE ".${prop.$k}[16]." SET `".$caldate[$m]."`=".$temp[$j][$m]." WHERE id = ".$j.";";
					mysqli_query($mysqli,$sql) or die('Error adding mkt rent.');
				}
			}
		}

		for($j=1;$j<=count($quarter);$j++){
			$l=0;
			for($m=1;$m<=count($quarter[$j]);$m++){
				if($m<=4)
					$sql = "UPDATE ".${prop.$k}[16]." SET `".(2015+$l)." Q".$m."`=".$quarter[$j][$m]." WHERE id =".$j.";";
				else{
					if(($m%4)!=0)
						$sql = "UPDATE ".${prop.$k}[16]." SET `".(2015+$l)." Q".($m%4)."`=".$quarter[$j][$m]." WHERE id =".$j.";";
					else
						$sql = "UPDATE ".${prop.$k}[16]." SET `".(2015+$l)." Q4` =".$quarter[$j][$m]." WHERE id =".$j.";";
				}
				mysqli_query($mysqli,$sql) or die('Error adding quarter.');
				if(($m%4)==0)
					$l++;
			}
		}

		for($j=1;$j<=count($annual);$j++){//row id
			for($m=1;$m<count($annual[$j]);$m++){//col date
				$sql = "UPDATE ".${prop.$k}[16]." SET `".(2015+$m-1)."`=".$annual[$j][$m]." WHERE id =".$j.";";
				mysqli_query($mysqli,$sql) or die('Error adding annual.');
			}
		}
//#endregion

		//index of purchase date
		$p = (12*(date('Y',strtotime(${prop.$k}[17]))-2015))+date('n',strtotime(${prop.$k}[17]));

		//irr
		$irr=array();
		$cf=0;
		for($j=$p;$j<=$curdate;$j++){
			$irr[$j-$p]=$temp[26][$j];
			$cf = $cf+$temp[26][$j];
		}

		$irr[$curdate-$p] = $irr[$curdate-$p]+$temp[29][$curdate];
		$cf = $cf+$temp[29][$curdate];

		//echo ((pow((PHPExcel_Calculation_Financial::IRR($irr,0.1)+1),12)-1)*100);
		//echo '<pre>'; print_r($irr); echo '</pre>';

		//echo count($temp);
		$a=1;
		for($j=((round($curdate/12,0)*12)+1);$j<=$curdate;$j++){
			for($l=1; $l<=count($temp);$l++){
				$miniarray[$a][$l] = $temp[$l][$j];
			}
			$a++;
		}
		$miniarray[4][$gprrow]=$temp[$gprrow][$curdate]; //rent
		$miniarray[4][$taxrow]=$temp[$taxrow][$curdate-12]; //property tax from 12 months ago
		$miniarray[4][$insrow]=$temp[$insrow][$curdate]; //insurance
		$miniarray[4][$hoarow]=$temp[$hoarow][$curdate]; //hoa
		$miniarray[4][$mgmtrow]=$temp[$mgmtrow][$curdate]; //mgmt
		$miniarray[4][$maintrow]=$purch*0.01/12; //maint
		$miniarray[4][$lcrow]=$temp[$gprrow][$curdate]; //lc
		$miniarray[4][$mktrow]=$temp[$mktrow][$curdate]; //mkt value

		//echo '<pre>'; print_r($irr); echo '</pre>';
		echo 'miniarray<br>';
		echo '<pre>'; print_r($miniarray); echo '</pre>';
		echo 'prop<br>';
		echo '<pre>'; print_r($miniarray); echo '</pre>';

		${prop.$k}[39] = $miniarray;
		${prop.$k}[40] = ((pow((PHPExcel_Calculation_Financial::IRR($irr,0.1)+1),12)-1)*100);
		${prop.$k}[41] =  (($cf+$temp[26][$p])/(-$temp[26][$p]))*100;
		$irr[$curdate-$p] = round($irr[$curdate-$p]-$temp[29][$curdate],2);
		${prop.$k}[42] =  -$temp[26][$p];
		${prop.$k}[43] = $irr;

		echo 'prop<br>';
		echo '<pre>'; print_r(${prop.$k}); echo '</pre>';


	//	$miniarray[1][1]
	/*	$miniarray=array();
		
		for($i=1;$i<=$numrows;$i++)
			$miniarray[$i]=0;
		
		for($z=$lastqt;$z<$dt;$z++){
			for($y=2;$y<=24;$y++){
				$miniarray[$y] = $miniarray[$y]+$temp[$y][$z];
			}
		}
		$miniarray[1] = $temp[1][$dt-1];
		$miniarray[25] = $temp[25][$dt-1];
		
		${prop.$k}[38] = $miniarray;
		
		$miniarray=array();
		
		for($i=1;$i<=25;$i++)
			$miniarray[$i]=0;
		
		$start = 12*($lastyr-2015)+1;
		for($z=$start;$z<$dt;$z++){
			for($y=2;$y<=24;$y++){
				$miniarray[$y] = $miniarray[$y] + $temp[$y][$z];
			}
		}
		$miniarray[1] = $temp[1][$dt-1];
		$miniarray[25] = $temp[25][$dt-1];
		
		${prop.$k}[39] = $miniarray;
		
		${prop.$k}[40] = $temp[1][$dt-1];
		
		$sql = "SELECT Amount FROM trans WHERE `Category`='Property Tax' AND `Category2` is NULL AND 
				`DESCRIPTION`='".($lastyr-1)."' AND `Location` LIKE '%".${prop.$k}[1]."%';";
		$result = mysqli_query($mysqli,$sql) or die('Error querying cal database.');
		while ($row = mysqli_fetch_assoc($result)) {
			${prop.$k}[41]=${prop.$k}[41]+$row['Amount'];
		}
		
		$sql = "SELECT Amount FROM trans WHERE `Category`='Insurance' AND `Category2` is NULL AND 
				`DESCRIPTION`='".($lastyr-1)."' AND `Location` LIKE '%".${prop.$k}[1]."%';";
		$result = mysqli_query($mysqli,$sql) or die('Error querying cal database.');
		while ($row = mysqli_fetch_assoc($result)) {
			${prop.$k}[42]=${prop.$k}[42]+$row['Amount'];
		}
		
		$sql = "SELECT Amount FROM trans WHERE `Category`='HOA' AND `Category2` is NULL AND 
				`DESCRIPTION`='".($lastyr-1)."' AND `Location` LIKE '%".${prop.$k}[1]."%';";
		$result = mysqli_query($mysqli,$sql) or die('Error querying cal database.');
		while ($row = mysqli_fetch_assoc($result)) {
			${prop.$k}[43]=${prop.$k}[43]+$row['Amount'];
		}

		${prop.$k}[44] = ${prop.$k}[37][11]/($dt-1);
		
		${prop.$k}[45] = $temp[25][$dt-1];
	*/	
	}
	
	$mysqli->close();
	
	for($k=1;$k<$numprops;$k++){
		$fullarray[$k] = ${prop.$k};
	}
		
	$_SESSION['fullarray']=$fullarray;
	$_SESSION['miniarray']=$miniarray;
	
	for($k=0;$k<$numprops;$k++){
		${prop.$k} = null;
	}
	$z=null;
	$sql=null;
	$annual=null;
	$quarter=null;
	$temp = null;
	$total=null;
	$fullarray=null;
	$mysqli = null;
	$d = null;
	$result = null;
	$caldate  = null;
	$lastyr = null;
	$numprops = null;
	$row = null;
	$lastqt = null;
	$i= null;
	$prop = null;
	$purch = null;
	$nummonths = null;
	$miniarray=null;
	$start=null;
?>