/**
* This is the main function that returns the array of emails
*
* @param int $mailing_id Id of mailing. No need to use it.
* @param array $filtersarray If you used the formFilter function. Empty otherwise.
* @return int <0 if error, number of emails added if ok
*/
function add_to_target($mailing_id, $filtersarray = array())
{
$target = array();
// CHANGE THIS
// ----- Your code start here -----
// You must fill the $target array with record like this
// $target[0]=array('email'=>'email_0','name'=>'name_0','firstname'=>'firstname_0', 'other'=>'other_0');
// ...
// $target[n]=array('email'=>'email_n','name'=>'name_n','firstname'=>'firstname_n', 'other'=>'other_n');
// Example: $target[0]=array('email'=>'*****@*****.**', 'name'=>'Doe', 'firstname'=>'John', 'other'=>'Other information');
// ----- Your code end here -----
return parent::add_to_target($mailing_id, $target);
}
/**
* Return here number of distinct emails returned by your selector.
*
* @param string $sql Requete sql de comptage
* @return int
*/
function getNbOfRecipients($sql = '')
{
global $conf;
// We must report here number of contacts when absolutely no filter selected (so all contacts).
// Number with a filter are show in the combo list for each filter.
// If we want a filter "a position is defined", we must add it into formFilter
$sql = "SELECT count(distinct(sp.email)) as nb";
$sql .= " FROM " . MAIN_DB_PREFIX . "socpeople as sp";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "societe as s ON s.rowid = sp.fk_soc";
$sql .= " WHERE sp.entity IN (" . getEntity('societe', 1) . ")";
$sql .= " AND sp.email != ''";
// Note that null != '' is false
$sql .= " AND sp.no_email = 0";
$sql .= " AND sp.statut = 1";
//$sql.= " AND sp.poste != ''";
// La requete doit retourner un champ "nb" pour etre comprise
// par parent::getNbOfRecipients
return parent::getNbOfRecipients($sql);
}
/**
* \brief Ajoute destinataires dans table des cibles
* \param mailing_id Id of emailing
* \param filterarray Requete sql de selection des destinataires
* \return int < 0 si erreur, nb ajout si ok
*/
function add_to_target($mailing_id,$filtersarray=array())
{
global $conf,$langs,$_FILES;
require_once(DOL_DOCUMENT_ROOT."/lib/files.lib.php");
// For compatibility with Unix, MS-Dos or Macintosh
ini_set('auto_detect_line_endings', true);
$cibles = array();
$upload_dir=$conf->mailing->dir_temp;
if (create_exdir($upload_dir) >= 0)
{
$resupload = dol_move_uploaded_file($_FILES['username']['tmp_name'], $upload_dir . "/" . $_FILES['username']['name'], 1, 0, $_FILES['username']['error']);
if (is_numeric($resupload) && $resupload > 0)
{
$cpt=0;
//$mesg = '<div class="ok">'.$langs->trans("FileTransferComplete").'</div>';
//print_r($_FILES);
$file=$upload_dir . "/" . $_FILES['username']['name'];
$handle = @fopen($file, "r");
if ($handle)
{
$i = 0;
$j = 0;
$old = '';
while (!feof($handle))
{
$cpt++;
$buffer = trim(fgets($handle));
$tab=explode(';',$buffer,4);
$email=$tab[0];
$name=$tab[1];
$firstname=$tab[2];
$other=$tab[3];
if (! empty($buffer))
{
//print 'xx'.dol_strlen($buffer).empty($buffer)."<br>\n";
$id=$cpt;
if (isValidEMail($email))
{
if ($old <> $email)
{
$cibles[$j] = array(
'email' => $email,
'name' => $name,
'firstname' => $firstname,
'other' => $other,
'source_url' => '',
'source_id' => '',
'source_type' => 'file'
);
$old = $email;
$j++;
}
}
else
{
$i++;
$langs->load("errors");
$this->error = $langs->trans("ErrorFoundBadEmailInFile",$i,$cpt,$email);
}
}
}
fclose($handle);
if ($i > 0)
{
return -$i;
}
}
else
{
$this->error = $langs->trans("ErrorFaildToOpenFile");
return -1;
}
dol_syslog(get_class($this)."::add_to_target mailing ".$cpt." targets found");
}
else
{
$langs->load("errors");
if ($resupload < 0) // Unknown error
{
$this->error = '<div class="error">'.$langs->trans("ErrorFileNotUploaded").'</div>';
}
else if (preg_match('/ErrorFileIsInfectedWithAVirus/',$resupload)) // Files infected by a virus
{
$this->error = '<div class="error">'.$langs->trans("ErrorFileIsInfectedWithAVirus").'</div>';
}
else // Known error
{
$this->error = '<div class="error">'.$langs->trans($resupload).'</div>';
}
}
}
ini_set('auto_detect_line_endings', false);
return parent::add_to_target($mailing_id, $cibles);
}
function getNbOfRecipients()
{
global $conf;
$sql = "SELECT count(distinct(s.email)) as nb";
$sql .= " FROM " . MAIN_DB_PREFIX . "societe as s";
$sql .= " WHERE s.email != ''";
$sql .= " AND s.entity = " . $conf->entity;
// La requete doit retourner un champ "nb" pour etre comprise
// par parent::getNbOfRecipients
return parent::getNbOfRecipients($sql);
}
/**
* Ajoute destinataires dans table des cibles
*
* @param int $mailing_id Id of emailing
* @param array $filtersarray Param to filter sql request. Deprecated. Should use $_POST instead.
* @return int < 0 si erreur, nb ajout si ok
*/
function add_to_target($mailing_id, $filtersarray = array())
{
global $langs, $_POST;
$langs->load("members");
$langs->load("companies");
$cibles = array();
$now = dol_now();
$dateendsubscriptionafter = dol_mktime($_POST['subscriptionafterhour'], $_POST['subscriptionaftermin'], $_POST['subscriptionaftersec'], $_POST['subscriptionaftermonth'], $_POST['subscriptionafterday'], $_POST['subscriptionafteryear']);
$dateendsubscriptionbefore = dol_mktime($_POST['subscriptionbeforehour'], $_POST['subscriptionbeforemin'], $_POST['subscriptionbeforesec'], $_POST['subscriptionbeforemonth'], $_POST['subscriptionbeforeday'], $_POST['subscriptionbeforeyear']);
// La requete doit retourner: id, email, fk_contact, name, firstname
$sql = "SELECT a.rowid as id, a.email as email, null as fk_contact, ";
$sql .= " a.lastname, a.firstname,";
$sql .= " a.datefin, a.civilite as civility_id, a.login, a.societe";
// Other fields
$sql .= " FROM " . MAIN_DB_PREFIX . "adherent as a";
$sql .= " WHERE a.email <> ''";
// Note that null != '' is false
$sql .= " AND a.email NOT IN (SELECT email FROM " . MAIN_DB_PREFIX . "mailing_cibles WHERE fk_mailing=" . $mailing_id . ")";
if (isset($_POST["filter"]) && $_POST["filter"] == '-1') {
$sql .= " AND a.statut=-1";
}
if (isset($_POST["filter"]) && $_POST["filter"] == '1a') {
$sql .= " AND a.statut=1 AND a.datefin >= '" . $this->db->idate($now) . "'";
}
if (isset($_POST["filter"]) && $_POST["filter"] == '1b') {
$sql .= " AND a.statut=1 AND (a.datefin IS NULL or a.datefin < '" . $this->db->idate($now) . "')";
}
if (isset($_POST["filter"]) && $_POST["filter"] == '0') {
$sql .= " AND a.statut=0";
}
if ($dateendsubscriptionafter > 0) {
$sql .= " AND datefin > '" . $this->db->idate($dateendsubscriptionafter) . "'";
}
if ($dateendsubscriptionbefore > 0) {
$sql .= " AND datefin < '" . $this->db->idate($dateendsubscriptionbefore) . "'";
}
$sql .= " ORDER BY a.email";
//print $sql;
// Add targets into table
dol_syslog(get_class($this) . "::add_to_target sql=" . $sql);
$result = $this->db->query($sql);
if ($result) {
$num = $this->db->num_rows($result);
$i = 0;
$j = 0;
dol_syslog(get_class($this) . "::add_to_target mailing " . $num . " targets found");
$old = '';
while ($i < $num) {
$obj = $this->db->fetch_object($result);
if ($old != $obj->email) {
$cibles[$j] = array('email' => $obj->email, 'fk_contact' => $obj->fk_contact, 'lastname' => $obj->lastname, 'firstname' => $obj->firstname, 'other' => $langs->transnoentities("Login") . '=' . $obj->login . ';' . ($langs->transnoentities("UserTitle") . '=' . ($obj->civility_id ? $langs->transnoentities("Civility" . $obj->civility_id) : '')) . ';' . ($langs->transnoentities("DateEnd") . '=' . dol_print_date($this->db->jdate($obj->datefin), 'day')) . ';' . ($langs->transnoentities("Company") . '=' . $obj->societe), 'source_url' => $this->url($obj->id), 'source_id' => $obj->id, 'source_type' => 'member');
$old = $obj->email;
$j++;
}
$i++;
}
} else {
dol_syslog($this->db->error());
$this->error = $this->db->error();
return -1;
}
return parent::add_to_target($mailing_id, $cibles);
}
}
}
if (GETPOST('clearlist')) {
// Loading Class
$obj = new MailingTargets($db);
$obj->clear_target($id);
header("Location: " . $_SERVER['PHP_SELF'] . "?id=" . $id);
exit;
}
if ($action == 'delete') {
// Ici, rowid indique le destinataire et id le mailing
$sql = "DELETE FROM " . MAIN_DB_PREFIX . "mailing_cibles WHERE rowid=" . $rowid;
$resql = $db->query($sql);
if ($resql) {
if (!empty($id)) {
$obj = new MailingTargets($db);
$obj->update_nb($id);
header("Location: " . $_SERVER['PHP_SELF'] . "?id=" . $id);
exit;
} else {
header("Location: list.php");
exit;
}
} else {
dol_print_error($db);
}
}
if ($_POST["button_removefilter"]) {
$search_lastname = '';
$search_firstname = '';
$search_email = '';
/**
* Load an object from its id and create a new one in database
*
* @param int $fromid Id of object to clone
* @param int $option1 1=Copy content, 0=Forget content
* @param int $option2 Not used
* @return int New id of clone
*/
function createFromClone($fromid, $option1, $option2)
{
global $user, $langs;
$error = 0;
$object = new Mailing($this->db);
$object->context['createfromclone'] = 'createfromclone';
$this->db->begin();
// Load source object
$object->fetch($fromid);
$object->id = 0;
$object->statut = 0;
// Clear fields
$object->titre = $langs->trans("CopyOf") . ' ' . $object->titre . ' ' . dol_print_date(dol_now());
// If no option copy content
if (empty($option1)) {
// Clear values
$object->nbemail = 0;
$object->sujet = '';
$object->body = '';
$object->bgcolor = '';
$object->bgimage = '';
$object->email_from = '';
$object->email_replyto = '';
$object->email_errorsto = '';
$object->user_creat = $user->id;
$object->user_valid = '';
$object->date_creat = '';
$object->date_valid = '';
$object->date_envoi = '';
}
// Create clone
$result = $object->create($user);
// Other options
if ($result < 0) {
$this->error = $object->error;
$error++;
}
if (!$error) {
//Clone target
if (!empty($option2)) {
require_once DOL_DOCUMENT_ROOT . '/core/modules/mailings/modules_mailings.php';
$mailing_target = new MailingTargets($this->db);
$target_array = array();
$sql = "SELECT fk_contact, ";
$sql .= " lastname, ";
$sql .= " firstname,";
$sql .= " email,";
$sql .= " other,";
$sql .= " source_url,";
$sql .= " source_id ,";
$sql .= " source_type ";
$sql .= " FROM " . MAIN_DB_PREFIX . "mailing_cibles ";
$sql .= " WHERE fk_mailing = " . $fromid;
dol_syslog(get_class($this) . "::createFromClone", LOG_DEBUG);
$result = $this->db->query($sql);
if ($result) {
if ($this->db->num_rows($result)) {
while ($obj = $this->db->fetch_object($result)) {
$target_array[] = array('fk_contact' => $obj->fk_contact, 'lastname' => $obj->lastname, 'firstname' => $obj->firstname, 'email' => $obj->email, 'other' => $obj->other, 'source_url' => $obj->source_url, 'source_id' => $obj->source_id, 'source_type' => $obj->source_type);
}
}
} else {
$this->error = $this->db->lasterror();
return -1;
}
$mailing_target->add_to_target($object->id, $target_array);
}
}
unset($object->context['createfromclone']);
// End
if (!$error) {
$this->db->commit();
return $object->id;
} else {
$this->db->rollback();
return -1;
}
}
/**
* Ajoute destinataires dans table des cibles
*
* @param int $mailing_id Id of emailing
* @param array $filtersarray Requete sql de selection des destinataires
* @return int <0 si erreur, nb ajout si ok
*/
function add_to_target($mailing_id, $filtersarray = array())
{
global $conf, $langs;
$cibles = array();
// List prospects levels
$prospectlevel = array();
$sql = "SELECT code, label";
$sql .= " FROM " . MAIN_DB_PREFIX . "c_prospectlevel";
$sql .= " WHERE active > 0";
$sql .= " ORDER BY label";
$resql = $this->db->query($sql);
if ($resql) {
$num = $this->db->num_rows($resql);
$i = 0;
while ($i < $num) {
$obj = $this->db->fetch_object($resql);
$prospectlevel[$obj->code] = $obj->label;
$i++;
}
} else {
dol_print_error($this->db);
}
// La requete doit retourner: id, email, fk_contact, name, firstname, other
$sql = "SELECT c.rowid as id, c.email as email, c.rowid as fk_contact,";
$sql .= " c.lastname, c.firstname, c.civilite as civility_id,";
$sql .= " s.nom as companyname";
$sql .= " FROM " . MAIN_DB_PREFIX . "socpeople as c";
$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "societe as s ON s.rowid = c.fk_soc";
$sql .= " WHERE c.entity IN (" . getEntity('societe', 1) . ")";
$sql .= " AND c.email <> ''";
$sql .= " AND c.no_email = 0";
$sql .= " AND c.email NOT IN (SELECT email FROM " . MAIN_DB_PREFIX . "mailing_cibles WHERE fk_mailing=" . $mailing_id . ")";
foreach ($filtersarray as $key) {
if ($key == 'prospects') {
$sql .= " AND s.client=2";
}
//print "xx".$key;
foreach ($prospectlevel as $codelevel => $valuelevel) {
if ($key == 'prospectslevel' . $codelevel) {
$sql .= " AND s.fk_prospectlevel='" . $codelevel . "'";
}
}
if ($key == 'customers') {
$sql .= " AND s.client=1";
}
if ($key == 'suppliers') {
$sql .= " AND s.fournisseur=1";
}
}
$sql .= " ORDER BY c.email";
//print "x".$sql;
// Stocke destinataires dans cibles
$result = $this->db->query($sql);
if ($result) {
$num = $this->db->num_rows($result);
$i = 0;
$j = 0;
dol_syslog(get_class($this) . "::add_to_target mailing " . $num . " targets found");
$old = '';
while ($i < $num) {
$obj = $this->db->fetch_object($result);
if ($old != $obj->email) {
$cibles[$j] = array('email' => $obj->email, 'fk_contact' => $obj->fk_contact, 'lastname' => $obj->lastname, 'firstname' => $obj->firstname, 'other' => $langs->transnoentities("ThirdParty") . '=' . $obj->companyname . ';' . ($langs->transnoentities("UserTitle") . '=' . ($obj->civilite_id ? $langs->transnoentities("Civility" . $obj->civilite_id) : '')), 'source_url' => $this->url($obj->id), 'source_id' => $obj->id, 'source_type' => 'contact');
$old = $obj->email;
$j++;
}
$i++;
}
} else {
dol_syslog($this->db->error());
$this->error = $this->db->error();
return -1;
}
return parent::add_to_target($mailing_id, $cibles);
}
/**
* Return here number of distinct emails returned by your selector.
* For example if this selector is used to extract 500 different
* emails from a text file, this function must return 500.
*
* @param string $sql SQL request to use to count
* @return int Number of recipients
*/
function getNbOfRecipients($sql = '')
{
$now = dol_now();
// Example: return parent::getNbOfRecipients("SELECT count(*) as nb from dolibarr_table");
// Example: return 500;
$sql = "SELECT count(*) as nb";
$sql .= " FROM " . MAIN_DB_PREFIX . "societe as s, " . MAIN_DB_PREFIX . "contrat as c";
$sql .= ", " . MAIN_DB_PREFIX . "contratdet as cd, " . MAIN_DB_PREFIX . "product as p";
$sql .= " WHERE s.entity IN (" . getEntity('societe', 1) . ")";
$sql .= " AND s.rowid = c.fk_soc AND cd.fk_contrat = c.rowid AND s.email != ''";
$sql .= " AND cd.statut= 4 AND cd.fk_product=p.rowid";
$sql .= " AND p.ref IN ('" . join("','", $this->arrayofproducts) . "')";
$sql .= " AND cd.date_fin_validite < '" . $this->db->idate($now) . "'";
$a = parent::getNbOfRecipients($sql);
return $a;
}
/**
* Return here number of distinct emails returned by your selector.
*
* @param string $sql Requete sql de comptage
* @return int Number of recipients
*/
function getNbOfRecipients($sql = '')
{
global $conf;
// We must report here number of contacts when absolutely no filter selected (so all contacts).
// Number with a filter are show in the combo list for each filter.
// If we want a filter "is inside at least one category", we must add it into formFilter
$sql = "SELECT count(distinct(c.email)) as nb";
$sql .= " FROM " . MAIN_DB_PREFIX . "socpeople as c";
$sql .= " WHERE c.entity IN (" . getEntity('societe', 1) . ")";
$sql .= " AND c.email != ''";
// Note that null != '' is false
$sql .= " AND c.no_email = 0";
/*
$sql = "SELECT count(distinct(sp.email)) as nb";
$sql.= " FROM ".MAIN_DB_PREFIX."socpeople as sp,";
$sql.= " ".MAIN_DB_PREFIX."societe as s,";
$sql.= " ".MAIN_DB_PREFIX."categorie as c,";
$sql.= " ".MAIN_DB_PREFIX."categorie_societe as cs";
$sql.= " WHERE s.rowid = sp.fk_soc";
$sql.= " AND sp.entity IN (".getEntity('societe', 1).")";
$sql.= " AND sp.email != ''"; // Note that null != '' is false
$sql.= " AND cs.fk_categorie = c.rowid";
$sql.= " AND cs.fk_societe = sp.fk_soc";
*/
// La requete doit retourner un champ "nb" pour etre comprise
// par parent::getNbOfRecipients
return parent::getNbOfRecipients($sql);
}
/**
* Ajoute destinataires dans table des cibles
*
* @param int $mailing_id Id of emailing
* @param array $filtersarray Requete sql de selection des destinataires
* @return int < 0 si erreur, nb ajout si ok
*/
function add_to_target($mailing_id, $filtersarray = array())
{
global $conf, $langs, $_FILES;
require_once DOL_DOCUMENT_ROOT . '/core/lib/files.lib.php';
$tmparray = explode(';', GETPOST('xinputuser'));
$email = $tmparray[0];
$lastname = $tmparray[1];
$firstname = $tmparray[2];
$other = $tmparray[3];
$cibles = array();
if (!empty($email)) {
if (isValidEMail($email)) {
$cibles[] = array('email' => $email, 'lastname' => $lastname, 'firstname' => $firstname, 'other' => $other, 'source_url' => '', 'source_id' => '', 'source_type' => 'file');
return parent::add_to_target($mailing_id, $cibles);
} else {
$langs->load("errors");
$this->error = $langs->trans("ErrorBadEMail", $email);
return -1;
}
} else {
$langs->load("errors");
$this->error = $langs->trans("ErrorBadEmail", $email);
return -1;
}
}
/**
* Ajoute destinataires dans table des cibles
*
* @param int $mailing_id Id of emailing
* @param array $filtersarray Requete sql de selection des destinataires
* @return int < 0 si erreur, nb ajout si ok
*/
function add_to_target($mailing_id, $filtersarray = array())
{
global $conf, $langs;
$langs->load("companies");
$cibles = array();
// La requete doit retourner: id, email, fk_contact, lastname, firstname
$sql = "SELECT u.rowid as id, u.email as email, null as fk_contact,";
$sql .= " u.lastname, u.firstname as firstname, u.civility as civility_id, u.login, u.office_phone";
$sql .= " FROM " . MAIN_DB_PREFIX . "user as u";
$sql .= " WHERE u.email <> ''";
// u.email IS NOT NULL est implicite dans ce test
$sql .= " AND u.entity IN (0," . $conf->entity . ")";
$sql .= " AND u.email NOT IN (SELECT email FROM " . MAIN_DB_PREFIX . "mailing_cibles WHERE fk_mailing=" . $mailing_id . ")";
foreach ($filtersarray as $key) {
if ($key == '1') {
$sql .= " AND u.statut=1";
}
if ($key == '0') {
$sql .= " AND u.statut=0";
}
}
$sql .= " ORDER BY u.email";
// Stocke destinataires dans cibles
$result = $this->db->query($sql);
if ($result) {
$num = $this->db->num_rows($result);
$i = 0;
$j = 0;
dol_syslog(get_class($this) . "::add_to_target mailing " . $num . " targets found");
$old = '';
while ($i < $num) {
$obj = $this->db->fetch_object($result);
if ($old != $obj->email) {
$cibles[$j] = array('email' => $obj->email, 'fk_contact' => $obj->fk_contact, 'lastname' => $obj->lastname, 'firstname' => $obj->firstname, 'other' => $langs->transnoentities("Login") . '=' . $obj->login . ';' . ($langs->transnoentities("UserTitle") . '=' . $obj->civility_id) . ';' . ($langs->transnoentities("PhonePro") . '=' . $obj->office_phone), 'source_url' => $this->url($obj->id), 'source_id' => $obj->id, 'source_type' => 'user');
$old = $obj->email;
$j++;
}
$i++;
}
} else {
dol_syslog($this->db->error());
$this->error = $this->db->error();
return -1;
}
return parent::add_to_target($mailing_id, $cibles);
}
/**
* \brief Return here number of distinct emails returned by your selector.
* For example if this selector is used to extract 500 different
* emails from a text file, this function must return 500.
* \return int
*/
function getNbOfRecipients($filter=1,$option='')
{
$now=dol_now();
// Example: return parent::getNbOfRecipients("SELECT count(*) as nb from dolibarr_table");
// Example: return 500;
$sql = " select count(*) as nb";
$sql.= " from ".MAIN_DB_PREFIX."societe as s, ".MAIN_DB_PREFIX."contrat as c,";
$sql.= " ".MAIN_DB_PREFIX."contratdet as cd, ".MAIN_DB_PREFIX."product as p";
$sql.= " where s.rowid = c.fk_soc AND cd.fk_contrat = c.rowid AND s.email != ''";
$sql.= " AND cd.statut= 4 AND cd.fk_product=p.rowid";
$sql.= " AND p.ref in ('".join("','",$this->arrayofproducts)."')";
$sql.= " AND cd.date_fin_validite < '".$this->db->idate($now)."'";
//print $sql;
$a=parent::getNbOfRecipients($sql);
return $a;
}
