| Line 3... |
Line 3... |
| 3 |
import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
|
3 |
import com.spice.profitmandi.dao.convertor.LocalDateTimeAttributeConverter;
|
| 4 |
import com.spice.profitmandi.dao.model.BillingDaysAvg;
|
4 |
import com.spice.profitmandi.dao.model.BillingDaysAvg;
|
| 5 |
import com.spice.profitmandi.dao.model.LastSaleDateModel;
|
5 |
import com.spice.profitmandi.dao.model.LastSaleDateModel;
|
| 6 |
import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;
|
6 |
import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;
|
| 7 |
import com.spice.profitmandi.dao.model.PriceDropReportModel;
|
7 |
import com.spice.profitmandi.dao.model.PriceDropReportModel;
|
| - |
|
8 |
import com.spice.profitmandi.model.WarehouseItemQtyModel;
|
| 8 |
import in.shop2020.model.v1.order.OrderStatus;
|
9 |
import in.shop2020.model.v1.order.OrderStatus;
|
| 9 |
import in.shop2020.model.v1.order.TaxType;
|
10 |
import in.shop2020.model.v1.order.TaxType;
|
| 10 |
|
11 |
|
| 11 |
import javax.persistence.*;
|
12 |
import javax.persistence.*;
|
| 12 |
import java.io.Serializable;
|
13 |
import java.io.Serializable;
|
| Line 119... |
Line 120... |
| 119 |
@NamedNativeQuery(name = "Order.selectBillingToDeliverAvg", query = "select o.customer_id, avg(DATEDIFF(o.delivery_timestamp,o.billing_timestamp)) as average from transaction.`order` o join fofo.fofo_store fs on o.customer_id = fs.id where date(o.created_timestamp) between :startDate and :endDate and o.billing_timestamp is not null and o.delivery_timestamp is not null and fs.internal = 0 group by o.customer_id", resultSetMapping = "BillingDaysAvg"),
|
120 |
@NamedNativeQuery(name = "Order.selectBillingToDeliverAvg", query = "select o.customer_id, avg(DATEDIFF(o.delivery_timestamp,o.billing_timestamp)) as average from transaction.`order` o join fofo.fofo_store fs on o.customer_id = fs.id where date(o.created_timestamp) between :startDate and :endDate and o.billing_timestamp is not null and o.delivery_timestamp is not null and fs.internal = 0 group by o.customer_id", resultSetMapping = "BillingDaysAvg"),
|
| 120 |
|
121 |
|
| 121 |
|
122 |
|
| 122 |
@NamedNativeQuery(name = "Order.getLastOrders", query = "select o.customer_id, Max(o.created_timestamp) as maxdate from transaction.`order` o join fofo.fofo_store fs on fs.id = o.customer_id where o.status in (7,9,10,12) and o.delivery_timestamp is not null and fs.internal = 0 group by o.customer_id", resultSetMapping = "LastSaleDays"),
|
123 |
@NamedNativeQuery(name = "Order.getLastOrders", query = "select o.customer_id, Max(o.created_timestamp) as maxdate from transaction.`order` o join fofo.fofo_store fs on fs.id = o.customer_id where o.status in (7,9,10,12) and o.delivery_timestamp is not null and fs.internal = 0 group by o.customer_id", resultSetMapping = "LastSaleDays"),
|
| 123 |
|
124 |
|
| - |
|
125 |
@NamedNativeQuery(name = "Order.itemAvailability", query = "select grouped.item_id, " +
|
| - |
|
126 |
" grouped.vendor_warehouse_id, " +
|
| - |
|
127 |
" sum(grouped.availability) as availability, " +
|
| - |
|
128 |
" sum(grouped.reserved) as reserved, " +
|
| - |
|
129 |
" sum(grouped.availability - grouped.reserved) as net_availability " +
|
| - |
|
130 |
"from (select l.item_id, " +
|
| - |
|
131 |
" o.fulfilmentWarehouseId as vendor_warehouse_id, " +
|
| - |
|
132 |
" 0 as availability, " +
|
| - |
|
133 |
" sum(l.quantity) as reserved " +
|
| - |
|
134 |
" from fofo.fofo_store fs " +
|
| - |
|
135 |
" join transaction.order o on o.customer_id = fs.id " +
|
| - |
|
136 |
" join transaction.lineitem l on l.order_id = o.id " +
|
| - |
|
137 |
" join catalog.item i on i.id = l.item_id " +
|
| - |
|
138 |
" where o.status in (3, 4) " +
|
| - |
|
139 |
" and l.item_id in :itemIds " +
|
| - |
|
140 |
" and o.warehouse_id = :warehouseId " +
|
| - |
|
141 |
" group by l.item_id, o.warehouse_id, o.fulfilmentWarehouseId " +
|
| - |
|
142 |
" union " +
|
| - |
|
143 |
" select ii.itemId AS item_id, " +
|
| - |
|
144 |
" ii.currentWarehouseId, " +
|
| - |
|
145 |
" sum((case " +
|
| - |
|
146 |
" when (s2.type in ('SALE', 'LOST_IN_WAREHOUSE', 'PURCHASE_RETURN', 'MARKED_USED', 'MARKED_BAD')) " +
|
| - |
|
147 |
" then -(s2.quantity) " +
|
| - |
|
148 |
" when (s2.type in ('PURCHASE', 'SALE_RET', 'MARKED_GOOD')) then s2.quantity end)) AS availability, " +
|
| - |
|
149 |
" 0 as reserved " +
|
| - |
|
150 |
" from ((warehouse.scanNew s1 join warehouse.scanNew s2 " +
|
| - |
|
151 |
" on ((s1.inventoryItemId = s2.inventoryItemId))) join warehouse.inventoryItem ii " +
|
| - |
|
152 |
" on ((ii.id = s2.inventoryItemId)) join catalog.item i on i.id = ii.itemId) " +
|
| - |
|
153 |
" where ((s1.type = 'PURCHASE') and (s1.scannedAt >= '2017-07-01')) " +
|
| - |
|
154 |
" and ii.itemId in :itemIds " +
|
| - |
|
155 |
" and ii.physicalWarehouseId = :warehouseId " +
|
| - |
|
156 |
" group by ii.itemId, ii.currentWarehouseId) as grouped " +
|
| - |
|
157 |
"group by item_id, vendor_warehouse_id", resultSetMapping = "itemAvailabilityReservationMapping"),
|
| - |
|
158 |
|
| 124 |
|
159 |
|
| 125 |
})
|
160 |
})
|
| 126 |
|
161 |
|
| 127 |
@SqlResultSetMappings({
|
162 |
@SqlResultSetMappings({
|
| - |
|
163 |
@SqlResultSetMapping(name = "itemAvailabilityReservationMapping", classes = {@ConstructorResult(targetClass = WarehouseItemQtyModel.class,
|
| - |
|
164 |
columns = {@ColumnResult(name = "item_id", type = Integer.class), @ColumnResult(name = "vendor_warehouse_id", type = Integer.class),
|
| - |
|
165 |
@ColumnResult(name = "availability", type = Integer.class),
|
| - |
|
166 |
@ColumnResult(name = "reserved", type = Integer.class), @ColumnResult(name = "net_availability", type = Integer.class)})}),
|
| 128 |
|
167 |
|
| 129 |
@SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "secondary_plan", type = Long.class), @ColumnResult(name = "total_price", type = Long.class), @ColumnResult(name = "auth_id", type = Integer.class), @ColumnResult(name = "commited_timestamp", type = LocalDateTime.class)})}),
|
168 |
@SqlResultSetMapping(name = "partnerSecondaryPlanModel", classes = {@ConstructorResult(targetClass = PartnerSecondaryPlanModel.class, columns = {@ColumnResult(name = "id", type = Integer.class), @ColumnResult(name = "brand", type = String.class), @ColumnResult(name = "secondary_plan", type = Long.class), @ColumnResult(name = "total_price", type = Long.class), @ColumnResult(name = "auth_id", type = Integer.class), @ColumnResult(name = "commited_timestamp", type = LocalDateTime.class)})}),
|
| 130 |
|
169 |
|
| 131 |
@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)})}),
|
170 |
@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)})}),
|
| 132 |
|
171 |
|