Subversion Repositories SmartDukaan

Rev

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