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