public override object ExecuteReport(MethodReturn refMr = null)
{
string servisIdQuery = "";
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
servisIdQuery = $" > 1 ";
}
List <object> reportDataSource = AppPool.EbaTestConnector.CreateQuery($@"
SELECT
tarihler.tarih, tarihler.yil, tarihler.ay, 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();
CloseCustomAppPool();
return(reportDataSource);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
#if DEBUG
selectedServisId = ServisId;
servisIdQuery = $" = {selectedServisId}";
#endif
MethodReturn mr = new MethodReturn();
List <ReportData> reportDataSource = new List <ReportData>();
List <QueryResult> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
select * from (select * from sason.rptable_yedekparcadetay where servisid {servisIdQuery} and tarih between {{startDate}} AND {{finishDate}}) rpt
")
.Parameter("startDate", StartDate.startOfDay())
.Parameter("finishDate", FinishDate.endOfDay())
.GetDataTable(mr)
.ToModels <QueryResult>();
CloseCustomAppPool();
return(reportDataSource);
}
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
(select vtsx.partnercode from vt_servisler vtsx where vtsx.servisid = a.servisid and vtsx.dilkod = 'Turkish') as partnercode,
(Select vtsxy.ISORTAKAD FROM vt_servisler vtsxy where vtsxy.dilkod = 'Turkish' and vtsxy.servisid = a.servisid) as servisad,
a.faturaturid,
a.faturano,
a.islemtarihi,
a.cariunvan,
a.bruttoplam,
a.indirimtoplam,
a.netkdvtoplam,
a.nettutar,
a.toplam,
a.carikod,
a.vno,
a.servisid
FROM faturalar a
WHERE a.servisid {servisIdQuery}
and a.islemtarihi between '{dateQuery}'
and a.faturaturid=4
ORDER BY servisad ,a.islemtarihi desc
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
string dateQuery = "";
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.Ad,
A.Saseno,
Bgrup.Kod AS Bakim_Grubu,
Bst.Kod AS Sozlesme_Tipi,
Bksoz.Bastarih AS Baslangic_Tarihi,
Bksoz.Bittarih AS Bitis_Tarihi,
Bksoz.Bakimadedi,
Bksoz.Oncekibakimadedi,
Bksoz.Kullanilan,
Bksoz.Tutar,
Pb.Kod ,
(CASE
WHEN Bksoz.Durumid=1 THEN 'AKTIF'
ELSE 'PASIF'
END) AS Sozlesme_Durum,
Bksoz.Km,
Bksoz.Saat,
Bksoz.Litre
FROM
Bakimsozlesmeler Bksoz,
Bakimgruplar Bgrup,
Araclar A,
Bakimsozlesmetipler Bst,
Varliklar V,
Parabirimler Pb
WHERE
Bksoz.Bakimgrupid=Bgrup.Id AND
A.Id=Bksoz.Aracid AND
Bksoz.Bakimsozlesmetipid=Bst.Id AND
Bksoz.Varlikid=V.Id AND
Pb.Id=Bksoz.Parabirimid AND
(A.Saseno = NVL('{SaseNo}', A.Saseno)) AND
Bksoz.Bastarih BETWEEN '{dateQuery}'
ORDER BY Bksoz.Id DESC
")
.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} )";
}
List <object> reportDataSource = AppPool.EbaTestConnector.CreateQuery($@"
select
--ie.id ISEMIR_ID,
srv.ISORTAKAD SERVIS_AD, srv.partnercode SERVIS_PARTNER_KOD,
ie.isemirno ISEMIR_NO, ie.KAYITTARIH ISEMIR_KAYITTARIHI, ie.TAMAMLANMATARIH ISEMIR_TAMAMLANMA_TARIHI, ie.saseno ISEMIR_SASENO
--,iei.ID ISEMIR_ISLEM_ID
,atip.kod GARANTI_KODU, atip.ad GARANTI_TIPI
,ay.ARIZAKODU AYR_ARIZAKODU, ay.CLAIMNO AYR_CLAIMNO, AY.ESAGARANTINO AYR_ESA_GARANTINO, AY.SERVISGARANTINO AYR_SERVIS_GARANTI_NO, AY.PDFGARANTINO AYR_PDF_GARANTI_NO
,mlz.KOD MALZEME_KODU, mlz.AD MALZEME_ADI, MLZ.MIKTAR MALZEME_MIKTAR, MLZ.BIRIM_AD MALZEME_BIRIM_AD
from (select * from servisisemirler where TAMAMLANMATARIH BETWEEN {{startDate}} AND {{finishDate}} and TEKNIKOLARAKTAMAMLA = 1 AND SERVISID {servisIdQuery}) ie
left join vt_servisler srv on srv.dilkod = 'Turkish' and srv.servisid = ie.servisid
-- AYRISTIRMA TIPLER
left join vw_ayristirmatipler atip on atip.id not in (1,2) and atip.dilkod = 'Turkish'
-- SERVIS ISEMIR ISLEMLER
left join servisisemirislemler iei on iei.servisisemirid = ie.id
-- AYRISTIRMALAR
inner join ayristirmalar ay on ay.ayristirmatipid = atip.id and AY.SERVISISEMIRISLEMID = iei.id
-- MALZEMELER
inner join (
select
a.id AYRISTIRMAID, 1 GROUPID, 'MALZEME' GROUPNAME, st.kod KOD, st.ad AD, ad.miktar MIKTAR, brm.ad BIRIM_AD, ad.gtutar TUTAR, AD.PDFMIKTAR, AD.PDFNETFIYAT, AD.PDFISLETIMUCRETI, AD.PDFONAYLANANYUZDE, AD.PDFTOPLAM, AD.PDFPARABIRIMID
from
ayristirmalar a, ayristirmadetaylar ad, servisismislemmalzemeler im, servisstoklar st, vw_birimler brm
where
AD.AYRISTIRMAID(+) = a.id
and ad.turid = 1 --malzeme
and im.id(+) = ad.referansid
and st.id(+)= im.servisstokid
and brm.id(+) = st.birimid and BRM.DILKOD(+) = 'Turkish'
) mlz on mlz.ayristirmaid = ay.ID
")
.Parameter("startDate", StartDate.startOfDay())
.Parameter("finishDate", FinishDate.endOfDay())
.GetDataTable()
.ToModels();
CloseCustomAppPool();
return(reportDataSource);
}
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}";
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
servisid,
partnercode,
servisad ,
isemirno,
saseno,
kayittarih,
tutar,
indirimlitutar,
plaka,
aw,
tip,
tipkod,
tipaciklama,
tipmiktar,
tiptutar ,
tipindirimlitutar,
tipbirimfiyat,
(CASE
WHEN isemirtipid = 1
THEN 'BAKIM'
WHEN isemirtipid = 2
THEN 'AKSIYON'
WHEN isemirtipid = 3
THEN 'ONARIM'
WHEN isemirtipid = 4
THEN 'ONARIMKAZA'
WHEN isemirtipid = 5
THEN 'EURO6CBAKIM'
WHEN isemirtipid = 6
THEN '2.ELONARIM'
END) AS isemirtip
FROM(
SELECT
a.servisid,
(SELECT vtsx.partnercode FROM vt_servisler vtsx WHERE vtsx.servisid = a.servisid AND vtsx.dilkod = 'Turkish') AS partnercode,
(SELECT vtsxy.ISORTAKAD FROM vt_servisler vtsxy WHERE vtsxy.dilkod = 'Turkish' AND vtsxy.servisid = a.servisid ) AS servisad ,
a.isemirno,
a.saseno,
a.kayittarih,
a.tutar,
a.indirimlitutar,
a.plaka,
TO_CHAR((SELECT zx.tutar/10 FROM servisiscilikfiyatlar zx WHERE zx.servisid = a.servisid AND zx.ayristirmatipid IS NULL )) AS aw,
'iscilik' AS tip ,isciliklerxx.kod AS tipkod,
isciliklerxx.aciklama AS tipaciklama,
isciliklerxx.miktar AS tipmiktar,
isciliklerxx.tutar AS tiptutar ,
isciliklerxx.indirimlitutar AS tipindirimlitutar,
0 AS tipBIRIMFIYAT,
b.isemirtipid
FROM servisisemirler a
INNER JOIN servisisemirislemler b ON a.ID = b.servisisemirid AND b.durumid = 1
LEFT JOIN (
SELECT DISTINCT c1.servisisemirislemid, c1.tutar , c1.aciklama, c1.indirimlitutar, c1.miktar, g1.kod
FROM servisismislemiscilikler c1
INNER JOIN mt_iscilikler g1 ON c1.iscilikid = g1.iscilikid AND g1.durumid = c1.durumid
LEFT JOIN servisiscilikler h1 ON h1.id = c1.servisiscilikid AND h1.durumid = c1.durumid
WHERE c1.durumid = 1 AND
g1.dilkod='Turkish'
) isciliklerxx ON b.id = isciliklerxx.servisisemirislemid
WHERE a.KAYITTARIH BETWEEN '{dateQuery}' AND
a.servisid {servisIdQuery}
UNION
SELECT
a.servisid,
(SELECT vtsx.partnercode from vt_servisler vtsx WHERE vtsx.servisid = a.servisid AND vtsx.dilkod = 'Turkish') AS partnercode,
(SELECT vtsxy.isortakad FROM vt_servisler vtsxy WHERE vtsxy.dilkod = 'Turkish' AND vtsxy.servisid = a.servisid ) AS servisad ,
a.isemirno,
a.saseno,
a.kayittarih,
a.tutar,
a.indirimlitutar,
a.plaka,
--to_char((SELECT zx.tutar/10 FROM servisiscilikfiyatlar zx WHERE zx.servisid = a.servisid AND zx.AYRISTIRMATIPID is null )) AS aw,,
'' AS aw,
'malzeme' AS tip , mmalzemelerxx.kod AS mmalzemelerkod , mmalzemelerxx.ad AS mmalzemelerad,
mmalzemelerxx.miktar AS mmalzemelermiktar, mmalzemelerxx.tutar AS mmalzemelertutar,
mmalzemelerxx.indirimlitutar AS mmalzemelerindirimlitutar, mmalzemelerxx.malzemebirimfiyat AS birimfiyat,
b.isemirtipid
FROM servisisemirler a
INNER JOIN servisisemirislemler b ON a.ID = b.servisisemirid AND b.durumid = 1
LEFT JOIN (
SELECT DISTINCT c2.servisisemirislemid, g2.kod, g2.ad, c2.miktar, c2.malzemebirimfiyat, c2.tutar, c2.indirimlitutar
FROM servisismislemmalzemeler c2
INNER JOIN servisstoklar g2 ON c2.servisstokid = g2.id
WHERE
c2.durumid = 1
) mmalzemelerxx ON b.id = mmalzemelerxx.servisisemirislemid
WHERE a.kayittarih BETWEEN '{dateQuery}' AND
a.servisid {servisIdQuery}
UNION
SELECT
a.servisid,
(SELECT vtsx.partnercode FROM vt_servisler vtsx WHERE vtsx.servisid = a.servisid AND vtsx.dilkod = 'Turkish') AS partnercode,
(SELECT vtsxy.ISORTAKAD FROM vt_servisler vtsxy WHERE vtsxy.dilkod = 'Turkish' AND vtsxy.servisid = a.servisid ) AS servisad ,
a.isemirno,
a.saseno,
a.kayittarih,
a.tutar,
a.indirimlitutar,
a.plaka,
--to_char((SELECT zx.tutar/10 FROM servisiscilikfiyatlar zx WHERE zx.servisid = a.servisid AND zx.AYRISTIRMATIPID is null )) AS aw,
'' AS aw,
'kalem' AS tip , kalemlerxx.kod AS kalemlerkod,
kalemlerxx.acikalama AS kalemler, kalemlerxx.miktar AS kalemlermiktar,
kalemlerxx.tutar AS kalemlertutar, kalemlerxx.indirimlitutar AS kalemlerindirimlitutar,
0 AS birimfiyat,
b.isemirtipid
FROM servisisemirler a
INNER JOIN servisisemirislemler b ON a.ID = b.servisisemirid AND b.durumid = 1
LEFT JOIN (
SELECT DISTINCT c3.servisisemirislemid, c3.digerkalemid, g3.kod, c3.tutar, c3.indirimlitutar, c3.miktar, vwd.ad AS acikalama
FROM SERVISISMISLEMDKALEMLER c3
INNER JOIN digerkalemler g3 ON C3.DIGERKALEMID = g3.id
INNER JOIN vw_digerkalemler vwd ON vwd.kod = g3.kod AND vwd.dilid = 0 AND vwd.durumid = 1
WHERE
c3.DURUMID = 1
) kalemlerxx ON b.id=kalemlerxx.servisisemirislemid
WHERE a.kayittarih BETWEEN '{dateQuery}' AND
a.servisid {servisIdQuery}
UNION
SELECT
a.servisid,
(SELECT vtsx.partnercode FROM vt_servisler vtsx WHERE vtsx.servisid = a.servisid AND vtsx.dilkod = 'Turkish') AS partnercode,
(SELECT vtsxy.ISORTAKAD FROM vt_servisler vtsxy WHERE vtsxy.dilkod = 'Turkish' AND vtsxy.servisid = a.servisid ) AS servisad ,
a.isemirno,
a.saseno,
a.kayittarih,
a.tutar,
a.indirimlitutar,
a.plaka,
--to_char((SELECT zx.tutar/10 FROM servisiscilikfiyatlar zx WHERE zx.servisid = a.servisid AND zx.AYRISTIRMATIPID IS NULL )) AS aw,
'' AS aw,
'hizmet' AS tip , '' kod ,
hizmetlerxx.aciklama AS hizmetleraciklama, 0 AS miktar, hizmetlerxx.tutar AS hizmetlertutar,
hizmetlerxx.indirimlitutar AS hizmetlerindirimlitutar , 0 AS birimfiyat,
b.isemirtipid
FROM servisisemirler a
INNER JOIN servisisemirislemler b ON a.ID = b.servisisemirid AND b.durumid = 1
LEFT JOIN (
SELECT DISTINCT c4.servisisemirislemid, c4.aciklama, c4.tutar, c4.indirimlitutar
FROM SERVISISMISLEMDHIZMETLER c4
WHERE
c4.DURUMID = 1
) hizmetlerxx ON b.id=hizmetlerxx.servisisemirislemid
WHERE a.kayittarih BETWEEN '{dateQuery}' AND
a.servisid {servisIdQuery}
) asd
WHERE tiptutar IS NOT NULL
ORDER BY asd.servisid , asd.isemirno,tip
")
.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 = "";
#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 = "";
#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 b.servisid,
(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,
d.kod,
d.ad,
a.miktar,
br.ad BIRIM,
A.BIRIMFIYAT ALIS_FIYATI,
b.TARIH,
f.faturano,
KURLAR_PKG.CAPRAZKURTARIH (2, 1, b.TARIH) kur,
kurlar_pkg.servisstokfiyatgetir (d.id, 2, TRUNC (SYSDATE)) as EUROLISTEFIYAT,
KURLAR_PKG.STOKFIYATINDGETIR (d.id, 2, 2, 1,0) as EUROINDFIYAT
FROM servisstokhareketdetaylar a
INNER JOIN servisstokhareketler b on B.ID = A.SERVISSTOKHAREKETID and b.parabirimid=1
INNER JOIN servissiparisler c on c.id=b.servissiparisid and c.siparisservisid=1
INNER JOIN servisstoklar d on d.id = A.SERVISSTOKID
INNER JOIN faturalar f on f.id=b.faturaid
INNER JOIN vw_birimler br on br.id=a.birimid and br.dilkod='Turkish'
INNER JOIN servisdeporaflar dr ON d.servisdeporafid = dr.id
INNER JOIN servisstokturler sst ON sst.id = d.servisstokturid
WHERE
a.stokislemtipdeger=1 and
b.servisid {servisIdQuery} AND
b.TARIH between '{dateQuery}'
ORDER BY b.servisid , b.TARIH 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}";
#if DEBUG
selectedServisId = ServisId;
servisIdQuery = $" {selectedServisId}";
#endif
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" in ({ServisIds.joinNumeric(",")}) ";
}
else
{
// servisIdQuery = $" > 1 ";
selectedServisId = ServisId;
servisIdQuery = $" in( {selectedServisId} )";
}
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT
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
FROM(SELECT servisstokturid,
a.id,
a.servisid hservisid,
a.kod,
C.STOKMIKTAR,
r.ad BIRIMAD,
-- kurlar_pkg.servisstokfiyatgetir(a.id, 2, TRUNC('26.03.2018'))
'' as 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
FROM(SELECT DISTINCT servisstokid
FROM sason.servisstokhareketdetaylar) h,
sason.servisstoklar a,
sason.vt_genelstok 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) p,
servisstokturler a
WHERE p.servisstokturid = a.id AND hservisid = {servisIdQuery}
")
.Parameter("startDate", StartDate.startOfDay())
.Parameter("finishDate", FinishDate.endOfDay())
.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 = 94;
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 <ReportData> reportDataSource = new List <ReportData>();
// List<QueryResult> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
QueryResult qr = new QueryResult();
#region query1
//QueryResult qr = new QueryResult();
List <QueryResultStok> queryStok = AppPool.EbaTestConnector.CreateQuery($@"
SELECT
sum(nvl(stok_oes,0)) as stok_oes,
sum(nvl(stok_oeM,0)) as stok_oeM,
sum(nvl(stok_essanayi,0)) as stok_essanayi,
sum(nvl(stok_my,0)) as stok_my,
sum(nvl(stok_yag,0)) as stok_yag,
sum(nvl(stok_yansanayi,0)) as stok_yansanayi,
sum(nvl(stok_yag,0) + nvl(stok_oes,0) +nvl(stok_oeM,0) + nvl(stok_essanayi,0) + nvl(stok_my,0) +nvl(stok_yansanayi,0)) as stok_toplam
FROM (
SELECT
case SERVISSTOKTURID
when 1 then sum(nvl(ORTALAMAMALIYET,0) * STOKMIKTAR)
end as stok_oem,
case SERVISSTOKTURID
when 6 then sum(nvl(ORTALAMAMALIYET,0)*STOKMIKTAR)
end as stok_yag,
case SERVISSTOKTURID
when 7 then sum(nvl(ORTALAMAMALIYET,0) * STOKMIKTAR)
end as stok_oes,
case SERVISSTOKTURID
when 8 then sum(nvl(ORTALAMAMALIYET,0)*STOKMIKTAR)
end as stok_essanayi,
case SERVISSTOKTURID
when 9 then sum(nvl(ORTALAMAMALIYET,0)*STOKMIKTAR)
end as stok_yansanayi,
case SERVISSTOKTURID
when 11 then sum(nvl(ORTALAMAMALIYET,0) *STOKMIKTAR)
end as stok_my
FROM(
SELECT
-- a.kod tur,
-- p.fiyat,
-- p.ID,
-- p.HSERVISID,
p.servisstokturid,
-- P.INDFIYAT EUROINDFIYAT,
-- P.FIYAT EUROLISTEFIYAT,
P.ORTALAMAMALIYET ORTALAMAMALIYET,
p.STOKMIKTAR
FROM (SELECT servisstokturid,
a.id,
-- a.servisid hservisid,
-- a.kod,
Cf.STOKMIKTAR,
-- r.ad BIRIMAD,
-- kurlar_pkg.servisstokfiyatgetir(a.id, 2, TRUNC(SYSDATE)) 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
FROM(SELECT DISTINCT servisstokid
FROM sason.servisstokhareketdetaylar) 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 ay.stokmiktar - NVL (bz.stokmiktar, 0) stokmiktar,
ay.servisstokid,
bz.servisstokid ambarstokid,
bz.stokmiktar ambarstokmiktar,
ay.servisid
FROM ( SELECT SUM (stokmiktar) STOKMIKTAR,
servisid,
servisstokid
FROM(SELECT servisid,
servisstokid,
amiktar * stokislemtipdeger STOKMIKTAR
FROM servisstokhareketdetaylar syc,
servisstokhareketler hyc
WHERE hyc.id = Syc.SERVISSTOKHAREKETID
AND hyc.servisid {servisIdQuery}
AND syc.servisdepoid NOT IN(21, 22))
GROUP BY servisid, servisstokid) ay
FULL OUTER JOIN
(SELECT SUM (axc.miktar) stokmiktar,
axc.servisstokid,
cxc.servisid
FROM servisismislemmalzemeler axc,
servisisemirislemler bxc,
servisisemirler cxc
WHERE cxc.id = bxc.servisisemirid
AND bxc.id = Axc.SERVISISEMIRISLEMID
AND axc.durumid = 1
AND cxc.teknikolaraktamamla = 0
AND cxc.servisid {servisIdQuery}
GROUP BY servisstokid, servisid) bz
ON(ay.servisstokid = bz.servisstokid))
) cf,
--sason.vw_birimler rf,
sason.servisdepolar df,
sason.servisdeporaflar pf
WHERE h.servisstokid = a.id
AND A.ID = Cf.SERVISSTOKID
AND Cf.STOKMIKTAR <> 0
AND a.servisid = cf.servisid
-- AND r.dilid = 0
AND A.SERVISDEPOID = df.id(+)
AND a.servisdeporafid = pf.id(+)
-- AND r.id = a.birimid
) p,
servisstokturler a
WHERE p.servisstokturid = a.id
) asd
group by SERVISSTOKTURID
) asasd
")
.GetDataTable(mr)
.ToModels <QueryResultStok>();
#endregion
ReportData reportData = new ReportData();
reportData.queryrStk = queryStok;
// string YEDEKPARCATOPLAM1 = (reportData.queryr[0].SERVISICITOPLAM + reportData.queryr[0].SERVISDISITOPLAM).toString();
decimal stok_oesx = Convert.ToDecimal((reportData.queryrStk[0].STOK_OES).toString());
decimal stok_oeMx = Convert.ToDecimal((reportData.queryrStk[0].STOK_OEM).toString());
decimal stok_essanayix = Convert.ToDecimal((reportData.queryrStk[0].STOK_ES_SANAYI).toString());
decimal stok_myx = Convert.ToDecimal((reportData.queryrStk[0].STOK_MY).toString());
decimal stok_yansanayix = Convert.ToDecimal((reportData.queryrStk[0].STOK_YANSANAYI).toString());
decimal stok_toplamx = Convert.ToDecimal((reportData.queryrStk[0].STOK_TOPLAM).toString());
#region query2
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT {ServisId} as servisid1,
(Select ISORTAKAD FROM vt_servisler servis where servis.dilkod = 'Turkish' and servis.servisid {servisIdQuery} and rownum < 2) as servisad,
---------- stok
ROUND (NVL(replace('{stok_toplamx}','.',','),'0'),2) as stok_toplam,
ROUND (NVL(replace('{stok_oesx}','.',','),'0'),2) as stok_oes,
ROUND (NVL(replace('{stok_oeMx}','.',','),'0'),2) as stok_oeM,
ROUND (NVL(replace('{stok_essanayix}','.',','),'0'),2) as stok_essanayi,
ROUND (NVL(replace('{stok_myx}','.',','),'0'),2) as stok_my,
ROUND (NVL(replace('{stok_yansanayix}','.',','),'0'),2) as stok_yansanayi,
servisicioem,
servisicioes,
servisiciesdeger,
servisicimyok,
servisicitoplam,
servisiciuygunparca,
servisiciucretliuygunparca,
servisicigaranti,
servisicioem2el,
servisicioes2el,
servisiciesdeger2el,
servisiciyansanayi2el,
servisicimyok2el,
servisicitoplam2el,
servisiciuygunparca2el,
servisiciucretliuygunparca2el,
servisicigaranti2el,
servisdisioem,
servisdisioes,
servisdisiesdeger,
servisdisimyok ,
servisdisitoplam,
servisdisiystoplam,
servisdisiuygunparca,
servisiciyansanayi,
servisdisiyansanayi,
servisiciyansanayitoplam,
servisdisiyansanayitoplam,
servisiciyag,
servisiciyag2el,
servisdisiyag,
yagtoplam,
sum (NVL(servisicitoplam,0) + NVL(servisdisitoplam,0)) as yedekparcatoplami ,
sum (NVL(servisicitoplam,0) + NVL(servisdisitoplam,0)) as ambar,
BAKIMPAKETI ,
uukko
FROM (
SELECT distinct
---- servis içi -- isemirtipi != 6 olanlar burada olacak olmayanlar asagıya eklenecek
sum(NVL(servisicioem,0)) as servisicioem,
sum(NVL(servisicioes,0)) as servisicioes,
sum(NVL(servisiciesdeger,0)) as servisiciesdeger,
sum(NVL(servisicimyok,0)) as servisicimyok,
sum(NVL(servisicioem,0)+NVL(servisicioes,0)+NVL(servisiciesdeger,0)+NVL(servisiciyansanayi,0)+NVL(servisicimyok,0)) as servisicitoplam,
sum(NVL(servisicioem,0)+NVL(servisicioes,0)+NVL(servisiciesdeger,0)) as servisiciuygunparca,
sum (NVL(servisiciucretliuygunparca,0)) as servisiciucretliuygunparca,
sum(NVL(servisicigaranti,0)) as servisicigaranti, ---- bunun içindeki bakım pakeytiini hesapla asagıya ekle
---- servis içi 2 el -- isemirtipi = 6 olanlar burada olacak
sum(NVL(servisicioem2el,0)) as servisicioem2el,
sum(NVL(servisicioes2el,0)) as servisicioes2el,
sum(NVL(servisiciesdeger2el,0)) as servisiciesdeger2el,
sum(NVL(servisicimyok2el,0)) as servisiciyansanayi2el,
sum(NVL(servisicimyok2el,0)) as servisicimyok2el,
sum(NVL(servisicioem2el,0)+NVL(servisicioes2el,0)+NVL(servisiciesdeger2el,0)+NVL(servisiciyansanayi2el,0)+NVL(servisicimyok2el,0)) as servisicitoplam2el,
sum(NVL(servisicioem2el,0)+NVL(servisicioes2el,0)+NVL(servisiciesdeger2el,0)) as servisiciuygunparca2el,
sum(NVL(servisiciucretliuygunparca2el,0)) as servisiciucretliuygunparca2el,
sum(NVL(servisicigaranti2el,0)) as servisicigaranti2el, ---- bunun içindeki bakım pakeytiini hesapla asagıya ekle
---- servis dısı
sum(NVL(servisdisioem,0)) as servisdisioem,
sum(NVL(servisdisioes,0)) as servisdisioes,
sum(NVL(servisdisiesdeger,0)) as servisdisiesdeger,
sum(NVL(servisdisimyok,0)) as servisdisimyok ,
sum(NVL(servisdisioem,0)+NVL(servisdisioes,0)+NVL(servisdisiesdeger,0)+NVL(servisdisiyansanayi,0)+NVL(servisdisimyok,0)) as servisdisitoplam,
sum(NVL(servisdisiyansanayi,0)+NVL(servisdisimyok,0)) as servisdisiystoplam,
sum(NVL(servisdisioem,0)+NVL(servisdisioes,0)+NVL(servisdisiesdeger,0)) as servisdisiuygunparca,
------ yan sanayi
sum(NVL(servisiciyansanayi,0)) as servisiciyansanayi,
sum(NVL(servisdisiyansanayi,0)) as servisdisiyansanayi,
sum(NVL(servisiciyansanayi,0)+NVL(servisicimyok,0)) as servisiciyansanayitoplam,
sum(NVL(servisdisiyansanayi,0)+NVL(servisdisimyok,0)) as servisdisiyansanayitoplam,
-------- yağ
sum(NVL(servisiciyag,0)) as servisiciyag,
sum(NVL(servisiciyag2el,0)) as servisiciyag2el,
sum(NVL(servisdisiyag,0)) as servisdisiyag,
sum(NVL(servisiciyag,0)+NVL(servisdisiyag,0)) as yagtoplam,
-- ,servisid
sum(NVL(BAKIMPAKETI,0)) as BAKIMPAKETI ,
sum(nvl(uukko,0)) as uukko
FROM (
SELECT distinct
SERVISSTOKTURID,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then
case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisicioem,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisicioes,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 6 then sum(TUTAR)
WHEN 6 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisiciyag,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisiciesdeger,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 9 then sum(TUTAR)
WHEN 9 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisiciyansanayi,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 11 then sum(TUTAR)
WHEN 11 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisicimyok,
---------------------
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 1 then sum(TUTAR)
end
end as servisdisioem,
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 7 then sum(TUTAR)
end
end as servisdisioes,
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 6 then sum(TUTAR)
end
end as servisdisiyag,
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 8 then sum(TUTAR)
end
end as servisdisiesdeger,
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 9 then sum(TUTAR)
end
end as servisdisiyansanayi,
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 11 then sum(TUTAR)
end
end as servisdisimyok,
----------------------------------------------------------------------- servis içi uygun parca
case AYRISTIRMATIPAD
when 'HARICI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
--when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
when 'DAHILI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
end as servisiciucretliuygunparca,
----------------------------------------------------------------------- xxxxxxxxxxxxxxxxxxxx uukko
case AYRISTIRMATIPAD
when 'HARICI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
--when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
when 'DAHILI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
end as uukko,
case AYRISTIRMATIPAD
when 'HARICI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then 0
when 7 then 0
when 8 then 0
end
end
when 'DAHILI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then 0
when 7 then 0
when 8 then 0
end
end
else case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 7 then sum(TUTAR)
-- WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 8 then sum(TUTAR)
-- WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
end as servisicigaranti,
----------------------------------------------------------------------- servis içi garanti
case AYRISTIRMATIPAD
when 'BAKIMPAKETI' then
case ISCILIK_PARCA
when 'Malzeme' then
case SERVISSTOKTURID
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
end as BAKIMPAKETI,
-------------------------------**************************
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then
case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisicioem2el,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisicioes2el,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 6 then sum(TUTAR)
WHEN 6 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisiciyag2el,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisiciesdeger2el,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 9 then sum(TUTAR)
WHEN 9 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisiciyansanayi2el,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 11 then sum(TUTAR)
WHEN 11 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisicimyok2el ,
case AYRISTIRMATIPAD
when 'HARICI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
--when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end
when 'DAHILI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end
end as servisiciucretliuygunparca2el,
case AYRISTIRMATIPAD
when 'HARICI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then 0
when 7 then 0
when 8 then 0
end
end
when 'DAHILI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then 0
when 7 then 0
when 8 then 0
end
end
else case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end
end as servisicigaranti2el
from (
select distinct * from sason.rptable_yedekparcadetay t
where
t.servisid {servisIdQuery} and
t.tarih BETWEEN '{dateQuery}'
) rpt
group by SERVISSTOKTURID,BELGETURU,AYRISTIRMATIPAD,ISEMIRTIPI ,ISCILIK_PARCA
) ert
) dsf
group by --- stok_oes, stok_oeM, stok_essanayi, stok_my, stok_yansanayi, stok_toplam,
servisicioem, servisicioes, servisiciesdeger, servisicimyok, servisicitoplam, servisiciuygunparca,
servisiciucretliuygunparca, servisicigaranti, servisicioem2el, servisicioes2el,
servisiciesdeger2el, servisiciyansanayi2el, servisicimyok2el,
servisicitoplam2el, servisiciuygunparca2el, servisiciucretliuygunparca2el,
servisicigaranti2el, servisdisioem, servisdisioes, servisdisiesdeger,
servisdisimyok , servisdisitoplam, servisdisiystoplam, servisdisiuygunparca,
servisiciyansanayi, servisdisiyansanayi, servisiciyansanayitoplam, servisdisiyansanayitoplam,
servisiciyag, servisiciyag2el, servisdisiyag, yagtoplam ,BAKIMPAKETI, uukko
asdasd
")
// .Parameter("stok_oesx", stok_oesx)
// .Parameter("stok_oeMx", stok_oeMx)
// .Parameter("stok_essanayix", stok_essanayix)
// .Parameter("stok_myx", stok_myx)
// .Parameter("stok_yansanayix", stok_yansanayix)
// .Parameter("stok_toplamx", stok_toplamx)
.GetDataTable(mr)
// .ToModels<QueryResult>();
.ToModels();
#endregion
// reportDataSource.add(reportData);
// reportData.AMBARCEVIRIM = (reportData.queryr[0].YEDEKPARCATOPLAM / reportData.queryrStk[0].STOK_TOPLAM) * 12;
CloseCustomAppPool();
// return reportData;
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
string dateQuery = "";
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
SRV.PARTNERCODE,
SRV.ISORTAKAD,
se.kod ENVANTER_AYARLAMA_ACIKLAMA,
SE.IPTALACIKLAMA,
se.TARIH,
(case
when
SE.DURUMID=0 then 'İPTAL EDİLDİ'
when
SE.DURUMID=1 then 'BEKLİYOR'
else
'TAMAMLANDI'
end) as ENVANTER_DURUM,
vts.kod,
CASE WHEN orj.orjinalgkod IS NULL THEN '' ELSE orj.orjinalgkod END orjinalkod,
vts.ad,
sst.kod as stokturkod,
SEM.MIKTAR,
SEM.STOKMIKTAR,
(sem.miktar-sem.stokmiktar) FARK,
sem.maciklama MALZEME_ACIKLAMA,
se.KULLANICIID,
kurlar_pkg.ORTALAMAMALIYET(vts.id) ortalamamaliyet
FROM servisenvantermalzemeler sem, servisenvanterler se, servisstoklar vts, vt_servisler srv, servisstokturler sst,
(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) orj
WHERE
sem.servisenvanterid=se.id and
vts.id=sem.servisstokid and
SRV.SERVISID=se.servisid and
srv.dilkod='Turkish' and
sst.id=VTS.SERVISSTOKTURID AND
vts.kod = orj.kod(+) AND
SRV.SERVISID {servisIdQuery} AND
se.TARIH between '{dateQuery}'
ORDER BY se.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 (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
A.SERVISGARANTINO,
j.partnercode serviskodu,
A.PDFgarantiTARIHi garantitarihi,
A.PDFDMSNO dmsno,
pdftoplam gidentutar,
A.PDFONAYNETGENELTOPLAM gelentutar,
A.claimstatus statu,
j.isortakad yetkiliservis,
a.arizakodu,
a.CASENO,
a.CLAIMNO,
C.AD ayristirmatipi,
E.AD isemirislemtipi,
F.MUSTERIAD,
F.KAYITTARIH isemirbastarihi,
F.TAMAMLANMATARIH isemirbittarih,
f.SASENO,
F.ISEMIRNO,
G.AD musteri,
h.regnumber plaka,
h.ad Turu
FROM ayristirmalar a,vw_ayristirmatipler c, servisisemirislemler d,vw_isemirtipler e,servisisemirler f,servisvarliklar g,
(SELECT a.saseno,
m.regnumber,
m.vehicletype,
b.ad,
dilkod
FROM araclar a,
esaaraclar ea,
vx_vis_vehiclemaster m,vw_aracturler b
WHERE ea.vin = a.saseno
AND ea.id = m.esaaracid
and M.VEHICLETYPE=b.kod) h, vt_servisler j
where
a.AYRISTIRMATIPID=c.id
and c.dilkod='Turkish'
and a.SERVISISEMIRISLEMID=D.ID
and D.ISEMIRTIPID=E.ID
and e.dilkod=c.dilkod
and D.servisisemirid=f.id
and F.SERVISVARLIKID=G.ID
and h.saseno=f.saseno
and h.dilkod=c.dilkod
and a.servisid=j.servisid
and j.dilkod=e.dilkod
and a.tarih between '{dateQuery}'
and a.servisid {servisIdQuery}
and a.claimstatus in ('Z107','Z109','Z999')
ORDER BY a.tarih DESC
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
string servisIdQuery = $" = {ServisId}";
string dateQuery = "";
#if DEBUG
servisIdQuery = $" = {ServisId}";
#endif
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
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()
.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 sorg1.servisid,
sorg2.partnercode,
sorg2.isortakad,
sorg2.sase,
sorg2.isemir,
sorg1.ayr
FROM
(SELECT si.servisid AS servisid,
COUNT(DISTINCT si.isemirno) AS ayr
FROM
servisisemirler si
INNER JOIN servisisemirislemler sisl ON sisl.servisisemirid=si.id
INNER JOIN vt_servisler srv ON srv.servisid=si.servisid
INNER JOIN ayristirmalar ayr ON ayr.isemirno=si.isemirno
WHERE
sisl.isemirtipid=2
AND sisl.isemiruygulamamanedenid IS NULL
AND si.tamamlanmatarih IS NOT NULL
AND si.tutar>0
AND ayr.isemirno=si.isemirno
AND ayr.claimstatus='Z110'
AND srv.dilkod='Turkish'
AND si.kayittarih BETWEEN '{dateQuery}'
AND si.servisid {servisIdQuery}
GROUP BY si.servisid
ORDER BY si.servisid) sorg1
FULL OUTER JOIN
(SELECT si.servisid AS servisid,
srv.partnercode,
srv.isortakad,
COUNT(DISTINCT si.saseno) AS sase,
COUNT(DISTINCT si.isemirno) AS isemir
FROM
servisisemirler si,
servisisemirislemler sisl,
vt_servisler srv
WHERE
sisl.servisisemirid=si.id
AND sisl.isemirtipid=2
AND srv.servisid=si.servisid
AND srv.dilkod='Turkish'
AND si.kayittarih BETWEEN '{dateQuery}'
AND si.servisid {servisIdQuery}
GROUP BY si.servisid, srv.partnercode,srv.isortakad
ORDER BY si.servisid ASC) sorg2
ON sorg1.servisid = sorg2.servisid
")
.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 asd.*,COUNT(asd.Kod) AS Miktar FROM (
SELECT Srv.Partnercode AS ServisKodu,
Srv.Isortakad AS ServisAdi,
Sstok.Kod AS Kod,
Sstok.Ad AS Ad,
--COUNT(Sstok.Kod) AS Miktar,
KURLAR_PKG.SERVISSTOKFIYATGETIR(Sshd.Servisstokid, 2,sysdate) AS EuroListeFiyat,
KURLAR_PKG.STOKFIYATINDGETIR(Sshd.Servisstokid, 2, 2, 1, 0) AS EuroIndFiyat,
NVL(to_char(Sshd.Servissiparisid),Sshd.Isemirno ) Isemirno,
to_char(SSH.TARIH,'DD/MM/YYYY') tarih
FROM Servisstokhareketdetaylar Sshd,
Servisstoklar Sstok,
Vt_Servisler Srv,
Servisstokhareketler ssh
WHERE Sshd.Stokislemtipdeger=-1
AND Sshd.Servisstokid=Sstok.Id
AND Srv.Servisid=Sstok.Servisid
AND Srv.Dilkod='Turkish'
AND Srv.Servisid {servisIdQuery}
AND SSH.ID = SSHD.SERVISSTOKHAREKETID AND ssh.durumid = 1
AND SSH.TARIH between '{dateQuery}'
) asd
GROUP BY ServisKodu,ServisAdi,Kod,Ad, Isemirno,EuroListeFiyat,EuroIndFiyat,tarih
ORDER BY ServisKodu, tarih desc, Miktar 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 *
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} )";
}
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT srv.partnercode AS servis_kodu,
aks.isortakad AS servis_adi,
aks.servisvarlikad AS musteri_adi,
aks.aksiyonad,
aks.saseno,
si.isemirno,
si.kayittarih,
si.tamamlanmatarih
FROM vt_aksiyonisemirler aks,
servisisemirler si,
servisisemirislemler isl,
vt_servisler srv
WHERE aks.dilkod='Turkish'
AND si.id=aks.servisisemirid
AND isl.servisisemirid=si.id
AND isl.isemiruygulamamanedenid=4
AND si.teknikolaraktamamla=1
AND srv.servisid=si.servisid
AND srv.dilkod='Turkish'
AND si.servisid {servisIdQuery}
AND si.tamamlanmatarih BETWEEN '{dateQuery}'
ORDER BY srv.partnercode,si.kayittarih DESC
")
.GetDataTable()
.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 v.partnercode servis_kodu,
v.isortakad servis_adi,
si.saseno,
si.isemirno,
TO_CHAR (si.kayittarih, 'DD.MM.YYYY') ie_kayit_tarihi,
at.ad arac_turu,
si.modelno model,
av.ad arac_varyant,
atip.ad arac_tipi,
TRUNC((si.kayittarih - svr.ilktesciltarihi)/365,0) yas,
svr.ilktesciltarihi
FROM vt_servisler v,
servisisemirler si,
vw_aracturler at,
vw_aractipler atip,
vw_aracvaryantlar av,
araclar a,
servisvarlikruhsatlar svr
WHERE v.dilkod = 'Turkish'
AND v.servisid = si.servisid
AND si.aractipad = at.kod
AND at.dilkod = 'Turkish'
AND atip.dilkod = 'Turkish'
AND a.saseno = si.saseno
AND atip.id = a.aractipid
AND av.id = a.aracvaryantid
AND av.dilkod = 'Turkish'
AND si.saseno=svr.saseno
AND si.servisid=svr.servisid
AND si.kayittarih BETWEEN '{dateQuery}'
AND (si.saseno = NVL ('{SaseNo}', si.saseno))
ORDER BY si.servisid, si.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} )";
}
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
MethodReturn mr = new MethodReturn();
List <ReportData> reportDataSource = new List <ReportData>();
// List<QueryResult> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
QueryResult qr = new QueryResult();
/*
#region query1
* //QueryResult qr = new QueryResult();
* List<QueryResultStok> queryStok = AppPool.EbaTestConnector.CreateQuery($@"
* select
* sum(nvl(stok_oes,0)) as stok_oes,
* sum(nvl(stok_oeM,0)) as stok_oeM,
* sum(nvl(stok_essanayi,0)) as stok_essanayi,
* sum(nvl(stok_my,0)) as stok_my,
*
* sum(nvl(stok_yag,0)) as stok_yag,
* sum(nvl(stok_yansanayi,0)) as stok_yansanayi,
* sum(nvl(stok_yag,0) + nvl(stok_oes,0) +nvl(stok_oeM,0) + nvl(stok_essanayi,0) + nvl(stok_my,0) +nvl(stok_yansanayi,0)) as stok_toplam
* FROM (
*
* SELECT
*
* case SERVISSTOKTURID
* when 1 then sum(nvl(ORTALAMAMALIYET,0) * STOKMIKTAR)
* end as stok_oem,
* case SERVISSTOKTURID
* when 6 then sum(nvl(ORTALAMAMALIYET,0)*STOKMIKTAR)
* end as stok_yag,
* case SERVISSTOKTURID
* when 7 then sum(nvl(ORTALAMAMALIYET,0) * STOKMIKTAR)
* end as stok_oes,
* case SERVISSTOKTURID
* when 8 then sum(nvl(ORTALAMAMALIYET,0)*STOKMIKTAR)
* end as stok_essanayi,
* case SERVISSTOKTURID
* when 9 then sum(nvl(ORTALAMAMALIYET,0)*STOKMIKTAR)
* end as stok_yansanayi,
* case SERVISSTOKTURID
* when 11 then sum(nvl(ORTALAMAMALIYET,0) *STOKMIKTAR)
* end as stok_my
* FROM(
* SELECT
* a.kod tur,p.fiyat,
* p.ID,
* p.HSERVISID,
* p.servisstokturid,
* P.INDFIYAT EUROINDFIYAT,
* P.FIYAT EUROLISTEFIYAT,
* P.ORTALAMAMALIYET ORTALAMAMALIYET,
* p.STOKMIKTAR
*
* FROM(SELECT servisstokturid,
* a.id,
* a.servisid hservisid,
* a.kod,
* C.STOKMIKTAR,
* r.ad BIRIMAD,
* kurlar_pkg.servisstokfiyatgetir(a.id, 2, TRUNC(SYSDATE))
* 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
* FROM(SELECT DISTINCT servisstokid
* FROM sason.servisstokhareketdetaylar) h,
* sason.servisstoklar a,
* sason.vt_genelstok 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) p,
* servisstokturler a
* WHERE p.servisstokturid = a.id AND hservisid {servisIdQuery}
* ) asd
* group by SERVISSTOKTURID
* ) asasd
*
*
* ")
* .GetDataTable(mr)
* .ToModels<QueryResultStok>();
#endregion
*
*
* ReportData reportData = new ReportData();
* reportData.queryrStk = queryStok;
* // string YEDEKPARCATOPLAM1 = (reportData.queryr[0].SERVISICITOPLAM + reportData.queryr[0].SERVISDISITOPLAM).toString();
*
* decimal stok_oesx = Convert.ToDecimal( (reportData.queryrStk[0].STOK_OES).toString() );
* decimal stok_oeMx = Convert.ToDecimal((reportData.queryrStk[0].STOK_OEM).toString() );
* decimal stok_essanayix = Convert.ToDecimal((reportData.queryrStk[0].STOK_ES_SANAYI).toString() );
* decimal stok_myx = Convert.ToDecimal((reportData.queryrStk[0].STOK_MY).toString() ) ;
* decimal stok_yansanayix = Convert.ToDecimal((reportData.queryrStk[0].STOK_YANSANAYI).toString() );
* decimal stok_toplamx = Convert.ToDecimal((reportData.queryrStk[0].STOK_TOPLAM).toString() );
*/
#region query2
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT servisid, (select vtsx.partnercode from vt_servisler vtsx where vtsx.servisid = dsf.servisid and vtsx.dilkod = 'Turkish') as partnercode,
(Select vtsxy.ISORTAKAD FROM vt_servisler vtsxy where vtsxy.dilkod = 'Turkish' and vtsxy.servisid = dsf.servisid ) as servisad,
---------- stok
stok.stok_toplam,
stok.stok_oes,
stok.stok_oeM,
stok.stok_essanayi,
stok.stok_my,
stok.stok_yansanayi,
servisicioem,
servisicioes,
servisiciesdeger,
servisicimyok,
servisicitoplam,
servisiciuygunparca,
servisiciucretliuygunparca,
servisicigaranti,
servisicioem2el,
servisicioes2el,
servisiciesdeger2el,
servisiciyansanayi2el,
servisicimyok2el,
servisicitoplam2el,
servisiciuygunparca2el,
servisiciucretliuygunparca2el,
servisicigaranti2el,
servisdisioem,
servisdisioes,
servisdisiesdeger,
servisdisimyok ,
servisdisitoplam,
servisdisiystoplam,
servisdisiuygunparca,
servisiciyansanayi,
servisdisiyansanayi,
servisiciyansanayitoplam,
servisdisiyansanayitoplam,
servisiciyag,
servisiciyag2el,
servisdisiyag,
yagtoplam,
sum (NVL(servisicitoplam,0) + NVL(servisdisitoplam,0)) as yedekparcatoplami ,
sum (NVL(servisicitoplam,0) + NVL(servisdisitoplam,0)) as ambar,
BAKIMPAKETI ,
uukko
FROM (
SELECT distinct servisid,
---- servis içi -- isemirtipi != 6 olanlar burada olacak olmayanlar asagıya eklenecek
sum(NVL(servisicioem,0)) as servisicioem,
sum(NVL(servisicioes,0)) as servisicioes,
sum(NVL(servisiciesdeger,0)) as servisiciesdeger,
sum(NVL(servisicimyok,0)) as servisicimyok,
sum(NVL(servisicioem,0)+NVL(servisicioes,0)+NVL(servisiciesdeger,0)+NVL(servisiciyansanayi,0)+NVL(servisicimyok,0)) as servisicitoplam,
sum(NVL(servisicioem,0)+NVL(servisicioes,0)+NVL(servisiciesdeger,0)) as servisiciuygunparca,
sum (NVL(servisiciucretliuygunparca,0)) as servisiciucretliuygunparca,
sum(NVL(servisicigaranti,0)) as servisicigaranti, ---- bunun içindeki bakım pakeytiini hesapla asagıya ekle
---- servis içi 2 el -- isemirtipi = 6 olanlar burada olacak
sum(NVL(servisicioem2el,0)) as servisicioem2el,
sum(NVL(servisicioes2el,0)) as servisicioes2el,
sum(NVL(servisiciesdeger2el,0)) as servisiciesdeger2el,
sum(NVL(servisicimyok2el,0)) as servisiciyansanayi2el,
sum(NVL(servisicimyok2el,0)) as servisicimyok2el,
sum(NVL(servisicioem2el,0)+NVL(servisicioes2el,0)+NVL(servisiciesdeger2el,0)+NVL(servisiciyansanayi2el,0)+NVL(servisicimyok2el,0)) as servisicitoplam2el,
sum(NVL(servisicioem2el,0)+NVL(servisicioes2el,0)+NVL(servisiciesdeger2el,0)) as servisiciuygunparca2el,
sum(NVL(servisiciucretliuygunparca2el,0)) as servisiciucretliuygunparca2el,
sum(NVL(servisicigaranti2el,0)) as servisicigaranti2el, ---- bunun içindeki bakım pakeytiini hesapla asagıya ekle
---- servis dısı
sum(NVL(servisdisioem,0)) as servisdisioem,
sum(NVL(servisdisioes,0)) as servisdisioes,
sum(NVL(servisdisiesdeger,0)) as servisdisiesdeger,
sum(NVL(servisdisimyok,0)) as servisdisimyok ,
sum(NVL(servisdisioem,0)+NVL(servisdisioes,0)+NVL(servisdisiesdeger,0)+NVL(servisdisiyansanayi,0)+NVL(servisdisimyok,0)) as servisdisitoplam,
sum(NVL(servisdisiyansanayi,0)+NVL(servisdisimyok,0)) as servisdisiystoplam,
sum(NVL(servisdisioem,0)+NVL(servisdisioes,0)+NVL(servisdisiesdeger,0)) as servisdisiuygunparca,
------ yan sanayi
sum(NVL(servisiciyansanayi,0)) as servisiciyansanayi,
sum(NVL(servisdisiyansanayi,0)) as servisdisiyansanayi,
sum(NVL(servisiciyansanayi,0)+NVL(servisicimyok,0)) as servisiciyansanayitoplam,
sum(NVL(servisdisiyansanayi,0)+NVL(servisdisimyok,0)) as servisdisiyansanayitoplam,
-------- yağ
sum(NVL(servisiciyag,0)) as servisiciyag,
sum(NVL(servisiciyag2el,0)) as servisiciyag2el,
sum(NVL(servisdisiyag,0)) as servisdisiyag,
sum(NVL(servisiciyag,0)+NVL(servisdisiyag,0)) as yagtoplam,
-- ,servisid
sum(NVL(BAKIMPAKETI,0)) as BAKIMPAKETI ,
sum(nvl(uukko,0)) as uukko
FROM (
SELECT distinct servisid,
SERVISSTOKTURID,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then
case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisicioem,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisicioes,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 6 then sum(TUTAR)
WHEN 6 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisiciyag,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisiciesdeger,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 9 then sum(TUTAR)
WHEN 9 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisiciyansanayi,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 11 then sum(TUTAR)
WHEN 11 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end as servisicimyok,
---------------------
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 1 then sum(TUTAR)
end
end as servisdisioem,
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 7 then sum(TUTAR)
end
end as servisdisioes,
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 6 then sum(TUTAR)
end
end as servisdisiyag,
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 8 then sum(TUTAR)
end
end as servisdisiesdeger,
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 9 then sum(TUTAR)
end
end as servisdisiyansanayi,
case BELGETURU
when 'Direk Satış' then
case SERVISSTOKTURID
when 11 then sum(TUTAR)
end
end as servisdisimyok,
----------------------------------------------------------------------- servis içi uygun parca
case AYRISTIRMATIPAD
when 'HARICI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
--when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
when 'DAHILI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
end as servisiciucretliuygunparca,
----------------------------------------------------------------------- xxxxxxxxxxxxxxxxxxxx uukko
case AYRISTIRMATIPAD
when 'HARICI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
--when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
when 'DAHILI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
end as uukko,
case AYRISTIRMATIPAD
when 'HARICI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then 0
when 7 then 0
when 8 then 0
end
end
when 'DAHILI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then 0
when 7 then 0
when 8 then 0
end
end
else case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 7 then sum(TUTAR)
-- WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
-- when 8 then sum(TUTAR)
-- WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
end as servisicigaranti,
----------------------------------------------------------------------- servis içi garanti
case AYRISTIRMATIPAD
when 'BAKIMPAKETI' then
case ISCILIK_PARCA
when 'Malzeme' then
case SERVISSTOKTURID
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN 0 else sum(TUTAR) END
end
end
end as BAKIMPAKETI,
-------------------------------**************************
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then
case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisicioem2el,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisicioes2el,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 6 then sum(TUTAR)
WHEN 6 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisiciyag2el,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisiciesdeger2el,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 9 then sum(TUTAR)
WHEN 9 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisiciyansanayi2el,
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 11 then sum(TUTAR)
WHEN 11 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end as servisicimyok2el ,
case AYRISTIRMATIPAD
when 'HARICI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
--when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end
when 'DAHILI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
--when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end
end as servisiciucretliuygunparca2el,
case AYRISTIRMATIPAD
when 'HARICI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then 0
when 7 then 0
when 8 then 0
end
end
when 'DAHILI' then
case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
when 1 then 0
when 7 then 0
when 8 then 0
end
end
else case BELGETURU
when 'İş Emri' then
case SERVISSTOKTURID
-- when 1 then sum(TUTAR)
WHEN 1 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
-- when 7 then sum(TUTAR)
WHEN 7 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
-- when 8 then sum(TUTAR)
WHEN 8 THEN case ISEMIRTIPI WHEN '2. EL ONARIM' THEN sum(TUTAR) else 0 END
end
end
end as servisicigaranti2el
from (
/* select distinct * from sason.rptable_yedekparcadetay t */
SELECT
t.AYRISTIRMATIPAD,
t.BELGENO,
t.BELGETURU,
t.BRUTTUTAR,
t.HASHSERVISID,
t.INDIRIMORAN,
t.ISCILIK_PARCA,
t.ISEMIRTIPI,
t.KUR kur,
t.MALZEMEAD,
t.MALZEMEKOD,
t.MIKTAR,
t.MUSTERIAD,
t.ORJINALKOD,
t.ORTALAMAMALIYET,
t.SASENO,
t.SERVISAD,
t.SERVISID,
t.SERVISSTOKTURAD,
t.TARIH,
t.TRAFIGECIKISTARIHI,
t.TUTAR,
t.URETICI,
t.VERGINO,
t.SERVISSTOKTURID
FROM sason.rp_yedekparcadetay t
WHERE
t.servisid {servisIdQuery} and
t.tarih BETWEEN '{dateQuery}'
) rpt
group by SERVISSTOKTURID,BELGETURU,AYRISTIRMATIPAD,ISEMIRTIPI ,ISCILIK_PARCA ,servisid
) ert
group by servisid
) dsf
inner join (
select HSERVISID,
sum(nvl(stok_oes,0)) as stok_oes,
sum(nvl(stok_oeM,0)) as stok_oeM,
sum(nvl(stok_essanayi,0)) as stok_essanayi,
sum(nvl(stok_my,0)) as stok_my,
sum(nvl(stok_yag,0)) as stok_yag,
sum(nvl(stok_yansanayi,0)) as stok_yansanayi,
sum(nvl(stok_yag,0) + nvl(stok_oes,0) +nvl(stok_oeM,0) + nvl(stok_essanayi,0) + nvl(stok_my,0) +nvl(stok_yansanayi,0)) as stok_toplam
FROM (
SELECT asd.HSERVISID,
case asd.SERVISSTOKTURID
when 1 then sum(nvl(asd.ORTALAMAMALIYET,0) * asd.STOKMIKTAR)
end as stok_oem,
case asd.SERVISSTOKTURID
when 6 then sum(nvl(asd.ORTALAMAMALIYET,0)*asd.STOKMIKTAR)
end as stok_yag,
case asd.SERVISSTOKTURID
when 7 then sum(nvl(asd.ORTALAMAMALIYET,0) * asd.STOKMIKTAR)
end as stok_oes,
case asd.SERVISSTOKTURID
when 8 then sum(nvl(asd.ORTALAMAMALIYET,0)*asd.STOKMIKTAR)
end as stok_essanayi,
case asd.SERVISSTOKTURID
when 9 then sum(nvl(asd.ORTALAMAMALIYET,0)*asd.STOKMIKTAR)
end as stok_yansanayi,
case asd.SERVISSTOKTURID
when 11 then sum(nvl(asd.ORTALAMAMALIYET,0) *asd.STOKMIKTAR)
end as stok_my
FROM(
SELECT
a.kod tur,p.fiyat,
p.ID,
p.HSERVISID,
p.servisstokturid,
P.INDFIYAT EUROINDFIYAT,
P.FIYAT EUROLISTEFIYAT,
P.ORTALAMAMALIYET ORTALAMAMALIYET,
p.STOKMIKTAR
FROM(SELECT servisstokturid,
a.id,
a.servisid hservisid,
a.kod,
C.STOKMIKTAR,
r.ad BIRIMAD,
kurlar_pkg.servisstokfiyatgetir(a.id, 2, TRUNC(SYSDATE))
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
FROM(SELECT DISTINCT servisstokid
FROM sason.servisstokhareketdetaylar) h,
sason.servisstoklar a,
sason.vt_genelstok 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) p,
servisstokturler a
WHERE p.servisstokturid = a.id AND p.HSERVISID {servisIdQuery}
) asd
group by asd.SERVISSTOKTURID ,asd.HSERVISID
) asasd
group by asasd.HSERVISID
) stok on servisid = STOK.HSERVISID
group by stok_oes, stok_oeM, stok_essanayi, stok_my, stok_yansanayi, stok_toplam,
servisicioem, servisicioes, servisiciesdeger, servisicimyok, servisicitoplam, servisiciuygunparca,
servisiciucretliuygunparca, servisicigaranti, servisicioem2el, servisicioes2el,
servisiciesdeger2el, servisiciyansanayi2el, servisicimyok2el,
servisicitoplam2el, servisiciuygunparca2el, servisiciucretliuygunparca2el,
servisicigaranti2el, servisdisioem, servisdisioes, servisdisiesdeger,
servisdisimyok , servisdisitoplam, servisdisiystoplam, servisdisiuygunparca,
servisiciyansanayi, servisdisiyansanayi, servisiciyansanayitoplam, servisdisiyansanayitoplam,
servisiciyag, servisiciyag2el, servisdisiyag, yagtoplam ,BAKIMPAKETI, uukko, servisid
")
.GetDataTable(mr)
.ToModels();
#endregion
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
string servisIdQuery1 = $" = {ServisId} ";
#region tarih
string dateQuery = "";
// string addSQLSelect = " ARAC_TURU, ";
string AracTurIdQuery = ""; // $" = {selectedAracTurId}";
decimal selectedServisId = 94; // ServisIds.first().toString("0").cto<decimal>();
#endregion
#region servisidz
// decimal selectedServisId = ServisId;
string servisIdQuery = ""; // $" = {selectedServisId}";
servisIdQuery = $" AND E_SSI31_FORM.SERVISID IN ( {ServisId} )";
if (ServisIds.isNotEmpty())
{
servisIdQuery = $" AND E_SSI31_FORM.SERVISID IN ({ServisIds.joinNumeric(",")}) ";
}
else
{
// selectedServisId = ServisId;
// servisIdQuery = $" AND E_SSI31_FORM.SERVISID IN ( {selectedServisId} )";
}
#endregion
#region aracturz
// decimal selectedAracTurId = AracTurIds.first().toString("0").cto<decimal>();
// decimal selectedAracTurId = 0;
if (AracTurIds.isNotEmpty())
{
// if (AracTurIds.Count > 0 )
AracTurIdQuery = $" AND ATT.ID in ({AracTurIds.joinNumeric(",")}) ";
}
else
{
AracTurIdQuery = "";
}
#endregion
string addSQLGROUP = " ARAC_TURU, ";
#region aractipz
// decimal selectedAracTipId = AracTipIds.first().toString("0").cto<decimal>();
string AracTipIdQuery = ""; // $" = {selectedAracTipId}";
string addSQLAracTur = "";
// if (AracTipIds.Count > 0 )
if (AracTipIds.isNotEmpty())
{
addSQLAracTur = $" CONCAT(CONCAT(E_SSI31_FORM.VEHICLETYPE,' - '), ATP.KOD) AS ARAC_TURU, ";
AracTipIdQuery = $" AND ATP.ID in ({AracTipIds.joinNumeric(",")}) ";
addSQLGROUP = addSQLGROUP + $" aractipi, ";
}
else
{
AracTipIdQuery = "";
addSQLAracTur = " E_SSI31_FORM.VEHICLETYPE AS ARAC_TURU, ";
}
#endregion
#region arizakod
string arizaKodQuery = "";
if (ArizaKod.Length == 7)
{
arizaKodQuery =
$" AND ( SUBSTR(e_ssi31_form.islemarizakod, 1, 7) ) = '{ArizaKod}' ";
}
else
{
arizaKodQuery = "";
}
#endregion
StartDate = StartDate.startOfDay();
FinishDate = FinishDate.endOfDay();
dateQuery = "" + StartDate.ToString("dd/MM/yyyy") + "' AND '" + FinishDate.ToString("dd/MM/yyyy") + "";
MethodReturn mr = new MethodReturn();
#region eskisql
/* List<object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
*
* SELECT OSUSERS.FIRSTNAME || ' ' || OSUSERS.LASTNAME AS AkisiBaslatan,
* LIVEFLOWS.RDATE AS TALEP_TARIHI,
* LIVEFLOWS.FDATE AS ONAY_TARIHI,
* FLOWSTATUSES.DESCRIPTION AS STATU,
* e_ssi31_form.FIRMAAD AS SERVIS_ADI,
* e_ssi31_form.garantituru,
* e_ssi31_form.garantistatu,
* e_ssi31_form.garantirednedenid_text AS GARANTI_RED_NEDENI,
* (SELECT esagarantino
* FROM ayristirmalar g
* WHERE g.id = e_ssi31_form.ayristirmaid) AS ESAGARANTINO,
* (SELECT t.ad
* FROM vw_ayristirmatipler t, ayristirmalar a
* WHERE a.ayristirmatipid = t.id
* AND a.id = e_ssi31_form.ayristirmaid
* AND dilkod = 'Turkish') AS AYRISTIRMA_TIPI,
* e_ssi31_form.servisgarantino,
* E_SSI31_FORM.VEHICLETYPE AS ARAC_TIPI,
* e_ssi31_form.saseno,
* e_ssi31_form.isemirno,
* e_ssi31_form.islemarizakod AS ARIZA_KODU,
* e_ssi31_form.islemtipad AS ISLEM_TIPI,
* e_ssi31_form.firstregdate AS TRAFIGE_CIKIS_TARIHI,
* e_ssi31_form.exworksdate AS FABRIKA_CIKIS_TARIHI,
* e_ssi31_form.modelnum AS ARAC_MODEL,
* e_ssi31_form.engineserialnum AS MOTOR_NO,
* e_ssi31_form.engineunittype AS MOTOR_TIPI,
* e_ssi31_form.pkm AS ARAC_KM
* FROM FLOWDOCUMENTS
* INNER JOIN LIVEFLOWS ON FLOWDOCUMENTS.PROCESSID = LIVEFLOWS.ID
* INNER JOIN e_ssi31_form ON FLOWDOCUMENTS.FILEPROFILEID = e_ssi31_form.ID
* INNER JOIN FLOWSTATUSES ON LIVEFLOWS.STATUS = FLOWSTATUSES.STATUS
* AND LIVEFLOWS.PROCESS = FLOWSTATUSES.PROCESS
* AND LIVEFLOWS.FLOWVERSION = FLOWSTATUSES.VERSION
* INNER JOIN OSUSERS ON LIVEFLOWS.USERID = OSUSERS.ID
* WHERE LIVEFLOWS.DELETED = 0
* AND(SELECT COUNT(*)
* FROM FLOWREQUESTS
* WHERE PROCESSID = LIVEFLOWS.ID) > 1
* AND LIVEFLOWS.DELETED = 0
* AND LIVEFLOWS.STATUS > 1
* AND LIVEFLOWS.RDATE BETWEEN '{dateQuery}'
* ORDER BY LIVEFLOWS.RDATE DESC
*
* ")
*/
#endregion
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT
SERVIS_ADI,
AYRISTIRMA_TIPI,
ARAC_TURU,
arizakod,
count(arizakod) as adet,
(SELECT vtsx.partnercode FROM vt_servisler vtsx where vtsx.servisid = asd.SERVISID and vtsx.dilkod = 'Turkish' and rownum <2) as partnercode
FROM (
SELECT
E_SSI31_FORM.SERVISID,
LIVEFLOWS.RDATE AS TALEP_TARIHI,
FLOWSTATUSES.DESCRIPTION AS STATU,
e_ssi31_form.FIRMAAD AS SERVIS_ADI,
ATP.ID aractipi,
(SELECT t.ad
FROM vw_ayristirmatipler t, ayristirmalar a
WHERE a.ayristirmatipid = t.id
AND a.id = e_ssi31_form.ayristirmaid
AND dilkod = 'Turkish') AS AYRISTIRMA_TIPI,
/* E_SSI31_FORM.VEHICLETYPE AS ARAC_TURU, */
{addSQLAracTur}
e_ssi31_form.islemarizakod AS ARIZA_KODU,
e_ssi31_form.modelnum AS ARAC_MODEL,
SUBSTR(e_ssi31_form.islemarizakod, 1, 7) arizakod
FROM FLOWDOCUMENTS
INNER JOIN LIVEFLOWS ON FLOWDOCUMENTS.PROCESSID = LIVEFLOWS.ID
INNER JOIN e_ssi31_form ON FLOWDOCUMENTS.FILEPROFILEID = e_ssi31_form.ID
INNER JOIN FLOWSTATUSES ON LIVEFLOWS.STATUS = FLOWSTATUSES.STATUS
AND LIVEFLOWS.PROCESS = FLOWSTATUSES.PROCESS
AND LIVEFLOWS.FLOWVERSION = FLOWSTATUSES.VERSION
INNER JOIN OSUSERS ON LIVEFLOWS.USERID = OSUSERS.ID
INNER JOIN SASON.ARACLAR ARC ON ARC.SASENO = E_SSI31_FORM.SASENO AND ARC.DURUMID = 1
LEFT JOIN SASON.ARACTURLER att ON ATT.ID = ARC.ARACTURID AND ATT.DURUMID = 1
LEFT JOIN SASON.ARACTIPLER atp ON ATP.ID = ARC.ARACTIPID AND ATP.DURUMID = 1
WHERE LIVEFLOWS.DELETED = 0
AND(SELECT COUNT(*)
FROM FLOWREQUESTS
WHERE PROCESSID = LIVEFLOWS.ID) > 1
AND LIVEFLOWS.DELETED = 0
AND LIVEFLOWS.STATUS > 1
AND LIVEFLOWS.RDATE BETWEEN '{dateQuery}'
{AracTurIdQuery}
{AracTipIdQuery}
{arizaKodQuery}
{servisIdQuery}
) asd
GROUP BY
SERVIS_ADI,
AYRISTIRMA_TIPI,
{addSQLGROUP}
arizakod,
SERVISID
ORDER BY adet 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
(SELECT vtsx.partnercode from vt_servisler vtsx where vtsx.servisid = d.servisid and vtsx.dilkod = 'Turkish') as partnercode,
(SELECT vtsxy.ISORTAKAD FROM vt_servisler vtsxy where vtsxy.dilkod = 'Turkish' and vtsxy.servisid = d.servisid) as servisad,
f.netkdvtoplam,
f.nettutar,
f.netkdvtoplam+f.nettutar toplam,
D.FATURANO,
D.SERVISID,
D.SERVISISEMIRID,
D.SERVISISORTAKID,
F.ACIKLAMA,
F.ADRES,
F.BRUTTOPLAM,
F.CARIUNVAN,
F.IL,
F.ILCE,
F.VERGIDAIRESI,
F.VNO,
F.ILGILIKISI,
F.ILGILIKISITELNO,
F.IRSALIYENO,
to_char(F.IRSALIYETARIHI,'dd/mm/yyyy') as IRSALIYETARIHI,
F.ISLEMTARIHI,
(SELECT zx.ISEMIRNO FROM servisisemirler zx where zx.id = d.SERVISISEMIRID) as ISEMIRNO
FROM sason.servisdishizmetalimlar d, sason.faturalar f
WHERE
d.faturaid = f.id AND
F.ISLEMTARIHI BETWEEN '{dateQuery}' AND
d.servisid {servisIdQuery}
ORDER BY servisad ,F.islemtarihi 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") + "";
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT
(SELECT vtsx.partnercode from vt_servisler vtsx where vtsx.servisid = vv.servisid and vtsx.dilkod = 'Turkish') as partnercode,
(SELECT vtsxy.ISORTAKAD FROM vt_servisler vtsxy where vtsxy.dilkod = 'Turkish' and vtsxy.servisid = vv.servisid) as servisad,
vv.servisid ,
tarihicin.tar ttarihh,
nvl( (
SELECT count(z.servisid) from servisisemirler z where
to_date(z.kayittarih,'dd/mm/yyyy') < to_date(tarihicin.tar,'dd/mm/yyyy') AND
z.servisid = vv.servisid
and z.teknikolaraktamamla = 0 and z.tamamlanmatarih is null
),0) ogkadar_kapatilmayan_emrler,
nvl((
SELECT count(z.servisid) from servisisemirler z where
to_date(z.kayittarih,'dd/mm/yyyy') = to_date(tarihicin.tar,'dd/mm/yyyy') AND
z.servisid = vv.servisid
group by to_date(z.kayittarih,'dd/mm/yyyy')),0) ogun_acilan_emirler,
nvl((
SELECT count(z.servisid) from servisisemirler z where
to_date(z.tamamlanmatarih,'dd/mm/yyyy') <> to_date(tarihicin.tar,'dd/mm/yyyy') AND
to_date(z.kayittarih,'dd/mm/yyyy') = to_date(tarihicin.tar,'dd/mm/yyyy') AND
z.servisid = vv.servisid
group by to_date(z.kayittarih,'dd/mm/yyyy')),0) ogun_kapatilmayan_emirler ,
nvl((
SELECT count(z.servisid) from servisisemirler z where
to_date(z.tamamlanmatarih,'dd/mm/yyyy') = to_date(tarihicin.tar,'dd/mm/yyyy') AND
to_date(z.kayittarih,'dd/mm/yyyy') = to_date(tarihicin.tar,'dd/mm/yyyy') AND
z.servisid = vv.servisid
),0) ogun_acilankapanan_emirler,
nvl((
SELECT count(z.servisid) from servisisemirler z where
to_date(z.tamamlanmatarih,'dd/mm/yyyy') = to_date(tarihicin.tar,'dd/mm/yyyy') AND
z.servisid = vv.servisid
),0) ogun_kapatilan_emir ,
nvl((
SELECT count(z.servisid) from servisisemirler z where
to_date(z.tamamlanmatarih,'dd/mm/yyyy') <= to_date(tarihicin.tar,'dd/mm/yyyy') AND
z.servisid = vv.servisid
),0) ogune_kadarkapatilan_emir
from vt_servisler vv
left join (
SELECT distinct to_date(x.kayittarih,'dd/mm/yyyy') tar
from servisisemirler x WHERE
x.kayittarih between '{dateQuery}'
) tarihicin on 1 = 1
where
vv.dilkod = 'Turkish' AND
vv.servisid not in (1,134,136) AND
vv.durumid = 1
and vv.servisid {servisIdQuery}
order by vv.servisid , tarihicin.tar desc
")
.GetDataTable()
.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 (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
(SELECT vtsx.partnercode FROM vt_servisler vtsx WHERE vtsx.servisid = a.servisid AND vtsx.dilkod = 'Turkish') AS partnercode,
(SELECT vtsxy.ISORTAKAD FROM vt_servisler vtsxy WHERE vtsxy.dilkod = 'Turkish' AND vtsxy.servisid = a.servisid) AS servisad,
DECODE (a.turid,
1, 'Malzeme',
2, 'İşçilik',
3, 'Diğer Kalem',
4, 'Dış Hizmet') AS Turu,
b.dahili,
a.harici,
c.garanti,
d.uzatilmisgaranti,
e.aksiyond,
f.traksiyon,
g.yedekparcagarantisi,
h.kanf,
i.kvsa,
j.reku,
k.erwc,
l.hediyepaketi,
m.bakimpaketi,
n.aksiyon,
o.comfort,
p.comfortplus,
r.comfortsuper,
a.servisid
FROM (SELECT
ROUND (SUM (d.atutar), 0) harici,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.durumid = 1 AND
a.servisid {servisIdQuery} AND
a.ayristirmatipid = 1 AND
a.tarih BETWEEN '{dateQuery}'
GROUP BY turid, a.servisid) a,
(SELECT
ROUND (SUM(d.atutar), 0) dahili,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.durumid = 1 AND
a.servisid {servisIdQuery} AND
a.ayristirmatipid = 2 AND
a.pdfgarantitarihi BETWEEN '{dateQuery}'
GROUP BY turid, a.servisid) b,
(SELECT
ROUND (SUM(d.pdftoplam), 0) garanti,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 3 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) c,
(SELECT
ROUND (SUM(d.pdftoplam), 0) uzatilmisgaranti,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 4 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) d,
(SELECT
ROUND (SUM(d.pdftoplam), 0) aksiyond,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 5 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) e,
(SELECT
ROUND (SUM(d.pdftoplam), 0) traksiyon,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 6 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) f,
(SELECT
ROUND (SUM(d.pdftoplam), 0) yedekparcagarantisi,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 7 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) g,
(SELECT
ROUND (SUM(d.pdftoplam), 0) kanf,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 8 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) h,
(SELECT
ROUND (SUM(d.pdftoplam), 0) kvsa,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 9 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) i,
(SELECT
ROUND (SUM(d.pdftoplam), 0) reku,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 10 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) j,
(SELECT
ROUND (SUM(d.pdftoplam), 0) erwc,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 11 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) k,
(SELECT
ROUND (SUM(d.pdftoplam), 0) hediyepaketi,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 12 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) l,
(SELECT
ROUND (SUM(d.pdftoplam), 0) bakimpaketi,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 13 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) m,
(SELECT
ROUND (SUM(d.pdftoplam), 0) aksiyon,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 14 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) n,
(SELECT
ROUND (SUM(d.pdftoplam), 0) comfort,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 15 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) o,
(SELECT
ROUND (SUM(d.pdftoplam), 0) comfortplus,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 16 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) p,
(SELECT
ROUND (SUM(d.pdftoplam), 0) comfortsuper,
turid,
a.servisid
FROM ayristirmalar a, ayristirmadetaylar d
WHERE a.id = d.ayristirmaid AND
a.pdfgarantitarihi BETWEEN '{dateQuery}' and
a.servisid {servisIdQuery} AND
a.durumid = 1 AND
a.ayristirmatipid = 17 AND
icmalid IS NOT NULL
GROUP BY turid, a.servisid) r
WHERE a.turid = b.turid(+) AND
a.turid = c.turid(+) AND
a.turid = d.turid(+) AND
a.turid = e.turid(+) AND
a.turid = f.turid(+) AND
a.turid = g.turid(+) AND
a.turid = h.turid(+) AND
a.turid = i.turid(+) AND
a.turid = j.turid(+) AND
a.turid = k.turid(+) AND
a.turid = l.turid(+) AND
a.turid = m.turid(+) AND
a.turid = n.turid(+) AND
a.turid = o.turid(+) AND
a.turid = p.turid(+) AND
a.turid = r.turid(+) AND
a.servisid = b.servisid(+) AND
a.servisid = c.servisid(+) AND
a.servisid = d.servisid(+) AND
a.servisid = e.servisid(+) AND
a.servisid = f.servisid(+) AND
a.servisid = g.servisid(+) AND
a.servisid = h.servisid(+) AND
a.servisid = i.servisid(+) AND
a.servisid = j.servisid(+) AND
a.servisid = k.servisid(+) AND
a.servisid = l.servisid(+) AND
a.servisid = m.servisid(+) AND
a.servisid = n.servisid(+) AND
a.servisid = o.servisid(+) AND
a.servisid = p.servisid(+) AND
a.servisid = r.servisid(+)
ORDER BY servisad 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 (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
SERVISID ,
SIPARISSERVISID HASHSERVISID,
(SELECT isortakad
FROM vt_servisler
WHERE servisid = siparisservisid AND dilkod = 'Turkish') servisad,
to_char(TARIH,'dd/mm/yyyy') TARIH ,
BELGENO ,
BELGETURU ,
ISEMIRTIPI ,
ISEMIRTIPID ,
VERGINO ,
MUSTERIAD ,
to_char(FATURATARIHI,'dd/mm/yyyy') FATURATARIHI ,
CLAIMSTATUS ,
MALZEMEKOD ,
ORJINALKOD ,
URETICI ,
SERVISSTOKTURAD ,
ISCILIK_PARCA ,
MALZEMEAD ,
MIKTAR ,
BRUTTUTAR ,
case BELGETURU when 'Direk Satış' THEN MIKTAR * TUTAR
ELSE TUTAR END
TUTAR,
SASENO ,
OZELSATISSASENOLAR ,
to_char(TRAFIGECIKISTARIHI,'dd/mm/yyyy') TRAFIGECIKISTARIHI,
KURLAR_PKG.ORTALAMAMALIYET (ORTALAMAMALIYET) ortalamamaliyet,
AYRISTIRMATIPAD ,
FIYAT2 ,
to_char(KUR) KUR,
SERVISSTOKID ,
SERVISSTOKTURID ,
EUROINDFIYAT ,
kurlar_pkg.servisstokfiyatgetir (EUROLISTEFIYAT, 2, TRUNC (SYSDATE)) EUROLISTEFIYAT
FROM sason.ypdata
WHERE
YEDEKPARCARAPORTARIHI between '{dateQuery}' and
SIPARISSERVISID {servisIdQuery}
")
.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") + "";
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT SUM(AIES) AS AIES , sum(KIES) AS KIES , servisid, partnercode , servisad , KAYITTARIH
FROM (
select max(acikadet) as AIES,
max(kapanan_adet) as KIES,
servisid,
(select vtsx.partnercode from vt_servisler vtsx where vtsx.servisid = dsf.servisid and vtsx.dilkod = 'Turkish') as partnercode,
(Select vtsxy.ISORTAKAD FROM vt_servisler vtsxy where vtsxy.dilkod = 'Turkish' and vtsxy.servisid = dsf.servisid) as servisad,
to_char(KAYITTARIH,'dd.mm.yyyy') as KAYITTARIH
from
(SELECT count(id) acikadet,null kapanan_adet ,servisid, KAYITTARIH
FROM servisisemirler
where
KAYITTARIH BETWEEN '{dateQuery}' AND
servisid {servisIdQuery}
group by servisid, KAYITTARIH
union all
SELECT null acikadet ,count(id)kapanan_adet,servisid, TAMAMLANMATARIH
FROM servisisemirler
where
TAMAMLANMATARIH BETWEEN '{dateQuery}'
and servisid {servisIdQuery}
group by servisid ,TAMAMLANMATARIH) dsf
group by servisid ,KAYITTARIH
)
GROUP BY servisid, partnercode, servisad, KAYITTARIH
order by servisid,to_date(KAYITTARIH) desc
")
.GetDataTable()
.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 (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") + "";
#region eskisql
/*
* List<object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
* SELECT DISTINCT
* AYRISTIRMATIPAD ,
* BELGENO,
* BELGETURU,
* BRUTTUTAR,
* HASHSERVISID ,
* INDIRIMORAN,
* ISCILIK_PARCA,
* ISEMIRTIPI,
* KUR,
* MALZEMEAD,
* MALZEMEKOD,
* MIKTAR,
* MUSTERIAD,
* ORJINALKOD,
* ORTALAMAMALIYET,
* SASENO,
* SERVISAD,
* SERVISID,
* SERVISSTOKTURAD,
* TARIH,
* to_char(TRAFIGECIKISTARIHI,'dd/mm/yyyy') as TRAFIGECIKISTARIHI,
* TUTAR,
* URETICI,
* VERGINO,
* SERVISSTOKTURID,
* {{startDate}} as bastar ,
* {{finishDate}} as bittar
* FROM sason.rptable_yedekparcadetay
* WHERE tarih BETWEEN {{startDate}} and {{finishDate}} AND
* SERVISID {servisIdQuery}
* order by SERVISAD , TARIH, belgeno,malzemekod, musteriad
*
* ")
* .Parameter("startDate", StartDate.startOfDay())
* .Parameter("finishDate", FinishDate.endOfDay())
* .GetDataTable(mr)
* .ToModels();
*
*/
#endregion
MethodReturn mr = new MethodReturn();
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT
SERVISID ,
SIPARISSERVISID HASHSERVISID,
(SELECT isortakad
FROM vt_servisler
WHERE servisid = siparisservisid AND dilkod = 'Turkish') servisad,
TARIH ,
BELGENO ,
BELGETURU ,
ISEMIRTIPI ,
ISEMIRTIPID ,
VERGINO ,
MUSTERIAD ,
FATURATARIHI ,
CLAIMSTATUS ,
MALZEMEKOD ,
ORJINALKOD ,
URETICI ,
SERVISSTOKTURAD ,
ISCILIK_PARCA ,
MALZEMEAD ,
MIKTAR ,
BRUTTUTAR ,
TUTAR ,
SASENO ,
OZELSATISSASENOLAR ,
to_char(TRAFIGECIKISTARIHI,'dd/mm/yyyy') TRAFIGECIKISTARIHI,
KURLAR_PKG.ORTALAMAMALIYET (ORTALAMAMALIYET) ortalamamaliyet,
AYRISTIRMATIPAD ,
FIYAT2 ,
to_char(KUR) KUR,
SERVISSTOKID ,
SERVISSTOKTURID ,
EUROINDFIYAT ,
kurlar_pkg.servisstokfiyatgetir (EUROLISTEFIYAT, 2, TRUNC (SYSDATE)) EUROLISTEFIYAT
FROM sason.ypdata
WHERE
YEDEKPARCARAPORTARIHI between '{dateQuery}' and
SIPARISSERVISID {servisIdQuery}
")
.GetDataTable(mr)
.ToModels();
CloseCustomAppPool();
return(queryResults);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
string dateQuery = "";
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,
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 = {ServisId}
and i.id in (select ixx.id from servisisemirler ixx where ixx.servisid ={ServisId} and ixx.KAYITTARIH between '{dateQuery}' AND (ixx.saseno = NVL ('{SaseNo}', ixx.saseno)) )
ORDER BY i.id desc
")
// .Parameter("StartDate", StartDate.Date)
// .Parameter("FinishDate", FinishDate.endOfDay())
// .Parameter("ServisId", ServisId)
// .Parameter("SaseNo", SaseNo.isNotNullOrWhiteSpace() ? null : SaseNo)
.GetDataTable(refMr)
.ToModels();
// reportDataSource = dtb.ToModels(refMr);
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 <ReportData> reportDataSource = new List <ReportData>();
QueryResult qr = new QueryResult();
#region query2
List <object> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
SELECT vts.partnercode, vts.ISORTAKAD ,
stok.stok_toplam,
stok.stok_oes,
stok.stok_oeM,
stok.stok_essanayi,
stok.stok_my,
stok.stok_yansanayi,
dfdfdf.* FROM (
SELECT servisid,
sum(servisicioem) servisicioem ,
sum(servisicioes) servisicioes ,
sum(servisiciesdeger) servisiciesdeger,
sum(servisicimyok) servisicimyok,
sum(servisicitoplam) servisicitoplam,
sum(servisiciuygunparca) servisiciuygunparca,
sum(servisiciucretliuygunparca) servisiciucretliuygunparca ,
sum(servisicigaranti) servisicigaranti,
sum(servisicioem2el) servisicioem2el,
sum(servisicioes2el) servisicioes2el,
sum(servisiciesdeger2el) servisiciesdeger2el ,
sum(servisiciyansanayi2el) servisiciyansanayi2el,
sum(servisicimyok2el) servisicimyok2el,
sum(servisicitoplam2el) servisicitoplam2el,
sum(servisiciuygunparca2el) servisiciuygunparca2el,
sum(servisiciucretliuygunparca2el) servisiciucretliuygunparca2el ,
sum(servisicigaranti2el) servisicigaranti2el,
sum(servisdisioem) servisdisioem,
sum(servisdisioes) servisdisioes,
sum(servisdisiesdeger) servisdisiesdeger,
sum(servisdisimyok) servisdisimyok ,
sum(servisdisitoplam) servisdisitoplam,
sum(servisdisiystoplam) servisdisiystoplam,
sum(servisdisiuygunparca) servisdisiuygunparca ,
sum(servisiciyansanayi) servisiciyansanayi,
sum(servisdisiyansanayi) servisdisiyansanayi,
sum(servisiciyansanayitoplam) servisiciyansanayitoplam,
sum(servisdisiyansanayitoplam) servisdisiyansanayitoplam,
sum(servisiciyag) servisiciyag,
sum(servisiciyag2el) servisiciyag2el,
sum(servisdisiyag) servisdisiyag,
sum(yagtoplam) yagtoplam,
sum(yedekparcatoplami) yedekparcatoplami ,
sum(ambar) ambar,
sum(BAKIMPAKETI) BAKIMPAKETI ,
sum(uukko) uukko
FROM sason.ypfaaliyet dsf
WHERE
dsf.servisid {servisIdQuery} and
dsf.YEDEKPARCAFALIYETRAPORTARIHI BETWEEN '{dateQuery}'
group by servisid
) dfdfdf
inner join (
select HSERVISID,
sum(nvl(stok_oes,0)) as stok_oes,
sum(nvl(stok_oeM,0)) as stok_oeM,
sum(nvl(stok_essanayi,0)) as stok_essanayi,
sum(nvl(stok_my,0)) as stok_my,
sum(nvl(stok_yag,0)) as stok_yag,
sum(nvl(stok_yansanayi,0)) as stok_yansanayi,
sum( /* nvl(stok_yag,0) + */ nvl(stok_oes,0) +nvl(stok_oeM,0) + nvl(stok_essanayi,0) + nvl(stok_my,0) +nvl(stok_yansanayi,0)) as stok_toplam
FROM (
SELECT asd.HSERVISID,
case asd.SERVISSTOKTURID
when 1 then sum(nvl(asd.ORTALAMAMALIYET,0) * asd.STOKMIKTAR)
end as stok_oem,
case asd.SERVISSTOKTURID
when 6 then sum(nvl(asd.ORTALAMAMALIYET,0)*asd.STOKMIKTAR)
end as stok_yag,
case asd.SERVISSTOKTURID
when 7 then sum(nvl(asd.ORTALAMAMALIYET,0) * asd.STOKMIKTAR)
end as stok_oes,
case asd.SERVISSTOKTURID
when 8 then sum(nvl(asd.ORTALAMAMALIYET,0)*asd.STOKMIKTAR)
end as stok_essanayi,
case asd.SERVISSTOKTURID
when 9 then sum(nvl(asd.ORTALAMAMALIYET,0)*asd.STOKMIKTAR)
end as stok_yansanayi,
case asd.SERVISSTOKTURID
when 11 then sum(nvl(asd.ORTALAMAMALIYET,0) *asd.STOKMIKTAR)
end as stok_my
FROM(
SELECT
a.kod tur,
p.fiyat,
p.ID,
p.HSERVISID,
p.servisstokturid,
P.INDFIYAT EUROINDFIYAT,
P.FIYAT EUROLISTEFIYAT,
P.ORTALAMAMALIYET ORTALAMAMALIYET,
p.STOKMIKTAR
FROM(SELECT servisstokturid,
a.id,
a.servisid hservisid,
a.kod,
C.STOKMIKTAR,
kurlar_pkg.servisstokfiyatgetir(a.id, 2, TRUNC(SYSDATE))
fiyat,
KURLAR_PKG.STOKFIYATINDGETIR(a.id,
2,
2,
1,
0)
INDFIYAT,
kurlar_pkg.ORTALAMAMALIYET(a.id) ORTALAMAMALIYET
FROM(SELECT DISTINCT servisstokid
FROM sason.servisstokhareketdetaylar
) h,
sason.servisstoklar a,
sason.vt_genelstok c
WHERE h.servisstokid = a.id
AND A.ID = C.SERVISSTOKID
AND C.STOKMIKTAR <> 0
AND a.servisid = c.servisid
AND a.SERVISID {servisIdQuery}
) p,
servisstokturler a
WHERE p.servisstokturid = a.id
) asd
group by asd.SERVISSTOKTURID ,asd.HSERVISID
) asasd
group by asasd.HSERVISID
)
stok on servisid = STOK.HSERVISID
inner join vt_servisler vts on vts.servisid= dfdfdf.servisid and vts.dilkod = 'Turkish'
")
.GetDataTable(mr)
.ToModels();
#endregion
CloseCustomAppPool();
return(queryResults);
}
