private void btnButHisroty_Click(object sender, EventArgs e)
{
DBTransactionEntities dc = new DBTransactionEntities();
dataGridView1.DataSource = dc.Buyhistory.ToArray();
dataGridView1.Columns["totalcost"].DefaultCellStyle.Format = "c0";
}
private void FrmAllTable_Load(object sender, EventArgs e)
{
DBTransactionEntities dc = new DBTransactionEntities();
var queryTransaction = dc.Transaction_history.AsParallel().Select(od => new
{
日期 = od.date,
股號 = od.stockid,
庫存 = od.amount,
買賣 = od.buysell,
價格 = od.price,
數量 = od.amount,
益 = od.netincome,
}).OrderBy(d => d.日期);
this.dataGridView1.DataSource = queryTransaction.ToArray();
dataGridView1.Columns["價格"].DefaultCellStyle.Format = "c2";
dataGridView1.Columns["價格"].Width = 150;
dataGridView1.Columns["損益"].DefaultCellStyle.Format = "c0";
dataGridView1.Columns["損益"].Width = 150;
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("新細明體", 12, FontStyle.Bold);
dataGridView1.DefaultCellStyle.Font = new Font("Times New Roman", 12, FontStyle.Regular);
var querybuy = dc.Buyhistory.AsParallel().Select(od => new
{
日期 = od.date,
股號 = od.stockid,
買進價格 = od.buyprice,
買進數量 = od.buyamount,
買進總成本 = od.totalcost,
平價格 = od.BEpoint,
預期報酬 = od.ERate,
目標價 = od.TP,
筆記 = od.Note
}).OrderBy(d => d.日期);
dataGridView2.DataSource = querybuy.ToArray();
//dataGridView1.Columns["Id"].Visible = false;
dataGridView2.Columns["買進總成本"].DefaultCellStyle.Format = "c0";
dataGridView2.ColumnHeadersDefaultCellStyle.Font = new Font("新細明體", 12, FontStyle.Bold);
dataGridView2.Columns["買進總成本"].Width = 120;
dataGridView2.DefaultCellStyle.Font = new Font("Times New Roman", 12, FontStyle.Regular);
var querysell = dc.Sellhistory.AsParallel().Select(od => new
{
日期 = od.date,
股號 = od.stockid,
賣出價格 = od.Sellprice,
賣出數量 = od.Sellamount,
獲利 = od.totalprofit,
報酬率 = od.ROI,
筆記 = od.Note
}).OrderBy(d => d.日期);
dataGridView3.DataSource = querysell.ToArray();
dataGridView3.Columns["獲利"].DefaultCellStyle.Format = "c0";
dataGridView3.Columns["報酬率"].DefaultCellStyle.Format = "P2";
dataGridView3.ColumnHeadersDefaultCellStyle.Font = new Font("新細明體", 12, FontStyle.Bold);
dataGridView3.DefaultCellStyle.Font = new Font("Times New Roman", 12, FontStyle.Regular);
}
private void BtnSellHistory_Click(object sender, EventArgs e)
{
DBTransactionEntities dc = new DBTransactionEntities();
dataGridView1.DataSource = dc.Sellhistory.ToArray();
dataGridView1.Columns["Id"].Visible = false;
dataGridView1.Columns["totalprofit"].DefaultCellStyle.Format = "c0";
}
private void button2_Click(object sender, EventArgs e)
{
int stockid = int.Parse(this.txtstockid2.Text);
DBTransactionEntities dc = new DBTransactionEntities();
decimal instock = 0;
instock = dc.Transaction_history.AsParallel().Where(s => s.stockid == stockid).Sum(a => a.amount).Value;
if (instock != 0)
{
this.labelInstock.Text = dc.Transaction_history.AsParallel().Where(s => s.stockid == stockid).Sum(a => a.amount).ToString();
decimal temp = (dc.Transaction_history.Where(s => s.stockid == stockid && s.buysell == true).Sum(c => c.netincome) / dc.Transaction_history.Where(s => s.stockid == stockid && s.buysell == true).Sum(c => c.amount) / -1000).Value;
this.labelavgcost.Text = temp.ToString("c2");
avgcost = double.Parse((dc.Transaction_history.Where(s => s.stockid == stockid && s.buysell == true).Sum(c => c.netincome) / dc.Transaction_history.Where(s => s.stockid == stockid && s.buysell == true).Sum(c => c.amount) / -1000).ToString());
//=====歷史買進表=====
var querybuy = dc.Buyhistory.AsParallel().Where(s => s.stockid == stockid).Select(od => new
{
日期 = od.date,
股號 = od.stockid,
買進價格 = od.buyprice,
買進數量 = od.buyamount,
買進總成本 = od.totalcost,
平價格 = od.BEpoint,
預期報酬 = od.ERate,
目標價 = od.TP,
筆記 = od.Note
});
this.dataGridView1.DataSource = querybuy.ToArray();
dataGridView1.Columns["買進總成本"].DefaultCellStyle.Format = "c0";
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("微軟正黑體", 10, FontStyle.Regular);
dataGridView1.Columns["買進總成本"].Width = 120;
}
//無庫存，顯示庫存個股
else
{
var querybuy = dc.Buyhistory.AsParallel().Select(od => new
{
日期 = od.date,
股號 = od.stockid,
買進價格 = od.buyprice,
買進數量 = od.buyamount,
買進總成本 = od.totalcost,
平價格 = od.BEpoint,
預期報酬 = od.ERate,
目標價 = od.TP,
筆記 = od.Note
});
this.dataGridView1.DataSource = querybuy.ToArray();
dataGridView1.Columns["買進總成本"].DefaultCellStyle.Format = "c0";
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("微軟正黑體", 10, FontStyle.Regular);
dataGridView1.Columns["買進總成本"].Width = 120;
MessageBox.Show("目前無庫存資訊");
}
}
//連線方法input買進資料
private void btnBuyInput_Click(object sender, EventArgs e)
{
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = Settings.Default.DBTransactionConnectionString;
using (SqlCommand command = new SqlCommand())
{
command.CommandText = "Insert into Buyhistory(buyprice,buyamount,totalcost,BEpoint,stockid,date) values (@price, @amount,@cost,@BEpoint,@id,@date)";
command.Connection = conn;
//=============================
//==============================
command.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(this.textBox2.Text);
command.Parameters.Add("@price", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtBuyPrice.Text);
command.Parameters.Add("@amount", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtBuyAmount.Text);
command.Parameters.Add("@cost", SqlDbType.Int).Value = _totalcost;
command.Parameters.Add("@BEpoint", SqlDbType.Decimal, 6).Value = _bepoint;
command.Parameters.Add("@date", SqlDbType.SmallDateTime, 6).Value = this.dateTimePicker1.Value.ToShortDateString();
conn.Open();
command.ExecuteNonQuery();
MessageBox.Show("Insert member successfully");
} //auto command.Dispose()
} //auto conn.Close(); conn.Dispose()
//重新整理datagridview
DBTransactionEntities dc = new DBTransactionEntities();
dataGridView1.DataSource = dc.Buyhistory.ToArray();
dataGridView1.Columns["Id"].Visible = false;
dataGridView1.Columns["totalcost"].DefaultCellStyle.Format = "c0";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void FrmMain_Load(object sender, EventArgs e)
{
//=====庫存狀況=====
DBTransactionEntities dc = new DBTransactionEntities();
var query = dc.Transaction_history.AsParallel().GroupBy(c => c.stockid).Select(od => new
{
股號 = od.Key,
庫存 = od.Sum(s => s.amount),
平均每股成本 = od.Where(bs => bs.buysell == true).Sum(c => c.netincome * (-1)) / od.Where(bs => bs.buysell == true).Sum(a => a.amount) / 1000,
總成本 = od.Where(bs => bs.buysell == true).Sum(c => c.netincome * (-1))
});
// var query = dc.Transaction_history.Select(c=>c).ToString();
this.dataGridView1.DataSource = query.ToArray();
dataGridView1.Columns["平均每股成本"].DefaultCellStyle.Format = "c2";
dataGridView1.Columns["平均每股成本"].Width = 150;
dataGridView1.Columns["總成本"].DefaultCellStyle.Format = "c0";
dataGridView1.Columns["總成本"].Width = 150;
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("微軟正黑體", 12, FontStyle.Bold);
dataGridView1.DefaultCellStyle.Font = new Font("Times New Roman", 12, FontStyle.Regular);
//=====RSS Reader=====
XDocument rssFeed = XDocument.Load("https://tw.stock.yahoo.com/rss/url/d/e/N1.html");
var queryrss = from item in rssFeed.Descendants("item").AsParallel()
select new
{
標題 = item.Element("title").Value,
發布時間 = item.Element("pubDate").Value,
link = item.Element("link").Value,
};
dataGridView2.DataSource = queryrss.ToList();
this.dataGridView2.Columns["標題"].Width = 350;
this.dataGridView2.Columns["發布時間"].Width = 200;
dataGridView2.ColumnHeadersDefaultCellStyle.Font = new Font("微軟正黑體", 12, FontStyle.Bold);
}
//限制輸入條件
private void Form1_Load(object sender, EventArgs e)
{
DBTransactionEntities dc = new DBTransactionEntities();
var querybuy = dc.Buyhistory.AsParallel().Select(od => new
{
日期 = od.date,
股號 = od.stockid,
買進價格 = od.buyprice,
買進數量 = od.buyamount,
買進總成本 = od.totalcost,
平價格 = od.BEpoint,
預期報酬 = od.ERate,
目標價 = od.TP,
筆記 = od.Note
});
dataGridView1.DataSource = querybuy.ToArray();
//dataGridView1.Columns["Id"].Visible = false;
dataGridView1.Columns["買進總成本"].DefaultCellStyle.Format = "c0";
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("微軟正黑體", 10, FontStyle.Bold);
dataGridView1.Columns["買進總成本"].Width = 120;
txtBuyPrice.MaxLength = 7;
}
private void btnSellInput_Click(object sender, EventArgs e)
{
if (this.txtNetIncome.Text != "")
{
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = Settings.Default.DBTransactionConnectionString;
using (SqlCommand command = new SqlCommand())
{
command.CommandText = "Insert into Sellhistory(Sellprice,Sellamount,totalprofit,ROI,stockid,date,Note) values (@price, @amount,@profit,@ROI,@id,@date,@Note)";
command.Connection = conn;
//=============================
//==============================
command.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(this.txtstockid2.Text);
command.Parameters.Add("@price", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtSellPrice.Text);
command.Parameters.Add("@amount", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtSellAmount.Text);
command.Parameters.Add("@profit", SqlDbType.Int).Value = (int)_netincome;
command.Parameters.Add("@ROI", SqlDbType.Decimal, 6).Value = (decimal)_ROI;
command.Parameters.Add("@date", SqlDbType.SmallDateTime, 6).Value = this.dateTimePicker2.Value.ToShortDateString();
command.Parameters.Add("@Note", SqlDbType.NVarChar, 6).Value = this.textBox1.Text;
conn.Open();
command.ExecuteNonQuery();
//===================================
command.CommandText = "Insert into [Transaction history](stockid,buysell,price,amount,netincome,date) values (@id2, @buysell,@price2,@amount2,@netincome,@date2)";
//==============================
command.Parameters.Add("@id2", SqlDbType.Int).Value = int.Parse(this.txtstockid2.Text);
command.Parameters.Add("@buysell", SqlDbType.Bit).Value = 0;
command.Parameters.Add("@price2", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtSellPrice.Text);
command.Parameters.Add("@amount2", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtSellAmount.Text) * (-1);
command.Parameters.Add("@netincome", SqlDbType.Int).Value = _profit;
command.Parameters.Add("@date2", SqlDbType.SmallDateTime, 6).Value = this.dateTimePicker2.Value.ToShortDateString();
//conn.Open();
command.ExecuteNonQuery();
MessageBox.Show("Insert data successfully");
} //auto command.Dispose()
} //auto conn.Close(); conn.Dispose()
//重新整理datagridview
DBTransactionEntities dc = new DBTransactionEntities();
var querysell = dc.Sellhistory.AsParallel().Select(od => new
{
日期 = od.date,
股號 = od.stockid,
賣出價格 = od.Sellprice,
賣出數量 = od.Sellamount,
獲利 = od.totalprofit,
報酬率 = od.ROI,
筆記 = od.Note
});
dataGridView2.DataSource = querysell.ToArray();
dataGridView2.Columns["獲利"].DefaultCellStyle.Format = "c0";
dataGridView2.Columns["報酬率"].DefaultCellStyle.Format = "P2";
dataGridView2.Columns["獲利"].DefaultCellStyle.Format = "c0";
dataGridView2.ColumnHeadersDefaultCellStyle.Font = new Font("微軟正黑體", 10, FontStyle.Regular);
dataGridView2.Columns["獲利"].Width = 120;
//更新frmmain庫存狀況
var query = dc.Transaction_history.GroupBy(c => c.stockid).Select(od => new
{
股號 = od.Key,
庫存 = od.Sum(s => s.amount),
平均每股成本 = od.Where(bs => bs.buysell == true).Sum(c => c.netincome * (-1)) / od.Where(bs => bs.buysell == true).Sum(a => a.amount) / 1000,
總成本 = od.Where(bs => bs.buysell == true).Sum(c => c.netincome * (-1))
});
f1.dataGridView1.DataSource = query.ToArray();
f1.dataGridView1.Columns["平均每股成本"].DefaultCellStyle.Format = "c2";
f1.dataGridView1.Columns["平均每股成本"].Width = 150;
f1.dataGridView1.Columns["總成本"].DefaultCellStyle.Format = "c0";
f1.dataGridView1.Columns["總成本"].Width = 150;
f1.dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("微軟正黑體", 12, FontStyle.Regular);
f1.dataGridView1.DefaultCellStyle.Font = new Font("Times New Roman", 12, FontStyle.Regular);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("請先查詢庫存及成本，並計算報酬!");
}
}
private void btnBuyInput_Click(object sender, EventArgs e)
{
if (txtBEPoint.Text != "")
{
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = Settings.Default.DBTransactionConnectionString;
using (SqlCommand command = new SqlCommand())
{
//===========輸入買進表========================
command.CommandText = "Insert into Buyhistory(buyprice,buyamount,totalcost,BEpoint,ERate,TP,stockid,date,Note) values (@price, @amount,@cost,@BEpoint,@ERate,@TP,@id,@date,@Note)";
command.Connection = conn;
//==============================
command.Parameters.Add("@id", SqlDbType.Int).Value = int.Parse(this.txtStockID.Text);
command.Parameters.Add("@price", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtBuyPrice.Text);
//command.Parameters.Add("@price", SqlDbType.Decimal, 6).Value = (decimal) _buyprice;
command.Parameters.Add("@amount", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtBuyAmount.Text);
//command.Parameters.Add("@amount", SqlDbType.Decimal, 6).Value = (decimal)_buyamount;
command.Parameters.Add("@cost", SqlDbType.Int).Value = _totalcost;
//command.Parameters.Add("@cost", SqlDbType.Int).Value = (decimal)_totalcost;
command.Parameters.Add("@BEpoint", SqlDbType.Decimal, 6).Value = (decimal)_bepoint;
command.Parameters.Add("@ERate", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtER.Text) / 100;
command.Parameters.Add("@TP", SqlDbType.Decimal, 6).Value = (decimal)_tp;
command.Parameters.Add("@date", SqlDbType.SmallDateTime, 6).Value = this.dateTimePicker1.Value.ToShortDateString();
command.Parameters.Add("@Note", SqlDbType.NVarChar, 6).Value = this.textBox1.Text;
conn.Open();
command.ExecuteNonQuery();
//===========輸入總表========================
command.CommandText = "Insert into [Transaction history](stockid,buysell,price,amount,netincome,date) values (@id2, @buysell,@price2,@amount2,@netincome,@date2)";
//==============================
command.Parameters.Add("@id2", SqlDbType.Int).Value = int.Parse(this.txtStockID.Text);
command.Parameters.Add("@buysell", SqlDbType.Bit).Value = 1;
command.Parameters.Add("@price2", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtBuyPrice.Text);
command.Parameters.Add("@amount2", SqlDbType.Decimal, 6).Value = decimal.Parse(this.txtBuyAmount.Text);
command.Parameters.Add("@netincome", SqlDbType.Int).Value = _totalcost * (-1);
command.Parameters.Add("@date2", SqlDbType.SmallDateTime, 6).Value = this.dateTimePicker1.Value.ToShortDateString();
command.ExecuteNonQuery();
MessageBox.Show("Insert data successfully");
} //auto command.Dispose()
} //auto conn.Close(); conn.Dispose()
//重新整理datagridview
DBTransactionEntities dc = new DBTransactionEntities();
var querybuy = dc.Buyhistory.AsParallel().Select(od => new
{
日期 = od.date,
股號 = od.stockid,
買進價格 = od.buyprice,
買進數量 = od.buyamount,
買進總成本 = od.totalcost,
平價格 = od.BEpoint,
預期報酬 = od.ERate,
目標價 = od.TP,
筆記 = od.Note
});
dataGridView1.DataSource = querybuy.ToArray();
//dataGridView1.Columns["Id"].Visible = false;
dataGridView1.Columns["買進總成本"].DefaultCellStyle.Format = "c0";
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new Font("微軟正黑體", 10, FontStyle.Bold);
dataGridView1.Columns["買進總成本"].Width = 120;
//更新frmmain庫存狀況
var query = dc.Transaction_history.GroupBy(c => c.stockid).Select(od => new
{
股號 = od.Key,
庫存 = od.Sum(s => s.amount),
平均成本 = od.Where(bs => bs.buysell == true).Sum(c => c.netincome * (-1)) / od.Where(bs => bs.buysell == true).Sum(a => a.amount) / 1000
});
f1.dataGridView1.DataSource = query.ToArray();
f1.dataGridView1.Columns["平均成本"].DefaultCellStyle.Format = "c2";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else
{
MessageBox.Show("請先計算成本及預期報酬!");
}
}
