[VBA] Read Write UTF-8 File

Dim reader As Object
Set reader = CreateObject("ADODB.Stream")
reader.CharSet = "UTF-8"
reader.LineSeparator = 10
reader.Open
reader.LoadFromFile "C:\test.txt"

Dim textLine As String
Do Until reader.EOS
    textLine = reader.ReadText(-2)
Loop
reader.Close

Dim writer As Object
Set writer = CreateObject("ADODB.Stream")
writer.CharSet = "UTF-8"
writer.Open
writer.WriteText "xxxxxxx" & Chr(10)

writer.SaveToFile "C:\test.txt", 2
writer.Close

擴充 LinqToSql 的 Left Join 關連 Table

Linq 在處理 Left Join 的語法有點煩人，想採用 EntityRef 的方式進行查詢，而建立 Entity 的關連表的方式很多，但 DBML 都是透過 Visual Studio 的工具去維護的，手動去修改他實在是不優啊！利用 partial class 的方式就可以避開這種問題。

在 AssociationAttribute 中的 IsForeignKey = false 就會採用 Left Join 進行關連查詢。

using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace MyConsoleApp.Database
{
    public partial class JobCommandMaterial
    {
        private Link<Material> _Material;

        [Association(Storage = "_Material", ThisKey = "MaterialCode", OtherKey = "MaterialCode")]
        public Material Material { get { return _Material.Value; } }
    }
}

參考：
AssociationAttribute 類別 (System.Data.Linq.Mapping)
EntityRef(TEntity) 結構 (System.Data.Linq)
Link(T) 結構 (System.Data.Linq)

LinqToSql 替換查詢的 Table

因為封存的 Table 是基於 Base Table 複製出來的，DBML 也不可能一開始就建立這些封存的 Table，但又想用 LinqToSql 去處理資料存取，所以想到用程式去換掉 SQL 裡的 Table Name。

var query = _dc.CtrlCommandRecord.Where(x => x.CreateDate > DateTime.Today);

DbCommand command = _dc.GetCommand(query);
command.CommandText = command.CommandText.Replace("CtrlCommandRecord", "CtrlCommandRecord_2017");
command.Connection = _dc.Connection;
command.Connection.Open();

DbDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
var list =  _dc.Translate<CtrlCommandRecord>(reader);

list.Dump();

用 Visual Studio 自動壓縮發佈的程式

針對只能用隨身碟安裝程式，又不想用 Web Deploy 這麼複雜工具，簡單的用壓縮檔來是處理，利用 Visual Studio 的檔案發佈再進行壓縮封裝，在原本的 pubxml 檔的後面加上 ZipPublishOutput Target 來接續發佈處理，這樣在 Visual Studio 在執行發佈時就可以產生壓縮封裝。

<?xml version="1.0" encoding="utf-8"?>
<!--
"C:\Program Files (x86)\MSBuild\14.0\Bin\MSBuild.exe" MySolution.sln /t:Rebuild /p:VisualStudioVersion=14.0;Configuration=Release;DeployOnBuild=true;PublishProfile=Release.pubxml
-->
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <WebPublishMethod>FileSystem</WebPublishMethod>
    <LastUsedBuildConfiguration>Release</LastUsedBuildConfiguration>
    <LastUsedPlatform>Any CPU</LastUsedPlatform>
    <SiteUrlToLaunchAfterPublish />
    <LaunchSiteAfterPublish>True</LaunchSiteAfterPublish>
    <ExcludeApp_Data>True</ExcludeApp_Data>
    <publishUrl>obj\Publish</publishUrl>
    <DeleteExistingFiles>True</DeleteExistingFiles>
    <AutoParameterizationWebConfigConnectionStrings>False</AutoParameterizationWebConfigConnectionStrings>
  </PropertyGroup>

  <Target Name="ZipPublishOutput" AfterTargets="GatherAllFilesToPublish;WebFileSystemPublish">
    <Message Text="== 封裝 7-Zip 安裝包 ===============================" Importance="high" />
    <PropertyGroup>
      <Today>$([System.DateTime]::Today.ToString("yyyyMMdd"))</Today>
      <zip>"C:\Program Files\7-Zip\7z.exe"</zip>
      <ReleasePath>$(ProjectDir)obj\Release\Package\PackageTmp</ReleasePath>
      <PackagePath>D:\MySolution_$(Today).exe</PackagePath>
    </PropertyGroup>

    <Exec Command='DEL $(PackagePath)' />
    <Exec Command='$(zip) a -t7z -mx9 -sfx7z.sfx -bd $(PackagePath) * | findstr /v "^$"' WorkingDirectory="$(ReleasePath)" />
  </Target>
</Project>

SQL Server Management Studio 資料表設計模式顯示欄位描述

開啟登錄檔編輯器 regedit.exe
搜尋 SSVPropViewColumnsSQL70 及 SSVPropViewColumnsSQL80
將值從 1,2,6; 改為 1,2,6,17;

各個屬性欄位代號：

1:  Column Name
2:  Data Type
3:  Length
4:  Precision
5:  Scale
6:  Allow Nulls
7:  Default Value
8:  Identity
9:  Identity Seed
10: Identity Increment
11: Row GUID
12: Nullable
13: Condensed Type
14: Not for Replication
15: Formula
16: Collation
17: Description

JavaScript 浮點數計算

一直一來都很少處理 JS 精準運算的問題，大部分的時候 UI 有點不準也不會造成太大的問題，這次用 JS 做一些加減的數字計算，遇到小數的時候就出現運算誤差，既然是 JS 計算機不精準是不行的，User 不可能接受的，找了一堆文章後沒一個靠譜的，但重點都是轉換到整數進行運算，再做浮點位移，整個計算最好盡量避免小數的處理，另外的問題就是位數不能太多。

function transformInt(num1, num2, padZeno, compute) {
    num1 = '' + num1;
    num2 = '' + num2;

    var p1 = 0, p2 = 0;
    try { p1 = num1.split(".")[1].length } catch (e) { }
    try { p2 = num2.split(".")[1].length } catch (e) { }

    if(padZeno){
        while (p1 < p2) { p1++; num1 += '0'; }
        while (p2 < p1) { p2++; num2 += '0'; }
    }

    var int1 = parseInt(num1.replace(".", ""), 10);
    var int2 = parseInt(num2.replace(".", ""), 10);
    return compute(int1, int2, p1, p2);
}


/*浮點數相加*/
function floatAdd(num1, num2) {
    return transformInt(num1, num2, true, function(int1, int2, p1, p2){
        return (int1 + int2) / Math.pow(10, p1);
    });
}

/*浮點數相減*/
function floatSub(num1, num2) {
    return transformInt(num1, num2, true, function(int1, int2, p1, p2){
        return (int1 - int2) / Math.pow(10, p1);
    });
}

/*浮點數相乘*/
function floatMul(num1, num2) {
    return transformInt(num1, num2, false, function(int1, int2, p1, p2){
        return (int1 * int2) / Math.pow(10, p1 + p2);
    });
}

/*浮點數相除*/
function floatDiv(num1, num2) {
    return transformInt(num1, num2, false, function(int1, int2, p1, p2){
        return (int1 / int2) / Math.pow(10, p1 - p2);
    });
}



function unitTest(method, list){
    console.log('=[ ' + method.name + ' ]==============================================');

    for (var i = 0; i < list.length; i++){
        var row = list[i];
        var result = method(row.num1, row.num2);

        if(result == row.ans){
            console.log(row, result);
        }else{
            console.error(row, result);
        }
    };
}

unitTest(floatAdd, [
    {num1:0.11, num2:0.2, ans: 0.31},
    {num1:0.21, num2:0.2, ans: 0.41},
    {num1:0.31, num2:0.2, ans: 0.51},
    {num1:0.41, num2:0.2, ans: 0.61},
    {num1:0.51, num2:0.2, ans: 0.71},
    {num1:0.61, num2:0.2, ans: 0.81},
    {num1:0.71, num2:0.2, ans: 0.91},
    {num1:0.81, num2:0.2, ans: 1.01},
    {num1:0.91, num2:0.2, ans: 1.11},
    {num1:1.01, num2:0.2, ans: 1.21},
]);

unitTest(floatSub, [
    {num1:1.02, num2:0.2, ans: 0.82},
    {num1:1.12, num2:0.2, ans: 0.92},
    {num1:1.22, num2:0.2, ans: 1.02},
    {num1:1.32, num2:0.2, ans: 1.12},
    {num1:1.42, num2:0.2, ans: 1.22},
    {num1:1.52, num2:0.2, ans: 1.32},
    {num1:1.62, num2:0.2, ans: 1.42},
    {num1:1.72, num2:0.2, ans: 1.52},
    {num1:1.82, num2:0.2, ans: 1.62},
    {num1:1.92, num2:0.2, ans: 1.72},
]);

unitTest(floatMul, [
    {num1:10, num2:0.14, ans: 1.4},
    {num1:10, num2:0.24, ans: 2.4},
    {num1:10, num2:0.34, ans: 3.4},
    {num1:10, num2:0.44, ans: 4.4},
    {num1:10, num2:0.54, ans: 5.4},
    {num1:10, num2:0.64, ans: 6.4},
    {num1:10, num2:0.74, ans: 7.4},
    {num1:10, num2:0.84, ans: 8.4},
    {num1:10, num2:0.94, ans: 9.4},
    {num1:10, num2:1.04, ans: 10.4},
]);

unitTest(floatDiv, [
    {num1:1.1, num2:0.1, ans: 11},
    {num1:1.1, num2:0.2, ans: 5.5},
    {num1:0.99, num2:0.3, ans: 3.3},
    {num1:1.1, num2:0.4, ans: 2.75},
    {num1:1.1, num2:0.5, ans: 2.2},
    {num1:1.32, num2:0.6, ans: 2.2},
    {num1:1.54, num2:0.7, ans: 2.2},
    {num1:1.76, num2:0.8, ans: 2.2},
    {num1:2.97, num2:0.9, ans: 3.3},
    {num1:1.1, num2:0.1, ans: 11},
]);

C# MVC + Cassette

Cassette 與 MVC 預設的 Bundle 最大的差異就是會自動轉換 CSS 的圖片網址。缺點就是開發時 F5 啟動專案會變慢。

NuGet 安裝

• Cassette.Aspnet
• Cassette.Views
• Cassette

Web.config

基本上安裝完就會自動配置以下設定，但可以在確認一下。

<configuration>
  <configSections>
    <section name="cassette"
             type="Cassette.CassetteConfigurationSection, Cassette"
             requirePermission="false"
    />
  </configSections>
  <system.web>
    <pages>
      <namespaces>
        <add namespace="Cassette.Views" />
      </namespaces>
    </pages>
    <httpModules>
      <add name="CassetteHttpModule"
           type="Cassette.Aspnet.CassetteHttpModule, Cassette.Aspnet"
      />
    </httpModules>
    <httpHandlers>
      <!-- 用來處理綑綁打包的處理器 -->
      <add path="cassette.axd"
           verb="*"
           type="Cassette.Aspnet.CassetteHttpHandler, Cassette.Aspnet"
       />
    </httpHandlers>
  </system.web>
  <system.webServer>
    <!-- 保留 httpHandlers(IIS6) 及 handlers(IIS7) 需要關閉驗證 -->
    <validation validateIntegratedModeConfiguration="false" />
    <modules>
      <add name="CassetteHttpModule"
           type="Cassette.Aspnet.CassetteHttpModule, Cassette.Aspnet"
      />
    </modules>
    <handlers>
      <!-- 用來處理綑綁打包的處理器 -->
      <add name="CassetteHttpHandler"
           path="cassette.axd"
           verb="*"
           allowPathInfo="true"
           preCondition="integratedMode"
           type="Cassette.Aspnet.CassetteHttpHandler, Cassette.Aspnet"
       />
    </handlers>
  </system.webServer>

  <!-- For configuration options: http://getcassette.net/documentation/v2/web-config -->
  <cassette rewriteHtml="false" />
</configuration>

Views/Web.config

增加給 cshtml 用的 namespace

<configuration>
  <system.web.webPages.razor>
    <pages pageBaseType="System.Web.Mvc.WebViewPage">
      <namespaces>
        <add namespace="Cassette.Views" />
      </namespaces>
    </pages>
  </system.web.webPages.razor>
</configuration>

CassetteConfiguration.cs

public class CassetteBundleConfiguration : IConfiguration<BundleCollection>
{
    public void Configure(BundleCollection bundles)
    {
        bundles.AddPerIndividualFile<StylesheetBundle>("Content");
        bundles.AddPerIndividualFile<ScriptBundle>("Scripts");

        bundles.Add<StylesheetBundle>("Styles/Bundles", new string[] 
        {
            "~/Scripts/bootstrap/css/bootstrap.css",
            "~/Scripts/bootstrap/css/bootstrap-theme.css",
            //...
        });

        bundles.Add<ScriptBundle>("Scripts/Bundles", new string[] 
        {
            "~/Scripts/jquery/jquery-1.12.2.js",
            "~/Scripts/jquery.validate/jquery.validate-1.12.0.js",
            //...
        });
    }
}

_Layout.cshtml

<!DOCTYPE html>
@{
    Bundles.Reference("Styles/Bundles");
    Bundles.Reference("Scripts/Bundles");
}
<html>
<head>
    @Bundles.RenderStylesheets()
</head>
<body id="DialogLayout">
    @Bundles.RenderScripts()
</body>
</html>

Release Mode

當 Web.config 的 compilation debug="false" 就會壓縮綑綁。

<compilation debug="false" targetFramework="4.5" />

天文鐘