Ejemplo n.º 1
0
        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));
            }
        }
Ejemplo n.º 2
0
        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)));
            }
        }
Ejemplo n.º 3
0
        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));
        }
Ejemplo n.º 4
0
        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)));
            }
        }