private async void toolStripButtonEdit_Click(object sender, EventArgs e) { toolStripLabel1.Text = ""; toolStrip1.Enabled = false; dt.Rows.Clear(); isEdit = true; SetButton(); string key = null; //Get key and id try { key = GlobalUtility.GetKey(); } catch (Exception ex) { MessageBox.Show($"GetKey from BSSDB failed {ex}"); return; } string id = GlobalUtility.GetID(); //parse TWSE and OTC 7-1 html if (!await ParseHtml($"http://siis.twse.com.tw/server-java/t150sa10?step=0&id=9200pd{id}&TYPEK=sii&key={key}", true) & !await ParseHtml($"http://siis.twse.com.tw/server-java/o_t150sa10?step=0&id=9200pd{id}&TYPEK=otc&key={key}", false)) { return; } GlobalUtility.LogInfo("Info", GlobalVar.globalParameter.userID + " 下載7-1試算表"); toolStrip1.Enabled = true; }
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 async void toolStripButtonEdit_Click(object sender, EventArgs e) { toolStripLabel1.Text = ""; toolStrip1.Enabled = false; dt.Rows.Clear(); isEdit = true; SetButton(); //Get key and id string key = GlobalUtility.GetKey(); string id = GlobalUtility.GetID(); DateTime lastTrade = TradeDate.LastNTradeDateDT(1); string aday = (lastTrade.Year - 1911) + lastTrade.ToString("MMdd"); //parse TWSE and OTC Incr html if (!await ParseHtml($"http://siis.twse.com.tw/server-java/t159sa04?step=1&id=9200pd{id}&TYPEK=sii&key={key}&cDATE={aday}&co_id=9200", true) & !await ParseHtml($"http://siis.twse.com.tw/server-java/t159sa04?step=1&id=9200pd{id}&TYPEK=otc&key={key}&cDATE={aday}&co_id=9200", false)) { return; } //LoadData(); GlobalUtility.LogInfo("Info", GlobalVar.globalParameter.userID + " 下載可增額列表"); toolStrip1.Enabled = true; }
private void OfficiallyApply() { try { UpdateData(); string sql1 = "DELETE FROM [EDIS].[dbo].[ReIssueOfficial] WHERE [UserID]='" + userID + "'"; string sql2 = @"INSERT INTO [EDIS].[dbo].[ReIssueOfficial] ([SerialNum],[UnderlyingID],[WarrantID],[WarrantName],[exeRatio],[ReIssueNum],[UseReward],[MarketTmr],[TraderID],[MDate],UserID) SELECT a.SerialNum, b.UnderlyingID, a.WarrantID, b.WarrantName, b.exeRatio, a.ReIssueNum, CASE WHEN b.isReward='1' THEN 'Y' ELSE 'N' END isReward, a.MarketTmr, a.TraderID, a.MDate, a.UserID FROM [EDIS].[dbo].[ReIssueTempList] a LEFT JOIN [EDIS].[dbo].[WarrantBasic] b ON a.WarrantID=b.WarrantID"; sql2 += " WHERE a.[UserID]='" + userID + "' AND a.[ConfirmChecked]='Y'"; string sql3 = "DELETE FROM [EDIS].[dbo].[ApplyTotalList] WHERE [UserID]='" + userID + "' AND [ApplyKind]='2'"; string sql4 = @"INSERT INTO [EDIS].[dbo].[ApplyTotalList] ([ApplyKind],[SerialNum],[Market],[UnderlyingID],[WarrantName],[CR] ,[IssueNum],[EquivalentNum],[Credit],[RewardCredit],[Type],[CP],[UseReward],[MarketTmr],[TraderID],[MDate],UserID) SELECT '2',a.SerialNum, b.Market, a.UnderlyingID, a.WarrantName, a.exeRatio, a.ReIssueNum, (a.exeRatio*a.ReIssueNum), b.IssueCredit, b.RewardIssueCredit, CASE WHEN SUBSTRING(c.WarrantType,1,2)='浮動' THEN '重設型' ELSE (CASE WHEN c.WarrantType='重設' THEN '牛熊證' ELSE '一般型' END) END, CASE WHEN SUBSTRING(c.WarrantType,LEN(c.WarrantType)-3,4)='熊證認售' OR SUBSTRING(c.WarrantType,LEN(c.WarrantType)-3,4)='認售權證' THEN 'P' ELSE 'C' END, a.UseReward,a.MarketTmr, a.TraderID, GETDATE(), a.UserID FROM [EDIS].[dbo].[ReIssueOfficial] a LEFT JOIN [EDIS].[dbo].[WarrantUnderlyingSummary] b ON a.UnderlyingID=b.UnderlyingID LEFT JOIN [EDIS].[dbo].[WarrantBasic] c ON a.WarrantID=c.WarrantID"; sql4 += " WHERE a.[UserID]='" + userID + "'"; conn.Open(); MSSQL.ExecSqlCmd(sql1, conn); MSSQL.ExecSqlCmd(sql2, conn); MSSQL.ExecSqlCmd(sql3, conn); MSSQL.ExecSqlCmd(sql4, conn); conn.Close(); toolStripLabel2.Text = DateTime.Now + "申請成功"; GlobalUtility.LogInfo("Info", GlobalVar.globalParameter.userID + " 申請" + applyCount + "檔權證增額"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void toolStripButton1_Click(object sender, EventArgs e) { if (GlobalVar.globalParameter.userGroup == "FE") { OfficiallyApply(); LoadData(); } else { if (DateTime.Now.TimeOfDay.TotalMinutes > 630) { MessageBox.Show("超過交易所申報時間,欲改條件請洽行政組"); } else if (DateTime.Now.TimeOfDay.TotalMinutes > 570) { DialogResult result = MessageBox.Show("超過約定的9:30了,已經告知組長及行政?", "逾時申請", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (result == DialogResult.Yes) { OfficiallyApply(); LoadData(); GlobalUtility.LogInfo("Announce", GlobalVar.globalParameter.userID + " 逾時申請" + applyCount + "檔權證增額"); } else { LoadData(); } } else { OfficiallyApply(); LoadData(); } } }
public bool TryIPLogin() { string IP = EDLib.Utility.GetLocalIPAddress(); string sqlTemp = $"SELECT [UserGroup],[UserLevel],[UserName],[Deputy],[UserID] FROM [EDIS].[dbo].[User] WHERE IP = '{IP}'"; //DataView dvTemp = DeriLib.Util.ExecSqlQry(sqlTemp , GlobalVar.loginSet.edisSqlConnString); DataView dvTemp = EDLib.SQL.MSSQL.ExecSqlQry(sqlTemp, GlobalVar.loginSet.edisSqlConnString).DefaultView; if (dvTemp.Count > 0) { foreach (DataRowView drTemp in dvTemp) { GlobalVar.globalParameter.userGroup = drTemp["UserGroup"].ToString(); GlobalVar.globalParameter.userLevel = drTemp["UserLevel"].ToString(); GlobalVar.globalParameter.userName = drTemp["UserName"].ToString(); GlobalVar.globalParameter.userDeputy = drTemp["Deputy"].ToString(); GlobalVar.globalParameter.userID = drTemp["UserID"].ToString(); } loginOK = true; GlobalUtility.LogInfo("Log", GlobalVar.globalParameter.userID + " login.(IP)"); this.Close(); GlobalVar.mainForm.Start(); return(true); } GlobalUtility.LogInfo("Log", IP + " login failed."); return(false); }
private void 刪除ToolStripMenuItem_Click(object sender, EventArgs e) { string applyKind = ultraGrid1.ActiveRow.Cells["ApplyKind"].Value.ToString(); string warrantName = ultraGrid1.ActiveRow.Cells["WarrantName"].Value.ToString(); DialogResult result = MessageBox.Show("刪除此檔 " + applyKind + warrantName + ",確定?", "刪除資料", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (result == DialogResult.Yes) { string serialNum = ultraGrid1.ActiveRow.Cells["SerialNum"].Value.ToString(); conn.Open(); EDLib.SQL.MSSQL.ExecSqlCmd("DELETE FROM [ApplyTotalList] WHERE SerialNum='" + serialNum + "'", conn); if (applyKind == "新發") { EDLib.SQL.MSSQL.ExecSqlCmd("DELETE FROM [ApplyOfficial] WHERE SerialNumber='" + serialNum + "'", conn); } else if (applyKind == "增額") { EDLib.SQL.MSSQL.ExecSqlCmd("DELETE FROM [ReIssueOfficial] WHERE SerialNum='" + serialNum + "'", conn); } conn.Close(); LoadData(); GlobalUtility.LogInfo("Info", GlobalVar.globalParameter.userID + " 刪除一檔" + applyKind + ": " + warrantName); } }
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 DISTINCT a.UnderlyingID ,b.UnifiedID ,b.FullName FROM [EDIS].[dbo].[ApplyTotalList] a LEFT JOIN [EDIS].[dbo].[WarrantUnderlying] b ON a.UnderlyingID=b.UnderlyingID WHERE a.Result>=a.EquivalentNum AND (b.StockType='DS' OR b.StockType='DR')"; DataView dv = DeriLib.Util.ExecSqlQry(sql, GlobalVar.loginSet.edisSqlConnString); int i = 2; if (dv.Count > 0) { app.Visible = true; workBook = app.Workbooks.Open(fileName); //workBook.EnvelopeVisible = false; Worksheet workSheet = (Worksheet)workBook.Sheets[1]; workSheet.get_Range("A3:Z1000").ClearContents(); foreach (DataRowView dr in dv) { workSheet.get_Range("A" + i.ToString(), "A" + i.ToString()).Value = i - 1; workSheet.get_Range("B" + i.ToString(), "B" + i.ToString()).Value = dr["UnifiedID"].ToString(); workSheet.get_Range("C" + i.ToString(), "C" + i.ToString()).Value = dr["FullName"].ToString(); i++; } 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(); } } }
private void toolStripButton1_Click(object sender, EventArgs e) { string info = ""; info = toolStripTextBox1.Text; if (info != "") { GlobalUtility.LogInfo("Announce", info); toolStripTextBox1.Text = ""; LoadUltraGrid2();// } }
public override WorkState DoWork() { try { if (GlobalVar.mainForm != null) { //GlobalVar.mainForm.SetUltraGrid2(); GlobalVar.mainForm.LoadUltraGrid2(); return(WorkState.Successful); } else { return(WorkState.Failed); } } catch (Exception ex) { GlobalUtility.LogInfo("Error", "AnnounceWork Error: " + ex.Message); return(WorkState.Exception); } }
private void Check() { string account = textBox1.Text; string password = textBox2.Text; if (account != "" && password != "") // Login with UserID { string sqlTemp = "SELECT [UserGroup],[UserLevel],[UserName],[Deputy] FROM [EDIS].[dbo].[User] WHERE UserID = '" + account + "' and [UserPasswordEncrypt] = HASHBYTES('SHA1', '" + password + "')"; DataView dvTemp = DeriLib.Util.ExecSqlQry(sqlTemp, GlobalVar.loginSet.edisSqlConnString); if (dvTemp.Count > 0) { foreach (DataRowView drTemp in dvTemp) { GlobalVar.globalParameter.userGroup = drTemp["UserGroup"].ToString(); GlobalVar.globalParameter.userLevel = drTemp["UserLevel"].ToString(); GlobalVar.globalParameter.userName = drTemp["UserName"].ToString(); GlobalVar.globalParameter.userDeputy = drTemp["Deputy"].ToString(); } GlobalVar.globalParameter.userID = account; loginOK = true; GlobalUtility.LogInfo("Log", GlobalVar.globalParameter.userID + " login.(ID)"); this.Close(); GlobalVar.mainForm.Start(); } else { MessageBox.Show("帳號密碼錯誤!"); textBox2.Text = ""; textBox1.Text = ""; textBox1.Focus(); GlobalUtility.LogInfo("Log", account + " login failed." + password); Thread.Sleep(5000); return; } /*if (password == passwordS) { * GlobalVar.globalParameter.userID = account; * GlobalVar.globalParameter.userDeputy = deputy; * GlobalVar.globalParameter.userGroup = group; * GlobalVar.globalParameter.userLevel = level; * GlobalVar.globalParameter.userName = name; * loginOK = true; * this.Close(); * GlobalVar.mainForm.Start(); * } else { * MessageBox.Show("密碼錯誤!"); * textBox2.Text = ""; * textBox2.Focus(); * GlobalUtility.logInfo("Log" , account + " login failed."+password); * return; * }*/ } else { MessageBox.Show("請輸入帳號密碼"); textBox2.Text = ""; textBox1.Focus(); return; } }
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 證系統上傳檔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(); } } }
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; bool noPrice = false; try { string sql = @"SELECT a.WarrantName ,a.IssueNum ,IsNull(c.MPrice,IsNull(c.BPrice,IsNull(c.APrice,0))) MPrice ,b.WarrantID FROM [EDIS].[dbo].[ApplyTotalList] a LEFT JOIN [EDIS].[dbo].[ReIssueOfficial] b ON a.SerialNum=b.SerialNum LEFT JOIN [EDIS].[dbo].[WarrantPrices] c ON b.WarrantID=c.CommodityID WHERE a.ApplyKind='2' AND a.Result + 0.00001 >=a.EquivalentNum ORDER BY a.Market desc, 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:Z1000").ClearContents(); foreach (DataRow dr in dv.Rows) { double warrantPrice = Convert.ToDouble(dr["MPrice"]); if (warrantPrice == 0.0) { noPrice = true; } workSheet.get_Range("A" + i.ToString(), "A" + i.ToString()).Value = dr["WarrantName"].ToString(); workSheet.get_Range("B" + i.ToString(), "B" + i.ToString()).Value = "權證增額"; workSheet.get_Range("C" + i.ToString(), "C" + i.ToString()).Value = DateTime.Today.ToString("yyyyMMdd"); workSheet.get_Range("D" + i.ToString(), "D" + i.ToString()).Value = "增額發行"; workSheet.get_Range("E" + i.ToString(), "E" + i.ToString()).Value = Convert.ToDouble(dr["IssueNum"]) * 1000; workSheet.get_Range("F" + i.ToString(), "F" + i.ToString()).Value = warrantPrice; i++; } if (noPrice) { MessageBox.Show("注意!有權證價格為零!"); } 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(); } } }
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 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 轉申請發行TXTToolStripMenuItem_Click(object sender, EventArgs e) { string fileTSE = "D:\\權證發行_相關Excel\\上傳檔\\TSE申請上傳檔.txt"; string fileOTC = "D:\\權證發行_相關Excel\\上傳檔\\OTC申請上傳檔.txt"; //TXTFileWriter tseWriter = new TXTFileWriter(fileTSE); //TXTFileWriter otcWriter = new TXTFileWriter(fileOTC); int tseCount = 0; int otcCount = 0; int tseReissue = 0; int otcReissue = 0; int tseReward = 0; int otcReward = 0; try { using (StreamWriter tseWriter = new StreamWriter(fileTSE, false, Encoding.GetEncoding("Big5"))) using (StreamWriter otcWriter = new StreamWriter(fileOTC, false, Encoding.GetEncoding("Big5"))) { string sql = @"SELECT a.ApplyKind ,a.Market ,a.WarrantName ,a.UnderlyingID ,a.IssueNum ,a.CR ,IsNull(CASE WHEN a.ApplyKind='2' THEN c.T ELSE b.T END,6) T ,a.Type ,a.CP ,CASE WHEN a.UseReward='Y' THEN '1' ELSE '0' END UseReward ,CASE WHEN a.MarketTmr='Y' THEN '1' Else '0' END MarketTmr FROM [EDIS].[dbo].[ApplyTotalList] a LEFT JOIN [EDIS].[dbo].[ApplyOfficial] b ON a.SerialNum=b.SerialNumber LEFT JOIN [EDIS].[dbo].[WarrantBasic] c ON a.WarrantName=c.WarrantName 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) { foreach (DataRow dr in dv.Rows) { string applyKind = dr["ApplyKind"].ToString(); string market = dr["Market"].ToString(); string warrantName = dr["WarrantName"].ToString(); string underlyingID = dr["UnderlyingID"].ToString(); double issueNum = Convert.ToDouble(dr["IssueNum"]); double cr = Convert.ToDouble(dr["CR"]); int t = Convert.ToInt32(dr["T"]); string type = dr["Type"].ToString(); string cp = dr["CP"].ToString(); string useReward = dr["UseReward"].ToString(); string marketTmr = dr["MarketTmr"].ToString(); string markup = " "; int byteLen = System.Text.Encoding.Default.GetBytes(warrantName).Length; warrantName = warrantName + markup.Substring(0, 16 - byteLen); underlyingID = underlyingID.PadRight(12, ' '); string issueNumS = issueNum.ToString().PadLeft(7, '0'); //issueNumS = issueNumS.PadLeft(7, '0'); string crS = (cr * 10000).ToString(); crS = crS.Substring(0, Math.Min(5, crS.Length)); crS = crS.PadLeft(5, '0'); string tS = t.ToString().PadLeft(2, '0'); //tS = tS.PadLeft(2, '0'); string tempType = "1"; if (type == "牛熊證") { if (cp == "P") { tempType = "4"; } else { tempType = "3"; } } else { if (cp == "P") { tempType = "2"; } else { tempType = "1"; } } string writestr = warrantName + underlyingID + issueNumS + crS + tS + tempType + useReward + marketTmr; if (market == "TSE") { //tseWriter.WriteFile(writestr); tseWriter.WriteLine(writestr); tseCount++; if (useReward == "1") { tseReward++; } if (applyKind == "2") { tseReissue++; } } else if (market == "OTC") { //otcWriter.WriteFile(writestr); otcWriter.WriteLine(writestr); otcCount++; if (useReward == "1") { otcReward++; } if (applyKind == "2") { otcReissue++; } } } } } string infoStr = $"TSE 共{tseCount}檔,增額{tseReissue}檔,獎勵{tseReward}檔。\nOTC共{otcCount}檔,增額{otcReissue}檔,獎勵{otcReward}檔。"; GlobalUtility.LogInfo("Info", $"今日共申請{(tseCount + otcCount)}檔權證發行/增額"); MessageBox.Show("轉TXT檔完成!\n" + infoStr); } catch (Exception ex) { MessageBox.Show(ex.Message); } }