private void DatabindDefaultValue()
{
#region SQL
string sql = @" SELECT MIN(X.SHIFT_DATE) AS START_DT,
MAX(Y.MIN_YR) AS START_YR,
MAX(Y.MIN_WK) AS START_WK,
MAX(X.SHIFT_DATE) AS END_DT,
MAX(Y.MAX_YR) AS END_YR,
MAX(Y.MAX_WK) AS END_WK,
MIN(Z.MIN_YR) AS MIN_YR,
MAX(Z.MAX_YR) AS MAX_YR
FROM SHIFT_DATE X,
(SELECT MIN(YEAR) AS MIN_YR,
MAX(TD_WEEK) - 2 AS MIN_WK,
MAX(YEAR) AS MAX_YR,
SUBSTR(MAX(YEAR || TD_WEEK)-1, 5, 2) AS MAX_WK
FROM SHIFT_DATE T
WHERE SYSDATE BETWEEN T.CALSTARTDATE AND ADD_MONTHS(T.CALENDDATE, 12)
ORDER BY SHIFT_DATE) Y,
(SELECT MAX(YEAR) AS MAX_YR, MIN(YEAR) AS MIN_YR FROM SHIFT_DATE) Z
WHERE X.YEAR || X.TD_WEEK = Y.MIN_YR || Y.MIN_WK
OR X.YEAR || X.TD_WEEK = Y.MAX_YR || Y.MAX_WK" ;
#endregion
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
using (IDataReader reader = database.ExecuteReader(sql))
{
if (reader.Read())
{
int minYR = Utils.ConvertObject <int>(reader["MIN_YR"]);
int maxYR = Utils.ConvertObject <int>(reader["MAX_YR"]);
this.DataBindDdlYear(minYR, maxYR);
this.ddlStartYear.Text = Utils.ConvertObject <string>(reader["START_YR"]);
this.DataBindWeekDdl(this.ddlStartWeek, this.ddlStartYear.Text, true);
this.ddlEndYear.Text = Utils.ConvertObject <string>(reader["END_YR"]);
this.DataBindWeekDdl(this.ddlEndWeek, this.ddlEndYear.Text, false);
this.ddlStartWeek.Text = Utils.ConvertObject <string>(reader["START_DT"]);
this.ddlEndWeek.Text = Utils.ConvertObject <string>(reader["END_DT"]);
}
}
}
private void setProject()
{
DataSet ds = new DataSet();
strSql = "select project_id, project_desc,project_name, applicant, applicant_dept, to_char(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(actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(actual_end_date,'yyyy/mm/dd') actual_end_date, priority, status,project_group_id,project_price ";
strSql += "from tms_project t ";
strSql += "where project_id = " + project_id;
IDataReader dr = m_objDB.ExecuteReader(strSql);
while (dr.Read())
{
lblProjectNo.Text = dr["project_id"].ToString();
lblProjectName.Text = dr["project_name"].ToString();
ddlStatus.SelectedValue = dr["status"].ToString();
lblOriginalStatus.Text = dr["status"].ToString();
txtProjectDesc.Text = dr["project_desc"].ToString();
lblAppilcant.Text = dr["applicant"].ToString();
lblAppilcantDept.Text = dr["applicant_dept"].ToString();
lblApplyDate.Text = dr["apply_date"].ToString();
txtEstimateStartDate.SelectedDate = Convert.ToDateTime(dr["Estimate_Start_Date"].ToString());
txtEstimateEndDate.SelectedDate = Convert.ToDateTime(dr["Estimate_End_Date"].ToString());
lblActualStartDate.Text = dr["Actual_Start_Date"].ToString();
lblActualEndDate.Text = dr["Actual_End_Date"].ToString();
ddlPiority.SelectedValue = dr["PRIORITY"].ToString();
ddlProjectGroup.SelectedValue = dr["project_group_id"].ToString();
txtPrice.Text = dr["project_price"].ToString();
}
////附件
strSql = "select * from tms_attachment where project_id = '134' ";
//strSql = "select * from tms_attachment where task_id = '101'";
dsAttach = m_objDB.ExecuteDataSet(strSql);
DataView dvAttach = dsAttach.Tables[0].DefaultView;
//dv.RowFilter = "ai_id is null ";
dlAttach.DataSource = dvAttach;
dlAttach.DataBind();
//process history
ds.Clear();
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 = '134' 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 = m_objDB.ExecuteDataSet(strSql);
dlProcessHistory.DataSource = ds;
dlProcessHistory.DataBind();
//get task member
ds.Clear();
strSql = "select a.task_id,b.member_name,b.is_owner from tms_task a , tms_member b where a.project_id = " + project_id + " and a.task_id = b.task_id ";
ds = m_objDB.ExecuteDataSet(strSql);
dv = ds.Tables[0].DefaultView;
//project task
strSql = "select rownum rn,t.* from ( ";
strSql += "select task_id,task_desc, to_char(estimate_start_date,'yyyy/mm/dd') estimate_start_date, to_char(estimate_end_date,'yyyy/mm/dd') estimate_end_date, status, ";
strSql += "to_char(actual_start_date,'yyyy/mm/dd') actual_start_date, to_char(actual_end_date,'yyyy/mm/dd') actual_end_date ";
strSql += "from tms_task where project_id = '" + project_id + "' order by estimate_end_date,estimate_start_date) t";
ds = m_objDB.ExecuteDataSet(strSql);
gvTask.DataSource = ds;
gvTask.DataBind();
//initField();
}
public override IDataReader ExecuteDataReader(IDbConnection connection, string cmdText, IList <IDbDataParameter> parameter)
{
return(DbAccessHelper.ExecuteReader((OleDbConnection)connection, CommandType.Text, cmdText, parameter == null ? null : parameter.Cast <OleDbParameter>().ToArray()));
}
