
-- 18-03-24

INSERT INTO `ophthalstock`.`tbl_modules` (`priority`, `moduleName`, `isHavingSubModule`, `isMobileOrWeb`, `moduleId`, `fnOnClick`, `moduleIcon`, `isActive`, `isDeleted`, `nUserId`, `nHospitalId`, `nBranchId`) VALUES ('15.9', 'Puchase GST Reports', '2', '1', '44', 'purchaseGstReports', 'fa fa-angle-right', '1', '0', '1', '1', '1');



INSERT INTO `ophthalstock`.`tblhospotalwiseconfigurations` (`configKey`, `configValue`, `isActive`) VALUES ('PlacepfsupplyinGSTReport', 'Mumbai', '1');






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

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_downloadPurchaseGstReport`(
in startDate date,
in endDate date,
in branchId varchar(250)
)
BEGIN

declare placeOfSupply varchar(1000);

set  placeOfSupply = (SELECT configValue FROM tblhospotalwiseconfigurations where configKey = 'PlacepfsupplyinGSTReport');

SELECT 
    TBLPMD.grnNumber AS grnNumber,
    TBLGSD.company_name AS supplierName, 
    TBLGSD.GSTIN AS supplierGstnNo,
    placeOfSupply AS state,
    ifnull(TBLPMD.invoiceNumber,'') AS invoiceNumber,
    DATE(TBLPMD.invoiceDate) AS invoiceDate,
    SUM(TBLGED.taxableAmount) AS netvalue,
    SUM(TBLGED.taxAmount / 2) AS sgstValue,
    SUM(TBLGED.taxAmount / 2) AS cgstValue,
    SUM(TBLGED.taxAmount) AS taxValue,
    SUM(TBLGED.totalAmount) AS totalAmount
FROM
    tbl_grn_for_po_orders TBLPMD
        JOIN
    tbl_grn_for_po_each_item_orders TBLGED ON TBLGED.nGrnId_fk = TBLPMD.aGrnId_pk
        JOIN
    tbl_mst_supplier TBLGSD ON TBLGSD.aSupplierId_pk = TBLPMD.nSupplierId_fk
WHERE
    DATE(TBLPMD.grnDate) BETWEEN startDate AND endDate
        AND IF(branchId = '-1',
        TRUE,
        TBLPMD.ngrnBranchId_fk = branchId) AND TBLPMD.nPoType_fk = 1
GROUP BY TBLGED.nGrnId_fk;

END$$

DELIMITER ;






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

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_downloadPurchaseReport`(
in startDate date,
in endDate date,
in branchId varchar(250)
)
BEGIN


SELECT 
    TBLPMD.grnNumber AS grnNumber,
    TBLGSD.company_name AS supplierName,
    IFNULL(TBLPMD.invoiceNumber, '') AS invoiceNumber,
    DATE(TBLPMD.invoiceDate) AS invoiceDate,
    SUM(TBLGED.totalAmount) AS totalAmount,
    IFNULL(TBLPRUD.fullName, '') AS enteredBy, 
    IFNULL(TBLPCUD.fullName, '') AS confirmedBy,
    DATE(TBLPMD.grnDate) AS entryDate,
    IFNULL(TBLGSD.credit_period, 0) AS creditPerion
FROM
    tbl_grn_for_po_orders TBLPMD
        JOIN
    tbl_grn_for_po_each_item_orders TBLGED ON TBLGED.nGrnId_fk = TBLPMD.aGrnId_pk
        JOIN
    tbl_mst_supplier TBLGSD ON TBLGSD.aSupplierId_pk = TBLPMD.nSupplierId_fk
        JOIN
    tbl_os_po_main_details TBLPOMD ON TBLPOMD.apoid = TBLPMD.nPoIdCentral_fk
        LEFT JOIN
    tbl_user TBLPRUD ON TBLPMD.nUserId_fk = TBLPRUD.userId
        LEFT JOIN
    tbl_user TBLPCUD ON TBLPOMD.po_authorized_by_fk = TBLPCUD.userId
WHERE
    DATE(TBLPMD.grnDate) BETWEEN startDate AND endDate
        AND IF(branchId = '-1',
        TRUE,
        TBLPMD.ngrnBranchId_fk = branchId) AND TBLPMD.nPoType_fk = 1
GROUP BY TBLGED.nGrnId_fk;
END$$
go
DELIMITER ;








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

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_downloadPurchaseReturnReport`(
in startDate date,
in endDate date,
in branchId varchar(250)
)
BEGIN


SELECT 
    TBLGSD.company_name AS supplierName,
    TBLGSD.GSTIN AS supplierGstnNo,
    IFNULL(TBLGD.invoiceNumber, '') AS invoiceNumber,
    TBLRTR.returnTypeName AS returnCase,
    TBLRID.itemName as itemName , 
    TBLHD.hsnCode AS hsnCode,
    TBLRED.returnQty AS returnQty,
    TBLGED.batchNo AS batchNo,
    TBLRISMD.purchasePrice AS unitPrice,
    (TBLRED.returnQty * TBLRISMD.purchasePrice) AS taxableValue,
    TBLHD.gstPercentage AS gstScheme,
    TBLHD.sgstPercentage AS sgstPer,
    (TBLRED.returnQty * TBLRISMD.purchasePrice * TBLHD.sgstPercentage / 100) AS sgstAmount,
    TBLHD.cgstPercentage AS cgstPer,
    (TBLRED.returnQty * TBLRISMD.purchasePrice * TBLHD.cgstPercentage / 100) AS cgstAmount,
    DATE(TBLRMD.nReturnedDate) AS returnDate
FROM
    tbl_grn_return_main_details TBLRMD
        JOIN
    tbl_grn_return_each_details TBLRED ON TBLRED.nReturnMainId = TBLRMD.aReturnMainId
        JOIN
    tbl_mst_items_details TBLRID ON TBLRID.aItemId_pk = TBLRED.itemId
        JOIN
    tbl_mst_supplier TBLGSD ON TBLGSD.aSupplierId_pk = TBLRED.supplierId
        JOIN
    tbl_supplier_item_mapping TBLRISMD ON TBLRISMD.supplier_item_mappingId = TBLRED.nSupplierMapId
        JOIN
    tbl_mst_hsn_codes TBLHD ON TBLHD.aHsnAutoId_pk = TBLRISMD.nhsnId_fk
        JOIN
    tbl_grn_for_po_orders TBLGD ON TBLGD.aGrnId_pk = TBLRED.nMainGrnId
        JOIN
    tbl_grn_for_po_each_item_orders TBLGED ON TBLGED.aGrnId_each_pk = TBLRED.nEachGrnId
        JOIN
    tbl_mst_returntypes TBLRTR ON TBLRTR.aReturnTypeId = TBLRED.nReturnType
WHERE
    DATE(TBLRMD.nReturnedDate) BETWEEN startDate AND endDate
        AND IF(branchId = '-1',
        TRUE,
        TBLRMD.nBranchId = branchId) 
        
        order by  DATE(TBLRMD.nReturnedDate)
;
END$$
go
DELIMITER ;




-- 19-03-24





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

DELIMITER $$
USE `ophthalstock`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sproc_downloadPurchaseGstReport`(
in startDate date,
in endDate date,
in branchId varchar(250)
)
BEGIN

declare placeOfSupply varchar(1000);

set  placeOfSupply = (SELECT configValue FROM tblhospotalwiseconfigurations where configKey = 'PlacepfsupplyinGSTReport');

SELECT 
    TBLPMD.grnNumber AS grnNumber,
    TBLGSD.company_name AS supplierName,
    TBLGSD.GSTIN AS supplierGstnNo,
    placeOfSupply AS state,
    ifnull(TBLPMD.invoiceNumber,'') AS invoiceNumber,
    DATE(TBLPMD.invoiceDate) AS invoiceDate,
    SUM(TBLGED.taxableAmount) AS netvalue,
    SUM(TBLGED.taxAmount / 2) AS sgstValue,
    SUM(TBLGED.taxAmount / 2) AS cgstValue,
    SUM(TBLGED.taxAmount) AS taxValue,
    SUM(TBLGED.totalAmount) AS totalAmount,
    SUM(ifnull(TBLPMD.frightCharges,0)) as otherAmount
FROM
    tbl_grn_for_po_orders TBLPMD
        JOIN 
    tbl_grn_for_po_each_item_orders TBLGED ON TBLGED.nGrnId_fk = TBLPMD.aGrnId_pk
        JOIN
    tbl_mst_supplier TBLGSD ON TBLGSD.aSupplierId_pk = TBLPMD.nSupplierId_fk
WHERE
    DATE(TBLPMD.grnDate) BETWEEN startDate AND endDate
        AND IF(branchId = '-1',
        TRUE,
        TBLPMD.ngrnBranchId_fk = branchId)
GROUP BY TBLGED.nGrnId_fk;

END$$
go
DELIMITER ;







-- 02-05-24



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),
in in_searchType int,
in in_serialNumber varchar(250),
in in_batchNo varchar(250),
in in_powerValue varchar(250)
)
BEGIN


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 if(in_searchType = '1',TGFPEIO.serialNumber = in_serialNumber,
        
     if(in_searchType = '3', TGFPEIO.diapter like concat(in_powerValue,'%'),
     
      if(in_searchType = '4', TGFPEIO.batchNo = in_batchNo,true ) ) )
    
    AND TMID.isActive = 1
    AND TGA.nItemId = itemShortCode
    AND TGA.nBranchId = branchId 
    
    order by  DATE(TGFPEIO.expiryDate) ASC ,TGA.aGrnAvailQtyId ASC
    ;
  --  GROUP BY TGA.nItemId , TGFPEIO.batchNo,TGFPEIO.strength,TGFPEIO.volume , DATE(TGFPEIO.expiryDate) ASC, TGFPEIO.serialNumber; -- 



/*
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.batchNo,TGFPEIO.strength,TGFPEIO.volume , 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.batchNo,TGFPEIO.strength,TGFPEIO.volume , 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),
in in_searchType int,
in in_serialNumber varchar(250),
in in_batchNo varchar(250),
in in_powerValue varchar(250)
)
BEGIN

SELECT 
        TGFPEIO.aGrnId_each_pk AS grnEachId,
        TGA.aGrnAvailQtyId AS availId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName,
       IFNULL((TGA.availQty),0)  AS availQty,
        DATE(TGFPEIO.expiryDate) AS expiryDate,
        TGFPEIO.batchNo AS batchNo,
        TGFPEIO.strength AS strength,
        TGFPEIO.volume AS volume,
        TGFPEIO.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
        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 if(in_searchType = '1',TGFPEIO.serialNumber = in_serialNumber,
        
     if(in_searchType = '3', TGFPEIO.diapter like concat(in_powerValue,'%'),
     
      if(in_searchType = '4', TGFPEIO.batchNo = in_batchNo,true ) ) )
    AND TMID.isActive = 1
    AND TGA.nItemId = itemShortCode
    AND TGA.nBranchId = branchId   
     
 --    GROUP BY  TGA.aGrnAvailQtyId,TMID.itemShortCode , TGFPEIO.batchNo ,TGFPEIO.strength,TGFPEIO.volume , DATE(TGFPEIO.expiryDate) ASC, TGFPEIO.serialNumber -- 
    ORDER BY DATE(TGFPEIO.expiryDate) ASC ,TGA.aGrnAvailQtyId ASC ;




/*
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.batchNo ,TGFPEIO.strength,TGFPEIO.volume , 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 ;



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,
in in_searchTypeId int,
in in_searchItemId int
)
BEGIN


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(in_searchItemId <> '-1',TGA.nItemId = in_searchItemId ,true)
    AND TMID.isActive = 1
    and DATE(TGFPEIO.expiryDate) > DATE(NOW())
    AND if(in_searchTypeId = '1',TGFPEIO.serialNumber like concat(itemShortCode,'%'),
    
    if(in_searchTypeId = '2', TMID.itemName like concat(itemShortCode,'%'), 
    
     if(in_searchTypeId = '3', TGFPEIO.diapter like concat(itemShortCode,'%'),
     
      if(in_searchTypeId = '4', TGFPEIO.batchNo like concat(itemShortCode,'%'),true ) ) )
    )
    AND TGA.nBranchId = branchId ; 
    
    
 
/*
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 ;





-- 03-05-24

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,
in in_searchTypeId int,
in in_searchItemId int
)
BEGIN


if(in_storeId <> 1 && in_searchTypeId = '4')
then



SELECT 
    TGA.aGrnAvailQtyId AS grnAvailableId,
    TMID.aItemId_pk AS itemId,
    TMID.nStoreId_fk AS storeId,
    TMID.itemShortCode AS itemShortCode,
    TMID.itemName AS itemName,
    IFNULL(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(in_searchItemId <> '-1',
        TGA.nItemId = in_searchItemId,
        TRUE)
        AND TMID.isActive = 1
        AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
        AND IF(in_searchTypeId = '1',
        TGFPEIO.serialNumber LIKE CONCAT(itemShortCode, '%'),
        IF(in_searchTypeId = '2',
            TMID.itemName LIKE CONCAT(itemShortCode, '%'),
            IF(in_searchTypeId = '3',
                TGFPEIO.diapter LIKE CONCAT(itemShortCode, '%'),
                IF(in_searchTypeId = '4',
                    TGFPEIO.batchNo LIKE CONCAT(itemShortCode, '%'),
                    TRUE))))
        AND TGA.nBranchId = branchId  
 
 group by TGA.nItemId , TGFPEIO.batchNo;
	
    
   else
   

SELECT 
    TGA.aGrnAvailQtyId AS grnAvailableId,
    TMID.aItemId_pk AS itemId,
    TMID.nStoreId_fk AS storeId,
    TMID.itemShortCode AS itemShortCode,
    TMID.itemName AS itemName,
    IFNULL(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(in_searchItemId <> '-1',
        TGA.nItemId = in_searchItemId,
        TRUE)
        AND TMID.isActive = 1
        AND DATE(TGFPEIO.expiryDate) > DATE(NOW())
        AND IF(in_searchTypeId = '1',
        TGFPEIO.serialNumber LIKE CONCAT(itemShortCode, '%'),
        IF(in_searchTypeId = '2',
            TMID.itemName LIKE CONCAT(itemShortCode, '%'),
            IF(in_searchTypeId = '3',
                TGFPEIO.diapter LIKE CONCAT(itemShortCode, '%'),
                IF(in_searchTypeId = '4',
                    TGFPEIO.batchNo LIKE CONCAT(itemShortCode, '%'),
                    TRUE))))
        AND TGA.nBranchId = branchId  
 
 group by if(in_searchTypeId = '1',TGFPEIO.serialNumber ,
    
    if(in_searchTypeId = '2', TMID.itemName ,  
    
     if(in_searchTypeId = '3', TGFPEIO.diapter , 
     
      if(in_searchTypeId = '4', TGFPEIO.batchNo ,true ) ) )
    ) ;
	
    
  end if;  
	
-- GROUP BY TGA.nItemId , TGFPEIO.batchNo , DATE(TGFPEIO.expiryDate) ASC, TGFPEIO.serialNumber limit 10;


/*



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

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,
     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),
in in_searchType int,
in in_serialNumber varchar(250),
in in_batchNo varchar(250),
in in_powerValue varchar(250)
)
BEGIN


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 DATE(TGFPEIO.expiryDate) > DATE(NOW()) 
  --  AND TMID.nStoreId_fk = 2 
  AND if(in_searchType = '1',TGFPEIO.serialNumber = in_serialNumber,
        
     if(in_searchType = '3', TGFPEIO.diapter like concat(in_powerValue,'%'),
     
      if(in_searchType = '4', TGFPEIO.batchNo = in_batchNo,true ) ) )
    
    AND TMID.isActive = 1
    AND TGA.nItemId = itemShortCode
    AND TGA.nBranchId = branchId and TGA.isReserved=0
 order by  DATE(TGFPEIO.expiryDate) ASC ,TGA.aGrnAvailQtyId ASC
    ;
        

/*

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

    
    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,
    
    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),
in in_searchType int,
in in_serialNumber varchar(250),
in in_batchNo varchar(250),
in in_powerValue varchar(250)
)
BEGIN



 

SELECT 
        TGFPEIO.aGrnId_each_pk AS grnEachId,
        TGA.aGrnAvailQtyId AS availId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName,
        IFNULL((TGA.availQty),0) AS availQty,
        DATE(TGFPEIO.expiryDate) AS expiryDate,
        TGFPEIO.batchNo AS batchNo,
        TGFPEIO.strength AS strength,
        TGFPEIO.volume AS volume,
        TGFPEIO.diapter,
        TGFPEIO.size,
        TGFPEIO.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
    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 if(in_searchType = '1',TGFPEIO.serialNumber = in_serialNumber,
        
     if(in_searchType = '3', TGFPEIO.diapter like concat(in_powerValue,'%'),
     
      if(in_searchType = '4', TGFPEIO.batchNo = in_batchNo,true ) ) )
    -- AND TMID.nStoreId_fk = 2
    AND TMID.isActive = 1
    AND TGA.nItemId = itemShortCode
    AND TGA.nBranchId = branchId and TGA.isReserved=0

    ORDER BY DATE(TGFPEIO.expiryDate) ASC ,TGA.aGrnAvailQtyId ASC ;
    





/*
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
  
    
    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,
   
    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.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

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

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,
in in_searchTypeId int,
in in_searchItemId int
)
BEGIN

if(in_storeId <> 1 && in_searchTypeId = '4')
then
SELECT  
    TGA.aGrnAvailQtyId AS grnAvailableId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName, 
        IFNULL(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(in_searchItemId <> '-1',TGA.nItemId = in_searchItemId ,true)
    AND TMID.isActive = 1
    and DATE(TGFPEIO.expiryDate) > DATE(NOW())
    AND if(in_searchTypeId = '1',TGFPEIO.serialNumber like concat(itemShortCode,'%'),
    
    if(in_searchTypeId = '2', TMID.itemName like concat(itemShortCode,'%'), 
    
     if(in_searchTypeId = '3', TGFPEIO.diapter like concat(itemShortCode,'%'),
     
      if(in_searchTypeId = '4', TGFPEIO.batchNo like concat(itemShortCode,'%'),true ) ) )
    )
    AND TGA.nBranchId = branchId
    
    group by TGA.nItemId , TGFPEIO.batchNo ;
    
    
    
    else
    
    SELECT  
    TGA.aGrnAvailQtyId AS grnAvailableId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName, 
        IFNULL(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(in_searchItemId <> '-1',TGA.nItemId = in_searchItemId ,true)
    AND TMID.isActive = 1
    and DATE(TGFPEIO.expiryDate) > DATE(NOW())
    AND if(in_searchTypeId = '1',TGFPEIO.serialNumber like concat(itemShortCode,'%'),
    
    if(in_searchTypeId = '2', TMID.itemName like concat(itemShortCode,'%'), 
    
     if(in_searchTypeId = '3', TGFPEIO.diapter like concat(itemShortCode,'%'),
     
      if(in_searchTypeId = '4', TGFPEIO.batchNo like concat(itemShortCode,'%'),true ) ) )
    )
    AND TGA.nBranchId = branchId
    
    group by if(in_searchTypeId = '1',TGFPEIO.serialNumber ,
    
    if(in_searchTypeId = '2', TMID.itemName , 
    
     if(in_searchTypeId = '3', TGFPEIO.diapter ,
     
      if(in_searchTypeId = '4', TGFPEIO.batchNo ,true ) ) )
    ) ;
    
    

end if;

 
/*
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 ;



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),
in in_searchType int,
in in_serialNumber varchar(250),
in in_batchNo varchar(250),
in in_powerValue varchar(250)
)
BEGIN


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 if(in_searchType = '1',TGFPEIO.serialNumber = in_serialNumber,
        
     if(in_searchType = '3', TGFPEIO.diapter like concat(in_powerValue,'%'),
     
      if(in_searchType = '4', TGFPEIO.batchNo = in_batchNo,true ) ) )
    
    AND TMID.isActive = 1
    AND TGA.nItemId = itemShortCode
    AND TGA.nBranchId = branchId 
    
    order by  DATE(TGFPEIO.expiryDate) ASC ,TGA.aGrnAvailQtyId ASC
    ;
  --  GROUP BY TGA.nItemId , TGFPEIO.batchNo,TGFPEIO.strength,TGFPEIO.volume , DATE(TGFPEIO.expiryDate) ASC, TGFPEIO.serialNumber; -- 



/*
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.batchNo,TGFPEIO.strength,TGFPEIO.volume , 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.batchNo,TGFPEIO.strength,TGFPEIO.volume , 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),
in in_searchType int,
in in_serialNumber varchar(250),
in in_batchNo varchar(250),
in in_powerValue varchar(250)
)
BEGIN

SELECT 
        TGFPEIO.aGrnId_each_pk AS grnEachId, 
        TGA.aGrnAvailQtyId AS availId,
        TMID.aItemId_pk AS itemId, 
        TMID.nStoreId_fk AS storeId,
        TMID.itemShortCode AS itemShortCode,
        TMID.itemName AS itemName,
       IFNULL((TGA.availQty),0)  AS availQty,
        DATE(TGFPEIO.expiryDate) AS expiryDate,
        TGFPEIO.batchNo AS batchNo,
        TGFPEIO.strength AS strength,
        TGFPEIO.volume AS volume,
        TGFPEIO.serialNumber AS serialNo,
        TGA.nSupplierMapId AS supplierMapId,
        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 if(in_searchType = '1',TGFPEIO.serialNumber = in_serialNumber,
        
     if(in_searchType = '3', TGFPEIO.diapter like concat(in_powerValue,'%'),
     
      if(in_searchType = '4', TGFPEIO.batchNo = in_batchNo,true ) ) )
    AND TMID.isActive = 1
    AND TGA.nItemId = itemShortCode
    AND TGA.nBranchId = branchId   
     
 --    GROUP BY  TGA.aGrnAvailQtyId,TMID.itemShortCode , TGFPEIO.batchNo ,TGFPEIO.strength,TGFPEIO.volume , DATE(TGFPEIO.expiryDate) ASC, TGFPEIO.serialNumber -- 
    ORDER BY DATE(TGFPEIO.expiryDate) ASC ,TGA.aGrnAvailQtyId ASC ;




/*
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.batchNo ,TGFPEIO.strength,TGFPEIO.volume , 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 ;

