/// <summary>
/// 添加短信发送明细
/// </summary>
/// <param name="model">短信发送明细实体</param>
/// <returns>返回1成功；其他失败</returns>
public int AddSmsDetail(MSmsDetail model)
{
if (model == null || string.IsNullOrEmpty(model.CompanyId) || model.Amount <= 0 ||
model.Number == null || string.IsNullOrEmpty(model.Content))
{
return(0);
}
model.PlanId = Guid.NewGuid().ToString();
DbCommand dc = _db.GetSqlStringCommand(SqlSmsDetailAdd + GetSmsDetailNumberSql(model.PlanId, model.Number));
_db.AddInParameter(dc, "PlanId", DbType.AnsiStringFixedLength, model.PlanId);
_db.AddInParameter(dc, "CompanyId", DbType.AnsiStringFixedLength, model.CompanyId);
_db.AddInParameter(dc, "Channel", DbType.Byte, (byte)model.Channel);
_db.AddInParameter(dc, "Content", DbType.String, model.Content);
_db.AddInParameter(dc, "Amount", DbType.Decimal, model.Amount);
_db.AddInParameter(dc, "UnitPrice", DbType.Decimal, model.UnitPrice);
_db.AddInParameter(dc, "IssueTime", DbType.DateTime, DateTime.Now);
_db.AddInParameter(dc, "Status", DbType.Byte, (byte)model.Status);
_db.AddInParameter(dc, "StatusDesc", DbType.String, model.StatusDesc);
_db.AddInParameter(dc, "OperatorId", DbType.AnsiStringFixedLength, model.OperatorId);
return(DbHelper.ExecuteSql(dc, _db) > 0 ? 1 : 0);
}
/// <summary>
/// 获取短信发送实体
/// </summary>
/// <param name="companyId">公司编号</param>
/// <param name="PlanId">发送任务编号</param>
/// <returns></returns>
public MSmsDetail GetSmsDetaiInfo(string CompanyId, string PlanId)
{
MSmsDetail model = null;
string sql = "select [PlanId],[CompanyId],[Channel],[Content],[Amount],[UnitPrice],[IssueTime],[Status],[StatusDesc],[OperatorId],(select Code,Type from tbl_SmsDetail114 where tbl_SmsDetail114.PlanId = tbl_SmsDetail.PlanId for xml raw, root('root')) as Number from tbl_SmsDetail where CompanyId=@CompanyId and PlanId=@PlanId";
DbCommand cmd = this._db.GetSqlStringCommand(sql);
this._db.AddInParameter(cmd, "CompanyId", DbType.AnsiStringFixedLength, CompanyId);
this._db.AddInParameter(cmd, "PlanId", DbType.AnsiStringFixedLength, PlanId);
using (IDataReader dr = DbHelper.ExecuteReader(cmd, _db))
{
if (dr.Read())
{
model = new MSmsDetail();
if (!dr.IsDBNull(dr.GetOrdinal("PlanId")))
{
model.PlanId = dr.GetString(dr.GetOrdinal("PlanId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("CompanyId")))
{
model.CompanyId = dr.GetString(dr.GetOrdinal("CompanyId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Channel")))
{
model.Channel = dr.GetByte(dr.GetOrdinal("Channel"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Content")))
{
model.Content = dr.GetString(dr.GetOrdinal("Content"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Amount")))
{
model.Amount = dr.GetDecimal(dr.GetOrdinal("Amount"));
}
if (!dr.IsDBNull(dr.GetOrdinal("UnitPrice")))
{
model.UnitPrice = dr.GetDecimal(dr.GetOrdinal("UnitPrice"));
}
if (!dr.IsDBNull(dr.GetOrdinal("IssueTime")))
{
model.IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Status")))
{
model.Status = (Model.EnumType.SmsStructure.SendStatus)dr.GetByte(dr.GetOrdinal("Status"));
}
if (!dr.IsDBNull(dr.GetOrdinal("StatusDesc")))
{
model.StatusDesc = dr.GetString(dr.GetOrdinal("StatusDesc"));
}
if (!dr.IsDBNull(dr.GetOrdinal("OperatorId")))
{
model.OperatorId = dr.GetString(dr.GetOrdinal("OperatorId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Number")))
{
model.Number = GetSmsNumber(dr.GetString(dr.GetOrdinal("Number")));
}
}
}
return(model);
}
/// <summary>
/// 获取短信发送明细
/// </summary>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">当前页数</param>
/// <param name="recordCount">总记录数</param>
/// <param name="companyId">公司编号</param>
/// <param name="model">查询实体</param>
/// <returns></returns>
public IList <MSmsDetail> GetSmsDetailList(int pageSize, int pageIndex, ref int recordCount, string companyId, MQuerySmsDetail model)
{
IList <MSmsDetail> list = null;
if (string.IsNullOrEmpty(companyId))
{
return(list);
}
MSmsDetail tmpModel;
#region Sql拼接
string fields = " [PlanId],[CompanyId],[Channel],[Content],[Amount],[UnitPrice],[IssueTime],[Status],[StatusDesc],[OperatorId],(select Code,Type from tbl_SmsDetail114 where tbl_SmsDetail114.PlanId = tbl_SmsDetail.PlanId for xml raw, root('root')) as Number ";
string tableName = "tbl_SmsDetail";
string orderByString = " [IssueTime] desc ";
string identityColumnName = " PlanId ";
var strWhere = new StringBuilder();
strWhere.AppendFormat(" CompanyId = '{0}' ", companyId);
if (model != null)
{
if (!string.IsNullOrEmpty(model.KeyWord.Trim()))
{
strWhere.AppendFormat(" and Content like '%{0}%' ", model.KeyWord.Trim());
}
if (model.Status.HasValue)
{
strWhere.AppendFormat(" and Status = {0} ", (int)model.Status.Value);
}
if (model.StartTime.HasValue)
{
strWhere.AppendFormat(" and datediff(day,'{0}',IssueTime)>=0", model.StartTime.Value);
}
if (model.EndTime.HasValue)
{
strWhere.AppendFormat(" and datediff(day,'{0}',IssueTime)<=0", model.EndTime.Value);
}
}
#endregion
using (IDataReader dr = DbHelper.ExecuteReader(_db, pageSize, pageIndex, ref recordCount, tableName
, identityColumnName, fields, strWhere.ToString(), orderByString))
{
list = new List <MSmsDetail>();
while (dr.Read())
{
tmpModel = new MSmsDetail();
if (!dr.IsDBNull(dr.GetOrdinal("PlanId")))
{
tmpModel.PlanId = dr.GetString(dr.GetOrdinal("PlanId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("CompanyId")))
{
tmpModel.CompanyId = dr.GetString(dr.GetOrdinal("CompanyId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Channel")))
{
tmpModel.Channel = dr.GetByte(dr.GetOrdinal("Channel"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Content")))
{
tmpModel.Content = dr.GetString(dr.GetOrdinal("Content"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Amount")))
{
tmpModel.Amount = dr.GetDecimal(dr.GetOrdinal("Amount"));
}
if (!dr.IsDBNull(dr.GetOrdinal("UnitPrice")))
{
tmpModel.UnitPrice = dr.GetDecimal(dr.GetOrdinal("UnitPrice"));
}
if (!dr.IsDBNull(dr.GetOrdinal("IssueTime")))
{
tmpModel.IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Status")))
{
tmpModel.Status = (Model.EnumType.SmsStructure.SendStatus)dr.GetByte(dr.GetOrdinal("Status"));
}
if (!dr.IsDBNull(dr.GetOrdinal("StatusDesc")))
{
tmpModel.StatusDesc = dr.GetString(dr.GetOrdinal("StatusDesc"));
}
if (!dr.IsDBNull(dr.GetOrdinal("OperatorId")))
{
tmpModel.OperatorId = dr.GetString(dr.GetOrdinal("OperatorId"));
}
if (!dr.IsDBNull(dr.GetOrdinal("Number")))
{
tmpModel.Number = GetSmsNumber(dr.GetString(dr.GetOrdinal("Number")));
}
list.Add(tmpModel);
}
}
return(list);
}
