/// <summary>
/// 更新
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult Edit(int id)
{
StuCertificate data = stuService.Get(id);
ViewBag.AreaCity = City;
ViewBag.AreaProvince = Province;
if (data != null)
{
return(View(new StuViewModel
{
Id = data.Id,
StudentName = 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,
Gender = data.Gender,
HeadImg = data.HeadImg
}));
}
else
{
return(View());
}
}
/// <summary>
/// 修改学生证书
/// </summary>
/// <param name="stuInfo">学生证书信息</param>
/// <returns>修改成功标识</returns>
public bool UpdatStu(StuCertificate stuInfo)
{
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"Update StuCertificate
Set [Name]=@Name
,[UserId]=@UserId
,[HeadImg]=@HeadImg
,[Gender]=@Gender
,[Number]=@Number
,[State]=@State
,[Category]=@Category
,[Province]=@Province
,[City]=@City
,[County]=@County
Where ID=@ID";
List <DbParameter> parametersList = new List <DbParameter>();
parametersList.Add(new SqlParameter("@ID", stuInfo.Id));
parametersList.Add(new SqlParameter("@UserId", stuInfo.UserId));
parametersList.Add(new SqlParameter("@Name", stuInfo.Name));
parametersList.Add(new SqlParameter("@HeadImg", stuInfo.HeadImg));
parametersList.Add(new SqlParameter("@Gender", (int)stuInfo.Gender));
parametersList.Add(new SqlParameter("@Number", stuInfo.Number));
parametersList.Add(new SqlParameter("@State", (int)stuInfo.State));
parametersList.Add(new SqlParameter("@Category", (int)stuInfo.Category));
parametersList.Add(new SqlParameter("@Province", stuInfo.Province));
parametersList.Add(new SqlParameter("@City", stuInfo.City));
parametersList.Add(new SqlParameter("@County", stuInfo.County));
return(dbHelper.ExecuteNonQuery(strSql, parametersList) > 0);
}
private List <StuCertificate> GetSheetData(ISheet sheet)
{
List <StuCertificate> stuCertList = null;
const int minrownum = 1;//最小行数，如果小于1行证明sheet无数据
if (sheet != null && sheet.LastRowNum >= minrownum)
{
int startrownum = 1;//从第2行开始取数据（row和cell从0开始）
int endrownum = sheet.LastRowNum;
stuCertList = new List <StuCertificate>();
StuCertificate stuCertInfo = null;
for (int index = startrownum; index <= endrownum; index++)
{
IRow row = sheet.GetRow(index);
stuCertInfo = new StuCertificate()
{
Name = Regex.Replace(CellSwitch(row.GetCell(0)), @"\s", ""),
Gender = CellSwitch(row.GetCell(1)).Equals("男") ? Gender.Male : Gender.Female,
Number = Regex.Replace(CellSwitch(row.GetCell(2)), @"\s", ""),
State = PublishState.Lower,
Province = Constant.DEFAULT_PROVINCE,
City = string.IsNullOrWhiteSpace(CellSwitch(row.GetCell(3))) ? "" : City.Where(g => g.Value.Equals(CellSwitch(row.GetCell(3)))).FirstOrDefault().Key + "",
Category = CellSwitch(row.GetCell(4)).Equals("学员认证") ? CertificateType.Train : CertificateType.Prize
};
stuCertList.Add(stuCertInfo);
}
}
return(stuCertList);
}
/// <summary>
/// 添加学生证书
/// </summary>
/// <param name="stuInfo">学生证书信息</param>
/// <returns>添加成功标识</returns>
public bool AddStu(StuCertificate stuInfo)
{
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"if exists(select Id from StuCertificate where Number=@Number)
begin
update StuCertificate set UserId=@UserId,Name=@Name,Gender=@Gender,HeadImg=@HeadImg,State=@State,Category=@Category,Province=@Province,City=@City,County=@County,CreateDate=getdate(),IsDeleted=0 where Number=@Number
end
else
begin
Insert Into StuCertificate(UserId, Name, Gender, HeadImg, Number, State, Category, Province, City, County)
Values(@UserId, @Name, @Gender, @HeadImg, @Number, @State, @Category, @Province, @City, @County)
end ";
List <DbParameter> parametersList = new List <DbParameter>();
parametersList.Add(new SqlParameter("@UserId", stuInfo.UserId));
parametersList.Add(new SqlParameter("@Name", stuInfo.Name));
parametersList.Add(new SqlParameter("@Gender", (int)stuInfo.Gender));
parametersList.Add(new SqlParameter("@HeadImg", stuInfo.HeadImg));
parametersList.Add(new SqlParameter("@Number", stuInfo.Number));
parametersList.Add(new SqlParameter("@State", (int)stuInfo.State));
parametersList.Add(new SqlParameter("@Category", (int)stuInfo.Category));
parametersList.Add(new SqlParameter("@Province", stuInfo.Province));
parametersList.Add(new SqlParameter("@City", stuInfo.City));
parametersList.Add(new SqlParameter("@County", stuInfo.County));
return(dbHelper.ExecuteNonQuery(strSql, parametersList) > 0);
}
/// <summary>
/// 从游标中读取数据
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private StuCertificate GetStuFromReader(DbDataReader reader)
{
StuCertificate stuInfo = new StuCertificate();
stuInfo.Id = Convert.ToInt32(reader["Id"]);
stuInfo.UserId = Convert.ToInt32(reader["UserId"]);
stuInfo.Number = reader["Number"].ToString();
stuInfo.Name = reader["Name"].ToString();
stuInfo.Gender = (Gender)Convert.ToInt32(reader["Gender"]);
stuInfo.State = (PublishState)reader["State"];
stuInfo.Category = (CertificateType)reader["Category"];
stuInfo.UserName = reader["UserName"].ToString();
if (ReaderExists(reader, "HeadImg") && DBNull.Value != reader["HeadImg"])
{
stuInfo.HeadImg = reader["HeadImg"].ToString();
}
if (ReaderExists(reader, "Province") && DBNull.Value != reader["Province"])
{
stuInfo.Province = reader["Province"].ToString();
}
if (ReaderExists(reader, "City") && DBNull.Value != reader["City"])
{
stuInfo.City = reader["City"].ToString();
}
if (ReaderExists(reader, "County") && DBNull.Value != reader["County"])
{
stuInfo.County = reader["County"].ToString();
}
stuInfo.CreateDate = Convert.ToDateTime(reader["CreateDate"]);
return(stuInfo);
}
public bool Add(StuCertificate stuInfo)
{
//参数验证
if (stuInfo == null)
{
ErrorMsg = ErrorCode.ParameterNull;
return(false);
}
return(_certificateProvider.AddStu(stuInfo));
}
public StuCertificate Get(int id)
{
//参数验证
if (id < 1)
{
ErrorMsg = ErrorCode.ParameterNull;
return(null);
}
//数据获取
StuCertificate stuInfo = _certificateProvider.GetStu(id);
return(stuInfo);
}
public StuCertificate GetStuByNumber(string number)
{
StuCertificate stuInfo = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT sc.Id, sc.UserId, sc.Name, sc.Gender, sc.HeadImg, sc.Number,u.Name as UserName, sc.State, sc.Category, sc.Province, sc.City, sc.County, sc.CreateDate from StuCertificate sc inner join [User] u on sc.UserId=u.Id where sc.IsDeleted=0 and sc.Number=@Number";
List <DbParameter> parametersList = new List <DbParameter>();
parametersList.Add(new SqlParameter("@Number", number));
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parametersList))
{
while (reader.Read())
{
stuInfo = GetStuFromReader(reader);
}
}
return(stuInfo);
}
public JsonResult Edit(StuViewModel model)
{
ResultRetrun rmodel = new ResultRetrun();
if (ModelState.IsValid)
{
StuCertificate data = stuService.Get(model.Id);
data.Number = model.Number;
data.Name = model.StudentName;
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 = stuService.Update(data);
}
return(Json(rmodel));
}
/// <summary>
/// 获取所有证书信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <returns>证书集合</returns>
public List <StuCertificate> GetStuAll(FilterEntityModel condition)
{
string whereSort = string.Empty;
if (condition != null)
{
condition.DefaultSort = SortType.Desc;
whereSort = condition.Where + condition.OrderBy;
}
List <StuCertificate> stuList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT sc.Id, sc.UserId, sc.Name, sc.Gender, sc.HeadImg, sc.Number,u.Name as UserName, sc.State, sc.Category, sc.Province, sc.City, sc.County, sc.CreateDate from StuCertificate sc inner join [User] u on sc.UserId=u.Id where sc.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))
{
stuList = new List <StuCertificate>();
StuCertificate stuInfo = null;
while (reader.Read())
{
stuInfo = GetStuFromReader(reader);
stuList.Add(stuInfo);
}
}
return(stuList);
}
public JsonResult Add(StuViewModel model)
{
ResultRetrun rmodel = new ResultRetrun();
if (ModelState.IsValid)
{
StuCertificate stuModel = new StuCertificate()
{
Number = model.Number,
Name = model.StudentName,
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 = stuService.Add(stuModel);
}
return(Json(rmodel));
}
public List <StuCertificate> GetStuPage(FilterEntityModel condition, out int totalNum)
{
totalNum = 0;
condition.DefaultSort = SortType.Desc;
condition.SortTbAsName = Constant.STU_CERTIFICATE_AS_NAME;
string sort = condition.OrderBy;
string asSort = condition.AsOrderBy;
string where = condition.Where;
List <StuCertificate> stuList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSqlQuery = @"select count(sc.ID) from [StuCertificate] sc inner join [User] u on sc.UserId=u.Id where sc.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]
,[Province]
,[City]
,[County]
,[CreateDate]
,[UserName]
FROM (select top (@pageSize*@pageIndex) sc.[ID]
,sc.[UserId]
,sc.[Name]
,sc.[Gender]
,sc.[HeadImg]
,sc.[Number]
,sc.[State]
,sc.[Category]
,sc.[Province]
,sc.[City]
,sc.[County]
,sc.[CreateDate]
,u.[Name] as UserName
,ROW_NUMBER() over(" + asSort + ") as num from [StuCertificate] sc inner join [User] u on sc.UserId=u.Id where sc.IsDeleted=0 " + where + ") as t where num between (@pageIndex - 1) * @pageSize + 1 and @pageIndex*@pageSize " + sort;
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
stuList = new List <StuCertificate>();
StuCertificate stuInfo = null;
while (reader.Read())
{
stuInfo = GetStuFromReader(reader);
stuList.Add(stuInfo);
}
}
return(stuList);
}
