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);
        }
Exemple #2
0
        /// <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);
        }