/// <summary> /// 返回作业区符合条件的非Soil表 /// </summary> /// <param name="tablename"></param> /// <param name="datetime"></param> /// <param name="cropcode"></param> /// <returns></returns> public static DataTable Return_TownTable(string tablename, string datetime, string cropcode) { string sqlstr = ""; switch (tablename) { case "CHLOROPHYLLRETRIEVAL": sqlstr = "select TOWNCODE,MONITORTIME,CROP_CODE,CHLOROPHYLLVALUE FROM CHLOROPHYLLRETRIEVAL_TOWN where MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "'"; break; case "CROPYIELD": sqlstr = "select TOWNCODE,MONITORTIME,CROP_CODE,CROP_YIELD FROM CROPYIELD_TOWN where MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "'"; break; case "WATERRETRIEVAL": sqlstr = "select TOWNCODE,MONITORTIME,CROP_CODE,WATERVALUE FROM WATERRETRIEVAL_TOWN where MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "'"; break; case "MATUREPERIOD": sqlstr = "select TOWNCODE,MONITORTIME,CROP_CODE,MATURE_PERIOD FROM MATUREPERIOD_TOWN where MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "'"; break; } SqlConnection con = DataBaseOperate.getSqlCon(); SqlDataAdapter sa = new SqlDataAdapter(sqlstr, con); DataTable dt = new DataTable(); sa.Fill(dt); return(Convert_TableValue(Convert_TableName(dt))); }
/// <summary> /// 获取TownCount /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static int get_TownCount() { List <string> list = new List <string>(); string strsql = "select count(distinct GLQ) from PLOT_DKINFO"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); int count = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); return(count); }
/// <summary> /// 获取CropCount /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static int get_CropCount() { List <string> list = new List <string>(); string strsql = "select count(distinct CropCode) from CROPINFO where DELFLAG=1"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); int count = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); return(count); }
/// <summary> /// 获取NutrientCount /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static int get_NutrientCount() { List <string> list = new List <string>(); string strsql = "select count(distinct NUTRIENT_CODE) from SOILNUTRIENT_CODE where DELFLAG='1'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); int count = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); return(count); }
/// <summary> /// 汇总到Town /// </summary> /// <param name="VILLAGECODE"></param> /// <param name="MONITORTIME"></param> /// <param name="CROP_CODE"></param> /// <param name="SOIL_NUTRIENT"></param> /// <returns></returns> public static int insert_Town(String procedure_name, SqlParameter[] param) { SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(procedure_name, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(param); int reslut = cmd.ExecuteNonQuery(); con.Close(); return(reslut); }
/// <summary> /// 数据入库 /// </summary> /// <param name="strsql"></param> /// <param name="param"></param> public static int InsertDatabase(string sqlProcedure, SqlParameter[] param) { SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(sqlProcedure, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(param); int result = cmd.ExecuteNonQuery(); con.Close(); return(result); }
/// <summary> /// 获取到Town的汇总值 /// </summary> /// <param name="procedure_name"></param> /// <param name="param"></param> /// <returns></returns> public static string get_TownValue(String procedure_name, SqlParameter[] param) { SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(procedure_name, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(param); cmd.ExecuteNonQuery(); string value = param[3].Value.ToString(); con.Close(); return(value); }
/// <summary> /// 获取处在某一时间段内的时间个数,用于循环汇总 /// </summary> /// <param name="strsql"></param> /// <param name="param"></param> /// <returns></returns> public static int getIncludeTimeCount(string strsql, SqlParameter[] param) { //string strsql = "select count(MONITORTIME) from MONITORTIME between @time1 and @time2"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); cmd.Parameters.AddRange(param); int count = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); cmd.Parameters.Clear(); return(count); }
/// <summary> /// 非Soil图片入库 /// </summary> /// <param name="list_picname"></param> public static void InsertPicture(string[] list_picname) { byte[] buffer = Return_Buffer(); //string strSql = "insert into MONITOR_PICTURE(INDICATOR_NAME,CROP_CODE,MONITORTIME,PIC_MAP) values(@INDICATOR_NAME,@CROP_CODE,@MONITORTIME,@PIC_MAP)"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = new SqlCommand("insert_Picture", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@INDICATOR_NAME", list_picname[0]); cmd.Parameters.AddWithValue("@CROP_CODE", DataBaseOperate.getCrop_Code(list_picname[1])); cmd.Parameters.AddWithValue("@MONITORTIME", Convert.ToDateTime(list_picname[2]).ToShortDateString()); cmd.Parameters.AddWithValue("@PIC_MAP", buffer); cmd.ExecuteNonQuery(); }
/// <summary> /// 根据作物名称获取Nutrient_code /// </summary> /// <param name="name"></param> /// <returns></returns> public static string get_NutrientCode(string name) { string value = ""; string strsql = "select NUTRIENT_CODE from SOILNUTRIENT_CODE where [NUTRIENT_NAME]='" + name + "'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { value = reader[0].ToString(); } con.Close(); return(value); }
/// <summary> /// 获取养分类型 /// </summary> /// <param name="Crop_Code"></param> /// <param name="date1"></param> /// <param name="date2"></param> /// <returns></returns> public static List <string> Get_NuterientCount(string Crop_Code, DateTime date1, DateTime date2) { List <string> list = new List <string>(); string sqlstr = "select distinct NUTRIENT_CODE FROM SOILNUTRIENT_VILLAGE where CROP_CODE='" + Crop_Code + "' and " + "MONITORTIME between '" + date1 + "' and '" + date2 + "'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(sqlstr, con); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { list.Add(dr["NUTRIENT_CODE"].ToString()); } con.Close(); return(list); }
/// <summary> /// 获取CropCode /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static List <string> get_CropCode() { List <string> list = new List <string>(); string strsql = "select distinct CropCode from CROPINFO where DELFLAG=1"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { list.Add(reader[0].ToString()); } con.Close(); return(list); }
/// <summary> /// 根据作物名称获取crop_code /// </summary> /// <param name="name"></param> /// <returns></returns> public static string get_CropCode(string name) { string value = ""; string strsql = "select CropCode from CROPINFO where [CropName]='" + name + "'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { value = reader[0].ToString(); } con.Close(); return(value); }
/// <summary> /// 获取TownCode /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static List <string> get_TownCode() { List <string> list = new List <string>(); string strsql = "select distinct GLQ from PLOT_DKINFO"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { list.Add(reader[0].ToString()); } con.Close(); return(list); }
/// <summary> /// 获取作物代码和名称,并绑定数据源 /// </summary> /// <returns></returns> public static Dictionary <string, string> setCropSource() { Dictionary <string, string> source = new Dictionary <string, string>(); string strsql = "select [CropCode],[CropName] from CROPINFO where DELFLAG=1"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { source.Add(reader["CropCode"].ToString(), reader["CropName"].ToString()); } con.Close(); return(source); }
/// <summary> /// 获取NutrientCode /// </summary> /// <param name="plotid"></param> /// <returns></returns> public static List <string> get_NutrientCode() { List <string> list = new List <string>(); string strsql = "select distinct NUTRIENT_CODE from SOILNUTRIENT_CODE where DELFLAG='1'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { list.Add(reader[0].ToString()); } con.Close(); return(list); }
/// <summary> /// 获取结果 /// </summary> /// <param name="strsql"></param> /// <param name="param"></param> /// <returns></returns> public static string getResult(string strsql, SqlParameter param) { string result = ""; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); cmd.Parameters.Add(param); SqlDataReader read = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (read.Read()) { result = read[0].ToString(); } con.Close(); return(result); }
/// <summary> /// 返回作业站符合条件的Soil表 /// </summary> /// <param name="tablename"></param> /// <param name="datetime"></param> /// <param name="cropcode"></param> /// <returns></returns> public static DataTable Return_VillageSoilTable(string datetime, string cropcode, string nuterient) { string sqlstr = "select VILLAGECODE,MONITORTIME,CROP_CODE,NUTRIENT_CODE,SOIL_NUTRIENT FROM SOILNUTRIENT_VILLAGE where MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "' and NUTRIENT_CODE='" + nuterient + "'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlDataAdapter sa = new SqlDataAdapter(sqlstr, con); DataTable dt = new DataTable(); sa.Fill(dt); if (dt.Rows.Count != 0) { return(Convert_TableValue(Convert_TableName(dt))); } else { return(null); } }
/// <summary> /// 获取处在某一时间段内的具体时间,用于汇总传参 /// </summary> /// <param name="strsql"></param> /// <param name="param"></param> /// <returns></returns> public static List <string> getIncludeTime(string strsql, SqlParameter[] param) { List <string> list = new List <string>(); //string strsql = "select MONITORTIME from MONITORTIME between @time1 and @time2"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con); cmd.Parameters.AddRange(param); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { list.Add(reader[0].ToString()); } con.Close(); cmd.Parameters.Clear(); return(list); }
/// <summary> /// 将图片流输出为文件 /// </summary> /// <param name="tablename"></param> public static string ExportSoilPicture(string type, string datetime, string cropcode, string nurient) { byte[] imagebytes; string datetimeforamt = datetime.Substring(0, datetime.LastIndexOf(" ")).Replace('/', '-'); string cropName = DataBaseOperate.get_CropCHName(cropcode); string nutrientName = DataBaseOperate.get_NutrientCHName(nurient); string pathname = Application.StartupPath + @"\Image\" + type + "_" + datetimeforamt + "_" + cropName + "_" + nutrientName + ".jpg"; string strSql = "select PIC_MAP from MONITOR_PICTURE where INDICATOR_NAME='" + type + "' and MONITORTIME='" + datetime + "' and CROP_CODE='" + cropcode + "' and NUTRIENT_CODE='" + nurient + "'"; SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = new SqlCommand(strSql, con); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); string value_path = ""; while (reader.Read()) { //获取图片数据 imagebytes = (byte[])reader["PIC_MAP"]; //将内存流格式化为位图 if (imagebytes.Length > 0) { MemoryStream stream = new MemoryStream(imagebytes); if (File.Exists(pathname)) { File.Delete(pathname); } Image image = Image.FromStream(stream); image.Save(pathname); stream.Close(); image.Dispose(); value_path = pathname; } else { value_path = ""; } } con.Close(); return(value_path); }
/// <summary> /// 获取作物类型 /// </summary> /// <param name="tablename"></param> /// <param name="date1"></param> /// <param name="date2"></param> /// <returns></returns> public static List <string> Get_CropCount(string rank, string tablename, DateTime date1, DateTime date2) { List <string> list = new List <string>(); string sqlstr = ""; switch (tablename) { case "CHLOROPHYLLRETRIEVAL": sqlstr = "select distinct CROP_CODE FROM CHLOROPHYLLRETRIEVAL_" + rank + " where MONITORTIME between '" + date1 + "' and '" + date2 + "'"; break; case "CROPYIELD": sqlstr = "select distinct CROP_CODE FROM CROPYIELD_" + rank + " where MONITORTIME between '" + date1 + "' and '" + date2 + "'"; break; case "WATERRETRIEVAL": sqlstr = "select distinct CROP_CODE FROM WATERRETRIEVAL_" + rank + " where MONITORTIME between '" + date1 + "' and '" + date2 + "'"; break; case "MATUREPERIOD": sqlstr = "select distinct CROP_CODE FROM MATUREPERIOD_" + rank + " where MONITORTIME between '" + date1 + "' and '" + date2 + "'"; break; case "SOILNUTRIENT": sqlstr = "select distinct CROP_CODE FROM SOILNUTRIENT_" + rank + " where MONITORTIME between '" + date1 + "' and '" + date2 + "'"; break; } SqlConnection con = DataBaseOperate.getSqlCon(); SqlCommand cmd = DataBaseOperate.getSqlCmd(sqlstr, con); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { list.Add(dr["CROP_CODE"].ToString()); } con.Close(); return(list); }