package com.reallinkbilling.report;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

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.GSTPurchaseInvoiceInfoDAO;
import com.reallinkbilling.db.GetDBConnection;

public class GetGSTPurchaseReport extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 3502790352342908340L;
	private List<GSTPurchaseInvoiceInfoDAO> fromDateToGSTReportDAOList = new ArrayList<GSTPurchaseInvoiceInfoDAO>();
	
	private  HttpSession session;

	private String fromDate;
	private String toDate;
	private String reportFormat;

private ArrayList<String> reportFormatList = new ArrayList<String>();
	
	
	private SessionMap<String,Object> sessionMap;  


	public String execute() {
		
		String result = "login";
		  session=ServletActionContext.getRequest().getSession(false);  
	        if(session==null || session.getAttribute("login")==null){ 
	        	addActionMessage("Please Login First !!!");
	        	result= "login";  
	        }  
	        else
	        {
	        
	        session=ServletActionContext.getRequest().getSession(false);  
			Connection connection = null;
			PreparedStatement preparedStatement = null;
			ResultSet resultSet = null;
			String sql = "";
			
			String db = (String) session.getAttribute("db");
			
			
			  
			try {
				
				DecimalFormat df = new DecimalFormat("##.##");
				
				connection = GetDBConnection.getDBConnection(db);
				
				
				LinkedHashMap<String,GSTPurchaseInvoiceInfoDAO> purchaseInvoiceMap = new LinkedHashMap<String,GSTPurchaseInvoiceInfoDAO>();
				String shopId = (String) this.getSessionMap().get("shopId");
	    		String shopCode = (String) this.getSessionMap().get("shopCode");
	    		
				 sql = "SELECT "
					  		+ "pi.purchase_invoice_number,"
					  		+ "DATE_FORMAT(pi.purchase_date,'%d-%m-%Y'),"
					  		+ "pi.supplier_id,"
					  		+ "pi.supplier_name,"
					  		+ "supplier.supplier_address,"
					  		+ "supplier.gst_number,"
					  		+ "pi.total_purchase_amount,"
					  		+ "pi.transportation_charges,"
					  		+ "pi.tcs_amount "
					  		+ "FROM "
					  		+ "shop_purchase_info_master as pi "
					  		+ "INNER JOIN "
					  		+ "shop_supplier_master as supplier "
					  		+ "ON "
					  		+ "pi.supplier_id = supplier.supplier_id "
					  		+ "WHERE "
					  		+ "(pi.purchase_date  "
					  		+ "BETWEEN "
					  		+ "STR_TO_DATE(?,'%d-%m-%Y') "
					  		+ "AND "
					  		+ "STR_TO_DATE(?,'%d-%m-%Y')) "					  		
					  		+ "AND "
					  		+ "pi.shop_id = ? "
					  		+ "AND "
					  		+ "pi.shop_code = ? "
					  		+ "AND "
					  		+ "supplier.shop_id = ? "
					  		+ "AND "
					  		+ "supplier.shop_code = ? "
					  		+ "ORDER BY pi.purchase_date";
				  
				  preparedStatement = connection.prepareStatement(sql);
				  
				  preparedStatement.setString(1, this.getFromDate());
				  preparedStatement.setString(2, this.getToDate());
				  preparedStatement.setString(3, shopId);				  
				  preparedStatement.setString(4, shopCode);
				  preparedStatement.setString(5, shopId);				  
				  preparedStatement.setString(6, shopCode);
				   
				//  System.out.println(preparedStatement.toString());
				  
				  resultSet = preparedStatement.executeQuery();
				  
				  while(resultSet.next())
				  {
					  GSTPurchaseInvoiceInfoDAO dao = new GSTPurchaseInvoiceInfoDAO();
					
					  dao.setInvoiceNumber(resultSet.getString(1));
					  dao.setInvoiceDate(resultSet.getString(2));
					  dao.setSupplierId(resultSet.getString(3));
					  dao.setSupplierName(resultSet.getString(4));
					  dao.setSupllierAddress(resultSet.getString(5));
					  dao.setSupplierGSTNumber(resultSet.getString(6));
					  dao.setTotalAmount(resultSet.getString(7));
					  dao.setFrieghtCharges(resultSet.getFloat(8));
					  dao.setTcsAmount(resultSet.getFloat(9));
					  
					  purchaseInvoiceMap.put(resultSet.getString(1), dao);
				  }
				  
				  
				  if(resultSet != null)
				  {
					  resultSet.close();
					  resultSet = null;
				  }
				  
				  if(preparedStatement != null)
				  {
					  preparedStatement.close();
					  preparedStatement = null;
				  }
				  int srNo = 1;
				  for(String purchaseInvoiceNumber:purchaseInvoiceMap.keySet())
				  {
					  
					  String getProductInfoSQL = "";
					  PreparedStatement getProductInfoPSMT = null;
					  ResultSet getProductInfoRS = null;
					  
					  GSTPurchaseInvoiceInfoDAO dao = purchaseInvoiceMap.get(purchaseInvoiceNumber);
					  
					  getProductInfoSQL = "SELECT "
					  		+ "product_id,"
					  		+ "product_name,"
					  		+ "product_total_amount,"
					  		+ "puroduct_quantity,"
					  		+ "purchase_rate,"
					  		+ "product_net_amount,"
					  		+ "product_igst_amount,"
					  		+ "product_cgst_amount,"
					  		+ "product_sgst_amount "
					  		+ "FROM "
					  		+ "shop_purchase_product_info_master "
					  		+ "WHERE "
					  		+ "purchase_invoice_number = ? "
					  		+ "AND "
					  		+ "shop_id = ? "
					  		+ "AND "
					  		+ "shop_code = ? ";
					  
					  getProductInfoPSMT = connection.prepareStatement(getProductInfoSQL);
					  
					  getProductInfoPSMT.setString(1, purchaseInvoiceNumber);
					  getProductInfoPSMT.setString(2, shopId);				  
					  getProductInfoPSMT.setString(3, shopCode);
					  getProductInfoRS = getProductInfoPSMT.executeQuery();
					  
					  
					 @SuppressWarnings("unchecked")
					HashMap<String,String> productHSNcode = (HashMap<String, String>) this.getSessionMap().get("productHSNcode");
			
					 while(getProductInfoRS.next())
					  {
						 GSTPurchaseInvoiceInfoDAO dao2 = new GSTPurchaseInvoiceInfoDAO(); 
						  dao2.setInvoiceDate(dao.getInvoiceDate());
						  dao2.setInvoiceNumber(dao.getInvoiceNumber());
						  dao2.setSupplierName(dao.getSupplierName());
						  dao2.setSupplierGSTNumber(dao.getSupplierGSTNumber());
						  dao2.setSupllierAddress(dao.getSupllierAddress());
						  dao2.setSrNo(""+srNo);
						  dao2.setProductHSCSACCode(productHSNcode.get(getProductInfoRS.getString(1)));
						  dao2.setProductName(getProductInfoRS.getString(2));
						  dao2.setQuantity(getProductInfoRS.getString(4));
						  dao2.setRate(getProductInfoRS.getString(5));
						  dao2.setTaxableAmount(df.format(getProductInfoRS.getFloat(6)));
						  dao2.setIgst(df.format(getProductInfoRS.getFloat(7)));
						  dao2.setSgst(df.format(getProductInfoRS.getFloat(8)));
						  dao2.setCgst(df.format(getProductInfoRS.getFloat(9)));
						  dao2.setTcsAmount(dao.getTcsAmount());  
						  dao2.setFrieghtCharges(dao.getFrieghtCharges());
							
								 
							dao2.setTotalAmount(df.format(getProductInfoRS.getFloat(6)+ getProductInfoRS.getFloat(7) + getProductInfoRS.getFloat(8) + getProductInfoRS.getFloat(9)));
								  dao2.setGrandTotal(df.format( getProductInfoRS.getFloat(6) 
										  	+ getProductInfoRS.getFloat(7) 
										  	+ getProductInfoRS.getFloat(8) 
										  	+ getProductInfoRS.getFloat(9)
										  	+ dao2.getTcsAmount()+dao2.getFrieghtCharges()));
							  
							  
							  srNo++;
							  this.getFromDateToGSTReportDAOList().add(dao2);
							
							  
						  
					  }
					  
				
					
					  
					  if(getProductInfoRS != null)
					  {
						  getProductInfoRS.close();
						  getProductInfoRS = null;
					  }
					  
					  if(getProductInfoPSMT != null)
					  {
						  getProductInfoPSMT.close();
						  getProductInfoPSMT = null;
					  }
					  
					  
					  
				  }
				  
			
					  
					
		    			
					  result = SUCCESS;
				 
			}
			catch (SQLException e) {
				e.printStackTrace();
				result = "error";
				addActionError("Records not found for specified dates!");
				
				
			} catch (IOException e) {
			
				result="error";
				//e.printStackTrace();
				addActionError("Records not found for specified dates!");
			}finally
			{
				if(resultSet != null)
   				{
   					try {resultSet.close(); resultSet = null;}catch(SQLException sqe){}   					
   				}
				if(preparedStatement != null)
				{
					try {
						preparedStatement.close(); preparedStatement = null;
					} catch (SQLException e) {
						
					}
				}
				
				if(connection != null)
				{
					try {
						connection.close(); connection = null;
					} catch (SQLException e) {
						
					}
				}
				
				
			}
			
			
			
	        }
	        
	        return result;
}
	
	@Override
	public void validate() {
		if(this.getFromDate().equals("") && this.getToDate().equals(""))
		{
			addActionError("Please select dates!");
		}
		
		
		
    	this.getReportFormatList().add("EXCEL");
    	this.getReportFormatList().add("PDF");
    	
	}
	
	
	public void setSession(Map<String, Object> map) {
		// TODO Auto-generated method stub
		 sessionMap=(SessionMap<String, Object>)map;  
		
	}

	

	public SessionMap<String, Object> getSessionMap() {
		return sessionMap;
	}


	public void setSessionMap(SessionMap<String, Object> sessionMap) {
		this.sessionMap = sessionMap;
	}


	public HttpSession getSession() {
		return session;
	}


	public void setSession(HttpSession session) {
		this.session = session;
	}








	public List<GSTPurchaseInvoiceInfoDAO> getFromDateToGSTReportDAOList() {
		return fromDateToGSTReportDAOList;
	}

	public void setFromDateToGSTReportDAOList(List<GSTPurchaseInvoiceInfoDAO> fromDateToGSTReportDAOList) {
		this.fromDateToGSTReportDAOList = fromDateToGSTReportDAOList;
	}

	public String getFromDate() {
		return fromDate;
	}




	public void setFromDate(String fromDate) {
		this.fromDate = fromDate;
	}




	public ArrayList<String> getReportFormatList() {
		return reportFormatList;
	}

	public void setReportFormatList(ArrayList<String> reportFormatList) {
		this.reportFormatList = reportFormatList;
	}

	public String getToDate() {
		return toDate;
	}




	public void setToDate(String toDate) {
		this.toDate = toDate;
	}




	public String getReportFormat() {
		return reportFormat;
	}

	public void setReportFormat(String reportFormat) {
		this.reportFormat = reportFormat;
	}

	public static long getSerialversionuid() {
		return serialVersionUID;
	}

	
	
}


