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

	if(isset($_POST['function2call']) && !empty($_POST['function2call'])) {
		$function2call = $_POST['function2call'];
		switch($function2call) {
			case 'setup' : createdata::setup($_POST['prop']);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();
	class createdata{
		public static function setup($property){
			$fullarray=array();
			$miniarray=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 get deet info on property
			$sql = "SELECT * FROM deets WHERE Page = '".$property."';";
			$result = mysqli_query($mysqli, $sql) or die('Error querying deet database.');

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

		//#region get list of unit numbers
			$unittype = $prop[19];
			if($unittype == null){
				$units=array();
			}
			else{
				for($m=0; $m<$prop[18];$m++){
					$units[$m]=$unittype++;
				}
			}
		//#endregion

		//#region empty property table and add back in rows
			$numrows=1;
			$sql = "TRUNCATE ".$prop[16]."; ";
			mysqli_query($mysqli,$sql) or die('Error dropping property database.');
			if(count($units)==0){
				$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Market Rent'); ";
				mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
				$numrows++;
			}
			else{
				for($m=0;$m<$prop[18];$m++){
				$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Market Rent ".$units[$m]."'); ";
				mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
				$numrows++;
				}
			}
			$gprrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Gross Potential Rent'); ";
			mysqli_query($mysqli,$sql) or die('Add gpr values.');
			$numrows++;
			$vacrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Vacancy'); ";
			mysqli_query($mysqli,$sql) or die('Add vacancy.');
			$numrows++;
			$rentrow=$numrows;
			if(count($units)==0){
				$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Rent'); ";
				mysqli_query($mysqli,$sql) or die('Add rent values.');
				$numrows++;
			}
			else{
				for($m=0;$m<$prop[18];$m++){
					$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Rent ".$units[$m]."'); ";
					mysqli_query($mysqli,$sql) or die('Add rent values.');
					$numrows++;
				}
			}
			$oirow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(+) Other Income'); ";
			mysqli_query($mysqli,$sql) or die('Add other income values.');
			$numrows++;
			$opexrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Operating Expenses'); ";
			mysqli_query($mysqli,$sql) or die('Add opex.');
			$numrows++;
			$taxrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Property Tax'); ";
			mysqli_query($mysqli,$sql) or die('Add prop tax.');
			$numrows++;
			$insrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Insurance'); ";
			mysqli_query($mysqli,$sql) or die('Add ins.');
			$numrows++;
			$hoarow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) HOA'); ";
			mysqli_query($mysqli,$sql) or die('Add hoa.');
			$numrows++;
			$mgmtrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Property Management'); ";
			mysqli_query($mysqli,$sql) or die('Add prop mgmt.');
			$numrows++;
			$utilrow=$numrows;
			if($unittype != null){
				for($m=0; $m<$prop[18];$m++){
					$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Utilities ".$units[$m]."'); ";
					mysqli_query($mysqli,$sql) or die('Add utilities.');
					$numrows++;
				}
			}
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Utilities'); ";
			mysqli_query($mysqli,$sql) or die('Add utilities.');
			$numrows++;
			$maintrow=$numrows;
			if($unittype != null){
				for($m=0; $m<$prop[18];$m++){
					$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Maintenance and Repair ".$units[$m]."'); ";
					mysqli_query($mysqli,$sql) or die('Add maint.');
					$numrows++;
				}
			}
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Maintenance and Repair'); ";
			mysqli_query($mysqli,$sql) or die('Add maint.');
			$numrows++;
			$noirow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'NOI'); ";
			mysqli_query($mysqli,$sql) or die('Add noi.');
			$numrows++;
			$tirow=$numrows;
			if(count($units)==0){
				$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Tenant Improvements'); ";
				mysqli_query($mysqli,$sql) or die('Add ti.');
				$numrows++;
			}
			else{
				for($m=0;$m<$prop[18];$m++){
					$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Tenant Improvements ".$units[$m]."'); ";
					mysqli_query($mysqli,$sql) or die('Add ti.');
					$numrows++;
				}
			}
			$lcrow=$numrows;
			if(count($units)==0){
				$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Leasing Commissions'); ";
				mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
				$numrows++;
			}
			else{
				for($m=0;$m<$prop[18];$m++){
					$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Leasing Commissions ".$units[$m]."'); ";
					mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
					$numrows++;
				}
			}
			$resrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Reserves'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$acqrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Acquisition'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$unlevrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Unlevered Cash Flow'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$loanrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(+) Loan Draw'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$loancostrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(+) Cost'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$pmtrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Principal'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$introw=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Interest'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$loanbalrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Loan Balance'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$levrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Levered Cash Flow'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$cirow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'(-) Capital Improvements'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$btcfrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Before Tax Cash Flow'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$deprow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Depreciation'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$baserow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Tax Basis'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
			$numrows++;
			$mktrow=$numrows;
			$sql = "INSERT INTO ".$prop[16]." (id,Category) VALUES (".$numrows.",'Market Value'); ";
			mysqli_query($mysqli,$sql) or die('Add mkt rent values.');
		//#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[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, annual, and quarterly arrays
			//go through all transactions and 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[32] = $prop[32] - $total[$j][4];
								break;}
							case 'Credit':{
								$prop[33] = $prop[33] - $total[$j][4];
								break;}
							case 'Option Money':{
								$prop[34] = $prop[34] - $total[$j][4];
								break;}
							case 'Broker Commission':{
								$prop[36] = $prop[36] - $total[$j][4];
								break;}
							case 'Title Company':{
								$prop[37] = $prop[37] - $total[$j][4];
								break;}
							case 'Attorney':{
								$prop[23] = $prop[23] - $total[$j][4];
								break;}
							case 'Title Insurance':{
								$prop[24] = $prop[24] - $total[$j][4];
								break;}
							case 'Recording Fees':{
								$prop[25] = $prop[25] - $total[$j][4];
								break;}
							case 'Survey':{
								$prop[26] = $prop[26] - $total[$j][4];
								break;}
							case 'Inspection':{
								$prop[28] = $prop[28] - $total[$j][4];
								break;}
							case 'Wire Fees':{
								$prop[29] = $prop[29] - $total[$j][4];
								break;}
							case 'Other':{
								$prop[30] = $prop[30] - $total[$j][4];
								break;}
							case 'Purchase Price':{
								$prop[31] = $prop[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[35] = $prop[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[27] = $prop[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[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[1]." ".$units[$m])){
								$temp[$utilrow+$m][$i] = $temp[$utilrow+$m][$i] + $total[$j][4];
							}
						}
						if($total[$j][2]=="Renovation")
							$prop[38] = $prop[38] - $total[$j][4];
						break;}	
					case 'Maintenance and Repair':{
						if($total[$j][5]==$prop[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[1]." ".$units[$m])){
								$temp[$maintrow+$m][$i] = $temp[$maintrow+$m][$i] + $total[$j][4];
							}
						}
						if($total[$j][2]=="Renovation")
							$prop[38] = $prop[38] - $total[$j][4];
						break;}
					case 'Tenant Improvement':{
						if($total[$j][5]==$prop[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[1]." ".$units[$m])){
								$temp[$tirow+$m][$i] = $temp[$tirow+$m][$i] + $total[$j][4];
							}
						}
						break;}
					case 'Leasing Commission':{
						if($total[$j][5]==$prop[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[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[38] = $prop[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[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[1]." ".$units[$m])){
									$temp[$rentrow+$m][$l] = $temp[$rentrow+$m][$l] + $total[$j][4];
								}
							}
						}
					}
				}
			}

			//set purchase price (does not include HOA and tax)
			$purchdate = (12*(date('Y',strtotime($prop[17]))-2015))+date('n',strtotime($prop[17]));
			$purch = $prop[23]+$prop[24]+$prop[25]+$prop[26]+$prop[28]+$prop[29]+$prop[30]+$prop[31]+$prop[32]+$prop[33]+$prop[34]+$prop[36]+$prop[37];
			$temp[$acqrow][$purchdate] = -$purch;

			//add capex values in temp
			$sql = "SELECT * FROM capex WHERE Location LIKE '%".$prop[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;
			}
		//#end region

		//#region fill out table
			for($j=1;$j<=count($temp);$j++){
				for($m=0;$m<count($temp[$j]);$m++){
					if($m==0){
						$sql = "UPDATE ".$prop[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[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[16]." SET `".(2015+$l)." Q".$m."`=".$quarter[$j][$m]." WHERE id =".$j.";";
					else{
						if(($m%4)!=0)
							$sql = "UPDATE ".$prop[16]." SET `".(2015+$l)." Q".($m%4)."`=".$quarter[$j][$m]." WHERE id =".$j.";";
						else
							$sql = "UPDATE ".$prop[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[16]." SET `".(2015+$m-1)."`=".$annual[$j][$m]." WHERE id =".$j.";";
					mysqli_query($mysqli,$sql) or die('Error adding annual.');
				}
			}
		//#endregion

		//#region calculate irr
			$irr=array();
			$cf=0;
			for($j=$purchdate;$j<=$curdate;$j++){
				$irr[$j-$purchdate]=$temp[$btcfrow][$j];
				$cf = $cf+$temp[$btcfrow][$j];
			}

			$irr[$curdate-$purchdate] = $irr[$curdate-$purchdate]+$temp[$mktrow][$curdate];
			$cf = $cf+$temp[$mktrow][$curdate];

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

		//#region fill out miniarray and prop
			//add the last quarter info into miniarray
			$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++;
			}
			//add basis amounts for extrapolation
			$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>';

			$prop[39] = $miniarray;
			$prop[40] = ((pow((PHPExcel_Calculation_Financial::IRR($irr,0.1)+1),12)-1)*100);
			$prop[41] =  ($cf/(-$temp[$btcfrow][$purchdate]))*100;
			$irr[$curdate-$purchdate] = round($irr[$curdate-$purchdate]-$temp[$mktrow][$curdate],2);
			$prop[42] =  -$temp[$acqrow][$purchdate];
			$prop[43] = $irr;

			//echo 'prop<br>';
			//echo '<pre>'; print_r($prop); echo '</pre>';
		//#endregion
	
		$mysqli->close();
	
		}
	}
/*	
	//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 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
*/

//#region for each property create property table
//	for ($k=1; $k<$numprops; $k++){
		//create miniarray with total number of rows
		
		//for($m=1;$m<=$numrows;$m++)
		//	$miniarray[$m]=0;
//#endregion

	//	$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[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[39] = $miniarray;
		
		$prop[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[1]."%';";
		$result = mysqli_query($mysqli,$sql) or die('Error querying cal database.');
		while ($row = mysqli_fetch_assoc($result)) {
			$prop[41]=$prop[41]+$row['Amount'];
		}
		
		$sql = "SELECT Amount FROM trans WHERE `Category`='Insurance' AND `Category2` is NULL AND 
				`DESCRIPTION`='".($lastyr-1)."' AND `Location` LIKE '%".$prop[1]."%';";
		$result = mysqli_query($mysqli,$sql) or die('Error querying cal database.');
		while ($row = mysqli_fetch_assoc($result)) {
			$prop[42]=$prop[42]+$row['Amount'];
		}
		
		$sql = "SELECT Amount FROM trans WHERE `Category`='HOA' AND `Category2` is NULL AND 
				`DESCRIPTION`='".($lastyr-1)."' AND `Location` LIKE '%".$prop[1]."%';";
		$result = mysqli_query($mysqli,$sql) or die('Error querying cal database.');
		while ($row = mysqli_fetch_assoc($result)) {
			$prop[43]=$prop[43]+$row['Amount'];
		}

		$prop[44] = $prop[37][11]/($dt-1);
		
		$prop[45] = $temp[25][$dt-1];
	*/	
//	}
	
/*	for($k=1;$k<$numprops;$k++){
		$fullarray[$k] = $prop;
	}
		
	$_SESSION['fullarray']=$fullarray;
	$_SESSION['miniarray']=$miniarray;
	
	for($k=0;$k<$numprops;$k++){
		$prop = 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;
	*/
?>