| Line 1... |
Line 1... |
| 1 |
package com.spice.profitmandi.dao.entity.transaction;
|
1 |
package com.spice.profitmandi.dao.entity.transaction;
|
| 2 |
|
2 |
|
| 3 |
import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
|
3 |
import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
|
| 4 |
import com.spice.profitmandi.dao.model.*;
|
4 |
import com.spice.profitmandi.dao.model.BillingDaysAvg;
|
| - |
|
5 |
import com.spice.profitmandi.dao.model.LastSaleDateModel;
|
| - |
|
6 |
import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;
|
| - |
|
7 |
import com.spice.profitmandi.dao.model.PriceDropReportModel;
|
| 5 |
import com.spice.profitmandi.model.WarehouseItemQtyModel;
|
8 |
import com.spice.profitmandi.model.WarehouseItemQtyModel;
|
| 6 |
import in.shop2020.model.v1.order.OrderStatus;
|
9 |
import in.shop2020.model.v1.order.OrderStatus;
|
| 7 |
import in.shop2020.model.v1.order.TaxType;
|
10 |
import in.shop2020.model.v1.order.TaxType;
|
| 8 |
|
11 |
|
| 9 |
import javax.persistence.*;
|
12 |
import javax.persistence.*;
|
| Line 91... |
Line 94... |
| 91 |
@NamedQuery(name = "Order.selectGrnPendingOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)" + " from FofoStore fs join Order o on fs.id = o.retailerId " + " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId " + " join Item i on (i.id = tl.itemId ) where fs.active = true and fs.internal = false and" + " o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null" + " and i.catalogItemId = :catalogItemId and fs.id = :fofoId group by i.catalogItemId, fs.id"),
|
94 |
@NamedQuery(name = "Order.selectGrnPendingOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)" + " from FofoStore fs join Order o on fs.id = o.retailerId " + " join LineItem li on o.id = li.orderId join TagListing tl on tl.itemId = li.itemId " + " join Item i on (i.id = tl.itemId ) where fs.active = true and fs.internal = false and" + " o.billingTimestamp is not null and o.refundTimestamp is null and o.partnerGrnTimestamp is null" + " and i.catalogItemId = :catalogItemId and fs.id = :fofoId group by i.catalogItemId, fs.id"),
|
| 92 |
|
95 |
|
| 93 |
@NamedQuery(name = "Order.selectPendingIndentOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)" + " from FofoStore fs join Order o on fs.id = o.retailerId" + " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId " + " join Item i on i.id = tl.itemId where " + " fs.active = true and fs.internal = false and o.status in (3,4)and i.catalogItemId = :catalogItemId and fs.id = :fofoId group by i.catalogItemId, fs.id"),
|
96 |
@NamedQuery(name = "Order.selectPendingIndentOrderQtyByCatalogId", query = "select sum(case when li.quantity is null then 0 else li.quantity end)" + " from FofoStore fs join Order o on fs.id = o.retailerId" + " join LineItem li on o.id = li.orderId " + " join TagListing tl on tl.itemId = li.itemId " + " join Item i on i.id = tl.itemId where " + " fs.active = true and fs.internal = false and o.status in (3,4)and i.catalogItemId = :catalogItemId and fs.id = :fofoId group by i.catalogItemId, fs.id"),
|
| 94 |
|
97 |
|
| 95 |
@NamedQuery(name = "Order.selectTodayOrders", query = "select new com.spice.profitmandi.dao.model.BrandRegionPOModel(" + " case when (pr.regionId is not null) then pr.regionId else fs.warehouseId end , li.brand, " + " sum(case when (o.createTimestamp between :startDate and :endDate and o.refundTimestamp is null) then cast(o.totalAmount AS integer ) else 0 end)," + " sum(case when (o.billingTimestamp is not null and (o.createTimestamp between :startDate and :endDate)) then cast(o.totalAmount AS integer) else 0 end)," + " sum(case when o.billingTimestamp is not null then cast(o.totalAmount AS integer ) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId" + " left join PartnerRegion pr on pr.fofoId=o.retailerId and pr.regionId=18" + " where o.refundTimestamp is null and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate)) and fs.internal = false and li.brand not in ('Smartdukaan','Dummy')" + " group by case when (pr.regionId is not null) then pr.regionId else fs.warehouseId end, li.brand"),
|
98 |
@NamedQuery(name = "Order.selectTodayOrders", query = "select new com.spice.profitmandi.dao.model.BrandRegionPOModel(" + " case when (pr.regionId is not null) then pr.regionId else fs.warehouseId end , li.brand, " + " sum(case when (o.createTimestamp between :startDate and :endDate and o.refundTimestamp is null) then cast(o.totalAmount AS integer ) else 0 end)," + " sum(case when (o.billingTimestamp is not null and (o.createTimestamp between :startDate and :endDate)) then cast(o.totalAmount AS integer) else 0 end)," + " sum(case when o.billingTimestamp is not null then cast(o.totalAmount AS integer ) else 0 end))" + " from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId" + " left join PartnerRegion pr on pr.fofoId=o.retailerId and pr.regionId=18" + " where o.refundTimestamp is null and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate)) and fs.internal = false and li.brand not in ('Smartdukaan','Dummy')" + " group by case when (pr.regionId is not null) then pr.regionId else fs.warehouseId end, li.brand"),
|
| 96 |
/* @NamedQuery(name = "Order.selectTodayOrdersRBM", query = "select new com.spice.profitmandi.dao.model.BrandAuthUserPOModel( p.authUserId,\n" +
|
99 |
@NamedQuery(name = "Order.selectTodayOrdersRBM", query = "select new com.spice.profitmandi.dao.model.BrandAuthUserPOModel( p.authUserId,\n" +
|
| 97 |
" li.brand,\n" +
|
100 |
" li.brand,\n" +
|
| 98 |
" sum(case\n" +
|
101 |
" sum(case\n" +
|
| 99 |
" when (o.createTimestamp between :startDate and :endDate and o.refundTimestamp is null) then cast(o.totalAmount AS integer )\n" +
|
102 |
" when (o.createTimestamp between :startDate and :endDate and o.refundTimestamp is null) then cast(o.totalAmount AS integer )\n" +
|
| 100 |
" else 0 end),\n" +
|
103 |
" else 0 end),\n" +
|
| 101 |
" sum(case\n" +
|
104 |
" sum(case\n" +
|
| Line 114... |
Line 117... |
| 114 |
" and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate))\n" +
|
117 |
" and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate))\n" +
|
| 115 |
" and fs.internal = false\n" +
|
118 |
" and fs.internal = false\n" +
|
| 116 |
" and p.categoryId = :categoryId\n" +
|
119 |
" and p.categoryId = :categoryId\n" +
|
| 117 |
" and p.escalationType = :escalationType\n" +
|
120 |
" and p.escalationType = :escalationType\n" +
|
| 118 |
" and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')\n" +
|
121 |
" and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')\n" +
|
| 119 |
"group by p.authUserId, li.brand"),*/
|
122 |
"group by p.authUserId, li.brand"),
|
| 120 |
|
123 |
|
| 121 |
@NamedQuery(name = "Order.selectTodayPartnersBilledRBM", query = "select new com.spice.profitmandi.dao.model.AuthIdPartnerCountModel( p.authUserId,\n" +
|
124 |
@NamedQuery(name = "Order.selectTodayPartnersBilledRBM", query = "select new com.spice.profitmandi.dao.model.AuthIdPartnerCountModel( p.authUserId,\n" +
|
| 122 |
" cast (count(distinct o.retailerId) as int))\n" +
|
125 |
" cast (count(distinct o.retailerId) as int))\n" +
|
| 123 |
"from Order o\n" +
|
126 |
"from Order o\n" +
|
| 124 |
" join LineItem li\n" +
|
127 |
" join LineItem li\n" +
|
| Line 266... |
Line 269... |
| 266 |
" and ii.itemId in :itemIds " +
|
269 |
" and ii.itemId in :itemIds " +
|
| 267 |
" and ii.physicalWarehouseId = :warehouseId " +
|
270 |
" and ii.physicalWarehouseId = :warehouseId " +
|
| 268 |
" group by ii.itemId, ii.currentWarehouseId) as grouped " +
|
271 |
" group by ii.itemId, ii.currentWarehouseId) as grouped " +
|
| 269 |
"group by item_id, vendor_warehouse_id", resultSetMapping = "itemAvailabilityReservationMapping"),
|
272 |
"group by item_id, vendor_warehouse_id", resultSetMapping = "itemAvailabilityReservationMapping"),
|
| 270 |
|
273 |
|
| 271 |
@NamedNativeQuery(
|
274 |
/*@NamedNativeQuery(
|
| 272 |
name = "Order.selectTodayOrdersRBM",
|
275 |
name = "Order.selectTodayOrdersRBM",
|
| 273 |
query = "SELECT region_partner_map.auth_user_id as auth_id,\n" +
|
276 |
query = "SELECT region_partner_map.auth_user_id as auth_id,\n" +
|
| 274 |
" li.brand,\n" +
|
277 |
" li.brand,\n" +
|
| 275 |
" SUM(\n" +
|
278 |
" SUM(\n" +
|
| 276 |
" CASE\n" +
|
279 |
" CASE\n" +
|
| Line 325... |
Line 328... |
| 325 |
"GROUP BY region_partner_map.auth_user_id, li.brand\n" +
|
328 |
"GROUP BY region_partner_map.auth_user_id, li.brand\n" +
|
| 326 |
"ORDER BY region_partner_map.auth_user_id, li.brand;\n" +
|
329 |
"ORDER BY region_partner_map.auth_user_id, li.brand;\n" +
|
| 327 |
"\n" +
|
330 |
"\n" +
|
| 328 |
"\n",
|
331 |
"\n",
|
| 329 |
resultSetMapping = "TodayPoByAuthUsers"
|
332 |
resultSetMapping = "TodayPoByAuthUsers"
|
| 330 |
)
|
333 |
)*/
|
| 331 |
|
334 |
|
| 332 |
|
335 |
|
| 333 |
|
336 |
|
| 334 |
})
|
337 |
})
|
| 335 |
|
338 |
|
| Line 344... |
Line 347... |
| 344 |
@SqlResultSetMapping(name = "PriceDropReportModel", classes = {@ConstructorResult(targetClass = PriceDropReportModel.class, columns = {@ColumnResult(name = "code", type = String.class), @ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "model_name", type = String.class), @ColumnResult(name = "model_number", type = String.class), @ColumnResult(name = "affected_on", type = LocalDateTime.class), @ColumnResult(name = "amount", type = Float.class), @ColumnResult(name = "partner_payout", type = Float.class), @ColumnResult(name = "imei", type = String.class), @ColumnResult(name = "status", type = String.class), @ColumnResult(name = "update_timestamp", type = LocalDateTime.class), @ColumnResult(name = "rejection_reason", type = String.class), @ColumnResult(name = "credit_timestamp", type = LocalDateTime.class), @ColumnResult(name = "reject_timestamp", type = LocalDateTime.class)})}),
|
347 |
@SqlResultSetMapping(name = "PriceDropReportModel", classes = {@ConstructorResult(targetClass = PriceDropReportModel.class, columns = {@ColumnResult(name = "code", type = String.class), @ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "model_name", type = String.class), @ColumnResult(name = "model_number", type = String.class), @ColumnResult(name = "affected_on", type = LocalDateTime.class), @ColumnResult(name = "amount", type = Float.class), @ColumnResult(name = "partner_payout", type = Float.class), @ColumnResult(name = "imei", type = String.class), @ColumnResult(name = "status", type = String.class), @ColumnResult(name = "update_timestamp", type = LocalDateTime.class), @ColumnResult(name = "rejection_reason", type = String.class), @ColumnResult(name = "credit_timestamp", type = LocalDateTime.class), @ColumnResult(name = "reject_timestamp", type = LocalDateTime.class)})}),
|
| 345 |
|
348 |
|
| 346 |
@SqlResultSetMapping(name = "BillingDaysAvg", classes = {@ConstructorResult(targetClass = BillingDaysAvg.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "average", type = Long.class)})}),
|
349 |
@SqlResultSetMapping(name = "BillingDaysAvg", classes = {@ConstructorResult(targetClass = BillingDaysAvg.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "average", type = Long.class)})}),
|
| 347 |
|
350 |
|
| 348 |
@SqlResultSetMapping(name = "LastSaleDays", classes = {@ConstructorResult(targetClass = LastSaleDateModel.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "maxdate", type = LocalDateTime.class)})}),
|
351 |
@SqlResultSetMapping(name = "LastSaleDays", classes = {@ConstructorResult(targetClass = LastSaleDateModel.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "maxdate", type = LocalDateTime.class)})}),
|
| 349 |
|
352 |
/*
|
| 350 |
@SqlResultSetMapping(name = "TodayPoByAuthUsers",
|
353 |
@SqlResultSetMapping(name = "TodayPoByAuthUsers",
|
| 351 |
classes = {@ConstructorResult(targetClass = BrandAuthUserPOModel.class,
|
354 |
classes = {@ConstructorResult(targetClass = BrandAuthUserPOModel.class,
|
| 352 |
columns = {
|
355 |
columns = {
|
| 353 |
@ColumnResult(name = "auth_id", type = Integer.class),
|
356 |
@ColumnResult(name = "auth_id", type = int.class),
|
| 354 |
@ColumnResult(name = "brand", type = String.class),
|
357 |
@ColumnResult(name = "brand", type = String.class),
|
| 355 |
@ColumnResult(name = "today_po_value", type = Long.class),
|
358 |
@ColumnResult(name = "today_po_value", type = long.class),
|
| 356 |
@ColumnResult(name = "today_billed_value", type = Long.class),
|
359 |
@ColumnResult(name = "today_billed_value", type = long.class),
|
| 357 |
@ColumnResult(name = "total_billed_value", type = Long.class),
|
360 |
@ColumnResult(name = "total_billed_value", type = long.class),
|
| 358 |
@ColumnResult(name = "partner_count", type = Integer.class)
|
361 |
@ColumnResult(name = "partner_count", type = int.class)
|
| - |
|
362 |
|
| 359 |
}
|
363 |
}
|
| 360 |
)}
|
364 |
)}
|
| 361 |
)
|
365 |
)*/
|
| 362 |
})
|
366 |
})
|
| 363 |
|
367 |
|
| 364 |
public class Order implements Serializable {
|
368 |
public class Order implements Serializable {
|
| 365 |
|
369 |
|
| 366 |
private static final long serialVersionUID = 1L;
|
370 |
private static final long serialVersionUID = 1L;
|