/*!50003 DROP PROCEDURE IF EXISTS `sp_AddtoCart_OneProduct` */;
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 FLOAT;
DECLARE isCustomerClubMember VARCHAR(10);

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;

select adm_store INTO intStoreID from karloff_admin where id = intUserID;

  CALL `sp_getCustomerClubMemberDetails`(intCustomerID,isCustomerClubMember);
  
select ProductOfferprice,ProductClubprice,PseudoStock,ProductIgst,ProductCgst,ProductSgst,
ProductCategory,ProductSubcategoryL1 into fProdOfferPrice,fProdClubPrice,intPseudoStock,
fIGSTPer,fCGSTPer,fSGSTPer,intProdCat,intProdSubCat	

from karloff_productdb where id = intProductID;
			 
		IF (isCustomerClubMember = 'Yes') THEN
		  SET fPriceP = fProdClubPrice;
		ELSE
		 SET fPriceP = fProdOfferPrice;
		END IF;
		
		   
set fPriceP = (fPriceP * intQty);
-- set fRate = (fPriceP * intQty);
set fCGST = (fPriceP * (fCGSTPer/100));
set fSGST = (fPriceP * (fSGSTPer/100));
set fIGST = (fPriceP * (fIGSTPer/100));

set fRate = (fPriceP -(fCGST + fSGST + fIGST));


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)  
  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 DROP PROCEDURE IF EXISTS `sp_AddtoCart_OneProduct_main` */;
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 DROP PROCEDURE IF EXISTS `sp_generateInvoice_details` */;
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());

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_generateInvoice_details_main` */;
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 DROP PROCEDURE IF EXISTS `sp_getCartDetails_fromOrderID` */;
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,`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 DROP PROCEDURE IF EXISTS `sp_getCustomerClubMemberDetails` */;
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 DROP PROCEDURE IF EXISTS `sp_UpdatePsudoProductStock` */;
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 DROP PROCEDURE IF EXISTS `sp_UpdateWebOrderTableforProductRemoved` */;
DELIMITER ;;
CREATE  PROCEDURE `sp_UpdateWebOrderTableforProductRemoved`(IN intProductID INT,
IN intQty INT,
IN intOrderID BIGINT)
BEGIN

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

SET fPriceP =0; set fRate = 0; set fCGST = 0; set fSGST =0; set fIGST = 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;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test_procedure` */;
DELIMITER ;;
CREATE  PROCEDURE `test_procedure`()
BEGIN
DECLARE intProductID,intQty,intUserID INT;
DECLARE intCustomerID,intOrderID BIGINT;
/* -- DECLARE Param5 VARCHAR(500);
 --  `sp_AddtoCart_OneProduct`(IN intProductID INT,IN intCustomerID BIGINT,IN intOrderID BIGINT,IN intQty INT,IN intUserID INT) 
 
 SET intProductID = 2420; SET intQty =1;  SET intUserID = 40;
  SET intCustomerID = 2062020142531; SET intOrderID =0506202018054540; 
  
 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);
END ;;
DELIMITER ;


