
--
-- Dumping routines for database 'ebillingdtls'
--
/*!50003 DROP PROCEDURE IF EXISTS `sp_AddtoCart_OneProduct` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_AddtoCart_OneProduct`(IN `intProductID` INT, IN `intCustomerID` BIGINT, IN `intOrderID` BIGINT, IN `intQty` INT, IN `intUserID` INT)
BEGIN

DECLARE intStoreID,intProdCat,intProdSubCat,intPseudoStock,isOrderDetailsPresent,isProductDetailsPresent INT;
DECLARE fProdOfferPrice,fProdClubPrice,fIGSTPer,fCGSTPer,fSGSTPer float;
DECLARE fPriceP,fRate,fCGST,fSGST,fIGST,fperPrice FLOAT;
DECLARE isCustomerClubMember VARCHAR(10);
-- DECLARE EXIT HANDLER FOR sqlexception SELECT 'ERROR - ' Message;
SET intStoreID = 0; SET intProdCat = 0; SET intProdSubCat = 0;set intPseudoStock = 0; set isOrderDetailsPresent =0; SET isProductDetailsPresent =0;
SET fProdOfferPrice = 0;SET fProdClubPrice = 0;SET fIGSTPer = 0;SET fCGSTPer = 0;SET fSGSTPer = 0;
SET fPriceP =0; set fRate = 0; set fCGST = 0; set fSGST =0; set fIGST = 0;set fperPrice =0;

select adm_store INTO intStoreID from karloff_admin where id = intUserID;

/* INSERT INTO karloff_db_log(logdtls) 
VALUES(CONCAT(intProductID,';',intCustomerID,';',intOrderID,';',intQty,';',intUserID));*/

select PseudoStock,ProductCategory,ProductSubcategoryL1 into intPseudoStock,intProdCat,intProdSubCat	
from karloff_productdb where id = intProductID;
-- INSERT INTO karloff_db_log(logdtls) VALUES('Select product details');
 CALL sp_getProduct_pricedetails(intProductID,0,intCustomerID,intQty,fperPrice,fRate,fSGST,fCGST,fIGST,fPriceP);
/* INSERT INTO karloff_db_log(logdtls) 
 VALUES(CONCAT('QTY -',intQty,'; PER PRICE -',fperPrice,'; RATE-',fRate,'; FINAL PRICE - ',fPriceP));*/

SELECT Count(ID) into isProductDetailsPresent FROM karloff_weborder_details WHERE order_id = intOrderID and product_id = intProductID;

IF (isProductDetailsPresent > 0) THEN

 UPDATE karloff_weborder_details SET BaseValue = (BaseValue + fRate),CgstAmount = (CgstAmount + fCGST),SgstAmount = (SgstAmount + fSGST),
 IgstAmount = (IgstAmount + fIGST), qty = (qty + intQty),net_amount = (net_amount+ fPriceP)  
  WHERE order_id = intOrderID and product_id = intProductID;

ELSE
 INSERT INTO `karloff_weborder_details`(`order_id`,`customer_id`,`product_id`,`product_category_id`,`product_subcategory_id`,`BaseValue`,`CgstAmount`,
`SgstAmount`,`IgstAmount`,`qty`,`net_amount`,`updated_at`,`created_at`)
VALUES(intOrderID,intCustomerID,intProductID,intProdCat,intProdSubCat,fRate,fCGST,fSGST,fIGST,intQty,fPriceP,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP());

END IF;


SELECT Count(ID) into isOrderDetailsPresent FROM karloff_weborder WHERE order_id = intOrderID;

	IF (isOrderDetailsPresent > 0) THEN
				
		update karloff_weborder set BaseValue = (BaseValue + fRate),CgstAmount = (CgstAmount + fCGST), 
		SgstAmount = (SgstAmount + fSGST),IgstAmount = (IgstAmount + fIGST),InvoiceAmount = (InvoiceAmount + fPriceP),
		NetAmount = (NetAmount + fPriceP)
		where order_id = intOrderID;
		
		
	ELSE
       INSERT INTO `karloff_weborder`(`storeId`,`order_id`,`customer_id`,`BaseValue`,`CgstAmount`,`SgstAmount`,`IgstAmount`,`InvoiceAmount`,
		`NetAmount`,`order_status`,`order_date`,`updated_at`,`created_at`)
		VALUES(intStoreID,intOrderID,intCustomerID,fRate,fCGST,fSGST,fIGST,fPriceP,fPriceP,7,Current_Date(),CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP());
	END IF;


 CALL sp_UpdatePsudoProductStock(intProductID,intQty,intOrderID,1);

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_AddtoCart_OneProduct_main` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_AddtoCart_OneProduct_main`(IN `intProductID` INT, IN `intCustomerID` VARCHAR(200), IN `intOrderID` VARCHAR(200), IN `intQty` INT, IN `intUserID` INT)
BEGIN

call sp_AddtoCart_OneProduct(intProductID,intCustomerID,intOrderID,intQty,intUserID);

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_Cupon_Applydetails` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_Cupon_Applydetails`(IN strOrderID VARCHAR(200),
IN strCuponDTLS VARCHAR(200),
OUT strOutPutDTLS varchar(500))
BEGIN
 DECLARE IsPromocodePresent,IsValidPromocode,IsCuponAlreadyApplied,intProductID,intCouponAmount,isProdPresent INT;
 DECLARE intSubCategoryID,intProductValue,isCashBackDiscountApplied INT;
 DECLARE intCustomerID LONG;
 DECLARE strDiscountType,strDiscountAppliedOn VARCHAR(50);
DECLARE totalSaleValue NUMERIC(10,2);
-- DECLARE EXIT HANDLER FOR sqlexception SELECT 'ERROR - ' Message;

 SET strDiscountType = ''; SET strDiscountAppliedOn = '';
 SET IsPromocodePresent = 0; SET IsValidPromocode =0; SET intCustomerID =0; set IsCuponAlreadyApplied =0;
 SET intProductID =0; SET intCouponAmount =0; SET intSubCategoryID =0; SET intProductValue = 0;
 SET totalSaleValue = 0; SET isCashBackDiscountApplied =0; SET isProdPresent=0;
 
 -- INSERT INTO karloff_db_log(logdtls) VALUES(CONCAT('order id -',strOrderID,';cupon -',strCuponDTLS));
 
 -- Check Promocode is present or not
 SELECT COUNT(ID)  INTO IsPromocodePresent FROM karloff_promo_code WHERE PromoCode = strCuponDTLS;
  IF (IsPromocodePresent > 0) THEN
  -- Check Promocode valid for today(DATE)
    SELECT COUNT(ID)  INTO IsValidPromocode FROM karloff_promo_code WHERE PromoCode = strCuponDTLS AND (CURRENT_TIMESTAMP BETWEEN ValidFrom AND ValidTill);
       IF (IsValidPromocode > 0) THEN       
	   
	   select customer_id into intCustomerID from karloff_weborder where order_id = strOrderID;
	   -- Check Already Applied or not
	  -- select COUNT(ID) INTO IsCuponAlreadyApplied from karloff_promocode_applied WHERE customer_id=intCustomerID AND coupon_applied = strCuponDTLS;
      select COUNT(ID) INTO IsCuponAlreadyApplied from karloff_weborder_details WHERE customer_id=intCustomerID AND (CouponCode = strCuponDTLS OR CashbackCode = strCuponDTLS) AND order_id <> strOrderID;
		IF (IsCuponAlreadyApplied = 0) THEN
	   -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++	   
	    SELECT DiscountType,CouponAmount,ProductId,ProductValue,SubCategoryId  
		INTO strDiscountType,intCouponAmount,intProductID,intProductValue,intSubCategoryID
		FROM karloff_promo_code WHERE PromoCode = strCuponDTLS; 
		-- ++++++++++++++++++++++++++++++++++++++
		/* SELECT BaseValue,CgstAmount,SgstAmount,IgstAmount,qty,net_amount FROM karloff_weborder_details WHERE qty>1;
		
		SELECT ROUND(SUM(BaseValue),2) AS baseval,ROUND(SUM(CgstAmount),2) AS cgstval,ROUND(SUM(SgstAmount),2) AS sgstval,
ROUND(SUM(IgstAmount),2) AS igstval,ROUND(SUM(BaseValue+CgstAmount+SgstAmount+IgstAmount),2) AS totalval
 FROM karloff_weborder_details WHERE order_id = 2020062617294640;
 */
  --  check 1)Product Cashback 2)Product Promocode Discount
 SELECT ROUND(SUM(BaseValue+CgstAmount+SgstAmount+IgstAmount),2) into totalSaleValue
 FROM karloff_weborder_details WHERE order_id = strOrderID and product_id = intProductID;
		-- ========================================
		SET isCashBackDiscountApplied = 0;
		IF (intProductValue <= totalSaleValue) THEN		 
		  SET isCashBackDiscountApplied = 1; SET strDiscountAppliedOn ='PRODUCTID';
          SET isProdPresent = 1;
        ELSEIF (intProductValue > totalSaleValue) THEN
            SET isProdPresent = 1;
            SET strOutPutDTLS = CONCAT('ERROR: Required cart amount ',intProductValue,' for ',(select ProductName from karloff_productdb where id = intProductID),' product!');	
		ELSE
		 IF (totalSaleValue = 0 OR totalSaleValue is NULL) THEN
		   SET strOutPutDTLS = 'ERROR: Cupon related product is not present!'; 
		 END IF;  
		END IF;
		
		 --  check 3)Sub-Category Cashback 4) Subcategory Promocode Discount
	   IF(isCashBackDiscountApplied = 0 AND isProdPresent = 0) THEN		
	     SELECT ROUND(SUM(BaseValue+CgstAmount+SgstAmount+IgstAmount),2) into totalSaleValue
         FROM karloff_weborder_details WHERE order_id = strOrderID and product_subcategory_id = intSubCategoryID;
	     IF (intProductValue <= totalSaleValue) THEN
		   SET isCashBackDiscountApplied = 1;	SET strDiscountAppliedOn ='PRODSUBCATID';	
		ELSE
			 IF totalSaleValue = 0 OR totalSaleValue is NULL THEN
			   SET strOutPutDTLS = CONCAT('ERROR: Cupon related product is not present!');	
			 END IF; 
		 END IF;
	    END IF;	  
		
		-- Check Applicable CUPON/DISCOUNT
        IF(isCashBackDiscountApplied = 1) THEN		
		 IF (strDiscountType = 'CASHBACK') THEN		  
			
			UPDATE `karloff_weborder_details` SET CashbackCode = strCuponDTLS,`CashbackDiscountAmount` = intCouponAmount
            WHERE order_id =strOrderID and (product_id =intProductID or product_subcategory_id = intSubCategoryID);
            
            UPDATE karloff_weborder SET CashbackTotalDiscount = intCouponAmount
            WHERE order_id = strOrderID AND customer_id = intCustomerID;

			SET strOutPutDTLS = CONCAT('SUCCESS: Good to go!;',intCouponAmount ,';0');
		ELSEIF (strDiscountType = 'DISCOUNT') THEN		  
		    
             UPDATE karloff_weborder_details SET CouponCode = strCuponDTLS,`CouponCodeDiscount` = intCouponAmount
             WHERE order_id = strOrderID and (product_id =intProductID or product_subcategory_id = intSubCategoryID);	

             UPDATE karloff_weborder SET CouponCodeTotalDiscount = intCouponAmount
             WHERE order_id = strOrderID AND customer_id = intCustomerID;
             
			CALL sp_updateOrderDetails_cuponapplied(strOrderID,intCouponAmount,intProductID,intSubCategoryID,strDiscountAppliedOn);
             
			SET strOutPutDTLS = CONCAT('SUCCESS: Good to go!;0;',intCouponAmount);				 
		  END IF;
		  INSERT INTO `karloff_promocode_applied`(`customer_id`,`coupon_applied`)
          VALUES(intCustomerID,strCuponDTLS);
		  -- Update WebOrder and WebOrder Details for product as PROMO Applied
		
       /* ELSE
         IF totalSaleValue = 0 THEN
           SET strOutPutDTLS = 'ERROR: Cupon related product is not present!';	
         ELSE
           SET strOutPutDTLS = 'ERROR: Total order amount is low!';	
         END IF;*/
          	
		END IF;
	-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++	  
		ELSE
		 SET strOutPutDTLS = 'ERROR: Cupon previously applied!';
	    END IF;   
	  ELSE
		 SET strOutPutDTLS = 'ERROR: Promo code expired!';
	  END IF;
  ELSE
     SET strOutPutDTLS = 'ERROR: No Promo code present!';
  END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_Cupon_DetailsfrmOrder` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_Cupon_DetailsfrmOrder`(IN strOrderID VARCHAR(200),
OUT strOutPutDTLS varchar(500))
BEGIN
 DECLARE DiscountAmount,CashbackAmount INT;
 DECLARE strDiscountCode,strCashbackCode VARCHAR(100);
 
DECLARE done INT DEFAULT FALSE;
DECLARE cur_prodlist CURSOR FOR 
SELECT CouponCode,CouponCodeDiscount,CashbackCode,CashbackDiscountAmount
FROM karloff_weborder_details WHERE order_id = strOrderID;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 

 OPEN cur_prodlist;
  read_loop: LOOP
    FETCH cur_prodlist INTO strDiscountCode,DiscountAmount,strCashbackCode,CashbackAmount;
    IF done THEN
      LEAVE read_loop;
    END IF;
    
      IF(strDiscountCode <> NULL OR strDiscountCode <> '') THEN	  
	   SET strOutPutDTLS = CONCAT('SUCCESS: Cupon Present!;0;',DiscountAmount,';',strDiscountCode);	  
	  ELSEIF(strCashbackCode <> NULL OR strCashbackCode <> '') THEN
	   SET strOutPutDTLS = CONCAT('SUCCESS: Cupon Present!;',CashbackAmount,';0;',strCashbackCode);
	  ELSE
	    SET strOutPutDTLS = 'NOTFOUND: No Promo code present!';
      END IF; 
   
  END LOOP;
  CLOSE cur_prodlist;
 
 END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_generateInvoice_details` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_generateInvoice_details`(IN `intCardAmount` INT, IN `intCashAmount` INT, IN `intPaytemAmount` INT, IN `intOnlineAmount` INT, IN `intUserID` INT, IN `intOrderID` BIGINT)
BEGIN
DECLARE intCurrentMaxInvoiveID,intNewInvoiveID,intTHISInvoiveIDPresent,intStoreID,intNetAmount INT;

set intCurrentMaxInvoiveID =0; set intNewInvoiveID =0;set intTHISInvoiveIDPresent =0;set intStoreID=0;set intNetAmount =0;

select MAX(invoice_id) into intCurrentMaxInvoiveID from karloff_invoice_master;
SET intNewInvoiveID = (intCurrentMaxInvoiveID + 1);
select COUNT(id) into intTHISInvoiveIDPresent from karloff_invoice_master where invoice_id = intNewInvoiveID;
IF (intTHISInvoiveIDPresent > 0) THEN
	SET intNewInvoiveID = (intNewInvoiveID + 1);
END IF;

select adm_store INTO intStoreID from karloff_admin where id = intUserID;
select NetAmount INTO intNetAmount from karloff_weborder where order_id = intOrderID;


INSERT INTO `karloff_invoice_master`(`invoice_id`,`order_id`,`customer_id`,`delivery_charge`,`GoCashPay`,`Promocode`,`PromocodeDiscount`,`PromoDiscBasePrice`,`PromoDiscGST`,
`CouponCodeTotalDiscount`,`CashbackTotalDiscount`,`discount_amount`,`RedeemAmount`,`BaseValue`,`CgstAmount`,`SgstAmount`,`IgstAmount`,`InvoiceAmount`,`NetAmount`,
`round_off_amount`,`order_status`,`payment_code`,`InvoiceDate`,`order_receive_date`,`order_receive_datetime`,`delivery_date`,`sale_point`,`sales_person`,
`sale_type`,`updated_at`,`created_at`) 
SELECT intNewInvoiveID,`order_id`,`customer_id`,`delivery_charge`,`GoCashPay`,`Promocode`,`PromocodeDiscount`,`PromoDiscBasePrice`,`PromoDiscGST`,
`CouponCodeTotalDiscount`,`CashbackTotalDiscount`,`discount_amount`,`RedeemAmount`,`BaseValue`,`CgstAmount`,`SgstAmount`,`IgstAmount`,`InvoiceAmount`,`NetAmount`,
`round_off_amount`,`order_status`,`payment_code`,Current_Date(),`order_date`,`order_datetime`,Current_Date(),intStoreID,intUserID,3,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP()
 FROM `karloff_weborder` WHERE order_id = intOrderID;

UPDATE `karloff_invoice_master` SET order_status = 5 WHERE order_id = intOrderID;
UPDATE `karloff_weborder` SET order_status = 5 WHERE order_id = intOrderID;

INSERT INTO `karloff_invoice_details`(`invoice_id`,`order_id`,`customer_id`,`product_id`,`product_category_id`,`product_subcategory_id`,`CgstPercent`,`SgstPercent`,`IgstPercent`,
`BaseValue`,`CgstAmount`,`SgstAmount`,`IgstAmount`,`RoundOffAmount`,`CouponCode`,`CouponCodeDiscount`,`CashbackCode`,`CashbackDiscountAmount`,
`subYesorNot`,`qty`,`net_amount`,`updated_at`,`created_at`)
SELECT intNewInvoiveID,`order_id`,`customer_id`,`product_id`,`product_category_id`,`product_subcategory_id`,`CgstPercent`,`SgstPercent`,`IgstPercent`,
`BaseValue`,`CgstAmount`,`SgstAmount`,`IgstAmount`,`RoundOffAmount`,`CouponCode`,`CouponCodeDiscount`,`CashbackCode`,`CashbackDiscountAmount`,
`subYesorNot`,`qty`,`net_amount`,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP() FROM `karloff_weborder_details` WHERE order_id = intOrderID;

INSERT INTO `karloff_account_manager`(`invoice_no`,`NetAmount`,`PromocodeDiscount`,`GoCashAmount`,`cash_amount`,`card_amount`,`paytm_amount`,`online_amount`,
`settled`,`updated_at`,`created_at`) VALUES(intNewInvoiveID,intNetAmount,0,0,intCashAmount,intCardAmount,intPaytemAmount,intOnlineAmount,1,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP());

CALL sp_updatefinalStock_details(intOrderID);

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_generateInvoice_details_main` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_generateInvoice_details_main`(IN `intCardAmount` INT, IN `intCashAmount` INT, IN `intPaytemAmount` INT, IN `intOnlineAmount` INT, IN `intUserID` INT, IN `intOrderID` VARCHAR(200))
BEGIN
CALL sp_generateInvoice_details(intCardAmount,intCashAmount,intPaytemAmount,intOnlineAmount,intUserID,intOrderID);
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_getAllProduct_lst` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_getAllProduct_lst`()
BEGIN

 Select t1.id,ProductCode,HsnCode,ProductName,ProductMrp,ProductOfferprice,ProductClubprice,
PseudoStock,t2.CategoryName,t2.CategoryIconPath,t3.SubcategoryL1Name,t3.SubCategoryIconPath,
t4.unit_name,t5.packing_name,t6.colourName,t6.hexCode,BrandName,IndividualLimit,ProductVolume
from karloff_productdb t1 
left join karloff_category t2 on t1.ProductCategory = t2.id 
left join karloff_subcategorylevel1 t3 on t3.id = t1.ProductSubcategoryL1
left join karloff_unitdb t4 on t1.ProductUnit = t4.id
left join karloff_packingdb t5 on t1.ProductCategory = t5.id 
left join karloff_prod_colour t6 on t1.ProductSubcategoryL1 = t6.id
left join karloff_brand t7 on t1.ProductBrand = t7.id
where ((ProductCode <>'') OR (ProductCode <> NULL)) and CurrentStock > 0 
and PseudoStock > 0 and ProductEnable = 1;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_getCartDetails_fromOrderID` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_getCartDetails_fromOrderID`(IN `intOrderID` VARCHAR(200))
BEGIN

-- select `product_id`,`BaseValue`,`CgstAmount`,`SgstAmount`,`IgstAmount`,`qty`,`net_amount` from karloff_weborder_details where order_id = intOrderID;
select t2.ProductName,t2.ProductCode,`product_id`,`BaseValue`,`CgstAmount`,`SgstAmount`,`IgstAmount`,`qty`,`net_amount` 
from karloff_weborder_details t1,karloff_productdb t2 where t1.product_id=t2.id and order_id = intOrderID;


select `BaseValue`,`CgstAmount`,`SgstAmount`,`IgstAmount`,`NetAmount` from karloff_weborder where order_id = intOrderID;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_getCustomerClubMemberDetails` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_getCustomerClubMemberDetails`(IN `intCustomerID` BIGINT, OUT `strYesNo` VARCHAR(20))
BEGIN
DECLARE intRowPresent INT;
SET strYesNo = 'No';
SET intRowPresent = 0;

	select (Select Count(ID) from karloff_club_membership where customer_id = t1.customer_id 
	and  expiry >= Current_Date()) INTO intRowPresent 
	from karloff_customer t1 where t1.id = intCustomerID;
    
    IF (intRowPresent > 0) THEN
      SET strYesNo = 'Yes';
    END IF;
                
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_getOrderCountdown_dtls` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_getOrderCountdown_dtls`()
BEGIN

-- select count(id) as totalid from karloff_weborder where order_status =1;

select 
(select count(id) from karloff_weborder where order_status =1) as neworder,
(select count(id) from karloff_weborder where order_status in (2,3)) as processorder ,
(select count(id) from karloff_weborder where order_status =4) as dispatchorder ,
(select count(id) from karloff_weborder where order_status =5) as deliveredorder  
from dual;


END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_getOrderList_fromStatusID` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_getOrderList_fromStatusID`(IN intOrderStatusID INT)
BEGIN

	/* select order_id,t1.customer_id,t1.updated_at,t1.created_at,t2.name,t2.mobile,NetAmount,order_status 
from karloff_weborder t1 ,karloff_customer t2 
where order_id not in (select order_id from karloff_invoice_master) and t1.customer_id = t2.customer_id 
and t1.order_status = intOrderStatusID; */

 select order_id,t1.customer_id,t2.customer_id,t1.updated_at,t1.created_at,t2.name,t2.mobile,NetAmount,
 order_status,t1.payment_code,t3.payment_name,delivery_charge,GoCashPay,RedeemAmount
from karloff_weborder t1 ,karloff_customer t2,karloff_payment_mode t3 
where t1.customer_id = t2.customer_id and t3.payment_code = t1.payment_code
and t1.order_status = intOrderStatusID;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_getOrder_Details` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_getOrder_Details`(IN strOrderID VARCHAR(200))
BEGIN

select InvoiceAmount, NetAmount, round_off_amount,status_name,delivery_date,
                t2.name,t2.mobile,t2.cust_gst,t2.customer_id,t1.created_at,
                t1.payment_code,t4.payment_name,delivery_charge,GoCashPay,RedeemAmount,t4.id as paymentid
				from karloff_weborder t1, karloff_customer t2,karloff_order_status t3,karloff_payment_mode t4
                 where t1.customer_id = t2.customer_id and t3.id = t1.order_status and t4.payment_code = t1.payment_code
                 and order_id = strOrderID;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_getProduct_dtls` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_getProduct_dtls`(IN strProductCode VARCHAR(50))
BEGIN

 Select t1.id,ProductCode,HsnCode,ProductName,ProductMrp,ProductOfferprice,ProductClubprice,
PseudoStock,t2.CategoryName,t2.CategoryIconPath,t3.SubcategoryL1Name,t3.SubCategoryIconPath,
t4.unit_name,t5.packing_name,t6.colourName,t6.hexCode,BrandName,IndividualLimit,ProductVolume
from karloff_productdb t1 
left join karloff_category t2 on t1.ProductCategory = t2.id 
left join karloff_subcategorylevel1 t3 on t3.id = t1.ProductSubcategoryL1
left join karloff_unitdb t4 on t1.ProductUnit = t4.id
left join karloff_packingdb t5 on t1.ProductCategory = t5.id 
left join karloff_prod_colour t6 on t1.ProductSubcategoryL1 = t6.id
left join karloff_brand t7 on t1.ProductBrand = t7.id
where ((ProductCode <>'') OR (ProductCode <> NULL)) and CurrentStock > 0 
and PseudoStock > 0 and ProductEnable = 1 AND ProductCode like CONCAT(strProductCode,"%");

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_getProduct_pricedetails` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_getProduct_pricedetails`(IN intProductID INT,
IN intDiscountAmount INT,
IN intCustomerID LONG,
IN intQty INT,
OUT intRateAmount FLOAT,
OUT intAmount FLOAT,
OUT intCGSTAmount FLOAT,
OUT intSGSTAmount FLOAT,
OUT intIGSTAmount FLOAT,
OUT intPRICEAmount FLOAT)
BEGIN
DECLARE isCustomerClubMember VARCHAR(10);
DECLARE fProdOfferPrice,fProdClubPrice,fIGSTPer,fCGSTPer,fSGSTPer float;
DECLARE fPriceP FLOAT;
 
SET isCustomerClubMember = ''; 
SET fProdOfferPrice = 0;SET fProdClubPrice = 0;SET fIGSTPer = 0;SET fCGSTPer = 0;SET fSGSTPer = 0;
-- SET fPriceP =0; set fRate = 0; set fCGST = 0; set fSGST =0; set fIGST = 0;
SET fPriceP =0;

 CALL sp_getCustomerClubMemberDetails(intCustomerID,isCustomerClubMember);
 
/* INSERT INTO karloff_db_log(logdtls) 
VALUES(CONCAT(intProductID,';',intDiscountAmount,';',intCustomerID,';',isCustomerClubMember,';')); */

 select ProductOfferprice,ProductClubprice,ProductIgst,ProductCgst,ProductSgst
  into fProdOfferPrice,fProdClubPrice,fIGSTPer,fCGSTPer,fSGSTPer
   from karloff_productdb where id = intProductID;
			 
		IF (isCustomerClubMember = 'Yes') THEN
		  SET fPriceP = fProdClubPrice;
		ELSE
		 SET fPriceP = fProdOfferPrice;
		END IF;
	 	
	SET intRateAmount = fPriceP;	
    
	set fPriceP = (fPriceP * intQty);
    
    IF (intDiscountAmount > 0) THEN
      SET fPriceP = (fPriceP - intDiscountAmount);
    END IF;
    
	-- set fRate = (fPriceP * intQty);
	set intCGSTAmount = (fPriceP * (fCGSTPer/100));
	set intSGSTAmount = (fPriceP * (fSGSTPer/100));
	set intIGSTAmount = (fPriceP * (fIGSTPer/100));
	set intAmount = (fPriceP -(intCGSTAmount + intSGSTAmount + intIGSTAmount));
	SET intPRICEAmount = fPriceP;
    

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_getSearch_OrderList` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_getSearch_OrderList`(IN intOrderID VARCHAR(200),
IN intOrderDate VARCHAR(200),
IN intOrderStatus VARCHAR(200),
IN intCustomerID VARCHAR(200),
IN strCustomerName VARCHAR(200),
IN strCustomerMobile VARCHAR(200))
BEGIN
DECLARE strQuery TEXT;
/*
DECLARE strQuery TEXT;

SET @strQuery = CONCAT(@strQuery," where FK_TypeID = ",intExamTypeID," and IsActive = 1 ");
SET @strQuery = CONCAT(@strQuery,"; ");

   PREPARE stmt FROM @strQuery;   
    EXECUTE stmt;	
	DEALLOCATE PREPARE stmt;

*/
-- select ID,status_name from karloff_order_status t1 where id in(1,2,3,4); 
-- SET @strQuery = CONCAT(@strQuery," select order_id,t1.customer_id,t2.customer_id,t1.updated_at,t1.created_at,t2.name,t2.mobile,NetAmount,");
SET @strQuery = "select order_id,t1.customer_id,t2.customer_id,t1.updated_at,t1.created_at,t2.name,t2.mobile,NetAmount,";
SET @strQuery = CONCAT(@strQuery," order_status,t1.payment_code,t3.payment_name,delivery_charge,GoCashPay,RedeemAmount");
SET @strQuery = CONCAT(@strQuery," from karloff_weborder t1 ,karloff_customer t2,karloff_payment_mode t3 ");
SET @strQuery = CONCAT(@strQuery," where t1.customer_id = t2.customer_id and t3.payment_code = t1.payment_code");

IF ((intOrderStatus IS NOT NULL) AND (intOrderStatus <> '')) THEN
   SET @strQuery = CONCAT(@strQuery," and t1.order_status ='", intOrderStatus,"'");
END IF;

IF ((intOrderID IS NOT NULL) AND (intOrderID <> '')) THEN
   SET @strQuery = CONCAT(@strQuery," and t1.order_id like('", intOrderID,"%')");
END IF;
-- ++++++++++++++++++++++++++++++++++++
IF ((intCustomerID IS NOT NULL) AND (intCustomerID <> '')) THEN
   SET @strQuery = CONCAT(@strQuery," and t2.customer_id like('", intCustomerID,"%')");
END IF;
IF ((strCustomerName IS NOT NULL) AND (strCustomerName <> '')) THEN
   SET @strQuery = CONCAT(@strQuery," and t2.name like('", strCustomerName,"%')");
END IF;
IF ((strCustomerMobile IS NOT NULL) AND (strCustomerMobile <> '')) THEN
   SET @strQuery = CONCAT(@strQuery," and t2.mobile like('", strCustomerMobile,"%')");
END IF;
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SET @strQuery = CONCAT(@strQuery,";");

PREPARE stmt FROM @strQuery;   
EXECUTE stmt;	
DEALLOCATE PREPARE stmt; 

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_insert_CustomerAddress` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_insert_CustomerAddress`(IN strCustomerID VARCHAR(200),
 IN strCustName VARCHAR(200),IN strCustMobile VARCHAR(200),
 IN strCustHouseNo VARCHAR(200),IN strCustLocality VARCHAR(200),
 IN strCustLandmark VARCHAR(200),IN strCustCity VARCHAR(200),IN strCustState VARCHAR(200),
 IN strCustPin VARCHAR(200),IN strCustAddresType VARCHAR(200),
 IN strCustisDefault INT)
BEGIN

update karloff_address_manager set is_default=1 where customer_id = strCustomerID;

INSERT INTO `karloff_address_manager`(`customer_id`,`full_name`,`phone_no`,`house_no`,`locality`,`landmark`,`city`,`state`,`type`,`is_default`,`pincode`,`updated_at`,`created_at`)
VALUES(strCustomerID,strCustName,strCustMobile,strCustHouseNo,strCustLocality,strCustLandmark,strCustCity,strCustState,strCustAddresType,strCustisDefault,strCustPin,CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP());

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_orderstatus_list` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_orderstatus_list`(IN intFlag INT)
BEGIN
 IF (intFlag = 0) THEN
	select ID,status_name from karloff_order_status t1;
  ELSEIF (intFlag = 1) THEN
	select ID,status_name from karloff_order_status t1 where id in(0,1,2,3,4,5,6); 
  ELSEIF (intFlag = 2) THEN
	select ID,status_name from karloff_order_status t1 where id in(1,2,3,4); 
  END IF; 
  
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_remove_orderdetails` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_remove_orderdetails`(IN `intOrderID` VARCHAR(200))
BEGIN
SELECT `product_id`,`product_category_id`,`product_subcategory_id`,`qty` 
FROM `karloff_weborder_details` WHERE order_id =intOrderID;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_set_CustomerAddressDefault` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_set_CustomerAddressDefault`(IN strCustomerID VARCHAR(200),
IN intAddressID VARCHAR(200))
BEGIN

update karloff_address_manager set is_default=1 where customer_id = strCustomerID;

update karloff_address_manager set is_default=0 
where customer_id = strCustomerID and id = intAddressID;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_updatefinalStock_details` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_updatefinalStock_details`(IN `intOrderID` BIGINT)
BEGIN
 -- **********************************************
  DECLARE done INT DEFAULT FALSE;
  DECLARE prodid,prodcatid,prodsubcatid,qtydtls INT; 
  
 DECLARE cursor_Prod CURSOR FOR 
 SELECT `product_id`,`product_category_id`,`product_subcategory_id`,`qty` 
 FROM `karloff_weborder_details` WHERE order_id = intOrderID;  
 
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     
   OPEN cursor_Prod;
  read_loop: LOOP
    FETCH cursor_Prod INTO prodid,prodcatid,prodsubcatid,qtydtls;
    IF done THEN
      LEAVE read_loop;
    END IF;
   -- ========================================= 
    update karloff_productdb set CurrentStock = (CurrentStock - qtydtls) where id = prodid and ProductCategory = prodcatid and ProductSubcategoryL1 = prodsubcatid;
  -- ==============================================
  END LOOP;
  CLOSE cursor_Prod;
	 
 -- ********************************************
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_updateOrderDetails_cuponapplied` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_updateOrderDetails_cuponapplied`(IN strOrderID VARCHAR(200),
IN intCouponAmount INT,
IN intProductID INT,
IN intSubCategoryID INT,
IN strDiscountAppliedOn VARCHAR(50))
BEGIN
DECLARE intQty INT;
DECLARE isCustomerClubMember VARCHAR(10);
DECLARE intCustomerID LONG;
DECLARE fProdOfferPrice,fProdClubPrice,fIGSTPer,fCGSTPer,fSGSTPer float;
DECLARE fPriceP,fRate,fCGST,fSGST,fIGST,fperPrice FLOAT;

SET intQty = 0;
-- SET isCustomerClubMember = ''; 
SET intCustomerID=0;
SET fProdOfferPrice = 0;SET fProdClubPrice = 0;SET fIGSTPer = 0;SET fCGSTPer = 0;SET fSGSTPer = 0;
SET fPriceP =0; set fRate = 0; set fCGST = 0; set fSGST =0; set fIGST = 0;set fperPrice=0;

 select customer_id into intCustomerID from karloff_weborder where order_id = strOrderID;
 -- CALL `sp_getCustomerClubMemberDetails`(intCustomerID,isCustomerClubMember);
  
  
 IF (strDiscountAppliedOn = 'PRODUCTID') THEN	
 -- UPDATE WEB-ORDER AND WEB-ORDER-DETAILS TABLE BEFORE PROCESS
	 SELECT `BaseValue`,`CgstAmount`,`SgstAmount`,`IgstAmount`,`net_amount`,QTY 
	INTO fRate,fCGST,fSGST,fIGST,fPriceP,intQty
	FROM karloff_weborder_details WHERE order_id = strOrderID AND product_id = intProductID;

	update karloff_weborder set BaseValue = (BaseValue - fRate),CgstAmount = (CgstAmount - fCGST), 
	SgstAmount = (SgstAmount - fSGST),IgstAmount = (IgstAmount - fIGST),InvoiceAmount = (InvoiceAmount - fPriceP),
	NetAmount = (NetAmount - fPriceP),updated_at= CURRENT_TIMESTAMP() where order_id = strOrderID;
    -- RESET ALL VALUES    
    SET fPriceP =0; set fRate = 0; set fCGST = 0; set fSGST =0; set fIGST = 0; set fperPrice =0;   
        
 -- GET PRODUCT VALUE DETAILS
     CALL sp_getProduct_pricedetails(intProductID,intCouponAmount,intCustomerID,intQty,fperPrice,fRate,fSGST,fCGST,fIGST,fPriceP);

 /* INSERT INTO karloff_db_log(logdtls) 
 VALUES(CONCAT('Disc -',intCouponAmount,'; QTY -',intQty,'; PER PRICE -',fperPrice,'; RATE-',fRate,'; FINAL PRICE - ',fPriceP));
*/
 
 -- APPLIED FINAL DETAILS
   UPDATE karloff_weborder_details SET `BaseValue` = fRate,`CgstAmount`= fCGST,`SgstAmount`= fSGST,
   `IgstAmount`= fIGST,`net_amount`= fPriceP 	
	WHERE order_id = strOrderID AND product_id = intProductID;
 
	update karloff_weborder set BaseValue = (BaseValue + fRate),CgstAmount = (CgstAmount + fCGST), 
	SgstAmount = (SgstAmount + fSGST),IgstAmount = (IgstAmount + fIGST),InvoiceAmount = (InvoiceAmount + fPriceP),
	NetAmount = (NetAmount + fPriceP),updated_at= CURRENT_TIMESTAMP() where order_id = strOrderID;
    
 ELSEIF (strDiscountAppliedOn = 'PRODSUBCATID') THEN
 
   CALL sp_updateOrderDetails_cuponapplied_2(strOrderID,intCouponAmount,intSubCategoryID);
   
 END IF;
 --  select * from karloff_promo_code;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_updateOrderDetails_cuponapplied_2` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_updateOrderDetails_cuponapplied_2`(IN strOrderID VARCHAR(200),
IN intCouponAmount INT,
IN intSubCategoryID INT)
BEGIN
DECLARE intQty,intProductID INT;
DECLARE isCustomerClubMember VARCHAR(10);
DECLARE intCustomerID LONG;
DECLARE fProdOfferPrice,fProdClubPrice,fIGSTPer,fCGSTPer,fSGSTPer float;
DECLARE fPriceP,fRate,fCGST,fSGST,fIGST,fperPrice FLOAT;
-- ++++++++++++++++++++++++++++++++++
DECLARE done INT DEFAULT FALSE;
DECLARE cur_prodlist CURSOR FOR 
SELECT BaseValue,CgstAmount,SgstAmount,IgstAmount,net_amount,QTY,product_id
FROM karloff_weborder_details WHERE order_id = intOrderID AND product_subcategory_id = intSubCategoryID;
 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++


SET intQty = 0; set intProductID = 0;
SET isCustomerClubMember = ''; SET intCustomerID=0;
SET fProdOfferPrice = 0;SET fProdClubPrice = 0;SET fIGSTPer = 0;SET fCGSTPer = 0;SET fSGSTPer = 0;
SET fPriceP =0; set fRate = 0; set fCGST = 0; set fSGST =0; set fIGST = 0;set fperPrice=0;

  select customer_id into intCustomerID from karloff_weborder where order_id = strOrderID; 
 -- +++++++++++++++++++++++++++++++++++++++
 OPEN cur_prodlist;
  read_loop: LOOP
    FETCH cur_prodlist INTO fRate,fCGST,fSGST,fIGST,fPriceP,intQty,intProductID;
    IF done THEN
      LEAVE read_loop;
    END IF;
  
    update karloff_weborder set BaseValue = (BaseValue - fRate),CgstAmount = (CgstAmount - fCGST), 
	SgstAmount = (SgstAmount - fSGST),IgstAmount = (IgstAmount - fIGST),InvoiceAmount = (InvoiceAmount - fPriceP),
	NetAmount = (NetAmount - fPriceP),updated_at= CURRENT_TIMESTAMP() where order_id = intOrderID;
    -- RESET ALL VALUES    
    SET fPriceP =0; set fRate = 0; set fCGST = 0; set fSGST =0; set fIGST = 0; set fperPrice =0;   
        
 -- GET PRODUCT VALUE DETAILS 
    CALL sp_getProduct_pricedetails(intProductID,intCouponAmount,intCustomerID,intQty,fperPrice,fRate,fSGST,fCGST,fIGST,fPriceP);

 
 -- APPLIED FINAL DETAILS
   UPDATE karloff_weborder_details SET `BaseValue` = fRate,`CgstAmount`= fCGST,`SgstAmount`= fSGST,
   `IgstAmount`= fIGST,`net_amount`= fPriceP 	
	WHERE order_id = intOrderID AND product_id = intProductID;
 
	update karloff_weborder set BaseValue = (BaseValue + fRate),CgstAmount = (CgstAmount + fCGST), 
	SgstAmount = (SgstAmount + fSGST),IgstAmount = (IgstAmount + fIGST),InvoiceAmount = (InvoiceAmount + fPriceP),
	NetAmount = (NetAmount + fPriceP),updated_at= CURRENT_TIMESTAMP() where order_id = intOrderID;       
   
  END LOOP;
  CLOSE cur_prodlist;
-- ++++++++++++++++++++++++++++++++++++++    
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_UpdatePsudoProductStock` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_AUTO_VALUE_ON_ZERO' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_UpdatePsudoProductStock`(IN `intProductID` INT, IN `intQty` INT, IN `intOrderID` VARCHAR(200), IN `intRemoved` INT)
BEGIN

	IF (intRemoved = 1) THEN  -- Product Added on cart
      update karloff_productdb set PseudoStock = (PseudoStock - intQty) where id = intProductID;
	ELSE   -- Product Removed from cart
       update karloff_productdb set PseudoStock = (PseudoStock + intQty) where id = intProductID;
   
     CALL sp_UpdateWebOrderTableforProductRemoved(intProductID,intQty,intOrderID);
	
    END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_UpdateWebOrderTableforProductRemoved` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `sp_UpdateWebOrderTableforProductRemoved`(IN `intProductID` INT, IN `intQty` INT, IN `intOrderID` BIGINT)
BEGIN

DECLARE fPriceP,fRate,fCGST,fSGST,fIGST FLOAT;
DECLARE intTotalProductinCart INT;

SET fPriceP =0; set fRate = 0; set fCGST = 0; set fSGST =0; set fIGST = 0;
SET intTotalProductinCart = 0;

SELECT `BaseValue`,`CgstAmount`,`SgstAmount`,`IgstAmount`,`net_amount` 
INTO fRate,fCGST,fSGST,fIGST,fPriceP
FROM karloff_weborder_details WHERE order_id = intOrderID AND product_id = intProductID;


update karloff_weborder set BaseValue = (BaseValue - fRate),CgstAmount = (CgstAmount - fCGST), 
		SgstAmount = (SgstAmount - fSGST),IgstAmount = (IgstAmount - fIGST),InvoiceAmount = (InvoiceAmount - fPriceP),
		NetAmount = (NetAmount - fPriceP),updated_at= CURRENT_TIMESTAMP()
		where order_id = intOrderID;

DELETE FROM karloff_weborder_details WHERE order_id = intOrderID AND product_id = intProductID;

SELECT COUNT(ID) INTO intTotalProductinCart FROM karloff_weborder_details WHERE order_id = intOrderID;
IF (intTotalProductinCart = 0) THEN
  DELETE FROM karloff_weborder WHERE order_id = intOrderID;
END IF;

END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `test_procedure` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE  PROCEDURE `test_procedure`()
BEGIN
DECLARE intProductID,intQty,intUserID INT;
DECLARE intCustomerID,intOrderID BIGINT;
  DECLARE strCuponDTLS,strOutPutDTLS VARCHAR(500);
 --  `sp_AddtoCart_OneProduct`(IN intProductID INT,IN intCustomerID BIGINT,IN intOrderID BIGINT,IN intQty INT,IN intUserID INT) 
 
/* SET intProductID = 103; SET intQty =1;  SET intUserID = 40;
  SET intCustomerID = 1577437; SET intOrderID =2020070712170840; 
  
 call `sp_AddtoCart_OneProduct`(intProductID,intCustomerID,intOrderID,intQty,intUserID);*/
-- SELECT Param3;
 -- select ;
 
/* SET intProductID = 259; SET intQty =2;
  SET intOrderID =2020060617165140; 
 call sp_UpdatePsudoProductStock(intProductID,intQty,intOrderID,0); */
 
 -- CALL sp_getSearch_OrderList('','','','','','');
 
 -- call sp_getOrderCountdown_dtls();
 /* SET intOrderID =2020062617210440; set strCuponDTLS = 'TESTDISCOUNT2';
 call sp_Cupon_Applydetails(intOrderID,strCuponDTLS,strOutPutDTLS);
 select strOutPutDTLS; */
  SET intOrderID =2020062617210440;
 call sp_Cupon_DetailsfrmOrder(intOrderID,strOutPutDTLS);
 select strOutPutDTLS;
 
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-07-11 16:29:07
