/**
|
* EC_SeckillBusinessDAL.cs
|
*
|
* 功 能： 特价业务数据访问实现
|
* 类 名： EC_SeckillBusinessDAL
|
*
|
* Ver 变更日期 负责人 变更内容
|
* ───────────────────────────────────
|
* V0.01 2013-4-12 9:23 吴崎均 更改InsertModel以及UpdateModel以与存储相匹配
|
* V0.02 吴崎均 新增分页实现、获取单个实体实现
|
* V0.03 2013-4-18 16:00 吴崎均 新增批量删除
|
* V0.04 2013-5-2 11:55 吴崎均 更新SelectModleById方法实现拼接sql获取实体方式为为调用存储过程sp_EC_SeckillBuffer_Merge归并缓冲并获取数据
|
* V0.05 2013-5-3 10:15 吴崎均 变更字典表关联数据为DicType + MeanValue
|
* V0.06 2013-5-14 11:53 吴崎均 改变分页方法，增加创建时间范围查询条件
|
* V0.07 2013-5-28 13:43 吴崎均 修改批量删除方法定义及实现
|
* V0.08 2013-6-2 10:42 吴崎均 增加修改状态方法
|
*
|
*
|
*
|
*
|
*
|
*/
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using CY.IDAL;
|
using System.Data.SqlClient;
|
using System.Data;
|
using CY.Model;
|
using CY.Infrastructure.Query;
|
|
namespace CY.SQLDAL
|
{
|
|
|
/// <summary>
|
/// 特价业务数据访问实现
|
/// </summary>
|
public class EC_SeckillBusinessDAL : IEC_SeckillBusinessDAL
|
{
|
|
private Database _dataBase = null;
|
|
/// <summary>
|
/// 初始化构造
|
/// </summary>
|
/// <param name="dataBase"></param>
|
public EC_SeckillBusinessDAL(Database dataBase)
|
{
|
_dataBase = dataBase;
|
|
}
|
|
#region 常量
|
|
/// <summary>
|
/// 查询目标
|
/// </summary>
|
const string SELECTTARGET = @" s.*,p.PrintName as PrintTypeName,d_state.Name as StateName,m.Name as MemberName,acd.LevelIcon as LevelIcon";
|
/// <summary>
|
/// 查询来源
|
/// </summary>
|
const string FROMSOUCEBEFORE = " EC_SeckillBusiness as s Inner Join EC_MemberBasic as m On(s.MemberId=m.MemberId ";
|
const string FROMSOUCEEND = " ) " +
|
"left join SysInquiry_PrintingType as p On(s.PrintTypeId=p.Keyid) " +
|
"left join Integrity_IntegrityCard acd On (s.MemberId=acd.MemberId ) " +
|
"left join Integrity_CreditLevelRule bcd on acd.RuleId = bcd.RuleId "+
|
"Left Join Sys_Dictionary as d_state On(d_state.DicType='特价业务状态' And s.[State]=d_state.MeanValue) where bcd.RuleType ='True' ";
|
/// <summary>
|
/// 分页默认排序字段
|
/// </summary>
|
const string ORDERBY = " s.Keyid desc";
|
|
#endregion
|
|
public EC_SeckillBusinessDAL()
|
{
|
_dataBase = new Database();
|
}
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.EC_SeckillBusiness trueModel = model as Model.EC_SeckillBusiness;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
else { }
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@MemberId",trueModel.MemberId),
|
new SqlParameter("@PrintTypeId",trueModel.PrintTypeId),
|
new SqlParameter("@BusinessName",trueModel.BusinessName),
|
new SqlParameter("@Specifications",trueModel.Specifications),
|
new SqlParameter("@OrderCount",trueModel.OrderCount),
|
new SqlParameter("@PrintCount",trueModel.PrintCount),
|
new SqlParameter("@Price",trueModel.Price),
|
new SqlParameter("@SurplusOrderCount",trueModel.OrderCount),
|
new SqlParameter("@Contacts",trueModel.Contacts),
|
new SqlParameter("@ContactPhone",trueModel.ContactPhone),
|
new SqlParameter("@QQ",trueModel.QQ),
|
new SqlParameter("@EndTime",trueModel.EndTime),
|
new SqlParameter("@State","0"),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@Remark",trueModel.Remark)
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_SeckillBusiness_Insert", 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.EC_SeckillBusiness trueModel = model as Model.EC_SeckillBusiness;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
else { }
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@MemberId",trueModel.MemberId),
|
new SqlParameter("@PrintTypeId",trueModel.PrintTypeId),
|
new SqlParameter("@BusinessName",trueModel.BusinessName),
|
new SqlParameter("@Specifications",trueModel.Specifications),
|
new SqlParameter("@OrderCount",trueModel.OrderCount),
|
new SqlParameter("@PrintCount",trueModel.PrintCount),
|
new SqlParameter("@Price",trueModel.Price),
|
new SqlParameter("@SurplusOrderCount",trueModel.SurplusOrderCount),
|
new SqlParameter("@Contacts",trueModel.Contacts),
|
new SqlParameter("@ContactPhone",trueModel.ContactPhone),
|
new SqlParameter("@QQ",trueModel.QQ),
|
new SqlParameter("@EndTime",trueModel.EndTime),
|
new SqlParameter("@State",trueModel.State),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@Remark",trueModel.Remark)
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_SeckillBusiness_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.EC_SeckillBusiness trueModel = model as Model.EC_SeckillBusiness;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
else { }
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid)
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_SeckillBusiness_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 分页查询
|
/// </summary>
|
/// <param name="query">查询对象</param>
|
/// <param name="pagination">分页对象</param>
|
/// <returns></returns>
|
public IEnumerable<Model.EC_SeckillBusiness> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
|
{
|
if (null == pagination || null == query || null == query.Criteria || 1 > query.Criteria.Count) return null; else { }
|
//query.Criteria 首个元素必须是排序字段，其值为结果排序字段
|
|
int maxParamIndex = query.Criteria.Count - 1;//最大索引
|
|
string[] orderbys = new string[] { ORDERBY };
|
string resultOrderBy = "";//结果集排序方式
|
|
if ("@orderBy".Equals(query.Criteria[maxParamIndex].PropertyName))
|
{
|
orderbys = string.Format("{0}", query.Criteria[maxParamIndex].Value).Split(',');
|
resultOrderBy = 1 == orderbys.Length ? resultOrderBy : orderbys[1];
|
}
|
|
string fromSouce = string.Format("{0}{1}{2}", FROMSOUCEBEFORE, query.Criteria[0].Value, FROMSOUCEEND);//拼装条件
|
return _dataBase.SelectModelPage<Model.EC_SeckillBusiness>(pagination, SELECTTARGET, fromSouce, orderbys[0], resultOrderBy);
|
|
|
}
|
|
/// <summary>
|
/// 分页查询(根据联系人和结束状态)
|
/// </summary>
|
/// <param name="pagination">分页对象</param>
|
/// <param name="contacts">联系人</param>
|
/// <param name="isEnd">是否结束(0为未结束 1为已结束 -1为不限)</param>
|
/// <param name="beginCreate">创建开始时间</param>
|
/// <param name="endCreate">创建结束时间</param>
|
/// <returns>查询结果 传入参数错误时结果为空</returns>
|
///
|
public IEnumerable<Model.EC_SeckillBusiness> SelectModelPage(Infrastructure.Query.Pagination pagination, string contacts, int isEnd, DateTime? beginCreate, DateTime? endCreate)
|
{
|
if (null == pagination) return null; else { }
|
|
string condition = "";
|
if (!string.IsNullOrEmpty(contacts))
|
{
|
condition += string.Format(" And s.BusinessName Like '%{0}%'", contacts);
|
}
|
if (-1 != isEnd)
|
{
|
condition += isEnd == 0 ? " And s.EndTime > Getdate() And s.State=0 " : " And ( s.EndTime < Getdate() Or s.State <> 0 )"; // 0为未结束 1为已结束 -1为不限
|
}
|
if (beginCreate.HasValue)
|
{
|
condition += string.Format(" And s.CreateTime >= '{0}' ", beginCreate.Value.ToString("yyyy-MM-dd"));
|
}
|
if (endCreate.HasValue)
|
{
|
condition += string.Format(" And s.CreateTime <= '{0}' ", endCreate.Value.ToString("yyyy-MM-dd"));
|
}
|
Query query = new Query(true);
|
query.Criteria.Add(new Criterion("condition", condition));
|
//query.Criteria.Add(new Criterion("orderBy", "s.Keyid desc,CreateTime desc"));
|
return SelectModelPage(query, pagination);
|
|
}
|
|
/// <summary>
|
/// 分页查询(根据联系人和结束状态)
|
/// </summary>
|
/// <param name="pagination">分页对象</param>
|
/// <param name="contacts">联系人</param>
|
/// <param name="isEnd">是否结束(0为未结束 1为已结束 -1为不限)</param>
|
/// <param name="beginCreate">创建开始时间</param>
|
/// <param name="endCreate">创建结束时间</param>
|
/// <param name="MemberId">会员编号</param>
|
/// <returns>查询结果 传入参数错误时结果为空</returns>
|
public IEnumerable<Model.EC_SeckillBusiness> SelectModelPage(Infrastructure.Query.Pagination pagination, string contacts, int isEnd, DateTime? beginCreate, DateTime? endCreate, Guid MemberId)
|
{
|
if (null == pagination)
|
return null;
|
else
|
{
|
}
|
|
string condition = "";
|
if (!string.IsNullOrEmpty(contacts))
|
{
|
condition += string.Format(" And s.BusinessName Like '%{0}%'", contacts);
|
;
|
}
|
if (-1 != isEnd)
|
{
|
condition += isEnd == 0 ? " And s.EndTime > Getdate() And s.State=0 " : " And ( s.EndTime < Getdate() Or s.State <> 0 )"; // 0为未结束 1为已结束 -1为不限
|
}
|
if (beginCreate.HasValue)
|
{
|
condition += string.Format(" And s.CreateTime >= '{0}' ", beginCreate.Value.ToString("yyyy-MM-dd"));
|
}
|
if (endCreate.HasValue)
|
{
|
condition += string.Format(" And s.CreateTime <= '{0}' ", endCreate.Value.ToString("yyyy-MM-dd"));
|
}
|
if (MemberId != Guid.Empty && MemberId != null)
|
{
|
condition += string.Format(" And m.MemberId = '{0}' ", MemberId);
|
}
|
Query query = new Query(true);
|
query.Criteria.Add(new Criterion("condition", condition));
|
//query.Criteria.Add(new Criterion("orderBy", "s.Keyid desc,CreateTime desc"));
|
return SelectModelPage(query, pagination);
|
|
}
|
|
/// <summary>
|
/// 单个查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.EC_SeckillBusiness> SelectAllModel(Infrastructure.Query.Query query)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 根据编号获取实体信息
|
/// </summary>
|
/// <param name="id">主键编号</param>
|
/// <returns></returns>
|
public EC_SeckillBusiness SelectModleById(int id)
|
{
|
if (id < 1) return null;//错误数据返会空
|
else { }
|
|
//IList<EC_SeckillBusiness> result = _dataBase.SelectModel<EC_SeckillBusiness>(SELECTTARGET, string.Format("{0}{1}{2}{3}", FROMSOUCEBEFORE, " And Keyid=", id, FROMSOUCEEND));
|
//return null == result || result.Count == 0 ? null : result[0];//返回结果
|
IList<EC_SeckillBusiness> result = null;
|
using (IDataReader reader = _dataBase.QueryDataReader("sp_EC_SeckillBuffer_Merge", CommandType.StoredProcedure, new SqlParameter() { ParameterName = "@seckillId", Value = id }))
|
{
|
result = _dataBase.ReadDataToModel<EC_SeckillBusiness>(reader);
|
}
|
return null == result || result.Count == 0 ? null : result[0];//返回结果
|
//IDataReader reader = _dataBase.QueryDataReader("sp_EC_SeckillBusiness_SelectByMember",
|
// CommandType.StoredProcedure,
|
// new SqlParameter() { ParameterName = "@memberId", Value = memberId },
|
// new SqlParameter() { ParameterName = "@printTypeId", Value = printTypeId });
|
|
//return Database.ReadDataToModelAndDispose<Model.EC_SeckillBusiness>(reader);
|
}
|
|
/// <summary>
|
/// 根据编号批量删除数据
|
/// </summary>
|
/// <param name="currentOperator">当前操作人</param>
|
/// <param name="ids">编号集合</param>
|
/// <returns></returns>
|
public bool DeleteDataByIds(string currentOperator, params int[] ids)
|
{
|
return Database.DeleteDataByIds<EC_SeckillBusiness>(this, currentOperator, ids);
|
}
|
|
/// <summary>
|
/// 减少秒杀数量
|
/// </summary>
|
/// <param name="seckillId">要操作的秒杀业务编号</param>
|
/// <param name="seckillNum">要减少的秒杀数量</param>
|
/// <param name="memberId">下单的用户编号</param>
|
/// <returns></returns>
|
internal bool DecreaseSeckillNum(int seckillId, int seckillNum, Guid memberId)
|
{
|
SqlParameter[] param = new SqlParameter[]{
|
new SqlParameter() {Direction=ParameterDirection.ReturnValue} ,
|
new SqlParameter() { ParameterName = "@seckillId",Value=seckillId },
|
new SqlParameter() { ParameterName = "@seckillNum",Value=seckillNum},
|
new SqlParameter() { ParameterName = "@memberId",Value=memberId}
|
};
|
_dataBase.Query("sp_EC_SeckillBuffer_Insert", CommandType.StoredProcedure, param);
|
return 1.Equals(param[0].Value);
|
}
|
|
/// <summary>
|
/// 改变特价秒杀业务状态
|
/// </summary>
|
/// <param name="id">数据编号</param>
|
/// <param name="state">状态</param>
|
/// <returns></returns>
|
public bool ChangeState(int id, int state)
|
{
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",id),
|
new SqlParameter("@State",state)
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_SeckillBusiness_ChangeState", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
}
|
}
|
