//根据交易号获取其中交易记录
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();
}
}
//修改数据
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();
}
//按类型转换
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);//运算结果
}
