/// <summary>
/// 从游标中读取数据
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private MemberUnit GetMemberUnitFromReader(DbDataReader reader)
{
MemberUnit MemberUnitInfo = new MemberUnit();
MemberUnitInfo.Id = Convert.ToInt32(reader["Id"]);
MemberUnitInfo.Name = reader["Name"].ToString();
MemberUnitInfo.Star = Convert.ToInt32(reader["Star"]);
MemberUnitInfo.Number = reader["Number"].ToString();
MemberUnitInfo.State = Convert.ToInt32(reader["State"]) < 1 ? PublishState.Lower : PublishState.Upper;
MemberUnitInfo.Category = Convert.ToInt32(reader["Category"]);
MemberUnitInfo.Province = reader["Province"].ToString();
MemberUnitInfo.City = reader["City"].ToString();
MemberUnitInfo.County = reader["County"].ToString();
MemberUnitInfo.CreateDate = Convert.ToDateTime(reader["CreateDate"]);
if (ReaderExists(reader, "CategoryName") && DBNull.Value != reader["CategoryName"])
{
MemberUnitInfo.CategoryName = reader["CategoryName"].ToString();
}
if (ReaderExists(reader, "HeadImg") && DBNull.Value != reader["HeadImg"])
{
MemberUnitInfo.HeadImg = reader["HeadImg"].ToString();
}
if (ReaderExists(reader, "Description") && DBNull.Value != reader["Description"])
{
MemberUnitInfo.Description = reader["Description"].ToString();
}
if (ReaderExists(reader, "Synopsis") && DBNull.Value != reader["Synopsis"])
{
MemberUnitInfo.Synopsis = reader["Synopsis"].ToString();
}
return(MemberUnitInfo);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult Edit(int id)
{
MemberUnit data = muService.Get(id);
InitData();
if (data != null)
{
MemberUnitViewModel model = new MemberUnitViewModel();
model.Id = data.Id;
model.MemberUnitName = data.Name;
model.CategoryName = data.CategoryName;
model.UserName = data.UserName;
model.State = (int)data.State;
model.UserId = data.UserId;
model.Description = data.Description;
model.HeadImg = data.HeadImg;
model.SmallHeadImg = GetThumb(data.HeadImg);
model.Star = data.Star;
model.City = string.IsNullOrEmpty(data.City) ? 0 : Convert.ToInt32(data.City);
model.Province = string.IsNullOrEmpty(data.Province) ? 0 : Convert.ToInt32(data.Province);
model.Number = data.Number;
model.Category = data.Category;
#region 类别可用判断 预留
//if (cdEnabledList != null && cdEnabledList.Count > 0 && cdEnabledList.Where(g => g.Id == data.Category).Count() > 0)
//{
// model.Category = data.Category;
//}
#endregion
return(View(model));
}
return(View());
}
/// <summary>
/// 添加会员单位
/// </summary>
/// <param name="memberUnitInfo">会员单位信息</param>
/// <returns>添加成功标识</returns>
public bool Add(MemberUnit memberUnitInfo)
{
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"if exists(select Id from MemberUnit where Number=@Number)
begin
update MemberUnit set Name=@Name,HeadImg=@HeadImg,Star=@Star,State=@State,Category=@Category,Description=@Description,Synopsis=@Synopsis,Province=@Province,City=@City,County=@County,UserId=@UserId,CreateDate=getdate(),IsDeleted=0 where Number=@Number
end
else
begin
Insert Into MemberUnit(Name, HeadImg, Star, Number, State, Category, Description,Synopsis, Province, City, County,UserId)
Values(@Name, @HeadImg, @Star, @Number, @State, @Category, @Description,@Synopsis, @Province, @City, @County,@UserId)
end ";
List <DbParameter> parametersList = new List <DbParameter>();
parametersList.Add(new SqlParameter("@Name", memberUnitInfo.Name));
parametersList.Add(new SqlParameter("@HeadImg", memberUnitInfo.HeadImg));
parametersList.Add(new SqlParameter("@Star", memberUnitInfo.Star));
parametersList.Add(new SqlParameter("@Number", memberUnitInfo.Number));
parametersList.Add(new SqlParameter("@State", (int)memberUnitInfo.State));
parametersList.Add(new SqlParameter("@Category", memberUnitInfo.Category));
parametersList.Add(new SqlParameter("@Description", memberUnitInfo.Description));
parametersList.Add(new SqlParameter("@Synopsis", memberUnitInfo.Synopsis));
parametersList.Add(new SqlParameter("@Province", memberUnitInfo.Province));
parametersList.Add(new SqlParameter("@City", memberUnitInfo.City));
parametersList.Add(new SqlParameter("@County", memberUnitInfo.County));
parametersList.Add(new SqlParameter("@UserId", memberUnitInfo.UserId));
return(dbHelper.ExecuteNonQuery(strSql, parametersList) > 0);
}
public ActionResult Detail(int id)
{
MemberUnit data = muService.Get(id);
if (data != null)
{
return(View(new MemberUnitViewModel
{
Id = data.Id,
MemberUnitName = data.Name,
CategoryName = data.CategoryName,
UserName = data.UserName,
State = (int)data.State,
Category = data.Category,
UserId = data.UserId,
Description = data.Description,
HeadImg = data.HeadImg,
SmallHeadImg = GetThumb(data.HeadImg),
Star = data.Star,
City = string.IsNullOrEmpty(data.City) ? 0 : Convert.ToInt32(data.City),
Province = string.IsNullOrEmpty(data.Province) ? 0 : Convert.ToInt32(data.Province),
Number = data.Number,
CreateTime = data.CreateDate.ToString("yyyy-MM-dd HH;MM")
}));
}
else
{
return(View());
}
}
public bool Add(MemberUnit memberUnitInfo)
{
//参数验证
if (memberUnitInfo == null)
{
ErrorMsg = ErrorCode.ParameterNull;
return(false);
}
memberUnitInfo.Synopsis = DisposeHtmlStr(memberUnitInfo.Description);
return(_memberUnitProvider.Add(memberUnitInfo));
}
public MemberUnit Get(int id)
{
//参数验证
if (id < 1)
{
ErrorMsg = ErrorCode.ParameterNull;
return(null);
}
//数据获取
MemberUnit memberUnitInfo = _memberUnitProvider.Get(id);
return(memberUnitInfo);
}
/// <summary>
/// 根据ID获取会员单位单位信息
/// </summary>
/// <param name="id">会员单位ID</param>
/// <returns>会员单位信息</returns>
public MemberUnit Get(int id)
{
MemberUnit MemberUnitInfo = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT m.Id, m.Name, m.HeadImg, m.Star, m.Number, m.State, m.Category,cd.name as CategoryName,Description, Province, City, County, m.CreateDate,m.Synopsis from MemberUnit m left join CategoryDictionary cd on m.category=cd.id where m.IsDeleted=0 and m.id=@Id ";
List <DbParameter> parametersList = new List <DbParameter>();
parametersList.Add(new SqlParameter("@Id", id));
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parametersList))
{
while (reader.Read())
{
MemberUnitInfo = GetMemberUnitFromReader(reader);
}
}
return(MemberUnitInfo);
}
private static IEnumerable <IUnit> ToUnits(XDocument document, XElement docElement)
{
var assemblyNode = docElement.Element("assembly");
var au = new AssemblyUnit(document, assemblyNode);
// member units
var memberUnits = docElement
.Element("members")
.Elements("member")
.Select(element => new MemberUnit(document, element))
.Where(member => member.Kind != MemberKind.NotSupported)
.GroupBy(unit => unit.TypeName)
.Select(group => MemberUnit.ComplementType(document, group))
.SelectMany(group => group)
.OrderBy(member => member, MemberUnit.Comparer)
.ToList <IUnit>();
memberUnits.Insert(0, au);
return(memberUnits.ToArray());
}
public JsonResult Edit(MemberUnitViewModel model)
{
ResultRetrun rmodel = new ResultRetrun();
if (ModelState.IsValid)
{
MemberUnit data = muService.Get(model.Id);
data.Name = model.MemberUnitName;
data.Description = !string.IsNullOrEmpty(model.Description) ? model.Description.Replace("\r\n", "") : string.Empty;
data.Category = model.Category;
data.State = (PublishState)model.State;
data.HeadImg = model.HeadImg;
data.Star = model.Star;
data.City = model.City.ToString();
data.Province = model.Province < 1 ? Constant.DEFAULT_PROVINCE : model.Province.ToString();
data.Number = model.Number;
rmodel.isSuccess = muService.Update(data);
}
return(Json(rmodel));
}
/// <summary>
/// 获取所有会员单位信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <returns>会员集合</returns>
public List <MemberUnit> GetAll(FilterEntityModel condition)
{
string whereSort = string.Empty;
if (condition != null)
{
condition.DefaultSort = SortType.Desc;
whereSort = condition.Where + condition.OrderBy;
}
List <MemberUnit> MemberUnitList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"SELECT m.Id, m.Name, m.HeadImg, m.Star, m.Number, m.State, m.Category,cd.name as CategoryName, Province, City, County, m.CreateDate,m.Synopsis from MemberUnit m left join CategoryDictionary cd on m.category=cd.id where m.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))
{
MemberUnitList = new List <MemberUnit>();
MemberUnit MemberUnitInfo = null;
while (reader.Read())
{
MemberUnitInfo = GetMemberUnitFromReader(reader);
MemberUnitList.Add(MemberUnitInfo);
}
}
return(MemberUnitList);
}
private List <MemberUnit> GetSheetData(ISheet sheet)
{
List <CategoryDictionary> cdAllList = cdService.GetAll(CategoryType.Member);
List <MemberUnit> memberCertList = null;
const int minrownum = 1;//最小行数，如果小于1行证明sheet无数据
if (sheet != null && sheet.LastRowNum >= minrownum)
{
int startrownum = 1;//从第2行开始取数据（row和cell从0开始）
int endrownum = sheet.LastRowNum;
memberCertList = new List <MemberUnit>();
MemberUnit memberCertInfo = null;
for (int index = startrownum; index <= endrownum; index++)
{
IRow row = sheet.GetRow(index);
int star = 0;
int.TryParse(Regex.Replace(CellSwitch(row.GetCell(2)), @"\s", ""), out star);
CategoryDictionary cdict = string.IsNullOrEmpty(CellSwitch(row.GetCell(4))) ? null : cdAllList.Find(g => g.Name.Equals(CellSwitch(row.GetCell(4))));
memberCertInfo = new MemberUnit()
{
Name = Regex.Replace(CellSwitch(row.GetCell(0)), @"\s", ""),
Number = Regex.Replace(CellSwitch(row.GetCell(1)), @"\s", ""),
Star = star,
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 = cdict != null ? cdict.Id : 0
};
memberCertList.Add(memberCertInfo);
}
}
return(memberCertList);
}
/// <summary>
/// 修改会员单位
/// </summary>
/// <param name="memberUnitInfo">会员单位信息</param>
/// <returns>修改成功标识</returns>
public bool Update(MemberUnit memberUnitInfo)
{
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSql = @"Update MemberUnit
Set [Name]=@Name
,[HeadImg]=@HeadImg
,[Star]=@Star
,[Number]=@Number
,[State]=@State
,[Category]=@Category
,[Description]=@Description
,[Synopsis]=@Synopsis
,[Province]=@Province
,[City]=@City
,[County]=@County
,[UserId]=@UserId
Where ID=@ID";
List <DbParameter> parametersList = new List <DbParameter>();
parametersList.Add(new SqlParameter("@ID", memberUnitInfo.Id));
parametersList.Add(new SqlParameter("@Name", memberUnitInfo.Name));
parametersList.Add(new SqlParameter("@HeadImg", memberUnitInfo.HeadImg));
parametersList.Add(new SqlParameter("@Star", memberUnitInfo.Star));
parametersList.Add(new SqlParameter("@Number", memberUnitInfo.Number));
parametersList.Add(new SqlParameter("@State", (int)memberUnitInfo.State));
parametersList.Add(new SqlParameter("@Category", memberUnitInfo.Category));
parametersList.Add(new SqlParameter("@Description", memberUnitInfo.Description));
parametersList.Add(new SqlParameter("@Province", memberUnitInfo.Province));
parametersList.Add(new SqlParameter("@City", memberUnitInfo.City));
parametersList.Add(new SqlParameter("@County", memberUnitInfo.County));
parametersList.Add(new SqlParameter("@UserId", memberUnitInfo.UserId));
parametersList.Add(new SqlParameter("@Synopsis", memberUnitInfo.Synopsis));
return(dbHelper.ExecuteNonQuery(strSql, parametersList) > 0);
}
public JsonResult Add(MemberUnitViewModel model)
{
ResultRetrun rmodel = new ResultRetrun();
if (ModelState.IsValid)
{
MemberUnit downModel = new MemberUnit()
{
Name = model.MemberUnitName,
Description = !string.IsNullOrEmpty(model.Description) ? model.Description.Replace("\r\n", "") : string.Empty,
Category = model.Category,
State = (PublishState)model.State,
HeadImg = model.HeadImg,
Star = model.Star,
UserId = GetAccount().Id,
Number = model.Number,
City = model.City.ToString(),
Province = model.Province.ToString()
};
rmodel.isSuccess = muService.Add(downModel);
}
return(Json(rmodel));
}
private static string GetIndentation(MemberUnit memberUnit) =>
memberUnit.Kind == MemberKind.Type ? string.Empty : " ";
private static string ToMarkdown(MemberUnit memberUnit) =>
$"{GetIndentation(memberUnit)}- {memberUnit.Link}";
/// <summary>
/// 获取会员单位分页信息
/// </summary>
/// <param name="condition">筛选条件</param>
/// <param name="totalNum">总条数</param>
/// <returns>会员集合</returns>
public List <MemberUnit> GetPage(FilterEntityModel condition, out int totalNum)
{
totalNum = 0;
condition.DefaultSort = SortType.Desc;
condition.SortTbAsName = Constant.MEMBER_UNIT_AS_NAME;
string sort = condition.OrderBy;
string asSort = condition.AsOrderBy;
string where = condition.Where;
List <MemberUnit> MemberUnitList = null;
DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer);
string strSqlQuery = @"select count(m.ID) from [MemberUnit] m left join CategoryDictionary cd on m.Category=cd.id where m.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]
,[Name]
,[HeadImg]
,[Star]
,[Number]
,[State]
,[Category]
,[CategoryName]
,[Province]
,[City]
,[County]
,[CreateDate]
,[Synopsis]
FROM (select top (@pageSize*@pageIndex) m.[ID]
,m.[Name]
,m.[HeadImg]
,m.[Star]
,m.[Number]
,m.[State]
,m.[Category]
,cd.[Name] as CategoryName
,m.[Province]
,m.[City]
,m.[County]
,m.[CreateDate]
,m.[Synopsis]
,ROW_NUMBER() over(" + asSort + ") as num from [MemberUnit] m left join CategoryDictionary cd on m.Category=cd.id where m.IsDeleted=0 " + where + ") as t where num between (@pageIndex - 1) * @pageSize + 1 and @pageIndex*@pageSize " + sort;
using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList))
{
MemberUnitList = new List <MemberUnit>();
MemberUnit MemberUnitInfo = null;
while (reader.Read())
{
MemberUnitInfo = GetMemberUnitFromReader(reader);
MemberUnitList.Add(MemberUnitInfo);
}
}
return(MemberUnitList);
}
private static string ToMarkdown(MemberUnit memberUnit) =>
$"{GetIndentation(memberUnit)}- {memberUnit.Link}";
private static string GetIndentation(MemberUnit memberUnit) => memberUnit.Kind == MemberKind.Type ? string.Empty : " ";
