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 QueryUserYjData(int startDate, int endDate) { Dictionary <string, decimal> dictSZ = new Dictionary <string, decimal>(); int idx = 1; JyDataSet.交易员业绩统计DataTable dtUserYj = new JyDataSet.交易员业绩统计DataTable(); try { string sql = string.Format("select 交易员,分组,sum(毛利)毛利,sum(买入数量+卖出数量)交易股数,sum(买入金额+卖出金额)交易额,sum(交易费用)手续费,sum(毛利-交易费用)利润,sum(净利润)NET, sum(买入数量*收盘价)使用市值,sum(交易额度*收盘价)总市值 from dbo.业绩统计 where 日期>={0} and 日期<={1} group by 分组,交易员 order by 分组 ", startDate, endDate); DataTable resultDt = SQLHelper.ExecuteDt(sql); if (resultDt == null || resultDt.Rows.Count == 0) { return(dtUserYj); } string sqlCap = string.Format("select 交易员,sum(交易额度*收盘价)总市值 from dbo.额度分配 where 日期>={0} and 日期<={1} group by 交易员", startDate, endDate); DataTable capDt = SQLHelper.ExecuteDt(sqlCap); foreach (DataRow dr in capDt.Rows) { if (!dictSZ.ContainsKey(dr["交易员"].ToString())) { dictSZ.Add(dr["交易员"].ToString(), decimal.Round(Convert.ToDecimal(dr["总市值"]), 2)); } } foreach (DataRow dr in resultDt.Rows) { JyDataSet.交易员业绩统计Row newRow = dtUserYj.New交易员业绩统计Row(); dtUserYj.Add交易员业绩统计Row(newRow); newRow.日期 = startDate == endDate?startDate.ToString() : string.Format("{0}-{1}", startDate, endDate); newRow.序号 = idx; newRow.账户 = dr["交易员"].ToString().Trim(); newRow.分组 = dr["分组"].ToString().Trim(); newRow.毛利润 = Convert.ToDecimal(dr["毛利"]); newRow.交易股数 = Convert.ToInt32(dr["交易股数"]); newRow.交易额 = Convert.ToDecimal(dr["交易额"]); newRow.手续费 = Convert.ToDecimal(dr["手续费"]); newRow.利润 = Convert.ToDecimal(dr["利润"]); newRow.隔夜仓 = 0.00M; newRow.其他 = 0.00M; newRow.T1 = 0.00M; newRow.NET = newRow.利润 + newRow.隔夜仓 + newRow.其他; newRow.使用市值 = decimal.Round(Convert.ToDecimal(dr["使用市值"]), 2); if (dictSZ.ContainsKey(newRow.账户)) { newRow.总市值 = dictSZ[newRow.账户]; } else { newRow.总市值 = 0; } newRow.效率 = newRow.交易额 == 0 ? "0.00%" : string.Format("{0}%", Math.Round(newRow.NET / newRow.交易额 * 100, 2, MidpointRounding.AwayFromZero)); newRow.使用率 = newRow.总市值 == 0 ? "0.00%" : string.Format("{0}%", Math.Round(newRow.使用市值 / newRow.总市值 * 100, 2, MidpointRounding.AwayFromZero)); newRow.使用效率 = decimal.Round(Convert.ToDecimal(newRow.效率.Replace("%", "").Trim()) / 100 * Convert.ToDecimal(newRow.使用率.Replace("%", "").Trim()) / 100 * 10000, 2); idx++; } } catch (Exception ex) { Program.logger.LogInfo("QueryUserYjData:" + ex.Message); } return(dtUserYj); }