Subversion Repositories SmartDukaan

Rev

Rev 35147 | Rev 35154 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 35147 Rev 35153
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.BillingDaysAvg;
 4 
import com.spice.profitmandi.dao.model.*;
5 
import com.spice.profitmandi.dao.model.LastSaleDateModel;
 - 
 
6 
import com.spice.profitmandi.dao.model.PartnerSecondaryPlanModel;
 - 
 
7 
import com.spice.profitmandi.dao.model.PriceDropReportModel;
 - 
 
8 
import com.spice.profitmandi.model.WarehouseItemQtyModel;
 5 
import com.spice.profitmandi.model.WarehouseItemQtyModel;
9 
import in.shop2020.model.v1.order.OrderStatus;
 6 
import in.shop2020.model.v1.order.OrderStatus;
10 
import in.shop2020.model.v1.order.TaxType;
 7 
import in.shop2020.model.v1.order.TaxType;
11 
 
 8 
 
12 
import javax.persistence.*;
 9 
import javax.persistence.*;
Line 94... Line 91...
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"),
 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"),
95 
 
 92 
 
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"),
 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"),
97 
 
 94 
 
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"),
 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"),
99 
        @NamedQuery(name = "Order.selectTodayOrdersRBM", query = "select new  com.spice.profitmandi.dao.model.BrandAuthUserPOModel( p.authUserId,\n" +
 96 
       /* @NamedQuery(name = "Order.selectTodayOrdersRBM", query = "select new  com.spice.profitmandi.dao.model.BrandAuthUserPOModel( p.authUserId,\n" +
100 
                "       li.brand,\n" +
 97 
                "       li.brand,\n" +
101 
                "       sum(case\n" +
 98 
                "       sum(case\n" +
102 
                "               when (o.createTimestamp between :startDate and :endDate and o.refundTimestamp is null) then cast(o.totalAmount AS integer )\n" +
 99 
                "               when (o.createTimestamp between :startDate and :endDate and o.refundTimestamp is null) then cast(o.totalAmount AS integer )\n" +
103 
                "               else 0 end),\n" +
 100 
                "               else 0 end),\n" +
104 
                "       sum(case\n" +
 101 
                "       sum(case\n" +
Line 117... Line 114...
117 
                "  and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate))\n" +
 114 
                "  and ((o.createTimestamp between :startDate and :endDate) or (o.billingTimestamp between :startDate and :endDate))\n" +
118 
                "  and fs.internal = false\n" +
 115 
                "  and fs.internal = false\n" +
119 
                "  and p.categoryId = :categoryId\n" +
 116 
                "  and p.categoryId = :categoryId\n" +
120 
                "  and p.escalationType = :escalationType\n" +
 117 
                "  and p.escalationType = :escalationType\n" +
121 
                "  and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')\n" +
 118 
                "  and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')\n" +
122 
                "group by p.authUserId, li.brand"),
 119 
                "group by p.authUserId, li.brand"),*/
123 
 
 120 
 
124 
        @NamedQuery(name = "Order.selectTodayPartnersBilledRBM", query = "select new  com.spice.profitmandi.dao.model.AuthIdPartnerCountModel( p.authUserId,\n" +
 121 
        @NamedQuery(name = "Order.selectTodayPartnersBilledRBM", query = "select new  com.spice.profitmandi.dao.model.AuthIdPartnerCountModel( p.authUserId,\n" +
125 
                "       cast (count(distinct o.retailerId) as int))\n" +
 122 
                "       cast (count(distinct o.retailerId) as int))\n" +
126 
                "from Order o\n" +
 123 
                "from Order o\n" +
127 
                "         join LineItem li\n" +
 124 
                "         join LineItem li\n" +
Line 269... Line 266...
269 
                "        and ii.itemId in :itemIds " +
 266 
                "        and ii.itemId in :itemIds " +
270 
                "        and ii.physicalWarehouseId = :warehouseId " +
 267 
                "        and ii.physicalWarehouseId = :warehouseId " +
271 
                "      group by ii.itemId, ii.currentWarehouseId) as grouped " +
 268 
                "      group by ii.itemId, ii.currentWarehouseId) as grouped " +
272 
                "group by item_id, vendor_warehouse_id", resultSetMapping = "itemAvailabilityReservationMapping"),
 269 
                "group by item_id, vendor_warehouse_id", resultSetMapping = "itemAvailabilityReservationMapping"),
273 
 
 270 
 
- 
 
 271 
        @NamedNativeQuery(
- 
 
 272 
                name = "Order.selectTodayOrdersRBM",
- 
 
 273 
                query = "SELECT region_partner_map.auth_user_id as auth_id,\n" +
- 
 
 274 
                        "       li.brand,\n" +
- 
 
 275 
                        "       SUM(\n" +
- 
 
 276 
                        "               CASE\n" +
- 
 
 277 
                        "                   WHEN (o.created_timestamp BETWEEN :startDate AND :endDate and o.refund_timestamp is null)\n" +
- 
 
 278 
                        "                       AND o.refund_timestamp IS NULL\n" +
- 
 
 279 
                        "                       THEN o.total_amount\n" +
- 
 
 280 
                        "                   ELSE 0\n" +
- 
 
 281 
                        "                   END\n" +
- 
 
 282 
                        "           )                         AS today_po_value,\n" +
- 
 
 283 
                        "       SUM(\n" +
- 
 
 284 
                        "               CASE\n" +
- 
 
 285 
                        "                   WHEN o.billing_timestamp IS NOT NULL\n" +
- 
 
 286 
                        "                       AND (o.created_timestamp BETWEEN :startDate AND :endDate)\n" +
- 
 
 287 
                        "                       THEN o.total_amount\n" +
- 
 
 288 
                        "                   ELSE 0\n" +
- 
 
 289 
                        "                   END\n" +
- 
 
 290 
                        "           )                         AS today_billed_value,\n" +
- 
 
 291 
                        "       SUM(\n" +
- 
 
 292 
                        "               CASE\n" +
- 
 
 293 
                        "                   WHEN o.billing_timestamp IS NOT NULL\n" +
- 
 
 294 
                        "                       THEN o.total_amount\n" +
- 
 
 295 
                        "                   ELSE 0\n" +
- 
 
 296 
                        "                   END\n" +
- 
 
 297 
                        "           )                         AS total_billed_value,\n" +
- 
 
 298 
                        "       COUNT(DISTINCT o.customer_id) AS partner_count\n" +
- 
 
 299 
                        "FROM transaction.`order` o\n" +
- 
 
 300 
                        "         JOIN transaction.lineitem li\n" +
- 
 
 301 
                        "              ON o.id = li.order_id\n" +
- 
 
 302 
                        "         JOIN fofo.fofo_store fs\n" +
- 
 
 303 
                        "              ON fs.id = o.customer_id\n" +
- 
 
 304 
                        "         LEFT JOIN (SELECT ps.position_id,\n" +
- 
 
 305 
                        "                           p.auth_user_id,\n" +
- 
 
 306 
                        "                           p.category_id,\n" +
- 
 
 307 
                        "                           p.escalation_type,\n" +
- 
 
 308 
                        "                           COALESCE(pr.fofo_id, ps.partner_id) AS final_retailer_id,\n" +
- 
 
 309 
                        "                           ps.region_id\n" +
- 
 
 310 
                        "                    FROM cs.partner_position ps\n" +
- 
 
 311 
                        "                             LEFT JOIN cs.partner_region pr\n" +
- 
 
 312 
                        "                                       ON ps.partner_id = 0 AND ps.region_id = pr.region_id\n" +
- 
 
 313 
                        "                             left join cs.position p on p.id = ps.position_id\n" +
- 
 
 314 
                        "                    where p.category_id = :categoryId \n" +
- 
 
 315 
                        "                      and p.escalation_type = :escalationType) AS region_partner_map\n" +
- 
 
 316 
                        "                   ON region_partner_map.final_retailer_id = fs.id\n" +
- 
 
 317 
                        "\n" +
- 
 
 318 
                        "WHERE o.refund_timestamp IS NULL\n" +
- 
 
 319 
                        "  AND (\n" +
- 
 
 320 
                        "        (o.created_timestamp BETWEEN :startDate AND :endDate)\n" +
- 
 
 321 
                        "        OR (o.billing_timestamp BETWEEN :startDate AND :endDate)\n" +
- 
 
 322 
                        "    )\n" +
- 
 
 323 
                        "  AND fs.internal = FALSE\n" +
- 
 
 324 
                        "  AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')\n" +
- 
 
 325 
                        "GROUP BY region_partner_map.auth_user_id, li.brand\n" +
- 
 
 326 
                        "ORDER BY region_partner_map.auth_user_id, li.brand;\n" +
- 
 
 327 
                        "\n" +
- 
 
 328 
                        "\n",
- 
 
 329 
                resultSetMapping = "TodayPoByAuthUsers"
- 
 
 330 
        )
- 
 
 331 
 
- 
 
 332 
 
274 
 
 333 
 
275 
})
 334 
})
276 
 
 335 
 
277 
@SqlResultSetMappings({
 336 
@SqlResultSetMappings({
278 
        @SqlResultSetMapping(name = "itemAvailabilityReservationMapping", classes = {@ConstructorResult(targetClass = WarehouseItemQtyModel.class,
 337 
        @SqlResultSetMapping(name = "itemAvailabilityReservationMapping", classes = {@ConstructorResult(targetClass = WarehouseItemQtyModel.class,
Line 284... Line 343...
284 
 
 343 
 
285 
        @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)})}),
 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)})}),
286 
 
 345 
 
287 
        @SqlResultSetMapping(name = "BillingDaysAvg", classes = {@ConstructorResult(targetClass = BillingDaysAvg.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "average", type = Long.class)})}),
 346 
        @SqlResultSetMapping(name = "BillingDaysAvg", classes = {@ConstructorResult(targetClass = BillingDaysAvg.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "average", type = Long.class)})}),
288 
 
 347 
 
289 
        @SqlResultSetMapping(name = "LastSaleDays", classes = {@ConstructorResult(targetClass = LastSaleDateModel.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "maxdate", type = LocalDateTime.class)})})
 348 
        @SqlResultSetMapping(name = "LastSaleDays", classes = {@ConstructorResult(targetClass = LastSaleDateModel.class, columns = {@ColumnResult(name = "customer_id", type = Integer.class), @ColumnResult(name = "maxdate", type = LocalDateTime.class)})}),
290 
 
 349 
 
- 
 
 350 
        @SqlResultSetMapping(name = "TodayPoByAuthUsers",
- 
 
 351 
                classes = {@ConstructorResult(targetClass = BrandAuthUserPOModel.class,
- 
 
 352 
                        columns = {
- 
 
 353 
                                @ColumnResult(name = "auth_id", type = Integer.class),
- 
 
 354 
                                @ColumnResult(name = "brand", type = String.class),
- 
 
 355 
                                @ColumnResult(name = "today_po_value", type = Long.class),
- 
 
 356 
                                @ColumnResult(name = "today_billed_value", type = Long.class),
- 
 
 357 
                                @ColumnResult(name = "total_billed_value", type = Long.class),
- 
 
 358 
                                @ColumnResult(name = "partner_count", type = Integer.class)
- 
 
 359 
                        }
- 
 
 360 
                )}
- 
 
 361 
        )
291 
})
 362 
})
292 
 
 363 
 
293 
public class Order implements Serializable {
 364 
public class Order implements Serializable {
294 
 
 365 
 
295 
    private static final long serialVersionUID = 1L;
 366 
    private static final long serialVersionUID = 1L;