<?php

namespace SynchWeb\Page;

use SynchWeb\Page;

use SynchWeb\TemplateParser;

class DC extends Page

{

public static $arg_list = array(

'id' => '\d+',

'ids' => '\d+',

'visit' => '\w+\d+-\d+',

's' => '[\w\d\-\/]+',

't' => '\w+',

'value' => '.*',

'sid' => '\d+',

'aid' => '\d+',

'pjid' => '\d+',

'pid' => '\d+',

'lid' => '\d+',

'h' => '\d\d',

'dmy' => '\d\d\d\d\d\d\d\d',

'ssid' => '\d+',

'dcg' => '\d+',

'a' => '\d+(.\d+)?',

'b' => '\d+(.\d+)?',

'c' => '\d+(.\d+)?',

'al' => '\d+(.\d+)?',

'be' => '\d+(.\d+)?',

'ga' => '\d+(.\d+)?',

'sg' => '\w+',

'single' => '\d',

'COMMENTS' => '.*',

'dcid' => '\d+',

'DATACOLLECTIONID' => '\d+',

'PERSONID' => '\d+',

'AUTOPROCPROGRAMMESSAGEID' => '\d+',

'PROCESSINGJOBID' => '\d+',

'expandgroups' => '\d',

'debug' => '\d',

'sgid' => '\d+'

);

public static $dispatch = array(

array('(/sing:le)(/:id)', 'get', '_data_collections', array('le' => '\w+', 'id' => '\d+')),

array('/chi', 'post', '_chk_image'),

array('/imq/:id', 'get', '_image_qi'),

array('/grid/:id', 'get', '_grid_info'),

array('/grid/xrc/:id', 'get', '_grid_xrc'),

array('/grid/map', 'get', '_grid_map'),

array('/ed/:id', 'get', '_edge', array('id' => '\d+'), 'edge'),

array('/mca/:id', 'get', '_mca', array('id' => '\d+'), 'mca'),

array('/strat/:id', 'get', '_dc_strategies'),

array('/rd/aid/:aid/:id', 'get', '_rd'),

array('/single/t/:t/:id', 'patch', '_set_comment'),

array('/sym', 'get', '_get_symmetry'),

array('/xfm(/:id)', 'get', '_fluo_map'),

array('/comments(/:dcid)', 'get', '_get_comments'),

array('/comments', 'post', '_add_comment'),

array('/dat/:id', 'get', '_plot'),

);

const EVTOA = 12398.4198;

# ------------------------------------------------------------------------

# Data Collection AJAX Requests

# This is pretty crazy, it will return unioned data collections, energy

# scans, xfe spectra, and robot/sample actions as a single array ordered

# by start time descending for:

# - a proposal /

# - a visit /visit/

# - a particular sample id /sid/

# - a project (explicit only) /pjid/

# - a protein /pid/

# Its also searchable (A-z0-9-/) and filterable

function _data_collections($single = null)

{

session_write_close();

// $this->db->set_debug(True);

$this->profile('starting dc page');

#if (!$this->has_arg('visit') &&

# !($this->has_arg('sid') && $this->arg('prop')) &&

# !($this->has_arg('pjid') && $this->arg('prop')))

# $this->_error('No visit, sample, or project specified');

if (!($this->has_arg('visit') || $this->has_arg('prop')))

$this->_error('No visit or proposal specified');

$args = array();

$where = '';

$where2 = '';

$where3 = '';

$where4 = '';

$sess = array('', '', '', '');

# Extra joins

$extj = array('', '', '', '');

$sample_joins = array(

'LEFT OUTER JOIN blsample smp ON dc.blsampleid = smp.blsampleid',

'LEFT OUTER JOIN blsample smp ON es.blsampleid = smp.blsampleid',

'LEFT OUTER JOIN blsample smp ON r.blsampleid = smp.blsampleid',

'LEFT OUTER JOIN blsample smp ON xrf.blsampleid = smp.blsampleid'

);

# Extra columns

$extc = '';

$extcg = '';

$with = '';

//$this->db->set_debug(True);

# Filter by types

if ($this->has_arg('t')) {

$where = ' AND dc.datacollectionid < 0';

$where2 = ' AND es.energyscanid < 0';

$where3 = ' AND r.robotactionid < 0';

$where4 = ' AND xrf.xfefluorescencespectrumid < 0';

if ($this->arg('t') == 'dc' || $this->arg('t') == 'sc' || $this->arg('t') == 'fc' || $this->arg('t') == 'gr') {

$where = '';

if ($this->arg('t') == 'sc')

$where = ' AND (dc.overlap != 0 OR ifnull(et.name, dcg.experimenttype) in ("Screening", "Characterization"))';

else if ($this->arg('t') == 'gr')

$where = ' AND dc.axisrange = 0';

else if ($this->arg('t') == 'fc')

$where = ' AND dc.overlap = 0 AND dc.axisrange > 0 AND dc.numberOfImages > 1 AND ifnull(et.name, dcg.experimenttype) not in ("Screening", "Characterization")';

} else if ($this->arg('t') == 'edge') {

$where2 = '';

} else if ($this->arg('t') == 'mca') {

$where4 = '';

} else if ($this->arg('t') == 'rb') {

$where3 = " AND (r.actiontype LIKE 'LOAD' OR r.actiontype LIKE 'UNLOAD' OR r.actiontype LIKE 'DISPOSE')";

} else if ($this->arg('t') == 'ac') {

$where3 = " AND (r.actiontype NOT LIKE 'LOAD' AND r.actiontype NOT LIKE 'UNLOAD' AND r.actiontype NOT LIKE 'DISPOSE')";

} else if ($this->arg('t') == 'flag') {

$where = " AND dc.comments LIKE '%_FLAG_%'";

$where2 = " AND es.comments LIKE '%_FLAG_%'";

$where4 = " AND xrf.comments LIKE '%_FLAG_%'";

} else if ($this->arg('t') == 'ap') {

$where = " AND ifnull(et.name, dcg.experimenttype) not in ('Screening', 'Characterization') AND app.processingstatus = 1";

$extj[0] .= "INNER JOIN autoprocintegration ap ON dc.datacollectionid = ap.datacollectionid

INNER JOIN autoprocprogram app ON app.autoprocprogramid = ap.autoprocprogramid";

} else if ($this->arg('t') == 'ph') {

$where = " AND ifnull(et.name, dcg.experimenttype) not in ('Screening', 'Characterization') AND app.processingstatus = 1 AND app.processingprograms in ('big_ep', 'fast_ep')";

$extj[0] .= "INNER JOIN processingjob pj ON dc.datacollectionid = pj.datacollectionid

INNER JOIN autoprocprogram app ON app.processingjobid = pj.processingjobid";

} else if ($this->arg('t') == 'err') {

$where = " AND appm.autoprocprogrammessageid IS NOT NULL AND (appm.severity = 'WARNING' OR appm.severity = 'ERROR')";

$extj[0] .= "LEFT OUTER JOIN autoprocintegration api ON dc.datacollectionid = api.datacollectionid

LEFT OUTER JOIN processingjob pj ON dc.datacollectionid = pj.datacollectionid

LEFT OUTER JOIN autoprocprogram app ON app.autoprocprogramid = api.autoprocprogramid

INNER JOIN autoprocprogrammessage appm ON appm.autoprocprogramid = app.autoprocprogramid";

} else if ($this->arg('t') == "scrystal" || $this->arg('t') == "nscrystal") {

// Single crystal or explicitly non-single-crystal fields

$where = ($this->arg('t') == "nscrystal") ? ' AND NOT ' : ' AND ';

// This IS NOT NULL is not redundant; this condition always evalutes to TRUE with AND NOT without it

$where .= '(ifnull(et.name, dcg.experimenttype) IS NOT NULL AND ifnull(et.name, dcg.experimenttype) in ("OSC", "Diamond Anvil High Pressure"))';

}

}

# Pagination

$start = 0;

$pp = $this->has_arg('per_page') ? $this->arg('per_page') : 15;

$end = $pp;

if ($this->has_arg('page')) {

$pg = $this->arg('page') - 1;

$start = $pg * $pp;

$end = $pg * $pp + $pp;

}

# Check that whatever we are looking for actually exists

$info = array();

# Visits

if ($this->has_arg('visit')) {

$pattern = '/([A-z]+)(\d+)-(\d+)/';

preg_match($pattern, $this->arg('visit'), $matches);

if (!sizeof($matches))

$this->_error('No such visit');

$info = $this->db->pq("SELECT TO_CHAR(s.startdate, 'HH24') as sh, TO_CHAR(s.startdate, 'DDMMYYYY') as dmy, s.sessionid, s.beamlinename as bl FROM blsession s INNER JOIN proposal p ON (p.proposalid = s.proposalid) WHERE p.proposalcode=:1 AND p.proposalnumber=:2 AND s.visit_number=:3", array($matches[1], $matches[2], $matches[3]));

if (!sizeof($info)) {

$this->_error('No such visit');

} else

$info = $info[0];

$sess = array('dcg.sessionid=:1', 'es.sessionid=:2', 'r.blsessionid=:3', 'xrf.sessionid=:4');

for ($i = 0; $i < 4; $i++)

array_push($args, $info['SESSIONID']);

# Subsamples

} else if ($this->has_arg('ssid') && $this->has_arg('prop')) {

$info = $this->db->pq("SELECT ss.blsubsampleid FROM blsubsample ss INNER JOIN blsample s ON s.blsampleid = ss.blsampleid INNER JOIN crystal cr ON cr.crystalid = s.crystalid INNER JOIN protein pr ON pr.proteinid = cr.proteinid INNER JOIN proposal p ON p.proposalid = pr.proposalid WHERE ss.blsubsampleid=:1 AND CONCAT(p.proposalcode, p.proposalnumber) LIKE :2", array($this->arg('ssid'), $this->arg('prop')));

$tables2 = array('dc', 'es', 'r', 'xrf');

$ct = 0;

foreach ($tables2 as $i => $t) {

if ($t == 'r')

$sess[$i] = 'r.robotactionid<1';

else {

$sess[$i] = $t . '.blsubsampleid=:' . ($ct + 1);

$ct++;

}

}

for ($i = 0; $i < 3; $i++)

array_push($args, $this->arg('ssid'));

# Samples

} else if ($this->has_arg('sid') && $this->has_arg('prop')) {

$info = $this->db->pq("SELECT s.blsampleid FROM blsample s INNER JOIN crystal cr ON cr.crystalid = s.crystalid INNER JOIN protein pr ON pr.proteinid = cr.proteinid INNER JOIN proposal p ON p.proposalid = pr.proposalid WHERE s.blsampleid=:1 AND CONCAT(p.proposalcode, p.proposalnumber) LIKE :2", array($this->arg('sid'), $this->arg('prop')));

$tables2 = array('dc', 'es', 'r', 'xrf');

foreach ($tables2 as $i => $t)

$sess[$i] = $t . '.blsampleid=:' . ($i + 1);

for ($i = 0; $i < 4; $i++)

array_push($args, $this->arg('sid'));

# Projects

} else if ($this->has_arg('pjid')) {

$info = $this->db->pq('SELECT p.title FROM project p LEFT OUTER JOIN project_has_person php ON php.projectid = p.projectid WHERE p.projectid=:1 AND (p.personid=:2 or php.personid=:3)', array($this->arg('pjid'), $this->user->personId, $this->user->personId));

$tables = array(

array('project_has_dcgroup', 'dc', 'datacollectiongroupid'),

array('project_has_energyscan', 'es', 'energyscanid'),

array('project_has_session', 'r', 'blsessionid', 'sessionid'),

array('project_has_xfefspectrum', 'xrf', 'xfefluorescencespectrumid'),

);

$extc = "CONCAT(prop.proposalcode, prop.proposalnumber, '-', ses.visit_number) as vis, CONCAT(prop.proposalcode, prop.proposalnumber) as prop, ";

$extcg = "max(CONCAT(prop.proposalcode, prop.proposalnumber, '-', ses.visit_number)) as vis, max(CONCAT(prop.proposalcode, prop.proposalnumber)) as prop, ";

if ($this->has_arg('dcg'))

$extcg = $extc;

foreach ($tables as $i => $t) {

$ct = sizeof($t) == 4 ? $t[3] : $t[2];

$extj[$i] .= " LEFT OUTER JOIN $t[0] prj ON $t[1].$t[2] = prj.$ct INNER JOIN proposal prop ON ses.proposalid = prop.proposalid";

$sess[$i] = 'prj.projectid=:' . ($i + 1);

}

for ($i = 0; $i < 4; $i++)

array_push($args, $this->arg('pjid'));

# Proteins

} else if ($this->has_arg('pid')) {

$info = $this->db->pq("SELECT proteinid FROM protein p WHERE p.proteinid=:1", array($this->arg('pid')));

foreach (array('dc', 'es', 'r', 'xrf') as $i => $t) {

$extj[$i] .= " INNER JOIN crystal cr ON cr.crystalid = smp.crystalid INNER JOIN protein pr ON pr.proteinid = cr.proteinid";

$sess[$i] = 'pr.proteinid=:' . (sizeof($args) + 1);

array_push($args, $this->arg('pid'));

}

# Ligands

} else if ($this->has_arg('lid')) {

$info = $this->db->pq("SELECT ligandid FROM ligand l WHERE l.ligandid=:1", array($this->arg('lid')));

foreach (array('dc', 'es', 'r', 'xrf') as $i => $t) {

$extj[$i] .= " INNER JOIN blsample_has_ligand bhl ON bhl.blsampleid = smp.blsampleid";

$sess[$i] = 'bhl.ligandid=:' . (sizeof($args) + 1);

array_push($args, $this->arg('lid'));

}

# Processing job

} else if ($this->has_arg('PROCESSINGJOBID')) {

$info = $this->db->pq('SELECT processingjobid

FROM processingjob pj

INNER JOIN datacollection dc ON pj.datacollectionid = dc.datacollectionid

INNER JOIN datacollectiongroup dcg ON dcg.datacollectiongroupid = dc.datacollectiongroupid

INNER JOIN blsession s ON s.sessionid = dcg.sessionid

WHERE pj.processingjobid=:1 AND s.proposalid=:2', array($this->arg('PROCESSINGJOBID'), $this->proposalid));

$where2 .= ' AND es.energyscanid < 0';

$where3 .= ' AND r.robotactionid < 0';

$where4 .= ' AND xrf.xfefluorescencespectrumid < 0';

for ($i = 0; $i < 4; $i++) {

$sess[$i] = 'ses.proposalid=:' . ($i + 1);

array_push($args, $this->proposalid);

}

$where .= ' AND pjis.processingjobid=:' . (sizeof($args) + 1);

array_push($args, $this->arg('PROCESSINGJOBID'));

$extj[0] .= ' LEFT OUTER JOIN processingjobimagesweep pjis ON pjis.datacollectionid=dc.datacollectionid';

} else if ($this->has_arg('sgid')) {

$info = $this->db->pq('SELECT blsampleid

FROM blsamplegroup_has_blsample bshg

LEFT OUTER JOIN blsamplegroup bsg ON bsg.blsamplegroupid = bshg.blsamplegroupid

WHERE bsg.blsamplegroupid=:1 AND bsg.proposalid=:2', array($this->arg('sgid'), $this->proposalid));

$with .= "WITH samples AS (SELECT sample.* FROM blsamplegroup_has_blsample bshg LEFT OUTER JOIN BLSample sample ON sample.blsampleid = bshg.blsampleid WHERE bshg.blsamplegroupid = {$this->arg('sgid')})";

$sample_joins[0] = 'JOIN samples smp ON smp.blsampleid = dc.blsampleid';

$sample_joins[1] = 'JOIN samples smp ON smp.blsampleid = es.blsampleid';

$sample_joins[2] = 'JOIN samples smp ON smp.blsampleid = r.blsampleid';

$sample_joins[3] = 'JOIN samples smp ON smp.blsampleid = xrf.blsampleid';

$sess[0] = '1=1';

$sess[1] = '1=1';

$sess[2] = '1=1';

$sess[3] = '1=1';

# Proposal

} else if ($this->has_arg('prop')) {

$info = $this->db->pq('SELECT proposalid FROM proposal p WHERE CONCAT(p.proposalcode, p.proposalnumber) LIKE :1', array($this->arg('prop')));

for ($i = 0; $i < 4; $i++) {

$sess[$i] = 'ses.proposalid=:' . ($i + 1);

array_push($args, $this->proposalid);

}

}

if (!sizeof($info)) {

if ($this->has_arg('sgid')) {

$this->_error('The specified sample group does not exist or have any samples added to it');

} else {

$this->_error('The specified visit, sample or project doesnt exist');

}

}

# Filter by time for visits

if (($this->has_arg('h') && ($this->has_arg('visit') || $this->has_arg('dmy'))) || $this->has_arg('dmy')) {

$where .= "AND dc.starttime > TO_DATE(:" . (sizeof($args) + 1) . ", 'HH24:MI:SS DDMMYYYY') AND dc.starttime < TO_DATE(:" . (sizeof($args) + 2) . ", 'HH24:MI:SS DDMMYYYY')";

$where2 .= "AND es.starttime > TO_DATE(:" . (sizeof($args) + 3) . ", 'HH24:MI:SS DDMMYYYY') AND es.starttime < TO_DATE(:" . (sizeof($args) + 4) . ", 'HH24:MI:SS DDMMYYYY')";

$where3 .= "AND r.starttimestamp > TO_DATE(:" . (sizeof($args) + 5) . ", 'HH24:MI:SS DDMMYYYY') AND r.starttimestamp < TO_DATE(:" . (sizeof($args) + 6) . ", 'HH24:MI:SS DDMMYYYY')";

$where4 .= "AND xrf.starttime > TO_DATE(:" . (sizeof($args) + 7) . ", 'HH24:MI:SS DDMMYYYY') AND xrf.starttime < TO_DATE(:" . (sizeof($args) + 8) . ", 'HH24:MI:SS DDMMYYYY')";

if ($this->has_arg('dmy')) {

$my = $this->arg('dmy');

} else {

$my = $info['DMY'];

if ($this->arg('h') < $info['SH']) {

$sd = mktime(0, 0, 0, substr($my, 2, 2), substr($my, 0, 2), substr($my, 4, 4)) + (3600 * 24);

$my = date('dmY', $sd);

}

}

if ($this->has_arg('h')) {

$st = $this->arg('h') . ':00:00 ';

$en = $this->arg('h') . ':59:59 ';

} else {

$st = '00:00:00';

$en = '23:59:59 ';

}

for ($i = 0; $i < 4; $i++) {

array_push($args, $st . $my);

array_push($args, $en . $my);

}

}

# If not staff check they have access to data collection

if (!$this->has_arg('visit') && !$this->staff) {

for ($i = 0; $i < 4; $i++) {

$extj[$i] .= " INNER JOIN session_has_person shp ON shp.sessionid = ses.sessionid AND shp.personid=:" . (sizeof($args) + 1);

array_push($args, $this->user->personId);

}

}

# View a single data collection

if ($this->has_arg('id')) {

$st = sizeof($args) + 1;

$where .= ' AND dc.datacollectionid=:' . $st;

$where3 .= ' AND r.robotactionid=:' . ($st + 1);

$where2 .= ' AND es.energyscanid=:' . ($st + 2);

$where4 .= ' AND xrf.xfefluorescencespectrumid=:' . ($st + 3);

for ($i = 0; $i < 4; $i++)

array_push($args, $this->arg('id'));

}

# Search terms

if ($this->has_arg('s')) {

$s = str_replace('_', '$_', $this->arg('s'));

$st = sizeof($args) + 1;

$where .= " AND (dc.filetemplate LIKE CONCAT('%',:$st,'%') ESCAPE '$' OR dc.imagedirectory LIKE CONCAT('%',:" . ($st + 1) . ",'%') ESCAPE '$' OR smp.name LIKE CONCAT('%', :" . ($st + 2) . ",'%') ESCAPE '$')";

$where2 .= " AND (es.comments LIKE CONCAT('%',:" . ($st + 3) . ",'%') ESCAPE '$' OR es.element LIKE CONCAT('%',:" . ($st + 4) . ",'%') ESCAPE '$' OR smp.name LIKE CONCAT('%',:" . ($st + 5) . ",'%') ESCAPE '$')";

$where3 .= ' AND r.robotactionid < 0';

$where4 .= " AND (xrf.filename LIKE CONCAT('%',:" . ($st + 6) . ",'%') ESCAPE '$' OR smp.name LIKE CONCAT('%',:" . ($st + 7) . ",'%') ESCAPE '$')";

for ($i = 0; $i < 8; $i++)

array_push($args, $s);

}

# Set Count field

if ($this->has_arg('dcg') || $this->has_arg('PROCESSINGJOBID') || $this->has_arg('sgid') || $this->has_arg('expandgroups')) {

$count_field = 'dc.datacollectionid';

} else {

$count_field = 'distinct dc.datacollectiongroupid';

}

# Data collection group

if ($this->has_arg('dcg') || $this->has_arg('PROCESSINGJOBID') || ($this->has_arg('id') && !$this->has_arg('t'))) {

$fields = "count(distinct dca.datacollectionfileattachmentid) as dcac,

if(dca.fileType='recip',1,0) as recip,

count(distinct dcc.datacollectioncommentid) as dccc,

1 as dcc,

smp.name as sample,

smp.blsampleid,

ses.visit_number as vn,

dc.kappastart as kappa,

dc.phistart as phi,

dc.startimagenumber as si,

ifnull(et.name, dcg.experimenttype) as dct,

dc.datacollectiongroupid as dcg,

dc.runstatus,

dc.beamsizeatsamplex as bsx,

dc.beamsizeatsampley as bsy,

dc.overlap,

dc.flux,

1 as scon,

'a' as spos,

'a' as san,

'data' as type,

dc.imageprefix as imp,

dc.datacollectionnumber as run,

dc.filetemplate,

dc.datacollectionid as id,

dc.numberofimages as ni,

dc.imagedirectory as dir,

dc.resolution,

dc.exposuretime,

dc.axisstart,

dc.numberofimages as numimg,

TO_CHAR(dc.starttime, 'DD-MM-YYYY HH24:MI:SS') as st,

dc.transmission,

dc.axisrange,

dc.wavelength,

".self::EVTOA."/dc.wavelength as energy,

dc.comments,

1 as epk,

1 as ein,

1 as wpk,

1 as win,

dc.xtalsnapshotfullpath1 as x1,

dc.xtalsnapshotfullpath2 as x2,

dc.xtalsnapshotfullpath3 as x3,

dc.xtalsnapshotfullpath4 as x4,

dc.starttime as sta,

dc.detectordistance as det,

dc.xbeam,

dc.ybeam,

dc.chistart,

dc.omegastart,

dcg.scanparameters as scanparams,

dc.voltage,

dc.c2aperture,

dc.c2lens,

dc.objaperture,

dc.magnification,

dc.totalabsorbeddose as totaldose,

d.detectorpixelsizehorizontal,

d.detectorpixelsizevertical,

d.detectormanufacturer,

d.detectormodel,

TIMESTAMPDIFF('MINUTE', dc.starttime, CURRENT_TIMESTAMP) as age,

dc.numberofpasses,

dc.c1aperture,

dc.c3aperture,

dc.c1lens,

dc.c3lens,

dc.detectormode,

dc.slitgaphorizontal,

dc.slitgapvertical,

dc.binning,

dc.averagetemperature,

dc.nominalmagnification,

dc.nominaldefocus,

dc.imagesizex,

dc.imagesizey,

dc.pixelsizeonimage,

dc.phaseplate,

ses.beamlinename as bl,

dc.blsubsampleid,

IF(dc.detectorMode = 'ROI', d.numberofroipixelsx, d.numberofpixelsx) as detectornumberofpixelsx,

IF(dc.detectorMode = 'ROI', d.numberofroipixelsy, d.numberofpixelsy) as detectornumberofpixelsy,

ses.archived,

ses.purgedProcessedData,

IFNULL(dc.rotationaxis, 'Omega') as rotationaxis,

dc.detector2theta";

$groupby = 'GROUP BY smp.name,

smp.blsampleid,

ses.visit_number,

dc.kappastart,

dc.phistart,

dc.startimagenumber,

ifnull(et.name, dcg.experimenttype),

dc.datacollectiongroupid,

dc.runstatus,

dc.beamsizeatsamplex,

dc.beamsizeatsampley,

dc.overlap,

dc.flux,

dc.imageprefix,

dc.datacollectionnumber,

dc.filetemplate,

dc.datacollectionid,

dc.numberofimages,

dc.imagedirectory,

dc.resolution,

dc.exposuretime,

dc.axisstart,

dc.numberofimages,

TO_CHAR(dc.starttime, \'DD-MM-YYYY HH24:MI:SS\'),

dc.transmission,

dc.axisrange,

dc.wavelength,

dc.comments,

dc.xtalsnapshotfullpath1,

dc.xtalsnapshotfullpath2,

dc.xtalsnapshotfullpath3,

dc.xtalsnapshotfullpath4,

dc.starttime,

dc.detectordistance,

dc.xbeam,

dc.ybeam,

dc.chistart

';

// $this->db->set_debug(True);

// will want to support these too at some point

$where2 .= ' AND es.energyscanid < 0';

$where3 .= ' AND r.robotactionid < 0';

$where4 .= ' AND xrf.xfefluorescencespectrumid < 0';

if ($this->has_arg('dcg')) {

$where .= ' AND dc.datacollectiongroupid=:' . (sizeof($args) + 1);

array_push($args, $this->arg('dcg'));

}

} else {

$groupby = 'GROUP BY dc.datacollectiongroupid';

$numimgs = 'totals.numimg';

if ($this->has_arg('expandgroups')) {

$groupby = 'GROUP BY dc.datacollectionid';

$numimgs = 'dc.numberofimages as numimg';

}

$fields = "count(distinct dca.datacollectionfileattachmentid) as dcac,

if(dca.fileType='recip',1,0) as recip,

count(distinct dcc.datacollectioncommentid) as dccc,

count(distinct dc.datacollectionid) as dcc,

min(smp.name) as sample,

min(smp.blsampleid) as blsampleid,

min(ses.visit_number) as vn,

min(dc.kappastart) as kappa,

min(dc.phistart) as phi,

min(dc.startimagenumber) as si,

min(ifnull(et.name, dcg.experimenttype)) as dct,

dc.datacollectiongroupid as dcg,

min(dc.runstatus) as runstatus,

min(dc.beamsizeatsamplex) as bsx,

min(dc.beamsizeatsampley) as bsy,

min(dc.overlap) as overlap,

max(dc.flux) as flux,

1 as scon,

'a' as spos,

'a' as san,

'data' as type,

min(dc.imageprefix) as imp,

min(dc.datacollectionnumber) as run,

min(dc.filetemplate) as filetemplate,

min(dc.datacollectionid) as id,

min(dc.numberofimages) as ni,

min(dc.imagedirectory) as dir,

min(dc.resolution) as resolution,

min(dc.exposuretime) as exposuretime,

min(dc.axisstart) as axisstart,

$numimgs,

TO_CHAR(min(dc.starttime), 'DD-MM-YYYY HH24:MI:SS') as st,

min(dc.transmission) as transmission,

min(dc.axisrange) as axisrange,

min(dc.wavelength) as wavelength,

".self::EVTOA."/min(dc.wavelength) as energy,

ifnull(dcg.comments, (select mindc.comments from datacollection mindc where mindc.datacollectionid=min(dc.datacollectionid))) as comments,

1 as epk,

1 as ein,

1 as wpk,

1 as win,

min(dc.xtalsnapshotfullpath1) as x1,

min(dc.xtalsnapshotfullpath2) as x2,

min(dc.xtalsnapshotfullpath3) as x3,

min(dc.xtalsnapshotfullpath4) as x4,

max(dc.starttime) as sta,

min(dc.detectordistance) as det,

min(dc.xbeam) as xbeam,

min(dc.ybeam) as ybeam,

min(dc.chistart) as chistart,

min(dc.omegastart) as omegastart,

dcg.scanparameters as scanparams,

max(dc.voltage) as voltage,

max(dc.c2aperture) as c2aperture,

max(dc.c2lens) as c2lens,

max(dc.objaperture) as objaperture,

max(dc.magnification) as magnification,

totals.totaldose as totaldose,

max(d.detectorpixelsizehorizontal) as detectorpixelsizehorizontal,

max(d.detectorpixelsizevertical) as detectorpixelsizevertical,

max(d.detectormanufacturer) as detectormanufacturer,

max(d.detectormodel) as detectormodel,

max(TIMESTAMPDIFF('MINUTE', dc.starttime, CURRENT_TIMESTAMP)) as age,

max(dc.numberofpasses) as numberofpasses,

max(dc.c1aperture) as c1aperture,

max(dc.c3aperture) as c3aperture,

max(dc.c1lens) as c1lens,

max(dc.c3lens) as c3lens,

max(dc.detectormode) as detectormode,

max(dc.slitgaphorizontal) as slitgaphorizontal,

max(dc.slitgapvertical) as slitgapvertical,

max(dc.binning) as binning,

max(dc.averagetemperature) as averagetemperature,

max(dc.nominalmagnification) as nominalmagnification,

max(dc.nominaldefocus) as nominaldefocus,

max(dc.imagesizex) as imagesizex,

max(dc.imagesizey) as imagesizey,

max(dc.pixelsizeonimage) as pixelsizeonimage,

max(dc.phaseplate) as phaseplate,

max(ses.beamlinename) as bl,

max(dc.blsubsampleid) as blsubsampleid,

IF(dc.detectorMode = 'ROI', max(d.numberofroipixelsx), max(d.numberofpixelsx)) as detectornumberofpixelsx,

IF(dc.detectorMode = 'ROI', max(d.numberofroipixelsy), max(d.numberofpixelsy)) as detectornumberofpixelsy,

max(ses.archived) as archived,

max(ses.purgedProcessedData) as purgedProcessedData,

IFNULL(max(dc.rotationaxis), 'Omega') as rotationaxis,

min(dc.detector2theta) as detector2theta";

}

// We don't want to remove duplicates, since if two counts are equal, one might go uncounted

$total_query = "SELECT sum(tot) as t FROM (

$with

SELECT count($count_field) as tot

FROM datacollection dc

INNER JOIN datacollectiongroup dcg ON dcg.datacollectiongroupid = dc.datacollectiongroupid

INNER JOIN blsession ses ON ses.sessionid = dcg.sessionid

LEFT OUTER JOIN experimenttype et on dcg.experimenttypeid = et.experimenttypeid

$sample_joins[0]

$extj[0]

WHERE $sess[0] $where

UNION ALL SELECT count(es.energyscanid) as tot

FROM energyscan es

INNER JOIN blsession ses ON ses.sessionid = es.sessionid

$sample_joins[1]

$extj[1]

WHERE $sess[1] $where2

UNION ALL SELECT count(xrf.xfefluorescencespectrumid) as tot

FROM xfefluorescencespectrum xrf

INNER JOIN blsession ses ON ses.sessionid = xrf.sessionid

$sample_joins[3]

$extj[3]

WHERE $sess[3] $where4

UNION ALL SELECT count(r.robotactionid) as tot

FROM robotaction r

INNER JOIN blsession ses ON ses.sessionid = r.blsessionid

$sample_joins[2]

$extj[2]

WHERE $sess[2] $where3

) inq";

$tot = $this->db->pq($total_query, $args);

$tot = $tot[0]['T'];

$this->profile('after page count');

$pgs = intval($tot / $pp);

if ($tot % $pp != 0)

$pgs++;

$st = sizeof($args) + 1;

array_push($args, $start);

array_push($args, $end);

$q = "

$with

SELECT $extcg $fields

FROM datacollection dc

INNER JOIN datacollectiongroup dcg ON dcg.datacollectiongroupid = dc.datacollectiongroupid

INNER JOIN (

select datacollectiongroupid, sum(totalabsorbeddose) as totaldose, sum(numberofimages) as numimg from datacollection group by datacollectiongroupid) totals

ON dc.datacollectiongroupid = totals.datacollectiongroupid

INNER JOIN blsession ses ON ses.sessionid = dcg.sessionid

LEFT OUTER JOIN experimenttype et on dcg.experimenttypeid = et.experimenttypeid

$sample_joins[0]

LEFT OUTER JOIN datacollectioncomment dcc ON dc.datacollectionid = dcc.datacollectionid

LEFT OUTER JOIN datacollectionfileattachment dca ON dc.datacollectionid = dca.datacollectionid

LEFT OUTER JOIN detector d ON d.detectorid = dc.detectorid

$extj[0]

WHERE $sess[0] $where

$groupby

UNION ALL

SELECT

$extc

1 as dcac,

0 as recip,

1 as dccc,

1 as dcc,

smp.name as sample,

smp.blsampleid,

ses.visit_number as vn,

1,

1,

1,

'A',

1,

'A',

es.beamsizehorizontal,

es.beamsizevertical,

1,

1,

1 as scon,

'A' as spos,

'A' as sn,

'edge' as type,

es.jpegchoochfilefullpath,

1,

es.scanfilefullpath,

es.energyscanid,

1,

es.element,

es.peakfprime as resolution,

es.exposuretime,

es.axisposition,

es.peakfdoubleprime as numimg,

es.starttime as st,

es.transmissionfactor,

es.inflectionfprime as axisrange,

es.inflectionfdoubleprime as wavelength,

1 as energy,

es.comments,

es.peakenergy,

es.inflectionenergy,

".self::EVTOA."/es.peakenergy as wpk,

".self::EVTOA."/es.inflectionenergy as win,

'A',

'A',

'A',

'A',

es.starttime as sta,

1,

1,

1,

0,

0,

1,

1,

1,

1,

1,

1,

1,

1,

1,

'',

'',

TIMESTAMPDIFF(MINUTE, es.starttime, CURRENT_TIMESTAMP) as age,

0,

0,

0,

0,

0,

'',

0,

0,

0,

0,

0,

0,

0,

0,

0,

0,

ses.beamlinename as bl,

es.blsubsampleid,

'',

'',

ses.archived,

ses.purgedProcessedData,

'',

''

FROM energyscan es

INNER JOIN blsession ses ON ses.sessionid = es.sessionid

$sample_joins[1]

$extj[1]

WHERE $sess[1] $where2

UNION

SELECT

$extc

1 as dcac,

0 as recip,

1 as dccc,

1 as dcc,

smp.name as sample,

smp.blsampleid,

ses.visit_number as vn,

1,

1,

1,

'A',

1,

'A',

xrf.beamsizehorizontal,

xrf.beamsizevertical,

1,

1,

1,

'A',

'A',

'mca' as type,

'A',

1,

'A',

xrf.xfefluorescencespectrumid,

1,

xrf.filename,

1,

xrf.exposuretime,

xrf.axisposition,

1,

TO_CHAR(xrf.starttime, 'DD-MM-YYYY HH24:MI:SS') as st,

xrf.beamtransmission,

1,

".self::EVTOA."/xrf.energy as wavelength,

xrf.energy as energy,

xrf.comments,

1,

1,

1 as wpk,

1 as win,

'A',

'A',

'A',

'A',

xrf.starttime as sta,

1,

1,

1,

0,

0,

1,

1,

1,

1,

1,

1,

1,

1,

1,

'',

'',

TIMESTAMPDIFF('MINUTE', xrf.starttime, CURRENT_TIMESTAMP) as age,

0,

0,

0,

0,

0,

'',

0,

0,

0,

0,

0,

0,

0,

0,

0,

0,

ses.beamlinename as bl,

xrf.blsubsampleid,

'',

'',

ses.archived,

ses.purgedProcessedData,

'',

''

FROM xfefluorescencespectrum xrf

INNER JOIN blsession ses ON ses.sessionid = xrf.sessionid

$sample_joins[3]

$extj[3]

WHERE $sess[3] $where4

UNION

SELECT

$extc

1 as dcac,

0 as recip,

1 as dccc,

1 as dcc,

smp.name as sample,

smp.blsampleid,

ses.visit_number as vn,

1,

1,

1,

'A',

1,

'A',

ROUND(TIMESTAMPDIFF('SECOND', CAST(r.starttimestamp AS DATE), CAST(r.endtimestamp AS DATE)), 1),

1,

1,

1,

1,

r.status,

r.message,

'load' as type,

r.actiontype,

1,

smp.code,

r.robotactionid,

1,

r.samplebarcode,

r.containerlocation,

r.dewarlocation,

1,

1,

TO_CHAR(r.starttimestamp, 'DD-MM-YYYY HH24:MI:SS') as st,

1,

1,

1,

1 as energy,

'A',

1,

1,

1 as wpk,

1 as win,

r.xtalsnapshotbefore,

r.xtalsnapshotafter,

'A',

'A',

r.starttimestamp as sta,

1,

1,

1,

0,

0,

1,

1,

1,

1,

1,

1,

1,

1,

1,

'',

'',

TIMESTAMPDIFF('MINUTE', r.starttimestamp, CURRENT_TIMESTAMP) as age,

0,

0,

0,

0,

0,

'',

0,

0,

0,

0,

0,

0,

0,

0,

0,

0,

ses.beamlinename as bl,

1 as blsubsampleid,

'',

'',

ses.archived,

ses.purgedProcessedData,

'',

''

FROM robotaction r

INNER JOIN blsession ses ON ses.sessionid = r.blsessionid

$sample_joins[2]

$extj[2]

WHERE $sess[2] $where3

ORDER BY sta DESC, id DESC";

$dcs = $this->db->paginate($q, $args);

$this->profile('main query');

foreach ($dcs as $i => &$dc) {

$dc['SN'] = 0;

$dc['DI'] = 0;