package com.reallinkbilling.download;

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;

import java.awt.Color;
import java.io.FileInputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.ServletRequestAware;
import org.apache.struts2.interceptor.ServletResponseAware;
import org.apache.struts2.interceptor.SessionAware;
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 com.reallinkbilling.DAO.ItemwisePurchaseReportDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;

public class DownloadItemWisePurchaseReport extends ActionSupport implements SessionAware,ServletRequestAware,ServletResponseAware{

	
	

		/**
		 * 
		 */
		private static final long serialVersionUID = 2231894051386352138L;

			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 SessionMap<String,Object> sessionMap;
		private String fromDate;
		private String toDate;
		private HttpServletRequest request = null;
		private HttpServletResponse response = null;
		private List<ItemwisePurchaseReportDAO> itemWisePurchaseList = new  ArrayList<ItemwisePurchaseReportDAO>();
		
		
		public void downloadPurchaseReport() throws Exception {
			
			//System.out.println("Bill Number : "+billNumber);
			
			float totalPuchaseAmt = 0.0F;
			
					Connection connection = null;
					PreparedStatement preparedStatement= null;
					ResultSet resultSet = null;
					
					String sql = "";
					
					Map<String, Object> session = ActionContext.getContext().getSession();
					
					String db = (String) session.get("db");
					try
		        	{
						
						
					
						connection = GetDBConnection.getDBConnection(db);
						
						String shopId = (String) this.getSessionMap().get("shopId");
			    		String shopCode = (String) this.getSessionMap().get("shopCode");
						
						
						sql = "SELECT "
								//+ "spim.purchase_date,"
								+ "sppim.product_name,"
								+ "sppim.purchase_unit,"
								+ "SUM(sppim.puroduct_quantity),"
								+ "SUM(sppim.product_total_amount),"							
								+ "SUM(sppim.product_net_amount),"
								+ "SUM(sppim.product_igst_amount),"
								+ "SUM(sppim.product_cgst_amount),"
								+ "SUM(sppim.product_sgst_amount) "
								+ "FROM "
								+ "shop_purchase_info_master AS spim "
								+ "INNER JOIN "
								+ "shop_purchase_product_info_master AS sppim "
								+ "ON "
								+ "spim.purchase_number = sppim.purchase_number "
								+ "WHERE "
								+ "spim.shop_id = ? "
								+ "AND "
								+ "spim.shop_code = ? "
								+ "AND "
								+ "sppim.shop_id = ? "
								+ "AND "
								+ "sppim.shop_code = ? "
								+ "AND "						
								+ "(spim.purchase_date "
								+ "BETWEEN STR_TO_DATE(?,'%d-%m-%Y') "
								+ "AND "
								+ "STR_TO_DATE(?,'%d-%m-%Y')) "
								+ "GROUP BY "
								+ "sppim.purchase_unit,sppim.product_name";
						
						preparedStatement = connection.prepareStatement(sql);
						preparedStatement.setString(1, shopId);
						preparedStatement.setString(2, shopCode);
						preparedStatement.setString(3, shopId);
						preparedStatement.setString(4, shopCode);					
						preparedStatement.setString(5, this.getFromDate());
						preparedStatement.setString(6, this.getToDate());
					
						
						resultSet = preparedStatement.executeQuery();
						DecimalFormat df = new DecimalFormat("##.##");
						
						while(resultSet.next())
						{
							ItemwisePurchaseReportDAO purchaseReportDAO =  new ItemwisePurchaseReportDAO();
							purchaseReportDAO.setProductName(resultSet.getString(1));
							purchaseReportDAO.setUnits(resultSet.getString(2));
							purchaseReportDAO.setQuantity(df.format(resultSet.getFloat(3)));
							purchaseReportDAO.setTotalAmount(df.format(resultSet.getFloat(4)));
							
							totalPuchaseAmt = totalPuchaseAmt + resultSet.getFloat(4);
							
							purchaseReportDAO.setRate(resultSet.getFloat(4)/resultSet.getFloat(3));
							this.getItemWisePurchaseList().add(purchaseReportDAO);
							
							
							
						}
						
						
						if(this.getItemWisePurchaseList().size()>0)
						{
							
							
							try
							{	
								
							String name = (String) this.getSessionMap().get("shopnameenglish");
							String address = (String) this.getSessionMap().get("shopaddresseng");
							String path= this.getRequest().getSession().getServletContext().getRealPath("/images/");
							
							
								
								 java.util.Date date = new java.util.Date();
					        	SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
					        	String reportDate = dateFormat.format(date);
								
								
					       	     long t = date.getTime();	        	     
					       	     java.sql.Time time = new java.sql.Time(t);
					       	     
								PDDocument document = new PDDocument(); 
								PDPage page = null;
								
								 page = new PDPage(PDRectangle.A4);
								
								 
								 document.addPage(page);
								 
								 
						         PDPageContentStream contentStream = new PDPageContentStream(document, page);
								 
						         int pageCount = 1;
						         
						         Table header;
						         
						         
						         
						         
						         TableBuilder tableHeaderBuilder = Table.builder()
							                .addColumnsOfWidth(550)				                
							                .font(HELVETICA)
							                .borderColor(Color.BLACK)
							                .horizontalAlignment(CENTER);
						         
						         Row dairyNameHeader = Row.builder()
							                .add(TextCell.builder().text(name).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.5F).build())				               
							                .backgroundColor(WHITE)
							                .textColor(Color.BLACK)
							                .font(PDType1Font.HELVETICA_BOLD).fontSize(16)				              
							                .horizontalAlignment(CENTER)
							                .build();
						         
						         Row addressRow = Row.builder()
							                .add(TextCell.builder().text(address).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.5F).build())				               
							                .backgroundColor(WHITE)
							                .textColor(Color.BLACK)
							                .font(PDType1Font.HELVETICA_BOLD).fontSize(8)				              
							                .horizontalAlignment(CENTER)
							                .build();
						         
						         
						         Row reportInfo1 = Row.builder()
							                .add(TextCell.builder()
							                		.text("Item wise Purchase Details Report From Date : "+this.getFromDate()+" to Date: "+this.getToDate()+" "+time)
							                		.horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.5F).build())				               
							                .backgroundColor(WHITE)
							                .textColor(Color.BLACK)
							                .font(PDType1Font.HELVETICA_BOLD).fontSize(12)				              
							                .horizontalAlignment(CENTER)
							                .build();
						         
						         
						         
						         
						         tableHeaderBuilder.addRow(dairyNameHeader);
						         tableHeaderBuilder.addRow(addressRow);
						         tableHeaderBuilder.addRow(reportInfo1);
						        
						         
						         header = tableHeaderBuilder.build();
						         
						         float startY = page.getMediaBox().getHeight() - PADDING20;
						         TableDrawer.builder()
					                .contentStream(contentStream)
					                .table(header)
					                .startX(PADDING30)
					                .startY(startY)
					                .build()
					                .draw();
								
						         
						         Table stockReportHeaderInfo;
						         TableBuilder tableStockInfoBuilder = Table.builder()
							                .addColumnsOfWidth(50,200,100,70,120)				                
							                .font(HELVETICA)
							                .borderWidth(0.5F)
							                .borderColor(Color.BLACK)
							                .horizontalAlignment(CENTER);
						         
						         
						         Row stockTableHeader = Row.builder()
						        		  	.add(TextCell.builder().text("SrNo").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())					              
							                .add(TextCell.builder().text("Product Name").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())				               
							                .add(TextCell.builder().text("Quantity").horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(1F).build())
							                .add(TextCell.builder().text("Avg. Rate").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
							                .add(TextCell.builder().text("Purchase Amount").horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(1F).build())
							               .backgroundColor(Color.LIGHT_GRAY)
							                .textColor(Color.BLACK)
							                .font(PDType1Font.HELVETICA_BOLD).fontSize(8)				              
							                .horizontalAlignment(CENTER)
							                .build();
						         
						         tableStockInfoBuilder.addRow(stockTableHeader);
						         stockReportHeaderInfo = tableStockInfoBuilder.build();
						        		
						        		 TableDrawer.builder()
							                .contentStream(contentStream)
							                .table(stockReportHeaderInfo)
							                .startX(PADDING20)
							                .startY(startY-50)
							                .build()
							                .draw();
						         
						        		 float currentY =  75.0F;
						        		 
						        		 int srNo = 1;
						        		 
						        		 Table stockInfoRowTable = null;
							        	 
								         TableBuilder tableStockInfoRowBuilder = Table.builder()
									                .addColumnsOfWidth(50,200,100,70,120)				                
									                .font(HELVETICA)
									                .borderWidth(0.5F)
									                .borderColor(Color.BLACK)
									                .horizontalAlignment(CENTER);
						
						        		 for(ItemwisePurchaseReportDAO detailsDAO :  this.getItemWisePurchaseList())
						        		 {
								        	 
									         if(currentY > 740)
									         {
									        	 stockInfoRowTable = tableStockInfoRowBuilder.build();
									        	 
									             TableDrawer.builder()
									                .contentStream(contentStream)
									                .table(stockInfoRowTable)
									                .startX(PADDING20)
									                .startY(startY-70)
									                .build()
									                .draw();
									             
									             stockInfoRowTable = null;
									             tableStockInfoRowBuilder = null;
									             
									             tableStockInfoRowBuilder = Table.builder()
											                .addColumnsOfWidth(50,200,100,70,120)				                
											                .font(HELVETICA)
											                .borderWidth(0.5F)
											                .borderColor(Color.BLACK)
											                .horizontalAlignment(CENTER);
									             
									             
									             
									             tableStockInfoRowBuilder.addRow(stockTableHeader);
									             
									        	 pageCount++;
									        	 contentStream.close();
									        	PDPage newPage = new PDPage(PDRectangle.A4);
									        	 contentStream = new PDPageContentStream(document,newPage);
									        	currentY = 20.0F;
									             document.addPage( newPage );
									             
									             
									             
									             Table headerOtherPage;
									             
									             
									             TableBuilder tableHeaderBuilderOtherPage = Table.builder()
											                .addColumnsOfWidth(550)				                
											                .font(HELVETICA)
											                .borderColor(Color.BLACK)
											                .horizontalAlignment(CENTER);
										         
										         Row dairyNameHeaderOtherPage = Row.builder()
											                .add(TextCell.builder().text(name).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.5F).build())				               
											                .backgroundColor(WHITE)
											                .textColor(Color.BLACK)
											                .font(PDType1Font.HELVETICA_BOLD).fontSize(16)				              
											                .horizontalAlignment(CENTER)
											                .build();
										         
										         Row addressRowOtherPage = Row.builder()
											                .add(TextCell.builder().text(address).horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.5F).build())				               
											                .backgroundColor(WHITE)
											                .textColor(Color.BLACK)
											                .font(PDType1Font.HELVETICA_BOLD).fontSize(8)				              
											                .horizontalAlignment(CENTER)
											                .build();
										         
										         
										         Row reportInfo1OtherPage = Row.builder()
											                .add(TextCell.builder()
											                  		.text("Item Wise Purchase Details Report From Date : "+this.getFromDate()+" to Date: "+this.getToDate()+" "+time)
															        .horizontalAlignment(CENTER).borderWidth(0).lineSpacing(0.5F).build())				               
											                .backgroundColor(WHITE)
											                .textColor(Color.BLACK)
											                .font(PDType1Font.HELVETICA_BOLD).fontSize(12)				              
											                .horizontalAlignment(CENTER)
											                .build();
										         
										         
										         
										         
										         tableHeaderBuilderOtherPage.addRow(dairyNameHeaderOtherPage);
										         tableHeaderBuilderOtherPage.addRow(addressRowOtherPage);
										         tableHeaderBuilderOtherPage.addRow(reportInfo1OtherPage);
										        
										         
										         headerOtherPage = tableHeaderBuilderOtherPage.build();
										         
										         TableDrawer.builder()
									                .contentStream(contentStream)
									                .table(headerOtherPage)
									                .startX(PADDING30)
									                .startY(startY)
									                .build()
									                .draw();
										         
										         
									         
									            
												
									         }
						        			 
									         
									         Row collectionTableInfoRow = Row.builder()
										                .add(TextCell.builder().text(""+srNo).horizontalAlignment(CENTER).borderWidth(0.5F).lineSpacing(0.5F).build())				               
										                .add(TextCell.builder().text(detailsDAO.getProductName()).fontSize(9).horizontalAlignment(LEFT).borderWidth(0.5F).lineSpacing(0.5F).build())
										                .add(TextCell.builder().text(detailsDAO.getQuantity()+" "+detailsDAO.getUnits()).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.5F).build())
										                .add(TextCell.builder().text(df.format(detailsDAO.getRate())).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.5F).build())
										                .add(TextCell.builder().text(detailsDAO.getTotalAmount()).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.5F).build())
										                .backgroundColor(Color.WHITE)
										                .textColor(Color.BLACK)
										                .font(PDType1Font.HELVETICA).fontSize(12)				              
										                .horizontalAlignment(CENTER)
										                .height(25F)
										                .build();
									         
								        	 tableStockInfoRowBuilder.addRow(collectionTableInfoRow);
								        	  currentY = (float) (currentY + 25.0F);
									         
									         srNo++;
									         
						        		 }
						        		 
						        		 
								         Row totalTableInfoRow = Row.builder()
									                .add(TextCell.builder().text("Total").colSpan(4).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.5F).build())				               
									                .add(TextCell.builder().text(df.format(totalPuchaseAmt)).horizontalAlignment(RIGHT).borderWidth(0.5F).lineSpacing(0.5F).build())
									                .backgroundColor(Color.WHITE)
									                .textColor(Color.BLACK)
									                .font(PDType1Font.HELVETICA_BOLD).fontSize(12)				              
									                .horizontalAlignment(CENTER)
									                .height(25F)
									                .build();
								         
							        	 tableStockInfoRowBuilder.addRow(totalTableInfoRow);
						        		   
							        	 stockInfoRowTable = tableStockInfoRowBuilder.build();
								         
								         
								        // float currentY = 
								         TableDrawer.builder()
							                .contentStream(contentStream)
							                .table(stockInfoRowTable)
							                .startX(PADDING20)
							                .startY(startY-63)
							                .build()
							                .draw();
								       
						        		 
						        		 contentStream.close();
										 	document.save(path+"/purchase-report.pdf");
									        document.close();
									        
									        

											response.setHeader("Content-disposition", "inline; filename=purchase-report.pdf");
											response.setContentType("application/pdf");
											FileInputStream fileInputStream = new FileInputStream(path+"/purchase-report.pdf");
											PrintWriter out = response.getWriter();  
											int bytes;
											while ((bytes = fileInputStream.read()) != -1) {
												out.write(bytes);
											}
											fileInputStream.close();
											out.flush();
											out.close();
						
							
		        	}catch (Exception 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) {}
						}
					
					}
		
				}
		   }catch (SQLException 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) {}
				}
			
			}
}
	
		public void setSession(Map<String, Object> map) {  
		    sessionMap=(SessionMap<String, Object>)map;  
		}

		public SessionMap<String, Object> getSessionMap() {
			return sessionMap;
		}

		public void setSessionMap(SessionMap<String, Object> sessionMap) {
			this.sessionMap = sessionMap;
		}
		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 void setServletResponse(HttpServletResponse httpServletResponse) {
			this.response = httpServletResponse;
		}

		
		public void setServletRequest(HttpServletRequest httpServletRequest) {
			this.request = httpServletRequest;
		}

		
	

		public List<ItemwisePurchaseReportDAO> getItemWisePurchaseList() {
			return itemWisePurchaseList;
		}

		public void setItemWisePurchaseList(List<ItemwisePurchaseReportDAO> itemWisePurchaseList) {
			this.itemWisePurchaseList = itemWisePurchaseList;
		}

		public static long getSerialversionuid() {
			return serialVersionUID;
		}

		public String getFromDate() {
			return fromDate;
		}

		public void setFromDate(String fromDate) {
			this.fromDate = fromDate;
		}

		public String getToDate() {
			return toDate;
		}

		public void setToDate(String toDate) {
			this.toDate = toDate;
		}
		
		
	}
