public override object ExecuteReport(MethodReturn refMr = null)
{
string servisIdQuery = "";
if (ServisIds.isNotEmpty())
{
servisIdQuery = $"in ({ServisIds.joinNumeric(",")})";
}
else
{
servisIdQuery = $">1";
}
List <ReportData> reportDataSource = new List <ReportData>();
List <RowData> rowDataSource = AppPool.EbaTestConnector.CreateQuery($@"
SELECT
tarihler.tarih, servis.PARTNERCODE, servis.servisid, servis.isortakad SERVISISORTAKAD, servis.VARLIKAD SERVISVARLIKAD, ags.arac_giris, ags.isemir_acilan, ags.isemir_kapanan
FROM
(SELECT TARIH, YIL, AY FROM TARIHLER WHERE TARIH BETWEEN {{startDate}} AND {{finishDate}}) tarihler
left join vt_servisler servis on servis.servisid {servisIdQuery} and servis.dilkod = 'Turkish'
left join mobilags ags on AGS.SERVIS = servis.servisid and AGS.TARIH = tarihler.tarih
order by tarihler.tarih, servis.servisid
")
.Parameter("startDate", StartDate.startOfDay())
.Parameter("finishDate", FinishDate.endOfDay())
.GetDataTable()
.ToModels <RowData>();
foreach (RowData data in rowDataSource)
{
int ay = data.TARIH.Month;
int yil = data.TARIH.Year;
ReportData reportData = reportDataSource.first(t => t.ServisId == data.SERVISID && t.Yil == yil);
if (reportData.isNull())
{
reportData = new ReportData()
{
PartnerCode = data.PARTNERCODE,
ServisId = data.SERVISID,
ServisAdi = data.SERVISISORTAKAD,
ServisVarlikAdi = data.SERVISVARLIKAD,
Yil = data.TARIH.Year,
};
reportDataSource.add(reportData);
}
reportData.SetPropertyValue($"AIS{ay}", reportData.GetPropertyValue <decimal>($"AIS{ay}") + data.ISEMIR_ACILAN);
reportData.SetPropertyValue($"KIS{ay}", reportData.GetPropertyValue <decimal>($"KIS{ay}") + data.ISEMIR_KAPANAN);
reportData.SetPropertyValue($"AGS{ay}", reportData.GetPropertyValue <decimal>($"AGS{ay}") + data.ARAC_GIRIS);
}
CloseCustomAppPool();
return(reportDataSource);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
decimal selectedAcikKalma = AcikKalma.first().toString("0").cto <decimal>();
string acikKalma = "";
decimal selectedAracKazali = AracKazali.first().ToString("0").cto <decimal>();
string aracKazali = "";
decimal selectedServisDisi = AracServisDisi.first().ToString("0").cto <decimal>();
string servisDisi = "";
decimal selectedCariKamu = CariKamu.first().ToString("0").cto <decimal>();
string cariKamu = "";
decimal selectedAracCikis = AracCikis.first().ToString("0").cto <decimal>();
string aracCikis = "";
#if DEBUG
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
#endif
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
#region filtreler
if (selectedAracKazali == 1)
{
aracKazali = "='EVET'";
}
else if (selectedAracKazali == 2)
{
aracKazali = "='HAYIR'";
}
else
{
aracKazali = " = 'HAYIR' or arac_kazali = 'EVET'";
}
if (selectedServisDisi == 1)
{
servisDisi = "='EVET'";
}
else if (selectedServisDisi == 2)
{
servisDisi = "='HAYIR'";
}
else
{
servisDisi = " = 'HAYIR' or arac_servis_disinda = 'EVET'";
}
if (selectedCariKamu == 1)
{
cariKamu = "='EVET'";
}
else if (selectedCariKamu == 2)
{
cariKamu = "='HAYIR'";
}
else
{
cariKamu = " = 'HAYIR' or kamu = 'EVET'";
}
if (selectedAracCikis == 1)
{
aracCikis = "IS NOT NULL";
}
else if (selectedAracCikis == 2)
{
aracCikis = "IS NULL";
}
else
{
aracCikis = " IS NOT NULL or araccikiszamani IS NULL";
}
if (selectedAcikKalma == 3)
{
acikKalma = ">3";
}
else if (selectedAcikKalma == 7)
{
acikKalma = ">7";
}
else if (selectedAcikKalma == 30)
{
acikKalma = ">30";
}
else
{
acikKalma = ">0";
}
#endregion
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT *
FROM (
SELECT ie1.servisid AS servisid,
srv.partnercode AS serviskodu,
srv.isortakad AS servisadi,
ie1.isemirno AS isemirno,
ie1.kayittarih AS kayittarihi,
SYSDATE raporgunu,
ROUND( SYSDATE - ie1.kayittarih) acikkalmagunu,
CASE WHEN ie1.arackazali = 1
THEN 'EVET'
ELSE 'HAYIR'
END arac_kazali,
TO_CHAR(ie1.araccikiszamani,'dd.mm.yyyy') AS araccikiszamani,
CASE WHEN ie1.aracserviste = 0
THEN 'HAYIR'
ELSE 'EVET'
END arac_servis_disinda,
CASE WHEN servar.varliktipid=3
THEN 'EVET'
ELSE 'HAYIR'
END kamu,
ie1.aciklama AS aciklama
FROM (SELECT *
FROM servisisemirler
WHERE teknikolaraktamamla = 0 AND tamamlanmatarih IS NULL) ie1
LEFT JOIN vt_servisler srv ON srv.dilkod = 'Turkish' AND srv.servisid = ie1.servisid
INNER JOIN servisvarliklar servar ON servar.id=ie1.servisvarlikid
)
WHERE acikkalmagunu {acikKalma}
AND ( servisid {servisIdQuery} )
AND ( arac_kazali {aracKazali} )
AND ( arac_servis_disinda {servisDisi} )
AND ( kamu {cariKamu} )
AND ( araccikiszamani {aracCikis} )
ORDER BY servisid,kayittarihi ASC
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
string dateQuery = "";
#if DEBUG
selectedServisId = ServisId;
servisIdQuery = $" = {selectedServisId}";
#endif
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
#region arizakod
string arizaKodQuery = "";
if (ArizaKod.Length == 7)
{
arizaKodQuery =
$" AND (SUBSTR(a.arizakodu, 1, 7) ) = '{ArizaKod}' ";
}
else
{
arizaKodQuery = "";
}
#endregion
#region saseNo
string saseNoQuery = "";
if (SaseNo.isNotEmpty())
{
saseNoQuery =
$" AND si.saseno = '{SaseNo}' ";
}
else
{
saseNoQuery = "";
}
#endregion
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT v.partnercode servis_kodu,
v.isortakad servis_adi,
TO_CHAR (si.tamamlanmatarih, 'DD.MM.YYYY') isemri_kapanma_tarihi,
EXTRACT (YEAR FROM si.tamamlanmatarih) isemri_tamamlanma_yili,
t.kod ayristirma_tipi,
(CASE
WHEN si.arackazali = 1
THEN 'EVET'
WHEN si.arackazali <> 1
THEN 'HAYIR'
END) AS arackazali,
ROUND (si.tamamlanmatarih - si.kayittarih, 2) acik_kalma_suresi,
(CASE
WHEN si.hizmetyerid = 1
THEN 'SERVIS'
WHEN si.hizmetyerid = 2
THEN 'SANTIYE'
WHEN si.hizmetyerid = 3
THEN 'YOL YARDIM'
END) AS hizmet_yeri,
si.turasist,
a.servisgarantino,
TO_CHAR (a.sonokumazamani, 'DD.MM.YYYY') garanti_kapanma_tarihi,
(CASE
WHEN a.claimstatus = 'Z110'
tHEN
TO_CHAR (EXTRACT (YEAR FROM a.sonokumazamani))
WHEN a.claimstatus IN ('Z107', 'Z109', 'Z999')
THEN
('GARANTI_RED')
ELSE
''
END) AS garanti_kapanma_yili,
a.claimno,
a.claimstatus,
t.garantituru,
r.plaka,
r.saseno,
vm.vehiclenum kisa_sase,
vm.vehicletype arac_tipi,
vm.modelnum arac_modeli,
a.pdfkmdurumu garanti_km,
si.km isemri_km,
vm.schadstkl emisyon_sinifi,
sv.ad musteri_adi,
sv.vergino,
a.arizakodu,
a.id,
si.tutar,
si.indirimlitutar,
si.ttutar tahmini_tutar,
si.aciklama,
si.arackazaaciklama kaza_aciklama,
si.sfnotu servis_fisi_notu,
a.gtutar ic_tutari,
a.pdftalepgeneltoplam oc_tutari,
v.gsad servis_garanti_sorumlusu,
v.tbsad teknik_bolge_sorumlusu
FROM vt_servisler v,
ayristirmalar a,
servisvarlikruhsatlar r,
esaaraclar ea,
vx_vis_vehiclemaster vm,
ayristirmatipler t,
servisvarliklar sv,
servisisemirler si
WHERE v.dilkod = 'Turkish'
AND a.servisid = v.servisid
AND si.isemirno = a.isemirno
AND r.saseno = ea.vin
AND ea.id = vm.esaaracid
AND a.ayristirmatipid = t.id
AND sv.id = r.servisvarlikid
AND r.servisid = a.servisid
AND si.saseno = r.saseno
AND a.durumid = 1
AND si.tamamlanmatarih BETWEEN '{dateQuery}'
AND a.servisid {servisIdQuery}
{saseNoQuery}
{arizaKodQuery}
order by servis_kodu, si.tamamlanmatarih desc
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
string dateQuery = "";
string dateGunQuery1 = "";
string dateGunQuery2 = "";
int MinGun1;
int MaxGun1;
if (MinGun.isNotEmpty())
{
MinGun1 = int.Parse(MinGun);
}
else
{
MinGun1 = -1;
}
if (MaxGun.isNotEmpty())
{
MaxGun1 = int.Parse(MaxGun);
}
else
{
MaxGun1 = -1;
}
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
if (MinGun1 > -1)
{
dateGunQuery1 = " AND acikkalmagunu >= " + MinGun;
}
if (MaxGun1 > -1)
{
dateGunQuery2 = " AND acikkalmagunu <= " + MaxGun;
}
// StartDate = StartDate.startOfDay();
// FinishDate = FinishDate.endOfDay();
// dateQuery = ""+StartDate.ToString("dd/MM/yyyy") + "' AND '"+ FinishDate.ToString("dd/MM/yyyy")+"";
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
select * from (
select
ie1.servisid,
SRV.PARTNERCODE,
SRV.ISORTAKAD SERVISADI,
ie1.isemirno,
ie1.kayittarih KAYITTARIHI,
SYSDATE RAPORGUNU,
round( sysdate - ie1.kayittarih) ACIKKALMAGUNU,
case
when ie1.arackazali = 1 then 'EVET'
else 'HAYIR'
end ARAC_KAZALI,
TO_CHAR(ie1.araccikiszamani,'dd.mm.yyyy') AS araccikiszamani,
case
when ie1.aracserviste = 0 then 'HAYIR'
else 'EVET'
end arac_servis_disinda,
case
when SERVAR.VARLIKTIPID=3 then 'EVET'
else 'HAYIR'
end KAMU,
ie1.aciklama
from (
select * from servisisemirler where teknikolaraktamamla = 0 and tamamlanmatarih is null) ie1
left join vt_servisler srv on srv.dilkod = 'Turkish' and srv.servisid = ie1.servisid
inner join servisvarliklar servar on SERVAR.id=ie1.servisvarlikid
WHERE srv.servisid {servisIdQuery}
)
where
1=1
{dateGunQuery1}
{dateGunQuery2}
order by ACIKKALMAGUNU desc ,kayittarihi asc
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
string dateQuery = "";
decimal TarihGrupId = TarihGrupIds.first().toString("0").cto <decimal>();
string dateBetweenQuery = "";
string dateBetweenQuery2 = "";
#if DEBUG
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
#endif
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
StartDate = StartDate.startOfDay();
dateQuery = "to_date('" + StartDate.ToString("dd/MM/yyyy") + "')";
if (TarihGrupId > 0)
{
dateBetweenQuery = " AND TO_DATE(sysdate, 'DD/MM/YYYY') - TO_DATE(TARIH, 'DD/MM/YYYY') <= " + TarihGrupId.ToString();
dateBetweenQuery2 = " AND TO_DATE(sysdate, 'DD/MM/YYYY') - TO_DATE(KayitTARIH, 'DD/MM/YYYY') <= " + TarihGrupId.ToString();
}
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT distinct p.STOKISLEMTIPDEGER,
(select vtsx.partnercode from vt_servisler vtsx where vtsx.servisid = p.HSERVISID and vtsx.dilkod = 'Turkish') as partnercode,
(Select vtsxy.ISORTAKAD FROM vt_servisler vtsxy where vtsxy.dilkod = 'Turkish' and vtsxy.servisid = p.HSERVISID ) as servisad,
a.kod tur,
p.ID,
P.AD,
p.HSERVISID,
p.KOD,
p.servisstokturid,
P.STOKMIKTAR STOKMIKTAR,
p.BIRIMAD,
P.INDFIYAT EUROINDFIYAT,
P.FIYAT EUROLISTEFIYAT,
P.ORTALAMAMALIYET ORTALAMAMALIYET,
p.SERVISDEPOAD,
p.SERVISDEPORAFAD,
p.stokmiktar * p.ortalamamaliyet stoktutar,
p.orjinalkod
FROM(SELECT servisstokturid, h.STOKISLEMTIPDEGER,
a.id,
a.servisid hservisid,
a.kod,
C.STOKMIKTAR,
r.ad BIRIMAD,
kurlar_pkg.servisstokfiyatgetir(a.id, 2, TRUNC({dateQuery}))
fiyat,
KURLAR_PKG.STOKFIYATINDGETIR(a.id,
2,
2,
1,
0)
indfiyat,
kurlar_pkg.ORTALAMAMALIYET(a.id) ortalamamaliyet,
d.ad SERVISDEPOAD,
p.ad SERVISDEPOrafAD,
a.ad,
CASE
WHEN (SELECT orjinalmalzemeid
FROM malzemeler
WHERE id = a.malzemeid)
IS NULL
THEN
''
ELSE
(SELECT CONCAT ('', gkod)
FROM malzemeler
WHERE id = (SELECT orjinalmalzemeid
FROM malzemeler
WHERE id = a.malzemeid))
END
orjinalkod
FROM(
SELECT DISTINCT *
FROM sason.servisstokhareketdetaylar sshd
inner join servisstokhareketler ssh on ssh.id = sshd.servisstokhareketid
where sshd.STOKISLEMTIPDEGER != -1 AND
ssh.servisid {servisIdQuery}
{dateBetweenQuery}
) h,
sason.servisstoklar a,
-- sason.vt_genelstok c,
(
SELECT CASE
WHEN servisstokid IS NULL THEN 0 - ambarstokmiktar
ELSE stokmiktar
END
stokmiktar,
CASE
WHEN servisstokid IS NULL THEN ambarstokid
ELSE servisstokid
END
servisstokid,
servisid
FROM (SELECT a.stokmiktar - NVL (b.stokmiktar, 0) stokmiktar,
a.servisstokid,
b.servisstokid ambarstokid,
b.stokmiktar ambarstokmiktar,
a.servisid
FROM ( SELECT SUM (stokmiktar) STOKMIKTAR,
servisid,
servisstokid
FROM(SELECT servisid,
servisstokid,
amiktar * stokislemtipdeger STOKMIKTAR
FROM servisstokhareketdetaylar sshd,
servisstokhareketler ssh
WHERE
sshd.STOKISLEMTIPDEGER != -1 AND
ssh.id = sshd.SERVISSTOKHAREKETID AND
sshd.servisdepoid NOT IN(21, 22) AND
ssh.servisid {servisIdQuery}
{dateBetweenQuery}
)
GROUP BY servisid, servisstokid) a
FULL OUTER JOIN
(SELECT SUM (a.miktar) stokmiktar,
a.servisstokid,
c.servisid
FROM servisismislemmalzemeler a,
servisisemirislemler b,
servisisemirler c
WHERE c.id = b.servisisemirid
AND b.id = A.SERVISISEMIRISLEMID
AND a.durumid = 1
AND c.teknikolaraktamamla = 0
AND c.servisid {servisIdQuery}
{dateBetweenQuery2}
GROUP BY servisstokid, servisid) b
ON(a.servisstokid = b.servisstokid))
) c,
sason.vw_birimler r,
sason.servisdepolar d,
sason.servisdeporaflar p
WHERE h.servisstokid = a.id
AND A.ID = C.SERVISSTOKID
AND C.STOKMIKTAR <> 0
AND a.servisid = c.servisid
AND r.dilkod = 'Turkish'
AND A.SERVISDEPOID = d.id(+)
AND a.servisdeporafid = p.id(+)
AND r.id = a.birimid
/*****************************************/
and
a.id not in
(
SELECT distinct -- p.STOKISLEMTIPDEGER,
p.ID
FROM(SELECT servisstokturid, h.STOKISLEMTIPDEGER,
a.id,
a.servisid hservisid,
a.kod,
C.STOKMIKTAR
FROM(
SELECT DISTINCT *
FROM sason.servisstokhareketdetaylar sshd
inner join servisstokhareketler ssh on ssh.id = sshd.servisstokhareketid
where
SSHD.STOKISLEMTIPDEGER = -1 AND
ssh.servisid {servisIdQuery}
{dateBetweenQuery}
) h,
sason.servisstoklar a,
-- sason.vt_genelstok c,
(
SELECT CASE
WHEN servisstokid IS NULL THEN 0 - ambarstokmiktar
ELSE stokmiktar
END
stokmiktar,
CASE
WHEN servisstokid IS NULL THEN ambarstokid
ELSE servisstokid
END
servisstokid,
servisid
FROM (SELECT a.stokmiktar - NVL (b.stokmiktar, 0) stokmiktar,
a.servisstokid,
b.servisstokid ambarstokid,
b.stokmiktar ambarstokmiktar,
a.servisid
FROM ( SELECT SUM (stokmiktar) STOKMIKTAR,
servisid,
servisstokid
FROM(SELECT servisid,
servisstokid,
amiktar * stokislemtipdeger STOKMIKTAR
FROM servisstokhareketdetaylar sshd,
servisstokhareketler ssh
WHERE ssh.id = sshd.SERVISSTOKHAREKETID AND
sshd.servisdepoid NOT IN(21, 22) AND
SSHD.STOKISLEMTIPDEGER = -1 AND
ssh.servisid {servisIdQuery}
{dateBetweenQuery}
)
GROUP BY servisid, servisstokid) a
FULL OUTER JOIN
(SELECT SUM (a.miktar) stokmiktar,
a.servisstokid,
c.servisid
FROM servisismislemmalzemeler a,
servisisemirislemler b,
servisisemirler c
WHERE c.id = b.servisisemirid
AND b.id = A.SERVISISEMIRISLEMID
AND a.durumid = 1
AND c.teknikolaraktamamla = 0
AND c.servisid {servisIdQuery}
{dateBetweenQuery2}
GROUP BY servisstokid, servisid) b
ON(a.servisstokid = b.servisstokid))
) c,
sason.vw_birimler r,
sason.servisdepolar d,
sason.servisdeporaflar p
WHERE h.servisstokid = a.id
AND A.ID = C.SERVISSTOKID
AND C.STOKMIKTAR <> 0
AND a.servisid = c.servisid
AND r.dilkod = 'Turkish'
AND A.SERVISDEPOID = d.id(+)
AND a.servisdeporafid = p.id(+)
AND r.id = a.birimid
AND a.servisid {servisIdQuery}
) p,
servisstokturler a
WHERE
p.servisstokturid = a.id AND hservisid {servisIdQuery}
)
/******************************************/
) p,
servisstokturler a
WHERE
p.servisstokturid = a.id AND hservisid {servisIdQuery} AND
P.STOKMIKTAR> 0
-- AND p.STOKISLEMTIPDEGER != -1
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
string servisIdQuery = "";
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
string hizmetYeriIdQuery = "";
if (HizmetYeriIds.isNotEmpty())
{
hizmetYeriIdQuery = $" and HIZMETYERID in ({HizmetYeriIds.joinNumeric(",")}) ";
}
List <object> reportDataSource = AppPool.EbaTestConnector.CreateQuery($@"
SELECT DISTINCT
servis.servisid, isemir.id isemirid, isortak.servisisortakid,
TRUNC(isemir.kayittarih) tarih,
servis.partnercode serviscode, servis.isortakad servisadi, servis.varlikad servisvarlikadi,
servisbilgi.adres servisadres, servisbilgi.ulke_ad servisadres_ulke, servisbilgi.il_ad servisadres_il, servisbilgi.ilce_ad servisadres_ilce,
isemir.isemirno isemirno,
osuser.firstname userfirstname, osuser.lastname userlastname,
isortak.vergino,
isortak.ad musteri_ad, isortak.servisvarlikad musteri_varlikad, isortak.varliktipad musteri_varliktipi, isortak.vergidairead musteri_vergidairesi, isortak.vergino musteri_vergino, isortak.vergidaireilad musteri_vergidairesi_il,
CASE WHEN isortak.filobuyuklukid IS NULL THEN 1 ELSE 0 END musteri_filobuyukluk,
--siotelfax.TELEFONNO MUSTERI_TELEFON,
isemir.musteriad musteri_kisi_ad, isemir.musteritelefon musteri_kisi_telefon,
--KONTAK.EPOSTA KONTAK_EPOSTA, KONTAK.EPOSTAIZIN KONTAK_IZIN_EPOSTA, KONTAK.ARAMAIZIN KONTAK_IZIN_TELEFONARAMA, KONTAK.SMSIZIN KONTAK_IZIN_SMS,
--KONTAK.SERVISISORTAKKONTAKTIPAD KONTAK_TIP,
--KONTAK.NO KONTAK_TELEFON,
--kontak.AD KONTAK_AD,
kontakfirmasahibi.ad kontakfirmasahibi,
kontakfirmasahibi.no kontakfirmasahibi_tel,
kontaksatisyetkilisi.ad kontaksatisyetkilisi,
kontaksatisyetkilisi.no kontaksatisyetkilisi_tel,
kontakgenelmudur.ad kontakgenelmudur,
kontakgenelmudur.no kontakgenelmudur_tel,
kontaksatissorumlusu.ad kontaksatissorumlusu,
kontaksatissorumlusu.no kontaksatissorumlusu_tel,
kontakservismuduru.ad kontakservismuduru,
kontakservismuduru.no kontakservismuduru_tel,
kontakfilo.ad kontakfiloyon,
kontakfilo.no kontakfiloyon_tel,
kontaksatinalma.ad kontaksatinalma,
kontaksatinalma.no kontaksatinalma_tel,
kontaksatismuduru.ad kontaksatismuduru,
kontaksatismuduru.no kontaksatismuduru_tel,
kontakgenelmuduryrd.ad kontakgenelmuduryrd,
kontakgenelmuduryrd.no kontakgenelmuduryrd_tel,
kontakbolgemuduru.ad kontakbolgemuduru,
kontakbolgemuduru.no kontakbolgemuduru_tel,
kontakbassofor.ad kontakbassofor,
kontakbassofor.no kontakbassofor_tel,
kontakdiger.ad kontakdiger,
kontakdiger.no kontakdiger_tel,
aracbilgiler.saseno arac_saseno, aracbilgiler.aractur arac_tur, aracbilgiler.plaka arac_plaka,
(CASE
WHEN aracbilgiler.manolmayan = 0
THEN ''
WHEN aracbilgiler.manolmayan = 1
THEN 'MAN OLMAYAN'
END) AS manolmayan,
(SELECT
CASE bx.isemirtipid
WHEN 1 then 'Evet'
WHEN 5 then 'Evet'
ELSE 'Hayır' end
FROM servisisemirler ax
LEFT JOIN servisisemirislemler bx ON bx.isemirtipid IN (1,5) AND bx.servisisemirid = ax.id
WHERE ax.isemirno = ISEMIR.ISEMIRNO AND ROWNUM <2 ) AS bakimmi
FROM (SELECT * FROM servisisemirler WHERE TRUNC(kayittarih) BETWEEN {{startDate}} AND {{finishDate}} AND servisid {servisIdQuery} {hizmetYeriIdQuery} ) isemir
LEFT JOIN vt_servisler servis ON servis.dilkod = 'Turkish' AND servis.servisid = isemir.servisid
--left join servisisemirler isemir on trunc(ISEMIR.KAYITTARIH) = tarihler.tarih and isemir.servisid = servis.servisid
LEFT JOIN osusers osuser ON osuser.id = isemir.kullaniciid
LEFT JOIN sason.vw_isortakkontakbilgiler kontakfirmasahibi ON kontakfirmasahibi.servisisortakid = isemir.servisisortakid AND kontakfirmasahibi.servisisortakkontaktipid = 1
LEFT JOIN sason.vw_isortakkontakbilgiler kontaksatisyetkilisi ON kontaksatisyetkilisi.servisisortakid = isemir.servisisortakid AND kontaksatisyetkilisi.servisisortakkontaktipid = 2
LEFT JOIN sason.vw_isortakkontakbilgiler kontakgenelmudur ON kontakgenelmudur.servisisortakid = isemir.servisisortakid AND kontakgenelmudur.servisisortakkontaktipid = 3
LEFT JOIN sason.vw_isortakkontakbilgiler kontaksatissorumlusu ON kontaksatissorumlusu.servisisortakid = isemir.servisisortakid AND kontaksatissorumlusu.servisisortakkontaktipid = 4
LEFT JOIN sason.vw_isortakkontakbilgiler kontakservismuduru ON kontakservismuduru.servisisortakid = isemir.servisisortakid AND kontakservismuduru.servisisortakkontaktipid = 5
LEFT JOIN sason.vw_isortakkontakbilgiler kontakfilo ON kontakfilo.servisisortakid = isemir.servisisortakid AND kontakfilo.servisisortakkontaktipid = 6
LEFT JOIN sason.vw_isortakkontakbilgiler kontaksatinalma ON kontaksatinalma.servisisortakid = isemir.servisisortakid AND kontaksatinalma.servisisortakkontaktipid = 7
LEFT JOIN sason.vw_isortakkontakbilgiler kontaksatismuduru ON kontaksatismuduru.servisisortakid = isemir.servisisortakid AND kontaksatismuduru.servisisortakkontaktipid = 8
LEFT JOIN sason.vw_isortakkontakbilgiler kontakgenelmuduryrd ON kontakgenelmuduryrd.servisisortakid = isemir.servisisortakid AND kontakgenelmuduryrd.servisisortakkontaktipid = 9
LEFT JOIN sason.vw_isortakkontakbilgiler kontakbolgemuduru ON kontakbolgemuduru.servisisortakid = isemir.servisisortakid AND kontakbolgemuduru.servisisortakkontaktipid = 10
LEFT JOIN sason.vw_isortakkontakbilgiler kontakbassofor ON kontakbassofor.servisisortakid = isemir.servisisortakid AND kontakbassofor.servisisortakkontaktipid = 11
LEFT JOIN sason.vw_isortakkontakbilgiler kontakdiger ON kontakdiger.servisisortakid = isemir.servisisortakid AND kontakdiger.servisisortakkontaktipid = 12
LEFT JOIN vt_servisisortaklar isortak ON isortak.dilkod = 'Turkish' AND ISORTAK.servisisortakid = isemir.servisisortakid
-- SERVIS BILGILERI (adres, telefon, fax)
LEFT JOIN(
SELECT se.id servisid, io.id isortakid, io.ad isortakad,
adres.adres, ulke.ad ulke_ad, il.ad il_ad, ilce.ad ilce_ad, tel.no telefon, fax.no fax
FROM servisler se,
isortaklar io,
isortakadresler ioadres,
adresler adres,
vw_ulkeler ulke,
iller il,
ilceler ilce,
isortaktelefonlar tel,
isortaktelefonlar fax
WHERE io.id(+) = se.isortakid
AND ioadres.ISORTAKID(+) = se.isortakid
AND ioadres.ISORTAKADRESTIPID(+) = 1
AND adres.id(+) = ioadres.adresid
AND ulke.dilkod(+) = 'Turkish'
AND ulke.id(+) = adres.ulkeid
AND il.id(+) = adres.ilid
AND ilce.id(+) = adres.ilceid
AND tel.isortakid(+)=io.id AND tel.isortaktelefontipid(+)=1
AND fax.isortakid(+)=io.id AND fax.isortaktelefontipid(+)=2
) servisbilgi ON servisbilgi.SERVISID = servis.servisid
/*
left join (
select
io.id servisisortakid, tel.no telefonno, fax.no faxno
from
servisisortaklar io,
servisisortaktelefonlar tel,
servisisortaktelefonlar fax
where
tel.servisisortakid(+)=io.id and tel.servisisortaktelefontipid(+)=1
and fax.servisisortakid(+)=io.id and fax.servisisortaktelefontipid(+)=2
) siotelfax on siotelfax.servisisortakid = isemir.servisisortakid
*/
LEFT JOIN(
SELECT sarac.id servisaracid, sarac.saseno, aractur.kod aractur, sruhsat.plaka, sarac.manolmayan manolmayan
FROM servisaraclar sarac,
araclar arac,
aracturler aractur,
servisvarlikruhsatlar sruhsat
WHERE
--esaarac.vin(+) = SARAC.SASENO
--and vm.esaaracid(+)=esaarac.id
arac.saseno(+)=sarac.saseno
AND aractur.id(+)=arac.aracturid
AND sruhsat.id(+)=sarac.servisvarlikruhsatid
) aracbilgiler on aracbilgiler.servisaracid = isemir.servisaracid
--WHERE kontak.servisisortakkontaktipad IN( 'BASSOFOR', 'FILOYONETICISI')
--order by servis.servisid, ISEMIR.ID
")
.Parameter("startDate", StartDate.startOfDay())
.Parameter("finishDate", FinishDate.endOfDay())
.GetDataTable()
.ToModels();
CloseCustomAppPool();
return(reportDataSource);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
// string dateQuery = "";
decimal selectedReceteId = ReceteIds.first().toString("0").cto <decimal>();
string receteIDQuery = $" = {selectedReceteId}";
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
if (ReceteIds.isNotEmpty())
{
receteIDQuery = $" in ({ReceteIds.joinNumeric(",")}) ";
}
else
{
selectedReceteId = 756;
receteIDQuery = $" in( {selectedReceteId} )";
}
/*
* StartDate = StartDate.startOfDay();
* FinishDate = FinishDate.endOfDay();
* dateQuery = ""+StartDate.ToString("dd/MM/yyyy") + "' AND '"+ FinishDate.ToString("dd/MM/yyyy")+"";
*/
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT
(SELECT vtsx.partnercode from vt_servisler vtsx where vtsx.servisid = b.SERVISID and vtsx.dilkod = 'Turkish') as partnercode,
(SELECT vtsxy.ISORTAKAD FROM vt_servisler vtsxy where vtsxy.dilkod = 'Turkish' and vtsxy.servisid = b.SERVISID ) as servisad,
b.*,
stok-miktar fark,
case when stok-miktar > -1 then 0
when stok-miktar < 0 then (stok-miktar)*kurlar_pkg.malzemefiyatindgetir(malzemeid,1,2) end TUTAR
from (SELECT a.*,sason.servisstok(sason.getservisstokid(a.malzemeid,servisid)) stok from (
SELECT
l.gkod,
l.ad,
S.SERVISID,
l.malzemeid,
MAX (miktar) miktar,
b.ad birimad,
R.KOD
FROM (SELECT * from receteler where id {receteIDQuery} ) r,
servisreceteler s,
recetemalzemeler m,
mt_malzemeler l,
vw_birimler b
WHERE
r.id = m.receteid
AND S.RECETEID = r.id
AND m.birimid = b.id
and b.dilkod='Turkish'
and l.malzemeid=m.malzemeid
GROUP BY l.gkod, l.ad, l.malzemeid, s.servisid, b.ad ,R.KOD) a
where servisid {servisIdQuery} ) b
order by kod , ad
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
string dateQuery = "";
#if DEBUG
selectedServisId = ServisId;
servisIdQuery = $" {selectedServisId}";
#endif
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in ( {selectedServisId} )";
}
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT *
FROM(
SELECT DISTINCT
(SELECT vtsx.partnercode FROM vt_servisler vtsx WHERE vtsx.servisid = a.servisid AND vtsx.dilkod = 'Turkish') AS partnercode,
a.durumid,
o3.ad isortakad,
d.id,
a.servisid,
sason.hashservisid (i.servisid) hashservisid,
f.faturano,
a.isemirno,
r.sirano,
tr.kod ayristirmatipad,
t.kod malzemekod,
t.ad malzemead,
d.turid,
a.arizakodu,
TO_CHAR(i.tamamlanmatarih,'dd/mm/yyyy') AS tamamlanmatarih,
TO_CHAR(i.kayittarih,'dd/mm/yyyy') AS kayittarih,
i.km,
i.kur,
i.aractipad,
i.modelno,
TO_CHAR(i.firstregdate,'dd/mm/yyyy') AS firstregdate,
t.tutar isemirtutar,
i.saseno,
r.isemirtipid,
a.pdfkdv,
a.pdfonaygeneltoplam,
a.pdfmatrah,
o2.ad,
a.claimstatus,
d.faturaid,
t.miktar,
t.tutar,
t.bruttutar,
CASE
WHEN (a.ayristirmatipid IN (1) AND d.faturaid IS NOT NULL)
OR a.claimstatus IN ('Z0110') --('Z057', 'Z060', 'Z070', 'Z0110')
THEN
'TAMAMLANMIS'
WHEN (a.ayristirmatipid IN (1) AND d.faturaid IS NOT NULL)
OR a.claimstatus IN ('Z107', 'Z109', 'Z999')
THEN
'REDDEDİLDİ'
ELSE
'DEVAM EDIYOR'
END durum,
st.kod servisstokturad,
CASE
WHEN ss.ureticivarlikid IS NULL THEN 'MAN' ELSE o1.ad
END uretici,
d.atutar,
d.pdfisletimucreti,
d.pdfitemid,
d.pdftoplam,
f.vno vergino,
orjinalkod,
kurlar_pkg.ortalamamaliyet (ss.id) ortalamamaliyet,
ROUND(
( 1
- t.tutar
/ CASE
WHEN t.bruttutar = 0 THEN NULL
ELSE t.bruttutar
END)
* 100,
2) indirimoran,
ic.tfattoplam,
TO_CHAR(ic.icmaltarihi,'dd/mm/yyyy') AS icmaltarihi,
CASE
WHEN (ic.icmaltarihi > SYSDATE)
THEN kurlar_pkg.caprazkurtarih (2, 1, SYSDATE)
WHEN (ic.icmaltarihi IS NULL )
THEN NULL
ELSE kurlar_pkg.caprazkurtarih (2, 1, ic.icmaltarihi)
END icmalkur,
servisstokturid,
bx.kod,
cx.ack ,
TO_CHAR(dx.ilktesciltarihi,'dd/mm/yyyy') AS ilktesciltarihi,
'{StartDate}' AS bastar,
'{FinishDate}' AS bittar,
i.id AS kyttarhidsi
FROM servisisemirler i,
servisisemirislemler r,
servisicmaller ic,
ayristirmadetaylar d,
ayristirmalar a,
ayristirmatipler tr,
/* servisisemirler i, */
faturalar f,
servisstokturler st,
sason.rp_isemirdetay t,
servisstoklar ss,
servisvarliklar o1,
servisvarliklar o2,
isortaklar o3,
servisler sv,
sason.isemirtipler bx ,
sason.lovturler cx,
servisvarlikruhsatlar dx
WHERE d.ayristirmaid = a.id
AND a.isemirno = i.isemirno
AND a.ayristirmatipid = tr.id
AND f.id(+) = d.faturaid
AND r.id = a.servisisemirislemid
AND t.referansid = d.referansid
AND st.id(+) = ss.servisstokturid
AND t.turid = d.turid
AND ss.kod(+) = t.kod
AND (ss.servisid = i.servisid OR ss.servisid IS NULL)
AND ss.ureticivarlikid = o1.id(+)
AND i.servisvarlikid = o2.id
AND sv.id = i.servisid
AND sv.isortakid = o3.id
AND a.icmalid = ic.id(+)
AND a.durumid = bx.id and d.turid = cx.id and i.saseno = dx.saseno
AND a.durumid = 1 AND i.id IN (SELECT ixx.id FROM servisisemirler ixx WHERE ixx.durumid = 1 AND ixx.servisid {servisIdQuery}
AND a.servisid = i.servisid
AND i.servisid {servisIdQuery}
AND ixx.kayittarih BETWEEN '{dateQuery}'
AND (i.saseno = NVL ('{SaseNo}', ixx.saseno)) )
AND a.ayristirmatipid NOT IN (1,2)
) asd
ORDER BY servisid, isemirno, servisstokturad ASC, kayittarih DESC
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
string dateQuery = "";
#if DEBUG
selectedServisId = ServisId;
servisIdQuery = $" = {selectedServisId}";
#endif
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
#region arizakod
string arizaKodQuery = "";
if (ArizaKod.Length == 7)
{
arizaKodQuery =
$" AND (SUBSTR(a.arizakodu, 1, 7) ) = '{ArizaKod}' ";
}
else
{
arizaKodQuery = "";
}
#endregion
#region saseNo
string saseNoQuery = "";
if (SaseNo.isNotEmpty())
{
saseNoQuery =
$" AND si.saseno = '{SaseNo}' ";
}
else
{
saseNoQuery = "";
}
#endregion
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT v.partnercode servis_kodu,
v.isortakad servis_adi,
(CASE
WHEN a.claimstatus = 'Z110'
THEN TO_CHAR (a.sonokumazamani,'DD.MM.YYYY')
ELSE NULL
END) garanti_kapanma_tarihi,
a.servisgarantino,
a.claimno,
a.claimstatus,
t.kod ayristirma_tipi,
t.garantituru,
r.plaka,
r.saseno,
vm.vehiclenum kisa_sase,
vm.vehicletype arac_tipi,
vm.modelnum arac_modeli,
a.pdfkmdurumu garanti_km,
vm.schadstkl emisyon_sinifi,
sv.ad musteri_adi,
sv.vergino,
a.arizakodu,
a.gtutar ic_tutari,
a.id,
TO_CHAR(si.tamamlanmatarih,'DD.MM.YYYY') ie_kapanma_tarihi,
a.pdftalepgeneltoplam oc_tutari,
v.gsad servis_garanti_sorumlusu,
v.tbsad teknik_bolge_sorumlusu,
SI.ISEMIRNO,
ss.kod ARZ_NDN_MLZ_KOD,
ss.ad ARZ_NDN_MLZ_AD
FROM vt_servisler v,
ayristirmalar a,
servisvarlikruhsatlar r,
esaaraclar ea,
vx_vis_vehiclemaster vm,
ayristirmatipler t,
servisvarliklar sv,
servisisemirler si,
servisisemirislemler sisl,
servisstoklar ss
WHERE v.dilkod = 'Turkish'
AND a.servisid = v.servisid
AND si.isemirno = a.isemirno
AND r.saseno = ea.vin
AND ea.id = vm.esaaracid
AND a.ayristirmatipid = t.id
AND sv.id = r.servisvarlikid
AND r.servisid=a.servisid
AND si.saseno=r.saseno
AND si.tamamlanmatarih BETWEEN '{dateQuery}'
AND a.durumid = 1
AND a.servisid=SI.SERVISID
AND sisl.id = a.servisisemirislemid
AND SISL.ARIZANEDENMALZEMEID = ss.malzemeid
AND SS.SERVISID=A.SERVISID
AND a.ayristirmatipid NOT IN(1,2,8)
AND a.servisid {servisIdQuery}
AND SS.SERVISID {servisIdQuery}
AND V.SERVISID {servisIdQuery}
AND R.SERVISID {servisIdQuery}
and SV.SERVISID {servisIdQuery}
and SI.SERVISID {servisIdQuery}
{saseNoQuery}
{arizaKodQuery}
ORDER BY servis_kodu, si.tamamlanmatarih DESC
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
string dateQuery = "";
#if DEBUG
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
#endif
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT distinct
(select vtsx.partnercode from vt_servisler vtsx where vtsx.servisid = a.servisid and vtsx.dilkod = 'Turkish') as partnercode,
a.durumid,
o3.ad ISORTAKAD,
d.id,
a.servisid,
sason.hashservisid (i.servisid) hashservisid,
f.faturano,
a.isemirno,
r.sirano,
tr.kod ayristirmatipad,
t.kod malzemekod,
t.ad malzemead,
D.TURID,
a.arizakodu,
to_char(I.TAMAMLANMATARIH,'dd/mm/yyyy') as TAMAMLANMATARIH,
to_char(I.KAYITTARIH,'dd/mm/yyyy') as KAYITTARIH,
I.KM,
I.KUR,
i.aractipad,
i.modelno,
to_char(i.firstregdate,'dd/mm/yyyy') as firstregdate,
T.TUTAR isemirtutar,
i.saseno,
R.ISEMIRTIPID,
A.PDFKDV,
A.PDFONAYGENELTOPLAM,
A.PDFMATRAH,
O2.AD,
a.claimstatus,
d.faturaid,
T.MIKTAR,
T.TUTAR,
T.BRUTTUTAR,
CASE
WHEN (a.ayristirmatipid IN (1) AND d.faturaid IS NOT NULL)
OR a.claimstatus IN ('Z057', 'Z060', 'Z070', 'Z0110')
THEN
'TAMAMLANMIS'
ELSE
'DEVAM EDIYOR'
END
DURUM,
St.kod SERVISSTOKTURad,
CASE WHEN ss.ureticivarlikid IS NULL THEN 'MAN' ELSE O1.AD END
uretici,
d.atutar,
D.PDFISLETIMUCRETI,
D.PDFITEMID,
D.PDFTOPLAM,
F.VNO vergino,
orjinalkod,
KURLAR_PKG.ORTALAMAMALIYET (ss.id) ortalamamaliyet,
ROUND (
( 1
- t.tutar
/ CASE WHEN t.bruttutar = 0 THEN NULL ELSE t.bruttutar END)
* 100,
2)
indirimoran,
IC.TFATTOPLAM,
to_char(IC.ICMALTARIHI,'dd/mm/yyyy') as ICMALTARIHI,
CASE
WHEN (ic.icmaltarihi > sysdate) then KURLAR_PKG.CAPRAZKURTARIH (2, 1, sysdate)
WHEN (ic.icmaltarihi is null ) then null
ELSE KURLAR_PKG.CAPRAZKURTARIH (2, 1d, ic.icmaltarihi) end
icmalkur,
servisstokturid,
Bx.KOD,
cx.ack ,
to_char(dx.ilktesciltarihi,'dd/mm/yyyy') as ilktesciltarihi,
'{StartDate}' as bastar,
'{FinishDate}' as bittar,
i.id as kyttarhidsi
FROM servisisemirler i, servisisemirislemler r,
servisicmaller ic,
ayristirmadetaylar d,
ayristirmalar a,
ayristirmatipler tr,
/* servisisemirler i, */
faturalar f,
servisstokturler st,
-- sason.rp_isemirdetay t,
(
SELECT z.id servisisemirid,
I.ID servisisemirislemid,
m.id referansid,
m.miktar,--
'MALZEME' TUR,
m.tutar bruttutar, --
m.indirimlitutar tutar,--
s.kod,--
s.ad,--
z.servisid,
Z.TAMAMLANMATARIH,
1 turid, --
CASE WHEN o.orjinalgkod IS NULL THEN s.kod ELSE o.orjinalgkod END
orjinalkod,
isemirno --
FROM servisisemirislemler i,
servisismislemmalzemeler m,
(SELECT m1.id malzemeid,
m1.kod,
m1.gkod,
m2.kod orjinalkod,
m2.gkod orjinalgkod,
m1.orjinalmalzemeid
FROM malzemeler m1, malzemeler m2
WHERE m1.orjinalmalzemeid = M2.ID) o,
servisstoklar s,
servisisemirler z
WHERE i.id = M.SERVISISEMIRISLEMID
AND s.id = m.servisstokid
AND z.id = i.servisisemirid
AND (kendigetirdi <> 1 OR kendigetirdi IS NULL)
AND (disaridayaptirdi <> 1 OR disaridayaptirdi IS NULL)
AND s.malzemeid = o.malzemeid(+)
AND (bakimislemynedenid IS NULL)
AND ( I.ISEMIRUYGULAMAMANEDENID IS NULL
OR I.ISEMIRUYGULAMAMANEDENID = 8)
AND m.durumid = 1
AND kullanildi = 1
and z.servisid {servisIdQuery}
and z.KAYITTARIH between '{dateQuery}'
UNION ALL
SELECT z.id servisisemirid,
i.id servisisemirislemid,
s.id referansid,
S.MIKTAR,
'ISCILIK' TUR,
s.tutar bruttutar,
s.indirimlitutar tutar,
NVL (NVL (c.kod, t.kod), s.aciklama) kod,
NVL (NVL (c.ad, t.ad), s.aciklama) ad,
z.servisid,
Z.TAMAMLANMATARIH,
2 turid,
NULL orjinalkod,
isemirno
FROM servisisemirislemler i,
servisismislemiscilikler s,
vw_servisiscilikler t,
mt_iscilikler c,
servisisemirler z
WHERE i.id = S.SERVISISEMIRISLEMID
AND c.iscilikid(+) = S.ISCILIKID
AND z.id = i.servisisemirid
AND (c.dilkod = 'Turkish' OR c.dilkod IS NULL)
AND (disaridayaptirdi <> 1 OR disaridayaptirdi IS NULL)
AND (bakimislemynedenid IS NULL)
AND s.durumid = 1
AND T.DILKOD(+) = 'Turkish'
AND S.SERVISISCILIKID = T.ID(+)
AND ( I.ISEMIRUYGULAMAMANEDENID IS NULL
OR I.ISEMIRUYGULAMAMANEDENID = 8)
and z.servisid {servisIdQuery}
and z.KAYITTARIH between '{dateQuery}'
UNION ALL
SELECT z.id servisisemirid,
i.id servisisemirislemid,
k.id referansid,
K.MIKTAR,
'DKALEM' TUR,
k.tutar bruttutar,
k.indirimlitutar tutar,
REPLACE (LOWER (sason.fn_rmtr (d.ad)), ' ') kod,
ad,
z.servisid,
Z.TAMAMLANMATARIH,
3 turid,
NULL orjinalkod,
isemirno
FROM servisisemirislemler i,
servisismislemdkalemler k,
vw_digerkalemler d,
servisisemirler z
WHERE i.id = k.SERVISISEMIRISLEMID
AND z.id = i.servisisemirid
AND d.id = K.DIGERKALEMID
AND dilkod = 'Turkish'
AND k.durumid = 1
AND ( I.ISEMIRUYGULAMAMANEDENID IS NULL
OR I.ISEMIRUYGULAMAMANEDENID = 8)
and z.servisid {servisIdQuery}
and z.KAYITTARIH between '{dateQuery}'
UNION ALL
SELECT z.id servisisemirid,
i.id servisisemirislemid,
k.id referansid,
1 miktar,
'DHIZMET' TUR,
k.tutar bruttutar,
k.indirimlitutar tutar,
REPLACE (LOWER (sason.fn_rmtr (d.aciklama)), ' ') kod,
d.aciklama ad,
z.servisid,
Z.TAMAMLANMATARIH,
4 turid,
NULL orjinalkod,
isemirno
FROM servisisemirislemler i,
servisismislemdhizmetler k,
servisdishizmetalimlar d,
servisisemirler z
WHERE i.id = k.SERVISISEMIRISLEMID
AND z.id = i.servisisemirid
AND d.id = K.SERVISDISHIZMETALIMID
AND k.durumid = 1
AND ( I.ISEMIRUYGULAMAMANEDENID IS NULL
OR I.ISEMIRUYGULAMAMANEDENID = 8)
and z.servisid {servisIdQuery}
and z.KAYITTARIH between '{dateQuery}'
) t,
servisstoklar ss,
servisvarliklar o1,
servisvarliklar o2,
isortaklar o3,
servisler sv,
sason.isemirtipler bx ,
sason.lovturler cx,
servisvarlikruhsatlar dx
WHERE d.ayristirmaid = a.id
AND a.isemirno = i.isemirno
AND a.ayristirmatipid = tr.id
AND f.id(+) = d.faturaid
AND r.id = a.servisisemirislemid
AND T.REFERANSID = d.referansid
AND st.id(+) = ss.servisstokturid
AND t.turid = d.turid
AND ss.kod(+) = T.KOD
AND (ss.servisid = i.servisid OR ss.servisid IS NULL)
AND ss.ureticivarlikid = O1.ID(+)
AND I.SERVISVARLIKID = O2.id
AND sv.id = i.servisid
AND sv.isortakid = o3.id
AND A.ICMALID = ic.id(+)
AND a.durumid = bx.id and D.TURID = Cx.ID AND i.saseno = dx.saseno
AND a.durumid = 1
and a.servisid = i.servisid
and i.servisid {servisIdQuery}
AND ( ss.servisstokturid = '6' OR
t.kod IN ('09.11001-0022' , '09.11001-0153' , '09.11001-0160', '09.11001-0823',
'09.11001-0820' , '09.11070-0641' , '09.11070-0434' , '09.11001-1003',
'09.11001-1000' , '09.11001-0013' , 'ZU.GOSAE-0010' , '09.11003-0514',
'09.11003-0507' , '09.11003-0517' , '09.11070-0732' , '09.11070-0692',
'09.11070-0742' , 'ZU.GGRES-0010' , 'ZU.GHD46-0010' , 'ZU.GHD46-0020' ) )
and i.id in (select ixx.id from servisisemirler ixx where ixx.servisid {servisIdQuery} and ixx.KAYITTARIH between '{dateQuery}' AND (ixx.saseno = NVL ('{SaseNo}', ixx.saseno)) )
-- ORDER BY i.servisid, i.id desc
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
string dateQuery = "";
#if DEBUG
selectedServisId = ServisId;
servisIdQuery = $" = {selectedServisId}";
#endif
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT e.saseno,
e.kayittarih,
e.tamamlanmatarih,
e.km,
e.endeks AS litre,
e.saat,
--E.TBITISTARIHI,
e.isemirno,
t.isortakad,
bakimtoplu AS bakim_no,
t.partnercode AS yskod,
CASE
WHEN bakimstatu =1 THEN 'TAM'
WHEN bakimstatu =0 THEN 'EKSIK'
ELSE 'HATALI'
END AS bakimstatu,
a.esagarantino,
a.servisgarantino,
a.claimstatus
FROM servisisemirler e,
servisisemirislemler i,
vt_servisler t,
ayristirmalar a
WHERE e.kayittarih BETWEEN '{dateQuery}'
AND e.id = i.servisisemirid
AND isemirtipid =1
AND (e.saseno = NVL ('{SaseNo}', e.saseno))
AND t.servisid = e.servisid
AND e.isemirno=a.isemirno
AND e.servisid {servisIdQuery}
AND t.dilkod='Turkish'
AND e.tamamlanmatarih IS NOT NULL
AND bakimtoplu IS NOT NULL
AND e.teknikolaraktamamla = 1
AND a.servisisemirislemid = i.id
ORDER BY e.servisid, e.kayittarih DESC
")
.GetDataTable(mr)
.ToModels();
//Eski sorgu
//SELECT E.SASENO,
// E.KAYITTARIH,
// E.TAMAMLANMATARIH,
// E.KM,
// E.ENDEKS as LITRE,
// E.SAAT,
// --E.TBITISTARIHI,
// E.ISEMIRNO,
// T.ISORTAKAD,
// BAKIMTOPLU as BAKIM_NO,
// T.PARTNERCODE as YSKOD,
// case
// when BAKIMSTATU =1 then 'TAM'
// when BAKIMSTATU =0 then 'EKSIK'
// else 'HATALI' end as BAKIMSTATU
//FROM SERVISISEMIRLER E,
// SERVISISEMIRISLEMLER I,
// VT_SERVISLER T
//WHERE E.KAYITTARIH BETWEEN '{dateQuery}'
// and E.ID = I.SERVISISEMIRID
// and ISEMIRTIPID =1
// and (E.SASENO = NVL ('{SaseNo}', E.SASENO))
// and T.SERVISID = E.SERVISID
// and E.SERVISID {servisIdQuery}
// and T.DILKOD='Turkish'
// and E.TAMAMLANMATARIH is not null
// /* and BAKIMTOPLU is not null */
// and E.TEKNIKOLARAKTAMAMLA = 1
// ORDER BY E.SERVISID, E.KAYITTARIH desc
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
string servisIdQuery = "";
string dateQuery = "";
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
servisIdQuery = $" > 1 ";
}
string addSQLAracTur = "";
addSQLAracTur = "";
string addSQLAracTurGroupBy = "";
string addSQLAracTurConcat1 = " vtsrv.isortakad, ";
string addSQLAracTurConcat2 = " 'Türkiye Geneli' isortakad, ";
if (AracTurIds.isNotEmpty())
{
addSQLAracTur = $" AND ar.id in ({AracTurIds.joinNumeric(",")}) ";
addSQLAracTurGroupBy = ", ar.kod ";
addSQLAracTurConcat1 = " CONCAT(CONCAT(vtsrv.isortakad, ' - '),ar.kod) isortakad, ";
addSQLAracTurConcat2 = " CONCAT(CONCAT('Türkiye Geneli', ' - '),ar.kod) isortakad, ";
}
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
List <object> reportDataSource = AppPool.EbaTestConnector.CreateQuery($@"
SELECT * FROM (
SELECT
trunc(round( sum((case when ie.araccikiszamani is not null then ie.araccikiszamani else ie.tamamlanmatarih end ) - ie.kayittarih)/count(ie.id),2)) ||','||
(trunc((round( sum((case when ie.araccikiszamani is not null then ie.araccikiszamani else ie.tamamlanmatarih end ) - ie.kayittarih)/count(ie.id),2)-
trunc(round( sum((case when ie.araccikiszamani is not null then ie.araccikiszamani else ie.tamamlanmatarih end ) - ie.kayittarih)/count(ie.id),2)))*24))||'' DOWNTIME ,
to_char(ie.tamamlanmatarih,'YYYY.MM') DONEM,
ie.servisid,
{addSQLAracTurConcat1}
vtsrv.partnercode
FROM
servisisemirler ie,
vt_servisler vtsrv,
aracturler ar ,
servisvarliklar servar
WHERE ie.tamamlanmatarih between '{dateQuery}' and ie.teknikolaraktamamla=1 and ie.servisid {servisIdQuery}
and (ie.arackazali <> 1 or ie.arackazaaciklama is null or ie.arackazaaciklama = '')
and vtsrv.dilkod(+) = 'Turkish' and vtsrv.servisid(+)=ie.servisid
and IE.SERVISVARLIKID=servar.id
and SERVAR.VARLIKTIPID<>3
and ar.kod = ie.aractipad and ar.durumid =1
{addSQLAracTur}
GROUP BY to_char(ie.tamamlanmatarih,'YYYY.MM'), ie.servisid, vtsrv.isortakad, vtsrv.partnercode {addSQLAracTurGroupBy}
UNION
SELECT
trunc(round( sum((case when ie.araccikiszamani is not null then ie.araccikiszamani else ie.tamamlanmatarih end ) - ie.kayittarih)/count(ie.id),2)) ||','||
(trunc((round( sum((case when ie.araccikiszamani is not null then ie.araccikiszamani else ie.tamamlanmatarih end ) - ie.kayittarih)/count(ie.id),2)-
trunc(round( sum((case when ie.araccikiszamani is not null then ie.araccikiszamani else ie.tamamlanmatarih end ) - ie.kayittarih)/count(ie.id),2)))*24))||'' DOWNTIME ,
to_char(ie.tamamlanmatarih,'YYYY.MM') DONEM,
-1 servisid,
{addSQLAracTurConcat2}
'R001' partnercode
FROM
servisisemirler ie,
vt_servisler vtsrv,
aracturler ar ,
servisvarliklar servar
WHERE ie.tamamlanmatarih between '{dateQuery}' and ie.teknikolaraktamamla=1
and (ie.arackazali <> 1 or ie.arackazaaciklama is null or ie.arackazaaciklama = '')
and IE.SERVISVARLIKID=servar.id
and SERVAR.VARLIKTIPID<>3
and vtsrv.dilkod(+) = 'Turkish' and vtsrv.servisid(+)=ie.servisid
and ar.kod = ie.aractipad and ar.durumid =1
{addSQLAracTur}
GROUP BY to_char(ie.tamamlanmatarih,'YYYY.MM'){addSQLAracTurGroupBy}
) asd
ORDER BY DONEM , servisid
")
.GetDataTable()
.ToModels();
CloseCustomAppPool();
return(reportDataSource);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
string dateQuery = "";
string fDate = "";
#if DEBUG
selectedServisId = ServisId;
servisIdQuery = $" = {selectedServisId}";
#endif
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
fDate = "'" + FinishDate.ToString("dd/MM/yyyy") + "'";
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT *
FROM(
SELECT DISTINCT MAX(i.kayittarih) AS kayit_tarihi,
c.*,
(SELECT vtsx.partnercode FROM vt_servisler vtsx WHERE vtsx.servisid = c.servisid AND vtsx.dilkod = 'Turkish') AS partnercode,
(SELECT vtsxy.ISORTAKAD FROM vt_servisler vtsxy WHERE vtsxy.dilkod = 'Turkish' AND vtsxy.servisid = c.servisid) AS servisad,
i.plaka,
i.aractipad,
i.modelno,
i.isemirno,
s_ort.ad AS musteri_ad,
i.musteriad AS Araci_getiren,
i.musteritelefon AS Araci_getiren_tel,
srv_knt.ad AS Filo_yoneticisi,
knt_tel.no AS filo_tel
FROM( SELECT a.saseno,
b.saseno AS saseyeni,
a.servisid
FROM (SELECT DISTINCT saseno ,servisid
FROM servisisemirler
WHERE kayittarih < SYSDATE-180
AND saseno IS NOT NULL) a,
(SELECT DISTINCT saseno
FROM servisisemirler
WHERE kayittarih > SYSDATE-180
AND saseno IS NOT NULL) b
WHERE a.saseno=b.saseno(+)) c,
servisisemirler i,
servisisortaklar s_ort,
vt_servisisortakkontaklar srv_knt,
vt_kontaktelefonlar knt_tel
WHERE saseyeni IS NULL
AND c.saseno=i.saseno
AND i.servisisortakid = s_ort.id
AND i.servisisortakid = srv_knt.servisisortakid
AND srv_knt.servisisortakkontaktipid = 6
AND srv_knt.kontakid = knt_tel.kontakid
AND knt_tel.dilkod = 'Turkish'
AND i.kayittarih BETWEEN '{dateQuery}'
AND i.servisid {servisIdQuery}
AND (c.saseno = NVL ('{SaseNo}', c.saseno))
AND 0 = (select count(kayittarih) from servisisemirler where kayittarih > {fDate} AND saseno = c.saseno)
AND i.kayittarih = (select max(kayittarih) from servisisemirler where kayittarih < {fDate} AND saseno = c.saseno)
GROUP BY c.saseno, c.saseyeni, i.plaka, i.aractipad,i.modelno, i.isemirno, i.musteriad,s_ort.ad,i.musteritelefon, srv_knt.ad,knt_tel.no,c.servisid
) asd
ORDER BY kayit_tarihi
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}