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 + '}';
    }
}