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("請先計算成本及預期報酬!"); } }