/**
|
* EC_QuoteDemandDAL.cs
|
*
|
* 功 能： 报价需求数据访问实现
|
* 类 名： EC_QuoteDemandDAL
|
*
|
* Ver 变更日期 负责人 变更内容
|
* ───────────────────────────────────
|
* V0.01 2013-4-12 9:23 吴崎均 更改InsertModel以及UpdateModel以与存储相匹配
|
* V0.02 吴崎均 新增分页实现、获取单个实体实现
|
* V0.03 2013-4-18 16:00 吴崎均 新增批量删除
|
* V0.04 2013-5-3 10:15 吴崎均 变更字典表关联数据为DicType + MeanValue
|
* V0.05 2013-5-22 17:32 吴崎均 修改方法“根据编号获取实体信息”实现为调用存储sp_EC_QuoteDemand_SelectRow
|
* V0.06 2013-5-28 13:43 吴崎均 修改批量删除方法定义及实现
|
* V0.07 2013-6-2 10:42 吴崎均 增加修改状态方法
|
* V0.07 2013-6-16 18:32 吴崎均 修改分页查询方法，增加查询条件
|
*
|
*
|
*
|
*
|
*
|
*/
|
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;
|
using System.Transactions;
|
|
namespace CY.SQLDAL
|
{
|
|
/// <summary>
|
/// 报价需求数据访问实现
|
/// </summary>
|
public class EC_QuoteDemandDAL : IEC_QuoteDemandDAL
|
{
|
|
private Database _dataBase = null;
|
#region 常量
|
|
/// <summary>
|
/// 查询目标
|
/// </summary>
|
const string SELECTTARGET = @" q.*,
|
(case when p.PrintName is null then '其他需求' else p.PrintName end) as PrintTypeName,
|
d_state.Name as StateName,
|
m.Name as MemberName,
|
d_calltype.Name as CallTypeName";
|
/// <summary>
|
/// 查询来源
|
/// </summary>
|
const string FROMSOUCEBEFORE = " EC_QuoteDemand as q Inner Join EC_MemberBasic as m On(q.MemberId=m.MemberId ";
|
const string FROMSOUCEEND = " ) " +
|
"left join SysInquiry_PrintingType as p On(q.PrintTypeId=p.Keyid) " +
|
"Left Join Sys_Dictionary as d_state On(d_state.DicType='用户需求状态' And q.[State]=d_state.MeanValue) " +
|
"Left Join Sys_Dictionary as d_calltype On(d_calltype.DicType='通知类型' And q.[CallTypeId]=d_calltype.MeanValue) " +
|
"";
|
/// <summary>
|
/// 分页默认排序字段
|
/// </summary>
|
const string ORDERBY = "q.Keyid desc";
|
#endregion
|
|
public EC_QuoteDemandDAL()
|
{
|
_dataBase = new Database();
|
}
|
|
/// <summary>
|
/// 原本的添加需求业务方法
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool OldInsert(EC_QuoteDemand model)
|
{
|
EC_QuoteDemand trueModel = model as EC_QuoteDemand;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
else { }
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter(){ParameterName="@Keyid",Direction=ParameterDirection.Output,SqlDbType=SqlDbType.Int},
|
new SqlParameter("@MemberId",trueModel.MemberId),
|
new SqlParameter("@PrintTypeId",trueModel.PrintTypeId),
|
new SqlParameter("@DemandName",trueModel.DemandName),
|
new SqlParameter("@DemandDemand",trueModel.DemandDemand),
|
new SqlParameter("@EndTime",trueModel.EndTime),
|
new SqlParameter("@CallTypeId",trueModel.CallTypeId),
|
new SqlParameter("@State",trueModel.State),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@ContactPhone",trueModel.ContactPhone),
|
new SqlParameter("@QQ",trueModel.QQ),
|
new SqlParameter("@Remark",trueModel.Remark)
|
};
|
|
_dataBase.Query("sp_EC_QuoteDemand_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
model.Keyid = (int)sqlParms[0].Value;
|
return true;
|
}
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
EC_QuoteDemand trueModel = model as EC_QuoteDemand;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
else { }
|
|
EC_DemandPrintParameterDAL printParameterDAL = new EC_DemandPrintParameterDAL(_dataBase);
|
using (TransactionScope tran = new TransactionScope())
|
{
|
bool isWin = false;
|
isWin = OldInsert(trueModel);
|
if (!isWin)
|
{
|
return false;
|
}
|
else ;
|
if (trueModel.PrintTypeId != -1)
|
{
|
trueModel.PrintParameter().TargetId = trueModel.Keyid;
|
isWin = printParameterDAL.InserModel(trueModel.PrintParameter());
|
if (!isWin) { return false; } else ;
|
}
|
tran.Complete();
|
|
}
|
return true;
|
}
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool OldUpdate(Infrastructure.Domain.IAggregateRoot model)
|
{
|
EC_QuoteDemand trueModel = model as EC_QuoteDemand;
|
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("@DemandName",trueModel.DemandName),
|
new SqlParameter("@DemandDemand",trueModel.DemandDemand),
|
new SqlParameter("@EndTime",trueModel.EndTime),
|
new SqlParameter("@CallTypeId",trueModel.CallTypeId),
|
new SqlParameter("@State","0"),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@ContactPhone",trueModel.ContactPhone),
|
new SqlParameter("@QQ",trueModel.QQ),
|
new SqlParameter("@Remark",trueModel.Remark)
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_QuoteDemand_Update", 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)
|
{
|
EC_QuoteDemand trueModel = model as EC_QuoteDemand;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
else { }
|
EC_DemandPrintParameterDAL eC_DemandPrintParameterDAL = new EC_DemandPrintParameterDAL(_dataBase);
|
|
using (TransactionScope tran = new TransactionScope())
|
{
|
//if (eC_DemandPrintParameterDAL.UpdateModel(trueModel.PrintParameter())) return false;
|
if (!this.OldUpdate(trueModel)) { return false; }
|
tran.Complete();
|
}
|
|
return true;
|
}
|
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
EC_QuoteDemand trueModel = model as EC_QuoteDemand;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
else { }
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid)
|
};
|
try
|
{
|
_dataBase.Query("sp_EC_QuoteDemand_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 删除需求
|
/// </summary>
|
/// <param name="demand"></param>
|
/// <returns></returns>
|
public bool DelModel(int keyId)
|
{
|
string sqlStr = "delete from EC_QuoteDemand where keyId=@keyId";
|
SqlParameter par = new SqlParameter("@keyId", keyId);
|
SqlParameter[] pars = new SqlParameter[]{
|
par
|
};
|
try
|
{
|
_dataBase.ExecuteSql(sqlStr, pars);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 分页查询
|
/// </summary>
|
/// <param name="query">查询对象</param>
|
/// <param name="pagination">分页对象</param>
|
/// <returns></returns>
|
public IEnumerable<EC_QuoteDemand> 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<EC_QuoteDemand>(pagination, SELECTTARGET, fromSouce, orderbys[0], resultOrderBy);
|
|
|
}
|
|
/// <summary>
|
/// 分页查询(前台用)
|
/// </summary>
|
/// <param name="pagination">分页对象</param>
|
/// <param name="province">省</param>
|
/// <param name="city">市</param>
|
/// <param name="printTypeId">印刷类型</param>
|
/// <returns></returns>
|
public IEnumerable<EC_QuoteDemand> SelectModelPage(Infrastructure.Query.Pagination pagination, string province = "", string city = "", int printTypeId = -1)
|
{
|
|
string stateId = "0";//获取有效类型编号
|
//using (IDataReader reader = _dataBase.QueryDataReader("Select Keyid From Sys_Dictionary Where DicType='用户需求状态' And MeanValue=0", CommandType.Text))
|
//{
|
// if (reader.Read())
|
// {
|
// stateId = reader[0].ToString();
|
// }
|
//}
|
string condition = string.Format(" And q.State = {0}", stateId);
|
condition += " And EndTime >= Getdate()";
|
if (!string.IsNullOrEmpty(province))
|
{
|
condition += string.Format(" And m.Province='{0}'", province);
|
}
|
else { }
|
if (!string.IsNullOrEmpty(city))
|
{
|
condition += string.Format(" And m.City='{0}'", city);
|
}
|
else { }
|
if (-1 != printTypeId)
|
{
|
condition += string.Format(" And d.PrintTypeId={0}", printTypeId);
|
}
|
else { }
|
|
Query query = new Query(true);
|
query.Criteria.Add(new Criterion("condition", condition));
|
//query.Criteria.Add(new Criterion("orderBy", "CreateTime,CreateTime"));
|
|
return SelectModelPage(query, pagination);
|
}
|
|
/// <summary>
|
/// 分页查询(后台用)
|
/// </summary>
|
/// <param name="pagination">分页对象</param>
|
/// <param name="memberId">会员编号</param>
|
/// <param name="name">名称</param>
|
/// <param name="beginDate">开始时间</param>
|
/// <param name="endDate">结束时间</param>
|
/// <param name="printTypeId">印刷类型</param>
|
/// <param name="state">状态</param>
|
/// <returns></returns>
|
public IEnumerable<EC_QuoteDemand> SelectModelPage(Infrastructure.Query.Pagination pagination, Guid memberId,string name, DateTime? beginDate, DateTime? endDate, int printTypeId = -1, int? state = null)
|
{
|
|
//string stateId = "";//获取有效类型编号
|
//using (IDataReader reader = _dataBase.QueryDataReader("Select Keyid From Sys_Dictionary Where DicType='用户需求状态' And MeanValue=0", CommandType.Text))
|
//{
|
// if (reader.Read())
|
// {
|
// stateId = reader[0].ToString();
|
// }
|
//}
|
string condition = "";// string.Format(" And q.State = {0}", stateId);
|
if (state.HasValue && -1 != state)
|
{
|
condition += state == 0 ? " And q.EndTime > Getdate() And q.State=0 " : " And ( q.EndTime < Getdate() Or q.State <> 0 )"; // 0为未结束 1为已结束 -1为不限
|
}
|
if (Guid.Empty != memberId)
|
{
|
condition += string.Format(" And q.MemberId='{0}'", memberId);
|
}
|
else { }
|
if (string.IsNullOrEmpty(name))
|
{
|
condition += string.Format(" And q.DemandName like '%{0}%'", name);
|
}
|
if (beginDate.HasValue)
|
{
|
condition += string.Format(" And q.CreateTime >= '{0}'", beginDate.Value.ToString("yyyy-MM-dd"));
|
}
|
else { }
|
if (endDate.HasValue)
|
{
|
condition += string.Format(" And q.CreateTime <= '{0}'", endDate.Value.ToString("yyyy-MM-dd"));
|
}
|
else { }
|
if (-1 != printTypeId)
|
{
|
condition += string.Format(" And q.PrintTypeId={0}", printTypeId);
|
}
|
else { }
|
|
Query query = new Query(true);
|
query.Criteria.Add(new Criterion("condition", condition));
|
//query.Criteria.Add(new Criterion("orderBy", "CreateTime,CreateTime"));
|
|
return SelectModelPage(query, pagination);
|
}
|
|
/// <summary>
|
/// 单个查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public IEnumerable<EC_QuoteDemand> SelectAllModel(Infrastructure.Query.Query query)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 根据编号获取实体信息
|
/// </summary>
|
/// <param name="id">主键编号</param>
|
/// <returns></returns>
|
public EC_QuoteDemand SelectModleById(int id)
|
{
|
if (id < 1) return null;//错误数据返会空
|
else { }
|
IList<EC_QuoteDemand> result = null;
|
|
using (IDataReader reader = _dataBase.QueryDataReader("sp_EC_QuoteDemand_SelectRow", CommandType.StoredProcedure, new SqlParameter("@keyid", id)))
|
{
|
result = _dataBase.ReadDataToModel<EC_QuoteDemand>(reader);
|
}
|
return null == result || result.Count == 0 ? null : result[0];//返回结果
|
|
}
|
|
/// <summary>
|
/// 根据编号批量删除数据
|
/// </summary>
|
/// <param name="currentOperator">当前操作人</param>
|
/// <param name="ids">编号集合</param>
|
/// <returns></returns>
|
public bool DeleteDataByIds(string currentOperator, params int[] ids)
|
{
|
return Database.DeleteDataByIds<EC_QuoteDemand>(this, currentOperator, ids);
|
}
|
|
/// <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_QuoteDemand_ChangeState", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 检测是否第一次发布需求
|
/// </summary>
|
/// <param name="MemberId"></param>
|
/// <returns></returns>
|
public bool IsTodayHasValue(Guid MemberId)
|
{
|
try
|
{
|
if (MemberId == Guid.Empty)
|
return false;//错误数据返会空
|
|
DateTime NowTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
|
|
IList<EC_QuoteDemand> result = _dataBase.SelectModel<EC_QuoteDemand>(" * ", "EC_QuoteDemand", string.Format(" MemberId='{0}' and CreateTime >= '{1}' and CreateTime <='{2}'", MemberId, NowTime, NowTime.AddDays(1))) as IList<EC_QuoteDemand>;//执行查询
|
|
return result == null || result.Count < 2;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 判断能不能发布相同需求
|
/// </summary>
|
/// <param name="MemberId"></param>
|
/// <param name="demand"></param>
|
/// <returns></returns>
|
public bool CanInsertModel(Guid MemberId, string demand)
|
{
|
bool canInsertModel = true;
|
string nowDateStr = DateTime.Now.ToString("yyyy-MM-dd");
|
string condition = string.Empty;
|
condition = " State=0 AND MemberId='{0}' and DemandDemand='{1}' and CAST(CreateTime AS DATE)='{2}'";
|
condition = string.Format(condition, MemberId.ToString(), demand, nowDateStr);
|
IList<EC_QuoteDemand> result = _dataBase.SelectModel<EC_QuoteDemand>(" * ", "EC_QuoteDemand", condition) as IList<EC_QuoteDemand>;//执行查询
|
if (result != null && result.Count > 0)
|
{
|
canInsertModel = false;
|
}
|
return canInsertModel;
|
}
|
|
/// <summary>
|
/// 获取当天发布的发布状态下的需求与下单要求一致的需求信息
|
/// </summary>
|
/// <param name="MemberId"></param>
|
/// <param name="demand"></param>
|
/// <returns></returns>
|
public EC_QuoteDemand GetModelByCanDel(Guid MemberId, string demand)
|
{
|
EC_QuoteDemand demandModel = null;
|
string nowDateStr = DateTime.Now.ToString("yyyy-MM-dd");
|
string condition = string.Empty;
|
condition = " State=0 AND MemberId='{0}' and DemandDemand='{1}' and CAST(CreateTime AS DATE)='{2}'";
|
condition = string.Format(condition, MemberId.ToString(), demand, nowDateStr);
|
IList<EC_QuoteDemand> result = _dataBase.SelectModel<EC_QuoteDemand>(" * ", "EC_QuoteDemand", condition) as IList<EC_QuoteDemand>;//执行查询
|
if (result != null && result.Count > 0)
|
{
|
demandModel = result[0];
|
}
|
return demandModel;
|
}
|
}
|
}
|
