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>();
ReportData reportData = new ReportData();
reportDataSource.add(reportData);
#region Yedek Parça Kısmı
List <QueryResult> parcalar = queryResults.where (t => t.SERVISSTOKTURID != 6).toList();
List <QueryResult> servisIciParcalar = parcalar.where (t => t.BelgeTuru == BelgeTuru.IsEmri).toList();
List <QueryResult> servisDisiParcalar = parcalar.where (t => t.BelgeTuru == BelgeTuru.Digerleri).toList();
#region Servis Içi
reportData.YedekParca.ServisIci.Detay.Oem = servisIciParcalar.where (t => t.ServisStokTur == ServisStokTurIds.Oem).select(t => t.TUTAR).Sum();
reportData.YedekParca.ServisIci.Detay.Oes = servisIciParcalar.where (t => t.ServisStokTur == ServisStokTurIds.Oes).select(t => t.TUTAR).Sum();
reportData.YedekParca.ServisIci.Detay.Esd = servisIciParcalar.where (t => t.ServisStokTur == ServisStokTurIds.EsDeger).select(t => t.TUTAR).Sum();
reportData.YedekParca.ServisIci.Detay.Yan_Sanayi = servisIciParcalar.where (t => t.ServisStokTur == ServisStokTurIds.Yansanayi).select(t => t.TUTAR).Sum();
reportData.YedekParca.ServisIci.Detay.Yan_Sanayi_Muadili_Yok = servisIciParcalar.where (t => t.ServisStokTur == ServisStokTurIds.MuadiliYok).select(t => t.TUTAR).Sum();
//reportData.YedekParca.ServisIci.UygunParca_Ucretli = reportData.YedekParca.ServisIci.Detay.Oem;
reportData.YedekParca.ServisIci.UygunParca_Garanti = 0;
#endregion
#region Servis Dışı
reportData.YedekParca.ServisDisi.Detay.Oem = servisDisiParcalar.where (t => t.ServisStokTur == ServisStokTurIds.Oem).select(t => t.TUTAR).Sum();
reportData.YedekParca.ServisDisi.Detay.Oes = servisDisiParcalar.where (t => t.ServisStokTur == ServisStokTurIds.Oes).select(t => t.TUTAR).Sum();
reportData.YedekParca.ServisDisi.Detay.Esd = servisDisiParcalar.where (t => t.ServisStokTur == ServisStokTurIds.EsDeger).select(t => t.TUTAR).Sum();
reportData.YedekParca.ServisDisi.Detay.Yan_Sanayi = servisDisiParcalar.where (t => t.ServisStokTur == ServisStokTurIds.Yansanayi).select(t => t.TUTAR).Sum();
reportData.YedekParca.ServisDisi.Detay.Yan_Sanayi_Muadili_Yok = servisDisiParcalar.where (t => t.ServisStokTur == ServisStokTurIds.MuadiliYok).select(t => t.TUTAR).Sum();
#endregion
#endregion
#region Yaglar Kısmı
List <QueryResult> yaglar = queryResults.where (t => t.SERVISSTOKTURID == 6).toList();
reportData.Yag.Servis_Icinde_Onarima_Kullanilan = yaglar.where (t => t.BelgeTuru == BelgeTuru.IsEmri).select(t => t.TUTAR).Sum();
reportData.Yag.Servis_Disina_veya_Direk_Satilan = yaglar.where (t => t.BelgeTuru != BelgeTuru.IsEmri).select(t => t.TUTAR).Sum();
#endregion
#region Stok Kısmı
List <Servis.StokRaporu01.QueryResult> servisStokRaporuResults = new Servis.StokRaporu01(selectedServisId, DateTime.Now).ExecuteReport(mr).cast <List <Servis.StokRaporu01.QueryResult> >();
reportData.Stok.Detay.Oem = servisStokRaporuResults.where (t => t.ServisStokTur == ServisStokTurIds.Oem).select(t => t.STOKTUTAR).Sum();
reportData.Stok.Detay.Oes = servisStokRaporuResults.where (t => t.ServisStokTur == ServisStokTurIds.Oes).select(t => t.STOKTUTAR).Sum();
reportData.Stok.Detay.Esd = servisStokRaporuResults.where (t => t.ServisStokTur == ServisStokTurIds.EsDeger).select(t => t.STOKTUTAR).Sum();
reportData.Stok.Detay.Yan_Sanayi = servisStokRaporuResults.where (t => t.ServisStokTur == ServisStokTurIds.Yansanayi).select(t => t.STOKTUTAR).Sum();
reportData.Stok.Detay.Yan_Sanayi_Muadili_Yok = servisStokRaporuResults.where (t => t.ServisStokTur == ServisStokTurIds.MuadiliYok).select(t => t.STOKTUTAR).Sum();
#endregion
CloseCustomAppPool();
return(reportDataSource);
}
public override object ExecuteReport(MethodReturn refMr = null)
{
decimal selectedServisId = ServisIds.first().toString("0").cto <decimal>();
string servisIdQuery = $" = {selectedServisId}";
string dateQuery = "";
int debugkontrol = 0;
#if DEBUG
debugkontrol = 1;
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();
List <QueryResult> queryResults = AppPool.EbaTestConnector.CreateQuery($@"
select
---- 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
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,
----------------------------------------------------------------------- servis içi garanti
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 ,
-------------------------------**************************
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
) ert
")
.GetDataTable(mr)
.ToModels <QueryResult>();
ReportData reportData = new ReportData();
reportData.queryr = queryResults;
string YEDEKPARCATOPLAM1 = (reportData.queryr[0].SERVISICITOPLAM + reportData.queryr[0].SERVISDISITOPLAM).toString();
//QueryResult qr = new QueryResult();
List <QueryResultStok> queryStok = AppPool.EbaTestConnector.CreateQuery($@"
select '{YEDEKPARCATOPLAM1}' as YEDEKPARCATOPLAM,
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_yansanayi,0)) as stok_yansanayi ,
sum(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(fiyat)
end as stok_oes,
case SERVISSTOKTURID
when 7 then sum(fiyat)
end as stok_oeM,
case SERVISSTOKTURID
when 8 then sum(fiyat)
end as stok_essanayi,
case SERVISSTOKTURID
when 9 then sum(fiyat)
end as stok_yansanayi,
case SERVISSTOKTURID
when 11 then sum(fiyat)
end as stok_my
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}
group by SERVISSTOKTURID
) ASDASD
")
.GetDataTable(mr)
.ToModels <QueryResultStok>();
reportData.queryrStk = queryStok;
// reportDataSource.add(reportData);
// reportData.AMBARCEVIRIM = (reportData.queryr[0].YEDEKPARCATOPLAM / reportData.queryrStk[0].STOK_TOPLAM) * 12;
CloseCustomAppPool();
return(reportData);
//return queryResults;
}