private void OnDateTimeIntervalChanging(DateTimeInterval dateTimeInterval)
 {
     txtBeginDate.Text = String.Empty;
     txtEndDate.Text = String.Empty;
     if (dateTimeInterval != null)
     {
         if (dateTimeInterval.BeginDate > DateTime.MinValue.Sql2005MinValue())
             txtBeginDate.Text = dateTimeInterval.BeginDate.ToLocalDateString();
         if (dateTimeInterval.EndDate < DateTime.MaxValue.Date)
             txtEndDate.Text = dateTimeInterval.EndDate.ToLocalDateString();
     }
 }
        public IQueryable GetPlans(DateTimeInterval dateTimeInterval, String name, string sortExpression,
                                   int startRowIndex, int maximumRows)
        {
            var query = from plan in DbContext.Plans
                        join package in DbContext.Packages on plan.PackageId equals package.PackageId
                        where
                            plan.AvailableStartDate >= dateTimeInterval.BeginDate &&
                            plan.AvailableEndDate <= dateTimeInterval.EndDate
                        select new
                                   {
                                       plan.PlanId,
                                       PlanName = plan.Name,
                                       AvailableEndDate = plan.AvailableEndDate.Date,
                                       AvailableStartDate = plan.AvailableStartDate.Date,
                                       PackageName = package.Name
                                   };

            if (!String.IsNullOrEmpty(name))
                query = query.Where(plan => plan.PlanName.Contains(name));

            return query.SortAndPage(sortExpression, startRowIndex, maximumRows, "PlanId");
        }
 /// <summary>
 /// this method return all information CustomerCalls
 /// </summary>
 /// <param name="companyId"></param>
 /// <param name="customerCallStatusId"></param>
 /// <param name="technicalEmployeeId"></param>
 /// <param name="dateTimeInterval"></param>
 /// <param name="sortExpression"></param>
 /// <param name="startRowIndex"></param>
 /// <param name="maximumRows"></param>
 /// <returns></returns>
 public IQueryable GetSupportCustomerCalls(Int32 companyId, Int32? customerCallStatusId,
                                           Int32? technicalEmployeeId, DateTimeInterval dateTimeInterval,
                                           string sortExpression, int startRowIndex, int maximumRows)
 {
     return GetCustomerCalls(companyId, null, customerCallStatusId, CustomerCallType.SUPPORT, technicalEmployeeId,
                             dateTimeInterval, sortExpression, startRowIndex, maximumRows);
 }
 /// <summary>
 ///  this is the count method of GetAccoladeCustomerCalls 
 /// </summary>
 /// <param name="companyId"></param>
 /// <param name="customerCallStatusId"></param>
 /// <param name="technicalEmployeeId"></param>
 /// <param name="dateTimeInterval"></param>
 /// <param name="sortExpression"></param>
 /// <param name="startRowIndex"></param>
 /// <param name="maximumRows"></param>
 /// <returns></returns>
 public Int32 GetAccoladeCustomerCallsCount(Int32 companyId, Int32? customerCallStatusId,
                                            Int32? technicalEmployeeId, DateTimeInterval dateTimeInterval,
                                            string sortExpression, int startRowIndex, int maximumRows)
 {
     return
         GetAccoladeCustomerCalls(companyId, customerCallStatusId, technicalEmployeeId, dateTimeInterval,
                                  sortExpression, startRowIndex, maximumRows).Cast<IQueryable>().Count();
 }
 public Int32 GetCustomerCallsCount(Int32? companyId, Int32? customerId, Int32? customerCallStatusId,
                                    Int32? customerCallType, Int32? technicalEmployeeId,
                                    DateTimeInterval dateTimeInterval, string sortExpression, int startRowIndex,
                                    int maximumRows)
 {
     return
         GetCustomerCalls(companyId, customerCallStatusId, customerCallStatusId, customerCallType,
                          technicalEmployeeId, dateTimeInterval, sortExpression, startRowIndex, maximumRows).Cast
             <object>().Count();
 }
        /// <summary>
        /// this method return customerCalls
        /// </summary>
        /// <param name="sortExpression"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="maximumRows"></param>
        /// <param name="companyId"></param>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public IQueryable GetCustomerCalls(Int32? companyId, Int32? customerId, Int32? customerCallStatusId,
                                           Int32? customerCallType, Int32? technicalEmployeeId,
                                           DateTimeInterval dateTimeInterval, string sortExpression, int startRowIndex,
                                           int maximumRows)
        {
            var queryCustomerCall = from customerCall in DbContext.CustomerCalls
                                    join employee in DbContext.Employees on customerCall.TechnicalEmployeeId equals
                                        employee.EmployeeId into gTechnicalEmployees
                                    from technicalEmployee in gTechnicalEmployees.DefaultIfEmpty()
                                    join postUsers in DbContext.Users on customerCall.UserId equals postUsers.UserId
                                        into gPostUsers
                                    from postUser in gPostUsers.DefaultIfEmpty()
                                    join postProfiles in DbContext.Profiles on postUser.ProfileId equals
                                        postProfiles.ProfileId into gPostProfiles
                                    from postProfile in gPostProfiles.DefaultIfEmpty()
                                    join technicalProfiles in DbContext.Profiles on technicalEmployee.ProfileId equals
                                        technicalProfiles.ProfileId into gTechnicalProfiles
                                    from technicalProfile in gTechnicalProfiles.DefaultIfEmpty()
                                    join customerCallStatus in DbContext.CustomerCallStatus on
                                        customerCall.CustomerCallStatusId equals customerCallStatus.CustomerCallStatusId
                                        into gCustomerCallStatus
                                    from customerCallStatus in gCustomerCallStatus.DefaultIfEmpty()
                                    where
                                        ((customerCall.OpenedDate >= dateTimeInterval.BeginDate) &&
                                         (customerCall.OpenedDate <= dateTimeInterval.EndDate))
                                    select new
                                               {
                                                   customerCall.CompanyId,
                                                   customerCall.CustomerCallId,
                                                   customerCall.ModifiedDate,
                                                   customerCall.CustomerId,
                                                   customerCall.CallNumber,
                                                   customerCall.CallNumberAssociated,
                                                   customerCall.Sector,
                                                   customerCall.OpenedDate,
                                                   customerCall.ClosedDate,
                                                   customerCall.CustomerEquipmentId,
                                                   customerCall.Description,
                                                   customerCall.CustomerCallTypeId,
                                                   customerCall.Subject,
                                                   customerCall.UserId,
                                                   customerCall.TechnicalEmployeeId,
                                                   customerCall.Source,
                                                   customerCall.Rating,
                                                   CustomerName =
                                        customerCall.Customer.Profile != null
                                            ? customerCall.Customer.Profile.Name
                                            : customerCall.Customer.LegalEntityProfile.CompanyName,
                                                   CustomerCallStatusId = customerCall.CustomerCallStatusId ?? 0,
                                                   technicalProfile,
                                                   customerCallStatusName = customerCallStatus.Name,
                                                   UserName = postProfile.Name,
                                                   UserNameEmail = postProfile.Email,
                                                   userNamePhone = postProfile.Phone
                                               };

            if (companyId.HasValue)
                queryCustomerCall = queryCustomerCall.Where(cc => cc.CompanyId == companyId);

            if (customerCallStatusId.HasValue)
            {
                if (customerCallStatusId == CustomerCallStatus.Opened)
                    queryCustomerCall = queryCustomerCall.Where(cc => cc.CustomerCallStatusId == CustomerCallStatus.New || cc.CustomerCallStatusId == CustomerCallStatus.Waiting);
                else
                    queryCustomerCall = queryCustomerCall.Where(cc => cc.CustomerCallStatusId == customerCallStatusId);
            }

            if (customerCallType.HasValue)
                queryCustomerCall = queryCustomerCall.Where(cc => cc.CustomerCallTypeId == customerCallType);

            if (technicalEmployeeId.HasValue)
                queryCustomerCall = queryCustomerCall.Where(cc => cc.TechnicalEmployeeId == (Int32)technicalEmployeeId);

            if (customerId.HasValue)
                queryCustomerCall = queryCustomerCall.Where(cc => cc.CustomerId == (Int32)customerId);

            if (String.IsNullOrEmpty(sortExpression))
                sortExpression = "OpenedDate DESC";

            return
                queryCustomerCall.SortAndPage(sortExpression, startRowIndex, maximumRows, "OpenedDate").
                    OrderByDescending(x => x.Rating);
        }
 /// <summary>
 /// this method return the total of CustomerFollowups
 /// </summary>
 /// <param name="companyId"></param>
 /// <param name="sortExpression"></param>
 /// <param name="startRowIndex"></param>
 /// <param name="maximumRows"></param>
 /// <returns></returns>
 public Int32 GetCustomerFollowupsCount(Int32 companyId, Int32? customerFollowupActionId, string contactName,
                                        DateTimeInterval dateTimeInterval,
                                        string sortExpression, Int32 startRowIndex, Int32 maximumRows)
 {
     return
         GetCustomerFollowups(companyId, customerFollowupActionId, contactName, dateTimeInterval, sortExpression,
                              startRowIndex, maximumRows).Cast<IQueryable>().Count();
 }
        /// <summary>
        /// this method return all CustomerFollowup
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="sortExpression"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="maximumRows"></param>
        /// <returns></returns>
        public IQueryable GetCustomerFollowups(Int32 companyId, Int32? customerFollowupActionId, string contactName,
                                               DateTimeInterval dateTimeInterval,
                                               string sortExpression, Int32 startRowIndex, Int32 maximumRows)
        {
            var query = from customerFollowup in DbContext.CustomerFollowups
                        where customerFollowup.CompanyId == companyId
                        join customerFollowupAction in DbContext.CustomerFollowupActions on
                            customerFollowup.CustomerFollowupActionId equals
                            customerFollowupAction.CustomerFollowupActionId into gCustomerFollowUpActions
                        from customerFollowUpActions in gCustomerFollowUpActions.DefaultIfEmpty()
                        join contact in DbContext.Contacts on customerFollowup.ContactId equals contact.ContactId
                        select new
                                   {
                                       customerFollowup.CustomerFollowupId,
                                       customerFollowup.CompanyId,
                                       customerFollowup.ContactId,
                                       customerFollowup.CustomerFollowupActionId,
                                       customerFollowup.UserId,
                                       customerFollowup.EntryDate,
                                       customerFollowup.Description,
                                       ContactName = contact.Name,
                                       CustomerFollowupActionName = customerFollowUpActions.Name
                                   };

            if (customerFollowupActionId.HasValue)
                query = query.Where(x => x.CustomerFollowupActionId == customerFollowupActionId);

            if (!String.IsNullOrEmpty(contactName))
                query = query.Where(x => x.ContactName.Contains(contactName));

            if (dateTimeInterval != null)
                query =
                    query.Where(
                        x => x.EntryDate >= dateTimeInterval.BeginDate && x.EntryDate <= dateTimeInterval.EndDate);

            return query.SortAndPage(
                String.IsNullOrEmpty(sortExpression)
                    ? "EntryDate Desc"
                    : sortExpression,
                startRowIndex, maximumRows, "CustomerFollowupId");
        }
 /// <summary>
 /// This method return all sales grouped by a PaymentMethod
 /// </summary>
 /// <param name="companyId"></param>
 /// <param name="startDate"></param>
 /// <param name="endDate"></param>
 /// <returns></returns>
 public DataTable MapOfSale_Payment(int companyId, DateTimeInterval dateInterval)
 {
     var query = from sale in DbContext.Sales
                 join invoice in DbContext.Invoices on sale.InvoiceId equals invoice.InvoiceId
                 join parcel in DbContext.Parcels on invoice.InvoiceId equals parcel.InvoiceId
                 join paymentMethod in DbContext.PaymentMethods on parcel.PaymentMethodId equals
                     paymentMethod.PaymentMethodId
                 where
                     (sale.CompanyId == companyId) &&
                     ((sale.SaleDate >= dateInterval.BeginDate) && (sale.SaleDate <= dateInterval.EndDate))
                 group parcel by paymentMethod.Name
                     into gParcel
                     select new
                                {
                                    Value = gParcel.Sum(p => p.Amount),
                                    Name = gParcel.Key
                                };
     return query.ToDataTable();
 }
 public Int32 GetSaleHistoryCount(Int32 companyId, Int32? customerId, Int32? representantId, Int32? receiptNumber,
                                  DateTimeInterval dateTimeInterval, bool? showCanceled, Int32? saleStatusId,
                                  String sortExpression, Int32 startRowIndex, Int32 maximumRows)
 {
     return
         GetSaleHistory(companyId, customerId, representantId, receiptNumber, dateTimeInterval, showCanceled, saleStatusId,
                        sortExpression, startRowIndex, maximumRows).Cast<Object>().Count();
 }
 public Int32 GetSaleHistoryCount(Int32 companyId, Int32? customerId, DateTimeInterval dateTimeInterval,
                                  bool? showCanceled)
 {
     return GetSaleHistory(companyId, customerId, dateTimeInterval, showCanceled).Rows.Count;
 }
        /// <summary>
        /// This method returns an extense list of the sales
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
#warning este método deve ser subistituido pelo método acima(linq)
        public DataTable GetSaleHistory(Int32 companyId, Int32? customerId, DateTimeInterval dateTimeInterval,
                                        bool? showCanceled)
        {
            string query = String.Empty;
            query =
                @" SELECT       
                                    s.SaleId, s.SaleDate,s.ReceiptId,Receipt.ReceiptNumber,Profile.Name AS EmployeeName,
                                    Customer.CustomerId,COALESCE(LegalEntityProfile.CompanyName,'') + COALESCE(cProfile.Name,'') AS Customer, 
                                    SUM(SaleItem.Quantity*SaleItem.UnitPrice) AS Total
                               FROM         
                                    Sale AS s INNER JOIN
                                    SaleItem ON SaleItem.SaleId = s.SaleId LEFT OUTER JOIN
                                    Product ON Product.ProductId = SaleItem.ProductId LEFT OUTER JOIN
                                    Customer ON s.CustomerId = Customer.CustomerId LEFT OUTER JOIN
                                    LegalEntityProfile ON Customer.LegalEntityProfileId = LegalEntityProfile.LegalEntityProfileId LEFT OUTER JOIN
                                    Profile as cProfile ON Customer.ProfileId = cProfile.ProfileId LEFT OUTER JOIN
                                    Employee ON s.VendorId = Employee.EmployeeId LEFT OUTER JOIN
                                    Profile ON Employee.ProfileId = Profile.ProfileId LEFT OUTER JOIN
                                    Receipt ON s.ReceiptId = Receipt.ReceiptId LEFT OUTER JOIN
                                    Invoice ON s.InvoiceId = Invoice.InvoiceId
                               WHERE     
                                    (s.CompanyId = @company) AND 
                                    (s.SaleDate >= @startDate) AND 
                                    (s.SaleDate <= @endDate)";

            if (customerId.HasValue)
            {
                query += "AND Customer.CustomerId = @customerId";
                DataManager.Parameters.Add("@customerId", customerId.Value);
            }


            query += " AND (s.IsCanceled = @showCanceled)";

            query +=
                "  GROUP BY s.SaleDate, LegalEntityProfile.CompanyName, Profile.Name,cProfile.Name, s.SaleId,s.ReceiptId,Receipt.ReceiptNumber,Customer.CustomerId";
            DataManager.Parameters.Add("@company", companyId);

            if (dateTimeInterval == null)
                dateTimeInterval = new DateTimeInterval(DateTime.Now.Sql2005MinValue(), DateTime.MaxValue);
            else
                dateTimeInterval.EndDate.AddDays(1);

            DataManager.Parameters.Add("@startDate", dateTimeInterval.BeginDate.Date);
            DataManager.Parameters.Add("@endDate", dateTimeInterval.EndDate.Date);

            DataManager.Parameters.Add("@showCanceled", showCanceled);
            return DataManager.ExecuteDataTable(query);
        }
        /// <summary>
        /// This method returns the history of sales of one company
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="customerId"></param>
        /// <param name="receiptNumber"></param>
        /// <param name="dateTimeInterval"></param>
        /// <param name="showCanceled"></param> 
        /// <returns></returns> 
        public IQueryable GetSaleHistory(Int32 companyId, Int32? customerId, Int32? representantId, Int32? receiptNumber,
                                         DateTimeInterval dateTimeInterval, bool? showCanceled, Int32? saleStatusId,
                                         string sortExpression, int startRowIndex, int maximumRows)
        {
            var query = from sale in DbContext.Sales
                        join customer in DbContext.Customers on sale.CustomerId equals customer.CustomerId into
                            gSale_customer
                        from customer in gSale_customer.DefaultIfEmpty()
                        join legalEntityProfile in DbContext.LegalEntityProfiles on customer.LegalEntityProfileId equals
                            legalEntityProfile.LegalEntityProfileId into gCustomerLegalEntityProfile
                        from legalEntityProfile in gCustomerLegalEntityProfile.DefaultIfEmpty()
                        join profile in DbContext.Profiles on customer.ProfileId equals profile.ProfileId into
                            gCustomerProfile
                        from profile in gCustomerProfile.DefaultIfEmpty()
                        join invoice in DbContext.Invoices on sale.InvoiceId equals invoice.InvoiceId into gSaleInvoice
                        from invoice in gSaleInvoice.DefaultIfEmpty()
                        join receipt in DbContext.Receipts on sale.ReceiptId equals receipt.ReceiptId into gReceipts
                        from receipt in gReceipts.DefaultIfEmpty()
                        join employee in DbContext.Employees on sale.VendorId equals employee.EmployeeId into
                            gEmployeeVendor
                        from employee in gEmployeeVendor.DefaultIfEmpty()
                        join employeeProfile in DbContext.Profiles on employee.ProfileId equals
                            employeeProfile.ProfileId
                        where
                            sale.SaleDate >= dateTimeInterval.BeginDate && sale.SaleDate <= dateTimeInterval.EndDate &&
                            sale.IsCanceled == showCanceled && sale.CompanyId == companyId
                        select new
                        {
                            sale.SaleStatusId,
                            sale.SaleId,
                            sale.SaleDate,
                            sale.IsCanceled,
                            sale.ReceiptId,
                            receipt.ReceiptNumber,
                            employeeName = employeeProfile.Name,
                            InvoiceValue = sale.InvoiceId != null ? sale.Invoice.Parcels.Sum(x => x.Amount) : 0,
                            CustomerId = (int?)customer.CustomerId,
                            RepresentantId = (int?)customer.RepresentantId,
                            customerName = (legalEntityProfile.CompanyName ?? (profile.Name ?? ""))
                        };

            if (customerId.HasValue)
                query = query.Where(customer => customer.CustomerId == customerId);

            if (receiptNumber.HasValue)
                query = query.Where(receipt => receipt.ReceiptNumber == receiptNumber);

            if (saleStatusId.HasValue)
                query = query.Where(sale => sale.SaleStatusId == saleStatusId);

            if (representantId.HasValue)
                query = query.Where(customer => customer.RepresentantId == representantId);


            return query.SortAndPage(sortExpression, startRowIndex, maximumRows, "SaleDate");
        }
        public IList GetProductsRankByDeposit(Int32 companyId, Int32? depositId, string sortExpression,
                                              DateTime startDate, DateTime endDate)
        {
            var dateTimeInterval = new DateTimeInterval(startDate, endDate);

            return GetProductsRankByDeposit(companyId, depositId, sortExpression, dateTimeInterval);
        }
 public Int32 GetPlansCount(DateTimeInterval dateTimeInterval, String name, string sortExpression,
                            int startRowIndex, int maximumRows)
 {
     return GetPlans(dateTimeInterval, name, sortExpression, startRowIndex, maximumRows).Cast<Object>().Count();
 }
        /// <summary>
        /// this method make the ABC Curve from product and deposi
        /// </summary>
        /// <param name="companyId">can't be null</param>
        /// <param name="depositId">can be null</param>
        /// <param name="sortExpression">can't be null</param>
        /// <param name="dateTimeInterval">can't be null</param>
        /// <returns>an IList of products</returns>
        public IList GetProductsRankByDeposit(Int32 companyId, Int32? depositId, string sortExpression,
                                              DateTimeInterval dateTimeInterval)
        {
            var pQuery = from products in GetAllProducts()
                         join saleItem in DbContext.SaleItems on products.ProductId equals saleItem.ProductId
                         join sale in DbContext.Sales on saleItem.SaleId equals sale.SaleId
                         where
                             products.CompanyId == companyId &&
                             (sale.SaleDate >= dateTimeInterval.BeginDate.Date &&
                              sale.SaleDate <= dateTimeInterval.EndDate.Date) && saleItem.UnitPrice > 0 &&
                             saleItem.UnitCost > 0
                         select new
                                    {
                                        productName = products.Name,
                                        quantity = saleItem.Quantity,
                                        unitPrice = saleItem.UnitPrice,
                                        depositId = sale.DepositId,
                                        productId = saleItem.ProductId,
                                        subTotal = saleItem.Quantity * saleItem.UnitPrice,
                                        subTotalCost = saleItem.Quantity * saleItem.UnitCost,
                                    };
            if (depositId != null)
                pQuery = pQuery.Where(x => x.depositId == depositId);

            Decimal? sumTotalQuery = pQuery.Sum(x => x.subTotal);
            var sumQuery = from query in pQuery
                           group query by new
                                              {
                                                  query.productId,
                                                  query.productName,
                                                  query.depositId
                                              }
                               into gQuery
                               select new
                                          {
                                              gQuery.Key.productName,
                                              quantity = gQuery.Sum(x => x.quantity),
                                              unitPrice = gQuery.Average(x => x.unitPrice),
                                              gQuery.Key.depositId,
                                              gQuery.Key.productId,
                                              sumSubtotal = gQuery.Sum(x => x.subTotal),
                                              sumSubTotalCost = gQuery.Sum(x => x.subTotalCost),
                                              sumTotal = sumTotalQuery,
                                              Percentage = (gQuery.Sum(x => x.subTotal) / sumTotalQuery) * 100,
                                              profit = (gQuery.Sum(x => x.subTotal) - gQuery.Sum(x => x.subTotalCost)),
                                              profitMargin =
                               ((gQuery.Sum(x => x.subTotal) - gQuery.Sum(x => x.subTotalCost)) /
                                gQuery.Sum(x => x.subTotalCost)) * 100
                                          };

            return sumQuery.OrderByDescending(x => x.sumSubtotal).Sort(sortExpression).ToList();
        }
        public IQueryable<Budget> GetBudgets(Int32 companyId, Int32? customerId, DateTimeInterval interval)
        {
            IQueryable<Budget> query = from budget in DbContext.Budgets
                                       where budget.CompanyId == companyId
                                       select budget;

            if (customerId.HasValue)
                query = query.Where(c => c.CustomerId == customerId);

            if (interval != null)
                query = query.Where(b => b.CreatedDate >= interval.BeginDate && b.CreatedDate <= interval.EndDate);

            return query;
        }