/// <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)));
}
public static DataTable get_VillPlotMulitData(string strsql)
{
SqlConnection con = DataBaseOperate.getSqlCon();
SqlDataAdapter sa = new SqlDataAdapter(strsql, con);
DataTable dt = new DataTable();
sa.Fill(dt);
return(convert_PlotTableValue(return_CHPlotTable(dt)));
}
/// <summary>
/// 通用的执行Adapter的sql语句返回DataTable
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public static DataTable return_DataTable(string strsql)
{
SqlConnection con = DataBaseOperate.getSqlCon();
SqlDataAdapter sa = new SqlDataAdapter(strsql, con);
DataTable dt = new DataTable();
sa.Fill(dt);
return(dt);
}
/// <summary>
/// 获取农场Code
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public static int getCountyCount()
{
string strsql = "select count(distinct QXCode) from COUNTY";
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>
/// 获取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>
/// 获取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>
/// 汇总到Town_SOILNUTRIENT
/// </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_County_SOILNUTRIENT(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>
/// 获取处在某一时间段内的时间个数，用于循环汇总
/// </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>
/// 获取到County的汇总值
/// </summary>
/// <param name="procedure_name"></param>
/// <param name="param"></param>
/// <returns></returns>
public static string get_CountyValue(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);
}
public static DataTable get_VillPlotData(SqlParameter[] param)
{
string strsql = "select * from VI_PLOT where PLOTID=@name and VI_TYPE=@VI_TYPE and VI_STATYPE=@VI_STATYPE and MONITORTIME between @date1 and @date2 and SENSORTYPE=@SENSORTYPE order by MONITORTIME asc";
SqlConnection con = DataBaseOperate.getSqlCon();
SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con);
cmd.Parameters.AddRange(param);
SqlDataAdapter sa = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sa.Fill(dt);
return(convert_PlotTableValue(return_CHPlotTable(dt)));
}
public static List <string> ReturnMultiValue(string sql)
{
List <string> nodeName = new List <string>();
SqlConnection con = DataBaseOperate.getSqlCon();
SqlCommand cmd = DataBaseOperate.getSqlCmd(sql, con);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
nodeName.Add(reader[0].ToString());
}
con.Close();
return(nodeName);
}
/// <summary>
/// 通过Reader方法获取数据列表
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public static List <string> return_DataList(string strsql)
{
List <string> list_table = new List <string>();
SqlConnection con = DataBaseOperate.getSqlCon();
SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
list_table.Add(reader[0].ToString());
}
con.Close();
return(list_table);
}
/// <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>
/// 获取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>
/// <returns></returns>
public static List <string> get_chartIndexType()
{
List <string> list = new List <string>();
SqlConnection con = DataBaseOperate.getSqlCon();
string strsql = "select distinct VINAME from VITYPEINFO";
SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con);
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
list.Add(read[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 + "' and DELFLAG=1";
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);
}
public static string get_TreeTownCode(string name, string upname)
{
string result = "";
string strsql = string.Format("select TowCode from TOWN where TownName='{0}' and QXCode='{1}'", name, upname);
SqlConnection con = DataBaseOperate.getSqlCon();
SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con);
SqlDataReader read = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (read.Read())
{
result = read[0].ToString();
}
con.Close();
return(result);
}
/// <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>
/// 获取CountyCode
/// </summary>
/// <param name="plotid"></param>
/// <returns></returns>
public static List <string> get_CountyCode()
{
List <string> list = new List <string>();
string strsql = "select distinct QXCode from COUNTY";
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>
/// <returns></returns>
public static int return_OperateSucess(string strsql)
{
SqlConnection con = DataBaseOperate.getSqlCon();
SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con);
//SqlTransaction trans = con.BeginTransaction();
//cmd.Transaction = trans;
//try
//{
int count = cmd.ExecuteNonQuery();
return(count);
//}
//catch
//{
// trans.Rollback();
// return 0;
//}
}
/// <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);
}
public static Dictionary <string, string> get_VillPlotChartData(SqlParameter[] param)
{
Dictionary <string, string> dicts = new Dictionary <string, string>();
string strsql = "select CONVERT(varchar(100), MONITORTIME, 111),VI_VALUE from VI_PLOT where PLOTID=@name and VI_TYPE=@VI_TYPE and VI_STATYPE=@VI_STATYPE and MONITORTIME between @date1 and @date2 and SENSORTYPE=@SENSORTYPE order by MONITORTIME asc";
SqlConnection con = DataBaseOperate.getSqlCon();
SqlCommand cmd = DataBaseOperate.getSqlCmd(strsql, con);
cmd.Parameters.AddRange(param);
SqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
dicts.Add(reader[0].ToString(), reader[1].ToString());
}
return(dicts);
}
catch (Exception ex)
{
MessageBox.Show("同一时间段内,存在两条记录！");
return(null);
}
}
