package ibase.webitm.ejb.mfg.servlet;

import ibase.system.config.ConnDriver;
import ibase.webitm.utility.ITMException;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.sql.*;
import java.text.SimpleDateFormat;

import ibase.utility.*;

import java.util.Calendar;
import java.util.List;
import java.util.Collection;
import java.util.ArrayList;
import java.io.FileOutputStream;
import java.io.File;
import org.apache.poi.hssf.usermodel.*;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
import org.w3c.dom.*;
import org.apache.poi.hssf.util.HSSFColor;

import ibase.webitm.ejb.mfg.ReqViewMrpBean;
import ibase.webitm.ejb.mfg.TimeMRP;

public class MrpExportExcelServlet extends HttpServlet
{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	int bonCardNo;

	public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException
	{
		doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException
	{
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver(); 

		String itemCode = "";
		String suppSour = "";
		String orderOpt = "";
		String unit = "";
		String desc = "";
		String demand = "";
		String supply = "";
		String quarantineStock = "";
		String planSupply = "";
		String runningDemand = "";
		String runningSupply = "";
		String runningPlanSupply = "";
		String yieldPerc = "";
		String batchQty = "";
		String purLeadTime = "";
		String qcLeadTime = "";
		String mfgLeadTime = "";
		String pendPOQty = "";
		String pendIndQty = "";

		HSSFCell cell = null;
		HSSFRow row = null;
		HSSFCellStyle cellStyle = null;
		
		try
		{

			HSSFWorkbook wb;
			wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("MRPREQ");
			HSSFRow titleRow = sheet.createRow(0);
			titleRow.setHeightInPoints(45);
			cellStyle = wb.createCellStyle();

			HSSFCellStyle style = wb.createCellStyle();
			style.setBorderTop((short) 6); // double lines border
			style.setBorderBottom((short) 1); // single line border
			style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
			HSSFFont font = wb.createFont();
			font.setFontName(HSSFFont.FONT_ARIAL);
			font.setFontHeightInPoints((short) 20);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			font.setColor(HSSFColor.BLUE.index);
			style.setFont(font);
			
			HSSFCellStyle style1 = wb.createCellStyle();
			HSSFFont font1 = wb.createFont();
			font1.setFontName(HSSFFont.FONT_ARIAL);
			font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			font1.setColor(HSSFColor.BLUE.index);
			style1.setFont(font1);
			
			HSSFCellStyle style2 = wb.createCellStyle();
			HSSFFont font2 = wb.createFont();
			font2.setFontName(HSSFFont.FONT_ARIAL);
			font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style2.setFont(font2);

			HSSFCell titleCell = titleRow.createCell((short) 0);
			titleCell.setCellValue(new HSSFRichTextString("MRP - Material Requirements"));
			titleCell.setCellStyle(style);
			sheet.autoSizeColumn((short) 1);

			HSSFRow headerRow = sheet.createRow(2);
			headerRow.setHeightInPoints(20);
			HSSFCell headerCell;
			
			List<String> titles = new ArrayList<String>();
		 	
			titles.add("ITEMCODE");
			titles.add("DESCRIPTION");
			titles.add("UNIT");
			titles.add("SUPP SOURCE");
			titles.add("DEMAND");
			titles.add("SUPPLY");
			titles.add("QUARANTINE STOCK");
			titles.add("PLAN SUPPLY");
			titles.add("RUNNING PLAN SUPPLY");
			titles.add("RUNNING DEMAND");
			titles.add("RUNNING SUPPLY");
			titles.add("PENDING INDENT");
			titles.add("PENDING PO");
			titles.add("REORDER QTY");
			titles.add("PURCHASE LEAD TIME");
			titles.add("MFG LEAD TIME");
			titles.add("QC LEAD TIME");
			titles.add("BATCH QTY");
			titles.add("YIELD PERC");
 
			List<List> itemlist = new ArrayList<List>();
			String saveSampleData = ( String ) request.getParameter("SAVE_SAMPLE_DATA");
			System.out.println("@@@@@@@@@@@@saveSampleData["+saveSampleData+"]");
			itemlist  = new ReqViewMrpBean().preSaveForm(saveSampleData);
			System.out.println("@@@@@@@@@@@@firstRow["+itemlist.size()+"]");
			for(int k=0; k<itemlist.size(); k++)
			{	
				System.out.println("inside for loop");
				if(k==itemlist.size()-1)
				{
					System.out.println("inside if");
					List<String> siteCodeList = new ArrayList<String>();
					siteCodeList= itemlist.get(k);	
					String sitecodeValue= siteCodeList.get(0);
					
					
					List<String> titleRow1 = new ArrayList<String>();
					titleRow1.add("SITECODE");
					titleRow1.add(sitecodeValue);
					
					int insertRow = 1;
					row = sheet.createRow(insertRow);
					for(int n=0; n<titleRow1.size(); n++)
					{						
					cell = row.createCell((short) n);
					cell.setCellValue(titleRow1.get(n));
					cell.setCellStyle(style1);
					}
					insertRow++;
					row = sheet.createRow(insertRow);
					for (int cellCtr = 0; cellCtr < titles.size(); cellCtr++)
					{	
						headerCell = headerRow.createCell((short) cellCtr);
						headerCell.setCellValue(titles.get(cellCtr));
						headerCell.setCellStyle(style2);
					}
					List<String> finalValues = new ArrayList<String>();
					for (int firstRow = 0; firstRow < itemlist.size()-1; firstRow++)
					{
						finalValues= itemlist.get(firstRow);
						System.out.println("finalValues"+finalValues);
							itemCode=finalValues.get(0);
							desc=finalValues.get(1);
							unit=finalValues.get(2);
							suppSour=finalValues.get(3);
							demand=finalValues.get(4);
							supply=finalValues.get(5);
							quarantineStock=finalValues.get(6);
							planSupply=finalValues.get(7);
							runningPlanSupply=finalValues.get(8);
							runningDemand=finalValues.get(9);
							runningSupply=finalValues.get(10);
							pendIndQty=finalValues.get(11);
							pendPOQty=finalValues.get(12);
							orderOpt=finalValues.get(13);
							purLeadTime=finalValues.get(14);
							mfgLeadTime=finalValues.get(15);
							qcLeadTime=finalValues.get(16);
							batchQty=finalValues.get(17);
							yieldPerc=finalValues.get(18);
							
						insertRow++;
						row = sheet.createRow(insertRow);
						for (int j = 0; j < titles.size(); j++)
						{
							cell = row.createCell((short) j);
						}
						
						row.getCell(0).setCellValue(itemCode);
						row.getCell(1).setCellValue(desc);
						row.getCell(2).setCellValue(unit);
						row.getCell(3).setCellValue(suppSour);
						row.getCell(4).setCellValue(demand);
						row.getCell(5).setCellValue(supply);
						row.getCell(6).setCellValue(quarantineStock);
						row.getCell(7).setCellValue(planSupply);
						row.getCell(8).setCellValue(runningPlanSupply);
						row.getCell(9).setCellValue(runningDemand);
						row.getCell(10).setCellValue(runningSupply);
						row.getCell(11).setCellValue(pendIndQty);
						row.getCell(12).setCellValue(pendPOQty);
						row.getCell(13).setCellValue(orderOpt);
						row.getCell(14).setCellValue(purLeadTime);
						row.getCell(15).setCellValue(mfgLeadTime);
						row.getCell(16).setCellValue(qcLeadTime);
						row.getCell(17).setCellValue(batchQty);
						row.getCell(18).setCellValue(yieldPerc);
						
					}
				}
			}	
			response.setHeader("Content-Disposition", "attachment;filename=ReqMRPExcelSheet.xls");
			ServletOutputStream out = response.getOutputStream();
			wb.write(out);
			out.flush();
			out.close(); 

		} catch (Exception e)
		{
			System.out.println("Exception : MrpExportExcelServlet :doPost(HttpServletRequest request, HttpServletResponse response) :" + e);
			try
			{
				throw new ITMException(e);
			} catch (ITMException e1)
			{
				e1.printStackTrace();
			}
		} finally
		{
			try
			{
				if (conn != null)
				{
					conn.close();
					conn = null;
				}
				
			} catch (Exception d)
			{
				d.printStackTrace();
				try
				{
					throw new ITMException(d);
				} catch (ITMException e)
				{
					e.printStackTrace();
				}
			}
		}
	}

}
