API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Sep 6th, 2018
135
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 88.54 KB | None | 0 0
raw download clone embed print report
  1. <?php
  2. class SummaryClass
  3. {
  4.     private $con;
  5.     private $dbHost = DB_HOST;
  6.     private $dbUser = DB_USER;
  7.     private $dbPassword = DB_PASS;
  8.     private $dbName = DB_NAME;
  9.  
  10.     public function __construct()
  11.     {
  12.         $this->con = mysqli_connect($this->dbHost, $this->dbUser, $this->dbPassword, $this->dbName)
  13.             or die(mysqli_connect_error());
  14.     }
  15.  
  16.     /*------------------------------------------------------------------
  17.     [Select currency symbol]
  18.     -------------------------------------------------------------------*/
  19.  
  20.     public function toCurrency()
  21.     {
  22.         if (strpos($_SESSION['currency'], "dollar") == true)
  23.             return " dollar";
  24.         else if (strpos($_SESSION['currency'], "euro") == true)
  25.             return " euro";
  26.         else if (strpos($_SESSION['currency'], "lira") == true)
  27.             return " lira";
  28.         else if (strpos($_SESSION['currency'], "pound") == true)
  29.             return " pound";
  30.         else if (strpos($_SESSION['currency'], "ruble") == true)
  31.             return " ruble";
  32.         else if (strpos($_SESSION['currency'], "rupee") == true)
  33.             return " rupee";
  34.         else if (strpos($_SESSION['currency'], "won") == true)
  35.             return " won";
  36.         else
  37.             return " yen";
  38.     }
  39.  
  40.     /*------------------------------------------------------------------
  41.     [Convert number to money format with error check]
  42.     -------------------------------------------------------------------*/
  43.  
  44.     public function toMoney($val, $r = 2)
  45.     {
  46.         $symbol = $_SESSION['currency'];
  47.         $n      = $val;
  48.         $c      = is_float($n) ? 1 : number_format($n, $r);
  49.         $d      = '.';
  50.         $t      = ',';
  51.         $sign   = ($n < 0) ? '-' : '';
  52.         $i      = $n = number_format(abs($n), $r);
  53.         $j      = (($j = $i . length) > 3) ? $j % 3 : 0;
  54.         $Mon    = $sign . ($j ? substr($i, 0, $j) + $t : '') . preg_replace('/(\d{3})(?=\d)/', "$1" + $t, substr($i, $j));
  55.         if ($Mon < 0)
  56.             return "<div class='balance-error'>" . $Mon . " " . $symbol . "</div>";
  57.         else
  58.             return $Mon . " " . $symbol;
  59.     }
  60.  
  61.     /*------------------------------------------------------------------
  62.     [Convert number to words]
  63.     -------------------------------------------------------------------*/
  64.     public function convert_number_to_words($number)
  65.     {
  66.         $hyphen      = '-';
  67.         $conjunction = ' and ';
  68.         $separator   = ', ';
  69.         $negative    = 'negative ';
  70.         $decimal     = ' point ';
  71.         $dictionary  = array(
  72.             0 => 'zero',
  73.             1 => 'one',
  74.             2 => 'two',
  75.             3 => 'three',
  76.             4 => 'four',
  77.             5 => 'five',
  78.             6 => 'six',
  79.             7 => 'seven',
  80.             8 => 'eight',
  81.             9 => 'nine',
  82.             10 => 'ten',
  83.             11 => 'eleven',
  84.             12 => 'twelve',
  85.             13 => 'thirteen',
  86.             14 => 'fourteen',
  87.             15 => 'fifteen',
  88.             16 => 'sixteen',
  89.             17 => 'seventeen',
  90.             18 => 'eighteen',
  91.             19 => 'nineteen',
  92.             20 => 'twenty',
  93.             30 => 'thirty',
  94.             40 => 'fourty',
  95.             50 => 'fifty',
  96.             60 => 'sixty',
  97.             70 => 'seventy',
  98.             80 => 'eighty',
  99.             90 => 'ninety',
  100.             100 => 'hundred',
  101.             1000 => 'thousand',
  102.             1000000 => 'million',
  103.             1000000000 => 'billion',
  104.             1000000000000 => 'trillion',
  105.             1000000000000000 => 'quadrillion',
  106.             1000000000000000000 => 'quintillion'
  107.         );
  108.         if (!is_numeric($number)) {
  109.             return false;
  110.         }
  111.         if (($number >= 0 && (int) $number < 0) || (int) $number < 0 - PHP_INT_MAX) {
  112.             trigger_error('convert_number_to_words only accepts numbers between -' . PHP_INT_MAX
  113.                 . ' and ' . PHP_INT_MAX, E_USER_WARNING);
  114.             return false;
  115.         }
  116.        
  117.         if ($number < 0) {
  118.             return $negative . $this->convert_number_to_words(abs($number));
  119.         }
  120.        
  121.         $string = $fraction = null;
  122.        
  123.         if (strpos($number, '.') !== false) {
  124.             list($number, $fraction) = explode('.', $number);
  125.         }
  126.        
  127.         switch (true) {
  128.             case $number < 21:
  129.                 $string = $dictionary[$number];
  130.                 break;
  131.             case $number < 100:
  132.                 $tens   = ((int) ($number / 10)) * 10;
  133.                 $units  = $number % 10;
  134.                 $string = $dictionary[$tens];
  135.                 if ($units) {
  136.                     $string .= $hyphen . $dictionary[$units];
  137.                 }
  138.                 break;
  139.             case $number < 1000:
  140.                 $hundreds  = $number / 100;
  141.                 $remainder = $number % 100;
  142.                 $string    = $dictionary[$hundreds] . ' ' . $dictionary[100];
  143.                 if ($remainder) {
  144.                     $string .= $conjunction . $this->convert_number_to_words($remainder);
  145.                 }
  146.                 break;
  147.             default:
  148.                 $baseUnit     = pow(1000, floor(log($number, 1000)));
  149.                 $numBaseUnits = (int) ($number / $baseUnit);
  150.                 $remainder    = $number % $baseUnit;
  151.                 $string       = $this->convert_number_to_words($numBaseUnits) . ' ' . $dictionary[$baseUnit];
  152.                 if ($remainder) {
  153.                     $string .= $remainder < 100 ? $conjunction : $separator;
  154.                     $string .= $this->convert_number_to_words($remainder);
  155.                 }
  156.                 break;
  157.         }
  158.         if (null !== $fraction && is_numeric($fraction)) {
  159.             $string .= $decimal;
  160.             $words = array();
  161.             foreach (str_split((string) $fraction) as $number) {
  162.                 $words[] = $dictionary[$number];
  163.             }
  164.             $string .= implode(' ', $words);
  165.         }
  166.        
  167.         return $string;
  168.     }
  169.  
  170.     /*------------------------------------------------------------------
  171.     [Create Income Statement summary]
  172.     -------------------------------------------------------------------*/
  173.  
  174.     public function createIncomeSummary($GrossRev, $TotalExpenses, $Ebitda, $OtherExpenses, $NetIncome)
  175.     {
  176.         $string = "";
  177.         $string = "Total revenue earned for the period is " . $this->convert_number_to_words($GrossRev)
  178.             . $this->toCurrency() . ".";
  179.         $string .= " Total direct expense was " . $this->convert_number_to_words($TotalExpenses * -1) . $this->toCurrency()
  180.             . ", total indirect expense was " . $this->convert_number_to_words($OtherExpenses * -1) . $this->toCurrency()
  181.             . " resulting in net income of " . $this->convert_number_to_words($NetIncome) . $this->toCurrency();
  182.         $string .= " and EBITDA was " . $this->convert_number_to_words($Ebitda) . $this->toCurrency() . ".";
  183.         return $string;
  184.     }
  185.  
  186.     /*------------------------------------------------------------------
  187.     [Create Owners Equity Summary]
  188.     -------------------------------------------------------------------*/
  189.  
  190.     public function createOwnersEquitySummary($Beginning, $Capital, $Drawing, $Income, $Equity)
  191.     {
  192.         $string = "";
  193.         $string = "Total equity at the beginning of the period is " . $this->convert_number_to_words($Beginning)
  194.             . $this->toCurrency() . ".";
  195.         $string .= " Total capital invested was " . $this->convert_number_to_words($Capital) . $this->toCurrency()
  196.             . ", total drawing was " . $this->convert_number_to_words($Drawing * -1) . $this->toCurrency()
  197.             . ", with net income of " . $this->convert_number_to_words($Income) . $this->toCurrency()
  198.             . " resulting in ending equity of " . $this->convert_number_to_words($Equity) . $this->toCurrency() . ".";
  199.         return $string;
  200.     }
  201.  
  202.     /*------------------------------------------------------------------
  203.     [Create Balance Sheet summary]
  204.     -------------------------------------------------------------------*/
  205.  
  206.     public function createBalanceSheetSummary($Cash, $Asset, $Liability, $Equity)
  207.     {
  208.         $string = "";
  209.         $string = "Total asset at the end of the period is " . $this->convert_number_to_words($Asset) . $this->toCurrency()
  210.             . " including total cash " . $this->convert_number_to_words($Cash) . $this->toCurrency() . ".";
  211.         $string .= " Total liability at the end of the period is " . $this->convert_number_to_words($Liability)
  212.             . $this->toCurrency() . " and total equity " . $this->convert_number_to_words($Equity) . $this->toCurrency() . ".";
  213.         return $string;
  214.     }
  215.  
  216.     /*------------------------------------------------------------------
  217.     [Create Cash Flow summary]
  218.     -------------------------------------------------------------------*/
  219.  
  220.     public function createCashFlowSummary($CashOp, $CashInv, $CashFin, $CashBeginning, $CashEnding)
  221.     {
  222.         $string = "";
  223.         $string = "Total cash at the beginning of the period is " . $this->convert_number_to_words($CashBeginning)
  224.             . $this->toCurrency() . ".";
  225.         $string .= " Cash flow for operating activites is " . $this->convert_number_to_words($CashOp) . $this->toCurrency()
  226.             . ", investing activities is " . $this->convert_number_to_words($CashInv) . $this->toCurrency()
  227.             . ", and financing activities is " . $this->convert_number_to_words($CashFin) . $this->toCurrency() . ".";
  228.         $string .= " Total ending cash is " . $this->convert_number_to_words($CashEnding) . $this->toCurrency() . ".";
  229.         return $string;
  230.     }
  231.  
  232.     /*------------------------------------------------------------------
  233.     [Waterfall graph for Income Statement]
  234.     -------------------------------------------------------------------*/
  235.  
  236.     public function incomeWaterFall($graphGrossRev, $graphTotalExpenses, $graphEbitda, $graphOtherExpenses,
  237.         $graphNetIncome)
  238.     {
  239.         $tooltipEbitda = ($graphEbitda < "0" ? "EBITDA (Loss)" : "EBITDA");
  240.         $tooltipIncome = ($graphNetIncome < "0" ? "Net Loss" : "Net Profit");
  241.        
  242.         if ($graphNetIncome < 0)
  243.             $barHeight = $graphGrossRev + abs($graphNetIncome);
  244.         else
  245.             $barHeight = $graphGrossRev;
  246.         if ($graphNetIncome < 0)
  247.             $revenueBottom = number_format(100 - ($graphGrossRev / $barHeight) * 100) . "%";
  248.         else
  249.             $revenueBottom = "0%";
  250.         $revenueHeight         = number_format(($graphGrossRev / $barHeight) * 100) . "%";
  251.         $directExpenseBottom   = number_format(100 - abs($graphTotalExpenses / $barHeight) * 100) . "%";
  252.         $ebitdaBottom          = min(str_replace("%", "", $revenueBottom), str_replace("%", "", $directExpenseBottom)) . "%";
  253.         $ebitdaHeight          = number_format(abs($graphEbitda / $barHeight) * 100) . "%";
  254.         $indirectExpenseHeight = number_format(abs($graphOtherExpenses / $barHeight) * 100) . "%";
  255.         $indirectExpenseBottom = (str_replace("%", "", $ebitdaBottom) + str_replace("%", "", $ebitdaHeight)
  256.             - str_replace("%", "", $indirectExpenseHeight)) . "%";
  257.         if ($graphEbitda < 0)
  258.             $indirectExpenseBottom = (str_replace("%", "", $indirectExpenseBottom) - str_replace("%", "", $ebitdaHeight)) . "%";
  259.         $incomeHeight = number_format(abs($graphNetIncome / $barHeight) * 100) . "%";
  260.         echo <<<EOT
  261.             <div class="bars income-width blue-last tooltip-bottom" title="Gross Revenue"
  262.                 style="bottom:$revenueBottom;left:12.5%;" data-percent="$revenueHeight"></div>
  263.             <div class="bars income-width red-last tooltip-bottom" title="Direct Expenses"
  264.                 style="bottom:$directExpenseBottom;left:25%;" data-percent="100%"></div>
  265.             <div class="bars income-width purple-last tooltip-bottom" title="$tooltipEbitda"
  266.                 style="bottom:$ebitdaBottom;left:37.5%;" data-percent="$ebitdaHeight"></div>
  267.             <div class="bars income-width orange-last tooltip-bottom" title="Indirect Expenses"
  268.                 style="bottom:$indirectExpenseBottom;left:50%;" data-percent="$indirectExpenseHeight"></div>
  269.             <div class="bars income-width brown-last tooltip-bottom" title="$tooltipIncome"
  270.                 style="bottom:0%;left:62.5%;" data-percent="$incomeHeight"></div>
  271. EOT;
  272.     }
  273.  
  274.     /*------------------------------------------------------------------
  275.     [Waterfall graph for Owners Equity Statement]
  276.     -------------------------------------------------------------------*/
  277.  
  278.     public function equityWaterFall($graphBeginning, $graphCapital, $graphDrawing, $graphIncome, $graphEquity)
  279.     {
  280.         $tooltipIncome = ($graphIncome < "0" ? "Net Loss" : "Net Profit");
  281.         $barHeight       = max($graphBeginning, $graphBeginning + $graphCapital, $graphBeginning + $graphCapital
  282.             + $graphDrawing, $graphBeginning + $graphCapital + $graphDrawing + $graphIncome);
  283.         $beginningHeight = number_format(($graphBeginning / $barHeight) * 100) . "%";
  284.         $capitalBottom   = $beginningHeight;
  285.         $capitalHeight   = number_format(($graphCapital / $barHeight) * 100) . "%";
  286.         $drawingHeight = number_format((abs($graphDrawing) / $barHeight) * 100) . "%";
  287.         $drawingBottom = (str_replace("%", "", $capitalBottom) + str_replace("%", "", $capitalHeight)
  288.             - str_replace("%", "", $drawingHeight)) . "%";
  289.         $incomeHeight = number_format((abs($graphIncome) / $barHeight) * 100) . "%";
  290.         if ($graphIncome < 0)
  291.             $incomeBottom = (str_replace("%", "", $drawingBottom) - str_replace("%", "", $incomeHeight)) . "%";
  292.         else
  293.             $incomeBottom = $drawingBottom;
  294.         $equityHeight = number_format((abs($graphEquity) / $barHeight) * 100) . "%";
  295.         echo <<<EOT
  296.             <div class="bars income-width purple-last tooltip-bottom" title="Beginning Equity"
  297.                 style="bottom:0%;left:12.5%;" data-percent="$beginningHeight"></div>
  298.             <div class="bars income-width blue-last tooltip-bottom" title="Investment"
  299.                 style="bottom:$capitalBottom;left:25%;" data-percent="$capitalHeight"></div>
  300.             <div class="bars income-width red-last tooltip-bottom" title="Drawing"
  301.                 style="bottom:$drawingBottom;left:37.5%;" data-percent="$drawingHeight"></div>
  302.             <div class="bars income-width orange-last tooltip-bottom" title="$tooltipIncome"
  303.                 style="bottom:$incomeBottom;left:50%;" data-percent="$incomeHeight"></div>
  304.             <div class="bars income-width brown-last tooltip-bottom" title="Closing Equity"
  305.                 style="bottom:0;left:62.5%;" data-percent="$equityHeight"></div>
  306. EOT;
  307.     }
  308.  
  309.     /*------------------------------------------------------------------
  310.     [Waterfall graph for Balance Sheet]
  311.     -------------------------------------------------------------------*/
  312.  
  313.     public function balanceWaterFall($graphCurrentAsset, $graphNonCurrentAsset, $graphCurrentLiability,
  314.         $graphNonCurrentLiability, $graphEquity)
  315.     {
  316.         $barHeight = max($graphCurrentAsset + $graphNonCurrentAsset, $graphCurrentLiability
  317.             + $graphNonCurrentLiability + $graphEquity);
  318.         $currentAssetBottom    = "0%";
  319.         $currentAssetHeight    = number_format(($graphCurrentAsset / $barHeight) * 100) . "%";
  320.         $nonCurrentAssetBottom = str_replace("%", "", $currentAssetHeight) . "%";
  321.         $nonCurrentAssetHeight = ($graphNonCurrentAsset != 0 ? "100%" : "0%");
  322.         $currentLiabilityBottom = "0%";
  323.         $currentLiabilityHeight = number_format(($graphCurrentLiability / $barHeight) * 100) . "%";
  324.         $nonCurrentLiabilityBottom = str_replace("%", "", $currentLiabilityHeight) . "%";
  325.         $nonCurrentLiabilityHeight = number_format(($graphNonCurrentLiability / $barHeight) * 100) . "%";
  326.         $equityBottom = (str_replace("%", "", $nonCurrentLiabilityHeight)
  327.             + str_replace("%", "", $currentLiabilityHeight)) . "%";
  328.         $equityHeight = ($graphEquity != 0 ? "100%" : "0%");
  329.         echo <<<EOT
  330.             <div class="bars income-width blue-last tooltip-bottom" title="Current Asset"
  331.                 style="bottom:$currentAssetBottom;left:12.5%;" data-percent="$currentAssetHeight"></div>
  332.             <div class="bars income-width red-last tooltip-bottom" title="Non-current Asset"
  333.                 style="bottom:$nonCurrentAssetBottom;left:25%;" data-percent="$nonCurrentAssetHeight"></div>
  334.             <div class="bars income-width purple-last tooltip-bottom" title="Current Liability"
  335.                 style="bottom:$currentLiabilityBottom;left:37.5%;" data-percent="$currentLiabilityHeight"></div>
  336.             <div class="bars income-width orange-last tooltip-bottom" title="Non-current Liability"
  337.                 style="bottom:$nonCurrentLiabilityBottom;left:50%;" data-percent="$nonCurrentLiabilityHeight"></div>
  338.             <div class="bars income-width brown-last tooltip-bottom" title="Total Equity"
  339.                 style="bottom:$equityBottom;left:62.5%;" data-percent="$equityHeight"></div>
  340. EOT;
  341.     }
  342.  
  343.     /*------------------------------------------------------------------
  344.     [Waterfall graph for Cash Flow Statement]
  345.     -------------------------------------------------------------------*/
  346.  
  347.     public function cashWaterFall($graphCash, $graphCashBeginning, $graphCashEnding)
  348.     {
  349.         $tooltipCash = ($graphCash < "0" ? "Less: Cash Outflow" : "Add: Cash Inflow");
  350.         $barHeight = max($graphCashBeginning, $graphCashBeginning + $graphCash);
  351.         $graphCashBeginningBottom = "0%";
  352.         $graphCashBeginningHeight = number_format(($graphCashBeginning / $barHeight) * 100) . "%";
  353.         $graphCashHeight = number_format(abs($graphCash / $barHeight) * 100) . "%";
  354.         if ($graphCash < 0)
  355.             $graphCashBottom = (str_replace("%", "", $graphCashBeginningHeight)
  356.                 - str_replace("%", "", $graphCashHeight)) . "%";
  357.         else
  358.             $graphCashBottom = $graphCashBeginningHeight;
  359.         $graphCashEndingBottom = "0%";
  360.         $graphCashEndingHeight = number_format(($graphCashEnding / $barHeight) * 100) . "%";
  361.         echo <<<EOT
  362.             <div class="bars income-width blue-last tooltip-bottom" title="Cash: Beginning Period"
  363.                 style="bottom:$graphCashBeginningBottom;left:12.5%;" data-percent="$graphCashBeginningHeight"></div>
  364.             <div class="bars income-width purple-last tooltip-bottom" title="$tooltipCash"
  365.                 style="bottom:$graphCashBottom;left:37.5%;" data-percent="$graphCashHeight"></div>
  366.             <div class="bars income-width brown-last tooltip-bottom" title="Cash: Ending Period"
  367.                 style="bottom:$graphCashEndingBottom;left:62.5%;" data-percent="$graphCashEndingHeight"></div>
  368. EOT;
  369.     }
  370.  
  371.     /*------------------------------------------------------------------
  372.     [Preview balance sheet and create excel export]
  373.     -------------------------------------------------------------------*/
  374.  
  375.     public function previewBalanceSheet($Income, $ClosingEquity, $objExcelBalance)
  376.     {
  377.         $Token = strtotime("now");
  378.         $Month = $_SESSION['month'];
  379.         $Year  = $_SESSION['year'];
  380.         $CheckError = "Correct";
  381.         $siteQuery = "SELECT * FROM balance WHERE Status = 'Open'";
  382.         $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  383.         $row           = mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  384.         $Cash          = round($row['cash'] + $row['procash'], 2);
  385.         $AccReceivable = $row['accrecser'];
  386.         $PreInsurance  = $row['preins'];
  387.         $PreRent       = $row['preren'];
  388.         $PreSupplies   = $row['presup'];
  389.         $Supplies      = $row['supplies'];
  390.         $Building      = $row['building'];
  391.         $Equipment     = $row['equipment'];
  392.         $DepBuilding   = $row['depbui'];
  393.         $DepEquipment  = $row['depequ'];
  394.         $DepSupplies   = $row['depsup'];
  395.         $Asset         = round($Cash + $AccReceivable + $PreInsurance + $PreRent + $PreSupplies + $Supplies + $Equipment
  396.             + $Building - $DepBuilding - $DepEquipment - $DepSupplies, 2);
  397.        
  398.         $CashReturn  = $Cash;
  399.         $AssetReturn = $Asset;
  400.        
  401.         $graphCurrentAsset    = round($row["cash"] + $row["procash"] + $row["accrecser"] + $row["preins"]
  402.             + $row["preren"] + $row["presup"], 2);
  403.         $graphNonCurrentAsset = round($row["supplies"] + $row["equipment"] + $row["building"] - $row["depbui"]
  404.             - $row["depsup"] - $row["depequ"], 2);
  405.        
  406.         if ($Cash < 0 || $AccReceivable < 0 || $PreInsurance < 0 || $PreRent < 0 || $PreSupplies < 0
  407.             || $Supplies < 0 || $Equipment < 0 || $Building < 0 || $DepBuilding < 0 || $DepEquipment < 0
  408.             || $DepSupplies < 0)
  409.             $CheckError = "Error";
  410.        
  411.         $objExcelBalance->getDefaultStyle()->getFont()->setName('Segoe UI');
  412.         $objExcelBalance->getDefaultStyle()->getFont()->setSize(11);
  413.         $objWriter = PHPExcel_IOFactory::createWriter($objExcelBalance, "Excel2007");
  414.         $objSheet  = $objExcelBalance->getActiveSheet();
  415.         $objSheet->setTitle('Balance Sheet');
  416.         $objSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  417.         $objSheet->getStyle('A1:B1')->getFont()->setBold(true)->setSize(11);
  418.         $objSheet->getColumnDimension('A')->setWidth(50);
  419.         $objSheet->getColumnDimension('B')->setWidth(50);
  420.         $objSheet->getStyle('B2:B34')->getNumberFormat()->setFormatCode('#,##0.00');
  421.         $objSheet->mergeCells('A1:B1');
  422.         $objSheet->getCell('A1')->setValue('Statement of Financial Position');
  423.         $objSheet->mergeCells('A2:B2');
  424.         $objSheet->getCell('A2')->setValue('For the month ending ' . $Month . '\'' . $Year);
  425.         $objSheet->getStyle('A3:B3')->getFont()->setBold(true)->setSize(11);
  426.         $objSheet->getCell('A3')->setValue('Account');
  427.         $objSheet->getCell('B3')->setValue('Amount');
  428.         $objSheet->getCell('A4')->setValue('Cash');
  429.         $objSheet->getCell('B4')->setValue($Cash);
  430.         $objSheet->getCell('A5')->setValue('Account Reveivable');
  431.         $objSheet->getCell('B5')->setValue($AccReceivable);
  432.         $objSheet->getCell('A6')->setValue('Prepaid Insurance');
  433.         $objSheet->getCell('B6')->setValue($PreInsurance);
  434.         $objSheet->getCell('A7')->setValue('Prepaid Rent');
  435.         $objSheet->getCell('B7')->setValue($PreRent);
  436.         $objSheet->getCell('A8')->setValue('Prepaid Supplies');
  437.         $objSheet->getCell('B8')->setValue($PreSupplies);
  438.         $objSheet->getCell('A9')->setValue('Supplies');
  439.         $objSheet->getCell('B9')->setValue($Supplies);
  440.         $objSheet->getCell('A10')->setValue('Equipment');
  441.         $objSheet->getCell('B10')->setValue($Equipment);
  442.         $objSheet->getCell('A11')->setValue('Building');
  443.         $objSheet->getCell('B11')->setValue($Building);
  444.         $objSheet->getCell('A12')->setValue('Acc. depriciation building');
  445.         $objSheet->getCell('B12')->setValue($DepBuilding * -1);
  446.         $objSheet->getCell('A13')->setValue('Acc. depriciation equipment');
  447.         $objSheet->getCell('B13')->setValue($DepEquipment * -1);
  448.         $objSheet->getCell('A14')->setValue('Acc. depriciation supplies');
  449.         $objSheet->getCell('B14')->setValue($DepSupplies * -1);
  450.         $objSheet->getCell('A15')->setValue('Total Asset');
  451.         $objSheet->getCell('B15')->setValue('=SUM(B4:B14)');
  452.         $objSheet->getStyle('A15:B15')->getFont()->setBold(true)->setSize(11);
  453.  
  454.         $Cash          = $this->toMoney($Cash);
  455.         $AccReceivable = $this->toMoney($AccReceivable);
  456.         $PreInsurance  = $this->toMoney($PreInsurance);
  457.         $PreRent       = $this->toMoney($PreRent);
  458.         $PreSupplies   = $this->toMoney($PreSupplies);
  459.         $Supplies      = $this->toMoney($Supplies);
  460.         $Equipment     = $this->toMoney($Equipment);
  461.         $Building      = $this->toMoney($Building);
  462.         $DepBuilding   = $this->toMoney($DepBuilding);
  463.         $DepEquipment  = $this->toMoney($DepEquipment);
  464.         $DepSupplies   = $this->toMoney($DepSupplies);
  465.         $Asset         = $this->toMoney($Asset);
  466.  
  467.         echo <<<EOT
  468.             <div class="ui card width-full">
  469.                 <div class="content">
  470.                     <div class="header"><i class="cubes icon"></i>Statement of Financial Position</div>
  471.                     <div class="meta">For the month ending $Month'$Year</div>
  472.                     <table class="ui">
  473.                         <thead>
  474.                             <tr>
  475.                                 <th class="summary-width-left"><i class="book icon"></i>Asset</th>
  476.                                 <th class="summary-width-right">Amount</th>
  477.                             </tr>
  478.                         </thead>
  479.                         <tbody>
  480.                             <tr><td>Cash:</td><td>Rp. $Cash</td></tr>
  481.                             <tr><td>Account Reveivable:</td><td>Rp. $AccReceivable</td></tr>
  482.                             <tr><td>Prepaid Insurance:</td><td>Rp. $PreInsurance</td></tr>
  483.                             <tr><td>Prepaid Rent:</td><td>Rp. $PreRent</td></tr>
  484.                             <tr><td>Prepaid Supplies:</td><td>Rp. $PreSupplies</td></tr>
  485.                             <tr><td>Supplies:</td><td>Rp. $Supplies</td></tr>
  486.                             <tr><td>Equipments:</td><td>Rp. $Equipment</td></tr>
  487.                             <tr><td>Building:</td><td>Rp. $Building</td></tr>
  488.                             <tr><td>Acc. depriciation building:</td><td>Rp. $DepBuilding</td></tr>
  489.                             <tr><td>Acc. depriciation equipment:</td><td>Rp. $DepEquipment</td></tr>
  490.                             <tr><td>Acc. depriciation supplies:</td><td>Rp. $DepSupplies</td></tr>
  491.                         </tbody>
  492.                         <tfoot>
  493.                             <tr>
  494.                                 <th>Total Asset:</th>
  495.                                 <th>$Asset</th>
  496.                             </tr>
  497.                         </tfoot>
  498.                     </table>
  499. EOT;
  500.         $UnuRevenue     = $row['unuser'];
  501.         $CostRevPayable = $row['costrevpay'];
  502.         $EquPayable     = $row['equpay'];
  503.         $SalPayable     = $row['salpay'];
  504.         $WagPayable     = $row['wagpay'];
  505.         $TelPayable     = $row['telpay'];
  506.         $UtiPayable     = $row['utipay'];
  507.         $RenPayable     = $row['renpay'];
  508.         $SupPayable     = $row['suppay'];
  509.         $AdvPayable     = $row['advpay'];
  510.         $VatPayable     = $row['vatpay'];
  511.         $TaxPayable     = $row['taxpay'];
  512.         $LegPayable     = $row['legpay'];
  513.         $MisPayable     = $row['mispay'];
  514.         $NotPayable     = $row['notpay'];
  515.         $IntPayable     = $row['intpay'];
  516.         $Liability      = round($UnuRevenue + $CostRevPayable + $EquPayable + $SalPayable + $WagPayable + $TelPayable
  517.             + $UtiPayable + $RenPayable + $SupPayable + $AdvPayable + $VatPayable + $TaxPayable + $LegPayable
  518.             + $MisPayable + $NotPayable + $IntPayable, 2);
  519.        
  520.         $LiabilityReturn = $Liability;
  521.        
  522.         $graphCurrentLiability    = round($row['unuser'] + $row['costrevpay'] + $row['equpay'] + $row['salpay']
  523.             + $row['wagpay'] + $row['telpay'] + $row['utipay'] + $row['renpay'] + $row['suppay'] + $row['advpay']
  524.             + $row['vatpay'] + $row['taxpay'] + $row['legpay'] + $row['mispay'] + $row['intpay'], 2);
  525.         $graphNonCurrentLiability = $row['notpay'];
  526.        
  527.         if ($UnuRevenue < 0 || $CostRevPayable < 0 || $EquPayable < 0 || $SalPayable < 0 || $WagPayable < 0
  528.             || $TelPayable < 0 || $UtiPayable < 0 || $RenPayable < 0 || $SupPayable < 0 || $AdvPayable < 0 || $VatPayable < 0
  529.             || $TaxPayable < 0 || $LegPayable < 0 || $MisPayable < 0 || $NotPayable < 0 || $IntPayable < 0)
  530.             $CheckError = "Error";
  531.        
  532.         $objSheet->getCell('A16')->setValue('Unearned Revenue');
  533.         $objSheet->getCell('B16')->setValue($UnuRevenue);
  534.         $objSheet->getCell('A17')->setValue('Revenue Cost Payable');
  535.         $objSheet->getCell('B17')->setValue($CostRevPayable);
  536.         $objSheet->getCell('A18')->setValue('Equipments Payable');
  537.         $objSheet->getCell('B18')->setValue($EquPayable);
  538.         $objSheet->getCell('A19')->setValue('Salaries Payable');
  539.         $objSheet->getCell('B19')->setValue($SalPayable);
  540.         $objSheet->getCell('A20')->setValue('Wages Payable');
  541.         $objSheet->getCell('B20')->setValue($WagPayable);
  542.         $objSheet->getCell('A21')->setValue('Tel. Bill Payable');
  543.         $objSheet->getCell('B21')->setValue($TelPayable);
  544.         $objSheet->getCell('A22')->setValue('Utility Bill Payable');
  545.         $objSheet->getCell('B22')->setValue($UtiPayable);
  546.         $objSheet->getCell('A23')->setValue('Rent Payable');
  547.         $objSheet->getCell('B23')->setValue($RenPayable);
  548.         $objSheet->getCell('A24')->setValue('Supplies Payable');
  549.         $objSheet->getCell('B24')->setValue($SupPayable);
  550.         $objSheet->getCell('A25')->setValue('Advertisement Payable');
  551.         $objSheet->getCell('B25')->setValue($AdvPayable);
  552.         $objSheet->getCell('A26')->setValue('VAT Payable');
  553.         $objSheet->getCell('B26')->setValue($VatPayable);
  554.         $objSheet->getCell('A27')->setValue('Tax Payable');
  555.         $objSheet->getCell('B27')->setValue($TaxPayable);
  556.         $objSheet->getCell('A28')->setValue('Legal & Admin Payable');
  557.         $objSheet->getCell('B28')->setValue($LegPayable);
  558.         $objSheet->getCell('A29')->setValue('Miscellaneous Payable');
  559.         $objSheet->getCell('B29')->setValue($MisPayable);
  560.         $objSheet->getCell('A30')->setValue('Notes Payable');
  561.         $objSheet->getCell('B30')->setValue($NotPayable);
  562.         $objSheet->getCell('A31')->setValue('Interest Payable');
  563.         $objSheet->getCell('B31')->setValue($IntPayable);
  564.         $objSheet->getCell('A32')->setValue('Total Liabilities');
  565.         $objSheet->getCell('B32')->setValue('=SUM(B16:B31)');
  566.         $objSheet->getStyle('A32:B32')->getFont()->setBold(true)->setSize(11);
  567.        
  568.         $UnuRevenue     = $this->toMoney($UnuRevenue);
  569.         $CostRevPayable = $this->toMoney($CostRevPayable);
  570.         $EquPayable     = $this->toMoney($EquPayable);
  571.         $SalPayable     = $this->toMoney($SalPayable);
  572.         $WagPayable     = $this->toMoney($WagPayable);
  573.         $TelPayable     = $this->toMoney($TelPayable);
  574.         $UtiPayable     = $this->toMoney($UtiPayable);
  575.         $RenPayable     = $this->toMoney($RenPayable);
  576.         $SupPayable     = $this->toMoney($SupPayable);
  577.         $AdvPayable     = $this->toMoney($AdvPayable);
  578.         $VatPayable     = $this->toMoney($VatPayable);
  579.         $TaxPayable     = $this->toMoney($TaxPayable);
  580.         $LegPayable     = $this->toMoney($LegPayable);
  581.         $MisPayable     = $this->toMoney($MisPayable);
  582.         $NotPayable     = $this->toMoney($NotPayable);
  583.         $IntPayable     = $this->toMoney($IntPayable);
  584.         $Liability      = $this->toMoney($Liability);
  585.         echo <<<EOT
  586.                     <table class="ui">
  587.                         <thead>
  588.                             <tr>
  589.                                 <th class="summary-width-left"><i class="book icon"></i>Liability</th>
  590.                                 <th class="summary-width-right">Amount</th>
  591.                             </tr>
  592.                         </thead>
  593.                         <tbody>
  594.                             <tr><td>Unearned Revenue:</td><td>Rp. $UnuRevenue</td></tr>
  595.                             <tr><td>Revenue Cost Payable:</td><td>Rp. $CostRevPayable</td></tr>
  596.                             <tr><td>Equipments Payable:</td><td>Rp. $EquPayable</td></tr>
  597.                             <tr><td>Salaries Payable:</td><td>Rp. $SalPayable</td></tr>
  598.                             <tr><td>Wages Payable:</td><td>Rp. $WagPayable</td></tr>
  599.                             <tr><td>Tel. Bill Payable:</td><td>Rp. $TelPayable</td></tr>
  600.                             <tr><td>Utility Bill Payable:</td><td>Rp. $UtiPayable</td></tr>
  601.                             <tr><td>Rent Payable:</td><td>Rp. $RenPayable</td></tr>
  602.                             <tr><td>Supplies Payable:</td><td>Rp. $SupPayable</td></tr>
  603.                             <tr><td>Advertisement Payable:</td><td>Rp. $AdvPayable</td></tr>
  604.                             <tr><td>VAT Payable:</td><td>Rp. $VatPayable</td></tr>
  605.                             <tr><td>Tax Payable:</td><td>Rp. $TaxPayable</td></tr>
  606.                             <tr><td>Legal & Admin Payable:</td><td>Rp. $LegPayable</td></tr>
  607.                             <tr><td>Miscellaneous Payable:</td><td>Rp. $MisPayable</td></tr>
  608.                             <tr><td>Notes Payable:</td><td>Rp. $NotPayable</td></tr>
  609.                             <tr><td>Interest Payable:</td><td>Rp. $IntPayable</td></tr>
  610.                         </tbody>
  611.                         <tfoot>
  612.                             <tr>
  613.                                 <th>Total Liabilities:</th>
  614.                                 <th>$Liability</th>
  615.                             </tr>
  616.                         </tfoot>
  617.                     </table>
  618. EOT;
  619.         $Equity       = $ClosingEquity;
  620.         $EquityReturn = $Equity;
  621.        
  622.         $graphEquity = $Equity;
  623.        
  624.         if ($Equity < 0)
  625.             $CheckError = "Error";
  626.        
  627.         $objSheet->getCell('A33')->setValue('Equity');
  628.         $objSheet->getCell('B33')->setValue($Equity);
  629.         $objSheet->getCell('A34')->setValue('Total Owners Equity');
  630.         $objSheet->getCell('B34')->setValue('=B33');
  631.         $objSheet->getStyle('A34:B34')->getFont()->setBold(true)->setSize(11);
  632.         $objWriter->save('statements/BalanceSheet' . '_' . $Token . '.xlsx');
  633.        
  634.         $Equity = $this->toMoney($Equity);
  635.         $BalanceSheetSummary = $this->createBalanceSheetSummary($CashReturn, $AssetReturn, $LiabilityReturn, $EquityReturn);
  636.         echo <<<EOT
  637.                     <table class="ui">
  638.                         <thead>
  639.                             <tr>
  640.                                 <th class="summary-width-left"><i class="book icon"></i>Owner's Equity:</th>
  641.                                 <th class="summary-width-right">Amount</th>
  642.                             </tr>
  643.                         </thead>
  644.                         <tbody>
  645.                             <tr><td>Equity:</td><td>Rp. $Equity</td></tr>
  646.                         </tbody>
  647.                         <tfoot>
  648.                             <tr>
  649.                                 <th>Total Owner's Equity:</th>
  650.                                 <th>$Equity</th>
  651.                             </tr>
  652.                         </tfoot>
  653.                     </table>
  654.                     <div class="summary">
  655.                         <div class="description waterfall right">
  656. EOT;
  657.         $this->balanceWaterFall($graphCurrentAsset, $graphNonCurrentAsset, $graphCurrentLiability,
  658.             $graphNonCurrentLiability, $graphEquity);
  659.         echo <<<EOT
  660.                         </div>
  661.                         <div class="description in-words left">
  662.                             Summary: $BalanceSheetSummary
  663.                         </div>
  664.                     </div>
  665.                 </div>
  666.                 <div class="extra">
  667.                     Download
  668.                     <a href='statements/BalanceSheet_$Token.xlsx'><i class="table icon"></i></a>
  669.                 </div>
  670.             </div>
  671.             <div class="ui horizontal divider"><i class="anchor icon"></i></div>
  672. EOT;
  673.         return array(
  674.             $CashReturn,
  675.             $AssetReturn,
  676.             $LiabilityReturn,
  677.             $EquityReturn,
  678.             $CheckError
  679.         );
  680.     }
  681.  
  682.     /*------------------------------------------------------------------
  683.     [Preview owner's equity statement and create excel export]
  684.     -------------------------------------------------------------------*/
  685.  
  686.     public function previewOwnerEquity($Income, $objExcelEquity)
  687.     {
  688.         $Token = strtotime("now");
  689.         $Month = $_SESSION['month'];
  690.         $Year  = $_SESSION['year'];
  691.        
  692.         $siteQuery = "SELECT * FROM balance WHERE Status = 'Open'";
  693.         $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  694.         $row = mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  695.        
  696.         $Beginning = $row['eqbeginning'];
  697.         $Capital   = $row['capital'];
  698.         $Drawing   = $row['drawing'];
  699.         $Equity    = $Beginning + $Capital + $Drawing + $Income;
  700.        
  701.         $graphBeginning = $Beginning;
  702.         $graphCapital   = $Capital;
  703.         $graphDrawing   = $Drawing;
  704.         $graphIncome    = $Income;
  705.         $graphEquity    = $Equity;
  706.        
  707.         $OwnersEquitySummary = $this->createOwnersEquitySummary($Beginning, $Capital, $Drawing, $Income, $Equity);
  708.         $EquityReturn        = $Equity;
  709.         $SignIncome          = ($Income < "0" ? "Less: Net Loss:" : "Add: Net Income:");
  710.        
  711.         $objExcelEquity->getDefaultStyle()->getFont()->setName('Segoe UI');
  712.         $objExcelEquity->getDefaultStyle()->getFont()->setSize(11);
  713.         $objWriter = PHPExcel_IOFactory::createWriter($objExcelEquity, "Excel2007");
  714.         $objSheet  = $objExcelEquity->getActiveSheet();
  715.         $objSheet->setTitle('Owners Equity Statement');
  716.         $objSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  717.         $objSheet->getStyle('A1:B1')->getFont()->setBold(true)->setSize(11);
  718.         $objSheet->getColumnDimension('A')->setWidth(50);
  719.         $objSheet->getColumnDimension('B')->setWidth(50);
  720.         $objSheet->getStyle('B2:B8')->getNumberFormat()->setFormatCode('#,##0.00');
  721.         $objSheet->mergeCells('A1:B1');
  722.         $objSheet->getCell('A1')->setValue('Statement of Owners Equity');
  723.         $objSheet->mergeCells('A2:B2');
  724.         $objSheet->getCell('A2')->setValue('For the month of ' . $Month . '\'' . $Year);
  725.         $objSheet->getStyle('A3:B3')->getFont()->setBold(true)->setSize(11);
  726.         $objSheet->getCell('A3')->setValue('Account');
  727.         $objSheet->getCell('B3')->setValue('Amount');
  728.         $objSheet->getCell('A4')->setValue('Equity at Beginning');
  729.         $objSheet->getCell('B4')->setValue($Beginning);
  730.         $objSheet->getCell('A5')->setValue('Investment');
  731.         $objSheet->getCell('B5')->setValue($Capital);
  732.         $objSheet->getCell('A6')->setValue('Less: Drawing');
  733.         $objSheet->getCell('B6')->setValue($Drawing);
  734.         $objSheet->getCell('A7')->setValue($SignIncome);
  735.         $objSheet->getCell('B7')->setValue($Income);
  736.         $objSheet->getCell('A8')->setValue('Total Owners Equity Closing');
  737.         $objSheet->getCell('B8')->setValue('=B4+B5+B6+B7');
  738.         $objSheet->getStyle('A8:B8')->getFont()->setBold(true)->setSize(11);
  739.        
  740.         $objWriter->save('statements/OwnersEquityStatement' . '_' . $Token . '.xlsx');
  741.        
  742.         $Beginning = $this->toMoney($Beginning);
  743.         $Capital   = $this->toMoney($Capital);
  744.         $Drawing   = $this->toMoney($Drawing * -1);
  745.         $Income    = $this->toMoney(abs($Income));
  746.         $Equity    = $this->toMoney($Equity);
  747.        
  748.         echo <<<EOT
  749.             <div class="ui card width-full">
  750.                 <div class="content">
  751.                     <div class="header"><i class="cubes icon"></i>Statement of Owner's Equity</div>
  752.                     <div class="meta">For the month of $Month'$Year</div>
  753.                     <table class="ui">
  754.                         <thead>
  755.                             <tr>
  756.                                 <th class="summary-width-left"><i class="book icon"></i>Equity</th>
  757.                                 <th class="summary-width-right">Amount</th>
  758.                             </tr>
  759.                         </thead>
  760.                         <tbody>
  761.                             <tr><td>Equity at Beginning:</td><td>Rp. $Beginning</td></tr>
  762.                             <tr><td>Investment:</td><td>Rp. $Capital</td></tr>
  763.                             <tr><td>Less: Drawing:</td><td>Rp. $Drawing</td></tr>
  764.                             <tr><td>$SignIncome</td><td>Rp. $Income</td></tr>
  765.                         </tbody>
  766.                         <tfoot>
  767.                             <tr>
  768.                                 <th>Total Owner's Equity Closing:</th>
  769.                                 <th>$Equity</th>
  770.                             </tr>
  771.                         </tfoot>
  772.                     </table>
  773.                     <div class="summary">
  774.                         <div class="description waterfall left">
  775. EOT;
  776.         $this->equityWaterFall($graphBeginning, $graphCapital, $graphDrawing, $graphIncome, $graphEquity);
  777.         echo <<<EOT
  778.                         </div>
  779.                         <div class="description in-words right">
  780.                             Summary: $OwnersEquitySummary
  781.                         </div>
  782.                     </div>
  783.                 </div>
  784.                 <div class="extra">
  785.                     Download
  786.                     <a href='statements/OwnersEquityStatement_$Token.xlsx'><i class="table icon"></i></a>
  787.                 </div>
  788.             </div>
  789.             <div class="ui horizontal divider"><i class="anchor icon"></i></div>
  790. EOT;
  791.         return array(
  792.             $EquityReturn
  793.         );
  794.     }
  795.  
  796.     /*------------------------------------------------------------------
  797.     [Preview income statement and create excel export]
  798.     -------------------------------------------------------------------*/
  799.  
  800.     public function previewIncomeStatement($objExcelIncome)
  801.     {
  802.         $Token = strtotime("now");
  803.         $Month = $_SESSION['month'];
  804.         $Year  = $_SESSION['year'];
  805.        
  806.         $siteQuery = "SELECT * FROM balance WHERE Status = 'Open'";
  807.         $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  808.         $row = mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  809.        
  810.         $ServiceRev       = $row['serrev'];
  811.         $InterestRev      = $row['intrev'];
  812.         $ProGain          = $row['progain'];
  813.         $CostRevExp       = $row['costrevexp'];
  814.         $SalaryExp        = $row['salexp'];
  815.         $WagesExp         = $row['wagexp'];
  816.         $RentExp          = $row['renexp'];
  817.         $InsuranceExp     = $row['insexp'];
  818.         $TelephoneExp     = $row['telexp'];
  819.         $UtilityExp       = $row['utiexp'];
  820.         $AdvertisementExp = $row['advexp'];
  821.         $InterestExp      = $row['intexp'];
  822.         $VatExp           = $row['vatexp'];
  823.         $TaxExp           = $row['taxexp'];
  824.         $LegExp           = $row['legexp'];
  825.         $MisExp           = $row['misexp'];
  826.         $BadExp           = $row['baddebtexp'];
  827.         $DepBuilding      = $row['depbuiexp'];
  828.         $DepEquipment     = $row['depequexp'];
  829.         $DepSupplies      = $row['depsupexp'];
  830.        
  831.         $GrossRev      = round($ServiceRev + $InterestRev + $ProGain, 2);
  832.         $TotalExpenses = round($CostRevExp + $SalaryExp + $WagesExp + $RentExp + $InsuranceExp + $TelephoneExp
  833.             + $UtilityExp + $AdvertisementExp + $MisExp + $LegExp + $BadExp, 2);
  834.         $Ebitda        = round($GrossRev + $TotalExpenses, 2);
  835.         $OtherExpenses = round($InterestExp + $VatExp + $TaxExp + $DepBuilding + $DepEquipment + $DepSupplies, 2);
  836.         $NetIncome     = round($Ebitda + $OtherExpenses, 2);
  837.        
  838.         $graphGrossRev      = $GrossRev;
  839.         $graphTotalExpenses = $TotalExpenses;
  840.         $graphEbitda        = $Ebitda;
  841.         $graphOtherExpenses = $OtherExpenses;
  842.         $graphNetIncome     = $NetIncome;
  843.        
  844.         $IncomeStatementSummary = $this->createIncomeSummary($GrossRev, $TotalExpenses, $Ebitda, $OtherExpenses, $NetIncome);
  845.         $IncomeReturn           = $NetIncome;
  846.        
  847.         $SignEbitda    = ($Ebitda < "0" ? "EBITDA (Loss):" : "EBITDA:");
  848.         $SignNetIncome = ($NetIncome < "0" ? "Net Loss:" : "Net Income:");
  849.        
  850.         $objExcelIncome->getDefaultStyle()->getFont()->setName('Segoe UI');
  851.         $objExcelIncome->getDefaultStyle()->getFont()->setSize(11);
  852.         $objWriter = PHPExcel_IOFactory::createWriter($objExcelIncome, "Excel2007");
  853.         $objSheet  = $objExcelIncome->getActiveSheet();
  854.         $objSheet->setTitle('Income Statement');
  855.         $objSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  856.         $objSheet->getStyle('A1:B1')->getFont()->setBold(true)->setSize(11);
  857.         $objSheet->getColumnDimension('A')->setWidth(50);
  858.         $objSheet->getColumnDimension('B')->setWidth(50);
  859.         $objSheet->getStyle('B2:B50')->getNumberFormat()->setFormatCode('#,##0.00');
  860.         $objSheet->mergeCells('A1:B1');
  861.         $objSheet->getCell('A1')->setValue('Income Statement');
  862.         $objSheet->mergeCells('A2:B2');
  863.         $objSheet->getCell('A2')->setValue('For the month of ' . $Month . '\'' . $Year);
  864.         $objSheet->getStyle('A3:B3')->getFont()->setBold(true)->setSize(11);
  865.         $objSheet->getCell('A3')->setValue('Account');
  866.         $objSheet->getCell('B3')->setValue('Amount');
  867.         $objSheet->getCell('A4')->setValue('Service Revenue');
  868.         $objSheet->getCell('B4')->setValue($ServiceRev);
  869.         $objSheet->getCell('A5')->setValue('Interest Revenue');
  870.         $objSheet->getCell('B5')->setValue($InterestRev);
  871.         $objSheet->getCell('A6')->setValue('Gain/loss of sales');
  872.         $objSheet->getCell('B6')->setValue($ProGain);
  873.         $objSheet->getCell('A7')->setValue('Gross Revenue');
  874.         $objSheet->getCell('B7')->setValue('=SUM(B4:B6)');
  875.         $objSheet->getStyle('A7:B7')->getFont()->setBold(true)->setSize(11);
  876.         $objSheet->getCell('A8')->setValue('Cost of Revenue');
  877.         $objSheet->getCell('B8')->setValue($CostRevExp * -1);
  878.         $objSheet->getCell('A9')->setValue('Salaries Expenses');
  879.         $objSheet->getCell('B9')->setValue($SalaryExp * -1);
  880.         $objSheet->getCell('A10')->setValue('Wages Expenses');
  881.         $objSheet->getCell('B10')->setValue($WagesExp * -1);
  882.         $objSheet->getCell('A11')->setValue('Rent Expenses');
  883.         $objSheet->getCell('B11')->setValue($RentExp * -1);
  884.         $objSheet->getCell('A12')->setValue('Insurance Expenses');
  885.         $objSheet->getCell('B12')->setValue($InsuranceExp * -1);
  886.         $objSheet->getCell('A13')->setValue('Telephone Expenses');
  887.         $objSheet->getCell('B13')->setValue($TelephoneExp * -1);
  888.         $objSheet->getCell('A14')->setValue('Utility Expenses');
  889.         $objSheet->getCell('B14')->setValue($UtilityExp * -1);
  890.         $objSheet->getCell('A15')->setValue('Advertisement Expenses');
  891.         $objSheet->getCell('B15')->setValue($AdvertisementExp * -1);
  892.         $objSheet->getCell('A16')->setValue('Legal & Admin Expenses');
  893.         $objSheet->getCell('B16')->setValue($LegExp * -1);
  894.         $objSheet->getCell('A17')->setValue('Miscellaneous Expenses');
  895.         $objSheet->getCell('B17')->setValue($MisExp * -1);
  896.         $objSheet->getCell('A18')->setValue('Bad Debt Expense');
  897.         $objSheet->getCell('B18')->setValue($BadExp * -1);
  898.         $objSheet->getCell('A19')->setValue('Total Direct Expenses');
  899.         $objSheet->getCell('B19')->setValue('=SUM(B8:B18)');
  900.         $objSheet->getStyle('A19:B19')->getFont()->setBold(true)->setSize(11);
  901.         $objSheet->getCell('A20')->setValue($SignEbitda);
  902.         $objSheet->getCell('B20')->setValue('=B7-B19');
  903.         $objSheet->getStyle('A20:B20')->getFont()->setBold(true)->setSize(11);
  904.         $objSheet->getCell('A21')->setValue('Dep. Expense Building');
  905.         $objSheet->getCell('B21')->setValue($DepBuilding * -1);
  906.         $objSheet->getCell('A22')->setValue('Dep. Expense Equipment');
  907.         $objSheet->getCell('B22')->setValue($DepEquipment * -1);
  908.         $objSheet->getCell('A23')->setValue('Dep. Expenses Supplies');
  909.         $objSheet->getCell('B23')->setValue($DepSupplies * -1);
  910.         $objSheet->getCell('A24')->setValue('Interest Expenses');
  911.         $objSheet->getCell('B24')->setValue($InterestExp * -1);
  912.         $objSheet->getCell('A25')->setValue('VAT Expenses');
  913.         $objSheet->getCell('B25')->setValue($VatExp * -1);
  914.         $objSheet->getCell('A26')->setValue('Tax Expenses');
  915.         $objSheet->getCell('B26')->setValue($TaxExp * -1);
  916.         $objSheet->getCell('A27')->setValue('Other Expenses');
  917.         $objSheet->getCell('B27')->setValue('=SUM(B21:B26)');
  918.         $objSheet->getStyle('A27:B27')->getFont()->setBold(true)->setSize(11);
  919.         $objSheet->getCell('A28')->setValue($SignNetIncome);
  920.         $objSheet->getCell('B28')->setValue('=B7-B19-B27');
  921.         $objSheet->getStyle('A28:B28')->getFont()->setBold(true)->setSize(11);
  922.         $objWriter->save('statements/IncomeStatement' . '_' . $Token . '.xlsx');
  923.        
  924.         $ServiceRev       = $this->toMoney($ServiceRev);
  925.         $InterestRev      = $this->toMoney($InterestRev);
  926.         $ProGain          = $this->toMoney($ProGain);
  927.         $CostRevExp       = $this->toMoney($CostRevExp * -1);
  928.         $SalaryExp        = $this->toMoney($SalaryExp * -1);
  929.         $WagesExp         = $this->toMoney($WagesExp * -1);
  930.         $RentExp          = $this->toMoney($RentExp * -1);
  931.         $InsuranceExp     = $this->toMoney($InsuranceExp * -1);
  932.         $TelephoneExp     = $this->toMoney($TelephoneExp * -1);
  933.         $UtilityExp       = $this->toMoney($UtilityExp * -1);
  934.         $AdvertisementExp = $this->toMoney($AdvertisementExp * -1);
  935.         $MisExp           = $this->toMoney($MisExp * -1);
  936.         $InterestExp      = $this->toMoney($InterestExp * -1);
  937.         $VatExp           = $this->toMoney($VatExp * -1);
  938.         $TaxExp           = $this->toMoney($TaxExp * -1);
  939.         $LegExp           = $this->toMoney($LegExp * -1);
  940.         $BadExp           = $this->toMoney($BadExp * -1);
  941.         $DepBuilding      = $this->toMoney($DepBuilding * -1);
  942.         $DepEquipment     = $this->toMoney($DepEquipment * -1);
  943.         $DepSupplies      = $this->toMoney($DepSupplies * -1);
  944.         $GrossRev         = $this->toMoney($GrossRev);
  945.         $TotalExpenses    = $this->toMoney($TotalExpenses * -1);
  946.         $Ebitda           = $this->toMoney(abs($Ebitda));
  947.         $OtherExpenses    = $this->toMoney($OtherExpenses * -1);
  948.         $NetIncome        = $this->toMoney(abs($NetIncome));
  949.         echo <<<EOT
  950.             <div class="ui card width-full">
  951.                 <div class="content">
  952.                     <div class="header"><i class="cubes icon"></i>Income Statement</div>
  953.                     <div class="meta">For the month of $Month'$Year</div>
  954.                     <table class="ui">
  955.                         <thead>
  956.                             <tr>
  957.                                 <th class="summary-width-left"><i class="book icon"></i>Gross Revenue</th>
  958.                                 <th class="summary-width-right"><i class="book icon"></i>Amount</th>
  959.                             </tr>
  960.                         </thead>
  961.                         <tbody>
  962.                             <tr><td>Service Revenue:</td><td>Rp. $ServiceRev</td></tr>
  963.                             <tr><td>Interest Revenue:</td><td>Rp. $InterestRev</td></tr>
  964.                             <tr><td>Gain/Loss on Asset Sales:</td><td>Rp. $ProGain</td></tr>
  965.                         </tbody>
  966.                         <tfoot>
  967.                             <tr>
  968.                                 <th>Gross Revenue:</th>
  969.                                 <th>$GrossRev</th>
  970.                             </tr>
  971.                         </tfoot>
  972.                     </table>
  973. EOT;
  974.         echo <<<EOT
  975.                     <table class="ui">
  976.                         <thead>
  977.                             <tr>
  978.                                 <th class="summary-width-left"><i class="book icon"></i>Direct Expense</th>
  979.                                 <th class="summary-width-right">Amount</th>
  980.                             </tr>
  981.                         </thead>
  982.                         <tbody>
  983.                             <tr><td>Cost of Revenue:</td><td>Rp. $CostRevExp</td></tr>
  984.                             <tr><td>Salaries Expense:</td><td>Rp. $SalaryExp</td></tr>
  985.                             <tr><td>Wages Expense:</td><td>Rp. $WagesExp</td></tr>
  986.                             <tr><td>Rent Expense:</td><td>Rp. $RentExp</td></tr>
  987.                             <tr><td>Insurance Expense:</td><td>Rp. $InsuranceExp</td></tr>
  988.                             <tr><td>Telephone Expense:</td><td>Rp. $TelephoneExp</td></tr>
  989.                             <tr><td>Utility Expense:</td><td>Rp. $UtilityExp</td></tr>
  990.                             <tr><td>Advertisement Expense:</td><td>Rp. $AdvertisementExp</td></tr>
  991.                             <tr><td>Legal & Admin Expense:</td><td>Rp. $LegExp</td></tr>
  992.                             <tr><td>Miscellaneous Expense:</td><td>Rp. $MisExp</td></tr>
  993.                             <tr><td>Bad Det Expense:</td><td>Rp. $BadExp</td></tr>
  994.                         </tbody>
  995.                         <tfoot>
  996.                             <tr>
  997.                                 <th>Total Direct Expense:</th>
  998.                                 <th>$TotalExpenses</th>
  999.                             </tr>
  1000.                             <tr>
  1001.                                 <th>$SignEbitda</th>
  1002.                                 <th>$Ebitda</th>
  1003.                             </tr>
  1004.                         </tfoot>
  1005.                     </table>
  1006. EOT;
  1007.         echo <<<EOT
  1008.                     <table class="ui">
  1009.                         <thead>
  1010.                             <tr>
  1011.                                 <th class="summary-width-left"><i class="book icon"></i>Other Expense</th>
  1012.                                 <th class="summary-width-right">Amount</th>
  1013.                             </tr>
  1014.                         </thead>
  1015.                         <tbody>
  1016.                             <tr><td>Dep. Expense Building:</td><td>Rp. $DepBuilding</td></tr>
  1017.                             <tr><td>Dep. Expense Equipment:</td><td>Rp. $DepEquipment</td></tr>
  1018.                             <tr><td>Dep. Expense Supplies:</td><td>Rp. $DepSupplies</td></tr>
  1019.                             <tr><td>Interest Expense:</td><td>Rp. $InterestExp</td></tr>
  1020.                             <tr><td>VAT Expense:</td><td>Rp. $VatExp</td></tr>
  1021.                             <tr><td>Tax Expense:</td><td>Rp. $TaxExp</td></tr>
  1022.                         </tbody>
  1023.                         <tfoot>
  1024.                             <tr>
  1025.                                 <th>Other Expenses:</th>
  1026.                                 <th>$OtherExpenses</th>
  1027.                             </tr>
  1028.                             <tr>
  1029.                                 <th>$SignNetIncome</th>
  1030.                                 <th>$NetIncome</th>
  1031.                             </tr>
  1032.                         </tfoot>
  1033.                     </table>
  1034.                     <div class="summary">
  1035.                         <div class="description waterfall right">
  1036. EOT;
  1037.         $this->incomeWaterFall($graphGrossRev, $graphTotalExpenses, $graphEbitda, $graphOtherExpenses, $graphNetIncome);
  1038.         echo <<<EOT
  1039.                         </div>
  1040.                         <div class="description in-words left">
  1041.                             Summary: $IncomeStatementSummary
  1042.                         </div>
  1043.                     </div>
  1044.                 </div>
  1045.                 <div class="extra">
  1046.                     Download
  1047.                     <a href='statements/IncomeStatement_$Token.xlsx'><i class="table icon"></i></a>
  1048.                 </div>
  1049.             </div>
  1050.             <div class="ui horizontal divider"><i class="anchor icon"></i></div>
  1051. EOT;
  1052.         return array(
  1053.             $IncomeReturn
  1054.         );
  1055.     }
  1056.  
  1057.     /*------------------------------------------------------------------
  1058.     [Preview cash flow statement and create excel export]
  1059.     -------------------------------------------------------------------*/
  1060.  
  1061.     public function previewCashFlowStatement($Income, $objExcelCash)
  1062.     {
  1063.         $Token = strtotime("now");
  1064.         $Month = $_SESSION['month'];
  1065.         $Year  = $_SESSION['year'];
  1066.        
  1067.         $siteQuery = "SELECT * FROM balance WHERE Status = 'Open'";
  1068.         $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  1069.         $row = mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  1070.         $ID  = $_SESSION['id'];
  1071.         if ($ID == '1') {
  1072.             $ProGain        = $row['progain'];
  1073.             $DepBuilding    = $row['depbuiexp'];
  1074.             $DepEquipment   = $row['depequexp'];
  1075.             $DepSupplies    = $row['depsupexp'];
  1076.             $UnuRevenue     = $row['unuser'];
  1077.             $AccReceivable  = $row['accrecser'];
  1078.             $CostRevPayable = $row['costrevpay'];
  1079.             $SalPayable     = $row['salpay'];
  1080.             $WagPayable     = $row['wagpay'];
  1081.             $TelPayable     = $row['telpay'];
  1082.             $UtiPayable     = $row['utipay'];
  1083.             $RenPayable     = $row['renpay'];
  1084.             $AdvPayable     = $row['advpay'];
  1085.             $VatPayable     = $row['vatpay'];
  1086.             $TaxPayable     = $row['taxpay'];
  1087.             $LegPayable     = $row['legpay'];
  1088.             $MisPayable     = $row['mispay'];
  1089.             $ProCash      = $row['procash'];
  1090.             $PreInsurance = $row['preins'];
  1091.             $PreRent      = $row['preren'];
  1092.             $PreSupplies  = $row['presup'];
  1093.             $BuiPurchase  = $row['cashbui'];
  1094.             $EquPurchase  = $row['cashequ'];
  1095.             $SupPurchase  = $row['cashsup'];
  1096.             $NotesPayable = $row['notpay'];
  1097.             $IntPayable   = $row['intpay'];
  1098.             $Capital = $row['capital'];
  1099.             $Drawing = $row['drawing'];
  1100.         }
  1101.         else {
  1102.             $siteQuery = "SELECT * FROM records ORDER BY ID DESC LIMIT 1";
  1103.             $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  1104.             $lastBalance = mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  1105.            
  1106.             $ProGain        = $row['progain'];
  1107.             $DepBuilding    = $row['depbuiexp'];
  1108.             $DepEquipment   = $row['depequexp'];
  1109.             $DepSupplies    = $row['depsupexp'];
  1110.             $UnuRevenue     = round($row['unuser'] - $lastBalance['unuser'], 2);
  1111.             $AccReceivable  = round($row['accrecser'] - $lastBalance['accrecser'], 2);
  1112.             $CostRevPayable = round($row['costrevpay'] - $lastBalance['costrevpay'], 2);
  1113.             $SalPayable     = round($row['salpay'] - $lastBalance['salpay'], 2);
  1114.             $WagPayable     = round($row['wagpay'] - $lastBalance['wagpay'], 2);
  1115.             $TelPayable     = round($row['telpay'] - $lastBalance['telpay'], 2);
  1116.             $UtiPayable     = round($row['utipay'] - $lastBalance['utipay'], 2);
  1117.             $RenPayable     = round($row['renpay'] - $lastBalance['renpay'], 2);
  1118.             $AdvPayable     = round($row['advpay'] - $lastBalance['advpay'], 2);
  1119.             $VatPayable     = round($row['vatpay'] - $lastBalance['vatpay'], 2);
  1120.             $TaxPayable     = round($row['taxpay'] - $lastBalance['taxpay'], 2);
  1121.             $LegPayable     = round($row['legpay'] - $lastBalance['legpay'], 2);
  1122.             $MisPayable     = round($row['mispay'] - $lastBalance['mispay'], 2);
  1123.             $ProCash      = $row['procash'];
  1124.             $PreInsurance = round($row['preins'] - $lastBalance['preins'], 2);
  1125.             $PreRent      = round($row['preren'] - $lastBalance['preren'], 2);
  1126.             $PreSupplies  = round($row['presup'] - $lastBalance['presup'], 2);
  1127.             $BuiPurchase  = $row['cashbui'];
  1128.             $EquPurchase  = $row['cashequ'];
  1129.             $SupPurchase  = $row['cashsup'];
  1130.             $NotesPayable = round($row['notpay'] - $lastBalance['notpay'], 2);
  1131.             $IntPayable   = round($row['intpay'] - $lastBalance['intpay'], 2);
  1132.             $Capital = $row['capital'];
  1133.             $Drawing = $row['drawing'];
  1134.         }
  1135.        
  1136.         $CashOp           = round($Income - $ProGain - $DepBuilding - $DepEquipment - $DepSupplies + $UnuRevenue
  1137.             - $AccReceivable + $CostRevPayable + $SalPayable + $WagPayable + $TelPayable + $UtiPayable + $RenPayable
  1138.             + $AdvPayable + $VatPayable + $TaxPayable + $LegPayable + $MisPayable + $IntPayable, 2);
  1139.         $CashInv          = round($NotesPayable - ($PreInsurance + $PreRent + $PreSupplies + $BuiPurchase + $EquPurchase + $SupPurchase)
  1140.             + $ProCash, 2);
  1141.         $CashFin          = round($Capital + $Drawing, 2);
  1142.         $CashBeginning    = $row['cashbeginning'];
  1143.         $CashEnding       = round($CashBeginning + $CashOp + $CashInv + $CashFin, 2);
  1144.         $CashEndingReturn = $CashEnding;
  1145.        
  1146.         $graphCash          = round($CashOp + $CashInv + $CashFin, 2);
  1147.         $graphCashBeginning = $CashBeginning;
  1148.         $graphCashEnding    = $CashEnding;
  1149.        
  1150.         $CashFlowSummary = $this->createCashFlowSummary($CashOp, $CashInv, $CashFin, $CashBeginning, $CashEnding);
  1151.        
  1152.         $SignIncome         = ($Income < "0" ? "Less: Net Loss:" : "Add: Net Income:");
  1153.         $SignProGain        = ($ProGain < "0" ? "Add: Loss on Asset Sale:" : "Less: Gain on Asset Sale:");
  1154.         $SignAccReceivable  = ($AccReceivable < "0" ? "Add: Decr. in Account Receivable:" : "Less: Incr. in Account Receivable:");
  1155.         $SignUnuRevenue     = ($UnuRevenue < "0" ? "Less: Decr. in Unearned Revenue:" : "Add: Incr. in Unearned Revenue:");
  1156.         $SignCostRevPayable = ($CostRevPayable < "0" ? "Less: Decr. in Revenue Cost Payable:" : "Add: Incr. in Revenue Cost Payable:");
  1157.         $SignSalPayable     = ($SalPayable < "0" ? "Less: Decr. in Salaries Payable:" : "Add: Incr. in Salaries Payable:");
  1158.         $SignWagPayable     = ($WagPayable < "0" ? "Less: Decr. in Wages payable:" : "Add: Incr. in Wages payable:");
  1159.         $SignTelPayable     = ($TelPayable < "0" ? "Less: Decr. in Tel. Bill Payable:" : "Add: Incr. in Tel. Bill Payable:");
  1160.         $SignUtiPayable     = ($UtiPayable < "0" ? "Less: Decr. in Util. Bill Payable:" : "Add: Incr. in Util. Bill Payable:");
  1161.         $SignRenPayable     = ($RenPayable < "0" ? "Less: Decr. in Rent Payable:" : "Add: Incr. in Rent Payable:");
  1162.         $SignAdvPayable     = ($AdvPayable < "0" ? "Less: Decr. in Adv. Payable:" : "Add: Incr. in Adv. Payable:");
  1163.         $SignIntPayable     = ($IntPayable < "0" ? "Less: Decr. in Interest Payable:" : "Add: Incr. in Interest Payable:");
  1164.         $SignVatPayable     = ($VatPayable < "0" ? "Less: Decr. in VAT Payable:" : "Add: Incr. in VAT Payable:");
  1165.         $SignTaxPayable     = ($TaxPayable < "0" ? "Less: Decr. in Tax Payable:" : "Add: Incr. in Tax Payable:");
  1166.         $SignLegPayable     = ($LegPayable < "0" ? "Less: Decr. in Legal & Admin Payable:" : "Add: Incr. in Legal & Admin Payable:");
  1167.         $SignMisPayable     = ($MisPayable < "0" ? "Less: Decr. in Misc. Payable:" : "Add: Incr. in Misc. Payable:");
  1168.         $SignPreInsurance   = ($PreInsurance < "0" ? "Less: Decr. in Prepaid Insurance:" : "Add: Incr. in Prepaid Insurance:");
  1169.         $SignPreRent        = ($PreRent < "0" ? "Less: Decr. in Prepaid Rent:" : "Add: Incr. in Prepaid Rent:");
  1170.         $SignPreSupplies    = ($PreSupplies < "0" ? "Less: Decr. in Prepaid Supplies:" : "Add: Incr. in Prepaid Supplies:");
  1171.         $SignNotesPayable   = ($NotesPayable < "0" ? "Less: Decr. in Notes Payable:" : "Add: Incr. in Notes Payable:");
  1172.         $SignCashOp         = ($CashOp < "0" ? "Less: Cash from Operating Activities:" : "Add: Cash from Operating Activities:");
  1173.         $SignCashInv        = ($CashInv < "0" ? "Less: Cash from Investing Activities:" : "Add: Cash from Investing Activities:");
  1174.         $SignCashFin        = ($CashFin < "0" ? "Less: Cash from Financing Activities:" : "Add: Cash from Financing Activities:");
  1175.        
  1176.         $objExcelCash->getDefaultStyle()->getFont()->setName('Segoe UI');
  1177.         $objExcelCash->getDefaultStyle()->getFont()->setSize(11);
  1178.         $objWriter = PHPExcel_IOFactory::createWriter($objExcelCash, "Excel2007");
  1179.         $objSheet  = $objExcelCash->getActiveSheet();
  1180.         $objSheet->setTitle('Income Statement');
  1181.         $objSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1182.         $objSheet->getStyle('A1:B1')->getFont()->setBold(true)->setSize(11);
  1183.         $objSheet->getColumnDimension('A')->setWidth(50);
  1184.         $objSheet->getColumnDimension('B')->setWidth(50);
  1185.         $objSheet->getStyle('B2:B50')->getNumberFormat()->setFormatCode('#,##0.00');
  1186.         $objSheet->mergeCells('A1:B1');
  1187.         $objSheet->getCell('A1')->setValue('Statement of Cash Flow');
  1188.         $objSheet->mergeCells('A2:B2');
  1189.         $objSheet->getCell('A2')->setValue('For the month of ' . $Month . '\'' . $Year);
  1190.         $objSheet->getCell('A3')->setValue('Account');
  1191.         $objSheet->getCell('B3')->setValue('Amount');
  1192.         $objSheet->getStyle('A3:B3')->getFont()->setBold(true)->setSize(11);
  1193.         $objSheet->getCell('A4')->setValue($SignIncome);
  1194.         $objSheet->getCell('B4')->setValue($Income);
  1195.         $objSheet->getCell('A5')->setValue($SignAccReceivable);
  1196.         $objSheet->getCell('B5')->setValue($AccReceivable);
  1197.         $objSheet->getCell('A6')->setValue($SignProGain);
  1198.         $objSheet->getCell('B6')->setValue($ProGain);
  1199.         $objSheet->getCell('A7')->setValue('Add: Dep. Expense Building:');
  1200.         $objSheet->getCell('B7')->setValue($DepBuilding);
  1201.         $objSheet->getCell('A8')->setValue('Add: Dep. Expense Equipment:');
  1202.         $objSheet->getCell('B8')->setValue($DepEquipment);
  1203.         $objSheet->getCell('A9')->setValue('Add: Dep. Expense Supplies:');
  1204.         $objSheet->getCell('B9')->setValue($DepSupplies);
  1205.         $objSheet->getCell('A10')->setValue($SignUnuRevenue);
  1206.         $objSheet->getCell('B10')->setValue($UnuRevenue);
  1207.         $objSheet->getCell('A11')->setValue($SignCostRevPayable);
  1208.         $objSheet->getCell('B11')->setValue($CostRevPayable);
  1209.         $objSheet->getCell('A12')->setValue($SignSalPayable);
  1210.         $objSheet->getCell('B12')->setValue($SalPayable);
  1211.         $objSheet->getCell('A13')->setValue($SignWagPayable);
  1212.         $objSheet->getCell('B13')->setValue($WagPayable);
  1213.         $objSheet->getCell('A14')->setValue($SignTelPayable);
  1214.         $objSheet->getCell('B14')->setValue($TelPayable);
  1215.         $objSheet->getCell('A15')->setValue($SignUtiPayable);
  1216.         $objSheet->getCell('B15')->setValue($UtiPayable);
  1217.         $objSheet->getCell('A16')->setValue($SignRenPayable);
  1218.         $objSheet->getCell('B16')->setValue($RenPayable);
  1219.         $objSheet->getCell('A17')->setValue($SignAdvPayable);
  1220.         $objSheet->getCell('B17')->setValue($AdvPayable);
  1221.         $objSheet->getCell('A18')->setValue($SignIntPayable);
  1222.         $objSheet->getCell('B18')->setValue($IntPayable);
  1223.         $objSheet->getCell('A19')->setValue($SignVatPayable);
  1224.         $objSheet->getCell('B19')->setValue($VatPayable);
  1225.         $objSheet->getCell('A20')->setValue($SignTaxPayable);
  1226.         $objSheet->getCell('B20')->setValue($TaxPayable);
  1227.         $objSheet->getCell('A21')->setValue($SignLegPayable);
  1228.         $objSheet->getCell('B21')->setValue($LegPayable);
  1229.         $objSheet->getCell('A22')->setValue($SignMisPayable);
  1230.         $objSheet->getCell('B22')->setValue($MisPayable);
  1231.         $objSheet->getCell('A23')->setValue($SignCashOp);
  1232.         $objSheet->getCell('B23')->setValue('=B4-B6-B7-B8-B9+B10-B5+B11+B12+B13+B14+B15+B16+B17+B18+B19+B20+B21+B22');
  1233.         $objSheet->getStyle('A23:B23')->getFont()->setBold(true)->setSize(11);
  1234.         $objSheet->getCell('A24')->setValue($SignPreInsurance);
  1235.         $objSheet->getCell('B24')->setValue($PreInsurance);
  1236.         $objSheet->getCell('A25')->setValue($SignPreRent);
  1237.         $objSheet->getCell('B25')->setValue($PreRent);
  1238.         $objSheet->getCell('A26')->setValue($SignPreSupplies);
  1239.         $objSheet->getCell('B26')->setValue($PreSupplies);
  1240.         $objSheet->getCell('A27')->setValue('Building Purchase');
  1241.         $objSheet->getCell('B27')->setValue($BuiPurchase);
  1242.         $objSheet->getCell('A28')->setValue('Equipment Purchase');
  1243.         $objSheet->getCell('B28')->setValue($EquPurchase);
  1244.         $objSheet->getCell('A29')->setValue('Supplies Purchase');
  1245.         $objSheet->getCell('B29')->setValue($SupPurchase);
  1246.         $objSheet->getCell('A30')->setValue($SignNotesPayable);
  1247.         $objSheet->getCell('B30')->setValue($NotesPayable);
  1248.         $objSheet->getCell('A31')->setValue('Add: Proceeds from Asset Sale');
  1249.         $objSheet->getCell('B31')->setValue($ProCash);
  1250.         $objSheet->getCell('A32')->setValue($SignCashInv);
  1251.         $objSheet->getCell('B32')->setValue('=B30+B31-B24-B25-B26-B27-B28-B29');
  1252.         $objSheet->getStyle('A32:B32')->getFont()->setBold(true)->setSize(11);
  1253.         $objSheet->getCell('A33')->setValue('Investment');
  1254.         $objSheet->getCell('B33')->setValue($Capital);
  1255.         $objSheet->getCell('A34')->setValue('Less: Drawing');
  1256.         $objSheet->getCell('B34')->setValue($Drawing);
  1257.         $objSheet->getCell('A35')->setValue($SignCashFin);
  1258.         $objSheet->getCell('B35')->setValue('=B33+B34');
  1259.         $objSheet->getStyle('A35:B35')->getFont()->setBold(true)->setSize(11);
  1260.         $objSheet->getCell('A36')->setValue('Cash: Beginning Month');
  1261.         $objSheet->getCell('B36')->setValue($CashBeginning);
  1262.         $objSheet->getStyle('A36:B36')->getFont()->setBold(true)->setSize(11);
  1263.         $objSheet->getCell('A37')->setValue('Cash: Ending Month');
  1264.         $objSheet->getCell('B37')->setValue('=B23+B32+B35+B36');
  1265.         $objSheet->getStyle('A37:B37')->getFont()->setBold(true)->setSize(11);
  1266.         $objWriter->save('statements/CashFlowStatement' . '_' . $Token . '.xlsx');
  1267.        
  1268.         $ProGain        = $this->toMoney(abs($ProGain));
  1269.         $DepBuilding    = $this->toMoney($DepBuilding * -1);
  1270.         $DepEquipment   = $this->toMoney($DepEquipment * -1);
  1271.         $DepSupplies    = $this->toMoney($DepSupplies * -1);
  1272.         $UnuRevenue     = $this->toMoney(abs($UnuRevenue));
  1273.         $AccReceivable  = $this->toMoney(abs($AccReceivable));
  1274.         $CostRevPayable = $this->toMoney(abs($CostRevPayable));
  1275.         $SalPayable     = $this->toMoney(abs($SalPayable));
  1276.         $WagPayable     = $this->toMoney(abs($WagPayable));
  1277.         $TelPayable     = $this->toMoney(abs($TelPayable));
  1278.         $UtiPayable     = $this->toMoney(abs($UtiPayable));
  1279.         $RenPayable     = $this->toMoney(abs($RenPayable));
  1280.         $AdvPayable     = $this->toMoney(abs($AdvPayable));
  1281.         $IntPayable     = $this->toMoney(abs($IntPayable));
  1282.         $VatPayable     = $this->toMoney(abs($VatPayable));
  1283.         $TaxPayable     = $this->toMoney(abs($TaxPayable));
  1284.         $LegPayable     = $this->toMoney(abs($LegPayable));
  1285.         $MisPayable     = $this->toMoney(abs($MisPayable));
  1286.         $Income         = $this->toMoney(abs($Income));
  1287.         $CashOp         = $this->toMoney(abs($CashOp));
  1288.         $ProCash      = $this->toMoney($ProCash);
  1289.         $PreInsurance = $this->toMoney(abs($PreInsurance));
  1290.         $PreRent      = $this->toMoney(abs($PreRent));
  1291.         $PreSupplies  = $this->toMoney(abs($PreSupplies));
  1292.         $BuiPurchase  = $this->toMoney($BuiPurchase);
  1293.         $EquPurchase  = $this->toMoney($EquPurchase);
  1294.         $SupPurchase  = $this->toMoney($SupPurchase);
  1295.         $NotesPayable = $this->toMoney(abs($NotesPayable));
  1296.         $CashInv      = $this->toMoney(abs($CashInv));
  1297.         $Capital = $this->toMoney($Capital);
  1298.         $Drawing = $this->toMoney($Drawing * -1);
  1299.         $CashFin = $this->toMoney(abs($CashFin));
  1300.         $CashBeginning = $this->toMoney($CashBeginning);
  1301.         $CashEnding    = $this->toMoney($CashEnding);
  1302.         echo <<<EOT
  1303.             <div class="ui card width-full">
  1304.                 <div class="content">
  1305.                     <div class="header"><i class="cubes icon"></i>Statement of Cash Flow</div>
  1306.                     <div class="meta">For the month of $Month'$Year</div>
  1307.                     <table class="ui">
  1308.                         <thead>
  1309.                             <tr>
  1310.                                 <th class="summary-width-left"><i class="book icon"></i>Operating Cash Flow</th>
  1311.                                 <th class="summary-width-right">Amount</th>
  1312.                             </tr>
  1313.                         </thead>
  1314.                         <tbody>
  1315.                             <tr><td>$SignIncome</td><td>Rp. $Income</td></tr>
  1316.                             <tr><td>$SignAccReceivable</td><td>Rp. $AccReceivable</td></tr>
  1317.                             <tr><td>$SignProGain</td><td>Rp. $ProGain</td></tr>
  1318.                             <tr><td>Add: Dep. Expense Building:</td><td>Rp. $DepBuilding</td></tr>
  1319.                             <tr><td>Add: Dep. Expense Equipment:</td><td>Rp. $DepEquipment</td></tr>
  1320.                             <tr><td>Add: Dep. Expense Supplies:</td><td>Rp. $DepSupplies</td></tr>
  1321.                             <tr><td>$SignUnuRevenue</td><td>Rp. $UnuRevenue</td></tr>
  1322.                             <tr><td>$SignCostRevPayable</td><td>Rp. $CostRevPayable</td></tr>
  1323.                             <tr><td>$SignSalPayable</td><td>Rp. $SalPayable</td></tr>
  1324.                             <tr><td>$SignWagPayable</td><td>Rp. $WagPayable</td></tr>
  1325.                             <tr><td>$SignTelPayable</td><td>Rp. $TelPayable</td></tr>
  1326.                             <tr><td>$SignUtiPayable</td><td>Rp. $UtiPayable</td></tr>
  1327.                             <tr><td>$SignRenPayable</td><td>Rp. $RenPayable</td></tr>
  1328.                             <tr><td>$SignAdvPayable</td><td>Rp. $AdvPayable</td></tr>
  1329.                             <tr><td>$SignIntPayable</td><td>Rp. $IntPayable</td></tr>
  1330.                             <tr><td>$SignVatPayable</td><td>Rp. $VatPayable</td></tr>
  1331.                             <tr><td>$SignTaxPayable</td><td>Rp. $TaxPayable</td></tr>
  1332.                             <tr><td>$SignLegPayable</td><td>Rp. $LegPayable</td></tr>
  1333.                             <tr><td>$SignMisPayable</td><td>Rp. $MisPayable</td></tr>
  1334.                         </tbody>
  1335.                         <tfoot>
  1336.                             <tr>
  1337.                                 <th>$SignCashOp</th>
  1338.                                 <th>$CashOp</th>
  1339.                             </tr>
  1340.                         </tfoot>
  1341.                     </table>
  1342. EOT;
  1343.         echo <<<EOT
  1344.                     <table class="ui">
  1345.                         <thead>
  1346.                             <tr>
  1347.                                 <th class="summary-width-left"><i class="book icon"></i>Investing Cash Flow</th>
  1348.                                 <th class="summary-width-right">Amount</th>
  1349.                             </tr>
  1350.                         </thead>
  1351.                         <tbody>
  1352.                             <tr><td>$SignPreInsurance</td><td>Rp. $PreInsurance</td></tr>
  1353.                             <tr><td>$SignPreRent</td><td>Rp. $PreRent</td></tr>
  1354.                             <tr><td>$SignPreSupplies</td><td>Rp. $PreSupplies</td></tr>
  1355.                             <tr><td>Building Purchase:</td><td>Rp. $BuiPurchase</td></tr>
  1356.                             <tr><td>Equipment Purchase:</td><td>Rp. $EquPurchase</td></tr>
  1357.                             <tr><td>Supplies Purchase:</td><td>Rp. $SupPurchase</td></tr>
  1358.                             <tr><td>$SignNotesPayable</td><td>Rp. $NotesPayable</td></tr>
  1359.                             <tr><td>Add: Proceeds from Asset Sale:</td><td>Rp. $ProCash</td></tr>
  1360.                         </tbody>
  1361.                         <tfoot>
  1362.                             <tr>
  1363.                                 <th>$SignCashInv</th>
  1364.                                 <th>$CashInv</th>
  1365.                             </tr>
  1366.                         </tfoot>
  1367.                     </table>
  1368. EOT;
  1369.        
  1370.         echo <<<EOT
  1371.                     <table class="ui">
  1372.                         <thead>
  1373.                             <tr>
  1374.                                 <th class="summary-width-left"><i class="book icon"></i>Financing Cash Flow</th>
  1375.                                 <th class="summary-width-right">Amount</th>
  1376.                             </tr>
  1377.                         </thead>
  1378.                         <tbody>
  1379.                             <tr><td>Investment:</td><td>Rp. $Capital</td></tr>
  1380.                             <tr><td>Less: Drawing:</td><td>Rp. $Drawing</td></tr>
  1381.                         </tbody>
  1382.                         <tfoot>
  1383.                             <tr>
  1384.                                 <th>$SignCashFin</th>
  1385.                                 <th>$CashFin</th>
  1386.                             </tr>
  1387.                             <tr>
  1388.                                 <th>Cash: Beginning Period:</th>
  1389.                                 <th>$CashBeginning</th>
  1390.                             </tr>
  1391.                             <tr>
  1392.                                 <th>Cash: Ending Period:</th>
  1393.                                 <th>$CashEnding</th>
  1394.                             </tr>
  1395.                         </tfoot>
  1396.                     </table>
  1397.                     <div class="summary">
  1398.                         <div class="description waterfall left">
  1399. EOT;
  1400.         $this->cashWaterFall($graphCash, $graphCashBeginning, $graphCashEnding);
  1401.         echo <<<EOT
  1402.                         </div>
  1403.                         <div class="description in-words right">
  1404.                             Summary: $CashFlowSummary
  1405.                         </div>
  1406.                     </div>
  1407.                 </div>
  1408.                 <div class="extra">
  1409.                     Download
  1410.                     <a href='statements/CashFlowStatement_$Token.xlsx'><i class="table icon"></i></a>
  1411.                 </div>
  1412.             </div>
  1413.             <div class="ui horizontal divider"><i class="anchor icon"></i></div>
  1414. EOT;
  1415.         return array(
  1416.             $CashEndingReturn
  1417.         );
  1418.     }
  1419.  
  1420.     /*------------------------------------------------------------------
  1421.     [Preview bank reconciliation and create excel export]
  1422.     -------------------------------------------------------------------*/
  1423.  
  1424.     public function previewBankReconciliation($CashEnding, $objExcelRecon)
  1425.     {
  1426.         $Token = strtotime("now");
  1427.         $Month = $_SESSION['month'];
  1428.         $Year  = $_SESSION['year'];
  1429.        
  1430.         $siteQuery = "SELECT * FROM balance WHERE Status = 'Open'";
  1431.         $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  1432.         $row = mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  1433.         $DepTransit  = $row['deptransit'];
  1434.         $OutChecks   = $row['outchecks'];
  1435.         $PettyCash   = $row['pettycash'];
  1436.         $BankBalance = round($CashEnding + $DepTransit + $OutChecks + $PettyCash, 2);
  1437.        
  1438.         $objExcelRecon->getDefaultStyle()->getFont()->setName('Segoe UI');
  1439.         $objExcelRecon->getDefaultStyle()->getFont()->setSize(11);
  1440.         $objWriter = PHPExcel_IOFactory::createWriter($objExcelRecon, "Excel2007");
  1441.         $objSheet  = $objExcelRecon->getActiveSheet();
  1442.         $objSheet->setTitle('Bank Reconciliation Statement');
  1443.         $objSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  1444.         $objSheet->getStyle('A1:B1')->getFont()->setBold(true)->setSize(11);
  1445.         $objSheet->getColumnDimension('A')->setWidth(50);
  1446.         $objSheet->getColumnDimension('B')->setWidth(50);
  1447.         $objSheet->getStyle('B2:B8')->getNumberFormat()->setFormatCode('#,##0.00');
  1448.         $objSheet->mergeCells('A1:B1');
  1449.         $objSheet->getCell('A1')->setValue('Statement of Bank Reconciliation');
  1450.         $objSheet->mergeCells('A2:B2');
  1451.         $objSheet->getCell('A2')->setValue('For the month ending ' . $Month . '\'' . $Year);
  1452.         $objSheet->getStyle('A3:B3')->getFont()->setBold(true)->setSize(11);
  1453.         $objSheet->getCell('A3')->setValue('Account');
  1454.         $objSheet->getCell('B3')->setValue('Amount');
  1455.         $objSheet->getCell('A4')->setValue('Cash: Account Statement');
  1456.         $objSheet->getCell('B4')->setValue($CashEnding);
  1457.         $objSheet->getCell('A5')->setValue('Deposit in Transit');
  1458.         $objSheet->getCell('B5')->setValue($DepTransit);
  1459.         $objSheet->getCell('A6')->setValue('Outstanding Checks');
  1460.         $objSheet->getCell('B6')->setValue($OutChecks);
  1461.         $objSheet->getCell('A7')->setValue('Petty Cash');
  1462.         $objSheet->getCell('B7')->setValue($PettyCash);
  1463.         $objSheet->getCell('A8')->setValue('Cash: Bank Statement');
  1464.         $objSheet->getCell('B8')->setValue('=B4+B5+B6+B7');
  1465.         $objSheet->getStyle('A8:B8')->getFont()->setBold(true)->setSize(11);
  1466.         $objWriter->save('statements/ReconciliationStatement' . '_' . $Token . '.xlsx');
  1467.        
  1468.         $CashEnding  = $this->toMoney($CashEnding);
  1469.         $DepTransit  = $this->toMoney($DepTransit * -1);
  1470.         $OutChecks   = $this->toMoney($OutChecks);
  1471.         $PettyCash   = $this->toMoney($PettyCash * -1);
  1472.         $BankBalance = $this->toMoney($BankBalance);
  1473.        
  1474.         echo <<<EOT
  1475.             <div class="ui card width-full">
  1476.                 <div class="content">
  1477.                     <div class="header"><i class="cubes icon"></i>Statement of Bank Reconciliation</div>
  1478.                     <div class="meta">For the month ending $Month'$Year</div>
  1479.                     <table class="ui">
  1480.                         <thead>
  1481.                             <tr>
  1482.                                 <th class="summary-width-left"><i class="book icon"></i>Particulars</th>
  1483.                                 <th class="summary-width-right">Amount</th>
  1484.                             </tr>
  1485.                         </thead>
  1486.                         <tbody>
  1487.                             <tr><td>Cash: Account Statement:</td><td>Rp. $CashEnding</td></tr>
  1488.                             <tr><td>Deposit in Transit:</td><td>Rp. $DepTransit</td></tr>
  1489.                             <tr><td>Outstanding Checks:</td><td>Rp. $OutChecks</td></tr>
  1490.                             <tr><td>Petty Cash:</td><td>Rp. $PettyCash</td></tr>
  1491.                         </tbody>
  1492.                         <tfoot>
  1493.                             <tr>
  1494.                                 <th>Cash: Bank Statement</th>
  1495.                                 <th>$BankBalance</th>
  1496.                             </tr>
  1497.                         </tfoot>
  1498.                     </table>
  1499.                 </div>
  1500.                 <div class="extra">
  1501.                     Download
  1502.                     <a href='statements/ReconciliationStatement_$Token.xlsx'><i class="table icon"></i></a>
  1503.                 </div>
  1504.             </div>
  1505.             <div class="ui horizontal divider"><i class="anchor icon"></i></div>
  1506. EOT;
  1507.     }
  1508.  
  1509.     /*------------------------------------------------------------------
  1510.     [Check correct balance sheet to view closing button]
  1511.     -------------------------------------------------------------------*/
  1512.  
  1513.     public function correctBalance($Asset, $Liability, $Equity, $Cash, $CashEnding, $CheckError)
  1514.     {
  1515.         $Asset = round($Asset, 2);
  1516.         $Equity = round($Liability + $Equity, 2);
  1517.         $Cash = round($Cash, 2);
  1518.         $CashEnding = round($CashEnding, 2);
  1519.         if ($Asset == $Equity && $Cash == $CashEnding && $CheckError == "Correct")
  1520.             return TRUE;
  1521.         else
  1522.             return FALSE;
  1523.     }
  1524.  
  1525.     /*------------------------------------------------------------------
  1526.     [Get next month from current month]
  1527.     -------------------------------------------------------------------*/
  1528.  
  1529.     public function getNextMonth($Month, $Year)
  1530.     {
  1531.         if ($Month == "Jan")
  1532.             $Month = "Feb";
  1533.         elseif ($Month == "Feb")
  1534.             $Month = "Mar";
  1535.         elseif ($Month == "Mar")
  1536.             $Month = "Apr";
  1537.         elseif ($Month == "Apr")
  1538.             $Month = "May";
  1539.         elseif ($Month == "May")
  1540.             $Month = "Jun";
  1541.         elseif ($Month == "Jun")
  1542.             $Month = "Jul";
  1543.         elseif ($Month == "Jul")
  1544.             $Month = "Aug";
  1545.         elseif ($Month == "Aug")
  1546.             $Month = "Sep";
  1547.         elseif ($Month == "Sep")
  1548.             $Month = "Oct";
  1549.         elseif ($Month == "Oct")
  1550.             $Month = "Nov";
  1551.         elseif ($Month == "Nov")
  1552.             $Month = "Dec";
  1553.         elseif ($Month == "Dec") {
  1554.             $Month = "Jan";
  1555.             $Year  = $Year + 1;
  1556.         }
  1557.         return array(
  1558.             $Month,
  1559.             $Year
  1560.         );
  1561.     }
  1562.  
  1563.     /*------------------------------------------------------------------
  1564.     [Backup database on month closing]
  1565.     -------------------------------------------------------------------*/
  1566.  
  1567.     public function backupDatabase($Month, $Year)
  1568.     {
  1569.         $return = "";
  1570.         $tables = array('balance','customer','entry','info','records','recurring','reminders');
  1571.         if($tables == '*')
  1572.         {
  1573.             $result = mysqli_query($this->con, 'SHOW TABLES');
  1574.             while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
  1575.             {
  1576.                 $tables[] = $row[0];
  1577.             }
  1578.         }
  1579.         else
  1580.         {
  1581.             $tables = is_array($tables) ? $tables : explode(',',$tables);
  1582.         }
  1583.         foreach($tables as $table)
  1584.         {
  1585.             $result = mysqli_query($this->con, 'SELECT * FROM '.$table);
  1586.             $num_fields = mysqli_num_fields($result);
  1587.            
  1588.             $return.= 'DROP TABLE '.$table.';';
  1589.             $row2 = mysqli_fetch_row(mysqli_query($this->con, 'SHOW CREATE TABLE '.$table));
  1590.             $return.= "\n\n".$row2[1].";\n\n";
  1591.            
  1592.             for ($i = 0; $i < $num_fields; $i++)
  1593.             {
  1594.                 while($row = mysqli_fetch_row($result))
  1595.                 {
  1596.                     $return.= 'INSERT INTO '.$table.' VALUES(';
  1597.                     for($j=0; $j<$num_fields; $j++)
  1598.                     {
  1599.                         $row[$j] = addslashes($row[$j]);
  1600.                         $row[$j] = ereg_replace("\n","\\n",$row[$j]);
  1601.                         if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
  1602.                         if ($j<($num_fields-1)) { $return.= ','; }
  1603.                     }
  1604.                     $return.= ");\n";
  1605.                 }
  1606.             }
  1607.             $return.="\n\n\n";
  1608.         }
  1609.         $handle = fopen('../../backup/Monthly-backup-'.$Month.'-'.$Year.'.sql','w+');
  1610.         fwrite($handle,$return);
  1611.         fclose($handle);
  1612.     }
  1613.  
  1614.     /*------------------------------------------------------------------
  1615.     [Insert balance sheet into archive and set info for next month]
  1616.     -------------------------------------------------------------------*/
  1617.  
  1618.     public function insertArchive()
  1619.     {
  1620.         $siteQuery = "SELECT * FROM info WHERE Status = 'Open'";
  1621.         $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  1622.         $siteInfo = mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  1623.         $ID       = $siteInfo['ID'];
  1624.         $Month    = $siteInfo['month'];
  1625.         $Year     = $siteInfo['year'];
  1626.  
  1627.         $this->backupDatabase($Month, $Year);
  1628.  
  1629.         $siteQuery = "INSERT INTO records
  1630.            (ID, month, year, cash, procash, accrecser, preins, preren, building, cashbui, depbui, equipment,
  1631.                cashequ, depequ, presup, supplies, cashsup, depsup, unuser, costrevpay, notpay, intpay, equpay,
  1632.                salpay, wagpay, telpay, utipay, renpay, suppay, advpay, vatpay, taxpay, legpay, mispay, costrevexp,
  1633.                depbuiexp, depequexp, depsupexp, insexp, renexp, salexp, wagexp, telexp, utiexp, advexp, intexp,
  1634.                vatexp, taxexp, legexp, misexp, baddebtexp, serrev, intrev, capital, drawing, cashbeginning,
  1635.                eqbeginning, progain, deptransit, outchecks, pettycash) select
  1636.            ID, month, year, cash, procash, accrecser, preins, preren, building, cashbui, depbui, equipment,
  1637.                cashequ, depequ, presup, supplies, cashsup, depsup, unuser, costrevpay, notpay, intpay, equpay,
  1638.                salpay, wagpay, telpay, utipay, renpay, suppay, advpay, vatpay, taxpay, legpay, mispay, costrevexp,
  1639.                depbuiexp, depequexp, depsupexp, insexp, renexp, salexp, wagexp, telexp, utiexp, advexp, intexp,
  1640.                vatexp, taxexp, legexp, misexp, baddebtexp, serrev, intrev, capital, drawing, cashbeginning,
  1641.                eqbeginning, progain, deptransit, outchecks, pettycash from balance WHERE Status='Open'";
  1642.         $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  1643.         $siteInfo = mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  1644.        
  1645.         $siteQuery = "SELECT * FROM info WHERE Status = 'Open'";
  1646.         $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  1647.         $siteInfo = mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  1648.  
  1649.         $ID       = $ID + 1;
  1650.         list($Month, $Year) = $this->getNextMonth($Month, $Year);
  1651.         $siteQuery = "UPDATE info SET month='$Month', year='$Year', ID='$ID' WHERE Status = 'Open'";
  1652.         $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  1653.         mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  1654.        
  1655.         $siteQuery = "UPDATE entry SET Status='Off' WHERE Status = 'On'";
  1656.         $siteResult = mysqli_query($this->con, $siteQuery) or die('MySql Error' . mysql_error());
  1657.         mysqli_fetch_array($siteResult, MYSQLI_ASSOC);
  1658.     }
  1659. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!