BEGIN
	DECLARE v_CurrencyCode_ VARCHAR(50);
	DECLARE v_Round_ int;

	SELECT cr.Symbol INTO v_CurrencyCode_ from tblCurrency cr where cr.CurrencyId =p_CurrencyID;
	SELECT fnGetRoundingPoint(p_CompanyID) INTO v_Round_;

	DROP TEMPORARY TABLE IF EXISTS tmp_Dashboard_user_invoices_;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Dashboard_user_invoices_(
		AssignedUserText varchar(100),
		Revenue decimal(18,8)
	);

	INSERT INTO tmp_Dashboard_user_invoices_
	SELECT
		ac.AccountName  AS `AssignedUserText`,
		SUM(`inv`.`GrandTotal`) AS `Revenue`
	FROM wavetelwholesaleBilling.tblInvoice inv
	INNER JOIN  tblAccount ac 
		ON ac.AccountID = inv.AccountID
	INNER JOIN tblUser tu 
		ON tu.UserID = ac.Owner
	WHERE
		ac.`Status` = 1
	AND tu.`Status` = 1
	AND (p_CurrencyID = '' OR ( p_CurrencyID !=''  AND ac.CurrencyId = p_CurrencyID))
	AND ac.CompanyID = p_CompanyID
	AND (p_OwnerID = '' OR   FIND_IN_SET(tu.UserID,p_OwnerID))
	AND `inv`.`InvoiceType` = 1
	AND (inv.IssueDate BETWEEN p_Start AND p_End)
	AND (( p_ListType = 'Weekly' AND WEEK(inv.IssueDate) = p_WeekOrMonth AND YEAR(inv.IssueDate) = p_Year) OR ( p_ListType = 'Monthly' AND MONTH(inv.IssueDate) = p_WeekOrMonth AND YEAR(inv.IssueDate) = p_Year))
	GROUP BY
		ac.AccountName;

	SELECT	
		td.AssignedUserText,
		ROUND(td.Revenue,v_Round_) AS Revenue,
		v_CurrencyCode_ AS CurrencyCode,
		v_Round_ AS round_number
	FROM  tmp_Dashboard_user_invoices_  td;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetCronJob` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetCronJob`(IN `p_companyid` INT, IN `p_Status` INT, IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(5), IN `p_isExport` INT)
BEGIN

   DECLARE v_OffSet_ int;
   SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
          
	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

    IF p_isExport = 0
    THEN
          
            SELECT
				tblCronJob.JobTitle,
                tblCronJobCommand.Title,
                tblCronJob.Status,
                tblCronJob.CronJobID,
                tblCronJob.CronJobCommandID,
                tblCronJob.Settings
            FROM tblCronJob
            INNER JOIN tblCronJobCommand
                ON tblCronJobCommand.CronJobCommandID = tblCronJob.CronJobCommandID
            WHERE tblCronJob.CompanyID = p_companyid
            AND (p_Status =2 OR tblCronJob.`Status`=p_Status)
            ORDER BY
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TitleDESC') THEN tblCronJobCommand.Title
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TitleASC') THEN tblCronJobCommand.Title
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'StatusDESC') THEN tblCronJob.Status
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'StatusASC') THEN tblCronJob.Status
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'JobTitleDESC') THEN tblCronJob.JobTitle
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'JobTitleASC') THEN tblCronJob.JobTitle
                END ASC
            LIMIT p_RowspPage OFFSET v_OffSet_;

        SELECT
            COUNT(tblCronJob.CronJobID) as totalcount
        FROM tblCronJob
            INNER JOIN tblCronJobCommand
                ON tblCronJobCommand.CronJobCommandID = tblCronJob.CronJobCommandID
            WHERE tblCronJob.CompanyID = p_companyid
            AND (p_Status =2 OR tblCronJob.`Status`=p_Status);
    END IF;

    IF p_isExport = 1
    THEN
        SELECT
			tblCronJob.JobTitle,
            tblCronJobCommand.Title,
            tblCronJob.Status
        FROM tblCronJob
        INNER JOIN tblCronJobCommand
            ON tblCronJobCommand.CronJobCommandID = tblCronJob.CronJobCommandID
        WHERE tblCronJob.CompanyID = p_companyid
		  AND (p_Status =2 OR tblCronJob.`Status`=p_Status);
    END IF;
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetCronJobHistory` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetCronJobHistory`(
	IN `p_CronJobID` INT,
	IN `p_StartDate` DATETIME,
	IN `p_EndDate` DATETIME,
	IN `p_SearchText` VARCHAR(50),
	IN `p_Status` VARCHAR(50),
	IN `p_PageNumber` INT,
	IN `p_RowspPage` INT,
	IN `p_lSortCol` VARCHAR(50),
	IN `p_SortOrder` VARCHAR(5),
	IN `p_isExport` INT


)
BEGIN

   DECLARE v_OffSet_ int;
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
   
       
	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
	
    IF p_isExport = 0
    THEN
            SELECT
                tblCronJobCommand.Title,
                tblCronJobLog.CronJobStatus,
                tblCronJobLog.Message,
                tblCronJobLog.created_at
            FROM tblCronJob
            INNER JOIN tblCronJobLog 
                ON tblCronJob.CronJobID = tblCronJobLog.CronJobID
                AND tblCronJobLog.CronJobID = p_CronJobID
                AND (p_Status = '' OR tblCronJobLog.CronJobStatus = p_Status)
                AND tblCronJobLog.created_at between p_StartDate and p_EndDate
                AND (p_SearchText='' OR tblCronJobLog.Message like Concat('%',p_SearchText,'%'))
            INNER JOIN tblCronJobCommand
                ON tblCronJobCommand.CronJobCommandID = tblCronJob.CronJobCommandID
            ORDER BY
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TitleDESC') THEN tblCronJobCommand.Title
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TitleASC') THEN tblCronJobCommand.Title
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CronJobStatusDESC') THEN tblCronJobLog.CronJobStatus
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CronJobStatusASC') THEN tblCronJobLog.CronJobStatus
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'MessageDESC') THEN tblCronJobLog.Message
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'MessageASC') THEN tblCronJobLog.Message
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'created_atDESC') THEN tblCronJobLog.created_at
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'created_atASC') THEN tblCronJobLog.created_at
                END ASC
           LIMIT p_RowspPage OFFSET v_OffSet_;

        SELECT
            COUNT(tblCronJobLog.CronJobID) as totalcount
        FROM tblCronJob
            INNER JOIN tblCronJobLog 
                ON tblCronJob.CronJobID = tblCronJobLog.CronJobID
                AND tblCronJobLog.CronJobID = p_CronJobID
                AND (p_Status = '' OR tblCronJobLog.CronJobStatus = p_Status)
                AND tblCronJobLog.created_at between p_StartDate and p_EndDate
                AND (p_SearchText='' OR tblCronJobLog.Message like Concat('%',p_SearchText,'%'))
            INNER JOIN tblCronJobCommand
                ON tblCronJobCommand.CronJobCommandID = tblCronJob.CronJobCommandID;
    END IF;

    IF p_isExport = 1
    THEN
        SELECT
            tblCronJobCommand.Title,
            tblCronJobLog.CronJobStatus,
            tblCronJobLog.Message,
            tblCronJobLog.created_at
        FROM tblCronJob
        INNER JOIN tblCronJobLog 
                ON tblCronJob.CronJobID = tblCronJobLog.CronJobID
                AND tblCronJobLog.CronJobID = p_CronJobID
                AND (p_Status = '' OR tblCronJobLog.CronJobStatus = p_Status)
                AND tblCronJobLog.created_at between p_StartDate and p_EndDate
                AND (p_SearchText='' OR tblCronJobLog.Message like Concat('%',p_SearchText,'%'))
        INNER JOIN tblCronjobCommand
            ON tblCronJobCommand.CronJobCommandID = tblCronJob.CronJobCommandID;
    END IF;
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetCronJobSetting` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetCronJobSetting`(IN `p_CronJobID` INT)
BEGIN

	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	select Settings from tblCronJob where CronJobID=p_CronJobID;
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getCustomerCodeRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getCustomerCodeRate`(
	IN `p_AccountID` INT,
	IN `p_trunkID` INT,
	IN `p_RateCDR` INT,
	IN `p_RateMethod` VARCHAR(50),
	IN `p_SpecifyRate` DECIMAL(18,6)
)
BEGIN
	DECLARE v_codedeckid_ INT;
	DECLARE v_ratetableid_ INT;

	SELECT
		CodeDeckId,
		RateTableID
		INTO v_codedeckid_, v_ratetableid_
	FROM tblCustomerTrunk
	WHERE tblCustomerTrunk.TrunkID = p_trunkID
	AND tblCustomerTrunk.AccountID = p_AccountID
	AND tblCustomerTrunk.Status = 1;

	IF p_RateCDR = 0
	THEN 

		DROP TEMPORARY TABLE IF EXISTS tmp_codes_;
		CREATE TEMPORARY TABLE tmp_codes_ (
			RateID INT,
			Code VARCHAR(50),
			INDEX tmp_codes_RateID (`RateID`),
			INDEX tmp_codes_Code (`Code`)
		);
		DROP TEMPORARY TABLE IF EXISTS tmp_codes2_;
		CREATE TEMPORARY TABLE tmp_codes2_ (
			RateID INT,
			Code VARCHAR(50),
			INDEX tmp_codes2_RateID (`RateID`),
			INDEX tmp_codes2_Code (`Code`)
		);
	
		INSERT INTO tmp_codes_
		SELECT
		DISTINCT
			tblRate.RateID,
			tblRate.Code
		FROM tblRate
		INNER JOIN tblCustomerRate
		ON tblCustomerRate.RateID = tblRate.RateID
		WHERE 
			 tblRate.CodeDeckId = v_codedeckid_
		AND CustomerID = p_AccountID
		AND tblCustomerRate.TrunkID = p_trunkID
		AND tblCustomerRate.EffectiveDate <= NOW();
	
		INSERT INTO tmp_codes2_ 
		SELECT * FROM tmp_codes_;
		
		INSERT INTO tmp_codes_
		SELECT
			DISTINCT
			tblRate.RateID,
			tblRate.Code
		FROM tblRate
		INNER JOIN tblRateTableRate
			ON tblRateTableRate.RateID = tblRate.RateID
		LEFT JOIN  tmp_codes2_ c ON c.RateID = tblRate.RateID
		WHERE 
			 tblRate.CodeDeckId = v_codedeckid_
		AND RateTableID = v_ratetableid_
		AND c.RateID IS NULL
		AND tblRateTableRate.EffectiveDate <= NOW();

	END IF;
	
	IF p_RateCDR = 1
	THEN 

		DROP TEMPORARY TABLE IF EXISTS tmp_codes_;
		CREATE TEMPORARY TABLE tmp_codes_ (
			RateID INT,
			Code VARCHAR(50),
			Rate Decimal(18,6),
			ConnectionFee Decimal(18,6),
			Interval1 INT,
			IntervalN INT,
			INDEX tmp_codes_RateID (`RateID`),
			INDEX tmp_codes_Code (`Code`)
		);
		DROP TEMPORARY TABLE IF EXISTS tmp_codes2_;
		CREATE TEMPORARY TABLE tmp_codes2_ (
			RateID INT,
			Code VARCHAR(50),
			Rate Decimal(18,6),
			ConnectionFee Decimal(18,6),
			Interval1 INT,
			IntervalN INT,
			INDEX tmp_codes2_RateID (`RateID`),
			INDEX tmp_codes2_Code (`Code`)
		);
	
		INSERT INTO tmp_codes_
		SELECT
		DISTINCT
			tblRate.RateID,
			tblRate.Code,
			tblCustomerRate.Rate,
			tblCustomerRate.ConnectionFee,
			tblCustomerRate.Interval1,
			tblCustomerRate.IntervalN
		FROM tblRate
		INNER JOIN tblCustomerRate
		ON tblCustomerRate.RateID = tblRate.RateID
		WHERE 
			 tblRate.CodeDeckId = v_codedeckid_
		AND CustomerID = p_AccountID
		AND tblCustomerRate.TrunkID = p_trunkID
		AND tblCustomerRate.EffectiveDate <= NOW();
	
		INSERT INTO tmp_codes2_ 
		SELECT * FROM tmp_codes_;
		
		INSERT INTO tmp_codes_
		SELECT
			DISTINCT
			tblRate.RateID,
			tblRate.Code,
			tblRateTableRate.Rate,
			tblRateTableRate.ConnectionFee,
			tblRateTableRate.Interval1,
			tblRateTableRate.IntervalN
		FROM tblRate
		INNER JOIN tblRateTableRate
			ON tblRateTableRate.RateID = tblRate.RateID
		LEFT JOIN  tmp_codes2_ c ON c.RateID = tblRate.RateID
		WHERE 
			 tblRate.CodeDeckId = v_codedeckid_
		AND RateTableID = v_ratetableid_
		AND c.RateID IS NULL
		AND tblRateTableRate.EffectiveDate <= NOW();
		
		
		
		IF p_RateMethod = 'SpecifyRate'
		THEN
		
			UPDATE tmp_codes_ SET Rate=p_SpecifyRate;
			
		END IF;

	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getCustomerInboundRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getCustomerInboundRate`(
	IN `p_AccountID` INT,
	IN `p_RateCDR` INT,
	IN `p_RateMethod` VARCHAR(50),
	IN `p_SpecifyRate` DECIMAL(18,6),
	IN `p_CLD` VARCHAR(500)
)
BEGIN

	DECLARE v_inboundratetableid_ INT;

	IF p_CLD != ''
	THEN

		SELECT
			RateTableID INTO v_inboundratetableid_
		FROM tblCLIRateTable
		WHERE AccountID = p_AccountID AND CLI = p_CLD;

	ELSE

		SELECT
			InboudRateTableID INTO v_inboundratetableid_
		FROM tblAccount
		WHERE AccountID = p_AccountID;

	END IF;
	
	IF p_RateCDR = 1
	THEN 

		DROP TEMPORARY TABLE IF EXISTS tmp_inboundcodes_;
		CREATE TEMPORARY TABLE tmp_inboundcodes_ (
			RateID INT,
			Code VARCHAR(50),
			Rate Decimal(18,6),
			ConnectionFee Decimal(18,6),
			Interval1 INT,
			IntervalN INT,
			INDEX tmp_inboundcodes_RateID (`RateID`),
			INDEX tmp_inboundcodes_Code (`Code`)
		);
		INSERT INTO tmp_inboundcodes_
		SELECT
			DISTINCT
			tblRate.RateID,
			tblRate.Code,
			tblRateTableRate.Rate,
			tblRateTableRate.ConnectionFee,
			tblRateTableRate.Interval1,
			tblRateTableRate.IntervalN
		FROM tblRate
		INNER JOIN tblRateTableRate
			ON tblRateTableRate.RateID = tblRate.RateID
		WHERE RateTableID = v_inboundratetableid_
		AND tblRateTableRate.EffectiveDate <= NOW();

		
		IF p_RateMethod = 'SpecifyRate'
		THEN

			UPDATE tmp_inboundcodes_ SET Rate=p_SpecifyRate;

		END IF;

	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetCustomerRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetCustomerRate`(IN `p_companyid` INT, IN `p_AccountID` INT, IN `p_trunkID` INT, IN `p_contryID` INT, IN `p_code` VARCHAR(50), IN `p_description` VARCHAR(50), IN `p_Effective` VARCHAR(50), IN `p_effectedRates` INT, IN `p_RoutinePlan` INT, IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(5), IN `p_isExport` INT )
BEGIN
   DECLARE v_codedeckid_ INT;
   DECLARE v_ratetableid_ INT;
   DECLARE v_RateTableAssignDate_ DATETIME;
   DECLARE v_NewA2ZAssign_ INT;
   DECLARE v_OffSet_ int;
   DECLARE v_IncludePrefix_ INT;
   DECLARE v_Prefix_ VARCHAR(50);
   DECLARE v_RatePrefix_ VARCHAR(50);
   DECLARE v_AreaPrefix_ VARCHAR(50);
   
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
   SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

    SELECT
        CodeDeckId,
        RateTableID,
        RateTableAssignDate,IncludePrefix INTO v_codedeckid_, v_ratetableid_, v_RateTableAssignDate_,v_IncludePrefix_
    FROM tblCustomerTrunk
    WHERE CompanyID = p_companyid
    AND tblCustomerTrunk.TrunkID = p_trunkID
    AND tblCustomerTrunk.AccountID = p_AccountID
    AND tblCustomerTrunk.Status = 1;
    
    SELECT
        Prefix,RatePrefix,AreaPrefix INTO v_Prefix_,v_RatePrefix_,v_AreaPrefix_
    FROM tblTrunk
    WHERE CompanyID = p_companyid
    AND tblTrunk.TrunkID = p_trunkID
    AND tblTrunk.Status = 1;
    
    

    DROP TEMPORARY TABLE IF EXISTS tmp_CustomerRates_;
    CREATE TEMPORARY TABLE tmp_CustomerRates_ (
        RateID INT,
        Interval1 INT,
        IntervalN  INT,
        Rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        EffectiveDate DATE,
        LastModifiedDate DATETIME,
        LastModifiedBy VARCHAR(50),
        CustomerRateId INT,
        RateTableRateID INT,
        TrunkID INT,
        RoutinePlan INT,
        INDEX tmp_CustomerRates__RateID (`RateID`),
        INDEX tmp_CustomerRates__TrunkID (`TrunkID`),
        INDEX tmp_CustomerRates__EffectiveDate (`EffectiveDate`)
    );
    DROP TEMPORARY TABLE IF EXISTS tmp_RateTableRate_;
    CREATE TEMPORARY TABLE tmp_RateTableRate_ (
        RateID INT,
        Interval1 INT,
        IntervalN INT,
        Rate DECIMAL(18, 6),
        ConnectionFee DECIMAL(18, 6),
        EffectiveDate DATE,
        LastModifiedDate DATETIME,
        LastModifiedBy VARCHAR(50),
        CustomerRateId INT,
        RateTableRateID INT,
        TrunkID INT,
        INDEX tmp_RateTableRate__RateID (`RateID`),
        INDEX tmp_RateTableRate__TrunkID (`TrunkID`),
        INDEX tmp_RateTableRate__EffectiveDate (`EffectiveDate`)

    );
    
    DROP TEMPORARY TABLE IF EXISTS tmp_customerrate_;
    CREATE TEMPORARY TABLE tmp_customerrate_ (
        RateID INT,
        Code VARCHAR(50),
        Description VARCHAR(200),
        Interval1 INT,
        IntervalN INT,
        ConnectionFee DECIMAL(18, 6),
        RoutinePlanName VARCHAR(50),
        Rate DECIMAL(18, 6),
        EffectiveDate DATE,
        LastModifiedDate DATETIME,
        LastModifiedBy VARCHAR(50),
        CustomerRateId INT,
        TrunkID INT,
        RateTableRateId INT,
        IncludePrefix TINYINT,
        Prefix VARCHAR(50),
        RatePrefix VARCHAR(50),
        AreaPrefix VARCHAR(50)
    );
     

    INSERT INTO tmp_CustomerRates_

            SELECT
                tblCustomerRate.RateID,
                tblCustomerRate.Interval1,
                tblCustomerRate.IntervalN,
                
                tblCustomerRate.Rate,
                tblCustomerRate.ConnectionFee,
                tblCustomerRate.EffectiveDate,
                tblCustomerRate.LastModifiedDate,
                tblCustomerRate.LastModifiedBy,
                tblCustomerRate.CustomerRateId,
                NULL AS RateTableRateID,
                p_trunkID as TrunkID,
                tblCustomerRate.RoutinePlan
                     
            FROM tblCustomerRate
            INNER JOIN tblRate
                ON tblCustomerRate.RateID = tblRate.RateID
            WHERE (p_contryID IS NULL OR tblRate.CountryID = p_contryID)
            AND (p_code IS NULL OR Code LIKE REPLACE(p_code, '*', '%'))
            AND (p_description IS NULL OR tblRate.Description LIKE REPLACE(p_description, '*', '%'))
            AND (tblRate.CompanyID = p_companyid)
            AND tblRate.CodeDeckId = v_codedeckid_
            AND tblCustomerRate.TrunkID = p_trunkID
            AND (p_RoutinePlan = 0 or tblCustomerRate.RoutinePlan = p_RoutinePlan)
            AND CustomerID = p_AccountID
             
            ORDER BY
                tblCustomerRate.TrunkId, tblCustomerRate.CustomerId,tblCustomerRate.RateID,tblCustomerRate.EffectiveDate DESC;
         
    	
		 
		
                
            
    	
	 	DROP TEMPORARY TABLE IF EXISTS tmp_CustomerRates4_;
			CREATE TEMPORARY TABLE IF NOT EXISTS tmp_CustomerRates4_ as (select * from tmp_CustomerRates_);	        
			DELETE n1 FROM tmp_CustomerRates_ n1, tmp_CustomerRates4_ n2 WHERE n1.EffectiveDate < n2.EffectiveDate 
			AND n1.TrunkID = n2.TrunkID
			AND  n1.RateID = n2.RateID
			AND  n1.EffectiveDate <= NOW()
			AND  n2.EffectiveDate <= NOW();
	 	
	 	DROP TEMPORARY TABLE IF EXISTS tmp_CustomerRates2_;
		CREATE TEMPORARY TABLE IF NOT EXISTS tmp_CustomerRates2_ as (select * from tmp_CustomerRates_);
		DROP TEMPORARY TABLE IF EXISTS tmp_CustomerRates3_;
	   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_CustomerRates3_ as (select * from tmp_CustomerRates_);
	   DROP TEMPORARY TABLE IF EXISTS tmp_CustomerRates5_;
	   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_CustomerRates5_ as (select * from tmp_CustomerRates_);

    INSERT INTO tmp_RateTableRate_
            SELECT
                tblRateTableRate.RateID,
                tblRateTableRate.Interval1,
                tblRateTableRate.IntervalN,
                tblRateTableRate.Rate,
                tblRateTableRate.ConnectionFee,
            	 
      			 tblRateTableRate.EffectiveDate,
                NULL AS LastModifiedDate,
                NULL AS LastModifiedBy,
                NULL AS CustomerRateId,
                tblRateTableRate.RateTableRateID,
                p_trunkID as TrunkID
            FROM tblRateTableRate
            INNER JOIN tblRate
                ON tblRateTableRate.RateID = tblRate.RateID
            WHERE (p_contryID IS NULL OR tblRate.CountryID = p_contryID)
            AND (p_code IS NULL OR Code LIKE REPLACE(p_code, '*', '%'))
            AND (p_description IS NULL OR tblRate.Description LIKE REPLACE(p_description, '*', '%'))
            AND (tblRate.CompanyID = p_companyid)
            AND tblRate.CodeDeckId = v_codedeckid_
            AND RateTableID = v_ratetableid_
            AND (
						(
							(SELECT count(*) from tmp_CustomerRates2_ cr where cr.RateID = tblRateTableRate.RateID) >0 	
							AND tblRateTableRate.EffectiveDate < 
								( SELECT MIN(cr.EffectiveDate)
                          FROM tmp_CustomerRates_ as cr
                          WHERE cr.RateID = tblRateTableRate.RateID
								)
							AND (SELECT count(*) from tmp_CustomerRates5_ cr where cr.RateID = tblRateTableRate.RateID AND cr.EffectiveDate <= NOW() ) = 0  
						)
						or  (  SELECT count(*) from tmp_CustomerRates3_ cr where cr.RateID = tblRateTableRate.RateID ) = 0
					)
            
                ORDER BY tblRateTableRate.RateTableId,tblRateTableRate.RateID,tblRateTableRate.effectivedate DESC;
            
	 
		 	
		  
		  DROP TEMPORARY TABLE IF EXISTS tmp_RateTableRate4_;
		  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_RateTableRate4_ as (select * from tmp_RateTableRate_);	        
        DELETE n1 FROM tmp_RateTableRate_ n1, tmp_RateTableRate4_ n2 WHERE n1.EffectiveDate < n2.EffectiveDate 
	 	  AND n1.TrunkID = n2.TrunkID
		  AND  n1.RateID = n2.RateID
		  AND  n1.EffectiveDate <= NOW()
		  AND  n2.EffectiveDate <= NOW();
		 

		
	   
	   
		  INSERT INTO tmp_customerrate_
        SELECT
        			 r.RateID,
                r.Code,
                r.Description,
                CASE WHEN allRates.Interval1 IS NULL
                THEN 
                    r.Interval1
                ELSE
                    allRates.Interval1
                END as Interval1,
                CASE WHEN allRates.IntervalN IS NULL
                THEN 
                    r.IntervalN
                ELSE
                    allRates.IntervalN
                END  IntervalN,
                allRates.ConnectionFee,
                allRates.RoutinePlanName,
                allRates.Rate,
                allRates.EffectiveDate,
                allRates.LastModifiedDate,
                allRates.LastModifiedBy,
                allRates.CustomerRateId,
                p_trunkID as TrunkID,
                allRates.RateTableRateId,
					v_IncludePrefix_ as IncludePrefix ,
   	         CASE  WHEN tblTrunk.TrunkID is not null
               THEN
               	tblTrunk.Prefix
               ELSE
               	v_Prefix_
					END AS Prefix,
					CASE  WHEN tblTrunk.TrunkID is not null
               THEN
               	tblTrunk.RatePrefix
               ELSE
               	v_RatePrefix_
					END AS RatePrefix,
					CASE  WHEN tblTrunk.TrunkID is not null
               THEN
               	tblTrunk.AreaPrefix
               ELSE
               	v_AreaPrefix_
					END AS AreaPrefix
        FROM tblRate r
        LEFT JOIN (SELECT
                CustomerRates.RateID,
                CustomerRates.Interval1,
                CustomerRates.IntervalN,
                tblTrunk.Trunk as RoutinePlanName,
                CustomerRates.ConnectionFee,
                CustomerRates.Rate,
                CustomerRates.EffectiveDate,
                CustomerRates.LastModifiedDate,
                CustomerRates.LastModifiedBy,
                CustomerRates.CustomerRateId,
                NULL AS RateTableRateID,
                p_trunkID as TrunkID,
                RoutinePlan
            FROM tmp_CustomerRates_ CustomerRates
            LEFT JOIN tblTrunk on tblTrunk.TrunkID = CustomerRates.RoutinePlan
            WHERE 
                (
					 	( p_Effective = 'Now' AND CustomerRates.EffectiveDate <= NOW() )
					 	OR
					 	( p_Effective = 'Future' AND CustomerRates.EffectiveDate > NOW())
					 	OR p_Effective = 'All'
					 )


            UNION ALL

            SELECT
            DISTINCT
                rtr.RateID,
                rtr.Interval1,
                rtr.IntervalN,
                NULL,
                rtr.ConnectionFee,
                rtr.Rate,
                rtr.EffectiveDate,
                NULL,
                NULL,
                NULL AS CustomerRateId,
                rtr.RateTableRateID,
                p_trunkID as TrunkID,
                NULL AS RoutinePlan
            FROM tmp_RateTableRate_ AS rtr
            LEFT JOIN tmp_CustomerRates2_ as cr
                ON cr.RateID = rtr.RateID AND 
						 (
						 	( p_Effective = 'Now' AND cr.EffectiveDate <= NOW() )
						 	OR
						 	( p_Effective = 'Future' AND cr.EffectiveDate > NOW())
						 	OR p_Effective = 'All'
						 )
            WHERE (
                (
                    p_Effective = 'Now' AND rtr.EffectiveDate <= NOW()
                    AND (
                            (cr.RateID IS NULL) 
                            OR
                            (cr.RateID IS NOT NULL AND rtr.RateTableRateID IS NULL) 
                        )

                )
                OR
                ( p_Effective = 'Future' AND rtr.EffectiveDate > NOW()
                    AND (
                            (cr.RateID IS NULL) 
                            OR
                            (
                                cr.RateID IS NOT NULL AND rtr.EffectiveDate < (
                                                                                SELECT IFNULL(MIN(crr.EffectiveDate), rtr.EffectiveDate)
                                                                                FROM tmp_CustomerRates3_ as crr
                                                                                WHERE crr.RateID = rtr.RateID
                                                                                )
                            )
                        )
                )
            OR p_Effective = 'All'

            )
				
				) allRates
            ON allRates.RateID = r.RateID
         LEFT JOIN tblTrunk on tblTrunk.TrunkID = RoutinePlan
        WHERE (p_contryID IS NULL OR r.CountryID = p_contryID)
        AND (p_code IS NULL OR Code LIKE REPLACE(p_code, '*', '%'))
        AND (p_description IS NULL OR r.Description LIKE REPLACE(p_description, '*', '%'))
        AND (r.CompanyID = p_companyid)
        AND r.CodeDeckId = v_codedeckid_
        AND  ((p_effectedRates = 1 AND Rate IS NOT NULL) OR  (p_effectedRates = 0));

   
    

    IF p_isExport = 0
    THEN


         SELECT
                RateID,
                Code,
                Description,
                Interval1,
                IntervalN,
                ConnectionFee,
                RoutinePlanName,
                Rate,
                EffectiveDate,
                LastModifiedDate,
                LastModifiedBy,
                CustomerRateId,
                TrunkID,
                RateTableRateId
            FROM tmp_customerrate_ 
            ORDER BY
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CodeDESC') THEN Code
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CodeASC') THEN Code
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionDESC') THEN Description
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionASC') THEN Description
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RateDESC') THEN Rate
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RateASC') THEN Rate
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'Interval1DESC') THEN Interval1
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'Interval1ASC') THEN Interval1
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'IntervalNDESC') THEN IntervalN
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'IntervalNASC') THEN IntervalN
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ConnectionFeeDESC') THEN ConnectionFee
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ConnectionFeeASC') THEN ConnectionFee
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EffectiveDateDESC') THEN EffectiveDate
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EffectiveDateASC') THEN EffectiveDate
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'LastModifiedDateDESC') THEN LastModifiedDate
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'LastModifiedDateASC') THEN LastModifiedDate
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'LastModifiedByDESC') THEN LastModifiedBy
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'LastModifiedByASC') THEN LastModifiedBy
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CustomerRateIdDESC') THEN CustomerRateId
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CustomerRateIdASC') THEN CustomerRateId
                END ASC
            LIMIT p_RowspPage OFFSET v_OffSet_;

        SELECT
            COUNT(RateID) AS totalcount
        FROM tmp_customerrate_;

    END IF;

    IF p_isExport = 1
    THEN
			 
          select 
            Code,
            Description,
            Interval1,
            IntervalN,
            ConnectionFee,
            Rate,
            EffectiveDate,
            LastModifiedDate,
            LastModifiedBy from tmp_customerrate_;

    END IF;
    
    
	IF p_isExport = 2
    THEN
			 
          select 
            Code,
            Description,
            Interval1,
            IntervalN,
            ConnectionFee,
            Rate,
            EffectiveDate from tmp_customerrate_;

    END IF;
    
 
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetDashboardDataJobs` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetDashboardDataJobs`(IN `p_companyId` int , IN `p_userId` int , IN `p_isadmin` int)
BEGIN
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	
	SELECT `tblJob`.`JobID`, `tblJob`.`Title`, `tblJobStatus`.`Title` AS `Status`, `tblJob`.`HasRead`, `tblJob`.`CreatedBy`, `tblJob`.`created_at`
	FROM tblJob INNER JOIN tblJobStatus ON `tblJob`.`JobStatusID` = `tblJobStatus`.`JobStatusID` INNER JOIN tblJobType ON `tblJob`.`JobTypeID` = `tblJobType`.`JobTypeID`
	WHERE `tblJob`.`CompanyID` = p_companyId AND ( p_isadmin = 1 OR (p_isadmin = 0 AND tblJob.JobLoggedUserID = p_userId) )
	ORDER BY `tblJob`.`ShowInCounter` DESC, `tblJob`.`updated_at` DESC,tblJob.JobID DESC
	LIMIT 10;


	
	SELECT count(*) AS totalpending FROM tblJob INNER JOIN tblJobStatus ON `tblJob`.`JobStatusID` = `tblJobStatus`.`JobStatusID`
	WHERE `tblJobStatus`.`Title` = 'Pending' AND `tblJob`.`CompanyID` = p_companyId AND
	( p_isadmin = 1 OR ( p_isadmin = 0 AND tblJob.JobLoggedUserID = p_userId) );
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetDashboardDataRecentDueRateSheet` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetDashboardDataRecentDueRateSheet`(IN `p_companyId` int)
BEGIN
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
        SELECT count(*) as TotalDueCustomer,DAYSDIFF from (
			SELECT distinct  a.AccountName,a.AccountID,cr.TrunkID,cr.EffectiveDate,TIMESTAMPDIFF(DAY,cr.EffectiveDate, NOW()) as DAYSDIFF
			FROM tblCustomerRate cr
			INNER JOIN tblRate  r on r.RateID =cr.RateID
			INNER JOIN tblCountry c on c.CountryID = r.CountryID
			INNER JOIN tblAccount a on a.AccountID = cr.CustomerID
			WHERE a.CompanyId =@companyId
			AND TIMESTAMPDIFF(DAY, cr.EffectiveDate, DATE_FORMAT (NOW(),120)) BETWEEN -1 AND 1

		) as tbl
		LEFT JOIN tblJob ON tblJob.AccountID = tbl.AccountID
					AND tblJob.JobID IN(select JobID from tblJob where JobTypeID = (select JobTypeID from tblJobType where Code='CD'))
					AND FIND_IN_SET (tbl.TrunkID,JSON_EXTRACT(tblJob.Options, '$.Trunks')) != 0

					AND ( TIMESTAMPDIFF(DAY, tblJob.created_at, NOW()) = DAYSDIFF or TIMESTAMPDIFF(DAY, tblJob.created_at, EffectiveDate)  BETWEEN -1 AND 0)

		where JobStatusID is null or JobStatusID <> (select JobStatusID from tblJobStatus where Code='S')
		group by DAYSDIFF
		order by DAYSDIFF desc;

 
        

        SELECT count(*) as TotalDueVendor ,DAYSDIFF from (
				SELECT  DISTINCT a.AccountName,a.AccountID,vr.TrunkID,vr.EffectiveDate,TIMESTAMPDIFF(DAY,vr.EffectiveDate, NOW()) as DAYSDIFF
			FROM tblVendorRate vr
			INNER JOIN tblRate  r on r.RateID =vr.RateID
			INNER JOIN tblCountry c on c.CountryID = r.CountryID
			INNER JOIN tblAccount a on a.AccountID = vr.AccountId
			WHERE a.CompanyId = @companyId
			AND TIMESTAMPDIFF(DAY,vr.EffectiveDate, NOW()) BETWEEN -1 AND 1
	   ) as tbl
		LEFT JOIN tblJob ON tblJob.AccountID = tbl.AccountID
					AND tblJob.JobID IN(select JobID from tblJob where JobTypeID = (select JobTypeID from tblJobType where Code='VD'))
					AND FIND_IN_SET (tbl.TrunkID,JSON_EXTRACT(tblJob.Options, '$.Trunks')) != 0
					AND ( TIMESTAMPDIFF(DAY, tblJob.created_at, NOW()) = DAYSDIFF or TIMESTAMPDIFF(DAY, tblJob.created_at, EffectiveDate)  BETWEEN -1 AND 0)

		where JobStatusID is null or JobStatusID <> (select JobStatusID from tblJobStatus where Code='S')
		group by DAYSDIFF
		order by DAYSDIFF desc;
		
		SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetDashboardProcessedFiles` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetDashboardProcessedFiles`(IN `p_companyId` int , IN `p_userId` int , IN `p_isadmin` int)
BEGIN
    select `tblJob`.`JobID`, `tblJob`.`Title`, `tblJobStatus`.`Title` as Status, `tblJob`.`CreatedBy`, `tblJob`.`created_at` from tblJob inner join tblJobStatus on `tblJob`.`JobStatusID` = `tblJobStatus`.`JobStatusID` inner join tblJobType on `tblJob`.`JobTypeID` = `tblJobType`.`JobTypeID`
        where ( tblJobStatus.Title = 'Success' OR tblJobStatus.Title = 'Completed' ) and `tblJob`.`CompanyID` = p_companyId and
        ( p_isadmin = 1 OR ( p_isadmin = 0 and `tblJob`.`JobLoggedUserID` = p_userId))  and `tblJob`.`updated_at` >= DATE_ADD(NOW(),INTERVAL -7 DAY)
		  LIMIT 10;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetDashboardRecentAccounts` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetDashboardRecentAccounts`(
	IN `p_companyId` int ,
	IN `p_userId` VARCHAR(500),
	IN `p_AccountManager` INT

)
BEGIN
    select  AccountID,AccountName,Phone,Email,created_by,created_at from tblAccount
          where
          `AccountType` = '1' and CompanyID = p_companyId and `Status` = '1'
          AND (  p_AccountManager = 0 OR (p_AccountManager = 1 AND find_in_set(tblAccount.Owner,p_userId)) )
    order by `tblAccount`.`AccountID` desc
	 LIMIT 10;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetDashboardRecentLeads` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetDashboardRecentLeads`(IN `p_companyId` int)
BEGIN
    select AccountID,AccountName,Phone,Email,created_by,created_at from tblAccount where (`AccountType` = '0' and `CompanyID` = p_companyId and `Status` = '1') order by `tblAccount`.`AccountID` desc LIMIT 10;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getDefaultCodes` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getDefaultCodes`(IN `p_CompanyID` INT)
BEGIN

	DROP TEMPORARY TABLE IF EXISTS tmp_codes_;
	CREATE TEMPORARY TABLE tmp_codes_ (
		RateID INT,
		Code VARCHAR(50),
		INDEX tmp_codes_RateID (`RateID`),
		INDEX tmp_codes_Code (`Code`)
	);

	INSERT INTO tmp_codes_
	SELECT
	DISTINCT
		tblRate.RateID,
		tblRate.Code
	FROM tblRate
	INNER JOIN tblCodeDeck
		ON tblCodeDeck.CodeDeckId = tblRate.CodeDeckId
	WHERE tblCodeDeck.CompanyId = p_CompanyID
	AND tblCodeDeck.DefaultCodedeck = 1 ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getDestinationCode` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getDestinationCode`(IN `p_DestinationGroupSetID` INT, IN `p_DestinationGroupID` INT, IN `p_CountryID` INT, IN `p_Code` VARCHAR(50), IN `p_Selected` INT, IN `p_Description` VARCHAR(50), IN `p_PageNumber` INT, IN `p_RowspPage` INT)
BEGIN
		
	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	
	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
	
	SELECT
		r.RateID,
		r.Code,
		r.Description,
		IF(DestinationGroupCodeID >0,1,0) as DestinationGroupCodeID
	FROM tblRate r
	INNER JOIN tblDestinationGroupSet dg
		ON dg.CodedeckID =r.CodeDeckId
	LEFT JOIN tblDestinationGroupCode dgc
		ON DestinationGroupID = p_DestinationGroupID
		AND dgc.RateID = r.RateID
	WHERE dg.DestinationGroupSetID = p_DestinationGroupSetID
	AND (p_Code = '' OR Code LIKE REPLACE(p_Code, '*', '%'))
	AND (p_Description = '' OR Description LIKE REPLACE(p_Description, '*', '%'))
	AND (p_CountryID = 0 OR r.CountryID = p_CountryID)
	AND (p_Selected = 0 OR (p_Selected = 1 AND DestinationGroupCodeID IS NOT NULL))
	LIMIT p_RowspPage OFFSET v_OffSet_;
	
	SELECT COUNT(*)  as totalcount
	FROM tblRate r
	INNER JOIN tblDestinationGroupSet dg
		ON dg.CodedeckID =r.CodeDeckId
	LEFT JOIN tblDestinationGroupCode dgc
		ON DestinationGroupID = p_DestinationGroupID
		AND dgc.RateID = r.RateID
	WHERE dg.DestinationGroupSetID = p_DestinationGroupSetID
	AND (p_Code = '' OR Code LIKE REPLACE(p_Code, '*', '%'))
	AND (p_Description = '' OR Description LIKE REPLACE(p_Description, '*', '%'))
	AND (p_CountryID = 0 OR r.CountryID = p_CountryID)
	AND (p_Selected = 0 OR (p_Selected = 1 AND DestinationGroupCodeID IS NOT NULL)); 

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getDestinationGroup` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getDestinationGroup`(IN `p_CompanyID` INT, IN `p_DestinationGroupSetID` INT, IN `p_Name` VARCHAR(50), IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(5), IN `p_isExport` INT)
BEGIN
	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

	IF p_isExport = 0
	THEN
		SELECT   
			dg.Name,
			CONCAT(SUBSTRING_INDEX(GROUP_CONCAT(r.Code ORDER BY r.Code ASC SEPARATOR ','), ',', 10),'...') as Code,
			dg.CreatedBy,
			dg.created_at,
			dg.DestinationGroupID,
			dg.DestinationGroupSetID
		FROM tblDestinationGroup dg
		LEFT JOIN tblDestinationGroupCode dgc
		ON dg.DestinationGroupID =  dgc.DestinationGroupID
		LEFT JOIN tblRate r 
			ON r.RateID = dgc.RateID
		WHERE dg.DestinationGroupSetID = p_DestinationGroupSetID
			AND (p_Name ='' OR dg.Name like  CONCAT('%',p_Name,'%'))
		GROUP BY dg.DestinationGroupID
		ORDER BY
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'NameDESC') THEN dg.Name
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'NameASC') THEN dg.Name
			END ASC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CreatedByDESC') THEN dg.CreatedBy
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CreatedByASC') THEN dg.CreatedBy
			END ASC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'created_atDESC') THEN dg.created_at
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'created_atASC') THEN dg.created_at
			END ASC
		LIMIT p_RowspPage OFFSET v_OffSet_;

		SELECT
			COUNT(DISTINCT dg.DestinationGroupID) AS totalcount
		FROM tblDestinationGroup dg
		LEFT JOIN tblDestinationGroupCode dgc
		ON dg.DestinationGroupID =  dgc.DestinationGroupID
		LEFT JOIN tblRate r 
			ON r.RateID = dgc.RateID
		WHERE dg.DestinationGroupSetID = p_DestinationGroupSetID
			AND (p_Name ='' OR dg.Name like  CONCAT('%',p_Name,'%'));
	END IF;

	IF p_isExport = 1
	THEN
		
		SELECT
			dg.Name as `Destionatio Group Name`,
			GROUP_CONCAT(r.Code ORDER BY r.Code ASC) as `Destination Codes`,
			dg.CreatedBy,
			dg.created_at
		FROM tblDestinationGroup dg
		LEFT JOIN tblDestinationGroupCode dgc
			ON dg.DestinationGroupID =  dgc.DestinationGroupID
		LEFT JOIN tblRate r 
			ON r.RateID = dgc.RateID
		WHERE dg.DestinationGroupSetID = p_DestinationGroupSetID
			AND (p_Name ='' OR dg.Name like  CONCAT('%',p_Name,'%'))
		GROUP BY dg.DestinationGroupID;

	END IF;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getDestinationGroupSet` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getDestinationGroupSet`(IN `p_CompanyID` INT, IN `p_Name` VARCHAR(50), IN `p_CodedeckID` INT, IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(5), IN `p_isExport` INT)
BEGIN
	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

	IF p_isExport = 0
	THEN
		SELECT   
			dgs.Name,
			cd.CodeDeckName,
			dgs.CreatedBy,
			dgs.created_at,
			dgs.DestinationGroupSetID,
			dgs.CodedeckID,
			dgs.CompanyID,
			(SELECT adp.DiscountPlanID FROM tblDiscountPlan dp  LEFT JOIN tblAccountDiscountPlan adp ON adp.DiscountPlanID = dp.DiscountPlanID WHERE dp.DestinationGroupSetID = dgs.DestinationGroupSetID LIMIT 1) as Applied
		FROM tblDestinationGroupSet dgs
		INNER JOIN tblCodeDeck cd 
			ON cd.CodeDeckId = dgs.CodedeckID
		WHERE dgs.CompanyID = p_CompanyID
			AND (p_Name ='' OR dgs.Name like  CONCAT('%',p_Name,'%'))
			AND (p_CodedeckID = 0 OR dgs.CodedeckID = p_CodedeckID)
		ORDER BY
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'NameDESC') THEN dgs.Name
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'NameASC') THEN dgs.Name
			END ASC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CreatedByDESC') THEN dgs.CreatedBy
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CreatedByASC') THEN dgs.CreatedBy
			END ASC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'created_atDESC') THEN dgs.created_at
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'created_atASC') THEN dgs.created_at
			END ASC
		LIMIT p_RowspPage OFFSET v_OffSet_;

		SELECT
			COUNT(dgs.DestinationGroupSetID) AS totalcount
		FROM tblDestinationGroupSet dgs
		INNER JOIN tblCodeDeck cd 
			ON cd.CodeDeckId = dgs.CodedeckID
		WHERE dgs.CompanyID = p_CompanyID
			AND (p_Name ='' OR dgs.Name like  CONCAT('%',p_Name,'%'))
			AND (p_CodedeckID = 0 OR dgs.CodedeckID = p_CodedeckID);
	END IF;

	IF p_isExport = 1
	THEN
		
		SELECT   
			dgs.Name,
			dgs.CreatedBy,
			dgs.created_at
		FROM tblDestinationGroupSet dgs
		INNER JOIN tblCodeDeck cd 
			ON cd.CodeDeckId = dgs.CodedeckID
		WHERE dgs.CompanyID = p_CompanyID
			AND (p_Name ='' OR dgs.Name like  CONCAT('%',p_Name,'%'))
			AND (p_CodedeckID = 0 OR dgs.CodedeckID = p_CodedeckID);

	END IF;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetDialStrings` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetDialStrings`(IN `p_dialstringid` int, IN `p_dialstring` varchar(250), IN `p_chargecode` varchar(250), IN `p_description` varchar(250), IN `p_forbidden` INT, IN `p_PageNumber` int, IN `p_RowspPage` int, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(5), IN `p_isExport` int )
BEGIN

   DECLARE v_OffSet_ int;
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	           
	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
    

    IF p_isExport = 0
    THEN


        SELECT
            DialStringCodeID,
            DialString,
            ChargeCode,
            Description,
            Forbidden
        FROM tblDialStringCode
        WHERE  (DialStringID = p_dialstringid)
         AND (Forbidden = p_forbidden)
			AND (p_dialstring IS NULL OR DialString LIKE REPLACE(p_dialstring, '*', '%'))
            AND (p_chargecode IS NULL OR ChargeCode LIKE REPLACE(p_chargecode, '*', '%'))
            AND (p_description IS NULL OR Description LIKE REPLACE(p_description, '*', '%'))            
        ORDER BY
            CASE
                WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DialStringDESC') THEN DialString
            END DESC,
            CASE
                WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DialStringASC') THEN DialString
            END ASC,
            CASE
                WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ChargeCodeDESC') THEN ChargeCode
            END DESC,
            CASE
                WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ChargeCodeASC') THEN ChargeCode
            END ASC,
            CASE
                WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionDESC') THEN Description
            END DESC,
            CASE
                WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionASC') THEN Description
            END ASC,
            CASE
                WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ForbiddenDESC') THEN Forbidden
            END DESC,
            CASE
                WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ForbiddenASC') THEN Forbidden
            END ASC
        LIMIT p_RowspPage OFFSET v_OffSet_;

        SELECT
            COUNT(DialStringCodeID) AS totalcount
        FROM tblDialStringCode
        WHERE  (DialStringID = p_dialstringid)
         AND (Forbidden = p_forbidden) 
			AND (p_dialstring IS NULL OR DialString LIKE REPLACE(p_dialstring, '*', '%'))
            AND (p_chargecode IS NULL OR ChargeCode LIKE REPLACE(p_chargecode, '*', '%'))
            AND (p_description IS NULL OR Description LIKE REPLACE(p_description, '*', '%'));

    END IF;

    IF p_isExport = 1
    THEN

        SELECT
            DialString,
            ChargeCode,
            Description,
            Forbidden
        FROM tblDialStringCode
        WHERE  (DialStringID = p_dialstringid)
         AND (Forbidden = p_forbidden)
			AND (p_dialstring IS NULL OR DialString LIKE REPLACE(p_dialstring, '*', '%'))
            AND (p_chargecode IS NULL OR ChargeCode LIKE REPLACE(p_chargecode, '*', '%'))
            AND (p_description IS NULL OR Description LIKE REPLACE(p_description, '*', '%'));   

    END IF;
    IF p_isExport = 2
    THEN

        SELECT
            DialStringCodeID,
            DialString,
            ChargeCode,
            Description,
            Forbidden
        FROM tblDialStringCode
        WHERE  (DialStringID = p_dialstringid)
         AND (Forbidden = p_forbidden)
			AND (p_dialstring IS NULL OR DialString LIKE REPLACE(p_dialstring, '*', '%'))
            AND (p_chargecode IS NULL OR ChargeCode LIKE REPLACE(p_chargecode, '*', '%'))
            AND (p_description IS NULL OR Description LIKE REPLACE(p_description, '*', '%'));   

    END IF;
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getDiscount` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getDiscount`(IN `p_CompanyID` INT, IN `p_DiscountPlanID` INT, IN `p_Name` VARCHAR(50), IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(5), IN `p_isExport` INT)
BEGIN
	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

	IF p_isExport = 0
	THEN
		SELECT   
			dg.Name,
			ROUND(ds.Threshold/60,0) as Threshold,
			ds.Discount,
			IF(ds.Unlimited =1 ,'Unlimited','') as UnlimitedText,
			dp.UpdatedBy,
			dp.updated_at,
			dp.DiscountID,
			dp.DiscountPlanID,
			dp.DestinationGroupID,
			ds.DiscountSchemeID,
			dp.Service,
			ds.Unlimited
		FROM tblDiscount dp
		INNER JOIN tblDestinationGroup dg 
			ON dg.DestinationGroupID = dp.DestinationGroupID
		INNER JOIN tblDiscountScheme ds 
			ON ds.DiscountID = dp.DiscountID
		WHERE dp.DiscountPlanID = p_DiscountPlanID
			AND (p_Name ='' OR dg.Name like  CONCAT('%',p_Name,'%'))
		ORDER BY
			CASE
					WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'NameDESC') THEN dg.Name
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'NameASC') THEN dg.Name
			END ASC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CreatedByDESC') THEN dp.CreatedBy
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CreatedByASC') THEN dp.CreatedBy
			END ASC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'created_atDESC') THEN dp.created_at
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'created_atASC') THEN dp.created_at
			END ASC
		LIMIT p_RowspPage OFFSET v_OffSet_;

		SELECT
			COUNT(dp.DiscountID) AS totalcount
		FROM tblDiscount dp
		INNER JOIN tblDestinationGroup dg ON dg.DestinationGroupID = dp.DestinationGroupID
		INNER JOIN tblDiscountScheme ds ON ds.DiscountID = dp.DiscountID
		WHERE dp.DiscountPlanID = p_DiscountPlanID
			AND (p_Name ='' OR dg.Name like  CONCAT('%',p_Name,'%'));
	END IF;

	IF p_isExport = 1
	THEN
		
		SELECT   
			dg.Name,
			dp.Service,
			ds.Threshold,
			ds.Discount,
			IF(ds.Unlimited =1 ,'Unlimited','') as Unlimited,
			dp.UpdatedBy,
			dp.updated_at
		FROM tblDiscount dp
		INNER JOIN tblDestinationGroup dg ON dg.DestinationGroupID = dp.DestinationGroupID
		INNER JOIN tblDiscountScheme ds ON ds.DiscountID = dp.DiscountID
		WHERE dp.DiscountPlanID = p_DiscountPlanID
			AND (p_Name ='' OR dg.Name like  CONCAT('%',p_Name,'%'));

	END IF;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getDiscountPlan` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getDiscountPlan`(IN `p_CompanyID` INT, IN `p_Name` VARCHAR(50), IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(5), IN `p_isExport` INT)
BEGIN
	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

	IF p_isExport = 0
	THEN
		SELECT   
			dp.Name,
			dgs.Name as DestinationGroupSet,
			c.Code as Currency,
			dp.UpdatedBy,
			dp.updated_at,
			dp.DiscountPlanID,
			dp.DestinationGroupSetID,
			dp.CurrencyID,
			dp.Description,
			(SELECT adp.DiscountPlanID FROM tblAccountDiscountPlan adp WHERE adp.DiscountPlanID = dp.DiscountPlanID LIMIT 1)as Applied
		FROM tblDiscountPlan dp
		INNER JOIN tblDestinationGroupSet dgs
			ON dgs.DestinationGroupSetID = dp.DestinationGroupSetID
		INNER JOIN tblCurrency c
			ON c.CurrencyId = dp.CurrencyID
		
		WHERE dp.CompanyID = p_CompanyID
			AND (p_Name ='' OR dp.Name like  CONCAT('%',p_Name,'%'))
		ORDER BY
			CASE
					WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'NameDESC') THEN dp.Name
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'NameASC') THEN dp.Name
			END ASC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CreatedByDESC') THEN dp.CreatedBy
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CreatedByASC') THEN dp.CreatedBy
			END ASC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'created_atDESC') THEN dp.created_at
			END DESC,
			CASE
				WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'created_atASC') THEN dp.created_at
			END ASC
		LIMIT p_RowspPage OFFSET v_OffSet_;

		SELECT
			COUNT(dp.DiscountPlanID) AS totalcount
		FROM tblDiscountPlan dp
		WHERE dp.CompanyID = p_CompanyID
			AND (p_Name ='' OR dp.Name like  CONCAT('%',p_Name,'%'));
	END IF;

	IF p_isExport = 1
	THEN
		
		SELECT   
			dp.Name,
			dp.UpdatedBy,
			dp.updated_at,
			dp.Description
		FROM tblDiscountPlan dp
		WHERE dp.CompanyID = p_CompanyID
			AND (p_Name ='' OR dp.Name like  CONCAT('%',p_Name,'%'));

	END IF;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetFromEmailAddress` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `prc_GetFromEmailAddress`(
	IN `p_CompanyID` int,
	IN `p_userID` int ,
	IN `p_Ticket` INT,
	IN `p_Admin` INT
)
BEGIN
	DECLARE V_Ticket_Permission int;
	DECLARE V_Ticket_Permission_level int;
	DECLARE V_User_Groups varchar(100);
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;	
	SELECT 0 INTO V_Ticket_Permission;
	SELECT 0 into V_Ticket_Permission_level;			
	IF p_Ticket = 1
	THEN	
		IF p_Admin > 0
		THEN
			SELECT 1 INTO V_Ticket_Permission;
		END IF;
		
		IF p_Admin < 1 
		THEN		
			SELECT 
				count(*) into V_Ticket_Permission
			FROM 
				tblUser u
			inner join 
				tblUserPermission up on u.UserID = up.UserID
			inner join 
				tblResourceCategories tc on up.resourceID = tc.ResourceCategoryID
			WHERE 
				tc.ResourceCategoryName = 'Tickets.View.GlobalAccess'  and u.UserID = p_userID;
		END IF;
		
		IF V_Ticket_Permission > 0 
		THEN
			SELECT 1 into V_Ticket_Permission_level;			
			IF p_Admin > 0
			THEN
				SELECT DISTINCT GroupEmailAddress as EmailFrom FROM tblTicketGroups where CompanyID = p_CompanyID and GroupEmailStatus = 1 and GroupEmailAddress IS NOT NULL
					UNION ALL			
				SELECT  tc.EmailFrom as EmailFrom FROM tblCompany tc where tc.EmailFrom IS NOT NULL AND tc.EmailFrom <> ''
					UNION ALL
				SELECT DISTINCT(tu.EmailAddress) as EmailFrom from tblUser tu;
			END IF;
			IF p_Admin < 1	
			THEN
				SELECT DISTINCT GroupEmailAddress as EmailFrom FROM tblTicketGroups where CompanyID = p_CompanyID and GroupEmailStatus = 1 and GroupEmailAddress IS NOT NULL
					UNION ALL	
				SELECT  tc.EmailFrom as EmailFrom FROM tblCompany tc where tc.EmailFrom IS NOT NULL AND tc.EmailFrom <> ''
					UNION ALL
				SELECT tu.EmailAddress as EmailFrom from tblUser tu where tu.UserID = p_userID;							
			END IF;
		END IF;
		
		IF V_Ticket_Permission_level = 0
			THEN
				SELECT 0 into V_Ticket_Permission;
				SELECT 
				/*distinct u.userid, up.AddRemove,tc.ResourceCategoryName as permname*/
				count(*) into V_Ticket_Permission
			FROM 
				tblUser u
			inner join 
				tblUserPermission up on u.UserID = up.UserID
			inner join 
				tblResourceCategories tc on up.resourceID = tc.ResourceCategoryID
			WHERE 
				tc.ResourceCategoryName = 'Tickets.View.GroupAccess'  and u.UserID = p_userID;
		END IF;

		IF V_Ticket_Permission > 0 and V_Ticket_Permission_level = 0 
		THEN
			SELECT 2 into V_Ticket_Permission_level;
			
			SELECT GROUP_CONCAT(GroupID SEPARATOR ',') into V_User_Groups FROM tblTicketGroupAgents TGA where TGA.UserID = p_userID;
			
			IF p_Admin > 0
			THEN
				SELECT DISTINCT GroupEmailAddress as EmailFrom FROM tblTicketGroups where CompanyID = p_CompanyID and GroupEmailStatus = 1 and GroupEmailAddress IS NOT NULL AND FIND_IN_SET(GroupID,V_User_Groups)
					UNION ALL			
				SELECT  tc.EmailFrom as EmailFrom FROM tblCompany tc where tc.EmailFrom IS NOT NULL AND tc.EmailFrom <> ''
					UNION ALL
				SELECT DISTINCT(tu.EmailAddress) as EmailFrom from tblUser tu;
			END IF;
			IF p_Admin < 1	
			THEN
				SELECT DISTINCT GroupEmailAddress as EmailFrom FROM tblTicketGroups where CompanyID = p_CompanyID and GroupEmailStatus = 1 and GroupEmailAddress IS NOT NULL AND FIND_IN_SET(GroupID,V_User_Groups)
					UNION ALL	
				SELECT  tc.EmailFrom as EmailFrom FROM tblCompany tc where tc.EmailFrom IS NOT NULL AND tc.EmailFrom <> ''
					UNION ALL
				SELECT tu.EmailAddress as EmailFrom from tblUser tu where tu.UserID = p_userID;							
			END IF;
			
		END IF;

		IF V_Ticket_Permission_level = 0 
		THEN
			SELECT 0 into V_Ticket_Permission;
			SELECT 3 into V_Ticket_Permission_level;
			IF p_Admin > 0
			THEN
				SELECT DISTINCT TG.GroupEmailAddress as EmailFrom FROM tblTicketGroups TG INNER JOIN tblTickets TT ON TT.Group = TG.GroupID where TG.CompanyID = p_CompanyID and GroupEmailStatus = 1 and TG.GroupEmailAddress IS NOT NULL AND TT.Agent = p_userID					
					UNION ALL			
				SELECT  tc.EmailFrom as EmailFrom FROM tblCompany tc where tc.EmailFrom IS NOT NULL AND tc.EmailFrom <> ''
					UNION ALL
				SELECT DISTINCT(tu.EmailAddress) as EmailFrom from tblUser tu;
			END IF;
			IF p_Admin < 1	
			THEN
				SELECT DISTINCT TG.GroupEmailAddress as EmailFrom FROM tblTicketGroups TG INNER JOIN tblTickets TT ON TT.Group = TG.GroupID where TG.CompanyID = p_CompanyID and GroupEmailStatus = 1 and TG.GroupEmailAddress IS NOT NULL AND TT.Agent = p_userID
					UNION ALL	
				SELECT  tc.EmailFrom as EmailFrom FROM tblCompany tc where tc.EmailFrom IS NOT NULL AND tc.EmailFrom <> ''
					UNION ALL
				SELECT tu.EmailAddress as EmailFrom from tblUser tu where tu.UserID = p_userID;							
			END IF;
			
		END IF;		
	END IF;
	
	IF p_Ticket = 0
	THEN
		IF p_Admin > 0
		THEN
			SELECT  tc.EmailFrom as EmailFrom FROM tblCompany tc where tc.EmailFrom IS NOT NULL AND tc.EmailFrom <> ''
				UNION ALL
			SELECT DISTINCT(tu.EmailAddress) as EmailFrom from tblUser tu;
		END IF;
		IF p_Admin < 1	
		THEN
			SELECT  tc.EmailFrom as EmailFrom FROM tblCompany tc where tc.EmailFrom IS NOT NULL AND tc.EmailFrom <> ''
				UNION ALL
			SELECT tu.EmailAddress as EmailFrom from tblUser tu where tu.UserID = p_userID;							
		END IF;
	END IF;
	/*SELECT V_Ticket_Permission_level;*/
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getJobDropdown` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getJobDropdown`(IN `p_CompanyId` int , IN `p_userId` int , IN `p_isadmin` int , IN `p_reset` int )
BEGIN
   
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
  
  IF p_reset = 1 
  THEN
    update `tblJob` set `ShowInCounter` = '0' where `CompanyID` = p_CompanyId and `ShowInCounter` = '1';
  END IF;
        select  `tblJob`.`JobID`, `tblJob`.`Title`, `tblJobStatus`.`Title` as Status, `tblJob`.`HasRead`, `tblJob`.`CreatedBy`, `tblJob`.`created_at`, `tblJobType`.`Title` as JobType 
        from `tblJob` inner join `tblJobStatus` on `tblJob`.`JobStatusID` = `tblJobStatus`.`JobStatusID` 
        inner join `tblJobType` on `tblJob`.`JobTypeID` = `tblJobType`.`JobTypeID` 
        where `tblJob`.`CompanyID` = p_CompanyId AND  `tblJob`.JobLoggedUserID = p_userId
        order by `tblJob`.`ShowInCounter` desc, `tblJob`.`updated_at` desc,`tblJob`.JobID desc 
		  limit 10;

	  select count(*) as totalNonVisitedJobs from `tblJob` 
    inner join `tblJobStatus` on `tblJob`.`JobStatusID` = `tblJobStatus`.`JobStatusID` 
    where `tblJob`.`CompanyID` = p_CompanyId and `tblJob`.`ShowInCounter` = '1' and `tblJob`.JobLoggedUserID = p_userId;
    
    

    select count(*) as totalPendingJobs from `tblJob` inner join `tblJobStatus` on `tblJob`.`JobStatusID` = `tblJobStatus`.`JobStatusID` 
    where `tblJobStatus`.`Title` = 'Pending' and `tblJob`.`CompanyID` = p_CompanyId AND 
	`tblJob`.JobLoggedUserID = p_userId;
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetLastRateTableRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetLastRateTableRate`(IN `p_companyid` INT, IN `p_RateTableId` INT, IN `p_EffectiveDate` datetime)
BEGIN
		SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	
        SELECT
            Code,
            Description,
            tblRateTableRate.Interval1,
            tblRateTableRate.IntervalN,
			ConnectionFee,
            Rate,
            EffectiveDate
        FROM tblRate
        INNER JOIN tblRateTableRate
            ON tblRateTableRate.RateID = tblRate.RateID
            AND tblRateTableRate.RateTableId = p_RateTableId
        INNER JOIN tblRateTable
            ON tblRateTable.RateTableId = tblRateTableRate.RateTableId
        WHERE		(tblRate.CompanyID = p_companyid)
		AND tblRateTableRate.RateTableId = p_RateTableId
		AND EffectiveDate = p_EffectiveDate;
		
		SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetLCR` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetLCR`(
	IN `p_companyid` INT,
	IN `p_trunkID` INT,
	IN `p_codedeckID` INT,
	IN `p_CurrencyID` INT,
	IN `p_code` VARCHAR(50),
	IN `p_PageNumber` INT,
	IN `p_RowspPage` INT,
	IN `p_SortOrder` VARCHAR(50),
	IN `p_Preference` INT,
	IN `p_isExport` INT





)
BEGIN

    
    DECLARE v_OffSet_ int;

    DECLARE v_Code VARCHAR(50) ;
DECLARE v_pointer_ int;
DECLARE v_rowCount_ int;
DECLARE v_p_code VARCHAR(50);
DECLARE v_Codlen_ int;
DECLARE v_position int;
DECLARE v_p_code__ VARCHAR(50);
DECLARE v_has_null_position int ;
DECLARE v_next_position1 VARCHAR(200) ;
	DECLARE v_CompanyCurrencyID_ INT; 	
	
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

     SET @@session.collation_connection='utf8_unicode_ci';
     SET @@session.character_set_results='utf8';

    SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

 

  
  
DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_;
       CREATE TEMPORARY TABLE tmp_VendorRate_stage_ (
          RowCode VARCHAR(50) ,
          AccountId INT ,
          AccountName VARCHAR(100) ,
          Code VARCHAR(50) ,
          Rate DECIMAL(18,6) ,
          ConnectionFee DECIMAL(18,6) ,
          EffectiveDate DATETIME ,
          Description VARCHAR(255),
          Preference INT,
          MaxMatchRank int ,
          prev_prev_RowCode VARCHAR(50),
          prev_AccountID int
        ) 
  ;

     DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage2_;
       CREATE TEMPORARY TABLE tmp_VendorRate_stage2_ (
          RowCode VARCHAR(50) ,
          AccountId INT ,
          AccountName VARCHAR(100) ,
          Code VARCHAR(50) ,
          Rate DECIMAL(18,6) ,
          ConnectionFee DECIMAL(18,6) ,
          EffectiveDate DATETIME ,
          Description VARCHAR(255),
          Preference INT,
          MaxMatchRank int ,
          prev_prev_RowCode VARCHAR(50),
          prev_AccountID int
        ) 
  ;

  DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_;
  CREATE TEMPORARY TABLE tmp_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) ,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) 
   ) 
   ;
     
   DROP TEMPORARY TABLE IF EXISTS tmp_final_VendorRate_;
  CREATE TEMPORARY TABLE tmp_final_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) ,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50),
     FinalRankNumber int
   ) 
  ;
   

DROP TEMPORARY TABLE IF EXISTS tmp_search_code_;
  CREATE TEMPORARY TABLE tmp_search_code_ (
     Code  varchar(50),
     INDEX Index1 (Code)
);


DROP TEMPORARY TABLE IF EXISTS tmp_code_;
  CREATE TEMPORARY TABLE tmp_code_ (
     RowCode  varchar(50),
     Code  varchar(50),
     RowNo int,
     INDEX Index1 (Code)
   );


DROP TEMPORARY TABLE IF EXISTS tmp_all_code_;
  CREATE TEMPORARY TABLE tmp_all_code_ ( 
     RowCode  varchar(50),
     Code  varchar(50),
     RowNo int,
     INDEX Index2 (Code)
   )
   ;

        
DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates_;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates_(
			AccountId int,
         AccountName varchar(200),
			Code varchar(50),
			Description varchar(200),
			Rate DECIMAL(18,6) ,
         ConnectionFee DECIMAL(18,6) , 
			EffectiveDate date,
			TrunkID int,
			CountryID int,
			RateID int,
			Preference int,
			INDEX IX_Code (Code)
	) 
  ;

     DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates1_;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates1_(
			AccountId int,
         AccountName varchar(200),
			Code varchar(50),
			Description varchar(200),
			Rate DECIMAL(18,6) ,
               ConnectionFee DECIMAL(18,6) , 
			EffectiveDate date,
			TrunkID int,
			CountryID int,
			RateID int,
			Preference int,
			INDEX IX_Code (Code),
			INDEX tmp_VendorCurrentRates_AccountId (`AccountId`,`TrunkID`,`RateId`,`EffectiveDate`)
	) 
  ;

 DROP TEMPORARY TABLE IF EXISTS tmp_VendorRateByRank_;
  CREATE TEMPORARY TABLE tmp_VendorRateByRank_ (      
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) ,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     rankname INT,
     INDEX IX_Code (Code,rankname)
   )
   ;

	SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = p_companyid;



          
          insert into tmp_search_code_
          SELECT  DISTINCT LEFT(f.Code, x.RowNo) as loopCode FROM (
          SELECT @RowNo  := @RowNo + 1 as RowNo
          FROM mysql.help_category 
          ,(SELECT @RowNo := 0 ) x
          limit 15
          ) x
          INNER JOIN tblRate AS f
          ON f.CompanyID = p_companyid  AND f.CodeDeckId = p_codedeckID
          AND ( CHAR_LENGTH(RTRIM(p_code)) = 0  OR f.Code LIKE REPLACE(p_code,'*', '%') )
          AND x.RowNo   <= LENGTH(f.Code) 
          order by loopCode   desc;


 
     
      INSERT INTO tmp_VendorCurrentRates1_ 
      Select DISTINCT AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference 
      FROM (
				SELECT distinct tblVendorRate.AccountId,tblAccount.AccountName, tblRate.Code, tblRate.Description, 
				 CASE WHEN  tblAccount.CurrencyId = p_CurrencyID
								THEN
									   tblVendorRate.Rate
					 WHEN  v_CompanyCurrencyID_ = p_CurrencyID  
							 THEN
								  (
								   ( tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = p_companyid ) )
								 )
							  ELSE 
							  (
										
								  (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = p_CurrencyID and  CompanyID = p_companyid )
									* (tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = p_companyid ))
							  )
							 END 
								as  Rate,
								 ConnectionFee,
                    		DATE_FORMAT (tblVendorRate.EffectiveDate, '%Y-%m-%d') AS EffectiveDate, tblVendorRate.TrunkID, tblRate.CountryID, tblRate.RateID,IFNULL(vp.Preference, 5) AS Preference 
                    		FROM      tblVendorRate
					     Inner join tblVendorTrunk vt on vt.CompanyID = p_companyid AND vt.AccountID = tblVendorRate.AccountID and vt.Status =  1 and vt.TrunkID =  p_trunkID  
							
						INNER JOIN tblAccount   ON  tblAccount.CompanyID = p_companyid AND tblVendorRate.AccountId = tblAccount.AccountID and tblAccount.IsVendor = 1
						INNER JOIN tblRate ON tblRate.CompanyID = p_companyid     AND    tblVendorRate.RateId = tblRate.RateID  
						INNER JOIN tmp_search_code_  SplitCode   on tblRate.Code = SplitCode.Code 
						LEFT JOIN tblVendorPreference vp
										 ON vp.AccountId = tblVendorRate.AccountId
										 AND vp.TrunkID = tblVendorRate.TrunkID
										 AND vp.RateId = tblVendorRate.RateId
						LEFT OUTER JOIN tblVendorBlocking AS blockCode   ON tblVendorRate.RateId = blockCode.RateId
																	  AND tblVendorRate.AccountId = blockCode.AccountId
																	  AND tblVendorRate.TrunkID = blockCode.TrunkID
						LEFT OUTER JOIN tblVendorBlocking AS blockCountry    ON tblRate.CountryID = blockCountry.CountryId
																	  AND tblVendorRate.AccountId = blockCountry.AccountId
																	  AND tblVendorRate.TrunkID = blockCountry.TrunkID
			  WHERE      
						( EffectiveDate <= NOW() )
						AND tblAccount.IsVendor = 1
						AND tblAccount.Status = 1
						AND tblAccount.CurrencyId is not NULL
						AND tblVendorRate.TrunkID = p_trunkID
						AND blockCode.RateId IS NULL
					   AND blockCountry.CountryId IS NULL
			
		) tbl
		order by Code asc;
		
         
     
     INSERT INTO tmp_VendorCurrentRates_ 
	  Select AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference 
      FROM (
			  SELECT * ,
				@row_num := IF(@prev_AccountId = AccountID AND @prev_TrunkID = TrunkID AND @prev_RateId = RateID AND @prev_EffectiveDate >= EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := AccountID,
				@prev_TrunkID := TrunkID,
				@prev_RateId := RateID,
				@prev_EffectiveDate := EffectiveDate
			  FROM tmp_VendorCurrentRates1_
			  ,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
           ORDER BY AccountId, TrunkID, RateId, EffectiveDate DESC	
		) tbl
		 WHERE RowID = 1 
		order by Code asc;
		
	 
 
         
          
        insert into tmp_all_code_ (RowCode,Code,RowNo)
               select RowCode , loopCode,RowNo
               from (
                    select   RowCode , loopCode,
                    @RowNo := ( CASE WHEN ( @prev_Code = tbl1.RowCode  ) THEN @RowNo + 1
                                   ELSE 1
                                   END
                                
                              )      as RowNo,
                    @prev_Code := tbl1.RowCode

                    from (
                              SELECT distinct f.Code as RowCode, LEFT(f.Code, x.RowNo) as loopCode FROM (
                                SELECT @RowNo  := @RowNo + 1 as RowNo
                                FROM mysql.help_category 
                                ,(SELECT @RowNo := 0 ) x
                                limit 15
                              ) x
                              INNER JOIN tmp_search_code_ AS f
                              ON  x.RowNo   <= LENGTH(f.Code)  AND ( CHAR_LENGTH(RTRIM(p_code)) = 0  OR Code LIKE REPLACE(p_code,'*', '%') )
                              order by RowCode desc,  LENGTH(loopCode) DESC 
                    ) tbl1
                    , ( Select @RowNo := 0 ) x
                ) tbl order by RowCode desc,  LENGTH(loopCode) DESC ;
          
    
        
        IF (p_isExport = 0)
        THEN
 			        
					insert into tmp_code_
                         select * from tmp_all_code_
                         order by RowCode	LIMIT p_RowspPage OFFSET v_OffSet_ ;
		
		ELSE

     					insert into tmp_code_
                              select * from tmp_all_code_
                              order by RowCode	  ;
			
		END IF;


IF p_Preference = 1 THEN

	
	INSERT IGNORE INTO tmp_VendorRateByRank_
	  SELECT
		AccountID,
		AccountName,
		Code,
		Rate,
		ConnectionFee,
		EffectiveDate,
		Description,
		Preference,
		preference_rank
	  FROM (SELECT
		  AccountID,
		  AccountName,
		  Code,
		  Rate,
		  ConnectionFee,
		  EffectiveDate,
		  Description,
		  Preference,
		  @preference_rank := CASE WHEN (@prev_Code     = Code AND @prev_Preference > Preference  ) THEN @preference_rank + 1 
								   WHEN (@prev_Code     = Code AND @prev_Preference = Preference AND @prev_Rate < Rate) THEN @preference_rank + 1 
								   WHEN (@prev_Code    = Code AND @prev_Preference = Preference AND @prev_Rate = Rate) THEN @preference_rank 
								   ELSE 1 
								   END AS preference_rank,
		  @prev_Code := Code,
		  @prev_Preference := IFNULL(Preference, 5),
		  @prev_Rate := Rate
		FROM tmp_VendorCurrentRates_ AS preference,
			 (SELECT @preference_rank := 0 , @prev_Code := ''  , @prev_Preference := 5,  @prev_Rate := 0) x
		ORDER BY preference.Code ASC, preference.Preference DESC, preference.Rate ASC,preference.AccountId ASC
		   
		 ) tbl
	  WHERE preference_rank <= 5
	  ORDER BY Code, preference_rank;

	ELSE


	
	INSERT IGNORE INTO tmp_VendorRateByRank_
	  SELECT
		AccountID,
		AccountName,
		Code,
		Rate,
		ConnectionFee,
		EffectiveDate,
		Description,
		Preference,
		RateRank
	  FROM (SELECT
		  AccountID,
		  AccountName,
		  Code,
		  Rate,
		  ConnectionFee,
		  EffectiveDate,
		  Description,
		  Preference,
		  @rank := CASE WHEN (@prev_Code    = Code AND @prev_Rate < Rate) THEN @rank + 1 
						WHEN (@prev_Code    = Code AND @prev_Rate = Rate) THEN @rank 
						ELSE 1 
						END AS RateRank,
		  @prev_Code := Code,
		  @prev_Rate := Rate
		FROM tmp_VendorCurrentRates_ AS rank,
			 (SELECT @rank := 0 , @prev_Code := '' , @prev_Rate := 0) f
		ORDER BY rank.Code ASC, rank.Rate,rank.AccountId ASC) tbl
	  WHERE RateRank <= 5
	  ORDER BY Code, RateRank;

	END IF;


             

 
DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_1;
 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorRate_stage_1 as (select * from tmp_VendorRate_stage_);

 insert ignore into tmp_VendorRate_stage_1 (
   	     RowCode,
     	AccountId ,
     	AccountName ,
     	Code ,
     	Rate ,
     	ConnectionFee,                                                           
     	EffectiveDate , 
     	Description ,
     	Preference
)
          SELECT  
          distinct                                
     	RowCode,
     	v.AccountId ,
     	v.AccountName ,
     	v.Code ,
     	v.Rate ,
     	v.ConnectionFee,                                                           
     	v.EffectiveDate , 
     	v.Description ,
     	v.Preference
          FROM tmp_VendorRateByRank_ v  
          left join  tmp_all_code_
			SplitCode   on v.Code = SplitCode.Code 
         where  SplitCode.Code is not null and rankname <= 5 
         order by AccountID,SplitCode.RowCode desc ,LENGTH(SplitCode.RowCode), v.Code desc, LENGTH(v.Code)  desc;
       

     insert ignore into tmp_VendorRate_stage_
          SELECT  
          distinct                                
     	RowCode,
     	v.AccountId ,
     	v.AccountName ,
     	v.Code ,
     	v.Rate ,
     	v.ConnectionFee,                                                           
     	v.EffectiveDate , 
     	v.Description ,
     	v.Preference, 
     	@rank := ( CASE WHEN( @prev_AccountID = v.AccountId  and @prev_RowCode     = RowCode   ) 
                                   THEN  @rank + 1 
                                   ELSE 1 
                              END 
          ) AS MaxMatchRank,
	     @prev_RowCode := RowCode	 ,															
          @prev_AccountID := v.AccountId   
          FROM tmp_VendorRate_stage_1 v  
          , (SELECT  @prev_RowCode := '',  @rank := 0 , @prev_Code := '' , @prev_AccountID := Null) f
          order by AccountID,RowCode desc ; 
         
    
    

              insert ignore into tmp_VendorRate_
              select
                    distinct 
				 AccountId ,
				 AccountName ,
				 Code ,
				 Rate ,
                    ConnectionFee,                                                           
				 EffectiveDate ,
				 Description ,
				 Preference,
				 RowCode
		   from tmp_VendorRate_stage_ 
           where MaxMatchRank = 1 
          
			  			  order by RowCode desc; 


     
                                   
		IF( p_Preference = 0 )
     	THEN
     			
			insert into tmp_final_VendorRate_
			SELECT
			     AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
					AccountId ,
				     AccountName ,
				     Code ,
				     Rate ,
                         ConnectionFee,
				     EffectiveDate ,
				     Description ,
				     Preference,
				     RowCode,
   			         @rank := CASE WHEN ( @prev_RowCode     = RowCode AND @prev_Rate <  Rate ) THEN @rank+1
                                   WHEN ( @prev_RowCode    = RowCode AND @prev_Rate = Rate ) THEN @rank 
						   ELSE 
						   1
						   END 
				  		  AS FinalRankNumber,
				  	@prev_RowCode  := RowCode,
					@prev_Rate  := Rate
					from tmp_VendorRate_ 
					,( SELECT @rank := 0 , @prev_RowCode := '' , @prev_Rate := 0 ) x 
				 	order by RowCode,Rate,AccountId ASC
				 
				) tbl1
				where 
				FinalRankNumber <= 5;
				 
		 ELSE 
		 
		 	 insert into tmp_final_VendorRate_
			SELECT
				AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
     					AccountId ,
     				     AccountName ,
     				     Code ,
     				     Rate ,
                             ConnectionFee,
     				     EffectiveDate ,
     				     Description ,
     				     Preference,
     				     RowCode,
                              @preference_rank := CASE WHEN (@prev_Code     = RowCode AND @prev_Preference > Preference  )   THEN @preference_rank + 1 
                                                       WHEN (@prev_Code     = RowCode AND @prev_Preference = Preference AND @prev_Rate < Rate) THEN @preference_rank + 1 
                                                       WHEN (@prev_Code    = RowCode AND @prev_Preference = Preference AND @prev_Rate = Rate) THEN @preference_rank 
                                                       ELSE 1 END AS FinalRankNumber,
                              @prev_Code := RowCode,
                              @prev_Preference := Preference,
                              @prev_Rate := Rate
					from tmp_VendorRate_ 
                          ,(SELECT @preference_rank := 0 , @prev_Code := ''  , @prev_Preference := 5,  @prev_Rate := 0) x
     			 	 order by RowCode ASC ,Preference DESC ,Rate ASC ,AccountId ASC
                         
				) tbl1
				where 
				FinalRankNumber <= 5;
		 
		 END IF;

          
        
          
        

    IF (p_isExport = 0)
    THEN 
    
    		 SELECT
           CONCAT(ANY_VALUE(t.RowCode) , ' : ' , ANY_VALUE(t.Description)) as Destination,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 1, CONCAT(ANY_VALUE(t.Code), '<br>', ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 1`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 2, CONCAT(ANY_VALUE(t.Code), '<br>', ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName), '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 2`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 3, CONCAT(ANY_VALUE(t.Code), '<br>', ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 3`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 4, CONCAT(ANY_VALUE(t.Code), '<br>', ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 4`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 5, CONCAT(ANY_VALUE(t.Code), '<br>', ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 5`
        FROM tmp_final_VendorRate_  t
          GROUP BY  RowCode   
          ORDER BY RowCode ASC
     	LIMIT p_RowspPage OFFSET v_OffSet_ ;

   
               
   
                select count(distinct RowCode) as totalcount from tmp_final_VendorRate_ where    ( CHAR_LENGTH(RTRIM(p_code)) = 0  OR RowCode LIKE REPLACE(p_code,'*', '%') )    ;
       
       
   END IF; 
   
   IF p_isExport = 1
    THEN
		SELECT
           CONCAT(ANY_VALUE(t.RowCode) , ' : ' , ANY_VALUE(t.Description)) as Destination,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 1, CONCAT(ANY_VALUE(t.Code), '<br>', ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 1`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 2, CONCAT(ANY_VALUE(t.Code), '<br>', ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName), '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 2`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 3, CONCAT(ANY_VALUE(t.Code), '<br>', ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 3`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 4, CONCAT(ANY_VALUE(t.Code), '<br>', ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 4`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 5, CONCAT(ANY_VALUE(t.Code), '<br>', ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 5`
        FROM tmp_final_VendorRate_  t
          GROUP BY  RowCode   
          ORDER BY RowCode ASC;
	END IF;		


SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetLCRwithPrefix` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetLCRwithPrefix`(
	IN `p_companyid` INT,
	IN `p_trunkID` INT,
	IN `p_codedeckID` INT,
	IN `p_CurrencyID` INT,
	IN `p_code` VARCHAR(50),
	IN `p_PageNumber` INT,
	IN `p_RowspPage` INT,
	IN `p_SortOrder` VARCHAR(50),
	IN `p_Preference` INT,
	IN `p_isExport` INT



)
BEGIN

DECLARE v_OffSet_ int;
DECLARE v_Code VARCHAR(50) ;
DECLARE v_pointer_ int;
DECLARE v_rowCount_ int;
DECLARE v_p_code VARCHAR(50);
DECLARE v_Codlen_ int;
DECLARE v_position int;
DECLARE v_p_code__ VARCHAR(50);
DECLARE v_has_null_position int ;
DECLARE v_next_position1 VARCHAR(200) ;
DECLARE v_CompanyCurrencyID_ INT; 	
	
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

     SET @@session.collation_connection='utf8_unicode_ci';
     SET @@session.character_set_results='utf8';

    SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

 

  
  
DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage_;
       CREATE TEMPORARY TABLE tmp_VendorRate_stage_ (
          RowCode VARCHAR(50) ,
          AccountId INT ,
          AccountName VARCHAR(100) ,
          Code VARCHAR(50) ,
          Rate DECIMAL(18,6) ,
          ConnectionFee DECIMAL(18,6) ,
          EffectiveDate DATETIME ,
          Description VARCHAR(255),
          Preference INT,
          MaxMatchRank int ,
          prev_prev_RowCode VARCHAR(50),
          prev_AccountID int
        ) 
  ;

     DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_stage2_;
       CREATE TEMPORARY TABLE tmp_VendorRate_stage2_ (
          RowCode VARCHAR(50) ,
          AccountId INT ,
          AccountName VARCHAR(100) ,
          Code VARCHAR(50) ,
          Rate DECIMAL(18,6) ,
          ConnectionFee DECIMAL(18,6) ,
          EffectiveDate DATETIME ,
          Description VARCHAR(255),
          Preference INT,
          MaxMatchRank int ,
          prev_prev_RowCode VARCHAR(50),
          prev_AccountID int
        ) 
  ;

  DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_;
  CREATE TEMPORARY TABLE tmp_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) ,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50) 
   ) 
   ;
     
   DROP TEMPORARY TABLE IF EXISTS tmp_final_VendorRate_;
  CREATE TEMPORARY TABLE tmp_final_VendorRate_ (
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) ,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     RowCode VARCHAR(50),
     FinalRankNumber int
   ) 
  ;
   

DROP TEMPORARY TABLE IF EXISTS tmp_search_code_;
  CREATE TEMPORARY TABLE tmp_search_code_ (
     Code  varchar(50),
     INDEX Index1 (Code)
);


DROP TEMPORARY TABLE IF EXISTS tmp_code_;
  CREATE TEMPORARY TABLE tmp_code_ (
     RowCode  varchar(50),
     Code  varchar(50),
     RowNo int,
     INDEX Index1 (Code)
   );


DROP TEMPORARY TABLE IF EXISTS tmp_all_code_;
  CREATE TEMPORARY TABLE tmp_all_code_ ( 
     RowCode  varchar(50),
     Code  varchar(50),
     
     INDEX Index2 (Code)
   )
   ;

        
DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates_;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates_(
			AccountId int,
         AccountName varchar(200),
			Code varchar(50),
			Description varchar(200),
			Rate DECIMAL(18,6) ,
         ConnectionFee DECIMAL(18,6) , 
			EffectiveDate date,
			TrunkID int,
			CountryID int,
			RateID int,
			Preference int,
			INDEX IX_Code (Code)
	) 
  ;

     DROP TEMPORARY TABLE IF EXISTS tmp_VendorCurrentRates1_;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorCurrentRates1_(
			AccountId int,
         AccountName varchar(200),
			Code varchar(50),
			Description varchar(200),
			Rate DECIMAL(18,6) ,
               ConnectionFee DECIMAL(18,6) , 
			EffectiveDate date,
			TrunkID int,
			CountryID int,
			RateID int,
			Preference int,
			INDEX IX_Code (Code),
			INDEX tmp_VendorCurrentRates_AccountId (`AccountId`,`TrunkID`,`RateId`,`EffectiveDate`)
	) 
  ;

 DROP TEMPORARY TABLE IF EXISTS tmp_VendorRateByRank_;
  CREATE TEMPORARY TABLE tmp_VendorRateByRank_ (      
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) ,
     Rate DECIMAL(18,6) ,
     ConnectionFee DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     Preference INT,
     rankname INT,
     INDEX IX_Code (Code,rankname)
   )
   ;

	SELECT CurrencyId INTO v_CompanyCurrencyID_ FROM  tblCompany WHERE CompanyID = p_companyid;



         

 
     
     INSERT INTO tmp_VendorCurrentRates1_ 
	  Select DISTINCT AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference 
      FROM (
				SELECT distinct tblVendorRate.AccountId,tblAccount.AccountName, tblRate.Code, tblRate.Description, 
				 CASE WHEN  tblAccount.CurrencyId = p_CurrencyID
								THEN
									   tblVendorRate.Rate
					 WHEN  v_CompanyCurrencyID_ = p_CurrencyID  
							 THEN
								  (
								   ( tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = p_companyid ) )
								 )
							  ELSE 
							  (
										
								  (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = p_CurrencyID and  CompanyID = p_companyid )
									* (tblVendorRate.rate  / (Select Value from tblCurrencyConversion where tblCurrencyConversion.CurrencyId = tblAccount.CurrencyId and  CompanyID = p_companyid ))
							  )
							 END
								as  Rate,
								 ConnectionFee,
			  DATE_FORMAT (tblVendorRate.EffectiveDate, '%Y-%m-%d') AS EffectiveDate, 
			    tblVendorRate.TrunkID, tblRate.CountryID, tblRate.RateID,IFNULL(vp.Preference, 5) AS Preference 
			  FROM      tblVendorRate
					 Inner join tblVendorTrunk vt on vt.CompanyID = p_companyid AND vt.AccountID = tblVendorRate.AccountID and vt.Status =  1 and vt.TrunkID =  p_trunkID  
							 
						INNER JOIN tblAccount   ON  tblAccount.CompanyID = p_companyid AND tblVendorRate.AccountId = tblAccount.AccountID 
						INNER JOIN tblRate ON tblRate.CompanyID = p_companyid   AND    tblVendorRate.RateId = tblRate.RateID
						
						
						
						LEFT JOIN tblVendorPreference vp
										 ON vp.AccountId = tblVendorRate.AccountId
										 AND vp.TrunkID = tblVendorRate.TrunkID
										 AND vp.RateId = tblVendorRate.RateId
						LEFT OUTER JOIN tblVendorBlocking AS blockCode   ON tblVendorRate.RateId = blockCode.RateId
																	  AND tblVendorRate.AccountId = blockCode.AccountId
																	  AND tblVendorRate.TrunkID = blockCode.TrunkID
						LEFT OUTER JOIN tblVendorBlocking AS blockCountry    ON tblRate.CountryID = blockCountry.CountryId
																	  AND tblVendorRate.AccountId = blockCountry.AccountId
																	  AND tblVendorRate.TrunkID = blockCountry.TrunkID
			  WHERE      

						( CHAR_LENGTH(RTRIM(p_code)) = 0 OR tblRate.Code LIKE REPLACE(p_code,'*', '%') )  
						AND EffectiveDate <= NOW() 
						AND tblAccount.IsVendor = 1
						AND tblAccount.Status = 1
						AND tblAccount.CurrencyId is not NULL
						AND tblVendorRate.TrunkID = p_trunkID
						AND blockCode.RateId IS NULL
					   AND blockCountry.CountryId IS NULL
			
		) tbl
		order by Code asc;
		
		
			
         
     
     INSERT INTO tmp_VendorCurrentRates_ 
	  Select AccountId,AccountName,Code,Description, Rate,ConnectionFee,EffectiveDate,TrunkID,CountryID,RateID,Preference 
      FROM (
			  SELECT * ,
				@row_num := IF(@prev_AccountId = AccountID AND @prev_TrunkID = TrunkID AND @prev_RateId = RateID AND @prev_EffectiveDate >= EffectiveDate, @row_num + 1, 1) AS RowID,
				@prev_AccountId := AccountID,
				@prev_TrunkID := TrunkID,
				@prev_RateId := RateID,
				@prev_EffectiveDate := EffectiveDate
			  FROM tmp_VendorCurrentRates1_
			  ,(SELECT @row_num := 1,  @prev_AccountId := '',@prev_TrunkID := '', @prev_RateId := '', @prev_EffectiveDate := '') x
           ORDER BY AccountId, TrunkID, RateId, EffectiveDate DESC	
		) tbl
		 WHERE RowID = 1 
		order by Code asc;
 
 	 
			
			
         
          
        
		
		
		 
    
        
        


IF p_Preference = 1 THEN

	
	INSERT IGNORE INTO tmp_VendorRateByRank_
	  SELECT
		AccountID,
		AccountName,
		Code,
		Rate,
		ConnectionFee,
		EffectiveDate,
		Description,
		Preference,
		preference_rank
	  FROM (SELECT
		  AccountID,
		  AccountName,
		  Code,
		  Rate,
		  ConnectionFee,
		  EffectiveDate,
		  Description,
		  Preference,
		  @preference_rank := CASE WHEN (@prev_Code     = Code AND @prev_Preference > Preference  ) THEN @preference_rank + 1 
								   WHEN (@prev_Code     = Code AND @prev_Preference = Preference AND @prev_Rate < Rate) THEN @preference_rank + 1 
								   WHEN (@prev_Code    = Code AND @prev_Preference = Preference AND @prev_Rate = Rate) THEN @preference_rank 
								   ELSE 1 
								   END AS preference_rank,
		  @prev_Code := Code,
		  @prev_Preference := IFNULL(Preference, 5),
		  @prev_Rate := Rate
		FROM tmp_VendorCurrentRates_ AS preference,
			 (SELECT @preference_rank := 0 , @prev_Code := ''  , @prev_Preference := 5,  @prev_Rate := 0) x
		ORDER BY preference.Code ASC, preference.Preference DESC, preference.Rate ASC,preference.AccountId ASC
		   
		 ) tbl
	  WHERE preference_rank <= 5
	  ORDER BY Code, preference_rank;

	ELSE


	
	INSERT IGNORE INTO tmp_VendorRateByRank_
	  SELECT
		AccountID,
		AccountName,
		Code,
		Rate,
		ConnectionFee,
		EffectiveDate,
		Description,
		Preference,
		RateRank
	  FROM (SELECT
		  AccountID,
		  AccountName,
		  Code,
		  Rate,
		  ConnectionFee,
		  EffectiveDate,
		  Description,
		  Preference,
		  @rank := CASE WHEN (@prev_Code    = Code AND @prev_Rate < Rate) THEN @rank + 1 
						WHEN (@prev_Code    = Code AND @prev_Rate = Rate) THEN @rank 
						ELSE 1 
						END AS RateRank,
		  @prev_Code := Code,
		  @prev_Rate := Rate
		FROM tmp_VendorCurrentRates_ AS rank,
			 (SELECT @rank := 0 , @prev_Code := '' , @prev_Rate := 0) f
		ORDER BY rank.Code ASC, rank.Rate,rank.AccountId ASC) tbl
	  WHERE RateRank <= 5
	  ORDER BY Code, RateRank;

	END IF;


             

 

			
              insert ignore into tmp_VendorRate_
              select
                    distinct 
				 AccountId ,
				 AccountName ,
				 Code ,
				 Rate ,
                 ConnectionFee,                                                           
				 EffectiveDate ,
				 Description ,
				 Preference,
				 Code as RowCode
		   from tmp_VendorRateByRank_ 
 			  order by RowCode desc; 

			  
			  

     
                                   
		IF( p_Preference = 0 )
     	THEN
     			
			insert into tmp_final_VendorRate_
			SELECT
			     AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
					AccountId ,
				     AccountName ,
				     Code ,
				     Rate ,
                         ConnectionFee,
				     EffectiveDate ,
				     Description ,
				     Preference,
				     RowCode,
   			         @rank := CASE WHEN ( @prev_RowCode     = RowCode AND @prev_Rate <  Rate ) THEN @rank+1
                                   WHEN ( @prev_RowCode    = RowCode AND @prev_Rate = Rate ) THEN @rank 
						   ELSE 
						   1
						   END 
				  		  AS FinalRankNumber,
				  	@prev_RowCode  := RowCode,
					@prev_Rate  := Rate
					from tmp_VendorRate_ 
					,( SELECT @rank := 0 , @prev_RowCode := '' , @prev_Rate := 0 ) x 
				 	order by RowCode,Rate,AccountId ASC
				 
				) tbl1
				where 
				FinalRankNumber <= 5;
				 
		 ELSE 
		 
		 	 insert into tmp_final_VendorRate_
			SELECT
				AccountId ,
			     AccountName ,
			     Code ,
			     Rate ,
                    ConnectionFee,
			     EffectiveDate ,
			     Description ,
			     Preference, 
			     RowCode,
	  		     FinalRankNumber
				from 
				( 				
					SELECT
     					AccountId ,
     				     AccountName ,
     				     Code ,
     				     Rate ,
                             ConnectionFee,
     				     EffectiveDate ,
     				     Description ,
     				     Preference,
     				     RowCode,
                              @preference_rank := CASE WHEN (@prev_Code     = RowCode AND @prev_Preference > Preference  )   THEN @preference_rank + 1 
                                                       WHEN (@prev_Code     = RowCode AND @prev_Preference = Preference AND @prev_Rate < Rate) THEN @preference_rank + 1 
                                                       WHEN (@prev_Code    = RowCode AND @prev_Preference = Preference AND @prev_Rate = Rate) THEN @preference_rank 
                                                       ELSE 1 END AS FinalRankNumber,
                              @prev_Code := RowCode,
                              @prev_Preference := Preference,
                              @prev_Rate := Rate
					from tmp_VendorRate_ 
                          ,(SELECT @preference_rank := 0 , @prev_Code := ''  , @prev_Preference := 5,  @prev_Rate := 0) x
     			 	 order by RowCode ASC ,Preference DESC ,Rate ASC ,AccountId ASC
                         
				) tbl1
				where 
				FinalRankNumber <= 5;
		 
		 END IF;

          
        
          
         
 

    IF (p_isExport = 0)
    THEN 
   	
		
					SELECT
			           CONCAT(ANY_VALUE(t.RowCode) , ' : ' , ANY_VALUE(t.Description)) as Destination,
			          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 1, CONCAT(   ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 1`,
			          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 2, CONCAT(  ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName), '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 2`,
			          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 3, CONCAT(  ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 3`,
			          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 4, CONCAT(  ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 4`,
			          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 5, CONCAT(  ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 5`
			        FROM tmp_final_VendorRate_  t
			          GROUP BY  RowCode   
			          ORDER BY RowCode ASC
			     	LIMIT p_RowspPage OFFSET v_OffSet_ ;
     	
               
   
                select count(distinct RowCode) as totalcount from tmp_final_VendorRate_ where    ( CHAR_LENGTH(RTRIM(p_code)) = 0  OR RowCode LIKE REPLACE(p_code,'*', '%') )    ;
       
   ELSE 
		     SELECT
           CONCAT(ANY_VALUE(t.RowCode) , ' : ' , ANY_VALUE(t.Description)) as Destination,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 1, CONCAT(  ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 1`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 2, CONCAT(  ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName), '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 2`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 3, CONCAT(  ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 3`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 4, CONCAT(  ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 4`,
          GROUP_CONCAT(if(ANY_VALUE(FinalRankNumber) = 5, CONCAT(  ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `POSITION 5`
        FROM tmp_final_VendorRate_  t
          GROUP BY  RowCode   
          ORDER BY RowCode ASC;
     	
     	
     	
   END IF; 


SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetLCR__ORIG` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetLCR__ORIG`(IN `p_companyid` INT, IN `p_trunkID` INT, IN `p_codedeckID` INT, IN `p_contryID` INT, IN `p_code` VARCHAR(50), IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_SortOrder` VARCHAR(50), IN `p_Preference` INT, IN `p_isExport` INT)
BEGIN
    DECLARE v_OffSet_ int;
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
                
    SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
    
  Call fnVendorCurrentRates(p_companyid,p_codedeckID,p_trunkID,p_contryID, p_code,'0');
  DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_;
  CREATE TEMPORARY TABLE tmp_VendorRate_ (      
     AccountId INT ,
     AccountName VARCHAR(100) ,
     Code VARCHAR(50) ,
     Rate DECIMAL(18,6) ,
     EffectiveDate DATETIME ,
     Description VARCHAR(255),
     rankname INT
   );
        
               
       
    INSERT  INTO tmp_VendorRate_
                SELECT  vr.AccountID ,
                        vr.AccountName ,
                        vr.Code ,
                        vr.Rate ,
                        vr.EffectiveDate ,
                        vr.Description,
                        vr.rankname
                FROM    ( 

                        SELECT 
                                    Description ,
                                    AccountID ,
                                    AccountName ,
                                    Code ,
                                    Rate ,
                                    EffectiveDate ,
                                    case when p_Preference = 1
                                    then preference_rank
                                    else
                                        rankname
                                    end as rankname
                                    
                                FROM (
                                                SELECT 
                                                Description ,
                                                AccountID ,
                                                AccountName ,
                                                Code ,
                                                Preference,
                                                Rate,
                                                EffectiveDate,
                                                preference_rank,
                                                @rank := CASE WHEN (@prev_Code2  = Code  AND @prev_Rate2 <  Rate) THEN @rank+1
                                                	   		  WHEN (@prev_Code2  = Code  AND @prev_Rate2 =  Rate) THEN @rank
                                                				  ELSE
																					1
																				  END
																				  AS rankname,
                                                @prev_Code2  := Code,
                                                @prev_Rate2  := Rate
                                                FROM    (SELECT 
                                                    Description ,
                                                    AccountID ,
                                                    AccountName ,
                                                    Code ,
                                                    Preference,
                                                    Rate,
                                                    EffectiveDate,
                                                    @preference_rank := CASE WHEN (@prev_Code  = Code AND @prev_Preference = Preference AND @prev_Rate2 <  Rate) THEN @preference_rank+1
					                                                	   		  WHEN (@prev_Code  = Code  AND @prev_Preference = Preference AND @prev_Rate2 =  Rate) THEN @preference_rank
               					                                 				  ELSE
																										1
																									  END as preference_rank,
                                                    @prev_Code  := Code,
                                                    @prev_Preference  := Preference,
                                                    @prev_Rate  := Rate
                                                    FROM 
                                                    (SELECT DISTINCT
                                                                r.Description ,
                                                                v.AccountId,v.TrunkID,v.RateId ,
                                                                a.AccountName ,
                                                                r.Code ,
                                                                vp.Preference,
                                                                v.Rate,
                                                                v.EffectiveDate ,
                                                                @row_num := IF(@prev_AccountId=v.AccountId AND @prev_TrunkID=v.TrunkID AND @prev_RateId=v.RateId and @prev_EffectiveDate >= v.effectivedate ,@row_num+1,1) AS RowID,
                                                                @prev_AccountId  := v.AccountId,
                                                                @prev_TrunkID  := v.TrunkID,
                                                                @prev_RateId  := v.RateId,
                                                                @prev_EffectiveDate  := v.effectivedate
                                                      FROM      tblVendorRate AS v
                                                                INNER JOIN tblAccount a ON v.AccountId = a.AccountID
                                                                INNER JOIN tblRate r ON v.RateId = r.RateID and r.CodeDeckId = p_codedeckID
                                                                INNER JOIN  tmp_VendorCurrentRates_ AS e ON 
                                                                    v.AccountId = e.AccountID 
                                                                    AND v.TrunkID = e.TrunkID
                                                                    AND     v.RateId = e.RateId
                                                                    AND v.EffectiveDate = e.EffectiveDate
                                                                    AND v.Rate > 0
                                                             
                                                                LEFT JOIN tblVendorPreference vp 
                                                                    ON vp.AccountId = v.AccountId
                                                                    AND vp.TrunkID = v.TrunkID
                                                                    AND vp.RateId = v.RateId
                                                            ,(SELECT @row_num := 1) x,(SELECT @prev_AccountId := '') a,(SELECT @prev_TrunkID := '') b,(SELECT @prev_RateId := '') c,(SELECT @prev_EffectiveDate := '') d

                                                    ORDER BY v.AccountId,v.TrunkID,v.RateId,v.EffectiveDate DESC
                                                    
                                            )as preference,(SELECT @preference_rank := 1) x,(SELECT @prev_Code := '') a,(SELECT @prev_Preference := '') b,(SELECT @prev_Rate := '') c
                                            where preference.RowID = 1
                                            ORDER BY preference.Code ASC, preference.Preference desc, preference.Rate ASC
                                            
                                        )as rank ,(SELECT @rank := 1) x,(SELECT @prev_Code2 := '') d,(SELECT @prev_Rate2 := '') f
                                        ORDER BY rank.Code ASC, rank.Rate ASC
    
                                    ) tbl
                           
                        ) vr
                WHERE   vr.rankname <= 5 
               ORDER BY rankname; 

         IF p_isExport = 0
        THEN
        
          SELECT
            
        	 CONCAT(t.Code , ' : ' , ANY_VALUE(t.Description)) as Destination,
          GROUP_CONCAT(if(ANY_VALUE(rankname) = 1, CONCAT(ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `Position 1`,
          GROUP_CONCAT(if(ANY_VALUE(rankname) = 2, CONCAT(ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName), '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `Position 2`,
          GROUP_CONCAT(if(ANY_VALUE(rankname) = 3, CONCAT(ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `Position 3`,
          GROUP_CONCAT(if(ANY_VALUE(rankname) = 4, CONCAT(ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `Position 4`,
          GROUP_CONCAT(if(ANY_VALUE(rankname) = 5, CONCAT(ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `Position 5`
        FROM      tmp_VendorRate_ t
          GROUP BY  t.Code
          ORDER BY  CASE WHEN (p_SortOrder = 'DESC') THEN `Destination` END DESC,
                       CASE WHEN (p_SortOrder = 'ASC') THEN `Destination` END ASC
                        
          LIMIT p_RowspPage OFFSET v_OffSet_;     

         select count(*)as totalcount FROM(SELECT code
         from tmp_VendorRate_ 
         GROUP by Code)tbl;
    
    END IF;
    
    
    IF p_isExport = 1
    THEN

        SELECT  
        	 CONCAT(t.Code , ' : ' , ANY_VALUE(t.Description)) as Destination,
          GROUP_CONCAT(if(ANY_VALUE(rankname) = 1, CONCAT(ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `Position 1`,
          GROUP_CONCAT(if(ANY_VALUE(rankname) = 2, CONCAT(ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName), '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `Position 2`,
          GROUP_CONCAT(if(ANY_VALUE(rankname) = 3, CONCAT(ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `Position 3`,
          GROUP_CONCAT(if(ANY_VALUE(rankname) = 4, CONCAT(ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `Position 4`,
          GROUP_CONCAT(if(ANY_VALUE(rankname) = 5, CONCAT(ANY_VALUE(t.Rate), '<br>', ANY_VALUE(t.AccountName) , '<br>', DATE_FORMAT (ANY_VALUE(t.EffectiveDate), '%d/%m/%Y'),'<br>'), NULL))AS `Position 5`
        FROM tmp_VendorRate_  t
          GROUP BY  t.Code
          ORDER BY `Destination` ASC;
        
    END IF;
    
   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getMissingAccountsByGateway` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getMissingAccountsByGateway`(IN `p_CompanyID` INT, IN `p_CompanyGatewayID` INT, IN `p_ProcessID` VARCHAR(250), IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(5), IN `p_Export` INT)
BEGIN
     DECLARE v_OffSet_ int;
     
     SET sql_mode = '';
     SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
     SET SESSION sql_mode='';
	
	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

	if p_Export = 0
	THEN

		SELECT DISTINCT
				ta.tblTempAccountID,				
				ta.AccountName as AccountName,
				ta.FirstName as FirstName,
				ta.LastName as LastName,
				ta.Email as Email
				from tblTempAccount ta
				left join tblAccount a on ta.AccountName=a.AccountName
				 	AND ta.CompanyID = a.CompanyId
				 	AND ta.AccountType = a.AccountType
				where ta.CompanyID =p_CompanyID 								
				AND ta.AccountType=1
				AND ta.CompanyGatewayID = p_CompanyGatewayID
				AND ta.ProcessID = p_ProcessID
				AND a.AccountID is null
				group by ta.AccountName
				ORDER BY				
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'AccountNameDESC') THEN ta.AccountName
                END DESC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'AccountNameASC') THEN ta.AccountName
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'FirstNameDESC') THEN ta.FirstName
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'FirstNameASC') THEN ta.FirstName
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'LastNameDESC') THEN ta.LastName
                END DESC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'LastNameASC') THEN ta.LastName
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EmailDESC') THEN ta.Email
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EmailASC') THEN ta.Email
                END ASC
				LIMIT p_RowspPage OFFSET v_OffSet_;				
			
			select count(*) as totalcount from(
			SELECT 
				COUNT(ta.tblTempAccountID) as totalcount				
				from tblTempAccount ta
				left join tblAccount a on ta.AccountName=a.AccountName
					 AND ta.CompanyID = a.CompanyId
					 AND ta.AccountType = a.AccountType
				where ta.CompanyID =p_CompanyID 
				AND ta.AccountType=1
				AND ta.CompanyGatewayID = p_CompanyGatewayID
				AND ta.ProcessID = p_ProcessID
				AND a.AccountID is null
				group by ta.AccountName)tbl;

	ELSE

			SELECT DISTINCT				
				ta.AccountName as AccountName,
				ta.FirstName as FirstName,
				ta.LastName as LastName,
				ta.Email as Email
				from tblTempAccount ta
				left join tblAccount a on ta.AccountName=a.AccountName
					 AND ta.CompanyID = a.CompanyId
					 AND ta.AccountType = a.AccountType
				where ta.CompanyID =p_CompanyID 
				AND ta.AccountType=1
				AND ta.CompanyGatewayID = p_CompanyGatewayID
				AND ta.ProcessID = p_ProcessID
				AND a.AccountID is null
				group by ta.AccountName;

	END IF;
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getMissingAccountsOfQuickbook` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getMissingAccountsOfQuickbook`(IN `p_CompanyID` INT, IN `p_ProcessID` VARCHAR(250), IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(5), IN `p_Export` INT)
BEGIN
     DECLARE v_OffSet_ int;
     SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	      
	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

	if p_Export = 0
	THEN

		SELECT DISTINCT
				max(ta.tblTempAccountID) as tblTempAccountID,				
				ta.AccountName as AccountName,
				max(ta.FirstName) as FirstName,
				max(ta.LastName) as LastName,
				max(ta.Email) as Email
				from tblTempAccount ta
				left join tblAccount a on ta.AccountName=a.AccountName
				 	AND ta.CompanyID = a.CompanyId
				 	AND ta.AccountType = a.AccountType
				where ta.CompanyID =p_CompanyID 								
				AND ta.AccountType=1
				AND ta.LeadSource = 'QuickbookImport'
				AND ta.ProcessID = p_ProcessID
				AND a.AccountID is null
				group by ta.AccountName
				ORDER BY				
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'AccountNameDESC') THEN ta.AccountName
                END DESC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'AccountNameASC') THEN ta.AccountName
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'FirstNameDESC') THEN max(ta.FirstName)
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'FirstNameASC') THEN max(ta.FirstName)
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'LastNameDESC') THEN max(ta.LastName)
                END DESC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'LastNameASC') THEN max(ta.LastName)
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EmailDESC') THEN max(ta.Email)
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EmailASC') THEN max(ta.Email)
                END ASC
				LIMIT p_RowspPage OFFSET v_OffSet_;				
			
			select count(*) as totalcount from(
			SELECT 
				COUNT(ta.tblTempAccountID) as totalcount				
				from tblTempAccount ta
				left join tblAccount a on ta.AccountName=a.AccountName
					 AND ta.CompanyID = a.CompanyId
					 AND ta.AccountType = a.AccountType
				where ta.CompanyID =p_CompanyID 
				AND ta.AccountType=1
				AND ta.LeadSource = 'QuickbookImport'
				AND ta.ProcessID = p_ProcessID
				AND a.AccountID is null
				group by ta.AccountName)tbl;

	ELSE

			SELECT DISTINCT				
				ta.AccountName as AccountName,
				max(ta.FirstName) as FirstName,
				max(ta.LastName) as LastName,
				max(ta.Email) as Email
				from tblTempAccount ta
				left join tblAccount a on ta.AccountName=a.AccountName
					 AND ta.CompanyID = a.CompanyId
					 AND ta.AccountType = a.AccountType
				where ta.CompanyID =p_CompanyID 
				AND ta.AccountType=1
				AND ta.LeadSource = 'QuickbookImport'
				AND ta.ProcessID = p_ProcessID
				AND a.AccountID is null
				group by ta.AccountName;

	END IF;
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getMsgsDropdown` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getMsgsDropdown`(
	IN `p_CompanyId` int ,
	IN `p_userId` int ,
	IN `p_isadmin` int ,
	IN `p_reset` int 


)
BEGIN
   
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  
  
  IF p_reset = 1 
  THEN
    UPDATE `tblMessages` set `ShowInCounter` = '0' where `CompanyID` = p_CompanyId AND `ShowInCounter` = '1';
  END IF;
        SELECT  
			 `tblMessages`.`MsgID`, `tblMessages`.`Title`,  `tblMessages`.`HasRead`, `tblMessages`.`CreatedBy`, `tblMessages`.`created_at`,`tblMessages`.Description,tblMessages.EmailID
        FROM 
			`tblMessages` 
        WHERE
			`tblMessages`.`CompanyID` = p_CompanyId AND (p_isadmin = 1 OR (p_isadmin = 0 AND tblMessages.MsgLoggedUserID = p_userId))
        order by 
			`tblMessages`.`ShowInCounter` desc, `tblMessages`.`updated_at` desc,`tblMessages`.MsgID desc 
		limit 10;
		  
		SELECT  
			count(*) as totalNonVisitedJobs
        FROM
			`tblMessages` 
			INNER JOIN AccountEmailLog ae
		    ON ae.AccountEmailLogID = tblMessages.EmailID
        WHERE
			`tblMessages`.`CompanyID` = p_CompanyId AND (p_isadmin = 1 OR (p_isadmin = 0 AND tblMessages.MsgLoggedUserID = p_userId))
			AND ae.EmailCall = 1 AND  tblMessages.HasRead=0
        order by
			`tblMessages`.`ShowInCounter` desc, `tblMessages`.`updated_at` desc,`tblMessages`.MsgID desc;
	 
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetOpportunities` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetOpportunities`(IN `p_CompanyID` INT, IN `p_BoardID` INT, IN `p_OpportunityName` VARCHAR(50), IN `p_Tags` VARCHAR(50), IN `p_OwnerID` VARCHAR(100), IN `p_AccountID` INT, IN `p_Status` VARCHAR(50), IN `p_CurrencyID` INT, IN `p_OpportunityClosed` INT)
BEGIN
 
	DECLARE v_WorthTotal DECIMAL(18,8);
	DECLARE v_Round_ int;
	DECLARE v_CurrencyCode_ VARCHAR(50);
	DECLARE v_Active_ int;
	SET v_Active_ = 1;
	SELECT fnGetRoundingPoint(p_CompanyID) INTO v_Round_;
	SELECT cr.Symbol INTO v_CurrencyCode_ from tblCurrency cr where cr.CurrencyId = p_CurrencyID;
		
 DROP TEMPORARY TABLE IF EXISTS tmp_Oppertunites_;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_Oppertunites_(
		BoardColumnID int,
		BoardColumnName varchar(255),
		Height varchar(10),
		Width varchar(10),
		OpportunityID int,
		OpportunityName varchar(255),
		BackGroundColour varchar(10),
		TextColour varchar(10),
		Company varchar(100),
		Title varchar(10),
		FirstName varchar(50),
		LastName varchar(50),
		Owner varchar(100),
		UserID int,
		Phone varchar(50),
		Email varchar(255),
		BoardID int,
		AccountID int,
		Tags varchar(255),
		Rating int,
		TaggedUsers varchar(100),
		`Status` int,
 	   Worth DECIMAL(18,8),
		OpportunityClosed int,
		ClosingDate varchar(15),
		ExpectedClosing varchar(15),
		StartTime varchar(15)
);
  
		INSERT INTO tmp_Oppertunites_
		SELECT 
				bc.BoardColumnID,
				bc.BoardColumnName,
				bc.Height,
				bc.Width,
				o.OpportunityID,
				o.OpportunityName,
				o.BackGroundColour,
				o.TextColour,
				o.Company,
				o.Title,
				o.FirstName,
				o.LastName,
				concat(u.FirstName,concat(' ',u.LastName)) as Owner,
				o.UserID,
				o.Phone,
				o.Email,
				b.BoardID,
				o.AccountID,
				o.Tags,
				o.Rating,
				o.TaggedUsers,
				o.`Status`,
			   o.Worth,
			   o.OpportunityClosed,
			   Date(o.ClosingDate) as ClosingDate,
			   Date(o.ExpectedClosing) as ExpectedClosing,
				Time(o.ExpectedClosing) as StartTime		
		FROM tblCRMBoards b
		INNER JOIN tblCRMBoardColumn bc on bc.BoardID = b.BoardID AND b.BoardID = p_BoardID
		LEFT JOIN tblOpportunity o on o.BoardID = b.BoardID
					AND o.BoardColumnID = bc.BoardColumnID
					AND o.CompanyID = p_CompanyID
					AND (o.OpportunityClosed = p_OpportunityClosed)
					AND (p_Tags = '' OR find_in_set(o.Tags,p_Tags))
					AND (p_OpportunityName = '' OR o.OpportunityName LIKE Concat('%',p_OpportunityName,'%'))
					AND (p_OwnerID = '' OR o.UserID = p_OwnerID)
					AND (p_AccountID = 0 OR o.AccountID = p_AccountID)
					AND (p_Status = '' OR find_in_set(o.`Status`,p_Status))
					AND (p_CurrencyID = 0 OR p_CurrencyID in (Select CurrencyId FROM tblAccount Where tblAccount.AccountID = o.AccountID))
					AND (v_Active_ = (Select `Status` FROM tblAccount Where tblAccount.AccountID = o.AccountID limit 1))
					AND (v_Active_ = (Select `Status` FROM tblUser Where tblUser.UserID = o.UserID limit 1))

	 	LEFT JOIN tblUser u on u.UserID = o.UserID
		ORDER BY bc.`Order`,o.`Order`; 

SELECT sum(Worth) as TotalWorth INTO v_WorthTotal from tmp_Oppertunites_;


 SELECT		*,	           
			ROUND(IFNULL(v_WorthTotal,0.00),v_Round_) as WorthTotal,
			v_CurrencyCode_ as CurrencyCode			
        FROM tmp_Oppertunites_ ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetOpportunityGrid` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetOpportunityGrid`(IN `p_CompanyID` INT, IN `p_BoardID` INT, IN `p_OpportunityName` VARCHAR(50), IN `p_Tags` VARCHAR(50), IN `p_OwnerID` VARCHAR(50), IN `p_AccountID` INT, IN `p_Status` VARCHAR(50), IN `p_CurrencyID` INT, IN `p_OpportunityClosed` INT, IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(50))
BEGIN
 DECLARE v_OffSet_ int;
 DECLARE v_Round_ int;
 
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
   SET SESSION group_concat_max_len = 1024;
     
 SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;


	SELECT fnGetRoundingPoint(p_CompanyID) INTO v_Round_;
SELECT 
  bc.BoardColumnID,
  bc.BoardColumnName,
  o.OpportunityID,
  o.OpportunityName,
  o.BackGroundColour,
  o.TextColour,
  o.Company,
  o.Title,
  o.FirstName,
  o.LastName,
  concat(u.FirstName,concat(' ',u.LastName)) as Owner,
  o.UserID,
  o.Phone,
  o.Email,
  b.BoardID,
  o.AccountID,
  o.Tags,
  o.Rating,
  o.TaggedUsers,
  o.`Status`,
     ROUND(o.Worth,v_Round_) as Worth,
     o.OpportunityClosed,
     Date(o.ClosingDate) as ClosingDate,
     Date(o.ExpectedClosing) as ExpectedClosing,
  Time(o.ExpectedClosing) as StartTime
FROM tblCRMBoards b
INNER JOIN tblCRMBoardColumn bc on bc.BoardID = b.BoardID
   AND (p_BoardID = 0 OR b.BoardID = p_BoardID)
INNER JOIN tblOpportunity o on o.BoardID = b.BoardID
   AND o.BoardColumnID = bc.BoardColumnID
   AND o.CompanyID = p_CompanyID
   AND (o.OpportunityClosed = p_OpportunityClosed)
   AND (p_Tags = '' OR find_in_set(o.Tags,p_Tags))
   AND (p_OpportunityName = '' OR o.OpportunityName LIKE Concat('%',p_OpportunityName,'%'))
   AND (p_OwnerID = '' OR find_in_set(o.`UserID`,p_OwnerID))
   AND (p_AccountID = 0 OR o.AccountID = p_AccountID)
   AND (p_Status = '' OR find_in_set(o.`Status`,p_Status))
INNER JOIN tblAccount ac on o.AccountID = ac.AccountID
 AND ac.`Status` = 1
 AND (p_CurrencyID = 0 OR ac.CurrencyId = p_CurrencyID) 
INNER JOIN tblUser u on o.UserID = u.UserID
	AND u.`Status` = 1
ORDER BY
  CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'OpportunityNameDESC') THEN o.OpportunityName
   END DESC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'OpportunityNameASC') THEN o.OpportunityName
   END ASC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RatingDESC') THEN o.Rating
   END DESC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RatingASC') THEN o.Rating
   END ASC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'StatusDESC') THEN o.`Status`
   END DESC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'StatusASC') THEN o.`Status`
   END ASC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'UserIDDESC') THEN concat(u.FirstName,' ',u.LastName)
   END DESC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'UserIDASC') THEN concat(u.FirstName,' ',u.LastName)
   END ASC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RelatedToDESC') THEN o.Company
   END DESC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RelatedToASC') THEN o.Company
   END ASC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ExpectedClosingASC') THEN o.ExpectedClosing
   END ASC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ExpectedClosingDESC') THEN o.ExpectedClosing
   END DESC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ValueDESC') THEN o.Worth
   END DESC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ValueASC') THEN o.Worth
   END ASC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RatingDESC') THEN o.Rating
   END DESC,
   CASE
       WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RatingASC') THEN o.Rating
   END ASC
  LIMIT p_RowspPage OFFSET v_OffSet_;
  
  SELECT 
  count(*) as totalcount
FROM tblCRMBoards b
INNER JOIN tblCRMBoardColumn bc on bc.BoardID = b.BoardID
  AND (p_BoardID = 0 OR b.BoardID = p_BoardID)
INNER JOIN tblOpportunity o on o.BoardID = b.BoardID
   AND o.BoardColumnID = bc.BoardColumnID
   AND o.CompanyID = p_CompanyID
   AND (o.OpportunityClosed = p_OpportunityClosed)
   AND (p_Tags = '' OR find_in_set(o.Tags,p_Tags))
   AND (p_OpportunityName = '' OR o.OpportunityName LIKE Concat('%',p_OpportunityName,'%'))
   AND (p_OwnerID = '' OR find_in_set(o.`UserID`,p_OwnerID))
   AND (p_AccountID = 0 OR o.AccountID = p_AccountID)
   AND (p_Status = '' OR find_in_set(o.`Status`,p_Status))
   AND (p_CurrencyID = 0 OR p_CurrencyID in (Select CurrencyId FROM tblAccount Where tblAccount.AccountID = o.AccountID))
   AND (1 in (Select `Status` FROM tblAccount Where tblAccount.AccountID = o.AccountID))
   AND (1 in (Select `Status` FROM tblUser Where tblUser.UserID = o.UserID))
INNER JOIN tblAccount ac on o.AccountID = ac.AccountID
 AND ac.`Status` = 1
 AND (p_CurrencyID = 0 OR ac.CurrencyId = p_CurrencyID) 
INNER JOIN tblUser u on o.UserID = u.UserID
	AND u.`Status` = 1;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetRateTableRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetRateTableRate`(
	IN `p_companyid` INT,
	IN `p_RateTableId` INT,
	IN `p_trunkID` INT,
	IN `p_contryID` INT,
	IN `p_code` VARCHAR(50),
	IN `p_description` VARCHAR(50),
	IN `p_effective` VARCHAR(50),
	IN `p_PageNumber` INT,
	IN `p_RowspPage` INT,
	IN `p_lSortCol` VARCHAR(50),
	IN `p_SortOrder` VARCHAR(5),
	IN `p_isExport` INT
)
BEGIN
	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
	
	DROP TEMPORARY TABLE IF EXISTS tmp_RateTableRate_;
   CREATE TEMPORARY TABLE tmp_RateTableRate_ (
        ID INT,
        Code VARCHAR(50),
        Description VARCHAR(200),
        Interval1 INT,
        IntervalN INT,
		  ConnectionFee DECIMAL(18, 6),
        Rate DECIMAL(18, 6),
        EffectiveDate DATE,
        updated_at DATETIME,
        ModifiedBy VARCHAR(50),
        RateTableRateID INT,
        RateID INT,
        INDEX tmp_RateTableRate_RateID (`RateID`)
    );
    
    
    
    INSERT INTO tmp_RateTableRate_
    SELECT
        RateTableRateID AS ID,
        Code,
        Description,
        ifnull(tblRateTableRate.Interval1,1) as Interval1,
        ifnull(tblRateTableRate.IntervalN,1) as IntervalN,
		  tblRateTableRate.ConnectionFee,
        IFNULL(tblRateTableRate.Rate, 0) as Rate,
        IFNULL(tblRateTableRate.EffectiveDate, NOW()) as EffectiveDate,
        tblRateTableRate.updated_at,
        tblRateTableRate.ModifiedBy,
        RateTableRateID,
        tblRate.RateID
    FROM tblRate
    LEFT JOIN tblRateTableRate
        ON tblRateTableRate.RateID = tblRate.RateID
        AND tblRateTableRate.RateTableId = p_RateTableId
    INNER JOIN tblRateTable
        ON tblRateTable.RateTableId = tblRateTableRate.RateTableId
    WHERE		(tblRate.CompanyID = p_companyid)
		AND (p_contryID = '' OR CountryID = p_contryID)
		AND (p_code = '' OR Code LIKE REPLACE(p_code, '*', '%'))
		AND (p_description = '' OR Description LIKE REPLACE(p_description, '*', '%'))
		AND TrunkID = p_trunkID
		AND (			
			p_effective = 'All' 
		OR (p_effective = 'Now' AND EffectiveDate <= NOW() )
		OR (p_effective = 'Future' AND EffectiveDate > NOW())
			);
	 
	  IF p_effective = 'Now'
		THEN
		   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_RateTableRate4_ as (select * from tmp_RateTableRate_);	        
         DELETE n1 FROM tmp_RateTableRate_ n1, tmp_RateTableRate4_ n2 WHERE n1.EffectiveDate < n2.EffectiveDate 
		   AND  n1.RateID = n2.RateID;
		END IF;
	 
    IF p_isExport = 0
    THEN

       SELECT * FROM tmp_RateTableRate_
					ORDER BY CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CodeDESC') THEN Code
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CodeASC') THEN Code
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionDESC') THEN Description
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionASC') THEN Description
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RateDESC') THEN Rate
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RateASC') THEN Rate
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'Interval1DESC') THEN Interval1
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'Interval1ASC') THEN Interval1
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'IntervalNDESC') THEN Interval1
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'IntervalNASC') THEN Interval1
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EffectiveDateDESC') THEN EffectiveDate
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EffectiveDateASC') THEN EffectiveDate
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'updated_atDESC') THEN updated_at
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'updated_atASC') THEN updated_at
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'updated_byDESC') THEN ModifiedBy
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'updated_byASC') THEN ModifiedBy
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RateTableRateIDDESC') THEN RateTableRateID
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RateTableRateIDASC') THEN RateTableRateID
                END ASC,				
				    CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ConnectionFeeDESC') THEN ConnectionFee
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ConnectionFeeASC') THEN ConnectionFee
                END ASC
		LIMIT p_RowspPage OFFSET v_OffSet_;



        SELECT
            COUNT(RateID) AS totalcount
        FROM tmp_RateTableRate_;

        


    END IF;

    IF p_isExport = 1
    THEN

        SELECT
            Code,
            Description,
            Interval1,
            IntervalN,
            ConnectionFee,
            Rate,
            EffectiveDate,
            updated_at,
            ModifiedBy

        FROM   tmp_RateTableRate_;
         

    END IF;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetRecentDueSheet` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetRecentDueSheet`(IN `p_companyId` INT , IN `p_isAdmin` INT , IN `p_AccountType` INT, IN `p_DueDate` VARCHAR(10), IN `p_PageNumber` INT, IN `p_RowspPage` INT, IN `p_lSortCol` VARCHAR(50), IN `p_SortOrder` VARCHAR(5), IN `p_isExport` INT 
)
BEGIN
	
	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;


    DROP TEMPORARY TABLE IF EXISTS tmp_DueSheets_;
    CREATE TEMPORARY TABLE tmp_DueSheets_ (
        AccountID INT,
        AccountName VARCHAR(100),
        Trunk VARCHAR(50),
        EffectiveDate DATE
    );


    IF p_AccountType = 1
    THEN
	  INSERT INTO tmp_DueSheets_
            SELECT DISTINCT
                TBL.AccountID,
                TBL.AccountName,
                TBL.Trunk,
                TBL.EffectiveDate
            FROM (SELECT DISTINCT
                    a.AccountID,
                    a.AccountName,
                    t.Trunk,
					t.TrunkID,
                    vr.EffectiveDate,
					TIMESTAMPDIFF(DAY, vr.EffectiveDate, DATE_FORMAT(NOW(),'%Y-%m-%d')) AS DAYSDIFF
                FROM tblVendorRate vr
				inner join tblVendorTrunk vt on vt.AccountID = vr.AccountId and vt.TrunkID = vr.TrunkID and vt.Status =1
                INNER JOIN tblRate r
                    ON r.RateID = vr.RateID
                INNER JOIN tblCountry c
                    ON c.CountryID = r.CountryID
                INNER JOIN tblAccount a
                    ON a.AccountID = vr.AccountId
                INNER JOIN tblTrunk t
                    ON t.TrunkID = vr.TrunkID
                WHERE a.CompanyId = p_companyId

				AND (
						(p_DueDate = 'Today' AND TIMESTAMPDIFF(DAY, vr.EffectiveDate, NOW()) = 0)
							OR
						(p_DueDate = 'Tomorrow' AND TIMESTAMPDIFF(DAY, vr.EffectiveDate, NOW()) = -1)
							OR
						(p_DueDate = 'Yesterday' AND TIMESTAMPDIFF(DAY, vr.EffectiveDate, NOW()) = 1)
					)
				AND  ((p_isAdmin <> 0 AND a.Owner = p_isAdmin) OR  (p_isAdmin = 0))

				) AS TBL

				Left JOIN tblJob ON tblJob.AccountID = TBL.AccountID
					AND tblJob.JobID IN(select JobID from tblJob where JobTypeID = (select JobTypeID from tblJobType where Code='VD'))
					AND FIND_IN_SET (TBL.TrunkID,JSON_EXTRACT(tblJob.Options, '$.Trunks')) != 0
					AND (
					(p_DueDate = 'Today' AND TIMESTAMPDIFF(DAY, tblJob.created_at, NOW()) = 0)
					OR
					(p_DueDate = 'Yesterday' AND TIMESTAMPDIFF(DAY, tblJob.created_at, NOW()) = DAYSDIFF or TIMESTAMPDIFF(DAY, tblJob.created_at, EffectiveDate)  BETWEEN -1 AND 0)
					)
					where JobStatusID is null or JobStatusID <> (select JobStatusID from tblJobStatus where Code='S');
    END IF;

    IF p_AccountType = 2
    THEN
	  INSERT INTO tmp_DueSheets_
            SELECT  DISTINCT
                TBL.AccountID,
                TBL.AccountName,
                TBL.Trunk,
                TBL.EffectiveDate
            FROM (SELECT DISTINCT
                a.AccountID,
                a.AccountName,
                t.Trunk,
				t.TrunkID,
                cr.EffectiveDate,
				TIMESTAMPDIFF(DAY, cr.EffectiveDate, DATE_FORMAT(NOW(),'%Y-%m-%d')) AS DAYSDIFF
            FROM tblCustomerRate cr
			inner join tblCustomerTrunk ct on ct.AccountID = cr.CustomerID and ct.TrunkID = cr.TrunkID and ct.Status =1
            INNER JOIN tblRate r
                ON r.RateID = cr.RateID
            INNER JOIN tblCountry c
                ON c.CountryID = r.CountryID
            INNER JOIN tblAccount a
                ON a.AccountID = cr.CustomerID
            INNER JOIN tblTrunk t
                ON t.TrunkID = cr.TrunkID
            WHERE a.CompanyId = p_companyId
				AND (
						(p_DueDate = 'Today' AND TIMESTAMPDIFF(DAY, cr.EffectiveDate, NOW()) = 0)
							OR
						(p_DueDate = 'Tomorrow' AND TIMESTAMPDIFF(DAY, cr.EffectiveDate, NOW()) = -1)
							OR
						(p_DueDate = 'Yesterday' AND TIMESTAMPDIFF(DAY, cr.EffectiveDate, NOW()) = 1)
					)
					AND  ((p_isAdmin <> 0 AND a.Owner = p_isAdmin) OR  (p_isAdmin = 0))
			) AS TBL

			LEFT JOIN tblJob ON tblJob.AccountID = TBL.AccountID
					AND tblJob.JobID IN(select JobID from tblJob where JobTypeID = (select JobTypeID from tblJobType where Code='CD'))
					AND FIND_IN_SET (TBL.TrunkID,JSON_EXTRACT(tblJob.Options, '$.Trunks')) != 0
					AND (
					(p_DueDate = 'Today' AND TIMESTAMPDIFF(DAY, tblJob.created_at, NOW()) = 0)
					OR
					(p_DueDate = 'Yesterday' AND TIMESTAMPDIFF(DAY, tblJob.created_at, NOW()) = DAYSDIFF or TIMESTAMPDIFF(DAY, tblJob.created_at, EffectiveDate)  BETWEEN -1 AND 0)
					)
						where JobStatusID is null or JobStatusID <> (select JobStatusID from tblJobStatus where Code='S');

    END IF;

    IF p_isExport = 0
    THEN
        SELECT
            AccountName,
            Trunk,
            EffectiveDate,
            AccountID
        FROM tmp_DueSheets_ AS DueSheets
        ORDER BY
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'AccountNameDESC') THEN AccountName
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'AccountNameASC') THEN AccountName
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TrunkDESC') THEN Trunk
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'TrunkASC') THEN Trunk
                END ASC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EffectiveDateDESC') THEN EffectiveDate
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EffectiveDateASC') THEN EffectiveDate
                END ASC
			LIMIT p_RowspPage OFFSET v_OffSet_;


		SELECT count(AccountName) as totalcount
      FROM tmp_DueSheets_ AS DueSheets;
    END IF;

    IF p_isExport = 1
    THEN
        SELECT
            AccountName,
            Trunk,
            EffectiveDate
        FROM tmp_DueSheets_ TBL;
    END IF;
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetRecentDueSheetCronJob` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetRecentDueSheetCronJob`(IN `p_companyId` INT)
BEGIN
	
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

            SELECT  DISTINCT
                TBL.AccountID,
                TBL.AccountName,
                TBL.Trunk,
                TBL.EffectiveDate,
				TBL.Owner,
				DAYSDIFF,
				'vender' as type
            FROM (SELECT DISTINCT
                    a.AccountID,
                    a.AccountName,
                    t.Trunk,
					t.TrunkID,
                    vr.EffectiveDate,
					a.Owner,
					TIMESTAMPDIFF(DAY,vr.EffectiveDate, NOW()) as DAYSDIFF
                FROM tblVendorRate vr
				inner join tblVendorTrunk vt on vt.AccountID = vr.AccountId and vt.TrunkID = vr.TrunkID and vt.Status =1
                INNER JOIN tblRate r
                    ON r.RateID = vr.RateID
                INNER JOIN tblCountry c
                    ON c.CountryID = r.CountryID
                INNER JOIN tblAccount a
                    ON a.AccountID = vr.AccountId
                INNER JOIN tblTrunk t
                    ON t.TrunkID = vr.TrunkID
                WHERE a.CompanyId = p_companyId

				AND TIMESTAMPDIFF(DAY, vr.EffectiveDate, DATE_FORMAT (NOW(),'%Y-%m-%d')) BETWEEN -1 AND 1

				) AS TBL

				Left JOIN tblJob ON tblJob.AccountID = TBL.AccountID
					AND tblJob.JobTypeID = (select JobTypeID from tblJobType where Code='VD')
					AND FIND_IN_SET (TBL.TrunkID,JSON_EXTRACT(tblJob.Options, '$.Trunks')) != 0
					AND ( TIMESTAMPDIFF(DAY, tblJob.created_at, NOW()) = DAYSDIFF or TIMESTAMPDIFF(DAY, tblJob.created_at, EffectiveDate)  BETWEEN -1 AND 0)
					where JobStatusID is null or JobStatusID <> (select JobStatusID from tblJobStatus where Code='S')

						UNION ALL

            SELECT  DISTINCT
                TBL.AccountID,
                TBL.AccountName,
                TBL.Trunk,
                TBL.EffectiveDate,
				tbl.Owner,
				DAYSDIFF,
				'customer' as type
            FROM (SELECT DISTINCT
                a.AccountID,
                a.AccountName,
				a.Owner,
                t.Trunk,
				t.TrunkID,
                cr.EffectiveDate,
				TIMESTAMPDIFF(DAY,cr.EffectiveDate, NOW()) as DAYSDIFF
            FROM tblCustomerRate cr
			inner join tblCustomerTrunk ct on ct.AccountID = cr.CustomerID and ct.TrunkID = cr.TrunkID and ct.Status =1
            INNER JOIN tblRate r
                ON r.RateID = cr.RateID
            INNER JOIN tblCountry c
                ON c.CountryID = r.CountryID
            INNER JOIN tblAccount a
                ON a.AccountID = cr.CustomerID
            INNER JOIN tblTrunk t
                ON t.TrunkID = cr.TrunkID
            WHERE a.CompanyId = p_companyId
				AND TIMESTAMPDIFF(DAY, cr.EffectiveDate, DATE_FORMAT (NOW(),'%Y-%m-%d')) BETWEEN -1 AND 1
			) AS TBL

			LEFT JOIN tblJob ON tblJob.AccountID = TBL.AccountID
					AND tblJob.JobTypeID = (select JobTypeID from tblJobType where Code='CD')
					AND FIND_IN_SET (TBL.TrunkID,JSON_EXTRACT(tblJob.Options, '$.Trunks')) != 0
					AND ( TIMESTAMPDIFF(DAY, tblJob.created_at, NOW()) = DAYSDIFF or TIMESTAMPDIFF(DAY, tblJob.created_at, EffectiveDate)  BETWEEN -1 AND 0)

						where JobStatusID is null or JobStatusID <> (select JobStatusID from tblJobStatus where Code='S')
						order by Owner,DAYSDIFF,type ASC;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetSkipResourceList` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetSkipResourceList`(IN `p_CompanyID` INT)
BEGIN

    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  
	
	select distinct r.ResourceID,r.ResourceValue,
		case when cs.CompanyID>0
		then
		1
		else
		null
		end As Checked 
	From `tblResource` r left join  `tblCompanySetting` cs on (FIND_IN_SET(r.ResourceID,cs.`Value`) != 0 ) and  r.CompanyID=cs.CompanyID and cs.`Key`='SkipPermissionAction'
		where r.CompanyID=p_CompanyID 
	order by
		 case when cs.CompanyID>0
	 then
		1
     else
	    null
     end
	  desc, r.ResourceValue asc;	
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetTasksBoard` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetTasksBoard`(
	IN `p_CompanyID` INT,
	IN `p_BoardID` INT,
	IN `p_TaskName` VARCHAR(50),
	IN `p_UserIDs` VARCHAR(50),
	IN `p_AccountIDs` INT,
	IN `p_Periority` INT,
	IN `p_DueDateFrom` VARCHAR(50),
	IN `p_DueDateTo` VARCHAR(50),
	IN `p_Status` INT,
	IN `p_Closed` INT



)
BEGIN
	DECLARE v_Actve_ int;
	SELECT 1 INTO v_Actve_;
	SELECT 
		bc.BoardColumnID,
		bc.BoardColumnName,
		bc.SetCompleted,
		ts.TaskID,
		ts.UsersIDs,
		(select GROUP_CONCAT( concat(u.FirstName,' ',u.LastName) SEPARATOR ', ') as Users from tblUser u where u.UserID = ts.UsersIDs) as Users,
		ts.AccountIDs,
		(select a.AccountName as company from tblAccount a where a.AccountID = ts.AccountIDs) as company,
		ts.Subject,
		ts.Description,
		Date(ts.DueDate) as DueDate,
		Time(ts.DueDate) as StartTime,
		ts.BoardColumnID as TaskStatus,
		ts.Priority,
		ts.Tags,
		ts.TaggedUsers,
		ts.BoardID,
		concat( u.FirstName,' ',u.LastName) as userName,
		ts.taskClosed
	FROM tblCRMBoards b
	INNER JOIN tblCRMBoardColumn bc on bc.BoardID = b.BoardID
			AND b.BoardID = p_BoardID
	LEFT JOIN tblTask ts on ts.BoardID = b.BoardID
		AND ts.BoardColumnID = bc.BoardColumnID
		AND ts.CompanyID= p_CompanyID
		AND (ts.taskClosed=p_Closed)
		AND (p_TaskName='' OR ts.Subject LIKE Concat('%',p_TaskName,'%'))
		AND (p_UserIDs=0 OR  FIND_IN_SET (ts.UsersIDs,p_UserIDs))
		AND (p_AccountIDs=0 OR ts.AccountIDs=p_AccountIDs)
		AND (p_Periority=0 OR ts.Priority = p_Periority) 
		AND (p_Status=0 OR ts.BoardColumnID=p_Status)
		AND (p_DueDateFrom=0 
				OR (p_DueDateFrom=1 AND (ts.DueDate !='0000-00-00 00:00:00' AND ts.DueDate < NOW() AND bc.SetCompleted=0))
				OR (p_DueDateFrom=2 AND (ts.DueDate !='0000-00-00 00:00:00' AND ts.DueDate >= NOW() AND ts.DueDate <= DATE(DATE_ADD(NOW(), INTERVAL +3 DAY)))) 
				OR ((p_DueDateFrom!='' OR p_DueDateTo!='') AND ts.DueDate BETWEEN STR_TO_DATE(p_DueDateFrom,'%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(p_DueDateTo,'%Y-%m-%d %H:%i:%s'))
			)
		AND (v_Actve_ = (Select MAX(`Status`) FROM tblUser Where tblUser.UserID = ts.UsersIDs))
	LEFT JOIN tblUser u on u.UserID = ts.UsersIDs 
	ORDER BY bc.`Order`,ts.`Order`;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetTasksGrid` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetTasksGrid`(
	IN `p_CompanyID` INT,
	IN `p_BoardID` INT,
	IN `p_TaskName` VARCHAR(50),
	IN `p_UserIDs` VARCHAR(50),
	IN `p_AccountIDs` INT,
	IN `p_Periority` INT,
	IN `p_DueDateFrom` VARCHAR(50),
	IN `p_DueDateTo` VARCHAR(50),
	IN `p_Status` INT,
	IN `p_Closed` INT,
	IN `p_PageNumber` INT,
	IN `p_RowspPage` INT,
	IN `p_lSortCol` VARCHAR(50),
	IN `p_SortOrder` VARCHAR(50)






)
BEGIN 
	DECLARE v_OffSet_ int;
	DECLARE v_Active_ int;
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
   SET SESSION group_concat_max_len = 1024;
   SET v_Active_=1;
	    
	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
		
		SELECT 
		bc.BoardColumnID,
		bc.BoardColumnName,
		bc.SetCompleted,
		ts.TaskID,
		ts.UsersIDs,
		(select GROUP_CONCAT( concat(u.FirstName,' ',u.LastName) SEPARATOR ', ') as Users from tblUser u where u.UserID = ts.UsersIDs) as Users,
		ts.AccountIDs,
		(select a.AccountName as company from tblAccount a where a.AccountID = ts.AccountIDs) as company,
		ts.Subject,
		ts.Description,
		Date(ts.DueDate) as DueDate,
		Time(ts.DueDate) as StartTime,
		ts.BoardColumnID as TaskStatus,
		ts.Priority,
		CASE 	WHEN ts.Priority=1 THEN 'High'
			 	WHEN ts.Priority=2 THEN 'Medium'
				WHEN ts.Priority=3 THEN 'Low'
		END as PriorityText,				
		ts.TaggedUsers,
		ts.BoardID,
      concat( u.FirstName,' ',u.LastName) as userName,
      ts.taskClosed
		FROM tblCRMBoards b
		INNER JOIN tblCRMBoardColumn bc on bc.BoardID = b.BoardID
				AND b.BoardID = p_BoardID
		INNER JOIN tblTask ts on ts.BoardID = b.BoardID 
		AND ts.BoardColumnID = bc.BoardColumnID
		AND ts.CompanyID= p_CompanyID
		AND (ts.taskClosed=p_Closed)
		AND (p_TaskName='' OR ts.Subject LIKE Concat('%',p_TaskName,'%'))
		AND (p_UserIDs=0 OR  FIND_IN_SET (ts.UsersIDs,p_UserIDs))
		AND (p_AccountIDs=0 OR ts.AccountIDs=p_AccountIDs)
		AND (p_Periority=0 OR ts.Priority = p_Periority) 
		AND (p_Status=0 OR ts.BoardColumnID=p_Status )
		AND (p_DueDateFrom=0 
				OR (p_DueDateFrom=1 AND (ts.DueDate !='0000-00-00 00:00:00' AND ts.DueDate < NOW() AND bc.SetCompleted=0))
				OR (p_DueDateFrom=2 AND (ts.DueDate !='0000-00-00 00:00:00' AND ts.DueDate >= NOW() AND ts.DueDate <= DATE(DATE_ADD(NOW(), INTERVAL +3 DAY)))) 
				OR ((p_DueDateFrom!='' OR p_DueDateTo!='') AND ts.DueDate BETWEEN STR_TO_DATE(p_DueDateFrom,'%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(p_DueDateTo,'%Y-%m-%d %H:%i:%s'))
			)
		AND (v_Active_ = (Select `Status` FROM tblUser Where tblUser.UserID = ts.UsersIDs limit 1))
		LEFT JOIN tblUser u on u.UserID = ts.UsersIDs
		ORDER BY
		CASE
		     WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'SubjectDESC') THEN ts.Subject
		 END DESC,
		 CASE
		     WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'SubjectASC') THEN ts.Subject
		 END ASC,
		 CASE
		     WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DueDateDESC') THEN ts.DueDate
		 END DESC,
		 CASE
		     WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DueDateASC') THEN ts.DueDate
		 END ASC,
		 CASE
		     WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'StatusDESC') THEN bc.`Order`
		 END DESC,
		 CASE
		     WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'StatusASC') THEN bc.`Order`
		 END ASC,
		 CASE
		     WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'UserIDDESC') THEN concat(u.FirstName,' ',u.LastName)
		 END DESC,
		 CASE
		     WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'UserIDASC') THEN concat(u.FirstName,' ',u.LastName)
		 END ASC,
		 CASE
		     WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RelatedToDESC') THEN ts.AccountIDs
		 END DESC,
		 CASE
		     WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RelatedToASC') THEN ts.AccountIDs
		 END ASC
		LIMIT p_RowspPage OFFSET v_OffSet_;
		
		SELECT COUNT(ts.TaskID) as totalcount
		FROM tblCRMBoards b
		INNER JOIN tblCRMBoardColumn bc on bc.BoardID = b.BoardID
				AND b.BoardID = p_BoardID
		INNER JOIN tblTask ts on ts.BoardID = b.BoardID 
		AND ts.BoardColumnID = bc.BoardColumnID
		AND ts.CompanyID= p_CompanyID
		AND (ts.taskClosed=p_Closed)
		AND (p_TaskName='' OR ts.Subject LIKE Concat('%',p_TaskName,'%'))
		AND (p_UserIDs=0 OR  FIND_IN_SET (ts.UsersIDs,p_UserIDs))
		AND (p_Periority=0 OR ts.Priority = p_Periority) 
		AND (p_Status=0 OR ts.BoardColumnID=p_Status )
		AND (p_DueDateFrom=0 
				OR (p_DueDateFrom=1 AND (ts.DueDate !='0000-00-00 00:00:00' AND ts.DueDate < NOW() AND bc.SetCompleted=0))
				OR (p_DueDateFrom=2 AND (ts.DueDate !='0000-00-00 00:00:00' AND ts.DueDate >= NOW() AND ts.DueDate <= DATE(DATE_ADD(NOW(), INTERVAL +2 DAY)))) 
				OR ((p_DueDateFrom!='' OR p_DueDateTo!='') AND ts.DueDate BETWEEN STR_TO_DATE(p_DueDateFrom,'%Y-%m-%d %H:%i:%s') AND STR_TO_DATE(p_DueDateTo,'%Y-%m-%d %H:%i:%s'))
			)
		AND (v_Active_ = (Select `Status` FROM tblUser Where tblUser.UserID = ts.UsersIDs limit 1))
		LEFT JOIN tblUser u on u.UserID = ts.UsersIDs;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetTasksSingle` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetTasksSingle`(IN `p_TaskID` INT)
BEGIN
	SELECT 
		t.Subject,
		 group_concat( concat(u.FirstName,' ',u.LastName)separator ',') as Name,		
		 case when t.Priority =1
			  then 'High'
			    else
			  'Low' end as Priority,
			DueDate,
		t.Description,
		bc.BoardColumnName as TaskStatus,
		t.created_at,
		t.Task_type as followup_task,
		t.CreatedBy as created_by,
		t.TaskID
	FROM tblTask t
	INNER JOIN tblCRMBoardColumn bc on  t.BoardColumnID = bc.BoardColumnID	
	INNER JOIN tblUser u on  u.UserID = t.UsersIDs		
	WHERE t.TaskID = p_TaskID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getUsers` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getUsers`(
	IN `p_CompanyID` INT,
	IN `p_Status` INT,
	IN `p_PageNumber` INT,
	IN `p_RowspPage` INT,
	IN `p_lSortCol` VARCHAR(50),
	IN `p_SortOrder` VARCHAR(5),
	IN `p_Export` INT


)
BEGIN
     DECLARE v_OffSet_ int;
     SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	      
	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;

	if p_Export = 0
	THEN
	
	select  
	u.Status,
	u.FirstName,
	u.LastName,
	u.EmailAddress,
	CASE WHEN u.AdminUser=1
	then 'Super Admin'
	Else group_concat(DISTINCT r.RoleName ORDER BY r.RoleName ASC SEPARATOR ', ')
	END as Roles,
	u.JobNotification,
	u.UserID	
	 from `tblUser` u 
	left join tblUserRole ur on u.UserID=ur.UserID
	left join tblRole r on ur.RoleID=r.RoleID 	
	where u.CompanyID=p_CompanyID and u.`Status`=p_Status
	group by u.UserID
	order by 
	CASE
                WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'StatusDESC') THEN Status
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'StatusASC') THEN Status
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'FirstNameDESC') THEN FirstName
                END DESC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'FirstNameASC') THEN FirstName
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'LastNameDESC') THEN LastName
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'LastNameASC') THEN LastName
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EmailAddressDESC') THEN EmailAddress
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EmailAddressASC') THEN EmailAddress
                END ASC,
				CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RoleDESC') THEN AdminUser
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RoleASC') THEN AdminUser
                END ASC
            LIMIT p_RowspPage OFFSET v_OffSet_;
            
         SELECT  			
			COUNT(UserID)	 AS totalcount
		   from `tblUser`
			where CompanyID=p_CompanyID and `Status`=p_Status;

	ELSE

			select  
			u.FirstName,
			u.LastName,
			u.EmailAddress,
			CASE WHEN u.AdminUser=1
			then 'Admin'
			Else group_concat(r.RoleName)
			END as AdminUser,
			u.JobNotification,
			u.UserID	
			 from `tblUser` u 
			left join tblUserRole ur on u.UserID=ur.UserID
			left join tblRole r on ur.RoleID=r.RoleID 	
			where u.CompanyID=p_CompanyID and u.`Status`=p_Status
			group by u.UserID
			order by FirstName ASC;

	END IF;
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetVendorBlockByCode` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetVendorBlockByCode`(IN `p_companyid` INT , IN `p_AccountID` INT, IN `p_trunkID` INT, IN `p_contryID` INT , IN `p_status` VARCHAR(50) , IN `p_code` VARCHAR(50), IN `p_PageNumber` INT , IN `p_RowspPage` INT , IN `p_lSortCol` VARCHAR(50) , IN `p_SortOrder` VARCHAR(5) , IN `p_isExport` INT)
BEGIN
	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
	
   IF p_isExport = 0
   THEN
   
		SELECT  Distinct
            `tblRate`.RateID
           ,`tblRate`.Code
           ,CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END as Status 
           ,`tblRate`.Description
           ,VendorBlockingId
	    FROM      `tblRate`
       INNER JOIN tblVendorRate ON tblRate.RateID = tblVendorRate.RateId AND AccountID = p_AccountID AND tblVendorRate.TrunkID = p_trunkID   
	    INNER JOIN tblVendorTrunk ON tblVendorTrunk.CodeDeckId = tblRate.CodeDeckId AND tblVendorTrunk.AccountID = p_AccountID AND tblVendorTrunk.TrunkID = p_trunkID   
       LEFT JOIN tblVendorBlocking ON tblVendorBlocking.RateId = tblVendorRate.RateID and tblVendorRate.TrunkID = tblVendorBlocking.TrunkID and tblVendorRate.AccountId = tblVendorBlocking.AccountId
		 WHERE ( p_contryID IS NULL OR tblRate.CountryID = p_contryID)
             AND ( tblRate.CompanyID = p_companyid )
             AND ( p_code IS NULL OR Code LIKE REPLACE(p_code,'*', '%') )
             AND ( p_status = 'All' or ( CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END ) = p_status)  
		 ORDER BY
		 		 	CASE WHEN ( CONCAT(p_lSortCol,p_SortOrder) = 'CodeDESC') THEN Code
					END DESC ,
					CASE WHEN ( CONCAT(p_lSortCol,p_SortOrder) = 'CodeASC') THEN Code
					END ASC ,
					CASE WHEN ( CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionDESC') THEN tblRate.Description
					END DESC ,
					CASE WHEN ( CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionASC') THEN tblRate.Description
					END ASC,
					CASE WHEN ( CONCAT(p_lSortCol,p_SortOrder) = 'StatusDESC') THEN VendorBlockingId
					END DESC,
					CASE WHEN ( CONCAT(p_lSortCol,p_SortOrder) = 'StatusASC') THEN VendorBlockingId
					END ASC
		LIMIT p_RowspPage OFFSET v_OffSet_;
            
      
		SELECT COUNT(DISTINCT tblRate.RateID) AS totalcount
      FROM    tblRate
      INNER JOIN tblVendorRate ON tblRate.RateID = tblVendorRate.RateId AND AccountID = p_AccountID AND tblVendorRate.TrunkID = p_trunkID   
		INNER JOIN tblVendorTrunk ON tblVendorTrunk.CodeDeckId = tblRate.CodeDeckId AND tblVendorTrunk.AccountID = p_AccountID AND tblVendorTrunk.TrunkID = p_trunkID   
      LEFT JOIN tblVendorBlocking ON tblVendorBlocking.RateId = tblVendorRate.RateID and tblVendorRate.TrunkID = tblVendorBlocking.TrunkID and tblVendorRate.AccountId = tblVendorBlocking.AccountId
      WHERE   ( p_contryID IS NULL OR tblRate.CountryID = p_contryID)
      		AND ( tblRate.CompanyID = p_companyid )
            AND ( p_code IS NULL OR Code LIKE REPLACE(p_code,'*', '%') )
            AND ( p_status = 'All' or ( CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END ) = p_status);  
	END IF;
  
   IF p_isExport = 1
   THEN 
		SELECT Distinct  Code
      		,CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END as Status
            ,tblRate.Description

		FROM    tblRate
      INNER JOIN tblVendorRate ON tblRate.RateID = tblVendorRate.RateId AND AccountID = p_AccountID AND tblVendorRate.TrunkID = p_trunkID   
		INNER JOIN tblVendorTrunk ON tblVendorTrunk.CodeDeckId = tblRate.CodeDeckId AND tblVendorTrunk.AccountID = p_AccountID AND tblVendorTrunk.TrunkID = p_trunkID   
      LEFT JOIN tblVendorBlocking ON tblVendorBlocking.RateId = tblVendorRate.RateID and tblVendorRate.TrunkID = tblVendorBlocking.TrunkID and tblVendorRate.AccountId = tblVendorBlocking.AccountId		
		WHERE   ( p_contryID IS NULL OR tblRate.CountryID = p_contryID)
      		AND ( tblRate.CompanyID = p_companyid )
            AND ( p_code IS NULL OR Code LIKE REPLACE(p_code,'*', '%') )
            AND ( p_status = 'All' or ( CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END ) = p_status)    
		ORDER BY Code,Status  ;                    
   END IF;
   IF p_isExport = 2
   THEN 
		SELECT Distinct  
			    tblRate.RateID as RateID
				,Code
      		,CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END as Status
            ,tblRate.Description

		FROM    tblRate
      INNER JOIN tblVendorRate ON tblRate.RateID = tblVendorRate.RateId AND AccountID = p_AccountID AND tblVendorRate.TrunkID = p_trunkID   
		INNER JOIN tblVendorTrunk ON tblVendorTrunk.CodeDeckId = tblRate.CodeDeckId AND tblVendorTrunk.AccountID = p_AccountID AND tblVendorTrunk.TrunkID = p_trunkID   
      LEFT JOIN tblVendorBlocking ON tblVendorBlocking.RateId = tblVendorRate.RateID and tblVendorRate.TrunkID = tblVendorBlocking.TrunkID and tblVendorRate.AccountId = tblVendorBlocking.AccountId		
		WHERE   ( p_contryID IS NULL OR tblRate.CountryID = p_contryID)
      		AND ( tblRate.CompanyID = p_companyid )
            AND ( p_code IS NULL OR Code LIKE REPLACE(p_code,'*', '%') )
            AND ( p_status = 'All' or ( CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END ) = p_status)    
		ORDER BY Code,Status  ;                    
   END IF;
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetVendorBlockByCountry` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetVendorBlockByCountry`(IN `p_AccountID` INT, IN `p_trunkID` INT, IN `p_contryID` INT , IN `p_status` VARCHAR(50) , IN `p_PageNumber` INT , IN `p_RowspPage` INT , IN `p_lSortCol` VARCHAR(50) , IN `p_SortOrder` VARCHAR(5) , IN `p_isExport` INT)
BEGIN
       
	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;      
		  
	IF p_isExport = 0
   THEN
  
		SELECT 
      	tblCountry.CountryID,
         Country,
         CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END as Status
		FROM      tblCountry
      LEFT JOIN tblVendorBlocking ON tblVendorBlocking.CountryId = tblCountry.CountryID AND TrunkID = p_trunkID AND AccountId = p_AccountID
      WHERE         ( p_contryID IS NULL OR tblCountry.CountryID = p_contryID)
      	AND ( p_status = 'All' or ( CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END ) = p_status)  
      ORDER BY 
	   	CASE WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CountryDESC') THEN Country
	      END DESC ,
	      CASE WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CountryASC')	THEN Country
	      END ASC ,
	      CASE WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'StatusDESC') THEN VendorBlockingId
	      END DESC,
	      CASE WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'StatusASC') THEN VendorBlockingId
	      END ASC
		LIMIT p_RowspPage OFFSET v_OffSet_;
		
		SELECT  COUNT(tblCountry.CountryID) AS totalcount
      FROM    tblCountry
      LEFT JOIN tblVendorBlocking ON tblVendorBlocking.CountryId = tblCountry.CountryID AND TrunkID = p_trunkID AND AccountId = p_AccountID
      WHERE   ( p_contryID IS NULL OR tblCountry.CountryID = p_contryID)
      	AND ( p_status = 'All' or ( CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END ) = p_status);  
	END IF;
  
   IF p_isExport = 1
   THEN 
  
   	SELECT   Country,
      			CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END as Status
		FROM    tblCountry
      LEFT JOIN tblVendorBlocking ON tblVendorBlocking.CountryId = tblCountry.CountryID AND TrunkID = p_trunkID AND AccountId = p_AccountID
      WHERE   ( p_contryID IS NULL OR tblCountry.CountryID = p_contryID)
      	AND ( p_status = 'All' or ( CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END ) = p_status);    
	END IF;
	IF p_isExport = 2
   THEN 
  
   	SELECT   tblCountry.CountryID,
					Country,
      			CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END as Status
		FROM    tblCountry
      LEFT JOIN tblVendorBlocking ON tblVendorBlocking.CountryId = tblCountry.CountryID AND TrunkID = p_trunkID AND AccountId = p_AccountID
      WHERE   ( p_contryID IS NULL OR tblCountry.CountryID = p_contryID)
      	AND ( p_status = 'All' or ( CASE WHEN tblVendorBlocking.VendorBlockingId IS NULL THEN 'Not Blocked' ELSE 'Blocked' END ) = p_status);    
	END IF;
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_getVendorCodeRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_getVendorCodeRate`(IN `p_AccountID` INT, IN `p_trunkID` INT, IN `p_RateCDR` INT)
BEGIN
 
	IF p_RateCDR = 0
	THEN 

		DROP TEMPORARY TABLE IF EXISTS tmp_vcodes_;
		CREATE TEMPORARY TABLE tmp_vcodes_ (
			RateID INT,
			Code VARCHAR(50),
			INDEX tmp_vcodes_RateID (`RateID`),
			INDEX tmp_vcodes_Code (`Code`)
		);

		INSERT INTO tmp_vcodes_
		SELECT
		DISTINCT
			tblRate.RateID,
			tblRate.Code
		FROM tblRate
		INNER JOIN tblVendorRate
		ON tblVendorRate.RateID = tblRate.RateID
		WHERE 
			 tblVendorRate.AccountId = p_AccountID
		AND tblVendorRate.TrunkID = p_trunkID
		AND tblVendorRate.EffectiveDate <= NOW();

	END IF;
	
	IF p_RateCDR = 1
	THEN 

		DROP TEMPORARY TABLE IF EXISTS tmp_vcodes_;
		CREATE TEMPORARY TABLE tmp_vcodes_ (
			RateID INT,
			Code VARCHAR(50),
			Rate Decimal(18,6),
			ConnectionFee Decimal(18,6),
			Interval1 INT,
			IntervalN INT,
			INDEX tmp_vcodes_RateID (`RateID`),
			INDEX tmp_vcodes_Code (`Code`)
		);
		
		INSERT INTO tmp_vcodes_
		SELECT
		DISTINCT
			tblRate.RateID,
			tblRate.Code,
			tblVendorRate.Rate,
			tblVendorRate.ConnectionFee,
			tblVendorRate.Interval1,
			tblVendorRate.IntervalN
		FROM tblRate
		INNER JOIN tblVendorRate
		ON tblVendorRate.RateID = tblRate.RateID
		WHERE 
			 tblVendorRate.AccountId = p_AccountID
		AND tblVendorRate.TrunkID = p_trunkID
		AND tblVendorRate.EffectiveDate <= NOW();
	
	END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetVendorCodes` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetVendorCodes`(IN `p_companyid` INT , IN `p_trunkID` INT, IN `p_contryID` VARCHAR(50) , IN `p_code` VARCHAR(50), IN `p_PageNumber` INT , IN `p_RowspPage` INT , IN `p_lSortCol` NVARCHAR(50) , IN `p_SortOrder` NVARCHAR(5) , IN `p_isExport` INT)
BEGIN

	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
        
        IF p_isExport = 0	
		  THEN
		  
		 	 SELECT  RateID,Code FROM(
                                SELECT distinct tblRate.Code as RateID
                                    ,tblRate.Code
                                FROM      tblRate                              
                                WHERE ( tblRate.CompanyID = p_companyid )
                                AND ( p_contryID  = '' OR  FIND_IN_SET(tblRate.CountryID,p_contryID) != 0  )
                                AND ( p_code = '' OR Code LIKE REPLACE(p_code,'*', '%') )
                            ) AS TBL2
            ORDER BY
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CodeDESC') THEN Code
                END DESC,
                CASE
                    WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CodeASC') THEN Code
                END ASC
				LIMIT p_RowspPage OFFSET v_OffSet_;    
  
				
				      
                                      
                    
    
		
		
		 Select Count(*) AS totalcount
      FROM
      (
      
            SELECT distinct tblRate.Code 
                                FROM      tblRate                              
                                WHERE ( tblRate.CompanyID = p_companyid )
                                AND ( p_contryID  = '' OR  FIND_IN_SET(tblRate.CountryID,p_contryID) != 0  )
                                AND ( p_code = '' OR Code LIKE REPLACE(p_code,'*', '%') )
		)	AS tbl ;
                    
    END IF;
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 
       
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetVendorPreference` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetVendorPreference`(IN `p_companyid` INT , IN `p_AccountID` INT, IN `p_trunkID` INT, IN `p_contryID` INT , IN `p_code` VARCHAR(50) , IN `p_description` VARCHAR(50) , IN `p_PageNumber` INT , IN `p_RowspPage` INT , IN `p_lSortCol` VARCHAR(50) , IN `p_SortOrder` VARCHAR(5) , IN `p_isExport` INT )
BEGIN	

		 
		DECLARE v_CodeDeckId_ int;
		DECLARE v_OffSet_ int;
		SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

		SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
	
		select CodeDeckId into v_CodeDeckId_  from tblVendorTrunk where AccountID = p_AccountID and TrunkID = p_trunkID;
        
         IF p_isExport = 0
			THEN

				SELECT
				   DISTINCT
					tblRate.RateID,
					Code,
					Preference,
					Description,
					VendorPreferenceID
				FROM  tblVendorRate
				JOIN tblRate
					ON tblVendorRate.RateId = tblRate.RateId
				LEFT JOIN tblVendorPreference 
					ON tblVendorPreference.AccountId = tblVendorRate.AccountId
                    AND tblVendorPreference.TrunkID = tblVendorRate.TrunkID
                    AND tblVendorPreference.RateId = tblVendorRate.RateId
				WHERE (p_contryID IS NULL OR CountryID = p_contryID)
				AND (p_code IS NULL OR Code LIKE REPLACE(p_code, '*', '%'))
				AND (p_description IS NULL OR tblRate.Description LIKE REPLACE(p_description, '*', '%'))
				AND (tblRate.CompanyID = p_companyid)
				AND tblVendorRate.TrunkID = p_trunkID
				AND tblVendorRate.AccountID = p_AccountID
				AND CodeDeckId = v_CodeDeckId_
				ORDER BY CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CodeDESC') THEN Code
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CodeASC') THEN Code
					END ASC,					 
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RateDESC') THEN Preference
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RateASC') THEN Preference
					END ASC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'VendorPreferenceIDDESC') THEN VendorPreferenceID
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'VendorPreferenceIDASC') THEN VendorPreferenceID
					END ASC
			LIMIT p_RowspPage OFFSET v_OffSet_;



				SELECT
					COUNT(DISTINCT RateID) AS totalcount
				FROM (SELECT
					tblRate.RateId,
					EffectiveDate
				FROM tblVendorRate
				JOIN tblRate
					ON tblVendorRate.RateId = tblRate.RateId
				LEFT JOIN tblVendorPreference 
					ON tblVendorPreference.AccountId = tblVendorRate.AccountId
                    AND tblVendorPreference.TrunkID = tblVendorRate.TrunkID
                    AND tblVendorPreference.RateId = tblVendorRate.RateId
				WHERE (p_contryID IS NULL
				OR CountryID = p_contryID)
				AND (p_code IS NULL
				OR Code LIKE REPLACE(p_code, '*', '%'))
				AND (p_description IS NULL
				OR tblRate.Description LIKE REPLACE(p_description, '*', '%'))
				AND (tblRate.CompanyID = p_companyid)
				AND tblVendorRate.TrunkID = p_trunkID
				AND tblVendorRate.AccountID = p_AccountID
				AND CodeDeckId = v_CodeDeckId_
				
			) AS tbl2;

		END IF;
	
		IF p_isExport = 1
		THEN

			SELECT DISTINCT
				Code,
				tblVendorPreference.Preference,
				Description
			FROM tblVendorRate
			JOIN tblRate
				ON tblVendorRate.RateId = tblRate.RateId
			LEFT JOIN tblVendorPreference 
					ON tblVendorPreference.AccountId = tblVendorRate.AccountId
                    AND tblVendorPreference.TrunkID = tblVendorRate.TrunkID
                    AND tblVendorPreference.RateId = tblVendorRate.RateId
			WHERE (p_contryID IS NULL OR CountryID = p_contryID)
			AND (p_code IS NULL OR Code LIKE REPLACE(p_code, '*', '%'))
			AND (p_description IS NULL OR tblRate.Description LIKE REPLACE(p_description, '*', '%'))
			AND (tblRate.CompanyID = p_companyid)
			AND tblVendorRate.TrunkID = p_trunkID
			AND tblVendorRate.AccountID = p_AccountID
			AND CodeDeckId = v_CodeDeckId_;
			
		END IF;
		IF p_isExport = 2
		THEN

			SELECT DISTINCT
				tblRate.RateID as RateID,
            Code,
            tblVendorPreference.Preference,
            Description
			FROM tblVendorRate
			JOIN tblRate
				ON tblVendorRate.RateId = tblRate.RateId
			LEFT JOIN tblVendorPreference 
					ON tblVendorPreference.AccountId = tblVendorRate.AccountId
                    AND tblVendorPreference.TrunkID = tblVendorRate.TrunkID
                    AND tblVendorPreference.RateId = tblVendorRate.RateId
			WHERE (p_contryID IS NULL OR CountryID = p_contryID)
			AND (p_code IS NULL OR Code LIKE REPLACE(p_code, '*', '%'))
			AND (p_description IS NULL OR tblRate.Description LIKE REPLACE(p_description, '*', '%'))
			AND (tblRate.CompanyID = p_companyid)
			AND tblVendorRate.TrunkID = p_trunkID
			AND tblVendorRate.AccountID = p_AccountID
			AND CodeDeckId = v_CodeDeckId_;
			
		END IF;
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_GetVendorRates` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_GetVendorRates`(IN `p_companyid` INT , IN `p_AccountID` INT, IN `p_trunkID` INT, IN `p_contryID` INT , IN `p_code` VARCHAR(50) , IN `p_description` VARCHAR(50) , IN `p_effective` varchar(100), IN `p_PageNumber` INT , IN `p_RowspPage` INT , IN `p_lSortCol` VARCHAR(50) , IN `p_SortOrder` VARCHAR(5) , IN `p_isExport` INT )
BEGIN	
		
 
	DECLARE v_CodeDeckId_ int;
	DECLARE v_OffSet_ int;
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	SET v_OffSet_ = (p_PageNumber * p_RowspPage) - p_RowspPage;
		 

		select CodeDeckId into v_CodeDeckId_  from tblVendorTrunk where AccountID = p_AccountID and TrunkID = p_trunkID;
		
		
		DROP TEMPORARY TABLE IF EXISTS tmp_VendorRate_;
	   CREATE TEMPORARY TABLE tmp_VendorRate_ (
	        VendorRateID INT,
	        Code VARCHAR(50),
	        Description VARCHAR(200),
			  ConnectionFee DECIMAL(18, 6),
	        Interval1 INT,
	        IntervalN INT,
	        Rate DECIMAL(18, 6),
	        EffectiveDate DATE,
	        updated_at DATETIME,
	        updated_by VARCHAR(50),
	        INDEX tmp_VendorRate_RateID (`Code`)
	    );
	    
	    INSERT INTO tmp_VendorRate_
		 SELECT
					VendorRateID,
					Code,
					tblRate.Description,
					tblVendorRate.ConnectionFee,
					CASE WHEN tblVendorRate.Interval1 IS NOT NULL
					THEN tblVendorRate.Interval1
					ELSE tblRate.Interval1
					END AS Interval1,
					CASE WHEN tblVendorRate.IntervalN IS NOT NULL
					THEN tblVendorRate.IntervalN
					ELSE tblRate.IntervalN
					END AS IntervalN ,
					Rate,
					EffectiveDate,
					tblVendorRate.updated_at,
					tblVendorRate.updated_by
				FROM tblVendorRate
				JOIN tblRate
					ON tblVendorRate.RateId = tblRate.RateId
				WHERE (p_contryID IS NULL
				OR CountryID = p_contryID)
				AND (p_code IS NULL
				OR Code LIKE REPLACE(p_code, '*', '%'))
				AND (p_description IS NULL
				OR tblRate.Description LIKE REPLACE(p_description, '*', '%'))
				AND (tblRate.CompanyID = p_companyid)
				AND TrunkID = p_trunkID
				AND tblVendorRate.AccountID = p_AccountID
				AND CodeDeckId = v_CodeDeckId_
				AND
					(
					(p_effective = 'Now' AND EffectiveDate <= NOW() )
					OR 
					(p_effective = 'Future' AND EffectiveDate > NOW())
					);
		IF p_effective = 'Now'
		THEN
		   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorRate2_ as (select * from tmp_VendorRate_);	        
         DELETE n1 FROM tmp_VendorRate_ n1, tmp_VendorRate2_ n2 WHERE n1.EffectiveDate < n2.EffectiveDate 
		   AND  n1.Code = n2.Code;
		END IF;        
        
		  
		   IF p_isExport = 0
			THEN
		 		SELECT
					VendorRateID,
					Code,
					Description,
					ConnectionFee,
					Interval1,
					IntervalN,
					Rate,
					EffectiveDate,
					updated_at,
					updated_by
					
				FROM  tmp_VendorRate_
				ORDER BY CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CodeDESC') THEN Code
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'CodeASC') THEN Code
					END ASC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionDESC') THEN Description
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'DescriptionASC') THEN Description
					END ASC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RateDESC') THEN Rate
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'RateASC') THEN Rate
					END ASC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ConnectionFeeDESC') THEN ConnectionFee
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'ConnectionFeeASC') THEN ConnectionFee
					END ASC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'Interval1DESC') THEN Interval1
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'Interval1ASC') THEN Interval1
					END ASC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'IntervalNDESC') THEN IntervalN
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'IntervalNASC') THEN IntervalN
					END ASC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EffectiveDateDESC') THEN EffectiveDate
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'EffectiveDateASC') THEN EffectiveDate
					END ASC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'updated_atDESC') THEN updated_at
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'updated_atASC') THEN updated_at
					END ASC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'updated_byDESC') THEN updated_by
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'updated_byASC') THEN updated_by
					END ASC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'VendorRateIDDESC') THEN VendorRateID
					END DESC,
					CASE
						WHEN (CONCAT(p_lSortCol,p_SortOrder) = 'VendorRateIDASC') THEN VendorRateID
					END ASC
				LIMIT p_RowspPage OFFSET v_OffSet_;



				SELECT
					COUNT(code) AS totalcount
				FROM tmp_VendorRate_;


			END IF;
	
       IF p_isExport = 1
		THEN

			SELECT
				Code,
				Description,
				Rate,
				EffectiveDate,
				updated_at AS `Modified Date`,
				updated_by AS `Modified By`

			FROM tmp_VendorRate_;
		END IF;
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_InsertDiscontinuedVendorRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_InsertDiscontinuedVendorRate`(IN `p_AccountId` INT, IN `p_TrunkId` INT)
BEGIN
	 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

		
		
	 	
	 	
	 	
	 	INSERT INTO tblVendorRateDiscontinued(
					VendorRateID,
			   	AccountId,
			   	TrunkID,
					RateId,
			   	Code,
			   	Description,
			   	Rate,
			   	EffectiveDate,
			   	Interval1,
			   	IntervalN,
			   	ConnectionFee,
			   	deleted_at
					)
		SELECT DISTINCT
			   	VendorRateID,
			   	AccountId,
			   	TrunkID,
					RateId,
			   	Code,
			   	Description,
			   	Rate,
			   	EffectiveDate,
			   	Interval1,
			   	IntervalN,
			   	ConnectionFee,
			   	deleted_at
		FROM tmp_Delete_VendorRate
			ORDER BY VendorRateID ASC
			; 
		
		
		
		DROP TEMPORARY TABLE IF EXISTS tmp_VendorRateDiscontinued_;
		CREATE TEMPORARY TABLE IF NOT EXISTS tmp_VendorRateDiscontinued_ (PRIMARY KEY (DiscontinuedID), INDEX tmp_UK_tblVendorRateDiscontinued (AccountId, RateId)) as (select * from tblVendorRateDiscontinued);

		  DELETE n1 FROM tblVendorRateDiscontinued n1, tmp_VendorRateDiscontinued_ n2 WHERE n1.DiscontinuedID < n2.DiscontinuedID
		  	 AND  n1.RateId = n2.RateId
		  	 AND  n1.AccountId = n2.AccountId
			 AND  n1.AccountId = p_AccountId;
	
		
	
		DELETE tblVendorRate
			FROM tblVendorRate
				INNER JOIN(	SELECT dv.VendorRateID FROM tmp_Delete_VendorRate dv) tmdv
					ON tmdv.VendorRateID = tblVendorRate.VendorRateID			
			WHERE tblVendorRate.AccountId = p_AccountId;
									
		CALL prc_ArchiveOldVendorRate(p_AccountId,p_TrunkId);
		
   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_insertUpdateDestinationCode` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_insertUpdateDestinationCode`(IN `p_DestinationGroupID` INT, IN `p_RateID` TEXT, IN `p_CountryID` INT, IN `p_Code` VARCHAR(50), IN `p_Description` VARCHAR(50), IN `p_Action` VARCHAR(50))
BEGIN

	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	
	IF p_Action = 'Insert'
	THEN

		INSERT INTO tblDestinationGroupCode (DestinationGroupID,RateID)
		SELECT p_DestinationGroupID,r.RateID
		FROM tblRate r
		INNER JOIN tblDestinationGroupSet dgs
			ON dgs.CodedeckID =r.CodeDeckId
		INNER JOIN tblDestinationGroup dg
			ON dgs.DestinationGroupSetID = dg.DestinationGroupSetID
		LEFT JOIN tblDestinationGroupCode dgc 
			ON dgc.DestinationGroupID = dg.DestinationGroupID 
			AND r.RateID = dgc.RateID
		WHERE dg.DestinationGroupID = p_DestinationGroupID
		AND (p_RateID = '' OR (p_RateID !='' AND FIND_IN_SET (r.RateID,p_RateID)!= 0 ))
		AND (p_Code = '' OR Code LIKE REPLACE(p_Code, '*', '%'))
		AND (p_Description = '' OR Description LIKE REPLACE(p_Description, '*', '%'))
		AND (p_CountryID = 0 OR r.CountryID = p_CountryID)
		AND dgc.DestinationGroupCodeID IS NULL;

	END IF;
	
	IF p_Action = 'Delete'
	THEN

		DELETE dgc
		FROM tblDestinationGroupCode dgc
		INNER JOIN tblRate r 
			ON r.RateID = dgc.RateID
		INNER JOIN tblDestinationGroup dg
			ON dg.DestinationGroupID = dgc.DestinationGroupID
		WHERE dg.DestinationGroupID = p_DestinationGroupID
		AND (p_RateID = '' OR (p_RateID !='' AND FIND_IN_SET (r.RateID,p_RateID)!= 0 ))
		AND (p_Code = '' OR Code LIKE REPLACE(p_Code, '*', '%'))
		AND (p_Description = '' OR Description LIKE REPLACE(p_Description, '*', '%'))
		AND (p_CountryID = 0 OR r.CountryID = p_CountryID);

	END IF;
	

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_isDiscountPlanApplied` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_isDiscountPlanApplied`(IN `p_Action` VARCHAR(50), IN `p_DestinationGroupSetID` INT, IN `p_DiscountPlanID` INT)
BEGIN
	
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	
	IF p_Action = 'DestinationGroupSet'
	THEN
		
		SELECT 
			dgs.DestinationGroupSetID 
		FROM tblDestinationGroupSet dgs
		INNER JOIN tblDiscountPlan dp
			ON dp.DestinationGroupSetID = dgs.DestinationGroupSetID
		INNER JOIN tblAccountDiscountPlan adp
			ON adp.DiscountPlanID = dp.DiscountPlanID
		WHERE dgs.DestinationGroupSetID = p_DestinationGroupSetID;
	
	END IF;
	
	IF p_Action = 'DiscountPlan'
	THEN
		
		SELECT 
			dp.DiscountPlanID 
		FROM tblDiscountPlan dp
		INNER JOIN tblAccountDiscountPlan adp
			ON adp.DiscountPlanID = dp.DiscountPlanID
		WHERE adp.DiscountPlanID = p_DiscountPlanID;
	
	END IF;
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_LowBalanceReminder` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_LowBalanceReminder`(IN `p_CompanyID` INT, IN `p_AccountID` INT, IN `p_BillingClassID` INT)
BEGIN

	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	
	CALL wavetelwholesaleBilling.prc_updateSOAOffSet(p_CompanyID,p_AccountID);
	
	
	SELECT
		IF ( (CASE WHEN ab.BalanceThreshold LIKE '%p' THEN REPLACE(ab.BalanceThreshold, 'p', '')/ 100 * ab.PermanentCredit ELSE ab.BalanceThreshold END) < ab.BalanceAmount AND ab.BalanceThreshold <> 0 ,1,0) as BalanceWarning,
		a.AccountID
	FROM tblAccountBalance ab 
	INNER JOIN tblAccount a 
		ON a.AccountID = ab.AccountID
	INNER JOIN tblAccountBilling abg 
		ON abg.AccountID  = a.AccountID
	INNER JOIN tblBillingClass b
		ON b.BillingClassID = abg.BillingClassID
	WHERE a.CompanyId = p_CompanyID
	AND (p_AccountID = 0 OR  a.AccountID = p_AccountID)
	AND (p_BillingClassID = 0 OR  b.BillingClassID = p_BillingClassID)
	AND ab.PermanentCredit IS NOT NULL
	AND ab.BalanceThreshold IS NOT NULL
	AND a.`Status` = 1;
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_ProcessDiscountPlan` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_ProcessDiscountPlan`(IN `p_processId` INT, IN `p_tbltempusagedetail_name` VARCHAR(200))
BEGIN
	
	DECLARE v_rowCount_ INT;
	DECLARE v_pointer_ INT;	
	DECLARE v_AccountID_ INT;
	
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	
	
	DROP TEMPORARY TABLE IF EXISTS tmp_Accounts_;
	CREATE TEMPORARY TABLE tmp_Accounts_  (
		RowID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		AccountID INT
	);
	SET @stm = CONCAT('
	INSERT INTO tmp_Accounts_(AccountID)
	SELECT DISTINCT ud.AccountID FROM wavetelwholesaleCDR.`' , p_tbltempusagedetail_name , '` ud 
	INNER JOIN tblAccountDiscountPlan adp
		ON ud.AccountID = adp.AccountID AND Type = 1
	WHERE ProcessID="' , p_processId , '" AND ud.is_inbound = 0;
	');
	
	PREPARE stmt FROM @stm;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;	
	
	SET v_pointer_ = 1;
	SET v_rowCount_ = (SELECT COUNT(*)FROM tmp_Accounts_);

	WHILE v_pointer_ <= v_rowCount_
	DO

		SET v_AccountID_ = (SELECT AccountID FROM tmp_Accounts_ t WHERE t.RowID = v_pointer_);
		
		
		CALL prc_applyAccountDiscountPlan(v_AccountID_,p_tbltempusagedetail_name,p_processId,0);
		
		SET v_pointer_ = v_pointer_ + 1;
	END WHILE;
	
	
	DROP TEMPORARY TABLE IF EXISTS tmp_Accounts_;
	CREATE TEMPORARY TABLE tmp_Accounts_  (
		RowID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		AccountID INT
	);
	SET @stm = CONCAT('
	INSERT INTO tmp_Accounts_(AccountID)
	SELECT DISTINCT ud.AccountID FROM wavetelwholesaleCDR.`' , p_tbltempusagedetail_name , '` ud 
	INNER JOIN tblAccountDiscountPlan adp
		ON ud.AccountID = adp.AccountID AND Type = 2
	WHERE ProcessID="' , p_processId , '" AND ud.is_inbound = 1;
	');
	
	PREPARE stmt FROM @stm;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
	
	SET v_pointer_ = 1;
	SET v_rowCount_ = (SELECT COUNT(*)FROM tmp_Accounts_);

	WHILE v_pointer_ <= v_rowCount_
	DO

		SET v_AccountID_ = (SELECT AccountID FROM tmp_Accounts_ t WHERE t.RowID = v_pointer_);
		
		
		CALL prc_applyAccountDiscountPlan(v_AccountID_,p_tbltempusagedetail_name,p_processId,1);
		
		SET v_pointer_ = v_pointer_ + 1;
	END WHILE;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_RateDeleteFromCodedeck` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_RateDeleteFromCodedeck`(IN `p_CompanyId` INT, IN `p_CodeDeckId` INT, IN `p_RateID` LONGTEXT, IN `p_CountryId` INT, IN `p_code` VARCHAR(50), IN `p_description` VARCHAR(200))
BEGIN

   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 
   
	delete tblRate FROM tblRate INNER JOIN(
	  select r.RateID FROM `tblRate` r
		where (r.CompanyID = p_CompanyId)
		AND (r.CodeDeckId = p_CodeDeckId)
		and ( p_RateID ='' OR  FIND_IN_SET(r.RateID, p_RateID) )
		AND (p_CountryId = 0 OR r.CountryID = p_CountryId)
		AND (p_code = '' OR r.Code LIKE REPLACE(p_code, '*', '%'))
		AND (p_description = '' OR r.Description LIKE REPLACE(p_description, '*', '%')))
	  tr on tr.RateID=tblRate.RateID;				
	  
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_RateTableRateInsertUpdate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_RateTableRateInsertUpdate`(IN `p_CompanyID` INT, IN `p_RateTableRateID` LONGTEXT
, IN `p_RateTableId` INT 
, IN `p_Rate` DECIMAL(18, 6) 
, IN `p_EffectiveDate` DATETIME
, IN `p_ModifiedBy` VARCHAR(50)
, IN `p_Interval1` INT
, IN `p_IntervalN` INT
, IN `p_ConnectionFee` DECIMAL(18, 6)
, IN `p_Critearea` INT
, IN `p_Critearea_TrunkID` INT
, IN `p_Critearea_CountryID` INT
, IN `p_Critearea_Code` VARCHAR(50)
, IN `p_Critearea_Description` VARCHAR(50)
, IN `p_Critearea_Effective` VARCHAR(50)
, IN `p_Update_EffectiveDate` INT
, IN `p_Update_Rate` INT
, IN `p_Update_Interval1` INT
, IN `p_Update_IntervalN` INT
, IN `p_Update_ConnectionFee` INT
, IN `p_Action` INT
)
BEGIN
              
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

    DROP TEMPORARY TABLE IF EXISTS tmp_Update_RateTable_;
    CREATE TEMPORARY TABLE tmp_Update_RateTable_ (
      RateID INT,
      EffectiveDate DATE,
      RateTableRateID INT
    ); 
    
    DROP TEMPORARY TABLE IF EXISTS tmp_Insert_RateTable_;
    CREATE TEMPORARY TABLE tmp_Insert_RateTable_ (
      RateID INT,
      EffectiveDate DATE,
      RateTableRateID INT
    );
    
    DROP TEMPORARY TABLE IF EXISTS tmp_all_RateId_;
    CREATE TEMPORARY TABLE tmp_all_RateId_ (
      RateID INT,
      EffectiveDate DATE,
      RateTableRateID INT
	  );
	 
    
    DROP TEMPORARY TABLE IF EXISTS tmp_RateTable_;
      CREATE TEMPORARY TABLE tmp_RateTable_ (
         RateID INT,
         EffectiveDate DATE,
         RateTableRateID INT,
			INDEX tmp_RateTable_RateId (`RateId`)  
     );
    
   IF p_Critearea = 0
	THEN
 		INSERT INTO tmp_RateTable_
			  SELECT RateID,EffectiveDate,RateTableRateID
			   FROM tblRateTableRate
					WHERE (FIND_IN_SET(RateTableRateID,p_RateTableRateID) != 0 );
	
	END IF;
	
	
	IF p_Critearea = 1
	THEN
		
		
		 INSERT INTO tmp_RateTable_
				SELECT
					 tblRateTableRate.RateID,
                IFNULL(tblRateTableRate.EffectiveDate, NOW()) as EffectiveDate,
                tblRateTableRate.RateTableRateID
            FROM tblRate
            LEFT JOIN tblRateTableRate
                ON tblRateTableRate.RateID = tblRate.RateID
                AND tblRateTableRate.RateTableId = p_RateTableId
            INNER JOIN tblRateTable
                ON tblRateTable.RateTableId = tblRateTableRate.RateTableId
            WHERE		(tblRate.CompanyID = p_CompanyID)
					AND (p_Critearea_CountryID = 0 OR CountryID = p_Critearea_CountryID)
					AND (p_Critearea_Code IS NULL OR Code LIKE REPLACE(p_Critearea_Code, '*', '%'))
					AND (p_Critearea_Description IS NULL OR Description LIKE REPLACE(p_Critearea_Description, '*', '%'))
					AND TrunkID = p_Critearea_TrunkID
					AND (			
							p_Critearea_Effective = 'All' 
						OR (p_Critearea_Effective = 'Now' AND EffectiveDate <= NOW() )
						OR (p_Critearea_Effective = 'Future' AND EffectiveDate > NOW())
						);
		 
		  IF p_Critearea_Effective = 'Now'
		  THEN 
			 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_RateTable4_ as (select * from tmp_RateTable_);	        
          DELETE n1 FROM tmp_RateTable_ n1, tmp_RateTable4_ n2 WHERE n1.EffectiveDate < n2.EffectiveDate 
	 	   AND  n1.RateId = n2.RateId;
		  
		  END IF;
		  		  
	END IF;
	
	IF p_action = 1
	THEN
	
	IF p_Update_EffectiveDate = 0
	THEN
	
	INSERT INTO tmp_Update_RateTable_
		SELECT RateID,EffectiveDate,RateTableRateID
			 FROM tmp_RateTable_ ;	
				
	END IF;			
	
	IF p_Update_EffectiveDate = 1
	THEN
	

		INSERT INTO tmp_Update_RateTable_
		SELECT tblRateTableRate.RateID,
				p_EffectiveDate,
				tblRateTableRate.RateTableRateID
			 FROM tblRateTableRate 
			 	INNER JOIN tmp_RateTable_ r on tblRateTableRate.RateID = r.RateID
			 	 WHERE RateTableId = p_RateTableId   
					AND tblRateTableRate.EffectiveDate = p_EffectiveDate;
	
		INSERT INTO tmp_all_RateId_
		SELECT  tblRateTableRate.RateID,
					MAX(tblRateTableRate.EffectiveDate),
					MAX(tblRateTableRate.RateTableRateID)
				 FROM tblRateTableRate
				 	 INNER JOIN tmp_RateTable_ r on tblRateTableRate.RateID = r.RateID
					 	 WHERE tblRateTableRate.RateTableId = p_RateTableId
						  GROUP BY tblRateTableRate.RateID;
	
		INSERT INTO tmp_Insert_RateTable_
		SELECT r.RateID,p_EffectiveDate,r.RateTableRateID
			 FROM tmp_all_RateId_ r
			 	 LEFT JOIN tmp_Update_RateTable_ ur
					 ON r.RateID=ur.RateID
			   WHERE ur.RateID is null ;
				
		
		INSERT INTO tblRateTableRate (
			RateID,
			RateTableId,
			Rate,
			EffectiveDate,
			created_at,
			CreatedBy,
			Interval1,
			IntervalN,
			ConnectionFee
		)
	    SELECT DISTINCT  tr.RateID,
		 						RateTableId,
		 						CASE WHEN p_Update_Rate = 1
									THEN    
										p_Rate									
									ELSE
										tr.Rate
									END
								AS Rate,
								p_EffectiveDate as EffectiveDate,
								NOW() as created_at,
								p_ModifiedBy as CreatedBy,
								CASE WHEN p_Update_Interval1 = 1
									THEN    
										p_Interval1									
									ELSE
										tr.Interval1
									END
								AS Interval1,
								CASE WHEN p_Update_IntervalN = 1
									THEN    
										p_IntervalN									
									ELSE
										tr.IntervalN
									END
								AS IntervalN,
								CASE WHEN p_Update_ConnectionFee = 1
									THEN    
										p_ConnectionFee									
									ELSE
										tr.ConnectionFee
									END
								AS ConnectionFee	
			 FROM tblRateTableRate tr
		   	 INNER JOIN tmp_Insert_RateTable_ r
			 		 ON  r.RateID = tr.RateID
			 		 	AND r.RateTableRateID = tr.RateTableRateID
	   		 		AND  RateTableId = p_RateTableId; 
	
	END IF; 
	
	
	
	SET @stm = '';		
	
	IF p_Update_Rate = 1
	THEN
		SET @stm = CONCAT(@stm,',Rate = ',p_Rate);		
	END IF;	
	
	IF p_Update_Interval1 = 1
	THEN
		SET @stm = CONCAT(@stm,',Interval1 = ',p_Interval1);		
	END IF;	
	
	IF p_Update_IntervalN = 1
	THEN
		SET @stm = CONCAT(@stm,',IntervalN = ',p_IntervalN);		
	END IF;	
	
	IF p_Update_ConnectionFee = 1
	THEN
		SET @stm = CONCAT(@stm,',ConnectionFee = ',p_ConnectionFee);		
	END IF;	
	
	IF @stm != ''
	THEN					
			SET @stm = CONCAT('
							UPDATE tblRateTableRate tr
						    INNER JOIN tmp_Update_RateTable_ r
							 	 ON  r.RateID = tr.RateID 
							 	 	AND r.RateTableRateID = tr.RateTableRateID  
										SET updated_at=NOW(),ModifiedBy="',p_ModifiedBy,'"',@stm,'
								    WHERE  RateTableId = ',p_RateTableId,';
						');
						
			PREPARE stmt FROM @stm;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
							
	END IF;						
		
	CALL prc_ArchiveOldRateTableRate(p_RateTableId); 
	
	END IF;
	
	IF p_action = 2
	THEN
		DELETE tblRateTableRate
			 FROM tblRateTableRate
				INNER JOIN tmp_RateTable_ 
					ON tblRateTableRate.RateTableRateID = tmp_RateTable_.RateTableRateID;
		
		
	END IF;
	
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_setAccountDiscountPlan` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_setAccountDiscountPlan`(IN `p_AccountID` INT, IN `p_DiscountPlanID` INT, IN `p_Type` INT, IN `p_BillingDays` INT, IN `p_DayDiff` INT, IN `p_CreatedBy` VARCHAR(50))
BEGIN
	
	DECLARE v_AccountDiscountPlanID INT;
	DECLARE v_StartDate DATE;
	DECLARE v_EndDate DATE;
	
	
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	
	SELECT StartDate,EndDate INTO v_StartDate,v_EndDate FROM tblAccountBillingPeriod WHERE AccountID = p_AccountID AND StartDate <= DATE(NOW()) AND EndDate > DATE(NOW());
	
	INSERT INTO tblAccountDiscountPlanHistory(AccountID,AccountDiscountPlanID,DiscountPlanID,Type,CreatedBy,Applied,Changed,StartDate,EndDate)
	SELECT AccountID,AccountDiscountPlanID,DiscountPlanID,Type,CreatedBy,created_at,NOW(),StartDate,EndDate FROM tblAccountDiscountPlan WHERE AccountID = p_AccountID AND Type = p_Type;
	
	INSERT INTO tblAccountDiscountSchemeHistory (AccountDiscountSchemeID,AccountDiscountPlanID,DiscountID,Threshold,Discount,Unlimited,SecondsUsed)
	SELECT ads.AccountDiscountSchemeID,ads.AccountDiscountPlanID,ads.DiscountID,ads.Threshold,ads.Discount,ads.Unlimited,ads.SecondsUsed 
	FROM tblAccountDiscountScheme ads
	INNER JOIN tblAccountDiscountPlan adp
		ON adp.AccountDiscountPlanID = ads.AccountDiscountPlanID
	WHERE AccountID = p_AccountID 
		AND Type = p_Type;
	
	DELETE ads FROM tblAccountDiscountScheme ads
	INNER JOIN tblAccountDiscountPlan adp
		ON adp.AccountDiscountPlanID = ads.AccountDiscountPlanID
	WHERE AccountID = p_AccountID 
		AND Type = p_Type;
		
	DELETE FROM tblAccountDiscountPlan WHERE AccountID = p_AccountID AND Type = p_Type; 
	
	IF p_DiscountPlanID > 0
	THEN
	 
		INSERT INTO tblAccountDiscountPlan (AccountID,DiscountPlanID,Type,CreatedBy,created_at,StartDate,EndDate)
		VALUES (p_AccountID,p_DiscountPlanID,p_Type,p_CreatedBy,now(),v_StartDate,v_EndDate);
		
		SET v_AccountDiscountPlanID = LAST_INSERT_ID(); 
		
		INSERT INTO tblAccountDiscountScheme(AccountDiscountPlanID,DiscountID,Threshold,Discount,Unlimited)
		SELECT v_AccountDiscountPlanID,d.DiscountID,Threshold*(p_DayDiff/p_BillingDays),Discount,Unlimited
		FROM tblDiscountPlan dp
		INNER JOIN tblDiscount d 
			ON d.DiscountPlanID = dp.DiscountPlanID
		INNER JOIN tblDiscountScheme ds
			ON ds.DiscountID = d.DiscountID
		WHERE dp.DiscountPlanID = p_DiscountPlanID;
	
	END IF;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_SplitAndInsertVendorRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_SplitAndInsertVendorRate`(IN `TempVendorRateID` INT, IN `Code` VARCHAR(500))
BEGIN

	DECLARE v_First_ INT;
	DECLARE v_Last_ INT;	
		
	SELECT  REPLACE(SUBSTRING(SUBSTRING_INDEX(Code, '-', 1)
                 , LENGTH(SUBSTRING_INDEX(Code, '-', 0)) + 1)
                 , '-'
                 , '') INTO v_First_;
                 
	 SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(Code, '-', 2)
                 , LENGTH(SUBSTRING_INDEX(Code, '-', 1)) + 1)
                 , '-'
                 , '') INTO v_Last_;                 
	
	
   WHILE v_Last_ >= v_First_
	DO
   	 INSERT my_splits VALUES (TempVendorRateID,v_Last_);
	    SET v_Last_ = v_Last_ - 1;
  END WHILE;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_SplitVendorRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_SplitVendorRate`(IN `p_processId` VARCHAR(200), IN `p_dialcodeSeparator` VARCHAR(50))
BEGIN

	DECLARE i INTEGER;
	DECLARE v_rowCount_ INT;
	DECLARE v_pointer_ INT;	
	DECLARE v_TempVendorRateID_ INT;
	DECLARE v_Code_ VARCHAR(500);	
	DECLARE newcodecount INT(11) DEFAULT 0;
	
	IF p_dialcodeSeparator !='null'
	THEN
	
	
	
	
	
	DROP TEMPORARY TABLE IF EXISTS `my_splits`;
	CREATE TEMPORARY TABLE `my_splits` (
		`TempVendorRateID` INT(11) NULL DEFAULT NULL,
		`Code` Text NULL DEFAULT NULL
	);
    
  SET i = 1;
  REPEAT
    INSERT INTO my_splits (TempVendorRateID, Code)
      SELECT TempVendorRateID , FnStringSplit(Code, p_dialcodeSeparator, i)  FROM tblTempVendorRate
      WHERE FnStringSplit(Code, p_dialcodeSeparator , i) IS NOT NULL
			 AND ProcessId = p_processId;
    SET i = i + 1;
    UNTIL ROW_COUNT() = 0
  END REPEAT;
  
  UPDATE my_splits SET Code = trim(Code);
  
	
  
  
  DROP TEMPORARY TABLE IF EXISTS tmp_newvendor_splite_;
	CREATE TEMPORARY TABLE tmp_newvendor_splite_  (
		RowID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		TempVendorRateID INT(11) NULL DEFAULT NULL,
		Code VARCHAR(500) NULL DEFAULT NULL
	);
	
	INSERT INTO tmp_newvendor_splite_(TempVendorRateID,Code)
	SELECT 
		TempVendorRateID,
		Code
	FROM my_splits
	WHERE Code like '%-%'
		AND TempVendorRateID IS NOT NULL;

  
  
	SET v_pointer_ = 1;
	SET v_rowCount_ = (SELECT COUNT(*)FROM tmp_newvendor_splite_);
	
	WHILE v_pointer_ <= v_rowCount_
	DO
		SET v_TempVendorRateID_ = (SELECT TempVendorRateID FROM tmp_newvendor_splite_ t WHERE t.RowID = v_pointer_); 
		SET v_Code_ = (SELECT Code FROM tmp_newvendor_splite_ t WHERE t.RowID = v_pointer_);
		
		Call prc_SplitAndInsertVendorRate(v_TempVendorRateID_,v_Code_);
		
	SET v_pointer_ = v_pointer_ + 1;
	END WHILE;
	
	
	
	DELETE FROM my_splits
		WHERE Code like '%-%'
			AND TempVendorRateID IS NOT NULL;

	

	
	
	 INSERT INTO tmp_split_VendorRate_
	SELECT DISTINCT
		   my_splits.TempVendorRateID as `TempVendorRateID`,
		   `CodeDeckId`,
		   my_splits.Code as Code,
		   `Description`,
			`Rate`,
			`EffectiveDate`,
			`Change`,
			`ProcessId`,
			`Preference`,
			`ConnectionFee`,
			`Interval1`,
			`IntervalN`,
			`Forbidden`
		 FROM my_splits
		   INNER JOIN tblTempVendorRate 
				ON my_splits.TempVendorRateID = tblTempVendorRate.TempVendorRateID
		  WHERE	tblTempVendorRate.ProcessId = p_processId;	
		  
	END IF;
	
	IF p_dialcodeSeparator = 'null'
	THEN
	
		INSERT INTO tmp_split_VendorRate_
		SELECT DISTINCT
			  `TempVendorRateID`,
			  `CodeDeckId`,
			   `Code`,
			   `Description`,
				`Rate`,
				`EffectiveDate`,
				`Change`,
				`ProcessId`,
				`Preference`,
				`ConnectionFee`,
				`Interval1`,
				`IntervalN`,
				`Forbidden`
			 FROM tblTempVendorRate
			  WHERE ProcessId = p_processId;	
	
	END IF;	
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_tmpUseTempTable` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_tmpUseTempTable`()
BEGIN

    
 
    
     Call prc_createtemptable();

	select * from tmpSplit;
	
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_UpdateAccountsStatus` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_UpdateAccountsStatus`(IN `p_CompanyID` int, IN `p_userID` int , IN `p_IsVendor` int , IN `p_isCustomer` int , IN `p_VerificationStatus` int, IN `p_AccountNo` VARCHAR(100), IN `p_ContactName` VARCHAR(50), IN `p_AccountName` VARCHAR(50), IN `p_tags` VARCHAR(50), IN `p_low_balance` INT, IN `P_status` INT)
BEGIN
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	
	UPDATE   tblAccount ta
	LEFT JOIN tblContact tc 
		ON tc.Owner=ta.AccountID
	LEFT JOIN tblAccountBalance abc
		ON abc.AccountID = ta.AccountID
	SET ta.Status = P_status
	WHERE ta.CompanyID = p_CompanyID
		AND ta.AccountType = 1
		AND ta.VerificationStatus = p_VerificationStatus
		AND (p_userID = 0 OR ta.Owner = p_userID)
		AND ((p_IsVendor = 0 OR ta.IsVendor = 1))
		AND ((p_isCustomer = 0 OR ta.IsCustomer = 1))
		AND ((p_AccountNo = '' OR ta.Number like p_AccountNo))
		AND ((p_AccountName = '' OR ta.AccountName like Concat('%',p_AccountName,'%')))
		AND ((p_tags = '' OR ta.tags like Concat(p_tags,'%')))
		AND ((p_ContactName = '' OR (CONCAT(IFNULL(tc.FirstName,'') ,' ', IFNULL(tc.LastName,''))) like Concat('%',p_ContactName,'%')))
		AND (p_low_balance = 0 OR ( p_low_balance = 1 AND abc.PermanentCredit > 0 AND (CASE WHEN abc.BalanceThreshold LIKE '%p' THEN REPLACE(abc.BalanceThreshold, 'p', '')/ 100 * abc.PermanentCredit ELSE abc.BalanceThreshold END) < abc.BalanceAmount) );

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_UpdateFailedJobToPending` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_UpdateFailedJobToPending`(IN `p_JobID` INT)
BEGIN

	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 	
 	
 	
 	UPDATE tblJob 
 	SET 
 	
		JobStatusID = ( Select tblJobStatus.JobStatusID from tblJobStatus where tblJobStatus.Code = 'P' ),
		PID = NULL,
		updated_at = now()
 	WHERE 
 	JobID  = p_JobID;
	 
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_UpdateInProgressJobStatusToFail` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_UpdateInProgressJobStatusToFail`(IN `p_JobID` INT, IN `p_JobStatusID` INT, IN `p_JobStatusMessage` LONGTEXT, IN `p_UserName` VARCHAR(250))
BEGIN

	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 	
 	
 	
 	
 	IF (SELECT count(*) from tblJob WHERE JobID  = p_JobID 	AND JobStatusID = ( Select tblJobStatus.JobStatusID from tblJobStatus where tblJobStatus.Code = 'I' ) ) = 1
	 THEN
 	
		 	UPDATE tblJob 
		 	SET 
		 	
				JobStatusID = p_JobStatusID,
				PID = NULL,
				ModifiedBy  = p_UserName,
				updated_at = now()
				
		 	WHERE 
		 	JobID  = p_JobID
			AND JobStatusID = ( Select tblJobStatus.JobStatusID from tblJobStatus where tblJobStatus.Code = 'I' );
 	
		 	IF p_JobStatusMessage != '' THEN
		
				UPDATE tblJob 
				SET 
					JobStatusMessage = concat(JobStatusMessage ,'\n' ,p_JobStatusMessage)
				WHERE 
				JobID  = p_JobID;
			 
		 	END IF;

		
   		SELECT '1' as result;
	
	ELSE
	
		SELECT '0' as result;	
		
	END IF;
	
 	
	 
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_UpdatePendingJobToCanceled` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_UpdatePendingJobToCanceled`(IN `p_JobID` INT, IN `p_UserName` VARCHAR(500))
BEGIN

	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 	
 	
 	
 	UPDATE tblJob 
 	SET 
		JobStatusID = ( Select tblJobStatus.JobStatusID from tblJobStatus where tblJobStatus.Code = 'C' ),
		ModifiedBy  = p_UserName,
		JobStatusMessage = concat(  p_UserName , ' has cancelled the job. ', '\n' , JobStatusMessage  ),
		updated_at = now()
 	WHERE 
 	JobID  = p_JobID
 	AND JobStatusID = ( Select tblJobStatus.JobStatusID from tblJobStatus where tblJobStatus.Code = 'P' );
	 
	 
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_VendorBlockByCodeBlock` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_VendorBlockByCodeBlock`(IN `p_AccountId` int , IN `p_Trunk` varchar(50) , IN `p_RateId` int , IN `p_Username` varchar(100)
)
BEGIN
   
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

	IF  (SELECT COUNT(*) FROM tblVendorBlocking WHERE AccountId = p_AccountId AND TrunkID = p_Trunk AND RateId = p_RateId) = 0
	THEN 
	
	  INSERT INTO tblVendorBlocking
	      (
	         `AccountId`        
	         ,`RateId`
	         ,`TrunkID`
	         ,`BlockedBy`
	      )          
	      VALUES (
	        p_AccountId  
	        ,p_RateId
	        ,p_Trunk
	        ,p_Username 
	        );
	END IF;
	
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
		
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_VendorBlockByCodeUnblock` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_VendorBlockByCodeUnblock`(IN `p_AccountId` int , IN `p_Trunk` varchar(50) , IN `p_RateId` int)
BEGIN
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;


  
  DELETE FROM `tblVendorBlocking`
   WHERE AccountId = p_AccountId
    AND TrunkID = p_Trunk AND RateId = p_RateId;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_VendorBlockByCountryBlock` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_VendorBlockByCountryBlock`(IN `p_AccountId` int , IN `p_Trunk` varchar(50) , IN `p_CountryId` int , IN `p_Username` varchar(100)
)
BEGIN
  
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
	IF  (SELECT COUNT(*) FROM tblVendorBlocking WHERE AccountId = p_AccountId AND TrunkID = p_Trunk AND CountryId = p_CountryId) = 0
	THEN 

		  INSERT INTO tblVendorBlocking
	      (
	         `AccountId`        
	         ,CountryId
	         ,`TrunkID`
	         ,`BlockedBy`
	      )          
	      VALUES (
	        p_AccountId  
	        ,p_CountryId
	        ,p_Trunk
	        ,p_Username 
	        );
	END IF;

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_VendorBlockByCountryUnblock` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_VendorBlockByCountryUnblock`(IN `p_AccountId` int , IN `p_Trunk` varchar(50) , IN `p_CountryId` int)
BEGIN 
  
  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  DELETE FROM `tblVendorBlocking`
   WHERE AccountId = p_AccountId
    AND TrunkID = p_Trunk AND CountryId = p_CountryId;
    
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_VendorBlockUnblockByAccount` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_VendorBlockUnblockByAccount`(IN `p_CompanyId` int, IN `p_AccountId` int, IN `p_code` VARCHAR(50),`p_RateId` longtext,IN `p_CountryId` longtext,IN `p_TrunkID` varchar(50) ,     IN `p_Username` varchar(100),IN `p_action` varchar(100) )
BEGIN
 
  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

  if(p_action ='country_block')
  THEN
		  
			  INSERT INTO tblVendorBlocking
			  (
					 `AccountId`
					 ,CountryId
					 ,`TrunkID`
					 ,`BlockedBy`
			  )
			  SELECT  
				p_AccountId as AccountId
				,tblCountry.CountryID as CountryId
				,p_TrunkID as TrunkID
				,p_Username as BlockedBy
				FROM    tblCountry
				LEFT JOIN tblVendorBlocking ON tblVendorBlocking.CountryId = tblCountry.CountryID AND TrunkID = p_TrunkID AND AccountId = p_AccountId
				WHERE  ( p_CountryId ='' OR  FIND_IN_SET(tblCountry.CountryID, p_CountryId) ) AND tblVendorBlocking.VendorBlockingId is null;
							  
  END IF;
  
  if(p_action ='country_unblock')
  THEN
  
	delete  from tblVendorBlocking 
	WHERE  AccountId = p_AccountId AND TrunkID = p_TrunkID AND ( p_CountryId ='' OR FIND_IN_SET(CountryId, p_CountryId) );
			

  END IF;

  IF(p_action ='code_block')
  THEN
		   
					IF(p_RateId = '' ) 
					THEN
                            
					  INSERT INTO tblVendorBlocking
					  (
						 `AccountId`
						 ,`RateId`
						 ,`TrunkID`
						 ,`BlockedBy`
					  )
					  SELECT tblVendorRate.AccountID, tblVendorRate.RateId ,tblVendorRate.TrunkID,p_Username as BlockedBy
					  FROM    `tblRate`
					  INNER JOIN tblVendorRate ON tblRate.RateID = tblVendorRate.RateId AND tblVendorRate.AccountID = p_AccountId AND tblVendorRate.TrunkID = p_TrunkID
					  INNER JOIN tblVendorTrunk ON tblVendorTrunk.CodeDeckId = tblRate.CodeDeckId AND tblVendorTrunk.AccountID = p_AccountId AND tblVendorTrunk.TrunkID = p_TrunkID
					  LEFT JOIN tblVendorBlocking ON tblVendorBlocking.RateId = tblVendorRate.RateID and tblVendorRate.TrunkID = tblVendorBlocking.TrunkID and tblVendorRate.AccountId = tblVendorBlocking.AccountId
					  WHERE tblVendorBlocking.VendorBlockingId is null AND ( tblRate.CompanyID = p_CompanyId ) AND ( p_code = '' OR  tblRate.Code LIKE REPLACE(p_code,'*', '%') );
						
					ELSE 
     
						  INSERT INTO tblVendorBlocking
						  (
							 `AccountId`
							 ,`RateId`
							 ,`TrunkID`
							 ,`BlockedBy`
						  )
						  SELECT p_AccountId as AccountId, RateID ,p_TrunkID as TrunkID ,p_Username as BlockedBy 
						  FROM tblRate 
						  WHERE FIND_IN_SET(RateId, p_RateId) > 0 AND CompanyID = p_CompanyId;
					 
					  
			 		END IF;				
				
END IF;
 IF(p_action ='code_unblock')
  THEN
 
			IF(p_RateId = ''  ) THEN
			
			     
				delete tblVendorBlocking from tblVendorBlocking 
					INNER JOIN(
						select VendorBlockingId 
						FROM `tblVendorBlocking` vb  
						INNER JOIN 
						(
							SELECT tblRate.RateID ,tblVendorRate.AccountID
								FROM    `tblRate`
							INNER JOIN tblVendorRate ON tblRate.RateID = tblVendorRate.RateId AND tblVendorRate.AccountID = p_AccountId AND tblVendorRate.TrunkID = p_TrunkID
							INNER JOIN tblVendorTrunk ON tblVendorTrunk.CodeDeckId = tblRate.CodeDeckId AND tblVendorTrunk.AccountID = p_AccountId AND tblVendorTrunk.TrunkID = p_TrunkID
							LEFT JOIN tblVendorBlocking ON tblVendorBlocking.RateId = tblVendorRate.RateID and tblVendorRate.TrunkID = tblVendorBlocking.TrunkID and tblVendorRate.AccountId = tblVendorBlocking.AccountId
							WHERE ( tblRate.CompanyID = p_CompanyId )
							 AND ( p_code = '' OR tblRate.Code LIKE REPLACE(p_code,'*', '%') )
						) v on v.AccountID = vb.AccountId  AND  vb.RateID = v.RateID  AND vb.TrunkID = p_TrunkID 
						
					)vb2 on vb2.VendorBlockingId=tblVendorBlocking.VendorBlockingId;	

			ELSE 

                    
				delete tblVendorBlocking from tblVendorBlocking WHERE AccountId = p_AccountId AND TrunkID = p_TrunkID AND FIND_IN_SET(RateId, p_RateId) > 0;
				
				
			END IF;	
 
  END IF;
  
  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_VendorBulkRateDelete` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_VendorBulkRateDelete`(IN `p_CompanyId` INT
, IN `p_AccountId` INT
, IN `p_TrunkId` INT 
, IN `p_VendorRateIds` TEXT, IN `p_code` varchar(50)
, IN `p_description` varchar(200)
, IN `p_CountryId` INT
, IN `p_effective` VARCHAR(50)
, IN `p_action` INT)
BEGIN

	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	 
	DROP TEMPORARY TABLE IF EXISTS tmp_Delete_VendorRate;
	CREATE TEMPORARY TABLE tmp_Delete_VendorRate (
     	VendorRateID INT,
      AccountId INT,
      TrunkID INT,
      RateId INT,
      Code VARCHAR(50),
      Description VARCHAR(200),
      Rate DECIMAL(18, 6),
      EffectiveDate DATETIME,
		Interval1 INT,
		IntervalN INT,
		ConnectionFee DECIMAL(18, 6),
		deleted_at DATETIME,
        INDEX tmp_VendorRateDiscontinued_VendorRateID (`VendorRateID`)
	);
	 
	 IF p_action = 1
	 THEN
	 
	  
	 INSERT INTO tmp_Delete_VendorRate(
	 	VendorRateID,
      AccountId,
      TrunkID,
      RateId,
      Code,
      Description,
      Rate,
      EffectiveDate,
		Interval1,
		IntervalN,
		ConnectionFee,
		deleted_at
	 )
		SELECT
		  VendorRateID,
		  p_AccountId AS AccountId,
		  p_TrunkId AS TrunkID,
		  v.RateId,
		  r.Code,
		  r.Description,
		  Rate,
		  EffectiveDate,
		  v.Interval1,
		  v.IntervalN,
		  ConnectionFee,
		  now() AS deleted_at
		FROM tblVendorRate v
		  INNER JOIN tblRate r
		  		ON r.RateID = v.RateId
		  INNER JOIN tblVendorTrunk vt
		  		ON vt.trunkID = p_TrunkId
				   AND vt.AccountID = p_AccountId
					AND vt.CodeDeckId = r.CodeDeckId
		WHERE 
			((p_CountryId IS NULL) OR (p_CountryId IS NOT NULL AND r.CountryId = p_CountryId))
			AND ((p_code IS NULL) OR (p_code IS NOT NULL AND r.Code like REPLACE(p_code,'*', '%')))
			AND ((p_description IS NULL) OR (p_description IS NOT NULL AND r.Description like REPLACE(p_description,'*', '%')))
		 	AND ((p_effective = 'Now' AND v.EffectiveDate <= NOW() ) OR (p_effective = 'Future' AND v.EffectiveDate> NOW() ) )
		 	AND v.AccountId = p_AccountId
			AND v.TrunkID = p_TrunkId;

	 END IF;
	
	IF p_action = 2
	THEN
		
		
		
			
		 INSERT INTO tmp_Delete_VendorRate(
		 	VendorRateID,
	      AccountId,
	      TrunkID,
	      RateId,
	      Code,
	      Description,
	      Rate,
	      EffectiveDate,
			Interval1,
			IntervalN,
			ConnectionFee,
			deleted_at
		 )
		SELECT
		  VendorRateID,
		  p_AccountId AS AccountId,
		  p_TrunkId AS TrunkID,
		  v.RateId,
		  r.Code,
		  r.Description,
		  Rate,
		  EffectiveDate,
		  v.Interval1,
		  v.IntervalN,
		  ConnectionFee,
		  now() AS deleted_at
		FROM tblVendorRate v
		  INNER JOIN tblRate r
		  		ON r.RateID = v.RateId
		WHERE v.AccountId = p_AccountId
				AND v.TrunkID = p_TrunkId
				AND ((p_VendorRateIds IS NULL) OR FIND_IN_SET(VendorRateID,p_VendorRateIds)!=0)
				;
	END IF; 	
	
	
	
	CALL prc_InsertDiscontinuedVendorRate(p_AccountId,p_TrunkId);
	
		 
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_VendorBulkRateUpdate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_VendorBulkRateUpdate`(IN `p_AccountId` INT
, IN `p_TrunkId` INT 
, IN `p_code` varchar(50)
, IN `p_description` varchar(200)
, IN `p_CountryId` INT
, IN `p_CompanyId` INT
, IN `p_Rate` decimal(18,6)
, IN `p_EffectiveDate` DATETIME
, IN `p_ConnectionFee` decimal(18,6)
, IN `p_Interval1` INT
, IN `p_IntervalN` INT
, IN `p_ModifiedBy` varchar(50)
, IN `p_effective` VARCHAR(50)
, IN `p_action` INT)
BEGIN

	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
	
	IF p_action = 1
	
	THEN
	
	UPDATE tblVendorRate

	INNER JOIN
	( 
	SELECT VendorRateID
	  FROM tblVendorRate v
	  INNER JOIN tblRate r ON r.RateID = v.RateId
	  INNER JOIN tblVendorTrunk vt on vt.trunkID = p_TrunkId AND vt.AccountID = p_AccountId AND vt.CodeDeckId = r.CodeDeckId
	 WHERE 
	 ((p_CountryId IS NULL) OR (p_CountryId IS NOT NULL AND r.CountryId = p_CountryId))
	 AND ((p_code IS NULL) OR (p_code IS NOT NULL AND r.Code like REPLACE(p_code,'*', '%')))
	 AND ((p_description IS NULL) OR (p_description IS NOT NULL AND r.Description like REPLACE(p_description,'*', '%')))
	 AND  ((p_effective = 'Now' and v.EffectiveDate <= NOW() ) OR (p_effective = 'Future' and v.EffectiveDate> NOW() ) )
	 AND v.AccountId = p_AccountId AND v.TrunkID = p_TrunkId
	 ) vr 
	 ON vr.VendorRateID = tblVendorRate.VendorRateID
	 	SET 
		Rate = p_Rate, 
		EffectiveDate = p_EffectiveDate,
		Interval1 = p_Interval1, 
		IntervalN = p_IntervalN,
		updated_by = p_ModifiedBy,
		ConnectionFee = p_ConnectionFee,
		updated_at = NOW(); 
 	END IF;

	 CALL prc_ArchiveOldVendorRate(p_AccountId,p_TrunkId);

	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_VendorPreferenceUpdateBySelectedRateId` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_VendorPreferenceUpdateBySelectedRateId`(IN `p_CompanyId` INT, IN `p_AccountId` LONGTEXT , IN `p_RateIDList` LONGTEXT , IN `p_TrunkId` VARCHAR(100) , IN `p_Preference` INT, IN `p_ModifiedBy` VARCHAR(50)
, IN `p_contryID` INT, IN `p_code` VARCHAR(50), IN `p_description` VARCHAR(50), IN `p_action` INT)
BEGIN


	DECLARE v_CodeDeckId_ int;

	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
	select CodeDeckId into v_CodeDeckId_  from tblVendorTrunk where AccountID = p_AccountID and TrunkID = p_trunkID;
	
	IF p_action = 0
	THEN	
			  DROP TEMPORARY TABLE IF EXISTS tblVendorRate_;
			  CREATE TEMPORARY TABLE tblVendorRate_  (
				RateID INT(11),				
				INDEX tmp_RateID (`RateID`) 
			  );
			  
			  INSERT INTO tblVendorRate_
			  SELECT RateID from tblRate 
				where FIND_IN_SET(RateID,p_RateIDList);
			  
			  SELECT *FROM	tblVendorRate_;
	END IF; 	
	
	IF p_action = 1
	THEN	
			DROP TEMPORARY TABLE IF EXISTS tblVendorRate_;
			  CREATE TEMPORARY TABLE tblVendorRate_  (
				RateID INT(11),
				INDEX tmp_RateID (`RateID`) 
			  );
  
			INSERT INTO tblVendorRate_
			SELECT 
				tblRate.RateID as RateID
			FROM tblVendorRate
			JOIN tblRate
				ON tblVendorRate.RateId = tblRate.RateId
			LEFT JOIN tblVendorPreference 
					ON tblVendorPreference.AccountId = tblVendorRate.AccountId
                    AND tblVendorPreference.TrunkID = tblVendorRate.TrunkID
                    AND tblVendorPreference.RateId = tblVendorRate.RateId
			WHERE (p_contryID IS NULL OR CountryID = p_contryID)
			AND (p_code IS NULL OR Code LIKE REPLACE(p_code, '*', '%'))
			AND (p_description IS NULL OR tblRate.Description LIKE REPLACE(p_description, '*', '%'))
			AND (tblRate.CompanyID = p_companyid)
			AND tblVendorRate.TrunkID = p_trunkID
			AND tblVendorRate.AccountID = p_AccountID
			AND CodeDeckId = v_CodeDeckId_;
	END IF;			
			
			UPDATE  tblVendorPreference v
		    INNER JOIN (
			select  tvp.VendorPreferenceID,tvp.RateId from tblVendorPreference tvp
			LEFT JOIN tblVendorRate_ vr on tvp.RateId = vr.RateID  			
			  where tvp.AccountId = p_AccountId
				and tvp.TrunkID = p_TrunkId
				and vr.RateID IS NOT NULL
		    )vp on vp.VendorPreferenceID = v.VendorPreferenceID
			SET Preference = p_Preference;
			
			INSERT  INTO tblVendorPreference
		   (  RateID ,
			AccountId ,
			  TrunkID ,
			  Preference,
			  CreatedBy,
			  created_at
		   )
		   
		   SELECT  DISTINCT
			 vr.RateID,
			 p_AccountId ,
			 p_TrunkId,
			 p_Preference ,
			 p_ModifiedBy,
			 NOW()
			 from tblVendorRate_ vr 
			 LEFT JOIN tblVendorPreference vp 
				ON vr.RateID = vp.RateID and vp.AccountId = p_AccountId and vp.TrunkID = p_TrunkId 
				where vp.RateID is null;
				
			IF p_Preference = 0
			THEN		
			delete tblVendorPreference FROM tblVendorPreference INNER JOIN(
				select vr.RateId FROM tblVendorRate_ vr)
				tr on tr.RateId=tblVendorPreference.RateID WHERE tblVendorPreference.AccountId = p_AccountId
	            AND tblVendorPreference.TrunkID = p_TrunkId;		
			END IF;	
	
			
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSCronJobDeleteOldCustomerRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSCronJobDeleteOldCustomerRate`()
BEGIN
     
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

   DELETE cr
   FROM tblCustomerRate cr
   INNER JOIN tblCustomerRate cr2
   ON cr2.CustomerID = cr.CustomerID
   AND cr2.TrunkID = cr.TrunkID
   AND cr2.RateID = cr.RateID
   WHERE   cr.EffectiveDate <= NOW() AND cr2.EffectiveDate <= NOW() AND cr.EffectiveDate < cr2.EffectiveDate;

    DROP TEMPORARY TABLE IF EXISTS _tmp_ArchiveOldEffectiveRates;
   CREATE TEMPORARY TABLE _tmp_ArchiveOldEffectiveRates (
    	  RowID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
     	  CustomerRateID INT,
        RateID INT,
        CustomerID INT,
        TrunkId INT,
        Rate DECIMAL(18, 6),
        EffectiveDate DATETIME,
        INDEX _tmp_ArchiveOldEffectiveRates_CustomerRateID (`CustomerRateID`,`RateID`,`TrunkId`)
	);
	DROP TEMPORARY TABLE IF EXISTS _tmp_ArchiveOldEffectiveRates2;
	CREATE TEMPORARY TABLE _tmp_ArchiveOldEffectiveRates2 (
    	  RowID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
     	  CustomerRateID INT,
        RateID INT,
        CustomerID INT,
        TrunkId INT,
        Rate DECIMAL(18, 6),
        EffectiveDate DATETIME,
       INDEX _tmp_ArchiveOldEffectiveRates_CustomerRateID (`CustomerRateID`,`RateID`,`TrunkId`)
	);
    
   INSERT INTO _tmp_ArchiveOldEffectiveRates (CustomerRateID,RateID,CustomerID,TrunkID,Rate,EffectiveDate)	
	SELECT
	   cr.CustomerRateID,
	   cr.RateID,
	   cr.CustomerID,
	   cr.TrunkID,
	   cr.Rate,
	   cr.EffectiveDate
	FROM tblCustomerRate cr
	ORDER BY cr.CustomerID ASC,cr.TrunkID ASC,cr.RateID ASC,EffectiveDate ASC;
	
	INSERT INTO _tmp_ArchiveOldEffectiveRates2
	SELECT * FROM _tmp_ArchiveOldEffectiveRates;

    DELETE tblCustomerRate
        FROM tblCustomerRate
        INNER JOIN(
        SELECT
            tt.CustomerRateID
        FROM _tmp_ArchiveOldEffectiveRates t
        INNER JOIN _tmp_ArchiveOldEffectiveRates2 tt
        ON tt.RowID = t.RowID + 1
            AND  t.CustomerId = tt.CustomerId
			   AND  t.TrunkId = tt.TrunkId
            AND t.RateID = tt.RateID
            AND t.Rate = tt.Rate) aold on aold.CustomerRateID = tblCustomerRate.CustomerRateID;
            
   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;         
	            
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSCronJobDeleteOldRateSheetDetails` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSCronJobDeleteOldRateSheetDetails`()
BEGIN
     
	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

    DELETE tblRateSheetDetails
    FROM tblRateSheetDetails
    INNER JOIN
    (
       SELECT
				DISTINCT
            rs.RateSheetID
         FROM tblRateSheet rs,tblRateSheet rs2
         WHERE rs.CustomerID = rs2.CustomerID
         AND rs.`Level` = rs2.`Level`
         AND rs.RateSheetID < rs2.RateSheetID 
    ) tbl ON tblRateSheetDetails.RateSheetID = tbl.RateSheetID;
        
   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSCronJobDeleteOldRateTableRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSCronJobDeleteOldRateTableRate`()
BEGIN
    
	 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 

   DELETE rtr
      FROM tblRateTableRate rtr
      INNER JOIN tblRateTableRate rtr2
      ON rtr.RateTableId = rtr2.RateTableId
      AND rtr.RateID = rtr2.RateID
      WHERE rtr.EffectiveDate <= NOW()
			AND rtr2.EffectiveDate <= NOW()
         AND rtr.EffectiveDate < rtr2.EffectiveDate;
            
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;            

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSCronJobDeleteOldVendorRate` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSCronJobDeleteOldVendorRate`()
BEGIN
 	 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
   DELETE vr
      FROM tblVendorRate vr
      INNER JOIN tblVendorRate vr2
      ON vr2.AccountId = vr.AccountId
      AND vr2.TrunkID = vr.TrunkID
      AND vr2.RateID = vr.RateID
      WHERE   vr.EffectiveDate <= NOW()
			AND  vr2.EffectiveDate <= NOW()
         AND vr.EffectiveDate < vr2.EffectiveDate;
	
   DROP TEMPORARY TABLE IF EXISTS _tmp_ArchiveOldEffectiveRates;
   CREATE TEMPORARY TABLE _tmp_ArchiveOldEffectiveRates (
    	  RowID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
     	  VendorRateID INT,
        RateID INT,
        CustomerID INT,
        TrunkId INT,
        Rate DECIMAL(18, 6),
        EffectiveDate DATETIME,
        INDEX _tmp_ArchiveOldEffectiveRates_VendorRateID (`VendorRateID`,`RateID`,`TrunkId`,`CustomerID`)
	);
	DROP TEMPORARY TABLE IF EXISTS _tmp_ArchiveOldEffectiveRates2;
	CREATE TEMPORARY TABLE _tmp_ArchiveOldEffectiveRates2 (
    	  RowID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
     	  VendorRateID INT,
        RateID INT,
        CustomerID INT,
        TrunkId INT,
        Rate DECIMAL(18, 6),
        EffectiveDate DATETIME,
       INDEX _tmp_ArchiveOldEffectiveRates_VendorRateID (`VendorRateID`,`RateID`,`TrunkId`,`CustomerID`)
	);

    
   INSERT INTO _tmp_ArchiveOldEffectiveRates (VendorRateID,RateID,CustomerID,TrunkID,Rate,EffectiveDate)	
	SELECT
	   VendorRateID,
	   RateID,
	   AccountId,
	   TrunkID,
	   Rate,
	   EffectiveDate
	FROM tblVendorRate 
 	ORDER BY AccountId ASC,TrunkID ,RateID ASC,EffectiveDate ASC;
	
	INSERT INTO _tmp_ArchiveOldEffectiveRates2
	SELECT * FROM _tmp_ArchiveOldEffectiveRates;

    DELETE tblVendorRate
        FROM tblVendorRate
        INNER JOIN(
        SELECT
            tt.VendorRateID
        FROM _tmp_ArchiveOldEffectiveRates t
        INNER JOIN _tmp_ArchiveOldEffectiveRates2 tt
            ON tt.RowID = t.RowID + 1
            AND  t.CustomerID = tt.CustomerID
			   AND  t.TrunkId = tt.TrunkId
            AND t.RateID = tt.RateID
            AND t.Rate = tt.Rate) aold on aold.VendorRateID = tblVendorRate.VendorRateID;
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSDeleteOldRateSheetDetails` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSDeleteOldRateSheetDetails`(IN `p_LatestRateSheetID` INT , IN `p_customerID` INT , IN `p_rateSheetCategory` VARCHAR(50)
)
BEGIN
		SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
        
        DELETE  tblRateSheetDetails
        FROM    tblRateSheetDetails
                JOIN tblRateSheet ON tblRateSheet.RateSheetID = tblRateSheetDetails.RateSheetID
        WHERE   CustomerID = p_customerID
                AND Level = p_rateSheetCategory
                AND tblRateSheetDetails.RateSheetID <> p_LatestRateSheetID; 
      SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
                
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `prc_WSGenerateRateSheet` */;
DELIMITER ;;
CREATE DEFINER=`neon-user-dev`@`localhost` PROCEDURE `prc_WSGenerateRateSheet`(IN `p_CustomerID` INT, IN `p_Trunk` VARCHAR(100)
)
BEGIN
    DECLARE v_trunkDescription_ VARCHAR(50);
    DECLARE v_lastRateSheetID_ INT ;
    DECLARE v_IncludePrefix_ TINYINT;
    DECLARE v_Prefix_ VARCHAR(50);
    DECLARE v_codedeckid_  INT;
    DECLARE v_ratetableid_ INT;
    DECLARE v_RateTableAssignDate_  DATETIME;
    DECLARE v_NewA2ZAssign_ INT;

    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;


       SELECT trunk INTO v_trunkDescription_
         FROM   tblTrunk
         WHERE  TrunkID = p_Trunk;

    DROP TEMPORARY TABLE IF EXISTS tmp_RateSheetRate_;
    CREATE TEMPORARY TABLE tmp_RateSheetRate_(
        RateID        INT,
        Destination   VARCHAR(200),
        Codes         VARCHAR(50),
        Interval1     INT,
        IntervalN     INT,
        Rate          DECIMAL(18, 6),
        `level`         VARCHAR(10),
        `change`        VARCHAR(50),
        EffectiveDate  DATE,
			  INDEX tmp_RateSheetRate_RateID (`RateID`)
    );
    DROP TEMPORARY TABLE IF EXISTS tmp_CustomerRates_;
    CREATE TEMPORARY TABLE tmp_CustomerRates_ (
        RateID INT,
        Interval1 INT,
        IntervalN  INT,
        Rate DECIMAL(18, 6),
        EffectiveDate DATE,
        LastModifiedDate DATETIME,
        INDEX tmp_CustomerRates_RateId (`RateID`)
    );
    DROP TEMPORARY TABLE IF EXISTS tmp_RateTableRate_;
    CREATE TEMPORARY TABLE tmp_RateTableRate_ (
        RateID INT,
        Interval1 INT,
        IntervalN  INT,
        Rate DECIMAL(18, 6),
        EffectiveDate DATE,
        updated_at DATETIME,
        INDEX tmp_RateTableRate_RateId (`RateID`)
    );

    DROP TEMPORARY TABLE IF EXISTS tmp_RateSheetDetail_;
    CREATE TEMPORARY TABLE tmp_RateSheetDetail_ (
        ratesheetdetailsid int,
        RateID int ,
        RateSheetID int,
        Destination varchar(200),
        Code varchar(50),
        Rate DECIMAL(18, 6),
        `change` varchar(50),
        EffectiveDate Date,
			  INDEX tmp_RateSheetDetail_RateId (`RateID`,`RateSheetID`)
    );
    SELECT RateSheetID INTO v_lastRateSheetID_
    FROM   tblRateSheet
    WHERE  CustomerID = p_CustomerID
        AND level = v_trunkDescription_
    ORDER  BY RateSheetID DESC LIMIT 1 ;

    SELECT includeprefix INTO v_IncludePrefix_
    FROM   tblCustomerTrunk
    WHERE  AccountID = p_CustomerID
      AND TrunkID = p_Trunk;

    SELECT prefix INTO v_Prefix_
    FROM   tblCustomerTrunk
    WHERE  AccountID = p_CustomerID
      AND TrunkID = p_Trunk;


    SELECT
        CodeDeckId,
        RateTableID,
        RateTableAssignDate
        INTO v_codedeckid_, v_ratetableid_, v_RateTableAssignDate_
    FROM tblCustomerTrunk
    WHERE tblCustomerTrunk.TrunkID = p_Trunk
    AND tblCustomerTrunk.AccountID = p_CustomerID
    AND tblCustomerTrunk.Status = 1;

  INSERT INTO tmp_CustomerRates_
      SELECT  RateID,
              Interval1,
              IntervalN,
              tblCustomerRate.Rate,
              effectivedate,
              lastmodifieddate
      FROM   tblAccount
         JOIN tblCustomerRate
           ON tblAccount.AccountID = tblCustomerRate.CustomerID
      WHERE  tblAccount.AccountID = p_CustomerID
          AND tblCustomerRate.Rate > 0
         AND tblCustomerRate.TrunkID = p_Trunk
         ORDER BY tblCustomerRate.CustomerID,tblCustomerRate.TrunkID,tblCustomerRate.RateID,tblCustomerRate.EffectiveDate DESC;
	
	DROP TEMPORARY TABLE IF EXISTS tmp_CustomerRates4_;
	DROP TEMPORARY TABLE IF EXISTS tmp_CustomerRates2_;
	
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_CustomerRates4_ as (select * from tmp_CustomerRates_);
   DELETE n1 FROM tmp_CustomerRates_ n1, tmp_CustomerRates4_ n2 WHERE n1.EffectiveDate < n2.EffectiveDate
   AND  n1.RateId = n2.RateId;

   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_CustomerRates2_ as (select * from tmp_CustomerRates_);

	INSERT INTO tmp_RateTableRate_
		SELECT
			tblRateTableRate.RateID,
         tblRateTableRate.Interval1,
         tblRateTableRate.IntervalN,
         tblRateTableRate.Rate,         
         tblRateTableRate.EffectiveDate,
         tblRateTableRate.updated_at
      FROM tblAccount
      JOIN tblCustomerTrunk
           ON tblCustomerTrunk.AccountID = tblAccount.AccountID
      JOIN tblRateTable
           ON tblCustomerTrunk.ratetableid = tblRateTable.ratetableid
      JOIN tblRateTableRate
           ON tblRateTableRate.ratetableid = tblRateTable.ratetableid
      LEFT JOIN tmp_CustomerRates_ trc1
            ON trc1.RateID = tblRateTableRate.RateID
      WHERE  tblAccount.AccountID = p_CustomerID
         AND tblRateTableRate.Rate > 0
         AND tblCustomerTrunk.TrunkID = p_Trunk
         AND (( tblRateTableRate.EffectiveDate <= Now()
             AND ( ( trc1.RateID IS NULL )
                OR ( trc1.RateID IS NOT NULL
                   AND tblRateTableRate.ratetablerateid IS NULL )
              ) )
          OR ( tblRateTableRate.EffectiveDate > Now()
             AND ( ( trc1.RateID IS NULL )
                OR ( trc1.RateID IS NOT NULL
                   AND tblRateTableRate.EffectiveDate < (SELECT
									   IFNULL(MIN(crr.EffectiveDate),
									   tblRateTableRate.EffectiveDate)
										  FROM   tmp_CustomerRates2_ crr
										  WHERE  crr.RateID =
					   tblRateTableRate.RateID
                      ) ) ) ) )
      ORDER BY tblRateTableRate.RateID,tblRateTableRate.EffectiveDate desc;

	DROP TEMPORARY TABLE IF EXISTS tmp_RateTableRate4_;
   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_RateTableRate4_ as (select * from tmp_RateTableRate_);
   DELETE n1 FROM tmp_RateTableRate_ n1, tmp_RateTableRate4_ n2 WHERE n1.EffectiveDate < n2.EffectiveDate
   AND  n1.RateId = n2.RateId;

    INSERt INTO tmp_RateSheetDetail_
      SELECT ratesheetdetailsid,
                     RateID,
                     RateSheetID,
                     Destination,
                     Code,
                     Rate,
                     `change`,
                     effectivedate
              FROM   tblRateSheetDetails
              WHERE  RateSheetID = v_lastRateSheetID_
          ORDER BY RateID,effectivedate desc;

	DROP TEMPORARY TABLE IF EXISTS tmp_RateSheetDetail4_;
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_RateSheetDetail4_ as (select * from tmp_RateSheetDetail_);
   DELETE n1 FROM tmp_RateSheetDetail_ n1, tmp_RateSheetDetail4_ n2 WHERE n1.EffectiveDate < n2.EffectiveDate
   AND  n1.RateId = n2.RateId;

      
