Пример #1
0
 //根据交易号获取其中交易记录
 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();
 }
Пример #2
0
 //数据库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);
 }
Пример #3
0
 //修改数据
 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();
 }
Пример #4
0
 //插入数据
 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();
 }
Пример #5
0
        //根据交易号获取其中交易记录
        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();
            }
        }
Пример #6
0
        //修改数据
        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();
        }
Пример #7
0
        //插入数据
        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();
        }
Пример #8
0
 //按类型转换
 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);
 }
Пример #9
0
 //重置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();
 }
Пример #10
0
 //删除交易记录
 public void DealListDelete(DealListEntity DLE)
 {
     deallist.Delate(DLE.deal);
     DealListRestore(DLE);
 }
Пример #11
0
 //修改交易记录
 public void DealListUpdate(DealListEntity DLE)
 {
     deallist.Update(DLE);
     DealListRestore(DLE);
 }
Пример #12
0
 //增加交易记录
 public void DealListAdd(DealListEntity DLE)
 {
     DLE.deal = dealid;
     deallist.Insert(DLE);
     StockHoldSet(DealList_Insert_StockHold(DLE));
     MoneyReadSet();
 }
Пример #13
0
 //删除交易记录
 public void DealListDelete(DealListEntity DLE)
 {
     ThreadPool.QueueUserWorkItem(DealListDeleteThread, DLE);
 }
Пример #14
0
 //增加交易记录
 public void DealListAdd(DealListEntity DLE)
 {
     ThreadPool.QueueUserWorkItem(DealListAddThread, DLE);
 }
Пример #15
0
 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;
 }
Пример #16
0
 //数据库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);//运算结果
 }