/// <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(); } }