예제 #1
0
    private void setProject()
    {
        DataSet ds = new DataSet();

        ds.Clear();
        strSql             = "select project_name from tms_project where project_id = '" + Request.QueryString["project_id"].ToString() + "'";
        ds                 = DBUtil.GetDataset(strSql);
        lblTitle.InnerText = ds.Tables[0].Rows[0]["project_name"].ToString();

        //process history
        strSql = "select replace(replace(process_comment,'\n','<br>'),' ','&nbsp;') process_comment,to_char(create_dttm,'yyyy/mm/dd hh24:mi:ss') create_dttm,create_user from tms_process_history where project_id = '" + Request.QueryString["project_id"].ToString() + "'order by create_dttm desc";
        //strSql = "select replace(process_comment,'\r\n','<br>') process_comment,to_char(create_dttm,'yyyy/mm/dd hh24:mi:ss') create_dttm,create_user from tms_process_history where task_id = '101' order by create_dttm desc";
        ds = DBUtil.GetDataset(strSql);
        dlProcessHistory.DataSource = ds;
        dlProcessHistory.DataBind();

        ////附件
        strSql = "select * from tms_attachment where project_id = '" + Request.QueryString["project_id"].ToString() + "' order by file_desc";
        //strSql = "select * from tms_attachment where task_id = '101'";
        ds = DBUtil.GetDataset(strSql);
        dlAttach.DataSource = ds;
        dlAttach.DataBind();

        if (ds.Tables[0].Rows.Count > 0)
        {
            fsAttach.Visible = true;
        }
    }
예제 #2
0
    private DataSet getProjectTaskDs()
    {
        string strSql       = string.Empty;
        string selectedDept = "''";

        strSql = @"select project_id, project_name, to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date,
                status from tms_project t where t.applicant_dept in ({0}) and nvl(t.status,'N/A') not in ('Cancel') ";

        if (chkDept.SelectedIndex > -1)
        {
            selectedDept = dsutil.getCheckbox2Sql(chkDept);
        }

        strSql = string.Format(strSql, selectedDept);

        ds = DBUtil.GetDataset(strSql);

        return(ds);
    }
예제 #3
0
    private void set_data_list()
    {
        strSql = @"select distinct t.project_id,t.project_group from tms_project_group t 
        where t.project_dept in (select t1.authority_dept from tms_authority_dept t1 where t1.dept = '{0}' ) order by t.project_id ";

        strSql = string.Format(strSql, Session["user_dept"].ToString());

        ddlProjectGroup.DataTextField  = "project_group";
        ddlProjectGroup.DataValueField = "project_id";
        ddlProjectGroup.DataSource     = DBUtil.GetDataset(strSql);
        ddlProjectGroup.DataBind();
        ddlProjectGroup.Items.Insert(0, "");
    }
예제 #4
0
    private void setTaskData()
    {
        OleDbDataReader dr;
        string          strSql;
        OleDbConnection myConnection = new OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings["dsn"]);

        myConnection.Open();

        strSql = "select * from tms_task where task_id = '" + Request.QueryString["task_id"].ToString() + "'";
        dr     = DBUtil.GetDatareader(strSql, myConnection);

        while (dr.Read())
        {
            lblTaskNo.Text        = dr["task_id"].ToString();
            lblTaskType.Text      = dr["task_type"].ToString();
            lblStatus.Text        = dr["status"].ToString();
            lblTaskDesc.Text      = dr["task_desc"].ToString();
            lblAppilcant.Text     = dr["applicant"].ToString();
            lblAppilcantDept.Text = dr["applicant_dept"].ToString();
            lblApplyDate.Text     = dr["apply_date"].ToString();
        }

        string strAddress = "http://" + System.Configuration.ConfigurationSettings.AppSettings["dnsname"] + Context.Request.ApplicationPath;

        hyLink.NavigateUrl = strAddress + "/task_assign.aspx?task_id=" + lblTaskNo.Text + "&mail_to=Y";

        //process history
        strSql = "select replace(replace(process_comment,'\n','<br>'),' ','&nbsp;') process_comment,to_char(create_dttm,'yyyy/mm/dd hh24:mi:ss') create_dttm,create_user from tms_process_history where task_id = '" + Request.QueryString["task_id"].ToString() + "' and ai_id is null order by create_dttm desc";
        DataSet ds = DBUtil.GetDataset(strSql, myConnection);

        if (ds.Tables[0].Rows.Count > 0)
        {
            dlProcessHistory.DataSource = ds;
            dlProcessHistory.DataBind();
            fs1.Visible = true;
        }

        myConnection.Close();
        myConnection.Dispose();
    }
예제 #5
0
    protected void Page_Load(object sender, EventArgs e)
    {
        string  strSql = "";
        DataSet ds     = new DataSet();
        dbutil  DBUtil = new dbutil();

        if (Session["user_name"] == null)
        {
            strSql = "select cname,dept from tms_empinfo t where t.empno='" + HttpContext.Current.User.Identity.Name.Substring(HttpContext.Current.User.Identity.Name.IndexOf("\\") + 1).ToUpper() + "'";
            ds     = DBUtil.GetDataset(strSql);

            if (ds.Tables[0].Rows.Count == 0)
            {
                deny_message();
            }
            else
            {
                Session["user_name"] = ds.Tables[0].Rows[0]["cname"].ToString();
                Session["user_dept"] = ds.Tables[0].Rows[0]["dept"].ToString();
            }
        }
    }
    private void setTaskData()
    {
        DataSet ds = new DataSet();

        string strSql = "";

        //今日到期 Task
        strSql = @"select  'Delay' type,t.task_id, t.task_desc, to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date,
        to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date, t.priority, t.status, t.task_type,t2.project_name
        from tms_task t,tms_member t1,tms_project t2
                where t.task_id=t1.task_id and t.project_id=t2.project_id(+) 
                and t1.member_id='{0}'
                and t.estimate_end_date<to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
                and t.status not in ('Close','Cancel')
        union
        select '今日到期' type, t.task_id, t.task_desc, to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date,
        to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date, t.priority, t.status, t.task_type,t2.project_name
        from tms_task t,tms_member t1,tms_project t2
                where t.task_id=t1.task_id and t.project_id=t2.project_id(+)
                and t1.member_id='{0}' 
                and t.estimate_end_date=to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
                and t.status not in ('Close','Cancel')
        union
        select '明日到期' type,t.task_id, t.task_desc, to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date,
        to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date, t.priority, t.status, t.task_type,t2.project_name
        from tms_task t,tms_member t1,tms_project t2
                where t.task_id=t1.task_id and t.project_id=t2.project_id(+) 
                and t1.member_id='{0}'
                and t.estimate_end_date=to_date(to_char(sysdate+1,'yyyy/mm/dd'),'yyyy/mm/dd')
                and t.status not in ('Close','Cancel')
        union
        select '處理中' type,t.task_id, t.task_desc, to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date,
        to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date, t.priority, t.status, t.task_type,t2.project_name
        from tms_task t,tms_member t1,tms_project t2
                where t.task_id=t1.task_id and t.project_id=t2.project_id(+) 
                and t1.member_id='{0}'
                and t.estimate_end_date>to_date(to_char(sysdate+1,'yyyy/mm/dd'),'yyyy/mm/dd')
                and t.status not in ('Close','Cancel') ";

        strSql            = string.Format(strSql, Request.QueryString["empno"].ToString());
        ds                = DBUtil.GetDataset(strSql);
        gvTask.DataSource = ds;
        gvTask.DataBind();

        //        //明日到期 Task
        //        strSql = @"select  t.task_id, t.task_desc, to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date,
        //        to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date, t.priority, t.status, t.task_type
        //        from tms_task t,tms_member t1
        //                where t.task_id=t1.task_id
        //                and t1.member_id='TWN050555'
        //                and t.estimate_end_date=to_date(to_char(sysdate+1,'yyyy/mm/dd'),'yyyy/mm/dd')
        //                and t.status not in ('Close','Cancel')";

        //        ds = DBUtil.GetDataset(strSql);
        //        gvTomorrowTask.DataSource = ds;
        //        gvTomorrowTask.DataBind();

        //        //Delay Task
        //        strSql = @"select  t.task_id, t.task_desc, to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date,
        //        to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date, t.priority, t.status, t.task_type
        //        from tms_task t,tms_member t1
        //                where t.task_id=t1.task_id
        //
        //                and t.estimate_end_date<to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
        //                and t.status not in ('Close','Cancel')";

        //        ds = DBUtil.GetDataset(strSql);
        //        gvDelay.DataSource = ds;
        //        gvDelay.DataBind();



        //今日到期 Action Item
        strSql = @" select 'Delay' type,t.task_id,t.ai_id,t.ai_desc, to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date,
                to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date, t.member_name, t.status, t.progress,t1.task_desc
                from tms_action_item t,tms_task t1
                where t.task_id=t1.task_id and t.estimate_end_date<to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
                and t.status not in ('Close','Cancel')
                and t.member_id='{0}'
                union
                select '今日到期' type, t.task_id,t.ai_id,t.ai_desc, to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date,
                to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date, t.member_name, t.status, t.progress,t1.task_desc
                from tms_action_item t,tms_task t1
                where t.task_id=t1.task_id and t.estimate_end_date=to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
                and t.status not in ('Close','Cancel')
                and t.member_id='{0}'
                union
                select '明日到期' type,t.task_id,t.ai_id,t.ai_desc, to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date,
                to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date, t.member_name, t.status, t.progress,t1.task_desc
                from tms_action_item t,tms_task t1
                where t.task_id=t1.task_id and t.estimate_end_date=to_date(to_char(sysdate+1,'yyyy/mm/dd'),'yyyy/mm/dd')
                and t.status not in ('Close','Cancel')
                and t.member_id='{0}'
            union
                select '處理中' type,t.task_id,t.ai_id,t.ai_desc, to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date,
                to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date, t.member_name, t.status, t.progress,t1.task_desc
                from tms_action_item t,tms_task t1
                where t.task_id=t1.task_id and t.estimate_end_date>to_date(to_char(sysdate+1,'yyyy/mm/dd'),'yyyy/mm/dd')
                and t.status not in ('Close','Cancel')
                and t.member_id='{0}'";

        strSql          = string.Format(strSql, Request.QueryString["empno"].ToString());
        ds              = DBUtil.GetDataset(strSql);
        gvAI.DataSource = ds;
        gvAI.DataBind();

        ////        //明日到期 Action Item
        ////        strSql = @"select t.task_id,t.ai_id,t.ai_desc, to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date,
        ////                to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date, t.member_name, t.status, t.progress,t1.task_desc
        ////                from tms_action_item t,tms_task t1
        ////                where t.task_id=t1.task_id and t.estimate_end_date=to_date(to_char(sysdate+1,'yyyy/mm/dd'),'yyyy/mm/dd')
        ////                and t.status not in ('Close','Cancel')
        ////                and t.member_id='TWN050555'";

        ////        ds = DBUtil.GetDataset(strSql);
        ////        gvTomorrowAI.DataSource = ds;
        ////        gvTomorrowAI.DataBind();

        ////        //逾期 Action Item
        ////        strSql = @"select t.task_id,t.ai_id,t.ai_desc, to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date,
        ////                to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date, t.member_name, t.status, t.progress,t1.task_desc
        ////                from tms_action_item t,tms_task t1
        ////                where t.task_id=t1.task_id and t.estimate_end_date<to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
        ////                and t.status not in ('Close','Cancel')
        ////                ";

        ////        ds = DBUtil.GetDataset(strSql);
        ////        gvDelayAI.DataSource = ds;
        ////        gvDelayAI.DataBind();
    }
예제 #7
0
    private void setTaskData()
    {
        DataSet ds  = new DataSet();
        DataSet ds2 = new DataSet();

        string strSql      = "";
        string remind_type = Request.QueryString["remind"].ToString();

        //該部門所有delay的prjoect和task
        if (remind_type == "boss")
        {
            strSql = @" select rownum, a.* from
                  (select distinct 
                       t.project_id,
                       t.applicant_dept,
                       t.project_desc,
                       t.project_name,
                       t.applicant,
                       t.apply_date,
                       to_char(t.estimate_start_date, 'YYYY/MM/DD') estimate_start_date,
                       to_char(t.estimate_end_date, 'YYYY/MM/DD') estimate_end_date,
                       to_char(t.actual_start_date, 'YYYY/MM/DD') actual_start_date,
                       to_char(t.actual_end_date, 'YYYY/MM/DD') actual_end_date,
                       t.priority,
                       t.status,
                       t1.project_group
                  from tms_project t,
                       tms_project_group t1             
                 where t.project_group_id = t1.project_id(+)
                   and t.estimate_end_date<to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
                   and t.status not in ('Close','Cancel')
                   and t.applicant_dept in
                       (select t.authority_dept
                          from tms_authority_dept t
                         where t.dept = '{0}')
                  order by applicant_dept, estimate_end_date, estimate_start_date)a";

            strSql = string.Format(strSql, Request.QueryString["dept"].ToString());
            ds     = DBUtil.GetDataset(strSql);
            gvProject.DataSource = ds;
            gvProject.DataBind();


            strSql = @"select rownum, a.* from
                       (select distinct         
                                    t.task_id,
                                    t.task_desc,
                                    t2.dept,
                                    to_char(t.estimate_start_date, 'yyyy/mm/dd') estimate_start_date,
                                    to_char(t.estimate_end_date, 'yyyy/mm/dd') estimate_end_date,
                                    to_char(t.actual_start_date, 'yyyy/mm/dd') actual_start_date,
                                    to_char(t.actual_end_date, 'yyyy/mm/dd') actual_end_date,
                                    t.priority,
                                    t.status,
                                    t.task_type,
                                    t3.project_name,
                                    t3.project_id
                      from tms_task        t,
                           tms_member      t1,
                           tms_empinfo     t2,
                           tms_project     t3,
                           tms_action_item t4
                     where t.task_id = t1.task_id
                       and t1.member_id = t2.empno
                       and t.project_id = t3.project_id(+)
                       and t.task_id = t4.task_id(+)
                       and t1.is_owner='Y'
                       and t2.dept in (select t.authority_dept
                                              from tms_authority_dept t
                                             where t.dept = '{0}')
                       and t.estimate_end_date <to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
                       and t.status not in ('Close','Cancel')
                     order by dept, estimate_end_date, estimate_start_date) a";


            strSql            = string.Format(strSql, Request.QueryString["dept"].ToString());
            ds                = DBUtil.GetDataset(strSql);
            gvTask.DataSource = ds;
            gvTask.DataBind();
        }

        //各課delay的task和ai
        else if (remind_type == "manager")
        {
            strSql = @"select rownum, a.* from
                       (select distinct 
                                    t.task_id,
                                    t.task_desc,
                                    t2.dept,
                                    to_char(t.estimate_start_date, 'yyyy/mm/dd') estimate_start_date,
                                    to_char(t.estimate_end_date, 'yyyy/mm/dd') estimate_end_date,
                                    to_char(t.actual_start_date, 'yyyy/mm/dd') actual_start_date,
                                    to_char(t.actual_end_date, 'yyyy/mm/dd') actual_end_date,
                                    t.priority,
                                    t.status,
                                    t.task_type,
                                    t3.project_name,
                                    t3.project_id
                      from tms_task        t,
                           tms_member      t1,
                           tms_empinfo     t2,
                           tms_project     t3,
                           tms_action_item t4
                     where t.task_id = t1.task_id
                       and t1.member_id = t2.empno
                       and t.project_id = t3.project_id(+)
                       and t.task_id = t4.task_id(+)
                       and t2.dept in ('{0}')
                       and t.estimate_end_date <to_date(to_char(sysdate,'yyyy/mm/dd'),'yyyy/mm/dd')
                       and t.status not in ('Close','Cancel')
                     order by estimate_end_date, estimate_start_date)a";

            strSql            = string.Format(strSql, Request.QueryString["dept"].ToString());
            ds2               = DBUtil.GetDataset(strSql);
            gvTask.DataSource = ds2;
            gvTask.DataBind();


            strSql = @"select rownum, a.* from
                  (select distinct
                       t.task_id,
                       t.ai_id,
                       t.ai_desc,
                       to_char(t.actual_start_date, 'yyyy/mm/dd') actual_start_date,
                       to_char(t.actual_end_date, 'yyyy/mm/dd') actual_end_date,
                       to_char(t.estimate_start_date, 'yyyy/mm/dd') estimate_start_date,
                       to_char(t.estimate_end_date, 'yyyy/mm/dd') estimate_end_date,
                       t.member_name,
                       t.status,
                       t.progress,
                       t1.task_desc,
                       t3.dept
                  from tms_action_item t, tms_task t1, tms_member t2, tms_empinfo t3
                 where t.task_id(+) = t1.task_id
                   and t1.task_id = t2.task_id
                   and t2.member_id = t3.empno  
                   and t.estimate_end_date <
                       to_date(to_char(sysdate, 'yyyy/mm/dd'), 'yyyy/mm/dd')
                   and t.status not in ('Close', 'Cancel')
                   and t3.dept in ('{0}')
                   order by estimate_end_date, estimate_start_date)a";

            strSql          = string.Format(strSql, Request.QueryString["dept"].ToString());
            ds2             = DBUtil.GetDataset(strSql);
            gvAI.DataSource = ds2;
            gvAI.DataBind();
        }
    }
예제 #8
0
    public void getMyTask(string empno, string start_date, string end_date, string task_desc, string status, string task_type, string ai_expand, string analysis_type)
    {
        vStartDate    = DateTime.ParseExact(start_date, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
        vEndDate      = DateTime.ParseExact(end_date, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
        vEmpno        = empno;
        vAnalysisType = analysis_type;
        vStatus       = status;

        lblAIExpand.Text     = ai_expand;
        lblAnalysisType.Text = analysis_type;

        strSql  = "select distinct t.task_id, t.task_desc, t.applicant, t.applicant_dept, to_char(t.apply_date,'yyyy/mm/dd') apply_date, to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date, ";
        strSql += "to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date, t.priority, t.status, t.task_type, t.project_id, t.category_id, t.request_category,t2.project_name ";
        strSql += "from tms_task t,tms_member t1,tms_project t2,tms_action_item t3 where t.task_id=t1.task_id and t.task_id=t3.task_id(+) and t1.member_id = '" + empno + "' and t.project_id = t2.project_id(+) ";

        if (status == "CloseLast7Day")
        {
            //當選擇最近七日close 時連最近七日結案AI的task也要列出來
            strSql += "and ((t.status = 'Close' and t.actual_end_date between sysdate-7 and sysdate+1)  ";
            strSql += "or (t3.status = 'Close' and t3.actual_end_date between sysdate-7 and sysdate+1 and t3.member_id = '" + empno + "')) ";

            lblStatus.Text = status;
            lblEmpno.Text  = empno;
        }
        else if (status == "CloseLastDay" && ai_expand == "N")
        {
            //分析報表by(人)-當選擇時間區間內close 時連最近結案的task要列出來
            string strTemp = string.Empty;
            strTemp += "and (t.status = 'Close' and t.actual_end_date between to_date('_fromDate','yyyymmdd') and to_date('_endDate','yyyymmdd')+1 and t1.member_id = '" + empno + "')";
            strSql  += strTemp.Replace("_fromDate", start_date).Replace("_endDate", end_date);
        }
        else if (status == "CloseLastDay" && ai_expand == "Y")
        {
            //分析報表by(人)-當選擇時間區間內close 時連最近結案的task要列出來
            string strTemp = string.Empty;
            strTemp += "and (t3.status = 'Close' and t3.actual_end_date between to_date('_fromDate','yyyymmdd') and to_date('_endDate','yyyymmdd')+1 and t1.member_id = '" + empno + "')";
            strSql  += strTemp.Replace("_fromDate", start_date).Replace("_endDate", end_date);
        }
        else
        {
            if (start_date != "")
            {
                strSql += "and t.apply_date >= to_date('" + start_date + "','yyyy/mm/dd') ";
            }
            if (end_date != "")
            {
                strSql += "and t.apply_date <= to_date('" + end_date + "','yyyy/mm/dd') + 1 ";
            }
            //if (status != "")
            //{
            if (status == "Receiving & Processing")
            {
                strSql += "and t.status in ('Receiving','Processing') ";
            }
            else if (status == "Delay")
            {
                strSql += "and t.estimate_end_date+1 < sysdate and t.status in ('Receiving','Processing') ";
            }
            else
            {
                strSql += "and t.status = '" + status + "' ";
            }
            //}

            lblStatus.Text = status;
            lblEmpno.Text  = empno;
        }

        if (task_desc != "")
        {
            strSql += "and upper(t.task_desc) like '%" + task_desc.ToUpper() + "%' ";
        }
        if (task_type != "")
        {
            strSql += "and t.task_type in (" + task_type + ") ";
        }
        //else
        //    strSql += "and t.task_type in ('') ";
        //當專案cancel時,可是task還是processing,這樣的task不應該在查詢task的狀態是processing被查出來
        strSql     += "and nvl(t2.status,'N/A') not in ('Cancel')";
        strSql     += "order by t.project_id,estimate_end_date,estimate_start_date";
        lblSql.Text = strSql;

        ds = DBUtil.GetDataset(strSql);
        gvTask.DataSource = ds;
        gvTask.DataBind();
        //將sql的資料存起來,當gridview換頁的時候重抓資料要使用
    }
예제 #9
0
    public void getMyTask(string empno, string start_date, string end_date, string task_desc, string status, string task_type, string ai_expand, string task_filedesc, string task_comment, string trace)
    {
        lblAIExpand.Text = ai_expand;

        strSql  = "select distinct t.task_id, t.task_desc, t.applicant, t.applicant_dept, to_char(t.apply_date,'yyyy/mm/dd') apply_date, to_char(t.estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(t.estimate_end_date,'yyyy/mm/dd') estimate_end_date, ";
        strSql += "to_char(t.actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(t.actual_end_date,'yyyy/mm/dd') actual_end_date, t.priority, t.status, t.task_type, t.project_id, t.category_id, t.request_category,t2.project_name, t1.member_id ";
        strSql += "from tms_task t,tms_member t1,tms_project t2,tms_action_item t3 where t.task_id=t1.task_id and t.task_id=t3.task_id(+) and t1.member_id = '" + empno + "' and t.project_id = t2.project_id(+) ";

        if (status == "CloseLast7Day")
        {
            //當選擇最近七日close 時連最近七日結案AI的task也要列出來
            strSql += "and ((t.status = 'Close' and t.actual_end_date between sysdate-7 and sysdate+1)  ";
            strSql += "or (t3.status = 'Close' and t3.actual_end_date between sysdate-7 and sysdate+1 and t3.member_id = '" + empno + "')) ";

            lblStatus.Text = status;
            lblEmpno.Text  = empno;
        }
        else if (status == "CloseLastDay")
        {
            //分析報表by(人)-當選擇時間區間內close 時連最近結案AI的task也要列出來
            string strTemp = string.Empty;
            strTemp += "and ((t.status = 'Close' and t.actual_end_date between to_date('_fromDate','yyyymmdd') and to_date('_endDate','yyyymmdd') )  ";
            strTemp += "or (t3.status = 'Close' and t3.actual_end_date between to_date('_fromDate','yyyymmdd') and to_date('_endDate','yyyymmdd') and t3.member_id = '" + empno + "')) ";

            strSql += strTemp.Replace("_fromDate", start_date).Replace("_endDate", end_date);
        }
        else
        {
            if (start_date != "")
            {
                strSql += "and t.apply_date >= to_date('" + start_date + "','yyyy/mm/dd') ";
            }
            if (end_date != "")
            {
                strSql += "and t.apply_date <= to_date('" + end_date + "','yyyy/mm/dd')+1 ";
            }
            //if (status != "")
            //{
            if (status == "Receiving & Processing")
            {
                strSql += "and t.status in ('Receiving','Processing') ";
            }
            else if (status == "All")
            {
                strSql += "";
            }
            else if (status == "Delay")
            {
                strSql += "and t.estimate_end_date+1 < sysdate and t.status in ('Receiving','Processing') ";
            }
            else
            {
                strSql += "and t.status = '" + status + "' ";
            }
            //}

            lblStatus.Text = status;
            lblEmpno.Text  = empno;
        }

        if (task_desc != "")
        {
            strSql += "and upper(t.task_desc) like '%" + task_desc.ToUpper() + "%' ";
        }

        if (task_type != "")
        {
            strSql += "and t.task_type in (" + task_type + ") ";
        }

        if (task_filedesc != "")
        {
            strSql += "and exists (select 'X' from tms_attachment a where a.task_id = t.task_id and upper(a.file_desc) like '%" + task_filedesc.ToUpper() + "%')";
        }

        if (task_comment != "")
        {
            strSql += "and exists (select 'X' from tms_process_history a where a.task_id = t.task_id and upper(a.process_comment) like '%" + task_comment.ToUpper() + "%')";
        }

        if (trace != "")
        {
            strSql += "and exists (select 'X' from tms_trace_list a where a.task_id = t.task_id and a.member_id = t1.member_id)";
        }

        //else
        //    strSql += "and t.task_type in ('') ";
        //當專案cancel時,可是task還是processing,這樣的task不應該在查詢task的狀態是processing被查出來
        strSql += "and nvl(t2.status,'N/A') not in ('Cancel')";
        strSql += "order by t.project_id,estimate_end_date,estimate_start_date";

        lblSql.Text = strSql;

        ds = DBUtil.GetDataset(strSql);
        gvTask.DataSource = ds;
        gvTask.DataBind();
        //將sql的資料存起來,當gridview換頁的時候重抓資料要使用
    }
예제 #10
0
    protected void Page_Load(object sender, EventArgs e)
    {
        DataSet ds = new DataSet();
        DataSet ds2 = new DataSet();
        DataSet ds3 = new DataSet();
        string  strMailTo = "", strMailSubject = "", strMailBody = "", strEmpno = "", strEmpname = "", strDept = "";
        //三級主管不寄每日工作摘要
        string strSql = string.Empty;

        //寄給部門內所有工程師每日工作摘要
        strSql  = "select t1.empno,t1.cname from tms_dept_manager t,tms_empinfo t1 where t.dept=t1.dept and t.task_remind_mail='Y'";
        strSql += "and t.manager_id <> t1.empno and nvl(t1.disable,'N')='N'";
        ds      = DBUtil.GetDataset(strSql);

        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            StringWriter   sw  = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);

            strEmpno   = ds.Tables[0].Rows[i]["empno"].ToString();
            strEmpname = ds.Tables[0].Rows[i]["cname"].ToString();

            Server.Execute("task_remind_content.aspx?empno=" + strEmpno + "&remind=" + "eng", htw);

            strMailTo = strEmpno + "@innolux.com.tw";
            //strMailTo = "*****@*****.**";
            strMailSubject = "[Task Management System] " + strEmpname + "的每日【" + DateTime.Now.ToString("yyyy/MM/dd") + "】工作事項提醒";
            strMailBody    = sw.ToString();

            DBUtil.sendMail(strMailBody, strMailTo, strMailSubject);
        }

        //寄給部門內各課三級主管該課delay的工作列表
        string strSql2 = string.Empty;

        strSql2 = @"select t.manager_id, t.manager_name, t.dept
                    from tms_dept_manager t
                    where t.manager_level = '3' and t.task_remind_mail='Y'";

        ds2 = DBUtil.GetDataset(strSql2);


        for (int i = 0; i < ds2.Tables[0].Rows.Count; i++)
        {
            StringWriter   sw  = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);

            strEmpno   = ds2.Tables[0].Rows[i]["manager_id"].ToString();
            strEmpname = ds2.Tables[0].Rows[i]["manager_name"].ToString();
            strDept    = ds2.Tables[0].Rows[i]["dept"].ToString();

            Server.Execute("task_remind_content_by_boss.aspx?dept=" + strDept + "&remind=" + "manager", htw);

            strMailTo = strEmpno + "@innolux.com.tw";
            //strMailTo = "*****@*****.**";
            strMailSubject = "[Task Management System] " + strDept + " " + "Delay的工作事項提醒";
            strMailBody    = sw.ToString();

            DBUtil.sendMail(strMailBody, strMailTo, strMailSubject);
        }

        //寄給部門二級主管該部門delay的工作列表
        string strSql3 = string.Empty;

        strSql3 = @"select *  from tms_dept_manager t  where t.manager_level = '2' and t.task_remind_mail = 'Y'";

        ds3 = DBUtil.GetDataset(strSql3);


        for (int i = 0; i < ds3.Tables[0].Rows.Count; i++)
        {
            StringWriter   sw  = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);

            strEmpno   = ds3.Tables[0].Rows[i]["manager_id"].ToString();
            strEmpname = ds3.Tables[0].Rows[i]["manager_name"].ToString();
            strDept    = ds3.Tables[0].Rows[i]["dept"].ToString();

            Server.Execute("task_remind_content_by_boss.aspx?dept=" + strDept + "&remind=" + "boss", htw);

            strMailTo = strEmpno + "@innolux.com.tw";
            //strMailTo = "*****@*****.**";
            strMailSubject = "[Task Management System] " + strDept + " " + "Delay的工作事項提醒";
            strMailBody    = sw.ToString();

            DBUtil.sendMail(strMailBody, strMailTo, strMailSubject);
        }


        Response.Write("<script language='javascript'>" + "\n");
        Response.Write("window.opener=null; window.close();</script>");
    }
예제 #11
0
    //public void bindGV(string strSql)
    //{
    //    lblSql.Text = strSql;

    //    ds = DBUtil.GetDataset(lblSql.Text);
    //    GridView1.DataSource = ds;
    //    GridView1.DataBind();

    //}

    public void getMyProject(string start_date, string end_date, string project_name, string project_desc, string status, string applicant, string project_group)
    {
        strSql  = "select a.*,rownum rn from ( ";
        strSql += "select t.project_id , t.project_name , t.applicant, t.apply_date , to_char(t.estimate_start_date,'YYYY/MM/DD') estimate_start_date , to_char(t.estimate_end_date,'YYYY/MM/DD') estimate_end_date , to_char(t.actual_start_date,'YYYY/MM/DD') actual_start_date, to_char(t.actual_end_date,'YYYY/MM/DD') actual_end_date , t.priority, t.status,x.ai_total,x.ai_processing,x.ai_hour,x.ai_unclose_ratio,t1.project_group ";
        strSql += " from tms_project t,tms_project_group t1, ";
        strSql += "(select t2.*,decode(t2.ai_total,0,0,round(((t2.ai_total-t2.ai_processing)/t2.ai_total),4)*100) ai_unclose_ratio from ( ";
        strSql += "select t.project_id,count(*) ai_total, ";
        strSql += "sum(decode(t1.status,'Receiving',1,'Processing',1,'Pending',1,0)) ai_processing, ";
        strSql += "sum(t1.ai_hour) ai_hour ";
        strSql += "from tms_task t,tms_action_item t1  ";
        strSql += "where t.task_id=t1.task_id  ";
        strSql += "and nvl(t1.status,'N/A') <> 'Cancel' ";
        strSql += "group by t.project_id) t2) x ";
        strSql += "where t.project_id=x.project_id(+) and t.project_group_id = t1.project_id(+) and t.applicant ='" + applicant + "' ";

        if (status == "CloseLast7Day")
        {
            strSql += "and t.actual_end_date between sysdate-6 and sysdate+1 ";
            strSql += "and t.status = 'Close' ";
        }
        else
        {
            if (start_date != "")
            {
                strSql += " and t.apply_date >= to_date('" + start_date + "','yyyy/mm/dd')";
            }
            if (end_date != "")
            {
                strSql += " and t.apply_date <= to_date('" + end_date + "','yyyy/mm/dd') +1 ";
            }

            if (status == "All")
            {
                strSql += "";
            }
            else if (status == "Delay")
            {
                strSql += " and t.estimate_end_date+1 < sysdate and t.status in ('Receiving','Processing')";
            }
            else
            {
                strSql += " and t.status = '" + status + "'";
            }
        }

        if (project_name != "")
        {
            strSql += " and upper(t.project_name) like '%" + project_name.ToUpper() + "%'";
        }
        if (project_desc != "")
        {
            strSql += " and upper(t.project_desc) like '%" + project_desc.ToUpper() + "%'";
        }
        if (project_group != "")
        {
            strSql += " and t.project_group_id = " + project_group;
        }

        strSql += " order by t1.project_group,t.estimate_end_date,t.estimate_start_date ) a ";
        //為了下一頁使用
        lblSql.Text = strSql;

        ds = DBUtil.GetDataset(lblSql.Text);
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }