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