Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.model;

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

@Entity
@NamedNativeQueries({

//        @NamedNativeQuery(name = "RbmTarget.getRbmAchievedMonthlyTarget",
//                query = "SELECT a.auth_id," +
//                        "       a.first_name," +
//                        "       a.warehouse_id," +
//                        "     sum(b.total_billed)," +
//                        "       ROUND(" +
//                        "                   COALESCE(SUM(b.total_billed), 0)" +
//                        "                   - COALESCE(SUM(ro.refundAmount), 0)" +
//                        "                   - COALESCE(SUM(b.total_rto_refund), 0), 0" +
//                        "           ) 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," +
//                        "                mtgt.purchase," +
//                        "                fs.warehouse_id" +
//                        "         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=pp.partner_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' AND fs.target != 0" +
//                        "" +
//                        "         UNION ALL" +
//                        "" +
//                        "         SELECT au.id AS auth_id," +
//                        "                CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
//                        "                fs.id AS fofo_id," +
//                        "                mtgt.purchase," +
//                        "                fs.warehouse_id" +
//                        "         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.region r ON pp.region_id = r.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=pp.partner_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' AND fs.target != 0" +
//                        "     ) a" +
//                        "         LEFT JOIN (" +
//                        "    SELECT odr.customer_id," +
//                        "           odr.id," +
//                        "           SUM(CASE WHEN odr.billing_timestamp IS NOT NULL THEN odr.total_amount ELSE 0 END) AS total_billed," +
//                        "           SUM(CASE WHEN 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 AND odr.refund_timestamp IS NULL)" +
//                        "            OR (odr.status = 31 AND odr.refund_timestamp >= :startDate AND odr.refund_timestamp < :endDate AND odr.billing_timestamp IS NOT NULL)" +
//                        "        )" +
//                        "    GROUP BY odr.customer_id" +
//                        ") b ON a.fofo_id = b.customer_id" +
//                        "         LEFT JOIN transaction.returnorderinfo ro ON ro.orderId = b.id" +
//                        "" +
//                        " GROUP BY a.auth_id, a.warehouse_id",
//                resultSetMapping = "AchievedMonthlyTarget"),
        @NamedNativeQuery(name = "RbmTarget.getRbmAchievedMonthlyTarget",
                query = "select a.auth_id,a.first_name,a.warehouse_id, b.total_billed," +
                        "       ROUND(" +
                        "            COALESCE((b.total_billed), 0)" +
                        "            - COALESCE((b.total_rto_refund), 0), 0" +
                        "    ) AS target_achieved" +
                        " from" +
                        " (SELECT au.id AS auth_id," +
                        "        CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
                        "        fs.warehouse_id" +
                        " 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=pp.partner_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' AND fs.target != 0" +
                        " group by auth_id,fs.warehouse_id) a" +
                        " left join(" +
                        " SELECT" +
                        "       fs.warehouse_id," +
                        "       SUM(CASE WHEN odr.billing_timestamp IS NOT NULL THEN odr.total_amount ELSE 0 END) AS total_billed," +
                        "       SUM(CASE WHEN 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" +
                        "         join fofo.fofo_store fs on fs.id=odr.customer_id" +
                        " where ci.category=10006" +
                        "  AND (" +
                        "        (odr.billing_timestamp >= :startDate AND odr.billing_timestamp <= :endDate AND odr.refund_timestamp IS NULL)" +
                        "        OR (odr.status = 31 AND odr.refund_timestamp >= :startDate AND odr.refund_timestamp < :endDate AND odr.billing_timestamp IS NOT NULL)" +
                        "    )" +
                        "  and fs.internal=0" +
                        " GROUP BY fs.warehouse_id" +
                        ") b on a.warehouse_id=b.warehouse_id" +
                        " group by a.warehouse_id",
                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 +
                '}';
    }
}