5 #use CXGN::DB::Connection;
8 #use CXGN::People::Person;
9 use CXGN
::Phenome
::Locus2Locus
;
10 use CXGN
::Phenome
::Locus
;
11 use CXGN
::DB
::InsertDBH
;
15 our ($opt_H, $opt_D, $opt_x);
19 print STDERR
"opt_H = $opt_H\n";
21 ######data that need to be provided #####################3
22 my $locus_name = 'locus name with put the suffix number'; #locus name with out the suffix number
23 my $locus_symbol = 'locus symbol'; #locus symbol without the number suffix
24 my $organism = 'tomato'; #well, you know what..:
25 #my $subject_id = 5584;
27 my $relationship_type= 'homolog'; # eg. 'homolog'
28 my $evidence_type = 'sequence or structural similarity'; # eg. 'sequence or structural similarity'
29 my $pubmed_id = 12514239;
30 my $sp_person_id = 865;
32 #################################
34 my ($evidence_id, $reference_id, $relationship_id);
37 $locus_name =~ /(\w+)/;
39 print "$locus_name\n";
40 $locus_symbol =~ /(\D+)/;
43 print "$locus_symbol\n";
46 my $dbh = CXGN
::DB
::InsertDBH
->new({dbname
=>$opt_D, dbhost
=>$opt_H, dbuser
=>"postgres"});
47 my (@all_locus_id, @all_symbol, @all_name, @all_organism);
50 ###### retreiving relationship id, evidence_id, and reference_id (dbxrefs)
51 my $sth_rel = $dbh->prepare("SELECT cvterm.dbxref_id FROM public.cvterm
52 WHERE cvterm.name ILIKE ?
53 AND cvterm.is_obsolete = 0");
55 $sth_rel->execute($relationship_type);
56 ($relationship_id) = $sth_rel->fetchrow_array();
58 my $sth_evi = $dbh->prepare("SELECT dbxref_id FROM public.cvterm where cv_id = 19 AND cvterm.name ILIKE '%$evidence_type'");
60 ($reference_id) = $sth_evi->fetchrow_array();
62 my $sth_ref = $dbh->prepare("SELECT dbxref_id FROM public.dbxref WHERE accession = ?");
63 $sth_ref->execute($pubmed_id);
64 ($reference_id) = $sth_ref->fetchrow_array();
66 ###############################################
69 if (($locus_name) && ($organism)) {
70 my $locus_query = "SELECT locus_id, locus_symbol, locus_name, common_name FROM phenome.locus
71 JOIN sgn.common_name USING (common_name_id)
72 WHERE locus_symbol ILIKE '$locus_symbol%' AND locus_name ILIKE '$locus_name%'
73 AND common_name ILIKE '$organism' AND locus.obsolete='f'";
76 #AND (locus_id != $object_id)
77 my $sth = $dbh->prepare($locus_query);
80 while ( my ($all_locus_id, $all_symbol, $all_name, $all_organism) = $sth->fetchrow_array()) {
82 push @all_locus_id, $all_locus_id;
83 push @all_symbol, $all_symbol;
84 push @all_name, $all_name;
85 push @all_organism, $all_organism;
87 print "retreiving locus_id: $all_locus_id\t symbol: $all_symbol\t name: $all_name\t organism: $all_organism\n";
92 for (my $i=0; $i<@all_name; $i++) {
93 my $subj_id = $all_locus_id[$i];
94 my $subj_name = $all_name[$i];
95 my $subj_symbol = $all_symbol[$i];
96 my $subj_name_full = $subj_name;
98 $subj_name =~ /(\w+)/;
101 $subj_symbol =~/(\D+)/;
104 my $locus_query = "SELECT locus_id, locus_symbol, locus_name, common_name FROM phenome.locus
105 LEFT JOIN sgn.common_name USING (common_name_id)
106 WHERE (locus_name ILIKE '$subj_name%'
107 AND locus_symbol ILIKE '$subj_symbol%'
108 AND locus_name NOT ILIKE '$subj_name_full')
109 AND (sgn.common_name.common_name ILIKE '$organism')
110 AND locus.obsolete='f'";
112 #if (!$reference_id) {$reference_id = undef};
114 my $sth = $dbh->prepare($locus_query);
116 my (@obj_id, @symbol, @name, @common_name);
117 my ($obj_id, $symbol, $name, $common_name);
121 while (($obj_id, $symbol, $name, $common_name) = $sth->fetchrow_array()) {
122 my $sth1 = $dbh->prepare("SELECT locus2locus_id FROM phenome.locus2locus
123 WHERE (subject_id = $subj_id AND object_id = $obj_id)
124 OR (subject_id = $obj_id AND object_id = $subj_id) ");
126 my ($l2l_id1) = $sth1->fetchrow_array();
128 print "l2l_id1: $l2l_id1\n";
129 if ($l2l_id1) {print "l2l association already exists for locus: $subj_id\n";}
131 my $locus2locus=CXGN
::Phenome
::Locus2Locus
->new($dbh);
132 $locus2locus->set_subject_id($subj_id);
133 $locus2locus->set_object_id($obj_id);
134 $locus2locus->set_relationship_id($relationship_id);
135 $locus2locus->set_evidence_id($evidence_id);
136 $locus2locus->set_reference_id($reference_id);
137 $locus2locus->set_sp_person_id($sp_person_id);
139 print STDERR
"Storing...object: $obj_id to subject: $subj_id\n";
141 my $locus2locus_id=$locus2locus->store();
144 if ($@
) { warn "Locus to locus association failed! $@"; }
146 # }#else {print "l2l association already exists for locus: $subj_id\n";}
151 # my $person = CXGN::People::Person->new($sp_person_id);
154 # my $user_link = qq |http://www.sgn.cornell.edu/solpeople/personal-info.pl?sp_person_id=$sp_person_id |;
155 # my $subject_locus_link= qq |http://www.sgn.cornell.edu/phenome/locus_display.pl?locus_id=$subj_id |;
156 # my $object_locus_link= qq |http://www.sgn.cornell.edu/phenome/locus_display.pl?locus_id=$obj_id |;
157 # my $subject="[New locus2locus association created]";
158 # my $username= $person->get_first_name()." ".$person->get_last_name();
159 # my $fdbk_body="$username ($user_link) has associated locus $object_locus_link \n to locus $subject_locus_link \n ";
161 # CXGN::Contact::send_email($subject,$fdbk_body, 'iyt2@cornell.edu');
167 print "$count associations created\n";
170 print STDERR
"An error occurred: $@\n";
174 print STDERR
"Committing...\n";
179 #print STDERR "Done. \n";