/// <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));
}
/// <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);
}
/// <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);
}
/// 取最大记录日期记录
/// </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);
}
/// <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);
}
/// <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);
}
/// <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));
}
/// <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);
}
/// <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;
}
}
/// <summary>
/// <summary>
/// 更新一条记录
/// </summary>
/// <param name="record">记录信息</param>
public void UpdateRecord(Model.HkRecord record, int oldId)
{
recordSQL.UpdateRecord(record, oldId);
}
/// <summary>
/// 新增一条记录
/// </summary>
/// <param name="record">记录信息</param>
public int AddRecord(Model.HkRecord record)
{
return(recordSQL.AddRecord(record));
}
/// <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);
}