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); // AND (NOTE_CONTENT LIKE '%主管決議:是%' OR NOTE_CONTENT LIKE '%主管決議: 是%' OR NOTE_CONTENT LIKE '%主管決議: 是%') string cmdTxt = @" SELECT [tb_USER].[USER_NAME],[COMPANY_NAME] ,REPLACE([NOTE_CONTENT],char(10),'<br/>') AS [NOTE_CONTENT] ,[tb_NOTE].[CREATE_DATETIME],CASE WHEN ([tb_NOTE].[FILE_NAME] LIKE '%.J%' OR [tb_NOTE].[FILE_NAME] LIKE '%.j%' OR [tb_NOTE].[FILE_NAME] LIKE '%.P%' OR [tb_NOTE].[FILE_NAME] LIKE '%.p%' ) THEN [tb_NOTE].[FILE_NAME] ELSE NULL END AS [FILE_NAME] FROM [HJ_BM_DB].[dbo].[tb_NOTE],[HJ_BM_DB].[dbo].[tb_COMPANY] LEFT JOIN [HJ_BM_DB].[dbo].[tb_USER] ON [USER_ID]=[OWNER_ID] LEFT JOIN [HJ_BM_DB].[dbo].[COPSALES] ON [COPSALES].[USER_ID]=[tb_USER].[USER_ID] WHERE [tb_COMPANY].COMPANY_ID=[tb_NOTE].COMPANY_ID AND CONVERT(nvarchar,[tb_NOTE].[CREATE_DATETIME],111)>=@SDATE AND CONVERT(nvarchar,[tb_NOTE].[CREATE_DATETIME],111)<=@EDATE AND [tb_NOTE].[COMPANY_ID]<>'0' ORDER BY [ORDERS],[USER_NAME],[COMPANY_NAME], [tb_NOTE].[CREATE_DATETIME] "; 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 static DataTable SEARCHCOMPANYNAME(string searchString) { DataTable dt = new DataTable(); string connectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); StringBuilder cmdTxt = new StringBuilder(); cmdTxt.AppendFormat(@" SELECT [COMPANY_ID] ,[COMPANY_NAME] FROM [HJ_BM_DB].[dbo].[tb_COMPANY] WHERE [COMPANY_NAME] LIKE @COMPANY_NAME+'%' ORDER BY [COMPANY_NAME] "); m_db.AddParameter("@COMPANY_NAME", searchString); dt.Load(m_db.ExecuteReader(cmdTxt.ToString())); if (dt.Rows.Count > 0) { return(dt); } else { return(null); } }
private void BindGrid() { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT [ID] ,[MA001] ,[MA002] ,[CLIENTS] ,REPLACE([OPERATIONS],char(10),'<br/>') AS [OPERATIONS] ,REPLACE([COMMENTS],char(10),'<br/>') AS [COMMENTS] FROM [TKBUSINESS].[dbo].[COPCOPMACLIENT] ORDER BY [MA001],[CLIENTS] "; //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 BindDropDownList() { DataTable dt = new DataTable(); dt.Columns.Add("PARAID", typeof(String)); dt.Columns.Add("PARANAME", typeof(String)); string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT [ID],[KIND],[PARAID],[PARANAME] FROM [TKRESEARCH].[dbo].[TBPARA] WHERE [KIND]='MEMOSTATUS' ORDER BY [PARAID] "; dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { DropDownList1.DataSource = dt; DropDownList1.DataTextField = "PARANAME"; DropDownList1.DataValueField = "PARANAME"; DropDownList1.DataBind(); } else { } }
private void BindGrid() { 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',[TB_EIP_SCH_WORK].[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] 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') AND DATEDIFF(day, [TB_EIP_SCH_WORK].[END_TIME],GETDATE())>=0 ORDER BY [EXECUTE_USER],[TB_EIP_SCH_WORK].[END_TIME],[SUBJECT] "; DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); Grid1.DataSource = dt; Grid1.DataBind(); }
public void SEARCHTBSALESDEVMEMO(string ID) { StringBuilder cmdTxt = new StringBuilder(); string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); cmdTxt.AppendFormat(@" SELECT [ID] ,[YEARS] ,[NAMES] FROM [TKBUSINESS].[dbo].[TBPROMOTIONNFEE] WHERE [ID]=@ID "); m_db.AddParameter("@ID", ID); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt.ToString())); if (dt.Rows.Count > 0) { TextBox2.Text = dt.Rows[0]["NAMES"].ToString(); } }
private void BindGrid(string SDATE) { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT MQ002,TC001,TC002,CONVERT(INT,TC029) TC029,CONVERT(INT,TC030) TC030,CONVERT(INT,(TC029+TC030)) AS MONEYS,TC053 AS TC053 , ( SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN '' ELSE '<br />' END +'是否生產:'+COPTD.UDF01+' 序號:'+CONVERT(NVARCHAR,TD003)+' '+CONVERT(NVARCHAR,TD005)+'-訂單數量'+CONVERT(NVARCHAR,CONVERT(int,TD008))+'-單價'+CONVERT(NVARCHAR,CONVERT(decimal(16,3),TD011))+'-贈品量'+CONVERT(NVARCHAR,CONVERT(INT,TD024)) AS 'data()' FROM [TK].dbo.COPTD WHERE TD001=TC001 AND TD002=TC002 FOR XML PATH(''), TYPE ).value('.','nvarchar(max)') As DETAILS FROM [TK].dbo.COPTC,[TK].dbo.CMSMQ WHERE TC001=MQ001 AND TC027='N' AND TC003=@SDATE ORDER BY TC001,TC002 "; m_db.AddParameter("@SDATE", SDATE); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); Grid1.DataSource = dt; Grid1.DataBind(); }
public string SEARCHCARDNO(string Account) { string connectionString = ConfigurationManager.ConnectionStrings["CHIYUconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT [EmployeeID],[CardNo] FROM [CHIYU].[dbo].[Person] WHERE [EmployeeID]=@Account "; m_db.AddParameter("@Account", Account); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { return(dt.Rows[0]["CardNo"].ToString()); } else { return(null); } }
public void SEARCHTTBPROJECTSSALES(string ID) { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT [ID] , [YEARS],[WEEKS],[DAYS],[STORES],[NAMES],[TARGETS],[FEES],[ITEMS] FROM [TKBUSINESS].[dbo].[TBPROJECTSSALES] WHERE [ID]=@ID "; m_db.AddParameter("@ID", ID); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { TextBox1.Text = dt.Rows[0]["YEARS"].ToString(); TextBox2.Text = dt.Rows[0]["WEEKS"].ToString(); DropDownList1.SelectedValue = dt.Rows[0]["STORES"].ToString().Trim(); TextBox3.Text = dt.Rows[0]["NAMES"].ToString(); TextBox4.Text = dt.Rows[0]["TARGETS"].ToString(); TextBox5.Text = dt.Rows[0]["DAYS"].ToString(); TextBox6.Text = dt.Rows[0]["ITEMS"].ToString(); TextBox7.Text = dt.Rows[0]["FEES"].ToString(); } }
private void BindGrid3(string SDAY, string EDAY) { string connectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); this.Session["SDAY"] = SDAY; this.Session["EDAY"] = EDAY; string cmdTxt = @" SELECT [tb_USER].USER_ACCOUNT,[tb_USER].USER_NAME,[tb_USER].GROUP_NAME,[tb_COMPANY].COMPANY_NAME FROM [HJ_BM_DB].[dbo].[tb_USER],[HJ_BM_DB].[dbo].[tb_COMPANY] WHERE [tb_USER].USER_ID=[tb_COMPANY].OWNER_ID AND COMPANY_ID NOT IN ( SELECT COMPANY_ID FROM [HJ_BM_DB].[dbo].[tb_NOTE] WHERE CONVERT(NVARCHAR,[tb_NOTE].[UPDATE_DATETIME],111)>=@SDAY AND CONVERT(NVARCHAR,[tb_NOTE].[UPDATE_DATETIME],111)<=@EDAY ) GROUP BY [tb_USER].USER_ACCOUNT,[tb_USER].USER_NAME,[tb_USER].GROUP_NAME,[tb_COMPANY].COMPANY_NAME ORDER BY [tb_USER].USER_ACCOUNT,[tb_USER].USER_NAME,[tb_USER].GROUP_NAME,[tb_COMPANY].COMPANY_NAME "; m_db.AddParameter("@SDAY", SDAY); m_db.AddParameter("@EDAY", EDAY); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); Grid3.DataSource = dt; Grid3.DataBind(); }
private void BindGrid(string SDAY, string EDAY) { string connectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); this.Session["SDAY"] = SDAY; this.Session["EDAY"] = EDAY; string cmdTxt = @" SELECT USER_ACCOUNT,USER_NAME,GROUP_NAME,NOTENUMS,COPNOTENUMS,TOTALNUMS,CONVERT(decimal(16,2),CONVERT(DECIMAL,COPNOTENUMS)/CONVERT(DECIMAL,TOTALNUMS)*100) AS PCTS FROM( SELECT [tb_USER].USER_ACCOUNT,[tb_USER].USER_NAME,[tb_USER].GROUP_NAME ,(SELECT COUNT([NOTE_CONTENT]) FROM [HJ_BM_DB].[dbo].[tb_NOTE] WHERE [tb_NOTE].CREATE_USER_ID=[tb_USER].USER_ID AND CONVERT(NVARCHAR,[UPDATE_DATETIME],111)>=@SDAY AND CONVERT(NVARCHAR,[UPDATE_DATETIME],111)<=@EDAY ) NOTENUMS ,(SELECT COUNT( DISTINCT COMPANY_ID) FROM [HJ_BM_DB].[dbo].[tb_NOTE] WHERE [tb_NOTE].CREATE_USER_ID=[tb_USER].USER_ID AND CONVERT(NVARCHAR,[UPDATE_DATETIME],111)>=@SDAY AND CONVERT(NVARCHAR,[UPDATE_DATETIME],111)<=@EDAY ) COPNOTENUMS ,(SELECT DISTINCT COUNT(COMP.COMPANY_NAME) FROM [HJ_BM_DB].[dbo].[tb_COMPANY] COMP WHERE COMP.OWNER_ID=[tb_USER].[USER_ID]) TOTALNUMS FROM [HJ_BM_DB].[dbo].[tb_USER] ) AS TEMP WHERE NOTENUMS>0 ORDER BY TEMP.GROUP_NAME,TEMP.USER_ACCOUNT "; m_db.AddParameter("@SDAY", SDAY); m_db.AddParameter("@EDAY", EDAY); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); Grid1.DataSource = dt; Grid1.DataBind(); }
public void OnBeforeExport(object sender, Ede.Uof.Utility.Component.BeforeExportEventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string STATUS = DropDownList1.Text; string cmdTxt = @" "; m_db.AddParameter("@STATUS", STATUS); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { dt.Columns[0].Caption = "編號"; e.Datasource = dt; } }
private void BindGrid2(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/>') 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') 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] "; m_db.AddParameter("@SDATE", SDATE); m_db.AddParameter("@EDATE", EDATE); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); 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) "; 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 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 [USER_NAME],[COMPANY_NAME] ,[NOTE_CONTENT] ,[tb_NOTE].[CREATE_DATETIME] FROM [HJ_BM_DB].[dbo].[tb_NOTE],[HJ_BM_DB].[dbo].[tb_COMPANY] LEFT JOIN [HJ_BM_DB].[dbo].[tb_USER] ON [USER_ID]=[OWNER_ID] WHERE [tb_COMPANY].COMPANY_ID=[tb_NOTE].COMPANY_ID AND CONVERT(nvarchar,[tb_NOTE].[CREATE_DATETIME],111)>=@SDATE AND CONVERT(nvarchar,[tb_NOTE].[CREATE_DATETIME],111)<=@EDATE ORDER BY [USER_NAME],[COMPANY_NAME], [tb_NOTE].[CREATE_DATETIME] "; 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 BindGrid3(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 [USER_NAME],[COMPANY_NAME],[OPPORTUNITY_NAME],[PRODUCT],REPLACE([NOTE_CONTENT],char(10),'<br/>') AS [NOTE_CONTENT] ,[tb_NOTE].[CREATE_DATETIME],Replace(Convert(Varchar(12),CONVERT(money,ISNULL([tb_OPPORTUNITY].[AMOUNT],0)),1),'.00','') AS AMOUNT,(CASE WHEN [USER_NAME]='公司' THEN '蔡顏鴻' ELSE [USER_NAME] END ) AS TEMP FROM [HJ_BM_DB].[dbo].[tb_NOTE],[HJ_BM_DB].[dbo].[tb_OPPORTUNITY] LEFT JOIN [HJ_BM_DB].[dbo].[tb_USER] ON [USER_ID]=[OWNER_ID] LEFT JOIN [HJ_BM_DB].[dbo].[tb_COMPANY] ON [tb_OPPORTUNITY].[COMPANY_ID]=[tb_COMPANY].[COMPANY_ID] WHERE [tb_NOTE].[OPPORTUNITY_ID]=[tb_OPPORTUNITY].[OPPORTUNITY_ID] AND CONVERT(nvarchar,[tb_NOTE].[CREATE_DATETIME],111)>=@SDATE AND CONVERT(nvarchar,[tb_NOTE].[CREATE_DATETIME],111)<=@EDATE ORDER BY TEMP,[USER_NAME],[COMPANY_NAME],[tb_NOTE].[CREATE_DATETIME] "; m_db.AddParameter("@SDATE", SDATE); m_db.AddParameter("@EDATE", EDATE); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); Grid3.DataSource = dt; Grid3.DataBind(); }
public void OnBeforeExport(object sender, Ede.Uof.Utility.Component.BeforeExportEventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT [USER_NAME],[COMPANY_NAME] ,[NOTE_CONTENT] ,[tb_NOTE].[CREATE_DATETIME] FROM [HJ_BM_DB].[dbo].[tb_NOTE],[HJ_BM_DB].[dbo].[tb_COMPANY] LEFT JOIN [HJ_BM_DB].[dbo].[tb_USER] ON [USER_ID]=[OWNER_ID] WHERE [tb_COMPANY].COMPANY_ID=[tb_NOTE].COMPANY_ID AND CONVERT(nvarchar,[tb_NOTE].[CREATE_DATETIME],111)>=@SDATE AND CONVERT(nvarchar,[tb_NOTE].[CREATE_DATETIME],111)<=@EDATE ORDER BY [USER_NAME],[COMPANY_NAME],[tb_NOTE].[CREATE_DATETIME] "; m_db.AddParameter("@SDATE", txtDate1.Text); m_db.AddParameter("@EDATE", txtDate2.Text); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { dt.Columns[0].Caption = "客戶"; dt.Columns[1].Caption = "記錄"; dt.Columns[2].Caption = "記錄日期"; e.Datasource = dt; } }
public void SEARCHTCOPCOPMACLIENT(string ID) { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT [ID] ,[MA001] ,[MA002] ,[CLIENTS] ,[OPERATIONS] ,[COMMENTS] FROM [TKBUSINESS].[dbo].[COPCOPMACLIENT] WHERE [ID]=@ID ORDER BY MA001 "; m_db.AddParameter("@ID", ID); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { TextBox1.Text = dt.Rows[0]["MA001"].ToString(); TextBox2.Text = dt.Rows[0]["MA002"].ToString(); TextBox3.Text = dt.Rows[0]["CLIENTS"].ToString(); TextBox4.Text = dt.Rows[0]["OPERATIONS"].ToString(); TextBox5.Text = dt.Rows[0]["COMMENTS"].ToString(); } }
private void BindDropDownList() { DataTable dt = new DataTable(); dt.Columns.Add("SALESFOCUS", typeof(String)); string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT '全部' AS SALESFOCUS UNION ALL SELECT SALESFOCUS FROM [TKBUSINESS].[dbo].[PRODUCTS] GROUP BY SALESFOCUS "; dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { DropDownList1.DataSource = dt; DropDownList1.DataTextField = "SALESFOCUS"; DropDownList1.DataValueField = "SALESFOCUS"; DropDownList1.DataBind(); } else { } }
private void BindGrid(string ID) { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT [ID] ,[MID] ,CONVERT(NVARCHAR, [ADDDATES],120) AS ADDDATES ,REPLACE([COMMENTS],char(10),'<br/>') AS [COMMENTS] ,[FILENAME] FROM [TKBUSINESS].[dbo].[TBSALESEVENTSCOMMENTS] WHERE [MID]=@ID ORDER BY [ADDDATES] DESC "; m_db.AddParameter("@ID", ID); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); Grid1.DataSource = dt; Grid1.DataBind(); }
private void BindGrid2(string SYEARS) { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT MQ002,TE001,TE002,TE003,MA002 , ( SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN '' ELSE '<br />' END +'序號-'+CONVERT(NVARCHAR,TF104)+'-'+CONVERT(NVARCHAR,TF006)+'-新訂單數量'+CONVERT(NVARCHAR,CONVERT(INT,TF009))+'-新單價'+CONVERT(NVARCHAR,TF013)+'-新贈品量'+CONVERT(NVARCHAR,CONVERT(INT,TF020)) AS 'data()' FROM [TK].dbo.COPTF WHERE TE001=TF001 AND TE002=TF002 AND TE003=TF003 FOR XML PATH(''), TYPE ).value('.','nvarchar(max)') As DETAILS FROM [TK].dbo.COPTE,[TK].dbo.COPMA,[TK].dbo.CMSMQ WHERE TE007=MA001 AND TE001=MQ001 AND TE005='N' AND TE029='N' AND TE002 LIKE @SYEARS+'%' ORDER BY TE001,TE002,TE003 "; m_db.AddParameter("@SYEARS", SYEARS); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); Grid2.DataSource = dt; Grid2.DataBind(); }
public string SEARCHTBSALESEVENTSCOMMENTSMAXID(string SERACHKEYS) { DataTable dt = new DataTable(); string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); StringBuilder cmdTxt = new StringBuilder(); cmdTxt.AppendFormat(@" SELECT TOP 1 MAX([ID]) ID FROM [TKBUSINESS].[dbo].[TBSALESEVENTSCOMMENTS] WHERE [COMMENTS]=@SERACHKEYS "); m_db.AddParameter("@SERACHKEYS", SERACHKEYS); dt.Load(m_db.ExecuteReader(cmdTxt.ToString())); if (dt.Rows.Count > 0) { return(dt.Rows[0]["ID"].ToString()); } else { return(null); } }
private void BindDropDownList() { DataTable dt = new DataTable(); dt.Columns.Add("PARAID", typeof(String)); dt.Columns.Add("PARANAME", typeof(String)); string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT [ID],[KIND],[PARAID],[PARANAME] FROM [TKRESEARCH].[dbo].[TBPARA] WHERE [KIND]='MEMOSTATUS' ORDER BY [PARAID] "; dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { DropDownList1.DataSource = dt; DropDownList1.DataTextField = "PARANAME"; DropDownList1.DataValueField = "PARANAME"; DropDownList1.DataBind(); } else { } //DataSet ds = new DataSet(); //DatabaseHelper DbQuery = new DatabaseHelper(); //DataTable dt = new DataTable(); //DataRow ndr = dt.NewRow(); //dt.Columns.Add("PARAID", typeof(String)); //dt.Columns.Add("PARANAME", typeof(String)); //string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); //using (SqlConnection conn = new SqlConnection(connectionString)) //{ // SqlCommand command = new SqlCommand(@" SELECT [ID],[KIND],[PARAID],[PARANAME] FROM [TKRESEARCH].[dbo].[TBPARA] WHERE [KIND]='MEMOSTATUS' ORDER BY [PARAID]", conn); // ds.Clear(); // SqlDataAdapter adapter = new SqlDataAdapter(command); // conn.Open(); // adapter.Fill(ds, command.ToString()); // if (ds.Tables[0].Rows.Count > 0) // { // DropDownList1.DataSource = ds.Tables[0]; // DropDownList1.DataTextField = "PARANAME"; // DropDownList1.DataValueField = "PARANAME"; // DropDownList1.DataBind(); // } // else // { // } //} }
public string SEARCHCREATE_USER_ID(string USER_NAME) { DataTable dt = new DataTable(); string connectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); StringBuilder cmdTxt = new StringBuilder(); cmdTxt.AppendFormat(@" SELECT [USER_ID] ,[USER_NAME] FROM [HJ_BM_DB].[dbo].[tb_USER] WHERE [USER_NAME]=@USER_NAME "); m_db.AddParameter("@USER_NAME", USER_NAME); dt.Load(m_db.ExecuteReader(cmdTxt.ToString())); if (dt.Rows.Count > 0) { return(dt.Rows[0]["USER_ID"].ToString()); } else { return(null); } }
private void BindDropDownList3() { DataTable dt = new DataTable(); dt.Columns.Add("ISCLOSES", typeof(String)); string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @"SELECT [ID],[ISCLOSES] FROM [TKBUSINESS].[dbo].[TBSALESCLOSES] ORDER BY [ID]"; dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { DropDownList3.DataSource = dt; DropDownList3.DataTextField = "ISCLOSES"; DropDownList3.DataValueField = "ISCLOSES"; DropDownList3.DataBind(); } else { } }
public int SEARCHTBSALESEVENTSCOMMENTS(string ID) { DataTable dt = new DataTable(); string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); StringBuilder cmdTxt = new StringBuilder(); cmdTxt.AppendFormat(@" SELECT [ID] ,[MID] ,[ADDDATES] ,[COMMENTS] ,[FILENAME] FROM [TKBUSINESS].[dbo].[TBSALESEVENTSCOMMENTS] WHERE [MID]=@MID "); m_db.AddParameter("@MID", ID); dt.Load(m_db.ExecuteReader(cmdTxt.ToString())); if (dt.Rows.Count > 0) { return(dt.Rows.Count); } else { return(0); } }
private void BindDropDownList1(string NAME) { DataTable dt = new DataTable(); dt.Columns.Add("NAME", typeof(String)); string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @"SELECT [ID],[NAME] FROM [TKBUSINESS].[dbo].[TBSALESNAME] WHERE ([NAME]=@NAME OR [LEADER] LIKE '%'+@NAME+'%' ) ORDER BY [ID]"; m_db.AddParameter("@NAME", NAME); dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { DropDownList1.DataSource = dt; DropDownList1.DataTextField = "NAME"; DropDownList1.DataValueField = "NAME"; DropDownList1.DataBind(); } else { } }
private void BindGrid12() { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); StringBuilder cmdTxt = new StringBuilder(); StringBuilder QUERYS = new StringBuilder(); cmdTxt.AppendFormat(@" SELECT [ID] ,[KINDS] ,[NAMES] ,[MOQS] ,[INDAYS] ,[COMMENTS] FROM [TKBUSINESS].[dbo].[TBPURMOQ] WHERE [KINDS]=@KINDS ORDER BY [ID] "); m_db.AddParameter("@KINDS", "樣品"); //m_db.AddParameter("@EDATE", EDATE); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt.ToString())); Grid12.DataSource = dt; Grid12.DataBind(); }
private void BindDropDownList2() { DataTable dt = new DataTable(); dt.Columns.Add("ORDER", typeof(String)); string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT '依該庫別的金額' AS ORDERS UNION ALL SELECT '依該庫別的數量' AS ORDERS UNION ALL SELECT '依品號' AS ORDERS "; dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { DropDownList2.DataSource = dt; DropDownList2.DataTextField = "ORDERS"; DropDownList2.DataValueField = "ORDERS"; DropDownList2.DataBind(); } else { } }
public void SEARCHTPRODUCTS(string ID) { string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString(); Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString); string cmdTxt = @" SELECT [PRODUCTS].[MB001],[PRODUCTSFEATURES],[SALESFOCUS],[COPYWRITINGS],[PICPATHS] ,[PRICES1],[PRICES2],[PRICES3] ,[MB002],[MOQS] FROM [TKBUSINESS].[dbo].[PRODUCTS] LEFT JOIN [TK].dbo.[INVMB] ON [PRODUCTS].[MB001]=[INVMB].[MB001] WHERE [PRODUCTS].[MB001]=@MB001 "; m_db.AddParameter("@MB001", ID); DataTable dt = new DataTable(); dt.Load(m_db.ExecuteReader(cmdTxt)); if (dt.Rows.Count > 0) { TextBox1.Text = dt.Rows[0]["MB002"].ToString(); TextBox2.Text = dt.Rows[0]["PRODUCTSFEATURES"].ToString(); TextBox3.Text = dt.Rows[0]["SALESFOCUS"].ToString(); TextBox4.Text = dt.Rows[0]["PRICES1"].ToString(); TextBox5.Text = dt.Rows[0]["PRICES2"].ToString(); TextBox6.Text = dt.Rows[0]["PRICES3"].ToString(); TextBox7.Text = dt.Rows[0]["MOQS"].ToString(); } }