protected void Get_TFT_Data()
{
string sSql_IN = this.Get_Input_Grid_SQL();
string sSql_OUT = this.Get_Output_Grid_SQL();
DataTable dt_in = m_objDB.ExecuteDataSet(sSql_IN).Tables[0];
DataTable dt_out = m_objDB.ExecuteDataSet(sSql_OUT).Tables[0];
DataView dv_in = dt_in.DefaultView;
DataView dv_out = dt_out.DefaultView;
dv_in.RowFilter = "shop in ('T1$ARY','T1$CEL','T1$CF','T2$ARY','T2$CEL','T2$CF')";
dv_out.RowFilter = "shop in ('T1$ARY','T1$CEL','T1$CF','T2$ARY','T2$CEL','T2$CF')";
this.RG_TFT.DataSource = this.RoTation(dv_in.ToTable(), dv_out.ToTable(), "TFT");
this.RG_TFT.DataBind();
}
protected void btnSearch_Click(object sender, ImageClickEventArgs e)
{
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
this.lsbAvaliableDefCode.DataSource = database.ExecuteDataSet(string.Format("SELECT DEFECT_CODE, DEFECT_CAT || ':' || DEFECT_CODE AS DEF_GROUP FROM {0} WHERE DEFECT_CODE LIKE '{1}%'", this.DBTableName, this.txtSearch.Text));
this.lsbAvaliableDefCode.DataTextField = "DEFECT_CODE";
this.lsbAvaliableDefCode.DataValueField = "DEF_GROUP";
this.lsbAvaliableDefCode.DataBind();
}
public void DisplayMultiDataTable()
{
string sSql_Shop = @" select distinct t.shop from mps_pp t
where t.shop like '{0}' || '%'
order by shop ";
sSql_Shop = string.Format(sSql_Shop, Shop_Transfer(sShop));
DataTable dt_shop = m_objDB.ExecuteDataSet(sSql_Shop).Tables[0];
//reportBase的function AddControlInTable會根據你傳進去的control在table中排列好
for (int i = 0; i < dt_shop.Rows.Count; i++)
{
DataTable dt = this.Get_Chart_DataTable(dt_shop.Rows[i]["shop"].ToString());
reportBase.AddControlInTable(this.Table1, this.DoChart(dt, "IN"), OrderBy.Row, 2, Align.Top);
reportBase.AddControlInTable(this.Table1, this.DoChart(dt, "OUT"), OrderBy.Row, 2, Align.Top);
reportBase.AddControlInTable(this.Table1, this.DoGrid(dt, "IN"), OrderBy.Row, 2, Align.Top);
reportBase.AddControlInTable(this.Table1, this.DoGrid(dt, "OUT"), OrderBy.Row, 2, Align.Top);
}
}
private void MenuDatabind()
{
string sql = @" select distinct d.* from ws_usergroup a,ws_menugroup b, ws_menu d
where a.groupid=b.groupid and b.itemno=d.itemno
and d.moduleid=1 and a.empno='{0}'
and d.sysname = 'Web_System' order by d.itemno";
sql = string.Format(sql, HttpContext.Current.User.Identity.Name.ToUpper());
DataSet ds = m_objDB.ExecuteDataSet(sql);
// module id = 1 for hearder menu item
foreach (DataRow row in ds.Tables[0].Rows)
{
RadMenuItem item = new RadMenuItem();
item.Value = row["itemno"].ToString();
item.Text = row["itemname"].ToString();
item.ToolTip = row["itemhint"] is DBNull ? "" : row["itemhint"].ToString();
// Set Default url if db setting is empty
//item.NavigateUrl = row["url"] is DBNull ? "~/Default.aspx" : row["url"].ToString();
item.NavigateUrl = row["url"] is DBNull ? "http://www.innolux.com/" : string.Format(row["url"].ToString(), HttpContext.Current.User.Identity.Name.ToUpper());
//設定開啟頁面的顯示位置
if (row["outsideflag"].ToString().Equals("0"))
{
item.Target = "_self";
}
else
{
item.Target = "_blank";
}
// Replace user id if out system need authenticate
//item.NavigateUrl = row["authflag"] is DBNull || row["authflag"].ToString() == "0" ? item.NavigateUrl : string.Format(item.NavigateUrl, "TWN050314");
// Add to parent item if the item is child item
object parentNo = row["parentno"];
if (parentNo is DBNull || parentNo.ToString() == "0")
{
this.RadMenu1.Items.Add(item);
}
else
{
RadMenuItem parentItem = this.RadMenu1.FindItemByValue(parentNo.ToString());
if (parentItem != null)
{
parentItem.Items.Add(item);
}
}
}
}
private void DataBindWeekDdl(DropDownList ddl, string year, bool startOrEnd)
{
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
string fmSql = @"SELECT TO_CHAR(T.TD_WEEK, '00') as TD_WEEK, MIN(T.SHIFT_DATE) START_DATE, MAX(T.SHIFT_DATE) END_DATE
FROM SHIFT_DATE T
WHERE T.YEAR = {0}
AND T.TD_WEEK IS NOT NULL
GROUP BY T.TD_WEEK
ORDER BY TO_CHAR(T.TD_WEEK, '00')" ;
ddl.DataTextField = "TD_WEEK";
ddl.DataValueField = startOrEnd ? "START_DATE" : "END_DATE";
ddl.DataSource = database.ExecuteDataSet(string.Format(fmSql, year)).Tables[0];
ddl.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
this.lsbDefGroup.DataSource =
database.ExecuteDataSet(string.Format("SELECT DISTINCT DEFECT_CAT FROM {0} WHERE DEFECT_CAT IS NOT NULL", this.DBTableName));
this.lsbDefGroup.DataTextField = "DEFECT_CAT";
this.lsbDefGroup.DataValueField = "DEFECT_CAT";
this.lsbDefGroup.DataBind();
this.RequiredFieldValidator1.ValidationGroup = this.ValidationGroup;
this.btnFilter.Enabled = this.lsbDefGroup.Items.Count > 0;
}
}
protected void RadTreeView1_NodeExpand(object sender, RadTreeNodeEventArgs e)
{
string sql = "";
switch (e.Node.Level)
{
case 0:
sql = "SELECT distinct PROD_MODEL as text FROM " + this.ProductTable +
" WHERE PROD_MODEL IS NOT NULL AND PROD_SIZE =" + e.Node.Value +
" and SHOP='" + this.ShopName + "'";
break;
case 1:
sql = "SELECT PROD_NAME as text FROM " + this.ProductTable +
" WHERE ACTIVE_FLAG='1' AND PROD_SIZE=" + e.Node.ParentNode.Value +
" AND PROD_MODEL=" + e.Node.Value +
" and SHOP='" + this.ShopName + "'";
break;
case 2:
sql = "SELECT prod_name as text FROM " + this.ProductTable +
" WHERE prod_name = '{0}' UNION SELECT SUBPROD_NAME AS prod_name FROM " + this.SubProductTable +
" T WHERE T.PROD_NAME = " + e.Node.Value;
break;
default:
return;
}
DbAccessHelper dbHelper = new DbAccessHelper(this.DBConnectionName);
foreach (DataRow row in dbHelper.ExecuteDataSet(sql).Tables[0].Rows)
{
RadTreeNode node = new RadTreeNode();
node.Text = row["text"].ToString();
node.Value = "'" + row["text"].ToString() + "'";
if (e.Node.Level < 2)
{
node.ExpandMode = TreeNodeExpandMode.ServerSideCallBack;
}
e.Node.Nodes.Add(node);
}
e.Node.Expanded = true;
}
private void SetDefaultValue()
{
string sql = @"
with X as (select max(to_number(year)) as max_yr, min(to_number(year)) as min_yr from shift_date ),
Y as
( select max(to_number(td_week)) - 2 as start_wk, year as start_yr, max(to_number(td_week)) as start_max_wk
from shift_date
where (year + 1) = to_char(sysdate, 'yyyy')
group by year
),
Z as
( select year as end_yr, td_week as end_wk, shift_date as end_dt, m.end_max_wk as end_max_wk
from shift_date,
( select max(to_number(td_week)) as end_max_wk
from shift_date
where year = to_char(sysdate, 'yyyy') ) m
where sysdate between calstartdate and calenddate
)
select Y.start_yr, Y.start_wk, Y.start_max_wk, Z.end_dt, Z.end_yr, Z.end_max_wk, Z.end_wk, X.max_yr, X.min_yr
from X, Y, Z
";
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
DataTable dt = database.ExecuteDataSet(sql).Tables[0];
if (dt == null || dt.Rows.Count < 1)
{
return;
}
int minYR = Utils.ConvertObject <int>(dt.Rows[0]["MIN_YR"]);
int maxYR = Utils.ConvertObject <int>(dt.Rows[0]["MAX_YR"]);
int startMaxWeek = Utils.ConvertObject <int>(dt.Rows[0]["START_MAX_WK"]);
int endMaxWeek = Utils.ConvertObject <int>(dt.Rows[0]["END_MAX_WK"]);
int endDefaultWeek = Utils.ConvertObject <int>(dt.Rows[0]["END_WK"]);
this.BindToComobox(this.ddlStartYear, minYR, maxYR, DateTime.Now.Year - 1);
this.BindToComobox(this.ddlEndYear, minYR, maxYR, DateTime.Now.Year);
this.BindToComobox(this.ddlStartWeek, 1, startMaxWeek, startMaxWeek - 2);
this.BindToComobox(this.ddlEndWeek, 1, endMaxWeek, endDefaultWeek);
}
protected void btnFilter_Click(object sender, EventArgs e)
{
string temp = "";
foreach (ListItem item in this.lsbDefGroup.Items)
{
if (item.Selected == true)
{
temp += ("'" + item.Value + "',");
}
}
if (!string.IsNullOrEmpty(temp))
{
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
temp = temp.Substring(0, temp.Length - 1);
this.lsbAvaliableDefCode.DataSource = database.ExecuteDataSet(string.Format("SELECT DEFECT_CODE, DEFECT_CAT || ':' || DEFECT_CODE AS DEF_GROUP FROM {0} WHERE DEFECT_CAT IN ({1})", this.DBTableName, temp));
this.lsbAvaliableDefCode.DataTextField = "DEFECT_CODE";
this.lsbAvaliableDefCode.DataValueField = "DEF_GROUP";
this.lsbAvaliableDefCode.DataBind();
}
}
private void DataBindAndSetDefault()
{
// Bind Year dropdownlist
DbAccessHelper database = new DbAccessHelper("InnoluxDB");
DataTable source = database.ExecuteDataSet("SELECT DISTINCT YEAR FROM SHIFT_DATE ORDER BY YEAR").Tables[0];
this.ddlStartYear.DataTextField = "YEAR";
this.ddlStartYear.DataValueField = "YEAR";
this.ddlEndYear.DataTextField = "YEAR";
this.ddlEndYear.DataValueField = "YEAR";
this.ddlEndYear.DataSource = this.ddlStartYear.DataSource = source;
this.ddlStartYear.DataBind();
this.ddlEndYear.DataBind();
// Set default value
DateTime current = DateTime.Now;
this.ddlStartYear.Text = current.AddYears(-1).Year.ToString();
this.ddlStartMonth.Text = "10";
this.ddlEndYear.Text = current.Year.ToString();
this.ddlEndMonth.Text = current.AddMonths(-1).Month.ToString();
}
private DataTable GetDataSource()
{
DbAccessHelper dbHelper = new DbAccessHelper(this.DBConnectionName);
string sql = @"select prod_size, prod_model, t.prod_name, s.subprod_name
from " + this.ProductTable + @" t, " + this.SubProductTable + @" s
where t.fab_id ='Fab2' and t.prod_size is not null and t.shop='" + this.ShopName + @"'
and t.prod_name = s.prod_name
and T.active_flag = 1" +
@"union
select prod_size, prod_model, t.prod_name, t.prod_name as subprod_name
from " + this.ProductTable + @" t
where t.fab_id ='Fab2' and t.prod_size is not null and t.shop='" + this.ShopName + @"'
and T.active_flag = 1 " +
@"ORDER BY prod_size, prod_model, prod_name";
try
{
return(dbHelper.ExecuteDataSet(sql).Tables[0]);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
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 DataSet ExecuteDataSet(IDbConnection connection, string cmdText, IList <IDbDataParameter> parameter)
{
return(DbAccessHelper.ExecuteDataSet((OleDbConnection)connection, CommandType.Text, cmdText, parameter == null ? null : parameter.Cast <OleDbParameter>().ToArray()));
}
