public SearchResultViewModel <ExpenseStatusesLogDto> GetStatusesLogOfExpense(ExpenseListViewCriteria criteria)
{
var query = this.expenseStatusLogRepository.GetAllIncluding(x => x.User).
Where(x => x.ExpenseId == criteria.ExpenseId);
return(this.CreateResult <ExpenseStatusLog, ExpenseStatusesLogDto>(query, criteria, "UpdatedDateTime desc"));
}
public SearchResultViewModel <ExpenseStatusesLogDto> GetStatusLogList(ExpenseListViewCriteria criteria)
{
var query = this.expenseStatusLogRepository.GetAllIncluding(x => x.User,
x => x.Expense,
x => x.Expense.User);
if (!string.IsNullOrEmpty(criteria.PartialDescription))
{
query = this.MatchInFields <ExpenseStatusLog>(query, criteria.PartialDescription, true, c => new[] {
c.User.FirstName.ToLower(),
c.User.LastName.ToLower(),
c.Expense.User.FirstName.ToLower(),
c.Expense.User.LastName.ToLower(),
c.Notes.ToLower(),
c.ReasonOfChange.ToLower()
});
}
if (criteria.StartDate != null)
{
query = query.Where(x => DbFunctions.DiffDays(criteria.StartDate, x.UpdatedDateTime) >= 0);
}
if (criteria.EndDate != null)
{
query = query.Where(x => DbFunctions.DiffDays(criteria.EndDate, x.UpdatedDateTime) <= 0);
}
if (criteria.UserId != 0)
{
query = query.Where(x => x.Expense.UserId == criteria.UserId);
}
return(this.CreateResult <ExpenseStatusLog, ExpenseStatusesLogDto>(query, criteria, "UpdatedDateTime desc"));
}
public HttpResponseMessage GetExpenseAdminList(HttpRequestMessage request, [FromUri] ExpenseListViewCriteria criteria)
{
return(CreateHttpResponse(request, () => {
HttpResponseMessage response = null;
response = request.CreateResponse(HttpStatusCode.OK, this.expenseAppService.GetExpensesList(criteria));
return response;
}));
}
public ExpensesListSearchResultViewModel <ExpenseListDto> GetExpensesList(ExpenseListViewCriteria criteria)
{
criteria.GetForBackEnd = true;
var searchResultQuery = this.GetExpenseQuery(criteria);
var searchResult = this.CreateResult <Expense, ExpenseDto>(searchResultQuery, criteria, "Id desc");
var ExpenseList = new ExpensesListSearchResultViewModel <ExpenseListDto>()
{
Results = Mapper.Map <IEnumerable <ExpenseDto>, IEnumerable <ExpenseListDto> >(searchResult.Results),
TotalItems = searchResult.TotalItems,
SubTotal = searchResultQuery.Select(x => x.Total).
DefaultIfEmpty(0).
Sum()
};
return(ExpenseList);
}
public HttpResponseMessage Search(HttpRequestMessage request, [FromUri] ExpenseListViewCriteria criteria)
{
return(CreateHttpResponse(request, () => {
HttpResponseMessage response = null;
if (criteria.CompanyId != 0)
{
var items = this.expenseAppService.Search(criteria);
response = request.CreateResponse(HttpStatusCode.OK, items);
}
else
{
response = request.CreateResponse(HttpStatusCode.BadRequest, new { success = false });
}
return response;
}));
}
public HttpResponseMessage Post(HttpRequestMessage request, ExpenseListViewCriteria criteria)
{
return(CreateHttpResponse(request, () =>
{
HttpResponseMessage response = null;
// Force params
// Approved SyncStatusId
criteria.SyncStatusId = 3;
// Not exported
criteria.Exported = 2;
// GetAll
criteria.Size = -1;
SearchResultViewModel <ExpenseDto> expenseList = this.expenseAppService.Search(criteria);
SearchResultViewModel <ExportExpenseDto> exportExpenseList = Mapper.Map <SearchResultViewModel <ExpenseDto>, SearchResultViewModel <ExportExpenseDto> >(expenseList);
string connectionStringsConfigPath = HttpContext.Current.Server.MapPath("~/ConnectionStrings.config");
XElement doc = XElement.Load($"{connectionStringsConfigPath}");
string reportsContextConnectionString = doc.Descendants("add")
.Where(x => (string)x.Attribute("name") == "ReportsContext")
.Select(x => (string)x.Attribute("connectionString"))
.FirstOrDefault();
using (SqlConnection connection =
new SqlConnection(reportsContextConnectionString ?? throw new InvalidOperationException("No se pudo establecer la conexión a la base de datos.")))
{
const string ProviderAccountNumberQuery = @"SELECT CONVERT(int,PVMPRH_NROCTA) from PVMPRH where @ProveedorId = PVMPRH_NRODOC";
List <long> providersNotExported = new List <long>();
List <KeyValuePair <long, int> > existingProviders = new List <KeyValuePair <long, int> >();
long expensesExported = 0;
long expensesNotExported = 0;
DateTime CurrentDate = DateTime.Today;
foreach (ExportExpenseDto expense in exportExpenseList.Results)
{
Int32 providerAccountNumber = 0;
if (existingProviders.All(x => x.Key != expense.Provider.Cuit))
{
SqlCommand command = new SqlCommand(ProviderAccountNumberQuery, connection);
command.Parameters.AddWithValue("@ProveedorId", expense.Provider.Cuit);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
if (reader.Read())
{
providerAccountNumber = reader.GetInt32(0);
if (providerAccountNumber != 0)
{
existingProviders.Insert(0, new KeyValuePair <long, int>(expense.Provider.Cuit, providerAccountNumber));
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
reader.Close();
command.Dispose();
connection.Close();
}
}
else
{
providerAccountNumber = existingProviders.Where(x => x.Key == expense.Provider.Cuit).Select(x => x.Value).FirstOrDefault();
}
if (providerAccountNumber != 0)
{
bool SIN_IVA = expense.Aliquot.Description == "SIN IVA";
string userCompanyGroupForExpense = expense.User.UserCompanyGroups.Where(x => x.Company.Id == expense.CompanyId).
Select(x => x.UserGroup.Code + x.Company.Id).
FirstOrDefault();
var stringifiedproviderAccountNumber = providerAccountNumber.ToString();
ReceiptHeader header = new ReceiptHeader()
{
SAR_CORMVH_IDENTI = expense.Id.ToString(),
SAR_CORMVH_CIRCOM = SIN_IVA ? CIRCOM.SIN_IVA : CIRCOM.IVA,
SAR_CORMVH_CIRAPL = SIN_IVA ? CIRCOM.SIN_IVA : CIRCOM.IVA,
SAR_CORMVH_CODEMP = "AL0" + expense.CompanyId,
SAR_CORMVH_CODORI = expense.Receipt,
SAR_CORMVH_CMPRAD = "N/A",
SAR_CORMVH_STATUS = 'N',
SAR_CORMVH_FCHMOV = CurrentDate,
SAR_CORMVH_NROCTA = stringifiedproviderAccountNumber.PadLeft(6)
};
receiptHeaderContext.Add(header);
var hasNotTaxesConcepts = Math.Abs(double.Parse(expense.NotTaxedConcepts)) > 0;
ReceiptItem itemDefault = new ReceiptItem()
{
SAR_CORMVI_IDENTI = expense.Id.ToString(),
SAR_CORMVI_TIPORI = expense.Category.Code,
SAR_CORMVI_CANTID = 1,
SAR_CORMVI_ARTORI = expense.CompanyId + expense.Category.Code + userCompanyGroupForExpense + "G",
SAR_CORMVI_NROITM = 1,
SAR_CORMVI_PRECIO = SIN_IVA ? expense.Total : expense.NetValue,
SAR_CORMVI_NROAPL = null,
SAR_CORMVI_ITMAPL = null
};
receiptItemContext.Add(itemDefault);
CreateDistribution(expense.Id, itemDefault.SAR_CORMVI_NROITM, expense.CompanyId, expense.User.Id_Erp);
D_Concepts Debe_concepts_default = new D_Concepts()
{
SAR_CORMVI08_IDENTI = expense.Id,
SAR_CORMVI08_IMPORT = expense.Total
};
debeConceptsRepository.Add(Debe_concepts_default);
H_Concepts Haber_concepts_default = new H_Concepts()
{
SAR_CORMVI09_IDENTI = expense.Id,
SAR_CORMVI09_CODCPT = expense.CompanyId + (expense.Payment.Description == "Efectivo" ? "E" : "T"),
SAR_CORMVI09_IMPORT = expense.Total
};
haberConceptsRepository.Add(Haber_concepts_default);
if (hasNotTaxesConcepts)
{
ReceiptItem secondLine = itemDefault.Clone();
secondLine.SAR_CORMVI_NROITM = 2;
secondLine.SAR_CORMVI_PRECIO = expense.NotTaxedConcepts;
secondLine.SAR_CORMVI_ARTORI = secondLine.SAR_CORMVI_ARTORI.Remove(secondLine.SAR_CORMVI_ARTORI.Length - 1, 1) + "N";
receiptItemContext.Add(secondLine);
CreateDistribution(expense.Id, secondLine.SAR_CORMVI_NROITM, expense.CompanyId, expense.User.Id_Erp);
}
string aliquotValue = expense.Aliquot.Value.ToString(CultureInfo.InvariantCulture);
if (!SIN_IVA)
{
TaxesDetails taxDetails = new TaxesDetails()
{
SAR_CORMVI07_IDENTI = expense.Id.ToString(),
SAR_CORMVI07_CODCPT = GetCODPT(aliquotValue),
SAR_CORMVI07_INGRES = expense.NetValue,
SAR_CORMVI07_IMPGRA = expense.NetValue,
SAR_CORMVI07_PORCEN = aliquotValue,
SAR_CORMVI07_NROITM = 1
};
taxesDetailsRepository.Add(taxDetails);
}
DistDimTesoH distDimTesoH = new DistDimTesoH()
{
SAR_CJRMVD10_IDENTI = expense.Id,
SAR_CJRMVD10_CODDIS = $"AL0{expense.CompanyId.ToString()}|{expense.User.Id_Erp}"
};
distDimTesoHRepository.Add(distDimTesoH);
this.reportsUnitOfWork.Commit();
expensesExported++;
}
else
{
expensesNotExported++;
if (!providersNotExported.Contains(expense.Provider.Cuit))
{
providersNotExported.Add(expense.Provider.Cuit);
}
}
}
foreach (var expense in expenseList.Results)
{
if (!providersNotExported.Contains(expense.Provider.Cuit))
{
expense.Exported = true;
expense.ExportedDateTime = DateTime.Now;
this.expenseRepository.Edit(Mapper.Map <ExpenseDto, Expense>(expense));
this.unitOfWork.Commit();
}
}
int notExportedProviders = providersNotExported.Count();
string providersPath = null;
if (notExportedProviders > 0)
{
ProviderListViewCriteria providersCriteria = new ProviderListViewCriteria()
{
Size = -1,
FileName = "Proveedores_inexistentes_exportacion_gastos"
};
SearchResultViewModel <ProviderDto> providersList = this.providerAppService.ExportData(providersCriteria, null, providersNotExported);
providersPath = this.SaveCsv <ProviderDto, ProviderListViewCriteria>(providersList,
providersCriteria,
providersCriteria.FileName + "_" + long.Parse(DateTime.Now.ToString("yyyyMMddHHmmss")),
false, null);
}
response = request.CreateResponse(HttpStatusCode.Created, new
{
success = true,
exported = expensesExported,
not_exported = expensesNotExported,
inexistent_providers = notExportedProviders,
error_providers_path = providersPath
});
}
return response;
}));
private IQueryable <Expense> GetExpenseQuery(ExpenseListViewCriteria criteria)
{
var config = this.configRepository.GetAll().OrderByDescending(x => x.UpdatedDateTime).FirstOrDefault();
var localTimeNow = this.timeService.LocalDateTimeNow;
var query = this.entityRepository.GetAllIncluding(x => x.Provider,
x => x.Provider.LegalCondition,
x => x.Category,
x => x.Images,
x => x.User,
x => x.User.UserCompanyGroups,
x => x.User.UserCompanyGroups.Select(y => y.Company),
x => x.User.UserCompanyGroups.Select(y => y.UserGroup),
x => x.SyncStatus,
x => x.Logs,
x => x.Aliquot,
x => x.Payment,
x => x.Company).
Where(x => x.Company.Id == criteria.CompanyId);
if (!criteria.GetForBackEnd)
{
query = query.Where(x => DbFunctions.DiffDays(x.Date, localTimeNow) <= config.SyncDays);
}
if (criteria.CategoryId != 0)
{
query = query.Where(x => x.CategoryId == criteria.CategoryId);
}
var user = this.userRepository.GetAllIncluding(x => x.Role).Where(x => x.Id == (int)this.CurrentUserId).FirstOrDefault();
if (user.Role.Description == "Usuario")
{
query = query.Where(x => x.User.Id == (int)this.CurrentUserId);
}
else
{
if (criteria.UserId != 0)
{
query = query.Where(x => x.User.Id == criteria.UserId);
}
}
if (criteria.StartDate != null)
{
query = query.Where(x => DbFunctions.DiffDays(criteria.StartDate, x.Date) >= 0);
}
if (criteria.EndDate != null)
{
query = query.Where(x => DbFunctions.DiffDays(criteria.EndDate, x.Date) <= 0);
}
if (criteria.ProviderId != 0)
{
query = query.Where(x => x.Provider.Id == criteria.ProviderId);
}
if (criteria.SyncStatusId != 0)
{
query = query.Where(x => x.SyncStatus.Id == criteria.SyncStatusId);
}
if (criteria.PaymentId != 0)
{
query = query.Where(x => x.Payment.Id == criteria.PaymentId);
}
if (criteria.Exported != 0)
{
if (criteria.Exported == 1)
{
query = query.Where(x => x.Exported.Equals(true));
}
if (criteria.Exported == 2)
{
query = query.Where(x => x.Exported.Equals(false));
}
}
if (!string.IsNullOrEmpty(criteria.PartialDescription))
{
query = this.MatchInFields <Expense>(query, criteria.PartialDescription, true, c => new[] {
c.Provider.LegalName.ToLower(),
c.Total.ToString(),
c.Provider.Cuit.ToString()
});
}
return(query);
}
public AnalyticsDto GetAnalytics(ExpenseListViewCriteria criteria)
{
var currentUser = this.userRepository.GetAllIncluding(x => x.Role).Where(x => x.Id == (int)this.CurrentUserId).FirstOrDefault();
var query = this.entityRepository.GetAllIncluding(x => x.Provider,
x => x.User,
x => x.SyncStatus,
x => x.Company).
Where(x => x.Company.Id == criteria.CompanyId &&
DbFunctions.DiffDays(criteria.StartDate, x.Date) >= 0 &&
DbFunctions.DiffDays(criteria.EndDate, x.Date) <= 0);
if (currentUser.Role.Description == "Usuario")
{
query = query.Where(x => x.User.Id == (int)this.CurrentUserId);
}
if (query.Count() == 0)
{
return(new AnalyticsDto());
}
var results = query.GroupBy(x => x.User.Id).ToList();
var analyticsDto = new AnalyticsDto();
analyticsDto.Vendors = new List <VendorAnalyticsDto>();
foreach (var userExpenses in results)
{
var user = this.userRepository.GetSingle(userExpenses.Key);
var vendorAnalytics = new VendorAnalyticsDto {
FullName = user.FirstName + " " + user.LastName
};
foreach (var expense in userExpenses)
{
var dbExpense = this.entityRepository.GetAllIncluding(x => x.Category).Where(x => x.Id == expense.Id).FirstOrDefault();
switch (dbExpense.Category.Description)
{
case "Comida":
analyticsDto.TotalFood = analyticsDto.TotalFood + expense.Total;
vendorAnalytics.TotalFood = vendorAnalytics.TotalFood + expense.Total;
break;
case "Alojamiento":
analyticsDto.TotalHotel = analyticsDto.TotalHotel + expense.Total;
vendorAnalytics.TotalHotel = vendorAnalytics.TotalHotel + expense.Total;
break;
case "Combustible":
analyticsDto.TotalFuel = analyticsDto.TotalFuel + expense.Total;
vendorAnalytics.TotalFuel = vendorAnalytics.TotalFuel + expense.Total;
break;
default:
analyticsDto.TotalOther = analyticsDto.TotalOther + expense.Total;
vendorAnalytics.TotalOther = vendorAnalytics.TotalOther + expense.Total;
break;
}
}
analyticsDto.Vendors.Add(vendorAnalytics);
}
return(analyticsDto);
}
public SearchResultViewModel <ExpenseReportDto> GetReport(ExpenseListViewCriteria criteria)
{
return(this.CreateResult <Expense, ExpenseReportDto>(this.GetExpenseQuery(criteria), criteria, "Id desc"));
}
