📄 smsinfo.cs
字号:
}
/// <summary>
///
/// </summary>
/// <param name="smsID"></param>
/// <param name="flag"></param>
/// <param name="operationUserID"></param>
/// <returns></returns>
public int ChangeSMSFlag(int smsID, int flag,int operationUserID,string data,string sendtype)
{
String changeDateTime = System.DateTime.Now.ToString();
String strSql = string.Format("UPDATE SMSInfo SET Flag = {0} , SubmitUserID = {1} , SMSDate = '{2}',Time='{4}',sendtype='{5}' WHERE (SMSID = {3})",
flag, operationUserID, changeDateTime, smsID,data ,sendtype);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
public int ChangeSMSFlagAndSetSendManID(int smsID, int sendUserID)
{
String changeDateTime = System.DateTime.Now.ToString();
String strSql = string.Format("UPDATE SMSInfo SET Flag = 2 , SendMsgUserID = {0}, SMSDate = '{1}' WHERE (SMSID = {2})",
sendUserID, changeDateTime, smsID);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
/// <summary>
/// 得到单条已发短信信息，不含协会和业务类型
/// </summary>
/// <param name="smsID"></param>
/// <returns></returns>
public DataTable GetSingleSMSInfo(int smsID)
{
String strSql = string.Format("SELECT SMSInfo.SMSID, Area.AreaName, SMSInfo.MessageContent, OperationUser.UserName, SMSInfo.SMSDate "+
"FROM SMSInfo INNER JOIN Area ON SMSInfo.AreaID = Area.AreaID INNER JOIN "+
"OperationUser ON SMSInfo.SendMsgUserID = OperationUser.OperationUserID INNER JOIN "+
"SMSCorporation ON SMSInfo.SMSID = SMSCorporation.SMSID INNER JOIN "+
"Corporation ON SMSCorporation.CorporationID = Corporation.CorporationID WHERE (SMSInfo.SMSID = {0})", smsID);
DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
return ds.Tables[0];
}
public DataTable GetContent(int smsID)
{
string strSQL = string.Format("select MessageContent,SendType,Time,Mobile from SMSInfo where (SMSID={0})",smsID);
DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
return ds.Tables[0];
}
public DataTable GetSMSContentInfo(int smsID)
{
string strSQL = string.Format("SELECT MessageContent,AreaID, CountyID, mobile, TradeID,SMSType FROM SMSInfo WHERE (SMSID = {0})", smsID);
DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
return ds.Tables[0];
}
/// <summary>
/// 获取定时发送的短信
/// </summary>
/// <returns></returns>
public DataTable GetSMSInfoBySendType()
{
String strSql = string.Format("select * from SMSInfo where SendType=1 and Flag = 2 and Time >=GetDate() and IsValid=1 ORDER BY SMSID DESC");
DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
return ds.Tables[0];
}
public int AddSMSInfo(SMSInfoConfig SMSInfoConf)
{
DbCommand insertCommand = db.GetStoredProcCommand("AddSMSInfo");
db.AddInParameter(insertCommand, "@UserID", DbType.Int32, SMSInfoConf.UserID);
db.AddInParameter(insertCommand, "@OperationUserID", DbType.Int32, SMSInfoConf.OperationUserID);
db.AddInParameter(insertCommand, "@MessageContent", DbType.String, SMSInfoConf.MessageContent);
db.AddInParameter(insertCommand, "@SMSType", DbType.Int32, SMSInfoConf.SMSType);
db.AddInParameter(insertCommand, "@Flag", DbType.Int32, SMSInfoConf.Flag);
db.AddInParameter(insertCommand, "@Date", DbType.String, SMSInfoConf.Date);
return db.ExecuteNonQuery(insertCommand);
}
/// <summary>
/// 保存待发短信
/// </summary>
/// <param name="SMSInfoConf"></param>
/// <returns></returns>
public int AddReadySMS(SMSInfoConfig SMSInfoConf)
{
string strSql = string.Format("INSERT INTO SMSInfo(MessageContent, SMSType, Flag, Date) VALUES ('{0}',{1},{2},'{3}')",
SMSInfoConf.MessageContent, SMSInfoConf.SMSType, SMSInfoConf.Flag, SMSInfoConf.Date);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
/// <summary>
/// 查看待提交短信
/// </summary>
/// <param name="SMSType"></param>
/// <returns></returns>
public DataTable GetReadySMS(int SMSType)
{
string strSql = string.Format("SELECT SMSID,Date, MessageContent FROM SMSInfo WHERE (SMSType = {0}) AND (Flag = 0) AND (IsValid <> 0)",
SMSType);
DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
return ds.Tables[0];
}
public DataTable GetSMSInfoByID(int smsID)
{
string strSQL = string.Format("SELECT MessageContent FROM SMSInfo WHERE SMSID = {0}", smsID);
DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
return ds.Tables[0];
}
public void AddSMSInfo(SMSInfoDefine SMSInfoDefineDataSet, SMSInfoConfig SMSInfoConf)
{
DataRow row = SMSInfoDefineDataSet.Tables["SMSInfo"].NewRow();
row["UserID"] = SMSInfoConf.UserID;
row["OperationUserID"] = SMSInfoConf.OperationUserID;
row["MessageContent"] = SMSInfoConf.MessageContent;
row["SMSType"] = SMSInfoConf.SMSType;
row["Falg"] = SMSInfoConf.Flag;
row["Date"] = SMSInfoConf.Date;
SMSInfoDefineDataSet.Tables["SMSInfo"].Rows.Add(row);
}
/// <summary>
/// 将短信标记为已发短信
/// </summary>
/// <param name="smsID"></param>
/// <returns></returns>
public int UpdateSMSToAreadySMS(int smsID,String sendMan)
{
string strSql = string.Format("UPDATE SMSInfo SET Flag = 2,SendMan = '{0}' WHERE(SMSID = {1})", sendMan,smsID);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
public int UpdateSMSFlag(int smsID, int changeValue,String sendMan)
{
String strSql = string.Format("UPDATE SMSInfo SET Flag = {0}, SendMan = '{1}' WHERE(SMSID = {2})", changeValue, sendMan, smsID);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
public int DeleteSMSInfo(int SMSID)
{
string strSql = string.Format("UPDATE SMSInfo Set IsValid = 0 WHERE (SMSID = {0})", SMSID);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
public int UpdateSMSInfo(SMSInfoConfig SMSInfoConf)
{
DbCommand command = db.GetStoredProcCommand("UpdateSMSInfo");
db.AddInParameter(command, "@SMSID", DbType.Int32, SMSInfoConf.SMSID);
db.AddInParameter(command, "@UserID", DbType.Int32, SMSInfoConf.UserID);
db.AddInParameter(command, "@OperationUserID", DbType.Int32, SMSInfoConf.OperationUserID);
db.AddInParameter(command, "@MessageContent", DbType.String, SMSInfoConf.MessageContent);
db.AddInParameter(command, "@SMSType", DbType.Int32, SMSInfoConf.SMSType);
db.AddInParameter(command, "@Flag", DbType.Int32, SMSInfoConf.Flag);
db.AddInParameter(command, "@Date", DbType.String, SMSInfoConf.Date);
return db.ExecuteNonQuery(command);
}
public int UpdateSMSInfo(string sms, int smsID)
{
string strSql = string.Format("UPDATE SMSInfo Set MessageContent = '{0}' WHERE SMSID = '{1}'", sms, smsID);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
/// <summary>
/// 按运营商发送
/// </summary>
/// <param name="operationUserID"></param>
/// <param name="sms"></param>
/// <param name="smsType"></param>
/// <param name="smsFlag"></param>
/// <param name="userFunction"></param>
/// <returns></returns>
public int SubmisionSMSByTrade(int operationUserID, string sms, int smsType, int smsFlag, int answerForCompany)
{
string strSql = string.Format("INSERT INTO SMSInfo(UserID, OperationUserID, MessageContent, SMSType, Flag, Date) " +
"SELECT PK_ID AS UserID, {0} AS OperationUserID, '{1}' AS MessageContent, {2} AS SMSType, {3} AS Flag, " +
"'{4}' AS Date FROM UserInfo WHERE (IsValid <> 0) AND AnswerForCompany = {5}", operationUserID, sms, smsType,
smsFlag, System.DateTime.Now.ToString(), answerForCompany);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
/// <summary>
/// 按协会团体发送短信
/// </summary>
/// <param name="operationUserID"></param>
/// <param name="sms"></param>
/// <param name="smsType"></param>
/// <param name="smsFlag"></param>
/// <param name="organise"></param>
/// <returns></returns>
public int SubmisionSMSByOrganise(int operationUserID, string sms, int smsType, int smsFlag, string organise)
{
string strSql = string.Format("INSERT INTO SMSInfo(UserID, OperationUserID, MessageContent, SMSType, Flag, Date) " +
"SELECT PK_ID AS UserID, {0} AS OperationUserID, '{1}' AS MessageContent, {2} AS SMSType, {3} AS Flag, " +
"'{4}' AS Date FROM UserInfo WHERE (IsValid <> 0) AND Organise = '{5}'", operationUserID, sms, smsType,
smsFlag, System.DateTime.Now.ToString(), organise);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
/// <summary>
/// 根据地区发送短信
/// </summary>
/// <param name="operationUserID"></param>
/// <param name="sms"></param>
/// <param name="smsType"></param>
/// <param name="smsFlag"></param>
/// <param name="userTypeID"></param>
/// <param name="sql"></param>
/// <returns></returns>
public int SubmissionByArea(int operationUserID, string sms, int smsType, int smsFlag,
string sql)
{
string strSql = string.Format("INSERT INTO SMSInfo(UserID, OperationUserID, MessageContent, " +
"SMSType, Flag, Date) SELECT PK_ID AS UserID, {0} AS OperationUserID, '{1}' " +
"AS MessageContent, {2} AS SMSType, {3} AS Flag, '{4}' AS Date FROM UserInfo " +
"WHERE (IsValid <> 0) {5}", operationUserID, sms, smsType,
smsFlag, System.DateTime.Now.ToString(),sql);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
/// <summary>
/// 按短信类型发送
/// </summary>
/// <param name="operationUserID"></param>
/// <param name="sms"></param>
/// <param name="smsType"></param>
/// <param name="smsFlag"></param>
/// <param name="className"></param>
/// <param name="sql"></param>
/// <returns></returns>
public int SubmissionBySMSType(int operationUserID, string sms, int smsType, int smsFlag,
string operationName, string sql)
{
string strSql = string.Format("INSERT INTO SMSInfo(UserID, OperationUserID, MessageContent, " +
"SMSType, Flag, Date) SELECT PK_ID AS UserID, {0} AS OperationUserID, '{1}' " +
"AS MessageContent, {2} AS SMSType, {3} AS Flag, '{4}' AS Date FROM UserInfo " +
"WHERE (IsValid <> 0) AND (ClassName = '{5}') {6}", operationUserID, sms, smsType,
smsFlag, System.DateTime.Now.ToString(), operationName, sql);
return db.ExecuteNonQuery(CommandType.Text, strSql);
}
/// <summary>
/// 得到所有的待审核短信
/// </summary>
/// <returns></returns>
public DataTable GetMostSubmisionSMS(int smsType)
{
String strSql = string.Format("SELECT SendObject.SMSID, SendObject.City, SendObject.County, SendObject.Organise, " +
"SMSInfo.MessageContent, SMSInfo.SendMan, SMSInfo.Date FROM SMSInfo INNER JOIN SendObject " +
"ON SMSInfo.SMSID = SendObject.SMSID WHERE (SMSInfo.IsValid <> 0) AND (SMSInfo.Flag = 1) AND (SMSInfo.SMSType = {0})", smsType);
DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
return ds.Tables[0];
}
/// <summary>
/// 根据发送人姓名查询
/// </summary>
/// <param name="userN"></param>
/// <returns></returns>
public DataTable GetSendSMSUser(int userID)
{
String strSql = string.Format("SELECT DISTINCT SMSInfo.SMSID AS smsid,SMSInfo.SendMsgUserID, SMSInfo.MessageContent, " +
"OperationUser.UserName, SMSInfo.SMSDate, SMSInfo.CountSMS, SMSInfo.CountLongNumSMS " +
"FROM SMSInfo INNER JOIN SMSSendUserList ON " +
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -