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 bool Valid()
{
ClearErrors();
if (IsNew && Courses.Any(c => Code == c.Code) || IsNew && Code == 0)
{
AddError("Code", Properties.Resources.Error_Exist);
}
if (string.IsNullOrEmpty(Title))
{
AddError("Title", Properties.Resources.Error_RequiredTitle);
}
if (Teacher == null)
{
AddError("Teacher", Properties.Resources.Error_Required);
}
if (StartDate.CompareTo(FinishDate) > 0 || FinishDate.CompareTo(StartDate) < 0)
{
AddError("StartDate", Properties.Resources.Error_StartDate);
AddError("FinishDate", Properties.Resources.Error_FinishDate);
}
if (StartTime.CompareTo(EndTime) > 0)
{
AddError("StartTime", Properties.Resources.Error_StartTime);
AddError("EndTime", Properties.Resources.Error_EndTime);
}
RaiseErrors();
return(true);
}
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}";
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 int Gap(string beginDate, string endDate)
{
DateTime StartDate, FinishDate;
StartDate = Convert.ToDateTime(ConvertJalaliToMiladi(beginDate));
FinishDate = Convert.ToDateTime(ConvertJalaliToMiladi(endDate));
TimeSpan Diff = FinishDate.Subtract(StartDate);
return(Diff.Days);
}
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 List <string> RememberGroup()
{
List <string> group = new List <string>();
group.Add(NameField.GetAttribute("value"));
group.Add(LocationDDL.GetAttribute("value"));
group.Add(DirectionDDL.GetAttribute("value"));
group.Add(StartDate.GetAttribute("value"));
group.Add(FinishDate.GetAttribute("value"));
group.Add(TeachersField.GetAttribute("value"));
group.Add(ExpertsField.GetAttribute("value"));
group.Add(StageDDL.GetAttribute("value"));
return(group);
}
private async void initAvailableAttractions()
{
if (Destination == null ||
StartDate == null ||
FinishDate == null)
{
return;
}
var events =
await new Seatwave(Seatwave.DEFAULT_API_KEY, Seatwave.DEFAULT_API_SECRET)
.GetEventsAsync
(
Destination.Name,
StartDate.AddHours(AfterArrivalRelaxTime),
FinishDate.AddHours(BeforeDepartureRelaxTime),
WhatAttraction
);
if (events == null)
{
return;
}
var avail = (from e in events
select new Attraction
{
Code = e.Id,
Name = e.GroupName,
Site = e.EventSwURL,
VenueId = e.VenueId,
Photos = new Uri[1] {
new Uri(e.GroupImageURL)
},
Tickets = createTickets(e)
});
if (avail == null)
{
return;
}
AvailableAttractions = avail.ToArray();
}
public DateTime?SetFinishDate(DataContext _db, int card_id)
{
RentAccrualsData rentAccrualsData = new RentAccrualsData(_db);
var _card = rentAccrualsData.ReturncCardIfnfo(card_id);
if (_card != null)
{
decimal balance = Math.Round(_card.PaymentAmount - _card.ChargeAmount, 2);
decimal amount = (decimal)_card.RentAmount;
int day = 0;
if (amount == 0)
{
return(null);
}
int service_days = rentAccrualsData.serviceDay();
while (true)
{
int coeff = service_days;// DateTime.DaysInMonth(DateTime.Now.AddDays(day).Year, DateTime.Now.AddDays(day).Month);
decimal dayly_amount = amount / coeff;
dayly_amount -= (dayly_amount * (decimal)_card.Card.Discount / 100);
if (balance < dayly_amount)
{
break;
}
balance -= dayly_amount;
day++;
}
FinishDate finishDate = new FinishDate();
_card.Card.RentFinishDate = finishDate.GenerateFinishDate(_card.CahrgeTime).AddDays(day);
rentAccrualsData.saveRentFinisheDate(_card);
return(_card.Card.FinishDate);
}
return(null);
}
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") + "";
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 string ToString()
=> $"ID: {ID}\nOwner: {OwnerID} - {Owner}\nServer: {ServerID}\n" +
$"Start date: {StartDate.ToString("dd/MM/yyyy")}\n" +
$"Finish date: {FinishDate.ToString("dd/MM/yyyy")}";
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 = $" = {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)
{
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)
{
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 string ToString()
{
return($"{Customer.FirstName} {Customer.LastName}:{StartDate.ToString()}->{FinishDate.ToString()}");
}
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 (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 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);
}
/// <summary>
/// Returns the task data as XML formatted for Excel.
/// </summary>
/// <returns>The task data as XML formatted for Excel.</returns>
public string ToXml()
{
string s = "";
//s += "<Row>" + System.Environment.NewLine;
//s += "<Cell><Data ss:Type=\"String\">" + Wbs + "</Data></Cell>" + System.Environment.NewLine;
//s += "<Cell><Data ss:Type=\"String\">" + ToXml(Name) + "</Data></Cell>" + System.Environment.NewLine;
//s += "<Cell ss:StyleID=\"s21\"><Data ss:Type=\"DateTime\">" + StartDateExcelFormat + "</Data></Cell>" + System.Environment.NewLine;
//s += "<Cell ss:StyleID=\"s21\"><Data ss:Type=\"DateTime\">" + FinishDateExcelFormat + "</Data></Cell>" + System.Environment.NewLine;
//s += "<Cell><Data ss:Type=\"Number\">" + (PercentComplete /100.0) + "</Data></Cell>" + System.Environment.NewLine;
//s += "</Row>" + System.Environment.NewLine;
s += "<SaveVersion>" + savedVersion + "</SaveVersion>" + System.Environment.NewLine;
s += "<Name>" + ToXml(Name) + "</Name>" + System.Environment.NewLine;
s += "<Title>" + ToXml(titleName) + "</Title>" + System.Environment.NewLine;
s += "<CreationDate>" + createdDate.ToString("yyyy-MM-dd'T'HH:mm:ss") + "</CreationDate>" + System.Environment.NewLine;
s += "<LastSaved>" + lastSaved.ToString("yyyy-MM-dd'T'HH:mm:ss") + "</LastSaved>" + System.Environment.NewLine;
s += "<ScheduleFromStart>1</ScheduleFromStart>" + System.Environment.NewLine;
s += "<StartDate>" + StartDate.ToString("yyyy-MM-dd'T'HH:mm:ss") + "</StartDate>" + System.Environment.NewLine;
s += "<FinishDate>" + FinishDate.ToString("yyyy-MM-dd'T'HH:mm:ss") + "</FinishDate>" + System.Environment.NewLine;
s += "<FYStartDate>1</FYStartDate>" + System.Environment.NewLine;
s += "<CriticalSlackLimit>0</CriticalSlackLimit>" + System.Environment.NewLine;
s += "<CurrencyDigits>2</CurrencyDigits>" + System.Environment.NewLine;
s += "<CurrencySymbol>$</CurrencySymbol>" + System.Environment.NewLine;
s += "<CurrencyCode>USD</CurrencyCode>" + System.Environment.NewLine;
s += "<CurrencySymbolPosition>0</CurrencySymbolPosition>" + System.Environment.NewLine;
s += "<CalendarUID>1</CalendarUID>" + System.Environment.NewLine;
s += "<DefaultStartTime>08:00:00</DefaultStartTime>" + System.Environment.NewLine;
s += "<DefaultFinishTime>17:00:00</DefaultFinishTime>" + System.Environment.NewLine;
s += "<MinutesPerDay>480</MinutesPerDay>" + System.Environment.NewLine;
s += "<MinutesPerWeek>2400</MinutesPerWeek>" + System.Environment.NewLine;
s += "<DaysPerMonth>20</DaysPerMonth>" + System.Environment.NewLine;
s += "<DefaultTaskType>0</DefaultTaskType>" + System.Environment.NewLine;
s += "<DefaultFixedCostAccrual>3</DefaultFixedCostAccrual>" + System.Environment.NewLine;
s += "<DefaultStandardRate>0</DefaultStandardRate>" + System.Environment.NewLine;
s += "<DefaultOvertimeRate>0</DefaultOvertimeRate>" + System.Environment.NewLine;
s += "<DurationFormat>7</DurationFormat>" + System.Environment.NewLine;
s += "<WorkFormat>2</WorkFormat>" + System.Environment.NewLine;
s += "<EditableActualCosts>0</EditableActualCosts>" + System.Environment.NewLine;
s += "<HonorConstraints>0</HonorConstraints>" + System.Environment.NewLine;
s += "<InsertedProjectsLikeSummary>1</InsertedProjectsLikeSummary>" + System.Environment.NewLine;
s += "<MultipleCriticalPaths>0</MultipleCriticalPaths>" + System.Environment.NewLine;
s += "<NewTasksEffortDriven>0</NewTasksEffortDriven>" + System.Environment.NewLine;
s += "<NewTasksEstimated>1</NewTasksEstimated>" + System.Environment.NewLine;
s += "<SplitsInProgressTasks>1</SplitsInProgressTasks>" + System.Environment.NewLine;
s += "<SpreadActualCost>0</SpreadActualCost>" + System.Environment.NewLine;
s += "<SpreadPercentComplete>0</SpreadPercentComplete>" + System.Environment.NewLine;
s += "<TaskUpdatesResource>1</TaskUpdatesResource>" + System.Environment.NewLine;
s += "<FiscalYearStart>0</FiscalYearStart>" + System.Environment.NewLine;
s += "<WeekStartDay>0</WeekStartDay>" + System.Environment.NewLine;
s += "<MoveCompletedEndsBack>0</MoveCompletedEndsBack>" + System.Environment.NewLine;
s += "<MoveRemainingStartsBack>0</MoveRemainingStartsBack>" + System.Environment.NewLine;
s += "<MoveRemainingStartsForward>0</MoveRemainingStartsForward>" + System.Environment.NewLine;
s += "<MoveCompletedEndsForward>0</MoveCompletedEndsForward>" + System.Environment.NewLine;
s += "<BaselineForEarnedValue>0</BaselineForEarnedValue>" + System.Environment.NewLine;
s += "<AutoAddNewResourcesAndTasks>1</AutoAddNewResourcesAndTasks>" + System.Environment.NewLine;
s += "<CurrentDate>" + DateTime.Now.ToString("yyyy-MM-dd'T'HH:mm:ss") + "</CurrentDate>" + System.Environment.NewLine;
s += "<MicrosoftProjectServerURL>1</MicrosoftProjectServerURL>" + System.Environment.NewLine;
s += "<Autolink>0</Autolink>" + System.Environment.NewLine;
s += "<NewTaskStartDate>0</NewTaskStartDate>" + System.Environment.NewLine;
s += "<NewTasksAreManual>1</NewTasksAreManual>" + System.Environment.NewLine;
s += "<DefaultTaskEVMethod>0</DefaultTaskEVMethod>" + System.Environment.NewLine;
s += "<ProjectExternallyEdited>0</ProjectExternallyEdited>" + System.Environment.NewLine;
s += "<ExtendedCreationDate>1984-01-01T00:00:00</ExtendedCreationDate>" + System.Environment.NewLine;
s += "<ActualsInSync>0</ActualsInSync>" + System.Environment.NewLine;
s += "<RemoveFileProperties>0</RemoveFileProperties>" + System.Environment.NewLine;
s += "<AdminProject>0</AdminProject>" + System.Environment.NewLine;
s += "<UpdateManuallyScheduledTasksWhenEditingLinks>1</UpdateManuallyScheduledTasksWhenEditingLinks>" + System.Environment.NewLine;
s += "<KeepTaskOnNearestWorkingTimeWhenMadeAutoScheduled>0</KeepTaskOnNearestWorkingTimeWhenMadeAutoScheduled>" + System.Environment.NewLine;
s += "<OutlineCodes/>" + System.Environment.NewLine;
s += "<WBSMasks/>" + System.Environment.NewLine;
s += "<ExtendedAttributes>" + System.Environment.NewLine;
s += "<ExtendedAttribute>" + System.Environment.NewLine;
s += "<FieldID>188743731</FieldID>" + System.Environment.NewLine;
s += "<FieldName>Text1</FieldName>" + System.Environment.NewLine;
s += "<Alias>Required Artifact</Alias>" + System.Environment.NewLine;
s += "<Guid>000039B7-8BBE-4CEB-82C4-FA8C0B400033</Guid>" + System.Environment.NewLine;
s += "<SecondaryPID>255869028</SecondaryPID>" + System.Environment.NewLine;
s += "<SecondaryGuid>000039B7-8BBE-4CEB-82C4-FA8C0F404064</SecondaryGuid>" + System.Environment.NewLine;
s += "</ExtendedAttribute>" + System.Environment.NewLine;
s += "</ExtendedAttributes>" + System.Environment.NewLine;
s += "<Calendars>" + System.Environment.NewLine;
s += "<Calendar>" + System.Environment.NewLine;
s += "<UID>1</UID>" + System.Environment.NewLine;
s += "<Name>Standard</Name>" + System.Environment.NewLine;
s += "<IsBaseCalendar>1</IsBaseCalendar>" + System.Environment.NewLine;
s += "<IsBaselineCalendar>0</IsBaselineCalendar>" + System.Environment.NewLine;
s += "<BaseCalendarUID>-1</BaseCalendarUID>" + System.Environment.NewLine;
s += "<WeekDays>" + System.Environment.NewLine;
s += "<WeekDay>" + System.Environment.NewLine;
s += "<DayType>1</DayType>" + System.Environment.NewLine;
s += "<DayWorking>0</DayWorking>" + System.Environment.NewLine;
s += "</WeekDay>" + System.Environment.NewLine;
s += "<WeekDay>" + System.Environment.NewLine;
s += "<DayType>2</DayType>" + System.Environment.NewLine;
s += "<DayWorking>1</DayWorking>" + System.Environment.NewLine;
s += "<WorkingTimes>" + System.Environment.NewLine;
s += "<WorkingTime>" + System.Environment.NewLine;
s += "<FromTime>08:00:00</FromTime>" + System.Environment.NewLine;
s += "<ToTime>12:00:00</ToTime>" + System.Environment.NewLine;
s += "</WorkingTime>" + System.Environment.NewLine;
s += "<WorkingTime>" + System.Environment.NewLine;
s += "<FromTime>13:00:00</FromTime>" + System.Environment.NewLine;
s += "<ToTime>17:00:00</ToTime>" + System.Environment.NewLine;
s += "</WorkingTime>" + System.Environment.NewLine;
s += "</WorkingTimes>" + System.Environment.NewLine;
s += "</WeekDay>" + System.Environment.NewLine;
s += "</WeekDays>" + System.Environment.NewLine;
s += "<Exceptions>" + System.Environment.NewLine;
s += "<Exception>" + System.Environment.NewLine;
s += "<EnteredByOccurrences>0</EnteredByOccurrences>" + System.Environment.NewLine;
s += "<TimePeriod>" + System.Environment.NewLine;
s += "<FromDate>2017-11-18T00:00:00</FromDate>" + System.Environment.NewLine;
s += "<ToDate>2017-11-18T23:59:00</ToDate>" + System.Environment.NewLine;
s += "</TimePeriod>" + System.Environment.NewLine;
s += "<Occurrences>1</Occurrences>" + System.Environment.NewLine;
s += "<Name></Name>" + System.Environment.NewLine;
s += "<Type>1</Type>" + System.Environment.NewLine;
s += "<DayWorking>1</DayWorking>" + System.Environment.NewLine;
s += "<WorkingTimes>" + System.Environment.NewLine;
s += "<WorkingTime>" + System.Environment.NewLine;
s += "<FromTime>08:00:00</FromTime>" + System.Environment.NewLine;
s += "<ToTime>12:00:00</ToTime>" + System.Environment.NewLine;
s += "</WorkingTime>" + System.Environment.NewLine;
s += "<WorkingTime>" + System.Environment.NewLine;
s += "<FromTime>13:00:00</FromTime>" + System.Environment.NewLine;
s += "<ToTime>17:00:00</ToTime>" + System.Environment.NewLine;
s += "</WorkingTime>" + System.Environment.NewLine;
s += "</WorkingTimes>" + System.Environment.NewLine;
s += "</Exception>" + System.Environment.NewLine;
s += "</Exceptions>" + System.Environment.NewLine;
s += "</Calendar>" + System.Environment.NewLine;
s += "<Calendar>" + System.Environment.NewLine;
s += "<UID>3</UID>" + System.Environment.NewLine;
s += "<Name>Yan (Yana</Name>" + System.Environment.NewLine;
s += "<IsBaseCalendar>0</IsBaseCalendar>" + System.Environment.NewLine;
s += "<IsBaselineCalendar>0</IsBaselineCalendar>" + System.Environment.NewLine;
s += "<BaseCalendarUID>1</BaseCalendarUID>" + System.Environment.NewLine;
s += "</Calendar>" + System.Environment.NewLine;
s += "</Calendars>";
// *** always remove last newline reference
return(s);
}
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)
{
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 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}";
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)
{
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}";
#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);
}
