public static List <SCList> FindScForRevision(string conn, int Provoz, int?Umisteni, int Rok, int Polol)
{
string SAP_dtb = ConfigurationManager.ConnectionStrings["SAP_dtb"].ConnectionString;
string RS_dtb = ConfigurationManager.ConnectionStrings["RS_dtb"].ConnectionString;
List <SCList> listplanrev = new List <SCList>();
StringBuilder sql = new StringBuilder();
sql.Append(" select t4.id, ");
sql.Append($" case when(Year(DATEADD(month, convert(int, t6.periodarevize), coalesce(t4.datumrevize, t4.datumprirazeni))) = '{Rok}'");
sql.Append($" and((Month(DATEADD(month, convert(int, t6.periodarevize), coalesce(t4.datumrevize, t4.datumprirazeni))) <= 6 and '{Polol}' = '1')");
sql.Append($" or(Month(DATEADD(month, convert(int, t6.periodarevize), coalesce(t4.datumrevize, t4.datumprirazeni))) > 6 and '{Polol}' = '2')))");
sql.Append($" or(Year(DATEADD(month, convert(int, t6.periodarevize), DATEADD(month, convert(int, t6.periodarevize), coalesce(t4.datumrevize, t4.datumprirazeni)))) = '{Rok}'");
sql.Append($" and((Month(DATEADD(month, convert(int, t6.periodarevize), DATEADD(month, convert(int, t6.periodarevize), coalesce(t4.datumrevize, t4.datumprirazeni)))) <= 6 and '{Polol}' = '1')");
sql.Append($" or(Month(DATEADD(month, convert(int, t6.periodarevize), DATEADD(month, convert(int, t6.periodarevize), coalesce(t4.datumrevize, t4.datumprirazeni)))) > 6 and '{Polol}' = '2')))");
sql.Append(" then 1 else 0 end as 'Revize',");
sql.Append($" case when(Year(DATEADD(month, convert(int, t6.periodapyro), coalesce(t4.datumpyro, t4.datumprirazeni))) = '{Rok}'");
sql.Append($" and((Month(DATEADD(month, convert(int, t6.periodapyro), coalesce(t4.datumpyro, t4.datumprirazeni))) <= 6 and '{Polol}' = '1')");
sql.Append($" or(Month(DATEADD(month, convert(int, t6.periodapyro), coalesce(t4.datumpyro, t4.datumprirazeni))) > 6 and '{Polol}' = '2')");
sql.Append(" )) then 1 else 0 end as 'Pyro',");
sql.Append($" case when(Year(DATEADD(month, convert(int, t6.periodabaterie), coalesce(t4.datumbaterie, t4.datumprirazeni))) = '{Rok}'");
sql.Append($" and((Month(DATEADD(month, convert(int, t6.periodabaterie), coalesce(t4.datumbaterie, t4.datumprirazeni))) <= 6 and '{Polol}' = '1')");
sql.Append($" or(Month(DATEADD(month, convert(int, t6.periodabaterie), coalesce(t4.datumbaterie, t4.datumprirazeni))) > 6 and '{Polol}' = '2')");
sql.Append(" )) then 1 else 0 end as 'Baterie',");
sql.Append($" case when(Year(DATEADD(month, convert(int, t6.periodatlakovazk), coalesce(t4.datumtlkzk, t4.datumprirazeni))) = '{Rok}'");
sql.Append($" and((Month(DATEADD(month, convert(int, t6.periodatlakovazk), coalesce(t4.datumtlkzk, t4.datumprirazeni))) <= 6 and '{Polol}' = '1')");
sql.Append($" or(Month(DATEADD(month, convert(int, t6.periodatlakovazk), coalesce(t4.datumtlkzk, t4.datumprirazeni))) > 6 and '{Polol}' = '2')");
sql.Append(" )) then 1 else 0 end as 'TlKZK'");
// revize tlakove nadoby
// vnitrni revize tlakove nadoby
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");
if (Umisteni == null)
{
sql.Append($" left join [{RS_dtb}].[dbo].[umisteni] t3 on t3.provozid = t2.id and t3.SamostatnaRevize = 'false' ");
}
else
{
sql.Append($" left join[{RS_dtb}].[dbo].[umisteni] t3 on t3.provozid = t2.id and t3.SamostatnaRevize = 'true'");
}
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($" and T2.Id = {Provoz}");
if (Umisteni != null)
{
sql.Append($" and T3.Id = {Umisteni}");
}
//LOGOVANI
log.Debug($"FindScForRevision Provoz:{Provoz}, Umisteni: {Umisteni}, Rok: {Rok}, Polol: {Polol} ");
log.Debug(sql.ToString());
SqlConnection cnn = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand
{
Connection = cnn,
CommandText = sql.ToString()
};
cnn.Open();
cmd.ExecuteNonQuery();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
//MAKES IT HERE
while (dr.Read())
{
SCList item = new SCList();
try
{
item.Id = dr.GetInt32(dr.GetOrdinal("id"));
}
catch (Exception ex)
{
log.Debug($"id prázdné {ex.Message} {ex.InnerException} {ex.Data}");
}
try
{
item.Revize = dr.GetInt32(dr.GetOrdinal("Revize"));
}
catch (Exception ex)
{
log.Debug($"Revize prázdné {ex.Message} {ex.InnerException} {ex.Data}");
}
try
{
item.Pyro = dr.GetInt32(dr.GetOrdinal("Pyro"));
}
catch (Exception ex)
{
log.Debug($"Pyro prázdné {ex.Message} {ex.InnerException} {ex.Data}");
}
try
{
item.Baterie = dr.GetInt32(dr.GetOrdinal("Baterie"));
}
catch (Exception ex)
{
log.Debug($"Baterie prázdné {ex.Message} {ex.InnerException} {ex.Data}");
}
try
{
item.TlkZk = dr.GetInt32(dr.GetOrdinal("TlkZk"));
}
catch (Exception ex)
{
log.Debug($"TlkZk prázdné {ex.Message} {ex.InnerException} {ex.Data}");
}
listplanrev.Add(item);
}
}
cnn.Close();
return(listplanrev);
}
/// <summary>
/// Prohledani seznamu a dohledani zda již neexistuji revize v danem obdobi
/// </summary>
/// <param name="conn"></param>
/// <param name="listplanrev"></param>
/// <returns></returns>
private static List <VypocetPlanuRevizi> LoopAndCreate(string conn, List <VypocetPlanuRevizi> listplanrev)
{
foreach (var item in listplanrev)
{
if (item.UmisteniId == 0)
{
var r1exist = Revize.ExistRevision(item.ZakaznikId, item.ProvozId, item.Rok_R1, item.R1POL, null);
if (r1exist == false)
{
item.Revize1 = Revize.GenerateRevision(item.ProvozId, item.Rok_R1, item.R1POL, item.R1.Value, StatusRevize.Planned(), null, "", "");
List <SCList> sclist = new List <SCList>();
sclist = SCList.FindScForRevision(conn, item.ProvozId, null, item.Rok_R1, item.R1POL);
var t = SCList.AddItemsFromList(sclist, item.Revize1.Id);
RevizeSC revizesc = new RevizeSC();
Revize.UpdateRevizeHeader(item.Revize1.Id);
//bool done = RevizeSC.CreateUpdateSC(SCProvozu.GetList(item.ProvozId, null, 1, null), item.Revize1.Id);
}
else
{
item.Revize1 = Revize.ReturnRevision(item.ZakaznikId, item.ProvozId, item.Rok_R1, item.R1POL, null, null);
}
var r2exist = Revize.ExistRevision(item.ZakaznikId, item.ProvozId, item.Rok_R2, item.R2POL, null);
if (r2exist == false)
{
item.Revize2 = Revize.GenerateRevision(item.ProvozId, item.Rok_R2, item.R2POL, item.R2.Value, StatusRevize.Planned(), null, "", "");
List <SCList> sclist = new List <SCList>();
sclist = SCList.FindScForRevision(conn, item.ProvozId, null, item.Rok_R2, item.R2POL);
var t = SCList.AddItemsFromList(sclist, item.Revize2.Id);
RevizeSC revizesc = new RevizeSC();
Revize.UpdateRevizeHeader(item.Revize2.Id);
}
else
{
item.Revize2 = Revize.ReturnRevision(item.ZakaznikId, item.ProvozId, item.Rok_R2, item.R2POL, null, null);
}
// update hlavicky - počet baterií, palníků atd
}
else
{
var r1exist = Revize.ExistRevision(item.ZakaznikId, item.ProvozId, item.Rok_R1, item.R1POL, item.UmisteniId);
if (r1exist == false)
{
item.Revize1 = Revize.GenerateRevision(item.ProvozId, item.Rok_R1, item.R1POL, item.R1.Value, StatusRevize.Planned(), item.UmisteniId, "", "");
List <SCList> sclist = new List <SCList>();
sclist = SCList.FindScForRevision(conn, item.ProvozId, item.UmisteniId, item.Rok_R1, item.R1POL);
var t = SCList.AddItemsFromList(sclist, item.Revize1.Id);
RevizeSC revizesc = new RevizeSC();
Revize.UpdateRevizeHeader(item.Revize1.Id);
}
else
{
item.Revize1 = Revize.ReturnRevision(item.ZakaznikId, item.ProvozId, item.Rok_R1, item.R1POL, item.UmisteniId, null);
}
var r2exist = Revize.ExistRevision(item.ZakaznikId, item.ProvozId, item.Rok_R2, item.R2POL, item.UmisteniId);
if (r2exist == false)
{
item.Revize2 = Revize.GenerateRevision(item.ProvozId, item.Rok_R2, item.R2POL, item.R2.Value, StatusRevize.Planned(), item.UmisteniId, "", "");
List <SCList> sclist = new List <SCList>();
sclist = SCList.FindScForRevision(conn, item.ProvozId, item.UmisteniId, item.Rok_R2, item.R2POL);
var t = SCList.AddItemsFromList(sclist, item.Revize2.Id);
RevizeSC revizesc = new RevizeSC();
Revize.UpdateRevizeHeader(item.Revize2.Id);
}
else
{
item.Revize2 = Revize.ReturnRevision(item.ZakaznikId, item.ProvozId, item.Rok_R2, item.R2POL, item.UmisteniId, null);
}
// update hlavicky - počet baterií, palníků atd
}
}
return(listplanrev);
}