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