-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmol_table_export.R
123 lines (97 loc) · 5.38 KB
/
mol_table_export.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
# TOOL mol_table_export.R: "Show vial data" (Print vial data as a table. By default information about all vials is reported. You can also set a filtering criteria)
# OUTPUT data.tsv
# OUTPUT OPTIONAL data.log
# PARAMETER OPTIONAL field: "Column" TYPE [vials.vial_barcode: vial_barcode, molecules.inchi_code: inchi_code, vials.external_ID: external_ID, vials.mol_weight: mol_weight, vials.purity: purity, vials.method: method, vials.vial_empty: vial_empty, vials.vial_tot: vial_tot, vials.content_mass: content_mass, vials.target_mass_min: vials.target_mass_min, vilas.target_mass_max: target_mass_max, vials.description: description, vials.insititute: insititute, vials.department: department, vials.person: person] DEFAULT vial_barcde (Query column)
# PARAMETER OPTIONAL comparison: "Filtering criteria" TYPE [equal-to: equal-to, smaller-than: smaller-than, larger-than: larger-than, not-equal-to: not-equal-to, none: none] DEFAULT none (Query condotion)
# PARAMETER OPTIONAL q_value: "Query value" TYPE STRING (Query value)
# PARAMETER OPTIONAL inchi_code: "InChI code" TYPE [yes: yes, no: no] DEFAULT yes (InChI code)
# PARAMETER OPTIONAL inchikey_code: "InChI key code" TYPE [yes: yes, no: no] DEFAULT yes (InChI code)
# PARAMETER OPTIONAL external_ID: "External ID" TYPE [yes: yes, no: no] DEFAULT yes (External ID)
# PARAMETER OPTIONAL mol_weight: "Molecular weight" TYPE [yes: yes, no: no] DEFAULT yes (Molecular weight)
# PARAMETER OPTIONAL purity: "Purity" TYPE [yes: yes, no: no] DEFAULT yes (Purity of the sample)
# PARAMETER OPTIONAL method: "Method" TYPE [yes: yes, no: no] DEFAULT yes (Method for determing chemistry)
# PARAMETER OPTIONAL vial_empty: "Empty vial mass" TYPE [yes: yes, no: no] DEFAULT yes (Empty vial mass)
# PARAMETER OPTIONAL vial_tot: "Total mass of vial" TYPE [yes: yes, no: no] DEFAULT yes (Total mass of vial)
# PARAMETER OPTIONAL content_mass: "Content mass" TYPE [yes: yes, no: no] DEFAULT yes (Content mass)
# PARAMETER OPTIONAL target_mass_min: "Minimum target mass" TYPE [yes: yes, no: no] DEFAULT yes (Minimum target mass)
# PARAMETER OPTIONAL target_mass_max: "Maximum target mass" TYPE [yes: yes, no: no] DEFAULT yes (Minimum target mass)
# PARAMETER OPTIONAL description: "Description" TYPE [yes: yes, no: no] DEFAULT yes (description)
# PARAMETER OPTIONAL univ: "Institute" TYPE [yes: yes, no: no] DEFAULT yes (Home institute of the imported data)
# PARAMETER OPTIONAL dep: "Department" TYPE [yes: yes, no: no] DEFAULT yes (Home department of the imported data)
# PARAMETER OPTIONAL person: "Contact person" TYPE [yes: yes, no: no] DEFAULT yes (Contact person for the imported data)
columns <- ("vials.vial_barcode")
if ( inchi_code == "yes" ){
columns <- paste(columns, ", molecules.inchi_code")
}
if ( inchikey_code == "yes" ){
columns <- paste(columns, ", molecules.inchikey_code")
}
if ( external_ID == "yes" ){
columns <- paste(columns, ", vials.external_ID")
}
if (mol_weight == "yes" ) {
columns <- paste(columns, ", vials.mol_weight")
}
if (purity == "yes" ) {
columns <- paste(columns, ", vials.purity")
}
if (method == "yes" ) {
columns <- paste(columns, ", vials.method")
}
if (vial_empty == "yes" ) {
columns <- paste(columns, ", vials.vial_empty")
}
if (vial_tot == "yes" ) {
columns <- paste(columns, ", vials.vial_tot")
}
if (content_mass == "yes" ) {
columns <- paste(columns, ", vials.content_mass")
}
if (target_mass_min == "yes" ) {
columns <- paste(columns, ", vials.target_mass_min")
}
if (target_mass_max == "yes" ) {
columns <- paste(columns, ", vials.target_mass_min")
}
if (description == "yes" ) {
columns <- paste(columns, ", vials.description")
}
if (univ == "yes" ) {
columns <- paste(columns, ", vials.institute")
}
if (dep == "yes" ) {
columns <- paste(columns, ", vials.department")
}
if (person == "yes" ) {
columns <- paste(columns, ", vials.person")
}
if (comparison == "equal-to"){
cm <- ("=")
}
if (comparison == "smaller-than"){
cm <- ("<")
}
if (comparison == "larger-than"){
cm <- (">")
}
if (comparison == "not-equal-to"){
cm <- ("!=")
}
mysql_command <- paste('mysql --defaults-file=/opt/chipster/tools_local/finchem/mysql_read.conf -e "SELECT ', columns , 'FROM vials, molecules WHERE vials.comp_num=molecules.comp_num" > data.tsv')
if (comparison != "none") {
if (field == "vials.mol_weight" || field == "vials.purity" || field == "vials.vial_empty" || field == "vials.vial_tot" || field == "vials.content_mass" || field == "vials.target_mass_min" || field == "vials.target_mass_min"){
comparison <- paste ("AND ", field, cm , q_value)
system("echo numerical >> data.log")
}else{
comparison <- paste ("AND ", field," ", cm," '", q_value,"' ", sep="")
system("echo string >> data.log")
}
sql_statement <- paste('SELECT ', columns , 'FROM vials, molecules WHERE vials.comp_num=molecules.comp_num', comparison )
echo_command <-paste("echo '", sql_statement,"' >> data.log")
system(echo_command)
mysql_command <- paste('mysql -u dbuser -pdbpass --host=192.168.1.7 finscreen -e "', sql_statement ,'" > data.tsv 2>> data.log')
}
#echo_command <- paste('echo "SELECT ', columns , 'FROM vials, molecules WHERE vials.comp_num=molecules.comp_num" >> data.log')
#system(echo_command)
system(mysql_command)
#system('mysql -u dbuser -pdbpass --host=192.168.1.7 finscreen -e "SELECT * FROM vials, molecules WHERE vials.comp_num=molecules.comp_num" > data.tsv')