示例#1
0
        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.");
            }
        }
示例#3
0
        /*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);
            }
        }
示例#5
0
        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);
            }
        }
示例#6
0
        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!");
        }
示例#7
0
        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);
            }
        }
示例#8
0
        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();
                }
            }
        }