Beispiel #1
0
        /// <summary>
        /// 일별주가 HTML 파싱 from Naver
        /// </summary>
        /// <param name="htmlCode"></param>
        /// <returns></returns>
        private DataTable parseHtml_price(string httpAddress, string code, int page)
        {
            DataTable dtPrice = new DataTable();

            dtPrice.Columns.Add("cateCd", typeof(string));
            dtPrice.Columns.Add("tradeDt", typeof(string));
            dtPrice.Columns.Add("stPrice", typeof(int));
            dtPrice.Columns.Add("highPrice", typeof(int));
            dtPrice.Columns.Add("lowPrice", typeof(int));
            dtPrice.Columns.Add("endPrice", typeof(int));
            dtPrice.Columns.Add("tradeSum", typeof(int));
            dtPrice.Columns.Add("antSum", typeof(int));
            dtPrice.Columns.Add("orgSum", typeof(int));
            dtPrice.Columns.Add("forSum", typeof(int));

            logDTO.setCateCd(code);
            logDTO.setDbNm("price");
            logDTO.setHttpAdrs(httpAddress);
            logDTO.setFuncNm(MethodBase.GetCurrentMethod().Name.ToString());
            logDTO.setPageNo(page);

            try
            {
                HtmlAgilityPack.HtmlWeb      web = new HtmlWeb();
                HtmlAgilityPack.HtmlDocument doc = web.Load(httpAddress);

                DataRow newRow;

                /* 거래일 추출 */
                int j = 0;
                foreach (HtmlNode row in doc.DocumentNode.SelectNodes("//span[@class='tah p10 gray03']"))
                {
                    newRow = dtPrice.NewRow();
                    string tradeDt_yyyymmdd = row.InnerText.ToString().Replace(".", "");
                    newRow["tradeDt"] = tradeDt_yyyymmdd;
                    dtPrice.Rows.Add(newRow);
                    j++;
                }

                /* 주가정보 추출 */
                int i = 0;
                foreach (HtmlNode row in doc.DocumentNode.SelectNodes("//span[@class='tah p11']"))
                {
                    int rowNum = i / 5;

                    if (row.InnerText == "0" && i % 5 == 1)
                    {
                        continue;
                    }
                    else if (row.InnerText == "0" && i % 5 == 4)
                    {
                        // 거래량에 0이 들어왔을 경우
                        dtPrice.Rows[rowNum]["tradeSum"] = 0;
                        i++;
                        continue;
                    }
                    dtPrice.Rows[rowNum]["cateCd"] = code;

                    switch (i % 5)
                    {
                    case 0:     // 종가
                        dtPrice.Rows[rowNum]["endPrice"] = row.InnerText.ToString() == "0" ? Convert.ToInt32(row.InnerText) : Convert.ToInt32(row.InnerText.Replace(",", ""));
                        break;

                    case 1:     // 시가
                        dtPrice.Rows[rowNum]["stPrice"] = row.InnerText.ToString() == "0" ? Convert.ToInt32(row.InnerText) : Convert.ToInt32(row.InnerText.Replace(",", ""));
                        break;

                    case 2:     // 고가
                        dtPrice.Rows[rowNum]["highPrice"] = row.InnerText.ToString() == "0" ? Convert.ToInt32(row.InnerText) : Convert.ToInt32(row.InnerText.Replace(",", ""));
                        break;

                    case 3:     // 저가
                        dtPrice.Rows[rowNum]["lowPrice"] = row.InnerText.ToString() == "0" ? Convert.ToInt32(row.InnerText) : Convert.ToInt32(row.InnerText.Replace(",", ""));
                        break;

                    case 4:     // 거래량
                        dtPrice.Rows[rowNum]["tradeSum"] = row.InnerText.ToString() == "0" ? Convert.ToInt32(row.InnerText) : Convert.ToInt32(row.InnerText.Replace(",", ""));
                        break;
                    }
                    i++;
                }
            }
            catch (Exception ex)
            {
                //logDTO.setMemo(ex.Message.ToString());
                //memoLog.Text += "\nMethodName =" + MethodBase.GetCurrentMethod().Name + ", code= " + code + ", page= " + page.ToString() + "==>" + ex.Message.ToString();
                //DB_insert_log();
                // logDB 에 저장
                throw ex;
            }

            return(dtPrice);
        }
        /// <summary>
        /// DB 저장 - 공통
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dt"></param>
        public void DB_insert(string tableName, DataTable dt)
        {
            string MyconnectString = "server=127.0.0.1; port=3306 ; database= investment ; uid=root ; pwd= 5587 ; SslMode=none";

            logDTO = new PuffAdderApplication.DTO.LogDTO();
            logDTO.setDbNm(tableName);
            logDTO.setFuncNm(MethodBase.GetCurrentMethod().Name);
            MySqlConnection conn = new MySqlConnection(MyconnectString);

            conn.Open();

            try
            {
                switch (tableName)
                {
                case "price":
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        MySqlCommand comm = conn.CreateCommand();

                        comm.CommandText = "INSERT INTO price" +
                                           " (CATE_CD" +
                                           ", TRADE_DT" +
                                           ", ST_PRICE" +
                                           ", HIGH_PRICE" +
                                           ", LOW_PRICE" +
                                           ", END_PRICE" +
                                           ", TRADE_SUM) " +
                                           "VALUES(@CATE_CD" +
                                           ", @TRADE_DT" +
                                           ", @ST_PRICE" +
                                           ", @HIGH_PRICE" +
                                           ", @LOW_PRICE" +
                                           ", @END_PRICE" +
                                           ", @TRADE_SUM) " +
                                           "ON DUPLICATE KEY " +
                                           "UPDATE ST_PRICE = @ST_PRICE" +
                                           ", HIGH_PRICE = @HIGH_PRICE" +
                                           ", LOW_PRICE = @LOW_PRICE" +
                                           ", END_PRICE = @END_PRICE" +
                                           ", TRADE_SUM = @TRADE_SUM";

                        logDTO.setCateCd(dt.Rows[i]["cateCd"].ToString());
                        logDTO.setTradeDt(dt.Rows[i]["tradeDt"].ToString());

                        comm.Parameters.AddWithValue("@CATE_CD", dt.Rows[i]["cateCd"]);
                        comm.Parameters.AddWithValue("@TRADE_DT", dt.Rows[i]["tradeDt"]);
                        comm.Parameters.AddWithValue("@ST_PRICE", dt.Rows[i]["stPrice"]);
                        comm.Parameters.AddWithValue("@HIGH_PRICE", dt.Rows[i]["highPrice"]);
                        comm.Parameters.AddWithValue("@LOW_PRICE", dt.Rows[i]["lowPrice"]);
                        comm.Parameters.AddWithValue("@END_PRICE", dt.Rows[i]["endPrice"]);
                        comm.Parameters.AddWithValue("@TRADE_SUM", dt.Rows[i]["tradeSum"]);

                        comm.ExecuteNonQuery();
                    }
                    break;

                case "categ":
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        MySqlCommand comm = conn.CreateCommand();
                        comm.CommandText = "INSERT INTO categ" +
                                           " (CATE_CD" +
                                           ", CATE_NM" +
                                           ", MARKET" +
                                           ", INDU_BASIC" +
                                           ", INDU_DTL" +
                                           ", VALD_YN" +
                                           ", ST_DT" +
                                           ", END_DT) " +
                                           "VALUES(@CATE_CD" +
                                           ", @CATE_NM" +
                                           ", @MARKET" +
                                           ", @INDU_BASIC" +
                                           ", @INDU_DTL" +
                                           ", @VALD_YN" +
                                           ", @ST_DT" +
                                           ", @END_DT) " +
                                           "ON DUPLICATE KEY " +
                                           "UPDATE CATE_NM = @CATE_NM" +
                                           ", MARKET  = @MARKET" +
                                           ", INDU_BASIC = @INDU_BASIC" +
                                           ", INDU_DTL = @INDU_DTL" +
                                           ", VALD_YN = @VALD_YN" +
                                           ", ST_DT = @ST_DT" +
                                           ", END_DT = @END_DT";

                        comm.Parameters.AddWithValue("@CATE_CD", dt.Rows[i]["cateCd"]);
                        comm.Parameters.AddWithValue("@CATE_NM", dt.Rows[i]["cateNm"]);
                        comm.Parameters.AddWithValue("@MARKET", dt.Rows[i]["market"]);
                        comm.Parameters.AddWithValue("@INDU_BASIC", dt.Rows[i]["induBasic"]);
                        comm.Parameters.AddWithValue("@INDU_DTL", dt.Rows[i]["induDtl"]);
                        comm.Parameters.AddWithValue("@VALD_YN", "Y");
                        comm.Parameters.AddWithValue("@ST_DT", "19000101");
                        comm.Parameters.AddWithValue("@END_DT", "99991231");
                        comm.ExecuteNonQuery();
                    }
                    break;

                case "trade":
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (dt.Rows[i]["tradeDt"] == null || dt.Rows[i]["tradeDt"].ToString() == "" || dt.Rows[i]["tradeDt"].ToString() == "0")
                        {
                            return;
                        }

                        MySqlCommand comm = conn.CreateCommand();
                        comm.CommandText = "INSERT INTO trade" +
                                           " (CATE_CD" +
                                           ", TRADE_DT" +
                                           ", ORG_SUM" +
                                           ", FOR_SUM" +
                                           ", FOR_HAVE_CNT" +
                                           ", FOR_HAVE_PNT) " +
                                           "VALUES(@CATE_CD" +
                                           ", @TRADE_DT" +
                                           ", @ORG_SUM" +
                                           ", @FOR_SUM" +
                                           ", @FOR_HAVE_CNT" +
                                           ", @FOR_HAVE_PNT) " +
                                           "ON DUPLICATE KEY " +
                                           "UPDATE ORG_SUM = @ORG_SUM" +
                                           ", FOR_SUM  = @FOR_SUM" +
                                           ", FOR_HAVE_CNT = @FOR_HAVE_CNT" +
                                           ", FOR_HAVE_PNT = @FOR_HAVE_PNT";

                        comm.Parameters.AddWithValue("@CATE_CD", dt.Rows[i]["cateCd"]);
                        comm.Parameters.AddWithValue("@TRADE_DT", dt.Rows[i]["tradeDt"]);
                        comm.Parameters.AddWithValue("@ORG_SUM", dt.Rows[i]["orgSum"]);
                        comm.Parameters.AddWithValue("@FOR_SUM", dt.Rows[i]["forSum"]);
                        comm.Parameters.AddWithValue("@FOR_HAVE_CNT", dt.Rows[i]["forHaveCnt"]);
                        comm.Parameters.AddWithValue("@FOR_HAVE_PNT", dt.Rows[i]["forHavePnt"]);
                        comm.ExecuteNonQuery();
                    }
                    break;

                case "success":
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        MySqlCommand comm = conn.CreateCommand();
                        comm.CommandText = "INSERT INTO success" +
                                           " (CATE_CD " +
                                           ", LAST_DTM )" +
                                           "VALUES(@CATE_CD " +
                                           ", @LAST_DTM )" +
                                           "ON DUPLICATE KEY " +
                                           "UPDATE LAST_DTM = @LAST_DTM";

                        comm.Parameters.AddWithValue("@CATE_CD", dt.Rows[i]["cateCd"]);
                        comm.Parameters.AddWithValue("@LAST_DTM", DateTime.Today.ToString("yyyyMMdd"));
                        comm.ExecuteNonQuery();
                        if (i == dt.Rows.Count - 1)
                        {
                            //memoLog.Text += "\n엑셀데이터 저장 완료";
                        }
                    }
                    break;
                }
            }
            catch (Exception ex)
            {
                logDTO.setMemo(ex.Message.ToString());
                DB_insert_log();
                //PuffAdderApplication.Client.ActiveForm.
                //memoLog.Text += "\n" + MethodBase.GetCurrentMethod().Name + "==>" + ex.Message.ToString();
            }
        }