//根据交易号获取其中交易记录 public void Select(int deal, out DealListEntity DLE) { SQLiteCommand cmd = new SQLiteCommand(conn); cmd.CommandText = "select * from '" + user + "_DealList' where deal=@deal"; cmd.Parameters.Add(new SQLiteParameter("deal", deal)); SQLiteDataReader reader = cmd.ExecuteReader(); List<DealListEntity> DLEL = Package(reader); if (DLEL.Count != 0) DLE = DLEL.First(); else DLE = new DealListEntity(); }
//数据库DealList读写测试 public void DBDataControllerDealListTest() { File.Delete(Environment.CurrentDirectory + "\\Stock1.db"); DBDataController DBC = new DBDataController("user6", Environment.CurrentDirectory + "\\Stock1.db"); DB_ERROR DBE = DBC.GetLastError(); DealListEntity DLE = new DealListEntity(); DLE.id = "600001"; DLE.name = "邯郸钢铁"; DBC.DealListAdd(DLE);//存入一条数据 List<DealListEntity> DLEL; DBC.DealListReadAll(out DLEL);//读取 Assert.AreEqual(1, DLEL.Count); Assert.AreEqual(DLE.id, DLEL[0].id);//判断读取与存入的数据是否一致 Assert.AreEqual(DLE.name, DLEL[0].name); }
//修改数据 public void Update(DealListEntity DLE) { SQLiteCommand cmd = new SQLiteCommand(conn); cmd.CommandText = "update '" + user + "_DealList' set name=@name,id=@id,date=@date,type=@type,money=@money,number=@number,taxrate=@taxrate,commission=@commission,explain=@explain,remark=@remark where deal=@deal"; cmd.Parameters.Add(new SQLiteParameter("deal", DLE.deal)); cmd.Parameters.Add(new SQLiteParameter("name", DLE.name)); cmd.Parameters.Add(new SQLiteParameter("id", DLE.id)); cmd.Parameters.Add(new SQLiteParameter("date", DLE.date)); cmd.Parameters.Add(new SQLiteParameter("type", DLE.type)); cmd.Parameters.Add(new SQLiteParameter("money", DLE.money)); cmd.Parameters.Add(new SQLiteParameter("number", DLE.number)); cmd.Parameters.Add(new SQLiteParameter("taxrate", DLE.taxrate)); cmd.Parameters.Add(new SQLiteParameter("commission", DLE.commission)); cmd.Parameters.Add(new SQLiteParameter("explain", DLE.explain)); cmd.Parameters.Add(new SQLiteParameter("remark", DLE.remark)); cmd.ExecuteNonQuery(); }
//插入数据 public void Insert(DealListEntity DLE) { SQLiteCommand cmd = new SQLiteCommand(conn); cmd.CommandText = "insert into '" + user + "_DealList' values(@deal,@name,@id,@date,@type,@money,@number,@taxrate,@commission,@explain,@remark)"; cmd.Parameters.Add(new SQLiteParameter("deal", DLE.deal)); cmd.Parameters.Add(new SQLiteParameter("name", DLE.name)); cmd.Parameters.Add(new SQLiteParameter("id", DLE.id)); cmd.Parameters.Add(new SQLiteParameter("date", DLE.date)); cmd.Parameters.Add(new SQLiteParameter("type", DLE.type)); cmd.Parameters.Add(new SQLiteParameter("money", DLE.money)); cmd.Parameters.Add(new SQLiteParameter("number", DLE.number)); cmd.Parameters.Add(new SQLiteParameter("taxrate", DLE.taxrate)); cmd.Parameters.Add(new SQLiteParameter("commission", DLE.commission)); cmd.Parameters.Add(new SQLiteParameter("explain", DLE.explain)); cmd.Parameters.Add(new SQLiteParameter("remark", DLE.remark)); cmd.ExecuteNonQuery(); }
//根据交易号获取其中交易记录 public void Select(int deal, out DealListEntity DLE) { SQLiteCommand cmd = new SQLiteCommand(conn); cmd.CommandText = "select * from '" + user + "_DealList' where deal=@deal"; cmd.Parameters.Add(new SQLiteParameter("deal", deal)); SQLiteDataReader reader = cmd.ExecuteReader(); List <DealListEntity> DLEL = Package(reader); if (DLEL.Count != 0) { DLE = DLEL.First(); } else { DLE = new DealListEntity(); } }
//按类型转换 private StockHoldEntity DealList_Insert_StockHold(DealListEntity DLE) { if (DLE.deal >= dealid) dealid = DLE.deal + 1; StockHoldEntity SHE = new StockHoldEntity(); SHE.id = DLE.id; SHE.name = DLE.name; double c; if (DLE.type == "买入") { SHE.hold = DLE.number; c = -(DLE.number * DLE.money); MoneyChangeNow(c); } else if (DLE.type == "卖出") { SHE.hold = -DLE.number; c = DLE.number * DLE.money; MoneyChangeNow(c); } else if (DLE.type == "补仓") { SHE.hold = DLE.number; c = -(DLE.number * DLE.money); MoneyChangeNow(c); } else if (DLE.type == "卖空") { SHE.hold = -DLE.number; c = DLE.number * DLE.money; MoneyChangeNow(c); } else { SHE.hold = DLE.number; c = -(DLE.number * DLE.money); MoneyChangeNow(c); } SHE.money = -c; StockHoldEntity SHE_=new StockHoldEntity(); SHE_.id = SHE.id; stockhold.Select(ref SHE_); HistoryStockHoldEntity HSHE = new HistoryStockHoldEntity(); HSHE.id = SHE.id; HSHE.number = SHE_.hold; HSHE.date = DLE.date; HSHE.change = SHE.hold; HSHE.money = DLE.money; historystockhold.Insert(HSHE); if (SHE_.name != null) { SHE.hold = SHE.hold + SHE_.hold; SHE.money = Convert.ToDouble(SHE.money) + Convert.ToDouble(SHE_.money); stockhold.Update(SHE); } else { stockhold.Insert(SHE); } return SHE; //StockHoldSet(SHE); }
//重置id相关 private void DealListRestore(DealListEntity DLE) { List<DealListEntity> DLEL; deallist.Select(DLE.id, out DLEL); StockHoldEntity SHE = new StockHoldEntity(); SHE.id = DLE.id; stockhold.Select(ref SHE); MoneyChangeNow(SHE.money); stockhold.Delete(DLE.id); historystockhold.Delete(DLE.id); if (DLEL.Count == 0) { SHE.hold = 0; SHE.id = DLE.id; SHE.money = 0; SHE.name = DLE.name; StockHoldSet(SHE); } foreach (DealListEntity D in DLEL) { SHE = DealList_Insert_StockHold(D); } StockHoldSet(SHE); MoneyReadSet(); }
//删除交易记录 public void DealListDelete(DealListEntity DLE) { deallist.Delate(DLE.deal); DealListRestore(DLE); }
//修改交易记录 public void DealListUpdate(DealListEntity DLE) { deallist.Update(DLE); DealListRestore(DLE); }
//增加交易记录 public void DealListAdd(DealListEntity DLE) { DLE.deal = dealid; deallist.Insert(DLE); StockHoldSet(DealList_Insert_StockHold(DLE)); MoneyReadSet(); }
//删除交易记录 public void DealListDelete(DealListEntity DLE) { ThreadPool.QueueUserWorkItem(DealListDeleteThread, DLE); }
//增加交易记录 public void DealListAdd(DealListEntity DLE) { ThreadPool.QueueUserWorkItem(DealListAddThread, DLE); }
public OPENEXCEL_ERROR Open(string FilePath, out List<DealListEntity> DLEL) { //Thread.Sleep(10000); DLEL = new List<DealListEntity>(); String sConn; if (FilePath.Last() == 's') { sConn = OleStrCon(".xls", FilePath, true); } else { sConn = OleStrCon(".xlsx", FilePath, true); } DataSet ds = new DataSet(); string[] column; //OLE读取excel try { using (OleDbConnection ole_conn = new OleDbConnection(sConn)) { try { ole_conn.Open();} catch (Exception) { return OPENEXCEL_ERROR.OLE_ERROR;//XLSX没安装Microsoft.Ace.OleDb.12.0则会报错,XLS没安装Microsoft.Jet.OLEDB.4.0会报错 } using (OleDbCommand ole_cmd = ole_conn.CreateCommand()) { System.Data.DataTable tableColumns = ole_conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, "Sheet1$", null }); string all_column = ""; int column_count = tableColumns.Rows.Count; column = new string[column_count]; for (int i = 0; i < column_count; i++)//读出所有的列名 { column[i] = tableColumns.Rows[i]["Column_Name"].ToString(); all_column += string.Format("{0,-10}|", column[i]); } ole_cmd.CommandText = "select * from [Sheet1$]"; OleDbDataAdapter adapter = new OleDbDataAdapter(ole_cmd); adapter.Fill(ds, "Sheet1"); } } } catch(OleDbException) { return OPENEXCEL_ERROR.OLE_ERROR; } List<DealListEntity> DLELNoSort = new List<DealListEntity>(); bool FORMAT = true; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DealListEntity DLE = new DealListEntity(); int count = 0; for (int j = 0; j < column.Length; j++) { string s; int k; count++;//数据必须存在以下需要的十列,其他多余的列不作处理 Type t = ds.Tables[0].Rows[i][column[j]].GetType(); switch(column[j]) { case "价格": if (t == DLE.money.GetType()) DLE.money = Convert.ToDouble(ds.Tables[0].Rows[i][column[j]]); else count--; break; case "佣金": s = ds.Tables[0].Rows[i][column[j]].ToString(); k = s.IndexOf('‰'); if (k > 0) DLE.commission = Convert.ToDouble(s.Substring(0, k)); else count--; break; case "备注": DLE.remark = ds.Tables[0].Rows[i][column[j]].ToString(); break; case "数量": int num; if (Int32.TryParse(ds.Tables[0].Rows[i][column[j]].ToString(), out num) == true) DLE.number = num; else count--; break; case "日期": if (t == DLE.date.GetType()) DLE.date = Convert.ToDateTime(ds.Tables[0].Rows[i][column[j]]); else count--; break; case "税率": s = ds.Tables[0].Rows[i][column[j]].ToString(); k = s.IndexOf('‰'); if (k > 0) DLE.taxrate = Convert.ToDouble(s.Substring(0, k)); else count--; break; case "类型": DLE.type = ds.Tables[0].Rows[i][column[j]].ToString(); break; case "股票名称": DLE.name = ds.Tables[0].Rows[i][column[j]].ToString(); break; case "股票编号": int stock; if (Int32.TryParse(ds.Tables[0].Rows[i][column[j]].ToString(), out stock) == true) DLE.id = stock.ToString().PadLeft(6, '0'); else count--; break; case "说明": DLE.explain = ds.Tables[0].Rows[i][column[j]].ToString(); break; default: count--; break; } } if (count == 10) { if (DLE.name != "" && DLE.id != "" && DLE.id.Length == 6)//股票名称和长度不对的行不保存 DLELNoSort.Add(DLE); } else FORMAT = false; } List<DealListEntity> DLELNoSortByDate = new List<DealListEntity>(); DLELNoSortByDate.AddRange(DLELNoSort.Where(s => s.type == "买入").ToList()); DLELNoSortByDate.AddRange(DLELNoSort.Where(s => s.type == "卖空").ToList()); DLELNoSortByDate.AddRange(DLELNoSort.Where(s => s.type == "卖出").ToList()); DLELNoSortByDate.AddRange(DLELNoSort.Where(s => s.type == "补仓").ToList()); DLEL = DLELNoSortByDate.OrderBy(s => s.date).ToList();//排序 if (FORMAT == false) return OPENEXCEL_ERROR.FORMAT_ERROR;//缺少相应的列 return OPENEXCEL_ERROR.OPEN_OK; }
//数据库StockHold读测试 public void DBDataControllerStockHoldTest() { File.Delete(Environment.CurrentDirectory + "\\Stock4.db"); DBDataController DBC = new DBDataController("user9", Environment.CurrentDirectory + "\\Stock4.db"); DB_ERROR DBE = DBC.GetLastError(); DealListEntity DLE = new DealListEntity(); DLE.id = "600001"; DLE.name = "邯郸钢铁"; DLE.money = 10.11; DLE.number = 400; DLE.type = "买入"; DBC.DealListAdd(DLE);//存入一条数据 DLE.id = "600001"; DLE.name = "邯郸钢铁"; DLE.money = 5.11; DLE.number = 200; DLE.type = "卖出"; DBC.DealListAdd(DLE);//再存入一条数据 List<StockHoldEntity> SHEL; DBC.StockHoldReadAll(out SHEL); Assert.AreEqual(1, SHEL.Count); Assert.AreEqual(DLE.id, SHEL[0].id);//判断读取与存入的数据是否一致 Assert.AreEqual(DLE.name, SHEL[0].name); Assert.AreEqual(200, SHEL[0].hold);//运算结果 Assert.AreEqual(3022, SHEL[0].money);//运算结果 }