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
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 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();
}
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();
}
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
{
}
}
public string GetFormNumber(string formId, string userGroupId, string formValueXML)
{
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 TOP 1 [CURRENT_NO]
FROM [UOF].[dbo].[TK_WKF_AUTONO]
WHERE [TRACK_ID]='{0}' AND [AUTO_YEAR]='{1}' AND [AUTO_MONTH]='{2}' AND [AUTO_DAY]='{3}'
", TRACK_ID, DateTime.Now.ToString("yyyy"), DateTime.Now.ToString("MM"), DateTime.Now.ToString("dd"));
DataTable dt = new DataTable();
dt.Load(m_db.ExecuteReader(cmdTxt.ToString()));
if (dt.Rows.Count >= 1)
{
string MAXNO = TRACK_ID + DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd") + GETMAXNO(dt.Rows[0]["CURRENT_NO"].ToString());
UPDATETK_WKF_AUTONO(MAXNO);
return(MAXNO);
}
else
{
string MAXNO = TRACK_ID + DateTime.Now.ToString("yyyy") + DateTime.Now.ToString("MM") + DateTime.Now.ToString("dd") + "0001";
INSERTTK_WKF_AUTONO(MAXNO);
return(MAXNO);
}
//throw new NotImplementedException();
}