smsinfo.cs

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

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using System.Data.Common;
using DataAccess.TableDefine;
using DataAccess.TableAccessor;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DataAccess.TableAccessor
{
    public class SMSInfo
    {
        private Database db = null;

        public SMSInfo()
        {
            db = DatabaseFactory.CreateDatabase("Connection String");
        }
        /// <summary>
        /// 添加编辑短信
        /// </summary>
        /// <param name="smsTypeID"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int AddEditSMS(int smsTypeID, String msg)
        {
            String strSql = string.Format("INSERT INTO SMSInfo(MessageContent, SMSType, Flag, SMSDate) VALUES ('{0}',{1},{2},'{3}');SELECT @@IDENTITY AS SMSID",
                msg, smsTypeID, 0, System.DateTime.Now.ToString());
            // db.ExecuteNonQuery(CommandType.Text, strSql);

            object obj = null;
            try
            {
                obj = db.ExecuteScalar(CommandType.Text, strSql);
            }
            catch
            { }
            if (obj != null)
                return int.Parse(obj.ToString());
            else
                return 0;
            //db.ExecuteReader(CommandType.Text, strSql);        

        }
        /// <summary>
        /// 根据编号获取短信
        /// </summary>
        /// <param name="smsID"></param>
        /// <returns></returns>
        public DataTable GetSMSContent(int smsID)
        {
          string   strSql = string.Format("select * from SMSInfo where IsValid=1 and SMSID={0}", smsID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 更新短信
        /// </summary>
        /// <param name="smsTypeID"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int UpdateEditSMS(int smsTypeID, String msg)
        {
            String strSql = string.Format("UPDATE SMSInfo SET MessageContent = '{0}' WHERE SMSTypeID = {1}", msg, smsTypeID );
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }

        ///// <summary>
        ///// 获取短信编号
        ///// </summary>
        ///// <param name="smsID"></param>
        ///// <returns></returns>
        public int GetSMSId()
        {
            String strSql = string.Format("SELECT max(SMSID) from SMSInfo where IsValid=1");
            object obj = null;
            try
            {
                obj = db.ExecuteScalar(CommandType.Text, strSql);
            }
            catch
            { }
            if (obj != null)
                return int.Parse(obj.ToString());
            else
                return 0;
        }

        /// <summary>
        /// 得到编辑短信信息
        /// </summary>
        /// <param name="smsTypeID">短信类型编号</param>
        /// <returns></returns>
        public DataTable GetEditSMSInfo(int smsTypeID)
        {
            String strSql = string.Format("SELECT MessageContent,SMSDate,SMSID FROM SMSInfo WHERE (SMSType = {0}) AND IsValid <> 0 AND Flag = 0", smsTypeID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        /// <summary>
        /// 审核编辑短信
        /// </summary>
        /// <param name="smsID"></param>
        /// <param name="submitUserID"></param>
        /// <param name="areaID"></param>
        /// <param name="countyID"></param>
        /// <param name="villageID"></param>
        /// <param name="coporationID"></param>
        /// <returns></returns>
        public int SetEditSMSToSubmitSMS(int smsID,int submitUserID,int areaID,int countyID,int villageID,
            ArrayList coporationID, ArrayList mainOperationID, ArrayList childOperationID, int tradeID)
        {
            String tempSql = "";
            String strSql = string.Format("UPDATE SMSInfo SET SubmitUserID ={0}, AreaID ={1}, CountyID ={2}, VillageID ={3}, " +
                "TradeID ={4}, Flag = 1, SMSDate = '{5}' WHERE SMSID = {6}", submitUserID, areaID, countyID, villageID, 
                tradeID, System.DateTime.Now.ToShortDateString(), smsID);

            String insertSMSOperationSql = "INSERT INTO SMSOperation(SMSID, MainOperationID, ChildOperationID) " +
                "VALUES ({0},{1},{2})";
            String insertSMSCorporationSql = "INSERT INTO SMSCorporation(SMSID, CorporationID) VALUES ({0},{1})";
            for (int i = 0; i < mainOperationID.Count; i++)
            {
                tempSql = String.Format(insertSMSOperationSql, smsID, mainOperationID[i], childOperationID[i]);
                db.ExecuteNonQuery(CommandType.Text, tempSql);
            }

            for (int i = 0; i < coporationID.Count; i++)
            {
                tempSql = String.Format(insertSMSCorporationSql,smsID,coporationID[i]);
                db.ExecuteNonQuery(CommandType.Text, tempSql);
            }

            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }

        public int SetEditSMSToSubmitSMS(int smsID, int submitUserID, int areaID, int countyID, int villageID, string userName, string mobile,
           ArrayList coporationID, ArrayList mainOperationID, ArrayList childOperationID, int tradeID)
        {
            String tempSql = "";
            String strSql = string.Format("UPDATE SMSInfo SET SubmitUserID ={0}, AreaID ={1}, CountyID ={2}, VillageID ={3}, Name = '{4}', mobile = '{5}', " +
                "TradeID ={6}, Flag = 1, SMSDate = '{7}' WHERE SMSID = {8}", submitUserID, areaID, countyID, villageID, userName, mobile,
                tradeID, System.DateTime.Now.ToShortDateString(), smsID);

            String insertSMSOperationSql = "INSERT INTO SMSOperation(SMSID, MainOperationID, ChildOperationID) " +
                "VALUES ({0},{1},{2})";
            String insertSMSCorporationSql = "INSERT INTO SMSCorporation(SMSID, CorporationID) VALUES ({0},{1})";
            for (int i = 0; i < mainOperationID.Count; i++)
            {
                tempSql = String.Format(insertSMSOperationSql, smsID, mainOperationID[i], childOperationID[i]);
                db.ExecuteNonQuery(CommandType.Text, tempSql);
            }

            for (int i = 0; i < coporationID.Count; i++)
            {
                tempSql = String.Format(insertSMSCorporationSql, smsID, coporationID[i]);
                db.ExecuteNonQuery(CommandType.Text, tempSql);
            }

            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }
        /// <summary>
        /// 根据smsTypeID得到所有待审核短信
        /// </summary>
        /// <param name="smsTypeID"></param>
        /// <returns></returns>
        public DataTable GetSubmitSMSInfo(int smsTypeID)
        {
            String strSql = string.Format("SELECT DISTINCT SMSInfo.SMSID AS smsid, SMSInfo.MessageContent, " +
                "OperationUser.UserName,SMSInfo.Mobile, SMSInfo.SMSDate FROM SMSInfo INNER JOIN SMSSendUserList ON " +
                "SMSInfo.SMSID = SMSSendUserList.SMSID INNER JOIN OperationUser ON SMSInfo.SubmitUserID " +
                "= OperationUser.OperationUserID WHERE (SMSInfo.Flag = 1) AND (SMSInfo.IsValid <> 0) AND " +
                "(SMSInfo.SMSType = {0})", smsTypeID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        /// <summary>
        /// 得到所有审核短信
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllSubmitInfo()
        {
            String strSql = string.Format("SELECT DISTINCT SMSInfo.SMSID AS smsid, SMSInfo.MessageContent, " +
               "OperationUser.UserName,SMSInfo.Mobile, SMSInfo.SMSDate FROM SMSInfo INNER JOIN SMSSendUserList ON " +
               "SMSInfo.SMSID = SMSSendUserList.SMSID INNER JOIN OperationUser ON SMSInfo.SubmitUserID " +
               "= OperationUser.OperationUserID WHERE (SMSInfo.Flag = 1) AND (SMSInfo.IsValid <> 0)");
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 得到单条审核短信
        /// </summary>
        /// <param name="smsID"></param>
        /// <returns></returns>
        //public DataTable GetSingleSubmitSMSInfo(int smsID)
        //{
        //    String strSql = String.Format("SELECT Area.AreaName, SMSInfo.TradeID, SMSInfo.SendMsgUserID, OperationUser.UserName, SMSInfo.Name, "+
        //        "SMSInfo.MessageContent, SMSInfo.SMSID, SMSInfo.SMSDate, Corporation.CorporationName FROM SMSCorporation INNER JOIN "+
        //        "SMSInfo INNER JOIN Area ON SMSInfo.AreaID = Area.AreaID INNER JOIN OperationUser ON SMSInfo.SubmitUserID = OperationUser.OperationUserID "+
        //        "ON SMSCorporation.SMSID = SMSInfo.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];
        //}
        /// <summary>
        /// 得到单条短信的业务类型 审核，已发通用
        /// </summary>
        /// <param name="smsID"></param>
        /// <returns></returns>
        public DataTable GetSingleSMSOperationInfo(int smsID)
        {
            String strSql = String.Format("SELECT SMSOperation.SMSOperationID, SMSOperation.SMSID, SMSOperation.MainOperationID, " +
                "SMSOperation.ChildOperationID, MainOperationType.MainOperationName, ChildOperationType.ChildOperationName FROM " +
                "SMSOperation INNER JOIN MainOperationType ON SMSOperation.MainOperationID = MainOperationType.MainOperationID INNER JOIN " +
                "ChildOperationType ON SMSOperation.ChildOperationID = ChildOperationType.ChildOperationID WHERE (SMSOperation.SMSID = {0})", smsID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        /// <summary>
        /// 得到单条短信的组织协会 审核，已发通用
        /// </summary>
        /// <param name="smsID"></param>
        /// <returns></returns>
        public DataTable GetSingleSMSCorporationInfo(int smsID)
        {
            String strSql = String.Format("SELECT SMSCorporation.SMSCorporationID, SMSCorporation.SMSID, SMSCorporation.CorporationID, " +
                "Corporation.CorporationName FROM SMSCorporation INNER JOIN Corporation ON SMSCorporation.CorporationID = " +
                "Corporation.CorporationID WHERE (SMSCorporation.SMSID = {0})", smsID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 删除短信
        /// </summary>
        /// <param name="smsID"></param>
        /// <returns></returns>
        public int DeleteSMS(int smsID)
        {
            String strSql = string.Format("UPDATE SMSInfo SET IsValid = 0 WHERE (SMSID = {0})", smsID);
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }

        public int UpdateSendMan(int smsID, int sendManID)
        {
            string strSQL = string.Format("UPDATE SMSInfo SET SendMsgUserID ={0} WHERE SMSID = {1}", sendManID, smsID);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }
        /// <summary>
        /// 更改短信状态
        /// </summary>
        /// <param name="smsID"></param>
        /// <param name="flag"></param>
        /// <returns></returns>
        public int ChangeSMSFlag(int smsID,int flag)
        {
            String strSql = string.Format("UPDATE SMSInfo SET Flag = {0} WHERE (SMSID = {1})", flag, smsID);
            return db.ExecuteNonQuery(CommandType.Text, strSql);