481 lines
26 KiB
PHP
481 lines
26 KiB
PHP
<?php
|
|
|
|
if ($argc < 2 )
|
|
{
|
|
exit( "Usage: program <file.csv> <test=0>\n" );
|
|
}
|
|
|
|
$INSERT = $argv[2];
|
|
|
|
// FIXME : Should adapt to the site hosted on...
|
|
// require_once('../sites/default/settings.php');
|
|
$databases = array (
|
|
'default' =>
|
|
array (
|
|
'default' =>
|
|
array (
|
|
'database' => 'drupal_dev52',
|
|
'username' => 'USER',
|
|
'password' => 'PASSWORD',
|
|
'host' => 'localhost',
|
|
'port' => '',
|
|
'driver' => 'mysql',
|
|
'prefix' => 'drupal_',
|
|
),
|
|
),
|
|
'civi' =>
|
|
array (
|
|
'default' =>
|
|
array (
|
|
'database' => 'civicrm_dev52',
|
|
'username' => 'USER',
|
|
'password' => 'PASSWORD',
|
|
'host' => 'localhost',
|
|
'port' => '',
|
|
'driver' => 'mysql',
|
|
'prefix' => 'civicrm_',
|
|
),
|
|
),
|
|
);
|
|
|
|
// $lineEnd = "\"<BR>\"";
|
|
$lineEnd = "\n";
|
|
|
|
$host=$databases['civi']['default']['host'];
|
|
$user=$databases['civi']['default']['username'];
|
|
$password=$databases['civi']['default']['password'];
|
|
$database=$databases['civi']['default']['database'];
|
|
|
|
$dbhandle=mysqli_connect($host, $user, $password, $database);
|
|
if ($dbhandle->connect_errno) {
|
|
printf("Connect failed: %s\n", $dbhandle->connect_error);
|
|
exit();
|
|
}
|
|
mysqli_begin_transaction($dbhandle);
|
|
|
|
$file = $argv[1];
|
|
echo "<PRE>";
|
|
$csv = array_map('str_getcsv', file($file));
|
|
array_walk($csv, function(&$a) use ($csv) {
|
|
$a = array_combine($csv[0], $a);
|
|
});
|
|
array_shift($csv);
|
|
// echo print_r($csv[0],1);
|
|
/*
|
|
$csv[0][Num_membre_CAB] => 14717
|
|
$csv[0][Montant_total] => 18
|
|
$csv[0][Montant_frais] => 0,5
|
|
$csv[0][QP_F▒d] => 15
|
|
$csv[0][QP_Club] => 3
|
|
$csv[0][Montant net] => 17,5
|
|
$csv[0][Date&Heure_paiement] => 26/07/2017 => 20171003141856
|
|
$csv[0][Mode_paiement] => Carte de cr▒dit
|
|
$csv[0][Type_operation] => Cotisation des membres
|
|
$csv[0][Status] => Termin▒ )
|
|
*/
|
|
|
|
$pf_val_QP_club_id = 9790;
|
|
$pf_id_QP_club_import = 2776;
|
|
|
|
$pf_val_QP_fed_id = 9789;
|
|
$pf_id_QP_fed_import = 2775;
|
|
$price_set_id = 383;
|
|
|
|
foreach ($csv as $csv_det) {
|
|
echo "Line ".$lineEnd;
|
|
// echo $key1." => ".$value1.$lineEnd;
|
|
|
|
$query0 = "select id as ID from civicrm_contact where external_identifier = ".$csv_det["Num_membre_CAB"].";";
|
|
echo __LINE__." ".$query0.$lineEnd;
|
|
$ins0=mysqli_query($dbhandle,$query0);
|
|
if (!$ins0) {
|
|
echo __LINE__." Error:".$query0.$lineEnd;
|
|
}
|
|
$results0=mysqli_fetch_assoc($ins0);
|
|
$contact_id = $results0['ID'];
|
|
if(!isset($contact_id) || $contact_id == ""){
|
|
echo "Error:contact_id:".$contact_id."external_identifier:".$csv_det["Num_membre_CAB"].$lineEnd;
|
|
continue;
|
|
}
|
|
|
|
|
|
$receive_date = substr($csv_det["Date&Heure_paiement"],6,4).substr($csv_det["Date&Heure_paiement"],3,2).substr($csv_det["Date&Heure_paiement"],0,2)."120000" ;
|
|
$Montant_total =$csv_det["Montant_total"];
|
|
$Montant_total = str_replace(",", ".", $Montant_total);
|
|
$label =$csv_det["label"];
|
|
$QP_Fed =$csv_det["QP_Fed"];
|
|
$QP_Fed = str_replace(",", ".", $QP_Fed);
|
|
$QP_Club =$csv_det["QP_Club"];
|
|
$QP_Club = str_replace(",", ".", $QP_Club);
|
|
$Montant_net =$csv_det["Montant net"];
|
|
$Montant_net = str_replace(",", ".", $Montant_net);
|
|
//$Mode_paiement =$csv_det["Mode_paiement"];
|
|
|
|
$source="Import Contribution en ligne : Escal'pade - Cotisation 2018";
|
|
$source = str_replace("'", "\'", $source);
|
|
mysqli_real_escape_string($dbhandle,$source);
|
|
|
|
if(strpos($csv_det["Mode_paiement"],"arte") > 0){
|
|
$Montant_frais = $csv_det["Montant_frais"];
|
|
$payment_instrument_id = 1;
|
|
$payment_processor_id = 11;
|
|
$to_financial_account_id = 17;
|
|
}else{
|
|
$Montant_frais = 0;
|
|
$payment_instrument_id = 4;
|
|
$payment_processor_id = "NULL";
|
|
$to_financial_account_id = 6;
|
|
}
|
|
|
|
$Montant_frais = str_replace(",", ".", $Montant_frais);
|
|
echo __LINE__." Montant_frais:".$Montant_frais;
|
|
$Type_operation =$csv_det["Type_operation"];
|
|
$Status =$csv_det["Status"];
|
|
/* #1 */
|
|
$insert1 = "INSERT INTO civicrm_contribution (contact_id , financial_type_id , contribution_page_id , payment_instrument_id , receive_date , non_deductible_amount , total_amount , fee_amount , net_amount , trxn_id , invoice_id , currency , cancel_reason , receipt_date , source , is_test , is_pay_later , contribution_status_id , address_id , check_number ) VALUES ( $contact_id , 2 , NULL , $payment_instrument_id , $receive_date , $Montant_total , $Montant_total , $Montant_frais , $Montant_net , NULL , NULL , 'EUR' , '0' , $receive_date , '$source' , 0 , 0 , 1 , NULL , NULL );";
|
|
echo $insert1.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert1=mysqli_query($dbhandle,$insert1);
|
|
if (!$resInsert1) {
|
|
echo __LINE__." Error:".$insert1.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
$insert2 = "INSERT INTO civicrm_financial_trxn (to_financial_account_id , trxn_date , total_amount , fee_amount , net_amount , currency , trxn_id , status_id , payment_processor_id , payment_instrument_id , check_number ,is_payment ) VALUES ( $to_financial_account_id , $receive_date , $Montant_total , $Montant_frais , $Montant_net , 'EUR' , NULL , 1 , $payment_processor_id , $payment_instrument_id , NULL , 1 );";
|
|
echo $insert2.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert2=mysqli_query($dbhandle,$insert2);
|
|
if (!$resInsert2) {
|
|
echo __LINE__." Error:".$insert2.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
|
|
/* #2 */
|
|
$query1 = "select max(id) as ID from civicrm_contribution;";
|
|
echo __LINE__." ".$query1.$lineEnd;
|
|
$ins1=mysqli_query($dbhandle,$query1);
|
|
if (!$ins1) {
|
|
echo __LINE__." Error:".$query1." ";
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
$results1=mysqli_fetch_assoc($ins1);
|
|
$Contrib_id = $results1['ID'];
|
|
|
|
$query2 = "select max(id) as ID from civicrm_financial_trxn;";
|
|
echo __LINE__." ".$query2.$lineEnd;
|
|
$ins2=mysqli_query($dbhandle,$query2);
|
|
if (!$ins2) {
|
|
echo __LINE__." Error:".$query2." ";
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
$results2=mysqli_fetch_assoc($ins2);
|
|
$Financial_trans_id = $results2['ID'];
|
|
|
|
$insert3 = "INSERT INTO civicrm_entity_financial_trxn (entity_table , entity_id , financial_trxn_id , amount ) VALUES ('civicrm_contribution' , $Contrib_id , $Financial_trans_id , $Montant_total );";
|
|
echo __LINE__.$insert3.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert3=mysqli_query($dbhandle,$insert3);
|
|
if (!$resInsert3) {
|
|
echo __LINE__." Error:".$insert3.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
|
|
/* #3 */
|
|
|
|
$insert4 = "INSERT INTO civicrm_line_item (entity_table , entity_id ,contribution_id, price_field_id , label , qty , unit_price , line_total , participant_count , price_field_value_id , financial_type_id ) VALUES ('civicrm_contribution' , $Contrib_id ,$Contrib_id , $pf_id_QP_fed_import , '$label' , 1 , $QP_Fed , $QP_Fed, 0 , $pf_val_QP_fed_id, 5 );";
|
|
echo __LINE__." ".$insert4.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert4=mysqli_query($dbhandle,$insert4);
|
|
if (!$resInsert4) {
|
|
echo __LINE__." Error:".$insert4.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
|
|
$query3 = "select max(id) as ID from civicrm_line_item;";
|
|
echo __LINE__." ".$query3.$lineEnd;
|
|
$ins3=mysqli_query($dbhandle,$query3);
|
|
if (!$ins3) {
|
|
echo __LINE__." Error:".$query3." ";
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
$results3=mysqli_fetch_assoc($ins3);
|
|
$civicrm_line_item_id = $results3['ID'];
|
|
$insert5 = "INSERT INTO civicrm_financial_item (transaction_date , contact_id , description , amount , currency , financial_account_id , status_id , entity_table , entity_id ) VALUES ( $receive_date , $contact_id , '$label' , $QP_Fed , 'EUR' , 13 , 1 , 'civicrm_line_item' , $civicrm_line_item_id );";
|
|
echo $insert5.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert5=mysqli_query($dbhandle,$insert5);
|
|
if (!$resInsert5) {
|
|
echo __LINE__." Error:".$insert5.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
$query4 = "select max(id) as ID from civicrm_financial_item;";
|
|
echo __LINE__." ".$query4.$lineEnd;
|
|
$ins4=mysqli_query($dbhandle,$query4);
|
|
if (!$ins4) {
|
|
echo __LINE__." Error:".$query4." ";
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
$results4=mysqli_fetch_assoc($ins4);
|
|
$civicrm_financial_item_id = $results4['ID'];
|
|
$insert6 = "INSERT INTO civicrm_entity_financial_trxn (entity_table , entity_id , financial_trxn_id , amount ) VALUES ('civicrm_financial_item' , $civicrm_financial_item_id , $Financial_trans_id , $QP_Fed);";
|
|
echo $insert6.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert6=mysqli_query($dbhandle,$insert6);
|
|
if (!$resInsert6) {
|
|
echo __LINE__." Error:".$insert6.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
/* #4 QP Club */
|
|
|
|
$insert7 = "INSERT INTO civicrm_line_item (entity_table , entity_id, contribution_id , price_field_id , label , qty , unit_price , line_total , participant_count , price_field_value_id , financial_type_id ) VALUES ('civicrm_contribution' , $Contrib_id, $Contrib_id , $pf_id_QP_club_import , '$label' , 1 , $QP_Club , $QP_Club , 0 , $pf_val_QP_club_id, 6 );";
|
|
echo __LINE__." ".$insert7.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert7=mysqli_query($dbhandle,$insert7);
|
|
if (!$resInsert7){
|
|
echo __LINE__." Error:".$insert7.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
$query5 = "select max(id) as ID from civicrm_line_item;";
|
|
echo __LINE__." ".$query5.$lineEnd;
|
|
$ins5=mysqli_query($dbhandle,$query5);
|
|
if (!$ins5) {
|
|
echo __LINE__." Error:".$query5." ";
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
$results5=mysqli_fetch_assoc($ins5);
|
|
$civicrm_line_item_id2 = $results5['ID'];
|
|
|
|
$insert8 = "INSERT INTO civicrm_financial_item (transaction_date , contact_id , description , amount , currency , financial_account_id , status_id , entity_table , entity_id ) VALUES ( $receive_date , $contact_id , '$label' , $QP_Club , 'EUR' , 14 , 1 , 'civicrm_line_item' , $civicrm_line_item_id2 );";
|
|
echo $insert8.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert8=mysqli_query($dbhandle,$insert8);
|
|
if (!$resInsert8) {
|
|
echo __LINE__." Error:".$insert8.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
$query5 = "select max(id) as ID from civicrm_financial_item;";
|
|
echo __LINE__." ".$query5.$lineEnd;
|
|
$ins5=mysqli_query($dbhandle,$query5);
|
|
if (!$ins5) {
|
|
echo __LINE__." Error:".$query5." ";
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
$results5=mysqli_fetch_assoc($ins5);
|
|
$civicrm_financial_item_id2 = $results5['ID'];
|
|
$insert9 = "INSERT INTO civicrm_entity_financial_trxn (entity_table , entity_id , financial_trxn_id , amount ) VALUES ('civicrm_financial_item' , $civicrm_financial_item_id2 , $Financial_trans_id , $QP_Club );";
|
|
echo __LINE__." ".$insert9.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert9=mysqli_query($dbhandle,$insert9);
|
|
if (!$resInsert9) {
|
|
echo __LINE__." Error:".$insert9.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
|
|
/*#5 Frais */
|
|
|
|
if($Montant_frais > 0){
|
|
|
|
$insert10 = "INSERT INTO civicrm_financial_trxn (from_financial_account_id , to_financial_account_id , trxn_date , total_amount , fee_amount , net_amount , currency , trxn_id , status_id , payment_processor_id , payment_instrument_id , check_number ) VALUES ( 17 , 5 , $receive_date , $Montant_frais , 0 , 0 , 'EUR' , NULL , 1 , $payment_processor_id , $payment_instrument_id , NULL );";
|
|
echo $insert10.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert10=mysqli_query($dbhandle,$insert10);
|
|
if (!$resInsert10) {
|
|
echo __LINE__." Error:".$insert10.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
$query6 = "select max(id) as ID from civicrm_financial_trxn;";
|
|
echo __LINE__." ".$query6.$lineEnd;
|
|
$ins6=mysqli_query($dbhandle,$query6);
|
|
if (!$ins6) {
|
|
echo __LINE__." Error:".$query6." ";
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
$results6=mysqli_fetch_assoc($ins6);
|
|
$Financial_trans_id2 = $results6['ID'];
|
|
|
|
$insert11 = "INSERT INTO civicrm_entity_financial_trxn (entity_table , entity_id , financial_trxn_id , amount ) VALUES ('civicrm_contribution' , $Contrib_id , $Financial_trans_id2 , $Montant_frais );";
|
|
echo $insert11.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert11=mysqli_query($dbhandle,$insert11);
|
|
if (!$resInsert11) {
|
|
echo __LINE__." Error:".$insert11.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
$insert12 = "INSERT INTO civicrm_financial_item (transaction_date , contact_id , description , amount , currency , financial_account_id , status_id , entity_table , entity_id ) VALUES ( $receive_date , 1 , 'Fee' , $Montant_frais , 'EUR' , 5 , 1 , 'civicrm_financial_trxn' , $Financial_trans_id2 );";
|
|
echo $insert12.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert12=mysqli_query($dbhandle,$insert12);
|
|
if (!$resInsert12) {
|
|
echo __LINE__." Error:".$insert12.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
$query7 = "select max(id) as ID from civicrm_financial_item;";
|
|
echo __LINE__." ".$query7.$lineEnd;
|
|
$ins7=mysqli_query($dbhandle,$query7);
|
|
if (!$ins7) {
|
|
echo __LINE__." Error:".$query7." ";
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
|
|
$results7=mysqli_fetch_assoc($ins7);
|
|
$civicrm_financial_item_id3 = $results7['ID'];
|
|
|
|
$insert13 = "INSERT INTO civicrm_entity_financial_trxn (entity_table , entity_id , financial_trxn_id , amount ) VALUES ('civicrm_financial_item' , $civicrm_financial_item_id3, $Financial_trans_id2 , $Montant_frais );";
|
|
echo $insert13.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert13=mysqli_query($dbhandle,$insert13);
|
|
if (!$resInsert13) {
|
|
echo __LINE__." Error:".$insert13.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
}
|
|
|
|
/* Log */
|
|
|
|
$insert14 = "INSERT INTO civicrm_activity (source_record_id , activity_type_id , subject , activity_date_time , status_id , priority_id , is_test ) VALUES ( $Contrib_id, 6 , '$Montant_total euro - $source' , $receive_date , 2, 2 , 0 );";
|
|
echo $insert14.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert14=mysqli_query($dbhandle,$insert14);
|
|
if (!$resInsert14) {
|
|
echo __LINE__." Error:".$insert14.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
|
|
// $activity_id = get civicrm_activity_id
|
|
$query8 = "select max(id) as ID from civicrm_activity;";
|
|
echo __LINE__." ".$query8.$lineEnd;
|
|
$ins8=mysqli_query($dbhandle,$query8);
|
|
if (!$ins8) {
|
|
echo __LINE__." Error:".$query8." ";
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
$results8=mysqli_fetch_assoc($ins8);
|
|
$civicrm_activity_id = $results8['ID'];
|
|
|
|
$insert15 = "INSERT INTO civicrm_activity_contact (activity_id , contact_id , record_type_id ) VALUES ( $civicrm_activity_id , $contact_id , 2 );";
|
|
echo $insert15.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert15=mysqli_query($dbhandle,$insert15);
|
|
if (!$resInsert15) {
|
|
echo __LINE__." Error:".$insert15.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
$insert16 = "INSERT INTO civicrm_log (entity_table , entity_id , data , modified_date ) VALUES ('civicrm_activity' , $civicrm_activity_id , 'Activity created for source=$contact_id' , $receive_date );";
|
|
echo $insert16.$lineEnd;
|
|
if($INSERT == 1){
|
|
$resInsert16=mysqli_query($dbhandle,$insert16);
|
|
if (!$resInsert16) {
|
|
echo __LINE__." Error:".$insert16.$lineEnd;
|
|
mysqli_rollback($dbhandle);
|
|
exit();
|
|
}else{
|
|
mysqli_commit($dbhandle);
|
|
}
|
|
}
|
|
|
|
echo "End "."$lineEnd.$lineEnd.$lineEnd";
|
|
|
|
|
|
}
|
|
echo "</PRE>";
|
|
mysqli_close($dbhandle);
|
|
?>
|