Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.model;

import javax.persistence.*;
import java.util.Objects;

@Entity
@NamedNativeQueries({
        @NamedNativeQuery(name = "RbmTarget.getRbmAchievedMonthlyTarget",
                query = "SELECT td.auth_id," +
                        "           td.first_name," +
                        "           td.warehouse_id," +
                        "           COALESCE(SUM(ad.total_billed), 0) as total_billed," +
                        "           ROUND(" +
                        "                       COALESCE(SUM(ad.total_billed), 0) - COALESCE(SUM(ad.total_rto_refund), 0), 2" +
                        "               ) AS target_achieved" +
                        "    FROM (" +
                        "             SELECT au.id AS auth_id," +
                        "                    CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
                        "                    fs.id AS fofo_id," +
                        "                    fs.warehouse_id," +
                        "                    SUM(mtgt.purchase) AS monthly_target" +
                        "             FROM auth.auth_user au" +
                        "                      JOIN cs.position p ON p.auth_user_id = au.id" +
                        "                      JOIN cs.partner_position pp ON pp.position_id = p.id" +
                        "                      JOIN fofo.fofo_store fs ON fs.id = pp.partner_id" +
                        "                      JOIN fofo.monthly_target mtgt ON mtgt.fofo_id = fs.id" +
                        "             WHERE pp.partner_id != 0" +
                        "               AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
                        "               AND p.category_id = 18" +
                        "               AND p.escalation_type = 'L1'" +
                        "             GROUP BY au.id, fs.id" +
                        "" +
                        "             UNION ALL" +
                        "" +
                        "             SELECT au.id AS auth_id," +
                        "                    CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
                        "                    fs.id AS fofo_id," +
                        "                    fs.warehouse_id," +
                        "                    SUM(mtgt.purchase) AS monthly_target" +
                        "             FROM auth.auth_user au" +
                        "                      JOIN cs.position p ON p.auth_user_id = au.id" +
                        "                      JOIN cs.partner_position pp ON pp.position_id = p.id" +
                        "                      JOIN cs.partner_region pr ON pr.region_id = pp.region_id" +
                        "                      JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id" +
                        "                      JOIN fofo.monthly_target mtgt ON mtgt.fofo_id = fs.id" +
                        "             WHERE pp.partner_id = 0" +
                        "               AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
                        "               AND p.category_id = 18" +
                        "               AND p.escalation_type = 'L1'" +
                        "             GROUP BY au.id, fs.id" +
                        "         ) td" +
                        "" +
                        "             LEFT JOIN (" +
                        "        SELECT odr.customer_id AS fofo_id," +
                        "               SUM(CASE" +
                        "                       WHEN odr.billing_timestamp IS NOT NULL" +
                        "                           AND odr.refund_timestamp IS NULL THEN odr.total_amount" +
                        "                       ELSE 0 END) AS total_billed," +
                        "" +
                        "               SUM(CASE" +
                        "                       WHEN odr.billing_timestamp IS NOT NULL" +
                        "                           AND odr.refund_timestamp IS NOT NULL THEN odr.total_amount" +
                        "                       ELSE 0 END) AS total_rto_refund" +
                        "        FROM transaction.order odr" +
                        "                 JOIN transaction.lineitem li ON li.order_id = odr.id" +
                        "                 JOIN catalog.item ci ON ci.id = li.item_id" +
                        "        WHERE ci.category = 10006" +
                        "          AND (" +
                        "                (odr.billing_timestamp >= :startDate AND odr.billing_timestamp < :endDate)" +
                        "                OR (odr.refund_timestamp >= :startDate AND odr.refund_timestamp < :endDate)" +
                        "            )" +
                        "        GROUP BY odr.customer_id" +
                        "    ) ad ON td.fofo_id = ad.fofo_id" +
                        "" +
                        "    GROUP BY td.auth_id, td.warehouse_id, td.first_name",
                resultSetMapping = "AchievedMonthlyTarget"),

})

@SqlResultSetMappings({

        @SqlResultSetMapping(name = "AchievedMonthlyTarget",
                classes = {@ConstructorResult(targetClass = MTDAchievedTargetModel.class,
                        columns = {
                                @ColumnResult(name = "auth_id", type = Integer.class),
                                @ColumnResult(name = "first_name ", type = String.class),
                                @ColumnResult(name = "warehouse_id ", type = Integer.class),
                                @ColumnResult(name = "target_achieved ", type = Float.class)
                        }
                )}
        )

})

public class MTDAchievedTargetModel {
    int authId;
    String rbmName;
    int warehouseId;
    float acheivedMonthlyTarget;
    // Synthetic primary key to satisfy JPA's requirement
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; // This will not be used in the query but satisfies JPA.

    public MTDAchievedTargetModel(int authId, String rbmName, int warehouseId, float monthlyTarget) {
        this.authId = authId;
        this.rbmName = rbmName;
        this.warehouseId = warehouseId;
        this.acheivedMonthlyTarget = monthlyTarget;
    }

    public int getAuthId() {
        return authId;
    }

    public void setAuthId(int authId) {
        this.authId = authId;
    }

    public String getRbmName() {
        return rbmName;
    }

    public void setRbmName(String rbmName) {
        this.rbmName = rbmName;
    }

    public int getWarehouseId() {
        return warehouseId;
    }

    public void setWarehouseId(int warehouseId) {
        this.warehouseId = warehouseId;
    }

    public float getAcheivedMonthlyTarget() {
        return acheivedMonthlyTarget;
    }

    public void setAcheivedMonthlyTarget(float acheivedMonthlyTarget) {
        this.acheivedMonthlyTarget = acheivedMonthlyTarget;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        MTDAchievedTargetModel that = (MTDAchievedTargetModel) o;
        return authId == that.authId && warehouseId == that.warehouseId && Float.compare(acheivedMonthlyTarget, that.acheivedMonthlyTarget) == 0 && Objects.equals(rbmName, that.rbmName);
    }

    @Override
    public int hashCode() {
        return Objects.hash(rbmName, warehouseId, acheivedMonthlyTarget);
    }

    @Override
    public String toString() {
        return "MTDAchievedTargetModel{" +
                "authId=" + authId +
                ", rbmName='" + rbmName + '\'' +
                ", warehouseId=" + warehouseId +
                ", acheivedMonthlyTarget=" + acheivedMonthlyTarget +
                '}';
    }
}