Subversion Repositories SmartDukaan

package com.spice.profitmandi.dao.model;

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


@Entity
@NamedNativeQueries({
        @NamedNativeQuery(name = "CatalogsReport.SoldCatalogsReport",
                query = " SELECT o.customer_name, fs.warehouse_id, o.warehouse_id billing_warehouse,i.model_number,i.catalog_item_id,t.selling_price, sum(li.quantity) quantity,cc.status,o.billing_timestamp from transaction.order o" +
                        " JOIN transaction.lineitem li on li.order_id=o.id" +
                        " JOIN fofo.fofo_store fs on fs.id=o.customer_id" +
                        " JOIN catalog.item i on i.id=li.item_id" +
                        " JOIN catalog.tag_listing t on t.item_id=i.id" +
                        " JOIN catalog.catagoriesd_catalog cc on (cc.catalog_id=i.catalog_item_id and (cc.start_date <= o.billing_timestamp AND  (cc.end_date IS NULL or cc.end_date > o.billing_timestamp)))" +
                        " WHERE (o.billing_timestamp >= :startDate AND o.billing_timestamp < :endDate and o.refund_timestamp is null) and li.brand not in ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo')" +
                        " GROUP BY o.customer_id , i.catalog_item_id",
                resultSetMapping = "SoldCatalogReport"),

})

@SqlResultSetMappings({

        @SqlResultSetMapping(name = "SoldCatalogReport",
                classes = {@ConstructorResult(targetClass = SoldCatalogsReportModel.class,
                        columns = {
                                @ColumnResult(name = "customer_name", type = String.class),
                                @ColumnResult(name = "warehouse_id", type = Integer.class),
                                @ColumnResult(name = "billing_warehouse ", type = Integer.class),
                                @ColumnResult(name = "model_number ", type = String.class),
                                @ColumnResult(name = "catalog_item_id ", type = Integer.class),
                                @ColumnResult(name = "selling_price ", type = Float.class),
                                @ColumnResult(name = "quantity ", type = Double.class),
                                @ColumnResult(name = "status ", type = String.class),
                                @ColumnResult(name = "billing_timestamp ", type = LocalDateTime.class)
                        }
                )}
        )

})

public class SoldCatalogsReportModel {
    String customerName;
    int partnerWarehouseId;
    int billingWarehouseId;
    String modelNumber;
    int catalogItemId;
    Float sellingPrice;
    Double soldQuantity;
    String status;
    LocalDateTime billingDate;
    // 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 SoldCatalogsReportModel(String customerName, int partnerWarehouseId, int billingWarehouseId, String modelNumber, int catalogItemId, Float sellingPrice, Double soldQuantity, String status, LocalDateTime billingDate) {
        this.customerName = customerName;
        this.partnerWarehouseId = partnerWarehouseId;
        this.billingWarehouseId = billingWarehouseId;
        this.modelNumber = modelNumber;
        this.catalogItemId = catalogItemId;
        this.sellingPrice = sellingPrice;
        this.soldQuantity = soldQuantity;
        this.status = status;
        this.billingDate = billingDate;
    }

    public String getCustomerName() {
        return customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    public int getPartnerWarehouseId() {
        return partnerWarehouseId;
    }

    public void setPartnerWarehouseId(int partnerWarehouseId) {
        this.partnerWarehouseId = partnerWarehouseId;
    }

    public int getBillingWarehouseId() {
        return billingWarehouseId;
    }

    public void setBillingWarehouseId(int billingWarehouseId) {
        this.billingWarehouseId = billingWarehouseId;
    }

    public String getModelNumber() {
        return modelNumber;
    }

    public void setModelNumber(String modelNumber) {
        this.modelNumber = modelNumber;
    }

    public int getCatalogItemId() {
        return catalogItemId;
    }

    public void setCatalogItemId(int catalogItemId) {
        this.catalogItemId = catalogItemId;
    }

    public Float getSellingPrice() {
        return sellingPrice;
    }

    public void setSellingPrice(Float sellingPrice) {
        this.sellingPrice = sellingPrice;
    }

    public Double getSoldQuantity() {
        return soldQuantity;
    }

    public void setSoldQuantity(Double soldQuantity) {
        this.soldQuantity = soldQuantity;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public LocalDateTime getBillingDate() {
        return billingDate;
    }

    public void setBillingDate(LocalDateTime billingDate) {
        this.billingDate = billingDate;
    }

    @Override
    public String toString() {
        return "SoldCatalogsReportModel{" +
                "customerName='" + customerName + '\'' +
                ", partnerWarehouseId=" + partnerWarehouseId +
                ", billingWarehouseId=" + billingWarehouseId +
                ", modelNumber='" + modelNumber + '\'' +
                ", catalogItemId=" + catalogItemId +
                ", sellingPrice=" + sellingPrice +
                ", soldQuantity=" + soldQuantity +
                ", status='" + status + '\'' +
                ", billingDate=" + billingDate +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        SoldCatalogsReportModel that = (SoldCatalogsReportModel) o;
        return partnerWarehouseId == that.partnerWarehouseId && billingWarehouseId == that.billingWarehouseId && catalogItemId == that.catalogItemId && Objects.equals(customerName, that.customerName) && Objects.equals(modelNumber, that.modelNumber) && Objects.equals(sellingPrice, that.sellingPrice) && Objects.equals(soldQuantity, that.soldQuantity) && Objects.equals(status, that.status) && Objects.equals(billingDate, that.billingDate);
    }

    @Override
    public int hashCode() {
        return Objects.hash(customerName, partnerWarehouseId, billingWarehouseId, modelNumber, catalogItemId, sellingPrice, soldQuantity, status, billingDate);
    }
}