0

I am trying to assist a friend in importing their legacy data into the new DevDemon Subscriptions add-on. I have analyzed the database tables and data and feel it should be populating properly at this point. The database tables are all populated, but the module interface is not displaying the entries in EE CP.

Here is the script, all be it rough and archaic, that we are testing with. Is there something obvious that I am missing in this? Is there a process trigger that needs to be run against them?

    <div id="container">
        <div id="form">
            <?php
                include "connection.php"; //Connect to Database

                //Upload File
                if (isset($_POST['submit'])) {
                    if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
                        echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
                    }

                    $csvRow = 1;

                    //Import uploaded file to Database
                    $csvHandle = fopen($_FILES['filename']['tmp_name'], "r");

                    $csvKey = 0; // Set the parent array key to 0

                    while (($csvData = fgetcsv($csvHandle)) !== FALSE) {

                        if($csvRow == 1){
                            $csvRow++;
                            continue;
                        }

                        $c = count($csvData); // Count the total keys in each row

                        // 33-40 are dates that need to be converted
                        $dateArray = array(33, 34, 35, 36, 37, 38, 39, 40);

                        // ## Flag variable ##########
                        $empty = true;

                        for ($x = 0; $x < $c; $x++) { // Loop through the "columns"

                            // ## Test each value ##########
                            $empty = $empty && (empty($csvData[$x]));

                            if(in_array($x, $dateArray)){
                                $date = date_create($csvData[$x]);
                                $FormattedDate = date_format($date,"Y-m-d H:i:s");
                                $csvData[$x] = $FormattedDate;
                            }
                        }


                        // Build the variables from the CSV
                        $csv_id = $csvData[0];
                        $csv_site_id = $csvData[1];
                        $csv_hash = $csvData[2];
                        $csv_member_id = $csvData[3];
                        $csv_card_id = $csvData[4];
                        $csv_coupon_id = $csvData[5];
                        $csv_plan_id = $csvData[6];
                        $csv_voucher_id = $csvData[7];
                        $csv_entry_id = $csvData[8];
                        $csv_recurring_interval = $csvData[9];
                        $csv_recurring_interval_count = $csvData[10];
                        $csv_amount = $csvData[11];
                        $csv_total_amount = $csvData[12];
                        $csv_total_cycles = $csvData[13];
                        $csv_status = $csvData[14];
                        $csv_renew = $csvData[15];
                        $csv_trial_starts_at = $csvData[16];
                        $csv_trial_ends_at = $csvData[17];
                        $csv_retry_count = $csvData[18];
                        $csv_retry_date = $csvData[19];
                        $csv_shipping_name = $csvData[20];
                        $csv_shipping_address1 = $csvData[21];
                        $csv_shipping_address2 = $csvData[22];
                        $csv_shipping_address3 = $csvData[23];
                        $csv_shipping_city = $csvData[24];
                        $csv_shipping_state = $csvData[25];
                        $csv_shipping_zip = $csvData[26];
                        $csv_shipping_country = $csvData[27];
                        $csv_notes = $csvData[28];
                        $csv_test_mode = $csvData[29];
                        $csv_created_by = $csvData[30];
                        $csv_updated_by = $csvData[31];
                        $csv_canceled_by = $csvData[32];
                        $csv_next_billing_at = $csvData[33];
                        $csv_last_billed_at = $csvData[34];
                        $csv_ended_at = $csvData[35];
                        $csv_canceled_at = $csvData[36];
                        $csv_expires_at = $csvData[37];
                        $csv_created_at = $csvData[38];
                        $csv_updated_at = $csvData[39];
                        $csv_deleted_at = $csvData[40];

                        // Create the Card
                        $sql_subs_cards = "" . "INSERT INTO exp_subs_cards(id,site_id,hash,member_id,cvc,name_on_card,brand,country,last4,exp_month,exp_year,billing_name,billing_address1,billing_address2,billing_address3,billing_city,billing_state,billing_zip,billing_country,gateway,gateway_card_id,gateway_customer_id,test_mode,created_by,updated_by,created_at,updated_at) VALUES('NULL','$csv_site_id','$csv_hash','$csv_member_id','000','IMPORTED MEMBER','IMPORT','NULL','0000','12','2099','IMPORTED MEMBER','IMPORTED MEMBER','NULL','NULL','IMPORTED','IMPORTED','IMPORT','NA','NULL','NULL','NULL','NULL','IMPORT SCRIPT','IMPORT SCRIPT','$csv_created_at','$csv_updated_at')";
                        mysql_query($sql_subs_cards) or die(mysql_error());

                        // Get the Card ID
                        $sql_card_query = mysql_query("SELECT id FROM exp_subs_cards WHERE member_id = '$csv_member_id'");
                        $sql_card_query_row = mysql_fetch_row($sql_card_query);
                        $new_card_id =  $sql_card_query_row[0];

                        // Create the Subscription
                        $sql_subs = "" . "INSERT INTO exp_subs(id,site_id,hash,member_id,card_id,coupon_id,plan_id,voucher_id,entry_id,recurring_interval,recurring_interval_count,amount,total_amount,total_cycles,status,renew,trial_starts_at,trial_ends_at,retry_count,retry_date,shipping_name,shipping_address1,shipping_address2,shipping_address3,shipping_city,shipping_state,shipping_zip,shipping_country,notes,test_mode,created_by,updated_by,canceled_by,next_billing_at,last_billed_at,ended_at,canceled_at,expires_at,created_at,updated_at,deleted_at) VALUES('NULL','$csv_site_id','$csv_hash','$csv_member_id','$new_card_id','$csv_coupon_id','$csv_plan_id','$csv_voucher_id','$csv_entry_id','$csv_recurring_interval','$csv_recurring_interval_count','$csv_amount','$csv_total_amount','$csv_total_cycles','$csv_status','$csv_renew','$csv_trial_starts_at','$csv_trial_ends_at','$csv_retry_count','$csv_retry_date','$csv_shipping_name','$csv_shipping_address1','$csv_shipping_address2','$csv_shipping_address3','$csv_shipping_city','$csv_shipping_state','$csv_shipping_zip','$csv_shipping_country','$csv_notes','$csv_test_mode','$csv_created_by','$csv_updated_by','$csv_canceled_by','$csv_next_billing_at','$csv_last_billed_at','$csv_ended_at','$csv_canceled_at','$csv_expires_at','$csv_created_at','$csv_updated_at','$csv_deleted_at')";
                        mysql_query($sql_subs) or die(mysql_error());

                        // Get the Subscription ID
                        $sql_subs_query = mysql_query("SELECT id FROM exp_subs WHERE member_id = '$csv_member_id' AND card_id = '$new_card_id'");
                        $sql_subs_query_row = mysql_fetch_row($sql_subs_query);
                        $new_subs_id =  $sql_subs_query_row[0];

                        // Create the Invoice
                        $sql_subs_invoices = "" . "INSERT INTO exp_subs_invoices(id,site_id,member_id,card_id,coupon_id,subscription_id,voucher_id,hash,currency,coupon_discount,tax,subtotal,total,test_mode,paid_at,created_by,updated_by,created_at,updated_at) VALUES('NULL','$csv_site_id','$csv_member_id','$new_card_id','$csv_coupon_id','$new_subs_id','$csv_voucher_id','$csv_hash','NULL','NULL','NULL','$csv_amount','$csv_total_amount','$csv_test_mode','NULL','$csv_created_by','$csv_updated_by','$csv_created_at','$csv_updated_at')";
                        mysql_query($sql_subs_invoices) or die(mysql_error());

                        // Get the Invoice ID
                        $sql_invoice_query = mysql_query("SELECT id FROM exp_subs_invoices WHERE member_id = '$csv_member_id' AND card_id = '$new_card_id' AND subscription_id = '$new_subs_id'");
                        $sql_invoice_query_row = mysql_fetch_row($sql_invoice_query);
                        $new_invoice_id =  $sql_invoice_query_row[0];

                        // Create the Charges
                        $sql_subs_charges = "" . "INSERT INTO exp_subs_charges(id,site_id,hash,member_id,card_id,invoice_id,subscription_id,voucher_id,mrr,gateway,gateway_id,gateway_data,amount,fees,paid,refunded,currency,cc_brand,cc_last4,description,status,failure_message,test_mode,created_by,updated_by,created_at,updated_at) VALUES('NULL','$csv_site_id','$csv_hash','$csv_member_id','$new_card_id','$new_invoice_id','$new_subs_id','$csv_voucher_id','NULL','NULL','NULL','NULL','$csv_amount','NULL','1','0','NULL','IMPORT','0000','IMPORTED MEMBER','success','NULL','$csv_test_mode','$csv_created_by','$csv_updated_by','$csv_created_at','$csv_updated_at')";
                        mysql_query($sql_subs_charges) or die(mysql_error());

                        // Get the Charges ID
                        $sql_charges_query = mysql_query("SELECT id FROM exp_subs_charges WHERE member_id = '$csv_member_id' AND card_id = '$new_card_id' AND subscription_id = '$new_subs_id'");
                        $sql_charges_query_row = mysql_fetch_row($sql_charges_query);
                        $new_charges_id =  $sql_charges_query_row[0];

                        // Create the Invoice Lines
                        $sql_subs_invoices_lines = "" . "INSERT INTO exp_subs_invoices_lines(id,invoice_id,type,description,quantity,amount,total,period_start,period_end,created_by,updated_by,created_at,updated_at) VALUES('NULL','$new_invoice_id','subscription','Imported Subscription Membership','1','$csv_amount','$csv_total_amount','NULL','NULL','$csv_created_by','$csv_updated_by','$csv_created_at','$csv_updated_at')";
                        mysql_query($sql_subs_invoices_lines) or die(mysql_error());

                        // Create a LOG
                        $sql_subs_logs = "" . "INSERT INTO exp_subs_logs(id,site_id,member_id,card_id,charge_id,invoice_id,subscription_id,voucher_id,coupon_id,plan_id,action,email_subject,email_to,test_mode,log_data,created_at) VALUES('NULL','$csv_site_id','$csv_member_id','$new_card_id','$new_charges_id','$new_invoice_id','$new_subs_id','$csv_voucher_id','$csv_coupon_id','$csv_plan_id','IMPORTED','NULL','NULL','$csv_test_mode','NULL','$csv_created_at')";
                        mysql_query($sql_subs_logs) or die(mysql_error());

                        // ## Stop loop if all empty ##########
                        if ($empty) {
                            break;
                        }
                        $csvKey++;
                    }

                    fclose($csvHandle);

                    print "<br />Import done";
                }else {
                    print "Upload new csv by browsing to file and clicking on Upload<br />\n";
                    print "<form enctype='multipart/form-data' action='upload.php' method='post'>";
                    print "File name to import:<br />\n";
                    print "<input size='50' type='file' name='filename'><br />\n";
                    print "<input type='submit' name='submit' value='Upload'></form>";
                }
            ?>

        </div>
    </div>
</body>

The CSV file contains the following data:

id  site_id hash    member_id   card_id coupon_id   plan_id voucher_id  entry_id    recurring_interval  recurring_interval_count    amount  total_amount    total_cycles    status  renew   trial_starts_at trial_ends_at   retry_count retry_date  shipping_name   shipping_address1   shipping_address2   shipping_address3   shipping_city   shipping_state  shipping_zip    shipping_country    notes   test_mode   created_by  updated_by  canceled_by next_billing_at last_billed_at  ended_at    canceled_at expires_at  created_at  updated_at  deleted_at
1   95e2e26a-26de-49cc-80f2-9ef01cc8f918    884 1   NULL    1   NULL    NULL    year    1   5000    5000    1   active  0   NULL    NULL    0   NULL    0   0   0       0   0   0   0   NULL    1   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    2/27/2015 13:59 2/27/2015 13:59 NULL
2
  • Is test_mode a 0 or 1 ? You might need to switch your CP to show test data or live data. is the dashboard populated with numbers? Aug 22 '15 at 16:05
  • Switching doesn't change anything. All of the data shows up in the database and all data shows in the CP EXCEPT for the subscriptions. No Subscriptions are shown.
    – W3bGuy
    Aug 26 '15 at 22:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.