package com.reallinkbilling.resource;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;
import org.apache.struts2.dispatcher.SessionMap;

import com.reallinkbilling.db.GetDBConnection;

public class GetListResource {
	
	private static HttpSession session;
	private static HttpServletResponse response;
	
	
	
	public static final Map<String,String> GetUnitShortEnglishName()
	{
		Map<String,String> unitList = new LinkedHashMap<String,String>();
		
		unitList.put("Boxes", "Bx");
		unitList.put("Bags", "Bgs");
		unitList.put("Bottles", "Btls");
		unitList.put("Bunches", "Bnc");
		unitList.put("Dozen", "Dz");
		unitList.put("Feet", "Ft");
		unitList.put("Grams", "gms");
		unitList.put("Kilograms", "kg");
		unitList.put("Liters", "ltr");
		unitList.put("Numbers", "Nos");
		unitList.put("Quintal", "Qtl");
		unitList.put("Rolls", "Rls");
		unitList.put("Sets", "Sets");
		unitList.put("Sheets", "Sheets");
		unitList.put("Units", "Nos");	
		unitList.put("Bunches", "Bunches");
		unitList.put("Cubic Meter", "CubMtr");
		unitList.put("Meter", "Mtr");	
		unitList.put("Cubic Centimeter", "CubCM");
		unitList.put("Cubic Inches", "CubInch");
		unitList.put("Centimeter", "CM");
		unitList.put("Carton", "CTON");
		unitList.put("Milli Grams", "ml");
		unitList.put("Metric TON", "TON");
		unitList.put("Packs", "Pks");
		unitList.put("Pieces", "Pcs");
		unitList.put("Square Feet", "SqFt");
		unitList.put("Square Inches", "SqInch");
		unitList.put("Square Meter", "SqM");
		unitList.put("Tola", "Tola");
		unitList.put("Thousand", "Thns");
		unitList.put("Tubes", "Tubes");	
		unitList.put("Cans", "Cans");	
		unitList.put("Brass", "Brs");
		
		return unitList;
	}

	
	
	public static ArrayList<String> getBarcodeLableSizeList()
	{
		ArrayList<String> labelSizeList = new ArrayList<String>();
		
		labelSizeList.add("80X25X2");
		labelSizeList.add("50X25X1");
		labelSizeList.add("38X25X1");
		labelSizeList.add("38X15X1");
		return labelSizeList;
	}
	
	

	public static ArrayList<String> getCustomerTypeList()
	{
		ArrayList<String> typeList = new ArrayList<String>();
		
		typeList.add("REGULER");
		typeList.add("DEALER");
		typeList.add("RETAILER");
		return typeList;
	}
	public static final Map<String,String> GetTaxList(SessionMap<String, Object> sessionMap)
	{
		Map<String,String> taxList = new LinkedHashMap<String,String>();
		
		Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet resultSet = null;
		
		String sql = "";
		
		if(sessionMap != null)
		{
					String db = (String) sessionMap.get("db");
					String shopId = (String) sessionMap.get("shopId");
					String shopCode = (String) sessionMap.get("shopCode");
					try
					{
						
						connection = GetDBConnection.getDBConnection(db);					
						
						sql = "SELECT "
								+ "id_tax_table,"
								+ "tax_name "
								+ "FROM "
								+ "shop_tax_table_master "
								+ "WHERE "
								+ "shop_id = ? "
								+ "AND "
								+ "shop_code = ? "
								+ "ORDER BY tax_name";
						
						preparedStatement = connection.prepareStatement(sql);
						preparedStatement.setString(1, shopId);
						preparedStatement.setString(2, shopCode);
						
						resultSet = preparedStatement.executeQuery();
						
						while(resultSet.next())
						{
							taxList.put(resultSet.getString(1),resultSet.getString(2));
						}
					
			    } catch (SQLException e) {
					
				} catch (IOException e) {
				
				}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 taxList;
	}
	
	public static final Map<String,String> GetTaxNamePercentage(String taxId, SessionMap<String, Object> sessionMap)
	{
		Map<String,String> taxInfo = new HashMap<String,String>();
		
		Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet resultSet = null;
		
		String sql = "";
		
		if(sessionMap != null)
		{
					String db = (String) sessionMap.get("db");
					
					try
					{
						String shopId = (String) sessionMap.get("shopId");
			    		String shopCode = (String) sessionMap.get("shopCode");
			    		
						connection = GetDBConnection.getDBConnection(db);		
						
						sql = "SELECT "
								+ "taxpercentage,"
								+ "tax_name "
								+ "FROM "
								+ "shop_tax_table_master "
								+ "WHERE "
								+ "id_tax_table = '"+taxId+"' "
								+ "AND "
								+ "shop_id = ? "
								+ "AND "
								+ "shop_code = ? ";
						
						
						preparedStatement = connection.prepareStatement(sql);
						preparedStatement.setString(1, shopId);
						preparedStatement.setString(2, shopCode);
						
						resultSet = preparedStatement.executeQuery();
						
						while(resultSet.next())
						{
							taxInfo.put("TAX-NAME", resultSet.getString(2));
							taxInfo.put("TAX-PERCENTAGE", resultSet.getString(1));
							
						}
					
			} catch (SQLException e) {
				
			} catch (IOException e) {
			
			}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 taxInfo;
		
	}
	
	public static final Map<String,Float> GetTaxIdPercentageList(SessionMap<String, Object> sessionMap)
	{
		Map<String,Float> taxInfo = new HashMap<String,Float>();
		
		Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet resultSet = null;
		
		String sql = "";
		
		if(sessionMap != null)
		{
					String db = (String) sessionMap.get("db");
					
					try
					{
						String shopId = (String) sessionMap.get("shopId");
			    		String shopCode = (String) sessionMap.get("shopCode");
			    		
						connection = GetDBConnection.getDBConnection(db);		
						
						sql = "SELECT "
								+ "id_tax_table,"
								+ "taxpercentage,"
								+ "tax_name "
								+ "FROM "
								+ "shop_tax_table_master "
								+ "WHERE "
								+ "shop_id = ? "
								+ "AND "
								+ "shop_code = ? ";
						
						
						preparedStatement = connection.prepareStatement(sql);
						preparedStatement.setString(1, shopId);
						preparedStatement.setString(2, shopCode);
						resultSet = preparedStatement.executeQuery();
						
						while(resultSet.next())
						{
							taxInfo.put(resultSet.getString(1), resultSet.getFloat(2));
							
							
						}
					
			} catch (SQLException e) {
				
			} catch (IOException e) {
			
			}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 taxInfo;
		
	}
	public static final Map<String,String> GetSupplierList() throws IOException
	{
		Map<String,String> supplierList = new LinkedHashMap<String,String>();
		
		Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet resultSet = null;
		
		String sql = "";
		session=ServletActionContext.getRequest().getSession(false); 
		response = ServletActionContext.getResponse();
		if(session != null)
		{
					String db = (String) session.getAttribute("db");
					
					try
					{
						
						connection = GetDBConnection.getDBConnection(db);					
						
						sql = "SELECT "
								+ "supplier_id,"
								+ "suplier_name "
								+ "FROM shop_supplier_master "
								+ "ORDER BY suplier_name";
						
						preparedStatement = connection.prepareStatement(sql);
						resultSet = preparedStatement.executeQuery();
						
						while(resultSet.next())
						{
							supplierList.put(resultSet.getString(1),resultSet.getString(2));
						}
					
			    } catch (SQLException e) {
					
				} catch (IOException e) {
				
				}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) {}
					}
					
				}
			}
			else
			{
				response.sendRedirect("login.jsp");
			}
		return supplierList;
	}
	
	
	public static final HashMap<String, String> getEmplyeeList(
			SessionMap<String, Object> sessionMap, String typeOfEmployee) {

		HashMap<String, String> employeeList = new LinkedHashMap<String, String>();

		employeeList.put("", "----- SELECT PERSON ----");

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		String sql = "";

		if (sessionMap != null) {

			String db = (String) sessionMap.get("db");

			String shopId = (String) sessionMap.get("shopId");
			String shopCode = (String) sessionMap.get("shopCode");

			try {

				connection = GetDBConnection.getDBConnection(db);

				if (!typeOfEmployee.contains("#")) {

					if (typeOfEmployee.equals("ALL")) {

						sql = "SELECT "
								+ "employee_id, "
								+ "employee_name "
								+ "FROM "
								+ "shop_employee_master "
								+ "WHERE "
								+ "shop_id = ? "
								+ "AND "
								+ "shop_code = ? "
								+ "ORDER BY employee_name";

						preparedStatement = connection.prepareStatement(sql);

						preparedStatement.setString(1, shopId);
						preparedStatement.setString(2, shopCode);

					} else {

						sql = "SELECT "
								+ "employee_id, "
								+ "employee_name "
								+ "FROM "
								+ "shop_employee_master "
								+ "WHERE "
								+ "shop_id = ? "
								+ "AND "
								+ "shop_code = ? "
								+ "AND "
								+ "employee_designation = ? "
								+ "ORDER BY employee_name";

						preparedStatement = connection.prepareStatement(sql);

						preparedStatement.setString(1, shopId);
						preparedStatement.setString(2, shopCode);
						preparedStatement.setString(3, typeOfEmployee);
					}

				} else {

					String employeeTypes[] = typeOfEmployee.split("#");

					String sql2 = "employee_designation IN (";

					int size = employeeTypes.length;

					for (String s : employeeTypes) {

						if (size > 1) {
							sql2 = sql2 + "'" + s + "',";
						} else {
							sql2 = sql2 + "'" + s + "') ";
						}

						size--;
					}

					sql = "SELECT "
							+ "employee_id, "
							+ "employee_name "
							+ "FROM "
							+ "shop_employee_master "
							+ "WHERE "
							+ "shop_id = ? "
							+ "AND "
							+ "shop_code = ? "
							+ "AND "
							+ sql2
							+ "ORDER BY employee_name";

					preparedStatement = connection.prepareStatement(sql);

					preparedStatement.setString(1, shopId);
					preparedStatement.setString(2, shopCode);
				}

				resultSet = preparedStatement.executeQuery();

				while (resultSet.next()) {

					employeeList.put(
							resultSet.getString(1),
							resultSet.getString(2)
					);
				}

			} catch (SQLException e) {

				e.printStackTrace();

			} catch (IOException e) {

				e.printStackTrace();

			} 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 employeeList;
	}
	
	public static final HashMap<String, String> getGodownList(
	        SessionMap<String, Object> sessionMap) {

	    HashMap<String, String> goDownList = new LinkedHashMap<String, String>();

	    goDownList.put("", "--- SELECT GODOWN ---");

	    Connection connection = null;
	    PreparedStatement preparedStatement = null;
	    ResultSet resultSet = null;

	    String sql = "";

	    if (sessionMap != null) {

	        String db = (String) sessionMap.get("db");

	        String shopId = (String) sessionMap.get("shopId");
	        String shopCode = (String) sessionMap.get("shopCode");

	        try {

	            connection = GetDBConnection.getDBConnection(db);

	            sql = "SELECT "
	                    + "godown_id, "
	                    + "godown_code, "
	                    + "godown_name "
	                    + "FROM "
	                    + "shop_godown_master "
	                    + "WHERE "
	                    + "shop_id = ? "
	                    + "AND "
	                    + "shop_code = ? "
	                    + "ORDER BY godown_name";

	            preparedStatement = connection.prepareStatement(sql);

	            preparedStatement.setString(1, shopId);
	            preparedStatement.setString(2, shopCode);

	            resultSet = preparedStatement.executeQuery();

	            while (resultSet.next()) {

	                // Do not show Main godown
	                if ("Main".equals(resultSet.getString(3))) {
	                    continue;
	                }

	                goDownList.put(
	                        resultSet.getString(1),
	                        resultSet.getString(2) + " | "
	                                + resultSet.getString(3)
	                );
	            }

	        } catch (SQLException e) {

	            e.printStackTrace();

	        } catch (IOException e) {

	            e.printStackTrace();

	        } 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 goDownList;
	}
	
	
	public static final String getTaxDetailsByTaxId(String taxId, SessionMap<String, Object> sessionMap)
	{
		String taxDetails="";
		
		Connection connection = null;
		PreparedStatement preparedStatement= null;
		ResultSet resultSet = null;
		
		String sql = "";
		
		if(sessionMap != null)
		{

			String db = (String) sessionMap.get("db");
			
			try
			{
				
				connection = GetDBConnection.getDBConnection(db);					
				
				sql = "SELECT taxname,"
						+ "taxpercentage "
						+ "FROM tax_table "
						+ "WHERE "
						+ "idtax_table = '"+taxId+"'";
				
				preparedStatement = connection.prepareStatement(sql);
				resultSet = preparedStatement.executeQuery();
				
				if(resultSet.next())
				{
					taxDetails = resultSet.getString(1)+":"+resultSet.getString(2);
					
				}
			
	    } catch (SQLException e) {
			
		} catch (IOException e) {
		
		}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) {}
			}
			
		}
	}
	else
	{
		try {
			response.sendRedirect("login.jsp");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
		
		
		return taxDetails;
	}
	public static final Map<String,String> GetUnitList()
	{
		Map<String,String> unitList = new LinkedHashMap<String,String>();
		
		unitList.put("Boxes", "Boxes");
		unitList.put("Bags", "Bags");
		unitList.put("Bottles", "Bottles");
		unitList.put("Bunches", "Bunches");
		unitList.put("Dozen", "Dozen");
		unitList.put("Feet", "Feet");
		unitList.put("Grams", "Grams");
		unitList.put("Kilograms", "Kilograms");
		unitList.put("Liters", "Liters");
		unitList.put("Numbers", "Numbers");
		unitList.put("Quintal", "Quintal");
		unitList.put("Rolls", "Rolls");
		unitList.put("Sets", "Sets");
		unitList.put("Sheets", "Sheets");
		unitList.put("Units", "Units");	
		unitList.put("Bunches", "Bunches");
		unitList.put("Cubic Meter", "Cubic Meter");
		unitList.put("Cubic Centimeter", "Cubic Centimeter");
		unitList.put("Cubic Inches", "Cubic Inches");
		unitList.put("Centimeter", "Centimeter");
		unitList.put("Meter", "Meter");	
		unitList.put("Carton", "Carton");
		unitList.put("Milli Grams", "Milli Grams");
		unitList.put("Metric TON", "Metric TON");
		unitList.put("Packs", "Packs");
		unitList.put("Pieces", "Pieces");
		unitList.put("Square Feet", "Square Feet");
		unitList.put("Square Inches", "Square Inches");
		unitList.put("Square Meter", "Square Meter");
		unitList.put("Tola", "Tola");
		unitList.put("Thousand", "Thousand");
		unitList.put("Tubes", "Tubes");	
		unitList.put("Cans", "Cans");	
		unitList.put("Brass", "Brass");	
		
		
		return unitList;
	}
	

	public static final Map<String,String> GetBillTypeList()
	{
		Map<String,String> billTypeList = new LinkedHashMap<String,String>();
		
		billTypeList.put("CASH-MEMO", "CASH-MEMO");
		billTypeList.put("BILL-OF-SUPPLY", "BILL-OF-SUPPLY");
		billTypeList.put("TAX-INVOICE", "TAX-INVOICE");
		billTypeList.put("QUOTATION", "QUOTATION");
		billTypeList.put("ESTIMATE", "ESTIMATE");
		billTypeList.put("COMPOSITE-BILL", "COMPOSITE-BILL");
		
		return billTypeList;
	}
	
	
	
	
}
