/// <summary>
/// 保存填报数据
/// </summary>
/// <param name="dtData">填报数据</param>
/// <returns></returns>
public bool SaveMetalFillData(DataTable dtData)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("begin Transaction T\n");
strSql.Append("declare @errorCount as int\n");
strSql.Append("set @errorCount=0\n");
foreach (DataRow drData in dtData.Rows)
{
string pointId = drData["point_id"].ToString();
string month = drData["month"].ToString();
string verticalId = drData["vertical_id"].ToString();
string kpf = drData["kpf"].ToString();
string samplingDay = drData["sampling_day"].ToString();
string fillId = new TSysSerialAccess().GetSerialNumber("metal_fill_id");
//删除原有数据
strSql.AppendFormat("delete from T_ENV_FILL_METAL_ITEM where SEDIMENT_METAL_FILL_ID=(select id from T_ENV_FILL_METAL where SEDIMENT_METAL_POINT_ID='{0}' and vertical_id='{1}')\n", pointId, verticalId);
strSql.Append("set @errorCount=@errorCount+@@error\n");
strSql.AppendFormat("delete from T_ENV_FILL_METAL where SEDIMENT_METAL_POINT_ID='{0}' and vertical_id='{1}'\n", pointId, verticalId);
strSql.Append("set @errorCount=@errorCount+@@error\n");
//基础项
strSql.AppendFormat("insert into T_ENV_FILL_METAL(id,SEDIMENT_METAL_POINT_ID,month,vertical_id,kpf,sampling_day) values('{0}','{1}','{2}','{3}','{4}','{5}')\n", fillId, pointId, month, verticalId, kpf, samplingDay);
strSql.Append("set @errorCount=@errorCount+@@error\n");
//监测项
foreach (DataColumn dcData in dtData.Columns)
{
if (dcData.ColumnName.Contains("_id_unSure"))
{
string itemId = drData[dcData].ToString();
string itemValue = drData[dcData.ColumnName.Replace("_id", "")].ToString();
string fillItemId = new TSysSerialAccess().GetSerialNumber("rmetal_fill_item_id");
strSql.AppendFormat("insert into T_ENV_FILL_METAL_ITEM(id,SEDIMENT_METAL_FILL_ID,item_id,item_value) values('{0}','{1}','{2}','{3}')\n", fillItemId, fillId, itemId, itemValue);
strSql.Append("set @errorCount=@errorCount+@@error\n");
}
}
}
strSql.Append("IF @errorCount <> 0\n");
strSql.Append("begin\n");
strSql.Append("select 'fail'\n");
strSql.Append("RollBack Transaction T\n");
strSql.Append("end\n");
strSql.Append("else\n");
strSql.Append("begin\n");
strSql.Append("select 'success'\n");
strSql.Append("COMMIT Transaction T\n");
strSql.Append("end\n");
DataTable dtResult = ExecuteDataTable(strSql.ToString());
bool result = (dtResult.Rows[0][0].ToString() == "success" ? true : false);
return(result);
}
/// <summary>
/// 获取填报数据
/// </summary>
/// <param name="year">年份</param>
/// <param name="month">月份</param>
/// <returns></returns>
public DataTable GetAir30FillData(string year, string month, string areacode)
{
StringBuilder sqlStr = new StringBuilder();
sqlStr.Append("select a.*,d.DICT_TEXT as areaName from T_ENV_FILL_AIR_30 a join T_SYS_DICT d on d.DICT_TYPE='dict_30Area' and d.DICT_CODE=a.AREA_CODE where 1=1 ");
if (!string.IsNullOrEmpty(year))
{
sqlStr.AppendFormat("and a.[year]='{0}' ", year);
}
if (!string.IsNullOrEmpty(month))
{
sqlStr.AppendFormat("and a.[month]='{0}'", month);
}
if (!string.IsNullOrEmpty(areacode))
{
sqlStr.AppendFormat("and a.AREA_CODE='{0}'", areacode);
}
DataTable dt = ExecuteDataTable(sqlStr.ToString());
if (dt.Rows.Count > 0)
{
return(dt);
}
else
{
int intdays = DateTime.DaysInMonth(int.Parse(year), int.Parse(month));
StringBuilder strSql = new StringBuilder();
strSql.Append("begin Transaction T\n");
strSql.Append("declare @errorCount as int\n");
strSql.Append("set @errorCount=0\n");
for (int i = 1; i <= intdays; i++)
{
string id = new TSysSerialAccess().GetSerialNumber("air_30_id");
strSql.AppendFormat("insert into T_ENV_FILL_AIR_30(id,year,month,DAY,AREA_CODE) values('{0}','{1}','{2}','{3}','{4}')", id, year, month, i.ToString(), areacode);
strSql.Append("set @errorCount=@errorCount+@@error\n");
}
strSql.Append("IF @errorCount <> 0\n");
strSql.Append("begin\n");
strSql.Append("select 'fail'\n");
strSql.Append("RollBack Transaction T\n");
strSql.Append("end\n");
strSql.Append("else\n");
strSql.Append("begin\n");
strSql.Append("select 'success'\n");
strSql.Append("COMMIT Transaction T\n");
strSql.Append("end\n");
string result = ExecuteDataTable(strSql.ToString()).Rows[0][0].ToString();
return(ExecuteDataTable(sqlStr.ToString()));
}
}
/// <summary>
/// 保存数据
/// </summary>
/// <param name="dtData">数据集</param>
/// <returns></returns>
public string SaveAlkaliFillData(DataTable dtData)
{
if (dtData.Rows.Count > 0)
{
string year = dtData.Rows[0]["year"].ToString();
string month = dtData.Rows[0]["SMONTH"].ToString();
StringBuilder strSql = new StringBuilder();
strSql.Append("begin Transaction T\n");
strSql.Append("declare @errorCount as int\n");
strSql.Append("set @errorCount=0\n");
strSql.AppendFormat("delete from T_ENV_FILL_ALKALI where year='{0}' and SMONTH='{1}'\n", year, month);
strSql.Append("set @errorCount=@errorCount+@@error\n");
foreach (DataRow drData in dtData.Rows)
{
string pointCode = drData["POINT_NUM"].ToString();
string pointName = drData["POINT_NAME"].ToString();
string smonth = drData["SMONTH"].ToString();
string sday = drData["SDAY"].ToString();
string shour = drData["SHOUR"].ToString();
string sminute = drData["SMINUTE"].ToString();
string emonth = drData["EMONTH"].ToString();
string eday = drData["EDAY"].ToString();
string ehour = drData["EHOUR"].ToString();
string eminute = drData["EMINUTE"].ToString();
string valuea = drData["VALUEA"].ToString();
string id = new TSysSerialAccess().GetSerialNumber("alkali_fill_id");
strSql.AppendFormat("insert into T_ENV_FILL_ALKALI(id,POINT_CODE,POINT_NUM,point_name,year,SMONTH,SDAY,SHOUR,SMINUTE,EMONTH,EDAY,EHOUR,EMINUTE,VALUEA) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')", id, "130300", pointCode, pointName, year, smonth, sday, shour, sminute, emonth, eday, ehour, eminute, valuea);
strSql.Append("set @errorCount=@errorCount+@@error\n");
}
strSql.Append("IF @errorCount <> 0\n");
strSql.Append("begin\n");
strSql.Append("select 'fail'\n");
strSql.Append("RollBack Transaction T\n");
strSql.Append("end\n");
strSql.Append("else\n");
strSql.Append("begin\n");
strSql.Append("select 'success'\n");
strSql.Append("COMMIT Transaction T\n");
strSql.Append("end\n");
string result = ExecuteDataTable(strSql.ToString()).Rows[0][0].ToString();
return(result);
}
else
{
return("");
}
}
public TSysSerialLogic(TSysSerialVo _tSysSerial)
{
tSysSerial = _tSysSerial;
access = new TSysSerialAccess();
}
public TSysSerialLogic()
{
access = new TSysSerialAccess();
}
/// <summary>
/// 保存填报数据
/// </summary>
/// <param name="dtData">填报数据</param>
/// <param name="pointId">监测点ID</param>
/// <returns></returns>
public bool SaveRainFillData(DataTable dtData, string pointId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("begin Transaction T\n");
strSql.Append("declare @errorCount as int\n");
strSql.Append("set @errorCount=0\n");
string beginMonth = dtData.Rows[0]["begin_month"].ToString(); //月份
//删除原有数据
strSql.AppendFormat("delete from T_ENV_FILL_RAIN_ITEM where RAIN_FILL_ID in(select id from T_ENV_FILL_RAIN where RAIN_POINT_ID='{0}' and BEGIN_MONTH='{1}')\n", pointId, beginMonth);
strSql.AppendFormat("set @errorCount=@errorCount+@@error\n");
strSql.AppendFormat("delete from T_ENV_FILL_RAIN where RAIN_POINT_ID='{0}' and BEGIN_MONTH='{1}'\n", pointId, beginMonth);
strSql.AppendFormat("set @errorCount=@errorCount+@@error\n");
foreach (DataRow drData in dtData.Rows)
{
string beginDay = drData["begin_day"].ToString();
string beginHour = drData["begin_hour"].ToString();
string beginMinute = drData["begin_minute"].ToString();
string endMonth = drData["end_month"].ToString();
string endDay = drData["end_day"].ToString();
string endHour = drData["end_hour"].ToString();
string endMinute = drData["end_minute"].ToString();
string rainType = drData["rain_type"].ToString();
string precipitation = drData["precipitation"].ToString();
string fillId = new TSysSerialAccess().GetSerialNumber("rain_fill_id");
//基础项
strSql.AppendFormat("insert into T_ENV_FILL_RAIN(id,RAIN_POINT_ID,begin_month,begin_day,begin_hour,begin_minute,end_month,end_day,end_hour,end_minute,rain_type,precipitation) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')\n", fillId, pointId, beginMonth, beginDay, beginHour, beginMinute, endMonth, endDay, endHour, endMinute, rainType, precipitation);
strSql.AppendFormat("set @errorCount=@errorCount+@@error\n");
//监测项
foreach (DataColumn dcData in dtData.Columns)
{
if (dcData.ColumnName.Contains("_id_unSure"))
{
string itemId = drData[dcData].ToString();
string itemValue = drData[dcData.ColumnName.Replace("_id", "")].ToString();
string fillItemId = new TSysSerialAccess().GetSerialNumber("rain_fill_item_id");
strSql.AppendFormat("insert into T_ENV_FILL_RAIN_ITEM(id,RAIN_FILL_ID,item_id,item_value) values('{0}','{1}','{2}','{3}')\n", fillItemId, fillId, itemId, itemValue);
strSql.AppendFormat("set @errorCount=@errorCount+@@error\n");
}
}
}
strSql.Append("IF @errorCount <> 0\n");
strSql.Append("begin\n");
strSql.Append("select 'fail'\n");
strSql.Append("RollBack Transaction T\n");
strSql.Append("end\n");
strSql.Append("else\n");
strSql.Append("begin\n");
strSql.Append("select 'success'\n");
strSql.Append("COMMIT Transaction T\n");
strSql.Append("end\n");
DataTable dtResult = ExecuteDataTable(strSql.ToString());
bool result = (dtResult.Rows[0][0].ToString() == "success" ? true : false);
return(result);
}
