package com.reallinkbilling.download;

import java.awt.Color;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;
import org.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.SessionAware;

import com.opensymphony.xwork2.ActionSupport;
import com.reallinkbilling.DAO.CustomerCreditReportDAO;
import com.reallinkbilling.DAO.CustomerInfoDAO;
import com.reallinkbilling.cipher.EncryptionDecryption;
import com.reallinkbilling.db.GetDBConnection;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.pdfbox.pdmodel.PDPage;
import org.apache.pdfbox.pdmodel.PDPageContentStream;
import org.apache.pdfbox.pdmodel.common.PDRectangle;
import org.apache.pdfbox.pdmodel.font.PDType1Font;
import org.vandeseer.easytable.TableDrawer;
import org.vandeseer.easytable.structure.Row;
import org.vandeseer.easytable.structure.Table;
import org.vandeseer.easytable.structure.Table.TableBuilder;
import org.vandeseer.easytable.structure.cell.TextCell;

import static java.awt.Color.WHITE;
import static org.apache.pdfbox.pdmodel.font.PDType1Font.HELVETICA;
import static org.vandeseer.easytable.settings.HorizontalAlignment.CENTER;
import static org.vandeseer.easytable.settings.HorizontalAlignment.LEFT;
import static org.vandeseer.easytable.settings.HorizontalAlignment.RIGHT;



public class DownloadCustCreditReport extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 693683379006686061L;
	
		public HttpServletRequest request;
		public HttpServletResponse response;
	
		private final static Color BLUE_DARK = new Color(76, 129, 190);
	    private final static Color BLUE_LIGHT_1 = new Color(186, 206, 230);
	    private final static Color BLUE_LIGHT_2 = new Color(218, 230, 242);

	    private final static Color GRAY_LIGHT_1 = new Color(245, 245, 245);
	    private final static Color GRAY_LIGHT_2 = new Color(240, 240, 240);
	    private final static Color GRAY_LIGHT_3 = new Color(216, 216, 216);
	    private static final float PADDING10 = 10f;
	    private static final float PADDING20 = 20f;
	    private static final float PADDING30 = 30f;
	    private static final float PADDING40 = 40f;
	    private static final float PADDING50 = 50f;

	private HttpSession session;
	private String minAmount;
	private String maxAmount;
	private String startDate;
	private String endDate;
	private List<CustomerCreditReportDAO> custCreditList = new ArrayList<CustomerCreditReportDAO>();
	private float totolReportBillAmount = 0.0F;
	private float totalReportPaymentAmount = 0.0F;
	private float totalReportCreditAmount = 0.0F;
	private String returnMessage;
	
	
	
	
	private SessionMap<String,Object> sessionMap;  
	
	public void download()
	
    {
		String result = "login";
		session=ServletActionContext.getRequest().getSession(false); 
		 
	        if(session==null || session.getAttribute("login")==null){ 
	        	addActionMessage("Please Login First !!!");
	        	result = "login";
	        }  
	        else{
	        	
	        	String shopId = (String) this.getSessionMap().get("shopId");
	    		String shopCode = (String) this.getSessionMap().get("shopCode");
	    		
	        	response = ServletActionContext.getResponse();
				request = ServletActionContext.getRequest();

	    		if(this.getMinAmount() != null && !this.getMinAmount().equals(""))
	    		{
	    			if(this.getMinAmount().length()<9)
	    			{
	    				try
	    				{
	    					float min = Float.parseFloat(this.getMinAmount());
	    					if(min<0)
	    					{
	    						addActionError("1.Minimum amount cannot be less than 0 !");
	    						setReturnMessage("1.Minimum amount cannot be less than 0 !");
	    					}
	    					
	    				}catch(NumberFormatException nfe)
	    				{
	    					addActionError("2.Minimum amount is not correct!");
	    					setReturnMessage("2.Minimum amount is not correct!");
	    					
	    				}
	    			}else
	    			{
	    				addActionError("Minimumm amount is not correct!");
	    				setReturnMessage("Minimumm amount is not correct!");
	    			}
	    		}
	    		
	    		if(this.getMaxAmount() != null && !this.getMaxAmount().equals(""))
	    		{
	    			if(this.getMaxAmount().length()<9)
	    			{
	    			try
	    			{
	    				
	    				float max = Float.parseFloat(this.getMaxAmount());
	    				if(max<0)
	    				{
	    					addActionError("Maximum amount cannot be less than 0 !");
	    					setReturnMessage("Maximum amount cannot be less than 0 !");
	    				}
	    				
	    			}catch(NumberFormatException nfe)
	    			{
	    				addActionError("Maximum amount is not correct!");
	    				setReturnMessage("Maximum amount is not correct!");
	    				
	    			}
	    			}else
	    			{
	    				addActionError("Maximum amount is not correct!");
	    				setReturnMessage("Maximum amount is not correct!");
	    			}
	    		}
	        	
	    		if(!hasActionErrors())
	    		{
	    			
	    		
	        	Date today = new Date(); // Fri Jun 17 14:54:28 PDT 2016 
	        	
	        	Calendar cal = Calendar.getInstance(); cal.setTime(today); // don't forget this if date is arbitrary e.g. 01-01-2014
	        	int year = cal.get(Calendar.YEAR);
	        	
	        	
	        	SimpleDateFormat sdf2 = new SimpleDateFormat("dd-MM-YY");
	        	
	        	this.setStartDate("01-04-"+(year-1));
	        	this.setEndDate(sdf2.format(today));
	        	startDate ="01-04-"+(year-1);
	        	endDate =sdf2.format(today);
	        	
	        	Connection connection = null;
	    		PreparedStatement preparedStatement= null;
	    		ResultSet resultSet = null;
	    	 
	    		String sql = "";
	    		String db = (String) this.getSessionMap().get("db");
	    		
	    		try
	    		{
	    			 Map<String,CustomerInfoDAO> customerInfoMap = new LinkedHashMap<String,CustomerInfoDAO>();
	    			
					connection = GetDBConnection.getDBConnection(db);
	    			
	    			sql = "SELECT "
	    					+ "customer_id,"
	    					+ "customer_name,"
	    					+ "mobile_number,"
	    					+ "billing_address,"
	    					+ "credit_limit "
	    					+ "FROM "
	    					+ "shop_customer_master "
	    					+ "WHERE "
	    					+ "shop_id = ? "
	    					+ "AND "
	    					+ "shop_code = ? "
	    					+ "ORDER BY customer_name ASC";
	    			
	    			preparedStatement = connection.prepareStatement(sql);
	    			preparedStatement.setString(1, shopId);
	    			preparedStatement.setString(2, shopCode);
	    			
	    			resultSet = preparedStatement.executeQuery();
	    			int count = 1;
	    		
	    		
	    			while(resultSet.next())
	    			{
	    				CustomerInfoDAO customerInfoDAO = new CustomerInfoDAO();
	    				
	    				customerInfoDAO.setCustomerId(resultSet.getString(1));
	    				customerInfoDAO.setCustName(resultSet.getString(2));
	    				customerInfoDAO.setMobileNumber(resultSet.getString(3));
	    				customerInfoDAO.setBillingaddress(resultSet.getString(4));
	    				customerInfoDAO.setCreditlimit(resultSet.getString(5));
	    				
	    				customerInfoMap.put(resultSet.getString(1),customerInfoDAO);	    				
	    			}
	    			
	    			if(resultSet != null)
	    			{
	    				resultSet.close();
	    				resultSet = null;
	    			}
	    			
	    			if(preparedStatement != null)
	    			{
	    				preparedStatement.close();
	    				preparedStatement = null;
	    			}
	    			
	    			Map<String,CustomerCreditReportDAO> custCreditReportMap = new LinkedHashMap<String,CustomerCreditReportDAO>() ;
	    			
	    			for(String custId: customerInfoMap.keySet())
	    			{
	    				float opeingBalFloat = 0.0F;
       					PreparedStatement getOpeningBalPSMT = null;
           				ResultSet getOpeningBalRs = null;
           				String getOpeningBalSQL = "SELECT "
           						+ "opening_balance,"
           						+ "DATE_FORMAT(opening_bal_update_date,'%d/%m/%Y'),"
           						+ "updated_by "
           						+ "FROM "
           						+ "shop_cust_close_open_bal_master "
           						+ "WHERE "
           						+ "shop_id = ? "
           						+ "AND "
           						+ "shop_code = ? "
           						+ "AND "
           						+ "customer_id = ?";
           				getOpeningBalPSMT = connection.prepareStatement(getOpeningBalSQL);
           				
           				getOpeningBalPSMT.setString(1, shopId);
           				getOpeningBalPSMT.setString(2, shopCode);
           				getOpeningBalPSMT.setString(3, custId);
           				
           				getOpeningBalRs = getOpeningBalPSMT.executeQuery();
           				
           				if(getOpeningBalRs.next())
           				{
       					
           					opeingBalFloat = getOpeningBalRs.getFloat(1);
       					
           				}
           				
           		
           				if(getOpeningBalRs != null)
           				{
           					getOpeningBalRs.close();
           					getOpeningBalRs = null;
           				}
           				if(getOpeningBalPSMT != null)
           				{
           					getOpeningBalPSMT.close();
           					getOpeningBalPSMT = null;
           				}
       					
       					
           				
	    				CustomerInfoDAO customerInfoDAO = customerInfoMap.get(custId);
	    				CustomerCreditReportDAO creditReportDAO = new CustomerCreditReportDAO();
	    				creditReportDAO.setCustometName(customerInfoDAO.getCustName());
	    				creditReportDAO.setCustomerMobile(customerInfoDAO.getMobileNumber());
	    				creditReportDAO.setCustomerAddress(customerInfoDAO.getBillingaddress());
	    				customerInfoDAO.setOpeningBalance(opeingBalFloat);
	    				creditReportDAO.setOpeningBalance(opeingBalFloat);
	    				creditReportDAO.setSrNo(count);
	    				count ++;
	    				
	    				float custTotalPaymentAmount = 0.0F;
	    				
	    				
	    				PreparedStatement getCustPaymentDetailsPSMT = null;
	    				ResultSet getCustPaymnetDetailsRS = null;
	    				
	    				String getCustPaymentDetailsSQL = "SELECT "
		    						+ "SUM(payment_amount) "
			       					+ "FROM "
			       					+ "shop_customer_payment_master "
			       					+ "WHERE "
			       					+ "shop_id = ? "
			       					+ "AND "
			       					+ "shop_code = ? "
			       					+ "AND "
			       					+ "customer_id = ? "
			       					+ "AND "
			       					+ "(entry_date "
			       					+ "BETWEEN "
			       					+ "STR_TO_DATE(?, '%d-%m-%Y') "
			       					+ "AND "
			       					+ "STR_TO_DATE(?, '%d-%m-%Y')) ";
	    		
	    			getCustPaymentDetailsPSMT = connection.prepareStatement(getCustPaymentDetailsSQL);
	    			
	    			getCustPaymentDetailsPSMT.setString(1, shopId);
	    			getCustPaymentDetailsPSMT.setString(2, shopCode);
	    			getCustPaymentDetailsPSMT.setString(3, custId);
	    			getCustPaymentDetailsPSMT.setString(4, this.getStartDate());
	    			getCustPaymentDetailsPSMT.setString(5, this.getEndDate());
	    			
	    			
	    			getCustPaymnetDetailsRS = getCustPaymentDetailsPSMT.executeQuery();
	    			
	    			if(getCustPaymnetDetailsRS.next())
	    			{
	    				if(getCustPaymnetDetailsRS.getString(1) != null)
	    				{
	    					custTotalPaymentAmount = getCustPaymnetDetailsRS.getFloat(1);
	    				}
	    			}
	    			
	    			creditReportDAO.setTotalPaymentAmount(""+custTotalPaymentAmount);
	    			
	    			custCreditReportMap.put(custId, creditReportDAO);
	    			
	    			
			    			if(getCustPaymnetDetailsRS != null)
			    			{
			    				getCustPaymnetDetailsRS.close();
			    				getCustPaymnetDetailsRS = null;
			    			}
			    			
			    			if(getCustPaymentDetailsPSMT != null)
			    			{
			    				getCustPaymentDetailsPSMT.close();
			    				getCustPaymentDetailsPSMT = null;
			    			}
			    			
			    			//System.out.println(creditReportDAO.getCustometName()+"::"+ creditReportDAO.getTotalPaymentAmount());
	    			}
	    			
	    			for(String custId: custCreditReportMap.keySet())	    				
	    			{
	    				CustomerCreditReportDAO creditReportDAO = custCreditReportMap.get(custId);
	    				
	    				float custTotalPaymentAmount = Float.parseFloat(creditReportDAO.getTotalPaymentAmount());
				    			float custTotalCreditAmount = 0.0F;
			    				float custTotalBillAmount = 0.0F;
    				
				    			float floatBillAmt = 0.0F;
			       				float floatBillAmtReceived = 0.0F;
			       				float floatBillBalanceAmt = 0.0F;
			       				
			       				float totalAmountRecieved = 0.0F;
			       				float totalBalanceAmt = 0.0F;			       			
			       				float totalBillAmount = 0.0F;
			       				
			       				
			       				PreparedStatement getBillAmountPSMT = null;
			       				ResultSet getBillAmountRS = null;
			       				String getBillAmountSQL= "";
			       				
			       				getBillAmountSQL = "SELECT "	           					
			           					+ "bill_total_amount,"
			           					+ "bill_amount_received,"
			           					+ "bill_amount_retrurn "
			           					+ "FROM "
			           					+ "shop_bill_info_master "
			           					+ "WHERE "
			           					+ "shop_id = ? "
			           					+ "AND "
			           					+ "shop_code = ? "
			           					+ "AND "		           					
			           					+ "customer_id = ? "
			           					+ "AND "
			           					+ "(bill_date "
			           					+ "BETWEEN "
			           					+ "STR_TO_DATE(?, '%d-%m-%Y') "
				       					+ "AND "
				       					+ "STR_TO_DATE(?, '%d-%m-%Y')) "
				       					+ "AND "
				       					+ "(bill_source IN ('ORIGINAL','BARCODEBILL'))";
			       				
			       				
			       				getBillAmountPSMT = connection.prepareStatement(getBillAmountSQL);
			       				getBillAmountPSMT.setString(1, shopId);
			       				getBillAmountPSMT.setString(2, shopCode);
			       				getBillAmountPSMT.setString(3, custId);
			       				getBillAmountPSMT.setString(4, this.getStartDate());
			       				getBillAmountPSMT.setString(5, this.getEndDate());
			       				
			       				
			       				getBillAmountRS = getBillAmountPSMT.executeQuery();
			       				
			       				while(getBillAmountRS.next())
			       				{
			       					
			       					
			       					try
			       					{
			       						if(getBillAmountRS.getString(2) != null)
				       						{
					       						floatBillAmt = getBillAmountRS.getFloat(1);
					       						floatBillAmtReceived = getBillAmountRS.getFloat(2);
					       						floatBillBalanceAmt = floatBillAmt - floatBillAmtReceived;
					       						totalBillAmount = totalBillAmount + floatBillAmt;
						           				
						           				if(floatBillBalanceAmt > 0)
						           				{
						           					totalAmountRecieved = totalAmountRecieved + floatBillAmtReceived;
						           					totalBalanceAmt = totalBalanceAmt+floatBillBalanceAmt;
						           					
						           					
						           				}
						           				else if(floatBillBalanceAmt <= 0)
						           				{
						           					totalAmountRecieved = totalAmountRecieved + floatBillAmt;
						           				}
				       						}
					           				
					           				
					           				
				       					}catch(NumberFormatException nfe)
				       					{
				       						nfe.printStackTrace();
				       						result = "error";
				       						addActionError("Error while fomatting ! "
				       								+ "While calculating each bill datails");
				       						
				       					}
			       				}
			       				
			       				
			       				
			       				
			       				PreparedStatement getChallanAmountPSMT = null;
			       				ResultSet getChallanAmountRS = null;
			       				String getChallanAmountSQL= "";
			       				
			       				getChallanAmountSQL = "SELECT "	           					
			           					+ "challan_total_amount,"
			           					+ "challan_amount_received,"
			           					+ "challan_amount_retrurn "
			           					+ "FROM "
			           					+ "shop_challan_info_master "
			           					+ "WHERE "
			           					+ "shop_id = ? "
			           					+ "AND "
			           					+ "shop_code = ? "
			           					+ "AND "		           					
			           					+ "customer_id = ? "
			           					+ "AND "
			           					+ "(challan_date "
			           					+ "BETWEEN "
			           					+ "STR_TO_DATE(?, '%d-%m-%Y') "
				       					+ "AND "
				       					+ "STR_TO_DATE(?, '%d-%m-%Y')) ";
			       				
			       				
			       				getChallanAmountPSMT = connection.prepareStatement(getChallanAmountSQL);
			       				getChallanAmountPSMT.setString(1, shopId);
			       				getChallanAmountPSMT.setString(2, shopCode);
			       				getChallanAmountPSMT.setString(3, custId);
			       				getChallanAmountPSMT.setString(4, this.getStartDate());
			       				getChallanAmountPSMT.setString(5, this.getEndDate());
			       				
			       				
			       				getChallanAmountRS = getChallanAmountPSMT.executeQuery();
			       				
			       				while(getChallanAmountRS.next())
			       				{
			       					
			       					
			       					try
			       					{
			       						if(getChallanAmountRS.getString(2) != null)
				       						{
					       						floatBillAmt = getChallanAmountRS.getFloat(1);
					       						floatBillAmtReceived = getChallanAmountRS.getFloat(2);
					       						floatBillBalanceAmt = floatBillAmt - floatBillAmtReceived;
					       						totalBillAmount = totalBillAmount + floatBillAmt;
						           				
						           				if(floatBillBalanceAmt > 0)
						           				{
						           					totalAmountRecieved = totalAmountRecieved + floatBillAmtReceived;
						           					totalBalanceAmt = totalBalanceAmt+floatBillBalanceAmt;
						           					
						           					
						           				}
						           				else if(floatBillBalanceAmt <= 0)
						           				{
						           					totalAmountRecieved = totalAmountRecieved + floatBillAmt;
						           				}
				       						}
					           				
					           				
					           				
				       					}catch(NumberFormatException nfe)
				       					{
				       						nfe.printStackTrace();
				       						result = "error";
				       						addActionError("Error while fomatting ! "
				       								+ "While calculating each bill datails");
				       						
				       					}
			       				}
			       				
			       				
			       				totalBalanceAmt = totalBalanceAmt + creditReportDAO.getOpeningBalance();
			       				
			       				
			       				
			       				custTotalPaymentAmount = custTotalPaymentAmount + totalAmountRecieved;
			       				custTotalCreditAmount = (creditReportDAO.getOpeningBalance()+totalBillAmount) -  custTotalPaymentAmount;
				       			custTotalBillAmount = totalBillAmount;
			       				
			       			
			       				
			       				creditReportDAO.setTotalBillAmount(""+custTotalBillAmount);
			       				creditReportDAO.setTotalCreditAmount(custTotalCreditAmount);
			       				creditReportDAO.setTotalPaymentAmount(""+custTotalPaymentAmount);
			       				
			       				creditReportDAO.setSmsData(EncryptionDecryption.encrypt(creditReportDAO.getCustomerMobile()
			       						+"-"+creditReportDAO.getTotalCreditAmount()));
			       				
			       				
			       				if(!this.getMinAmount().equals("") && !this.getMaxAmount().equals(""))
			       				{
			       					float minAmt = Float.parseFloat(this.getMinAmount());
			       					float maxAmt = Float.parseFloat(this.getMaxAmount());
			       					
			       					if( custTotalCreditAmount > 0 && custTotalCreditAmount >=minAmt && custTotalCreditAmount <=maxAmt)
			       					{
			       						this.getCustCreditList().add(creditReportDAO);
			       					}
			       					
			       				}
			       				else if(!this.getMinAmount().equals("") && this.getMaxAmount().equals(""))
			       				{
			       					float minAmt = Float.parseFloat(this.getMinAmount());
			       				
			       					
			       					if(custTotalCreditAmount > 0 && custTotalCreditAmount >=minAmt)
			       					{
			       						this.getCustCreditList().add(creditReportDAO);
			       					}
			       				}
			       				else if(this.getMinAmount().equals("") && !this.getMaxAmount().equals(""))
			       				{
			       					float maxAmt = Float.parseFloat(this.getMaxAmount());
			       				
			       					
			       					if(custTotalCreditAmount > 0 && custTotalCreditAmount <=maxAmt)
			       					{
			       						this.getCustCreditList().add(creditReportDAO);
			       					}
			       				}
			       				else
			       				{
			       					if(custTotalCreditAmount > 0) {
			       						
			       						this.getCustCreditList().add(creditReportDAO);
			       					}
			       					
			       				}
			       				
			       				
			       				
			       			
			       			   
			       				
			       				if(getBillAmountRS != null)
			       				{
			       					getBillAmountRS.close();
			       					getBillAmountRS = null;
			       				}
			       				if(getBillAmountPSMT != null)
			       				{
			       					getBillAmountPSMT.close();
			       					getBillAmountPSMT = null;
			       				}
			       				
			       				
				    			
	    			}	
	    			
	    			// Sort by outstanding amount: highest to lowest
	    			Collections.sort(custCreditList,
	    			    new java.util.Comparator<CustomerCreditReportDAO>() {
	    			        @Override
	    			        public int compare(CustomerCreditReportDAO c1,
	    			                           CustomerCreditReportDAO c2) {
	    			            return Float.compare(
	    			                c2.getTotalCreditAmount(),
	    			                c1.getTotalCreditAmount()
	    			            );
	    			        }
	    			    }
	    			);

	    			// Calculate report totals
	    			int listSize = custCreditList.size();

	    			for (int s = 0; s < listSize; s++) {

	    			    CustomerCreditReportDAO dao = custCreditList.get(s);

	    			    totolReportBillAmount += Float.parseFloat(dao.getTotalBillAmount());

	    			    totalReportPaymentAmount += Float.parseFloat(dao.getTotalPaymentAmount());

	    			    totalReportCreditAmount += dao.getTotalCreditAmount();
	    			}
	    			
	    			this.setTotolReportBillAmount(totolReportBillAmount);
	    			this.setTotalReportPaymentAmount(totalReportPaymentAmount);
	    			this.setTotalReportCreditAmount(totalReportCreditAmount);
	    			
	    			
	
	    		
	    			
	    			
	    			
	    			String path=	request.getSession().getServletContext().getRealPath("/images/");
	    			PDDocument document = new PDDocument(); 
					PDPage page = null;
					
					SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-Y");
					Date date = new Date();
					
					String dateToday = sdf.format(date); 
						 page = new PDPage(PDRectangle.A4);
					
					
				        document.addPage(page);
				         PDPageContentStream contentStream = new PDPageContentStream(document, page);
				         int pageCount = 1;
			    			 Table header;
			    			 Table creditDetails;
			    			 
			    			
			    			  TableBuilder tableHeaderBuilder = Table.builder()
						                .addColumnsOfWidth(550)				                
						                .font(HELVETICA)
						                .borderColor(Color.BLACK)
						                .horizontalAlignment(CENTER);
					         
					          Row headerName = Row.builder()
						                .add(TextCell.builder().text((String)this.getSessionMap().get("shopnameenglish")).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(10F).build())				               
						                .backgroundColor(WHITE)
						                .textColor(Color.BLACK)
						                .font(PDType1Font.HELVETICA_BOLD).fontSize(18)				              
						                .horizontalAlignment(CENTER)
						                .build();
					          
					          Row address = Row.builder()
						                .add(TextCell.builder().text((String)this.getSessionMap().get("shopaddresseng")).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(10F).build())				               
						                .backgroundColor(WHITE)
						                .textColor(Color.BLACK)
						                .font(PDType1Font.HELVETICA_BOLD).fontSize(10)				              
						                .horizontalAlignment(CENTER)
						                .build();
					          
					          Row info1 = Row.builder()
						                .add(TextCell.builder().text("Customer Credit Report From Date: "
						                			+startDate+" To Date : "+endDate +"       Page No . : "+pageCount).borderWidthBottom(0.0F).horizontalAlignment(CENTER).build())				               
						                .backgroundColor(WHITE)
						                .textColor(Color.BLACK)
						                .font(PDType1Font.HELVETICA_BOLD).fontSize(10)
						                .horizontalAlignment(CENTER)
						                .build();
					          
					          Row info2 = Row.builder()
						                .add(TextCell.builder().text("Report generated for Minimum Credit Amount: "
						                		+ ""+this.getMinAmount()
						                		+ "and Maximum Credit Amount:"+this.getMaxAmount()+ " , Generated On : "+dateToday).borderWidthBottom(0.0F).horizontalAlignment(CENTER).build())				               
						                .backgroundColor(WHITE)
						                .textColor(Color.BLACK)
						                .font(PDType1Font.HELVETICA_BOLD).fontSize(8)
						                .horizontalAlignment(CENTER)
						                .build();
					         
					          
					          tableHeaderBuilder.addRow(headerName);
					          tableHeaderBuilder.addRow(address);
					          tableHeaderBuilder.addRow(info1);
					          tableHeaderBuilder.addRow(info2);
					          header = tableHeaderBuilder.build();
					          
					          
					          TableBuilder tableCreditDetailsBuilder = Table.builder()
						                .addColumnsOfWidth(20,140,60,115,60,60,60,60)				                
						                .font(HELVETICA)
						                .borderWidth(0.5F)
						                .borderColor(Color.BLACK)
						                .horizontalAlignment(CENTER);
					          
					          Row tableHeader = Row.builder()
					        		  .add(TextCell.builder().text("Sr.").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())				                
						                .add(TextCell.builder().text("Customer Name").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
						                .add(TextCell.builder().text("Mobile").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
						                .add(TextCell.builder().text("Address").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())				                
						                .add(TextCell.builder().text("Open.Bal.").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
							            .add(TextCell.builder().text("Bill Amount").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
						                .add(TextCell.builder().text("Received Amount").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
						                .add(TextCell.builder().text("Outstanding Amount").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())									                
						                .backgroundColor(Color.LIGHT_GRAY)
						                .textColor(Color.BLACK)
						                .font(PDType1Font.HELVETICA_BOLD).fontSize(8)				              
						                .horizontalAlignment(CENTER)
						                .build();
					          tableCreditDetailsBuilder.addRow(tableHeader);
					       
					        
					     
					        	  int size = custCreditList.size();
					        	  
					        	  
						          float startY = page.getMediaBox().getHeight() - PADDING20;
						          float currentY = startY;
						          TableDrawer.builder()
					                .contentStream(contentStream)
					                .table(header)
					                .startX(PADDING30)
					                .startY(startY)
					                .build()
					                .draw();
						          
						        
					        	  
					        	  for(int s=0;s<size;s++)
					        	  {
					        		  
					        		  if(currentY < 100)
					        		  {
					        			  pageCount++;
					        			  creditDetails = tableCreditDetailsBuilder.build();
					        			  TableDrawer.builder()
							                .contentStream(contentStream)
							                .table(creditDetails)
							                .startX(PADDING10)
							                .startY(startY-50)
							                .build()
							                .draw();
					        			  
					        			  contentStream.close();
					        			  
					        			  header = null;
					        			  
					        			 
							    			 
							    			
							    			  tableHeaderBuilder = Table.builder()
										                .addColumnsOfWidth(550)				                
										                .font(HELVETICA)
										                .borderColor(Color.BLACK)
										                .horizontalAlignment(CENTER);
									         
									           headerName = Row.builder()
										                .add(TextCell.builder().text((String)this.getSessionMap().get("shopnameenglish")).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(10F).build())				               
										                .backgroundColor(WHITE)
										                .textColor(Color.BLACK)
										                .font(PDType1Font.HELVETICA_BOLD).fontSize(18)				              
										                .horizontalAlignment(CENTER)
										                .build();
									            address = Row.builder()
										                .add(TextCell.builder().text((String)this.getSessionMap().get("shopaddresseng")).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(10F).build())				               
										                .backgroundColor(WHITE)
										                .textColor(Color.BLACK)
										                .font(PDType1Font.HELVETICA_BOLD).fontSize(10)				              
										                .horizontalAlignment(CENTER)
										                .build();
									          
									           info1 = Row.builder()
										                .add(TextCell.builder().text("Customer Credit Report From Date: "
										                			+startDate+" To Date : "+endDate +"       Page No . : "+pageCount).borderWidthBottom(0.0F).horizontalAlignment(CENTER).build())				               
										                .backgroundColor(WHITE)
										                .textColor(Color.BLACK)
										                .font(PDType1Font.HELVETICA_BOLD).fontSize(10)
										                .horizontalAlignment(CENTER)
										                .build();
									          
									           info2 = Row.builder()
										                .add(TextCell.builder().text("Report generated for Minimum Credit Amount: "
										                		+ ""+this.getMinAmount()
										                		+ "and Maximum Credit Amount:"+this.getMaxAmount()+ " , Generated On : "+dateToday).borderWidthBottom(0.0F).horizontalAlignment(CENTER).build())				               
										                .backgroundColor(WHITE)
										                .textColor(Color.BLACK)
										                .font(PDType1Font.HELVETICA_BOLD).fontSize(8)
										                .horizontalAlignment(CENTER)
										                .build();
									         
									          
									          tableHeaderBuilder.addRow(headerName);
									          tableHeaderBuilder.addRow(address);
									          tableHeaderBuilder.addRow(info1);
									          tableHeaderBuilder.addRow(info2);
									          header = tableHeaderBuilder.build();
					        			  
					        			  creditDetails = null;
					        			   tableCreditDetailsBuilder = Table.builder()
					        					   .addColumnsOfWidth(20,140,60,115,60,60,60,60)				                
									                .font(HELVETICA)
									                .borderWidth(0.5F)
									                .borderColor(Color.BLACK)
									                .horizontalAlignment(CENTER);
								          
								           tableHeader = Row.builder()
								        		  .add(TextCell.builder().text("Sr.").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())				                
									                .add(TextCell.builder().text("Customer Name").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
									                .add(TextCell.builder().text("Mobile").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
									                .add(TextCell.builder().text("Address").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())				                
									                .add(TextCell.builder().text("Open.Bal.").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
											        .add(TextCell.builder().text("Bill Amount").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
									                .add(TextCell.builder().text("Received Amount").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
									                .add(TextCell.builder().text("Outstanding Amount").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())									                
									                .backgroundColor(Color.LIGHT_GRAY)
									                .textColor(Color.BLACK)
									                .font(PDType1Font.HELVETICA_BOLD).fontSize(8)				              
									                .horizontalAlignment(CENTER)
									                .build();
								          tableCreditDetailsBuilder.addRow(tableHeader);
								          
					        			  
					        				PDPage newPage = new PDPage(PDRectangle.A4);
								        	 contentStream = new PDPageContentStream(document,newPage);
								        	currentY =  newPage.getMediaBox().getHeight() - PADDING20;
								             document.addPage( newPage );
								             
								             TableDrawer.builder()
								                .contentStream(contentStream)
								                .table(header)
								                .startX(PADDING30)
								                .startY(currentY)
								                .build()
								                .draw();
					        			  
					        		  }
					        		  
					        		  CustomerCreditReportDAO  dao2 =custCreditList.get(s) ;
					        		  	Row custInfo = Row.builder()
					        			  	.add(TextCell.builder().text(""+(s+1)).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.5F).build())				                
					        			  	.add(TextCell.builder().text(dao2.getCustometName()).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.5F).build())				                
					        			  	.add(TextCell.builder().text(dao2.getCustomerMobile()).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.5F).build())				                
					        			  	.add(TextCell.builder().text(dao2.getCustomerAddress()).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.5F).build())				                
					        				.add(TextCell.builder().text(""+dao2.getOpeningBalance()).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.5F).build())				          
					        				.add(TextCell.builder().text(dao2.getTotalBillAmount()).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.5F).build())				                
					        			  	.add(TextCell.builder().text(dao2.getTotalPaymentAmount()).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.5F).build())				                
					        			  	.add(TextCell.builder().text(""+dao2.getTotalCreditAmount()).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.5F).build())				                
					        			  	.backgroundColor(Color.WHITE)
							                .textColor(Color.BLACK)
							                .font(PDType1Font.HELVETICA)
							                .fontSize(8)				              
							                .horizontalAlignment(CENTER)
							                .height(25F)					                
							                .build();
					        	  
					        	  tableCreditDetailsBuilder.addRow(custInfo);
					        	 
					        	  currentY = currentY -25;
					        	
					        	  }
					        	  
					        	  Row custInfo = Row.builder()
					        			  	.add(TextCell.builder().text("Total").colSpan(5).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(1F).build())				                
					        			  	.add(TextCell.builder().text(""+totolReportBillAmount).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(1F).build())				                
					        			  	.add(TextCell.builder().text(""+totalReportPaymentAmount).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(1F).build())				                
					        			  	.add(TextCell.builder().text(""+totalReportCreditAmount).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(1F).build())				                
					        			  	.backgroundColor(Color.WHITE)
							                .textColor(Color.BLACK)
							                .font(PDType1Font.HELVETICA_BOLD)
							                .fontSize(8)				              
							                .horizontalAlignment(CENTER)
							                .height(15F)					                
							                .build();
					        	  
					        	  tableCreditDetailsBuilder.addRow(custInfo);
					        	  creditDetails = tableCreditDetailsBuilder.build();
					         
					          
					          TableDrawer.builder()
				                .contentStream(contentStream)
				                .table(creditDetails)
				                .startX(PADDING10)
				                .startY(startY-50)
				                .build()
				                .draw();
	    			   contentStream.close();
				        
				       // System.out.println(path);
								
				        
				        document.save(path+"/cust-credit-report.pdf");
				        document.close();
				        
				        response.setHeader("Content-disposition", "inline; filename=customer-credit-report.pdf");
						response.setContentType("application/pdf");
						@SuppressWarnings("resource")
						FileInputStream fileInputStream = new FileInputStream(path+"/cust-credit-report.pdf");
						PrintWriter out = response.getWriter();  
						int bytes;
						while ((bytes = fileInputStream.read()) != -1) {
							out.write(bytes);
						}
						fileInputStream.close();
						out.flush();
						out.close();
			        
						
					//String fontPath =	request.getSession().getServletContext().getRealPath("/fonts/Nirmala.ttf");
							setReturnMessage("Downloaded Successfully !");
							result = "success";
	    			
	    			
	
	    		}
	    		catch (SQLException e) {
	    			//e.printStackTrace();
	    			result = "error";
	    			
	    			addActionError(e.getErrorCode()+" || "+e.getMessage() +" || Something Went Wrong!");
	    		} catch (IOException e) {
	    			result = "error";
	    			
	    			addActionError(e.getMessage()+" || "+e.getCause()+" || Something Went Wrong!");
	    		}
    			finally {
	    			if(resultSet != null)
					{
						try {
							resultSet.close();
						} catch (SQLException e) {
							
						}
					}
	    			if(preparedStatement != null)
	    			{
	    				try {preparedStatement.close();} catch (SQLException e) {}
	    			}
	    			if(connection != null)
	    			{
	    				
	    				try {connection.close();}catch (SQLException e) {}
	    			}
	    			
	    		}
        	
	    		}
	        }
        
}


@Override
public void validate() {
	
	if(this.getMinAmount() != null && !this.getMinAmount().equals(""))
	{
		if(this.getMinAmount().length()<9)
		{
			try
			{
				float min = Float.parseFloat(this.getMinAmount());
				if(min<0)
				{
					addActionError("1.Minimum amount cannot be less than 0 !");
				}
				
			}catch(NumberFormatException nfe)
			{
				addActionError("2.Minimum amount is not correct!");
				
			}
		}else
		{
			addActionError("Minimumm amount is not correct!");
		}
	}
	
	if(this.getMaxAmount() != null && !this.getMaxAmount().equals(""))
	{
		if(this.getMaxAmount().length()<9)
		{
		try
		{
			
			float max = Float.parseFloat(this.getMaxAmount());
			if(max<0)
			{
				addActionError("Maximum amount cannot be less than 0 !");
			}
			
		}catch(NumberFormatException nfe)
		{
			addActionError("Maximum amount is not correct!");
			
		}
		}else
		{
			addActionError("Maximum amount is not correct!");
		}
	}
	       
	}


	

	public String getStartDate() {
		return startDate;
	}


	public void setStartDate(String startDate) {
		this.startDate = startDate;
	}


	public String getEndDate() {
		return endDate;
	}


	public void setEndDate(String endDate) {
		this.endDate = endDate;
	}


	public String getMinAmount() {
		return minAmount;
	}


	public void setMinAmount(String minAmount) {
		this.minAmount = minAmount;
	}


	public String getMaxAmount() {
		return maxAmount;
	}


	public void setMaxAmount(String maxAmount) {
		this.maxAmount = maxAmount;
	}


	public String getReturnMessage() {
		return returnMessage;
	}


	public void setReturnMessage(String returnMessage) {
		this.returnMessage = returnMessage;
	}


	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;  
	}




	public List<CustomerCreditReportDAO> getCustCreditList() {
		return custCreditList;
	}




	public void setCustCreditList(List<CustomerCreditReportDAO> custCreditList) {
		this.custCreditList = custCreditList;
	}


	public HttpServletRequest getRequest() {
		return request;
	}


	public void setRequest(HttpServletRequest request) {
		this.request = request;
	}


	public HttpServletResponse getResponse() {
		return response;
	}


	public void setResponse(HttpServletResponse response) {
		this.response = response;
	}


	public HttpSession getSession() {
		return session;
	}


	public void setSession(HttpSession session) {
		this.session = session;
	}


	public float getTotolReportBillAmount() {
		return totolReportBillAmount;
	}


	public void setTotolReportBillAmount(float totolReportBillAmount) {
		this.totolReportBillAmount = totolReportBillAmount;
	}


	public float getTotalReportPaymentAmount() {
		return totalReportPaymentAmount;
	}


	public void setTotalReportPaymentAmount(float totalReportPaymentAmount) {
		this.totalReportPaymentAmount = totalReportPaymentAmount;
	}


	public float getTotalReportCreditAmount() {
		return totalReportCreditAmount;
	}


	public void setTotalReportCreditAmount(float totalReportCreditAmount) {
		this.totalReportCreditAmount = totalReportCreditAmount;
	}
	       
	
}
