Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.model;

import javax.persistence.*;

@Entity
@NamedNativeQueries({
        @NamedNativeQuery(name = "RBM.WeeklyBilling",
                query = "SELECT " +
                        "    a.auth_id, " +
                        "    a.rbm_Name, " +
                        "    a.fofo_id, " +
                        "    a.fofo_code, " +
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) BETWEEN 1 AND 7 THEN o.total_amount ELSE 0 END), 0) AS week1_amount, " +
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) BETWEEN 8 AND 15 THEN o.total_amount ELSE 0 END), 0) AS week2_amount, " +
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) BETWEEN 16 AND 22 THEN o.total_amount ELSE 0 END), 0) AS week3_amount, " +
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) >= 23 THEN o.total_amount ELSE 0 END), 0) AS week4_amount, " +
                        "    COALESCE(SUM(o.total_amount), 0) AS mtd_amount " +
                        "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, a.fofo_code",
                resultSetMapping = "WeeklyBilling")
})
@SqlResultSetMappings({
        @SqlResultSetMapping(name = "WeeklyBilling",
                classes = {@ConstructorResult(targetClass = RbmWeeklyBillingModel.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 = "week1_amount", type = Long.class),
                                @ColumnResult(name = "week2_amount", type = Long.class),
                                @ColumnResult(name = "week3_amount", type = Long.class),
                                @ColumnResult(name = "week4_amount", type = Long.class),
                                @ColumnResult(name = "mtd_amount", type = Long.class)
                        }
                )}
        )
})
public class RbmWeeklyBillingModel {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private int authId;
    private String rbmName;
    private int fofoId;
    private String fofoCode;
    private long week1Amount;
    private long week2Amount;
    private long week3Amount;
    private long week4Amount;
    private long mtdAmount;

    private static final long BILLING_THRESHOLD = 20000L;

    public RbmWeeklyBillingModel() {}

    public RbmWeeklyBillingModel(int authId, String rbmName, int fofoId, String fofoCode,
                                  long week1Amount, long week2Amount, long week3Amount,
                                  long week4Amount, long mtdAmount) {
        this.authId = authId;
        this.rbmName = rbmName;
        this.fofoId = fofoId;
        this.fofoCode = fofoCode;
        this.week1Amount = week1Amount;
        this.week2Amount = week2Amount;
        this.week3Amount = week3Amount;
        this.week4Amount = week4Amount;
        this.mtdAmount = mtdAmount;
    }

    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 getFofoCode() { return fofoCode; }
    public void setFofoCode(String fofoCode) { this.fofoCode = fofoCode; }

    public long getWeek1Amount() { return week1Amount; }
    public void setWeek1Amount(long week1Amount) { this.week1Amount = week1Amount; }

    public long getWeek2Amount() { return week2Amount; }
    public void setWeek2Amount(long week2Amount) { this.week2Amount = week2Amount; }

    public long getWeek3Amount() { return week3Amount; }
    public void setWeek3Amount(long week3Amount) { this.week3Amount = week3Amount; }

    public long getWeek4Amount() { return week4Amount; }
    public void setWeek4Amount(long week4Amount) { this.week4Amount = week4Amount; }

    public long getMtdAmount() { return mtdAmount; }
    public void setMtdAmount(long mtdAmount) { this.mtdAmount = mtdAmount; }

    public boolean isWeek1Billed() { return week1Amount > BILLING_THRESHOLD; }
    public boolean isWeek2Billed() { return week2Amount > BILLING_THRESHOLD; }
    public boolean isWeek3Billed() { return week3Amount > BILLING_THRESHOLD; }
    public boolean isWeek4Billed() { return week4Amount > BILLING_THRESHOLD; }
    public boolean isMtdBilled() { return mtdAmount > BILLING_THRESHOLD; }
}