2021年5月9日 星期日

Golang 安裝 xorm.io/reverse

xorm/reverse 可將資料表結構反轉成程式中給 xorm 使用的struct

 [安裝 xorm.io/reverse 發生錯誤]

> go get xorm.io/reverse  
# github.com/mattn/go-sqlite3
cc1.exe: sorry, unimplemented: 64-bit mode not compiled in
原因:系統的 gcc 需支援 64 bit,而我早期裝的是 32 bit
> gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=c:/mingw/bin/../libexec/gcc/mingw32/4.7.2/lto-wrapper.exe
Target: mingw32
Configured with: ../gcc-4.7.2/configure --enable-languages=c,c++,ada,fortran,objc,obj-c++ --disable-sjlj-exceptions --with-dwarf2 --enable-shared --enable-libgomp --disable-win32-registry --enable-libstdcxx-debug --disable-build-poststage1-with-cxx --enable-version-specific-runtime-libs --build=mingw32 --prefix=/mingw
Thread model: win32
gcc version 4.7.2 (GCC)


[解決辦法:安裝 64 位元的 MinGW-w64]
下載網址









安裝後,將 MinGW-w64 的 bin 目錄,加入使用者環境變數
C:\Program Files\mingw-w64\x86_64-8.1.0-win32-seh-rt_v6-rev0\mingw64\bin
如果暫時不想刪除舊的 C:\MinGW\bin,須將新的路徑,移到上方





重啟命令視窗,即可讀取到新的設定(若是在 VS code Terminal,須將所有 VS code 關閉重啟)
> gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=C:/Program\ Files/mingw-w64/x86_64-8.1.0-win32-seh-rt_v6-rev0/mingw64/bin/../libexec/gcc/x86_64-w64-mingw32/8.1.0/lto-wrapper.exe
Target: x86_64-w64-mingw32
Configured with: ../../../src/gcc-8.1.0/configure --host=x86_64-w64-mingw32 --build=x86_64-w64-mingw32 --target=x86_64-w64-mingw32 --prefix=/mingw64 --with-sysroot=/c/mingw810/x86_64-810-win32-seh-rt_v6-rev0/mingw64 --enable-shared --enable-static --disable-multilib --enable-languages=c,c++,fortran,lto --enable-libstdcxx-time=yes --enable-threads=win32 --enable-libgomp --enable-libatomic --enable-lto --enable-graphite --enable-checking=release --enable-fully-dynamic-string --enable-version-specific-runtime-libs --disable-libstdcxx-pch --disable-libstdcxx-debug --enable-bootstrap --disable-rpath --disable-win32-registry --disable-nls --disable-werror --disable-symvers --with-gnu-as --with-gnu-ld --with-arch=nocona --with-tune=core2 --with-libiconv --with-system-zlib --with-gmp=/c/mingw810/prerequisites/x86_64-w64-mingw32-static --with-mpfr=/c/mingw810/prerequisites/x86_64-w64-mingw32-static --with-mpc=/c/mingw810/prerequisites/x86_64-w64-mingw32-static --with-isl=/c/mingw810/prerequisites/x86_64-w64-mingw32-static --with-pkgversion='x86_64-win32-seh-rev0, Built by MinGW-W64 project' --with-bugurl=https://sourceforge.net/projects/mingw-w64 CFLAGS='-O2 -pipe -fno-ident -I/c/mingw810/x86_64-810-win32-seh-rt_v6-rev0/mingw64/opt/include -I/c/mingw810/prerequisites/x86_64-zlib-static/include -I/c/mingw810/prerequisites/x86_64-w64-mingw32-static/include' CXXFLAGS='-O2 -pipe -fno-ident -I/c/mingw810/x86_64-810-win32-seh-rt_v6-rev0/mingw64/opt/include -I/c/mingw810/prerequisites/x86_64-zlib-static/include -I/c/mingw810/prerequisites/x86_64-w64-mingw32-static/include' CPPFLAGS=' -I/c/mingw810/x86_64-810-win32-seh-rt_v6-rev0/mingw64/opt/include -I/c/mingw810/prerequisites/x86_64-zlib-static/include -I/c/mingw810/prerequisites/x86_64-w64-mingw32-static/include' LDFLAGS='-pipe -fno-ident -L/c/mingw810/x86_64-810-win32-seh-rt_v6-rev0/mingw64/opt/lib -L/c/mingw810/prerequisites/x86_64-zlib-static/lib -L/c/mingw810/prerequisites/x86_64-w64-mingw32-static/lib '
Thread model: win32
gcc version 8.1.0 (x86_64-win32-seh-rev0, Built by MinGW-W64 project)

確定設定生效後,即可正常安裝
安裝後會在 GOPATH 底下的 bin 目錄,產生 reverse.exe 執行檔
> go get xorm.io/reverse  
go get: added xorm.io/reverse v0.1.1


[xorm reverse 用法]
1.新增設定文件,例如檔名為 custom.yml,內容為
kind: reverse
name: aa #資料庫名稱
source:
  database: mysql #哪一種資料庫
  conn_str: 'root:密碼@tcp(127.0.0.1:3306)/aa' #連接資料庫設定
targets:
- type: codes
  language: golang
  output_dir: ./test_model #輸出在哪個目錄底下
2.執行將資料表轉換成程式碼結構的命令
> reverse -f custom.yml
結果會在 output_dir(./test_model)目錄下產生 models.go 檔

aa資料庫底下,有兩個資料表a1、b1
CREATE TABLE `a1` (
  `a_id` int(11) NOT NULL,
  `a_data` varchar(45) DEFAULT NULL,
  `a_dtime` datetime DEFAULT NULL,
  `a_cur_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='測試1';

CREATE TABLE `b1` (
  `b_id` int(11) NOT NULL,
  `b_num` int(10) UNSIGNED ZEROFILL DEFAULT NULL COMMENT '測試',
  `b_num2` int(11) NOT NULL,
  `b_data` decimal(11,2) NOT NULL,
  `b_add_dtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `a1`
  ADD PRIMARY KEY (`a_id`),
  ADD UNIQUE KEY `a_data` (`a_data`);

ALTER TABLE `b1`
  ADD PRIMARY KEY (`b_id`);


ALTER TABLE `a1`
  MODIFY `a_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `b1`
  MODIFY `b_id` int(11) NOT NULL AUTO_INCREMENT;

產生的 models.go 檔內容如下,轉成 xorm 的Column屬性定義:
package models

import (
	"time"
)

type A1 struct {
	AId      int       `xorm:"not null pk autoincr INT(11)"`
	AData    string    `xorm:"unique VARCHAR(45)"`
	ADtime   time.Time `xorm:"DATETIME"`
	ACurTime time.Time `xorm:"TIMESTAMP"`
}

type B1 struct {
	BId       int       `xorm:"not null pk autoincr INT(11)"`
	BNum      int       `xorm:"comment('測試') INT(10)"`
	BNum2     int       `xorm:"not null INT(11)"`
	BData     string    `xorm:"not null DECIMAL(11,2)"`
	BAddDtime time.Time `xorm:"not null default CURRENT_TIMESTAMP TIMESTAMP"`
}
跟原資料表結構相比,可發現有些細節消失了(資料表COMMENT、欄位UNSIGNED ZEROFILL屬性、...也許還會有其他的)
另外測試將產生的 models.go 內容,用engine.Sync2(new(A1), new(B1)) 自動建立資料表,這些細節也是沒有了。
engine.Sync2()能夠根據程式碼中的資料表結構,部分自動同步建立(修改)資料庫中的資料表,例如:新增欄位可以,但刪除欄位不可以。



參考:



2021年5月8日 星期六

Nginx 反向代理 WebSocket

設定 https://example.com:9999/socket.io
反向代理到 http://192.168.0.10:9999 的 WebSocket 服務

map $http_upgrade $connection_upgrade {
    default Upgrade;
    '' close;
}

upstream websocket {
    server 192.168.0.10:9999;
}

server {
    listen 9999 ssl;
    server_name example.com;
    
    #ssl
    ssl_certificate /etc/nginx/ssl/example.com.crt;
    ssl_certificate_key /etc/nginx/ssl/example.com.key;

    location /socket.io {
        proxy_pass http://websocket;
        #Version 1.1 is recommended for use with keepalive connections
        proxy_http_version 1.1; #WebSocket
        proxy_set_header Upgrade $http_upgrade; #WebSocket
        proxy_set_header Connection $connection_upgrade; #WebSocket       
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto https;
        proxy_set_header Cookie $http_cookie;
    }
    
    location = /favicon.ico {
        log_not_found off;
        access_log off;
    }
}


參考:



2021年4月7日 星期三

Windows 視窗置頂

C# 列出 windows 目前開啟的視窗(Process),將選擇的視窗(Process)設為置頂,固定在最上層。

程式碼:

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WinTopMost
{
    public partial class Form1 : Form
    {
        [DllImport("user32.dll", SetLastError = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        private static extern bool SetWindowPos(IntPtr hWnd, int hWndInsertAfter, int x, int y, int cx, int cy, int uFlags);
        private const int HWND_TOPMOST = -1;
        private const int HWND_NOTOPMOST = -2;
        private const int SWP_NOMOVE = 0x0002;
        private const int SWP_NOSIZE = 0x0001;

        [DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowLong(IntPtr hWnd, int nIndex);
        const int GWL_EXSTYLE = -20;
        const int WS_EX_TOPMOST = 0x0008;

        private ArrayList ProcessDataArrList;

        public Form1()
        {
            InitializeComponent();
            LoadProcesses();
        }


        //取得Process
        private void LoadProcesses()
        {
            //this.listBox1.Items.Clear();
            Process[] allProcesses = Process.GetProcesses();

            this.ProcessDataArrList = new ArrayList();
            foreach (Process process in allProcesses)
            {
                if (this.chkWinTitleNoEmpty.Checked && "" == process.MainWindowTitle)
                {
                    //略過MainWindowTitle為空
                    continue;
                }
                bool isTop = IsWindowTopMost(process.MainWindowHandle);
                if (this.chkOnlyTopWin.Checked && !isTop)
                {
                    //略過非置頂
                    continue;
                }
                string isTopStr = isTop ? "Top" : "X  ";
                string name = isTopStr + " # " + process.MainWindowTitle + " # " + process.Id + " # " + process.ProcessName;
                this.ProcessDataArrList.Add(new ProcessData(process, name));
            }

            this.listBox1.DataSource = ProcessDataArrList;
            this.listBox1.ValueMember = "MyKey";
            this.listBox1.DisplayMember = "MyName";
        }

        //視窗是否置頂
        private bool IsWindowTopMost(IntPtr hWnd)
        {
            int exStyle = GetWindowLong(hWnd, GWL_EXSTYLE);
            return (exStyle & WS_EX_TOPMOST) == WS_EX_TOPMOST;
        }

        //置頂
        private void btnTopMost_Click(object sender, EventArgs e)
        {
            if (this.listBox1.SelectedIndex != -1)
            {
                ProcessData ProcessData = this.listBox1.SelectedItem as ProcessData;
                SetWindowPos(ProcessData.MyKey.MainWindowHandle,
                    HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE | SWP_NOSIZE);

                LoadProcesses();
            }
        }

        //取消置頂
        private void btnNoTopMost_Click(object sender, EventArgs e)
        {
            if (this.listBox1.SelectedIndex != -1)
            {
                ProcessData ProcessData = this.listBox1.SelectedItem as ProcessData;
                SetWindowPos(ProcessData.MyKey.MainWindowHandle,
                    HWND_NOTOPMOST, 0, 0, 0, 0, SWP_NOMOVE | SWP_NOSIZE);

                LoadProcesses();
            }
        }

        //更新列表
        private void btnRefreshList_Click(object sender, EventArgs e)
        {
            LoadProcesses();
        }

        //MainWindowTitle不為空
        private void chkWinTitleNoEmpty_CheckedChanged(object sender, EventArgs e)
        {
            LoadProcesses();
        }

        //只列出置頂視窗
        private void chkOnlyTopWin_CheckedChanged(object sender, EventArgs e)
        {
            LoadProcesses();
        }

        //連結網址
        private void linkLabelxyz_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string target = "https://xyz.cinc.biz";
            try
            {
                //System.Diagnostics.Process.Start(target);//系統找不到指定的檔案
                //https://stackoverflow.com/questions/21835891/process-starturl-fails
                System.Diagnostics.Process.Start(new ProcessStartInfo(target) { UseShellExecute = true });
            }
            catch (System.ComponentModel.Win32Exception noBrowser)
            {
                if (noBrowser.ErrorCode == -2147467259)
                    MessageBox.Show(noBrowser.Message);
            }
            catch (System.Exception other)
            {
                MessageBox.Show(other.Message);
            }
        }


    }

    public class ProcessData
    {
        private Process pKey;
        private string pName;

        public ProcessData(Process process, string name)
        {

            this.pKey = process;
            this.pName = name;
        }

        public Process MyKey
        {
            get
            {
                return pKey;
            }
        }

        public string MyName
        {

            get
            {
                return pName;
            }
        }
    }
}



執行畫面:




檔案下載:
https://drive.google.com/uc?export=download&id=11-f-e5sxz_yYdu-csaKu3CuUUzuItS--

SHA256:
ddebfe2fae4009667b2401d746f8113d57bb799f751c8056df2eec208c5e03ab




參考:



2021年3月31日 星期三

Google Sheets 抓上櫃股票股價

在 Google Sheets 試算表中,上市股票股價可以使用 GOOGLEFINANCE() 取得股價。 
上櫃OTC股票則需另外用指令碼處理。
  1. 新增一個工作表,假設名稱為「上櫃股票」,並填入股票代碼。
    如下圖:


  2. 「工具」 -> 「指令碼編輯器」


  3. 填入以下程式碼,選擇執行「mainOTC()」
    function getDataOTC(sheet_name, col_code, col_stock_name, col_price, col_updtime, col_err, row_start, row_end) {
      var spreadsheet = SpreadsheetApp.getActive();
      var rows = spreadsheet.getRange(sheet_name + "!" + col_code + row_start + ":" + col_code + row_end);
      var numRows = rows.getNumRows();
      var data = rows.getValues();
      var options = {
        //若來源網址SSL不安全(或過期)出現Exception: SSL Error,且確定來源資料安全時,可關閉validateHttpsCertificates
        //'validateHttpsCertificates': false
      };
      for (var i = 0; i < numRows; i++) {
        var code = data[i][0];
        //上市
        //https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=tse_2353.tw&json=1&delay=0
        //上櫃
        //https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=otc_6488.tw&json=1&delay=0
        var url = "https://mis.twse.com.tw/stock/api/getStockInfo.jsp?ex_ch=otc_" + code + ".tw&json=1&delay=0"
        //舊的
        //var url = "https://mis.tse.com.tw/stock/api/getStock.jsp?ch=" + code + ".tw&json=1&_=";
        try {
          var response = UrlFetchApp.fetch(url);
          var json = response.getContentText("UTF-8");
          var data_arr = JSON.parse(json);
          spreadsheet.getRange((sheet_name + "!" + col_stock_name) + (row_start + i)).setValue(data_arr.msgArray[0].n);//股票名稱
          // data_arr.msgArray[0].y 股價(昨天成交價)
          // data_arr.msgArray[0].z 股價(最近成交價)
          var stock_price = data_arr.msgArray[0].z.trim();
          if (isNaN(parseFloat(stock_price))) {
            throw ("股價數值異常:" + stock_price);//盤中可能抓不到股價,抓到"-"
          }
          spreadsheet.getRange((sheet_name + "!" + col_price) + (row_start + i)).setValue(stock_price);
          spreadsheet.getRange((sheet_name + "!" + col_updtime) + (row_start + i)).setValue("股價更新:" + Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm:ss"));
          spreadsheet.getRange((sheet_name + "!" + col_err) + (row_start + i)).setValue("");
        } catch (e) {
          spreadsheet.getRange((sheet_name + "!" + col_err) + (row_start + i)).setValue("Err:" + Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm:ss") + " " + e);
        }
        Utilities.sleep(1000);//等1秒再繼續下一個
      }
    }
    
    function mainOTC() {
      getDataOTC(
        "上櫃股票", //工作表名稱
        "A",//股票代碼在工作表的欄位
        "B",//股票名稱在工作表的欄位
        "C",//股價在工作表的欄位
        "D",//更新時間在工作表的欄位
        "E",//錯誤訊息在工作表的欄位
        2,  //資料從第幾列開始
        21  //資料在第幾列結束
      );
    }



  4. 因為要在工作表自動填入資料,所以須授權程式可存取 Google Docs
    若之後要取消授權,可於 https://myaccount.google.com/permissions?pli=1 取消。














  5. 抓取結果


  6. 可設定「觸發條件」,讓程式每隔一段時間自動執行,避免每次都要手動執行。











參考:

2021年2月25日 星期四

jQuery trggie 傳參數

$('#test').on('change', function (event, a1, a2) {
    console.log(event); //The event object is always passed as the first parameter to an event handler

    console.log(a1); //aaa
    console.log(a2); //bbb
});

//第二個列陣參數,可用來傳其它參數
$('#test').trigger('change', ['aaa', 'bbb']);


參考:
https://api.jquery.com/trigger/
https://stackoverflow.com/questions/16401538/passing-parameters-on-jquery-trigger