com.quamto.jira.data.time.dao.UserConfigurationDAO Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of plugins-data Show documentation
The newest version!
package com.quamto.jira.data.time.dao;

import com.quamto.core.QException;
import com.quamto.db.DbConnection;
import com.quamto.db.SQLParameter;
import com.quamto.entity.BaseEntity;
import com.quamto.entity.BaseEntityDAO;

import com.quamto.jira.data.time.entity.UserConfigurationEntity;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * Created by QUAMTO-DEV3 on 28/07/2017.
 * [email protected]
 */
public class UserConfigurationDAO extends BaseEntityDAO{

    public UserConfigurationDAO(DbConnection dbConnection){
        super( dbConnection, "qji_user_configuration", "ucf_id", UserConfigurationDAO.class );
    }

    /**
     * Get an instance of the entity with data loaded according to Id provided
     */
    @Override
    public UserConfigurationEntity get(Long id) throws QException {
        UserConfigurationEntity entity = null;
        try {
            entity = (UserConfigurationEntity)getEntityLoaded(id);
        } catch (Exception e) {
            throw new QException(e, QException.ExceptionType.LoadData_err, subClassName + "-get");
        }
        return entity;
    }

    /**
     * Make assignments of fields and values of entity to be used in
     * insertion and update database operations
     */
    @Override
    public void loadQueryParameters(BaseEntity entity)
            throws QException {
        try {
            UserConfigurationEntity ent = (UserConfigurationEntity) entity;
            addQueryParameter(entityIdFieldName, ent.getID(), SQLParameter.SQLDataType.Long_sdt);
            addQueryParameter("ucf_id_user", ent.getIdUser(), SQLParameter.SQLDataType.String_sdt);
            addQueryParameter("ucf_id_owner", ent.getIdOwner(), SQLParameter.SQLDataType.Long_sdt);
            addQueryParameter("ucf_hour_cost", ent.getHourCost(), SQLParameter.SQLDataType.Decimal_sdt);
            addQueryParameter("ucf_created_from", ent.getCreatedFrom(), SQLParameter.SQLDataType.DateTime_sdt);
        } catch (Exception e) {
            throw new QException(e, QException.ExceptionType.LoadData_err, subClassName + "-loadQueryParameters");
        }
    }

    /**
     * Load the values of ResultSet of table from entity in to returned object.
     */
    @Override
    public UserConfigurationEntity loadEntityAttributesFromRs(ResultSet rs) throws QException {
        UserConfigurationEntity entity = new UserConfigurationEntity();
        try {
            entity.setID(rs.getLong(entityIdFieldName));
            entity.setIdUser(rs.getString("ucf_id_user"));
            entity.setIdOwner(rs.getLong("ucf_id_owner"));
            entity.setHourCost(rs.getDouble("ucf_hour_cost"));
            entity.setCreatedFrom(rs.getTimestamp("ucf_created_from"));

        } catch (Exception e) {
            throw new QException(e, QException.ExceptionType.LoadData_err, subClassName + "-loadEntityAttributesFromRs");
        }
        return entity;
    }

    /**
     * Gets list of users with costs
     * @return Return a list of the costs
     */
    public List getAllUsersCosts(Long idOwner) throws QException {
        ArrayList listUsersWithCosts = new ArrayList();
        try {
            ResultSet rs = getResultSet();

            String query = "SELECT * "
                    + " FROM " + entityTableName
                    + " WHERE ucf_id_owner = " + idOwner;

            rs = dbOperator.getResultSet(query);

            while(rs.next()){
                listUsersWithCosts.add(loadEntityAttributesFromRs(rs));
            }
        } catch (Exception e) {
            throw new QException(e, QException.ExceptionType.LoadData_err, subClassName + "-getAllUsersCosts");
        }
        return listUsersWithCosts;
    }

    /**
     * Gets configuration cost the user
     * @return Return configuration cost the user to the owner in the database
     */
    public UserConfigurationEntity getCostByOwnerUser(String idUser, Long idOwner) throws QException {
        UserConfigurationEntity entity = null;
        try {
            ResultSet rs = getResultSet();

            String query = "SELECT * "
                    + " FROM " + entityTableName
                    + " WHERE ucf_id_user = '" +idUser +"'"
                    + " AND ucf_id_owner = " +idOwner;

            rs = dbOperator.getResultSet(query);

            while(rs.next()){
                entity = loadEntityAttributesFromRs(rs);
            }
        } catch (Exception e) {
            throw new QException(e, QException.ExceptionType.LoadData_err, subClassName + "-getCostByOwnerUser");
        }
        return entity;
    }

    /**
     * Gets list of costs of the issue
     * @return Return a list cost of the issue
     */
    public List> getDataIssue(Long idIssue, Long idOwner) throws QException {
        List> listIssuesWithCost = null;

        try {
            ResultSet rs = getResultSet();

            String query = " SELECT CASE WHEN his.uch_hour_cost IS NULL " +
                           " THEN " +
                            " (conf.ucf_hour_cost /3600) * rec.tir_worked_time " +
                            " ELSE " +
                            " (his.uch_hour_cost /3600) * rec.tir_worked_time " +
                            " END cost, " +
                            " conf.ucf_hour_cost, rec.tir_worked_time, rec.tir_id, " +
                            "  rec.tir_id_state, rec.tir_id_registration_type , his.uch_hour_cost " +
                            " FROM qji_user_configuration conf " +
                            " LEFT JOIN  qji_time_record rec ON conf.ucf_id_user = rec.tir_id_user " +
                            " LEFT JOIN qji_user_costs_history his " +
                            " ON his.uch_id_user =  rec.tir_id_user " +
                            " AND " +
                            " rec.tir_registration_date BETWEEN his.uch_from_date AND  his.uch_until_date " +
                            "WHERE rec.tir_id_entity ="+idIssue+
                            " AND rec.tir_id_owner ="+idOwner;

            rs = dbOperator.getResultSet(query);
            listIssuesWithCost = mapArrayFromQuery(query);


        } catch (Exception e) {
            throw new QException(e, QException.ExceptionType.LoadData_err, subClassName + "-getDataIssue");
        }
        return listIssuesWithCost;
    }

    /**
     * Gets list of costs of the issue
     * @return Return a list cost of the issue
     */
    public List> getDataByIssues(ArrayList idIssues, Long idOwner,
                                                     String initialDate, String endDate) throws QException {
        List> listIssuesWithCost = null;

        try {
            ResultSet rs = getResultSet();

            String Issues = "";

            for(int i = 0; i < idIssues.size(); i++){
                Issues += "'"+ idIssues.get(i)+"'";
                if(i != idIssues.size()-1){
                    Issues += ", ";
                }
            }

            String query =  " SELECT CASE WHEN his.uch_hour_cost IS NULL " +
                            "           THEN (conf.ucf_hour_cost /3600) * rec.tir_worked_time " +
                            "           ELSE (his.uch_hour_cost /3600) * rec.tir_worked_time " +
                            "        END cost, " +
                            "       conf.ucf_hour_cost, rec.tir_worked_time, " +
                            "       rec.tir_id_state, rec.tir_id_registration_type , his.uch_hour_cost, rec.tir_id_entity " +
                            "    FROM qji_user_configuration conf " +
                            "    LEFT JOIN  qji_time_record rec ON conf.ucf_id_user = rec.tir_id_user " +
                            "    LEFT JOIN qji_user_costs_history his ON " +
                            "               (his.uch_id_user =  rec.tir_id_user " +
                            "           AND  rec.tir_registration_date BETWEEN his.uch_from_date AND  his.uch_until_date )" +
                            "    WHERE rec.tir_id_entity IN ("+Issues+")"+
                            "      AND rec.tir_id_owner ='"+idOwner+"'";

            if(initialDate != null && endDate != null){
                query += " AND rec.tir_registration_date BETWEEN '" + initialDate + "' AND '" + endDate +  " 23:59:59'";
            }

            rs = dbOperator.getResultSet(query);
            listIssuesWithCost = mapArrayFromQuery(query);

        } catch (Exception e) {
            throw new QException(e, QException.ExceptionType.LoadData_err, subClassName + "-getDataByIssues");
        }
        return listIssuesWithCost;
    }

    @Override
    public void close() throws Exception {

    }

    @Override
    public List getAll() throws QException {
        return null;
    }

}