smsinfo.cs

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

                "SMSInfo.SMSID = SMSSendUserList.SMSID INNER JOIN OperationUser ON SMSInfo.SendMsgUserID =  " +
                "OperationUser.OperationUserID WHERE (SMSInfo.Flag = 2) AND (SMSInfo.IsValid <> 0) AND  " +
                "(SMSInfo.SendMsgUserID = {0}) ", userID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        /// <summary>
        /// 根据日期发送
        /// </summary>
        /// <param name="smsDate"></param>
        /// <returns></returns>
        public DataTable GetSendSMSDater(string  smsDate)
        {
            String strSql = string.Format("SELECT DISTINCT SMSInfo.SMSID AS smsid, SMSInfo.MessageContent, " +
                "OperationUser.UserName, SMSInfo.SMSDate,  SMSInfo.CountSMS, SMSInfo.CountLongNumSMS " +
                "FROM SMSInfo INNER JOIN SMSSendUserList ON  " +
                "SMSInfo.SMSID = SMSSendUserList.SMSID INNER JOIN OperationUser ON SMSInfo.SendMsgUserID =  " +
                "OperationUser.OperationUserID WHERE (SMSInfo.Flag = 2)  and(SMSInfo.IsValid <> 0) AND  " +
                "(SMSInfo.SMSDate like '{0}%')", smsDate);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetSendSMSInfo(int smsType, string smsDate)
        {
            String strSql = string.Format("SELECT DISTINCT SMSInfo.SMSID AS smsid, SMSInfo.MessageContent, " +
               "OperationUser.UserName, SMSInfo.SMSDate,  SMSInfo.CountSMS, SMSInfo.CountLongNumSMS " +
               "FROM SMSInfo INNER JOIN SMSSendUserList ON  " +
               "SMSInfo.SMSID = SMSSendUserList.SMSID INNER JOIN OperationUser ON SMSInfo.SendMsgUserID =  " +
               "OperationUser.OperationUserID WHERE (SMSInfo.Flag = 2) and (SMSInfo.IsValid <> 0)and (SMSInfo.SMSDate like '{1}%') AND (SMSInfo.SMSType = {0})", smsType,smsDate);
                   
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetMostAreadySendSMS(int smsType)
        {
            String strSql = string.Format("SELECT DISTINCT SMSInfo.SMSID AS smsid, SMSInfo.MessageContent, " +
                "OperationUser.UserName, SMSInfo.SMSDate,  SMSInfo.CountSMS, SMSInfo.CountLongNumSMS " +
                "FROM SMSInfo INNER JOIN SMSSendUserList ON  " +
                "SMSInfo.SMSID = SMSSendUserList.SMSID INNER JOIN OperationUser ON SMSInfo.SendMsgUserID =  " +
                "OperationUser.OperationUserID WHERE (SMSInfo.Flag = 2) AND (SMSInfo.IsValid <> 0) AND  " +
                "(SMSInfo.SMSType = {0})", smsType);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetMostSMS()
        {
            string strSql = "SELECT SMSInfo.SMSID, CASE WHEN (UserInfo.UserFunction) = 0 THEN '家校通用户' WHEN (UserFunction) " +
            "= 1 THEN '考勤用户' ELSE '双向用户' END AS UserFunction, UserInfo.CompanyName, UserInfo.SchoolName, UserInfo.Branch, " +
            "UserInfo.GradeName, UserInfo.Dept, UserInfo.ClassName, UserInfo.Organise, UserInfo.Name, UserInfo.Mobile, " +
            "SMSInfo.MessageContent, SMSInfo.Date FROM SMSInfo INNER JOIN UserInfo ON UserInfo.PK_ID = SMSInfo.UserID " +
            "WHERE (SMSInfo.IsValid <> 0)";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }




        public String GetDateBySMSID(int smsID)
        {
            String strSQL = string.Format("SELECT SMSInfo.Date FROM SendObject INNER JOIN SMSInfo ON SendObject.SmsID = SMSInfo.SMSID " +
                "WHERE(SendObject.SmsID = {0})", smsID);
            return db.ExecuteScalar(CommandType.Text, strSQL).ToString();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="userTypeID"></param>
        /// <param name="gradeName"></param>
        /// <param name="className"></param>
        /// <param name="userID"></param>
        /// <param name="tradeID">不传值的情况下直接传4</param>
        /// <returns></returns>
        public DataTable GetMobileList(String city,String county,String village,String operationType,
            String Particular,String organise,int trade)
        {
            String strSql = "SELECT UserInfo.Mobile, UserInfo.AnswerForCompany FROM UserInfo " +
                "INNER JOIN UserType ON UserInfo.UserTypeID = UserType.UserTypeID WHERE (1=1) ";
            if (city != "")
                strSql += string.Format(" AND (UserType.TypeName = '{0}')", city);
            if (county != "")
                strSql += string.Format(" AND (UserInfo.SchoolName = '{0}')", county);
            if (village != "")
                strSql += string.Format(" AND (UserInfo.GradeName = '{0}')", village);
            if (operationType != "")
                strSql += string.Format(" AND (UserInfo.ClassName = '{0}')", operationType);
            if (Particular != "")
                strSql += string.Format(" AND (UserInfo.Duty = '{0}')", Particular);
            if (organise != "")
                strSql += string.Format(" AND (UserInfo.Organise = '{0}')", organise);
            if (trade != 4)
                strSql += string.Format(" AND (UserInfo.AnswerForCompany = {0})", trade);

            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        /// <summary>
        /// SMSType用来标识是学校短信还是农村短信类型　学校短信为0,农村短信为1
        /// </summary>
        /// <param name="msgContent"></param>
        /// <param name="mobile"></param>
        /// <param name="trade"></param>
        /// <returns></returns>
        public int SendingSMS(String msgContent, String mobile, int trade)
        {
            String strSql = string.Format("INSERT INTO SendingSMS(MessageContent,Mobile,Trade,SMSType) " +
                "SELECT '{0}' AS MessageContent, '{1}' AS Mobile, {2} AS Trade, 1 AS SMSType", msgContent, mobile, trade);
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }


        public string GetMsgContentAndDateBySMSID(int smsID)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public String GetMsgContentBySMSID(int smsID)
        {
            String strSql = String.Format("SELECT MessageContent FROM SMSInfo WHERE (SMSID = {0})", smsID);
            object obj = db.ExecuteScalar(CommandType.Text, strSql);
            try
            {
                return obj.ToString();
            }
            catch
            {
                return "";
            }

        }
        public String GetTimeBySMSID(int smsID)
        {
            String strSql = String.Format("SELECT Time FROM SMSInfo WHERE (SMSID = {0})", smsID);
            object obj = db.ExecuteScalar(CommandType.Text, strSql);
            try
            {
                return obj.ToString();
            }
            catch
            {
                return "";
            }

        }

        public DataTable GetSingleSMSInfoToSend(int smsID)
        {
            String strSql = String.Format("SELECT SMSInfo.SMSID, SMSInfo.MessageContent, SMSInfo.AreaID, " +
                "SMSCorporation.CorporationID, SMSInfo.Name, SMSInfo.Mobile FROM SMSInfo INNER JOIN " +
                "SMSCorporation ON SMSInfo.SMSID = SMSCorporation.SMSID WHERE SMSInfo.SMSID = {0}", smsID);
            return db.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        public DataTable GetSingleSubmitSMSInfo(int smsID)
        {
            String strSql = String.Format("SELECT SMSInfo.SMSDate, OperationUser.UserName, SMSInfo.MessageContent " +
                "FROM SMSInfo INNER JOIN OperationUser ON SMSInfo.SubmitUserID = OperationUser.OperationUserID " +
                "WHERE (SMSInfo.SMSID = {0})", smsID);
            return db.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        public DataTable GetSingleAreadySMSInfo(int smsID)
        {
            String strSql = String.Format("SELECT SMSInfo.SMSDate, OperationUser.UserName, SMSInfo.MessageContent " +
                "FROM SMSInfo INNER JOIN OperationUser ON SMSInfo.SendMsgUserID = OperationUser.OperationUserID " +
                "WHERE (SMSInfo.SMSID = {0})", smsID);
            return db.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        public int UpdateCountSMSInfo(int countSMS, int countLongSMS, int smsID)
        {
            String strSql = String.Format("UPDATE SMSInfo SET CountSMS = {0}, CountLongNumSMS = {1} " +
                "WHERE SMSID = {2}", countSMS, countLongSMS, smsID);
            try
            {
                return db.ExecuteNonQuery(CommandType.Text, strSql);
            }
            catch
            {
                return 0;
            }

        }
        /// <summary>
        /// 统计短信量总数
        /// </summary>
        /// <returns></returns>
        public DataTable CountSMS()
        {
            String strSql = "SELECT MainSMSTypeInfo.MainSMSTypeName, SMSTypeInfo.SMSTypeName, " +
                "SMSInfo.SMSID, SMSInfo.MessageContent, SMSInfo.CountSMS, SMSInfo.CountLongNumSMS, SMSInfo.SMSDate " +
                "FROM SMSInfo INNER JOIN SMSTypeInfo ON SMSInfo.SMSType = SMSTypeInfo.SMSTypeID INNER JOIN " +
                "MainSMSTypeInfo ON MainSMSTypeInfo.MainSMSTypeInfoID = SMSTypeInfo.MainSMSTypeInfoID " +
                "WHERE (SMSInfo.IsValid <> 0) AND (SMSTypeInfo.IsValid <> 0) AND (MainSMSTypeInfo.IsValid <> 0) AND " +
                "(SMSInfo.Flag = 2) GROUP BY MainSMSTypeInfo.MainSMSTypeName, SMSTypeInfo.SMSTypeName, SMSInfo.SMSID, " +
                "SMSInfo.MessageContent, SMSInfo.CountSMS, SMSInfo.CountLongNumSMS, SMSInfo.SMSDate";
            return db.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }

        public DataTable CountSMSByDate(String beginDate, String endDate)
        {
            String strSql = String.Format("SELECT MainSMSTypeInfo.MainSMSTypeName, SMSTypeInfo.SMSTypeName, " +
                "SMSInfo.SMSID, SMSInfo.MessageContent, SMSInfo.CountSMS, SMSInfo.CountLongNumSMS, SMSInfo.SMSDate " +
                "FROM SMSInfo INNER JOIN SMSTypeInfo ON SMSInfo.SMSType = SMSTypeInfo.SMSTypeID INNER JOIN " +
                "MainSMSTypeInfo ON MainSMSTypeInfo.MainSMSTypeInfoID = SMSTypeInfo.MainSMSTypeInfoID " +
                "WHERE (SMSInfo.IsValid <> 0) AND (SMSTypeInfo.IsValid <> 0) AND (MainSMSTypeInfo.IsValid <> 0) AND " +
                "(SMSInfo.Flag = 2)  AND (SMSInfo.SMSDate between '{0}' and '{1}') GROUP BY MainSMSTypeInfo.MainSMSTypeName, " +
                "SMSTypeInfo.SMSTypeName, SMSInfo.SMSID,SMSInfo.MessageContent, SMSInfo.CountSMS, SMSInfo.CountLongNumSMS, " +
                "SMSInfo.SMSDate", beginDate, endDate);
            return db.ExecuteDataSet(CommandType.Text, strSql).Tables[0];

        }

        //public DataTable CountSMSreplyByDate(String beginDate, String endDate)
        //{
        //    String strSql = String.Format("select Deliver.MsCentent,Deliver.Mobile,Deliver.DeliverTime from Deliver where DeliverTime between '{0}' and '{1}'", beginDate, endDate);
        //    return db.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        //}

        /// <summary>
        /// 已发短信存入SendSMS表中，便于统计短信数量
        /// </summary>
        /// <param name="mobile"></param>
        /// <param name="msgconten"></param>
        /// <param name="trade"></param>
        /// <returns></returns>
        public int Send(String mobile, String msgconten, int trade)
        {
            String strSql = String.Format("INSERT INTO SendSMS(Mobile, MessContent,TradeId,SendTime) VALUES('{0}','{1}','{2}','{3}');SELECT @@IDENTITY AS SmsID", mobile, msgconten, trade, System.DateTime.Now.ToString());
            return db.ExecuteNonQuery(CommandType.Text, strSql);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int GetCountSMS()// WHERE (SMSID = {0})", smsID
        {

            String strSql = String.Format("select  count(*)  from dbo.SendSMS  ");
            object obj = db.ExecuteScalar(CommandType.Text, strSql);
            return int.Parse(obj.ToString());

        }
        /// <summary>
        /// 得到可以发条数
        /// </summary>
        /// <returns></returns>
        public int GetCount()
        {
            String strSql = String.Format("select Count from dbo.CountSMS");
            object obj = db.ExecuteScalar(CommandType.Text, strSql);
            return int.Parse(obj.ToString());
        }
    }

}