/// <summary> /// 更新 /// </summary> /// <param name="id"></param> /// <returns></returns> public ActionResult Edit(int id) { TeaCertificate data = teaService.Get(id); ViewBag.AreaCity = City; ViewBag.AreaProvince = Province; if (data != null) { return(View(new TeaViewModel { Id = data.Id, TeacherName = data.Name, Number = data.Number, City = string.IsNullOrEmpty(data.City) ? 0 : Convert.ToInt32(data.City), Province = string.IsNullOrEmpty(data.Province) ? 0 : Convert.ToInt32(data.Province), State = (int)data.State, Category = data.Category, Level = data.Level, Gender = data.Gender, HeadImg = data.HeadImg })); } else { return(View()); } }
/// <summary> /// 修改教师证书 /// </summary> /// <param name="teaInfo">教师证书信息</param> /// <returns>修改成功标识</returns> public bool UpdatTea(TeaCertificate teaInfo) { DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSql = @"Update TeaCertificate Set [Name]=@Name ,[UserId]=@UserId ,[HeadImg]=@HeadImg ,[Gender]=@Gender ,[Number]=@Number ,[State]=@State ,[Category]=@Category ,[Level]=@Level ,[Province]=@Province ,[City]=@City ,[County]=@County Where ID=@ID"; List <DbParameter> parametersList = new List <DbParameter>(); parametersList.Add(new SqlParameter("@ID", teaInfo.Id)); parametersList.Add(new SqlParameter("@UserId", teaInfo.UserId)); parametersList.Add(new SqlParameter("@Name", teaInfo.Name)); parametersList.Add(new SqlParameter("@HeadImg", teaInfo.HeadImg)); parametersList.Add(new SqlParameter("@Gender", (int)teaInfo.Gender)); parametersList.Add(new SqlParameter("@Number", teaInfo.Number)); parametersList.Add(new SqlParameter("@State", (int)teaInfo.State)); parametersList.Add(new SqlParameter("@Category", (int)teaInfo.Category)); parametersList.Add(new SqlParameter("@Province", teaInfo.Province)); parametersList.Add(new SqlParameter("@City", teaInfo.City)); parametersList.Add(new SqlParameter("@County", teaInfo.County)); parametersList.Add(new SqlParameter("@Level", (int)teaInfo.Level)); return(dbHelper.ExecuteNonQuery(strSql, parametersList) > 0); }
private List <TeaCertificate> GetSheetData(ISheet sheet) { List <TeaCertificate> teaCertList = null; const int minrownum = 1;//最小行数,如果小于1行证明sheet无数据 if (sheet != null && sheet.LastRowNum >= minrownum) { int startrownum = 1;//从第2行开始取数据(row和cell从0开始) int endrownum = sheet.LastRowNum; teaCertList = new List <TeaCertificate>(); TeaCertificate teaCertInfo = null; for (int index = startrownum; index <= endrownum; index++) { IRow row = sheet.GetRow(index); teaCertInfo = new TeaCertificate() { Name = Regex.Replace(CellSwitch(row.GetCell(0)), @"\s", ""), Gender = CellSwitch(row.GetCell(1)).Equals("男") ? Gender.Male : Gender.Female, Level = CellSwitch(row.GetCell(2)).Equals("特级教师") ? LevelType.Super : CellSwitch(row.GetCell(2)).Equals("高级教师") ? LevelType.Senior : LevelType.General, Number = Regex.Replace(CellSwitch(row.GetCell(3)), @"\s", ""), State = PublishState.Lower, Province = Constant.DEFAULT_PROVINCE, City = string.IsNullOrWhiteSpace(CellSwitch(row.GetCell(4))) ? "" : City.Where(g => g.Value.Equals(CellSwitch(row.GetCell(4)))).FirstOrDefault().Key + "", Category = CellSwitch(row.GetCell(5)).Equals("师资认证") ? CertificateType.Train : CertificateType.Prize }; teaCertList.Add(teaCertInfo); } } return(teaCertList); }
/// <summary> /// 添加教师证书 /// </summary> /// <param name="teaInfo">教师证书信息</param> /// <returns>添加成功标识</returns> public bool AddTea(TeaCertificate teaInfo) { DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSql = @"if exists(select Id from TeaCertificate where Number=@Number) begin update TeaCertificate set UserId=@UserId,Name=@Name,Gender=@Gender,HeadImg=@HeadImg,State=@State,Category=@Category,Level=@Level,Province=@Province,City=@City,County=@County,CreateDate=getdate(),IsDeleted=0 where Number=@Number end else begin Insert Into TeaCertificate(UserId, Name, Gender, HeadImg, Number, State, Category, Level, Province, City, County) Values(@UserId, @Name, @Gender, @HeadImg, @Number, @State, @Category, @Level, @Province, @City, @County) end "; List <DbParameter> parametersList = new List <DbParameter>(); parametersList.Add(new SqlParameter("@UserId", teaInfo.UserId)); parametersList.Add(new SqlParameter("@Name", teaInfo.Name)); parametersList.Add(new SqlParameter("@Gender", (int)teaInfo.Gender)); parametersList.Add(new SqlParameter("@HeadImg", teaInfo.HeadImg)); parametersList.Add(new SqlParameter("@Number", teaInfo.Number)); parametersList.Add(new SqlParameter("@State", (int)teaInfo.State)); parametersList.Add(new SqlParameter("@Category", (int)teaInfo.Category)); parametersList.Add(new SqlParameter("@Level", (int)teaInfo.Level)); parametersList.Add(new SqlParameter("@Province", teaInfo.Province)); parametersList.Add(new SqlParameter("@City", teaInfo.City)); parametersList.Add(new SqlParameter("@County", teaInfo.County)); return(dbHelper.ExecuteNonQuery(strSql, parametersList) > 0); }
/// <summary> /// 从游标中读取数据 /// </summary> /// <param name="reader"></param> /// <returns></returns> private TeaCertificate GetTeaFromReader(DbDataReader reader) { TeaCertificate teaInfo = new TeaCertificate(); teaInfo.Id = Convert.ToInt32(reader["Id"]); teaInfo.UserId = Convert.ToInt32(reader["UserId"]); teaInfo.Number = reader["Number"].ToString(); teaInfo.Name = reader["Name"].ToString(); teaInfo.Gender = (Gender)Convert.ToInt32(reader["Gender"]); teaInfo.State = (PublishState)reader["State"]; teaInfo.Category = (CertificateType)reader["Category"]; teaInfo.Level = (LevelType)reader["Level"]; teaInfo.UserName = reader["UserName"].ToString(); if (ReaderExists(reader, "HeadImg") && DBNull.Value != reader["HeadImg"]) { teaInfo.HeadImg = reader["HeadImg"].ToString(); } if (ReaderExists(reader, "Province") && DBNull.Value != reader["Province"]) { teaInfo.Province = reader["Province"].ToString(); } if (ReaderExists(reader, "City") && DBNull.Value != reader["City"]) { teaInfo.City = reader["City"].ToString(); } if (ReaderExists(reader, "County") && DBNull.Value != reader["County"]) { teaInfo.County = reader["County"].ToString(); } teaInfo.CreateDate = Convert.ToDateTime(reader["CreateDate"]); return(teaInfo); }
public bool Add(TeaCertificate teaInfo) { //参数验证 if (teaInfo == null) { ErrorMsg = ErrorCode.ParameterNull; return(false); } return(_certificateProvider.AddTea(teaInfo)); }
public TeaCertificate Get(int id) { //参数验证 if (id < 1) { ErrorMsg = ErrorCode.ParameterNull; return(null); } //数据获取 TeaCertificate teaInfo = _certificateProvider.GetTea(id); return(teaInfo); }
public TeaCertificate GetTeaByNumber(string number) { TeaCertificate teaInfo = null; DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSql = @"SELECT tc.Id, tc.UserId, tc.Name, tc.Gender, tc.HeadImg, tc.Number,u.Name as UserName, tc.State, tc.Category, tc.Level, tc.Province, tc.City, tc.County, tc.CreateDate from TeaCertificate tc inner join [User] u on tc.UserId=u.Id where tc.IsDeleted=0 and tc.Number=@Number"; List <DbParameter> parametersList = new List <DbParameter>(); parametersList.Add(new SqlParameter("@Number", number)); using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parametersList)) { while (reader.Read()) { teaInfo = GetTeaFromReader(reader); } } return(teaInfo); }
public JsonResult Edit(TeaViewModel model) { ResultRetrun rmodel = new ResultRetrun(); if (ModelState.IsValid) { TeaCertificate data = teaService.Get(model.Id); data.Number = model.Number; data.Name = model.TeacherName; data.Level = model.Level; data.Category = model.Category; data.State = (PublishState)model.State; data.Gender = model.Gender; data.City = model.City.ToString(); data.Province = model.Province < 1 ? Constant.DEFAULT_PROVINCE : model.Province.ToString(); data.HeadImg = model.HeadImg; rmodel.isSuccess = teaService.Update(data); } return(Json(rmodel)); }
/// <summary> /// 获取所有证书信息 /// </summary> /// <param name="condition">筛选条件</param> /// <returns>证书集合</returns> public List <TeaCertificate> GetTeaAll(FilterEntityModel condition) { string whereSort = string.Empty; if (condition != null) { condition.DefaultSort = SortType.Desc; whereSort = condition.Where + condition.OrderBy; } List <TeaCertificate> teaList = null; DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSql = @"SELECT tc.Id, tc.UserId, tc.Name, tc.Gender, tc.HeadImg, tc.Number,u.Name as UserName, tc.State, tc.Category, tc.Level, tc.Province, tc.City, tc.County, tc.CreateDate from TeaCertificate tc inner join [User] u on tc.UserId=u.Id where tc.IsDeleted=0 " + whereSort; IList <DbParameter> parameList = null; if (condition != null && condition.SqlParList.Count > 0) { parameList = new List <DbParameter>(); foreach (var item in condition.SqlParList) { parameList.Add(new SqlParameter(item.Key, item.Value)); } } using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList)) { teaList = new List <TeaCertificate>(); TeaCertificate teaInfo = null; while (reader.Read()) { teaInfo = GetTeaFromReader(reader); teaList.Add(teaInfo); } } return(teaList); }
public JsonResult Add(TeaViewModel model) { ResultRetrun rmodel = new ResultRetrun(); if (ModelState.IsValid) { TeaCertificate teaModel = new TeaCertificate() { Number = model.Number, Name = model.TeacherName, Level = model.Level, Category = model.Category, State = (PublishState)model.State, Gender = model.Gender, City = model.City.ToString(), Province = model.Province.ToString(), UserId = GetAccount().Id, HeadImg = model.HeadImg }; rmodel.isSuccess = teaService.Add(teaModel); } return(Json(rmodel)); }
public List <TeaCertificate> GetTeaPage(FilterEntityModel condition, out int totalNum) { totalNum = 0; condition.DefaultSort = SortType.Desc; condition.SortTbAsName = Constant.TEA_CERTIFICATE_AS_NAME; string sort = condition.OrderBy; string asSort = condition.AsOrderBy; string where = condition.Where; List <TeaCertificate> teaList = null; DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSqlQuery = @"select count(tc.ID) from [TeaCertificate] tc inner join [User] u on tc.UserId=u.Id where tc.IsDeleted=0 " + where;//查询有多少条记录 IList <DbParameter> parameList = new List <DbParameter>(); parameList.Add(new SqlParameter("@pageSize", condition.PageSize)); parameList.Add(new SqlParameter("@pageIndex", condition.PageIndex)); if (condition.SqlParList.Count > 0) { foreach (var item in condition.SqlParList) { parameList.Add(new SqlParameter(item.Key, item.Value)); } } totalNum = Convert.ToInt32(dbHelper.ExecuteScalar(strSqlQuery, parameList)); string strSql = @"SELECT [ID] ,[UserId] ,[Name] ,[Gender] ,[HeadImg] ,[Number] ,[State] ,[Category] ,[Level] ,[Province] ,[City] ,[County] ,[CreateDate] ,[UserName] FROM (select top (@pageSize*@pageIndex) tc.[ID] ,tc.[UserId] ,tc.[Name] ,tc.[Gender] ,tc.[HeadImg] ,tc.[Number] ,tc.[State] ,tc.[Category] ,tc.[Level] ,tc.[Province] ,tc.[City] ,tc.[County] ,tc.[CreateDate] ,u.[Name] as UserName ,ROW_NUMBER() over(" + asSort + ") as num from [TeaCertificate] tc inner join [User] u on tc.UserId=u.Id where tc.IsDeleted=0 " + where + ") as t where num between (@pageIndex - 1) * @pageSize + 1 and @pageIndex*@pageSize " + sort; using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList)) { teaList = new List <TeaCertificate>(); TeaCertificate teaInfo = null; while (reader.Read()) { teaInfo = GetTeaFromReader(reader); teaList.Add(teaInfo); } } return(teaList); }