Subversion Repositories SmartDukaan

Rev

Go to most recent revision | Details | 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, " +
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 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) >= 23 THEN o.total_amount ELSE 0 END), 0) AS week4_amount, " +
17 
                        "    COALESCE(SUM(o.total_amount), 0) AS mtd_amount " +
18 
                        "FROM ( " +
19 
                        "    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 
                        "    FROM auth.auth_user au " +
22 
                        "    JOIN cs.position p ON p.auth_user_id = au.id " +
23 
                        "    JOIN cs.partner_position pp ON pp.position_id = p.id " +
24 
                        "    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 " +
26 
                        "    WHERE pp.partner_id != 0 AND p.category_id = 18 AND p.escalation_type = 'L1' " +
27 
                        "          AND fs.active = 1 AND fs.internal = false " +
28 
                        "    UNION ALL " +
29 
                        "    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 " +
31 
                        "    FROM auth.auth_user au " +
32 
                        "    JOIN cs.position p ON p.auth_user_id = au.id " +
33 
                        "    JOIN cs.partner_position pp ON pp.position_id = p.id " +
34 
                        "    JOIN cs.region r ON pp.region_id = r.id " +
35 
                        "    JOIN cs.partner_region pr ON pr.region_id = pp.region_id " +
36 
                        "    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 " +
38 
                        "    WHERE pp.partner_id = 0 AND p.category_id = 18 AND fs.active = 1 " +
39 
                        "          AND fs.internal = false AND p.escalation_type = 'L1' " +
40 
                        ") a " +
41 
                        "LEFT JOIN transaction.order o ON o.customer_id = a.fofo_id " +
42 
                        "    AND o.billing_timestamp >= :startDate " +
43 
                        "    AND o.billing_timestamp < :endDate " +
44 
                        "GROUP BY a.auth_id, a.Rbm_Name, a.fofo_id, a.fofo_code",
45 
                resultSetMapping = "WeeklyBilling")
46 
})
47 
@SqlResultSetMappings({
48 
        @SqlResultSetMapping(name = "WeeklyBilling",
49 
                classes = {@ConstructorResult(targetClass = RbmWeeklyBillingModel.class,
50 
                        columns = {
51 
                                @ColumnResult(name = "auth_id", type = Integer.class),
52 
                                @ColumnResult(name = "rbm_name", type = String.class),
53 
                                @ColumnResult(name = "fofo_id", type = Integer.class),
54 
                                @ColumnResult(name = "fofo_code", type = String.class),
55 
                                @ColumnResult(name = "week1_amount", type = Long.class),
56 
                                @ColumnResult(name = "week2_amount", type = Long.class),
57 
                                @ColumnResult(name = "week3_amount", type = Long.class),
58 
                                @ColumnResult(name = "week4_amount", type = Long.class),
59 
                                @ColumnResult(name = "mtd_amount", type = Long.class)
60 
                        }
61 
                )}
62 
        )
63 
})
64 
public class RbmWeeklyBillingModel {
65 
 
66 
    @Id
67 
    @GeneratedValue(strategy = GenerationType.IDENTITY)
68 
    private Long id;
69 
 
70 
    private int authId;
71 
    private String rbmName;
72 
    private int fofoId;
73 
    private String fofoCode;
74 
    private long week1Amount;
75 
    private long week2Amount;
76 
    private long week3Amount;
77 
    private long week4Amount;
78 
    private long mtdAmount;
79 
 
80 
    private static final long BILLING_THRESHOLD = 20000L;
81 
 
82 
    public RbmWeeklyBillingModel() {}
83 
 
84 
    public RbmWeeklyBillingModel(int authId, String rbmName, int fofoId, String fofoCode,
85 
                                  long week1Amount, long week2Amount, long week3Amount,
86 
                                  long week4Amount, long mtdAmount) {
87 
        this.authId = authId;
88 
        this.rbmName = rbmName;
89 
        this.fofoId = fofoId;
90 
        this.fofoCode = fofoCode;
91 
        this.week1Amount = week1Amount;
92 
        this.week2Amount = week2Amount;
93 
        this.week3Amount = week3Amount;
94 
        this.week4Amount = week4Amount;
95 
        this.mtdAmount = mtdAmount;
96 
    }
97 
 
98 
    public int getAuthId() { return authId; }
99 
    public void setAuthId(int authId) { this.authId = authId; }
100 
 
101 
    public String getRbmName() { return rbmName; }
102 
    public void setRbmName(String rbmName) { this.rbmName = rbmName; }
103 
 
104 
    public int getFofoId() { return fofoId; }
105 
    public void setFofoId(int fofoId) { this.fofoId = fofoId; }
106 
 
107 
    public String getFofoCode() { return fofoCode; }
108 
    public void setFofoCode(String fofoCode) { this.fofoCode = fofoCode; }
109 
 
110 
    public long getWeek1Amount() { return week1Amount; }
111 
    public void setWeek1Amount(long week1Amount) { this.week1Amount = week1Amount; }
112 
 
113 
    public long getWeek2Amount() { return week2Amount; }
114 
    public void setWeek2Amount(long week2Amount) { this.week2Amount = week2Amount; }
115 
 
116 
    public long getWeek3Amount() { return week3Amount; }
117 
    public void setWeek3Amount(long week3Amount) { this.week3Amount = week3Amount; }
118 
 
119 
    public long getWeek4Amount() { return week4Amount; }
120 
    public void setWeek4Amount(long week4Amount) { this.week4Amount = week4Amount; }
121 
 
122 
    public long getMtdAmount() { return mtdAmount; }
123 
    public void setMtdAmount(long mtdAmount) { this.mtdAmount = mtdAmount; }
124 
 
125 
    public boolean isWeek1Billed() { return week1Amount > BILLING_THRESHOLD; }
126 
    public boolean isWeek2Billed() { return week2Amount > BILLING_THRESHOLD; }
127 
    public boolean isWeek3Billed() { return week3Amount > BILLING_THRESHOLD; }
128 
    public boolean isWeek4Billed() { return week4Amount > BILLING_THRESHOLD; }
129 
    public boolean isMtdBilled() { return mtdAmount > BILLING_THRESHOLD; }
130 
}