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