
-- 20-12-23


USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtItemName_Autosugest`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtItemName_Autosugest`(
in itemShortCode varchar(100),
in branchId int,
in in_storeId int

)
BEGIN
if(in_storeId = '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
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
WHERE
    TGA.availQty > 0
        AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
        AND TMID.isActive = 1
        AND TMID.itemName LIKE CONCAT(itemShortCode, '%')
        AND TGA.nBranchId = branchId
		
GROUP BY TGA.nItemId , TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC, TGFPEIO.serialNumber;
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
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
WHERE
    TGA.availQty > 0
        AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
        AND TMID.isActive = 1
        AND TMID.itemName LIKE CONCAT(itemShortCode, '%')
        AND TGA.nBranchId = branchId
		
GROUP BY TGA.nItemId , TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC;
end if;

END$$
go
DELIMITER ;








USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode`(
in itemShortCode varchar(100),
in branchId int,
in in_storeId int,
in in_grnAvailableId varchar(250)
)
BEGIN

if(in_storeId = '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
    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
    WHERE TGA.availQty > 0
    AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
  --  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) ASC, 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
    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
    WHERE TGA.availQty > 0
    AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
  --  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) ASC
        ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
end if;


END$$
go
DELIMITER ;









USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getEachItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getEachItemDetailsWrtCode`(
in itemShortCode varchar(100),
in branchId int,
in in_storeId int,
in in_grnAvailableId varchar(250)
)
BEGIN

if(in_storeId = '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
    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
    WHERE TGA.availQty > 0
    AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
    -- 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) ASC, 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
    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
    WHERE TGA.availQty > 0
    AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
    -- 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) ASC
    ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
end if;



END$$
go
DELIMITER ;






USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode_Autosugest`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode_Autosugest`(
in itemShortCode varchar(100),
in branchId int,
in in_storeId int
)
BEGIN

if(in_storeId = '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  
    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
    WHERE TGA.availQty > 0
    AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
  
    AND TMID.isActive = 1
    AND TMID.itemName like concat(itemShortCode,'%')
    AND TGA.nBranchId = branchId
    GROUP BY TGA.nItemId ,TGFPEIO.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC, TGFPEIO.serialNumber;
    
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
    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
    WHERE TGA.availQty > 0
    AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
   
    AND TMID.isActive = 1
    AND TMID.itemName like concat(itemShortCode,'%')
    AND TGA.nBranchId = branchId
    GROUP BY TGA.nItemId ,TGFPEIO.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC;
end if;

END$$
go
DELIMITER ;






USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode_byStrenthAndValume`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode_byStrenthAndValume`(
in itemShortCode varchar(100),
in branchId int,
in strenth int,
in valume int,
in in_storeId int,
in in_grnAvailableId varchar(250)

)
BEGIN
if(in_storeId = '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,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber
    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
    WHERE TGA.availQty > 0
    AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
         AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)

    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGFPEIO.strength=strenth and TGFPEIO.volume=valume
    GROUP BY TGA.nItemId ,TGFPEIO.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC, TGFPEIO.serialNumber;
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,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber
    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
    WHERE TGA.availQty > 0
    AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
         AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)

    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGFPEIO.strength=strenth and TGFPEIO.volume=valume
    GROUP BY TGA.nItemId ,TGFPEIO.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC;
end if;

END$$
go
DELIMITER ;






USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getEachItemDetailsWrtCode_byStrenthAndValume`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getEachItemDetailsWrtCode_byStrenthAndValume`(
in itemShortCode varchar(100),
in branchId int,
in strenth int,
in valume int,
in in_storeId int,
in in_grnAvailableId varchar(250)
)
BEGIN

if(in_storeId = '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.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
        TBLSC.storeFlag,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber
    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
    WHERE TGA.availQty > 0
    AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
    -- AND TMID.nStoreId_fk = 2
    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
             AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)

    AND TGA.nBranchId = branchId and TGFPEIO.strength=strenth and TGFPEIO.volume=valume
    GROUP BY  TGA.aGrnAvailQtyId,TMID.itemShortCode ,TGFPEIO.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC, 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.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
        TBLSC.storeFlag,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber
    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
    WHERE TGA.availQty > 0
    AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
    -- AND TMID.nStoreId_fk = 2
    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
             AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)

    AND TGA.nBranchId = branchId and TGFPEIO.strength=strenth and TGFPEIO.volume=valume
    GROUP BY  TGA.aGrnAvailQtyId,TMID.itemShortCode ,TGFPEIO.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC
    ORDER BY DATE(TGFPEIO.expiryDate) ASC;
end if;



END$$
go
DELIMITER ;




-- 21-12-23



ALTER TABLE `ophthalstock`.`tbl_mst_storecreation` 
ADD COLUMN `isMandatoryBatchAndExpiry` INT NULL DEFAULT 0 AFTER `storeFlag`;






-- 22-12-23



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtItemName_Autosugest`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtItemName_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

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
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
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) ASC, TGFPEIO.serialNumber;
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
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
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) ASC;
end if;

END$$
go
DELIMITER ;




USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode`(
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
 
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
    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
    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) ASC, 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
    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
    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) ASC
        ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
end if;


END$$
go
DELIMITER ;




USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getEachItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getEachItemDetailsWrtCode`(
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


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
    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
    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) ASC, 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
    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
    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) ASC
    ORDER BY DATE(TGFPEIO.expiryDate) ASC  ;
end if;



END$$
go
DELIMITER ;




USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode_Autosugest`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode_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


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  
    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
    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.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC, TGFPEIO.serialNumber;
    
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
    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
    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.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC;
end if;

END$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode_byStrenthAndValume`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode_byStrenthAndValume`(
in itemShortCode varchar(100),
in branchId int,
in strenth int,
in valume 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


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,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber
    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
    WHERE TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
         AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)

    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGFPEIO.strength=strenth and TGFPEIO.volume=valume
    GROUP BY TGA.nItemId ,TGFPEIO.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC, TGFPEIO.serialNumber;
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,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber
    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
    WHERE TGA.availQty > 0
        AND if(isMandatoryBatchAndExpiryflag = 1 ,DATE(TGFPEIO.expiryDate) > DATE(NOW()),true) 
         AND  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)

    AND TMID.isActive = 1
    AND TMID.itemShortCode = itemShortCode
    AND TGA.nBranchId = branchId and TGFPEIO.strength=strenth and TGFPEIO.volume=valume
    GROUP BY TGA.nItemId ,TGFPEIO.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC;
end if;

END$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getEachItemDetailsWrtCode_byStrenthAndValume`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getEachItemDetailsWrtCode_byStrenthAndValume`(
in itemShortCode varchar(100),
in branchId int,
in strenth int,
in valume 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

 
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,
        TBLSC.storeFlag,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber
    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
    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  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)

    AND TGA.nBranchId = branchId and TGFPEIO.strength=strenth and TGFPEIO.volume=valume
    GROUP BY  TGA.aGrnAvailQtyId,TMID.itemShortCode ,TGFPEIO.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC, 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.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
        TBLSC.storeFlag,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber
    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
    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  find_in_set(TGA.aGrnAvailQtyId,in_grnAvailableId)

    AND TGA.nBranchId = branchId and TGFPEIO.strength=strenth and TGFPEIO.volume=valume
    GROUP BY  TGA.aGrnAvailQtyId,TMID.itemShortCode ,TGFPEIO.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC
    ORDER BY DATE(TGFPEIO.expiryDate) ASC;
end if;



END$$
go
DELIMITER ;





 
-- 22-12-23       2


USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemsForReturn`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getItemsForReturn`(
in branchId int,
in storeId int,
in itemId int,
in batchNo varchar(25),
in poType int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN

declare isMandatoryBatchAndExpiryflag int;

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




SELECT 
		TGFPO.aGrnId_pk AS mainGrnId
        ,TGFPO.nSupplierId_fk AS supplierId
        ,TGFPEIO.aGrnId_each_pk AS eachGrnId
        ,TGFPEIO.nMainTransferId AS mainTransferId
        ,TGFPEIO.nEachTransferId AS eachTransferId
		,TGFPO.grnNumber
        ,TMS.company_name
        ,TGFPEIO.nSupplierItemMapid AS supplierItemMapId
        ,TGFPEIO.serialNumber
        ,DATE(TGFPEIO.expiryDate) AS expiryDate
        ,TGFPEIO.batchNo 
        ,IF(TMSC.storeFlag=1,TGFPEIO.diapter,TGFPEIO.strength) AS dioStr
        ,IF(TMSC.storeFlag=1,TGFPEIO.size,TGFPEIO.volume) AS sizVol
		-- ,UDF_getItemAvailableQtyByItemId(itemId, branchId, -1) AS availQty
        -- ,UDF_getAvailableQtyInGrn(TGFPEIO.aGrnId_each_pk) AS availQty
        ,TGA.availQty  AS availQty
        ,TGA.availQty  AS retQty
        ,THB1.abranchId AS grnBranchId
        ,THB1.branchHospName AS grnBranch
        ,THB2.abranchId AS receivedFromBranchId
        ,THB2.branchHospName AS receivedFromBranch
        ,THB2.nbranchcode AS receivedFromBranchCode
        --
        ,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) AS taxAmt,
        (IFNULL(TSIM.purchasePrice,0) + (IFNULL(TMHC.gstPercentage,0)/100.0) * IFNULL(TSIM.purchasePrice,0)) AS itemPrice
       ,TMID.itemName as itemName
      
      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
        
        -- JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TGFPO.nSupplierId_fk
        LEFT JOIN tbl_stocks_issued_to_emr TSITE ON TSITE.issedStock_Pk = TGFPEIO.nEachTransferId
        JOIN tblhospitalbranches THB1 ON THB1.abranchId = TGFPEIO.ngrnBranchId_fk
        LEFT JOIN tblhospitalbranches THB2 ON THB2.abranchId = TSITE.nIssuedFromBranchId_fk
        -- 
        JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TGFPEIO.nItemId_fk

		JOIN tbl_mst_storecreation TMSC ON TMSC.aStoreId_pk = TGFPEIO.nStoreId_fk

        -- JOIN tbl_supplier_item_mapping TSIM ON TSIM.supplier_item_mappingId = TGFPEIO	.nSupplierItemMapid
		JOIN tbl_supplier_item_mapping TSIM ON 
         -- CONSIGNMENT STOCK ACCEPTANCE RETURN WITH SUPPLIER MAP ID = NULL
			/*IF(TGFPEIO.nPoType_fk = 0
				,TRUE
				,TSIM.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid)*/
		TSIM.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
		JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk -- TGFPO.nSupplierId_fk
        		JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnAutoId_pk = TSIM.nhsnId_fk
	WHERE TGFPEIO.nItemId_fk = itemId
			AND TGFPEIO.ngrnBranchId_fk = branchId

            AND if(isMandatoryBatchAndExpiryflag = 1,TGFPEIO.batchNo = batchNo,true) 
              AND TGFPEIO.nPoType_fk = poType 
              
              AND TGFPEIO.nStoreId_fk = storeId and  TGA.availQty > 0
          --  AND (TGFPEIO.nPoType_fk = poType OR TGFPEIO.nPoType_fk = 0) -- CONSIGNMENT STOCK ACCEPTANCE RETURN WITH PO TYPE ID = 0
          --  AND TSITE.nIssuedFromBranchId_fk = 0;
          GROUP BY TGFPEIO.aGrnId_each_pk
	ORDER BY receivedFromBranchId, eachGrnId DESC
   LIMIT minlimitRow , maxlimitRow ;
END$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemsForReturnByMonths`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemsForReturnByMonths`(
in branchId int,
in storeId int,
in expiryDate date,
in minlimitRow int,
in maxlimitRow int
)
BEGIN

declare isMandatoryBatchAndExpiryflag int;

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



SELECT 
		TGFPO.aGrnId_pk AS mainGrnId
        ,TGFPO.nSupplierId_fk AS supplierId
        ,TGFPEIO.aGrnId_each_pk AS eachGrnId
        ,TGFPEIO.nMainTransferId AS mainTransferId
        ,TGFPEIO.nEachTransferId AS eachTransferId
		,TGFPO.grnNumber
        ,TMS.company_name
        ,TGFPEIO.nSupplierItemMapid AS supplierItemMapId
        ,TGFPEIO.serialNumber
        ,DATE(TGFPEIO.expiryDate) AS expiryDate
        ,TGFPEIO.batchNo 
        ,IF(TMSC.storeFlag=1,TGFPEIO.diapter,TGFPEIO.strength) AS dioStr
        ,IF(TMSC.storeFlag=1,TGFPEIO.size,TGFPEIO.volume) AS sizVol
		-- ,UDF_getItemAvailableQtyByItemId(itemId, branchId, -1) AS availQty
        -- ,UDF_getAvailableQtyInGrn(TGFPEIO.aGrnId_each_pk) AS availQty
        ,TGA.availQty  AS availQty
        ,TGA.availQty  AS retQty
        ,THB1.abranchId AS grnBranchId
        ,THB1.branchHospName AS grnBranch
        ,THB2.abranchId AS receivedFromBranchId
        ,THB2.branchHospName AS receivedFromBranch
        ,THB2.nbranchcode AS receivedFromBranchCode
        --
        ,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) AS taxAmt,
        (IFNULL(TSIM.purchasePrice,0) + (IFNULL(TMHC.gstPercentage,0)/100.0) * IFNULL(TSIM.purchasePrice,0)) AS itemPrice,
        TMID.itemName as itemName
        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
        
        -- JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TGFPO.nSupplierId_fk
        LEFT JOIN tbl_stocks_issued_to_emr TSITE ON TSITE.issedStock_Pk = TGFPEIO.nEachTransferId
        JOIN tblhospitalbranches THB1 ON THB1.abranchId = TGFPEIO.ngrnBranchId_fk
        LEFT JOIN tblhospitalbranches THB2 ON THB2.abranchId = TSITE.nIssuedFromBranchId_fk
        -- 
        JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TGFPEIO.nItemId_fk

		JOIN tbl_mst_storecreation TMSC ON TMSC.aStoreId_pk = TGFPEIO.nStoreId_fk

        -- JOIN tbl_supplier_item_mapping TSIM ON TSIM.supplier_item_mappingId = TGFPEIO	.nSupplierItemMapid
		JOIN tbl_supplier_item_mapping TSIM ON 
         -- CONSIGNMENT STOCK ACCEPTANCE RETURN WITH SUPPLIER MAP ID = NULL
			/*IF(TGFPEIO.nPoType_fk = 0
				,TRUE
				,TSIM.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid)*/
		TSIM.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
		JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk -- TGFPO.nSupplierId_fk
        		JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnAutoId_pk = TSIM.nhsnId_fk
	WHERE 
    if(isMandatoryBatchAndExpiryflag=1,DATE(TGFPEIO.expiryDate) < expiryDate and DATE(TGFPEIO.expiryDate) > DATE(NOW()),true)
     AND
			 TGFPEIO.ngrnBranchId_fk = branchId
                           AND TGFPEIO.nStoreId_fk = storeId and TGA.availQty > 0

          --  AND (TGFPEIO.nPoType_fk = poType OR TGFPEIO.nPoType_fk = 0) -- CONSIGNMENT STOCK ACCEPTANCE RETURN WITH PO TYPE ID = 0
          --  AND TSITE.nIssuedFromBranchId_fk = 0;
          GROUP BY TGFPEIO.aGrnId_each_pk
	ORDER BY receivedFromBranchId, eachGrnId DESC
   LIMIT minlimitRow , maxlimitRow ;
   
   
END$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode_Autosugest`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode_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


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  
    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
    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.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC, 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
    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
    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.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC limit 10;
end if;

END$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtItemName_Autosugest`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtItemName_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

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
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
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) ASC, 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
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
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) ASC limit 10;
end if;

END$$
go
DELIMITER ;



UPDATE tbl_mst_storecreation 
SET 
    isMandatoryBatchAndExpiry = 1
WHERE
    aStoreId_pk <= 3;
	
	
	
	
	
	
	
	
	

-- 28-12-23


INSERT INTO `ophthalstock`.`tbl_mst_potypes` (`poTypeNAme`, `poTypeShortName`) VALUES ('Dispatch', 'DISP');


INSERT INTO `ophthalstock`.`tbl_mst_module_features` (`nModuleId`, `featureName`) VALUES ('8', 'DIRECT_STOCK_ISSUE');




USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtItemName_Autosugest`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtItemName_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) ASC, 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) ASC 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$$
go
DELIMITER ;







USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getTransferredDcItemsList`;

DELIMITER $$
USE `ophthalstock`$$
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$$
go
DELIMITER ;


USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsByDcId`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getItemDetailsByDcId`(
in dcId int,
in branchId int
)
BEGIN

SELECT 
	TSTM.aMainStckTrnfrId AS mainTransferId,
    TSITE.issedStock_Pk AS eachTransferId,
	TMID.aItemId_pk as itemId,
	TMID.itemShortCode AS itemCode,
	TMID.itemName AS itemName,
	IF(TMS.storeFlag=1,TGFPEIO.diapter,TGFPEIO.strength) as diaptOrStrength,
	IF(TMS.storeFlag=1,TGFPEIO.size,TGFPEIO.volume) as strOrVol,
    TSITE.issuedToBranchPoId AS branchMainPoId,
    TSITE.issuedToBrancheACHPoId AS branchEachPoId,
	TSITE.batchNo as batchNo,
	DATE(TSITE.expiryDate) AS expDate,
	TSITE.serialNo AS serialNo,
	TSITE.issuedQty AS issuedQty,
	-- TSITE.availQty AS availQty,
	TGFPEIO.nGrnTypeId AS grnTypeId,
    UDF_getIndentedItemsTransferPendingQty(branchId, TSITE.issuedToBrancheACHPoId) AS itemPendingQty,
    TOPEID.nSupplierItemMapid AS supplierMapId,
    TGFPEIO.unitPrice,
    TBLIHD.gstPercentage
 FROM tbl_stocks_issued_to_emr AS TSITE
JOIN tbl_stock_transfer_maindetails AS TSTM ON TSTM.aMainStckTrnfrId = TSITE.nMainStockTrnsfrId
JOIN tbl_grn_for_po_each_item_orders AS TGFPEIO ON TGFPEIO.aGrnId_each_pk =  TSITE.ngrnEachId_fk
JOIN tbl_os_po_each_item_details TOPEID ON TOPEID.apoeachid = TSITE.issuedToBrancheACHPoId
JOIN tbl_mst_items_details AS TMID ON TMID.aItemId_pk = TGFPEIO.nItemId_fk
JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TGFPEIO.nStoreId_fk
JOIN
tbl_supplier_item_mapping TBLISMTD ON TBLISMTD.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
JOIN
tbl_mst_hsn_codes TBLIHD ON TBLIHD.aHsnAutoId_pk = TBLISMTD.nhsnId_fk
WHERE TSITE.nMainStockTrnsfrId=dcId
	 AND TSITE.nIssuedToBranchId_fk = branchId
GROUP BY TSITE.issedStock_Pk ;

END$$
go
DELIMITER ;




USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getHospitalBranchesList`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getHospitalBranchesList`()
BEGIN
 select abranchId, branchHospName, nbranchcode,hospetalFlag from tblhospitalbranches
 WHERE branchActive = 1;
END$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode`(
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
    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
    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) ASC, 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
    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
    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
    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
    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) ASC
        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
    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
    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$$
go
DELIMITER ;





-- 28-12-23  2


USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode`(
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) ASC, 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) ASC
        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$$
go
DELIMITER ;




USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getEachItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getEachItemDetailsWrtCode`(
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) ASC, 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) ASC
    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$$
go
DELIMITER ;




-- 29-12-23


USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getIndentItemsDetails`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getIndentItemsDetails`(
in indentNumber text,
in branchId int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN
SELECT 	
		TOPMD.apoid,
				TOPMD.po_number,
				TOPEID.item_id_fk ,
				TMID.itemShortCode,
                TMID.itemName,
                IF(TMS.storeFlag = 1, TOPEID.item_diapter, TOPEID.item_strength) AS dioOrStr,
                IF(TMS.storeFlag = 1, TOPEID.item_size, TOPEID.item_volume) AS SizoRVol,
                TMID.nStoreId_fk,
                TMS.storeShortCode,
                TMS.storeName,
                po_to_supplier_id_fk as supplierId,
                IFNULL(TOPEID.item_order_qty,0) AS itemOrderQty,
              --  IFNULL(UDF_getPoItemsBalanceQtyWhileGrn(TOPMD.apoid, TOPEID.apoeachid),0) AS itemsPending,
                DATE(TOPMD.po_date) AS poDate,
                TUP.userName AS indentedUser,TMS.storeFlag,
                UDF_getIndentedItemsTransferPendingQty(branchId, TOPEID.apoeachid) AS itemsPending,
                TOPEID.apoeachid,
                TOPMD.po_type_fk as poTypeId
		FROM tbl_os_po_main_details TOPMD
			JOIN tbl_os_po_each_item_details TOPEID ON TOPEID.npoid_fk = TOPMD.apoid
            JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TOPEID.item_id_fk
            JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TMID.nStoreId_fk
            JOIN tbl_user TUP ON TUP.userId = TOPEID.nUserId_fk
		WHERE po_number = indentNumber
			AND TOPMD.po_from_branch_fk = branchId
			AND TOPEID.isActive = 1
            AND TOPEID.isDeleted = 0
            AND TOPMD.typeOfRequest = 2
            LIMIT minlimitRow , maxlimitRow ;
END$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsByDcId`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getItemDetailsByDcId`(
in dcId int,
in branchId int
)
BEGIN

SELECT 
	TSTM.aMainStckTrnfrId AS mainTransferId,
    TSITE.issedStock_Pk AS eachTransferId,
	TMID.aItemId_pk as itemId,
	TMID.itemShortCode AS itemCode,
	TMID.itemName AS itemName,
	IF(TMS.storeFlag=1,TGFPEIO.diapter,TGFPEIO.strength) as diaptOrStrength,
	IF(TMS.storeFlag=1,TGFPEIO.size,TGFPEIO.volume) as strOrVol,
    TSITE.issuedToBranchPoId AS branchMainPoId,
    TSITE.issuedToBrancheACHPoId AS branchEachPoId,
	TSITE.batchNo as batchNo,
	DATE(TSITE.expiryDate) AS expDate,
	TSITE.serialNo AS serialNo,
	TSITE.issuedQty AS issuedQty,
	-- TSITE.availQty AS availQty,
	TGFPEIO.nGrnTypeId AS grnTypeId,
    UDF_getIndentedItemsTransferPendingQty(branchId, TSITE.issuedToBrancheACHPoId) AS itemPendingQty,
    TOPEID.nSupplierItemMapid AS supplierMapId,
    TGFPEIO.unitPrice,
    TBLIHD.gstPercentage,
	TSITE.nstockType_fk as poTypeId
 FROM tbl_stocks_issued_to_emr AS TSITE
JOIN tbl_stock_transfer_maindetails AS TSTM ON TSTM.aMainStckTrnfrId = TSITE.nMainStockTrnsfrId and TSITE.nMainStockTrnsfrId=dcId
JOIN tbl_grn_for_po_each_item_orders AS TGFPEIO ON TGFPEIO.aGrnId_each_pk =  TSITE.ngrnEachId_fk
JOIN tbl_os_po_each_item_details TOPEID ON TOPEID.apoeachid = TSITE.issuedToBrancheACHPoId
JOIN
tbl_os_po_main_details TBLPOMD ON TBLPOMD.apoid = TOPEID.npoid_fk
JOIN tbl_mst_items_details AS TMID ON TMID.aItemId_pk = TGFPEIO.nItemId_fk
JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TGFPEIO.nStoreId_fk
JOIN
tbl_supplier_item_mapping TBLISMTD ON TBLISMTD.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
JOIN
tbl_mst_hsn_codes TBLIHD ON TBLIHD.aHsnAutoId_pk = TBLISMTD.nhsnId_fk
WHERE  TSITE.nIssuedToBranchId_fk = branchId
GROUP BY TSITE.issedStock_Pk ;

END$$
go
DELIMITER ;




USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getIndentItemsInGrn`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getIndentItemsInGrn`(
in mainGrnId int,
in branchId int,
in poTypeId int
)
BEGIN
SELECT 
		TGFPO.aGrnId_pk AS mainGrnId,
        TOPMD.apoid AS mainPoId,
        TOPMD.po_number AS poNumber,
		TGFPO.grnNumber,
        DATE(TGFPO.grnDate) AS grnDate,
        DATE(TOPMD.po_date) AS poDate,
        TOPMD.nUserId_fk AS poUserId,
        TGFPO.nUserId_fk AS grnUserId,
        TUP1.userName AS indentByUser,
		TUP2.userName AS grnByUser,
        TGFPEIO.aGrnId_each_pk AS eachGrnId,
        TGFPEIO.nStoreId_fk AS storeId,
        TMS.storeName AS storeName,
        TGFPEIO.nItemId_fk AS itemId,
        TMID.itemName,
		IF(TMS.storeFlag = 1, TGFPEIO.diapter, TGFPEIO.strength) AS dioOrStr,
        IF(TMS.storeFlag = 1, TGFPEIO.size, TGFPEIO.volume) AS sizOrVol,
        TGFPEIO.batchNo,
        DATE(TGFPEIO.expiryDate) AS expiryDate,
        TGFPEIO.serialNumber,
        TGFPEIO.receivedQty
	FROM tbl_grn_for_po_orders TGFPO 
		JOIN tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPO.aGrnId_pk = TGFPEIO.nGrnId_fk
        JOIN tbl_os_po_main_details TOPMD ON TOPMD.apoid = TGFPEIO.nPoIdCentral_fk
        JOIN tbl_user TUP1 ON TUP1.userId = TOPMD.nUserId_fk
        JOIN tbl_user TUP2 ON TUP2.userId = TGFPO.nUserId_fk
        JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TGFPEIO.nItemId_fk
        JOIN tbl_mst_storecreation TMS ON TMS.aStoreId_pk = TGFPEIO.nStoreId_fk
    WHERE TGFPEIO.nGrnId_fk = mainGrnId
		AND TGFPEIO.ngrnBranchId_fk = branchId
      and find_in_set(TGFPEIO.nPoType_fk,concat(poTypeId,',','5'))
    GROUP BY TGFPEIO.aGrnId_each_pk;
END$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_create_po_maindetails_Emr`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_create_po_maindetails_Emr`(
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 nUserId_fks int,
in po_total_amounts float,
in n_poid_from_branch_fks int,
out autoPOid int,
in gstTypes int,
in poRemarks text,
in requesType int,
in indentToBranchIds int,
in po_fc_amounts float,
in po_discount_amounts float,
in po_final_total_amounts float,
in emrpatientId int,
in patientCode varchar(250),
in patientName varchar(250),
in councilId int,
in surgId int,
in reservedById int,
in reservedByName varchar(250),
in isReserved int,
out indentNumber varchar(250)
)
BEGIN

DECLARE po_num varchar(200);
DECLARE po_bracnh_format varchar(150);

	INSERT INTO tbl_os_po_main_details
		(
		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,
		nUserId_fk,
        n_poid_from_branch_fk,
        gstType,
        poRemarkss,
        typeOfRequest,
        indentToBranchId,
        freight_charge,
        discount_amount,
        final_total_amount,
        patientId,
        counsilId,
        surgAppId,
        patName,
        patientCode,
        reservedById,
        reservedByName,
        isReserved
        )
		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,
		nUserId_fks,
        n_poid_from_branch_fks,
        gstTypes,
        poRemarks,
        requesType,
        indentToBranchIds,
        po_fc_amounts,
        po_discount_amounts ,
        po_final_total_amounts ,
        emrpatientId,
        councilId,
        surgId,
        patientName,
        patientCode,
        reservedById,
        reservedByName,
        isReserved
        );

set autoPOid = last_insert_id();

set po_bracnh_format = (select ifnull(po_raise_format, 'EQ') from tblhospitalbranches where abranchId = po_from_branch_fks);
if(po_type_fks = 1) then
set po_num = concat(ifnull(po_bracnh_format, 'EQ'), '/PO/', DATE_FORMAT(now(), '%d%m%Y'), '/', autoPOid);
else if(po_type_fks = 4) then
set po_num = concat(ifnull(po_bracnh_format, 'EQ'), '/IND/', DATE_FORMAT(now(), '%d%m%Y'), '/', autoPOid);
else if(po_type_fks = 5) then
set po_num = concat(ifnull(po_bracnh_format, 'EQ'), '/DSI/', DATE_FORMAT(now(), '%d%m%Y'), '/', autoPOid);
else 
set po_num = concat(ifnull(po_bracnh_format, 'EQ'), '/CPO/', DATE_FORMAT(now(), '%d%m%Y'), '/', autoPOid);
end if;
end if;
end if;
update tbl_os_po_main_details
set po_number = po_num
where apoid = autoPOid;

set indentNumber = po_num;
END$$
GO
DELIMITER ;



USE `ophthalstock_drsk`;
DROP procedure IF EXISTS `sproc_getBatchBtItemId`;

DELIMITER $$
USE `ophthalstock_drsk`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getBatchBtItemId`(
in branchId int,
in storeId int,
in itemId int,
in potype int 
)
BEGIN

SELECT 
    batchNo
FROM
    tbl_grn_for_po_orders TGFPO
        JOIN
    tbl_grn_for_po_each_item_orders TGFPEIO ON TGFPO.aGrnId_pk = TGFPEIO.nGrnId_fk
WHERE
    TGFPEIO.nItemId_fk = itemId
        AND TGFPEIO.nStoreId_fk = storeId  and find_in_set(TGFPEIO.nPoType_fk,concat(potype,',','5'))
        AND IF(branchId = - 1,
        TRUE,
        TGFPO.ngrnBranchId_fk = branchId)
GROUP BY batchNo;
END$$
go
DELIMITER ;



USE `ophthalstock_drsk`;
DROP procedure IF EXISTS `sproc_getItemsForReturn`;

DELIMITER $$
USE `ophthalstock_drsk`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sproc_getItemsForReturn`(
in branchId int,
in storeId int,
in itemId int,
in batchNo varchar(25),
in poType int,
in minlimitRow int,
in maxlimitRow int
)
BEGIN

declare isMandatoryBatchAndExpiryflag int;

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




SELECT 
		TGFPO.aGrnId_pk AS mainGrnId
        ,TGFPO.nSupplierId_fk AS supplierId
        ,TGFPEIO.aGrnId_each_pk AS eachGrnId
        ,TGFPEIO.nMainTransferId AS mainTransferId
        ,TGFPEIO.nEachTransferId AS eachTransferId
		,TGFPO.grnNumber
        ,TMS.company_name
        ,TGFPEIO.nSupplierItemMapid AS supplierItemMapId
        ,TGFPEIO.serialNumber
        ,DATE(TGFPEIO.expiryDate) AS expiryDate
        ,TGFPEIO.batchNo 
        ,IF(TMSC.storeFlag=1,TGFPEIO.diapter,TGFPEIO.strength) AS dioStr
        ,IF(TMSC.storeFlag=1,TGFPEIO.size,TGFPEIO.volume) AS sizVol
		-- ,UDF_getItemAvailableQtyByItemId(itemId, branchId, -1) AS availQty
        -- ,UDF_getAvailableQtyInGrn(TGFPEIO.aGrnId_each_pk) AS availQty
        ,TGA.availQty  AS availQty
        ,TGA.availQty  AS retQty
        ,THB1.abranchId AS grnBranchId
        ,THB1.branchHospName AS grnBranch
        ,THB2.abranchId AS receivedFromBranchId
        ,THB2.branchHospName AS receivedFromBranch
        ,THB2.nbranchcode AS receivedFromBranchCode
        --
        ,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) AS taxAmt,
        (IFNULL(TSIM.purchasePrice,0) + (IFNULL(TMHC.gstPercentage,0)/100.0) * IFNULL(TSIM.purchasePrice,0)) AS itemPrice
       ,TMID.itemName as itemName
      
      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
        
        -- JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TGFPO.nSupplierId_fk
        LEFT JOIN tbl_stocks_issued_to_emr TSITE ON TSITE.issedStock_Pk = TGFPEIO.nEachTransferId
        JOIN tblhospitalbranches THB1 ON THB1.abranchId = TGFPEIO.ngrnBranchId_fk
        LEFT JOIN tblhospitalbranches THB2 ON THB2.abranchId = TSITE.nIssuedFromBranchId_fk
        -- 
        JOIN tbl_mst_items_details TMID ON TMID.aItemId_pk = TGFPEIO.nItemId_fk

		JOIN tbl_mst_storecreation TMSC ON TMSC.aStoreId_pk = TGFPEIO.nStoreId_fk

        -- JOIN tbl_supplier_item_mapping TSIM ON TSIM.supplier_item_mappingId = TGFPEIO	.nSupplierItemMapid
		JOIN tbl_supplier_item_mapping TSIM ON 
         -- CONSIGNMENT STOCK ACCEPTANCE RETURN WITH SUPPLIER MAP ID = NULL
			/*IF(TGFPEIO.nPoType_fk = 0
				,TRUE
				,TSIM.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid)*/
		TSIM.supplier_item_mappingId = TGFPEIO.nSupplierItemMapid
		JOIN tbl_mst_supplier TMS ON TMS.aSupplierId_pk = TSIM.nSupplierId_fk -- TGFPO.nSupplierId_fk
        		JOIN tbl_mst_hsn_codes TMHC ON TMHC.aHsnAutoId_pk = TSIM.nhsnId_fk
	WHERE TGFPEIO.nItemId_fk = itemId
			AND TGFPEIO.ngrnBranchId_fk = branchId

            AND if(isMandatoryBatchAndExpiryflag = 1,TGFPEIO.batchNo = batchNo,true) 
             and find_in_set(TGFPEIO.nPoType_fk,concat(poType,',','5'))
               
              AND TGFPEIO.nStoreId_fk = storeId and  TGA.availQty > 0
          --  AND (TGFPEIO.nPoType_fk = poType OR TGFPEIO.nPoType_fk = 0) -- CONSIGNMENT STOCK ACCEPTANCE RETURN WITH PO TYPE ID = 0
          --  AND TSITE.nIssuedFromBranchId_fk = 0;
          GROUP BY TGFPEIO.aGrnId_each_pk
	ORDER BY receivedFromBranchId, eachGrnId DESC
   LIMIT minlimitRow , maxlimitRow ;
END$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getStocIssueItemDetailsWrtItemName_Autosugest`;

DELIMITER $$
USE `ophthalstock`$$
CREATE 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) ASC, 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) ASC 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$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getstocIssueItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE 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) ASC, 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) ASC
        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$$
go
DELIMITER ;




USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getstocIssueEachItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE 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) ASC, 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) ASC
    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$$
go
DELIMITER ;








USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtItemName_Autosugest`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtItemName_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) ASC, 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
       

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
    
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
       
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
  
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) ASC 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
       
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
   
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$$
go
DELIMITER ;




USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode`(
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) ASC, 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
       
    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
    
    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
       
    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
   
    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) ASC
        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
       
    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
     
    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$$
go
DELIMITER ;



USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getEachItemDetailsWrtCode`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getEachItemDetailsWrtCode`(
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,
    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) ASC, 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,
   
    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,

    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) ASC
    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,
    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$$
go
DELIMITER ;




-- 01-01-24

INSERT INTO `ophthalstock_drsk`.`tbl_modules` (`aModuleId`, `priority`, `moduleName`, `isHavingSubModule`, `isMobileOrWeb`, `moduleId`, `fnOnClick`, `moduleIcon`, `isActive`, `isDeleted`, `nUserId`, `nHospitalId`, `nBranchId`) VALUES ('61', '15.8', 'Direct Stock Issue Report', '2', '1', '44', 'directStockIssueReport', 'fa fa-angle-right', '1', '0', '1', '1', '1');




USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getStocIssueListToBranchesDetails`;

DELIMITER $$
USE `ophthalstock`$$
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$$
go
DELIMITER ;




-- 02-01-24 

USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_updatAcceptReturnIsReservedStatus`;

DELIMITER $$
USE `ophthalstock`$$
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 ;





-- 02-01-24  -- 2


USE `ophthalstock`;
DROP procedure IF EXISTS `sproc_getItemDetailsWrtCode_Autosugest`;

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_getItemDetailsWrtCode_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


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,
        TGFPEIO.diapter,
        TGFPEIO.size,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber  
    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
    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.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC, 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,
        TGFPEIO.diapter,
        TGFPEIO.size,
    batchNo,
    DATE(expiryDate) AS expiryDate,
    serialNumber
    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
    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.strength,TGFPEIO.volume, TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC limit 10;
end if;

END$$
go
DELIMITER ;

