public static function getForTimesheet(ConstraintChain $hours_cc = null)
{
$db = DB::Instance();
$query = 'select to_char(h.start_time, \'YYYY-MM-DD\') AS day, ht.name AS type, p.name AS project, t.name AS task, h.description, h.billable, h.duration
FROM hours h LEFT JOIN projects p ON (h.project_id=p.id)
LEFT JOIN tasks t ON (h.task_id=t.id)
LEFT JOIN hour_types ht ON (ht.id=h.type_id)';
$where = $hours_cc->__toString('h');
if (!empty($where)) {
$query .= ' WHERE ' . $where;
}
$query .= ' ORDER BY h.start_time';
$hours = $db->GetArray($query);
return $hours;
}
public function getTotalCost(ConstraintChain $cc = null)
{
// TODO : replace with getSum function in DataObject class
$db = DB::Instance();
$query = 'SELECT COALESCE(sum(cost),0) FROM opportunities WHERE status_id=' . $db->qstr($this->id) . ' AND usercompanyid=' . $db->qstr(EGS_COMPANY_ID);
if ($cc != null) {
$where = $cc->__toString();
if (!empty($where)) {
$query .= ' AND ' . $where;
}
}
$total = $db->GetOne($query);
if ($total === false) {
die($db->ErrorMsg());
}
return $total;
}
function getAllUsers(ConstraintChain $cc = null, $ignore_tree = false)
{
$db = DB::Instance();
$tablename = $this->_tablename;
if (empty($cc)) {
$cc = new ConstraintChain();
}
$query = 'SELECT ' . $this->idField . ', ' . $this->getIdentifier() . ' FROM ' . $this->_tablename;
$constraint = $cc->__toString();
if (!empty($constraint)) {
$query .= ' WHERE ' . $constraint;
}
$query .= ' ORDER BY username';
$results = $db->GetAssoc($query);
if ($this->idField == $this->getIdentifier() && $results) {
foreach ($results as $key => $nothing) {
$results[$key] = $key;
}
}
return $results;
}
function deleteAll($cc = null)
{
$db = DB::Instance();
$result = false;
if (is_null($cc)) {
$cc = new ConstraintChain();
}
if ($cc instanceof ConstraintChain) {
if ($this->_templateobject->isAccessControlled()) {
if (!isModuleAdmin()) {
$cc->add(new Constraint('usernameaccess', '=', EGS_USERNAME));
$cc->add(new Constraint('owner', '=', EGS_USERNAME), 'OR');
}
} else {
$cc->add(new Constraint('usercompanyid', '=', EGS_COMPANY_ID));
}
$do = DataObjectFactory::Factory($this->_doname);
$query = 'DELETE FROM ' . $do->getTableName() . ' where ' . $cc->__toString();
$result = $db->Execute($query);
}
return $result !== false;
}
public function getInvoiceExportList($_definition_id = '')
{
$invoice = new SInvoice();
$collection = new SInvoiceCollection($invoice);
$cc = new ConstraintChain();
$cc->add(new Constraint('transaction_type', '=', 'I'));
$cc->add(new Constraint('status', '=', 'O'));
$cc->add(new Constraint('despatch_date', 'is not', 'NULL'));
$cc->add(new Constraint('print_count', '=', '0'));
$cc->add(new Constraint('invoice_method', '=', 'D'));
$cc->add(new Constraint('edi_invoice_definition_id', '=', $_definition_id));
$translog = new EDITransactionLog();
$cc1 = new ConstraintChain();
$cc1->add(new Constraint('status', '=', 'C'));
$cc1->add(new Constraint('action', '=', 'S'));
$cc1->add(new Constraint('data_definition_id', '=', $_definition_id));
$cc1->add(new Constraint('internal_id', '=', '(' . $collection->getViewName() . '.id)'));
$cc->add(new Constraint('not', 'exists', '(select id from ' . $translog->getTableName() . ' where ' . $cc1->__toString() . ')'));
$invoice->orderby = 'invoice_number';
// echo 'cc='.$cc->__toString().'<br>';
return $invoice->getAll($cc, false, true);
}
private function selectorOverview()
{
$fields = array();
$tables = array();
$cc = new ConstraintChain();
$count = 0;
$this->orderby = $this->_templateobject->getDisplayFieldNames();
foreach ($this->_templateobject->getDisplayFieldNames() as $field => $tag) {
$count++;
$fields[$field] = 'a' . $count . '.name as ' . $field;
$fields[$field . '_id'] = 'a' . $count . '.id as ' . $field . '_id';
$tables[$count] = $this->_tablename . ' a' . $count;
if ($count > 1) {
$cc->add(new Constraint('a' . $count . '.parent_id', '=', '(a' . ($count - 1) . '.id)'));
}
}
$fields['usercompanyid'] = 'a' . $count . '.usercompanyid';
$fields = array_merge(array('id' => 'a' . $count . '.id'), $fields);
$query = '(select ' . implode(',', $fields) . ' from ' . implode(',', $tables) . ' where ' . ($constraint = $cc->__toString() . ') as selector_overview');
return $query;
}
function instantiate($interface, $type = 'SY')
{
if (!defined('EGS_COMPANY_ID')) {
$usercompanyid = -1;
} else {
$usercompanyid = EGS_COMPANY_ID;
}
if (!isset($_SESSION['injectorclass'][$usercompanyid][$interface])) {
$cc = new ConstraintChain();
$cc->add(new Constraint('name', '=', $interface));
$cc->add(new Constraint('category', '=', $type));
$cc1 = new ConstraintChain();
$cc1->add(new Constraint('usercompanyid', '=', $usercompanyid));
if ($usercompanyid > 0) {
$cc1->add(new Constraint('usercompanyid', '=', -1), 'OR');
}
$cc2 = new ConstraintChain();
$cc2->add($cc1);
$cc2->add($cc);
$query = "select * from injector_classes where " . $cc2->__toString() . " order by usercompanyid";
$db =& DB::Instance();
$result = $db->GetRow($query);
if (empty($result)) {
return FALSE;
}
$_SESSION['injectorclass'][$usercompanyid][$interface] = $result['class_name'];
}
$class_name = $_SESSION['injectorclass'][$usercompanyid][$interface];
$dependencies = self::instantiateDependencies(new ReflectionClass($class_name));
return call_user_func_array(array(new ReflectionClass($class_name), 'newInstance'), $dependencies);
}
function getQuery($fields = '', ConstraintChain $cc = null, $use_collection = FALSE)
{
$db = DB::Instance();
$tablename = $this->_tablename;
if ($use_collection) {
$collection_name = get_class($this) . 'Collection';
$coln = new $collection_name($this);
$tablename = $coln->_tablename;
}
if (empty($cc)) {
$cc = new ConstraintChain();
}
if ($this->isAccessControlled() && $this->countAccessConstraints('read') > 0) {
$cc->add($this->getAccessConstraint('read'));
}
$uc = new ConstraintChain();
if ($this->isField('usercompanyid')) {
$uc->add(new Constraint('usercompanyid', '=', EGS_COMPANY_ID));
}
$uc->add($cc);
if (empty($fields)) {
$fields = '1';
}
if (is_array($fields) && !empty($fields)) {
$fields = implode(',', $fields);
}
if (empty($fields)) {
$fields = '1';
}
$query = 'SELECT ' . $fields . ' FROM ' . $tablename;
$constraint = $uc->__toString();
if (!empty($constraint)) {
$query .= ' WHERE ' . $constraint;
}
return $query;
}
