public static string Q2A1Web(string obj1, string obj2, string obj3) { Charting obj = new Charting(); obj.categories = new List <string>(); // DataSet ds = Fn.FillDSet(@"SELECT tblGeneratorReFueling.InputOil, tblGeneratorReFueling.InputOil * tblGeneratorReFueling.FuelPrice AS Price, CONVERT(VARCHAR(50),tblGeneratorReFueling.FuelingTime,103) //+ ' ' + LTRIM(RIGHT(CONVERT(VARCHAR(20), tblGeneratorReFueling.FuelingTime, 100), 7)) // AS [Date] //FROM tblGeneratorReFueling INNER JOIN // tblGenerator ON tblGeneratorReFueling.tblGeneratorID = tblGenerator.tblGeneratorID INNER JOIN // tbl_Company ON tblGenerator.LocationID = tbl_Company.CompanyID //WHERE (tblGeneratorReFueling.dtDateTime BETWEEN CONVERT(DATETIME, '"+obj1+@"', 103) AND DATEADD(MILLISECOND, - 2, DATEADD(DD, 1, CONVERT(DATETIME, '"+obj2+@"', 103)))) AND // (tbl_Company.CompanyName = '"+obj3+@"') // ORDER BY tblGeneratorReFueling.FuelingTime "); DataSet ds = Fn.FillDSet(@"exec Q2A1Web '" + obj1 + "', '" + obj2 + "', '" + obj3 + "'"); DataTable d = ds.Tables[0]; int colCount = d.Columns.Count; List <string> colName = new List <string>(); for (int i = 0; i < colCount; i++) { colName.Add(d.Columns[i].ColumnName); } for (int i = 0; i < d.Rows.Count; i++) { obj.categories.Add(Convert.ToString(d.Rows[i][2])); } obj.Series = new List <Seris>(); for (int i = 0; i < colName.Count() - 1; i++) { Seris ss = new Seris(); ss.Mydata = new List <string>(); ss.Myname = colName[i]; for (int ii = 0; ii < d.Rows.Count; ii++) { ss.Mydata.Add(Convert.ToString(d.Rows[ii][i])); } obj.Series.Add(ss); } string ret = JsonConvert.SerializeObject(obj); return(ret); }
public static string GenChart(string Q, string yr) { //string[] F = Q.Split('/'); //string[] T = yr.Split('/'); Charting obj = new Charting(); obj.categories = new List <string>(); DataSet ds = Fn.FillDSet(@"exec sp_db_GenChart '" + Q + "','" + yr + "'"); DataTable d = ds.Tables[0]; int colCount = d.Columns.Count; List <string> colName = new List <string>(); for (int i = 0; i < colCount; i++) { colName.Add(d.Columns[i].ColumnName); } for (int i = 0; i < d.Rows.Count; i++) { obj.categories.Add(Convert.ToString(d.Rows[i][3])); } obj.Series = new List <Seris>(); for (int i = 0; i < colName.Count() - 1; i++) { Seris ss = new Seris(); ss.Mydata = new List <string>(); ss.Myname = colName[i]; for (int ii = 0; ii < d.Rows.Count; ii++) { ss.Mydata.Add(Convert.ToString(d.Rows[ii][i])); } obj.Series.Add(ss); } string ret = JsonConvert.SerializeObject(obj); return(ret); }
public static string Q4(string Q, string yr) { // Charting obj = new Charting(); // obj.categories = new List<string>(); // DataSet ds = Fn.FillDSet(@"dbo.CrossTab ' //SELECT NAME, Days, Day, CONVERT(DATETIME,Day,103) AS DD //FROM ( // //SELECT COUNT(*) AS Days, UserID, Day, NAME //FROM (SELECT tblUserLog.UserID, CONVERT(VARCHAR(50), tblUserLog.LoginDT, 103) AS Day, TblHResources.Full_Name AS NAME // FROM tblUserLog INNER JOIN // TblHResources ON tblUserLog.UserID = TblHResources.User_ID // WHERE (tblUserLog.LoginDT BETWEEN CONVERT(DATETIME, ''" + Q + @"'', 103) AND CONVERT(DATETIME, ''" + yr + @"'', 103))) AS xxx // //GROUP BY xxx.UserID, xxx.Day, xxx.NAME // // ) AS X // //', //'Name','max(Days ELSE 0 )[]',N'DD,Day',NULL,',1,1'"); // DataTable d = ds.Tables[0]; // int colCount = d.Columns.Count; // List<string> colName = new List<string>(); // for (int i = 2; i < colCount; i++) // { // colName.Add(d.Columns[i].ColumnName); // } // for (int i = 0; i < d.Rows.Count; i++) // { // obj.categories.Add(Convert.ToString(d.Rows[i][1])); // // obj.categories.Add(Convert.ToString(i)); // } // obj.Series = new List<Seris>(); // for (int i = 0; i < colName.Count(); i++) // { // Seris ss = new Seris(); // ss.Mydata = new List<string>(); // ss.Myname = colName[i]; // for (int ii = 0; ii < d.Rows.Count; ii++) // { // ss.Mydata.Add(Convert.ToString(d.Rows[ii][i+2])); // } // obj.Series.Add(ss); // } // string ret = JsonConvert.SerializeObject(obj); // return ret; Charting obj = new Charting(); obj.categories = new List <string>(); // DataSet ds = Fn.FillDSet(@"SELECT NAME, Count(*) as Logins //FROM (SELECT tblUserLog.UserID, TblHResources.Full_Name AS NAME // FROM tblUserLog INNER JOIN // TblHResources ON tblUserLog.UserID = TblHResources.User_ID // WHERE (tblUserLog.UserID!=10037) AND (tblUserLog.LoginDT BETWEEN CONVERT(DATETIME, '" + Q + @"', 103) AND DATEADD(MINUTE, 1439,CONVERT(DATETIME, '" + yr + @"', 103)))) AS x //GROUP BY NAME"); DataSet ds = Fn.FillDSet(@"exec sp_db_Q4 '" + Q + "','" + yr + "'"); DataTable d = ds.Tables[0]; int colCount = d.Columns.Count; List <string> colName = new List <string>(); //for (int i = 0; i < colCount; i++) //{ // colName.Add(d.Columns[i].ColumnName); //} for (int i = 0; i < d.Rows.Count; i++) { colName.Add(Convert.ToString(d.Rows[i][0])); } for (int i = 0; i < d.Rows.Count; i++) { obj.categories.Add(Convert.ToString(d.Rows[i][0])); } obj.Series = new List <Seris>(); for (int i = 0; i < d.Rows.Count; i++) { Seris ss = new Seris(); ss.Mydata = new List <string>(); //ss.Myname = colName[i]; ss.Myname = Convert.ToString(d.Rows[i][0]); ss.Mydata.Add(Convert.ToString(d.Rows[i][1])); //ss.Mydata.Add(Convert.ToString(d.Rows[i][1])); //for (int ii = 0; ii < d.Rows.Count; ii++) //{ // ss.Mydata.Add(Convert.ToString(d.Rows[ii][1])); //} obj.Series.Add(ss); } string ret = JsonConvert.SerializeObject(obj); return(ret); }