Exemple #1
0
        public List <Quotation> Quotations(int id, int problemid)
        {
            SqlConnection con = new SqlConnection(Resource.CadenaConexion);

            con.Open();
            List <Quotation> lista = new List <Quotation>();

            try
            {
                SpecialistManager specialistManager = new SpecialistManager();
                ProblemManager    problemmanager    = new ProblemManager();

                string sql = "Select qu.Id,ProblemId,qu.specialistid,qu.Description,qu.Price," +
                             "qu.EstimatedTime,qu.IncludesMaterials,qu.State,qu.StartDate," +
                             "qu.FinishDate,qu.FinalPrice,qu.SpecialistRate,qu.SpecialistComment," +
                             "qu.CustomerRate,qu.CustomerComment from Quotations qu " +
                             "join Problems pro on pro.Id = qu.ProblemId " +
                             "where pro.customerid = @id and ProblemId = @problemid";

                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.Add("@problemid", System.Data.SqlDbType.NVarChar).Value = problemid;
                cmd.Parameters.Add("@id", System.Data.SqlDbType.NVarChar).Value        = id;
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    Quotation spe = new Quotation();
                    spe.id                = reader.GetInt32(0);
                    spe.problem           = problemmanager.Obtener(reader.GetInt32(1));
                    spe.specialist        = specialistManager.Obtener(reader.GetInt32(2));
                    spe.description       = reader.GetString(3);
                    spe.price             = reader.GetDecimal(4);
                    spe.estimatedTime     = reader.GetByte(5);
                    spe.includesMaterial  = reader.GetBoolean(6);
                    spe.state             = reader.GetByte(7);
                    spe.startDate         = reader.GetDateTime(8);
                    spe.finishDate        = reader.GetDateTime(9);
                    spe.finalPrice        = reader.GetDecimal(10);
                    spe.specialistRate    = reader.GetDecimal(11);
                    spe.specialistComment = reader.GetString(12);
                    spe.customerRate      = reader.GetDecimal(13);
                    spe.customerComment   = reader.GetString(14);
                    lista.Add(spe);
                }
                reader.Close();
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e);
            }
            if (con.State == System.Data.ConnectionState.Open)
            {
                con.Close();
            }
            return(lista);
        }
Exemple #2
0
        public Quotation Insertar(Quotation spe)
        {
            Quotation     result = null;
            SqlConnection con    = new SqlConnection(Resource.CadenaConexion);

            con.Open();
            try
            {
                string sql = "Insert into Quotations(ProblemId,SpecialistId,Description,Price,EstimatedTime" +
                             ",IncludesMaterials,State,StartDate,FinishDate,FinalPrice,SpecialistRate,SpecialistComment" +
                             ",CustomerRate,CustomerComment) output INSERTED.Id values(@problemid,@specialistid,@description" +
                             ",@price,@estimatedtime,@includesmaterials,@state,@startdate,@finishdate,@finalprice" +
                             ",@specialistrate,@specialistcomment,@customerrate,@customercomment)";

                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.Add("@problemid", System.Data.SqlDbType.Int).Value             = spe.problem.id;
                cmd.Parameters.Add("@specialistid", System.Data.SqlDbType.Int).Value          = spe.specialist.id;
                cmd.Parameters.Add("@description", System.Data.SqlDbType.VarChar).Value       = spe.description;
                cmd.Parameters.Add("@price", System.Data.SqlDbType.Decimal).Value             = spe.price;
                cmd.Parameters.Add("@estimatedtime", System.Data.SqlDbType.Bit).Value         = spe.estimatedTime;
                cmd.Parameters.Add("@includesmaterials", System.Data.SqlDbType.Bit).Value     = spe.includesMaterial;
                cmd.Parameters.Add("@state", System.Data.SqlDbType.Bit).Value                 = spe.state;
                cmd.Parameters.Add("@startdate", System.Data.SqlDbType.DateTime).Value        = spe.startDate;
                cmd.Parameters.Add("@finishdate", System.Data.SqlDbType.DateTime).Value       = spe.finishDate;
                cmd.Parameters.Add("@finalprice", System.Data.SqlDbType.Decimal).Value        = spe.finalPrice;
                cmd.Parameters.Add("@specialistrate", System.Data.SqlDbType.Decimal).Value    = spe.specialistRate;
                cmd.Parameters.Add("@specialistcomment", System.Data.SqlDbType.VarChar).Value = spe.specialistComment;
                cmd.Parameters.Add("@customerrate", System.Data.SqlDbType.Decimal).Value      = spe.customerRate;
                cmd.Parameters.Add("@customercomment", System.Data.SqlDbType.VarChar).Value   = spe.customerComment;
                int modified = (int)cmd.ExecuteScalar();
                if (modified != 0)
                {
                    ProblemManager    problemManager    = new ProblemManager();
                    SpecialistManager specialistManager = new SpecialistManager();

                    result            = Obtener(modified);
                    result.specialist = specialistManager.Obtener(result.specialist.id);
                    result.problem    = problemManager.Obtener(result.problem.id);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                result = null;
            }
            if (con.State == System.Data.ConnectionState.Open)
            {
                con.Close();
            }
            return(result);
        }
Exemple #3
0
        public Quotation Obtener(int id)
        {
            Quotation     spe = null;
            SqlConnection con = new SqlConnection(Resource.CadenaConexion);

            con.Open();
            string sql = "select Id,ProblemId,SpecialistId,Description," +
                         "Price,EstimatedTime,IncludesMaterials,State," +
                         "StartDate,FinishDate,FinalPrice,SpecialistRate," +
                         "SpecialistComment,CustomerRate,CustomerComment from Quotations where Id = @quotation";

            SqlCommand cmd = new SqlCommand(sql, con);

            cmd.Parameters.Add("@quotation", System.Data.SqlDbType.NVarChar).Value = id;
            SqlDataReader reader =
                cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

            if (reader.Read())
            {
                ProblemManager    problemManager    = new ProblemManager();
                SpecialistManager specialistManager = new SpecialistManager();

                spe                   = new Quotation();
                spe.id                = reader.GetInt32(0);
                spe.problem           = problemManager.Obtener(reader.GetInt32(1));
                spe.specialist        = specialistManager.Obtener(reader.GetInt32(2));
                spe.description       = reader.GetString(3);
                spe.price             = reader.GetDecimal(4);
                spe.estimatedTime     = reader.GetByte(5);
                spe.includesMaterial  = reader.GetBoolean(6);
                spe.state             = reader.GetByte(7);
                spe.startDate         = reader.GetDateTime(8);
                spe.finishDate        = reader.GetDateTime(9);
                spe.finalPrice        = reader.GetDecimal(10);
                spe.specialistRate    = reader.GetDecimal(11);
                spe.specialistComment = reader.GetString(12);
                spe.customerRate      = reader.GetDecimal(13);
                spe.customerComment   = reader.GetString(14);
            }
            reader.Close();
            if (con.State == System.Data.ConnectionState.Open)
            {
                con.Close();
            }
            return(spe);
        }