Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.model;

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


@Entity
@NamedNativeQueries({
        @NamedNativeQuery(name = "RBM.RbmBilledFofoId",
                query = "SELECT" +
                        "    a.auth_id," +
                        "    a.rbm_Name," +
                        "    a.fofo_id," +
                        "    a.fofo_code," +
                        "    (CASE " +
                        "        WHEN SUM(o.total_amount) > 20000 THEN 'Billed' " +
                        "        ELSE 'Not billed' " +
                        "     END) AS status " +
                        " FROM (" +
                        "         SELECT" +
                        "             au.id AS auth_id," +
                        "             CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name," +
                        "             fs.id AS fofo_id," +
                        "             fs.code as fofo_code" +
                        "         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 mt ON mt.fofo_id = fs.id and mt.on_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') and mt.purchase > 0" +
                        "         WHERE pp.partner_id != 0" +
                        "           AND p.category_id = 18" +
                        "           AND p.escalation_type = 'L1'" +
                        "           AND fs.active = 1" +
                        "           AND fs.internal = false" +
                        "         UNION ALL" +
                        "         SELECT" +
                        "             au.id AS auth_id," +
                        "             CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name," +
                        "             fs.id AS fofo_id," +
                        "             fs.code as fofo_code" +
                        "         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 mt ON mt.fofo_id = fs.id and mt.on_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') and mt.purchase > 0" +
                        "         WHERE pp.partner_id = 0" +
                        "           AND p.category_id = 18" +
                        "           AND fs.active = 1" +
                        "           AND fs.internal = false" +
                        "           AND p.escalation_type = 'L1'" +
                        "     ) a" +
                        "    LEFT JOIN transaction.order o ON o.customer_id = a.fofo_id" +
                        "    AND o.billing_timestamp >= :startDate" +
                        "    AND o.billing_timestamp < :endDate" +
                        " GROUP BY a.auth_id, a.Rbm_Name, a.fofo_id",
                resultSetMapping = "BilledFofoId"),

})

@SqlResultSetMappings({

        @SqlResultSetMapping(name = "BilledFofoId",
                classes = {@ConstructorResult(targetClass = RbmBilledFofoIdsModel.class,
                        columns = {
                                @ColumnResult(name = "auth_id", type = Integer.class),
                                @ColumnResult(name = "rbm_name", type = String.class),
                                @ColumnResult(name = "fofo_id", type = Integer.class),
                                @ColumnResult(name = "fofo_code", type = String.class),
                                @ColumnResult(name = "status ", type = String.class),
                        }
                )}
        )

})

public class RbmBilledFofoIdsModel {
    int authId;
    String rbmName;
    int fofoId;
    String partnerCode;
    String status;

    // 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 RbmBilledFofoIdsModel(int authId, String rbmName, int fofoId, String partnerCode, String status) {
        this.authId = authId;
        this.rbmName = rbmName;
        this.fofoId = fofoId;
        this.partnerCode = partnerCode;
        this.status = status;
    }

    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 getFofoId() {
        return fofoId;
    }

    public void setFofoId(int fofoId) {
        this.fofoId = fofoId;
    }

    public String getpartnerCode() {
        return partnerCode;
    }

    public void setpartnerCode(String partnerCode) {
        this.partnerCode = partnerCode;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "RbmBilledFofoIdsModel{" +
                "authId=" + authId +
                ", rbmName='" + rbmName + '\'' +
                ", fofoId=" + fofoId +
                ", partnerCode='" + partnerCode + '\'' +
                ", status='" + status + '\'' +
                '}';
    }

    @Override
    public boolean equals(Object o) {

        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        RbmBilledFofoIdsModel that = (RbmBilledFofoIdsModel) o;
        return authId == that.authId && fofoId == that.fofoId && Objects.equals(rbmName, that.rbmName) && Objects.equals(partnerCode, that.partnerCode) && Objects.equals(status, that.status);
    }

    @Override
    public int hashCode() {
        return Objects.hash(authId, rbmName, fofoId, partnerCode, status);
    }
}