Difference between revisions of "Person"

From WormBaseWiki
Jump to navigationJump to search
 
(447 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 +
==Form URL==
 +
http://mangolassi.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi (sandbox for testing)
  
 +
* http://mangolassi.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi?display_or_edit=edit  -- for edit mode
 +
* http://mangolassi.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi?display_or_edit=display  -- for display mode
 +
 +
==New postgreSQL Tables==
 +
# Data tables
 +
# History tables
 +
 +
Some data tables do not have history, only have joinkey (two#), value, two_timestamp (timestamp) :
 +
* two -- second column 'two' is an integer
 +
* two_unsubscribe -- second column is text
 +
* two_curator_ip -- second column is text
 +
* two_lineage - joinkey , two_sentname (not editable), two_othername (not editable), two_number (of the person connecting to), two_role, two_date1 (Beginning in the year), two_date2 (Through the year), two_sender (email of sender or REV- email for reverse connection). two_lineage is a 2 way connection table, connects to sender and recipient who is the other person role connecting identified by REV-.
 +
 +
All other data tables have joinkey (two#), two_order (integer), two_<table> (data -- text), two_curator (two# -- text), two_timestamp (timestamp).
 +
History tables have h_ in front to keep track of editing history (h_two_status) keeping same columns:
 +
* Data and History tables:
 +
* two_firstname -- single value. always has a value.
 +
* h_two_firstname -- single values. to keep track of editing history.
 +
* two_middlename -- single value.
 +
* h_two_middlename -- single value. to keep track of editing history.
 +
* two_lastname -- single value. always has a value.
 +
* h_two_lastname -- single values. to keep track of editing history.
 +
* two_standardname -- single value. always has a value.
 +
* h_two_standardname -- single values. to keep track of editing history.
 +
* two_street -- multi value.
 +
* h_two_street -- multi value. to keep track of editing history.
 +
* two_city -- single value.
 +
* h_two_city -- single value. to keep track of editing history.
 +
* two_state -- single value.
 +
* h_two_state -- single value. to keep track of editing history.
 +
* two_post -- single value.
 +
* h_two_post -- single value. to keep track of editing history.
 +
* two_country -- single value.
 +
* h_two_country -- single value. to keep track of editing history.
 +
* two_institution -- multi value.
 +
* h_two_institution -- multi value. to keep track of editing history.
 +
* two_old_institution -- multi value.
 +
* h_two_old_institution -- multi value. to keep track of editing history.
 +
* two_old_inst_date -- multi value. from time we knew it was current/got data
 +
* h_two_old_inst_date -- multi value. to keep track of editing history.
 +
* two_mainphone -- multi value.
 +
* h_two_mainphone -- multi value. to keep track of editing history.
 +
* two_labphone -- multi value.
 +
* h_two_labphone -- multi value. to keep track of editing history.
 +
* two_officephone -- multi value.
 +
* h_two_officephone -- multi value. to keep track of editing history.
 +
* two_otherphone -- multi value.
 +
* h_two_otherphone -- multi value. to keep track of editing history.
 +
* two_fax -- multi value.
 +
* h_two_fax -- multi value. to keep track of editing history.
 +
* two_email -- multi value.
 +
* h_two_email -- multi value. to keep track of editing history.
 +
* two_old_email -- multi value. (add to Person editor form section to log old_date from time we knew it was current/got data)
 +
* h_two_old_email_date -- multi value. to keep track of editing history.
 +
* two_pis -- multi value.
 +
* h_two_pis -- multi value. to keep track of editing history.
 +
* two_lab -- multi value.
 +
* h_two_lab -- multi value. to keep track of editing history.
 +
* two_oldlab -- multi value.
 +
* h_two_oldlab -- multi value. to keep track of editing history.
 +
* two_left_field -- single value.
 +
* h_two_left_field -- single value. to keep track of editing history.
 +
* two_unable_to_contact -- single value.
 +
* h_two_unable_to_contact -- single value. to keep track of editing history.
 +
* two_privacy -- single value.
 +
* h_two_privacy -- single value. to keep track of editing history.
 +
* two_aka_firstname -- multi value.
 +
* h_two_aka_firstname -- multi value. to keep track of editing history.
 +
* two_aka_middlename -- multi value.
 +
* h_two_aka_middlename -- multi value. to keep track of editing history.
 +
* two_aka_lastname -- multi value.
 +
* h_two_aka_lastname -- multi value. to keep track of editing history.
 +
* two_webpage -- multi value.
 +
* h_two_webpage -- multi value. to keep track of editing history.
 +
* two_wormbase_comment -- multi value.
 +
* h_two_wormbase_comment -- multi value. to keep track of editing history.
 +
* two_hide -- single value.
 +
* h_two_hide -- single value. to keep track of editing history.
 +
* two_status -- single value.
 +
* h_two_status -- single value. to keep track of editing history.
 +
* two_mergedinto -- single value.
 +
* h_two_mergedinto -- single value. to keep track of editing history.
 +
* two_acqmerge -- single value.
 +
* h_two_acqmerge -- single value. to keep track of editing history.
 +
* two_comment -- multi value.
 +
* h_two_comment -- multi value. to keep track of editing history.
 +
* two_usefulwebpage -- multi value.
 +
* h_two_usefulwebpage -- multi value. to keep track of editing history.
 +
 +
==Changes between old and new tables==
 +
Table in Postgress
 +
* Adding history tables as h_two_...
 +
* Removing two_apu_ tables, two_groups
 +
* Adding two_usefulwebpage, two_old_inst_date, two_old_email_date
 +
* Changing two_comment into normal table with order
 +
* Changing old_timestamp column to two_curator (all values will be 'two1')
  
 
==Questions==
 
==Questions==
 +
 +
'''Please add a button link to Person-editor to possible two. When curating new paper to mark possible match as real possible two, Example WBPaper00039714, 131412 Nicholas, Hannah R. two2627; the possible connection is visual only, could you make a button to mark it as real possible two?'''
 +
 +
'''Could you add 'Switch to display.' at the bottom of the page above lineage part? Person-editor editor mode'''
 +
 +
'''If you see WBPaper00039714, under 'affiliation' there are 3 affiliations, could this data for iwm2011 abstracts show under affiliations for xml part?'''
 +
 +
'''WBPaper00039713 none of the Person's linked to this paper were sent emails for verification june 28, 2011. This is 1 example only'''
 +
 +
'''WBPaper00039707 has all authors linked to virtual possible twos, but the regular program didn't connect them, is there a way that they can also be connected?'''
  
 
Please confirm that no one uses this form
 
Please confirm that no one uses this form
 
http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cgi
 
http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cgi
−
In the last year 111 IPs have used it (mostly once) but a couple of them over 100 each.  If you don't use it, is it in the emails you send out or in the submit forms in WormBase ?
+
In the last year 111 IPs have used it (mostly once) but a couple of them over 100 each.  If you don't use it, is it in the emails you send out or in the submit forms in WormBase ? -- J  answer Please keep form, must be the one linking to old papers associated but not verified -- Ceci ''Sounds good, added to list of forms to check -- J'' thanks ceci
  
 
1681 two_middlename without value, is that okay ?
 
1681 two_middlename without value, is that okay ?
−
* SELECT * FROM two_middlename WHERE two_middlename = \'\';
+
SELECT * FROM two_middlename WHERE two_middlename = ' ';  <-- no space in between the singlequotes --J  answer It was from when we thoght that a data was needed in tag, please delete if not needed -- Ceci.
 +
 
 +
1004 two_middlename with NULL order, value, old_timestamp, what should we do with that ?
 +
SELECT * FROM two_middlename WHERE two_order IS NULL;
 +
 
 +
answer i've been adding aka_middlename = NULL to every aka entry, if it is not necessary them we can delete it, but if it is necessary please keep it. Talk tomorrow? Ceci. I am also fixing some typos showing searching in new Person editor Ceci ''I don't know if you need it to say 'NULL' or not, but I thought you were never supposed to have blank values, you can skype me whenever you want -- J'' will talk in person about all middle/aka names, empty values
 +
 
 +
1) two_aka_midlename always has NULL (when none middlename to fill)
 +
2) two_middlename
 +
Primero dijimos all tags filled
 +
Mas adelante me parece recordar que habiamos dicho que cuando la gente no tiene middle name no se ponia.
 +
http://www.wormbase.org/db/misc/person?name=WBPerson1823;class=Person
 +
Si esto esta mal hay que volver a poner 1 espacio vacio a los que no tienen data en two_middename.
 +
 
 +
''I don't know if it needs to be a certain way, when I set it up I let you know how it should be and you should make a note of it so you can tell me if something is wrong later.''
 +
 
 +
 
 +
 
 +
two427 had under wormbase_comment :
 +
* two427  |        1 | Due to illness, please get in touch with: Mark Edgley (edgley@interchange.ubc.ca) for gene knockout inquiries. Teresa Rogalski (rogalski@zoology.ubc.ca) for muscle lab matters.  2003-03-07 18:36:25.089309-08 | 2003-03-19 11:38:57.295736-08 |
 +
* two427  |        1 | Lab:DM or VC | 2003-03-10 16:23:56.877045-08 | 2003-03-19 11:38:57.295736-08
 +
* two427  |        2 | DM his own lab and VC head of the KO | 2008-01-31 00:00:00-08 | 2008-01-30 10:04:25.884154-08
 +
Which has two order '1', I've deleted the first one, if you want to add it again as order '3' -- J.  answer Deleted OK, thanks Ceci
 +
 
 +
Please comment on these changes :
 +
* I've added history tables, and changed the postgres tables to store the curator in the 4th column where the old_timestamp used to be.  Updating data updates the data table with current timestamp ; removes any history data for that table-joinkey-order in the last 10 minutes, and inserts with current timestamp. answer OK Ceci
 +
 
 +
* Street field always has at least 4 fields.  answer YES ceci
 +
 
 +
* name and aka_name now show first/middle/last in a row with timestamp below them. answer Like it Ceci
 +
 
 +
* old_email shows data from old_email_date horizontally with timestamp. answer OK ceci
 +
 
 +
* old_institution shows old_inst_date data, and webpage shows usefulwebpage vertically because I assume you need more space, if you'd rather display them horizontally like old_email, let me know.  answer OK ceci
 +
 
 +
* All changes to the person editing section of the person editor are done, please let me know if I overlooked something. answer looks ok, will look more in detail, thanks Juancarlos ceci Cool, let me know.  Thanks -- J two_comment is missing ceci// added - J// thanks, c
 +
 
 +
* In the Search Paper (to create people from XML) section, do you want to see input fields to create new people for authors that are already verified 'YES' ?  Right now people that are verified 'YES' have a grey background, if you don't want to see the inputs, do you still want the grey background ?  Probably easier to talk about this in person / skype.  Some papers I tried were 00003865 00026893 -- J
 +
 
 +
* wow thanks, looks really great, will look in more detail. Yes talk in person, lots of new stuffs, when do you have time? I have to be in Pasadena on Friday, is that OK with you? ''See email, we can talk until around 1pm'' OK answered email
 +
 
 +
* I like the grey part. Paper 00026893 is showing aid77392 Davis J - xml Jerel C. Davis - 1 matches two4351 (Ralph E. Davis) -> Are matches to last names even when it doesn't match first initial or first name? ceci ''no, it's the same matching script that we wrote a few weeks ago, it matches to fullname exactly, we went over how that worked, right ?  If not we can go over it in person''
 +
 
 +
 
 +
* Last name Davis, first initial J shouldn't it match to two3329 Justin R Davis, two4914 Jamie Davis, two7656 Joseph S. Davis? ceci ''It matches exactly on xml first+last not initials, the single_match and histogram script you run manually matches with initials and creates them automatically, this is for manual curation''
 +
 
 +
* Like the grey background, but when clicked on Create people from XML volvio a crear Nadia D. Singh, va a haber un boton para marcar Do not create or ignore it?. Really love the multiple institutions - addresses (hide-show-assign), really like it!!! cec ''If you assign an institution it does something, if you don't it ignores the person''. Yes, I noticed after trying several different things, it is in may notes to talk to you tomorrow.
 +
 
 +
* could you add old_institution? it will be useful when creating Persons form oldpapers,o when pdf shows currently at different affiliation, I could add both datas. c - Create new people from XML. Please add old_institution to this part (some people no longer at inst) Just 1 line, no address. ''Are those two issues the same thing ?  If so the old institutions would be one of the 20 normal institutions, but you'd assign it under an old_inst dropdown instead of the normal inst dropdown.  So you're saying that if selected in the old_inst dropdown, it would only take the institution line from that inst# and add it to the two_old_institution and the current date to two_old_inst_date.  Would there only ever be one old institution ?  Would you ever want to add both old institution and current institution ?'' yes. sometimes I'd add both current and old institution
 +
 
 +
* WBPaper00026893. When editing another institution, it is showing street from 1st institution at creating people from XML ''fixed'' OK, thanks
 +
 
 +
* ''We only need to change this script for person stats: /home/postgres/work/get_stuff/for_paul/curation_stats/wbperson_creation_stats/get_recent.pl which keys off of the two_display.cgi (unless we change the two_display.cgi to keep working,do you want it to ?)'' YES  ''from To Do below it's decided that we'll have a display and editor in the person_editor with a checkbox for display mode (default off), and links between display - editor and editor - display.  New question : Sometimes outside people have to look at the paper display, will they ever need to look at some kind of person display ?  Because if so we don't want them to see the editor, so we should keep the two forms separate in that case.'' ok good idea, when people see their contact data to update, does it link to two_display? http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/person.cgi?action=Display&number=WBPerson1 ''no, the person.cgi gets its own person display independent from the two_display.cgi'' People outside Caltech don't ever use the two display, so we can get rid of it an just use the new person editor in display vs. edit mode.
 +
 
 +
* Confirm paper: When they connect papers through confirm_paper.cgi (always)
 +
It says ``WBPerson$two Thank you for updating your Author Person Paper
 +
connection''
 +
When they connect papers through confirm_paper.cgi (saying 'NO')
 +
It says ``assigned new join $join to author $aid because said no to
 +
paper $joinkey''
 +
When they comment through confirm_paper.cgi
 +
It says ``$two $curator comment for paper connections''
 +
 
 +
When someone verifies a paper through generic.cgi
 +
It says ``${wbperson}, thank you for updating your Author Person Paper
 +
connection''  but only if
 +
/home/postgres/public_html/cgi-bin/data/confirm_paper_mailing.txt
 +
hasn't changed any data in the last day.
 +
 
 +
1.- generic.cgi (where is it?)
 +
You can see almost all the forms (including this one) in the sitemap http://tazendra.caltech.edu/~azurebrd/cgi-bin/index.cgi
 +
You can also see it in the emails that you send people to verify
 +
papers, when anyone clicks yes or no it's a link to that form
 +
 
 +
Looking at site map/ generic
 +
http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi
 +
shows: Your IP is :
 +
I clicked yes to a paper
 +
http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi?action=VerifyPaper&two_number=two10606&aid=105080&pap_join=1&yes_no=YES
 +
 
 +
So when you ask me where is the generic.cgi should i tell you the above link or is there a shorter generic version?
 +
 
 +
The generic.cgi has multiple uses.  The default is to show the IP, the
 +
one that relates to person verification is
 +
http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi?action=VerifyPaper
 +
 
 +
but the whole link is more useful because it makes it clear what
 +
values need to be passed in, and in what format.
 +
 
 +
2.- confirm_paper.cgi
 +
http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cgi
 +
yes
 +
 
 +
* Kimberly flag papers for Person curation
 +
She's going to flag them in the table pap_curation_flags as
 +
'author_person_priority' if they're priority and it will not have a
 +
value if they're not priority.  So when you look at the checkout you
 +
can just look at the ones that are 'author_person_priority'.
 +
 
 +
Kimberly, this made me realize that since it's going into
 +
curation_flags the value can only either be there or not be there, so
 +
we might want to get rid of the 'blank' value because it implies that
 +
blank and not-priority are different, when they're stored the same
 +
way.  We could make it a checkbox like functional annotation.
 +
Also, to both of you, should I just set all existing papers to'priority' ?
 +
Sounds good to me, c
 +
 
 +
I'm fine with leaving it as a drop-down but with only two values and priority as the default.
 +
We could set all existing papers to priority for now, but that doesn't
 +
preclude us from re-categorizing some if we decide at a later time that
 +
Reviews, for example, could reasonably be set to not_priority, right? Kimberly
 +
 
 +
in the Enter New Papers section :
 +
- the author-person select at the top with the box for free pmids has an author-person dropdown with values priority and not_priority which populate  pap_curation_flags with 'author_person'
 +
- each pmid has an aut-per_priority dropdown that works the same way
 +
 
 +
I've updated the pap_match.pm to allow the extra flag
 +
 
 +
I'll now work on populating all the papers except for those with
 +
functional_annotation as 'author_person' priority in
 +
pap_curation_flags.  Cecilia, this means that in the future Kimberly
 +
will always flag this for you and not set that for those that are
 +
functional annotation, so you don't need to think about functional
 +
annotation anymore, just whether it's flagged as priority or not.
 +
Note that as of 2013-12-05, the functional_annotation flag was changed to now be non_nematode.
 +
 
 +
 
 +
* New script: that looks at all the stuff that was verified in the last 24 hours or by day, except for those exceptions below.
 +
I want to receive daily email notifications or list for every time a
 +
paper is verified NOT mine. Unless:
 +
- pap_author_verified 'NO Cecilia Nakamura'
 +
- pap_author_verified YES after verified NOT same day
 +
- do not read NOT if there is a YES in latest timestamp
 +
We'd get data from pap_author_verified, we can't tell what came in to there from
 +
generic.cgi as opposed to confirm_paper.cgi, we can only know the values and their timestamps.
 +
Thanks, ceci
 +
''And also filter out the papers you've already done, right ?  Do you
 +
want to give me the range or papers that you've already done so I can
 +
enter them ?''
 +
done, but I don't have documented all I have already curated, will mark them done/delete when I see them. ceci
 +
 
 +
* change two_display.cgi to keep working
 +
keys off means that it gets values from.  In this case, it means that
 +
it knows what tables to look at by looking at the code of the
 +
two_display which lists the tables.  So the two_display would need to
 +
list all the new tables and not the old tables.
 +
 
 +
Yo si quiero seguir usando el two_display!
 +
What's the advantage of the two_display vs. the future paper_editor ? J
 +
 
 +
http://tazendra.caltech.edu/~postgres/cgi-bin/cecilia/two_display.cgi
 +
I like to to-display because it shows only data, and I can do searches
 +
I use it all the time.
 +
 
 +
Ok, the searches should be better in the future person_editor, but the only-data display would be better in the two_display.cgi  Or we could add a section in the paper_editor to do only display.
 +
 
 +
Would this section be able to do also searches? Otherwise I'd like a two_display.cgi where I can both. search and display. c
 +
You could probably toggle in the front page whether you wanted to use
 +
it in editor mode, or in display mode.  But once you were in the
 +
search results page you couldn't change your mind at that point.  Or
 +
maybe in the editor there could be a link to the display and
 +
viceversa.  What would you prefer ?  (think about it, let me know in wiki) J
 +
I'd prefer a link to the display and viceversa. c ''ok''
 +
 
 +
* New two-display in Person-editor:
 +
** http://mangolassi.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi?display_or_edit=edit -- for edit mode
 +
** http://mangolassi.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi?display_or_edit=display -- for display mode
 +
 
 +
''Ceci, please fix the multiple entries in the postgres table two by running /home/cecilia/work/gaps_in_twos/get_recent.pl which now looks for gaps and tells you of multiples :''
 +
 
 +
*testdb=# SELECT * FROM two WHERE two = 871;
 +
**two871  | 871 | 2002-08-12 09:25:21.17393-07
 +
**two871  | 871 | 2004-11-27 14:44:38.614083-08
 +
 
 +
*testdb=# SELECT * FROM two WHERE two = 871 AND two_timestamp > '2004-11-26' AND two_timestamp < '2004-11-28';
 +
**two871  | 871 | 2004-11-27 14:44:38.614083-08
 +
 
 +
''Once you do the SELECT, you can do the DELETE to get rid of just that one'' Thanks, fixed.
 +
 
 +
Just recording your oral response.
 +
all Paper search in mangolassi are showing as:
 +
pap_curation_done author_person
 +
in red bold letters
 +
Just for me to see if it is the way i'd like it to show.
 +
Yes, i like it, impossible to miss it.
 +
 
 +
'''*Person_lineage: 2011-06-17'''
 +
'''/home/postgres/work/pgpopulation/two_people/two_lineage/update_twos_in_two_lineage.pl
 +
output files /home/postgres/work/pgpopulation/two_people/two_lineage/
 +
vim lineage_psql_commands
 +
Veo dos posibles soluciones al problema de IGNORE Dr. Chris Link
 +
IGNORE Dr. Chris Link
 +
UPDATE two_lineage SET joinkey = 'NO' WHERE two_sentname = 'Dr. Chris
 +
Link' AND two_othername = 'Dr. Andreas Kampktter' AND two_role =
 +
'Lab_visitor' AND two_sender = 'REV - kampkoet@uni-duesseldorf.de';
 +
UPDATE two_lineage SET two_number = 'NO' WHERE two_othername = 'Dr. Chris
 +
Link' AND two_sentname = 'Dr. Andreas Kampktter' AND two_role =
 +
'withLab_visitor' AND two_sender = 'kampkoet@uni-duesseldorf.de';
 +
IGNORE Dr. David Hall
 +
1a Is it possible for the script to not ignore data and only ignore titles?
 +
1b Add under line Lineage Connections with(Please do not add titles to names, script will ignore your data)???
 +
2 Changes in Person_lineage form:
 +
2a red letters (Your Email) and (Your Full name) change them for black color
 +
2b in section 'LINEAGE CONNECTIONS WITH '
 +
Relationship 1 :(keep relation I trained...etc)
 +
Change instead of 1 large blank space split it into 3 spaces, labeled First, Middle and Last names
 +
I like solution #2'''
 +
 
 +
==To Do==
 +
===For new person editor===
 +
Check these forms / scripts (get rid of two_fullname view and replace with code looking at firstname middlename and lastname) :
 +
* /home/postgres/public_html/cgi-bin/cecilia/two_display.cgi # OBSOLETE
 +
* /home/postgres/public_html/cgi-bin/cecilia/twoeditor.cgi # OBSOLETE
 +
* /home/postgres/public_html/cgi-bin/paper_editor.cgi # WAS OK
 +
* /home/postgres/work/get_stuff/for_paul/curation_stats/wbperson_creation_stats/get_recent.pl # WAS OK
 +
* /home/postgres/work/pgpopulation/two_people/two_lineage/update_twos_in_two_lineage.pl # WAS OK, looking at row[3] for curator now instead of old_timestamp
 +
* /home/azurebrd/public_html/cgi-bin/forms/paper_display.cgi # WAS OK
 +
* /home/azurebrd/public_html/cgi-bin/forms/person.cgi  # DONE
 +
* /home/azurebrd/public_html/cgi-bin/forms/person_lineage.cgi # DONE, replaced two_fullname with two_firstname two_middlename two_lastname. looking at row[3] for curator now instead of old_timestamp
 +
* /home/azurebrd/public_html/cgi-bin/forms/confirm_paper.cgi # NEEDS TO BE FIXED, looking at wpa instead of pap tables (?)
 +
* /home/azurebrd/public_html/cgi-bin/forms/generic.cgi?action=VerifyPaper&two_number=two11941&aid=125345&pap_join=1&yes_no=YES # WAS OK
 +
* /home/cecilia/UPLOAD/new-upload/connect_single_match_authors_and_get_histogram.pl # WAS OK, looking at row[3] for curator now instead of old_timestamp
 +
* /home/cecilia/UPLOAD/new-upload/verify_by_labs_or_lineage.pl # WAS OK
 +
* /home/cecilia/UPLOAD/new-upload/email_connected_authors.pl  # WAS OK, points to /home/postgres/work/pgpopulation/pap_papers/author_person/email_connected_authors.pl
 +
* /home/cecilia/work/gaps_in_twos/get_recent.pl      # WAS OK
 +
* /home/acedb/cecilia/citace_upload/get_pap_person_ace.pl
 +
Creando :
 +
* /home/acedb/cecilia/citace_upload/errors_in_pap_person.ace (vim this and get two# with no status)
 +
----
 +
''Cecilia, put range of papers to mark as curated for author_person here to populate pap_curation_done with value 'author_person' for those WBPaperIDs's joinkeys''
 +
'''33058-33200 33201-33300 33301-33400 33401-33500 33501-33586 33601-33644 34636-34641 35601-35700 35701-35800 35801-35900 35901-36000 36738 36750 36765 36796 36870 37108-37109 37135 37144 34157-37161 37637 37909 37923 37998 38037 38126 38142 38144 38152 38193 38341'''  When Person curation is done a message will show at top of Person editor page-> Search paper = as pap_curation_done author_person, in bold red letters.
 +
 
 +
Fix two10121 has no status, two11402 has no status, two11917 has no status, FIXED
 +
 
 +
===For paper Person verification===
 +
----
 +
* http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi?action=VerifyPaper&two_number=two11941&aid=125345&pap_join=1&yes_no=YES
 +
* vim public_html/cgi-bin/forms/confirm-paper.cgi (regular)
 +
* vim public_html/cgi-bin/forms/generic.cgi (generic)
 +
Change subject letter to email author for verification to show standard name instead of WBPerson id
 +
NO email to Cecilia standard automatic reply to paper verification, Yes to cronjobs to keep track of response instead of email to cecilia
 +
Possibly we could get rid of that and implement a separate way to
 +
track who's responded.  We're already planning to have a cronjob check
 +
all the 'NO' verification in a 24 hour period, we could have it check
 +
all the verifications and sort that into 'NO' and 'YES', or some other
 +
system that she'll device. ''Cecilia, please clarify what you want to change and how to change it'' Updated subject of confirm paper to not show WBPerson id and to not email me a copy of the of the thank you automatic reply.
 +
 
 +
* script to check last 24 hours of YES/NO  ''Cecilia, tell me when you want this to run, and how you want the data grouped (by person / by paper / by yes/no ?)''
 +
Run at noon. Data grouped by Papers verified NO, showing aid# + Person#. At the end of run, count of Persons verified YES  and count of #Papers.
 +
* http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cgi (check it lots of changes 2011-06-08)
 +
* Sat, 11 Jun 2011 Subject: Re: author_verified data in old paper tables from confirm_paper.cgi
 +
**Para ver papeles connectados/verificados a la tabla antigua:
 +
SELECT * FROM wpa_author_verified ORDER BY wpa_timestamp DESC;
 +
**Para ver el papel que corresponde al author:
 +
testdb=> SELECT * FROM pap_author WHERE pap_author = '98640';
 +
**Whatever is in the paper_editor.cgi is what's real.
 +
I wrote a script that I put here :
 +
(cd,ls) /home/postgres/work/get_stuff/for_cecilia/20110611_author_verified_wpa
 +
And it looks like almost all the values are the same in both tables,
 +
so I guess whoever used the confirm_paper.cgi was using it just to add
 +
values that already were there.
 +
If you see anything in wpa_author_verified that isn't in
 +
pap_author_verified let me know so we can look at why the script
 +
didn't find it.
 +
 
 +
===Person Update Form===
 +
* http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/person.cgi?action=Editor&number=two6742
 +
Update contact information form to have extra lines for old_institution, to not show akas, I don't want it to be editable, ''we'll talk about this later'' ok
 +
*UPDATED May 25, 2011: Changed colors. Deleted extra timestamp boxes, extra lines from city, state, post, country. Added old_timestamp with at lest 3 lines.
 +
 
 +
===Full name (discontinued) changed to manually get values===
 +
* Table full_name sees 3 tables, First + middle + last names, if a table is missing it doesn't see it.
 +
* Changed to manually get 3 values (first_name, middle_name, last_name) if available and combine them instead of full_name. This will resolve problem of people with no middle name, adding an empty value to middle_name tag.
 +
 
 +
===two_display -> Person editor===
 +
Old two_display replaced by this really awesome multi tasking new one. Can do multiple combination searches.
 +
*http://mangolassi.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi?display_or_edit=edit -- for edit mode
 +
*http://mangolassi.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi?display_or_edit=display -- for display mode
 +
 
 +
===Person editor: display mode, edit mode===
 +
* Functionality:
 +
 
 +
# Search - Display or Edit mode - Results Links to Person_editor
 +
## Priority = two#, will ignore rest.
 +
## Combine search = unlimited search fields
 +
### example- email + caltech + case insensitive = Will give results of all People with caltech in email address.
 +
### example- ^ (carat) Li (last_name) substring = will give results of all last names starting with Li
 +
### example- ard$ (last_name) + caltech (email) substring = two699 Alison Claire Woollard lastname : Woollard
 +
# Create New Person: Manual curation, New autofill for some tags.
 +
# Search Paper: Individual discriminate paper curation. Will show data from Pubmed xml. Required data: lastname, instituion and either inst or old_inst. Check curation_flags: author_person, if not flagged do not Person curate.
 +
## Curation_flags
 +
## Bold red message Person curation done.
 +
## WBPaper00026893 paper editor link.
 +
## Identifiers and pdf links
 +
## pmid 15965246 xml found
 +
## Affiliation : Stanford University, Stanford, California 94305, USA. ndsingh@stanford.edu
 +
## Inst1 - click to show another institution - hide another institution - show institutions with data only
 +
### institution : create all institutions linked to paper, current or old clicking to show another institution. This is a required data to create new Persons.
 +
### street  - auto fill. Data manually entered.
 +
### street  - auto fill. Data manually entered.
 +
### street  - auto fill. Data manually entered.
 +
### street  - auto fill. Data manually entered.
 +
### city    - auto fill. Data manually entered.
 +
### state  - auto fill. Data manually entered.
 +
### post    - auto fill. Data manually entered.
 +
### country - auto fill. Data manually entered.
 +
## Under this section will show data from xml and postgres pap_ tables:
 +
### From postgres pap_ tables: aid,author, possible two#,sent, verified.
 +
### From pubmed xml and Cecilia manual curation: standard name, firstname, middlename, lastname (required data to create new Person).
 +
### From Cecilia manual curation: email, inst, old inst (either inst or old_inst is required to create new Person).
 +
## Enter each author's data (only new Persons to create), Under inst select #1, #2 etc, as well of oldinst. NO# institution will not create new Person (For existing Person do not mark any Institution here).
 +
## CREATE people from XML. Required lastname, institution, either inst or old_inst. Will mark Paper as Person-verified. If paper is in the list but was already Person curated, click here without adding any inst or oldinst to mark paper as already curated. Clicking 'Create people from xml' will make a new entry in pap_curation_done for "author_person".
 +
## Creating persons from authors in WBPaper00026893 paper editor link.
 +
## Checkout next paper id WBPaper00026894 - check out previous paper - checkout page.
 +
# Checkout Papers: Will show papers not yet Person curated, flagged from 'author_person' priority in pap_curation_flags, Valid papers.
 +
 
 +
==scripts that run automatically==
 +
Note: make sure to keep it updated when adding new scripts and forms, or when I stop using
 +
others.  (also let Juancarlos know when stop using some so he can remove them from tazendra.)
 +
 
 +
script for person stats : run to get stats, and they're called by this script
 +
/home/postgres/work/get_stuff/for_paul/curation_stats/wrapper.sh
 +
which runs every monday at 2am.
 +
 
 +
/home/postgres/work/get_stuff/for_paul/curation_stats/wbperson_creation_stats/get_recent.pl
 +
 
 +
This one for person lineage stats :
 +
/home/postgres/work/get_stuff/for_paul/curation_stats/wbperson_lineage_stats/get_recent.pl
 +
 
 +
This one for paper-author-person stats :
 +
/home/postgres/work/get_stuff/for_paul/curation_stats/wbpaper_author_person_stats/get_recent.pl
 +
 
 +
Note: April 17, 2011 We only need to change the first one, which keys off of the two_display.cgi (unless we change the two_display.cgi to keep working,do you want it to ?)YES. keys off means that it gets values from.  In this case, it means that it knows what tables to look at by looking at the code of the two_display which lists the tables.  So the two_display would need to list all the new tables and not the old tables
 +
 
 +
==scripts and forms==
 +
Current May 18, 2011
 +
* /home/postgres/public_html/cgi-bin/cecilia/two_display.cgi
 +
* /home/postgres/public_html/cgi-bin/cecilia/twoeditor.cgi
 +
* /home/postgres/public_html/cgi-bin/paper_editor.cgi
 +
* /home/postgres/work/get_stuff/for_paul/curation_stats/wbperson_creation_stats/get_recent.pl
 +
* /home/azurebrd/public_html/cgi-bin/forms/paper_display.cgi
 +
* /home/azurebrd/public_html/cgi-bin/forms/person.cgi
 +
* /home/azurebrd/public_html/cgi-bin/forms/person_lineage.cgi
 +
* /home/azurebrd/public_html/cgi-bin/forms/confirm_paper.cgi
 +
* /home/azurebrd/work/parsings/authorperson/citaceLineage/update_twos_in_two_lineage.pl
 +
* /home/cecilia/UPLOAD/new-upload/connect_single_match_authors_and_get_histogram.pl
 +
* /home/cecilia/UPLOAD/new-upload/verify_by_labs_or_lineage.pl
 +
* /home/cecilia/UPLOAD/new-upload/email_connected_authors.pl
 +
* /home/cecilia/work/gaps_in_twos/get_recent.pl
 +
* /home/acedb/cecilia/citace_upload/get_pap_person_ace.pl
 +
* http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi?action=VerifyPaper
 +
* http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cgi
 +
* http://tazendra.caltech.edu/~azurebrd/cgi-bin/index.cgi (site map)
 +
* http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/person_lineage.cgi
 +
* http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/person.cgi
 +
 
 +
===tables for Person submition form===
 +
* two_firstname -- 1 field. Required.
 +
* two_middlename -- (if not middle name, empty value?, 1 empty space?). Will talk later.
 +
* two_lastname -- 1 field. Required.
 +
* two_standardname -- 1 field. Name as people would like to be addressed. Example: Tuco.
 +
* two_street -- always shows at least 4 fields.
 +
* two_city -- 1 field.
 +
* two_state -- 1 field.
 +
* two_post -- 1 field.
 +
* two_country -- 1 field.
 +
* two_institution -- multi value.
 +
* two_old_institution -- always shows at least 3 fields.
 +
* two_old_inst_date -- '''Should submitters enter old-date ???'''
 +
* two_mainphone -- 2 fields.
 +
* two_labphone -- 2 fields.
 +
* two_officephone -- 2 fields.
 +
* two_otherphone -- 2 fields.
 +
* two_fax -- 2 fields.
 +
* two_email -- 2 fields. Required.
 +
* two_old_email -- 2 fields.
 +
* two_old_email_date - '''Should submitters enter old-date???'''
 +
* two_oldlab -- 2 fields.
 +
* two_left_field -- 1 field.
 +
* two_webpage -- 2 fields.
 +
* two_lab -- 2 fields.
 +
* two_oldlab -- 2 fields.
 +
* two_left_field -- 1 field.
 +
* two_webpage -- 2 fields.
 +
 
 +
===tables for Person editor form===
 +
http://mangolassi.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi
 +
* two_number -- single value. Enter WBPerson number to edit data.
 +
* two_firstname -- single value. always has a value.
 +
* two_middlename -- single value. if not middle name, empty value?, 1 empty space?
 +
* two_lastname -- single value. always has a value. Required.
 +
* two_standardname -- single value. always has a value.
 +
* two_street -- always shows at least 4 fields.
 +
* two_city -- single value.
 +
* two_state -- single value.
 +
* two_post -- single value.
 +
* two_country -- single value.
 +
* two_institution -- multi value.
 +
* two_old_institution -- multi value. always shows at least 3 fields. do not forget to log old_date when entering old_institution data
 +
* two_old_inst_date -- multi value. log old_date from time we knew it was current/got data.
 +
* two_mainphone -- multi value.
 +
* two_labphone -- multi value.
 +
* two_officephone -- multi value.
 +
* two_otherphone -- multi value.
 +
* two_fax -- multi value.
 +
* two_email -- multi value.
 +
* two_old_email -- multi value. Do not forget to log old_date.
 +
* two_old_email_date -- multi value. Log old_date from time we knew it was current/got data.
 +
* two_pis -- multi value.
 +
* two_lab -- multi value.
 +
* two_oldlab -- multi value.
 +
* two_left_field -- single value.
 +
* two_unable_to_contact -- single value.
 +
* two_privacy -- single value. Person do not show in WormBase, only Suplemental information (not linked to Person).
 +
* two_aka_firstname -- multi value.
 +
* two_aka_middlename -- multi value.
 +
* two_aka_lastname -- multi value.
 +
* two_webpage -- multi value.
 +
* two_usefulwebpage -- multi value. Clicking a box next to two_webpage will activate this tag, created to mark webpages with useful information to create Persons from lab members data.
 +
* two_wormbase_comment -- multi value.
 +
* two_hide -- single value. Hyde data, usually email when people don't want their email addresses showing in our webpage. Do not send emails. Email deleted from two_email and entered into two_hide.
 +
* two_status -- single value.
 +
* two_mergedinto -- single value. Enter two# of the profile I want to keep. When Person were created double, I pick to delete the one with least papers connected. Transfer all current data to the selected profile (acqmerge), mark each paper connected to this profile in Paper_editor as NOT his/her and enter new join - possible two. And mark this Person as INVALID.
 +
* two_acqmerge -- single value. Enter two# of the profile you want to delete. When Person were created double, I pick to keep the one with more papers connected, and update contact data, lineage, papers connections and verify them if verified.
 +
* two_comment -- multi value.
 +
* two_usefulwebpage -- multi value. Tag where you can write 'useful' or 'not useful'. Not useful means webpage doesn't show lab members data,  do no open it to look for lab member info. Note: this is for future data (when new Person-editor is Live), prior to this all webpages will have empty tag. I will manually check each webpage in the future as extra time is available or when curating papers and have to refer to PI webpage. In Person display will show under same order: above webpage and below usefulness.
 +
* two_status -- single value. Entered automatically at Person creation. Manually mark as INVALID when created double. Don't forget to add values to mergeinto and acqmerfe. Invalid will show in bold red letters at top of two_display form.
 +
- To create the new Person after entering data: NO button, NO reload page (will create a new two#).
 +
- Link to Person display.
 +
history tables are invisible. keeping track of old_email and old_institution history.
 +
http://www.wormbase.org/db/misc/person?name=WBPerson3680;class=Person
 +
See under Tracking, old email and old institution are dated, showing history date (two_old_email_date and two_old_inst_date)
 +
 
 +
===person_lineage===
 +
All tables are single value. two_lineage is a 2 way connection table, connects to sender and recipient who is the other person role connecting identified by REV-.
 +
* joinkey: two#
 +
* two_sentname: Name or WBPerson# of the person sending data. In REV will be reverse.
 +
* two_othername: Name or WBPerson# of the person to link data from sender. In REV will be sender.
 +
* two_number: two number of othername, and this is the data that will show in WB website.
 +
* two_role: As trainer and trainee. 'I trained as a...' and 'I trained this person as a...'. To delete role in Person-editor scroll up above 'I trained as a PhD under'.
 +
* two_date1: starting year. To delete date1 in Person-editor scroll up above Present.
 +
* two_date2: ending year. To delete date2 in Person-editor scroll up above Present.
 +
* two_sender: when updated from person_lineage form it will show email from sender.
 +
* two_timestamp: not useful for editing.
 +
 
 +
===Tables for Confirm paper===
 +
* http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cgi
 +
It is not showing some papers, example WBPerson : 11871, it shows no papers.
 +
Showing old tables, fixed June 8, 2011.
 +
 
 +
Select your name among:  Pick
 +
Or tpe your WBPerson number:  Pick
 +
I am Cecilia, box click Yes or let unmarked to verified as the author not as Cecilia.
 +
 
 +
*table, WBPerson number, order, value
 +
* table:
 +
** firstname
 +
** middlename
 +
** lastname
 +
** institution
 +
** street
 +
** city
 +
** state
 +
** city
 +
** country
 +
** mainphone
 +
** officephone
 +
** labphone
 +
** otherphone
 +
** fax
 +
** email
 +
** old_email
 +
** pis
 +
** lab
 +
** oldlab
 +
** left_field
 +
** privacy
 +
** aka_firstname
 +
** aka_middlename
 +
** aka_lastname
 +
** webpage
 +
*** From each batch of papers below, please click the corresponding ``Select !'' button, and follow the directions there.
 +
Afterwards, come back to this page if there is another batch of papers you have not selected :
 +
1) Publications and Abstracts (not confirmed by author in black)
 +
2) Publications and Abstracts (author confirmed in red and blue)
 +
3) Papers are grouped in batches of 10 or less. When a batch is verified, these papers will go to the bottom grouped as blue or red accordingly. For your convenience the next unverified batch will show always on top in black as batch 'papers: 1 to 10'.
 +
4) If you have already confirmed some publications (in red and bue), there is no need to confirm them again unless you'd like to review them.
 +
 
 +
There are 6 matching papers :
 +
(Unverified are black, Yours are blue, Not Yours are red)
 +
*** Update all publications from this batch as Yours: (green color.
 +
clicking this button will show a page confirming data:
 +
Curator : Bob Clifford
 +
 
 +
WBPerson : 871
 +
Thank you for verifying these paper connections.
 +
 
 +
Please click here to select another batch if there is another still unselected.
 +
 
 +
Please leave a comment if something is incorrect or unclear.
 +
Your record has been updated and changes will show in a WormBase release soon.
 +
 
 +
Your publication record has been updated to show 00002009 as yours.
 +
Your publication record has been updated to show 00004490 as yours.
 +
Your publication record has been updated to show 00012571 as yours.
 +
Your publication record has been updated to show 00021701 as yours.
 +
Your publication record has been updated to show 00022892 as yours.
 +
*** Update all publications from this batch as Not Yours : (red color)
 +
* Automatic letters emailed by script after people verified papers. Thank you very much for helping associate your C. elegans publications and abstracts.
 +
**1. One is when confirmed by confirm_paper comes from (tazendra-..e/cecilia-2: vim /home/azurebrd/public_html/cgi-bin/forms/confirm_paper.cgi).
 +
**2. The other one is when confirmed by monthly letter with links to list of papers with link to paper and click here if Yes, click here is Not. Comes from generic.cgi. May 1, 2014 updated letter to:
 +
Thank you very much for helping associate your C. elegans and
 +
other nematodes publications and abstracts.
 +
 
 +
Updates will appear in the next release of WormBase in your WBPerson page
 +
http://www.wormbase.org [www.wormbase.org] .
 +
 
 +
The full release schedule is available here:
 +
http://www.wormbase.org/about/release_schedule
 +
 
 +
Please do not hesitate to contact me if you have any questions.
 +
Have a great day.
 +
 
 +
**2.1. generic.cgi (where is it?) You can see almost all the forms (including this one) in the sitemap http://tazendra.caltech.edu/~azurebrd/cgi-bin/index.cgi You can also see it in the emails that you send people to verify papers, when anyone clicks yes or no it's a link to that form
 +
Looking at site map/ generic http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi shows: Your IP is : I clicked yes to a paper http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi?action=VerifyPaper&two_number=two10606&aid=105080&pap_join=1&yes_no=YES
 +
So when you ask me where is the generic.cgi should i tell you the above link or is there a shorter generic version?
 +
The generic.cgi has multiple uses. The default is to show the IP, the one that relates to person verification is http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi?action=VerifyPaper
 +
but the whole link is more useful because it makes it clear what values need to be passed in, and in what format.
 +
**2.2. confirm_paper.cgi http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cgi yes
 +
* Email asking to verify papers with papers list with links to papers and Yes mine and Not mine click and links to update Person form and lineage. tazendra/cecilia/UPLOAD/new_upload/
 +
*http://tazendra.caltech.edu/~postgres/cgi-bin/paper_editor.cgi?curator_id=two1&action=Person+Author+Curation&two_number_search=11871
 +
Shows WBPaper00036637
 +
showing new tables.
 +
 
 +
FROM Paper editor:
 +
1.- http://tazendra.caltech.edu/~postgres/cgi-bin/paper_editor.cgi?
 +
number (paper number)to curate Person-paper connections by papers.
 +
* aid - author id number
 +
* author - author name
 +
* new - to add another possible connection to this author
 +
* possible - auto complete writing possible Person name adds Person number)
 +
* pos_time - time stamp from when connecting possible
 +
* sent - not editable, shows SENT when email was sent for paper verification
 +
* verified - editable clicking in the box will show curator name and YES or YES" or NOT mine, or empty.
 +
* ver_time - time stamp for verification time.
 +
 
 +
2.- From Paper_editor under 'Person Author Curation'
 +
enter two_number (tab)- to curate Person-paper connection by Person.
 +
* two number
 +
* institution
 +
* name
 +
* aka
 +
* show (collapsible)street, city, state, post, country, mainphone, officephone, labphone, otherphone, fax, email, old_email, pis, lab, webpage.
 +
To collapse click anywhere inside.
 +
* Color index : Verified YES Cecilia (blue), Verified YES Raymond (purple), Verified YES (green), Verified NO (red), Verified YES to Other possible (not shown)(brown), Connected not verified (pink), Connected to Other not verfied (olive green) Exact Match not connected to anyone (aquamarine), Last name Match (black).
 +
* matches - if many connections will show in batches of 10 (Paper Author Person Group)
 +
** first showing matches not verified
 +
** shows at the bottom list of exact matches to all names and akas and time to get this search results.
 +
 
 +
===Tables for Person Lineage submission form===
 +
* joinkey
 +
* two_sentname - single value, not editable.
 +
* two_othername - single value, not editable.
 +
* two_number - single value, editable. WBPerson# to connect to
 +
* two_role - multiple values, editable.
 +
* two_date1 - single value, editable. Beginning in the year.
 +
* two_date2 - single value, editable. Through the year.
 +
* two_sender -single value (email of sender or REV- email for reverse connection)
 +
Table is a 2 way connection table, connects to sender and recipient who is the other person role connecting identified by REV-.
 +
 
 +
Note: to see all lineage submitions
 +
* http://tazendra.caltech.edu/~azurebrd/cgi-bin/data/person_lineage.ace
 +
 
 +
== Person Model==
 +
Revised and approved for sign off on the 16th June. 2011
 +
 
 +
?Person PostgreSQL_id UNIQUE Text      // for linking to internal RDB
 +
                                        // Text instead of Int so it says WBperson####
 +
        Name    First_name    UNIQUE  Text    // prefered first name
 +
                Middle_name  Text            // middle name(s)
 +
                Last_name    UNIQUE  ?Person_name XREF Last_name_of    // prefered last name
 +
                Standard_name UNIQUE  ?Person_name XREF Standard_name_of  // name to be used for displays
 +
                                              // default is "First_name Last_name"
 +
                Full_name    UNIQUE  ?Person_name XREF Full_name_of // field for storing long name [030321 krb]
 +
                Also_known_as  ?Person_name XREF Other_name_of // new XREF added [030310 krb]
 +
        CGC_representative_for ?Laboratory XREF Representative
 +
        Laboratory ?Laboratory XREF Registered_lab_members
 +
        Affiliation Text
 +
        Conducted ?Analysis XREF Conducted_by
 +
        Address #Address
 +
        Tracking        Old_laboratory ?Laboratory XREF Past_lab_members      // Lab(s) the person previously worked in
 +
                        Last_verified UNIQUE DateType                        // Last time the name/contact information in this record was actively verified
 +
                        Last_attempt_to_contact UNIQUE DateType UNIQUE Text  // Text is method of attempt to contact
 +
                        Old_address DateType #Address  // for history
 +
                        Left_the_field Text
 +
                        History        Merged_into      UNIQUE ?Person XREF Acquires_merge
 +
                                      Acquires_merge    ?Person XREF Merged_into
 +
                        Status  Valid
 +
                                Invalid
 +
        Comment Text
 +
        Lineage Supervised    ?Person XREF Supervised_by #Role
 +
                Supervised_by ?Person XREF Supervised #Role
 +
                Worked_with  ?Person XREF Worked_with #Role
 +
        Publication    Paper ?Paper XREF Person #Evidence
 +
                        Not_paper ?Paper XREF Not_person #Evidence
 +
                        Publishes_as ?Author    // confirmed author names (curated) can't XREF because of paper model
 +
                        Possibly_publishes_as ?Author XREF Possible_person
 +
                                                // curated for Author->Paper->Person
 +
                                                // connection but not yet verified by
 +
                                                // the Person
 +
 
 +
#Address  Street_address Text        // multiple lines including town, state, zip/postcode, whatever
 +
          Country UNIQUE Text
 +
          Institution UNIQUE Text
 +
          Email Text                // multiple emails allowed
 +
          Main_phone    Text        // phone number for the institution, I presume
 +
          Lab_phone    Text
 +
          Office_phone  Text
 +
          Other_phone  Text UNIQUE Text  // second text can be used to indicate type
 +
          Fax          Text
 +
          Web_page      Text    // web page is part of the address info I think
 +
 
 +
== Notes ==
 +
* update to Paper editor (2011-05-18)Updated to add the author_person priority flag, creating the table  pap_curation_done  and moving the genestudied_done to
 +
pap_curation_done as 'genestudied'.
 +
in the Enter New Papers section :
 +
- the author-person select at the top with the box for free pmids has an author-person dropdown with values priority and not_priority which populate  pap_curation_flags with 'author_person'
 +
- each pmid has an aut-per_priority dropdown that works the same way
 +
 
 +
I've updated the pap_match.pm to allow the extra flag
 +
 
 +
I'll now work on populating all the papers except for those with functional_annotation as 'author_person' priority in pap_curation_flags.  Cecilia, this means that in the future Kimberly will always flag this for you and not set that for those that are
 +
functional annotation, so you don't need to think about functional annotation anymore, just whether it's flagged as priority or not.
 +
 
 +
* sandbox, mangolasi is only for testing. Tazendra is Live. all Paper search in mangolassi are showing as:
 +
pap_curation_done author_person
 +
in red bold letters
 +
Just for me to see if it is the way i'd like it to show.
 +
Yes, i like it, impossible to miss it.
 +
 
 +
* 2011-06-03 Cecilia, I've moved the script to update lineage names to WBPerson#### to the postgres account to :
 +
/home/postgres/work/pgpopulation/two_people/two_lineage/update_twos_in_two_lineage.pl
 +
You should add that to the list of scripts to update when we change the person tables.
 +
2011-06-07 missed a line, re -ran  output at
 +
/home/postgres/work/pgpopulation/two_people/two_lineage/
 +
 
 +
==Notes Person editor==
 +
Going live June 16, 2011 after 2pm.
 +
* Connects to Postgres database named testdb.
 +
* normal tables @normal_tables : firstname middlename lastname standardname aka_firstname aka_middlename aka_lastname email old_email old_email_date street city state post country institution old_institution old_inst_date mainphone labphone officephone otherphone fax pis lab oldlab left_field unable_to_contact privacy webpage usefulwebpage wormbase_comment hide status mergedinto acqmerge comment.
 +
** Organization Tables Person (normal tables)
 +
*** Table properties : type_input, order_type, min_rows
 +
**** For type_input: (input -tries to autocomplete- and checkbox) all above tables and word 'number' have a type_input value = 'input', but usefulwebpage = 'checkbox'.
 +
**** order_type: single value and multi value.
 +
***** order_type: value = 'single' for tables "firstname middlename lastname standardname city state post country left_field unable_to_contact hide status mergedinto".
 +
***** order_type: value = 'multi' for tables "street institution old_institution old_inst_date mainphone labphone officephone otherphone fax email old_email old_email_date pis lab oldlab privacy aka_firstname aka_middlename aka_lastname webpage usefulwebpage wormbase_comment acqmerge comment'.
 +
**** min_rows: (minimum amount of rows), street = 4. All others = 1.
 +
 
 +
* This form will show different pages depending what your click, pick, type, autocomplete loading from or to.
 +
** The first 3 pages I don't see. They will work in the background from javascripts actions.
 +
** &autocompleteXHR for tables that allow auto complete will query from Postgres.
 +
** &updatePostgresTableField: updates to Postgres updating from Person editor section after clicking away from input field/tab, or changing value in dropdown or checkbox.
 +
** &updatePostgresLineageData: updates to Postgres updating from lineage section after clicking away from input field/tab, or changing value in dropdown or checkbox.
 +
** These 6 pages are loaded visible in the browser, depending which one i pick (search paper, checkout papers, crate new person, search in display or search in edit mode. Front page (what you see when loading person editor), create people from xml. There are 6 pages, with this parameter (which_page) which the javascript file uses to load different actions (like autocomplete in one page, or adding extra institutions, or editing upon blurring a field). Focus - enter into cell, blur - getting out of the cell. When burring in the Person-editor edit mode it will update. RECAP: which_page tells javascript which actions to load for a specific page.
 +
** $frontpage = &firstPage by default.
 +
** &search(): Searches Person , display or edit mode.
 +
** &createNewPerson(): Creates new person
 +
** &searchPaper(): Searches for individual paper.
 +
** &checkoutPapers(): List of all papers not yet person curated. Will only display the highest 50 paper IDs
 +
** &personStatistics: List of Valid and Invalid Persons by month and year to keep track of how many new people enter the field.
 +
** &createPeopleFromXml(): DO NOT RELOAD. Extracts authors and institution data from PubMed xml, allowing to create multiple people with same institutions.
 +
 
 +
=== &autocompleteXHR ===
 +
 
 +
* Less than 5 characters (including space) will give max 20 results, 5 or more = max 500 results.
 +
** Fields:
 +
*** General: query for words that match in the corresponding table's corresponding column.  The table and column are 'two_<field>'.
 +
*** number: query table two_standardname, column 'joinkey'.
 +
** Postgres Query:
 +
*** case insensitive: It will change the words I type into lower case by lowercasing query words and using LOWER column values in postgres query. First it will query for columns beginning with characters searched (cecil), if not enough results will query for characters anywhere and not at the beginning (ecili).  After query is complete, if there are too many results, change the last result to say "more ..."
 +
**** SELECT DISTINCT($column) FROM $table WHERE LOWER($column) ~ '^$words' ORDER BY $column;
 +
**** for example ^cecil for searching for word beginning with: SELECT DISTINCT(two_firstname) FROM two_firstname WHERE LOWER(two_firstname) ~ '^cecil' ORDER BY two_firstname;
 +
**** SELECT DISTINCT($column) FROM $table WHERE LOWER($column) ~ '$words' AND LOWER($column) !~ '^$words' ORDER BY $column;
 +
**** ~ '$words' =  anywhere, !~ '^$words' = not at the beginning.
 +
**** example: SELECT DISTINCT(two_firstname) FROM two_firstname WHERE LOWER(two_firstname) ~ 'ecili' AND LOWER(two_firstname) !~ '^ecili' ORDER BY two_firstname;
 +
 
 +
=== &updatePostgresTableField ===
 +
* update tables for Person information section, edit mode. Deletes from data and history tables and inserts into data and history tables. Now I can delete data in Person-editor,no need to delete in psql. Only delete two in psql when two is empty, hit button to create a new two and no data entered there.
 +
** parameters: $field, $joinkey, $order, $newValue, $curator_two
 +
*** $field = firstname, etc.
 +
*** $joinkey = two#.
 +
*** $order = two_order = 1, two_order = 2(value). Everything has two_order (two_order = '$order';) if not IS NULL.
 +
*** $newValue = New data entered.
 +
*** $curator_two = curator two1, two1823, etc
 +
** Postgres commands:
 +
*** DELETE FROM two_$field WHERE joinkey = '$joinkey' AND two_order = '$order';
 +
*** Example for text table: DELETE FROM two_email WHERE joinkey = 'two1' AND two_order = '1';
 +
*** DELETE FROM h_two_$field WHERE joinkey = '$joinkey' AND two_order = '$order' AND two_timestamp > now() - interval '10 minutes';
 +
*** Example for history table: DELETE FROM h_two_email WHERE joinkey = 'two1' AND two_order = '1' AND two_timestamp > now() - interval '10 minutes'; It will keep history only of data entered before 10 minutes ago, for cases of multiple editing same thing to not store unuseful history.
 +
*** INSERT  VALUES between single quotes '<newValue>', but if NO value them insert NULL (no quotes) will show blank, in entered 'NULL' will show NULL.
 +
*** NO blank values editing in Person-editor, it will not add data to data tables and will put NULL in history tables.
 +
*** Every time editing two tables, history tables are written.
 +
*** INSERT INTO h_two_$field VALUES ('$joinkey', $order, $newValue, '$curator_two')";
 +
*** If value is NOT NULL = INSERT INTO two_$field VALUES ('$joinkey', $order, $newValue, '$curator_two');
 +
 
 +
=== &updatePostgresLineageData ===
 +
** Columnas:sentname,othername, othertwo, role, daate1, date2, sender, timestamp.
 +
*** Each column has a value for each row, except timestamp.
 +
*** Value is the data that shows in the form, except for role, example. "I Trained as a PhD under" is withPhd. "I trained this person as a Postdoc" = Postdoc
 +
*** Value can be blank if no data. It is dropdown in role, date1, date2.
 +
** When updating lineage, gets values of the columns for the row and DELETE data in the row in Postgres then INSERTS new data per cell change (click away from cell after typing/click a new value in the dropdown). Each time a cell changes enters a new row. Deletes the whole row and inserts a new row with old data not changed and new value in the column that changed.
 +
**Reverse role, Reverse sender.
 +
***Reverse role:
 +
****Only 'Collaborated' = Rev 'Collaborated'
 +
****Role 'withSomething' = Rev 'Something'
 +
****Role 'Something' = Rev 'withSomething'
 +
***Reverse sender:
 +
****'REV - Something' = 'Something'
 +
****'Something' = 'REV - Something'
 +
**Blank values are NULL in Postgress.
 +
** Deleting from postgres. Associates by joinkey and two_number, in this way it can be edited by any sender or sendee. Deletes  both ways joinkey - othertwo, sentname - othername, othername - sentname, othertwo - joinkey, role - reverse_role, date1 - date1, date2 - date2, sender - reverse_sender.
 +
*** The qualifiers are " = '<value>'" if there is a value, or " IS NULL" if there isn't.
 +
**** "DELETE FROM two_lineage WHERE joinkey = $joinkey AND two_sentname $sentname_qual AND two_othername $othername_qual AND two_number $othertwo_qual AND two_date1 $date1_qual AND two_date2 $date2_qual AND two_role $role_qual AND two_sender $sender_qual";
 +
*** "DELETE FROM two_lineage WHERE joinkey $othertwo_qual AND two_sentname $othername_qual AND two_othername $sentname_qual AND two_number = $joinkey AND two_date1 $date1_qual AND two_date2 $date2_qual AND two_role $reverse_role_qual AND two_sender $reverse_sender_qual";
 +
**INSERT: after deleting row inserts new values. (NULL where blank, same if no changes, and newValue for the chosen column that has a new value.)
 +
*** "INSERT INTO two_lineage VALUES ( $joinkey, $sentname, $othername, $othertwo, $role, $date1, $date2, $sender )";
 +
*** "INSERT INTO two_lineage VALUES ( $othertwo, $othername, $sentname, $joinkey, $reverse_role, $date1, $date2, $reverse_sender )";
 +
 
 +
=== &createNewPerson===
 +
* Creates two# in tables 'two' and 'two_status' and re-directs to Person editor mode.
 +
**1 gets highest joinkey
 +
*** 'SELECT two FROM two ORDER BY two::INTEGER DESC'; gets the highest number integer (sorter by numbers) in descending order.
 +
**2 add 1 to the highest number and add a 'two' in front of the new number to create the new joinkey (two#).
 +
** &addToPgcommandsTwoCreation($two_number, $curator_two, @pgcommands); (also use for create new people en xml section)
 +
** INSERT INTO two VALUES ('$joinkey', '$two_number'). Add new value to two table into postgres instead of doing it mannualy as before to create new two#.
 +
*** &addToPgAndHst('status', $joinkey, '1', 'Valid', $curator_two, @pgcommands); (Pg= Postgres, Hst= History)
 +
**** First value 'Field' = status, firstname, city, hyide, leftfield...etc).
 +
**** second value 'joinkey' = two# for person
 +
**** third value 'two_order' = 1, 2, 3...etc
 +
**** fourth value 'value' = any data entered
 +
**** fifth value 'curator_two' = two1 or Lito
 +
****  if ($data) { if ($data =~ m/\'/) { $data =~ s/\'/''/g; } $data = "E'$data'"; } else { $data = 'NULL'; }. If no data gets NULL, otherwise put single quotes around it.
 +
**** insert into datatables: "INSERT INTO two_$table VALUES ('$joinkey', $order, $data, '$curator_two')";
 +
**** insert into historytables: "INSERT INTO h_two_$table VALUES ('$joinkey', $order, $data, '$curator_two')";
 +
**3 'which_page' value is hidden.
 +
*** print "<input type=\"hidden\" name=\"which_page\" id=\"which_page\" value=\"createNewPerson\">";
 +
*** print "<input type=\"hidden\" name=\"redirect_to\" id=\"redirect_to\" value=\"$url\">"; tells javascript which page to re-direct to this url, so if hitting reload it does not keep crating new person without data besides two# and status.
 +
*** my $url = "person_editor.cgi?curator_two=$curator_two&action=Search&display_or_edit=edit&input_number_1=$joinkey";
 +
 
 +
=== &search ===
 +
* When searching under a number will give a two# result, otherwise it will search for all fields with values, that will give results with two#s and links to Person-editor display mode.
 +
** when pressing search botton, first searches if there is a value in the 'field number'.
 +
** If you search by two# it will go to Person editor display mode page, if you search by any other way, it will go to search results page with links to Person editor display mode page. If the value has a number it will show it by doing  '&displayPerson("two$1", $curator_two);', otherwise it will show 'not number' and will show possible matches to Persons.
 +
** which_page is searchResults.
 +
** if no number found, it will search input_<each normal table>_1(order only has value 1 in the first page). Will skip where field has no data.
 +
** Parameters in form to put in Variable in the cgi code: substring, case insensitive.
 +
*** no parameter query = operator
 +
*** substring parameter query ~ operator
 +
*** case insensitive parameter query ~* operator
 +
*** SELECT joinkey, two_$table FROM two_$table WHERE two_$table $operator '$data';
 +
*** 1. Result stores by matches. two# and table. Example searching substring Bar (firstname)will store joinkey all two#s and all First names staring with Bar. Will search for joinkey (two#) and all the other tables queried. Will group by joinkey and sub-group of number of tables matched. Will query all the standard names of two that matched query.
 +
*** 2. Result stores by tables. table, two#, data.
 +
*** Results shown by the number of tables matched in descending order.  For each two#:
 +
**** Link to person-editor editor mode.
 +
**** <a href=\"person_editor.cgi?curator_two=$curator_two&action=Search&display_or_edit=display&input_number_1=$joinkey\">$joinkey</a>
 +
**** color brown for Standard name
 +
**** will show all tables that match the two#, and the corresponding data in color green.
 +
 
 +
=== &displayPerson===
 +
Search for any two data (last name, institution, email...etc), can do multi searches. Display data or display the editor.
 +
* two modes, Display and Editor.
 +
* make_something_input and make_something box, and make_something dropdown will make cells, cells will make rows, rows will make the table, which is what the page shows.
 +
* for all normal_tables
 +
** cgi form tells Javascript what tables/fields to look at by passing a hidden input element with class='fields'.To enter updates (including new data) and autocomplete.
 +
** SELECT * FROM two_$table WHERE joinkey = '$joinkey' ORDER BY two_order
 +
** Look for data in two tables for the joinkey (two#).
 +
** Results will be stored in %pgdata (hash)by table and two_order these 4 different data : highest_order (what's why it's sorted by two_order, so it will store all the two_orders per table, example two_street with 4 lines will store all 4 instead of 1), data, row_curator, timestamp.
 +
 
 +
* which_page for Person display is displayPersonDisplay
 +
* which_page for Person editor is displayPersonEditor
 +
 
 +
* $toggle_url = "person_editor.cgi?curator_two=$curator_two&action=Search&display_or_edit=$opp_display_or_edit&input_number_1=$joinkey" (link will switch modes display - edit). Same thing repited below to have link to switch modes also above lineage part.
 +
 
 +
* These 2 subroutines (&makeInputField and &makeCheckboxField) make html elements that have input Id = "input_${table}_$order" for the javascript to know which html elements to assign postgres updating functions. All inputs fields with an id that follows this pattern updates if which_page is displayPersonEditor, it can be text, checkbox. Important to remember if something is not updating correctly.
 +
** ''&makeInputField'', will make a td cell with an input field with autocomplete (value in the input field is the data).
 +
** ''&makeCheckboxField'' makes an input checkbox.
 +
* This subroutine ''&makeDisplayField'' makes a cell with data and in the case of 'webpage' will create a link from the data.
 +
 
 +
* for each normal_tables
 +
** tables skipped because it will already have been displayed as something else (($table eq 'middlename') || ($table eq 'lastname') || ($table eq 'aka_middlename') || ($table eq 'aka_lastname') || ($table eq 'old_inst_date') || ($table eq 'old_email_date') || ($table eq 'usefulwebpage') );
 +
** $highest_order. (two_order exist in Postgres, creating new order will be highest_order+1)
 +
*** starts as 0
 +
*** when data row is displayed (highest_order becomes two_order if two_order > highest_order)
 +
*** when on edit mode (extra empty rows will show, if you create new order, example line 4 of street it will be highest order (3) +1 = order 4, when in Postgress it will became two_order = 4)
 +
** for each two_order, sorted numerically. 3 special cases (firstname and aka_firstname; old_email and old_institution have old_date; webpage has usefulwebpage) and 1 normal case (all others)
 +
*** normal case :
 +
**** $entry_data .= &makeSingleNormal(\%makeSingleNormal, $display_or_edit, $joinkey, $order, $table);
 +
**** UPDATE August 30, 2011. change section on  makeSingleNormal  under
 +
if ($display_or_edit eq 'edit') {
 +
    ($td_one_data) = &makeInputField($one_data, $one_table, $joinkey, $order, '3', '1', '', $td_width, $input_size);
 +
    if ( ($bgcolor eq $blue) && ( ($one_table eq 'email') || ($one_table eq 'institution') ) ) { ($one_table) = &labelAddMoveButton($one_table, $order); } }  # if the table already existed and has a corresponding old table, add a button to move the data
 +
 
 +
So that if the $bgcolor is $blue and the table is either email or
 +
institution, add the ``move'' button by calling
 +
&labelAddMoveButton($one_table, $order)
 +
 
 +
All this does is :
 +
sub labelAddMoveButton {
 +
my ($table, $order) = @_;
 +
$table .= " <button onclick=\"moveDataToOldTable('$table', '$order')\">move</button>";
 +
return $table;
 +
} # sub labelAddMoveButton
 +
 
 +
Note that this changes  makeSingleNormal  instead of making a whole
 +
new type for singleNormal that has buttons.
 +
 
 +
**** &makeSingleNormal (hace single normal row). Size 80. Background color white by default (empty), and blue color when there is existing data. It will show timestamp minus the milliseconds.
 +
***** in edit mode: &makeInputField($one_data, $one_table, $joinkey, $order, '3', '1', '', $td_width, $input_size);
 +
***** in display mode: &makeDisplayField($one_data, $one_table, $joinkey, $order, '3', '1', '', $td_width, $input_size);
 +
**** $display_or_edit (display or edit mode)
 +
**** $joinkey (two#, example two625)
 +
**** $order (order1, 2...etc)
 +
**** $table (example street)
 +
*** 3 special cases:
 +
**** &makeTripletHorizontal: Makes a row of data for 3 tables: first name => middlename, lastname and lable name (first, middle and last name). Size is 20. Background white color when empty and blue when there is data in the first table (firstname or aka_firstname)even if there is no data in middle name.It will show timestamp minus the milliseconds. In &makeDisplayField will display the 3 normal tables and in &makeInputField will edit the 3 normal tables. It will add timestamps to each of the 3 normal tables in each cell.
 +
*****table eq 'firstname') {$entry_data .= &makeTripletHorizontal(\%pgdata, $display_or_edit, $joinkey, $order, $table, 'middlename', 'lastname', 'name');
 +
***** elsif ($table eq 'aka_firstname') {$entry_data .= &makeTripletHorizontal(\%pgdata, $display_or_edit, $joinkey, $order, $table, 'aka_middlename', 'aka_lastname', 'aka_name');
 +
**** &makeDoubleVertical, double data vertical for old email, old instituion, and webpage. Old-email and old _emaildate are 2 rows with 1 single order. Input size is 80. Background is white color when empty and blue color when the first cell has previous data. In edit mode will &makeInputField($one_data, $one_table, $joinkey, $order, '3', '1', '', $td_width, $input_size);.In display mode it will &makeDisplayField($one_data, $one_table, $joinkey, $order, '3', '1', '', $td_width, $input_size);.
 +
UPDATE August 30, 2011 change section on  makeDoubleVertical  this now always looks for the
 +
entry with a 'white' $bgcolor, meaning that it has the order for the
 +
new row, and changes the $order to be :
 +
if ($bgcolor eq 'white') { $order .= "<input type=\"hidden\" id=\"highest_${one_table}_order\" value=\"$order\">\n"; }        # have an id for the next highest order of the old tables
 +
 
 +
So that the javascript can get the order to use for moving data to the
 +
new tables.
 +
*****elsif ($table eq 'old_email') { $entry_data .= &makeDoubleVertical(\%pgdata, $display_or_edit, $joinkey, $order, $table, 'old_email_date', 'old_email');
 +
*****elsif ($table eq 'old_institution') {$entry_data .= &makeDoubleVertical(\%pgdata, $display_or_edit, $joinkey, $order, $table, 'old_inst_date', 'old_institution');
 +
**** &makeCheckboxInput: For webpages. Input size 80. Background white color when empty, and blue color when the first one (webpage) has data. usefulwebpage (to mark webpages with lab members data useful to me for curating Person). inputId = "input_${table}_$order".
 +
***** elsif ($table eq 'webpage') {$entry_data .= &makeCheckboxInput(\%pgdata, $display_or_edit, $joinkey, $order, $table, 'usefulwebpage', 'webpage'); }
 +
***** For display: ($td_one_data) = &makeDisplayField($one_data, $one_table, $joinkey, $order, '2', '1', '', '', $input_size); webpage table will take 2 slots in the row.
 +
***** For display: ($td_two_data) = &makeDisplayField($two_data, $two_table, $joinkey, $order, '1', '1', '', '', $input_size); usefulwebpage table will take 1 slot in the row.
 +
***** For edit mode:$display_or_edit eq 'edit') {($td_one_data) = &makeInputField($one_data, $one_table, $joinkey, $order, '3', '1', '', $td_width, $input_size);
 +
***** For edit mode:($two_checkbox) = &makeCheckboxField($two_data, $two_table, $joinkey, $order);
 +
** Only in edit mode show extra empty input rows if appropriate. This uses the same &make<whatever> subroutines as above in the foreach two_order section.
 +
*** order_type single needs 1 row.
 +
*** order_type multi need existing numbers of rows with data +1.
 +
*** in the case of the field 'street' there is a minimum of 4, so show 4 or #rows+1, whichever is higher.
 +
 
 +
* For each normal_tables, there are 2 hidden input fields :
 +
** value $highest_order with id highest_order_$table. For javascript to know how many rows to make autocomplete listeners and postgres update listeners for each field.
 +
** value $type_input{$table} with id type_input_$table. For javascript to know what type of data, text (autocomplete+blur) or checkbox (click).
 +
* * $toggle_url = "person_editor.cgi?curator_two=$curator_two&action=Search&display_or_edit=$opp_display_or_edit&input_number_1=$joinkey" (link will switch modes display - edit)[Same link as above @ $toggle_url, to have switch modes also at botom part of the page above lineage part. added : print "Switch to <a href=\"$toggle_url\">$opp_display_or_edit</a>.<br />\n";August 31, 2011]
 +
*  &makeLineageDisplay($joinkey, $display_or_edit, $header_bgcolor, $header_color);
 +
** parameters:
 +
*** joinkey is two#
 +
*** display or edit mode
 +
*** header background color is grey
 +
*** header font color is black
 +
** &populateLineageDropdowns: This fills the values for dropdown fields
 +
*** date columns: date1 date2 (date 1 is starting year, date 2 is ending year. They will be in order: first, empty; second, present; third, this year; fourth-through last, going back until 1900)
 +
*** role columns:
 +
****'withPhd' = 'I trained as a PhD under';
 +
****'withPostdoc' = 'I trained as a Postdoc under';
 +
****'withMasters' = 'I trained as a Masters under';
 +
****'withUndergrad' = 'I trained as an Undergrad under';
 +
****'withHighschool' = 'I trained as a High School student under';
 +
****'withSabbatical' = 'I trained for a Sabbatical under';
 +
****'withLab_visitor' = 'I trained as a Lab Visitor under';
 +
****'withResearch_staff' = 'I trained as a Research Staff under';
 +
****'withAssistant_professor' = 'I trained as an Assistant Professor under';
 +
****'withUnknown'  = 'I trained as an Unknown under';
 +
****'Phd' = 'I trained this person as a PhD';
 +
****'Postdoc' = 'I trained this person as a Postdoc';
 +
****'Masters' = 'I trained this person as a Masters';
 +
****'Undergrad' = 'I trained this person as an Undergrad';
 +
****'Highschool' = 'I trained this person as a High School student';
 +
****'Sabbatical' = 'I trained this person during a Sabbatical';
 +
****'Lab_visitor' = 'I trained this person as a Lab Visitor';
 +
****'Research_staff' = 'I trained this person as a Research Staff';
 +
****'Assistant_professor' = 'I trained this person as an Assistant Professor';
 +
****'Unknown' = 'I trained this person as an Unknown';
 +
****'Collaborated' = 'I collaborated with';
 +
** background color: existing data are blue. all new entries are white. boxes are white.
 +
** section description: Two#, Lineage.
 +
** headers of columns: sentmame, othername, othertwo, role, date1, date2, sender, timestamp.
 +
** "SELECT * FROM two_lineage WHERE joinkey = '$joinkey';"  For each postgres row result:
 +
*** timestamp: It will show timestamp minus the milliseconds
 +
*** In edit mode: &makeLineageRowEdit($lineage_count, $bgcolor, $joinkey, $sentname, $othername, $othertwo, $role, $date1, $date2, $sender, $timestamp);
 +
*** In display mode: print "<tr bgcolor='$bgcolor'><td>$sentname</td><td>$othername</td><td>$othertwo</td><td>$role</td><td>$date1</td><td>$date2</td><td>$sender</td><td>$timestamp</td></tr>\n";
 +
** Edit mode: &makeLineageRowEdit($lineage_count, $bgcolor, $joinkey, '', '', '', '', '', '', '', ''); 5 times to make 5 extra lineage fields.
 +
** hidden inputs field for javascript to know what type of listeners to make, and how many, and for which:
 +
*** print "<input type=\"hidden\" id=\"highest_order_lineage\" value=\"$lineage_count\">\n"; (highest_order_lineage/so javascript knows how many rows of lineage)
 +
*** For the next the lineage_fields class is so javascript knows names of the columns
 +
*** print "<input type=\"hidden\" class=\"lineage_fields\" value=\"sentname\">\n";
 +
*** print "<input type=\"hidden\" class=\"lineage_fields\" value=\"othername\">\n";
 +
*** print "<input type=\"hidden\" class=\"lineage_fields\" value=\"othertwo\">\n";
 +
*** print "<input type=\"hidden\" class=\"lineage_fields\" value=\"role\">\n";
 +
*** print "<input type=\"hidden\" class=\"lineage_fields\" value=\"date1\">\n";
 +
*** print "<input type=\"hidden\" class=\"lineage_fields\" value=\"date2\">\n";
 +
*** print "<input type=\"hidden\" class=\"lineage_fields\" value=\"sender\">\n";
 +
*** For the next type_input_<something> fields are so javascript knows type of the column, values can be input (text box) or dropdown.
 +
*** print "<input type=\"hidden\" id=\"type_input_sentname\"  value=\"input\">\n";
 +
*** print "<input type=\"hidden\" id=\"type_input_othername\" value=\"input\">\n";
 +
*** print "<input type=\"hidden\" id=\"type_input_othertwo\"  value=\"input\">\n";
 +
*** print "<input type=\"hidden\" id=\"type_input_role\"      value=\"dropdown\">\n";
 +
*** print "<input type=\"hidden\" id=\"type_input_date1\"    value=\"dropdown\">\n";
 +
*** print "<input type=\"hidden\" id=\"type_input_date2\"    value=\"dropdown\">\n";
 +
*** print "<input type=\"hidden\" id=\"type_input_sender\"    value=\"input\">\n";
 +
** makeLineageRowEdit: makes a row with different cells to edit lineage information:
 +
*** $lineage_count, $bgcolor, $joinkey, $sentname, $othername, $othertwo, $role, $date1, $date2, $sender, $timestamp)
 +
*** &makeLineageInput($lineage_count, $joinkey, $sentname, 'sentname');
 +
*** &makeLineageInput($lineage_count, $joinkey, $othername, 'othername');
 +
*** &makeLineageInput($lineage_count, $joinkey, $othertwo, 'othertwo');
 +
*** &makeLineageDropdown($lineage_count, $joinkey, $role, 'role');
 +
*** &makeLineageDropdown($lineage_count, $joinkey, $date1, 'date1');
 +
*** &makeLineageDropdown($lineage_count, $joinkey, $date2, 'date2');
 +
*** &makeLineageInput($lineage_count, $joinkey, $sender, 'sender');
 +
** These 2 subroutines (&makeLineageInput and &makeLineageDropdown) make html elements that have input Id = "cur_${column}_${lineage_count}\" for the javascript to know which html elements to assign postgres updating functions. All inputs fields with an id that follows this pattern updates if which_page is displayPersonEditor, it can be text, dropdown. Important to remember if something is not updating correctly. There is also a hidden html input element with the old value to keep the values of columns not updated with id = old_${column}_${lineage_count}
 +
*** &makeLineageInput, will make a td cell with an input field for lineage (value in the input field is the data).
 +
*** &makeLineageDropdown, will make a td cell with an input dropdown field for lineage.
 +
 
 +
** &getAkaHash. Script run weekly and Person-editor are the same now. "If Person-editor is not connecting the sinle match, tell Juancarlos to fix it, it should work now July8, 2011"
 +
**F M L and under regular name
 +
** delete empty spaces beginning and end of the name
 +
** keep empty space between names
 +
** delete commas and periods
 +
** change all letters to lower cases
 +
** for middle name adds first initial
 +
** delete two_status Invalid
 +
* Por each Person each Last name  Each name (including initial)
 +
** L F
 +
** F L
 +
** If middle name exist: algorithm
 +
** L F M
 +
** L FM
 +
** F M L
 +
** FM L
 +
 
 +
===&checkoutPapers===
 +
There are 2 tables: pap_curation_flags and pap_curation_done. If the following problem occurs address to Juancarlos.
 +
If a paper is not showing when they should in the check out papers list and it is flagged in Paper-editor under table pap_curation_flags for author_person curation and it is not marked as author_person_done. If a paper is marked in pap_curation_done it shouldn't show in the list. If a paper is marked in those two fields or in neither shouldn't show in list.
 +
* which_page for checkoutPapers is checkoutPapers
 +
* $max_papers_to_show = 50;
 +
* SELECT joinkey FROM pap_curation_flags WHERE pap_curation_flags = 'author_person' AND joinkey NOT IN (SELECT joinkey FROM pap_curation_done WHERE pap_curation_done = 'author_person') ORDER BY joinkey::INTEGER DESC
 +
** Selects papers from check out papers section, by joinkey (paper id); from the pap_curation_flags where papers have been flagged for author_person (flags) and joinkey (paper#) is not already flagged as pap_curation_done done for author_person curation. Sorted by paper id in descending order.
 +
* for each row returned from the query:
 +
** keep track of number of results (number of papers)
 +
** show links until I have more than $max_papers_to_show from the query in the form.
 +
** link to Person_editor ( person_editor.cgi?curator_two=$curator_two&paper_id=$row[0]&action=Search+Paper )
 +
** print "There are " . scalar($count) . " papers, will only display the highest $max_papers_to_show paper IDs :<br/> (Shows the count of all papers not marked as person curation done that are also flagged for author_person_curation and shows a list of links for 50 papers.
 +
===&searchPaper===
 +
* Will look for the first block of numbers that you write in WBPaperID box. If you type no numbers results will say NOT a number.
 +
* &displayPaper(&padZeros($1), $curator_two); If you input numbers will display paper#.
 +
** 0002342 -> 2342 -> 00002342 Deletes the zeros in front of numbers,and replaces zeros in front to have 8 digit numbers. You can write the paper number without the zeros in front.
 +
* &displayPaper
 +
** which page is displayPaperXml.
 +
** 2 queries.
 +
*** SELECT pap_curation_flags FROM pap_curation_flags WHERE joinkey = '$joinkey' ); (joinkey is WBPaper#).print font style bold, color red, size 14pt\>curation_flags : $curation_flags.
 +
*** SELECT pap_curation_done FROM pap_curation_done WHERE joinkey = '$joinkey'); print bold style=\'color: red;font-size: 14pt\'>curation_done : $curation_done (font style bold, color red, size 14)
 +
** Results shows WBPaper#, next to it link to the paper editor
 +
*** my $paper_editor_url = "http://tazendra.caltech.edu/~postgres/cgi-bin/paper_editor.cgi?curator_id=$curator_two&action=Search&data_number=$joinkey"
 +
** identifiers: SELECT * FROM pap_identifier WHERE joinkey = '$joinkey'" ); (joinkey is WBpaper#)
 +
*** will show all identifiers in each own line. If there is a pmid identifier, will show pmid#######, it will get rid of the word pmid and show only the number in the link to pmid webpage. http://www.ncbi.nlm.nih.gov/pubmed/20559560
 +
*** WormBase pdf link "SELECT pap_electronic_path FROM pap_electronic_path WHERE joinkey = '$joinkey' AND pap_electronic_path IS NOT NULL" ); adds link http://tazendra.caltech.edu/~acedb/daniel/ to the pdf link.
 +
** "SELECT pap_author FROM pap_author WHERE joinkey = '$joinkey' ORDER BY pap_order" ); (Will look for authors id in order for that paper)
 +
*** Results will go to @aids and $aids.
 +
**** for list @aids = (1 2 3 4)
 +
**** for queries $aids = "'1', '2', '3', '4'" (author 1, author, author 3...etc. = aid 116743, 116744...etc)
 +
**** tables = index possible sent verified. Index is the author name.
 +
***** foreach my $table (@aut_tables) "SELECT * FROM pap_author_$table WHERE author_id IN ('$aids')".
 +
***** for each table (index possible sent verified )will make a querie
 +
***** "SELECT * FROM pap_author_index WHERE author_id IN ('116743','116744')" ); (allows multiple author number search at the same time)
 +
***** SELECT * FROM pap_author_possible WHERE author_id IN ('116743','116744');
 +
******  author_id | pap_author_possible | pap_join | pap_curator | pap_timestamp First column is zero, next one 1...etc.(pap_join is author join#, for when adding another possible to same author, because the other possible is not the author.
 +
*** $pg_aid {$row[0]}{$table}{$row[2]} = $row[1];
 +
*** Results will be stored in %pg_aid (hash) by (row[0] is author id), ($table is by 4 tables index possible sent verified)  ( ({$row[2]} is pap_join {author join order 1, new possible, author join order2) = ($row[1] is the data pap_author_possible or pap_author_index, or sent or verified);
 +
*** $ = How to get a value from % hush using the keys. hush used to store multivalues.
 +
*** $pg_aid {$aid}{$table}{$join} = ceci ($pg_aid {$key}{$key}{$key} = value -resultado)= $pg_aid {15008}{verified}{2} = YES
 +
** To read pmid. Delete '/' only needed for Perl, not used in xml.
 +
*** If there is not pmid: will print No PMID found for WBPaper$joinkey<br />\n";
 +
*** If there is pmid: will look the xml file. $xmlfile = '/home/postgres/work/pgpopulation/wpa_papers/pmid_downloads/done/' . $pmid; and will print "pmid $pmid xml found<br />\n";
 +
*** If there is no xml file: will print "NO XML for $pmid\n";
 +
**** Affiliation data: <Affiliation> HERE data </Affiliation>. If this xml tag doesn't show in the xml, it will not show it in the xml form under affiliation. If the affiliations are showing else where, look at the xml file at tazendra, cd /home/postgres/work/pgpopulation/wpa_papers/pmid_downloads/done/  ,list (l), vim number of paper tazendra-..oads/done-6: vim 19725999
 +
*** Results of looking at the xml will be stored in %xml_authors. %xml_authors = &getXmlAuthors($xmldata);
 +
** &getXmlAuthors: <AuthorWHATEVER_maybe_nothing>DATA</Author> case insensitive could be in multiple lines. Gets authors data from xml, maybe weird data as <Author ValidYN="Y">.
 +
*** Captures all authors and gets Last name, ForeName, Initial. For each xml author will get:
 +
**** my ($lastname) = $author_xml =~ /\<LastName\>(.+?)\<\/LastName\>/i; (i means case insensitive)
 +
**** my ($initials) = $author_xml =~ /\<Initials\>(.+?)\<\/Initials\>/i;
 +
**** my ($forename) = $author_xml =~ /\<ForeName\>(.+?)\<\/ForeName\>/i; (Forename is first name)
 +
*** my $author = $lastname . " " . $initials; according to Kimberly an author is last name + empty + initial. In pap_author_index.
 +
*** Results will be stored in %xml_authors bu author and last name, fist name, initial (first initial),standard name (forename and last name) 
 +
**** $xml_authors{$author}{lastname} = $lastname;
 +
**** $xml_authors{$author}{firstname} = $forename;
 +
**** $xml_authors{$author}{initials} = $initials;
 +
**** $xml_authors{$author}{standardname} = "$forename $lastname"; }
 +
** hidden values: curator_two and paper_joinkey, max_instution. To print data for the form to see it.
 +
** $max_institutions = 100; hidden value.(tell Juancarlos later if you want to increase the number of maximun institutions, Feb 3, 2012 increased from 40 to 100).
 +
** @affil_tables = ( institution street city state post country );
 +
*** for my $i (1 .. $max_institutions) {for each institution from 1 to 100 maximun}
 +
*** &showInstitutionEditor($i, $curator_two, \@affil_tables, $affiliation, $max_institutions);
 +
**** $i = Shows to Institution# that you are working on. 1 to 100 max.
 +
**** $curator_two = curator doing curation, two1
 +
**** \@affil_tables = affiliation tables ( institution street city state post country )
 +
**** $affiliation (The affiliation data from xml, they show only 1, I have to get other affiliations from pmid)
 +
** InstituionEditor.
 +
*** If instituion# ($i) is less than maximun instituion it will show a link to show another institution #. if ($i < $max_institutions) {# link to show another institution if it's not the last one (last cannot show anything else)
 +
**** my $html_table_id = $i + 1; $html_table_id = 'table_' . $html_table_id;
 +
****** my $html_table_id = $i + 1 (is the  isntsitution # plus 1)
 +
****** $html_table_id = 'table_' . $html_table_id; (table_1, table_2...etc are the names of the tables, means whole data under Instituion 1, same for institution2...etc)
 +
*** $show_another_institution = "<a href=\"#\" onclick=\"document.getElementById('$html_table_id').style.display = ''; return false\">show another institution</a>"; }(When you click will show the instituion table # +1)
 +
*** If institution# is higher than 1, shows a link to hide this institution. if ($i > 1) {# link to hide this institution if it's not the first one (first must always be there, it will never hide Institution #1)
 +
**** my $html_table_id = 'table_' . $i;
 +
**** $hide_this_institution = "<a href=\"#\" onclick=\"document.getElementById('$html_table_id').style.display = 'none'; return false\">hide this institution</a>"; } (When you click 'hide this institution' will display NONE, it will hide it)
 +
**** By default only shows 1 institution. It will never hide Institution #1
 +
*** will print Inst#, curator#, link to show another institution or hide this institution.
 +
**** $show institutions with data only. Max 100. It will show institutions only if they have something in the Institution field (not street, etc.). It will also hide institutions that don't have data. It will never hide Institution #1.
 +
*** For Institution table, Inst#1: Only Inst#1 will have pre-populated with affiliation data.
 +
*** For each affiliation table ( institution street city state post country )will:
 +
**** If it is not street $order = $1 (means that in all others but street, i (instituion#1 will have order 1 for institution, state, city, post and country, instituion#2 will have input_institution_2, state2, city2...etc )
 +
**** For street order, the order will be 4 lines per inst# even if they are blanks. For Inst #2 will be order 5 to 8 and so on.
 +
**** my $table_row_autocomplete = &showEditorText($table, $order, $input_size, $colspan, $value);
 +
**** print "<tr bgcolor=\"$blue\">$table_row_autocomplete</tr>\n"; } }
 +
***** &showEditorText: will have 1 cell on the left side of the form, that is the label, name of the field, with and id lable_$table; and teh cell of the right side of the form has a lot of different html elements to make the field with autocomplete. input id=\"$inputId\" name=\"$inputId\" size=\"$input_size\" value=\"$value\">\n", this input field is important because is where you enter the data.
 +
****** td = cell
 +
****** $inputId = "input_${table}_$order"; (input_institution_1, means institution #1 or #2...etc whichever you are using)
 +
** For all affiliations_tables
 +
*** cgi forms tells Javascript what tables/fields to look at by passing a hidden input element with class='fields' To autocomplete.
 +
*** Will print "<input type=\"hidden\" class=\"fields\" value=\"$table\" \/>\n";
 +
*** print "<input type=\"hidden\" id=\"type_input_$table\" value=\"$type_input{$table}\">\n"; (value $type_input{$table} with id type_input_$table. For javascript to know what type of data, text, (autocomplete)
 +
*** print "<input type=\"hidden\" id=\"highest_order_$table\" value=\"$highest_order_table\">\n"; }(value $highest_order with id highest_order_$table. For javascript to know how many rows to make autocomplete listeners)What it means is the highest order for each field but street will be max institutions, and for street 4 x max institution.
 +
*&getAkaHash. Script run weekly and Person-editor are the same now. "If Person-editor is not connecting the sinle match, tell Juancarlos to fix it, it should work now July8, 2011"
 +
**F M L and under regular name
 +
** delete empty spaces beginning and end of the name
 +
** keep empty space between names
 +
** delete commas and periods
 +
** change all letters to lower cases
 +
** for middle name adds first initial
 +
** delete two_status Invalid
 +
** Por each Person each Last name  Each name (including initial) is stored in the %aka_hash like $aka_hash{possible name combination}{two#}
 +
*** L F
 +
*** F L
 +
*** If middle name exist: algorithm
 +
*** L F M
 +
*** L FM
 +
*** F M L
 +
*** FM L
 +
** how aka gets stored:
 +
***
 +
* table (Person/author editor part, create new, shows two existing data) has columns (aid, author, possible, sent, verified, standard name, firstname, middlename, lstname, email, inst, old institution)
 +
** To display the table. For each author id will get aid, $aname = $pg_aid {$aid} {index}{'1'}.(aid is author id, aname is author name, $pg_aid is postgres author id, index (table)is the list of author in the specific paper, '1' is the author order, author order is always 1, but possible can be 1, or 2 or more until i connenct it to the real author. bgcolor blue. background color is blue, if it is already verified changes to grey color.
 +
** hidden numbering author information, starts with 0, 1, 2, 3...etc. hidden the numbering value so it won't be change by mistake. aid_$i(author 0, 1, 2, 3 etc. total number of authors in the paper) type: hidden (can be show or hidden, checkbox), value (resultado) = $aid (author id).
 +
** Process names to match against the aka hash. Gets authors names from aname (see few lines above), them take out commas and dots, replace underscores for spaces.
 +
** For possible, sent and verified will get data from $pg_aid and show it in the table cells.
 +
*** $hightest_join. will keep track of the highest join for each author_id to make a new one.
 +
*** $table eq'verified' if YES means already verified and will show in color grey.
 +
*** $table eq'possible' will show a link to person_editor.cgi?action=Search&input_number_1=$twonum&curator_two=$curator_two
 +
** Match against %xml_authors. is the xml has a author name (aname)will get lastname, firstname and standard name. will say the xml author was found putting it in the %xml_authors_found (hash).
 +
** print by cells (td). First row is value id _0 for all the columns, ejemplo aid_0, standard name_0, middlename_0, second row will be _1, aid_1, standardname_1, email_1, ins_choice_$1, old_inst_choice_$1, etc.
 +
*** Print hidden highest join. input type=hidden name=highest_join_$i
 +
*** 'aid' 1st cell Print author aid (131888)author name. author 0, 1, 2, 3 etc. total number of authors in the paper) type: hidden, value (resultado) = $aid (author id).
 +
*** 'author' 2nd cell Print author name.
 +
*** 'possible' 3rd cell Print all possible two
 +
*** 'sent' 4th cell Print Sent if email was sent or blank if not.
 +
*** 'verified' 5th cell Print if the paper was verified or blank if not.
 +
*** 'standard name' - data comes from xml_authors hash
 +
*** 'firstname' - data comes from xml_authors hash
 +
*** 'middlename'- blank from xl
 +
*** 'lastname' - data comes from xml_authors hash
 +
*** 'email' -  blank from xml
 +
*** 'inst' - blank from xml. select dropdawn, values are blank, Inst1, Inst2... max (inst100)
 +
*** 'old inst' - blank from xml. select dropdawn, values are blank, Inst1, Inst2... max (inst100)
 +
**** line 2. &matchFullnameToAka: standard name = full name, first name = fore name, last name = last name.
 +
aka_hash is in lowercase. Will transform capital letters of full name to lower cases.
 +
**** if the fullname is in the aka hash.
 +
** Por each Person each Last name  Each name (including initial) is stored in the %aka_hash like $aka_hash{possible name combination}{two#}. For each author standard_name will compare against all aka possible combinations from the aka hash giving a result of two#s, will show a link to person_editor.cgi?action=Search&input_number_1=$twonum&curator_two=$curator_two. Samething repeated to all authors.
 +
*** L F
 +
*** F L
 +
*** If middle name exist: algorithm
 +
*** L F M
 +
*** L FM
 +
*** F M L
 +
*** FM L
 +
* hidden input: aids_to_check values is how many authors id are in the paper.
 +
* if curation_done button of 'Create people fom XML' won't show.
 +
* if the author from the paper do not match with the xml (kimberly or I changed name with no foreign characters)it will say here 'xml author $aname has no match in paper Firstname: $firstname; Lastname: $lastname; Initials : $initials.
 +
 
 +
 
 +
===&personStatistics===
 +
Statistics on person curation by year-month, to keep track of how many new people enter the field. Juancarlos has added a button "Person Statistics" that looks at the h_two_status(history table for status) and groups by year-month and status Valid /Invalid. The first column is the year-month.
 +
Second are how many have a 'Valid' value in that time range.
 +
Third are how many have an 'Invalid' value in that time range.
 +
Fourth are 2nd minus 3rd (total valid person increase).
 +
Fifth, Sixth, Seventh the total from the beginning of time up to that  year-month.
 +
 
 +
===&createPeopleFromXml===
 +
* get paper joinkey from cgi. Showing 'Creating persons from authors in WBPaperxxxxxxx' and a link to paper editor http://tazendra.caltech.edu/~postgres/cgi-bin/paper_editor.cgi?curator_id=$curator_two&action=Search&data_number=$paper_joinkey
 +
* link to previous paper: $prev_url = "person_editor.cgi?curator_two=$curator_two&paper_id=$prev_paper_joinkey&action=Search+Paper";
 +
* link to next paper: my $next_paper_joinkey = $paper_joinkey + 1; my $next_url = "person_editor.cgi?curator_two=$curator_two&paper_id=$next_paper_joinkey&action=Search+Paper";
 +
* checkout_page: my $checkout_url = "person_editor.cgi?curator_two=$curator_two&paper_id=&action=Checkout+Papers"; "<a href="$checkout_url">checkout page</a>.";
 +
* write hidden inputs :
 +
** paper_joinkey = $paper_joinkey
 +
** which_page = createPeopleFromXml
 +
* get max instituions
 +
* aids_to_check
 +
* for each of $max_institutions, for each of the tables (institution, street, city, state, post, country)will get data when pressing 'Create People from xml' buton.
 +
** will store data $inst{$1 - 40}{table} {$j (for street means 1-4, and all others 1}= $data. Example Instituion1, street 1,2,3, 4 (even if blank), state1, city1, post1, country1. Institution2, street 5, 6, 7. 8, city2, post 2, state2, country2...etc.
 +
* @two_fields: old_inst_choice inst_choice aid highest_join standardname firstname middlename lastname email
 +
* @two_tables:  standardname firstname middlename lastname email.
 +
* $two_number = &gethighestJoinkey: "SELECT two FROM two ORDER BY two::INTEGER DESC". Will get the highest two#.
 +
* for each of the author rows ($i) (0 - $aid_to-check amount of author of papel, starting with 0 = row 0):
 +
** for each table of @two_fields (old_inst_choice inst_choice aid highest_join standardname firstname middlename lastname email ) will store date like this: $aids{$i (raw# 0 - #of authors in the paper)}{table (old instituition choice, standard name...etc}{1 (always is 1)}= $data.
 +
** After getting all the data from the form, gets author id $aid = $aids{$i(es el row)}{aid}{1}= $data
 +
** $pap_join = is the join per author/Person possible, example author id (aid 13194)join 1, if it is not the author, I assign another possible join 2.
 +
** $pap_join = 1; if ($aids{$i}{highest_join}{1}) { $pap_join = $aids{$i}{highest_join}{1} + 1; } The pap_join is 1, but if there is already 1 (row), the pap_join will be the highest_join + 1.
 +
** $aid_error_message. If I don't choose and institution or last name will give you an error. 1.- error No last name -> $aid_error_message .= No lastname chosen for AID $aid. 2.- No inst_choice -> $aid_error_message .= No institution nor old institution chosen for AID $aid.
 +
** If the institution chosen doesn't have a value (data) for institution field 'institution' under Inst1, or Inst2 (which is Institution number or Institution id) will give an error.
 +
*** $inst_id = $aids{$i}{inst_choice}{1}; If there is an institution choice, will get it.
 +
*** unless ($inst{$inst_id}{institution}{1}) { $inst_error_message .= "ERROR : Institution $inst_id chosen for Inst, but it has no institution data>n"; } }
 +
** $old_inst_id = $aids{$i}{old_inst_choice}{1}; Same thing for old institution, if old_inst_choice was selected and it doesn't have a value (data) for old institution field, it will print an error message. ERROR : Institution $old_inst_id chosen for old Inst, but it has no institution data.
 +
** $two_number++; after getting the highest joinkey, it will get a two#.
 +
** @pgcommands = &addToPgcommandsTwoCreation($two_number, $curator_two, @pgcommands);
 +
*** see under section above &createNewPerson (&addToPgcommandsTwoCreation($two_number, $curator_two, @pgcommands);)
 +
** foreach two_tables (standardname firstname middlename lastname email) will get data for %aids hash ($aids, author id) and put it into Postgres and History.
 +
*** my $data = $aids{$i}{$table}{$order};
 +
***(@pgcommands) = &addToPgAndHst($table, $joinkey, $order, $data, $curator_two, @pgcommands);
 +
*** see under section above &createNewPerson.
 +
** foreach my $table (@inst_tables)For each existing Institution table, will look for data in institution hash to add to Postgres and History tables.
 +
*** (@pgcommands) = &addToPgAndHst($table, $joinkey, $order, $inst{$inst_id}{$table}{$order}, $curator_two, @pgcommands);
 +
** if ($old_inst_id)old institution data. If there is an old_institution it will go under old_institution tagg in Postgres. Then it will get an old institution date.
 +
*** my $table = 'old_institution'; my $order = 1; (only get the first institution)
 +
*** @pgcommands) = &addToPgAndHst($table, $joinkey, $order, $inst{$old_inst_id}{'institution'}{$order}, $curator_two, @pgcommands);
 +
*** print "$table : $order : $inst{$old_inst_id}{'institution'}{$order};
 +
*** my $date = &getPgDate(); $table = 'old_inst_date'; # use current date for old institution date (confirmed by Cecilia)
 +
*** @pgcommands) = &addToPgAndHst($table, $joinkey, $order, $date, $curator_two, @pgcommands);
 +
*** print "$table : $order : $date
 +
** $edit_two_url = 'person_editor.cgi?curator_two=$curator_two&action=Search&display_or_edit=edit&input_number_1=$joinkey'; # add link to person_editor in edit mode.
 +
* pap_curation_done: If pap_curation is done, does nothing. If it is not done it will select the highest pap_curation_done (there are for author_person, genestudied and gocuration in the same dropdown Paper-editor)
 +
** my $is_curation_done = 0; "SELECT pap_curation_done FROM pap_curation_done WHERE pap_curation_done = 'author_person' AND joinkey = '$paper_joinkey";
 +
** push @pgcommands, "INSERT INTO pap_curation_done VALUES ('$paper_joinkey', 'author_person', '$order', '$curator_two')";
 +
**push @pgcommands, "INSERT INTO h_pap_curation_done VALUES ('$paper_joinkey', 'author_person', '$order', '$curator_two')";} # unless ($is_curation_done)
 +
* print and execute all pg commands
 +
** foreach my $pgcommand (@pgcommands) {print "$pgcommands";
 +
** $dbh->do( $pgcommand );} # foreach my $pgcommand (@pgcommands)
 +
** #($isOk) = &updatePostgresByTableJoinkeyNewvalue($field, $joinkey, $order, $newValue, $curator_two);
 +
* print "<input type=\"hidden\" name=\"max_institutions\" value=\"$max_institutions\">\n"; Will print also max institutions, maybe not really needed.
 +
 
 +
===&firstPage===
 +
* which_page = firstPage (Is the front page of the Person-editor form)
 +
* &populateCurators. From drop down at 'Select your Name :' can select from @curator_list = ('two1823', 'two1');
 +
* 1 # display curators in alphabetical (array) order, if IP matches existing ip record, select it. The form selects it at opening the form.
 +
* 2 Date : $date -> print current date top right corner of the form
 +
* 3 WBPaperID: input id= paper_id name= paper_id size=10 value=' '><input type=submit name=action value= Search Paper>
 +
* 4 Search Paper
 +
* 5 Checkout Papers
 +
* 6 Create New Person
 +
* 7 Search
 +
* 7.1 Display (input type= radio name= display_or_edit value= display $display_checked >display)
 +
* 7.2 Edit (input type= radio name= display_or_edit value=edit $edit_checked >edit)
 +
* foreach my $table ("number", @normal_tables) For each normal table show &showEditorText
 +
** Refer to &searchPaper section for explanation of &showEditorText. Shows the data in the fields with autocomplete but will not save it.
 +
** input type= hidden class= fields value= $table
 +
** input type= hidden id= type_input_$table value= $type_input{$table}
 +
** input type= hidden id= highest_order_$table value= 1 (Value always 1 for all fields including street)
 +
*** cgi form tells Javascript what tables/fields to look at by passing a hidden input element with class='fields'.To show data with autocomplete.
 +
** will print 2 check boxes: substring_$table and case_$table
 +
*** input type= checkbox value= on name= substring_$table >substring
 +
*** input type= checkbox value= on name= case_$table >case insensitive (automatic substring)<
 +
*** table two (where you input two# for searching) and two_status have no checkboxes for substring and case insensitive (automatic substring).
 +
 
 +
 
 +
===PERSON dumper===
 +
* log into acedb: ssh acedb@tazendra.caltech.edu (cd /home/acedb/cecilia/citace_upload)
 +
** ./get_pap_person_ace.pl > persons.ace (tazendra-..e_upload-30: ./get_pap_person_ace.pl > persons.ace)
 +
** tazendra-..e_upload-32: cp persons.ace persons.ace2011July4 (copy result file, changing date in each upload)
 +
* $error_file = 'errors_in_pap_person.ace';
 +
** tazendra-..ce_upload-8: vim errors_in_pap_person.ace
 +
** If error, correct them and re-do el dump,do not rename el file.
 +
*** DELETE FROM two_status WHERE joinkey = 'two11214';
 +
*** DELETE FROM two WHERE joinkey = 'two11214';
 +
** test dump tazendra-..e_upload-30: ./test_read.sh
 +
Si hay error, vuelvelo a hacer y no cambies el nombre del file, se escribe encima.
 +
** c) Hacer spc a Wen tazendra-..e_upload-22: scp /home/acedb/public_html/cecilia/persons/persons.ace
 +
citace@spica.caltech.edu:Data_for_citace/Data_from_Cecilia/(en una sola linea los2 lineas de arriba)citace@spica.caltech.edu's password:
 +
 
 +
* @normal_tables = qw(firstname middlename lastname standardname aka_firstname aka_middlename aka_lastname email old_email old_email_date street city state post country institution old_institution old_inst_date mainphone labphone officephone otherphone fax pis lab oldlab left_field unable_to_contact privacy webpage usefulwebpage wormbase_comment hide status mergedinto acqmerge comment );
 +
* Qualifiers:
 +
** $qualifier_paper_hash
 +
** $qualifier_general
 +
*** my $specific_two_num = 'all'; $specific_two_num = 'two1';$specific_two_num = 'two6792'; (when all is selected will get all two#s, and when not a specific two number has to be selected.
 +
*** $qualifier_paper_hash = " AND pap_author_possible.pap_author_possible = '$specific_two_num'";(pap_author_possible has to match the two#)
 +
*** $qualifier_general = " AND joinkey = '$specific_two_num'"; }
 +
* &populatePaperHash paperHash Will read all data from paper tables and put it into %paperHash.
 +
** %paper_valid; my %paper_index
 +
** SELECT * FROM pap_author ORDER BY pap_timestamp (Will select all authors from pap_author table. Mapping author_id to papers)
 +
** while (my @row = $result->fetchrow) { $paper_index{$row[1]} = "WBPaper$row[0]"; }
 +
*** row1 = author id; WBPaper$row = paper id.
 +
** %author_valid; my %author_index;
 +
** SELECT * FROM pap_author_index ORDER BY pap_timestamp (Will select all authors id and authors name getting rid of space and comments from author_index hash. Mapping author_id to names)
 +
*** $author_index{$row[0]} = $row[1]; } ({$row[0]} es author id); ($row[1] = author name).
 +
** SELECT pap_author_possible.author_id, pap_author_possible.pap_author_possible, pap_author_possible.pap_join  , pap_author_verified.pap_author_verified, pap_author_verified.pap_timestamp (Will select all these paps tables and columns, ejemplo 'pap_author_possible.author_id' 'pap_author_possible (table)', after period is column, 'author_id (column)'. FROM pap_author_possible, pap_author_verified, Looking for 'pap_author_verified.author_id' and 'pap_author_verified.pap_join' AND 'pap_author_verified.pap_author_verified' is YES OR NO (meaning NO blank verified)
 +
**$qualifier_paper_hash (If picked 'all' at $qualifier_paper_hash, the qualifier is blank, NO qualifier)
 +
** $person = $row[1]; my $author_id = $row[0]; my $verified = $row[3]; my $paper = ''; my $author_name = '';
 +
**(person = row 2, author_id = row1 , verified = row 4, paper = ?, author_name = ?)
 +
**(Selects all author from pap_author tables, giving authors id to papers. Then selects authors id (stripping extra spaces and comments) mapping author id to names. Then selects possible and verified looking for verified ids and Yes or No)
 +
**  if ($verified =~ m/NO/) If paper is verified NO. If there is a dot in the paper, meaning erratum or paper inside a paper it will not delete it. It will delete it from paperHash the Person-Paper connections for these 3: paper, paper_lab, paper_lineage (verified by script by labs and lineage). And add it to not_paper connection.(not_paper is for Cecilia to keep track of papers verified as Not mine)
 +
*** delete $paperHash{$person}{paper}{$paper}; delete $paperHash{$person}{paper_lab}{$paper}; delete $paperHash{$person}{paper_lineage}{$paper};$paperHash{$person}{not_paper}{$paper}++;
 +
** if verified YES. Only if there are $paper_index and $author_id. $paperHash{$person}{paper}{$paper}. In paper hash (directory) if a specific Person has been verified Yes (verification of the Person connection) to a specific paper, it will connect it here.
 +
*** If not verified by author_id ne (no equal) 'two363' (Raymond, by script).
 +
**** with 2 empty spaces between YES and Raymond Lee: YES__Raymond Lee = verified by lab script ($paperHash{$person}{paper_lab}{$paper}).
 +
**** with 1 empty space between YES and Raymond Lee: YES_Raymond Lee = verified by lineage script ($paperHash{$person}{paper_lineage}{$paper}).
 +
 
 +
==IMPORTANT NOTES==
 +
*Subject: hide / privacy / unsubscribe
 +
* hide : whole entry doesn't dump to .ace for citace upload doesn't show anything
 +
* privacy : email doesn't show in .ace for citace upload might be used for paper connection emails ?
 +
* unsubscribe : NEW  show in .ace, don't send emails for newsletters
 +
** To remove from database: use hide
 +
** To unsubscribe from letters:
 +
*** testdb=> INSERT INTO two_unsubscribe VALUES ('two15191', 'unsubscribe');
 +
*** testdb=> SELECT * FROM  two_unsubscribe;
 +
** To remove unsubscribe:
 +
*** testdb=> DELETE FROM two_unsubscribe WHERE joinkey = 'two15191';
 +
* Do not reload from page after creating new people from xml.
 +
* After testing that middle names are ok in new Person, delete two_middlename WHERE middlename = apsotrofe apostrofe;
 +
 
 +
* UPDATE January 22, 2014
 +
Paper_editor - Person Author Curation
 +
http://tazendra.caltech.edu/~postgres/cgi-bin/paper_editor.cgi
 +
 
 +
Papers flagged for non_nematode and author_person at the same time, were not showing for Person curation.
 +
we changed that query from
 +
  SELECT * FROM pap_author WHERE pap_author IN ('$author_ids') AND joinkey NOT IN (SELECT joinkey FROM pap_curation_flags WHERE pap_curation_flags = 'non_nematode')
 +
to
 +
  SELECT * FROM pap_author WHERE pap_author IN ('$author_ids') AND joinkey IN (SELECT joinkey FROM pap_curation_flags WHERE pap_curation_flags = 'author_person')
 +
 
 +
meaning instead of looking for not having non_nematode, now it must
 +
have author_person  under pap_curation_flags.
 +
 
 +
 
 +
* UPDATE March 6, 2013
 +
1.- two_hide changes because Paul Davis: Mysterious People In WormBase.
 +
That's an interesting point especially as their WBPerson IDs exist associated with data in other objects such as papers.
 +
 
 +
ftp://ftp.sanger.ac.uk/pub2/wormbase/STAFF/Mysterious_People.dat
 +
 
 +
I think it would be better to remove/hide their contact data but leave a stub object that at lease contains their name??
 +
 
 +
Cecilia.
 +
 
 +
The "Valid" WBPerson IDs might be valid, but they are empty in the citace upload meaning that they are referenced by other classes (Author) meaning that the ?Person data and ?Author data are out of sync in the WS237 upload.
 +
 
 +
** dump was printing nothing if tag two_hide.
 +
** changed to always show Object (WBPerson#)and status.
 +
** if status invalid: checks for merge.
 +
** if status valid: checks for two_hide.
 +
** if two_hide: prints only status and standard name and comment 'This Person requested not to be contacted'.
 +
 
 +
2.- Old_institution tag.
 +
Not possible to stop dumping dates in old_institution date as suggested by Raymond because:
 +
We can't stop putting that in acedb because the model says :
 +
  Old_address DateType #Address  // for history
 +
 
 +
which makes sense since we have to differentiate the data from
 +
different #Address hashes
 +
 
 +
Problem: Old_institution tag with same date was dumping only one in acedb/citace, ergo showing only 1 in tree-display in WB website.
 +
Solution: Changed dates in Person-editor, so each shows a different date. I have added dates in same line of Old_institution for Person who requested it, see two906.
 +
Website: Requested to not show dates in old_institution since we don't really know when people started nor ended at each institution.
 +
 
 +
 
 +
* UPDATE February 3, 2012
 +
Added 'show institutions with data only' in section Person editor: display mode, edit mode/Functionality/Inst1 - click to show another institution - hide another institution. It will show all institutions created in the form if I hit back to continue editing same paper with over 40 authors. Delete Curation Flags/curation_done/author_person from Paper editor to continue editing same paper. Added total 100 institutions to 40 original number.
 +
** It will show institutions only if they have something in the Institution field (not street, etc.)
 +
** It will also hide institutions that don't have data.
 +
** It will never hide Institution #1.
 +
 
 +
* Email asking to verify papers
 +
/home/postgres/work/pgpopulation/pap_papers/author_person/email_connected_authors.pl
 +
 
 +
* August 29, 2012. Paper editor autocomplete was showing invalid Persons. FIXED. j chan: I changed the ontology_annotator.cgi  getAnyWBPersonAutocomplete so that it finds all two_status = 'Invalid'
 +
and when it find a person ID to autocomplete on, it add 'INVALID' after the name, before the " ( WBPerson#### ) "
 +
 
 +
 
 +
July7, 2011 changed text Kimberly request.
 +
 
 +
* Changed text (date) of automatic letter after people verify papers(Octubre 5, 2011)
 +
/home/azurebrd/public_html/cgi-bin/forms/confirm_paper.cgi
 +
 
 +
* 2001-06-06  Person editor in tazendra (development database)
 +
 
 +
http://tazendra.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi
 +
 
 +
We don't have the mangolassi sandbox, so I've made a development database, separate from the database with real data (devdb for developtment vs. testdb for real data), and copied the person_editor.cgi to tazendra, to access the development database.  So you can test it, and it should work just like it used to, and you can mess with the data.  Don't forget that any links to the paper editor and so forth are real links and data in those forms will make real changes.
 +
 
 +
* 2001-06-07 Person_lineage:
 +
 
 +
http://tazendra.caltech.edu/~azurebrd/cgi-bin/data/person_lineage.ace
 +
aqui se ven todos los lineage mandados
 +
 
 +
/home/postgres/work/pgpopulation/two_people/two_lineage/update_twos_in_two_lineage.pl
 +
output files /home/postgres/work/pgpopulation/two_people/two_lineage/
 +
 
 +
cd  /home/postgres/work/pgpopulation/two_people/two_lineage/
 +
ls
 +
lineage_psql_commands  not_found  update_twos_in_two_lineage.pl*
 +
 
 +
vim lineage_psql_commands : ve los datos, como los ve, que pone y que no pone.
 +
vim not-found : lista de los que no encuentra
 +
vim update_twos_in_two_lineage.pl* : es el programa
 +
 
 +
* SELECT * FROM pap_author WHERE pap_author = '98640';
 +
To search for Paper by author id.
 +
 
 +
* Invalid paper - To delete possible, sent and verified when invaliding a paper.
 +
** Paper editor can invalid verified by clicking in verified box, will mark Yes first, click again will mark it NOT mine, click 3rd time and will mark it empty.
 +
** sent is not editable, use psql to delete data.
 +
** SELECT * FROM pap_author_possible WHERE author_id = '80358';
 +
** SELECT * FROM pap_author_sent WHERE author_id = '80358';
 +
** SELECT * FROM pap_author_verified WHERE author_id = '80358';
 +
** DELETE FROM pap_author_possible WHERE author_id = '80358';
 +
** DELETE FROM pap_author_sent WHERE author_id = '80358';
 +
** DELETE FROM pap_author_verified WHERE author_id = '80358';
 +
 
 +
:%s/80352/80353/g
 +
 
 +
* This is to move the data from the old tables to the new tables with history.
 +
**/home/postgres/work/pgpopulation/two_people/20110515_person_retable/recreate_tables.p
 +
 
 +
=== aka ===
 +
# http://wiki.wormbase.org/index.php/Author_Person (script upgraded 2011 for Person-editor)
 +
# http://tazendra.caltech.edu/~postgres/cgi-bin/paper_editor.cgi?; (From Paper-editor, Person Author Curation section)
 +
# http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cg (Confirm paper form, NOT in use anymore)
 +
# Asociar a  single match: tazendra-..e/cecilia-3: cd UPLOAD/new-upload; ./connect_single_match_authors_and_get_histogram.pl (prgrma que corro semanalmente para asociar a single match, them i ran another program to verify by lab/lineage, monthly a sent emails for paper verification and ran upload as requested montly or bi-monthly.
 +
* 1.- Script upgrade to catch more possible connections
 +
**F M L and under regular name
 +
** delete empty spaces beginning and end of the name
 +
** keep empty space between names
 +
** delete commas and periods
 +
** change all letters to lower cases
 +
** for middle name adds first initial
 +
** delete two_status Invalid
 +
* Por each Person each Last name  Each name (including initial)
 +
** L F
 +
** F L
 +
** If middle name exist: algorithm
 +
** L F M
 +
** L FM
 +
** F M L
 +
** FM L
 +
*- for xml test program
 +
**1. akas lower case
 +
**2.  - For each author of each paper not verified YES search:
 +
              Paper  auhor_id    Author_name
 +
        - look for pap_identifier to get pmid/read XML
 +
        - open''
 +
 
 +
'' b.- Pubmed xml
 +
        xtract:  <LastName>Pierce</LastName>
 +
                    <ForeName>Sarah B</ForeName>
 +
                    <Initials>SB</Initials>
 +
        Construct exact name:
 +
        two_firstname = ForeName
 +
        two_middlename = blank
 +
        two_lastname = LastName
 +
        two_standardname = Forename + LastName
 +
        two_aka_firstname = Initials
 +
        two_aka_middlename = NULL
 +
        two_aka_lastname = Forename
 +
 
 +
                  <Affiliation>Departments of Medicine (Medical
 +
Genetics), Genome Sciences, Biological Structure, and Biochemistry,
 +
University of Washington, Seattle, WA 98195.</Affiliation>
 +
** running a comparacion between the one I ran weekly and the one in peson-editor, showed that the later is connecting to valid and invalid Person, Lito is fixing it to that both restrict it only to Valid Person. Got rid of invalid two section
 +
July 13, 2011 finished documenting Person editor. Next step will be documenting uploads.
 +
 
 +
September 1, 2011 Updates to Person editor
 +
* 1.- Add 'Switch to display' link to Person editor, editor mode at the button, just above lineage part.
 +
added :
 +
  print "Switch to <a href=\"$toggle_url\">$opp_display_or_edit</a>.<br/>\n";
 +
before :
 +
  &makeLineageDisplay($joinkey, $display_or_edit, $header_bgcolor,$header_color);
 +
* 2.- Add move to old button 'move' to email -> old_email and old_email_date.
 +
New javascript function :
 +
function moveDataToOldTable(field, order) {
 +
// some tables have data tables and corresponding old data and old timestamp tables, this moves it from data to the old tables and deletes from data table.  2011 08 31
 +
var joinkey = document.getElementById("person_joinkey").value;
 +
var tsId = 'timestamp_' + field + '_' + order;
 +
var timestamp = document.getElementById(tsId).value;
 +
var valueId = 'input_' + field + '_' + order;
 +
var value = document.getElementById(valueId).value;
 +
var oldOrderId = 'highest_old_' + field + '_order';
 +
var highestOldOrder = document.getElementById(oldOrderId).value;
 +
var oldField = 'old_' + field;
 +
var oldTsField = 'old_' + field + '_date'; if (field === 'institution') { oldTsField = 'old_inst_date'; }
 +
updatePostgresTableField(oldField, joinkey, highestOldOrder, value);
 +
updatePostgresTableField(oldTsField, joinkey, highestOldOrder, timestamp);
 +
updatePostgresTableField(field, joinkey, order, '');
 +
} // function moveDataToOldTable(e)
 +
 
 +
 
 +
change section on  makeDoubleVertical  this now always looks for the
 +
entry with a 'white' $bgcolor, meaning that it has the order for the
 +
new row, and changes the $order to be :
 +
if ($bgcolor eq 'white') { $order .= "<input type=\"hidden\" id=\"highest_${one_table}_order\" value=\"$order\">\n"; }        # have an id for the next highest order of the old tables
 +
 
 +
So that the javascript can get the order to use for moving data to the new tables.
 +
 
 +
 
 +
change section on  makeSingleNormal  under
 +
if ($display_or_edit eq 'edit') {
 +
    ($td_one_data) = &makeInputField($one_data, $one_table, $joinkey, $order, '3', '1', '', $td_width, $input_size);
 +
    if ( ($bgcolor eq $blue) && ( ($one_table eq 'email') || ($one_table eq 'institution') ) ) { ($one_table) = &labelAddMoveButton($one_table, $order); } }  # if the table already existed and has a corresponding old table, add a button to move the data
 +
 
 +
So that if the $bgcolor is $blue and the table is either email or institution, add the ``move'' button by calling
 +
&labelAddMoveButton($one_table, $order)
 +
 
 +
All this does is :
 +
sub labelAddMoveButton {
 +
my ($table, $order) = @_;
 +
$table .= " <button onclick=\"moveDataToOldTable('$table', '$order')\">move</button>";
 +
return $table;
 +
} # sub labelAddMoveButton
 +
 
 +
Note that this changes  makeSingleNormal  instead of making a whole
 +
new type for singleNormal that has buttons.
 +
 
 +
==Perl notes==
 +
* % anuncia que es un hash
 +
* Hash is an sistema de organizacion de llaves( cualquier palabra, varable, codigo). Las llaves pueden dar subdivisiones o valores. Ejemplo hash Bob = %Bob
 +
** %Bob = puede tener multiple valores
 +
** $Bob {two1} (llave)= cecilia (1 valor)
 +
** $Bob {two625} = Paul (1 valor)
 +
** $Var = 1 (1 valor)
 +
** $x = ceci (1 valor)
 +
** $y = falso (1 valor)
 +
* %ROB (hash)
 +
** $ROB {name} {two1} = ceci (ROB = hash); ({name} {two1} = keys); (ceci = values, resultado)
 +
** $ROB {name} {two625} = Paul
 +
** $ROB {city} {two1} = Chastsworth
 +
** $ROB {city} {two625} = Pasadena
 +
*pg_aid = postgres author id.
 +
*Example %pg_aid hash, how to get a value from the %pg-aid hash using the keys
 +
**$pg_aid {$aid (1234 numero del author id)}{index (table index)} {$join (1 or whatever)}= ceci (value el resultado).
 +
* %pg_aid hash
 +
**$pg_aid multiple keys divided by {} = value (resultado)
 +
**$pg_aid {$aid}{index}{$join} = ceci
 +
**$pg_aid {$aid}{possible}{$join} = two1
 +
**$pg_aid {$aid}{sent}{$join} = SENT
 +
**$pg_aid {$aid}{verified}{$join} = YES
 +
 
 +
==javascript notes==
 +
* blur = Focus - enter into cell. blur - getting out of the cell by clicking else where or hitting tab. When burring in the Person-editor edit mode it will update. RECAP: which_page tells javascript which actions to load for a specific page. javascript file uses to load different actions (like autocomplete in one page, or adding extra institutions, or editing upon blurring a field).
 +
* which_page is a label for javascript to know  which actions to do in a specific page, autocomplete ans show and hide another institution, updating data.  Example, Person editor, if you click edit will use autocomple.
 +
 
 +
==Useful links==
 +
http://www.mediawiki.org/wiki/Help:Formatting

Latest revision as of 00:05, 2 May 2014

Form URL

http://mangolassi.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi (sandbox for testing)

New postgreSQL Tables

  1. Data tables
  2. History tables

Some data tables do not have history, only have joinkey (two#), value, two_timestamp (timestamp) :

  • two -- second column 'two' is an integer
  • two_unsubscribe -- second column is text
  • two_curator_ip -- second column is text
  • two_lineage - joinkey , two_sentname (not editable), two_othername (not editable), two_number (of the person connecting to), two_role, two_date1 (Beginning in the year), two_date2 (Through the year), two_sender (email of sender or REV- email for reverse connection). two_lineage is a 2 way connection table, connects to sender and recipient who is the other person role connecting identified by REV-.

All other data tables have joinkey (two#), two_order (integer), two_

(data -- text), two_curator (two# -- text), two_timestamp (timestamp). History tables have h_ in front to keep track of editing history (h_two_status) keeping same columns:
  • Data and History tables:
  • two_firstname -- single value. always has a value.
  • h_two_firstname -- single values. to keep track of editing history.
  • two_middlename -- single value.
  • h_two_middlename -- single value. to keep track of editing history.
  • two_lastname -- single value. always has a value.
  • h_two_lastname -- single values. to keep track of editing history.
  • two_standardname -- single value. always has a value.
  • h_two_standardname -- single values. to keep track of editing history.
  • two_street -- multi value.
  • h_two_street -- multi value. to keep track of editing history.
  • two_city -- single value.
  • h_two_city -- single value. to keep track of editing history.
  • two_state -- single value.
  • h_two_state -- single value. to keep track of editing history.
  • two_post -- single value.
  • h_two_post -- single value. to keep track of editing history.
  • two_country -- single value.
  • h_two_country -- single value. to keep track of editing history.
  • two_institution -- multi value.
  • h_two_institution -- multi value. to keep track of editing history.
  • two_old_institution -- multi value.
  • h_two_old_institution -- multi value. to keep track of editing history.
  • two_old_inst_date -- multi value. from time we knew it was current/got data
  • h_two_old_inst_date -- multi value. to keep track of editing history.
  • two_mainphone -- multi value.
  • h_two_mainphone -- multi value. to keep track of editing history.
  • two_labphone -- multi value.
  • h_two_labphone -- multi value. to keep track of editing history.
  • two_officephone -- multi value.
  • h_two_officephone -- multi value. to keep track of editing history.
  • two_otherphone -- multi value.
  • h_two_otherphone -- multi value. to keep track of editing history.
  • two_fax -- multi value.
  • h_two_fax -- multi value. to keep track of editing history.
  • two_email -- multi value.
  • h_two_email -- multi value. to keep track of editing history.
  • two_old_email -- multi value. (add to Person editor form section to log old_date from time we knew it was current/got data)
  • h_two_old_email_date -- multi value. to keep track of editing history.
  • two_pis -- multi value.
  • h_two_pis -- multi value. to keep track of editing history.
  • two_lab -- multi value.
  • h_two_lab -- multi value. to keep track of editing history.
  • two_oldlab -- multi value.
  • h_two_oldlab -- multi value. to keep track of editing history.
  • two_left_field -- single value.
  • h_two_left_field -- single value. to keep track of editing history.
  • two_unable_to_contact -- single value.
  • h_two_unable_to_contact -- single value. to keep track of editing history.
  • two_privacy -- single value.
  • h_two_privacy -- single value. to keep track of editing history.
  • two_aka_firstname -- multi value.
  • h_two_aka_firstname -- multi value. to keep track of editing history.
  • two_aka_middlename -- multi value.
  • h_two_aka_middlename -- multi value. to keep track of editing history.
  • two_aka_lastname -- multi value.
  • h_two_aka_lastname -- multi value. to keep track of editing history.
  • two_webpage -- multi value.
  • h_two_webpage -- multi value. to keep track of editing history.
  • two_wormbase_comment -- multi value.
  • h_two_wormbase_comment -- multi value. to keep track of editing history.
  • two_hide -- single value.
  • h_two_hide -- single value. to keep track of editing history.
  • two_status -- single value.
  • h_two_status -- single value. to keep track of editing history.
  • two_mergedinto -- single value.
  • h_two_mergedinto -- single value. to keep track of editing history.
  • two_acqmerge -- single value.
  • h_two_acqmerge -- single value. to keep track of editing history.
  • two_comment -- multi value.
  • h_two_comment -- multi value. to keep track of editing history.
  • two_usefulwebpage -- multi value.
  • h_two_usefulwebpage -- multi value. to keep track of editing history.

Changes between old and new tables

Table in Postgress

  • Adding history tables as h_two_...
  • Removing two_apu_ tables, two_groups
  • Adding two_usefulwebpage, two_old_inst_date, two_old_email_date
  • Changing two_comment into normal table with order
  • Changing old_timestamp column to two_curator (all values will be 'two1')

Questions

Please add a button link to Person-editor to possible two. When curating new paper to mark possible match as real possible two, Example WBPaper00039714, 131412 Nicholas, Hannah R. two2627; the possible connection is visual only, could you make a button to mark it as real possible two?

Could you add 'Switch to display.' at the bottom of the page above lineage part? Person-editor editor mode

If you see WBPaper00039714, under 'affiliation' there are 3 affiliations, could this data for iwm2011 abstracts show under affiliations for xml part?

WBPaper00039713 none of the Person's linked to this paper were sent emails for verification june 28, 2011. This is 1 example only

WBPaper00039707 has all authors linked to virtual possible twos, but the regular program didn't connect them, is there a way that they can also be connected?

Please confirm that no one uses this form http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cgi In the last year 111 IPs have used it (mostly once) but a couple of them over 100 each. If you don't use it, is it in the emails you send out or in the submit forms in WormBase ? -- J answer Please keep form, must be the one linking to old papers associated but not verified -- Ceci Sounds good, added to list of forms to check -- J thanks ceci

1681 two_middlename without value, is that okay ? SELECT * FROM two_middlename WHERE two_middlename = ' '; <-- no space in between the singlequotes --J answer It was from when we thoght that a data was needed in tag, please delete if not needed -- Ceci.

1004 two_middlename with NULL order, value, old_timestamp, what should we do with that ? SELECT * FROM two_middlename WHERE two_order IS NULL;

answer i've been adding aka_middlename = NULL to every aka entry, if it is not necessary them we can delete it, but if it is necessary please keep it. Talk tomorrow? Ceci. I am also fixing some typos showing searching in new Person editor Ceci I don't know if you need it to say 'NULL' or not, but I thought you were never supposed to have blank values, you can skype me whenever you want -- J will talk in person about all middle/aka names, empty values

1) two_aka_midlename always has NULL (when none middlename to fill) 2) two_middlename Primero dijimos all tags filled Mas adelante me parece recordar que habiamos dicho que cuando la gente no tiene middle name no se ponia. http://www.wormbase.org/db/misc/person?name=WBPerson1823;class=Person Si esto esta mal hay que volver a poner 1 espacio vacio a los que no tienen data en two_middename.

I don't know if it needs to be a certain way, when I set it up I let you know how it should be and you should make a note of it so you can tell me if something is wrong later.


two427 had under wormbase_comment :

  • two427 | 1 | Due to illness, please get in touch with: Mark Edgley (edgley@interchange.ubc.ca) for gene knockout inquiries. Teresa Rogalski (rogalski@zoology.ubc.ca) for muscle lab matters. 2003-03-07 18:36:25.089309-08 | 2003-03-19 11:38:57.295736-08 |
  • two427 | 1 | Lab:DM or VC | 2003-03-10 16:23:56.877045-08 | 2003-03-19 11:38:57.295736-08
  • two427 | 2 | DM his own lab and VC head of the KO | 2008-01-31 00:00:00-08 | 2008-01-30 10:04:25.884154-08

Which has two order '1', I've deleted the first one, if you want to add it again as order '3' -- J. answer Deleted OK, thanks Ceci

Please comment on these changes :

  • I've added history tables, and changed the postgres tables to store the curator in the 4th column where the old_timestamp used to be. Updating data updates the data table with current timestamp ; removes any history data for that table-joinkey-order in the last 10 minutes, and inserts with current timestamp. answer OK Ceci
  • Street field always has at least 4 fields. answer YES ceci
  • name and aka_name now show first/middle/last in a row with timestamp below them. answer Like it Ceci
  • old_email shows data from old_email_date horizontally with timestamp. answer OK ceci
  • old_institution shows old_inst_date data, and webpage shows usefulwebpage vertically because I assume you need more space, if you'd rather display them horizontally like old_email, let me know. answer OK ceci
  • All changes to the person editing section of the person editor are done, please let me know if I overlooked something. answer looks ok, will look more in detail, thanks Juancarlos ceci Cool, let me know. Thanks -- J two_comment is missing ceci// added - J// thanks, c
  • In the Search Paper (to create people from XML) section, do you want to see input fields to create new people for authors that are already verified 'YES' ? Right now people that are verified 'YES' have a grey background, if you don't want to see the inputs, do you still want the grey background ? Probably easier to talk about this in person / skype. Some papers I tried were 00003865 00026893 -- J
  • wow thanks, looks really great, will look in more detail. Yes talk in person, lots of new stuffs, when do you have time? I have to be in Pasadena on Friday, is that OK with you? See email, we can talk until around 1pm OK answered email
  • I like the grey part. Paper 00026893 is showing aid77392 Davis J - xml Jerel C. Davis - 1 matches two4351 (Ralph E. Davis) -> Are matches to last names even when it doesn't match first initial or first name? ceci no, it's the same matching script that we wrote a few weeks ago, it matches to fullname exactly, we went over how that worked, right ? If not we can go over it in person


  • Last name Davis, first initial J shouldn't it match to two3329 Justin R Davis, two4914 Jamie Davis, two7656 Joseph S. Davis? ceci It matches exactly on xml first+last not initials, the single_match and histogram script you run manually matches with initials and creates them automatically, this is for manual curation
  • Like the grey background, but when clicked on Create people from XML volvio a crear Nadia D. Singh, va a haber un boton para marcar Do not create or ignore it?. Really love the multiple institutions - addresses (hide-show-assign), really like it!!! cec If you assign an institution it does something, if you don't it ignores the person. Yes, I noticed after trying several different things, it is in may notes to talk to you tomorrow.
  • could you add old_institution? it will be useful when creating Persons form oldpapers,o when pdf shows currently at different affiliation, I could add both datas. c - Create new people from XML. Please add old_institution to this part (some people no longer at inst) Just 1 line, no address. Are those two issues the same thing ? If so the old institutions would be one of the 20 normal institutions, but you'd assign it under an old_inst dropdown instead of the normal inst dropdown. So you're saying that if selected in the old_inst dropdown, it would only take the institution line from that inst# and add it to the two_old_institution and the current date to two_old_inst_date. Would there only ever be one old institution ? Would you ever want to add both old institution and current institution ? yes. sometimes I'd add both current and old institution
  • WBPaper00026893. When editing another institution, it is showing street from 1st institution at creating people from XML fixed OK, thanks
  • We only need to change this script for person stats: /home/postgres/work/get_stuff/for_paul/curation_stats/wbperson_creation_stats/get_recent.pl which keys off of the two_display.cgi (unless we change the two_display.cgi to keep working,do you want it to ?) YES from To Do below it's decided that we'll have a display and editor in the person_editor with a checkbox for display mode (default off), and links between display - editor and editor - display. New question : Sometimes outside people have to look at the paper display, will they ever need to look at some kind of person display ? Because if so we don't want them to see the editor, so we should keep the two forms separate in that case. ok good idea, when people see their contact data to update, does it link to two_display? http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/person.cgi?action=Display&number=WBPerson1 no, the person.cgi gets its own person display independent from the two_display.cgi People outside Caltech don't ever use the two display, so we can get rid of it an just use the new person editor in display vs. edit mode.
  • Confirm paper: When they connect papers through confirm_paper.cgi (always)

It says ``WBPerson$two Thank you for updating your Author Person Paper connection When they connect papers through confirm_paper.cgi (saying 'NO') It says ``assigned new join $join to author $aid because said no to paper $joinkey When they comment through confirm_paper.cgi It says ``$two $curator comment for paper connections

When someone verifies a paper through generic.cgi It says ``${wbperson}, thank you for updating your Author Person Paper connection but only if /home/postgres/public_html/cgi-bin/data/confirm_paper_mailing.txt hasn't changed any data in the last day.

1.- generic.cgi (where is it?) You can see almost all the forms (including this one) in the sitemap http://tazendra.caltech.edu/~azurebrd/cgi-bin/index.cgi You can also see it in the emails that you send people to verify papers, when anyone clicks yes or no it's a link to that form

Looking at site map/ generic http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi shows: Your IP is : I clicked yes to a paper http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi?action=VerifyPaper&two_number=two10606&aid=105080&pap_join=1&yes_no=YES

So when you ask me where is the generic.cgi should i tell you the above link or is there a shorter generic version?

The generic.cgi has multiple uses. The default is to show the IP, the one that relates to person verification is http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi?action=VerifyPaper

but the whole link is more useful because it makes it clear what values need to be passed in, and in what format.

2.- confirm_paper.cgi http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cgi yes

  • Kimberly flag papers for Person curation

She's going to flag them in the table pap_curation_flags as 'author_person_priority' if they're priority and it will not have a value if they're not priority. So when you look at the checkout you can just look at the ones that are 'author_person_priority'.

Kimberly, this made me realize that since it's going into curation_flags the value can only either be there or not be there, so we might want to get rid of the 'blank' value because it implies that blank and not-priority are different, when they're stored the same way. We could make it a checkbox like functional annotation. Also, to both of you, should I just set all existing papers to'priority' ? Sounds good to me, c

I'm fine with leaving it as a drop-down but with only two values and priority as the default. We could set all existing papers to priority for now, but that doesn't preclude us from re-categorizing some if we decide at a later time that Reviews, for example, could reasonably be set to not_priority, right? Kimberly

in the Enter New Papers section : - the author-person select at the top with the box for free pmids has an author-person dropdown with values priority and not_priority which populate pap_curation_flags with 'author_person' - each pmid has an aut-per_priority dropdown that works the same way

I've updated the pap_match.pm to allow the extra flag

I'll now work on populating all the papers except for those with functional_annotation as 'author_person' priority in pap_curation_flags. Cecilia, this means that in the future Kimberly will always flag this for you and not set that for those that are functional annotation, so you don't need to think about functional annotation anymore, just whether it's flagged as priority or not. Note that as of 2013-12-05, the functional_annotation flag was changed to now be non_nematode.


  • New script: that looks at all the stuff that was verified in the last 24 hours or by day, except for those exceptions below.

I want to receive daily email notifications or list for every time a paper is verified NOT mine. Unless: - pap_author_verified 'NO Cecilia Nakamura' - pap_author_verified YES after verified NOT same day - do not read NOT if there is a YES in latest timestamp We'd get data from pap_author_verified, we can't tell what came in to there from generic.cgi as opposed to confirm_paper.cgi, we can only know the values and their timestamps. Thanks, ceci And also filter out the papers you've already done, right ? Do you want to give me the range or papers that you've already done so I can enter them ? done, but I don't have documented all I have already curated, will mark them done/delete when I see them. ceci

  • change two_display.cgi to keep working

keys off means that it gets values from. In this case, it means that it knows what tables to look at by looking at the code of the two_display which lists the tables. So the two_display would need to list all the new tables and not the old tables.

Yo si quiero seguir usando el two_display! What's the advantage of the two_display vs. the future paper_editor ? J

http://tazendra.caltech.edu/~postgres/cgi-bin/cecilia/two_display.cgi I like to to-display because it shows only data, and I can do searches I use it all the time.

Ok, the searches should be better in the future person_editor, but the only-data display would be better in the two_display.cgi Or we could add a section in the paper_editor to do only display.

Would this section be able to do also searches? Otherwise I'd like a two_display.cgi where I can both. search and display. c You could probably toggle in the front page whether you wanted to use it in editor mode, or in display mode. But once you were in the search results page you couldn't change your mind at that point. Or maybe in the editor there could be a link to the display and viceversa. What would you prefer ? (think about it, let me know in wiki) J I'd prefer a link to the display and viceversa. c ok

Ceci, please fix the multiple entries in the postgres table two by running /home/cecilia/work/gaps_in_twos/get_recent.pl which now looks for gaps and tells you of multiples :

  • testdb=# SELECT * FROM two WHERE two = 871;
    • two871 | 871 | 2002-08-12 09:25:21.17393-07
    • two871 | 871 | 2004-11-27 14:44:38.614083-08
  • testdb=# SELECT * FROM two WHERE two = 871 AND two_timestamp > '2004-11-26' AND two_timestamp < '2004-11-28';
    • two871 | 871 | 2004-11-27 14:44:38.614083-08

Once you do the SELECT, you can do the DELETE to get rid of just that one Thanks, fixed.

Just recording your oral response. all Paper search in mangolassi are showing as: pap_curation_done author_person in red bold letters Just for me to see if it is the way i'd like it to show. Yes, i like it, impossible to miss it.

*Person_lineage: 2011-06-17 /home/postgres/work/pgpopulation/two_people/two_lineage/update_twos_in_two_lineage.pl output files /home/postgres/work/pgpopulation/two_people/two_lineage/ vim lineage_psql_commands Veo dos posibles soluciones al problema de IGNORE Dr. Chris Link IGNORE Dr. Chris Link UPDATE two_lineage SET joinkey = 'NO' WHERE two_sentname = 'Dr. Chris Link' AND two_othername = 'Dr. Andreas Kampktter' AND two_role = 'Lab_visitor' AND two_sender = 'REV - kampkoet@uni-duesseldorf.de'; UPDATE two_lineage SET two_number = 'NO' WHERE two_othername = 'Dr. Chris Link' AND two_sentname = 'Dr. Andreas Kampktter' AND two_role = 'withLab_visitor' AND two_sender = 'kampkoet@uni-duesseldorf.de'; IGNORE Dr. David Hall 1a Is it possible for the script to not ignore data and only ignore titles? 1b Add under line Lineage Connections with(Please do not add titles to names, script will ignore your data)??? 2 Changes in Person_lineage form: 2a red letters (Your Email) and (Your Full name) change them for black color 2b in section 'LINEAGE CONNECTIONS WITH ' Relationship 1 :(keep relation I trained...etc) Change instead of 1 large blank space split it into 3 spaces, labeled First, Middle and Last names I like solution #2

To Do

For new person editor

Check these forms / scripts (get rid of two_fullname view and replace with code looking at firstname middlename and lastname) :

  • /home/postgres/public_html/cgi-bin/cecilia/two_display.cgi # OBSOLETE
  • /home/postgres/public_html/cgi-bin/cecilia/twoeditor.cgi # OBSOLETE
  • /home/postgres/public_html/cgi-bin/paper_editor.cgi # WAS OK
  • /home/postgres/work/get_stuff/for_paul/curation_stats/wbperson_creation_stats/get_recent.pl # WAS OK
  • /home/postgres/work/pgpopulation/two_people/two_lineage/update_twos_in_two_lineage.pl # WAS OK, looking at row[3] for curator now instead of old_timestamp
  • /home/azurebrd/public_html/cgi-bin/forms/paper_display.cgi # WAS OK
  • /home/azurebrd/public_html/cgi-bin/forms/person.cgi # DONE
  • /home/azurebrd/public_html/cgi-bin/forms/person_lineage.cgi # DONE, replaced two_fullname with two_firstname two_middlename two_lastname. looking at row[3] for curator now instead of old_timestamp
  • /home/azurebrd/public_html/cgi-bin/forms/confirm_paper.cgi # NEEDS TO BE FIXED, looking at wpa instead of pap tables (?)
  • /home/azurebrd/public_html/cgi-bin/forms/generic.cgi?action=VerifyPaper&two_number=two11941&aid=125345&pap_join=1&yes_no=YES # WAS OK
  • /home/cecilia/UPLOAD/new-upload/connect_single_match_authors_and_get_histogram.pl # WAS OK, looking at row[3] for curator now instead of old_timestamp
  • /home/cecilia/UPLOAD/new-upload/verify_by_labs_or_lineage.pl # WAS OK
  • /home/cecilia/UPLOAD/new-upload/email_connected_authors.pl # WAS OK, points to /home/postgres/work/pgpopulation/pap_papers/author_person/email_connected_authors.pl
  • /home/cecilia/work/gaps_in_twos/get_recent.pl # WAS OK
  • /home/acedb/cecilia/citace_upload/get_pap_person_ace.pl

Creando :

  • /home/acedb/cecilia/citace_upload/errors_in_pap_person.ace (vim this and get two# with no status)

Cecilia, put range of papers to mark as curated for author_person here to populate pap_curation_done with value 'author_person' for those WBPaperIDs's joinkeys 33058-33200 33201-33300 33301-33400 33401-33500 33501-33586 33601-33644 34636-34641 35601-35700 35701-35800 35801-35900 35901-36000 36738 36750 36765 36796 36870 37108-37109 37135 37144 34157-37161 37637 37909 37923 37998 38037 38126 38142 38144 38152 38193 38341 When Person curation is done a message will show at top of Person editor page-> Search paper = as pap_curation_done author_person, in bold red letters.

Fix two10121 has no status, two11402 has no status, two11917 has no status, FIXED

For paper Person verification

Change subject letter to email author for verification to show standard name instead of WBPerson id NO email to Cecilia standard automatic reply to paper verification, Yes to cronjobs to keep track of response instead of email to cecilia Possibly we could get rid of that and implement a separate way to track who's responded. We're already planning to have a cronjob check all the 'NO' verification in a 24 hour period, we could have it check all the verifications and sort that into 'NO' and 'YES', or some other system that she'll device. Cecilia, please clarify what you want to change and how to change it Updated subject of confirm paper to not show WBPerson id and to not email me a copy of the of the thank you automatic reply.

  • script to check last 24 hours of YES/NO Cecilia, tell me when you want this to run, and how you want the data grouped (by person / by paper / by yes/no ?)

Run at noon. Data grouped by Papers verified NO, showing aid# + Person#. At the end of run, count of Persons verified YES and count of #Papers.

SELECT * FROM wpa_author_verified ORDER BY wpa_timestamp DESC;

    • Para ver el papel que corresponde al author:

testdb=> SELECT * FROM pap_author WHERE pap_author = '98640';

    • Whatever is in the paper_editor.cgi is what's real.

I wrote a script that I put here : (cd,ls) /home/postgres/work/get_stuff/for_cecilia/20110611_author_verified_wpa And it looks like almost all the values are the same in both tables, so I guess whoever used the confirm_paper.cgi was using it just to add values that already were there. If you see anything in wpa_author_verified that isn't in pap_author_verified let me know so we can look at why the script didn't find it.

Person Update Form

Update contact information form to have extra lines for old_institution, to not show akas, I don't want it to be editable, we'll talk about this later ok

  • UPDATED May 25, 2011: Changed colors. Deleted extra timestamp boxes, extra lines from city, state, post, country. Added old_timestamp with at lest 3 lines.

Full name (discontinued) changed to manually get values

  • Table full_name sees 3 tables, First + middle + last names, if a table is missing it doesn't see it.
  • Changed to manually get 3 values (first_name, middle_name, last_name) if available and combine them instead of full_name. This will resolve problem of people with no middle name, adding an empty value to middle_name tag.

two_display -> Person editor

Old two_display replaced by this really awesome multi tasking new one. Can do multiple combination searches.

Person editor: display mode, edit mode

  • Functionality:
  1. Search - Display or Edit mode - Results Links to Person_editor
    1. Priority = two#, will ignore rest.
    2. Combine search = unlimited search fields
      1. example- email + caltech + case insensitive = Will give results of all People with caltech in email address.
      2. example- ^ (carat) Li (last_name) substring = will give results of all last names starting with Li
      3. example- ard$ (last_name) + caltech (email) substring = two699 Alison Claire Woollard lastname : Woollard
  2. Create New Person: Manual curation, New autofill for some tags.
  3. Search Paper: Individual discriminate paper curation. Will show data from Pubmed xml. Required data: lastname, instituion and either inst or old_inst. Check curation_flags: author_person, if not flagged do not Person curate.
    1. Curation_flags
    2. Bold red message Person curation done.
    3. WBPaper00026893 paper editor link.
    4. Identifiers and pdf links
    5. pmid 15965246 xml found
    6. Affiliation : Stanford University, Stanford, California 94305, USA. ndsingh@stanford.edu
    7. Inst1 - click to show another institution - hide another institution - show institutions with data only
      1. institution : create all institutions linked to paper, current or old clicking to show another institution. This is a required data to create new Persons.
      2. street - auto fill. Data manually entered.
      3. street - auto fill. Data manually entered.
      4. street - auto fill. Data manually entered.
      5. street - auto fill. Data manually entered.
      6. city - auto fill. Data manually entered.
      7. state - auto fill. Data manually entered.
      8. post - auto fill. Data manually entered.
      9. country - auto fill. Data manually entered.
    8. Under this section will show data from xml and postgres pap_ tables:
      1. From postgres pap_ tables: aid,author, possible two#,sent, verified.
      2. From pubmed xml and Cecilia manual curation: standard name, firstname, middlename, lastname (required data to create new Person).
      3. From Cecilia manual curation: email, inst, old inst (either inst or old_inst is required to create new Person).
    9. Enter each author's data (only new Persons to create), Under inst select #1, #2 etc, as well of oldinst. NO# institution will not create new Person (For existing Person do not mark any Institution here).
    10. CREATE people from XML. Required lastname, institution, either inst or old_inst. Will mark Paper as Person-verified. If paper is in the list but was already Person curated, click here without adding any inst or oldinst to mark paper as already curated. Clicking 'Create people from xml' will make a new entry in pap_curation_done for "author_person".
    11. Creating persons from authors in WBPaper00026893 paper editor link.
    12. Checkout next paper id WBPaper00026894 - check out previous paper - checkout page.
  4. Checkout Papers: Will show papers not yet Person curated, flagged from 'author_person' priority in pap_curation_flags, Valid papers.

scripts that run automatically

Note: make sure to keep it updated when adding new scripts and forms, or when I stop using others. (also let Juancarlos know when stop using some so he can remove them from tazendra.)

script for person stats : run to get stats, and they're called by this script /home/postgres/work/get_stuff/for_paul/curation_stats/wrapper.sh which runs every monday at 2am.

/home/postgres/work/get_stuff/for_paul/curation_stats/wbperson_creation_stats/get_recent.pl

This one for person lineage stats : /home/postgres/work/get_stuff/for_paul/curation_stats/wbperson_lineage_stats/get_recent.pl

This one for paper-author-person stats : /home/postgres/work/get_stuff/for_paul/curation_stats/wbpaper_author_person_stats/get_recent.pl

Note: April 17, 2011 We only need to change the first one, which keys off of the two_display.cgi (unless we change the two_display.cgi to keep working,do you want it to ?)YES. keys off means that it gets values from. In this case, it means that it knows what tables to look at by looking at the code of the two_display which lists the tables. So the two_display would need to list all the new tables and not the old tables

scripts and forms

Current May 18, 2011

tables for Person submition form

  • two_firstname -- 1 field. Required.
  • two_middlename -- (if not middle name, empty value?, 1 empty space?). Will talk later.
  • two_lastname -- 1 field. Required.
  • two_standardname -- 1 field. Name as people would like to be addressed. Example: Tuco.
  • two_street -- always shows at least 4 fields.
  • two_city -- 1 field.
  • two_state -- 1 field.
  • two_post -- 1 field.
  • two_country -- 1 field.
  • two_institution -- multi value.
  • two_old_institution -- always shows at least 3 fields.
  • two_old_inst_date -- Should submitters enter old-date ???
  • two_mainphone -- 2 fields.
  • two_labphone -- 2 fields.
  • two_officephone -- 2 fields.
  • two_otherphone -- 2 fields.
  • two_fax -- 2 fields.
  • two_email -- 2 fields. Required.
  • two_old_email -- 2 fields.
  • two_old_email_date - Should submitters enter old-date???
  • two_oldlab -- 2 fields.
  • two_left_field -- 1 field.
  • two_webpage -- 2 fields.
  • two_lab -- 2 fields.
  • two_oldlab -- 2 fields.
  • two_left_field -- 1 field.
  • two_webpage -- 2 fields.

tables for Person editor form

http://mangolassi.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi

  • two_number -- single value. Enter WBPerson number to edit data.
  • two_firstname -- single value. always has a value.
  • two_middlename -- single value. if not middle name, empty value?, 1 empty space?
  • two_lastname -- single value. always has a value. Required.
  • two_standardname -- single value. always has a value.
  • two_street -- always shows at least 4 fields.
  • two_city -- single value.
  • two_state -- single value.
  • two_post -- single value.
  • two_country -- single value.
  • two_institution -- multi value.
  • two_old_institution -- multi value. always shows at least 3 fields. do not forget to log old_date when entering old_institution data
  • two_old_inst_date -- multi value. log old_date from time we knew it was current/got data.
  • two_mainphone -- multi value.
  • two_labphone -- multi value.
  • two_officephone -- multi value.
  • two_otherphone -- multi value.
  • two_fax -- multi value.
  • two_email -- multi value.
  • two_old_email -- multi value. Do not forget to log old_date.
  • two_old_email_date -- multi value. Log old_date from time we knew it was current/got data.
  • two_pis -- multi value.
  • two_lab -- multi value.
  • two_oldlab -- multi value.
  • two_left_field -- single value.
  • two_unable_to_contact -- single value.
  • two_privacy -- single value. Person do not show in WormBase, only Suplemental information (not linked to Person).
  • two_aka_firstname -- multi value.
  • two_aka_middlename -- multi value.
  • two_aka_lastname -- multi value.
  • two_webpage -- multi value.
  • two_usefulwebpage -- multi value. Clicking a box next to two_webpage will activate this tag, created to mark webpages with useful information to create Persons from lab members data.
  • two_wormbase_comment -- multi value.
  • two_hide -- single value. Hyde data, usually email when people don't want their email addresses showing in our webpage. Do not send emails. Email deleted from two_email and entered into two_hide.
  • two_status -- single value.
  • two_mergedinto -- single value. Enter two# of the profile I want to keep. When Person were created double, I pick to delete the one with least papers connected. Transfer all current data to the selected profile (acqmerge), mark each paper connected to this profile in Paper_editor as NOT his/her and enter new join - possible two. And mark this Person as INVALID.
  • two_acqmerge -- single value. Enter two# of the profile you want to delete. When Person were created double, I pick to keep the one with more papers connected, and update contact data, lineage, papers connections and verify them if verified.
  • two_comment -- multi value.
  • two_usefulwebpage -- multi value. Tag where you can write 'useful' or 'not useful'. Not useful means webpage doesn't show lab members data, do no open it to look for lab member info. Note: this is for future data (when new Person-editor is Live), prior to this all webpages will have empty tag. I will manually check each webpage in the future as extra time is available or when curating papers and have to refer to PI webpage. In Person display will show under same order: above webpage and below usefulness.
  • two_status -- single value. Entered automatically at Person creation. Manually mark as INVALID when created double. Don't forget to add values to mergeinto and acqmerfe. Invalid will show in bold red letters at top of two_display form.

- To create the new Person after entering data: NO button, NO reload page (will create a new two#). - Link to Person display. 

history tables are invisible. keeping track of old_email and old_institution history.
http://www.wormbase.org/db/misc/person?name=WBPerson3680;class=Person
See under Tracking, old email and old institution are dated, showing history date (two_old_email_date and two_old_inst_date)

person_lineage

All tables are single value. two_lineage is a 2 way connection table, connects to sender and recipient who is the other person role connecting identified by REV-.

  • joinkey: two#
  • two_sentname: Name or WBPerson# of the person sending data. In REV will be reverse.
  • two_othername: Name or WBPerson# of the person to link data from sender. In REV will be sender.
  • two_number: two number of othername, and this is the data that will show in WB website.
  • two_role: As trainer and trainee. 'I trained as a...' and 'I trained this person as a...'. To delete role in Person-editor scroll up above 'I trained as a PhD under'.
  • two_date1: starting year. To delete date1 in Person-editor scroll up above Present.
  • two_date2: ending year. To delete date2 in Person-editor scroll up above Present.
  • two_sender: when updated from person_lineage form it will show email from sender.
  • two_timestamp: not useful for editing.

Tables for Confirm paper

It is not showing some papers, example WBPerson : 11871, it shows no papers. Showing old tables, fixed June 8, 2011.

Select your name among: Pick Or tpe your WBPerson number: Pick I am Cecilia, box click Yes or let unmarked to verified as the author not as Cecilia.

  • table, WBPerson number, order, value
  • table:
    • firstname
    • middlename
    • lastname
    • institution
    • street
    • city
    • state
    • city
    • country
    • mainphone
    • officephone
    • labphone
    • otherphone
    • fax
    • email
    • old_email
    • pis
    • lab
    • oldlab
    • left_field
    • privacy
    • aka_firstname
    • aka_middlename
    • aka_lastname
    • webpage
      • From each batch of papers below, please click the corresponding ``Select ! button, and follow the directions there.

Afterwards, come back to this page if there is another batch of papers you have not selected : 1) Publications and Abstracts (not confirmed by author in black) 2) Publications and Abstracts (author confirmed in red and blue) 3) Papers are grouped in batches of 10 or less. When a batch is verified, these papers will go to the bottom grouped as blue or red accordingly. For your convenience the next unverified batch will show always on top in black as batch 'papers: 1 to 10'. 4) If you have already confirmed some publications (in red and bue), there is no need to confirm them again unless you'd like to review them.

There are 6 matching papers : (Unverified are black, Yours are blue, Not Yours are red)

      • Update all publications from this batch as Yours: (green color.

clicking this button will show a page confirming data: Curator : Bob Clifford

WBPerson : 871 Thank you for verifying these paper connections.

Please click here to select another batch if there is another still unselected.

Please leave a comment if something is incorrect or unclear. Your record has been updated and changes will show in a WormBase release soon.

Your publication record has been updated to show 00002009 as yours. Your publication record has been updated to show 00004490 as yours. Your publication record has been updated to show 00012571 as yours. Your publication record has been updated to show 00021701 as yours. Your publication record has been updated to show 00022892 as yours.

      • Update all publications from this batch as Not Yours : (red color)
  • Automatic letters emailed by script after people verified papers. Thank you very much for helping associate your C. elegans publications and abstracts.
    • 1. One is when confirmed by confirm_paper comes from (tazendra-..e/cecilia-2: vim /home/azurebrd/public_html/cgi-bin/forms/confirm_paper.cgi).
    • 2. The other one is when confirmed by monthly letter with links to list of papers with link to paper and click here if Yes, click here is Not. Comes from generic.cgi. May 1, 2014 updated letter to:

Thank you very much for helping associate your C. elegans and other nematodes publications and abstracts.

Updates will appear in the next release of WormBase in your WBPerson page http://www.wormbase.org [www.wormbase.org] .

The full release schedule is available here: http://www.wormbase.org/about/release_schedule

Please do not hesitate to contact me if you have any questions. Have a great day.

    • 2.1. generic.cgi (where is it?) You can see almost all the forms (including this one) in the sitemap http://tazendra.caltech.edu/~azurebrd/cgi-bin/index.cgi You can also see it in the emails that you send people to verify papers, when anyone clicks yes or no it's a link to that form

Looking at site map/ generic http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi shows: Your IP is : I clicked yes to a paper http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi?action=VerifyPaper&two_number=two10606&aid=105080&pap_join=1&yes_no=YES So when you ask me where is the generic.cgi should i tell you the above link or is there a shorter generic version? The generic.cgi has multiple uses. The default is to show the IP, the one that relates to person verification is http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/generic.cgi?action=VerifyPaper but the whole link is more useful because it makes it clear what values need to be passed in, and in what format.

Shows WBPaper00036637 showing new tables.

FROM Paper editor: 1.- http://tazendra.caltech.edu/~postgres/cgi-bin/paper_editor.cgi? number (paper number)to curate Person-paper connections by papers.

  • aid - author id number
  • author - author name
  • new - to add another possible connection to this author
  • possible - auto complete writing possible Person name adds Person number)
  • pos_time - time stamp from when connecting possible
  • sent - not editable, shows SENT when email was sent for paper verification
  • verified - editable clicking in the box will show curator name and YES or YES" or NOT mine, or empty.
  • ver_time - time stamp for verification time.

2.- From Paper_editor under 'Person Author Curation' enter two_number (tab)- to curate Person-paper connection by Person.

  • two number
  • institution
  • name
  • aka
  • show (collapsible)street, city, state, post, country, mainphone, officephone, labphone, otherphone, fax, email, old_email, pis, lab, webpage.

To collapse click anywhere inside.

  • Color index : Verified YES Cecilia (blue), Verified YES Raymond (purple), Verified YES (green), Verified NO (red), Verified YES to Other possible (not shown)(brown), Connected not verified (pink), Connected to Other not verfied (olive green) Exact Match not connected to anyone (aquamarine), Last name Match (black).
  • matches - if many connections will show in batches of 10 (Paper Author Person Group)
    • first showing matches not verified
    • shows at the bottom list of exact matches to all names and akas and time to get this search results.

Tables for Person Lineage submission form

  • joinkey
  • two_sentname - single value, not editable.
  • two_othername - single value, not editable.
  • two_number - single value, editable. WBPerson# to connect to
  • two_role - multiple values, editable.
  • two_date1 - single value, editable. Beginning in the year.
  • two_date2 - single value, editable. Through the year.
  • two_sender -single value (email of sender or REV- email for reverse connection)

Table is a 2 way connection table, connects to sender and recipient who is the other person role connecting identified by REV-.

Note: to see all lineage submitions

Person Model

Revised and approved for sign off on the 16th June. 2011

?Person PostgreSQL_id UNIQUE Text // for linking to internal RDB 

                                       // Text instead of Int so it says WBperson####
       Name    First_name    UNIQUE  Text    // prefered first name
               Middle_name   Text            // middle name(s)
               Last_name     UNIQUE  ?Person_name XREF Last_name_of    // prefered last name
               Standard_name UNIQUE  ?Person_name XREF Standard_name_of   // name to be used for displays
                                             // default is "First_name Last_name"
               Full_name     UNIQUE  ?Person_name XREF Full_name_of // field for storing long name [030321 krb]
               Also_known_as   ?Person_name XREF Other_name_of // new XREF added [030310 krb]
       CGC_representative_for ?Laboratory XREF Representative
       Laboratory ?Laboratory XREF Registered_lab_members
       Affiliation Text
       Conducted ?Analysis XREF Conducted_by
       Address #Address
       Tracking        Old_laboratory ?Laboratory XREF Past_lab_members      // Lab(s) the person previously worked in
                       Last_verified UNIQUE DateType                         // Last time the name/contact information in this record was actively verified
                       Last_attempt_to_contact UNIQUE DateType UNIQUE Text   // Text is method of attempt to contact
                       Old_address DateType #Address   // for history
                       Left_the_field Text
                       History        Merged_into       UNIQUE ?Person XREF Acquires_merge
                                      Acquires_merge    ?Person XREF Merged_into
                       Status  Valid
                               Invalid
       Comment Text
       Lineage Supervised    ?Person XREF Supervised_by #Role
               Supervised_by ?Person XREF Supervised #Role
               Worked_with   ?Person XREF Worked_with #Role
       Publication     Paper ?Paper XREF Person #Evidence
                       Not_paper ?Paper XREF Not_person #Evidence
                       Publishes_as ?Author    // confirmed author names (curated) can't XREF because of paper model
                       Possibly_publishes_as ?Author XREF Possible_person
                                               // curated for Author->Paper->Person
                                               // connection but not yet verified by
                                               // the Person
  1. Address Street_address Text // multiple lines including town, state, zip/postcode, whatever
         Country UNIQUE Text
         Institution UNIQUE Text
         Email Text                 // multiple emails allowed
         Main_phone    Text         // phone number for the institution, I presume
         Lab_phone     Text
         Office_phone  Text
         Other_phone   Text UNIQUE Text  // second text can be used to indicate type
         Fax           Text
         Web_page      Text    // web page is part of the address info I think

Notes

  • update to Paper editor (2011-05-18)Updated to add the author_person priority flag, creating the table pap_curation_done and moving the genestudied_done to

pap_curation_done as 'genestudied'. in the Enter New Papers section : - the author-person select at the top with the box for free pmids has an author-person dropdown with values priority and not_priority which populate pap_curation_flags with 'author_person' - each pmid has an aut-per_priority dropdown that works the same way

I've updated the pap_match.pm to allow the extra flag

I'll now work on populating all the papers except for those with functional_annotation as 'author_person' priority in pap_curation_flags. Cecilia, this means that in the future Kimberly will always flag this for you and not set that for those that are functional annotation, so you don't need to think about functional annotation anymore, just whether it's flagged as priority or not.

  • sandbox, mangolasi is only for testing. Tazendra is Live. all Paper search in mangolassi are showing as:

pap_curation_done author_person in red bold letters Just for me to see if it is the way i'd like it to show. Yes, i like it, impossible to miss it.

  • 2011-06-03 Cecilia, I've moved the script to update lineage names to WBPerson#### to the postgres account to :

/home/postgres/work/pgpopulation/two_people/two_lineage/update_twos_in_two_lineage.pl You should add that to the list of scripts to update when we change the person tables. 2011-06-07 missed a line, re -ran output at /home/postgres/work/pgpopulation/two_people/two_lineage/

Notes Person editor

Going live June 16, 2011 after 2pm.

  • Connects to Postgres database named testdb.
  • normal tables @normal_tables : firstname middlename lastname standardname aka_firstname aka_middlename aka_lastname email old_email old_email_date street city state post country institution old_institution old_inst_date mainphone labphone officephone otherphone fax pis lab oldlab left_field unable_to_contact privacy webpage usefulwebpage wormbase_comment hide status mergedinto acqmerge comment.
    • Organization Tables Person (normal tables)
      • Table properties : type_input, order_type, min_rows
        • For type_input: (input -tries to autocomplete- and checkbox) all above tables and word 'number' have a type_input value = 'input', but usefulwebpage = 'checkbox'.
        • order_type: single value and multi value.
          • order_type: value = 'single' for tables "firstname middlename lastname standardname city state post country left_field unable_to_contact hide status mergedinto".
          • order_type: value = 'multi' for tables "street institution old_institution old_inst_date mainphone labphone officephone otherphone fax email old_email old_email_date pis lab oldlab privacy aka_firstname aka_middlename aka_lastname webpage usefulwebpage wormbase_comment acqmerge comment'.
        • min_rows: (minimum amount of rows), street = 4. All others = 1.
  • This form will show different pages depending what your click, pick, type, autocomplete loading from or to.
    • The first 3 pages I don't see. They will work in the background from javascripts actions.
    • &autocompleteXHR for tables that allow auto complete will query from Postgres.
    • &updatePostgresTableField: updates to Postgres updating from Person editor section after clicking away from input field/tab, or changing value in dropdown or checkbox.
    • &updatePostgresLineageData: updates to Postgres updating from lineage section after clicking away from input field/tab, or changing value in dropdown or checkbox.
    • These 6 pages are loaded visible in the browser, depending which one i pick (search paper, checkout papers, crate new person, search in display or search in edit mode. Front page (what you see when loading person editor), create people from xml. There are 6 pages, with this parameter (which_page) which the javascript file uses to load different actions (like autocomplete in one page, or adding extra institutions, or editing upon blurring a field). Focus - enter into cell, blur - getting out of the cell. When burring in the Person-editor edit mode it will update. RECAP: which_page tells javascript which actions to load for a specific page.
    • $frontpage = &firstPage by default.
    • &search(): Searches Person , display or edit mode.
    • &createNewPerson(): Creates new person
    • &searchPaper(): Searches for individual paper.
    • &checkoutPapers(): List of all papers not yet person curated. Will only display the highest 50 paper IDs
    • &personStatistics: List of Valid and Invalid Persons by month and year to keep track of how many new people enter the field.
    • &createPeopleFromXml(): DO NOT RELOAD. Extracts authors and institution data from PubMed xml, allowing to create multiple people with same institutions.

&autocompleteXHR

  • Less than 5 characters (including space) will give max 20 results, 5 or more = max 500 results.
    • Fields:
      • General: query for words that match in the corresponding table's corresponding column. The table and column are 'two_<field>'.
      • number: query table two_standardname, column 'joinkey'.
    • Postgres Query:
      • case insensitive: It will change the words I type into lower case by lowercasing query words and using LOWER column values in postgres query. First it will query for columns beginning with characters searched (cecil), if not enough results will query for characters anywhere and not at the beginning (ecili). After query is complete, if there are too many results, change the last result to say "more ..."
        • SELECT DISTINCT($column) FROM $table WHERE LOWER($column) ~ '^$words' ORDER BY $column;
        • for example ^cecil for searching for word beginning with: SELECT DISTINCT(two_firstname) FROM two_firstname WHERE LOWER(two_firstname) ~ '^cecil' ORDER BY two_firstname;
        • SELECT DISTINCT($column) FROM $table WHERE LOWER($column) ~ '$words' AND LOWER($column) !~ '^$words' ORDER BY $column;
        • ~ '$words' = anywhere, !~ '^$words' = not at the beginning.
        • example: SELECT DISTINCT(two_firstname) FROM two_firstname WHERE LOWER(two_firstname) ~ 'ecili' AND LOWER(two_firstname) !~ '^ecili' ORDER BY two_firstname;

&updatePostgresTableField

  • update tables for Person information section, edit mode. Deletes from data and history tables and inserts into data and history tables. Now I can delete data in Person-editor,no need to delete in psql. Only delete two in psql when two is empty, hit button to create a new two and no data entered there.
    • parameters: $field, $joinkey, $order, $newValue, $curator_two
      • $field = firstname, etc.
      • $joinkey = two#.
      • $order = two_order = 1, two_order = 2(value). Everything has two_order (two_order = '$order';) if not IS NULL.
      • $newValue = New data entered.
      • $curator_two = curator two1, two1823, etc
    • Postgres commands:
      • DELETE FROM two_$field WHERE joinkey = '$joinkey' AND two_order = '$order';
      • Example for text table: DELETE FROM two_email WHERE joinkey = 'two1' AND two_order = '1';
      • DELETE FROM h_two_$field WHERE joinkey = '$joinkey' AND two_order = '$order' AND two_timestamp > now() - interval '10 minutes';
      • Example for history table: DELETE FROM h_two_email WHERE joinkey = 'two1' AND two_order = '1' AND two_timestamp > now() - interval '10 minutes'; It will keep history only of data entered before 10 minutes ago, for cases of multiple editing same thing to not store unuseful history.
      • INSERT VALUES between single quotes '<newValue>', but if NO value them insert NULL (no quotes) will show blank, in entered 'NULL' will show NULL.
      • NO blank values editing in Person-editor, it will not add data to data tables and will put NULL in history tables.
      • Every time editing two tables, history tables are written.
      • INSERT INTO h_two_$field VALUES ('$joinkey', $order, $newValue, '$curator_two')";
      • If value is NOT NULL = INSERT INTO two_$field VALUES ('$joinkey', $order, $newValue, '$curator_two');

&updatePostgresLineageData

    • Columnas:sentname,othername, othertwo, role, daate1, date2, sender, timestamp.
      • Each column has a value for each row, except timestamp.
      • Value is the data that shows in the form, except for role, example. "I Trained as a PhD under" is withPhd. "I trained this person as a Postdoc" = Postdoc
      • Value can be blank if no data. It is dropdown in role, date1, date2.
    • When updating lineage, gets values of the columns for the row and DELETE data in the row in Postgres then INSERTS new data per cell change (click away from cell after typing/click a new value in the dropdown). Each time a cell changes enters a new row. Deletes the whole row and inserts a new row with old data not changed and new value in the column that changed.
    • Reverse role, Reverse sender.
      • Reverse role:
        • Only 'Collaborated' = Rev 'Collaborated'
        • Role 'withSomething' = Rev 'Something'
        • Role 'Something' = Rev 'withSomething'
      • Reverse sender:
        • 'REV - Something' = 'Something'
        • 'Something' = 'REV - Something'
    • Blank values are NULL in Postgress.
    • Deleting from postgres. Associates by joinkey and two_number, in this way it can be edited by any sender or sendee. Deletes both ways joinkey - othertwo, sentname - othername, othername - sentname, othertwo - joinkey, role - reverse_role, date1 - date1, date2 - date2, sender - reverse_sender.
      • The qualifiers are " = '<value>'" if there is a value, or " IS NULL" if there isn't.
        • "DELETE FROM two_lineage WHERE joinkey = $joinkey AND two_sentname $sentname_qual AND two_othername $othername_qual AND two_number $othertwo_qual AND two_date1 $date1_qual AND two_date2 $date2_qual AND two_role $role_qual AND two_sender $sender_qual";
      • "DELETE FROM two_lineage WHERE joinkey $othertwo_qual AND two_sentname $othername_qual AND two_othername $sentname_qual AND two_number = $joinkey AND two_date1 $date1_qual AND two_date2 $date2_qual AND two_role $reverse_role_qual AND two_sender $reverse_sender_qual";
    • INSERT: after deleting row inserts new values. (NULL where blank, same if no changes, and newValue for the chosen column that has a new value.)
      • "INSERT INTO two_lineage VALUES ( $joinkey, $sentname, $othername, $othertwo, $role, $date1, $date2, $sender )";
      • "INSERT INTO two_lineage VALUES ( $othertwo, $othername, $sentname, $joinkey, $reverse_role, $date1, $date2, $reverse_sender )";

&createNewPerson

  • Creates two# in tables 'two' and 'two_status' and re-directs to Person editor mode.
    • 1 gets highest joinkey
      • 'SELECT two FROM two ORDER BY two::INTEGER DESC'; gets the highest number integer (sorter by numbers) in descending order.
    • 2 add 1 to the highest number and add a 'two' in front of the new number to create the new joinkey (two#).
    • &addToPgcommandsTwoCreation($two_number, $curator_two, @pgcommands); (also use for create new people en xml section)
    • INSERT INTO two VALUES ('$joinkey', '$two_number'). Add new value to two table into postgres instead of doing it mannualy as before to create new two#.
      • &addToPgAndHst('status', $joinkey, '1', 'Valid', $curator_two, @pgcommands); (Pg= Postgres, Hst= History)
        • First value 'Field' = status, firstname, city, hyide, leftfield...etc).
        • second value 'joinkey' = two# for person
        • third value 'two_order' = 1, 2, 3...etc
        • fourth value 'value' = any data entered
        • fifth value 'curator_two' = two1 or Lito
        • if ($data) { if ($data =~ m/\'/) { $data =~ s/\'//g; } $data = "E'$data'"; } else { $data = 'NULL'; }. If no data gets NULL, otherwise put single quotes around it.
        • insert into datatables: "INSERT INTO two_$table VALUES ('$joinkey', $order, $data, '$curator_two')";
        • insert into historytables: "INSERT INTO h_two_$table VALUES ('$joinkey', $order, $data, '$curator_two')";
    • 3 'which_page' value is hidden.
      • print "<input type=\"hidden\" name=\"which_page\" id=\"which_page\" value=\"createNewPerson\">";
      • print "<input type=\"hidden\" name=\"redirect_to\" id=\"redirect_to\" value=\"$url\">"; tells javascript which page to re-direct to this url, so if hitting reload it does not keep crating new person without data besides two# and status.
      • my $url = "person_editor.cgi?curator_two=$curator_two&action=Search&display_or_edit=edit&input_number_1=$joinkey";

&search

  • When searching under a number will give a two# result, otherwise it will search for all fields with values, that will give results with two#s and links to Person-editor display mode.
    • when pressing search botton, first searches if there is a value in the 'field number'.
    • If you search by two# it will go to Person editor display mode page, if you search by any other way, it will go to search results page with links to Person editor display mode page. If the value has a number it will show it by doing '&displayPerson("two$1", $curator_two);', otherwise it will show 'not number' and will show possible matches to Persons.
    • which_page is searchResults.
    • if no number found, it will search input_<each normal table>_1(order only has value 1 in the first page). Will skip where field has no data.
    • Parameters in form to put in Variable in the cgi code: substring, case insensitive.
      • no parameter query = operator
      • substring parameter query ~ operator
      • case insensitive parameter query ~* operator
      • SELECT joinkey, two_$table FROM two_$table WHERE two_$table $operator '$data';
      • 1. Result stores by matches. two# and table. Example searching substring Bar (firstname)will store joinkey all two#s and all First names staring with Bar. Will search for joinkey (two#) and all the other tables queried. Will group by joinkey and sub-group of number of tables matched. Will query all the standard names of two that matched query.
      • 2. Result stores by tables. table, two#, data.
      • Results shown by the number of tables matched in descending order. For each two#:
        • Link to person-editor editor mode.
        • <a href=\"person_editor.cgi?curator_two=$curator_two&action=Search&display_or_edit=display&input_number_1=$joinkey\">$joinkey</a>
        • color brown for Standard name
        • will show all tables that match the two#, and the corresponding data in color green.

&displayPerson

Search for any two data (last name, institution, email...etc), can do multi searches. Display data or display the editor.

  • two modes, Display and Editor.
  • make_something_input and make_something box, and make_something dropdown will make cells, cells will make rows, rows will make the table, which is what the page shows.
  • for all normal_tables
    • cgi form tells Javascript what tables/fields to look at by passing a hidden input element with class='fields'.To enter updates (including new data) and autocomplete.
    • SELECT * FROM two_$table WHERE joinkey = '$joinkey' ORDER BY two_order
    • Look for data in two tables for the joinkey (two#).
    • Results will be stored in %pgdata (hash)by table and two_order these 4 different data : highest_order (what's why it's sorted by two_order, so it will store all the two_orders per table, example two_street with 4 lines will store all 4 instead of 1), data, row_curator, timestamp.
  • which_page for Person display is displayPersonDisplay
  • which_page for Person editor is displayPersonEditor
  • $toggle_url = "person_editor.cgi?curator_two=$curator_two&action=Search&display_or_edit=$opp_display_or_edit&input_number_1=$joinkey" (link will switch modes display - edit). Same thing repited below to have link to switch modes also above lineage part.
  • These 2 subroutines (&makeInputField and &makeCheckboxField) make html elements that have input Id = "input_${table}_$order" for the javascript to know which html elements to assign postgres updating functions. All inputs fields with an id that follows this pattern updates if which_page is displayPersonEditor, it can be text, checkbox. Important to remember if something is not updating correctly.
    • &makeInputField, will make a td cell with an input field with autocomplete (value in the input field is the data).
    • &makeCheckboxField makes an input checkbox.
  • This subroutine &makeDisplayField makes a cell with data and in the case of 'webpage' will create a link from the data.
  • for each normal_tables
    • tables skipped because it will already have been displayed as something else (($table eq 'middlename') || ($table eq 'lastname') || ($table eq 'aka_middlename') || ($table eq 'aka_lastname') || ($table eq 'old_inst_date') || ($table eq 'old_email_date') || ($table eq 'usefulwebpage') );
    • $highest_order. (two_order exist in Postgres, creating new order will be highest_order+1)
      • starts as 0
      • when data row is displayed (highest_order becomes two_order if two_order > highest_order)
      • when on edit mode (extra empty rows will show, if you create new order, example line 4 of street it will be highest order (3) +1 = order 4, when in Postgress it will became two_order = 4)
    • for each two_order, sorted numerically. 3 special cases (firstname and aka_firstname; old_email and old_institution have old_date; webpage has usefulwebpage) and 1 normal case (all others)
      • normal case :
        • $entry_data .= &makeSingleNormal(\%makeSingleNormal, $display_or_edit, $joinkey, $order, $table);
        • UPDATE August 30, 2011. change section on makeSingleNormal under
if ($display_or_edit eq 'edit') {
    ($td_one_data) = &makeInputField($one_data, $one_table, $joinkey, $order, '3', '1', , $td_width, $input_size);
    if ( ($bgcolor eq $blue) && ( ($one_table eq 'email') || ($one_table eq 'institution') ) ) { ($one_table) = &labelAddMoveButton($one_table, $order); } }  # if the table already existed and has a corresponding old table, add a button to move the data

So that if the $bgcolor is $blue and the table is either email or institution, add the ``move button by calling &labelAddMoveButton($one_table, $order)

All this does is : sub labelAddMoveButton { 

my ($table, $order) = @_;
$table .= " <button onclick=\"moveDataToOldTable('$table', '$order')\">move</button>";
return $table;

} # sub labelAddMoveButton

Note that this changes makeSingleNormal instead of making a whole new type for singleNormal that has buttons.

        • &makeSingleNormal (hace single normal row). Size 80. Background color white by default (empty), and blue color when there is existing data. It will show timestamp minus the milliseconds.
          • in edit mode: &makeInputField($one_data, $one_table, $joinkey, $order, '3', '1', , $td_width, $input_size);
          • in display mode: &makeDisplayField($one_data, $one_table, $joinkey, $order, '3', '1', , $td_width, $input_size);
        • $display_or_edit (display or edit mode)
        • $joinkey (two#, example two625)
        • $order (order1, 2...etc)
        • $table (example street)
      • 3 special cases:
        • &makeTripletHorizontal: Makes a row of data for 3 tables: first name => middlename, lastname and lable name (first, middle and last name). Size is 20. Background white color when empty and blue when there is data in the first table (firstname or aka_firstname)even if there is no data in middle name.It will show timestamp minus the milliseconds. In &makeDisplayField will display the 3 normal tables and in &makeInputField will edit the 3 normal tables. It will add timestamps to each of the 3 normal tables in each cell.
          • table eq 'firstname') {$entry_data .= &makeTripletHorizontal(\%pgdata, $display_or_edit, $joinkey, $order, $table, 'middlename', 'lastname', 'name');
          • elsif ($table eq 'aka_firstname') {$entry_data .= &makeTripletHorizontal(\%pgdata, $display_or_edit, $joinkey, $order, $table, 'aka_middlename', 'aka_lastname', 'aka_name');
        • &makeDoubleVertical, double data vertical for old email, old instituion, and webpage. Old-email and old _emaildate are 2 rows with 1 single order. Input size is 80. Background is white color when empty and blue color when the first cell has previous data. In edit mode will &makeInputField($one_data, $one_table, $joinkey, $order, '3', '1', , $td_width, $input_size);.In display mode it will &makeDisplayField($one_data, $one_table, $joinkey, $order, '3', '1', , $td_width, $input_size);.

UPDATE August 30, 2011 change section on makeDoubleVertical this now always looks for the entry with a 'white' $bgcolor, meaning that it has the order for the new row, and changes the $order to be : 

if ($bgcolor eq 'white') { $order .= "<input type=\"hidden\" id=\"highest_${one_table}_order\" value=\"$order\">\n"; }        # have an id for the next highest order of the old tables

So that the javascript can get the order to use for moving data to the new tables.

          • elsif ($table eq 'old_email') { $entry_data .= &makeDoubleVertical(\%pgdata, $display_or_edit, $joinkey, $order, $table, 'old_email_date', 'old_email');
          • elsif ($table eq 'old_institution') {$entry_data .= &makeDoubleVertical(\%pgdata, $display_or_edit, $joinkey, $order, $table, 'old_inst_date', 'old_institution');
        • &makeCheckboxInput: For webpages. Input size 80. Background white color when empty, and blue color when the first one (webpage) has data. usefulwebpage (to mark webpages with lab members data useful to me for curating Person). inputId = "input_${table}_$order".
          • elsif ($table eq 'webpage') {$entry_data .= &makeCheckboxInput(\%pgdata, $display_or_edit, $joinkey, $order, $table, 'usefulwebpage', 'webpage'); }
          • For display: ($td_one_data) = &makeDisplayField($one_data, $one_table, $joinkey, $order, '2', '1', , , $input_size); webpage table will take 2 slots in the row.
          • For display: ($td_two_data) = &makeDisplayField($two_data, $two_table, $joinkey, $order, '1', '1', , , $input_size); usefulwebpage table will take 1 slot in the row.
          • For edit mode:$display_or_edit eq 'edit') {($td_one_data) = &makeInputField($one_data, $one_table, $joinkey, $order, '3', '1', , $td_width, $input_size);
          • For edit mode:($two_checkbox) = &makeCheckboxField($two_data, $two_table, $joinkey, $order);
    • Only in edit mode show extra empty input rows if appropriate. This uses the same &make<whatever> subroutines as above in the foreach two_order section.
      • order_type single needs 1 row.
      • order_type multi need existing numbers of rows with data +1.
      • in the case of the field 'street' there is a minimum of 4, so show 4 or #rows+1, whichever is higher.
  • For each normal_tables, there are 2 hidden input fields :
    • value $highest_order with id highest_order_$table. For javascript to know how many rows to make autocomplete listeners and postgres update listeners for each field.
    • value $type_input{$table} with id type_input_$table. For javascript to know what type of data, text (autocomplete+blur) or checkbox (click).
  • * $toggle_url = "person_editor.cgi?curator_two=$curator_two&action=Search&display_or_edit=$opp_display_or_edit&input_number_1=$joinkey" (link will switch modes display - edit)[Same link as above @ $toggle_url, to have switch modes also at botom part of the page above lineage part. added : print "Switch to <a href=\"$toggle_url\">$opp_display_or_edit</a>.
    \n";August 31, 2011]
  • &makeLineageDisplay($joinkey, $display_or_edit, $header_bgcolor, $header_color);
    • parameters:
      • joinkey is two#
      • display or edit mode
      • header background color is grey
      • header font color is black
    • &populateLineageDropdowns: This fills the values for dropdown fields
      • date columns: date1 date2 (date 1 is starting year, date 2 is ending year. They will be in order: first, empty; second, present; third, this year; fourth-through last, going back until 1900)
      • role columns:
        • 'withPhd' = 'I trained as a PhD under';
        • 'withPostdoc' = 'I trained as a Postdoc under';
        • 'withMasters' = 'I trained as a Masters under';
        • 'withUndergrad' = 'I trained as an Undergrad under';
        • 'withHighschool' = 'I trained as a High School student under';
        • 'withSabbatical' = 'I trained for a Sabbatical under';
        • 'withLab_visitor' = 'I trained as a Lab Visitor under';
        • 'withResearch_staff' = 'I trained as a Research Staff under';
        • 'withAssistant_professor' = 'I trained as an Assistant Professor under';
        • 'withUnknown' = 'I trained as an Unknown under';
        • 'Phd' = 'I trained this person as a PhD';
        • 'Postdoc' = 'I trained this person as a Postdoc';
        • 'Masters' = 'I trained this person as a Masters';
        • 'Undergrad' = 'I trained this person as an Undergrad';
        • 'Highschool' = 'I trained this person as a High School student';
        • 'Sabbatical' = 'I trained this person during a Sabbatical';
        • 'Lab_visitor' = 'I trained this person as a Lab Visitor';
        • 'Research_staff' = 'I trained this person as a Research Staff';
        • 'Assistant_professor' = 'I trained this person as an Assistant Professor';
        • 'Unknown' = 'I trained this person as an Unknown';
        • 'Collaborated' = 'I collaborated with';
    • background color: existing data are blue. all new entries are white. boxes are white.
    • section description: Two#, Lineage.
    • headers of columns: sentmame, othername, othertwo, role, date1, date2, sender, timestamp.
    • "SELECT * FROM two_lineage WHERE joinkey = '$joinkey';" For each postgres row result:
      • timestamp: It will show timestamp minus the milliseconds
      • In edit mode: &makeLineageRowEdit($lineage_count, $bgcolor, $joinkey, $sentname, $othername, $othertwo, $role, $date1, $date2, $sender, $timestamp);
      • In display mode: print "
\n";
  • Edit mode: &makeLineageRowEdit($lineage_count, $bgcolor, $joinkey, , , , , , , , ); 5 times to make 5 extra lineage fields.
  • hidden inputs field for javascript to know what type of listeners to make, and how many, and for which:
    • print "<input type=\"hidden\" id=\"highest_order_lineage\" value=\"$lineage_count\">\n"; (highest_order_lineage/so javascript knows how many rows of lineage)
    • For the next the lineage_fields class is so javascript knows names of the columns
    • print "<input type=\"hidden\" class=\"lineage_fields\" value=\"sentname\">\n";
    • print "<input type=\"hidden\" class=\"lineage_fields\" value=\"othername\">\n";
    • print "<input type=\"hidden\" class=\"lineage_fields\" value=\"othertwo\">\n";
    • print "<input type=\"hidden\" class=\"lineage_fields\" value=\"role\">\n";
    • print "<input type=\"hidden\" class=\"lineage_fields\" value=\"date1\">\n";
    • print "<input type=\"hidden\" class=\"lineage_fields\" value=\"date2\">\n";
    • print "<input type=\"hidden\" class=\"lineage_fields\" value=\"sender\">\n";
    • For the next type_input_<something> fields are so javascript knows type of the column, values can be input (text box) or dropdown.
    • print "<input type=\"hidden\" id=\"type_input_sentname\" value=\"input\">\n";
    • print "<input type=\"hidden\" id=\"type_input_othername\" value=\"input\">\n";
    • print "<input type=\"hidden\" id=\"type_input_othertwo\" value=\"input\">\n";
    • print "<input type=\"hidden\" id=\"type_input_role\" value=\"dropdown\">\n";
    • print "<input type=\"hidden\" id=\"type_input_date1\" value=\"dropdown\">\n";
    • print "<input type=\"hidden\" id=\"type_input_date2\" value=\"dropdown\">\n";
    • print "<input type=\"hidden\" id=\"type_input_sender\" value=\"input\">\n";
  • makeLineageRowEdit: makes a row with different cells to edit lineage information:
    • $lineage_count, $bgcolor, $joinkey, $sentname, $othername, $othertwo, $role, $date1, $date2, $sender, $timestamp)
    • &makeLineageInput($lineage_count, $joinkey, $sentname, 'sentname');
    • &makeLineageInput($lineage_count, $joinkey, $othername, 'othername');
    • &makeLineageInput($lineage_count, $joinkey, $othertwo, 'othertwo');
    • &makeLineageDropdown($lineage_count, $joinkey, $role, 'role');
    • &makeLineageDropdown($lineage_count, $joinkey, $date1, 'date1');
    • &makeLineageDropdown($lineage_count, $joinkey, $date2, 'date2');
    • &makeLineageInput($lineage_count, $joinkey, $sender, 'sender');
  • These 2 subroutines (&makeLineageInput and &makeLineageDropdown) make html elements that have input Id = "cur_${column}_${lineage_count}\" for the javascript to know which html elements to assign postgres updating functions. All inputs fields with an id that follows this pattern updates if which_page is displayPersonEditor, it can be text, dropdown. Important to remember if something is not updating correctly. There is also a hidden html input element with the old value to keep the values of columns not updated with id = old_${column}_${lineage_count}
    • &makeLineageInput, will make a td cell with an input field for lineage (value in the input field is the data).
    • &makeLineageDropdown, will make a td cell with an input dropdown field for lineage.
      • &getAkaHash. Script run weekly and Person-editor are the same now. "If Person-editor is not connecting the sinle match, tell Juancarlos to fix it, it should work now July8, 2011"
      • F M L and under regular name
      • delete empty spaces beginning and end of the name
      • keep empty space between names
      • delete commas and periods
      • change all letters to lower cases
      • for middle name adds first initial
      • delete two_status Invalid
    • Por each Person each Last name Each name (including initial)
      • L F
      • F L
      • If middle name exist: algorithm
      • L F M
      • L FM
      • F M L
      • FM L

    &checkoutPapers

    There are 2 tables: pap_curation_flags and pap_curation_done. If the following problem occurs address to Juancarlos. If a paper is not showing when they should in the check out papers list and it is flagged in Paper-editor under table pap_curation_flags for author_person curation and it is not marked as author_person_done. If a paper is marked in pap_curation_done it shouldn't show in the list. If a paper is marked in those two fields or in neither shouldn't show in list.

    • which_page for checkoutPapers is checkoutPapers
    • $max_papers_to_show = 50;
    • SELECT joinkey FROM pap_curation_flags WHERE pap_curation_flags = 'author_person' AND joinkey NOT IN (SELECT joinkey FROM pap_curation_done WHERE pap_curation_done = 'author_person') ORDER BY joinkey::INTEGER DESC
      • Selects papers from check out papers section, by joinkey (paper id); from the pap_curation_flags where papers have been flagged for author_person (flags) and joinkey (paper#) is not already flagged as pap_curation_done done for author_person curation. Sorted by paper id in descending order.
    • for each row returned from the query:
      • keep track of number of results (number of papers)
      • show links until I have more than $max_papers_to_show from the query in the form.
      • link to Person_editor ( person_editor.cgi?curator_two=$curator_two&paper_id=$row[0]&action=Search+Paper )
      • print "There are " . scalar($count) . " papers, will only display the highest $max_papers_to_show paper IDs :
        (Shows the count of all papers not marked as person curation done that are also flagged for author_person_curation and shows a list of links for 50 papers.

    &searchPaper

    • Will look for the first block of numbers that you write in WBPaperID box. If you type no numbers results will say NOT a number.
    • &displayPaper(&padZeros($1), $curator_two); If you input numbers will display paper#.
      • 0002342 -> 2342 -> 00002342 Deletes the zeros in front of numbers,and replaces zeros in front to have 8 digit numbers. You can write the paper number without the zeros in front.
    • &displayPaper
      • which page is displayPaperXml.
      • 2 queries.
        • SELECT pap_curation_flags FROM pap_curation_flags WHERE joinkey = '$joinkey' ); (joinkey is WBPaper#).print font style bold, color red, size 14pt\>curation_flags : $curation_flags.
        • SELECT pap_curation_done FROM pap_curation_done WHERE joinkey = '$joinkey'); print bold style=\'color: red;font-size: 14pt\'>curation_done : $curation_done (font style bold, color red, size 14)
      • Results shows WBPaper#, next to it link to the paper editor
      • identifiers: SELECT * FROM pap_identifier WHERE joinkey = '$joinkey'" ); (joinkey is WBpaper#)
        • will show all identifiers in each own line. If there is a pmid identifier, will show pmid#######, it will get rid of the word pmid and show only the number in the link to pmid webpage. http://www.ncbi.nlm.nih.gov/pubmed/20559560
        • WormBase pdf link "SELECT pap_electronic_path FROM pap_electronic_path WHERE joinkey = '$joinkey' AND pap_electronic_path IS NOT NULL" ); adds link http://tazendra.caltech.edu/~acedb/daniel/ to the pdf link.
      • "SELECT pap_author FROM pap_author WHERE joinkey = '$joinkey' ORDER BY pap_order" ); (Will look for authors id in order for that paper)
        • Results will go to @aids and $aids.
          • for list @aids = (1 2 3 4)
          • for queries $aids = "'1', '2', '3', '4'" (author 1, author, author 3...etc. = aid 116743, 116744...etc)
          • tables = index possible sent verified. Index is the author name.
            • foreach my $table (@aut_tables) "SELECT * FROM pap_author_$table WHERE author_id IN ('$aids')".
            • for each table (index possible sent verified )will make a querie
            • "SELECT * FROM pap_author_index WHERE author_id IN ('116743','116744')" ); (allows multiple author number search at the same time)
            • SELECT * FROM pap_author_possible WHERE author_id IN ('116743','116744');
              • author_id | pap_author_possible | pap_join | pap_curator | pap_timestamp First column is zero, next one 1...etc.(pap_join is author join#, for when adding another possible to same author, because the other possible is not the author.
        • $pg_aid {$row[0]}{$table}{$row[2]} = $row[1];
        • Results will be stored in %pg_aid (hash) by (row[0] is author id), ($table is by 4 tables index possible sent verified) ( ({$row[2]} is pap_join {author join order 1, new possible, author join order2) = ($row[1] is the data pap_author_possible or pap_author_index, or sent or verified);
        • $ = How to get a value from % hush using the keys. hush used to store multivalues.
        • $pg_aid {$aid}{$table}{$join} = ceci ($pg_aid {$key}{$key}{$key} = value -resultado)= $pg_aid {15008}{verified}{2} = YES
      • To read pmid. Delete '/' only needed for Perl, not used in xml.
        • If there is not pmid: will print No PMID found for WBPaper$joinkey
          \n";
        • If there is pmid: will look the xml file. $xmlfile = '/home/postgres/work/pgpopulation/wpa_papers/pmid_downloads/done/' . $pmid; and will print "pmid $pmid xml found
          \n";
        • If there is no xml file: will print "NO XML for $pmid\n";
          • Affiliation data: <Affiliation> HERE data </Affiliation>. If this xml tag doesn't show in the xml, it will not show it in the xml form under affiliation. If the affiliations are showing else where, look at the xml file at tazendra, cd /home/postgres/work/pgpopulation/wpa_papers/pmid_downloads/done/ ,list (l), vim number of paper tazendra-..oads/done-6: vim 19725999
        • Results of looking at the xml will be stored in %xml_authors. %xml_authors = &getXmlAuthors($xmldata);
      • &getXmlAuthors: <AuthorWHATEVER_maybe_nothing>DATA</Author> case insensitive could be in multiple lines. Gets authors data from xml, maybe weird data as <Author ValidYN="Y">.
        • Captures all authors and gets Last name, ForeName, Initial. For each xml author will get:
          • my ($lastname) = $author_xml =~ /\<LastName\>(.+?)\<\/LastName\>/i; (i means case insensitive)
          • my ($initials) = $author_xml =~ /\<Initials\>(.+?)\<\/Initials\>/i;
          • my ($forename) = $author_xml =~ /\<ForeName\>(.+?)\<\/ForeName\>/i; (Forename is first name)
        • my $author = $lastname . " " . $initials; according to Kimberly an author is last name + empty + initial. In pap_author_index.
        • Results will be stored in %xml_authors bu author and last name, fist name, initial (first initial),standard name (forename and last name)
          • $xml_authors{$author}{lastname} = $lastname;
          • $xml_authors{$author}{firstname} = $forename;
          • $xml_authors{$author}{initials} = $initials;
          • $xml_authors{$author}{standardname} = "$forename $lastname"; }
      • hidden values: curator_two and paper_joinkey, max_instution. To print data for the form to see it.
      • $max_institutions = 100; hidden value.(tell Juancarlos later if you want to increase the number of maximun institutions, Feb 3, 2012 increased from 40 to 100).
      • @affil_tables = ( institution street city state post country );
        • for my $i (1 .. $max_institutions) {for each institution from 1 to 100 maximun}
        • &showInstitutionEditor($i, $curator_two, \@affil_tables, $affiliation, $max_institutions);
          • $i = Shows to Institution# that you are working on. 1 to 100 max.
          • $curator_two = curator doing curation, two1
          • \@affil_tables = affiliation tables ( institution street city state post country )
          • $affiliation (The affiliation data from xml, they show only 1, I have to get other affiliations from pmid)
      • InstituionEditor.
        • If instituion# ($i) is less than maximun instituion it will show a link to show another institution #. if ($i < $max_institutions) {# link to show another institution if it's not the last one (last cannot show anything else)
          • my $html_table_id = $i + 1; $html_table_id = 'table_' . $html_table_id;
              • my $html_table_id = $i + 1 (is the isntsitution # plus 1)
              • $html_table_id = 'table_' . $html_table_id; (table_1, table_2...etc are the names of the tables, means whole data under Instituion 1, same for institution2...etc)
        • $show_another_institution = "<a href=\"#\" onclick=\"document.getElementById('$html_table_id').style.display = ; return false\">show another institution</a>"; }(When you click will show the instituion table # +1)
        • If institution# is higher than 1, shows a link to hide this institution. if ($i > 1) {# link to hide this institution if it's not the first one (first must always be there, it will never hide Institution #1)
          • my $html_table_id = 'table_' . $i;
          • $hide_this_institution = "<a href=\"#\" onclick=\"document.getElementById('$html_table_id').style.display = 'none'; return false\">hide this institution</a>"; } (When you click 'hide this institution' will display NONE, it will hide it)
          • By default only shows 1 institution. It will never hide Institution #1
        • will print Inst#, curator#, link to show another institution or hide this institution.
          • $show institutions with data only. Max 100. It will show institutions only if they have something in the Institution field (not street, etc.). It will also hide institutions that don't have data. It will never hide Institution #1.
        • For Institution table, Inst#1: Only Inst#1 will have pre-populated with affiliation data.
        • For each affiliation table ( institution street city state post country )will:
          • If it is not street $order = $1 (means that in all others but street, i (instituion#1 will have order 1 for institution, state, city, post and country, instituion#2 will have input_institution_2, state2, city2...etc )
          • For street order, the order will be 4 lines per inst# even if they are blanks. For Inst #2 will be order 5 to 8 and so on.
          • my $table_row_autocomplete = &showEditorText($table, $order, $input_size, $colspan, $value);
          • print "
    $table_row_autocomplete\n"; } }
    • &showEditorText: will have 1 cell on the left side of the form, that is the label, name of the field, with and id lable_$table; and teh cell of the right side of the form has a lot of different html elements to make the field with autocomplete. input id=\"$inputId\" name=\"$inputId\" size=\"$input_size\" value=\"$value\">\n", this input field is important because is where you enter the data.
      • td = cell
      • $inputId = "input_${table}_$order"; (input_institution_1, means institution #1 or #2...etc whichever you are using)
  • For all affiliations_tables
    • cgi forms tells Javascript what tables/fields to look at by passing a hidden input element with class='fields' To autocomplete.
    • Will print "<input type=\"hidden\" class=\"fields\" value=\"$table\" \/>\n";
    • print "<input type=\"hidden\" id=\"type_input_$table\" value=\"$type_input{$table}\">\n"; (value $type_input{$table} with id type_input_$table. For javascript to know what type of data, text, (autocomplete)
    • print "<input type=\"hidden\" id=\"highest_order_$table\" value=\"$highest_order_table\">\n"; }(value $highest_order with id highest_order_$table. For javascript to know how many rows to make autocomplete listeners)What it means is the highest order for each field but street will be max institutions, and for street 4 x max institution.
  • &getAkaHash. Script run weekly and Person-editor are the same now. "If Person-editor is not connecting the sinle match, tell Juancarlos to fix it, it should work now July8, 2011"
    • F M L and under regular name
    • delete empty spaces beginning and end of the name
    • keep empty space between names
    • delete commas and periods
    • change all letters to lower cases
    • for middle name adds first initial
    • delete two_status Invalid
    • Por each Person each Last name Each name (including initial) is stored in the %aka_hash like $aka_hash{possible name combination}{two#}
      • L F
      • F L
      • If middle name exist: algorithm
      • L F M
      • L FM
      • F M L
      • FM L
    • how aka gets stored:
  • table (Person/author editor part, create new, shows two existing data) has columns (aid, author, possible, sent, verified, standard name, firstname, middlename, lstname, email, inst, old institution)
    • To display the table. For each author id will get aid, $aname = $pg_aid {$aid} {index}{'1'}.(aid is author id, aname is author name, $pg_aid is postgres author id, index (table)is the list of author in the specific paper, '1' is the author order, author order is always 1, but possible can be 1, or 2 or more until i connenct it to the real author. bgcolor blue. background color is blue, if it is already verified changes to grey color.
    • hidden numbering author information, starts with 0, 1, 2, 3...etc. hidden the numbering value so it won't be change by mistake. aid_$i(author 0, 1, 2, 3 etc. total number of authors in the paper) type: hidden (can be show or hidden, checkbox), value (resultado) = $aid (author id).
    • Process names to match against the aka hash. Gets authors names from aname (see few lines above), them take out commas and dots, replace underscores for spaces.
    • For possible, sent and verified will get data from $pg_aid and show it in the table cells.
      • $hightest_join. will keep track of the highest join for each author_id to make a new one.
      • $table eq'verified' if YES means already verified and will show in color grey.
      • $table eq'possible' will show a link to person_editor.cgi?action=Search&input_number_1=$twonum&curator_two=$curator_two
    • Match against %xml_authors. is the xml has a author name (aname)will get lastname, firstname and standard name. will say the xml author was found putting it in the %xml_authors_found (hash).
    • print by cells (td). First row is value id _0 for all the columns, ejemplo aid_0, standard name_0, middlename_0, second row will be _1, aid_1, standardname_1, email_1, ins_choice_$1, old_inst_choice_$1, etc.
      • Print hidden highest join. input type=hidden name=highest_join_$i
      • 'aid' 1st cell Print author aid (131888)author name. author 0, 1, 2, 3 etc. total number of authors in the paper) type: hidden, value (resultado) = $aid (author id).
      • 'author' 2nd cell Print author name.
      • 'possible' 3rd cell Print all possible two
      • 'sent' 4th cell Print Sent if email was sent or blank if not.
      • 'verified' 5th cell Print if the paper was verified or blank if not.
      • 'standard name' - data comes from xml_authors hash
      • 'firstname' - data comes from xml_authors hash
      • 'middlename'- blank from xl
      • 'lastname' - data comes from xml_authors hash
      • 'email' - blank from xml
      • 'inst' - blank from xml. select dropdawn, values are blank, Inst1, Inst2... max (inst100)
      • 'old inst' - blank from xml. select dropdawn, values are blank, Inst1, Inst2... max (inst100)
        • line 2. &matchFullnameToAka: standard name = full name, first name = fore name, last name = last name.

    • aka_hash is in lowercase. Will transform capital letters of full name to lower cases.

          • if the fullname is in the aka hash.
      • Por each Person each Last name Each name (including initial) is stored in the %aka_hash like $aka_hash{possible name combination}{two#}. For each author standard_name will compare against all aka possible combinations from the aka hash giving a result of two#s, will show a link to person_editor.cgi?action=Search&input_number_1=$twonum&curator_two=$curator_two. Samething repeated to all authors.
        • L F
        • F L
        • If middle name exist: algorithm
        • L F M
        • L FM
        • F M L
        • FM L
    • hidden input: aids_to_check values is how many authors id are in the paper.
    • if curation_done button of 'Create people fom XML' won't show.
    • if the author from the paper do not match with the xml (kimberly or I changed name with no foreign characters)it will say here 'xml author $aname has no match in paper Firstname: $firstname; Lastname: $lastname; Initials : $initials.


    &personStatistics

    Statistics on person curation by year-month, to keep track of how many new people enter the field. Juancarlos has added a button "Person Statistics" that looks at the h_two_status(history table for status) and groups by year-month and status Valid /Invalid. The first column is the year-month. Second are how many have a 'Valid' value in that time range. Third are how many have an 'Invalid' value in that time range. Fourth are 2nd minus 3rd (total valid person increase). Fifth, Sixth, Seventh the total from the beginning of time up to that year-month.

    &createPeopleFromXml

    • get paper joinkey from cgi. Showing 'Creating persons from authors in WBPaperxxxxxxx' and a link to paper editor http://tazendra.caltech.edu/~postgres/cgi-bin/paper_editor.cgi?curator_id=$curator_two&action=Search&data_number=$paper_joinkey
    • link to previous paper: $prev_url = "person_editor.cgi?curator_two=$curator_two&paper_id=$prev_paper_joinkey&action=Search+Paper";
    • link to next paper: my $next_paper_joinkey = $paper_joinkey + 1; my $next_url = "person_editor.cgi?curator_two=$curator_two&paper_id=$next_paper_joinkey&action=Search+Paper";
    • checkout_page: my $checkout_url = "person_editor.cgi?curator_two=$curator_two&paper_id=&action=Checkout+Papers"; "<a href="$checkout_url">checkout page</a>.";
    • write hidden inputs :
      • paper_joinkey = $paper_joinkey
      • which_page = createPeopleFromXml
    • get max instituions
    • aids_to_check
    • for each of $max_institutions, for each of the tables (institution, street, city, state, post, country)will get data when pressing 'Create People from xml' buton.
      • will store data $inst{$1 - 40}{table} {$j (for street means 1-4, and all others 1}= $data. Example Instituion1, street 1,2,3, 4 (even if blank), state1, city1, post1, country1. Institution2, street 5, 6, 7. 8, city2, post 2, state2, country2...etc.
    • @two_fields: old_inst_choice inst_choice aid highest_join standardname firstname middlename lastname email
    • @two_tables: standardname firstname middlename lastname email.
    • $two_number = &gethighestJoinkey: "SELECT two FROM two ORDER BY two::INTEGER DESC". Will get the highest two#.
    • for each of the author rows ($i) (0 - $aid_to-check amount of author of papel, starting with 0 = row 0):
      • for each table of @two_fields (old_inst_choice inst_choice aid highest_join standardname firstname middlename lastname email ) will store date like this: $aids{$i (raw# 0 - #of authors in the paper)}{table (old instituition choice, standard name...etc}{1 (always is 1)}= $data.
      • After getting all the data from the form, gets author id $aid = $aids{$i(es el row)}{aid}{1}= $data
      • $pap_join = is the join per author/Person possible, example author id (aid 13194)join 1, if it is not the author, I assign another possible join 2.
      • $pap_join = 1; if ($aids{$i}{highest_join}{1}) { $pap_join = $aids{$i}{highest_join}{1} + 1; } The pap_join is 1, but if there is already 1 (row), the pap_join will be the highest_join + 1.
      • $aid_error_message. If I don't choose and institution or last name will give you an error. 1.- error No last name -> $aid_error_message .= No lastname chosen for AID $aid. 2.- No inst_choice -> $aid_error_message .= No institution nor old institution chosen for AID $aid.
      • If the institution chosen doesn't have a value (data) for institution field 'institution' under Inst1, or Inst2 (which is Institution number or Institution id) will give an error.
        • $inst_id = $aids{$i}{inst_choice}{1}; If there is an institution choice, will get it.
        • unless ($inst{$inst_id}{institution}{1}) { $inst_error_message .= "ERROR : Institution $inst_id chosen for Inst, but it has no institution data>n"; } }
      • $old_inst_id = $aids{$i}{old_inst_choice}{1}; Same thing for old institution, if old_inst_choice was selected and it doesn't have a value (data) for old institution field, it will print an error message. ERROR : Institution $old_inst_id chosen for old Inst, but it has no institution data.
      • $two_number++; after getting the highest joinkey, it will get a two#.
      • @pgcommands = &addToPgcommandsTwoCreation($two_number, $curator_two, @pgcommands);
        • see under section above &createNewPerson (&addToPgcommandsTwoCreation($two_number, $curator_two, @pgcommands);)
      • foreach two_tables (standardname firstname middlename lastname email) will get data for %aids hash ($aids, author id) and put it into Postgres and History.
        • my $data = $aids{$i}{$table}{$order};
        • (@pgcommands) = &addToPgAndHst($table, $joinkey, $order, $data, $curator_two, @pgcommands);
        • see under section above &createNewPerson.
      • foreach my $table (@inst_tables)For each existing Institution table, will look for data in institution hash to add to Postgres and History tables.
        • (@pgcommands) = &addToPgAndHst($table, $joinkey, $order, $inst{$inst_id}{$table}{$order}, $curator_two, @pgcommands);
      • if ($old_inst_id)old institution data. If there is an old_institution it will go under old_institution tagg in Postgres. Then it will get an old institution date.
        • my $table = 'old_institution'; my $order = 1; (only get the first institution)
        • @pgcommands) = &addToPgAndHst($table, $joinkey, $order, $inst{$old_inst_id}{'institution'}{$order}, $curator_two, @pgcommands);
        • print "$table : $order : $inst{$old_inst_id}{'institution'}{$order};
        • my $date = &getPgDate(); $table = 'old_inst_date'; # use current date for old institution date (confirmed by Cecilia)
        • @pgcommands) = &addToPgAndHst($table, $joinkey, $order, $date, $curator_two, @pgcommands);
        • print "$table : $order : $date
      • $edit_two_url = 'person_editor.cgi?curator_two=$curator_two&action=Search&display_or_edit=edit&input_number_1=$joinkey'; # add link to person_editor in edit mode.
    • pap_curation_done: If pap_curation is done, does nothing. If it is not done it will select the highest pap_curation_done (there are for author_person, genestudied and gocuration in the same dropdown Paper-editor)
      • my $is_curation_done = 0; "SELECT pap_curation_done FROM pap_curation_done WHERE pap_curation_done = 'author_person' AND joinkey = '$paper_joinkey";
      • push @pgcommands, "INSERT INTO pap_curation_done VALUES ('$paper_joinkey', 'author_person', '$order', '$curator_two')";
      • push @pgcommands, "INSERT INTO h_pap_curation_done VALUES ('$paper_joinkey', 'author_person', '$order', '$curator_two')";} # unless ($is_curation_done)
    • print and execute all pg commands
      • foreach my $pgcommand (@pgcommands) {print "$pgcommands";
      • $dbh->do( $pgcommand );} # foreach my $pgcommand (@pgcommands)
      • #($isOk) = &updatePostgresByTableJoinkeyNewvalue($field, $joinkey, $order, $newValue, $curator_two);
    • print "<input type=\"hidden\" name=\"max_institutions\" value=\"$max_institutions\">\n"; Will print also max institutions, maybe not really needed.

    &firstPage

    • which_page = firstPage (Is the front page of the Person-editor form)
    • &populateCurators. From drop down at 'Select your Name :' can select from @curator_list = ('two1823', 'two1');
    • 1 # display curators in alphabetical (array) order, if IP matches existing ip record, select it. The form selects it at opening the form.
    • 2 Date : $date -> print current date top right corner of the form
    • 3 WBPaperID: input id= paper_id name= paper_id size=10 value=' '><input type=submit name=action value= Search Paper>
    • 4 Search Paper
    • 5 Checkout Papers
    • 6 Create New Person
    • 7 Search
    • 7.1 Display (input type= radio name= display_or_edit value= display $display_checked >display)
    • 7.2 Edit (input type= radio name= display_or_edit value=edit $edit_checked >edit)
    • foreach my $table ("number", @normal_tables) For each normal table show &showEditorText
      • Refer to &searchPaper section for explanation of &showEditorText. Shows the data in the fields with autocomplete but will not save it.
      • input type= hidden class= fields value= $table
      • input type= hidden id= type_input_$table value= $type_input{$table}
      • input type= hidden id= highest_order_$table value= 1 (Value always 1 for all fields including street)
        • cgi form tells Javascript what tables/fields to look at by passing a hidden input element with class='fields'.To show data with autocomplete.
      • will print 2 check boxes: substring_$table and case_$table
        • input type= checkbox value= on name= substring_$table >substring
        • input type= checkbox value= on name= case_$table >case insensitive (automatic substring)<
        • table two (where you input two# for searching) and two_status have no checkboxes for substring and case insensitive (automatic substring).


    PERSON dumper

    • log into acedb: ssh acedb@tazendra.caltech.edu (cd /home/acedb/cecilia/citace_upload)
      • ./get_pap_person_ace.pl > persons.ace (tazendra-..e_upload-30: ./get_pap_person_ace.pl > persons.ace)
      • tazendra-..e_upload-32: cp persons.ace persons.ace2011July4 (copy result file, changing date in each upload)
    • $error_file = 'errors_in_pap_person.ace';
      • tazendra-..ce_upload-8: vim errors_in_pap_person.ace
      • If error, correct them and re-do el dump,do not rename el file.
        • DELETE FROM two_status WHERE joinkey = 'two11214';
        • DELETE FROM two WHERE joinkey = 'two11214';
      • test dump tazendra-..e_upload-30: ./test_read.sh

    Si hay error, vuelvelo a hacer y no cambies el nombre del file, se escribe encima.

      • c) Hacer spc a Wen tazendra-..e_upload-22: scp /home/acedb/public_html/cecilia/persons/persons.ace

    citace@spica.caltech.edu:Data_for_citace/Data_from_Cecilia/(en una sola linea los2 lineas de arriba)citace@spica.caltech.edu's password:

    • @normal_tables = qw(firstname middlename lastname standardname aka_firstname aka_middlename aka_lastname email old_email old_email_date street city state post country institution old_institution old_inst_date mainphone labphone officephone otherphone fax pis lab oldlab left_field unable_to_contact privacy webpage usefulwebpage wormbase_comment hide status mergedinto acqmerge comment );
    • Qualifiers:
      • $qualifier_paper_hash
      • $qualifier_general
        • my $specific_two_num = 'all'; $specific_two_num = 'two1';$specific_two_num = 'two6792'; (when all is selected will get all two#s, and when not a specific two number has to be selected.
        • $qualifier_paper_hash = " AND pap_author_possible.pap_author_possible = '$specific_two_num'";(pap_author_possible has to match the two#)
        • $qualifier_general = " AND joinkey = '$specific_two_num'"; }
    • &populatePaperHash paperHash Will read all data from paper tables and put it into %paperHash.
      • %paper_valid; my %paper_index
      • SELECT * FROM pap_author ORDER BY pap_timestamp (Will select all authors from pap_author table. Mapping author_id to papers)
      • while (my @row = $result->fetchrow) { $paper_index{$row[1]} = "WBPaper$row[0]"; }
        • row1 = author id; WBPaper$row = paper id.
      • %author_valid; my %author_index;
      • SELECT * FROM pap_author_index ORDER BY pap_timestamp (Will select all authors id and authors name getting rid of space and comments from author_index hash. Mapping author_id to names)
        • $author_index{$row[0]} = $row[1]; } ({$row[0]} es author id); ($row[1] = author name).
      • SELECT pap_author_possible.author_id, pap_author_possible.pap_author_possible, pap_author_possible.pap_join , pap_author_verified.pap_author_verified, pap_author_verified.pap_timestamp (Will select all these paps tables and columns, ejemplo 'pap_author_possible.author_id' 'pap_author_possible (table)', after period is column, 'author_id (column)'. FROM pap_author_possible, pap_author_verified, Looking for 'pap_author_verified.author_id' and 'pap_author_verified.pap_join' AND 'pap_author_verified.pap_author_verified' is YES OR NO (meaning NO blank verified)
      • $qualifier_paper_hash (If picked 'all' at $qualifier_paper_hash, the qualifier is blank, NO qualifier)
      • $person = $row[1]; my $author_id = $row[0]; my $verified = $row[3]; my $paper = ; my $author_name = ;
      • (person = row 2, author_id = row1 , verified = row 4, paper = ?, author_name = ?)
      • (Selects all author from pap_author tables, giving authors id to papers. Then selects authors id (stripping extra spaces and comments) mapping author id to names. Then selects possible and verified looking for verified ids and Yes or No)
      • if ($verified =~ m/NO/) If paper is verified NO. If there is a dot in the paper, meaning erratum or paper inside a paper it will not delete it. It will delete it from paperHash the Person-Paper connections for these 3: paper, paper_lab, paper_lineage (verified by script by labs and lineage). And add it to not_paper connection.(not_paper is for Cecilia to keep track of papers verified as Not mine)
        • delete $paperHash{$person}{paper}{$paper}; delete $paperHash{$person}{paper_lab}{$paper}; delete $paperHash{$person}{paper_lineage}{$paper};$paperHash{$person}{not_paper}{$paper}++;
      • if verified YES. Only if there are $paper_index and $author_id. $paperHash{$person}{paper}{$paper}. In paper hash (directory) if a specific Person has been verified Yes (verification of the Person connection) to a specific paper, it will connect it here.
        • If not verified by author_id ne (no equal) 'two363' (Raymond, by script).
          • with 2 empty spaces between YES and Raymond Lee: YES__Raymond Lee = verified by lab script ($paperHash{$person}{paper_lab}{$paper}).
          • with 1 empty space between YES and Raymond Lee: YES_Raymond Lee = verified by lineage script ($paperHash{$person}{paper_lineage}{$paper}).

    IMPORTANT NOTES

    • Subject: hide / privacy / unsubscribe
    • hide : whole entry doesn't dump to .ace for citace upload doesn't show anything
    • privacy : email doesn't show in .ace for citace upload might be used for paper connection emails ?
    • unsubscribe : NEW show in .ace, don't send emails for newsletters
      • To remove from database: use hide
      • To unsubscribe from letters:
        • testdb=> INSERT INTO two_unsubscribe VALUES ('two15191', 'unsubscribe');
        • testdb=> SELECT * FROM two_unsubscribe;
      • To remove unsubscribe:
        • testdb=> DELETE FROM two_unsubscribe WHERE joinkey = 'two15191';
    • Do not reload from page after creating new people from xml.
    • After testing that middle names are ok in new Person, delete two_middlename WHERE middlename = apsotrofe apostrofe;
    • UPDATE January 22, 2014

    Paper_editor - Person Author Curation http://tazendra.caltech.edu/~postgres/cgi-bin/paper_editor.cgi

    Papers flagged for non_nematode and author_person at the same time, were not showing for Person curation. we changed that query from 

     SELECT * FROM pap_author WHERE pap_author IN ('$author_ids') AND joinkey NOT IN (SELECT joinkey FROM pap_curation_flags WHERE pap_curation_flags = 'non_nematode')

    to 

     SELECT * FROM pap_author WHERE pap_author IN ('$author_ids') AND joinkey IN (SELECT joinkey FROM pap_curation_flags WHERE pap_curation_flags = 'author_person')

    meaning instead of looking for not having non_nematode, now it must have author_person under pap_curation_flags.


    • UPDATE March 6, 2013

    1.- two_hide changes because Paul Davis: Mysterious People In WormBase. That's an interesting point especially as their WBPerson IDs exist associated with data in other objects such as papers.

    ftp://ftp.sanger.ac.uk/pub2/wormbase/STAFF/Mysterious_People.dat

    I think it would be better to remove/hide their contact data but leave a stub object that at lease contains their name??

    Cecilia.

    The "Valid" WBPerson IDs might be valid, but they are empty in the citace upload meaning that they are referenced by other classes (Author) meaning that the ?Person data and ?Author data are out of sync in the WS237 upload.

      • dump was printing nothing if tag two_hide.
      • changed to always show Object (WBPerson#)and status.
      • if status invalid: checks for merge.
      • if status valid: checks for two_hide.
      • if two_hide: prints only status and standard name and comment 'This Person requested not to be contacted'.

    2.- Old_institution tag. Not possible to stop dumping dates in old_institution date as suggested by Raymond because: We can't stop putting that in acedb because the model says : 

     Old_address DateType #Address   // for history

    which makes sense since we have to differentiate the data from different #Address hashes

    Problem: Old_institution tag with same date was dumping only one in acedb/citace, ergo showing only 1 in tree-display in WB website. Solution: Changed dates in Person-editor, so each shows a different date. I have added dates in same line of Old_institution for Person who requested it, see two906. Website: Requested to not show dates in old_institution since we don't really know when people started nor ended at each institution.


    • UPDATE February 3, 2012

    Added 'show institutions with data only' in section Person editor: display mode, edit mode/Functionality/Inst1 - click to show another institution - hide another institution. It will show all institutions created in the form if I hit back to continue editing same paper with over 40 authors. Delete Curation Flags/curation_done/author_person from Paper editor to continue editing same paper. Added total 100 institutions to 40 original number.

      • It will show institutions only if they have something in the Institution field (not street, etc.)
      • It will also hide institutions that don't have data.
      • It will never hide Institution #1.
    • Email asking to verify papers

    /home/postgres/work/pgpopulation/pap_papers/author_person/email_connected_authors.pl

    • August 29, 2012. Paper editor autocomplete was showing invalid Persons. FIXED. j chan: I changed the ontology_annotator.cgi getAnyWBPersonAutocomplete so that it finds all two_status = 'Invalid'

    and when it find a person ID to autocomplete on, it add 'INVALID' after the name, before the " ( WBPerson#### ) "


    July7, 2011 changed text Kimberly request.

    • Changed text (date) of automatic letter after people verify papers(Octubre 5, 2011)

    /home/azurebrd/public_html/cgi-bin/forms/confirm_paper.cgi

    • 2001-06-06 Person editor in tazendra (development database)

    http://tazendra.caltech.edu/~postgres/cgi-bin/cecilia/person_editor.cgi

    We don't have the mangolassi sandbox, so I've made a development database, separate from the database with real data (devdb for developtment vs. testdb for real data), and copied the person_editor.cgi to tazendra, to access the development database. So you can test it, and it should work just like it used to, and you can mess with the data. Don't forget that any links to the paper editor and so forth are real links and data in those forms will make real changes.

    • 2001-06-07 Person_lineage:

    http://tazendra.caltech.edu/~azurebrd/cgi-bin/data/person_lineage.ace aqui se ven todos los lineage mandados

    /home/postgres/work/pgpopulation/two_people/two_lineage/update_twos_in_two_lineage.pl output files /home/postgres/work/pgpopulation/two_people/two_lineage/

    cd /home/postgres/work/pgpopulation/two_people/two_lineage/ ls lineage_psql_commands not_found update_twos_in_two_lineage.pl*

    vim lineage_psql_commands : ve los datos, como los ve, que pone y que no pone. vim not-found : lista de los que no encuentra vim update_twos_in_two_lineage.pl* : es el programa

    • SELECT * FROM pap_author WHERE pap_author = '98640';

    To search for Paper by author id.

    • Invalid paper - To delete possible, sent and verified when invaliding a paper.
      • Paper editor can invalid verified by clicking in verified box, will mark Yes first, click again will mark it NOT mine, click 3rd time and will mark it empty.
      • sent is not editable, use psql to delete data.
      • SELECT * FROM pap_author_possible WHERE author_id = '80358';
      • SELECT * FROM pap_author_sent WHERE author_id = '80358';
      • SELECT * FROM pap_author_verified WHERE author_id = '80358';
      • DELETE FROM pap_author_possible WHERE author_id = '80358';
      • DELETE FROM pap_author_sent WHERE author_id = '80358';
      • DELETE FROM pap_author_verified WHERE author_id = '80358';
    %s/80352/80353/g
    • This is to move the data from the old tables to the new tables with history.
      • /home/postgres/work/pgpopulation/two_people/20110515_person_retable/recreate_tables.p

    aka

    1. http://wiki.wormbase.org/index.php/Author_Person (script upgraded 2011 for Person-editor)
    2. http://tazendra.caltech.edu/~postgres/cgi-bin/paper_editor.cgi?; (From Paper-editor, Person Author Curation section)
    3. http://tazendra.caltech.edu/~azurebrd/cgi-bin/forms/confirm_paper.cg (Confirm paper form, NOT in use anymore)
    4. Asociar a single match: tazendra-..e/cecilia-3: cd UPLOAD/new-upload; ./connect_single_match_authors_and_get_histogram.pl (prgrma que corro semanalmente para asociar a single match, them i ran another program to verify by lab/lineage, monthly a sent emails for paper verification and ran upload as requested montly or bi-monthly.
    • 1.- Script upgrade to catch more possible connections
      • F M L and under regular name
      • delete empty spaces beginning and end of the name
      • keep empty space between names
      • delete commas and periods
      • change all letters to lower cases
      • for middle name adds first initial
      • delete two_status Invalid
    • Por each Person each Last name Each name (including initial)
      • L F
      • F L
      • If middle name exist: algorithm
      • L F M
      • L FM
      • F M L
      • FM L
    • - for xml test program
      • 1. akas lower case
      • 2. - For each author of each paper not verified YES search:
                  Paper   auhor_id    Author_name
        - look for pap_identifier to get pmid/read XML
        - open

    b.- Pubmed xml 

            xtract:  <LastName>Pierce</LastName>
                   <ForeName>Sarah B</ForeName>
                   <Initials>SB</Initials>
        Construct exact name:
        two_firstname = ForeName
        two_middlename = blank
        two_lastname = LastName
        two_standardname = Forename + LastName
        two_aka_firstname = Initials
        two_aka_middlename = NULL
        two_aka_lastname = Forename

                      <Affiliation>Departments of Medicine (Medical

    Genetics), Genome Sciences, Biological Structure, and Biochemistry, University of Washington, Seattle, WA 98195.</Affiliation>

      • running a comparacion between the one I ran weekly and the one in peson-editor, showed that the later is connecting to valid and invalid Person, Lito is fixing it to that both restrict it only to Valid Person. Got rid of invalid two section

    July 13, 2011 finished documenting Person editor. Next step will be documenting uploads.

    September 1, 2011 Updates to Person editor

    • 1.- Add 'Switch to display' link to Person editor, editor mode at the button, just above lineage part.

    added : 

     print "Switch to <a href=\"$toggle_url\">$opp_display_or_edit</a>.
    \n";

    before : 

     &makeLineageDisplay($joinkey, $display_or_edit, $header_bgcolor,$header_color);
    • 2.- Add move to old button 'move' to email -> old_email and old_email_date.

    New javascript function : function moveDataToOldTable(field, order) { // some tables have data tables and corresponding old data and old timestamp tables, this moves it from data to the old tables and deletes from data table. 2011 08 31 

    var joinkey = document.getElementById("person_joinkey").value;
var tsId = 'timestamp_' + field + '_' + order;
var timestamp = document.getElementById(tsId).value;
var valueId = 'input_' + field + '_' + order;
var value = document.getElementById(valueId).value;
var oldOrderId = 'highest_old_' + field + '_order';
var highestOldOrder = document.getElementById(oldOrderId).value;
var oldField = 'old_' + field;
var oldTsField = 'old_' + field + '_date'; if (field === 'institution') { oldTsField = 'old_inst_date'; }
updatePostgresTableField(oldField, joinkey, highestOldOrder, value);
updatePostgresTableField(oldTsField, joinkey, highestOldOrder, timestamp);
updatePostgresTableField(field, joinkey, order, );

    } // function moveDataToOldTable(e)


    change section on makeDoubleVertical this now always looks for the entry with a 'white' $bgcolor, meaning that it has the order for the new row, and changes the $order to be : 

    if ($bgcolor eq 'white') { $order .= "<input type=\"hidden\" id=\"highest_${one_table}_order\" value=\"$order\">\n"; }        # have an id for the next highest order of the old tables

    So that the javascript can get the order to use for moving data to the new tables.


    change section on makeSingleNormal under 

    if ($display_or_edit eq 'edit') {
    ($td_one_data) = &makeInputField($one_data, $one_table, $joinkey, $order, '3', '1', , $td_width, $input_size);
    if ( ($bgcolor eq $blue) && ( ($one_table eq 'email') || ($one_table eq 'institution') ) ) { ($one_table) = &labelAddMoveButton($one_table, $order); } }  # if the table already existed and has a corresponding old table, add a button to move the data

    So that if the $bgcolor is $blue and the table is either email or institution, add the ``move button by calling &labelAddMoveButton($one_table, $order)

    All this does is : sub labelAddMoveButton { 

    my ($table, $order) = @_;
$table .= " <button onclick=\"moveDataToOldTable('$table', '$order')\">move</button>";
return $table;

    } # sub labelAddMoveButton

    Note that this changes makeSingleNormal instead of making a whole new type for singleNormal that has buttons.

    Perl notes

    •  % anuncia que es un hash
    • Hash is an sistema de organizacion de llaves( cualquier palabra, varable, codigo). Las llaves pueden dar subdivisiones o valores. Ejemplo hash Bob = %Bob
      • %Bob = puede tener multiple valores
      • $Bob {two1} (llave)= cecilia (1 valor)
      • $Bob {two625} = Paul (1 valor)
      • $Var = 1 (1 valor)
      • $x = ceci (1 valor)
      • $y = falso (1 valor)
    • %ROB (hash)
      • $ROB {name} {two1} = ceci (ROB = hash); ({name} {two1} = keys); (ceci = values, resultado)
      • $ROB {name} {two625} = Paul
      • $ROB {city} {two1} = Chastsworth
      • $ROB {city} {two625} = Pasadena
    • pg_aid = postgres author id.
    • Example %pg_aid hash, how to get a value from the %pg-aid hash using the keys
      • $pg_aid {$aid (1234 numero del author id)}{index (table index)} {$join (1 or whatever)}= ceci (value el resultado).
    • %pg_aid hash
      • $pg_aid multiple keys divided by {} = value (resultado)
      • $pg_aid {$aid}{index}{$join} = ceci
      • $pg_aid {$aid}{possible}{$join} = two1
      • $pg_aid {$aid}{sent}{$join} = SENT
      • $pg_aid {$aid}{verified}{$join} = YES

    javascript notes

    • blur = Focus - enter into cell. blur - getting out of the cell by clicking else where or hitting tab. When burring in the Person-editor edit mode it will update. RECAP: which_page tells javascript which actions to load for a specific page. javascript file uses to load different actions (like autocomplete in one page, or adding extra institutions, or editing upon blurring a field).
    • which_page is a label for javascript to know which actions to do in a specific page, autocomplete ans show and hide another institution, updating data. Example, Person editor, if you click edit will use autocomple.

    Useful links

    http://www.mediawiki.org/wiki/Help:Formatting

    $sentname$othername$othertwo$role$date1$date2$sender$timestamp
    Retrieved from "https://wiki.wormbase.org/index.php?title=Person&oldid=23091"