コード例 #1
0
        public static void VymenaLahve(int RevizeSCid, int AriclId, string SC, DateTime DatumVyroby, DateTime DatumDodani)
        {
            RevizeSC oldRevizeSC = new RevizeSC();
            Revize   revize      = new Revize();
            Provoz   provoz      = new Provoz();

            RevizeSC     newRevizeSC     = new RevizeSC();
            SCProvozu    oldSCProvozu    = new SCProvozu();
            SCProvozu    newSCProvozu    = new SCProvozu();
            SerioveCislo oldSerioveCislo = new SerioveCislo();
            SerioveCislo newSerioveCislo = new SerioveCislo();
            VymenyLahvi  vymenyLahvi     = new VymenyLahvi();


            oldRevizeSC     = RevizeSC.GetRevizeSCByRevizeSCid(RevizeSCid);
            oldSCProvozu    = SCProvozu.GetSCProvozuById(oldRevizeSC.SCProvozuId);
            oldSerioveCislo = SerioveCislo.GetSerioveCisloById(oldSCProvozu.SerioveCisloId);
            revize          = Revize.GetById(oldRevizeSC.RevizeId);
            provoz          = Provoz.GetById(revize.ProvozId);

            SCImport sCImport = new SCImport();

            sCImport.ArtiklId           = oldSerioveCislo.ArtiklId;
            sCImport.ArtiklSAPKod       = oldSerioveCislo.Artikl.KodSAP;
            sCImport.Baterie            = oldSCProvozu.Baterie;
            sCImport.BaterieArtikl      = oldSCProvozu.BaterieArtikl;
            sCImport.DatumBaterie       = oldSCProvozu.DatumBaterie;
            sCImport.DatumDodani        = DatumDodani;
            sCImport.DatumPosledniZmeny = revize.DatumRevize;
            sCImport.DatumPrirazeni     = revize.DatumRevize;
            sCImport.DatumPyro          = oldSCProvozu.DatumPyro;
            sCImport.DatumRevize        = oldSCProvozu.DatumRevize;
            sCImport.DatumTlkZk         = revize.DatumRevize;
            sCImport.DatumVymeny        = null;
            sCImport.DatumVyroby        = revize.DatumRevize;
            sCImport.Lokace             = oldSCProvozu.Lokace;
            sCImport.Proverit           = false;
            sCImport.Provozy            = oldSCProvozu.ProvozId;
            sCImport.SerioveCislo       = SC;
            sCImport.Submitted          = true;
            sCImport.Umisteni           = oldSCProvozu.Umisteni.Value;
            sCImport.Zakaznik           = provoz.ZakaznikId;
            sCImport.ZakaznikSAPKod     = provoz.Zakaznik.KodSAP;
            sCImport.Znaceni            = oldSCProvozu.Znaceni;

            // vytvoreni Serioveho cisla akcniho prvku
            var id = SerioveCislo.AddSeriovecislo(sCImport);

            newSerioveCislo = SerioveCislo.GetSerioveCisloById(id);
            // vyvoreni noveho provozusc
            var idscprovozu = SCProvozu.AddSCProvozu(sCImport, id);

            newSCProvozu = SCProvozu.GetSCProvozuById(idscprovozu);
            //Dohledani serioveho cisla lahve
            var seriovecislolahve = DohledaniSeriovehoCislaLahveDleSeriovehoCislaAkcnihoprvku(newSerioveCislo.SerioveCislo1, newSerioveCislo.Artikl.KodSAP);

            // vytvoření vazby vymeny mezi jednotlivými SCProvozu
            vymenyLahvi = VymenyLahvi.GenerujVymenu(oldSCProvozu, newSCProvozu, revize.DatumRevize, revize.Id, seriovecislolahve);
            // zneaktivneni stareho provozusc
            SCProvozu.ZneaktivniSCProvozu(oldSCProvozu, revize.DatumRevize);

            // odebrani stare revizesc
            RevizeSC.Remove(oldRevizeSC.Id);

            // vymena v nasledujicich otevrenych revizich stareho revizesc za noveho
            RevizeSC.ChangeRevizeSCForUpcomingOpenRevision(oldSCProvozu, newSCProvozu, revize.DatumRevize);
        }
コード例 #2
0
ファイル: SIL.cs プロジェクト: petnovcz/VST_sprava_servisu
        public static List <SIL_dobaprovozu> Calc_SIL_DobaProvozu(int Rok, SIL_dobaprovozu PrumerDobyProvozu)
        {
            List <SIL_dobaprovozu> list = new List <SIL_dobaprovozu>();

            string        connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            StringBuilder sql = new StringBuilder();


            sql.Append($" declare @rok as int = {Rok} ");
            sql.Append($" declare @prumerTAKU float = '{PrumerDobyProvozu.TAKU.ToString().Replace(",", ".")}'");
            sql.Append($" declare @prumerTAKUII float = '{PrumerDobyProvozu.TAKUII.ToString().Replace(",", ".")}'");
            sql.Append(" select *, " +
                       "case when (((x.II - X.I) * 1.0 / x.PocetdnimeziIaIIrevizi) * 1.0 IS NULL) then @prumerTAKU else CAST(((x.II - X.I) * 1.0 / x.PocetdnimeziIaIIrevizi) * 1.0 as float) end as 'TAKU',"
                       + " case when (((x.II - X.I) * 1.0 / x.PocetdnimeziIaIIrevizi * 1.0) * 365.25 IS null) then @prumerTAKUII else CAST(((x.II - X.I) * 1.0 / x.PocetdnimeziIaIIrevizi * 1.0) * 365.25 as float) end as 'TAKUII' "
                       + " from( select t0.NazevZakaznika," +
                       "t0.Id as 'ZakaznikId', " +
                       "t1.NazevProvozu,t1.Id as 'ProvozId', " +
                       "t2.NazevUmisteni,t2.Id as 'UmisteniId', " +
                       "t5.Nazev as 'Artikl'," +
                       "t5.Id as 'ArtiklId', " +
                       "t4.SerioveCislo, " +
                       "t4.Id as 'SerioveCisloId'," +
                       "t3.Id as 'SCProvozuId', " +
                       "(SELECT HodinyProvozu FROM( SELECT ROW_NUMBER() OVER(ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t1.HodinyProvozu from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok  and t1.SCProvozuId = t3.id) as foo  WHERE rownumber = 2) as 'I',");
            sql.Append("(SELECT KontrolaProvedenaDne FROM( SELECT ROW_NUMBER() OVER(ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t0.KontrolaProvedenaDne from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok  and t1.SCProvozuId = t3.id) as foo  WHERE rownumber = 2 ) as 'DatumI', ");
            sql.Append(" (SELECT HodinyProvozu FROM( SELECT ROW_NUMBER() OVER (ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t1.HodinyProvozu from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok  and t1.SCProvozuId = t3.id) as foo WHERE rownumber = 1) as 'II',");
            sql.Append(" (SELECT KontrolaProvedenaDne FROM( SELECT ROW_NUMBER() OVER(ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t0.KontrolaProvedenaDne from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok  and t1.SCProvozuId = t3.id) as foo  WHERE rownumber = 1 ) as 'DatumII',");
            sql.Append(" CAST(DateDiff(D, ((SELECT KontrolaProvedenaDne FROM( SELECT ROW_NUMBER() OVER(ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t0.KontrolaProvedenaDne from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok  and t1.SCProvozuId = t3.id) as foo WHERE rownumber = 2)),");
            sql.Append(" (SELECT KontrolaProvedenaDne FROM( SELECT ROW_NUMBER() OVER (ORDER BY coalesce(t0.KontrolaProvedenaDne, t0.Datumrevize) desc) AS rownumber, t0.KontrolaProvedenaDne from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok <= @rok  and t1.SCProvozuId = t3.id) as foo WHERE rownumber = 1)) as decimal) as 'PocetdnimeziIaIIrevizi',");
            sql.Append(" (select COUNT(*) from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok = @rok  and t1.SCProvozuId = t3.id) as 'count' from Zakaznik t0 left join Provoz t1 on t1.ZakaznikId = t0.Id left join Umisteni t2 on t2.ProvozId = t1.Id ");
            sql.Append(" left join SCProvozu t3 on t3.Umisteni = t2.Id left join SerioveCislo t4 on t4.Id = t3.SerioveCisloId left join Artikl t5 on t5.Id = t4.ArtiklId left join SkupinaArtiklu t6 on t6.Id = t5.SkupinaArtiklu");
            sql.Append(" where t6.Id = 130 and (select COUNT(*) from Revize t0 left join RevizeSC t1 on t0.Id = t1.RevizeId where Rok = @rok  and t1.SCProvozuId = t3.id) > 0 and t3.StatusId = 1  ) X");

            //log.Debug($"Nacteni dat pri importu artiklu z SAP {sql.ToString()}");
            SqlConnection cnn = new SqlConnection(connectionString);
            //SqlConnection con = new SqlConnection(cnn);

            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())
                {
                    SIL_dobaprovozu sil = new SIL_dobaprovozu();
                    try
                    {
                        sil.ZakaznikId = dr.GetInt32(dr.GetOrdinal("ZakaznikId"));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: ZakaznikId " + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.ProvozId = dr.GetInt32(dr.GetOrdinal("ProvozId"));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: ProvozId" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }

                    try
                    {
                        sil.UmisteniId = dr.GetInt32(dr.GetOrdinal("UmisteniId"));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: UmisteniId" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.SCProvozuId = dr.GetInt32(dr.GetOrdinal("SCProvozuId"));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: SCProvozuId" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.SerioveCisloId = dr.GetInt32(dr.GetOrdinal("SerioveCisloId"));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: SerioveCisloId" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.ArtiklId = dr.GetInt32(dr.GetOrdinal("ArtiklId"));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: ArtiklId" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.DobaprovozuI = dr.GetInt64(dr.GetOrdinal("I"));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: DobaprovozuI" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.DobaprovozuII = dr.GetInt64(dr.GetOrdinal("II"));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: DobaprovozuII" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.DniMeziRevizemi = dr.GetDecimal(dr.GetOrdinal("PocetdnimeziIaIIrevizi"));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: DniMeziRevizemi" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.PocetReviziVRoce = dr.GetInt32(dr.GetOrdinal("count"));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: PocetReviziVRoce" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.TAKU = Convert.ToDecimal(dr.GetDouble(dr.GetOrdinal("TAKU")));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: TAKU" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.TAKUII = Convert.ToDecimal(dr.GetDouble(dr.GetOrdinal("TAKUII")));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: TAKUII" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.DatumI = (dr.GetDateTime(dr.GetOrdinal("DatumI")));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: TAKUII" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }
                    try
                    {
                        sil.DatumII = (dr.GetDateTime(dr.GetOrdinal("DatumII")));
                    }
                    catch (Exception ex)
                    {
                        log.Debug("Error number: TAKUII" + ex.HResult + " - " + ex.Message + " - " + ex.Data + " - " + ex.InnerException);
                    }


                    sil.Zakaznik     = Zakaznik.GetById(sil.ZakaznikId);
                    sil.Provoz       = Provoz.GetById(sil.ProvozId);
                    sil.Umisteni     = Umisteni.GetById(sil.UmisteniId);
                    sil.SerioveCislo = SerioveCislo.GetSerioveCisloById(sil.SerioveCisloId);
                    sil.SCProvozu    = SCProvozu.GetSCProvozuById(sil.SCProvozuId);
                    sil.Artikl       = Artikl.GetArtiklById(sil.ArtiklId);

                    list.Add(sil);
                }
            }
            cnn.Close();
            return(list);
        }