public JyDataSet.业绩统计DataTable QueryYJData(int startDate, int endDate)
{
JyDataSet.业绩统计DataTable dtNew = new JyDataSet.业绩统计DataTable();
string sql = string.Format("select 交易员,组合号,证券代码,证券名称,毛利,交易费用,净利润,买入数量,买入金额,买入均价,卖出数量,卖出金额,卖出均价,日期 from dbo.业绩统计 where 日期>={0} and 日期<={1}", startDate, endDate);
DataTable resultDt = SQLHelper.ExecuteDt(sql);
foreach (DataRow dr in resultDt.Rows)
{
JyDataSet.业绩统计Row newRow = dtNew.New业绩统计Row();
dtNew.Add业绩统计Row(newRow);
newRow.交易员 = dr["交易员"].ToString();
newRow.组合号 = dr["组合号"].ToString();
newRow.证券代码 = dr["证券代码"].ToString();
newRow.证券名称 = dr["证券名称"].ToString();
newRow.毛利 = (decimal)dr["毛利"];
newRow.交易费用 = (decimal)dr["交易费用"];
newRow.净利润 = (decimal)dr["净利润"];
newRow.买入数量 = (decimal)dr["买入数量"];
newRow.买入金额 = (decimal)dr["买入金额"];
newRow.买入均价 = (decimal)dr["买入均价"];
newRow.卖出数量 = (decimal)dr["卖出数量"];
newRow.卖出金额 = (decimal)dr["卖出金额"];
newRow.卖出均价 = (decimal)dr["卖出均价"];
}
return(dtNew);
}
public bool SendYJData(JyDataSet.业绩统计DataTable dtYJ, string serverMac)
{
try
{
if (dtYJ.Rows.Count == 0)
{
Program.logger.LogInfo(string.Format("接收server-{0}的业绩统计DataTable.Rows=0", serverMac));
return(false);
}
JyDataSet.业绩统计Row firstRow1 = dtYJ[0];
//判断当前MAC是否已经更新数据
string sqlMac = string.Format("select 更新日期 from dbo.日志表 where 服务器MAC='{0}' and 数据类型='业绩'", serverMac);
DataTable dtMac = SQLHelper.ExecuteDt(sqlMac);
if (dtMac.Rows.Count > 0)
{
if (firstRow1.日期 == Convert.ToInt32(dtMac.Rows[0]["更新日期"]))
{
string updateType = ConfigMain.GetConfigValue("UpdateType");
if ("0".Equals(updateType))
{
//删除原有server Mac的数据
string sqlDel = string.Format("delete from dbo.业绩统计 where 服务器MAC='{0}' and 日期={1}", serverMac, firstRow1.日期);
SQLHelper.ExecuteSql(sqlDel);
}
}
}
decimal closePrice;
Dictionary <int, decimal> dictPrice = new Dictionary <int, decimal>();
StringBuilder sb = new StringBuilder();
sb.Append("begin ");
Program.logger.LogInfo(string.Format("业绩数据入库开始：{0},当前server-{1}", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), serverMac));
foreach (JyDataSet.业绩统计Row 业绩统计Row1 in dtYJ)
{
//string sql = "insert into 业绩统计(交易员,组合号,证券代码,证券名称,毛利,交易费用,净利润,买入数量,买入金额,买入均价,卖出数量,卖出金额,卖出均价)values(@交易员,@组合号,@证券代码,@证券名称,@毛利,@交易费用,@净利润,@买入数量,@买入金额,@买入均价,@卖出数量,@卖出金额,@卖出均价)";
//SqlParameter[] sqlParm = new SqlParameter[] { new SqlParameter("@交易员", 业绩统计Row1.交易员),new SqlParameter("@组合号", 业绩统计Row1.组合号), new SqlParameter("@证券代码", 业绩统计Row1.证券代码) ,
//new SqlParameter("@证券名称", 业绩统计Row1.证券名称), new SqlParameter("@毛利", 业绩统计Row1.毛利),new SqlParameter("@交易费用", 业绩统计Row1.交易费用),
//new SqlParameter("@净利润", 业绩统计Row1.净利润),new SqlParameter("@买入数量", 业绩统计Row1.买入数量),new SqlParameter("@买入金额", 业绩统计Row1.买入金额),new SqlParameter("@买入均价", 业绩统计Row1.买入均价),new SqlParameter("@卖出数量", 业绩统计Row1.卖出数量),
//new SqlParameter("@卖出金额", 业绩统计Row1.卖出金额),new SqlParameter("@卖出均价", 业绩统计Row1.卖出均价)};
//SQLHelper.ExecuteSql(sql, sqlParm);
int iCode = Convert.ToInt32(业绩统计Row1.证券代码.Trim());
if (dictPrice.ContainsKey(iCode))
{
closePrice = dictPrice[iCode];
}
else
{
closePrice = CommonUtils.GetClosePrice(业绩统计Row1.证券代码.Trim());
}
string sql = string.Format("insert into dbo.业绩统计(交易员,组合号,证券代码,证券名称,毛利,交易费用,净利润,买入数量,买入金额,买入均价,卖出数量,卖出金额,卖出均价,日期,交易额度,收盘价,分组,服务器MAC)values('{0}','{1}','{2}','{3}',{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},'{16}','{17}');",
业绩统计Row1.交易员,
业绩统计Row1.组合号,
业绩统计Row1.证券代码,
业绩统计Row1.证券名称,
业绩统计Row1.毛利,
业绩统计Row1.交易费用,
业绩统计Row1.净利润,
业绩统计Row1.买入数量,
业绩统计Row1.买入金额,
业绩统计Row1.买入均价,
业绩统计Row1.卖出数量,
业绩统计Row1.卖出金额,
业绩统计Row1.卖出均价,
业绩统计Row1.日期,
业绩统计Row1.交易额度,
closePrice,
业绩统计Row1.分组,
serverMac
);
sb.Append(sql);
}
sb.Append(" end;");
SQLHelper.ExecuteSql(sb.ToString());
//更新日志表
string sqlLog = string.Empty;
SqlParameter[] sqlParm;
if (dtMac.Rows.Count > 0)
{
sqlLog = "update dbo.日志表 set 更新日期=@更新日期,更新数量=@更新数量 where 服务器MAC=@服务器MAC and 数据类型='业绩'";
sqlParm = new SqlParameter[] { new SqlParameter("@更新日期", firstRow1.日期), new SqlParameter("@更新数量", dtYJ.Rows.Count), new SqlParameter("@服务器MAC", serverMac) };
}
else
{
sqlLog = "insert into dbo.日志表(服务器MAC,更新数量,数据类型,更新日期)values(@服务器MAC,@更新数量,@数据类型,@更新日期)";
sqlParm = new SqlParameter[] { new SqlParameter("@更新日期", firstRow1.日期), new SqlParameter("@更新数量", dtYJ.Rows.Count), new SqlParameter("@数据类型", "业绩"), new SqlParameter("@服务器MAC", serverMac) };
}
SQLHelper.ExecuteSql(sqlLog, sqlParm);
Program.logger.LogInfo(string.Format("业绩数据入库结束：{0},当前server-{1}", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), serverMac));
}
catch (Exception ex)
{
Program.logger.LogInfo(string.Format("业绩数据入库异常：{0},当前server-{1}", ex.Message, serverMac));
return(false);
}
return(true);
}
