public List <User> GetUserByPhone(string phoneNum) { var list = new List <User>(); var user = new User(); string sql = @"SELECT Id,BUSurname,BUGivenname,BUJobNumber,BUSex,BUAvatars,BUPhoneNum,BUEmail,BUDepartId,BUIsValid,BUTitle FROM " + tableName + " WHERE BUPhoneNum=@BUPhoneNum AND " + " BUIsValid=@BUIsValid"; SqlParameter[] para = { new SqlParameter("@BUIsValid", EnabledEnum.Enabled.GetHashCode()), new SqlParameter("@BUPhoneNum", phoneNum) }; var ds = SqlHelper.ExecuteDataSet(CommandType.Text, sql, null, para); if (ds != null && ds.Tables.Count > 0) { var dt = new DataTable(); dt = ds.Tables[0]; list = DataConvertHelper.DataTableToList <User>(dt); } return(list); }
// GET: /SysUser/Default1 public ActionResult Index(int?page, int?iden, int?rid) { Pager pager = new Pager(); pager.table = "AdsCustomer"; pager.strwhere = "1=1"; int identity = iden ?? 0; if (identity != 0) { pager.strwhere = pager.strwhere + " and CustomerIdentity=" + iden; } int roleid = rid ?? 0; if (roleid != 0) { pager.strwhere = pager.strwhere + " and CustomerRole=" + roleid; } pager.PageSize = SkyPageSize; pager.PageNo = page ?? 1; pager.FieldKey = "CustomerId"; pager.FiledOrder = "CustomerId desc"; pager = CommonDal.GetPager(pager); IList <AdsCustomer> customers = DataConvertHelper <AdsCustomer> .ConvertToModel(pager.EntityDataTable); var customersAsIPageList = new StaticPagedList <AdsCustomer>(customers, pager.PageNo, pager.PageSize, pager.Amount); ViewBag.SmallTitle = "客户总数共计:" + pager.Amount + "人"; CategoryServices categoryServices = new CategoryServices(); ViewData["CustomerRole"] = categoryServices.GetCategorySelectList(SkyCustomerRootId); ViewData["CusRolebtn"] = categoryServices.GetCategoryListByParentID(SkyCustomerRootId); return(View(customersAsIPageList)); }
//底部导航5个页面 #region 1量表测评列表 public ActionResult Ceping(int?page) { if (Session["CustomerId"] != null) { int id = int.Parse(Session["CustomerId"].ToString()); var babys = unitOfWork.adsBabysRepository.Get(filter: u => u.CustomerId == id && u.Babystatus == true); int count = babys.Count(); if (count > 0) { AdsBaby baby = babys.First() as AdsBaby; Pager pager = new Pager(); pager.table = "Baogao"; pager.strwhere = "BabyId=" + baby.BabyId; pager.PageSize = 20; pager.PageNo = page ?? 1; pager.FieldKey = "BaogaoId"; pager.FiledOrder = "BaogaoId desc"; pager = CommonDal.GetPager(pager); IList <Baogao> baogaos = DataConvertHelper <Baogao> .ConvertToModel(pager.EntityDataTable); var baogaosAsIPageList = new StaticPagedList <Baogao>(baogaos, pager.PageNo, pager.PageSize, pager.Amount); ViewBag.babyname = baby.BabyName; return(View(baogaosAsIPageList)); } else { return(RedirectToAction("NoBaby")); } } else { return(RedirectToAction("Login")); } }
/// <summary> /// 描述:根据工号获取用户 /// </summary> /// <param name="jobnumber"></param> /// <returns></returns> public List <UserModel> GetUserByJobNumber(string jobnumber) { var list = new List <UserModel>(); var user = new UserModel(); string sql = @"SELECT U.Id,BUName,U.BUJobNumber,U.BUSex,U.BUAvatars,U.BUPhoneNum,U.BUEmail,U.BUDepartId,D.BDDeptName AS BUDeptName, U.BUIsValid,U.BUTitle FROM " + userTableName + " U WITH(NOLOCK) LEFT JOIN " + depatrTableName + " D WITH(NOLOCK) ON U.BUDepartId = D.Id " + " WHERE U.BUJobNumber=@BUJobNumber AND " + " U.BUIsValid=@BUIsValid"; SqlParameter[] para = { new SqlParameter("@BUIsValid", EnabledEnum.Enabled.GetHashCode()), new SqlParameter("@BUJobNumber", jobnumber) }; var ds = ExecuteDataSet(CommandType.Text, sql, null, para); if (ds != null && ds.Tables.Count > 0) { var dt = new DataTable(); dt = ds.Tables[0]; list = DataConvertHelper.DataTableToList <UserModel>(dt); } return(list); }
/// <summary> /// 多选题解析 /// 解析用户回答,获取下一题 /// </summary> /// <param name="uanswer">用户答题对象</param> /// <param name="question">问题对象</param> /// <param name="answer">用户答案文本</param> /// <returns>下一题</returns> private WeiExecuteResult MultiCheckAnalysis(UserAnswerDetail uanswer, Question question, UserAnswerDetail nuseranswerdetail) { decimal?next = null; // decimal.Floor(question.Sort + 1); // 用户的答案数组 //var tmpU = uanswer.Answer.ToCharArray().Distinct().Except(CommonHelper.SPECIALCHARACTERS); var qalist = question.QuestionAnswerList.OrderBy(x => x.Sort); string answer = DataConvertHelper.ChineseNumberAnalysis(uanswer.Answer).ToLower(); if (!question.QuestionItemList.Any(x => answer.IndexOf(x.Code.ToLower()) != -1)) { return(WeiExecuteResultHelper.Failed(this._propertyService.GetValue("Answer.NotConsistent"))); } string keycode = null; QuestionAnswer qamodel = null; switch (question.MatchingType) { case MatchingType.Multiple: { foreach (var qanswer in qalist) { var tmpQ = qanswer.AnswerKeys.ToLower().Split('|'); keycode = tmpQ.FirstOrDefault(y => { if (string.IsNullOrEmpty(y)) { return(false); } if (y == "*") { return(true); } var tmp2 = y.ToLower().Split('&'); bool result = !tmp2.Any(z => answer.IndexOf(z) == -1); return(result); }); if (!string.IsNullOrEmpty(keycode)) { UserAnswerQueue uaqueue = new UserAnswerQueue() { QuestionAnswer_Id = qanswer.Id, UserAnswer_Id = uanswer.UserAnswer_Id, Status = 0, QCode = keycode }; this._userAnswerQueueRepository.Insert(uaqueue); } } } break; default: { foreach (var qanswer in qalist) { var tmpQ = qanswer.AnswerKeys.ToLower().Split('|'); keycode = tmpQ.FirstOrDefault(y => { if (string.IsNullOrEmpty(y)) { return(false); } if (y == "*") { return(true); } var tmp2 = y.ToLower().Split('&'); bool result = !tmp2.Any(z => answer.IndexOf(z) == -1); return(result); }); if (!string.IsNullOrEmpty(keycode)) { qamodel = qanswer; break; } } } break; } //if (qamodel == null) //{ // return WeiExecuteResultHelper.Failed(this._propertyService.GetValue("Answer.NotConsistent")); //} if (qamodel != null) { nuseranswerdetail.QCode = keycode; //if (qamodel.Next != null) next = qamodel.Next; } else { next = decimal.Floor(question.Sort + 1); } //nuseranswerdetail.QCode = keycode; //next = qamodel.Next; return(WeiExecuteResultHelper.Success(null, next)); }
/// <summary> /// /// </summary> /// <param name="param"></param> /// <param name="totalCount"></param> /// <returns></returns> public List <MaToolModel> SearchPageList(MaToolSearchModel param, out int totalCount) { var list = new List <MaToolModel>(); var selectSql = new StringBuilder(); var countSql = new StringBuilder(); var whereSql = new StringBuilder(); whereSql.Append(" WHERE 1 = 1 AND BMIsValid = 1"); if (param.Classification > 0) { whereSql.Append(string.Format(" AND BMClassification = {0}", param.Classification)); } if (!string.IsNullOrWhiteSpace(param.MachineName)) { whereSql.Append(string.Format(" AND BMEquipmentName Like N'%{0}%'", param.MachineName)); } if (!string.IsNullOrWhiteSpace(param.EquipmentNo)) { whereSql.Append(string.Format(" AND BMEquipmentNo Like N'%{0}%'", param.EquipmentNo)); } if (!string.IsNullOrWhiteSpace(param.Type)) { whereSql.Append(string.Format(" AND BMType Like N'%{0}%'", param.Type)); } if (!string.IsNullOrWhiteSpace(param.FixtureNo)) { whereSql.Append(string.Format(" AND BMFixtureNo Like N'%{0}%'", param.FixtureNo)); } selectSql.Append(string.Format(@" SELECT newTable.* FROM ( SELECT TOP ( {0} * {1} ) ROW_NUMBER() OVER ( ORDER BY BMOperateTime DESC) RowNum ,[Id] ,[BMEquipmentName] ,[BMClassification] ,[BMEquipmentNo] ,[BMFixtureNo] ,[BMType] ,[BMSerialNumber] ,[BMQuantity] ,[BMManufacturedDate] ,[BMPower] ,[BMOutlineDimension] ,[BMAbility] ,[BMNeedPressureAir] ,[BMNeedCoolingWater] ,[BMIncomingDate] ,[BMRemarks] ,[BMIsValid] ,[BMCreateUserNo] ,[BMCreateUserName] ,[BMCreateTime] ,[BMOperateUserNo] ,[BMOperateUserName] ,[BMOperateTime] FROM {2} with(NOLOCK) {3} ORDER BY BMOperateTime DESC) newTable WHERE newTable.RowNum > ( ( {0} - 1 ) * {1} ) ", param.CurrentPage, param.PageSize, tableName, whereSql.ToString())); countSql.Append(string.Format(@"SELECT COUNT(1) FROM {0} with(NOLOCK) {1} ", tableName, whereSql.ToString())); var ds = ExecuteDataSet(CommandType.Text, selectSql.ToString()); totalCount = ExecuteCount(CommandType.Text, countSql.ToString()); if (ds != null && ds.Tables.Count > 0) { DataTable dt = new DataTable(); dt = ds.Tables[0]; list = DataConvertHelper.DataTableToList <MaToolModel>(dt); } return(list); }
/// <summary> /// 获取消息并携带单个实体对象 /// </summary> /// <typeparam name="T">目标实体类型</typeparam> /// <param name="helper">数据库操作辅助类对象</param> /// <param name="procName">存储过程名称</param> /// <param name="param">存储过程参数</param> /// <param name="includeError">是否包括错误返回值</param> /// <returns></returns> public static IMessage <T> GetMessageForObject <T>(this SqlHelper helper, string procName, IList <SqlParameter> param, bool includeError = true) { if (includeError) { param.BuildErrorParameter(); } param.BuildReturnParameter(); DataSet dataSet = helper.ExecuteFillDataSet(CommandType.StoredProcedure, procName, param.ToArray <SqlParameter>()); if (!ValidationHelper.IsNotEmptyDataSet(dataSet)) { return(new Message <T>(param[param.Count - 1].Value.ToString().ToInt32(), param[param.Count - 2].Value.ToString(), default(T))); } return(new Message <T>(param[param.Count - 1].Value.ToString().ToInt32(), param[param.Count - 2].Value.ToString(), DataConvertHelper.ToObject <T>(dataSet.Tables[0].Rows[0]))); }
/// <summary> /// 将数据行转换为指定类型的对象 /// </summary> /// <typeparam name="T">指定类型</typeparam> /// <param name="row">数据行</param> /// <param name="excludeProperties">需要排除的属性名称列表</param> /// <returns></returns> public static T ParseToObject <T>(this DataRow row, params string[] excludeProperties) { return(DataConvertHelper.ToObject <T>(row, excludeProperties)); }
/// <summary> /// 将数据表转换为指定类型的对象集合 /// </summary> /// <typeparam name="T">指定类型</typeparam> /// <param name="table">数据表</param> /// <param name="excludeProperties">需要排除的属性名称列表</param> /// <returns></returns> public static IList <T> ParseToList <T>(this DataTable table, params string[] excludeProperties) { return(DataConvertHelper.ToList <T>(table, excludeProperties)); }
public ActionResult Calendar(int?page, int?cid, int?orderid) { int oid = orderid ?? 1; AdsBaby baby = new AdsBaby(); if (Session["CustomerId"] != null) { int id = int.Parse(Session["CustomerId"].ToString()); var babys = unitOfWork.adsBabysRepository.Get(filter: u => u.CustomerId == id && u.Babystatus == true); int count = babys.Count(); if (count > 0) { baby = babys.First() as AdsBaby; ViewData["videocat"] = CategoryServices.GetCategoryListByParentID(2); ViewBag.babyName = baby.BabyName; ViewBag.babyId = baby.BabyId; ViewBag.cepingcount = StatisticsServices.GetCepingCountByBabyId(baby.BabyId); ViewBag.pingjiacount = StatisticsServices.GetPingjiaCountByBabyId(baby.BabyId, 0); ViewBag.days = StatisticsServices.GetDaysByCustomerId(baby.BabyRegTime); List <BaogaoDemention> demlist = PlanServices.PlanCategory(baby.BabyId); if (demlist.Count() == 0) { return(RedirectToAction("NoScale", new { name = baby.BabyName })); } int categoryid = cid ?? demlist[0].demcategoryid; Pager pager = new Pager(); pager.table = "AdsVideo"; pager.strwhere = "VideoCategory=" + categoryid; pager.PageSize = 15; pager.PageNo = page ?? 1; pager.FieldKey = "VideoId"; pager.FiledOrder = "VideoId desc"; pager = CommonDal.GetPager(pager); IList <AdsVideo> videos = DataConvertHelper <AdsVideo> .ConvertToModel(pager.EntityDataTable); var videosAsIPageList = new StaticPagedList <AdsVideo>(videos, pager.PageNo, pager.PageSize, pager.Amount); ViewBag.orderid = oid; if (oid == 1 || oid == 2) { ViewBag.ctitle = "必修任务"; ViewBag.cminTitle = "每次需训练30分钟"; } if (oid == 3 || oid == 4) { ViewBag.ctitle = "选修任务"; ViewBag.cminTitle = "每次需训练20分钟"; } if (oid == 5) { ViewBag.ctitle = "一般任务"; ViewBag.cminTitle = "自行安排训练"; } ViewData["dem"] = demlist; return(View(videosAsIPageList)); } else { return(RedirectToAction("NoBaby")); } } else { return(RedirectToAction("Login")); } }
public ActionResult UploadDefectCodeFiles() { var result = new ResultInfoModel() { IsSuccess = false }; StringBuilder strbuild = new StringBuilder(); string FileName; string savePath; HttpPostedFileBase file = Request.Files["file"]; if (file == null || file.ContentLength <= 0) { result.Message = "please choose file"; return(Content(JsonHelper.JsonSerializer(result))); } else { string fileName = Path.GetFileName(file.FileName); int filesize = file.ContentLength; //获取上传文件的大小单位为字节byte string fileEx = Path.GetExtension(fileName); //获取上传文件的扩展名 string NoFileName = Path.GetFileNameWithoutExtension(fileName); //获取无扩展名的文件名 int Maxsize = 5000 * 1024; //定义上传文件的最大空间大小为4M string FileType = ".xls,.xlsx"; //定义上传文件的类型字符串 FileName = NoFileName + fileEx; if (!FileType.Contains(fileEx)) { result.Message = "please upload .xls and .xlsx"; return(Content(JsonHelper.JsonSerializer(result))); } if (filesize >= Maxsize) { result.Message = string.Format("file size can't big than {0}", Maxsize); return(Content(JsonHelper.JsonSerializer(result))); } string path = Server.MapPath("~/App_Data/uploads"); savePath = Path.Combine(path, FileName); file.SaveAs(savePath); string strConn; strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties=Excel 12.0;"; using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn); DataSet myDataSet = new DataSet(); try { myCommand.Fill(myDataSet, "ExcelInfo"); } catch (Exception ex) { result.Message = ex.Message; return(Content(JsonHelper.JsonSerializer(result))); } DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable(); var importResult = new Importresult(); importResult.FalseInfo = new List <FalseInfo>(); try { for (int i = 0; i < table.Rows.Count; i++) { CodeDefectModel model = new CodeDefectModel(); model.BDCodeType = table.Rows[i][0].ToString(); model.BDCodeNo = DataConvertHelper.ToInt(table.Rows[i][1].ToString(), 0); model.BDCode = table.Rows[i][2].ToString(); model.BDCodeNameEn = table.Rows[i][3].ToString(); model.BDCodeNameCn = table.Rows[i][4].ToString(); var inserResult = CodeBusiness.SaveDefectCode(model, this.LoginUser); } result.IsSuccess = true; } catch (Exception ex) { result.Message = ex.Message; return(Content(JsonHelper.JsonSerializer(result))); } conn.Close(); } } return(Content(JsonHelper.JsonSerializer(result))); }
public ActionResult ImportMachine(int type) { var result = new ImportUploadResult() { IsSuccess = false }; var invalidlist = new List <InvalidData>(); StringBuilder strbuild = new StringBuilder(); string FileName; string savePath; HttpPostedFileBase file = Request.Files["file"]; if (file == null || file.ContentLength <= 0) { result.Message = "please choose file"; return(Content(JsonHelper.JsonSerializer(result))); } else { string fileName = Path.GetFileName(file.FileName); int filesize = file.ContentLength; //获取上传文件的大小单位为字节byte string fileEx = Path.GetExtension(fileName); //获取上传文件的扩展名 string NoFileName = Path.GetFileNameWithoutExtension(fileName); //获取无扩展名的文件名 int Maxsize = 4000 * 1024; //定义上传文件的最大空间大小为4M string FileType = ".xls,.xlsx"; //定义上传文件的类型字符串 FileName = NoFileName + fileEx; if (!FileType.Contains(fileEx)) { result.Message = "please upload .xls and .xlsx"; return(Content(JsonHelper.JsonSerializer(result))); } if (filesize >= Maxsize) { result.Message = string.Format("file size can't big than {0}", Maxsize); return(Content(JsonHelper.JsonSerializer(result))); } string path = Server.MapPath("~/App_Data/uploads"); savePath = Path.Combine(path, FileName); file.SaveAs(savePath); } string strConn; strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties=Excel 12.0;"; using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn); DataSet myDataSet = new DataSet(); try { myCommand.Fill(myDataSet, "ExcelInfo"); } catch (Exception ex) { result.Message = ex.Message; return(Content(JsonHelper.JsonSerializer(result))); } DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable(); try { for (int i = 0; i < table.Rows.Count; i++) { var datevalid1 = false; var datevalid2 = false; var invalid = new InvalidData(); if (!string.IsNullOrEmpty(table.Rows[i][6].ToString())) { datevalid1 = DataConvertHelper.IsDateTime(table.Rows[i][6].ToString()); if (!datevalid1) { invalid.Key = table.Rows[i][2].ToString(); invalid.Value1 = table.Rows[i][6].ToString(); } } if (!string.IsNullOrEmpty(table.Rows[i][12].ToString())) { datevalid2 = DataConvertHelper.IsDateTime(table.Rows[i][12].ToString()); if (!datevalid2) { invalid.Key = table.Rows[i][2].ToString(); invalid.Value2 = table.Rows[i][12].ToString(); } } if (!datevalid1 && !datevalid2) { invalidlist.Add(invalid); continue; } var model = new MaToolModel(); model.BMClassification = type; model.BMEquipmentName = table.Rows[i][0].ToString(); model.BMEquipmentNo = table.Rows[i][1].ToString(); model.BMFixtureNo = table.Rows[i][2].ToString(); model.BMType = table.Rows[i][3].ToString(); model.BMSerialNumber = table.Rows[i][4].ToString(); model.BMQuantity = DataConvertHelper.ToInt(table.Rows[i][5].ToString(), 0); model.BMManufacturedDate = table.Rows[i][6].ToString(); model.BMPower = table.Rows[i][7].ToString(); model.BMOutlineDimension = table.Rows[i][8].ToString(); model.BMAbility = table.Rows[i][9].ToString(); model.BMNeedPressureAir = DataConvertHelper.GetYesOrNoValue(table.Rows[i][10].ToString()); model.BMNeedCoolingWater = DataConvertHelper.GetYesOrNoValue(table.Rows[i][11].ToString()); model.BMIncomingDate = table.Rows[i][12].ToString(); model.BMRemarks = table.Rows[i][13].ToString(); var inserResult = MaterialBusiness.SaveMaTool(model, LoginUser); //if (type != 4) //{ // MachineTool(type, table, i); //} //else { // MaterialTool(type, table, i); //} } result.invalidData = invalidlist; result.IsSuccess = true; } catch (Exception ex) { result.Message = ex.Message; return(Content(JsonHelper.JsonSerializer(result))); } conn.Close(); } return(Content(JsonHelper.JsonSerializer(result))); }
/// <summary> /// Test /// </summary> /// <param name="file"></param> /// <returns></returns> public ActionResult UploadMaterialFiles() { var result = new ResultInfoModel() { IsSuccess = false }; StringBuilder strbuild = new StringBuilder(); string FileName; string savePath; HttpPostedFileBase file = Request.Files["file"]; if (file == null || file.ContentLength <= 0) { result.Message = "please choose file"; return(Content(JsonHelper.JsonSerializer(result))); } else { string fileName = Path.GetFileName(file.FileName); int filesize = file.ContentLength; //获取上传文件的大小单位为字节byte string fileEx = Path.GetExtension(fileName); //获取上传文件的扩展名 string NoFileName = Path.GetFileNameWithoutExtension(fileName); //获取无扩展名的文件名 int Maxsize = 4000 * 1024; //定义上传文件的最大空间大小为4M string FileType = ".xls,.xlsx"; //定义上传文件的类型字符串 FileName = NoFileName + fileEx; if (!FileType.Contains(fileEx)) { result.Message = "please upload .xls and .xlsx"; return(Content(JsonHelper.JsonSerializer(result))); } if (filesize >= Maxsize) { result.Message = string.Format("file size can't big than {0}", Maxsize); return(Content(JsonHelper.JsonSerializer(result))); } string path = Server.MapPath("~/App_Data/uploads"); savePath = Path.Combine(path, FileName); file.SaveAs(savePath); string strConn; strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties=Excel 12.0;"; using (OleDbConnection conn = new OleDbConnection(strConn)) { conn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn); DataSet myDataSet = new DataSet(); try { myCommand.Fill(myDataSet, "ExcelInfo"); } catch (Exception ex) { result.Message = ex.Message; return(Content(JsonHelper.JsonSerializer(result))); } DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable(); try { for (int i = 0; i < table.Rows.Count; i++) { MaterialInfoModel model = new MaterialInfoModel(); model.MIProcessType = table.Rows[i][0].ToString(); model.MICustomer = table.Rows[i][1].ToString(); model.MISapPN = table.Rows[i][2].ToString(); model.MIProductName = table.Rows[i][3].ToString(); model.MIInjectionMC = table.Rows[i][4].ToString(); model.MICustomerPN = table.Rows[i][5].ToString(); model.MICavity = DataConvertHelper.ToInt(table.Rows[i][6].ToString(), 0); model.MICycletime = DataConvertHelper.ToDecimal(table.Rows[i][7].ToString(), 0); model.MICycletimeCav = DataConvertHelper.ToDecimal(table.Rows[i][8].ToString(), 0); model.MIStandardHeadcount = DataConvertHelper.ToInt(table.Rows[i][9].ToString(), 0); model.MTStandardScrap = table.Rows[i][10].ToString(); model.MIMaterialPN = table.Rows[i][11].ToString(); model.MICavityG = DataConvertHelper.ToDecimal(table.Rows[i][12].ToString(), 0); model.MIMoldNo = table.Rows[i][13].ToString(); model.MIAssAC = table.Rows[i][14].ToString(); model.MIWorkOrder = table.Rows[i][15].ToString(); MaterialBusiness.SaveMaterial(model, this.LoginUser); var insertResult = MaterialBusiness.SaveMaterial(model, LoginUser); } result.IsSuccess = true; } catch (Exception ex) { result.Message = ex.Message; return(Content(JsonHelper.JsonSerializer(result))); } conn.Close(); } return(Content(JsonHelper.JsonSerializer(result))); } }
public List <UserView> SearchUser(UserSearchViewModel search, out int totalCount) { var list = new List <UserView>(); var sql = new StringBuilder(); var countSql = new StringBuilder(); var whereSql = new StringBuilder(); whereSql.Append(" WHERE 1=1 AND A.BUIsValid=1"); if (!string.IsNullOrEmpty(search.Name)) { whereSql.Append(string.Format(" AND A.BUName LIKE N'%{0}%' OR A.BUEnglishName LIKE '%{0}%'", search.Name)); } if (!string.IsNullOrEmpty(search.DepartName)) { whereSql.Append(string.Format(" AND A.BUDepartName LIKE '%{0}%'", search.DepartName)); } if (!string.IsNullOrEmpty(search.Position)) { whereSql.Append(string.Format(" AND A.BUPosition LIKE '%{0}%'", search.Position)); } if (!string.IsNullOrEmpty(search.Account)) { whereSql.Append(string.Format(" AND B.BAAccount LIKE '%{0}%'", search.Account)); } sql.Append(string.Format(@" SELECT newTable.* FROM ( SELECT TOP ( {0} * {1} ) ROW_NUMBER() OVER (ORDER BY A.Id DESC) RowNum ,A.[Id] AS UserId ,A.[BUName] ,A.[BUJobNumber] ,A.[BUSex] ,A.[BUAvatars] ,A.[BUPhoneNum] ,A.[BUEmail] ,A.[BUDepartId] ,A.[BUTitle] ,A.[BUCreateUserNo] ,A.[BUCreateUserName] ,A.[BUCreateTime] ,A.[BUOperateUserNo] ,A.[BUOperateUserName] ,A.[BUOperateTime] ,A.[BUIsValid] ,A.[BUDepartName] ,A.[BUEnglishName] ,A.[BUPosition] ,A.[BUExtensionPhone] ,A.[BUMobilePhone] ,B.BAAccount AS Account FROM {2} A WiTH (NOLOCK) LEFT JOIN {3} B WITH (NOLOCK) ON A.Id=B.BAUserId AND B.BAIsValid=1 {4} ORDER BY A.BUIsValid DESC , A.BUCreateTime DESC) newTable WHERE newTable.RowNum>(({0}-1)*{1}) ", search.CurrentPage, search.PageSize, userTableName, accountTableName, whereSql.ToString())); countSql.Append(string.Format(@"SELECT COUNT(1) FROM ( SELECT A.[Id] ,A.[BUName] ,A.[BUJobNumber] ,A.[BUSex] ,A.[BUAvatars] ,A.[BUPhoneNum] ,A.[BUEmail] ,A.[BUDepartId] ,A.[BUTitle] ,A.[BUCreateUserNo] ,A.[BUCreateUserName] ,A.[BUCreateTime] ,A.[BUOperateUserNo] ,A.[BUOperateUserName] ,A.[BUOperateTime] ,A.[BUIsValid] ,A.[BUDepartName] ,A.[BUEnglishName] ,A.[BUPosition] ,A.[BUExtensionPhone] ,A.[BUMobilePhone] ,B.BAAccount AS Account FROM {0} A WiTH (NOLOCK) LEFT JOIN {1} B WITH (NOLOCK) ON A.Id=B.BAUserId AND B.BAIsValid=1 {2} )TEMP ", userTableName, accountTableName, whereSql.ToString())); var ds = ExecuteDataSet(CommandType.Text, sql.ToString()); totalCount = ExecuteCount(CommandType.Text, countSql.ToString()); if (ds != null && ds.Tables.Count > 0) { DataTable dt = new DataTable(); dt = ds.Tables[0]; list = DataConvertHelper.DataTableToList <UserView>(dt); } return(list); }