static void sftp_gen() { //var file_m = DateTime.Now.AddDays(-1).ToString("yyyyMMddHHmmss"); var file_m = DateTime.Now.ToString("yyyyMMddHHmmss"); var filename = String.Format(@"E:\ftproot\ftpuser\www\ssh\OpenSSH\data\NPPRSI001_{0}.DAT", file_m); using (var dc = new DataClasses_afwnltDataContext()) { var arrResults = dc.ExecuteQuery <vw_RetailSalesJP>("select distinct * FROM vw_RetailSalesJP where CONVERT(varchar, ClosingDate,112) = '" + file_m.Left(8) + "' or CONVERT(varchar, AFWMakeDate,112)='" + file_m.Left(8) + "'").ToList(); if (arrResults.Count > 0) { Console.WriteLine("Data :" + arrResults.Count.ToString()); WriteToFile("Data :" + arrResults.Count.ToString()); using (StreamWriter sw = new StreamWriter(filename)) { Console.WriteLine("Create File..."); WriteToFile("Create File..."); foreach (var s in arrResults) { var _TITLE = (!string.IsNullOrEmpty(s.TITLE)) ? s.TITLE.ToString() : "-"; var _FIRST_NAME = (!string.IsNullOrEmpty(s.FIRST_NAME)) ? s.FIRST_NAME.ToString() : "-"; var _LAST_NAME = (!string.IsNullOrEmpty(s.LAST_NAME)) ? s.LAST_NAME.ToString() : "-"; var _CUSTOMER_TYPE = (!string.IsNullOrEmpty(s.CUSTOMER_TYPE)) ? s.CUSTOMER_TYPE.ToString() : "-"; var _ID_CARD = (!string.IsNullOrEmpty(s.ID_CARD)) ? s.ID_CARD.ToString() : "-"; var _TAX_ID_NO = (!string.IsNullOrEmpty(s.TAX_ID_NO)) ? s.TAX_ID_NO.ToString() : "-"; var _MOBILE_NO = (!string.IsNullOrEmpty(s.MOBILE_NO)) ? s.MOBILE_NO.ToString() : "-"; var _HOME_NO = (!string.IsNullOrEmpty(s.HOME_NO)) ? s.HOME_NO.ToString() : "-"; var _OFFICE_NO = (!string.IsNullOrEmpty(s.OFFICE_NO)) ? s.OFFICE_NO.ToString() : "-"; var _EMAIL = (!string.IsNullOrEmpty(s.Email)) ? s.Email.ToString() : "-"; var _CONTACT_PERSON = (!string.IsNullOrEmpty(s.CONTACT_PERSON)) ? s.CONTACT_PERSON.ToString() : "-"; var _CONTACT_ADDRESS = (!string.IsNullOrEmpty(s.CONTACT_ADDRESS)) ? s.CONTACT_ADDRESS.ToString() : "-"; var _POSTAL_ADDRESS = (!string.IsNullOrEmpty(s.POSTAL_ADDRESS)) ? s.POSTAL_ADDRESS.ToString() : "-"; var _VIN = (!string.IsNullOrEmpty(s.VIN)) ? s.VIN.ToString() : "-"; var _CLIENT_CODE = (!string.IsNullOrEmpty(s.CLIENT_CODE)) ? s.CLIENT_CODE.ToString() : "-"; //var _CLOSING_DATE = s.ClosingDate.Value.ToString("yyyyMMdd"); var _CLOSING_DATE = (!string.IsNullOrEmpty(s.CLOSING_DATE)) ? s.CLOSING_DATE.ToString() : "-"; //1 TITLE VARCHAR2 15 //2 FIRST_NAME VARCHAR2 100 //3 LAST_NAME VARCHAR2 100 กรณีเป็นบริษัท ให้ระบุเป็น - //4 CUSTOMER_TYPE VARCHAR2 1 1=นิติบุคคล, 2=บริษัท, 3=หน่วยงานภาครัฐ //5 ID_CARD VARCHAR2 20 รวม Passport No. //6 TAX_ID_NO VARCHAR2 20 //7 MOBILE_NO VARCHAR2 10 //8 HOME_NO VARCHAR2 20 //9 OFFICE_NO VARCHAR2 20 //10 EMAIL VARCHAR2 40 //11 CONTACT_PERSON VARCHAR2 100 กรณีเป็นหน่วยงานภาครัฐหรือบริษัท //12 CONTACT_ADDRESS VARCHAR2 255 ที่อยู่ทั้งหมด //13 POSTAL_ADDRESS VARCHAR2 255 //14 VIN VARCHAR2 30 Chassis No. //15 CLIENT_CODE VARCHAR2 100 //16 CLOSING_DATE VARCHAR2 100 วันที่ส่งประกัน StringBuilder sb = new StringBuilder(); sb.Append(_TITLE.Trim().Left(15)); sb.Append("|" + _FIRST_NAME.Replace(Environment.NewLine, "").Trim().Left(100)); sb.Append("|" + _LAST_NAME.Replace(Environment.NewLine, "").Trim().Left(100)); sb.Append("|" + _CUSTOMER_TYPE.Replace(Environment.NewLine, "").Trim().Left(1)); sb.Append("|" + _ID_CARD.Replace(Environment.NewLine, "").Trim().Left(20)); sb.Append("|" + _TAX_ID_NO.Replace(Environment.NewLine, "").Trim().Left(20)); sb.Append("|" + _MOBILE_NO.Replace(Environment.NewLine, "").Trim().Left(10)); sb.Append("|" + _HOME_NO.Replace(Environment.NewLine, "").Trim().Left(20)); sb.Append("|" + _OFFICE_NO.Replace(Environment.NewLine, "").Trim().Left(20)); sb.Append("|" + _EMAIL.Replace(Environment.NewLine, "").Trim().Left(40)); sb.Append("|" + _CONTACT_PERSON.Replace(Environment.NewLine, "").Trim().Left(100)); sb.Append("|" + _CONTACT_ADDRESS.Replace(Environment.NewLine, "").Trim().Left(255)); sb.Append("|" + _POSTAL_ADDRESS.Replace(Environment.NewLine, "").Trim().Left(255)); sb.Append("|" + _VIN.Replace(Environment.NewLine, "").Trim().Left(30)); sb.Append("|" + _CLIENT_CODE.Replace(Environment.NewLine, "").Trim().Left(100)); sb.Append("|" + _CLOSING_DATE.Replace(Environment.NewLine, "").Trim().Left(100)); sw.WriteLine(sb.ToString()); } } //=============================== #Console1 ================================================ Process p = Process.Start(@"E:\ftproot\ftpuser\www\ssh\OpenSSH\ssh.exe", @"-L 60001:sfxsv901.jp.nissan.biz:22 -i E:\ftproot\ftpuser\www\ssh\id_rsa_20160203 [email protected]"); Console.WriteLine("Connecting Gateway..."); WriteToFile("Connecting Gateway..."); System.Threading.Thread.Sleep(10000); //=============================== #Console2 ================================================ var keyFile = new PrivateKeyFile(@"E:\ftproot\ftpuser\www\ssh\id_rsa_20160203"); var keyFiles = new[] { keyFile }; var methods_client = new List <AuthenticationMethod>(); methods_client.Add(new PrivateKeyAuthenticationMethod("NMTCRMFTP04", keyFiles)); var con_client = new ConnectionInfo("localhost", 60001, "NMTCRMFTP04", methods_client.ToArray()); using (var client = new SftpClient(con_client)) { try { client.Connect(); if (client.IsConnected) { Console.WriteLine("Client Connected"); WriteToFile("Client Connected"); System.Threading.Thread.Sleep(1000); Console.WriteLine("Upload File..."); WriteToFile("Upload File..."); System.Threading.Thread.Sleep(1000); FileInfo f = new FileInfo(filename); string uploadfile = f.FullName; using (FileStream fileStream = new FileStream(uploadfile, FileMode.Open)) { if (fileStream != null) { client.BufferSize = 4 * 1024; client.UploadFile(fileStream, "/export/ftpdata1/ESB01/nmtcrmftp04/" + f.Name, null); } } System.Threading.Thread.Sleep(5000); //=============== Send Mail ========== Console.WriteLine("Send Mail..."); WriteToFile("Send Mail..."); System.Threading.Thread.Sleep(1000); try { string smtp = System.Configuration.ConfigurationManager.AppSettings["smtp"]; string mail_from = System.Configuration.ConfigurationManager.AppSettings["mailfrom"]; string mail_to = System.Configuration.ConfigurationManager.AppSettings["mailto"]; string mail_cc = System.Configuration.ConfigurationManager.AppSettings["mailcc"]; string mail_bcc = System.Configuration.ConfigurationManager.AppSettings["mailbcc"]; MailMessage mail = new MailMessage(); //SmtpClient SmtpServer = new SmtpClient("172.16.40.244");//172.16.40.135 //SmtpClient SmtpServer = new SmtpClient("172.16.40.135"); SmtpClient SmtpServer = new SmtpClient(smtp); mail.IsBodyHtml = true; mail.From = new MailAddress(mail_from); //mail.To.Add(mail_to); //mail.CC.Add(mail_cc); //mail.Bcc.Add(mail_bcc); string[] _MailTo = mail_to.Split(';'); foreach (var item in _MailTo) { if (!string.IsNullOrEmpty(item.Trim())) { mail.To.Add(item); } } string[] _MailCc = mail_cc.Split(';'); foreach (var item in _MailCc) { if (!string.IsNullOrEmpty(item.Trim())) { mail.CC.Add(item); } } string[] _MailBcc = mail_bcc.Split(';'); foreach (var item in _MailBcc) { if (!string.IsNullOrEmpty(item.Trim())) { mail.Bcc.Add(item); } } mail.Subject = String.Format("Request NPP data as of {0} (Daily)", DateTime.Now.ToString("dd MMM yyyy")); StringBuilder sb = new StringBuilder(); sb.Append("Dear K.Pojdej,<br><br>"); sb.AppendFormat("As your requested, please find attached files the NPP data for NMT as {0} for your consideration.<br><br>", DateTime.Now.ToString("dd MMM yyyy")); sb.Append("Best regards,<br><br>"); sb.Append("<b><span lang=EN-NZ style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:black;mso-ansi-language:EN-NZ'>Dusit Prasertsilp</span></b><br>"); sb.Append("<b><span lang=EN-NZ style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:black;mso-ansi-language:EN-NZ'>Division Manager</span></b><br>"); sb.Append("<b><span lang=EN-NZ style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:black;mso-ansi-language:EN-NZ'>Information Technology</span></b><br>"); sb.Append("<b><span lang=EN-NZ style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:black;mso-ansi-language:EN-NZ'>Lockton Wattana Insurance Brokers (Thailand) Ltd.</span></b><br>"); sb.Append("<span style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:#999999'>4th Floor, United Center Building,</span><br>"); sb.Append("<span style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:#999999'>323 Silom Road, Khet Bangrak, Bangkok 10500</span><br>"); sb.Append("<span style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:#999999'>Tel: +66 (0) 2 353 7000 (Ext. 2911)</span><br>"); sb.Append("<span style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:#999999'>Fax: +66 (0)2 353 7001-2</span><br>"); sb.Append("<span style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:#999999'>Mobile: +66 (8) 4 752 0536</span><br>"); sb.Append("<span style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:#999999'>E-mail: [email protected]</span><br>"); sb.Append("<span style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:#999999'>Website: www.locktonwattana.co.th</span><br>"); sb.Append("<span style='font-size:10.0pt;font-family:\"Tahoma\",\"sans-serif\";color:#999999'>Company Registration No.: 0105527011324</span><br>"); //mail.Body = sb.ToString(); //'=========================================================== var bodyHTML = "<html><body>" + sb.ToString() + "<br><img src='cid:LOGO_IMAGE1' alt='Logo' /><br><span style='font-size:16.0pt;font-family:Angsana New,serif;color:green'><img src='cid:LOGO_IMAGE2' alt='Logo' /><i>Please consider the environment before printing this e-mail.</i></span></body></html>"; AlternateView alternateView = AlternateView.CreateAlternateViewFromString(bodyHTML, null, "text/html"); var path_to_the_image_file1 = String.Format(@"E:\ftproot\ftpuser\www\ssh\OpenSSH\logo\{0}", "image02.jpg"); var path_to_the_image_file2 = String.Format(@"E:\ftproot\ftpuser\www\ssh\OpenSSH\logo\{0}", "image002.jpg"); //Create the LinkedResource here LinkedResource logo1 = new LinkedResource(path_to_the_image_file1, "image/jpeg");// 'Content Type is set as image/jpeg logo1.ContentId = "LOGO_IMAGE1"; logo1.TransferEncoding = System.Net.Mime.TransferEncoding.Base64; alternateView.LinkedResources.Add(logo1); LinkedResource logo2 = new LinkedResource(path_to_the_image_file2, "image/jpeg"); //'Content Type is set as image/jpeg logo2.ContentId = "LOGO_IMAGE2"; logo2.TransferEncoding = System.Net.Mime.TransferEncoding.Base64; alternateView.LinkedResources.Add(logo2); mail.AlternateViews.Add(alternateView); //'=========================================================== System.Net.Mail.Attachment attachment; attachment = new System.Net.Mail.Attachment(filename); mail.Attachments.Add(attachment); //SmtpServer.Port = 587; //SmtpServer.Credentials = new System.Net.NetworkCredential("your [email protected]", "your password"); //SmtpServer.EnableSsl = true; SmtpServer.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials; SmtpServer.Send(mail); } catch (Exception ex) { Console.WriteLine(ex.Message); WriteToFile(ex.Message); } } } catch (Exception ex) { Console.WriteLine(ex.Message); WriteToFile(ex.Message); } finally { Console.WriteLine("Client Exit."); WriteToFile("Client Exit."); client.Disconnect(); client.Dispose(); } } p.CloseMainWindow(); System.Threading.Thread.Sleep(5000); } } }
static void genfile() { var file_m = DateTime.Now.ToString("yyyyMMddHHmmss"); var filename = String.Format(@"c:\temp\NPPRSI001_{0}.DAT", file_m); using (var dc = new DataClasses_afwnltDataContext()) { //var arrResults = dc.ExecuteQuery<vw_RetailSalesJP>("select distinct * FROM vw_RetailSalesJP where CONVERT(varchar, ClosingDate,112) between '20171001' and '20170430'").ToList(); var arrResults = dc.ExecuteQuery <vw_RetailSalesJP>("select distinct * FROM vw_RetailSalesJP where (CONVERT(varchar, ClosingDate,112) >= '20171001' or CONVERT(varchar, AFWMakeDate,112) >= '20171001') and MOBILE_NO <> '0000000000'").ToList(); //select distinct * FROM vw_RetailSalesJP where CONVERT(varchar, ClosingDate,112) >= '20170701' and MOBILE_NO <> '0000000000' Console.WriteLine("Data :" + arrResults.Count.ToString()); //WriteToFile("Data :" + arrResults.Count.ToString()); using (StreamWriter sw = new StreamWriter(filename)) { Console.WriteLine("Create File..."); //WriteToFile("Create File..."); foreach (var s in arrResults) { var _TITLE = (!string.IsNullOrEmpty(s.TITLE)) ? s.TITLE.ToString() : "-"; var _FIRST_NAME = (!string.IsNullOrEmpty(s.FIRST_NAME)) ? s.FIRST_NAME.ToString() : "-"; var _LAST_NAME = (!string.IsNullOrEmpty(s.LAST_NAME)) ? s.LAST_NAME.ToString() : "-"; var _CUSTOMER_TYPE = (!string.IsNullOrEmpty(s.CUSTOMER_TYPE)) ? s.CUSTOMER_TYPE.ToString() : "-"; var _ID_CARD = (!string.IsNullOrEmpty(s.ID_CARD)) ? s.ID_CARD.ToString() : "-"; var _TAX_ID_NO = (!string.IsNullOrEmpty(s.TAX_ID_NO)) ? s.TAX_ID_NO.ToString() : "-"; var _MOBILE_NO = (!string.IsNullOrEmpty(s.MOBILE_NO)) ? s.MOBILE_NO.ToString() : "-"; var _HOME_NO = (!string.IsNullOrEmpty(s.HOME_NO)) ? s.HOME_NO.ToString() : "-"; var _OFFICE_NO = (!string.IsNullOrEmpty(s.OFFICE_NO)) ? s.OFFICE_NO.ToString() : "-"; var _EMAIL = (!string.IsNullOrEmpty(s.Email)) ? s.Email.ToString() : "-"; var _CONTACT_PERSON = (!string.IsNullOrEmpty(s.CONTACT_PERSON)) ? s.CONTACT_PERSON.ToString() : "-"; var _CONTACT_ADDRESS = (!string.IsNullOrEmpty(s.CONTACT_ADDRESS)) ? s.CONTACT_ADDRESS.ToString() : "-"; var _POSTAL_ADDRESS = (!string.IsNullOrEmpty(s.POSTAL_ADDRESS)) ? s.POSTAL_ADDRESS.ToString() : "-"; var _VIN = (!string.IsNullOrEmpty(s.VIN)) ? s.VIN.ToString() : "-"; var _CLIENT_CODE = (!string.IsNullOrEmpty(s.CLIENT_CODE)) ? s.CLIENT_CODE.ToString() : "-"; //var _CLOSING_DATE = s.ClosingDate.Value.ToString("yyyyMMdd"); var _CLOSING_DATE = (!string.IsNullOrEmpty(s.CLOSING_DATE)) ? s.CLOSING_DATE.ToString() : "-"; //1 TITLE VARCHAR2 15 //2 FIRST_NAME VARCHAR2 100 //3 LAST_NAME VARCHAR2 100 กรณีเป็นบริษัท ให้ระบุเป็น - //4 CUSTOMER_TYPE VARCHAR2 1 1=นิติบุคคล, 2=บริษัท, 3=หน่วยงานภาครัฐ //5 ID_CARD VARCHAR2 20 รวม Passport No. //6 TAX_ID_NO VARCHAR2 20 //7 MOBILE_NO VARCHAR2 10 //8 HOME_NO VARCHAR2 20 //9 OFFICE_NO VARCHAR2 20 //10 EMAIL VARCHAR2 40 //11 CONTACT_PERSON VARCHAR2 100 กรณีเป็นหน่วยงานภาครัฐหรือบริษัท //12 CONTACT_ADDRESS VARCHAR2 255 ที่อยู่ทั้งหมด //13 POSTAL_ADDRESS VARCHAR2 255 //14 VIN VARCHAR2 30 Chassis No. //15 CLIENT_CODE VARCHAR2 100 //16 CLOSING_DATE VARCHAR2 100 วันที่ส่งประกัน StringBuilder sb = new StringBuilder(); sb.Append(_TITLE.Trim().Left(15)); sb.Append("|" + _FIRST_NAME.Replace(Environment.NewLine, "").Trim().Left(100)); sb.Append("|" + _LAST_NAME.Replace(Environment.NewLine, "").Trim().Left(100)); sb.Append("|" + _CUSTOMER_TYPE.Replace(Environment.NewLine, "").Trim().Left(1)); sb.Append("|" + _ID_CARD.Replace(Environment.NewLine, "").Trim().Left(20)); sb.Append("|" + _TAX_ID_NO.Replace(Environment.NewLine, "").Trim().Left(20)); sb.Append("|" + _MOBILE_NO.Replace(Environment.NewLine, "").Trim().Left(10)); sb.Append("|" + _HOME_NO.Replace(Environment.NewLine, "").Trim().Left(20)); sb.Append("|" + _OFFICE_NO.Replace(Environment.NewLine, "").Trim().Left(20)); sb.Append("|" + _EMAIL.Replace(Environment.NewLine, "").Trim().Left(40)); sb.Append("|" + _CONTACT_PERSON.Replace(Environment.NewLine, "").Trim().Left(100)); sb.Append("|" + _CONTACT_ADDRESS.Replace(Environment.NewLine, "").Trim().Left(255)); sb.Append("|" + _POSTAL_ADDRESS.Replace(Environment.NewLine, "").Trim().Left(255)); sb.Append("|" + _VIN.Replace(Environment.NewLine, "").Trim().Left(30)); sb.Append("|" + _CLIENT_CODE.Replace(Environment.NewLine, "").Trim().Left(100)); sb.Append("|" + _CLOSING_DATE.Replace(Environment.NewLine, "").Trim().Left(100)); sw.WriteLine(sb.ToString()); } } } Console.WriteLine("End.."); }