private void refreshCommodityBasics()
        {
            try {
                string sql = "SELECT [股票代號], [股票名稱], isNull([上市上櫃],'1') 市場, IsNull([公司名稱], '') 公司名稱, IsNull([統一編號], '00000000') 統一編號 FROM [上市櫃公司基本資料] WHERE ";

                //DataView dv = DeriLib.Util.ExecSqlQry("SELECT WRTCAN_CMONEY_ID FROM [V_CANDIDATE] ORDER BY WRTCAN_CMONEY_ID", GlobalVar.loginSet.warrantSysSqlConnString);
                DataTable dv   = MSSQL.ExecSqlQry("SELECT WRTCAN_CMONEY_ID FROM [CANDIDATE] WHERE WRTCAN_DATE = (select max(WRTCAN_DATE) from [WAFT].[dbo].[CANDIDATE]) ORDER BY WRTCAN_CMONEY_ID", GlobalVar.loginSet.warrantSysSqlConnString); // V_CANDIDATE
                string    cStr = "";
                foreach (DataRow dr in dv.Rows)
                {
                    cStr += "'" + dr["WRTCAN_CMONEY_ID"].ToString() + "',";
                }
                if (cStr.Length > 0)
                {
                    cStr = cStr.Substring(0, cStr.Length - 1);
                }

                sql += "[股票代號] IN (" + cStr + ") ORDER BY [股票代號]";
                ADODB.Recordset rs = cn.CMExecute(ref arg, srvLocation, cnPort, sql);

                string cmdText = "UPDATE [WarrantUnderlying] SET UnderlyingName=@UnderlyingName, Market=@Market, UnifiedID=@UnifiedID, FullName=@FullName WHERE UnderlyingID=@Underlying";
                List <System.Data.SqlClient.SqlParameter> pars = new List <System.Data.SqlClient.SqlParameter>();
                pars.Add(new System.Data.SqlClient.SqlParameter("@UnderlyingID", SqlDbType.VarChar));
                pars.Add(new System.Data.SqlClient.SqlParameter("@UnderlyingName", SqlDbType.VarChar));
                pars.Add(new System.Data.SqlClient.SqlParameter("@Market", SqlDbType.VarChar));
                pars.Add(new System.Data.SqlClient.SqlParameter("@FullName", SqlDbType.VarChar));
                pars.Add(new System.Data.SqlClient.SqlParameter("@UnifiedID", SqlDbType.VarChar));
                SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, cmdText, pars);


                for (; !rs.EOF; rs.MoveNext())
                {
                    string commodityID   = rs.Fields["股票代號"].Value;
                    string commodityName = rs.Fields["股票名稱"].Value;
                    string market        = rs.Fields["市場"].Value;
                    string unifiedID     = rs.Fields["統一編號"].Value;
                    string fullName      = rs.Fields["公司名稱"].Value;

                    h.SetParameterValue("@UnderlyingID", commodityID);
                    h.SetParameterValue("@UnderlyingName", commodityName);
                    h.SetParameterValue("@Market", market);
                    h.SetParameterValue("@UnifiedID", unifiedID);
                    h.SetParameterValue("@FullName", fullName);

                    h.ExecuteCommand();
                }
                h.Dispose();
            } catch (Exception ex) {
                MessageBox.Show("RefreshCommodityBasics" + ex.Message);
                //GlobalVar.errProcess.Add(1, "[CMoneyWork_refreshIssuableUnderlyingData][" + ex.Message + "][" + ex.StackTrace + "]");
            }
        }
        private void updateIssueCheck()
        {
            try {
                SQLCommandHelper deleteIssueCheck = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, "DELETE FROM [WarrantIssueCheck]", new List <System.Data.SqlClient.SqlParameter>());
                deleteIssueCheck.ExecuteCommand();
                deleteIssueCheck.Dispose();

                SQLCommandHelper deleteIssueCheckPut = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, "DELETE FROM [WarrantIssueCheckPut]", new List <System.Data.SqlClient.SqlParameter>());
                deleteIssueCheckPut.ExecuteCommand();
                deleteIssueCheckPut.Dispose();

                string sqlIssueCheck = "INSERT INTO [WarrantIssueCheck] values (@UnderlyingID, @UnderlyingName, @MDate, @CashDividend, @StockDividend, @CashDividendDate, @StockDividendDate, @PublicOfferingDate, @DisposeEndDate, @WatchCount, @WarningScore, @CanIssuePut, @AccNetIncome)";
                List <System.Data.SqlClient.SqlParameter> pars = new List <System.Data.SqlClient.SqlParameter>();

                pars.Add(new System.Data.SqlClient.SqlParameter("@UnderlyingID", SqlDbType.VarChar));
                pars.Add(new System.Data.SqlClient.SqlParameter("@UnderlyingName", SqlDbType.VarChar));
                pars.Add(new System.Data.SqlClient.SqlParameter("@MDate", SqlDbType.DateTime));
                pars.Add(new System.Data.SqlClient.SqlParameter("@CashDividend", SqlDbType.Float));
                pars.Add(new System.Data.SqlClient.SqlParameter("@StockDividend", SqlDbType.Float));
                pars.Add(new System.Data.SqlClient.SqlParameter("@CashDividendDate", SqlDbType.Date));
                pars.Add(new System.Data.SqlClient.SqlParameter("@StockDividendDate", SqlDbType.Date));
                pars.Add(new System.Data.SqlClient.SqlParameter("@PublicOfferingDate", SqlDbType.Date));
                pars.Add(new System.Data.SqlClient.SqlParameter("@DisposeEndDate", SqlDbType.Date));
                pars.Add(new System.Data.SqlClient.SqlParameter("@WatchCount", SqlDbType.Int));
                pars.Add(new System.Data.SqlClient.SqlParameter("@WarningScore", SqlDbType.Int));
                pars.Add(new System.Data.SqlClient.SqlParameter("@CanIssuePut", SqlDbType.VarChar));
                pars.Add(new System.Data.SqlClient.SqlParameter("@AccNetIncome", SqlDbType.Float));

                string sqlIssueCheckPut = "INSERT INTO [WarrantIssueCheckPut] values (@UnderlyingID, @UnderlyingName, @MDate, @IsTW50Stocks, @PERatio, @SumEarning, @Price, @PriceQuarter, @PriceYear, @ReturnQuarter, @ReturnYear)";
                List <System.Data.SqlClient.SqlParameter> parsPut = new List <System.Data.SqlClient.SqlParameter>();

                parsPut.Add(new System.Data.SqlClient.SqlParameter("@UnderlyingId", SqlDbType.VarChar));
                parsPut.Add(new System.Data.SqlClient.SqlParameter("@UnderlyingName", SqlDbType.VarChar));
                parsPut.Add(new System.Data.SqlClient.SqlParameter("@MDate", SqlDbType.DateTime));
                parsPut.Add(new System.Data.SqlClient.SqlParameter("@IsTW50Stocks", SqlDbType.VarChar));
                parsPut.Add(new System.Data.SqlClient.SqlParameter("@PERatio", SqlDbType.Float));
                parsPut.Add(new System.Data.SqlClient.SqlParameter("@SumEarning", SqlDbType.Float));
                parsPut.Add(new System.Data.SqlClient.SqlParameter("@Price", SqlDbType.Float));
                parsPut.Add(new System.Data.SqlClient.SqlParameter("@PriceQuarter", SqlDbType.Float));
                parsPut.Add(new System.Data.SqlClient.SqlParameter("@PriceYear", SqlDbType.Float));
                parsPut.Add(new System.Data.SqlClient.SqlParameter("@ReturnQuarter", SqlDbType.Float));
                parsPut.Add(new System.Data.SqlClient.SqlParameter("@ReturnYear", SqlDbType.Float));

                SQLCommandHelper insertIssueCheck    = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, sqlIssueCheck, pars);
                SQLCommandHelper insertIssueCheckPut = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, sqlIssueCheckPut, parsPut);

                foreach (CommodityData d in data.Values)
                {
                    d.checkPutIssueability();

                    insertIssueCheck.SetParameterValue("@UnderlyingID", d.commodityID);
                    insertIssueCheck.SetParameterValue("@UnderlyingName", d.commodityName);
                    insertIssueCheck.SetParameterValue("@MDate", DateTime.Now);
                    insertIssueCheck.SetParameterValue("@CashDividend", d.cashDividend);
                    insertIssueCheck.SetParameterValue("@StockDividend", d.stockDividend);
                    if (d.exCashDividendDate.ToString("yyyyMMdd") == "00010101")
                    {
                        insertIssueCheck.SetParameterValue("@CashDividendDate", null);
                    }
                    else
                    {
                        insertIssueCheck.SetParameterValue("@CashDividendDate", d.exCashDividendDate);
                    }

                    if (d.exStockDividendDate.ToString("yyyyMMdd") == "00010101")
                    {
                        insertIssueCheck.SetParameterValue("@StockDividendDate", null);
                    }
                    else
                    {
                        insertIssueCheck.SetParameterValue("@StockDividendDate", d.exStockDividendDate);
                    }

                    if (d.exPODate.ToString("yyyyMMdd") == "00010101")
                    {
                        insertIssueCheck.SetParameterValue("@PublicOfferingDate", null);
                    }
                    else
                    {
                        insertIssueCheck.SetParameterValue("@PublicOfferingDate", d.exPODate);
                    }

                    if (d.disposeEndDate.ToString("yyyyMMdd") == "00010101")
                    {
                        insertIssueCheck.SetParameterValue("@DisposeEndDate", null);
                    }
                    else
                    {
                        insertIssueCheck.SetParameterValue("@DisposeEndDate", d.disposeEndDate);
                    }

                    insertIssueCheck.SetParameterValue("@WatchCount", d.watchCount);
                    insertIssueCheck.SetParameterValue("@WarningScore", d.warningScore);
                    insertIssueCheck.SetParameterValue("@CanIssuePut", d.isPutIssuable ? "Y" : "N");
                    insertIssueCheck.SetParameterValue("@AccNetIncome", d.accNetIncome);

                    insertIssueCheckPut.SetParameterValue("@UnderlyingID", d.commodityID);
                    insertIssueCheckPut.SetParameterValue("@UnderlyingName", d.commodityName);
                    insertIssueCheckPut.SetParameterValue("@MDate", DateTime.Now);
                    insertIssueCheckPut.SetParameterValue("@IsTW50Stocks", d.isTW50Stocks ? "Y" : "N");
                    insertIssueCheckPut.SetParameterValue("@PERatio", d.peRatio);
                    insertIssueCheckPut.SetParameterValue("@SumEarning", d.commodityEarning.sumEarning);
                    insertIssueCheckPut.SetParameterValue("@Price", d.price);
                    insertIssueCheckPut.SetParameterValue("@PriceQuarter", d.priceQuarter);
                    insertIssueCheckPut.SetParameterValue("@PriceYear", d.priceYear);
                    insertIssueCheckPut.SetParameterValue("@ReturnQuarter", d.returnQuarter);
                    insertIssueCheckPut.SetParameterValue("@ReturnYear", d.returnYear);

                    insertIssueCheckPut.ExecuteCommand();
                    insertIssueCheck.ExecuteCommand();
                }

                insertIssueCheck.Dispose();
                insertIssueCheckPut.Dispose();
            } catch (Exception ex) {
                //GlobalVar.errProcess.Add(1, "[CMoneyWork_updateIssueCheck][" + ex.Message + "][" + ex.StackTrace + "]");
                MessageBox.Show("UpdateIssueCheck" + ex.ToString());
            }
        }
        private void insertWarrantUnderlying()
        {
            try {
                conn.Open();
                //更新可發行標的代號,標的名稱,交易員代號,交易員名稱,標的全名

                /*MSSQL.ExecSqlCmd(@"INSERT INTO [EDIS].[dbo].[WarrantUnderlying] (UnderlyingID, UnderlyingIDCMoney, UnderlyingName, TraderID, TraderName, StockType, FullName)
                 *                 SELECT a.[WRTCAN_STKID], a.[WRTCAN_CMONEY_ID], b.[FLGDAT_FLGDTA], ISNULL(c.[TraderAccount],'7643'), ISNULL(c.[TraderName],'Aaron'), a.[WRTCAN_STOCKTYPE], a.[WRTCAN_FULL_NAME]
                 *                 FROM [10.7.0.52].[WAFT].[dbo].[V_CANDIDATE] a
                 *                 LEFT JOIN [10.7.0.52].[WAFT].[dbo].[V_FLAGDATA_STOCK_UNDERLYING_NAME_LIST] b ON a.[WRTCAN_STKID]=b.[FLGDAT_FLGVAR]
                 *                 LEFT JOIN [10.19.1.20].[EDIS].[dbo].[Underlying_Trader] c ON a.[WRTCAN_STKID]=c.UID COLLATE Chinese_Taiwan_Stroke_CI_AS
                 *                 WHERE a.[WRTCAN_CAN_ISSUE]='1'", conn);*/
                // LEFT JOIN [10.10.1.30].[EDIS].[dbo].[Underlying_TraderIssue] c ON a.[WRTCAN_STKID]=c.UID COLLATE Chinese_Taiwan_Stroke_CI_AS
                MSSQL.ExecSqlCmd(@"INSERT INTO [EDIS].[dbo].[WarrantUnderlying] (UnderlyingID, UnderlyingIDCMoney, UnderlyingName, TraderID, TraderName, StockType, FullName) 
select C.WRTCAN_STKID, C.WRTCAN_CMONEY_ID, C.WRTCAN_SHORT_NAME, C.TraderAssount, C.TraderName, C.WRTCAN_STOCKTYPE, C.WRTCAN_FULL_NAME from 
(SELECT A.WRTCAN_STKID, A.WRTCAN_CMONEY_ID, A.WRTCAN_SHORT_NAME, ISNULL(B.TraderAccount,'7643') as TraderAssount, ISNULL(B.TraderName,'Aaron') as TraderName, A.WRTCAN_STOCKTYPE, A.WRTCAN_FULL_NAME,    
    CASE WHEN (WRTCAN_STOCKTYPE = 'DI' OR WRTCAN_STOCKTYPE = 'DE') AND (AUT.FLGDAT_FLGVAR is null OR AUT.FLGDAT_FLGVAR = 0 OR AUT.FLGDAT_FLGVAR < CONVERT(VARCHAR, GETDATE(), 112)) THEN '未授權'                       
    WHEN WRTCAN_STOCKTYPE = 'DS' AND A.WRTCAN_STKID IN('2883', '6005') THEN '未授權'
    WHEN (WRTCAN_STOCKTYPE = 'DS' OR WRTCAN_STOCKTYPE = 'DR') AND A.WRTCAN_SOURCE = 'STOCK_A' AND (C.FLGDAT_FLGVAR <> 'A' OR C.FLGDAT_FLGVAR is null) THEN '非A級券商'               
    ELSE '1'
    END as CHECK_CAN_ISSUE
FROM [10.7.0.52].[WAFT].[dbo].[CANDIDATE] as A WITH(NOLOCK)
LEFT JOIN  [10.7.0.52].EDAISYS.dbo.FLAGDATAS as AUT WITH(NOLOCK)
    ON WRTCAN_INSNBR = AUT.FLGDAT_FLGNBR AND AUT.FLGDAT_FLGNAM = 'WRT_AUTHORIZATION_MAINTAIN' AND AUT.FLGDAT_FLGNBR = A.WRTCAN_INSNBR 
LEFT JOIN  [10.7.0.52].EDAISYS.dbo.FLAGDATAS as C WITH (NOLOCK)
     ON C.FLGDAT_FLGNAM = 'WRT_MARKET_RATING' and  convert(varchar(10), GETDATE(), 112) between C.FLGDAT_FLGNBR and C.FLGDAT_ORDERS
LEFT JOIN [10.19.1.20].[EDIS].[dbo].[Underlying_Trader] as B ON A.[WRTCAN_STKID]=B.UID COLLATE Chinese_Taiwan_Stroke_CI_AS
WHERE A.WRTCAN_DATE = ( SELECT MAX(WRTCAN_DATE) FROM  [10.7.0.52].WAFT.dbo.CANDIDATE WHERE WRTCAN_VER = 1) AND A.WRTCAN_VER = 1)as C
WHERE C.CHECK_CAN_ISSUE = '1'", conn);


                //先預設市場是TSE,以免有些比對不到
                MSSQL.ExecSqlCmd("UPDATE [EDIS].[dbo].[WarrantUnderlying] SET [Market]='TSE'", conn);

                //先從權證系統找市場
                MSSQL.ExecSqlCmd(@"UPDATE [EDIS].[dbo].[WarrantUnderlying] 
                                   SET [Market]=substring(B.[ISUQTA_MKTTYPE],4,3) 
                                   FROM [10.7.0.52].[EXTSRC].[dbo].[V_WRT_ISSUE_QUOTA] B 
                                   WHERE [UnderlyingID]=B.[ISUQTA_STKID] COLLATE Chinese_Taiwan_Stroke_CI_AS AND B.[ISUQTA_DATE]=(SELECT MAX([ISUQTA_DATE]) FROM [10.7.0.52].[EXTSRC].[dbo].[V_WRT_ISSUE_QUOTA])", conn);
                conn.Close();

                string sql = "SELECT [股票代號], isNull([上市上櫃],'1') 市場, IsNull([統一編號], '00000000') 統一編號 FROM [上市櫃公司基本資料] WHERE ";
                //DataView dv = DeriLib.Util.ExecSqlQry("SELECT [UnderlyingIDCMoney] FROM [WarrantUnderlying] ORDER BY [UnderlyingIDCMoney]", GlobalVar.loginSet.edisSqlConnString);
                DataTable dv = MSSQL.ExecSqlQry("SELECT [UnderlyingIDCMoney] FROM [WarrantUnderlying] ORDER BY [UnderlyingIDCMoney]", GlobalVar.loginSet.edisSqlConnString);

                string cStr = "";
                foreach (DataRow dr in dv.Rows)
                {
                    cStr += "'" + dr["UnderlyingIDCMoney"].ToString() + "',";
                }
                if (cStr.Length > 0)
                {
                    cStr = cStr.Substring(0, cStr.Length - 1);
                }

                sql += "[股票代號] IN (" + cStr + ") ORDER BY [股票代號]";
                ADODB.Recordset rs = cn.CMExecute(ref arg, srvLocation, cnPort, sql);

                string cmdText = "UPDATE [WarrantUnderlying] SET UnifiedID=@UnifiedID WHERE UnderlyingIDCMoney=@UnderlyingIDCMoney";
                List <System.Data.SqlClient.SqlParameter> pars = new List <System.Data.SqlClient.SqlParameter>();
                pars.Add(new SqlParameter("@UnderlyingIDCMoney", SqlDbType.VarChar));
                //pars.Add(new SqlParameter("@Market", SqlDbType.VarChar));
                pars.Add(new SqlParameter("@UnifiedID", SqlDbType.VarChar));
                SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, cmdText, pars);

                for (; !rs.EOF; rs.MoveNext())
                {
                    string commodityIDCMoney = rs.Fields["股票代號"].Value;

                    /*
                     * string marketN = rs.Fields["市場"].Value;
                     * string market="";
                     * if (marketN=="1")
                     *  market="TSE";
                     * else if (marketN=="2")
                     *  market="OTC";
                     * else
                     *  market="";
                     */
                    string unifiedID = rs.Fields["統一編號"].Value;

                    h.SetParameterValue("@UnderlyingIDCMoney", commodityIDCMoney);
                    //h.SetParameterValue("@Market", market);
                    h.SetParameterValue("@UnifiedID", unifiedID);

                    h.ExecuteCommand();
                }
                h.Dispose();
            } catch (Exception ex) {
                MessageBox.Show(ex.Message);
            }
        }