OA系统

开发平台：
ASP/ASPX

副本 my_list_d.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;
//System.Web.HttpUtility.UrlPathEncode 
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;
using System.Reflection;
using System.Runtime.InteropServices;
using ExcelApplication = Microsoft.Office.Interop.Excel.Application;
using Label = System.Web.UI.WebControls.Label;
public partial class web_approvel_ch_my_list_d : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoginDAO.CheckLogin(Session, Response, "../../", 2);
           
          
            if (Request.QueryString["a_id"] != null)
            {
                PnlApprovel.Visible = true;
                PnlFlow.Visible = true;
                FlowList.DataKeyNames = new string[] { "id" };
                this.SetValue(Request.QueryString["a_id"]);
                this.SetAmtValue(Request.QueryString["a_id"]);
            }
            if (Session["user"] != null)
            {
                User user = (User)Session["user"];
                DepartmentId.Value = user.Department.Id.ToString();
                PositionId.Value = user.PositionId.ToString();
            }
        }
    }
    protected void FlowList_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            SqlConnection conn = dbConnection.getConnection();
            conn.Open();
            SqlCommand cmd = new SqlCommand("select apply_type, is_check, is_agree, comment from " +
                "OA_CH_APPROVEL_FORM_FLOW where id=" + FlowList.DataKeys[e.Row.RowIndex].Value.ToString(), conn);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                Label label = (Label)e.Row.FindControl("LblApplyType");
                if (dr["apply_type"].ToString().Equals("1"))
                {
                    label.Text = "會簽";
                }
                else if (dr["apply_type"].ToString().Equals("3"))
                {
                    label.Text = "代理";
                }
                else
                {
                    label.Text = "審批";
                }
                label = (Label)e.Row.FindControl("LblIsCheck");
                if (dr["is_check"].ToString().Equals("N"))
                {
                    label.Text = "未處理";
                }
                else
                {
                    label.Text = "已處理";
                    label = (Label)e.Row.FindControl("LblIsAgree");
                    if (dr["is_agree"].ToString().Equals("Y"))
                    {
                        label.Text = "已同意";
                    }
                    else
                    {
                        label.Text = "不同意";
                    }
                }
                label = (Label)e.Row.FindControl("LblComment");
                label.Text = dr["comment"].ToString();
            }
            dr.Close();
            conn.Close();
        }
    }
  
    protected void BtnOk_Click(object sender, EventArgs e)
    {
        if (Request.QueryString["a_id"] != null)
        {
            string id = Request.QueryString["a_id"];
            SqlConnection conn = dbConnection.getConnection();
            conn.Open();
            if (State.Value.Equals("2"))
            {
                SqlTransaction tx = conn.BeginTransaction();
                try
                {
                    SqlCommand cmd = new SqlCommand("update OA_CH_BUSINESS_FORM set state=1, total_result='跑流程中' where id=" + id, conn);
                    cmd.Transaction = tx;
                    cmd.ExecuteNonQuery();
                    //mail
                    ArrayList name1 = new ArrayList();
                    ArrayList mail1 = new ArrayList();
                    cmd = new SqlCommand("select top 1 a.email_work,a.emp_name from oa_employee as a inner join " +
                       "oa_ch_approvel_form_flow as b on b.operator_id=a.id left join OA_ch_business_form as c " +
                         "on b.form_id=c.id where b.step=c.step+1 and is_business='Y' and c.id=" + id + " order by position desc", conn);
                    cmd.Transaction = tx;
                    SqlDataReader dr3 = cmd.ExecuteReader();
                    while (dr3.Read())
                    {
                        name1.Add(dr3["emp_name"].ToString());
                        mail1.Add(dr3["email_work"].ToString());
                        //mail.mail_q(mail1[0], name1[0], TxtEmpName.Text, "中幹出差申請單");
                    }
                    dr3.Close();
                    tx.Commit();
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                    tx.Rollback();
                }
            }
            else
            {
                SqlCommand cmd = new SqlCommand("update OA_CH_BUSINESS_FORM set state=1, total_result='跑流程中' where id=" + id, conn);
                cmd.ExecuteNonQuery();
            }
            conn.Close();
            Response.Redirect("my_list.aspx");
        }
    }
 
    protected void SetValue(string id)
    {
        SqlConnection conn = dbConnection.getConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand("select a.form_id, e.emp_name, e.emp_no, p.name position, a.apply_date," +
            "d.name department, a.days, a.begin_time, a.end_time,  depart_place, arrive_place, " +
            "a.depart_time, a.arrive_time, a.state from OA_CH_BUSINESS_FORM a inner join OA_EMPLOYEE e on a.emp_id=e.id " +
            "inner join OA_DEPARTMENT d on a.department_id=d.id inner join OA_EMPLOYEE_POSITION p on " +
            "e.position=p.id  where a.id=" + id, conn);
        SqlDataReader dr = cmd.ExecuteReader();
        int state = 0;
        if (dr.Read())
        {
            TxtFormId.Text = dr["form_id"].ToString();
            TxtEmpName.Text = dr["emp_name"].ToString();
            TxtEmpNo.Text = dr["emp_no"].ToString();
            TxtPosition.Text = dr["position"].ToString();
            TxtApplyDate.Text = dr["apply_date"].ToString();
            TxtDepartment.Text = dr["department"].ToString();
            TxtDays.Text = dr["days"].ToString();
            TxtBeginTime.Text = dr["begin_time"].ToString();
            TxtEndTime.Text = dr["end_time"].ToString();
            TxtDepartPlace.Text = dr["depart_place"].ToString();
            TxtArrivePlace.Text = dr["arrive_place"].ToString();
            TxtDepartTime.Text = dr["depart_time"].ToString();
            TxtArriveTime.Text = dr["arrive_time"].ToString();
            state = Convert.ToInt32(dr["state"].ToString());
            State.Value = state.ToString();
        }
        dr.Close();
        int flag = 0;
        cmd = new SqlCommand("select * from OA_CH_BUSINESS_DETAIL where form_id=" + id +
            " order by number", conn);
        dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            flag = 1;
            //動態創建表格行
            TableRow tr1 = new TableRow();
            TableCell td1 = new TableCell();
            td1.BorderWidth = Unit.Pixel(1);
            Label label = new Label();
            label.BorderStyle = BorderStyle.None;
            label.Text = dr["number"].ToString();
            td1.Controls.Add(label);
            tr1.Cells.Add(td1);
            TableCell td2 = new TableCell();
            td2.BorderWidth = Unit.Pixel(1);
            label = new Label();
            label.BorderStyle = BorderStyle.None;
            label.Text = dr["date"].ToString();
            td2.Controls.Add(label);
            tr1.Cells.Add(td2);
            TableCell td3 = new TableCell();
            td3.BorderWidth = Unit.Pixel(1);
            label = new Label();
            label.BorderStyle = BorderStyle.None;
            label.Text = dr["d_start"].ToString();
            td3.Controls.Add(label);
            tr1.Cells.Add(td3);
            TableCell td4 = new TableCell();
            td4.BorderWidth = Unit.Pixel(1);
            label = new Label();
            label.BorderStyle = BorderStyle.None;
            label.Text = dr["d_end"].ToString();
            td4.Controls.Add(label);
            tr1.Cells.Add(td4);
            TableCell td5 = new TableCell();
            td5.BorderWidth = Unit.Pixel(1);
            label = new Label();
            label.BorderStyle = BorderStyle.None;
            label.Text = dr["tool"].ToString();
            td5.Controls.Add(label);
            tr1.Cells.Add(td5);
            TableCell td6 = new TableCell();
            td6.BorderWidth = Unit.Pixel(1);
            label = new Label();
            label.BorderStyle = BorderStyle.None;
            label.Text = dr["content"].ToString();
            td6.Controls.Add(label);
            tr1.Cells.Add(td6);
            TableCell td7 = new TableCell();
            td7.BorderWidth = Unit.Pixel(1);
            label = new Label();
            label.BorderStyle = BorderStyle.None;
            label.Text = dr["remark"].ToString();
            td7.Controls.Add(label);
            tr1.Cells.Add(td7);
            TblDetail.Rows.Add(tr1);
        }
        dr.Close();
        conn.Close();
        if (flag == 0)
        {
            TblDetail.Visible = false;
        }
        if (state != 1)
        {
            PnlFlow.Visible = false;
            Panel1.Visible = true;
        }
    }
    protected void SetAmtValue(string id)
    {
        SqlConnection conn = dbConnection.getConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand("select * from OA_CH_BUSINESS_DETAIL1 where form_id=" + id, conn);
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            TxtFAmt1.Text = dr["f_eat"].ToString();
            TxtFRemark1.Text = dr["f_eat_remark"].ToString();
            TxtFAmt2.Text = dr["f_house"].ToString();
            TxtFRemark2.Text = dr["f_house_remark"].ToString();
            TxtFAmt3.Text = dr["f_move"].ToString();
            TxtFRemark3.Text = dr["f_move_remark"].ToString();
            TxtFAmt4.Text = dr["f_other"].ToString();
            TxtFRemark4.Text = dr["f_other_remark"].ToString();
            TxtFAmt5.Text = dr["f_total"].ToString();
            TxtFRemark5.Text = dr["f_total_remark"].ToString();
        }
        dr.Close();
        conn.Close();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
      /*  Response.Clear();
        Response.Buffer = true;
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMdd") + ".doc");
     //   Response.ContentEncoding = System.Text.Encoding.UTF8;
      //  Response.ContentEncoding = Encoding.UTF7;
        Response.HeaderEncoding = System.Text.Encoding.UTF8;
        Response.ContentType = "vnd.ms-excel/msword";
        this.EnableViewState = false;
        Response.ContentEncoding = System.Text.Encoding.UTF8; 
*/
        int r = 1;
        int c = 1;
        int v=0;
        
        ExcelApplication exc = new ExcelApplication();
        exc.Visible = false;
        exc.UserControl = false;
        Workbooks workbooks = exc.Workbooks;
        _Workbook workbook = workbooks.Add(true);
        Sheets shs = workbook.Sheets;
        _Worksheet sh = (_Worksheet)shs.get_Item(1);
        Range rang = sh.get_Range(sh.Cells[1, 1], sh.Cells[1, 11]);
        rang.Merge(true);
        
        rang.Value2 = "流通事業群中幹出差單 ";
        rang.HorizontalAlignment = XlHAlign.xlHAlignCenter;
        string[] t1 = new string[] { "申請單號","'"+TxtFormId.Text.ToString(), "差假别", TxtOffType.Text.ToString(), "申請人工號","'"+TxtEmpNo.Text.ToString(),
            "出差日期起",TxtBeginTime.Text.ToString(), "申請人姓名",TxtEmpName.Text.ToString(),"出差日期止",TxtEndTime.Text.ToString(),"申請日期",TxtApplyDate.Text.ToString(),
            "去程出發地",TxtDepartPlace.Text.ToString(),"申請人部門",TxtDepartment.Text.ToString(),"回程終地點",TxtArrivePlace.Text.ToString(),"申請人職稱",TxtPosition.Text.ToString(),
            "去程)起飛時間",TxtDepartTime.Text.ToString(),"出差天數",TxtDays.Text.ToString(),"(回程)起飛時間",TxtArriveTime.Text.ToString()};
        
        for (int r1 = 0; r1 < t1.Length; r1++)
        {
            if (c < 8)
            {
                Range rang1 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
                rang1.Merge(true);
                
                rang1.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rang1.Borders.Weight = XlBorderWeight.xlMedium;
                rang1.Value2 = t1[r1];
                c = c + 2;
            }
            else
            {
                 c = 1;
                 r++;
                Range rang1 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
                rang1.Merge(true);
                rang1.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rang1.Borders.Weight = XlBorderWeight.xlMedium;
                rang1.Value2 = t1[r1];
                c = c + 2;
            }
            
                 
              
            
        }
        
        string[] t2=new string[]{"序號","出差期間","起","迄","交通工具","業務內容","備注"};
        r=r+2;
        c = 0;
        for (int r2 = 0; r2 < t2.Length; r2++)
        {
             Range rang2 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang2.Merge(true);
            rang2.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang2.Borders.Weight = XlBorderWeight.xlMedium;
            rang2.Value2 = t2[r2];
            c = c + 2;
        }
        r = r + 1;
        c = 0;
        SqlConnection conn = dbConnection.getConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand("select * from OA_CH_BUSINESS_DETAIL where form_id=" + Request.QueryString["a_id"] +
            " order by number", conn);
        SqlDataReader dr = cmd.ExecuteReader();
            
        while (dr.Read())
        {
            Range rang3 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang3.Merge(true);
            rang3.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang3.Borders.Weight = XlBorderWeight.xlMedium;
            rang3.Value2 =dr["number"].ToString() ;
            c = c + 2;
            Range rang4 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang4.Merge(true);
            rang4.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang4.Borders.Weight = XlBorderWeight.xlMedium;
            rang4.Value2 = dr["date"].ToString();
            c = c + 2;
            Range rang5 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang5.Merge(true);
            rang5.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang5.Borders.Weight = XlBorderWeight.xlMedium;
            rang5.Value2 = dr["d_start"].ToString();
            c = c + 2;
            Range rang6 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang6.Merge(true);
            rang6.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang6.Borders.Weight = XlBorderWeight.xlMedium;
            rang6.Value2 = dr["d_end"].ToString();
            c = c + 2;
            Range rang7 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang7.Merge(true);
            rang7.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang7.Borders.Weight = XlBorderWeight.xlMedium;
            rang7.Value2 = dr["tool"].ToString();
            c = c + 2;
            Range rang8 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang8.Merge(true);
            rang8.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang8.Borders.Weight = XlBorderWeight.xlMedium;
            rang8.Value2 = dr["content"].ToString();
            c = c + 2;
            Range rang9 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang9.Merge(true);
            rang9.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang9.Borders.Weight = XlBorderWeight.xlMedium;
            rang9.Value2 = dr["remark"].ToString();
            c = c + 2;
            r++;
            c = 0;
        }
        dr.Close();
        r = r + 1;
        c = 1;
        string[] t3 = new string[] { "序號", "費用內容", "申請金額", "備注", "1", "膳食費用", TxtFAmt1.Text,TxtFRemark1.Text, "2",
            "住宿費用",TxtFAmt2.Text, TxtFRemark2.Text, "3","交通費用", TxtFAmt3.Text, TxtFRemark3.Text,"4","其他費用", TxtFAmt4.Text,TxtFRemark4.Text,
            "5", "合計",TxtFAmt5.Text,TxtFRemark5.Text };
        for (int r3 = 0; r3 < t3.Length; r3++)
        {
            if (c < 8)
            {
                Range rang10 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
                rang10.Merge(true);
                rang10.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rang10.Borders.Weight = XlBorderWeight.xlMedium;
                rang10.Value2 = t3[r3];
                c = c + 2;
            }
            else
            {
                c = 1;
                r++;
                Range rang10 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
                rang10.Merge(true);
                rang10.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rang10.Borders.Weight = XlBorderWeight.xlMedium;
                rang10.Value2 = t3[r3];
                c = c + 2;
            }
        
        }
        r = r + 2;
        c = 1;
       string[] t4 = new string[] {"步驟","辦理人","是否同意","處理意見","辦理日期"};
        for (int r4 = 0; r4 < t4.Length; r4++)
        {
            Range rang11 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang11.Merge(true);
            rang11.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang11.Borders.Weight = XlBorderWeight.xlMedium;
            rang11.Value2 = t4[r4];
            c = c + 2;
        }
        r = r + 1;
        c = 1;
         cmd = new SqlCommand("SELECT e.emp_name,step,is_agree,comment,check_date from OA_CH_APPROVEL_FORM_FLOW"+
          " as a left join oa_employee as e on a.operator_id=e.id where form_id=" + Request.QueryString["a_id"] +
          " order by step", conn);
         dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Range rang3 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang3.Merge(true);
            rang3.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang3.Borders.Weight = XlBorderWeight.xlMedium;
            rang3.Value2 = dr["step"].ToString();
            c = c + 2;
            Range rang4 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang4.Merge(true);
            rang4.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang4.Borders.Weight = XlBorderWeight.xlMedium;
            rang4.Value2 = dr["emp_name"].ToString();
            c = c + 2;
            Range rang5 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang5.Merge(true);
            rang5.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang5.Borders.Weight = XlBorderWeight.xlMedium;
            if (dr["is_agree"].Equals("Y"))
            { rang5.Value2 = "同意"; }
            else { rang5.Value2 = "不同意"; }
            c = c + 2;
            Range rang6 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang6.Merge(true);
            rang6.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang6.Borders.Weight = XlBorderWeight.xlMedium;
            rang6.Value2 = dr["comment"].ToString();
            c = c + 2;
            Range rang7 = sh.get_Range(sh.Cells[r + 2, c + 1], sh.Cells[r + 2, c + 2]);
            rang7.Merge(true);
            rang7.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rang7.Borders.Weight = XlBorderWeight.xlMedium;
            rang7.Value2 = dr["check_date"].ToString();
            c = c + 2;
                      
            r++;
            c = 1;
        }
        dr.Close();
        conn.Close();
       
            exc.Visible = true;
        exc.UserControl = true;
    }
   
}

						