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;
}