private void btnQuery_Click(object sender, EventArgs e) { try { this.lbInfo.Text = "正在查询..."; string[] arrSDate = this.dateTimePicker1.Text.Split(new char[] { ' ' })[0].Split('/'); if (arrSDate.Length != 3) { MessageBox.Show("开始日期格式不正确!", "Error"); this.lbInfo.Text = ""; return; } int startDate = int.Parse(string.Format("{0}{1}{2}", arrSDate[0], arrSDate[1].PadLeft(2, '0'), arrSDate[2].PadLeft(2, '0'))); string[] arrEDate = this.dateTimePicker2.Text.Split(new char[] { ' ' })[0].Split('/'); if (arrEDate.Length != 3) { MessageBox.Show("结束日期格式不正确!", "Error"); this.lbInfo.Text = ""; return; } int endDate = int.Parse(string.Format("{0}{1}{2}", arrEDate[0], arrEDate[1].PadLeft(2, '0'), arrEDate[2].PadLeft(2, '0'))); if (startDate > endDate) { MessageBox.Show("结束日期不能小于开始日期", "Error"); this.lbInfo.Text = ""; return; } JyDataSet.业绩统计DataTable 业绩统计DataTable1 = Program.DataServiceClient.QueryYJData(startDate, endDate); this.dataGridView1.DataSource = new DataTable(); //按交易员统计 if (this.radioButton交易员.Checked) { JyDataSet.交易员业绩统计DataTable userDt = Program.DataServiceClient.QueryUserYjData(startDate, endDate); this.dataGridView1.DataSource = CommonUtils.SummaryUserData(userDt); this.dataGridView1.Columns["分组"].Visible = false; this.dataGridView1.Columns[8].DefaultCellStyle.BackColor = Color.Yellow; } else if (this.radioButton组合号.Checked) { JyDataSet.分帐户业绩统计DataTable groupDt = Program.DataServiceClient.QueryGroupYjData(startDate, endDate, this.comboBox1.SelectedItem.ToString()); this.dataGridView1.DataSource = CommonUtils.SummaryGroupData(groupDt); this.dataGridView1.Columns["分组"].Visible = false; this.dataGridView1.Columns[6].DefaultCellStyle.BackColor = Color.Yellow; } else if (this.radioButton分组.Checked) { JyDataSet.分组业绩统计DataTable userRegionDt = Program.DataServiceClient.QueryUserRegionYjData(startDate, endDate); this.dataGridView1.DataSource = CommonUtils.SummaryUserRegion(userRegionDt); } this.lbInfo.Text = string.Format("查询结果:{0} 条", this.dataGridView1.Rows.Count == 0 ? 0 : this.dataGridView1.Rows.Count - 1); iStartDate = startDate; iEndDate = endDate; } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); } }
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); }