OA系统

开发平台：
ASP/ASPX

mail_list_receive.aspx.cs：源码内容
							﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
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.Data.SqlClient;
public partial class web_mail_mail_list_receive : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        LoginDAO.CheckLogin(Session, Response, "../", 2);
        if (!IsPostBack)
        {
            //設置主鍵
            MailList.DataKeyNames = new string[] { "id" };
            if (Request.QueryString["r_id"] != null)
            {
                PnlMail.Visible = true;
                this.readMail(Request.QueryString["r_id"]);
                this.setMail(Request.QueryString["r_id"]);
            }
        }
    }
    protected void readMail(string id)
    {
        DateMgr mgr = new DateMgr();
        string time = mgr.getDateTime();
        SqlConnection conn = dbConnection.getConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand("update OA_MAIL_RECEIVER set is_read=1, read_date='"+
            time+"' where id="+id, conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        MailList.DataBind();
    }
    protected void setMail(string r_id)
    {
        try
        {
            SqlConnection conn = dbConnection.getConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT r.mail_id, m.title, m.content, s.send_date, "+
                "e.id, e.emp_no, e.emp_name, p.name position FROM dbo.OA_MAIL_RECEIVER AS r INNER JOIN "+
                "dbo.OA_MAIL_SENDER AS s ON r.mail_id = s.mail_id INNER JOIN dbo.OA_EMPLOYEE AS e "+
                "ON s.sender = e.id INNER JOIN dbo.OA_MAIL_CONTENT AS m ON r.mail_id = m.id inner "+
                "join OA_EMPLOYEE_POSITION p on e.position=p.id WHERE r.id="+r_id, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            string mail_id = "0";
            if (dr.Read())
            {
                mail_id = dr["mail_id"].ToString();
                LblTitle.Text = dr["title"].ToString();
                TxtContent.Text = dr["content"].ToString().Replace("<br>", "n").Replace("&nbsp;"," ");
                LblSendDate.Text = dr["send_date"].ToString();
                SenderId.Value = dr["id"].ToString();
                LblSender.Text = dr["emp_no"].ToString() + " " + dr["emp_name"].ToString() + dr["position"].ToString();
            }
            dr.Close();
            cmd = new SqlCommand("select e.id, e.emp_no, e.emp_name, p.name from OA_MAIL_RECEIVER r inner join " +
                "OA_EMPLOYEE e on r.receiver=e.id inner join OA_EMPLOYEE_POSITION p on e.position=p.id "+
                "where mail_id="+mail_id, conn);
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ReceiverIds.Value = ReceiverIds.Value + dr["id"].ToString() + ";";
                LblReceivers.Text = LblReceivers.Text + dr["emp_no"].ToString() + " " + 
                    dr["emp_name"].ToString() + dr["name"].ToString() + "; ";
            }
            dr.Close();
            if(!LblReceivers.Text.Equals(""))
            {
                LblReceivers.Text = LblReceivers.Text.Substring(0, LblReceivers.Text.Length - 2);
            }
            cmd = new SqlCommand("select cn_name, en_name, content_length from OA_MAIL_ATTACHMENT where mail_id=" + mail_id, conn);
            dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                BtnAttachment.Text = dr["cn_name"].ToString();
                AttachmentEnName.Value = dr["en_name"].ToString();
                ContentLength.Value = dr["content_length"].ToString();
            }
            else
            {
                //沒有附件
                LblNoAttachment.Visible = true;
                BtnAttachment.Visible = false;
            }
            dr.Close();
            conn.Close();
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }
    protected void MailList_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        //行高亮
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Attributes.Add("onmouseover", "this.style.cursor='pointer';");
            e.Row.Attributes.Add("onclick", "location.href='mail_list_receive.aspx?r_id=" + MailList.DataKeys[e.Row.RowIndex].Value.ToString() + "'");
        }
    }
    protected void MailList_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            LinkButton btnDelete = (LinkButton)e.Row.FindControl("BtnDelete");
            btnDelete.CommandArgument = MailList.DataKeys[e.Row.RowIndex].Value.ToString();
            SqlConnection conn = dbConnection.getConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand("select is_read from OA_MAIL_RECEIVER where id=" + MailList.DataKeys[e.Row.RowIndex].Value.ToString(), conn);
            int is_read = Convert.ToInt32(cmd.ExecuteScalar());
            Label label = (Label)e.Row.FindControl("LblState");
            if (is_read == 1)
            {
                label.Text = "已讀";
                label.ForeColor = System.Drawing.Color.Blue;
            }
            else
            {
                label.Text = "未讀";
                label.ForeColor = System.Drawing.Color.Red;
            }
            conn.Close();
        }
    }
    protected void MailList_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("DeleteData"))
        {
            string id = e.CommandArgument.ToString();
            //點擊刪除按鈕
            try
            {
                SqlConnection conn = dbConnection.getConnection();
                conn.Open();
                SqlCommand cmd = new SqlCommand("update OA_MAIL_RECEIVER set is_del=1 where id=" + id, conn);
                cmd.ExecuteNonQuery(); 
                MailList.DataBind();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }
    }
    protected void BtnAttachment_Click(object sender, EventArgs e)
    {
        this.DownLoadFile(AttachmentEnName.Value);
    }
    protected void DownLoadFile(string name)
    {
        string directory = "\web\oa_upload\mail\";
        try
        {
            Response.Clear();
            Response.ClearHeaders();
            Response.Buffer = false;
            Response.ContentType = "application/octet-stream";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(BtnAttachment.Text, System.Text.Encoding.UTF8));
            Response.AppendHeader("Content-Length", ContentLength.Value);
            Response.WriteFile(directory + name);
            Response.Flush();
            Response.End();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }
    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        SqlConnection conn = dbConnection.getConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand("update OA_MAIL_RECEIVER set is_del=1 where id=" + Request.QueryString["r_id"], conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        MailList.DataBind();
        PnlMail.Visible = false;
    }
    protected void BtnReply_Click(object sender, EventArgs e)
    {
        TxtReplyReceiver.Text = LblSender.Text;
        TxtReplyTitle.Text = "RE:" + LblTitle.Text;
        TxtReplyContent.Text = "nnn--------------------------------------------------------n";
        TxtReplyContent.Text = TxtReplyContent.Text + "From:" + LblSender.Text + "n";
        TxtReplyContent.Text = TxtReplyContent.Text + "Sent:" + LblSendDate.Text + "n";
        TxtReplyContent.Text = TxtReplyContent.Text + "To:" + LblReceivers.Text + "n";
        TxtReplyContent.Text = TxtReplyContent.Text + "Subject:" + LblTitle.Text + "nn";
        TxtReplyContent.Text = TxtReplyContent.Text + TxtContent.Text+"nn";
        if (!BtnAttachment.Text.Equals(""))
        {
            TxtReplyContent.Text = TxtReplyContent.Text + "Attachment:" + BtnAttachment.Text + "n";
        }
        PnlReply.Visible = true;
        PnlReplyAll.Visible = false;
        PnlMail.Visible = false;
        PnlFw.Visible = false;
        TxtReplyContent.Focus();
    }
    protected void BtnReplyOk_Click(object sender, EventArgs e)
    {
        int flag = 0;
        if (TxtReplyTitle.Text == "")
        {
            flag = 1;
            Response.Write("<script language='javascript'>alert('主旨不能為空!');</script>");
        }
        if (TxtReplyContent.Text == "")
        {
            flag = 1;
            Response.Write("<script language='javascript'>alert('郵件正文不能為空!');</script>");
        }
        if (flag == 0)
        {
            DateMgr date = new DateMgr();
            string time = date.getTime();
            //上傳檔案
            if (!this.uploadReplyFile(time))
            {
                if (FileUpload1.HasFile)
                {
                    //上傳檔案不成功
                }
                else
                {
                    //沒有上傳附件
                    this.saveMail();
                }
            }
            else
            {
                this.saveMail();
            }
        }
    }
    protected void saveMail()
    {
        #region
        //添加數据
        SqlConnection conn = dbConnection.getConnection();
        conn.Open();
        int mail_id = 1;
        SqlCommand cmd = new SqlCommand("select max(id) from OA_MAIL_CONTENT", conn);
        try
        {
            mail_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        int sender_id = 1;
        cmd = new SqlCommand("select max(id) from OA_MAIL_SENDER", conn);
        try
        {
            sender_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        int receiver_id = 1;
        cmd = new SqlCommand("select max(id) from OA_MAIL_RECEIVER", conn);
        try
        {
            receiver_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        int attachment_id = 1;
        cmd = new SqlCommand("select max(id) from OA_MAIL_ATTACHMENT", conn);
        try
        {
            attachment_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        #endregion
        SqlTransaction tx = conn.BeginTransaction();
        try
        {
            //添加郵件內容
            cmd = new SqlCommand("insert into OA_MAIL_CONTENT(id, title, content) values (" +
                mail_id + ", '" + TxtReplyTitle.Text + "', '" + TxtReplyContent.Text.Replace("n", "<br>").Replace(" ", "&nbsp") + "')", conn);
            cmd.Transaction = tx;
            cmd.ExecuteNonQuery();
            //添加發件人信息
            int user_id = 0;
            if (Session["user"] != null)
            {
                user_id = ((User)Session["user"]).Id;
            }
            DateMgr mgr = new DateMgr();
            string time = mgr.getDateTime();
            cmd = new SqlCommand("insert into OA_MAIL_SENDER(id, mail_id, sender, state, " +
                "is_del, send_date, cre_date)values(" + sender_id + ", " + mail_id + ", " + user_id +
                ", 1, 0, '" + time + "', '" + time + "')", conn);
            cmd.Transaction = tx;
            cmd.ExecuteNonQuery();
            //添加收件人
            cmd = new SqlCommand("insert into OA_MAIL_RECEIVER(id, mail_id, " +
                "receiver, is_read, is_del, read_date) values (" + receiver_id + ", " +
                mail_id + ", " + SenderId.Value + ", 0, 0, '')", conn);
            cmd.Transaction = tx;
            cmd.ExecuteNonQuery();
            //添加附件
            if (!FileUpload1.FileName.Equals(""))
            {
                DateMgr date = new DateMgr();
                time = date.getTime();
                string name = "";
                try
                {
                    name = FileUpload1.FileName.Substring(0, FileUpload1.FileName.Length - 4) + time + System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                }
                catch { }
                cmd = new SqlCommand("insert into OA_MAIL_ATTACHMENT(id, mail_id, cn_name, en_name, content_length)" +
                    " values (" + attachment_id + ", " + mail_id + ", '"+FileUpload1.FileName+"','" + name + "','" +
                    ContentLength1.Value + "')", conn);
                cmd.Transaction = tx;
                cmd.ExecuteNonQuery();
            }
            tx.Commit();
            Response.Redirect("mail_list_send.aspx");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
            tx.Rollback();
        }
        conn.Close();
    }
    protected bool uploadReplyFile(string time)
    {
        bool flag = false;
        string server_ip = "";
        string root = "";
        string folder = "";
        if (Application["FILE_SERVER_IP"] != null)
        {
            server_ip = Application["FILE_SERVER_IP"].ToString();
        }
        if (Application["FILE_SERVER_ROOT"] != null)
        {
            root = Application["FILE_SERVER_ROOT"].ToString();
        }
        if (Application["MAIL_FOLDER"] != null)
        {
            folder = Application["MAIL_FOLDER"].ToString();
        }
        //string directory = "\\" + server_ip + "\" + root + "\" + folder+"\";
        string directory = "f:\oa(new)\web\oa_upload\mail\";
        if (FileUpload1.HasFile)
        {
            bool extenstion = false;
            string fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
            string[] allowedExtensions = 
                { ".gif", ".jpg", ".doc", ".xls", ".rar",".pdf", ".zip", ".txt", ".vsd", ".vss", ".vst", ".vdx", ".vsx", ".vtx", ".html", ".htm" };
            for (int i = 0; i < allowedExtensions.Length; i++)
            {
                if (fileExtension == allowedExtensions[i])
                {
                    extenstion = true;
                    break;
                }
            }
            if (!extenstion)
            {
                Response.Write("<script language='javascript'>alert('只允許上傳格式為gif,pdf, jpg, doc, xls, rar, zip, txt, vsd, vss, vst, vdx, vsx, vtx, html, htm的檔案!');</script>");
                return false;
            }
            else
            {
                string name = FileUpload1.FileName.Substring(0, FileUpload1.FileName.Length - 4) + time + System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
                FileUpload1.PostedFile.SaveAs(directory + name);
                ContentLength1.Value = FileUpload1.PostedFile.ContentLength.ToString();
                flag = true;
            }
        }
        return flag;
    }
    protected void BtnReplyAllOk_Click(object sender, EventArgs e)
    {
        int flag = 0;
        if (TxtReplyAllTitle.Text == "")
        {
            flag = 1;
            Response.Write("<script language='javascript'>alert('主旨不能為空!');</script>");
        }
        if (TxtReplyAllContent.Text == "")
        {
            flag = 1;
            Response.Write("<script language='javascript'>alert('郵件正文不能為空!');</script>");
        }
        if (flag == 0)
        {
            DateMgr date = new DateMgr();
            string time = date.getTime();
            //上傳檔案
            if (!this.uploadReplyAllFile(time))
            {
                if (FileUpload2.HasFile)
                {
                    //上傳檔案不成功
                }
                else
                {
                    //沒有上傳附件
                    this.saveReplyAllMail();
                }
            }
            else
            {
                this.saveReplyAllMail();
            }
        }
    }
    protected bool uploadReplyAllFile(string time)
    {
        bool flag = false;
        string server_ip = "";
        string root = "";
        string folder = "";
        if (Application["FILE_SERVER_IP"] != null)
        {
            server_ip = Application["FILE_SERVER_IP"].ToString();
        }
        if (Application["FILE_SERVER_ROOT"] != null)
        {
            root = Application["FILE_SERVER_ROOT"].ToString();
        }
        if (Application["MAIL_FOLDER"] != null)
        {
            folder = Application["MAIL_FOLDER"].ToString();
        }
        //string directory = "\\" + server_ip + "\" + root + "\" + folder+"\";
        string directory = "f:\oa(new)\web\oa_upload\mail\";
        if (FileUpload2.HasFile)
        {
            bool extenstion = false;
            string fileExtension = System.IO.Path.GetExtension(FileUpload2.FileName).ToLower();
            string[] allowedExtensions = 
                { ".gif", ".jpg", ".doc", ".xls", ".rar", ".zip",".pdf", ".txt", ".vsd", ".vss", ".vst", ".vdx", ".vsx", ".vtx", ".html", ".htm" };
            for (int i = 0; i < allowedExtensions.Length; i++)
            {
                if (fileExtension == allowedExtensions[i])
                {
                    extenstion = true;
                    break;
                }
            }
            if (!extenstion)
            {
                Response.Write("<script language='javascript'>alert('只允許上傳格式為gif, pdf,jpg, doc, xls, rar, zip, txt, vsd, vss, vst, vdx, vsx, vtx, html, htm的檔案!');</script>");
                return false;
            }
            else
            {
                string name = FileUpload2.FileName.Substring(0, FileUpload2.FileName.Length - 4) + time + System.IO.Path.GetExtension(FileUpload2.FileName).ToLower();
                FileUpload2.PostedFile.SaveAs(directory + name);
                ContentLength2.Value = FileUpload2.PostedFile.ContentLength.ToString();
                flag = true;
            }
        }
        return flag;
    }
    protected void saveReplyAllMail()
    {
        //添加數据
        SqlConnection conn = dbConnection.getConnection();
        conn.Open();
        int mail_id = 1;
        SqlCommand cmd = new SqlCommand("select max(id) from OA_MAIL_CONTENT", conn);
        try
        {
            mail_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        int sender_id = 1;
        cmd = new SqlCommand("select max(id) from OA_MAIL_SENDER", conn);
        try
        {
            sender_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        int receiver_id = 1;
        cmd = new SqlCommand("select max(id) from OA_MAIL_RECEIVER", conn);
        try
        {
            receiver_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        int attachment_id = 1;
        cmd = new SqlCommand("select max(id) from OA_MAIL_ATTACHMENT", conn);
        try
        {
            attachment_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        SqlTransaction tx = conn.BeginTransaction();
        try
        {
            //添加郵件內容
            cmd = new SqlCommand("insert into OA_MAIL_CONTENT(id, title, content) values (" +
                mail_id + ", '" + TxtReplyAllTitle.Text + "', '" + TxtReplyAllContent.Text.Replace("n", "<br>").Replace(" ", "&nbsp") + "')", conn);
            cmd.Transaction = tx;
            cmd.ExecuteNonQuery();
            //添加發件人信息
            int user_id = 0;
            if (Session["user"] != null)
            {
                user_id = ((User)Session["user"]).Id;
            }
            DateMgr mgr = new DateMgr();
            string time = mgr.getDateTime();
            cmd = new SqlCommand("insert into OA_MAIL_SENDER(id, mail_id, sender, state, " +
                "is_del, send_date, cre_date)values(" + sender_id + ", " + mail_id + ", " + user_id +
                ", 1, 0, '" + time + "', '" + time + "')", conn);
            cmd.Transaction = tx;
            cmd.ExecuteNonQuery();
            //添加收件人
            string[] receiver_ids = ReceiverIds.Value.Split(new char[] { ';' });
            for (int i = 0; i < receiver_ids.Length-1; i++)
            {
                cmd = new SqlCommand("insert into OA_MAIL_RECEIVER(id, mail_id, " +
                    "receiver, is_read, is_del, read_date) values (" + receiver_id + ", " +
                    mail_id + ", " + receiver_ids[i] + ", 0, 0, '')", conn);
                cmd.Transaction = tx;
                cmd.ExecuteNonQuery();
                receiver_id++;
            }
            //添加附件
            if (!FileUpload2.FileName.Equals(""))
            {
                DateMgr date = new DateMgr();
                time = date.getTime();
                string name = "";
                try
                {
                    name = FileUpload2.FileName.Substring(0, FileUpload2.FileName.Length - 4) + time + System.IO.Path.GetExtension(FileUpload2.FileName).ToLower();
                }
                catch { }
                cmd = new SqlCommand("insert into OA_MAIL_ATTACHMENT(id, mail_id, cn_name,en_name, content_length)" +
                    " values (" + attachment_id + ", " + mail_id + ", '"+FileUpload2.FileName+"', '" + name + "','" +
                    ContentLength2.Value + "')", conn);
                cmd.Transaction = tx;
                cmd.ExecuteNonQuery();
            }
            tx.Commit();
            Response.Redirect("mail_list_send.aspx");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
            tx.Rollback();
        }
        conn.Close();
    }
    protected void BtnReplyAll_Click(object sender, EventArgs e)
    {
        if (!ReceiverIds.Value.Contains(SenderId.Value))
        {
            TxtReplyAllReceiver.Text = LblSender.Text + ";" + LblReceivers.Text;
            ReceiverIds.Value = SenderId.Value + ";" + ReceiverIds.Value;
        }
        else
        {
            TxtReplyAllReceiver.Text = LblReceivers.Text;
        }
        
        TxtReplyAllTitle.Text = "RE:" + LblTitle.Text;
        TxtReplyAllContent.Text = "nnn--------------------------------------------------------n";
        TxtReplyAllContent.Text = TxtReplyAllContent.Text + "From:" + LblSender.Text + "n";
        TxtReplyAllContent.Text = TxtReplyAllContent.Text + "Sent:" + LblSendDate.Text + "n";
        TxtReplyAllContent.Text = TxtReplyAllContent.Text + "To:" + LblReceivers.Text + "n";
        TxtReplyAllContent.Text = TxtReplyAllContent.Text + "Subject:" + LblTitle.Text + "nn";
        TxtReplyAllContent.Text = TxtReplyAllContent.Text + TxtContent.Text + "nn";
        if (!BtnAttachment.Text.Equals(""))
        {
            TxtReplyAllContent.Text = TxtReplyAllContent.Text + "Attachment:" + BtnAttachment.Text + "n";
        }
        PnlReply.Visible = false;
        PnlReplyAll.Visible = true;
        PnlMail.Visible = false;
        PnlFw.Visible = false;
        TxtReplyAllContent.Focus();
    }
    protected void BtnFw_Click(object sender, EventArgs e)
    {
        if (!BtnAttachment.Text.Equals(""))
        {
            TxtFile.Text = BtnAttachment.Text;
        }
        else
        {
            TxtFile.Text = "無";
        }
        TxtFwTitle.Text = "FW:" + LblTitle.Text;
        TxtFwContent.Text = "nnn--------------------------------------------------------n";
        TxtFwContent.Text = TxtFwContent.Text + "From:" + LblSender.Text + "n";
        TxtFwContent.Text = TxtFwContent.Text + "Sent:" + LblSendDate.Text + "n";
        TxtFwContent.Text = TxtFwContent.Text + "To:" + LblReceivers.Text + "n";
        TxtFwContent.Text = TxtFwContent.Text + "Subject:" + LblTitle.Text + "nn";
        TxtFwContent.Text = TxtFwContent.Text + TxtContent.Text + "nn";
        if (!BtnAttachment.Text.Equals(""))
        {
            TxtFwContent.Text = TxtFwContent.Text + "Attachment:" + BtnAttachment.Text + "n";
        }
        PnlReply.Visible = false;
        PnlReplyAll.Visible = false;
        PnlMail.Visible = false;
        PnlFw.Visible = true;
    }
    protected void BtnFwOk_Click(object sender, EventArgs e)
    {
        int flag = 0;
        if (Employee_ids.Value == "")
        {
            flag = 1;
            Response.Write("<script language='javascript'>alert('收件人不能為空!');</script>");
        }
        if (TxtFwTitle.Text == "")
        {
            flag = 1;
            Response.Write("<script language='javascript'>alert('主旨不能為空!');</script>");
        }
        if (TxtFwContent.Text == "")
        {
            flag = 1;
            Response.Write("<script language='javascript'>alert('郵件正文不能為空!');</script>");
        }
        if (flag == 0)
        {
            this.saveFwMail();         
        }
    }
    protected void saveFwMail()
    {
        //添加數据
        SqlConnection conn = dbConnection.getConnection();
        conn.Open();
        int mail_id = 1;
        SqlCommand cmd = new SqlCommand("select max(id) from OA_MAIL_CONTENT", conn);
        try
        {
            mail_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        int sender_id = 1;
        cmd = new SqlCommand("select max(id) from OA_MAIL_SENDER", conn);
        try
        {
            sender_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        int receiver_id = 1;
        cmd = new SqlCommand("select max(id) from OA_MAIL_RECEIVER", conn);
        try
        {
            receiver_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        int attachment_id = 1;
        cmd = new SqlCommand("select max(id) from OA_MAIL_ATTACHMENT", conn);
        try
        {
            attachment_id = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
        }
        catch { }
        SqlTransaction tx = conn.BeginTransaction();
        try
        {
            //添加郵件內容
            cmd = new SqlCommand("insert into OA_MAIL_CONTENT(id, title, content) values (" +
                mail_id + ", '" + TxtFwTitle.Text + "', '" + TxtFwContent.Text.Replace("n", "<br>").Replace(" ", "&nbsp") + "')", conn);
            cmd.Transaction = tx;
            cmd.ExecuteNonQuery();
            //添加發件人信息
            int user_id = 0;
            if (Session["user"] != null)
            {
                user_id = ((User)Session["user"]).Id;
            }
            DateMgr mgr = new DateMgr();
            string time = mgr.getDateTime();
            cmd = new SqlCommand("insert into OA_MAIL_SENDER(id, mail_id, sender, state, " +
                "is_del, send_date, cre_date)values(" + sender_id + ", " + mail_id + ", " + user_id +
                ", 1, 0, '" + time + "', '" + time + "')", conn);
            cmd.Transaction = tx;
            cmd.ExecuteNonQuery();
            //添加收件人
            if (!Employee_ids.Value.Equals(""))
            {
                string[] receiver_ids = Employee_ids.Value.Split(new char[] { ';' });
                for (int i = 0; i < receiver_ids.Length; i++)
                {
                    cmd = new SqlCommand("insert into OA_MAIL_RECEIVER(id, mail_id, " +
                        "receiver, is_read, is_del, read_date) values (" + receiver_id + ", " +
                        mail_id + ", " + receiver_ids[i] + ", 0, 0, '')", conn);
                    cmd.Transaction = tx;
                    cmd.ExecuteNonQuery();
                    receiver_id++;
                }
            }
            //添加附件
            if (!TxtFile.Text.Equals("無"))
            {
                if (Request.QueryString["r_id"] != null)
                {
                    cmd = new SqlCommand("select a.cn_name, a.en_name, a.content_length from OA_MAIL_RECEIVER r inner join OA_MAIL_ATTACHMENT a on r.mail_id = a.mail_id where r.id="+Request.QueryString["r_id"], conn);
                    cmd.Transaction = tx;
                    SqlDataReader dr = cmd.ExecuteReader();
                    string cn_name = "";
                    string en_name = "";
                    string content_length = "";
                    if (dr.Read())
                    {
                        cn_name = dr["cn_name"].ToString();
                        en_name = dr["en_name"].ToString();
                        content_length = dr["content_length"].ToString();
                    }
                    dr.Close();
                    cmd = new SqlCommand("insert into OA_MAIL_ATTACHMENT(id, mail_id, cn_name, en_name, content_length)" +
                        " values (" + attachment_id + ", " + mail_id + ", '" + cn_name + "','" + en_name + "','"+
                        ContentLength.Value + "')", conn);
                    cmd.Transaction = tx;
                    cmd.ExecuteNonQuery();
                }
            }
            tx.Commit();
            Response.Redirect("mail_list_send.aspx");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
            tx.Rollback();
        }
        conn.Close();
    }
}

						