//重置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 AddDealList(string user, int deal)
{
InitializeComponent();
this.titlename.Text = "修改交易";
add.Visibility = Visibility.Hidden;
ComboBoxItem cbi = new ComboBoxItem();
cbi.Content = user;
this.user.Items.Add(cbi);
this.user.SelectedIndex = 0;
this.user.IsEnabled = false;
DealListEntity DLE = UserPanelController.Handler().DBControllerByName(user).DealListReadByDeal(deal);
this.deal = DLE.deal;
name.Text = DLE.name;
id.Text = DLE.id;
date.Text = DLE.date.ToString();
type.Text = DLE.type;
money.Text = DLE.money.ToString();
number.Text = DLE.number.ToString();
taxrate.Text = DLE.taxrate.ToString() + "‰";
commission.Text = DLE.commission.ToString() + "‰";
explain.Text = DLE.explain;
remark.Text = DLE.remark;
}
//数据库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); //运算结果
}
//批量增加交易记录
public void DealListAdd(List <DealListEntity> DLEL)
{
Dictionary <string, StockHoldEntity> dict = new Dictionary <string, StockHoldEntity>();
foreach (DealListEntity DLE in DLEL)
{
DealListEntity D = DLE;
D.deal = dealid;
deallist.Insert(D);
StockHoldEntity SHE = DealList_Insert_StockHold(D);
if (dict.ContainsKey(SHE.id))
{
dict[SHE.id] = SHE;
}
else
{
dict.Add(SHE.id, SHE);
}
}
foreach (var x in dict)
{
StockHoldSet(x.Value);
}
MoneyReadSet();
}
//增加交易记录
public void DealListAdd(DealListEntity DLE)
{
DLE.deal = dealid;
deallist.Insert(DLE);
StockHoldSet(DealList_Insert_StockHold(DLE));
MoneyReadSet();
}
//数据库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);
}
private void DList_MouseDoubleClick(object sender, MouseButtonEventArgs e)
{
object o = DList.SelectedItem;
if (o == null)
{
return;
}
ItemData item = o as ItemData;
AddDealList adl = new AddDealList(user.Text, item.deal);
adl.ShowDialog();
DealListEntity DLE = UserPanelController.Handler().DBControllerByName(user.Text).DealListReadByDeal(item.deal);
int index = DList.SelectedIndex;
DList.Items.Remove(item);
if (DLE.id == item.id)
{
item = new ItemData(DLE.deal, DLE.name, DLE.id, DLE.date.ToString("yyyy/MM/dd"), DLE.type, DLE.money.ToString(), DLE.number.ToString(), DLE.taxrate.ToString(), DLE.commission.ToString(), DLE.explain, DLE.remark);
DList.Items.Insert(index, item);
}
DList.UpdateLayout();
//MessageBox.Show("暂时未提供修改功能!");
}
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);
}
//删除交易记录
public void DealListDelete(DealListEntity DLE)
{
ThreadPool.QueueUserWorkItem(DealListDeleteThread, DLE);
}
//增加交易记录
public void DealListAdd(DealListEntity DLE)
{
ThreadPool.QueueUserWorkItem(DealListAddThread, DLE);
}
//按类型转换
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);
}
//删除交易记录
public void DealListDelete(DealListEntity DLE)
{
deallist.Delate(DLE.deal);
DealListRestore(DLE);
}
//修改交易记录
public void DealListUpdate(DealListEntity DLE)
{
deallist.Update(DLE);
DealListRestore(DLE);
}