Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.model;

import javax.persistence.*;
import java.util.Objects;


@Entity
@NamedNativeQueries({
        @NamedNativeQuery(name = "Aging.SoldAgingModel",
                query = "SELECT" +
                        "       a.Rbm_Name," +
                        "       SUM(CASE WHEN b.status = 'SLOWMOVING' THEN b.selling_price ELSE 0 END) AS SLOWMOVING_Billed," +
                        "       SUM(CASE  WHEN b.status = 'RUNNING' THEN b.selling_price  ELSE 0 END) AS RUNNING_Billed," +
                        "       SUM(CASE WHEN b.status = 'FASTMOVING' THEN b.selling_price  ELSE 0 END)  AS FASTMOVING_Billed," +
                        "       SUM(CASE WHEN b.status = 'HID' THEN b.selling_price ELSE 0 END) AS HID_Billed," +
                        "       COALESCE(SUM(CASE WHEN b.status IS NULL THEN b.selling_price ELSE 0 END),0) AS Other_Billed," +
                        "      COALESCE(SUM(b.selling_price),0) AS Total_Amount" +
                        " FROM (SELECT au.id                                    AS auth_id," +
                        "             CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name," +
                        "             fs.id                                    AS fofo_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," +
                        "             CONCAT(au.first_name, ' ', au.last_name) AS Rbm_Name," +
                        "             fs.id                                    AS fofo_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" +
                        "      LEFT JOIN (SELECT o.customer_id, tl.selling_price, cc.status from transaction.order o" +
                        "      JOIN transaction.lineitem li ON li.order_id = o.id AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
                        "      JOIN catalog.item ci ON ci.id = li.item_id" +
                        "      JOIN catalog.tag_listing tl ON tl.item_id = ci.id" +
                        "      LEFT JOIN catalog.catagoriesd_catalog cc ON cc.catalog_id = ci.catalog_item_id" +
                        "      AND (cc.start_date <= o.created_timestamp AND  (cc.end_date IS NULL OR cc.end_date > o.created_timestamp))" +
                        "      JOIN warehouse.scanNew s ON s.orderId = o.id" +
                        "      JOIN warehouse.inventoryItem ii ON ii.id = s.inventoryItemId" +
                        "      JOIN warehouse.inventoryItem ii2 ON ii.serialNumber = ii2.serialNumber AND ii2.lastScanType != 'PURCHASE_RETURN'" +
                        "      JOIN warehouse.purchase p ON p.id = ii2.purchaseId" +
                        "      JOIN warehouse.purchaseorder po ON po.id = p.purchaseOrder_id" +
                        "      JOIN warehouse.invoice inv ON p.invoice_id = inv.id AND po.supplierId = inv.supplierId AND po.warehouseId = ii2.physicalWarehouseId" +
                        "      JOIN warehouse.supplier su ON su.id = inv.supplierId AND su.internal = 0" +
                        "" +
                        " where ((o.created_timestamp >= :startDate AND o.created_timestamp < :endDate) AND (o.billing_timestamp >= :startDate AND o.billing_timestamp < :endDate) AND o.refund_timestamp IS NULL)" +
                        " and inv.invoiceDate <  DATE_SUB(o.billing_timestamp, INTERVAL 15 DAY)) b on b.customer_id = a.fofo_id" +
                        " GROUP BY a.auth_id, a.Rbm_Name",
                resultSetMapping = "SoldAgingModel"),

})

@SqlResultSetMappings({

        @SqlResultSetMapping(name = "SoldAgingModel",
                classes = {@ConstructorResult(targetClass = Sold15daysOldAgingModel.class,
                        columns = {
                                @ColumnResult(name = "Rbm_Name", type = String.class),
                                @ColumnResult(name = "SLOWMOVING_Billed", type = Long.class),
                                @ColumnResult(name = "RUNNING_Billed", type = Long.class),
                                @ColumnResult(name = "FASTMOVING_Billed", type = Long.class),
                                @ColumnResult(name = "HID_Billed", type = Long.class),
                                @ColumnResult(name = "Other_Billed", type = Long.class),
                                @ColumnResult(name = "Total_Amount", type = Long.class),
                        }
                )}
        )

})

public class Sold15daysOldAgingModel {
    String rbmName;
    long slowmovingBilled;
    long runningBilled;
    long fastmovingBilled;
    long hidBilled;
    long othersBilled;
    long totalBilled;

    // Synthetic primary key to satisfy JPA's requirement
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; // This will not be used in the query but satisfies JPA.

    public Sold15daysOldAgingModel(String rbmName, long slowmovingBilled, long runningBilled, long fastmovingBilled, long hidBilled, long othersBilled, long totalBilled) {
        this.rbmName = rbmName;
        this.slowmovingBilled = slowmovingBilled;
        this.runningBilled = runningBilled;
        this.fastmovingBilled = fastmovingBilled;
        this.hidBilled = hidBilled;
        this.othersBilled = othersBilled;
        this.totalBilled = totalBilled;
    }

    public String getRbmName() {
        return rbmName;
    }

    public void setRbmName(String rbmName) {
        this.rbmName = rbmName;
    }

    public long getSlowmovingBilled() {
        return slowmovingBilled;
    }

    public void setSlowmovingBilled(long slowmovingBilled) {
        this.slowmovingBilled = slowmovingBilled;
    }

    public long getRunningBilled() {
        return runningBilled;
    }

    public void setRunningBilled(long runningBilled) {
        this.runningBilled = runningBilled;
    }

    public long getFastmovingBilled() {
        return fastmovingBilled;
    }

    public void setFastmovingBilled(long fastmovingBilled) {
        this.fastmovingBilled = fastmovingBilled;
    }

    public long getHidBilled() {
        return hidBilled;
    }

    public void setHidBilled(long hidBilled) {
        this.hidBilled = hidBilled;
    }

    public long getOthersBilled() {
        return othersBilled;
    }

    public void setOthersBilled(long othersBilled) {
        this.othersBilled = othersBilled;
    }

    public long getTotalBilled() {
        return totalBilled;
    }

    public void setTotalBilled(long totalBilled) {
        this.totalBilled = totalBilled;
    }

    @Override
    public String toString() {
        return "Sold15daysAgingModel{" +
                "rbmName='" + rbmName + '\'' +
                ", slowmovingBilled=" + slowmovingBilled +
                ", runningBilled=" + runningBilled +
                ", fastmovingBilled=" + fastmovingBilled +
                ", hidBilled=" + hidBilled +
                ", othersBilled=" + othersBilled +
                ", totalBilled=" + totalBilled +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Sold15daysOldAgingModel that = (Sold15daysOldAgingModel) o;
        return slowmovingBilled == that.slowmovingBilled && runningBilled == that.runningBilled && fastmovingBilled == that.fastmovingBilled && hidBilled == that.hidBilled && othersBilled == that.othersBilled && totalBilled == that.totalBilled && Objects.equals(rbmName, that.rbmName);
    }

    @Override
    public int hashCode() {
        return Objects.hash(rbmName, slowmovingBilled, runningBilled, fastmovingBilled, hidBilled, othersBilled, totalBilled);
    }
}