public DataTable Cate_List(ref string err_msg, string cate_id = "", string sort = "", string status = "", string title_query = "", string lang_id = "")
{
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(CService.conn_string());
SqlCommand cmd = new SqlCommand();
string[] Array_cate_id;
string[] Array_title_query;
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
cmd.Connection = conn;
Array_cate_id = cate_id.Split(',');
Array_title_query = title_query.Split(',');
csql = "select "
+ " a1.* "
+ "from "
+ "("
+ "select distinct "
+ " b1.ID, b1.CATE_NAME, b1.CATE_DESC "
+ ", b1.SORT, b1.STATUS, b1.LANG_ID, b2.LANG_NAME "
+ ", b1.USR_GRP "
+ "from "
+ " " + cate_dbf_name + " b1 "
+ "LEFT JOIN LANG b2 ON b1.LANG_ID = b2.LANG_ID "
+ "where "
+ " b1.status <> 'D' ";
if (status.Trim().Length > 0)
{
csql = csql + " and b1.status = @status ";
}
if (cate_id.Trim().Length > 0)
{
csql = csql + " and b1.id in (";
for (int i = 0; i < Array_cate_id.Length; i++)
{
if (i > 0)
{
csql = csql + ",";
}
csql = csql + "@str_cate_id" + i.ToString();
}
csql = csql + ") ";
}
if (title_query.Trim().Length > 0)
{
csql = csql + " and (";
for (int i = 0; i < Array_title_query.Length; i++)
{
if (i > 0)
{
csql = csql + " or ";
}
csql = csql + " b1.cate_name like @str_title_query" + i.ToString() + " ";
}
csql = csql + ") ";
}
if (lang_id.Trim().Length > 0)
{
csql = csql + " and b1.lang_id = @lang_id ";
}
csql = csql + ")a1 ";
if (sort.Trim().Length > 0)
{
csql = csql + " order by " + sort + " ";
}
else
{
csql = csql + " order by a1.sort ";
}
cmd.CommandText = csql;
//---------------------------------------------------------------//
cmd.Parameters.Clear();
if (status.Trim().Length > 0)
{
cmd.Parameters.AddWithValue("@status", status);
}
if (cate_id.Trim().Length > 0)
{
for (int i = 0; i < Array_cate_id.Length; i++)
{
cmd.Parameters.AddWithValue("@str_cate_id" + i.ToString(), Array_cate_id[i]);
}
}
if (title_query.Trim().Length > 0)
{
for (int i = 0; i < Array_title_query.Length; i++)
{
cmd.Parameters.AddWithValue("@str_title_query" + i.ToString(), "%" + Array_title_query[i] + "%");
}
}
if (lang_id.Trim().Length > 0)
{
cmd.Parameters.AddWithValue("@lang_id", lang_id);
}
//--------------------------------------------------------------//
if (ds.Tables["cate"] != null)
{
ds.Tables["cate"].Clear();
}
SqlDataAdapter cate_ada = new SqlDataAdapter();
cate_ada.SelectCommand = cmd;
cate_ada.Fill(ds, "cate");
cate_ada = null;
dt = ds.Tables["cate"];
}
catch (Exception ex)
{
err_msg = ex.Message;
CService.msg_write("Error", ex.Message, ex.StackTrace, System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
cmd = null;
conn = null;
}
return(dt);
}
public DataTable List(ref string err_msg, string id = "", string sort = "", string status = "", string title_query = "", string start_date = "", string end_date = "", string is_index = "", string cate_id = "", string lang_id = "")
{
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(CService.conn_string());
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
string[] Array_id;
string[] Array_title_query;
string[] Array_lang_id;
//string[] Array_cate_id;
try
{
Array_id = id.Split(',');
Array_title_query = title_query.Split(',');
Array_lang_id = lang_id.Split(',');
//Array_cate_id = cate_id.Split(',');
csql = "select "
+ " a1.* "
+ "from "
+ "("
+ "select distinct "
+ " a1.id,a1.bd_dt, a1.c_title, a1.c_desc, a1.sort, a1.status "
+ ", a1.lang_id, a2.lang_name "
//+ ", a1.cate_id, a3.cate_name "
+ ", convert(nvarchar(10),a1.c_date,23) as c_date, a1.is_index "
+ ", a4.img_file, isnull(a5.img_count,0) as img_count "
+ "from "
+ " " + dbf_name + " a1 "
+ "LEFT JOIN LANG a2 ON a1.LANG_ID = a2.LANG_ID "
//+ "LEFT JOIN " + cate_dbf_name + " a3 ON a1.cate_id = a3.id "
+ "LEFT JOIN IMG a4 ON Convert(nvarchar(50),a1.id) = a4.img_no and a4.is_index = 'Y' and img_kind = '" + img_kind + "' "
+ "LEFT JOIN ( "
+ " Select "
+ " img_no, count(id) as img_count "
+ " From "
+ " IMG "
+ " Where "
+ " IMG_KIND = '" + img_kind + "' "
+ " Group By "
+ " img_no "
+ ") a5 ON Convert(nvarchar(50),a1.id) = a5.img_no "
+ "where "
+ " a1.status <> 'D' ";
if (status.Trim().Length > 0)
{
csql = csql + " and a1.status = @status ";
}
if (id.Trim().Length > 0)
{
csql = csql + " and a1.id in (";
for (int i = 0; i < Array_id.Length; i++)
{
if (i > 0)
{
csql = csql + ",";
}
csql = csql + "@str_id" + i.ToString();
}
csql = csql + ") ";
}
if (title_query.Trim().Length > 0)
{
csql = csql + " and (";
for (int i = 0; i < Array_title_query.Length; i++)
{
if (i > 0)
{
csql = csql + " or ";
}
csql = csql + " a1.c_title like @str_title_query" + i.ToString() + " ";
}
csql = csql + ") ";
}
if (start_date.Trim().Length > 0)
{
csql = csql + "and a1.c_date >= @start_date ";
}
if (end_date.Trim().Length > 0)
{
csql = csql + "and a1.c_date <= @end_date ";
}
if (is_index.Trim().Length > 0)
{
csql = csql + "and a1.is_index = @is_index ";
}
if (lang_id.Trim().Length > 0)
{
csql = csql + " and a1.lang_id in (";
for (int i = 0; i < Array_lang_id.Length; i++)
{
if (i > 0)
{
csql = csql + ",";
}
csql = csql + "@str_lang_id" + i.ToString();
}
csql = csql + ") ";
}
//if (cate_id.Trim().Length > 0)
//{
// csql = csql + " and a1.cate_id in (";
// for (int i = 0; i < Array_cate_id.Length; i++)
// {
// if (i > 0)
// {
// csql = csql + ",";
// }
// csql = csql + "@str_cate_id" + i.ToString();
// }
// csql = csql + ") ";
//}
csql = csql + ")a1 ";
if (sort.Trim().Length > 0)
{
csql = csql + " order by " + sort + " ";
}
else
{
//csql = csql + " order by a1.cate_id, a1.sort desc ";
csql = csql + " order by a1.sort desc ";
}
//--------------------------------------//
if (IsDebug == "On")
{
string cc_msg = "csql:" + csql;
CService.msg_write("Debug", cc_msg, "", System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
//--------------------------------------//
cmd.CommandText = csql;
//---------------------------------------------------------------//
cmd.Parameters.Clear();
if (status.Trim().Length > 0)
{
cmd.Parameters.AddWithValue("@status", status);
}
if (start_date.Trim().Length > 0)
{
cmd.Parameters.AddWithValue("@start_date", start_date);
}
if (end_date.Trim().Length > 0)
{
cmd.Parameters.AddWithValue("@end_date", end_date);
}
if (id.Trim().Length > 0)
{
for (int i = 0; i < Array_id.Length; i++)
{
cmd.Parameters.AddWithValue("@str_id" + i.ToString(), Array_id[i]);
}
}
if (title_query.Trim().Length > 0)
{
for (int i = 0; i < Array_title_query.Length; i++)
{
cmd.Parameters.AddWithValue("@str_title_query" + i.ToString(), "%" + Array_title_query[i] + "%");
}
}
if (lang_id.Trim().Length > 0)
{
for (int i = 0; i < Array_lang_id.Length; i++)
{
cmd.Parameters.AddWithValue("@str_lang_id" + i.ToString(), Array_lang_id[i]);
}
}
//if (cate_id.Trim().Length > 0)
//{
// for (int i = 0; i < Array_cate_id.Length; i++)
// {
// cmd.Parameters.AddWithValue("@str_cate_id" + i.ToString(), Array_cate_id[i]);
// }
//}
if (is_index.Trim().Length > 0)
{
cmd.Parameters.AddWithValue("@is_index", is_index);
}
//--------------------------------------------------------------//
if (ds.Tables["list"] != null)
{
ds.Tables["list"].Clear();
}
SqlDataAdapter list_ada = new SqlDataAdapter();
list_ada.SelectCommand = cmd;
list_ada.Fill(ds, "list");
list_ada = null;
dt = ds.Tables["list"];
//--------------------------------------//
if (IsDebug == "On")
{
string cc_msg = "dt_Count:" + dt.Rows.Count.ToString();
CService.msg_write("Debug", cc_msg, "", System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
//--------------------------------------//
}
catch (Exception ex)
{
err_msg = ex.Message;
CService.msg_write("Error", ex.Message, ex.StackTrace, System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
cmd = null;
conn = null;
}
return(dt);
}
public DataTable MailTo(string From, string From_Name, string M_To, string M_CC, string subject, string body)
{
string err_msg = "";
string c_msg = "";
string status = "";
string[] c_To;
string[] c_CC;
string str_Host = "";
string str_Account = "";
string str_Pwd = "";
string str_Port = "";
string str_SSL = "";
int int_Port = 0;
DataTable dt = new DataTable();
DataRow dw;
dt.Columns.Add("status", System.Type.GetType("System.String"));
dt.Columns.Add("c_msg", System.Type.GetType("System.String"));
dt.Columns.Add("err_msg", System.Type.GetType("System.String"));
status = "N";
SqlConnection conn = new SqlConnection(CService.conn_string());
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
//抓取Smtp Server 設定
try
{
csql = "SELECT ms_id, _BD_ID, _BD_DT, _UPD_ID, _UPD_DT, Host, Port, Account, Pwd, SSL FROM Mail_Server";
cmd.CommandText = csql;
if (ds.Tables["m_server"] != null)
{
ds.Tables["m_server"].Clear();
}
SqlDataAdapter news_ada = new SqlDataAdapter();
news_ada.SelectCommand = cmd;
news_ada.Fill(ds, "m_server");
news_ada = null;
if (ds.Tables["m_server"].Rows.Count > 0)
{
str_Host = ds.Tables["m_server"].Rows[0]["Host"].ToString();
str_Account = ds.Tables["m_server"].Rows[0]["Account"].ToString();
str_Pwd = ds.Tables["m_server"].Rows[0]["Pwd"].ToString();
str_Port = ds.Tables["m_server"].Rows[0]["Port"].ToString();
str_SSL = ds.Tables["m_server"].Rows[0]["SSL"].ToString();
int_Port = Convert.ToInt32(str_Port);
//=====================================//
From = str_Account;
}
else
{
str_Host = "";
str_Account = "";
str_Pwd = "";
str_Port = "";
str_SSL = "";
int_Port = 0;
}
}
catch (Exception ex)
{
err_msg = ex.Message;
CService.msg_write("Error", ex.Message, ex.StackTrace, System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
cmd = null;
conn = null;
}
try
{
System.Net.Mail.MailMessage em = new System.Net.Mail.MailMessage();
//寄件者
em.From = new System.Net.Mail.MailAddress(From, From_Name, System.Text.Encoding.UTF8);
//收件者
if (M_To.Trim().Length > 0)
{
c_To = M_To.Split(',');
for (int i = 0; i < c_To.Length; i++)
{
//em.To.Add(new System.Net.Mail.MailAddress("收件信箱"));
em.To.Add(new System.Net.Mail.MailAddress(c_To[i]));
}
}
//副本
if (M_CC.Trim().Length > 0)
{
c_CC = M_CC.Split(',');
for (int i = 0; i < c_CC.Length; i++)
{
//em.To.Add(new System.Net.Mail.MailAddress("收件信箱"));
em.CC.Add(new System.Net.Mail.MailAddress(c_CC[i]));
}
}
//em.Bcc.Add(new System.Net.Mail.MailAddress("*****@*****.**"));
//em.Bcc.Add(new System.Net.Mail.MailAddress("*****@*****.**"));
//[email protected]
//主題
em.Subject = subject;
em.SubjectEncoding = System.Text.Encoding.UTF8;
//內容
em.Body = body;
em.BodyEncoding = System.Text.Encoding.UTF8;
em.IsBodyHtml = true; //信件內容是否使用HTML格式
//----------------------------------------------------------------------------//
//Mail Server 設定
System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient();
//=== Google 設定 ===//
//登入帳號認證
//smtp.Credentials = new System.Net.NetworkCredential("帳號", "密碼");
//使用587 Port - google要設定
//smtp.Port = 587;
//smtp.EnableSsl = true; //啟動SSL
//end of google設定
//smtp.Host = "smtp.gmail.com"; //SMTP伺服器
//=================//
//=== 一般設定 ====//
//smtp.Host = "msa.hinet.net"; //SMTP伺服器
//===============//
//=== IsCom (Office365 設定) == //
//====登入帳號認證設定 == //
smtp.UseDefaultCredentials = false;
//smtp.Credentials = new System.Net.NetworkCredential("*****@*****.**", "Suha7367");
smtp.Credentials = new System.Net.NetworkCredential(str_Account, str_Pwd);
//=====================//
//=====Port 設定=====//
//使用587 Port - Office365 要設定
//smtp.Port = 587;
smtp.Port = int_Port;
//====================//
//==== SSL 設定 =======//
//smtp.EnableSsl = true; //啟動SSL
//啟動SSL
if (str_SSL == "Y")
{
smtp.EnableSsl = true;
}
else
{
smtp.EnableSsl = false;
}
//=====================//
//end of Office365設定
//===== Host 設定 ======//
//smtp.Host = "smtp.office365.com"; //SMTP伺服器
smtp.Host = str_Host; //SMTP伺服器
//=====================//
//----------------------------------------------------------------------------//
smtp.Send(em); //寄出
status = "Y";
}
catch (Exception ex)
{
status = "N";
err_msg = ex.Message;
CService.msg_write("Error", ex.Message, ex.StackTrace, System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
finally
{
dw = dt.NewRow();
dw["status"] = status;
dw["c_msg"] = c_msg;
dw["err_msg"] = err_msg;
dt.Rows.Add(dw);
}
return(dt);
}
public DataTable Lang_List(ref string err_msg, string lang_id = "")
{
DataTable dt = new DataTable();
string[] clang_id;
string str_lang_id = "";
SqlConnection conn = new SqlConnection(CService.conn_string());
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
try
{
clang_id = lang_id.Split(',');
for (int i = 0; i < clang_id.Length; i++)
{
if (i > 0)
{
str_lang_id = str_lang_id + ",";
}
str_lang_id = str_lang_id + clang_id[i];
}
csql = "select "
+ " * "
+ "from "
+ " lang "
+ "where "
+ " status = 'Y' ";
if (lang_id.Trim().Length > 0)
{
csql = csql + " and lang_id in (";
for (int i = 0; i < clang_id.Length; i++)
{
if (i > 0)
{
csql = csql + ",";
}
csql = csql + "'@str_lang_id" + i.ToString() + "'";
}
csql = csql + ") ";
}
csql = csql + "order by sort ";
cmd.CommandText = csql;
if (lang_id.Trim().Length > 0)
{
cmd.Parameters.Clear();
for (int i = 0; i < lang_id.Length; i++)
{
cmd.Parameters.AddWithValue("@str_lang_id" + i.ToString(), lang_id[i]);
}
}
if (ds.Tables["lang"] != null)
{
ds.Tables["lang"].Clear();
}
SqlDataAdapter lang_ada = new SqlDataAdapter();
lang_ada.SelectCommand = cmd;
lang_ada.Fill(ds, "lang");
lang_ada = null;
dt = ds.Tables["lang"];
}
catch (Exception ex)
{
err_msg = ex.Message;
CService.msg_write("Error", ex.Message, ex.StackTrace, System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
cmd = null;
conn = null;
}
return(dt);
}