/*
* Pull's data with station and date time range
*/
public string PullDataPGDB_station(tenday ten, string station)
{
ConnDB conn = new ConnDB();
int year = Int32.Parse(ten.year);
int month = Int32.Parse(ten.month);
int num_of_month = Int32.Parse(ten.num_of_month);
string sql = "select t_800_80.fid,t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.WW_Max,"
+ "t_800_80.TTT_Aver,t_800_80.TTT_Max ,t_800_80.Num_of_Tmax ,t_800_80.TTT_Min,t_800_80.Sum_of_RRR ,t_800_80.TxTxTxAver,"
+ "t_800_80.txtxtx_max,t_800_80.num_of_tmin,t_800_80.Num_of_RRR ,t_800_80.Num_of_Tx_Max,t_800_80.TxTxTx_Min,station2.lat,station2.lon " +
"from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex where t_800_80.year = " + year + " and t_800_80.month = " + month + " and t_800_80.num_of_month = " + num_of_month + " and t_800_80.sindex =" + station + "";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(ConstructModel(DataTableToList(data.Tables[0])));
return(JsonString);
}
public string only_pull_percipitation(string p_max, tenday startdate, tenday enddate)
{
ConnDB conn = new ConnDB();
string sql = "";
if (string.IsNullOrWhiteSpace(p_max))
{
p_max = get_perc_max();
sql = "select t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.sum_of_rrr,station2.lat,station2.lon from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex " +
" where t_800_80.year between " + startdate.year + " and " + enddate.year + " and t_800_80.month between " + startdate.month + " and " + enddate.month + " " +
"and t_800_80.num_of_month between " + startdate.num_of_month + " and " + enddate.num_of_month + " and sum_of_rrr <= " + p_max + " ";
}
else
{
sql = "select t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.sum_of_rrr,station2.lat,station2.lon from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex " +
" where t_800_80.year between " + startdate.year + " and " + enddate.year + " and t_800_80.month between " + startdate.month + " and " + enddate.month + " " +
"and t_800_80.num_of_month between " + startdate.num_of_month + " and " + enddate.num_of_month + " and sum_of_rrr <= " + p_max + " ";
}
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(PercTableToList(data.Tables[0]));
return(JsonString);
}
/*
* 2020-09-23
*/
public string multi_station(string[] sindex)
{
ConnDB conn = new ConnDB();
DataTable bgtable = new DataTable();
DataSet data = new DataSet();
for (int i = 0; i < sindex.Length; i++)
{
string sql = "select t_800_80.fid,t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.WW_Max,"
+ "t_800_80.TTT_Aver,t_800_80.TTT_Max ,t_800_80.Num_of_Tmax ,t_800_80.TTT_Min,t_800_80.Sum_of_RRR ,t_800_80.TxTxTxAver,"
+ "t_800_80.txtxtx_max,t_800_80.num_of_tmin,t_800_80.Num_of_RRR ,t_800_80.Num_of_Tx_Max,t_800_80.TxTxTx_Min,station2.lat,station2.lon " +
"from t_800_80 inner join station2 on t_800_80.sindex = " + sindex[i] + "";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
da.Fill(data);
}
conn.ClosePG();
for (int i = 0; i < data.Tables.Count; i++)
{
bgtable.Merge(data.Tables[i]);
}
string JsonString = "";
JsonString = JsonConvert.SerializeObject(ConstructModel(DataTableToList(bgtable)));
return(JsonString);
}
public string PullDataPG()
{
ConnDB conn = new ConnDB();
/*string sql = "select t_800_80.fid,t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.WW_Max,t_800_80.TTT_Aver,t_800_80.TTT_Max,t_800_80.Num_of_TMax," +
* "t_800_80.TTT_Min,t_800_80.Sum_of_RRR,t_800_80.TxTxTxAver,t_800_80.Num_of_RRR,t_800_80.Num_of_Tx_Max,t_800_80.TxTxTx_Min" +
* " ,station2.lat,station2.lon from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex";*/
string sql = "select t_800_80.fid,t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.WW_Max,"
+ "t_800_80.TTT_Aver,t_800_80.TTT_Max ,t_800_80.Num_of_Tmax ,t_800_80.TTT_Min,t_800_80.Sum_of_RRR ,t_800_80.TxTxTxAver,"
+ "t_800_80.txtxtx_max,t_800_80.num_of_tmin,t_800_80.Num_of_RRR ,t_800_80.Num_of_Tx_Max,t_800_80.TxTxTx_Min,station2.lat,station2.lon " +
"from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(ConstructModel(DataTableToList(data.Tables[0])));
return(JsonString);
}
public string only_perc(string station)
{
ConnDB conn = new ConnDB();
string sql = "";
if (string.IsNullOrWhiteSpace(station))
{
sql = "select * from agro_catal_p";
}
else
{
int sta = Int32.Parse(station);
sql = "select * from agro_catal_p index =" + sta + "";
}
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(catalt(data.Tables[0]));
return(JsonString);
}
public string PullDataPGDB_station(tenday ten, string station)
{
ConnDB conn = new ConnDB();
int year = Int32.Parse(ten.year);
int month = Int32.Parse(ten.month);
int num_of_month = Int32.Parse(ten.num_of_month);
string sql = "SELECT t_800_93.id, t_800_93.sindex, t_800_93.year, t_800_93.month, t_800_93.num_of_month, t_800_93.height_snow_west, t_800_93.density_snow_west, t_800_93.height_snow_north, t_800_93.density_snow_north, t_800_93.height_snow_east, t_800_93.density_snow_east, t_800_93.height_snow_south, t_800_93.density_snow_south, t_800_93.field_of_west, t_800_93.field_of_north, t_800_93.field_of_east, t_800_93.field_of_south, t_800_93.update_time " +
",station2.lat,station2.lon" +
" from t_800_93 inner join station2 on t_800_93.sindex = station2.sindex where t_800_93.year = " + year + " and t_800_93.month = " + month + " and t_800_93.num_of_month = " + num_of_month + " and t_800_93.sindex =" + station + "";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(ConstructModel(DataTableToList(data.Tables[0])));
return(JsonString);
}
public string only_pull_temp(string ttt_aver, string ttt_max, string ttt_min, tenday startdate, tenday enddate)
{
ConnDB conn = new ConnDB();
string tmax, tmin, sql = "";
/*
* if(string.IsNullOrWhiteSpace(ttt_aver) || string.IsNullOrWhiteSpace(ttt_max) || string.IsNullOrWhiteSpace(ttt_min) )
* {
*
* }*/
if (string.IsNullOrWhiteSpace(ttt_max))
{
tmax = get_max_temp();
}
else
{
tmax = ttt_max;
}
if (string.IsNullOrWhiteSpace(ttt_min))
{
tmin = get_min_temp();
}
else
{
tmin = ttt_min;
}
if (string.IsNullOrWhiteSpace(ttt_aver))
{
sql = "select t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.ttt_aver,t_800_80.ttt_max,t_800_80.ttt_min,t_800_80.num_of_tmin,t_800_80.num_of_tmax,station2.lat,station2.lon from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex " +
" where t_800_80.year between " + startdate.year + " and " + enddate.year + " and t_800_80.month between " + startdate.month + " and " + enddate.month + " " +
"and t_800_80.num_of_month between " + startdate.num_of_month + " and " + enddate.num_of_month + " and ttt_min >= " + tmin + " and ttt_max <=" + tmax + "";
}
else
{
sql = "select t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.ttt_aver,t_800_80.ttt_max,t_800_80.ttt_min,t_800_80.num_of_tmin,t_800_80.num_of_tmax,station2.lat,station2.lon from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex " +
" where t_800_80.year between " + startdate.year + " and " + enddate.year + " and t_800_80.month between " + startdate.month + " and " + enddate.month + " " +
"and t_800_80.num_of_month between " + startdate.num_of_month + " and " + enddate.num_of_month + " and ttt_min >= " + tmin + " and ttt_max <=" + tmax + " and ttt_aver >=" + ttt_aver + "";
}
/* string sql = "select t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.ttt_aver,t_800_80.ttt_max,t_800_80.ttt_min,t_800_80.num_of_tmin,t_800_80.num_of_tmax,station2.lat,station2.lon from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex " +
* " where t_800_80.year between "+startdate.year+" and "+enddate.year+" and t_800_80.month between "+startdate.month+" and "+enddate.month+" " +
* "and t_800_80.num_of_month between "+startdate.num_of_month+" and "+enddate.num_of_month+" ";
*/
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(TempTableToList(data.Tables[0]));
return(JsonString);
}
public string get_perc_max()
{
ConnDB conn = new ConnDB();
string sql = "select max(sum_of_rrr) from t_800_80";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataReader reader = cmd.ExecuteReader();
string t_min = "";
while (reader.Read())
{
t_min = Convert.ToString(reader["max"]);
}
conn.ClosePG();
return(t_min);
}
public string udur()
{
ConnDB conn = new ConnDB();
DataSet data = new DataSet();
string sql = "select distinct t_800_83.month , t_800_83.year, t_800_83.num_of_month from t_800_83 order by t_800_83.year,t_800_83.month,t_800_83.num_of_month ASC";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(GaragTableToList(data.Tables[0]));
return(JsonString);
}
public string GetCatal_station(int station)
{
ConnDB conn = new ConnDB();
string sql = "select * from agro_catal_t where index = " + station + "";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(data.Tables[0]);
return(JsonString);
}
public string PullDataPG()
{
ConnDB conn = new ConnDB();
string sql = "select t_800_83.sindex,t_800_83.year,t_800_83.month,t_800_83.num_of_month,t_800_83.height_of_snow,t_800_83.density_of_snow,t_800_83.field_of_snow,station2.lat,station2.lon " +
"from t_800_83 inner join station2 on t_800_83.sindex = station2.sindex";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(ConstructModel(DataTableToList(data.Tables[0])));
return(JsonString);
}
public string only_pull_soil(string tx_aver, string tx_max, string tx_min, tenday startdate, tenday enddate)
{
ConnDB conn = new ConnDB();
string sql = "";
if (string.IsNullOrWhiteSpace(tx_min))
{
tx_max = get_soil_max();
}
if (string.IsNullOrWhiteSpace(tx_min))
{
tx_min = get_soil_min();
}
if (string.IsNullOrWhiteSpace(tx_aver))
{
sql = "select t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.txtxtxaver,t_800_80.txtxtx_max,t_800_80.txtxtx_min,t_800_80.num_of_tx_max,station2.lat,station2.lon from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex " +
" where t_800_80.year between " + startdate.year + " and " + enddate.year + " and t_800_80.month between " + startdate.month + " and " + enddate.month + " " +
"and t_800_80.num_of_month between " + startdate.num_of_month + " and " + enddate.num_of_month + " and ttt_min >= " + tx_min + " and ttt_max <=" + tx_max + "";
}
else
{
sql = "select t_800_80.sindex,t_800_80.year,t_800_80.month,t_800_80.num_of_month,t_800_80.txtxtxaver,t_800_80.txtxtx_max,t_800_80.txtxtx_min,t_800_80.num_of_tx_max,station2.lat,station2.lon from t_800_80 inner join station2 on t_800_80.sindex = station2.sindex " +
" where t_800_80.year between " + startdate.year + " and " + enddate.year + " and t_800_80.month between " + startdate.month + " and " + enddate.month + " " +
"and t_800_80.num_of_month between " + startdate.num_of_month + " and " + enddate.num_of_month + " and ttt_min >= " + tx_max + " and ttt_max <=" + tx_max + " and txtxtx_aver =" + tx_aver + "";
}
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(SoilTableToList(data.Tables[0]));
return(JsonString);
}
public string only_pull_snow(string height_of_snow, string density_of_snow, tenday startdate, tenday enddate)
{
ConnDB conn = new ConnDB();
string sql, snow_h_max, snow_d_max = "";
if (string.IsNullOrWhiteSpace(height_of_snow))
{
snow_h_max = get_height_of_snow();
}
else
{
snow_h_max = height_of_snow;
}
if (string.IsNullOrWhiteSpace(density_of_snow))
{
snow_d_max = get_density_of_snow();
}
else
{
snow_d_max = density_of_snow;
}
sql = "select t_800_83.sindex,t_800_83.year,t_800_83.month,t_800_83.num_of_month,t_800_83.height_of_snow,t_800_83.density_of_snow,t_800_83.field_of_snow,station2.lat,station2.lon from t_800_83 inner join station2 on t_800_83.sindex = station2.sindex " +
" where t_800_83.year between " + startdate.year + " and " + enddate.year + " and t_800_83.month between " + startdate.month + " and " + enddate.month + " " +
"and t_800_83.num_of_month between " + startdate.num_of_month + " and " + enddate.num_of_month + " and height_of_snow <= " + snow_h_max + " and density_of_snow <=" + snow_d_max + "";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(SnowTableToList(data.Tables[0]));
return(JsonString);
}
public string PullData_begin_end(tenday begindate, tenday enddate)
{
ConnDB conn = new ConnDB();
string sql = "select t_800_93.id, t_800_93.sindex, t_800_93.year, t_800_93.month, t_800_93.num_of_month, t_800_93.height_snow_west, t_800_93.density_snow_west, t_800_93.height_snow_north, t_800_93.density_snow_north, t_800_93.height_snow_east, t_800_93.density_snow_east, t_800_93.height_snow_south, t_800_93.density_snow_south, t_800_93.field_of_west, t_800_93.field_of_north, t_800_93.field_of_east, t_800_93.field_of_south, t_800_93.update_time " +
",station2.lat,station2.lon " +
"from t_800_93 inner join station2 on t_800_93.sindex = station2.sindex where t_800_93.year = " + begindate.year + " and t_800_93.month = " + begindate.month + " and t_800_93.num_of_month =" + begindate.num_of_month + " or t_800_93.year = " + enddate.year + " and t_800_93.month =" + enddate.month + " "
+ " and t_800_93.num_of_month =" + enddate.num_of_month + " ";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(ConstructModel(DataTableToList(data.Tables[0])));
return(JsonString);
}
public string only_pull_snow(tenday startdate, tenday enddate)
{
ConnDB conn = new ConnDB();
string sql = " ";
sql = "select t_800_93.*,station2.lat,station2.lon from t_800_93 inner join station2 on t_800_93.sindex = station2.sindex " +
" where t_800_93.year between " + startdate.year + " and " + enddate.year + " and t_800_93.month between " + startdate.month + " and " + enddate.month + " " +
"and t_800_93.num_of_month between " + startdate.num_of_month + " and " + enddate.num_of_month + " ";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(SnowDirectionTableToList(data.Tables[0]));
return(JsonString);
}
public string PullData_begin_end(tenday begindate, tenday enddate)
{
ConnDB conn = new ConnDB();
string sql = "select t_800_83.fid,t_800_83.sindex,t_800_83.year,t_800_83.month,t_800_83.num_of_month,t_800_83.WW_Max,"
+ "t_800_83.TTT_Aver,t_800_83.TTT_Max ,t_800_83.Num_of_Tmax ,t_800_83.TTT_Min,t_800_80.Sum_of_RRR ,t_800_80.TxTxTxAver,"
+ "t_800_83.txtxtx_max,t_800_83.num_of_tmin,t_800_83.Num_of_RRR ,t_800_83.Num_of_Tx_Max,t_800_83.TxTxTx_Min,station2.lat,station2.lon " +
"from t_800_83 inner join station2 on t_800_83.sindex = station2.sindex where t_800_83.year = " + begindate.year + " and t_800_83.month = " + begindate.month + " and t_800_83.num_of_month =" + begindate.num_of_month + " or t_800_83.year = " + enddate.year + " and t_800_83.month =" + enddate.month + " "
+ " and t_800_83.num_of_month =" + enddate.num_of_month + " ";
NpgsqlCommand cmd = conn.RunCmdPG(sql);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
DataSet data = new DataSet();
da.Fill(data);
conn.ClosePG();
string JsonString = string.Empty;
JsonString = JsonConvert.SerializeObject(ConstructModel(DataTableToList(data.Tables[0])));
return(JsonString);
}