package com.reallinkbilling.delete;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map;

import org.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.SessionAware;

import com.reallinkbilling.DAO.BillProductInfoDAO;
import com.reallinkbilling.cipher.EncryptionDecryption;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.update.UpdateProductStockDetails;
import com.reallinkbilling.utils.MaintainCloseingBalUtil;
import com.opensymphony.xwork2.ActionSupport;

public class DeleteBill extends ActionSupport implements SessionAware{

	/**
	 * 
	 */
	private static final long serialVersionUID = 6208411094853732491L;
	private SessionMap<String,Object> sessionMap; 
	private String billNumber;
	private String typeOfBill;
	private String fromDate;
	private String toDate;
	private String key;
	private String custName;
	private String custMobile;
	private String dateAsStringType2;
	
	public String execute() {
		 
		String result = "login";
	
		
	        if(this.getSessionMap()==null || this.getSessionMap().get("login")==null){ 
	        	addActionMessage("Please Logout and Login again !");
	        	result = "login";
	        }  
	        else{
	        	
	        	Connection connection = null;
				PreparedStatement preparedStatement= null;
				ResultSet resultSet = null;
			
				String shopId = (String) this.getSessionMap().get("shopId");
	    		String shopCode = (String) this.getSessionMap().get("shopCode");
	        	
	    		  String fullUserName = (String) this.getSessionMap().get("userFullName");
		        	
		        	String splitUserName [] = new String[5];
		        	String deletedBy = "";
		        	try
		        	{
		        		splitUserName = fullUserName.split(" ");
		        		deletedBy = splitUserName[0];
		        		
		        	}catch(Exception e)
		        	{
		        		deletedBy =fullUserName;
		        	}
		        	
		        	java.util.Date date = new java.util.Date();
		      	      long t = date.getTime();	        	     
		      	      java.sql.Time time = new java.sql.Time(t);
						
			
	    		String custId = EncryptionDecryption.decrypt(getKey());	        	
	        	setKey(EncryptionDecryption.encrypt(custId));
	        	
				String sql = "";
				String db = (String)  this.getSessionMap().get("db");
				
				try
				{
					connection = GetDBConnection.getDBConnection(db);
					connection.setAutoCommit(false);
					
					sql = "SELECT "
    						+ "account_credit_debit_id "
							+ "FROM "
							+ "shop_bill_info_master "
							+ "WHERE "
    						+ "bill_number = ? "
    						+ "AND "
    						+ "shop_id = ? "
							+ "AND "
							+ "shop_code = ? ";
					
					preparedStatement = connection.prepareStatement(sql);
					
					preparedStatement.setString(1, this.getBillNumber());
    				preparedStatement.setString(2, shopId);
    				preparedStatement.setString(3, shopCode);
    				
					resultSet = preparedStatement.executeQuery();
					
					 boolean isCloseingBalMaintained = false;
					   
					if(resultSet.next()){
						String accCrditDebitId = resultSet.getString(1);
						
						if(resultSet != null)
						{
							resultSet.close();
							resultSet = null;
						}
						if(preparedStatement != null)
						{
							preparedStatement.close();
							preparedStatement = null;
						}
                       
						if(accCrditDebitId != null && !accCrditDebitId.equals("") )
						{
							
						
						sql = "SELECT "
								+ "entry_id,"
								+ "account_id,"
								+ "account_number,"
								+ "entry_date,"
								+ "entry_time,"
								+ "entry_type,"
								+ "amount,"
								+ "payment_type,"
								+ "entry_by,"
								+ "entry_details "
								+ "FROM "
								+ "bank_account_credit_debit_master "
								+ "WHERE "
								+ "entry_id = ? "
								+ "AND "
								+ "shop_id = ? "
								+ "AND "
								+ "shop_code = ? ";
								
						
						preparedStatement = connection.prepareStatement(sql);
						preparedStatement.setString(1, accCrditDebitId);
						preparedStatement.setString(2, shopId);
						preparedStatement.setString(3, shopCode);
						
						resultSet = preparedStatement.executeQuery();
						
						int m = 0;
						String paymentType = null;
						String entryDate1 = null;
						String accountId = null;
						Float amount = null;
						
						if(resultSet.next())
						{
							accountId = resultSet.getString(2);
						    paymentType = resultSet.getString(6);
							 entryDate1 = resultSet.getString(4);
							 amount = resultSet.getFloat(7);
							 
							PreparedStatement  insertIntoDeletedPSMT = null;
							
							String inertDeletedSQL = "INSERT "
									+ "INTO "
									+ "bank_account_credit_debit_deleted_entry_master "
									+ "("
									+ "entry_id,"
									+ "shop_id,"
			        				+ "shop_code,"
			        				+ "account_id,"
									+ "account_number,"
									+ "entry_date,"
									+ "entry_time,"
									+ "entry_type,"
									+ "amount,"
									+ "payment_type,"
									+ "entry_by,"
									+ "entry_details) "
									+ "VALUES "
									+ "(?,?,?,?,?,?,?,?,?,?,?,?)";
							
							insertIntoDeletedPSMT = connection.prepareStatement(inertDeletedSQL);
							
							insertIntoDeletedPSMT.setString(1, accCrditDebitId);
							insertIntoDeletedPSMT.setString(2, shopId);
							insertIntoDeletedPSMT.setString(3, shopCode);
			        		insertIntoDeletedPSMT.setString(4, resultSet.getString(2));
							insertIntoDeletedPSMT.setString(5, resultSet.getString(3));
							insertIntoDeletedPSMT.setString(6, resultSet.getString(4));
							insertIntoDeletedPSMT.setTime(7, time);
							insertIntoDeletedPSMT.setString(8, resultSet.getString(6));
							insertIntoDeletedPSMT.setFloat(9, resultSet.getFloat(7));
							insertIntoDeletedPSMT.setString(10, resultSet.getString(8));
							insertIntoDeletedPSMT.setString(11, deletedBy);
							insertIntoDeletedPSMT.setString(12, resultSet.getString(10));
							
							  m = insertIntoDeletedPSMT.executeUpdate();
							
							if(insertIntoDeletedPSMT != null)
							{
								insertIntoDeletedPSMT.close();
								insertIntoDeletedPSMT = null;
							}
							
						}
						
						
						if(resultSet != null)
						{
							resultSet.close();
							resultSet = null;
						}
						if(preparedStatement != null)
						{
							preparedStatement.close();
							preparedStatement = null;
						}
						
						if(m>0)
						{
							

						
						sql = "DELETE "
								+ "FROM "
								+ "bank_account_credit_debit_master "
								+ "WHERE "
								+ "entry_id = ? "
								+ "AND "
								+ "shop_id = ? "
								+ "AND "
								+ "shop_code = ? ";
						
						preparedStatement = connection.prepareStatement(sql);
						preparedStatement.setString(1, accCrditDebitId);
						preparedStatement.setString(2, shopId);
						preparedStatement.setString(3, shopCode);
						
						int k = preparedStatement.executeUpdate();
						
						if(k>0  )
						{
							
							SimpleDateFormat inputDateFormat = new SimpleDateFormat("yyyy-MM-dd");
							Date parsedDate = null;

							try {
							    parsedDate = inputDateFormat.parse(entryDate1); 
							} catch (ParseException e) {
							    e.printStackTrace();  
							}

							if (parsedDate != null) {
							     SimpleDateFormat outputDateFormat = new SimpleDateFormat("dd-MM-yyyy");
							    String formattedDate = outputDateFormat.format(parsedDate); 
							   
							    if (paymentType.equals("CREDIT")) {
							    	isCloseingBalMaintained = MaintainCloseingBalUtil.maintainCloseingBal(accountId, "DEBIT", formattedDate, amount, deletedBy, connection, this.getSessionMap());
							    } else {
							    	isCloseingBalMaintained = MaintainCloseingBalUtil.maintainCloseingBal(accountId, "CREDIT", formattedDate, amount, deletedBy, connection, this.getSessionMap());
							    }
							}
					
							
		    			}
		    		}
		    			}
						
						
						
					}
					
					
					sql = "SELECT "
	       					+ "customer_name,"
	       					+ "mobile_number "
	       					+ "FROM "
	       					+ "shop_customer_master "
	       					+ "where "
	       					+ "shop_id = ? "
	       					+ "AND "
	       					+ "shop_code = ? "
	       					+ "AND "
	       					+ "customer_id = ?";
	       			
	       			
	       			preparedStatement = connection.prepareStatement(sql);
	       			preparedStatement.setString(1, shopId);
	       			preparedStatement.setString(2, shopCode);
	       			preparedStatement.setString(3, custId);
	       			
	       			resultSet = preparedStatement.executeQuery();
	       			
	       			resultSet.last();
	       			int rowCount = resultSet.getRow();
	       			resultSet.beforeFirst();
	       			
	       			
	       			if(rowCount>0)
	       			{
	       				result = "success";
		       			while(resultSet.next())
		       			{
		       				
		       				this.setCustName(resultSet.getString(1));
		       				this.setCustMobile(resultSet.getString(2));					
		       				
		       				
		       			}
	       			}
	       			else
	       			{
	       				result = "error";
	       				addActionError("Details Not Fount !");
	       			}
	       			
	       			if(resultSet != null)
	       			{
	       				resultSet.close();
	       				resultSet = null;
	       			}
	       			
	       			if(preparedStatement != null)
	       			{
	       				preparedStatement.close();
	       				preparedStatement = null;
	       			}
					
					sql = "SELECT "
							+ "product_id,"
							+ "product_name,"
							+ "sales_unit,"
							+ "sales_rate,"
							+ "product_quantity "
							+ "FROM "
							+ "shop_bill_product_info_master "
							+ "WHERE "
							+ "shop_id = ? "
							+ "AND "
							+ "shop_code = ? "
							+ "AND "
							+ "bill_number = ? "
							+ "AND "
							+ "bill_type = ? "
							+ "AND "
							+ "bill_date = STR_TO_DATE(?,'%d-%m-%Y') ";
					
					preparedStatement = connection.prepareStatement(sql);
					
					preparedStatement.setString(1, shopId);
					preparedStatement.setString(2, shopCode);
					preparedStatement.setString(3, this.getBillNumber());
					preparedStatement.setString(4, this.getTypeOfBill());
					preparedStatement.setString(5, this.getDateAsStringType2());
					
					//System.out.println(preparedStatement.toString());
					resultSet = preparedStatement.executeQuery();
					resultSet.last();
					int noOfRows = resultSet.getRow();
					resultSet.beforeFirst();
					
					ArrayList<BillProductInfoDAO> productList = new ArrayList<BillProductInfoDAO>();
					
					if(noOfRows>0)
					{
						
						while (resultSet.next()) 
						{
							
							BillProductInfoDAO infoDAO = new BillProductInfoDAO();
							infoDAO.setProductId(resultSet.getString(1));
							infoDAO.setProductName(resultSet.getString(2));
							infoDAO.setSalesUnit(resultSet.getString(3));
							infoDAO.setSalesRate(resultSet.getFloat(4));
							infoDAO.setQuantity(resultSet.getFloat(5));
							
							productList.add(infoDAO);
						}
						
						int productCount = productList.size();
						boolean stockAdded = true;
						int productQuantityUpdatedCount = 0;
						
						for(BillProductInfoDAO infoDAO : productList)
						{
							stockAdded = UpdateProductStockDetails.addStockToCurrentStock(connection, this.getSessionMap(), infoDAO.getProductId(), infoDAO.getProductName(), infoDAO.getQuantity());
							 
							if(stockAdded)
							{
								productQuantityUpdatedCount = productQuantityUpdatedCount + 1;
							}
						}
						
						
						if(stockAdded && (productQuantityUpdatedCount==productCount))
						{
							
							if(preparedStatement != null)
							{
								preparedStatement.close();
								preparedStatement = null;
							}
							
							sql = "DELETE "
									+ "FROM "
									+ "shop_bill_product_info_master "
									+ "WHERE "
									+ "shop_id = ? "
									+ "AND "
									+ "shop_code = ? "
									+ "AND "
									+ "bill_number = ? "
									+ "AND "
									+ "bill_type = ? "
									+ "AND "
									+ "bill_date = STR_TO_DATE(?,'%d-%m-%Y') ";
							
							preparedStatement = connection.prepareStatement(sql);
							
							preparedStatement.setString(1, shopId);
							preparedStatement.setString(2, shopCode);
							preparedStatement.setString(3, this.getBillNumber());
							preparedStatement.setString(4, this.getTypeOfBill());
							preparedStatement.setString(5, this.getDateAsStringType2());
								
							int rowCountDeleted = preparedStatement.executeUpdate();
							
							
							if(rowCountDeleted > 0)
							{
								if(preparedStatement != null)
								{
									preparedStatement.close();
									preparedStatement = null;
								}

								// For composite bills, reverse stock of sub products as well.
								if("COMPOSITE-BILL".equals(this.getTypeOfBill()))
								{
									if(resultSet != null)
									{
										resultSet.close();
										resultSet = null;
									}
									
									sql = "SELECT "
											+ "product_id,"
											+ "product_quantity,"
											+ "product_quantity_2 "
											+ "FROM "
											+ "shop_bill_sub_product_info_master "
											+ "WHERE "
											+ "shop_id = ? "
											+ "AND "
											+ "shop_code = ? "
											+ "AND "
											+ "bill_number = ? "
											+ "AND "
											+ "bill_type = ? "
											+ "AND "
											+ "bill_date = STR_TO_DATE(?,'%d-%m-%Y') ";
									
									preparedStatement = connection.prepareStatement(sql);
									preparedStatement.setString(1, shopId);
									preparedStatement.setString(2, shopCode);
									preparedStatement.setString(3, this.getBillNumber());
									preparedStatement.setString(4, this.getTypeOfBill());
									preparedStatement.setString(5, this.getDateAsStringType2());
									
									resultSet = preparedStatement.executeQuery();
									boolean subStockAdded = true;
									while(resultSet.next())
									{
										subStockAdded = UpdateProductStockDetails.addStockToCurrentStock(
												connection,
												this.getSessionMap(),
												resultSet.getString(1),
												resultSet.getFloat(2),
												resultSet.getFloat(3));
										if(!subStockAdded)
										{
											break;
										}
									}
									
									if(resultSet != null)
									{
										resultSet.close();
										resultSet = null;
									}
									if(preparedStatement != null)
									{
										preparedStatement.close();
										preparedStatement = null;
									}
									
									if(!subStockAdded)
									{
										connection.rollback();
										result = ERROR;
										addActionError("Something went Wrong! Error while reversing sub product stock!");
										return result;
									}
									
									sql = "DELETE "
											+ "FROM "
											+ "shop_bill_sub_product_info_master "
											+ "WHERE "
											+ "shop_id = ? "
											+ "AND "
											+ "shop_code = ? "
											+ "AND "
											+ "bill_number = ? "
											+ "AND "
											+ "bill_type = ? "
											+ "AND "
											+ "bill_date = STR_TO_DATE(?,'%d-%m-%Y') ";
									
									preparedStatement = connection.prepareStatement(sql);
									preparedStatement.setString(1, shopId);
									preparedStatement.setString(2, shopCode);
									preparedStatement.setString(3, this.getBillNumber());
									preparedStatement.setString(4, this.getTypeOfBill());
									preparedStatement.setString(5, this.getDateAsStringType2());
									preparedStatement.executeUpdate();
									
									if(preparedStatement != null)
									{
										preparedStatement.close();
										preparedStatement = null;
									}
								}
								
								
                                sql = "DELETE FROM "
										+ "shop_bill_info_master "
										+ "WHERE "
										+ "shop_id = ? "
										+ "AND "
										+ "shop_code = ? "
										+ "AND "
										+ "bill_number = ? "
										+ "AND "
										+ "bill_type = ? "
										+ "AND "
										+ "bill_date = STR_TO_DATE(?,'%d-%m-%Y') ";
								
								preparedStatement = connection.prepareStatement(sql);
								
								preparedStatement.setString(1, shopId);
								preparedStatement.setString(2, shopCode);
								preparedStatement.setString(3, this.getBillNumber());
								preparedStatement.setString(4, this.getTypeOfBill());
								preparedStatement.setString(5, this.getDateAsStringType2());
								
							
								int billDeletedCount = preparedStatement.executeUpdate();
								
                                if(billDeletedCount > 0)
								{
                                    // Remove salesman commission entry for this bill (if exists)
                                    if(preparedStatement != null){ try{ preparedStatement.close(); preparedStatement = null; }catch(Exception ig){} }
                                    String delComm = "DELETE FROM shop_salesman_commission WHERE shop_id=? AND shop_code=? AND bill_number=? AND bill_date=STR_TO_DATE(?,'%d-%m-%Y') AND bill_type=?";
                                    preparedStatement = connection.prepareStatement(delComm);
                                    preparedStatement.setString(1, shopId);
                                    preparedStatement.setString(2, shopCode);
                                    preparedStatement.setString(3, this.getBillNumber());
                                    preparedStatement.setString(4, this.getDateAsStringType2());
                                    preparedStatement.setString(5, this.getTypeOfBill());
                                    try{ preparedStatement.executeUpdate(); }catch(Exception ig){}
                                    if(preparedStatement != null){ try{ preparedStatement.close(); preparedStatement = null; }catch(Exception ig){} }

									connection.commit();
									result = SUCCESS;
									
									addActionMessage("Bill Deleted Successfully!");
								}
								else
								{
									connection.rollback();
									result = ERROR;
									addActionError("Something went Wrong! Please try again! Error while bill delete!");
									
								}
								
								
							}
							else
							{
								connection.rollback();
								result = ERROR;
								addActionError("Something went Wrong! Please try again! Error while bill product delete!");
							}
							
							
						}
						else
						{
							connection.rollback();
							result = ERROR;
							addActionError("Something went Wrong! Please try again! Error while bill product qty update!");
						}
					}
					else
					{
						result = ERROR;
						addActionError("Details Not Found! Please try Again!");
					}
					
					
				}
				catch (SQLException e) {
					
					//e.printStackTrace();
					result = ERROR;
					
					addActionError(e.getErrorCode()+" || "+e.getMessage() +" || Something Went Wrong !");
				}
				catch (Exception e) {
					result = ERROR;
					
					addActionError(e.getMessage() +" || Something Went Wrong !");
				}
				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 result;
	}
	
	
	
	
	public String getBillNumber() {
		return billNumber;
	}




	public void setBillNumber(String billNumber) {
		this.billNumber = billNumber;
	}




	public String getTypeOfBill() {
		return typeOfBill;
	}




	public void setTypeOfBill(String typeOfBill) {
		this.typeOfBill = typeOfBill;
	}




	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;
	}




	public String getKey() {
		return key;
	}




	public void setKey(String key) {
		this.key = key;
	}




	public static long getSerialversionuid() {
		return serialVersionUID;
	}




	public String getCustName() {
		return custName;
	}




	public void setCustName(String custName) {
		this.custName = custName;
	}




	public String getCustMobile() {
		return custMobile;
	}




	public void setCustMobile(String custMobile) {
		this.custMobile = custMobile;
	}




	public String getDateAsStringType2() {
		return dateAsStringType2;
	}




	public void setDateAsStringType2(String dateAsStringType2) {
		this.dateAsStringType2 = dateAsStringType2;
	}




	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;  
	}
	

}



