Нужен эффективный во времени метод импорта большого файла CSV через PHP в несколько таблиц MySQL

Ладно, у меня здесь серьезные проблемы. Я новичок в этом сайте и новичок в области импорта CSV-данных через PHP, но я не новичок в программировании.

В настоящее время я работаю над созданием менеджера отношений с клиентами. Мне нужно создать скрипт для импорта файла, который будет заполнять базу данных выводами. Основной вопрос здесь заключается в том, что ведущие данные состоят из компаний и сотрудников указанной компании. Кроме того, из основных таблиц отделяются несколько других таблиц, таких как информация о выставлении счетов.

У меня есть рабочий скрипт, который позволит пользователям сопоставлять импортированные данные с определенными строками и столбцами.

function mapData($file) { // Open the Text File $fd = fopen($file, "r"); // Return FALSE if file not found if(!$fd) { return FALSE; } // Get the First Two Lines $first = 0; $data = array(); while(!feof($fd)) { if($first == 0) { $cols = fgetcsv($fd, 4096); $data['cols'] = array(); if(is_array($cols) && count($cols)) { foreach($cols as $col) { if(!$col) { continue; } $data['cols'][] = $col; } } if(empty($data['cols'])) { return array(); } $first++; continue; } else { $data['first'] = fgetcsv($fd, 4096); break; } } fclose($fd); // Return Data return $data; } 

Вышеупомянутый скрипт активируется только после того, как CodeIgniter перемещает файл в рабочий каталог. Я уже знаю, что это за имя файла. Файл входит и возвращает список столбцов и первую строку. Любые пустые столбцы игнорируются.

После этого процесс переходит к скрипту сопоставления. После того, как будет выполнено сопоставление и будет нажата «Импорт», эта часть кода загружается.

 function importLeads($file, $map) { // Open the Text File if(!file_exists($file)) { return false; } error_reporting(E_ALL); set_time_limit(240); ini_set("memory_limit", "512M"); $fd = fopen($file, "r"); // Return FALSE if file not found if(!$fd) { return FALSE; } // Traverse Each Line of the File $true = false; $first = 0; while(!feof($fd)) { if($first == 0) { $cols = fgetcsv($fd); $first++; continue; } // Get the columns of each line $row = fgetcsv($fd); // Traverse columns $group = array(); $lead_status = array(); $lead_type = array(); $lead_source = array(); $user = array(); $user_cstm = array(); $user_prof = array(); $acct = array(); $acct_cstm = array(); $acct_prof = array(); $acct_group = array(); if(!$row) { continue; } foreach($row as $num => $val) { if(empty($map[$num])) { continue; } $val = str_replace('"', """, $val); $val = str_replace("'", "'", $val); switch($map[$num]) { // Company Account case "company_name": $acct['company_name'] = $val; break; case "lead_type": $lead_type['name'] = $val; break; case "lead_source": $lead_source['name'] = $val; break; case "lead_source_description": $lead_source['name'] = $val; break; case "campaign": $campaign['name'] = $val; break; case "mcn": $acct['mcn'] = $val; break; case "usdot": $acct['usdot'] = $val; break; case "sic_codes": $acct_cstm['sic_codes'] = $val; break; case "naics_codes": $acct_cstm['naics_codes'] = $val; break; case "agent_assigned": $acct_cstm['agent_assigned'] = $val; break; case "group_assigned": $group['name'] = $val; break; case "rating": $acct_cstm['rating'] = $val; break; case "main_phone": $acct['phone'] = $val; break; case "billing_phone": $acct_cstm['billing_phone'] = $val; break; case "company_fax": $acct['fax'] = $val; break; case "company_email": $acct['email2'] = $val; break; // Company Location case "primary_address": $acct['address'] = $val; break; case "primary_address2": $acct['address2'] = $val; break; case "primary_city": $acct['city'] = $val; break; case "primary_state": $acct['state'] = $val; break; case "primary_zip": $acct['zip'] = $val; break; case "primary_country": $acct['country'] = $val; break; case "billing_address": $billing['address'] = $val; break; case "billing_address2": $billing['address2'] = $val; break; case "billing_city": $billing['city'] = $val; break; case "billing_state": $billing['state'] = $val; break; case "billing_zip": $billing['zip'] = $val; break; case "billing_country": $billing['country'] = $val; break; case "company_website": $acct_cstm['website'] = $val; break; case "company_revenue": $acct_cstm['revenue'] = $val; break; case "company_about": $acct_prof['aboutus'] = $val; break; // Misc. Company Data case "bols_per_mo": $acct_cstm['approx_bols_per_mo'] = $val; break; case "no_employees": $acct_cstm['no_employees'] = $val; break; case "no_drivers": $acct_prof['drivers'] = $val; break; case "no_trucks": $acct_prof['power_units'] = $val; break; case "no_trailers": $acct_cstm['no_trailers'] = $acct_prof['trailers'] = $val; break; case "no_parcels_day": $acct_cstm['no_parcels_day'] = $val; break; case "no_shipping_locations": $acct_cstm['no_shipping_locations'] = $val; break; case "approves_inbound": $acct_cstm['approves_inbound'] = $val; break; case "what_erp_used": $acct_cstm['what_erp_used'] = $val; break; case "birddog": $acct_cstm['birddog_referral'] = $val; break; case "status_notes": $acct_cstm['status_notes'] = $val; break; case "notes": $acct_cstm['notes'] = $val; break; case "internal_notes": $acct_cstm['notes_internal'] = $val; break; // User Data case "salutation": $user_cstm['salutation'] = $val; break; case "first_name": $user['first_name'] = $billing['first_name'] = $val; break; case "last_name": $user['last_name'] = $billing['last_name'] = $val; break; case "user_title": $user_prof['title'] = $val; break; case "user_about": $user_prof['about'] = $val; break; case "user_email": $user['email'] = $val; break; case "home_phone": $user_prof['phone'] = $val; break; case "mobile_phone": $user_cstm['mobile_phone'] = $val; break; case "direct_phone": $user_cstm['direct_phone'] = $val; break; case "user_fax": $user_prof['fax'] = $val; break; case "user_locale": $user['location'] = $val; break; case "user_website": $user_prof['website_url'] = $val; break; case "user_facebook": $user_prof['fb_url'] = $val; break; case "user_twitter": $user_prof['twitter_url'] = $val; break; case "user_linkedin": $user_prof['linkedin_url'] = $val; break; } } if(empty($acct['company_name']) || empty($user['first_name']) || empty($user['last_name'])) { continue; } $this->db = $this->load->database('crm_db', TRUE); if(isset($lead_type['name']) && ($name = $lead_type['name'])) { $count = $this->db->count_all("lead_types"); $check = $this->db->get_where("lead_types", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_types", array("name" => $name, "order" => $count)); $ltype = $this->db->insert_id(); $acct_cstm['lead_type'] = $acct['account_type'] = $user['company_type'] = $ltype; } } if(isset($lead_source['name']) && ($name = $lead_source['name'])) { $count = $this->db->count_all("lead_sources"); $check = $this->db->get_where("lead_sources", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_sources", array("name" => $name, "order" => $count)); $acct_cstm['lead_source'] = $this->db->insert_id(); } } if(isset($campaign['name']) && ($name = $campaign['name'])) { $check = $this->db->get_where("campaigns", array("name" => $name)); if($check->num_rows() < 1) { $campaign['id'] = $accounts_cstm['campaign'] = $this->Secure_m->generate_sugar_id(); $campaign['date_entered'] = time(); $campaign['date_modified'] = time(); $campaign['modified_user_id'] = $this->session->userdata('id'); $campaign['created_by'] = $this->session->userdata('id'); $this->db->insert("campaigns", $campaign); } } if(isset($group['name']) && ($name = $group['name'])) { $order = $this->db->count_all("groups"); $check = $this->db->get_where("groups", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("groups", array("name" => $name, "order" => $order)); $acct_group['id'] = $this->db->insert_id(); } } $mem = new stdclass; $uid = 0; if(is_array($user) && count($user)) { $where = ""; if(!empty($user['phone'])) { $where .= "prof.phone = '{$user['phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['phone']}' OR "; $where .= "cstm.direct_phone = '{$user['phone']}'"; } if(!empty($user['mobile_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['mobile_phone']}'"; } if(!empty($user['direct_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['direct_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['direct_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['direct_phone']}'"; } $query = $this->db->query($this->Account_m->userQuery($where)); $mem = reset($query->result()); if($where && !empty($mem->id)) { $uid = $mem->id; $new = array(); foreach($user as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads", $user, array("id" => $uid)); $user = $new; } else { $user['uxtime'] = time(); $user['isclient'] = 0; $user['flag'] = 0; $user['activation_code'] = $this->Secure_m->generate_activate_id(); $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); } $user['id'] = $uid; $this->db->insert("leads", $user); } } if($uid && is_array($user_prof) && count($user_prof)) { if(!empty($mem->uid)) { $new = array(); foreach($user_prof as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("mprofiles", $user_prof, array("uid" => $uid)); $user_prof = $new; } else { $user_prof['uid'] = $uid; $user_prof['flag'] = 0; $this->db->insert("ldetails", $user_prof); } } if($uid && is_array($user_cstm) && count($user_cstm)) { $query = $this->db->get_where("leads_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($user_cstm as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads_cstm", $acct_prof, array("fa_user_id" => $cid)); $user_cstm = $new; } else { $user_cstm['crm_id'] = $uid; $user_cstm['date_entered'] = time(); $user_cstm['date_modified'] = time(); $user_cstm['created_by'] = $this->session->userdata('id'); $user_cstm['modified_user_id'] = $this->session->userdata('id'); $this->db->insert("leads_cstm", $user_cstm); } } $cmp = new stdclass; $cid = 0; if(is_array($acct) && count($acct)) { $acct['uid'] = $uid; $acct['main_contact'] = "{$user['first_name']} {$user['last_name']}"; if(!empty($user['email'])) { $acct['email'] = $user['email']; } $acct['isprospect'] = 0; $acct['flag'] = 0; if(!empty($acct['mcn'])) { $where .= "fms.mcn = '{$acct['mcn']}'"; } if(!empty($acct['phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['phone']}' OR "; $where .= "crm.billing_phone = '{$acct['phone']}'"; } if(!empty($acct['billing_phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['billing_phone']}' OR "; $where .= "crm.billing_phone = '{$acct['billing_phone']}'"; } if(!empty($acct['company_name'])) { if($where) { $where .= " OR "; } $where .= "fms.company_name = '{$acct['company_name']}'"; } $query = $this->db->query($this->Account_m->acctQuery($where)); $cmp = reset($query->result()); if($where && !empty($cmp->id)) { $cid = $cmp->id; $new = array(); foreach($acct as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts", $billing, array("cid" => $cid)); $acct = $new; } else { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("accounts", array("id" => $cid), 1); $data = reset($query->result()); } $acct['id'] = $cid; $this->db->insert("accounts", $acct); } } if($cid && is_array($acct_group) && count($acct_group)) { $grp = $this->db->get_where("accounts_groups", array("cid" => $cid, "gid" => $acct_group['id'])); if(empty($cmp->id)) { $acct_group['cid'] = $cid; $this->db->insert("accounts_groups", $acct_group); } } if($cid && is_array($acct_prof) && count($acct_prof)) { if(!empty($cmp->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("cprofiles", $acct_prof, array("cid" => $cid)); $acct_prof = $new; } else { $acct_prof['cid'] = $cid; $acct_prof['flag'] = 0; $this->db->insert("adetails", $acct_prof); } } if($cid && is_array($billing) && count($billing)) { $bill = $this->db->get_where("accounts_billing", array("cid" => $cid)); if(!empty($bill->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_billing", $billing, array("cid" => $cid)); } else { $billing['cid'] = $cid; $billing['flag'] = 0; $this->db->insert("accounts_billing", $billing); } } if($cid && $uid) { $this->db->update("leads", array("cid" => $cid), array("id" => $uid)); } if($cid && is_array($acct_cstm) && count($acct_cstm)) { $query = $this->db->get_where("accounts_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($acct_cstm as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_cstm", $acct_cstm, array("crm_id" => $cid)); $acct_cstm = $new; } else { $acct_cstm['crm_id'] = $cid; $acct_cstm['date_entered'] = time(); $acct_cstm['date_modified'] = time(); $acct_cstm['created_by'] = $this->session->userdata('id'); $acct_cstm['modified_user_id'] = $this->session->userdata('id'); if(empty($acct_cstm['rating'])) { $acct_cstm['rating'] = 1; } $this->db->insert("accounts_cstm", $acct_cstm); } } $true = TRUE; } fclose($fd); return $true; } с function importLeads($file, $map) { // Open the Text File if(!file_exists($file)) { return false; } error_reporting(E_ALL); set_time_limit(240); ini_set("memory_limit", "512M"); $fd = fopen($file, "r"); // Return FALSE if file not found if(!$fd) { return FALSE; } // Traverse Each Line of the File $true = false; $first = 0; while(!feof($fd)) { if($first == 0) { $cols = fgetcsv($fd); $first++; continue; } // Get the columns of each line $row = fgetcsv($fd); // Traverse columns $group = array(); $lead_status = array(); $lead_type = array(); $lead_source = array(); $user = array(); $user_cstm = array(); $user_prof = array(); $acct = array(); $acct_cstm = array(); $acct_prof = array(); $acct_group = array(); if(!$row) { continue; } foreach($row as $num => $val) { if(empty($map[$num])) { continue; } $val = str_replace('"', """, $val); $val = str_replace("'", "'", $val); switch($map[$num]) { // Company Account case "company_name": $acct['company_name'] = $val; break; case "lead_type": $lead_type['name'] = $val; break; case "lead_source": $lead_source['name'] = $val; break; case "lead_source_description": $lead_source['name'] = $val; break; case "campaign": $campaign['name'] = $val; break; case "mcn": $acct['mcn'] = $val; break; case "usdot": $acct['usdot'] = $val; break; case "sic_codes": $acct_cstm['sic_codes'] = $val; break; case "naics_codes": $acct_cstm['naics_codes'] = $val; break; case "agent_assigned": $acct_cstm['agent_assigned'] = $val; break; case "group_assigned": $group['name'] = $val; break; case "rating": $acct_cstm['rating'] = $val; break; case "main_phone": $acct['phone'] = $val; break; case "billing_phone": $acct_cstm['billing_phone'] = $val; break; case "company_fax": $acct['fax'] = $val; break; case "company_email": $acct['email2'] = $val; break; // Company Location case "primary_address": $acct['address'] = $val; break; case "primary_address2": $acct['address2'] = $val; break; case "primary_city": $acct['city'] = $val; break; case "primary_state": $acct['state'] = $val; break; case "primary_zip": $acct['zip'] = $val; break; case "primary_country": $acct['country'] = $val; break; case "billing_address": $billing['address'] = $val; break; case "billing_address2": $billing['address2'] = $val; break; case "billing_city": $billing['city'] = $val; break; case "billing_state": $billing['state'] = $val; break; case "billing_zip": $billing['zip'] = $val; break; case "billing_country": $billing['country'] = $val; break; case "company_website": $acct_cstm['website'] = $val; break; case "company_revenue": $acct_cstm['revenue'] = $val; break; case "company_about": $acct_prof['aboutus'] = $val; break; // Misc. Company Data case "bols_per_mo": $acct_cstm['approx_bols_per_mo'] = $val; break; case "no_employees": $acct_cstm['no_employees'] = $val; break; case "no_drivers": $acct_prof['drivers'] = $val; break; case "no_trucks": $acct_prof['power_units'] = $val; break; case "no_trailers": $acct_cstm['no_trailers'] = $acct_prof['trailers'] = $val; break; case "no_parcels_day": $acct_cstm['no_parcels_day'] = $val; break; case "no_shipping_locations": $acct_cstm['no_shipping_locations'] = $val; break; case "approves_inbound": $acct_cstm['approves_inbound'] = $val; break; case "what_erp_used": $acct_cstm['what_erp_used'] = $val; break; case "birddog": $acct_cstm['birddog_referral'] = $val; break; case "status_notes": $acct_cstm['status_notes'] = $val; break; case "notes": $acct_cstm['notes'] = $val; break; case "internal_notes": $acct_cstm['notes_internal'] = $val; break; // User Data case "salutation": $user_cstm['salutation'] = $val; break; case "first_name": $user['first_name'] = $billing['first_name'] = $val; break; case "last_name": $user['last_name'] = $billing['last_name'] = $val; break; case "user_title": $user_prof['title'] = $val; break; case "user_about": $user_prof['about'] = $val; break; case "user_email": $user['email'] = $val; break; case "home_phone": $user_prof['phone'] = $val; break; case "mobile_phone": $user_cstm['mobile_phone'] = $val; break; case "direct_phone": $user_cstm['direct_phone'] = $val; break; case "user_fax": $user_prof['fax'] = $val; break; case "user_locale": $user['location'] = $val; break; case "user_website": $user_prof['website_url'] = $val; break; case "user_facebook": $user_prof['fb_url'] = $val; break; case "user_twitter": $user_prof['twitter_url'] = $val; break; case "user_linkedin": $user_prof['linkedin_url'] = $val; break; } } if(empty($acct['company_name']) || empty($user['first_name']) || empty($user['last_name'])) { continue; } $this->db = $this->load->database('crm_db', TRUE); if(isset($lead_type['name']) && ($name = $lead_type['name'])) { $count = $this->db->count_all("lead_types"); $check = $this->db->get_where("lead_types", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_types", array("name" => $name, "order" => $count)); $ltype = $this->db->insert_id(); $acct_cstm['lead_type'] = $acct['account_type'] = $user['company_type'] = $ltype; } } if(isset($lead_source['name']) && ($name = $lead_source['name'])) { $count = $this->db->count_all("lead_sources"); $check = $this->db->get_where("lead_sources", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_sources", array("name" => $name, "order" => $count)); $acct_cstm['lead_source'] = $this->db->insert_id(); } } if(isset($campaign['name']) && ($name = $campaign['name'])) { $check = $this->db->get_where("campaigns", array("name" => $name)); if($check->num_rows() < 1) { $campaign['id'] = $accounts_cstm['campaign'] = $this->Secure_m->generate_sugar_id(); $campaign['date_entered'] = time(); $campaign['date_modified'] = time(); $campaign['modified_user_id'] = $this->session->userdata('id'); $campaign['created_by'] = $this->session->userdata('id'); $this->db->insert("campaigns", $campaign); } } if(isset($group['name']) && ($name = $group['name'])) { $order = $this->db->count_all("groups"); $check = $this->db->get_where("groups", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("groups", array("name" => $name, "order" => $order)); $acct_group['id'] = $this->db->insert_id(); } } $mem = new stdclass; $uid = 0; if(is_array($user) && count($user)) { $where = ""; if(!empty($user['phone'])) { $where .= "prof.phone = '{$user['phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['phone']}' OR "; $where .= "cstm.direct_phone = '{$user['phone']}'"; } if(!empty($user['mobile_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['mobile_phone']}'"; } if(!empty($user['direct_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['direct_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['direct_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['direct_phone']}'"; } $query = $this->db->query($this->Account_m->userQuery($where)); $mem = reset($query->result()); if($where && !empty($mem->id)) { $uid = $mem->id; $new = array(); foreach($user as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads", $user, array("id" => $uid)); $user = $new; } else { $user['uxtime'] = time(); $user['isclient'] = 0; $user['flag'] = 0; $user['activation_code'] = $this->Secure_m->generate_activate_id(); $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); } $user['id'] = $uid; $this->db->insert("leads", $user); } } if($uid && is_array($user_prof) && count($user_prof)) { if(!empty($mem->uid)) { $new = array(); foreach($user_prof as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("mprofiles", $user_prof, array("uid" => $uid)); $user_prof = $new; } else { $user_prof['uid'] = $uid; $user_prof['flag'] = 0; $this->db->insert("ldetails", $user_prof); } } if($uid && is_array($user_cstm) && count($user_cstm)) { $query = $this->db->get_where("leads_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($user_cstm as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads_cstm", $acct_prof, array("fa_user_id" => $cid)); $user_cstm = $new; } else { $user_cstm['crm_id'] = $uid; $user_cstm['date_entered'] = time(); $user_cstm['date_modified'] = time(); $user_cstm['created_by'] = $this->session->userdata('id'); $user_cstm['modified_user_id'] = $this->session->userdata('id'); $this->db->insert("leads_cstm", $user_cstm); } } $cmp = new stdclass; $cid = 0; if(is_array($acct) && count($acct)) { $acct['uid'] = $uid; $acct['main_contact'] = "{$user['first_name']} {$user['last_name']}"; if(!empty($user['email'])) { $acct['email'] = $user['email']; } $acct['isprospect'] = 0; $acct['flag'] = 0; if(!empty($acct['mcn'])) { $where .= "fms.mcn = '{$acct['mcn']}'"; } if(!empty($acct['phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['phone']}' OR "; $where .= "crm.billing_phone = '{$acct['phone']}'"; } if(!empty($acct['billing_phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['billing_phone']}' OR "; $where .= "crm.billing_phone = '{$acct['billing_phone']}'"; } if(!empty($acct['company_name'])) { if($where) { $where .= " OR "; } $where .= "fms.company_name = '{$acct['company_name']}'"; } $query = $this->db->query($this->Account_m->acctQuery($where)); $cmp = reset($query->result()); if($where && !empty($cmp->id)) { $cid = $cmp->id; $new = array(); foreach($acct as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts", $billing, array("cid" => $cid)); $acct = $new; } else { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("accounts", array("id" => $cid), 1); $data = reset($query->result()); } $acct['id'] = $cid; $this->db->insert("accounts", $acct); } } if($cid && is_array($acct_group) && count($acct_group)) { $grp = $this->db->get_where("accounts_groups", array("cid" => $cid, "gid" => $acct_group['id'])); if(empty($cmp->id)) { $acct_group['cid'] = $cid; $this->db->insert("accounts_groups", $acct_group); } } if($cid && is_array($acct_prof) && count($acct_prof)) { if(!empty($cmp->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("cprofiles", $acct_prof, array("cid" => $cid)); $acct_prof = $new; } else { $acct_prof['cid'] = $cid; $acct_prof['flag'] = 0; $this->db->insert("adetails", $acct_prof); } } if($cid && is_array($billing) && count($billing)) { $bill = $this->db->get_where("accounts_billing", array("cid" => $cid)); if(!empty($bill->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_billing", $billing, array("cid" => $cid)); } else { $billing['cid'] = $cid; $billing['flag'] = 0; $this->db->insert("accounts_billing", $billing); } } if($cid && $uid) { $this->db->update("leads", array("cid" => $cid), array("id" => $uid)); } if($cid && is_array($acct_cstm) && count($acct_cstm)) { $query = $this->db->get_where("accounts_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($acct_cstm as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_cstm", $acct_cstm, array("crm_id" => $cid)); $acct_cstm = $new; } else { $acct_cstm['crm_id'] = $cid; $acct_cstm['date_entered'] = time(); $acct_cstm['date_modified'] = time(); $acct_cstm['created_by'] = $this->session->userdata('id'); $acct_cstm['modified_user_id'] = $this->session->userdata('id'); if(empty($acct_cstm['rating'])) { $acct_cstm['rating'] = 1; } $this->db->insert("accounts_cstm", $acct_cstm); } } $true = TRUE; } fclose($fd); return $true; } с function importLeads($file, $map) { // Open the Text File if(!file_exists($file)) { return false; } error_reporting(E_ALL); set_time_limit(240); ini_set("memory_limit", "512M"); $fd = fopen($file, "r"); // Return FALSE if file not found if(!$fd) { return FALSE; } // Traverse Each Line of the File $true = false; $first = 0; while(!feof($fd)) { if($first == 0) { $cols = fgetcsv($fd); $first++; continue; } // Get the columns of each line $row = fgetcsv($fd); // Traverse columns $group = array(); $lead_status = array(); $lead_type = array(); $lead_source = array(); $user = array(); $user_cstm = array(); $user_prof = array(); $acct = array(); $acct_cstm = array(); $acct_prof = array(); $acct_group = array(); if(!$row) { continue; } foreach($row as $num => $val) { if(empty($map[$num])) { continue; } $val = str_replace('"', """, $val); $val = str_replace("'", "'", $val); switch($map[$num]) { // Company Account case "company_name": $acct['company_name'] = $val; break; case "lead_type": $lead_type['name'] = $val; break; case "lead_source": $lead_source['name'] = $val; break; case "lead_source_description": $lead_source['name'] = $val; break; case "campaign": $campaign['name'] = $val; break; case "mcn": $acct['mcn'] = $val; break; case "usdot": $acct['usdot'] = $val; break; case "sic_codes": $acct_cstm['sic_codes'] = $val; break; case "naics_codes": $acct_cstm['naics_codes'] = $val; break; case "agent_assigned": $acct_cstm['agent_assigned'] = $val; break; case "group_assigned": $group['name'] = $val; break; case "rating": $acct_cstm['rating'] = $val; break; case "main_phone": $acct['phone'] = $val; break; case "billing_phone": $acct_cstm['billing_phone'] = $val; break; case "company_fax": $acct['fax'] = $val; break; case "company_email": $acct['email2'] = $val; break; // Company Location case "primary_address": $acct['address'] = $val; break; case "primary_address2": $acct['address2'] = $val; break; case "primary_city": $acct['city'] = $val; break; case "primary_state": $acct['state'] = $val; break; case "primary_zip": $acct['zip'] = $val; break; case "primary_country": $acct['country'] = $val; break; case "billing_address": $billing['address'] = $val; break; case "billing_address2": $billing['address2'] = $val; break; case "billing_city": $billing['city'] = $val; break; case "billing_state": $billing['state'] = $val; break; case "billing_zip": $billing['zip'] = $val; break; case "billing_country": $billing['country'] = $val; break; case "company_website": $acct_cstm['website'] = $val; break; case "company_revenue": $acct_cstm['revenue'] = $val; break; case "company_about": $acct_prof['aboutus'] = $val; break; // Misc. Company Data case "bols_per_mo": $acct_cstm['approx_bols_per_mo'] = $val; break; case "no_employees": $acct_cstm['no_employees'] = $val; break; case "no_drivers": $acct_prof['drivers'] = $val; break; case "no_trucks": $acct_prof['power_units'] = $val; break; case "no_trailers": $acct_cstm['no_trailers'] = $acct_prof['trailers'] = $val; break; case "no_parcels_day": $acct_cstm['no_parcels_day'] = $val; break; case "no_shipping_locations": $acct_cstm['no_shipping_locations'] = $val; break; case "approves_inbound": $acct_cstm['approves_inbound'] = $val; break; case "what_erp_used": $acct_cstm['what_erp_used'] = $val; break; case "birddog": $acct_cstm['birddog_referral'] = $val; break; case "status_notes": $acct_cstm['status_notes'] = $val; break; case "notes": $acct_cstm['notes'] = $val; break; case "internal_notes": $acct_cstm['notes_internal'] = $val; break; // User Data case "salutation": $user_cstm['salutation'] = $val; break; case "first_name": $user['first_name'] = $billing['first_name'] = $val; break; case "last_name": $user['last_name'] = $billing['last_name'] = $val; break; case "user_title": $user_prof['title'] = $val; break; case "user_about": $user_prof['about'] = $val; break; case "user_email": $user['email'] = $val; break; case "home_phone": $user_prof['phone'] = $val; break; case "mobile_phone": $user_cstm['mobile_phone'] = $val; break; case "direct_phone": $user_cstm['direct_phone'] = $val; break; case "user_fax": $user_prof['fax'] = $val; break; case "user_locale": $user['location'] = $val; break; case "user_website": $user_prof['website_url'] = $val; break; case "user_facebook": $user_prof['fb_url'] = $val; break; case "user_twitter": $user_prof['twitter_url'] = $val; break; case "user_linkedin": $user_prof['linkedin_url'] = $val; break; } } if(empty($acct['company_name']) || empty($user['first_name']) || empty($user['last_name'])) { continue; } $this->db = $this->load->database('crm_db', TRUE); if(isset($lead_type['name']) && ($name = $lead_type['name'])) { $count = $this->db->count_all("lead_types"); $check = $this->db->get_where("lead_types", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_types", array("name" => $name, "order" => $count)); $ltype = $this->db->insert_id(); $acct_cstm['lead_type'] = $acct['account_type'] = $user['company_type'] = $ltype; } } if(isset($lead_source['name']) && ($name = $lead_source['name'])) { $count = $this->db->count_all("lead_sources"); $check = $this->db->get_where("lead_sources", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_sources", array("name" => $name, "order" => $count)); $acct_cstm['lead_source'] = $this->db->insert_id(); } } if(isset($campaign['name']) && ($name = $campaign['name'])) { $check = $this->db->get_where("campaigns", array("name" => $name)); if($check->num_rows() < 1) { $campaign['id'] = $accounts_cstm['campaign'] = $this->Secure_m->generate_sugar_id(); $campaign['date_entered'] = time(); $campaign['date_modified'] = time(); $campaign['modified_user_id'] = $this->session->userdata('id'); $campaign['created_by'] = $this->session->userdata('id'); $this->db->insert("campaigns", $campaign); } } if(isset($group['name']) && ($name = $group['name'])) { $order = $this->db->count_all("groups"); $check = $this->db->get_where("groups", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("groups", array("name" => $name, "order" => $order)); $acct_group['id'] = $this->db->insert_id(); } } $mem = new stdclass; $uid = 0; if(is_array($user) && count($user)) { $where = ""; if(!empty($user['phone'])) { $where .= "prof.phone = '{$user['phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['phone']}' OR "; $where .= "cstm.direct_phone = '{$user['phone']}'"; } if(!empty($user['mobile_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['mobile_phone']}'"; } if(!empty($user['direct_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['direct_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['direct_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['direct_phone']}'"; } $query = $this->db->query($this->Account_m->userQuery($where)); $mem = reset($query->result()); if($where && !empty($mem->id)) { $uid = $mem->id; $new = array(); foreach($user as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads", $user, array("id" => $uid)); $user = $new; } else { $user['uxtime'] = time(); $user['isclient'] = 0; $user['flag'] = 0; $user['activation_code'] = $this->Secure_m->generate_activate_id(); $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); } $user['id'] = $uid; $this->db->insert("leads", $user); } } if($uid && is_array($user_prof) && count($user_prof)) { if(!empty($mem->uid)) { $new = array(); foreach($user_prof as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("mprofiles", $user_prof, array("uid" => $uid)); $user_prof = $new; } else { $user_prof['uid'] = $uid; $user_prof['flag'] = 0; $this->db->insert("ldetails", $user_prof); } } if($uid && is_array($user_cstm) && count($user_cstm)) { $query = $this->db->get_where("leads_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($user_cstm as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads_cstm", $acct_prof, array("fa_user_id" => $cid)); $user_cstm = $new; } else { $user_cstm['crm_id'] = $uid; $user_cstm['date_entered'] = time(); $user_cstm['date_modified'] = time(); $user_cstm['created_by'] = $this->session->userdata('id'); $user_cstm['modified_user_id'] = $this->session->userdata('id'); $this->db->insert("leads_cstm", $user_cstm); } } $cmp = new stdclass; $cid = 0; if(is_array($acct) && count($acct)) { $acct['uid'] = $uid; $acct['main_contact'] = "{$user['first_name']} {$user['last_name']}"; if(!empty($user['email'])) { $acct['email'] = $user['email']; } $acct['isprospect'] = 0; $acct['flag'] = 0; if(!empty($acct['mcn'])) { $where .= "fms.mcn = '{$acct['mcn']}'"; } if(!empty($acct['phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['phone']}' OR "; $where .= "crm.billing_phone = '{$acct['phone']}'"; } if(!empty($acct['billing_phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['billing_phone']}' OR "; $where .= "crm.billing_phone = '{$acct['billing_phone']}'"; } if(!empty($acct['company_name'])) { if($where) { $where .= " OR "; } $where .= "fms.company_name = '{$acct['company_name']}'"; } $query = $this->db->query($this->Account_m->acctQuery($where)); $cmp = reset($query->result()); if($where && !empty($cmp->id)) { $cid = $cmp->id; $new = array(); foreach($acct as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts", $billing, array("cid" => $cid)); $acct = $new; } else { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("accounts", array("id" => $cid), 1); $data = reset($query->result()); } $acct['id'] = $cid; $this->db->insert("accounts", $acct); } } if($cid && is_array($acct_group) && count($acct_group)) { $grp = $this->db->get_where("accounts_groups", array("cid" => $cid, "gid" => $acct_group['id'])); if(empty($cmp->id)) { $acct_group['cid'] = $cid; $this->db->insert("accounts_groups", $acct_group); } } if($cid && is_array($acct_prof) && count($acct_prof)) { if(!empty($cmp->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("cprofiles", $acct_prof, array("cid" => $cid)); $acct_prof = $new; } else { $acct_prof['cid'] = $cid; $acct_prof['flag'] = 0; $this->db->insert("adetails", $acct_prof); } } if($cid && is_array($billing) && count($billing)) { $bill = $this->db->get_where("accounts_billing", array("cid" => $cid)); if(!empty($bill->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_billing", $billing, array("cid" => $cid)); } else { $billing['cid'] = $cid; $billing['flag'] = 0; $this->db->insert("accounts_billing", $billing); } } if($cid && $uid) { $this->db->update("leads", array("cid" => $cid), array("id" => $uid)); } if($cid && is_array($acct_cstm) && count($acct_cstm)) { $query = $this->db->get_where("accounts_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($acct_cstm as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_cstm", $acct_cstm, array("crm_id" => $cid)); $acct_cstm = $new; } else { $acct_cstm['crm_id'] = $cid; $acct_cstm['date_entered'] = time(); $acct_cstm['date_modified'] = time(); $acct_cstm['created_by'] = $this->session->userdata('id'); $acct_cstm['modified_user_id'] = $this->session->userdata('id'); if(empty($acct_cstm['rating'])) { $acct_cstm['rating'] = 1; } $this->db->insert("accounts_cstm", $acct_cstm); } } $true = TRUE; } fclose($fd); return $true; } с function importLeads($file, $map) { // Open the Text File if(!file_exists($file)) { return false; } error_reporting(E_ALL); set_time_limit(240); ini_set("memory_limit", "512M"); $fd = fopen($file, "r"); // Return FALSE if file not found if(!$fd) { return FALSE; } // Traverse Each Line of the File $true = false; $first = 0; while(!feof($fd)) { if($first == 0) { $cols = fgetcsv($fd); $first++; continue; } // Get the columns of each line $row = fgetcsv($fd); // Traverse columns $group = array(); $lead_status = array(); $lead_type = array(); $lead_source = array(); $user = array(); $user_cstm = array(); $user_prof = array(); $acct = array(); $acct_cstm = array(); $acct_prof = array(); $acct_group = array(); if(!$row) { continue; } foreach($row as $num => $val) { if(empty($map[$num])) { continue; } $val = str_replace('"', """, $val); $val = str_replace("'", "'", $val); switch($map[$num]) { // Company Account case "company_name": $acct['company_name'] = $val; break; case "lead_type": $lead_type['name'] = $val; break; case "lead_source": $lead_source['name'] = $val; break; case "lead_source_description": $lead_source['name'] = $val; break; case "campaign": $campaign['name'] = $val; break; case "mcn": $acct['mcn'] = $val; break; case "usdot": $acct['usdot'] = $val; break; case "sic_codes": $acct_cstm['sic_codes'] = $val; break; case "naics_codes": $acct_cstm['naics_codes'] = $val; break; case "agent_assigned": $acct_cstm['agent_assigned'] = $val; break; case "group_assigned": $group['name'] = $val; break; case "rating": $acct_cstm['rating'] = $val; break; case "main_phone": $acct['phone'] = $val; break; case "billing_phone": $acct_cstm['billing_phone'] = $val; break; case "company_fax": $acct['fax'] = $val; break; case "company_email": $acct['email2'] = $val; break; // Company Location case "primary_address": $acct['address'] = $val; break; case "primary_address2": $acct['address2'] = $val; break; case "primary_city": $acct['city'] = $val; break; case "primary_state": $acct['state'] = $val; break; case "primary_zip": $acct['zip'] = $val; break; case "primary_country": $acct['country'] = $val; break; case "billing_address": $billing['address'] = $val; break; case "billing_address2": $billing['address2'] = $val; break; case "billing_city": $billing['city'] = $val; break; case "billing_state": $billing['state'] = $val; break; case "billing_zip": $billing['zip'] = $val; break; case "billing_country": $billing['country'] = $val; break; case "company_website": $acct_cstm['website'] = $val; break; case "company_revenue": $acct_cstm['revenue'] = $val; break; case "company_about": $acct_prof['aboutus'] = $val; break; // Misc. Company Data case "bols_per_mo": $acct_cstm['approx_bols_per_mo'] = $val; break; case "no_employees": $acct_cstm['no_employees'] = $val; break; case "no_drivers": $acct_prof['drivers'] = $val; break; case "no_trucks": $acct_prof['power_units'] = $val; break; case "no_trailers": $acct_cstm['no_trailers'] = $acct_prof['trailers'] = $val; break; case "no_parcels_day": $acct_cstm['no_parcels_day'] = $val; break; case "no_shipping_locations": $acct_cstm['no_shipping_locations'] = $val; break; case "approves_inbound": $acct_cstm['approves_inbound'] = $val; break; case "what_erp_used": $acct_cstm['what_erp_used'] = $val; break; case "birddog": $acct_cstm['birddog_referral'] = $val; break; case "status_notes": $acct_cstm['status_notes'] = $val; break; case "notes": $acct_cstm['notes'] = $val; break; case "internal_notes": $acct_cstm['notes_internal'] = $val; break; // User Data case "salutation": $user_cstm['salutation'] = $val; break; case "first_name": $user['first_name'] = $billing['first_name'] = $val; break; case "last_name": $user['last_name'] = $billing['last_name'] = $val; break; case "user_title": $user_prof['title'] = $val; break; case "user_about": $user_prof['about'] = $val; break; case "user_email": $user['email'] = $val; break; case "home_phone": $user_prof['phone'] = $val; break; case "mobile_phone": $user_cstm['mobile_phone'] = $val; break; case "direct_phone": $user_cstm['direct_phone'] = $val; break; case "user_fax": $user_prof['fax'] = $val; break; case "user_locale": $user['location'] = $val; break; case "user_website": $user_prof['website_url'] = $val; break; case "user_facebook": $user_prof['fb_url'] = $val; break; case "user_twitter": $user_prof['twitter_url'] = $val; break; case "user_linkedin": $user_prof['linkedin_url'] = $val; break; } } if(empty($acct['company_name']) || empty($user['first_name']) || empty($user['last_name'])) { continue; } $this->db = $this->load->database('crm_db', TRUE); if(isset($lead_type['name']) && ($name = $lead_type['name'])) { $count = $this->db->count_all("lead_types"); $check = $this->db->get_where("lead_types", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_types", array("name" => $name, "order" => $count)); $ltype = $this->db->insert_id(); $acct_cstm['lead_type'] = $acct['account_type'] = $user['company_type'] = $ltype; } } if(isset($lead_source['name']) && ($name = $lead_source['name'])) { $count = $this->db->count_all("lead_sources"); $check = $this->db->get_where("lead_sources", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_sources", array("name" => $name, "order" => $count)); $acct_cstm['lead_source'] = $this->db->insert_id(); } } if(isset($campaign['name']) && ($name = $campaign['name'])) { $check = $this->db->get_where("campaigns", array("name" => $name)); if($check->num_rows() < 1) { $campaign['id'] = $accounts_cstm['campaign'] = $this->Secure_m->generate_sugar_id(); $campaign['date_entered'] = time(); $campaign['date_modified'] = time(); $campaign['modified_user_id'] = $this->session->userdata('id'); $campaign['created_by'] = $this->session->userdata('id'); $this->db->insert("campaigns", $campaign); } } if(isset($group['name']) && ($name = $group['name'])) { $order = $this->db->count_all("groups"); $check = $this->db->get_where("groups", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("groups", array("name" => $name, "order" => $order)); $acct_group['id'] = $this->db->insert_id(); } } $mem = new stdclass; $uid = 0; if(is_array($user) && count($user)) { $where = ""; if(!empty($user['phone'])) { $where .= "prof.phone = '{$user['phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['phone']}' OR "; $where .= "cstm.direct_phone = '{$user['phone']}'"; } if(!empty($user['mobile_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['mobile_phone']}'"; } if(!empty($user['direct_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['direct_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['direct_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['direct_phone']}'"; } $query = $this->db->query($this->Account_m->userQuery($where)); $mem = reset($query->result()); if($where && !empty($mem->id)) { $uid = $mem->id; $new = array(); foreach($user as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads", $user, array("id" => $uid)); $user = $new; } else { $user['uxtime'] = time(); $user['isclient'] = 0; $user['flag'] = 0; $user['activation_code'] = $this->Secure_m->generate_activate_id(); $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); } $user['id'] = $uid; $this->db->insert("leads", $user); } } if($uid && is_array($user_prof) && count($user_prof)) { if(!empty($mem->uid)) { $new = array(); foreach($user_prof as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("mprofiles", $user_prof, array("uid" => $uid)); $user_prof = $new; } else { $user_prof['uid'] = $uid; $user_prof['flag'] = 0; $this->db->insert("ldetails", $user_prof); } } if($uid && is_array($user_cstm) && count($user_cstm)) { $query = $this->db->get_where("leads_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($user_cstm as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads_cstm", $acct_prof, array("fa_user_id" => $cid)); $user_cstm = $new; } else { $user_cstm['crm_id'] = $uid; $user_cstm['date_entered'] = time(); $user_cstm['date_modified'] = time(); $user_cstm['created_by'] = $this->session->userdata('id'); $user_cstm['modified_user_id'] = $this->session->userdata('id'); $this->db->insert("leads_cstm", $user_cstm); } } $cmp = new stdclass; $cid = 0; if(is_array($acct) && count($acct)) { $acct['uid'] = $uid; $acct['main_contact'] = "{$user['first_name']} {$user['last_name']}"; if(!empty($user['email'])) { $acct['email'] = $user['email']; } $acct['isprospect'] = 0; $acct['flag'] = 0; if(!empty($acct['mcn'])) { $where .= "fms.mcn = '{$acct['mcn']}'"; } if(!empty($acct['phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['phone']}' OR "; $where .= "crm.billing_phone = '{$acct['phone']}'"; } if(!empty($acct['billing_phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['billing_phone']}' OR "; $where .= "crm.billing_phone = '{$acct['billing_phone']}'"; } if(!empty($acct['company_name'])) { if($where) { $where .= " OR "; } $where .= "fms.company_name = '{$acct['company_name']}'"; } $query = $this->db->query($this->Account_m->acctQuery($where)); $cmp = reset($query->result()); if($where && !empty($cmp->id)) { $cid = $cmp->id; $new = array(); foreach($acct as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts", $billing, array("cid" => $cid)); $acct = $new; } else { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("accounts", array("id" => $cid), 1); $data = reset($query->result()); } $acct['id'] = $cid; $this->db->insert("accounts", $acct); } } if($cid && is_array($acct_group) && count($acct_group)) { $grp = $this->db->get_where("accounts_groups", array("cid" => $cid, "gid" => $acct_group['id'])); if(empty($cmp->id)) { $acct_group['cid'] = $cid; $this->db->insert("accounts_groups", $acct_group); } } if($cid && is_array($acct_prof) && count($acct_prof)) { if(!empty($cmp->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("cprofiles", $acct_prof, array("cid" => $cid)); $acct_prof = $new; } else { $acct_prof['cid'] = $cid; $acct_prof['flag'] = 0; $this->db->insert("adetails", $acct_prof); } } if($cid && is_array($billing) && count($billing)) { $bill = $this->db->get_where("accounts_billing", array("cid" => $cid)); if(!empty($bill->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_billing", $billing, array("cid" => $cid)); } else { $billing['cid'] = $cid; $billing['flag'] = 0; $this->db->insert("accounts_billing", $billing); } } if($cid && $uid) { $this->db->update("leads", array("cid" => $cid), array("id" => $uid)); } if($cid && is_array($acct_cstm) && count($acct_cstm)) { $query = $this->db->get_where("accounts_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($acct_cstm as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_cstm", $acct_cstm, array("crm_id" => $cid)); $acct_cstm = $new; } else { $acct_cstm['crm_id'] = $cid; $acct_cstm['date_entered'] = time(); $acct_cstm['date_modified'] = time(); $acct_cstm['created_by'] = $this->session->userdata('id'); $acct_cstm['modified_user_id'] = $this->session->userdata('id'); if(empty($acct_cstm['rating'])) { $acct_cstm['rating'] = 1; } $this->db->insert("accounts_cstm", $acct_cstm); } } $true = TRUE; } fclose($fd); return $true; } с function importLeads($file, $map) { // Open the Text File if(!file_exists($file)) { return false; } error_reporting(E_ALL); set_time_limit(240); ini_set("memory_limit", "512M"); $fd = fopen($file, "r"); // Return FALSE if file not found if(!$fd) { return FALSE; } // Traverse Each Line of the File $true = false; $first = 0; while(!feof($fd)) { if($first == 0) { $cols = fgetcsv($fd); $first++; continue; } // Get the columns of each line $row = fgetcsv($fd); // Traverse columns $group = array(); $lead_status = array(); $lead_type = array(); $lead_source = array(); $user = array(); $user_cstm = array(); $user_prof = array(); $acct = array(); $acct_cstm = array(); $acct_prof = array(); $acct_group = array(); if(!$row) { continue; } foreach($row as $num => $val) { if(empty($map[$num])) { continue; } $val = str_replace('"', """, $val); $val = str_replace("'", "'", $val); switch($map[$num]) { // Company Account case "company_name": $acct['company_name'] = $val; break; case "lead_type": $lead_type['name'] = $val; break; case "lead_source": $lead_source['name'] = $val; break; case "lead_source_description": $lead_source['name'] = $val; break; case "campaign": $campaign['name'] = $val; break; case "mcn": $acct['mcn'] = $val; break; case "usdot": $acct['usdot'] = $val; break; case "sic_codes": $acct_cstm['sic_codes'] = $val; break; case "naics_codes": $acct_cstm['naics_codes'] = $val; break; case "agent_assigned": $acct_cstm['agent_assigned'] = $val; break; case "group_assigned": $group['name'] = $val; break; case "rating": $acct_cstm['rating'] = $val; break; case "main_phone": $acct['phone'] = $val; break; case "billing_phone": $acct_cstm['billing_phone'] = $val; break; case "company_fax": $acct['fax'] = $val; break; case "company_email": $acct['email2'] = $val; break; // Company Location case "primary_address": $acct['address'] = $val; break; case "primary_address2": $acct['address2'] = $val; break; case "primary_city": $acct['city'] = $val; break; case "primary_state": $acct['state'] = $val; break; case "primary_zip": $acct['zip'] = $val; break; case "primary_country": $acct['country'] = $val; break; case "billing_address": $billing['address'] = $val; break; case "billing_address2": $billing['address2'] = $val; break; case "billing_city": $billing['city'] = $val; break; case "billing_state": $billing['state'] = $val; break; case "billing_zip": $billing['zip'] = $val; break; case "billing_country": $billing['country'] = $val; break; case "company_website": $acct_cstm['website'] = $val; break; case "company_revenue": $acct_cstm['revenue'] = $val; break; case "company_about": $acct_prof['aboutus'] = $val; break; // Misc. Company Data case "bols_per_mo": $acct_cstm['approx_bols_per_mo'] = $val; break; case "no_employees": $acct_cstm['no_employees'] = $val; break; case "no_drivers": $acct_prof['drivers'] = $val; break; case "no_trucks": $acct_prof['power_units'] = $val; break; case "no_trailers": $acct_cstm['no_trailers'] = $acct_prof['trailers'] = $val; break; case "no_parcels_day": $acct_cstm['no_parcels_day'] = $val; break; case "no_shipping_locations": $acct_cstm['no_shipping_locations'] = $val; break; case "approves_inbound": $acct_cstm['approves_inbound'] = $val; break; case "what_erp_used": $acct_cstm['what_erp_used'] = $val; break; case "birddog": $acct_cstm['birddog_referral'] = $val; break; case "status_notes": $acct_cstm['status_notes'] = $val; break; case "notes": $acct_cstm['notes'] = $val; break; case "internal_notes": $acct_cstm['notes_internal'] = $val; break; // User Data case "salutation": $user_cstm['salutation'] = $val; break; case "first_name": $user['first_name'] = $billing['first_name'] = $val; break; case "last_name": $user['last_name'] = $billing['last_name'] = $val; break; case "user_title": $user_prof['title'] = $val; break; case "user_about": $user_prof['about'] = $val; break; case "user_email": $user['email'] = $val; break; case "home_phone": $user_prof['phone'] = $val; break; case "mobile_phone": $user_cstm['mobile_phone'] = $val; break; case "direct_phone": $user_cstm['direct_phone'] = $val; break; case "user_fax": $user_prof['fax'] = $val; break; case "user_locale": $user['location'] = $val; break; case "user_website": $user_prof['website_url'] = $val; break; case "user_facebook": $user_prof['fb_url'] = $val; break; case "user_twitter": $user_prof['twitter_url'] = $val; break; case "user_linkedin": $user_prof['linkedin_url'] = $val; break; } } if(empty($acct['company_name']) || empty($user['first_name']) || empty($user['last_name'])) { continue; } $this->db = $this->load->database('crm_db', TRUE); if(isset($lead_type['name']) && ($name = $lead_type['name'])) { $count = $this->db->count_all("lead_types"); $check = $this->db->get_where("lead_types", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_types", array("name" => $name, "order" => $count)); $ltype = $this->db->insert_id(); $acct_cstm['lead_type'] = $acct['account_type'] = $user['company_type'] = $ltype; } } if(isset($lead_source['name']) && ($name = $lead_source['name'])) { $count = $this->db->count_all("lead_sources"); $check = $this->db->get_where("lead_sources", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_sources", array("name" => $name, "order" => $count)); $acct_cstm['lead_source'] = $this->db->insert_id(); } } if(isset($campaign['name']) && ($name = $campaign['name'])) { $check = $this->db->get_where("campaigns", array("name" => $name)); if($check->num_rows() < 1) { $campaign['id'] = $accounts_cstm['campaign'] = $this->Secure_m->generate_sugar_id(); $campaign['date_entered'] = time(); $campaign['date_modified'] = time(); $campaign['modified_user_id'] = $this->session->userdata('id'); $campaign['created_by'] = $this->session->userdata('id'); $this->db->insert("campaigns", $campaign); } } if(isset($group['name']) && ($name = $group['name'])) { $order = $this->db->count_all("groups"); $check = $this->db->get_where("groups", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("groups", array("name" => $name, "order" => $order)); $acct_group['id'] = $this->db->insert_id(); } } $mem = new stdclass; $uid = 0; if(is_array($user) && count($user)) { $where = ""; if(!empty($user['phone'])) { $where .= "prof.phone = '{$user['phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['phone']}' OR "; $where .= "cstm.direct_phone = '{$user['phone']}'"; } if(!empty($user['mobile_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['mobile_phone']}'"; } if(!empty($user['direct_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['direct_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['direct_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['direct_phone']}'"; } $query = $this->db->query($this->Account_m->userQuery($where)); $mem = reset($query->result()); if($where && !empty($mem->id)) { $uid = $mem->id; $new = array(); foreach($user as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads", $user, array("id" => $uid)); $user = $new; } else { $user['uxtime'] = time(); $user['isclient'] = 0; $user['flag'] = 0; $user['activation_code'] = $this->Secure_m->generate_activate_id(); $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); } $user['id'] = $uid; $this->db->insert("leads", $user); } } if($uid && is_array($user_prof) && count($user_prof)) { if(!empty($mem->uid)) { $new = array(); foreach($user_prof as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("mprofiles", $user_prof, array("uid" => $uid)); $user_prof = $new; } else { $user_prof['uid'] = $uid; $user_prof['flag'] = 0; $this->db->insert("ldetails", $user_prof); } } if($uid && is_array($user_cstm) && count($user_cstm)) { $query = $this->db->get_where("leads_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($user_cstm as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads_cstm", $acct_prof, array("fa_user_id" => $cid)); $user_cstm = $new; } else { $user_cstm['crm_id'] = $uid; $user_cstm['date_entered'] = time(); $user_cstm['date_modified'] = time(); $user_cstm['created_by'] = $this->session->userdata('id'); $user_cstm['modified_user_id'] = $this->session->userdata('id'); $this->db->insert("leads_cstm", $user_cstm); } } $cmp = new stdclass; $cid = 0; if(is_array($acct) && count($acct)) { $acct['uid'] = $uid; $acct['main_contact'] = "{$user['first_name']} {$user['last_name']}"; if(!empty($user['email'])) { $acct['email'] = $user['email']; } $acct['isprospect'] = 0; $acct['flag'] = 0; if(!empty($acct['mcn'])) { $where .= "fms.mcn = '{$acct['mcn']}'"; } if(!empty($acct['phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['phone']}' OR "; $where .= "crm.billing_phone = '{$acct['phone']}'"; } if(!empty($acct['billing_phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['billing_phone']}' OR "; $where .= "crm.billing_phone = '{$acct['billing_phone']}'"; } if(!empty($acct['company_name'])) { if($where) { $where .= " OR "; } $where .= "fms.company_name = '{$acct['company_name']}'"; } $query = $this->db->query($this->Account_m->acctQuery($where)); $cmp = reset($query->result()); if($where && !empty($cmp->id)) { $cid = $cmp->id; $new = array(); foreach($acct as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts", $billing, array("cid" => $cid)); $acct = $new; } else { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("accounts", array("id" => $cid), 1); $data = reset($query->result()); } $acct['id'] = $cid; $this->db->insert("accounts", $acct); } } if($cid && is_array($acct_group) && count($acct_group)) { $grp = $this->db->get_where("accounts_groups", array("cid" => $cid, "gid" => $acct_group['id'])); if(empty($cmp->id)) { $acct_group['cid'] = $cid; $this->db->insert("accounts_groups", $acct_group); } } if($cid && is_array($acct_prof) && count($acct_prof)) { if(!empty($cmp->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("cprofiles", $acct_prof, array("cid" => $cid)); $acct_prof = $new; } else { $acct_prof['cid'] = $cid; $acct_prof['flag'] = 0; $this->db->insert("adetails", $acct_prof); } } if($cid && is_array($billing) && count($billing)) { $bill = $this->db->get_where("accounts_billing", array("cid" => $cid)); if(!empty($bill->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_billing", $billing, array("cid" => $cid)); } else { $billing['cid'] = $cid; $billing['flag'] = 0; $this->db->insert("accounts_billing", $billing); } } if($cid && $uid) { $this->db->update("leads", array("cid" => $cid), array("id" => $uid)); } if($cid && is_array($acct_cstm) && count($acct_cstm)) { $query = $this->db->get_where("accounts_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($acct_cstm as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_cstm", $acct_cstm, array("crm_id" => $cid)); $acct_cstm = $new; } else { $acct_cstm['crm_id'] = $cid; $acct_cstm['date_entered'] = time(); $acct_cstm['date_modified'] = time(); $acct_cstm['created_by'] = $this->session->userdata('id'); $acct_cstm['modified_user_id'] = $this->session->userdata('id'); if(empty($acct_cstm['rating'])) { $acct_cstm['rating'] = 1; } $this->db->insert("accounts_cstm", $acct_cstm); } } $true = TRUE; } fclose($fd); return $true; } с function importLeads($file, $map) { // Open the Text File if(!file_exists($file)) { return false; } error_reporting(E_ALL); set_time_limit(240); ini_set("memory_limit", "512M"); $fd = fopen($file, "r"); // Return FALSE if file not found if(!$fd) { return FALSE; } // Traverse Each Line of the File $true = false; $first = 0; while(!feof($fd)) { if($first == 0) { $cols = fgetcsv($fd); $first++; continue; } // Get the columns of each line $row = fgetcsv($fd); // Traverse columns $group = array(); $lead_status = array(); $lead_type = array(); $lead_source = array(); $user = array(); $user_cstm = array(); $user_prof = array(); $acct = array(); $acct_cstm = array(); $acct_prof = array(); $acct_group = array(); if(!$row) { continue; } foreach($row as $num => $val) { if(empty($map[$num])) { continue; } $val = str_replace('"', """, $val); $val = str_replace("'", "'", $val); switch($map[$num]) { // Company Account case "company_name": $acct['company_name'] = $val; break; case "lead_type": $lead_type['name'] = $val; break; case "lead_source": $lead_source['name'] = $val; break; case "lead_source_description": $lead_source['name'] = $val; break; case "campaign": $campaign['name'] = $val; break; case "mcn": $acct['mcn'] = $val; break; case "usdot": $acct['usdot'] = $val; break; case "sic_codes": $acct_cstm['sic_codes'] = $val; break; case "naics_codes": $acct_cstm['naics_codes'] = $val; break; case "agent_assigned": $acct_cstm['agent_assigned'] = $val; break; case "group_assigned": $group['name'] = $val; break; case "rating": $acct_cstm['rating'] = $val; break; case "main_phone": $acct['phone'] = $val; break; case "billing_phone": $acct_cstm['billing_phone'] = $val; break; case "company_fax": $acct['fax'] = $val; break; case "company_email": $acct['email2'] = $val; break; // Company Location case "primary_address": $acct['address'] = $val; break; case "primary_address2": $acct['address2'] = $val; break; case "primary_city": $acct['city'] = $val; break; case "primary_state": $acct['state'] = $val; break; case "primary_zip": $acct['zip'] = $val; break; case "primary_country": $acct['country'] = $val; break; case "billing_address": $billing['address'] = $val; break; case "billing_address2": $billing['address2'] = $val; break; case "billing_city": $billing['city'] = $val; break; case "billing_state": $billing['state'] = $val; break; case "billing_zip": $billing['zip'] = $val; break; case "billing_country": $billing['country'] = $val; break; case "company_website": $acct_cstm['website'] = $val; break; case "company_revenue": $acct_cstm['revenue'] = $val; break; case "company_about": $acct_prof['aboutus'] = $val; break; // Misc. Company Data case "bols_per_mo": $acct_cstm['approx_bols_per_mo'] = $val; break; case "no_employees": $acct_cstm['no_employees'] = $val; break; case "no_drivers": $acct_prof['drivers'] = $val; break; case "no_trucks": $acct_prof['power_units'] = $val; break; case "no_trailers": $acct_cstm['no_trailers'] = $acct_prof['trailers'] = $val; break; case "no_parcels_day": $acct_cstm['no_parcels_day'] = $val; break; case "no_shipping_locations": $acct_cstm['no_shipping_locations'] = $val; break; case "approves_inbound": $acct_cstm['approves_inbound'] = $val; break; case "what_erp_used": $acct_cstm['what_erp_used'] = $val; break; case "birddog": $acct_cstm['birddog_referral'] = $val; break; case "status_notes": $acct_cstm['status_notes'] = $val; break; case "notes": $acct_cstm['notes'] = $val; break; case "internal_notes": $acct_cstm['notes_internal'] = $val; break; // User Data case "salutation": $user_cstm['salutation'] = $val; break; case "first_name": $user['first_name'] = $billing['first_name'] = $val; break; case "last_name": $user['last_name'] = $billing['last_name'] = $val; break; case "user_title": $user_prof['title'] = $val; break; case "user_about": $user_prof['about'] = $val; break; case "user_email": $user['email'] = $val; break; case "home_phone": $user_prof['phone'] = $val; break; case "mobile_phone": $user_cstm['mobile_phone'] = $val; break; case "direct_phone": $user_cstm['direct_phone'] = $val; break; case "user_fax": $user_prof['fax'] = $val; break; case "user_locale": $user['location'] = $val; break; case "user_website": $user_prof['website_url'] = $val; break; case "user_facebook": $user_prof['fb_url'] = $val; break; case "user_twitter": $user_prof['twitter_url'] = $val; break; case "user_linkedin": $user_prof['linkedin_url'] = $val; break; } } if(empty($acct['company_name']) || empty($user['first_name']) || empty($user['last_name'])) { continue; } $this->db = $this->load->database('crm_db', TRUE); if(isset($lead_type['name']) && ($name = $lead_type['name'])) { $count = $this->db->count_all("lead_types"); $check = $this->db->get_where("lead_types", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_types", array("name" => $name, "order" => $count)); $ltype = $this->db->insert_id(); $acct_cstm['lead_type'] = $acct['account_type'] = $user['company_type'] = $ltype; } } if(isset($lead_source['name']) && ($name = $lead_source['name'])) { $count = $this->db->count_all("lead_sources"); $check = $this->db->get_where("lead_sources", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_sources", array("name" => $name, "order" => $count)); $acct_cstm['lead_source'] = $this->db->insert_id(); } } if(isset($campaign['name']) && ($name = $campaign['name'])) { $check = $this->db->get_where("campaigns", array("name" => $name)); if($check->num_rows() < 1) { $campaign['id'] = $accounts_cstm['campaign'] = $this->Secure_m->generate_sugar_id(); $campaign['date_entered'] = time(); $campaign['date_modified'] = time(); $campaign['modified_user_id'] = $this->session->userdata('id'); $campaign['created_by'] = $this->session->userdata('id'); $this->db->insert("campaigns", $campaign); } } if(isset($group['name']) && ($name = $group['name'])) { $order = $this->db->count_all("groups"); $check = $this->db->get_where("groups", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("groups", array("name" => $name, "order" => $order)); $acct_group['id'] = $this->db->insert_id(); } } $mem = new stdclass; $uid = 0; if(is_array($user) && count($user)) { $where = ""; if(!empty($user['phone'])) { $where .= "prof.phone = '{$user['phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['phone']}' OR "; $where .= "cstm.direct_phone = '{$user['phone']}'"; } if(!empty($user['mobile_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['mobile_phone']}'"; } if(!empty($user['direct_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['direct_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['direct_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['direct_phone']}'"; } $query = $this->db->query($this->Account_m->userQuery($where)); $mem = reset($query->result()); if($where && !empty($mem->id)) { $uid = $mem->id; $new = array(); foreach($user as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads", $user, array("id" => $uid)); $user = $new; } else { $user['uxtime'] = time(); $user['isclient'] = 0; $user['flag'] = 0; $user['activation_code'] = $this->Secure_m->generate_activate_id(); $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); } $user['id'] = $uid; $this->db->insert("leads", $user); } } if($uid && is_array($user_prof) && count($user_prof)) { if(!empty($mem->uid)) { $new = array(); foreach($user_prof as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("mprofiles", $user_prof, array("uid" => $uid)); $user_prof = $new; } else { $user_prof['uid'] = $uid; $user_prof['flag'] = 0; $this->db->insert("ldetails", $user_prof); } } if($uid && is_array($user_cstm) && count($user_cstm)) { $query = $this->db->get_where("leads_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($user_cstm as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads_cstm", $acct_prof, array("fa_user_id" => $cid)); $user_cstm = $new; } else { $user_cstm['crm_id'] = $uid; $user_cstm['date_entered'] = time(); $user_cstm['date_modified'] = time(); $user_cstm['created_by'] = $this->session->userdata('id'); $user_cstm['modified_user_id'] = $this->session->userdata('id'); $this->db->insert("leads_cstm", $user_cstm); } } $cmp = new stdclass; $cid = 0; if(is_array($acct) && count($acct)) { $acct['uid'] = $uid; $acct['main_contact'] = "{$user['first_name']} {$user['last_name']}"; if(!empty($user['email'])) { $acct['email'] = $user['email']; } $acct['isprospect'] = 0; $acct['flag'] = 0; if(!empty($acct['mcn'])) { $where .= "fms.mcn = '{$acct['mcn']}'"; } if(!empty($acct['phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['phone']}' OR "; $where .= "crm.billing_phone = '{$acct['phone']}'"; } if(!empty($acct['billing_phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['billing_phone']}' OR "; $where .= "crm.billing_phone = '{$acct['billing_phone']}'"; } if(!empty($acct['company_name'])) { if($where) { $where .= " OR "; } $where .= "fms.company_name = '{$acct['company_name']}'"; } $query = $this->db->query($this->Account_m->acctQuery($where)); $cmp = reset($query->result()); if($where && !empty($cmp->id)) { $cid = $cmp->id; $new = array(); foreach($acct as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts", $billing, array("cid" => $cid)); $acct = $new; } else { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("accounts", array("id" => $cid), 1); $data = reset($query->result()); } $acct['id'] = $cid; $this->db->insert("accounts", $acct); } } if($cid && is_array($acct_group) && count($acct_group)) { $grp = $this->db->get_where("accounts_groups", array("cid" => $cid, "gid" => $acct_group['id'])); if(empty($cmp->id)) { $acct_group['cid'] = $cid; $this->db->insert("accounts_groups", $acct_group); } } if($cid && is_array($acct_prof) && count($acct_prof)) { if(!empty($cmp->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("cprofiles", $acct_prof, array("cid" => $cid)); $acct_prof = $new; } else { $acct_prof['cid'] = $cid; $acct_prof['flag'] = 0; $this->db->insert("adetails", $acct_prof); } } if($cid && is_array($billing) && count($billing)) { $bill = $this->db->get_where("accounts_billing", array("cid" => $cid)); if(!empty($bill->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_billing", $billing, array("cid" => $cid)); } else { $billing['cid'] = $cid; $billing['flag'] = 0; $this->db->insert("accounts_billing", $billing); } } if($cid && $uid) { $this->db->update("leads", array("cid" => $cid), array("id" => $uid)); } if($cid && is_array($acct_cstm) && count($acct_cstm)) { $query = $this->db->get_where("accounts_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($acct_cstm as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_cstm", $acct_cstm, array("crm_id" => $cid)); $acct_cstm = $new; } else { $acct_cstm['crm_id'] = $cid; $acct_cstm['date_entered'] = time(); $acct_cstm['date_modified'] = time(); $acct_cstm['created_by'] = $this->session->userdata('id'); $acct_cstm['modified_user_id'] = $this->session->userdata('id'); if(empty($acct_cstm['rating'])) { $acct_cstm['rating'] = 1; } $this->db->insert("accounts_cstm", $acct_cstm); } } $true = TRUE; } fclose($fd); return $true; } с function importLeads($file, $map) { // Open the Text File if(!file_exists($file)) { return false; } error_reporting(E_ALL); set_time_limit(240); ini_set("memory_limit", "512M"); $fd = fopen($file, "r"); // Return FALSE if file not found if(!$fd) { return FALSE; } // Traverse Each Line of the File $true = false; $first = 0; while(!feof($fd)) { if($first == 0) { $cols = fgetcsv($fd); $first++; continue; } // Get the columns of each line $row = fgetcsv($fd); // Traverse columns $group = array(); $lead_status = array(); $lead_type = array(); $lead_source = array(); $user = array(); $user_cstm = array(); $user_prof = array(); $acct = array(); $acct_cstm = array(); $acct_prof = array(); $acct_group = array(); if(!$row) { continue; } foreach($row as $num => $val) { if(empty($map[$num])) { continue; } $val = str_replace('"', """, $val); $val = str_replace("'", "'", $val); switch($map[$num]) { // Company Account case "company_name": $acct['company_name'] = $val; break; case "lead_type": $lead_type['name'] = $val; break; case "lead_source": $lead_source['name'] = $val; break; case "lead_source_description": $lead_source['name'] = $val; break; case "campaign": $campaign['name'] = $val; break; case "mcn": $acct['mcn'] = $val; break; case "usdot": $acct['usdot'] = $val; break; case "sic_codes": $acct_cstm['sic_codes'] = $val; break; case "naics_codes": $acct_cstm['naics_codes'] = $val; break; case "agent_assigned": $acct_cstm['agent_assigned'] = $val; break; case "group_assigned": $group['name'] = $val; break; case "rating": $acct_cstm['rating'] = $val; break; case "main_phone": $acct['phone'] = $val; break; case "billing_phone": $acct_cstm['billing_phone'] = $val; break; case "company_fax": $acct['fax'] = $val; break; case "company_email": $acct['email2'] = $val; break; // Company Location case "primary_address": $acct['address'] = $val; break; case "primary_address2": $acct['address2'] = $val; break; case "primary_city": $acct['city'] = $val; break; case "primary_state": $acct['state'] = $val; break; case "primary_zip": $acct['zip'] = $val; break; case "primary_country": $acct['country'] = $val; break; case "billing_address": $billing['address'] = $val; break; case "billing_address2": $billing['address2'] = $val; break; case "billing_city": $billing['city'] = $val; break; case "billing_state": $billing['state'] = $val; break; case "billing_zip": $billing['zip'] = $val; break; case "billing_country": $billing['country'] = $val; break; case "company_website": $acct_cstm['website'] = $val; break; case "company_revenue": $acct_cstm['revenue'] = $val; break; case "company_about": $acct_prof['aboutus'] = $val; break; // Misc. Company Data case "bols_per_mo": $acct_cstm['approx_bols_per_mo'] = $val; break; case "no_employees": $acct_cstm['no_employees'] = $val; break; case "no_drivers": $acct_prof['drivers'] = $val; break; case "no_trucks": $acct_prof['power_units'] = $val; break; case "no_trailers": $acct_cstm['no_trailers'] = $acct_prof['trailers'] = $val; break; case "no_parcels_day": $acct_cstm['no_parcels_day'] = $val; break; case "no_shipping_locations": $acct_cstm['no_shipping_locations'] = $val; break; case "approves_inbound": $acct_cstm['approves_inbound'] = $val; break; case "what_erp_used": $acct_cstm['what_erp_used'] = $val; break; case "birddog": $acct_cstm['birddog_referral'] = $val; break; case "status_notes": $acct_cstm['status_notes'] = $val; break; case "notes": $acct_cstm['notes'] = $val; break; case "internal_notes": $acct_cstm['notes_internal'] = $val; break; // User Data case "salutation": $user_cstm['salutation'] = $val; break; case "first_name": $user['first_name'] = $billing['first_name'] = $val; break; case "last_name": $user['last_name'] = $billing['last_name'] = $val; break; case "user_title": $user_prof['title'] = $val; break; case "user_about": $user_prof['about'] = $val; break; case "user_email": $user['email'] = $val; break; case "home_phone": $user_prof['phone'] = $val; break; case "mobile_phone": $user_cstm['mobile_phone'] = $val; break; case "direct_phone": $user_cstm['direct_phone'] = $val; break; case "user_fax": $user_prof['fax'] = $val; break; case "user_locale": $user['location'] = $val; break; case "user_website": $user_prof['website_url'] = $val; break; case "user_facebook": $user_prof['fb_url'] = $val; break; case "user_twitter": $user_prof['twitter_url'] = $val; break; case "user_linkedin": $user_prof['linkedin_url'] = $val; break; } } if(empty($acct['company_name']) || empty($user['first_name']) || empty($user['last_name'])) { continue; } $this->db = $this->load->database('crm_db', TRUE); if(isset($lead_type['name']) && ($name = $lead_type['name'])) { $count = $this->db->count_all("lead_types"); $check = $this->db->get_where("lead_types", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_types", array("name" => $name, "order" => $count)); $ltype = $this->db->insert_id(); $acct_cstm['lead_type'] = $acct['account_type'] = $user['company_type'] = $ltype; } } if(isset($lead_source['name']) && ($name = $lead_source['name'])) { $count = $this->db->count_all("lead_sources"); $check = $this->db->get_where("lead_sources", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_sources", array("name" => $name, "order" => $count)); $acct_cstm['lead_source'] = $this->db->insert_id(); } } if(isset($campaign['name']) && ($name = $campaign['name'])) { $check = $this->db->get_where("campaigns", array("name" => $name)); if($check->num_rows() < 1) { $campaign['id'] = $accounts_cstm['campaign'] = $this->Secure_m->generate_sugar_id(); $campaign['date_entered'] = time(); $campaign['date_modified'] = time(); $campaign['modified_user_id'] = $this->session->userdata('id'); $campaign['created_by'] = $this->session->userdata('id'); $this->db->insert("campaigns", $campaign); } } if(isset($group['name']) && ($name = $group['name'])) { $order = $this->db->count_all("groups"); $check = $this->db->get_where("groups", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("groups", array("name" => $name, "order" => $order)); $acct_group['id'] = $this->db->insert_id(); } } $mem = new stdclass; $uid = 0; if(is_array($user) && count($user)) { $where = ""; if(!empty($user['phone'])) { $where .= "prof.phone = '{$user['phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['phone']}' OR "; $where .= "cstm.direct_phone = '{$user['phone']}'"; } if(!empty($user['mobile_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['mobile_phone']}'"; } if(!empty($user['direct_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['direct_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['direct_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['direct_phone']}'"; } $query = $this->db->query($this->Account_m->userQuery($where)); $mem = reset($query->result()); if($where && !empty($mem->id)) { $uid = $mem->id; $new = array(); foreach($user as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads", $user, array("id" => $uid)); $user = $new; } else { $user['uxtime'] = time(); $user['isclient'] = 0; $user['flag'] = 0; $user['activation_code'] = $this->Secure_m->generate_activate_id(); $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); } $user['id'] = $uid; $this->db->insert("leads", $user); } } if($uid && is_array($user_prof) && count($user_prof)) { if(!empty($mem->uid)) { $new = array(); foreach($user_prof as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("mprofiles", $user_prof, array("uid" => $uid)); $user_prof = $new; } else { $user_prof['uid'] = $uid; $user_prof['flag'] = 0; $this->db->insert("ldetails", $user_prof); } } if($uid && is_array($user_cstm) && count($user_cstm)) { $query = $this->db->get_where("leads_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($user_cstm as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads_cstm", $acct_prof, array("fa_user_id" => $cid)); $user_cstm = $new; } else { $user_cstm['crm_id'] = $uid; $user_cstm['date_entered'] = time(); $user_cstm['date_modified'] = time(); $user_cstm['created_by'] = $this->session->userdata('id'); $user_cstm['modified_user_id'] = $this->session->userdata('id'); $this->db->insert("leads_cstm", $user_cstm); } } $cmp = new stdclass; $cid = 0; if(is_array($acct) && count($acct)) { $acct['uid'] = $uid; $acct['main_contact'] = "{$user['first_name']} {$user['last_name']}"; if(!empty($user['email'])) { $acct['email'] = $user['email']; } $acct['isprospect'] = 0; $acct['flag'] = 0; if(!empty($acct['mcn'])) { $where .= "fms.mcn = '{$acct['mcn']}'"; } if(!empty($acct['phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['phone']}' OR "; $where .= "crm.billing_phone = '{$acct['phone']}'"; } if(!empty($acct['billing_phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['billing_phone']}' OR "; $where .= "crm.billing_phone = '{$acct['billing_phone']}'"; } if(!empty($acct['company_name'])) { if($where) { $where .= " OR "; } $where .= "fms.company_name = '{$acct['company_name']}'"; } $query = $this->db->query($this->Account_m->acctQuery($where)); $cmp = reset($query->result()); if($where && !empty($cmp->id)) { $cid = $cmp->id; $new = array(); foreach($acct as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts", $billing, array("cid" => $cid)); $acct = $new; } else { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("accounts", array("id" => $cid), 1); $data = reset($query->result()); } $acct['id'] = $cid; $this->db->insert("accounts", $acct); } } if($cid && is_array($acct_group) && count($acct_group)) { $grp = $this->db->get_where("accounts_groups", array("cid" => $cid, "gid" => $acct_group['id'])); if(empty($cmp->id)) { $acct_group['cid'] = $cid; $this->db->insert("accounts_groups", $acct_group); } } if($cid && is_array($acct_prof) && count($acct_prof)) { if(!empty($cmp->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("cprofiles", $acct_prof, array("cid" => $cid)); $acct_prof = $new; } else { $acct_prof['cid'] = $cid; $acct_prof['flag'] = 0; $this->db->insert("adetails", $acct_prof); } } if($cid && is_array($billing) && count($billing)) { $bill = $this->db->get_where("accounts_billing", array("cid" => $cid)); if(!empty($bill->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_billing", $billing, array("cid" => $cid)); } else { $billing['cid'] = $cid; $billing['flag'] = 0; $this->db->insert("accounts_billing", $billing); } } if($cid && $uid) { $this->db->update("leads", array("cid" => $cid), array("id" => $uid)); } if($cid && is_array($acct_cstm) && count($acct_cstm)) { $query = $this->db->get_where("accounts_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($acct_cstm as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_cstm", $acct_cstm, array("crm_id" => $cid)); $acct_cstm = $new; } else { $acct_cstm['crm_id'] = $cid; $acct_cstm['date_entered'] = time(); $acct_cstm['date_modified'] = time(); $acct_cstm['created_by'] = $this->session->userdata('id'); $acct_cstm['modified_user_id'] = $this->session->userdata('id'); if(empty($acct_cstm['rating'])) { $acct_cstm['rating'] = 1; } $this->db->insert("accounts_cstm", $acct_cstm); } } $true = TRUE; } fclose($fd); return $true; } с function importLeads($file, $map) { // Open the Text File if(!file_exists($file)) { return false; } error_reporting(E_ALL); set_time_limit(240); ini_set("memory_limit", "512M"); $fd = fopen($file, "r"); // Return FALSE if file not found if(!$fd) { return FALSE; } // Traverse Each Line of the File $true = false; $first = 0; while(!feof($fd)) { if($first == 0) { $cols = fgetcsv($fd); $first++; continue; } // Get the columns of each line $row = fgetcsv($fd); // Traverse columns $group = array(); $lead_status = array(); $lead_type = array(); $lead_source = array(); $user = array(); $user_cstm = array(); $user_prof = array(); $acct = array(); $acct_cstm = array(); $acct_prof = array(); $acct_group = array(); if(!$row) { continue; } foreach($row as $num => $val) { if(empty($map[$num])) { continue; } $val = str_replace('"', """, $val); $val = str_replace("'", "'", $val); switch($map[$num]) { // Company Account case "company_name": $acct['company_name'] = $val; break; case "lead_type": $lead_type['name'] = $val; break; case "lead_source": $lead_source['name'] = $val; break; case "lead_source_description": $lead_source['name'] = $val; break; case "campaign": $campaign['name'] = $val; break; case "mcn": $acct['mcn'] = $val; break; case "usdot": $acct['usdot'] = $val; break; case "sic_codes": $acct_cstm['sic_codes'] = $val; break; case "naics_codes": $acct_cstm['naics_codes'] = $val; break; case "agent_assigned": $acct_cstm['agent_assigned'] = $val; break; case "group_assigned": $group['name'] = $val; break; case "rating": $acct_cstm['rating'] = $val; break; case "main_phone": $acct['phone'] = $val; break; case "billing_phone": $acct_cstm['billing_phone'] = $val; break; case "company_fax": $acct['fax'] = $val; break; case "company_email": $acct['email2'] = $val; break; // Company Location case "primary_address": $acct['address'] = $val; break; case "primary_address2": $acct['address2'] = $val; break; case "primary_city": $acct['city'] = $val; break; case "primary_state": $acct['state'] = $val; break; case "primary_zip": $acct['zip'] = $val; break; case "primary_country": $acct['country'] = $val; break; case "billing_address": $billing['address'] = $val; break; case "billing_address2": $billing['address2'] = $val; break; case "billing_city": $billing['city'] = $val; break; case "billing_state": $billing['state'] = $val; break; case "billing_zip": $billing['zip'] = $val; break; case "billing_country": $billing['country'] = $val; break; case "company_website": $acct_cstm['website'] = $val; break; case "company_revenue": $acct_cstm['revenue'] = $val; break; case "company_about": $acct_prof['aboutus'] = $val; break; // Misc. Company Data case "bols_per_mo": $acct_cstm['approx_bols_per_mo'] = $val; break; case "no_employees": $acct_cstm['no_employees'] = $val; break; case "no_drivers": $acct_prof['drivers'] = $val; break; case "no_trucks": $acct_prof['power_units'] = $val; break; case "no_trailers": $acct_cstm['no_trailers'] = $acct_prof['trailers'] = $val; break; case "no_parcels_day": $acct_cstm['no_parcels_day'] = $val; break; case "no_shipping_locations": $acct_cstm['no_shipping_locations'] = $val; break; case "approves_inbound": $acct_cstm['approves_inbound'] = $val; break; case "what_erp_used": $acct_cstm['what_erp_used'] = $val; break; case "birddog": $acct_cstm['birddog_referral'] = $val; break; case "status_notes": $acct_cstm['status_notes'] = $val; break; case "notes": $acct_cstm['notes'] = $val; break; case "internal_notes": $acct_cstm['notes_internal'] = $val; break; // User Data case "salutation": $user_cstm['salutation'] = $val; break; case "first_name": $user['first_name'] = $billing['first_name'] = $val; break; case "last_name": $user['last_name'] = $billing['last_name'] = $val; break; case "user_title": $user_prof['title'] = $val; break; case "user_about": $user_prof['about'] = $val; break; case "user_email": $user['email'] = $val; break; case "home_phone": $user_prof['phone'] = $val; break; case "mobile_phone": $user_cstm['mobile_phone'] = $val; break; case "direct_phone": $user_cstm['direct_phone'] = $val; break; case "user_fax": $user_prof['fax'] = $val; break; case "user_locale": $user['location'] = $val; break; case "user_website": $user_prof['website_url'] = $val; break; case "user_facebook": $user_prof['fb_url'] = $val; break; case "user_twitter": $user_prof['twitter_url'] = $val; break; case "user_linkedin": $user_prof['linkedin_url'] = $val; break; } } if(empty($acct['company_name']) || empty($user['first_name']) || empty($user['last_name'])) { continue; } $this->db = $this->load->database('crm_db', TRUE); if(isset($lead_type['name']) && ($name = $lead_type['name'])) { $count = $this->db->count_all("lead_types"); $check = $this->db->get_where("lead_types", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_types", array("name" => $name, "order" => $count)); $ltype = $this->db->insert_id(); $acct_cstm['lead_type'] = $acct['account_type'] = $user['company_type'] = $ltype; } } if(isset($lead_source['name']) && ($name = $lead_source['name'])) { $count = $this->db->count_all("lead_sources"); $check = $this->db->get_where("lead_sources", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("lead_sources", array("name" => $name, "order" => $count)); $acct_cstm['lead_source'] = $this->db->insert_id(); } } if(isset($campaign['name']) && ($name = $campaign['name'])) { $check = $this->db->get_where("campaigns", array("name" => $name)); if($check->num_rows() < 1) { $campaign['id'] = $accounts_cstm['campaign'] = $this->Secure_m->generate_sugar_id(); $campaign['date_entered'] = time(); $campaign['date_modified'] = time(); $campaign['modified_user_id'] = $this->session->userdata('id'); $campaign['created_by'] = $this->session->userdata('id'); $this->db->insert("campaigns", $campaign); } } if(isset($group['name']) && ($name = $group['name'])) { $order = $this->db->count_all("groups"); $check = $this->db->get_where("groups", array("name" => $name)); if($check->num_rows() < 1) { $this->db->insert("groups", array("name" => $name, "order" => $order)); $acct_group['id'] = $this->db->insert_id(); } } $mem = new stdclass; $uid = 0; if(is_array($user) && count($user)) { $where = ""; if(!empty($user['phone'])) { $where .= "prof.phone = '{$user['phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['phone']}' OR "; $where .= "cstm.direct_phone = '{$user['phone']}'"; } if(!empty($user['mobile_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['mobile_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['mobile_phone']}'"; } if(!empty($user['direct_phone'])) { if($where) { $where .= " OR "; } $where .= "prof.phone = '{$user['direct_phone']}' OR "; $where .= "cstm.mobile_phone = '{$user['direct_phone']}' OR "; $where .= "cstm.direct_phone = '{$user['direct_phone']}'"; } $query = $this->db->query($this->Account_m->userQuery($where)); $mem = reset($query->result()); if($where && !empty($mem->id)) { $uid = $mem->id; $new = array(); foreach($user as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads", $user, array("id" => $uid)); $user = $new; } else { $user['uxtime'] = time(); $user['isclient'] = 0; $user['flag'] = 0; $user['activation_code'] = $this->Secure_m->generate_activate_id(); $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $uid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); } $user['id'] = $uid; $this->db->insert("leads", $user); } } if($uid && is_array($user_prof) && count($user_prof)) { if(!empty($mem->uid)) { $new = array(); foreach($user_prof as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("mprofiles", $user_prof, array("uid" => $uid)); $user_prof = $new; } else { $user_prof['uid'] = $uid; $user_prof['flag'] = 0; $this->db->insert("ldetails", $user_prof); } } if($uid && is_array($user_cstm) && count($user_cstm)) { $query = $this->db->get_where("leads_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($user_cstm as $k => $v) { if(!empty($mem->$k)) { $new[$k] = $mem->$k; unset($user_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("leads_cstm", $acct_prof, array("fa_user_id" => $cid)); $user_cstm = $new; } else { $user_cstm['crm_id'] = $uid; $user_cstm['date_entered'] = time(); $user_cstm['date_modified'] = time(); $user_cstm['created_by'] = $this->session->userdata('id'); $user_cstm['modified_user_id'] = $this->session->userdata('id'); $this->db->insert("leads_cstm", $user_cstm); } } $cmp = new stdclass; $cid = 0; if(is_array($acct) && count($acct)) { $acct['uid'] = $uid; $acct['main_contact'] = "{$user['first_name']} {$user['last_name']}"; if(!empty($user['email'])) { $acct['email'] = $user['email']; } $acct['isprospect'] = 0; $acct['flag'] = 0; if(!empty($acct['mcn'])) { $where .= "fms.mcn = '{$acct['mcn']}'"; } if(!empty($acct['phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['phone']}' OR "; $where .= "crm.billing_phone = '{$acct['phone']}'"; } if(!empty($acct['billing_phone'])) { if($where) { $where .= " OR "; } $where .= "fms.phone = '{$acct['billing_phone']}' OR "; $where .= "crm.billing_phone = '{$acct['billing_phone']}'"; } if(!empty($acct['company_name'])) { if($where) { $where .= " OR "; } $where .= "fms.company_name = '{$acct['company_name']}'"; } $query = $this->db->query($this->Account_m->acctQuery($where)); $cmp = reset($query->result()); if($where && !empty($cmp->id)) { $cid = $cmp->id; $new = array(); foreach($acct as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts", $billing, array("cid" => $cid)); $acct = $new; } else { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("leads", array("id" => $uid), 1); $data = reset($query->result()); while(!empty($data->id)) { $cid = $this->Secure_m->generate_activate_id(10); $query = $this->db->get_where("accounts", array("id" => $cid), 1); $data = reset($query->result()); } $acct['id'] = $cid; $this->db->insert("accounts", $acct); } } if($cid && is_array($acct_group) && count($acct_group)) { $grp = $this->db->get_where("accounts_groups", array("cid" => $cid, "gid" => $acct_group['id'])); if(empty($cmp->id)) { $acct_group['cid'] = $cid; $this->db->insert("accounts_groups", $acct_group); } } if($cid && is_array($acct_prof) && count($acct_prof)) { if(!empty($cmp->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("cprofiles", $acct_prof, array("cid" => $cid)); $acct_prof = $new; } else { $acct_prof['cid'] = $cid; $acct_prof['flag'] = 0; $this->db->insert("adetails", $acct_prof); } } if($cid && is_array($billing) && count($billing)) { $bill = $this->db->get_where("accounts_billing", array("cid" => $cid)); if(!empty($bill->id)) { $new = array(); foreach($acct_prof as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_prof[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_billing", $billing, array("cid" => $cid)); } else { $billing['cid'] = $cid; $billing['flag'] = 0; $this->db->insert("accounts_billing", $billing); } } if($cid && $uid) { $this->db->update("leads", array("cid" => $cid), array("id" => $uid)); } if($cid && is_array($acct_cstm) && count($acct_cstm)) { $query = $this->db->get_where("accounts_cstm", array("crm_id" => $cid), 1); $data = reset($query->result()); if(!empty($data->crm_id)) { $new = array(); foreach($acct_cstm as $k => $v) { if(!empty($cmp->$k)) { $new[$k] = $cmp->$k; unset($acct_cstm[$k]); } else { $new[$k] = $v; } } //$this->db->update("accounts_cstm", $acct_cstm, array("crm_id" => $cid)); $acct_cstm = $new; } else { $acct_cstm['crm_id'] = $cid; $acct_cstm['date_entered'] = time(); $acct_cstm['date_modified'] = time(); $acct_cstm['created_by'] = $this->session->userdata('id'); $acct_cstm['modified_user_id'] = $this->session->userdata('id'); if(empty($acct_cstm['rating'])) { $acct_cstm['rating'] = 1; } $this->db->insert("accounts_cstm", $acct_cstm); } } $true = TRUE; } fclose($fd); return $true; } 

Теперь, насколько я вижу, скрипт работает отлично. Нет ничего плохого в самом коде. Проблема в том, что после примерно 400-500 строк скрипт просто останавливается. Я не получаю сообщение об ошибке, но никакой код не обрабатывается.

Я знаю это, потому что после этого у меня есть код, который должен возвращать страницу перенаправления через AJAX. Тем не менее, после моего цикла в функции importLeads.

Я не уверен, как сделать этот скрипт более эффективным … Я уверен, что это время, но я не знаю, как сделать его более эффективным. Мне нужен этот скрипт для обработки всей информации выше отдельно. У меня есть множество отдельных таблиц, которые все связаны друг с другом, и этот сценарий импорта должен устанавливать все по-разному.

Я говорил с моим клиентом об этом проекте. Этот скрипт работает, когда я опускаю его до 400 строк. У него есть много таких файлов CSV, которые составляют около 75 000 строк. Тот, который я импортирую, меньше, всего около 1200 строк.

Я попытался изучить альтернативные методы, такие как сценарий импорта MySQL, но я не могу этого сделать, потому что этот скрипт должен импортировать данные в отдельные таблицы, и сначала он должен проверить существующие данные. Я также должен иметь все пустые поля с импортированной информацией, но это еще хуже.

Если кто-нибудь знает более эффективный метод, это будет очень признательно. Я старался быть настолько подробным, насколько мог. Следует отметить, что я использую CodeIgniter, но если есть более эффективный способ, который не использует CodeIgniter, я возьму его (я все же могу поместить его в модель CI).

Я написал PHP-скрипты для массового загрузки данных, опубликованных с помощью дампа данных Stack Overflow. Я импортирую миллионы строк, и это не так долго.

Вот несколько советов:

  • Не полагайтесь на autocommit. Накладные расходы на запуск и совершение транзакции для каждой строки огромны. Используйте явные транзакции и фиксируйте после каждых 1000 строк (или более).

  • Используйте подготовленные заявления. Поскольку вы в основном делаете одни и те же вставки тысячи раз, вы можете подготовить каждую вставку до начала цикла, а затем выполнить во время цикла, передав значения в качестве параметров. Я не знаю, как это сделать с помощью библиотеки баз данных CodeIgniter, вам придется разобраться в этом.

  • Настройте MySQL для импорта. Увеличьте кеш-буферы и так далее. Дополнительную информацию см. В разделе « Скорость инструкций INSERT» .

  • Используйте LOAD DATA INFILE. Если возможно. Это буквально в 20 раз быстрее, чем использование INSERT для загрузки данных подряд за строкой. Я понимаю, если вы не можете, потому что вам нужно получить последний идентификатор вставки и так далее. Но в большинстве случаев, даже если вы читаете файл CSV, переставляете его и записываете в несколько файлов CSV с временным временем, загрузка данных по-прежнему выполняется быстрее, чем при использовании INSERT.

  • Сделайте это в автономном режиме. Не выполняйте длительные задачи во время веб-запроса. Ограничение времени PHP-запроса прекратит работу, если не сегодня, а затем во вторник, когда задание будет на 10% больше. Вместо этого сделайте запрос веб-запроса заданием, а затем верните управление пользователю. Вы должны запустить импорт данных в качестве серверного процесса и периодически позволять пользователю заглядывать в скорость прогресса. Например, дешевый способ сделать это для вашего скрипта импорта для вывода «.». к временному файлу, а затем пользователь может запросить просмотр временного файла и продолжить перезагрузку в своем браузере. Если вы хотите получить фантазию, сделайте что-нибудь с Ajax.

Чтобы эффективно импортировать данные в MySQL, вы должны использовать LOAD DATA INFILE . Это будет иметь огромное значение в производительности.

Если вам необходимо предварительно обработать ваши данные, сделайте это с помощью приведенного выше сценария, затем экспортируйте обратно в CSV / TSV и используйте запросы LOAD DATA для окончательного импорта в вашу базу данных.

Ваш скрипт не выходит за пределы 500 строк, потому что он, скорее всего, достигнет ограничения времени выполнения PHP. Вы можете использовать функцию set_time_limit (), чтобы дать вашему сценарию никаких ограничений по времени вообще, и в этом случае вам нужно вызвать set_time_limit(0) в начале вашего скрипта.

Еще один пункт, который я должен открыть, – это код, КОТОРЫЙ НЕОБХОДИМО выполнить следующее:

в настоящее время, где вы это делаете:

 foreach($row as $num => $val) { if(empty($map[$num])) { continue; } $val = str_replace('"', "&#34;", $val); $val = str_replace("'", "&#39;", $val); switch($map[$num]) { // Company Account case "company_name": $acct['company_name'] = $val; break; 

Для этого вам нужно изменить переключатель / корпус:

1) создать карту данных ваших отображаемых полей. Карта данных должна иметь правильный массив, к которому относится поле, а также индекс этого массива. Например:

 $dataMap['company_name'] = array($acct, 'company_name'); $dataMap['lead_type'] = array($lead_type, 'name'); . . . $dataMap['bols_per_mo'] = array($acct_cstm, 'approx_bols_per_mo'); . . . 

И так далее

Затем 2) Замените свой массивный оператор switch этим простым фрагментом кода:

 foreach($row as $num => $val) { if(empty($map[$num])) { continue; } $val = str_replace('"', "&#34;", $val); $val = str_replace("'", "&#39;", $val); $mappingRecord = $dataMap[ $map[$num] ]; //The first element is the array the data should go in $destinationArray = $mappingRecord[0]; //the second element is the index of the array it should go in $destinationArray[$mappingRecord[1]] = $val; 

загружать исходные данные csv-файла в промежуточные таблицы с использованием метода ввода данных, который является хорошим и быстрым:

 set autocommit = 0; load data infile.. load data infile.. ... commit; 

после загрузки данных запустите очистку данных, картографирование и проверку сохраненных процедур и т. д.

 call cleanse_staging_data(); call map_staging_data(); call validate_staging_data(); 

как только данные будут обработаны, скопируйте данные из промежуточных таблиц в правильные таблицы данных:

 call copy_staging_to_production(); 

или что-то типа того.

Вы нажимаете ограничение времени скрипта PHP, загружая свои большие файлы?

Попробуй это:

 set_time_limit(0); 

для отключения 30-секундного ограничения по умолчанию. Существует ограничение на сервер, которое вы можете получить с помощью max_execution_time() . Если для этого задания массовой загрузки недостаточно широкого диапазона для сервера, вам нужно выяснить, как заставить локального репортера сервера изменить его или сделать загрузку другим способом.

Иногда мне приходилось делать что-то подобное. Есть несколько проблем, с которыми вы потенциально сталкиваетесь:

  1. Тайм-аут сценария PHP. Через заданный период времени PHP автоматически уничтожит этот процесс. Вы можете отключить это в файле php.ini или с помощью set_time_limit(0) . Однако некоторые хосты отключили этот метод, и на некоторых хостах есть отдельная система часов, чтобы убивать процессы, которые выполнялись в течение определенного периода времени, чтобы сервер не был сбит.
  2. Предел памяти – PHP позволит вам установить максимальный предел памяти в том же php.ini. Если он ударит его, это вызовет фатальную ошибку и умрет. Вы можете увидеть это в журнале ошибок, но ничего не будет выводиться в браузер.
  3. MySQL Query Overhead – как указывали другие, для каждого отдельного запроса много накладных расходов. У меня не было достаточного количества строк, чтобы оправдать выброс load data infile в шторм. Мне тоже не нужно было получать результаты для каждого отдельного запроса, поэтому я просто поместил их в один вызов и уволил его в один mysql_query (который вы можете сделать с вашего контроллера в CodeIgniter так: mysql_query($sql, $this->db->conn_id); так как он выкинет, если вы передадите это DB :: query).

Билл Карвин указал на множество хороших способов оптимизации, когда вы имеете дело с очень большими наборами данных, но если у вас возникли проблемы с ~ 400 строк, я не думаю, что это покажется вам очень хорошим. Проверьте свои журналы ошибок, устраните проблему и затем оптимизируйте ее.