vtiger CRM 5.0.2 : /modules/HelpDesk/Merge.php source

[Sommaire] [Imprimer]
   1  <?php
   2  /*********************************************************************************
   3  ** The contents of this file are subject to the vtiger CRM Public License Version 1.0
   4   * ("License"); You may not use this file except in compliance with the License
   5   * The Original Code is:  vtiger CRM Open Source
   6   * The Initial Developer of the Original Code is vtiger.
   7   * Portions created by vtiger are Copyright (C) vtiger.
   8   * All Rights Reserved.
   9  *
  10   ********************************************************************************/
  11  ?>
  12  <html>
  13  <body>
  14  <script>
  15  if (document.layers || (!document.all && document.getElementById))
  16  {
  17      document.write("This feature requires IE 5.5 or higher for Windows on Microsoft Windows 2000, Windows NT4 SP6, Windows XP.");
  18      document.write("<br><br>Click <a href='#' onclick='window.history.back();'>here</a> to return to the previous page");
  19  }
  20  else if(document.all)
  21  {
  22      document.write("<OBJECT Name='vtigerCRM' codebase='modules/Settings/vtigerCRM.CAB#version=1,5,0,0' id='objMMPage' classid='clsid:0FC436C2-2E62-46EF-A3FB-E68E94705126' width=0 height=0></object>");
  23  }
  24  </script>
  25  <?php
  26  
  27  require_once ('include/database/PearDatabase.php');
  28  require_once ('config.php');
  29  
  30  $templateid = $_REQUEST['mergefile'];
  31  
  32  if($templateid == "")
  33  {
  34      die("Select Mail Merge Template");
  35  }
  36  //get the particular file from db and store it in the local hard disk.
  37  //store the path to the location where the file is stored and pass it  as parameter to the method 
  38  $sql = "select filename,data,filesize from vtiger_wordtemplates where templateid=".$templateid;
  39  
  40  $result = $adb->query($sql);
  41  $temparray = $adb->fetch_array($result);
  42  
  43  $fileContent = $temparray['data'];
  44  $filename=$temparray['filename'];
  45  $filesize=$temparray['filesize'];
  46  $wordtemplatedownloadpath =$root_directory ."/test/wordtemplatedownload/";
  47  
  48  $handle = fopen($wordtemplatedownloadpath .$temparray['filename'],"wb");
  49  fwrite($handle,base64_decode($fileContent),$filesize);
  50  fclose($handle);
  51  
  52  //<<<<<<<<<<<<<<<<<<<<<<<<<<<for mass merge>>>>>>>>>>>>>>>>>>>>>>>>>>>
  53  $mass_merge = $_REQUEST['idlist'];
  54  $single_record = $_REQUEST['record'];
  55  
  56  if($mass_merge != "")
  57  {    
  58      $mass_merge = explode(";",$mass_merge);
  59      $temp_mass_merge = $mass_merge;
  60      if(array_pop($temp_mass_merge)=="")
  61          array_pop($mass_merge);
  62      $mass_merge = implode(",",$mass_merge);
  63  }
  64  else if($single_record != "")
  65  {
  66      $mass_merge = $single_record;    
  67  }
  68  else
  69  {
  70      die("Record Id is not found, cannot merge the document");
  71  }
  72  
  73  //<<<<<<<<<<<<<<<<header for csv and select columns for query>>>>>>>>>>>>>>>>>>>>>>>>
  74  $query1="select vtiger_tab.name,vtiger_field.tablename,vtiger_field.columnname,vtiger_field.fieldlabel from vtiger_field inner join vtiger_tab on vtiger_tab.tabid = vtiger_field.tabid where vtiger_field.tabid in (13,4,6) and vtiger_field.uitype <> 61 and (vtiger_field.tablename <>'CustomerDetails' and block <> 6 and block <> 75) and block <> 30 order by vtiger_field.tablename";
  75  
  76  $result = $adb->query($query1);
  77  $y=$adb->num_rows($result);
  78      
  79  for ($x=0; $x<$y; $x++)
  80  { 
  81      $tablename = $adb->query_result($result,$x,"tablename");
  82      $columnname = $adb->query_result($result,$x,"columnname");
  83      $modulename = $adb->query_result($result,$x,"name");
  84  
  85      $column_name = $tablename.".".$columnname;
  86  
  87      if($columnname == "parent_id")
  88      {
  89          $column_name = "case crmentityRelHelpDesk.setype when 'Accounts' then accountRelHelpDesk.accountname when 'Contacts' then concat(contactdetailsRelHelpDesk.firstname,' ',contactdetailsRelHelpDesk.lastname) End";
  90      }
  91      if($columnname == "product_id")
  92      {
  93          $column_name = "productsRel.productname";
  94      }
  95      if($tablename == "vtiger_crmentity")
  96      {
  97          if($modulename == "Contacts")
  98          {
  99              $tablename = "crmentityContacts";
 100              $column_name = $tablename.".".$columnname;
 101          }
 102          if($modulename == "Accounts")
 103          {
 104              $tablename = "crmentityAccounts";
 105              $column_name = $tablename.".".$columnname;
 106          }
 107  
 108      }
 109  
 110      if($columnname == "smownerid")
 111      {
 112          if($modulename == "Accounts")
 113          {
 114              $column_name = "concat(usersAccounts.last_name,' ',usersAccounts.first_name) as username";
 115          }
 116          if($modulename == "Contacts")
 117          {
 118              $column_name = "concat(usersContacts.last_name,' ',usersContacts.first_name) as usercname";
 119          }
 120          if($modulename == "HelpDesk")
 121          {
 122              $column_name = "concat(vtiger_users.last_name,' ',vtiger_users.first_name) as userhelpname,vtiger_users.first_name,vtiger_users.last_name,vtiger_users.user_name,vtiger_users.yahoo_id,vtiger_users.title,vtiger_users.phone_work,vtiger_users.department,vtiger_users.phone_mobile,vtiger_users.phone_other,vtiger_users.phone_fax,vtiger_users.email1,vtiger_users.phone_home,vtiger_users.email2,vtiger_users.address_street,vtiger_users.address_city,vtiger_users.address_state,vtiger_users.address_postalcode,vtiger_users.address_country";
 123          }
 124      }
 125      if($columnname == "parentid")
 126      {
 127          $column_name = "accountAccount.accountname";
 128      }
 129      if($columnname == "accountid")
 130      {
 131          $column_name = "accountContacts.accountname";
 132      }
 133      if($columnname == "reportsto")
 134      {
 135          $column_name = "contactdetailsContacts.lastname";
 136      }
 137  
 138      $querycolumns[$x] = $column_name;
 139  
 140      if($modulename == "Accounts")
 141      {
 142          $field_label[$x] = "ACCOUNT_".strtoupper(str_replace(" ","",$adb->query_result($result,$x,"fieldlabel")));
 143      }
 144      if($modulename == "Contacts")
 145      {
 146          $field_label[$x] = "CONTACT_".strtoupper(str_replace(" ","",$adb->query_result($result,$x,"fieldlabel")));
 147      }
 148      if($modulename == "HelpDesk")
 149      {
 150          $field_label[$x] = "TICKET_".strtoupper(str_replace(" ","",$adb->query_result($result,$x,"fieldlabel")));
 151          if($columnname == "smownerid")
 152          {
 153              $field_label[$x] = $field_label[$x].",USER_FIRSTNAME,USER_LASTNAME,USER_USERNAME,USER_YAHOOID,USER_TITLE,USER_OFFICEPHONE,USER_DEPARTMENT,USER_MOBILE,USER_OTHERPHONE,USER_FAX,USER_EMAIL,USER_HOMEPHONE,USER_OTHEREMAIL,USER_PRIMARYADDRESS,USER_CITY,USER_STATE,USER_POSTALCODE,USER_COUNTRY";
 154          }
 155      }
 156  
 157  }
 158  $csvheader = implode(",",$field_label);
 159  //<<<<<<<<<<<<<<<<End>>>>>>>>>>>>>>>>>>>>>>>>
 160  
 161  if(count($querycolumns) > 0)
 162  {
 163      $selectcolumns = implode($querycolumns,",");
 164  
 165      $query ="select ".$selectcolumns." from vtiger_troubletickets
 166              inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_troubletickets.ticketid
 167              inner join vtiger_ticketcf on vtiger_ticketcf.ticketid = vtiger_troubletickets.ticketid
 168              left join vtiger_crmentity as crmentityRelHelpDesk on crmentityRelHelpDesk.crmid = vtiger_troubletickets.parent_id
 169              left join vtiger_account as accountRelHelpDesk on accountRelHelpDesk.accountid=crmentityRelHelpDesk.crmid
 170              left join vtiger_contactdetails as contactdetailsRelHelpDesk on contactdetailsRelHelpDesk.contactid= crmentityRelHelpDesk.crmid
 171              left join vtiger_products as productsRel on productsRel.productid = vtiger_troubletickets.product_id
 172              left join vtiger_users on vtiger_crmentity.smownerid=vtiger_users.id
 173              left join vtiger_account on vtiger_account.accountid = vtiger_troubletickets.parent_id               
 174              left join vtiger_crmentity as crmentityAccounts on crmentityAccounts.crmid = vtiger_account.accountid 
 175              left join vtiger_accountbillads on vtiger_accountbillads.accountaddressid = vtiger_account.accountid
 176              left join vtiger_accountshipads on vtiger_accountshipads.accountaddressid = vtiger_account.accountid
 177              left join vtiger_accountscf on vtiger_accountbillads.accountaddressid = vtiger_accountscf.accountid 
 178              left join vtiger_account as accountAccount on accountAccount.accountid = vtiger_troubletickets.parent_id
 179              left join vtiger_users as usersAccounts on usersAccounts.id = crmentityAccounts.smownerid
 180              left join vtiger_contactdetails on vtiger_contactdetails.contactid = vtiger_troubletickets.parent_id               
 181              left join vtiger_crmentity as crmentityContacts on crmentityContacts.crmid = vtiger_contactdetails.contactid 
 182              left join vtiger_contactaddress on vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid 
 183              left join vtiger_contactsubdetails on vtiger_contactdetails.contactid = vtiger_contactsubdetails.contactsubscriptionid 
 184              left join vtiger_contactscf on vtiger_contactdetails.contactid = vtiger_contactscf.contactid 
 185              left join vtiger_contactdetails as contactdetailsContacts on contactdetailsContacts.contactid = vtiger_contactdetails.reportsto
 186              left join vtiger_account as accountContacts on accountContacts.accountid = vtiger_contactdetails.accountid 
 187              left join vtiger_users as usersContacts on usersContacts.id = crmentityContacts.smownerid
 188              where vtiger_crmentity.deleted=0 and ((crmentityContacts.deleted=0 || crmentityContacts.deleted is null)||(crmentityAccounts.deleted=0 || crmentityAccounts.deleted is null)) 
 189              and vtiger_troubletickets.ticketid in (".$mass_merge.")";
 190  
 191      $result = $adb->query($query);
 192  
 193      while($columnValues = $adb->fetch_array($result))
 194      {
 195          $y=$adb->num_fields($result);
 196          for($x=0; $x<$y; $x++)
 197          {
 198              $value = $columnValues[$x];
 199              //<<<<<<<<<<<<<<<for blank vtiger_fields>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 200              if($value == "0")
 201              {
 202                  $value = "";
 203              }
 204              if(trim($value) == "--None--" || trim($value) == "--none--")
 205              {
 206                  $value = "";
 207              }
 208              //<<<<<<<<<<<<<<<End>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 209              $actual_values[$x] = $value;
 210              $actual_values[$x] = str_replace('"'," ",$actual_values[$x]);
 211              //if value contains any line feed or carriage return replace the value with ".value."
 212              if (preg_match ("/(\r\n)/", $actual_values[$x])) 
 213              {
 214                  $actual_values[$x] = '"'.$actual_values[$x].'"';
 215              }
 216              $actual_values[$x] = str_replace(","," ",$actual_values[$x]);
 217          }
 218          $mergevalue[] = implode($actual_values,",");      
 219      }
 220      $csvdata = implode($mergevalue,"###");
 221  }
 222  else
 223  {
 224      die("No fields to do Merge");
 225  }    
 226  
 227  $handle = fopen($wordtemplatedownloadpath."datasrc.csv","wb");
 228  fwrite($handle,$csvheader."\r\n");
 229  fwrite($handle,str_replace("###","\r\n",$csvdata));
 230  fclose($handle);
 231  
 232  ?>
 233  <script>
 234  if (window.ActiveXObject)
 235  {
 236      try 
 237      {
 238            ovtigerVM = eval("new ActiveXObject('vtigerCRM.ActiveX');");
 239            if(ovtigerVM)
 240          {
 241              var filename = "<?php echo $filename?>";
 242              if(filename != "")
 243              {
 244                  if(objMMPage.bDLTempDoc("<?php echo $site_URL?>/test/wordtemplatedownload/<?php echo $filename?>","MMTemplate.doc"))
 245                  {
 246                      try
 247                      {
 248                          if(objMMPage.Init())
 249                          {
 250                              objMMPage.vLTemplateDoc();
 251                              objMMPage.bBulkHDSrc("<?php echo $site_URL;?>/test/wordtemplatedownload/datasrc.csv");
 252                              objMMPage.vBulkOpenDoc();
 253                              objMMPage.UnInit()
 254                                  window.history.back();
 255                          }        
 256                      }catch(errorObject)
 257                      {
 258                          document.write("Error while processing mail merge operation");
 259                      }
 260                  }
 261                  else
 262                  {
 263                      document.write("Cannot get template document");
 264                  }
 265              }
 266          }
 267      }
 268      catch(e)
 269      {
 270          document.write("Requires to download ActiveX Control from vtigerCRM. Please, ensure that you have administration privilage");
 271      }
 272  }
 273  </script>
 274  </body>
 275  </html>
Code source de vtiger CRM 5.0.2

/modules/HelpDesk/ -> Merge.php (source)
