(59489,61319140,87646508773,359,1,2,0,0,0,36,0,0,0,0,NULL,0,NULL,0,3,36,0,'2020-06-23 23:14:13','2020-06-23 23:14:13'),(59490,61319140,87646508773,2193,1,2,0,0,0,6,0,0,0,0,NULL,0,NULL,0,1,6,0,'2020-06-23 23:14:13','2020-06-23 23:14:13'),(59491,61319140,87646508773,389,1,2,0,0,0,16,0,0,0,0,NULL,0,NULL,0,1,16,0,'2020-06-23 23:14:13','2020-06-23 23:14:13'),(59492,61319140,87646508773,2354,6,19,0,0,0,47.4576,4.27,4.27,0,0,NULL,0,NULL,0,1,56,0,'2020-06-23 23:14:13','2020-06-23 23:14:13'),(59493,61319140,87646508773,392,1,2,0,0,0,17,0,0,0,0,NULL,0,NULL,0,1,17,0,'2020-06-23 23:14:13','2020-06-23 23:14:13'),(59494,61319140,87646508773,290,2,11,0,0,0,361.905,9.05,9.05,0,0,NULL,0,NULL,0,1,380,0,'2020-06-23 23:14:13','2020-06-23 23:14:13'),(59495,61319140,87646508773,370,1,2,0,0,0,13,0,0,0,0,NULL,0,NULL,0,1,13,0,'2020-06-23 23:14:13','2020-06-23 23:14:13'),(59496,64085542,208787558925,378,1,2,0,0,0,10,0,0,0,0,NULL,0,NULL,0,1,10,0,'2020-06-24 00:11:40','2020-06-24 00:11:40'),(59497,64085542,208787558925,367,1,2,0,0,0,7,0,0,0,0,NULL,0,NULL,0,1,7,0,'2020-06-24 00:11:40','2020-06-24 00:11:40'),(59498,64085542,208787558925,1733,9,31,0,0,0,83.8983,7.55,7.55,0,0,NULL,0,NULL,0,1,99,0,'2020-06-24 00:11:40','2020-06-24 00:11:40'),(59499,64085542,208787558925,2538,9,31,0,0,0,16.9492,1.53,1.53,0,0,NULL,0,NULL,0,1,20,0,'2020-06-24 00:11:40','2020-06-24 00:11:40'),(59500,64085542,208787558925,1027,3,9,0,0,0,20,1,1,0,0,NULL,0,NULL,0,1,22,0,'2020-06-24 00:11:40','2020-06-24 00:11:40'),(59501,64085542,208787558925,370,1,2,0,0,0,14,0,0,0,0,NULL,0,NULL,0,1,14,0,'2020-06-24 00:11:40','2020-06-24 00:11:40'),(59502,64085542,208787558925,369,1,2,0,0,0,14,0,0,0,0,NULL,0,NULL,0,1,14,0,'2020-06-24 00:11:40','2020-06-24 00:11:40'),(59503,64085542,208787558925,360,1,2,0,0,0,26,0,0,0,0,NULL,0,NULL,0,1,26,0,'2020-06-24 00:11:40','2020-06-24 00:11:40'),(59504,6044346,550145100785,1050,3,10,0,0,0,215,0,0,0,0,NULL,0,NULL,0,1,215,0,'2020-06-24 00:44:04','2020-06-24 00:44:04'),(59505,65371685,150379639967,946,4,22,0,0,0,154.237,13.88,13.88,0,0,NULL,0,NULL,0,1,182,0,'2020-06-24 01:30:53','2020-06-24 01:30:53'),(59506,65371685,150379639967,307,2,11,0,0,0,361.905,9.05,9.05,0,0,NULL,0,NULL,0,1,380,0,'2020-06-24 01:30:53','2020-06-24 01:30:53'),(59507,65929231,137176320353,2035,3,8,3,3,0,76.1905,1.90476,1.90476,0,0,'0',0,'0',0,2,80,0,'2020-06-24 07:54:35','2020-06-24 07:54:35'),(59508,65929231,137176320353,2042,3,43,3,3,0,17.1429,0.428571,0.428571,0,0,'0',0,'0',0,1,18,0,'2020-06-24 07:55:32','2020-06-24 07:55:32'),(59509,65929231,137176320353,2228,3,10,0,0,0,23,0,0,0,0,'0',0,'0',0,1,23,0,'2020-06-24 07:55:47','2020-06-24 07:55:47'),(59510,60753319,59404240545,980,5,28,0,0,0,499,0,0,0,0,NULL,0,NULL,0,1,499,0,'2020-06-24 08:10:07','2020-06-24 08:10:07'),(59511,64069304,59404240545,980,5,28,0,0,0,499,0,0,0,0,NULL,0,NULL,0,1,499,0,'2020-06-24 08:34:40','2020-06-24 08:34:40'),(59512,62620218,99375246237,3576,1,2,0,0,0,11,0,0,0,0,NULL,0,NULL,0,1,11,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59513,62620218,99375246237,374,1,2,0,0,0,36,0,0,0,0,NULL,0,NULL,0,2,36,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59514,62620218,99375246237,364,1,2,0,0,0,39,0,0,0,0,NULL,0,NULL,0,1,39,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59515,62620218,99375246237,395,1,2,0,0,0,18,0,0,0,0,NULL,0,NULL,0,2,18,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59516,62620218,99375246237,1214,2,84,0,0,0,66,0,0,0,0,NULL,0,NULL,0,3,66,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59517,62620218,99375246237,551,1,2,0,0,0,25,0,0,0,0,NULL,0,NULL,0,1,25,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59518,62620218,99375246237,408,1,2,0,0,0,8,0,0,0,0,NULL,0,NULL,0,1,8,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59519,62620218,99375246237,360,1,2,0,0,0,52,0,0,0,0,NULL,0,NULL,0,2,52,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59520,62620218,99375246237,1220,2,11,0,0,0,61.9048,1.55,1.55,0,0,NULL,0,NULL,0,1,65,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59521,62620218,99375246237,359,1,2,0,0,0,26,0,0,0,0,NULL,0,NULL,0,2,26,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59522,62620218,99375246237,377,1,2,0,0,0,16,0,0,0,0,NULL,0,NULL,0,1,16,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59523,62620218,99375246237,370,1,2,0,0,0,14,0,0,0,0,NULL,0,NULL,0,1,14,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59524,62620218,99375246237,362,1,2,0,0,0,12,0,0,0,0,NULL,0,NULL,0,1,12,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59525,62620218,99375246237,369,1,2,0,0,0,28,0,0,0,0,NULL,0,NULL,0,2,28,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59526,62620218,99375246237,366,1,2,0,0,0,19,0,0,0,0,NULL,0,NULL,0,1,19,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59527,62620218,99375246237,389,1,2,0,0,0,20,0,0,0,0,NULL,0,NULL,0,1,20,0,'2020-06-24 08:47:26','2020-06-24 08:47:26'),(59528,63955378,358445508115,365,1,2,0,0,0,12,0,0,0,0,NULL,0,NULL,0,1,12,0,'2020-06-24 09:39:39','2020-06-24 09:39:39'),(59529,63955378,358445508115,359,1,2,0,0,0,104,0,0,0,0,NULL,0,NULL,0,8,104,0,'2020-06-24 09:39:39','2020-06-24 09:39:39'),(59530,63955378,358445508115,360,1,2,0,0,0,260,0,0,0,0,NULL,0,NULL,0,10,260,0,'2020-06-24 09:39:39','2020-06-24 09:39:39'),(59531,62547478,36143879903,1449,9,30,0,0,0,74.5763,6.71,6.71,0,0,NULL,0,NULL,0,2,88,0,'2020-06-24 10:11:25','2020-06-24 10:11:25'),(59532,62547478,36143879903,360,1,2,0,0,0,52,0,0,0,0,NULL,0,NULL,0,2,52,0,'2020-06-24 10:11:25','2020-06-24 10:11:25'),(59533,62547478,36143879903,359,1,2,0,0,0,26,0,0,0,0,NULL,0,NULL,0,2,26,0,'2020-06-24 10:11:25','2020-06-24 10:11:25'),(59534,62547478,36143879903,1515,9,30,0,0,0,26.2712,2.36,2.36,0,0,NULL,0,NULL,0,1,31,0,'2020-06-24 10:11:25','2020-06-24 10:11:25'),(59535,62547478,36143879903,551,1,2,0,0,0,25,0,0,0,0,NULL,0,NULL,0,1,25,0,'2020-06-24 10:11:25','2020-06-24 10:11:25'),(59536,62547478,36143879903,1713,1,2,0,0,0,57,0,0,0,0,NULL,0,NULL,0,3,57,0,'2020-06-24 10:11:25','2020-06-24 10:11:25'),(59537,62547478,36143879903,381,1,2,0,0,0,32,0,0,0,0,NULL,0,NULL,0,2,32,0,'2020-06-24 10:11:25','2020-06-24 10:11:25'),(59538,62547478,36143879903,364,1,2,0,0,0,39,0,0,0,0,NULL,0,NULL,0,1,39,0,'2020-06-24 10:11:25','2020-06-24 10:11:25'),(59539,62547478,36143879903,3604,1,2,0,0,0,35,0,0,0,0,NULL,0,NULL,0,1,35,0,'2020-06-24 10:11:25','2020-06-24 10:11:25'),(59540,63362388,1445451847,1436,8,30,0,0,0,132.203,11.9,11.9,0,0,NULL,0,NULL,0,2,156,0,'2020-06-24 10:31:33','2020-06-24 10:31:33'),(59541,63362388,1445451847,393,1,2,0,0,0,22,0,0,0,0,NULL,0,NULL,0,1,22,0,'2020-06-24 10:31:33','2020-06-24 10:31:33'),(59542,63362388,1445451847,390,1,2,0,0,0,69,0,0,0,0,NULL,0,NULL,0,3,69,0,'2020-06-24 10:31:33','2020-06-24 10:31:33'),(59543,63362388,1445451847,373,1,2,0,0,0,40,0,0,0,0,NULL,0,NULL,0,4,40,0,'2020-06-24 10:31:33','2020-06-24 10:31:33'),(59544,63362388,1445451847,364,1,2,0,0,0,78,0,0,0,0,NULL,0,NULL,0,2,78,0,'2020-06-24 10:31:33','2020-06-24 10:31:33'),(59545,63362388,1445451847,448,1,2,0,0,0,22,0,0,0,0,NULL,0,NULL,0,2,22,0,'2020-06-24 10:31:33','2020-06-24 10:31:33'),(59546,65761052,913542796944,980,5,28,0,0,0,499,0,0,0,0,NULL,0,NULL,0,1,499,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59547,65761052,913542796944,389,1,2,0,0,0,20,0,0,0,0,NULL,0,NULL,0,1,20,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59548,65761052,913542796944,378,1,2,0,0,0,10,0,0,0,0,NULL,0,NULL,0,1,10,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59549,65761052,913542796944,382,1,2,0,0,0,20,0,0,0,0,NULL,0,NULL,0,1,20,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59550,65761052,913542796944,395,1,2,0,0,0,9,0,0,0,0,NULL,0,NULL,0,1,9,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59551,65761052,913542796944,370,1,2,0,0,0,14,0,0,0,0,NULL,0,NULL,0,1,14,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59552,65761052,913542796944,369,1,2,0,0,0,14,0,0,0,0,NULL,0,NULL,0,1,14,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59553,65761052,913542796944,367,1,2,0,0,0,7,0,0,0,0,NULL,0,NULL,0,1,7,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59554,65761052,913542796944,448,1,2,0,0,0,22,0,0,0,0,NULL,0,NULL,0,2,22,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59555,65761052,913542796944,381,1,2,0,0,0,16,0,0,0,0,NULL,0,NULL,0,1,16,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59556,65761052,913542796944,374,1,2,0,0,0,18,0,0,0,0,NULL,0,NULL,0,1,18,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59557,65761052,913542796944,359,1,2,0,0,0,13,0,0,0,0,NULL,0,NULL,0,1,13,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59558,65761052,913542796944,1214,2,84,0,0,0,44,0,0,0,0,NULL,0,NULL,0,2,44,0,'2020-06-24 10:56:57','2020-06-24 10:56:57'),(59559,60740879,1578219,1001,3,8,0,0,0,12.381,0.31,0.31,0,0,NULL,0,NULL,0,1,13,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59560,60740879,1578219,448,1,2,0,0,0,33,0,0,0,0,NULL,0,NULL,0,3,33,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59561,60740879,1578219,1352,3,9,0,0,0,14,0,0,0,0,NULL,0,NULL,0,1,14,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59562,60740879,1578219,1220,2,11,0,0,0,61.9048,1.55,1.55,0,0,NULL,0,NULL,0,1,65,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59563,60740879,1578219,1214,2,84,0,0,0,44,0,0,0,0,NULL,0,NULL,0,2,44,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59564,60740879,1578219,1077,3,43,0,0,0,28.5714,0.71,0.71,0,0,NULL,0,NULL,0,1,30,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59565,60740879,1578219,415,1,2,0,0,0,20,0,0,0,0,NULL,0,NULL,0,1,20,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59566,60740879,1578219,378,1,2,0,0,0,10,0,0,0,0,NULL,0,NULL,0,1,10,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59567,60740879,1578219,382,1,2,0,0,0,20,0,0,0,0,NULL,0,NULL,0,1,20,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59568,60740879,1578219,389,1,2,0,0,0,20,0,0,0,0,NULL,0,NULL,0,1,20,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59569,60740879,1578219,1868,3,42,0,0,0,224.762,5.62,5.62,0,0,NULL,0,NULL,0,2,236,0,'2020-06-24 11:29:07','2020-06-24 11:29:07'),(59570,61385933,7792259947,1214,2,84,0,0,0,22,0,0,0,0,NULL,0,NULL,0,1,22,0,'2020-06-24 11:53:13','2020-06-24 11:53:13'),(59571,61598482,261469688072,1560,4,23,0,0,0,16.9492,1.53,1.53,0,0,NULL,0,NULL,0,2,20,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59572,61598482,261469688072,1567,4,23,0,0,0,16.9492,1.53,1.53,0,0,NULL,0,NULL,0,2,20,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59573,61598482,261469688072,1693,4,23,0,0,0,8.47458,0.76,0.76,0,0,NULL,0,NULL,0,1,10,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59574,61598482,261469688072,2550,4,23,0,0,0,12.7119,1.14,1.14,0,0,NULL,0,NULL,0,1,15,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59575,61598482,261469688072,2271,4,23,0,0,0,12.7119,1.14,1.14,0,0,NULL,0,NULL,0,1,15,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59576,61598482,261469688072,1558,4,23,0,0,0,16.9492,1.53,1.53,0,0,NULL,0,NULL,0,2,20,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59577,61598482,261469688072,2214,4,55,0,0,0,33.8983,3.05,3.05,0,0,NULL,0,NULL,0,2,40,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59578,61598482,261469688072,915,4,23,0,0,0,50.8475,4.58,4.58,0,0,NULL,0,NULL,0,2,60,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59579,61598482,261469688072,2344,4,55,0,0,0,25.4237,2.29,2.29,0,0,NULL,0,NULL,0,2,30,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59580,61598482,261469688072,1447,8,30,0,0,0,33.8983,3.05,3.05,0,0,NULL,0,NULL,0,2,40,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59581,61598482,261469688072,480,9,30,0,0,0,46.6102,4.19,4.19,0,0,NULL,0,NULL,0,1,55,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59582,61598482,261469688072,1451,9,30,0,0,0,35.5932,3.2,3.2,0,0,NULL,0,NULL,0,1,42,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59583,61598482,261469688072,2040,3,43,0,0,0,130,0,0,0,0,NULL,0,NULL,0,2,130,0,'2020-06-24 12:01:15','2020-06-24 12:01:15'),(59584,63793023,21504858351,1016,3,40,0,0,0,37.2881,3.36,3.36,0,0,NULL,0,NULL,0,1,44,0,'2020-06-24 12:11:37','2020-06-24 12:11:37'),(59585,6232453,21504858351,1903,3,9,0,0,0,17,0,0,0,0,NULL,0,NULL,0,1,17,0,'2020-06-24 12:18:23','2020-06-24 12:18:23');

--
-- Dumping events for database 'karloffc_grocerkarl'
--

--
-- Dumping routines for database 'karloffc_grocerkarl'
--
/*!50003 DROP PROCEDURE IF EXISTS `sp_AddtoCart_OneProduct` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DEFINER=`root`@`localhost` 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 DEFINER=`root`@`localhost` 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 DROP PROCEDURE IF EXISTS `sp_generateInvoice_details_main` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DEFINER=`root`@`localhost` 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 DEFINER=`root`@`localhost` 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_getOrderCountdown_dtls` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DROP PROCEDURE IF EXISTS `sp_getOrderList_fromStatusID` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DROP PROCEDURE IF EXISTS `sp_getOrder_Details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DROP PROCEDURE IF EXISTS `sp_getSearch_OrderList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DROP PROCEDURE IF EXISTS `sp_insert_CustomerAddress` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DROP PROCEDURE IF EXISTS `sp_orderstatus_list` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DROP PROCEDURE IF EXISTS `sp_remove_orderdetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DROP PROCEDURE IF EXISTS `sp_set_CustomerAddressDefault` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DROP PROCEDURE IF EXISTS `sp_updatefinalStock_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DROP PROCEDURE IF EXISTS `sp_UpdatePsudoProductStock` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` 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 DEFINER=`root`@`localhost` 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 DEFINER=`root`@`localhost` 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); */
 
 -- CALL sp_getSearch_OrderList('','','','','','');
 
 call sp_getOrderCountdown_dtls();
END ;;
DELIMITER ;


