3 require_once 'Pman/Roo.php';
5 class Pman_Xtuple_Import_Customers extends Pman_Roo
9 if (HTML_FlexyFramework::get()->cli) {
12 return parent::getAuth();
17 $this->transObj = DB_DataObject::Factory('vendinfo');
19 $this->transObj->query('BEGIN');
21 PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));
23 $img = DB_DataObject::Factory('images');
26 'ontable' => 'ipshead'
28 $img->onUpload(false);
30 require_once 'File/Convert.php';
31 $fc = new File_Convert($img->getStoreName(), $img->mimetype );
32 $csv = $fc->convert('text/csv');
33 $this->importCsv($csv);
36 function importCsv($csv)
38 ini_set("auto_detect_line_endings", true);
40 $fh = fopen($csv, 'r');
42 $this->jerr("invalid file");
46 'ACTIVE STATUS', 'CUSTOMER', 'BALANCE', 'BALANCE TOTAL', 'COMPANY', 'MR, MRS',
47 'FIRST NAME', 'M.I.', 'LAST NAME', 'CONTACT', 'PHONE', 'FAX', 'ALT. PHONE', 'ALT. CONTACT', 'EMAIL', 'BILL TO 1',
48 'BILL TO 2', 'BILL TO 3', 'BILL TO 4', 'BILL TO 5', 'SHIP TO 1', 'SHIP TO 2', 'SHIP TO 3', 'SHIP TO 4', 'SHIP TO 5',
49 'CUSTOMER TYPE', 'TERMS', 'REP', 'TAX CODE', 'RESALE NUM', 'ACCOUNT NO.', 'CREDIT LIMIT', 'JOB STATUS', 'JOB TYPE',
50 'JOB DESCRIPTION', 'START DATE', 'PROJECTED END', 'END DATE', 'NOTE'
56 while(false !== ($n = fgetcsv($fh,10000, ',', '"'))) {
61 $cols[] = strtoupper(trim($k));
68 if (!in_array($r,$cols)) {
75 foreach($cols as $i=>$k) {
82 $this->jerr("could not find a row with " . implode(' / ', $req));
87 $tz = DB_DataObject::Factory('taxzone');
88 $tz->whereAdd("taxzone_code != 'NO TAX'");
89 if (!$tz->find(true)) {
90 $this->jerr("could not find tax zone for taxable");
94 foreach ($rows as $row){
95 $custinfo = DB_DataObject::factory('custinfo');
96 if(empty($row['COMPANY'])){
100 if(!$custinfo->get('cust_name', $row['COMPANY'])){ // customer not exist
101 $custinfo->setFrom(array(
102 'cust_active' => $row['ACTIVE STATUS'] == 'Active' ? TRUE : FALSE,
103 'cust_salesrep_id' => $this->salesrep($row['REP']),
104 'cust_name' => $row['COMPANY'],
105 'cust_number' => str_replace(' ', '', strtoupper($row['COMPANY'])),
106 'cust_creditlmt' => empty($row['CREDIT LIMIT']) ? 0 : $row['CREDIT LIMIT'],
107 'cust_terms_id' => $this->terms($row['TERMS']),
108 'cust_taxzone_id' => $tz->pid()
112 foreach($custinfo->defaults() as $k=>$v) {
113 if (!isset($custinfo->$k)) {
123 if(!$custinfo->pid()){
124 $this->jerr("error occur on insert customer " . $row['COMPANY']);
129 $terms_id = $this->terms($row['TERMS']);
130 if($custinfo->cust_terms_id != $terms_id){
131 $oldcust = clone($custinfo);
132 $custinfo->cust_terms_id = $terms_id;
133 $custinfo->update($oldcust);
137 // check cntct and update cust_cntct_id
138 $cntct = DB_DataObject::factory('cntct');
139 if(empty($row['FIRST NAME']) && empty($row['LAST NAME'])){
140 $row['FIRST NAME'] = $row['CONTACT'];
142 if(empty($row['CONTACT'])){
143 $row['FIRST NAME'] = $row['CUSTOMER'];
149 'cntct_first_name' => empty($row['MR, MRS']) ? $row['FIRST NAME'] : $row['MR, MRS'] . ' ' . $row['FIRST NAME'],
150 'cntct_last_name' => $row['LAST NAME'],
152 'cntct_phone' => $row['PHONE'],
153 'cntct_fax' => $row['FAX'],
154 'cntct_email' => $row['EMAIL'],
155 'cntct_notes' => $row['NOTE'],
156 'cntct_crmacct_id' => $cntct->sqlValue("(SELECT crmacct_id FROM crmacct WHERE crmacct_cust_id = {$custinfo->pid()})")
158 $c['cntct_name'] = empty($c['cntct_last_name']) ? $c['cntct_first_name'] : $c['cntct_first_name'] . ' ' . $c['cntct_last_name'];
159 if(!$cntct->get('cntct_name', $c['cntct_name'])){
160 $cntct = $cntct->createFromArray($c);
163 $this->jerr("error occur on insert cntct " . $row['CUSTOMER']);
167 if(!$cntct->cntct_addr_id){
168 $addrs = $this->addrValue($row);
169 $addr = DB_DataObject::Factory('addr');
170 $addr->createFromArray($addrs);
173 $this->jerr("error occur on insert address " . $row['CUSTOMER']);
176 $oldcn = clone($cntct);
177 $cntct->cntct_addr_id = $addr->pid();
178 $cntct->update($oldcn);
181 if(!$custinfo->cust_cntct_id){
182 $oldcust = clone($custinfo);
183 $custinfo->cust_cntct_id = $cntct->pid();
184 $custinfo->update($oldcust);
187 // fetch all cntct id to update the shipment list
188 $cn = DB_DataObject::factory('cntct');
190 cntct_crmacct_id = (SELECT crmacct_id FROM crmacct WHERE crmacct_cust_id = {$custinfo->pid()})
192 $list = $cn->fetchAll('cntct_id');
194 $custinfo->updateShipList($list);
204 * use default salesrep accounts if the code does not exist
206 function salesrep($salesrep_name)
208 $salesrep = DB_DataObject::factory('salesrep');
209 if(!$salesrep->get('salesrep_name', $salesrep_name)){
210 $salesrep->get('salesrep_name', 'accounts');
212 return $salesrep->pid();
217 * use default terms C.O.D. if the code does not exist
221 $terms = DB_DataObject::factory('terms');
223 terms_code ILIKE '%{$terms->escape($t)}%'
225 terms_descrip ILIKE '%{$terms->escape($t)}%'
227 if($terms->find(true)){
228 return $terms->pid();
231 $terms->setFrom(array(
232 'terms_code' => strtoupper($t),
233 'terms_descrip' => $t,
235 'terms_duedays' => 0,
236 'terms_discprcnt' => 0,
237 'terms_cutoffday' => 0,
244 $this->jerr("error occur on insert a terms " . $t);
247 return $terms->pid();
250 function addrValue($row)
252 $australiaStates = array(
253 'ACT' => 'Australian Capital Territory',
254 'JBT' => 'Jervis Bay Territory',
255 'NSW' => 'New South Wales',
256 'NT' => 'Northern Territory',
257 'QLD' => 'Queensland',
258 'SA' => 'South Australia',
261 'WA' => 'Western Australia'
266 'SHIP TO 3' => 'addr_line3',
267 'SHIP TO 2' => 'addr_line2',
268 'SHIP TO 1' => 'addr_line1'
275 foreach (array_keys($address) as $a){
280 foreach ($australiaStates as $k => $v){
281 if(!preg_match("/{$k}\s{0,}[0-9]{0,}$/", $row[$a])){
284 $city = trim(preg_replace("/{$k}\s{0,}[0-9]{0,}$/", '', $row[$a]));
286 $postalcode = trim(preg_replace("/{$city}/", '', $row[$a]));
294 foreach ($address as $k => $v){
296 $row['SHIP TO 3'] = implode(' ', array($row['SHIP TO 3'], $row['SHIP TO 4']));
301 'addr_line1' => isset($address['SHIP TO 1']) ? $row['SHIP TO 1'] : '',
302 'addr_line2' => isset($address['SHIP TO 2']) ? $row['SHIP TO 2'] : '',
303 'addr_line3' => isset($address['SHIP TO 3']) ? $row['SHIP TO 3'] : '',
304 'addr_city' => $city,
305 'addr_state' => $state,
306 'addr_postalcode' => $postalcode