owners = [] ownerdonations = [] donationtypes = [] ppo = {} ppt = {} asm.setid("owner", 100) asm.setid("ownerdonation", 100) asm.setid("donationtype", 100) print "DELETE FROM owner WHERE ID >= 100;" print "DELETE FROM ownerdonation WHERE ID >= 100;" print "DELETE FROM donationtype WHERE ID >= 100;" # customers.csv for row in asm.csv_to_list(PATH + "customers.csv"): if ppo.has_key(row["id"]): continue o = asm.Owner() owners.append(o) ppo[row["id"]] = o o.OwnerForeNames = row["First"] o.OwnerSurname = row["Last"] o.OwnerName = o.OwnerForeNames + " " + o.OwnerSurname o.OwnerAddress = row["Address"] o.OwnerTown = row["City"] o.OwnerCounty = row["State"] o.OwnerPostcode = row["Zip"] o.HomeTelephone = row["Phone"] o.CreatedDate = asm.getdate_ddmmyyyy(row["DateAdd"]) o.Comments = row["Notes"]
print "DELETE FROM animalmedicaltreatment WHERE ID >= 100;" print "DELETE FROM animalvaccination WHERE ID >= 100;" print "DELETE FROM owner WHERE ID >= 100;" print "DELETE FROM ownerdonation WHERE ID >= 100;" print "DELETE FROM vaccinationtype;" to = asm.Owner() to.OwnerSurname = "Unknown Transfer Owner" owners.append(to) ro = asm.Owner() ro.OwnerSurname = "Unknown Reclaim Owner" owners.append(ro) # people.csv for row in asm.csv_to_list(PATH + "people.csv"): o = asm.Owner() ppo[row["id"]] = o owners.append(o) o.OwnerForeNames = row["first name"] o.OwnerSurname = row["last name"] o.HomeTelephone = row["home phone"] o.WorkTelephone = row["work phone"] o.MobileTelephone = row["cell phone"] o.EmailAddress = row["primary email"] o.OwnerAddress = row["address"] + " " + row["second address line"] o.OwnerTown = row["city"] o.OwnerCounty = row["region/state"] o.OwnerPostcode = row["postalCode"] o.Comments = "%s %s %s" % (row["general comments"], row["hidden comments"], row["banned comments"]) if row["banned date"] != "": o.IsBanned = 1
#!/usr/bin/python import asm, datetime def findin(d, k, v, k2): """ Finds the value for k2 in list d where k = v """ for a in d: if a[k] == v: return a[k2] return "" canimal = asm.csv_to_list("data/hs0701_ac/Animal.xlsx.csv") cacat = asm.csv_to_list("data/hs0701_ac/aCat.xlsx.csv") cadog = asm.csv_to_list("data/hs0701_ac/aDog.xlsx.csv") caother = asm.csv_to_list("data/hs0701_ac/aOther.xlsx.csv") canimalnote = asm.csv_to_list("data/hs0701_ac/Animal_Note.xlsx.csv") ccustomeranimallink = asm.csv_to_list( "data/hs0701_ac/CustomerAnimalLink.xlsx.csv") ccustomer = asm.csv_to_list("data/hs0701_ac/Customer.xlsx.csv") ccustomernote = asm.csv_to_list("data/hs0701_ac/Customer_Note.xlsx.csv") cdisposition = asm.csv_to_list("data/hs0701_ac/Disposition.xlsx.csv") cintake = asm.csv_to_list("data/hs0701_ac/Intake.xlsx.csv") cmicrochip = asm.csv_to_list("data/hs0701_ac/Microchip.xlsx.csv") cnote = asm.csv_to_list("data/hs0701_ac/Note.xlsx.csv") cadoptions = asm.csv_to_list("data/hs0701_ac/dAdoption.xlsx.csv") cdied = asm.csv_to_list("data/hs0701_ac/dDiedInCare.xlsx.csv")
print "DELETE FROM animalmedical WHERE ID >= %s;" % START_ID print "DELETE FROM animalmedicaltreatment WHERE ID >= %s;" % START_ID print "DELETE FROM dbfs WHERE ID >= %s;" % START_ID print "DELETE FROM media WHERE ID >= %s;" % START_ID print "DELETE FROM owner WHERE ID >= %s;" % START_ID print "DELETE FROM ownerdonation WHERE ID >= %s;" % START_ID print "DELETE FROM adoption WHERE ID >= %s;" % START_ID print "DELETE FROM log WHERE ID >= %s;" % START_ID uo = asm.Owner() uo.OwnerSurname = "Unknown" uo.OwnerName = "Unknown" owners.append(uo) # load lookups into memory cnotes = asm.csv_to_list(PATH + "dbo_tblNotes.csv") cspecies = asm.csv_to_list(PATH + "dbo_tblSpecies.csv") cbreeds = asm.csv_to_list(PATH + "dbo_tblAnimalBreeds.csv") ctypes = asm.csv_to_list(PATH + "dbo_AnimalType.csv") cpaymentmethods = asm.csv_to_list(PATH + "dbo_tblPaymentTypes.csv") clocations = asm.csv_to_list(PATH + "dbo_tblPhysicalLocation.csv") # tblDocument.csv for row in asm.csv_to_list(PATH + "dbo_tblDocument.csv"): if row["objectypeid"] == "0" and row["extension"] == "jpg" and row[ "isDefault"] == "-1": documents[row["objectid"]] = PATH + "images/doc_%s.jpg" % row["docID"] # tblNotes for r in cnotes: if r["fieldText"] != "":
asm.setid("owner", 100) asm.setid("adoption", 100) owners = [] movements = [] animals = [] animalvaccinations = [] animalmedicals = [] # List of trackids we've seen for animals so far ppa = {} # List of trackids we've seen for people so far ppo = {} for d in asm.csv_to_list("%s/animals.csv" % PATH): # New animal record if we haven't seen this trackid before trackid = d["TrackID"].strip() if not trackid in ppa: extradata = "" a = asm.Animal() ppa[trackid] = a a.AnimalName = d["Name"] a.AnimalTypeID = gettype(d["Type"]) a.SpeciesID = getspecies(d["Type"]) if d["Entry Date"].strip() != "": a.DateBroughtIn = getdate(d["Entry Date"]) a.Neutuered = d["Spay"].strip() == "Y" and 1 or 0 a.CombiTested = 1 a.FLVResult = d["Fel Leuk"].strip() == "Y" and 0 or 1
START_ID) print("DELETE FROM owner WHERE ID >= %s AND LastChangedBy = 'conversion';" % START_ID) print("DELETE FROM adoption WHERE ID >= %s AND LastChangedBy = 'conversion';" % START_ID) # print("DELETE FROM media;") # Create an unknown owner uo = asm.Owner() owners.append(uo) uo.OwnerSurname = "Unknown Owner" uo.OwnerName = uo.OwnerSurname # Deal with people first for d in asm.csv_to_list(PERSON_FILENAME, remove_non_ascii=True): # Each row contains a person o = asm.Owner() owners.append(o) ppo[d["People_Ctr"]] = o o.OwnerForeNames = d["PERSONFIRSTNAME"] o.OwnerSurname = d["PERSONLASTNAME"] o.OwnerName = o.OwnerForeNames + " " + o.OwnerSurname o.OwnerAddress = d["PERSONADDRESS"] o.OwnerTown = d["PERSONCITY"] o.OwnerCounty = d["PERSONSTATE"] o.OwnerPostcode = d["PERSONZIPCODE"] o.EmailAddress = d["PERSONEMAIL"] o.WorkTelephone = d["PERSONWORKPHONE"] o.MobileTelephone = d["PERSONCELLPHONE"] o.IsBanned = asm.iif(d["PERSONFLAGS"].find("Banned") != -1, 1, 0)
print "DELETE FROM animalmedicaltreatment WHERE ID >= 100;" print "DELETE FROM animalvaccination WHERE ID >= 100;" print "DELETE FROM owner WHERE ID >= 100;" print "DELETE FROM ownerdonation WHERE ID >= 100;" print "DELETE FROM vaccinationtype;" to = asm.Owner() to.OwnerSurname = "Unknown Transfer Owner" owners.append(to) ro = asm.Owner() ro.OwnerSurname = "Unknown Reclaim Owner" owners.append(ro) # people.csv for row in asm.csv_to_list(PATH + "people.csv"): o = asm.Owner() ppo[row["id"]] = o owners.append(o) o.OwnerForeNames = row["first name"] o.OwnerSurname = row["last name"] o.HomeTelephone = row["home phone"] o.WorkTelephone = row["work phone"] o.MobileTelephone = row["cell phone"] o.EmailAddress = row["primary email"] o.OwnerAddress = row["address"] + " " + row["second address line"] o.OwnerTown = row["city"] o.OwnerCounty = row["region/state"] o.OwnerPostcode = row["postalCode"] o.Comments = "%s %s %s" % (row["general comments"], row["hidden comments"], row["banned comments"])
asm.setid("owner", 100) asm.setid("ownerdonation", 100) asm.setid("adoption", 100) asm.setid("media", 100) asm.setid("dbfs", 300) print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100;" print "DELETE FROM media WHERE ID >= 100;" print "DELETE FROM dbfs WHERE ID >= 200;" print "DELETE FROM owner WHERE ID >= 100;" print "DELETE FROM ownerdonation WHERE ID >= 100;" print "DELETE FROM animalvaccination WHERE ID >= 100;" print "DELETE FROM adoption WHERE ID >= 100;" cadoptions = asm.csv_to_list("data/kc0748_almosthome/adoptions.csv") ccats = asm.csv_to_list("data/kc0748_almosthome/cats.csv") cdonors = asm.csv_to_list("data/kc0748_almosthome/donordatabase.csv") cpeople = asm.csv_to_list("data/kc0748_almosthome/people.csv") creceipts = asm.csv_to_list("data/kc0748_almosthome/receipts.csv") cvolunteers = asm.csv_to_list("data/kc0748_almosthome/volunteers.csv") vaccmap = { "Leuk": 12, "ombo": 9, "all": 9, "All": 9, "fvrcp": 9, "FVCC": 9, "FVRC": 9, "FVRCP": 9,
#uo.OwnerName = "Unknown Owner" #uo.Comments = "Catchall for adopted animal data from ShelterLuv" print("\\set ON_ERROR_STOP\nBEGIN;") print("DELETE FROM adoption WHERE ID >= %s;" % START_ID) print("DELETE FROM animal WHERE ID >= %s;" % START_ID) print("DELETE FROM animalmedical WHERE ID >= %s;" % START_ID) print("DELETE FROM animalmedicaltreatment WHERE ID >= %s;" % START_ID) print("DELETE FROM animaltest WHERE ID >= %s;" % START_ID) print("DELETE FROM animalvaccination WHERE ID >= %s;" % START_ID) print("DELETE FROM owner WHERE ID >= %s;" % START_ID) print("DELETE FROM ownerdonation WHERE ID >= %s;" % START_ID) print("DELETE FROM testtype WHERE ID >= %s;" % START_ID) print("DELETE FROM vaccinationtype WHERE ID >= %s;" % START_ID) for d in asm.csv_to_list("%s/people.csv" % PATH): if d["Name"] == "Name": continue # skip repeated header rows if d["Name"] in ppo: continue # skip repeated rows # Each row contains a person o = asm.Owner() owners.append(o) ppo[d["Name"]] = o # ppo[d["Person ID"]] = o # Never seen one in a person file so have to use name o.SplitName(d["Name"]) o.OwnerAddress = d["Street"] o.OwnerTown = d["City"] o.OwnerCounty = d["State"] o.OwnerPostcode = d["Zip Code"] o.EmailAddress = d["Primary Email"] o.HomeTelephone = d["Phone"] # Last file I saw had repeated "Comments" columns, so need to be renumbered manually
asm.setid("animalvaccination", START_ID) asm.setid("animalmedical", START_ID) asm.setid("animalmedicaltreatment", START_ID) print "DELETE FROM animal WHERE ID >= %s;" % START_ID print "DELETE FROM animalvaccination WHERE ID >= %s;" % START_ID print "DELETE FROM animalmedical WHERE ID >= %s;" % START_ID print "DELETE FROM animalmedicaltreatment WHERE ID >= %s;" % START_ID print "DELETE FROM dbfs WHERE ID >= %s;" % START_ID print "DELETE FROM media WHERE ID >= %s;" % START_ID print "DELETE FROM owner WHERE ID >= %s;" % START_ID print "DELETE FROM ownerdonation WHERE ID >= %s;" % START_ID print "DELETE FROM adoption WHERE ID >= %s;" % START_ID # load lookups into memory cnotes = asm.csv_to_list(PATH + "tblNotes.csv") cspecies = asm.csv_to_list(PATH + "tblSpecies.csv") cbreeds = asm.csv_to_list(PATH + "tblAnimalBreeds.csv") ctypes = asm.csv_to_list(PATH + "AnimalType.csv") cpaymentmethods = asm.csv_to_list(PATH + "tblPaymentTypes.csv") # tblDocument.csv for row in asm.csv_to_list(PATH + "tblDocument.csv"): if row["objectypeid"] == "0" and row["extension"] == "jpg" and row[ "isDefault"] == "-1": documents[row["objectid"]] = PATH + "images/doc_%s.jpg" % row["docID"] # tblNotes for r in cnotes: if r["fieldText"] != "": animalnotes[r["animalID"]] = r["fieldText"]
ppo = {} ppa = {} numused = {} asm.setid("owner", 100) asm.setid("ownerlicence", 100) asm.setid("animal", 100) # Remove existing print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100;" print "DELETE FROM ownerlicence WHERE ID >= 100;" print "DELETE FROM owner WHERE ID >= 100;" # Load up data files cfile = asm.csv_to_list(PATH, unicodehtml=True) # Each row contains a person, animal and licence for row in cfile: # person first personkey = row["Surname"] + row["Forename"] + row["Address #"] if ppo.has_key(personkey): o = ppo[personkey] else: o = asm.Owner() owners.append(o) ppo[personkey] = o o.OwnerForeNames = row["Forename"] o.OwnerSurname = row["Surname"] o.OwnerName = o.OwnerForeNames + " " + o.OwnerSurname
owners = [] movements = [] animals = [] ppa = {} ppo = {} asm.setid("animal", 100) asm.setid("owner", 100) asm.setid("adoption", 100) print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM owner WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM adoption WHERE ID >= 100 AND CreatedBy = 'conversion';" cadoptions = asm.csv_to_list("data/sz1242_access/adoptions.csv", unicodehtml=True) ccatinfo = asm.csv_to_list("data/sz1242_access/catinfo.csv", unicodehtml=True) creturns = asm.csv_to_list("data/sz1242_access/returns.csv", unicodehtml=True) for d in ccatinfo: a = asm.Animal() animals.append(a) ppa[d["CatName"]] = a a.AnimalTypeID = 12 # stray cat if d["AcquiredType"].startswith("County"): a.AnimalTypeID = 44 # county shelter cat a.TransferIn = 1 a.SpeciesID = 2 a.AnimalName = d["CatName"] if a.AnimalName.strip() == "": a.AnimalName = "(unknown)"
nextmovementid = 100 startanimalid = 100 startanimalcontrolid = 100 startownerdonationid = 100 startownerid = 100 startmovementid = 100 asm.setid("donationtype", 100) unknown = asm.Owner(nextownerid) owners.append(unknown) nextownerid += 1 unknown.OwnerSurname = "Unknown" unknown.OwnerName = unknown.OwnerSurname for row in asm.csv_to_list(PATH + "list1.csv", strip=True): o = asm.Owner(nextownerid) owners.append(o) nextownerid += 1 o.ExtraID = row["MasterID"] o.OwnerTitle = row["TITLE"] o.OwnerForeNames = row["FIRSTNAME"] o.OwnerSurname = row["LASTNAME"] if o.OwnerSurname == "": o.OwnerSurname = "(blank)" o.OwnerName = o.OwnerForeNames + " " + o.OwnerSurname o.OwnerAddress = row["ADD1"] o.OwnerTown = row["CITY"] o.OwnerCounty = row["STATE"] o.OwnerPostcode = row["ZIP"] o.HomeTelephone = row["PHONENUMB"] o.WorkTelephone = row["BPHONE"]
asm.setid("animal", 100) asm.setid("owner", 100) asm.setid("ownerdonation", 100) asm.setid("ownerlicence", 100) asm.setid("adoption", 100) asm.setid("animalcontrol", 100) print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100 AND ID < 49999;" print "DELETE FROM animalcontrol WHERE ID >= 100 AND ID < 49999;" print "DELETE FROM owner WHERE ID >= 100 AND ID < 49999;" print "DELETE FROM ownerdonation WHERE ID >= 100 AND ID < 49999;" print "DELETE FROM ownerlicence WHERE ID >= 100 AND ID < 49999;" print "DELETE FROM adoption WHERE ID >= 100 AND ID < 49999;" for p in asm.csv_to_list("%s/NAMES.csv" % PATH): o = asm.Owner() owners.append(o) ppo[p["ID"]] = o o.OwnerForeNames = p["F_NAME"] o.OwnerSurname = p["L_NAME"] o.OwnerAddress = "%s %s\n%s" % (p["ADR_ST_NUM"], p["ADR_ST_NAM"], p["ADR_LINE2"]) o.OwnerTown = p["CITY"] o.OwnerCounty = p["STATE"] o.OwnerPostcode = p["ZIP"] o.HomeTelephone = p["H_PHONE"] o.WorkTelephone = p["W_PHONE"] comments = "ID: %s" % p["ID"] comments += "\n%s" % asm.nulltostr(p["NAMES_TXT"]) o.Comments = comments
# Create a transfer owner to = asm.Owner() owners.append(to) to.OwnerSurname = "Other Shelter" to.OwnerName = to.OwnerSurname # Create an unknown owner uo = asm.Owner() owners.append(uo) uo.OwnerSurname = "Unknown Owner" uo.OwnerName = uo.OwnerSurname # Load up data files caddress = asm.csv_to_list("%s/address.csv" % PATH, uppercasekeys=True, strip=True) caddrlink = asm.csv_to_list("%s/addrlink.csv" % PATH, uppercasekeys=True, strip=True) canimal = asm.csv_to_list("%s/animal.csv" % PATH, uppercasekeys=True, strip=True) clicense = asm.csv_to_list("%s/license.csv" % PATH, uppercasekeys=True, strip=True) cperson = asm.csv_to_list("%s/person.csv" % PATH, uppercasekeys=True, strip=True) cshelter = asm.csv_to_list("%s/shelter.csv" % PATH, uppercasekeys=True,
print "DELETE FROM animal WHERE ID >= 100;" print "DELETE FROM animalvaccination WHERE ID >= 100;" print "DELETE FROM owner WHERE ID >= 100;" print "DELETE FROM ownerlicence WHERE ID >= 100;" print "DELETE FROM adoption WHERE ID >= 100;" print "DELETE FROM media WHERE ID >= 100;" print "DELETE FROM dbfs WHERE ID >= 300;" # Create a transfer owner o = asm.Owner() owners.append(o) o.OwnerSurname = "Other Shelter" o.OwnerName = o.OwnerSurname # Load up data files caddress = asm.csv_to_list("%s/address.csv" % PATH, uppercasekeys=True, strip=True) caddrlink = asm.csv_to_list("%s/addrlink.csv" % PATH, uppercasekeys=True, strip=True) canimal = asm.csv_to_list("%s/animal.csv" % PATH, uppercasekeys=True, strip=True) clicense = asm.csv_to_list("%s/license.csv" % PATH, uppercasekeys=True, strip=True) cperson = asm.csv_to_list("%s/person.csv" % PATH, uppercasekeys=True, strip=True) cshelter = asm.csv_to_list("%s/shelter.csv" % PATH, uppercasekeys=True, strip=True) cvacc = asm.csv_to_list("%s/vacc.csv" % PATH, uppercasekeys=True, strip=True) # Start with animals for row in canimal: a = asm.Animal() animals.append(a) ppa[row["ANIMALKEY"]] = a a.AnimalTypeID = gettype(row["ANIMLDES"]) a.SpeciesID = asm.species_id_for_name(row["ANIMLDES"].split(" ")[0]) a.AnimalName = row["PETNAME"]
print "DELETE FROM animalvaccination WHERE ID >= %d AND CreatedBy = 'conversion';" % START_ID # Create a transfer owner to = asm.Owner() owners.append(to) to.OwnerSurname = "Other Shelter" to.OwnerName = to.OwnerSurname # Create an unknown owner uo = asm.Owner() owners.append(uo) uo.OwnerSurname = "Unknown Owner" uo.OwnerName = uo.OwnerSurname # Load up data files cadopt = asm.csv_to_list("%s/adoption_list.csv" % PATH, uppercasekeys=True) cdisp = asm.csv_to_list("%s/disposition.csv" % PATH, uppercasekeys=True) cmed = asm.csv_to_list("%s/medication_usage.csv" % PATH, uppercasekeys=True, unicodehtml=True) # Dispositions/animals for row in cdisp: if row["ANIMALUID"] in ppa: a = ppa[row["ANIMALUID"]] else: a = asm.Animal() animals.append(a) ppa[row["ANIMALUID"]] = a a.SpeciesID = asm.species_id_for_name(row["SPECIES"]) if a.SpeciesID == 1 and row["INTAKE"].startswith("Stray"):
owners = [] movements = [] animals = [] asm.setid("animal", 100) asm.setid("owner", 100) asm.setid("adoption", 100) asm.nextyearcode = 16 print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM owner WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM adoption WHERE ID >= 100 AND CreatedBy = 'conversion';" # Customers first for d in asm.csv_to_list("%s/customers.csv" % PATH): o = asm.Owner() owners.append(o) ppo[d["CustID"]] = o o.OwnerForeNames = ("%s %s" % (d["FName"], d["MName"])).strip() o.OwnerSurname = d["LName"] o.OwnerName = o.OwnerForeNames + " " + o.OwnerSurname o.OwnerAddress = "%s %s" % (d["Address1"], d["Address2"]) o.OwnerTown = d["City"] o.OwnerCounty = d["State"] o.OwnerPostcode = d["ZipCode"] o.HomeTelephone = d["HPhone"] o.MobileTelephone = d["BPhone"] o.Comments = d["Notes"] typemap = {
print "DELETE FROM animalvaccination WHERE ID >= 50000;" print "DELETE FROM log WHERE ID >= 50000;" print "DELETE FROM owner WHERE ID >= 50000;" print "DELETE FROM ownerlicence WHERE ID >= 50000;" print "DELETE FROM adoption WHERE ID >= 50000;" print "DELETE FROM media WHERE ID >= 50000;" print "DELETE FROM dbfs WHERE ID >= 50000;" # Create a transfer owner to = asm.Owner() owners.append(to) to.OwnerSurname = "Other Shelter" to.OwnerName = to.OwnerSurname # Load up data files canimaldispo = asm.csv_to_list("%s/sysAnimalDispositionChoices.csv" % PATH) canimalrectypes = asm.csv_to_list("%s/sysAnimalReceivedTypes.csv" % PATH) canimalstatuses = asm.csv_to_list("%s/sysAnimalStatusChoices.csv" % PATH) cbreeds = asm.csv_to_list("%s/sysBreeds.csv" % PATH) ccolors = asm.csv_to_list("%s/sysCoatColors.csv" % PATH) cgenders = asm.csv_to_list("%s/sysGenderChoices.csv" % PATH) cpens = asm.csv_to_list("%s/sysPens.csv" % PATH) cshelterareas = asm.csv_to_list("%s/sysShelterAreas.csv" % PATH) cspecies = asm.csv_to_list("%s/sysSpecies.csv" % PATH) cvacctype = asm.csv_to_list("%s/sysVaccinations.csv" % PATH) cadoptions = asm.csv_to_list("%s/tblAdoptions.csv" % PATH) canimalguardians = asm.csv_to_list("%s/tblAnimalGuardians.csv" % PATH) canimals = asm.csv_to_list("%s/tblAnimals.csv" % PATH) canimalids = asm.csv_to_list("%s/tblAnimalIDs.csv" % PATH) canimalimages = asm.csv_to_list("%s/tblAnimalImages.csv" % PATH) canimalintakes = asm.csv_to_list("%s/tblAnimalIntakesDispositions.csv" % PATH)
asm.setid("animal", START_ID) asm.setid("owner", START_ID) asm.setid("adoption", START_ID) print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= %s AND CreatedBy = 'conversion';" % START_ID print "DELETE FROM owner WHERE ID >= %s AND CreatedBy = 'conversion';" % START_ID print "DELETE FROM adoption WHERE ID >= %s AND CreatedBy = 'conversion';" % START_ID # Create an unknown owner uo = asm.Owner() owners.append(uo) uo.OwnerSurname = "Unknown Owner" uo.OwnerName = uo.OwnerSurname for d in asm.csv_to_list("%s/animals.csv" % PATH): a = asm.Animal() animals.append(a) ppa[d["ID"]] = a if d["Species"] == "Cat": a.AnimalTypeID = 11 # Unwanted Cat elif d["Species"] == "Dog": a.AnimalTypeID = 2 # Unwanted Dog else: a.AnimalTypeID = 40 # Misc a.SpeciesID = asm.species_id_for_name(d["Species"]) a.AnimalName = d["Name"] if a.AnimalName.strip() == "": a.AnimalName = "(unknown)" a.DateBroughtIn = getdate(d["Date of Well Check"]) or getdate(d["Adopted Date"]) or getdate(d["Birthday"]) or asm.today() a.DateOfBirth = getdate(d["Birthday"]) or a.DateBroughtIn
uo = asm.Owner() owners.append(uo) uo.OwnerSurname = "Unknown Owner" uo.OwnerName = uo.OwnerSurname pf = "" if PETFINDER_ID != "": asm.setid("media", START_ID) asm.setid("dbfs", START_ID) print "DELETE FROM media WHERE ID >= %s;" % START_ID print "DELETE FROM dbfs WHERE ID >= %s;" % START_ID pf = asm.petfinder_get_adoptable(PETFINDER_ID) # Deal with people first (if set) if PERSON_FILENAME != "" and asm.file_exists(PERSON_FILENAME): for d in asm.csv_to_list(PERSON_FILENAME): # Ignore repeated headers if d["Person ID"] == "Person ID": continue # Each row contains a person o = asm.Owner() owners.append(o) ppo[d["Person ID"]] = o o.OwnerForeNames = d["Name First"] o.OwnerSurname = d["Name Last"] o.OwnerName = o.OwnerForeNames + " " + o.OwnerSurname o.OwnerAddress = d["Street Address"] o.OwnerTown = d["City"] o.OwnerCounty = d["Province"] o.OwnerPostcode = d["Postal Code"] o.EmailAddress = d["Email"] o.HomeTelephone = d["Phone Number"]
print "DELETE FROM dbfs WHERE ID >= %d;" % START_ID # Create a transfer owner to = asm.Owner() owners.append(to) to.OwnerSurname = "Other Shelter" to.OwnerName = to.OwnerSurname # And an unknown owner uo = asm.Owner() owners.append(uo) uo.OwnerSurname = "Unknown Owner" uo.OwnerName = uo.OwnerSurname # Load up data files canimaldispo = asm.csv_to_list("%s/sysAnimalDispositionChoices.csv" % PATH) canimalrectypes = asm.csv_to_list("%s/sysAnimalReceivedTypes.csv" % PATH) canimalstatuses = asm.csv_to_list("%s/sysAnimalStatusChoices.csv" % PATH) cbreeds = asm.csv_to_list("%s/sysBreeds.csv" % PATH) ccolors = asm.csv_to_list("%s/sysCoatColors.csv" % PATH) cgenders = asm.csv_to_list("%s/sysGenderChoices.csv" % PATH) cpens = asm.csv_to_list("%s/sysPens.csv" % PATH) cshelterareas = asm.csv_to_list("%s/sysShelterAreas.csv" % PATH) cspecies = asm.csv_to_list("%s/sysSpecies.csv" % PATH) cvacctype = asm.csv_to_list("%s/sysVaccinations.csv" % PATH) cadoptions = asm.csv_to_list("%s/tblAdoptions.csv" % PATH) canimalguardians = asm.csv_to_list("%s/tblAnimalGuardians.csv" % PATH) canimals = asm.csv_to_list("%s/tblAnimals.csv" % PATH) canimalids = asm.csv_to_list("%s/tblAnimalIDs.csv" % PATH) canimalimages = asm.csv_to_list("%s/tblAnimalImages.csv" % PATH) canimalintakes = asm.csv_to_list("%s/tblAnimalIntakesDispositions.csv" % PATH)
uo.OwnerName = "Unknown Owner" uo.Comments = "Catchall for adopted animal data from RescueGroups" print("\\set ON_ERROR_STOP\nBEGIN;") print("DELETE FROM adoption WHERE ID >= 100;") print("DELETE FROM animal WHERE ID >= 100;") print("DELETE FROM owner WHERE ID >= 100;") print("DELETE FROM ownerdonation WHERE ID >= 100;") if IMPORT_PICTURES: print("DELETE FROM media WHERE ID >= 100;") print("DELETE FROM dbfs WHERE ID >= 300;") pfpage = "" if PETFINDER_ID != "": pfpage = asm.petfinder_get_adoptable(PETFINDER_ID) for d in asm.csv_to_list("%s/Animals.csv" % PATH): if d["Status"] == "Deleted": continue a = asm.Animal() animals.append(a) if d["Species"] == "Cat": animaltype = 11 animalletter = "U" else: animaltype = 2 animalletter = "D" a.AnimalTypeID = animaltype a.SpeciesID = asm.species_id_for_name(d["Species"]) if "Animal ID" in d: a.ShortCode = "RG%s" % d["Animal ID"] a.ShelterCode = "%s %s" % (a.ShortCode, a.ID) ppa[d["Animal ID"]] = a
asm.setid("owner", 100) asm.setid("adoption", 100) print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM owner WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM adoption WHERE ID >= 100 AND CreatedBy = 'conversion';" # Create an unknown owner uo = asm.Owner() owners.append(uo) uo.OwnerSurname = "Unknown Owner" uo.OwnerName = uo.OwnerSurname # Each row contains an animal with possible adoption and owner info for d in asm.csv_to_list(FILENAME): a = asm.Animal() animals.append(a) # ACC Number if d["ACC Number"].strip() != "": asm.additional_field("accno", 2, a.ID, d["ACC Number"]) # animaltype is upstairs or downstairs, upstairs = 42, downstairs = 43 if d["Cat Cafe Down (Y/N)"] == "Y": a.AnimalTypeID = 43 a.generateCode("D") else: a.AnimalTypeID = 42 a.generateCode("U") # all cats a.SpeciesID = 2
a.Archived = 1 a.ActiveMovementID = m.ID a.ActiveMovementType = 5 a.LastChangedDate = m.MovementDate movements.append(m) # Process licence files LICENCE_FILES = [ "20082009", "20102011", "20122013", "20142015", "2016", "2018", "2019" ] for s in LICENCE_FILES: fname = PATH + s + ".csv" fdate = asm.getdate_yyyymmdd("%s/01/01" % s[:4]) asm.stderr("%s / %s" % (fname, fdate)) for d in asm.csv_to_list(fname): process_licence(d, fdate) # Process impound files IMPOUND_FILES = ["impound2017", "impound2018", "impound2019"] for s in IMPOUND_FILES: fname = PATH + s + ".csv" fdate = asm.getdate_yyyymmdd("%s/01/01" % s[7:]) asm.stderr("%s / %s" % (fname, fdate)) for d in asm.csv_to_list(fname): process_impound(d, fdate) # Now that everything else is done, output stored records for a in animals: print a for m in movements:
ppo = {} asm.setid("animal", 100) asm.setid("animalvaccination", 100) asm.setid("owner", 100) asm.setid("adoption", 100) # Remove existing print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100;" print "DELETE FROM animalvaccination WHERE ID >= 100;" print "DELETE FROM owner WHERE ID >= 100;" print "DELETE FROM adoption WHERE ID >= 100;" # Load up data files cowner = asm.csv_to_list("%s/tblOwnerDetails.csv" % PATH) canimal = asm.csv_to_list("%s/tblPetDetails.csv" % PATH) csurr = asm.csv_to_list("%s/tblSurrenderDetails.csv" % PATH) # People first for row in cowner: o = asm.Owner() owners.append(o) ppo[row["OwnerId"]] = o o.OwnerTitle = row["Title"] o.OwnerForeNames = row["FirstName"] o.OwnerSurname = row["Surname"] o.OwnerName = o.OwnerTitle + " " + o.OwnerForeNames + " " + o.OwnerSurname o.OwnerAddress = row["Street"] o.OwnerTown = row["Suburb"] o.OwnerCounty = row["State"]
uo = asm.Owner() owners.append(uo) uo.OwnerSurname = "Unknown Owner" uo.OwnerName = uo.OwnerSurname pf = "" if PETFINDER_ID != "": asm.setid("media", START_ID) asm.setid("dbfs", START_ID) print "DELETE FROM media WHERE ID >= %s;" % START_ID print "DELETE FROM dbfs WHERE ID >= %s;" % START_ID pf = asm.petfinder_get_adoptable(PETFINDER_ID) # Deal with people first (if set) if PERSON_FILENAME != "": for d in asm.csv_to_list(PERSON_FILENAME): # Each row contains a person o = asm.Owner() owners.append(o) ppo[d["Person ID"]] = o o.OwnerForeNames = d["Name First"] o.OwnerSurname = d["Name Last"] o.OwnerName = o.OwnerForeNames + " " + o.OwnerSurname o.OwnerAddress = "%s %s %s %s" % (d["Street Number"], d["Street Name"], d["Street Type"], d["Street Direction"]) o.OwnerTown = d["City"] o.OwnerCounty = d["Province"] o.OwnerPostcode = d["Postal Code"] o.EmailAddress = d["Email"] o.HomeTelephone = d["Phone Number"]
animalvaccinations = [] asm.setid("animal", 100) asm.setid("animaltest", 100) asm.setid("animalvaccination", 100) asm.setid("owner", 100) asm.setid("adoption", 100) print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM animaltest WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM animalvaccination WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM owner WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM adoption WHERE ID >= 100 AND CreatedBy = 'conversion';" for d in asm.csv_to_list("data/kw1533_excel.csv"): a = asm.Animal() animals.append(a) a.AnimalTypeID = "COALESCE((SELECT ID FROM animaltype WHERE AnimalType LIKE '%s%%' LIMIT 1), 11)" % d[ "Type"] a.EntryReasonID = 17 # Surrender if d["Entry Category"] == "Stray": a.EntryReasonID = 7 # Stray a.SpeciesID = asm.species_id_for_name(d["Species"]) a.AcceptanceNumber = d["Litter"] a.AnimalName = d["Name"] if a.AnimalName.strip() == "": a.AnimalName = "(unknown)" a.DateBroughtIn = getdate(d["Date brought in"]) if a.DateBroughtIn is None: a.DateBroughtIn = asm.today() if "Date Of Birth" in d and d["Date of Birth"].strip() != "":
asm.setid("adoption", 100) print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= 100 AND CreatedBy LIKE '%conversion';" print "DELETE FROM owner WHERE ID >= 100 AND CreatedBy = 'conversion';" print "DELETE FROM adoption WHERE ID >= 100 AND CreatedBy = 'conversion';" pf = "" if PETFINDER_ID != "": asm.setid("media", 100) asm.setid("dbfs", 200) print "DELETE FROM media WHERE ID >= 100;" print "DELETE FROM dbfs WHERE ID >= 200;" pf = asm.petfinder_get_adoptable(PETFINDER_ID) data = asm.csv_to_list(PATH) uo = asm.Owner() uo.OwnerSurname = "Unknown Owner" uo.OwnerName = uo.OwnerSurname owners.append(uo) for d in data: a = asm.Animal() animals.append(a) a.AnimalTypeID = asm.iif(d["Pet Type"] == "Cat", 11, 2) if a.AnimalTypeID == 11 and d["Intake Type"] == "Stray": a.AnimalTypeID = 12 a.SpeciesID = asm.species_id_for_name(d["Pet Type"]) a.AnimalName = d["Pet Name"] if a.AnimalName.strip() == "":
asm.setid("animal", 100) asm.setid("owner", 100) asm.setid("adoption", 100) asm.setid("animalmedical", 100) asm.setid("animalmedicaltreatment", 100) asm.setid("animalvaccination", 100) print "DELETE FROM animal WHERE ID >= 100;" print "DELETE FROM animalmedical WHERE ID >= 100;" print "DELETE FROM animalmedicaltreatment WHERE ID >= 100;" print "DELETE FROM animalvaccination WHERE ID >= 100;" print "DELETE FROM owner WHERE ID >= 100;" print "DELETE FROM adoption WHERE ID >= 100;" canimals = asm.csv_to_list(PATH + "animals.csv") cvacc = asm.csv_to_list(PATH + "vacc.csv") for row in canimals: a = asm.Animal() animals.append(a) ppa[row["Animal ID"]] = a a.AnimalName = row["Name"] if a.AnimalName.strip() == "": a.AnimalName = "(unknown)" typecol = row["Type"] breedcol = row["Breed"] breed2col = row["Secondary Breed"] a.AnimalTypeID = asm.type_id_for_name(typecol) a.generateCode(asm.type_name_for_id(a.AnimalTypeID)) a.SpeciesID = asm.species_id_for_name(typecol)
#print "DELETE FROM dbfs WHERE ID >= 300;" # Create a transfer owner o = asm.Owner() owners.append(o) o.OwnerSurname = "Other Shelter" o.OwnerName = o.OwnerSurname # Create an unknown adopter uo = asm.Owner() owners.append(uo) uo.OwnerSurname = "Unknown" uo.OwnerName = o.OwnerSurname # Load up data files caddress = asm.csv_to_list("%s/address.csv" % PATH, remove_control=True) caddrlink = asm.csv_to_list("%s/addrlink.csv" % PATH, remove_control=True) canimal = asm.csv_to_list("%s/animal.csv" % PATH, remove_control=True) clicense = asm.csv_to_list("%s/license.csv" % PATH, remove_control=True) cperson = asm.csv_to_list("%s/person.csv" % PATH, remove_control=True) cshelter = asm.csv_to_list("%s/shelter.csv" % PATH, remove_control=True) cvacc = asm.csv_to_list("%s/vacc.csv" % PATH, remove_control=True) # Next, addresses for row in caddress: addresses[row["ADDRESSKEY"]] = { "address": row["ADDRESSSTR"] + " " + row["ADDRESSST2"] + " " + row["ADDRESSST3"], "city": row["ADDRESSCIT"], "state": row["ADDRESSSTA"], "zip": row["ADDRESSPOS"] }
owners.append(to) ro = asm.Owner() ro.OwnerSurname = "Unknown Reclaim Owner" owners.append(ro) fo = asm.Owner() fo.OwnerSurname = "Unknown Foster Owner" owners.append(fo) uo = asm.Owner() uo.OwnerSurname = "Unknown Adopter" owners.append(uo) # people.csv for row in asm.csv_to_list(PATH + "people.csv", remove_non_ascii=True): if row["last name"] is None: continue o = asm.Owner() ppo[row["id"]] = o owners.append(o) o.OwnerForeNames = row["first name"] o.OwnerSurname = row["last name"] o.HomeTelephone = row["home phone"] o.WorkTelephone = row["work phone"] o.MobileTelephone = row["cell phone"] o.EmailAddress = row["primary email"] o.OwnerAddress = "%s %s" % (row["address"], row["second address line"]) o.OwnerTown = row["city"] o.OwnerCounty = row["region/state"] o.OwnerPostcode = row["postalCode"] o.Comments = "%s %s %s" % (row["general comments"], row["hidden comments"],
asm.setid("media", 100) asm.setid("owner", 100) asm.setid("ownerdonation", 100) asm.setid("adoption", 100) asm.setid("animalvaccination", 100) print "DELETE FROM animal WHERE ID >= 100;" print "DELETE FROM animalvaccination WHERE ID >= 100;" print "DELETE FROM dbfs WHERE ID >= 100;" print "DELETE FROM media WHERE ID >= 100;" print "DELETE FROM owner WHERE ID >= 100;" print "DELETE FROM ownerdonation WHERE ID >= 100;" print "DELETE FROM adoption WHERE ID >= 100;" # load lookups into memory cnotes = asm.csv_to_list(PATH + "tblnotes.csv") cspecies = asm.csv_to_list(PATH + "tblspecies.csv") cbreeds = asm.csv_to_list(PATH + "tblanimalbreeds.csv") ctypes = asm.csv_to_list(PATH + "animaltype.csv") cpaymentmethods = asm.csv_to_list(PATH + "tblpaymenttypes.csv") # parse a fast version of the document table where we can lookup # the image name for an animal's preferred picture for row in asm.csv_to_list(PATH + "tbldocument.csv"): if row["objectypeid"] == "0" and row["extension"] == "jpg" and row["isDefault"] == "-1": documents[row["objectid"]] = PATH + "images/doc_%s.jpg" % row["docID"] # tblsuburblist.csv for row in asm.csv_to_list(PATH + "tblsuburblist.csv"): s = SBSuburb() s.id = row["ID"].strip()
if PICTURE_IMPORT: asm.setid("dbfs", START_ID) print "\\set ON_ERROR_STOP\nBEGIN;" print "DELETE FROM animal WHERE ID >= %s AND CreatedBy = 'conversion';" % START_ID print "DELETE FROM owner WHERE ID >= %s AND CreatedBy = 'conversion';" % START_ID print "DELETE FROM adoption WHERE ID >= %s AND CreatedBy = 'conversion';" % START_ID if PICTURE_IMPORT: print "DELETE FROM media WHERE ID >= %d;" % START_ID if PICTURE_IMPORT: print "DELETE FROM dbfs WHERE ID >= %d;" % START_ID # Create an unknown owner uo = asm.Owner() owners.append(uo) uo.OwnerSurname = "Unknown Owner" uo.OwnerName = uo.OwnerSurname for d in asm.csv_to_list(ANIMALS, strip=True, remove_non_ascii=True): a = asm.Animal() animals.append(a) if d["Species"] == "Cat": a.AnimalTypeID = 11 # Unwanted Cat if d["Entry Category"] == "Stray": a.AnimalTypeID = 12 # Stray Cat elif d["Species"] == "Dog": a.AnimalTypeID = 2 # Unwanted Dog if d["Entry Category"] == "Stray": a.AnimalTypeID = 10 # Stray Dog else: a.AnimalTypeID = 40 # Misc a.SpeciesID = asm.species_id_for_name(d["Species"]) a.AnimalName = d["Animal name"] if a.AnimalName.strip() == "":