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 QueryGroupYjData(int startDate, int endDate, string region)
{
Dictionary <string, decimal> dictSZ = new Dictionary <string, decimal>();
JyDataSet.分帐户业绩统计DataTable dtGroupYj = new JyDataSet.分帐户业绩统计DataTable();
try
{
string sql = string.Empty;
string sqlCap = string.Empty;
if (region.Equals("ALL"))
{
sql = string.Format("select 组合号,sum(毛利)毛利,sum(买入数量+卖出数量)交易股数,sum(买入金额+卖出金额)交易额,sum(交易费用)手续费,sum(毛利-交易费用)利润,sum(净利润)NET,sum(买入数量*收盘价)使用市值,sum(交易额度*收盘价)总市值 from dbo.业绩统计 where 日期>={0} and 日期<={1} group by 组合号 ", startDate, endDate);
sqlCap = string.Format("select 组合号,sum(交易额度*收盘价)总市值 from dbo.额度分配 where 日期>={0} and 日期<={1} group by 组合号", startDate, endDate);
}
else
{
sql = string.Format("select 组合号,sum(毛利)毛利,sum(买入数量+卖出数量)交易股数,sum(买入金额+卖出金额)交易额,sum(交易费用)手续费,sum(毛利-交易费用)利润,sum(净利润)NET,sum(买入数量*收盘价)使用市值,sum(交易额度*收盘价)总市值 from dbo.业绩统计 where 日期>={0} and 日期<={1} and 分组='{2}' group by 组合号 ", startDate, endDate, region);
sqlCap = string.Format("select 组合号,sum(交易额度*收盘价)总市值 from dbo.额度分配 where 日期>={0} and 日期<={1} and 分组='{2}' group by 组合号", startDate, endDate, region);
}
DataTable resultDt = SQLHelper.ExecuteDt(sql);
if (resultDt == null || resultDt.Rows.Count == 0)
{
return(dtGroupYj);
}
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 = dtGroupYj.New分帐户业绩统计Row();
dtGroupYj.Add分帐户业绩统计Row(newRow);
newRow.日期 = startDate == endDate?startDate.ToString() : string.Format("{0}-{1}", startDate, endDate);
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);
}
}
catch (Exception ex)
{
Program.logger.LogInfo("QueryGroupYjData：" + ex.Message);
}
return(dtGroupYj);
}
