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

class Fixtax extends CI_Controller {
	public $headerTbl = array(
								"comp_cash_sales"=>
										array(
											"comp_cash_sales_charges"=>"cashSaleID",
											"comp_cash_sales_detail"=>"invoiceID",
										),
								"comp_delivery_order"=>
										array(
											"comp_delivery_detail"=>"deliveryID"
										),
								"comp_location_transfer"=>
										array(
											"comp_location_transfer_detail"=>"locationTransferID"
										),
								"comp_payment"=>
										array(
											"comp_payment_detail"=>"paymentID"
										),
								"comp_purchase_order"=>
										array(
											"comp_purchase_order_detail"=>"purchaseOrderID"
										),
								"comp_quotation"=>
										array(
											"comp_quotation_detail"=>"quotationID"
										),
								"comp_receive_item"=>
										array(
											"comp_receive_item_detail"=>"receiveID"
										),
								"comp_receive_payment"=>
										array(
											"comp_receive_payment_detail"=>"receivePaymentID"
										),
								"comp_receive_payment_others"=>
										array(
											"comp_receive_payment_others_detail"=>"receivePaymentOthersID"
										),
								"comp_sales_creditnote"=>
										array(
											"comp_sales_creditnote_detail"=>"creditnoteID",
											"comp_sales_creditnote_charges"=>"creditNoteID"
										),
								"comp_sales_debitnote"=>
										array(
											"comp_sales_debitnote_detail"=>"debitnoteID",
											"comp_sales_debitnote_charges"=>"debitNoteID"
										),
								"comp_sales_order"=>
										array(
											"comp_sales_detail"=>"salesID"
										),
								"comp_sales_invoice"=>
										array(
											"comp_sales_invoice_detail"=>"invoiceID",
											"comp_sales_invoice_charges"=>"invoiceID"
										),
								"comp_supplier_creditnote"=>
										array(
											"comp_supplier_creditnote_detail"=>"creditnoteID",
											"comp_supplier_creditnote_charges"=>"creditNoteID"
										),
								"comp_supplier_debitnote"=>
										array(
											"comp_supplier_debitnote_detail"=>"debitnoteID",
											"comp_supplier_debitnote_charges"=>"debitNoteID"
										),
								"comp_supplier_invoice"=>
										array(
											"comp_supplier_invoice_detail"=>"invoiceID",
											"comp_supplier_invoice_charges"=>"invoiceID"
										),
								"item_adjustment"=>
										array(
											"item_adjustment_detail"=>"adjustmentID"
										),
								"wakaf"=>
										array(
											"wakaf_details_allocation"=>"wakafID",
											"wakaf_details_distribution"=>"wakafID"
										),
								"zakat_summary"=>
										array(
											"zakat_details"=>"zakatSummaryID"
										),
								"comp_payment_others"=>
										array(
											"comp_payment_others_with_item"=>"otherPaymentID",
											"comp_payment_others_without_item"=>"otherPaymentID"
										),							
								"comp_gst_transaction"=>
										array(
											"comp_gst_transaction_detail"=>"gstTransID"
										),
							);

	public function index(){
		$sql = "update _ref_tax_master set defaultTaxAccount = '810040000', taxClass = 1 where id = 18";
		$this->db->query($sql);

		$db=strtolower($this->db->database);
		$tableIn= "Tables_in_".$db;
		$tblList = $this->db->query("SHOW TABLES FROM ".$db)->result();
		$replaceCode = $this->getRefTaxID(array('TX','BL'));	//260010000,810040000
		if(!empty($replaceCode)){
			echo "Fixing tax account code!";
			foreach($tblList as $v){
				preg_match('/^_ref/', $v->$tableIn, $ref);
				preg_match('/^view_/', $v->$tableIn, $view);
				preg_match('/_detail/', $v->$tableIn, $detail);
				preg_match('/_charges/', $v->$tableIn, $charges);
				
				//Avoiding header tables
				if((!empty($charges) || !empty($detail)) && empty($ref) && empty($view)){

					$taxID = $this->db->query("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '".$db."' AND TABLE_NAME = '".$v->$tableIn."' AND COLUMN_NAME like '%taxID%'")->result();	
					
					
					if(!empty($taxID)){
						$hdrTbl = $this->getHeaderTable($v->$tableIn); 
						$journalNo = $this->db->query("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '".$db."' AND TABLE_NAME = '".$hdrTbl[0]."' AND COLUMN_NAME like '%journalNo%'")->result();
						if(!empty($hdrTbl) && !empty($journalNo)){
							$this->fixIT($hdrTbl[0],$hdrTbl[1],$hdrTbl[2],$replaceCode);
						}
					}
					echo "*********************************************************<br />";
				}
			}
			$this->fixTaxMaster($replaceCode);
		}else{
			echo "Tax code fixed already!";
		}
		
	}

	public function fixTaxMaster($data){
		foreach($data as $v){
			$this->db->where('ID',$v['taxID'])
					->update('tax_master',array('taxAccount'=>$v['replaceCode']));
		}
	}

	public function fixIT($tblHdr,$tbl,$conn,$repaceCode){

		foreach($repaceCode as $v){
			if($tbl == 'comp_gst_transaction_detail') continue;
			$temp = $this->db->select("a.lineNo,a.ID childID,b.ID,b.journalNo")
					->from($tbl." as a")
					->join($tblHdr." as b","b.ID = a.".$conn,"left")
					->where(array('a.taxID'=>$v['taxID'],'b.companyID'=>$v['companyID']))->get()->result();
			

			if(!empty($temp[0]->journalNo)){
				//echo $v['code']."<->".$v['taxID']."<->".$temp[0]->lineNo."<->".$v['prevCode']."<->".$temp[0]->journalNo."<->".$tblHdr."<->".$tbl."<->".$temp[0]->childID.PHP_EOL;

				//$this->db->set('acctCode',$v['replaceCode'])
				//$this->db->select('*')
				$this->db->where(array('line'=>$temp[0]->lineNo,'acctCode'=>$v['prevCode'],'journalID'=>$temp[0]->journalNo))
						->update('comp_journal_detail',array('acctCode'=>$v['replaceCode']));

			}
		}
	}

	public function getRefTaxID($tax){
		foreach($tax as $v){
			$taxID = $this->db->select('ID,code,defaultTaxAccount')
						->from('_ref_tax_master')
						->where('code',$v)->get()->result_array();	
			$replaceCode[] = $this->db->select('a.ID taxID,a.code,a.taxAccount as prevCode,a.companyID')
									->select('(select ID from comp_chart_of_acct where acctCode="'.$taxID[0]['defaultTaxAccount'].'" and companyID = a.companyID) as replaceCode',false)
									->from('tax_master a')
									->where(array('code'=>$v))->get()->result_array();

		}
		$final = array();

		foreach($replaceCode as &$eachCode){
			foreach($eachCode as &$row){
				if($row['prevCode'] != $row['replaceCode']){
					$final[] = $row;
				}
			}
		}
		
		return $final;
	}


	public function getHeaderTable($childTbl){
		foreach($this->headerTbl as $key => $v){
			if(isset($v[$childTbl])){
				return array($key,$childTbl,$v[$childTbl]);
			}
		}
		return false;
	}
}