private void UpdateData()
{
try {
MSSQL.ExecSqlCmd("DELETE FROM [ReIssueTempList] WHERE UserID='" + userID + "'", conn);
string sql = @"INSERT INTO [ReIssueTempList] (SerialNum, WarrantID, ReIssueNum, MarketTmr, ConfirmChecked, TraderID, MDate, UserID) ";
sql += "VALUES(@SerialNum, @WarrantID, @ReIssueNum, @MarketTmr, @ConfirmChecked, @TraderID, @MDate, @UserID)";
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(new SqlParameter("@SerialNum", SqlDbType.VarChar));
ps.Add(new SqlParameter("@WarrantID", SqlDbType.VarChar));
ps.Add(new SqlParameter("@ReIssueNum", SqlDbType.Float));
ps.Add(new SqlParameter("@MarketTmr", SqlDbType.VarChar));
ps.Add(new SqlParameter("@ConfirmChecked", SqlDbType.VarChar));
ps.Add(new SqlParameter("@TraderID", SqlDbType.VarChar));
ps.Add(new SqlParameter("@MDate", SqlDbType.DateTime));
ps.Add(new SqlParameter("@UserID", SqlDbType.VarChar));
SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, sql, ps);
int i = 1;
applyCount = 0;
foreach (UltraGridRow r in ultraGrid1.Rows)
{
string serialNumber = DateTime.Today.ToString("yyyyMMdd") + userID + "02" + i.ToString("0#");
string warrantID = r.Cells["WarrantID"].Value.ToString();
double reIssueNum = Convert.ToDouble(r.Cells["ReIssueNum"].Value);
string marketTmr = r.Cells["MarketTmr"].Value == DBNull.Value ? "Y" : r.Cells["MarketTmr"].Value.ToString();
string traderID = r.Cells["TraderID"].Value == DBNull.Value ? userID : r.Cells["TraderID"].Value.ToString();
bool confirmed = false;
confirmed = r.Cells["ConfirmChecked"].Value == DBNull.Value ? false : Convert.ToBoolean(r.Cells["ConfirmChecked"].Value);
string confirmChecked = "N";
if (confirmed)
{
confirmChecked = "Y";
applyCount++;
}
else
{
confirmChecked = "N";
}
h.SetParameterValue("@SerialNum", serialNumber);
h.SetParameterValue("@WarrantID", warrantID);
h.SetParameterValue("@ReIssueNum", reIssueNum);
h.SetParameterValue("@MarketTmr", marketTmr);
h.SetParameterValue("@ConfirmChecked", confirmChecked);
h.SetParameterValue("@TraderID", traderID);
h.SetParameterValue("@MDate", DateTime.Now);
h.SetParameterValue("@UserID", userID);
h.ExecuteCommand();
i++;
}
h.Dispose();
GlobalUtility.LogInfo("Log", GlobalVar.globalParameter.userID + " 編輯/更新" + (i - 1) + "檔增額");
} catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
private void 修正權證名稱ToolStripMenuItem_Click(object sender, EventArgs e)
{
string sql5 = "SELECT [SerialNum], [WarrantName] FROM [EDIS].[dbo].[ApplyTotalList] WHERE [ApplyKind]='1'";
System.Data.DataTable dv = MSSQL.ExecSqlQry(sql5, GlobalVar.loginSet.edisSqlConnString);
string cmdText = "UPDATE [ApplyTotalList] SET WarrantName=@WarrantName WHERE SerialNum=@SerialNum";
List <System.Data.SqlClient.SqlParameter> pars = new List <System.Data.SqlClient.SqlParameter>();
pars.Add(new SqlParameter("@WarrantName", SqlDbType.VarChar));
pars.Add(new SqlParameter("@SerialNum", SqlDbType.VarChar));
SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, cmdText, pars);
bool updated = false;
foreach (DataRow dr in dv.Rows)
{
string serialNum = dr["SerialNum"].ToString();
string warrantName = dr["WarrantName"].ToString();
string sqlTemp = "select top (1) WarrantName from (SELECT [WarrantName] FROM [EDIS].[dbo].[WarrantBasic] WHERE SUBSTRING(WarrantName,1,(len(WarrantName)-3))='" + warrantName.Substring(0, warrantName.Length - 3) + "' union ";
sqlTemp += " SELECT [WarrantName] FROM [EDIS].[dbo].[ApplyTotalList] WHERE [ApplyKind]='1' AND [SerialNum]<" + serialNum + " AND SUBSTRING(WarrantName,1,(len(WarrantName)-3))='" + warrantName.Substring(0, warrantName.Length - 3) + "') as tb1 ";
sqlTemp += " order by SUBSTRING(WarrantName,len(WarrantName)-1,len(WarrantName)) desc";
System.Data.DataTable dvTemp = MSSQL.ExecSqlQry(sqlTemp, GlobalVar.loginSet.edisSqlConnString);
int count = 0;
if (dvTemp.Rows.Count > 0)
{
string lastWarrantName = dvTemp.Rows[0][0].ToString();
if (!int.TryParse(lastWarrantName.Substring(lastWarrantName.Length - 2, 2), out count))
{
MessageBox.Show("parse failed " + lastWarrantName);
}
}
if (warrantName.Substring(warrantName.Length - 2, 2) != (count + 1).ToString("0#"))
{
updated = true;
warrantName = warrantName.Substring(0, warrantName.Length - 2) + (count + 1).ToString("0#");
h.SetParameterValue("@WarrantName", warrantName);
h.SetParameterValue("@SerialNum", serialNum);
h.ExecuteCommand();
}
}
h.Dispose();
if (updated)
{
MessageBox.Show("Magic!");
}
else
{
MessageBox.Show("No magic.");
}
}
/*public static string GetHtml(string url) {
* string firstResponse = null;
* try {
* WebRequest req = WebRequest.Create(url);
*
* WebResponse resp = req.GetResponse();
* Stream dataStream = resp.GetResponseStream();
* StreamReader reader = new StreamReader(dataStream, System.Text.Encoding.Default);
* firstResponse = reader.ReadToEnd();
*
* //Close connection
* req.Abort();
* resp.Close();
* dataStream.Close();
* reader.Close();
* } catch (Exception err) {
* MessageBox.Show(err.ToString());
* }
* return firstResponse;
* }*/
public static void LogInfo(string type, string content)
{
string sqlInfo = "INSERT INTO [InformationLog] ([MDate],[InformationType],[InformationContent],[MUser]) values(@MDate, @InformationType, @InformationContent, @MUser)";
List <SqlParameter> psInfo = new List <SqlParameter> {
new SqlParameter("@MDate", SqlDbType.DateTime),
new SqlParameter("@InformationType", SqlDbType.VarChar),
new SqlParameter("@InformationContent", SqlDbType.VarChar),
new SqlParameter("@MUser", SqlDbType.VarChar)
};
SQLCommandHelper hInfo = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, sqlInfo, psInfo);
hInfo.SetParameterValue("@MDate", DateTime.Now);
hInfo.SetParameterValue("@InformationType", type);
hInfo.SetParameterValue("@InformationContent", content);
hInfo.SetParameterValue("@MUser", GlobalVar.globalParameter.userID);
hInfo.ExecuteCommand();
hInfo.Dispose();
}
private void UpdateData()
{
try {
string cmdText = "UPDATE [ApplyTotalList] SET WarrantName=@WarrantName, CR=@CR, IssueNum=@IssueNum, EquivalentNum=@EquivalentNum, Result=@Result, UseReward=@UseReward WHERE SerialNum=@SerialNum";
List <System.Data.SqlClient.SqlParameter> pars = new List <SqlParameter>();
pars.Add(new SqlParameter("@WarrantName", SqlDbType.VarChar));
pars.Add(new SqlParameter("@CR", SqlDbType.Float));
pars.Add(new SqlParameter("@IssueNum", SqlDbType.Float));
pars.Add(new SqlParameter("@EquivalentNum", SqlDbType.Float));
pars.Add(new SqlParameter("@Result", SqlDbType.Float));
pars.Add(new SqlParameter("@SerialNum", SqlDbType.VarChar));
pars.Add(new SqlParameter("@UseReward", SqlDbType.VarChar));
SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, cmdText, pars);
foreach (Infragistics.Win.UltraWinGrid.UltraGridRow r in ultraGrid1.Rows)
{
string warrantName = r.Cells["WarrantName"].Value.ToString();
double cr = r.Cells["CR"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["CR"].Value);
double issueNum = r.Cells["IssueNum"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["IssueNum"].Value);
double equivalentNum = cr * issueNum;
double result = r.Cells["Result"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["Result"].Value);
string useReward = r.Cells["UseReward"].Value.ToString();
string serialNum = r.Cells["SerialNum"].Value.ToString();
h.SetParameterValue("@WarrantName", warrantName);
h.SetParameterValue("@CR", cr);
h.SetParameterValue("@IssueNum", issueNum);
h.SetParameterValue("@EquivalentNum", equivalentNum);
h.SetParameterValue("@Result", result);
h.SetParameterValue("@UseReward", useReward);
h.SetParameterValue("@SerialNum", serialNum);
h.ExecuteCommand();
}
h.Dispose();
string cmdText2 = "UPDATE [ApplyOfficial] SET R=@R, IssueNum=@IssueNum, UseReward=@UseReward WHERE SerialNumber=@SerialNumber";
List <SqlParameter> pars2 = new List <SqlParameter>();
pars2.Add(new SqlParameter("@R", SqlDbType.Float));
pars2.Add(new SqlParameter("@IssueNum", SqlDbType.Float));
pars2.Add(new SqlParameter("@SerialNumber", SqlDbType.VarChar));
pars2.Add(new SqlParameter("@UseReward", SqlDbType.VarChar));
SQLCommandHelper h2 = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, cmdText2, pars2);
foreach (Infragistics.Win.UltraWinGrid.UltraGridRow r in ultraGrid1.Rows)
{
double cr = r.Cells["CR"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["CR"].Value);
double issueNum = r.Cells["IssueNum"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["IssueNum"].Value);
string serialNumber = r.Cells["SerialNum"].Value.ToString();
string useReward = r.Cells["UseReward"].Value.ToString();
h2.SetParameterValue("@R", cr);
h2.SetParameterValue("@IssueNum", issueNum);
h2.SetParameterValue("@SerialNumber", serialNumber);
h2.SetParameterValue("@UseReward", useReward);
h2.ExecuteCommand();
}
h2.Dispose();
GlobalUtility.LogInfo("Info", GlobalVar.globalParameter.userID + " 更新搶額度總表");
} catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
private void UpdateDB()
{
for (int x = ultraGrid1.Rows.Count - 1; x >= 0; x--)
{
try {
if (ultraGrid1.Rows[x].Cells[0].Value.ToString() == "")
{
ultraGrid1.Rows[x].Delete(false);
}
} catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
MSSQL.ExecSqlCmd("DELETE FROM [Apply_71]", conn);
try {
string sql = "INSERT INTO [Apply_71] values(@Issuer,@WarrantName,@UnderlyingID,@IssueNum,@exeRatio,@ApplyTime,@AvailableShares,@LastDayUsedShares,@TodayApplyShares,@AccUsedShares,@SameUnderlying,@OriApplyTime,@Result, @ApplyStatus, @ReIssueResult, @SerialNum)";
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(new SqlParameter("@Issuer", SqlDbType.VarChar));
ps.Add(new SqlParameter("@WarrantName", SqlDbType.VarChar));
ps.Add(new SqlParameter("@UnderlyingID", SqlDbType.VarChar));
ps.Add(new SqlParameter("@IssueNum", SqlDbType.Float));
ps.Add(new SqlParameter("@exeRatio", SqlDbType.Float));
ps.Add(new SqlParameter("@ApplyTime", SqlDbType.VarChar));
ps.Add(new SqlParameter("@AvailableShares", SqlDbType.Float));
ps.Add(new SqlParameter("@LastDayUsedShares", SqlDbType.Float));
ps.Add(new SqlParameter("@TodayApplyShares", SqlDbType.Float));
ps.Add(new SqlParameter("@AccUsedShares", SqlDbType.VarChar));
ps.Add(new SqlParameter("@SameUnderlying", SqlDbType.VarChar));
ps.Add(new SqlParameter("@OriApplyTime", SqlDbType.VarChar));
ps.Add(new SqlParameter("@Result", SqlDbType.Float));
ps.Add(new SqlParameter("@ApplyStatus", SqlDbType.VarChar));
ps.Add(new SqlParameter("@ReIssueResult", SqlDbType.Float));
ps.Add(new SqlParameter("@SerialNum", SqlDbType.VarChar));
SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, sql, ps);
foreach (UltraGridRow r in ultraGrid1.Rows)
{
string issuer = r.Cells["Issuer"].Value.ToString();
string warrantName = r.Cells["WarrantName"].Value.ToString();
string underlyingID = r.Cells["UnderlyingID"].Value.ToString();
double issueNum = Convert.ToDouble(r.Cells["IssueNum"].Value);
double exeRatio = Convert.ToDouble(r.Cells["exeRatio"].Value);
string applyTime = r.Cells["ApplyTime"].Value.ToString();
double availableShares = 0.0;
double lastDayUsedShares = 0.0;
double todayApplyShares = 0.0;
//if (underlyingID == "IX0001") {
if (char.IsLetter(underlyingID[0]))
{
availableShares = 0.0;
lastDayUsedShares = 0.0;
todayApplyShares = 0.0;
}
else
{
availableShares = Convert.ToDouble(r.Cells["AvailableShares"].Value);
lastDayUsedShares = Convert.ToDouble(r.Cells["LastDayUsedShares"].Value);
todayApplyShares = Convert.ToDouble(r.Cells["TodayApplyShares"].Value);
}
string accUsedShares = r.Cells["AccUsedShares"].Value.ToString();
string sameUnderlying = r.Cells["SameUnderlying"].Value.ToString();
string oriApplyTime = r.Cells["OriApplyTime"].Value.ToString();
//string underlyingName = warrantName.Substring(1, warrantName.Length - 7);//需考慮以前的短權證名稱
double multiplier = 1.0;
string sqlTemp = "SELECT CASE WHEN [StockType]='DS' OR [StockType]='DR' THEN 0.22 ELSE 1 END AS Multiplier FROM [EDIS].[dbo].[WarrantUnderlying] WHERE UnderlyingID = '" + underlyingID + "'";
//DataView dv = DeriLib.Util.ExecSqlQry(sqlTemp, GlobalVar.loginSet.edisSqlConnString);
DataTable dv = MSSQL.ExecSqlQry(sqlTemp, GlobalVar.loginSet.edisSqlConnString);
foreach (DataRow dr in dv.Rows)
{
multiplier = Convert.ToDouble(dr["Multiplier"]);
}
double todayAvailable = Math.Round(((availableShares * multiplier - lastDayUsedShares) / 1000), 1);
double attempShares = issueNum * exeRatio;
double result = 0.0;
double tempAvailable = 0.0;
string applyStatus = "";
tempAvailable = todayAvailable - todayApplyShares / 1000 + attempShares;
//if (underlyingID == "IX0001") {
if (char.IsLetter(underlyingID[0]))
{
result = attempShares;
applyStatus = "Y";
}
else if (applyTime.Substring(0, 2) == "09" || applyTime.Substring(0, 2) == "10")
{
if (tempAvailable >= attempShares)
{
result = attempShares;
applyStatus = "Y";
}
else if (tempAvailable > 0)
{
result = tempAvailable;
applyStatus = "排隊中";
}
else
{
result = 0;
if (todayAvailable >= 0.6 * attempShares)
{
applyStatus = "排隊中";
}
else
{
applyStatus = "X 沒額度";
}
}
}
else if (applyTime.Substring(0, 2) == "22")
{
result = 0;
applyStatus = "X 沒額度";
}
double accUsed = (lastDayUsedShares + todayApplyShares) / availableShares;
double reIssueResult = 0.0;
if (accUsed <= 0.3 || underlyingID.StartsWith("00"))
{
reIssueResult = attempShares;
}
h.SetParameterValue("@Issuer", issuer);
h.SetParameterValue("@WarrantName", warrantName);
h.SetParameterValue("@UnderlyingID", underlyingID);
h.SetParameterValue("@IssueNum", issueNum);
h.SetParameterValue("@exeRatio", exeRatio);
h.SetParameterValue("@ApplyTime", applyTime);
h.SetParameterValue("@AvailableShares", availableShares);
h.SetParameterValue("@LastDayUsedShares", lastDayUsedShares);
h.SetParameterValue("@TodayApplyShares", todayApplyShares);
h.SetParameterValue("@AccUsedShares", accUsedShares);
h.SetParameterValue("@SameUnderlying", sameUnderlying);
h.SetParameterValue("@OriApplyTime", oriApplyTime);
h.SetParameterValue("@Result", result);
h.SetParameterValue("@ApplyStatus", applyStatus);
h.SetParameterValue("@ReIssueResult", reIssueResult);
h.SetParameterValue("@SerialNum", "0");
h.ExecuteCommand();
}
h.Dispose();
string sql5 = "UPDATE [EDIS].[dbo].[Apply_71] SET SerialNum = B.SerialNum FROM [EDIS].[dbo].[ApplyTotalList] B WHERE [Apply_71].[WarrantName]=B.WarrantName";
string sql2 = "UPDATE [EDIS].[dbo].[ApplyTotalList] SET Result=0";
string sql3 = @"UPDATE [EDIS].[dbo].[ApplyTotalList]
SET Result= CASE WHEN ApplyKind='1' THEN B.Result ELSE B.ReIssueResult END
FROM [EDIS].[dbo].[Apply_71] B
WHERE [ApplyTotalList].[WarrantName]=B.WarrantName";
string sql4 = @"UPDATE [EDIS].[dbo].[ApplyTotalList]
SET Result= CASE WHEN [RewardCredit]>=[EquivalentNum] THEN [EquivalentNum] ELSE [RewardCredit] END
WHERE [UseReward]='Y'";
conn.Open();
MSSQL.ExecSqlCmd(sql5, conn);
MSSQL.ExecSqlCmd(sql2, conn);
MSSQL.ExecSqlCmd(sql3, conn);
MSSQL.ExecSqlCmd(sql4, conn);
conn.Close();
toolStripLabel1.Text = DateTime.Now + "更新成功";
GlobalUtility.LogInfo("Info", GlobalVar.globalParameter.userID + " 更新7-1試算表");
} catch (Exception ex) {
GlobalUtility.LogInfo("Exception", GlobalVar.globalParameter.userID + "7-1試算表" + ex.Message);
MessageBox.Show(ex.Message);
}
}
private void 加到申請編輯表ToolStripMenuItem_Click(object sender, EventArgs e)
{
string sqlTemp = "SELECT * FROM [EDIS].[dbo].[ApplyTempList] WHERE UserID='" + userID + "'";
DataView dvTemp = DeriLib.Util.ExecSqlQry(sqlTemp, GlobalVar.loginSet.edisSqlConnString);
int count = dvTemp.Count;
string serialNum = DateTime.Today.ToString("yyyyMMdd") + userID + "01" + (count + 1).ToString("0#");
string underlyingID = ultraGrid1.ActiveRow.Cells["標的代號"].Value.ToString();
string underlyingName = ultraGrid1.ActiveRow.Cells["標的名稱"].Value.ToString();
string WID = ultraGrid1.ActiveRow.Cells["權證代號"].Value.ToString();
double k = Convert.ToDouble(ultraGrid1.ActiveRow.Cells["K"].Value);
int t = Convert.ToInt32(ultraGrid1.ActiveRow.Cells["T"].Value);
double cr = Convert.ToDouble(ultraGrid1.ActiveRow.Cells["行使比例"].Value);
double hv = Convert.ToDouble(ultraGrid1.ActiveRow.Cells["HV"].Value);
double iv = Convert.ToDouble(ultraGrid1.ActiveRow.Cells["IV"].Value);
double issueNum = 10000.0;
double resetR = Convert.ToDouble(ultraGrid1.ActiveRow.Cells["重設比"].Value);
double barrierR = Convert.ToDouble(ultraGrid1.ActiveRow.Cells["界限比"].Value);
double financialR = Convert.ToDouble(ultraGrid1.ActiveRow.Cells["財務費用"].Value);
string type = ultraGrid1.ActiveRow.Cells["型態"].Value.ToString();
string cp = ultraGrid1.ActiveRow.Cells["CP"].Value.ToString();
string useReward = "N";
string confirm = "N";
string is1500W = "N";
string tempName = "";
string traderID = ultraGrid1.ActiveRow.Cells["交易員"].Value.ToString().PadLeft(7, '0');
DateTime expiryDate;
expiryDate = GlobalVar.globalParameter.nextTradeDate3.AddMonths(t);
expiryDate = expiryDate.AddDays(-1);
string sqlTemp2 = "SELECT TOP 1 TradeDate from TradeDate WHERE IsTrade='Y' AND TradeDate >= '" + expiryDate.ToString("yyyy-MM-dd") + "'";
DataView dvTemp2 = DeriLib.Util.ExecSqlQry(sqlTemp2, GlobalVar.loginSet.tsquoteSqlConnString);
foreach (DataRowView drTemp in dvTemp2)
{
expiryDate = Convert.ToDateTime(drTemp["TradeDate"]);
}
string expiryMonth = "";
int month = expiryDate.Month;
if (month >= 10)
{
if (month == 10)
{
expiryMonth = "A";
}
if (month == 11)
{
expiryMonth = "B";
}
if (month == 12)
{
expiryMonth = "C";
}
}
else
{
expiryMonth = month.ToString();
}
string expiryYear = "";
expiryYear = expiryDate.AddYears(-1).ToString("yyyy");
expiryYear = expiryYear.Substring(expiryYear.Length - 1, 1);
string warrantType = "";
string tempType = "";
if (type == "牛熊證")
{
if (cp == "P")
{
warrantType = "熊";
tempType = "4";
}
else
{
warrantType = "牛";
tempType = "3";
}
}
else
{
if (cp == "P")
{
warrantType = "售";
tempType = "2";
}
else
{
warrantType = "購";
tempType = "1";
}
}
tempName = underlyingName + "凱基" + expiryYear + expiryMonth + warrantType;
string sqlTemp3 = @"INSERT INTO [ApplyTempList] (SerialNum, UnderlyingID, K, T, R, HV, IV, IssueNum, ResetR, BarrierR, FinancialR, Type, CP, UseReward, ConfirmChecked, Apply1500W, UserID, MDate, TempName, TempType, TraderID) ";
sqlTemp3 += "VALUES(@SerialNum, @UnderlyingID, @K, @T, @R, @HV, @IV, @IssueNum, @ResetR, @BarrierR, @FinancialR, @Type, @CP, @UseReward, @ConfirmChecked, @Apply1500W, @UserID, @MDate, @TempName ,@TempType, @TraderID)";
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(new SqlParameter("@SerialNum", SqlDbType.VarChar));
ps.Add(new SqlParameter("@UnderlyingID", SqlDbType.VarChar));
ps.Add(new SqlParameter("@K", SqlDbType.Float));
ps.Add(new SqlParameter("@T", SqlDbType.Int));
ps.Add(new SqlParameter("@R", SqlDbType.Float));
ps.Add(new SqlParameter("@HV", SqlDbType.Float));
ps.Add(new SqlParameter("@IV", SqlDbType.Float));
ps.Add(new SqlParameter("@IssueNum", SqlDbType.Float));
ps.Add(new SqlParameter("@ResetR", SqlDbType.Float));
ps.Add(new SqlParameter("@BarrierR", SqlDbType.Float));
ps.Add(new SqlParameter("@FinancialR", SqlDbType.Float));
ps.Add(new SqlParameter("@Type", SqlDbType.VarChar));
ps.Add(new SqlParameter("@CP", SqlDbType.VarChar));
ps.Add(new SqlParameter("@UseReward", SqlDbType.VarChar));
ps.Add(new SqlParameter("@ConfirmChecked", SqlDbType.VarChar));
ps.Add(new SqlParameter("@Apply1500W", SqlDbType.VarChar));
ps.Add(new SqlParameter("@UserID", SqlDbType.VarChar));
ps.Add(new SqlParameter("@MDate", SqlDbType.DateTime));
ps.Add(new SqlParameter("@TempName", SqlDbType.VarChar));
ps.Add(new SqlParameter("@TempType", SqlDbType.VarChar));
ps.Add(new SqlParameter("@TraderID", SqlDbType.VarChar));
SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, sqlTemp3, ps);
h.SetParameterValue("@SerialNum", serialNum);
h.SetParameterValue("@UnderlyingID", underlyingID);
h.SetParameterValue("@K", k);
h.SetParameterValue("@T", t);
h.SetParameterValue("@R", cr);
h.SetParameterValue("@HV", hv);
h.SetParameterValue("@IV", iv);
h.SetParameterValue("@IssueNum", issueNum);
h.SetParameterValue("@ResetR", resetR);
h.SetParameterValue("@BarrierR", barrierR);
h.SetParameterValue("@FinancialR", financialR);
h.SetParameterValue("@Type", type);
h.SetParameterValue("@CP", cp);
h.SetParameterValue("@UseReward", useReward);
h.SetParameterValue("@ConfirmChecked", confirm);
h.SetParameterValue("@Apply1500W", is1500W);
h.SetParameterValue("@UserID", userID);
h.SetParameterValue("@MDate", DateTime.Now);
h.SetParameterValue("@TempName", tempName);
h.SetParameterValue("@TempType", tempType);
h.SetParameterValue("@TraderID", traderID);
h.ExecuteCommand();
h.Dispose();
GlobalUtility.LogInfo("Log", GlobalVar.globalParameter.userID + " " + WID + " 右鍵新增 " + underlyingID + underlyingName + " 一檔權證");
MessageBox.Show("Done!");
}
private void UpdateData()
{
try {
string cmdText = "UPDATE [ApplyOfficial] SET K=@K, T=@T, HV=@HV, IV=@IV, ResetR=@ResetR, BarrierR=@BarrierR, FinancialR=@FinancialR, Type=@Type, CP=@CP, Apply1500W=@Apply1500W, MDate=@MDate WHERE SerialNumber=@SerialNumber";
List <SqlParameter> pars = new List <SqlParameter>();
pars.Add(new SqlParameter("@K", SqlDbType.Float));
pars.Add(new SqlParameter("@T", SqlDbType.Int));
pars.Add(new SqlParameter("@HV", SqlDbType.Float));
pars.Add(new SqlParameter("@IV", SqlDbType.Float));
pars.Add(new SqlParameter("@ResetR", SqlDbType.Float));
pars.Add(new SqlParameter("@BarrierR", SqlDbType.Float));
pars.Add(new SqlParameter("@FinancialR", SqlDbType.Float));
pars.Add(new SqlParameter("@Type", SqlDbType.VarChar));
pars.Add(new SqlParameter("@CP", SqlDbType.VarChar));
pars.Add(new SqlParameter("@Apply1500W", SqlDbType.VarChar));
//pars.Add(new SqlParameter("@TraderID", SqlDbType.VarChar));
pars.Add(new SqlParameter("@MDate", SqlDbType.DateTime));
pars.Add(new SqlParameter("@SerialNumber", SqlDbType.VarChar));
SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, cmdText, pars);
foreach (Infragistics.Win.UltraWinGrid.UltraGridRow r in ultraGrid1.Rows)
{
double k = r.Cells["履約價"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["履約價"].Value);
double t = r.Cells["期間"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["期間"].Value);
double hv = r.Cells["HV"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["HV"].Value);
double iv = 0.0;
double resetR = r.Cells["重設比"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["重設比"].Value);
double barrierR = r.Cells["界限比"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["界限比"].Value);
double financialR = r.Cells["財務費用"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["財務費用"].Value);
string type = r.Cells["類型"].Value.ToString();
string cp = r.Cells["CP"].Value.ToString();
string apply1500w = r.Cells["1500W"].Value.ToString();
string serialNumber = r.Cells["序號"].Value.ToString();
//string traderID = "000"+r.Cells["交易員"].Value.ToString();
if (apply1500w == "Y")
{
iv = r.Cells["IVOri"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["IVOri"].Value);
}
else
{
iv = r.Cells["IV"].Value == DBNull.Value ? 0 : Convert.ToDouble(r.Cells["IV"].Value);
}
h.SetParameterValue("@K", k);
h.SetParameterValue("@T", t);
h.SetParameterValue("@HV", hv);
h.SetParameterValue("@IV", iv);
h.SetParameterValue("@ResetR", resetR);
h.SetParameterValue("@BarrierR", barrierR);
h.SetParameterValue("@FinancialR", financialR);
h.SetParameterValue("@Type", type);
h.SetParameterValue("@CP", cp);
h.SetParameterValue("@Apply1500W", apply1500w);
//h.SetParameterValue("@TraderID", traderID);
h.SetParameterValue("@MDate", DateTime.Now);
h.SetParameterValue("@SerialNumber", serialNumber);
h.ExecuteCommand();
}
h.Dispose();
GlobalUtility.LogInfo("Info", GlobalVar.globalParameter.userID + " 更新發行總表");
} catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
private void UpdateDB()
{
for (int x = ultraGrid1.Rows.Count - 1; x >= 0; x--)
{
try {
if (ultraGrid1.Rows[x].Cells[1].Value.ToString() == "")
{
ultraGrid1.Rows[x].Delete(false);
}
} catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
EDLib.SQL.MSSQL.ExecSqlCmd("DELETE FROM [WarrantReIssuable]", conn);
try {
string sql = "INSERT INTO [WarrantReIssuable] values(@WarrantID,@WarrantName,@IssueNum,@SoldNum,@Last1Sold,@Last2Sold,@Last3Sold,@LastTradingDate,@ReIssuable,@MDate)";
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(new SqlParameter("@WarrantID", SqlDbType.VarChar));
ps.Add(new SqlParameter("@WarrantName", SqlDbType.VarChar));
ps.Add(new SqlParameter("@IssueNum", SqlDbType.Float));
ps.Add(new SqlParameter("@SoldNum", SqlDbType.Float));
ps.Add(new SqlParameter("@Last1Sold", SqlDbType.Float));
ps.Add(new SqlParameter("@Last2Sold", SqlDbType.Float));
ps.Add(new SqlParameter("@Last3Sold", SqlDbType.Float));
ps.Add(new SqlParameter("@LastTradingDate", SqlDbType.VarChar));
ps.Add(new SqlParameter("@ReIssuable", SqlDbType.VarChar));
ps.Add(new SqlParameter("@MDate", SqlDbType.DateTime));
SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, sql, ps);
foreach (Infragistics.Win.UltraWinGrid.UltraGridRow r in ultraGrid1.Rows)
{
string warrantID = r.Cells["WarrantID"].Value.ToString();
string warrantName = r.Cells["WarrantName"].Value.ToString();
double issueNum = Convert.ToDouble(r.Cells["IssueNum"].Value);
double soldNum = Convert.ToDouble(r.Cells["SoldNum"].Value);
double last1Sold = Convert.ToDouble(r.Cells["Last1Sold"].Value);
double last2Sold = Convert.ToDouble(r.Cells["Last2Sold"].Value);
double last3Sold = Convert.ToDouble(r.Cells["Last3Sold"].Value);
string lastTradingDate = r.Cells["LastTradingDate"].Value.ToString();
string reIssuable = r.Cells["ReIssuable"].Value.ToString();
h.SetParameterValue("@WarrantID", warrantID);
h.SetParameterValue("@WarrantName", warrantName);
h.SetParameterValue("@IssueNum", issueNum);
h.SetParameterValue("@SoldNum", soldNum);
h.SetParameterValue("@Last1Sold", last1Sold);
h.SetParameterValue("@Last2Sold", last2Sold);
h.SetParameterValue("@Last3Sold", last3Sold);
h.SetParameterValue("@LastTradingDate", lastTradingDate);
h.SetParameterValue("@ReIssuable", reIssuable);
h.SetParameterValue("@MDate", DateTime.Now);
h.ExecuteCommand();
}
h.Dispose();
toolStripLabel1.Text = DateTime.Now + "更新成功";
GlobalUtility.LogInfo("Info", GlobalVar.globalParameter.userID + " 更新可增額列表");
} catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
private void 證系統上傳檔ToolStripMenuItem_Click(object sender, EventArgs e)
{
string fileName = "D:\\權證發行_相關Excel\\上傳檔\\權證發行匯入檔.xls";
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook workBook = null;
try {
string sql = @"SELECT a.UnderlyingID
,c.TraderID
,a.WarrantName
,c.Type
,c.CP
,IsNull(b.MPrice,0) MPrice
,c.K
,c.ResetR
,c.BarrierR
,c.T
,a.CR
,c.HV
,c.IV
,a.IssueNum
,c.FinancialR
,a.UseReward
,c.Apply1500W
,c.SerialNumber
FROM [EDIS].[dbo].[ApplyTotalList] a
LEFT JOIN [EDIS].[dbo].[WarrantPrices] b ON a.UnderlyingID=b.CommodityID
LEFT JOIN [EDIS].[dbo].[ApplyOfficial] c ON a.SerialNum=c.SerialNumber
WHERE a.ApplyKind='1' AND a.Result+0.00001 >= a.EquivalentNum
ORDER BY a.Market desc, a.Type, a.CP, a.UnderlyingID, a.SerialNum"; //a.SerialNum
//DataView dv = DeriLib.Util.ExecSqlQry(sql, GlobalVar.loginSet.edisSqlConnString);
System.Data.DataTable dv = MSSQL.ExecSqlQry(sql, GlobalVar.loginSet.edisSqlConnString);
if (dv.Rows.Count > 0)
{
int i = 3;
app.Visible = true;
workBook = app.Workbooks.Open(fileName);
//workBook.EnvelopeVisible = false;
Worksheet workSheet = (Worksheet)workBook.Sheets[1];
workSheet.get_Range("A3:BZ1000").ClearContents();
//workSheet.UsedRange.
foreach (DataRow dr in dv.Rows)
{
string date = DateTime.Today.ToString("yyyyMMdd");
string underlyingID = dr["UnderlyingID"].ToString();
string traderID = dr["TraderID"].ToString().TrimStart('0');
if (traderID == "10120" || traderID == "10329")
{
traderID = "7643";
}
string warrantName = dr["WarrantName"].ToString();
string type = dr["Type"].ToString();
string cp = dr["CP"].ToString();
string isReset = "N";
if (type == "重設型" || type == "牛熊證")
{
isReset = "Y";
}
double stockPrice = Convert.ToDouble(dr["MPrice"]);
double k = Convert.ToDouble(dr["K"]);
double resetR = Convert.ToDouble(dr["ResetR"]);
double barrierR = Convert.ToDouble(dr["BarrierR"]);
if (isReset == "Y")
{
k = Math.Round(resetR / 100 * stockPrice, 2);
}
double barrierP = Math.Round(barrierR / 100 * stockPrice, 2);
if (type == "牛熊證")
{
if (cp == "C")
{
barrierP = Math.Round(Math.Floor(barrierR * stockPrice) / 100, 2);
}
else if (cp == "P")
{
barrierP = Math.Round(Math.Ceiling(barrierR * stockPrice) / 100, 2);
}
}
//Check for moneyness constraint
if (type != "牛熊證")
{
if ((cp == "C" && k / stockPrice >= 1.5) || (cp == "P" && k / stockPrice <= 0.5))
{
MessageBox.Show(warrantName + " 超過價外50%限制");
// continue;
}
}
int t = Convert.ToInt32(dr["T"]);
double cr = Convert.ToDouble(dr["CR"]);
double r = GlobalVar.globalParameter.interestRate * 100;
double hv = Convert.ToDouble(dr["HV"]);
double iv = Convert.ToDouble(dr["IV"]);
double issueNum = Convert.ToDouble(dr["IssueNum"]);
double price = 0.0;
double financialR = Convert.ToDouble(dr["FinancialR"]);
string isReward = dr["UseReward"].ToString();
string is1500W = dr["Apply1500W"].ToString();
string serialNum = dr["SerialNumber"].ToString();
double p = 0.0;
double vol = iv / 100;
if (is1500W == "Y")
{
CallPutType cpType = CallPutType.Call;
if (cp == "P")
{
cpType = CallPutType.Put;
}
if (type == "牛熊證")
{
p = Pricing.BullBearWarrantPrice(cpType, stockPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, financialR, cr);
}
else if (type == "重設型")
{
p = Pricing.ResetWarrantPrice(cpType, stockPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
else
{
p = Pricing.NormalWarrantPrice(cpType, stockPrice, k, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
double totalValue = p * issueNum * 1000;
double volUpperLimmit = vol * 2;
while (totalValue < 15000000 && vol < volUpperLimmit)
{
vol += 0.01;
if (type == "牛熊證")
{
p = Pricing.BullBearWarrantPrice(cpType, stockPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, financialR, cr);
}
else if (type == "重設型")
{
p = Pricing.ResetWarrantPrice(cpType, stockPrice, resetR, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
else
{
p = Pricing.NormalWarrantPrice(cpType, stockPrice, k, GlobalVar.globalParameter.interestRate, vol, t, cr);
}
totalValue = p * issueNum * 1000;
}
if (vol < volUpperLimmit)
{
iv = vol * 100;
string cmdText = "UPDATE [ApplyOfficial] SET IVNew=@IVNew WHERE SerialNumber=@SerialNumber";
List <System.Data.SqlClient.SqlParameter> pars = new List <System.Data.SqlClient.SqlParameter>();
pars.Add(new SqlParameter("@IVNew", SqlDbType.Float));
pars.Add(new SqlParameter("@SerialNumber", SqlDbType.VarChar));
SQLCommandHelper h = new SQLCommandHelper(GlobalVar.loginSet.edisSqlConnString, cmdText, pars);
h.SetParameterValue("@IVNew", iv);
h.SetParameterValue("@SerialNumber", serialNum);
h.ExecuteCommand();
h.Dispose();
}
}
if (type == "重設型")
{
type = "一般型";
}
if (cp == "P")
{
cp = "認售";
}
else
{
cp = "認購";
}
try {
// workSheet.Cells[1][i] = date;
workSheet.get_Range("A" + i.ToString(), "A" + i.ToString()).Value = date;
workSheet.get_Range("B" + i.ToString(), "B" + i.ToString()).Value = underlyingID;
workSheet.get_Range("C" + i.ToString(), "C" + i.ToString()).Value = traderID;
workSheet.get_Range("D" + i.ToString(), "D" + i.ToString()).Value = warrantName;
workSheet.get_Range("E" + i.ToString(), "E" + i.ToString()).Value = type;
workSheet.get_Range("F" + i.ToString(), "F" + i.ToString()).Value = cp;
workSheet.get_Range("G" + i.ToString(), "G" + i.ToString()).Value = isReset;
workSheet.get_Range("H" + i.ToString(), "H" + i.ToString()).Value = stockPrice;
workSheet.get_Range("I" + i.ToString(), "I" + i.ToString()).Value = k;
workSheet.get_Range("J" + i.ToString(), "J" + i.ToString()).Value = resetR;
workSheet.get_Range("K" + i.ToString(), "K" + i.ToString()).Value = barrierP;
workSheet.get_Range("L" + i.ToString(), "L" + i.ToString()).Value = barrierR;
workSheet.get_Range("M" + i.ToString(), "M" + i.ToString()).Value = t;
workSheet.get_Range("N" + i.ToString(), "N" + i.ToString()).Value = cr;
workSheet.get_Range("O" + i.ToString(), "O" + i.ToString()).Value = r;
workSheet.get_Range("P" + i.ToString(), "P" + i.ToString()).Value = hv;
workSheet.get_Range("Q" + i.ToString(), "Q" + i.ToString()).Value = iv;
workSheet.get_Range("R" + i.ToString(), "R" + i.ToString()).Value = issueNum;
workSheet.get_Range("S" + i.ToString(), "S" + i.ToString()).Value = price;
workSheet.get_Range("T" + i.ToString(), "T" + i.ToString()).Value = financialR;
workSheet.get_Range("Y" + i.ToString(), "Y" + i.ToString()).Value = isReward;
i++;
} catch (Exception ex) {
MessageBox.Show("write" + ex.Message);
}
}
string sql2 = "SELECT [UnderlyingID] FROM [EDIS].[dbo].[ApplyOfficial] as A "
+ " left join (Select CS8010, count(1) as count from [10.19.1.20].[VOLDB].[dbo].[ED_RelationUnderlying] "
+ $" where RecordDate = (select top 1 RecordDate from [10.19.1.20].[VOLDB].[dbo].[ED_RelationUnderlying])"
+ " group by CS8010) as B on A.UnderlyingID = B.CS8010 "
+ " left join (SELECT stkid, MAX([IssueVol]) as MAX, min(IssueVol) as min FROM[10.19.1.20].[EDIS].[dbo].[WARRANTS]"
+ " where kgiwrt = '他家' and marketdate <= GETDATE() and lasttradedate >= GETDATE() and IssueVol<> 0 "
+ " group by stkid ) as C on A.UnderlyingID = C.stkid "
+ " WHERE B.count > 0 and (((IVNew > C.MAX or IVNew < C.min) and Apply1500W = 'Y') or ((IV > C.MAX or IV < C.min) and Apply1500W = 'N'))";
System.Data.DataTable badParam = MSSQL.ExecSqlQry(sql2, GlobalVar.loginSet.edisSqlConnString);
foreach (DataRow Row in badParam.Rows)
{
//WindowState = FormWindowState.Minimized;
//Show();
//WindowState = FormWindowState.Normal;
Activate();
MessageBox.Show(Row["UnderlyingID"] + " 為關係人標的，波動度超過可發範圍，會被稽核該該叫，請修改條件。");
}
GlobalUtility.LogInfo("Log", GlobalVar.globalParameter.userID + "產發行上傳檔");
app.Visible = false;
MessageBox.Show("發行上傳檔完成!");
}
else
{
MessageBox.Show("無可發行權證");
}
} catch (Exception ex) {
MessageBox.Show(ex.Message);
} finally {
if (workBook != null)
{
workBook.Save();
workBook.Close();
}
if (app != null)
{
app.Quit();
}
}
}