public CommodityBasicList getCommodityBasics() { CommodityBasicList cBL = new CommodityBasicList(); try { string sql = "SELECT [股票代號], [股票名稱], isNull([上市上櫃],'1') 市場, IsNull([公司名稱], '') 公司名稱, IsNull([統一編號], '00000000') 統一編號 FROM [上市櫃公司基本資料] WHERE "; List <string> datas = new List <string>(); //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); for (; !rs.EOF; rs.MoveNext()) { string cid = Convert.ToString(rs.Fields["股票代號"].Value); string cnm = Convert.ToString(rs.Fields["股票名稱"].Value); string mktN = Convert.ToString(rs.Fields["市場"].Value); string mkt = ""; if (mktN == "1") { mkt = "TSE"; } else if (mktN == "2") { mkt = "OTC"; } else { mkt = ""; } string uid = Convert.ToString(rs.Fields["統一編號"].Value); string fnm = Convert.ToString(rs.Fields["公司名稱"].Value); if (uid != null && cid != null && cnm != null && mkt != null && fnm != null && uid != "00000000" && uid != "0" && uid != "" && !datas.Contains(uid)) { CommodityBasic b = new CommodityBasic(cid, cnm, mkt, uid, fnm); datas.Add(uid); cBL.add(b); } } } catch (Exception ex) { MessageBox.Show("GetCommodityBasics" + ex.Message); //GlobalVar.errProcess.Add(1, "[CMoneyWork_getCommodityBasics][" + ex.Message + "][" + ex.StackTrace + "]"); } return(cBL); }
private static 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.100.10.131].[WAFT].[dbo].[V_CANDIDATE] a * LEFT JOIN [10.100.10.131].[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.TraderAccount, 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 TraderAccount, 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', '2823') 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.100.10.131].[WAFT].[dbo].[CANDIDATE] as A WITH(NOLOCK) LEFT JOIN [10.100.10.131].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.100.10.131].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.100.10.131].WAFT.dbo.CANDIDATE WHERE WRTCAN_VER = 1) AND A.WRTCAN_VER = 1 and A.WRTCAN_IS_WARRANT_UNDERLYING = 1)as C WHERE C.CHECK_CAN_ISSUE = '1'", conn); MSSQL.ExecSqlCmd(@"Insert into [EDIS].[dbo].[WarrantUnderlying] (UnderlyingID, UnderlyingIDCMoney, UnderlyingName, TraderID, TraderName, StockType, FullName) SELECT [CommodityID], [CommodityID], CommodityID, '6387', 'Eric', 'DI', CommodityID FROM[EDIS].[dbo].[WarrantPrices] where CommodityId like 'TXF%' and len(CommodityId) = 5", 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.100.10.131].[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.100.10.131].[EXTSRC].[dbo].[V_WRT_ISSUE_QUOTA] WHERE [UnderlyingID]=B.[ISUQTA_STKID] COLLATE Chinese_Taiwan_Stroke_CI_AS)", conn); conn.Close(); string sql = "SELECT [股票代號], isNull([上市上櫃],'1') 市場, IsNull([統一編號], '00000000') 統一編號 FROM [上市櫃公司基本資料] WHERE "; //DataView dv = DeriLib.Util.ExecSqlQry("SELECT [UnderlyingIDCMoney] FROM [WarrantUnderlying] ORDER BY [UnderlyingIDCMoney]", LoginSet.edisSqlConnString); DataTable dv = MSSQL.ExecSqlQry("SELECT [UnderlyingIDCMoney] FROM [WarrantUnderlying] ORDER BY [UnderlyingIDCMoney]", 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(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); } }