Subversion Repositories SmartDukaan

Rev

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