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