//public void UpdateCompanyInfo() private void backgroundWorker_companyProf_DoWork(object sender, DoWorkEventArgs e) { List <String> stock_ary = new List <String>(); companyProf_inprogress = true; try { String qstr = "SELECT Distinct(stock_index) from tw_stock order by stock_index"; MySqlCommand cmd = new MySqlCommand(qstr, db_manager.getConnection()); MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { String idx = (String)rdr["stock_index"]; if (idx.StartsWith("0") == false) { stock_ary.Add(idx); } } rdr.Close(); CompanyProfilePage cpp = new CompanyProfilePage(); //foreach(object obj in stock_ary) while (stock_ary.Count > 0) { String idx = stock_ary.ElementAt(0); stock_ary.RemoveAt(0); Thread.Sleep(10 * 1000); CompanyData company_data = cpp.DownloadInfo(idx); if (company_data.capital.Equals("")) { Logger.v("company profile " + idx + "download fail"); stock_ary.Add(idx); continue; } String capital_str = company_data.capital.Replace(",", "").Replace("元", ""); float capital_float = float.Parse(capital_str); int capital = (int)(capital_float / 100000000); if (cpp.isExistCompanyProfile(db_manager.getConnection(), idx)) { cpp.UpdateCompanyProfile(db_manager.getConnection(), idx, company_data.FullName, capital); Logger.v("company profile " + idx + ", update to " + String.Format("{0}", capital)); } else { cpp.AddCompanyProfile(db_manager.getConnection(), idx, company_data.FullName, capital); Logger.v("company profile " + idx + "added: " + String.Format("{0}", capital)); } if (backgroundWorker_companyProf.CancellationPending) { break; } } } catch (MySql.Data.MySqlClient.MySqlException ex) { //MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message, // "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
/* * public void DownloadMonthRevenue(MySqlConnection conn, String stock_index, int year, int month) * { * CompanyData res = new CompanyData(); * String url = (year > 101) ? @"http://mops.twse.com.tw/mops/web/t05st10" : @"http://mops.twse.com.tw/mops/web/t05st10"; * HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(url); * CookieContainer cookieContainer = new CookieContainer(); * //req.CookieContainer = cookieContainer; * req.UserAgent = "Mozilla/4.0"; * req.Method = "GET"; * try * { * using (WebResponse wr = req.GetResponse()) * { * HttpWebRequest req_post = (HttpWebRequest)HttpWebRequest.Create(url); * req_post.Method = "POST"; * req_post.ContentType = "application/x-www-form-urlencoded"; * String data_src = "encodeURIComponent=1&run=Y&step=0&yearmonth=10112&colorchg=&TYPEK=sii%20&co_id=" + stock_index + "&off=1&year=" + String.Format("{0}", year) + "&month=" + String.Format("{0}", month) + "&firstin=true"; * ASCIIEncoding encoding = new ASCIIEncoding(); * byte[] data = encoding.GetBytes(data_src); * * req_post.ContentLength = data.Length; * * using (Stream stream = req_post.GetRequestStream()) * { * stream.Write(data, 0, data.Length); * } * * HttpWebResponse response = (HttpWebResponse)req_post.GetResponse(); * * string responsehtml = new StreamReader(response.GetResponseStream()).ReadToEnd(); * Document doc = NSoup.NSoupClient.Parse(responsehtml); * * Elements table01s = doc.Select("#table01 .hasBorder"); * if (table01s.Count == 0) * return; * * Elements trs = table01s[0].Select("tr"); * foreach (Element tr in trs) * { * Elements ths = tr.Select("th"); * Elements tds = tr.Select("td"); * if ( (ths.Count >= 1) && (tds.Count >= 2) ) * { * if ("本月".Equals(ths[0].Text())) * { * String revenue_str = tds[1].Text(); * revenue_str = Util.numberString2IntString(revenue_str); // 123,456.78 -> 123456 * string sql_ins = "INSERT INTO " + companyRevenue_db_name + * "(stock_index, year, month, revenue ) VALUE(" + * "'" + stock_index + "'," + year + "," + String.Format("{0}", month) + "," + revenue_str + ");"; * MySqlCommand cmd_insert = new MySqlCommand(sql_ins, conn); * try * { * cmd_insert.ExecuteNonQuery(); * } * catch (MySql.Data.MySqlClient.MySqlException ex) * { * } * } * } * } * * } * } * catch (Exception err) * { * Debug.WriteLine(err.ToString()); * } * //return res; * }*/ public void DownloadMonthRevenue(MySqlConnection conn, String stock_index, int year, int month) { CompanyData res = new CompanyData(); String url = (year > 101) ? @"http://mops.twse.com.tw/mops/web/t05st10_ifrs" : @"http://mops.twse.com.tw/mops/web/t05st10"; HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(url); CookieContainer cookieContainer = new CookieContainer(); //req.CookieContainer = cookieContainer; req.UserAgent = "Mozilla/4.0"; req.Method = "GET"; try { using (WebResponse wr = req.GetResponse()) { HttpWebRequest req_post = (HttpWebRequest)HttpWebRequest.Create(url); req_post.Method = "POST"; req_post.ContentType = "application/x-www-form-urlencoded"; String year_str = String.Format("{0}", year); String month_str = String.Format("{0:00}", month); String data_src = "encodeURIComponent=1&run=Y&step=0&yearmonth=" + year_str + month_str + "&colorchg=&TYPEK=sii%20&co_id=" + stock_index + "&off=1&year=" + year_str + "&month=" + month_str + "&firstin=true"; ASCIIEncoding encoding = new ASCIIEncoding(); byte[] data = encoding.GetBytes(data_src); req_post.ContentLength = data.Length; using (Stream stream = req_post.GetRequestStream()) { stream.Write(data, 0, data.Length); } HttpWebResponse response = (HttpWebResponse)req_post.GetResponse(); string responsehtml = new StreamReader(response.GetResponseStream()).ReadToEnd(); Document doc = NSoup.NSoupClient.Parse(responsehtml); Elements table01s = doc.Select("table .hasBorder"); if (table01s.Count == 0) { return; } String revenue_str = null; //get data from last table and last row foreach (Element table in table01s) { Elements trs = table.Select("tr"); foreach (Element tr in trs) { Elements ths = tr.Select("th"); Elements tds = tr.Select("td"); if ((ths.Count >= 1) && (tds.Count >= 1)) { if ("本月".Equals(ths[0].Text())) { revenue_str = tds[tds.Count - 1].Text(); //last row } } } } if (revenue_str != null) { revenue_str = Util.numberString2IntString(revenue_str); // 123,456.78 -> 123456 string sql_ins = "INSERT INTO " + companyRevenue_db_name + "(stock_index, year, month, revenue ) VALUE(" + "'" + stock_index + "'," + year + "," + String.Format("{0}", month) + "," + revenue_str + ");"; MySqlCommand cmd_insert = new MySqlCommand(sql_ins, conn); try { cmd_insert.ExecuteNonQuery(); Logger.v(" Add revenue:" + stock_index + "'," + year + "," + String.Format("{0}", month) + "," + revenue_str); } catch (MySql.Data.MySqlClient.MySqlException ex) { Logger.e("DownloadMonthRevenue " + stock_index + "'," + year + "," + String.Format("{0}", month) + "failed"); } } } } catch (Exception err) { Debug.WriteLine(err.ToString()); Logger.e("DownloadMonthRevenue " + stock_index + "'," + year + "," + String.Format("{0}", month) + "failed"); } //return res; }
String companyRevenue_db_name = "company_revenue"; //monthly revenue public CompanyData DownloadInfo(String stock_index) { CompanyData res = new CompanyData(); String url = @"http://mops.twse.com.tw/mops/web/t05st03"; HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(url); CookieContainer cookieContainer = new CookieContainer(); //req.CookieContainer = cookieContainer; req.UserAgent = "Mozilla/4.0"; req.Method = "GET"; try { using (WebResponse wr = req.GetResponse()) { HttpWebRequest req_post = (HttpWebRequest)HttpWebRequest.Create(url); req_post.Method = "POST"; req_post.ContentType = "application/x-www-form-urlencoded"; String data_src = "encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&co_id=" + stock_index; //2379"; ASCIIEncoding encoding = new ASCIIEncoding(); byte[] data = encoding.GetBytes(data_src); req_post.ContentLength = data.Length; using (Stream stream = req_post.GetRequestStream()) { stream.Write(data, 0, data.Length); } HttpWebResponse response = (HttpWebResponse)req_post.GetResponse(); string responsehtml = new StreamReader(response.GetResponseStream()).ReadToEnd(); Document doc = NSoup.NSoupClient.Parse(responsehtml); Elements table01_trs = doc.Select("#table01 .hasBorder tr"); if (table01_trs != null) { foreach (Element tr in table01_trs) { Elements ch = tr.Children; int i; for (i = 0; i < ch.Count; i++) { if (ch[i].Tag.ToString().Equals("th")) { String name = ch[i].Text(); String value = ""; if ((i + 1) < ch.Count) //still more than 1 element behind { i++; if (ch[i].Tag.ToString().Equals("td")) { value = ch[i].Text(); if (name.Equals("實收資本額")) { res.capital = value; } else if (name.Equals("公司名稱")) { res.FullName = value; } } } } } } } } } catch (Exception err) { Debug.WriteLine(err.ToString()); Logger.e("DownloadInfo capital:" + stock_index + " failed"); } return(res); }
/*public void DownloadEpsInfo(MySqlConnection conn, String stock_index, int year, int season) * { * CompanyData res = new CompanyData(); * String url = (year > 101) ? @"http://mops.twse.com.tw/mops/web/t163sb01" : @"http://mops.twse.com.tw/mops/web/t56sb01n_1"; * HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(url); * CookieContainer cookieContainer = new CookieContainer(); * //req.CookieContainer = cookieContainer; * req.UserAgent = "Mozilla/4.0"; * req.Method = "GET"; * try * { * using (WebResponse wr = req.GetResponse()) * { * HttpWebRequest req_post = (HttpWebRequest)HttpWebRequest.Create(url); * req_post.Method = "POST"; * req_post.ContentType = "application/x-www-form-urlencoded"; * String data_src = "encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=" + stock_index + "&year=" + String.Format("{0}", year) + "&season=" + String.Format("{0}", season); * ASCIIEncoding encoding = new ASCIIEncoding(); * byte[] data = encoding.GetBytes(data_src); * * req_post.ContentLength = data.Length; * * using (Stream stream = req_post.GetRequestStream()) * { * stream.Write(data, 0, data.Length); * } * * HttpWebResponse response = (HttpWebResponse)req_post.GetResponse(); * * string responsehtml = new StreamReader(response.GetResponseStream()).ReadToEnd(); * Document doc = NSoup.NSoupClient.Parse(responsehtml); * * Elements table01s = doc.Select("#table01"); * if (table01s.Count == 0) * return; * Element table01 = table01s[0]; * Elements tables = table01.Select("table"); * if (tables != null) * { * Element last_table = tables[tables.Count-2]; //?? * Elements trs = last_table.Select("tr"); * foreach (Element tr in trs) * { * Elements tds = tr.Select("td"); * if (tds.Count >= 2) * { * if("基本每股盈餘".Equals(tds[0].Text()) ) * { * String eps_str = tds[1].Text(); * * * // create the java mysql update preparedstatement * String update = "update " + companyEarning_db_name + " set eps=" + eps_str + " where stock_index=" + stock_index + " AND year=" + year + " AND season=" + season + ";" ; * MySqlCommand cmd_insert = new MySqlCommand(update, conn); * try * { * cmd_insert.ExecuteNonQuery(); * } * catch (MySql.Data.MySqlClient.MySqlException ex) * { * } * } * } * } * } * } * } * catch (Exception err) * { * Debug.WriteLine(err.ToString()); * } * //return res; * }*/ public void DownloadEpsInfo(MySqlConnection conn, String stock_index, int year, int season) { CompanyData res = new CompanyData(); String url = (year > 101) ? @"http://mops.twse.com.tw/mops/web/t163sb01" : @"http://mops.twse.com.tw/mops/web/t56sb01n_1"; HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(url); CookieContainer cookieContainer = new CookieContainer(); //req.CookieContainer = cookieContainer; req.UserAgent = "Mozilla/4.0"; req.Method = "GET"; try { using (WebResponse wr = req.GetResponse()) { HttpWebRequest req_post = (HttpWebRequest)HttpWebRequest.Create(url); req_post.Method = "POST"; req_post.ContentType = "application/x-www-form-urlencoded"; String data_src = "encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=" + stock_index + "&year=" + String.Format("{0}", year) + "&season=" + String.Format("{0}", season); ASCIIEncoding encoding = new ASCIIEncoding(); byte[] data = encoding.GetBytes(data_src); req_post.ContentLength = data.Length; using (Stream stream = req_post.GetRequestStream()) { stream.Write(data, 0, data.Length); } HttpWebResponse response = (HttpWebResponse)req_post.GetResponse(); string responsehtml = new StreamReader(response.GetResponseStream()).ReadToEnd(); Document doc = NSoup.NSoupClient.Parse(responsehtml); //Elements table01s = doc.Select("#table01"); //if (table01s.Count == 0) // return; //Element table01 = table01s[0]; Elements tables = doc.Select("table .hasBorder"); foreach (Element table in tables) { Elements trs = table.Select("tr"); foreach (Element tr in trs) { Elements tds = tr.Select("td"); if (tds.Count >= 2) { //if ("基本每股盈餘".Equals(tds[0].Text())) if (tds[0].Text().StartsWith("基本每股盈餘")) { String eps_str = tds[1].Text(); // create the java mysql update preparedstatement String update = "update " + companyEarning_db_name + " set eps=" + eps_str + " where stock_index=" + stock_index + " AND year=" + year + " AND season=" + season + ";"; MySqlCommand cmd_insert = new MySqlCommand(update, conn); try { cmd_insert.ExecuteNonQuery(); Logger.v("update eps:" + stock_index + "(" + year + "," + season + ") : " + eps_str); } catch (MySql.Data.MySqlClient.MySqlException ex) { Logger.e("update eps:" + stock_index + "(" + year + "," + season + ") failed"); } } } } } } } catch (Exception err) { Debug.WriteLine(err.ToString()); Logger.e("update eps:" + stock_index + "(" + year + "," + season + ") failed"); } //return res; }
public void DownloadEarningInfo(MySqlConnection conn, String stock_index, int year, List <YearSeasonMonth> esp2download) { CompanyData res = new CompanyData(); String url = after_ifrs(year) ? @"http://mops.twse.com.tw/mops/web/t163sb08" : @"http://mops.twse.com.tw/mops/web/t05st24"; HttpWebRequest req = (HttpWebRequest)HttpWebRequest.Create(url); CookieContainer cookieContainer = new CookieContainer(); //req.CookieContainer = cookieContainer; req.UserAgent = "Mozilla/4.0"; req.Method = "GET"; try { using (WebResponse wr = req.GetResponse()) { HttpWebRequest req_post = (HttpWebRequest)HttpWebRequest.Create(url); req_post.Method = "POST"; req_post.ContentType = "application/x-www-form-urlencoded"; String data_src = "encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&t05st29_c_ifrs=N&t05st30_c_ifrs=N&TYPEK=all&isnew=true&co_id=" + stock_index + "&year=" + String.Format("{0}", year); ASCIIEncoding encoding = new ASCIIEncoding(); byte[] data = encoding.GetBytes(data_src); req_post.ContentLength = data.Length; using (Stream stream = req_post.GetRequestStream()) { stream.Write(data, 0, data.Length); } HttpWebResponse response = (HttpWebResponse)req_post.GetResponse(); string responsehtml = new StreamReader(response.GetResponseStream()).ReadToEnd(); Document doc = NSoup.NSoupClient.Parse(responsehtml); Elements table01_trs = doc.Select("table .hasBorder tr"); if (table01_trs != null) { String year_str = ""; int season = 0; //foreach (Element tr in table01_trs) for (int row = 1; row < table01_trs.Count; row++) { Element tr = table01_trs[row]; Elements tds = tr.Select("td"); int td_idx = 0; if (tds.Count == 7) {//first td is year String td0str = tds[0].Text(); year_str = td0str.Split(new char[] { ' ' })[0]; td_idx = 2; season = 1; } else if (tds.Count == 6) { td_idx = 1; } else if (tds.Count == 5) //new format { Elements ths = tr.Select("th"); if (ths.Count > 1) { //first season String yesrstr = ths[0].Text(); year_str = yesrstr.Split(new char[] { '年' })[0]; season = 1; } } String[] info = new String[5]; for (int i = 0; i < 5; i++) { info[i] = tds[td_idx++].Text(); } info[0] = Util.numberString2IntString(info[0]); // 123,456.78 -> 123456 //String[] segs = info[0].Split(new char[]{'.'}); // 123,456.78 -> 123456 //info[0] = segs[0].Replace(",", ""); //to do : check if duplicate int year_int = int.Parse(year_str); if (GetSeasonEarning(conn, stock_index, year_int, season) == null) { string sql_ins = "INSERT INTO " + companyEarning_db_name + "(stock_index, year, season, revenue, grossProfitMargin, operatingProfitMargin, netProfitMarginBeforeTax, netProfitMargin ) VALUE(" + "'" + stock_index + "'," + year_str + "," + String.Format("{0}", season) + "," + info[0] + "," + info[1] + "," + info[2] + "," + info[3] + "," + info[4] + " );"; MySqlCommand cmd_insert = new MySqlCommand(sql_ins, conn); try { cmd_insert.ExecuteNonQuery(); Logger.v("add profitMargin: " + stock_index + "(" + year_str + "," + String.Format("{0}", season) + "):" + info[0] + "," + info[1] + "," + info[2] + "," + info[3] + "," + info[4]); esp2download.Add(new YearSeasonMonth(year_int, season)); } catch (MySql.Data.MySqlClient.MySqlException ex) { Logger.e("DownloadEarningInfo:" + stock_index + "(" + year_str + "," + season + ") failed"); } } season++; } } } } catch (Exception err) { Debug.WriteLine(err.ToString()); } //return res; }