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