public void UPDATETBPROMOTIONNFEE(string ID, string YEARS, string SALES, string NAMES, string KINDS, string PROMOTIONS, string PROMOTIONSSETS, string SDATES, string CLIENTS, string STORES, string SALESNUMS, string SALESMONEYS, string COSTMONEYS, string FEEMONEYS, string PROFITS, string COMMENTS, string DEPNAME, string TITLES, string ACTSALESMONEYS, string ACTCOSTMONEYS, string ACTFEEMONEYS, string ACTPROFITS)
{
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].[TBPROMOTIONNFEE]
SET
[YEARS]=@YEARS
,[DEPNAME]=@DEPNAME
,[TITLES]=@TITLES
,[SALES]=@SALES
,[NAMES]=@NAMES
,[KINDS]=@KINDS
,[PROMOTIONS]=@PROMOTIONS
,[PROMOTIONSSETS]=@PROMOTIONSSETS
,[SDATES]=@SDATES
,[CLIENTS]=@CLIENTS
,[STORES]=@STORES
,[SALESNUMS]=@SALESNUMS
,[SALESMONEYS]=@SALESMONEYS
,[PROFITS]=@PROFITS
,[COMMENTS]=@COMMENTS
,[COSTMONEYS]=@COSTMONEYS
,[FEEMONEYS]=@FEEMONEYS
,[ACTSALESMONEYS]=@ACTSALESMONEYS
,[ACTCOSTMONEYS]=@ACTCOSTMONEYS
,[ACTFEEMONEYS]=@ACTFEEMONEYS
,[ACTPROFITS]=@ACTPROFITS
WHERE [ID]= @ID
";
m_db.AddParameter("@ID", ID);
m_db.AddParameter("@YEARS", YEARS);
m_db.AddParameter("@DEPNAME", DEPNAME);
m_db.AddParameter("@TITLES", TITLES);
m_db.AddParameter("@SALES", SALES);
m_db.AddParameter("@NAMES", NAMES);
m_db.AddParameter("@KINDS", KINDS);
m_db.AddParameter("@PROMOTIONS", PROMOTIONS);
m_db.AddParameter("@PROMOTIONSSETS", PROMOTIONSSETS);
m_db.AddParameter("@SDATES", SDATES);
m_db.AddParameter("@CLIENTS", CLIENTS);
m_db.AddParameter("@STORES", STORES);
m_db.AddParameter("@SALESNUMS", SALESNUMS);
m_db.AddParameter("@SALESMONEYS", SALESMONEYS);
m_db.AddParameter("@PROFITS", PROFITS);
m_db.AddParameter("@COMMENTS", COMMENTS);
m_db.AddParameter("@COSTMONEYS", COSTMONEYS);
m_db.AddParameter("@FEEMONEYS", FEEMONEYS);
m_db.AddParameter("@ACTSALESMONEYS", ACTSALESMONEYS);
m_db.AddParameter("@ACTCOSTMONEYS", ACTCOSTMONEYS);
m_db.AddParameter("@ACTFEEMONEYS", ACTFEEMONEYS);
m_db.AddParameter("@ACTPROFITS", ACTPROFITS);
m_db.ExecuteNonQuery(cmdTxt);
}
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();
}
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 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);
//AND (NOTE_CONTENT LIKE '%主管決議:是%' OR NOTE_CONTENT LIKE '%主管決議: 是%' OR NOTE_CONTENT LIKE '%主管決議: 是%')
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();
}
//OnBeforeExport
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.SelectedDate.Value.ToString("yyyy/MM/dd"));
m_db.AddParameter("@EDATE", txtDate2.SelectedDate.Value.ToString("yyyy/MM/dd"));
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 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);
}
}
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 void UPDATETBPURMOQ(string ID, string KINDS, string NAMES, string MOQS, string INDAYS, string COMMENTS)
{
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].[TBPURMOQ]
SET
[KINDS]=@KINDS
,[NAMES]=@NAMES
,[MOQS]=@MOQS
,[INDAYS]=@INDAYS
,[COMMENTS]=@COMMENTS
WHERE [ID]=@ID
";
m_db.AddParameter("@ID", ID);
m_db.AddParameter("@KINDS", KINDS);
m_db.AddParameter("@NAMES", NAMES);
m_db.AddParameter("@MOQS", MOQS);
m_db.AddParameter("@INDAYS", INDAYS);
m_db.AddParameter("@COMMENTS", COMMENTS);
m_db.ExecuteNonQuery(cmdTxt);
}
public void SEARCHTBPURMOQ(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],[KINDS],[NAMES],[MOQS],[INDAYS],[COMMENTS]
FROM [TKBUSINESS].[dbo].[TBPURMOQ]
WHERE [ID]=@ID
";
m_db.AddParameter("@ID", ID);
DataTable dt = new DataTable();
dt.Load(m_db.ExecuteReader(cmdTxt));
if (dt.Rows.Count > 0)
{
DropDownList1.SelectedValue = dt.Rows[0]["KINDS"].ToString();
TextBox1.Text = dt.Rows[0]["NAMES"].ToString();
TextBox2.Text = dt.Rows[0]["MOQS"].ToString();
TextBox3.Text = dt.Rows[0]["INDAYS"].ToString();
TextBox4.Text = dt.Rows[0]["COMMENTS"].ToString();
}
}
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 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
// {
// }
//}
}
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 NOT IN ('全部') AND ([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 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();
}
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 BindDropDownList1()
{
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)
{
DropDownList1.DataSource = dt;
DropDownList1.DataTextField = "ISCLOSES";
DropDownList1.DataValueField = "ISCLOSES";
DropDownList1.DataBind();
}
else
{
}
}
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 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 BindGrid(string STATUS)
{
string connectionString = ConfigurationManager.ConnectionStrings["ERPconnectionstring"].ToString();
Ede.Uof.Utility.Data.DatabaseHelper m_db = new Ede.Uof.Utility.Data.DatabaseHelper(connectionString);
this.Session["STATUS"] = STATUS;
string cmdTxt = @" SELECT
[SERNO]
,[STATUS]
,CONVERT(NVARCHAR(10),[SDATES],111) AS SDATES
,[PRODUCTS]
,[CLIENTS]
,[SALES]
,[NUMS]
,CONVERT(NVARCHAR(10),[TESTDATES],111) AS TESTDATES
,[TESTMEMO]
FROM [TKRESEARCH].[dbo].[TBDEVNEW]
WHERE STATUS=@STATUS
ORDER BY [SALES],[CLIENTS],[PRODUCTS]
";
m_db.AddParameter("@STATUS", STATUS);
DataTable dt = new DataTable();
dt.Load(m_db.ExecuteReader(cmdTxt));
Grid1.DataSource = dt;
Grid1.DataBind();
}
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 void SEARCHTBDEVMEMO(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],[SERNO],[STATUS],[KIND],[CLIENT],[PROD],[SPEC],[PLACES],[ONSALES],[OWNER],[FEASIBILITY],[SAMPLETRIAL],[COSTTRIAL],[SENDINSPECTION],[PROOFREADING],[PRODUCTION],[MEMO] FROM [TKRESEARCH].[dbo].[TBDEVMEMO] WHERE [ID]=@ID ";
m_db.AddParameter("@ID", ID);
DataTable dt = new DataTable();
dt.Load(m_db.ExecuteReader(cmdTxt));
if (dt.Rows.Count > 0)
{
DropDownList1.Text = dt.Rows[0]["STATUS"].ToString();
DropDownList2.Text = dt.Rows[0]["KIND"].ToString();
TextBox1.Text = dt.Rows[0]["CLIENT"].ToString();
TextBox2.Text = dt.Rows[0]["PROD"].ToString();
TextBox5.Text = dt.Rows[0]["SPEC"].ToString();
TextBox7.Text = dt.Rows[0]["PLACES"].ToString();
TextBox8.Text = dt.Rows[0]["ONSALES"].ToString();
TextBox13.Text = dt.Rows[0]["OWNER"].ToString();
TextBox14.Text = dt.Rows[0]["MEMO"].ToString();
TextBox3.Text = dt.Rows[0]["FEASIBILITY"].ToString();
TextBox4.Text = dt.Rows[0]["SAMPLETRIAL"].ToString();
TextBox6.Text = dt.Rows[0]["COSTTRIAL"].ToString();
TextBox9.Text = dt.Rows[0]["SENDINSPECTION"].ToString();
TextBox10.Text = dt.Rows[0]["PROOFREADING"].ToString();
TextBox11.Text = dt.Rows[0]["PRODUCTION"].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
{
}
}
public void UPDATETBDEVMEMO(string ID, string STATUS, string KIND, string CLIENT, string PROD, string SPEC, string PLACES, string ONSALES, string OWNER, string MEMO, string FEASIBILITY, string SAMPLETRIAL, string COSTTRIAL, string SENDINSPECTION, string PROOFREADING, string PRODUCTION)
{
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 STATUS=@STATUS,KIND=@KIND,CLIENT=@CLIENT,PROD=@PROD,SPEC=@SPEC,PLACES=@PLACES,ONSALES=@ONSALES,OWNER=@OWNER,MEMO=@MEMO,FEASIBILITY=@FEASIBILITY,SAMPLETRIAL=@SAMPLETRIAL,COSTTRIAL=@COSTTRIAL,SENDINSPECTION=@SENDINSPECTION,PROOFREADING=@PROOFREADING,PRODUCTION=@PRODUCTION WHERE[ID]=@ID
";
m_db.AddParameter("@ID", ID);
m_db.AddParameter("@STATUS", STATUS);
m_db.AddParameter("@KIND", KIND);
m_db.AddParameter("@CLIENT", CLIENT);
m_db.AddParameter("@PROD", PROD);
m_db.AddParameter("@SPEC", SPEC);
m_db.AddParameter("@PLACES", PLACES);
m_db.AddParameter("@ONSALES", ONSALES);
m_db.AddParameter("@OWNER", OWNER);
m_db.AddParameter("@MEMO", MEMO);
m_db.AddParameter("@FEASIBILITY", FEASIBILITY);
m_db.AddParameter("@SAMPLETRIAL", SAMPLETRIAL);
m_db.AddParameter("@COSTTRIAL", COSTTRIAL);
m_db.AddParameter("@SENDINSPECTION", SENDINSPECTION);
m_db.AddParameter("@PROOFREADING", PROOFREADING);
m_db.AddParameter("@PRODUCTION", PRODUCTION);
m_db.ExecuteNonQuery(cmdTxt);
}
private void BindGrid5(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 COUNT(*) AS COUNTS
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
AND [tb_NOTE].[COMPANY_ID]<>'0'
";
m_db.AddParameter("@SDATE", SDATE);
m_db.AddParameter("@EDATE", EDATE);
DataTable dt = new DataTable();
dt.Load(m_db.ExecuteReader(cmdTxt));
Grid5.DataSource = dt;
Grid5.DataBind();
}
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 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 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);
}
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();
}
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();
}
}
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
{
}
}
public void UPDATETBSALESSCHEDULES(string ID, string DEALER, string OWNERS, string EVENTS, string MONTHS1, string MONTHS2, string MONTHS3, string MONTHS4, string MONTHS5, string MONTHS6, string MONTHS7, string MONTHS8, string MONTHS9, string MONTHS10, string MONTHS11, string MONTHS12)
{
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].[TBSALESSCHEDULES]
SET [DEALER]=@DEALER,[OWNERS]=@OWNERS,[EVENTS]=@EVENTS,[MONTHS1]=@MONTHS1,[MONTHS2]=@MONTHS2,[MONTHS3]=@MONTHS3,[MONTHS4]=@MONTHS4,[MONTHS5]=@MONTHS5,[MONTHS6]=@MONTHS6,[MONTHS7]=@MONTHS7,[MONTHS8]=@MONTHS8,[MONTHS9]=@MONTHS9,[MONTHS10]=@MONTHS10,[MONTHS11]=@MONTHS11,[MONTHS12]=@MONTHS12
WHERE [ID]=@ID
";
m_db.AddParameter("@ID", ID);
m_db.AddParameter("@DEALER", DEALER);
m_db.AddParameter("@OWNERS", OWNERS);
m_db.AddParameter("@EVENTS", EVENTS);
m_db.AddParameter("@MONTHS1", MONTHS1);
m_db.AddParameter("@MONTHS2", MONTHS2);
m_db.AddParameter("@MONTHS3", MONTHS3);
m_db.AddParameter("@MONTHS4", MONTHS4);
m_db.AddParameter("@MONTHS5", MONTHS5);
m_db.AddParameter("@MONTHS6", MONTHS6);
m_db.AddParameter("@MONTHS7", MONTHS7);
m_db.AddParameter("@MONTHS8", MONTHS8);
m_db.AddParameter("@MONTHS9", MONTHS9);
m_db.AddParameter("@MONTHS10", MONTHS10);
m_db.AddParameter("@MONTHS11", MONTHS11);
m_db.AddParameter("@MONTHS12", MONTHS12);
m_db.ExecuteNonQuery(cmdTxt);
}
