Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
33917 ranu 1 
package com.spice.profitmandi.dao.model;
2 
 
3 
import javax.persistence.*;
4 
import java.util.Objects;
5 
 
6 
@Entity
7 
@NamedNativeQueries({
8 
        @NamedNativeQuery(name = "RbmTarget.getRbmAchievedMonthlyTarget",
34315 ranu 9 
                query = "SELECT td.auth_id," +
10 
                        "           td.first_name," +
11 
                        "           td.warehouse_id," +
12 
                        "           COALESCE(SUM(ad.total_billed), 0) as total_billed," +
13 
                        "           ROUND(" +
14 
                        "                       COALESCE(SUM(ad.total_billed), 0) - COALESCE(SUM(ad.total_rto_refund), 0), 2" +
15 
                        "               ) AS target_achieved" +
16 
                        "    FROM (" +
17 
                        "             SELECT au.id AS auth_id," +
18 
                        "                    CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
19 
                        "                    fs.id AS fofo_id," +
20 
                        "                    fs.warehouse_id," +
21 
                        "                    SUM(mtgt.purchase) AS monthly_target" +
22 
                        "             FROM auth.auth_user au" +
23 
                        "                      JOIN cs.position p ON p.auth_user_id = au.id" +
24 
                        "                      JOIN cs.partner_position pp ON pp.position_id = p.id" +
25 
                        "                      JOIN fofo.fofo_store fs ON fs.id = pp.partner_id" +
26 
                        "                      JOIN fofo.monthly_target mtgt ON mtgt.fofo_id = fs.id" +
27 
                        "             WHERE pp.partner_id != 0" +
28 
                        "               AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
29 
                        "               AND p.category_id = 18" +
30 
                        "               AND p.escalation_type = 'L1'" +
31 
                        "             GROUP BY au.id, fs.id" +
32 
                        "" +
33 
                        "             UNION ALL" +
34 
                        "" +
35 
                        "             SELECT au.id AS auth_id," +
36 
                        "                    CONCAT(au.first_name, ' ', au.last_name) AS first_name," +
37 
                        "                    fs.id AS fofo_id," +
38 
                        "                    fs.warehouse_id," +
39 
                        "                    SUM(mtgt.purchase) AS monthly_target" +
40 
                        "             FROM auth.auth_user au" +
41 
                        "                      JOIN cs.position p ON p.auth_user_id = au.id" +
42 
                        "                      JOIN cs.partner_position pp ON pp.position_id = p.id" +
43 
                        "                      JOIN cs.partner_region pr ON pr.region_id = pp.region_id" +
44 
                        "                      JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id" +
45 
                        "                      JOIN fofo.monthly_target mtgt ON mtgt.fofo_id = fs.id" +
46 
                        "             WHERE pp.partner_id = 0" +
47 
                        "               AND DATE_FORMAT(mtgt.on_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')" +
48 
                        "               AND p.category_id = 18" +
49 
                        "               AND p.escalation_type = 'L1'" +
50 
                        "             GROUP BY au.id, fs.id" +
51 
                        "         ) td" +
52 
                        "" +
53 
                        "             LEFT JOIN (" +
54 
                        "        SELECT odr.customer_id AS fofo_id," +
55 
                        "               SUM(CASE" +
56 
                        "                       WHEN odr.billing_timestamp IS NOT NULL" +
57 
                        "                           AND odr.refund_timestamp IS NULL THEN odr.total_amount" +
58 
                        "                       ELSE 0 END) AS total_billed," +
59 
                        "" +
60 
                        "               SUM(CASE" +
61 
                        "                       WHEN odr.billing_timestamp IS NOT NULL" +
62 
                        "                           AND odr.refund_timestamp IS NOT NULL THEN odr.total_amount" +
63 
                        "                       ELSE 0 END) AS total_rto_refund" +
64 
                        "        FROM transaction.order odr" +
65 
                        "                 JOIN transaction.lineitem li ON li.order_id = odr.id" +
66 
                        "                 JOIN catalog.item ci ON ci.id = li.item_id" +
67 
                        "        WHERE ci.category = 10006" +
68 
                        "          AND (" +
69 
                        "                (odr.billing_timestamp >= :startDate AND odr.billing_timestamp < :endDate)" +
70 
                        "                OR (odr.refund_timestamp >= :startDate AND odr.refund_timestamp < :endDate)" +
71 
                        "            )" +
72 
                        "        GROUP BY odr.customer_id" +
73 
                        "    ) ad ON td.fofo_id = ad.fofo_id" +
74 
                        "" +
75 
                        "    GROUP BY td.auth_id, td.warehouse_id, td.first_name",
33917 ranu 76 
                resultSetMapping = "AchievedMonthlyTarget"),
77 
 
78 
})
79 
 
80 
@SqlResultSetMappings({
81 
 
82 
        @SqlResultSetMapping(name = "AchievedMonthlyTarget",
83 
                classes = {@ConstructorResult(targetClass = MTDAchievedTargetModel.class,
84 
                        columns = {
85 
                                @ColumnResult(name = "auth_id", type = Integer.class),
86 
                                @ColumnResult(name = "first_name ", type = String.class),
87 
                                @ColumnResult(name = "warehouse_id ", type = Integer.class),
88 
                                @ColumnResult(name = "target_achieved ", type = Float.class)
89 
                        }
90 
                )}
91 
        )
92 
 
93 
})
94 
 
95 
public class MTDAchievedTargetModel {
96 
    int authId;
97 
    String rbmName;
98 
    int warehouseId;
99 
    float acheivedMonthlyTarget;
100 
    // Synthetic primary key to satisfy JPA's requirement
101 
    @Id
102 
    @GeneratedValue(strategy = GenerationType.IDENTITY)
103 
    private Long id; // This will not be used in the query but satisfies JPA.
104 
 
105 
    public MTDAchievedTargetModel(int authId, String rbmName, int warehouseId, float monthlyTarget) {
106 
        this.authId = authId;
107 
        this.rbmName = rbmName;
108 
        this.warehouseId = warehouseId;
109 
        this.acheivedMonthlyTarget = monthlyTarget;
110 
    }
111 
 
112 
    public int getAuthId() {
113 
        return authId;
114 
    }
115 
 
116 
    public void setAuthId(int authId) {
117 
        this.authId = authId;
118 
    }
119 
 
120 
    public String getRbmName() {
121 
        return rbmName;
122 
    }
123 
 
124 
    public void setRbmName(String rbmName) {
125 
        this.rbmName = rbmName;
126 
    }
127 
 
128 
    public int getWarehouseId() {
129 
        return warehouseId;
130 
    }
131 
 
132 
    public void setWarehouseId(int warehouseId) {
133 
        this.warehouseId = warehouseId;
134 
    }
135 
 
136 
    public float getAcheivedMonthlyTarget() {
137 
        return acheivedMonthlyTarget;
138 
    }
139 
 
140 
    public void setAcheivedMonthlyTarget(float acheivedMonthlyTarget) {
141 
        this.acheivedMonthlyTarget = acheivedMonthlyTarget;
142 
    }
143 
 
144 
    @Override
145 
    public boolean equals(Object o) {
146 
        if (this == o) return true;
147 
        if (o == null || getClass() != o.getClass()) return false;
148 
        MTDAchievedTargetModel that = (MTDAchievedTargetModel) o;
149 
        return authId == that.authId && warehouseId == that.warehouseId && Float.compare(acheivedMonthlyTarget, that.acheivedMonthlyTarget) == 0 && Objects.equals(rbmName, that.rbmName);
150 
    }
151 
 
152 
    @Override
153 
    public int hashCode() {
154 
        return Objects.hash(rbmName, warehouseId, acheivedMonthlyTarget);
155 
    }
156 
 
157 
    @Override
158 
    public String toString() {
159 
        return "MTDAchievedTargetModel{" +
160 
                "authId=" + authId +
161 
                ", rbmName='" + rbmName + '\'' +
162 
                ", warehouseId=" + warehouseId +
163 
                ", acheivedMonthlyTarget=" + acheivedMonthlyTarget +
164 
                '}';
165 
    }
166 
}