public int PushFinancialStatesToDb(FinancialState financialStateObject)
{
using (var context = new CrossFinanceContext())
{
context.FinancialStates.Add(financialStateObject);
context.SaveChanges();
}
return(financialStateObject.Id);
}
public ImportService.FinancialState MapToServiceObj(FinancialState financialState)
{
var serviceFinancialState = new ImportService.FinancialState()
{
Interest = financialState.Interests,
PenaltyInterest = financialState.PenaltyInterests,
Fees = financialState.Fees,
CourtFees = financialState.CourtFees,
CourtRepresentationFees = financialState.RepresentationCourtFees,
Capital = financialState.Capital
};
return(serviceFinancialState);
}
private string checkData(Person person, FinancialState financial)
{
string error = string.Empty;
PeselValidation peselValidation = new PeselValidation();
if (!peselValidation.Valid(person.NationalIdentificationNumber))
{
error += "Pesel: " + person.NationalIdentificationNumber + " is incorrect";
}
if (financial.Id != 0)
{
error += "Wrong data in FinancialState.";
}
return(error);
}
public FinancialState CreateFromDataTable(DataTable dataTable, int row)
{
FinancialState financialState = new FinancialState()
{
Interests = ConvertToDecimal(GetValueFromColumn(dataTable, InterestsCaption, row)),
PenaltyInterests = ConvertToDecimal(GetValueFromColumn(dataTable, PentalyInterestsCaption, row)),
Fees = ConvertToDecimal(GetValueFromColumn(dataTable, FeesCaption, row)),
CourtFees = ConvertToDecimal(GetValueFromColumn(dataTable, CourtFeesCaption, row)),
RepresentationCourtFees = ConvertToDecimal(GetValueFromColumn(dataTable, RepresentationCourtFeesCaption, row)),
VindicationCosts = ConvertToDecimal(GetValueFromColumn(dataTable, VindicationCostsCaption, row)),
RepresentationVindicationCosts = ConvertToDecimal(GetValueFromColumn(dataTable, RepresentationVindicationCostsCaption, row)),
Capital = ConvertToDecimal(GetValueFromColumn(dataTable, CapitalCaption, row)),
};
return(financialState);
}
public List <Agreement> ImportRecords(string path)
{
DataTable dataTable = excelImporter.ImportExcelFileToDataTable(path); // tutaj to ma byc?
List <Agreement> agreements = new List <Agreement>();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
Address address = _addressBuilder.CreateFromDataTable(dataTable, i);
Person person = _personBuilder.CreateFromDataTable(dataTable, i, address);
FinancialState financialState = _financialStateBuilder.CreateFromDataTable(dataTable, i);
Agreement agreement = _agreementBuilder.CreateFromDataTable(dataTable, i, person, financialState);
agreements.Add(agreement);
}
return(agreements);
}
public void Export(Person person, Address address, FinancialState financial, Agreement agreement)
{
CFServiceReference.ImportServiceClient importService = new CFServiceReference.ImportServiceClient();
CFServiceReference.Address webServiceAdress = new CFServiceReference.Address
{
City = address.PostOfficeCity,
PostalCode = address.PostCode,
HouseNo = address.StreetNumber,
LocaleNo = address.FlatNumber,
Street = address.StreetName
};
CFServiceReference.Address[] tabAddress = { webServiceAdress };
CFServiceReference.IdentityDocument document = new CFServiceReference.IdentityDocument
{
Number = agreement.Number
};
CFServiceReference.IdentityDocument[] tabDocuments = { document };
CFServiceReference.FinancialState webServiceFinancialState = new CFServiceReference.FinancialState
{
CourtFees = financial.CourtFees,
CourtRepresentationFees = financial.RepresentationCourtFees,
Fees = financial.Fees,
Interest = financial.Interests,
EnforcementFees = financial.RepresentationVindicationCosts,
EnforcementRepresentationFees = financial.RepresentationCourtFees,
Capital = financial.OutstandingLiabilites,
PenaltyInterest = financial.PenaltyInterests,
};
CFServiceReference.Person webServicePerson = new CFServiceReference.Person
{
Name = person.FirstName,
Surname = person.Surname,
NationalIdentificationNumber = person.NationalIdentificationNumber,
Addresses = tabAddress,
ExtensionData = person.ExtensionData,
IdentityDocuments = tabDocuments,
FinancialState = webServiceFinancialState
};
//TODO: połączyć się
//importService.DoImport(webServicePerson);
}
/// <summary>
/// Action of <see cref="PersonController" />.
/// <list type="destination"><item><term>Creates a special view model <see cref="FullInfoViewModel" /></term></item><item><term>Redirects to <see cref="Person" /> full information page with created view model.</term></item></list>
/// </summary>
/// <param name="currPerson">Requested Person, about whom to show full information.
/// Creating automatically, by model binding mechanism, using relevant fields of <see cref="Person" />.</param>
/// <param name="fullName">Full name of clicked Person</param>
/// <returns>Returns a view with the name of "<c>Views/Person/PersonFull info.cshtml</c>", filled with <see cref="FullInfoViewModel" /> data.</returns>
public ViewResult PersonFullInfo(Person currPerson, string fullName)
{
Address currAddress = _repository.AddressRepository.AddressList.First(a => a.Id == currPerson.AddressId);
Agreement currAgreement = _repository.AgreementRepository.AgreementList.First(a => a.PersonId == currPerson.Id);
FinancialState currFinancialState = _repository.FinancialStateRepository.FinancialStateList.First(f => f.Id == currAgreement.FinancialStateId);
FullInfoViewModel viewModel = new FullInfoViewModel
{
PersonParamValues = new List <string>
{
currPerson.FirstName,
currPerson.Surname,
currAgreement.Number,
currPerson.NationalIdentificationNumber,
currPerson.PhoneNumber,
currPerson.PhoneNumber2,
CreateFullAddress(currAddress.PostOfficeCity,
currAddress.StreetName,
currAddress.StreetNumber,
currAddress.FlatNumber,
currAddress.PostCode),
CreateFullAddress(currAddress.CorrespondencePostOfficeCity,
currAddress.CorrespondenceStreetName,
currAddress.CorrespondenceStreetNumber,
currAddress.CorrespondenceFlatNumber,
currAddress.CorrespondencePostCode)
},
FinancialStateParamValues = new List <string>
{
currFinancialState.OutstandingLiabilities.ToString(),
currFinancialState.Interests.ToString(),
currFinancialState.PenaltyInterests.ToString(),
currFinancialState.Fees.ToString(),
currFinancialState.CourtFees.ToString(),
currFinancialState.RepresentationCourtFees.ToString(),
currFinancialState.VindicationCosts.ToString(),
currFinancialState.RepresentationVindicationCosts.ToString()
}
};
return(View(viewModel));
}
/// <summary>
/// Uploads to database Excel file.
/// </summary>
/// <param name="pathName">Path, to temporary saved on server, copy of chosen Excel file.</param>
/// <returns>Returns the list of <see cref="Person" />, whose <see cref="Person.NationalIdentificationNumber" /> is not valid.</returns>
/// <permission cref="System.Security.PermissionSet"> Accessible from the outside.</permission>
public IList <Person> UploadToDBExcelFile(string pathName)
{
OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + pathName + "; Extended Properties='Excel 8.0;HDR=Yes'");
sheetName = "[" + GetSheetName(connection) + "]";
IList <Address> addressList = GetAddressTableData(connection);
IList <FinancialState> financialStateList = GetFinancialStateTableData(connection);
IList <Person> personList = GetPersonTableData(connection);
IList <Agreement> agreementList = GetAgreementTableData(connection);
IList <Person> invalidPersonList = new List <Person>();
int listPosIterator = 0;
foreach (var address in addressList)
{
Person currentPerson = personList.ElementAt(listPosIterator);
FinancialState currentFinancialState = financialStateList.ElementAt(listPosIterator);
Agreement currentAgreement = agreementList.ElementAt(listPosIterator);
if (IsAvailableForUpload(personList, currentPerson))
{
_repository.AddressRepository.SaveAddress(address);
_repository.FinancialStateRepository.SaveFinancialState(currentFinancialState);
currentPerson.AddressId = _repository.AddressRepository.AddressList.OrderByDescending(e => e.Id).First().Id;
_repository.PersonRepository.SavePerson(currentPerson);
currentAgreement.FinancialStateId = _repository.FinancialStateRepository.FinancialStateList.OrderByDescending(e => e.Id).First().Id;
currentAgreement.PersonId = _repository.PersonRepository.PersonList.OrderByDescending(e => e.Id).First().Id;
_repository.AgreementRepository.SaveAgreement(currentAgreement);
}
else
{
invalidPersonList.Add(currentPerson);
}
listPosIterator++;
}
return(invalidPersonList);
}
public FinancialState MapFinancialState(Worksheet worksheet, int i, Dictionary <string, int> columnNamesWithIndex)
{
var outstandingLiabilitesIndex = columnNamesWithIndex["Kapital"];
decimal outstandingLiabilites = (decimal)worksheet.Cells[outstandingLiabilitesIndex][i].Value;
var interestsIndex = columnNamesWithIndex["odsetki"];
decimal interests = (decimal)worksheet.Cells[interestsIndex][i].Value;
var penaltyInterestsIndex = columnNamesWithIndex["odsetki Karne"];
decimal penaltyInterests = (decimal)worksheet.Cells[penaltyInterestsIndex][i].Value;
var feesIndex = columnNamesWithIndex["opłaty"];
decimal fees = (decimal)worksheet.Cells[feesIndex][i].Value;
var courtFeesIndex = columnNamesWithIndex["koszty sadowe"];
decimal courtFees = (decimal)worksheet.Cells[courtFeesIndex][i].Value;
var representationCourtFeesIndex = columnNamesWithIndex["koszty zastepstwa sadowego"];
decimal representationCourtFees = (decimal)worksheet.Cells[representationCourtFeesIndex][i].Value;
var vindicationcostsIndex = columnNamesWithIndex["koszty egzekucji"];
decimal vindicationcosts = (decimal)worksheet.Cells[vindicationcostsIndex][i].Value;
var representationVindicationcostsIndex = columnNamesWithIndex["koszty zastepstwa egzekucyjnego"];
decimal representationVindicationcosts = (decimal)worksheet.Cells[representationVindicationcostsIndex][i].Value;
FinancialState newFinancialState = new FinancialState
{
OutstandingLiabilities = outstandingLiabilites,
Interests = interests,
PenaltyInterests = penaltyInterests,
Fees = fees,
CourtFees = courtFees,
RepresentationCourtFees = representationCourtFees,
VindicationCosts = vindicationcosts,
RepresentationVindicationCosts = representationVindicationcosts
};
return(newFinancialState);
}
public void Insert(FinancialState financialState)
{
string sql = @"INSERT INTO `importapp`.`financialstate` (`OutstandingLiabilites`, `Interests`, `PenaltyInterests`, `Fees`, `CourtFees`,
`RepresentationCourtFees`, `VindicationCosts`, `RepresentationVindicationCosts`)
VALUES (@OutstandingLiabilites, @Interests, @PenaltyInterests, @Fees, @CourtFees,
@RepresentationCourtFees, @VindicationCosts, @RepresentationVindicationCosts); SELECT LAST_INSERT_ID();";
var parameters = new DynamicParameters();
parameters.Add("@OutstandingLiabilites", financialState.OutstandingLiabilites);
parameters.Add("@Interests", financialState.Interests);
parameters.Add("@PenaltyInterests", financialState.PenaltyInterests);
parameters.Add("@Fees", financialState.Fees);
parameters.Add("@CourtFees", financialState.CourtFees);
parameters.Add("@RepresentationCourtFees", financialState.RepresentationCourtFees);
parameters.Add("@VindicationCosts", financialState.VindicationCosts);
parameters.Add("@RepresentationVindicationCosts", financialState.RepresentationVindicationCosts);
using (var connection = new MySqlConnection(Connections.ConnectionString))
{
financialState.Id = connection.ExecuteScalar <int>(sql, parameters);
}
}
/// <summary>
/// Gets list of <see cref="FinancialState" /> from Excel file;
/// </summary>
/// <param name="connection">Connection object to Excel file.</param>
/// <returns>Returns <see cref="FinancialState" /> list, type of <see cref="IList{FinancialState}" /></returns>
/// <permission cref="System.Security.PermissionSet"> Available only inside class <see cref="ExcelHelper" />.</permission>
private IList <FinancialState> GetFinancialStateTableData(OleDbConnection connection)
{
IList <FinancialState> financialStateList = new List <FinancialState>();
string selectCommand = "SELECT [Kapital]," +
"[odsetki]," +
"[odsetki Karne]," +
"[opłaty]," +
"[koszty sadowe]," +
"[koszty zastepstwa sadowego]," +
"[koszty egzekucji]," +
"[koszty zastepstwa egzekucyjnego] FROM" + sheetName;
OleDbCommand command = new OleDbCommand(selectCommand, connection);
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read() && reader[0].ToString() != String.Empty)
{
FinancialState newFinancialState = new FinancialState
{
OutstandingLiabilities = Decimal.Parse(reader[0].ToString()),
Interests = Decimal.Parse(reader[1].ToString()),
PenaltyInterests = Decimal.Parse(reader[2].ToString()),
Fees = !String.IsNullOrEmpty(reader[3].ToString()) ? Decimal.Parse(reader[3].ToString()) : (decimal?)null,
CourtFees = Decimal.Parse(reader[4].ToString()),
RepresentationCourtFees = !String.IsNullOrEmpty(reader[5].ToString()) ? Decimal.Parse(reader[5].ToString()) : (decimal?)null,
VindicationCosts = !String.IsNullOrEmpty(reader[6].ToString()) ? Decimal.Parse(reader[6].ToString()) : (decimal?)null,
RepresentationVindicationCosts = !String.IsNullOrEmpty(reader[7].ToString()) ? Decimal.Parse(reader[7].ToString()) : (decimal?)null,
};
financialStateList.Add(newFinancialState);
}
connection.Close();
return(financialStateList);
}
public bool InsertSingleRow(Person person, Address address, FinancialState financial, Agreement agreement)
{
using (var dbContextTransaction = Database.BeginTransaction())
{
try
{
Addresses.Add(address);
FinancialStates.Add(financial);
person.Address = address;
Persons.Add(person);
agreement.FinancialState = financial;
agreement.Person = person;
Agreements.Add(agreement);
dbContextTransaction.Commit();
SaveChanges();
return(true);
}
catch (Exception)
{
dbContextTransaction.Rollback();
return(false);
}
}
}
public static Agreement MapAgreement(this IExcelDataReader reader, Person person, FinancialState financialState)
{
return(new Agreement
{
Number = reader.GetValue(1).ToString(),
Person = person,
FinancialState = financialState
});
}
public Agreement CreateFromDataTable(DataTable dataTable, int row, Person person, FinancialState financialState)
{
Agreement agreement = new Agreement()
{
Number = GetValueFromColumn(dataTable, NumberCaption, row),
Person = person,
FinancialState = financialState
};
return(agreement);
}
public void Add(FinancialState financialstate)
{
_allFinancialStates.Add(financialstate);
}
/// <summary>
/// Saves new financial state.
/// </summary>
/// <param name="financialState">Financial state to save.</param>
/// <permission cref="System.Security.PermissionSet"> Accessible from the outside.</permission>
public void SaveFinancialState(FinancialState financialState)
{
context.FinancialStates.Add(financialState);
context.SaveChanges();
}