//
// GET: /Administrator/MyOffice/
public ActionResult Index(IndexProductInStorageModel productInStorageModel)
{
var products = _productRepository.Search("").ToList();
var storages = _storageRepository.Search("").Where(p => WorkContext.MyStorages.Contains(p.StorageId)).ToList();
var model = new IndexProductInStorageModel
{
Products = products,
Storages = storages,
GroupByProduct = productInStorageModel.GroupByProduct,
GroupByStorage = productInStorageModel.GroupByStorage,
ProductsSelected = productInStorageModel.ProductsSelected,
StoragesSelected = productInStorageModel.StoragesSelected
};
int[] productsArr = new int[0];
if (!string.IsNullOrEmpty(productInStorageModel.ProductsSelected))
{
productsArr = productInStorageModel.ProductsSelected.Split(',').Select(int.Parse).ToArray();
}
int[] storagesArr = new int[0];
if (!string.IsNullOrEmpty(productInStorageModel.StoragesSelected))
{
storagesArr = productInStorageModel.StoragesSelected.Split(',').Select(int.Parse).ToArray();
}
var filter = new ReportFilterModel
{
ListProducts = productsArr,
FromListStorages = storagesArr
};
model.ProductInStorageModels = GridModel(filter);
return(View(model));
}
public HttpResponseMessage GetReportExcel(ReportFilterModel model)
{
try
{
var report = _reportService.GetReportById(model.ReportId);
var data = GetReportData(model);
string filePath = ExcelUtility.createXSL(data, report.Title);
byte[] file = System.IO.File.ReadAllBytes(filePath);
var response = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(file)
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = filePath
};
return(response);
}
catch (Exception ex)
{
var response = new HttpResponseMessage(HttpStatusCode.InternalServerError);
return(response);
}
}
public IHttpActionResult GetmprRequisitionfilters()
{
ReportFilterModel status = new ReportFilterModel();
status = _paBusenessAcess.GetmprRequisitionfilters();
return(Ok(status));
}
private List <ProductInStorageModel> GridModel(ReportFilterModel filterModel)
{
var listProducts = new int[1];
var isGetAllListProducts = true;
//Filter by deliver to
if (filterModel.ListProducts != null && filterModel.ListProducts.Count() > 0)
{
listProducts = filterModel.ListProducts;
isGetAllListProducts = false;
}
var listFromListStorages = new int[1];
var isGetAllFromListStorages = true;
//Filter by deliver to
if (filterModel.FromListStorages != null && filterModel.FromListStorages.Count() > 0)
{
listFromListStorages = filterModel.FromListStorages;
isGetAllFromListStorages = false;
}
var model = from x in Repository.Search(filterModel.search).Where(p =>
(listProducts.Contains(p.ProductId) || (isGetAllListProducts)) &&
(listFromListStorages.Contains(p.StorageId) || (isGetAllFromListStorages && WorkContext.MyStorages.Contains(p.StorageId))))
select new ProductInStorageModel
{
StorageId = x.StorageId,
ProductId = x.ProductId,
ProductName = x.Product.ProductName,
StorageName = x.Storage.StorageName,
Amount = x.Amount,
ProductInStorageId = x.ProductInStorageId
};
return(model.ToList());
}
public ActionResult GridModel(ReportFilterModel filterModel)
{
var listOffices = new int[1];
var isGetAllListOffices = true;
//Filter by deliver to
if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0)
{
listOffices = filterModel.ListOffices;
isGetAllListOffices = false;
}
var model = Repository.Search(filterModel.search).Where(p => WorkContext.MyOffices.Contains(p.MyOfficeId) &&
(listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) &&
((!WorkContext.Roles.Contains(RoleType.Administrator) && !p.HideWithNormalUser) || WorkContext.Roles.Contains(RoleType.Administrator))).OrderByDescending(p => p.CustomerId);
var gridModel = new GridModel <CustomerModel>
{
Data = model.Select(x => new CustomerModel
{
Address = x.Address,
CustomerId = x.CustomerId,
CustomerName = x.CustomerName,
PhoneNumber = x.PhoneNumber,
Fax = x.Fax,
CustomerCode = x.CustomerCode
})
};
return(new JsonResult
{
Data = gridModel
});
}
public async Task <ActionResult <IEnumerable <ParameterisedReportModel> > > GetParametrisedReport(
[FromQuery] ReportFilterModel filterModel)
{
IEnumerable <ParameterisedReportModel> reportModel = await _insightsService.GetParameterisedReportAsync(filterModel);
return(Ok(reportModel));
}
public ActionResult GridModel(ReportFilterModel filterModel)
{
//Default get all
var fromDate = DateTime.Today;
var isAllFromDate = true;
var toDate = DateTime.Today;
var isAllToDate = true;
//Filter by date
if (filterModel.FromDate.HasValue)
{
fromDate = filterModel.FromDate.Value.Date;
isAllFromDate = false;
}
if (filterModel.ToDate.HasValue)
{
toDate = filterModel.ToDate.Value.Date.AddDays(1);
isAllToDate = false;
}
var listOffices = new int[1];
var isGetAllListOffices = true;
//Filter by deliver to
if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0)
{
listOffices = filterModel.ListOffices;
isGetAllListOffices = false;
}
var model = from x in Repository.Search("").Where(p =>
(listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) &&
((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate)))
select new OrderDeliveryPackageModel
{
OrderDeliveryPackageId = x.OrderDeliveryPackageId,
CreateDate = x.CreateDate,
Note = x.Note,
ShippingFee = x.ShippingFee,
MyOfficeName = x.MyOffice.OfficeName
};
double total = 0;
try
{
total = Convert.ToDouble(model.Sum(p => p.ShippingFee));
}
catch
{
}
Session["TotalShippingFee"] = String.Format("{0:0,0}", total);
var gridModel = new GridModel <OrderDeliveryPackageModel>
{
Data = model
};
return(new JsonResult
{
Data = gridModel
});
}
public ActionResult GetRevenue(ReportFilterModel filterModel)
{
decimal doanhThu = 0;
decimal loiNhuan = 0;
decimal giaVon = 0;
decimal chiPhi = 0;
//Default get all
var fromDate = DateTime.Today;
var isAllFromDate = true;
var toDate = DateTime.Today;
var isAllToDate = true;
//Filter by date
if (filterModel.FromDate.HasValue)
{
fromDate = filterModel.FromDate.Value.Date;
isAllFromDate = false;
}
if (filterModel.ToDate.HasValue)
{
toDate = filterModel.ToDate.Value.Date.AddDays(1);
isAllToDate = false;
}
var listOffices = new int[1];
var isGetAllListOffices = true;
//Filter by deliver to
if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0)
{
listOffices = filterModel.ListOffices;
isGetAllListOffices = false;
}
var orders = _orderRepository.Search("")
.Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) &&
((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate)) && p.Paid).Include(p => p.OrderDetails).ToList();
foreach (var item in orders)
{
doanhThu += item.TotalCost;
}
//giá gốc
foreach (var item in orders)
{
var donHangTheoGiaVon = GetOrgiranalCostForAnOrder(item);
giaVon += donHangTheoGiaVon;
}
//chi phí
var expenses = _expenseRepository.Search("")
.Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) &&
((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate))).ToList();
foreach (var item in expenses)
{
chiPhi += item.Cost;
}
loiNhuan = doanhThu - giaVon - chiPhi;
return(Json(new { DoanhThu = String.Format("{0:0,0}", doanhThu), LoiNhuan = String.Format("{0:0,0}", loiNhuan), GiaVon = String.Format("{0:0,0}", giaVon), ChiPhi = String.Format("{0:0,0}", chiPhi) }));
}
public ActionResult GridModel(ReportFilterModel filterModel)
{
//Default get all
var fromDate = DateTime.Today;
var isAllFromDate = true;
var toDate = DateTime.Today;
var isAllToDate = true;
//Filter by date
if (filterModel.FromDate.HasValue)
{
fromDate = filterModel.FromDate.Value.Date;
isAllFromDate = false;
}
if (filterModel.ToDate.HasValue)
{
toDate = filterModel.ToDate.Value.Date.AddDays(1);
isAllToDate = false;
}
var toListStorages = new int[1];
var isGetAllToStorages = true;
//Filter by deliver to
if (filterModel.ToListStorages != null && filterModel.ToListStorages.Count() > 0)
{
toListStorages = filterModel.ToListStorages;
isGetAllToStorages = false;
}
var model = from x in Repository.Search(filterModel.search).Where(p => WorkContext.MyStorages.Contains(p.ToStorageId) &&
(toListStorages.Contains(p.ToStorageId) || (isGetAllToStorages && WorkContext.MyStorages.Contains(p.ToStorageId))) &&
((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate)))
join m in _userRepository.GetAll() on x.ApproveFromManagerId equals m.UserId into m1
from m2 in m1.DefaultIfEmpty()
join s in _userRepository.GetAll() on x.ApproveFromStorageStaffId equals s.UserId into s1
from s2 in s1.DefaultIfEmpty()
select new ImportTrackModel
{
ImportTrackId = x.ImportTrackId,
CreateDate = x.CreateDate,
Note = x.Note,
StorageId = x.ToStorageId,
ImportUserName = x.CreateUser.FirstName + " " + x.CreateUser.LastName,
ApproveFromManagerName = (m2 == null ? String.Empty : m2.FirstName + " " + m2.LastName),
ApproveFromStorageStaffName = (s2 == null ? String.Empty : s2.FirstName + " " + s2.LastName),
};
var gridModel = new GridModel <ImportTrackModel>
{
Data = model
};
return(new JsonResult
{
Data = gridModel
});
}
public ActionResult GridModel(ReportFilterModel filterModel)
{
//Default get all
var fromDate = DateTime.Today;
var isAllFromDate = true;
var toDate = DateTime.Today;
var isAllToDate = true;
//Filter by date
if (filterModel.FromDate.HasValue)
{
fromDate = filterModel.FromDate.Value.Date;
isAllFromDate = false;
}
if (filterModel.ToDate.HasValue)
{
toDate = filterModel.ToDate.Value.Date.AddDays(1);
isAllToDate = false;
}
var listOffices = new int[1];
var isGetAllListOffices = true;
//Filter by deliver to
if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0)
{
listOffices = filterModel.ListOffices;
isGetAllListOffices = false;
}
var model = from x in Repository.Search("").Where(p =>
(listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) &&
((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate)))
select new DebtModel
{
DebtId = x.DebtId,
CreateDate = x.CreateDate,
Note = x.Note,
FromDate = x.FromDate,
ToDate = x.ToDate,
Total = x.Total,
Paid = x.Paid,
PaidLeft = x.Total - x.Paid,
MyOfficeName = x.MyOffice.OfficeName
};
var gridModel = new GridModel <DebtModel>
{
Data = model
};
return(new JsonResult
{
Data = gridModel
});
}
private DataSet GetReportData(ReportFilterModel model)
{
string proc = "prc_ReportExecute";
string query = string.Empty;
string prcName = "prc_report";
string startDate = "";
string endDate = "";
if (model.Template.ToUpper() == "ISYEARMONTH")
{
startDate = new DateTime(model.Year, model.Month, 1).ToString();
endDate = new DateTime(model.Year, model.Month, DateTime.DaysInMonth(model.Year, model.Month)).ToString();
}
if (model.Template.ToUpper() == "ISDATE")
{
if (!string.IsNullOrEmpty(model.From) && !string.IsNullOrEmpty(model.To))
{
startDate = Convert.ToDateTime(model.From).ToString();
endDate = Convert.ToDateTime(model.To).ToString();
}
}
query = string.Format("{0} '{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}'",
prcName,
model.ClientId,
startDate,
endDate,
model.CallGapType,
model.AgeSlab,
model.FeildFeedback,
model.OverallSummary,
model.MisType,
model.Page == 1 ? 0 : (model.Page - 1) * model.PageSize,
model.PageSize,
"ASC",
model.ReportId
);
SqlParameter[] param =
{
new SqlParameter("@CurrentPage", "1"),
new SqlParameter("@PageSize", "-1"),
new SqlParameter("@Query", query),
};
DataSet data = _spService.ExcuteSpAnonmious(proc, param, 3);
return(data);
}
/// <summary>
/// 根据filterId获取filter对象
/// </summary>
/// <param name="filterId"></param>
/// <returns></returns>
public ReportFilterModel GetFilter(long filterId)
{
ReportFilterModel ft = new ReportFilterModel();
var filter = _reportFilterRepository.FirstOrDefault(filterId);
if (filter != null)
{
ft = AutoMapper.Mapper.Map(filter, ft);
}
return(ft);
}
public ActionResult OrderGridModel(ReportFilterModel filterModel)
{
//Default get all
var fromDate = DateTime.Today;
var isAllFromDate = true;
var toDate = DateTime.Today;
var isAllToDate = true;
//Filter by date
if (filterModel.FromDate.HasValue)
{
fromDate = filterModel.FromDate.Value.Date;
isAllFromDate = false;
}
if (filterModel.ToDate.HasValue)
{
toDate = filterModel.ToDate.Value.Date.AddDays(1);
isAllToDate = false;
}
var listOffices = new int[1];
var isGetAllListOffices = true;
//Filter by deliver to
if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0)
{
listOffices = filterModel.ListOffices;
isGetAllListOffices = false;
}
var model = _orderRepository.Search("")
.Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) &&
((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate)) && p.Paid)
.Include(p => p.MyOffice).Include(p => p.Customer);
var gridModel = new GridModel <OrderModel>
{
Data = model.Select(x => new OrderModel
{
OrderId = x.OrderId,
CreateDate = x.CreateDate,
Status = x.Status,
TotalCost = x.TotalCost,
CustomerName = x.Customer.CustomerName,
MyOfficeIName = x.MyOffice.OfficeName,
Paid = x.Paid
})
};
return(new JsonResult
{
Data = gridModel
});
}
public ActionResult ExpenseGridModel(ReportFilterModel filterModel)
{
//Default get all
var fromDate = DateTime.Today;
var isAllFromDate = true;
var toDate = DateTime.Today;
var isAllToDate = true;
//Filter by date
if (filterModel.FromDate.HasValue)
{
fromDate = filterModel.FromDate.Value.Date;
isAllFromDate = false;
}
if (filterModel.ToDate.HasValue)
{
toDate = filterModel.ToDate.Value.Date.AddDays(1);
isAllToDate = false;
}
var listOffices = new int[1];
var isGetAllListOffices = true;
//Filter by deliver to
if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0)
{
listOffices = filterModel.ListOffices;
isGetAllListOffices = false;
}
var model = _expenseRepository.Search("")
.Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) &&
((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate)));
var gridModel = new GridModel <ExpenseModel>
{
Data = model.Select(x => new ExpenseModel
{
ExpenseId = x.ExpenseId,
CreateDate = x.CreateDate,
Note = x.Note,
Cost = x.Cost,
ExpenseName = x.ExpenseName
})
};
return(new JsonResult
{
Data = gridModel
});
}
public IHttpActionResult GetReport(ReportFilterModel model)
{
try
{
var report = _reportService.GetReportById(model.ReportId);
var data = GetReportData(model);
return(Ok(new ArrayList {
data, report.Title
}));
}
catch (Exception ex)
{
return(ResponseMessage(Request.CreateResponse(HttpStatusCode.InternalServerError, ex.Message)));
}
}
public async Task <IActionResult> Get([FromQuery] ReportFilterModel filter)
{
var reports = await this.reportService.GetAll(
filter.Keyword,
filter.Email,
filter.Name,
filter.LocationId,
filter.IsPositive,
orderBy : filter.OrderByEnum,
page : filter.Page,
pageSize : filter.PageSize);
var models = reports.ToModels(this.filesHelper, Url.Content, this.generalSettings.SmallPictureWidth, this.generalSettings.SmallPictureHeight);
return(this.Ok(models, reports.HasNextPage, reports.TotalCount));
}
public HttpResponseMessage GetReportPdf(ReportFilterModel model)
{
try
{
var report = _reportService.GetReportById(model.ReportId);
var data = GetReportData(model);
// report start
var parameters = new List <ReportParameters>();
parameters.Add(new ReportParameters()
{
Key = "CompanyName",
Value = ""
});
parameters.Add(new ReportParameters()
{
Key = "Title",
Value = report.Title
});
string fileName = GenerateReport.GetReportWithDs(report.PdfReportName, "Report", parameters, data);
byte[] file = System.IO.File.ReadAllBytes(HttpContext.Current.Server.MapPath("~/Report/") + fileName);
var response = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(file)
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/pdf");
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = fileName
};
return(response);
}
catch (Exception ex)
{
var response = new HttpResponseMessage(HttpStatusCode.InternalServerError);
return(response);
}
}
public IActionResult Build([Bind] ReportFilterModel filter)
{
var user = _userFactory.GetUser(User.Identity.Name);
if (user == null)
{
throw new ApplicationException("No record of such user");
}
int userId = user.Id;
int?partnerId = null;
if (User.IsInRole(WebConstants.PartnerRole))
{
if (user == null)
{
throw new ApplicationException("No record of such user");
}
if (user.PartnerId == null)
{
throw new ApplicationException("The current user has the permissions of a Partner, but isn't tied to any");
}
partnerId = user.PartnerId;
}
var data = _reportFactory.GenerateReportData(filter);
// now export to excel (and save everything to db !!!)
var reportPath = ReportHelper.Export(data, filter);
_reportFactory.CreateReport(new Services.Models.ReportModel()
{
Name = Path.GetFileNameWithoutExtension(reportPath),
CreatedDate = DateTime.UtcNow,
DiscountsUserId = userId,
FilterJson = JsonConvert.SerializeObject(filter),
PartnerId = partnerId,
PathToFile = reportPath
});
return(Index());
}
public ActionResult CreateReportFilter(long reportId)
{
var report = _reportRepository.GetById(reportId);
var maxDisplayOrder = report.ReportFilters.Max(p => p.DisplayOrder) ?? 0;
var reportFilter = new ReportFilter
{
ReportId = reportId,
IsNew = true,
DisplayOrder = maxDisplayOrder + 1
};
_reportFilterRepository.InsertAndCommit(reportFilter);
var model = new ReportFilterModel();
model = reportFilter.ToModel();
var html = this.ReportFilterPanel(model);
return(Json(new { Id = reportFilter.Id, Html = html }));
}
public List <ExportData> GetExportData(ReportFilterModel reportFilterModel)
{
var ret = new List <ExportData>();
var fromStr = reportFilterModel.From.ToString("dd/MM/yyyy");
var toStr = reportFilterModel.To.ToString("dd/MM/yyyy");
var query = $@"set dateformat dmy
select ed.Id, ed.ExportName, ed.ExportDate, ed.UserFullName, l.Name from ExportData ed left join Locals l on ed.LocalId = l.Id
where l.Id = {reportFilterModel.LocalId} and ed.ExportDate between '{fromStr}' and '{toStr}'";
var res = DbExecutor.Execute(query);
for (int i = 0; i < res.Count; i++)
{
var ht = res[i] as Hashtable;
var toAdd = new ExportData(ht);
ret.Add(toAdd);
}
return(ret);
}
public object PaymentMonthReport(ReportFilterModel model)
{
using (DbContext _db = new DbContext())
{
var user = ApiCommon.GetUser(HttpContext.Current.User.Identity, _db);
if (user != null && model != null)
{
if (model.Month == 0 || model.Year == 0 || model.Month > 12)
{
return(new HttpActionResult(HttpStatusCode.BadRequest, @"{""message"":""Please enter a valid month and year.""}"));
}
using (var dapper = new Helpers.Dapper())
{
var payments = dapper.Connection.Query($"select * From Payments Where month(TransDate)=@month and year(TransDate)=@year and PaymentStatus=@status order by TransDate {model.SortOrder}", new { month = model.Month, year = model.Year, status = model.PaymentStatus });
return(payments.Select(i => new { i.Amount, i.CreditCard_CardId, i.PaymentStatus, i.TransDate }));;
}
}
}
return(new HttpActionResult(HttpStatusCode.Unauthorized, @"{""message"":""User doesn't exist.""}"));
}
public ActionResult SaveReportFilter(ReportFilterModel model)
{
if (ModelState.IsValid)
{
var reportFilter = _reportFilterRepository.GetById(model.Id);
if (string.IsNullOrEmpty(reportFilter.Name))
{
var filter = _filterRepository.GetById(model.FilterId);
model.Name = filter.Name + "_" + Guid.NewGuid();
}
//always set IsNew to false when saving
reportFilter.IsNew = false;
reportFilter = model.ToEntity(reportFilter);
_reportFilterRepository.UpdateAndCommit(reportFilter);
return(new NullJsonResult());
}
else
{
return(Json(new { Errors = ModelState.Errors().ToHtmlString() }));
}
}
public IEnumerable <ReportRecord> GenerateReportData(ReportFilterModel filter)
{
var entries = _usedActionservice.GetUsedActions().AsQueryable().ApplyFilter(filter);
return(entries.Select(x => new
{
action = filter.GroupByAction ? x.Action.Name : null,
user = filter.GroupByUser ? x.User.UserName : null,
partner = filter.GroupByPartner ? x.Partner.Name : null,
partnerType = filter.GroupByPartnerType ? x.Partner.PartnerType.Name : null,
originalValue = x.OriginalValue,
actionValue = x.ActionValue
}).GroupBy(g => new { g.action, g.user, g.partner, g.partnerType })
.Select(g => new ReportRecord()
{
ActionName = g.Key.action,
UserName = g.Key.user,
PartnerName = g.Key.partner,
PartnerTypeName = g.Key.partnerType,
OriginalValue = g.Sum(x => x.originalValue ?? 0),
ActionValue = g.Sum(x => x.actionValue)
}).AsEnumerable());
}
public Task <IEnumerable <ParameterisedReportModel> > GetParameterisedReportAsync(ReportFilterModel reportFilters)
{
return(_http.GetFromJsonAsync <IEnumerable <ParameterisedReportModel> >($"{insightsBaseUrl}/ParametrisedReport?{reportFilters.QueryString}"));
}
public string ReportFilterPanel(ReportFilterModel model)
{
var html = this.RenderPartialViewToString("_ReportFilterDetails", model);
return(html);
}
public ActionResult ImportFromOtherStorageModel(ReportFilterModel filterModel)
{
//Default get all
var fromDate = DateTime.Today;
var isAllFromDate = true;
var toDate = DateTime.Today;
var isAllToDate = true;
//Filter by date
if (filterModel.FromDate.HasValue)
{
fromDate = filterModel.FromDate.Value.Date;
isAllFromDate = false;
}
if (filterModel.ToDate.HasValue)
{
toDate = filterModel.ToDate.Value.Date.AddDays(1);
isAllToDate = false;
}
var toListStorages = new int[1];
var isGetAllToStorages = true;
//Filter by deliver to
if (filterModel.ToListStorages != null && filterModel.ToListStorages.Count() > 0)
{
toListStorages = filterModel.ToListStorages;
isGetAllToStorages = false;
}
var model = from x in Repository.Search(filterModel.search).Where(p =>
(toListStorages.Contains(p.ToStorageId) || (isGetAllToStorages && WorkContext.MyStorages.Contains(p.ToStorageId))) &&
((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate)))
join m in _userRepository.GetAll() on x.ApproveFromManagerId equals m.UserId into m1
from m2 in m1.DefaultIfEmpty()
join s in _userRepository.GetAll() on x.ApproveFromStorageStaffId equals s.UserId into s1
from s2 in s1.DefaultIfEmpty()
join t in _userRepository.GetAll() on x.ApproveFromGeneralDeliveryManId equals t.UserId into t1
from t2 in s1.DefaultIfEmpty()
join h in _userRepository.GetAll() on x.ApproveFromReceiveStorageStaffId equals h.UserId into h1
from h2 in s1.DefaultIfEmpty()
join y in _storageRepository.GetAll() on x.FromStorageId equals y.StorageId into y1
from y2 in y1.DefaultIfEmpty()
join w in _storageRepository.GetAll() on x.ToStorageId equals w.StorageId into w1
from w2 in y1.DefaultIfEmpty()
select new TransferModel
{
TransferId = x.TransferId,
CreateDate = x.CreateDate,
Note = x.Note,
ApproveFromManagerName = (m2 == null ? String.Empty : m2.FirstName + " " + m2.LastName),
ApproveFromStorageStaffName = (s2 == null ? String.Empty : s2.FirstName + " " + s2.LastName),
ApproveFromGeneralDeliveryMan = (t2 == null ? String.Empty : t2.FirstName + " " + t2.LastName),
StorageStaffApproveReceive = (t2 == null ? String.Empty : t2.FirstName + " " + t2.LastName),
FromStorageName = (y2 == null ? "" : y2.StorageName),
ToStorageName = (w2 == null ? "" : w2.StorageName),
};
var gridModel = new GridModel <TransferModel>
{
Data = model
};
return(new JsonResult
{
Data = gridModel
});
}
public ActionResult GridModel(ReportFilterModel filterModel)
{
//Default get all
var fromDate = DateTime.Today;
var isAllFromDate = true;
var toDate = DateTime.Today;
var isAllToDate = true;
//Filter by date
if (filterModel.FromDate.HasValue)
{
fromDate = filterModel.FromDate.Value.Date;
isAllFromDate = false;
}
if (filterModel.ToDate.HasValue)
{
toDate = filterModel.ToDate.Value.Date.AddDays(1);
isAllToDate = false;
}
var fromListCustomers = new int[1];
var isGetAllFromCustomers = true;
//Filter by deliver to
if (filterModel.ListCustomers != null && filterModel.ListCustomers.Count() > 0)
{
fromListCustomers = filterModel.ListCustomers;
isGetAllFromCustomers = false;
}
var listOffices = new int[1];
var isGetAllListOffices = true;
//Filter by deliver to
if (filterModel.ListOffices != null && filterModel.ListOffices.Count() > 0)
{
listOffices = filterModel.ListOffices;
isGetAllListOffices = false;
}
var listProducts = new int[1];
var isGetAllListProducts = true;
//Filter by deliver to
if (filterModel.ListProducts != null && filterModel.ListProducts.Count() > 0)
{
listProducts = filterModel.ListProducts;
isGetAllListProducts = false;
}
var model = from x in Repository.Search(filterModel.search).Where(p => (listOffices.Contains(p.MyOfficeId) || (isGetAllListOffices && WorkContext.MyOffices.Contains(p.MyOfficeId))) &&
(fromListCustomers.Contains(p.Order.CustomerId) || (isGetAllFromCustomers)) &&
(p.ExportDetails.Any(k => listProducts.Contains(k.ProductId)) || (isGetAllListProducts)) &&
((p.CreateDate >= fromDate || isAllFromDate) && (p.CreateDate <= toDate || isAllToDate)))
join m in _myOfficeRepository.GetAll() on x.MyOfficeId equals m.MyOfficeId into m1
from m2 in m1.DefaultIfEmpty()
join s in _customerRepository.GetAll() on x.Order.CustomerId equals s.CustomerId into s1
from s2 in s1.DefaultIfEmpty()
select new ExportTrackModel
{
ExportTrackId = x.ExportTrackId,
CreateDate = x.CreateDate,
Note = x.Note,
MyOfficeName = (m2 == null ? String.Empty : m2.OfficeName),
CustomerName = (s2 == null ? String.Empty : s2.CustomerName)
};
var gridModel = new GridModel <ExportTrackModel>
{
Data = model
};
return(new JsonResult
{
Data = gridModel
});
}
public static string Export(IEnumerable <ReportRecord> records, ReportFilterModel filter)
{
var file = Path.GetTempFileName();
using (var document = SpreadsheetDocument.Create(file, SpreadsheetDocumentType.Workbook, true))
{
var workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
var sharedStringTablePart = workbookPart.AddNewPart <SharedStringTablePart>();
sharedStringTablePart.SharedStringTable = new SharedStringTable();
var sheets = workbookPart.Workbook.AppendChild(new Sheets());
var sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Report"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
}
using (var document = SpreadsheetDocument.Open(file, true))
{
var worksheetPart = document.WorkbookPart.WorksheetParts.First();
var sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
var sharedStringTable = document.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First().SharedStringTable;
// future use
int columnCount = 0;
var hRow = new Row();
if (filter.GroupByPartnerType)
{
AddStringCell("Partner Type", hRow, sharedStringTable); columnCount++;
}
if (filter.GroupByPartner)
{
AddStringCell("Partner", hRow, sharedStringTable); columnCount++;
}
if (filter.GroupByUser)
{
AddStringCell("User", hRow, sharedStringTable); columnCount++;
}
if (filter.GroupByAction)
{
AddStringCell("Action", hRow, sharedStringTable); columnCount++;
}
AddStringCell("Original Value", hRow, sharedStringTable); columnCount++;
AddStringCell("Discount", hRow, sharedStringTable); columnCount++;
sheetData.AppendChild(hRow);
foreach (var r in records)
{
var row = new Row();
if (filter.GroupByPartnerType)
{
AddStringCell(r.PartnerTypeName, row, sharedStringTable);
}
if (filter.GroupByPartner)
{
AddStringCell(r.PartnerName, row, sharedStringTable);
}
if (filter.GroupByUser)
{
AddStringCell(r.UserName, row, sharedStringTable);
}
if (filter.GroupByAction)
{
AddStringCell(r.ActionName, row, sharedStringTable);
}
row.Append(new Cell {
CellValue = new CellValue(r.OriginalValue)
});
row.Append(new Cell {
CellValue = new CellValue(r.ActionValue)
});
sheetData.AppendChild(row);
}
}
var filePath = string.Format(ReportRelativePathTemplate, DateTime.Now.Ticks);
Directory.CreateDirectory(Path.GetDirectoryName(filePath));
File.Move(file, filePath);
return(filePath);
}
public async Task <IEnumerable <ParameterisedReportModel> > GetParameterisedReportAsync(ReportFilterModel filterModel)
{
var sqlModel = new SqlParamsModel
{
Sql = "pkg_insights.p_parameterised_report",
Parameters = new OracleDynamicParameters()
};
sqlModel.Parameters.Add("pi_csv_filter_params", filterModel.Csv, dbType: OracleMappingType.Varchar2, ParameterDirection.Input);
sqlModel.Parameters.Add("po_cursor", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
return(await _dataAccess.QueryAsync <ParameterisedReportModel>(sqlModel));
}
public async Task <ReportFilterModel> GetMediaReportFilters(int userId)
{
ReportFilterModel result = new ReportFilterModel();
try
{
await Task.Run(() =>
{
using (SocialCRMEntities db = new SocialCRMEntities())
{
result.MediaTypesList = new List <SelectItemList>();
db.MediaTypes.ToList().ForEach(delegate(MediaType obj)
{
result.MediaTypesList.Add(new SelectItemList()
{
Value = obj.Id,
Text = obj.TypeName,
MediaTypeId = obj.Id
});
});
result.NewsTypeList = new List <SelectItemList>();
db.NewsTypes.OrderBy(x => x.NewsTypeName).ToList().ForEach(delegate(NewsType obj)
{
result.NewsTypeList.Add(new SelectItemList()
{
Value = obj.Id,
Text = obj.NewsTypeName,
MediaTypeId = obj.MediaTypeId
});
});
result.ChannelsList = new List <SelectItemList>();
db.MediaChannels.OrderBy(x => x.ChannelName).ToList().ForEach(delegate(MediaChannel obj)
{
result.ChannelsList.Add(new SelectItemList()
{
Value = obj.Id,
Text = obj.ChannelName,
MediaTypeId = obj.MediaTypeId
});
});
result.CategoriesList = new List <SelectItemList>();
db.Categories.OrderBy(x => x.CategoryName).ToList().ForEach(delegate(Category obj)
{
result.CategoriesList.Add(new SelectItemList()
{
Value = obj.Id,
Text = obj.CategoryName,
MediaTypeId = 0
});
});
result.NewsRelatedToList = new List <SelectItemList>();
db.NewsRelatedToes.OrderBy(x => x.Name).ToList().ForEach(delegate(NewsRelatedTo obj)
{
result.NewsRelatedToList.Add(new SelectItemList()
{
Value = obj.Id,
Text = obj.Name,
MediaTypeId = 0
});
});
result.SentimentList = new List <SelectItemList>();
db.Sentiments.OrderBy(x => x.Name).ToList().ForEach(delegate(Sentiment obj)
{
result.SentimentList.Add(new SelectItemList()
{
Value = obj.Id,
Text = obj.Name,
MediaTypeId = 0
});
});
result.AgentList = new List <SelectItemList>();
db.AspNetUsers.OrderBy(x => x.Name).ToList().ForEach(delegate(AspNetUser obj)
{
result.AgentList.Add(new SelectItemList()
{
Value = obj.Id,
Text = obj.Name,
MediaTypeId = 0
});
});
}
});
}
catch (Exception ex)
{
result = null;
}
return(result);
}
