protected void Page_Load(object sender, EventArgs e)
{
//In this example, we show how to connect FusionCharts to a database.
//For the sake of ease, we've used an Access database which is present in
//../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each
//other.
//xmlData will be used to store the entire XML document generated
StringBuilder xmlData = new StringBuilder();
//Generate the chart element
xmlData.Append("<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'>");
//Create recordset to get details for the factories
string factoryQuery = "select a.FactoryId,a.FactoryName,sum(b.Quantity) as TotQ from Factory_Master a,Factory_Output b where a.FactoryId=b.FactoryID group by a.FactoryId,a.FactoryName";
DbConn oRs = new DbConn(factoryQuery);
//Iterate through each record
while (oRs.ReadData.Read())
{
//Generate <set name='..' value='..' />
xmlData.AppendFormat("<set label='{0}' value='{1}' />", oRs.ReadData["FactoryName"].ToString(), oRs.ReadData["TotQ"].ToString());
}
oRs.ReadData.Close();
//Close chart element
xmlData.Append("</chart>");
//Create the chart - Pie 3D Chart with data from xmlData
Literal1.Text = FusionCharts.RenderChart("../FusionCharts/Pie3D.swf", "", xmlData.ToString(), "FactorySum", "600", "300", false, true);
}
protected void Page_Load(object sender, EventArgs e)
{
//In this example, we show how to connect FusionCharts to a database.
//For the sake of ease, we've used an MSACCESS databases containing two
//tables.
//strXML will be used to store the entire XML document generated
//Generate the chart element
StringBuilder xmlData = new StringBuilder();
xmlData.Append("<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' >");
string strQuery = "select a.FactoryId,a.FactoryName,sum(b.Quantity) as TotQ from Factory_Master a,Factory_Output b where a.FactoryId=b.FactoryID group by a.FactoryId,a.FactoryName";
// Connect to the DB
DbConn oRs = new DbConn(strQuery);
//Iterate through each factory
while (oRs.ReadData.Read())
{
//Generate <set label='..' value='..' link='..' />
//Note that we're setting link as newchart-xmlurl-url
//This link denotes that linked chart would open
//The source data for each each is defined in the URL which will get data dynamically from the database as per the fctory id
xmlData.AppendFormat("<set label='{0}' value='{1}' link='{2}'/>", oRs.ReadData["FactoryName"].ToString(), oRs.ReadData["TotQ"].ToString(), Server.UrlEncode("newchart-xmlurl-FactoryData.aspx?FactoryId=" + oRs.ReadData["FactoryId"].ToString()));
}
oRs.ReadData.Close();
xmlData.Append("</chart>");
//Create the chart - Pie 3D Chart with data from strXML
Literal1.Text = FusionCharts.RenderChart("../FusionCharts/Pie3D.swf", "", xmlData.ToString(), "FactorySum", "500", "250", false, true, false);
}
protected void Page_Load(object sender, EventArgs e)
{
//This will execute first time the page loads and not on ajax post back calls
if (!IsPostBack)
{
//Generate Radio button from the available Factory names in Database
//Get Factory names from Factory_Master
string strSQL = "select FactoryId,FactoryName from Factory_Master";
//Open datareader using DBConn object
DbConn rs1 = new DbConn(strSQL);
//Fetch all record
while (rs1.ReadData.Read())
{
//Creating Radio Button List
RadioButtonList1.Items.Add(new ListItem(rs1.ReadData["FactoryName"].ToString(), rs1.ReadData["FactoryId"].ToString()));
}
// close datareader
rs1.ReadData.Close();
//Select First radio button as dafult value
RadioButtonList1.Items[0].Selected = true;
//Show chart as per selected radio button.
updateChart();
}
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder xmlData = new StringBuilder();
xmlData.Append("<chart caption='Factory Output report' subCaption='By Quantity' showBorder='1' formatNumberScale='0' rotatelabels='1' showvalues='0'>");
xmlData.AppendFormat("<categories>");
string factoryQuery = "select distinct format(datepro,'dd/mm/yyyy') as dd from factory_output";
DbConn oRs = new DbConn(factoryQuery);
while (oRs.ReadData.Read())
{
xmlData.AppendFormat("<category label='{0}'/>", oRs.ReadData["dd"].ToString());
}
oRs.ReadData.Close();
xmlData.AppendFormat("</categories>");
string factoryquery2 = "select * from factory_master";
DbConn oRs1 = new DbConn(factoryquery2);
while (oRs1.ReadData.Read())
{
xmlData.AppendFormat("<dataset seriesName='{0}'>", oRs1.ReadData["factoryname"].ToString());
string factoryquery3 = "select quantity from factory_output where factoryid=" + oRs1.ReadData["factoryid"].ToString();
DbConn oRs2 = new DbConn(factoryquery3);
while (oRs2.ReadData.Read())
{
xmlData.AppendFormat("<set value='{0}'/>", oRs2.ReadData[0].ToString());
}
oRs2.ReadData.Close();
xmlData.AppendFormat("</dataset>");
}
oRs1.ReadData.Close();
xmlData.AppendFormat("</chart>");
Literal1.Text = Literal1.Text = FusionCharts.RenderChart("../FusionCharts/MSLine.swf", "", xmlData.ToString(), "myFirst", "600", "300", false, true, false);
}
protected void Page_Load(object sender, EventArgs e)
{
//This page is invoked from Default.asp. When the user clicks on a pie
//slice in Default.aspx, the factory Id is passed to this page. We need
//to get that factory id, get information from database and then show
//a detailed chart.
//First, get the factory Id
// string factoryId ="";
//Request the factory Id from Querystring
string factoryId = Request["FactoryId"];
//xmlData will be used to store the entire XML document generated
StringBuilder xmlData = new StringBuilder();
//Generate the chart element string
xmlData.Append("<chart palette='2' caption='Factory " + factoryId + " Output ' subcaption='(In Units)' xAxisName='Date (dd/MM)' showValues='1' labelStep='2' >");
//Now, we get the data for that factory
string query = "select DatePro, Quantity from Factory_Output where FactoryId=" + factoryId;
DbConn oRs = new DbConn(query);
//Iterate through each record
while (oRs.ReadData.Read())
{
//Convert date from database into dd/mm format
//Generate <set name='..' value='..' />
xmlData.Append("<set label='" + Convert.ToDateTime(oRs.ReadData["DatePro"]).ToString("dd/MM") + "' value='" + oRs.ReadData["Quantity"].ToString() + "'/>");
}
oRs.ReadData.Close();
//Close <chart> element
xmlData.Append("</chart>");
//Create the chart - Column 2D Chart with data from xmlData
Literal1.Text = FusionCharts.RenderChart("../FusionCharts/Column2D.swf", "", xmlData.ToString(), "FactoryDetailed", "600", "300", false, true);
}
public string GetFactorySummaryChartHtml()
{
//xmlData will be used to store the entire XML document generated
StringBuilder xmlData=new StringBuilder();
//Generate the chart element
xmlData.Append("<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' >");
//Create recordset to get details for the factories
string factoryQuery = "select a.FactoryId, a.FactoryName, sum(b.Quantity) as TotQ from .Factory_Master a, Factory_Output b where a.FactoryId=b.FactoryID group by a.FactoryId, a.FactoryName ";
DbConn oRs=new DbConn(factoryQuery);
//Iterate through each record
while(oRs.ReadData.Read()){
//Generate <set name='..' value='..' link='...'/>
//The link causes drill-down by calling (here) a JavaScript function
//The function is passed the Factory id
//The function updates the second chart
xmlData.AppendFormat("<set label='{0}' value='{1}' link='JavaScript:updateChart({2})' />", oRs.ReadData["FactoryName"].ToString(), oRs.ReadData["TotQ"].ToString(), oRs.ReadData["FactoryId"].ToString());
}
//Close chart element
xmlData.Append("</chart>");
//Create the chart - Pie 3D Chart with data from xmlData
return FusionCharts.RenderChart("../FusionCharts/Pie3D.swf", "", xmlData.ToString(), "FactorySum", "500", "250", false, true);
}
protected void Page_Load(object sender, EventArgs e)
{
int Id;
string strQuery2 = "Select FactoryId from Factory_Master";
DbConn oRs3 = new DbConn(strQuery2);
StringBuilder strXML = new StringBuilder();
//Generate the chart element string
strXML.Append("<chart palette='2' caption='Factory Output ' subcaption='(In Units)' xAxisName='Date' showValues='1' labelStep='2' >");
// Connet to the DB
while (oRs3.ReadData.Read())
{
//int Id = Convert.ToInt32(oRs3.ReadData.Read());
//Now, we get the data for that factory
Id = Convert.ToInt32(oRs3.ReadData.Read());
string strQuery = "select Format(DatePro,'dd/MM') as dDate, Quantity from Factory_Output where FactoryId=" + Id.ToString();
DbConn oRs2 = new DbConn(strQuery);
//Iterate through each factory
while (oRs2.ReadData.Read())
{
//Here, we convert date into a more readable form for set label.
strXML.AppendFormat("<set label='{0}' value='{1}' />", oRs2.ReadData["dDate"].ToString(), oRs2.ReadData["Quantity"].ToString());
}
}
//Close <chart> element
strXML.Append("</chart>");
//Create the chart - Column 2D Chart with data from strXML
Literal1.Text = FusionCharts.RenderChart("../FusionCharts/Column2D.swf", "", strXML.ToString(), "FactoryDetailed", "600", "300", false, true, false);
}
protected void Page_Load(object sender, EventArgs e)
{
StringBuilder strXML = new StringBuilder();
//$strXML will be used to store the entire XML document generated
//Generate the chart element
strXML.Append("<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'>");
// Fetch all factory records
string strQuery = "select * from Factory_Master ";
DbConn oRs1 = new DbConn(strQuery);
while (oRs1.ReadData.Read())
{
string strQuery1 = "select sum(Quantity) as TotQ from Factory_Output where FactoryId=" + oRs1.ReadData["FactoryId"];
DbConn oRs = new DbConn(strQuery1);
//Iterate through each factory
while (oRs.ReadData.Read())
{
//Now create a second query to get details for this factory
//Note that we're setting link as Detailed.php?FactoryId=<<FactoryId>>
strXML.AppendFormat("<set label='{0}' value='{1}' link='{2}' />", oRs1.ReadData["FactoryName"].ToString(), oRs.ReadData["TotQ"].ToString(), ("Detailed.aspx?Id=" + oRs1.ReadData["FactoryId"]));
}
//free the resultset
oRs.ReadData.Close();
}
oRs1.ReadData.Close();
//Finally, close <chart> element
strXML.Append("</chart>");
//Create the chart - Pie 3D Chart with data from strXML
Literal1.Text = FusionCharts.RenderChart("../FusionCharts/Pie3D.swf", "", strXML.ToString(), "FactorySum", "600", "300", false, true, false);
}
/// <summary>
/// update FusionCharts and gridview with as per selected factory name
/// </summary>
private void updateChart()
{
//Get factory details depending on FactoryID from selected Radio Button
string strSQL = "select DatePro, Quantity from Factory_Output where FactoryId=" + RadioButtonList1.SelectedValue.ToString() + " order by DatePro";
//Create data reader to bind data with GridView
DbConn rs = new DbConn(strSQL);
//Fillup gridview with data from datareader
GridView1.DataSource = rs.ReadData;
// binding the data
GridView1.DataBind();
//Create database connection to get data for chart
DbConn oRs = new DbConn(strSQL);
//Create FusionCharts XML
StringBuilder strXML = new StringBuilder();
//Create chart element
strXML.AppendFormat("<chart caption='Factory {0}' showborder='0' bgcolor='FFFFFF' bgalpha='100' subcaption='Daily Production' xAxisName='Day' yAxisName='Units' rotateLabels='1' placeValuesInside='1' rotateValues='1' >", RadioButtonList1.SelectedValue.ToString());
//Iterate through database
while (oRs.ReadData.Read())
{
//Create set element
//Also set date into d/M format
strXML.AppendFormat("<set label='{0}' value='{1}' />", Convert.ToDateTime(oRs.ReadData["DatePro"]).ToString("d/M"), oRs.ReadData["Quantity"].ToString());
}
//Close chart element
strXML.Append("</chart>");
//outPut will store the HTML of the chart rendered as string
string outPut = "";
if (IsPostBack)
{
//when an ajax call is made we use RenderChartHTML method
outPut = FusionCharts.RenderChartHTML("../FusionCharts/Column2D.swf", "", strXML.ToString(), "chart1", "445", "350", false, false);
}
else
{
//When the page is loaded for the first time, we call RenderChart() method to avoid IE's 'Click here to Acrtivate...' message
outPut = FusionCharts.RenderChart("../FusionCharts/Column2D.swf", "", strXML.ToString(), "chart1", "445", "350", false, false);
}
//Clear panel which will contain the chart
Panel1.Controls.Clear();
//Add Litaral control to Panel which adds the chart from outPut string
Panel1.Controls.Add(new LiteralControl(outPut));
// close Data Reader
oRs.ReadData.Close();
}
/// <summary>
/// This function creates 3 charts reflecting the Employment Distribution
/// </summary>
public void GetCharts()
{
// strXML will be used to store the entire XML document generated
string strXML, strQuery;
// define i for counter
int i;
//We need to build three charts here. So, we use a loop to iterate through data
//and build XML on the fly.
i = 1;
// Create SQL Query String
strQuery = "select group_id from fcmap_group_master";
// Create dataReader object using DbHelper.GetReader function
DbConn aReader = new DbConn(strQuery);
// Check reader has record or not
if (aReader.ReadData.HasRows == true)
{
// Read all Group data
while (aReader.ReadData.Read())
{
// Get the dataURL for the chart
StringBuilder strURL = new StringBuilder();
strURL.AppendFormat("DataGen.aspx?op=getChartEmpStat&groupID={0}&entity_id={1}&Internal_Id={2}", aReader.ReadData["group_id"].ToString(), Request["entity_id"], Request["Internal_Id"]);
Response.Write("<tr><td>");
// Create the chart - 2 Pie 3D Chart and 1 Column 3Dwith data from strXML
// If group id is 1,2 then show pie3d chart
if (Convert.ToInt16(aReader.ReadData["group_id"]) != 3)
{
// Create the Chart with data contained in strURL
// and Return HTML output that embeds the chart
// We use FusionCharts class of InfoSoftGlobal namespace (FusionCharts.dll in BIN folder)
// RenderChart renders the necessary HTML needed to render the chart
Response.Write(FusionCharts.RenderChart("../Charts/Pie3D.swf", Server.UrlEncode(strURL.ToString()), "", "Chart_unemp" + i, "500", "350", false, false));
}
else
{
//if group id is 3 then show column3d chart
// Create the Chart with data contained in strURL
// and Return HTML output that embeds the chart
// We use FusionCharts class of InfoSoftGlobal namespace (FusionCharts.dll in BIN folder)
// RenderChart renders the necessary HTML needed to render the chart
Response.Write(FusionCharts.RenderChart("../Charts/Column3D.swf", Server.UrlEncode(strURL.ToString()), "", "Chart_emp" + i, "500", "350", false, false));
}
Response.Write("</td></tr>");
// Increase counter i by 1
i += 1;
}
}
// close reader
aReader.ReadData.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
//This page generates the XML data for the Pie Chart contained in
//Default.aspx.
//For the sake of ease, we've used an Access database which is present in
//../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to
//each other.
//xmlData will be used to store the entire XML document generated
StringBuilder xmlData = new StringBuilder();
//Default.aspx has passed us a property animate. We request that.
string animateChart;
animateChart = Request["animate"];
//Set default value of 1
if (animateChart != null && animateChart.Length == 0)
{
animateChart = "1";
}
//Generate the chart element
xmlData.AppendFormat("<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation='{0}'>", animateChart);
//create recordset to get details for the factories
string query = "select a.FactoryId, a.FactoryName, sum(b.Quantity) as TotQ from .Factory_Master a, Factory_Output b where a.FactoryId=b.FactoryID group by a.FactoryId, a.FactoryName";
DbConn oRs = new DbConn(query);
//Iterate through each factory
while (oRs.ReadData.Read())
{
//Generate <set name='..' value='..' />
xmlData.AppendFormat("<set label='{0}' value='{1}' />", oRs.ReadData["FactoryName"].ToString(), oRs.ReadData["TotQ"].ToString());
}
oRs.ReadData.Close();
//Close chart element
xmlData.Append("</chart>");
//Set Proper output content-type
Response.ContentType = "text/xml";
//Just write out the XML data
//NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER
Response.Write(xmlData.ToString());
}
protected void Page_Load(object sender, EventArgs e)
{
//This page generates the XML data for the Pie Chart contained in
//Default.asp.
//For the sake of ease, we've used an Access database which is present in
//../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each
//other.
//Database Objects - Initialization
DbConn oRs; string strQuery;
//strXML will be used to store the entire XML document generated
string strXML;
//Generate the graph element
strXML = "<graph caption='Factory Output report' subCaption='By Quantity' decimalPrecision='0' showNames='1' numberSuffix=' Units' pieSliceDepth='30' formatNumberScale='0'>";
//Iterate through each factory
strQuery = "select * from Factory_Master";
oRs = new DbConn(strQuery);
while (oRs.ReadData.Read())
{
//Now create second recordset to get details for this factory
strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" + oRs.ReadData["FactoryId"].ToString();
DbConn oRs2 = new DbConn(strQuery);
oRs2.ReadData.Read();
//Generate <set name='..' value='..'/>
strXML += "<set name='" + oRs.ReadData["FactoryName"].ToString() + "' value='" + oRs2.ReadData["TotOutput"].ToString() + "' />";
//Close recordset
oRs2.ReadData.Close();
}
oRs.ReadData.Close();
//Finally, close <graph> element
strXML += "</graph>";
//Set Proper output content-type
Response.ContentType = "text/xml";
//Just write out the XML data
//NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER
Response.Write(strXML);
}
protected void Page_Load(object sender, EventArgs e)
{
dbConn = new DbConn();
SQL = "SELECT * FROM tblRoleAccess";
ds = new DataSet();
ds = dbConn.createDataSet(SQL);
dt = new DataTable();
dt = ds.Tables[0];
//dt.Rows.Count.ToString();
Response.Write(dt.Rows.Count.ToString() + "<br/>");
for (int i = 0; i < dt.Rows.Count; i++)
{
Response.Write("INSERT INTO tblRoleAccess VALUES('" + dt.Rows[i][0].ToString() + "', '" + dt.Rows[i][1].ToString() + "', '" + dt.Rows[i][2].ToString() + "')<br/>");
}
}
protected void Page_Load(object sender, EventArgs e)
{
//This page builds XML from database. The database contains UTF-8 encoded multilingual text.
//We have pre encoded this file as UTF-8 encoded with BOM
//Hence, we would just retrieve the text and relay it to the chart
//For the sake of ease, we've used an MSAccess(MDB) databases - sales and all data in a table - 'monthly_utf8'
//strXML will be used to store the entire XML document generated
string strXML;
string strQuery;
//Generate the chart element
strXML = "<chart caption='Monthly Sales Summary' subcaption='For the year 2008' ";
strXML = strXML + " xAxisName='Month' yAxisName='Sales' numberPrefix='$' showNames='1'";
strXML = strXML + " showValues='0' showColumnShadow='1' animation='1'";
strXML = strXML + " baseFontColor='666666' lineColor='FF5904' lineAlpha='85'";
strXML = strXML + " valuePadding='10' labelDisplay='Rotate' useRoundEdges='1' >";
// Fetch all factory records
strQuery = "select * from monthly_utf8";
DbConn oRs = new DbConn(strQuery);
//Iterate through each month
while (oRs.ReadData.Read())
{
//Generate <set label='..' value='..'/>
strXML = strXML + "<set label='" + oRs.ReadData["month_name"] + "' value='" + oRs.ReadData["amount"] + "' />";
}
oRs.ReadData.Close();
// add style
strXML = strXML + "<styles><definition><style name='myCaptionFont' type='font' size='12'/></definition>";
strXML = strXML + "<application><apply toObject='datalabels' styles='myCaptionFont' /></application></styles>";
// Finally, close <chart> element
strXML = strXML + "</chart>";
// Set Proper output content-type
Response.ContentType = "text/xml; characterset=utf-8";
Response.BinaryWrite(new byte[] { 0xEF, 0xBB, 0xBF });
// Just write out the XML data
Response.Write (strXML);
}
public string GetFactorySummaryChartHtml()
{
//In this example, we show how to connect FusionCharts to a database.
//For the sake of ease, we've used an Access database which is present in
//../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each
//other.
//xmlData will be used to store the entire XML document generated
StringBuilder xmlData=new StringBuilder();
//We also keep a flag to specify whether we've to animate the chart or not.
//If the user is viewing the detailed chart and comes back to this page, he shouldn't
//see the animation again.
string animateChart;
animateChart = Request["animate"];
//Set default value of 1
if (animateChart != null && animateChart.Length == 0)
{
animateChart = "1";
}
//Generate the chart element
xmlData.AppendFormat("<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation='{0}' >",animateChart);
//Create recordset to get details for the factories
string factoryQuery = "select a.FactoryId,a.FactoryName,sum(b.Quantity) as TotQ from Factory_Master a,Factory_Output b where a.FactoryId=b.FactoryID group by a.FactoryId,a.FactoryName";
DbConn oRs = new DbConn(factoryQuery);
//Iterate through each factory
while (oRs.ReadData.Read()){
//Generate <set name='...' value='...' link='...'/>
//The link causes drill-down by loading a another page
//The page is passed the factoryId
//Accordingly the page creates a detailed chart against that FactoryId
xmlData.AppendFormat("<set label='{0}' value='{1}' link='{2}'/>", oRs.ReadData["FactoryName"].ToString(), oRs.ReadData["TotQ"].ToString(), Server.UrlEncode("Detailed.aspx?FactoryId=" + oRs.ReadData["FactoryId"].ToString()));
}
//Finally, close <chart> element
xmlData.Append("</chart>");
//Create the chart - Pie 3D Chart with data from xmlData
return FusionCharts.RenderChart("../FusionCharts/Pie3D.swf", "", xmlData.ToString(), "FactorySum", "600", "300", false, false);
}
public int GetAvailableNode(DbConn PubConn)
{
return(SqlHelper.Visit(ps =>
{
string sql = "select id from tb_node where (nodelastupdatetime='' or TIMESTAMPDIFF(minute ,nodecreatetime,NOW())>20) LIMIT 0,1";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, null);
if (ds.Tables[0].Rows.Count > 0)
{
int id = Convert.ToInt32(ds.Tables[0].Rows[0]["id"]);
return id;
}
else
{
string sqlinsert = "insert into tb_node (nodename,nodecreatetime,nodeip)values('ÐÂÔö½Úµã',NOW(),'');select @@IDENTITY";
int id = Convert.ToInt32(PubConn.ExecuteScalar(sqlinsert, null));
return id;
}
}));
}
public virtual bool Edit(DbConn PubConn, tb_datanode_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@datanodepartition", model.datanodepartition),
//
new ProcedureParameter("@serverip", model.serverip),
//
new ProcedureParameter("@username", model.username),
//
new ProcedureParameter("@password", model.password)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_datanode set datanodepartition=@datanodepartition,serverip=@serverip,username=@username,password=@password where id=@id", Par);
return(rev == 1);
}
public tb_consumer_model RegisterConsumer(DbConn PubConn, int clientid, string clientname, List <int> partitionindexs)
{
long tempid = CommonHelper.GenerateIntID();
tb_consumer_dal dal = new tb_consumer_dal();
dal.DeleteNotOnLineByClientID(PubConn, clientid, SystemParamConfig.Consumer_ConsumerHeartbeat_MAX_TIME_OUT);
List <int> usedpartitionindexs = dal.GetRegisterPartitionIndexs(PubConn, clientid);
var conflictpartitionindexs = (from o in usedpartitionindexs from n in partitionindexs where o == n select o).ToList();
if (conflictpartitionindexs != null && conflictpartitionindexs.Count > 0)
{
throw new BusinessMQException(string.Format("当前分区序号已经被注册使用中,冲突分区序号为:{0},可能是上次消费者异常终止导致消费者依然在注册中,请尝试在{1}秒系统超时后重试。",
string.Join(",", conflictpartitionindexs.ToArray()), SystemParamConfig.Consumer_ConsumerHeartbeat_MAX_TIME_OUT));
}
dal.Add2(PubConn, new tb_consumer_model()
{
clientname = clientname, consumerclientid = clientid, partitionindexs = string.Join(",", partitionindexs.ToArray()), tempid = tempid
});
return(dal.Get(PubConn, tempid, clientid));
}
public virtual bool Edit(DbConn PubConn, tb_cluster_monitorinfo_snapshot_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@serverid", model.serverid),
//
new ProcedureParameter("@monitorinfojson", model.monitorinfojson),
//
new ProcedureParameter("@lastupdatetime", model.lastupdatetime),
//
new ProcedureParameter("@createtime", model.createtime)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_cluster_monitorinfo_snapshot set serverid=@serverid,monitorinfojson=@monitorinfojson,lastupdatetime=@lastupdatetime,createtime=@createtime where id=@id", Par);
return(rev == 1);
}
public ActionResult Update(tb_producter_model model)
{
using (DbConn conn = DbConfig.CreateConn(DataConfig.MqManage))
{
conn.Open();
tb_producter_model result = dal.Get(conn, model.id);
if (result != null)
{
result.mqpathid = model.mqpathid;
result.productername = model.productername;
result.ip = model.ip;
if (dal.Edit(conn, result))
{
return(RedirectToAction("index"));
}
}
ModelState.AddModelError("Error", "更新错误");
return(View(result));
}
}
public virtual bool Add(DbConn PubConn, tb_error_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@msg", model.msg),
//
new ProcedureParameter("@errortype", model.errortype),
//
new ProcedureParameter("@errorcreatetime", model.errorcreatetime),
//
new ProcedureParameter("@taskid", model.taskid),
//
new ProcedureParameter("@nodeid", model.nodeid)
};
int rev = PubConn.ExecuteSql(@"insert into tb_error(msg,errortype,errorcreatetime,taskid,nodeid)
values(@msg,@errortype,@errorcreatetime,@taskid,@nodeid)" , Par);
return(rev == 1);
}
public virtual bool Add(DbConn PubConn, tb_node_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@nodename", model.nodename),
//
new ProcedureParameter("@nodecreatetime", model.nodecreatetime),
//
new ProcedureParameter("@nodeip", model.nodeip),
//
new ProcedureParameter("@nodelastupdatetime", model.nodelastupdatetime),
//
new ProcedureParameter("@ifcheckstate", model.ifcheckstate)
};
int rev = PubConn.ExecuteSql(@"insert into tb_node(nodename,nodecreatetime,nodeip,nodelastupdatetime,ifcheckstate)
values(@nodename,@nodecreatetime,@nodeip,@nodelastupdatetime,@ifcheckstate)" , Par);
return(rev == 1);
}
public virtual bool Edit(DbConn PubConn, tb_businessversion_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@taskjson", model.taskjson),
//
new ProcedureParameter("@businessversion", model.businessversion),
new ProcedureParameter("@description", model.description),
//
new ProcedureParameter("@createtime", model.createtime)
};
Par.Add(new ProcedureParameter("@id", model.id));
int rev = PubConn.ExecuteSql("update tb_businessversion set taskjson=@taskjson,businessversion=@businessversion,description=@description,createtime=@createtime where id=@id", Par);
return(rev == 1);
}
public Models.DbModels.api Get(DbConn PubConn, string area, string controller, string action, int apptype)
{
XXF.Db.SimpleProcedureParameter para = new SimpleProcedureParameter();
para.Add("area", area ?? "");
para.Add("controller", controller ?? "");
para.Add("action", action ?? "");
para.Add("@apptype", apptype);
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from api s where s.area=@area and s.controller=@controller and s.action=@action and s.apptype=@apptype");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), para.ToParameters());
if (ds != null && ds.Tables.Count > 0)
{
return(CreateModel(ds.Tables[0].Rows[0]));
}
return(null);
}
/// <summary>
/// 获取所有队列
/// </summary>
/// <returns></returns>
public JsonResult GetMqPath()
{
using (DbConn conn = DbConfig.CreateConn(DataConfig.MqManage))
{
conn.Open();
IList <tb_mqpath_model> list = mqPathDal.GetAllMaPath(conn);
if (list != null && list.Count > 0)
{
var m = (from a in list
select new
{
key = a.id,
value = a.mqpath
}).Distinct();
return(Json(m, JsonRequestBehavior.AllowGet));
}
return(Json("", JsonRequestBehavior.AllowGet));
}
}
public JsonResult CheckTaskState(int id, int state)
{
return(this.Visit(EnumUserRole.None, () =>
{
tb_task_dal dal = new tb_task_dal();
using (DbConn PubConn = DbConn.CreateConn(Config.TaskConnectString))
{
PubConn.Open();
int taskstate = dal.CheckTaskState(PubConn, id);
if (taskstate == state)
{
return Json(new { code = 1, msg = "Success" }, JsonRequestBehavior.AllowGet);
}
else
{
return Json(new { code = -1, msg = "" }, JsonRequestBehavior.AllowGet);
}
}
}));
}
public virtual bool Add(DbConn PubConn, tb_version_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@taskid", model.taskid),
//
new ProcedureParameter("@version", model.version),
//
new ProcedureParameter("@versioncreatetime", model.versioncreatetime),
//压缩文件二进制文件
new ProcedureParameter("@zipfile", model.zipfile),
//
new ProcedureParameter("@zipfilename", model.zipfilename)
};
int rev = PubConn.ExecuteSql(@"insert into tb_version(taskid,version,versioncreatetime,zipfile,zipfilename)
values(@taskid,@version,@versioncreatetime,@zipfile,@zipfilename)" , Par);
return(rev == 1);
}
public int AddOrUpdate(DbConn PubConn, tb_node_model model)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("@nodename", model.nodename);
ps.Add("@nodecreatetime", model.nodecreatetime);
ps.Add("@nodeip", model.nodeip);
ps.Add("@nodelastupdatetime", model.nodelastupdatetime);
ps.Add("@ifcheckstate", model.ifcheckstate);
ps.Add("@id", model.id);
string updatecmd = "update tb_node set nodeip=@nodeip,nodelastupdatetime=@nodelastupdatetime where id=@id";
string insertcmd = @"insert into tb_node(nodename,nodecreatetime,nodeip,ifcheckstate)
values(@nodename,@nodecreatetime,@nodeip,@ifcheckstate)" ;
if (PubConn.ExecuteSql(updatecmd, ps.ToParameters()) <= 0)
{
PubConn.ExecuteSql(insertcmd, ps.ToParameters());
}
return 1;
}));
}
/// <summary>
/// 自定义条件删除
/// </summary>
/// <param name="where">自定义条件，where子句（不包含关键字Where）</param>
/// <param name="param">参数（对象属性自动转为sql中的参数，eg：new {Id=10},则执行sql会转为参数对象 @Id,值为10）</param>
/// <param name="tran">事务</param>
/// <returns></returns>
public virtual int Remove(string where, object param = null, IDbTransaction tran = null)
{
const string format = @"DELETE FROM {0} {1};";
var whereClause = string.Empty;
if (!string.IsNullOrWhiteSpace(where))
{
whereClause = where.Trim();
if (!whereClause.StartsWith("where", StringComparison.OrdinalIgnoreCase))
{
whereClause = "WHERE " + whereClause;
}
}
var sql = string.Format(format, DataBaseTableName, whereClause);
return(DbConn.Execute(sql, param: param, transaction: tran));
}
public List <tb_messagequeue_model> GetListMoreThanID(DbConn PubConn, int topcount, long id)
{
return(SqlHelper.Visit((ps) =>
{
List <tb_messagequeue_model> rs = new List <tb_messagequeue_model>();
StringBuilder stringSql = new StringBuilder();
stringSql.Append(string.Format(@"select top {1} * from {0} s WITH(NOLOCK) where id>@id", TableName, topcount));
ps.Add("id", id);
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
rs.Add(CreateModel(dr));
}
}
return rs;
}));
}
/// <summary>
/// 폼이 로드될 때
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void HistoryForm_Load(object sender, EventArgs e)
{
//0. 오라클 접속 변수 얻어오기
odec = DbConn.GetDbConn();
//1. 일반, 특수 기본값 채워주기
//일반으로 기본값 잡음.
//2. 날짜 세팅
this.dtStartTime.Value = DateTime.Now.Date;
this.dtEndTime.Value = DateTime.Now.Date.AddMinutes(1439);
//3. 상태 드랍박스 채워주기
this.cbStatus.Items.Add("선택없음");
this.cbStatus.Items.Add("대기");
this.cbStatus.Items.Add("전송중");
this.cbStatus.Items.Add("종료");
this.cbStatus.SelectedIndex = 0; // "선택없음" 을 선택
}
public virtual bool Add(DbConn PubConn, tb_log_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//
new ProcedureParameter("@mqpathid", model.mqpathid),
//
new ProcedureParameter("@mqpath", model.mqpath),
//
new ProcedureParameter("@methodname", model.methodname),
//
new ProcedureParameter("@info", model.info),
//
new ProcedureParameter("@createtime", model.createtime)
};
int rev = PubConn.ExecuteSql(@"insert into tb_log(mqpathid,mqpath,methodname,info,createtime)
values(@mqpathid,@mqpath,@methodname,@info,@createtime)" , Par);
return(rev == 1);
}
public static void RefreashRedisServerIP()
{
try
{
using (DbConn PubConn = DbConn.CreateConn(GlobalConfig.TaskDataBaseConnectString))
{
PubConn.Open();
var dal = new tb_config_dal();
var config = dal.Get(PubConn, RedisConfig.RedisServerKey);
if (config != null)
{
RedisConfig.RedisServer = config.configvalue;
}
}
}
catch (Exception exp)
{
LogHelper.AddNodeError(string.Format("从配置中获取{0}出错,", RedisConfig.RedisServerKey), exp);
}
}
public long GetMinID(DbConn PubConn, DateTime time)
{
return(SqlHelper.Visit((ps) =>
{
StringBuilder stringSql = new StringBuilder();
stringSql.Append(string.Format(@"select min(id) from {0} s WITH(NOLOCK) where mqcreatetime>@time", TableName));
DataSet ds = new DataSet();
ps.Add("@time", time);
PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters());
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0][0] is DBNull)
{
return 0;
}
return Convert.ToInt64(ds.Tables[0].Rows[0][0]);
}
return 0;
}));
}
public JsonResult Delete(int id)
{
return(this.Visit(Core.EnumUserRole.Admin, () =>
{
try
{
tb_user_dal dal = new tb_user_dal();
using (DbConn PubConn = DbConfig.CreateConn(Config.TaskConnectString))
{
PubConn.Open();
bool state = dal.DeleteOneNode(PubConn, id);
return Json(new { code = 1, state = state });
}
}
catch (Exception ex)
{
return Json(new { code = -1, msg = ex.Message });
}
}));
}
public void AddToCartWithQty(Product item, int qty)
{
using (var ctx = new DbConn())
{
// Get the matching cart and album instances
// var cartItem = Find(item.id);
var cartItem = ctx.Carts.SingleOrDefault(
c => c.CartId == ShoppingCartId &&
c.ProductId == item.id);
if (cartItem == null)
{
if (qty != 0)
{
cartItem = new Cart
{
ProductId = item.id,
CartId = ShoppingCartId,
Count = qty,
DateCreated = DateTime.Now
};
}
else
{
throw new HttpException("ex");
}
// Create a new cart item if no cart item exists
ctx.Carts.Add(cartItem);
}
else
{
// If the item does exist in the cart,
// then add one to the quantity
cartItem.Count++;
}
// Save changes
ctx.SaveChanges();
}
}
public void SqlToDataSet(DataSet ds, DbConn dbConn, string sql, List <ProcedureParameter> procedurePar)
{
IDbConnection conn = dbConn.GetConnection();
SqlDataAdapter da = null;
SqlCommand sqlCommand = null;
try
{
sqlCommand = new SqlCommand();
sqlCommand.CommandTimeout = 0;
sqlCommand.Connection = (SqlConnection)conn;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = sql;
if (procedurePar != null)
{
for (int index = 0; index < procedurePar.Count; ++index)
{
SqlParameter sqlParameter = ParameterTransform(procedurePar[index]);
sqlCommand.Parameters.Add(sqlParameter);
}
}
da = new SqlDataAdapter()
{
SelectCommand = sqlCommand
};
da.Fill(ds);
}
finally
{
/*if (conn != null)
* {
* if (conn.State != ConnectionState.Closed)
* conn.Close();
* conn.Dispose();
* }*/
sqlCommand?.Dispose();
da?.Dispose();
}
}
/// <summary>
/// 同步页面
/// </summary>
/// <returns></returns>
public ActionResult Sync()
{
return(this.Visit(Core.EnumUserRole.Admin, () =>
{
PagedList <tb_tasksyncmapinfo_model> pageList;
var list = new List <tb_tasksyncmap_model>();
using (DbConn PubConn = DbConfig.CreateConn(Config.TaskConnectString))
{
PubConn.Open();
tb_tasksyncmap_dal tasksyncdal = new tb_tasksyncmap_dal();
list = tasksyncdal.GetList(PubConn);
var modelList = new List <tb_tasksyncmapinfo_model>();
if (list != null && list.Count > 0)
{
foreach (var item in list)
{
var fromtaskinfo = TaskHelper.GetTask(item.fromtaskid, PubConn);
var fromtasknodeinfo = TaskHelper.GetNode(fromtaskinfo.nodeid, PubConn);
var totaskinfo = TaskHelper.GetTask(item.totaskid, PubConn);
var totasknodeinfo = TaskHelper.GetNode(totaskinfo.nodeid, PubConn);
var fromtaskversioninfo = TaskHelper.GetSimpleVersion(fromtaskinfo.id, fromtaskinfo.taskversion, PubConn);
var totaskversioninfo = TaskHelper.GetSimpleVersion(totaskinfo.id, totaskinfo.taskversion, PubConn);
modelList.Add(new tb_tasksyncmapinfo_model
{
id = item.id,
fromtask = new tasksyncinfo {
taskid = fromtaskinfo.id, taskname = fromtaskinfo.taskname, nodeid = fromtasknodeinfo.id, nodename = fromtasknodeinfo.nodename, version = fromtaskinfo.taskversion.ToString(), assemblyversion = fromtaskversioninfo.assemblyversion, createtime = fromtaskversioninfo.versioncreatetime.ToString("yyyy-MM-dd HH:mm:ss")
},
totask = new tasksyncinfo {
taskid = totaskinfo.id, taskname = totaskinfo.taskname, nodeid = totasknodeinfo.id, nodename = totasknodeinfo.nodename, version = totaskinfo.taskversion.ToString(), assemblyversion = totaskversioninfo.assemblyversion, createtime = totaskversioninfo.versioncreatetime.ToString("yyyy-MM-dd HH:mm:ss")
},
isdiff = true //fromtaskversioninfo.assemblyversion != totaskversioninfo.assemblyversion
});
}
}
pageList = new PagedList <tb_tasksyncmapinfo_model>(modelList.Where(item => item.isdiff), 1, 1000);
return View(pageList);
}
}));
}
public tb_consumer_partition_model RegisterConsumerPartition(DbConn PubConn, int clientid, int partitionindex, string mqpath, long tempid)
{
var mqpathmodel = GetMQPath(PubConn, mqpath);
tb_mqpath_partition_dal mqpathpartitiondal = new tb_mqpath_partition_dal();
var mqpathpartitionmodel = mqpathpartitiondal.GetOfConsumer(PubConn, partitionindex, mqpathmodel.id);
if (mqpathpartitionmodel == null)
{
return(null);
}
tb_consumer_partition_dal dal = new tb_consumer_partition_dal();
if (dal.Edit2(PubConn, new tb_consumer_partition_model()
{
consumerclientid = clientid, lastconsumertempid = tempid, partitionid = mqpathpartitionmodel.partitionid, partitionindex = partitionindex
}) <= 0)
{
var partitionidinfo = PartitionRuleHelper.GetPartitionIDInfo(mqpathpartitionmodel.partitionid); var datanodename = PartitionRuleHelper.GetDataNodeName(partitionidinfo.DataNodePartition);
long maxid = -1; DateTime serverdate = PubConn.GetServerDate(); string tablename = PartitionRuleHelper.GetTableName(partitionidinfo.TablePartition, serverdate);
SqlHelper.ExcuteSql(this.GetDataNodeConnectString(PubConn, partitionidinfo.DataNodePartition), (c) => {
var exist = c.TableIsExist(tablename); if (!exist)
{
throw new BusinessMQException(string.Format("当前数据节点{0},表{1}不存在", partitionidinfo.DataNodePartition, tablename));
}
tb_messagequeue_dal mqdal = new tb_messagequeue_dal(); mqdal.TableName = tablename;
maxid = mqdal.GetMaxId(c);
if (maxid <= 0)
{
maxid = PartitionRuleHelper.GetMQID(new MQIDInfo()
{
AutoID = 0, DataNodePartition = partitionidinfo.DataNodePartition, Day = serverdate, TablePartition = partitionidinfo.TablePartition
});
}
});
dal.Add2(PubConn, new tb_consumer_partition_model()
{
consumerclientid = clientid, lastconsumertempid = tempid, lastmqid = maxid, partitionid = mqpathpartitionmodel.partitionid, partitionindex = partitionindex
});
}
return(dal.Get(PubConn, clientid, mqpathpartitionmodel.partitionid));
}
public List <tb_node_model> GetList(DbConn PubConn, string keyword, string cstime, string cetime, int pagesize, int pageindex, out int count)
{
int _count = 0;
List <tb_node_model> Model = new List <tb_node_model>();
DataSet dsList = SqlHelper.Visit <DataSet>(ps =>
{
string sqlwhere = "";
string sql = "select ROW_NUMBER() over(order by id desc) as rownum,id,nodename,nodecreatetime,nodeip,nodelastupdatetime,ifcheckstate from tb_node where 1=1 ";
//string sql = "select * from tb_node where 1=1 ";
if (!string.IsNullOrWhiteSpace(keyword))
{
ps.Add("keyword", keyword);
sqlwhere = " and (nodename like '%'+@keyword+'%' or nodeip like '%'+@keyword+'%') ";
}
DateTime d = DateTime.Now;
if (DateTime.TryParse(cstime, out d))
{
ps.Add("CStime", Convert.ToDateTime(cstime));
sqlwhere += " and nodecreatetime>=@CStime";
}
if (DateTime.TryParse(cetime, out d))
{
ps.Add("CEtime", Convert.ToDateTime(cetime));
sqlwhere += " and nodecreatetime<=@CEtime";
}
_count = Convert.ToInt32(PubConn.ExecuteScalar("select count(1) from tb_node where 1=1 " + sqlwhere, ps.ToParameters()));
DataSet ds = new DataSet();
string sqlSel = "select * from (" + sql + sqlwhere + ") A where rownum between " + ((pageindex - 1) * pagesize + 1) + " and " + pagesize * pageindex;
//string sqlSel = sql + sqlwhere + " order by id desc limit " + ((pageindex - 1) * pagesize ) + "," + pagesize;
PubConn.SqlToDataSet(ds, sqlSel, ps.ToParameters());
return(ds);
});
foreach (DataRow dr in dsList.Tables[0].Rows)
{
tb_node_model n = CreateModel(dr);
Model.Add(n);
}
count = _count;
return(Model);
}
public JsonResult ChangeMoreTaskState(string poststr)
{
return(this.Visit(EnumUserRole.Admin, () =>
{
List <PostChangeModel> post = new List <PostChangeModel>();
post = JsonConvert.DeserializeObject <List <PostChangeModel> >(poststr);
tb_command_dal dal = new tb_command_dal();
tb_task_dal taskDal = new tb_task_dal();
using (DbConn PubConn = DbConn.CreateConn(Config.TaskConnectString))
{
PubConn.Open();
foreach (PostChangeModel m in post)
{
m.state = m.state == 0 ? 1 : 0;
if (taskDal.CheckTaskState(PubConn, m.id) == m.state)
{
string msg = m.state == 1 ? "已开启" : "已关闭";
return Json(new { code = -1, msg = msg });
}
else
{
tb_command_model c = new tb_command_model()
{
command = "",
commandcreatetime = DateTime.Now,
commandname = m.state == (int)EnumTaskCommandName.StartTask ? EnumTaskCommandName.StartTask.ToString() : EnumTaskCommandName.StopTask.ToString(),
taskid = m.id,
nodeid = m.nodeid,
commandstate = (int)EnumTaskCommandState.None
};
dal.Add(PubConn, c);
RedisHelper.SendMessage(new Core.Redis.RedisCommondInfo()
{
CommondType = Core.Redis.EnumCommondType.TaskCommand, NodeId = m.nodeid
});
}
}
return Json(new { code = 1, data = post });
}
}));
}
public string GetCodeName(string st, string ct, string ar)
{
DbConn con = new DbConn();
string scode, ccode, acode;
try
{
scode = con.Db_state.SingleOrDefault(p => p.StCode == st).StName;
}
catch
{
scode = st;
}
try
{
ccode = con.Db_city.SingleOrDefault(p => p.CtCode == ct).CtName;
}
catch
{
ccode = ct;
}
try
{
acode = con.Db_area.SingleOrDefault(p => p.aCode == ar).aName;
}
catch
{
acode = ar;
}
double?charges = 0;
try
{
charges = con.Db_area.SingleOrDefault(p => p.aCode == ar).Charges;
}
catch
{
charges = 0;
}
return(scode + "|" + ccode + "|" + acode + "|" + charges);
}
public List <Dictionary <string, object> > GetChartJson(DbConn PubConn, string Fday, string Nday, string key)
{
return(SqlHelper.Visit(ps =>
{
List <Dictionary <string, object> > model = new List <Dictionary <string, object> >();
ChartDal dal = new ChartDal();
ps.Add("Fday", Fday);
ps.Add("Nday", Nday);
string sql = "select date,AVG(" + key + ") Cvalue from tb_timewatchlog_api_dayreport where date=@Fday or date=@Nday group by date";
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, sql, ps.ToParameters());
foreach (DataRow dr in ds.Tables[0].Rows)
{
Dictionary <string, object> m = new Dictionary <string, object>();
m.Add("Date", dr["date"]);
m.Add("Cvalue", dr["Cvalue"]);
model.Add(m);
}
return model;
}));
}
public virtual List <tb_mqpath_partition_model> GetList(DbConn PubConn, int mqpathid)
{
return(SqlHelper.Visit((ps) =>
{
List <tb_mqpath_partition_model> rs = new List <tb_mqpath_partition_model>();
List <ProcedureParameter> Par = new List <ProcedureParameter>();
Par.Add(new ProcedureParameter("@mqpathid", mqpathid)); Par.Add(new ProcedureParameter("@state", (int)EnumMqPathPartitionState.Running));
StringBuilder stringSql = new StringBuilder();
stringSql.Append(@"select s.* from tb_mqpath_partition s WITH(NOLOCK) where mqpathid=@mqpathid and state=@state order by partitionindex");
DataSet ds = new DataSet();
PubConn.SqlToDataSet(ds, stringSql.ToString(), Par);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
rs.Add(CreateModel(dr));
}
}
return rs;
}));
}
public int AddOrUpdate(DbConn PubConn, tb_performance_model model)
{
return(SqlHelper.Visit(ps =>
{
ps.Add("@cpu", model.cpu);
ps.Add("@memory", model.memory);
ps.Add("@installdirsize", model.installdirsize);
ps.Add("@taskid", model.taskid);
ps.Add("@nodeid", model.nodeid);
ps.Add("@lastupdatetime", model.lastupdatetime);
ps.Add("@id", model.id);
string updatecmd = "update tb_performance set cpu=@cpu,memory=@memory,installdirsize=@installdirsize,nodeid=@nodeid,lastupdatetime=@lastupdatetime where taskid=@taskid";
string insertcmd = @"insert into tb_performance(cpu,memory,installdirsize,taskid,nodeid,lastupdatetime)
values(@cpu,@memory,@installdirsize,@taskid,@nodeid,@lastupdatetime)" ;
if (PubConn.ExecuteSql(updatecmd, ps.ToParameters()) <= 0)
{
PubConn.ExecuteSql(insertcmd, ps.ToParameters());
}
return 1;
}));
}
//In this example, we show a combination of database + JavaScript rendering using FusionCharts.
//The entire app (page) can be summarized as under. This app shows the break-down
//of factory wise output generated. In a pie chart, we first show the sum of quantity
//generated by each factory. These pie slices, when clicked would show detailed date-wise
//output of that factory.
//The XML data for the pie chart is fully created in ASP.NET at run-time. ASP.NET interacts
//with the database and creates the XML for this.
//Now, for the column chart (date-wise output report), we do not submit request to the server.
//Instead we store the data for the factories in JavaScript arrays. These JavaScript
//arrays are rendered by our ASP.NET Code (at run-time). We also have a few defined JavaScript
//functions which react to the click event of pie slice.
//We've used an Access database which is present in ../App_Data/FactoryDB.mdb.
//It just contains two tables, which are linked to each other.
//Before the page is rendered, we need to connect to the database and get the
//data, as we'll need to convert this data into JavaScript variables.
/// <summary>
/// The following string will contain the JS Data and variables.
/// This string will be built in ASP.NET and rendered at run-time as JavaScript.
/// </summary>
public string GetScript()
{
//Here, we generate the JavaScript array code for the factory data.
//String to store JavaScript variables
StringBuilder jsVarString = new StringBuilder();
//Generate SQL querystring to get all factory Ids
string factoryQuery = "select FactoryId from Factory_Master";
//Sets JavaScript Array Index
int indexCount = 0;
//Create a table to store factoryIds
DbConn oRs = new DbConn(factoryQuery);
//Iterate through each record
while (oRs.ReadData.Read())
{
indexCount++;
//Build JavaScript : Create a new JavaScript Array
jsVarString.AppendFormat("\t\t data[{0}] = new Array();", indexCount.ToString());
jsVarString.Append(Environment.NewLine);
//Create an SQL Query for the current FactoryId
string outputQuery = "select DatePro, Quantity from Factory_Output where FactoryId=" + oRs.ReadData["FactoryId"].ToString() + " order by DatePro Asc";
DbConn oRs1 = new DbConn(outputQuery);
//Iterate Through records
while (oRs1.ReadData.Read())
{
//Build JavaScript : Push Factory Data into JavaScript Array
//Convert date into specific dd/MM format
jsVarString.AppendFormat("\t\t data[{0}].push(new Array('{1}',{2}));", indexCount.ToString(), ((DateTime)oRs1.ReadData["DatePro"]).ToString("dd/MM"), oRs1.ReadData["Quantity"].ToString());
jsVarString.Append(Environment.NewLine);
}
}
//Returns JavaScript variables
return jsVarString.ToString();
}
public string CreateChart()
{
//In this example, we show how to connect FusionCharts to a database.
//For the sake of ease, we've used an Access database which is present in
//../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each
//other.
//Database Objects - Initialization
DbConn oRs; string strQuery;
//strXML will be used to store the entire XML document generated
string strXML;
//Generate the graph element
strXML = "<graph caption='Factory Output report' subCaption='By Quantity' decimalPrecision='0' showNames='1' numberSuffix=' Units' pieSliceDepth='30' formatNumberScale='0' >";
//Iterate through each factory
strQuery = "select * from Factory_Master";
oRs = new DbConn(strQuery);
while(oRs.ReadData.Read()){
//Now create second recordset to get details for this factory
strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" + oRs.ReadData["FactoryId"].ToString();
DbConn oRs2 = new DbConn(strQuery);
oRs2.ReadData.Read();
//Generate <set name='..' value='..' link='..' />
//Note that we're setting link as Detailed.asp?FactoryId=<<FactoryId>>
strXML += "<set name='" + oRs.ReadData["FactoryName"].ToString() + "' value='" + oRs2.ReadData["TotOutput"].ToString() + "' link='" + Server.UrlEncode("Detailed.aspx?FactoryId=" + oRs.ReadData["FactoryId"].ToString()) + "'/>";
//Close recordset
oRs2.ReadData.Close();
}
//Finally, close <graph> element
strXML += "</graph>";
oRs.ReadData.Close();
//Create the chart - Pie 3D Chart with data from strXML
return FusionCharts.RenderChart("../FusionCharts/FCF_Pie3D.swf", "", strXML, "FactorySum", "650", "450", false, false);
}
protected void Page_Load(object sender, EventArgs e)
{
//This page is invoked from Default.aspx. When the user clicks on a pie
//slice in Default.aspx, the factory Id is passed to this page. We need
//to get that factory id, get information from database and then write XML.
//First, get the factory Id
string factoryId;
//Request the factory Id from Querystring
factoryId = Request["FactoryId"];
//xmlData will be used to store the entire XML document generated
StringBuilder xmlData=new StringBuilder();
//Create recordset to get details for the selected factory
string query = "select DatePro,Quantity from Factory_Output where FactoryId=" + factoryId;
DbConn oRs = new DbConn(query);
//Generate the chart element
xmlData.AppendFormat("<chart palette='2' caption='Factory {0} Output ' subcaption='(In Units)' xAxisName='Date (dd/MM)' showValues='1' labelStep='2' >",factoryId);
//Iterate through each record
while (oRs.ReadData.Read()){
//Convert date from database into dd/mm format
//Generate <set name='..' value='..' />
xmlData.AppendFormat("<set label='{0}' value='{1}'/>",((DateTime)oRs.ReadData["DatePro"]).ToString("dd/MM"),oRs.ReadData["Quantity"].ToString());
}
oRs.ReadData.Close();
//Close <chart> element
xmlData.Append("</chart>");
//Just write out the XML data
Response.ContentType = "text/xml";
//NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER
Response.Output.Write(xmlData.ToString());
}
public string CreateChart()
{
//Initialize the Pie chart with sum of production for each of the factories
//strXML will be used to store the entire XML document generated
string strXML, strQuery;
//Re-initialize Index
indexCount = 0;
//Generate the graph element
strXML = "<graph caption='Factory Output report' subCaption='By Quantity' decimalPrecision='0' showNames='1' numberSuffix=' Units' pieSliceDepth='20' formatNumberScale='0' >";
//Iterate through each factory
strQuery = "select * from Factory_Master";
DbConn oRs = new DbConn(strQuery);
while (oRs.ReadData.Read())
{
//Update index count - sequential
indexCount++;
//Now create second recordset to get details for this factory
strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" + oRs.ReadData["FactoryId"].ToString();
DbConn oRs2 = new DbConn(strQuery);
oRs2.ReadData.Read();
//Generate <set name='..' value='..' link='..' />
//Note that we're setting link as updateChart(factoryIndex) - JS Function
strXML += "<set name='" + oRs.ReadData["FactoryName"].ToString() + "' value='" + oRs2.ReadData["TotOutput"].ToString() + "' link='javascript:updateChart(" + indexCount + ")'/>";
//Close recordset
oRs2.ReadData.Close();
}
//Finally, close <graph> element
strXML += "</graph>";
oRs.ReadData.Read();
//Create the chart - Pie 3D Chart with data from strXML
return FusionCharts.RenderChart("../FusionCharts/FCF_Pie3D.swf", "", strXML, "FactorySum", "650", "300", false, true);
}
/// <summary>
/// Show Pie Chart on first load
/// </summary>
public void showPieChart()
{
if (!IsPostBack)
{
// SQL Query for Factory wise Total Quantity
string strSQL = "select a.FactoryId,a.FactoryName,sum(b.Quantity) as TotQ from Factory_Master a,Factory_Output b where a.FactoryId=b.FactoryID group by a.FactoryId,a.FactoryName";
// Connect DataBase and create data reader
DbConn oRs = new DbConn(strSQL);
// create strXML for XML
StringBuilder strXML = new StringBuilder();
// Add chart element
strXML.AppendFormat("<chart slicingDistance='0' caption='Factory wise Production' subcaption='Total Production in Units' formatNumberScale='0' pieSliceDepth='25'>");
// fetch data reader
while (oRs.ReadData.Read())
{
// create link to javascript function for ajax post back call
string link = "javascript:updateChart(" + oRs.ReadData["FactoryId"].ToString() + ")";
//add set element
strXML.AppendFormat("<set label='{0}' value='{1}' link='{2}' />", oRs.ReadData["FactoryName"].ToString(), oRs.ReadData["TotQ"].ToString(), link);
}
// close data reader
oRs.ReadData.Close();
// close chart element
strXML.Append("</chart>");
// create pie chart and store it to output string
Chart sales = new Chart("pie3d", "myChart1", "440", "350", "xml", strXML.ToString());
string outPut = sales.Render();
// write the output string
Response.Write(outPut);
}
}
public string CreateChart()
{
//In this example, we show how to connect FusionCharts to a database.
//For the sake of ease, we've used an Access database which is present in
//../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each
//other.
//Database Objects - Initialization
DbConn oRs; string strQuery;
//strXML will be used to store the entire XML document generated
string strXML;
//Generate the graph element
strXML = "<graph caption='Factory Output report' subCaption='By Quantity' decimalPrecision='0' showNames='1' numberSuffix=' Units' pieSliceDepth='30' formatNumberScale='0'>";
//SQL Query
strQuery = "select a.FactoryId,a.FactoryName, sum(b.Quantity) as TotOutput from factory_master a,factory_output b where a.FactoryId=b.FactoryId group by a.FactoryId,a.FactoryName";
// Open Data Reader
oRs = new DbConn(strQuery);
//Iterate through each factory
while(oRs.ReadData.Read()){
//Generate <set name='..' value='..' />
strXML += "<set name='" + oRs.ReadData["FactoryName"].ToString() + "' value='" + oRs.ReadData["TotOutput"].ToString() + "' />";
}
// Close Data Reader
oRs.ReadData.Close();
//Finally, close <graph> element
strXML += "</graph>";
//Create the chart - Pie 3D Chart with data from strXML
return FusionCharts.RenderChart("../FusionCharts/FCF_Pie3D.swf", "", strXML, "FactorySum", "650", "450", false, false);
}
public string CreateChart()
{
//This page is invoked from Default.asp. When the user clicks on a pie
//slice in Default.asp, the factory Id is passed to this page. We need
//to get that factory id, get information from database and then show
//a detailed chart.
//First, get the factory Id
string FactoryId, FactoryName;
Util util = new Util();
//Request the factory Id from Querystring
FactoryId = Request["FactoryId"];
FactoryName = Request["FactoryName"];
DbConn oRs; string strQuery;
//strXML will be used to store the entire XML document generated
string strXML;
//Generate the graph element string
strXML = "<graph caption='" + FactoryName + " Output ' subcaption='(In Units)' xAxisName='Date' showValues='1' decimalPrecision='0' rotateNames='1' >";
//Now, we get the data for that factory
strQuery = "select * from Factory_Output where FactoryId=" + FactoryId;
oRs = new DbConn(strQuery);
while(oRs.ReadData.Read()){
//Here, we convert date into a more readable form for set name.
strXML += "<set name='" + Convert.ToDateTime(oRs.ReadData["DatePro"]).ToString("dd/MM/yyyy") + "' value='" + oRs.ReadData["Quantity"].ToString() + "' color='" + util.getFCColor() + "'/>";
}
//Close <graph> element
strXML += "</graph>";
oRs.ReadData.Close();
//Create the chart - Column 2D Chart with data from strXML
return FusionCharts.RenderChart("../FusionCharts/FCF_Column2D.swf", "", strXML, "FactoryDetailed", "600", "300", false, false);
}
protected void Page_Load(object sender, EventArgs e)
{
//In this example, we show how to connect FusionCharts to a database.
//For the sake of ease, we//ve used an Access database which is present in
//../App_Data/FactoryDB.mdb. It just contains two tables, which are linked to each
//other.
//xmlData will be used to store the entire XML document generated
StringBuilder xmlData = new StringBuilder();
//We also keep a flag to specify whether we've to animate the chart or not.
//If the user is viewing the detailed chart and comes back to this page, he shouldn't
//see the animation again.
//Generate the chart element
xmlData.Append("<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'>");
//create recordset to get details for the factories
string factoryQuery = "select a.FactoryId, a.FactoryName, sum(b.Quantity) as TotQ from .Factory_Master a, Factory_Output b where a.FactoryId=b.FactoryID group by a.FactoryId, a.FactoryName";
DbConn oRs = new DbConn(factoryQuery);
//Iterate through each record
while (oRs.ReadData.Read())
{
//Generate <set name='...' value='...' link='...'/>
//The link causes drill-down by loading a another page
//The page is passed the factoryId
//Accordingly the page creates a detailed chart against that FactoryId
xmlData.Append("<set label='" + oRs.ReadData["FactoryName"].ToString() + "' value='" + oRs.ReadData["TotQ"].ToString() + "' link='" + ("Detailed.aspx?FactoryId=" + oRs.ReadData["FactoryId"].ToString()) + "'/>");
}
oRs.ReadData.Close();
//Finally, close <chart> element
xmlData.Append("</chart>");
//Create the chart - Pie 3D Chart with data from xmlData
Literal1.Text = FusionCharts.RenderChart("../FusionCharts/Pie3D.swf", "", xmlData.ToString(), "FactorySum", "600", "300", false, true);
}
/// <summary>
/// This function returns the XML data for the chart for a given subgroup.
/// </summary>
public void getChartEmpStat()
{
// Variable to store XML data
StringBuilder strXML = new StringBuilder();
// Variable to store SQL Query
string strQuery;
// Create SQL query string
strQuery = "select group_id,group_name from fcmap_group_master where group_id=" + Request["groupID"];
// Create data Reader object
DbConn Rs = new DbConn(strQuery);
// Open dataReader
if (Rs.ReadData.HasRows == true)
{
// Read first record
Rs.ReadData.Read();
// Customize XML Based on which group we've passed as Request
if (Convert.ToInt16(Rs.ReadData["group_id"]) != 3)
{
strXML.AppendFormat("<chart caption='Employment By {0} Distribution Report' showBorder='1' formatNumberScale='0' numberSuffix='' showPercentValues ='1' showPercentInToolTip='1' >", Rs.ReadData["group_name"].ToString());
}
else
{
strXML.AppendFormat("<chart caption='{0} Age Groups report' showBorder='1' formatNumberScale='0' numberSuffix='' xAxisName='Age Groups' yAxisName='Unemployed' >", Rs.ReadData["group_name"].ToString());
}
// Create Query String
strQuery = "select a.Internal_Id,a.entity_id,b.group_name,c.subgroup_name,sum(a.data) as datap from fcmap_distribution a, fcmap_group_master b, fcmap_subgroup_master c where a.group_id=b.group_id and a.subgroup_id=c.subgroup_id group by a.Internal_Id,a.entity_id,b.group_name,c.subgroup_name having a.Internal_Id='" + Request["Internal_Id"] + "' and a.entity_id='" + Request["entity_id"] + "' and b.group_name='" + Rs.ReadData["group_name"].ToString() + "'";
// Create data Reader object
DbConn Rs1 = new DbConn(strQuery);
// Iterate through each group-wise record
if (Rs1.ReadData.HasRows == true)
{
// Fetch all records
while (Rs1.ReadData.Read())
{
// Generate <set label='..' value='..' />
strXML.AppendFormat("<set label='{0}' value='{1}' />", Rs1.ReadData["subgroup_name"].ToString(), Rs1.ReadData["datap"].ToString());
}
}
Rs1.ReadData.Close();
}
Rs.ReadData.Close();
// If needed, you can append additional XML tags here - like STYLE or MARKERS
strXML.Append("<styles><definition><style name='captionFont' type='Font' size='14' /></definition><application><apply toObject='caption' styles='captionFont'/></application></styles>");
// Finally, close <chart> element
strXML.Append("</chart>");
// Set Proper output content-type
Response.ContentType = "text/xml";
// Just write out the XML data
// NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER
Response.Write(strXML.ToString());
}
/// <summary>
/// This program creates XML for USA Map to show polulation % of each state
/// </summary>
public void GetUSMapDetails()
{
/*
In this example, we show how to connect FusionMaps to a database.
You can connect to any database. Here, we've shown MSSQL/Access.
*/
//strXML will be used to store the entire XML document generated
StringBuilder strXML = new StringBuilder();
//Variable to store SQL Queries
string strQuery;
//Variable to store total Population
double sumdata;
//Generate the map element
//Create the opening <map> element and add the attributes that we need.
strXML.Append("<map borderColor='FFFFFF' fillAlpha='80' showBevel='0' numberSuffix='% of total US population' legendBorderColor='F1f1f1' hoverColor='FFFFFF' legendPosition='bottom'>");
//Define color ranges
strXML.Append("<colorRange>");
strXML.Append("<color minValue='0' maxValue='0.50' displayValue='0% to 0.50% of total' color='D64646' />");
strXML.Append("<color minValue='0.50' maxValue='1' displayValue='0.50% to 1% of total' color='F6BD0F' />");
strXML.Append("<color minValue='1' maxValue='3' displayValue='1% to 3% of total' color='8BBA00' />");
strXML.Append("<color minValue='3' maxValue='10' displayValue='3% or above of total' color='AFD8F8' />");
strXML.Append("</colorRange>");
//store the sql query
//create the datareader object to connect to table
strQuery = "select sum(data) as datap from fcmap_distribution";
DbConn Rs = new DbConn(strQuery);
//Initialize sum container
sumdata = 0;
if (Rs.ReadData.HasRows == true)
{ // read first record
Rs.ReadData.Read();
// Store sum
sumdata = Convert.ToDouble(Rs.ReadData["datap"]);
}
//close the reader
Rs.ReadData.Close();
//Fetch all Internal id and data sum
strQuery = "select Internal_Id, (sum(data) / " + sumdata + ")*100 as datap from fcmap_distribution group by Internal_Id";
DbConn Rs1 = new DbConn(strQuery);
// Add map data element
strXML.Append("<data>");
//Check if we've records to show
if (Rs1.ReadData.HasRows == true)
{
//Iterate through the database
while (Rs1.ReadData.Read())
{
// Create query string
strQuery = "select map_swf from fcmap_master where Internal_Id='" + Rs1.ReadData["Internal_Id"].ToString() + "'";
// Open fcmap_master table to get map swf names
DbConn Rs2 = new DbConn(strQuery);
// Read first record
Rs2.ReadData.Read();
// The link will in format StateDetails.aspx?Internal_Id=Int_Id&map=map_swf.swf - we'll need to URL Encode this link to convert & to %26 (or manually add it as %26 instead of &)
string LinkURL = Server.UrlEncode("StateDetails.aspx?Internal_Id=" + Rs1.ReadData["Internal_Id"].ToString() + "&map=" + Rs2.ReadData["map_swf"].ToString());
// Generate <entity id=".." value=".." /> and also add link to it
strXML.AppendFormat("<entity id='{0}' value='{1}' link='{2}' />", Rs1.ReadData["Internal_Id"].ToString(), Math.Round(Convert.ToDouble(Rs1.ReadData["datap"]), 2), LinkURL);
Rs2.ReadData.Close();
}
}
// Close reader
Rs1.ReadData.Close();
// Finally, close <map> element and add
strXML.Append("</data>");
// If needed, you can append additional XML tags here - like STYLE or MARKERS
strXML.Append("<styles><definition><style type='animation' name='animX' param='_xscale' start='0' duration='1' /><style type='animation' name='animY' param='_yscale' start='0' duration='1' /><style type='animation' name='animAlpha' param='_alpha' start='0' duration='1' /><style type='shadow' name='myShadow' color='FFFFFF' distance='1' /></definition>");
strXML.Append("<application><apply toObject='PLOT' styles='animX,animY' /><apply toObject='LABELS' styles='myShadow,animAlpha' /></application></styles>");
// Close Map element
strXML.Append("</map>");
// Set Proper output content-type
Response.ContentType = "text/xml";
// Just write out the XML data
// NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER
Response.Write(strXML.ToString());
}
public void GetjsVar()
{
//In this example, we show a combination of database + JavaScript rendering using FusionCharts.
//The entire app (page) can be summarized as under. This app shows the break-down
//of factory wise output generated. In a pie chart, we first show the sum of quantity
//generated by each factory. These pie slices, when clicked would show detailed date-wise
//output of that factory.
//The XML data for the pie chart is fully created in ASP at run-time. ASP interacts
//with the database and creates the XML for this.
//Now, for the column chart (date-wise output report), we do not submit request to the server.
//Instead we store the data for the factories in JavaScript arrays. These JavaScript
//arrays are rendered by our ASP Code (at run-time). We also have a few defined JavaScript
//functions which react to the click event of pie slice.
//We've used an Access database which is present in ../DB/FactoryDB.mdb.
//It just contains two tables, which are linked to each other.
//Before the page is rendered, we need to connect to the database and get the
//data, as we'll need to convert this data into JavaScript variables.
//The following string will contain the JS Data and variables.
//This string will be built in ASP and rendered at run-time as JavaScript.
jsVarString = "";
//Database Objects
DbConn oRs; string strQuery;
indexCount = 0;
//Iterate through each factory
strQuery = "select * from Factory_Master";
oRs = new DbConn(strQuery);
while (oRs.ReadData.Read())
{
indexCount++;
//Create JavaScript code to add sub-array to data array
//data is an array defined in JavaScript (see below)
//We've added vbTab & vbCRLF to data so that if you View Source of the
//output HTML, it will appear properly. It helps during debugging
jsVarString += "\t\t data[" + indexCount + "] = new Array();" + "\n";
//Now create second recordset to get date-wise details for this factory
strQuery = "select * from Factory_Output where FactoryId=" + oRs.ReadData["FactoryId"].ToString() + " order by DatePro Asc";
DbConn oRs2 = new DbConn(strQuery);
while (oRs2.ReadData.Read())
{
//Put this data into JavaScript as another nested array.
//Finally the array would look like data[factoryIndex][i][dataLabel,dataValue]
jsVarString += "\t\t data[" + indexCount + "].push(new Array('" + Convert.ToDateTime(oRs2.ReadData["DatePro"]).ToString("dd/MM/yyyy") + "'," + oRs2.ReadData["Quantity"].ToString() + "));" + "\n";
}
//Close recordset
oRs2.ReadData.Close();
}
oRs.ReadData.Read();
}
/// <summary>
/// Get State Details : Business, Software, Employee
/// </summary>
public void GetStateDetails()
{
// Variables to store XML Data and sum of data
// strXML will be used to store the entire XML document generated
StringBuilder strXML = new StringBuilder();
double sumdata;
//Variable to store SQL Query
string strQuery;
// Generate the map element
strXML.Append("<map borderColor='FFFFFF' fillAlpha='80' hoverColor='FFFFFF' showBevel='0' legendBorderColor='F1f1f1' legendPosition='bottom'>");
//Define color ranges
strXML.Append("<colorRange>");
strXML.Append("<color minValue='0' maxValue='93' displayValue='0% to 93%' color='D64646' />");
strXML.Append("<color minValue='93' maxValue='94' displayValue='93% to 94%' color='F6BD0F' />");
strXML.Append("<color minValue='94' maxValue='95' displayValue='94% to 95%' color='8BBA00' />");
strXML.Append("<color minValue='95' maxValue='100' displayValue='95% or above' color='AFD8F8' />");
strXML.Append("</colorRange>");
//Start the <data> element
strXML.Append("<data>");
// Fetch entity records
strQuery = "select a.Internal_Id,a.entity_id,sum(data) as datap from fcmap_distribution a group by a.Internal_Id,a.entity_id having a.Internal_Id='" + Request["Internal_Id"] + "'";
DbConn Rs = new DbConn(strQuery);
// Set sumdata to Zero
sumdata = 0;
// If we've records to iterate, proceed
if (Rs.ReadData.HasRows == true)
{
// Read data reader till end
while (Rs.ReadData.Read())
{
// We create custom tool text for each entity.
string tooltext = "";
// Total counter
double totEmp = 0;
double StateValue, TotalStateValue, StatePer;
StateValue = 0; TotalStateValue = 0; StatePer = 0;
//Get details for the region
strQuery = "select a.Internal_Id,a.entity_id,b.group_name,sum(data) as datap from fcmap_distribution a, fcmap_group_master b where b.group_id=a.group_id group by a.Internal_Id ,a.entity_id, b.group_name having a.Internal_Id='" + Rs.ReadData["Internal_Id"].ToString() + "' and entity_id='" + Rs.ReadData["entity_id"].ToString() + "'";
DbConn Rs1 = new DbConn(strQuery);
//Read data till end
while (Rs1.ReadData.Read())
{
// Calculate value
StateValue = Convert.ToDouble(Rs1.ReadData["datap"]);
TotalStateValue = Convert.ToDouble(Rs.ReadData["datap"]);
// Get percentage of employment
StatePer = Math.Round((StateValue / TotalStateValue) * 100, 2);
// Add to tooltext
tooltext += Rs1.ReadData["group_name"].ToString() + ":" + StatePer + "% \n";
// If it's not unemployed group
if (Rs1.ReadData["group_name"].ToString() != "Unemployed")
{ //calculate total employed
totEmp += StatePer;
}
}
//close data reader
Rs1.ReadData.Close();
// Generate <entity id=".." value=".." />
// Also append link to Charts.aspx passing all required information (from querystring) and the database
// We also add our custom tooltext
string LinkURL = Server.UrlEncode("Charts.aspx?" + Request.ServerVariables["QUERY_STRING"] + "&entity_id=" + Rs.ReadData["entity_id"].ToString());
strXML.AppendFormat("<entity id='{0}' value='{1}' link='{2}' tooltext='{3}' />", Rs.ReadData["entity_id"].ToString(), totEmp, LinkURL, tooltext);
}
}
//close data reader
Rs.ReadData.Close();
// Finally, close <data> element
strXML.Append("</data>");
//Add style
strXML.Append("<styles><definition><style type='animation' name='animX' param='_xscale' start='0' duration='1' /><style type='animation' name='animY' param='_yscale' start='0' duration='1' /><style type='animation' name='animAlpha' param='_alpha' start='0' duration='1' /><style type='shadow' name='myShadow' color='FFFFFF' distance='1' /></definition>");
strXML.Append("<application><apply toObject='PLOT' styles='animX,animY' /><apply toObject='LABELS' styles='myShadow,animAlpha' /></application></styles>");
// Close map element
strXML.Append("</map>");
// Set Proper output content-type
Response.ContentType = "text/xml";
// Just write out the XML data
// NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER
Response.Write(strXML.ToString());
}
/// <summary>
/// drillDown to show Column2D chart
/// </summary>
/// <param name="FacID">Factory Id</param>
private void drillDown(string FacID)
{
//SQL Query for Factory Details for the factory Id passed as parameter
string strSQL = "select a.FactoryId,a.FactoryName,b.DatePro,b.Quantity from Factory_Master a,Factory_Output b where a.FactoryId=b.FactoryID and a.FactoryId=" + FacID + " order by b.DatePro";
// Create data reader
DbConn oRs = new DbConn(strSQL);
//strXML for storing XML
StringBuilder strXML = new StringBuilder();
//Add Chart element
strXML.AppendFormat("<chart caption='Factory wise Production' subcaption='Factory {0} : Daily Production' xAxisName='Day' yAxisName='Units' rotateLabels='1' bgAlpha='100' bgColor='ffffff' showBorder='0' showvalues='0' yAxisMaxValue='200'>", FacID);
//Iterate through database
while (oRs.ReadData.Read())
{
// add set element
strXML.AppendFormat("<set label='{0}' value='{1}' />", Convert.ToDateTime(oRs.ReadData["DatePro"]).ToString("d/M"), oRs.ReadData["Quantity"].ToString());
}
// close data reader
oRs.ReadData.Close();
// close chart element
strXML.Append("</chart>");
// create Column2D chart and srore it to output string
string outPut = FusionCharts.RenderChartHTML("../FusionCharts/Column2D.swf", "", strXML.ToString(), "chart2", "450", "350", false);
// clear the Panel
Panel1.Controls.Clear();
//Add chart to the panel
Panel1.Controls.Add(new LiteralControl(outPut));
}
protected void Page_Load(object sender, EventArgs e)
{
// Create the `xmlData` StringBuilder object to store the data fetched
//from the database as a string.
StringBuilder xmlData = new StringBuilder();
// Initialize the chart-level attributes and append them to the
//`xmlData` StringBuilder object.
xmlData.Append("<chart caption='Factory Output report' subCaption='By Quantity' showBorder='1' formatNumberScale='0' rotatelabels='1' showvalues='0'>");
// Initialize the `<categories>` element.
xmlData.AppendFormat("<categories>");
// Every date between January 01, 2003 and January 20, 2003 is entered thrice
// in the **datepro** field in the **FactoryDB **database.
// The dates will be shown as category labels on the x-axis of the chart.
// Because we need to show each date only once, use the `select` query
// with the `distinct` keyword to fetch only one instance of each date from the database.
// Store the output of the `select` query in the `factoryQuery` string variable.
string factoryQuery = "select distinct format(date,'dd/mm/yyyy') as dd from Gas_Total_Production_Field";
// Establish the database connection.
DbConn oRs = new DbConn(factoryQuery);
// Iterate through the data in the `factoryQuery` variable and add the dates as
// labels to the `<category>` element.
// Append this data to the `xmlData` object.
while (oRs.ReadData.Read())
{
xmlData.AppendFormat("<category label='{0}'/>", oRs.ReadData["dd"].ToString());
}
//Close the database connection.
oRs.ReadData.Close();
//Close the `<catgories>` element.
xmlData.AppendFormat("</categories>");
//Fetch all details for the three factories from the **Factory_Master** table
// and store the result in the `factoryquery2` variable.
string factoryquery2 = "select distinct phe23 from Gas_Total_Production_Field";
//Establish the database connection..
DbConn oRs1 = new DbConn(factoryquery2);
// Iterate through the results in the `factoryquery2` variable to fetch the
// factory name and factory id.
while (oRs1.ReadData.Read())
{
// Append the factory name as the value for the `seriesName` attribute.
xmlData.AppendFormat("<dataset seriesName='{0}'>", oRs1.ReadData["phe23"]);
// Based on the factory id, fetch the quantity produced by each factory on each day
// from the factory_output table.
// Store the results in the `factoryquery3` string object.
string factoryquery3 = "select quantity from factory_output where factoryid=" + oRs1.ReadData["factoryid"].ToString();
//Establish the database connection.
DbConn oRs2 = new DbConn(factoryquery3);
// Iterate through the results in the `factoryquery3` object and fetch the quantity details
// for each factory.
// Append the quantity details as the the value for the `<set>` element.
// while (oRs2.ReadData.Read())
// {
// xmlData.AppendFormat("<set value='{0}'/>", oRs2.ReadData[0].ToString());
// }
// Close the database connection.
oRs2.ReadData.Close();
// Close the `<dataset>` element.
xmlData.AppendFormat("</dataset>");
}
// Close the database connection.
oRs1.ReadData.Close();
// Close the `<chart>` element.
xmlData.AppendFormat("</chart>");
// Initialize the chart.
Chart factoryOutput = new Chart("msline", "myChart", "600", "350", "xml", xmlData.ToString());
// Render the chart.
Literal Literal1 = (Literal)FindControl("Literal1");
Literal1.Text = factoryOutput.Render();
}
protected void Page_Load(object sender, EventArgs e)
{
// Create the `jsonData` StringBuilder object to store the data fetched
//from the database as a string.
StringBuilder jsonData = new StringBuilder();
bool ReqDatasetComma = false, ReqComma = false;
// Initialize the chart-level attributes and append them to the
//`jsonData` StringBuilder object.
jsonData.Append("{" +
"'chart': {"+
// add chart level attrubutes
"'caption': 'Factory Output report'," +
"'subCaption': 'By Quantity',"+
"'formatNumberScale': '0'," +
"'rotatelabels': '1'," +
"'showvalues': '0'," +
"'showBorder': '1'" +
"},");
// Initialize the Categories object.
jsonData.Append("'categories': [" +
"{" +
"'category': [");
// Every date between January 01, 2003 and January 20, 2003 is entered thrice
// in the datepro field in the FactoryDB database.
// The dates will be shown as category labels on the x-axis of the chart.
// Because we need to show each date only once, use the `select` query
// with the `distinct` keyword to fetch only one instance of each date from the database.
// Store the output of the `select` query in the `factoryQuery` string variable.
string factoryQuery = "select distinct format(datepro,'dd/mm/yyyy') as dd from factory_output";
// Establish the database connection.
DbConn oRs = new DbConn(factoryQuery);
// Iterate through the data in the `factoryQuery` variable and add the dates as
// labels to the category object.
// Append this data to the `jsonData` object.
while (oRs.ReadData.Read())
{
if (ReqComma) {
jsonData.Append(",");
}
else {
ReqComma = true;
}
jsonData.AppendFormat("{{" +
// category level attributes
"'label': '{0}'" +
"}}", oRs.ReadData["dd"].ToString());
}
//Close the database connection.
oRs.ReadData.Close();
//Close the catgories object.
jsonData.Append("]" +
"}" +
"],");
// Initialize the Dataset object.
jsonData.Append("'dataset': [");
//Fetch all details for the three factories from the Factory_Master table
// and store the result in the `factoryquery2` variable.
string factoryquery2 = "select * from factory_master";
//Establish the database connection.
DbConn oRs1 = new DbConn(factoryquery2);
// Iterate through the results in the `factoryquery2` variable to fetch the
// factory name and factory id.
while (oRs1.ReadData.Read())
{
if (ReqDatasetComma) {
jsonData.Append(",");
}
else {
ReqDatasetComma = true;
}
// Append the factory name as the value for the `seriesName` attribute.
jsonData.AppendFormat("{{" +
// dataset level attributes
"'seriesname': '{0}'," +
"'data': [", oRs1.ReadData["factoryname"].ToString());
// Based on the factory id, fetch the quantity produced by each factory on each day
// from the factory_output table.
// Store the results in the `factoryquery3` string object.
string factoryquery3 = "select quantity from factory_output where factoryid=" + oRs1.ReadData["factoryid"].ToString();
//Establish the database connection.
DbConn oRs2 = new DbConn(factoryquery3);
ReqComma = false;
// Iterate through the results in the `factoryquery3` object and fetch the quantity details
// for each factory.
// Append the quantity details as the the value for the `<set>` element.
while (oRs2.ReadData.Read())
{
if (ReqComma) {
jsonData.Append(",");
}
else {
ReqComma = true;
}
jsonData.AppendFormat("{{" +
// data set attributes
"'value': '{0}'" +
"}}", oRs2.ReadData[0].ToString());
}
// Close the database connection.
oRs2.ReadData.Close();
// Close individual dataset object.
jsonData.Append("]" +
"}");
}
// Close the database connection.
oRs1.ReadData.Close();
// Close the JSON object.
jsonData.Append("]" +
"}");
// Initialize chart - MSLine Chart data pulling from database
Chart factoryOutput = new Chart("msline", "myChart", "600", "350", "json", jsonData.ToString());
// Render the chart
Literal1.Text = factoryOutput.Render();
}
