DNN - 開發環境中如何產生 install.zip

建立好 DNN的模組開發環境後
若直接編譯專案，即便編譯成功，專案目錄夾內也不會產生 install 目錄。

解決方法:

VS要先進入 .build \ Build.proj    把Debug 改為 Release 模式
再重新編譯專案，此時就可以在專案目錄夾中找到install.zip

例:
      C:\inetpub\wwwroot\StockModule\App01\install\App01_00.00.01_Install.zip
      C:\inetpub\wwwroot\StockModule\App01\install\App01_00.00.01_Source.zip

VS - Visual Studio 如何一個.Sln 當中放入多個 Project

Visual Studio 如何一個.Sln 當中放入多過個 Project

1.新增專案 -->其他專案類型-->Visual Studio 方案 -->新增空白方案
2.在新增的空白方案上 --> (右鍵) 新增專案

步驟2可做多次就可以建立多個專案在同一個方案當中囉。

DNN - 新增DNN Module 出現 沒有IIS權限的問題

新增DNN Module 出現 沒有IIS權限的問題

1.執行 VS 2012 時記得要用系統管理者的權限執行
2.進入 IIS
   在 預設網站 --> 繫結 --> 新增一個 Dnndev.me 的位置
3.重新新增專案即可

IIS - 應用程式挑選不到.Net Framework 4.0

IIS內要把Web目錄轉換成 應用程式時竟然挑選不到 .Net Framework 4.0
解決方法: 
用系統管理員權限執行DOS
C:\WINDOWS\Microsoft.NET\Framework\v4.0.xxxxx版本 aspnet_regiis -i 
執行完畢後重新開啟IIS應該就可以挑選到 4.0版本。

ASP.Net - GridViewScroll with jQuery (GridView 凍結表頭)

一個很不錯的GridView 凍結表頭範例
http://gridviewscroll.aspcity.idv.tw/

DNN - 如何修改隱私權內文

Go to menu "Admin > Languages" and click for edit its content for the site or the host.
Keep on the edition of the "GlobalResources" file.
Search for "MESSAGE_PORTAL_PRIVACY.Text" and "MESSAGE_PORTAL_TERMS.Text" and modify them.

DNN - UserProfile圖像無法顯示

解決方法:
系統管理員登入
主機管理-->SiteManagement
進階設定-->網站別名-->Default Alias-->"Dnnap/dnn"

DNN - Images放置位置

C:\inetpub\wwwroot\DNN\images

DNN - 調整網站時區

網站設定-->進階設定-->可用性設定-->網站時區 (UTC+08:00)

DNN - AD登入如何設定DefaultButton

修改Login.ascx

C:\inetpub\wwwroot\DNN\DesktopModules\AuthenticationServices\ActiveDirectory\Login.ascx



主要是增加一個Panel 並且設定好 DefaultButton="cmdLogin"

例:

<div class="dnnForm dnnLoginService dnnClear">
<asp:Panel ID="pnlLogon" runat="server" DefaultButton="cmdLogin" Width="100%" >
    <div class="dnnFormItem">
        <asp:label id="plUsername" AssociatedControlID="txtUsername" runat="server" resourcekey="Username" CssClass="dnnFormLabel" />
        <asp:textbox id="txtUsername" runat="server" />
    </div>
    <div class="dnnFormItem">
        <asp:label id="plPassword" AssociatedControlID="txtPassword" runat="server" resourcekey="Password" CssClass="dnnFormLabel" />
        <asp:textbox id="txtPassword" textmode="Password" runat="server" />
    </div>
    <div class="dnnFormItem" id="divCaptcha1" runat="server" visible="false">
        <asp:label id="plCaptcha" AssociatedControlID="ctlCaptcha" runat="server" resourcekey="Captcha" CssClass="dnnFormLabel" />
    </div>
    <div class="dnnFormItem" id="divCaptcha2" runat="server" visible="false">
        <dnn:captchacontrol id="ctlCaptcha" captchawidth="130" captchaheight="40" runat="server" errorstyle-cssclass="dnnFormMessage dnnFormError" />
    </div>
    <p><asp:LinkButton id="cmdLogin" resourcekey="cmdLogin" cssclass="dnnPrimaryAction" text="Login" runat="server" /></p>
</asp:Panel>
</div>

DNN - User反應無法登入，查看EvenLog 出現 "Validation of viewstate MAC failed"

可能可以用下面方法解決

參考網址:
http://blog.janjonas.net/2013-04-18/dotnetnuke-workaround-validation-viewstate-failed-exception-dnn_7-login-form

DotNetNuke: Workaround for “Validation of viewstate MAC failed” exception when using the DNN 7 login form in multiple tabs

DotNetNuke: Workaround for “Validation of viewstate MAC failed” exception when using the DNN 7 login form in multiple tabs
Jan Jonas April 18th, 2013 Comments: 8Categories: Howto, ProgrammingAs I’ve written in one of my previous posts, I found out that one gets a “Validation of viewstate MAC failed” exception when submitting a DNN login form and the login status has changed from “not logged in” to “logged in” after the login form initially was loaded (e.g. by using an addition browser tab to log in).
After contacting the DNN support team it turned out, that this is not a bug, but a security feature in DNN. In detail, DNN adds the username of the current session to the APS.NET ViewStateUserKey (see “Take Advantage of ASP.NET Built-in Features to Fend Off Web Attacks” on MSDN for more information).
In DNN profession knowledge base, two resolutions are described which both have their drawbacks:
•One could edit the Default.aspx.cs and remove the username from the ViewStateUserKey which has to be done after each DNN update because Default.aspx.cs will be overwritten when updating the framework.
•One could disable “ViewState MAC validation” entirely by setting enableViewStateMac to false in Web.config file which is not recommended due to security problems.I finally came up with the following code snippet that I’ve added to the code behind file of the skin:


protected override void OnInit(EventArgs e)
{
  // Catch "Validation of viewstate MAC failed" exceptions and redirect the user
  // to the current page (i.e. force a redirect on the client)
  Page.Error += (sender, args) =>
  {
    if (!(HttpContext.Current.Error is HttpException)) return;
    if (!(HttpContext.Current.Error.InnerException is ViewStateException)) return;

    HttpContext.Current.Response.Clear();
    Response.Redirect(Request.UrlReferrer == null ? Request.Url.ToString() : Request.UrlReferrer.ToString());
  };

  base.OnInit(e);
}

In detail the code above adds an error handler that catches “Validation of viewstate MAC failed” exceptions and forces the client to reload the current page in case of such an excpetion (which updates the ViewStateUserKey according to the current session state). Since this error handler needs to be added on every page, the skin is the perfect place to put the code into.
Related posts:
1DotNetNuke: “Validation of viewstate MAC failed” exception when using the DNN 7 login form in multiple tabs 2ASP.NET MVC 3: Ajax Form with jQuery validate supporting (unobtrusive) Client Side Validation and Server Side Validation 3ASP.NET MVC 3: Using jQuery .ajax() function to submit Ajax Form supporting (unobtrusive) Client Side Validation and Server Side Validation 4ASP.NET MVC 3: Using JSON result for jQuery Ajax Forms validation 5ASP.NET MVC 3: Validate complex Types (Objects & Lists) in Ajax Form using jQuery and JSON on Client Side and Server Side

DNN - 隱藏原DNN系統的登入頁簽

編輯
C:\inetpub\wwwroot\DNN706\DesktopModules\AuthenticationServices\DNN\Login.ascx

<!-- 20150709 隱藏 原DNN系統的登入頁簽-->
<%@ DNN:DNNTabStrip
    ID="tsLogin"
    runat="server"
    TabRenderMode="All"
    CssTabContainer="LoginTabGroup"
    CssContentContainer="LoginContainerGroup"
    DefaultContainerCssClass="LoginContainer"
    DefaultLabel-CssClass="LoginTab"
    DefaultLabel-CssClassHover="LoginTabHover"
    DefaultLabel-CssClassSelected="LoginTabSelected"
    visible="false"
    SelectedIndex="1" %>
<!-- ~~ -->

DNN - 修改字型的方法

C:\inetpub\wwwroot\DNN\Portals\_default\Skins\Gravity\skin.css

找到 font-family ，在這之後增加 "字型名稱"

例:

微軟正黑體
font-family: Microsoft JhengHei

DNN - 開啟UserOnline功能

1. Host--> Host Settings --> 關閉 Users Online 模組的功能？ ==> 取消勾選

2. Host--> 排程 --> Purge Users Online --> 啟用排程 --> RunNow --> update

3. 連至DataBase --> DNN DB --> select * from UsersOnline

DNN - 使用NPOI元件時References ICSharpCode.SharpZipLib.dll 會出現問題

一開始是把

ICSharpCode.SharpZipLib.dll
NPOI.dll
NPOI.OOXML.dll
NPOI.OpenXml4Net.dll
NPOI.OpenXmlFormats.dll

5個DLL檔放置於 C:\inetpub\wwwroot\DNN\bin\

此時再上傳安裝新的Module時會出現DLL衝突的異常畫面

後來把 ICSharpCode.SharpZipLib.dll 移入 C:\inetpub\wwwroot\DNN\bin\Providers
即可改善此問題。

猜想應該是 DNN原本內建的 SharpZipLib.dll 與 NPOI 內建的 ICSharpCode.SharpZipLib.dll 相衝突

DNN - 中文Menu彈跳視窗異常

DNN 7.0.X Bug

網站管理-->網站設定-->可用性設定-->(取消勾選)Enable Pop-Ups

DNN - 串登入者資訊

private UserInfo _currentUser =
DotNetNuke.Entities.Users.UserController.GetCurrentUserInfo();


or


DotNetNuke.Entities.Users.UserInfo user = DotNetNuke.Entities.Users.UserController.GetUser(PortalSettings.PortalId, user_id, true);


or


Dim nowUser As UserInfo = DotNetNuke.Entities.Users.UserController.GetCurrentUserInfo
response.write(nowUser)

Highcharts-參考範例

Highcharts 這個套件似乎蠻有趣的，先收集一下相關教學，有空時再來玩玩

Highstock Demos
http://www.highcharts.com/stock/demo

Highcharts Asp.net: Create Pie chart with jQuery ajax in C# (MS SQL database)
http://codepedia.info/2015/07/highcharts-asp-net-create-pie-chart-with-jquery-ajax-in-c-sharp-ms-sql-database/

Highcharts in asp.net using jquery ajax
http://www.codeproject.com/Articles/820349/Highcharts-in-asp-net-using-jquery-ajax

Highchart with C# and Data from database
http://highchartsdata.blogspot.tw/2013/04/highchart-with-c-and-data-from-database.html

在 Asp.net 中使用 DataTable 作為 Highcharts 資料來源
http://thedeadbody.blogspot.tw/2014/07/aspnet-datatable-highcharts.html

highcharts plugin Demo
http://w3937.pixnet.net/blog/post/96638123-%5Bjquery%5D-highcharts-plugin-demo

強大的jQuery圖表套件-Highcharts
http://www.dotblogs.com.tw/lastsecret/archive/2011/01/30/21137.aspx

jquery图表插件highcharts
http://blog.csdn.net/xuqianghit/article/details/6196276

如何讓HighCharts支援圖表匯出中文檔名 using C# MVC4
http://pinylnln.pixnet.net/blog/post/45015932-%E5%A6%82%E4%BD%95%E8%AE%93highcharts%E6%94%AF%E6%8F%B4%E5%9C%96%E8%A1%A8%E5%8C%AF%E5%87%BA%E4%B8%AD%E6%96%87%E6%AA%94%E5%90%8D-using-c%23-m

Highstock K線圖教學1
http://www.peng8.net/2015/02/06/how-to-use-highstock-with-kline/

Highstock K線圖教學2
http://annan211.iteye.com/blog/2058913

Highstock K線圖教學3
http://www.xuebuyuan.com/2049467.html

SQL-均線糾結+成交量大於5日均量3倍

select a.xDate , a.StockNo , b.Close_5MA ,c.Close_20MA , d.Close_55MA ,e.Volume_5MA, a.xVolume
from StockDailyHis2 as a
join Close_5MA as b on a.StockNo = b.StockNo and a.Date = b.Date
join Close_20MA as c on b.StockNo = c.StockNo and b.Date = c.Date
and ((cast(b.Close_5MA as float) / cast(c.Close_20MA as float) >=0.9)
or (cast(b.Close_5MA as float) / cast(c.Close_20MA as float) <=1.1))
join Close_55MA as d on b.StockNo = d.StockNo and b.Date = d.Date
and ((cast(b.Close_5MA as float) / cast(d.Close_55MA as float) >=0.9)
or (cast(b.Close_5MA as float) / cast(d.Close_55MA as float) <=1.1))

join Volume_5MA as e on a.StockNo = e.StockNo and a.Date = e.Date
and (cast(e.Volume_5MA as float) * 3 < cast(a.xVolume as float))

where a.xDate >= '20150101'
and cast(a.xVolume as float) > 1000
order by a.xDate , StockNo


紀錄: 9/11 跑出 2443,4526
過陣子再來看結果 XD

AVAST-Google Chrome 瀏覽器無法正常看到Yahoo首頁的圖片

Google Chrome 瀏覽器無法正常看到Yahoo首頁
經測試原來是 AVAST掃毒軟體在作怪

avast介面>>設定>>主動防護>>網頁防護>>自定>>取消勾選"啟用HTTPS掃描"

關閉瀏覽器再重新打開就正常囉

Python-如何利用Gmail發信

import smtplib
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText

def sendGmailSmtp(strGmailUser,strGmailPassword,strRecipient,strSubject,strContent):
    strMessage = MIMEMultipart()
    strMessage['From'] = strGmailUser
    strMessage['To'] = strRecipient
    strMessage['Subject'] = strSubject
    strMessage.attach(MIMEText(strContent))
    mailServer = smtplib.SMTP('smtp.gmail.com', 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()
    mailServer.login(strGmailUser, strGmailPassword)
    mailServer.sendmail(strGmailUser, strRecipient, strMessage.as_string())
    mailServer.close()
    return 'send successed'


print sendGmailSmtp('寄件人@gmail.com','Gmail應用程式密碼','收件人@gmail.com','subject','Gino Test')

Ps, 記得先至Gmail申請應用程式密碼，才能順利發信。

Python-字串處理語法

[Substring]

sTmp='2015-09-05'
sYY=sTmp[0:4]
sMM=sTmp[5:-3]
sDD=sTmp[8:]

---

[Trim]

左trim
info[0].lstrip()
右trim
info[0].rstrip()

---

[Replace]

tmpData = tmpData.replace('=','')
tmpData = tmpData.replace('\""','')
tmpData = tmpData.replace(' ','')

Stock-K線圖顏色含意

K線圖的紅黑K，取決於開盤與收盤價的高低

開盤價高於收盤價，開高走低，當日K線呈黑K
開盤價低於收盤價，開低走高，當日K限呈紅K
開盤至收盤上下震盪，收盤價與開盤價接近，產生上下影線,收十字線
開盤價高於收盤價，收黑十字線
開盤價低於收盤價，收紅十字線
開盤價收盤價持平，收黑十字線


成交量是紅棒綠棒，完全取決於收盤價是收漲收跌

收跌，成交量呈綠棒
收漲，成交量呈紅棒
收平，成交量呈白棒

ASP.NET - GridView流水號

參考位置:
http://blog.xuite.net/xiaolian/blog/46204394-%E5%9C%A8GridView%E4%B8%AD%E5%8A%A0%E5%85%A5%E8%87%AA%E5%8B%95%E7%B7%A8%E8%99%9F%E7%9A%84%E5%BA%8F%E8%99%9F
很實用的範例:

在Aspx檔案中GridView表格內插入一個欄位，在第一欄加上TemplateField的欄位，語法如下：

流水號從第一筆編起
  <asp:TemplateField HeaderText="序號">
          <ItemTemplate>
             <%#GridView1.PageIndex * GridView1.PageSize + GridView1.Rows.Count + 1%>
           </ItemTemplate>
           <HeaderStyle Wrap="False"  />
            <ItemStyle  HorizontalAlign="Center" VerticalAlign="Middle" />
  </asp:TemplateField>

OR

  <asp:TemplateField HeaderText="序號">
         <ItemTemplate>
            <%#Container.DataItemIndex + 1%>
         </ItemTemplate>
         <HeaderStyle Wrap="False"  />
         <ItemStyle  HorizontalAlign="Center" VerticalAlign="Middle" />
  </asp:TemplateField>


  流水號每頁從1編起
  <asp:TemplateField HeaderText="序號">
          <ItemTemplate>
             <%#Container.DisplayIndex + 1%>
           </ItemTemplate>
           <HeaderStyle Wrap="False"  />
           <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" />
  </asp:TemplateField>

DNN-初次聯網出現IIS 404/500 Error Message

初次聯網出現IIS 404/500 Error Message
解決方法:
http://www.dotnetnuke.com/Resources/Forums/forumid/89/postid/345924/scope/posts.aspx
主要修改Web.config

  <system.webServer>
    <modules runAllManagedModulesForAllRequests="true">
      <add name="RequestFilter" type="DotNetNuke.HttpModules.RequestFilter.RequestFilterModule, DotNetNuke.HttpModules" preCondition="managedHandler" />
      <add name="UrlRewrite" type="DotNetNuke.HttpModules.UrlRewriteModule, DotNetNuke.HttpModules" preCondition="managedHandler" />
      <add name="MobileRedirect" type="DotNetNuke.HttpModules.MobileRedirectModule, DotNetNuke.HttpModules" preCondition="managedHandler" />
      <add name="Exception" type="DotNetNuke.HttpModules.Exceptions.ExceptionModule, DotNetNuke.HttpModules" preCondition="managedHandler" />
      <add name="UsersOnline" type="DotNetNuke.HttpModules.UsersOnline.UsersOnlineModule, DotNetNuke.HttpModules" preCondition="managedHandler" />
      <add name="DNNMembership" type="DotNetNuke.HttpModules.Membership.MembershipModule, DotNetNuke.HttpModules" preCondition="managedHandler" />
      <add name="Personalization" type="DotNetNuke.HttpModules.Personalization.PersonalizationModule, DotNetNuke.HttpModules" preCondition="managedHandler" />
      <add name="Analytics" type="DotNetNuke.HttpModules.Analytics.AnalyticsModule, DotNetNuke.HttpModules" preCondition="managedHandler" />
      <add name="Services" type="DotNetNuke.HttpModules.Services.ServicesModule, DotNetNuke.HttpModules" />
      <remove name="UrlRoutingModule-4.0" />
      <add name="UrlRoutingModule-4.0" type="System.Web.Routing.UrlRoutingModule" preCondition="" />
      <add name="ClientDependencyModule" type="ClientDependency.Core.Module.ClientDependencyModule, ClientDependency.Core" />
      <add name="RadUploadModule" type="Telerik.Web.UI.RadUploadHttpModule, Telerik.Web.UI" preCondition="managedHandler" />
      <add name="Detector" type="FiftyOne.Foundation.Mobile.Detection.DetectorModule, FiftyOne.Foundation" preCondition="managedHandler" />
      <remove name="FormsAuthentication" />
      <add name="FormsAuthentication" type="Mvolo.Modules.FormsAuthModule" />


      <!--把這行註解，避免500 or 404 錯誤 -->
      <!--<add name="Authentication" type="DotNetNuke.Authentication.ActiveDirectory.HttpModules.AuthenticationModule, DotNetNuke.Authentication.ActiveDirectory" />-->
    </modules>

DNN-安裝注意事項

把下載下來的DNN install file
-->放置於C:\inetpub\wwwroot\DNN706
-->檔案屬性 取消唯讀

C:\inetpub\wwwroot\DNN706 檔案內容-->安全性
增加IIS_IUSER 修改權限
IIS --> DNN706目錄 (右鍵) 轉換為應用程式
IIS 勾選匿名驗證
設定應用程式集 --> 增加 名稱:DNN  /  .Net版本挑選4.0  /  整合式

---

開發環境設定

C:\windows\system32\drivers\etc\hosts
新增hostname
127.0.0.1     dnndev

IIS --> 站台 --> Default Web Site -->(右邊選單)
在 [站台繫結] 對話方塊中，選取要新增主機標頭的繫結，然後按一下 [編輯] 或按一下 [新增] 新增含有主機標頭的繫結。
在 [主機名稱] 方塊中，輸入站台的主機標頭，例如 dnndev。

SQL - 連兩週千張大戶增幅超過1%

select a.StockNo ,a.DATE '本週' , a.Rate '本週持股比' , b.Rate '上週持股比',
round((cast(a.Rate as float) - cast(b.Rate as float)),2) '增幅%'
from
(select *,id0=row_number()over(order by StockNo , Date) from Stockholder where Item = '1000001' )a,  --本周
(select *,id0=row_number()over(order by StockNo , Date) from Stockholder where Item = '1000001' )b,  --前一周
(select *,id0=row_number()over(order by StockNo , Date) from Stockholder where Item = '1000001' )c  --前兩周
where 1=1
and a.id0=b.id0+1
and b.id0=c.id0+1
and a.StockNo = b.StockNo
and a.StockNo = c.StockNo
and a.Date = '20150821'
and round((cast(a.Rate as float) - cast(b.Rate as float)),2) > 1
and round((cast(b.Rate as float) - cast(c.Rate as float)),2) > 1

Stock - 資料來源List

1.集保戶股權分散表查詢(千張大戶)
http://www.tdcc.com.tw/smWeb/QryStock.jsp

---

2.抓上市與上櫃各股每月營收
http://mops.twse.com.tw/nas/t21/sii/t21sc03_104_1_0.html

---

3.抓股票每日開盤收盤價
[Google]

https://www.google.com/finance/historical?q=TPE:2330&startdate=1/31/1986&start=30&num=30

[政府資料開放平台]-[上市]
http://data.gov.tw/iisi/logaccess/6007?dataUrl=http://www.twse.com.tw/ch/trading/exchange/STOCK_DAY_ALL/STOCK_DAY_ALL.php?download=csv&ndctype=CSV&ndcnid=11549

[上櫃每日收盤價]
http://www.tpex.org.tw/web/stock/aftertrading/otc_quotes_no1430/stk_wn1430_download.php?l=zh-tw&se=EW&s=0,asc,0

[凱基證券]
http://www.kgieworld.com.tw/stock/stock_2_7.aspx?findex=1

[Yahoo] -個股(歷史資料)
http://ichart.finance.yahoo.com/table.csv?s=1234.TW
註: 上市.TW / 上櫃 .TWO

---

[Yahoo]-盤中股價
http://download.finance.yahoo.com/d/quotes.csv?f=1d1t1c1bhgavp2&s=1234.TW
註: 上市.TW / 上櫃 .TWO

[Yahoo] 加權指數
http://download.finance.yahoo.com/d/quotes.csv?f=1d1t1hgvp2&s=^TWII

---

4.上市每日個股當沖量
http://www.twse.com.tw/ch/trading/exchange/TWTB4U/TWTB4U.php?input_date=104/09/15
5.上櫃每日個股當沖量 http://www.tpex.org.tw/web/stock/trading/intraday_stat/intraday_trading_stat_download.php?l=zh-tw&d=104/09/18&c=&s=0,asc,0

---

6.上市每日個股EPS
http://www.twse.com.tw/ch/trading/exchange/BWIBBU/BWIBBU_d.php
http://www.twse.com.tw/ch/trading/exchange/BWIBBU/BWIBBU_print.php?language=ch&save=csv
7.上櫃每日個股EPS
http://www.tpex.org.tw/web/stock/aftertrading/peratio_analysis/pera_download.php?l=zh-tw&d=104/09/28&c=&s=0,asc,0

python - 日期迴圈

在網路上撈取歷史資料網站時，時常要下時間條件，因此常用到時間迴圈
import datetime as dt
    startdate = dt.datetime(2015, 8,1)
    end_ate = dt.datetime(2015, 12,31)

    totaldays = (enddate - startdate).days + 1

    #在for迴圈內作業。
    for daynumber in range(totaldays):
        datestring = (startdate + dt.timedelta(days = daynumber)).date()
        #print datestring.strftime("%Y%m%d")
     

Python - 安裝步驟 for Win7

1.先在windows環境安裝python-2.7.8.msi
(下載位置: https://www.python.org/download/releases/2.7.8/)

2.把get-pip.py
放置於C:\Python27\目錄下
執行: python get-pip.py
(下載位置: https://pip.pypa.io/en/latest/installing.html)

3.安裝pymssql
c:\Python27\Scripts\pip install pymysql

4.安裝 beautifulsoup4
c:\Python27\Scripts\easy_install beautifulsoup4

5.安裝 requests
C:\Python27\Scripts>pip install requests

SQL-如何透過MS SQL Server寄信(Gmail)

1.可先參考
http://www.dotblogs.com.tw/michaelchen/archive/2015/01/11/database_mail_use_gmail_stmp.aspx
進行基本設定

2.設定完畢後，若無法正常寄信，先檢查 "Database Mail Server 紀錄"
若出現 "Gmail 無法正常寄信 5.5.1 Authentication Required. Learn more ..." 的錯誤訊息，表示Gmail 帳號驗證不正確
可參考 http://demo.tc/post/807 設定 "Gmail應用程式密碼"

3.設定完畢後，再重新設定SQL Server Database Mail 之設定即可。

SQL-爆量長紅

select W.StockNo , W.Date , W.xOpen '開盤價', W.xClose '收盤價', W.xHigh '最高價', W.xLow '最低價',
W.xVolume '成交量', X.Volume_20MA '20MA成交量', W.Rate '漲跌幅'
from StockDailyHis as W
join Volume_20MA as X on W.Date = X.Date and W.StockNo = X.StockNo
where cast(W.Rate as float) > 4 --漲4%以上
and cast(W.xVolume as float) > 1000 --成交量千張以上
and cast(W.xVolume as float) > cast(X.Volume_20MA as float) *3  --今日成交量超過3倍20日均量
and W.xClose > W.xOpen --紅K
order by W.Date , W.StockNo

SQL-20日平均量

select A.StockNo , A.Date , (
select round(avg(cast(B.xVolume as float)),2)  from
(select top 20 cast(C.xVolume as float) xVolume from StockDailyHis C
  where C.Date<=A.Date and C.StockNo = A.StockNo order by C.Date desc) B
) Volume_20MA from StockDailyHis A where
1=1 and A.Date >= '20140101'
group by A.StockNo, A.Date
order by A.StockNo , A.Date

SQL-個股每日漲跌幅

select a.StockNo ,a.DATE '今日' , a.xClose '今日收盤價' , b.xClose '昨日收盤價',
round(((cast(a.xClose as float) - cast(b.xClose as float))/ cast(a.xClose as float)) * 100,2) '漲跌幅'
from
 (select *,id0=row_number()over(order by StockNo , Date) from StockDailyHis )a,  --今天
 (select *,id0=row_number()over(order by StockNo , Date) from StockDailyHis )b   --昨天
where 1=1
and a.id0=b.id0+1
and a.StockNo = b.StockNo
and a.Date >= '20140101'

SQL-收盤價55日均線

select A.Date , (
select round(avg(cast(B.xClose as float)),2) from
(select top 55 cast(C.xclose as float) xClose from StockDailyHis C
  where C.Date<=A.Date and C.StockNo = A.StockNo order by C.Date desc) B
) from StockDailyHis A where StockNo = '1234'

SQL-股價撈取跳空邏輯

select a.StockNo ,a.DATE '今日' , a.xLow '今日最低', b.xHigh '昨日最高' , a.xClose '今日收盤價' , b.xClose '昨日收盤價'
from
 (select *,id0=row_number()over(order by StockNo , Date) from StockDailyHis )a,  --今天
 (select *,id0=row_number()over(order by StockNo , Date) from StockDailyHis )b   --昨天
where
a.id0=b.id0+1
and cast(a.xLow as decimal) > cast(b.xHigh as decimal)
and a.StockNo = b.StockNo
and a.xClose >= a.xOpen --紅K
and a.Date >= '20150701'
and a.Date <= '20150731'
order by a.StockNo , a.Date