public ActionResult AccumulativeDrivieTimeoutToExcel(ReportSearchModels 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));
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"] = "汇A00002";
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<ReportListModel> list = ConvertToList<ReportListModel>.Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
ViewBag.ExportFlag = true;
ViewData.Model = model;
string viewHtml = RenderPartialViewToString(this, "_AccumulativeDrivieTimeoutGrid");
var reportName = this.GetUIText(base.GetFunctionsInfo("ReportManage", "AccumulativeDrivieTimeout", "SeachAccumulativeDrivieTimeout").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>
/// <param name="pageSize">页大小</param>
/// <param name="currentPage">当前页</param>
/// <param name="actionName">动作名称</param>
/// <param name="controllerID">动作所属控制器ID</param>
/// <param name="areaID">动作所属控制器所属区域ID</param>
/// <returns></returns>
public static AsiatekPagedList <ActionListModel> GetPagedActions(int pageSize, int currentPage = 1, string actionName = "", int controllerID = -1, int areaID = -1)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "Actions act"),
new SqlParameter("@joinStr", "INNER JOIN Controllers c ON act.ControllerID=c.ID INNER JOIN Areas a ON c.AreaID=a.ID"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", currentPage),
new SqlParameter("@orderBy", "act.ID"),
new SqlParameter("@showColumns", "act.ID,act.ActionName,act.Description,c.ControllerName,a.AreaName"),
};
//如果选择了区域，但是控制器选择了全部
if (areaID != -1 && controllerID == -1)
{
paras.Add(new SqlParameter("@conditionStr", "act.ActionName LIKE '%" + actionName + "%' AND c.AreaID='" + areaID + "'"));
}
else if (controllerID != -1)//选择了具体的控制器
{
paras.Add(new SqlParameter("@conditionStr", "act.ActionName LIKE '%" + actionName + "%' AND act.ControllerID='" + controllerID + "'"));
}
else
{
paras.Add(new SqlParameter("@conditionStr", "act.ActionName LIKE '%" + actionName + "%'"));
}
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 <ActionListModel> list = ConvertToList <ActionListModel> .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>
/// 根据区域编号获取全部功能下拉列表数据（用于级联）
/// <para>包括功能编号、功能名称</para>
/// </summary>
/// <param name="areaID">功能对应的动作所属控制器所属区域编号</param>
/// <returns></returns>
public static List <FunctionDDLModel> GetFunctionsByAreaID(int areaID)
{
string sql = @"SELECT f.ID,f.FunctionName FROM dbo.Functions f
LEFT JOIN dbo.Actions act ON f.ActionID=act.ID
LEFT JOIN dbo.Controllers c ON act.ControllerID=c.ID
LEFT JOIN dbo.Areas a ON c.AreaID=a.ID
WHERE a.ID=@areaID";
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@areaID", SqlDbType.Int),
};
paras[0].Value = areaID;
return(ConvertToList <FunctionDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())));
}
/// <summary>
/// 查询运管所分页信息
/// </summary>
/// <param name="model"></param>
/// <param name="searchPage"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static AsiatekPagedList <TransportManagementListModel> GetPagedTransportManagement
(TransportManagementSearchModel model, int searchPage, int pageSize)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "[dbo].[TransportManagement]"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "ID DESC"),
new SqlParameter("@showColumns", @"[ID],[Code],[Name]")
};
string conditionStr = " Status = 0 ";
if (!string.IsNullOrWhiteSpace(model.Code))
{
conditionStr += " AND Code LIKE '%" + model.Code + "%'";
}
if (!string.IsNullOrWhiteSpace(model.Name))
{
conditionStr += " AND Name LIKE '%" + model.Name + "%'";
}
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 <TransportManagementListModel> list = ConvertToList <TransportManagementListModel> .
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 AsiatekPagedList <NeedDealExceptionListModel> GetNeedDealExceptionList(NeedDealExceptionSearchModel model, int searchPage, int pageSize, int userID)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "VW_SZYDExceptions e"),
new SqlParameter("@joinStr", @" INNER JOIN Func_GetVehiclesListByUserID_New(" + userID + @") v ON e.VIN=v.VIN
INNER JOIN dbo.ExceptionType et ON et.ID=e.ExceptionTypeID
INNER JOIN dbo.Terminals t ON t.LinkedVehicleID=v.VID
INNER JOIN dbo.ServerInfo si ON t.ServerInfoID=si.ID AND si.ServerCode=e.ServerCode"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "e.SignalStartTime DESC"),
new SqlParameter("@showColumns", @"e.ID AS ExID,et.ExName,v.VehicleName,e.SignalStartTime,e.StartAddress,e.SignalEndTime,e.EndAddress,si.LinkedServerName,e.TerminalCode"),
};
#region 筛选条件
string conditionStr = "e.ExceptionTypeID IN(2,5,103) AND e.Status<>1 AND e.SignalStartTime BETWEEN '" + model.BeginDateTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND '" + model.EndDateTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
if (model.EndState == 1)//结束
{
conditionStr += " AND e.SignalEndTime IS NOT NULL";
}
else if (model.EndState == 0)//未结束
{
conditionStr += " AND e.SignalEndTime IS NULL";
}
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 list = ConvertToList <NeedDealExceptionListModel> .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 AsiatekPagedList <ReceiverMailInfoListModel> GetPagedReceiverMailInfo(ReceiverMailInfoSearchModel model, int searchPage, int pageSize)
{
string joinStr = string.Empty;
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "ReceiverMailInfo rmi"),
new SqlParameter("@joinStr", joinStr),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "rmi.CreateTime desc"),
new SqlParameter("@showColumns", @"rmi.ID ,rmi.Email,rmi.Name,rmi.Remark,rmi.Status"),
};
string conditionStr = " 1=1 ";//不查询删除
if (!string.IsNullOrWhiteSpace(model.Email))
{
conditionStr += (string.Format(" and rmi.Email like '%{0}%'", model.Email.Trim()));
}
if (!string.IsNullOrWhiteSpace(model.Name))
{
conditionStr += (string.Format(" and rmi.Name like '%{0}%'", model.Name.Trim()));
}
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 <ReceiverMailInfoListModel> list = ConvertToList <ReceiverMailInfoListModel> .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 GetLostTerminalInfo(LostReportModels model)
{
//model.GPSStartTime = "2016-10-10 00:00:00";
//model.GPSEndTime = "2016-10-10 23:59:59";
//model.PagedDatas = ReportBLL.GetLostPagedSpeed(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);
List <LostReportListModel> list = ConvertToList <LostReportListModel> .Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
return(PartialView("_LostConnectionReportDetail", model));
}
public static AsiatekPagedList <SensorListModels> GetPagedSensor(SensorSearchModel model, int searchPage, int pageSize)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "SensorList s"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "s.TypeID"),
new SqlParameter("@showColumns", @"s.TypeID ,s.SensorCode,
s.SensorName,
s.Value1,
s.Value2,
s.Remark,
s.Status,s.TypeCode"),
};
string conditionStr = " Status<>9 ";//不查询删除和报废的
if (!string.IsNullOrWhiteSpace(model.SensorName))
{
conditionStr += " AND s.SensorName LIKE '%" + model.SensorName + "%'";
}
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 <SensorListModels> list = ConvertToList <SensorListModels> .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 <InspectionModel> GetInspectionInfoByID(long ID)
{
string sql = @"SELECT OBJECT_TYPE AS ObjType ,
OBJECT_ID AS ObjID ,
AccessCode ,
Version ,
CAST(M1 AS INT) M1 ,
CAST(IA1 AS INT) IA1 ,
CAST(IC1 AS INT) IC1 ,
FLAG,
PLATNAMR AS PlatformName
FROM InspectionServer.TMS.dbo.TB_PLATFORM_CHECKMSG
WHERE MSG_ID = @ID";
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@ID",
Value = ID.ToString(),
},
};
List <InspectionModel> list = ConvertToList <InspectionModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
InspectionModel data = null;
string msg = string.Empty;
if (list == null)
{
msg = PromptInformation.DBError;
}
else if (list.Count == 0)
{
msg = PromptInformation.NotExists;
}
else
{
data = list[0];
}
return(new SelectResult <InspectionModel>()
{
DataResult = data,
Message = msg
});
}
public ActionResult SeachAccumulativeDrivieTimeout()
{
var model = new ReportSearchModels();
model.SearchPage = 1;
model.GPSStartTime = DateTime.Now.AddDays(-1).ToString("yyyy-M-dd 00:00:00");
model.GPSEndTime = DateTime.Now.AddDays(-1).ToString("yyyy-M-dd 23:59:59");
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);
//model.PagedDatas = ReportBLL.GetLostPagedSpeed(model, 1, this.PageSize, base.CurrentUserID);
List<ReportListModel> list = ConvertToList<ReportListModel>.Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
return PartialView("_SeachAccumulativeDrivieTimeout", model);
}
public static AsiatekPagedList <MapLinesListModel> GetPagedMapLines(MapLinesSearchModel model, int searchPage, int pageSize)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "MapLinesList"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "ID DESC"),
new SqlParameter("@showColumns", @"ID,
LinesType,
LinesName,
StartTime,
EndTime "),
};
#region 筛选条件
string conditionStr = "Status=0";
if (!string.IsNullOrWhiteSpace(model.LinesName))
{
conditionStr += " AND LinesName LIKE '%" + model.LinesName + "%'";
}
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 <MapLinesListModel> list = ConvertToList <MapLinesListModel> .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="functionID"></param>
/// <returns></returns>
public static SelectResult <FunctionEditModel> GetFunctionByID(int functionID)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@ID", SqlDbType.Int),
};
paras[0].Value = functionID;
string sql = @"SELECT fun.ID ,
fun.FunctionName ,
fun.ActionID ,
fun.ParentID ,
fun.Description,
fun.IsBackground,
fun.OrderIndex,
act.ControllerID ,
c.AreaID,
fun.IsAppFeatures,
fun.FeaturesCode,fun.IsAppHomeModule,fun.IsAppShortcutMenu
FROM dbo.Functions fun
LEFT JOIN dbo.Actions act ON fun.ActionID = act.ID
LEFT JOIN dbo.Controllers c ON act.ControllerID = c.ID
WHERE fun.ID = @ID";
List <FunctionEditModel> list = ConvertToList <FunctionEditModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
FunctionEditModel data = null;
string msg = string.Empty;
if (list == null)
{
msg = PromptInformation.DBError;
}
else if (list.Count == 0)
{
msg = PromptInformation.NotExists;
}
else
{
data = list[0];
}
return(new SelectResult <FunctionEditModel>()
{
DataResult = data,
Message = msg
});
}
/// <summary>
/// 获取全部功能信息
/// </summary>
/// <returns></returns>
public static List <FunctionsInfoModel> GetAllFunctions()
{
string sql = @"SELECT
f.ID,
FunctionName ,
AreaName ,
ControllerName ,
ActionName,
f.ParentID,
f.OrderIndex
FROM dbo.Functions f
LEFT JOIN dbo.Actions act ON f.ActionID=act.ID
LEFT JOIN dbo.Controllers c ON act.ControllerID=c.ID
LEFT JOIN dbo.Areas a ON c.AreaID=a.ID";
return(ConvertToList <FunctionsInfoModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
/// <summary>
/// 获取24小时内未回复的针对平台的查岗信息
/// </summary>
/// <returns></returns>
public static List <InspectionListModel> GetPlatformInspectionInfos()
{
string sql = @"SELECT CAST(MSG_ID AS BIGINT) AS ID ,
MSG_CONTENT AS Content ,
CONVERT(VARCHAR,CREATE_TIME,121) AS CheckDateTime,
PLATNAMR AS PlatformName
FROM InspectionServer.TMS.dbo.TB_PLATFORM_CHECKMSG tpc
WHERE FLAG = 0 AND OBJECT_TYPE=1 AND DATEDIFF(hour, CREATE_TIME, GETDATE()) <= 24";
// string sql = @"SELECT CAST(MSG_ID AS BIGINT) AS ID ,
// MSG_CONTENT AS Content ,
// CONVERT(VARCHAR,CREATE_TIME,121) AS CheckDateTime,
// PLATNAMR AS PlatformName
//FROM InspectionServer.TMS.dbo.TB_PLATFORM_CHECKMSG tpc
//WHERE OBJECT_TYPE=1";
return(ConvertToList <InspectionListModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql)));
}
public ActionResult SeachFatigueDrive()
{
var model = new FatigueDriveSeachModel();
model.SearchPage = 1;
model.GPSStartTime = DateTime.Now.AddDays(-1).ToString("yyyy-M-dd 00:00:00");
model.GPSEndTime = DateTime.Now.AddDays(-1).ToString("yyyy-M-dd 23:59:59");
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);
List <FatigueDriveListModel> list = ConvertToList <FatigueDriveListModel> .Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
return(PartialView("_SeachFatigueDrive", model));
}
public static List <ServerInfoModel> GetServerInfoByPlateNum(string PlateNum)
{
string sql = @" SELECT v.PlateNum,s.LinkedServerName
FROM dbo.Vehicles v
INNER JOIN dbo.Terminals t ON v.ID=t.LinkedVehicleID
INNER JOIN dbo.ServerInfo s ON s.ID = t.ServerInfoID
WHERE v.PlateNum=@PlateNum ";
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@PlateNum", SqlDbType.NVarChar)
};
paras[0].Value = PlateNum;
var list = ConvertToList <ServerInfoModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
return(list);
}
/// <summary>
/// 获取单位下拉列表信息
/// 包含单位ID、单位名称
/// </summary>
public static List <SimCodeDDLModel> GetNotUserdSimCodeList(string simCode, int?simCodeId)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@SimCode", SqlDbType.VarChar)
};
paras[0].Value = "%" + simCode + "%";
string sql = "SELECT [ID],[SimCode] FROM [dbo].[SimCodeList] WHERE [Status]<> 9 AND [Status] <> 1 AND SimCode LIKE @SimCode ";
if (simCodeId.HasValue && simCodeId.Value > 0)
{
sql += " UNION SELECT [ID],[SimCode] FROM [dbo].[SimCodeList] WHERE ID = " + simCodeId;
}
return(ConvertToList <SimCodeDDLModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray())));
}
public ActionResult GetAccumulativeDrivieTimeoutInfo(ReportSearchModels 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));
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"] = "汇A00002";
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<ReportListModel> list = ConvertToList<ReportListModel>.Convert(dt);
model.PagedDatas = list.ToPagedList(1, 1, 1);
return PartialView("_AccumulativeDrivieTimeoutGrid", model);
}
public static SelectResult <EditSimCodeModels> GetSimCodeID(int id)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@id", SqlDbType.Int),
};
paras[0].Value = id;
string sql = @" SELECT a.[ID]
,a.[SimCode]
,a.[CommMode]
,OStr.StrucName AS OwnerStrucName
,a.[OwnerStrucID]
,UStr.StrucName AS UseStrucName
,a.[UseStrucID]
,a.[ServiceProviderID]
,a.[PurchaseDate]
,a.[OpeningDate]
,a.[ExpiryDate]
,a.[Remark]
,a.[Status] FROM dbo.SimCodeList AS a LEFT JOIN Structures AS OStr ON a.OwnerStrucID = OStr.ID
LEFT JOIN Structures AS UStr ON a.UseStrucID = UStr.ID WHERE a.ID=@id";
List <EditSimCodeModels> list = ConvertToList <EditSimCodeModels> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
EditSimCodeModels data = null;
string msg = string.Empty;
if (list == null)
{
msg = PromptInformation.DBError;
}
else if (list.Count == 0)
{
msg = PromptInformation.NotExists;
}
else
{
data = list[0];
}
return(new SelectResult <EditSimCodeModels>()
{
DataResult = data,
Message = msg
});
}
public static SelectResult <UserEditModel> GetUserByID(int id)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@ID", SqlDbType.Int),
};
paras[0].Value = id;
string sql = @"SELECT a.ID ,
UserName ,
RoleID ,
NickName ,
StrucID ,
ContactNumber1 ,
ContactNumber2 ,
ContactAddress ,
a.Remark,
b.StrucName,
a.VehicleViewMode
FROM dbo.Users AS a
LEFT JOIN Structures AS b ON a.StrucID = b.ID
WHERE a.ID = @ID AND a.[Status] <> 9";
List <UserEditModel> list = ConvertToList <UserEditModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
UserEditModel data = null;
string msg = string.Empty;
if (list == null)
{
msg = PromptInformation.DBError;
}
else if (list.Count == 0)
{
msg = PromptInformation.NotExists;
}
else
{
data = list[0];
}
return(new SelectResult <UserEditModel>()
{
DataResult = data,
Message = msg
});
}
public static AsiatekPagedList <VehicleMaintainListModels> GetPagedPlateColors(VehicleMaintainSearchModels model, int searchPage, int pageSize)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "PlateColors p"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "p.Code"),
new SqlParameter("@showColumns", @"p.Code ,p.Name"),
};
string conditionStr = " 1=1 ";
if (!string.IsNullOrWhiteSpace(model.PlateCode))
{
conditionStr += " AND p.Code LIKE '%" + model.PlateCode + "%'";
}
if (!string.IsNullOrWhiteSpace(model.PlateName))
{
conditionStr += " AND p.Name LIKE '%" + model.PlateName + "%'";
}
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 <VehicleMaintainListModels> list = ConvertToList <VehicleMaintainListModels> .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 <EditPickUpTransportPointModel> GetPickUpTransportPointByID(int id)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@ID",
SqlDbType = SqlDbType.Int,
},
};
paras[0].Value = id;
string sql = @"SELECT ID
,SettingType
,AddressName
,AddressCode
,EFType
,EFInfo
FROM dbo.MGJH_TransportPointSetting
WHERE ID=@ID";
List <EditPickUpTransportPointModel> list = ConvertToList <EditPickUpTransportPointModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
EditPickUpTransportPointModel data = null;
string msg = string.Empty;
if (list == null)
{
msg = PromptInformation.DBError;
}
else if (list.Count == 0)
{
msg = PromptInformation.NotExists;
}
else
{
data = list[0];
data.EFInfo = ChangeCoordinateSystem(data.EFType, data.EFInfo, 2); //将取出的车机坐标转成地图坐标，显示
}
return(new SelectResult <EditPickUpTransportPointModel>()
{
DataResult = data,
Message = msg
});
}
/// <summary>
/// 根据单位ID获取单位及子单位的路线信息
/// </summary>
public static List <StrucMapLinesModel> GetStrucMapLines(int strucID, string lineName)
{
string sql = "SELECT * FROM dbo.Func_GetLineInfoByStrucID(@strucID) WHERE LinesName LIKE @lineName";
SqlParameter[] paras = new SqlParameter[2];
paras[0] = new SqlParameter()
{
ParameterName = "@strucID",
Value = strucID,
SqlDbType = SqlDbType.Int
};
paras[1] = new SqlParameter()
{
ParameterName = "@lineName",
Value = "%" + lineName + "%",
};
return(ConvertToList <StrucMapLinesModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras)));
}
/// <summary>
/// 获取用户分配的车辆信息 获取其自己和自己子单位的所有车辆
/// </summary>
public static AsiatekPagedList <MSBindVehicleListModel> GetPagedCustomerVehicle(int id, MSBindVehicleSearchModel model, int searchPage, int pageSize, int strucID)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "dbo.Vehicles ve "),
new SqlParameter("@joinStr", @"INNER JOIN dbo.Structures st ON ve.StrucID = st.ID
INNER JOIN Func_GetAllTheSubsetOfVehiclesByStrucID(" + strucID + ") AS vt ON ve.ID=vt.VID "),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", searchPage),
new SqlParameter("@orderBy", "ve.ID DESC"),
new SqlParameter("@showColumns", @"ve.ID AS VehicleID,ve.PlateNum,ve.VIN,ve.VehicleName,ve.StrucID,st.StrucName "),
};
StringBuilder sbWhere = new StringBuilder("ve.Status<>9 AND st.Status<>9 AND ve.ID NOT IN (SELECT VehicleID FROM dbo.VehicleMaintenance WHERE ScheduleID=" + id + ")");
if (!string.IsNullOrWhiteSpace(model.VehicleName))
{
sbWhere.Append(" AND ve.VehicleName LIKE '%" + model.VehicleName + "%'");
}
if (!string.IsNullOrEmpty(sbWhere.ToString()))
{
paras.Add(new SqlParameter("@conditionStr", sbWhere.ToString()));
}
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 <MSBindVehicleListModel> list = ConvertToList <MSBindVehicleListModel> .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 <RepairRecordModel> GetRecordByID(int id)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@id", SqlDbType.Int),
};
paras[0].Value = id;
string sql = @"SELECT * FROM dbo.RepairRecord WHERE ID=@id";
List <RepairRecordModel> list = ConvertToList <RepairRecordModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
RepairRecordModel data = null;
string msg = string.Empty;
if (list == null)
{
msg = PromptInformation.DBError;
}
else if (list.Count == 0)
{
msg = PromptInformation.NotExists;
}
else
{
data = list[0];
}
string recordsql = @"select * from dbo.RepairRecordDetail where LinkedRecordID=@id";
List <SqlParameter> paras1 = new List <SqlParameter>()
{
new SqlParameter("@id", SqlDbType.Int),
};
paras1[0].Value = id;
List <RepairRecordDetail> detaillist = ConvertToList <RepairRecordDetail> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, recordsql, paras1.ToArray()));
data.RepairRecordDetail = detaillist;
data.RepairStartTime = Convert.ToDateTime(data.RepairStartTime).ToString("yyyy-MM-dd");
data.RepairEndTime = Convert.ToDateTime(data.RepairEndTime).ToString("yyyy-MM-dd");
return(new SelectResult <RepairRecordModel>()
{
DataResult = data,
Message = msg
});
}
/// <summary>
/// 获取用户分配的车辆信息（车代号、车辆使用单位名） 默认模式
/// </summary>
/// <param name="strucID"></param>
/// <param name="vehicleName"></param>
/// <returns></returns>
public static List <UserVehicles> GetDefaultVehiclesAndStrucName(int strucID, string vehicleName)
{
string sql = @"SELECT vt.VID,vt.VehicleName,vt.VIN,s.StrucName FROM dbo.Structures s
INNER JOIN Func_GetAllTheSubsetOfVehiclesByStrucID(@StrucID) AS vt ON s.ID=vt.StrucID WHERE vt.VehicleName LIKE @vehicleName";
SqlParameter[] paras = new SqlParameter[2];
paras[0] = new SqlParameter()
{
ParameterName = "@StrucID",
Value = strucID,
SqlDbType = SqlDbType.Int
};
paras[1] = new SqlParameter()
{
ParameterName = "@vehicleName",
Value = "%" + vehicleName + "%",
};
return(ConvertToList <UserVehicles> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras)));
}
// /// <summary>
// /// 根据用户ID获取用户分配的有效车辆尚未结束的紧急报警异常
// /// </summary>
// /// <param name="userID"></param>
// /// <returns></returns>
// public static List<EmergencyAlarmInfoModel> GetEmergencyAlarms(int userID)
// {
// //考虑到跨月情况，上月紧急报警还是需要查询出来，
// //那么规定如果当前是这个月的前三天，需要同时查询上个月的异常表
// //之所以是3天，是因为考虑周末2天没人看，所以多留一天，否则第三天不刷出来，就没人知道了
// //天数多也没意义
// //还有关于上个月异常表可能不存在的情况，这个只针对刚使用的时候
// //那个时候没有上个月异常表
// //本质上这里需要判断上个月异常表是否存在，存在才去查询
// //但是我们这里采用人工添加上个月月表的方式避免这个问题。
// var now = DateTime.Now;
// string currentMonth = now.ToString("yyyyMM");
// string prevMonth = now.AddMonths(-1).ToString("yyyyMM");
// string sql = string.Empty;
// if (now.Day <= 3)//前三天，需要查询上月异常表
// {
// #region 语句
// sql = @"
// DECLARE @tempTable TABLE(VehicleName NVARCHAR(20),StrucName NVARCHAR(200),TerminalCode NVARCHAR(30))
// INSERT INTO @tempTable
// SELECT userVehicles.VehicleName,s.StrucName,t.TerminalCode FROM dbo.Structures s
// INNER JOIN
// (
// SELECT v.ID AS VID,v.StrucID,v.VehicleName FROM dbo.Vehicles v
// INNER JOIN
// (
// SELECT StrucID FROM dbo.StructureDistributionInfo
// WHERE UserID=@userID
// ) AS temp1
// ON v.StrucID=temp1.StrucID
// WHERE v.IsReceived=1 AND v.Status=0
// UNION
// SELECT v.ID,v.StrucID,v.VehicleName FROM dbo.VehicleDistributionInfo vdi
// INNER JOIN dbo.Vehicles v ON vdi.VehicleID=v.ID
// WHERE UserID=@userID AND v.IsReceived=1 AND v.Status=0
// ) AS userVehicles ON userVehicles.StrucID=s.ID
// INNER JOIN dbo.VehiclesTerminals vt ON userVehicles.VID=vt.VehicleID
// INNER JOIN dbo.Terminals t ON vt.TerminalID=t.ID
// WHERE vt.IsPrimary=1
// SELECT e.ID,e.GPSStartTime AS StartDateTime,e.Status,e.Address,[@tempTable].* FROM dbo.Exception_" + currentMonth + @" e
// INNER JOIN @tempTable ON [@tempTable].TerminalCode=e.TerminalCode
// WHERE ExceptionTypeID=1 AND GPSEndTime IS NULL
// UNION ALL
// SELECT e.ID,e.GPSStartTime AS StartDateTime,e.Status,e.Address,[@tempTable].* FROM dbo.Exception_" + prevMonth + @" e
// INNER JOIN @tempTable ON [@tempTable].TerminalCode=e.TerminalCode
// WHERE ExceptionTypeID=1 AND GPSEndTime IS NULL";
// #endregion
// }
// else//只查询当月的
// {
// #region 语句
// sql = @"
//SELECT e.ID,e.GPSStartTime AS StartDateTime,e.Status,e.Address,tempTable.* FROM dbo.Exception_" + currentMonth + @" e
//INNER JOIN
//(
//SELECT userVehicles.VehicleName,s.StrucName,t.TerminalCode FROM dbo.Structures s
//INNER JOIN
//(
// SELECT v.ID AS VID,v.StrucID,v.VehicleName FROM dbo.Vehicles v
// INNER JOIN
// (
// SELECT StrucID FROM dbo.StructureDistributionInfo
// WHERE UserID=@userID
// ) AS temp1
// ON v.StrucID=temp1.StrucID
// WHERE v.IsReceived=1 AND v.Status=0
// UNION
// SELECT v.ID,v.StrucID,v.VehicleName FROM dbo.VehicleDistributionInfo vdi
// INNER JOIN dbo.Vehicles v ON vdi.VehicleID=v.ID
// WHERE UserID=@userID AND v.IsReceived=1 AND v.Status=0
//) AS userVehicles ON userVehicles.StrucID=s.ID
//INNER JOIN dbo.VehiclesTerminals vt ON userVehicles.VID=vt.VehicleID
//INNER JOIN dbo.Terminals t ON vt.TerminalID=t.ID
//WHERE vt.IsPrimary=1
//) AS tempTable
// ON tempTable.TerminalCode=e.TerminalCode
//WHERE ExceptionTypeID=1 AND GPSEndTime IS NULL";
// #endregion
// }
// SqlParameter[] paras = new SqlParameter[1];
// paras[0] = new SqlParameter()
// {
// ParameterName = "@userID",
// Value = userID,
// SqlDbType = SqlDbType.Int
// };
// return ConvertToList<EmergencyAlarmInfoModel>.Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras));
// }
// /// <summary>
// /// 根据用户ID获取用户分配的有效车辆尚未结束的紧急报警异常
// /// </summary>
// /// <param name="userID"></param>
// /// <returns></returns>
// public static List<EmergencyAlarmInfoModel> GetEmergencyAlarms(int userID)
// {
// string sql = @"SELECT e.ID,e.GPSStartTime AS StartDateTime,e.Status,e.Address,tempTable.* FROM dbo.Exception e
//INNER JOIN
//(
//SELECT userVehicles.VehicleName,s.StrucName,t.TerminalCode FROM dbo.Structures s
//INNER JOIN
//(
// SELECT v.ID AS VID,v.StrucID,v.VehicleName FROM dbo.Vehicles v
// INNER JOIN
// (
// SELECT StrucID FROM dbo.StructureDistributionInfo
// WHERE UserID=@userID
// ) AS temp1
// ON v.StrucID=temp1.StrucID
// WHERE v.IsReceived=1 AND v.Status=0
// UNION
// SELECT v.ID,v.StrucID,v.VehicleName FROM dbo.VehicleDistributionInfo vdi
// INNER JOIN dbo.Vehicles v ON vdi.VehicleID=v.ID
// WHERE UserID=@userID AND v.IsReceived=1 AND v.Status=0
//) AS userVehicles ON userVehicles.StrucID=s.ID
//INNER JOIN dbo.VehiclesTerminals vt ON userVehicles.VID=vt.VehicleID
//INNER JOIN dbo.Terminals t ON vt.TerminalID=t.ID
//WHERE vt.IsPrimary=1
//) AS tempTable
// ON tempTable.TerminalCode=e.TerminalCode
//WHERE ExceptionTypeID=1 AND GPSEndTime IS NULL";
// SqlParameter[] paras = new SqlParameter[1];
// paras[0] = new SqlParameter()
// {
// ParameterName = "@userID",
// Value = userID,
// SqlDbType = SqlDbType.Int
// };
// return ConvertToList<EmergencyAlarmInfoModel>.Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras));
// }
#region 紧急告警
#region 自由模式
/// <summary>
/// 根据用户ID获取用户分配的有效车辆尚未结束的紧急报警异常 自由模式
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public static List <EmergencyAlarmInfoModel> GetEmergencyAlarms(int userID)
{
// string sql = @"SELECT e.ID,e.TerminalCode,tempTable.StrucName,tempTable.VehicleName,e.SignalStartTime AS StartDateTime,e.StartAddress AS Address,e.Status FROM
//dbo.VW_Exceptions e
//INNER JOIN
//(
//SELECT userVehicles.VehicleName,userVehicles.VIN,s.StrucName FROM dbo.Structures s
//INNER JOIN
//(
// SELECT v.ID AS VID,v.StrucID,v.VehicleName,v.VIN FROM dbo.Vehicles v
// INNER JOIN
// (
// SELECT StrucID FROM dbo.StructureDistributionInfo
// WHERE UserID=@userID
// ) AS temp1
// ON v.StrucID=temp1.StrucID
// WHERE v.IsReceived=1 AND v.Status=0
// UNION
// SELECT v.ID,v.StrucID,v.VehicleName,v.VIN FROM dbo.VehicleDistributionInfo vdi
// INNER JOIN dbo.Vehicles v ON vdi.VehicleID=v.ID
// WHERE UserID=@userID AND v.IsReceived=1 AND v.Status=0
//) AS userVehicles ON userVehicles.StrucID=s.ID
//) AS tempTable ON e.VIN=tempTable.VIN
//WHERE e.ExceptionTypeID=1 AND e.SignalEndTime IS NULL";
string sql = @"SELECT e.ID,e.TerminalCode,tempTable.StrucName,tempTable.VehicleName,
e.SignalStartTime AS StartDateTime,e.StartAddress AS Address,e.Status FROM
dbo.VW_Exceptions e
INNER JOIN
(
SELECT userVehicles.VehicleName,userVehicles.VIN,s.StrucName FROM dbo.Structures s
INNER JOIN Func_GetVehiclesListByUserID_New(@userID) AS userVehicles ON userVehicles.StrucID=s.ID
) AS tempTable ON e.VIN=tempTable.VIN
WHERE e.ExceptionTypeID=1 AND e.SignalEndTime IS NULL";
SqlParameter[] paras = new SqlParameter[1];
paras[0] = new SqlParameter()
{
ParameterName = "@userID",
Value = userID,
SqlDbType = SqlDbType.Int
};
return(ConvertToList <EmergencyAlarmInfoModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras)));
}
/// <summary>
/// 获取角色分页数据
/// </summary>
/// <param name="currentPage">当前页</param>
/// <param name="pageSize">页大小</param>
/// <param name="roleName">角色编号</param>
/// <returns></returns>
public static AsiatekPagedList <RoleListModel> GetPagedRoleInfo(int currentPage, int pageSize, bool isSuperAdmin, string roleName = "")
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@tableName", "Roles"),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@currentPage", currentPage),
new SqlParameter("@orderBy", "ID"),
};
if (isSuperAdmin)//超级管理员查询全部角色信息
{
paras.Add(new SqlParameter("@conditionStr", "RoleName LIKE '%" + roleName + "%'"));
}
else//其他则只查询普通用户
{
paras.Add(new SqlParameter("@conditionStr", "RoleName LIKE '%" + roleName + "%' AND RoleLevel='2'"));
}
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 <RoleListModel> list = ConvertToList <RoleListModel> .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 <EditSynchroReceivePointModel> GetSynchroReceivePointByID(int id)
{
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@ID",
SqlDbType = SqlDbType.Int,
},
};
paras[0].Value = id;
string sql = @"SELECT sr.ID
,sr.CustomerName
,sr.AddressName
,sr.AddressCode
,sr.AddressArea
FROM dbo.MGJH_SynchroReceiveAddress sr
WHERE sr.ID=@ID";
List <EditSynchroReceivePointModel> list = ConvertToList <EditSynchroReceivePointModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sql, paras.ToArray()));
EditSynchroReceivePointModel data = null;
string msg = string.Empty;
if (list == null)
{
msg = PromptInformation.DBError;
}
else if (list.Count == 0)
{
msg = PromptInformation.NotExists;
}
else
{
data = list[0];
}
return(new SelectResult <EditSynchroReceivePointModel>()
{
DataResult = data,
Message = msg
});
}
public static List <EFPropertyPeriodModel> GetEFPropertyPeriodByID(int id)
{
List <EFPropertyPeriodModel> list = new List <EFPropertyPeriodModel>();
string sql = @" SELECT COUNT(0) FROM dbo.ElectricFencePropertyPeriod WHERE EFPropertyID=@EFPropertyID ";
List <SqlParameter> paras = new List <SqlParameter>()
{
new SqlParameter("@EFPropertyID", SqlDbType.Int),
};
paras[0].Value = id;
var count = MSSQLHelper.ExecuteScalar(CommandType.Text, sql, paras.ToArray());
if (count != null)
{
string sqlTemp = string.Format(@" SELECT Week,CONVERT(VARCHAR(12), StartTime, 114) AS StartTime,CONVERT(VARCHAR(12), EndTime, 114) AS EndTime FROM dbo.ElectricFencePropertyPeriod WHERE EFPropertyID={0} ORDER BY Week ASC", id);
list = ConvertToList <EFPropertyPeriodModel> .Convert(MSSQLHelper.ExecuteDataTable(CommandType.Text, sqlTemp));
}
return(list);
}
