Subversion Repositories SmartDukaan

Rev

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

Rev 33823 Rev 33873
Line 22... Line 22...
22 
 */
 22 
 */
23 
 
 23 
 
24 
@Entity
 24 
@Entity
25 
@Table(name = "transaction.`order`")
 25 
@Table(name = "transaction.`order`")
26 
@NamedQueries({@NamedQuery(name = "Order.selectAll", query = "select o from Order o"), @NamedQuery(name = "Order.selectById", query = "select o from Order o where o.id= :id"), @NamedQuery(name = "Order.selectByTransactionId", query = "select t.id, t.createTimestamp, o.retailerAddress1, o.retailerAddress2, o.retailerCity, " + "o.retailerPinCode, o.retailerState, o.shippingCost, o.statusDescription, o.invoiceNumber, o.airwayBillNumber, o.totalAmount, li.brand, li.modelName, " + "li.modelNumber, li.color, li.quantity, li.unitPrice, p.id, p.name,  o.shippingTimestamp, o.status, o.promisedDeliveryTime, o.retailerName, t.status, i.catalogItemId  from Transaction t join Order o on o.transactionId = t.id " + "join LineItem li on li.orderId = o.id left join Provider p on p.id = o.logisticsProviderId join Item i on i.id=li.itemId where o.transactionId = :transactionId"),
 26 
@NamedQueries({@NamedQuery(name = "Order.selectAll", query = "select o from Order o"), @NamedQuery(name = "Order.selectById", query = "select o from Order o where o.id= :id"), @NamedQuery(name = "Order.selectByTransactionId", query = "select t.id, t.createTimestamp, o.retailerAddress1, o.retailerAddress2, o.retailerCity, " + "o.retailerPinCode, o.retailerState, o.shippingCost, o.statusDescription, o.invoiceNumber, o.airwayBillNumber, o.totalAmount, li.brand, li.modelName, " + "li.modelNumber, li.color, li.quantity, li.unitPrice, p.id, p.name,  o.shippingTimestamp, o.status, o.promisedDeliveryTime, o.retailerName, t.status, i.catalogItemId  from Transaction t join Order o on o.transactionId = t.id " + "join LineItem li on li.orderId = o.id left join Provider p on p.id = o.logisticsProviderId join Item i on i.id=li.itemId where o.transactionId = :transactionId"),
- 
 
 27 
        @NamedQuery(name = "Order.partnerStockPoAndGrnPending",
- 
 
 28 
                query = "select new com.spice.profitmandi.dao.model.PoAndGrnPendingPartnerStockModel" +
- 
 
 29 
                        "(li.itemId, odr.partnerGrnTimestamp, odr.billingTimestamp) " +
- 
 
 30 
                        "FROM Order odr " +
- 
 
 31 
                        "JOIN LineItem li on li.orderId = odr.id " +
- 
 
 32 
                        "WHERE odr.retailerId = (:fofoId) " +
- 
 
 33 
                        "AND odr.refundTimestamp is null " +
- 
 
 34 
                        "AND (odr.billingTimestamp is null OR (odr.billingTimestamp is not null AND odr.partnerGrnTimestamp is null))"),
27 
 
 35 
 
28 
        @NamedQuery(name = "Order.selectItemIdTypeQuantity", query = "select new com.spice.profitmandi.dao.model.ItemIdTypeQuantity(li.itemId, i.type, li.quantity) from Order o join LineItem li on li.orderId = o.id join Item i on i.id = li.itemId where o.invoiceNumber = :invoiceNumber and o.retailerId = :retailerId"),
 36 
        @NamedQuery(name = "Order.selectItemIdTypeQuantity", query = "select new com.spice.profitmandi.dao.model.ItemIdTypeQuantity(li.itemId, i.type, li.quantity) from Order o join LineItem li on li.orderId = o.id join Item i on i.id = li.itemId where o.invoiceNumber = :invoiceNumber and o.retailerId = :retailerId"),
29 
 
 37 
 
30 
        @NamedQuery(name = "Order.selectAllBilledOrderGroupByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderBillingModel(li.brand, " + "sum(case when o.billingTimestamp >= :today then CAST(o.totalAmount  AS integer) else 0 end)," + "sum(case when o.billingTimestamp >= :threedays  and o.billingTimestamp < :endDate  then CAST(o.totalAmount  AS integer) else 0 end)," + "sum(case when concat(year(o.billingTimestamp ), month(o.billingTimestamp ))= :mtd  then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between  :lmtdStartDate and :lmtdEndDate  then CAST(o.totalAmount  AS integer) else 0 end)," + "sum(case when o.billingTimestamp between  :lmtdStartDate and :lmsEndDate  then CAST(o.totalAmount  AS integer) else 0 end)," + "	sum(case when o.billingTimestamp >= :today then li.quantity else 0 end)," + "	sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then li.quantity else 0 end)," + " sum(case when concat(year(o.billingTimestamp), month(o.billingTimestamp))= :mtd  then li.quantity else 0 end)," + "	sum(case when o.billingTimestamp between  :lmtdStartDate and :lmtdEndDate then li.quantity else 0 end)," + "	sum(case when o.billingTimestamp between  :lmtdStartDate and :lmsEndDate  then li.quantity else 0 end)" + " )" + "	 from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + "	and o.billingTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and fs.internal = false group by li.brand"),
 38 
        @NamedQuery(name = "Order.selectAllBilledOrderGroupByBrand", query = "select new com.spice.profitmandi.dao.model.SecondaryOrderBillingModel(li.brand, " + "sum(case when o.billingTimestamp >= :today then CAST(o.totalAmount  AS integer) else 0 end)," + "sum(case when o.billingTimestamp >= :threedays  and o.billingTimestamp < :endDate  then CAST(o.totalAmount  AS integer) else 0 end)," + "sum(case when concat(year(o.billingTimestamp ), month(o.billingTimestamp ))= :mtd  then CAST(o.totalAmount AS integer) else 0 end)," + "sum(case when o.billingTimestamp between  :lmtdStartDate and :lmtdEndDate  then CAST(o.totalAmount  AS integer) else 0 end)," + "sum(case when o.billingTimestamp between  :lmtdStartDate and :lmsEndDate  then CAST(o.totalAmount  AS integer) else 0 end)," + "	sum(case when o.billingTimestamp >= :today then li.quantity else 0 end)," + "	sum(case when o.billingTimestamp >= :threedays and o.billingTimestamp < :endDate then li.quantity else 0 end)," + " sum(case when concat(year(o.billingTimestamp), month(o.billingTimestamp))= :mtd  then li.quantity else 0 end)," + "	sum(case when o.billingTimestamp between  :lmtdStartDate and :lmtdEndDate then li.quantity else 0 end)," + "	sum(case when o.billingTimestamp between  :lmtdStartDate and :lmsEndDate  then li.quantity else 0 end)" + " )" + "	 from Order o join LineItem li on o.id = li.orderId join FofoStore fs on fs.id = o.retailerId where o.status in (7,9,10,12)" + "	and o.billingTimestamp >= :lmtdStartDate and fs.warehouseId in :warehouseId and fs.internal = false group by li.brand"),
31 
 
 39