Subversion Repositories SmartDukaan

Rev

Rev 35453 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 35453 Rev 35669
Line 8... Line 8...
8 
                query = "SELECT " +
 8 
                query = "SELECT " +
9 
                        "    a.auth_id, " +
 9 
                        "    a.auth_id, " +
10 
                        "    a.rbm_Name, " +
 10 
                        "    a.rbm_Name, " +
11 
                        "    a.fofo_id, " +
 11 
                        "    a.fofo_id, " +
12 
                        "    a.fofo_code, " +
 12 
                        "    a.fofo_code, " +
- 
 
 13 
                        "    a.monthly_target, " +
13 
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) BETWEEN 1 AND 7 THEN o.total_amount ELSE 0 END), 0) AS week1_amount, " +
 14 
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) BETWEEN 1 AND 7 THEN o.total_amount ELSE 0 END), 0) AS week1_amount, " +
14 
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) BETWEEN 8 AND 15 THEN o.total_amount ELSE 0 END), 0) AS week2_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, " +
15 
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) BETWEEN 16 AND 22 THEN o.total_amount ELSE 0 END), 0) AS week3_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, " +
16 
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) >= 23 THEN o.total_amount ELSE 0 END), 0) AS week4_amount, " +
 17 
                        "    COALESCE(SUM(CASE WHEN DAY(o.billing_timestamp) >= 23 THEN o.total_amount ELSE 0 END), 0) AS week4_amount, " +
17 
                        "    COALESCE(SUM(o.total_amount), 0) AS mtd_amount " +
 18 
                        "    COALESCE(SUM(o.total_amount), 0) AS mtd_amount " +
18 
                        "FROM ( " +
 19 
                        "FROM ( " +
19 
                        "    SELECT au.id AS auth_id, CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name, " +
 20 
                        "    SELECT au.id AS auth_id, CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name, " +
20 
                        "           fs.id AS fofo_id, fs.code as fofo_code " +
 21 
                        "           fs.id AS fofo_id, fs.code as fofo_code, " +
- 
 
 22 
                        "           COALESCE(mt.purchase, 0) AS monthly_target " +
21 
                        "    FROM auth.auth_user au " +
 23 
                        "    FROM auth.auth_user au " +
22 
                        "    JOIN cs.position p ON p.auth_user_id = au.id " +
 24 
                        "    JOIN cs.position p ON p.auth_user_id = au.id " +
23 
                        "    JOIN cs.partner_position pp ON pp.position_id = p.id " +
 25 
                        "    JOIN cs.partner_position pp ON pp.position_id = p.id " +
24 
                        "    JOIN fofo.fofo_store fs ON fs.id = pp.partner_id " +
 26 
                        "    JOIN fofo.fofo_store fs ON fs.id = pp.partner_id " +
25 
                        "    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 " +
 27 
                        "    LEFT JOIN fofo.monthly_target mt ON mt.fofo_id = fs.id AND mt.on_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') " +
26 
                        "    WHERE pp.partner_id != 0 AND p.category_id = 18 AND p.escalation_type = 'L1' " +
 28 
                        "    WHERE pp.partner_id != 0 AND p.category_id = 18 AND p.escalation_type = 'L1' " +
27 
                        "          AND fs.active = 1 AND fs.internal = false " +
 29 
                        "          AND fs.active = 1 AND fs.internal = false " +
28 
                        "    UNION ALL " +
 30 
                        "    UNION ALL " +
29 
                        "    SELECT au.id AS auth_id, CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name, " +
 31 
                        "    SELECT au.id AS auth_id, CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name, " +
30 
                        "           fs.id AS fofo_id, fs.code as fofo_code " +
 32 
                        "           fs.id AS fofo_id, fs.code as fofo_code, " +
- 
 
 33 
                        "           COALESCE(mt.purchase, 0) AS monthly_target " +
31 
                        "    FROM auth.auth_user au " +
 34 
                        "    FROM auth.auth_user au " +
32 
                        "    JOIN cs.position p ON p.auth_user_id = au.id " +
 35 
                        "    JOIN cs.position p ON p.auth_user_id = au.id " +
33 
                        "    JOIN cs.partner_position pp ON pp.position_id = p.id " +
 36 
                        "    JOIN cs.partner_position pp ON pp.position_id = p.id " +
34 
                        "    JOIN cs.region r ON pp.region_id = r.id " +
 37 
                        "    JOIN cs.region r ON pp.region_id = r.id " +
35 
                        "    JOIN cs.partner_region pr ON pr.region_id = pp.region_id " +
 38 
                        "    JOIN cs.partner_region pr ON pr.region_id = pp.region_id " +
36 
                        "    JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id " +
 39 
                        "    JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id " +
37 
                        "    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 " +
 40 
                        "    LEFT JOIN fofo.monthly_target mt ON mt.fofo_id = fs.id AND mt.on_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') " +
38 
                        "    WHERE pp.partner_id = 0 AND p.category_id = 18 AND fs.active = 1 " +
 41 
                        "    WHERE pp.partner_id = 0 AND p.category_id = 18 AND fs.active = 1 " +
39 
                        "          AND fs.internal = false AND p.escalation_type = 'L1' " +
 42 
                        "          AND fs.internal = false AND p.escalation_type = 'L1' " +
40 
                        ") a " +
 43 
                        ") a " +
41 
                        "LEFT JOIN transaction.order o ON o.customer_id = a.fofo_id " +
 44 
                        "LEFT JOIN transaction.order o ON o.customer_id = a.fofo_id " +
42 
                        "    AND o.billing_timestamp >= :startDate " +
 45 
                        "    AND o.billing_timestamp >= :startDate " +
43 
                        "    AND o.billing_timestamp < :endDate " +
 46 
                        "    AND o.billing_timestamp < :endDate " +
44 
                        "GROUP BY a.auth_id, a.Rbm_Name, a.fofo_id, a.fofo_code",
 47 
                        "GROUP BY a.auth_id, a.Rbm_Name, a.fofo_id, a.fofo_code, a.monthly_target",
45 
                resultSetMapping = "WeeklyBilling")
 48 
                resultSetMapping = "WeeklyBilling")
46 
})
 49 
})
47 
@SqlResultSetMappings({
 50 
@SqlResultSetMappings({
48 
        @SqlResultSetMapping(name = "WeeklyBilling",
 51 
        @SqlResultSetMapping(name = "WeeklyBilling",
49 
                classes = {@ConstructorResult(targetClass = RbmWeeklyBillingModel.class,
 52 
                classes = {@ConstructorResult(targetClass = RbmWeeklyBillingModel.class,
50 
                        columns = {
 53 
                        columns = {
51 
                                @ColumnResult(name = "auth_id", type = Integer.class),
 54 
                                @ColumnResult(name = "auth_id", type = Integer.class),
52 
                                @ColumnResult(name = "rbm_name", type = String.class),
 55 
                                @ColumnResult(name = "rbm_name", type = String.class),
53 
                                @ColumnResult(name = "fofo_id", type = Integer.class),
 56 
                                @ColumnResult(name = "fofo_id", type = Integer.class),
54 
                                @ColumnResult(name = "fofo_code", type = String.class),
 57 
                                @ColumnResult(name = "fofo_code", type = String.class),
- 
 
 58 
                                @ColumnResult(name = "monthly_target", type = Long.class),
55 
                                @ColumnResult(name = "week1_amount", type = Long.class),
 59 
                                @ColumnResult(name = "week1_amount", type = Long.class),
56 
                                @ColumnResult(name = "week2_amount", type = Long.class),
 60 
                                @ColumnResult(name = "week2_amount", type = Long.class),
57 
                                @ColumnResult(name = "week3_amount", type = Long.class),
 61 
                                @ColumnResult(name = "week3_amount", type = Long.class),
58 
                                @ColumnResult(name = "week4_amount", type = Long.class),
 62 
                                @ColumnResult(name = "week4_amount", type = Long.class),
59 
                                @ColumnResult(name = "mtd_amount", type = Long.class)
 63 
                                @ColumnResult(name = "mtd_amount", type = Long.class)
Line 69... Line 73...
69 
 
 73 
 
70 
    private int authId;
 74 
    private int authId;
71 
    private String rbmName;
 75 
    private String rbmName;
72 
    private int fofoId;
 76 
    private int fofoId;
73 
    private String fofoCode;
 77 
    private String fofoCode;
- 
 
 78 
    private static final long TARGET_THRESHOLD = 100000L;
74 
    private long week1Amount;
 79 
    private long week1Amount;
75 
    private long week2Amount;
 80 
    private long week2Amount;
76 
    private long week3Amount;
 81 
    private long week3Amount;
77 
    private long week4Amount;
 82 
    private long week4Amount;
78 
    private long mtdAmount;
 83 
    private long mtdAmount;
79 
 
 84 
 
80 
    private static final long BILLING_THRESHOLD = 20000L;
 85 
    private static final long BILLING_THRESHOLD = 20000L;
- 
 
 86 
    private static final long DEFAULT_TARGET = 100000L;
- 
 
 87 
    private long monthlyTarget;
81 
 
 88 
 
82 
    public RbmWeeklyBillingModel() {}
 89 
    public RbmWeeklyBillingModel() {}
83 
 
 90 
 
84 
    public RbmWeeklyBillingModel(int authId, String rbmName, int fofoId, String fofoCode,
 91 
    public RbmWeeklyBillingModel(int authId, String rbmName, int fofoId, String fofoCode,
85 
                                  long week1Amount, long week2Amount, long week3Amount,
 92 
                                 long monthlyTarget, long week1Amount, long week2Amount, long week3Amount,
86 
                                  long week4Amount, long mtdAmount) {
 93 
                                  long week4Amount, long mtdAmount) {
87 
        this.authId = authId;
 94 
        this.authId = authId;
88 
        this.rbmName = rbmName;
 95 
        this.rbmName = rbmName;
89 
        this.fofoId = fofoId;
 96 
        this.fofoId = fofoId;
90 
        this.fofoCode = fofoCode;
 97 
        this.fofoCode = fofoCode;
- 
 
 98 
        this.monthlyTarget = monthlyTarget;
91 
        this.week1Amount = week1Amount;
 99 
        this.week1Amount = week1Amount;
92 
        this.week2Amount = week2Amount;
 100 
        this.week2Amount = week2Amount;
93 
        this.week3Amount = week3Amount;
 101 
        this.week3Amount = week3Amount;
94 
        this.week4Amount = week4Amount;
 102 
        this.week4Amount = week4Amount;
95 
        this.mtdAmount = mtdAmount;
 103 
        this.mtdAmount = mtdAmount;
Line 105... Line 113...
105 
    public void setFofoId(int fofoId) { this.fofoId = fofoId; }
 113 
    public void setFofoId(int fofoId) { this.fofoId = fofoId; }
106 
 
 114 
 
107 
    public String getFofoCode() { return fofoCode; }
 115 
    public String getFofoCode() { return fofoCode; }
108 
    public void setFofoCode(String fofoCode) { this.fofoCode = fofoCode; }
 116 
    public void setFofoCode(String fofoCode) { this.fofoCode = fofoCode; }
109 
 
 117 
 
- 
 
 118 
    public long getMonthlyTarget() {
- 
 
 119 
        return monthlyTarget;
- 
 
 120 
    }
- 
 
 121 
 
- 
 
 122 
    public void setMonthlyTarget(long monthlyTarget) {
- 
 
 123 
        this.monthlyTarget = monthlyTarget;
- 
 
 124 
    }
- 
 
 125 
 
110 
    public long getWeek1Amount() { return week1Amount; }
 126 
    public long getWeek1Amount() { return week1Amount; }
111 
    public void setWeek1Amount(long week1Amount) { this.week1Amount = week1Amount; }
 127 
    public void setWeek1Amount(long week1Amount) { this.week1Amount = week1Amount; }
112 
 
 128 
 
113 
    public long getWeek2Amount() { return week2Amount; }
 129 
    public long getWeek2Amount() { return week2Amount; }
114 
    public void setWeek2Amount(long week2Amount) { this.week2Amount = week2Amount; }
 130 
    public void setWeek2Amount(long week2Amount) { this.week2Amount = week2Amount; }
Line 120... Line 136...
120 
    public void setWeek4Amount(long week4Amount) { this.week4Amount = week4Amount; }
 136 
    public void setWeek4Amount(long week4Amount) { this.week4Amount = week4Amount; }
121 
 
 137 
 
122 
    public long getMtdAmount() { return mtdAmount; }
 138 
    public long getMtdAmount() { return mtdAmount; }
123 
    public void setMtdAmount(long mtdAmount) { this.mtdAmount = mtdAmount; }
 139 
    public void setMtdAmount(long mtdAmount) { this.mtdAmount = mtdAmount; }
124 
 
 140 
 
- 
 
 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() {
125 
    public boolean isWeek1Billed() { return week1Amount > BILLING_THRESHOLD; }
 147 
        return monthlyTarget > 0 ? TARGET_THRESHOLD : BILLING_THRESHOLD;
- 
 
 148 
    }
- 
 
 149 
 
- 
 
 150 
    public boolean isWeek1Billed() {
- 
 
 151 
        return week1Amount > getBillingThreshold();
- 
 
 152 
    }
- 
 
 153 
 
126 
    public boolean isWeek2Billed() { return week2Amount > BILLING_THRESHOLD; }
 154 
    public boolean isWeek2Billed() {
- 
 
 155 
        return week2Amount > getBillingThreshold();
- 
 
 156 
    }
- 
 
 157 
 
127 
    public boolean isWeek3Billed() { return week3Amount > BILLING_THRESHOLD; }
 158 
    public boolean isWeek3Billed() {
- 
 
 159 
        return week3Amount > getBillingThreshold();
- 
 
 160 
    }
- 
 
 161 
 
128 
    public boolean isWeek4Billed() { return week4Amount > BILLING_THRESHOLD; }
 162 
    public boolean isWeek4Billed() {
- 
 
 163 
        return week4Amount > getBillingThreshold();
- 
 
 164 
    }
- 
 
 165 
 
129 
    public boolean isMtdBilled() { return mtdAmount > BILLING_THRESHOLD; }
 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 
    }
130 
}
 176 
}