smsinfo.cs

来自「该服务平台解决了计算机网络与移动网络之间信息交换问题」· CS 代码 · 共 623 行 · 第 1/3 页

            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];
        }

        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 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];
        }
    }

}