emailboxdao.cs

小型ERP系统源码,修改版本

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Data.SqlClient;
using DLOA.DALFactory;
using DLOA.Model;
using System.Collections.Generic;

namespace DLOA.DAO
{
    /// <summary>
    /// EmailBoxDAO 的摘要说明
    /// </summary>
    public class EmailBoxDAO : DbHelperSQL
    {
        public EmailBoxDAO()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        /// <summary>
        /// 检查用户需要发送邮件的对象是否在本企业中存在
        /// </summary>
        /// <param name="toUser">收件人可以多个，多个之间用“；”号隔开</param>
        /// <returns>验证是否通过</returns>
        public string CheckUser(string toUser)
        {
            string[] arr = toUser.Split(';');
             StringBuilder strSql = null;
            foreach (string str in arr)
            {
                if (str.Trim() != "")
                {
                    strSql = new StringBuilder();
                    strSql.Append("select count(1) from Personnel");
                    strSql.Append(" where id= @id");
                    SqlParameter[] parameters = {
					        new SqlParameter("@id", SqlDbType.Int,4)
				    };
                    parameters[0].Value = str;
                    bool exists =  DbHelperSQL.Exists(strSql.ToString(), parameters);
                    if (!exists)
                    {
                        return "收件人中包含不是本企业的人，请核对候在发送";
                    }                    
                }
            }
            return "";
        }


        /// <summary>
        /// 发送邮件
        /// </summary>
        /// <param name="toUser">收件人可以多个，多个之间用“；”号隔开</param>
        /// <param name="emailModel">邮件实体对象</param>
        /// <param name="listAccess">附件集合</param>
        public void Add(string toUser, EmailBox emailModel, List<EmailAccessories> listAccess)
        {
            SqlConnection con = null;
            SqlTransaction trans =  null;
            SqlCommand cmd  = null;
            try
            {
                con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
                con.Open();
                //启动事务
                trans = con.BeginTransaction();
                cmd = new SqlCommand();
                cmd.Transaction = trans;

                string[] arr = toUser.Split(';');
                StringBuilder strSql = null;
                foreach (string str in arr)
                {
                    if (str.Trim() != "")
                    {
                        //保存邮件信息
                        strSql = new StringBuilder();
                        strSql.Append("insert into EmailBox(");
                        strSql.Append("toUser,fromUser,title,content,datex,type)");
                        strSql.Append(" values (");
                        strSql.Append("@toUser,@fromUser,@title,@content,@datex,@type)");
                        strSql.Append(";select @@IDENTITY");
                        SqlParameter[] parameters = {
					        new SqlParameter("@toUser", SqlDbType.VarChar,50),
					        new SqlParameter("@fromUser", SqlDbType.VarChar,50),
					        new SqlParameter("@title", SqlDbType.VarChar,500),
					        new SqlParameter("@content", SqlDbType.VarChar,8000),
					        new SqlParameter("@datex", SqlDbType.DateTime),
					        new SqlParameter("@type", SqlDbType.VarChar,2)
                        };
                        parameters[0].Value = str;
                        parameters[1].Value = emailModel.fromUser;
                        parameters[2].Value = emailModel.title;
                        parameters[3].Value = emailModel.content;
                        parameters[4].Value = emailModel.datex;
                        parameters[5].Value = emailModel.type;

                        object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
                        //添加附件
                        if (obj != null)
                        {
                            foreach (EmailAccessories acc in listAccess)
                            {
                                strSql = new StringBuilder();
                                strSql.Append("insert into EmailAccessories(");
                                strSql.Append("emailId,path,fileName)");
                                strSql.Append(" values (");
                                strSql.Append("@emailId,@path,@fileName)");
                                SqlParameter[] accParameters = {
					                    new SqlParameter("@emailId", SqlDbType.Int,4),
					                    new SqlParameter("@path", SqlDbType.VarChar,200),
					                    new SqlParameter("@fileName", SqlDbType.VarChar,100)};
                                accParameters[0].Value = Convert.ToInt32(obj);
                                accParameters[1].Value = acc.path;
                                accParameters[2].Value = acc.fileName;
                                DbHelperSQL.GetSingle(strSql.ToString(), accParameters);
                            }
                        }

                    }
                }
                //提交事务
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
            }
            finally
            {
                try { con.Close(); }
                catch { }
                try { cmd.Dispose(); }
                catch { }
                try { trans.Dispose(); }
                catch { }
            }

        }

        /// <summary>
        /// 收邮件
        /// </summary>
        public void ReciveEmail(List<EmailBox> emailList)
        {
            SqlConnection con = null;
            SqlTransaction trans = null;
            SqlCommand cmd = null;
            try
            {
                con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
                con.Open();
                //启动事务
                trans = con.BeginTransaction();
                cmd = new SqlCommand();
                cmd.Transaction = trans;
                StringBuilder strSql = null;
                foreach (EmailBox email in emailList)
                {
                    //保存邮件信息
                    strSql = new StringBuilder();
                    strSql.Append("insert into EmailBox(");
                    strSql.Append("toUser,fromUser,title,content,datex,type)");
                    strSql.Append(" values (");
                    strSql.Append("@toUser,@fromUser,@title,@content,@datex,@type)");
                    strSql.Append(";select @@IDENTITY");
                    SqlParameter[] parameters = {
					        new SqlParameter("@toUser", SqlDbType.VarChar,50),
					        new SqlParameter("@fromUser", SqlDbType.VarChar,50),
					        new SqlParameter("@title", SqlDbType.VarChar,500),
					        new SqlParameter("@content", SqlDbType.VarChar,8000),
					        new SqlParameter("@datex", SqlDbType.DateTime),
					        new SqlParameter("@type", SqlDbType.VarChar,2)
                        };
                    parameters[0].Value = email.toUser;
                    parameters[1].Value = email.fromUser;
                    parameters[2].Value = email.title;
                    parameters[3].Value = email.content;
                    parameters[4].Value = DateTime.Now;
                    parameters[5].Value = "R"; //表示收件
                    object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
                    //添加附件
                    if (obj != null)
                    {
                        foreach (EmailAccessories acc in email.AccList)
                        {
                            strSql = new StringBuilder();
                            strSql.Append("insert into EmailAccessories(");
                            strSql.Append("emailId,path,fileName)");
                            strSql.Append(" values (");
                            strSql.Append("@emailId,@path,@fileName)");
                            SqlParameter[] accParameters = {
					                    new SqlParameter("@emailId", SqlDbType.Int,4),
					                    new SqlParameter("@path", SqlDbType.VarChar,200),
					                    new SqlParameter("@fileName", SqlDbType.VarChar,100)};
                            accParameters[0].Value = Convert.ToInt32(obj);
                            accParameters[1].Value = ""; //没有文件完整路径
                            accParameters[2].Value = acc.fileName;
                            DbHelperSQL.GetSingle(strSql.ToString(), accParameters);
                        }
                    }
                }

                //提交事务
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
            }
            finally
            {
                try { con.Close(); }
                catch { }
                try { cmd.Dispose(); }
                catch { }
                try { trans.Dispose(); }
                catch { }
            }
        }



        /// <summary>
        /// 获得邮件信息
        /// </summary>
        /// <param name="userId">当前用户</param>
        /// <param name="type">获得邮件类型"S"发件箱,"R"为收件箱</param>
        /// <param name="isView">是否已经查看</param>
        /// <returns></returns>
        public DataTable GetReciveEmail(int userId, string type, int isView)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT EmailAccessories.fileName, EmailBox.toUser, EmailBox.fromUser, ");
            strSql.Append(" EmailBox.title, EmailBox.content, EmailBox.datex, EmailBox.isView, ");
            strSql.Append(" EmailBox.id");
            strSql.Append(" FROM EmailAccessories INNER JOIN ");
            strSql.Append("  EmailBox ON EmailAccessories.emailId = EmailBox.id where 1 = 1");
            if (type == "S")
            {
                strSql.Append(" EmailBox.fromUser = "+userId);
            }
            else
            {
                strSql.Append(" EmailBox.toUser = " + userId);
            }
            strSql.Append(" and isView = " + isView);
            return DbHelperSQL.QueryBySql(strSql.ToString());
        }

        /// <summary>
        /// 获得企业邮件
        /// </summary>
        public DataTable GetReciveEmail(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT EmailBox.id, EmailBox.toUser, EmailBox.fromUser,  ");
            strSql.Append(" EmailBox.title, EmailBox.content, EmailBox.datex, EmailBox.type,  ");
            strSql.Append(" EmailBox.isView, Personnel_1.name as fromUserName, Personnel.name AS toUserName ");
            strSql.Append(" FROM EmailBox INNER JOIN ");
            strSql.Append(" Personnel ON EmailBox.toUser = Personnel.id INNER JOIN ");
            strSql.Append(" Personnel Personnel_1 ON EmailBox.fromUser = Personnel_1.id ");
            if (strWhere != "")
            {
                strSql.Append("where " + strWhere);
            }
            strSql.Append(" order by EmailBox.datex desc ");
            return DbHelperSQL.QueryBySql(strSql.ToString());
        }

        public DataTable GetReciveEmail(int emailId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT EmailBox.id, EmailBox.toUser, EmailBox.fromUser,  ");
            strSql.Append(" EmailBox.title, EmailBox.content, EmailBox.datex, EmailBox.type,  ");
            strSql.Append(" EmailBox.isView, Personnel_1.name as fromUserName, Personnel.name AS toUserName, ");
            strSql.Append(" EmailAccessories.path,EmailAccessories.fileName ");
            strSql.Append(" FROM EmailBox INNER JOIN ");
            strSql.Append(" Personnel ON EmailBox.toUser = Personnel.id INNER JOIN ");
            strSql.Append(" Personnel Personnel_1 ON EmailBox.fromUser = Personnel_1.id ");
            strSql.Append(" Left Join EmailAccessories on EmailAccessories.emailId = EmailBox.id");
            strSql.Append(" where EmailBox.id = " + emailId);
            return DbHelperSQL.QueryBySql(strSql.ToString());
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(string id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete EmailBox ");
            strSql.Append(" where id in ("+id+") ");
          
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }
    }
}