D
--
SELECT orp.ora ora1,
CASE WHEN c2.stationare>0 THEN ADDTIME(ord.ora, SEC_TO_TIME(-c2.stationare*60)) ELSE CAST(ord.ora AS TIME) END ora2,
legaturi.id, legaturi.pret, legaturi.km, legaturi.durata, legaturi.nivel,
trase.id traseu_id, trase.nume, trase.note, trase.dtModificat, trase.sursa, trase.trust_level, trase.tel_rezervari,
trase.rezervariP,
trase.tipRezervari & CASE WHEN tro.permisii IS NULL OR tro.permisii & 6 = 6 OR tro.permisii & 8 = 8 THEN 255 ELSE 0 END tipRezervari, trase.plataRezervari,
CASE WHEN masini.tip IS NULL THEN curse.tip_masina ELSE masini.tip END tipAuto, masini.dotari, masini.categorie,
co.id coid, co.nume conume,
CASE WHEN brands.nume IS NULL THEN co.brand ELSE brands.nume END cobrand,
co.rating, co.reviews, co.level1, co.level2, co.level3, co.level4,
CASE WHEN brands.logo IS NULL THEN co.logo ELSE brands.logo END logo, co.email, co.email, co.emailRez, co.subdomain,
orp.statie_id s1id, ord.statie_id s2id, co.platitor, co.appconst,
orp.alt_statie_id alt_statie_p, ord.alt_statie_id alt_statie_d, orp.company_id alt_company, DATEDIFF(NOW(), trase.dtModificat) vechime,
CASE WHEN co.data_expir > NOW() THEN 1 ELSE 0 END isPartner, CASE WHEN co.mobil='' THEN co.tel ELSE co.mobil END telRez,
(co.rezervari & trase.rezervari) curezervari,
orp.id orp_id, ord.id ord_id, co.discount, co.comision,
curse.d1, curse.d2, curse.d3, curse.d4, curse.d5, curse.d6, curse.d7, legaturi.versiuni, curse.cursa cuno, curse.id cursa_id
, trase.reguliFlexPrice ,CASE WHEN curse.traseu_id=trase.id THEN curse.ref ELSE cursa2.ref END refCursa, trase.ref refTraseu
,legaturi.id idRuta, legaturi.weekdays, legaturi.stationare,
bo.dtStart boStart,
CASE WHEN bo.dtEnd IS NULL AND legaturi.dtStart>'2025-04-11' THEN legaturi.dtStart ELSE bo.dtEnd END boEnd,
bo.note boNote,
CASE WHEN iCursa.locuri=0 THEN 1 ELSE iCursa.bSuspendata END bSuspendata,
IFNULL(iCursa.locuri,masini.locuri) locuri,
CASE WHEN trase.tipRezervari & 4 >0
AND (tro.permisii IS NULL OR tro.permisii & 6 = 6 OR tro.permisii & 8 = 8)
AND (legaturi.nivel>0 || EXISTS (
SELECT pret FROM preturi p1 JOIN tarife tf1 ON p1.tarif_id=tf1.id AND tf1.ord=0
WHERE p1.loc_plecare_id=legaturi.loc_plecare_id AND p1.loc_sosire_id=legaturi.loc_sosire_id AND tf1.company_id=trase.company_id
))
THEN 1 ELSE 0 END areVanzari
, tro.permisii, trase.saptamanal, iCursa.id iCursa , trase.cursaSpeciala, trase.scolara, trase.cod, trase.numeScurt,
(SELECT COUNT(id) FROM trust_notes WHERE traseu_id=trase.id) comentarii,
legaturi.transbordare
FROM legaturi
JOIN orar orp ON orp.id = legaturi.ora_plecare_id
JOIN orar ord ON ord.id = legaturi.ora_sosire_id
JOIN circuit c2 ON c2.id=ord.circuit_id
JOIN trase ON legaturi.traseu_id = trase.id AND trase.antena=0
LEFT JOIN tronsoane tro ON tro.traseu_id=legaturi.traseu_id AND tro.st_plecare_id=legaturi.statie_plecare_id AND tro.st_sosire_id=legaturi.statie_sosire_id
LEFT JOIN brands ON brands.id=trase.brand_id
JOIN companii co ON trase.company_id = co.id
JOIN curse ON curse.id=orp.cursa_id
JOIN curse cursa2 ON cursa2.id=ord.cursa_id
LEFT JOIN iCursa ON iCursa.cursa_id=curse.ref AND iCursa.zi=DATE_ADD('2025-04-11', INTERVAL -orp.zi DAY)
LEFT JOIN masini ON masini.id = COALESCE(iCursa.masina_id, CASE orp.zi WHEN 0 THEN curse.d5
WHEN 1 THEN curse.d4
WHEN 2 THEN curse.d3
END)
LEFT JOIN blackouts bo ON bo.dtStart<='2025-04-11' AND bo.dtEnd>'2025-04-11' AND
(bo.traseu_id IS NULL AND bo.operator_id=trase.company_id OR bo.cursa_id IS NULL AND bo.traseu_id=trase.id OR bo.cursa_id=curse.id)
WHERE legaturi.loc_plecare_id =79 AND legaturi.loc_sosire_id =45668
AND (trase.tipRezervari & 12 > 0)
AND (legaturi.dtEnd IS NULL OR legaturi.dtEnd > '2025-04-11') AND (legaturi.dtStart IS NULL OR legaturi.dtStart <= '2025-04-11')
AND trase.d2=1
ORDER BY areVanzari desc,orp.ora24, legaturi.nivel, boStart