private void BindGrid(string SDATE, string EDATE)
{
string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString();
Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString);
string cmdTxt = @" SELECT MD002,ISNULL(MA002,'') AS MA002,TA003,TA034,CASE WHEN TA007<>'kg' THEN CONVERT(INT,TA015) ELSE 0 END AS TA015,CASE WHEN TA007='kg' THEN TA015 ELSE 0 END AS TA015KG,TA007
FROM [TK].dbo.CMSMD,[TK].dbo.MOCTA
LEFT JOIN [TK].dbo.COPTC ON TC001=TA026 AND TC002=TA027
LEFT JOIN [TK].dbo.COPMA ON MA001=TC004
WHERE TA021=MD001
AND MD001 IN ('02','03','04','09')
AND TA003>=@SDATE AND TA003<=@EDATE
ORDER BY MD002,TA003,MA002
";
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();
}
private void BindGrid(string SDATE, string EDATE)
{
string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString();
Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString);
string cmdTxt = @" SELECT [MANU],[CLINET],CONVERT(NVARCHAR,[MANUDATE],112) AS MANUDATE,[MOCMANULINE].[MB002],CONVERT(INT,(ISNULL([PACKAGE],0))) AS PACKAGE,ISNULL([NUM],0) AS NUM,MB004
FROM [TKMOC].[dbo].[MOCMANULINE]
LEFT JOIN [TK].dbo.INVMB ON INVMB.MB001=[MOCMANULINE].MB001
WHERE CONVERT(NVARCHAR,[MANUDATE],112)>=@SDATE AND CONVERT(NVARCHAR,[MANUDATE],112)<=@EDATE
AND (NUM>0 OR [PACKAGE]>0)
ORDER BY [MANU],[MANUDATE],[CLINET],[MB002]
";
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 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 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 [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 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);
// 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();
}
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;
}
}
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 cmdTxt = @" SELECT [MANU],[CLINET],CONVERT(NVARCHAR,[MANUDATE],112) AS MANUDATE,[MOCMANULINE].[MB002],CONVERT(INT,(ISNULL([PACKAGE],0))) AS PACKAGE,ISNULL([NUM],0) AS NUM,MB004
FROM [TKMOC].[dbo].[MOCMANULINE]
LEFT JOIN [TK].dbo.INVMB ON INVMB.MB001=[MOCMANULINE].MB001
WHERE CONVERT(NVARCHAR,[MANUDATE],112)>=@SDATE AND CONVERT(NVARCHAR,[MANUDATE],112)<=@EDATE
AND (NUM>0 OR [PACKAGE]>0)
ORDER BY [MANU],[MANUDATE],[CLINET],[MB002]
";
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 = "預計生產日期";
dt.Columns[3].Caption = "品名";
dt.Columns[4].Caption = "包裝數";
dt.Columns[5].Caption = "重量";
e.Datasource = dt;
}
}
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] ,[NOTE_CONTENT] ,CONVERT(NVARCHAR,[tb_NOTE].[CREATE_DATETIME],111) AS [CREATE_DATETIME],CASE WHEN ([tb_NOTE].[FILE_NAME] LIKE '%Jpg%' OR [tb_NOTE].[FILE_NAME] LIKE '%JPG%' OR [tb_NOTE].[FILE_NAME] LIKE '%jpg%' OR [tb_NOTE].[FILE_NAME] LIKE '%png%' OR [tb_NOTE].[FILE_NAME] LIKE '%PNG%' OR [tb_NOTE].[FILE_NAME] LIKE '%Pmg%') 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]
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);
//string cmdTxt = @"
// SELECT CONVERT(NVARCHAR,DATES,111) DATES,[TB_EB_USER].[USER_GUID],[TB_EB_USER].[ACCOUNT],[TB_EB_USER].[NAME],[tb_USER].USER_ID,START_TIME,[SUBJECT],[DESCRIPTION],TEMP4.[CREATE_DATETIME],[COMPANY_NAME] ,[NOTE_CONTENT]
// FROM (
// SELECT DATEADD(d,rows-1,@SDATE) DATES from
// (
// SELECT
// ID,row_number()over(ORDER BY id) rows
// FROM
// sysobjects
// )TEMP
// WHERE
// TEMP.rows <= DATEDIFF(d,@SDATE, @EDATE) + 1
// ) AS TEMP2
// LEFT JOIN [UOF].[dbo].[TB_EB_USER] ON [TB_EB_USER].[NAME] IN ('洪櫻芬','王琇平','葉枋俐','何姍怡','林琪琪','林杏育','張釋予','蔡顏鴻','陳帟靜','黃鈺涵')
// LEFT JOIN [HJ_BM_DB].[dbo].[tb_USER] ON [tb_USER].USER_GUID=[TB_EB_USER].USER_GUID
// LEFT JOIN
// (
// SELECT [EXECUTE_USER],CONVERT(NVARCHAR,[START_TIME],111) AS START_TIME,[SUBJECT],[DESCRIPTION]
// FROM [UOF].dbo.[TB_EIP_SCH_WORK]
// WHERE [SOURCE_TYPE]='Self'
// ) AS TEMP3 ON TEMP3.[EXECUTE_USER]=[TB_EB_USER].USER_GUID AND START_TIME=CONVERT(NVARCHAR,DATES,111)
// LEFT JOIN
// (
// SELECT [OWNER_ID],[COMPANY_NAME] ,[NOTE_CONTENT],CONVERT(NVARCHAR,[tb_NOTE].[CREATE_DATETIME],111) CREATE_DATETIME
// FROM [HJ_BM_DB].[dbo].[tb_NOTE],[HJ_BM_DB].[dbo].[tb_COMPANY]
// WHERE [tb_COMPANY].COMPANY_ID=[tb_NOTE].COMPANY_ID
// ) AS TEMP4 ON TEMP4.OWNER_ID=USER_ID AND TEMP4.CREATE_DATETIME=CONVERT(NVARCHAR,DATES,111)
// WHERE (ISNULL([SUBJECT],'')<>'' OR ISNULL([NOTE_CONTENT],'')<>'')
// ORDER BY [NAME],CONVERT(NVARCHAR,DATES,111)
// ";
DataTable dt = new DataTable();
dt.Load(m_db.ExecuteReader(cmdTxt));
Grid3.DataSource = dt;
Grid3.DataBind();
}
public void UPDATEPRODUCTS(string ID, string PRODUCTSFEATURES, string SALESFOCUS, string COPYWRITINGS, string PRICES1, string PRICES2, string PRICES3, string MOQS)
{
string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString();
Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString);
string cmdTxt = @"
UPDATE [TKBUSINESS].[dbo].[PRODUCTS]
SET
[PRODUCTSFEATURES]=@PRODUCTSFEATURES
,[SALESFOCUS]=@SALESFOCUS
,[COPYWRITINGS]=@COPYWRITINGS
,[PRICES1]=@PRICES1
,[PRICES2]=@PRICES2
,[PRICES3]=@PRICES3
,[MOQS]=@MOQS
WHERE [MB001]=@ID
";
m_db.AddParameter("@ID", ID);
m_db.AddParameter("@PRODUCTSFEATURES", PRODUCTSFEATURES);
m_db.AddParameter("@SALESFOCUS", SALESFOCUS);
m_db.AddParameter("@COPYWRITINGS", COPYWRITINGS);
m_db.AddParameter("@PRICES1", PRICES1);
m_db.AddParameter("@PRICES2", PRICES2);
m_db.AddParameter("@PRICES3", PRICES3);
m_db.AddParameter("@MOQS", MOQS);
m_db.ExecuteNonQuery(cmdTxt);
}
public void UPDATETBPROJECTS(string ID, string YEARS, string WEEKS, string STORES, string NAMES, string ITEMS, string CONTENTS, string DAYS)
{
string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString();
Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString);
string cmdTxt = @"
UPDATE [TKBUSINESS].[dbo].[TBPROJECTS]
SET
[YEARS]=@YEARS
,[WEEKS]=@WEEKS
,[STORES]=@STORES
,[NAMES]=@NAMES
,[ITEMS]=@ITEMS
,[CONTENTS]=@CONTENTS
,[DAYS]=@DAYS
WHERE [ID]=@ID
";
m_db.AddParameter("@ID", ID);
m_db.AddParameter("@YEARS", YEARS);
m_db.AddParameter("@WEEKS", WEEKS);
m_db.AddParameter("@STORES", STORES);
m_db.AddParameter("@NAMES", NAMES);
m_db.AddParameter("@ITEMS", ITEMS);
m_db.AddParameter("@CONTENTS", CONTENTS);
m_db.AddParameter("@DAYS", DAYS);
m_db.ExecuteNonQuery(cmdTxt);
}
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();
}
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 void DELETETBPROMOTIONNFEEPRODUCTS(string MID, string ID)
{
string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString();
Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString);
string cmdTxt = @"
DELETE [TKBUSINESS].[dbo].[TBPROMOTIONNFEEPRODUCTS]
WHERE MID=@MID AND ID=@ID
";
m_db.AddParameter("@MID", MID);
m_db.AddParameter("@ID", ID);
m_db.ExecuteNonQuery(cmdTxt);
}
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();
}
}
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 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();
}
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);
}
}
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);
}
}
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 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 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);
}
}
public void UPDATETBDEVMEMO(string PID, string MEMO)
{
string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString();
Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString);
string cmdTxt = @" UPDATE [TKRESEARCH].[dbo].[TBDEVMEMO]
SET [MEMO]=@MEMO
WHERE [ID]=@ID
";
m_db.AddParameter("@ID", PID);
m_db.AddParameter("@MEMO", MEMO);
m_db.ExecuteNonQuery(cmdTxt);
}
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();
}
}
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);
}
}
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
{
}
}
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();
}
}
