Ejemplo n.º 1
0
        public List <InvoiceDTO> GetListInvoice()
        {
            List <InvoiceDTO> list = null;
            string            SQL  = "SELECT ID, Date_Of_Purcharse, Customer_ID FROM Invoices";
            SqlConnection     cnn  = DBUtils.GetConnection();
            SqlCommand        cmd  = new SqlCommand(SQL, cnn);

            try
            {
                if (cnn.State == ConnectionState.Closed)
                {
                    cnn.Open();
                    SqlDataReader     rd                = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    CustomerDAO       cusDAO            = new CustomerDAO();
                    InvoiceDetailsDAO invoiceDetailsDAO = new InvoiceDetailsDAO();
                    if (rd.HasRows)
                    {
                        while (rd.Read())
                        {
                            if (list == null)
                            {
                                list = new List <InvoiceDTO>();
                            }
                            InvoiceDTO invoiceDTO = new InvoiceDTO
                            {
                                ID = rd.GetInt32(0),
                                Date_Of_Purcharse = rd.GetDateTime(1),
                                Customer_ID       = rd.GetInt32(2),
                            };
                            invoiceDTO.Customer_Name = cusDAO.FindNameByID(invoiceDTO.Customer_ID);
                            invoiceDTO.Total         = invoiceDetailsDAO.GetTotalPriceOfInvoice(invoiceDTO.ID);
                            list.Add(invoiceDTO);
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return(list);
        }
Ejemplo n.º 2
0
        public double GetTotalByMonthAndYear(int month, int year)
        {
            double        total = 0;
            string        SQL   = "SELECT ID FROM Invoices WHERE MONTH(Date_Of_Purcharse) = @Month AND YEAR(Date_Of_Purcharse) = @Year";
            SqlConnection cnn   = DBUtils.GetConnection();
            SqlCommand    cmd   = new SqlCommand(SQL, cnn);

            cmd.Parameters.AddWithValue("@Month", month);
            cmd.Parameters.AddWithValue("@Year", year);
            try
            {
                if (cnn.State == ConnectionState.Closed)
                {
                    cnn.Open();
                    SqlDataReader     rd                = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    CustomerDAO       cusDAO            = new CustomerDAO();
                    InvoiceDetailsDAO invoiceDetailsDAO = new InvoiceDetailsDAO();
                    if (rd.HasRows)
                    {
                        while (rd.Read())
                        {
                            InvoiceDTO invoiceDTO = new InvoiceDTO
                            {
                                ID = rd.GetInt32(0)
                            };
                            total += invoiceDetailsDAO.GetTotalPriceOfInvoice(invoiceDTO.ID);
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return(total);
        }