[set page_banner]Interchange Orders[/set] [set fm_class]Orders[/set] [set help_name]interchange orders[/set] [set page_title]Interchange Orders[/set] [set icon_name]icon_item.gif[/set] @_FM_STD_HEAD_@ [seti error][perl tables="transactions"] sub newCustomer { my ($dbh, $custnr) = @_; $custnr = $dbh->quote($custnr); my $q = "SELECT company, fname, lname, address1, address2, address3, city, zip, phone_day, mv_shipmode, email, fax FROM userdb WHERE username = $custnr"; my $sth = $dbh->prepare($q) or return "** Error preparing $q"; $sth->execute or return "** Error executing $q"; return $dbh->errstr if $dbh->err; my ($company, $fname, $lname, $addr1, $addr2, $addr3, $city, $zip, $phone, $ship, $email, $fax) = $sth->fetchrow; my $contact_id = "currval('co_contactsequence')"; my $attention = $fname . " " . $lname; my $custtype = ($company) ? 60002 : 60001; $company = $dbh->quote($company); $fname = $dbh->quote($fname); $lname = $dbh->quote($lname); $addr1 = $dbh->quote($addr1); $addr2 = $dbh->quote($addr2); $addr3 = $dbh->quote($addr3); $city = $dbh->quote($city); $zip = $dbh->quote($zip); $phone = $dbh->quote($phone); $email = $dbh->quote($email); $fax = $dbh->quote($fax); $attention = $dbh->quote($attention); $q = "INSERT INTO co_contact (type) VALUES (50001); INSERT INTO co_customer (contact_id, customernr, type,custgrp_id, credit_code, deliverycode, paycode, freightcode) VALUES ($contact_id,$custnr,$custtype, 1, 1, 1, 3, 8); INSERT INTO co_address (contact_id,type,country_id,address1,address2,address3,postalcode,city) SELECT $contact_id,70001,48,$addr1,$addr2,$addr3,$zip, name FROM co_postalcodes po WHERE po.postalcode = $zip; INSERT INTO ar_custacct (contact_id,type) VALUES ($contact_id, 100001);"; # ------- Private customer or company if ($custtype == 60001) { $q .= "INSERT INTO co_person (contact_id,firstname,lastname) VALUES ($contact_id, $fname, $lname);"; } else { $q .= "INSERT INTO co_company (contact_id,name,attention) VALUES ($contact_id, $company, $attention);"; } $q .= "INSERT INTO co_telephone (contact_id,type,phonenr,updated) VALUES ($contact_id,90001,$phone,'now');" if ($phone); $q .= "INSERT INTO co_telephone (contact_id,type,phonenr,updated) VALUES ($contact_id,90003,$fax,'now');" if ($fax); $q .= "INSERT INTO co_url (contact_id,type,url,updated) VALUES ($contact_id,80001,$email,'now');" if ($email); $sth = $dbh->prepare($q) or return "** Error preparing $q"; $sth->execute or return "** Error executing $q"; $dbh->commit; }; # ----- Main routine my $dbh = $Sql{transactions}; $dbh->{Autocommit} = 0; my ($ordernr, $modifier); if (!$Values->{select_all}) { $ordernr = $Values->{"select_order"} || return "[L]**Please select all or some orders[/L]" ; $ordernr =~ s/\000/','/g; }; $modifier = " AND t.order_number IN ('$ordernr')" if $ordernr; my $q = "SELECT DISTINCT(username) FROM transactions t WHERE status = 'pending' AND archived=0 AND deleted=0 AND NOT EXISTS (SELECT customernr FROM co_customer cu WHERE t.username = cu.customernr)" . $modifier; my $sth = $dbh->prepare($q) or return "** Error preparing $q"; $sth->execute or return "** Error executing $q"; return $dbh->errstr if $dbh->err; while ( my ($custnr) = $sth->fetchrow_array ) { my $err = newCustomer($dbh, $custnr); return $err if $err; }; # ----- Order head $q = "INSERT INTO so_orderhead (contact_id, status, currency_id, ourref,credit_code, deliverycode,paycode,freightcode,orderdate,ordertotal) SELECT cu.contact_id, 100, 1, t.order_number, 1,1,3,8,'now', to_number(t.total_cost,'99999999999D99') FROM co_customer cu, transactions t WHERE t.archived=0 AND t.deleted=0 AND t.username = cu.customernr" . $modifier; $sth = $dbh->prepare($q) or return "** Error preparing $q"; $sth->execute or return "** Error executing $q"; return $dbh->errstr if $dbh->err; # ----- Order lines $modifier = " AND o.order_number IN ('$ordernr')" if $ordernr; $q = "INSERT INTO so_orderline (ordernr,linenr,art_id,description,qty,unitprice, linetotal,linevat,updated) SELECT oh.ordernr, SUBSTRING(o.code FROM POSITION('-' IN o.code)+1), ar.art_id, RTRIM(o.description), to_number(o.quantity,'99999999999D99'), to_number(o.price,'99999999999D99'), to_number(o.subtotal,'99999999999D99'), to_number(o.subtotal,'99999999999D99')/(100+v.percent)*v.percent FROM orderline o, transactions t, so_orderhead oh, pd_article ar, pd_productgroup pg, pd_vat v WHERE o.order_number = oh.ourref AND o.order_number = t.order_number AND t.archived=0 AND t.deleted=0 AND ar.articlenr = sku AND pg.pgrp_id = ar.pgrp_id AND v.vat_id = pg.vat_id" . $modifier; $sth = $dbh->prepare($q) or return "** Error preparing $q"; $sth->execute or return "** Error executing $q"; return $dbh->errstr if $dbh->err; # ----- Shipping cost $modifier = " AND t.order_number IN ('$ordernr')" if $ordernr; $q = "INSERT INTO so_orderline (ordernr,linenr,art_id,description,qty,unitprice, linetotal,linevat) SELECT oh.ordernr, (SELECT MAX(ol.linenr)+1 FROM so_orderline ol WHERE oh.ordernr = ol.ordernr), ar.art_id, ar.description, 1, to_number(t.shipping,'99999999999D99'), to_number(t.shipping,'99999999999D99'), to_number(t.shipping,'99999999999D99')/(100+v.percent)*v.percent FROM transactions t, so_orderhead oh, pd_article ar, pd_productgroup pg, pd_vat v WHERE t.order_number = oh.ourref AND ar.art_id = (SELECT CASE WHEN t.shipmode LIKE '%Efterkrav%' THEN (SELECT art_id FROM pd_article WHERE altartnr = 'levpo') WHEN t.payment_method = 'Kreditkort' THEN (SELECT art_id FROM pd_article WHERE altartnr = 'levdankort') ELSE (SELECT art_id FROM pd_article WHERE altartnr = 'levgiro') END) AND pg.pgrp_id = ar.pgrp_id AND v.vat_id = pg.vat_id" . $modifier; $sth = $dbh->prepare($q) or return "** Error preparing $q"; $sth->execute or return "** Error executing $q"; return $dbh->errstr if $dbh->err; # ----- update vat total $modifier = " WHERE ordernr IN ('$ordernr')" if $ordernr; $q = "UPDATE so_orderhead SET vattotal = ol.linevat FROM (SELECT ordernr as onum, SUM(linevat) as linevat FROM so_orderline GROUP BY ordernr) ol WHERE ordernr = ol.onum"; $q .= " AND ourref IN ('$ordernr')" if $ordernr; $sth = $dbh->prepare($q) or return "** Error preparing $q"; $sth->execute or return "** Error executing $q"; return $dbh->errstr if $dbh->err; # ----- Update order status $modifier = " AND ourref IN ('$ordernr')" if $ordernr; $q = "UPDATE so_orderhead SET status = 90 WHERE ordernr IN (SELECT oh.ordernr as onum FROM so_orderline ol, so_orderhead oh WHERE art_id = (SELECT art_id FROM pd_article WHERE altartnr = 'levgiro') AND oh.ordernr = ol.ordernr $modifier)"; $sth = $dbh->prepare($q) or return "** Error preparing $q"; $sth->execute or return "** Error executing $q"; return $dbh->errstr if $dbh->err; # ----- Update transactions (remove from IC order page) $modifier = " WHERE order_number IN ('$ordernr')" if $ordernr; $q = "UPDATE transactions SET archived = 1" . $modifier; $sth = $dbh->prepare($q) or return "** Error preparing $q"; $sth->execute or return "** Error executing $q"; return $dbh->errstr if $dbh->err; undef $Values->{select_order}; undef $Values->{select_all}; $rc = $dbh->commit; $Scratch->{ordernr} = $ordernr; $Scratch->{ordernr} ||= "All"; return; [/perl][/seti] [set returnpage]freemoney/soicList[/set] [if scratch error]

[L]There was an error adding the new order. Please check your data.[/L]

[scratch error] [else] [L]Interchange orders ([/L] '[scratchd ordernr]' [L]) are transferred to Freemoney[/L] [/else] [/if] [value name=mv_action set=""]

[L]BACK[/L] @_FM_STD_FOOTER_@