//public DateTime DatumRevize1 { get; set; }
//public DateTime DatumRevize2 { get; set; }
internal protected static List <VypocetPlanuRevizi> Run(string conn, int Year)
{
//bool done = true;
List <VypocetPlanuRevizi> listplanrev = new List <VypocetPlanuRevizi>();
listplanrev = VypocetPlanuRevizi.Calculate(conn, Year);
listplanrev = VypocetPlanuRevizi.LoopAndCreate(conn, listplanrev);
List <VypocetPlanuRevizi> listplanrev2 = new List <VypocetPlanuRevizi>();
listplanrev2 = VypocetPlanuRevizi.Calculate2(conn);
listplanrev2 = VypocetPlanuRevizi.LoopAndCreate(conn, listplanrev2);
return(listplanrev);
}
/// <summary>
/// Výpočet příští revize dle sériových čísel v provozu pro umisteni která nejsou resena samostatně
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
private static List <VypocetPlanuRevizi> Calculate(string conn, int year)
{
string SAP_dtb = ConfigurationManager.ConnectionStrings["SAP_dtb"].ConnectionString;
string RS_dtb = ConfigurationManager.ConnectionStrings["RS_dtb"].ConnectionString;
List <VypocetPlanuRevizi> listplanrev = new List <VypocetPlanuRevizi>();
StringBuilder sql = new StringBuilder();
sql.Append(" select x.ZakaznikId , x.Zakaznik,x.ProvozId ,x.Provoz, ");
sql.Append(" 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(" (");
sql.Append(" select t1.Id as 'ZakaznikId', t1.NazevZakaznika as 'Zakaznik',");
sql.Append(" t2.Id as 'ProvozId', t2.NazevProvozu as 'Provoz', t3.NazevUmisteni, t6.Nazev, t5.seriovecislo,");
sql.Append(" DATEADD(month, convert(int, t6.periodarevize), coalesce(t4.datumrevize, t4.datumprirazeni)) as 'NextRevize',");
sql.Append(" DATEADD(month, convert(int, t6.periodarevize), DATEADD(month, convert(int, t6.periodarevize), coalesce(t4.datumrevize, t4.datumprirazeni))) as 'Next2Revize',");
sql.Append(" DATEADD(month, convert(int, t6.periodabaterie), coalesce(t4.datumbaterie, t4.datumprirazeni)) as 'NextBaterie',");
sql.Append(" DATEADD(month, convert(int, t6.periodapyro), coalesce(t4.datumpyro, t4.datumprirazeni)) as 'NextPyro',");
sql.Append(" DATEADD(month, convert(int, t6.periodatlakovazk), coalesce(t4.datumtlkzk, t4.datumprirazeni)) as 'NextTlkZk'");
sql.Append($" from [{RS_dtb}].[dbo].[Region] t0");
sql.Append($" left join [{RS_dtb}].[dbo].[Zakaznik] t1 on t0.id = t1.regionid");
sql.Append($" left join [{RS_dtb}].[dbo].[provoz] t2 on t2.zakaznikid = t1.id");
sql.Append($" left join [{RS_dtb}].[dbo].[umisteni] t3 on t3.provozid = t2.id and t3.SamostatnaRevize = 'false'");
sql.Append($" left join [{RS_dtb}].[dbo].[scprovozu] t4 on t4.provozid = t2.id and t4.umisteni = t3.id");
sql.Append($" left join [{RS_dtb}].[dbo].[SerioveCislo] t5 on t5.Id = t4.SerioveCisloId");
sql.Append($" left join [{RS_dtb}].[dbo].[Artikl] t6 on t5.ArtiklId = T6.Id");
sql.Append(" where t3.id is not null and t4.id is not null");
sql.Append(" ) x");
sql.Append(" group by x.ZakaznikId, x.Zakaznik,x.ProvozId, x.Provoz");
//LOGOVANI
log.Debug($"Calculate pro revizi pro rok: {year}");
log.Debug(sql.ToString());
SqlConnection cnn = new SqlConnection(conn);
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())
{
VypocetPlanuRevizi item = new VypocetPlanuRevizi();
try
{
item.ZakaznikId = dr.GetInt32(dr.GetOrdinal("ZakaznikId"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
item.Zakaznik = dr.GetString(dr.GetOrdinal("Zakaznik"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
item.ProvozId = dr.GetInt32(dr.GetOrdinal("ProvozId"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
item.Provoz = dr.GetString(dr.GetOrdinal("Provoz"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
item.R1 = dr.GetDateTime(dr.GetOrdinal("R1"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
item.R1POL = dr.GetInt32(dr.GetOrdinal("R1POL"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
item.Rok_R1 = dr.GetInt32(dr.GetOrdinal("Rok_R1"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
item.R2 = dr.GetDateTime(dr.GetOrdinal("R2"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
item.R2POL = dr.GetInt32(dr.GetOrdinal("R2POL"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
try
{
item.Rok_R2 = dr.GetInt32(dr.GetOrdinal("Rok_R2"));
}
catch (Exception ex) { log.Error("Error number: " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException); }
listplanrev.Add(item);
}
}
cnn.Close();
return(listplanrev);
}
public ActionResult RunGenerator(int Year)
{
List <VypocetPlanuRevizi> list = VypocetPlanuRevizi.Run(connectionString, Year);
return(RedirectToAction("Nahled", "Revize", null));
}
