/// <summary>
/// 获取功能分页信息
/// </summary>
/// <param name="pageSize">页大小</param>
/// <param name="currentPage">当前页</param>
/// <param name="functionName">功能名称</param>
/// <param name="controllerID">功能对应的动作所属控制器编号</param>
/// <param name="areaID">功能对应的动作所属控制器所属区域编号</param>
/// <param name="parentFunctionID">上级功能编号</param>
/// <returns></returns>
public static AsiatekPagedList <FunctionListModel> GetPagedFunctions(FunctionSettingModel model, int pageSize)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "VW_GetFunctionsWithLevelID f"),
new SqlParameter("@joinStr", @"LEFT JOIN Actions act ON f.ActionID=act.ID
LEFT JOIN Controllers c ON act.ControllerID=c.ID
LEFT JOIN Areas a ON c.AreaID=a.ID
LEFT JOIN Functions f2 ON f.ParentID=f2.ID"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", model.SearchPage),
new SqlParameter("@orderBy", "f.LevelID"),
new SqlParameter("@showColumns", @"f.ID ,f.IsBackground,f.LevelID,
f.FunctionName ,
f2.FunctionName AS ParentFunctionName ,
act.ActionName ,
c.ControllerName ,
a.AreaName"),
};
string conditionStr = "f.FunctionName LIKE '%" + model.FunctionName + "%'";
if (model.IsMenu)//搜索菜单，搜索菜单的情况下，只额外考虑功能名、顶级功能、上级功能
{
conditionStr += " AND f.ActionID IS NULL";
if (model.IsTopFunction)//顶级功能
{
conditionStr += " AND f.ParentID IS NULL";
}
else if (model.ParentFunctionID != -1)//非顶级功能并且选了具体的上级功能
{
conditionStr += " AND f.ParentID='" + model.ParentFunctionID + "'";
}
}
else//非菜单，正常与Action绑定的功能
{
if (model.IsTopFunction)
{
conditionStr += " AND f.ParentID IS NULL";
}
//1.选择了所属区域、未选所属控制器与具体上级功能
if (model.AreaID != -1 && model.ControllerID == -1 && model.ParentFunctionID == -1)
{
conditionStr += " AND c.AreaID='" + model.AreaID + "'";
}
//2.选择了具体的控制器，但是没选具体上级功能
else if (model.ControllerID != -1 && model.ParentFunctionID == -1)
{
conditionStr += " AND act.ControllerID='" + model.ControllerID + "'";
}
//3.非顶级功能且选择了具体的上级功能
else if (model.ParentFunctionID != -1 && !model.IsTopFunction)
{
conditionStr += " AND f.ParentID='" + model.ParentFunctionID + "'";
}
}
if (model.IsAppFeatures)
{
conditionStr += " AND f.IsAppFeatures= 1";
}
//if (model.IsMenu)
//{
// if (model.ParentFunctionID == -1)//没选上级功能
// {
// paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%' AND f.ActionID IS NULL"));
// }
// else
// {
// paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%' AND f.ActionID IS NULL AND f.ParentID='" + model.ParentFunctionID + "'"));
// }
//}
//else if (model.AreaID != -1 && model.ControllerID == -1 && model.ParentFunctionID == -1)//选择了区域，没选控制器与上级功能
//{
// paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%' AND c.AreaID='" + model.AreaID + "'"));
//}
//else if (model.ControllerID != -1 && model.ParentFunctionID == -1)//选择了具体的控制器，但是没选上级功能
//{
// paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%' AND act.ControllerID='" + model.ControllerID + "'"));
//}
//else if (model.ParentFunctionID != -1)//选择了上级功能
//{
// paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%' AND f.ParentID='" + model.ParentFunctionID + "'"));
//}
//else//什么都没选
//{
// paras.Add(new SqlParameter("@conditionStr", "f.FunctionName LIKE '%" + model.FunctionName + "%'"));
//}
paras.Add(new SqlParameter("@conditionStr", conditionStr));
paras.Add(new SqlParameter()
{
ParameterName = "@totalItemCount",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
paras.Add(new SqlParameter()
{
ParameterName = "@newCurrentPage",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
List <FunctionListModel> list = ConvertToList <FunctionListModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray()));
int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value);
int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value);
return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount));
}
public ActionResult RiskEarlyWarnToExcel(RiskEarlyWarnSeachModel model)
{
//model.PagedDatas = ReportBLL.GetPagedSpeed(model, 1, Int32.MaxValue, base.CurrentUserID);
DataTable dt = new DataTable();
dt.Columns.Add("StrucName", typeof(string));
dt.Columns.Add("VehicleName", typeof(string));
dt.Columns.Add("GPSStartTime", typeof(string));
dt.Columns.Add("GPSEndTime", typeof(string));
dt.Columns.Add("Time", typeof(string));
dt.Columns.Add("DealUser", typeof(string));
dt.Columns.Add("DealTime", typeof(string));
dt.Columns.Add("DealInfo", typeof(string));
DataRow dr = dt.NewRow();
dr["StrucName"] = "南京亚士德";
dr["VehicleName"] = "京TEX008";
dr["GPSStartTime"] = "2016-10-10 15:04:20";
dr["GPSEndTime"] = "2016-10-10 15:05:32";
dr["Time"] = "1分12秒";
dr["DealUser"] = "******";
dr["DealTime"] = "2016-10-12 10:15:32";
dr["DealInfo"] = "超速";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["StrucName"] = "南京亚士德";
dr1["VehicleName"] = "亚A00001";
dr1["GPSStartTime"] = "2016-10-10 17:06:52";
dr1["GPSEndTime"] = "2016-10-10 17:08:01";
dr1["Time"] = "1分09秒";
dr1["DealUser"] = "******";
dr1["DealTime"] = "2016-10-12 10:15:32";
dr1["DealInfo"] = "滚筒停转";
dt.Rows.Add(dr1);
DataRow dr2 = dt.NewRow();
dr2["StrucName"] = "南京亚士德";
dr2["VehicleName"] = "京TEX201";
dr2["GPSStartTime"] = "2016-10-10 17:06:52";
dr2["GPSEndTime"] = "2016-10-10 17:08:01";
dr2["Time"] = "1分09秒";
dr2["DealUser"] = "******";
dr2["DealTime"] = "2016-10-12 10:15:32";
dr2["DealInfo"] = "超速";
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["StrucName"] = "南京亚士德";
dr3["VehicleName"] = "汇A00002";
dr3["GPSStartTime"] = "2016-10-10 15:04:20";
dr3["GPSEndTime"] = "2016-10-10 15:05:32";
dr3["Time"] = "1分12秒";
dr3["DealUser"] = "******";
dr3["DealTime"] = "2016-10-12 10:15:32";
dr3["DealInfo"] = "超速";
dt.Rows.Add(dr3);
List <RiskEarlyWarnListModel> list = ConvertToList <RiskEarlyWarnListModel> .Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
ViewBag.ExportFlag = true;
ViewData.Model = model;
string viewHtml = RenderPartialViewToString(this, "_RiskEarlyWarnGrid");
var reportName = this.GetUIText(base.GetFunctionsInfo("ReportManage", "RiskEarlyWarn", "SeachRiskEarlyWarn").FunctionName); //功能名称
return(File(System.Text.Encoding.UTF8.GetBytes(viewHtml), "application/ms-excel", string.Format("{0}.xls", reportName + "(" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ")")));
}
/// <summary>
/// 获取区域信息下拉列表数据
/// 包含区域编号、区域名称
/// </summary>
/// <returns></returns>
public static List <AreaDDLModel> GetAreas()
{
string sql = "SELECT ID,AreaName FROM Areas";
return(ConvertToList <AreaDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
//3、通过车机信息正常计算里程
#region 链接服务器写在配置文件中
// public static async Task<ReturnData> GetMilieageByHistoryAsync(CLCS_MileageModel model)
// {
// //根据车牌号和开始、结束时间，分别去历史轨迹表中查询对应的数据，计算车机里程
// var linkedServer = WebConfigurationManager.AppSettings["linkedServer"];
// #region 根据车牌号查询历史轨迹数据
// //判断查询月表，分开查询时间点里程数据然后计算
// string st = model.StartTime.ToString("yyyyMM");
// string et = model.EndTime.ToString("yyyyMM");
// //考虑到数据库月表数据量比较大，这边直接查询出所有满足条件的数据，然后再计算里程数据
// //查询同一张月表中的数据，只要关联一张月表就可以了
// string sql = "";
// if (st == et)
// {
// sql = string.Format(@" SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName,
//(SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage ,gn.SignalDateTime
// FROM dbo.Vehicles ve
// INNER JOIN {0}.GNSS.dbo.[{1}] gn ON gn.VIN = ve.VIN
// INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID
// WHERE ve.PlateNum='{2}' AND gn.SignalDateTime>='{3}' AND gn.SignalDateTime<='{4}'
// ORDER BY gn.SignalDateTime ", linkedServer, st, model.PlateNum, model.StartTime, model.EndTime);
// }
// else {
// sql = string.Format(@" SELECT * FROM (
//SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName,
//(SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage ,gn.SignalDateTime
// FROM dbo.Vehicles ve
// INNER JOIN {0}.GNSS.dbo.[{1}] gn ON gn.VIN = ve.VIN
// INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID
// WHERE ve.PlateNum='{2}' AND gn.SignalDateTime>='{3}'
// UNION ALL
//SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName,
//(SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage,gn.SignalDateTime
// FROM dbo.Vehicles ve
// INNER JOIN {0}.GNSS.dbo.[{4}] gn ON gn.VIN = ve.VIN
// INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID
// WHERE ve.PlateNum='{2}' AND gn.SignalDateTime<='{5}' ) AS rs
//ORDER BY rs.SignalDateTime", linkedServer, st, model.PlateNum, model.StartTime, et, model.EndTime);
// }
// var dt = await MSDBHelper.ExecuteDataTableAsync(sql, System.Data.CommandType.Text).ConfigureAwait(false);
// var list = ConvertToList<SearchResultData>.Convert(dt);
// if (list == null)
// {
// return null;
// }
// if (list.Count == 0)
// {
// return null;
// }
// var rs = new ReturnData();
// var count = list.Count;
// rs.VehicleCode = list[0].VehicleCode;
// rs.VehicleName = list[0].VehicleName;
// rs.StrucName = list[0].StrucName;
// rs.PStrucName = list[0].PStrucName;
// rs.BeginTime = model.StartTime;
// rs.EndTime = model.EndTime;
// rs.BeginMilieage = list[0].Mileage;
// rs.EndMilieage = list[count - 1].Mileage;
// rs.ReturnMileage = rs.EndMilieage - rs.BeginMilieage;
// return rs;
// #endregion
// }
#endregion
#region 读取终端表中的链接服务器
public static async Task <ReturnData> GetMilieageByHistoryAsync(CLCS_MileageModel model)
{
//根据车牌号和开始、结束时间，分别去历史轨迹表中查询对应的数据，计算车机里程
List <ServerInfoModel> slist = GetServerInfoByPlateNum(model.PlateNum);
if (slist == null || slist.Count == 0)
{
return(null);
}
var linkedServer = slist[0].LinkedServerName;
#region 根据车牌号查询历史轨迹数据
//判断查询月表，分开查询时间点里程数据然后计算
string st = model.StartTime.ToString("yyyyMM");
string et = model.EndTime.ToString("yyyyMM");
//考虑到数据库月表数据量比较大，这边直接查询出所有满足条件的数据，然后再计算里程数据
//查询同一张月表中的数据，只要关联一张月表就可以了
string sql = "";
if (st == et)
{
sql = string.Format(@" SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName,
(SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage ,gn.SignalDateTime
FROM dbo.Vehicles ve
INNER JOIN {0}.GNSS.dbo.[{1}] gn ON gn.VIN = ve.VIN
INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID
WHERE ve.PlateNum='{2}' AND gn.SignalDateTime>='{3}' AND gn.SignalDateTime<='{4}'
ORDER BY gn.SignalDateTime ", linkedServer, st, model.PlateNum, model.StartTime, model.EndTime);
}
else
{
sql = string.Format(@" SELECT * FROM (
SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName,
(SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage ,gn.SignalDateTime
FROM dbo.Vehicles ve
INNER JOIN {0}.GNSS.dbo.[{1}] gn ON gn.VIN = ve.VIN
INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID
WHERE ve.PlateNum='{2}' AND gn.SignalDateTime>='{3}'
UNION ALL
SELECT ve.[PlateNum] AS VehicleCode,ve.VehicleName,sc.StrucName,
(SELECT StrucName FROM dbo.Structures WHERE ID=sc.ParentID) AS PStrucName,gn.Mileage,gn.SignalDateTime
FROM dbo.Vehicles ve
INNER JOIN {0}.GNSS.dbo.[{4}] gn ON gn.VIN = ve.VIN
INNER JOIN dbo.Structures sc ON sc.ID = ve.StrucID
WHERE ve.PlateNum='{2}' AND gn.SignalDateTime<='{5}' ) AS rs
ORDER BY rs.SignalDateTime", linkedServer, st, model.PlateNum, model.StartTime, et, model.EndTime);
}
var dt = await MSDBHelper.ExecuteDataTableAsync(sql, System.Data.CommandType.Text).ConfigureAwait(false);
var list = ConvertToList <SearchResultData> .Convert(dt);
if (list == null)
{
return(null);
}
if (list.Count == 0)
{
return(null);
}
var rs = new ReturnData();
var count = list.Count;
rs.VehicleCode = list[0].VehicleCode;
rs.VehicleName = list[0].VehicleName;
rs.StrucName = list[0].StrucName;
rs.PStrucName = list[0].PStrucName;
rs.BeginTime = model.StartTime;
rs.EndTime = model.EndTime;
rs.BeginMilieage = list[0].Mileage;
rs.EndMilieage = list[count - 1].Mileage;
rs.ReturnMileage = rs.EndMilieage - rs.BeginMilieage;
return(rs);
#endregion
}
public ActionResult AbnormalRouteToExcel(AbnormalRouteSearchModel model)
{
//model.PagedDatas = ReportBLL.GetPagedSpeed(model, 1, Int32.MaxValue, base.CurrentUserID);
DataTable dt = new DataTable();
dt.Columns.Add("StrucName", typeof(string));
dt.Columns.Add("VehicleName", typeof(string));
dt.Columns.Add("PathName", typeof(string));
dt.Columns.Add("ExceptionTypeName", typeof(string));
dt.Columns.Add("GPSStartTime", typeof(string));
dt.Columns.Add("GPSEndTime", typeof(string));
dt.Columns.Add("Time", typeof(string));
DataRow dr = dt.NewRow();
dr["StrucName"] = "南京亚士德";
dr["VehicleName"] = "京TEX008";
dr["PathName"] = "软件大道-将军山";
dr["ExceptionTypeName"] = "路段超速";
dr["GPSStartTime"] = "2016-10-10 15:04:20";
dr["GPSEndTime"] = "2016-10-10 15:05:32";
dr["Time"] = "1分12秒";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["StrucName"] = "南京亚士德";
dr1["VehicleName"] = "亚A00001";
dr1["PathName"] = "鸡鸣寺-中华门";
dr1["ExceptionTypeName"] = "进路线";
dr1["GPSStartTime"] = "2016-10-10 17:06:52";
dr1["GPSEndTime"] = "2016-10-10 17:08:01";
dr1["Time"] = "1分09秒";
dt.Rows.Add(dr1);
DataRow dr2 = dt.NewRow();
dr2["StrucName"] = "南京亚士德";
dr2["VehicleName"] = "京TEX201";
dr2["PathName"] = "软件大道-将军山";
dr2["ExceptionTypeName"] = "出路线";
dr2["GPSStartTime"] = "2016-10-10 17:06:52";
dr2["GPSEndTime"] = "2016-10-10 17:08:01";
dr2["Time"] = "1分09秒";
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["StrucName"] = "南京亚士德";
dr3["VehicleName"] = "汇A00002";
dr3["PathName"] = "鸡鸣寺-中华门";
dr3["ExceptionTypeName"] = "出路线";
dr3["GPSStartTime"] = "2016-10-10 15:04:20";
dr3["GPSEndTime"] = "2016-10-10 15:05:32";
dr3["Time"] = "1分12秒";
dt.Rows.Add(dr3);
List <AbnormalRouteListModel> list = ConvertToList <AbnormalRouteListModel> .Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
ViewBag.ExportFlag = true;
ViewData.Model = model;
string viewHtml = RenderPartialViewToString(this, "_AbnormalRouteGrid");
var reportName = this.GetUIText(base.GetFunctionsInfo("ReportManage", "AbnormalRoute", "SeachAbnormalRoute").FunctionName); //功能名称
return(File(System.Text.Encoding.UTF8.GetBytes(viewHtml), "application/ms-excel", string.Format("{0}.xls", reportName + "(" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ")")));
}
public ActionResult GetOverSpeedInfo(ReportSearchModels model)
{
#region 老版本
// SearchDataWithPagedDatas<ReportSearchModels, ReportListModel> result =
//new SearchDataWithPagedDatas<ReportSearchModels, ReportListModel>();
// result.SearchModel = model;
// result.PagedDatas = ReportBLL.GetPagedSpeed(model, searchPage, this.PageSize);
// return PartialView("_SpeedReportDetailGrid", result);
#endregion
//model.GPSStartTime = "2016-10-10 00:00:00";
//model.GPSEndTime = "2016-10-10 23:59:59";
//model.PagedDatas = ReportBLL.GetPagedSpeed(model, model.SearchPage, this.PageSize, base.CurrentUserID);
DataTable dt = new DataTable();
dt.Columns.Add("StrucName", typeof(string));
dt.Columns.Add("VehicleName", typeof(string));
dt.Columns.Add("GPSStartTime", typeof(string));
dt.Columns.Add("GPSEndTime", typeof(string));
dt.Columns.Add("Time", typeof(string));
DataRow dr = dt.NewRow();
dr["StrucName"] = "南京亚士德";
dr["VehicleName"] = "京TEX008";
dr["GPSStartTime"] = "2016-10-10 15:04:20";
dr["GPSEndTime"] = "2016-10-10 15:05:32";
dr["Time"] = "1分12秒";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["StrucName"] = "南京亚士德";
dr1["VehicleName"] = "亚A00001";
dr1["GPSStartTime"] = "2016-10-10 17:06:52";
dr1["GPSEndTime"] = "2016-10-10 17:08:01";
dr1["Time"] = "1分09秒";
dt.Rows.Add(dr1);
DataRow dr2 = dt.NewRow();
dr2["StrucName"] = "南京亚士德";
dr2["VehicleName"] = "京TEX201";
dr2["GPSStartTime"] = "2016-10-10 17:06:52";
dr2["GPSEndTime"] = "2016-10-10 17:08:01";
dr2["Time"] = "1分09秒";
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["StrucName"] = "南京亚士德";
dr3["VehicleName"] = "京TEX008";
dr3["GPSStartTime"] = "2016-10-10 15:04:20";
dr3["GPSEndTime"] = "2016-10-10 15:05:32";
dr3["Time"] = "1分12秒";
dt.Rows.Add(dr3);
DataRow dr4 = dt.NewRow();
dr4["StrucName"] = "南京亚士德";
dr4["VehicleName"] = "亚A00001";
dr4["GPSStartTime"] = "2016-10-10 17:06:52";
dr4["GPSEndTime"] = "2016-10-10 17:08:01";
dr4["Time"] = "1分09秒";
dt.Rows.Add(dr4);
DataRow dr5 = dt.NewRow();
dr5["StrucName"] = "南京亚士德";
dr5["VehicleName"] = "京TEX201";
dr5["GPSStartTime"] = "2016-10-10 17:06:52";
dr5["GPSEndTime"] = "2016-10-10 17:08:01";
dr5["Time"] = "1分09秒";
dt.Rows.Add(dr5);
DataRow dr6 = dt.NewRow();
dr6["StrucName"] = "南京亚士德";
dr6["VehicleName"] = "京TEX008";
dr6["GPSStartTime"] = "2016-10-10 15:04:20";
dr6["GPSEndTime"] = "2016-10-10 15:05:32";
dr6["Time"] = "1分12秒";
dt.Rows.Add(dr6);
//DataRow dr7 = dt.NewRow();
//dr7["StrucName"] = "南京亚士德";
//dr7["VehicleName"] = "亚A00001";
//dr7["GPSStartTime"] = "2016-10-10 17:06:52";
//dr7["GPSEndTime"] = "2016-10-10 17:08:01";
//dr7["Time"] = "1分09秒";
//dt.Rows.Add(dr7);
//DataRow dr8 = dt.NewRow();
//dr8["StrucName"] = "南京亚士德";
//dr8["VehicleName"] = "京TEX201";
//dr8["GPSStartTime"] = "2016-10-10 17:06:52";
//dr8["GPSEndTime"] = "2016-10-10 17:08:01";
//dr8["Time"] = "1分09秒";
//dt.Rows.Add(dr8);
//DataRow dr9 = dt.NewRow();
//dr9["StrucName"] = "南京亚士德";
//dr9["VehicleName"] = "京TEX008";
//dr9["GPSStartTime"] = "2016-10-10 15:04:20";
//dr9["GPSEndTime"] = "2016-10-10 15:05:32";
//dr9["Time"] = "1分12秒";
//dt.Rows.Add(dr9);
//DataRow dr10 = dt.NewRow();
//dr10["StrucName"] = "南京亚士德";
//dr10["VehicleName"] = "亚A00001";
//dr10["GPSStartTime"] = "2016-10-10 17:06:52";
//dr10["GPSEndTime"] = "2016-10-10 17:08:01";
//dr10["Time"] = "1分09秒";
//dt.Rows.Add(dr10);
//DataRow dr11 = dt.NewRow();
//dr11["StrucName"] = "南京亚士德";
//dr11["VehicleName"] = "京TEX201";
//dr11["GPSStartTime"] = "2016-10-10 17:06:52";
//dr11["GPSEndTime"] = "2016-10-10 17:08:01";
//dr11["Time"] = "1分09秒";
//dt.Rows.Add(dr11);
List <ReportListModel> list = ConvertToList <ReportListModel> .Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
return(PartialView("_SpeedReportDetailGrid", model));
}
public static AsiatekPagedList <ElectricFencePropertyListModel> GetPagedEFPropertys(ElectricFencePropertySearchModel model, int searchPage, int pageSize, int strucID)
{
#region 是否属于单位亚士德，是则查所有，否则查询前用户所属单位的非亚士德上级以及所有其子单位
string isSql = @" SELECT ParentID FROM dbo.Structures WHERE ID =@StrucID ";
List <SqlParameter> isParas = new List <SqlParameter>()
{
new SqlParameter("@StrucID", SqlDbType.Int),
};
isParas[0].Value = strucID;
var isResult = MSSQLHelper.ExecuteScalar(CommandType.Text, isSql, isParas.ToArray());
#endregion
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "dbo.ElectricFenceProperty efp "),
new SqlParameter("@joinStr", @" INNER JOIN dbo.Users AS us ON us.ID = efp.CreateUser "),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "efp.CreateTime DESC"),
new SqlParameter("@showColumns", @"efp.ID,efp.PropertyName,efp.FenceState,efp.ValidStartTime,efp.ValidEndTime,
efp.AlarmType,efp.IsSpeed,efp.MaxSpeed,efp.IsPeriod "),
};
string conditionStr = " ";
if (isResult != DBNull.Value)
{
conditionStr = "efp.Status<>9 AND us.StrucID IN (SELECT ID FROM Func_GetAllSubStructsAndParentStructsByStrucID(" + strucID + ") WHERE ParentID IS NOT NULL) ";
}
else
{
conditionStr = "efp.Status<>9 ";
}
if (!string.IsNullOrWhiteSpace(model.PropertyName))
{
conditionStr += " AND efp.PropertyName LIKE '%" + model.PropertyName + "%'";
}
if (!string.IsNullOrWhiteSpace(conditionStr))
{
paras.Add(new SqlParameter("@conditionStr", conditionStr));
}
paras.Add(new SqlParameter()
{
ParameterName = "@totalItemCount",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
paras.Add(new SqlParameter()
{
ParameterName = "@newCurrentPage",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
var rs = MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray());
List <ElectricFencePropertyListModel> list = ConvertToList <ElectricFencePropertyListModel> .Convert(rs);
int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value);
int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value);
return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount));
}
/// <summary>
/// 获取驾照状态信息
/// </summary>
/// <returns></returns>
public static List <DriveLicenseStateSelectModel> GetDriveLicenseStates()
{
string sql = "SELECT * FROM dbo.DriveLicenseState WHERE Status=0";
return(ConvertToList <DriveLicenseStateSelectModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
public static AsiatekPagedList <UserListModel> GetPagedUserInfo(UserSearchModel model, int searchPage, int pageSize, bool isSuperAdmin)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "Users u"),
new SqlParameter("@joinStr", "INNER JOIN Structures s ON u.StrucID=s.ID"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "u.ID"),
new SqlParameter("@showColumns", "u.ID,u.UserName,u.NickName,s.StrucName AS SubordinateStrucName,u.VehicleViewMode"),
};
string conditionStr = "u.Status<>9";
if (!isSuperAdmin)//不是超级管理员只查询角色等级大于等于3的用户
{
conditionStr += " AND u.RoleID<>1 AND u.RoleID<>2";
}
bool b1 = !string.IsNullOrWhiteSpace(model.UserName);
bool b2 = !string.IsNullOrWhiteSpace(model.NickName);
if (b1 && b2)//条件同时包含用户名和昵称
{
conditionStr += " AND (u.UserName LIKE '%" + model.UserName + "%' OR u.NickName LIKE '%" + model.NickName + "%')";
}
else if (b1)//只有用户名
{
conditionStr += " AND u.UserName LIKE '%" + model.UserName + "%'";
}
else if (b2)//只有昵称
{
conditionStr += " AND u.NickName LIKE '%" + model.NickName + "%'";
}
if (model.StrucID != -1)//如果选择了具体的隶属单位
{
conditionStr += " AND u.StrucID=" + model.StrucID + "";
}
if (model.VehicleViewMode != -1)
{
conditionStr += " AND u.VehicleViewMode=" + model.VehicleViewMode + "";
}
paras.Add(new SqlParameter("@conditionStr", conditionStr));
paras.Add(new SqlParameter()
{
ParameterName = "@totalItemCount",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
paras.Add(new SqlParameter()
{
ParameterName = "@newCurrentPage",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
List <UserListModel> list = ConvertToList <UserListModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray()));
int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value);
int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value);
return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount));
}
/// <summary>
/// 获取员工信息分页数据
/// </summary>
/// <param name="model"></param>
/// <param name="searchPage"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static AsiatekPagedList <EmployeeInfoListModel> GetPagedEmployeeInfos(EmployeeInfoSearchModel model, int searchPage, int pageSize)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "EmployeeInfo E"),
new SqlParameter("@joinStr", @" LEFT JOIN (SELECT * FROM dbo.DriveType WHERE Status=0) DT
ON E.DriveTypeID=DT.DriveTypeID
LEFT JOIN (SELECT * FROM dbo.DriveLicenseState WHERE Status=0) DLS
ON E.DriveLicenseStateID=DLS.DriveLicenseStateID "),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "E.ID DESC"),
new SqlParameter("@showColumns", @"E.ID,
E.EmployeeID,
E.EmployeeName,
E.EmployeeGender,
E.CertificateCode,
E.ContactPhone,
E.EmergePhone,
E.IsDriver,
E.IsCarrier,
DT.DriveTypeID,
DT.DriveTypeName,
DLS.DriveLicenseStateID,
DLS.DriveLicenseStateName "),
};
#region 筛选条件
string conditionStr = "E.Status=0";
if (!string.IsNullOrWhiteSpace(model.EmployeeID))
{
conditionStr += " AND E.EmployeeID LIKE '%" + model.EmployeeID + "%'";
}
if (!string.IsNullOrWhiteSpace(model.EmployeeName))
{
conditionStr += " AND E.EmployeeName LIKE '%" + model.EmployeeName + "%'";
}
if (model.DriveLicenseStateID != -1)
{
conditionStr += " AND DLS.DriveLicenseStateID =" + model.DriveLicenseStateID + "";
}
if (!string.IsNullOrWhiteSpace(conditionStr))
{
paras.Add(new SqlParameter("@conditionStr", conditionStr));
}
#endregion
paras.Add(new SqlParameter()
{
ParameterName = "@totalItemCount",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
paras.Add(new SqlParameter()
{
ParameterName = "@newCurrentPage",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
List <EmployeeInfoListModel> list = ConvertToList <EmployeeInfoListModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray()));
int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value);
int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value);
return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount));
}
/// <summary>
/// 获取证件类型信息
/// </summary>
/// <returns></returns>
public static List <CertificateTypeSelectModel> GetCertificateTypes()
{
string sql = "SELECT * FROM dbo.CertificateType WHERE Status=0";
return(ConvertToList <CertificateTypeSelectModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
/// <summary>
/// 获取员工信息分页数据
/// </summary>
/// <param name="model"></param>
/// <param name="searchPage"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static AsiatekPagedList <EmployeeInfoPageModel> GetPagedEmployeeInfo_New(EmployeeInfoFindModel model, int searchPage, int pageSize, int strucID)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "EmployeeInfo AS E"),
new SqlParameter("@joinStr", @" INNER JOIN Func_GetStrucAndSubStrucByUserAffiliatedStrucID(" + strucID + ") AS S ON E.StrucID = S.ID"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "E.ID DESC"),
new SqlParameter("@showColumns", @"E.ID,E.EmployeeName,E.EmployeeGender,E.IsDriver,E.IsCarrier,
E.CertificateCode,E.ContactPhone,E.EmergePhone,S.StrucName"),
};
#region 筛选条件
string conditionStr = "E.Status=0";
if (!string.IsNullOrWhiteSpace(model.EmployeeName))
{
conditionStr += " AND E.EmployeeName LIKE '%" + model.EmployeeName + "%'";
}
if (!string.IsNullOrWhiteSpace(model.CertificateCode))
{
conditionStr += " AND E.CertificateCode LIKE '%" + model.CertificateCode + "%'";
}
if (!string.IsNullOrWhiteSpace(model.ContactPhone))
{
conditionStr += " AND E.ContactPhone LIKE '%" + model.ContactPhone + "%'";
}
if (model.IsCarriers != -1)
{
conditionStr += " AND E.IsCarrier LIKE '%" + model.IsCarriers + "%'";
}
if (model.IsDrivers != -1)
{
conditionStr += " AND E.IsDriver LIKE '%" + model.IsDrivers + "%'";
}
if (model.SearchStrucID.HasValue)
{
conditionStr += " AND E.StrucID = " + model.SearchStrucID + "";
}
paras.Add(new SqlParameter("@conditionStr", conditionStr));
#endregion
paras.Add(new SqlParameter()
{
ParameterName = "@totalItemCount",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
paras.Add(new SqlParameter()
{
ParameterName = "@newCurrentPage",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
List <EmployeeInfoPageModel> list = ConvertToList <EmployeeInfoPageModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray()));
int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value);
int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value);
return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount));
}
public static List <MaintenanceCheckAlarmModel> GetDefaultMaintenanceCheckAlarmModel(int strucID)
{
//alarmMile=设置里程数-（当前里程数-初始里程数）%设定里程数
//alarmTime=设置天数-（初试时间和信号时间的间隔天数）%设置天数
//alarmMile<=提前提醒里程数，alarmTime<=提前提醒天数，车辆保养预警提醒
//因为sg.Mileage、vm.FirstMaintenanceMile是浮点数，ms.SettingMile是整数，数据类型 float 和 int 在 modulo 运算符中不兼容，两个float也无法运算，所以将前者转化成int型，四舍五入，会出现一点偏差
string sql = string.Format(@" SELECT vm.VehicleID,vm.ScheduleID,vm.FirstMaintenanceMile,vm.FirstMaintenanceTime,
ms.ScheduleName,ms.RulesType,ms.SettingMile,ms.PreMile,ms.SettingDay,ms.PreDay,userVehicles.VehicleName,userVehicles.VIN,userVehicles.StrucName,
CASE ms.RulesType
WHEN 1 THEN ms.SettingMile-CAST((sg.Mileage-vm.FirstMaintenanceMile) AS INT)%ms.SettingMile
WHEN 3 THEN ms.SettingMile-CAST((sg.Mileage-vm.FirstMaintenanceMile) AS INT)%ms.SettingMile
END AS alarmMile,
CASE ms.RulesType
WHEN 2 THEN ms.SettingDay-(SELECT DATEDIFF(DAY,vm.FirstMaintenanceTime,sg.SignalDateTime))%ms.SettingDay
WHEN 3 THEN ms.SettingDay-(SELECT DATEDIFF(DAY,vm.FirstMaintenanceTime,sg.SignalDateTime))%ms.SettingDay
END AS alarmTime
FROM dbo.VehicleMaintenance vm
INNER JOIN dbo.MaintenanceSchedule ms ON ms.ID=vm.ScheduleID
INNER JOIN
(
SELECT vt.VID AS ID,vt.VehicleName,vt.VIN,s.StrucName FROM dbo.Structures s
INNER JOIN Func_GetAllTheSubsetOfVehiclesByStrucID({0}) AS vt ON s.ID=vt.StrucID
) AS userVehicles ON vm.VehicleID=userVehicles.ID
INNER JOIN [dbo].[VW_GetRealTimeSignals] sg ON sg.VIN = userVehicles.VIN ", strucID);
List <MaintenanceCheckAlarmModel> list = ConvertToList <MaintenanceCheckAlarmModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql));
List <MaintenanceCheckAlarmModel> result = new List <MaintenanceCheckAlarmModel>();
if (list == null || list.Count <= 0)
{
return(null);
}
else
{ //如果既设定了里程周期又设定了时间周期，只要有一个有预警就进行提醒，两者都有预警就都提醒
foreach (var model in list)
{
//里程预警提醒的
if (model.RulesType == 1 && model.alarmMile != null && model.alarmMile <= model.PreMile)
{
model.AlarmInfo = @DisplayText.Mileage2 + @UIText.MaintenanceCheckAlarm;
result.Add(model);
}
//时间预警提醒的
if (model.RulesType == 2 && model.alarmTime != null && model.alarmTime <= model.PreDay)
{
model.AlarmInfo = @DisplayText.Time + @UIText.MaintenanceCheckAlarm;
result.Add(model);
}
//里程或时间预警提醒的
if (model.RulesType == 3)
{
//两者都有
if (model.alarmMile != null && model.alarmTime != null && model.alarmMile <= model.PreMile && model.alarmTime <= model.PreDay)
{
model.AlarmInfo = @DisplayText.Mileage2 + @DisplayText.And + @DisplayText.Time + @UIText.MaintenanceCheckAlarm;
result.Add(model);
}
//只有里程预警
else if (model.alarmMile != null && model.alarmMile <= model.PreMile)
{
model.AlarmInfo = @DisplayText.Mileage2 + @UIText.MaintenanceCheckAlarm;
result.Add(model);
}
//只有时间预警
else if (model.alarmTime != null && model.alarmTime <= model.PreDay)
{
model.AlarmInfo = @DisplayText.Time + @UIText.MaintenanceCheckAlarm;
result.Add(model);
}
}
}
}
return(result);
}
public static List <MaintenanceCheckAlarmModel> GetMaintenanceCheckAlarm(int userID)
{
string sql = string.Format(@"SELECT vm.VehicleID,vm.ScheduleID,vm.FirstMaintenanceMile,vm.FirstMaintenanceTime,
ms.ScheduleName,ms.RulesType,ms.SettingMile,ms.PreMile,ms.SettingDay,ms.PreDay,v.VehicleName,v.VIN,s.StrucName,
CASE ms.RulesType
WHEN 1 THEN ms.SettingMile-CAST((sg.Mileage-vm.FirstMaintenanceMile) AS INT)%ms.SettingMile
WHEN 3 THEN ms.SettingMile-CAST((sg.Mileage-vm.FirstMaintenanceMile) AS INT)%ms.SettingMile
END AS alarmMile,
CASE ms.RulesType
WHEN 2 THEN ms.SettingDay-(SELECT DATEDIFF(DAY,vm.FirstMaintenanceTime,sg.SignalDateTime))%ms.SettingDay
WHEN 3 THEN ms.SettingDay-(SELECT DATEDIFF(DAY,vm.FirstMaintenanceTime,sg.SignalDateTime))%ms.SettingDay
END AS alarmTime
FROM dbo.VehicleMaintenance vm
INNER JOIN dbo.MaintenanceSchedule ms ON ms.ID=vm.ScheduleID
INNER JOIN dbo.Vehicles v ON v.ID = vm.VehicleID
INNER JOIN [dbo].[VW_GetRealTimeSignals] sg ON sg.VIN = v.VIN
LEFT JOIN dbo.Structures s on v.StrucID=s.ID
WHERE v.ID in (SELECT vt.VID FROM dbo.Structures s INNER JOIN Func_GetVehiclesListByUserID_New({0}) AS vt ON s.ID=vt.StrucID)", userID);
List <MaintenanceCheckAlarmModel> list = ConvertToList <MaintenanceCheckAlarmModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql));
List <MaintenanceCheckAlarmModel> result = new List <MaintenanceCheckAlarmModel>();
if (list == null || list.Count <= 0)
{
return(null);
}
else
{ //如果既设定了里程周期又设定了时间周期，只要有一个有预警就进行提醒，两者都有预警就都提醒
foreach (var model in list)
{
//里程预警提醒的
if (model.RulesType == 1 && model.alarmMile != null && model.alarmMile <= model.PreMile)
{
model.AlarmInfo = @DisplayText.Mileage2 + @UIText.MaintenanceCheckAlarm;
result.Add(model);
}
//时间预警提醒的
if (model.RulesType == 2 && model.alarmTime != null && model.alarmTime <= model.PreDay)
{
model.AlarmInfo = @DisplayText.Time + @UIText.MaintenanceCheckAlarm;
result.Add(model);
}
//里程或时间预警提醒的
if (model.RulesType == 3)
{
//只有里程预警
if (model.alarmMile != null && model.alarmTime == null && model.alarmMile <= model.PreMile)
{
model.AlarmInfo = @DisplayText.Mileage2 + @UIText.MaintenanceCheckAlarm;
result.Add(model);
}
//只有时间预警
if (model.alarmTime != null && model.alarmMile == null && model.alarmTime <= model.PreDay)
{
model.AlarmInfo = @DisplayText.Time + @UIText.MaintenanceCheckAlarm;
result.Add(model);
}
//两者都有
if (model.alarmMile != null && model.alarmTime != null && model.alarmMile <= model.PreMile && model.alarmTime <= model.PreDay)
{
model.AlarmInfo = @DisplayText.Mileage2 + @DisplayText.And + @DisplayText.Time + @UIText.MaintenanceCheckAlarm;
result.Add(model);
}
}
}
}
return(result);
}
/// <summary>
/// 获取控制器下拉列表信息
/// 包含控制器编号、控制器名称
/// </summary>
/// <returns></returns>
public static List <ControllerDDLModel> GetControllers()
{
string sql = "SELECT ID,ControllerName FROM Controllers";
return(ConvertToList <ControllerDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
public static List <ServerInfoDDLModel> GetServerInfoList()
{
string sql = "SELECT [ID],[ServerName] FROM [dbo].[ServerInfo]";
return(ConvertToList <ServerInfoDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
/// <summary>
/// 根据用户ID获取用户分配的车辆的最新信号 自由模式
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public static List <RealTimeSignalTreeModel> GetRealTimeSignals(int userID)
{
#region 获取服务器名
List <ServerInfoModel> linkName_list = ReportBLL.GetServerInfo(userID, 0);
// string linkName_sql = @" SELECT DISTINCT sv.LinkedServerName FROM dbo.Vehicles ve
// INNER JOIN dbo.Terminals tt ON tt.LinkedVehicleID = ve.ID
// INNER JOIN dbo.ServerInfo sv ON sv.ID = tt.ServerInfoID
// WHERE ve.Status<>9 ";
// var linkName_list = ConvertToList<GetLinkedServerName>.Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, linkName_sql));
if (linkName_list == null || linkName_list.Count == 0)
{
return(null);
}
#endregion
string sql = "";
string linkedServerName = "";
//SqlParameter[] paras = new SqlParameter[2*linkName_list.Count];
for (int i = 0; i < linkName_list.Count; i++)
{
linkedServerName = linkName_list[i].LinkedServerName;
// sql += string.Format(@"SELECT outerTB.ID AS VID,outerTB.VehicleName AS VN,outerTB.StrucID AS SID,outerTB.StrucName AS SN,outerTB.VIN,
// outerTB.ParentID AS SPID,CASE WHEN DATEDIFF(MINUTE,sg.SignalDateTime,GETDATE())>10 THEN 0
// ELSE 1 END AS IsOnline,CASE WHEN sg.Speed >0 THEN 1 ELSE 0 END AS IsRunning,
// sg.Latitude AS Latitude,sg.Longitude AS Longitude FROM {0}.GNSS.dbo.Signals sg WITH(NOLOCK)
// INNER JOIN
// (
// SELECT innerTB.*,s.StrucName,s.ParentID,t.TerminalCode FROM dbo.Structures s WITH(NOLOCK)
// INNER JOIN
// (
// SELECT v.ID,v.VehicleName,v.StrucID,v.VIN FROM dbo.Vehicles v WITH(NOLOCK) INNER JOIN
// (
// SELECT StrucID FROM dbo.StructureDistributionInfo WITH(NOLOCK)
// WHERE UserID={1}) AS temp1 ON v.StrucID=temp1.StrucID
// WHERE v.Status=0 AND v.IsReceived=1
// UNION
// SELECT v.ID,v.VehicleName,v.StrucID,v.VIN FROM dbo.Vehicles v WITH(NOLOCK) INNER JOIN
// (
// SELECT VehicleID FROM dbo.VehicleDistributionInfo WITH(NOLOCK)
// WHERE UserID={1}) AS temp1 ON v.ID=temp1.VehicleID
// WHERE v.Status=0 AND v.IsReceived=1
// ) AS innerTB ON s.ID=innerTB.StrucID
// INNER JOIN dbo.Terminals t WITH(NOLOCK) ON innerTB.ID=t.LinkedVehicleID
// INNER JOIN dbo.ServerInfo sv ON sv.ID = t.ServerInfoID
// WHERE sv.LinkedServerName= '{0}'
// )AS outerTB
// ON sg.VIN=outerTB.VIN ", linkedServerName, userID);
sql += string.Format(@"SELECT outerTB.VID,outerTB.VehicleName AS VN,outerTB.StrucID AS SID,outerTB.StrucName AS SN,outerTB.VIN,
outerTB.ParentID AS SPID,CASE WHEN DATEDIFF(MINUTE,sg.SignalDateTime,GETDATE())>10 THEN 0
ELSE 1 END AS IsOnline,CASE WHEN sg.Speed >0 THEN 1 ELSE 0 END AS IsRunning,
sg.Latitude AS Latitude,sg.Longitude AS Longitude FROM {0}.GNSS.dbo.Signals sg WITH(NOLOCK)
INNER JOIN
(
SELECT innerTB.*,s.StrucName,s.ParentID,t.TerminalCode FROM dbo.Structures s WITH(NOLOCK)
INNER JOIN Func_GetVehiclesListByUserID_New({1}) AS innerTB ON s.ID=innerTB.StrucID
INNER JOIN dbo.Terminals t WITH(NOLOCK) ON innerTB.VID=t.LinkedVehicleID
INNER JOIN dbo.ServerInfo sv ON sv.ID = t.ServerInfoID
WHERE sv.LinkedServerName= '{0}'
) AS outerTB ON sg.VIN=outerTB.VIN ", linkedServerName, userID);
if (i != linkName_list.Count - 1)
{
sql += " UNION ";
}
//paras[2*i] = new SqlParameter()
//{
// ParameterName = "@userID",
// Value = userID,
// SqlDbType = SqlDbType.Int
//};
//paras[2 * i + 1] = new SqlParameter()
//{
// ParameterName = "@LinkedServerName",
// Value = linkedServerName,
// SqlDbType = SqlDbType.VarChar
//};
}
return(ConvertToList <RealTimeSignalTreeModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
public static AsiatekPagedList <TerminalTypeListModel> GetPagedTerminalTypes(TerminalTypeSearchModel model, int searchPage, int pageSize)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "TerminalTypes tt"),
new SqlParameter("@joinStr", "INNER JOIN TerminalManufacturer tm ON tt.TerminalManufacturerID = tm.ID"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "tt.ID"),
new SqlParameter("@showColumns", @"tt.ID ,tt.CommunicationMode,
tt.TerminalName ,
tt.ACCOFF_Frequency ,
tt.ACCON_Frequency ,
tt.Filter ,
tm.ManufacturerName"),
};
string conditionStr = string.Empty;
if (!string.IsNullOrWhiteSpace(model.TerminalName))
{
conditionStr += "tt.TerminalName LIKE '%" + model.TerminalName + "%'";
}
if (model.TerminalManufacturerID != -1)
{
if (string.IsNullOrWhiteSpace(conditionStr))
{
conditionStr += "tt.TerminalManufacturerID=" + model.TerminalManufacturerID + "";
}
else
{
conditionStr += " AND tt.TerminalManufacturerID=" + model.TerminalManufacturerID + "";
}
}
if (!string.IsNullOrWhiteSpace(conditionStr))
{
paras.Add(new SqlParameter("@conditionStr", conditionStr));
}
paras.Add(new SqlParameter()
{
ParameterName = "@totalItemCount",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
paras.Add(new SqlParameter()
{
ParameterName = "@newCurrentPage",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
List <TerminalTypeListModel> list = ConvertToList <TerminalTypeListModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray()));
int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value);
int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value);
return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount));
}
public static SelectResult <TemperatureAlarmRuleEditModel> GetTemperatureAlarmRuleByID(int id)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@ID", SqlDbType.Int),
};
paras[0].Value = id;
string sql = @"SELECT tar.ID,tar.Name,tar.[Description],tar.AffiliatedStrucID,struc.StrucName AS AffiliatedStrucName,
CAST(tarp.StartTime AS CHAR(8)) AS StartTime,CAST(tarp.EndTime AS CHAR(8)) AS EndTime,
tard.SensorCode,tard.InstallationPosition,tard.LowestTemperature,tard.HighestTemperature,tard.[Enabled],s.SensorName
FROM dbo.TemperatureAlarmRules tar
INNER JOIN dbo.TemperatureAlarmRuleDetails tard ON tar.ID=tard.TemperatureAlarmRuleID
INNER JOIN dbo.TemperatureAlarmRuleTimePeriods tarp ON tar.ID=tarp.TemperatureAlarmRuleID
INNER JOIN dbo.Structures struc ON tar.AffiliatedStrucID=struc.ID
INNER JOIN dbo.SensorList s ON s.SensorCode=tard.SensorCode
WHERE tar.ID=@ID
ORDER BY tard.SensorCode ASC";
var list = ConvertToList <TemperatureAlarmRuleEditFlatModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
TemperatureAlarmRuleEditModel data = null;
string msg = string.Empty;
if (list == null)
{
msg = PromptInformation.DBError;
}
else if (list.Count == 0)
{
msg = PromptInformation.NotExists;
}
else
{
var result = from item in list
group item by new { item.ID, item.Name, item.Description, item.AffiliatedStrucID, item.AffiliatedStrucName, item.StartTime, item.EndTime } into groupedResult
select new TemperatureAlarmRuleEditModel
{
ID = groupedResult.Key.ID,
Name = groupedResult.Key.Name,
Description = groupedResult.Key.Description,
AffiliatedStrucID = groupedResult.Key.AffiliatedStrucID,
AffiliatedStrucName = groupedResult.Key.AffiliatedStrucName,
StartTime = groupedResult.Key.StartTime,
EndTime = groupedResult.Key.EndTime,
TemperatureAlarmRuleDetails = (from subItem in list
where subItem.ID == groupedResult.Key.ID
select new TemperatureAlarmRuleDetailModel
{
Enabled = subItem.Enabled,
SensorCode = subItem.SensorCode,
SensorName = subItem.SensorName,
HighestTemperature = subItem.HighestTemperature,
LowestTemperature = subItem.LowestTemperature,
InstallationPosition = subItem.InstallationPosition
}).ToList()
};
data = result.FirstOrDefault();
}
return(new SelectResult <TemperatureAlarmRuleEditModel>()
{
DataResult = data,
Message = msg
});
}
/// <summary>
/// 获取终端类型下拉信息数据
/// 包含ID、名称
/// </summary>
public static List <TerminalTypeDDLModel> GetTerminalTypes()
{
string sql = "SELECT ID,TerminalName FROM dbo.TerminalTypes";
return(ConvertToList <TerminalTypeDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
public ActionResult UnusuallyDetailSummaryToExcel(EquipmentFailureSearchModel model)
{
//model.PagedDatas = ReportBLL.GetPagedSpeed(model, 1, Int32.MaxValue, base.CurrentUserID);
DataTable dt = new DataTable();
dt.Columns.Add("StrucName", typeof(string));
dt.Columns.Add("VehicleName", typeof(string));
dt.Columns.Add("ExceptionTypeName", typeof(string));
dt.Columns.Add("GPSStartTime", typeof(string));
dt.Columns.Add("GPSEndTime", typeof(string));
dt.Columns.Add("Time", typeof(string));
DataRow dr = dt.NewRow();
dr["StrucName"] = "南京亚士德";
dr["VehicleName"] = "京TEX008";
dr["ExceptionTypeName"] = "GNSS天线未接或被剪断";
dr["GPSStartTime"] = "2016-10-10 15:04:20";
dr["GPSEndTime"] = "2016-10-10 15:05:32";
dr["Time"] = "1分12秒";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["StrucName"] = "南京亚士德";
dr1["VehicleName"] = "京TEX141";
dr1["ExceptionTypeName"] = "TTS模块故障";
dr1["GPSStartTime"] = "2016-10-10 17:06:52";
dr1["GPSEndTime"] = "2016-10-10 17:08:01";
dr1["Time"] = "1分09秒";
dt.Rows.Add(dr1);
DataRow dr2 = dt.NewRow();
dr2["StrucName"] = "南京亚士德";
dr2["VehicleName"] = "京TEX201";
dr2["ExceptionTypeName"] = "TTS模块故障";
dr2["GPSStartTime"] = "2016-10-10 17:06:52";
dr2["GPSEndTime"] = "2016-10-10 17:08:01";
dr2["Time"] = "1分09秒";
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["StrucName"] = "南京亚士德";
dr3["VehicleName"] = "京TEX318";
dr3["ExceptionTypeName"] = "道路运输证IC卡模块故障";
dr3["GPSStartTime"] = "2016-10-10 15:04:20";
dr3["GPSEndTime"] = "2016-10-10 15:05:32";
dr3["Time"] = "1分12秒";
dt.Rows.Add(dr3);
List <EquipmentFailureListModel> list = ConvertToList <EquipmentFailureListModel> .Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
ViewBag.ExportFlag = true;
ViewData.Model = model;
string viewHtml = RenderPartialViewToString(this, "_UnusuallyDetailSummaryGrid");
var reportName = this.GetUIText(base.GetFunctionsInfo("ReportManage", "UnusuallyDetailSummary", "SeachUnusuallyDetailSummary").FunctionName); //功能名称
return(File(System.Text.Encoding.UTF8.GetBytes(viewHtml), "application/ms-excel", string.Format("{0}.xls", reportName + "(" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ")")));
}
public static AsiatekPagedList <MGJH_ReceiveTransportPointListModel> GetPagedReceiveTransportPoints(MGJH_ReceiveTransportPointSearchModel model, int searchPage, int pageSize)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "dbo.MGJH_TransportPointSetting tp "),
new SqlParameter("@joinStr", @" LEFT JOIN dbo.MGJH_TransportPointSetting tp2 ON tp2.ID=tp.SuperiorAddressID "),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "tp.CreateTime DESC"),
new SqlParameter("@showColumns", @"tp.ID
,tp.SettingType
,tp.CustomerName
,tp.AddressName
,tp.AddressCode
,tp.AddressArea
,tp.SuperiorAddressID
,tp.IsUnloadPoint
,tp.EFType
,tp.EFInfo
,tp.UnloadTime/60 AS UnloadTime
,tp.UnloadTimeError/60 AS UnloadTimeError
,tp2.AddressName AS SuperiorAddressName"),
};
#region 筛选条件
string conditionStr = " tp.SettingType=2";
if (!string.IsNullOrWhiteSpace(model.AddressName))
{
conditionStr += " AND tp.AddressName LIKE '%" + model.AddressName + "%'";
}
if (!string.IsNullOrWhiteSpace(model.AddressCode))
{
conditionStr += " AND tp.AddressCode LIKE '%" + model.AddressCode + "%'";
}
if (!string.IsNullOrWhiteSpace(model.CustomerName))
{
conditionStr += " AND tp.CustomerName LIKE '%" + model.CustomerName + "%'";
}
if (!string.IsNullOrWhiteSpace(model.AddressArea))
{
conditionStr += " AND tp.AddressArea LIKE '%" + model.AddressArea + "%'";
}
if (!string.IsNullOrWhiteSpace(conditionStr))
{
paras.Add(new SqlParameter("@conditionStr", conditionStr));
}
#endregion
paras.Add(new SqlParameter()
{
ParameterName = "@totalItemCount",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
paras.Add(new SqlParameter()
{
ParameterName = "@newCurrentPage",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
var rs = MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray());
List <MGJH_ReceiveTransportPointListModel> list = ConvertToList <MGJH_ReceiveTransportPointListModel> .Convert(rs);
int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value);
int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value);
return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount));
}
public static SelectResult <EditMaintenanceScheduleModel> GetMaintenanceScheduleByID(int id)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@ID",
SqlDbType = SqlDbType.Int,
},
};
paras[0].Value = id;
string sql = @"SELECT ms.ID
, ms.ScheduleName
, ms.RulesType
, ms.SettingMile
, ms.PreMile
, ms.SettingDay
, ms.PreDay
, ms.Remark
,num.BindVehicleNum
FROM dbo.MaintenanceSchedule ms
LEFT JOIN (SELECT COUNT(*) AS BindVehicleNum,ScheduleID FROM dbo.VehicleMaintenance GROUP BY ScheduleID) num ON num.ScheduleID=ms.ID
WHERE ms.ID=@ID";
List <EditMaintenanceScheduleModel> list = ConvertToList <EditMaintenanceScheduleModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
EditMaintenanceScheduleModel data = null;
string msg = string.Empty;
if (list == null)
{
msg = PromptInformation.DBError;
}
else if (list.Count == 0)
{
msg = PromptInformation.NotExists;
}
else
{
//保养方案表有数据的情况下，查询保养内容表，填充Model.MaintenanceProjectDetails数据
List <SqlParameter> detailParas = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@ID",
SqlDbType = SqlDbType.Int,
},
};
detailParas[0].Value = id;
string detailSql = @"SELECT ID AS ProjectID,
ProjectName
, ScheduleID
, PartsName
, PartsBrand
, PartsVersion
, Num
, Unit
, UnitPrice
FROM dbo.MaintenanceProject
WHERE ScheduleID=@ID";
List <MaintenanceProjectModel> detailList = ConvertToList <MaintenanceProjectModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, detailSql, detailParas.ToArray()));
list[0].MaintenanceProjectDetails = detailList;
data = list[0];
}
return(new SelectResult <EditMaintenanceScheduleModel>()
{
DataResult = data,
Message = msg
});
}
public static List <SuperiorAddressModel> GetAddSuperiorAddress()
{
string sql = "SELECT ID,AddressName FROM dbo.MGJH_TransportPointSetting WHERE SettingType=2 ";
return(ConvertToList <SuperiorAddressModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
public ActionResult GetAbnormalRouteInfo(AbnormalRouteSearchModel model)
{
DataTable dt = new DataTable();
dt.Columns.Add("StrucName", typeof(string));
dt.Columns.Add("VehicleName", typeof(string));
dt.Columns.Add("PathName", typeof(string));
dt.Columns.Add("ExceptionTypeName", typeof(string));
dt.Columns.Add("GPSStartTime", typeof(string));
dt.Columns.Add("GPSEndTime", typeof(string));
dt.Columns.Add("Time", typeof(string));
DataRow dr = dt.NewRow();
dr["StrucName"] = "南京亚士德";
dr["VehicleName"] = "京TEX008";
dr["PathName"] = "软件大道-将军山";
dr["ExceptionTypeName"] = "路段超速";
dr["GPSStartTime"] = "2016-10-10 15:04:20";
dr["GPSEndTime"] = "2016-10-10 15:05:32";
dr["Time"] = "1分12秒";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["StrucName"] = "南京亚士德";
dr1["VehicleName"] = "亚A00001";
dr1["PathName"] = "鸡鸣寺-中华门";
dr1["ExceptionTypeName"] = "进路线";
dr1["GPSStartTime"] = "2016-10-10 17:06:52";
dr1["GPSEndTime"] = "2016-10-10 17:08:01";
dr1["Time"] = "1分09秒";
dt.Rows.Add(dr1);
DataRow dr2 = dt.NewRow();
dr2["StrucName"] = "南京亚士德";
dr2["VehicleName"] = "京TEX201";
dr2["PathName"] = "软件大道-将军山";
dr2["ExceptionTypeName"] = "出路线";
dr2["GPSStartTime"] = "2016-10-10 17:06:52";
dr2["GPSEndTime"] = "2016-10-10 17:08:01";
dr2["Time"] = "1分09秒";
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["StrucName"] = "南京亚士德";
dr3["VehicleName"] = "汇A00002";
dr3["PathName"] = "鸡鸣寺-中华门";
dr3["ExceptionTypeName"] = "出路线";
dr3["GPSStartTime"] = "2016-10-10 15:04:20";
dr3["GPSEndTime"] = "2016-10-10 15:05:32";
dr3["Time"] = "1分12秒";
dt.Rows.Add(dr3);
List <AbnormalRouteListModel> list = ConvertToList <AbnormalRouteListModel> .Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
return(PartialView("_AbnormalRouteGrid", model));
}
public static AsiatekPagedList <MGJH_SynchroReceivePointListModel> GetPagedSynchroReceivePoints(MGJH_SynchroReceivePointSearchModel model, int searchPage, int pageSize)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "dbo.MGJH_SynchroReceiveAddress sr "),
new SqlParameter("@joinStr", @" LEFT JOIN dbo.MGJH_TransportPointSetting tp ON tp.SourceID=sr.ID "),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "sr.ID DESC"),
new SqlParameter("@showColumns", @"sr.ID
,sr.CustomerName
,sr.AddressName
,sr.AddressCode
,sr.AddressArea,tp.SourceID"),
};
#region 筛选条件
string conditionStr = " 1=1 ";
if (!string.IsNullOrWhiteSpace(model.AddressName))
{
conditionStr += " AND sr.AddressName LIKE '%" + model.AddressName + "%'";
}
if (!string.IsNullOrWhiteSpace(model.AddressCode))
{
conditionStr += " AND sr.AddressCode LIKE '%" + model.AddressCode + "%'";
}
if (!string.IsNullOrWhiteSpace(model.CustomerName))
{
conditionStr += " AND sr.CustomerName LIKE '%" + model.CustomerName + "%'";
}
if (!string.IsNullOrWhiteSpace(model.AddressArea))
{
conditionStr += " AND sr.AddressArea LIKE '%" + model.AddressArea + "%'";
}
if (model.IsSynchro != -1) //全部
{
if (model.IsSynchro == 1) //已同步
{
conditionStr += " AND tp.SourceID IS NOT NULL";
}
else if (model.IsSynchro == 2) //未同步
{
conditionStr += " AND tp.SourceID IS NULL";
}
}
if (!string.IsNullOrWhiteSpace(conditionStr))
{
paras.Add(new SqlParameter("@conditionStr", conditionStr));
}
#endregion
paras.Add(new SqlParameter()
{
ParameterName = "@totalItemCount",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
paras.Add(new SqlParameter()
{
ParameterName = "@newCurrentPage",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
var rs = MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray());
List <MGJH_SynchroReceivePointListModel> list = ConvertToList <MGJH_SynchroReceivePointListModel> .Convert(rs);
int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value);
int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value);
return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount));
}
public ActionResult GetRiskEarlyWarnInfo(RiskEarlyWarnSeachModel model)
{
DataTable dt = new DataTable();
dt.Columns.Add("StrucName", typeof(string));
dt.Columns.Add("VehicleName", typeof(string));
dt.Columns.Add("GPSStartTime", typeof(string));
dt.Columns.Add("GPSEndTime", typeof(string));
dt.Columns.Add("Time", typeof(string));
dt.Columns.Add("DealUser", typeof(string));
dt.Columns.Add("DealTime", typeof(string));
dt.Columns.Add("DealInfo", typeof(string));
DataRow dr = dt.NewRow();
dr["StrucName"] = "南京亚士德";
dr["VehicleName"] = "京TEX008";
dr["GPSStartTime"] = "2016-10-10 15:04:20";
dr["GPSEndTime"] = "2016-10-10 15:05:32";
dr["Time"] = "1分12秒";
dr["DealUser"] = "******";
dr["DealTime"] = "2016-10-12 10:15:32";
dr["DealInfo"] = "超速";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["StrucName"] = "南京亚士德";
dr1["VehicleName"] = "亚A00001";
dr1["GPSStartTime"] = "2016-10-10 17:06:52";
dr1["GPSEndTime"] = "2016-10-10 17:08:01";
dr1["Time"] = "1分09秒";
dr1["DealUser"] = "******";
dr1["DealTime"] = "2016-10-12 10:15:32";
dr1["DealInfo"] = "滚筒停转";
dt.Rows.Add(dr1);
DataRow dr2 = dt.NewRow();
dr2["StrucName"] = "南京亚士德";
dr2["VehicleName"] = "京TEX201";
dr2["GPSStartTime"] = "2016-10-10 17:06:52";
dr2["GPSEndTime"] = "2016-10-10 17:08:01";
dr2["Time"] = "1分09秒";
dr2["DealUser"] = "******";
dr2["DealTime"] = "2016-10-12 10:15:32";
dr2["DealInfo"] = "超速";
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["StrucName"] = "南京亚士德";
dr3["VehicleName"] = "汇A00002";
dr3["GPSStartTime"] = "2016-10-10 15:04:20";
dr3["GPSEndTime"] = "2016-10-10 15:05:32";
dr3["Time"] = "1分12秒";
dr3["DealUser"] = "******";
dr3["DealTime"] = "2016-10-12 10:15:32";
dr3["DealInfo"] = "超速";
dt.Rows.Add(dr3);
List <RiskEarlyWarnListModel> list = ConvertToList <RiskEarlyWarnListModel> .Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
return(PartialView("_RiskEarlyWarnGrid", model));
}
/// <summary>
/// 运管所下拉列表
/// </summary>
/// <returns></returns>
public static List <TransportManagementDDLModel> GetTransportManagementDDL()
{
string sql = @"SELECT [ID],[Name] FROM [dbo].[TransportManagement] WHERE Status <> 9";
return(ConvertToList <TransportManagementDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
public static AsiatekPagedList <RepairRecordModel> GetPagedRepairRecord(RepairRecordSearchModel model, int searchPage, int pageSize, int currentStrucID)
{
string joinStr = string.Format(@"
INNER JOIN dbo.Vehicles v ON v.ID = r.LinkedVehicleID
INNER JOIN Func_GetStrucAndSubStrucByUserAffiliatedStrucID({0}) s ON v.StrucID = s.ID", currentStrucID);
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "RepairRecord r"),
new SqlParameter("@joinStr", joinStr),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "r.ID"),
new SqlParameter("@showColumns", @"r.ID,r.PlateNum,r.DriverName,r.DriverPhone,r.RepairStartTime,r.RepairEndTime ,r.RepairType ,r.TotalPrice,r.Status,(select NickName from Users where ID=r.OperateUserID) AS OperateUserName,(select NickName from Users where ID=r.AuditUserID) AS AuditUserName"),
};
string conditionStr = " 1=1 ";//不查询删除和报废的
if (!string.IsNullOrWhiteSpace(model.PlateNum))
{
conditionStr += " AND r.PlateNum LIKE '%" + model.PlateNum + "%'";
}
if (!string.IsNullOrWhiteSpace(model.Status) && model.Status != "-1")
{
conditionStr += " AND r.Status='" + model.Status + "'";
}
if (model.RepairStartTimestart != DateTime.MinValue && model.RepairStartTimeend != DateTime.MinValue && model.RepairStartTimeend >= model.RepairStartTimestart)
{
conditionStr += " AND r.RepairStartTime Between '" + model.RepairStartTimestart + "' and '" + model.RepairStartTimeend.AddDays(1) + "'";
}
else if (model.RepairStartTimestart != DateTime.MinValue && model.RepairStartTimeend != DateTime.MinValue && model.RepairStartTimeend < model.RepairStartTimestart)
{
return(new AsiatekPagedList <RepairRecordModel>(new List <RepairRecordModel>(), 1, 10, 0));
}
else if (model.RepairStartTimestart == DateTime.MinValue && model.RepairStartTimeend != DateTime.MinValue)
{
conditionStr += " AND r.RepairStartTime< '" + model.RepairStartTimeend.AddDays(1) + "'";
}
else if (model.RepairStartTimestart != DateTime.MinValue && model.RepairStartTimeend == DateTime.MinValue)
{
conditionStr += " AND r.RepairStartTime> '" + model.RepairStartTimestart + "'";
}
if (!string.IsNullOrWhiteSpace(conditionStr))
{
paras.Add(new SqlParameter("@conditionStr", conditionStr));
}
paras.Add(new SqlParameter()
{
ParameterName = "@totalItemCount",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
paras.Add(new SqlParameter()
{
ParameterName = "@newCurrentPage",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.Int
});
List <RepairRecordModel> list = ConvertToList <RepairRecordModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.StoredProcedure, "Proc_GetPagedDatas", paras.ToArray()));
foreach (var recordmodel in list)
{
recordmodel.RepairStartTime = Convert.ToDateTime(recordmodel.RepairStartTime).ToString("yyyy-MM-dd");
recordmodel.RepairEndTime = Convert.ToDateTime(recordmodel.RepairEndTime).ToString("yyyy-MM-dd");
}
int totalItemCount = Convert.ToInt32(paras[paras.Count - 2].Value);
int newCurrentPage = Convert.ToInt32(paras[paras.Count - 1].Value);
return(list.ToPagedList(newCurrentPage, pageSize, totalItemCount));
}
/// <summary>
/// 获得除后台功能外的功能树节点信息
/// </summary>
/// <returns></returns>
public static List <FunctionTreeNodeModel> GetNormalFunctionsForTree()
{
string sql = @"SELECT ID,FunctionName,ParentID FROM dbo.Functions WHERE IsBackground=0 ";
return(ConvertToList <FunctionTreeNodeModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}