Inventur

private string checkSubStocks(int inv_id)
        {
            string query = "SELECT[INV_LageaXVon], [INV_LageaYVon] FROM[Justin].[dbo].[LAGER_INVENTUR]  WHERE INV_Jahr = 0 AND INV_RefLagerBis > 0 AND INV_Bemerkung = '' AND [INV_ID] = @inv_id ORDER BY INV_Name";
            var parInvId = new SqlParameter("@inv_id", System.Data.SqlDbType.Int);
            parInvId.Value = inv_id;
            try
            {
                connection.Open();
                var cmd = new SqlCommand(query, connection);
                cmd.Parameters.Add(parInvId);
                var reader = cmd.ExecuteReader();
                reader.Read();
                string lageaX = reader["INV_LageaXVon"].ToString();
                if (lageaX == "")
                    return "";
                string lageaY = reader["INV_LageaYVon"].ToString();
                if (lageaY != "")
                    return "SELECT [Lageo_Name], [Prod_SerienNrErfassen], [Lagea_Menge], [ART_NR], [ART_BEZ] FROM[Justin].[dbo].[vLager_ArtikelInventur] WHERE Lageo_Nr = @stockNr AND Lagea_Menge > 0 AND Lagea_AchseX ='" + lageaX + "' AND Lagea_AchseY = '" + lageaY + "' ORDER BY ART_NR";
                else
                    return "SELECT [Lageo_Name], [Prod_SerienNrErfassen], [Lagea_Menge], [ART_NR], [ART_BEZ] FROM[Justin].[dbo].[vLager_ArtikelInventur] WHERE Lageo_Nr = @stockNr AND Lagea_Menge > 0 AND Lagea_AchseX ='" + lageaX + "' ORDER BY ART_NR";
            }
            catch(Exception ex)
            {
                logger.Error("OmnitrackerDatabridge: JustinDBConnection:checkSubStocks(): Exception: " + ex);
                EventLog.WriteEventLogWarning("OmnitrackerDatabridge: JustinDBConnection:checkSubStocks: Exception: " + ex);
            }
            finally
            {
                connection.Close();
            }
            return "";
        }

        public List<Article> GetArticlesFromStock(int stockNr, int inv_id)
        {
            string query = "SELECT [Lageo_Name], [Prod_SerienNrErfassen], [Lagea_Menge], [ART_NR], [ART_BEZ] FROM[Justin].[dbo].[vLager_ArtikelInventur] WHERE Lageo_Nr = @stockNr AND Lagea_Menge > 0";
            string subStocksQuery = checkSubStocks(inv_id);
            if (subStocksQuery != "")
                query = subStocksQuery;

            var parNr = new SqlParameter("@stockNr", System.Data.SqlDbType.Int);
            parNr.Value = stockNr;
            var cmd = new SqlCommand(query, connection);
            cmd.Parameters.Add(parNr);
            var articles = new List<Article>();
            try
            {
                connection.Open();
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    int amount = Convert.ToInt32(reader["Lagea_Menge"]);
                    string articleNumber = reader["ART_NR"].ToString();
                    string articleDescription = reader["ART_BEZ"].ToString();
                    bool hasSerialNumber = false;
                    if (Convert.ToInt32(reader["Prod_SerienNrErfassen"]) > 0)
                        hasSerialNumber = true;
                    articles.Add(new Article(articleNumber, articleDescription, amount, hasSerialNumber));
                }
            }
            catch(Exception ex)
            {
                logger.Error("OmnitrackerDatabridge: JustinDBConnection:GetArticlesFromStock(): Exception: " + ex);
                EventLog.WriteEventLogWarning("OmnitrackerDatabridge: JustinDBConnection:GetArticlesFromStock: Exception: " + ex);
            }
            finally
            {
                connection.Close();
            }
            foreach(var a in articles)
            {
                if (a.hasSerialNumber)
                    a.serialNumbers = GetSerialNumbersFromArticle(a.articleNumber);
            }
            return articles;
        }