DELIMITER ;;
CREATE  PROCEDURE `SPNobat_Level2_FindById`(IN `id` INT)
BEGIN
Select *  From  nobat_level2_items   WHERE Fldid =  id   and FldDeleted = 0   ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPNobat_Level2_Items_search` */;
DELIMITER ;;
CREATE  PROCEDURE `SPNobat_Level2_Items_search`(IN `Wheree` VARCHAR(1055) CHARSET utf8mb4, IN `limitt` INT)
BEGIN
  

  SET @stmt_str = CONCAT(' SELECT *
FROM nobat_level2_items
WHERE nobat_level2_items.FldDeleted = 0  and ', Wheree, '
ORDER BY nobat_level2_items.Fldid   limit ', limitt, ' ');
  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPNobat_Level3_Items_search` */;
DELIMITER ;;
CREATE  PROCEDURE `SPNobat_Level3_Items_search`(IN `Wheree` VARCHAR(1055) CHARSET utf8mb4, IN `limitt` INT)
BEGIN

  SET @stmt_str = CONCAT(' SELECT *
FROM nobat_level3_items
WHERE FldDeleted = 0  and ', Wheree, '
ORDER BY Fldid   limit ', limitt, ' ');
  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPNobat_Level4_Items_search` */;
DELIMITER ;;
CREATE  PROCEDURE `SPNobat_Level4_Items_search`(IN `Wheree` VARCHAR(1055) CHARSET utf8mb4, IN `limitt` INT)
BEGIN

  SET @stmt_str = CONCAT(' SELECT *
FROM nobat_level4_items
WHERE FldDeleted = 0  and ', Wheree, '
ORDER BY Fldid   limit ', limitt, ' ');
  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPNobat_Rating_Aprov` */;
DELIMITER ;;
CREATE  PROCEDURE `SPNobat_Rating_Aprov`(IN t_ype  int , IN idComment  int   )
BEGIN
   
  declare id_level1 bigint;
  declare id_Nobat bigint;
 declare commentsCount int;
 Declare SumRate bigint;
 declare SuggestCount int;
 
 
        update rating set FldStatus = t_ype where  Fldid=idComment and FldDeleted=0  ;


set id_Nobat = ( select  FldIdNobat from rating where  Fldid=idComment and FldDeleted=0 );
set id_level1 = (  select  FldidLastLevel from nobat_allnobats where Fldid = id_Nobat );
set commentsCount = (  SELECT         COUNT(*)   
FROM            rating INNER JOIN
                         nobat_allnobats ON rating.FldIdNobat = nobat_allnobats.Fldid
WHERE        (rating.FldStatus = 2) AND (rating.flddeleted = 0) and nobat_allnobats.FldidLastLevel=id_level1 );
 set SumRate = (  SELECT          sum(rating.fldrate) 
FROM            rating INNER JOIN
                         nobat_allnobats ON rating.FldIdNobat = nobat_allnobats.Fldid
WHERE        (rating.FldStatus = 2) AND (rating.flddeleted = 0) and nobat_allnobats.FldidLastLevel=id_level1 );
 
set SuggestCount = ( SELECT        COUNT(*)   
FROM            rating INNER JOIN
                         nobat_allnobats ON rating.FldIdNobat = nobat_allnobats.Fldid
WHERE        (rating.FldStatus = 2) and rating.FldSuggest = 1 AND (rating.flddeleted = 0) and nobat_allnobats.FldidLastLevel=id_level1);




update nobat_level1 set  FldLasRating =ifnull( SumRate,0) , FldLastCommensCount=ifnull( commentsCount , 0) , FldLastSuggest=SuggestCount where   nobat_level1.Fldid =id_level1 ;



     
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPNobat_ReportAll` */;
DELIMITER ;;
CREATE  PROCEDURE `SPNobat_ReportAll`(IN `flLevel` INT, IN `Wheree` VARCHAR(1055))
BEGIN

if  length(LTRIM(rtrim(Wheree)))<2 then
	  set  Wheree=' 1=1' ;
end if;

 IF flLevel=1  then
  
    SET @stmt_str = CONCAT(' SELECT *,ROW_NUMBER() OVER (Order by  Fldid_L1) AS RowNumber 
  FROM (SELECT        L1.Fldid AS Fldid_L1, L1.Fldtitle AS Fldtitle_L1, AllN.Fldid AS Fldid_AllN, AllN.FldIdCustomer,
  AllN.FldFlag, AllN.FldDate,  fMilladyToShamsi_NoTime(AllN.FldDate) AS FldDateSRT, AllN.FldCallerid, fMilladyToShamsi_byTime( AllN.FldCallTime) AS FldCallTime, 
                         AllN.FldTimeStartSTR, AllN.FldTimeEndSTR, AllN.FldCodeMeli, AllN.FldMobileNO, AllN.Fldvoicepath, AllN.FldActive, AllN.FldCommentVoicePath
FROM            nobat_level1 AS L1 INNER JOIN
                         nobat_allnobats AS AllN ON L1.Fldid = AllN.FldidLastLevel
WHERE        (AllN.FldActive = 1) AND (AllN.FldDeleted = 0) AND (L1.FldDeleted = 0) AND (L1.FldActive = 1) AND (length(L1.Fldvoicepath) > 0) ) as ty  where ', Wheree, '   ');
  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
 end if;
       IF flLevel=2  then
  
    SET @stmt_str = CONCAT(' SELECT *,ROW_NUMBER() OVER (Order by  Fldid_L1) AS RowNumber 
  FROM (SELECT        L1.Fldid AS Fldid_L1, L1.Fldtitle AS Fldtitle_L1, AllN.Fldid AS Fldid_AllN, AllN.FldIdCustomer,
  AllN.FldFlag, AllN.FldDate,  fMilladyToShamsi_NoTime(AllN.FldDate) AS FldDateSRT, AllN.FldCallerid, fMilladyToShamsi_byTime( AllN.FldCallTime) AS FldCallTime, 
                         AllN.FldTimeStartSTR, AllN.FldTimeEndSTR, AllN.FldCodeMeli, AllN.FldMobileNO, AllN.Fldvoicepath, AllN.FldActive, AllN.FldCommentVoicePath
FROM            nobat_level1 AS L1 INNER JOIN
                         nobat_allnobats AS AllN ON L1.Fldid = AllN.FldidLastLevel
WHERE        (AllN.FldActive = 1) AND (AllN.FldDeleted = 0) AND (L1.FldDeleted = 0) AND (L1.FldActive = 1) AND (length(L1.Fldvoicepath) > 0) ) as ty  where ', Wheree, '   ');
  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
 end if;
      
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPNobat_ReturnActiveItems_ByLevel` */;
DELIMITER ;;
CREATE  PROCEDURE `SPNobat_ReturnActiveItems_ByLevel`(
IN `flLevel` SMALLINT, 
IN `Wheree` VARCHAR(1055) CHARSET utf8mb4)
BEGIN


 

call SPNobat_DeactiveNearNobats();



	  if  length(LTRIM(rtrim(Wheree))) <2  then
	  set  Wheree=' 1=1' ;
		end if ;

   IF flLevel=1 then 
   
     SET @stmt_str = CONCAT(' SELECT *,(@row_number := @row_number + 1)   AS RowNumber
  FROM (
SELECT distinct
         t.Fldid, t.IdCustomer, t.FldIdlevel2_Items, t.Fldvoicepath, t.Fldtitle, t.FldActive, t.FldidDB, t.FldPcName, t.FldSex, t.FldNationalCode, t.FldMobile, t.FldJobTitle, t.FldActive_OV, t.Fldvoicepath_OV, t.FldShortCode, t.FldWebDesc, 
                         t.FldHostName, t.FldInsertTime, t.FldInsertUser, t.FldDeleted		 			  ,t.FldLastCommensCount as commentsCount ,CASE  
      WHEN t.FldLastCommensCount >0  THEN round( t.FldLasRating/t.FldLastCommensCount , 2) 
      ELSE 0
  END as  avrageRating  ,
  CASE  
      WHEN t.FldLastCommensCount >0  THEN round(( t.FldLastSuggest/t.FldLastCommensCount)*100 , 2)  
      ELSE 0
  END as  SuggestCount  , t.fimages,
                      t.furl,
                      
                      t.flast_update
  FROM 
  nobat_level1 t
  INNER JOIN nobat_allnobats
    ON t.Fldid = nobat_allnobats.FldidLastLevel, (SELECT @row_number := 0) AS x
WHERE t.FldDeleted = 0
AND t.FldActive = 1
AND nobat_allnobats.FldActive = 1
AND nobat_allnobats.FldDeleted = 0
AND nobat_allnobats.FldFlag = 0 ) as ty   where ' , Wheree  );

 


  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
   
   end if ;
   
   IF flLevel=2 then 
   
     SET @stmt_str = CONCAT('     SELECT *,(@row_number := @row_number + 1)   AS RowNumber
	 FROM (SELECT DISTINCT t.Fldid, t.FldIdlevel3_Items	,t.FldWebDesc		,  t.Fldvoicepath, t.Fldtitle, t.FldActive, t.FldidDB, t.FldPcName, t.FldDeleted, t.FldHostName, t.FldInsertTime, t.FldInsertUser , t.fimages,
                      t.furl,
                      
                      t.flast_update
FROM            nobat_allnobats INNER JOIN
                         nobat_level1 ON nobat_allnobats.FldidLastLevel = nobat_level1.Fldid INNER JOIN
                         nobat_level2_items AS t ON nobat_level1.FldIdlevel2_Items = t.Fldid, (SELECT @row_number := 0) AS x
WHERE        (t.FldDeleted = 0) AND (t.FldActive = 1) AND  (nobat_allnobats.FldActive = 1) AND (nobat_allnobats.FldDeleted = 0) AND (nobat_level1.FldDeleted = 0) AND (nobat_level1.FldActive = 1) AND 
                         (nobat_allnobats.FldFlag = 0)) AS ty  where  ' , Wheree  );

 


  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
   
   end if ;
   
   IF flLevel=3 then 
   
     SET @stmt_str = CONCAT(' SELECT *,(@row_number := @row_number + 1)   AS RowNumber
FROM
	(
	SELECT DISTINCT
		nobat_level3_items.Fldid,
		nobat_level3_items.FldIdlevel4_Items,
		nobat_level3_items.Fldvoicepath,
		nobat_level3_items.Fldtitle,
		nobat_level3_items.FldActive,
		nobat_level3_items.FldidDB,
		nobat_level3_items.FldPcName,
        	nobat_level3_items.FldWebDesc		 ,
		nobat_level3_items.FldDeleted,
		nobat_level3_items.FldHostName,
		nobat_level3_items.FldInsertTime,
		nobat_level3_items.FldInsertUser 
	FROM
		nobat_level3_items
		INNER JOIN nobat_level2_items ON nobat_level3_items.Fldid = nobat_level2_items.FldIdlevel3_Items
		INNER JOIN nobat_level1 ON nobat_level2_items.Fldid = nobat_level1.FldIdlevel2_Items
		INNER JOIN nobat_allnobats ON nobat_level1.Fldid = nobat_allnobats.FldidLastLevel , (SELECT @row_number := 0) AS x
	WHERE
		( nobat_level2_items.FldDeleted = 0 ) 
		AND ( nobat_level2_items.FldActive = 1 ) 
	 
		AND ( nobat_allnobats.FldActive = 1 ) 
		AND ( nobat_allnobats.FldDeleted = 0 ) 
		AND ( nobat_level1.FldDeleted = 0 ) 
		AND ( nobat_level1.FldActive = 1 ) 
	 
		AND ( nobat_level3_items.FldActive = 1 ) 
		AND ( nobat_level3_items.FldDeleted = 0 ) 
	 
	AND ( nobat_allnobats.FldFlag = 0 ) 
	) AS ty
                         
                        
                         where  ' , Wheree  );

 


  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
   
   end if ;
   IF flLevel=4 then 
   
     SET @stmt_str = CONCAT('   SELECT *, (@row_number := @row_number + 1)   AS RowNumber
   FROM (SELECT DISTINCT 
                         nobat_level4_items.Fldid, nobat_level4_items.Fldvoicepath      	 ,	nobat_level4_items.FldWebDesc	, nobat_level4_items.Fldtitle, nobat_level4_items.FldActive, nobat_level4_items.FldidDB, nobat_level4_items.FldPcName, nobat_level4_items.FldDeleted, 
                         nobat_level4_items.FldHostName, nobat_level4_items.FldInsertTime, nobat_level4_items.FldInsertUser
FROM            nobat_allnobats INNER JOIN
                         nobat_level1 ON nobat_allnobats.FldidLastLevel = nobat_level1.Fldid INNER JOIN
                         nobat_level2_items AS t ON nobat_level1.FldIdlevel2_Items = t.Fldid INNER JOIN
                         nobat_level3_items ON t.FldIdlevel3_Items = nobat_level3_items.Fldid INNER JOIN
                         nobat_level4_items ON nobat_level3_items.FldIdlevel4_Items = nobat_level4_items.Fldid , (SELECT @row_number := 0) AS x
WHERE        (t.FldDeleted = 0) AND (t.FldActive = 1) AND   (nobat_allnobats.FldActive = 1) AND (nobat_allnobats.FldDeleted = 0) AND (nobat_level1.FldDeleted = 0) AND (nobat_level1.FldActive = 1) AND 
                           (nobat_level3_items.FldActive = 1) AND (nobat_level3_items.FldDeleted = 0)  
                         AND (nobat_allnobats.FldFlag = 0) AND  (nobat_level4_items.FldActive = 1) AND (nobat_level4_items.FldDeleted = 0)  ) AS ty 
  where  ' , Wheree  );

 


  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
   
   end if ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPNobat_ReturnAllNobatTime_ByWhere` */;
DELIMITER ;;
CREATE  PROCEDURE `SPNobat_ReturnAllNobatTime_ByWhere`( 
  in  fdate date,
    in  flastleve int,
  in   fidlastlevel int,
    in  Type_ int,
   in   Favetime int
   )
BEGIN
   
  	  call  SPNobat_DeactiveNearNobats();
      IF  Type_ = 0 then
        SELECT
      COUNT(*)
    FROM nobat_allnobats AS t
    WHERE t.flddeleted = 0
    AND t.FldActive = 1
    AND fldflag = 0
    AND t.FldLastLevel =  flastleve
    AND t.FldidLastLevel =  fidlastlevel
    AND FldDate =  fdate;
end if ;


      IF  Type_ = 1 then
        SELECT
      FldTimeStart,
      FldTimeEnd,
      FldTimeStartSTR,
      FldTimeEndSTR,
      Fldid,FldDate
    FROM nobat_allnobats AS t
    WHERE t.flddeleted = 0
    AND t.FldActive = 1
    AND fldflag = 0
    AND t.FldLastLevel =  flastleve
    AND t.FldidLastLevel =  fidlastlevel
    AND FldDate =  fdate
    AND FldTimeStart < 720; 
end if ;
      IF  Type_ = 2 then
         SELECT
      FldTimeStart,
      FldTimeEnd,
      FldTimeStartSTR,
      FldTimeEndSTR,
      Fldid,FldDate
    FROM nobat_allnobats AS t
    WHERE t.flddeleted = 0
    AND t.FldActive = 1
    AND fldflag = 0
    AND t.FldLastLevel =  flastleve
    AND t.FldidLastLevel =  fidlastlevel
    AND FldDate =  fdate
    AND FldTimeStart BETWEEN 720 AND 1080;
end if ;
      IF  Type_ = 3 then
         SELECT
      FldTimeStart,
      FldTimeEnd,
      FldTimeStartSTR,
      FldTimeEndSTR,
      Fldid,FldDate
    FROM nobat_allnobats AS t
    WHERE t.flddeleted = 0
    AND t.FldActive = 1
    AND fldflag = 0
    AND t.FldLastLevel =  flastleve
    AND t.FldidLastLevel =  fidlastlevel
    AND FldDate =  fdate
    AND FldTimeStart BETWEEN 1080 AND 1440;
end if ;

	 IF  Type_ = 7 then
	 
	   SELECT DISTINCT   *  FROM (
        SELECT
     count(*) as 'count' , 'Morning' as tp
    FROM nobat_allnobats AS t
    WHERE t.flddeleted = 0
    AND t.FldActive = 1
    AND fldflag = 0
    AND t.FldLastLevel =  flastleve
    AND t.FldidLastLevel =  fidlastlevel
    AND FldDate =  fdate
    AND FldTimeStart < 720

      union  
         SELECT
       count(*) as 'count' , 'Noon' as tp
    FROM nobat_allnobats AS t
    WHERE t.flddeleted = 0
    AND t.FldActive = 1
    AND fldflag = 0
    AND t.FldLastLevel =  flastleve
    AND t.FldidLastLevel =  fidlastlevel
    AND FldDate =  fdate
    AND FldTimeStart BETWEEN 720 AND 1080

       union
         SELECT
          count(*) as 'count' , 'evening' as tp  
    FROM nobat_allnobats AS t
    WHERE t.flddeleted = 0
    AND t.FldActive = 1
    AND fldflag = 0
    AND t.FldLastLevel =  flastleve
    AND t.FldidLastLevel =  fidlastlevel
    AND FldDate =  fdate
    AND FldTimeStart BETWEEN 1080 AND 1440
	
	 ) as t  ;
	 end if ;

      IF  Type_ = 4 then
         SELECT
      FldTimeStart,
      FldTimeEnd,
      FldTimeStartSTR,
      FldTimeEndSTR,
      Fldid,FldDate
    FROM nobat_allnobats AS t
    WHERE t.flddeleted = 0
    AND t.FldActive = 1
    AND fldflag = 0
    AND t.FldLastLevel =  flastleve
    AND t.FldidLastLevel =  fidlastlevel
    AND FldDate =  fdate;
 end if ;
      IF  Type_ = 5 AND  Favetime >= 0 then
 
	   SELECT DISTINCT   *  FROM (
	   
	 (  SELECT 
        FldTimeStart,  FldTimeEnd,  FldTimeStartSTR,   FldTimeEndSTR,  Fldid ,FldDate
      FROM nobat_allnobats AS t
      WHERE t.flddeleted = 0  AND t.FldActive = 1   AND fldflag = 0  AND t.FldLastLevel = 1  AND t.FldidLastLevel =  fidlastlevel
      AND FldDate =  fdate  AND FldTimeStart <=  Favetime   AND t.FldTimeEnd >=  Favetime   limit 1    )

    union

   (  SELECT   FldTimeStart,   FldTimeEnd,  FldTimeStartSTR,  FldTimeEndSTR,  Fldid  ,FldDate
      FROM nobat_allnobats AS t
      WHERE t.flddeleted = 0
      AND t.FldActive = 1  AND fldflag = 0   AND t.FldLastLevel =  flastleve   AND t.FldidLastLevel =  fidlastlevel
      AND FldDate =  fdate   AND FldTimeStart <=  Favetime   ORDER BY FldTimeStart DESC   limit 2  )
	  
    UNION	  
 
   (  SELECT       FldTimeStart,   FldTimeEnd,   FldTimeStartSTR,   FldTimeEndSTR,   Fldid  ,FldDate
      FROM nobat_allnobats AS t
      WHERE t.flddeleted = 0 AND t.FldActive = 1   AND fldflag = 0   AND t.FldLastLevel =  flastleve
      AND t.FldidLastLevel =  fidlastlevel   AND FldDate =  fdate  AND FldTimeStart >=  Favetime
      ORDER BY FldTimeStart   limit 2)
	  ) as dt   ORDER BY Fldid  ;
	 end if ;
	  IF  Type_ = 6 then
SELECT          FldTimeStart, FldTimeEnd, FldTimeStartSTR, FldTimeEndSTR, Fldid, FldDate
FROM            nobat_allnobats AS t
WHERE        (FldDeleted = 0) AND (FldActive = 1) AND (FldFlag = 0) AND (FldLastLevel =  flastleve) AND (FldidLastLevel =  fidlastlevel) AND (Fldid >  Favetime) limit 1 ;
end if;
     
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPNobat_ReturnIdsLevel1ByIdLevel2` */;
DELIMITER ;;
CREATE  PROCEDURE `SPNobat_ReturnIdsLevel1ByIdLevel2`(in Param1 int )
BEGIN
	 declare  EndDate   datetime ;
	 declare End_day_interval int ;
  set End_day_interval   = (select   nobat_settings.fldval from nobat_settings where fldkey ='End_day_interval'  limit 1);

     set   EndDate = DATE_ADD( now(), INTERVAL End_day_interval  DAY)    ;
      
       SELECT *,
 (@row_number := @row_number + 1)   AS RowNumber  FROM (
SELECT distinct
   t.Fldid, t.IdCustomer, t.FldIdlevel2_Items, t.Fldvoicepath, t.Fldtitle, t.FldActive, t.FldidDB, t.FldPcName, t.FldSex, t.FldNationalCode, t.FldMobile, t.FldJobTitle, t.FldActive_OV, t.Fldvoicepath_OV, t.FldShortCode, t.FldWebDesc, 
                         t.FldHostName, t.FldInsertTime, t.FldInsertUser, t.FldDeleted, t.fimages,
                      t.furl,
                      t.fhtml,
                      t.flast_update

						  ,t.FldLastCommensCount as commentsCount ,  CASE
    WHEN t.FldLastCommensCount >0  THEN round( t.FldLasRating/t.FldLastCommensCount , 2)  
    ELSE 0
END as avrageRating  ,  CASE
    WHEN t.FldLastCommensCount >0  THEN round(( t.FldLastSuggest/t.FldLastCommensCount)*100 , 2)  
    ELSE 0
END  as SuggestCount
FROM 
  nobat_level1 t
  INNER JOIN nobat_allnobats
    ON t.Fldid = nobat_allnobats.FldidLastLevel , (SELECT @row_number := 0) AS x 
WHERE t.FldDeleted = 0
AND t.FldActive = 1
 
AND nobat_allnobats.FldActive = 1
AND nobat_allnobats.FldDeleted = 0
AND nobat_allnobats.FldFlag = 0
AND nobat_allnobats.FldDate<= EndDate) as ty   WHERE ty.Fldid IN (SELECT DISTINCT nobat_level1.Fldid
FROM            nobat_allnobats INNER JOIN
                         nobat_level1 ON nobat_allnobats.FldidLastLevel = nobat_level1.Fldid INNER JOIN
                         nobat_level2_items ON nobat_level1.FldIdlevel2_Items = nobat_level2_items.Fldid
WHERE        (nobat_level2_items.Fldid = Param1) AND (nobat_level2_items.FldDeleted = 0) AND (nobat_level2_items.FldActive = 1)   AND  
                         (nobat_level1.FldDeleted = 0) AND (nobat_level1.FldActive = 1)   AND (nobat_allnobats.FldFlag = 0) AND (nobat_allnobats.FldDeleted = 0) AND (nobat_allnobats.FldActive = 1) AND 
                         (nobat_allnobats.FldDate <= EndDate));


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPNobat_ReturnIdsLevel2ByIdLevel3` */;
DELIMITER ;;
CREATE  PROCEDURE `SPNobat_ReturnIdsLevel2ByIdLevel3`(in Param1 int )
BEGIN
	 declare  EndDate   datetime ;
	 declare End_day_interval int ;
  set End_day_interval   = (select   nobat_settings.fldval from nobat_settings where fldkey ='End_day_interval'  limit 1);

     set   EndDate = DATE_ADD( now(), INTERVAL End_day_interval  DAY)    ;
      
  SELECT *,
 (@row_number := @row_number + 1)   AS RowNumber  FROM (SELECT DISTINCT t.*
FROM            nobat_allnobats INNER JOIN
                         nobat_level1 ON nobat_allnobats.FldidLastLevel = nobat_level1.Fldid INNER JOIN
                         nobat_level2_items AS t ON nobat_level1.FldIdlevel2_Items = t.Fldid   , (SELECT @row_number := 0) AS x
WHERE        (t.FldDeleted = 0) AND (t.FldActive = 1)  AND (nobat_allnobats.FldActive = 1) AND (nobat_allnobats.FldDeleted = 0) AND (nobat_allnobats.FldFlag = 0) AND (nobat_allnobats.FldDate <= EndDate) 
                         AND (nobat_level1.FldDeleted = 0) AND (nobat_level1.FldActive = 1)  
) AS ty
  WHERE ty.Fldid IN (SELECT DISTINCT nobat_level2_items.Fldid
 FROM nobat_level3_items
  INNER JOIN nobat_level2_items
    ON nobat_level3_items.Fldid = nobat_level2_items.FldIdlevel3_Items
  INNER JOIN nobat_level1
    ON nobat_level2_items.Fldid = nobat_level1.FldIdlevel2_Items
  INNER JOIN nobat_allnobats
    ON nobat_level1.Fldid = nobat_allnobats.FldidLastLevel
WHERE        (nobat_level2_items.FldDeleted = 0) AND (nobat_level2_items.FldActive = 1)  AND   (nobat_level1.FldDeleted = 0) AND 
                         (nobat_level1.FldActive = 1)   AND (nobat_allnobats.FldFlag = 0) AND (nobat_allnobats.FldDeleted = 0) AND (nobat_allnobats.FldActive = 1) AND (nobat_allnobats.FldDate <= EndDate) 
                         AND   (nobat_level3_items.FldDeleted = 0) AND (nobat_level3_items.FldActive = 1)   AND (nobat_level3_items.Fldid = Param1));
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPNobat_ReturnIdsLevel3ByIdLevel4` */;
DELIMITER ;;
CREATE  PROCEDURE `SPNobat_ReturnIdsLevel3ByIdLevel4`(in Param1 int )
BEGIN
	 declare  EndDate   datetime ;
	 declare End_day_interval int ;
  set End_day_interval   = (select   nobat_settings.fldval from nobat_settings where fldkey ='End_day_interval'  limit 1);

     set   EndDate = DATE_ADD( now(), INTERVAL End_day_interval  DAY)    ;
      
        SELECT *,
 (@row_number := @row_number + 1)   AS RowNumber 
	  FROM (SELECT DISTINCT 
                         nobat_level3_items.*
FROM            nobat_allnobats INNER JOIN
                         nobat_level1 ON nobat_allnobats.FldidLastLevel = nobat_level1.Fldid INNER JOIN
                         nobat_level2_items AS t ON nobat_level1.FldIdlevel2_Items = t.Fldid INNER JOIN
                         nobat_level3_items ON t.FldIdlevel3_Items = nobat_level3_items.Fldid INNER JOIN
                         nobat_level4_items ON nobat_level3_items.FldIdlevel4_Items = nobat_level4_items.Fldid , (SELECT @row_number := 0) AS x 
WHERE        (t.FldDeleted = 0) AND (t.FldActive = 1)  AND (nobat_allnobats.FldActive = 1) AND (nobat_allnobats.FldDeleted = 0) AND (nobat_allnobats.FldFlag = 0) AND (nobat_allnobats.FldDate <= EndDate) 
                         AND (nobat_level1.FldDeleted = 0) AND (nobat_level1.FldActive = 1) AND   (nobat_level3_items.FldActive = 1) AND 
                         (nobat_level3_items.FldDeleted = 0) AND   (nobat_level4_items.FldActive = 1) AND (nobat_level4_items.FldDeleted = 0)   
                         ) AS ty 
  WHERE ty.Fldid IN (SELECT DISTINCT nobat_level3_items.Fldid
FROM            nobat_allnobats INNER JOIN
                         nobat_level1 ON nobat_allnobats.FldidLastLevel = nobat_level1.Fldid INNER JOIN
                         nobat_level2_items ON nobat_level1.FldIdlevel2_Items = nobat_level2_items.Fldid INNER JOIN
                         nobat_level3_items ON nobat_level2_items.FldIdlevel3_Items = nobat_level3_items.Fldid INNER JOIN
                         nobat_level4_items ON nobat_level3_items.FldIdlevel4_Items = nobat_level4_items.Fldid
WHERE        (nobat_level2_items.FldDeleted = 0) AND (nobat_level2_items.FldActive = 1)  AND   (nobat_level1.FldDeleted = 0) AND 
                         (nobat_level1.FldActive = 1) AND  (nobat_allnobats.FldFlag = 0) AND (nobat_allnobats.FldDeleted = 0) AND (nobat_allnobats.FldActive = 1) AND (nobat_allnobats.FldDate <= EndDate) 
                           AND (nobat_level3_items.FldDeleted = 0) AND (nobat_level3_items.FldActive = 1)   AND 
                         (nobat_level4_items.FldActive = 1) AND (nobat_level4_items.FldDeleted = 0)   AND (nobat_level4_items.Fldid = Param1));

 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `Spnobat_settingsGet` */;
DELIMITER ;;
CREATE  PROCEDURE `Spnobat_settingsGet`()
BEGIN
Select *    From  nobat_settings    where  flddeleted = 0 ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPorders_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPorders_AddUpdate`(
IN `i_d` BIGINT, 
IN `o_rdercode` VARCHAR(255) CHARSET utf8mb4, 
IN `f_idproduct` BIGINT, 
IN `f_idcustomer` BIGINT, 
IN `f_status` VARCHAR(255) CHARSET utf8mb4, 
 IN `f_desc` VARCHAR(955) CHARSET utf8mb4, 
 IN `f_host` VARCHAR(955) CHARSET utf8mb4 
)
BEGIN
 
declare lastcronmin integer ;
  IF i_d > 0 THEN
      
   
       UPDATE  orders
            SET 
               fidproduct  = f_idproduct ,   fidcustomer =f_idcustomer  ,   fstatus = f_status ,   fdesc = f_desc ,  fhost  = f_host 
                
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
    set lastcronmin = (select max(cronmin) +1  from  orders );
              
              INSERT  INTO  orders  ( ordercode, fidproduct ,fidcustomer  , fstatus , fdesc ,     fhost   , cronmin          )
               VALUES ( o_rdercode,  f_idproduct ,f_idcustomer  , f_status , f_desc ,     f_host  , lastcronmin ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPpages_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPpages_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_name` VARCHAR(255) CHARSET utf8mb4, 
 
 
 
IN `f_html` LONGTEXT CHARSET utf8mb4,
 IN `f_layout` LONGTEXT CHARSET utf8mb4,
IN `f_author` VARCHAR(255) CHARSET utf8mb4, 
IN `f_type` VARCHAR(255) CHARSET utf8mb4, 
 IN `f_com_type` VARCHAR(255) CHARSET utf8mb4, 
 
IN `f_active` TINYINT,
IN `no_html` TINYINT ,
IN `f_canedit` VARCHAR(255) CHARSET utf8mb4 ,
IN `f_desc` VARCHAR(955) CHARSET utf8mb4,
IN `f_ctitle` VARCHAR(955) CHARSET utf8mb4,
  IN `f_canonical` VARCHAR(1245) CHARSET utf8mb4 ,
IN `f_picAlt` VARCHAR(655) CHARSET utf8mb4 ,
IN `f_keys` VARCHAR(1255) CHARSET utf8mb4 ,
IN `f_image` VARCHAR(1900) CHARSET utf8mb4, 
IN `f_fixtype` VARCHAR(955) CHARSET utf8mb4
)
BEGIN

  declare f_last_update datetime ;
 set f_last_update =   now() ;

   IF i_d > 0 THEN
      
   if   no_html > 0  then
			UPDATE pages  SET 
              fimage=f_image ,  fkeys =f_keys ,   fcanonical=f_canonical ,fpicAlt=f_picAlt , fname=f_name , factive=f_active,    flast_update=f_last_update , flayout=f_layout , fctitle=f_ctitle ,
               fauthor=f_author ,ftype=f_type   , fcom_type=f_com_type , fcanedit=f_canedit , fdesc=f_desc, ffixtype=f_fixtype
			WHERE id = i_d and id > 10  ;

         else
         	 UPDATE pages  SET 
               fimage=f_image ,  fkeys =f_keys ,   fcanonical=f_canonical ,fpicAlt=f_picAlt ,   fname=f_name , factive=f_active,  fhtml=f_html, flast_update=f_last_update , flayout=f_layout , fctitle = f_ctitle ,
               fauthor=f_author ,ftype=f_type    , fcom_type=f_com_type ,    fcanedit=f_canedit, fdesc=f_desc, ffixtype=f_fixtype
			WHERE id = i_d   and id > 10  ;
         
         end if;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  pages (fhtml,fauthor,ftype,factive,fname,flast_update ,fcom_type ,fcanedit  , fdesc ,ffixtype ,fctitle ,fpicAlt , fcanonical , fkeys ,fimage
             )
               VALUES (f_html,f_author,f_type,f_active,f_name,f_last_update ,f_com_type ,f_canedit , f_desc ,f_fixtype , f_ctitle , f_picAlt  , f_canonical ,f_keys , f_image
                   ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPPaymethods_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPPaymethods_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_name` VARCHAR(255) CHARSET utf8mb4, 
 
IN `f_type` VARCHAR(255) CHARSET utf8mb4, 
IN `f_desc` LONGTEXT CHARSET utf8mb4,
IN `f_extra` LONGTEXT CHARSET utf8mb4,
IN `f_active_coupon` TINYINT ,
IN `f_active_wallet` TINYINT ,
IN `f_active_Virtual` TINYINT ,
IN `f_only_wallet` TINYINT , 
IN `f_active_Amazing` TINYINT ,
IN `f_min_Value` bigint ,
IN `f_max_Value` bigint ,
IN `f_active` TINYINT
)
BEGIN

 

  IF i_d > 0 THEN
       UPDATE billpaymethods
            SET 
             fmax_Value = f_max_Value , fmin_Value =f_min_Value ,   fname=f_name , factive=f_active,   fdesc=f_desc   , fextra=f_extra  , factive_coupon=f_active_coupon , factive_wallet= f_active_wallet
                , factive_Virtual=f_active_Virtual ,fonly_wallet=f_only_wallet , factive_Amazing = f_active_Amazing
                , fupdatetime = now()
                  
         WHERE id = i_d ;
		 select i_d  as id;
  END IF;
  IF i_d = 0 THEN
              INSERT  INTO  billpaymethods (fmax_Value, fmin_Value , fname  , factive ,   fdesc    , fextra ,ftype  , factive_coupon , factive_wallet,factive_Virtual , fonly_wallet,factive_Amazing)
               VALUES ( f_max_Value ,f_min_Value , f_name , f_active,      f_desc   , f_extra ,f_type ,f_active_coupon ,f_active_wallet,f_active_Virtual  ,f_only_wallet,f_active_Amazing) ;
    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPpostscatag_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPpostscatag_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_name` VARCHAR(255) CHARSET utf8mb4, 
 
IN `f_images` LONGTEXT CHARSET utf8mb4,
IN `f_content` LONGTEXT CHARSET utf8mb4,
IN `f_layout` LONGTEXT CHARSET utf8mb4,
IN `f_html` LONGTEXT CHARSET utf8mb4,
IN `f_comissionP` int(5),
IN `f_author` VARCHAR(255) CHARSET utf8mb4, 
IN `f_type` VARCHAR(255) CHARSET utf8mb4,
 IN `f_ctitle` VARCHAR(255) CHARSET utf8mb4,
 IN `f_products_msg` VARCHAR(1255) CHARSET utf8mb4, 
 IN `f_products_msg2` VARCHAR(1255) CHARSET utf8mb4, 
 IN `f_parent` BIGINT, 
IN `f_keys` VARCHAR(1255) CHARSET utf8mb4,
  IN `f_canonical` VARCHAR(1245) CHARSET utf8mb4 ,
IN `f_picAlt` VARCHAR(655) CHARSET utf8mb4 ,
 IN `f_isLink`tinyint(3), 
 
  IN `f_show_inMainCatags`tinyint(3), 
   IN `f_show_inRmnue`tinyint(3), 
    IN `f_show_inMegamenue`tinyint(3), 
     IN `f_show_inElemans`tinyint(3), 
     
     
IN `f_active` TINYINT
)
BEGIN

  declare f_last_update datetime ;
 set f_last_update =   now() ;

  IF i_d > 0 THEN
      
   
       UPDATE postscatag
            SET 
           fcanonical=f_canonical ,fpicAlt=f_picAlt , fshow_inMainCatags=f_show_inMainCatags ,  fshow_inRmnue =f_show_inRmnue  , fshow_inMegamenue = f_show_inMegamenue ,fshow_inElemans  = f_show_inElemans ,  
                fname=f_name , factive=f_active,fimages=f_images  ,  flast_update=f_last_update , fproducts_msg=f_products_msg, fproducts_msg2=f_products_msg2,
                fcontent=f_content ,fauthor=f_author   ,fkeys=f_keys ,fparent=f_parent ,flayout=f_layout , fctitle=f_ctitle
                , fcomissionP= f_comissionP , fisLink= f_isLink
                
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  postscatag (
              fshow_inMainCatags , fshow_inRmnue  , fshow_inMegamenue , fshow_inElemans ,  
              fisLink,fcomissionP,fcontent,fhtml,fauthor,ftype,fkeys,factive,fimages,fname,flast_update,fparent,flayout ,
              fctitle,fproducts_msg,fproducts_msg2 ,fpicAlt , fcanonical
             )
               VALUES (
               f_show_inMainCatags , f_show_inRmnue  , f_show_inMegamenue , f_show_inElemans ,  
               f_isLink,f_comissionP, f_content,f_html,f_author,f_type,f_keys,f_active,f_images,f_name,f_last_update,f_parent,f_layout ,
               f_ctitle,f_products_msg,f_products_msg2 , f_picAlt  , f_canonical
                   ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPpostscatag_UpdateHTML` */;
DELIMITER ;;
CREATE  PROCEDURE `SPpostscatag_UpdateHTML`(
IN `i_d` BIGINT, 
IN `f_html` LONGTEXT CHARSET utf8mb4
)
BEGIN

  declare f_last_update datetime ;
 set f_last_update =   now() ;

  IF i_d > 0 THEN
      
   
       UPDATE postscatag
            SET 
                fhtml=f_html   , flast_update=f_last_update    WHERE id = i_d ; 
                select i_d  as id;
   
  END IF;

  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPPosts_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPPosts_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_idwebuser` BIGINT, 
IN `f_name` VARCHAR(255) CHARSET utf8mb4, 
IN `f_imagescount` INT, 
IN `f_images` LONGTEXT CHARSET utf8mb4,
IN `f_content` LONGTEXT CHARSET utf8mb4,
IN `f_layout` LONGTEXT CHARSET utf8mb4,
IN `f_html` LONGTEXT CHARSET utf8mb4,
IN `f_author` VARCHAR(255) CHARSET utf8mb4, 
IN `f_ctitle` VARCHAR(500) CHARSET utf8mb4, 
IN `f_type` VARCHAR(255) CHARSET utf8mb4, 
IN `f_idCateg` INT,
 
IN `f_keys` VARCHAR(1255) CHARSET utf8mb4, 
 IN `f_canonical` VARCHAR(1245) CHARSET utf8mb4 ,
IN `f_picAlt` VARCHAR(655) CHARSET utf8mb4 ,
IN `f_active` TINYINT
)
BEGIN

  declare f_last_update datetime ;
 set f_last_update =   now() ;

  IF i_d > 0 THEN
      
   
       UPDATE posts
            SET 
              fcanonical=f_canonical ,fpicAlt=f_picAlt ,   fname=f_name , factive=f_active,fimages=f_images  , fidCateg=f_idCateg   , flast_update=f_last_update , fctitle=f_ctitle ,
                fimagescount=f_imagescount ,fcontent=f_content ,fauthor=f_author ,ftype=f_type ,fkeys=f_keys ,flayout=f_layout
                
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  posts (fctitle,fimagescount,fcontent,fhtml,fauthor,ftype,fidCateg,fkeys,factive,fimages,fname,flast_update,flayout,fpicAlt , fcanonical , fidwebuser
             )
               VALUES (f_ctitle,f_imagescount,f_content,f_html,f_author,f_type,f_idCateg,f_keys,f_active,f_images,f_name,f_last_update ,f_layout , f_picAlt  , f_canonical , f_idwebuser
                   ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPPosts_UpdateHTML` */;
DELIMITER ;;
CREATE  PROCEDURE `SPPosts_UpdateHTML`(
IN `i_d` BIGINT, 
IN `f_html` LONGTEXT CHARSET utf8mb4,
IN `f_extra` LONGTEXT CHARSET utf8mb4
)
BEGIN

  declare f_last_update datetime ;
 set f_last_update =   now() ;

  IF i_d > 0 THEN
      
   
       UPDATE posts
            SET 
                fhtml=f_html   , flast_update=f_last_update  , fextra=f_extra   WHERE id = i_d ; 
                select i_d  as id;
   
  END IF;

  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPPost_AllComments` */;
DELIMITER ;;
CREATE  PROCEDURE `SPPost_AllComments`(IN `Wheree` VARCHAR(1055) CHARSET utf8mb4, IN `limitt` INT)
BEGIN
   
 	  	  if  length(LTRIM(rtrim(Wheree)))<2  then
	  set  Wheree=' 1=1' ;
      end if;
      
        SET @stmt_str = CONCAT('  

(select c.* , p.fname as pname from post_comments as c inner join posts as p on p.id = c.fidpost where p.fdeleted=0 and c.fdeleted=0   ', Wheree, '
ORDER BY  c.id   )
union all 
(select c.* , p.fname as pname from post_comments as c inner join products as p on p.id = c.fidpost where p.fdeleted=0 and c.fdeleted=0   ', Wheree, '
ORDER BY  c.id )


 limit ', limitt, ' ');
  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
   
     
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPpost_comments_Aprov` */;
DELIMITER ;;
CREATE  PROCEDURE `SPpost_comments_Aprov`(IN t_ype  int , IN idComment  int   )
BEGIN
   
 
 
 
        update post_comments set fstatus = t_ype where  id=idComment and fdeleted=0  ;

  



     
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPProductsAtribute_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPProductsAtribute_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_key` VARCHAR(255) CHARSET utf8mb4, 
IN `f_idCateg` int,  
IN `f_type` VARCHAR(255) CHARSET utf8mb4, 
IN `f_active` TINYINT
)
BEGIN

declare co integer ;
       set   co  =  (select count(id ) as id   from product_allattribute where fkey = f_key and  fidCateg = f_idCateg) ;
       
       if  co > 0  then
        set i_d =   (select  id       from product_allattribute where fkey = f_key  and  fidCateg = f_idCateg) ;
       end if ;


  IF i_d > 0 THEN
      
   
       UPDATE product_allattribute
            SET 
                fkey=f_key , factive=f_active    ,ftype=f_type  , fidCateg = f_idCateg
                
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  product_allattribute (fkey,  ftype, factive  ,fidCateg   )
               VALUES (f_key, f_type, f_active    ,    f_idCateg      ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPProducts_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPProducts_AddUpdate`(
 IN `i_d` BIGINT, 
IN `f_name` VARCHAR(955) CHARSET utf8mb4,
 IN `f_latiName` VARCHAR(955) CHARSET utf8mb4, 
IN `f_desc` LONGTEXT CHARSET utf8mb4, 
IN `f_info1` LONGTEXT CHARSET utf8mb4, 
IN `f_info2` LONGTEXT CHARSET utf8mb4, 
IN `f_html` LONGTEXT CHARSET utf8mb4,
IN `f_images` LONGTEXT CHARSET utf8mb4,
IN `f_active` TINYINT,
 IN `f_comissionP` TINYINT, 
IN `f_idCateg` INT,
IN `f_keys` VARCHAR(1255) CHARSET utf8mb4 ,
IN `f_is_down` INT,
IN `f_is_virtual` INT,
IN `f_sku` VARCHAR(255) CHARSET utf8mb4 ,
IN `f_is_soon` INT,
IN `f_layout` LONGTEXT CHARSET utf8mb4,
IN `f_isService` INT,
IN `f_id_brand` INT,
IN `f_idCarrierId` longtext  CHARSET utf8mb4,
IN `f_tax` INT,
IN `f_tax_shenase` VARCHAR(145) CHARSET utf8mb4 ,
IN `f_taxUnit` VARCHAR(145) CHARSET utf8mb4 ,
IN `f_type` varchar(200) CHARSET utf8mb4, 
IN `f_weight` decimal(10,2),
IN `f_length` decimal(10,2),
IN `f_width` decimal(10,2),
IN `f_height` decimal(10,2),
IN `f_creatorInfo` longtext CHARSET utf8mb4 ,
IN `f_canonical` VARCHAR(1245) CHARSET utf8mb4 ,
IN `f_picAlt` VARCHAR(655) CHARSET utf8mb4 ,
IN `f_zarib` INT,
IN `f_ctitle` VARCHAR(555) CHARSET utf8mb4 
)
BEGIN

   IF i_d > 0 THEN
  
  
   
                    
                    
       
       UPDATE products
            SET 
           fidCarrierId = f_idCarrierId ,  fpicAlt=f_picAlt ,   fzarib=f_zarib ,   fname=f_name ,  fidCateg=f_idCateg  , fcomissionP=f_comissionP   , fcanonical=f_canonical  
                ,fdesc=f_desc, fimages=f_images ,factive=f_active, 
                  fkeys= f_keys ,
                fis_down=f_is_down ,fis_virtual =f_is_virtual , fis_soon=f_is_soon , 
flayout=f_layout , fctitle=f_ctitle ,
                   ftax = f_tax ,ftaxUnit=f_taxUnit,ftax_shenase=f_tax_shenase
               ,finfo1 = f_info1 ,finfo2 = f_info2,
                  fid_brand = f_id_brand   ,fweight = f_weight ,flength = f_length   ,fwidth = f_width  ,   fheight = f_height 
              ,ftype=f_type , fhtml=f_html  
                 , fsku = f_sku , flatiName = f_latiName, fisService = f_isService 
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  products (fidCarrierId ,fzarib , fpicAlt,  fcanonical ,fcomissionP ,
            fname , fdesc,fhtml,fimages,factive, 
             fidCateg ,fkeys ,fis_down ,fis_virtual ,fis_soon  ,
             fweight ,flength,fwidth,fheight ,fsku, fid_brand ,finfo1,finfo2,flatiName , fisService  
            ,fcomments  ,fsuggests ,  flayout ,fctitle ,  ftax,ftaxUnit,ftax_shenase,ftype , fcreatorInfo)
               VALUES (f_idCarrierId ,f_zarib ,f_picAlt , f_canonical ,f_comissionP ,
                   f_name , f_desc,f_html,f_images, f_active, 
                   f_idCateg , f_keys,f_is_down ,f_is_virtual ,f_is_soon , 
                   f_weight ,f_length,f_width,f_height, f_sku, f_id_brand ,f_info1,f_info2 ,f_latiName ,  f_isService 
                    ,  0 ,0 ,   f_layout , f_ctitle ,  f_tax,f_taxUnit,f_tax_shenase,f_type, f_creatorInfo) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPProducts_UpdateHTML` */;
DELIMITER ;;
CREATE  PROCEDURE `SPProducts_UpdateHTML`(
IN `i_d` BIGINT, 
IN `f_html` LONGTEXT CHARSET utf8mb4
)
BEGIN

  declare f_last_update datetime ;
 set f_last_update =   now() ;

  IF i_d > 0 THEN
      
   
       UPDATE products
            SET 
                fhtml=f_html        WHERE id = i_d ; 
                select i_d  as id;
   
  END IF;

  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPProducts_Varients_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPProducts_Varients_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_price` BIGINT, 
IN `f_ronding` BIGINT, 
IN `f_down_extra` LONGTEXT CHARSET utf8mb4,
IN `f_active` TINYINT, 
IN `f_off` INT, 
IN `a_mazing_foff` INT, 
IN `f_is_Single_sale` INT,
IN `f_is_freeShip` INT,
IN `f_Manage_stock_quantity` INT,
IN `f_stock_quantity` INT,
IN `f_low_stock_amount` INT,
IN `f_idproduct` bigint,
IN `f_idstore` bigint,
IN `f_canShappingByMainStore` tinyint,
IN `f_canShappingBySeller` tinyint,
IN `f_daystoSendMainStore` tinyint,
IN `f_daystoSendCustomerByMainStore` tinyint,
IN `f_daystoSendCustomerBySeller` tinyint,
IN `f_inMainStoreStock` tinyint,
 IN `f_max_sell_quantity` INT,
IN `f_is_amazing` INT,
IN `f_is_soon` INT,
IN `f_weight` decimal(10,2),
IN `f_length` decimal(10,2),
IN `f_width` decimal(10,2),
IN `f_height` decimal(10,2),
IN `f_type` varchar(200) CHARSET utf8mb4, 
IN `f_garantidesc` varchar(2245) CHARSET utf8mb4,
IN `f_customBuyBoxdesc` varchar(2245) CHARSET utf8mb4, 
IN `famazing_startDate`  VARCHAR(255) CHARSET utf8mb4 ,
in famazing_endDate  VARCHAR(255) CHARSET utf8mb4 ,
in famazing_startTime  VARCHAR(255) CHARSET utf8mb4 ,
in famazing_endTime   VARCHAR(255) CHARSET utf8mb4 
)
BEGIN
  declare Oldfis_amazing int ;
  declare  Oldfprice int ;

    declare Oldamazing_foff int ;
  declare  Oldoff int ;
    declare Oldfactive int ;
  declare  Oldfronding int ;
   
  declare  f_date_update_price datetime ;
  
  


  IF i_d > 0 THEN
  
  
   
   
         set   Oldfis_amazing = ( select  fis_amazing  from  products_varients where id = i_d  );
         set   Oldamazing_foff = ( select  amazing_foff  from  products_varients where id = i_d  );
         set  Oldfprice = ( select  fprice  from  products_varients where id = i_d  );
         set  Oldoff = ( select  foff  from  products_varients where id = i_d  );
         set   Oldfronding = ( select  fronding  from  products_varients where id = i_d  );
         set   Oldfactive = ( select  factive  from  products_varients where id = i_d  );
     
       if Oldfis_amazing <> f_is_amazing  or  Oldamazing_foff <> a_mazing_foff  or Oldfprice <> f_price  or  Oldoff <> f_off  or  Oldfronding <> f_ronding  or  Oldfactive <> f_active then
		 insert into products_varients_pricechange (fidproduct_variant,fidstore,fprice,foff,factive,fronding,fis_amazing, amazing_foff)
         values (i_d , f_idstore,f_price , f_off ,f_active , f_ronding , f_is_amazing ,a_mazing_foff  ) ;
       END IF; 
       
	   if     Oldfprice <> f_price      then  
		   set f_date_update_price =   now() ;
         else
          set f_date_update_price =   ( select  fdate_update_price  from  products_varients where id = i_d  );
       END IF; 
       
       
       UPDATE products_varients
            SET 
                 fprice=f_price , fis_soon = f_is_soon ,  fcustomBuyBoxdesc=f_customBuyBoxdesc  , fronding=f_ronding 
                 , fdaystoSendCustomerByMainStore=f_daystoSendCustomerByMainStore  
                 , fdaystoSendCustomerBySeller=f_daystoSendCustomerBySeller 
                 , fgarantidesc=f_garantidesc 
                 , finMainStoreStock=f_inMainStoreStock 
                   ,factive=f_active,foff=f_off,
                     fweight = f_weight ,flength = f_length   ,fwidth = f_width  ,   fheight = f_height ,
                fdate_update_price= f_date_update_price ,  
                  fcanShappingBySeller=f_canShappingBySeller,fcanShappingByMainStore=f_canShappingByMainStore,
                  fdaystoSendMainStore=f_daystoSendMainStore,
                fis_amazing= f_is_amazing, ftype=f_type , amazing_startDate=famazing_startDate ,
                amazing_endDate=famazing_endDate , amazing_startTime=famazing_startTime 
                , fManage_stock_quantity= f_Manage_stock_quantity,  fis_Single_sale= f_is_Single_sale,amazing_endTime = famazing_endTime
                ,fstock_quantity = f_stock_quantity 
                ,flow_stock_amount = f_low_stock_amount
               , fis_freeShip=f_is_freeShip ,fidproduct=f_idproduct 
                 ,fidstore = f_idstore  ,   fmax_sell_quantity = f_max_sell_quantity,
                    fdown_extra = f_down_extra , amazing_foff=a_mazing_foff , fdeleted = 0 
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  products_varients (
          fronding,  fprice, factive,foff, fis_soon ,
            fdate_update_price ,  fis_amazing ,ftype,amazing_startDate ,
           amazing_endDate ,amazing_startTime,amazing_endTime , fis_Single_sale,fManage_stock_quantity,fstock_quantity,flow_stock_amount ,
           fmax_sell_quantity, fidproduct, fdown_extra , fsellcount ,
         amazing_foff ,  fis_freeShip,fidstore ,fcanShappingBySeller,fcanShappingByMainStore,fdaystoSendMainStore ,
         fdaystoSendCustomerByMainStore , fgarantidesc , fcustomBuyBoxdesc , finMainStoreStock , fdaystoSendCustomerBySeller 
         ,  fweight ,flength,fwidth,fheight )
               VALUES (
                  f_ronding,  f_price,  
                   f_active,f_off ,  f_is_soon ,  now()    ,  f_is_amazing ,
                   f_type,   famazing_startDate  ,     famazing_endDate 
                   ,famazing_startTime,famazing_endTime ,f_is_Single_sale , f_Manage_stock_quantity,f_stock_quantity,f_low_stock_amount ,
                    f_max_sell_quantity, f_idproduct,   
                     f_down_extra ,0,  a_mazing_foff  , f_is_freeShip ,f_idstore ,  f_canShappingBySeller,f_canShappingByMainStore,
                     f_daystoSendMainStore,f_daystoSendCustomerByMainStore , f_garantidesc , f_customBuyBoxdesc, f_inMainStoreStock , f_daystoSendCustomerBySeller 
                     , f_weight ,f_length,f_width,f_height) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPproduct_attribute_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPproduct_attribute_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_value` VARCHAR(9955) CHARSET utf8mb4, 
IN `f_view_mode` VARCHAR(9955) CHARSET utf8mb4, 
 IN `f_enable_variation` tinyint, 
  IN `f_attribute_visibility` tinyint, 
IN `f_idatribute` bigint, 
IN `f_order` VARCHAR(40) CHARSET utf8mb4, 
IN `f_idproduct` bigint
)
BEGIN

    
	   declare co integer ;
       set   co  =  (select count(id ) as id   from product_attribute where idproduct = f_idproduct and idatribute=f_idatribute  ) ;
       
       if  co > 0  then
        set i_d =   (select  id       from product_attribute where idproduct = f_idproduct and idatribute=f_idatribute ) ;
       end if ; 
 

  IF i_d > 0 THEN
      
   
            UPDATE product_attribute
            SET 
                fview_mode=f_view_mode ,  fvalue=f_value   , fattribute_visibility=f_attribute_visibility , fenable_variation=f_enable_variation
             ,   forder = f_order
            WHERE id = i_d ;
         
                   
         

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
            
              INSERT  INTO  product_attribute (fvalue,  idproduct, idatribute  ,fattribute_visibility  , fenable_variation , fview_mode , forder)
               VALUES (f_value, f_idproduct, f_idatribute   ,f_attribute_visibility,       f_enable_variation  ,f_view_mode ,f_order  ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPproduct_brand_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPproduct_brand_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_name` VARCHAR(900) CHARSET utf8mb4, 
 IN `f_latinName` VARCHAR(900) CHARSET utf8mb4,  
 IN `f_images` VARCHAR(1900) CHARSET utf8mb4, 
 IN `f_layout`longtext CHARSET utf8mb4, 
  IN `f_idcatags`mediumtext CHARSET utf8mb4,  
  IN `f_ctitle` VARCHAR(555) CHARSET utf8mb4,   
  IN `f_desc` VARCHAR(955) CHARSET utf8mb4,  
  IN `f_canonical` VARCHAR(1245) CHARSET utf8mb4 ,
IN `f_picAlt` VARCHAR(655) CHARSET utf8mb4 ,
IN `f_keys` VARCHAR(1255) CHARSET utf8mb4 ,
IN `f_active` TINYINT
)
BEGIN

declare co integer ;
       set   co  =  (select count(id ) as id   from product_brand where fname = f_name  ) ;
       
       if  co > 0  then
        set i_d =   (select  id       from product_brand where fname = f_name  limit 1 ) ;
       end if ;


  IF i_d > 0 THEN
      
   
       UPDATE product_brand
            SET 
           fkeys =f_keys ,   fcanonical=f_canonical ,fpicAlt=f_picAlt ,  fname=f_name , factive=f_active , fctitle= f_ctitle , fdesc=f_desc , flatinName=f_latinName  , fimages=f_images , flayout =f_layout ,fidcatags=f_idcatags
               ,  flast_update = now()
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  product_brand (fname,   factive  ,flatinName ,fimages ,flayout , fidcatags , fctitle ,fdesc ,fpicAlt , fcanonical , fkeys)
               VALUES (f_name,  f_active       ,f_latinName  ,f_images   ,f_layout , f_idcatags , f_ctitle,f_desc , f_picAlt  , f_canonical ,f_keys) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPProduct_CATAGS_LEVELS` */;
DELIMITER ;;
CREATE  PROCEDURE `SPProduct_CATAGS_LEVELS`(  IN id_gatag  int    )
BEGIN
   
 
 select  fname , id , furl, fisLink  from 
 (select fname , id , furl , fisLink from postscatag WHERE id = id_gatag  and fdeleted = 0 
 union all
 select      parent_name  , parent_id, parent_url , ifnull( parent_fisLink, 0 ) as parent_fisLink 
from   (SELECT @r AS catag_id, 
   (SELECT @r := fparent    FROM postscatag WHERE id = catag_id and fdeleted = 0 ) AS parent_id, 
    (SELECT  @n := fname  FROM postscatag WHERE id = catag_id and fdeleted = 0 ) AS parent_name, 
    (SELECT  @w := furl  FROM postscatag WHERE id = catag_id and fdeleted = 0 ) AS parent_url, 
    (SELECT  @z := fisLink  FROM postscatag WHERE id = catag_id and fdeleted = 0 ) AS parent_fisLink, 
   @l := @l + 1 AS level 
 FROM (SELECT @r := id_gatag, @l := 0) val, postscatag WHERE @r <> 0 ) as tbl ) as tb where id <> 0 ;
 
 


 
  
     
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPRating_AllComments` */;
DELIMITER ;;
CREATE  PROCEDURE `SPRating_AllComments`(IN `Wheree` VARCHAR(1055) CHARSET utf8mb4, IN `limitt` INT)
BEGIN
   
   call SPNobat_DeactiveNearNobats();
   	  	  if  length(LTRIM(rtrim(Wheree)))<2  then
	  set  Wheree=' 1=1' ;
      end if;
      
        SET @stmt_str = CONCAT('SELECT   rating.Fldid ,  fMilladyToShamsi_NoTime( nobat_allnobats.FldDate) AS FldDatePER,  fMilladyToShamsi_ByTime( nobat_allnobats.FldCallTime) AS PersianCallTime,  nobat_allnobats.FldidLastLevel, 
                         tblwebusers.FldName, tblwebusers.FldLastName, tblwebusers.FldSex, tblwebusers.FldNationalCode as FldCodeMeli , tblwebusers.FldMobile as FldMobileNO,   rating.FldRate,   rating.FldStatus,   rating.FldComment, 
                          rating.FldIdNobat,  rating.FldIdCustomer, nobat_level1.Fldtitle,  nobat_level2_items.Fldtitle AS  level2_Items_Fldtitle,  nobat_allnobats.FldFlag,  nobat_allnobats.FldAproved,  nobat_allnobats.FldActive
FROM            tblwebusers INNER JOIN
                          rating ON tblwebusers.Fldid =  rating.FldIdCustomer INNER JOIN
                          nobat_allnobats ON  rating.FldIdNobat =  nobat_allnobats.Fldid INNER JOIN
                         nobat_level1 ON  nobat_allnobats.FldidLastLevel = nobat_level1.Fldid INNER JOIN
                          nobat_level2_items ON nobat_level1.FldIdlevel2_Items =  nobat_level2_items.Fldid
WHERE        ( nobat_allnobats.FldDeleted = 0) AND ( rating.flddeleted = 0)   ', Wheree, '
ORDER BY  nobat_allnobats.Fldid   limit ', limitt, ' ');
  PREPARE stmt FROM @stmt_str;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
   
     
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPRating_AllCommentsByIdLevel1` */;
DELIMITER ;;
CREATE  PROCEDURE `SPRating_AllCommentsByIdLevel1`(IN IdLevel1  bigint  , IN limitt INT)
BEGIN
   
   call SPNobat_DeactiveNearNobats();
   	  	 
SELECT         rating.Fldid,  ( rating.FldInsertTime) AS insertTime,  tblwebusers.FldName,  tblwebusers.FldLastName,  rating.FldRate,  rating.FldComment,  rating.FldSuggest
FROM             tblwebusers INNER JOIN
                          rating ON  tblwebusers.Fldid =  rating.FldIdCustomer INNER JOIN
                          nobat_allnobats ON  rating.FldIdNobat =  nobat_allnobats.Fldid
WHERE        ( nobat_allnobats.FldDeleted = 0) AND ( rating.flddeleted = 0) AND ( rating.FldStatus = 2) AND ( nobat_allnobats.FldidLastLevel = IdLevel1)
ORDER BY  rating.FldInsertTime DESC  limit limitt ;
         
     
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPReportsAdd` */;
DELIMITER ;;
CREATE  PROCEDURE `SPReportsAdd`(IN `R_ptid` INT, IN `_rptstring` LONGTEXT CHARSET utf8mb4, IN `Rptname` VARCHAR(200) CHARSET utf8mb4, IN `ModuleID` INT)
BEGIN
 
insert reports(Rptid,RptContent,Rptname,ModuleID,FormCode) VALUES( R_ptid ,_rptstring,Rptname,ModuleID, 1 );

    SELECT   LAST_INSERT_ID();

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPReportsUpdateAndLog` */;
DELIMITER ;;
CREATE  PROCEDURE `SPReportsUpdateAndLog`(IN `R_ptid` INT, IN `_rptstring` LONGTEXT CHARSET utf8mb4)
BEGIN
insert tblreportlog(FldReport,FldName,FldIdUser) select RptContent,Rptname,1 from reports where RptContent is Not NULL and RptID=R_ptid ;
update reports set RptContent=_rptstring  where RptID=R_ptid ;
 select RptID from  reports where RptID = R_ptid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPReports_FindByid` */;
DELIMITER ;;
CREATE  PROCEDURE `SPReports_FindByid`(IN `R_ptid` INT)
BEGIN
select * from reports where Rptid =  R_ptid  ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPreview_and_rate_Aprov` */;
DELIMITER ;;
CREATE  PROCEDURE `SPreview_and_rate_Aprov`(IN t_ype  int , IN idComment  int   )
BEGIN
   
  declare f_iditem bigint;
 
 declare commentsCount int   ;
 Declare SumRate bigint;
  Declare endRate bigint  ;
 declare SuggestCount int   ;
  declare Suggest_per int   ;
  
    declare f_id_VarientProduct bigint;
   declare commentsCount_VarientProduct int   ;
 Declare SumRate_VarientProduct bigint;
  Declare endRate_VarientProduct bigint  ;
 declare SuggestCount_VarientProduct int   ;
  declare Suggest_per_VarientProduct int   ;
  
  
    declare f_idstore bigint;
   declare commentsCount_store int   ;
 Declare SumRate_store bigint;
  Declare endRate_store bigint  ;
 declare SuggestCount_store int   ;
  declare Suggest_per_store int   ;
 
  
 set commentsCount = 0;
  set SumRate = 0;
   set SuggestCount = 0;
    set  Suggest_per = 0;
    
   set commentsCount_VarientProduct = 0;
  set SumRate_VarientProduct = 0;
   set SuggestCount_VarientProduct = 0;
    set  Suggest_per_VarientProduct = 0; 

     set commentsCount_store = 0;
  set SumRate_store = 0;
   set SuggestCount_store = 0;
    set  Suggest_per_store= 0; 

    
update tbl_review_and_rate set fstatus = t_ype where  id=idComment and fdeleted=0  ;


 
set f_iditem = (  select  fiditem from tbl_review_and_rate where id = idComment );
set f_id_VarientProduct = (  select  fidproducts_varients  from tbl_review_and_rate where id = idComment );
set f_idstore = (  select  fidstore  from tbl_review_and_rate where id = idComment );


set commentsCount = ( SELECT COUNT(*) FROM tbl_review_and_rate where fiditem = f_iditem and ( fstatus = 2)
  AND ( fdeleted = 0) and ftype = "product" );
set commentsCount_VarientProduct = ( SELECT COUNT(*) FROM tbl_review_and_rate where fidproducts_varients = f_id_VarientProduct and ( fstatus = 2)
  AND ( fdeleted = 0) and ftype = "product" );
set commentsCount_store = ( SELECT COUNT(*) FROM tbl_review_and_rate where fidstore = f_idstore and ( fstatus = 2)
  AND ( fdeleted = 0) and ftype = "product" );


set SumRate = (  SELECT sum( frate) FROM tbl_review_and_rate where fiditem = f_iditem and ( fstatus = 2)
  AND ( fdeleted = 0) and ftype = "product" );
set SumRate_VarientProduct = (  SELECT sum( frate) FROM tbl_review_and_rate where fidproducts_varients = f_id_VarientProduct and ( fstatus = 2)
  AND ( fdeleted = 0) and ftype = "product" );
set SumRate_store = (  SELECT sum( frate) FROM tbl_review_and_rate where fidstore = f_idstore and ( fstatus = 2)
  AND ( fdeleted = 0) and ftype = "product" );


set SuggestCount = ( SELECT COUNT(*)   FROM tbl_review_and_rate where fiditem = f_iditem and ( fstatus = 2)
  AND ( fdeleted = 0) and ftype = "product"  and fsuggest = 1  ) ;
set SuggestCount_VarientProduct = ( SELECT COUNT(*)   FROM tbl_review_and_rate where fidproducts_varients = f_id_VarientProduct  and ( fstatus = 2)
  AND ( fdeleted = 0) and ftype = "product"  and fsuggest = 1  ) ;  
set SuggestCount_store = ( SELECT COUNT(*)   FROM tbl_review_and_rate where fidstore = f_idstore  and ( fstatus = 2)
  AND ( fdeleted = 0) and ftype = "product"  and fsuggest = 1  ) ;    
  
  
  set commentsCount_VarientProduct = (ifnull(commentsCount_VarientProduct ,0 ) ) ;
  set SumRate_VarientProduct = (ifnull(SumRate_VarientProduct ,0 ))  ;
   set SuggestCount_VarientProduct = ( ifnull(SuggestCount_VarientProduct ,0 ))  ;
 
  set commentsCount = (ifnull(commentsCount ,0 ) ) ;
  set SumRate = (ifnull(SumRate ,0 ))  ;
   set SuggestCount = ( ifnull(SuggestCount ,0 ))  ;
   
    set commentsCount_store = (ifnull(commentsCount_store ,0 ) ) ;
  set SumRate_store = (ifnull(SumRate_store ,0 ))  ;
   set SuggestCount_store = ( ifnull(SuggestCount_store ,0 ))  ; 


 if commentsCount > 0 and  SumRate > 0 then
set endRate = (  SumRate  / commentsCount ) ;
end if ;
 if commentsCount_VarientProduct > 0 and  SumRate_VarientProduct > 0 then
set endRate_VarientProduct = (  SumRate_VarientProduct  / commentsCount_VarientProduct ) ;
end if ;

  if commentsCount_store > 0 and  SumRate_store > 0 then
set endRate_store = (  SumRate_store  / commentsCount_store ) ;
end if ;


update products set  frate = endRate , fcomments= commentsCount      , fsuggests=SuggestCount where   id = f_iditem   ;

update products set  frate = 100 , fcomments= 0   , fsuggests=0 where   fcomments  is null or  fcomments =0  ;

update products_varients set  frate = endRate_VarientProduct , fcomments= commentsCount_VarientProduct      , fsuggests=SuggestCount_VarientProduct  where   id = f_id_VarientProduct   ;

update products_varients set  frate = 100 , fcomments= 0   , fsuggests=0  where   fcomments  is null or  fcomments =0 ;


update stores set  frate = endRate_store , fcomments= commentsCount_store      , fsuggests=SuggestCount_store  where   id = f_idstore   ;

update stores  set  frate = 100 , fcomments= 0   , fsuggests=0  where   fcomments  is null or  fcomments =0 ;


select endRate,endRate_VarientProduct,endRate_store ;
     
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spSerachFromWeb` */;
DELIMITER ;;
CREATE  PROCEDURE `spSerachFromWeb`(IN `Wheree` VARCHAR(1055) CHARSET utf8mb4, IN `limitt` INT)
BEGIN

   set  Wheree=(select  concat( '%', Wheree ,'%' ));
   
   
SELECT       nobat_level1.Fldid, nobat_level1.Fldtitle, nobat_level1.FldActive, nobat_level1.FldSex, nobat_level1.FldShortCode, nobat_level1.FldWebDesc, nobat_level2_items.Fldtitle AS Fldtitle2
  ,nobat_level1.FldLastCommensCount as commentsCount , 
  CASE  
      WHEN nobat_level1.FldLastCommensCount >0  THEN round( nobat_level1.FldLasRating/nobat_level1.FldLastCommensCount , 2)  
      ELSE 0
  END as avrageRating
    ,
CASE  
      WHEN nobat_level1.FldLastCommensCount >0  THEN round(( nobat_level1.FldLastSuggest/nobat_level1.FldLastCommensCount)*100 , 2)  
      ELSE 0
  END as  SuggestCount   , nobat_level1.fimages,
                      nobat_level1.furl,
                      nobat_level1.fhtml,
                      nobat_level1.flast_update
 
FROM            nobat_level1 INNER JOIN
                         nobat_level2_items ON nobat_level1.FldIdlevel2_Items = nobat_level2_items.Fldid
WHERE      (  (nobat_level2_items.FldDeleted = 0) AND (nobat_level1.FldDeleted = 0)) and
                        (  (nobat_level1.FldShortCode LIKE Wheree) or   (nobat_level2_items.Fldtitle LIKE Wheree)  or (nobat_level1.Fldtitle LIKE Wheree)) limit limitt  ;
   
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPSMS_AllSMSSended_UserMessages` */;
DELIMITER ;;
CREATE  PROCEDURE `SPSMS_AllSMSSended_UserMessages`(   
in idCustomer bigint ,
   in count bigint  )
BEGIN

SELECT        allsmssended.tell, allsmssended.message, fMilladyToShamsi_ByTime( allsmssended.insertTime ) as insertTime  , allsmssended.result, tblwebusers.Fldid
 
FROM            allsmssended INNER JOIN
                         tblwebusers ON allsmssended.tell = tblwebusers.FldMobile
						 where tblwebusers.fldid = idCustomer
						 order by allsmssended.insertTime desc limit count  ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPStores_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPStores_AddUpdate`(
IN `i_d` BIGINT,
 IN `f_idwebuser` BIGINT, 
IN `f_name` VARCHAR(900) CHARSET utf8mb4, 
 IN `f_latinName` VARCHAR(900) CHARSET utf8mb4,  
 IN `f_images` VARCHAR(1900) CHARSET utf8mb4, 
 IN `f_layout`longtext CHARSET utf8mb4, 
  IN `f_ctitle` VARCHAR(555) CHARSET utf8mb4,   
  IN `f_desc` VARCHAR(955) CHARSET utf8mb4,  
 
  
   IN `i_s_new` tinyint(3)  ,  
      IN `i_s_official` tinyint(3)  , 
         IN `i_s_trusted` tinyint(3)  , 
         IN `f_status` VARCHAR(555) CHARSET utf8mb4,   
          IN `f_comissionP` tinyint(3)  , 
         IN `f_id_province_cities` int, 
IN `f_plak` VARCHAR(255) CHARSET utf8mb4, 
IN `f_vahed` VARCHAR(255) CHARSET utf8mb4, 
IN `f_postalcode` VARCHAR(255) CHARSET utf8mb4, 
IN `f_street` VARCHAR(255) CHARSET utf8mb4, 
 IN `f_mapCenter` VARCHAR(255) CHARSET utf8mb4, 
  IN `f_mapAddress` longtext CHARSET utf8mb4, 
  
    IN `f_BankInfo` longtext CHARSET utf8mb4, 
         
          IN `o_n_no_return` tinyint(3)  , 
           IN `o_n_commitment` tinyint(3)  , 
            IN `o_n_time_shipping` tinyint(3)  , 
            
    IN `f_name_og` VARCHAR(555) CHARSET utf8mb4,  
      IN `f_encode_og` VARCHAR(555) CHARSET utf8mb4,  
        IN `f_sabtNo_og` VARCHAR(555) CHARSET utf8mb4,  
          IN `f_address_og` VARCHAR(1055) CHARSET utf8mb4,  
            IN `f_tell_og` VARCHAR(555) CHARSET utf8mb4,  IN `f_supportMobile` VARCHAR(555) CHARSET utf8mb4,  
              IN `f_shenaseMeli_og` VARCHAR(555) CHARSET utf8mb4,  
IN `f_active` TINYINT
)
BEGIN

declare co integer ;
       set   co  =  (select count(id ) as id   from stores where fidwebuser = f_idwebuser  ) ;
       
       if  co > 0  then
        set i_d =   (select  id from stores where fidwebuser = f_idwebuser ) ;
       end if ;


  IF i_d > 0 THEN
      
   
       UPDATE stores
            SET 
              fcomissionP=f_comissionP ,  fname=f_name , factive=f_active , fctitle= f_ctitle , fdesc=f_desc , flatinName=f_latinName  , fimages=f_images , flayout =f_layout  ,
                fname_og=f_name_og ,  fencode_og=f_encode_og ,fsabtNo_og=f_sabtNo_og , faddress_og=f_address_og , ftell_og=f_tell_og , fshenaseMeli_og=f_shenaseMeli_og ,
                is_new=i_s_new,is_official=i_s_official, is_trusted=i_s_trusted, fstatus=f_status ,  
                on_no_return=o_n_no_return,on_commitment=o_n_commitment,on_time_shipping=o_n_time_shipping  ,
                fBankInfo=f_BankInfo, fdeleted = 0 , fsupportMobile=f_supportMobile ,
                fid_province_cities=f_id_province_cities , fplak =f_plak , fpostalcode =f_postalcode , fvahed=f_vahed
                ,fstreet =f_street ,  fmapAddress=f_mapAddress , fmapCenter=f_mapCenter
                
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  stores (fcomissionP,fname,   factive  ,flatinName ,fimages ,flayout ,  fctitle , fdesc , fidwebuser , fname_og ,fencode_og   ,fsabtNo_og  ,faddress_og 
              , ftell_og  ,fshenaseMeli_og  ,is_new , is_official , is_trusted ,  frate ,on_no_return ,on_commitment ,on_time_shipping,
               fid_province_cities , fplak ,  fpostalcode,fvahed , fstreet ,  fmapAddress , fmapCenter ,fBankInfo , fsupportMobile  )
               VALUES (f_comissionP ,f_name,  f_active       ,f_latinName  ,f_images   ,f_layout ,  f_ctitle , f_desc ,f_idwebuser , f_name_og ,f_encode_og   ,f_sabtNo_og  ,f_address_og  
               , f_tell_og  ,f_shenaseMeli_og  ,i_s_new , i_s_official , i_s_trusted ,   100 , 100, 100, 100 ,
               f_id_province_cities , f_plak , f_postalcode,f_vahed , f_street, f_mapAddress , f_mapCenter ,f_BankInfo , f_supportMobile ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPstory_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPstory_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_name` VARCHAR(255) CHARSET utf8mb4, 
 IN `f_extra` LONGTEXT CHARSET utf8mb4,
 IN `f_items` LONGTEXT CHARSET utf8mb4,
 
 
IN `f_desc` LONGTEXT CHARSET utf8mb4,
 IN `f_allowSellersAdd` TINYINT ,
IN `f_active` TINYINT
)
BEGIN

 

IF i_d > 0 THEN
       UPDATE story
            SET 
               fname=f_name , factive=f_active,   fdesc=f_desc  , fextra=f_extra  , fitems=f_items , fallowSellersAdd=f_allowSellersAdd
                  
                  
         WHERE id = i_d ;
		 select i_d  as id;
  END IF;
  IF i_d = 0 THEN
              INSERT  INTO  story (  fname ,fextra , factive ,   fdesc  ,fitems  , fallowSellersAdd   )
               VALUES ( f_name , f_extra, f_active,      f_desc  ,f_items  , f_allowSellersAdd  ) ;
    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPtags_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPtags_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_idwebuser` BIGINT, 
IN `f_name` VARCHAR(900) CHARSET utf8mb4, 
 
 IN `f_images` VARCHAR(1900) CHARSET utf8mb4, 
 IN `f_layout`longtext CHARSET utf8mb4, 
 
  IN `f_ctitle` VARCHAR(555) CHARSET utf8mb4,   
  IN `f_desc` VARCHAR(955) CHARSET utf8mb4,  
IN `f_active` TINYINT
)
BEGIN

declare co integer ;
       set   co  =  (select count(id ) as id   from tags where fname = f_name  ) ;
       
       if  co > 0  then
        set i_d =   (select  id       from tags where fname = f_name  limit 1 ) ;
       end if ;


  IF i_d > 0 THEN
      
   
       UPDATE tags
            SET 
                fname=f_name , factive=f_active , fctitle= f_ctitle , fdesc=f_desc ,  fimages=f_images , flayout =f_layout  
               ,  flast_update = now()
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  tags (fname,   factive    ,fimages ,flayout   , fctitle ,fdesc , fidwebuser)
               VALUES (f_name,  f_active       ,f_images   ,f_layout  , f_ctitle,f_desc , f_idwebuser ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sptblexcelemailsSenderSettings_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `sptblexcelemailsSenderSettings_AddUpdate`(IN   f_title   VARCHAR(150) CHARSET utf8mb4, IN  f_data  longtext  CHARSET utf8mb4   )
BEGIN

  
 
   if (select count(*) from emailsend_settings where ftitle = f_title ) > 0 then    
  update emailsend_settings set fdata=f_data   where ftitle = f_title    ;
  else  
  INSERT INTO emailsend_settings  (fdata, ftitle) VALUES  
 (f_data , f_title ) ;
  end if; 



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sptblexcelemails_list_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `sptblexcelemails_list_AddUpdate`(IN   f_email   VARCHAR(150) CHARSET utf8mb4, IN   f_name   VARCHAR(150)  CHARSET utf8mb4 , IN   u_tm_id   VARCHAR(150)  CHARSET utf8mb4, IN   u_tm_source   VARCHAR(150)  CHARSET utf8mb4
, IN   u_rl   VARCHAR(150)  CHARSET utf8mb4, IN   u_tm_title   VARCHAR(150)  CHARSET utf8mb4, IN   f_title   VARCHAR(150)  CHARSET utf8mb4, IN   c_hecked   integer  )
BEGIN

  
 
   if (select count(*) from excelemails_list where femail = f_email ) > 0 then    
  update excelemails_list set fname=f_name , utm_id=u_tm_id , utm_source=u_tm_source , url=u_rl , utm_title=u_tm_title
  , checked=c_hecked  where ftitle = f_title   and  femail = f_email ;
  else  
  INSERT INTO excelemails_list  (femail, fname, utm_id ,utm_source ,url,utm_title ,ftitle,checked ) VALUES  
 (f_email, f_name, u_tm_id ,u_tm_source ,u_rl,u_tm_title ,f_title,c_hecked ) ;
  end if; 



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spTblHolidaysGet` */;
DELIMITER ;;
CREATE  PROCEDURE `spTblHolidaysGet`()
BEGIN
 select FldId , FldTitle, DATE_FORMAT( FldDate,'%Y/%m/%d') AS FldDate   from tblholidays where flddeleted=0 ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sptblUsers_Insert` */;
DELIMITER ;;
CREATE  PROCEDURE `sptblUsers_Insert`(
IN `F_ldUsername` VARCHAR(2250) CHARSET utf8mb4,
 IN `F_ldPassword` VARCHAR(2250) CHARSET utf8mb4, 
 IN `F_ldname` VARCHAR(950) CHARSET utf8mb4,
 IN `F_ldLastName` VARCHAR(950) CHARSET utf8mb4,
 IN `F_ldEmail` VARCHAR(150) CHARSET utf8mb4,
 IN `F_ldMobile` VARCHAR(20) CHARSET utf8mb4,
 IN `F_ldActivation` TINYINT(1),
 IN `F_ldType` TINYINT(5),
 IN `F_ldIsAdmin` TINYINT(1),
 IN `F_ldNeedPassChange` TINYINT(1))
BEGIN
 declare id bigint ;
 
set F_ldMobile=LTRIM(rtrim(F_ldMobile));
set F_ldUsername=LTRIM(rtrim(F_ldUsername));
set F_ldPassword=LTRIM(rtrim(F_ldPassword));

 
set id= (select fldid from tblusers where FldUsername=F_ldUsername  or   FldMobile = F_ldMobile limit 1);
if  id>0  then
		update tblusers set FldUsername=F_ldUsername ,   FldMobile = F_ldMobile  ,  FldNeddChangePass=F_ldNeedPassChange  , fldemail=F_ldEmail , FldType = F_ldType ,
		FldPass =F_ldPassword ,  FldIsActive =F_ldActivation  , FldIsAdmin = F_ldIsAdmin  , FldDeleted=0  where fldid =id ;
		select id ;
		 
		else
		 
		INSERT INTO  tblusers(FldUsername, FldPass, Fldname , FldLastName , FldType ,  FldIsActive,  FldIsAdmin ,  FldEmail, FldMobile,   FldNeddChangePass)
						  values(F_ldUsername, F_ldPassword, F_ldname , F_ldLastName ,F_ldType ,  F_ldActivation , F_ldIsAdmin ,  F_ldEmail, F_ldMobile, F_ldNeedPassChange);
		select LAST_INSERT_ID() as id;
end if;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sptblWebSettingsPortall_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `sptblWebSettingsPortall_AddUpdate`(IN `F_ldKey` VARCHAR(150) CHARSET utf8mb4, IN `F_ldValue` LONGTEXT CHARSET utf8mb4)
BEGIN


 
UPDATE     tblwebsettingsportall   SET    FldValue =F_ldValue  where FldKey=F_ldKey     ;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sptblWebUsers_ChangePasswordNewPortall` */;
DELIMITER ;;
CREATE  PROCEDURE `sptblWebUsers_ChangePasswordNewPortall`(
in F_ldId int,
 in U_serName varchar(200)  CHARSET utf8mb4,
 in O_ldPassword varchar(200)  CHARSET utf8mb4, 
 in N_ewPassword varchar(200)  CHARSET utf8mb4)
BEGIN

declare IdWebuser int ;
set IdWebuser = -1 ;
set IdWebuser = (select  FldId from tblwebusers where FldId=F_ldId and FldPassword=O_ldPassword   and FldUsername = U_serName) ;


if IdWebuser = -1 or IdWebuser is null  then
 
    select -1 as id ;
 
else
 
    update  tblwebusers set FldPassword=N_ewPassword,   FldNeedPassChange = 0, FldPasswordChangeDate = now()
        where FldId=IdWebuser ;
    select IdWebuser as id;
end if ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sptblWebUsers_CheckedLogin` */;
DELIMITER ;;
CREATE  PROCEDURE `sptblWebUsers_CheckedLogin`( IN F_ldUsername varchar(50) CHARSET utf8mb4, 
 IN F_ldPassword varchar(200)  CHARSET utf8mb4)
BEGIN

declare F_ldNationalCode varchar(20);
declare IS_Customer tinyint(1);
declare IS_Level1  tinyint(1);
declare IS_Admin  tinyint(1);
declare IS_Seller  tinyint(1);
declare Id_Customer bigint;
declare Id_Level1 bigint;
declare currentID int;
declare tmp varchar(900);

set tmp = ( select  CONCAT( FldId,'___#____',ifnull(FldNationalCode,0) ,'___#____', FldId ,'___#____', ifnull(FldIdLevel1,0) ,'___#____',ifnull(FldIsAdmin,0),'___#____',ifnull(FldIsSeller,0))  as res
from tblwebusers where FldUsername=F_ldUsername and  (FldPassword)=(F_ldPassword) and FldDeleted=0 and FldActivation = 1 );

set currentID =( SELECT SPLIT_STR(tmp,'___#____',1));
set F_ldNationalCode =( SELECT SPLIT_STR(tmp,'___#____',2));
set Id_Customer =( SELECT SPLIT_STR(tmp,'___#____',3));
set Id_Level1 =( SELECT SPLIT_STR(tmp,'___#____',4));
set IS_Admin =( SELECT SPLIT_STR(tmp,'___#____',5));
set IS_Seller =( SELECT SPLIT_STR(tmp,'___#____',6));




 

 if (select COUNT(*) from nobat_level1  where  fldid = Id_Level1  and FldDeleted = 0  )>0 then
  
   set IS_Level1=1 ;
  end if;

if currentID > 0 then
 
	UPDATE tblwebusers
       SET  FldLastLogin  = now()
    WHERE FldId = currentID ;
end if;

if Id_Customer>0 then
set IS_Customer=1 ;
end if;
select *, ifnull(IS_Customer,0)  as IS_Customer,  ifnull(IS_Level1,0)  as IS_Level1 ,  ifnull(IS_Admin,0)  as IS_Admin ,  ifnull(IS_Seller,0)  as IS_Seller 
from tblwebusers where FldId = currentID ;





END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sptblWebUsers_Insert` */;
DELIMITER ;;
CREATE  PROCEDURE `sptblWebUsers_Insert`(
IN `F_ldUsername` VARCHAR(2250) CHARSET utf8mb4, 
IN `F_ldPassword` VARCHAR(2250) CHARSET utf8mb4, 
IN `F_ldIdCustomer` INT, 
IN `F_ldActivation` TINYINT(1), 
IN `F_ldNationalCode` VARCHAR(20) CHARSET utf8mb4, 
IN `F_ldEmail` VARCHAR(200) CHARSET utf8mb4, 
IN `F_ldMobile` VARCHAR(200) CHARSET utf8mb4, 
IN `F_ldLastPassType` TINYINT(1), 

IN `f_name_og` VARCHAR(445) CHARSET utf8mb4, 
IN `f_encode_og` VARCHAR(45) CHARSET utf8mb4, 
IN `f_shenaseMeli_og` VARCHAR(45) CHARSET utf8mb4, 
IN `f_sabtNo_og` VARCHAR(45) CHARSET utf8mb4, 
IN `f_address_og` VARCHAR(1045) CHARSET utf8mb4, 
IN `f_tell_og` VARCHAR(45) CHARSET utf8mb4, 



IN `F_ldNeedPassChange` TINYINT(1))
BEGIN
 declare id bigint ;
set F_ldNationalCode=LTRIM(rtrim(F_ldNationalCode));
set F_ldMobile=LTRIM(rtrim(F_ldMobile));
set F_ldUsername=LTRIM(rtrim(F_ldUsername));
set F_ldPassword=LTRIM(rtrim(F_ldPassword));

 



set id= (select fldid from tblwebusers where FldUsername=F_ldUsername  or FldMobile = F_ldMobile);
if  id>0  then
		update tblwebusers set     FldNeedPassChange=1    ,fname_og=f_name_og ,fencode_og=f_encode_og , fshenaseMeli_og=f_shenaseMeli_og ,
        fsabtNo_og=f_sabtNo_og, faddress_og=f_address_og , ftell_og=f_tell_og ,
		FldPassword =F_ldPassword , FldActivation =F_ldActivation  , FldDeleted=0  where fldid =id ;
		select id ;
		 
 else
		 
		INSERT INTO  tblwebusers(FldUsername, FldPassword,  FldActivation, FldNationalCode, FldEmail, FldMobile,  FldNeedPassChange , fmnulist,
       fname_og , fencode_og , fshenaseMeli_og ,fsabtNo_og ,faddress_og , ftell_og )
						  values(F_ldUsername, F_ldPassword,  F_ldActivation, F_ldNationalCode, F_ldEmail, F_ldMobile, F_ldNeedPassChange , "webuser" ,
                         f_name_og , fencode_og , f_shenaseMeli_og ,f_sabtNo_og ,f_address_og , f_tell_og  );
		select LAST_INSERT_ID() as id;
end if;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sptblWebUser_ExistEmail` */;
DELIMITER ;;
CREATE  PROCEDURE `sptblWebUser_ExistEmail`(IN  Email VARCHAR(200) CHARSET utf8mb4)
BEGIN

 select ifnull(count( * ),0) as result  from tblwebusers where FldEmail=Email ;



 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sptblWebUser_ExistNationalCode` */;
DELIMITER ;;
CREATE  PROCEDURE `sptblWebUser_ExistNationalCode`(IN `nationalCode` VARCHAR(10) CHARSET utf8mb4, IN `checkAll` TINYINT(1))
BEGIN

declare cnt   int  ;
declare currentID int;

set cnt =( select  count(*) from tblwebusers where FldNationalCode=nationalCode and FldDeleted=0) ;
if ifnull(cnt, 0) > 0 then
 
	set cnt = -1 ;
end if ;

 



select ifnull(cnt,0) as result ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sptblWebUser_ExistUserName` */;
DELIMITER ;;
CREATE  PROCEDURE `sptblWebUser_ExistUserName`(IN  UserName VARCHAR(100) CHARSET utf8mb4)
BEGIN

 select ifnull(count( * ),0) as result  from tblwebusers where FldUsername=UserName ;



 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPtbl_faq_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPtbl_faq_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_question` VARCHAR(255) CHARSET utf8mb4, 
IN `f_answer` LONGTEXT CHARSET utf8mb4,
IN `f_images` LONGTEXT CHARSET utf8mb4,
IN `f_author` VARCHAR(255) CHARSET utf8mb4, 
IN `f_idcatags`  mediumtext CHARSET utf8mb4, 
IN `f_keys` VARCHAR(1255) CHARSET utf8mb4, 
IN `f_active` TINYINT
)
BEGIN
 

  IF i_d > 0 THEN
      
   
       UPDATE  tbl_faq
            SET 
                fquestion=f_question , factive=f_active,fimages=f_images  , fidcatags=f_idcatags   , flast_update=now() ,
                fanswer=f_answer , fauthor=f_author   ,fkeys=f_keys 
                
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  tbl_faq  (fquestion,fanswer,fauthor,fidcatags,fkeys,factive,fimages,flast_update
             )
               VALUES (f_question,f_answer,f_author,f_idcatags,f_keys,f_active,f_images,now()
                   ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPtbl_faq_UpdateHTML` */;
DELIMITER ;;
CREATE  PROCEDURE `SPtbl_faq_UpdateHTML`(
IN `i_d` BIGINT, 
IN `f_html` LONGTEXT CHARSET utf8mb4
)
BEGIN

 

  IF i_d > 0 THEN
      
   
       UPDATE  tbl_faq
            SET 
                fhtml=f_html   , flast_update=now()      WHERE id = i_d ; 
                select i_d  as id;
   
  END IF;

  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPtbl_forms_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPtbl_forms_AddUpdate`(
IN `i_d` BIGINT, 
 IN `f_title` varchar(400) CHARSET utf8mb4,
 IN `f_author` varchar(400) CHARSET utf8mb4,
 IN `f_mobiletoSms` varchar(400) CHARSET utf8mb4, 
 IN `f_emailtoSend` varchar(400) CHARSET utf8mb4,
  IN `f_redirectto` varchar(400) CHARSET utf8mb4,
  IN `f_btntext` varchar(100) CHARSET utf8mb4,
   IN `f_successMsg` varchar(550) CHARSET utf8mb4,
   IN `f_failMsg` varchar(550) CHARSET utf8mb4,
 
IN `f_layout` LONGTEXT CHARSET utf8mb4,
 
 
 
IN `f_active` TINYINT
)
BEGIN
 

  IF i_d > 0 THEN
      
   
       UPDATE  tbl_forms
            SET 
                flayout=f_layout , factive=f_active,  flast_update=now()    ,ftitle=f_title  , fauthor=f_author , fredirectto=f_redirectto
                , fmobiletoSms =f_mobiletoSms ,  femailtoSend=f_emailtoSend  , fbtntext =  f_btntext , fsuccessMsg  = f_successMsg  , ffailMsg  = f_failMsg
               
         WHERE id = i_d ;

		 select i_d  as id;
   
  END IF;

  IF i_d = 0 THEN
 
             
              INSERT  INTO  tbl_forms  (flayout,factive,flast_update ,ftitle , fauthor,fmobiletoSms,femailtoSend , fbtntext , fsuccessMsg , ffailMsg ,fredirectto )   
              VALUES (f_layout,f_active,now() ,f_title ,f_author , f_mobiletoSms  , f_emailtoSend  ,f_btntext  , f_successMsg , f_failMsg ,f_redirectto ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPtbl_review_and_rate_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPtbl_review_and_rate_AddUpdate`(
 
 in      i_d bigint,
 in     f_idwebuser bigint,
  in    f_idref bigint,  
  in    f_iditem bigint,

in    f_idstore bigint,
in    f_idproducts_varients bigint,
 in     f_type varchar(400)   CHARSET utf8mb4 ,
 in     f_rate int(10),
 
 in     f_text longtext   CHARSET utf8mb4 ,
in     f_cons longtext   CHARSET utf8mb4 ,
in     f_positivePoints longtext   CHARSET utf8mb4 ,
 in     f_anonymous tinyint(2),
 in     f_title varchar(400)   CHARSET utf8mb4 ,
 in     f_extra longtext   CHARSET utf8mb4 ,
 in     f_suggest tinyint(2)
 )
BEGIN

   DECLARE   varid bigint ;
   
   
   if f_rate<20 then
   set f_rate=20 ;
end if;
     

      IF  i_d > 0  then
	  
		UPDATE  tbl_review_and_rate
            SET 
               frate = f_rate,         fcons = f_cons, 
			   ftext=f_text , fpositivePoints=f_positivePoints ,
               ftitle=f_title,
				 fstatus=1 , fdeleted= 0 ,
				 fsuggest = f_suggest , fanonymous=f_anonymous , flastUpdate=now()
         WHERE   id = i_d  ;

		 select i_d as id ;

		end if ;
      IF i_d = 0 then
          
		
		     SET varid =   (   SELECT   id   FROM tbl_review_and_rate  WHERE  fidwebuser=f_idwebuser  AND  fidref = f_idref and fiditem =f_iditem  limit 1  ) ;
		  IF  varid > 0 then
           SET i_d = varid ;
 end if ;
             IF i_d > 0 then
			  
         UPDATE tbl_review_and_rate    SET   frate = f_rate,         fcons = f_cons, 
			   ftext=f_text , fpositivePoints=f_positivePoints ,
               ftitle=f_title,
				 fstatus=1 , fdeleted= 0 ,
				 fsuggest = f_suggest , fanonymous=f_anonymous , flastUpdate=now()
		 WHERE id = i_d ;
		 
		  SELECT i_d as id ;
         END if;
      IF i_d = 0 then
          
             INSERT tbl_review_and_rate (fiditem,fidref ,  fidwebuser, frate    , 
		 ftext , fsuggest ,ftype ,fcons ,  fpositivePoints , ftitle  , fanonymous ,fextra,fidstore,fidproducts_varients)    VALUES (f_iditem,f_idref ,  f_idwebuser, f_rate ,
		 f_text , f_suggest ,f_type ,f_cons ,  f_positivePoints , f_title ,f_anonymous , f_extra , f_idstore,f_idproducts_varients) 

;
			 
            SELECT last_insert_id() as id ;

         END if ;
   END if ;
     
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPTiketsDetails_Add` */;
DELIMITER ;;
CREATE  PROCEDURE `SPTiketsDetails_Add`(
 
IN `f_idtiket`int, 
 
IN `f_text` longtext CHARSET utf8mb4,
IN `f_data`  longtext CHARSET utf8mb4 ,
IN `f_userId`  int ,
IN `f_type` varchar(200) CHARSET utf8mb4,
 IN `f_inseruser` VARCHAR(255) CHARSET utf8mb4 
)
BEGIN
    INSERT  INTO   codeman_tiketdetails   ( ftext   , fidtiket  ,  fdata   ,fuserId     ,ftype   ,finsertuser          )
   VALUES (f_text  ,    f_idtiket  ,   f_data      ,f_userId  ,f_type ,f_inseruser ) ;

    SELECT  LAST_INSERT_ID() as id;
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPTikets_Add` */;
DELIMITER ;;
CREATE  PROCEDURE `SPTikets_Add`(
 
 
IN `f_idtype`int, 
IN `f_subject` VARCHAR(255) CHARSET utf8mb4,
IN `f_userId`  int ,
 IN `f_inseruser` VARCHAR(255) CHARSET utf8mb4 
)
BEGIN
   INSERT  INTO  codeman_tikets   (  fidtype  ,fsubject  , fuserId    ,finsertuser             )
   VALUES (    f_idtype , f_subject  , f_userId  ,f_inseruser    ) ;
                  

    SELECT  LAST_INSERT_ID() as id;
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPuser_favorite_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPuser_favorite_AddUpdate`(
 
IN `i_dwebuser` BIGINT, 
 IN `i_dproduct` BIGINT ,IN `f_idVariantProduct` BIGINT 
)
BEGIN

 declare i_d bigint  ;
 set i_d =(select id from user_favorite where idwebuser=i_dwebuser and idproduct=i_dproduct) ;

  IF i_d  is null THEN
      
    INSERT  INTO  user_favorite (             idwebuser ,   idproduct  , fidVariantProduct      )
               VALUES (         i_dwebuser ,   i_dproduct  , f_idVariantProduct      ) ;
      
      else
   
    delete from user_favorite  WHERE id = i_d ;
		 
  END IF;

 
 
     select * from       user_favorite  where    idwebuser=i_dwebuser ;
             
                  
 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPvisitors_search_history_updateUserSearchHistory` */;
DELIMITER ;;
CREATE  PROCEDURE `SPvisitors_search_history_updateUserSearchHistory`(IN m_count int ,IN m_max int ,IN f__search varchar(50) CHARSET utf8mb4)
BEGIN

declare maxcount  int;
set maxcount = ( SELECT MAX(fresultCount)
    FROM visitors_search_history
    WHERE fsearch = f__search) ;
    
if     m_max > maxcount  then
set maxcount = m_max ;
end if;
    
    
UPDATE visitors_search_history
SET    fcount = fcount - 1  + m_count   ,  fresultCount= maxcount 
WHERE fsearch = f__search;








END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPwallet_Add` */;
DELIMITER ;;
CREATE  PROCEDURE `SPwallet_Add`(
IN `f_idwebuser` BIGINT, 
IN `f_deposit` BIGINT, 
IN `f_withdrawal` BIGINT, 
IN `f_desc` LONGTEXT CHARSET utf8mb4,
IN `f_extra` LONGTEXT CHARSET utf8mb4,
IN `f_ref` LONGTEXT CHARSET utf8mb4,
IN `f_insertedUser` BIGINT ,
IN `f_insertedUserFullName` varchar(450) CHARSET utf8mb4,
IN `f_type_ref` LONGTEXT CHARSET utf8mb4 
)
BEGIN
declare balance bigint  ;
START TRANSACTION;

if  f_deposit < 0 or f_withdrawal < 0 then
select -1 as balance;
end if;

if f_deposit > 0 then 
set f_withdrawal = 0 ;
 INSERT  INTO wallet   (  fidwebuser ,  fdeposit    ,  fwithdrawal     ,  fdesc   , fextra     ,   fref   ,    ftype_ref ,finsertedUser , finsertedUserFullName  )
               VALUES ( f_idwebuser ,  f_deposit    ,  0     ,  f_desc   , f_extra     ,   f_ref   ,    f_type_ref  ,f_insertedUser  , f_insertedUserFullName) ;
               
                SELECT  LAST_INSERT_ID() as id;
end if;

if f_withdrawal > 0 then 

set f_deposit = 0 ;

set balance = (	select ifnull( sum(fdeposit) -   sum(fwithdrawal)  , 0 ) as Balance   from   wallet      where     ftype_ref != 'tempTransaction'    and      fidwebuser  = f_idwebuser);

if balance >=  f_withdrawal  then 

 INSERT  INTO wallet   (  fidwebuser ,  fdeposit    ,  fwithdrawal     ,  fdesc   , fextra     ,   fref   ,    ftype_ref   ,finsertedUser  , finsertedUserFullName)
               VALUES ( f_idwebuser ,  0    ,  f_withdrawal     ,  f_desc   , f_extra     ,   f_ref   ,    f_type_ref  ,f_insertedUser ,f_insertedUserFullName  ) ;
               
                SELECT  LAST_INSERT_ID() as id;
       else 
       select -2  as balance ;
                end if ;
end if;
             
   
 COMMIT;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPwallet_Balance` */;
DELIMITER ;;
CREATE  PROCEDURE `SPwallet_Balance`(
IN `f_idwebuser` BIGINT
)
BEGIN

START TRANSACTION;


			select ifnull( sum(fdeposit) -   sum(fwithdrawal)  , 0 ) as Balance   from   wallet      where    fdeleted=0 and      fidwebuser  = f_idwebuser
               and   ftype_ref != 'tempTransaction' ;
   
 COMMIT;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPwallet_Balance_all` */;
DELIMITER ;;
CREATE  PROCEDURE `SPwallet_Balance_all`(
IN `f_idwebuser` BIGINT
)
BEGIN

START TRANSACTION;


			select ifnull( sum(fdeposit) -   sum(fwithdrawal)  , 0 ) as Balance   from   wallet    where  fdeleted=0  
              and   ftype_ref != 'tempTransaction'  ;
   
 COMMIT;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SPWebuser_bill_address_AddUpdate` */;
DELIMITER ;;
CREATE  PROCEDURE `SPWebuser_bill_address_AddUpdate`(
IN `i_d` BIGINT, 
IN `f_id_province_cities` int, 
IN `f_plak` VARCHAR(255) CHARSET utf8mb4, 
IN `f_vahed` VARCHAR(255) CHARSET utf8mb4, 
IN `f_postalcode` VARCHAR(255) CHARSET utf8mb4, 
IN `f_name` VARCHAR(255) CHARSET utf8mb4, 
IN `f_mobile` VARCHAR(255) CHARSET utf8mb4, 
IN `f_lastname` VARCHAR(255) CHARSET utf8mb4, 
IN `f_street` VARCHAR(255) CHARSET utf8mb4, 
 IN `f_idwebuser` BIGINT, 
 IN `f_mapCenter` VARCHAR(255) CHARSET utf8mb4, 
  IN `f_mapAddress` longtext CHARSET utf8mb4, 
IN `f_active` TINYINT
)
BEGIN

 

IF i_d > 0 THEN
       UPDATE webuser_bill_address
            SET  fid_province_cities=f_id_province_cities , fplak =f_plak ,flastname = f_lastname,fmobile =f_mobile ,fname =f_name ,fpostalcode =f_postalcode , fvahed=f_vahed
                ,fstreet =f_street ,factive =f_active , fmapAddress=f_mapAddress , fmapCenter=f_mapCenter
                  
         WHERE id = i_d ;
		 select i_d  as id;
  END IF;
  IF i_d = 0 THEN
              INSERT  INTO  webuser_bill_address (  fid_province_cities , fplak ,flastname ,fmobile ,fname , fpostalcode,fvahed , fstreet, factive ,fidwebuser ,
              fmapAddress , fmapCenter )
               VALUES (  f_id_province_cities , f_plak ,f_lastname ,f_mobile ,f_name , f_postalcode,f_vahed , f_street, f_active ,f_idwebuser ,
               f_mapAddress , f_mapCenter ) ;
    SELECT  LAST_INSERT_ID() as id;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `updateVariableProductStokManager` */;
DELIMITER ;;
CREATE  PROCEDURE `updateVariableProductStokManager`(IN `i_d`  bigint )
BEGIN
   select "no_Manage_stock" as type ,  count(*) as count , "" as max    from productview  where id= i_d    
   and (   (fManage_stock_quantity =0)    )
union
select "Manage_stock" as type , count(*) as count , max(fstock_quantity) as max   from productview  where id= i_d    
 and (    (fManage_stock_quantity =1 and fstock_quantity > 0) );

END ;;
DELIMITER ;

--
-- Final view structure for view `bill_coupon_info`
--

/*!50001 DROP VIEW IF EXISTS `bill_coupon_info`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `bill_coupon_info` AS select `bc`.`idbillcoupon` AS `idbillcoupon`,`b`.`id` AS `bid`,`b`.`fidwebuser` AS `bfidwebuser`,`b`.`fidbillpaymethods` AS `bfidbillpaymethods`,`b`.`fstatus` AS `bfstatus`,`b`.`fname` AS `bfname`,`b`.`flastname` AS `bflastname`,`b`.`fmobile` AS `bfmobile`,`b`.`faddress` AS `bfaddress`,`b`.`fdesc` AS `bfdesc`,`b`.`inserttime` AS `binserttime`,`b`.`fextra` AS `bfextra`,`b`.`ftransa` AS `bftransa`,`b`.`fidtransaction` AS `bfidtransaction`,`b`.`ftranUID` AS `bftranUID`,`bm`.`id` AS `bmid`,`bm`.`ftype` AS `bmftype`,`bm`.`fname` AS `bmname`,`bm`.`fdesc` AS `bmdesc`,`bm`.`fextra` AS `bmfextra` from ((`billing` `b` join `billpaymethods` `bm` on(`b`.`fidbillpaymethods` = `bm`.`id`)) join `billcoupon_used` `bc` on(`bc`.`id` = `b`.`fidbillcoupon_used`)) */;

--
-- Final view structure for view `billinfo`
--

/*!50001 DROP VIEW IF EXISTS `billinfo`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `billinfo` AS select `billing`.`id` AS `id`,`billing`.`faddedByAdminID` AS `faddedByAdminID`,`billing`.`fidwebuser` AS `fidwebuser`,`billing`.`fidbillpaymethods` AS `fidbillpaymethods`,`billing`.`fidbillcoupon_used` AS `fidbillcoupon_used`,`billing`.`idbillcarriermethods` AS `idbillcarriermethods`,`billing`.`idwebuser_bill_address` AS `idwebuser_bill_address`,`billing`.`fstatus` AS `fstatus`,`billing`.`fsubstatus` AS `fsubstatus`,`billing`.`fname` AS `fname`,`billing`.`flastname` AS `flastname`,`billing`.`fmobile` AS `fmobile`,`billing`.`ffiledata` AS `ffiledata`,`billing`.`faddress` AS `faddress`,`billing`.`fdeleverRangeStart` AS `fdeleverRangeStart`,`billing`.`fdeleverRangeEnd` AS `fdeleverRangeEnd`,`billing`.`fpostData` AS `fpostData`,`billing`.`fdesc` AS `fdesc`,`billing`.`inserttime` AS `inserttime`,`billing`.`fextra` AS `fextra`,`RETURNBILLPRICE`(`billing`.`id`) AS `billprice`,`billing`.`ftransa` AS `ftransa`,`billing`.`fidtransaction` AS `fidtransaction`,`billing`.`ftranUID` AS `ftranUID`,`billing`.`fip` AS `fip`,`billing`.`femail` AS `femail`,`billing`.`fbillpriceAll` AS `fbillpriceAll`,`billing`.`fpayPriceAll` AS `fpayPriceAll`,`billing`.`fdiscountAll` AS `fdiscountAll`,`billing`.`fdeliveryCode` AS `fdeliveryCode`,`billing`.`fcarrierPriceAll` AS `fcarrierPriceAll`,`FFFFCONVERTMILLADYTOGHAMARY`(`billing`.`inserttime`) AS `pinserttime`,`bd`.`fidproduct` AS `fidproduct`,`bd`.`id` AS `bd_id`,`bd`.`fprice` AS `fprice`,`bd`.`fstatus` AS `bd_fstatus`,`bd`.`fdesc` AS `bd_fdesc`,`bd`.`fextra` AS `bd_fextra`,`bd`.`fcount` AS `fcount`,`bd`.`fpriceUnit` AS `fpriceUnit`,`bd`.`fpriceAll` AS `fpriceAll`,`bd`.`fparent` AS `fparent`,`bd`.`ftype` AS `ftype`,`bd`.`fname` AS `bd_fname`,`bd`.`fidstore` AS `fidstore`,`bd`.`foff` AS `foff`,`bd`.`fimage` AS `fimage`,`bd`.`fValuevariabelDependenc1` AS `fValuevariabelDependenc1`,`bd`.`fValuevariabelDependenc2` AS `fValuevariabelDependenc2`,`bp`.`ftype` AS `bp_ftype`,`bp`.`fname` AS `bp_fname`,`bp`.`fdesc` AS `bp_fdesc`,`bp`.`factive` AS `bp_factive`,`bp`.`fextra` AS `bp_fextra`,`bp`.`fname` AS `paymethod`,`bd`.`fisService` AS `fisService`,`bd`.`fis_down` AS `fis_down`,`bp`.`ftype` AS `paymethod_ftype`,`RETURNBILLPRODUCTSIMAGE`(`billing`.`id`) AS `images`,(select `store_settings`.`fldval` from `store_settings` where `store_settings`.`fldkey` = 'STORE_AWAIT_PAYMENT_MIN') - floor(time_to_sec(timediff(current_timestamp(),`billing`.`inserttime`)) / 60) AS `timeractive` from ((`billing` join `billing_details` `bd` on(`billing`.`id` = `bd`.`fidbilling`)) left join `billpaymethods` `bp` on(`billing`.`fidbillpaymethods` = `bp`.`id`)) where `billing`.`fdeleted` = 0 and `bd`.`fdeleted` = 0 */;

--
-- Final view structure for view `mainbasket`
--

/*!50001 DROP VIEW IF EXISTS `mainbasket`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `mainbasket` AS select `b`.`idwebuser` AS `idwebuser`,`b`.`idproduct` AS `idproduct`,`b`.`idtempUser` AS `idtempUser`,`b`.`type` AS `type`,`b`.`idexternalRequest` AS `idexternalRequest`,`b`.`count` AS `count`,`b`.`id` AS `id_basket`,`p`.`id` AS `id`,`v`.`id` AS `ID_varient_product`,`p`.`fsku` AS `fsku`,`p`.`fzarib` AS `fzarib`,`p`.`fname` AS `fname`,`v`.`fprice` AS `fprice`,`v`.`fweight` AS `fweight`,`RETURN_PRODUCT_PRICEUNIT`(`v`.`id`,`v`.`fis_amazing`) AS `fpriceUnit`,`RETURN_PRODUCT_PRICEUNIT`(`v`.`id`,`v`.`fis_amazing`) * `b`.`count` AS `fpriceAll`,`v`.`fis_amazing` AS `fis_amazing`,`v`.`amazing_startDate` AS `amazing_startDate`,`v`.`amazing_endDate` AS `amazing_endDate`,`p`.`fimages` AS `fimages`,`RETURN_CARRIERNAMEBYID`(`p`.`fidCarrierId`) AS `CarriersData`,`p`.`fidCarrierId` AS `fidCarrierId`,`p`.`factive` AS `factive`,`v`.`foff` AS `foff`,`v`.`fronding` AS `fronding`,`v`.`amazing_foff` AS `amazing_foff`,`v`.`fdate_update_price` AS `fdate_update_price`,`p`.`fis_down` AS `fis_down`,`p`.`fis_virtual` AS `fis_virtual`,`p`.`fisService` AS `fisService`,`v`.`fis_Single_sale` AS `fis_Single_sale`,`p`.`fidCateg` AS `fidCateg`,`v`.`fvariabelDependenc1desc` AS `fvariabelDependenc1desc`,`v`.`fvariabelDependenc2desc` AS `fvariabelDependenc2desc`,`v`.`fmax_sell_quantity` AS `fmax_sell_quantity`,`p`.`fkeys` AS `fkeys`,`v`.`fis_freeShip` AS `fis_freeShip`,`v`.`fidproduct` AS `fparent`,`s`.`fname` AS `storeName`,`s`.`fimages` AS `storeLogo`,`s`.`furl` AS `storeUrl`,`s`.`frate` AS `storeRate`,`s`.`fcode` AS `storeCode`,`s`.`is_new` AS `storeis_new`,`s`.`is_official` AS `storeis_official`,`s`.`is_trusted` AS `storeis_trusted`,`v`.`fcanShappingByMainStore` AS `fcanShappingByMainStore`,`v`.`fManage_stock_quantity` AS `fManage_stock_quantity`,`v`.`fstock_quantity` AS `fstock_quantity`,`v`.`fcanShappingBySeller` AS `fcanShappingBySeller`,`v`.`fdaystoSendMainStore` AS `fdaystoSendMainStore`,`s`.`id` AS `storeId`,ifnull(concat(`p`.`furl`,'/varient?id=',`v`.`id`),'') AS `furl` from (((`buy_basket` `b` join `products_varients` `v` on(`v`.`id` = `b`.`idproduct`)) join `products` `p` on(`p`.`id` = `v`.`fidproduct`)) join `stores` `s` on(`s`.`id` = `v`.`fidstore`)) where `p`.`factive` = 1 and `v`.`factive` = 1 and `p`.`fdeleted` = 0 and `s`.`factive` = 1 and `s`.`fdeleted` = 0 and `v`.`fdeleted` = 0 */;

--
-- Final view structure for view `mainbasket_next`
--

/*!50001 DROP VIEW IF EXISTS `mainbasket_next`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `mainbasket_next` AS select `b`.`idwebuser` AS `idwebuser`,`b`.`idproduct` AS `idproduct`,`b`.`idtempUser` AS `idtempUser`,`b`.`type` AS `type`,`b`.`count` AS `count`,`b`.`id` AS `id_basket`,`p`.`id` AS `id`,`p`.`fsku` AS `fsku`,`p`.`fname` AS `fname`,`v`.`fprice` AS `fprice`,`RETURN_PRODUCT_PRICEUNIT`(`v`.`id`,`v`.`fis_amazing`) AS `fpriceUnit`,`RETURN_PRODUCT_PRICEUNIT`(`v`.`id`,`v`.`fis_amazing`) * `b`.`count` AS `fpriceAll`,`p`.`fimages` AS `fimages`,`p`.`factive` AS `factive`,`RETURN_CARRIERNAMEBYID`(`p`.`fidCarrierId`) AS `CarriersData`,`v`.`foff` AS `foff`,`v`.`fronding` AS `fronding`,`v`.`amazing_foff` AS `amazing_foff`,`v`.`fis_amazing` AS `fis_amazing`,`v`.`amazing_startDate` AS `amazing_startDate`,`v`.`amazing_endDate` AS `amazing_endDate`,`v`.`fdate_update_price` AS `fdate_update_price`,`p`.`fis_down` AS `fis_down`,`p`.`fzarib` AS `fzarib`,`p`.`fis_virtual` AS `fis_virtual`,`v`.`fis_Single_sale` AS `fis_Single_sale`,`v`.`fvariabelDependenc1desc` AS `fvariabelDependenc1desc`,`v`.`fvariabelDependenc2desc` AS `fvariabelDependenc2desc`,`p`.`fidCateg` AS `fidCateg`,`v`.`fmax_sell_quantity` AS `fmax_sell_quantity`,`p`.`fkeys` AS `fkeys`,`v`.`fis_freeShip` AS `fis_freeShip`,`v`.`fidproduct` AS `fparent`,`s`.`fname` AS `storeName`,`s`.`fimages` AS `storeLogo`,`s`.`furl` AS `storeUrl`,`s`.`frate` AS `storeRate`,`s`.`fcode` AS `storeCode`,`s`.`is_new` AS `storeis_new`,`s`.`is_official` AS `storeis_official`,`s`.`is_trusted` AS `storeis_trusted`,`s`.`id` AS `storeId`,ifnull(concat(`p`.`furl`,'/varient?id=',`v`.`id`),'') AS `furl` from (((`buy_basket_next` `b` join `products_varients` `v` on(`v`.`id` = `b`.`idproduct`)) join `products` `p` on(`p`.`id` = `v`.`fidproduct`)) join `stores` `s` on(`s`.`id` = `v`.`fidstore`)) where `p`.`factive` = 1 and `v`.`factive` = 1 and `p`.`fdeleted` = 0 and `s`.`factive` = 1 and `s`.`fdeleted` = 0 and `v`.`fdeleted` = 0 */;

--
-- Final view structure for view `oneproductattributes`
--

/*!50001 DROP VIEW IF EXISTS `oneproductattributes`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `oneproductattributes` AS select `pa`.`id` AS `paid`,`pa`.`idproduct` AS `paidproduct`,`pa`.`forder` AS `forder`,`pa`.`idatribute` AS `paidatribute`,`pa`.`fenable_variation` AS `fenable_variation`,`pa`.`fattribute_visibility` AS `fattribute_visibility`,`pa`.`fview_mode` AS `fview_mode`,case `pa`.`fenable_variation` when 1 then (select group_concat(`product_attribute_variant`.`fvalue` separator ',') from `product_attribute_variant` where `product_attribute_variant`.`fidproduct_attribute` = `pa`.`id` group by `product_attribute_variant`.`fidproduct_attribute`) else `pa`.`fvalue` end AS `pafvalue`,case `pa`.`fenable_variation` when 1 then (select json_arrayagg(json_object('id',`product_attribute_variant`.`id`,'fvalue',`product_attribute_variant`.`fvalue`,'fdesc',`product_attribute_variant`.`fdesc`)) from `product_attribute_variant` where `product_attribute_variant`.`fidproduct_attribute` = `pa`.`id`) else `pa`.`fvalue` end AS `jsonValue`,`paa`.`id` AS `paaid`,`paa`.`fkey` AS `paafkey`,`postscatag`.`fname` AS `category_name`,`postscatag`.`forder` AS `category_order`,`paa`.`factive` AS `paafactive`,`paa`.`ftype` AS `paaftype` from ((`product_attribute` `pa` join `product_allattribute` `paa` on(`paa`.`id` = `pa`.`idatribute`)) join `postscatag` on(`postscatag`.`id` = `paa`.`fidCateg`)) order by `postscatag`.`forder` */;

--
-- Final view structure for view `product_export_to_excel`
--

/*!50001 DROP VIEW IF EXISTS `product_export_to_excel`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `product_export_to_excel` AS select `p`.`id` AS `id`,`v`.`id` AS `varient_id`,`s`.`fcode` AS `storeCode`,`p`.`fname` AS `fname`,`v`.`fprice` AS `fprice`,`RETURN_PRODUCT_PRICEUNIT`(`v`.`id`,`v`.`fis_amazing`) AS `fpriceUnit`,`p`.`fdesc` AS `fdesc`,`p`.`fimages` AS `fimages`,`p`.`fctitle` AS `fctitle`,`p`.`finfo2` AS `finfo2`,`v`.`fcustomBuyBoxdesc` AS `fcustomBuyBoxdesc`,`p`.`flatiName` AS `flatiName`,`p`.`fzarib` AS `fzarib`,`p`.`factive` AS `factive`,`p`.`fpicAlt` AS `fpicAlt`,`v`.`factive` AS `V_factive`,`v`.`fis_freeShip` AS `fis_freeShip`,`v`.`fronding` AS `fronding`,`p`.`finfo1` AS `finfo1`,`v`.`foff` AS `foff`,`v`.`amazing_foff` AS `amazing_foff`,`p`.`furl` AS `furl`,`v`.`fdate_update_price` AS `fdate_update_price`,`p`.`fidCateg` AS `fidCateg`,`p`.`fkeys` AS `fkeys`,`p`.`fis_down` AS `fis_down`,`p`.`fis_virtual` AS `fis_virtual`,`v`.`fis_Single_sale` AS `fis_Single_sale`,`v`.`flow_stock_amount` AS `flow_stock_amount`,`v`.`fdaystoSendMainStore` AS `fdaystoSendMainStore`,`v`.`fcanShappingByMainStore` AS `fcanShappingByMainStore`,`v`.`fdaystoSendCustomerByMainStore` AS `fdaystoSendCustomerByMainStore`,`v`.`fdaystoSendCustomerBySeller` AS `fdaystoSendCustomerBySeller`,`v`.`finMainStoreStock` AS `finMainStoreStock`,`v`.`fcanShappingBySeller` AS `fcanShappingBySeller`,`v`.`fvariabelDependenc1desc` AS `fvariabelDependenc1desc`,`v`.`fvariabelDependenc2desc` AS `fvariabelDependenc2desc`,`v`.`fmax_sell_quantity` AS `fmax_sell_quantity`,`p`.`fsku` AS `fsku`,`v`.`fManage_stock_quantity` AS `fManage_stock_quantity`,case ifnull(`v`.`fManage_stock_quantity`,0) when 0 then 0 else (select `RETURNSTOCK_QUANTITY`(`p`.`id`,`v`.`fstock_quantity`) AS `b`) end AS `fstock_quantity`,`v`.`fis_soon` AS `fis_soon`,`v`.`fis_amazing` AS `fis_amazing`,`p`.`fid_brand` AS `fid_brand`,`RETURN_PRODUCT_COLORS`(`p`.`id`) AS `ProductColors` from ((`products` `p` join `products_varients` `v` on(`v`.`fidproduct` = `p`.`id`)) join `stores` `s` on(`s`.`id` = `v`.`fidstore`)) where ifnull(`p`.`fhidden`,0) = 0 order by `v`.`id` desc */;

--
-- Final view structure for view `product_mini`
--

/*!50001 DROP VIEW IF EXISTS `product_mini`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `product_mini` AS select `p`.`id` AS `id`,`p`.`fseen` AS `fseen`,`v`.`id` AS `varient_id`,`s`.`id` AS `storeId`,`s`.`fcode` AS `storeCode`,`p`.`fname` AS `fname`,`v`.`fprice` AS `fprice`,`RETURN_PRODUCT_PRICEUNIT`(`v`.`id`,`v`.`fis_amazing`) AS `fpriceUnit`,`p`.`fdesc` AS `fdesc`,`p`.`fdefult_variabels` AS `fdefult_variabels`,`p`.`fimages` AS `fimages`,`p`.`frate` AS `frate`,`p`.`fctitle` AS `fctitle`,`p`.`fzarib` AS `fzarib`,`p`.`fpicAlt` AS `fpicAlt`,`p`.`fcomments` AS `fcomments`,`v`.`fsellcount` AS `fsellcount`,`v`.`fronding` AS `fronding`,`p`.`fsuggests` AS `fsuggests`,`p`.`finfo2` AS `finfo2`,`v`.`fcustomBuyBoxdesc` AS `fcustomBuyBoxdesc`,`p`.`flatiName` AS `flatiName`,`p`.`factive` AS `factive`,`v`.`factive` AS `V_factive`,`v`.`fis_freeShip` AS `fis_freeShip`,`p`.`finfo1` AS `finfo1`,`v`.`foff` AS `foff`,`v`.`amazing_foff` AS `amazing_foff`,`p`.`furl` AS `furl`,`v`.`fdate_update_price` AS `fdate_update_price`,`p`.`fidCateg` AS `fidCateg`,`p`.`fkeys` AS `fkeys`,`p`.`fis_down` AS `fis_down`,`p`.`fis_virtual` AS `fis_virtual`,`v`.`fis_Single_sale` AS `fis_Single_sale`,`v`.`flow_stock_amount` AS `flow_stock_amount`,`v`.`fweight` AS `fweight`,`v`.`flength` AS `flength`,`v`.`fdaystoSendMainStore` AS `fdaystoSendMainStore`,`v`.`fcanShappingByMainStore` AS `fcanShappingByMainStore`,`v`.`fdaystoSendCustomerByMainStore` AS `fdaystoSendCustomerByMainStore`,`v`.`fdaystoSendCustomerBySeller` AS `fdaystoSendCustomerBySeller`,`v`.`finMainStoreStock` AS `finMainStoreStock`,`v`.`fcanShappingBySeller` AS `fcanShappingBySeller`,`p`.`flayout` AS `flayout`,`v`.`fwidth` AS `fwidth`,`v`.`fvariabelDependenc1desc` AS `fvariabelDependenc1desc`,`v`.`fheight` AS `fheight`,`v`.`fvariabelDependenc2desc` AS `fvariabelDependenc2desc`,`v`.`fmax_sell_quantity` AS `fmax_sell_quantity`,`p`.`fsku` AS `fsku`,`v`.`fManage_stock_quantity` AS `fManage_stock_quantity`,case ifnull(`v`.`fManage_stock_quantity`,0) when 0 then 0 else (select `RETURNSTOCK_QUANTITY`(`p`.`id`,`v`.`fstock_quantity`) AS `b`) end AS `fstock_quantity`,`v`.`fis_soon` AS `fis_soon`,`v`.`fis_amazing` AS `fis_amazing`,`p`.`fid_brand` AS `fid_brand`,`RETURN_CARRIERNAMEBYID`(`p`.`fidCarrierId`) AS `CarriersData`,`RETURN_PRODUCT_COLORS`(`p`.`id`) AS `ProductColors`,`FFFFCONVERTMILLADYTOGHAMARY_BYNULL`(`v`.`amazing_startDate`) AS `amazing_startDate_fa`,`FFFFCONVERTMILLADYTOGHAMARY_BYNULL`(`v`.`amazing_endDate`) AS `amazing_endDate_fa`,`v`.`amazing_startDate` AS `amazing_startDate`,`v`.`amazing_endDate` AS `amazing_endDate`,`v`.`amazing_startTime` AS `amazing_startTime`,`v`.`amazing_endTime` AS `amazing_endTime` from ((`products` `p` join `products_varients` `v` on(`v`.`fidproduct` = `p`.`id`)) join `stores` `s` on(`s`.`id` = `v`.`fidstore`)) where ifnull(`p`.`fhidden`,0) = 0 and `p`.`fdeleted` = 0 and `v`.`fdeleted` = 0 and `v`.`factive` = 1 and `p`.`fdefult_variabels` = `v`.`id` and `p`.`factive` = 1 and (`v`.`fcanShappingByMainStore` = 1 or `v`.`fcanShappingBySeller` = 1) order by `v`.`id` desc */;

--
-- Final view structure for view `product_mini_new`
--

/*!50001 DROP VIEW IF EXISTS `product_mini_new`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `product_mini_new` AS select `p`.`id` AS `id`,`p`.`fseen` AS `fseen`,`v`.`id` AS `varient_id`,`s`.`id` AS `storeId`,`s`.`fcode` AS `storeCode`,`p`.`fname` AS `fname`,`v`.`fprice` AS `fprice`,`p`.`fdesc` AS `fdesc`,`p`.`fdefult_variabels` AS `fdefult_variabels`,`p`.`fimages` AS `fimages`,`p`.`frate` AS `frate`,`p`.`fctitle` AS `fctitle`,`p`.`fzarib` AS `fzarib`,`p`.`fpicAlt` AS `fpicAlt`,`p`.`fcomments` AS `fcomments`,`v`.`fsellcount` AS `fsellcount`,`v`.`fronding` AS `fronding`,`p`.`fsuggests` AS `fsuggests`,`p`.`finfo2` AS `finfo2`,`v`.`fcustomBuyBoxdesc` AS `fcustomBuyBoxdesc`,`p`.`flatiName` AS `flatiName`,`p`.`factive` AS `factive`,`v`.`factive` AS `V_factive`,`v`.`fis_freeShip` AS `fis_freeShip`,`p`.`finfo1` AS `finfo1`,`v`.`foff` AS `foff`,`v`.`amazing_foff` AS `amazing_foff`,`p`.`furl` AS `furl`,`v`.`fdate_update_price` AS `fdate_update_price`,`p`.`fidCateg` AS `fidCateg`,`p`.`fkeys` AS `fkeys`,`p`.`fis_down` AS `fis_down`,`p`.`fis_virtual` AS `fis_virtual`,`v`.`fis_Single_sale` AS `fis_Single_sale`,`v`.`flow_stock_amount` AS `flow_stock_amount`,`v`.`fweight` AS `fweight`,`v`.`flength` AS `flength`,`v`.`fdaystoSendMainStore` AS `fdaystoSendMainStore`,`v`.`fcanShappingByMainStore` AS `fcanShappingByMainStore`,`v`.`fdaystoSendCustomerByMainStore` AS `fdaystoSendCustomerByMainStore`,`v`.`fdaystoSendCustomerBySeller` AS `fdaystoSendCustomerBySeller`,`v`.`finMainStoreStock` AS `finMainStoreStock`,`v`.`fcanShappingBySeller` AS `fcanShappingBySeller`,`p`.`flayout` AS `flayout`,`v`.`fwidth` AS `fwidth`,`v`.`fvariabelDependenc1desc` AS `fvariabelDependenc1desc`,`v`.`fheight` AS `fheight`,`v`.`fvariabelDependenc2desc` AS `fvariabelDependenc2desc`,`v`.`fmax_sell_quantity` AS `fmax_sell_quantity`,`p`.`fsku` AS `fsku`,`v`.`fManage_stock_quantity` AS `fManage_stock_quantity`,case ifnull(`v`.`fManage_stock_quantity`,0) when 0 then 0 else (select `RETURNSTOCK_QUANTITY`(`p`.`id`,`v`.`fstock_quantity`) AS `b`) end AS `fstock_quantity`,`v`.`fis_soon` AS `fis_soon`,`v`.`fis_amazing` AS `fis_amazing`,`p`.`fid_brand` AS `fid_brand`,`RETURN_PRODUCT_PRICEUNIT`(`v`.`id`,`v`.`fis_amazing`) AS `fpriceUnit`,`p`.`fidCarrierId` AS `fidCarrierId`,`FFFFCONVERTMILLADYTOGHAMARY_BYNULL`(`v`.`amazing_startDate`) AS `amazing_startDate_fa`,`FFFFCONVERTMILLADYTOGHAMARY_BYNULL`(`v`.`amazing_endDate`) AS `amazing_endDate_fa`,`v`.`amazing_startDate` AS `amazing_startDate`,`v`.`amazing_endDate` AS `amazing_endDate`,`v`.`amazing_startTime` AS `amazing_startTime`,`v`.`amazing_endTime` AS `amazing_endTime` from ((`products` `p` join `products_varients` `v` on(`v`.`fidproduct` = `p`.`id`)) join `stores` `s` on(`s`.`id` = `v`.`fidstore`)) where ifnull(`p`.`fhidden`,0) = 0 and `p`.`fdeleted` = 0 and `v`.`fdeleted` = 0 and `v`.`factive` = 1 and `p`.`fdefult_variabels` = `v`.`id` and `p`.`factive` = 1 and (`v`.`fcanShappingByMainStore` = 1 or `v`.`fcanShappingBySeller` = 1) order by `v`.`id` desc */;

--
-- Final view structure for view `productview`
--

/*!50001 DROP VIEW IF EXISTS `productview`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `productview` AS select case ifnull(`p`.`fid_brand`,0) when 0 then 'متفرقه' else (select ifnull(`product_brand`.`fname`,'متفرقه') AS `b` from `product_brand` where `product_brand`.`id` = `p`.`fid_brand` and `product_brand`.`factive` = 1) end AS `brand`,`RETURN_CARRIERNAMEBYID`(`p`.`fidCarrierId`) AS `CarriersData`,case ifnull(`p`.`fidCateg`,0) when 0 then '' else (select ifnull(`postscatag`.`fname`,'') AS `b` from `postscatag` where `postscatag`.`id` = `p`.`fidCateg` and `postscatag`.`factive` = 1) end AS `Categ`,`p`.`id` AS `id`,`p`.`fzarib` AS `fzarib`,`RETURN_PRODUCT_IN_ORDER_COUNT`(`p`.`id`) AS `product_in_order_count`,`RETURN_PRODUCT_SELL_COUNT`(`p`.`id`) AS `product_sell_count`,`p`.`fname` AS `fname`,`p`.`flatiName` AS `flatiName`,`v`.`fprice` AS `fprice`,`p`.`frate` AS `frate`,`p`.`fcomments` AS `fcomments`,`p`.`fsuggests` AS `fsuggests`,`v`.`fsellcount` AS `fsellcount`,`v`.`fronding` AS `fronding`,`p`.`fctitle` AS `fctitle`,`p`.`fpicAlt` AS `fpicAlt`,`p`.`fdesc` AS `fdesc`,`p`.`fcomissionP` AS `fcomissionP`,`v`.`fis_freeShip` AS `fis_freeShip`,`v`.`fgarantidesc` AS `fgarantidesc`,`p`.`finfo1` AS `finfo1`,`p`.`finfo2` AS `finfo2`,`p`.`fimages` AS `fimages`,`p`.`flayout` AS `flayout`,`p`.`factive` AS `factive`,`v`.`foff` AS `foff`,`v`.`amazing_foff` AS `amazing_foff`,`v`.`fdate_update_price` AS `fdate_update_price`,`p`.`finserttime` AS `finserttime`,`p`.`fdeleted` AS `fdeleted`,`p`.`fidCateg` AS `fidCateg`,`p`.`fkeys` AS `fkeys`,`p`.`furl` AS `furl`,`v`.`ftype` AS `ftype`,`p`.`fis_down` AS `fis_down`,`p`.`fis_virtual` AS `fis_virtual`,`v`.`fcustomBuyBoxdesc` AS `fcustomBuyBoxdesc`,`v`.`fis_soon` AS `fis_soon`,`v`.`fis_amazing` AS `fis_amazing`,`v`.`fis_Single_sale` AS `fis_Single_sale`,`v`.`amazing_startDate` AS `amazing_startDate`,`v`.`amazing_endDate` AS `amazing_endDate`,`FFFFCONVERTMILLADYTOGHAMARY_BYNULL`(`v`.`amazing_startDate`) AS `amazing_startDate_fa`,`FFFFCONVERTMILLADYTOGHAMARY_BYNULL`(`v`.`amazing_endDate`) AS `amazing_endDate_fa`,`v`.`amazing_startTime` AS `amazing_startTime`,`v`.`amazing_endTime` AS `amazing_endTime`,`v`.`fManage_stock_quantity` AS `fManage_stock_quantity`,`v`.`fstock_quantity` AS `fstock_quantity`,`v`.`flow_stock_amount` AS `flow_stock_amount`,`v`.`fweight` AS `fweight`,`v`.`flength` AS `flength`,`v`.`fwidth` AS `fwidth`,`v`.`fheight` AS `fheight`,`v`.`fmax_sell_quantity` AS `fmax_sell_quantity`,`p`.`fsku` AS `fsku`,`v`.`id` AS `varient_id`,`RETURN_PRODUCT_COLORS`(`p`.`id`) AS `ProductColors`,`v`.`fvariabelDependenc1` AS `fvariabelDependenc1`,`v`.`fvariabelDependenc2` AS `fvariabelDependenc2`,`p`.`fdefult_variabels` AS `fdefult_variabels`,`p`.`fid_brand` AS `fid_brand` from (`products` `p` join `products_varients` `v` on(`v`.`fidproduct` = `p`.`id`)) where `p`.`fdeleted` = 0 and `v`.`fdeleted` = 0 and `v`.`factive` = 1 and `p`.`factive` = 1 and (`v`.`fcanShappingByMainStore` = 1 or `v`.`fcanShappingBySeller` = 1) order by `v`.`id` desc */;

--
-- Final view structure for view `productview_fullhtml`
--

/*!50001 DROP VIEW IF EXISTS `productview_fullhtml`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `productview_fullhtml` AS select case ifnull(`p`.`fid_brand`,0) when 0 then 'متفرقه' else (select ifnull(`product_brand`.`fname`,'متفرقه') AS `b` from `product_brand` where `product_brand`.`id` = `p`.`fid_brand` and `product_brand`.`factive` = 1) end AS `brand`,`RETURN_CARRIERNAMEBYID`(`p`.`fidCarrierId`) AS `CarriersData`,case ifnull(`p`.`fidCateg`,0) when 0 then '' else (select ifnull(`postscatag`.`fname`,'') AS `b` from `postscatag` where `postscatag`.`id` = `p`.`fidCateg` and `postscatag`.`factive` = 1) end AS `Categ`,`p`.`id` AS `id`,`RETURN_PRODUCT_IN_ORDER_COUNT`(`p`.`id`) AS `product_in_order_count`,`RETURN_PRODUCT_SELL_COUNT`(`p`.`id`) AS `product_sell_count`,`RETURN_CATEGURY_PARENTS_STR`(`p`.`fidCateg`) AS `cataglevels`,`p`.`fname` AS `fname`,`p`.`flatiName` AS `flatiName`,`v`.`fprice` AS `fprice`,`p`.`fdesc` AS `fdesc`,`p`.`fctitle` AS `fctitle`,`p`.`fpicAlt` AS `fpicAlt`,`v`.`fis_freeShip` AS `fis_freeShip`,`v`.`fgarantidesc` AS `fgarantidesc`,`v`.`fronding` AS `fronding`,`v`.`fcustomBuyBoxdesc` AS `fcustomBuyBoxdesc`,`p`.`finfo1` AS `finfo1`,`p`.`finfo2` AS `finfo2`,`p`.`fimages` AS `fimages`,`p`.`fzarib` AS `fzarib`,`p`.`fcomissionP` AS `fcomissionP`,`p`.`factive` AS `factive`,`v`.`foff` AS `foff`,`v`.`amazing_foff` AS `amazing_foff`,`p`.`frate` AS `frate`,`p`.`fcomments` AS `fcomments`,`v`.`fsellcount` AS `fsellcount`,`p`.`fsuggests` AS `fsuggests`,`v`.`fdate_update_price` AS `fdate_update_price`,`v`.`finserttime` AS `finserttime`,`p`.`fdeleted` AS `fdeleted`,`p`.`fidCateg` AS `fidCateg`,`p`.`fkeys` AS `fkeys`,`p`.`furl` AS `furl`,`p`.`fhtml` AS `fhtml`,`p`.`fscript` AS `fscript`,`v`.`ftype` AS `ftype`,`p`.`flayout` AS `flayout`,`p`.`fis_down` AS `fis_down`,`p`.`fis_virtual` AS `fis_virtual`,`v`.`fis_soon` AS `fis_soon`,`v`.`fis_amazing` AS `fis_amazing`,`v`.`fis_Single_sale` AS `fis_Single_sale`,`v`.`amazing_startDate` AS `amazing_startDate`,`FFFFCONVERTMILLADYTOGHAMARY_BYNULL`(`v`.`amazing_startDate`) AS `amazing_startDate_fa`,`FFFFCONVERTMILLADYTOGHAMARY_BYNULL`(`v`.`amazing_endDate`) AS `amazing_endDate_fa`,`v`.`amazing_endDate` AS `amazing_endDate`,`v`.`amazing_startTime` AS `amazing_startTime`,`v`.`amazing_endTime` AS `amazing_endTime`,`v`.`fManage_stock_quantity` AS `fManage_stock_quantity`,`v`.`fstock_quantity` AS `fstock_quantity`,`v`.`flow_stock_amount` AS `flow_stock_amount`,`v`.`fweight` AS `fweight`,`v`.`flength` AS `flength`,`v`.`fwidth` AS `fwidth`,`v`.`fheight` AS `fheight`,`v`.`fmax_sell_quantity` AS `fmax_sell_quantity`,`p`.`fsku` AS `fsku`,`v`.`id` AS `varient_id`,`v`.`fvariabelDependenc1` AS `fvariabelDependenc1`,`v`.`fvariabelDependenc2` AS `fvariabelDependenc2`,`p`.`fdefult_variabels` AS `fdefult_variabels`,`p`.`fid_brand` AS `fid_brand`,`RETURN_BRANDIMAGEBYID`(`p`.`fid_brand`) AS `brandImage` from (`products` `p` join `products_varients` `v` on(`v`.`fidproduct` = `p`.`id`)) where `p`.`fdeleted` = 0 and `v`.`fdeleted` = 0 and `v`.`factive` = 1 and `p`.`factive` = 1 order by `v`.`id` desc */;

--
-- Final view structure for view `service_view`
--

/*!50001 DROP VIEW IF EXISTS `service_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `service_view` AS select `s`.`id` AS `id`,`s`.`fidbilling_details` AS `fidbilling_details`,`s`.`fdesc` AS `fdesc`,`s`.`fstatus` AS `fstatus`,`s`.`ftype` AS `ftype`,`s`.`inserttime` AS `inserttime`,`s`.`fextra` AS `fextra`,`s`.`fdeleted` AS `fdeleted`,`s`.`fexpireDate` AS `fexpireDate`,`p`.`fname` AS `fname`,`p`.`fname_en` AS `fname_en`,`p`.`fimages` AS `fimages`,`p`.`id` AS `fidproduct`,`b`.`fidwebuser` AS `fidwebuser`,concat(`b`.`fname`,' ',`b`.`flastname`) AS `username` from (((`services` `s` join `billing_details` `d` on(`d`.`id` = `s`.`fidbilling_details`)) join `products` `p` on(`p`.`id` = `d`.`fidproduct`)) join `billing` `b` on(`b`.`id` = `d`.`fidbilling`)) */;

--
-- Final view structure for view `store_varient_product_info`
--

/*!50001 DROP VIEW IF EXISTS `store_varient_product_info`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013  SQL SECURITY DEFINER */
/*!50001 VIEW `store_varient_product_info` AS select `pv`.`id` AS `id`,`pv`.`id` AS `ID_varient_product`,`pv`.`fgarantidesc` AS `fgarantidesc`,`pv`.`fidproduct` AS `fidproduct`,`pv`.`fidstore` AS `fidstore`,`pv`.`fprice` AS `fprice`,`pv`.`factive` AS `factive`,`pv`.`foff` AS `foff`,`pv`.`fdate_update_price` AS `fdate_update_price`,`pv`.`fdeleted` AS `fdeleted`,`pv`.`fis_amazing` AS `fis_amazing`,`pv`.`fis_Single_sale` AS `fis_Single_sale`,`pv`.`amazing_startDate` AS `amazing_startDate`,`pv`.`fcustomBuyBoxdesc` AS `fcustomBuyBoxdesc`,`pv`.`amazing_endDate` AS `amazing_endDate`,`pv`.`amazing_startTime` AS `amazing_startTime`,`pv`.`amazing_endTime` AS `amazing_endTime`,`pv`.`fManage_stock_quantity` AS `fManage_stock_quantity`,`pv`.`fstock_quantity` AS `fstock_quantity`,`pv`.`flow_stock_amount` AS `flow_stock_amount`,`pv`.`fweight` AS `fweight`,`pv`.`flength` AS `flength`,`pv`.`fis_soon` AS `fis_soon`,`pv`.`fwidth` AS `fwidth`,`pv`.`fheight` AS `fheight`,`pv`.`fmax_sell_quantity` AS `fmax_sell_quantity`,`pv`.`fvariabelDependenc1` AS `fvariabelDependenc1`,`pv`.`fvariabelDependenc2` AS `fvariabelDependenc2`,`pv`.`fvariabelDependenc1desc` AS `fvariabelDependenc1desc`,`pv`.`fvariabelDependenc2desc` AS `fvariabelDependenc2desc`,`pv`.`finfo1` AS `finfo1`,`pv`.`finfo2` AS `finfo2`,`pv`.`fsellcount` AS `fsellcount`,`pv`.`fdown_extra` AS `fdown_extra`,`pv`.`amazing_foff` AS `amazing_foff`,`pv`.`fis_freeShip` AS `fis_freeShip`,`pv`.`fcanShappingByMainStore` AS `fcanShappingByMainStore`,`pv`.`fcanShappingBySeller` AS `fcanShappingBySeller`,`pv`.`fdaystoSendMainStore` AS `fdaystoSendMainStore`,`pv`.`finMainStoreStock` AS `finMainStoreStock`,`pv`.`fdaystoSendCustomerByMainStore` AS `fdaystoSendCustomerByMainStore`,`pv`.`fdaystoSendCustomerBySeller` AS `fdaystoSendCustomerBySeller`,`pv`.`ftype` AS `ftype`,`pv`.`frate` AS `frate`,`pv`.`fsuggests` AS `fsuggests`,`pv`.`fcomments` AS `fcomments`,`pv`.`fronding` AS `fronding`,`s`.`fname` AS `storeName`,`s`.`fimages` AS `storeLogo`,`s`.`furl` AS `storeUrl`,`s`.`fcode` AS `storeCode`,`s`.`frate` AS `storeRate`,`s`.`fcomments` AS `storeComments`,`s`.`fsuggests` AS `storeSuggests`,`s`.`fimages` AS `storeImages`,`p`.`fzarib` AS `fzarib`,`RETURN_CARRIERNAMEBYID`(`p`.`fidCarrierId`) AS `CarriersData`,`s`.`on_time_shipping` AS `store_on_time_shipping`,`s`.`on_commitment` AS `store_on_commitment`,`s`.`on_no_return` AS `store_on_no_return`,`s`.`finserttime` AS `store_Inserttime`,`s`.`is_new` AS `storeis_new`,`s`.`is_official` AS `storeis_official`,`s`.`is_trusted` AS `storeis_trusted`,`RETURNproducts_varients_extra`(`pv`.`id`) AS `products_varients_extra`,`RETURN_PRODUCT_PRICEUNIT`(`pv`.`id`,`pv`.`fis_amazing`) AS `fpriceUnit`,`FFFFCONVERTMILLADYTOGHAMARY_BYNULL`(`pv`.`amazing_startDate`) AS `amazing_startDate_fa`,`FFFFCONVERTMILLADYTOGHAMARY_BYNULL`(`pv`.`amazing_endDate`) AS `amazing_endDate_fa` from ((`products_varients` `pv` join `stores` `s` on(`pv`.`fidstore` = `s`.`id`)) join `products` `p` on(`p`.`id` = `pv`.`fidproduct`)) where `pv`.`fdeleted` = 0 and `pv`.`factive` = 1 and `s`.`fdeleted` = 0 and `s`.`factive` = 1 and (`pv`.`fcanShappingByMainStore` = 1 or `pv`.`fcanShappingBySeller` = 1) */;


