public static List <string> getThirdName(string levelName)
{
string code = DataBaseOperate.get_SHICode(levelName);
string strsql = string.Format("SELECT [CountyName] FROM [COUNTY] where SHICode='{0}'", code);
return(ReturnMultiValue(strsql));
}
/// <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 List <string> getSixthName(string Fifthname)
{
string code = DataBaseOperate.getVillCode(Fifthname);
string strsql = string.Format("SELECT FULLNAME FROM PLOT_DKINFO where JMZ='{0}'", code);
return(ReturnMultiValue(strsql));
}
public static List <string> getSecondName(string levelName)
{
string code = DataBaseOperate.get_ProCode(levelName);
string strsql = string.Format("SELECT [CityName] FROM [CITY] where ProCode='{0}'", code);
return(ReturnMultiValue(strsql));
}
private void btn_Chart_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(rankCHname))
{
typename = DataManager.get_TableEName(cmb_Type.SelectedItem.ToString());
tablename = typename + "_" + rankname;
if (!tablename.Contains("SOILNUTRIENT"))
{
ChartObject co = new ChartObject();
co.cropcode = DataBaseOperate.get_CropCode(cmbCropType.SelectedItem.ToString());
co.datemin = dT_maize_s.Value;
co.datemax = dT_maize_e.Value;
co.treeNodeEName = treeNodeEName;
co.chartType = cmb_ChartType.SelectedItem.ToString();
BindDataSource(co);
}
else
{
ChartObject co = new ChartObject();
co.cropcode = DataBaseOperate.get_CropCode(cmbCropType.SelectedItem.ToString());
co.datemin = dT_maize_s.Value;
co.datemax = dT_maize_e.Value;
co.treeNodeEName = treeNodeEName;
co.nutrientcode = DataBaseOperate.get_NutrientCode(cmb_Nutrient.SelectedItem.ToString());
co.chartType = cmb_ChartType.SelectedItem.ToString();
BindSoilDataSource(co);
}
InitDataSet();
}
else
{
MessageBox.Show("请选择监测类型！");
}
}
private void btn_InDatabase_Click(object sender, EventArgs e)
{
int result = 0;
string sVI_TYPE = this.cbx_VIType.Text.Trim();
string sVI_STATYPE = this.cbx_StaValueType.Text.Trim();
float fVI = 0.00f;
for (int i = 1; i < dtInfo.Rows.Count - 1; i++)
{
string str = dtInfo.Rows[i][5].ToString();
bool b = float.TryParse(str, out fVI);
if (!b || str.Contains("正"))
{
str = "0.00";
}
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@PLOTID", dtInfo.Rows[i]["地块编号"]),
new SqlParameter("@MONITORTIME", dtInfo.Rows[i]["监测时间"]),
new SqlParameter("@CROP_CODE", 10),
new SqlParameter("@VI_TYPE", sVI_TYPE),
new SqlParameter("@VI_STATYPE", sVI_STATYPE),
new SqlParameter("@VI_VALUE", str),
new SqlParameter("@SENSORTYPE", 1),
new SqlParameter("@RECORDTIME", DateTime.Now)
};
result = DataBaseOperate.InsertDatabase("insert_Plot_VI", param);
}
if (result > 0)
{
MessageBox.Show("入库成功！");
}
}
/// <summary>
/// 返回满足查询条件的数据表
/// </summary>
/// <param name="tablename"></param>
/// <param name="cropcode"></param>
/// <returns></returns>
public static DataTable get_SelectResult(string tablename, string cropName, DateTime date1, DateTime date2)
{
string cropcode = DataBaseOperate.get_CropCode(cropName);
string strsql = string.Format("select * from {0} where CROP_CODE='{1}' and MONITORTIME between '{2}' and '{3}'", tablename, cropcode, date1, date2);
return(return_DataTable(strsql));
}
private void btn_Input_Click(object sender, EventArgs e)
{
int reslut = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
string TownCode = DataBaseOperate.getTownCode(dt.Rows[i][0].ToString());
DateTime MONITORTIME = Convert.ToDateTime(dt.Rows[i][1]);
string CROP_CODE = DataBaseOperate.get_CropCode(dt.Rows[i][2].ToString());
string nutrient_code = DataBaseOperate.get_NutrientCode(dt.Rows[i][3].ToString());
float value = float.Parse(dt.Rows[i][4].ToString());
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@TOWNCODE", TownCode),
new SqlParameter("@MONITORTIME", MONITORTIME),
new SqlParameter("@CROP_CODE", CROP_CODE),
new SqlParameter("@NUTRIENT_CODE", nutrient_code),
new SqlParameter("@SOIL_NUTRIENT", value),
new SqlParameter("@RECORDTIME", DateTime.Now.ToShortDateString())
};
reslut = AggregateToTown.insert_Town_SOILNUTRIENT("insert_town_SOILNUTRIENT", param);
}
if (reslut > 0)
{
MessageBox.Show("数据库入库成功！");
}
}
public static List <string> getForthName(string levelName)
{
string code = DataBaseOperate.get_QXCode(levelName);
string strsql = string.Format("SELECT [TownName] FROM [TOWN] where QXCode='{0}'", code);
return(ReturnMultiValue(strsql));
}
/// <summary>
///传感器类型中文名
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static string get_SensorNAME(string name)
{
string strsql = "select SENSORNAME from SENSORTYPEINFO where SENSORTYPE=@name";
SqlParameter parm = new SqlParameter("@name", name);
string result = DataBaseOperate.getResult(strsql, parm);
return(result);
}
/// <summary>
/// 返回数据库中字段的中文名
/// </summary>
/// <param name="tablename"></param>
/// <returns></returns>
public static string get_TableCHName(string tablename)
{
string strsql = "select TableCHName from DIC_TABLENAME where TableEName=@name";
SqlParameter param = new SqlParameter("@name", tablename);
string result = DataBaseOperate.getResult(strsql, param);
return(result);
}
public static DataTable getVillageSelectResult(string tablename, string cropName, DateTime date1, DateTime date2, string townname)
{
string cropcode = DataBaseOperate.get_CropCode(cropName);
string towncode = DataBaseOperate.getTownCode(townname);
string strsql = string.Format("select * from {0} where CROP_CODE='{1}' and MONITORTIME between '{2}' and '{3}' and VILLAGECODE in (select VillCode from VILLAGE where TowCode='{4}')", tablename, cropcode, date1, date2, towncode);
return(return_DataTable(strsql));
}
public static DataTable getPlotSelectResult(string tablename, string cropName, DateTime date1, DateTime date2, string Villagename)
{
string cropcode = DataBaseOperate.get_CropCode(cropName);
string Villagecode = DataBaseOperate.getVillCode(Villagename);
string strsql = string.Format("select * from {0} where CROP_CODE='{1}' and MONITORTIME between '{2}' and '{3}' and PLOTID in (select PlotID from PLOT_DKINFO where JMZ='{4}')", tablename, cropcode, date1, date2, Villagecode);
return(return_DataTable(strsql));
}
/// <summary>
///指标类型代码
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static string get_VITYPE(string name)
{
string strsql = "select VITYPE from VITYPEINFO where VINAME=@name";
SqlParameter parm = new SqlParameter("@name", name);
string result = DataBaseOperate.getResult(strsql, parm);
return(result);
}
public static List <string> getFifthName(string forthname, string thirdname)
{
string code1 = DataBaseOperate.get_QXCode(thirdname);
string code = DataBaseOperate.get_TreeTownCode(forthname, code1);
string strsql = string.Format("SELECT VillName FROM VILLAGE where TowCode='{0}'", code);
return(ReturnMultiValue(strsql));
}
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);
}
private void treeView2_AfterSelect(object sender, TreeViewEventArgs e)
{
if (e.Node.Level == 5)
{
plotname = DataBaseOperate.getChartPlotId(e.Node.Text);
}
else
{
plotname = "";
}
}
/// <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>
/// 获取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_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="dt"></param>
/// <returns></returns>
public static DataTable convert_PlotTableValue(DataTable dt)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][0] = DataBaseOperate.get_PlotName(dt.Rows[i][0].ToString());
dt.Rows[i][2] = DataBaseOperate.get_CropCHName(dt.Rows[i][2].ToString());
dt.Rows[i][4] = get_VINAME(dt.Rows[i][4].ToString());
dt.Rows[i][5] = get_STANAME(dt.Rows[i][5].ToString());
dt.Rows[i][6] = get_SensorNAME(dt.Rows[i][6].ToString());
}
return(dt);
}
/// <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="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);
}
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)));
}
/// <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);
}
/// <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>
/// 处在选择的时间范围内的天数
/// </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);
}