Subversion Repositories SmartDukaan

Rev

Rev 35453 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
35453 amit 1 
package com.spice.profitmandi.dao.model;
2 
 
3 
import javax.persistence.*;
4 
 
5 
@Entity
6 
@NamedNativeQueries({
7 
        @NamedNativeQuery(name = "RBM.WeeklyBilling",
8 
                query = "SELECT " +
9 
                        "    a.auth_id, " +
10 
                        "    a.rbm_Name, " +
11 
                        "    a.fofo_id, " +
12 
                        "    a.fofo_code, " +
35669 ranu 13 
                        "    a.monthly_target, " +
35453 amit 14 
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) BETWEEN 1 AND 7 THEN o.total_amount ELSE 0 END), 0) AS week1_amount, " +
15 
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) BETWEEN 8 AND 15 THEN o.total_amount ELSE 0 END), 0) AS week2_amount, " +
16 
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) BETWEEN 16 AND 22 THEN o.total_amount ELSE 0 END), 0) AS week3_amount, " +
17 
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) >= 23 THEN o.total_amount ELSE 0 END), 0) AS week4_amount, " +
18 
                        "    COALESCE(SUM(o.total_amount), 0) AS mtd_amount " +
19 
                        "FROM ( " +
20 
                        "    SELECT au.id AS auth_id, CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name, " +
35669 ranu 21 
                        "           fs.id AS fofo_id, fs.code as fofo_code, " +
22 
                        "           COALESCE(mt.purchase, 0) AS monthly_target " +
35453 amit 23 
                        "    FROM auth.auth_user au " +
24 
                        "    JOIN cs.position p ON p.auth_user_id = au.id " +
25 
                        "    JOIN cs.partner_position pp ON pp.position_id = p.id " +
26 
                        "    JOIN fofo.fofo_store fs ON fs.id = pp.partner_id " +
35669 ranu 27 
                        "    LEFT JOIN fofo.monthly_target mt ON mt.fofo_id = fs.id AND mt.on_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') " +
35453 amit 28 
                        "    WHERE pp.partner_id != 0 AND p.category_id = 18 AND p.escalation_type = 'L1' " +
29 
                        "          AND fs.active = 1 AND fs.internal = false " +
30 
                        "    UNION ALL " +
31 
                        "    SELECT au.id AS auth_id, CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name, " +
35669 ranu 32 
                        "           fs.id AS fofo_id, fs.code as fofo_code, " +
33 
                        "           COALESCE(mt.purchase, 0) AS monthly_target " +
35453 amit 34 
                        "    FROM auth.auth_user au " +
35 
                        "    JOIN cs.position p ON p.auth_user_id = au.id " +
36 
                        "    JOIN cs.partner_position pp ON pp.position_id = p.id " +
37 
                        "    JOIN cs.region r ON pp.region_id = r.id " +
38 
                        "    JOIN cs.partner_region pr ON pr.region_id = pp.region_id " +
39 
                        "    JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id " +
35669 ranu 40 
                        "    LEFT JOIN fofo.monthly_target mt ON mt.fofo_id = fs.id AND mt.on_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') " +
35453 amit 41 
                        "    WHERE pp.partner_id = 0 AND p.category_id = 18 AND fs.active = 1 " +
42 
                        "          AND fs.internal = false AND p.escalation_type = 'L1' " +
43 
                        ") a " +
44 
                        "LEFT JOIN transaction.order o ON o.customer_id = a.fofo_id " +
45 
                        "    AND o.billing_timestamp >= :startDate " +
46 
                        "    AND o.billing_timestamp < :endDate " +
35669 ranu 47 
                        "GROUP BY a.auth_id, a.Rbm_Name, a.fofo_id, a.fofo_code, a.monthly_target",
35453 amit 48 
                resultSetMapping = "WeeklyBilling")
49 
})
50 
@SqlResultSetMappings({
51 
        @SqlResultSetMapping(name = "WeeklyBilling",
52 
                classes = {@ConstructorResult(targetClass = RbmWeeklyBillingModel.class,
53 
                        columns = {
54 
                                @ColumnResult(name = "auth_id", type = Integer.class),
55 
                                @ColumnResult(name = "rbm_name", type = String.class),
56 
                                @ColumnResult(name = "fofo_id", type = Integer.class),
57 
                                @ColumnResult(name = "fofo_code", type = String.class),
35669 ranu 58 
                                @ColumnResult(name = "monthly_target", type = Long.class),
35453 amit 59 
                                @ColumnResult(name = "week1_amount", type = Long.class),
60 
                                @ColumnResult(name = "week2_amount", type = Long.class),
61 
                                @ColumnResult(name = "week3_amount", type = Long.class),
62 
                                @ColumnResult(name = "week4_amount", type = Long.class),
63 
                                @ColumnResult(name = "mtd_amount", type = Long.class)
64 
                        }
65 
                )}
66 
        )
67 
})
68 
public class RbmWeeklyBillingModel {
69 
 
70 
    @Id
71 
    @GeneratedValue(strategy = GenerationType.IDENTITY)
72 
    private Long id;
73 
 
74 
    private int authId;
75 
    private String rbmName;
76 
    private int fofoId;
77 
    private String fofoCode;
35669 ranu 78 
    private static final long TARGET_THRESHOLD = 100000L;
35453 amit 79 
    private long week1Amount;
80 
    private long week2Amount;
81 
    private long week3Amount;
82 
    private long week4Amount;
83 
    private long mtdAmount;
84 
 
85 
    private static final long BILLING_THRESHOLD = 20000L;
35669 ranu 86 
    private static final long DEFAULT_TARGET = 100000L;
87 
    private long monthlyTarget;
35453 amit 88 
 
89 
    public RbmWeeklyBillingModel() {}
90 
 
91 
    public RbmWeeklyBillingModel(int authId, String rbmName, int fofoId, String fofoCode,
35669 ranu 92 
                                 long monthlyTarget, long week1Amount, long week2Amount, long week3Amount,
35453 amit 93 
                                  long week4Amount, long mtdAmount) {
94 
        this.authId = authId;
95 
        this.rbmName = rbmName;
96 
        this.fofoId = fofoId;
97 
        this.fofoCode = fofoCode;
35669 ranu 98 
        this.monthlyTarget = monthlyTarget;
35453 amit 99 
        this.week1Amount = week1Amount;
100 
        this.week2Amount = week2Amount;
101 
        this.week3Amount = week3Amount;
102 
        this.week4Amount = week4Amount;
103 
        this.mtdAmount = mtdAmount;
104 
    }
105 
 
106 
    public int getAuthId() { return authId; }
107 
    public void setAuthId(int authId) { this.authId = authId; }
108 
 
109 
    public String getRbmName() { return rbmName; }
110 
    public void setRbmName(String rbmName) { this.rbmName = rbmName; }
111 
 
112 
    public int getFofoId() { return fofoId; }
113 
    public void setFofoId(int fofoId) { this.fofoId = fofoId; }
114 
 
115 
    public String getFofoCode() { return fofoCode; }
116 
    public void setFofoCode(String fofoCode) { this.fofoCode = fofoCode; }
117 
 
35669 ranu 118 
    public long getMonthlyTarget() {
119 
        return monthlyTarget;
120 
    }
121 
 
122 
    public void setMonthlyTarget(long monthlyTarget) {
123 
        this.monthlyTarget = monthlyTarget;
124 
    }
125 
 
35453 amit 126 
    public long getWeek1Amount() { return week1Amount; }
127 
    public void setWeek1Amount(long week1Amount) { this.week1Amount = week1Amount; }
128 
 
129 
    public long getWeek2Amount() { return week2Amount; }
130 
    public void setWeek2Amount(long week2Amount) { this.week2Amount = week2Amount; }
131 
 
132 
    public long getWeek3Amount() { return week3Amount; }
133 
    public void setWeek3Amount(long week3Amount) { this.week3Amount = week3Amount; }
134 
 
135 
    public long getWeek4Amount() { return week4Amount; }
136 
    public void setWeek4Amount(long week4Amount) { this.week4Amount = week4Amount; }
137 
 
138 
    public long getMtdAmount() { return mtdAmount; }
139 
    public void setMtdAmount(long mtdAmount) { this.mtdAmount = mtdAmount; }
140 
 
35669 ranu 141 
    /**
142 
     * Get the billing threshold for this partner.
143 
     * - Partners WITH monthly target: must bill > 100000
144 
     * - Partners WITHOUT monthly target: must bill > 20000
145 
     */
146 
    private long getBillingThreshold() {
147 
        return monthlyTarget > 0 ? TARGET_THRESHOLD : BILLING_THRESHOLD;
148 
    }
149 
 
150 
    public boolean isWeek1Billed() {
151 
        return week1Amount > getBillingThreshold();
152 
    }
153 
 
154 
    public boolean isWeek2Billed() {
155 
        return week2Amount > getBillingThreshold();
156 
    }
157 
 
158 
    public boolean isWeek3Billed() {
159 
        return week3Amount > getBillingThreshold();
160 
    }
161 
 
162 
    public boolean isWeek4Billed() {
163 
        return week4Amount > getBillingThreshold();
164 
    }
165 
 
166 
    public boolean isMtdBilled() {
167 
        return mtdAmount > getBillingThreshold();
168 
    }
169 
 
170 
    /**
171 
     * All partners are considered "targeted" - those with monthly target and those without.
172 
     */
173 
    public boolean isTargetedPartner() {
174 
        return true;
175 
    }
35453 amit 176 
}