private void BindGrid(string SDATE, string EDATE) { string connectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT USER1.[NAME] AS 'NAME1',REPLACE([TB_EIP_SCH_WORK].[SUBJECT],char(10),'<br/>') AS SUBJECT ,(SELECT TOP 1 ISNULL([DESCRIPTION],'') FROM [UOF].[dbo].[TB_EIP_SCH_WORK_RECORD] WHERE [TB_EIP_SCH_WORK_RECORD].[WORK_GUID]=[TB_EIP_SCH_WORK].[WORK_GUID] ORDER BY CREATE_TIME DESC) AS 'DESCRIPTION' ,CONVERT(NVARCHAR,[TB_EIP_SCH_WORK].[END_TIME],111) AS END_TIME,DATEDIFF(day, [TB_EIP_SCH_WORK].[END_TIME],GETDATE()) AS DIFFDATES,USER2.[NAME] AS 'NAME2' ,CASE WHEN [TB_EIP_SCH_WORK].[WORK_STATE]='NotYetBegin' THEN '當未開始' ELSE '進行中' END AS 'STATUS' ,[TB_EIP_SCH_WORK].[WORK_STATE],[TB_EIP_SCH_WORK].[EXECUTE_USER],[TB_EIP_SCH_WORK].[SOURCE_USER] ,[TB_EIP_SCH_WORK].WORK_GUID FROM [UOF].[dbo].[TB_EIP_SCH_WORK] LEFT JOIN [UOF].[dbo].[TB_EB_USER] USER1 ON USER1.USER_GUID=[TB_EIP_SCH_WORK].[EXECUTE_USER] LEFT JOIN [UOF].[dbo].[TB_EB_USER] USER2 ON USER2.USER_GUID=[TB_EIP_SCH_WORK].[SOURCE_USER] WHERE [WORK_STATE] IN ('NotYetBegin','Proceeding','Audit') AND USER1.[NAME] IN ('洪櫻芬','王琇平','葉枋俐','何姍怡','林琪琪','林杏育','張釋予','蔡顏鴻','陳帟靜','黃鈺涵') AND CONVERT(NVARCHAR,[TB_EIP_SCH_WORK].[END_TIME],111) >=@SDATE AND CONVERT(NVARCHAR,[TB_EIP_SCH_WORK].[END_TIME],111) <=@EDATE ORDER BY [EXECUTE_USER],[TB_EIP_SCH_WORK].[END_TIME],[SUBJECT] "; //string cmdTxt = @" // SELECT TOP 1 // 'NAME1'AS 'NAME1','SUBJECT' AS SUBJECT // ,'DESCRIPTION' AS 'DESCRIPTION' // ,'' AS END_TIME // ,'' AS DIFFDATES // ,'NAME2' AS 'NAME2' // ,'STATUS' AS 'STATUS' // ,'1' 'WORK_STATE','' 'EXECUTE_USER','' 'SOURCE_USER' // ,'' 'WORK_GUID' // FROM [UOF].[dbo].TB_DMS_AGENCY_SUBSCRIBE // "; m_db.AddParameter("@SDATE", SDATE); m_db.AddParameter("@EDATE", EDATE); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); Grid1.DataSource = dt; Grid1.DataBind(); }
private void BindGrid2(string SALESFOCUS) { string connectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); StringBuilder cmdTxt = new StringBuilder(); StringBuilder QUERYS = new StringBuilder(); //狀態 if (!string.IsNullOrEmpty(DropDownList1.Text)) { if (DropDownList1.Text.Equals("Y")) { QUERYS.AppendFormat(@" AND WORK_STATE IN ('Completed') "); } else if (DropDownList1.Text.Equals("N")) { QUERYS.AppendFormat(@" AND WORK_STATE NOT IN ('Completed') "); } } //校稿名稱 if (!string.IsNullOrEmpty(TextBox1.Text)) { QUERYS.AppendFormat(@" AND SUBJECT LIKE '%'+@SUBJECT+'%' "); } cmdTxt.AppendFormat(@" SELECT SUBJECT,CASE WHEN WORK_STATE='Completed' THEN '已完成' WHEN WORK_STATE='NotYetBegin' THEN '尚未開始' WHEN WORK_STATE='Audit' THEN '交付人審查中' WHEN WORK_STATE='Proceeding' THEN '進行中' ELSE WORK_STATE END WORK_STATE ,USER1.NAME AS '交付者',USER2.NAME AS '執行者',CONVERT(nvarchar,CREATE_TIME,112 ) CREATE_TIME,CONVERT(nvarchar,END_TIME,112 ) END_TIME ,(SELECT TOP 1 DESCRIPTION FROM [UOF].dbo.TB_EIP_SCH_WORK_RECORD WHERE TB_EIP_SCH_WORK_RECORD.WORK_GUID=TB_EIP_SCH_WORK.WORK_GUID ORDER BY CREATE_TIME DESC) AS 'DESCRIPTION' ,WORK_GUID,CREATE_USER,EXECUTE_USER FROM [UOF].dbo.TB_EIP_SCH_WORK LEFT JOIN [UOF].dbo.TB_EB_USER USER1 ON USER1.USER_GUID=CREATE_USER LEFT JOIN [UOF].dbo.TB_EB_USER USER2 ON USER2.USER_GUID=EXECUTE_USER WHERE 1=1 {0} ORDER BY SUBJECT,CREATE_TIME DESC ", QUERYS.ToString()); //m_db.AddParameter("@SDATE", SDATE); //m_db.AddParameter("@EDATE", EDATE); m_db.AddParameter("@SUBJECT", TextBox1.Text); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt.ToString())); Grid2.DataSource = dt; Grid2.DataBind(); }
private void BindGrid(string SDATE, string EDATE) { string connectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT [TB_EB_USER].[USER_GUID],[tb_USER].[USER_ID],[ACCOUNT],[NAME],CONVERT(NVARCHAR,[START_TIME],111) AS START_TIME,SUBJECT,DESCRIPTION FROM [UOF].[dbo].[TB_EB_USER],[UOF].dbo.[TB_EIP_SCH_WORK],[HJ_BM_DB].[dbo].[tb_USER] WHERE [TB_EB_USER].[USER_GUID]=[TB_EIP_SCH_WORK].EXECUTE_USER AND [tb_USER].[USER_GUID]=[TB_EB_USER].[USER_GUID] AND [SOURCE_TYPE]='Self' AND [NAME] IN ('洪櫻芬','王琇平','葉枋俐','何姍怡','林琪琪','林杏育','張釋予','蔡顏鴻','陳帟靜','黃鈺涵') AND CONVERT(NVARCHAR,[START_TIME],111)>=@SDATE AND CONVERT(NVARCHAR,[START_TIME],111)<=@EDATE ORDER BY [NAME],CONVERT(NVARCHAR,[START_TIME],112) "; //string cmdTxt = @" // SELECT TOP 1 // '' 'USER_GUID' // ,'' '[USER_ID' // ,'' 'ACCOUNT' // ,'' 'NAME' // ,'' AS START_TIME // ,''SUBJECT // ,'' DESCRIPTION // FROM [UOF].[dbo].TB_DMS_AGENCY_SUBSCRIBE // "; m_db.AddParameter("@SDATE", SDATE); m_db.AddParameter("@EDATE", EDATE); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); Grid1.DataSource = dt; Grid1.DataBind(); }
private void BindGrid(string MB001) { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); this.Session["MB001"] = MB001; string cmdTxt = @" SELECT TOP 100 MB001,MB002 FROM [TK].dbo.INVMB WHERE MB001 LIKE @MB001+'%' ORDER BY MB001 "; m_db.AddParameter("@MB001", MB001); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); Grid1.DataSource = dt; Grid1.DataBind(); }
public string SEARCHCMSMV(string MV001) { try { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT MV001,MV004 FROM [TK].[dbo].[CMSMV] WHERE MV001=@MV001 "; m_db.AddParameter("@MV001", MV001); //m_db.AddParameter("@EDATE", EDATE); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { return(dt.Rows[0]["MV004"].ToString()); } else { return(""); } } catch { return(""); } finally { } }