DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getItemsTransferredDetails`(
in eachGrnId int,
in branchId int
)
BEGIN
SELECT 
		TSTMD.aMainStckTrnfrId  AS mainTransferId,
        TSTMD.dcNumber as dcNumber,
        TSTMD.indentNumber as indentNumber,
        DATE(TSTMD.dcDate) as dcDate,
        TSITE.issuedQty as transferredQty,
        THB.branchHospName as transferredToBranch,
        THB1.branchHospName as transferredFromBranch        
    FROM tbl_grn_for_po_each_item_orders TGFPEIO
		JOIN tbl_stocks_issued_to_emr TSITE ON TSITE.ngrnEachId_fk = TGFPEIO.aGrnId_each_pk
		JOIN tbl_stock_transfer_maindetails TSTMD ON TSTMD.aMainStckTrnfrId = TSITE.nMainStockTrnsfrId
        JOIN tblhospitalbranches THB ON THB.abranchId = TSTMD.transferredToBranchId
        JOIN tblhospitalbranches THB1 ON THB1.abranchId = TSTMD.branchId
	WHERE TSITE.ngrnEachId_fk = eachGrnId AND TSTMD.branchId = branchId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getItemWiseAvailQtyInAllBranches` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getItemWiseAvailQtyInAllBranches`(
in branchId int,
in storeId int 
)
BEGIN
SELECT 
    *
FROM
    (SELECT 
        THB.branchHospName,
            TMS.storeName,
            TMID.itemShortCode,
            TMID.itemName,
            SUM(TGA.availQty) AS availQty,
            TGFPEIO.strength AS diapOrStr,
            TGFPEIO.volume AS sizeOrVol,
            TGA.nBranchId,
            TBLBD.brandName,
            TBLMD.manufacturerName,
            TGFPEIO.batchNo AS batchNo,
            TGFPEIO.serialNumber AS serialNumber,
            DATE(TGFPEIO.expiryDate) AS expiryDate,
            TBLIGD.grnNumber AS invoiceNo,
            TBLISMD.purchasePrice AS price
    FROM
        tbl_grn_availqty TGA
    JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TGA.nItemId
    JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TMID.nStoreId_fk
    JOIN tblhospitalbranches THB ON THB.abranchId = TGA.nBranchId
    JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
    JOIN tbl_grn_for_po_orders TBLIGD ON TBLIGD.aGrnId_pk = TGFPEIO.nGrnId_fk
    JOIN tbl_mst_brands TBLBD ON TBLBD.aBrandTypeId_pk = TMID.nBrandTypeId_fk
    JOIN tbl_mst_item_manufacturers TBLMD ON TBLMD.aManufacturerId_pk = TMID.nItemManufacturerId_fk
    JOIN tbl_supplier_item_mapping TBLISMD ON TBLISMD.supplier_item_mappingId = TGA.nSupplierMapId
    WHERE
        TMID.isActive = 1 AND TMID.isDeleted = 0
            AND TGA.availQty > 0
            AND IF(storeId = - 1, TRUE, TGA.nStoreId = storeId)
            AND TMS.storeFlag <> 1
            AND IF(branchId != - 1, TGA.nBranchId = branchId, TRUE)
    GROUP BY TGA.nBranchId , TGA.nItemId , TGFPEIO.strength , TGFPEIO.volume UNION SELECT 
        THB.branchHospName,
            TMS.storeName,
            TMID.itemShortCode,
            TMID.itemName,
            SUM(TGA.availQty) AS availQty,
            TGFPEIO.diapter AS diapOrStr,
            TGFPEIO.size AS sizeOrVol,
            TGA.nBranchId,
            TBLBD.brandName,
            TBLMD.manufacturerName,
            TGFPEIO.batchNo AS batchNo,
            TGFPEIO.serialNumber AS serialNumber,
            DATE(TGFPEIO.expiryDate) AS expiryDate,
            TBLIGD.grnNumber AS invoiceNo,
            TBLISMD.purchasePrice AS price
    FROM
        tbl_grn_availqty TGA
    JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TGA.nItemId
    JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TMID.nStoreId_fk
    JOIN tblhospitalbranches THB ON THB.abranchId = TGA.nBranchId
    JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
    JOIN tbl_grn_for_po_orders TBLIGD ON TBLIGD.aGrnId_pk = TGFPEIO.nGrnId_fk
    JOIN tbl_mst_brands TBLBD ON TBLBD.aBrandTypeId_pk = TMID.nBrandTypeId_fk
    JOIN tbl_mst_item_manufacturers TBLMD ON TBLMD.aManufacturerId_pk = TMID.nItemManufacturerId_fk
    JOIN tbl_supplier_item_mapping TBLISMD ON TBLISMD.supplier_item_mappingId = TGA.nSupplierMapId
    WHERE
        TMID.isActive = 1 AND TMID.isDeleted = 0
            AND TGA.availQty > 0
            AND IF(storeId = - 1, TRUE, TGA.nStoreId = storeId)
            AND TMS.storeFlag = 1
            AND IF(branchId != - 1, TGA.nBranchId = branchId, TRUE)
    GROUP BY TGA.nBranchId , TGA.nItemId , TGFPEIO.diapter , TGFPEIO.size) AS TMP
ORDER BY TMP.nBranchId , TMP.itemName;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getLeftRightVal` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getLeftRightVal`(
IN itemId int,
IN storeId int,
IN stockType int,
IN branchId int
)
BEGIN
SELECT 
		IF(storeId = 1, diapter, strength) AS leftVal,
        IF(storeId = 1, strength,volume) AS rightVal
        FROM tbl_grn_for_po_each_item_orders
	WHERE 
		nItemId_fk 		= itemId
        AND nStoreId_fk = storeId
        AND nPoType_fk  = stockType
        AND ngrnBranchId_fk = branchId
	GROUP BY diapter, strength
ORDER BY aGrnId_each_pk DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getLocation` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getLocation`(
)
BEGIN
SELECT 
	aLocationId_pk AS alocationId, 
	locnName AS locationName
	FROM tbl_mst_locations TML
	WHERE TML.isActive =1 AND TML.isDeleted = 0;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getLocationList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getLocationList`()
BEGIN
SELECT 
    TBLLOC.aLocationId_pk AS locId,
    TBLLOC.locnShortCode AS locShortCode,
    TBLLOC.locnName AS locName
FROM
    tbl_mst_locations TBLLOC
WHERE
    TBLLOC.isActive = 1
        AND TBLLOC.isDeleted = 0;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMainDebitDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getMainDebitDetails`(
in mainDebitId int,
in debitStatus int,
in branchId int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
SELECT 
		TBNA.aMainDNId AS mainDnId
        ,TBNA.DNNumber AS dnNumber
        ,TBNA.totQty AS totalQty
        ,TBNA.noOfItems AS totalItems
        ,TBNA.totTaxAmount AS totalTaxAmt
        ,TBNA.totAmount AS totalAmount
    FROM tbl_debit_note_main TBNA 
		-- JOIN tbl_debit_note_each TBNE ON TBNA.aMainDNId = TBNE.nMainDnId
	WHERE TBNA.aMainDNId = mainDebitId AND TBNA.debitStatus = debitStatus  -- AND TBNA.nBranchId = branchId;
    LIMIT minlimitRow , maxlimitRow;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMainGrnIdFromGrnNumber` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getMainGrnIdFromGrnNumber`(
in grnNumber varchar(100),
in branchId int
)
BEGIN
SELECT aGrnId_pk FROM tbl_grn_for_po_orders WHERE grnNumber = grnNumber AND ngrnBranchId_fk = branchId
LIMIT 0,1; 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMainListForCommercials` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getMainListForCommercials`(
in commStatusId varchar(25),
in grnBranchId int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
	SELECT TGFPO.aGrnId_pk
			,TGFPO.grnNumber
			,DATE(TGFPO.grnDate) AS grnDate
			,TGFPEIO.nStoreId_fk
			,TMS.storeName
			,TMP.aPoTypeId
			,TMP.poTypeNAme
            ,TGFPO.invoiceNumber
                ,TGFPO.invoiceAmount
                ,TGFPO.invoiceTaxAmt
                ,TGFPO.frightCharges
                ,TGFPEIO.nPoIdCentral_fk AS mainPoId
	FROM tbl_grn_for_po_each_item_orders TGFPEIO
			JOIN tbl_grn_for_po_orders TGFPO ON TGFPO.aGrnId_pk = TGFPEIO.nGrnId_fk
			JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TGFPEIO.nStoreId_fk
			JOIN tbl_mst_potypes TMP ON TMP.aPoTypeId = TGFPEIO.nPoType_fk
	WHERE  FIND_IN_SET(TGFPEIO.nCommercialStatusId, commStatusId) 
		AND TGFPEIO.ngrnBranchId_fk = grnBranchId
	GROUP BY TGFPEIO.nGrnId_fk
		ORDER BY TGFPO.aGrnId_pk DESC
	LIMIT minlimitRow , maxlimitRow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMainStockTransferDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getMainStockTransferDetails`(
in branchIds int,
in storeIds int,
in requestType int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
SELECT aMainStckTrnfrId, dcNumber, DATE(dcDate) AS dcDate, indentNumber, 
 THB.branchHospName as transferredToBranch, 
 TMSC.storeName as storeName,
 TMP.poTypeNAme as typeName
 FROM tbl_stock_transfer_maindetails TSTMD
 JOIN tblhospitalbranches THB ON THB.abranchId = TSTMD.transferredToBranchId
 JOIN tbl_mst_storecreation TMSC ON TMSC.aStoreId_pk = TSTMD.storeId
 JOIN tbl_mst_potypes TMP ON TMP.aPoTypeId = TSTMD.typeId
 WHERE 
	TSTMD.isActive = 1
	AND TSTMD.isDeleted = 0
   -- AND TSTMD.transferredToBranchId = branchIds
   AND TSTMD.branchId = branchIds
    AND TSTMD.storeId = storeIds
    ORDER BY aMainStckTrnfrId DESC
	LIMIT minlimitRow , maxlimitRow ; 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMasterBrandNamesList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getMasterBrandNamesList`()
BEGIN

SELECT 
    aBrandTypeId_pk, brandName
FROM
    tbl_mst_brands
WHERE
    isActive = 1 AND isDeleted = 0;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMasterItemsList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getMasterItemsList`()
BEGIN
SELECT 
    aItemId_pk AS itemId,
    nStoreId_fk AS storeId,
    itemShortCode,
    itemName AS `name`,
    storeName,
    nHsnCodeId_fk AS  hsn,
    itemDescription AS  `desc`,
    genericName AS GenericName,
    nItemManufacturerId_fk AS  manufacturer,
    nBrandTypeId_fk AS brand,
    UNIT AS unit,
    TBLHD.hsnCode AS hsnCode,
    TBLHD.gstPercentage AS  gstPercentage,
   TBLHD.sgstPercentage AS sgstPercentage,
   TBLHD.cgstPercentage AS cgstPercentage,
   TBLHD.igstPercentage AS igstPercentage,
   
   aItemId_pk ,
    nStoreId_fk ,
    itemShortCode,
    itemName ,
    nHsnCodeId_fk,
    itemDescription, 
    nItemManufacturerId_fk ,
    nBrandTypeId_fk 
FROM
    tbl_mst_items_details TMID
        JOIN
    tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TMID.nStoreId_fk
    JOIN 
    tbl_mst_hsn_codes TBLHD ON TBLHD.aHsnAutoId_pk = TMID.nHsnCodeId_fk
WHERE
    TMID.isActive = 1 AND TMID.isDeleted = 0
ORDER BY aItemId_pk DESC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMasterManufactureNamesList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getMasterManufactureNamesList`()
BEGIN

SELECT 
    manufacturerName, aManufacturerId_pk
FROM
    tbl_mst_item_manufacturers
WHERE
    isActive = 1 AND isDeleted = 0;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMasterModulesList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getMasterModulesList`()
BEGIN
SELECT aModuleId, moduleName, isHavingSubModule  FROM tbl_modules
		WHERE isActive = 1 AND isDeleted = 0 AND 
        (isHavingSubModule = 0 OR isHavingSubModule = 1) ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMasterStoresList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getMasterStoresList`()
BEGIN

select aStoreId_pk as  storeId,storeName,storeFlag from tbl_mst_storecreation where isActive = 1;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getModulesFeatures` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getModulesFeatures`(
in moduleId int
)
BEGIN
SELECT aMstModFeatureId, featureName FROM tbl_mst_module_features
 WHERE isActive = 1 AND isDeleted = 0 AND nModuleId = moduleId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getMstHSNCodesList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getMstHSNCodesList`()
BEGIN
SELECT 
    aHsnAutoId_pk as hsnId,
    hsnCode,
    gstPercentage,
    sgstPercentage,
    cgstPercentage,
    igstPercentage
FROM
    tbl_mst_hsn_codes
WHERE
    isActive = 1 order by aHsnAutoId_pk desc;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_GetOTConsumedList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_GetOTConsumedList`(
in_Fromdate varchar(50),
in_Todate varchar(50),
in_branchIds varchar(50),
in otOrOpd int -- 2 OT & 3 OPD
)
BEGIN
SELECT  
    TBLE.itemName,
    TBLE.itemShortCode,
    DATE(TBLE.expiryDate) AS expiryDate,
    TBLE.diapter,
    TBLE.size,
    TBLE.strength,
    TBLE.volume,
    TBLE.storeType,
    TBLE.batchNo AS batchNo,
    GROUP_CONCAT(DISTINCT TBLE.serialNo) AS serialNo,
    COUNT(TBLE.nItemId) itemCount,
    SUM(TBLE.usedQty) AS usedQty,
    TBLE.nItemId
FROM
    tbl_ot_stock_consumption_main_details TBLM
        JOIN
    tbl_ot_stock_consumption_each_details TBLE ON TBLE.nMainConsumptionId = TBLM.aOtStockConsumptionMainId
WHERE
    Date(TBLM.insertedDate) between in_Fromdate and in_Todate
   AND TBLM.nBranchId in(in_branchIds)
   AND IF(otOrOpd = 2, TBLM.patientId <> -1, TBLM.patientId = -1)
GROUP BY TBLE.nItemId , diapter , size , TBLE.batchNo AND TBLE.nItemId , strength , volume , TBLE.batchNo;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getOtStock_ConsumedPatiets` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getOtStock_ConsumedPatiets`(
in fromdate date,
in toDate date,
in branchId int
)
BEGIN

SELECT 
    aOtStockConsumptionMainId,
    patCode,
    patName,
    surgeryName,
    procedureName
FROM
    TBL_OT_STOCK_CONSUMPTION_MAIN_DETAILS
WHERE
    DATE(insertedDate) BETWEEN fromdate AND toDate
        AND nBranchId = branchId
        AND patientId <> - 1
        AND patientId <> 0
        AND patientId IS NOT NULL
ORDER BY aOtStockConsumptionMainId DESC;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getOtStock_Consumed_Itemsist` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getOtStock_Consumed_Itemsist`(
in consumMainAid int
)
BEGIN


SELECT 
    itemName, itemShortCode, usedQty
FROM
    tbl_ot_stock_consumption_each_details
WHERE
    nMainConsumptionId =consumMainAid and find_in_set(storeType,'2,3');

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getPoEachItemsDetailsForprint` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getPoEachItemsDetailsForprint`(
in poId int, 
in branchId int
)
BEGIN
SELECT 
	TOPMD.apoid,
    TOPEID.apoeachid,
    TOPMD.gstType,
    TOPMD.instore_fk,
	TMID.aItemId_pk,
    TMID.itemShortCode,
    /*concat(IFNULL(TMID.itemName,''),
			' [',IFNULL(TMIMT.itemMakeTypeName,'')
            ,'] [',IFNULL(TMIT.itemtypeName,''),']'
            ,if(1 = 1, 
				concat('[',IFNULL(TOPEID.item_diapter,''),'] [',IFNULL(TOPEID.item_size,''),']')
            ,concat('[',IFNULL(TOPEID.item_strength,''),'] [',IFNULL(TOPEID.item_volume,''),']'))
	) as itemFullName,*/
    IFNULL(TMID.itemName,'') AS itemFullName,
    IFNULL(TOPEID.item_diapter,'') AS diopter,
    IFNULL(TOPEID.item_size,'') AS size,
    IFNULL(TOPEID.item_strength,'') AS strength,
    IFNULL(TOPEID.item_volume,'') AS volume,
    TMID.itemName,
    TMHC.aHsnAutoId_pk,
    TMHC.hsnCode,
    TOPEID.item_order_qty,    
    TMHC.gstPercentage,
    TMHC.sgstPercentage,
    (((IFNULL(TSIM.purchasePrice,0) * IFNULL(TMHC.sgstPercentage,0)))/100) * IFNULL(TOPEID.item_order_qty,0) AS sgstAmt,
    TMHC.cgstPercentage,
    (((IFNULL(TSIM.purchasePrice,0) * IFNULL(TMHC.cgstPercentage,0)))/100) * IFNULL(TOPEID.item_order_qty,0) AS cgstAmt,
    TMHC.igstPercentage,
    (((IFNULL(TSIM.purchasePrice,0) * IFNULL(TMHC.igstPercentage,0)))/100) * IFNULL(TOPEID.item_order_qty,0) AS igstAmt,
    TSIM.purchasePrice AS unitQtyPrice,
    (((IFNULL(TSIM.purchasePrice,0) * IFNULL(TMHC.gstPercentage,0)))/100) AS unitQtyTaxPrice,
    (IFNULL(TSIM.purchasePrice,0) * IFNULL(TOPEID.item_order_qty,0)) AS poItemPriceWoTax,
    (((IFNULL(TSIM.purchasePrice,0) * IFNULL(TMHC.gstPercentage,0)))/100) * IFNULL(TOPEID.item_order_qty,0) AS poItemTaxPrice,
    (IFNULL(TSIM.purchasePrice,0) * IFNULL(TOPEID.item_order_qty,0)) +
    (((IFNULL(TSIM.purchasePrice,0) * IFNULL(TMHC.gstPercentage,0)))/100) * IFNULL(TOPEID.item_order_qty,0) AS poItemPriceWithTax,
    ifnull(TOPMD.discount_amount, 0) discount_amount,
    ifnull(TOPMD.poRemarkss, '') poRemarkss,
    
    ifnull(TOPEID.item_free_qty, 0) item_free_qty
FROM tbl_mst_items_details TMID
	JOIN tbl_os_po_each_item_details TOPEID ON TOPEID.item_id_fk = TMID.aItemId_pk
    JOIN tbl_os_po_main_details TOPMD ON TOPMD.apoid = TOPEID.npoid_fk

    JOIN tbl_supplier_item_mapping TSIM  ON
     TSIM.supplier_item_mappingId = TOPEID.nSupplierItemMapid
     -- ON TSIM.nSupplierId_fk = TOPMD.po_to_supplier_id_fk
		AND TOPEID.item_id_fk = TSIM.nItemId_fk AND TOPMD.po_from_branch_fk = TSIM.nBranchId
-- 	LEFT JOIN tbl_mst_items_make_type TMIMT ON TMIMT.aItemsMakeTypeId_pk = TMID.nItemtypeMakeId_fk
--  LEFT JOIN tbl_mst_items_type TMIT ON TMIT.aItemtypeId_pk = TMID.nItemtypeId_fk
    JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnAutoId_pk = TSIM.nhsnId_fk
WHERE TOPMD.apoid = poId
	AND TOPMD.po_from_branch_fk = branchId
GROUP BY TOPEID.apoeachid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getPoItemsDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getPoItemsDetails`(
in poId int,
in storeId int,
in poItemStatusIds text
)
BEGIN
select apoeachid, 
	TOPEID.item_id_fk, 
	TMID.itemCode as itemsCode1,
    TMID.itemShortCode as itemCode,
    TOPMD.instore_fk as poType,
	-- concat(IFNULL(TMID.itemName,''),' [',IFNULL(TMIMT.itemMakeTypeName,''),'] [',IFNULL(TMIT.itemtypeName,''),']') as itemName,
    IFNULL(TMID.itemName,'') as itemName,
	TOPEID.item_diapter,
	TOPEID.item_size,
	TOPEID.item_strength,
	TOPEID.item_volume,
	TOPEID.item_total_amt_with_gst,
	(TOPEID.item_order_qty ) as item_order_qty ,
	 ifnull(SUM(TGFPEIO.receivedQty),0) AS receivedQty,
	 IF(
			ifnull(SUM(TGFPEIO.receivedQty),0) = 0
			,'Un Delivered'
			,IF(item_order_qty = ifnull(SUM(TGFPEIO.receivedQty),0) ,'Delivered','Partially Delivered')) as itemReceivedStatus,
		/*if(ifnull((TOPEID.item_order_qty),0) - ifnull(SUM(TGFPEIO.receivedQty),0) > 0
			,ifnull((TOPEID.item_order_qty),0) - ifnull(SUM(TGFPEIO.receivedQty),0)
			,0
			) AS balanceQty,*/
	( UDF_getPoItemsBalanceQtyWhileGrn(TOPMD.apoid, TOPEID.apoeachid) + TOPEID.item_free_qty) AS balanceQty,
	TOPEID.poItemStatusId,
	if(TOPEID.poItemStatusId = '7','deleted','active') as itemCls,
	TOPMD.po_total_items,
	TOPMD.po_total_items_qty,
	TOPMD.po_total_amount,
    TOPMD.final_total_amount,
    TOPMD.freight_charge,
    TOPMD.discount_amount,
    IFNULL(TOPEID.nSupplierItemMapid,'') AS nSupplierItemMapid,
    IFNULL(TSIM.purchasePrice,0) AS purchasePrice,
    TMID.nHsnCodeId_fk AS HSNId,
    TMHC.hsnCode,
    IFNULL(TMHC.sgstPercentage,0) AS sgstPercentage,
    IFNULL(TMHC.cgstPercentage,0) AS cgstPercentage,
    IFNULL(TMHC.igstPercentage,0) AS igstPercentage,
    IFNULL(TMHC.gstPercentage,0)  AS gstPercentage,
     (IFNULL(TMHC.gstPercentage,0)/100.0) * IFNULL(TSIM.purchasePrice,0) AS taxAmt,
     TBLSD.storeFlag ,
TSIM.TATinDays,
TSIM.nBranchId,
TSIM.nhsnId_fk,
TOPEID.item_free_qty as freeQty,
-- TSIM.freeQty,
TSIM.nUserId_fk,
TSIM.nSupplierId_fk,
TSIM.isAtPriority,
hsnCode as item_hsn_code,
 ifnull(TGFPEIO.aGrnId_each_pk,'') as  aGrnId_each_pk,
 TOPMD.gstType,
  TOPMD.apoid as mainPodId
	from tbl_os_po_each_item_details TOPEID
	JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TOPEID.item_id_fk and TMID.isActive = 1
    JOIN tbl_supplier_item_mapping TSIM ON TSIM.supplier_item_mappingId = TOPEID.nSupplierItemMapid
     JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnAutoId_pk = TSIM.nhsnId_fk
	JOIN tbl_os_po_main_details TOPMD ON TOPMD.apoid = TOPEID.npoid_fk
-- 	JOIN tbl_mst_items_details TMID2 ON TMID2.aItemId_pk = TOPEID.item_id_fk
-- 	LEFT JOIN tbl_mst_items_make_type TMIMT ON TMIMT.aItemsMakeTypeId_pk = TMID.nItemtypeMakeId_fk
-- 	LEFT JOIN tbl_mst_items_type TMIT ON TMIT.aItemtypeId_pk = TMID.nItemtypeId_fk
	LEFT JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.nPoEachId = TOPEID.apoeachid
       join tbl_mst_storecreation TBLSD ON TBLSD.aStoreId_pk=TOPMD.instore_fk

	where TOPEID.npoid_fk = poId
    AND find_in_Set(TOPEID.poItemStatusId,poItemStatusIds) 
	AND TOPMD.instore_fk = storeId
	AND TOPEID.isActive = 1
	AND TOPEID.isDeleted = 0
	GROUP BY 
    TOPEID.apoeachid
   /*TOPEID.npoid_fk ,
		TOPEID.item_id_fk
		, TOPEID.item_diapter
        , TOPEID.item_size
        , TOPEID.item_strength
        , TOPEID.item_volume*/ ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getPoItemsForCommercials` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getPoItemsForCommercials`(
in poId int,
in branchId int
)
BEGIN
SELECT 
		TMID.aItemId_pk AS itemId
        ,TMID.itemName
        ,COUNT(TOPEID.item_id_fk) AS noOfItems
		,(TOPEID.item_order_qty) AS orderQty
        ,IFNULL(TSIM.purchasePrice,0) AS purchasePrice,
		TMID.nHsnCodeId_fk AS HSNId,
		TMHC.hsnCode,
        TMID.aItemId_pk AS itemId,
		IFNULL(TMHC.sgstPercentage,0) AS sgstPercentage,
		IFNULL(TMHC.cgstPercentage,0) AS cgstPercentage,
		IFNULL(TMHC.igstPercentage,0) AS igstPercentage,
		IFNULL(TMHC.gstPercentage,0)  AS gstPercentage,
		((IFNULL(TMHC.gstPercentage,0)/100.0) * IFNULL(TSIM.purchasePrice,0)) * TOPEID.item_order_qty AS taxAmt,
        ((IFNULL(TSIM.purchasePrice,0) + (IFNULL(TMHC.gstPercentage,0)/100.0) * IFNULL(TSIM.purchasePrice,0))) * TOPEID.item_order_qty AS itemPrice
    FROM 
		tbl_os_po_each_item_details TOPEID
	JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TOPEID.item_id_fk
    JOIN tbl_supplier_item_mapping TSIM ON TSIM.supplier_item_mappingId = TOPEID.nSupplierItemMapid
    JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnAutoId_pk = TMID.nHsnCodeId_fk
		WHERE TOPEID.npoid_fk = poId
	 GROUP BY TOPEID.apoeachid;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getPoListForIssue` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getPoListForIssue`(
in in_branchId varchar(45),
in in_storeId int,
in in_poNumber text,
in requestType int,
in indentToBranchIds int,
in searchByDate varchar(45),
in minlimitRow int,
in maxlimitRow int
)
BEGIN

SELECT 
    TOPMD.apoid, 
    TOPMD.po_number,
    TOPMD.po_type_fk,
    TMP.poTypeNAme,
    DATE(TOPMD.po_date) AS po_date,
    TOPMD.po_total_items,
    TOPMD.po_total_items_qty,
    TOPMD.po_status,
    TOPEIDFB.apoeachid as eachPoIds,
    TOPEIDFB.item_id_fk  as itemIds,
    TBLI.itemName AS itemName,
    TBLI.itemShortCode AS itemShortCode,
     
    /*TBLSUP.company_name as old1,
    TOPMD.po_to_supplier_id_fk as old2,*/
    (
    SELECT TMS.company_name FROM 
		tbl_supplier_item_mapping TSIM 
	JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk
    WHERE TSIM.nBranchId = indentToBranchIds AND TSIM.nItemId_fk = TOPEIDFB.item_id_fk AND TSIM.isAtPriority = 1 and TSIM.isActive=1
    ) AS company_name,
    
    (
    SELECT TMS.aSupplierId_pk FROM 
		tbl_supplier_item_mapping TSIM 
	JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk
    WHERE TSIM.nBranchId = indentToBranchIds AND TSIM.nItemId_fk = TOPEIDFB.item_id_fk AND TSIM.isAtPriority = 1 and TSIM.isActive=1
    ) AS po_to_supplier_id_fk,
    
    TOPEIDFB.item_order_qty,
    tbl_brch.nbranchcode as nbranchcode_req,
    TOPEIDFB.item_diapter, 
    TOPEIDFB.item_size,
    TOPEIDFB.item_strength,
    TOPEIDFB.item_volume,
    -- TBLSUP.nLocationId_fk,
    (
    SELECT TMS.nLocationId_fk FROM 
		tbl_supplier_item_mapping TSIM 
	JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk
    WHERE TSIM.nBranchId = indentToBranchIds AND TSIM.nItemId_fk = TOPEIDFB.item_id_fk AND TSIM.isAtPriority = 1 and TSIM.isActive=1
    ) AS nLocationId_fk,
    
	TOPMD.po_from_branch_fk as branchId,
    (SELECT TSIM.supplier_item_mappingId FROM  
		tbl_supplier_item_mapping TSIM 
	JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk
    WHERE TSIM.nBranchId = indentToBranchIds AND TSIM.nItemId_fk = TOPEIDFB.item_id_fk AND TSIM.isAtPriority = 1 and TSIM.isActive=1
    )  AS supplierMapId
FROM
   
    tbl_os_po_main_details TOPMD
    JOIN tbl_os_po_each_item_details TOPEIDFB ON TOPEIDFB.npoid_fk = TOPMD.apoid
    JOIN tbl_mst_potypes TMP ON TMP.aPoTypeId = TOPMD.po_type_fk
    JOIN tbl_mst_supplier TBLSUP ON TBLSUP.aSupplierId_pk = TOPMD.po_to_supplier_id_fk
    JOIN tbl_mst_items_details TBLI ON TBLI.aItemId_pk = TOPEIDFB.item_id_fk
    JOIN tblhospitalbranches tbl_brch on tbl_brch.abranchId = TOPMD.po_from_branch_fk

WHERE
 if(in_branchId != '',TOPMD.po_from_branch_fk = in_branchId,true) AND
 if(searchByDate != '',TOPMD.po_date = searchByDate,true) 
        AND TOPMD.instore_fk = in_storeId
        -- AND TOPMD.typeOfRequest = 2 -- NEWLY ADDED
        AND if(in_poNumber != '',TOPMD.po_number = in_poNumber,true)
        AND TOPMD.typeOfRequest = requestType
        AND TOPMD.indentToBranchId = indentToBranchIds
        and if(requestType='2',TOPEIDFB.poItemStatusId<>13,true)  
   group by TOPEIDFB.npoid_fk
    ORDER BY po_to_supplier_id_fk ASC,  TOPMD.apoid DESC
    LIMIT minlimitRow , maxlimitRow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getPOSearchAuthorize_OrderDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getPOSearchAuthorize_OrderDetails`(
in poNo varchar(25),
in poParam int,
in branchId int,
in requestType int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
select 
-- tbl_os_po_main_details TOPMD
		TOPMD.apoid
	, TOPMD.po_number
	, TOPMD.po_status
    , TOPMD.po_from_branch_fk
    , date(TOPMD.po_date) as po_date
    , TOPMD.po_raisedby_fk
    , TOPMD.po_to_supplier_id_fk
    , TOPMD.po_total_items
    , TOPMD.po_total_items_qty
    , TOPMD.instore_fk
    , TMP.poTypeNAme
    , TC.branchHospName as branchName
    , TU.userName
    , TMS.code
    , TMS.company_name as supplierName
   -- , TMS.GSTIN as GSTIN
 --   , TMS.IGSTN as IGSTIN
    , TMS.po_emailId as supplierEmailId
  --  , TMS.pan_no
  --  , TMS.drugl_no
  --  , TMS.credit_period
  --  , TMS.com_address
  --  , TMS.po_address
  --  , TMS.contact_person
    , TMS.mobile_no
 FROM tbl_os_po_main_details TOPMD
	JOIN tblhospitalbranches TC ON TC.abranchId = TOPMD.po_from_branch_fk
    JOIN tbl_user TU ON TU.userId = TOPMD.po_raisedby_fk
    JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TOPMD.po_to_supplier_id_fk
     JOIN tbl_mst_potypes TMP ON TMP.aPoTypeId = TOPMD.po_type_fk
    WHERE 
		CASE 
			WHEN poParam = 1 THEN TOPMD.po_number = poNo
            WHEN poParam = 2 THEN DATE(TOPMD.po_date) = poNo
            ELSE ''
		END
    /*IF(poParam = 1 ,/*PO NUMBER
				TOPMD.po_number = poNo,
            IF(poParam = 2, DATE(TOPMD.po_date) = poNo,''))*/
	AND find_in_Set(TOPMD.po_status,1)
     AND TOPMD.po_from_branch_fk = branchId
      AND TOPMD.typeOfRequest = requestType
    ORDER BY TOPMD.apoid DESC
    LIMIT minlimitRow , maxlimitRow;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getPOSearch_OrderDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getPOSearch_OrderDetails`(
in poNo varchar(25),
in poParam int,
in branchId int,
in requestType int
)
BEGIN
select 
-- tbl_os_po_main_details TOPMD
		TOPMD.apoid
	, TOPMD.po_number
	, TOPMD.po_status
    , TOPMD.po_from_branch_fk
    , date(TOPMD.po_date) as po_date
    , TOPMD.po_raisedby_fk
    , TOPMD.po_to_supplier_id_fk
    , TOPMD.po_total_items
    , TOPMD.po_total_items_qty
    , TOPMD.instore_fk
     , TMP.poTypeNAme
    , THB1.branchHospName as branchName
    , TU.userName
    , TMS.code
    , TMS.company_name as supplierName
   -- , TMS.GSTIN as GSTIN
 --   , TMS.IGSTN as IGSTIN
    , TMS.po_emailId as supplierEmailId
  --  , TMS.pan_no
  --  , TMS.drugl_no
  --  , TMS.credit_period
  --  , TMS.com_address
  --  , TMS.po_address
  --  , TMS.contact_person
    , TMS.mobile_no
    ,'' AS  branchHospName
    , '' AS abranchId
    
    -- references of id's in central w.r.t branch
    ,'' as branchPoNumberInCentral
    ,'' as requestBranchIdInCentral
    ,'' as branchPoDeatilsIdInCentral
    ,'' as poIdInBranchInCentral
    
    ,TOPMD.po_type_fk
    ,TOPMD.po_to_supplier_id_fk,
    TBLSD.storeFlag,
    TBLSD.storeName
 FROM tbl_os_po_main_details TOPMD
	-- JOIN tbl_mst_customers TC ON TC.aCustomerId_pk = TOPMD.po_from_branch_fk
    JOIN tbl_user TU ON TU.userId = TOPMD.po_raisedby_fk
    JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TOPMD.po_to_supplier_id_fk
     JOIN tbl_mst_potypes TMP ON TMP.aPoTypeId = TOPMD.po_type_fk
    -- LEFT JOIN tbl_os_po_main_details_from_branch TOPMDFB ON TOPMDFB.apoid = TOPMD.n_poid_from_branch_fk
    -- LEFT JOIN tblhospitalbranches THB ON THB.abranchId = TOPMDFB.po_from_branch_fk
	LEFT JOIN tblhospitalbranches THB1 ON THB1.abranchId = TOPMD.po_from_branch_fk
   join tbl_mst_storecreation TBLSD ON TBLSD.aStoreId_pk=TOPMD.instore_fk
    WHERE CASE 
			WHEN poParam = 1 THEN TOPMD.po_number = poNo
            WHEN poParam = 2 THEN DATE(TOPMD.po_date) = poNo
            ELSE ''
		END
	AND !find_in_set(TOPMD.po_status,1)
    AND TOPMD.po_from_branch_fk = branchId
     AND TOPMD.typeOfRequest = requestType
    ORDER BY TOPMD.apoid DESC
    LIMIT 0, 25;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getPoVendorAndBranchDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getPoVendorAndBranchDetails`(
in poId int,
in branchId int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
SELECT 
		TOPMD.apoid,
        TOPMD.po_number,
        DATE(TOPMD.po_date) AS po_date,
        DATE(TOPMD.po_expexted_delivery_date) AS po_expexted_delivery_date,
        TU.userName AS preparedUser,
        '' AS deliverInstrn,
		TMS.company_name AS supplierName, 
        TMS.code AS supplierCode, 
        TMS.com_address, TMS.GSTIN AS vendorGSTIN,
        TMS.credit_period,
        THB.bGSTINno,
        THB.branchHospName,
        THB.nbranchcode,
        THB.hospBranch_BillingAddress,
        
        ifnull(TMS.phone_no, '') sup_phone_no,
        ifnull(TMS.fax, '') sup_fax,
        ifnull(TMS.cin, '') sup_cin,
        ifnull(TMS.pan_no, '') sup_pan_no,
        
        ifnull(THB.bhospitalPhNo, '') brch_phone_no,
        ifnull(THB.fax, '') brch_fax,
        ifnull(THB.cin_number, '') brch_cin,
        ifnull(THB.PANNo, '') brch_pan_no,
        
        TUAUTH.userName as authUser
	FROM tbl_os_po_main_details TOPMD
		JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TOPMD.po_to_supplier_id_fk
		JOIN tblhospitalbranches THB ON THB.abranchId = branchId
        JOIN tbl_user TU ON TU.userId = TOPMD.po_raisedby_fk
        JOIN tbl_user TUAUTH ON TUAUTH.userId = TOPMD.po_authorized_by_fk
    WHERE TOPMD.apoid = poId
     LIMIT minlimitRow , maxlimitRow;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getPO_OrderDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getPO_OrderDetails`(
in branchId int,
in requestType int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
select 
-- tbl_os_po_main_details TOPMD
		TOPMD.apoid
	, TOPMD.po_number
	, TOPMD.po_status
    , TOPMD.po_from_branch_fk
    , date(TOPMD.po_date) as po_date
    , TOPMD.po_raisedby_fk
    , TOPMD.po_to_supplier_id_fk
    , TOPMD.po_total_items
    , TOPMD.po_total_items_qty
    , TOPMD.instore_fk
    , TMP.poTypeNAme
    , TC. branchHospName as branchName -- vp
    , TU.userName
    , TMS.code
    , TMS.company_name as supplierName
   -- , TMS.GSTIN as GSTIN
 --   , TMS.IGSTN as IGSTIN
    , TMS.po_emailId as supplierEmailId
  --  , TMS.pan_no
  --  , TMS.drugl_no
  --  , TMS.credit_period
  --  , TMS.com_address
  --  , TMS.po_address
  --  , TMS.contact_person
    , TMS.mobile_no
    , THB.branchHospName
    , THB.abranchId
    
    -- references of id's in central w.r.t branch
    ,TOPMDFB.po_number as branchPoNumberInCentral
    ,TOPMDFB.po_from_branch_fk as requestBranchIdInCentral
    ,TOPMDFB.apoid as branchPoDeatilsIdInCentral
    ,TOPMDFB.n_po_raised_barcnh_po_autopkid_fk as poIdInBranchInCentral
    
    ,TOPMD.po_type_fk
    ,TOPMD.po_to_supplier_id_fk
 FROM tbl_os_po_main_details TOPMD
	JOIN tblhospitalbranches TC ON TC.abranchId = TOPMD.po_from_branch_fk
    JOIN tbl_user TU ON TU.userId = TOPMD.po_raisedby_fk
    JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TOPMD.po_to_supplier_id_fk
     JOIN tbl_mst_potypes TMP ON TMP.aPoTypeId = TOPMD.po_type_fk
    LEFT JOIN tbl_os_po_main_details_from_branch TOPMDFB ON TOPMDFB.apoid = TOPMD.n_poid_from_branch_fk
    LEFT JOIN tblhospitalbranches THB ON THB.abranchId = TOPMDFB.po_from_branch_fk
	-- LEFT JOIN tblhospitalbranches THB1 ON THB1.abranchId = TOPMD.po_from_branch_fk
    
    WHERE !FIND_IN_SET(TOPMD.po_status,1)
     AND TOPMD.po_from_branch_fk = branchId
     AND TOPMD.typeOfRequest = requestType
    ORDER BY TOPMD.apoid DESC
    LIMIT minlimitRow , maxlimitRow;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getPO_OrderToAuthorizeDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getPO_OrderToAuthorizeDetails`(
in branchId int,
in requestType int,
in poItemStatusIds text,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
select 
-- tbl_os_po_main_details TOPMD
		TOPMD.apoid
	, TOPMD.po_number
	, TOPMD.po_status
    , TOPMD.po_from_branch_fk
    , date(TOPMD.po_date) as po_date
    , TOPMD.po_raisedby_fk
    , TOPMD.po_to_supplier_id_fk
    , TOPMD.po_total_items
    , TOPMD.po_total_items_qty
    , TOPMD.instore_fk
    , TMP.poTypeNAme
    , TC. branchHospName as branchName
    , TU.userName
    , TMS.code
    , TMS.company_name as supplierName
   -- , TMS.GSTIN as GSTIN
 --   , TMS.IGSTN as IGSTIN
    , TMS.po_emailId as supplierEmailId
  --  , TMS.pan_no
  --  , TMS.drugl_no
  --  , TMS.credit_period
  --  , TMS.com_address
  --  , TMS.po_address
  --  , TMS.contact_person
    , TMS.mobile_no,
    TBLSD.storeName,
    TBLSD.storeFlag
 FROM tbl_os_po_main_details TOPMD
 	JOIN tbl_os_po_each_item_details TOPEID ON TOPEID.npoid_fk = TOPMD.apoid
 	JOIN tblhospitalbranches TC ON TC.abranchId = TOPMD.po_from_branch_fk
    JOIN tbl_user TU ON TU.userId = TOPMD.po_raisedby_fk
    JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TOPMD.po_to_supplier_id_fk
    JOIN tbl_mst_potypes TMP ON TMP.aPoTypeId = TOPMD.po_type_fk
    join tbl_mst_storecreation TBLSD ON TBLSD.aStoreId_pk=TOPMD.instore_fk
--    WHERE find_in_Set(TOPMD.po_status,'1,9')
-- 	WHERE TOPEID.poItemStatusId IN (1,9)
WHERE find_in_Set(TOPEID.poItemStatusId,poItemStatusIds)
    AND TOPMD.po_from_branch_fk = branchId
    AND TOPMD.typeOfRequest = requestType
    GROUP BY TOPMD.apoid
    ORDER BY TOPMD.apoid DESC
    LIMIT minlimitRow , maxlimitRow;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getPO_OrderToAuthorizeDetailsByPoNumber` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getPO_OrderToAuthorizeDetailsByPoNumber`(
in branchId int,
in poNumber varchar(45),
in searchById varchar(45),
in poStatusId int,
in requestType int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN

select 
-- tbl_os_po_main_details TOPMD
		TOPMD.apoid
	, TOPMD.po_number
	, TOPMD.po_status
    , TOPMD.po_from_branch_fk
    , date(TOPMD.po_date) as po_date
    , TOPMD.po_raisedby_fk
    , TOPMD.po_to_supplier_id_fk
    , TOPMD.po_total_items
    , TOPMD.po_total_items_qty
    , TOPMD.instore_fk
    , TMP.poTypeNAme
    , TC. branchHospName as branchName
    , TU.userName
    , TMS.code
    , TMS.company_name as supplierName
   -- , TMS.GSTIN as GSTIN
 --   , TMS.IGSTN as IGSTIN
    , TMS.po_emailId as supplierEmailId
  --  , TMS.pan_no
  --  , TMS.drugl_no
  --  , TMS.credit_period
  --  , TMS.com_address
  --  , TMS.po_address
  --  , TMS.contact_person
    , TMS.mobile_no,
         TMSC.storeName AS storeName
    
 FROM tbl_os_po_main_details TOPMD
 	JOIN tbl_os_po_each_item_details TOPEID ON TOPEID.npoid_fk = TOPMD.apoid
 	JOIN tblhospitalbranches TC ON TC.abranchId = TOPMD.po_from_branch_fk
    JOIN tbl_user TU ON TU.userId = TOPMD.po_raisedby_fk
    JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TOPMD.po_to_supplier_id_fk
    JOIN tbl_mst_potypes TMP ON TMP.aPoTypeId = TOPMD.po_type_fk
     JOIN tbl_mst_locations TML ON TMS.nLocationId_fk = TML.aLocationId_pk
	JOIN tbl_mst_storecreation TMSC ON TMSC.aStoreId_pk = TOPMD.instore_fk

--    WHERE find_in_Set(TOPMD.po_status,'1,9')
-- 	WHERE TOPEID.poItemStatusId IN (1,9)

WHERE 
	CASE
			WHEN searchById = '1' THEN
				TOPMD.po_number = poNumber and TOPMD.po_from_branch_fk = branchId and TOPMD.po_status= poStatusId AND TOPMD.typeOfRequest = requestType
			WHEN searchById = '2'THEN
				TOPMD.po_date = poNumber and TOPMD.po_from_branch_fk = branchId and TOPMD.po_status= poStatusId AND TOPMD.typeOfRequest = requestType
			WHEN searchById = '3'THEN
				TOPMD.po_to_supplier_id_fk = poNumber and TOPMD.po_from_branch_fk = branchId and TOPMD.po_status= poStatusId AND TOPMD.typeOfRequest = requestType
			WHEN searchById = '4'THEN
				TMS.nLocationId_fk = poNumber and TOPMD.po_from_branch_fk = branchId and TOPMD.po_status= poStatusId AND TOPMD.typeOfRequest = requestType
			ELSE
				''
		END
    
   -- AND if(searchById = '1',TOPMD.po_number = poNumber ,TOPMD.po_date = poNumber)
    GROUP BY TOPMD.apoid
    ORDER BY TOPMD.apoid DESC
    LIMIT minlimitRow , maxlimitRow;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getReservedindentAvialdetailsToEmr` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getReservedindentAvialdetailsToEmr`(
in poMainOrderId int,
in poGrnMainId int
)
BEGIN

if exists (select * from tbl_os_po_main_details where isReserved=1 and apoid=poMainOrderId) then


select TGA.aGrnAvailQtyId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName,
        TGFPEIO.diapter,
        TGFPEIO.serialNumber AS serialNo, 
        TGFPEIO.batchNo AS batchNo,
		DATE(TGFPEIO.expiryDate) AS expiryDate,
        (select counsilId from tbl_os_po_main_details where isReserved=1 and apoid=poMainOrderId) as councilId,
        1 as available
       from 
     tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     join tbl_grn_for_po_orders TBLGPO ON TBLGPO.aGrnId_pk = TGA.nMainGrnId
     where TBLGPO.aGrnId_pk = poGrnMainId and TMID.nStoreId_fk=1;

 else
 select 0 as available;
END if;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getReservedIolItemDetails_toEmr` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getReservedIolItemDetails_toEmr`(
in branchId int,
in diaperSearch varchar(45),
in filterItems int
)
BEGIN
SELECT 
        TGFPEIO.aGrnId_each_pk AS grnEachId,
        TGA.aGrnAvailQtyId AS availId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName,
        IFNULL((TGA.availQty),0) AS availQty,
        DATE(TGFPEIO.expiryDate) AS expiryDate,
        TGFPEIO.batchNo AS batchNo,
        TGFPEIO.strength AS strength,
        TGFPEIO.volume AS volume,
        TGFPEIO.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
        TGA.patName,
        TGA.patientCode,
        TGFPEIO.diapter,
        TGA.surgeryDate
    FROM tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
    WHERE 
     TMID.isActive = 1
    AND TGA.nBranchId = branchId and TMID.nStoreId_fk=1 and TGA.isReserved=1 and if(diaperSearch='',true,TGFPEIO.diapter=diaperSearch) and TGA.availQty <> 0 and  if(filterItems='1',true,TGFPEIO.nGrnTypeId=3)
    GROUP BY  TGA.aGrnAvailQtyId 
    ORDER BY  TMID.aItemId_pk,DATE(TGFPEIO.expiryDate) ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getReservedIolPatientList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getReservedIolPatientList`(
in fromDate date,
in toDate date,
in branchIds text
)
BEGIN
SELECT 
        TGFPEIO.aGrnId_each_pk AS grnEachId,
        TGA.aGrnAvailQtyId AS availId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName,
        DATE(TGFPEIO.expiryDate) AS expiryDate,
        TGFPEIO.batchNo AS batchNo,
        TGFPEIO.serialNumber AS serialNo,
        TGA.patName,
        TGA.patientCode,
        TGFPEIO.diapter,
        DATE(TGA.reservedDate) AS reservedDate
    FROM tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
    WHERE 
     if(branchIds='-1',true,find_in_set(TGA.nBranchId,branchIds)) and TMID.nStoreId_fk=1 and TGA.isReserved=1 and DATE(TGA.reservedDate) between fromDate and toDate
    GROUP BY  TGA.aGrnAvailQtyId 
    ORDER BY  TMID.aItemId_pk,DATE(TGFPEIO.expiryDate) ASC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getReturnedGoodMainList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getReturnedGoodMainList`(
in 	storeId int,
in filterParam int,
in filterVal varchar(50),
in branchId int,
in returnSts int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
SELECT 
	TGRMD.aReturnMainId,
    date(TGRMD.nReturnedDate) as returnedDate,
    TU.userName AS returnedUser,
	TGFPO.grnNumber, 
    date(TGFPO.grnDate) as grnDate, 
    TGFPO.nSupplierId_fk,
    TMS.company_name
    FROM tbl_grn_return_main_details TGRMD
JOIN tbl_grn_return_each_details TGRED ON TGRED.nReturnMainId = TGRMD.aReturnMainId
JOIN tbl_grn_for_po_orders TGFPO ON TGFPO.aGrnId_pk = TGRMD.nMainGrnId
JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TGFPO.nSupplierId_fk
JOIN tbl_user TU ON TU.userId = TGRMD.nReturnedUserId
WHERE 
	CASE
		WHEN filterParam = 1 THEN TMS.code = filterVal
		WHEN filterParam = 2 THEN ''
		ELSE "oops.. Not Avilable"
	END
    AND TGFPO.nStoreId_fk = storeId
    AND TGRMD.nBranchId = branchId
    AND TGRED.returnStatus = returnSts
   LIMIT minlimitRow , maxlimitRow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getreturnedItemsDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getreturnedItemsDetails`(
in returnMainId int,
in returnBranchId int,
in returnStatusId int
)
BEGIN
SELECT 
		TGRED.aReturnEachId 
        ,TGRED.nReturnMainId
        ,TGRED.nMainGrnId
        ,TGRED.nEachGrnId
        ,TGRED.returnQty
        ,TGRED.returnComments
        ,TGRED.returnStatus AS returnStatusId
        ,TMRS.returnStatus AS returnStatus
        ,TGRED.nSupplierMapId AS supplierMapId
        #---
        ,TGFPEIO.nItemId_fk AS itemId
        ,IF(TMSC.storeFlag = 1, TGFPEIO.diapter, TGFPEIO.strength) AS dioOrStr
        ,IF(TMSC.storeFlag = 1, TGFPEIO.size, TGFPEIO.volume) AS sizOrVol
        ,TGFPEIO.batchNo
        ,TGFPEIO.serialNumber
        ,DATE(TGFPEIO.expiryDate) AS expiryDate
        #--
        ,TMID.itemShortCode
        ,TMID.itemName
        ,TSITE.ngrnId_fk AS depoMainGrnId
        ,TSITE.ngrnEachId_fk  AS depoEachGrnId
        ,TSITE.nIssuedFromBranchId_fk AS issuedFromBranchId
        ,TSITE.nIssuedToBranchId_fk AS issuedToBranchId
       -- ,TGFPEIO.nEachTransferId
	FROM
		tbl_grn_return_each_details TGRED
        JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGRED.nEachGrnId
        JOIN tbl_mst_returnstatus TMRS ON TMRS.areturnstausId = TGRED.returnStatus
        JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TGFPEIO.nItemId_fk
        LEFT JOIN tbl_stocks_issued_to_emr TSITE ON TSITE.issedStock_Pk = TGFPEIO.nEachTransferId
		JOIN tbl_mst_storecreation TMSC ON TMSC.aStoreId_pk = TGFPEIO.nStoreId_fk

	WHERE TGRED.nReturnMainId = returnMainId
		AND TGRED.nReturnFromBranchId = returnBranchId
        AND TGRED.returnStatus = returnStatusId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getReturnedItemsList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getReturnedItemsList`(
in returnStatusId int,
in storeId int,
in loggedinBranchId int,
in returnedToBranchIds int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
SELECT 
		 TGRMD.aReturnMainId AS mainReturnId
		,GROUP_CONCAT(TGRED.aReturnEachId) AS eachReturnIds
        ,TGRMD.returnNumber
        ,DATE(TGRMD.nReturnedDate) AS returnDate
        ,TGRMD.returnReason
        ,TGRMD.nReturnedUserId        
        ,THB.abranchId AS returnedToBranchId
        ,THB.branchHospName AS returnedToBranch
        ,TU.userId AS returnedByUserId
        ,TU.userName AS returnedByUserName 
   --    ,THBF.branchHospName as returnFromBranch
	FROM tbl_grn_return_main_details TGRMD
		JOIN tbl_grn_return_each_details TGRED ON TGRED.nReturnMainId = TGRMD.aReturnMainId
       left JOIN tblhospitalbranches THB ON THB.abranchId = TGRMD.nReturnToBranchId
        JOIN tbl_user TU ON TU.userId = TGRMD.nReturnedUserId
	-- 	left JOIN tblhospitalbranches THBF ON THBF.abranchId = TGRMD.nBranchId

	WHERE TGRED.returnStatus = returnStatusId
      AND IF(storeId <> 0, TGRED.nStoreId = storeId, TRUE)
    -- AND IF(returnedToBranchIds <> -1,TGRMD.nReturnToBranchId = returnedToBranchIds,TRUE)
     AND if(loggedinBranchId <> 0 ,TGRMD.nBranchId = loggedinBranchId,true) 
    GROUP BY TGRMD.aReturnMainId
	ORDER BY TGRMD.aReturnMainId DESC
     LIMIT minlimitRow , maxlimitRow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getstocIssueEachItemDetailsWrtCode` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getstocIssueEachItemDetailsWrtCode`(
in itemShortCode varchar(100),
in branchId int,
in in_storeId int,
in in_grnAvailableId varchar(250)
)
BEGIN

declare isMandatoryBatchAndExpiryflag int;

set  isMandatoryBatchAndExpiryflag = (select isMandatoryBatchAndExpiry from tbl_mst_storecreation  where isActive = 1 and aStoreId_pk = in_storeId limit 1);


if(in_storeId = '1')
then

if(isMandatoryBatchAndExpiryflag = '1')
then


SELECT 
        TGFPEIO.aGrnId_each_pk AS grnEachId,
        TGA.aGrnAvailQtyId AS availId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName,
        IFNULL((TGA.availQty),0) AS availQty,
        DATE(TGFPEIO.expiryDate) AS expiryDate,
        TGFPEIO.batchNo AS batchNo,
        TGFPEIO.strength AS strength,
        TGFPEIO.volume AS volume,
        TGFPEIO.diapter,
        TGFPEIO.size,
        TGFPEIO.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber
    ,
    
    TGFPEIO.strength,
    TGFPEIO.volume,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
          IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice ,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
    
    FROM tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
    WHERE TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
    -- AND TMID.nStoreId_fk = 2
    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGA.isReserved=0
     AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)

    GROUP BY  TGA.aGrnAvailQtyId ,TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) , TGFPEIO.serialNumber
    ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
    
  else
  
  
SELECT 
        TGFPEIO.aGrnId_each_pk AS grnEachId,
        TGA.aGrnAvailQtyId AS availId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName,
        IFNULL((TGA.availQty),0) AS availQty,
        DATE(TGFPEIO.expiryDate) AS expiryDate,
        TGFPEIO.batchNo AS batchNo,
        TGFPEIO.strength AS strength,
        TGFPEIO.volume AS volume,
        TGFPEIO.diapter,
        TGFPEIO.size,
        TGFPEIO.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber,
    
    TGFPEIO.strength,
    TGFPEIO.volume,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
          IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice ,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
    FROM tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
    WHERE TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
    -- AND TMID.nStoreId_fk = 2
    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGA.isReserved=0
     AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)

    GROUP BY  TGA.aGrnAvailQtyId , TGFPEIO.serialNumber
    ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
end if;

else 


if(isMandatoryBatchAndExpiryflag = '1')

then 


SELECT 
        TGFPEIO.aGrnId_each_pk AS grnEachId,
        TGA.aGrnAvailQtyId AS availId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName,
        IFNULL((TGA.availQty),0) AS availQty,
        DATE(TGFPEIO.expiryDate) AS expiryDate,
        TGFPEIO.batchNo AS batchNo,
        TGFPEIO.strength AS strength,
        TGFPEIO.volume AS volume,
        TGFPEIO.diapter,
        TGFPEIO.size,
        TGFPEIO.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber,
    
    TGFPEIO.strength,
    TGFPEIO.volume,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
          IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice ,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
    FROM tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
    WHERE TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
    -- AND TMID.nStoreId_fk = 2
    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGA.isReserved=0
     AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)
 
    GROUP BY  TGA.aGrnAvailQtyId ,TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) 
    ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
    
    
else


SELECT 
        TGFPEIO.aGrnId_each_pk AS grnEachId,
        TGA.aGrnAvailQtyId AS availId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName,
        IFNULL((TGA.availQty),0) AS availQty,
        DATE(TGFPEIO.expiryDate) AS expiryDate,
        TGFPEIO.batchNo AS batchNo,
        TGFPEIO.strength AS strength,
        TGFPEIO.volume AS volume,
        TGFPEIO.diapter,
        TGFPEIO.size,
        TGFPEIO.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber ,
    
    TGFPEIO.strength,
    TGFPEIO.volume,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
          IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice ,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
    FROM tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
    WHERE TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
    -- AND TMID.nStoreId_fk = 2
    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGA.isReserved=0
     AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)
 
    GROUP BY  TGA.aGrnAvailQtyId 
    ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;    
end if;

end if;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getstocIssueItemDetailsWrtCode` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getstocIssueItemDetailsWrtCode`(
in itemShortCode varchar(100),
in branchId int,
in in_storeId int,
in in_grnAvailableId varchar(250)
)
BEGIN



declare isMandatoryBatchAndExpiryflag int;

set  isMandatoryBatchAndExpiryflag = (select isMandatoryBatchAndExpiry from tbl_mst_storecreation  where isActive = 1 and aStoreId_pk = in_storeId limit 1);


if(in_storeId = '1')
then
 
 if(isMandatoryBatchAndExpiryflag = '1')
 then
 
SELECT  
        -- TGA.aGrnAvailQtyId AS GENAVAILID,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName, 
        IFNULL(SUM(TGA.availQty),0) AS availQty,
        TBLSC.storeFlag,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber ,
    
    TGFPEIO.strength,
    TGFPEIO.volume,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
          IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice ,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
    FROM tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     JOIN tbl_mst_storecreation TBLSC ON TBLSC.aStoreId_pk = TMID.nStoreId_fk
     JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
    
    WHERE TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
  --  AND TMID.nStoreId_fk = 2 
    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGA.isReserved=0
     AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)
    GROUP BY TGA.nItemId , TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) , TGFPEIO.serialNumber
        ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
        
        
 else 
 
 
SELECT  
        -- TGA.aGrnAvailQtyId AS GENAVAILID,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName, 
        IFNULL(SUM(TGA.availQty),0) AS availQty,
        TBLSC.storeFlag,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber,
    
    TGFPEIO.strength,
    TGFPEIO.volume,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
          IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice ,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
    FROM tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     JOIN tbl_mst_storecreation TBLSC ON TBLSC.aStoreId_pk = TMID.nStoreId_fk
     JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
    WHERE TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
  --  AND TMID.nStoreId_fk = 2 
    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGA.isReserved=0
     AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)
    GROUP BY TGA.nItemId ,  TGFPEIO.serialNumber
        ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
 
 end if;
 
 
else 

if(isMandatoryBatchAndExpiryflag = '1')
then


SELECT   
        -- TGA.aGrnAvailQtyId AS GENAVAILID,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName, 
        IFNULL(SUM(TGA.availQty),0) AS availQty,
        TBLSC.storeFlag,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber ,
    
    TGFPEIO.strength,
    TGFPEIO.volume,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
          IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice ,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId 
    FROM tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     JOIN tbl_mst_storecreation TBLSC ON TBLSC.aStoreId_pk = TMID.nStoreId_fk
     JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
    WHERE TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
  --  AND TMID.nStoreId_fk = 2 
    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGA.isReserved=0
     AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)
    GROUP BY TGA.nItemId , TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) 
        ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
        
 else
 
SELECT   
        -- TGA.aGrnAvailQtyId AS GENAVAILID,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName, 
        IFNULL(SUM(TGA.availQty),0) AS availQty,
        TBLSC.storeFlag,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber ,
    
    TGFPEIO.strength,
    TGFPEIO.volume,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
          IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice ,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
    FROM tbl_mst_items_details TMID 
     JOIN tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     JOIN tbl_mst_storecreation TBLSC ON TBLSC.aStoreId_pk = TMID.nStoreId_fk
     JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
    WHERE TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
  --  AND TMID.nStoreId_fk = 2 
    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGA.isReserved=0
     AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)
    GROUP BY TGA.nItemId 
        ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
end if;

end if;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getStocIssueItemDetailsWrtItemName_Autosugest` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getStocIssueItemDetailsWrtItemName_Autosugest`(
in itemShortCode varchar(100),
in branchId int,
in in_storeId int
)
BEGIN




declare isMandatoryBatchAndExpiryflag int;

set  isMandatoryBatchAndExpiryflag = (select isMandatoryBatchAndExpiry from tbl_mst_storecreation  where isActive = 1 and aStoreId_pk = in_storeId limit 1);
if(in_storeId = '1')
then

if(isMandatoryBatchAndExpiryflag = '1')
then
SELECT 
    group_concat(TGA.aGrnAvailQtyId) AS grnAvailableId,
    TMID.aItemId_pk AS itemId,
    TMID.nStoreId_fk AS storeId,
    TMID.itemShortCode AS itemShortCode,
    TMID.itemName AS itemName,
    IFNULL(SUM(TGA.availQty), 0) AS availQty,
    TGFPEIO.strength,
    TGFPEIO.volume,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
          IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice ,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
FROM
    tbl_mst_items_details TMID
        JOIN
    tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
        JOIN
    tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
WHERE
    TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
        AND TMID.isActive = 1
        AND TMID.itemName LIKE CONCAT(itemShortCode, '%')
        AND TGA.nBranchId = branchId
		
GROUP BY TGA.nItemId , TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) , TGFPEIO.serialNumber limit 10;

else

SELECT 
    group_concat(TGA.aGrnAvailQtyId) AS grnAvailableId,
    TMID.aItemId_pk AS itemId,
    TMID.nStoreId_fk AS storeId,
    TMID.itemShortCode AS itemShortCode,
    TMID.itemName AS itemName,
    IFNULL(SUM(TGA.availQty), 0) AS availQty,
    TGFPEIO.strength,
    TGFPEIO.volume,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
          IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice ,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
FROM
    tbl_mst_items_details TMID
        JOIN
    tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
        JOIN
    tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
     JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
WHERE
    TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
        AND TMID.isActive = 1
        AND TMID.itemName LIKE CONCAT(itemShortCode, '%')
        AND TGA.nBranchId = branchId
		
GROUP BY TGA.nItemId ,  TGFPEIO.serialNumber limit 10;
end if;

else 
 
 if(isMandatoryBatchAndExpiryflag = '1')
 then 
 SELECT 
    group_concat(TGA.aGrnAvailQtyId) AS grnAvailableId,
    TMID.aItemId_pk AS itemId,
    TMID.nStoreId_fk AS storeId,
    TMID.itemShortCode AS itemShortCode,
    TMID.itemName AS itemName,
    IFNULL(SUM(TGA.availQty), 0) AS availQty,
    TGFPEIO.strength,
    TGFPEIO.volume,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber ,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
         IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
FROM
    tbl_mst_items_details TMID
        JOIN
    tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
        JOIN
    tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
    JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
WHERE
    TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
        AND TMID.isActive = 1
        AND TMID.itemName LIKE CONCAT(itemShortCode, '%')
        AND TGA.nBranchId = branchId
		
GROUP BY TGA.nItemId , TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate)  limit 10;

else

SELECT 
    group_concat(TGA.aGrnAvailQtyId) AS grnAvailableId,
    TMID.aItemId_pk AS itemId,
    TMID.nStoreId_fk AS storeId,
    TMID.itemShortCode AS itemShortCode,
    TMID.itemName AS itemName,
    IFNULL(SUM(TGA.availQty), 0) AS availQty,
    TGFPEIO.strength,
    TGFPEIO.volume,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber ,
     TGFPEIO.size ,
      TGFPEIO.diapter,
       TGFPEIO.nSupplierItemMapid AS  supplierMapid,
       IHSNCD.hsnCode,
         IHSNCD.gstPercentage,
         IHSNCD.sgstPercentage,
         IHSNCD.cgstPercentage,
         IHSNCD.igstPercentage,
         ISMID.purchasePrice AS  unitPrice,
    TGFPEIO.nGrnId_fk as grnMainId,
    TGFPEIO.aGrnId_each_pk as eachGrnId,
    TGFPEIO.nPoIdCentral_fk as poMainId,
    TGFPEIO.nPoEachId as poEachId,
    TBLGMD.grnNumber ,
    TGFPEIO.nSupplierId_fk as supplierId
FROM
    tbl_mst_items_details TMID
        JOIN
    tbl_grn_availqty TGA ON TGA.nItemId = TMID.aItemId_pk AND TGA.nStoreId = in_storeId
        JOIN
    tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TGA.nEachGrnId
    JOIN
    tbl_supplier_item_mapping ISMID ON ISMID.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
    JOIN
    tbl_mst_hsn_codes IHSNCD ON IHSNCD.aHsnAutoId_pk = ISMID.nhsnId_fk
    JOIN
    tbl_grn_for_po_orders TBLGMD ON TBLGMD.aGrnId_pk = TGFPEIO.nGrnId_fk
WHERE
    TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
        AND TMID.isActive = 1
        AND TMID.itemName LIKE CONCAT(itemShortCode, '%')
        AND TGA.nBranchId = branchId
		
GROUP BY TGA.nItemId limit 10;
 end if;

end if;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getStocIssueListToBranchesDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getStocIssueListToBranchesDetails`(
in startDate date,
in endDate date,
in branchId varchar(250),
in storeId varchar(250)

)
BEGIN


SELECT 
    IFNULL(TBLDSID.batchNo, '') AS batchNo,
    DATE(TBLDSID.expiryDate) AS expiryDate,
    TBLDSID.serialNo,
    TBLID.itemName,
    TBLSISD.storeName,
    TBLSIBD.branchHospName,
    TBLDSID.issuedQty,
    DATE(TBLDSID.insertedDate) AS issuedDate
FROM
    tbl_stocks_issued_to_emr TBLDSID
        JOIN
    tbl_mst_items_details TBLID ON TBLID.aItemId_pk = TBLDSID.nItemId_fk
        AND IF(storeId = - 1,
        TRUE,
        TBLDSID.nStoreId_fk = storeId)
        AND IF(branchId = - 1,
        TRUE,
        TBLDSID.nIssuedToBranchId_fk = branchId)
        JOIN
    tbl_mst_storecreation TBLSISD ON TBLSISD.aStoreId_pk = TBLDSID.nStoreId_fk
        JOIN
    tblhospitalbranches TBLSIBD ON TBLSIBD.abranchId = TBLDSID.nIssuedToBranchId_fk
WHERE
    DATE(TBLDSID.insertedDate) BETWEEN startDate AND endDate
ORDER BY TBLDSID.nIssuedToBranchId_fk , DATE(TBLDSID.insertedDate) , TBLDSID.nStoreId_fk
;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getStocksTransferredDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getStocksTransferredDetails`(
in mainTransferId int,
in approvalStatus int,
in transferTypes int
)
BEGIN
 SELECT 
			TSITE.nMainStockTrnsfrId AS mainStckTrnfrId,
            TSITE.issedStock_Pk AS eachStckTrnfrId,
            TSITE.nStoreId_fk AS storeId,
            TMS.storeName AS storeName,
            TSITE.nItemId_fk AS itemId,
            TMID.itemShortCode,
            TMID.itemName,
            IF(TMS.storeFlag = 1,
				TGFPEIO.diapter,
                TGFPEIO.strength
            ) AS diaStr,
            IF(TMS.storeFlag = 1,
				TGFPEIO.size,
                TGFPEIO.volume
            ) AS sizVol,
            issuedQty AS transfredQty,
            TGFPEIO.serialNumber,
            TGFPEIO.batchNo,
            DATE(TGFPEIO.expiryDate) AS expiryDate,
            /*IF(
				isApproved = 0,
                'Waiting',
                IF(isApproved = 1,
                'Approved',
                IF(isApproved = 2,
                'Rejected',
                '')
                )
            ) AS statuses,*/
            TMSTS.statusname AS statuses,
            TGFPO.grnNumber,
            TSTMD.dcNumber AS dcNumber,
            DATE(TSTMD.dcDate) AS dcDate,
            TGFPEIO.nSupplierItemMapid AS supplierMapId,
           TGFPO.nPoIdCentral_fk,
          TGFPO.nSupplierId_fk, 
          TGFPEIO.nPoEachId,
          TMS.storeFlag
    FROM 
		tbl_stocks_issued_to_emr TSITE
		JOIN tbl_stock_transfer_maindetails TSTMD ON TSTMD.aMainStckTrnfrId = TSITE.nMainStockTrnsfrId
        JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TSITE.nStoreId_fk
        JOIN tbl_mst_items_details TMID ON TSITE.nItemId_fk = TMID.aItemId_pk
        JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.aGrnId_each_pk = TSITE.ngrnEachId_fk
        JOIN tbl_grn_for_po_orders TGFPO ON TGFPO.aGrnId_pk = TGFPEIO.nGrnId_fk
        JOIN tbl_mst_stocktransferstatus TMSTS ON TMSTS.aStockTrnsfMstId = TSITE.isApproved
    WHERE TSITE.tranferType = transferTypes -- 3
    AND IF(approvalStatus = -1,
			FIND_IN_SET(isApproved, 
			(SELECT GROUP_CONCAT(isApproved) FROM tbl_stocks_issued_to_emr))
            , isApproved = approvalStatus)
	AND TSITE.nMainStockTrnsfrId = mainTransferId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getStockTranferWitoutIndent` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getStockTranferWitoutIndent`(
in transferTypes int,
in frmbranchId int,
in toBranchId int,
in transferDate datetime,
in approvalStatus int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
SELECT 
			TSITE.nMainStockTrnsfrId AS mainStckTrnfrId,
            GROUP_CONCAT(TSITE.issedStock_Pk) AS eachStckTrnfrId,
            THB1.abranchId AS fromBranchId,
			THB1.branchHospName AS fromHospName,
            THB2.abranchId AS toBranchId,
			THB2.branchHospName AS toHospName,
            nStoreId_fk AS storeId,
            TMS.storeName AS storeName,
           --  SUM(issuedQty) AS transfrQty,
            COUNT(issuedQty) AS transfrQty,
            DATE(TSITE.insertedDate) AS transferDate,
            TU.userName,
            TSTMD.dcNumber AS dcNumber,
            DATE(TSTMD.dcDate) AS dcDate,
            TMS.storeFlag
    FROM 
		tbl_stocks_issued_to_emr TSITE
	JOIN tbl_stock_transfer_maindetails TSTMD ON TSTMD.aMainStckTrnfrId = TSITE.nMainStockTrnsfrId
	JOIN tblhospitalbranches THB1 ON THB1.abranchId = TSITE.nIssuedFromBranchId_fk
    JOIN tblhospitalbranches THB2 ON THB2.abranchId = TSITE.nIssuedToBranchId_fk
    JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TSITE.nStoreId_fk
    JOIN tbl_user TU ON TU.userId = TSITE.nUserId_fk
    WHERE tranferType = transferTypes -- 3
		-- AND isApproved = approvalStatus
         AND IF(approvalStatus = -1,
			FIND_IN_SET(isApproved, 
			(SELECT GROUP_CONCAT(isApproved) FROM tbl_stocks_issued_to_emr))
            , isApproved = approvalStatus)
        AND if(frmbranchId='-1',true,TSITE.nIssuedFromBranchId_fk = frmbranchId) 
        AND if(toBranchId='-1',true,TSITE.nIssuedToBranchId_fk	 = toBranchId) 
	-- AND nMainStockTrnsfrId >= 22
    GROUP BY TSITE.nMainStockTrnsfrId, TSITE.nStoreId_fk
    ORDER BY TSITE.nMainStockTrnsfrId DESC
      LIMIT minlimitRow , maxlimitRow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getStockWithoutIndentGrnMainDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getStockWithoutIndentGrnMainDetails`(
in receivedBranchId int,
IN transferredBranchId INT,
in minlimitRow int,
in maxlimitRow int,
in reqType int
)
BEGIN
SELECT 
	TGFPO.aGrnId_pk AS mainGrnId,
    GROUP_CONCAT(TGFPEIO.aGrnId_each_pk) AS eachGrnIds,
	TGFPO.grnNumber AS grnNumber,
    DATE(TGFPO.grnDate) AS grnDate,
    TGFPEIO.nStoreId_fk AS storeId,
	TMS.storeName,
	COUNT(*) AS itemCount,
    TU.userName AS receivedByUserName,
    THB.branchHospName AS receivedFromBranch,
    TGFPEIO.ngrnBranchId_fk as acceptedBranch
 FROM tbl_grn_for_po_each_item_orders TGFPEIO 
	JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk 	= TGFPEIO.nStoreId_fk
    JOIN tbl_grn_for_po_orders TGFPO ON TGFPO.aGrnId_pk = TGFPEIO.nGrnId_fk
    JOIN tbl_user TU ON TU.userId = TGFPEIO.nUserId_fk
    JOIN tbl_stocks_issued_to_emr TSITE ON TSITE.issedStock_Pk = TGFPEIO.nEachTransferId
    JOIN tblhospitalbranches THB ON THB.abranchId = TSITE.nIssuedFromBranchId_fk
WHERE TGFPEIO.nGrnTypeId = reqType -- 3 
		AND TGFPEIO.ngrnBranchId_fk = receivedBranchId
        AND if(transferredBranchId='-1',true,TSITE.nIssuedFromBranchId_fk = transferredBranchId) 
GROUP BY TGFPEIO.nGrnId_fk, TGFPEIO.nStoreId_fk
ORDER BY TGFPEIO.nGrnId_fk DESC
 LIMIT minlimitRow , maxlimitRow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getStoreIds` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getStoreIds`(
out getStoreIds varchar(250)
)
BEGIN

set getStoreIds = (select group_concat(aStoreId_pk) as storeIds from tbl_mst_storecreation);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getSubmodulesList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getSubmodulesList`(
in moduleIds int
)
BEGIN
SELECT * FROM tbl_modules
		WHERE isActive = 1 AND isDeleted = 0 AND moduleId = moduleIds AND
        (isHavingSubModule = 2) ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getSupplierMappedItemsList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getSupplierMappedItemsList`(
in storeId int,
in branchId int,
in supplierId int,
in reqFrom int -- 1: PO, 2: Indent
)
BEGIN
/*SELECT 
	TMID.aItemId_pk AS itemId,
    TMID.itemShortCode AS itemShortCode,
    TMID.itemName AS itemName,
    '' AS requiredQty
FROM tblhospitalbranches THB
	JOIN tbl_supplier_item_mapping TSIM ON TSIM.nBranchId = THB.abranchId
	JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TSIM.nItemId_fk AND TSIM.isActive = 1 AND TMID.isActive = 1
	JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TMID.nStoreId_fk
WHERE TMS.aStoreId_pk = storeId
	AND THB.abranchId = branchId
	AND TSIM.nSupplierId_fk = supplierId;*/
    IF(reqFrom = 2) THEN
    SELECT 
    IFNULL(t1.aItemId_pk, '') aItemId_pk,
    -- IFNULL(t1.itemCode, '') itemCode,
    IFNULL(t1.itemShortCode, '') itemShortCode,
    IFNULL(t1.itemName, '') itemName,
    IFNULL(t1.strength, '') DiopterORStrength,
    IFNULL(t1.volume, '') SizeORVolume,
    '' AS orderQty,
    -- IFNULL(t1.ROL, '0') ROL,
    -- -IFNULL(t1.ROQ, '0') ROQ,
    -- IFNULL(t1.UNIT, '') UNIT, 
    IFNULL(t2.supplier_item_mappingId, '') supplier_item_mappingId,
    IFNULL(t2.nSupplierId_fk, '0') nSupplierId_fk
    -- IFNULL(t2.purchasePrice, '0') purchasePrice,
    -- IFNULL(t2.sellingPrice, '0') sellingPrice,
    -- IFNULL(t2.MRP, '0') MRP,
    -- IFNULL(t2.freeQty, '0') freeQty,
    -- IFNULL(t3.hsnCode, '') hsnCode,
    -- IFNULL(t3.gstPercentage, '0') gstPercentage,
    -- IFNULL(t3.sgstPercentage, '0') sgstPercentage,
    -- IFNULL(t3.cgstPercentage, '0') cgstPercentage,
    -- IFNULL(t3.igstPercentage, '0') igstPercentage,
    -- IFNULL(t2.supplier_item_mappingId, '') supplier_item_mappingId
FROM
    (SELECT 
        *
    FROM
        tbl_mst_items_details
    WHERE
        /*aItemId_pk = itemid AND*/ nStoreId_fk = storeId
	) t1
        JOIN
    tbl_supplier_item_mapping t2 ON t2.nItemId_fk = t1.aItemId_pk and   t2.isActive = 1 AND t2.isDeleted = 0
		AND t2.nSupplierId_fk = supplierId  
        AND t2.nBranchId = branchId
        JOIN
    tbl_mst_hsn_codes t3 ON t3.aHsnAutoId_pk = t2.nhsnId_fk;
    ELSE IF(reqFrom = 1) THEN
    SELECT 
    IFNULL(t1.aItemId_pk, '') aItemId_pk,
    -- IFNULL(t1.itemCode, '') itemCode,
    IFNULL(t1.itemShortCode, '') itemShortCode,
    IFNULL(t1.itemName, '') itemName,
    IFNULL(t1.strength, '') DiopterORStrength,
    IFNULL(t1.volume, '') SizeORVolume,
    '' AS orderQty,
     IFNULL(t1.ROL, '0') ROL,
    IFNULL(t1.ROQ, '0') ROQ,
     IFNULL(t1.UNIT, '') UNIT, 
    IFNULL(t2.supplier_item_mappingId, '') supplier_item_mappingId,
    IFNULL(t2.nSupplierId_fk, '0') nSupplierId_fk,
     IFNULL(t2.purchasePrice, '0') purchasePrice,
     IFNULL(t2.sellingPrice, '0') sellingPrice,
     IFNULL(t2.MRP, '0') MRP,
     IFNULL(t2.freeQty, '0') freeQty,
     IFNULL(t3.hsnCode, '') hsnCode,
     IFNULL(t3.gstPercentage, '0') gstPercentage,
     IFNULL(t3.sgstPercentage, '0') sgstPercentage,
     IFNULL(t3.cgstPercentage, '0') cgstPercentage,
     IFNULL(t3.igstPercentage, '0') igstPercentage,
     IFNULL(t2.supplier_item_mappingId, '') supplier_item_mappingId
FROM
    (SELECT 
        *
    FROM
        tbl_mst_items_details
    WHERE
        /*aItemId_pk = itemid AND*/ nStoreId_fk = storeId
	) t1
        JOIN
    tbl_supplier_item_mapping t2 ON t2.nItemId_fk = t1.aItemId_pk and   t2.isActive = 1 AND t2.isDeleted = 0
		AND t2.nSupplierId_fk = supplierId  
        AND t2.nBranchId = branchId
        JOIN
    tbl_mst_hsn_codes t3 ON t3.aHsnAutoId_pk = t2.nhsnId_fk;
END IF;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getSuppliersList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getSuppliersList`(in supFlag int)
BEGIN
SELECT 
 aSupplierId_pk,
    aSupplierId_pk as supplierId, code, company_name ,
    GSTIN as gstin,
    mobile_no as mobileNo,
    credit_period as creditPeriod,
    emailId as emailId,
    po_emailId as poEmailId,
    IGSTN as igstin,
    registration_type as registrationType,
    nBusiness_typeId_fk as businessType,
     nLocationId_fk as  location  ,
     com_address as commAddress,
     po_address as poAddress,
   (select group_concat(nStoreId_fk) as storeIds   from tbl_supplier_store_mapping where isActive=1 and nSupplierId_fk=aSupplierId_pk) as storeIds,
   company_name as supplierName
FROM
    tbl_mst_supplier
WHERE
    isActive = 1 AND isDeleted = 0
        AND supplierFlag = supFlag
        
       order by  aSupplierId_pk desc;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getSupplierWiseOutStanding` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getSupplierWiseOutStanding`(
in dateToFilter date
)
BEGIN


SELECT 
	T2.company_name as venderName,
    T2.credit_period as creditPeriod,  
    sum(if(datediff(dateToFilter,T1.invoiceDate)>T2.credit_period=true,ifnull(T1.invoiceAmount,0)+ifnull(T1.frightCharges,0),0)) as outCreditPerionAmt,
	sum(if(datediff(dateToFilter,T1.invoiceDate)<=T2.credit_period=true,ifnull(T1.invoiceAmount,0)+ifnull(T1.frightCharges,0),0)) as inCreditPerionAmt,
     sum(if(datediff(dateToFilter,T1.invoiceDate)>T2.credit_period=true,1,0)) as outCreditPerionCount,
	sum(if(datediff(dateToFilter,T1.invoiceDate)<=T2.credit_period=true,1,0)) as inCreditPerionCount,
    T3.totalCreditAmount,
    T3.id as vendorCreditPriId,
      concat('[',group_concat(concat('{',concat('"grnId"',':',T1.aGrnId_pk),',',concat('"invoiceDate"',':',concat('"',date(T1.invoiceDate),'"')),',',concat('"daysToPay"',':',if(T2.credit_period-datediff(dateToFilter,T1.invoiceDate) like ('%-%'),-1,T2.credit_period-datediff(dateToFilter,T1.invoiceDate))),',',concat('"invoiceAmt"',':',ifnull(T1.invoiceAmount,0)+ifnull(T1.frightCharges,0)),'}')),']')   as paymentsGrns
FROM 
    tbl_grn_for_po_orders T1
        JOIN
    tbl_mst_supplier T2 ON T1.nSupplierId_fk = T2.aSupplierId_pk and T1.isPaid=0 and T1.nPoType_fk=1 -- and T1.ngrnBranchId_fk=in_branchId
    LEFT JOIN
    tbl_vender_outstand_payment_main_transaction T3 ON T3.vendorId=T1.nSupplierId_fk   and T3.branchId='-1'
group by T1.nSupplierId_fk   ;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getSupplier_CreditAmount` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getSupplier_CreditAmount`(
in in_supplierId int,
in in_branchId int
)
BEGIN

SELECT 
   totalCreditAmount
FROM
    tbl_vender_outstand_payment_main_transaction
WHERE
    branchId = in_branchId
        AND vendorId = in_supplierId;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getSupplier_ItemMappedDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getSupplier_ItemMappedDetails`(
in supplierId int,
in storeIds varchar(45),
in branchId int
)
BEGIN

SELECT 
   TBLID.itemName,
   TBIM.supplier_item_mappingId AS itemMapId,
   TBLSC.storeName,
   TBIM.purchasePrice,
   TBLS.credit_period,
   TBLHC.gstPercentage,TBIM.TATinDays,
   TBIM.isActive
FROM
  tbl_mst_supplier TBLS
       join 
	tbl_supplier_item_mapping TBIM ON TBIM.nSupplierId_fk = TBLS.aSupplierId_pk 
       
       JOIN 
       tbl_mst_items_details TBLID ON TBLID.aItemId_pk=TBIM.nItemId_fk
       JOIN 
       tbl_mst_hsn_codes TBLHC ON TBLHC.aHsnAutoId_pk=TBIM.nhsnId_fk
      JOIN
     tbl_mst_storecreation TBLSC ON TBLSC.aStoreId_pk = TBLID.nStoreId_fk
    where TBLS.aSupplierId_pk=supplierId and if(storeIds='0',true,find_in_set(TBLID.nStoreId_fk,storeIds))    and TBIM.nBranchId=branchId
    order by    TBIM.isActive desc
    ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getSupplier_previous_itemDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getSupplier_previous_itemDetails`(
in typeOfrequest int,
in storeId int,
in branchId int,
in inSupplierId int
)
BEGIN

SELECT 
    T2.item_id_fk AS itemId,
    IF(T1.instore_fk = '1',
        item_diapter,
        item_strength) AS diaStrenth,
    IF(T1.instore_fk = '1',
        item_size,
        item_volume) AS sizeValume,
        T2.item_order_qty as orderQty
FROM 
   (select *  from tbl_os_po_main_details T0 where  T0.typeOfRequest = typeOfrequest and T0.instore_fk = storeId and T0.po_from_branch_fk = branchId and T0.po_to_supplier_id_fk = inSupplierId ORDER BY T0.apoid DESC LIMIT 1) T1
        JOIN
    tbl_os_po_each_item_details T2 ON T1.apoid = T2.npoid_fk

;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getSupplier_storeMappingDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getSupplier_storeMappingDetails`()
BEGIN


SELECT 
    TBLS.mobile_no,
    TBLS.company_name,
    TBLS.contact_person,
    storeName,
    TBLS.aSupplierId_pk,
  ifnull(TBLSM.nStoreId_fk,0)  as nStoreId_fk
FROM
    tbl_mst_supplier TBLS
	left   JOIN
    tbl_supplier_store_mapping TBLSM ON TBLSM.nSupplierId_fk = TBLS.aSupplierId_pk
       left JOIN
    tbl_mst_storecreation TBLSC ON TBLSC.aStoreId_pk = TBLSM.nStoreId_fk
GROUP BY TBLSM.nSupplierId_fk , TBLSM.nStoreId_fk;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getTabMasterReturnType` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getTabMasterReturnType`()
BEGIN
SELECT TMRT.aReturnTypeId AS returnTypeId, TMRT.returnTypeName AS returnType
FROM tbl_mst_returntypes AS TMRT
WHERE TMRT.isActive = 1 AND TMRT.isDeleted = 0;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getThePaidToVendorTransactions` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getThePaidToVendorTransactions`(
in vendorCreditPriId int
)
BEGIN


SELECT 
    paymentDate, paidAmount, paymentRemarks,isCreditOrDebit
FROM
    tbl_vender_outstand_payment_each_transaction
WHERE
    mainTrancId = vendorCreditPriId
ORDER BY paymentDate desc,id DESC
LIMIT 20;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getTotPosForDashBoard` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getTotPosForDashBoard`(
in branchId varchar(45),
in storeTypeId varchar(45)
)
BEGIN
SELECT SUM(TOPEID.item_order_qty) as totOrderQty,TOPEID.item_name as itemName,TOPEID.item_size as size,
TOPEID.item_diapter as diapter,TOPEID.item_strength as strength,TOPEID.item_volume as volume,TOPEID.item_id_fk as itemId
FROM tbl_os_po_each_item_details TOPEID
join tbl_os_po_main_details TOPMD ON TOPMD.apoid=TOPEID.npoid_fk
where TOPMD.instore_fk=storeTypeId and TOPEID.indentToBranchId =branchId and TOPEID.poItemStatusId=2
group by item_id_fk ,if(storeTypeId=1,item_diapter,item_strength), if(storeTypeId=1,item_size,item_volume);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getTransferredDcItemsList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getTransferredDcItemsList`(
 branchIds int,
 requestType int
)
BEGIN
SELECT 
    aMainStckTrnfrId AS mainTransferId,
    dcNumber,
    DATE(dcDate) AS dcDate,
    indentNumber
FROM
    tbl_stock_transfer_maindetails 
WHERE
    transferredToBranchId = branchIds
        AND requestType
        AND stock_transfer_status = 0 and find_in_set(typeId,concat(requestType,',','5'));
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_GetUserBranchMapDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_GetUserBranchMapDetails`(
in userNames varchar(100)
)
BEGIN
SELECT THB.abranchId, THB.branchHospName FROM tbl_user_branches_map TUBM
	JOIN tblhospitalbranches THB ON THB.abranchId = TUBM.nBranchId
		 AND TUBM.isActive = 1
		 AND TUBM.isDeleted = 0
    JOIN tbl_user TU ON TU.userId = TUBM.nUserId
		AND TU.isActive = 1
		AND TU.isDeleted = 0
    WHERE TU.userName = userNames
    ORDER BY abranchId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getUserFeaturesPermissions` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getUserFeaturesPermissions`(
in userId int,
in branchid int
)
BEGIN
SELECT aMstModFeatureId, featureName, isEnabled FROM tbl_mst_module_features TMMF
JOIN tbl_user_module_features_map TUMFM ON TUMFM.nFeatureId = TMMF.aMstModFeatureId
WHERE TUMFM.toUserId = userId
AND nBranchId = branchid
AND TUMFM.isActive = 1
AND TUMFM.isDeleted = 0
ORDER BY aMstModFeatureId ASC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getUserList` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getUserList`(
in branchId int
)
BEGIN
 SELECT userId, userName, fullName, employeeId, mobileNumber, mobileNumber, emailId
	FROM tbl_user TU
 JOIN tbl_user_branches_map TUBM ON TUBM.nUserId = TU.userId
 WHERE TUBM.nBranchId = branchId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getuser_stores_MappedDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getuser_stores_MappedDetails`(
in userId int
)
BEGIN

SELECT 
    T1.*
FROM
    tbl_mst_storecreation T1
        JOIN
    tbl_user_stores_map T2 ON T1.isActive = 1
        AND T1.aStoreId_pk = T2.storeId
        AND T2.userId = userId
ORDER BY aStoreId_pk ASC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getVender_OutStandingDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getVender_OutStandingDetails`(
in in_branchId int,
in dateToFilter date
)
BEGIN

SELECT 
	T2.company_name as venderName,
    T2.credit_period as creditPeriod, 
    sum(if(datediff(dateToFilter,T1.invoiceDate)>T2.credit_period=true,ifnull(T1.invoiceAmount,0)+ifnull(T1.frightCharges,0),0)) as outCreditPerionAmt,
	sum(if(datediff(dateToFilter,T1.invoiceDate)<=T2.credit_period=true,ifnull(T1.invoiceAmount,0)+ifnull(T1.frightCharges,0),0)) as inCreditPerionAmt,
     sum(if(datediff(dateToFilter,T1.invoiceDate)>T2.credit_period=true,1,0)) as outCreditPerionCount,
	sum(if(datediff(dateToFilter,T1.invoiceDate)<=T2.credit_period=true,1,0)) as inCreditPerionCount,
    T3.totalCreditAmount, 
    T3.id as vendorCreditPriId, 
  concat('[',group_concat(concat('{',concat('"grnId"',':',T1.aGrnId_pk),',',concat('"invoiceDate"',':',concat('"',date(T1.invoiceDate),'"')),',',concat('"daysToPay"',':',if(T2.credit_period-datediff(dateToFilter,T1.invoiceDate) like ('%-%') ,-1,T2.credit_period-datediff(dateToFilter,T1.invoiceDate))),',',concat('"invoiceAmt"',':',ifnull(T1.invoiceAmount,0)+ifnull(T1.frightCharges,0)),'}')),']')   as paymentsGrns
FROM
    tbl_grn_for_po_orders T1 
        JOIN
    tbl_mst_supplier T2 ON T1.nSupplierId_fk = T2.aSupplierId_pk and T1.isPaid=0 and T1.nPoType_fk=1 
    LEFT JOIN
    tbl_vender_outstand_payment_main_transaction T3 ON T3.vendorId=T1.nSupplierId_fk and T3.branchId=T1.ngrnBranchId_fk
where T1.ngrnBranchId_fk=in_branchId
group by T1.nSupplierId_fk;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getVender_OutStandingPODetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getVender_OutStandingPODetails`(
in in_vendorId int,
in in_branchId int
)
BEGIN

SELECT 
	T2.company_name as venderName,
    T2.credit_period as creditPeriod,
         T1.invoiceNumber,
         date(T1.invoiceDate) as invoiceDate,
   ifnull(T1.invoiceAmount,0)+ifnull(T1.frightCharges,0) as invoiceAmount,
     datediff(now(),T1.invoiceDate) as creditPerionCount,
     T1.nSupplierId_fk as supplierId,
     T1.aGrnId_pk as grnId,
     T1.grnNumber,
     T1.ngrnBranchId_fk as branchId,
     T3.branchHospName
FROM
    tbl_grn_for_po_orders T1
        JOIN
    tbl_mst_supplier T2 ON T1.nSupplierId_fk = T2.aSupplierId_pk and T1.isPaid=0 and T1.nPoType_fk=1 and T1.nSupplierId_fk=in_vendorId   -- and if(in_vendorId='-1',true,T1.nSupplierId_fk=in_vendorId)
    JOIN 
    tblhospitalbranches T3 on T3.abranchId=T1.ngrnBranchId_fk
    and if(in_branchId='-1',true,T1.ngrnBranchId_fk=in_branchId)
    ;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getVender_PaymentDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getVender_PaymentDetails`(
in in_venderId int ,
in in_branchId int
)
BEGIN

SELECT 
   T1.id, T1.paidAmount, T1.paymentDate, T1.paymentMode, T1.remarks,T1.advanceAmtConsumed,
   T3.company_name AS venderName,
   group_concat(distinct(T5.branchHospName)) as branchHospName
FROM
    tbl_vendor_payment_main_details T1
    
     JOIN 
     tbl_mst_supplier T3 ON T3.aSupplierId_pk=T1.venderId
     JOIN
     tbl_vendor_payment_each_details T4 on T4.mainTransId=T1.id
		JOIN
     tblhospitalbranches T5 on T5.abranchId=T4.branchId
WHERE
    T1.venderId = in_venderId and if(in_branchId='-1',true,T1.branchId=in_branchId)
    group by T1.id
    order by date(T1.paymentDate) desc,T1.id desc
    ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getVender_PaymentEachDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getVender_PaymentEachDetails`(
in in_mainTransId int 
)
BEGIN
SELECT 
    T1.id, T1.invoiceNo, T1.invoiceAmount, T1.invoiceDate,T2.branchHospName
FROM
    tbl_vendor_payment_each_details T1
     JOIN
     tblhospitalbranches T2 on T2.abranchId=T1.branchId
WHERE
    T1.mainTransId = in_mainTransId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_getVendorPaymentReport` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getVendorPaymentReport`(
in fromdate date,
in todate date,
in branchIds varchar(250),
in supplierIds varchar(250)
)
BEGIN

SELECT 
    group_concat(concat(T2.invoiceNo,'(',T2.grnNumber,')')) as invoiceNo,
    T1.paymentDate ,
    T1.paidAmount,
    T1.paymentMode,
    T1.remarks,
    T3.fullName as userName,
       group_concat(distinct(T4.branchHospName)) as branchHospName,
    T1.advanceAmtConsumed,
    T5.company_name as supplierName
FROM
    tbl_vendor_payment_main_details T1
        JOIN
    tbl_vendor_payment_each_details T2 ON T1.id = T2.mainTransId
     JOIN
     tbl_user T3 ON T1.userId=T3.userId
      JOIN
     tblhospitalbranches T4 on T4.abranchId=T2.branchId
     JOIN 
     tbl_mst_supplier T5 ON T5.aSupplierId_pk=T1.venderId
    where if(branchIds='-1',true,find_in_set(T1.branchId,branchIds))   and if(supplierIds='-1',true,find_in_set(T1.venderId,supplierIds))
    and T1.paymentDate between fromdate and todate
     group by T1.id
     ;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_eachitem_po_for_central` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_eachitem_po_for_central`(
in apoeachids text
)
BEGIN
SELECT 
    IFNULL(apoeachid, 0) apoeachid,
    IFNULL(npoid_fk, '0') npoid_fk,
    IFNULL(item_id_fk, '0') item_id_fk,
    IFNULL(item_code, '0') item_code,
    IFNULL(item_name, '0') item_name,
    IFNULL(item_hsn_code, '0') item_hsn_code,
    IFNULL(item_diapter, '0') item_diapter,
    IFNULL(item_size, '0') item_size,
    IFNULL(item_strength, '0') item_strength,
    IFNULL(item_volume, '0') item_volume,
    IFNULL(item_free_qty, '0') item_free_qty,
    IFNULL(item_order_qty, '0') item_order_qty,
    IFNULL(item_unit_price, '0') item_unit_price,
    IFNULL(item_discount_percent, '0') item_discount_percent,
    IFNULL(item_discount_rs, '0') item_discount_rs,
    IFNULL(item_gst_percent, '0') item_gst_percent,
    IFNULL(item_gst_rs, '0') item_gst_rs,
    IFNULL(item_sgst_percent, '0') item_sgst_percent,
    IFNULL(item_sgst_rs, '0') item_sgst_rs,
    IFNULL(item_cgst_percent, '0') item_cgst_percent,
    IFNULL(item_cgst_rs, '0') item_cgst_rs,
    IFNULL(item_igst_percent, '0') item_igst_percent,
    IFNULL(item_igst_rs, '0') item_igst_rs,
    IFNULL(item_total_amt_without_gst, '0') item_total_amt_without_gst,
    IFNULL(item_total_amt_with_gst, '0') item_total_amt_with_gst,
    IFNULL(poItemStatusId, '0') poItemStatusId
FROM
    tbl_os_po_each_item_details
WHERE
    find_in_set(apoeachid ,apoeachids);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_generatepo_supplier_configdetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_generatepo_supplier_configdetails`(
in nStoreIds varchar(50)
)
BEGIN
SELECT 
    ifnull(t1.aSupplierId_pk, '') aSupplierId_pk,
    ifnull(t1.code, '') code,
    ifnull(t1.company_name, '') company_name,
    ifnull(t1.GSTIN, '') GSTIN,
    ifnull(t1.IGSTN, '') IGSTN,
    ifnull(t1.emailId, '') emailId,
    ifnull(t1.po_emailId, '') po_emailId,
    ifnull(t1.credit_period, '') credit_period,
    ifnull(t1.nLocationId_fk, '') nLocationId_fk,
    ifnull(t3.aLocationId_pk, '') aLocationId_pk,
    ifnull(t3.locnCode, '') locnCode,
    ifnull(t3.locnShortCode, '') locnShortCode,
    ifnull(t3.locnName, '') locnName,
    IFNULL(t1.TATinDays,0) AS turnAroundTimeInDays,
    IFNULL(ADDDATE(DATE(NOW()), IFNULL(t1.TATinDays,0)),'') AS expDeliveryDate
FROM
    tbl_mst_supplier t1
        JOIN
    tbl_supplier_store_mapping t2 ON  find_in_set(t2.nStoreId_fk,nStoreIds) AND t2.isActive = 1
        AND t2.nSupplierId_fk = t1.aSupplierId_pk
        JOIN
	tbl_mst_locations t3 on t3.aLocationId_pk = t1.nLocationId_fk
ORDER BY t1.nLocationId_fk ASC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_generatepo_supplier_configdetails_indent` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_generatepo_supplier_configdetails_indent`(
in nStoreIds int,
in hospetalFlag int
)
BEGIN
SELECT 
    ifnull(t1.aSupplierId_pk, '') aSupplierId_pk,
    ifnull(t1.code, '') code,
    ifnull(t1.company_name, '') company_name,
    ifnull(t1.GSTIN, '') GSTIN,
    ifnull(t1.IGSTN, '') IGSTN,
    ifnull(t1.emailId, '') emailId,
    ifnull(t1.po_emailId, '') po_emailId,
    ifnull(t1.credit_period, '') credit_period,
    ifnull(t1.nLocationId_fk, '') nLocationId_fk,
    ifnull(t3.aLocationId_pk, '') aLocationId_pk,
    ifnull(t3.locnCode, '') locnCode,
    ifnull(t3.locnShortCode, '') locnShortCode,
    ifnull(t3.locnName, '') locnName,
    IFNULL(t1.TATinDays,0) AS turnAroundTimeInDays,
    IFNULL(ADDDATE(DATE(NOW()), IFNULL(t1.TATinDays,0)),'') AS expDeliveryDate
FROM
    tbl_mst_supplier t1
        JOIN
    tbl_supplier_store_mapping t2 ON t2.nStoreId_fk = nStoreIds AND t2.isActive = 1
        AND t2.nSupplierId_fk = t1.aSupplierId_pk
        JOIN
	tbl_mst_locations t3 on t3.aLocationId_pk = t1.nLocationId_fk 
 where find_in_set(t1.supplierFlag,if(hospetalFlag=3,'0,1','0'))
ORDER BY t1.nLocationId_fk ASC;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_items` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_items`()
begin
    select
     im.aItemId_pk as itemId
    ,im.nStoreId_fk as storeId
    ,im.itemCode
    ,im.itemName 
    ,COALESCE(sm.nSupplierId_fk, 0) as supplierId
	from tbl_mst_items_details im
    left join tbl_supplier_item_mapping sm on sm.nItemId_fk=im.aItemId_pk
	where im.isActive=1 and im.isDeleted=0;
    end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_item_availqty_indent` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_item_availqty_indent`(
in itemIds int,
in indentToBranchIds int
)
BEGIN

SELECT 
    IFNULL(SUM(availQty), 0) AS availQty
FROM
    tbl_grn_availqty
WHERE
    nItemId = itemIds AND nBranchId = indentToBranchIds;
    
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_main_po_for_central` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_main_po_for_central`(
in poids text,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
SELECT 
    IFNULL(apoid, 0) apoid,
    IFNULL(po_number, '0') po_number,
    IFNULL(instore_fk, '0') instore_fk,
    IFNULL(po_type_fk, '0') po_type_fk,
    IFNULL(po_from_branch_fk, '0') po_from_branch_fk,
    IFNULL(po_date, '0') po_date,
    IFNULL(po_raisedby_fk, '0') po_raisedby_fk,
    IFNULL(po_billingaddress, '0') po_billingaddress,
    IFNULL(po_deliveryaddress, '0') po_deliveryaddress,
    IFNULL(po_to_supplier_id_fk, '0') po_to_supplier_id_fk,
    IFNULL(po_expexted_delivery_date, '0') po_expexted_delivery_date,
    IFNULL(po_total_items, '0') po_total_items,
    IFNULL(po_total_items_qty, '0') po_total_items_qty,
    IFNULL(po_total_amount, '0') po_total_amount,
    IFNULL(po_status, '0') po_status,
    IFNULL(po_status_text, '0') po_status_text,
    IFNULL(po_authorized_by_fk, '0') po_authorized_by_fk,
    IFNULL(po_authorized_date, '0') po_authorized_date
FROM
    tbl_os_po_main_details
WHERE
    apoid = poids
    LIMIT minlimitRow , maxlimitRow;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_pocreation_auto_item_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_pocreation_auto_item_details`(
in itemid int,
in supplierId int ,
in branchIds int
)
BEGIN
SELECT 
    IFNULL(t1.aItemId_pk, '') aItemId_pk,
    IFNULL(t1.itemCode, '') itemCode,
    IFNULL(t1.itemShortCode, '') itemShortCode,
    IFNULL(t1.itemName, '') itemName,
    IFNULL(t1.strength, '') strength,
    IFNULL(t1.volume, '') volume,
    IFNULL(t1.ROL, '0') ROL,
    IFNULL(t1.ROQ, '0') ROQ,
    IFNULL(t1.UNIT, '') UNIT, 
    IFNULL(t2.supplier_item_mappingId, '') supplier_item_mappingId,
    IFNULL(t2.nSupplierId_fk, '0') nSupplierId_fk,
    IFNULL(t2.purchasePrice, '0') purchasePrice,
    IFNULL(t2.sellingPrice, '0') sellingPrice,
    IFNULL(t2.MRP, '0') MRP,
    IFNULL(t2.freeQty, '0') freeQty,
    IFNULL(t3.hsnCode, '') hsnCode,
    IFNULL(t3.gstPercentage, '0') gstPercentage,
    IFNULL(t3.sgstPercentage, '0') sgstPercentage,
    IFNULL(t3.cgstPercentage, '0') cgstPercentage,
    IFNULL(t3.igstPercentage, '0') igstPercentage,
    IFNULL(t2.supplier_item_mappingId, '') supplier_item_mappingId
FROM
    (SELECT 
        *
    FROM
        tbl_mst_items_details
    WHERE
        aItemId_pk = itemid 
	) t1
        JOIN
    tbl_supplier_item_mapping t2 ON t2.nItemId_fk = t1.aItemId_pk and   t2.isActive = 1 AND t2.isDeleted = 0
		AND t2.nSupplierId_fk = supplierId  
        AND t2.nBranchId = branchIds
        JOIN
    tbl_mst_hsn_codes t3 ON t3.aHsnAutoId_pk = t2.nhsnId_fk;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_pocreation_item_autosuggest` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_pocreation_item_autosuggest`(
in itemnames varchar(100),
in supplierid int,
in branchIds int,
in storeId int
)
BEGIN
SELECT 
    IFNULL(t1.aItemId_pk, '') aItemId_pk,
    IFNULL(t1.itemCode, '') itemCode,
    IFNULL(t1.itemShortCode, '') itemShortCode,
    IFNULL(t1.itemName, '') itemName,
    IFNULL(t1.strength, '') strength,
    IFNULL(t1.volume, '') volume,
    IFNULL(t1.ROL, '0') ROL,
    IFNULL(t1.ROQ, '0') ROQ,
    IFNULL(t2.supplier_item_mappingId, '') supplier_item_mappingId,
    IFNULL(t2.nSupplierId_fk, '0') nSupplierId_fk,
    IFNULL(t2.purchasePrice, '0') purchasePrice,
    IFNULL(t2.sellingPrice, '0') sellingPrice,
    IFNULL(t2.MRP, '0') MRP,
    IFNULL(t2.freeQty, '0') freeQty,
    IFNULL(T3.hsnCode,'0') as hsnCode
FROM
   tbl_mst_items_details t1
        JOIN
    tbl_supplier_item_mapping t2 ON if(supplierid=-1,true,t2.nSupplierId_fk = supplierid)  AND t1.isActive = 1 and
         t2.nItemId_fk = t1.aItemId_pk 
         AND t2.nBranchId = branchIds and  t1.nStoreId_fk = storeId 
         JOIN
         tbl_mst_hsn_codes T3 on T3.aHsnAutoId_pk=t2.nhsnId_fk
       --   join 
    --    (SELECT nSupplierId_fk,group_concat(nStoreId_fk) as nStoreId_fk from   tbl_supplier_store_mapping) TBLSM ON if(supplierid=-1,true,t2.nSupplierId_fk = supplierid) and find_in_set(storeId,TBLSM.nStoreId_fk)
-- 	AND t1.nStoreId_fk = storeId
WHERE
     if(supplierid=-1,true,t2.nSupplierId_fk = supplierid)   AND t1.itemName LIKE CONCAT('%', itemnames, '%')
       AND t2.isActive = 1 AND t2.isDeleted = 0
LIMIT 20;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_poeachlistfrom_branch_topogen` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_poeachlistfrom_branch_topogen`(
in poids int
)
BEGIN
SELECT 
	IFNULL(t1.apoeachid, '0') apoeachid_branch,
     IFNULL(t1.npoid_fk, '0') npoid_fk_branch,
    IFNULL(t1.item_id_fk, '0') item_id_fk,
    IFNULL(t1.item_code, '0') item_code,
    IFNULL(t1.item_name, '0') item_name,
    IFNULL(t1.item_free_qty, '0') item_free_qty,
    -- IFNULL(t1.item_order_qty, '0') item_order_qty,
    UDF_getIssuedItemsBalanceQty(t1.apoeachid) AS item_order_qty,
    IFNULL(t1.item_diapter, '0') item_diapter,
    IFNULL(t1.item_size, '0') item_size,
    IFNULL(t1.item_strength, '0') item_strength,
    IFNULL(t1.item_volume, '0') item_volume,
    IFNULL(t2.ROL, '0') ROL,
    IFNULL(t2.ROQ, '0') ROQ,
    IFNULL(t2.UNIT, '') UNIT,
    IFNULL(t3.supplier_item_mappingId, '') supplier_item_mappingId,
    IFNULL(t3.nSupplierId_fk, '0') nSupplierId_fk,
    IFNULL(t3.purchasePrice, '0') purchasePrice,
    IFNULL(t3.sellingPrice, '0') sellingPrice,
    IFNULL(t3.MRP, '0') MRP,
    IFNULL(t3.freeQty, '0') freeQty,
    IFNULL(t4.hsnCode, '') hsnCode,
    IFNULL(t4.gstPercentage, '0') gstPercentage,
    IFNULL(t4.sgstPercentage, '0') sgstPercentage,
    IFNULL(t4.cgstPercentage, '0') cgstPercentage,
    IFNULL(t4.igstPercentage, '0') igstPercentage
FROM
    tbl_os_po_each_item_details_from_branch t1
        JOIN
    tbl_mst_items_details t2 ON t1.npoid_fk = poids
        AND t1.is_po_raised IS NULL
        AND t2.aItemId_pk = t1.item_id_fk 
        AND UDF_getIssuedItemsBalanceQty(t1.apoeachid) > 0
        JOIN
    tbl_supplier_item_mapping t3 ON t3.nItemId_fk = t2.aItemId_pk
        JOIN
    tbl_mst_hsn_codes t4 ON t4.aHsnAutoId_pk = t2.nHsnCodeId_fk
	-- LEFT JOIN
-- 	tbl_stocks_issued_to_emr TSITE ON TSITE.ngrnEachId_fk = t1.apoeachid
     group by t1.apoeachid;
  --   group by t1.npoid_fk, t1.item_diapter, t1.item_size, t1.item_strength, t1.item_volume;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_pomainfrom_branch_topogen` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_pomainfrom_branch_topogen`(
in ponum varchar(50)
)
BEGIN
SELECT 
    apoid,
    IFNULL(po_number, '') po_number,
    po_from_branch_fk,
    IFNULL(po_total_items, 0) po_total_items,
    IFNULL(po_total_items_qty, 0) po_total_items_qty,
    IFNULL(po_total_amount, 0) po_total_amount,
    IFNULL(DATE(po_date), '') po_date,
    IFNULL(t2.branchHospName, '') branchHospName,
    t1.instore_fk as storeId
FROM
    tbl_os_po_main_details_from_branch t1
    join
    tblhospitalbranches t2 on IF(ponum = '', '1=1', t1.po_number = ponum) and t2.abranchId = t1.po_from_branch_fk
ORDER BY apoid DESC
LIMIT 25;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_store` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_store`()
begin
    select
     st.aStoreId_pk as storeId
    ,storeShortCode
    ,storeName
	from tbl_mst_storecreation st
	where st.isActive=1 and st.isDeleted=0;
    end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_get_supplies` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_get_supplies`()
begin
    select
    s.aSupplierId_pk as supplierId
    ,ssm.nStoreId_fk as storeId
    ,s.code
    ,s.company_name as supplierName
    ,l.locnName as location
	from tbl_supplier_store_mapping ssm
	join tbl_mst_supplier s on s.aSupplierId_pk=ssm.nSupplierId_fk
    join tbl_mst_locations l on l.aLocationId_pk=s.nLocationId_fk;
    end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_insertInto_tbl_grn_for_po_each_item_orders` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_insertInto_tbl_grn_for_po_each_item_orders`(
nPoIdCentral_fks varchar(25),
nPoIdBranch_fks varchar(25),
nPoIdBranch_Central_fks varchar(25),
nGrnIdCentral_fks varchar(25),
nPoType_fks int,
nSupplierId_fks int,
nStoreId_fks int,
nGrnUserId_fks int,
nItemId_fks int,
receivedQtys float,
batchNos varchar(25),
expiryDates datetime,
serialNumbers varchar(100),
itemAmounts float,
grnBranchIds int,
nUserIds int,
in_mainGrnId int,
sizeOfItem varchar(25),
diopterOfItem varchar(25),
strengthOfItem varchar(25),
volOfItem varchar(25),
eachPoIds int,
supplierItemMapId int,
 in grnTypeId int,
 in eachTransferId int,
 in mainTransferId int,
 in nGrnAt datetime,
 in poMainOrderId int,
  in in_orderQty int,
   in in_freeQty int,
   in_unitPrice float,
   in_itemPriceBeforeDiscount float,
   in_discountPercentage float,
   in_disCountInRupees float,
   in_taxableAmount float,
   in_taxAmount float,
   in_totalAmount float
 -- ,out autoGenId int
)
BEGIN
DECLARE autoGenId INT;
DECLARE autoAvailId INT;
DECLARE declar_indentEachId int;
insert into tbl_grn_for_po_each_item_orders(
nPoIdCentral_fk,
nPoIdBranch_fk,
nPoIdBranch_Central_fk,
nGrnIdCentral_fk,
nPoType_fk,
nSupplierId_fk,
nStoreId_fk,
nGrnUserId_fk,
nItemId_fk,
receivedQty,
batchNo,
expiryDate,
serialNumber,
itemAmount,
ngrnBranchId_fk,
nUserId_fk,
nGrnId_fk,
size,
diapter,
strength,
volume,
nPoEachId,
nSupplierItemMapid,
nGrnTypeId,
nEachTransferId,
nMainTransferId,
orderQty,
freeQty,
unitPrice,
itemPriceBeforeDiscount,
discountPercentage,
disCountInRupees,
taxableAmount,
taxAmount,
totalAmount
) values(
nPoIdCentral_fks,
nPoIdBranch_fks,
null,
nGrnIdCentral_fks,
nPoType_fks,
nSupplierId_fks,
nStoreId_fks,
nGrnUserId_fks,
nItemId_fks,
receivedQtys,
batchNos,
expiryDates,
serialNumbers,
itemAmounts,
grnBranchIds,
nUserIds,
in_mainGrnId,
sizeOfItem,
diopterOfItem,
strengthOfItem,
volOfItem,
eachPoIds,
supplierItemMapId,
grnTypeId,
eachTransferId,
mainTransferId,
in_orderQty,
in_freeQty,
in_unitPrice,
in_itemPriceBeforeDiscount,
in_discountPercentage,
in_disCountInRupees,
in_taxableAmount,
in_taxAmount,
in_totalAmount
);
 SET autoGenId = last_insert_id();
 INSERT INTO `tbl_grn_availqty`
		(
			`nMainGrnId`,
			`nEachGrnId`,
			`grnQty`,
            `transferedQty`,
			`returnedQty`,
			`availQty`,
			`nUserId`,
            `nBranchId`,
            nStoreId,
            nItemId,
            nPotypeId,
            nSupplierId,
            nSupplierMapId
		)
		VALUES
		(
			in_mainGrnId,
            autoGenId,
            receivedQtys,
            0,
            0,
            receivedQtys,
            nUserIds,
            grnBranchIds,
            nStoreId_fks,
            nItemId_fks,
            nPoType_fks,
            nSupplierId_fks,
            supplierItemMapId
		);

set autoAvailId = last_insert_id();
INSERT INTO `tbl_grn_status_history_log`
(
nGrnForPoMainId,
nGrnForEachId,
GrnBy,
GrnAt,
statusId,
remarks
)
VALUES
(
in_mainGrnId,
autoGenId,
nUserIds,
nGrnAt,
statusId,
remarks);

if exists (select * from tbl_os_po_main_details where isReserved=1 and apoid=poMainOrderId) then
update tbl_grn_availqty
set 
 patientId = (select patientId from tbl_os_po_main_details where  apoid=poMainOrderId),
 counsilId = (select counsilId from tbl_os_po_main_details where  apoid=poMainOrderId),
 surgAppId = (select surgAppId from tbl_os_po_main_details where  apoid=poMainOrderId),
 patName = (select patName from tbl_os_po_main_details where  apoid=poMainOrderId),
 patientCode = (select patientCode from tbl_os_po_main_details where  apoid=poMainOrderId),  
 reservedById = (select reservedById from tbl_os_po_main_details where  apoid=poMainOrderId),
 reservedByName = (select reservedByName from tbl_os_po_main_details where  apoid=poMainOrderId),
 isReserved = 1
 where aGrnAvailQtyId=autoAvailId;
END if;



set declar_indentEachId = (select IFNULL(indentEachid,'0') as indentEachid from tbl_grn_for_po_each_item_orders ITGPEID
join tbl_os_po_each_item_details ITOPEID ON ITGPEID.nPoEachId=ITOPEID.apoeachid where ITOPEID.apoeachid=eachPoIds limit 1);

if(declar_indentEachId<>'0')
then
update tbl_os_po_each_item_details set indentPoraiseStatus=2 where apoeachid=declar_indentEachId;
end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_insertInto_tbl_grn_for_po_orders` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_insertInto_tbl_grn_for_po_orders`(
grnNumbers varchar(25),
 nPoIdCentral_fks int,
 nPoIdBranch_fks int, 
 nPoIdBranch_Central_fks text,  
 nGrnIdCentral_fks int, 
 nPoType_fks int,
 nSupplierId_fks int, 
 nStoreId_fks int, 
 nGrnUserId_fks int, 
 grnDates datetime,
 dcNumbers varchar(25), 
 dcDates datetime, 
 invoiceNumbers varchar(25), 
 invoiceAmounts float, 
 frightChargess float, 
 invoicePhotoUploadNames varchar(25),
 grnRemarkss text,
 grnTotalItemss varchar(25),
 grnTotalItemsQtys varchar(25),
 nUserId_fks int,
 grnBranchIds int,
 invoiceTaxAmts float,
 sendToBranchs int,
 invoiceDates datetime,
 in_centralGRNNum text,
  in_saveFrom int,
 in_dcId int,
 out autoGenGrnid int,
 out grnNumber varchar(50)
)
BEGIN

DECLARE grn_bracnh_format varchar(50);
DECLARE grn_num varchar(50);

insert into tbl_grn_for_po_orders(
grnNumber,
 nPoIdCentral_fk,
 nPoIdBranch_fk, 
 nPoIdBranch_Central_fk, 
 nGrnIdCentral_fk, 
 nPoType_fk,
 nSupplierId_fk, 
 nStoreId_fk, 
 nGrnUserId_fk, 
 grnDate,
 dcNumber, 
 dcDate, 
 invoiceNumber, 
 invoiceAmount, 
 frightCharges, 
 invoicePhotoUploadName,
 grnRemarks,
 grnTotalItems,
 grnTotalItemsQty,
 nUserId_fk,
 ngrnBranchId_fk,
 invoiceTaxAmt,
 sendToBranch,
 invoiceDate,
 centralGRNNum
) values (
 grnNumbers,
 nPoIdCentral_fks,
 nPoIdBranch_fks, 
 null, 
 nGrnIdCentral_fks, 
 nPoType_fks,
 nSupplierId_fks, 
 nStoreId_fks, 
 nGrnUserId_fks, 
 grnDates,
 dcNumbers, 
 dcDates, 
 invoiceNumbers, 
 invoiceAmounts, 
 frightChargess, 
 invoicePhotoUploadNames,
 grnRemarkss,
 grnTotalItemss,
 grnTotalItemsQtys,
 nUserId_fks,
 grnBranchIds,
 invoiceTaxAmts,
 sendToBranchs,
 invoiceDates,
 in_centralGRNNum
);
set autoGenGrnid = last_insert_id();

set grn_bracnh_format = (select ifnull(grnShortCode, 'EQ') from tblhospitalbranches where abranchId = grnBranchIds);

if (nPoType_fks = 1) then
set grn_num = concat(ifnull(grn_bracnh_format, 'EQ'), '/GRN/', DATE_FORMAT(now(), '%d%m%Y'), '/', autoGenGrnid);
else if (nPoType_fks = 4) then
set grn_num = concat(ifnull(grn_bracnh_format, 'EQ'), '/IGRN/', DATE_FORMAT(now(), '%d%m%Y'), '/', autoGenGrnid);
else 
set grn_num = concat(ifnull(grn_bracnh_format, 'EQ'), '/CGRN/', DATE_FORMAT(now(), '%d%m%Y'), '/', autoGenGrnid);
end if;
end if;
update tbl_grn_for_po_orders
set grnNumber = grn_num
where aGrnId_pk = autoGenGrnid;

if(in_saveFrom = 2) then
UPDATE tbl_stock_transfer_maindetails
SET stock_transfer_status = 1
where aMainStckTrnfrId = in_dcId;
END IF;

 set grnNumber = grn_num;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_insertInto_tbl_ot_stock_consumption_each_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_insertInto_tbl_ot_stock_consumption_each_details`(
in_nMainConsumptionId int,
in_patientId int,
in_apptId int,
in_surgicalAppointmentId int,
in_nGrnAvailId int,
in_usedQty int,
in_nBranchId int,
in_nUserId int,
in_serialNo varchar(100),
in_batchNo  varchar(100),
in_diapter  varchar(100),
in_size  varchar(100),
in_strength  varchar(100),
in_volume varchar(100),
in_storeType int,
in_nItemId int,
in_itemName varchar(250),
in_itemShortCode varchar(250),
in_expiryDate varchar(50)
)
BEGIN

INSERT INTO `tbl_ot_stock_consumption_each_details`
(
`nMainConsumptionId`,
`patientId`,
`apptId`,
`surgicalAppointmentId`,
`nGrnAvailId`,
`usedQty`,
`nBranchId`,
`nUserId`,
`serialNo`, 
`batchNo`,
`diapter`,
`size`,
`strength`,
`volume`,
`storeType`,
`nItemId`,
`itemName`,
`itemShortCode`,
`expiryDate`
)
VALUES
(
in_nMainConsumptionId,
in_patientId,
in_apptId,
in_surgicalAppointmentId,
in_nGrnAvailId,
in_usedQty,
in_nBranchId,
in_nUserId,
in_serialNo,
in_batchNo,
in_diapter,
in_size,
in_strength,
in_volume,
in_storeType,
in_nItemId,
in_itemName,
in_itemShortCode,
in_expiryDate
);

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_Insert_Into_tbl_grn_return_each_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_Insert_Into_tbl_grn_return_each_details`(
in returnMainId int,
in mainGrnId int,
in eachGrnId int,
in returnQtys float,
in returnReasons text
)
BEGIN
INSERT INTO tbl_grn_return_each_details
    (nReturnMainId, nMainGrnId, nEachGrnId, returnQty, returnReason)
    VALUES
    (returnMainId, mainGrnId, eachGrnId, returnQtys, returnReasons);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_Insert_into_tbl_grn_return_main_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_Insert_into_tbl_grn_return_main_details`(
mainGrnId int,
reason text,
returnedDate datetime,
returnedUser int,
userId int,
branchIds int
)
BEGIN
INSERT INTO tbl_grn_return_main_details
    (nMainGrnId, returnReason, nReturnedUserId, nReturnedDate, nUserId_fk, nBranchId)
    VALUES
    (mainGrnId, reason, returnedUser, returnedDate, userId, branchIds);
	
    SELECT  last_insert_id() AS mainReturnId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_Insert_into_tbl_ot_stock_consumption_main_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_Insert_into_tbl_ot_stock_consumption_main_details`(
in_patId int,
in_surgApptId int,
in_inpatId int,
in_branchId int,
in_surgeryDate varchar(50),
in_Patcode varchar(250),
in_PatName varchar(250),
in_SurName varchar(250),
in_procName text

)
BEGIN
INSERT INTO TBL_OT_STOCK_CONSUMPTION_MAIN_DETAILS(patientId,surgicalAppointmentId,inPatientId,nBranchId,surgeryDate
,patCode,patName,surgeryName,procedureName
)
VALUES(in_patId,in_surgApptId,in_inpatId,in_branchId,in_surgeryDate,in_Patcode,in_PatName,in_SurName,in_procName);

select  last_insert_id() AS OtStockConsumptionMainId; 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_insert_into_tbl_stock_Transfer_mainDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_insert_into_tbl_stock_Transfer_mainDetails`(
-- 	dcNumbers varchar(50)
--     , dcDates datetime
     indentNumbers varchar(50)
    , branchIds int
    , transferredToBranchIds int
    , storeIds int
    , typeIds int
)
BEGIN
DECLARE stockTrnferID INT;
insert into tbl_stock_transfer_maindetails
 (
-- dcNumber
   dcDate
 , indentNumber
 , branchId
 , transferredToBranchId
 , storeId
 , typeId
 ) 
values (
--    dcNumbers
     NOW()
    , indentNumbers
    , branchIds
    , transferredToBranchIds
    , storeIds
    , typeIds
 );
SET stockTrnferID = LAST_INSERT_ID();

UPDATE tbl_stock_transfer_maindetails
SET dcNumber = CONCAT('DC',stockTrnferID)
WHERE aMainStckTrnfrId = stockTrnferID;

SELECT stockTrnferID; 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_Insert_StockIssued_ToEmr` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_Insert_StockIssued_ToEmr`(
in in_ngrnIdfk int,
in in_ngrnEachIdfk int ,
in in_nItemIdfk int,
in in_itemName text,
in in_availQty int,
in in_receivQty int,
in in_batchNo varchar(45),
in in_expiryDate datetime,
in in_serialNo varchar(45),
in in_nstockidfk int,
in in_nstockTypefk tinyint,
in in_issuedToUserId int,
in in_issuedUserName varchar(45),
in in_inbranchidfk int,
in in_nUserIdfk int,

in in_issueFromBranchId int,
in in_issueToBranchId int,
in in_centralGrnId int,
in in_centralGrnEachId int,
in in_centralGrnNumber text

)
BEGIN

insert into tbl_stocks_issued_to_emr(
`ngrnId_fk`,
`ngrnEachId_fk`,
`nItemId_fk`,
`itemName`,
`availQty`,
`issuedQty`,
`batchNo`,
`expiryDate`,
`serialNo`,
`nStoreId_fk`,
`nstockType_fk`,
`issuedToUserId`,
`issuedUserName`,
`nbranchid_fk`,
`insertedDate`,
`lastModifiedDate`,
`isActive`,
`isDeleted`,
`nUserId_fk`,
 nIssuedFromBranchId_fk,
 nIssuedToBranchId_fk,
 nCentralGrnId_fk,
nCentralGrnEachId_fk,
centralGrnNumber
) values(
in_ngrnIdfk,
in_ngrnEachIdfk,
in_nItemIdfk,
in_itemName,
in_availQty,
in_receivQty,
in_batchNo,
date(in_expiryDate),
in_serialNo,
in_nstockidfk,
in_nstockTypefk,
in_issuedToUserId,
in_issuedUserName,
in_inbranchidfk,
now(),
now(),
1,
0,
in_nUserIdfk,
in_issueFromBranchId, 
in_issueToBranchId,
in_centralGrnId,
in_centralGrnEachId,
in_centralGrnNumber

);

UPDATE tbl_grn_for_po_orders
SET issuedToBranch = in_issueToBranchId
WHERE aGrnId_pk = in_ngrnIdfk;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_Insert_StockTransfer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_Insert_StockTransfer`(
in in_ngrnIdfk int,
in in_ngrnEachIdfk int ,
in in_nItemIdfk int,
in in_receivQty int,
in in_batchNo varchar(45),
in in_expiryDate datetime,
in in_serialNo varchar(45),
in in_nstockidfk int,
in in_nstockTypefk tinyint,
in in_issuedToUserId int,
in in_issuedUserName varchar(45),
in in_inbranchidfk int,
in in_nUserIdfk int,
in in_issueFromBranchId int,
in in_issueToBranchId int,
in in_centralGrnNumber text,
in in_issuedToBranchPoId int,
in in_issuedToBrancheACHPoId int,
 in nMainStockTrnsfrIds int,
 in stockTransferTypeId int
)
BEGIN
IF(select UDF_getAvailableQtyInGrn(in_ngrnEachIdfk) > 0) THEN
insert into tbl_stocks_issued_to_emr(
`ngrnId_fk`,
`ngrnEachId_fk`,
`nItemId_fk`,

`issuedQty`,
`batchNo`,
`expiryDate`,
`serialNo`,
`nStoreId_fk`,
`nstockType_fk`,
`issuedToUserId`,
`issuedUserName`,
`nbranchid_fk`,
`insertedDate`,
`lastModifiedDate`,
`isActive`,
`isDeleted`,
`nUserId_fk`,
 nIssuedFromBranchId_fk,
 nIssuedToBranchId_fk,


centralGrnNumber,
issuedToBranchPoId,
issuedToBrancheACHPoId,
nMainStockTrnsfrId,
tranferType
) values(
in_ngrnIdfk,
in_ngrnEachIdfk,
in_nItemIdfk,

in_receivQty,
in_batchNo,
date(in_expiryDate),
in_serialNo,
in_nstockidfk,
in_nstockTypefk,
in_issuedToUserId,
in_issuedUserName,
in_inbranchidfk,
now(),
now(),
1,
0,
in_nUserIdfk,
in_issueFromBranchId, 
in_issueToBranchId,

in_centralGrnNumber,
in_issuedToBranchPoId,
in_issuedToBrancheACHPoId,
nMainStockTrnsfrIds,
stockTransferTypeId
);

UPDATE tbl_grn_for_po_orders
SET issuedToBranch = in_issueToBranchId
WHERE aGrnId_pk = in_ngrnIdfk;

END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_insert_supplierpayment_Each_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_insert_supplierpayment_Each_details`(
in in_mainTransId int,
in in_grnId int ,
in in_invoiceNo varchar(250),
in in_invoiceDate date,
in in_invoiceAmount float,
in in_venderId int,
in in_BranchId int,
in in_UserId int,
in in_grnNumber varchar(250)
)
BEGIN

INSERT INTO `tbl_vendor_payment_each_details`
(
`mainTransId`,
`grnId`,
`invoiceNo`,
`invoiceDate`,
`invoiceAmount`,
`venderId`,
`branchId`,
`userId`,
`grnNumber`)
VALUES
(in_mainTransId,in_grnId,in_invoiceNo,in_invoiceDate,in_invoiceAmount,in_venderId,in_BranchId,in_UserId,in_grnNumber);

update tbl_grn_for_po_orders set isPaid=1 where aGrnId_pk=in_grnId;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_insert_supplierpayment_main_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_insert_supplierpayment_main_details`(
in in_venderId int,
in in_totalInvoiceCount int,
in in_crediPeriodInvCount int,
in in_totalInvAmt float,
in in_crediPeriodInvAmt float,
in in_paidAmount float,
in in_paymentMode varchar(250),
in in_creditAmount float,
in in_paymentDate date,
in in_BranchId int,
in in_UserId int,
in in_remarks varchar(250),
in in_PayModeId int,
in in_advanceAmtCosumed float,
out autoPriid int
)
BEGIN


INSERT INTO `tbl_vendor_payment_main_details`
(
`venderId`,
`totalInvoiceCount`,
`crediPeriodInvCount`,
`totalInvAmt`,
`crediPeriodInvAmt`,
`paidAmount`,
`creditAmount`,
`paymentMode`,
`paymentDate`,
`branchId`,
`userId`,
`remarks`,
`payModeId`,
`advanceAmtConsumed`)
VALUES
(in_venderId,
in_totalInvoiceCount,
in_crediPeriodInvCount,
in_totalInvAmt,
in_crediPeriodInvAmt, 
in_paidAmount
,in_creditAmount,
in_paymentMode,
in_paymentDate,
in_BranchId,
in_UserId,
in_remarks,
in_PayModeId,
in_advanceAmtCosumed);

set autoPriid = last_insert_id();

if(in_advanceAmtCosumed<>'0')
then
call sproc_saveSupplierOutstandPayment(in_venderId,in_BranchId,in_totalInvAmt,in_advanceAmtCosumed ,in_PayModeId,in_remarks,in_UserId,in_paymentDate,2,autoPriid);
end if;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_ItemAvailabilityByItemId` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_ItemAvailabilityByItemId`(
in itemId int,
in storeId int,
in diopterVal varchar(10),
in sizeVal varchar(10),
in strengthVal varchar(10),
in volumeVal varchar(10),
in branchId int, 
in minlimitRow int,
in maxlimitRow int
)
BEGIN
SELECT 
    TMP.*
FROM
    (SELECT 
        IFNULL(TGFPEIO.receivedQty, 0) AS grnQty,
            0 AS issuedQtyToAllBranches,
            0 AS issuedQtyToRequestBranch,
            SUM(IFNULL(TGA.availQty, 0)) AS availableQty,
            TGFPO.grnNumber,
            TGFPEIO.*,
            TGA.isReserved
    FROM
        tbl_grn_for_po_orders TGFPO
     JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPEIO.nGrnId_fk = TGFPO.aGrnId_pk
     JOIN tbl_grn_availqty TGA ON TGA.nEachGrnId = TGFPEIO.aGrnId_each_pk
    LEFT JOIN tbl_stocks_issued_to_emr TSITE ON TSITE.ngrnEachId_fk = TGFPEIO.aGrnId_each_pk
    WHERE
        TGFPEIO.nItemId_fk = itemId
            AND IF(storeId = 1, (TGFPEIO.diapter = diopterVal
            AND TGFPEIO.size = sizeVal), TGFPEIO.strength = strengthVal
            AND TGFPEIO.volume = volumeVal)
            AND TGFPEIO.ngrnBranchId_fk = branchId
    GROUP BY TGFPEIO.nItemId_fk) AS TMP
ORDER BY expiryDate
LIMIT MINLIMITROW , MAXLIMITROW; 
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_ItemListForStockTransfer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_ItemListForStockTransfer`(
in in_branchId varchar(45),
in in_storeId int,
in in_poNumber text,
in requestType int,
in indentToBranchIds int,
in searchByDate varchar(45),
in minlimitRow int, 
in maxlimitRow int
)
BEGIN
SELECT 

	TOPMD.apoid,
    TOPMD.po_number,
    TOPMD.po_type_fk,
	TMP.poTypeNAme,
    DATE(TOPMD.po_date) AS po_date,
    TOPMD.po_total_items,
    TOPMD.po_total_items_qty,
    TOPMD.po_status,
    TOPEIDFB.apoeachid as eachPoIds,
    TOPEIDFB.item_id_fk	 as itemIds,
    TBLI.itemName AS itemName,
    TBLI.itemShortCode AS itemShortCode,
    
    /*TBLSUP.company_name,
    TOPMD.po_to_supplier_id_fk,*/
    
     (
    SELECT TMS.company_name FROM 
		tbl_supplier_item_mapping TSIM 
	JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk
    WHERE TSIM.nBranchId = indentToBranchIds AND TSIM.nItemId_fk = TOPEIDFB.item_id_fk AND TSIM.isAtPriority = 1
    ) AS company_name,
    
    (
    SELECT TMS.aSupplierId_pk FROM 
		tbl_supplier_item_mapping TSIM 
	JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk
    WHERE TSIM.nBranchId = indentToBranchIds AND TSIM.nItemId_fk = TOPEIDFB.item_id_fk AND TSIM.isAtPriority = 1
    ) AS po_to_supplier_id_fk,
    
  sum(TOPEIDFB.item_order_qty) AS item_order_qty,
    tbl_brch.nbranchcode as nbranchcode_req,
    TOPEIDFB.item_diapter,
    TOPEIDFB.item_size,
    TOPEIDFB.item_strength,
    TOPEIDFB.item_volume,
    TBLSUP.nLocationId_fk,
     (SELECT TSIM.supplier_item_mappingId FROM  
		tbl_supplier_item_mapping TSIM 
	JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk
    WHERE TSIM.nBranchId = indentToBranchIds AND TSIM.nItemId_fk = TOPEIDFB.item_id_fk AND TSIM.isAtPriority = 1
    )  AS supplierMapId
    /*,
    (SELECT TSIM.ReqQty FROM  
		tbl_supplier_item_mapping TSIM 
	JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk
    WHERE TSIM.nBranchId = indentToBranchIds AND TSIM.nItemId_fk = TOPEIDFB.item_id_fk AND TSIM.isAtPriority = 1
    )  AS item_order_qty*/
FROM
    
    tbl_os_po_main_details TOPMD
    JOIN tbl_os_po_each_item_details TOPEIDFB ON TOPEIDFB.npoid_fk = TOPMD.apoid
    JOIN tbl_mst_potypes TMP ON TMP.aPoTypeId = TOPMD.po_type_fk
    JOIN tbl_mst_supplier TBLSUP ON TBLSUP.aSupplierId_pk = TOPMD.po_to_supplier_id_fk
    JOIN tbl_mst_items_details TBLI ON TBLI.aItemId_pk = TOPEIDFB.item_id_fk
    JOIN tblhospitalbranches tbl_brch on tbl_brch.abranchId = TOPMD.po_from_branch_fk
WHERE
if(in_branchId != '',TOPMD.po_from_branch_fk = in_branchId,true) AND
 if(searchByDate != '',TOPMD.po_date = searchByDate,true) 
        AND TOPMD.instore_fk = in_storeId
		-- AND TOPMD.typeOfRequest = 2 -- NEWLY ADDED
        AND if(in_poNumber != '',TOPMD.po_number = in_poNumber,true)
        AND TOPMD.typeOfRequest = requestType
        AND TOPMD.indentToBranchId = indentToBranchIds
    GROUP BY TOPEIDFB.item_id_fk,TOPMD.po_to_supplier_id_fk , IF(in_storeId = 1,
    TOPEIDFB.item_diapter,
    TOPEIDFB.item_strength) , IF(in_storeId = 1,
    TOPEIDFB.item_size,
    TOPEIDFB.item_volume)
    ORDER BY TBLI.itemName asc
	LIMIT minlimitRow , maxlimitRow ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_ItemRegistration` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_ItemRegistration`(
in_nStoreId_fk int,
in_itemName varchar(250),
in_nBrandTypeId_fk int,
in_nItemManufacturerId_fk int,
in_itemDescription text,
in_genericName text,
in_nHsnCodeId_fk varchar(100),
in_UNIT varchar(45)
)
BEGIN
declare itemIdGen int;
declare validate int;

set validate = (
SELECT 
    COUNT(*)
FROM
    tbl_mst_items_details
WHERE
    itemName = in_itemName
        AND nHsnCodeId_fk = in_nHsnCodeId_fk
);

IF(validate = 0) Then
INSERT INTO `tbl_mst_items_details`
(
`nStoreId_fk`,
`itemName`,
`nBrandTypeId_fk`,
`itemDescription`,
`nItemManufacturerId_fk`,
`genericName`,
`nHsnCodeId_fk`,
`UNIT`
)
VALUES
(
in_nStoreId_fk,
in_itemName ,
in_nBrandTypeId_fk,
in_itemDescription,
in_nItemManufacturerId_fk,
in_genericName,
in_nHsnCodeId_fk,
in_UNIT
);

set itemIdGen = LAST_INSERT_ID();

UPDATE tbl_mst_items_details 
SET 
    itemShortCode = CONCAT('ITEM', itemIdGen)
WHERE
    aItemId_pk = itemIdGen;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_item_autosuggest` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_item_autosuggest`(
in itemnames varchar(100),
in in_storeId int

)
BEGIN
SELECT 
    IFNULL(t1.aItemId_pk, '') aItemId_pk,
    IFNULL(t1.itemCode, '') itemCode,
    IFNULL(t1.itemShortCode, '') itemShortCode,
    IFNULL(t1.itemName, '') itemName,
    IFNULL(t1.strength, '') strength,
    IFNULL(t1.volume, '') volume,
    IFNULL(t1.ROL, '0') ROL,
    IFNULL(t1.ROQ, '0') ROQ
 
FROM
    tbl_mst_items_details t1
    WHERE
       if(in_storeId <> '', nStoreId_fk = in_storeId ,"1=1") AND itemName LIKE CONCAT('%', itemnames, '%')
    LIMIT 20;
     
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_loaddepoHubBranchesByIds` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_loaddepoHubBranchesByIds`(
in inoperationTypeId int,
in loggedBranchId int,
in loggedHospFlag int
)
BEGIN


SELECT 
    T1.abranchId AS branchId,
    T1.branchHospName AS branchName,
    T1.nbranchcode AS branchCode
FROM
    tblhospitalbranches T1
WHERE
    T1.hospetalFlag = inoperationTypeId AND T1.branchActive = 1;


 


/*
if(inoperationTypeId='2')
THEN

SELECT DISTINCT
    T1.abranchId AS branchId,
    T1.branchHospName AS branchName,
    T1.nbranchcode AS branchCode
FROM
    tbl_depo_hub_mapping_details T2
        JOIN
    tblhospitalbranches T1 ON T1.abranchId = T2.branchId
WHERE
    T2.operationTypeId = loggedHospFlag
        AND T2.depoOrHubId = loggedBranchId;
 
 ELSE IF (inoperationTypeId='3')
 THEN
 SELECT DISTINCT
    T1.abranchId AS branchId,
    T1.branchHospName AS branchName,
    T1.nbranchcode AS branchCode
FROM
    tbl_depo_hub_mapping_details T2
        JOIN
    tbl_depo_hub_mapping_details T1 ON T1.abranchId = T2.depoOrHubId
WHERE
    T2.operationTypeId = loggedHospFlag
        AND T2.depoOrHubId = loggedBranchId;
 END IF;

END IF;
*/
 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_loadTaxPercentages` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_loadTaxPercentages`(
in hsnCode_id int,
in flag int
)
BEGIN
SELECT 
    gstPercentage,
    sgstPercentage,
    cgstPercentage,
    igstPercentage,
    hsnCode,
    aHsnAutoId_pk
FROM
    tbl_mst_hsn_codes 
WHERE
   if(flag='1',hsnCode like concat('%',hsnCode_id,'%') ,gstPercentage = hsnCode_id) AND isActive = 1
        AND isDeleted = 0;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_push_po_central_frombranch_eachitem_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_push_po_central_frombranch_eachitem_details`(
in npoid_fks int,
in item_id_fks int,
in item_codes varchar(50),
in item_names tinytext,
in item_hsn_codes varchar(50),
in item_order_qtys int,
in item_unit_prices float,
in item_discount_percents float,
in item_discount_rss float,
in item_gst_percents float,
in item_gst_rss float,
in item_sgst_percents float,
in item_sgst_rss float,
in item_cgst_percents float,
in item_cgst_rss float,
in item_igst_percents float, 
in item_igst_rss float,
in item_total_amt_without_gsts float,
in item_total_amt_with_gsts float,
in item_diapters varchar(25),
in item_strengths varchar(25),
in item_volumes varchar(25),
in po_statuss int,
in item_free_qtys int,
in item_sizes varchar(25),
in neachpoids_fk int,
in branchId int
)
BEGIN
	INSERT INTO tbl_os_po_each_item_details_from_branch 
	(
	npoid_fk,
	item_id_fk,
	item_code,
	item_name,
	item_hsn_code,
	item_order_qty,
	item_unit_price,
	item_discount_percent,
	item_discount_rs,
	item_gst_percent,
	item_gst_rs,
	item_sgst_percent,
	item_sgst_rs,
	item_cgst_percent,
	item_cgst_rs,
	item_igst_percent,
	item_igst_rs,
	item_total_amt_without_gst,
	item_total_amt_with_gst,
	item_diapter,
    item_strength,
    item_volume,
    poItemStatusId,
    item_free_qty,
    item_size,
    n_po_raised_barcnh_po_eachpkid_fk,
    n_po_raised_branchId_fk
    )
	VALUES
	(
	npoid_fks,
	item_id_fks,
	item_codes,
	item_names,
	item_hsn_codes,
	item_order_qtys,
	item_unit_prices,
	item_discount_percents,
	item_discount_rss,
	item_gst_percents,
	item_gst_rss,
	item_sgst_percents,
	item_sgst_rss,
	item_cgst_percents,
	item_cgst_rss,
	item_igst_percents,
	item_igst_rss,
	item_total_amt_without_gsts,
	item_total_amt_with_gsts,
	item_diapters,
    item_strengths,
    item_volumes,
    po_statuss,
    item_free_qtys,
    item_sizes,
    neachpoids_fk,
    branchId
    );
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_push_po_central_frombranch_main_details` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_push_po_central_frombranch_main_details`(
in po_numbers varchar(30),
in instore_fks int,
in po_type_fks int,
in po_from_branch_fks int,
in po_dates varchar(30),
in po_raisedby_fks int,
in po_billingaddresss text,
in po_deliveryaddresss text,
in po_to_supplier_id_fks int,
in po_expexted_delivery_dates  varchar(30),
in po_total_itemss int,
in po_total_items_qtys int,
in po_statuss int,
in po_status_texts varchar(30),
in po_authorized_by_fks int,
in po_authorized_dates varchar(30),
in po_total_amounts float,
in apoids int,
out autoPOid int,
in branchId int
)
BEGIN
INSERT INTO tbl_os_po_main_details_from_branch
		(
		po_number,
		instore_fk,
		po_type_fk,
		po_from_branch_fk,
		po_date,
		po_raisedby_fk,
		po_billingaddress,
		po_deliveryaddress,
		po_to_supplier_id_fk,
		po_expexted_delivery_date,
		po_total_items,
		po_total_items_qty,
        po_total_amount,
		po_status,
		po_status_text,
		po_authorized_by_fk,
		po_authorized_date,
        n_po_raised_barcnh_po_autopkid_fk,
        n_po_raised_branchId_fk
		)
		VALUES
		(
		po_numbers,
		instore_fks,
		po_type_fks,
		po_from_branch_fks,
		po_dates,
		po_raisedby_fks,
		po_billingaddresss,
		po_deliveryaddresss,
		po_to_supplier_id_fks,
		po_expexted_delivery_dates,
		po_total_itemss,
		po_total_items_qtys,
        po_total_amounts,
		po_statuss,
		po_status_texts,
		po_authorized_by_fks,
		po_authorized_dates,
        apoids,
        branchId
		);

set autoPOid =last_insert_id();
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_saveBranchRegistration` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_saveBranchRegistration`(
in branchId varchar(250),
in branchName varchar(250),
in branchCode varchar(250),
in branchLocationId varchar(250),
in poCode varchar(250),
in grnCode varchar(250),
in contactNo varchar(250),
in emergencyNo varchar(250),
in deliveryAddr varchar(250),
in billingAddr varchar(250),
in branchAddr varchar(250),
in branchPinCode varchar(250),
in gstinNo varchar(250),
in faxNo varchar(250),
in operationType varchar(250),
out genId int
)
BEGIN

INSERT INTO `tblhospitalbranches`
(
`branchHospName`,
`bhospitalAddress`,
`bPincode`,
`bhospitalPhNo`,
`bhospitalEmergencyNo`,
`nbranchcode`,
`bGSTINno`,
`hospBranch_DeliveryAddress`,
`hospBranch_BillingAddress`,
`nHospBranch_LocationId`,
`grnShortCode`,
`po_raise_format`,
`fax`,
`hospetalFlag`)
VALUES
(branchName,branchAddr,branchPinCode,contactNo,emergencyNo,branchCode,gstinNo,deliveryAddr,billingAddr,branchLocationId,grnCode,poCode,faxNo,operationType);

set genId = LAST_INSERT_ID();
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_saveDepoHubMappingDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_saveDepoHubMappingDetails`(
in in_operationTypeId int,
in in_hubOrDepoId int,
in in_BranchId int,
in in_userId int
)
BEGIN

INSERT INTO `tbl_depo_hub_mapping_details`
(
`operationTypeId`,
`depoOrHubId`,
`branchId`,
`userId`)
VALUES
(
in_operationTypeId,
in_hubOrDepoId,
in_BranchId,
in_userId
);


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_saveHSNCodeForMasters` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_saveHSNCodeForMasters`(
in in_hsnCode varchar(250),
in in_gstPerc double,
in in_sgstPerc double ,
in in_cgstPerc double ,
in in_igstPerc double 
)
BEGIN


INSERT INTO `tbl_mst_hsn_codes`
(
`hsnCode`,
`gstPercentage`,
`sgstPercentage`,
`cgstPercentage`,
`igstPercentage`)
VALUES
(
in_hsnCode,
in_gstPerc,
in_sgstPerc,
in_cgstPerc,
in_igstPerc

);



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_saveSupplierOutstandPayment` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_saveSupplierOutstandPayment`(
in in_venderId int,
in in_BranchId int,
in in_totalInvAmt float,
in in_paidAmount float,
in in_PayModeId int,
in in_remarks varchar(250),
in in_UserId int,
in in_paymentDate date,
in in_isCreditOrDebit int,
in in_debitTransactionMainTableId int
)
BEGIN

declare in_mainTransId int;

set in_mainTransId=(select ifnull(id,0) as id from tbl_vender_outstand_payment_main_transaction where branchId=in_BranchId and vendorId=in_venderId);
if exists(select * from tbl_vender_outstand_payment_main_transaction where branchId=in_BranchId and vendorId=in_venderId)
then
update tbl_vender_outstand_payment_main_transaction
set totalCreditAmount=totalCreditAmount+ if(in_isCreditOrDebit=2,concat('-',in_paidAmount),in_paidAmount) ,lastModifiedDate=now()
where id=in_mainTransId;

else

INSERT INTO `tbl_vender_outstand_payment_main_transaction`
(
`vendorId`,
`branchId`,
`totalCreditAmount`)
VALUES
(in_venderId,in_BranchId,in_paidAmount);

set in_mainTransId = last_insert_id();


end if;


INSERT INTO `tbl_vender_outstand_payment_each_transaction`
(
`mainTrancId`,
`vendorId`,
`branchId`,
`paidAmount`,
`userId`,
`outStandAmount`,
`paymentDate`,
`paymentModeId`,
`paymentRemarks`,
`isCreditOrDebit`,
`debitTransactionMainTableId`)
VALUES
(in_mainTransId,in_venderId,in_BranchId,in_paidAmount,in_UserId,in_totalInvAmt,in_paymentDate,in_PayModeId,in_remarks,in_isCreditOrDebit,in_debitTransactionMainTableId); -- isCreditOeDebit 1 for Credit and 2 For Debit


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_saveSupplierRegistration` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_saveSupplierRegistration`(
in branchName varchar(250),
in gstinNo varchar(250),
in branchLocationId varchar(250),
in contactNo varchar(250),
out genId int
)
BEGIN


INSERT INTO `tbl_mst_supplier`
(
`company_name`,
`nBusiness_typeId_fk`,
`registration_type`,
`GSTIN`,
`IGSTN`,
`emailId`,
`po_emailId`,
`nLocationId_fk`,
`mobile_no`,supplierFlag)
VALUES
(branchName,'1','',gstinNo,gstinNo,'','',branchLocationId,mobile_no,1);
set genId = LAST_INSERT_ID();
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_stocksIssuedToBranch` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_stocksIssuedToBranch`(
in branchId int,
in mainStckTrnfrId int
)
BEGIN
SELECT  
    issedStock_Pk,
		TOPMD.po_number,
        TOPEID.item_id_fk
        ,concat(TMID.itemShortCode,' : ',TMID.itemName,
        if(TMS.storeFlag = 1,
			concat('[',IFNULL(item_diapter,''),'][',IFNULL(item_size,''),']')
        ,	concat('[',IFNULL(item_strength,''),'][',IFNULL(item_volume,''),']')
        )) as itemName
        ,IFNULL(TOPEID.item_diapter,'') as item_diapter
        ,IFNULL(TOPEID.item_size,'') as item_size
        ,IFNULL(TOPEID.item_strength,'') as item_strength
        ,IFNULL(TOPEID.item_volume,'') as item_volume
        ,IFNULL(TSITE.issuedQty,'') as issuedQty
        ,TSITE.lastModifiedDate as issuedDateTime
        ,THB.branchHospName
        ,TSITE.batchNo
        ,DATE(TSITE.expiryDate) AS expiryDate
        ,TSITE.serialNo
        FROM 
		tbl_stocks_issued_to_emr TSITE 
       /* JOIN tbl_os_po_main_details_from_branch TOPMDFB ON TSITE.issuedToBranchPoId = TOPMDFB.apoid
        JOIN tbl_os_po_each_item_details_from_branch TOPEIDFB ON TOPEIDFB.apoeachid = TSITE.issuedToBrancheACHPoId*/
        JOIN tbl_os_po_main_details TOPMD ON TSITE.issuedToBranchPoId = TOPMD.apoid
        JOIN tbl_os_po_each_item_details TOPEID ON TOPEID.apoeachid = TSITE.issuedToBrancheACHPoId
        JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TSITE.nItemId_fk
        JOIN tblhospitalbranches THB ON THB.abranchId = TSITE.nIssuedToBranchId_fk
		JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TSITE.nStoreId_fk

        where TSITE.nIssuedFromBranchId_fk = branchId AND TSITE.nMainStockTrnsfrId = mainStckTrnfrId
        group by TSITE.issedStock_Pk -- TOPMDFB.apoid, TOPEIDFB.apoeachid
        order by issedStock_Pk desc
        limit 0,50;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updatAcceptReturnIsReservedStatus` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updatAcceptReturnIsReservedStatus`(
in eachGrnIds int,
in grnBranchIds int
)
BEGIN
UPDATE tbl_grn_availqty 
SET 
    isReserved = 0
WHERE
    nEachGrnId = eachGrnIds 
        AND nBranchId = grnBranchIds and aGrnAvailQtyId <> 0 ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateAvailableIolQtyFromEmrToOthpl` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateAvailableIolQtyFromEmrToOthpl`(
in grnAvilId int,
in in_patientId int,
in in_patientCode varchar(250),
in in_patientName varchar(250),
in in_councilId int,
in in_surId int,
in in_resevedById int,
in in_reservedByName varchar(250),
in in_isReserved int,
in in_surgeryDate date
)
BEGIN

declare isRess int;
declare con int;

set con = (select isReserved from tbl_grn_availqty where  isReserved=1 and counsilId = in_councilId limit 0,1);
if (con=1) then

set isRess = 1;
else

update tbl_grn_availqty
set
patientId = in_patientId,
patientCode = in_patientCode,
patName = in_patientName,
counsilId = in_councilId,
surgAppId = in_surId,
reservedById = in_resevedById,
reservedByName = in_reservedByName,
isReserved = in_isReserved,
surgeryDate = in_surgeryDate,
reservedDate = now()
where aGrnAvailQtyId = grnAvilId;
set isRess = 0;


end if;


select isRess as available;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateAvailableIolQtysByGrnId` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateAvailableIolQtysByGrnId`(
in transferedQtys int,
in returnedQtys int,
in returnAcceptedQtys int,
in eachGrnIds int,
in grnBranchIds int,
in returnToSuppliers int
)
BEGIN

UPDATE tbl_grn_availqty 
			SET
				transferedQty = IF(transferedQtys = -1,transferedQty, transferedQty + transferedQtys)
                ,returnedQty = IF(returnedQtys = -1,returnedQty, returnedQty + returnedQtys)
                ,returnAcceptedQty = IF(returnAcceptedQtys = -1,returnAcceptedQty, returnAcceptedQty + returnAcceptedQtys)
                ,returnToSupplier = IF(returnToSuppliers = -1,returnToSupplier,returnToSupplier + returnToSuppliers)
               ,availQty = -- ((availQty + returnAcceptedQty)- (returnedQty + transferedQty))
					IF(transferedQtys != -1, availQty + transferedQtys,
						IF(returnedQtys != -1,availQty - returnedQtys,
                            IF(returnAcceptedQtys != -1,
								availQty + returnAcceptedQty,
									availQty
								)
							)
                        )
		WHERE 
        nEachGrnId = eachGrnIds -- prev nEachGrnId = eachGrnIds
        and nBranchId = grnBranchIds AND aGrnAvailQtyId <> 0; 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateAvailableQtysByEachGrnId` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateAvailableQtysByEachGrnId`(
in transferedQtys int,
in returnedQtys int,
in returnAcceptedQtys int,
in eachGrnIds int,
in grnBranchIds int,
in returnToSuppliers int
)
BEGIN

declare storeflagId int;

set storeflagId=(SELECT storeFlag FROM  tbl_grn_availqty TGV JOIN tbl_mst_storecreation TMS ON TGV.nStoreId = TMS.aStoreId_pk where nEachGrnId = eachGrnIds and nBranchId = grnBranchIds);

UPDATE tbl_grn_availqty
			SET
				transferedQty = IF(transferedQtys = -1,transferedQty, transferedQty + transferedQtys)
                ,returnedQty = IF(returnedQtys = -1,returnedQty, returnedQty + returnedQtys)
                ,returnAcceptedQty = IF(returnAcceptedQtys = -1,returnAcceptedQty, returnAcceptedQty + returnAcceptedQtys)
                ,returnToSupplier = IF(returnToSuppliers = -1,returnToSupplier,returnToSupplier + returnToSuppliers)
               ,availQty = -- ((availQty + returnAcceptedQty)- (returnedQty + transferedQty))
					IF(transferedQtys != -1, availQty - transferedQtys,
						IF(returnedQtys != -1,availQty - returnedQtys,
                            IF(returnAcceptedQtys != -1,
								availQty + returnAcceptedQty,
									availQty
								)
							)
                        ),
                        isReserved = if(storeflagId='1',3,0),
                        consumptionDate = now()
		WHERE nEachGrnId = eachGrnIds and nBranchId = grnBranchIds;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateBranchRegistration` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateBranchRegistration`(
in branchId varchar(250),
in branchName varchar(250),
in branchCode varchar(250),
in branchLocationId varchar(250),
in poCode varchar(250),
in grnCode varchar(250),
in contactNo varchar(250),
in emergencyNo varchar(250),
in deliveryAddr varchar(250),
in billingAddr varchar(250),
in branchAddr varchar(250),
in branchPinCode varchar(250),
in gstinNo varchar(250),
in faxNo varchar(250),
in operationType varchar(250))
BEGIN

update tblhospitalbranches set
`branchHospName`=branchName,
`bhospitalAddress`=branchAddr,
`bPincode`=branchPinCode,
`bhospitalPhNo`=contactNo,
`bhospitalEmergencyNo`=emergencyNo,
`nbranchcode`=branchCode,
`bGSTINno`=gstinNo,
`hospBranch_DeliveryAddress`=deliveryAddr,
`hospBranch_BillingAddress`=billingAddr,
`nHospBranch_LocationId`=branchLocationId,
`grnShortCode`=grnCode,
`po_raise_format`=poCode,
`fax`=faxNo,
`hospetalFlag`=operationType
where abranchId=branchId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateBranchSupplier` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateBranchSupplier`(
in insupplerName varchar(250),
in inlocationId varchar(50),
in incontactNo varchar(50),
in inGstnNo varchar(250),
in reffBranchName varchar(250)
)
BEGIN

update tbl_mst_supplier 
set 
company_name=insupplerName,
nLocationId_fk=inlocationId,
mobile_no=incontactNo,
GSTIN=inGstnNo,
IGSTN=inGstnNo
where company_name=reffBranchName and aSupplierId_pk<>0;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateDebitNoteEachStatusByDebitNoteEachId` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_updateDebitNoteEachStatusByDebitNoteEachId`(
in debitNoteEachId int
)
BEGIN
UPDATE tbl_debit_note_each
SET debitStatus = 2
WHERE aEachDnId =debitNoteEachId;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateDepoStockAfterBranchReturn` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateDepoStockAfterBranchReturn`(
in returnQty int,
in depoEachGrnId int,
in grnBranchId int
)
BEGIN
UPDATE tbl_grn_for_po_each_item_orders 
SET receivedQty = receivedQty + returnQty
WHERE aGrnId_each_pk = depoEachGrnId
AND ngrnBranchId_fk = grnBranchId ;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateGrnAvailQty` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateGrnAvailQty`(
in grnAvailQtyId int,
in returnQty int,
in branchId int
)
BEGIN
 UPDATE tbl_grn_availqty
		SET returnToSupplier =  IF((returnToSupplier - returnQty) >= 0, (returnToSupplier - returnQty), 0)
	WHERE aGrnAvailQtyId = grnAvailQtyId AND nBranchId = branchId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateGrnCommercialStatus` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateGrnCommercialStatus`(
in commStatusId int,
in eachGrnId int,
in remarks text,
in updatedBy int,
in updatedDate datetime
)
BEGIN
IF(commStatusId < 4) THEN 
UPDATE tbl_grn_for_po_each_item_orders TGFPEIO
	SET nCommercialStatusId = commStatusId
    ,lastModifiedDate = now()
    ,financeUserId  = updatedBy
    ,financeDate 	= updatedDate
    ,financeRemarks = remarks
WHERE aGrnId_each_pk = eachGrnId;
ELSE 
UPDATE tbl_grn_for_po_each_item_orders TGFPEIO
	SET nCommercialStatusId = commStatusId
    ,lastModifiedDate = now()
    ,accountsUserId  = updatedBy
    ,accountsDate 	= updatedDate
    ,accountsRemarks = remarks
WHERE aGrnId_each_pk = eachGrnId;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateGrnStatus` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_updateGrnStatus`(
in in_grnId 	int,
in in_branchId  int,
in in_storeId 	int,
in in_statusVal int,
in in_statusRem text,
in in_commericalStatusBy int,
in in_commercialStatusUpdatedDate datetime
)
BEGIN

IF (in_statusVal <= 3) THEN 
UPDATE tbl_grn_for_po_orders 
SET 
    nCommertialStatus = in_statusVal,
    statusRemarks = in_statusRem,
    commercialStatusUpdatedBy = in_commericalStatusBy,
    commercialStatusUpdatedDate = in_commercialStatusUpdatedDate
WHERE
    aGrnId_pk = in_grnId;
ELSE
UPDATE tbl_grn_for_po_orders 
SET 
    accountsStatusId = in_statusVal,
    accountsRemarks = in_statusRem,
    accountsUserId = in_commericalStatusBy,
    accountsDate = in_commercialStatusUpdatedDate
WHERE
    aGrnId_pk = in_grnId;
END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateHSNCodeForMasters` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_updateHSNCodeForMasters`(
in in_hsnCode varchar(250),
in in_gstPerc double,
in in_sgstPerc double ,
in in_cgstPerc double ,
in in_igstPerc double  ,
in in_hsnId int
)
BEGIN


UPDATE `tbl_mst_hsn_codes`
SET
`hsnCode` = in_hsnCode,
`gstPercentage` = in_gstPerc,
`sgstPercentage` = in_sgstPerc,
`cgstPercentage` = in_cgstPerc,
`igstPercentage` = in_igstPerc ,
`lastModifiedDate` = now()
WHERE `aHsnAutoId_pk` = in_hsnId;



END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateIndentQtyToSupItemMapTable` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateIndentQtyToSupItemMapTable`(
in itemId int,
in reqQuaty int ,
in indentToBrach int
)
BEGIN

UPDATE tbl_supplier_item_mapping 
SET 
    ReqQty = ReqQty+reqQuaty
WHERE
nBranchId = indentToBrach AND nItemId_fk = itemId AND isAtPriority = 1;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateItemDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_updateItemDetails`(
in_nStoreId_fk int,
in_itemName varchar(250),
in_nBrandTypeId_fk int,
in_nItemManufacturerId_fk int,
in_itemDescription text,
in_genericName text,
in_nHsnCodeId_fk varchar(100),
in_UNIT varchar(45),
in_itemId int
)
BEGIN


UPDATE `tbl_mst_items_details`
SET
`nStoreId_fk` = in_nStoreId_fk,
`itemName` = in_itemName,
`nBrandTypeId_fk` =in_nBrandTypeId_fk,
`itemDescription` = in_itemDescription,
`nItemManufacturerId_fk` = in_nItemManufacturerId_fk,
`genericName` = in_genericName,
`nHsnCodeId_fk` = in_nHsnCodeId_fk,
`UNIT` = in_UNIT,
`lastModifiedDate` = now(),
`nUserId_fk` =1
WHERE `aItemId_pk` = in_itemId;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updatePoAuthorizationStatus` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updatePoAuthorizationStatus`(
in poStatus int,
in PoId int,
in poUserId int,
in poDate date,
in reasons text
)
BEGIN
	update tbl_os_po_main_details
	set po_status = poStatus,
	po_authorized_by_fk = poUserId,
	po_authorized_date = poDate,
    reasonForAuthOrRej = reasons
	Where apoid = PoId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updatePoEachItemsStatus` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updatePoEachItemsStatus`(
in eachPoId int,
in statusId int
)
BEGIN
UPDATE tbl_os_po_each_item_details
SET poItemStatusId = statusId
WHERE apoeachid = eachPoId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updatePoItemPrice` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updatePoItemPrice`(
in in_poeachId int,
in in_newUnitPrice float,
in in_supitemMapId int,
in in_TATinDays int,
in in_nBranchId int,
in in_nhsnId_fk int,
in in_freeQty int,
in in_nUserId_fk int,
in in_nSupplierId_fk int,
in in_itemId int,
in in_isAtPriority int,
in in_itemHsnCode varchar(45),
in in_sgstPercentage int,
in in_cgstPercentage int,
in in_igstPercentage int,
in in_gstPercentage int,
in in_totaAmountwithoutTax float,
in in_totaAmountwithTax float,
in in_sgstAmount float,
in in_cgstAmount float,
in in_igstAmount float,
in in_gstAmount float,
in in_mainPoId int
)
BEGIN

declare suplierItemMapId int;
declare oldTotalAmount float;

update tbl_supplier_item_mapping set isActive=0 
where supplier_item_mappingId=in_supitemMapId;


INSERT INTO `tbl_supplier_item_mapping`
(
`nSupplierId_fk`,
`nItemId_fk`,
`TATinDays`,
`nBranchId`,
`nhsnId_fk`,
`freeQty`,
`nUserId_fk`,
`isAtPriority`,
`purchasePrice`,
`sellingPrice`,
`MRP`)
VALUES
(
in_nSupplierId_fk,
in_itemId,
in_TATinDays,
in_nBranchId,
in_nhsnId_fk,
in_freeQty,
in_nUserId_fk,
in_isAtPriority,
in_newUnitPrice,
in_newUnitPrice,
in_newUnitPrice
); 

set suplierItemMapId =last_insert_id();

set oldTotalAmount=(select IFNULL(item_total_amt_with_gst,0) as oldtotalAmtWithGst from tbl_os_po_each_item_details where apoeachid=in_poeachId);

update tbl_os_po_main_details set
po_total_amount = po_total_amount-oldTotalAmount+in_totaAmountwithTax + freight_charge - discount_amount,
final_total_amount = final_total_amount -oldTotalAmount + in_totaAmountwithTax + freight_charge - discount_amount
where apoid=in_mainPoId;

update tbl_os_po_each_item_details set
 nSupplierItemMapid=suplierItemMapId,
 item_hsn_code=in_itemHsnCode,
 item_unit_price=in_newUnitPrice,
 item_gst_percent=in_gstPercentage,
 item_gst_rs=in_gstAmount,
 item_sgst_percent=in_sgstPercentage,
 item_sgst_rs=in_sgstAmount,
 item_cgst_percent=in_cgstPercentage,
 item_cgst_rs=in_cgstAmount,
 item_igst_percent=in_igstPercentage,
 item_igst_rs=in_igstAmount,
 item_total_amt_without_gst=in_totaAmountwithoutTax,
 item_total_amt_with_gst=in_totaAmountwithTax
 where apoeachid=in_poeachId;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updatePOMainAndEachStatus` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updatePOMainAndEachStatus`(
in poStatus int,
in PoId int,
in poUserId int,
in poDate date,
in reasons text
)
BEGIN
	update tbl_os_po_main_details
	set po_status = poStatus,
	po_authorized_by_fk = poUserId,
	po_authorized_date = poDate,
    reasonForAuthOrRej = reasons
	Where apoid = PoId;
  
  update   tbl_os_po_each_item_details
  SET poItemStatusId = poStatus,
  nUserId_fk = poUserId
  Where npoid_fk = PoId;
  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updatereturnStatus` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_updatereturnStatus`(
in eachReturnId int,
in returnStatuss tinyint,
in authUserId int,
in authDate date,
in authComments text,
in loggedInUserId int,
in stockIn int
)
BEGIN
 UPDATE tbl_grn_return_each_details 
		SET returnStatus = returnStatuss
			,nAuthUserId = authUserId
            ,authDate = authDate
            ,authComments = authComments
            ,lastModifiedDate = now()
            ,nUserId_fk = loggedInUserId
            ,isStockIn = stockIn
	WHERE aReturnEachId = eachReturnId;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateStockTransferDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateStockTransferDetails`(
in isApproveds tinyint,
in approvedBy int,
in approverRemarks text,
in eachTransferId int
)
BEGIN
DECLARE eachGrnId int;
DECLARE transferredQty int;
UPDATE tbl_stocks_issued_to_emr
	SET isApproved = isApproveds,
    approvedBy = approvedBy,
    approverRemarks = approverRemarks,
    approvedDateTime = NOW()
    WHERE issedStock_Pk = eachTransferId;

/*
 * UPDATE QTY OF GRN ITEM IF STATUS = REJECTED (2)
 */
 IF(isApproveds = 2) THEN
	SET eachGrnId = (SELECT ngrnEachId_fk FROM 
    tbl_stocks_issued_to_emr WHERE issedStock_Pk = eachTransferId
    LIMIT 0,1);
    SET transferredQty = (SELECT issuedQty FROM 
    tbl_stocks_issued_to_emr WHERE issedStock_Pk = eachTransferId
    LIMIT 0,1);
	
    UPDATE tbl_grn_availqty
		SET availQty = (IFNULL(availQty,0) + IFNULL(transferredQty, 0))
	WHERE nEachGrnId = eachGrnId AND aGrnAvailQtyId = eachGrnId;
 END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_updateSupplierDetails` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_updateSupplierDetails`(
in insupplierName varchar(100),
in inbussinessType varchar(50),
in inregistrationtype varchar(50),
in ingstnnNo varchar(45),
in inigstnNo varchar(45),
in inEmailId  varchar(255),
in inpoEmaiId varchar(255),
in inLocationId varchar(50),
in incrediPeriod int(11),
in incompAddress varchar(255),
in inpoAddress varchar(255),
in inmobileNo varchar(10),
in insupplierId int
)
BEGIN

UPDATE `tbl_mst_supplier`
SET
`company_name` = insupplierName,
`nBusiness_typeId_fk` = inbussinessType,
`registration_type` =inregistrationtype,
`GSTIN` = ingstnnNo,
`IGSTN` =inigstnNo,
`emailId` =inEmailId,
`po_emailId` =inpoEmaiId,
`nLocationId_fk` = inLocationId,
`credit_period` = incrediPeriod,
`com_address` = incompAddress,
`po_address` =inpoAddress,
`mobile_no` =inmobileNo,
`lastModifiedDate` = now()
WHERE `aSupplierId_pk` = insupplierId;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_frombranch_eachpo_afterraised` */;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_update_frombranch_eachpo_afterraised`(
in apoeachids text
)
BEGIN
UPDATE tbl_os_po_each_item_details_from_branch 
SET 
    is_po_raised = 1
WHERE
    FIND_IN_SET(apoeachid, apoeachids);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_update_IOLDeatils_ToPatient` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_update_IOLDeatils_ToPatient`(
in_consumAuid int,
in_nGrnAvailId int,
in_usedQty int,
in_serialNo varchar(100),
in_batchNo  varchar(100),
in_diapter  varchar(100),
in_size  varchar(100),
in_nItemId int,
in_itemName varchar(250),
in_itemShortCode varchar(250),
in_expiryDate varchar(50)
)
BEGIN

update tbl_ot_stock_consumption_each_details

set 
`nGrnAvailId`=in_nGrnAvailId,
`usedQty`=in_usedQty,
`serialNo`=in_serialNo,
`batchNo`= in_batchNo,
`diapter`=in_diapter,
`size`=in_size,
`nItemId`=in_nItemId,
`itemName`=in_itemName,
`itemShortCode`=in_itemShortCode,
`expiryDate` = in_expiryDate,
`lastModified` = now()
where `aOtStockConsumptionEachId` = in_consumAuid;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_user_login_succes_responce` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_user_login_succes_responce`(
in userId int,
in branchid int
)
BEGIN


 select distinct moduleId,userId, userName, password, imageName,
    
    aModuleId, moduleName,TUM.isActive, isHavingSubModule, moduleId, fnOnClick,moduleIcon, ifnull(t2.nHospBranch_LocationId, -1) nHospBranch_LocationId
   , t2.hospetalFlag
    from tbl_user TU
    left join tbl_user_module_map TUM on TU.userId = TUM.nUserId 
    left join tbl_modules TM on TM.aModuleId = TUM.nModuleId
    join 
		(select * from tbl_user_branches_map where nBranchId = branchid) t1 on t1.nUserId = TUM.nUserId
	join 
		(select * from tblhospitalbranches where abranchId = branchid) t2 on t2.abranchId = t1.nBranchId
    where TU.userId = userId 
   -- and find_in_set(marketIds, TU.nMarketIds)-- TU.nMarketIds in (marketIds)
    and TU.isActive = 1 and TU.isDeleted = 0 and TUM.isActive = 1 and TUM.bDeleted = 0  AND TM.isMobileOrWeb = 1
    -- and TU.nHospitalId = hospId and TU.nBranchId = branchid
   --  and TUM.nHospitalId = hospId  and TUM.nBranchId = branchid
    order by priority;  

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_validate_user_login` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_validate_user_login`(
in userNames varchar(100),
in passwords varchar(250),
in hospId int,
in branchid int
)
BEGIN	 
    select distinct moduleId,userId, userName, password, imageName,
    
    aModuleId, moduleName,TUM.isActive, isHavingSubModule, moduleId, fnOnClick,moduleIcon, ifnull(t2.nHospBranch_LocationId, -1) nHospBranch_LocationId
   , t2.hospetalFlag,t2.branchHospName,t2.hospBranch_DeliveryAddress
    from tbl_user TU
    left join tbl_user_module_map TUM on TU.userId = TUM.nUserId 
    left join tbl_modules TM on TM.aModuleId = TUM.nModuleId
    join 
		(select * from tbl_user_branches_map where nBranchId = branchid) t1 on t1.nUserId = TUM.nUserId
	join 
		(select * from tblhospitalbranches where abranchId = branchid) t2 on t2.abranchId = t1.nBranchId
    where TU.userName = userNames and TU.password = passwords
   -- and find_in_set(marketIds, TU.nMarketIds)-- TU.nMarketIds in (marketIds)
    and TU.isActive = 1 and TU.isDeleted = 0 and TUM.isActive = 1 and TUM.bDeleted = 0  AND TM.isMobileOrWeb = 1
    -- and TU.nHospitalId = hospId and TU.nBranchId = branchid
   --  and TUM.nHospitalId = hospId  and TUM.nBranchId = branchid
    order by priority;   
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_validate_user_login_branchuser` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_validate_user_login_branchuser`(
in userNames varchar(100),
in passwords varchar(250),
in hospId int,
in branchid int
)
BEGIN
select userId, userName,  imageName, password
    from tbl_user TU
    where TU.userName = userNames and TU.password = passwords
		and TU.isActive = 1 and TU.isDeleted = 0 and TU.isWebOrMobileUser = 1 
		and TU.IsCentralOrBranchUser = 2 and find_in_set(branchid, TU.nBranchId);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `sproc_verifyCounterOpened` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_verifyCounterOpened`(
in thisDate date,
in branchId int
)
BEGIN
select count(*) as counters from tbl_dailystock_details TDSD
	-- JOIN tbl_supplier_item_mapping TSIM on TSIM.supplier_item_mappingId = TDSD.nItemRateId
WHERE DATE(TDSD.stockDate) = thisDate 
  and  
	IF(branchId = -1, TRUE ,TDSD.nBranchId = branchId);
END ;;
DELIMITER ;


