/// <summary>
/// 根据主键值读取记录。如果数据库不存在这条数据将返回null
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="columns">需要返回的列，不提供任何列名时默认将返回所有列</param>
public SMSNoticeEntity GetSMSNotice(int id)
{
string sql = @"SELECT [Id],[MobilePhone],[SMSContent],[Status],[SendTime],[SystemType]
FROM
dbo.[SMSNotice] WITH(NOLOCK)
WHERE [Id]=@id" ;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@Id", DbType.Int32, id);
SMSNoticeEntity entity = new SMSNoticeEntity();
using (IDataReader reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.MobilePhone = StringUtils.GetDbString(reader["MobilePhone"]);
entity.SMSContent = StringUtils.GetDbString(reader["SMSContent"]);
entity.Status = StringUtils.GetDbInt(reader["Status"]);
entity.SendTime = StringUtils.GetDbDateTime(reader["SendTime"]);
entity.SystemType = StringUtils.GetDbInt(reader["SystemType"]);
}
}
return(entity);
}
public IList <SMSNoticeEntity> GetSMSWaitSend(int Num_i)
{
string sql = @" SELECT top " + Num_i + @" [Id],[MobilePhone],[SMSContent],[Status],[SendTime],[SystemType],CreateTime from dbo.[SMSNotice] WITH(NOLOCK)
WHERE Status= @Status order by id desc" ;
IList <SMSNoticeEntity> entityList = new List <SMSNoticeEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@Status", DbType.Int32, (int)SMSNoticeStatus.WaitSend);
//db.AddInParameter(cmd, "@Num", DbType.Int32, Num_i);
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
SMSNoticeEntity entity = new SMSNoticeEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.MobilePhone = StringUtils.GetDbString(reader["MobilePhone"]);
entity.SMSContent = StringUtils.GetDbString(reader["SMSContent"]);
entity.Status = StringUtils.GetDbInt(reader["Status"]);
entity.SendTime = StringUtils.GetDbDateTime(reader["SendTime"]);
entity.SystemType = StringUtils.GetDbInt(reader["SystemType"]);
entity.CreateTime = StringUtils.GetDateTime(reader["CreateTime"]);
entityList.Add(entity);
}
}
return(entityList);
}
/// <summary>
/// 判断当前节点是否已存在相同的
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int ExistNum(SMSNoticeEntity entity)
{
///id=0,判断总数，ID>0判断除自己之外的总数
string sql = @"Select count(1) from dbo.[SMSNotice] WITH(NOLOCK) ";
string where = "where ";
if (entity.Id == 0)
{
}
else
{
}
sql = sql + where;
DbCommand cmd = db.GetSqlStringCommand(sql);
if (entity.Id > 0)
{
db.AddInParameter(cmd, "@Id", DbType.Int32, entity.Id);
}
object identity = db.ExecuteScalar(cmd);
if (identity == null || identity == DBNull.Value)
{
return(0);
}
return(Convert.ToInt32(identity));
}
/// <summary>
/// 审核申请
/// </summary>
/// <returns></returns>
public int CheckTheApply()
{
int _id = FormString.IntSafeQ("id");
int _status = FormString.IntSafeQ("status");
int _result = MemberBLL.Instance.UpdateMemberSatus(_id, _status);
if (_result > 0)
{
string msgbodyt = "";
if (_status == (int)MemberStatus.Active)
{
msgbodyt = SMSTempletBLL.Instance.GetSMSContentByCode(SMSCodeCollection.RegisterPersonalCheck);
}
if (_status == (int)MemberStatus.Rejected)
{
msgbodyt = SMSTempletBLL.Instance.GetSMSContentByCode(SMSCodeCollection.RegisterPersonalCheckR);
}
if (!string.IsNullOrEmpty(msgbodyt))
{
MemberEntity mementity = MemberBLL.Instance.GetMember(_id);
SMSNoticeEntity notice = new SMSNoticeEntity();
notice.MobilePhone = mementity.MobilePhone;
notice.SMSContent = msgbodyt;
notice.SystemType = (int)SystemType.B2B;
SMSNoticeBLL.Instance.AddSMSNotice(notice);
}
}
return(_result);
}
/// <summary>
/// 审核拒绝
/// </summary>
/// <returns></returns>
public int RefuseStore()
{
int _StoreId = FormString.IntSafeQ("StoreId");
int _MemberId = FormString.IntSafeQ("MemberId");
int _billId = FormString.IntSafeQ("billId");
//StoreEntity _supplierEntity = StoreBLL.Instance.GetStore(_StoreId);
//_supplierEntity.Status = (int)StoreStatus.Rejected;
//int _result = StoreBLL.Instance.UpdateStore(_supplierEntity);
//MemberEntity _memberEntity = MemberBLL.Instance.GetMember(_MemberId);
//_memberEntity.Status = (int)MemberStatus.Rejected;
//MemberBLL.Instance.UpdateMember(_memberEntity);
//_result = MemBillVATBLL.Instance.UpdateMemBillVATStatus(_billId, (int)MemBillStatus.AuditReject);
int _result = MemberBLL.Instance.CheckSupplier(_MemberId, (int)MemberStatus.Rejected, _StoreId, (int)StoreStatus.Rejected, _billId, (int)MemBillStatus.AuditReject, memid);
if (_result > 0)
{
string msgbodyt = SMSTempletBLL.Instance.GetSMSContentByCode(SMSCodeCollection.RegisterCompanyCheckR);
if (!string.IsNullOrEmpty(msgbodyt))
{
MemberEntity mementity = MemberBLL.Instance.GetMember(_MemberId);
SMSNoticeEntity notice = new SMSNoticeEntity();
notice.MobilePhone = mementity.MobilePhone;
notice.SMSContent = msgbodyt;
notice.SystemType = (int)SystemType.B2B;
SMSNoticeBLL.Instance.AddSMSNotice(notice);
}
}
return(_result);
}
public int SMSNoticeSendAccess(SMSNoticeEntity entity)
{
string sql = @" UPDATE dbo.[SMSNotice] SET [Status]=@Status,[SendTime]=getdate() ,ReturnMsg=@ReturnMsg,SendProvider=@SendProvider
WHERE [Id]=@id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@Id", DbType.Int32, entity.Id);
db.AddInParameter(cmd, "@Status", DbType.Int32, (int)SMSNoticeStatus.HasSend);
db.AddInParameter(cmd, "@SendProvider", DbType.String, entity.SendProvider);
db.AddInParameter(cmd, "@ReturnMsg", DbType.String, entity.ReturnMsg);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 编辑待发送短信
/// </summary>
/// <returns></returns>
public ActionResult SendMsgEdit()
{
int memid = QueryString.IntSafeQ("memid", 0);
int _id = QueryString.IntSafeQ("id", 0);
string _SMSContent = QueryString.SafeQ("SMSContent", 0, 2000);
VWMemberEntity _entity = MemberBLL.Instance.GetVWMember(memid);
SMSNoticeEntity entity = SMSNoticeBLL.Instance.GetSMSNotice(_id);
ViewBag.entity = entity;
ViewBag.MobilePhone = _entity.MobilePhone;
ViewBag.SMSContent = _SMSContent;
return(View());
}
/// <summary>
/// 插入一条记录到表SMSNotice，如果表中存在自增字段，则返回值为新记录的自增字段值，否则返回0。
/// 该方法提供给界面等UI层调用
/// </summary>
/// <param name="sMSNotice">要添加的SMSNotice数据实体对象</param>
public int AddSMSNotice(SMSNoticeEntity sMSNotice)
{
if (sMSNotice.Id > 0)
{
return(UpdateSMSNotice(sMSNotice));
}
//else if (SMSNoticeBLL.Instance.IsExist(sMSNotice))
//{
// return (int)CommonStatus.ADD_Fail_Exist;
//}
else
{
return(SMSNoticeDA.Instance.AddSMSNotice(sMSNotice));
}
}
/// <summary>
/// 根据主键值更新记录的全部字段（注意：该方法不会对自增字段、timestamp类型字段以及主键字段更新！如果要更新主键字段，请使用Update方法）。
/// 如果数据库有数据被更新了则返回True，否则返回False
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="sMSNotice">待更新的实体对象</param>
public int UpdateSMSNotice(SMSNoticeEntity entity)
{
string sql = @" UPDATE dbo.[SMSNotice] SET
[MobilePhone]=@MobilePhone,[SMSContent]=@SMSContent,[Status]=@Status,[SendTime]=@SendTime,[SystemType]=@SystemType,[CreateTime]=@CreateTime
WHERE [Id]=@id";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@Id", DbType.Int32, entity.Id);
db.AddInParameter(cmd, "@MobilePhone", DbType.String, entity.MobilePhone);
db.AddInParameter(cmd, "@SMSContent", DbType.String, entity.SMSContent);
db.AddInParameter(cmd, "@Status", DbType.Int32, entity.Status);
db.AddInParameter(cmd, "@SendTime", DbType.DateTime, entity.SendTime);
db.AddInParameter(cmd, "@SystemType", DbType.Int32, entity.SystemType);
db.AddInParameter(cmd, "@CreateTime", DbType.DateTime, entity.CreateTime);
return(db.ExecuteNonQuery(cmd));
}
/// <summary>
/// 添加待发送短信
/// </summary>
/// <returns></returns>
public int AddSendMsg()
{
string _mobilePhone = FormString.SafeQ("mobilePhone");
string _SMSContent = FormString.SafeQ("SMSContent");
int _sendStatus = FormString.IntSafeQ("sendStatus");
int _systemType = FormString.IntSafeQ("systemType");
SMSNoticeEntity entity = new SMSNoticeEntity();
entity.MobilePhone = _mobilePhone;
entity.SMSContent = _SMSContent;
entity.Status = _sendStatus;
entity.SystemType = _systemType;
entity.SendTime = DateTime.Now.AddDays(10);
entity.CreateTime = DateTime.Now;
return(SMSNoticeBLL.Instance.AddSMSNotice(entity));
}
/// <summary>
/// 插入一条记录到表SMSNotice，如果表中存在自增字段，则返回值为新记录的自增字段值，否则返回0
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="sMSNotice">待插入的实体对象</param>
public int AddSMSNotice(SMSNoticeEntity entity)
{
string sql = @"insert into SMSNotice( [CreateTime],[MobilePhone],[SMSContent],[Status],[SystemType])VALUES
( getdate(),@MobilePhone,@SMSContent,@Status,@SystemType);
SELECT SCOPE_IDENTITY();" ;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@MobilePhone", DbType.String, entity.MobilePhone);
db.AddInParameter(cmd, "@SMSContent", DbType.String, entity.SMSContent);
db.AddInParameter(cmd, "@Status", DbType.Int32, entity.Status);
db.AddInParameter(cmd, "@SystemType", DbType.Int32, entity.SystemType);
object identity = db.ExecuteScalar(cmd);
if (identity == null || identity == DBNull.Value)
{
return(0);
}
return(Convert.ToInt32(identity));
}
/// <summary>
/// 修改待发送短信
/// </summary>
/// <returns></returns>
public int UpdateSendMsg()
{
int _id = FormString.IntSafeQ("id");
string _mobilePhone = FormString.SafeQ("mobilePhone");
string _SMSContent = FormString.SafeQ("SMSContent");
int _sendStatus = FormString.IntSafeQ("sendStatus");
int _systemType = FormString.IntSafeQ("systemType");
SMSNoticeEntity entity = SMSNoticeBLL.Instance.GetSMSNotice(_id);
entity.MobilePhone = _mobilePhone;
entity.SMSContent = _SMSContent;
entity.Status = _sendStatus;
entity.SystemType = _systemType;
entity.CreateTime = DateTime.Now;
entity.SendTime = DateTime.Now.AddDays(30);
return(SMSNoticeBLL.Instance.UpdateSMSNotice(entity));
}
/// <summary>
/// 发送营销短信
/// </summary>
/// <param name="sendentity">短信对象</param>
/// <param name="smstype">通道类型</param>
/// <returns></returns>
public SMSNoticeEntity SendSmsBehind(SMSNoticeEntity sendentity, int smstype = 2)
{
string mobile = sendentity.MobilePhone;
string smsbody = sendentity.SMSContent.EndsWith("【易店心】")? sendentity.SMSContent: sendentity.SMSContent + "【易店心】";
sendentity.ReturnMsg = "";
int isactive = 1;
IList <ConfigSmsProviderEntity> list = GetConfigSmsProviderAll(smstype, isactive, false);//获取所有有效的短信提供商
foreach (ConfigSmsProviderEntity entity in list)
{
try
{
sendentity.SendProvider = entity.Name;
if (entity.Name.ToLower() == SMSProviders.ZhuTongKeJi.ToLower())
{
sendentity.ReturnMsg = ZhuTongKeJiSend(entity.AppId, entity.UserCode, entity.PassWord, entity.Url, mobile, smsbody);
if (sendentity.ReturnMsg.Contains(","))
{
string[] temp = sendentity.ReturnMsg.Split(',');
if (temp[0] == "1")
{
break;
}
}
}
else if (entity.Name.ToLower() == SMSProviders.ChuangShiManDao.ToLower())
{
sendentity.ReturnMsg = ChuangShiManDaoSend(entity.AppId, entity.PassWord, entity.Url, mobile, smsbody, "1");
if (StringUtils.GetDbLong(sendentity.ReturnMsg) > 0)
{
break;
}
}
}
catch (Exception ex)
{
sendentity.ReturnMsg = ex.Message;
}
}
return(sendentity);
}
/// <summary>
/// 读取记录列表。
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="columns">需要返回的列，不提供任何列名时默认将返回所有列</param>
public IList <SMSNoticeEntity> GetSMSNoticeAll()
{
string sql = @"SELECT [Id],[MobilePhone],[SMSContent],[Status],[SendTime],[SystemType] from dbo.[SMSNotice] WITH(NOLOCK) ";
IList <SMSNoticeEntity> entityList = new List <SMSNoticeEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
SMSNoticeEntity entity = new SMSNoticeEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.MobilePhone = StringUtils.GetDbString(reader["MobilePhone"]);
entity.SMSContent = StringUtils.GetDbString(reader["SMSContent"]);
entity.Status = StringUtils.GetDbInt(reader["Status"]);
entity.SendTime = StringUtils.GetDbDateTime(reader["SendTime"]);
entity.SystemType = StringUtils.GetDbInt(reader["SystemType"]);
entityList.Add(entity);
}
}
return(entityList);
}
public int SMSNoticeSendAccess(SMSNoticeEntity entity)
{
return(SMSNoticeDA.Instance.SMSNoticeSendAccess(entity));
}
/// <summary>
/// 读取记录列表。
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="columns">需要返回的列，不提供任何列名时默认将返回所有列</param>
public IList <SMSNoticeEntity> GetSMSNoticeList(int pagesize, int pageindex, ref int recordCount, int systemType, int status)
{
string where = " WHERE 1=1 ";
if (systemType > 0)
{
where += " And SystemType=@SystemType";
}
if (status > -1)
{
where += " And Status=@Status";
}
string sql = @"SELECT [Id],[MobilePhone],[SMSContent],[Status],[SendTime],[SystemType],[CreateTime]
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS ROWNUMBER,
[Id],[MobilePhone],[SMSContent],[Status],[SendTime],[SystemType],[CreateTime] from dbo.[SMSNotice] WITH(NOLOCK)
" + where + @") as temp
where rownumber BETWEEN ((@PageIndex - 1) * @PageSize + 1) AND @PageIndex * @PageSize" ;
string sql2 = @"Select count(1) from dbo.[SMSNotice] with (nolock) " + where;
IList <SMSNoticeEntity> entityList = new List <SMSNoticeEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@PageIndex", DbType.Int32, pageindex);
db.AddInParameter(cmd, "@PageSize", DbType.Int32, pagesize);
if (systemType > 0)
{
db.AddInParameter(cmd, "@SystemType", DbType.Int32, systemType);
}
if (status > -1)
{
db.AddInParameter(cmd, "@Status", DbType.Int32, status);
}
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
SMSNoticeEntity entity = new SMSNoticeEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.MobilePhone = StringUtils.GetDbString(reader["MobilePhone"]);
entity.SMSContent = StringUtils.GetDbString(reader["SMSContent"]);
entity.Status = StringUtils.GetDbInt(reader["Status"]);
entity.CreateTime = StringUtils.GetDbDateTime(reader["CreateTime"]);
entity.SendTime = StringUtils.GetDbDateTime(reader["SendTime"]);
entity.SystemType = StringUtils.GetDbInt(reader["SystemType"]);
entityList.Add(entity);
}
}
cmd = db.GetSqlStringCommand(sql2);
if (systemType > 0)
{
db.AddInParameter(cmd, "@SystemType", DbType.Int32, systemType);
}
if (status > -1)
{
db.AddInParameter(cmd, "@Status", DbType.Int32, status);
}
using (IDataReader reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
recordCount = StringUtils.GetDbInt(reader[0]);
}
else
{
recordCount = 0;
}
}
return(entityList);
}
/// <summary>
/// 判断对象是否存在
/// </summary>
/// <param name="dicEnum"></param>
/// <returns></returns>
public bool IsExist(SMSNoticeEntity sMSNotice)
{
return(SMSNoticeDA.Instance.ExistNum(sMSNotice) > 0);
}
/// <summary>
/// 更新一条SMSNotice记录。
/// 该方法提供给界面等UI层调用
/// </summary>
/// <param name="sMSNotice">待更新的实体对象</param>
/// <param name="columns">要更新的列名，不提供任何列名时默认将更新主键之外的所有列</param>
public int UpdateSMSNotice(SMSNoticeEntity sMSNotice)
{
return(SMSNoticeDA.Instance.UpdateSMSNotice(sMSNotice));
}
