<?php
class Tax_model extends CI_Model
{

	public function __construct()
	{
		parent::__construct();
	}

	function saveBasicTaxInfo ($data)
	{
		if ($data['tax_basic_row_id'])
		{
			$this->db->where('tax_basic_row_id', $data['tax_basic_row_id']);
			$this->db->update('tbl_tax_basic', $data);
		}
        else
		{
			$this->db->insert('tbl_tax_basic', $data);
		}
	}

	function getBasicTaxInfo ($merchant_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_tax_basic');
		$this->db->where('merchant_row_id', $merchant_row_id);
		$res = $this->db->get();
		$result = $res->row();
		return $result;
	}

	function getStateTaxList($tax_basic_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_tax_state');
		$this->db->where('tax_basic_row_id', $tax_basic_row_id);
        $this->db->order_by('state_name');
		$res = $this->db->get();
		$result = $res->result();
		return $result;
	}

	function getCountryTaxList($tax_basic_row_id)
	{
		$this->db->select('*');
		$this->db->from('tbl_tax_country');
		$this->db->where('tax_basic_row_id', $tax_basic_row_id);
        $this->db->order_by('country_name');
		$res = $this->db->get();
		$result = $res->result();
		return $result;
	}

    function getZipTaxList($tax_basic_row_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_tax_zip');
        $this->db->where('tax_basic_row_id', $tax_basic_row_id);
        $this->db->order_by('zip_code');
        $res = $this->db->get();
        $result = $res->result();
        return $result;
    }

    function getTaxRate($merchant_row_id, $country, $state, $zip)
    {
        $taxBasic = $this->getBasicTaxInfo($merchant_row_id);

        $taxRate = new stdClass();
        $taxRate->isState = 0;
        $taxRate->isCountry = 0;
        $taxRate->taxRate = 0;

        if(isset($taxBasic->has_zip_tax))
        {
            if($country == 'US' && $taxBasic->has_zip_tax && $zip)
            {
                $sql = 'SELECT tax_rate
                        FROM tbl_tax_zip
                        WHERE tax_basic_row_id = ' . $taxBasic->tax_basic_row_id . '
                        AND zip_code = ' . $this->db->escape($zip);

                $res = $this->db->query($sql);
                $row = $res->row();

                if($row)
                {
                    $taxRate->isState = 1;
                    $taxRate->taxRate = $row->tax_rate;
                    return $taxRate;
                }
            }
        }

        if(isset($taxBasic->has_state_tax))
        {
            if(($country == 'US' || $country == 'CA')  && $taxBasic->has_state_tax && $state)
            {
                $sql = 'SELECT tax_rate
                        FROM tbl_tax_state
                        WHERE tax_basic_row_id = ' . $taxBasic->tax_basic_row_id . '
                        AND state_name = ' . $this->db->escape($state) . '
                        AND country_short_name = ' . $this->db->escape($country);
                $res = $this->db->query($sql);
                $row = $res->row();

                if($row)
                {
                    $taxRate->isState = 1;
                    $taxRate->taxRate = $row->tax_rate;
                    return $taxRate;
                }
            }
        }

        if(isset($taxBasic->has_country_tax))
        {
            if($taxBasic->has_country_tax && $country)
            {
                $sql = 'SELECT tax_rate
                        FROM tbl_tax_country
                        WHERE tax_basic_row_id = ' . $taxBasic->tax_basic_row_id . '
                        AND country_name = ' . $this->db->escape($country);

                $res = $this->db->query($sql);
                $row = $res->row();

                if($row)
                {
                    $taxRate->isCountry = 1;
                    $taxRate->taxRate = $row->tax_rate;
                }
            }
        }
        return $taxRate;
    }

	function saveStateTax($data)
	{
		$this->db->insert('tbl_tax_state', $data);
	}

	function saveCountryTax($data)
	{
		$this->db->insert('tbl_tax_country', $data);
	}

    function saveZipTax($data)
    {
        $this->db->insert('tbl_tax_zip', $data);
    }

    function saveZipTaxCSV($tax_basic_row_id, $lines)
    {
        $success = false;

        if(is_array($lines) && count($lines))
        {
            $cnt = 0; //for creating a batch

            $sql = 'INSERT INTO tbl_tax_zip (tax_basic_row_id, zip_code, tax_rate)
                    VALUES ';
            $data = array();

            foreach($lines as $line)
            {
                $parts = explode(',', $line);

                if(count($parts)==2)
                {
                    $value = floatval(trim($parts[1]));
                    if((!is_null($parts[0]) && preg_match("/^[a-zA-Z0-9]{1,}$/", $parts[0])) && (!is_null($parts[1]) && $value))
                    {
                        $data[] = '(' . $tax_basic_row_id . ', ' . $this->db->escape($parts[0]) . ', ' . $value . ')';
                        $cnt++;

                        if($cnt == 50)
                        {
                            $rSql = $sql . implode(',', $data);
                            $data = array();
                            $this->db->query($rSql);
                            $cnt = 0;
                            $success = true;
                        }
                    }
                    else
                    {
                        $success = false;
                    }
                }
            }

            if($cnt > 0)
            {
                $rSql = $sql . implode(',', $data);
                $this->db->query($rSql);
                $success = true;
            }
            return $success;
        }
        return $success;
    }

	function deleteState ($state_tax_row_id)
	{
		$this->db->where('state_tax_row_id', $state_tax_row_id);
		$this->db->delete('tbl_tax_state');
	}

	function deleteCountry ($country_tax_row_id)
	{
		$this->db->where('country_tax_row_id', $country_tax_row_id);
		$this->db->delete('tbl_tax_country');
	}

    function deleteZip($zip_tax_row_id)
    {
        $this->db->where('zip_tax_row_id', $zip_tax_row_id);
        $this->db->delete('tbl_tax_zip');
    }

    function deleteAllZip($tax_basic_row_id, $merchant_row_id){
        $this->db->where('tax_basic_row_id', $tax_basic_row_id);
        $this->db->where('merchant_row_id', $merchant_row_id);
        $query = $this->db->get('tbl_tax_basic');
        $row = $query->row();

        if($row){
            $sql = 'DELETE FROM tbl_tax_zip
                WHERE tax_basic_row_id = ' . $tax_basic_row_id;

            $this->db->query($sql);
        }
    }

	function getTaxInformation ($merchant_row_id, $country, $state = '')
	{
		$basic_info = $this->getBasicTaxInfo($merchant_row_id);
		if (count($basic_info) != 0)
		{
			$tax_basic_row_id = $basic_info->tax_basic_row_id;
			$country_tax_flag = $basic_info->country_tax_flag;
			$state_tax_flag = $basic_info->state_tax_flag;
			if ($country_tax_flag != 0 && $state_tax_flag != 0)
			{
				$hasStateArray = $this->config->item('country_has_state');
				if (in_array($country, $hasStateArray))
				{
					$this->db->select('*');
					$this->db->from('tbl_state_tax');
					$this->db->where('tax_basic_row_id', $tax_basic_row_id);
					$res1 = $this->db->get();
					$result1 = $res1->result();
					$st = 0;
					for ($i = 0; $i < count($result1); $i ++)
					{
						$states = explode(',', $result1[$i]->state_name);
						for ($k = 0; $k < count($states); $k ++)
						{
							$stateArray[$st] = $states[$k];
							$rateArray1[$st] = $result1[$i]->tax_rate;
							$st ++;
						}
					}
					for ($j = 0; $j < count($stateArray); $j ++)
					{
						if ($state == $stateArray[$j])
						{
							$taxRate = $rateArray1[$j];
							break;
						}
					}
				} else
				{
					$this->db->select('*');
					$this->db->from('tbl_country_tax');
					$this->db->where('tax_basic_row_id', $tax_basic_row_id);
					$res2 = $this->db->get();
					$result2 = $res2->result();
					$cn = 0;
					for ($l = 0; $l < count($result2); $l ++)
					{
						$countries = explode(',', $result2[$l]->country_name);
						for ($m = 0; $m < count($countries); $m ++)
						{
							$countryArray[$cn] = $countries[$m];
							$rateArray2[$cn] = $result2[$l]->tax_rate;
							$cn ++;
						}
					}
					for ($n = 0; $n < count($countryArray); $n ++)
					{
						if ($country == $countryArray[$n])
						{
							$taxRate = $rateArray2[$n];
							break;
						}
					}
				}
			}
		}
		if ($taxRate != null)
			return $taxRate; else
			return 0;
	}

	/**
	 * @author G J Jidni
	 * return state tax rate by merchant row id and stae
	 *
	 * @param int $merchant_row_id
	 * @param string $state
	 * @return float
	 */
	function getStateTaxRate ($merchant_row_id, $state)
	{
		$this->db->select('st.tax_rate');
		$this->db->from('tbl_state_tax st');
		$this->db->from('tbl_tax_basic tb');
		$this->db->like('st.state_name',$state);
		$this->db->where('st.tax_basic_row_id = tb.tax_basic_row_id');
		$this->db->where('tb.merchant_row_id', $merchant_row_id);
		$resultSet = $this->db->get();
		return $resultSet->row();
	}

	/**
	 * @author G J Jidni
	 * return country tax rate by merchant row id and country
	 *
	 * @param int $merchant_row_id
	 * @param string $country
	 * @return float
	 */
	function getCountryTaxRate ($merchant_row_id, $country)
	{
		$this->db->select('ct.tax_rate');
		$this->db->from('tbl_country_tax ct');
		$this->db->from('tbl_tax_basic tb');
		$this->db->like('ct.country_name',$country);
		$this->db->where('ct.tax_basic_row_id = tb.tax_basic_row_id');
		$this->db->where('tb.merchant_row_id', $merchant_row_id);
		$resultSet = $this->db->get();
		return $resultSet->row();
	}

    function getRecurringSalesTax($merchant_row_id, $productDetails, $country, $state, $zip)
    {
        $taxRate = $this->getTaxRate($merchant_row_id, $country, $state, $zip);

        if ($productDetails['is_tax_applicable'])
        {
            if($taxRate->isState)
            {
                if($productDetails['stateTaxRate'])
                {
                    return ($productDetails['recurring_price'] * $productDetails['stateTaxRate']) / 100;
                }
                else if($taxRate->taxRate)
                {
                    return ($productDetails['recurring_price'] * $taxRate->taxRate) / 100;
                }
            }
            else if($taxRate->isCountry)
            {
                if($productDetails['countryTaxRate'])
                {
                    return ($productDetails['recurring_price'] * $productDetails['countryTaxRate']) / 100;
                }
                else if($taxRate->taxRate)
                {
                    return ($productDetails['recurring_price'] * $taxRate->taxRate) / 100;
                }
            }
            else
            {
                if($productDetails['stateTaxRate'])
                {
                    return ($productDetails['recurring_price'] * $productDetails['stateTaxRate']) / 100;
                }
                else if($productDetails['countryTaxRate'])
                {
                    return ($productDetails['recurring_price'] * $productDetails['countryTaxRate']) / 100;
                }
            }
        }
        return 0;
    }
    
    //@Anjuman | D 03222012
    //check duplicate zipcode
    function checkZipcodeToBeAdded($tax_basic_row_id,$zip_code)
    {
        $this->db->select('zip_code');
        $this->db->from('tbl_tax_zip');
        $this->db->where('tax_basic_row_id', $tax_basic_row_id);
        $this->db->where('zip_code', $zip_code);
        $res = $this->db->get();
        return $res->row_array(); 
    }
    //End of : Anjuman
}
?>