package com.reallinkbilling.update;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

import org.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.SessionAware;

import com.opensymphony.xwork2.ActionSupport;
import com.reallinkbilling.db.GetDBConnection;

public class UpdateProductStockDetails extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 3927832833390378702L;

	private SessionMap<String,Object> sessionMap;  
	
	
	
	public static boolean addStockToCurrentStock(Connection connection, SessionMap<String, Object> sessionMap, String productId, String productName, Float productQuantity)
	{
		
		boolean isStockAdded = false;
		
		
		
		PreparedStatement updateStockPSMT= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		String db = (String) sessionMap.get("db");
		
		
		try
		{
	
	
			String shopId = (String) sessionMap.get("shopId");
			String shopCode = (String) sessionMap.get("shopCode");
		
	
		float oldQuantity = 0.0F;
		float newQuantity = 0.0F;
		float stockConversionRatio = 0.0F;
	
		sql = "SELECT "
				+ "in_stock_quantity,"
				+ "stock_conversion_ratio "
				+ "FROM "
				+ "shop_product_master "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "ANd "
				+ "shop_code = ? ";
		
		updateStockPSMT = connection.prepareStatement(sql);
		
		updateStockPSMT.setString(1, productId);
		updateStockPSMT.setString(2, shopId);
		updateStockPSMT.setString(3, shopCode);
		
	//	System.out.println(updateStockPSMT.toString());
		
		resultSet = updateStockPSMT.executeQuery();
		
		if(resultSet.next())
		{
			oldQuantity = resultSet.getFloat(1);
			
		}
		
		newQuantity = oldQuantity + productQuantity ; 
		
	/*	System.out.println("oldQuantity : "+oldQuantity);
		System.out.println("productQuantity : "+productQuantity);
		System.out.println("newQuantity : "+ newQuantity);*/
		
		
		
		if(resultSet != null)
		{
			resultSet.close();
			resultSet = null;
		}
		
		if(updateStockPSMT != null)
		{
			updateStockPSMT.close();
			updateStockPSMT = null;
		}
		sql = "UPDATE "
				+ "shop_product_master "
				+ "SET "
				+ "in_stock_quantity = ? "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ? ";
				
		
		updateStockPSMT = connection.prepareStatement(sql);
		
		
		updateStockPSMT.setFloat(1, newQuantity);
		updateStockPSMT.setString(2, productId);
		updateStockPSMT.setString(3, shopId);
		updateStockPSMT.setString(4, shopCode);

		int rowCount = updateStockPSMT.executeUpdate();
		
		if(rowCount>0)
		{			
			isStockAdded = true;
		}
		
		
		}catch (SQLException e) {
	 		//e.printStackTrace();
			isStockAdded = false;
	 		
	 	}
		catch (Exception e) {
			//e.printStackTrace();
			isStockAdded = false;
		}
		finally
		{
			
			if(resultSet != null)
			{
				try {
					resultSet.close();
					resultSet = null;
				} catch (SQLException e) {
				
				}
			}
			if(updateStockPSMT != null)
			{
				try {
					updateStockPSMT.close();
					updateStockPSMT = null;
				} catch (SQLException e) {
					
				}
			}
		
			
		}
		//System.out.println(" in update isStockAdded : "+isStockAdded);
		
	
		return isStockAdded;
		
	}
	
	

	public static boolean addStockPurchaseDetail(Connection connection, SessionMap<String, Object> sessionMap, String productId, String productName, Float productQuantity, String serialBatchId)
	{
		
		boolean isStockAdded = false;
		
		
		
		PreparedStatement updateStockPSMT= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		String db = (String) sessionMap.get("db");
		
		
		try
		{
	
	
			String shopId = (String) sessionMap.get("shopId");
			String shopCode = (String) sessionMap.get("shopCode");
		
	
		float oldQuantity = 0.0F;
		float newQuantity = 0.0F;
		
		sql = "SELECT "
				+ "puroduct_quantity "
				+ "FROM "
				+ "shop_purchase_product_info_master "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "serial_no_batch_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "ANd "
				+ "shop_code = ? ";
		
		updateStockPSMT = connection.prepareStatement(sql);
		
		updateStockPSMT.setString(1, productId);
		updateStockPSMT.setString(2, serialBatchId);
		updateStockPSMT.setString(3, shopId);
		updateStockPSMT.setString(4, shopCode);
		
	//	System.out.println(updateStockPSMT.toString());
		
		resultSet = updateStockPSMT.executeQuery();
		
		if(resultSet.next())
		{
			oldQuantity = resultSet.getFloat(1);
			
		}
		
		newQuantity = oldQuantity + productQuantity ; 
		
	/*	System.out.println("oldQuantity : "+oldQuantity);
		System.out.println("productQuantity : "+productQuantity);
		System.out.println("newQuantity : "+ newQuantity);*/
		
		
		
		if(resultSet != null)
		{
			resultSet.close();
			resultSet = null;
		}
		
		if(updateStockPSMT != null)
		{
			updateStockPSMT.close();
			updateStockPSMT = null;
		}
		sql = "UPDATE "
				+ "shop_purchase_product_info_master "
				+ "SET "
				+ "puroduct_quantity = ? "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "serial_no_batch_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ? ";
				
		
		updateStockPSMT = connection.prepareStatement(sql);
		
		
		updateStockPSMT.setFloat(1, newQuantity);
		updateStockPSMT.setString(2, productId);
		updateStockPSMT.setString(3, serialBatchId);
		updateStockPSMT.setString(4, shopId);
		updateStockPSMT.setString(5, shopCode);

		int rowCount = updateStockPSMT.executeUpdate();
		
		if(rowCount>0)
		{			
			isStockAdded = true;
		}
		
		
		}catch (SQLException e) {
	 		//e.printStackTrace();
			isStockAdded = false;
	 		
	 	}
		catch (Exception e) {
			//e.printStackTrace();
			isStockAdded = false;
		}
		finally
		{
			
			if(resultSet != null)
			{
				try {
					resultSet.close();
					resultSet = null;
				} catch (SQLException e) {
				
				}
			}
			if(updateStockPSMT != null)
			{
				try {
					updateStockPSMT.close();
					updateStockPSMT = null;
				} catch (SQLException e) {
					
				}
			}
		
			
		}
		//System.out.println(" in update isStockAdded : "+isStockAdded);
		
	
		return isStockAdded;
		
	}
	
	
	

public static boolean minusStockFromAssignedGodown(
        Connection connection,
        SessionMap<String, Object> sessionMap,
        String productId,
        Float quantity) {

    if (connection == null || sessionMap == null ||
            productId == null || quantity == null ||
            quantity <= 0) {
        return false;
    }

    String shopId = (String) sessionMap.get("shopId");
    String shopCode = (String) sessionMap.get("shopCode");
    String godownCode =
            (String) sessionMap.get("assignedGodownCode");
    
   

    	// ADD THE PRINT STATEMENTS HERE
    	System.out.println("Assigned Godown Code = " + godownCode);
    	System.out.println("Product ID = " + productId);
    	System.out.println("Quantity to deduct = " + quantity);

    if (shopId == null || shopCode == null ||
            godownCode == null || godownCode.trim().isEmpty()) {
        return false;
    }

    String sql =
        "SELECT w.entry_id, w.in_stock_quantity " +
        "FROM warehouse_stock_master w " +
        "JOIN shop_godown_master g " +
        "ON g.godown_id = w.warehouse_id " +
        "AND g.shop_id = w.shop_id " +
        "AND g.shop_code = w.shop_code " +
        "WHERE w.product_id = ? " +
        "AND w.shop_id = ? " +
        "AND w.shop_code = ? " +
        "AND g.godown_code = ? " +
        "AND g.shop_id = ? " +
        "AND g.shop_code = ? " +
        "FOR UPDATE";

    try (PreparedStatement ps = connection.prepareStatement(sql)) {
        ps.setString(1, productId);
        ps.setString(2, shopId);
        ps.setString(3, shopCode);
        ps.setString(4, godownCode);
        ps.setString(5, shopId);
        ps.setString(6, shopCode);

//        try (ResultSet rs = ps.executeQuery()) {
//            if (!rs.next()) {
//                return false;
//            }
//
//            int entryId = rs.getInt("entry_id");
//            float available = rs.getFloat("in_stock_quantity");
//
//            // Do not guess which row to update if duplicates exist.
//            if (rs.next() || available < quantity) {
//                return false;
//            }
        
        
        try (ResultSet rs = ps.executeQuery()) {

            if (!rs.next()) {
                System.out.println(
                    "ERROR: No warehouse stock row found for Product ID = "
                    + productId
                    + ", Shop ID = " + shopId
                    + ", Shop Code = " + shopCode
                    + ", Godown Code = " + godownCode
                );
                return false;
            }

            int entryId = rs.getInt("entry_id");
            float available = rs.getFloat("in_stock_quantity");

            System.out.println("Warehouse Stock Entry ID = " + entryId);
            System.out.println("Available Godown Stock = " + available);
            System.out.println("Required Quantity = " + quantity);

            if (rs.next()) {
                System.out.println(
                    "ERROR: Multiple warehouse stock rows match this product and Godown."
                );
                return false;
            }

            if (available < quantity) {
                System.out.println(
                    "ERROR: Insufficient Godown stock. Available = "
                    + available + ", Required = " + quantity
                );
                return false;
            }

            String updateSql =
                "UPDATE warehouse_stock_master " +
                "SET in_stock_quantity = in_stock_quantity - ? " +
                "WHERE entry_id = ? " +
                "AND in_stock_quantity >= ?";

            try (PreparedStatement update =
                    connection.prepareStatement(updateSql)) {
                update.setFloat(1, quantity);
                update.setInt(2, entryId);
                update.setFloat(3, quantity);

                return update.executeUpdate() == 1;
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
}

	
	
	

	public static boolean addStockToCurrentStock(SessionMap<String, Object> sessionMap, String productId, String productName, Float productQuantity)
	{
		
		boolean isStockAdded = false;
		
		
		Connection connection = null;
		PreparedStatement updateStockPSMT= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		String db = (String) sessionMap.get("db");
		
		
		try
		{
			connection = GetDBConnection.getDBConnection(db);
	
			String shopId = (String) sessionMap.get("shopId");
			String shopCode = (String) sessionMap.get("shopCode");
		
	
		float oldQuantity = 0.0F;
		float newQuantity = 0.0F;
		float stockConversionRatio = 0.0F;
	
		sql = "SELECT "
				+ "in_stock_quantity,"
				+ "stock_conversion_ratio "
				+ "FROM "
				+ "shop_product_master "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "ANd "
				+ "shop_code = ? ";
		
		updateStockPSMT = connection.prepareStatement(sql);
		
		updateStockPSMT.setString(1, productId);
		updateStockPSMT.setString(2, shopId);
		updateStockPSMT.setString(3, shopCode);
		
	//	System.out.println(updateStockPSMT.toString());
		
		resultSet = updateStockPSMT.executeQuery();
		
		if(resultSet.next())
		{
			oldQuantity = resultSet.getFloat(1);
			
		}
		
		newQuantity = oldQuantity + productQuantity ; 
		
	/*	System.out.println("oldQuantity : "+oldQuantity);
		System.out.println("productQuantity : "+productQuantity);
		System.out.println("newQuantity : "+ newQuantity);*/
		
		
		
		if(resultSet != null)
		{
			resultSet.close();
			resultSet = null;
		}
		
		if(updateStockPSMT != null)
		{
			updateStockPSMT.close();
			updateStockPSMT = null;
		}
		sql = "UPDATE "
				+ "shop_product_master "
				+ "SET "
				+ "in_stock_quantity = ? "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ? ";
				
		
		updateStockPSMT = connection.prepareStatement(sql);
		
		
		updateStockPSMT.setFloat(1, newQuantity);
		updateStockPSMT.setString(2, productId);
		updateStockPSMT.setString(3, shopId);
		updateStockPSMT.setString(4, shopCode);

		int rowCount = updateStockPSMT.executeUpdate();
		
		if(rowCount>0)
		{			
			isStockAdded = true;
		}
		
		
		}catch (SQLException e) {
	 		//e.printStackTrace();
			isStockAdded = false;
	 		
	 	}
		catch (Exception e) {
			//e.printStackTrace();
			isStockAdded = false;
		}
		finally
		{
			
			if(resultSet != null)
			{
				try {
					resultSet.close();
					resultSet = null;
				} catch (SQLException e) {
				
				}
			}
			if(updateStockPSMT != null)
			{
				try {
					updateStockPSMT.close();
					updateStockPSMT = null;
				} catch (SQLException e) {
					
				}
			}
			if(connection != null)
			{
				try {
					connection.close();
					connection = null;
				} catch (SQLException e) {
				
				}
			}
			
		}
		//System.out.println(" in update isStockAdded : "+isStockAdded);
		
	
		return isStockAdded;
		
	}
	
	public static boolean addStockToCurrentStock(Connection connection, SessionMap<String, Object> sessionMap, String productId, Float productQuantity, Float productQuantity2)
	{
		
		boolean isStockAdded = false;
		
		

		PreparedStatement updateStockPSMT= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		String db = (String) sessionMap.get("db");
		
		
		try
		{


	
			String shopId = (String) sessionMap.get("shopId");
			String shopCode = (String) sessionMap.get("shopCode");
		
	
		float oldQuantity = 0.0F;
		float newQuantity = 0.0F;
		float oldQuantity2 = 0.0F;
		float newQuantity2 = 0.0F;
		float stockConversionRatio = 0.0F;
	
		sql = "SELECT "
				+ "in_stock_quantity,"
				+ "in_stock_quantity_2,"
				+ "stock_conversion_ratio "
				+ "FROM "
				+ "shop_product_master "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "ANd "
				+ "shop_code = ? ";
		
		updateStockPSMT = connection.prepareStatement(sql);
		
		updateStockPSMT.setString(1, productId);
		updateStockPSMT.setString(2, shopId);
		updateStockPSMT.setString(3, shopCode);
		
	//	System.out.println(updateStockPSMT.toString());
		
		resultSet = updateStockPSMT.executeQuery();
		
		if(resultSet.next())
		{
			oldQuantity = resultSet.getFloat(1);
			oldQuantity2 = resultSet.getFloat(2);
		}
		
		newQuantity = oldQuantity + productQuantity ; 
		newQuantity2 = oldQuantity2 + productQuantity2;
	/*	System.out.println("oldQuantity : "+oldQuantity);
		System.out.println("productQuantity : "+productQuantity);
		System.out.println("newQuantity : "+ newQuantity);*/
		
		
		
		if(resultSet != null)
		{
			resultSet.close();
			resultSet = null;
		}
		
		if(updateStockPSMT != null)
		{
			updateStockPSMT.close();
			updateStockPSMT = null;
		}
		sql = "UPDATE "
				+ "shop_product_master "
				+ "SET "
				+ "in_stock_quantity = ?,"
				+ "in_stock_quantity_2 = ? "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ? ";
				
		
		updateStockPSMT = connection.prepareStatement(sql);
		
		
		updateStockPSMT.setFloat(1, newQuantity);
		updateStockPSMT.setFloat(2, newQuantity2);
		updateStockPSMT.setString(3, productId);
		updateStockPSMT.setString(4, shopId);
		updateStockPSMT.setString(5, shopCode);

		int rowCount = updateStockPSMT.executeUpdate();
		
		if(rowCount>0)
		{			
			isStockAdded = true;
		}
		
		
		}catch (SQLException e) {
	 		//e.printStackTrace();
			isStockAdded = false;
	 		
	 	}
		catch (Exception e) {
			//e.printStackTrace();
			isStockAdded = false;
		}
		finally
		{
			
			if(resultSet != null)
			{
				try {
					resultSet.close();
					resultSet = null;
				} catch (SQLException e) {
				
				}
			}
			if(updateStockPSMT != null)
			{
				try {
					updateStockPSMT.close();
					updateStockPSMT = null;
				} catch (SQLException e) {
					
				}
			}
			
			
		}
		//System.out.println(" in update isStockAdded : "+isStockAdded);
		
	
		return isStockAdded;
		
	}
	public static boolean minusStockToCurrentStock(SessionMap<String, Object> sessionMap,String productId, String productName, Float productQuantity)
	{
		
		
		boolean isStockMinus = false;
		
		String shopId = (String) sessionMap.get("shopId");
		String shopCode = (String) sessionMap.get("shopCode");
		
		Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet  resultSet = null;
		String db = (String) sessionMap.get("db");
		
		String sql = "";
		
		
		try
		{
	
			connection = GetDBConnection.getDBConnection(db);
			
		float oldQuantity = 0.0F;
		float newQuantity = 0.0F;
		
		sql = "SELECT "
				+ "in_stock_quantity "			
				+ "FROM "
				+ "shop_product_master "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ?";
		
		preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setString(1, productId);
		preparedStatement.setString(2, shopId);
		preparedStatement.setString(3, shopCode);
		
		
	
		resultSet = preparedStatement.executeQuery();
		
		if(resultSet.next())
		{
			oldQuantity = resultSet.getFloat(1);
			
		}
	
		
		newQuantity = oldQuantity - productQuantity;
		
		if(preparedStatement != null)
		{
			preparedStatement.close();
		}
		sql = "UPDATE "
				+ "shop_product_master "
				+ "SET "
				+ "in_stock_quantity = ? "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ?";
		
		preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setFloat(1, (float) (Math.round(newQuantity * 100.0) / 100.0));
		preparedStatement.setString(2, productId);
		preparedStatement.setString(3, shopId);
		preparedStatement.setString(4, shopCode);
		
		int rowCount = preparedStatement.executeUpdate();
		
		if(rowCount>0)
		{
			isStockMinus = true;
		}
		
		
		}catch (SQLException e) {
	 		
			isStockMinus = false;
	 		
	 	}
		catch (Exception e) {
			isStockMinus = false;
		}
		finally
		{
			
			if(resultSet != null)
			{
				try {
					resultSet.close();
					resultSet = null;
				} catch (SQLException e) {
				
				}
			}
			if(preparedStatement != null)
			{
				try {
					preparedStatement.close();
					preparedStatement = null;
				} catch (SQLException e) {
					
				}
			}
			if(connection != null)
			{
				try {
					connection.close();
					connection = null;
				} catch (SQLException e) {
					
				}
			}
			
			
			
			
		}
		
		
		
		return isStockMinus;
		
	}
	
	
	public static boolean minusStockToCurrentStock(Connection connection,  SessionMap<String, Object> sessionMap,String productId,  Float productQuantity)
	{
		
		
		boolean isStockMinus = false;
		
		String shopId = (String) sessionMap.get("shopId");
		String shopCode = (String) sessionMap.get("shopCode");
		
		
		PreparedStatement preparedStatement= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		
		
		try
		{
	
		
		float oldQuantity = 0.0F;
		float newQuantity = 0.0F;
		
		sql = "SELECT "
				+ "in_stock_quantity "			
				+ "FROM "
				+ "shop_product_master "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ?";
		
		preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setString(1, productId);
		preparedStatement.setString(2, shopId);
		preparedStatement.setString(3, shopCode);
		
		
	
		resultSet = preparedStatement.executeQuery();
		
		if(resultSet.next())
		{
			oldQuantity = resultSet.getFloat(1);
			
		}
	
		
		newQuantity = oldQuantity - productQuantity;
		
		if(preparedStatement != null)
		{
			preparedStatement.close();
		}
		sql = "UPDATE "
				+ "shop_product_master "
				+ "SET "
				+ "in_stock_quantity = ? "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ?";
		
		preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setFloat(1, (float) (Math.round(newQuantity * 100.0) / 100.0));
		preparedStatement.setString(2, productId);
		preparedStatement.setString(3, shopId);
		preparedStatement.setString(4, shopCode);
		
		int rowCount = preparedStatement.executeUpdate();
		
		if(rowCount>0)
		{
			isStockMinus = true;
		}
		
		
		}catch (SQLException e) {
	 		
			isStockMinus = false;
	 		
	 	}
		catch (Exception e) {
			isStockMinus = false;
		}
		finally
		{
			
			if(resultSet != null)
			{
				try {
					resultSet.close();
					resultSet = null;
				} catch (SQLException e) {
				
				}
			}
			if(preparedStatement != null)
			{
				try {
					preparedStatement.close();
					preparedStatement = null;
				} catch (SQLException e) {
					
				}
			}
			
			
			
			
		}
		
		
		
		return isStockMinus;
		
	}
	
	
	public static boolean minusStockToCurrentStock(Connection connection,  SessionMap<String, Object> sessionMap,String productId, String productName, Float productQuantity, Float productQuantity2)
	{
		
		
		boolean isStockMinus = false;
		
		String shopId = (String) sessionMap.get("shopId");
		String shopCode = (String) sessionMap.get("shopCode");
		
		
		PreparedStatement preparedStatement= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		
		
		try
		{
	
		
		float oldQuantity = 0.0F;
		float newQuantity = 0.0F;
		
		float oldQuantity2 = 0.0F;
		float newQuantity2 = 0.0F;
		
		sql = "SELECT "
				+ "in_stock_quantity,"
				+ "in_stock_quantity_2 "			
				+ "FROM "
				+ "shop_product_master "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ?";
		
		preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setString(1, productId);
		preparedStatement.setString(2, shopId);
		preparedStatement.setString(3, shopCode);
		
		
	
		resultSet = preparedStatement.executeQuery();
		
		if(resultSet.next())
		{
			oldQuantity = resultSet.getFloat(1);
			oldQuantity2 = resultSet.getFloat(2);
			
			
		}
	
		
		newQuantity = oldQuantity - productQuantity;
		newQuantity2 = oldQuantity2 - productQuantity2;
		
		if(preparedStatement != null)
		{
			preparedStatement.close();
			preparedStatement = null;
		}
		 int retval = Float.compare(newQuantity, 0.0F);
		
		if(retval >= 0)
		{
			sql = "UPDATE "
					+ "shop_product_master "
					+ "SET "
					+ "in_stock_quantity = ?,"
					+ "in_stock_quantity_2 = ? "
					+ "WHERE "
					+ "product_id = ? "
					+ "AND "
					+ "shop_id = ? "
					+ "AND "
					+ "shop_code = ?";
			
			preparedStatement = connection.prepareStatement(sql);
			
			
			
			preparedStatement.setFloat(1, newQuantity);
			preparedStatement.setFloat(2, newQuantity2);
			preparedStatement.setString(3, productId);
			preparedStatement.setString(4, shopId);
			preparedStatement.setString(5, shopCode);
			
			int rowCount = preparedStatement.executeUpdate();
			
			if(rowCount>0)
			{
				isStockMinus = true;
			}
			
		}
		else
		{
			
			isStockMinus = false;
		}
		}catch (SQLException e) {
	 		//e.printStackTrace();
			isStockMinus = false;
	 		
	 	}
		catch (Exception e) {
			//e.printStackTrace();
			isStockMinus = false;
		}
		finally
		{
			
			if(resultSet != null)
			{
				try {
					resultSet.close();
					resultSet = null;
				} catch (SQLException e) {
				
				}
			}
			if(preparedStatement != null)
			{
				try {
					preparedStatement.close();
					preparedStatement = null;
				} catch (SQLException e) {
					
				}
			}
			
			
			
			
		}
		
		
		
		return isStockMinus;
		
	}
	
	
	public static boolean minusStockToPurchaseDetails(Connection connection,  SessionMap<String, Object> sessionMap,String productId, String productName, Float productQuantity, String serialBatchId)
	{
		
		
		boolean isStockMinus = false;
		
		String shopId = (String) sessionMap.get("shopId");
		String shopCode = (String) sessionMap.get("shopCode");
		
		
		PreparedStatement preparedStatement= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		
		
		try
		{
	
		
		float oldQuantity = 0.0F;
		float newQuantity = 0.0F;
		
		sql = "SELECT "
				+ "puroduct_quantity "			
				+ "FROM "
				+ "shop_purchase_product_info_master "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "serial_no_batch_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ? ";
		
		preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setString(1, productId);
		preparedStatement.setString(2, serialBatchId);		
		preparedStatement.setString(3, shopId);
		preparedStatement.setString(4, shopCode);
		
		
	
		resultSet = preparedStatement.executeQuery();
		
		if(resultSet.next())
		{
			oldQuantity = resultSet.getFloat(1);
			
		}
	
		
		newQuantity = oldQuantity - productQuantity;
		
		if(preparedStatement != null)
		{
			preparedStatement.close();
			preparedStatement = null;
		}
		 int retval = Float.compare(newQuantity, 0.0F);
		
		if(retval >= 0)
		{
			sql = "UPDATE "
					+ "shop_purchase_product_info_master "
					+ "SET "
					+ "puroduct_quantity = ? "
					+ "WHERE "
					+ "product_id = ? "
					+ "AND "
					+ "serial_no_batch_id = ? "
					+ "AND "
					+ "shop_id = ? "
					+ "AND "
					+ "shop_code = ?";
			
			preparedStatement = connection.prepareStatement(sql);
			
			
			
			preparedStatement.setFloat(1, newQuantity);
			preparedStatement.setString(2, productId);
			preparedStatement.setString(3, serialBatchId);
			preparedStatement.setString(4, shopId);
			preparedStatement.setString(5, shopCode);
			
			int rowCount = preparedStatement.executeUpdate();
			
			if(rowCount>0)
			{
				isStockMinus = true;
			}
			
		}
		else
		{
			
			isStockMinus = false;
		}
		}catch (SQLException e) {
	 		//e.printStackTrace();
			isStockMinus = false;
	 		
	 	}
		catch (Exception e) {
			//e.printStackTrace();
			isStockMinus = false;
		}
		finally
		{
			
			if(resultSet != null)
			{
				try {
					resultSet.close();
					resultSet = null;
				} catch (SQLException e) {
				
				}
			}
			if(preparedStatement != null)
			{
				try {
					preparedStatement.close();
					preparedStatement = null;
				} catch (SQLException e) {
					
				}
			}
			
			
			
			
		}
		
		
		
		return isStockMinus;
		
	}
	
	
	public static boolean minusStockToCurrentStockForPurchase(Connection connection,  SessionMap<String, Object> sessionMap,String productId, String productName, Float productQuantity)
	{
		
		
		boolean isStockMinus = false;
		
		String shopId = (String) sessionMap.get("shopId");
		String shopCode = (String) sessionMap.get("shopCode");
		
		
		PreparedStatement preparedStatement= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		
		
		try
		{
	
		
		float oldQuantity = 0.0F;
		float newQuantity = 0.0F;
		
		sql = "SELECT "
				+ "in_stock_quantity "			
				+ "FROM "
				+ "shop_product_master "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ?";
		
		preparedStatement = connection.prepareStatement(sql);
		
		preparedStatement.setString(1, productId);
		preparedStatement.setString(2, shopId);
		preparedStatement.setString(3, shopCode);
		
	
	
		resultSet = preparedStatement.executeQuery();
		
		if(resultSet.next())
		{
			oldQuantity = resultSet.getFloat(1);
			
		}
	
		
		newQuantity = oldQuantity - productQuantity;
		
		
		if(preparedStatement != null)
		{
			preparedStatement.close();
			preparedStatement = null;
		}
		
	
			sql = "UPDATE "
					+ "shop_product_master "
					+ "SET "
					+ "in_stock_quantity = ? "
					+ "WHERE "
					+ "product_id = ? "
					+ "AND "
					+ "shop_id = ? "
					+ "AND "
					+ "shop_code = ?";
			
			preparedStatement = connection.prepareStatement(sql);
			
			
			
			preparedStatement.setFloat(1, newQuantity);
			preparedStatement.setString(2, productId);
			preparedStatement.setString(3, shopId);
			preparedStatement.setString(4, shopCode);
			
			int rowCount = preparedStatement.executeUpdate();
			
			if(rowCount>0)
			{
				isStockMinus = true;
			}
			
	
		}catch (SQLException e) {
	 		//e.printStackTrace();
			isStockMinus = false;
	 		
	 	}
		catch (Exception e) {
			//e.printStackTrace();
			isStockMinus = false;
		}
		finally
		{
			
			if(resultSet != null)
			{
				try {
					resultSet.close();
					resultSet = null;
				} catch (SQLException e) {
				
				}
			}
			if(preparedStatement != null)
			{
				try {
					preparedStatement.close();
					preparedStatement = null;
				} catch (SQLException e) {
					
				}
			}
			
			
		}
		
		
		
		return isStockMinus;
		
	}
	
	
	
	
	public static boolean addStockToCurrentStockForPurchase(Connection connection, SessionMap<String, Object> sessionMap, String productId, String productName, Float productQuantity)
	{
		
		boolean isStockAdded = false;
		
		
		
		PreparedStatement updateStockPSMT= null;
		ResultSet  resultSet = null;
		
		String sql = "";
		String db = (String) sessionMap.get("db");
		
		
		try
		{
	
	
			String shopId = (String) sessionMap.get("shopId");
			String shopCode = (String) sessionMap.get("shopCode");
		
	
		float oldQuantity = 0.0F;
		float newQuantity = 0.0F;
		float stockConversionRatio = 0.0F;
	
		sql = "SELECT "
				+ "in_stock_quantity,"
				+ "stock_conversion_ratio "
				+ "FROM "
				+ "shop_product_master "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "ANd "
				+ "shop_code = ? ";
		
		updateStockPSMT = connection.prepareStatement(sql);
		
		updateStockPSMT.setString(1, productId);
		updateStockPSMT.setString(2, shopId);
		updateStockPSMT.setString(3, shopCode);
		
	//	System.out.println(updateStockPSMT.toString());
		
		resultSet = updateStockPSMT.executeQuery();
		
		if(resultSet.next())
		{
			oldQuantity = resultSet.getFloat(1);
			stockConversionRatio = resultSet.getFloat(2);
		}
		
		
	//	System.out.println("stockConversionRatio : "+stockConversionRatio);
		 
		newQuantity = oldQuantity + (productQuantity*stockConversionRatio); 
		
		
		 int retval = Float.compare(newQuantity, 0.0F);
		
		/*System.out.println("oldQuantity : "+oldQuantity);
		System.out.println("productQuantity : "+productQuantity);
		System.out.println("newQuantity : "+ newQuantity);*/
		
	
		
		if(resultSet != null)
		{
			resultSet.close();
			resultSet = null;
		}
		
		if(updateStockPSMT != null)
		{
			updateStockPSMT.close();
			updateStockPSMT = null;
		}
		
		if(retval>=0)
		{
		sql = "UPDATE "
				+ "shop_product_master "
				+ "SET "
				+ "in_stock_quantity = ? "
				+ "WHERE "
				+ "product_id = ? "
				+ "AND "
				+ "shop_id = ? "
				+ "AND "
				+ "shop_code = ? ";
				
		
		updateStockPSMT = connection.prepareStatement(sql);
		
		
		updateStockPSMT.setFloat(1, newQuantity);
		updateStockPSMT.setString(2, productId);
		updateStockPSMT.setString(3, shopId);
		updateStockPSMT.setString(4, shopCode);

		int rowCount = updateStockPSMT.executeUpdate();
		
		if(rowCount>0)
		{			
			isStockAdded = true;
		}
		}else
		{
			isStockAdded = false;
		}
		
		}catch (SQLException e) {
	 		e.printStackTrace();
			isStockAdded = false;
	 		
	 	}
		catch (Exception e) {
			e.printStackTrace();
			isStockAdded = false;
		}
		finally
		{
			
			if(resultSet != null)
			{
				try {
					resultSet.close();
					resultSet = null;
				} catch (SQLException e) {
				
				}
			}
			if(updateStockPSMT != null)
			{
				try {
					updateStockPSMT.close();
					updateStockPSMT = null;
				} catch (SQLException e) {
					
				}
			}
		
			
		}
		//System.out.println(" in update isStockAdded : "+isStockAdded);
		
	
		return isStockAdded;
		
	}
	
	public SessionMap<String, Object> getSessionMap() {
		return sessionMap;
	}

	public void setSessionMap(SessionMap<String, Object> sessionMap) {
		this.sessionMap = sessionMap;
	}


	public void setSession(Map<String, Object> map) {  
	    sessionMap=(SessionMap<String, Object>)map;  
	}

}
