/// <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();
}
}