Subversion Repositories SmartDukaan

Rev

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

Rev 35155 Rev 35172
Line 322... Line 322...
322
                        "  AND fs.internal = FALSE" +
322
                        "  AND fs.internal = FALSE" +
323
                        "  AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
323
                        "  AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
324
                        "GROUP BY region_partner_map.auth_user_id, li.brand" +
324
                        "GROUP BY region_partner_map.auth_user_id, li.brand" +
325
                        " ORDER BY region_partner_map.auth_user_id, li.brand",
325
                        " ORDER BY region_partner_map.auth_user_id, li.brand",
326
                resultSetMapping = "TodayPoByAuthUsers"
326
                resultSetMapping = "TodayPoByAuthUsers"
-
 
327
        ),
-
 
328
 
-
 
329
        @NamedNativeQuery(
-
 
330
                name = "Order.selectTodayOrdersModelWise",
-
 
331
                query = "SELECT o.created_timestamp,o.customer_name,fs.code,fs.warehouse_id, li.brand,concat(i.model_name, ' ', i.model_number) as modal,i.catalog_item_id ,coalesce(cc.status, '-') as status,sum(li.quantity) as quantity," +
-
 
332
                        "       SUM(" +
-
 
333
                        "               CASE" +
-
 
334
                        "                   WHEN (o.created_timestamp BETWEEN :startDate AND :endDate and o.refund_timestamp is null)" +
-
 
335
                        "                       AND o.refund_timestamp IS NULL" +
-
 
336
                        "                       THEN o.total_amount" +
-
 
337
                        "                   ELSE 0" +
-
 
338
                        "                   END" +
-
 
339
                        "           )    AS today_po_value," +
-
 
340
                        "       SUM(" +
-
 
341
                        "               CASE" +
-
 
342
                        "                   WHEN o.billing_timestamp IS NOT NULL" +
-
 
343
                        "                       AND (o.created_timestamp BETWEEN :startDate AND :endDate)" +
-
 
344
                        "                       THEN o.total_amount" +
-
 
345
                        "                   ELSE 0" +
-
 
346
                        "                   END" +
-
 
347
                        "           ) AS today_billed_value" +
-
 
348
                        " FROM transaction.`order` o" +
-
 
349
                        "         JOIN transaction.lineitem li" +
-
 
350
                        "              ON o.id = li.order_id" +
-
 
351
                        "         JOIN fofo.fofo_store fs ON fs.id = o.customer_id" +
-
 
352
                        " join catalog.item i on i.id = li.item_id" +
-
 
353
                        " join catalog.catagoriesd_catalog cc on i.catalog_item_id = cc.catalog_id AND cc.end_date IS NULL" +
-
 
354
                        " WHERE o.refund_timestamp IS NULL" +
-
 
355
                        "  AND (" +
-
 
356
                        "        (o.created_timestamp BETWEEN :startDate AND :endDate)" +
-
 
357
                        "        OR (o.billing_timestamp BETWEEN :startDate AND :endDate)" +
-
 
358
                        "    )" +
-
 
359
                        "  AND fs.internal = FALSE" +
-
 
360
                        "  AND fs.active = TRUE" +
-
 
361
                        "  AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
-
 
362
                        "GROUP BY o.customer_id, i.catalog_item_id",
-
 
363
                resultSetMapping = "TodayPoByCatalog"
327
        )
364
        )
328
 
365
 
329
 
366
 
330
 
367
 
331
})
368
})
Line 353... Line 390...
353
                                @ColumnResult(name = "today_billed_value", type = Long.class),
390
                                @ColumnResult(name = "today_billed_value", type = Long.class),
354
                                @ColumnResult(name = "total_billed_value", type = Long.class),
391
                                @ColumnResult(name = "total_billed_value", type = Long.class),
355
                                @ColumnResult(name = "partner_count", type = Integer.class)
392
                                @ColumnResult(name = "partner_count", type = Integer.class)
356
                        }
393
                        }
357
                )
394
                )
-
 
395
        ),
-
 
396
 
-
 
397
        @SqlResultSetMapping(name = "TodayPoByCatalog",
-
 
398
                classes = @ConstructorResult(
-
 
399
                        targetClass = ModelWisePartnerPoModel.class,
-
 
400
                        columns = {
-
 
401
                                @ColumnResult(name = "created_timestamp", type = LocalDateTime.class),
-
 
402
                                @ColumnResult(name = "customer_name", type = String.class),
-
 
403
                                @ColumnResult(name = "code", type = String.class),
-
 
404
                                @ColumnResult(name = "warehouse_id", type = Integer.class),
-
 
405
                                @ColumnResult(name = "brand", type = String.class),
-
 
406
                                @ColumnResult(name = "modal", type = String.class),
-
 
407
                                @ColumnResult(name = "catalog_item_id", type = Integer.class),
-
 
408
                                @ColumnResult(name = "status", type = String.class),
-
 
409
                                @ColumnResult(name = "quantity", type = Long.class),
-
 
410
                                @ColumnResult(name = "today_po_value", type = Long.class),
-
 
411
                                @ColumnResult(name = "today_billed_value", type = Long.class)
-
 
412
                        }
-
 
413
                )
358
        )
414
        )
359
 
415
 
360
 
416
 
361
})
417
})
362
 
418