/// <summary>
/// 开始计算
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
List <RainCaculateConditon> lstCondition = new List <RainCaculateConditon>();
if (radioButton1.Checked)
{
if (dateTimePicker1.Value == null || dateTimePicker2.Value == null || string.IsNullOrEmpty(txtState.Text))
{
MsgBox.ShowInfo("计算参数不足,请补充.");
return;
}
RainCaculateConditon temp = new RainCaculateConditon()
{
StartTime = dateTimePicker1.Value,
EndTime = dateTimePicker2.Value,
EventNum = "1",
State = txtState.Text
};
lstCondition.Add(temp);
}
else
{
if (string.IsNullOrEmpty(txtFilePath.Text))
{
MsgBox.ShowInfo("计算参数不足,请补充.");
return;
}
//读取Excel模板
DataTable sheetTable = ExcelReader.GetExcelContext(txtFilePath.Text, comboBox1.Text);
for (int i = 0; i < sheetTable.Rows.Count; i++)
{
try
{
RainCaculateConditon temp = new RainCaculateConditon()
{
StartTime = DateTime.Parse(sheetTable.Rows[i][1].ToString()),
EndTime = DateTime.Parse(sheetTable.Rows[i][2].ToString()),
EventNum = sheetTable.Rows[i][0].ToString(),
State = sheetTable.Rows[i][3].ToString()
};
lstCondition.Add(temp);
}
catch (Exception ex)
{
}
}
}
button2.Enabled = false;
button3.Enabled = false;
dataGridView1.Rows.Clear();
backgroundWorker1.RunWorkerAsync(lstCondition);
}
private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
FormOutput.AppendProress(e.ProgressPercentage);
List <RainCaculateResult> result = e.UserState as List <RainCaculateResult>;
if (result == null)
{
RainCaculateConditon condition = e.UserState as RainCaculateConditon;
int index = dataGridView1.Rows.Add();
//赋值各行数据
dataGridView1[0, index].Value = condition.EventNum;
dataGridView1[1, index].Value = condition.StartTime.ToLongDateString();
dataGridView1[2, index].Value = condition.EndTime.ToLongDateString();
dataGridView1[3, index].Value = condition.State;
dataGridView1[4, index].Value = "该行未查询到数据";
//加上红色背景色
dataGridView1.Rows[index].DefaultCellStyle.BackColor = Color.Red;
return;
}
List <string> lstEventNum = result.OrderBy(t => t.EventNum).Select(t => t.EventNum).Distinct().ToList();
foreach (var item in lstEventNum)
{
int index = dataGridView1.Rows.Add();
//赋值各行数据
dataGridView1[0, index].Value = item;
dataGridView1[1, index].Value = lstCondition.Where(t => t.EventNum == item).Select(t => t.StartTime.ToLongDateString()).FirstOrDefault();
dataGridView1[2, index].Value = lstCondition.Where(t => t.EventNum == item).Select(t => t.EndTime.ToLongDateString()).FirstOrDefault();;
dataGridView1[3, index].Value = lstCondition.Where(t => t.EventNum == item).Select(t => t.State).FirstOrDefault();
for (int i = 0; i < hourArry.Length; i++)
{
string columName = $"RAINFALL_{hourArry[i]}_HOUR";
dataGridView1[columName, index].Value = result.Where(t => t.Day == false && t.EventNum == item && t.MonthMax == false && t.During == hourArry[i]).Select(t => t.MaxValue).FirstOrDefault();
columName = $"RAINFALL_{hourArry[i]}_HOUR_TIME";
dataGridView1[columName, index].Value = result.Where(t => t.Day == false && t.EventNum == item && t.MonthMax == false && t.During == hourArry[i]).Select(t => t.MaxValueDate).FirstOrDefault();
columName = $"RAINFALL_{hourArry[i]}_HOUR_QC";
dataGridView1[columName, index].Value = result.Where(t => t.Day == false && t.EventNum == item && t.MonthMax == false && t.During == hourArry[i]).Select(t => t.MaxValueQc).FirstOrDefault();
columName = $"MAX_{hourArry[i]}_HOUR_MONTH";
dataGridView1[columName, index].Value = result.Where(t => t.Day == false && t.EventNum == item && t.MonthMax == true && t.During == hourArry[i]).Select(t => t.MaxValue).FirstOrDefault();
columName = $"MAX_{hourArry[i]}_HOUR_MONTH_TIME";
dataGridView1[columName, index].Value = result.Where(t => t.Day == false && t.EventNum == item && t.MonthMax == true && t.During == hourArry[i]).Select(t => t.MaxValueDate).FirstOrDefault();
columName = $"MAX_{hourArry[i]}_HOUR_MONTH_QC";
dataGridView1[columName, index].Value = result.Where(t => t.Day == false && t.EventNum == item && t.MonthMax == true && t.During == hourArry[i]).Select(t => t.MaxValueQc).FirstOrDefault();
}
for (int i = 0; i < dayArry.Length; i++)
{
string columName = $"RAINFALL_{dayArry[i]}_DAY";
dataGridView1[columName, index].Value = result.Where(t => t.Day == true && t.EventNum == item && t.MonthMax == false && t.During == dayArry[i]).Select(t => t.MaxValue).FirstOrDefault();
if (yxDayArry.Contains(dayArry[i]))
{
//加入有效雨量，计算有效雨量的最大值日期
DateTime yxMaxTime = result.Where(t => t.Day == true && t.EventNum == item && t.MonthMax == false && t.During == dayArry[i]).Select(t => t.MaxValueDate).FirstOrDefault();
if (yxMaxTime != DateTime.MinValue)
{
//计算有效雨量值,并乘以系数
double yxVal = 0;
for (int j = 0; j < dayArry[i]; j++)
{
string sql = $"SELECT sum(rainfall) FROM RAINFALL_STATE where monitornum = '{dataGridView1[3, index].Value}' and recorddate > '{yxMaxTime.AddDays(-j - 1)}' and recorddate<='{yxMaxTime.AddDays(-j)}'";
DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count > 0)
{
double?val = ConvertDecimal(ds.Tables[0].Rows[0][0]);
if (val.HasValue)
{
double arg = Math.Round(Math.Pow(0.8, j), 2);
yxVal += (val.Value * arg);
}
}
}
columName = $"RAINFALL_{dayArry[i]}_DAY_YX";
dataGridView1[columName, index].Value = yxVal;
}
}
columName = $"RAINFALL_{dayArry[i]}_DAY_TIME";
dataGridView1[columName, index].Value = result.Where(t => t.Day == true && t.EventNum == item && t.MonthMax == false && t.During == dayArry[i]).Select(t => t.MaxValueDate).FirstOrDefault();
columName = $"RAINFALL_{dayArry[i]}_DAY_QC";
dataGridView1[columName, index].Value = result.Where(t => t.Day == true && t.EventNum == item && t.MonthMax == false && t.During == dayArry[i]).Select(t => t.MaxValueQc).FirstOrDefault();
columName = $"MAX_{dayArry[i]}_DAY_MONTH";
dataGridView1[columName, index].Value = result.Where(t => t.Day == true && t.EventNum == item && t.MonthMax == true && t.During == dayArry[i]).Select(t => t.MaxValue).FirstOrDefault();
columName = $"MAX_{dayArry[i]}_DAY_MONTH_TIME";
dataGridView1[columName, index].Value = result.Where(t => t.Day == true && t.EventNum == item && t.MonthMax == true && t.During == dayArry[i]).Select(t => t.MaxValueDate).FirstOrDefault();
columName = $"MAX_{dayArry[i]}_DAY_MONTH_QC";
dataGridView1[columName, index].Value = result.Where(t => t.Day == true && t.EventNum == item && t.MonthMax == true && t.During == dayArry[i]).Select(t => t.MaxValueQc).FirstOrDefault();
}
}
}
private List <RainCaculateResult> CaculateRain(RainCaculateConditon condition)
{
List <RainCaculateResult> result = new List <RainCaculateResult>();
DataSet ds = null;
//1h,3h,6h,12h,24h,48h,72h 时间范围内最大值
string commandText = string.Empty;
for (int i = 0; i < hourArry.Length; i++)
{
commandText += string.Format(@"SELECT top 1
RAINFALL_{3}_HOUR
,TIME
,RAINFALL_{3}_HOUR_C
FROM [DB_RainMonitor].[dbo].[RAINFALL_HOUR]
where MONITORNUM = '{0}'
and TIME >= '{1}'
and TIME < '{2}' order by RAINFALL_{3}_HOUR desc;", condition.State, condition.StartTime.ToShortDateString(), condition.EndTime.AddDays(1).ToShortDateString(), hourArry[i]);
commandText += string.Format(@"SELECT top 1
RAINFALL_{3}_HOUR
,TIME
,RAINFALL_{3}_HOUR_C
FROM [DB_RainMonitor].[dbo].[RAINFALL_HOUR]
where MONITORNUM = '{0}'
and TIME >= '{1}-{4}-1'
and TIME <'{2}-{5}-1' order by RAINFALL_{3}_HOUR desc;", condition.State, condition.StartTime.Year, condition.EndTime.AddMonths(1).Year, hourArry[i], condition.StartTime.Month, condition.EndTime.AddMonths(1).Month);
}
ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, commandText);
for (int i = 0; i < hourArry.Length * 2; i++)
{
if (ds.Tables[i].Rows.Count > 0)
{
if (i % 2 == 0)
{
DataRow row = ds.Tables[i].Rows[0];
RainCaculateResult temp = new RainCaculateResult();
temp.MaxValue = ConvertDecimal(row[0]);
temp.MaxValueDate = DateTime.Parse(row[1].ToString());
temp.MaxValueQc = row[2]?.ToString();
temp.EventNum = condition.EventNum;
temp.During = hourArry[i / 2];
result.Add(temp);
}
else
{
DataRow row = ds.Tables[i].Rows[0];
RainCaculateResult tempMonth = new RainCaculateResult();
tempMonth.MaxValue = ConvertDecimal(row[0]);
tempMonth.MaxValueDate = DateTime.Parse(row[1].ToString());
tempMonth.MaxValueQc = row[2]?.ToString();
tempMonth.MonthMax = true;
tempMonth.EventNum = condition.EventNum;
tempMonth.During = hourArry[(i - 1) / 2];
result.Add(tempMonth);
}
}
}
commandText = string.Empty;
//1d,2d,3d,5d,7d,15d,30d时间范围最大值
for (int i = 0; i < dayArry.Length; i++)
{
commandText += string.Format(@"SELECT top 1
RAINFALL_{3}_DAY
,TIME
,RAINFALL_{3}_DAY_C
FROM [DB_RainMonitor].[dbo].[RAINFALL_DAY]
where MONITORNUM = '{0}'
and TIME >= '{1}'
and TIME < '{2}' order by RAINFALL_{3}_DAY desc;", condition.State, condition.StartTime.ToShortDateString(), condition.EndTime.AddDays(1).ToShortDateString(), dayArry[i]);
commandText += string.Format(@"SELECT top 1
RAINFALL_{3}_DAY
,TIME
,RAINFALL_{3}_DAY_C
FROM [DB_RainMonitor].[dbo].[RAINFALL_DAY]
where MONITORNUM = '{0}'
and TIME >= '{1}-{4}-1'
and TIME <'{2}-{5}-1' order by RAINFALL_{3}_DAY desc;", condition.State, condition.StartTime.Year, condition.EndTime.AddMonths(1).Year, dayArry[i], condition.StartTime.Month, condition.EndTime.AddMonths(1).Month);
}
ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, commandText);
for (int i = 0; i < dayArry.Length * 2; i++)
{
if (ds.Tables[i].Rows.Count > 0)
{
if (i % 2 == 0)
{
DataRow row = ds.Tables[i].Rows[0];
RainCaculateResult temp = new RainCaculateResult();
temp.MaxValue = ConvertDecimal(row[0]);
temp.MaxValueDate = DateTime.Parse(row[1].ToString());
temp.MaxValueQc = row[2]?.ToString();
temp.Day = true;
temp.EventNum = condition.EventNum;
temp.During = dayArry[i / 2];
result.Add(temp);
}
else
{
DataRow row = ds.Tables[i].Rows[0];
RainCaculateResult tempMonth = new RainCaculateResult();
tempMonth.MaxValue = ConvertDecimal(row[0]);
tempMonth.MaxValueDate = DateTime.Parse(row[1].ToString());
tempMonth.MaxValueQc = row[2]?.ToString();
tempMonth.MonthMax = true;
tempMonth.Day = true;
tempMonth.EventNum = condition.EventNum;
tempMonth.During = dayArry[(i - 1) / 2];
result.Add(tempMonth);
}
}
}
return(result);
}