private void btnDividend_Click(object sender, EventArgs e)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
// テーブルを作成
List <decimal> tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'dividend'");
if (tblLst[0] > 0)
{
db.DBExecuteSQL("DROP TABLE dividend ");
}
string sql = @"CREATE TABLE dividend
(
OrderNo NUMERIC
,StockCode NUMERIC
,Market TEXT
,CompanyName TEXT
,Dividend NUMERIC
,DividendYield NUMERIC
,DetailUrl TEXT
) ";
db.DBExecuteSQL(sql);
List <Utility.DividendEntity> list = new List <Utility.DividendEntity>();
Utility.FinanceUtil finance = new Utility.FinanceUtil();
list = finance.GetDividendEntityList();
string insertSql = @"INSERT INTO dividend
(
OrderNo
,StockCode
,Market
,CompanyName
,Dividend
,DividendYield
,DetailUrl
) VALUES (
:OrderNo
,:StockCode
,:Market
,:CompanyName
,:Dividend
,:DividendYield
,:DetailUrl
)";
db.DBInsert(insertSql, list);
List <Utility.DividendEntity> dividend = db.DBSelect <Utility.DividendEntity>("SELECT * FROM dividend ");
this.dgvStockPrice.DataSource = dividend;
}
}
private async Task UpdateDividend()
{
Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
this.txtUpdateStatus.Text = "";
this.txtUpdateStatus.Text += "配当利回り取得開始" + Environment.NewLine;
// 情報の取得
List <Utility.DividendEntity> list = new List <Utility.DividendEntity>();
await Task.Run(() =>
{
Utility.FinanceUtil finance = new Utility.FinanceUtil();
list = finance.GetDividendEntityList();
});
sw.Stop();
this.txtUpdateStatus.Text += "配当利回り取得終了 " + sw.Elapsed.ToString() + Environment.NewLine;
sw.Restart();
this.txtUpdateStatus.Text += "配当利回り更新開始" + Environment.NewLine;
await Task.Run(() =>
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
// 削除
db.DBExecuteSQL("DELETE FROM dividend");
// 登録
string insertSql = @"INSERT INTO dividend
(
OrderNo
,StockCode
,Market
,CompanyName
,Dividend
,DividendYield
,DetailUrl
) VALUES (
:OrderNo
,:StockCode
,:Market
,:CompanyName
,:Dividend
,:DividendYield
,:DetailUrl
)";
db.DBInsert(insertSql, list);
db.DBExecuteSQL("DELETE FROM dividend WHERE StockCode NOT IN (SELECT TargetStockCode From targetcode) ");
}
});
sw.Stop();
this.txtUpdateStatus.Text += "配当利回り更新終了 " + sw.Elapsed.ToString() + Environment.NewLine;
}
private async Task UpdateStockCode()
{
Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
this.txtUpdateStatus.Text = "";
this.txtUpdateStatus.Text += "証券コード取得開始" + Environment.NewLine;
// 情報の取得
List <StockPriceEntity> list = new List <StockPriceEntity>();
await Task.Run(() =>
{
Utility.FinanceUtil finance = new Utility.FinanceUtil();
list = finance.GetStockCodeList();
});
sw.Stop();
this.txtUpdateStatus.Text += "証券コード取得終了 " + sw.Elapsed.ToString() + Environment.NewLine;
sw.Restart();
this.txtUpdateStatus.Text += "証券コード更新開始" + Environment.NewLine;
await Task.Run(() =>
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
// 削除
db.DBExecuteSQL("DELETE FROM stockcode");
// 登録
string insertSql = @"INSERT INTO stockcode
(
StockCode
) VALUES (
:StockCode
)";
db.DBInsert(insertSql, list);
}
});
sw.Stop();
this.txtUpdateStatus.Text += "証券コード更新終了 " + sw.Elapsed.ToString() + Environment.NewLine;
}
private void btnStockPrice_Click(object sender, EventArgs e)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
// テーブルを作成
List <decimal> tblLst = db.DBSelect <decimal>("SELECT COUNT(*) CNT FROM sqlite_master WHERE type = 'table' AND name = 'stockprice'");
if (tblLst[0] >= 0)
{
db.DBExecuteSQL("DROP TABLE stockprice ");
}
string sql = @"create table stockprice
(
StockCode NUMERIC
,CompanyName TEXT
,StockDate TEXT
,OpeningPrice NUMERIC
,HighPrice NUMERIC
,LowPrice NUMERIC
,ClosingPrice NUMERIC
,TradeVolume NUMERIC
,AdjustmentClosingPrice NUMERIC
) ";
db.DBExecuteSQL(sql);
List <Utility.StockPriceEntity> list = new List <Utility.StockPriceEntity>();
Utility.FinanceUtil finance = new Utility.FinanceUtil();
list = finance.GetStockPriceEntityList(Convert.ToInt32(txtStockCode.Text), DateTime.Now.AddMonths(-3), DateTime.Now);
string insertSql = @"INSERT INTO stockprice
(
StockCode
,CompanyName
,StockDate
,OpeningPrice
,HighPrice
,LowPrice
,ClosingPrice
,TradeVolume
,AdjustmentClosingPrice
) VALUES (
:StockCode
,:CompanyName
,:StockDate
,:OpeningPrice
,:HighPrice
,:LowPrice
,:ClosingPrice
,:TradeVolume
,:AdjustmentClosingPrice
)";
db.DBInsert(insertSql, list);
List <Utility.StockPriceEntity> stockprice = db.DBSelect <Utility.StockPriceEntity>("SELECT * FROM stockprice ");
this.dgvStockPrice.DataSource = stockprice;
// チャート生成
CreateStockChart(stockprice);
}
}
private void btnNumbering_Click(object sender, EventArgs e)
{
// 情報の取得
List <Utility.StockPriceEntity> listStock = new List <Utility.StockPriceEntity>();
List <Utility.NumberingPriceEntity> listNum = new List <Utility.NumberingPriceEntity>();
using (Utility.DbUtil db = new Utility.DbUtil())
{
listStock = db.DBSelect <StockPriceEntity>(@"SELECT
stockcode.StockCode
,ifnull(profile.CompanyName,'') CompanyName
FROM stockcode
LEFT JOIN profile ON stockcode.StockCode = profile.StockCode
ORDER BY stockcode.StockCode ");
db.DBExecuteSQL("DELETE FROM pricenumbering");
}
StringBuilder sb = new StringBuilder();
sb.Length = 0;
sb.AppendLine(" SELECT ");
sb.AppendLine(" StockCode ");
sb.AppendLine(" ,CompanyName ");
sb.AppendLine(" ,StockDate ");
sb.AppendLine(" ,OpeningPrice ");
sb.AppendLine(" ,HighPrice ");
sb.AppendLine(" ,LowPrice ");
sb.AppendLine(" ,ClosingPrice ");
sb.AppendLine(" ,TradeVolume ");
sb.AppendLine(" ,AdjustmentClosingPrice ");
sb.AppendLine(" ,(SELECT COUNT(*) + 1 ");
sb.AppendLine(" FROM stockprice ");
sb.AppendLine(" WHERE StockCode = :StockCode ");
sb.AppendLine(" AND StockDate >= :StockDate ");
sb.AppendLine(" AND (StockDate < t.StockDate OR (StockDate = t.StockDate AND StockCode < t.StockCode )) ");
sb.AppendLine(" ) AS RowNum ");
sb.AppendLine(" FROM stockprice t ");
sb.AppendLine("WHERE t.StockCode = :StockCode ");
sb.AppendLine(" AND t.StockDate >= :StockDate ");
foreach (StockPriceEntity r in listStock)
{
using (Utility.DbUtil db = new Utility.DbUtil())
{
listNum = db.DBSelect <NumberingPriceEntity>(sb.ToString(), new { StockCode = r.StockCode, StockDate = DateTime.Now.AddMonths(-3).Date });
string insertSql = @"INSERT INTO pricenumbering
(
StockCode
,CompanyName
,StockDate
,RowNum
,OpeningPrice
,HighPrice
,LowPrice
,ClosingPrice
,TradeVolume
,AdjustmentClosingPrice
) VALUES (
:StockCode
,:CompanyName
,:StockDate
,:RowNum
,:OpeningPrice
,:HighPrice
,:LowPrice
,:ClosingPrice
,:TradeVolume
,:AdjustmentClosingPrice
)";
if (listNum.Count() > 0)
{
db.DBInsert(insertSql, listNum);
}
}
}
}