/// <summary>
/// 获取部门下面含有执法证件的人员
/// 添加人：周 鹏
/// 添加时间：2015-04-14
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <returns></returns>
public List <BaseUserEntity> GetDeptsUsersDistinctNoCarNo(BaseUserEntity seach)
{
try
{
var strSql = new StringBuilder("");
// strSql.AppendFormat(
// @"SELECT BU.UserId AS 'Id',Account AS 'LoginName',RealName AS 'UserName',[Password] AS 'PassWord',Secretkey,DepartmentId AS 'DeptId' FROM Base_User AS BU
//JOIN Base_Certificate AS BC ON BU.UserId=BC.UserId
//WHERE [Enabled]=1");
strSql.AppendFormat(
@"SELECT BU.Id AS 'Id',Account AS 'LoginName',RealName AS 'UserName',[Password] AS 'PassWord',Secretkey,DepartmentId AS 'DeptId' FROM CrmUser AS BU
WHERE [Enabled]=1");
if (!string.IsNullOrEmpty(seach.DeptId))
{
strSql.AppendFormat(@" AND DepartmentId='{0}'", seach.DeptId);
}
strSql.Append(" ORDER BY SortCode");
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql.ToString()).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseUserEntity> .ConvertToList(list)
: new List <BaseUserEntity>());
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 操作日志查询
/// </summary>
/// <param name="logType">日志类型</param>
/// <param name="model">模块</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="pageSzie"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public PageJqDatagrid <ComOperationLogEntity> GetCommonQuryGetCommonQury(int logType, string startTime, string endTime, int pageSzie = 15,
int pageIndex = 1)
{
//计时
var stopwatch = new Stopwatch();
stopwatch.Start();
int totalRecords;
var data = new ComOperationLogDal().GetCommonQury(logType, startTime, endTime, pageSzie, pageIndex, out totalRecords);
var list = ConvertListHelper <ComOperationLogEntity> .ConvertToList(data);
stopwatch.Stop();
var totalPage = (totalRecords + pageSzie - 1) / pageSzie; //计算页数
return(new PageJqDatagrid <ComOperationLogEntity>
{
page = pageIndex,
rows = list,
total = totalPage,
records = totalRecords,
costtime = stopwatch.ElapsedMilliseconds.ToString()
});
}
/// <summary>
/// 执法证（监督）证使用管理
/// </summary>
/// <returns></returns>
public ActionResult CertificateUseManage()
{
var depts = _baseBll.GetCertificateType("0035");
ViewData["CardType"] = new SelectList(ConvertListHelper <ComResourceEntity> .ConvertToList(depts), "Id", "RsKey");
ViewData["UserID"] = Request["uid"];
return(View());
}
/// <summary>
/// 查询数据实体
/// 添加人：周 鹏
/// 添加时间：2015-02-26
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <param name="id">编号</param>
/// <returns></returns>
public BaseDepartmentEntity Get(string id)
{
var strSql = string.Format("SELECT * FROM CrmDepartment WHERE Id='{0}'", id);
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseDepartmentEntity> .ConvertToList(list)[0]
: new BaseDepartmentEntity());
}
public List <BaseCompanyEntity> GetAllCompany()
{
var strSql = string.Format("SELECT Id AS CompanyId,* FROM CrmCompany WHERE ParentId='D51BBD91-1381-4D02-8910-E3654F34D6A0' AND Enabled=1 ORDER BY SortCode");
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseCompanyEntity> .ConvertToList(list)
: new List <BaseCompanyEntity>());
}
/// <summary>
/// 查询所有部门
/// 添加人：周 鹏
/// 添加时间：2015-02-14
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <returns></returns>
public List <BaseCompanyEntity> GetAllCompanys()
{
var strSql = string.Format("SELECT * FROM CrmCompany WHERE Enabled=1 ORDER BY SortCode");
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseCompanyEntity> .ConvertToList(list)
: new List <BaseCompanyEntity>());
}
/// <summary>
/// 所有执法大队
/// </summary>
/// <returns></returns>
public List <BaseDepartmentEntity> GetAllsDetachments()
{
var strSql = string.Format("SELECT * FROM CrmDepartment WHERE Enabled=1 AND CompanyId='c1ef0d6f-4ba6-4acb-8c98-cc5a87f3954f'");
strSql += "ORDER BY SortCode";
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseDepartmentEntity> .ConvertToList(list)
: new List <BaseDepartmentEntity>());
}
/// <summary>
/// 查询部分部门
/// 添加人;叶念
/// 添加时间：2015-07-14
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public IEnumerable <BaseDepartmentEntity> GetAllDetachments(string type = "lochus")
{
var strSql = string.Format("SELECT Id AS DepartmentId,* FROM CrmDepartment WHERE Enabled=1 ");
//if (type.Equals("lochus"))
//{
// strSql += " AND FullName LIKE '%中队'";
//}
strSql += "ORDER BY SortCode";
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql).Tables[0];
return(list != null && list.Rows.Count > 0 ? ConvertListHelper <BaseDepartmentEntity> .ConvertToList(list)
: new List <BaseDepartmentEntity>());
}
/// <summary>
/// 获取所有用户的ID和名字
/// 添加人：xj
/// 添加时间：2018-11-06
/// </summary>
/// <returns></returns>
public List <BaseUserEntity> GetAllUserNew()
{
try
{
var strSql = string.Format(@"SELECT ID,REALNAME UserName FROM CRMUSER WHERE ROWSTATUS = 1");
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseUserEntity> .ConvertToList(list)
: null);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 查询所有部门
/// 添加人：周 鹏
/// 添加时间：2015-02-14
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <returns></returns>
public List <BaseDepartmentEntity> GetAllDepartments(string CompanyId = "")
{
var strSql = string.Format("SELECT * FROM CrmDepartment WHERE Enabled=1 ");
if (!string.IsNullOrEmpty(CompanyId))
{
strSql += string.Format(" AND CompanyId='{0}' ", CompanyId);
}
strSql += " ORDER BY SortCode";
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseDepartmentEntity> .ConvertToList(list)
: new List <BaseDepartmentEntity>());
}
/// <summary>
/// 执法大队
/// </summary>
/// <param name="CompanyID"></param>
/// <returns></returns>
public List <BaseCompanyEntity> GetCompanyNameByID(string CompanyID)
{
var strSql = string.Format("SELECT * FROM CrmCompany WHERE RowStatus=1 ");
if (!string.IsNullOrEmpty(CompanyID))
{
strSql += string.Format(" AND ID='{0}' ", CompanyID);
}
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseCompanyEntity> .ConvertToList(list)
: new List <BaseCompanyEntity>());
}
/// <summary>
/// 根据ID获取用户信息
/// 添加人：周 鹏
/// 添加时间：2015-02-09
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <param name="id">用户ID</param>
/// <returns></returns>
public BaseUserEntity GetEntity(string id)
{
try
{
var strSql = new StringBuilder("");
strSql.AppendFormat(@"SELECT [Id],[CompanyId],[DepartmentId], RealName AS 'UserName' FROM CrmUser AS CU WHERE CU.[Enabled]=1 AND CU.Id='{0}'", id);
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql.ToString()).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseUserEntity> .ConvertToList(list)[0]
: new BaseUserEntity());
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 根据ID获取用户信息
/// 添加人：周 鹏
/// 添加时间：2015-02-09
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <param name="id">用户ID</param>
/// <returns></returns>
public BaseUserEntity Get(string id)
{
try
{
var strSql = new StringBuilder("");
strSql.AppendFormat(@"SELECT CU.Id AS 'Id',Account AS 'LoginName',RealName AS 'UserName',[Password] AS 'PassWord',Secretkey,CU.CompanyId,CU.DepartmentId AS 'DeptId',CD.FullName AS DeptName,Mobile FROM CrmUser AS CU
LEFT JOIN CrmDepartment AS CD ON CU.DepartmentId=CD.Id
WHERE CU.[Enabled]=1 AND CU.Id='{0}'", id);
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql.ToString()).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseUserEntity> .ConvertToList(list)[0]
: new BaseUserEntity());
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 获取所以得用户信息
/// 添加人：胡耀锋
/// 添加时间：2015-05-06
/// </summary>
/// <returns></returns>
public List <BaseUserEntity> GetAllUser()
{
try
{
var strSql = string.Format(@"SELECT BU.UserId AS 'Id',Account AS 'LoginName',RealName AS 'UserName',[Password] AS 'PassWord',Secretkey,DepartmentId AS 'DeptId',Mobile,BC.CertificateNo
FROM CrmUser AS BU
LEFT JOIN Base_Certificate AS BC ON BC.UserId=BU.UserId
WHERE [Enabled]=1 ");
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseUserEntity> .ConvertToList(list)
: null);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
public IEnumerable <BaseUserEntity> GetAllBaseUsers()
{
try
{
var strSql = new StringBuilder("");
strSql.AppendFormat(
@"SELECT Id AS 'Id',Account AS 'LoginName',RealName AS 'UserName',[Password] AS 'PassWord',Secretkey,DepartmentId AS 'DeptId',Mobile FROM CrmUser WHERE [Enabled]=1");
strSql.Append(" ORDER BY SortCode");
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql.ToString()).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseUserEntity> .ConvertToList(list)
: new List <BaseUserEntity>());
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 根据手机类型获取最新版本信息
/// </summary>
/// <returns></returns>
public ComVersionManagementEntity GetNewVersionInfo(int mobileType)
{
var strsql = new StringBuilder();
strsql.AppendFormat(
@"SELECT TOP 1 *
FROM ComVersionManagement WITH(NOLOCK) Where RowStatus=1 AND MobileType={0} ORDER BY VersionNo DESC",
mobileType);
WriteDatabase.Comment = new StringExtension.SqlComment
{
Author = "",
FileName = "ComVersionManagementDal.cs",
ForUse = "根据手机类型获取最新版本信息",
FunName = "GetNewVersionInfo"
};
var dt = WriteDatabase.Query(strsql.ToString());
var list = ConvertListHelper <ComVersionManagementEntity> .ConvertToList(dt);
return(list.Any() ? list[0] : new ComVersionManagementEntity());
}
/// <summary>
/// 根据用户名查询用户信息
/// 添加人：周 鹏
/// 添加时间：2015-02-09
/// </summary>
/// <history>
/// 修改描述：时间+作者+描述
/// </history>
/// <param name="username">用户登录名</param>
/// <returns></returns>
public BaseUserEntity GetUserByUserName(string username)
{
try
{
var strSql = string.Format(@"SELECT CU.Id AS 'Id',Account AS 'LoginName',RealName AS 'UserName',[Password] AS 'PassWord',
Secretkey,CU.DepartmentId AS 'DeptId',CD.FullName AS DeptName, Mobile,BC.CertificateNo,
CU.CompanyId,CCP.FullName CompanyName,CU.ChangePasswordDate
FROM CrmUser AS CU WITH(NOLOCK)
JOIN CrmDepartment AS CD WITH(NOLOCK) ON CU.DepartmentId=CD.Id
join CrmCompany as CCP WITH(NOLOCK) ON CU.CompanyId=CCP.Id
LEFT JOIN Base_Certificate AS BC WITH(NOLOCK) ON BC.UserId=CU.Id
WHERE CU.RowStatus=1 AND CU.[Enabled]=1 AND (Account='{0}' OR Mobile='{0}')", username);
var list = SqlHelper.ExecuteDataset(SqlHelper.SqlConnStringJcxxdbRead, CommandType.Text, strSql).Tables[0];
return(list != null && list.Rows.Count > 0
? ConvertListHelper <BaseUserEntity> .ConvertToList(list)[0]
: new BaseUserEntity());
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 短信消息查询
/// </summary>
/// <param name="person">姓名</param>
/// <param name="phoneNumber">手机号码</param>
/// <param name="dateStart">开始时间</param>
/// <param name="dateEnd">结束时间</param>
/// <param name="pageSzie"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public PageJqDatagrid <ComNoteContentEntity> GetCommonQuryGetCommonQury(string person, string phoneNumber, string dateStart, string dateEnd, int pageSzie = 15,
int pageIndex = 1)
{
//计时
var stopwatch = new Stopwatch();
stopwatch.Start();
int totalRecords;
var data = new ComNoteDal().GetCommonQury(person, phoneNumber, dateStart, dateEnd, pageSzie, pageIndex, out totalRecords);
var list = ConvertListHelper <ComNoteContentEntity> .ConvertToList(data);
stopwatch.Stop();
var totalPage = (totalRecords + pageSzie - 1) / pageSzie; //计算页数
return(new PageJqDatagrid <ComNoteContentEntity>
{
page = pageIndex,
rows = list,
total = totalPage,
records = totalRecords,
costtime = stopwatch.ElapsedMilliseconds.ToString()
});
}