/// <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); }