public JsonResult ChartData(string YearTaken, string Method, string SortBy)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string conn = ConfigurationManager.ConnectionStrings["kalingaPPDO"].ConnectionString;
using (SqlConnection cn = new SqlConnection(conn))
{
string myQuery = "select * from vw_PalayProductionIrrigatedRainfedUpland where YearTaken = @year AND Method = @Method";
SqlCommand cmd = new SqlCommand()
{
CommandText = myQuery,
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@year", YearTaken);
cmd.Parameters.AddWithValue("@Method", Method);
cmd.Connection = cn;
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData()
{
label = dr["Municipality"].ToString(),
data = dr[SortBy].ToString()
};
t.Add(tsData);
counter++;
}
}
}
return(Json(t, JsonRequestBehavior.AllowGet));
}
public JsonResult ChartData3(string YearTaken, string LandCoverClass)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string conn = ConfigurationManager.ConnectionStrings["kalingaPPDO"].ConnectionString;
using (SqlConnection cn = new SqlConnection(conn))
{
string myQuery = "select * from vw_ForestCoverByVegetationByYear where YearTaken = @year AND LandCoverClassificationDescription = @LandCoverClass";
SqlCommand cmd = new SqlCommand()
{
CommandText = myQuery,
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@year", YearTaken);
cmd.Parameters.AddWithValue("@LandCoverClass", LandCoverClass);
cmd.Connection = cn;
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData()
{
data = dr["AreaHectares"].ToString(),
data2 = dr["Distribution"].ToString()
};
t.Add(tsData);
counter++;
}
}
}
return(Json(t, JsonRequestBehavior.AllowGet));
}
public JsonResult ChartData1(string YearTaken, string Municipality)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string conn = ConfigurationManager.ConnectionStrings["kalingaPPDO"].ConnectionString;
using (SqlConnection cn = new SqlConnection(conn))
{
string myQuery = "select * from vw_LivestockAndPoultryInventoryByMunicipalityByYear where YearTaken = @year AND Municipality = @Municipality";
SqlCommand cmd = new SqlCommand()
{
CommandText = myQuery,
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@year", YearTaken);
cmd.Parameters.AddWithValue("@Municipality", Municipality);
cmd.Connection = cn;
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData()
{
label = dr["LivestockPoultry"].ToString(),
data = dr["NumberofLivestock"].ToString()
};
t.Add(tsData);
counter++;
}
}
}
return(Json(t, JsonRequestBehavior.AllowGet));
}
public static List <trafficSourceData> getTrafficSourceData(List <string> gData)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string[] arrColor = new string[] { "#231F20", "#FFC200", "#F44937", "#16F27E", "#FC9775", "#5A69A6" };
string conn = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (MySqlConnection cn = new MySqlConnection(conn))
{
string myQuery = "SELECT HOUR(calldate) AS hora, count(*) AS total FROM asteriskcdrdb.cdr WHERE calldate >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) GROUP BY hora ORDER BY hora DESC limit 24";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = myQuery;
cmd.CommandType = CommandType.Text;
// cmd.Parameters.AddWithValue("@year", gData[0]);
//cmd.Parameters.AddWithValue("@month", gData[1]);
cmd.Connection = cn;
cn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData();
tsData.value = dr["total"].ToString();
tsData.label = dr["hora"].ToString();
//tsData.color = arrColor[counter];
t.Add(tsData);
counter++;
}
}
}
return(t);
}
public JsonResult ChartData(string YearTaken)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string conn = ConfigurationManager.ConnectionStrings["kalingaPPDO"].ConnectionString;
using (SqlConnection cn = new SqlConnection(conn))
{
string myQuery = "select * from vw_HouseholdHighestEducationAttained where YearTaken = @year";
SqlCommand cmd = new SqlCommand()
{
CommandText = myQuery,
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@year", YearTaken);
cmd.Connection = cn;
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData()
{
data = dr["MalePopulation"].ToString(),
data2 = dr["FemalePopulation"].ToString(),
label = dr["HighestEducAttainedDesc"].ToString()
};
t.Add(tsData);
counter++;
}
}
}
return(Json(t, JsonRequestBehavior.AllowGet));
}
public JsonResult ChartData6(string YearTaken)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string conn = ConfigurationManager.ConnectionStrings["kalingaPPDO"].ConnectionString;
using (SqlConnection cn = new SqlConnection(conn))
{
string myQuery = "select * from vw_HouseholdPopulationByCitizenshipAndSex where YearTaken = @year AND genderDescription = 'Female'";
SqlCommand cmd = new SqlCommand()
{
CommandText = myQuery,
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@year", YearTaken);
cmd.Connection = cn;
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData()
{
data = dr["NumberofHousehold"].ToString(),
label = dr["Nationality"].ToString()
};
t.Add(tsData);
counter++;
}
}
}
return(Json(t, JsonRequestBehavior.AllowGet));
}
public JsonResult ChartData2(int YearTaken)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string conn = ConfigurationManager.ConnectionStrings["kalingaPPDO"].ConnectionString;
using (SqlConnection cn = new SqlConnection(conn))
{
string myQuery = "select * from vw_PopulationDensityByMunicipalityByYear where YearTaken = @year";
SqlCommand cmd = new SqlCommand()
{
CommandText = myQuery,
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@year", YearTaken);
cmd.Connection = cn;
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData()
{
data = dr["PopulationPerArea"].ToString()
};
t.Add(tsData);
counter++;
}
}
}
return(Json(t, JsonRequestBehavior.AllowGet));
}
public JsonResult ChartData2(string YearTaken, string Description)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string conn = ConfigurationManager.ConnectionStrings["kalingaPPDO"].ConnectionString;
using (SqlConnection cn = new SqlConnection(conn))
{
string myQuery = "select * from vw_ByMajorBusinessOrIndustry where YearTaken = @year AND MajorBusinessIndustryID = @Desc AND genderDescription= 'Male' ";
SqlCommand cmd = new SqlCommand()
{
CommandText = myQuery,
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@year", YearTaken);
cmd.Parameters.AddWithValue("@Desc", Description);
cmd.Connection = cn;
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData()
{
data = dr["NumberofPopulation"].ToString(),
label = dr["AgeGroup"].ToString()
};
t.Add(tsData);
counter++;
}
}
}
return(Json(t, JsonRequestBehavior.AllowGet));
}
public JsonResult ChartData1(int YearTaken, string Municipality)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string conn = ConfigurationManager.ConnectionStrings["kalingaPPDO"].ConnectionString;
using (SqlConnection cn = new SqlConnection(conn))
{
string myQuery = "select * from vw_StatusOfIrrigationSystemByMunicipalityByYear where YearTaken = @year and Municipality = @municipality";
SqlCommand cmd = new SqlCommand()
{
CommandText = myQuery,
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@year", YearTaken);
cmd.Parameters.AddWithValue("@municipality", Municipality);
cmd.Connection = cn;
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData()
{
data = dr["AreasIrrigable"].ToString(),
data2 = dr["NatlIrrigationSys"].ToString(),
data3 = dr["NIAAssisted"].ToString(),
data4 = dr["OtherAgency"].ToString(),
data5 = dr["PrivateIrrigation"].ToString(),
data6 = dr["PumpSystem"].ToString(),
data7 = dr["IrrigationDev"].ToString(),
data8 = dr["RemainingAreas"].ToString()
};
t.Add(tsData);
counter++;
}
}
}
return(Json(t, JsonRequestBehavior.AllowGet));
}
public JsonResult ChartData(int YearTaken, string Industry)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string conn = ConfigurationManager.ConnectionStrings["kalingaPPDO"].ConnectionString;
using (SqlConnection cn = new SqlConnection(conn))
{
string myQuery = "select * from vw_EstablishmentByIndustryByYear where YearTaken = @year and IndustryClassification = @Industry";
SqlCommand cmd = new SqlCommand()
{
CommandText = myQuery,
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@year", YearTaken);
cmd.Parameters.AddWithValue("@Industry", Industry);
cmd.Connection = cn;
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData()
{
data = dr["NumberofFirms"].ToString(),
data2 = dr["PercentFirms"].ToString(),
data3 = dr["Investment"].ToString(),
data4 = dr["PercentInvest"].ToString(),
data5 = dr["NumberofEmployee"].ToString(),
data6 = dr["Distribution"].ToString()
};
t.Add(tsData);
counter++;
}
}
}
return(Json(t, JsonRequestBehavior.AllowGet));
}
public JsonResult ChartData2(string YearTaken, string CropType)
{
List <trafficSourceData> t = new List <trafficSourceData>();
string conn = ConfigurationManager.ConnectionStrings["kalingaPPDO"].ConnectionString;
using (SqlConnection cn = new SqlConnection(conn))
{
string myQuery = "select * from vw_OtherHighValueCropsAreaAndProduction where YearTaken = @year AND HighValueCrop = @HighValueCrop";
SqlCommand cmd = new SqlCommand()
{
CommandText = myQuery,
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@year", YearTaken);
cmd.Parameters.AddWithValue("@HighValueCrop", CropType);
cmd.Connection = cn;
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
int counter = 0;
while (dr.Read())
{
trafficSourceData tsData = new trafficSourceData()
{
label = dr["HighValueCrop"].ToString(),
data = dr["AreaHectares"].ToString(),
data2 = dr["ProdMetricTons"].ToString()
};
t.Add(tsData);
counter++;
}
}
}
return(Json(t, JsonRequestBehavior.AllowGet));
}
