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