D-Net

package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.BankAccount;

import eu.dnetlib.goldoa.domain.Budget;

import eu.dnetlib.goldoa.domain.BudgetComment;

import eu.dnetlib.goldoa.domain.Comment;

import eu.dnetlib.goldoa.domain.Currency;

import org.apache.commons.codec.digest.DigestUtils;

import org.apache.commons.io.IOUtils;

import org.hibernate.criterion.Order;

import org.hibernate.criterion.Restrictions;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.dao.EmptyResultDataAccessException;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.PreparedStatementCreator;

import org.springframework.jdbc.core.PreparedStatementSetter;

import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;

import java.io.ByteArrayOutputStream;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import java.util.UUID;

/**

 * Created by antleb on 4/2/15.

 */

public class BudgetDAO extends AbstractDao<String, Budget>{

	@Autowired

	private DataSource dataSource;

	private static final String GET_BUDGET = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b where id=?";

	private static final String GET_BUDGETS = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b order by date";

	private static final String UPDATE_BUDGET = "update budget set date=?, startdate=?, \"enddate\"=?, amount_requested=?, amount_granted=?, remaining=?, currency=?, status=?, \"user\"=?, bank_name=?, bank_address=?, bank_code=?, bank_holder=?, bank_iban=?, transfer_cost=?, other_cost=?, date_paid=?, organization=?, publisher=?, invoice=? where id=?";

	private static final String INSERT_BUDGET = "insert into budget (date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, \"user\", bank_name, bank_address, bank_code, bank_holder, bank_iban, transfer_cost, other_cost, date_paid, organization, publisher, invoice, id) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private static final String GET_USER_BUDGETS = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b where \"user\"=? order by date";

	private static final String GET_ORGANIZATION_BUDGETS = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b where array[organization]::text[] <@ ?::text[] order by date";

	private static final String GET_PUBLISHER_BUDGETS = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b where publisher=? order by date";

	private static final String GET_ACCOUNTING_BUDGETS = "select id, date, startdate, \"enddate\", amount_requested, amount_granted, remaining, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, \"user\", transfer_cost, other_cost, date_paid, organization, publisher, invoice from budget b order by date";

	private static final String APPROVE_BUDGET = "update budget set status = ?, amount_granted=?, remaining=? where id=?";

	private static final String UPDATE_BUDGET_STATUS = "update budget set status = ?  where id=?";

	private static final String UPDATE_BUDGET_TERMS = "update budget set termsmimetype=?, termsfile=? where id=?";

	private static final String INSERT_BUDGET_TERMS = "insert into budget (termsmimetype, termsfile, id) values (?,?,?)";

//	private static final String GET_BUDGET_FILE = "select termsmimetype, termsfile from budget where id=?";

	private RowMapper<Budget> budgetRowMapper = new RowMapper<Budget>() {

		@Override

		public Budget mapRow(ResultSet rs, int rowNum) throws SQLException {

			BankAccount bankAccount = new BankAccount(rs.getString("bank_name"), rs.getString("bank_address"), rs.getString("bank_code"),

					rs.getString("bank_holder"), rs.getString("bank_iban"));

			Budget budget = new Budget(rs.getString("id"),  rs.getTimestamp("date"), rs.getTimestamp("startdate"),

					rs.getTimestamp("enddate"), rs.getFloat("amount_requested"), rs.getFloat("amount_granted"),

					Currency.valueOf(rs.getString("currency")), rs.getFloat("remaining"), rs.getInt("status"),

					rs.getString("user"), rs.getString("organization"), rs.getString("publisher"), bankAccount,

					rs.getFloat("transfer_cost"), rs.getFloat("other_cost"), rs.getTimestamp("date_paid"), rs.getString("invoice"));

			rs.getFloat("amount_requested");

			if (rs.wasNull())

				budget.setAmountRequested(null);

			rs.getFloat("amount_granted");

			if (rs.wasNull())

				budget.setAmountGranted(null);

			rs.getFloat("remaining");

			if (rs.wasNull())

				budget.setRemaining(null);

			rs.getFloat("transfer_cost");

			if (rs.wasNull())

				budget.setTransfer_cost(null);

			rs.getFloat("other_cost");

			if (rs.wasNull())

				budget.setOther_cost(null);

			return budget;

		}

	};

	public Budget saveBudget(Budget budget) {

		persist(budget);

		return budget;

	}

	public Budget getBudget(String budgetId) {

		return getByKey(budgetId);

	}

	@SuppressWarnings("unchecked")

	public List<Budget> getBudgets() {

		return createEntityCriteria().addOrder(Order.asc("date")).list();

	}

	@SuppressWarnings("unchecked")

	public List<Budget> getBudgetsForUser(String user_email) {

		return createEntityCriteria().add(Restrictions.ilike("user_email", user_email))

				.addOrder(Order.asc("date")).list();

	}

	public List<Budget> getBudgetsForOrganization(final List<String> organizationIds) {

		List<Budget> rs = new ArrayList<>();

		for(String orgID : organizationIds){

			rs.add((Budget) createEntityCriteria().add(Restrictions.ilike("organization", orgID))

					.addOrder(Order.asc("date")).list());

		}

		return rs;

	}

	@SuppressWarnings("unchecked")

	public List<Budget> getBudgetsForPublisher(String publisherId) {

		return createEntityCriteria().add(Restrictions.ilike("publisher", publisherId))

				.addOrder(Order.asc("date")).list();

	}

	@SuppressWarnings("unchecked")

	public List<Budget> getBudgetsForAccounting() {

		return createEntityCriteria().addOrder(Order.asc("date")).list();

	}

	public void initiallyApproveBudget(String budgetId) {

		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.INITIALLY_APPROVED.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});

	}

	public void approveBudget(String budgetId, float amountGranted) {

		new JdbcTemplate(dataSource).update(APPROVE_BUDGET, new Object[]{Budget.Status.APPROVED.getCode(), amountGranted, amountGranted, budgetId}, new int[]{Types.INTEGER, Types.REAL, Types.REAL, Types.VARCHAR});

	}

	public void rejectBudget(String budgetId) {

		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.REJECTED.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});

	}

	public void processingBudget(String budgetId) {

		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.ACCOUNTING_PROCESSING.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});

	}

	public void accountingDeniedBudget(String budgetId) {

		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.ACCOUNTING_DENIED.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});

	}

	public void accountingPaidBudget(String budgetId) {

		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.ACCOUNTING_PAID.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});

	}

	public void accountingOnHoldBudget(String budgetId) {

		new JdbcTemplate(dataSource).update(UPDATE_BUDGET_STATUS, new Object[]{Budget.Status.ACCOUNTING_ONHOLD.getCode(), budgetId}, new int[]{Types.INTEGER, Types.VARCHAR});

	}

	public void uploadTerms(final String id, final String contentType, InputStream inputStream) {

		try {

			final ByteArrayOutputStream baos = new ByteArrayOutputStream();

			final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

			IOUtils.copy(inputStream, baos);

			IOUtils.closeQuietly(baos);

			final PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override

				public void setValues(PreparedStatement ps) throws SQLException {

					ps.setString(1, contentType);

					ps.setBytes(2, baos.toByteArray());

					ps.setString(3, id);

				}

			};

			if (jdbcTemplate.update(UPDATE_BUDGET_TERMS, pss) == 0) {

				jdbcTemplate.update(INSERT_BUDGET_TERMS, pss);

			}

		} catch (Exception e) {

			e.printStackTrace();

		}

	}

	public void saveComment(String budgetId, String personId, String comment, String template) {

		String commentId = "portal::" + DigestUtils.md5Hex(comment + new Date());

		new JdbcTemplate(dataSource).update("with comm as ( insert into comment (id, person, comment, date, template) values (?, ?, ?, ?, ?) returning id) insert into budget_comment (budget, comment) select ?, comm.id from comm",

				new Object[]{commentId, personId, comment, new Date(), template, budgetId}, new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR});

	}

	public List<Comment> getBudgetComments(String budgetId) {

//		try {

//			return new JdbcTemplate(dataSource).query("select c.comment, c.date, c.person, c.template from comment c join budget_comment bc on bc.comment=c.id and bc.budget=? order by c.date asc", new String[]{budgetId}, new int[]{Types.VARCHAR}, new RowMapper<Comment>() {

//				@Override

//				public Comment mapRow(ResultSet rs, int rowNum) throws SQLException {

//					return new Comment(new Person(rs.getString("person")), rs.getTimestamp("date"), rs.getString("comment"), rs.getString("template"));

//				}

//			});

//		} catch (EmptyResultDataAccessException e) {

//			return null;

//		}

	}

	public void uploadBankReceipt(final String budgetId, final String contentType, InputStream inputStream) {

		try {

			final ByteArrayOutputStream baos = new ByteArrayOutputStream();

			final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

			IOUtils.copy(inputStream, baos);

			IOUtils.closeQuietly(baos);

			final PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override

				public void setValues(PreparedStatement ps) throws SQLException {

					ps.setString(1, contentType);

					ps.setBytes(2, baos.toByteArray());

					ps.setString(3, budgetId);

				}

			};

			jdbcTemplate.update("update budget set bank_receipt_contenttype=?, bank_receipt=? where id=?", pss);

		} catch (Exception e) {

			e.printStackTrace();

		}

	}

	public void uploadInitialContract(final String budgetId, final String contentType, InputStream inputStream) {

		try {

			final ByteArrayOutputStream baos = new ByteArrayOutputStream();

			final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

			final String id = "portal::" + UUID.randomUUID().toString();

			IOUtils.copy(inputStream, baos);

			IOUtils.closeQuietly(baos);

			final PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override

				public void setValues(PreparedStatement ps) throws SQLException {

					ps.setString(1, id);

					ps.setString(2, contentType);

					ps.setBytes(3, baos.toByteArray());

					ps.setString(4, budgetId);

				}

			};

			jdbcTemplate.update("with con as (insert into budgetfile (id, contenttype, content) values (?,?,?) returning id) update budget b set initialcontractfile=con.id where b.id=?", pss);

		} catch (Exception e) {

			e.printStackTrace();

		}

	}

	public void uploadBeneficiaryContract(final String budgetId, final String contentType, InputStream inputStream) {

		try {

			final ByteArrayOutputStream baos = new ByteArrayOutputStream();

			final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

			final String id = "portal::" + UUID.randomUUID().toString();

			IOUtils.copy(inputStream, baos);

			IOUtils.closeQuietly(baos);

			final PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override

				public void setValues(PreparedStatement ps) throws SQLException {

					ps.setString(1, id);

					ps.setString(2, contentType);

					ps.setBytes(3, baos.toByteArray());

					ps.setString(4, budgetId);

				}

			};

			jdbcTemplate.update("with con as (insert into budgetfile (id, contenttype, content) values (?,?,?) returning id) update budget b set beneficiarysignedcontract=con.id where b.id=?", pss);

		} catch (Exception e) {

			e.printStackTrace();

		}

	}

	public void uploadSignedContract(final String budgetId, final String contentType, InputStream inputStream) {

		try {

			final ByteArrayOutputStream baos = new ByteArrayOutputStream();

			final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

			final String id = "portal::" + UUID.randomUUID().toString();

			IOUtils.copy(inputStream, baos);

			IOUtils.closeQuietly(baos);

			final PreparedStatementSetter pss = new PreparedStatementSetter() {

				@Override

				public void setValues(PreparedStatement ps) throws SQLException {

					ps.setString(1, id);

					ps.setString(2, contentType);

					ps.setBytes(3, baos.toByteArray());

					ps.setString(4, budgetId);

				}

			};

			jdbcTemplate.update("with con as (insert into budgetfile (id, contenttype, content) values (?,?,?) returning id) update budget b set signedcontract=con.id where b.id=?", pss);

		} catch (Exception e) {

			e.printStackTrace();

		}

	}

	public int getRequestId() {

		return new JdbcTemplate(dataSource).queryForObject("select nextval('budget_id_seq') as id", new RowMapper<Integer>() {

			@Override

			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {

				return rs.getInt("id");

			}

		});

	}

}