Subversion Repositories SmartDukaan

Rev

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