/// <summary>
/// Generate All Invoices to a contract
/// </summary>
/// <param name="contract"></param>
/// <returns></returns>
//private void GenerateLoanFromContract(Contract contract, int qtdParcels)
//{
// AccountManager accountManager = new AccountManager(this);
// //contract.FinancierOperation = accountManager.GetFinancierOperation(contract.CompanyId, Convert.ToInt32(contract.FinancierOperationId));
// contract.FinancierCondition = accountManager.GetFinancierConditionByParcelCount(contract.CompanyId, Convert.ToInt32(contract.FinancierOperationId), qtdParcels) ?? new FinancierCondition();
// if (contract.FinancierOperation.Brokerage.HasValue && contract.FinancierOperation.Brokerage.Value)
// {
// GenerateBrokerCommission(contract);
// GenerateRepresententBrokerComission(contract);
// }
// else
// {
// GenerateMonthlyInvoicesFromLoan(contract);
// GenerateBillToCustomerFromLoan(contract);
// }
//}
#region Calculate Commission
//private void GenerateBrokerCommission(Contract contract)
//{
// InvoicesManager manager = new InvoicesManager(this);
// Invoice invoice = new Invoice();
// invoice.CompanyId = contract.CompanyId;
// invoice.CustomerId = contract.CustomerId;
// invoice.EntryDate = contract.FirstParcelDueDate.Value.AddDays(contract.FinancierOperation.ReceiveDay).NextUtilDay();
// //invoice.Creditor = !String.IsNullOrEmpty(contract.ContractNumber) ? contract.ContractNumber : contract.ContractId.ToString();
// invoice.Description = "Comissão Contrato N. " + contract.ContractNumber;
// //invoice.InvoiceValue = Decimal.Zero;
// if (contract.InvoiceId.HasValue)
// {
// Invoice original_invoice = manager.GetInvoice(contract.CompanyId, contract.InvoiceId.Value);
// new ParcelsManager(this).DeleteAllNextInvoiceParcels(invoice, DateTime.Now);
// manager.Update(original_invoice, invoice);
// }
// else
// {
// //manager.Insert(invoice, null);
// manager.InsertRetrievingId(invoice);
// contract.InvoiceId = invoice.InvoiceId;
// }
// GenerateBrokerCommissionParcel(contract, invoice);
// DbContext.SubmitChanges();
//}
//private Parcel GenerateBrokerCommissionParcel(Contract contract, Invoice invoice)
//{
// if (contract.FinancierOperation == null)
// return null;
// Parcel parcel = new Parcel();
// parcel.FinancierOperationId = contract.FinancierOperationId;
// parcel.CompanyId = contract.CompanyId;
// parcel.Description = "1/1";
// parcel.DueDate = contract.FirstParcelDueDate.Value.AddDays(contract.FinancierOperation.ReceiveDay).NextUtilDay(); ;
// parcel.Amount = CalculateNetWorth(contract) * contract.FinancierCondition.PercentCompany / 100m;
// if (contract.Insurance.HasValue) //seguro
// parcel.Amount += contract.Insurance.Value * Convert.ToDecimal(contract.FinancierOperation.InsurancePercent) / 100m;
// if (contract.MoneyReserves.HasValue) //pecúlio
// parcel.Amount += contract.MoneyReserves.Value * Convert.ToDecimal(contract.FinancierOperation.PeculiumPercent) / 100m;
// if (contract.MonthlyFee.HasValue)// mensalidade
// parcel.Amount += (contract.MonthlyFee.Value * contract.FinancierCondition.ParcelCount) * Convert.ToDecimal(contract.FinancierOperation.MonthlyFeePercent) / 100m;
// //invoice.InvoiceValue += parcel.Amount;
// parcel.InvoiceId = invoice.InvoiceId;
// new InvoicesManager(this).Insert(parcel, contract.FinancierCondition);
// return parcel;
//}
//private void GenerateRepresententBrokerComission(Contract contract)
//{
// if (contract.RepresentantId.HasValue)
// {
// BillManager billManager = new BillManager(this);
// Bill bill = new Bill();
// bill.CompanyId = contract.CompanyId;
// bill.DocumentType = (Int32)DocumentType.others;
// bill.EntryDate = contract.FirstParcelDueDate.Value.AddDays(contract.FinancierOperation.ReceiveDay).NextUtilDay();
// //if (contract.RepresentantId.HasValue)
// //{
// // // bill.Creditor = (contract.Representant.Profile != null ? contract.Representant.Profile.AbreviatedName : contract.Representant.LegalEntityProfile.CompanyName);
// // bill.Description = "Comissão »" + bill.Creditor;
// //}
// billManager.Insert(bill, null);
// GenerateRepresententBrokerComissionParcel(contract, bill);
// DbContext.SubmitChanges();
// }
//}
//private Parcel GenerateRepresententBrokerComissionParcel(Contract contract, Bill bill)
//{
// if (contract.FinancierOperation == null)
// return null;
// Parcel parcel = new Parcel();
// parcel.FinancierOperationId = contract.FinancierOperationId;
// parcel.CompanyId = contract.CompanyId;
// parcel.Description = "1/1";
// parcel.DueDate = contract.FirstParcelDueDate.Value.AddDays(contract.FinancierOperation.ReceiveDay).NextUtilDay(); ;
// parcel.Amount = CalculateNetWorth(contract) * contract.FinancierCondition.PercentRepresentant / 100m;
// if (contract.Insurance.HasValue) //seguro
// parcel.Amount += contract.Insurance.Value * Convert.ToDecimal(contract.FinancierOperation.InsurancePercentPerRepresentant) / 100m;
// if (contract.MoneyReserves.HasValue) //pecúlio
// parcel.Amount += contract.MoneyReserves.Value * Convert.ToDecimal(contract.FinancierOperation.PeculiumPercentPerRepresentant) / 100m;
// if (contract.MonthlyFee.HasValue)// mensalidade
// parcel.Amount += (contract.MonthlyFee.Value * contract.FinancierCondition.ParcelCount) * Convert.ToDecimal(contract.FinancierOperation.MonthlyFeePercentPerRepresentant) / 100m;
// //bill.BillValue += parcel.Amount;
// parcel.BillId = bill.BillId;
// new BillManager(this).Insert(parcel, contract.FinancierCondition);
// return parcel;
//}
#endregion
private void GenerateBillToCustomerFromLoan(Contract contract)
{
var billManager = new FinancialManager(this);
var parcelsManager = new ParcelsManager(this);
var customerManager = new CustomerManager(this);
var bill = new Bill();
bill.CompanyId = contract.CompanyId;
bill.DocumentType = (Int32)DocumentType.others;
Customer customer = customerManager.GetCustomer(contract.CustomerId, contract.CompanyId);
//bill.Creditor = customer.Profile != null ? customer.Profile.Name : customer.LegalEntityProfile.CompanyName;
//bill.Description = "Empréstimo p/ " + bill.Creditor;
//bill.BillValue = contract.ContractValue.Value - CalculateOtherDebits(contract);
billManager.Insert(bill, null);
//
var parcel = new Parcel();
parcel.FinancierOperationId = contract.FinancierOperationId;
parcel.CompanyId = contract.CompanyId;
parcel.BillId = bill.BillId;
parcel.Description = "1/1";
parcel.DueDate = contract.BeginDate.NextUtilDay();
//parcel.Amount = bill.BillValue.Value;
parcelsManager.Insert(parcel, null);
}
/// <summary>
/// This method retrieves customers data from a excel file and stores in db
/// </summary>
/// <param name="companyId"></param>
/// <param name="userId"></param>
/// <param name="message"></param>
public void ImportDataFromExcelFile(Int32 companyId, Int32 userId, string fileName, out string message)
{
ProfileManager profileManager;
profileManager = new ProfileManager(this);
using (var excelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + @"; Extended Properties=Excel 5.0"))
{
var excelCommand = new OleDbCommand("Select * from [CLIENTES$]", excelConnection);
excelConnection.Open();
OleDbDataReader excelReader;
try
{
excelReader = excelCommand.ExecuteReader();
}
catch (OleDbException)
{
message = "Nome da planilha incorreto! Certifique-se que o nome da planilha no seu arquivo é CLIENTES";
return;
}
RetrieveExistentColunms(excelReader);
//
// Verifies if exist the necessary columms from excel file
//
if (!ExistRequiredInformationsToProfile(excelReader) && !ExistRequiredColumnsToLegalEntityProfile(excelReader) || !GetColumm("CEP", excelReader))
{
message = "Coluna obrigatória faltando no arquivo!";
return;
}
var addressManager = new AddressManager(this);
while (excelReader.Read())
{
totalRegisters++;
if (!String.IsNullOrEmpty(excelReader["CEP"].ToString()))
{
if (addressManager.GetAddress(excelReader["CEP"].ToString().Replace("-", "")) == null)
{
if (IsValidAddress(excelReader))
{
InsertNewAddress(excelReader);
}
else
{
errors++;
continue;
}
}
}
//
// Retrieve the cpnj's e cpf's from db for to compare with
// the cpnj's e cpf's retrieved from excel file
//
listCPF.AddRange(profileManager.GetCPFnumbers(companyId));
listCNPJ.AddRange(profileManager.GetCNPJnumbers(companyId));
var customer = new Customer();
var profile = new Profile();
var legalEntityProfile = new LegalEntityProfile();
var contact = new Contact();
if (ExistRequiredInformationsToProfile(excelReader) && !String.IsNullOrEmpty(excelReader["CPF"].ToString()) && !String.IsNullOrEmpty(excelReader["NOME"].ToString()))
{
//
// profile informations
//
if (ExistCpf(companyId, excelReader))
{
// verifies if the cpf is related with some customer
if (CustomerManager.GetCustomer(companyId, excelReader["CPF"].ToString()) == null)
{
AttachCustomerToProfile(companyId, userId, excelReader);
continue;
}
else
{
errors++;
}
continue;
}
profile = FillProfileData(excelReader);
if (!ValidateProfileData(profile) || !ValidateAddressData(profile.PostalCode ?? String.Empty, profile.AddressNumber ?? String.Empty, profile.AddressComp ?? String.Empty))
{
errors++;
continue;
}
//save the customer with profile
customer.CompanyId = companyId;
customer.ModifiedDate = DateTime.Now;
customer.Profile = profile;
CustomerManager.Insert(customer);
//
// insert a contact and related it with customer
//
if (ExistsColumm("CONTATO"))
{
if (!String.IsNullOrEmpty(excelReader["CONTATO"].ToString()))
{
SaveCustomerContact(customer, userId, excelReader["CONTATO"].ToString());
}
}
listCPF.Add(excelReader["CPF"].ToString());
continue;
}
//
// LegalEntityProfile informations
//
if (ExistRequiredColumnsToLegalEntityProfile(excelReader) && !String.IsNullOrEmpty(excelReader["CNPJ"].ToString()) && !String.IsNullOrEmpty(excelReader["RAZAO_SOCIAL"].ToString()))
{
if (ExistCnpj(companyId, excelReader))
{
// verifies if the cnpj is related with some customer
if (CustomerManager.GetCustomer(companyId, excelReader["CNPJ"].ToString()) == null)
{
AttachCustomerToLegalEntityProfile(companyId, userId, excelReader);
continue;
}
else
{
errors++;
}
continue;
}
legalEntityProfile = FillLegalEntityProfileData(excelReader);
if (!ValidateLegalEntityProfileData(legalEntityProfile) ||
!ValidateAddressData(profile.PostalCode ?? String.Empty, profile.AddressNumber ?? String.Empty, profile.AddressComp ?? String.Empty))
{
errors++;
continue;
}
//
// insert a customer
//
customer.CompanyId = companyId;
customer.ModifiedDate = DateTime.Now;
customer.LegalEntityProfile = legalEntityProfile;
CustomerManager.Insert(customer);
//
// insert a contact and related it with customer
//
if (ExistsColumm("CONTATO"))
{
if (!String.IsNullOrEmpty(excelReader["CONTATO"].ToString()))
{
SaveCustomerContact(customer, userId, excelReader["CONTATO"].ToString());
}
}
listCNPJ.Add(excelReader["CNPJ"].ToString());
}
}
message = "Total de Registros: " + totalRegisters + "; Registros importados com sucesso: " + Convert.ToInt32(totalRegisters - errors) +
"; Registros não importados por incompatibilidade de dados: " + errors;
excelConnection.Close();
}
}