SELECT
user.landlordppid AS LandLordID,
user.email AS HVEmail,
a.name,
ro.id AS Objektsid,
ro.publishedts AS Pub,
ro.firstpublishedts AS FörstaPub,
user.creationdate AS KontoSkapat,
aa.finishedts AS SigneringsDatum,
adminarea1,
locality,
SUBSTRING_INDEX((SELECT
area
FROM
rentcalculatordetails rc
WHERE
user.id = rc.userid
ORDER BY updatedts DESC
LIMIT 1),
‘,’,
– 2) AS SenasteArea,
(SELECT
area
FROM
rentcalculatordetails rc
WHERE
user.id = rc.userid
ORDER BY updatedts DESC
LIMIT 1) AS SenasteArea_FullSträng,
(SELECT
calculatedvalue
FROM
rentcalculatordetails rc
WHERE
user.id = rc.userid
ORDER BY updatedts DESC
LIMIT 1) AS SenastePris_Hyreskalkylator,
(SELECT
leasingperiodstartfrom
FROM
rentcalculatordetails rc
WHERE
user.id = rc.userid
ORDER BY updatedts DESC
LIMIT 1) AS SenasteUthyrningsperiod_Hyreskalkylator,
ro.landlordmonthlyprice AS PrisIAnnons,
aa.landlordmonthlyprice AS PrisIKontrakt,
IF(ro.endrentaldate IS NULL,
6,
(DATEDIFF(ro.endrentaldate, ro.startrentaldate) / 30)) AS PeriodIAnnons,
(ro.landlordmonthlyprice * 0.15) * (IF(ro.endrentaldate IS NULL,
6,
(DATEDIFF(ro.endrentaldate, ro.startrentaldate) / 30))) AS UppskattadIntäkt,
(ro.landlordmonthlyprice – aa.landlordmonthlyprice) / aa.landlordmonthlyprice AS DiffPris,
(SELECT
COUNT(1)
FROM
viewing
WHERE
ro.id = viewing.rentalobjectid) AS AntalIntresse,
(SELECT
COUNT(1)
FROM
viewing
WHERE
ro.id = viewing.rentalobjectid
AND time IS NOT NULL) AS AntalVisning,
(SELECT
EXISTS( SELECT
1
FROM
viewing
WHERE
ro.id = viewing.rentalobjectid)
) AS OmIntresse,
(SELECT
EXISTS( SELECT
1
FROM
viewing
WHERE
ro.id = viewing.rentalobjectid
AND viewing.latestavailabletimes = ‘[]’)
) AS OmIntresse_UtanVisningstid_UtanVisning,
(SELECT
EXISTS( SELECT
1
FROM
viewing
WHERE
ro.id = viewing.rentalobjectid
AND time IS NOT NULL)
) AS OmVisning,
(SELECT
EXISTS( SELECT
1
FROM
agreement
WHERE
ro.id = agreement.rentalobjectid
AND scrivestatus = ‘closed’)
) AS OmAvtal,
DATEDIFF(ro.creationts, user.creationdate) AS KontoSkapasAnnonsSkapas,
DATEDIFF(ro.firstpublishedts, user.creationdate),
DATEDIFF(ro.firstpublishedts, ro.creationts) AS AnnonsSkapasAnnonsPub,
DATEDIFF(v.time, ro.firstpublishedts) AS AnnonspublicerasFörstaVisning,
DATEDIFF(aa.finishedts, v.time) AS FörstaVisningKontraktsigneras,
DATEDIFF(aa.startdate, aa.finishedts) AS KontraktSignerasInlytt,
DATEDIFF(aa.enddate, aa.startdate) AS InflyttUtflytt,
LatestActiveDate,
tp.NumberOfClicks,
MinRooms,
MaxRooms,
MinSquareMeters,
MaxSquareMeters,
MaxPrice AS HGMaxprisBudget,
tp.StartRentalDate AS HGStartDatum,
tp.EndRentalDate AS HGSlutDatum,
aa.payfeeuntil,
aa.enddate,
DATEDIFF(aa.payfeeuntil, aa.enddate) AS DagarFöreUppsägning,
DATEDIFF(aa.enddate, aa.startdate) AS Kontraktslängd,
DATEDIFF(aa.payfeeuntil, aa.enddate) / (DATEDIFF(aa.enddate, aa.startdate) + DATEDIFF(aa.payfeeuntil, aa.enddate)) AS AndelTidKvar,
IF(aa.payfeeuntil IS NULL, 0, 1) AS OmFörtidsUppsagdUtanFörlängning,
(SELECT
totalincome
FROM
creditinformation c
WHERE
c.tennantppid = t.tennantppid
ORDER BY creditinformationdate ASC
LIMIT 1) AS FörstTotalInkomst,
(SELECT
debtsum
FROM
creditinformation c
WHERE
c.tennantppid = t.tennantppid
ORDER BY creditinformationdate ASC
LIMIT 1) AS FörstaSkuldsaldo,
(SELECT
creditscoring
FROM
creditinformation c
WHERE
c.tennantppid = t.tennantppid
ORDER BY creditinformationdate ASC
LIMIT 1) AS FörstCreditScore,
(SELECT
debtsum
FROM
creditinformation c
WHERE
c.tennantppid = v.tennantppid
ORDER BY creditinformationdate ASC
LIMIT 1) AS SkuldSaldoVisning,
(SELECT
creditscoring
FROM
creditinformation c
WHERE
c.tennantppid = v.tennantppid
ORDER BY creditinformationdate ASC
LIMIT 1) AS CreditScoreVisning
FROM
user
LEFT JOIN
landlord ON user.landlordppid = landlord.landlordppid
LEFT JOIN
landlordpp ON user.landlordppid = landlordpp.id
LEFT JOIN
rentalobject ro ON landlord.id = ro.landlordid
LEFT JOIN
viewing v ON v.rentalobjectid = ro.id
LEFT JOIN
emailcserviceagent e ON e.email = user.email
LEFT JOIN
agent a ON a.id = e.agentid
LEFT JOIN
agreement aa ON aa.rentalobjectid = ro.id
LEFT JOIN
tennantprofile tp ON tp.id = aa.tennantprofileid
LEFT JOIN
tennant t ON tp.tennantid = t.id
WHERE
user.landlordppid IS NOT NULL
GROUP BY user.landlordppid