無腦Hard Code程式

跨年完第一天上班，還有放假症候群的時候
有user反應某個舊功能下拉選單年份要更新
看了一下，程式的年份是用寫死的 =="
(i < 6的地方，6是寫死的，不是用算的)












有可能當初需求是取最近的六年資料
改版後就可以用算的顯示年份了

[Line Bot] 建置Line Bot環境(使用Bottender + Heroku)

Bottender

Bottender是chot bot框架，著重async function語法
安裝時，node.js的版本需大於7.6

安裝Bottender的環境參考下篇
https://bottender.js.org/docs/GettingStarted

Heroku

安裝完heroku cli後

$ heroku login

將repository clone至本機

$ heroku git:clone -a <heroku app name>
$ cd <heroku app name>

push 至hekoku

$ git add .
$ git commit -am "make it better"
$ git push heroku master

Line Developer

登入至line developer新增帳號
若要刪除自動回覆訊息，要進入Line @Manager移除

訊息 -> 自動回覆訊息 delete
記下Line Developer頁面底下的Channel secret、Channel access token
並於到Heroku頁面設定參數 (Settings -> Config Vars新增變數)




另外要Use Webhhok要啟用Enabled
Webhook URL加入heroku app的網址


JS有兩的地方要調整，accessToken和channelSecret改成讀參數

bottender.config.js

module.exports = {
  // line
  accessToken: process.env.ACCESS_TOKEN,
  channelSecret: process.env.CHANNEL_SECRET,
};

sendMethod改成 reply
index.js

const { LineBot } = require('bottender');
const { createServer } = require('bottender/express');

const config = require('./bottender.config.js');

const bot = new LineBot({
  accessToken: config.accessToken,
  channelSecret: config.channelSecret,
  sendMethod: 'reply', // Default: 'push'
});

bot.onEvent(async context => {
  await context.sendText('Hello World');
});

const server = createServer(bot);

server.listen((process.env.PORT || 5000), () => {
  console.log('server is running on 5000 port...');
});

執行畫面


Ref:
http://rainstingtw.blogspot.com/2018/07/how-to-use-bottender-with-heroku-line-bot.html
來寫個氣象機器人吧！


 

[SQL Server] Where condition in comma seperated string

最近在抓資料遇到一個狀況
比對的條件是用逗號分隔，如下圖


但是SQL Server沒有類似現成的function
另外，也不能在條件的前後加"'"，直接用in
後來找到一篇，可以用charIndex，並在前後加","
這樣就可以比對出想要的結果了

DECLARE @categoryId INT
SET @categoryId = 3

SELECT *
FROM myTable
WHERE CHARINDEX(',' + CAST(@categoryId AS VARCHAR(MAX)) + ',', ',' + categoryIds + ',') > 0

Ref:
https://stackoverflow.com/questions/33278789/how-can-i-check-whether-a-number-is-contained-in-comma-separated-list-stored-in

[C#][Json]讀Json處理小數點

讀JSON檔的時候，有些有小數的欄位太長
想去掉小數點，可以在Entity上面加converter
透過converter，取到的資料已經做好處理
參考範例如下

public class TestEntity {
    [JsonConverter(typeof(CustomDecimalNullConverter))]
    public decimal val {get; set;}
}


void Main() {   
    var settings = new JsonSerializerSettings();
    settings.Converters.Add(new CustomDecimalNullConverter());
    var result = JsonConvert.DeserializeObject>(json, settings);
}

// For completeness: A stupid example converter
class CustomDecimalNullConverter : JsonConverter
{
    public override bool CanConvert(Type objectType)
    {
        return objectType == typeof(decimal);
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        if (reader.TokenType == JsonToken.Null)
        {
            return 0m;
        }
        else
        {
            return Convert.ToDecimal(reader.Value);
        }
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        writer.WriteValue((decimal)value);
    }
}

Ref: https://stackoverflow.com/questions/51885268/custom-rule-for-deserializing-decimal-values-in-json-net
 

[IIS]HTTP 錯誤 500.19 - Internal Server Error

最近公司因應稽核，把權限更改為更嚴謹
原本測試區的server也變成權限不足無法連

 



網路上找到的解法是，在資料夾的安全性裡
新增Authenticated Users，就可以連了













不過最後有提到，要設定IIS AppPool就沒去研究了
因為不是新增應用程式，而且測試環境沒那麼嚴謹

Ref:
https://dotblogs.com.tw/caubekimo/2010/09/19/17805

[.Net] Connect Exchange Server

為了要可以使用Exchage server寄信，必須從NuGet下載Exchange Web Services (EWS)
後來測試，怎麼連不都連不上，才發現原來有相依.Net的版本(好像要3.5以上)

另外一個要注意的是網址，用的是exchange.asmx，不是原本的xxx.com/owa/auth/logon.aspx
這個環節也試了很久，後來看到別人寫的範例都是.asmx，想說試看看就可以連了

Sample Code:

ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(ValidateServerCertificate);
ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2010);//版本預設值最新版
service.Credentials = new WebCredentials(ConfigurationManager.AppSettings["mailAccount"], ConfigurationManager.AppSettings["mailPassword"]);
service.Url = new Uri("https://xxxx.com/ews/exchange.asmx"); // Server路徑

EmailMessage email = new EmailMessage(service);
  
email.ToRecipients.Add(contact.Name, contact.Email); //收件者

email.CcRecipients.Add("AAA", "test1@mail.com"); //CC
email.BccRecipients.Add("BBB", "test2@mail.com "); //密件副本
email.Subject = mailJob.Subject; //主旨
email.Body = mailJob.Content; //內容
email.Body.BodyType = BodyType.HTML; //格式
email.Send();

Ref:
http://blog.oscarscode.com/dot-net/get-started-with-exchange-web-services-ews/
https://stackoverflow.com/questions/13517323/exchange-web-service-api-and-401-unauthorized-exception
https://blog.miniasp.com/post/2007/11/01/The-remote-certificate-is-invalid-according-to-the-validation-procedure.aspx

[SQL]Remove duplicates

This will also take care of duplicates (return one row for each user_id):

SELECT * FROM (
  SELECT u.*, FIRST_VALUE(u.rowid) OVER(PARTITION BY u.user_id ORDER BY u.date DESC) AS last_rowid
  FROM users u
) u2
WHERE u2.rowid = u2.last_rowid

Ref:
http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column