Subversion Repositories SmartDukaan

Rev

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