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); }