예제 #1
0
        public static ratecolumns oldRatesValues(int _rateid)
        {
            ratecolumns oRec1   = new ratecolumns();
            string      _cmdstr = "SELECT rate_rateamt, rate_companyRtamt FROM [Rates] "
                                  + " WHERE rate_id = @rid";

            if (connect == null || connect.State == ConnectionState.Closed)
            {
                connect.Open();
            }

            try
            {
                command = new SqlCommand(_cmdstr, connect);
                command.Parameters.AddWithValue("@rid", _rateid);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    oRec1.EErate = Convert.ToDecimal(reader[0]);
                    oRec1.COrate = Convert.ToDecimal(reader[1]);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                connect.Close();
                command.Dispose();
            }
            return(oRec1);
        }
예제 #2
0
        public static void insertRates(ratecolumns inValues, string _pgcode)
        {
            string _cmdstr = "SELECT ph_id FROM [Planhier] "
                             + " WHERE ph_plancd = @pcode AND ph_plandesc = @dsc AND ph_tiercd = @tcode AND ph_progmcd = @prog";

            string _cmdstr1 = "SELECT COUNT(*) FROM [Rates] "
                              + " WHERE rate_plhr_id = @pid AND rate_rateamt = @r1 AND rate_companyRtamt = @r2 AND rate_effyrmo = @yrmo AND rate_py = @py";

            string _cmdstr2 = "INSERT INTO [Rates] (rate_plhr_id, rate_rateamt,rate_companyRtamt,rate_effyrmo,rate_py) "
                              + " VALUES (@pid,@r1,@r2,@yrmo,@py) ";

            string _cmdstr3 = "SELECT rate_id FROM [Rates] "
                              + " WHERE rate_plhr_id = @pid AND rate_rateamt = @r1 AND rate_companyRtamt = @r2 AND rate_effyrmo = @yrmo AND rate_py = @py";

            int            _plhrid  = 0;
            int            _foundid = 0;
            SqlTransaction ts;

            if (connect == null || connect.State == ConnectionState.Closed)
            {
                connect.Open();
            }
            ts = connect.BeginTransaction();
            try
            {
                command = new SqlCommand(_cmdstr, connect, ts);
                command.Parameters.AddWithValue("@pcode", inValues.PlanCode);
                command.Parameters.AddWithValue("@dsc", inValues.PlanDesc.Trim());
                command.Parameters.AddWithValue("@tcode", inValues.TierCode);
                command.Parameters.AddWithValue("@prog", _pgcode);
                if (!command.ExecuteScalar().Equals(DBNull.Value))
                {
                    _plhrid = Convert.ToInt32(command.ExecuteScalar());
                }
                if (_plhrid != 0)
                {
                    command = new SqlCommand(_cmdstr1, connect, ts);
                    command.Parameters.AddWithValue("@pid", _plhrid);
                    command.Parameters.AddWithValue("@r1", inValues.EErate);
                    command.Parameters.AddWithValue("@r2", inValues.COrate);
                    command.Parameters.AddWithValue("@yrmo", inValues.EffYrmo);
                    command.Parameters.AddWithValue("@py", inValues.EffYrmo.Substring(0, 4));
                    _foundid = Convert.ToInt32(command.ExecuteScalar());
                    if (_foundid > 0)
                    {
                        throw (new Exception(" Rates for selected plan are already defined!"));
                    }
                    else
                    {
                        command = new SqlCommand(_cmdstr2, connect, ts);
                        command.Parameters.AddWithValue("@pid", _plhrid);
                        command.Parameters.AddWithValue("@r1", inValues.EErate);
                        command.Parameters.AddWithValue("@r2", inValues.COrate);
                        command.Parameters.AddWithValue("@yrmo", inValues.EffYrmo);
                        command.Parameters.AddWithValue("@py", inValues.EffYrmo.Substring(0, 4));
                        command.ExecuteNonQuery();

                        command = new SqlCommand(_cmdstr3, connect, ts);
                        command.Parameters.AddWithValue("@pid", _plhrid);
                        command.Parameters.AddWithValue("@r1", inValues.EErate);
                        command.Parameters.AddWithValue("@r2", inValues.COrate);
                        command.Parameters.AddWithValue("@yrmo", inValues.EffYrmo);
                        command.Parameters.AddWithValue("@py", inValues.EffYrmo.Substring(0, 4));
                        HttpContext.Current.Session["rateid"]   = command.ExecuteScalar();
                        HttpContext.Current.Session["ratephid"] = _plhrid;
                    }
                }
                ts.Commit();
            }
            catch (Exception ex)
            {
                ts.Rollback();
                if (ex.Message.Contains("Rates for selected plan are already defined!"))
                {
                    throw (new Exception("Error Inserting record - Rates for selected plan are already defined!"));
                }
                else
                {
                    throw (new Exception("Error Inserting record!"));
                }
            }
            finally
            {
                connect.Close();
                command.Dispose();
            }
        }