<?php
/*
* @Author: Ali Tareque Chowdhury
* @Created On: 17 SEP 2009
*/
class Shipping_model extends CI_Model
{
	public function __construct()
	{
		parent::__construct();
	}

	//Get the list and status of all the Custom Shipping Methods
	function getAllShippingMethod()
    {
        $sql = 'SELECT *
                FROM tbl_shipping
				WHERE category="custom"
                ORDER BY shipping_id ASC';
        $resultSet = $this->db->query($sql);

        return $resultSet->result_array();
    }

	//Get the list and status of all the Carrier Based Shipping Methods
	function getAllCarrierShippingMethod()
    {
        $sql = 'SELECT *
                FROM tbl_shipping
				WHERE category="carrier"
                ORDER BY shipping_id ASC';
        $resultSet = $this->db->query($sql);

        return $resultSet->result_array();
    }
	//Get the SHIPPING_ID of the Shipping Method that is being in use
	function getActiveShippingMethod($merchant_row_id)
    {
        $sql = 'SELECT shipping_id
                FROM tbl_shipping_merchant
                WHERE merchant_row_id='.$merchant_row_id.'
                AND status=1';
        $resultSet = $this->db->query($sql);
		$exist=$resultSet->result_array();

        if(empty($exist))
        {
			$sql = 'SELECT shipping_id
                    FROM tbl_shipping_carrier_merchant
					WHERE merchant_row_id='.$merchant_row_id.'
                    AND status=1';
			$resultSet = $this->db->query($sql);
			$exist=$resultSet->result_array();
		}

        return $exist;
    }

    function setShippingInfoMerchant($merchantRowId)
    {
        $escapedMerchantRowId = $this->db->escape($merchantRowId);
        $sql = 'INSERT INTO tbl_shipping_info_merchant(merchant_row_id,
                                                        country,
                                                        state,
                                                        shipping_address,
                                                        company,
                                                        zip,
                                                        secondary_country)
                 SELECT ' . $merchantRowId . ',
                        billing_country,
                        billing_state,
                        billing_address,
                        billing_company,
                        IF(billing_zip_code != \'\', billing_zip_code, billing_postal_code),
                        \'\'
                FROM tbl_merchant_billing
                WHERE merchant_row_id='.$escapedMerchantRowId;

        if(!$this->db->query($sql))
        {
            log_general('shipping_model:setShippingInfoMerchant: tbl_shipping_info_merchant insert failed (primary 1)');
        }

        /* SETTING UP MERCHANT SPECIFIC SHIPPING METHOD-----START*/
        $sql= 'INSERT INTO tbl_shipping_merchant(merchant_row_id, shipping_id, status, rate)
                VALUES(' . $escapedMerchantRowId . ', 1, 0, 0),
                (' . $escapedMerchantRowId . ', 2, 1, 0),
                (' . $escapedMerchantRowId . ', 4, 0, 0),
                (' . $escapedMerchantRowId . ', 9, 0, 0)';

        if(!$this->db->query($sql))
        {
            log_general('shipping_model:setShippingInfoMerchant: tbl_shipping_merchant insert failed');
        }
        /* SETTING UP MERCHANT SPECIFIC SHIPPING METHOD-----END*/

        /* SETTING UP MERCHANT SPECIFIC CARRIER BASED SHIPPING METHOD-----START*/
        $sql='INSERT INTO tbl_shipping_carrier_merchant(merchant_row_id, shipping_id, status)
                VALUES(' . $escapedMerchantRowId . ', 3, 0),
                (' . $escapedMerchantRowId . ', 5, 0),
                (' . $escapedMerchantRowId . ', 6, 0),
                (' . $escapedMerchantRowId . ', 7, 0),
                (' . $escapedMerchantRowId . ', 8, 0)';

        if(!$this->db->query($sql))
        {
            log_general('shipping_model:setShippingInfoMerchant: tbl_shipping_carrier_merchant insert failed');
        }
         /* SETTING UP MERCHANT SPECIFIC CARRIER BASED SHIPPING METHOD-----END*/

        return true;
    }

	//Get the information of the SELECTED Shipping Method
	function getShippingConfigurationInfo($merchant_row_id, $shipping_id)
    {
		switch ($shipping_id)
        {
			case 1:
            case 2:
            case 4:
            case 9:
				$sql = 'SELECT *
                        FROM tbl_shipping_merchant
						WHERE merchant_row_id=' . $this->db->escape($merchant_row_id) . '
                        AND shipping_id=' . $this->db->escape($shipping_id) . '
                        LIMIT 1';

				$resultSet = $this->db->query($sql);
				return $resultSet->row();
			case 3:
            case 5:
            case 6:
            case 7:
            case 8:
            case 10:
				$sql = 'SELECT *
                        FROM tbl_shipping_carrier_merchant
						WHERE merchant_row_id=' . $this->db->escape($merchant_row_id) . '
                        AND shipping_id=' . $this->db->escape($shipping_id) . '
                        LIMIT 1';
				$resultSet = $this->db->query($sql);
				return $resultSet->row();
        }
    }

	//Save the Configuration Information of the SELECTED Shipping Method
	function saveShippingConfigurationInfo($merchant_row_id,$data)
    {
		switch ($data['shipping_id'])
        {
			case 1:
            case 9:
				$sql = 'UPDATE tbl_shipping_merchant
						SET rate='.$this->db->escape($data['rate']).',
                            international_rate='.$this->db->escape($data['international_rate']).',
							status='.$this->db->escape($data['status']).'
						WHERE merchant_row_id='.$this->db->escape($merchant_row_id).' AND shipping_id='.$this->db->escape($data['shipping_id']);

				$this->db->query($sql);
				break;
			case 2:
				$sql = 'UPDATE tbl_shipping_merchant
						SET  status='.$this->db->escape($data['status']).'
						WHERE merchant_row_id='.$this->db->escape($merchant_row_id).' AND shipping_id='.$this->db->escape($data['shipping_id']);

				$this->db->query($sql);
				break;
            case 4:
                $sql = "INSERT INTO tbl_shipping_price_based(lower_limit, upper_limit, charge_type, charge_amount,charge_amount_international,merchant_row_id, shipping_id)
                        VALUES(".$this->db->escape($data['lower_limit']).",
                                ".$this->db->escape($data['upper_limit']).",
                                ".$this->db->escape($data['charge_type']).",
                                ".$this->db->escape($data['charge_amount']).",
                                ".$this->db->escape($data['charge_amount_international']).",
                                ".$this->db->escape($merchant_row_id).",
                                ".$this->db->escape($data['shipping_id']).")";

                $this->db->query($sql);
                break;
            case 3:
            case 5:
            case 6:
            case 7:
            case 8:
            case 10:
                $data['account_info_1'] = empty($data['account_info_1']) ? '' : $data['account_info_1'];
                $data['account_info_2'] = empty($data['account_info_2']) ? '' : $data['account_info_2'];
                $data['account_info_3'] = empty($data['account_info_3']) ? '' : $data['account_info_3'];
                $data['account_info_4'] = empty($data['account_info_4']) ? '' : $data['account_info_4'];
                $data['account_info_5'] = empty($data['account_info_5']) ? '' : $data['account_info_5'];

                $data['custom_options'] = empty($data['custom_options']) ? '' : $data['custom_options'];

				$sql = 'SELECT * FROM tbl_shipping_carrier_merchant
                        WHERE merchant_row_id = ' . $this->db->escape($merchant_row_id) . '
                        AND shipping_id = ' . $this->db->escape($data['shipping_id']);
                $query = $this->db->query($sql);
                if ($query->num_rows() > 0)
                {
	                $sql = 'UPDATE tbl_shipping_carrier_merchant
	                        SET status = ' . $this->db->escape($data['status']) . ',
	                        use_default = ' . $data['use_default'] . ',
	                        account_info_1 = ' . $this->db->escape($data['account_info_1']) . ',
	                        account_info_2 = ' . $this->db->escape($data['account_info_2']) . ',
	                        account_info_3 = ' . $this->db->escape($data['account_info_3']) . ',
	                        account_info_4 = ' . $this->db->escape($data['account_info_4']) . ',
	                        account_info_5 = ' . $this->db->escape($data['account_info_5']) . ',
	                        selected_services = ' . $this->db->escape($data['selected_services']) . ',
                            optional_text = ' . $this->db->escape($data['optional_text']) . ',
							custom_options = ' . $this->db->escape($data['custom_options']) . ',
                            additional_shipping_amount = ' . $this->db->escape($data['additional_shipping_amount']) . ',
                            additional_shipping_type = ' . $this->db->escape($data['additional_shipping_type']) . '
	                        WHERE merchant_row_id = ' . $this->db->escape($merchant_row_id) . '
	                        AND shipping_id = ' . $this->db->escape($data['shipping_id']) . ' LIMIT 1';
	                $this->db->query($sql);
                }
                else 
                {
                	$sql_reg_endicia = "INSERT INTO tbl_shipping_carrier_merchant (merchant_row_id,shipping_id, selected_services, status, use_default, account_info_1, account_info_2, account_info_3,custom_options, additional_shipping_amount, additional_shipping_type) VALUES (". $this->db->escape($merchant_row_id).",". $this->db->escape($data['shipping_id']).",".$this->db->escape($data['selected_services']).",".$this->db->escape($data['status']).",".$data['use_default'].",".$this->db->escape($data['account_info_1']).",".$this->db->escape($data['account_info_2']).",".$this->db->escape($data['account_info_3']).",".$this->db->escape($data['custom_options']) . "," . $this->db->escape($data['additional_shipping_amount']) . "," . $this->db->escape($data['additional_shipping_type']) . ")";
                	$query = $this->db->query($sql_reg_endicia);
                	$this->db->query($sql);
                }

                break;
		}

        $shipping_method_active = $this->getActiveShippingMethod($merchant_row_id);
        $data['total_active_methods']= count($shipping_method_active);

		//The LOGIC below SETS all the other Shipping Method Status to 0 except the one that is ENABLED NOW
		if(($data['status']==1) && ($data['shipping_id']!=4) && (($data['shipping_id']==1)|| $data['shipping_id']==2 || $data['shipping_id']==9))//If 'ENABLED' then set all other methods' status to 0
        {
			$sql = "UPDATE tbl_shipping_merchant
			        SET status='0'
                    WHERE merchant_row_id=".$merchant_row_id."
                    AND status='1'
                    AND shipping_id !=".$this->db->escape($data['shipping_id']);
			$this->db->query($sql);

			$sql = "UPDATE tbl_shipping_carrier_merchant
			        SET status='0'
                    WHERE merchant_row_id=".$merchant_row_id."
                    AND status='1'
                    AND shipping_id !=".$this->db->escape($data['shipping_id']);
			$this->db->query($sql);
		}
        if(($data['status']==1) && ($data['shipping_id']!=4) && ($data['shipping_id']!=1 || $data['shipping_id']!=2 || $data['shipping_id'] != 9))//If 'ENABLED' then set all other methods' status to 0
        {
            $sql = "UPDATE tbl_shipping_merchant
                    SET status='0'
                    WHERE merchant_row_id=".$merchant_row_id."
                    AND status='1'
                    AND shipping_id !=".$this->db->escape($data['shipping_id']);
            $this->db->query($sql);
        }
        else if(($data['status']==0)&& ($data['shipping_id']!=4) && ($data['total_active_methods'] < 1)){//if 'Disabled' then set PER ITEM as the default Shipping Method
            $sql = "UPDATE tbl_shipping_merchant
                    SET status='1'
                    WHERE merchant_row_id=".$this->db->escape($merchant_row_id)." AND shipping_id='2'";
            $this->db->query($sql);
        }
         
    }

	//Save the Shipping FROM Information For the Merchant
    function saveShippingFromInfo($merchant_row_id,$data)
    {
        if(!empty($merchant_row_id)){
            $this->db->select('merchant_row_id');
            $this->db->where('merchant_row_id', $merchant_row_id);
            $query = $this->db->get('tbl_shipping_info_merchant');
            $row = $query->row();

            if(!empty($row) && $row->merchant_row_id){
                $this->db->where('merchant_row_id', $merchant_row_id);
                $this->db->update('tbl_shipping_info_merchant', $data);
            }
            else{
                $this->db->insert('tbl_shipping_info_merchant', $data);
            }
        }
    }

	//Get the Shipping Method Detail Information
	function getActiveShippingMethodDetails($merchant_row_id,$shipping_id)
    {
        $custom_services_id= array('1','2','4', '9');
        $carrier_services_id= array('3','5','6','7','8');
        $result = false;
        if(array_intersect($custom_services_id, $shipping_id))
        {
            $sql = "SELECT *
                        FROM tbl_shipping_merchant sm
                        INNER JOIN tbl_shipping s ON s.shipping_id=sm.shipping_id
                        WHERE sm.shipping_id=".$this->db->escape($shipping_id[0])." AND sm.merchant_row_id=".$this->db->escape($merchant_row_id);
                $query=$this->db->query($sql);
                $result=$query->result_array();

        }
        else if(array_intersect($carrier_services_id, $shipping_id))
        {
            $array_string=implode(',',$shipping_id);
            $sql = "SELECT *
                        FROM tbl_shipping_carrier_merchant scm
                        INNER JOIN tbl_shipping s ON s.shipping_id=scm.shipping_id
                        WHERE scm.shipping_id IN(".$array_string.") AND scm.merchant_row_id=".$this->db->escape($merchant_row_id);
            $query=$this->db->query($sql);
            $result=$query->result_array();
        }

        return $result;
	}

	function saveShippingInfo($order_data, $order_details_id)
    {   $bundle_name='';
        if($order_data['bundle_id'] > 0)
        {
            $sql="SELECT offer_name FROM tbl_product_bundle WHERE product_bundle_row_id=".$this->db->escape($order_data['bundle_id']);
            $query=$this->db->query($sql);
            $result=$query->row();
            $bundle_name=$result->offer_name;
        }

        $user_type=0;
        $updated_by=0;
        $newNote='';
        if($order_data['special_comments']!='' || $order_data['special_comments']!=0)
        {
            $user_type=3;
            $updated_by=$order_data['customer_id'];
            $data['last_modify_time']=getCurrentDateTimeForDB();
            $updateName=$this->getUpdatedByName($user_type, $updated_by);

            $newNote =  '<div class="note">
                        <b>'.$updateName->update_name.'</b><br />
                        <b>Posted On: </b>'.$data['last_modify_time'].'<br />
                        '.$order_data['special_comments'].'</div>';
        }
        
        if($order_data['method_name']=='FedEx')
        {
            $shipping_destination  = $order_data['shipping_destination'];       
        }
        else
        {
            $shipping_destination  = 0;
        }
        isset($order_data['free_text_attribute']) OR $order_data['free_text_attribute'] = '';
        $sql="INSERT INTO tbl_order_shipping_info(order_row_id,
                        merchant_row_id,
                        product_row_id,
                        customer_id,
                        order_details_id,
                        shipping_id,
                        method_name,
                        shipping_amount,
                        shipping_service,
                        user_type,
                        updated_by,
                        notes,
                        add_to_shipping_module,
                        assigned_company_id,
                        product_attribute,
                        free_text_attribute,
                        bundle_id,
                        bundle_name,
                        status,
                        recurring_cycle,
                        shipping_destination
                        )
                    VALUES(".$this->db->escape($order_data['order_row_id']).",
                            ".$this->db->escape($order_data['merchant_row_id']).",
                            ".$this->db->escape($order_data['product_row_id']).",
                            ".$this->db->escape($order_data['customer_id']).",
                            ".$this->db->escape($order_details_id).",
                            ".$this->db->escape($order_data['shipping_id']).",
                            ".$this->db->escape($order_data['method_name']).",
                            ".$this->db->escape($order_data['total_shipping_amount']).",
                            ".$this->db->escape($order_data['shipping_service']).",
                            ".$this->db->escape($user_type).",
                            ".$this->db->escape($updated_by).",
                            ".$this->db->escape($newNote).",
                            ".$this->db->escape($order_data['add_to_shipping_module']).",
                            ".$this->db->escape($order_data['assigned_company_id']).",
                            ".$this->db->escape($order_data['product_attribute']).",
                            ".$this->db->escape($order_data['free_text_attribute']).",
                            ".$this->db->escape($order_data['bundle_id']).",
                            ".$this->db->escape($bundle_name).",
                            ".'1'.",
                            ".'1'.",
                            '".$shipping_destination."')";
        $this->db->query($sql);
        return true;
	}

    function changeShippingStatusByOrder($orderRowId, $status)
    {
        $this->db->where('order_row_id', $orderRowId);
        $this->db->update('tbl_order_shipping_info', array('status' => $status));
    }

	//Get the information of the Merchant's Shipping Origin
	function getShippingOriginDetails($merchant_row_id)
    {
		$sql = 'SELECT *
                FROM tbl_shipping_info_merchant
			    WHERE merchant_row_id= ' . $this->db->escape($merchant_row_id);
		$query=$this->db->query($sql);
		$result=$query->row();
		return $result;
	}

	//Get the Details of the Shipping Method for a particular Order
	function getShippingDetailsOrderwise($order_row_id, $merchant_row_id)
    {
		$sql = "SELECT *
                FROM tbl_order_shipping_info
			    WHERE merchant_row_id=".$this->db->escape($merchant_row_id)." AND order_row_id=".$this->db->escape($order_row_id);
		$query=$this->db->query($sql);
		$result=$query->row();
		return $result;
	}
    //Get Existing Price Based List for the particular Merchant
    function getExistingPriceList ($merchant_row_id)
    {
        $sql = "(SELECT ship_priceb_id, CONCAT('".getCurrencySymbol()."','',lower_limit) as lower_limit, ";
        $sql .= "(CASE upper_limit WHEN  0 THEN 'Unlimited' ELSE CONCAT('".getCurrencySymbol()."','',upper_limit) END) as upper_limit, ";
        $sql .= "(CASE WHEN charge_type = 1 THEN CONCAT(charge_amount,'','%')";
        $sql .= " WHEN charge_type = 2 THEN CONCAT('".getCurrencySymbol()."','',charge_amount) END) as rate,";
        $sql .= "(CASE WHEN charge_type = 1 THEN CONCAT(charge_amount_international,'','%')";
        $sql .= " WHEN charge_type = 2 THEN CONCAT('".getCurrencySymbol()."','',charge_amount_international) END) as rate_international,";
        $sql .= " 'Delete' as delete_charge,";
        $sql .= " 'Edit' as edit_charge";
        $sql .= " FROM tbl_shipping_price_based";
        $sql .= " WHERE merchant_row_id = $merchant_row_id) as XYZ";

        $this->load->library ( 'rapyd' );
        $this->rapyd->load('datagrid');
        $grid = new DataGrid('', $sql);
        $grid->order_by('ship_priceb_id', 'asc');
        $grid->per_page = 20;
        $grid->column_orderby('Lower Limit', '<#lower_limit#>', 'lower_limit', 'align=\'left\'');
        $grid->column_orderby('Upper Limit', '<#upper_limit#>', 'upper_limit', 'align=\'left\'');
        $grid->column_orderby('Domestic Shipping Rate', '<#rate#>', 'rate', 'align=\'left\'');
        $grid->column_orderby('International Shipping Rate', '<#rate_international#>', 'rate_international', 'align=\'left\'');
        $grid->column_detail('Action', 'edit_charge', 'products/setup_shipping/editShippingPriceBased/<#ship_priceb_id#>', 'align=\'left\'', false, 'class="submodal-500-300"', true, 'colspan="2"');
        $grid->column_detail('Delete', 'delete_charge', 'products/setup_shipping/deletePriceBasedInfo/<#ship_priceb_id#>', 'align=\'left\'', false, 'onclick = "return checkDelete();"', false);
        $grid->build();
        return $grid->output;
    }
    //Get the total number of Proce Break up ranges Setup by the Merchant
    function countData ($merchant_row_id)
    {
        $sql="SELECT COUNT(*) AS cnt FROM tbl_shipping_price_based
              WHERE merchant_row_id=".$this->db->escape($merchant_row_id);
        $res = $this->db->query($sql);
        $result = $res->row();
        $count = $result->cnt;
        return $count;
    }
    //Set the Next lower Limit for the Price Based Setup
    function setNextLowerLimt ($merchant_row_id)
    {
        $sql="SELECT MAX(ship_priceb_id) AS maxDis
                FROM tbl_shipping_price_based
                WHERE merchant_row_id=".$this->db->escape($merchant_row_id);

        $recordSet = $this->db->query($sql);
        $result = $recordSet->row();
        $maxId = $result->maxDis;

        if ($maxId != null)
        {

            $sql="SELECT *
                FROM tbl_shipping_price_based
                WHERE ship_priceb_id=".$this->db->escape($maxId);

            $res2 = $this->db->query($sql);
            $result2 = $res2->row_array();
            $maxUpperValue = $result2['upper_limit'];
        } else
        {
            $maxUpperValue = 0.00;
        }

        return $maxUpperValue;
    }
    //Saves the Status for the Price Based shipping Method

    function saveShippingStatusInfo($merchant_row_id, $data)
    {
         $sql = 'UPDATE tbl_shipping_merchant
                            SET status='.$this->db->escape($data['status']).'
                            WHERE merchant_row_id='.$this->db->escape($merchant_row_id).' AND shipping_id='.$this->db->escape($data['shipping_id']);
                    $this->db->query($sql);
                    $status=$this->db->affected_rows();


        //The LOGIC below SETS all the other Shipping Method Status to 0 except the one that is ENABLED NOW
        if($data['status']==1)//If 'ENABLED' then set all other methods' status to 0
        {
            $sql = "UPDATE tbl_shipping_merchant
                    SET status='0'
                    WHERE merchant_row_id=".$merchant_row_id."
                    AND status='1'
                    AND shipping_id !=".$this->db->escape($data['shipping_id']);
            $this->db->query($sql);

            $sql = "UPDATE tbl_shipping_carrier_merchant
                    SET status='0'
                    WHERE merchant_row_id=".$merchant_row_id."
                    AND status='1'
                    AND shipping_id !=".$this->db->escape($data['shipping_id']);
            $this->db->query($sql);
        }
        else if($data['status']==0){//if 'Disabled' then set PER ITEM as the default Shipping Method
            $sql = "UPDATE tbl_shipping_merchant
                            SET status='1'
                            WHERE merchant_row_id=".$this->db->escape($merchant_row_id)." AND shipping_id='2'";
                    $this->db->query($sql);
        }


        return $status;
    }
//Function below checks whether to show the Entry form for lower and upper limit in Priced Based mehtod
    function checkShowing($merchant_row_id)
    {
       $sql="SELECT max(ship_priceb_id) as maxDis
              FROM tbl_shipping_price_based
              WHERE merchant_row_id=".$this->db->escape($merchant_row_id);
        $res1 = $this->db->query($sql);
        $result1 = $res1->row();
        $maxDis = $result1->maxDis;
        if($maxDis > 0)
        {
            $sql="SELECT *
                  FROM tbl_shipping_price_based
                  WHERE merchant_row_id=".$this->db->escape($merchant_row_id).' AND ship_priceb_id='.$this->db->escape($maxDis);
            $res2 = $this->db->query($sql);
            $result2 = $res2->row();
            $max_upper = $result2->upper_limit;
            return $max_upper;
        }
        else
        {
            return 1;
        }
    }
//Function below fethes all the information of a particular Price break in Price based Method
    function getDetailsByShipPriceRowId ($ship_priceb_id)
    {
        $sql="SELECT *
              FROM tbl_shipping_price_based
              WHERE ship_priceb_id=".$this->db->escape($ship_priceb_id);
        $res = $this->db->query($sql);
        $result = $res->row();
        return $result;
    }
//Function below UPDATES the informtion for the Price Based price break
    function savePriceBasedEditInfo ($data)
    {
        $sql="UPDATE tbl_shipping_price_based
              SET lower_limit=".$this->db->escape($data['lower_limit']).",
                  upper_limit=".$this->db->escape($data['upper_limit']).",
                  charge_type=".$this->db->escape($data['charge_type']).",
                  charge_amount_international=".$this->db->escape($data['charge_amount_international']).",
                  charge_amount=".$this->db->escape($data['charge_amount'])."
              WHERE merchant_row_id=".$this->db->escape($data['merchant_row_id'])." AND ship_priceb_id=".$this->db->escape($data['ship_priceb_id']);
       $this->db->query($sql);
       $status= $this->db->affected_rows();
       return $status;
    }
//Function below Deletes a selected price break scheme from Price Based Shipping Method
    function deletePriceBasedInfo($data)
    {
        $sql="DELETE
              FROM tbl_shipping_price_based
              WHERE merchant_row_id=".$this->db->escape($data['merchant_row_id'])." AND ship_priceb_id >=".$this->db->escape($data['ship_priceb_id']);
        $this->db->query($sql);
        $status=$this->db->affected_rows();
        return $status;
    }
//Function below gets the Price breaks details for a particular merchant
    function getShippingPriceBreaksDetails($merchant_row_id,$shipping_id)
    {
        //$shipping_id_string=implode($shipping_id);
        $sql="SELECT *
              FROM tbl_shipping_price_based
              WHERE merchant_row_id=".$this->db->escape($merchant_row_id)." AND shipping_id =".$this->db->escape($shipping_id)." ORDER BY ship_priceb_id ASC";

        $result=$this->db->query($sql);
        $result_array=$result->result_array();

        return $result_array;
    }

//Function below gets the Price breaks details for a particular merchant
    function getInternationalShippingPriceBreaksDetails($merchant_row_id,$shipping_id)
    {
        $sql="SELECT *
              FROM tbl_shipping_price_based
              WHERE merchant_row_id=".$this->db->escape($merchant_row_id)." AND shipping_id=".$this->db->escape($shipping_id)." AND zone='1' ORDER BY ship_priceb_id ASC";

        $result=$this->db->query($sql);
        $result_array=$result->result_array();

        return $result_array;
    }

    function default_shipping_list($merchant_row_id ,$condition, $data, $config){

        $date_format = 'DATE_FORMAT(ord.created_on,\'' . $_SESSION['date_format'] . '\') AS created_on';
        $sql="SELECT    osi.ord_ship_id AS ord_ship_id,
                        osi.merchant_row_id AS merchant_row_id,
                        osi.product_row_id AS product_row_id,
                        osi.order_row_id AS order_row_id,
                        osi.customer_id AS customer_id,
                        osi.shipping_id AS shipping_id,
                        osi.method_name AS method_name,
                        osi.shipping_service AS shipping_service,
						osi.new_method_name AS new_method_name,
                        osi.new_shipping_service AS new_shipping_service,
						osi.label_file_name AS label_file_name,
                        osi.recurring_cycle AS recurring_cycle,
                        osi.track_id AS track_id,
                        osi.status AS processing_status,
                        osi.last_status AS last_status,
                        osi.notes AS notes,
                        osi.shipper AS shipper,
                        osi.bundle_name AS bundle_name,
                        osi.assigned_company_id AS assigned_company_id,
                        ordtls.product_description AS product_description,
                        ordtls.bundle_id AS bundle_id,
                        ordtls.product_quantity AS product_quantity,
                        ordtls.product_price AS product_price,
                        ord.product_total AS product_total,
                        ord.total_amount AS total_amount,
                        ord.order_discount AS order_discount,
                        ord.country_tax AS country_tax,
                        ord.shpping_amount AS shpping_amount,
                        ord.created_on AS created_on,
                        ord.order_id AS order_id,
                        ord.shipping_address_line1,
                        ord.shipping_address_line2,
                        ord.shipping_customer_city,
                        CASE TRIM(ord.shipping_customer_state)
                        WHEN '' THEN ord.customer_state
                        ELSE ord.shipping_customer_state
                        END AS shipping_customer_state,
                        ord.shipping_customer_zip,
                        ord.shipping_name,
                        ord.shipping_company_name,
                        ord.home_phone_number,
                        ord.customer_email,
                        ord.shipping_country,
                        $date_format,
                        CONCAT(ord.first_name,' ',ord.last_name) as cust_name
                FROM tbl_order_shipping_info osi
                INNER JOIN tbl_order ord ON ord.order_row_id=osi.order_row_id
                INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=osi.order_details_id
                WHERE osi.merchant_row_id=".$this->db->escape($merchant_row_id)."
                AND osi.order_row_id IN (".$data['order_row_ids'].") AND osi.add_to_shipping_module='1'".$condition." ORDER BY ord.created_on DESC";


        $result=$this->db->query($sql);

        $result_array= $result->result_array();
        //print_r($sql);
        return $result_array;

    }

    function user_shipping_list($merchant_row_id ,$condition, $data, $config){

        $date_format = 'DATE_FORMAT(ord.created_on,\'' . $_SESSION['date_format'] . '\') AS created_on';
        $sql="SELECT    osi.ord_ship_id AS ord_ship_id,
                        osi.merchant_row_id AS merchant_row_id,
                        osi.product_row_id AS product_row_id,
                        osi.order_row_id AS order_row_id,
                        osi.customer_id AS customer_id,
                        osi.shipping_id AS shipping_id,
                        osi.method_name AS method_name,
                        osi.shipping_service AS shipping_service,
                        osi.new_method_name AS new_method_name,
                        osi.new_shipping_service AS new_shipping_service,
                        osi.recurring_cycle AS recurring_cycle,
                        osi.track_id AS track_id,
                        osi.status AS processing_status,
                        osi.last_status AS last_status,
                        osi.notes AS notes,
                        osi.shipper AS shipper,
						osi.label_file_name AS label_file_name,
                        ordtls.product_description AS product_description,
                        osi.bundle_name AS bundle_name,
                        ordtls.product_quantity AS product_quantity,
                        ordtls.product_price AS product_price,
                        ord.product_total AS product_total,
                        ord.total_amount AS total_amount,
                        ord.order_discount AS order_discount,
                        ord.country_tax AS country_tax,
                        ord.shpping_amount AS shpping_amount,
                        ord.created_on AS created_on,
                        ord.order_id AS order_id,
                        ord.shipping_address_line1,
                        ord.shipping_address_line2,
                        ord.shipping_customer_city,
                        CASE TRIM(ord.shipping_customer_state)
                        WHEN '' THEN ord.customer_state
                        ELSE ord.shipping_customer_state
                        END AS shipping_customer_state,
                        ord.shipping_customer_zip,
                        ord.shipping_name,
                        ord.shipping_company_name,
                        ord.home_phone_number,
                        ord.customer_email,
                        ord.shipping_country,
                        $date_format,
                        CONCAT(ord.first_name,' ',ord.last_name) as cust_name
                FROM tbl_order_shipping_info osi
                INNER JOIN tbl_order ord ON ord.order_row_id=osi.order_row_id
                INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=osi.order_details_id
                WHERE osi.merchant_row_id=".$this->db->escape($merchant_row_id)."
                AND osi.order_row_id IN (".$data['order_row_ids'].") AND osi.add_to_shipping_module='1' AND osi.status !='4' AND osi.assigned_company_id=".$this->db->escape($data['ship_user_data']->company_id)." ".$condition." ORDER BY ord.created_on DESC";


        $result=$this->db->query($sql);

        $result_array= $result->result_array();
        //print_r($sql);
        return $result_array;

    }

    function assign_company($ord_ship_id, $data)
    {
        $sql="UPDATE tbl_order_shipping_info
              SET assigned_company_id=".$this->db->escape($data['company_id']).",
                  assigned_company_name =".$this->db->escape($data['fulfillment_company']).",
                  task_assign_time =".$this->db->escape($data['task_assign_time']).",
                  updated_by =".$this->db->escape($data['updated_by'])."
              WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();
        /* Insert data in the tbl_fulfillment_email_notification Starts */

        if($data['company_id'] > 0)
        {
            $sql="SELECT ord.order_id as order_id,
                         osi.order_row_id as order_row_id,
                         osi.merchant_row_id as merchant_row_id,
                         osi.assigned_company_id
                  FROM tbl_order_shipping_info osi
                  INNER JOIN tbl_order ord ON ord.order_row_id=osi.order_row_id
                  WHERE  osi.ord_ship_id=".$this->db->escape($ord_ship_id)." AND osi.assigned_company_id=".$this->db->escape($data['company_id']);
            $query = $this->db->query($sql);
            $result = $query->row();

            //Sends email to Fulfillment Users Starts
            $getInstance = &get_instance();
            $getInstance->load->model('Mail_Manager_model', 'Mail_Manager_model', TRUE);
            
            //For <#OrderInvoice#> merge code
            $getInstance->load->model('carts/Cart_Setup_model', 'Cart_Setup_model', TRUE);
            $getInstance->load->model('orders/Order_model', 'Order_model', TRUE);
            $getInstance->load->model('customers/Customer_Setup_model', 'Customer_Setup_model', TRUE);
            $getInstance->load->model('Gift_Certificate_model', 'Gift_Certificate_model', true);
            $getInstance->load->model('merchant/Merchant_model', 'Merchant_model', TRUE);
            
            $merchantBasicInfo = $getInstance->Cart_Setup_model->get_basic_setup($tempOrder->merchant_row_id);
            $data['orderDetails']     = $getInstance->Order_model->getOrderByRowId($result->order_row_id);
            $data['orderProductList'] = $getInstance->Order_model->getOrderDetailsByOrderRowId($result->order_row_id);
            $data['appliedCoupons']   = $getInstance->Order_model->getAppliedCouponsOfOrder($result->order_row_id);
            $data['customer_info']    = $getInstance->Customer_Setup_model->getCustomerByOrderRowId($result->order_row_id);
            $data['appliedGiftCertificates'] = $getInstance->Gift_Certificate_model->getAppliedGiftCertificates($result->order_row_id);
            $data['merchant_info']   =  $getInstance->Merchant_model->getMerchantDetails($result->merchant_row_id);
            $data['currency_symbol'] = $merchantBasicInfo->currency_symbol;
            $data['order_row_id']    = $result->order_row_id;
            //For <#OrderInvoice#> merge code 
            $getInstance->Mail_Manager_model->sendRegularOrderEmailToFulfillmentUser($result->order_row_id, $result->merchant_row_id, $data);
        }
        else
        {
            $sql="SELECT ord.order_id as order_id,
                         osi.assigned_company_id as company_id
                  FROM tbl_order_shipping_info osi
                  INNER JOIN tbl_order ord ON ord.order_row_id=osi.order_row_id
                  WHERE  osi.ord_ship_id=".$this->db->escape($ord_ship_id)." AND osi.assigned_company_id='0'";
            $query = $this->db->query($sql);
            $result = $query->row();

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

        return $update_status;

    }
    function modify_status($ord_ship_id, $data)
    {
        //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>//
        $sql="SELECT order_row_id, bundle_id
              FROM tbl_order_shipping_info
              WHERE ord_ship_id=".$this->db->escape($ord_ship_id)." AND bundle_id!=0";
        $query=$this->db->query($sql);
        $result=$query->row();

        if($result->order_row_id > 0)
        {
            $sql="SELECT ord_ship_id, bundle_id, order_row_id
                  FROM tbl_order_shipping_info
                  WHERE order_row_id=".$this->db->escape($result->order_row_id)." AND bundle_id=".$this->db->escape($result->bundle_id)." ORDER BY ord_ship_id ASC";
            $query=$this->db->query($sql);
            $result=$query->result_array();
            $counter=0;
            $status_updated = 0;
            foreach($result AS $aResult)
            {
                if($counter==1)
                {
                    if($aResult['ord_ship_id']==$ord_ship_id)
                    {
                        $sql="UPDATE tbl_order_shipping_info
                              SET status=".$this->db->escape($data['status']).",
                                  status_update_time =".$this->db->escape($data['status_update_time']).",
                                  user_type =".$this->db->escape($data['user_type']).",
                                  updated_by =".$this->db->escape($data['updated_by']).",
                                  last_modify_time =".$this->db->escape($data['last_modify_time']).",
                                  last_status=".$this->db->escape($data['last_status'])."
                              WHERE order_row_id=".$this->db->escape($aResult['order_row_id'])." AND bundle_id=".$this->db->escape($aResult['bundle_id']);
                        $this->db->query($sql);
                        $update_status=$this->db->affected_rows();
						$status_updated = 1;
                       return $update_status."_333";
                    }
                    else
                    {
                     $sql="UPDATE tbl_order_shipping_info
                      		SET status=".$this->db->escape($data['status']).",
                          	status_update_time =".$this->db->escape($data['status_update_time']).",
                          	user_type =".$this->db->escape($data['user_type']).",
                          	updated_by =".$this->db->escape($data['updated_by']).",
                          	last_modify_time =".$this->db->escape($data['last_modify_time']).",
                          	last_status=".$this->db->escape($data['last_status'])."
                      		WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
                	$this->db->query($sql);
                	$update_status=$this->db->affected_rows();
					$status_updated = 1;
                	return $update_status."_111";
                    }
                    break;
                }
                $counter++;
            }
            /* START - Added this to solve a bug: when in a bundle there was only one product, status was not getting updated */
            /* Added on: 06 JANUARY, 2012 by ZERIN */
            if ($status_updated==0)
            {
            	$sql="UPDATE tbl_order_shipping_info
                      		SET status=".$this->db->escape($data['status']).",
                          	status_update_time =".$this->db->escape($data['status_update_time']).",
                          	user_type =".$this->db->escape($data['user_type']).",
                          	updated_by =".$this->db->escape($data['updated_by']).",
                          	last_modify_time =".$this->db->escape($data['last_modify_time']).",
                          	last_status=".$this->db->escape($data['last_status'])."
                      		WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
                $this->db->query($sql);
                $update_status=$this->db->affected_rows();
				$status_updated = 1;
                return $update_status."_111";
            }
            /* END - Added this to solve a bug: when in a bundle there was only one product, status was not getting updated */
        }

        //>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>//
        else
        {
            $sql="UPDATE tbl_order_shipping_info
                  SET status=".$this->db->escape($data['status']).",
                      status_update_time =".$this->db->escape($data['status_update_time']).",
                      user_type =".$this->db->escape($data['user_type']).",
                      updated_by =".$this->db->escape($data['updated_by']).",
                      last_modify_time =".$this->db->escape($data['last_modify_time']).",
                      last_status=".$this->db->escape($data['last_status'])."
                  WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
            $this->db->query($sql);
            $update_status=$this->db->affected_rows();
            return $update_status."_111";
        }


    }
    function modify_shipper($ord_ship_id, $data)
    {
        $sql="UPDATE tbl_order_shipping_info
              SET shipper=".$this->db->escape($data['shipper']).",
                  user_type =".$this->db->escape($data['user_type']).",
                  updated_by =".$this->db->escape($data['updated_by']).",
                  last_modify_time =".$this->db->escape($data['last_modify_time'])."
              WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();
        return $update_status;

    }
    function update_fulfillment_data($ord_ship_id, $data)
    {
        $sql="UPDATE tbl_order_shipping_info
              SET shipper=".$this->db->escape($data['shipper']).",
			  	  new_method_name=".$this->db->escape($data['new_method_name']).",
				  new_shipping_service=".$this->db->escape($data['new_shipping_service']).",
                  user_type =".$this->db->escape($data['user_type']).",
                  updated_by =".$this->db->escape($data['updated_by']).",
                  last_modify_time =".$this->db->escape($data['last_modify_time']).",
				  label_file_name = ''
              WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();
        return $update_status;

    }

    function getUpdatedByName($user_type, $updated_by)
    {
        if($user_type==1)
        {
            $sql="Select first_name as update_name FROM tbl_merchant WHERE merchant_row_id=".$this->db->escape($updated_by);

        }
        else if($user_type==2)
        {
            $sql="Select name as update_name FROM tbl_shipping_module_user WHERE shipping_user_id=".$this->db->escape($updated_by);
        }
        else if($user_type==3)
        {
            $sql="Select first_name as update_name FROM tbl_customer WHERE customer_id=".$this->db->escape($updated_by);
        }
        $query=$this->db->query($sql);
        $result=$query->row();

        return $result;
    }
    function db_note($ord_ship_id)
    {
        $sql="SELECT notes FROM tbl_order_shipping_info WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $query=$this->db->query($sql);
        $result= $query->row();
        return $result;
    }
    function modify_notes($ord_ship_id, $data)
    {
        $sql="SELECT DISTINCT(order_row_id) FROM tbl_order_shipping_info WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $query=$this->db->query($sql);
        $order_row_idOBJ=$query->row();
        $order_row_id=$order_row_idOBJ->order_row_id;

        $sql="UPDATE tbl_order_shipping_info
              SET notes=".$this->db->escape($data['notes']).",
                  last_modify_time =".$this->db->escape($data['last_modify_time']).",
                  user_type =".$this->db->escape($data['user_type']).",
                  updated_by =".$this->db->escape($data['updated_by'])."
              WHERE order_row_id=".$this->db->escape($order_row_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();


        return $update_status;

    }
    function modify_track_id($ord_ship_id, $data)
    {
        $sql="UPDATE tbl_order_shipping_info
              SET track_id=".$this->db->escape($data['track_id']).",
                  last_modify_time =".$this->db->escape($data['last_modify_time']).",
                  user_type =".$this->db->escape($data['user_type']).",
                  updated_by =".$this->db->escape($data['updated_by'])."
              WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();
        return $update_status;

    }
    function update_order_tracking($order_row_id, $data)
    {
		//die('here'.$order_row_id);
        $sql="UPDATE tbl_order_shipping_info
              SET track_id=".$this->db->escape($data['track_id']).",
			  	  label_file_name =".$this->db->escape($data['label_file_name']).",
                  last_modify_time =".$this->db->escape($data['last_modify_time']).",
                  user_type =".$this->db->escape($data['user_type']).",
                  updated_by =".$this->db->escape($data['updated_by'])."
              WHERE order_row_id  =".$this->db->escape($order_row_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();
        return $update_status;

    }

    function getShippingAddressDetails($merchant_row_id, $order_row_id)
    {
        $sql="SELECT shipping_address_line1,
                     shipping_address_line2,
                     shipping_customer_city,
                     shipping_customer_state,
                     shipping_customer_zip,
                     shipping_name,
                     shipping_company_name,
                     home_phone_number,
                     customer_email,
                     shipping_country
              FROM tbl_order
              WHERE merchant_row_id=".$this->db->escape($merchant_row_id)." AND order_row_id=".$this->db->escape($order_row_id);
        $result=$this->db->query($sql);
        $result_object=$result->row();

        return $result_object;
    }

    function getOrderDetails($merchant_row_id, $order_row_id)
    {
        $sql="SELECT ordtls.product_description,
                     ordtls.product_quantity,
                     osi.recurring_cycle,
                     osi.status
              FROM tbl_order_shipping_info osi
              INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=osi.order_details_id
              WHERE osi.merchant_row_id=".$this->db->escape($merchant_row_id)." AND osi.order_row_id=".$this->db->escape($order_row_id)." AND osi.add_to_shipping_module='1'";
        $result= $this->db->query($sql);
        $result_array=$result->result_array();

        return $result_array;
    }
    function getFulFillmentOrderDetails($merchant_row_id, $order_row_id)
    {
        $sql="SELECT ordtls.*, ordtls.product_description,
                     ordtls.product_quantity,
                     osi.recurring_cycle,
                     osi.status, osi.method_name, osi.shipping_service, osi.new_method_name, osi.new_shipping_service, osi.label_file_name,
					 p.weight, p.width, p.length, p.height
              FROM tbl_order_shipping_info osi
			  INNER JOIN tbl_product p ON p.product_row_id=osi.product_row_id
              INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=osi.order_details_id
              WHERE osi.merchant_row_id=".$this->db->escape($merchant_row_id)." AND osi.order_row_id=".$this->db->escape($order_row_id)." AND osi.add_to_shipping_module='1'";
        $result= $this->db->query($sql);
        //die($sql);
		return $result->result();
    }

    function getMerchantShippingStatusDetails($merchant_row_id)
    {

           $selected_order_condition = '';
           $statusCondition = " AND (osi.status='1' OR osi.status='3') ";
           if($this->input->post('chk_order_ship'))
           {
               $selected_orders = $this->input->post('chk_order_ship');

               for($i = 0; $i< count($selected_orders); $i++)
               {
               $orderid_other = explode('|',$selected_orders[$i]);

               $selected_order_id_list[] = $orderid_other[0];
               }
               $selected_order_id_list = implode (',',$selected_order_id_list);

               $selected_order_condition =" AND ord.order_row_id IN (" . $selected_order_id_list . ")";
               $statusCondition = '';
           }

        if(!empty($merchant_row_id)){
           $sql="SELECT ord.order_id AS 'ORDERID',
                        ord.shipping_name AS 'NAME',
                        ord.customer_email AS 'EMAIL',
                        ord.home_phone_number AS 'PHONENUMBER',
                        CONCAT('\"', ord.shipping_company_name, '\"') AS 'COMPANY',
                        CONCAT('\"', ord.shipping_address_line1, '\"') AS 'ADDRESS1',
                        CONCAT('\"', ord.shipping_address_line2, '\"') AS 'ADDRESS2',
                        ord.shipping_customer_city AS 'CITY',
                        ord.shipping_customer_state AS 'STATE',
                        ord.shipping_customer_zip AS 'ZIPCODE',
                        ord.shipping_country AS 'COUNTRY',
                        IF (osi.new_method_name = '', osi.method_name, osi.new_method_name) AS 'SHIPVIA',
                        IF (osi.new_shipping_service = '', osi.shipping_service, osi.new_shipping_service) AS 'SHIPMETHOD',
                        IF(osi.track_id != '', CONCAT('\"ID: ', osi.track_id, '\"'), '')  AS 'TRACKINGID',
                        ord.created_on AS 'ORDER DATE',
                        p.product_id AS 'PRODUCTID',
                        CONCAT('\"', ordtls.product_description, '\"') AS 'PRODUCTDESC',
                        IFNULL(IFNULL(ordtls.sub_sku, p.product_code), '') AS 'SKU',
                        ordtls.product_quantity AS 'QUANTITY',
                        CASE osi.status_update_time
                            WHEN '0000-00-00 00:00:00'
                            THEN 'Not Shipped'
                            ELSE DATE_FORMAT(osi.status_update_time, '%m/%d/%Y %H:%i')
                        END AS 'SHIPPEDDATE',
                        CASE osi.last_status
                            WHEN '1' THEN 'Processing'
                            WHEN '2' THEN 'Shipped'
                            WHEN '3' THEN 'Pending'
                            ELSE 'Processing'
                        END AS 'START OF SESSION STATUS',
                        CASE osi.status
                            WHEN '1' THEN 'Processing'
                            WHEN '2' THEN 'Shipped'
                            ELSE 'Pending'
                        END AS 'END OF SESSION STATUS'
                  FROM tbl_order_shipping_info osi
                  INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id = osi.order_details_id
                  INNER JOIN tbl_order ord ON ord.order_row_id = osi.order_row_id
                  INNER JOIN tbl_customer cust ON cust.customer_id = ord.customer_id
                  INNER JOIN tbl_product p ON p.product_row_id = osi.product_row_id
                  WHERE osi.merchant_row_id = ".$this->db->escape($merchant_row_id)."
                  AND osi.add_to_shipping_module='1'
                  ".$selected_order_condition."
                  AND ord.order_status=" . $this->config->item('cos_accepted_num') . "
                  {$statusCondition}";


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

        return false;
    }

     function getMerchantShippingStatusDetailsByCompany($merchant_row_id,$company_id)
    {

        if(!empty($merchant_row_id) & !empty($company_id)){
            $sql="SELECT ord.order_id AS 'ORDERID',
                        ord.shipping_name AS 'NAME',
                        ord.customer_email AS 'EMAIL',
                        ord.home_phone_number AS 'PHONENUMBER',
                        CONCAT('\"', ord.shipping_company_name, '\"') AS 'COMPANY',
                        CONCAT('\"', ord.shipping_address_line1, '\"') AS 'ADDRESS1',
                        CONCAT('\"', ord.shipping_address_line2, '\"') AS 'ADDRESS2',
                        ord.shipping_customer_city AS 'CITY',
                        ord.shipping_customer_state AS 'STATE',
                        ord.shipping_customer_zip AS 'ZIPCODE',
                        ord.shipping_country AS 'COUNTRY',
                        IF (osi.new_method_name = '', osi.method_name, osi.new_method_name) AS 'SHIPVIA',
                        IF (osi.new_shipping_service = '', osi.shipping_service, osi.new_shipping_service) AS 'SHIPMETHOD',
                        IF(osi.track_id != '', CONCAT('\"ID: ', osi.track_id, '\"'), '')  AS 'TRACKINGID',
                        ord.created_on AS 'ORDER DATE',
                        p.product_id AS 'PRODUCTID',
                        ordtls.product_description AS 'PRODUCTDESC',
                        IFNULL(IFNULL(ordtls.sub_sku, p.product_code), '') AS 'SKU',
                        ordtls.product_quantity AS 'QUANTITY',
                        CASE osi.status_update_time
                            WHEN '0000-00-00 00:00:00'
                            THEN 'Not Shipped'
                            ELSE DATE_FORMAT(osi.status_update_time, '%m/%d/%Y %H:%i')
                        END AS 'SHIPPEDDATE',
                        CASE osi.last_status
                            WHEN '1' THEN 'Processing'
                            WHEN '2' THEN 'Shipped'
                            WHEN '3' THEN 'Pending'
                            ELSE 'Processing'
                        END AS 'START OF SESSION STATUS',
                        CASE osi.status
                            WHEN '1' THEN 'Processing'
                            WHEN '2' THEN 'Shipped'
                            ELSE 'Pending'
                        END AS 'END OF SESSION STATUS'
                  FROM tbl_order_shipping_info osi
                  INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id = osi.order_details_id
                  INNER JOIN tbl_order ord ON ord.order_row_id = osi.order_row_id
                  INNER JOIN tbl_customer cust ON cust.customer_id = ord.customer_id
                  INNER JOIN tbl_product p ON p.product_row_id = osi.product_row_id
                  WHERE osi.merchant_row_id = ".$this->db->escape($merchant_row_id)."
                  AND osi.status !=0 AND osi.add_to_shipping_module = '1' AND osi.is_archived = '0' AND ord.order_status =1 AND ( ord.charge_status =4 OR ord.charge_status =2 ) AND osi.assigned_company_id =".$company_id." AND ( osi.status = '1' OR osi.status = '3' )";


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

        return false;
    }

    function show_shipping_module_users ($merchant_row_id)
    {
        $sql = "(SELECT smu.shipping_user_id as shipping_user_id,";
        $sql .= " smu.merchant_row_id as merchant_row_id,";
        $sql .= " smu.name as name,";
        $sql .= " fc.fulfillment_company as company_name,";
        $sql .= " smu.username as username,";
        $sql .= " smu.password, smu.creation_date,";
        $sql .= " CASE smu.active_status WHEN '0' THEN 'Inactive' WHEN '1' THEN 'Active' ELSE 'Inactive' END AS active_status";
        $sql .= " FROM tbl_shipping_module_user smu";
        $sql .= " INNER JOIN tbl_fulfillment_company fc ON fc.company_id=smu.company_id";
        $sql .= " WHERE smu.merchant_row_id = $merchant_row_id ) as XYZ";

        $this->load->library ( 'rapyd' );
        $this->rapyd->load('datagrid');
        $grid = new DataGrid('', $sql);
        $grid->order_by('company_name asc, name asc', '');
        $grid->per_page = 20;
        $grid->column_detail('Company Name', 'company_name', 'process_shipping/process_shipping/edit_shipping_module_user/<#shipping_user_id#>', 'align=\'left\'', false, 'class="submodal-640-480"', true);
        $grid->column_detail('Name', 'name', 'process_shipping/process_shipping/edit_shipping_module_user/<#shipping_user_id#>', 'align=\'left\'', false, 'class="submodal-640-480"', true);
        $grid->column('User Name', 'username',  'align=\'left\'');
        $grid->column('Password', 'password', 'align=\'left\'');
        $grid->column('Status', 'active_status',  'align=\'left\'');
        $grid->build();
        return $grid->output;
    }
    function show_shipping_fulfillment_company ($merchant_row_id)
    {
        $sql = "(SELECT *, ";
        $sql .= " CASE company_type WHEN '1' THEN 'DEFAULT' ELSE 'THIRD PARTY' END AS company_type_text,";
        $sql .= " CASE active_status WHEN '1' THEN 'ACTIVE' ELSE 'INACTIVE' END AS active_status_text";
        $sql .= " FROM tbl_fulfillment_company";
        $sql .= " WHERE merchant_row_id = $merchant_row_id ) as XYZ";

        $this->load->library ( 'rapyd' );
        $this->rapyd->load('datagrid');
        $grid = new DataGrid('', $sql);
        $grid->order_by('fulfillment_company', 'asc');
        $grid->per_page = 20;
        $grid->column('Company Name', 'fulfillment_company', 'align=\'left\'');
        $grid->column('Company Type', 'company_type_text', 'align=\'left\'');
        $grid->column('Active Status', 'active_status_text', 'align=\'left\'');
        $grid->column_detail('Actions', 'Edit', 'process_shipping/process_shipping/edit_fulfillment_company/<#company_id#>','width="35"', false,'class="submodal-600-300"', true);
        $grid->column_detail('', 'Activate/Deactivate', 'process_shipping/process_shipping/activate_deactivate_fulfillment_company/<#company_id#>','width="40"', false, 'class="submodal-600-200"', true);

        $grid->build();
        return $grid->output;
    }
    function delete_fulfillment_company($merchant_row_id, $company_id)
    {
        $sql="SELECT * FROM tbl_fulfillment_company
              WHERE company_id=".$this->db->escape($company_id)."
              AND merchant_row_id=".$this->db->escape($merchant_row_id);
        $query=$this->db->query($sql);
        $result=$query->row();


        if($result->company_type!=1)
        {
            $sql="DELETE
              FROM tbl_fulfillment_company
              WHERE company_id=".$this->db->escape($company_id)."
              AND merchant_row_id=".$this->db->escape($merchant_row_id);
              $this->db->query($sql);
            $status2=$this->db->affected_rows();
            if($status2 > 0)
            {
                $status2=1;
                /*********** Assigning the users to the default Company Starts  ************/
                $sql="SELECT company_id
                      FROM tbl_fulfillment_company
                      WHERE merchant_row_id=".$this->db->escape($merchant_row_id)." AND company_type='1'";
                $query = $this->db->query($sql);
                $result= $query->row();

                if($result->company_id > 0)
                {
                    $sql3="UPDATE tbl_shipping_module_user
                           SET company_id=".$this->db->escape($result->company_id)."
                           WHERE merchant_row_id=".$this->db->escape($merchant_row_id)."
                           AND company_id=".$this->db->escape($company_id);
                    $this->db->query($sql3);
                }
                /*********** Assigning the users to the default Company Ends  ************/
            }

        }
        else if($result->company_type==1)
        {
            $status2=2;
        }
        else
        {
            $status2=0;
        }

        return $status2;

    }
    function email_shipping_module_user_info($merchant_row_id, $data)
    {
        $getInstance = & get_instance();
        $getInstance->load->model('Mail_Manager_model', 'Mail_Manager_model', TRUE);
        $email_status = $getInstance->Mail_Manager_model->sendFulfillmentUserCredentials($merchant_row_id, $data);
        return $email_status;
    }

    function save_shipping_module_user($merchant_row_id, $data)
    {
    //Check whether Same username and password exists
        if($data['shipping_user_id']=='')
        {
            $data['shipping_user_id']=0;
        }
        $sql="SELECT *
              FROM tbl_shipping_module_user
              WHERE username=".$this->db->escape($data['username'])."
              AND merchant_row_id=".$this->db->escape($merchant_row_id)."
              AND shipping_user_id !=".$this->db->escape($data['shipping_user_id']);
        $query=$this->db->query($sql);
        $is_exist=$query->num_rows();
        if($is_exist > 0)
        {
            return 1;
        }
        else
            {

                if($data['shipping_user_id'] > 0)
                {
                    $sql="UPDATE tbl_shipping_module_user
                          SET name=".$this->db->escape($data['name']).",
                              company_id=".$this->db->escape($data['company_id']).",
                              email=".$this->db->escape($data['email']).",
                              username=".$this->db->escape($data['username']).",
                              password=".$this->db->escape($data['password']).",
                              active_status=".$this->db->escape($data['active_status']).",
                              modify_date=".$this->db->escape($data['creation_date'])."
                          WHERE shipping_user_id=".$this->db->escape($data['shipping_user_id'])."
                          AND merchant_row_id=".$this->db->escape($merchant_row_id);
                    $query=$this->db->query($sql);
                    $is_updated=$this->db->affected_rows();
                    if($is_updated >0)
                    {
                        return 2;
                    }
                    else
                    {
                        return 3;
                    }
                }
                else
                {
                    $sql="INSERT INTO tbl_shipping_module_user(merchant_row_id,name,company_id,email,username,password,creation_date,active_status)
                          VALUES(".$this->db->escape($merchant_row_id).",
                                 ".$this->db->escape($data['name']).",
                                 ".$this->db->escape($data['company_id']).",
                                 ".$this->db->escape($data['email']).",
                                 ".$this->db->escape($data['username']).",
                                 ".$this->db->escape($data['password']).",
                                 ".$this->db->escape($data['creation_date']).",
                                 ".$this->db->escape($data['active_status']).")";
                    $query=$this->db->query($sql);
                    $is_updated=$this->db->affected_rows();

                    if($is_updated >0)
                    {
                        $getInstance = &get_instance();
                        $getInstance->load->model('Mail_Manager_model', 'Mail_Manager_model', TRUE);
                        $getInstance->Mail_Manager_model->sendFulfillmentUserCredentials($merchant_row_id, $data);

                        return 4;
                    }
                    else
                    {
                        return 5;
                    }
                }
            }

    }
    function save_fulfillment_company($merchant_row_id, $data)
    {
    //Check whether Same username and password exists
        if($data['company_id']=='')
        {
            $data['company_id']=0;
        }
        $sql="SELECT *
              FROM tbl_fulfillment_company
              WHERE fulfillment_company=".$this->db->escape($data['fulfillment_company'])."
              AND merchant_row_id=".$this->db->escape($merchant_row_id)."
              AND company_id !=".$this->db->escape($data['company_id']);
        $query=$this->db->query($sql);
        $is_exist=$query->num_rows();
        if($is_exist > 0)
        {
            return 1;
        }
        else
            {

                if($data['company_id'] > 0)
                {
                    $sql="UPDATE tbl_fulfillment_company
                          SET fulfillment_company=".$this->db->escape($data['fulfillment_company']).",
                              modify_date=".$this->db->escape($data['creation_date'])."
                          WHERE company_id=".$this->db->escape($data['company_id'])."
                          AND merchant_row_id=".$this->db->escape($merchant_row_id);
                    $query=$this->db->query($sql);
                    $is_updated=$this->db->affected_rows();
                    if($is_updated >0)
                    {
                        return 2;
                    }
                    else
                    {
                        return 3;
                    }
                }
                else
                {
                    $sql="INSERT INTO tbl_fulfillment_company(merchant_row_id, fulfillment_company,active_status,creation_date)
                          VALUES(".$this->db->escape($merchant_row_id).",
                                 ".$this->db->escape($data['fulfillment_company']).",
                                 ".$this->db->escape($data['active_status']).",
                                 ".$this->db->escape($data['creation_date']).")";
                    $query=$this->db->query($sql);
                    $is_updated=$this->db->affected_rows();
                    if($is_updated >0)
                    {
                        return 4;
                    }
                    else
                    {
                        return 5;
                    }
                }
            }

    }
    function activate_deactive_fulfillment_company($merchant_row_id, $data)
    {
        if($data['company_id'] > 0)
                {
                    $sql="UPDATE tbl_fulfillment_company
                          SET active_status=".$this->db->escape($data['active_status']).",
                              modify_date=".$this->db->escape($data['creation_date'])."
                          WHERE company_id=".$this->db->escape($data['company_id'])."
                          AND merchant_row_id=".$this->db->escape($merchant_row_id);
                    $query=$this->db->query($sql);
                    $is_updated=$this->db->affected_rows();
                    if($is_updated >0)
                    {
                        $sqlU="UPDATE tbl_shipping_module_user
                               SET active_status=".$this->db->escape($data['active_status'])."
                               WHERE merchant_row_id=".$this->db->escape($merchant_row_id)." AND
                               company_id=".$this->db->escape($data['company_id']);
                        $queryU=$this->db->query($sqlU);

                        if($data['active_status']==0)
                        {
                            $sqlP="UPDATE tbl_product
                                   SET assigned_company_id='0'
                                   WHERE merchant_row_id=".$this->db->escape($merchant_row_id)."
                                   AND assigned_company_id=".$this->db->escape($data['company_id']);
                            $queryP=$this->db->query($sqlP);
                        }

                        return 1;
                    }
                    else
                    {
                        return 0;
                    }
                }
        else{
            return 0;
        }

    }

    function get_user_data($merchant_row_id=0, $shipping_user_id=0)
    {
        $sql="SELECT * FROM tbl_shipping_module_user
              WHERE shipping_user_id=".$this->db->escape($shipping_user_id)."
              AND merchant_row_id=".$this->db->escape($merchant_row_id);
        $query=$this->db->query($sql);
        $result=$query->row_array();
        return $result;

    }
    function get_fulfillment_company_active_status($merchant_row_id=0,$company_id=0)
    {
        $sql="SELECT active_status as active_status,
                     fulfillment_company as company_name
              FROM tbl_fulfillment_company
              WHERE merchant_row_id=".$this->db->escape($merchant_row_id)."
              AND company_id=".$this->db->escape($company_id);
        $query=$this->db->query($sql);
        $result=$query->row();

        return $result;

    }

    function get_fulfillment_company_data($merchant_row_id=0, $company_id=0)
    {
        $sql="SELECT * FROM tbl_fulfillment_company
              WHERE company_id=".$this->db->escape($company_id)."
              AND merchant_row_id=".$this->db->escape($merchant_row_id);
        $query=$this->db->query($sql);
        $result=$query->row();
        return $result;

    }

    /// Recurrin order Add to Fulfillment Module//////////
    function saveRecurringShippingInfo($recurring_orders_id=0, $payment_id=0)
    {
        $sql="SELECT rd.recurring_orders_id,
                     rd.merchant_row_id,
                     rd.product_row_id,
                     rd.customer_id,
                     pd.payment_id,
                     osi.shipping_id,
                     osi.method_name,
                     osi.shipping_amount,
                     osi.product_attribute,
                     osi.free_text_attribute,
                     osi.shipping_service,
                     osi.bundle_id,
                     osi.bundle_name,
                     rd.add_ship_to_recurring,
                     rd.add_tax_to_recurring,
                     rd.add_recurring_to_shipping_module,
                     rd.assigned_company_id,
                     rd.recurring_shipping_charge,
                     rd.order_row_id,
                     rd.successfull_attempted
              FROM tbl_recurring_orders rd
              INNER JOIN tbl_payment_details pd ON pd.recurring_orders_id=rd.recurring_orders_id
              INNER JOIN tbl_order_shipping_info osi ON osi.order_details_id=rd.order_details_id
              WHERE pd.payment_id=".$this->db->escape($payment_id)." AND rd.recurring_orders_id=".$this->db->escape($recurring_orders_id)." AND pd.success='1'
              AND pd.is_refund='0' AND rd.add_recurring_to_shipping_module='1'";

        $query= $this->db->query($sql);
        $result=$query->row();
        $data= $query->num_rows();

        if($data > 0)
        {
            $recurring_cycle = $result->successfull_attempted + 1;
            if($result->shipping_service!='')
            {

            $sql="INSERT INTO tbl_recurring_order_shipping_info(recurring_orders_id, order_row_id, merchant_row_id,product_row_id,customer_id, payment_id,shipping_id, method_name, shipping_amount, shipping_service,add_to_shipping_module,assigned_company_id,product_attribute,free_text_attribute,bundle_id,bundle_name,status,recurring_cycle)
                        VALUES(".$this->db->escape($result->recurring_orders_id).",
                                ".$this->db->escape($result->order_row_id).",
                                ".$this->db->escape($result->merchant_row_id).",
                                ".$this->db->escape($result->product_row_id).",
                                ".$this->db->escape($result->customer_id).",
                                ".$this->db->escape($result->payment_id).",
                                ".$this->db->escape($result->shipping_id).",
                                ".$this->db->escape($result->method_name).",
                                ".$this->db->escape($result->recurring_shipping_charge).",
                                ".$this->db->escape($result->shipping_service).",
                                ".$this->db->escape($result->add_recurring_to_shipping_module).",
                                ".$this->db->escape($result->assigned_company_id).",
                                ".$this->db->escape($result->product_attribute).",
                                ".$this->db->escape($result->free_text_attribute).",
                                ".$this->db->escape($result->bundle_id).",
                                ".$this->db->escape($result->bundle_name).",
                                ".'1'.",
                                ".$this->db->escape($recurring_cycle).")";
            }
            else
            {
                $sql="INSERT INTO tbl_recurring_order_shipping_info(recurring_orders_id, order_row_id, merchant_row_id,product_row_id,customer_id, payment_id,shipping_id, method_name, shipping_amount, add_to_shipping_module,assigned_company_id,product_attribute,free_text_attribute,bundle_id,bundle_name,status,recurring_cycle)
                        VALUES(".$this->db->escape($result->recurring_orders_id).",
                                ".$this->db->escape($result->order_row_id).",
                                ".$this->db->escape($result->merchant_row_id).",
                                ".$this->db->escape($result->product_row_id).",
                                ".$this->db->escape($result->customer_id).",
                                ".$this->db->escape($result->payment_id).",
                                ".$this->db->escape($result->shipping_id).",
                                ".$this->db->escape($result->method_name).",
                                ".$this->db->escape($result->recurring_shipping_charge).",
                                ".$this->db->escape($result->add_recurring_to_shipping_module).",
                                ".$this->db->escape($result->assigned_company_id).",
                                ".$this->db->escape($result->product_attribute).",
                                ".$this->db->escape($result->free_text_attribute).",
                                ".$this->db->escape($result->bundle_id).",
                                ".$this->db->escape($result->bundle_name).",
                                ".'1'.",
                                ".$this->db->escape($recurring_cycle).")";

            }

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

        return true;
    }

/////////// Add Recurring Orders to Fulfillment Module /////////////////
    function default_recurring_shipping_list($merchant_row_id ,$condition, $data, $config){

        $date_format = 'DATE_FORMAT(pd.date_of_transaction,\'' . $_SESSION['date_format'] . '\') AS created_on';

              $sql="SELECT rosi.ord_ship_id AS ord_ship_id,
                        rosi.recurring_orders_id,   
                        rosi.merchant_row_id AS merchant_row_id,
                        rosi.product_row_id AS product_row_id,
                        rosi.shipping_id AS shipping_id,
                        rosi.method_name AS method_name,
                        rosi.shipping_service AS shipping_service,
                        rosi.recurring_cycle AS recurring_cycle,
                        rosi.track_id AS track_id,
                        rosi.status AS processing_status,
                        rosi.last_status AS last_status,
                        rosi.notes AS notes,
                        rosi.shipper AS shipper,
                        rosi.bundle_id AS bundle_id,
                        rosi.bundle_name AS bundle_name,
                        rosi.shipping_amount AS shipping_amount,
                        rosi.assigned_company_id AS assigned_company_id,
                        ordtls.product_description AS product_description,
                        ord.order_id AS order_id,
                        pd.order_row_id AS order_row_id,
                        pd.recurring_payment_id,
                        ord.shipping_address_line1,
                        ord.shipping_address_line2,
                        ord.shipping_customer_city,
                        CASE TRIM(ord.shipping_customer_state)
                        WHEN '' THEN ord.customer_state
                        ELSE ord.shipping_customer_state
                        END AS shipping_customer_state,
                        ord.shipping_customer_zip,
                        ord.shipping_name,
                        ord.shipping_company_name,
                        ord.home_phone_number,
                        ord.customer_email,
                        ord.shipping_country,
                        $date_format,
                        CONCAT(ord.first_name,' ',ord.last_name) as cust_name
                FROM tbl_recurring_order_shipping_info rosi
                INNER JOIN tbl_payment_details pd ON pd.payment_id=rosi.payment_id
                INNER JOIN tbl_recurring_orders ro ON ro.recurring_orders_id=pd.recurring_orders_id
                INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=ro.order_details_id
                INNER JOIN tbl_order ord ON ord.order_row_id=pd.order_row_id
                WHERE rosi.merchant_row_id=".$this->db->escape($merchant_row_id)."
                AND rosi.recurring_orders_id IN (".$data['recurring_orders_id'].") AND pd.is_refund = 0 AND rosi.add_to_shipping_module='1' ".$condition." ORDER BY pd.date_of_transaction DESC";




        $result=$this->db->query($sql);

        $result_array= $result->result_array();

        return $result_array;

    }

    function user_recurring_shipping_list($merchant_row_id ,$condition, $data, $config)
    {
      $date_format = 'DATE_FORMAT(pd.date_of_transaction,\'' . $_SESSION['date_format'] . '\') AS created_on';
      $sql="SELECT rosi.ord_ship_id AS ord_ship_id,
                rosi.recurring_orders_id,   
                rosi.merchant_row_id AS merchant_row_id,
                rosi.product_row_id AS product_row_id,
                rosi.shipping_id AS shipping_id,
                rosi.method_name AS method_name,
				rosi.new_method_name AS new_method_name,
                rosi.new_shipping_service AS new_shipping_service,
                rosi.label_file_name AS label_file_name,
                rosi.shipping_service AS shipping_service,
                rosi.recurring_cycle AS recurring_cycle,
                rosi.track_id AS track_id,
                rosi.status AS processing_status,
                rosi.last_status AS last_status,
                rosi.notes AS notes,
                rosi.shipper AS shipper,
                rosi.bundle_id AS bundle_id,
                rosi.bundle_name AS bundle_name,
                rosi.shipping_amount AS shipping_amount,
                ordtls.product_description AS product_description,
                ord.order_id AS order_id,
                pd.order_row_id AS order_row_id,
                pd.recurring_payment_id,
                ord.shipping_address_line1,
                ord.shipping_address_line2,
                ord.shipping_customer_city,
                CASE TRIM(ord.shipping_customer_state)
                WHEN '' THEN ord.customer_state
                ELSE ord.shipping_customer_state
                END AS shipping_customer_state,
                ord.shipping_customer_zip,
                ord.shipping_name,
                ord.shipping_company_name,
                ord.home_phone_number,
                ord.customer_email,
                ord.shipping_country,
                $date_format,
                CONCAT(ord.first_name,' ',ord.last_name) as cust_name
        FROM tbl_recurring_order_shipping_info rosi
        INNER JOIN tbl_payment_details pd ON pd.payment_id=rosi.payment_id
        INNER JOIN tbl_recurring_orders ro ON ro.recurring_orders_id=pd.recurring_orders_id
        INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=ro.order_details_id
        INNER JOIN tbl_order ord ON ord.order_row_id=pd.order_row_id
        WHERE rosi.merchant_row_id=".$this->db->escape($merchant_row_id)."
        AND rosi.recurring_orders_id IN (".$data['recurring_orders_id'].") AND rosi.add_to_shipping_module='1' AND pd.is_refund = 0 AND rosi.status !='4' ".$condition." ORDER BY pd.date_of_transaction DESC";

        $result=$this->db->query($sql);
        $result_array= $result->result_array();
        return $result_array;
    }

    function assign_company_recurring($ord_ship_id, $data)
    {
        $sql="UPDATE tbl_recurring_order_shipping_info
              SET assigned_company_id=".$this->db->escape($data['company_id']).",
                  assigned_company_name =".$this->db->escape($data['fulfillment_company']).",
                  task_assign_time =".$this->db->escape($data['task_assign_time']).",
                  updated_by =".$this->db->escape($data['updated_by'])."
              WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();

        /* Insert data in the tbl_fulfillment_email_notification Starts */

        if($data['company_id'] > 0)
        {
            $sql="SELECT ord.order_id as order_id,
                         rosi.assigned_company_id
                  FROM tbl_recurring_order_shipping_info rosi
                  INNER JOIN tbl_order ord ON ord.order_row_id=rosi.order_row_id
                  WHERE  rosi.ord_ship_id=".$this->db->escape($ord_ship_id)." AND rosi.assigned_company_id=".$this->db->escape($data['company_id']);
            $query = $this->db->query($sql);
            $result = $query->row();

            $sql="SELECT order_id
                  FROM tbl_fulfillment_email_notification
                  WHERE order_id=".$this->db->escape($result->order_id)." AND is_recurring_order='1'";
            $query = $this->db->query($sql);
            $exists = $query->num_rows();

            if($exists > 0)
            {
                $sql1 = "UPDATE tbl_fulfillment_email_notification
                        SET company_id=".$this->db->escape($data['company_id'])."
                        WHERE order_id=".$this->db->escape($result->order_id)." AND is_recurring_order='1'";
                $this->db->query($sql1);
            }
            else
            {
                $sql1 = "INSERT INTO tbl_fulfillment_email_notification(order_id, company_id, email_sent_status, is_recurring_order)
                         VALUES(".$this->db->escape($result->order_id).",
                                ".$this->db->escape($data['company_id']).",
                                '0', '1') ";
                $this->db->query($sql1);
            }

        }
        else
        {
            $sql="SELECT ord.order_id as order_id,
                         rosi.assigned_company_id as company_id
                  FROM tbl_recurring_order_shipping_info rosi
                  INNER JOIN tbl_order ord ON ord.order_row_id=rosi.order_row_id
                  WHERE  rosi.ord_ship_id=".$this->db->escape($ord_ship_id)." AND rosi.assigned_company_id='0'";
            $query = $this->db->query($sql);
            $result = $query->row();

            $sql = "DELETE FROM tbl_fulfillment_email_notification
                    WHERE order_id=".$this->db->escape($result->order_id)." AND is_recurring_order='1'";

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

        /* Insert data in the tbl_fulfillment_email_notification Ends */



        return $update_status;

    }
    function modify_status_recurring($ord_ship_id, $data)
    {
        $sql="UPDATE tbl_recurring_order_shipping_info
              SET status=".$this->db->escape($data['status']).",
                  status_update_time =".$this->db->escape($data['status_update_time']).",
                  user_type =".$this->db->escape($data['user_type']).",
                  updated_by =".$this->db->escape($data['updated_by']).",
                  last_modify_time =".$this->db->escape($data['last_modify_time']).",
                  last_status=".$this->db->escape($data['last_status'])."
              WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();

        return $update_status;
    }

    function modify_shipper_recurring($ord_ship_id, $data)
    {
        $sql="UPDATE tbl_recurring_order_shipping_info
              SET shipper=".$this->db->escape($data['shipper']).",
                  user_type =".$this->db->escape($data['user_type']).",
                  updated_by =".$this->db->escape($data['updated_by']).",
                  last_modify_time =".$this->db->escape($data['last_modify_time'])."
              WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();
        return $update_status;

    }
    function db_note_recurring($ord_ship_id)
    {
        $sql="SELECT notes FROM tbl_recurring_order_shipping_info WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $query=$this->db->query($sql);
        $result= $query->row();
        return $result;
    }
    function modify_notes_recurring($ord_ship_id, $data)
    {
        $sql="SELECT DISTINCT(order_row_id) FROM tbl_recurring_order_shipping_info WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $query=$this->db->query($sql);
        $order_row_idOBJ=$query->row();
        $order_row_id=$order_row_idOBJ->order_row_id;

        $sql="UPDATE tbl_recurring_order_shipping_info
              SET notes=".$this->db->escape($data['notes']).",
                  last_modify_time =".$this->db->escape($data['last_modify_time']).",
                  user_type =".$this->db->escape($data['user_type']).",
                  updated_by =".$this->db->escape($data['updated_by'])."
              WHERE order_row_id=".$this->db->escape($order_row_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();


        return $update_status;

    }
    function modify_track_id_recurring($ord_ship_id, $data)
    {
        $sql="UPDATE tbl_recurring_order_shipping_info
              SET track_id=".$this->db->escape($data['track_id']).",
                  last_modify_time =".$this->db->escape($data['last_modify_time']).",
                  user_type =".$this->db->escape($data['user_type']).",
                  updated_by =".$this->db->escape($data['updated_by'])."
              WHERE ord_ship_id=".$this->db->escape($ord_ship_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();
        return $update_status;

    }
    function getOrderDetailsRecurring($merchant_row_id, $order_row_id)
    {
        $sql="SELECT ordtls.product_description,
                     ordtls.product_quantity,
                     rosi.recurring_cycle,
                     rosi.status
              FROM tbl_recurring_order_shipping_info rosi
              INNER JOIN tbl_payment_details pd ON pd.payment_id=rosi.payment_id
              INNER JOIN tbl_recurring_orders ro ON ro.recurring_orders_id=pd.recurring_orders_id
              INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=ro.order_details_id
              INNER JOIN tbl_customer cust ON cust.customer_id=ro.customer_id
              INNER JOIN tbl_order ord ON ord.order_row_id=pd.order_row_id
              WHERE rosi.merchant_row_id=".$this->db->escape($merchant_row_id)." AND ro.order_row_id=".$this->db->escape($order_row_id)." AND rosi.add_to_shipping_module='1'";
        $result= $this->db->query($sql);
        $result_array=$result->result_array();

        return $result_array;
    }

    function getMerchantShippingStatusDetailsRecurring($merchant_row_id)
    {
        if($merchant_row_id){
            $sql="SELECT ord.order_id AS 'ORDERID',
                        ord.shipping_name AS 'NAME',
                        ord.customer_email AS 'EMAIL',
                        ord.home_phone_number AS 'PHONENUMBER',
                        CONCAT('\"', ord.shipping_company_name, '\"') AS 'COMPANY',
                        CONCAT('\"', ord.shipping_address_line1, '\"') AS 'ADDRESS1',
                        CONCAT('\"', ord.shipping_address_line2, '\"') AS 'ADDRESS2',
                        ord.shipping_customer_city AS 'CITY',
                        ord.shipping_customer_state AS 'STATE',
                        ord.shipping_customer_zip AS 'ZIPCODE',
                        ord.shipping_country AS 'COUNTRY',
                        IF (rosi.new_method_name = '', rosi.method_name, rosi.new_method_name) AS 'SHIPVIA',
                        IF (rosi.new_shipping_service = '', rosi.shipping_service, rosi.new_shipping_service) AS 'SHIPMETHOD',
                        IF(rosi.track_id != '', CONCAT('\"ID: ', rosi.track_id, '\"'), '')  AS 'TRACKINGID',
                        pd.date_of_transaction AS 'ORDER DATE',
                        p.product_id AS 'PRODUCTID',
                        CONCAT('\"', ordtls.product_description, '\"') AS 'PRODUCTDESC',
                        IFNULL(IFNULL(ordtls.sub_sku, p.product_code), '') AS 'SKU',
                        ro.product_quantity AS 'QUANTITY',
                        CASE rosi.status_update_time
                            WHEN '0000-00-00 00:00:00'
                            THEN 'Not Shipped'
                            ELSE DATE_FORMAT(rosi.status_update_time, '%m/%d/%Y %H:%i')
                        END AS 'SHIPPEDDATE',
                        CASE rosi.last_status
                            WHEN '1' THEN 'Processing'
                            WHEN '2' THEN 'Shipped'
                            WHEN '3' THEN 'Pending'
                            ELSE 'Processing'
                        END AS 'START OF SESSION STATUS',
                        CASE rosi.status
                            WHEN '1' THEN 'Processing'
                            WHEN '2' THEN 'Shipped'
                            ELSE 'Pending'
                        END AS 'END OF SESSION STATUS'
            FROM tbl_recurring_order_shipping_info rosi
            INNER JOIN tbl_payment_details pd ON pd.payment_id=rosi.payment_id
            INNER JOIN tbl_recurring_orders ro ON ro.recurring_orders_id = rosi.recurring_orders_id
            INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id = ro.order_details_id
            INNER JOIN tbl_customer cust ON cust.customer_id = ro.customer_id
            INNER JOIN tbl_order ord ON ord.order_row_id = rosi.order_row_id
            INNER JOIN tbl_product p ON p.product_row_id = rosi.product_row_id
            WHERE rosi.merchant_row_id = " . $this->db->escape($merchant_row_id) . "
            AND rosi.add_to_shipping_module='1'
            AND (rosi.status = '1' OR rosi.status = '3')";

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

    function getMerchantShippingStatusDetailsRecurringByCompany($merchant_row_id,$company_id)
    {
        if(!empty($merchant_row_id) & !empty($company_id)){
            $sql="SELECT ord.order_id AS 'ORDERID',
                        ord.shipping_name AS 'NAME',
                        ord.customer_email AS 'EMAIL',
                        ord.home_phone_number AS 'PHONENUMBER',
                        CONCAT('\"', ord.shipping_company_name, '\"') AS 'COMPANY',
                        CONCAT('\"', ord.shipping_address_line1, '\"') AS 'ADDRESS1',
                        CONCAT('\"', ord.shipping_address_line2, '\"') AS 'ADDRESS2',
                        ord.shipping_customer_city AS 'CITY',
                        ord.shipping_customer_state AS 'STATE',
                        ord.shipping_customer_zip AS 'ZIPCODE',
                        ord.shipping_country AS 'COUNTRY',
                        IF (rosi.new_method_name = '', rosi.method_name, rosi.new_method_name) AS 'SHIPVIA',
                        IF (rosi.new_shipping_service = '', rosi.shipping_service, rosi.new_shipping_service) AS 'SHIPMETHOD',
                        IF(rosi.track_id != '', CONCAT('\"ID: ', rosi.track_id, '\"'), '')  AS 'TRACKINGID',
                        pd.date_of_transaction AS 'ORDER DATE',
                        p.product_id AS 'PRODUCTID',
                        ordtls.product_description AS 'PRODUCTDESC',
                        IFNULL(IFNULL(ordtls.sub_sku, p.product_code), '') AS 'SKU',
                        ro.product_quantity AS 'QUANTITY',
                        CASE rosi.status_update_time
                            WHEN '0000-00-00 00:00:00'
                            THEN 'Not Shipped'
                            ELSE DATE_FORMAT(rosi.status_update_time, '%m/%d/%Y %H:%i')
                        END AS 'SHIPPEDDATE',
                        CASE rosi.last_status
                            WHEN '1' THEN 'Processing'
                            WHEN '2' THEN 'Shipped'
                            WHEN '3' THEN 'Pending'
                            ELSE 'Processing'
                        END AS 'START OF SESSION STATUS',
                        CASE rosi.status
                            WHEN '1' THEN 'Processing'
                            WHEN '2' THEN 'Shipped'
                            ELSE 'Pending'
                        END AS 'END OF SESSION STATUS'
            FROM tbl_recurring_order_shipping_info rosi
            INNER JOIN tbl_payment_details pd ON pd.payment_id=rosi.payment_id
            INNER JOIN tbl_recurring_orders ro ON ro.recurring_orders_id = rosi.recurring_orders_id
            INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id = ro.order_details_id
            INNER JOIN tbl_customer cust ON cust.customer_id = ro.customer_id
            INNER JOIN tbl_order ord ON ord.order_row_id = rosi.order_row_id
            INNER JOIN tbl_product p ON p.product_row_id = rosi.product_row_id
            WHERE rosi.merchant_row_id = " . $this->db->escape($merchant_row_id) . "
            AND rosi.add_to_shipping_module='1'
            AND pd.success='1' AND rosi.assigned_company_id=".$company_id." AND (rosi.status='1' OR rosi.status='3')";

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

    function get_total_regular_orders_number($merchant_row_id=0 ,$omit_status_no=0)
    {
        //$condition=" AND osi.status = 1";
        $condition =" AND (osi.status='1' OR osi.status='3') ";
        $sql="SELECT osi.order_row_id
              FROM tbl_order_shipping_info osi
              INNER JOIN tbl_order ord ON ord.order_row_id=osi.order_row_id
              INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=osi.order_details_id
              INNER JOIN tbl_customer cust ON cust.customer_id=ord.customer_id
              WHERE osi.merchant_row_id=".$this->db->escape($merchant_row_id)."
              AND osi.add_to_shipping_module='1'
              AND ord.order_status=".$this->config->item('cos_accepted_num')."
              AND (ord.charge_status=".$this->config->item('ccs_charge_accepted_num')." OR ord.charge_status=".$this->config->item('ccs_accepted_manual_num').")
              ".$condition."
              GROUP BY osi.order_row_id
              ORDER BY ord.created_on DESC";

        $query= $this->db->query($sql);
        $total_rows=$query->num_rows();
        return $total_rows;
    }
    function get_total_regular_orders_number_tp($merchant_row_id=0 ,$omit_status_no=0,$company_id=0)
    {

        $condition =" AND (osi.status='1' OR osi.status='3') ";
        $sql="SELECT osi.order_row_id
              FROM tbl_order_shipping_info osi
              INNER JOIN tbl_order ord ON ord.order_row_id=osi.order_row_id
              INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=osi.order_details_id
              INNER JOIN tbl_customer cust ON cust.customer_id=ord.customer_id
              WHERE osi.merchant_row_id=".$this->db->escape($merchant_row_id)."
              AND osi.add_to_shipping_module='1'
              AND ord.order_status=".$this->config->item('cos_accepted_num')."
              AND (ord.charge_status=".$this->config->item('ccs_charge_accepted_num')." OR ord.charge_status=".$this->config->item('ccs_accepted_manual_num').")
              AND osi.assigned_company_id=".$this->db->escape($company_id)." ".$condition."
              GROUP BY osi.order_row_id
              ORDER BY ord.created_on DESC";

        $query= $this->db->query($sql);
        $total_rows=$query->num_rows();
        //dumpVar($sql);
        //die();
        return $total_rows;
    }

    function get_total_recurring_orders_number($merchant_row_id=0 ,$omit_status_no=0)
    {
        //$condition='AND rosi.status !='.$this->db->escape($omit_status_no);
        $condition =" AND (rosi.status='1' OR rosi.status='3') ";

        $sql="SELECT rosi.recurring_orders_id
              FROM tbl_recurring_order_shipping_info rosi
              INNER JOIN tbl_payment_details pd ON pd.payment_id=rosi.payment_id
              INNER JOIN tbl_recurring_orders ro ON ro.recurring_orders_id=pd.recurring_orders_id
              INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=ro.order_details_id
              INNER JOIN tbl_customer cust ON cust.customer_id=ro.customer_id
              INNER JOIN tbl_order ord ON ord.order_row_id=pd.order_row_id
              WHERE rosi.merchant_row_id=".$this->db->escape($merchant_row_id)." AND rosi.add_to_shipping_module='1' AND pd.is_refund = 0 AND pd.success='1' ".$condition." GROUP BY ro.order_row_id ORDER BY pd.date_of_transaction DESC";
        $query= $this->db->query($sql);
        $total_rows=$query->num_rows();
        return $total_rows;
    }
    function get_total_recurring_orders_number_tp($merchant_row_id=0 ,$omit_status_no=0,$company_id=0)
    {
        // before it searched status not in 2,4 now i have conveterted it status in 1,3. bcz status 5 or other(if have) should not be enlisted. by masud
        $condition =" AND (rosi.status='1' OR rosi.status='3') ";
        $sql="SELECT rosi.recurring_orders_id
              FROM tbl_recurring_order_shipping_info rosi
              INNER JOIN tbl_payment_details pd ON pd.payment_id=rosi.payment_id
              INNER JOIN tbl_recurring_orders ro ON ro.recurring_orders_id=pd.recurring_orders_id
              INNER JOIN tbl_order_details ordtls ON ordtls.order_details_id=ro.order_details_id
              INNER JOIN tbl_customer cust ON cust.customer_id=ro.customer_id
              INNER JOIN tbl_order ord ON ord.order_row_id=pd.order_row_id
              WHERE rosi.merchant_row_id=".$this->db->escape($merchant_row_id)." AND rosi.add_to_shipping_module='1' AND pd.is_refund = 0 AND pd.success='1' AND rosi.assigned_company_id=".$this->db->escape($company_id)." ".$condition." GROUP BY ro.order_row_id ORDER BY pd.date_of_transaction DESC";
        $query= $this->db->query($sql);
        $total_rows=$query->num_rows();
        return $total_rows;
    }

    function deleteShippingOrder($merchant_row_id,$order_row_id)
    {
        if(!empty($merchant_row_id) && !empty($order_row_id))
        {
            $sql="DELETE FROM tbl_order_shipping_info
                    WHERE merchant_row_id=".$this->db->escape($merchant_row_id)."
                    AND order_row_id=".$this->db->escape($order_row_id);
            $this->db->query($sql);
            return true;
        }

        return false;
    }

    function deleteRecurringShippingOrder($merchant_row_id,$order_row_id)
    {
        if(!empty($merchant_row_id) && !empty($order_row_id))
        {
            $sql="DELETE FROM tbl_recurring_order_shipping_info
                  WHERE merchant_row_id=".$this->db->escape($merchant_row_id)."
                  AND order_row_id=".$this->db->escape($order_row_id);
            $this->db->query($sql);
            return true;
        }
        return false;
    }

    function get_fulfillment_company_list($merchant_row_id=0)
    {
        $sql="SELECT *
              FROM tbl_fulfillment_company
              WHERE merchant_row_id=".$this->db->escape($merchant_row_id)." ORDER BY fulfillment_company ASC";

        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }
    function get_fulfillment_company_list_active($merchant_row_id=0)
    {
        $sql="SELECT *
              FROM tbl_fulfillment_company
              WHERE merchant_row_id=".$this->db->escape($merchant_row_id)." AND active_status='1' ORDER BY fulfillment_company ASC";

        $query = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }
    function get_fulfillment_company_id($merchant_row_id=0, $company_id=0)
    {
        $sql="SELECT company_id
              FROM tbl_fulfillment_company
              WHERE merchant_row_id=".$this->db->escape($merchant_row_id)."
              AND company_id=".$this->db->escape($company_id);

        $query = $this->db->query($sql);
        $result = $query->row();
        return $result;
    }

    function insert_fulfillment_company_data($merchant_row_id=0)
    {
        $creation_date = getCurrentDateTimeForDB();
        $sql="INSERT INTO tbl_fulfillment_company(merchant_row_id,fulfillment_company,company_type,creation_date) VALUES(".$this->db->escape($merchant_row_id).",
                'My Fulfillment Company',
                '1',
                ".$this->db->escape($creation_date).")";
        $this->db->query($sql);
        return $this->db->insert_id();
    }
    function insert_fulfillment_user_data($merchant_row_id=0,$company_id=0)
    {
        $sql="SELECT merchant_row_id,
                     email_address
              FROM tbl_merchant
              WHERE merchant_row_id=".$this->db->escape($merchant_row_id);
        $query = $this->db->query($sql);
        $result = $query->row();
        $creation_date = getCurrentDateTimeForDB();

        $sql="INSERT INTO tbl_shipping_module_user(merchant_row_id,company_id,email,name,username,password,creation_date,active_status,user_type) VALUES(".$this->db->escape($merchant_row_id).",
                            ".$this->db->escape($company_id).",
                            ".$this->db->escape($result->email_address).",
                        'Default Fulfillment User',
                        'defaultuser',
                        '123456',
                        ".$this->db->escape($creation_date).",
                        '1',
                        '1')";
        $this->db->query($sql);
    }

    function setEmailSendStatus($data)
    {
        $sql="DELETE FROM tbl_fulfillment_email_notification WHERE email_id=".$this->db->escape($data['email_id']);
        $this->db->query($sql);

        return true;
    }

    function getFulfillmentCompanyID($item=0)
    {
        $sql="SELECT assigned_company_id
              FROM tbl_product
              WHERE product_row_id=".$this->db->escape($item);
        $query=$this->db->query($sql);
        $result= $query->row();
        return $result->assigned_company_id;
    }

    function getAllCarrierInfoByMerchant($merchantRowId)
    {
        if(!empty($merchantRowId))
        {
            $sql = 'SELECT *
                    FROM tbl_shipping_carrier_merchant
                    WHERE merchant_row_id = ' . $this->db->escape($merchantRowId);

            $query = $this->db->query($sql);
            return $query->result();
        }

        return false;
    }

    function getFlatRateServices($merchantRowId)
    {
        if(!empty($merchantRowId))
        {
            $this->db->where('merchant_row_id', $merchantRowId);
            //$this->db->order_by('is_domestic DESC, rate ASC');
            $this->db->order_by('is_domestic DESC, service_order ASC');
            $q = $this->db->get('tbl_shipping_flat_rate_service');
            return $q->result();
        }

        return false;
    }

    function getDomesticFlatRateServices($merchantRowId)
    {
        if(!empty($merchantRowId))
        {
            $this->db->select('rate, title');
            $this->db->where('merchant_row_id', $merchantRowId);
            $this->db->where('is_domestic', '1');
            $this->db->where('active_status', '1');
            $this->db->order_by('service_order ASC');
            $q = $this->db->get('tbl_shipping_flat_rate_service');
            return $q->result();
        }

        return false;
    }

    function getInternationalFlatRateServices($merchantRowId)
    {
        if(!empty($merchantRowId))
        {
            $this->db->select('rate, title');
            $this->db->where('merchant_row_id', $merchantRowId);
            $this->db->where('is_domestic', '0');
            $this->db->where('active_status', '1');
            $this->db->order_by('service_order ASC');
            $q = $this->db->get('tbl_shipping_flat_rate_service');
            return $q->result();
        }

        return false;
    }

    function deleteFlatRateServices($merchantRowId)
    {
        if(!empty($merchantRowId))
        {
            $this->db->where('merchant_row_id', $merchantRowId);
            $this->db->delete('tbl_shipping_flat_rate_service');
        }
    }

    function saveFlatRateServices($services)
    {
        $sql = 'INSERT INTO tbl_shipping_flat_rate_service
                VALUES';
        $values = array();

        foreach($services as $service)
        {
            $values[] = '(' . $service['merchant_row_id'] . ',' . $service['rate'] . ',' . $this->db->escape($service['title']) . ',' . $service['is_domestic'] . ',' . $service['active_status'] . ',' . $service['service_order'] . ')';
        }

        $sql .= implode(',', $values);
        $this->db->query($sql);
    }

    function getFlatRateService($merchantRowId, $title, $isDomestic = true)
    {
        $this->db->select('rate, title');
        $this->db->where('merchant_row_id', $merchantRowId);
        $this->db->where('title', $title);
        $this->db->where('is_domestic', $isDomestic ? '1' : '0');
        $this->db->where('active_status', '1');
        $q = $this->db->get('tbl_shipping_flat_rate_service');
        return $q->row();
    }

    function get_shipping_status($order_row_id)
    {
        $sql="SELECT status from tbl_order_shipping_info WHERE order_row_id=".$order_row_id;
        $statusinfo=$this->db->query($sql);
        return $statusinfo->row()->status;

    }
    function modify_status_by_orderid($order_row_id, $data)
    {

          $sql="UPDATE tbl_order_shipping_info
                  SET status=".$this->db->escape($data['status']).",
                      status_update_time =".$this->db->escape($data['status_update_time']).",
                      user_type =".$this->db->escape($data['user_type']).",
                      updated_by =".$this->db->escape($data['updated_by']).",
                      last_modify_time =".$this->db->escape($data['last_modify_time']).",
                      last_status=".$this->db->escape($data['last_status'])."
                  WHERE order_row_id=".$this->db->escape($order_row_id);

            $this->db->query($sql);
            $update_status=$this->db->affected_rows();
    }
    function loadEndiciaInformation($merchant_row_id)
    {
		$carrierInfo = $this->getAllCarrierInfoByMerchant($merchant_row_id);
        if(!empty($carrierInfo))
        {
            foreach($carrierInfo as $carrier)
            {
                switch($carrier->shipping_id)
                {
                    case $this->config->item('shipping_endicia_post'):
                        $data['endiciapost'] = $carrier;
                        break;
                }
            }
        }
        //to prevent warning generation
        $blankInfo = new stdClass();
        $blankInfo->merchant_row_id = $merchant_row_id;
        $blankInfo->use_default = 1;
        $blankInfo->account_info_1 = '';
        $blankInfo->account_info_2 = '';
        $blankInfo->account_info_3 = '';
        $blankInfo->account_info_4 = '';
        $blankInfo->account_info_5 = '';
        $blankInfo->status = 0;
        $blankInfo->server_status = 0;

        if(empty($data['endiciapost']))
        {
            $blankInfo->shipping_id = $this->config->item('shipping_endicia_post');
            $data['endiciapost'] = $blankInfo;
        }
		if($data['endiciapost']->use_default == 0){
			$data['endiciapost']->account_info_2;    //Endicia PassPhrase
		}
		if(!empty($data['endiciapost']))
   		return  $data['endiciapost'];
   		else
   		return NULL;
    }
    function saveEndiciaInformation($merchant_row_id,$data)
    {
    	$sql = 'UPDATE tbl_shipping_carrier_merchant
	                        SET account_info_2 = ' . $this->db->escape($data['account_info_2_new']) . '
	                        WHERE merchant_row_id = ' . $this->db->escape($merchant_row_id) . '
	                        AND shipping_id = ' . $this->db->escape($data['shipping_id']) . ' LIMIT 1';
		//die($sql);
	    $this->db->query($sql);
    } 
    function update_order_endicia_label($order_row_id, $data)
    {
        $sql="UPDATE tbl_order_shipping_info
              SET label_file_name ='',
                  last_modify_time =".$this->db->escape($data['last_modify_time'])."
              WHERE order_row_id  =".$this->db->escape($order_row_id);
        $this->db->query($sql);
        $update_status=$this->db->affected_rows();
        return $update_status;

    }
    
    /*
    *@Anjuman : 03192012 (mmddyy)
    * select comsolidated shipping box size : width, height, length  
    */
    function getConsolidateShippingBoxSize($merchant_row_id)
    {
        if(!empty($merchant_row_id))
        {
            $this->db->select('box_width, box_height, box_length');
            $this->db->where('merchant_row_id', $merchant_row_id);  
            $query = $this->db->get('tbl_cart_basic');
            return $query->row();
        }
    }
    
   //Modified : 03232012
   //Save consolidate shipping box size
    function saveConsolidateShippingBoxSize($merchant_row_id,$data)
    {  
        if(!empty($merchant_row_id))
        {   
            $this->db->where('merchant_row_id', $merchant_row_id);
            $this->db->update('tbl_cart_basic', $data); 
        }
    }
    //End of : Anjuman  
    
    function saveRecurringOrderShippingInfo($data)
    {
        if(!$data['ord_ship_id'] || !$data['merchant_row_id'])
           return false;
           
        $this->db->where('merchant_row_id', $data['merchant_row_id']);
        $this->db->where('ord_ship_id', $data['ord_ship_id']);
        $this->db->update('tbl_recurring_order_shipping_info', $data); 
    }
    
}
?>