Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.model;

import javax.persistence.*;

import java.util.Objects;

/**

 * Brand × movement-status roll-up of what a single RBM sold, for the RBM DRR

 * dashboard drill-down. Same auth_user → fofo_store derivation as

 * {@link TodayAchievedMovementModel}. Value = line-item revenue (quantity

 * × selling_price) so multi-line orders aren't multi-counted per bucket.

 * Rows are grouped by (brand, movement_status); the controller aggregates

 * to brand totals and per-brand bucket splits for the UI.

 */

@Entity

@NamedNativeQueries({

        @NamedNativeQuery(

                name = "RbmDrr.BrandSoldByRbm",

                query = "SELECT li.brand                                       AS brand," +

                        "       COALESCE(cc.status, 'OTHER')                    AS movement_status," +

                        "       COALESCE(SUM(li.quantity), 0)                  AS quantity," +

                        "       COALESCE(SUM(li.quantity * t.selling_price), 0) AS value_sold" +

                        " FROM (SELECT au.id AS auth_id, fs.id AS fofo_id, fs.warehouse_id" +

                        "       FROM auth.auth_user au" +

                        "                JOIN cs.position p ON p.auth_user_id = au.id" +

                        "                JOIN cs.partner_position pp ON pp.position_id = p.id" +

                        "                JOIN fofo.fofo_store fs ON fs.id = pp.partner_id" +

                        "       WHERE pp.partner_id != 0" +

                        "         AND p.category_id = 18" +

                        "         AND p.escalation_type = 'L1'" +

                        "         AND fs.active = 1" +

                        "         AND fs.internal = false" +

                        "       UNION ALL" +

                        "       SELECT au.id AS auth_id, fs.id AS fofo_id, fs.warehouse_id" +

                        "       FROM auth.auth_user au" +

                        "                JOIN cs.position p ON p.auth_user_id = au.id" +

                        "                JOIN cs.partner_position pp ON pp.position_id = p.id" +

                        "                JOIN cs.region r ON pp.region_id = r.id" +

                        "                JOIN cs.partner_region pr ON pr.region_id = pp.region_id" +

                        "                JOIN fofo.fofo_store fs ON fs.id = pr.fofo_id" +

                        "       WHERE pp.partner_id = 0" +

                        "         AND p.category_id = 18" +

                        "         AND fs.active = 1" +

                        "         AND fs.internal = false" +

                        "         AND p.escalation_type = 'L1') a" +

                        " JOIN transaction.order odr ON odr.customer_id = a.fofo_id" +

                        " JOIN transaction.lineitem li ON li.order_id = odr.id" +

                        " JOIN catalog.item i ON i.id = li.item_id" +

                        " JOIN catalog.tag_listing t ON t.item_id = i.id" +

                        " LEFT JOIN catalog.catagoriesd_catalog cc" +

                        "        ON cc.catalog_id = i.catalog_item_id" +

                        "       AND cc.start_date <= odr.created_timestamp" +

                        "       AND (cc.end_date IS NULL OR cc.end_date > odr.created_timestamp)" +

                        " WHERE a.auth_id = :authId" +

                        "   AND a.warehouse_id = :warehouseId" +

                        "   AND odr.created_timestamp >= :startDate" +

                        "   AND odr.created_timestamp <  :endDate" +

                        "   AND odr.refund_timestamp IS NULL" +

                        "   AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +

                        " GROUP BY li.brand, movement_status" +

                        " ORDER BY value_sold DESC",

                resultSetMapping = "RbmDrrBrandSoldByRbm")

})

@SqlResultSetMappings({

        @SqlResultSetMapping(

                name = "RbmDrrBrandSoldByRbm",

                classes = {

                        @ConstructorResult(

                                targetClass = RbmBrandSoldModel.class,

                                columns = {

                                        @ColumnResult(name = "brand", type = String.class),

                                        @ColumnResult(name = "movement_status", type = String.class),

                                        @ColumnResult(name = "quantity", type = Double.class),

                                        @ColumnResult(name = "value_sold", type = Double.class)

                                }

                        )

                }

        )

})

public class RbmBrandSoldModel {

    private String brand;

    private String movementStatus;

    private Double quantity;

    private Double valueSold;

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    private Long id;

    public RbmBrandSoldModel() {

    }

    public RbmBrandSoldModel(String brand, String movementStatus, Double quantity, Double valueSold) {

        this.brand = brand;

        this.movementStatus = movementStatus;

        this.quantity = quantity;

        this.valueSold = valueSold;

    }

    public String getBrand() {

        return brand;

    }

    public void setBrand(String brand) {

        this.brand = brand;

    }

    public String getMovementStatus() {

        return movementStatus;

    }

    public void setMovementStatus(String movementStatus) {

        this.movementStatus = movementStatus;

    }

    public Double getQuantity() {

        return quantity;

    }

    public void setQuantity(Double quantity) {

        this.quantity = quantity;

    }

    public Double getValueSold() {

        return valueSold;

    }

    public void setValueSold(Double valueSold) {

        this.valueSold = valueSold;

    }

    @Override

    public boolean equals(Object o) {

        if (this == o) return true;

        if (o == null || getClass() != o.getClass()) return false;

        RbmBrandSoldModel that = (RbmBrandSoldModel) o;

        return Objects.equals(brand, that.brand)

                && Objects.equals(movementStatus, that.movementStatus)

                && Objects.equals(quantity, that.quantity)

                && Objects.equals(valueSold, that.valueSold);

    }

    @Override

    public int hashCode() {

        return Objects.hash(brand, movementStatus, quantity, valueSold);

    }

    @Override

    public String toString() {

        return "RbmBrandSoldModel{brand='" + brand + "', movementStatus='" + movementStatus

                + "', quantity=" + quantity + ", valueSold=" + valueSold + '}';

    }

}