Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
34055 ranu 1 
package com.spice.profitmandi.dao.model;
2 
 
3 
import javax.persistence.*;
4 
import java.util.Objects;
5 
 
6 
 
7 
@Entity
8 
@NamedNativeQueries({
9 
        @NamedNativeQuery(name = "Aging.SoldAgingModel",
10 
                query = "SELECT" +
11 
                        "       a.Rbm_Name," +
34098 ranu 12 
                        "       SUM(CASE WHEN b.status = 'SLOWMOVING' THEN b.selling_price ELSE 0 END) AS SLOWMOVING_Billed," +
13 
                        "       SUM(CASE  WHEN b.status = 'RUNNING' THEN b.selling_price  ELSE 0 END) AS RUNNING_Billed," +
14 
                        "       SUM(CASE WHEN b.status = 'FASTMOVING' THEN b.selling_price  ELSE 0 END)  AS FASTMOVING_Billed," +
34074 ranu 15 
                        "       SUM(CASE WHEN b.status = 'HID' THEN b.selling_price ELSE 0 END) AS HID_Billed," +
16 
                        "       COALESCE(SUM(CASE WHEN b.status IS NULL THEN b.selling_price ELSE 0 END),0) AS Other_Billed," +
17 
                        "      COALESCE(SUM(b.selling_price),0) AS Total_Amount" +
34055 ranu 18 
                        " FROM (SELECT au.id                                    AS auth_id," +
19 
                        "             CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name," +
20 
                        "             fs.id                                    AS fofo_id" +
21 
                        "      FROM auth.auth_user au" +
22 
                        "               JOIN cs.position p ON p.auth_user_id = au.id" +
23 
                        "               JOIN cs.partner_position pp ON pp.position_id = p.id" +
24 
                        "               JOIN fofo.fofo_store fs ON fs.id = pp.partner_id" +
25 
                        "      WHERE pp.partner_id != 0" +
26 
                        "        AND p.category_id = 18" +
27 
                        "        AND p.escalation_type = 'L1'" +
28 
                        "        AND fs.active = 1" +
29 
                        "        and fs.internal = false" +
30 
                        "      UNION ALL" +
31 
                        "      SELECT au.id                                    AS auth_id," +
32 
                        "             CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name," +
33 
                        "             fs.id                                    AS fofo_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" +
40 
                        "      WHERE pp.partner_id = 0" +
41 
                        "        AND p.category_id = 18" +
42 
                        "        AND fs.active = 1" +
43 
                        "        and fs.internal = false" +
44 
                        "        AND p.escalation_type = 'L1') a" +
34074 ranu 45 
                        "      LEFT JOIN (SELECT o.customer_id, tl.selling_price, cc.status from transaction.order o" +
34055 ranu 46 
                        "      JOIN transaction.lineitem li ON li.order_id = o.id AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
36934 ranu 47 
                        "      JOIN catalog.item ci ON ci.id = li.item_id AND ci.category = 10006" +
34055 ranu 48 
                        "      JOIN catalog.tag_listing tl ON tl.item_id = ci.id" +
34074 ranu 49 
                        "      LEFT JOIN catalog.catagoriesd_catalog cc ON cc.catalog_id = ci.catalog_item_id" +
34055 ranu 50 
                        "      AND (cc.start_date <= o.created_timestamp AND  (cc.end_date IS NULL OR cc.end_date > o.created_timestamp))" +
51 
                        "      JOIN warehouse.scanNew s ON s.orderId = o.id" +
52 
                        "      JOIN warehouse.inventoryItem ii ON ii.id = s.inventoryItemId" +
53 
                        "      JOIN warehouse.inventoryItem ii2 ON ii.serialNumber = ii2.serialNumber AND ii2.lastScanType != 'PURCHASE_RETURN'" +
54 
                        "      JOIN warehouse.purchase p ON p.id = ii2.purchaseId" +
55 
                        "      JOIN warehouse.purchaseorder po ON po.id = p.purchaseOrder_id" +
34068 ranu 56 
                        "      JOIN warehouse.invoice inv ON p.invoice_id = inv.id AND po.supplierId = inv.supplierId AND po.warehouseId = ii2.physicalWarehouseId" +
34055 ranu 57 
                        "      JOIN warehouse.supplier su ON su.id = inv.supplierId AND su.internal = 0" +
58 
                        "" +
34056 ranu 59 
                        " where ((o.created_timestamp >= :startDate AND o.created_timestamp < :endDate) AND (o.billing_timestamp >= :startDate AND o.billing_timestamp < :endDate) AND o.refund_timestamp IS NULL)" +
36301 ranu 60 
                        " and inv.invoiceDate <  DATE_SUB(o.billing_timestamp, INTERVAL 15 DAY)) b on b.customer_id = a.fofo_id" +
34055 ranu 61 
                        " GROUP BY a.auth_id, a.Rbm_Name",
62 
                resultSetMapping = "SoldAgingModel"),
63 
 
64 
})
65 
 
66 
@SqlResultSetMappings({
67 
 
68 
        @SqlResultSetMapping(name = "SoldAgingModel",
69 
                classes = {@ConstructorResult(targetClass = Sold15daysOldAgingModel.class,
70 
                        columns = {
71 
                                @ColumnResult(name = "Rbm_Name", type = String.class),
35479 amit 72 
                                @ColumnResult(name = "SLOWMOVING_Billed", type = Long.class),
73 
                                @ColumnResult(name = "RUNNING_Billed", type = Long.class),
74 
                                @ColumnResult(name = "FASTMOVING_Billed", type = Long.class),
75 
                                @ColumnResult(name = "HID_Billed", type = Long.class),
76 
                                @ColumnResult(name = "Other_Billed", type = Long.class),
77 
                                @ColumnResult(name = "Total_Amount", type = Long.class),
34055 ranu 78 
                        }
79 
                )}
80 
        )
81 
 
82 
})
83 
 
84 
public class Sold15daysOldAgingModel {
85 
    String rbmName;
34098 ranu 86 
    long slowmovingBilled;
34055 ranu 87 
    long runningBilled;
88 
    long fastmovingBilled;
89 
    long hidBilled;
90 
    long othersBilled;
91 
    long totalBilled;
92 
 
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 
 
34098 ranu 98 
    public Sold15daysOldAgingModel(String rbmName, long slowmovingBilled, long runningBilled, long fastmovingBilled, long hidBilled, long othersBilled, long totalBilled) {
34055 ranu 99 
        this.rbmName = rbmName;
34098 ranu 100 
        this.slowmovingBilled = slowmovingBilled;
34055 ranu 101 
        this.runningBilled = runningBilled;
102 
        this.fastmovingBilled = fastmovingBilled;
103 
        this.hidBilled = hidBilled;
104 
        this.othersBilled = othersBilled;
105 
        this.totalBilled = totalBilled;
106 
    }
107 
 
108 
    public String getRbmName() {
109 
        return rbmName;
110 
    }
111 
 
112 
    public void setRbmName(String rbmName) {
113 
        this.rbmName = rbmName;
114 
    }
115 
 
34098 ranu 116 
    public long getSlowmovingBilled() {
117 
        return slowmovingBilled;
34055 ranu 118 
    }
119 
 
34098 ranu 120 
    public void setSlowmovingBilled(long slowmovingBilled) {
121 
        this.slowmovingBilled = slowmovingBilled;
34055 ranu 122 
    }
123 
 
124 
    public long getRunningBilled() {
125 
        return runningBilled;
126 
    }
127 
 
128 
    public void setRunningBilled(long runningBilled) {
129 
        this.runningBilled = runningBilled;
130 
    }
131 
 
132 
    public long getFastmovingBilled() {
133 
        return fastmovingBilled;
134 
    }
135 
 
136 
    public void setFastmovingBilled(long fastmovingBilled) {
137 
        this.fastmovingBilled = fastmovingBilled;
138 
    }
139 
 
140 
    public long getHidBilled() {
141 
        return hidBilled;
142 
    }
143 
 
144 
    public void setHidBilled(long hidBilled) {
145 
        this.hidBilled = hidBilled;
146 
    }
147 
 
148 
    public long getOthersBilled() {
149 
        return othersBilled;
150 
    }
151 
 
152 
    public void setOthersBilled(long othersBilled) {
153 
        this.othersBilled = othersBilled;
154 
    }
155 
 
156 
    public long getTotalBilled() {
157 
        return totalBilled;
158 
    }
159 
 
160 
    public void setTotalBilled(long totalBilled) {
161 
        this.totalBilled = totalBilled;
162 
    }
163 
 
164 
    @Override
165 
    public String toString() {
166 
        return "Sold15daysAgingModel{" +
167 
                "rbmName='" + rbmName + '\'' +
34098 ranu 168 
                ", slowmovingBilled=" + slowmovingBilled +
34055 ranu 169 
                ", runningBilled=" + runningBilled +
170 
                ", fastmovingBilled=" + fastmovingBilled +
171 
                ", hidBilled=" + hidBilled +
172 
                ", othersBilled=" + othersBilled +
173 
                ", totalBilled=" + totalBilled +
174 
                '}';
175 
    }
176 
 
177 
    @Override
178 
    public boolean equals(Object o) {
179 
        if (this == o) return true;
180 
        if (o == null || getClass() != o.getClass()) return false;
181 
        Sold15daysOldAgingModel that = (Sold15daysOldAgingModel) o;
34098 ranu 182 
        return slowmovingBilled == that.slowmovingBilled && runningBilled == that.runningBilled && fastmovingBilled == that.fastmovingBilled && hidBilled == that.hidBilled && othersBilled == that.othersBilled && totalBilled == that.totalBilled && Objects.equals(rbmName, that.rbmName);
34055 ranu 183 
    }
184 
 
185 
    @Override
186 
    public int hashCode() {
34098 ranu 187 
        return Objects.hash(rbmName, slowmovingBilled, runningBilled, fastmovingBilled, hidBilled, othersBilled, totalBilled);
34055 ranu 188 
    }
189 
}