smssenduserlist.cs

该服务平台解决了计算机网络与移动网络之间信息交换问题

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DataAccess.TableAccessor
{
    public class SMSSendUserList
    {
        private Database db;

        public SMSSendUserList()
        {
            db = DatabaseFactory.CreateDatabase("Connection String");
        }

        public int AddSMSSendUserList(int smsID, int userID, int custID,string typeName,string mobile,string Name)
        {
            String strSql = String.Format("INSERT INTO SMSSendUserList(SMSID, UserID,CustID,TypeName,Mobile,Name) VALUES ({0},{1},{2},'{3}','{4}','{5}')", smsID, userID,custID,typeName ,mobile ,Name);
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }

        public int DeleteSMSSendUser(int smsID)
        {
            String strSql = String.Format("DELETE SMSSendUserList WHERE SMSSendUserID = {0}", smsID);
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }
        /// <summary>
        /// 获取所有发送对象
        /// </summary>
        /// <param name="SMSID"></param>
        /// <returns></returns>
        public DataTable GetSemdUserList(int SMSID)
        {
            string strSql = string.Format("select * from SMSSendUserList where SMSID ={0}",SMSID);
            return db.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }
        /// <summary>
        /// 根据smsID删除重复的发送对象
        /// </summary>
        /// <param name="smsID"></param>
        /// <returns></returns>
        public int DeleteRepeat(int smsID)
        {
            string strSql = string.Format("delete  from   SMSSendUserList   where SMSID={0} and SMSSendUserID   not   in   ("+
                "select   max(SMSSendUserID)   from   SMSSendUserList   group   by   Mobile  )",smsID);
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }

        //职工
        public DataTable GetSendUserInfo(int SMSID)
        {
            String strSql = String.Format("SELECT UserInfo.UserID, Area.AreaName, Corporation.CorporationName, UserInfo.Name, " +
                "UserInfo.Duty, CASE WHEN (Sex) = 0 THEN '男' WHEN (Sex) = 1 THEN '女' ELSE ' ' END AS Sex, UserInfo.Mobile FROM UserInfo INNER JOIN " +
                "Area ON UserInfo.AreaID = Area.AreaID INNER JOIN SMSSendUserList ON UserInfo.UserID = SMSSendUserList.UserID " +
                "INNER JOIN Corporation ON UserInfo.CountyID = Corporation.CorporationID WHERE (UserInfo.IsValid <> 0) AND " +
                "(SMSSendUserList.SMSID = {0})", SMSID);
            return db.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }
        //客户
        public DataTable GetSendCustInfo(int SMSID)
        {
            String strSql = String.Format("SELECT CustomerInfo.CustomerID, Type.TypeName,CustomerInfo.Name, " +
               "CustomerInfo.Duty, CASE WHEN (Sex) = 0 THEN '男' WHEN (Sex) = 1 THEN '女' ELSE ' ' END AS CustomerInfo.Sex, CustomerInfo.Mobile FROM CustomerInfo INNER JOIN " +
               "Type ON CustomerInfo.TypeID = Type.TypeID INNER JOIN SMSSendUserList ON CustomerInfo.CustomerID = SMSSendUserList.CustID " +
               " WHERE (CustomerInfo.IsValid <> 0) AND " +
               "(SMSSendUserList.SMSID = {0})", SMSID);
            return db.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }
        public int DeleteSendUserInfoBySMSID(int smsID)
        {
            String strSql = String.Format("DELETE SMSSendUserList WHERE SMSID = {0}", smsID);
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }
    }
}