-- View: public.VStoreRentalReport

-- DROP VIEW public."VStoreRentalReport";

CREATE OR REPLACE VIEW public."VStoreRentalReport"
 AS
 SELECT od."Id",
    od."CreatorId",
    od."CreateDatetime",
    od."ModifierId",
    od."ModifyDatetime",
    od."DeleteDatetime",
    om."OrderNum",
    om."OriginalId" AS "OriginalMainId",
    om."EN_Type",
    om."TenantName",
    om."TenantPhone",
    om."TenantMobile",
    om."Email",
    om."EN_RentedFor",
    om."EN_PayMethod",
    om."EN_ShippingMethod",
    deliveryaddr."Receiver" AS "DeliveryReceiver",
    deliveryaddr."Mobile" AS "DeliveryMobile",
    deliveryaddr."StoreId" AS "DeliveryStoreId",
    deliveryaddr."ZipCode" AS "DeliveryZipCode",
    deliveryaddr."County" AS "DeliveryCounty",
    deliveryaddr."City" AS "DeliveryCity",
    deliveryaddr."Address" AS "DeliveryAddress",
    deliveryaddr."FreightAreaId" AS "DeliveryFreightAreaId",
    deliveryarea."Name" AS "DeliveryFreightageAreaName",
    deliveryarea."ModelNo" AS "DeliveryFreightageModelNo",
    takebackaddr."Receiver" AS "TakebackReceiver",
    takebackaddr."Mobile" AS "TakebackMobile",
    takebackaddr."StoreId" AS "TakebackStoreId",
    takebackaddr."ZipCode" AS "TakebackZipCode",
    takebackaddr."County" AS "TakebackCounty",
    takebackaddr."City" AS "TakebackCity",
    takebackaddr."Address" AS "TakebackAddress",
    takebackaddr."FreightAreaId" AS "TakebackFreightAreaId",
    takebackarea."Name" AS "TakebackFreightageAreaName",
    takebackarea."ModelNo" AS "TakebackFreightageModelNo",
    om."OwnerStoreId",
    ownerstore."Name" AS "OwnerStoreName",
    ownerstore."MarketId" AS "OwnerMarketId",
    om."EN_PaymentStatus",
    om."PaymentDatetime",
    om."Remark",
    om."Memo",
    od."Id" AS "DetailId",
    od."OrderId",
    od."OriginalId" AS "OriginalDetailId",
    od."ParentProductId",
    od."ProductId",
    product."EN_Recovery",
    product."ModelNo",
    od."SeatWidthAttrId",
    seatattr."Name" AS "SeatWidthAttrName",
    od."ProductName",
    od."ProductCode",
    od."CoverImage",
    od."FreightTypeId",
    od."EN_PeriodType",
    od."UnitPrice",
    od."Qty",
    od."StartDate",
    od."EndDate",
    od."ConsumableFee",
    od."Deposit",
    od."DepositRefund",
    od."Cost",
    od."DeliveryFreightage",
    od."TakebackFreightage",
    od."EN_ShipPlace",
    od."EN_BuyType",
    od."EN_Status",
    od."EN_RenewStatus",
    od."EN_Verified",
    od."VerifiedMonth",
    od."EN_FreightVerified",
    od."FreightVerifiedMonth",
    od."EN_ConsumableFeeVerified",
    od."ConsumableFeeVerifiedMonth"
   FROM "OrderMain" om
     LEFT JOIN "OrderDetail" od ON od."OrderId" = om."Id"
     LEFT JOIN "OrderAddress" deliveryaddr ON om."Id" = deliveryaddr."OrderId" AND deliveryaddr."EN_Type" = 100 AND deliveryaddr."DeleteDatetime" IS NULL
     LEFT JOIN "FreightArea" deliveryarea ON deliveryarea."Id" = deliveryaddr."FreightAreaId" AND deliveryarea."DeleteDatetime" IS NULL
     LEFT JOIN "OrderAddress" takebackaddr ON om."Id" = takebackaddr."OrderId" AND takebackaddr."EN_Type" = 200 AND takebackaddr."DeleteDatetime" IS NULL
     LEFT JOIN "FreightArea" takebackarea ON takebackarea."Id" = takebackaddr."FreightAreaId" AND takebackarea."DeleteDatetime" IS NULL
     LEFT JOIN "Store" ownerstore ON ownerstore."Id" = om."OwnerStoreId" AND ownerstore."DeleteDatetime" IS NULL
     LEFT JOIN "ProductAttributes" seatattr ON seatattr."EN_Type" = 100 AND seatattr."Id" = od."SeatWidthAttrId" AND seatattr."DeleteDatetime" IS NULL
     LEFT JOIN "ProductMain" product ON product."Id" = od."ProductId" AND product."DeleteDatetime" IS NULL
  WHERE om."DeleteDatetime" IS NULL AND od."DeleteDatetime" IS NULL AND om."EN_Source" = 200 AND om."EN_Status" > 600 AND od."EN_Status" > 100;

ALTER TABLE public."VStoreRentalReport"
    OWNER TO "assistiveRent";

