public HttpResponseMessage get_last_six_month_order_of_driver(string driver_id) { JObject obj = new JObject(); try { SqlCommand cmd = new SqlCommand(); string sql = @"spGetLastSixMonthOrderOfDriver"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@driver_id", driver_id); DataTable dt = DAL.Select(sql, cmd); List <object> x_axis = new List <object>(); List <object> y_axis = new List <object>(); foreach (DataRow dr in dt.Rows) { x_axis.Add(dr["month"].ToString()); y_axis.Add(dr["count"].ToString()); } DashboardGraph dashboardGraph = new DashboardGraph(); dashboardGraph.x_axis = x_axis; dashboardGraph.y_axis = y_axis; return(Request.CreateResponse(HttpStatusCode.OK, dashboardGraph)); } catch (Exception ex) { obj["success"] = false; obj["data"] = ex.Message + " " + ex.StackTrace; return(Request.CreateResponse(HttpStatusCode.InternalServerError, obj)); } }
public static void Start(SchemaBuilder sb, DynamicQueryManager dqm) { if (sb.NotDefined(MethodInfo.GetCurrentMethod())) { GetDashboard = GetDashboardDefault; PermissionAuthLogic.RegisterPermissions(DashboardPermission.ViewDashboard); UserAssetsImporter.RegisterName <DashboardEntity>("Dashboard"); UserAssetsImporter.PartNames.AddRange(new Dictionary <string, Type> { { "UserChartPart", typeof(UserChartPartEntity) }, { "UserQueryPart", typeof(UserQueryPartEntity) }, { "LinkListPart", typeof(LinkListPartEntity) }, { "ValueUserQueryListPart", typeof(ValueUserQueryListPartEntity) }, }); sb.Include <DashboardEntity>() .WithQuery(dqm, () => cp => new { Entity = cp, cp.Id, cp.DisplayName, cp.EntityType, cp.Owner, cp.DashboardPriority, }); sb.Include <LinkListPartEntity>() .WithQuery(dqm, () => cp => new { Entity = cp, ToStr = cp.ToString(), Links = cp.Links.Count }); sb.Include <ValueUserQueryListPartEntity>() .WithQuery(dqm, () => cp => new { Entity = cp, ToStr = cp.ToString(), Links = cp.UserQueries.Count }); if (sb.Settings.ImplementedBy((DashboardEntity cp) => cp.Parts.First().Content, typeof(UserQueryPartEntity))) { sb.Schema.EntityEvents <UserQueryEntity>().PreUnsafeDelete += query => { Database.MListQuery((DashboardEntity cp) => cp.Parts).Where(mle => query.Contains(((UserQueryPartEntity)mle.Element.Content).UserQuery)).UnsafeDeleteMList(); Database.Query <UserQueryPartEntity>().Where(uqp => query.Contains(uqp.UserQuery)).UnsafeDelete(); }; sb.Schema.Table <UserQueryEntity>().PreDeleteSqlSync += arg => { var uq = (UserQueryEntity)arg; var parts = Administrator.UnsafeDeletePreCommand((DashboardEntity cp) => cp.Parts, Database.MListQuery((DashboardEntity cp) => cp.Parts) .Where(mle => ((UserQueryPartEntity)mle.Element.Content).UserQuery == uq)); var parts2 = Administrator.UnsafeDeletePreCommand(Database.Query <UserQueryPartEntity>() .Where(mle => mle.UserQuery == uq)); return(SqlPreCommand.Combine(Spacing.Simple, parts, parts2)); }; } if (sb.Settings.ImplementedBy((DashboardEntity cp) => cp.Parts.First().Content, typeof(UserChartPartEntity))) { sb.Schema.EntityEvents <UserChartEntity>().PreUnsafeDelete += query => { Database.MListQuery((DashboardEntity cp) => cp.Parts).Where(mle => query.Contains(((UserChartPartEntity)mle.Element.Content).UserChart)).UnsafeDeleteMList(); Database.Query <UserChartPartEntity>().Where(uqp => query.Contains(uqp.UserChart)).UnsafeDelete(); }; sb.Schema.Table <UserChartEntity>().PreDeleteSqlSync += arg => { var uc = (UserChartEntity)arg; var parts = Administrator.UnsafeDeletePreCommand((DashboardEntity cp) => cp.Parts, Database.MListQuery((DashboardEntity cp) => cp.Parts) .Where(mle => ((UserChartPartEntity)mle.Element.Content).UserChart == uc)); var parts2 = Administrator.UnsafeDeletePreCommand(Database.Query <UserChartPartEntity>() .Where(mle => mle.UserChart == uc)); return(SqlPreCommand.Combine(Spacing.Simple, parts, parts2)); }; } DashboardGraph.Register(); Dashboards = sb.GlobalLazy(() => Database.Query <DashboardEntity>().ToDictionary(a => a.ToLite()), new InvalidateWith(typeof(DashboardEntity))); DashboardsByType = sb.GlobalLazy(() => Dashboards.Value.Values.Where(a => a.EntityType != null) .GroupToDictionary(a => TypeLogic.IdToType.GetOrThrow(a.EntityType.Id), a => a.ToLite()), new InvalidateWith(typeof(DashboardEntity))); } }
public HttpResponseMessage get_top_driver_wrt_transporter(string period, string transporter_id, string date_from, string date_to) { //date_from and date_to in yyyy-MM-dd format. JObject obj = new JObject(); try { string condition_sql = ""; if (period.Trim().ToLower() == "this week") { condition_sql = " CAST(tbl_order_detail.completion_datetime as date) >= cast (GETDATE() - (case when DATEPART(dw, getDate()) = 1 then 6 else DATEPART(dw, getDate()) end) as date) "; } else if (period.Trim().ToLower() == "last week") { condition_sql = @" CAST(tbl_order_detail.completion_datetime as date) >= cast (GETDATE() - (case when DATEPART(dw, getDate()) = 1 then 6 else DATEPART(dw, getDate()) end) - 7 as date) and CAST(tbl_order_detail.completion_datetime as date) <= cast (GETDATE() - (case when DATEPART(dw, getDate()) = 1 then 6 else DATEPART(dw, getDate()) end) - 1 as date) "; } else if (period.Trim().ToLower() == "this month") { condition_sql = @"month(tbl_order_detail.completion_datetime) = month (GETDATE()) and YEAR(tbl_order_detail.completion_datetime) = YEAR(getdate())"; } else if (period.Trim().ToLower() == "last month") { condition_sql = @"month(tbl_order_detail.completion_datetime) = case when MONTH(getDate()) = 1 then 12 else MONTH(getdate()) - 1 end and YEAR(tbl_order_detail.completion_datetime) = YEAR(getdate())"; } else if (period.Trim().ToLower() == "other") { condition_sql = @"tbl_order_detail.completion_datetime between '" + date_from + "' and '" + date_to + "'"; } string sql = @"select top (5) tbl_order_detail.driver_id, UPPER(LEFT(tbl_users.first_name,1)) + LOWER(SUBSTRING(tbl_users.first_name, 2, LEN(tbl_users.first_name))) + ' ' + UPPER(LEFT(tbl_users.last_name,1)) + LOWER(SUBSTRING(tbl_users.last_name, 2, LEN(tbl_users.last_name))) as name, COUNT(order_detail_id) as total_orders from tbl_order_detail inner join tbl_users on tbl_order_detail.driver_id = tbl_users.user_id inner join tbl_driver_transporter on tbl_driver_transporter.driver_id = tbl_users.user_id where " + condition_sql + @" and tbl_driver_transporter.transporter_id = 7 and tbl_order_detail.status_id = 5 group by tbl_order_detail.driver_id, tbl_users.first_name, tbl_users.last_name order by AVG(tbl_order_detail.rating)"; SqlCommand cmd = new SqlCommand(); cmd.Parameters.AddWithValue("@transporter_id", transporter_id); DataTable dt = DAL.Select(sql, cmd); List <object> x_axis = new List <object>(); List <object> y_axis = new List <object>(); foreach (DataRow dr in dt.Rows) { x_axis.Add(dr["name"].ToString()); y_axis.Add(dr["total_orders"].ToString()); } DashboardGraph dashboardGraph = new DashboardGraph(); dashboardGraph.x_axis = x_axis; dashboardGraph.y_axis = y_axis; return(Request.CreateResponse(HttpStatusCode.OK, dashboardGraph)); } catch (Exception ex) { obj["success"] = false; obj["data"] = ex.Message + " " + ex.StackTrace; } return(Request.CreateResponse(HttpStatusCode.InternalServerError, obj)); }
public static void Start(SchemaBuilder sb) { if (sb.NotDefined(MethodInfo.GetCurrentMethod())) { PermissionAuthLogic.RegisterPermissions(DashboardPermission.ViewDashboard); UserAssetsImporter.Register <DashboardEntity>("Dashboard", DashboardOperation.Save); UserAssetsImporter.PartNames.AddRange(new Dictionary <string, Type> { { "UserChartPart", typeof(UserChartPartEntity) }, { "CombinedUserChartPart", typeof(CombinedUserChartPartEntity) }, { "UserQueryPart", typeof(UserQueryPartEntity) }, { "LinkListPart", typeof(LinkListPartEntity) }, { "ValueUserQueryListPart", typeof(ValueUserQueryListPartEntity) }, { "UserTreePart", typeof(UserTreePartEntity) }, }); sb.Include <DashboardEntity>() .WithQuery(() => cp => new { Entity = cp, cp.Id, cp.DisplayName, cp.EntityType, cp.Owner, cp.DashboardPriority, }); if (sb.Settings.ImplementedBy((DashboardEntity cp) => cp.Parts.First().Content, typeof(UserQueryPartEntity))) { sb.Schema.EntityEvents <UserQueryEntity>().PreUnsafeDelete += query => { Database.MListQuery((DashboardEntity cp) => cp.Parts).Where(mle => query.Contains(((UserQueryPartEntity)mle.Element.Content).UserQuery)).UnsafeDeleteMList(); Database.Query <UserQueryPartEntity>().Where(uqp => query.Contains(uqp.UserQuery)).UnsafeDelete(); return(null); }; sb.Schema.Table <UserQueryEntity>().PreDeleteSqlSync += arg => { var uq = (UserQueryEntity)arg; var parts = Administrator.UnsafeDeletePreCommandMList((DashboardEntity cp) => cp.Parts, Database.MListQuery((DashboardEntity cp) => cp.Parts) .Where(mle => ((UserQueryPartEntity)mle.Element.Content).UserQuery == uq)); var parts2 = Administrator.UnsafeDeletePreCommand(Database.Query <UserQueryPartEntity>() .Where(mle => mle.UserQuery == uq)); return(SqlPreCommand.Combine(Spacing.Simple, parts, parts2)); }; } if (sb.Settings.ImplementedBy((DashboardEntity cp) => cp.Parts.First().Content, typeof(UserChartPartEntity))) { sb.Schema.EntityEvents <UserChartEntity>().PreUnsafeDelete += query => { Database.MListQuery((DashboardEntity cp) => cp.Parts).Where(mle => query.Contains(((UserChartPartEntity)mle.Element.Content).UserChart)).UnsafeDeleteMList(); Database.Query <UserChartPartEntity>().Where(uqp => query.Contains(uqp.UserChart)).UnsafeDelete(); Database.MListQuery((DashboardEntity cp) => cp.Parts).Where(mle => ((CombinedUserChartPartEntity)mle.Element.Content).UserCharts.Any(uc => query.Contains(uc))).UnsafeDeleteMList(); Database.Query <CombinedUserChartPartEntity>().Where(cuqp => cuqp.UserCharts.Any(uc => query.Contains(uc))).UnsafeDelete(); return(null); }; sb.Schema.Table <UserChartEntity>().PreDeleteSqlSync += arg => { var uc = (UserChartEntity)arg; var mlistElems = Administrator.UnsafeDeletePreCommandMList((DashboardEntity cp) => cp.Parts, Database.MListQuery((DashboardEntity cp) => cp.Parts) .Where(mle => ((UserChartPartEntity)mle.Element.Content).UserChart == uc)); var parts = Administrator.UnsafeDeletePreCommand(Database.Query <UserChartPartEntity>() .Where(mle => mle.UserChart == uc)); var mlistElems2 = Administrator.UnsafeDeletePreCommandMList((DashboardEntity cp) => cp.Parts, Database.MListQuery((DashboardEntity cp) => cp.Parts) .Where(mle => ((CombinedUserChartPartEntity)mle.Element.Content).UserCharts.Contains(uc))); var parts2 = Administrator.UnsafeDeletePreCommand(Database.Query <CombinedUserChartPartEntity>() .Where(mle => mle.UserCharts.Contains(uc))); return(SqlPreCommand.Combine(Spacing.Simple, mlistElems, parts, mlistElems2, parts2)); }; } DashboardGraph.Register(); Dashboards = sb.GlobalLazy(() => Database.Query <DashboardEntity>().ToDictionary(a => a.ToLite()), new InvalidateWith(typeof(DashboardEntity))); DashboardsByType = sb.GlobalLazy(() => Dashboards.Value.Values.Where(a => a.EntityType != null) .SelectCatch(d => KeyValuePair.Create(TypeLogic.IdToType.GetOrThrow(d.EntityType !.Id), d.ToLite())) .GroupToDictionary(), new InvalidateWith(typeof(DashboardEntity))); } }