using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using CY.IDAL.Inquiry;
|
using CY.Model;
|
using System.Data.SqlClient;
|
using System.Data;
|
using System.Transactions;
|
using CY.Infrastructure.Common;
|
|
namespace CY.SQLDAL
|
{
|
/// <summary>
|
/// 品牌相关操作--SQL数据库操作
|
/// </summary>
|
public class BrandInfoDAL : IBrandInfoDAL
|
{
|
private Database _dataBase = null;
|
|
public BrandInfoDAL()
|
{
|
_dataBase = new Database();
|
}
|
|
public BrandInfoDAL(Database dataBase)
|
{
|
_dataBase = dataBase;
|
}
|
|
/// <summary>
|
/// 新增品牌
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.SysInquiry_BrandInfo trueModel = model as Model.SysInquiry_BrandInfo;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@BrandName",trueModel.BrandName),
|
new SqlParameter("@Status",trueModel.Status),
|
new SqlParameter("@OrderNum",trueModel.OrderNum),
|
new SqlParameter(){ParameterName="@KeyId", SqlDbType=SqlDbType.Int,Direction=ParameterDirection.Output}
|
};
|
try
|
{
|
_dataBase.Query("SysInquiry_BrandInfo_ADD", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 修改品牌
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.SysInquiry_BrandInfo trueModel = model as Model.SysInquiry_BrandInfo;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@BrandName",trueModel.BrandName),
|
new SqlParameter("@Status",trueModel.Status),
|
new SqlParameter("@KeyId",trueModel.KeyId),
|
new SqlParameter("@OrderNum",trueModel.OrderNum)
|
};
|
try
|
{
|
_dataBase.Query("SysInquiry_BrandInfo_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 删除品牌
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.SysInquiry_BrandInfo trueModel = model as Model.SysInquiry_BrandInfo;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@KeyId",trueModel.KeyId)
|
};
|
try
|
{
|
_dataBase.Query("SysInquiry_BrandInfo_Delete", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 分页返回品牌列表
|
/// </summary>
|
/// <param name="brandName">品牌名称</param>
|
/// <param name="pagination">分页参数</param>
|
/// <returns></returns>
|
public IEnumerable<Model.SysInquiry_BrandInfo> SelectModelPage(int paperTypeId, int paperId, string brandName, int status, Infrastructure.Query.Pagination pagination)
|
{
|
//string condition=string.Empty;
|
//condition = " 1=1 ";
|
//if(!string.IsNullOrEmpty(brandName))
|
//{
|
// condition +=" and BrandName like '%"+brandName+"%' ";
|
//}
|
//if (status != -1)
|
//{
|
// condition += " and Status="+status;
|
//}
|
//return _dataBase.SelectModelPage<SysInquiry_BrandInfo>(pagination, "*", "SysInquiry_BrandInfo", "OrderNum", "OrderNum", condition);
|
string selectTarget = @" t.* ";
|
string fromSouce = @" ( select distinct a.*,c.PaperName,e.OrderNum as paperTypeOrderNum,c.OrderNum AS PaperOrderNum,
|
CASE
|
WHEN d.KeyId IS NULL THEN 0
|
ELSE 1
|
END AS IsDefaultBrand
|
from
|
dbo.SysInquiry_BrandInfo a LEFT JOIN dbo.SysInquiry_BrandListByPaper b ON a.KeyId=b.BrandId
|
LEFT JOIN dbo.SysInquiry_PaperInfo c ON b.PaperId=c.PapeId and c.status='true'
|
LEFT JOIN dbo.SysInquiry_PaperInfo d ON a.KeyId=d.DefaultBrandId
|
left join SysInquiry_PaperType e on e.KeyId=c.PaperTypeId
|
where 1=1 ";
|
|
if (!string.IsNullOrEmpty(brandName))
|
{
|
fromSouce += " and a.BrandName like '%" + brandName + "%' ";
|
}
|
if (status != -1)
|
{
|
fromSouce += " and a.Status=" + status;
|
}
|
if (paperTypeId != -1)
|
{
|
fromSouce += " and c.PaperTypeId=" + paperTypeId;
|
}
|
|
if (paperId != -1)
|
{
|
fromSouce += " and b.PaperId=" + paperId;
|
}
|
fromSouce += " ) as t";
|
IEnumerable<Model.SysInquiry_BrandInfo> list = _dataBase.SelectModelPage<SysInquiry_BrandInfo>(pagination, selectTarget, fromSouce, "t.paperTypeOrderNum,t.PaperOrderNum,t.OrderNum", "paperTypeOrderNum,PaperOrderNum,OrderNum");
|
return list;
|
}
|
|
/// <summary>
|
/// 根据主键返回品牌实体
|
/// </summary>
|
/// <param name="keyid">主键ID</param>
|
/// <returns>品牌实体</returns>
|
public SysInquiry_BrandInfo SelectModelByKey(int keyid)
|
{
|
//string condition=string.Empty;
|
//condition=" KeyId="+keyid;
|
//IList<SysInquiry_BrandInfo> result = _dataBase.SelectModel<SysInquiry_BrandInfo>("*", "SysInquiry_BrandInfo", condition);
|
//return null == result || result.Count == 0 ? null : result[0];
|
|
string selectTarget = @"a.*,c.PaperName,c.PaperTypeId,c.PapeId,
|
CASE
|
WHEN d.KeyId IS NULL THEN 0
|
ELSE 1
|
END AS IsDefaultBrand";
|
string fromSouce = @" dbo.SysInquiry_BrandInfo a LEFT JOIN dbo.SysInquiry_BrandListByPaper b ON a.KeyId=b.BrandId
|
LEFT JOIN dbo.SysInquiry_PaperInfo c ON b.PaperId=c.PapeId
|
LEFT JOIN dbo.SysInquiry_PaperInfo d ON a.KeyId=d.DefaultBrandId ";
|
string condition = string.Empty;
|
condition = " a.KeyId=" + keyid;
|
IList<SysInquiry_BrandInfo> result = _dataBase.SelectModel<SysInquiry_BrandInfo>(selectTarget, fromSouce, condition);
|
return null == result || result.Count == 0 ? null : result[0];
|
}
|
|
/// <summary>
|
/// 判断是否有相同的品牌名称
|
/// </summary>
|
/// <param name="brandName"></param>
|
/// <returns></returns>
|
public bool IsExistsBrandName(string brandName, int PaperId)
|
{
|
string condition = string.Empty;
|
condition = " a.BrandName='" + brandName + "' and b.PaperId='" + PaperId + "'";
|
IList<SysInquiry_BrandInfo> result = _dataBase.SelectModel<SysInquiry_BrandInfo>(" a.* ", "SysInquiry_BrandInfo as a left join SysInquiry_BrandListByPaper as b on a.KeyId = b.BrandId", condition);
|
if (result != null && result.Count != 0)
|
{
|
return true;
|
}
|
else
|
{
|
return false;
|
}
|
}
|
|
/// <summary>
|
/// 批量删除品牌
|
/// </summary>
|
/// <param name="keyIds"></param>
|
/// <returns></returns>
|
public bool DeleteBrandInfoList(List<int> keyIds)
|
{
|
bool isSuccess = true;
|
using (TransactionScope scope = new TransactionScope())
|
{
|
foreach (int keyId in keyIds)
|
{
|
SysInquiry_BrandInfo model = SelectModelByKey(keyId);
|
isSuccess = DeleteModel(model);
|
if (!isSuccess)
|
break;
|
}
|
if (isSuccess)
|
scope.Complete();
|
}
|
return isSuccess;
|
}
|
|
/// <summary>
|
/// 返回所有有效的品牌列表
|
/// </summary>
|
/// <returns></returns>
|
public IEnumerable<SysInquiry_BrandInfo> GetBrandList()
|
{
|
string condition = string.Empty;
|
condition = " Status=1 order by OrderNum ";
|
IList<SysInquiry_BrandInfo> result = _dataBase.SelectModel<SysInquiry_BrandInfo>("*", "SysInquiry_BrandInfo", condition);
|
return result;
|
}
|
|
public bool InsertBrandModel(SysInquiry_BrandInfo model, SysInquiry_BrandListByPaper brandPaper, SysInquiry_PaperInfo paperModel)
|
{
|
bool isSuccess = true;
|
PaperInfoDAL _paperInfoDAL = new PaperInfoDAL(_dataBase);
|
using (TransactionScope scope = new TransactionScope())
|
{
|
if (model == null)
|
{
|
isSuccess = false;
|
}
|
else
|
{
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@BrandName",model.BrandName),
|
new SqlParameter("@Status",model.Status),
|
new SqlParameter("@OrderNum",model.OrderNum),
|
new SqlParameter(){ParameterName="@KeyId", SqlDbType=SqlDbType.Int,Direction=ParameterDirection.Output}
|
};
|
try
|
{
|
_dataBase.Query("SysInquiry_BrandInfo_ADD", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
model.KeyId = sqlParms[3].Value.ToInt32().Value;
|
brandPaper.BrandId = model.KeyId;
|
if (paperModel != null)
|
{
|
paperModel.DefaultBrandId = brandPaper.BrandId;
|
}
|
isSuccess = true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
isSuccess = false;
|
}
|
if (isSuccess)
|
{
|
isSuccess = _paperInfoDAL.InsertBrandByPaper(brandPaper, paperModel);
|
}
|
}
|
if (isSuccess)
|
scope.Complete();
|
}
|
return isSuccess;
|
|
}
|
|
public bool UpdateBrandModel(SysInquiry_BrandInfo model, SysInquiry_BrandListByPaper brandPaper, SysInquiry_PaperInfo paperModel, SysInquiry_PaperInfo orPaperModel)
|
{
|
bool isSuccess = true;
|
PaperInfoDAL _paperInfoDAL = new PaperInfoDAL(_dataBase);
|
using (TransactionScope scope = new TransactionScope())
|
{
|
if (model == null)
|
{
|
isSuccess = false;
|
}
|
else
|
{
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@BrandName",model.BrandName),
|
new SqlParameter("@Status",model.Status),
|
new SqlParameter("@KeyId",model.KeyId),
|
new SqlParameter("@OrderNum",model.OrderNum)
|
};
|
try
|
{
|
_dataBase.Query("SysInquiry_BrandInfo_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
isSuccess = true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
if (isSuccess)
|
{
|
isSuccess = _paperInfoDAL.UpdateBrandByPaper(brandPaper, paperModel, orPaperModel);
|
}
|
if (isSuccess)
|
scope.Complete();
|
}
|
}
|
return isSuccess;
|
}
|
|
/// <summary>
|
/// 获取最新排序顺序
|
/// </summary>
|
/// <returns></returns>
|
public int GetOrderNumByMax(int paperId)
|
{
|
int orderNum = 1;
|
IList<SysInquiry_BrandInfo> result = _dataBase.SelectModel<SysInquiry_BrandInfo>(" MAX(OrderNum)+1 AS OrderNum ", "dbo.SysInquiry_BrandInfo a LEFT JOIN dbo.SysInquiry_BrandListByPaper b ON a.KeyId=b.BrandId", " b.PaperId=" + paperId);
|
if (result != null && result.Count > 0)
|
{
|
if (result[0].OrderNum.HasValue)
|
{
|
orderNum = result[0].OrderNum.Value;
|
}
|
else
|
{
|
orderNum = 1;
|
}
|
}
|
return orderNum;
|
}
|
}
|
}
|
