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
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 si.tamamlanmatarih BETWEEN '{dateQuery}'
AND a.durumid=1
AND a.ayristirmatipid NOT IN(1,2,8)
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 = "";
#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,
si.tamamlanmatarih 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,
r.plaka,
r.saseno,
vm.vehiclenum kisa_sase,
vm.vehicletype arac_tipi,
vm.modelnum arac_modeli,
si.km,
vm.schadstkl emisyon_sinifi,
sv.ad musteri_adi,
sv.vergino,
a.arizakodu,
si.aciklama,
si.arackazaaciklama kaza_aciklama,
si.sfnotu servis_fisi_notu,
a.id,
si.tutar,
si.indirimlitutar,
si.ttutar tahmini_tutar,
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.teknikolaraktamamla=1
AND a.ayristirmatipid in (1, 2)
AND a.servisid {servisIdQuery}
AND si.tamamlanmatarih BETWEEN '{dateQuery}'
{saseNoQuery}
{arizaKodQuery}
order by servis_kodu, si.tamamlanmatarih desc
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}