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>'),' ',' ') 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;
}
}
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);
}
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, "");
}
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>'),' ',' ') 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();
}
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();
}
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();
}
}
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換頁的時候重抓資料要使用
}
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換頁的時候重抓資料要使用
}
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>");
}
//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();
}