/// <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); }