星期二, 7月 14, 2015

DHL Shipment Validation by C#

公司要導入跟物流的自動交互,如此就可以自動從 ERP 將資料傳到物流的系統,順便直接產生出提單 Label 直接可以貼在箱上。

DHL 提供的範例為 Java 的,只好問 Google 看看大家的經驗了,

經過好幾天的看文件,找範例,問廠商,總算可以順利的印出 Label 了。

string requestText//導入 XML 資料
WebRequest requestRate = HttpWebRequest.Create("https://xmlpitest-ea.dhl.com/XMLShippingServlet");//DHL 的測試環境
requestRate.ContentType = "application/x-www-form-urlencoded";
requestRate.Method = "POST";

using (var stream = requestRate.GetRequestStream())
{
var arrBytes = System.Text.ASCIIEncoding.ASCII.GetBytes(requestText);
stream.Write(arrBytes, 0, arrBytes.Length);

stream.Close();

}



WebResponse responseRate = requestRate.GetResponse();

var respStream = responseRate.GetResponseStream();

var reader = new StreamReader(respStream, System.Text.Encoding.ASCII);

string strResponse = reader.ReadToEnd();

respStream.Close();



XmlDocument xdoc = new XmlDocument();

xdoc.LoadXml(strResponse);



            XmlNodeList list = xdoc.GetElementsByTagName("OutputImage");



if (list.Count > 0)

{

//EPL2 直接傳給印表機進行列印

byte[] bytes = Convert.FromBase64String(list[0].InnerText.ToString());

IntPtr pUnmanagedBytes = new IntPtr(0);

int nLength = bytes.Length;

pUnmanagedBytes = Marshal.AllocCoTaskMem(nLength);

Marshal.Copy(bytes, 0, pUnmanagedBytes, nLength);

RawPrinterHelper.SendBytesToPrinter("DHL_Label", pUnmanagedBytes, nLength);

Marshal.FreeCoTaskMem(pUnmanagedBytes);

}

這邊會用到一個 Microsoft 網站只有找到 VB.Net 的,但網路上有人已經改成 C# 的 Class

public class RawPrinterHelper
  {
    // Structure and API declarions:
    [StructLayout(LayoutKind.Sequential, CharSet = CharSet.Ansi)]
    public class DOCINFOA
    {
      [MarshalAs(UnmanagedType.LPStr)]
      public string pDocName;
      [MarshalAs(UnmanagedType.LPStr)]
      public string pOutputFile;
      [MarshalAs(UnmanagedType.LPStr)]
      public string pDataType;
    }
    [DllImport("winspool.Drv", EntryPoint = "OpenPrinterA", SetLastError = true, CharSet = CharSet.Ansi, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
    public static extern bool OpenPrinter([MarshalAs(UnmanagedType.LPStr)] string szPrinter, out IntPtr hPrinter, IntPtr pd);

    [DllImport("winspool.Drv", EntryPoint = "ClosePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
    public static extern bool ClosePrinter(IntPtr hPrinter);

    [DllImport("winspool.Drv", EntryPoint = "StartDocPrinterA", SetLastError = true, CharSet = CharSet.Ansi, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
    public static extern bool StartDocPrinter(IntPtr hPrinter, Int32 level, [In, MarshalAs(UnmanagedType.LPStruct)] DOCINFOA di);

    [DllImport("winspool.Drv", EntryPoint = "EndDocPrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
    public static extern bool EndDocPrinter(IntPtr hPrinter);

    [DllImport("winspool.Drv", EntryPoint = "StartPagePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
    public static extern bool StartPagePrinter(IntPtr hPrinter);

    [DllImport("winspool.Drv", EntryPoint = "EndPagePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
    public static extern bool EndPagePrinter(IntPtr hPrinter);

    [DllImport("winspool.Drv", EntryPoint = "WritePrinter", SetLastError = true, ExactSpelling = true, CallingConvention = CallingConvention.StdCall)]
    public static extern bool WritePrinter(IntPtr hPrinter, IntPtr pBytes, Int32 dwCount, out Int32 dwWritten);

    // SendBytesToPrinter()
    // When the function is given a printer name and an unmanaged array
    // of bytes, the function sends those bytes to the print queue.
    // Returns true on success, false on failure.
    public static bool SendBytesToPrinter(string szPrinterName, IntPtr pBytes, Int32 dwCount)
    {
      Int32 dwError = 0, dwWritten = 0;
      IntPtr hPrinter = new IntPtr(0);
      DOCINFOA di = new DOCINFOA();
      bool bSuccess = false; // Assume failure unless you specifically succeed.

      di.pDocName = "My C#.NET RAW Document";
      di.pDataType = "RAW";

      // Open the printer.
      if (OpenPrinter(szPrinterName.Normalize(), out hPrinter, IntPtr.Zero))
      {
        // Start a document.
        if (StartDocPrinter(hPrinter, 1, di))
        {
          // Start a page.
          if (StartPagePrinter(hPrinter))
          {
            // Write your bytes.
            bSuccess = WritePrinter(hPrinter, pBytes, dwCount, out dwWritten);
            EndPagePrinter(hPrinter);
          }
          EndDocPrinter(hPrinter);
        }
        ClosePrinter(hPrinter);
      }
      // If you did not succeed, GetLastError may give more information
      // about why not.
      if (bSuccess == false)
      {
        dwError = Marshal.GetLastWin32Error();
      }
      return bSuccess;
    }
    public static bool SendMemoryToPrinter(string szPrinterName, MemoryStream ms)
    {
      BinaryReader br = new BinaryReader(ms);
      Byte[] bytes = new Byte[ms.Length];
      bool bSuccess = false;
      IntPtr pUnmanagedBytes = new IntPtr(0);
      int nLength;

      nLength = Convert.ToInt32(ms.Length);
      bytes = br.ReadBytes(nLength);
      pUnmanagedBytes = Marshal.AllocCoTaskMem(nLength);
      Marshal.Copy(bytes, 0, pUnmanagedBytes, nLength);
      bSuccess = SendBytesToPrinter(szPrinterName, pUnmanagedBytes, nLength);
      Marshal.FreeCoTaskMem(pUnmanagedBytes);
      return bSuccess;
    }
    public static bool SendFileToPrinter(string szPrinterName, string szFileName)
    {
      // Open the file.
      FileStream fs = new FileStream(szFileName, FileMode.Open);
      // Create a BinaryReader on the file.
      BinaryReader br = new BinaryReader(fs);
      // Dim an array of bytes big enough to hold the file's contents.
      Byte[] bytes = new Byte[fs.Length];
      bool bSuccess = false;
      // Your unmanaged pointer.
      IntPtr pUnmanagedBytes = new IntPtr(0);
      int nLength;

      nLength = Convert.ToInt32(fs.Length);
      // Read the contents of the file into the array.
      bytes = br.ReadBytes(nLength);
      // Allocate some unmanaged memory for those bytes.
      pUnmanagedBytes = Marshal.AllocCoTaskMem(nLength);
      // Copy the managed byte array into the unmanaged array.
      Marshal.Copy(bytes, 0, pUnmanagedBytes, nLength);
      // Send the unmanaged bytes to the printer.
      bSuccess = SendBytesToPrinter(szPrinterName, pUnmanagedBytes, nLength);
      // Free the unmanaged memory that you allocated earlier.
      Marshal.FreeCoTaskMem(pUnmanagedBytes);
      return bSuccess;
    }
    public static bool SendStringToPrinter(string szPrinterName, string szString)
    {
      IntPtr pBytes;
      Int32 dwCount;
      // How many characters are in the string?
      dwCount = szString.Length;
      // Assume that the printer is expecting ANSI text, and then convert
      // the string to ANSI text.
      pBytes = Marshal.StringToCoTaskMemAnsi(szString);
      // Send the converted ANSI string to the printer.
      SendBytesToPrinter(szPrinterName, pBytes, dwCount);
      Marshal.FreeCoTaskMem(pBytes);
      return true;
    }
  }

星期三, 1月 07, 2015

這次訂票的經驗告訴我

訂票系統真的很複雜,很難寫,

光是一個網站的編碼都無法整合了

難怪我會看到亂碼~~~~機車哩,浪費我很多時間,只搶到給我媽跟阿姨們的,自己只能等加場了。

一直重新整理,一天三千次以上也不厭倦呀~~~~


星期四, 12月 25, 2014

小米手環入手囉

等了好久,總算等到台灣開賣了,當然要趁聖誕節買一個來玩玩看囉!!






















戴在手上其實很輕,沒有什麼感覺,可能要小心的就是不小心擠壓到彈飛出去吧XD

星期五, 12月 05, 2014

SQL Server 刪除特定連線

最近發現以前別人寫的 Web Service 在資料庫連線控制沒有寫好,連線用完了也不關閉,造成IIS 的 Application Pool 連線數過多,

因為是特定的主機,當然只能 Kill 特定的 spid

只好先抓出那台主機所有 spid 來進行刪除的動作


DECLARE @spid int, @SQLstr nvarchar(128)
DECLARE spids_cr CURSOR FOR
SELECT p.spid FROM sys.sysprocesses P JOIN sys.sysdatabases D ON (D.dbid = P.dbid) JOIN sys.sysusers U ON (P.uid = U.uid) WHERE hostname != '' AND hostname = @hostname AND [P].[program_name]='.Net SqlClient Data Provider                                                                                                    ' 
FOR READ ONLY

OPEN spids_cr

FETCH spids_cr INTO @spid

WHILE (@@FETCH_STATUS=0)
BEGIN
SET @SQLstr = 'KILL ' + CAST(@spid AS varchar)
EXEC sp_executesql @SQLstr
FETCH spids_cr INTO @spid
END

CLOSE spids_cr
DEALLOCATE spids_cr
參考:http://www.stev.org/post/2011/03/01/MS-SQL-Kill-connections-by-host.aspx http://sharedderrick.blogspot.tw/2008/01/kill-all-connection.html

星期三, 8月 06, 2014

效能監控與發送警示郵件

有一天公司的 SQL Server 記憶體竟然使用到 64G,整個服務根本就是掛掉了,

想要查詢正在執行什麼語句都沒辦法,只好把服務重啟了,

正常來講,我們的記憶體使用量連 32G 都不到的,為了預防這種情況發生,

只好開始找尋監控的方式,

這裡寫到
http://stackoverflow.com/questions/6189927/sql-memory-viewing-actual-memory-used-and-memory-available

可以直接抓出 SQL Server 使用的記憶體大小

SELECT object_name, counter_name, cntr_value AS 'Total Server Memory (KB)'
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Total Server Memory (KB)'
不過看起來好像有點不準,於是繼續尋找其他方法,

政廷提供了這個網址,使用效能監視器
http://www.dotblogs.com.tw/jamesfu/archive/2012/12/07/perfmon.aspx

搭配他我找到了這個效能監視器加上工作排程器
http://www.dotblogs.com.tw/kirkchen/archive/2012/11/24/use-performance-monitor-to-monitor-server-and-send-alert-email.aspx

所以最後使用就使用效能監視器紀錄使用狀況,當超過設定值的時候就發送郵件通知

星期一, 7月 28, 2014

換馬桶進水器

昨天一早就被老媽叫醒,說廁所的馬桶水箱在漏水,

我第一件事情就是先關掉馬桶的進水管, 打開水箱觀察一下是什麼問題,

馬上就發現原因了,原來是浮水球桿子斷了,本來想用膠帶黏一下好了,

沒想到根本不夠力,只好換掉了,

去特力屋買了進水器跟浮球組回家來換,其實構造很簡單,只是馬桶下方不好使力而已,

大約 20 分鐘就換好了。


看看說明,是不是很簡單呀,上次換這個請水電來,花了一千二呢。



星期五, 7月 18, 2014

Team Foundation Server 還原別人的簽出

使用 TFS 最怕遇到的就是程式被別人簽出了,而簽出的人又不在或是離職了,系統設定為只能單一簽出的時候就很麻煩了,最簡單的方式就是還原該使用者的簽出,

1. 當然要有 VS 2010 或是 VS 2012

2. 安裝 Microsoft Visual Studio Team Foudation Server 2010(2012) Power Tools

3. 在原始檔控制總管中隨便找一個檔案或文件夾按右鍵點選 "尋找 " ->"Find Status"

4. 進行 ckecked out 檔案的搜尋

5. 在找出的檔案按右鍵 "Undo",就可以還原別人的簽出了。


星期三, 7月 16, 2014

Windows Server 2012 使用 ASP.NET

Windows Server 2012 真的很討厭,很多介面或設定都跟以前不一樣了,

我只是想測試一下 IIS 8.0+ASP.Net 有沒有使用上的問題,就花了不少時間,

首先,安裝 .Net Framework 3.5 只能從添加角色和功能進行安裝就算了, 

還要修改光碟的路徑才行,不然就會連到 Windows Update 安裝,耗時費工, 

還好後來在網路上還是找到方法了,

 

指定備用源路徑:設定為 光碟槽:\SOURCES\Sxs\,就可以了

本以為這樣就結束了,

結果 IIS 架好,系統也都 Copy 過去後,打開網頁出現 Error,一看錯誤,

原來是 IIS 不認識 aspx,這個簡單,我知道,重新註冊就行了,於是我打開了 cmd,

切換目錄後輸入 aspnet_regiis -i ,沒想到出現了 Windows Server 2012 不支援這種方式,
















請到角色與功能中移除重新安裝,天曉得,移除後 Windows 就掛掉了,更別提安裝了,

只好重灌,以及上網找資料了,才知道,不只是 ASP.NET 跟 IIS 要安裝,

還有其他角色要安裝才能用,

















如此一來,就一切正常了。


參考:http://www.dotblogs.com.tw/regionbbs/archive/2012/09/13/installing.net.framework.3.5.on.windows.server.2012.aspx
http://blog.darkthread.net/post-2013-11-29-iis8-asp-net-setup.aspx


星期三, 7月 02, 2014

AlwaysOn 設定相同 SQL 帳號

SQL Server 的 AlwaysOn 在 MSDN 跟上課聽到的內容都是使用網域帳號在登入及演練,但是這並不實際,因為根本不可能讓程式也用網域帳號呀。

只好想辦法在兩邊都建立一樣的 SQL 帳號,測試結果不行,移轉的後帳號還是不能用,後來才發現,原來建立算是不同的帳號,要想辦法建立具有相同 SID 和密碼的 SQL 驗證登入帳戶才行。

現在第一台建立好帳號 UserName
SELECT SUSER_SID('UserName') as SID,
LOGINPROPERTY('UserName','PasswordHash') as Password
這樣可以查出 UserName 的 SID 跟 Password 之後,再去第二台建立帳號
CREATE LOGIN [UserName] WITH 
PASSWORD = 0x02004E8321B1C292991E5B4CF64E3983B3736C59567C2736910F3E0B17BCEF6DFE0BB37AE03A6FAA9CEB3AC2BB1DE290C08710B25C0DD78D708532D54B2888A590BB2BE4200E HASHED, 
SID = 0x849A0DE6AEF87147BF0588C621CE91AF, 
DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

這樣就算 AlwaysOn 切換機器,同一個 SQL 帳號也一樣可以使用了


參考:http://www.dotblogs.com.tw/jamesfu/archive/2012/07/19/syncaccount.aspx

星期四, 6月 05, 2014

Excel 合併多個檔案的 Sheet 1 到一個檔案中

最近在進行 Excel 檔導入資料,沒想到程式開發好了之後,廠商竟然是把資料分開提供,一百多個檔案,不可能每個月都分成一百多次進行導入,於是就想起了用VBA來整理成一個檔案了

分成一百多個檔案,是整我嗎?

打開 Excel 之後,用 Alt+F11 開啟 vba 編輯器,接著點選 "插入" 新增 "模組"

接著在模組中貼上 VBA 之後點選 F5 開始執行,就會把資料都整理在這個 Excel 第一個 Sheet 了

Sub simpleXlsMerger()  
  Dim bookList As Workbook  
  Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object  
  Application.ScreenUpdating = False  
  Set mergeObj = CreateObject("Scripting.FileSystemObject")  
  'change folder path of excel files here  
  Set dirObj = mergeObj.Getfolder("D:\Excels\")  
  Set filesObj = dirObj.Files  
  For Each everyObj In filesObj  
    Set bookList = Workbooks.Open(everyObj, UpdateLinks:=False, ReadOnly:=True)  
    ActiveSheet.AutoFilterMode = False  
    bookList.Worksheets("Sheet1").Select  
    bookList.Worksheets("Sheet1").AutoFilterMode = False  
    'change "A2" with cell reference of start point for every files here  
    'for example "B3:IV" to merge all files start from columns B and rows 3  
    'If you're files using more than IV column, change it to the latest column  
    'Also change "A" column on "A65536" to the same column as start point  
    Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy  
    ThisWorkbook.Worksheets(1).Activate  
    'Do not change the following column. It's not the same column as above  
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial  
    Application.CutCopyMode = False  
    bookList.Close False  
  Next  
  End Sub  
新增一種只抓取特定行並且貼上值,避免原本的資料是公式,

  Sub simpleXlsMerger()  
  Dim bookList As Workbook  
  Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object  
  Application.ScreenUpdating = False  
  Set mergeObj = CreateObject("Scripting.FileSystemObject")  
  '修改Excel檔的所在資料夾  
  Set dirObj = mergeObj.Getfolder("D:\Excels\")  
  Set filesObj = dirObj.Files  
  For Each everyObj In filesObj  
    Set bookList = Workbooks.Open(everyObj, UpdateLinks:=False, ReadOnly:=True)  
    ActiveSheet.AutoFilterMode = False  
    bookList.Worksheets("Sheet2").Select  
    bookList.Worksheets("Sheet2").AutoFilterMode = False  
    '我只要這個範圍的資料  
    Range("A4:L4").Copy  
    ThisWorkbook.Worksheets(1).Activate  
    'Do not change the following column. It's not the same column as above  
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues  
    Application.CutCopyMode = False  
    bookList.Close False  
  Next  
  End Sub