<?php
include('includes/session.php');
ini_set('memory_limit', '512M'); //Raise to 512 MB
$seconds = 0;
set_time_limit ( $seconds );

$title=_('GL Report');

require_once(dirname(__FILE__) . "/Classes/PHPExcel.php");
$phpExcel = new PHPExcel();
 
$styleArray = array(
	'font' => array(
		'bold' => true,
	)
);

if(isset($_POST['export']))
{
    //to get information from form
    $date_start =  FormatDateForSQL($_POST['date_start']);
    $date_end =  FormatDateForSQL($_POST['date_end']);
    
    $period_start = $_POST['StartPeriod'];
    $period_end =  $_POST['EndPeriod'];
    
    if($date_start != "--" && $date_end != "--")
    {
        $where_condition = "gltrans.trandate >= '". $date_start ."' and gltrans.trandate <= '". $date_end ."'";
        
        //20140722 YS Chin: Get Period Name
        $DisplayStart = $date_start;
        $DisplayEnd = $date_end;
    }
    else if ($period_start != "" && $period_end != "")
    {
        $where_condition = "gltrans.periodno >= '". $period_start ."' and gltrans.periodno <= '". $period_end ."'";
        
        //20140722 YS Chin: Get Period Name
        $sql = "select 
                (select lastdate_in_period from periods where periodno = '". $period_start ."') as startperiod,
                (select lastdate_in_period from periods where periodno = '". $period_end ."') as endperiod
                from periods limit 1";
                //where periodno = '". $period_start ."'";
        
        $result=DB_query($sql, $db);
        while($rows = DB_fetch_row($result))
        {
            $DisplayStart = MonthAndYearFromSQLDate($rows[0]);
            $DisplayEnd = MonthAndYearFromSQLDate($rows[1]);   
        }
    }
        
    //Get the active sheet and assign to a variable
    $gl = $phpExcel->getActiveSheet();
    //set sheet title
    $gl->setTitle("GL");
	
    $sql="select distinct gltrans.account, chartmaster.accountname
            from gltrans
            inner join chartmaster
            on gltrans.account=chartmaster.accountcode
            where ".$where_condition."
            order by gltrans.account";
    
    $result=DB_query($sql, $db);
    //echo $sql;
    $i=0;
    
    //set starting row and column for data
    $row = 3;
    $column = 'J';
    
    while($rows = DB_fetch_row($result))
    {
        $cell = $column . $row;
        $account[$i]        =   $rows[0];
        $account_name[$i]   =   $rows[1];

        //column headers, set the title
        $gl->setCellValue($cell, $account[$i]." - ".$account_name[$i]);
        
        $column++;
        $i++;
    }
        
        
    //Document Title/Headers etc
    //20140722 YS Chin: Reformat title
    $gl->setCellValue("A1",$_SESSION['CompanyRecord']['coyname']);
    $gl->setCellValue("A2","General Ledger from " . $DisplayStart . " to " . $DisplayEnd );
    
    $gl->setCellValue("A4","Journal #");
    $gl->setCellValue("B4","Date");
    $gl->setCellValue("C4","Account");
    $gl->setCellValue("D4","Description");
    
    
    //set to be adjustable columns
    $debit_column_excel = 'H';
    $credit_column_excel = 'I';
    $gl->setCellValue($debit_column_excel."4","Dr");
    $gl->setCellValue($credit_column_excel."4","Cr");
    
    
    $sql="select gltrans.counterindex,
                gltrans.trandate,
                gltrans.account,
                chartmaster.accountname,
                gltrans.narrative,
                gltrans.amount,
                gltrans.type,
                gltrans.typeno
            from gltrans
            inner join chartmaster
            on gltrans.account=chartmaster.accountcode
            inner join systypes
            on gltrans.type=systypes.typeid
            inner join periods
            on gltrans.periodno=periods.periodno
            where ".$where_condition."
            order by gltrans.trandate, gltrans.type, gltrans.typeno";
        
    $result=DB_query($sql, $db);
    
    //setting row to begin data
    $row = 4;
    $sheets = 0;
    
    while(list($index,$transdate,$accountno,$accountname,$description,$amount,$type,$typeno)=DB_fetch_row($result))
    {
        if(($type!=$prev_type) && ($typeno!=$prev_type_no))
        {
            ++$row;
        }
        
        $gl->setCellValue("A".$row,$index);
        $gl->setCellValue("B".$row,$transdate);
        // xlsWriteLabel($xlsRow,2,$accountno." - ".$accountname);
        $gl->setCellValue("C".$row,$accountno);
        $gl->setCellValue("D".$row,$accountname);
        $gl->setCellValue("E".$row,$description);
        
        if($amount>=0)
        {
            $gl->setCellValue($debit_column_excel . $row, $amount);
        }
        else if($amount<0)
        {
            $gl->setCellValue($credit_column_excel . $row, str_ireplace("-", "", $amount));
        }
        
        //Create a new sheets for each account code
        $sheet_title = str_replace('/', " ", $accountno);
        $accounts[$sheets] = $accountno;
        
        if($phpExcel->getSheetByName($sheet_title)==null)
        {
            $sheets++;
            $rows_[$sheet_title]=5;
            
            $accounts[$sheets] = $phpExcel->createSheet();
            $accounts[$sheets]->setTitle($sheet_title);

            //$accounts[$sheets]->setCellValue("A1","Project Account - " . $_SESSION['CompanyRecord']['coyname']);
            //20140722 YS Chin: Reformat title
            $accounts[$sheets]->setCellValue("A1",$_SESSION['CompanyRecord']['coyname']);
            $accounts[$sheets]->setCellValue("A2","Account " . $accountno . " " . $accountname . " from " . $DisplayStart . " to " . $DisplayEnd );

            $accounts[$sheets]->setCellValue("A4","Journal #");
            $accounts[$sheets]->setCellValue("B4","Date");
            $accounts[$sheets]->setCellValue("C4","Account");
            $accounts[$sheets]->setCellValue("D4","Description");
            //set to be adjustable columns
            $debit_column_excel = 'H';
            $credit_column_excel = 'I';
            $accounts[$sheets]->setCellValue($debit_column_excel."4","Dr");
            $accounts[$sheets]->setCellValue($credit_column_excel."4","Cr");
        }
                
        $phpExcel->getSheetByName($sheet_title)->setCellValue("A".$rows_[$sheet_title],$index);
        $phpExcel->getSheetByName($sheet_title)->setCellValue("B".$rows_[$sheet_title],$transdate);
        $phpExcel->getSheetByName($sheet_title)->setCellValue("C".$rows_[$sheet_title],$accountno);
        $phpExcel->getSheetByName($sheet_title)->setCellValue("D".$rows_[$sheet_title],$accountname);
        $phpExcel->getSheetByName($sheet_title)->setCellValue("E".$rows_[$sheet_title],$description);
        
        if($amount>=0)
        {
            $phpExcel->getSheetByName($sheet_title)->setCellValue($debit_column_excel . $rows_[$sheet_title], $amount);
            $rows_[$sheet_title]++;
        }
        else if($amount<0)
        {
            $phpExcel->getSheetByName($sheet_title)->setCellValue($credit_column_excel . $rows_[$sheet_title], str_ireplace("-", "", $amount));
            $rows_[$sheet_title]++;
        }
                
        //set starting column for data
        $column_start = 'J';
        
        for($j=0; $i>$j; $j++, $column_start++)
        {
            if($account[$j]==$accountno)
            {
                if($amount >= 0)
                {
                    $cell_formula = "=" . $debit_column_excel  . $row;
                }
                else if ($amount < 0)
                {
                    $cell_formula = "=-" . $credit_column_excel . $row;
                }
            
                $gl->setCellValue($column_start.$row, "$cell_formula");

                break;
            }
        }
        
        $prev_type=$type;
        $prev_type_no=$typeno;
        
        $row++;
    }

    $phpExcel->setActiveSheetIndex(0);
    
    //Output the generated excel file so that the user can save or open the file.
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=\"GLExcelReport.xls\"");
    header("Cache-Control: max-age=0");
    
    $objWriter = PHPExcel_IOFactory::createWriter($phpExcel, "Excel5");
    $objWriter->save("php://output");
    exit;
}

else
{
    $Title = _('Export GL');
    include('includes/header.php');
?>
    <form method="post">
	<?php echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />'; ?>
	
	<?php
	    //20150105 YS Chin: Header for Export to Excel
	    echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/note_add.png" title="' . _('Export') . '" alt="" />' . ' ' . _('Export GL') . '</p>';
	?>

        <table class="selection">
            <tr>
                <th colspan="2">GL Report</th>
            </tr>
            <tr>
                <td>
                    Transaction Start Date:
                </td>
                <td>
		    <?php
		    echo '<input type="Text" class="date" alt="' .$_SESSION['DefaultDateFormat'] .'" name="date_start" />';
		    ?>
                </td>
            </tr>
            <tr>
                <td>
                    Transaction End Date:
                </td>
                <td>
		    <?php
		    echo '<input type="Text" class="date" alt="' .$_SESSION['DefaultDateFormat'] .'" name="date_end" />';
		    ?>
                </td>
            </tr>
	    
	    <tr>
		<td></td>
		<td>
		    OR
		</td>
	    </tr>
	    
	    <tr>
		<td>
		    Posting Period from:</td><td><select Name=StartPeriod>';
		    <?php
		    $sql = "SELECT periodno, lastdate_in_period FROM periods ORDER BY periodno DESC";
		    $Periods = DB_query($sql,$db);
		    $id=0;
		    /*Dates in SQL format */
		    $DefaultPeriod = GetPeriod(date($_SESSION['DefaultDateFormat'],mktime(0,0,0,Date('m')+1,0,Date('Y'))),$db);
		    
		    while ($myrow=DB_fetch_array($Periods,$db)){
		    
				    if($myrow['periodno']==$DefaultPeriod){
					    echo '<option selected="selected" value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
				    } else {
					    echo '<option value ="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
				    }
			    }
		    ?>
		</td>
	    </tr>
	    <tr>
		<td>
		    Posting Period to:</td><td><select Name=EndPeriod>';
		    <?php
		    $sql = "SELECT periodno, lastdate_in_period FROM periods ORDER BY periodno DESC";
		    $Periods = DB_query($sql,$db);
		    $id=0;
		    
		    while ($myrow=DB_fetch_array($Periods,$db)){
		    
				    if($myrow['periodno']==$DefaultPeriod){
					    echo '<option selected="selected" value="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
				    } else {
					    echo '<option value ="' . $myrow['periodno'] . '">' . MonthAndYearFromSQLDate($myrow['lastdate_in_period']) . '</option>';
				    }
			    }
		    ?>
		</td>
	    </tr>
	    
            <tr>
                <td></td>
                <td>
		    <br />
                    <input type="submit" value="Export" name="export">
                </td>
            </tr>
        </table>
    </form>
<?php
}

/*
* Safely encode a string for use as a filename
* @param string $title The title to use for the file
* @return string The file safe title
*/
function filename($title)
{
    $result = strtolower(trim($title));
    $result = str_replace("'", '', $result);
    $result = preg_replace('#[^a-z0-9_]+#', '-', $result);
    $result = preg_replace('#\-{2,}#', '-', $result);
    return preg_replace('#(^\-+|\-+$)#D', '', $result);
}

include('includes/footer.php');
?>

