/// <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="time1">时间1</param>
/// <param name="time2">时间2</param>
/// <param name="corpName">具体表</param>
/// <returns></returns>
public static List <string> get_DateDetail(DateTime time1, DateTime time2, string corpName)
{
string str_datevalue = "select distinct MONITORTIME from " + corpName + " where MONITORTIME between @time1 and @time2";
SqlParameter[] param_date = new SqlParameter[] {
new SqlParameter("@time1", time1),
new SqlParameter("@time2", time2)
};
List <string> date_list = DataBaseOperate.getIncludeTime(str_datevalue, param_date);
return(date_list);
}
/// <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="time1">时间1</param>
/// <param name="time2">时间2</param>
/// <param name="corpName">具体表</param>
/// <returns></returns>
public static int get_DateCount(DateTime time1, DateTime time2, string corpName)
{
string str_datecount = "select count(distinct MONITORTIME) from " + corpName + " where MONITORTIME between @time1 and @time2";
SqlParameter[] param_date = new SqlParameter[] {
new SqlParameter("@time1", time1),
new SqlParameter("@time2", time2)
};
int count = DataBaseOperate.getIncludeTimeCount(str_datecount, param_date);
return(count);
}
/// <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>
/// 获取养分类型
/// </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>
/// 根据作物名称获取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>
/// 获取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);
}
/// <summary>
/// 转换表中的值
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static DataTable Convert_TableValue(DataTable dt)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Columns.Count == 4) //根据输出列数判断是否是Soil
{
if (dt.Columns[0].ColumnName == "作业站") //根据列名判断作物范围
{
dt.Rows[i][0] = DataBaseOperate.getVillName(dt.Rows[i][0].ToString());
}
else if (dt.Columns[0].ColumnName == "作业区")
{
dt.Rows[i][0] = DataBaseOperate.getTownName(dt.Rows[i][0].ToString());
}
dt.Rows[i][1] = ((DateTime)dt.Rows[i][1]).ToShortDateString();
dt.Rows[i][2] = DataBaseOperate.get_CropCHName(dt.Rows[i][2].ToString());
dt.Rows[i][3] = Math.Round((double)dt.Rows[i][3], 3);
}
else //soil
{
if (dt.Columns[0].ColumnName == "作业站")
{
dt.Rows[i][0] = DataBaseOperate.getVillName(dt.Rows[i][0].ToString());
}
else if (dt.Columns[0].ColumnName == "作业区")
{
dt.Rows[i][0] = DataBaseOperate.getTownName(dt.Rows[i][0].ToString());
}
dt.Rows[i][1] = ((DateTime)dt.Rows[i][1]).ToShortDateString();
dt.Rows[i][2] = DataBaseOperate.get_CropCHName(dt.Rows[i][2].ToString());
dt.Rows[i][3] = DataBaseOperate.get_NutrientCHName(dt.Rows[i][3].ToString());
dt.Rows[i][4] = Math.Round((double)dt.Rows[i][4], 4);
}
}
return(dt);
}
