ec_email_order_shipped

one of the documented procedures in this installation of the ACS
Usage:
ec_email_order_shipped   shipment_id
What it does:
Use this to send out the "Order Shipped" email after a shipment is made (full or partial order).
Defined in: /web/philip/tcl/ecommerce-email.tcl

Source code:


    
    set db [ns_db gethandle subquery]

    set selection [ns_db 1row $db "select u.email, u.user_id, s.shipment_date, s.address_id, o.order_state, o.order_id
    from ec_orders o, users u, ec_shipments s
    where o.user_id = u.user_id
    and o.order_id = s.order_id
    and s.shipment_id=$shipment_id"]
    set_variables_after_query

    set shipped_date [util_AnsiDatetoPrettyDate $shipment_date]

    # get item_summary
    set selection [ns_db select $db "select p.product_name, p.one_line_description, p.product_id, i.price_charged, i.price_name, count(*) as quantity
from ec_items i, ec_products p
where i.product_id=p.product_id
and i.shipment_id=$shipment_id
group by p.product_name, p.one_line_description, p.product_id, i.price_charged, i.price_name"]

    set item_list [list]
    while { [ns_db getrow $db $selection] } {
	set_variables_after_query
	lappend item_list "Quantity $quantity: $product_name; $price_name: [ec_pretty_price $price_charged]"
    }

    set item_summary [join $item_list "\n"]

    set address [ec_pretty_mailing_address_from_ec_addresses $db $address_id]

    # see whether this completes the order
    if { $order_state == "fulfilled" } {
	set order_completion_sentence "This completes your order."
    } else {
	set order_completion_sentence "There is still more to come.  We will let you know when\nthe rest of your order ships."
    }

    set customer_service_signature [ec_customer_service_signature]
    set system_url "[ec_insecure_url]"

    # have to get rid of ampersands in above variables because they 
    # mess up regsubs
    regsub -all "&" $item_summary {\\&} item_summary
    regsub -all "&" $address {\\&} address
    regsub -all "&" $order_completion_sentence {\\&} order_completion_sentence
    regsub -all "&" $customer_service_signature {\\&} customer_service_signature
    regsub -all "&" $system_url {\\&} system_url
    
    # Note: template #2 is defined to be the "Order Shipped" email
    set selection [ns_db 1row $db "select subject as email_subject, message as email_body, issue_type_list from ec_email_templates where email_template_id=2"]
    set_variables_after_query

    # and get rid of ctrl-M's in the body
    regsub -all "\r" $email_body "" email_body

    regsub -all "shipped_date_here" $email_body $shipped_date email_body
    regsub -all "item_summary_here" $email_body $item_summary email_body
    regsub -all "address_here" $email_body $address email_body
    regsub -all "sentence_about_whether_this_completes_the_order_here" $email_body $order_completion_sentence email_body
    regsub -all "customer_service_signature_here" $email_body $customer_service_signature email_body
    regsub -all "system_url_here" $email_body $system_url email_body

    ns_db dml $db "begin transaction"

    # create a customer service issue/interaction/action
    set user_identification_and_issue_id [ec_customer_service_simple_issue $db "" "automatic" "email" "[DoubleApos "To: $email\nFrom: [ad_parameter CustomerServiceEmailAddress ecommerce]\nSubject: $email_subject"]" $order_id $issue_type_list $email_body $user_id]

    set user_identification_id [lindex $user_identification_and_issue_id 0]
    set issue_id [lindex $user_identification_and_issue_id 1]

    # add a row to the automatic email log
    ns_db dml $db "insert into ec_automatic_email_log
    (user_identification_id, email_template_id, order_id, shipment_id, date_sent)
    values
    ($user_identification_id, 2, $order_id, $shipment_id, sysdate)
    "

    ns_db dml $db "end transaction"

    set email_from [ec_customer_service_email_address $user_identification_id $issue_id]
   
    ec_sendmail_from_service "$email" "$email_from" "$email_subject" "$email_body"

    ns_db releasehandle $db


philg@mit.edu