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; }