public static List <NAVContractInvoiceHeaderViewModel> GetNotasCreditoRegistadas(string ContractNo, string NAVDatabaseName, string NAVCompanyName)
{
try
{
List <NAVContractInvoiceHeaderViewModel> result = new List <NAVContractInvoiceHeaderViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@NoContrato", ContractNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2009NotasCreditoRegistadas @DBName, @CompanyName, @NoContrato", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVContractInvoiceHeaderViewModel()
{
No_ = (string)temp.No_,
ValorContrato = (decimal?)temp.ValorContrato,
Data = (string)temp.Data
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static decimal GetTotalMeals(string NAVDatabaseName, string NAVCompanyName, string projectNo)
{
decimal?totalMeals = null;
try
{
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@JobNo", projectNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017CafetariasTotalRefeicoes @DBName, @CompanyName, @JobNo", parameters);
var item = data.FirstOrDefault();
if (item != null)
{
if (item.MealsTotal is decimal)
{
totalMeals = item.MealsTotal;
}
}
}
}
catch
{
}
return(totalMeals.HasValue ? totalMeals.Value : 0);
}
public static List <NAVSupplierViewModels> GetAll(string NAVDatabaseName, string NAVCompanyName, string TermNo)
{
try
{
List <NAVSupplierViewModels> result = new List <NAVSupplierViewModels>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@NoTermo", TermNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017Fornecedores @DBName, @CompanyName, @NoTermo", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVSupplierViewModels()
{
No_ = (string)temp.No_,
Name = (string)temp.Name,
Address = (string)temp.Address,
VATBusinessPostingGroup = (string)temp.VATBusinessPostingGroup,
Blocked = (int)temp.Blocked,
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVJobViewModel> GetJob(string NAVDatabaseName, string NAVCompanyName)
{
try
{
List <NAVJobViewModel> result = new List <NAVJobViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017Job @DBName, @CompanyName", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVJobViewModel()
{
No_ = (string)temp.No_
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVEncomendaAbertoViewModel> GetByDimValue(string NAVDatabaseName, string NAVCompanyName, int DimValueID)
{
try
{
List <NAVEncomendaAbertoViewModel> result = new List <NAVEncomendaAbertoViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@SetID", DimValueID)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017EncomendaAberto @DBName, @CompanyName, @SetID", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVEncomendaAbertoViewModel()
{
Code = (string)temp.DocNo,
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static NAV2017VendorBankAccountViewModel GetVendor(string NAVDatabaseName, string NAVCompanyName, string VendorNo)
{
try
{
NAV2017VendorBankAccountViewModel result = new NAV2017VendorBankAccountViewModel();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@VendorNo", VendorNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017VendorBankAccount @DBName, @CompanyName, @VendorNo", parameters);
foreach (dynamic temp in data)
{
result.VendorNo = (string)temp.Vendor;
result.VendorName = (string)temp.Name;
result.IBAN = (string)temp.IBAN;
result.NIB = (string)temp.NIB;
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <EncomendasViewModel> EncomendasPorRequisicao(string NAVDatabaseName, string NAVCompanyName, string Requisicao, int Tipo)
{
try
{
List <EncomendasViewModel> result = new List <EncomendasViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@OrderId", Requisicao),
new SqlParameter("@Type", Tipo),
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017EncomendasPorRequisicao @DBName, @CompanyName, @OrderId, @Type", parameters);
foreach (dynamic temp in data)
{
result.Add(new EncomendasViewModel()
{
No = temp.NoEncomenda.Equals(DBNull.Value) ? "" : (string)temp.NoEncomenda,
PayToVendorNo = temp.Fornecedor.Equals(DBNull.Value) ? "" : (string)temp.Fornecedor,
OrderDate = (DateTime)temp.DataEncomenda,
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVMeasureUnitViewModel> GetAllMeasureUnitWithResource(string NAVDatabaseName, string NAVCompanyName, string resource)
{
try
{
List <NAVMeasureUnitViewModel> result = new List <NAVMeasureUnitViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@resource", resource),
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017UnidadesMedidaWithResource @DBName, @CompanyName, @resource", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVMeasureUnitViewModel()
{
Code = (string)temp.Code,
Description = (string)temp.Code
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <SPInvoiceListViewModel> GetAllAutorized()
{
try
{
List <SPInvoiceListViewModel> result = new List <SPInvoiceListViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@Autorization", 1),
new SqlParameter("@Invoiced", 0)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017ProjectMovemmentsInvoiceGrid @Autorization, @Invoiced", parameters);
foreach (dynamic temp in data)
{
SPInvoiceListViewModel item = new SPInvoiceListViewModel();
item.ClientRequest = temp.PedidodoCliente.Equals(DBNull.Value) ? "" : (string)temp.PedidodoCliente;
item.InvoiceToClientNo = temp.FaturaNoCliente.Equals(DBNull.Value) ? "" : (string)temp.FaturaNoCliente;
item.CommitmentNumber = temp.NoCompromisso.Equals(DBNull.Value) ? "" : (string)temp.NoCompromisso;
item.ProjectNo = temp.NoProjeto.Equals(DBNull.Value) ? "" : (string)temp.NoProjeto;
item.Date = temp.Data.Equals(DBNull.Value) ? "" : (string)temp.Data.ToString("yyyy-MM-dd");
item.LineNo = (int)temp.NoLinha;
item.MovementType = temp.TipoMovimento.Equals(DBNull.Value) ? null : (int?)temp.TipoMovimento;
item.Type = temp.Tipo.Equals(DBNull.Value) ? null : (int?)temp.Tipo;
item.Code = temp.Código.Equals(DBNull.Value) ? "" : (string)temp.Código;
item.Description = temp.Descrição.Equals(DBNull.Value) ? "" : (string)temp.Descrição;
item.MeasurementUnitCode = temp.CodUnidadeMedida.Equals(DBNull.Value) ? "" : (string)temp.CodUnidadeMedida;
item.Quantity = temp.Quantidade.Equals(DBNull.Value) ? null : (decimal?)temp.Quantidade;
item.LocationCode = temp.CodLocalizacao.Equals(DBNull.Value) ? "" : (string)temp.CodLocalizacao;
item.ProjectContabGroup = temp.GrupoContabProjeto.Equals(DBNull.Value) ? "" : (string)temp.GrupoContabProjeto;
item.RegionCode = temp.CodigoRegiao.Equals(DBNull.Value) ? "" : (string)temp.CodigoRegiao;
item.FunctionalAreaCode = temp.CodAreaFuncional.Equals(DBNull.Value) ? "" : (string)temp.CodAreaFuncional;
item.ResponsabilityCenterCode = temp.CodCentroResponsabilidade.Equals(DBNull.Value) ? "" : (string)temp.CodCentroResponsabilidade;
item.User = temp.Utilizador.Equals(DBNull.Value) ? "" : (string)temp.Utilizador;
item.UnitCost = temp.CustoUnitario.Equals(DBNull.Value) ? null : (decimal?)temp.CustoUnitario;
item.TotalCost = temp.CustoTotal.Equals(DBNull.Value) ? null : (decimal?)temp.CustoTotal;
item.UnitPrice = temp.PrecoUnitario.Equals(DBNull.Value) ? null : (decimal?)temp.PrecoUnitario;
item.TotalPrice = temp.PrecoTotal.Equals(DBNull.Value) ? null : (decimal?)temp.PrecoTotal;
item.Billable = temp.Faturável.Equals(DBNull.Value) ? null : (bool?)temp.Faturável;
item.AutorizatedInvoice = temp.FaturacaoAutorizada.Equals(DBNull.Value) ? null : (bool?)temp.FaturacaoAutorizada;
item.AutorizatedInvoiceData = temp.DataAutorizacaoFaturacao.Equals(DBNull.Value) ? "" : (string)temp.DataAutorizacaoFaturacao.ToString("yyyy-MM-dd");
item.ConsumptionDate = temp.DataConsumo.Equals(DBNull.Value) ? "" : (string)temp.DataConsumo.ToString("yyyy-MM-dd");
item.CreateDate = temp.DataHoraCriacao.Equals(DBNull.Value) ? null : (DateTime?)temp.DataHoraCriacao;
item.CreateUser = temp.UtilizadorCriacao.Equals(DBNull.Value) ? "" : (string)temp.UtilizadorCriacao;
item.Registered = temp.Registado.Equals(DBNull.Value) ? null : (bool?)temp.Registado;
item.Billed = temp.Faturada.Equals(DBNull.Value) ? null : (bool?)temp.Faturada;
item.MealType = temp.TipoRefeicao.Equals(DBNull.Value) ? null : (int?)temp.TipoRefeicao;
item.InvoiceGroup = temp.GrupoFatura.Equals(DBNull.Value) ? null : (int?)temp.GrupoFatura;
item.InvoiceGroupDescription = temp.GrupoFaturaDescricao.Equals(DBNull.Value) ? "" : (string)temp.GrupoFaturaDescricao;
result.Add(item);
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static int?UpdateBalance(string NAVDatabaseName, string NAVCompanyName, string NAVClientNo, string EntryNo, string SinalizacaoRec, string Obs)
{
try
{
var result = new List <NAVClientesBalanceControlViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var _parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@CustomerNo", NAVClientNo),
new SqlParameter("@Obs", Obs),
new SqlParameter("@SinalizacaoRec", SinalizacaoRec),
new SqlParameter("@EntryNo", EntryNo)
};
var data = ctx.execStoredProcedureNQ("exec NAV2017ClientesBalancesUpdate @DBName, @CompanyName, @CustomerNo, @Obs, @SinalizacaoRec, @EntryNo", _parameters);
return(data);
}
}
catch (Exception ex)
{
return(null);
}
}
public static NAVRequisitionTypeViewModel GetById(string code, string NAVDatabaseName, string NAVCompanyName)
{
try
{
List <NAVRequisitionTypeViewModel> result = new List <NAVRequisitionTypeViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017TiposRequisicoes @DBName, @CompanyName", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVRequisitionTypeViewModel()
{
Code = (string)temp.Tipo,
Description = (string)temp.Description,
});
}
}
return(result.Where(x => x.Code == code).FirstOrDefault());
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVNoSeries> GetNoSeries(string NAVDatabaseName, string NAVCompanyName)
{
try
{
List <NAVNoSeries> result = new List <NAVNoSeries>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017NoSeries @DBName, @CompanyName", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVNoSeries()
{
Code = (string)temp.Code,
Description = (string)temp.Description,
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static string GetClientNameByNo(string NoClient, string NAVDatabaseName, string NAVCompanyName)
{
try
{
string result = "";
if (!string.IsNullOrEmpty(NoClient))
{
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@NoCliente", NoClient)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017Clientes @DBName, @CompanyName, @NoCliente", parameters);
foreach (dynamic temp in data)
{
result = (string)temp.Name;
}
}
}
return(result);
}
catch (Exception ex)
{
return("");
}
}
public static List <SuppliersProductsRefs> GetSuplierProductRefsForRequisition(string NAVDatabaseName, string NAVCompanyName, string requisitionNo)
{
try
{
List <SuppliersProductsRefs> result = new List <SuppliersProductsRefs>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@NoRequisicao", requisitionNo),
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017ObterProdutosFornecedor @DBName, @CompanyName, @NoRequisicao", parameters);
foreach (dynamic temp in data)
{
var item = new SuppliersProductsRefs();
item.SupplierNo = temp.SupplierNo;
item.ProductId = temp.ProductId;
item.UnitOfMeasureCode = temp.UnitOfMeasureCode;
item.SupplierProductId = temp.SupplierProductId;
result.Add(item);
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVSalesLinesViewModel> FindSalesLine(string NAVDatabaseName, string NAVCompanyName, string NAVContract, string NAVClientNo)
{
List <NAVSalesLinesViewModel> result = new List <NAVSalesLinesViewModel>();
try
{
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@ContractNo", NAVContract),
new SqlParameter("@CustomerNo", NAVClientNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017Pre_Registo @DBName, @CompanyName, @ContractNo, @CustomerNo", parameters);
if (data != null)
{
foreach (dynamic item in data)
{
result.Add(new NAVSalesLinesViewModel()
{
ContractNo = (string)item.ContractNo_,
ClientNo = (string)item.Sell_toCustomerNo_,
DocNo = (string)item.DocumentNo,
});
}
}
return(result);
}
}
catch (Exception ex)
{
return(result);
}
}
public static List <NAVSalesCrMemoLinesViewModel> GetSalesCrMemoLines(string NAVDatabaseName, string NAVCompanyName, string NAVContractNo, DateTime InitialDate, DateTime ExpirationDate)
{
try
{
List <NAVSalesCrMemoLinesViewModel> result = new List <NAVSalesCrMemoLinesViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@ContractNoPortal", NAVContractNo),
new SqlParameter("@InitialDate", InitialDate.ToString("yyyy/MM/dd h:mm tt")),
new SqlParameter("@ExpirationDate", ExpirationDate.ToString("yyyy/MM/dd h:mm tt"))
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017LinhaCRMemo @DBName, @CompanyName, @ContractNoPortal, @InitialDate, @ExpirationDate", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVSalesCrMemoLinesViewModel()
{
ContractNo = (string)temp.ContractNoPortal,
PostingDate = (string)temp.PostingDate,
Amount = (decimal)temp.Amount
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVClientesInvoicesDetailsViewModel> GetCrMemoDetails(string NAVDatabaseName, string NAVCompanyName, string No)
{
try
{
List <NAVClientesInvoicesDetailsViewModel> result = new List <NAVClientesInvoicesDetailsViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@No", No),
//new SqlParameter("@Regions", "''"),
//new SqlParameter("@FunctionalAreas", "''"),
//new SqlParameter("@RespCenters", "''"),
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017ClientesCrMemoDetails @DBName, @CompanyName, @No", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVClientesInvoicesDetailsViewModel()
{
No = temp.No.Equals(DBNull.Value) ? "" : (string)temp.No.ToString(),
AmountIncludingVAT = temp.AmountIncludingVAT.Equals(DBNull.Value) ? "" : (string)temp.AmountIncludingVAT.ToString(),
FunctionalAreaId = temp.FunctionalAreaId.Equals(DBNull.Value) ? "" : (string)temp.FunctionalAreaId.ToString(),
RegionId = temp.RegionId.Equals(DBNull.Value) ? "" : (string)temp.RegionId.ToString(),
RespCenterId = temp.RespCenterId.Equals(DBNull.Value) ? "" : (string)temp.RespCenterId.ToString(),
SellToCustomerNo = temp.SellToCustomerNo.Equals(DBNull.Value) ? "" : (string)temp.SellToCustomerNo.ToString(),
Tipo = temp.Tipo.Equals(DBNull.Value) ? "" : (string)temp.Tipo.ToString(),
Amount = temp.Amount.Equals(DBNull.Value) ? "" : (string)temp.Amount.ToString(),
Description = temp.Description.Equals(DBNull.Value) ? "" : (string)temp.Description.ToString(),
Description2 = temp.Description2.Equals(DBNull.Value) ? "" : (string)temp.Description2.ToString(),
DimensionSetID = temp.DimensionSetID.Equals(DBNull.Value) ? "" : (string)temp.DimensionSetID.ToString(),
DocumentNo = temp.DocumentNo.Equals(DBNull.Value) ? "" : (string)temp.DocumentNo.ToString(),
LineNo = temp.LineNo_.Equals(DBNull.Value) ? "" : (string)temp.LineNo_.ToString(),
Quantity = temp.Quantity.Equals(DBNull.Value) ? "" : (string)temp.Quantity.ToString(),
ServiceContractNo = temp.ServiceContractNo.Equals(DBNull.Value) ? "" : (string)temp.ServiceContractNo.ToString(),
UnitOfMeasure = temp.UnitOfMeasure.Equals(DBNull.Value) ? "" : (string)temp.UnitOfMeasure.ToString(),
UnitPrice = temp.UnitPrice.Equals(DBNull.Value) ? "" : (string)temp.UnitPrice.ToString(),
VAT = temp.VAT.Equals(DBNull.Value) ? "" : (string)temp.VAT.ToString(),
TipoRefeicao = temp.TipoRefeicao.Equals(DBNull.Value) ? "" : (string)temp.TipoRefeicao.ToString(),
MealTypeDescription = temp.MealTypeDescription.Equals(DBNull.Value) ? "" : (string)temp.MealTypeDescription.ToString(),
GrupoServico = temp.GrupoServico.Equals(DBNull.Value) ? "" : (string)temp.GrupoServico.ToString(),
ServiceGroupDescription = temp.ServiceGroupDescription.Equals(DBNull.Value) ? "" : (string)temp.ServiceGroupDescription.ToString(),
CodServCliente = temp.CodServCliente.Equals(DBNull.Value) ? "" : (string)temp.CodServCliente.ToString(),
DesServCliente = temp.DesServCliente.Equals(DBNull.Value) ? "" : (string)temp.DesServCliente.ToString(),
NGuiaResiduosGAR = temp.NGuiaResiduosGAR.Equals(DBNull.Value) ? "" : (string)temp.NGuiaResiduosGAR.ToString(),
NGuiaExterna = temp.NGuiaExterna.Equals(DBNull.Value) ? "" : (string)temp.NGuiaExterna.ToString(),
DataConsumo = temp.DataConsumo.Equals(DBNull.Value) ? "" : Convert.ToDateTime((string)temp.DataConsumo.ToString()).ToShortDateString().Contains("01/01/1753") ? "" : Convert.ToDateTime((string)temp.DataConsumo.ToString()).ToShortDateString(),
});
}
return(result);
}
}
catch (Exception ex)
{
return(null);
}
}
private static List <PurchaseHeader> GetPurchasesBy(string NAVDatabaseName, string NAVCompanyName, NAVBaseDocumentTypes type, string supplierId, string orderId, string externalDocNo)
{
try
{
List <PurchaseHeader> result = new List <PurchaseHeader>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@Type", (int)type),
new SqlParameter("@SupplierId", supplierId),
new SqlParameter("@OrderId", orderId),
new SqlParameter("@ExternalDocNo", externalDocNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017Encomendas @DBName, @CompanyName, @Type, @SupplierId, @OrderId, @ExternalDocNo", parameters);
foreach (dynamic temp in data)
{
var item = new PurchaseHeader();
item.DocumentType = (int)temp.DocumentType;
item.No = temp.No;
item.OrderDate = temp.OrderDate is DBNull ? string.Empty : ((DateTime)temp.OrderDate).ToString("yyyy-MM-dd");
item.DueDate = temp.DueDate is DBNull ? string.Empty : ((DateTime)temp.DueDate).ToString("yyyy-MM-dd");
item.LocationCode = temp.LocationCode;
item.RegionId = temp.RegionId is DBNull ? string.Empty : temp.RegionId;
item.FunctionalAreaId = temp.FunctionalAreaId is DBNull ? string.Empty : temp.FunctionalAreaId;
item.RespCenterId = temp.RespCenterId is DBNull ? string.Empty : temp.RespCenterId;
item.VendorOrderNo = temp.VendorOrderNo;
item.VendorInvoiceNo = temp.VendorInvoiceNo;
item.VendorCrMemoNo = temp.VendorCrMemoNo;
item.BuyFromVendorNo = temp.BuyFromVendorNo;
item.BuyFromVendorName = temp.BuyFromVendorName;
item.DocumentDate = temp.DocumentDate is DBNull ? string.Empty : ((DateTime)temp.DocumentDate).ToString("yyyy-MM-dd");
item.DimensionSetID = temp.DimensionSetID;
item.RelatedDocument = temp.RelatedDocument;
item.ValorFactura = temp.ValorFactura;
item.SourceDocNo = temp.SourceDocNo;
item.Quantity = temp.Quantity;
item.QuantityReceived = temp.QuantityReceived;
item.AmountRcdNotInvoiced = temp.AmountRcdNotInvoiced;
item.Amount = temp.Amount;
item.AmountIncludingVAT = temp.AmountIncludingVAT;
result.Add(item);
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVFornecedoresViewModel> GetFornecedores(string NAVDatabaseName, string NAVCompanyName, string NAVFornecedorNo)
{
try
{
List <NAVFornecedoresViewModel> result = new List <NAVFornecedoresViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@NoFornecedor", NAVFornecedorNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017FornecedoresLista @DBName, @CompanyName, @NoFornecedor", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVFornecedoresViewModel()
{
No = temp.No.Equals(DBNull.Value) ? "" : (string)temp.No,
Name = temp.Name.Equals(DBNull.Value) ? "" : (string)temp.Name,
FullAddress = temp.FullAddress.Equals(DBNull.Value) ? "" : (string)temp.FullAddress,
PostCode = temp.PostCode.Equals(DBNull.Value) ? "" : (string)temp.PostCode,
City = temp.City.Equals(DBNull.Value) ? "" : (string)temp.City,
Country = temp.Country.Equals(DBNull.Value) ? "" : (string)temp.Country,
Phone = temp.Phone.Equals(DBNull.Value) ? "" : (string)temp.Phone,
Email = temp.Email.Equals(DBNull.Value) ? "" : (string)temp.Email,
Fax = temp.Fax.Equals(DBNull.Value) ? "" : (string)temp.Fax,
HomePage = temp.HomePage.Equals(DBNull.Value) ? "" : (string)temp.HomePage,
VATRegistrationNo = temp.VATRegistrationNo.Equals(DBNull.Value) ? "" : (string)temp.VATRegistrationNo,
PaymentTermsCode = temp.PaymentTermsCode.Equals(DBNull.Value) ? "" : (string)temp.PaymentTermsCode,
PaymentMethodCode = temp.PaymentMethodCode.Equals(DBNull.Value) ? "" : (string)temp.PaymentMethodCode,
NoClienteAssociado = temp.NoClienteAssociado.Equals(DBNull.Value) ? "" : (string)temp.NoClienteAssociado,
Blocked = (int)temp.Blocked,
BlockedText = temp.BlockedText.Equals(DBNull.Value) ? "" : (string)temp.BlockedText,
Address = temp.Address.Equals(DBNull.Value) ? "" : (string)temp.Address,
Address2 = temp.Address2.Equals(DBNull.Value) ? "" : (string)temp.Address2,
Distrito = temp.Distrito.Equals(DBNull.Value) ? "" : (string)temp.Distrito,
Criticidade = (int)temp.Criticidade,
CriticidadeText = temp.CriticidadeText.Equals(DBNull.Value) ? "" : (string)temp.CriticidadeText,
Observacoes = temp.Observacoes.Equals(DBNull.Value) ? "" : (string)temp.Observacoes,
});
}
return(result);
}
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVDimValueViewModel> GetById(string NAVDatabaseName, string NAVCompanyName, int NAVDimType, string UserId, string RespCenter)
{
try
{
List <NAVDimValueViewModel> result = new List <NAVDimValueViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@TipoDim", NAVDimType),
new SqlParameter("@RespCenter", RespCenter)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017ValoresDimensao @DBName, @CompanyName, @TipoDim, @RespCenter", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVDimValueViewModel()
{
Code = (string)temp.Code,
Name = (string)temp.Name,
DimValueID = (int)temp.DimValueID
});
}
if (result.Count > 0)
{
var userDimensions = DBUserDimensions.GetByUserId(UserId);
if (userDimensions != null)
{
List <UserDimensionsViewModel> userDimensionsViewModel = userDimensions.ParseToViewModel();
userDimensionsViewModel.RemoveAll(x => x.Dimension != NAVDimType);
if (userDimensionsViewModel.Count > 0)
{
result.RemoveAll(x => !userDimensionsViewModel.Any(y => y.DimensionValue == x.Code));
}
}
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <EncomendasLinhasViewModel> ListLinesByNo(string NAVDatabaseName, string NAVCompanyName, string No, string No_FilterExpression)
{
try
{
List <EncomendasLinhasViewModel> result = new List <EncomendasLinhasViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@No", No),
new SqlParameter("@No_FilterExpression", No_FilterExpression)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017EncomendasLinhasList @DBName, @CompanyName, @No", parameters);
foreach (dynamic temp in data)
{
result.Add(new EncomendasLinhasViewModel()
{
No = temp.No.Equals(DBNull.Value) ? "" : (string)temp.No,
AllocationNo = temp.AllocationNo.Equals(DBNull.Value) ? "" : (string)temp.AllocationNo,
LocationCode = temp.LocationCode.Equals(DBNull.Value) ? "" : (string)temp.LocationCode,
Amount = (decimal?)temp.Amount,
AmountIncludingVAT = (decimal?)temp.AmountIncludingVAT,
Description = temp.Description.Equals(DBNull.Value) ? "" : (string)temp.Description,
Description2 = temp.Description2.Equals(DBNull.Value) ? "" : (string)temp.Description2,
DirectUnitCost = (decimal?)temp.DirectUnitCost,
FunctionalAreaId = temp.FunctionalAreaId.Equals(DBNull.Value) ? "" : (string)temp.FunctionalAreaId,
JobNo = temp.JobNo.Equals(DBNull.Value) ? "" : (string)temp.JobNo,
LineNo = (int?)temp.LineN,
Quantity = (decimal?)temp.Quantity,
QuantityInvoiced = (decimal?)temp.QuantityInvoiced,
QuantityReceived = (decimal?)temp.QuantityReceived,
RegionId = temp.RegionId.Equals(DBNull.Value) ? "" : (string)temp.RegionId,
RespCenterId = temp.RespCenterId.Equals(DBNull.Value) ? "" : (string)temp.RespCenterId,
UnitOfMeasure = temp.UnitOfMeasure.Equals(DBNull.Value) ? "" : (string)temp.UnitOfMeasure,
VAT = (decimal)temp.VAT,
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVClientesBalanceControlViewModel> GetBalances(string NAVDatabaseName, string NAVCompanyName, string NAVClientNo)
{
try
{
var result = new List <NAVClientesBalanceControlViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@CustomerNo", NAVClientNo),
//new SqlParameter("@Regions", "''"),
//new SqlParameter("@FunctionalAreas", "''"),
//new SqlParameter("@RespCenters", "''"),
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017ClientesBalances @DBName, @CompanyName, @CustomerNo", parameters);
var minDate = new DateTime(2008, 1, 1);
foreach (dynamic temp in data)
{
result.Add(new NAVClientesBalanceControlViewModel()
{
EntryNo = (int)temp.EntryNo,
Amount = (decimal?)temp.Amount,
CustomerNo = temp.CustomerNo.Equals(DBNull.Value) ? "" : (string)temp.CustomerNo,
DataConcil = (DateTime?)temp.DataConcil != null && (DateTime?)temp.DataConcil > minDate ? (DateTime?)temp.DataConcil : null,
Description = temp.Description.Equals(DBNull.Value) ? "" : (string)temp.Description,
DocumentNo = temp.DocumentNo.Equals(DBNull.Value) ? "" : (string)temp.DocumentNo,
DocumentType = temp.DocumentType.Equals(DBNull.Value) ? "" : (string)temp.DocumentType.ToString(),
DocumentTypeText = temp.DocumentTypeText.Equals(DBNull.Value) ? "" : (string)temp.DocumentTypeText.ToString(),
Obs = temp.Obs.Equals(DBNull.Value) ? "" : (string)temp.Obs,
PostingDate = (DateTime?)temp.PostingDate,
RemainingAmount = (decimal?)temp.RemainingAmount,
SinalizacaoRec = (int?)temp.SinalizacaoRec == 0 || (int?)temp.SinalizacaoRec == null ? false : true,
DocumentDate = (DateTime?)temp.DocumentDate,
DueDate = (DateTime?)temp.DueDate,
FunctionalAreaId = temp.FunctionalAreaId.Equals(DBNull.Value) ? "" : (string)temp.FunctionalAreaId,
RegionId = temp.RegionId.Equals(DBNull.Value) ? "" : (string)temp.RegionId,
RespCenterId = temp.RespCenterId.Equals(DBNull.Value) ? "" : (string)temp.RespCenterId
});
}
return(result);
}
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVJobLedgerEntryViewModel> GetFiltered(string projectNo, int?MovementNo, string NAVDatabaseName, string NAVCompanyName)
{
try
{
List <NAVJobLedgerEntryViewModel> result = new List <NAVJobLedgerEntryViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@NoProjeto", projectNo),
new SqlParameter("@NoMovimento", MovementNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017MovimentosProjeto @DBName, @CompanyName, @NoCliente, @NoMovimento", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVJobLedgerEntryViewModel()
{
EntryNo = (string)temp.EntryNo,
JobNo = (string)temp.JobNo,
PostingDate = (string)temp.PostingDate,
DocumentDate = (string)temp.DocumentDate,
EntryType = (string)temp.EntryType,
DocumentNo = (string)temp.DocumentNo,
Type = (string)temp.Type,
No = (string)temp.No,
Description = (string)temp.Description,
Quantity = (decimal)temp.Quantity,
UnitOfMeasureCode = (string)temp.UnitOfMeasureCode,
UnitCost = (decimal)temp.UnitCost,
TotalCost = (decimal)temp.TotalCost,
UnitPrice = (decimal)temp.UnitPrice,
TotalPrice = (decimal)temp.TotalPrice,
LineAmount = (decimal)temp.LineAmount,
LocationCode = (string)temp.LocationCode,
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static NAVAddressesViewModel GetByCode(string Code, string NAVDatabaseName, string NAVCompanyName)
{
try
{
if (string.IsNullOrEmpty(Code))
{
return(null);
}
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@NoCliente", "")
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017EnderecosEnvio @DBName, @CompanyName, @NoCliente", parameters);
string customerNo = string.Empty;
foreach (dynamic temp in data)
{
customerNo = (string)temp.Code;
if (!string.IsNullOrEmpty(customerNo) && customerNo == Code)
//if ((string)temp.Code == Code)
{
return(new NAVAddressesViewModel()
{
Code = (string)temp.Code,
Name = (string)temp.Name,
Name1 = (string)temp.Name1,
Name2 = (string)temp.Name2,
Address = (string)temp.Address,
Address1 = (string)temp.Address1,
Address2 = (string)temp.Address2,
ZipCode = (string)temp.ZipCode,
City = (string)temp.City,
Contact = (string)temp.Contact
});
}
}
}
return(null);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVClientesInvoicesViewModel> GetInvoices(string NAVDatabaseName, string NAVCompanyName, string NAVClientNo)
{
try
{
List <NAVClientesInvoicesViewModel> result = new List <NAVClientesInvoicesViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@CustomerNo", NAVClientNo),
//new SqlParameter("@Regions", "''"),
//new SqlParameter("@FunctionalAreas", "''"),
//new SqlParameter("@RespCenters", "''"),
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017ClientesInvoices @DBName, @CompanyName, @CustomerNo", parameters);
var minDate = new DateTime(2008, 1, 1);
foreach (dynamic temp in data)
{
result.Add(new NAVClientesInvoicesViewModel()
{
No_ = temp.No_.Equals(DBNull.Value) ? "" : (string)temp.No_,
ProjectNo = temp.ProjectNo.Equals(DBNull.Value) ? "" : (string)temp.ProjectNo,
DataServPrestado = temp.DataServPrestado.Equals(DBNull.Value) ? "" : (string)temp.DataServPrestado,
AmountIncludingVAT = temp.AmountIncludingVAT.Equals(DBNull.Value) ? "" : (string)temp.AmountIncludingVAT.ToString(),
BillToCustomerNo = temp.BillToCustomerNo.Equals(DBNull.Value) ? "" : (string)temp.BillToCustomerNo,
CreationDate = (DateTime?)temp.CreationDate,
DocumentDate = (DateTime?)temp.DocumentDate,
DueDate = (DateTime?)temp.DueDate,
FunctionalAreaId = temp.FunctionalAreaId.Equals(DBNull.Value) ? "" : (string)temp.FunctionalAreaId,
Paid = (bool)temp.Paid,
RegionId = temp.RegionId.Equals(DBNull.Value) ? "" : (string)temp.RegionId,
RespCenterId = temp.RespCenterId.Equals(DBNull.Value) ? "" : (string)temp.RespCenterId,
SellToCustomerNo = temp.SellToCustomerNo.Equals(DBNull.Value) ? "" : (string)temp.SellToCustomerNo,
Tipo = temp.Tipo.Equals(DBNull.Value) ? "" : (string)temp.Tipo,
ValorPendente = temp.ValorPendente.Equals(DBNull.Value) ? "" : (string)temp.ValorPendente.ToString()
});
}
return(result);
}
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVProductsViewModel> GetProductsForPreRequisitions(string NAVDatabaseName, string NAVCompanyName, string allowedDimensions, string requisitionType)
{
try
{
List <NAVProductsViewModel> result = new List <NAVProductsViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@AllowedDimensions", allowedDimensions),
new SqlParameter("@RequisitionType", requisitionType),
new SqlParameter("@LocationCode", "")
};
IEnumerable <dynamic> data = ctx.execStoredProcedure(@"exec NAV2017ProdutosPreRequisicao @DBName, @CompanyName, @AllowedDimensions, @RequisitionType, @LocationCode", parameters);
foreach (dynamic temp in data)
{
var item = new NAVProductsViewModel();
item.Code = (string)temp.No_;
item.Name = (string)temp.Description;
item.Name2 = (string)temp.Description2;
item.MeasureUnit = (string)temp.Base_Unit_of_Measure;
item.ItemCategoryCode = (string)temp.Item_Category_Code;
item.ProductGroupCode = (string)temp.Product_Group_Code;
item.VendorProductNo = (string)temp.Vendor_Item_No_;
item.LastCostDirect = temp.Last_Direct_Cost.Equals(DBNull.Value) ? 0 : (decimal)temp.Last_Direct_Cost;
item.VendorNo = (string)temp.Vendor_No_;
item.UnitCost = temp.UnitCost.Equals(DBNull.Value) ? 0 : (decimal)temp.UnitCost;
item.LocationCode = temp.Location_Code.Equals(DBNull.Value) ? "" : (string)temp.Location_Code;
item.VATProductPostingGroup = (string)temp.VATProductPostingGroup;
item.AreaFiltro = (string)temp.AreaFiltro;
result.Add(item);
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVProductsViewModel> GetAllProducts(string NAVDatabaseName, string NAVCompanyName, string productNo)
{
try
{
List <NAVProductsViewModel> result = new List <NAVProductsViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@NoProduto", productNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017Produtos @DBName, @CompanyName, @NoProduto", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVProductsViewModel()
{
Code = (string)temp.No_,
Name = (string)temp.Description,
Name2 = (string)temp.Description2,
MeasureUnit = (string)temp.Base_Unit_of_Measure,
ItemCategoryCode = (string)temp.Item_Category_Code,
ProductGroupCode = (string)temp.Product_Group_Code,
VendorProductNo = (string)temp.Vendor_Item_No_,
LastCostDirect = (decimal)temp.Last_Direct_Cost,
VendorNo = (string)temp.Vendor_No_,
VATProductPostingGroup = (string)temp.VATProductPostingGroup,
UnitCost = (decimal)temp.UnitCost,
InventoryValueZero = (byte)temp.InventoryValueZero,
AreaFiltro = (string)temp.AreaFiltro
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVContractInvoiceLinesViewModel> GetNotasdeCreditoLinesByNo(string ContractNo, string NAVDatabaseName, string NAVCompanyName)
{
try
{
List <NAVContractInvoiceLinesViewModel> result = new List <NAVContractInvoiceLinesViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@NoContrato", ContractNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2009LinhasNotasdeCredito @DBName, @CompanyName, @NoContrato", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVContractInvoiceLinesViewModel()
{
No_ = (string)temp.No_,
DocNo = (string)temp.DocNo,
Description = (string)temp.Description,
Description2 = (string)temp.Description2,
UnitOfMeasure = (string)temp.UnitOfMeasure,
Quantity = (decimal?)temp.Quantity,
UnitPrice = (decimal?)temp.UnitPrice,
Amount = (decimal?)temp.Amount,
AmountIncludingVAT = (decimal?)temp.AmountIncludingVAT,
JobNo = (string)temp.JobNo,
ExternalShipmentNo_ = (string)temp.ExternalShipmentNo_,
DataRegistoDiario = (DateTime?)temp.DataRegistoDiario
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVVendorViewModel> GetVendor(string NAVDatabaseName, string NAVCompanyName)
{
try
{
List <NAVVendorViewModel> result = new List <NAVVendorViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017Vendor @DBName, @CompanyName", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVVendorViewModel()
{
No_ = (string)temp.No_,
Name = (string)temp.Name,
Email = (string)temp.Email,
Address = (string)temp.Address,
VATRegistrationNo = (string)temp.VATRegistrationNo,
PostCode = (string)temp.PostCode,
VATBusinessPostingGroup = (string)temp.VATBusinessPostingGroup,
City = (string)temp.City,
Cod_Termos_Pagamento = (string)temp.Cod_Termos_Pagamento,
Cod_Formas_Pagamento = (string)temp.Cod_Formas_Pagamento,
Atividade = (string)temp.Atividade,
Preferencial = (int)temp.Preferencial
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
public static List <NAVAddressesViewModel> GetByClientNo(string ClientNo, string NAVDatabaseName, string NAVCompanyName)
{
try
{
List <NAVAddressesViewModel> result = new List <NAVAddressesViewModel>();
using (var ctx = new SuchDBContextExtention())
{
var parameters = new[] {
new SqlParameter("@DBName", NAVDatabaseName),
new SqlParameter("@CompanyName", NAVCompanyName),
new SqlParameter("@NoCliente", ClientNo)
};
IEnumerable <dynamic> data = ctx.execStoredProcedure("exec NAV2017EnderecosEnvio @DBName, @CompanyName, @NoCliente", parameters);
foreach (dynamic temp in data)
{
result.Add(new NAVAddressesViewModel()
{
Code = (string)temp.Code,
Name = (string)temp.Name,
Name1 = (string)temp.Name1,
Name2 = (string)temp.Name2,
Address = (string)temp.Address,
Address1 = (string)temp.Address1,
Address2 = (string)temp.Address2,
ZipCode = (string)temp.ZipCode,
City = (string)temp.City,
Contact = (string)temp.Contact
});
}
}
return(result);
}
catch (Exception ex)
{
return(null);
}
}
