internal protected static List <ZakaznickySeznam> GetCustomerListWithoutRevision(int Rok, int Skupina, string Search)
{
List <ZakaznickySeznam> list = new List <ZakaznickySeznam>();
//načtení defaultního connection stringu SQL SERVIS
string con = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
// definování SQL querry
StringBuilder sql = new StringBuilder();
sql.Append(" select t0.ID as 'ZakaznikId', T0.NazevZakaznika as 'Zakaznik',");
sql.Append(" T1.ID as 'ProvozId', T1.NazevProvozu as 'Provoz',");
sql.Append(" T2.Id as 'UmisteniId', T2.NazevUmisteni as 'NazevUmisteni'");
sql.Append(" from Zakaznik t0 inner join Provoz t1 on t0.id = t1.zakaznikid left join Umisteni t2 on t1.id = t2.provozid and t2.samostatnarevize = 1 left join Region t3 on t0.RegionId = t3.Id");
sql.Append($" where (t3.Skupina = '{Skupina}' or 0 = '{Skupina}') and (t0.NazevZakaznika like '%{Search}%' or '{Search}' = '')");
sql.Append($" and (select COUNT(*) from Revize where provozid = t1.id and (UmisteniId = t2.id or UmisteniID is null) and rok = '{Rok}') = 0");
sql.Append($" and (select COUNT(*) from SCProvozu tx where tx.provozid = t1.id and (tx.Umisteni = t2.id or tx.Umisteni is null or (select SamostatnaRevize from Umisteni where id = tx.Umisteni) = 0)) > 0");
log.Debug($"GetCustomerListWithoutRevision pro Rok: {Rok}, Skupina: {Skupina}, Search: {Search}");
log.Debug(sql.ToString());
SqlConnection cnn = new SqlConnection(con);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = sql.ToString();
cnn.Open();
cmd.ExecuteNonQuery();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
//MAKES IT HERE
while (dr.Read())
{
ZakaznickySeznam item = new ZakaznickySeznam();
try
{
item.ZakaznikId = dr.GetInt32(dr.GetOrdinal("ZakaznikId"));
}
catch (Exception ex)
{
log.Debug("GetCustomerListWithoutRevision - načtení ZakaznikId: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
item.Zakaznik = dr.GetString(dr.GetOrdinal("Zakaznik"));
}
catch (Exception ex)
{
log.Debug("GetCustomerListWithoutRevision - načtení Zakaznik: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
item.ProvozId = dr.GetInt32(dr.GetOrdinal("ProvozId"));
}
catch (Exception ex)
{
log.Debug("GetCustomerListWithoutRevision - načtení ProvozId: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
item.Provoz = dr.GetString(dr.GetOrdinal("Provoz"));
}
catch (Exception ex)
{
log.Debug("GetCustomerListWithoutRevision - načtení Provoz: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
item.UmisteniId = dr.GetInt32(dr.GetOrdinal("UmisteniId"));
}
catch (Exception ex)
{
log.Debug("GetCustomerListWithoutRevision - načtení UmisteniId: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
item.NazevUmisteni = dr.GetString(dr.GetOrdinal("NazevUmisteni"));
}
catch (Exception ex)
{
log.Debug("GetCustomerListWithoutRevision - načtení NazevUmisteni: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
list.Add(item);
}
}
cnn.Close();
return(list);
}
/// <summary>
/// Spocita minimalni datum pro nasledujic revizi 1 a revizi 2
/// </summary>
/// <param name="ZakaznikId"></param>
/// <param name="ProvozId"></param>
/// <param name="Rok"></param>
/// <param name="UmisteniId"></param>
/// <returns></returns>
internal protected static DnyRevize MinimalniDatum(int ZakaznikId, int ProvozId, int Rok, int?UmisteniId)
{
if (UmisteniId == null)
{
UmisteniId = 0;
}
DnyRevize dnyrevize = new DnyRevize();
string con = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
StringBuilder sql = new StringBuilder();
sql.Append(" select x.ZakaznikId , x.Zakaznik ,x.ProvozId ,x.Provoz, min(x.NextRevize) 'R1', Year(min(x.NextRevize)) as 'Rok_R1', case when Month(min(x.NextRevize)) <= 6 then 1 else 2 end as 'R1POL', ");
sql.Append(" min(x.Next2Revize) 'R2', Year(min(x.Next2Revize)) as 'Rok_R2', case when Month(min(x.Next2Revize)) <= 6 then 1 else 2 end as 'R2POL'");
sql.Append(" from (");
sql.Append(" select t1.Id as 'ZakaznikId', t1.NazevZakaznika as 'Zakaznik',");
sql.Append(" t2.Id as 'ProvozId', t2.NazevProvozu as 'Provoz', t3.Id as 'UmisteniId', t3.NazevUmisteni, t6.Nazev, t5.seriovecislo,");
sql.Append(" DATEADD(month, convert(int, coalesce(t4.UpravenaPeriodaRevize,t6.periodarevize)), coalesce(t4.datumrevize, t4.datumprirazeni)) as 'NextRevize',");
sql.Append(" DATEADD(month, convert(int, coalesce(t4.UpravenaPeriodaRevize,t6.periodarevize)), DATEADD(month, convert(int, coalesce(t4.UpravenaPeriodaRevize,t6.periodarevize)), coalesce(t4.datumrevize, t4.datumprirazeni))) as 'Next2Revize',");
sql.Append(" DATEADD(month, convert(int, coalesce(t4.UpravenaPeriodaBaterie,t6.periodabaterie)), coalesce(t4.datumbaterie, t4.datumprirazeni)) as 'NextBaterie',");
sql.Append(" DATEADD(month, convert(int, coalesce(t4.UpravenaPeriodaPyro,t6.periodapyro)), coalesce(t4.datumpyro, t4.datumprirazeni)) as 'NextPyro',");
sql.Append(" DATEADD(month, convert(int, coalesce(t4.UpravenaPeriodaTlkZk,t6.periodatlakovazk)), coalesce(t4.datumtlkzk, t4.datumprirazeni)) as 'NextTlkZk'");
sql.Append(" from Region t0");
sql.Append(" left join Zakaznik t1 on t0.id = t1.regionid");
sql.Append(" left join Provoz t2 on t2.zakaznikid = t1.id");
sql.Append(" left join Umisteni t3 on t3.provozid = t2.id");
sql.Append(" left join Scprovozu t4 on t4.provozid = t2.id and t4.umisteni = t3.id");
sql.Append(" left join SerioveCislo t5 on t5.Id = t4.SerioveCisloId");
sql.Append(" left join Artikl t6 on t5.ArtiklId = T6.Id");
sql.Append(" where");
sql.Append($" t1.ID = '{ZakaznikId}' and T2.id = '{ProvozId}' and(T3.Id = '{UmisteniId}' or '{UmisteniId}' = '0')");
sql.Append(" ) x");
sql.Append(" group by x.ZakaznikId, x.Zakaznik,x.ProvozId, x.Provoz");
//LOGOVANI
log.Debug($"MinimalniDatum pro revizi pro ZakaznikID: {ZakaznikId},ProvozId : {ProvozId}, Rok: {Rok}, UmisteniId: {UmisteniId}");
log.Debug(sql.ToString());
SqlConnection cnn = new SqlConnection(con);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = sql.ToString();
cnn.Open();
cmd.ExecuteNonQuery();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
//MAKES IT HERE
while (dr.Read())
{
ZakaznickySeznam item = new ZakaznickySeznam();
try
{
dnyrevize.DenRevize1 = dr.GetDateTime(dr.GetOrdinal("R1"));
}
catch (Exception ex)
{
log.Debug("MinimalniDatum - Načtení R1: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
try
{
dnyrevize.DenRevize2 = dr.GetDateTime(dr.GetOrdinal("R2"));
}
catch (Exception ex)
{
log.Debug("MinimalniDatum - Načtení R2: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
}
}
}
cnn.Close();
return(dnyrevize);
}