<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class TestAcct extends CI_Controller {
	
	public function __construct(){
		parent::__construct();
		$this->load->model('Useraccess');
		$query = $this->Useraccess->useraccess_id_module2('10', 'setup');
		if(!$query){
			echo '<script>alert("Not authorised..");
				window.location.replace("'.base_url().'Error");
				</script>';
		}
		$this->load->library('layouts');
//		$this->load->model('menu');
	}
//senarai select
//SELECT a.id, a.accountcode, a.accountid, b.id,b.acctcode, b.acctname 
	//FROM `comp_control_acct_setup` a , comp_chart_of_acct b 
	//WHERE a.companyid = 15 and a.accountid = b.id
//SELECT * FROM `_ref_chart_of_acct` where businesstypeid = 1 ORDER BY `acctCode` ASC
//SELECT * FROM `comp_chart_of_acct` WHERE companyid =15 order by acctcode
//SELECT a.id, a.accountcode, a.accountid, b.id,b.acctcode, b.acctname 
	//FROM `comp_control_acct_setup` a , comp_chart_of_acct b 
	//WHERE a.companyid = 15 and a.accountid = b.id
//SELECT a.id, a.itemcode,a.name, 
	//a.incomeid, b.acctname as saleAcct,
	//a.cogsid, c.acctname as cogsacct
	//a.inventoriesid, d.actname as inventoryacct
	//a.inputtaxid, 
//a.outputtaxid
//FROM `item` a 
//left outer join comp_chart_of_acct b on a.incomeid = b.id,
//left outer join comp_chart_of_acct c on a.cogsid = c.id,
//left outer join comp_chart_of_acct d on a.inventoriesid = d.id,
//left outer join tax_master e on a.inputtaxid = e.id 
//left outer join tax_master f on a.outputtaxid = f.id 



//	    $this->db->where('ID', element('compID', $this->session->userdata('logged_in')));

	public function index()
	{   
/*		$tajuk = '';
	    $query = $this->db->query("");
		$this->display($query->result(),$tajuk);
*/
//var_dump($this);

		$companyID = element('compID', $this->session->userdata('logged_in'));

		echo '<h3>['.$companyID.']'.element('compName', $this->session->userdata('logged_in')).'<h3>';
		echo '<h4>Account Checklist</h4>';
		$tajuk = 'Chart Of Account';
	    $query = $this->db->query("select a.id, a.acctcode, a.acctname, a.acctGroupID, b.acctGroupName, d.acctTypeName from (comp_chart_of_acct a left outer join _ref_acct_group b on a.acctgroupid = b.id) left outer join comp_acct_type d on d.id = b.accttypeid where a.companyid= ".$companyID. " order by acctcode" );
		$this->display($query->result(),$tajuk);

		$tajuk = 'Tax Master';
	    $query = $this->db->query("select a.id, a.code, a.name, a.taxtype, b.description as taxTypeDescription, a.taxpercentage, a.taxclass, c.shortname as taxclassname, a.taxgroup, d.description as taxGroupName, a.taxaccount, concat('[',e.acctcode,']',e.acctname) as chartacct from (((tax_master a left outer join tax_type b on a.taxtype = b.id ) left outer join tax_class c on c.id = a.taxclass) left outer join tax_group d on d.id = b.taxgroupid) left outer join comp_chart_of_acct e on e.id=a.taxaccount where a.companyID = ".$companyID." order by a.code");
		$this->display($query->result(),$tajuk);

		$tajuk = 'Default System General Setup';
	    $query = $this->db->query("SELECT a.id, a.accountcode, a.accountid, b.id,b.acctcode, b.acctname, d.acctTypeName FROM ((comp_control_acct_setup a left outer join comp_chart_of_acct b on a.accountid = b.id ) left outer join _ref_acct_group c on c.id = b.acctgroupID ) left outer join comp_acct_type d on d.id = c.accttypeid WHERE a.companyid = ".$companyID." ");
		$this->display($query->result(),$tajuk);

		$tajuk = 'Item and Acct Setup';
	    $query = $this->db->query("SELECT a.id, a.itemcode,a.name as item, a.incomeid, concat('[',b.acctcode,']',b.acctname) as saleAcct, a.cogsid, concat('[',c.acctcode,']',c.acctname) as cogsacct, a.inventoriesid, concat('[',d.acctcode,']' , d.acctname) as inventoryacct, a.inputtaxid, e.code as inputCode, a.outputtaxid, f.code as outputCode FROM ((((`item` a left outer join comp_chart_of_acct b on a.incomeid = b.id) left outer join comp_chart_of_acct c on a.cogsid = c.id) left outer join comp_chart_of_acct d on a.inventoriesid = d.id) left outer join tax_master e on a.inputtaxid = e.id ) left outer join tax_master f on a.outputtaxid = f.id WHERE a.companyid=".$companyID." order by a.name");
		$this->display($query->result(),$tajuk);

		$tajuk = 'Customer';
	    $query = $this->db->query("select a.id, a.customername, a.accountcode, a.linkedAPAccountid as ARaccount, concat('[',b.acctcode,']',b.acctname) as chartacct from customer a left outer join comp_chart_of_acct b on b.id = a.linkedAPAccountid where a.companyid=".$companyID." order by a.customername");
		$this->display($query->result(),$tajuk);

		$tajuk = 'Supplier';
	    $query = $this->db->query("select a.id, a.suppliername, a.suppliercode, a.linkedAPAccountid as APaccount, concat('[',b.acctcode,']',b.acctname) as chartacct  from supplier  a left outer join comp_chart_of_acct b on b.id = a.linkedAPAccountid where a.companyid=".$companyID." order by a.suppliername");
		$this->display($query->result(),$tajuk);

	}

private function display($rs, $tajuk='Tajuk'){
	if ($rs){
		echo '<h4>'.$tajuk.'</h4>';
		echo '<table border="0">';
		echo '<tr valign="top" bgcolor="#99CCFF" class="sambung2"><td align="center"><b>No</b></td>';
//		var_dump($rs);
//		for ($i = 1; $i <= sizeof($rs[0]); $i++) {
		foreach($rs[0] as $key => $val) {
			echo '<td align="center"><b>'.$key.'</b></td>'; 
			}
		echo '</tr>'; 
		$currentrow = 0;
		foreach($rs as $row){
			$currentrow++;
			echo '<tr class="HighlightableRow"';
			$flagrekod = $currentrow % 2; 
			if ($flagrekod == 1) 
				echo 'bgcolor="F0F1F1"'; 
			else echo 'bgcolor="FFFFFF"';	
			echo ' class="sambung2" valign="top" >';
			echo '<td>'.$currentrow.'</td>';
			foreach ($row as $col => $colVal) {
//		var_dump($col);
				echo '<td>&nbsp;'.$colVal.'&nbsp;</td>'; 
			}
			echo '</tr>'; 
		}
		echo '</table>'; 
	}else $this->db->last_query().'<br>table empty';
}


//to add chartofacct not included in the company. work for all company 
public function addChartOfAcct(){
	$rs = $this->db->query('select id from company order by id');
	$rsCompany = $rs->result();
	foreach ($rsCompany as $row) {
		foreach ($row as $rowCol => $rowVal){
			echo '<br>rowcol'.$rowCol.' rowval'.$rowVal; 
			$rsChart = $this->db->query('select acctCode, acctName, acctGroupID from _ref_chart_of_acct where acctCode not in (select acctCode from comp_chart_of_acct where companyid = '.$rowVal.') and acctCode in ("310000000","810031000","810032000","810033000") order by acctCode');
			echo '<br>'.$this->db->last_query().'<br>';
			$rsChartOfAcct = $rsChart->result();  
			foreach($rsChartOfAcct as $val) {
						$acctCode = $val->acctCode;
						$acctName = $val->acctName;
						$acctGroupID = $val->acctGroupID;
						$sqlinsert = 'insert into comp_chart_of_acct (companyid, acctCode,acctname,acctGroupID,createdBy) values ('.$rowVal.',"'.$acctCode.'","'.$acctName.'",'.$acctGroupID.',1)';
						echo '<br>'.$sqlinsert;
		//			$this->db->query($sqlinsert);
			}
		}
	}

}


}