private Guid GetAddress(ExcelDataItem item)
{
Addresses address = new Addresses(Utils.ConnectionString());
List <Addresses> addressesForPostcode = address.GetAddressesByAddress_Postcode(item.Owner_Postcode);
if (addressesForPostcode.Count > 0)
{
//
foreach (Addresses postcodeAddress in addressesForPostcode)
{
if (postcodeAddress.Address_1 == item.Owner_Address_1)
{
return(postcodeAddress.Address_ID);
}
}
}
//Create New Address
address.Address_1 = item.Owner_Address_1;
address.Address_2 = item.Owner_Address_2;
address.Address_Town = item.Owner_Town;
address.Address_County = item.Owner_County;
address.Address_Postcode = item.Owner_Postcode;
Guid?owner_Address_ID = address.Insert_Address(user_ID);
return((Guid)owner_Address_ID);
}
private Guid GetOwner(Guid owner_Address_ID, ExcelDataItem item)
{
People owner = new People(Utils.ConnectionString());
List <People> ownerList = owner.GetPeopleByAddress_ID(owner_Address_ID);
if (ownerList.Count > 0)
{
foreach (People person in ownerList)
{
if (person.Person_Forename == item.Owner_First_Name && person.Person_Surname == item.Owner_Last_Name)
{
return(person.Person_ID);
}
}
}
//Create New Person
owner.Person_Title = item.Owner_Title;
owner.Person_Surname = item.Owner_Last_Name;
owner.Person_Forename = item.Owner_First_Name;
owner.Address_ID = owner_Address_ID;
owner.Person_Landline = item.Owner_Phone;
owner.Person_Email = item.Owner_Email;
Guid?owner_ID = owner.Insert_Person(user_ID);
return((Guid)owner_ID);
}
private Guid CreateEntrant(ExcelDataItem item)
{
Entrants entrant = new Entrants(Utils.ConnectionString());
entrant.Show_ID = show_ID;
entrant.Entry_Date = item.Date_Of_Entry;
entrant.Withold_Address = !item.Show_Address_In_Catalogue;
return((Guid)entrant.Insert_Entrant(user_ID));
}
private bool CreateDatabaseEntry(ExcelDataItem item)
{
Guid owner_Address_ID = GetAddress(item);
owner_ID = GetOwner(owner_Address_ID, item);
dog_ID = GetDog(item);
LinkDogToOwner(owner_ID, dog_ID);
if (owner_ID != prev_Owner_ID)
{
entrant_ID = CreateEntrant(item);
}
return(AddDogToClass(item));
}
private bool AddDogToClass(ExcelDataItem item)
{
bool ok = true;
ShowEntryClasses showEntryClasses = new ShowEntryClasses(Utils.ConnectionString());
ShowEntryClasses sec = showEntryClasses.GetShowEntryClassByShowAndClassNo(show_ID, item.Entered_Class);
Judges judges = new Judges(Utils.ConnectionString(), sec.Show_Entry_Class_ID);
if (sec != null)
{
dogClassCount++;
if (dog_ID == prev_Dog_ID)
{
if (dogClassCount > dogClassMax)
{
Utils.LogToFile("Maximum number of classes would be exceeded");
ok = false;
}
}
else
{
dogClassCount = 1;
}
if (ok)
{
if (CorrectGenderForClass(sec.Class_Gender, item.Sex))
{
showEntryClassID = sec.Show_Entry_Class_ID;
DogClasses dc = new DogClasses(Utils.ConnectionString());
List <DogClasses> dcList = dc.GetDog_ClassesByDog_IDAndShow_Entry_Class_ID(dog_ID, showEntryClassID);
if (dcList.Count > 0)
{
Utils.LogToFile("Dog already entered in this class.");
ok = false;
}
else
{
dc.Entrant_ID = entrant_ID;
dc.Dog_ID = dog_ID;
dc.Show_Entry_Class_ID = showEntryClassID;
if (!string.IsNullOrEmpty(item.Preferred_Judge))
{
dc.Preferred_Part = GetPreferredPart(judges, item.Preferred_Judge);
}
dc.Handler_ID = owner_ID;
dc.Special_Request = item.Notes_To_Organiser;
dc.Running_Order = item.RunningOrder;
Guid?dogClassID = dc.Insert_Dog_Class(user_ID);
if (dogClassID == null)
{
Utils.LogToFile("Failed to insert to tblDogClasses");
ok = false;
}
}
}
else
{
Utils.LogToFile("Incorrect gender for class");
ok = false;
}
}
}
else
{
Utils.LogToFile("Unable to get Show Entry Class by Show ID and Class No.");
ok = false;
}
if (!ok)
{
string msg = string.Format("Unable to add {0} ({1}) to class {2}", item.Registered_Name, item.Registered_Number, (sec != null) ? sec.Class_Name_Description : item.Entered_Class.ToString());
Utils.LogToFile(msg);
}
return(ok);
}
private List <ExcelDataItem> ReadExcelData()
{
List <ExcelDataItem> excelDataItems = new List <ExcelDataItem>();
if (excelFile != "")
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(excelFile, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
int rowNumber = 1;
int recsAdded = 0;
int previousCol = -1;
int currentCol = 0;
string lastRegName = "";
bool eof = false;
foreach (Row excelDataRow in rows)
{
bool ok = true;
ExcelDataItem item = new ExcelDataItem();
if (rowNumber == 1)
{
//Column Headings
}
if (rowNumber > 1 && !eof)
{
item.RowNumber = rowNumber;
//Getting Competitor Data (31 cols)
foreach (Cell cell in excelDataRow.Descendants <Cell>())
{
int tempInt = -1;
string tempRegNo = "";
if (eof)
{
ok = false;
break;
}
currentCol = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
switch (currentCol)
{
case 0:
string showName = GetCellValue(spreadsheetDocument, cell).ToString();
item.Show_Name = showName;
break;
case 1:
int.TryParse(GetCellValue(spreadsheetDocument, cell), out tempInt);
if (tempInt > 0)
{
item.Owner_ID = tempInt;
}
else
{
item.Owner_ID = 0;
//Utils.LogToFile(string.Format("Owner ID was not numeric for record number {0}", rowNumber));
}
break;
case 2:
item.Owner_Title = GetCellValue(spreadsheetDocument, cell);
break;
case 3:
item.Owner_First_Name = CleanFirstName(GetCellValue(spreadsheetDocument, cell).ToLowerInvariant().Trim());
break;
case 4:
item.Owner_Last_Name = textInfo.ToTitleCase(GetCellValue(spreadsheetDocument, cell));
break;
case 5:
item.Owner_Address_1 = textInfo.ToTitleCase(GetCellValue(spreadsheetDocument, cell));
break;
case 6:
item.Owner_Address_2 = textInfo.ToTitleCase(GetCellValue(spreadsheetDocument, cell));
break;
case 7:
item.Owner_Town = textInfo.ToTitleCase(GetCellValue(spreadsheetDocument, cell));
break;
case 8:
item.Owner_County = textInfo.ToTitleCase(GetCellValue(spreadsheetDocument, cell));
break;
case 9:
item.Owner_Postcode = GetCellValue(spreadsheetDocument, cell).ToUpperInvariant();
break;
case 10:
item.Owner_Country = GetCellValue(spreadsheetDocument, cell);
break;
case 11:
item.Owner_Phone = GetCellValue(spreadsheetDocument, cell);
break;
case 12:
item.Owner_Email = GetCellValue(spreadsheetDocument, cell);
break;
case 13:
item.Owner_Registered_Name = textInfo.ToTitleCase(GetCellValue(spreadsheetDocument, cell));
break;
case 14:
item.Vehicle_Registration = GetCellValue(spreadsheetDocument, cell);
break;
case 15:
item.Registered_Name = CleanDogName(GetCellValue(spreadsheetDocument, cell).ToLowerInvariant().Trim());
break;
case 16:
tempRegNo = GetCellValue(spreadsheetDocument, cell).ToUpperInvariant();
item.Registered_Number = FixKCName(tempRegNo, item.Registered_Name != lastRegName);
lastRegName = item.Registered_Name;
break;
case 17:
item.Breed = textInfo.ToTitleCase(GetCellValue(spreadsheetDocument, cell));
if (string.IsNullOrWhiteSpace(item.Breed))
{
Utils.LogToFile(string.Format("Dog Breed not specified for record number {0}", rowNumber));
item.Breed = "Not Specified";
}
break;
case 18:
item.Sex = GetCellValue(spreadsheetDocument, cell);
break;
case 19:
int.TryParse(GetCellValue(spreadsheetDocument, cell), out tempInt);
if (tempInt > 0)
{
if (tempInt > 3000)
{
item.Date_Of_Birth = Utils.FromExcelSerialDate(tempInt);
item.Year_Of_Birth = (short)item.Date_Of_Birth.Year;
}
else
{
item.Year_Of_Birth = (short)tempInt;
}
}
else if (tempInt == 0)
{
item.Date_Of_Birth = new DateTime();
}
else
{
Utils.LogToFile(string.Format("Invalid date of birth for record number {0}", rowNumber));
}
break;
//case 19:
// item.Breeder = GetCellValue(spreadsheetDocument, cell);
// break;
//case 20:
// item.Sire = GetCellValue(spreadsheetDocument, cell);
// break;
//case 21:
// item.Dam = GetCellValue(spreadsheetDocument, cell);
// break;
case 20:
int.TryParse(GetCellValue(spreadsheetDocument, cell), out tempInt);
if (tempInt > -1)
{
item.Merit_Points = tempInt;
}
else
{
Utils.LogToFile(string.Format("Merit Points was not numeric for record number {0}", rowNumber));
ok = false;
}
break;
case 21:
int.TryParse(GetCellValue(spreadsheetDocument, cell), out tempInt);
if (tempInt > -1)
{
item.Entered_Class = tempInt;
}
else
{
Utils.LogToFile(string.Format("Entered Class was not numeric for record number {0}", rowNumber));
ok = false;
}
break;
case 22:
item.Preferred_Judge = GetCellValue(spreadsheetDocument, cell);
break;
case 23:
item.Extras = GetCellValue(spreadsheetDocument, cell);
break;
case 24:
item.Confirmed_Acceptance_Of_Declaration = GetCellValue(spreadsheetDocument, cell) == "Yes";
break;
case 25:
item.Withdraw_All_Entries_If_Balloted_Out_Of_Championship_C = GetCellValue(spreadsheetDocument, cell) == "Yes";
break;
case 26:
item.Show_Address_In_Catalogue = GetCellValue(spreadsheetDocument, cell) == "Yes";
break;
case 27:
item.Notes_To_Organiser = GetCellValue(spreadsheetDocument, cell);
break;
case 28:
var DoE = GetCellValue(spreadsheetDocument, cell);
if (DoE.ToString() == "WyD")
{
int yr = DateTime.Now.Year;
item.Date_Of_Entry = new DateTime(yr, 04, 01);
break;
}
int.TryParse(DoE, out tempInt);
if (tempInt > 0)
{
item.Date_Of_Entry = Utils.FromExcelSerialDate(tempInt);
}
else
{
item.Date_Of_Entry = null;
Utils.LogToFile(string.Format("Invalid date of entry for record number {0}", rowNumber));
}
break;
case 29:
item.ImportRecord = GetCellValue(spreadsheetDocument, cell) == "Y";
break;
case 30:
int.TryParse(GetCellValue(spreadsheetDocument, cell), out tempInt);
if (tempInt > 0)
{
item.RunningOrder = (short)tempInt;
}
break;
}
previousCol = currentCol;
}
if (item.Show_Name == null || item.Show_Name.Length == 0)
{
eof = true;
break;
}
if (ok)
{
excelDataItems.Add(item);
recsAdded++;
}
}
previousCol = -1;
rowNumber++;
}
Utils.LogToFile(string.Format("{0} records retrieved from the spreadsheet", recsAdded));
}
}
return(excelDataItems);
}
private Guid GetDog(ExcelDataItem item)
{
Dogs dog = new Dogs(Utils.ConnectionString());
List <Dogs> dogList = dog.GetDogByRegNo(item.Registered_Number);
if (dogList.Count > 0)
{
//Get ID for existing Dog
bool saveChanges = false;
foreach (Dogs regDog in dogList)
{
if (regDog.Dog_KC_Name != item.Registered_Name)
{
Utils.LogToFile(string.Format("Registered Name Changed from {0} to {1} for Reg No.: {2}", regDog.Dog_KC_Name, item.Registered_Name, item.Registered_Number));
regDog.Dog_KC_Name = item.Registered_Name;
saveChanges = true;
}
if (regDog.Date_Of_Birth != item.Date_Of_Birth)
{
Utils.LogToFile(string.Format("Date of Birth Changed from {0} to {1} for Reg No.: {2}", regDog.Date_Of_Birth, item.Date_Of_Birth, item.Registered_Number));
if (item.Date_Of_Birth != null && item.Date_Of_Birth != new DateTime())
{
Utils.LogToFile(string.Format("Date of Birth Changed from {0} to {1} for Reg No.: {2}", regDog.Date_Of_Birth, item.Date_Of_Birth, item.Registered_Number));
regDog.Date_Of_Birth = item.Date_Of_Birth;
}
else
{
Utils.LogToFile(string.Format("Date of Birth Changed from {0} to {1} for Reg No.: {2}", regDog.Date_Of_Birth, "NULL", item.Registered_Number));
regDog.Date_Of_Birth = null;
}
saveChanges = true;
}
if (regDog.Year_Of_Birth != item.Year_Of_Birth)
{
Utils.LogToFile(string.Format("Year of Birth Changed from {0} to {1} for Reg No.: {2}", regDog.Year_Of_Birth, item.Year_Of_Birth, item.Registered_Number));
regDog.Year_Of_Birth = item.Year_Of_Birth;
saveChanges = true;
}
if (saveChanges)
{
regDog.Update_Dog(regDog.Dog_ID, user_ID);
}
return(regDog.Dog_ID);
}
}
//Create New Dog
dog.Dog_KC_Name = item.Registered_Name;
dog.Dog_Breed_ID = GetBreedIDFromBreedName(item.Breed);
dog.Dog_Gender_ID = GetGender(item.Sex);
dog.Reg_No = item.Registered_Number;
if (item.Date_Of_Birth != null && item.Date_Of_Birth != new DateTime())
{
dog.Date_Of_Birth = item.Date_Of_Birth;
}
else
{
dog.Date_Of_Birth = null;
}
if (item.Year_Of_Birth > 0)
{
dog.Year_Of_Birth = item.Year_Of_Birth;
}
dog.Merit_Points = (short)item.Merit_Points;
//dog.Breeder = item.Breeder;
//dog.Sire = item.Sire;
//dog.Dam = item.Dam;
Guid?dog_ID = dog.Insert_Dog(user_ID);
return((Guid)dog_ID);
}
