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(); }