328 lines
15 KiB
PHP
328 lines
15 KiB
PHP
<?php
|
|
/*=======================================================================
|
|
*
|
|
* inc_group.inc.php
|
|
*
|
|
* Autor: Marc Vollmann
|
|
*
|
|
=======================================================================*/
|
|
|
|
|
|
// Gets all group members of one or more specified groups and object types (items)
|
|
// Returns an array with all IDs of the requested type
|
|
// $grpIds : Array of group IDs
|
|
// $item : Specific object type (e.g. "cs" <=> customer, etc.)
|
|
function getGroupMemberIDs($grpIds, $item = "cs", $f_hq_id) {
|
|
global $db, $PHP_SELF, $hq_id;
|
|
|
|
$retArray = array();
|
|
|
|
$dbTables = array();
|
|
$dbTables["cs"] = "customer"; $dbTables["cr"] = "courier"; $dbTables["emp"] = "employee";
|
|
$dbTables["crvh"] = "couriervehicle"; $dbTables["pt"] = "prospect"; $dbTables["at"] = "article";
|
|
$dbTables["srv"] = "service"; $dbTables["srvt"] = "servicetype";
|
|
|
|
// Get all group IDs
|
|
$grpIdsLen = count($grpIds);
|
|
$whereClauseGroups = "";
|
|
for ($i = 0; $i < $grpIdsLen; $i++) :
|
|
if ($whereClauseGroups != "") : $whereClauseGroups .= " OR "; endif;
|
|
$whereClauseGroups .= " " . $item . "_group LIKE '%," . $grpIds[$i] . ",%' ";
|
|
endfor;
|
|
|
|
$whereClauseHq = " hq_id = '" . $hq_id . "' AND ";
|
|
if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
$whereClauseHq = " hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
endif;
|
|
if ($item == "at") :
|
|
$whereClauseHq = "";
|
|
endif;
|
|
|
|
if ($whereClauseGroups != "") :
|
|
if ($item == "cs" || $item == "cr" || $item == "srv" || $item == "srvt") :
|
|
$sqlquery = "SELECT " . $item . "_id AS id FROM " . $dbTables[$item] . " WHERE " . $whereClauseHq . " (" . $whereClauseGroups . ") ORDER BY " . $item . "_id";
|
|
endif;
|
|
if ($item == "emp") :
|
|
// Attention: In table "job" the field "emp_id" contains the id of the user ("usr_id")
|
|
$sqlquery = "SELECT usr.usr_id AS id FROM employee AS emp, user AS usr WHERE emp.usr_id = usr.usr_id AND usr.hq_id = '" . $hq_id . "' AND (" . $whereClauseGroups . ") ORDER BY emp.emp_id";
|
|
endif;
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[] = $row["id"];
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
return $retArray;
|
|
}
|
|
|
|
// Gets all group members of one or more specified groups and object types (items)
|
|
// Returns an array with a set of fields
|
|
// $grpIds : Array of group IDs
|
|
// $item : Specific object type (e.g. "cs" <=> customer, etc.)
|
|
function getGroupMembers($grpIds, $item = "cs", $f_hq_id, $mode = "0") {
|
|
global $db, $PHP_SELF, $hq_id;
|
|
|
|
$retArray = array();
|
|
|
|
// Get all group IDs
|
|
$grpIdsLen = count($grpIds);
|
|
$whereClauseGroups = "";
|
|
for ($i = 0; $i < $grpIdsLen; $i++) :
|
|
if ($whereClauseGroups != "") : $whereClauseGroups .= " OR "; endif;
|
|
$whereClauseGroups .= " " . $item . "." . $item . "_group LIKE '%," . $grpIds[$i] . ",%' ";
|
|
endfor;
|
|
|
|
$count = 0;
|
|
if ($whereClauseGroups != "") :
|
|
if ($item == "grp") :
|
|
$whereClauseGrp = " grp.hq_id = '" . $hq_id . "' AND ";
|
|
if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
$whereClauseGrp = " grp.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
endif;
|
|
$sqlquery = "SELECT grp.grp_id, grp.grp_name"
|
|
. " FROM groups AS grp"
|
|
. " WHERE " . $whereClauseGrp . " (" . $whereClauseGroups . ")"
|
|
. " ORDER BY grp.grp_name";
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count]["grp_name"] = $row["grp_name"];
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
if ($item == "cs") :
|
|
$whereClauseCs = " cs.hq_id = '" . $hq_id . "' AND ";
|
|
if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
$whereClauseCs = " cs.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
endif;
|
|
$sqlquery = "SELECT cs.cs_id, cs.cs_eid, cmp.cmp_comp, cmp.cmp_comp2"
|
|
. " FROM customer AS cs, company AS cmp"
|
|
. " WHERE " . $whereClauseCs . " cs.cmp_id = cmp.cmp_id AND (" . $whereClauseGroups . ")"
|
|
. " ORDER BY cmp.cmp_comp";
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count]["cs_id"] = $row["cs_id"];
|
|
$retArray[$count]["cs_eid"] = $row["cs_eid"];
|
|
$retArray[$count]["cmp_comp"] = $row["cmp_comp"];
|
|
$retArray[$count]["cmp_comp2"] = $row["cmp_comp2"];
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
if ($item == "cr") :
|
|
$whereClauseCr = " cr.hq_id = '" . $hq_id . "' AND ";
|
|
if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
$whereClauseCr = " cr.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
endif;
|
|
$sqlquery = "SELECT cr.cr_id, cr.cr_eid, usr.usr_name, usr.usr_firstname"
|
|
. " FROM courier AS cr, user AS usr"
|
|
. " WHERE " . $whereClauseCr . " cr.usr_id = usr.usr_id AND (" . $whereClauseGroups . ")"
|
|
. " ORDER BY usr.usr_name";
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count]["cr_id"] = $row["cr_id"];
|
|
$retArray[$count]["cr_eid"] = $row["cr_eid"];
|
|
$retArray[$count]["usr_name"] = $row["usr_name"];
|
|
$retArray[$count]["usr_firstname"] = $row["usr_firstname"];
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
if ($item == "emp") :
|
|
// Attention: In table "job" the field "emp_id" contains the id of the user ("usr_id")
|
|
$whereClauseUsr = " usr.hq_id = '" . $hq_id . "' AND ";
|
|
if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
$whereClauseUsr = " usr.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
endif;
|
|
$sqlquery = "SELECT usr.usr_id, usr.usr_name, usr.usr_firstname"
|
|
. " FROM employee AS emp, user AS usr"
|
|
. " WHERE " . $whereClauseUsr . " emp.usr_id = usr.usr_id AND (" . $whereClauseGroups . ")"
|
|
. " ORDER BY usr.usr_name";
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count]["usr_id"] = $row["usr_id"];
|
|
$retArray[$count]["usr_name"] = $row["usr_name"];
|
|
$retArray[$count]["usr_firstname"] = $row["usr_firstname"];
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
if ($item == "br") :
|
|
$sqlquery = "SELECT br.br_id, br.br_key, br.br_name"
|
|
. " FROM branch AS br"
|
|
. " WHERE " . $whereClauseGroups
|
|
. " ORDER BY br.br_name";
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count]["br_id"] = $row["br_id"];
|
|
$retArray[$count]["br_key"] = $row["br_key"];
|
|
$retArray[$count]["br_name"] = $row["br_name"];
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
if ($item == "at") :
|
|
$whereClauseAt = "";
|
|
// $whereClauseAt = " cr.hq_id = '" . $hq_id . "' AND ";
|
|
// if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
// $whereClauseCr = " cr.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
// endif;
|
|
$sqlquery = "SELECT at.at_id, at.at_eid, at.at_name"
|
|
. " FROM article AS at"
|
|
. " WHERE " . $whereClauseAt . " (" . $whereClauseGroups . ")"
|
|
. " ORDER BY at.at_name";
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count]["at_id"] = $row["at_id"];
|
|
$retArray[$count]["at_eid"] = $row["at_eid"];
|
|
$retArray[$count]["at_name"] = $row["at_name"];
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
if ($item == "srv") :
|
|
$whereClauseSrv = " srv.hq_id = '" . $hq_id . "' AND ";
|
|
if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
$whereClauseSrv = " srv.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
endif;
|
|
$sqlquery = "SELECT srv.srv_id, srv.srv_name"
|
|
. " FROM service AS srv"
|
|
. " WHERE " . $whereClauseSrv . " srv.srv_mode = '" . $mode . "' AND (" . $whereClauseGroups . ")"
|
|
. " ORDER BY srv.srv_name";
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count]["srv_id"] = $row["srv_id"];
|
|
$retArray[$count]["srv_name"] = $row["srv_name"];
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
if ($item == "srvt") :
|
|
$whereClauseSrvt = " srvt.hq_id = '" . $hq_id . "' AND ";
|
|
if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
$whereClauseSrvt = " srvt.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
endif;
|
|
$sqlquery = "SELECT srvt.srvt_id, srvt.srvt_name"
|
|
. " FROM servicetype AS srvt"
|
|
. " WHERE " . $whereClauseSrvt . " srvt.srvt_mode = '" . $mode . "' AND (" . $whereClauseGroups . ")"
|
|
. " ORDER BY srvt.srvt_name";
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$retArray[$count]["srvt_id"] = $row["srvt_id"];
|
|
$retArray[$count]["srvt_name"] = $row["srvt_name"];
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
endif;
|
|
return $retArray;
|
|
}
|
|
|
|
// Gets all EMAIL ADDRESSES of group members of one or more specified groups and object types (items)
|
|
// Returns an array with the email addresses
|
|
// $grpIds : Array of group IDs
|
|
// $item : Specific object type (e.g. "cs" <=> customer, etc.)
|
|
function getGroupMemberEmailaddresses($grpIds, $item = "cs", $f_hq_id, $specialWhereClause = "") {
|
|
global $db, $PHP_SELF, $hq_id;
|
|
|
|
$retArray = array();
|
|
$tmpArray1 = array();
|
|
$tmpArray2 = array();
|
|
$tmpArray3 = array();
|
|
|
|
// Get all group IDs
|
|
$grpIdsLen = count($grpIds);
|
|
$whereClauseGroups = "";
|
|
for ($i = 0; $i < $grpIdsLen; $i++) :
|
|
if ($whereClauseGroups != "") : $whereClauseGroups .= " OR "; endif;
|
|
$whereClauseGroups .= " " . $item . "." . $item . "_group LIKE '%," . $grpIds[$i] . ",%' ";
|
|
endfor;
|
|
|
|
$specialWhereClause = trim ($specialWhereClause);
|
|
if ($specialWhereClause != "") :
|
|
$specialWhereClause = " AND " . $specialWhereClause . " ";
|
|
endif;
|
|
|
|
if ($whereClauseGroups != "") :
|
|
$count = 0; // Init
|
|
if ($item == "cs") :
|
|
$whereClauseCs = " cs.hq_id = '" . $hq_id . "' AND ";
|
|
if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
$whereClauseCs = " cs.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
endif;
|
|
$sqlquery1 = "SELECT usr.usr_email AS email, cs.cs_id"
|
|
. " FROM customer AS cs, company AS cmp, employee AS emp, user AS usr"
|
|
. " WHERE " . $whereClauseCs . " cs.cmp_id = cmp.cmp_id AND (" . $whereClauseGroups . ") AND cs_jbstatusmail2csc = '0' AND"
|
|
. " cs.cs_admin = emp.emp_id AND emp.usr_id = usr.usr_id AND usr.usr_email != ''" . $specialWhereClause;
|
|
|
|
$sqlquery2 = "SELECT cscad.cscad_email AS email, cs.cs_id"
|
|
. " FROM customer AS cs, company AS cmp, costcenter AS csc, costcenteraddress AS cscad"
|
|
. " WHERE " . $whereClauseCs . " cs.cmp_id = cmp.cmp_id AND (" . $whereClauseGroups . ") AND cs_jbstatusmail2csc = '1' AND"
|
|
. " csc.cs_id = cs.cs_id AND csc.csc_id = cscad.csc_id AND cscad.adt_id = '2' AND cscad.cscad_email != ''" . $specialWhereClause;
|
|
|
|
$result = $db->query("(" . $sqlquery1 . ") UNION (" . $sqlquery2 . ")");
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$tmpArray1[$count] = $row["email"];
|
|
$tmpArray2[$count] = $row["cs_id"];
|
|
$tmpArray3[$count] = "cs";
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
if ($item == "cr") :
|
|
$whereClauseCr = " cr.hq_id = '" . $hq_id . "' AND ";
|
|
if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
$whereClauseCr = " cr.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
endif;
|
|
$sqlquery = "SELECT usr.usr_email AS email, cr.cr_id"
|
|
. " FROM courier AS cr, user AS usr, company AS cmp"
|
|
. " WHERE " . $whereClauseCr . " cmp.cmp_id = cr.cmp_id AND cr.usr_id = usr.usr_id AND (" . $whereClauseGroups . ") AND usr.usr_email != ''" . $specialWhereClause;
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$tmpArray1[$count] = $row["email"];
|
|
$tmpArray2[$count] = $row["cr_id"];
|
|
$tmpArray3[$count] = "cr";
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
if ($item == "emp") :
|
|
// Attention: In table "job" the field "emp_id" contains the id of the user ("usr_id")
|
|
$whereClauseUsr = " usr.hq_id = '" . $hq_id . "' AND ";
|
|
if ($f_hq_id != "" && count($f_hq_id) >= 1) :
|
|
$whereClauseUsr = " usr.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND ";
|
|
endif;
|
|
$sqlquery = "SELECT usr.usr_email AS email, emp.emp_id"
|
|
. " FROM employee AS emp, user AS usr"
|
|
. " WHERE " . $whereClauseUsr . " emp.usr_id = usr.usr_id AND (" . $whereClauseGroups . ") AND usr.usr_email != ''";
|
|
|
|
$result = $db->query($sqlquery);
|
|
if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());
|
|
while ($row = $result->fetch_assoc()):
|
|
$tmpArray1[$count] = $row["email"];
|
|
$tmpArray2[$count] = $row["emp_id"];
|
|
$tmpArray3[$count] = "emp";
|
|
$count++;
|
|
endwhile;
|
|
$result->free();
|
|
endif;
|
|
endif;
|
|
$retArray = array($tmpArray1, $tmpArray2, $tmpArray3);
|
|
return $retArray;
|
|
}
|
|
?>
|