package com.reallinkbilling.ajax;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.SessionAware;

import com.opensymphony.xwork2.ActionSupport;
import com.reallinkbilling.db.GetDBConnection;

public class GetSalesmanCommissionSummary extends ActionSupport implements SessionAware {

	private static final long serialVersionUID = 110022334455667788L;

	private SessionMap<String, Object> sessionMap;

	private String fromDate;
	private String toDate;
	private String salesmanName;

	private List<Map<String, String>> commissionList =
			new ArrayList<Map<String, String>>();

	/* =========================================================
	   GRAND TOTAL COMMISSION
	   ========================================================= */
	private String totalCommission = "0.00";

	public String execute() {

		String result = SUCCESS;

		if (this.getSessionMap() == null
				|| this.getSessionMap().get("login") == null) {
			return SUCCESS;
		}

		String shopId = (String) this.getSessionMap().get("shopId");
		String shopCode = (String) this.getSessionMap().get("shopCode");
		String db = (String) this.getSessionMap().get("db");

		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		/* =========================================================
		   TOTAL VARIABLE
		   ========================================================= */
		float grandTotalCommission = 0.00f;

		try {

			connection = GetDBConnection.getDBConnection(db);

			String sql =
					"SELECT s.salesman_id, s.salesman_name, "
					+ "IFNULL(SUM(c.commission_amount),0) AS total_commission "
					+ "FROM shop_salesman_master s "
					+ "LEFT JOIN shop_salesman_commission c "
					+ "ON c.employee_id = s.salesman_id "
					+ "AND c.shop_id = s.shop_id "
					+ "AND c.shop_code = s.shop_code "
					+ "AND c.bill_date BETWEEN "
					+ "STR_TO_DATE(?,'%d-%m-%Y') "
					+ "AND STR_TO_DATE(?,'%d-%m-%Y') "
					+ "WHERE s.shop_id = ? "
					+ "AND s.shop_code = ? "
					+ "AND s.active = 'Y' ";

			/* =====================================================
			   SEARCH BY SALESMAN NAME
			   ===================================================== */
			if (salesmanName != null
					&& !salesmanName.trim().isEmpty()) {

				sql += "AND s.salesman_name LIKE ? ";
			}

			sql +=
					"GROUP BY s.salesman_id, s.salesman_name "
					+ "ORDER BY s.salesman_name";

			ps = connection.prepareStatement(sql);

			int index = 1;

			ps.setString(index++, this.getFromDate());
			ps.setString(index++, this.getToDate());
			ps.setString(index++, shopId);
			ps.setString(index++, shopCode);

			if (salesmanName != null
					&& !salesmanName.trim().isEmpty()) {

				ps.setString(
						index++,
						"%" + salesmanName.trim() + "%"
				);
			}

			rs = ps.executeQuery();

			while (rs.next()) {

				Map<String, String> row =
						new HashMap<String, String>();

				int salesmanId =
						rs.getInt("salesman_id");

				String salesmanNameValue =
						rs.getString("salesman_name");

				float salesmanCommission =
						rs.getFloat("total_commission");

				row.put(
						"salesmanId",
						String.valueOf(salesmanId)
				);

				row.put(
						"salesmanName",
						salesmanNameValue
				);

				row.put(
						"totalCommission",
						String.format(
								"%.2f",
								salesmanCommission
						)
				);

				this.getCommissionList().add(row);

				/* =================================================
				   ADD EACH SALESMAN COMMISSION TO GRAND TOTAL
				   ================================================= */
				grandTotalCommission =
						grandTotalCommission
						+ salesmanCommission;
			}

			/* =====================================================
			   SET GRAND TOTAL
			   ===================================================== */
			this.setTotalCommission(
					String.format(
							"%.2f",
							grandTotalCommission
					)
			);

		} catch (SQLException | IOException e) {

			e.printStackTrace();

		} finally {

			try {
				if (rs != null)
					rs.close();
			} catch (Exception ig) {
			}

			try {
				if (ps != null)
					ps.close();
			} catch (Exception ig) {
			}

			try {
				if (connection != null)
					connection.close();
			} catch (Exception ig) {
			}
		}

		return result;
	}

	public void setSession(Map<String, Object> map) {

		sessionMap =
				(SessionMap<String, Object>) map;
	}

	public SessionMap<String, Object> getSessionMap() {

		return sessionMap;
	}

	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 getSalesmanName() {

		return salesmanName;
	}

	public void setSalesmanName(String salesmanName) {

		this.salesmanName = salesmanName;
	}

	public List<Map<String, String>> getCommissionList() {

		return commissionList;
	}

	public void setCommissionList(
			List<Map<String, String>> commissionList) {

		this.commissionList = commissionList;
	}

	/* =========================================================
	   TOTAL COMMISSION GETTER / SETTER
	   ========================================================= */

	public String getTotalCommission() {

		return totalCommission;
	}

	public void setTotalCommission(String totalCommission) {

		this.totalCommission = totalCommission;
	}
}