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);
}
}