public async Task <ActionResult> Export([FromBody] OpportunityReport_OpportunityReportFilterDTO filter)
{
if (UnAuthorization)
{
return(Forbid());
}
var info_vn = System.Globalization.CultureInfo.GetCultureInfo("vi-VN");
if (!ModelState.IsValid)
{
throw new BindException(ModelState);
}
// Lấy danh sách Opportunity
var query = from t1 in DataContext.Opportunity select t1;
if (filter.OpportunityName != null)
{
query = query.Where(p => p.Name, filter.OpportunityName);
}
if (filter.OpportunityAmount != null)
{
query = query.Where(p => p.Amount, filter.OpportunityAmount);
}
if (filter.OpportunityItemId != null)
{
query = query.Where(p => p.Amount, filter.OpportunityAmount);
}
if (filter.OpportunityCompanyId != null)
{
query = query.Where(p => p.CompanyId, filter.OpportunityCompanyId);
}
if (filter.OpportunitySaleStageId != null)
{
query = query.Where(p => p.SaleStageId, filter.OpportunitySaleStageId);
}
if (filter.OpportunityProbabilityId != null)
{
query = query.Where(p => p.ProbabilityId, filter.OpportunityProbabilityId);
}
if (filter.OpportunityClosingDate != null)
{
query = query.Where(p => p.ClosingDate, filter.OpportunityClosingDate);
}
if (filter.OpportunityCreatedAt != null)
{
query = query.Where(p => p.CreatedAt, filter.OpportunityCreatedAt);
}
if (filter.AppUserId != null)
{
query = query.Where(p => p.AppUserId, filter.AppUserId);
}
//Tìm kiếm theo OpportunityItemId
if (filter.OpportunityItemId != null)
{
if (filter.OpportunityItemId.Equal.HasValue)
{
query = from q in query
join ar in DataContext.OpportunityItemMapping on q.Id equals ar.OpportunityId
where ar.ItemId == filter.OpportunityItemId.Equal.Value
select q;
}
}
var data = query.Distinct().OrderBy(p => p.CreatedAt).ThenBy(p => p.Name).Skip(filter.Skip).Take(filter.Take).Select(p => new OpportunityReport_OpportunityDTO
{
Id = p.Id,
Name = p.Name,
ProbabilityName = p.Probability == null ? "" : p.Probability.Name,
Amount = p.Amount == null ? "" : p.Amount.Value.ToString(),
ClosingDate = p.ClosingDate,
CreatedAt = p.CreatedAt,
SaleStageName = p.SaleStage == null ? "" : p.SaleStage.Name,
CompanyName = p.Company == null ? "" : p.Company.Name,
CountItem = p.OpportunityItemMappings == null ? "" : p.OpportunityItemMappings.Count().ToString(),
TotalRevenueOfItem = p.OpportunityItemMappings == null ? "" : p.OpportunityItemMappings.Sum(p => (p.RequestQuantity * p.SalePrice)).ToString(),
AppUserName = p.AppUser == null ? "" : p.AppUser.DisplayName,
ResultName = p.PotentialResult == null ? "" : p.PotentialResult.Name,
ContactName = p.OpportunityContactMappings == null ? "" : string.Join(',', p.OpportunityContactMappings.Select(p => p.Contact.Name)),
ForecastAmount = p.ForecastAmount.HasValue ? p.ForecastAmount.ToString() : "",
LeadSourceName = p.LeadSource == null ? "" : p.LeadSource.Name,
OpportunityItemMappings = p.OpportunityItemMappings == null ? null : p.OpportunityItemMappings.Select(p => new OpportunityReport_OpportunityItemMappingDTO
{
OpportunityId = p.OpportunityId,
ItemId = p.ItemId,
UnitOfMeasureId = p.UnitOfMeasureId,
Quantity = p.Quantity,
DiscountPercentage = p.DiscountPercentage,
RequestQuantity = p.RequestQuantity,
PrimaryPrice = p.PrimaryPrice,
SalePrice = p.SalePrice,
Discount = p.Discount,
VAT = p.VAT,
VATOther = p.VATOther,
Amount = p.Amount,
Factor = p.Factor,
Item = p.Item == null ? null : new OpportunityReport_ItemDTO
{
Id = p.Item.Id,
Name = p.Item.Name
},
UnitOfMeasure = p.UnitOfMeasure == null ? null : new OpportunityReport_UnitOfMeasureDTO
{
Name = p.UnitOfMeasure.Name
}
}).ToList()
}).ToList();
OpportunityReport_OpportunityTotalDTO Result = new OpportunityReport_OpportunityTotalDTO();
Result.TotalAmount = data.Sum(p => string.IsNullOrEmpty(p.Amount) ? 0 : decimal.Parse(p.Amount)).ToString();
Result.TotalItem = data.Sum(p => string.IsNullOrEmpty(p.CountItem) ? 0 : decimal.Parse(p.CountItem)).ToString();
Result.TotalForecastAmount = data.Sum(p => string.IsNullOrEmpty(p.ForecastAmount) ? 0 : decimal.Parse(p.ForecastAmount)).ToString();
var maxDate = data.Max(p => p.CreatedAt).HasValue ? data.Max(p => p.CreatedAt).Value : default(DateTime);
var minDate = data.Min(p => p.CreatedAt).HasValue ? data.Min(p => p.CreatedAt).Value : default(DateTime);
DateTime Start = filter.OpportunityCreatedAt?.GreaterEqual == null ?
minDate :
filter.OpportunityCreatedAt.GreaterEqual.Value;
DateTime End = filter.OpportunityCreatedAt?.LessEqual == null ?
maxDate :
filter.OpportunityCreatedAt.LessEqual.Value;
//Lấy danh sách Opportunity
var LtsItemIds = new List <long>();
var OpportunityReportExports = new List <OpportunityReportExport>();
foreach (var opportunity in data.OrderByDescending(p => p.CreatedAt))
{
if (opportunity.OpportunityItemMappings.Any())
{
foreach (var item in opportunity.OpportunityItemMappings)
{
OpportunityReportExport obj = new OpportunityReportExport();
obj.Name = opportunity.Name;
obj.CompanyName = opportunity.CompanyName;
obj.ContactName = opportunity.ContactName;
obj.ClosingDate = opportunity.ClosingDate.HasValue ? opportunity.ClosingDate.Value.ToString("dd/MM/yyyy") : "";
obj.ProbabilityName = opportunity.ProbabilityName;
obj.ItemName = item.Item == null ? null : item.Item.Name;
obj.UnitOfMeasureName = item.UnitOfMeasure == null ? null : item.UnitOfMeasure.Name;
obj.SalePrice = item.SalePrice.HasValue ? string.Format(info_vn, "{0:c}", item.SalePrice.Value) : "";
obj.RequestQuantity = item.RequestQuantity;
LtsItemIds.Add(item.ItemId);
obj.SaleStageName = opportunity.SaleStageName;
obj.Amount = string.Format(info_vn, "{0:c}", string.IsNullOrEmpty(opportunity.Amount) ? (decimal)0.00 : decimal.Parse(opportunity.Amount));
obj.ForecastAmount = string.Format(info_vn, "{0:c}", string.IsNullOrEmpty(opportunity.ForecastAmount) ? (decimal)0.00 : decimal.Parse(opportunity.ForecastAmount));
obj.LeadSourceName = opportunity.LeadSourceName;
obj.AppUserName = opportunity.AppUserName;
obj.Result = opportunity.ResultName;
OpportunityReportExports.Add(obj);
}
}
else
{
OpportunityReportExport obj = new OpportunityReportExport();
obj.Name = opportunity.Name;
obj.CompanyName = opportunity.CompanyName;
obj.ContactName = opportunity.ContactName;
obj.ClosingDate = opportunity.ClosingDate.HasValue ? opportunity.ClosingDate.Value.ToString("dd/MM/yyyy") : "";
obj.ProbabilityName = opportunity.ProbabilityName;
obj.SaleStageName = opportunity.SaleStageName;
obj.Amount = string.Format(info_vn, "{0:c}", string.IsNullOrEmpty(opportunity.Amount) ? (decimal)0.00 : decimal.Parse(opportunity.Amount));
obj.ForecastAmount = string.Format(info_vn, "{0:c}", string.IsNullOrEmpty(opportunity.ForecastAmount) ? (decimal)0.00 : decimal.Parse(opportunity.ForecastAmount));
obj.LeadSourceName = opportunity.LeadSourceName;
obj.AppUserName = opportunity.AppUserName;
obj.Result = opportunity.ResultName;
OpportunityReportExports.Add(obj);
}
}
var STT = 1;
foreach (var item in OpportunityReportExports)
{
item.STT = STT;
STT++;
}
string path = "Templates/OpportunityReport.xlsx";
byte[] arr = System.IO.File.ReadAllBytes(path);
MemoryStream input = new MemoryStream(arr);
MemoryStream output = new MemoryStream();
dynamic Data = new ExpandoObject();
Data.Start = Start.AddHours(CurrentContext.TimeZone).ToString("dd-MM-yyyy");
Data.End = End.AddHours(CurrentContext.TimeZone).ToString("dd-MM-yyyy");
Data.OpportunityReportExports = OpportunityReportExports;
Data.TotalAmount = string.Format(info_vn, "{0:c}", decimal.Parse(Result.TotalAmount));
Data.TotalItem = LtsItemIds.Distinct().Count();
Data.TotalForecastAmount = String.Format(info_vn, "{0:c}", decimal.Parse(Result.TotalForecastAmount));
using (var document = StaticParams.DocumentFactory.Open(input, output, "xlsx"))
{
document.Process(Data);
};
return(File(output.ToArray(), "application/octet-stream", "BaoCaoCoHoi.xlsx"));
}
public async Task <ActionResult <OpportunityReport_OpportunityTotalDTO> > Total([FromBody] OpportunityReport_OpportunityReportFilterDTO filter)
{
if (UnAuthorization)
{
return(Forbid());
}
if (!ModelState.IsValid)
{
throw new BindException(ModelState);
}
List <long> ItemIds = new List <long>();
// Lấy danh sách Opportunity
var query = from t1 in DataContext.Opportunity select t1;
if (filter.OpportunityName != null)
{
query = query.Where(p => p.Name, filter.OpportunityName);
}
if (filter.OpportunityAmount != null)
{
query = query.Where(p => p.Amount, filter.OpportunityAmount);
}
if (filter.OpportunityItemId != null)
{
query = query.Where(p => p.Amount, filter.OpportunityAmount);
}
if (filter.OpportunityCompanyId != null)
{
query = query.Where(p => p.CompanyId, filter.OpportunityCompanyId);
}
if (filter.OpportunitySaleStageId != null)
{
query = query.Where(p => p.SaleStageId, filter.OpportunitySaleStageId);
}
if (filter.OpportunityProbabilityId != null)
{
query = query.Where(p => p.ProbabilityId, filter.OpportunityProbabilityId);
}
if (filter.OpportunityClosingDate != null)
{
query = query.Where(p => p.ClosingDate, filter.OpportunityClosingDate);
}
if (filter.OpportunityCreatedAt != null)
{
query = query.Where(p => p.CreatedAt, filter.OpportunityCreatedAt);
}
if (filter.AppUserId != null)
{
query = query.Where(p => p.AppUserId, filter.AppUserId);
}
//Tìm kiếm theo OpportunityItemId
if (filter.OpportunityItemId != null)
{
if (filter.OpportunityItemId.Equal.HasValue)
{
query = from q in query
join ar in DataContext.OpportunityItemMapping on q.Id equals ar.OpportunityId
where ar.ItemId == filter.OpportunityItemId.Equal.Value
select q;
}
}
var data = query.Distinct().OrderBy(p => p.ClosingDate).ThenBy(p => p.Name)
//.Skip(filter.Skip)
//.Take(filter.Take)
.Select(p => new OpportunityReport_OpportunityDTO
{
Id = p.Id,
Name = p.Name,
ProbabilityName = p.Probability == null ? "" : p.Probability.Name,
Amount = p.Amount == null ? "" : p.Amount.Value.ToString(),
ClosingDate = p.ClosingDate,
SaleStageName = p.SaleStage == null ? "" : p.SaleStage.Name,
CompanyName = p.Company == null ? "" : p.Company.Name,
CountItem = p.OpportunityItemMappings == null ? "" : p.OpportunityItemMappings.Count().ToString(),
TotalRevenueOfItem = p.OpportunityItemMappings == null ? "" : p.OpportunityItemMappings.Sum(p => (p.RequestQuantity * p.SalePrice)).ToString(),
OpportunityItemMappings = p.OpportunityItemMappings == null ? null : p.OpportunityItemMappings.Select(p => new OpportunityReport_OpportunityItemMappingDTO
{
OpportunityId = p.OpportunityId,
ItemId = p.ItemId,
UnitOfMeasureId = p.UnitOfMeasureId,
Quantity = p.Quantity,
DiscountPercentage = p.DiscountPercentage,
RequestQuantity = p.RequestQuantity,
PrimaryPrice = p.PrimaryPrice,
SalePrice = p.SalePrice,
Discount = p.Discount,
VAT = p.VAT,
VATOther = p.VATOther,
Amount = p.Amount,
Factor = p.Factor,
Item = p.Item == null ? null : new OpportunityReport_ItemDTO
{
Id = p.Item.Id,
Name = p.Item.Name
},
UnitOfMeasure = p.UnitOfMeasure == null ? null : new OpportunityReport_UnitOfMeasureDTO
{
Name = p.UnitOfMeasure.Name
}
}).ToList()
}).ToList();
// Tính tổng số sản phẩm khác nhau
foreach (var p in data)
{
var ids = p.OpportunityItemMappings.Select(p => p.ItemId);
ItemIds.AddRange(ids);
}
OpportunityReport_OpportunityTotalDTO OpportunityReport_OpportunityTotalDTO = new OpportunityReport_OpportunityTotalDTO();
OpportunityReport_OpportunityTotalDTO.TotalAmount = data.Sum(p => string.IsNullOrEmpty(p.Amount) ? 0 : decimal.Parse(p.Amount)).ToString();
OpportunityReport_OpportunityTotalDTO.TotalItem = ItemIds.Distinct().Count().ToString();
OpportunityReport_OpportunityTotalDTO.TotalRevenue = data.Sum(p => string.IsNullOrEmpty(p.TotalRevenueOfItem) ? 0 : decimal.Parse(p.TotalRevenueOfItem)).ToString();
return(OpportunityReport_OpportunityTotalDTO);
}
