Пример #1
0
        /// <summary>
        /// 更新一条记录
        /// </summary>
        /// <param name="record">记录信息</param>
        public int UpdateRecord(Model.HkRecord record, long oldId)
        {
            string strUpdate = "update [LuHeRecord] " +
                               "set [id]=@id " +
                               "[record_date]=@record_date" +
                               ",[times]=@times" +
                               ",[first_num]=@first_num" +
                               ",[second_num]=@second_num" +
                               ",[third_num]=@third_num" +
                               ",[fourth_num]=@fourth_num" +
                               ",[fifth_num]=@fifth_num" +
                               ",[sixth_num]=@sixth_num" +
                               ",[seventh_num]=@seventh_num " +
                               "where [id]=@oldId";

            SqlParameter[] pars = new SqlParameter[11];
            pars[0]  = new SqlParameter("@record_date", record.RecordDate);
            pars[1]  = new SqlParameter("@times", record.Times);
            pars[2]  = new SqlParameter("@first_num", record.FirstNum);
            pars[3]  = new SqlParameter("@second_num", record.SecondNum);
            pars[4]  = new SqlParameter("@third_num", record.ThirdNum);
            pars[5]  = new SqlParameter("@fourth_num", record.FourthNum);
            pars[6]  = new SqlParameter("@fifth_num", record.FifthNum);
            pars[7]  = new SqlParameter("@sixth_num", record.SixthNum);
            pars[8]  = new SqlParameter("@seventh_num", record.SeventhNum);
            pars[9]  = new SqlParameter("@id", record.Id);
            pars[10] = new SqlParameter("@oldId", oldId);
            DBUtility.SQLServerHelper helper = new DBUtility.SQLServerHelper();
            return(helper.RunSQL(strUpdate, pars));
        }
Пример #2
0
        /// <summary>
        /// 按期次查询单条记录
        /// </summary>
        /// <param name="times"></param>
        /// <returns></returns>
        public Model.HkRecord GetDataByTimes(string times)
        {
            //查询语句
            string strSelect = "select * from [LuHeRecord] where [times]=@times";

            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@times", times);
            //记录对象
            Model.HkRecord record = new Model.HkRecord();
            //数据读取对象
            SqlDataReader read;

            DBUtility.SQLServerHelper helper = new DBUtility.SQLServerHelper();
            //执行查询
            helper.RunSQL(strSelect, pars, out read);
            while (read.Read())
            {
                record.Id         = read.GetInt64(0);
                record.RecordDate = read.GetDateTime(1);
                record.Times      = read.GetString(2);
                record.FirstNum   = read.GetByte(3);
                record.SecondNum  = read.GetByte(4);
                record.ThirdNum   = read.GetByte(5);
                record.FourthNum  = read.GetByte(6);
                record.FifthNum   = read.GetByte(7);
                record.SixthNum   = read.GetByte(8);
                record.SeventhNum = read.GetByte(9);
            }
            //返回记录对象
            return(record);
        }
Пример #3
0
        /// <summary>
        /// 取某一日期范围的记录
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public List <Model.HkRecord> GetDataByRecordDate(DateTime start, DateTime end)
        {
            string strSelect =
                @"SELECT [id]
                    ,[record_date]
                    ,[times]
                    ,[first_num]
                    ,[second_num]
                    ,[third_num]
                    ,[fourth_num]
                    ,[fifth_num]
                    ,[sixth_num]
                    ,[seventh_num]
                FROM [db_record].[dbo].[LuHeRecord]
                WHERE [record_date] <=@start AND [record_date]>=@end
                ORDER BY [record_date] ASC";
            SqlDataReader dr;

            SqlParameter[] pars = new SqlParameter[2];
            pars[0] = new SqlParameter("@start", start);
            pars[1] = new SqlParameter("@end", end);
            DBUtility.SQLServerHelper helper = new DBUtility.SQLServerHelper();
            helper.RunSQL(strSelect, pars, out dr);
            List <Model.HkRecord> lRecord = new List <Model.HkRecord>();

            while (dr.Read())
            {
                Model.HkRecord record = new Model.HkRecord();
                record.Id         = dr.GetInt64(0);
                record.RecordDate = dr.GetDateTime(1);
                record.Times      = dr.GetString(2);
                record.FirstNum   = dr.GetByte(3);
                record.SecondNum  = dr.GetByte(4);
                record.ThirdNum   = dr.GetByte(5);
                record.FourthNum  = dr.GetByte(6);
                record.FifthNum   = dr.GetByte(7);
                record.SixthNum   = dr.GetByte(8);
                record.SeventhNum = dr.GetByte(9);
                lRecord.Add(record);
            }
            return(lRecord);
        }
Пример #4
0
        /// 取最大记录日期记录
        /// </summary>
        /// <returns></returns>
        public Model.HkRecord GetMaxRecord()
        {
            //查询语句
            string strSelect =
                @"SELECT TOP 1 [id]
                        ,[record_date]
                        ,[times]
                        ,[first_num]
                        ,[second_num]
                        ,[third_num]
                        ,[fourth_num]
                        ,[fifth_num]
                        ,[sixth_num]
                        ,[seventh_num]
                    FROM [LuHeRecord]
                    ORDER BY [record_date] DESC";

            //记录列表
            Model.HkRecord record = new Model.HkRecord();
            //数据读取对象
            SqlDataReader read;

            DBUtility.SQLServerHelper helper = new DBUtility.SQLServerHelper();
            helper.RunSQL(strSelect, out read);
            if (read.Read())
            {
                record.Id         = read.GetInt64(0);
                record.RecordDate = read.GetDateTime(1);
                record.Times      = read.GetString(2);
                record.FirstNum   = read.GetByte(3);
                record.SecondNum  = read.GetByte(4);
                record.ThirdNum   = read.GetByte(5);
                record.FourthNum  = read.GetByte(6);
                record.FifthNum   = read.GetByte(7);
                record.SixthNum   = read.GetByte(8);
                record.SeventhNum = read.GetByte(9);
            }
            return(record);
        }
Пример #5
0
        /// <summary>
        /// 查询所有记录
        /// </summary>
        /// <returns></returns>
        public List <Model.HkRecord> GetData()
        {
            //查询语句
            string strSelect =
                @"SELECT [id]
                        ,[record_date]
                        ,[times]
                        ,[first_num]
                        ,[second_num]
                        ,[third_num]
                        ,[fourth_num]
                        ,[fifth_num]
                        ,[sixth_num]
                        ,[seventh_num]
                    FROM [LuHeRecord]";
            //记录列表
            List <Model.HkRecord> lRecord = new List <Model.HkRecord>();
            //数据读取对象
            SqlDataReader read;

            DBUtility.SQLServerHelper helper = new DBUtility.SQLServerHelper();
            helper.RunSQL(strSelect, out read);
            while (read.Read())
            {
                Model.HkRecord record = new Model.HkRecord();
                record.Id         = read.GetInt64(0);
                record.RecordDate = read.GetDateTime(1);
                record.Times      = read.GetString(2);
                record.FirstNum   = read.GetByte(3);
                record.SecondNum  = read.GetByte(4);
                record.ThirdNum   = read.GetByte(5);
                record.FourthNum  = read.GetByte(6);
                record.FifthNum   = read.GetByte(7);
                record.SixthNum   = read.GetByte(8);
                record.SeventhNum = read.GetByte(9);
                lRecord.Add(record);
            }
            return(lRecord);
        }
Пример #6
0
        /// <summary>
        /// 取小于某期次的指定数量的记录
        /// </summary>
        /// <param name="tiems"></param>
        /// <param name="dataCount"></param>
        /// <returns></returns>
        public List <Model.HkRecord> GetDataByLessThanTimes(string times, int dataCount)
        {
            string strSelect =
                @"SELECT * FROM
                    (
                        SELECT TOP " + dataCount + @" * FROM [LuHeRecord] 
                            WHERE [record_date]<=
                                (
                                    SELECT [record_date] FROM [LuHeRecord] WHERE [times]=@times
                                )
                            ORDER BY [record_date] DESC
                    ) T
                ORDER BY [record_date] ASC";
            SqlDataReader dr;

            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@times", times);
            DBUtility.SQLServerHelper helper = new DBUtility.SQLServerHelper();
            helper.RunSQL(strSelect, pars, out dr);
            List <Model.HkRecord> lRecord = new List <Model.HkRecord>();

            while (dr.Read())
            {
                Model.HkRecord record = new Model.HkRecord();
                record.Id         = dr.GetInt64(0);
                record.RecordDate = dr.GetDateTime(1);
                record.Times      = dr.GetString(2);
                record.FirstNum   = dr.GetByte(3);
                record.SecondNum  = dr.GetByte(4);
                record.ThirdNum   = dr.GetByte(5);
                record.FourthNum  = dr.GetByte(6);
                record.FifthNum   = dr.GetByte(7);
                record.SixthNum   = dr.GetByte(8);
                record.SeventhNum = dr.GetByte(9);
                lRecord.Add(record);
            }
            return(lRecord);
        }
Пример #7
0
        /// <summary>
        /// 新增一条记录
        /// </summary>
        /// <param name="record">记录信息</param>
        public int AddRecord(Model.HkRecord record)
        {
            //先删除相同的记录
            DeleteRecord(record.RecordDate, record.Times);
            string strInsert = "insert into [LuHeRecord]" +
                               "([record_date]" +
                               ",[times]" +
                               ",[first_num]" +
                               ",[second_num]" +
                               ",[third_num]" +
                               ",[fourth_num]" +
                               ",[fifth_num]" +
                               ",[sixth_num]" +
                               ",[seventh_num])" +
                               "values" +
                               "(@record_date" +
                               ",@times" +
                               ",@first_num" +
                               ",@second_num" +
                               ",@third_num" +
                               ",@fourth_num" +
                               ",@fifth_num" +
                               ",@sixth_num" +
                               ",@seventh_num)";

            SqlParameter[] pars = new SqlParameter[9];
            pars[0] = new SqlParameter("@record_date", record.RecordDate);
            pars[1] = new SqlParameter("@times", record.Times);
            pars[2] = new SqlParameter("@first_num", record.FirstNum);
            pars[3] = new SqlParameter("@second_num", record.SecondNum);
            pars[4] = new SqlParameter("@third_num", record.ThirdNum);
            pars[5] = new SqlParameter("@fourth_num", record.FourthNum);
            pars[6] = new SqlParameter("@fifth_num", record.FifthNum);
            pars[7] = new SqlParameter("@sixth_num", record.SixthNum);
            pars[8] = new SqlParameter("@seventh_num", record.SeventhNum);
            DBUtility.SQLServerHelper helper = new DBUtility.SQLServerHelper();
            return(helper.RunSQL(strInsert, pars));
        }
Пример #8
0
        /// <summary>
        /// 取得记录数据
        /// </summary>
        /// <param name="fileName">包含路径的文件名</param>
        /// <param name="columnCount">要导入数据的列数</param>
        /// <returns></returns>
        public List <Model.HkRecord> GetHkRecord(string fileName)
        {
            //数组列表
            List <Model.HkRecord> lRecord = new List <Model.HkRecord>();

            if (xlApp == null)
            {
                xlApp = new Excel.Application();
                //是否可见
                xlApp.Visible = this.Visible;
            }
            //以只读方式打开文件
            try
            {
                xlBook = xlApp.Workbooks.Open(fileName, ReadOnly: true);
            }
            catch (Exception ex)
            {
                throw new Exception("打开文件失败!" + ex.Message);
            }
            for (int i = 1; i <= xlBook.Sheets.Count; i++)
            {
                //当前序号工作表
                Excel.Worksheet sht = xlBook.Sheets[i];
                //查找最后一个"序号"单元格
                Excel.Range rng = sht.Range[
                    sht.Cells[2, 1],
                    sht.Cells[sht.UsedRange.Rows.Count, sht.UsedRange.Columns.Count]
                                  ].Find("序号");
                if (rng == null)
                {
                    throw new Exception("工作表" + sht.Name + ",没有第一个单元格为‘序号’的结束行!");
                }
                //结束行号
                int endRow = rng.Row;
                //记录日期
                DateTime recordDate;
                if (sht.Cells[2, 2].Value == null)
                {
                    throw new Exception("工作表" + sht.Name + ",第二行记录日期为空!");
                }
                if (!DateTime.TryParse(sht.Cells[2, 2].Value.ToString(), out recordDate))
                {
                    throw new Exception("工作表" + sht.Name + ",第二行记录日期格式错误!");
                }
                //遍历所有行
                for (int r = 2; r < endRow; r++)
                {
                    OnGetingRecord(null);
                    //是否停止
                    if (this.IsStop)
                    {
                        return(null);
                    }
                    try
                    {
                        //声明记录对象
                        Model.HkRecord record = new Model.HkRecord();
                        //记录日期
                        record.RecordDate = DateTime.Parse(sht.Cells[r, 2].Value.ToString());
                        //记录期次
                        string strTimes = sht.Cells[r, 3].Value.ToString();
                        //期次必须为4位年3位期次
                        if (strTimes != recordDate.Year.ToString() + (r - 1).ToString("000"))
                        {
                            throw new Exception("工作表" + sht.Name + ",第" + r + "行期次错误!" + "必须为4位年3位连续的期次。");
                        }
                        //写入数据到记录对象
                        record.Times      = strTimes;
                        record.FirstNum   = byte.Parse(sht.Cells[r, 4].Value.ToString());
                        record.SecondNum  = byte.Parse(sht.Cells[r, 5].Value.ToString());
                        record.ThirdNum   = byte.Parse(sht.Cells[r, 6].Value.ToString());
                        record.FourthNum  = byte.Parse(sht.Cells[r, 7].Value.ToString());
                        record.FifthNum   = byte.Parse(sht.Cells[r, 8].Value.ToString());
                        record.SixthNum   = byte.Parse(sht.Cells[r, 9].Value.ToString());
                        record.SeventhNum = byte.Parse(sht.Cells[r, 10].Value.ToString());
                        //添加到列表
                        lRecord.Add(record);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("工作表" + sht.Name + ",第" + r + "行格式错误!" + ex.Message);
                    }
                }
            }
            return(lRecord);
        }
Пример #9
0
        /// <summary>
        /// 刷新分析记录(写入实际号码并计算实际收益和余额)
        /// </summary>
        private void RefreshByRecord()
        {
            //分析结果操作对象
            BLL.AnalysisBLL analysisBll = new BLL.AnalysisBLL();
            //获取所有数据
            List <Model.HkRecordAnalysis> lAnalysis = analysisBll.GetData();

            //记录操作对象
            BLL.RecordBLL recordBll = new BLL.RecordBLL();
            //余额
            double balance = 0;

            if (lAnalysis.Count == 0)
            {
                return;
            }
            try
            {
                for (int i = 0; i < lAnalysis.Count; i++)
                {
                    Model.HkRecordAnalysis analysis = lAnalysis[i];
                    if (analysis.Outlay <= 0)
                    {
                        //余额为当前余额
                        analysis.Balance = balance;
                        continue;
                    }
                    else
                    {
                        //号码金额为空
                        if (analysis.NumMoney.Length == 0)
                        {
                            //实际收益-支出
                            analysis.RelityIncome -= analysis.Outlay;
                            //余额=余额-支出
                            balance = balance - analysis.Outlay;
                            //写入余额
                            analysis.Balance = balance;
                        }
                        else
                        {
                            //操作第几位数字
                            byte whatNumber = analysis.WhatNumber;
                            //当前期次
                            string times = analysis.Times;
                            //按期次获取记录
                            Model.HkRecord record = recordBll.GetDataByTimes(times);
                            //在记录找不到分析的期次
                            if (record.Times == null)
                            {
                                break;
                            }
                            byte number;
                            switch (whatNumber)
                            {
                            case 1:
                                number = record.FirstNum;
                                break;

                            case 2:
                                number = record.SecondNum;
                                break;

                            case 3:
                                number = record.ThirdNum;
                                break;

                            case 4:
                                number = record.FourthNum;
                                break;

                            case 5:
                                number = record.FifthNum;
                                break;

                            case 6:
                                number = record.SixthNum;
                                break;

                            case 7:
                                number = record.SeventhNum;
                                break;

                            default:
                                throw new Exception("期次不正确,必须为1-7之间!");
                            }
                            analysis.RecordNum = number;//记录号码
                            string   numMoney  = analysis.NumMoney.Replace(" ", "").Replace(";", ";").Replace(":", ":");
                            string[] numMoneys = numMoney.Split(new char[] { ';' });
                            //号码与金额是否有记录号码
                            bool isHasNumber = false;
                            foreach (string item in numMoneys)
                            {
                                int index = item.IndexOf(":");
                                if (index > 0)
                                {
                                    string strNum   = item.Substring(0, index);
                                    string strMoney = item.Substring(index + 1);
                                    //分析号码与实际记录一致
                                    if (strNum == number.ToString())
                                    {
                                        double money;
                                        if (double.TryParse(strMoney, out money))
                                        {
                                            double sumMoney = money * analysis.Multiple;
                                            //实际收益
                                            double relityIncome = sumMoney - analysis.Outlay;
                                            analysis.RelityIncome = relityIncome;
                                            isHasNumber           = true;
                                            break;
                                        }
                                    }
                                }
                            }
                            if (isHasNumber == false)
                            {
                                //实际收益为负的支出
                                analysis.RelityIncome = -analysis.Outlay;
                            }
                            //余额=余额+实际收益
                            balance         += analysis.RelityIncome;
                            analysis.Balance = balance;
                        }
                    }
                }
                //更新列表
                analysisBll.Update(lAnalysis);
            }
            catch (Exception ex)
            {
                MessageBox.Show("刷新过程发生错误," + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                frmMdi.tsslInfo.Text      = "刷新失败!";
                frmMdi.tsslInfo.BackColor = Color.Yellow;
            }
        }
Пример #10
0
 /// <summary>
 /// <summary>
 /// 更新一条记录
 /// </summary>
 /// <param name="record">记录信息</param>
 public void UpdateRecord(Model.HkRecord record, int oldId)
 {
     recordSQL.UpdateRecord(record, oldId);
 }
Пример #11
0
 /// <summary>
 /// 新增一条记录
 /// </summary>
 /// <param name="record">记录信息</param>
 public int AddRecord(Model.HkRecord record)
 {
     return(recordSQL.AddRecord(record));
 }
Пример #12
0
        /// <summary>
        /// 保存数据表
        /// </summary>
        /// <param name="dttab"></param>
        /// <returns></returns>
        public bool SaveData(DataTable dttab)
        {
            //取得更改的数据
            dttab = dttab.GetChanges();
            //表不为空
            if (dttab != null)
            {
                //启动事务
                using (TransactionScope scope = new TransactionScope())
                {
                    //遍历所有行
                    for (int i = 0; i < dttab.Rows.Count; i++)
                    {
                        //当前行的状态
                        DataRowState state = dttab.Rows[i].RowState;
                        //是否为新增、删除、修改
                        if (state == DataRowState.Added || state == DataRowState.Modified)
                        {
                            Model.HkRecord record = new Model.HkRecord();
                            long           id;
                            object         value = dttab.Rows[i]["id"];
                            if (value != null && long.TryParse(value.ToString(), out id))
                            {
                                record.Id = id;
                            }
                            value             = dttab.Rows[i]["record_date"];
                            record.RecordDate = DateTime.Parse(value.ToString());
                            value             = dttab.Rows[i]["times"];
                            record.Times      = value.ToString();
                            value             = dttab.Rows[i]["first_num"];
                            record.FirstNum   = byte.Parse(value.ToString());
                            value             = dttab.Rows[i]["second_num"];
                            record.SecondNum  = byte.Parse(value.ToString());
                            value             = dttab.Rows[i]["third_num"];
                            record.ThirdNum   = byte.Parse(value.ToString());
                            value             = dttab.Rows[i]["fourth_num"];
                            record.FourthNum  = byte.Parse(value.ToString());
                            value             = dttab.Rows[i]["fifth_num"];
                            record.FifthNum   = byte.Parse(value.ToString());
                            value             = dttab.Rows[i]["sixth_num"];
                            record.SixthNum   = byte.Parse(value.ToString());
                            value             = dttab.Rows[i]["seventh_num"];
                            record.SeventhNum = byte.Parse(value.ToString());
                            switch (state)
                            {
                            case DataRowState.Added:
                                recordSQL.AddRecord(record);
                                break;

                            case DataRowState.Modified:
                                long oldId = long.Parse(dttab.Rows[i]["id", DataRowVersion.Original].ToString());
                                recordSQL.UpdateRecord(record, oldId);
                                break;

                            default:
                                break;
                            }
                        }
                        else if (dttab.Rows[i].RowState == DataRowState.Deleted)
                        {
                            long oldId;
                            if (long.TryParse(dttab.Rows[i]["id", DataRowVersion.Original].ToString(), out oldId))
                            {
                                recordSQL.DeleteRecord(oldId);
                            }
                            ;
                        }
                    }
                    //提交事务
                    scope.Complete();
                }
                //提交更改
                dttab.AcceptChanges();
            }
            return(true);
        }