API tools faq
paste
Advertisement
Anakthewolf

Import data from XLSX to MySQL - JSON

Anakthewolf
Aug 23rd, 2014
360
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Perl 11.09 KB | None | 0 0
raw download clone embed print report
  1. #!/usr/bin/perl
  2.  
  3. # import data to easyprofile from xlsx file - by Anak | anak@cpan.org - 22.08.2014 - GNU GPL v2
  4.  
  5. use strict;
  6. use warnings;
  7. use feature 'say','switch';
  8.  
  9. use Data::Dumper;
  10. use DBI;
  11. use JSON;
  12. use Spreadsheet::XLSX;
  13.  
  14. our $version='0.5';
  15.  
  16.  
  17. my $xlsxDocument='doc.xlsx';
  18. my $excel=Spreadsheet::XLSX->new($xlsxDocument);
  19. my $json=JSON->new->allow_nonref;
  20.  
  21. my $dbh=dbConnect('user','pass','dbname','localhost');
  22. my $dbTable='lw5pv_jsn_fields';
  23.  
  24.  
  25. foreach my $sheet (@{$excel->{Worksheet}}) {
  26.     foreach my $row(1..$sheet->{MaxRow}) {
  27.  
  28.         my $regione=$sheet->{Cells}[$row][0]->{Val};
  29.  
  30.         #my $provincia=$sheet->{Cells}[$row][11]->{Val};
  31.         #$provincia=$sheet->{Cells}[$row][1]->{Val} if $provincia eq '';
  32.         my $provincia=$sheet->{Cells}[$row][1]->{Val};
  33.        
  34.         #my $siglaProvincia=$sheet->{Cells}[$row][12]->{Val};
  35.         #$siglaProvincia=$sheet->{Cells}[$row][2]->{Val} if $siglaProvincia eq '';
  36.         my $siglaProvincia=$sheet->{Cells}[$row][2]->{Val};
  37.        
  38.         my $comune=$sheet->{Cells}[$row][4]->{Val};
  39.         my $codiceComune=$sheet->{Cells}[$row][3]->{Val};
  40.        
  41.         my $istituto=$sheet->{Cells}[$row][9]->{Val}.' '.$sheet->{Cells}[$row][6]->{Val};
  42.         my $codiceIstituto=$sheet->{Cells}[$row][5]->{Val};
  43.  
  44.         $regione =~ s/ |'|"//g;
  45.         $provincia =~ s/ |'|"//g;
  46.         $comune =~ s/ |'|"//g;
  47.         $istituto =~ s/ |'|"//g;
  48.  
  49.         say 'Regione: '.$regione;
  50.         say 'Provincia: '.$provincia;
  51.         say 'Comune: '.$comune;
  52.         say 'Istituto: '.$istituto;
  53.  
  54.         my ($idRecordRegione, $aliasRegione)=fromDb(
  55.             'regione',
  56.             $regione,
  57.             ''
  58.         );
  59.  
  60.         my ($idRecordProvincia, $aliasProvincia)=fromDb(
  61.             'provincia',
  62.             $regione,
  63.             $idRecordRegione,
  64.             $provincia,
  65.             $siglaProvincia,
  66.             $aliasRegione
  67.         );
  68.  
  69.         my ($idRecordComune, $aliasComune)=fromDb(
  70.             'comune',
  71.             $provincia,
  72.             $idRecordProvincia,
  73.             $comune,
  74.             $codiceComune,
  75.             $aliasRegione.'/'.$aliasProvincia
  76.         );
  77.  
  78.         my ($idRecordIstituto, $aliasIstituto)=fromDb(
  79.             'istituto',
  80.             $comune,
  81.             $idRecordComune,
  82.             $istituto,
  83.             $codiceIstituto,
  84.             $aliasRegione.'/'.$aliasProvincia.'/'.$aliasComune
  85.         )
  86.     }
  87. }
  88.  
  89. say '--> Importazione completata con successo';
  90. exit;
  91.  
  92. sub fromDb {
  93.     my $target=shift;
  94.     my ($idRecord,$path,$jsonData);
  95.     my $alias='';
  96.     $path='insegnante_scuola_pubblica';
  97.    
  98.     given ($target) {
  99.         when ('regione') {
  100.             my $regione=shift;
  101.             my $title='Regione';
  102.             $path.='/regione';
  103.             ($idRecord,$alias)=editRecord($title, $regione, $regione, $path, '')
  104.         }
  105.        
  106.         when ('provincia') {
  107.             my ($regione, $idRecordRegione, $provincia, $siglaProvincia,$aliasRegione)=@_;
  108.             my $title='Province_'.$regione;
  109.             'target provincia - idRecordRegione='.$idRecordRegione;
  110.             $path.='/'.$aliasRegione;
  111.             ($idRecord,$alias)=editRecord($title, $provincia, $siglaProvincia, $path, $idRecordRegione)        
  112.         }
  113.  
  114.         when ('comune') {
  115.             my ($provincia, $idRecordProvincia, $comune, $codiceComune,$aliasProvincia)=@_;
  116.             my $title='Comuni_'.$provincia;
  117.             'target comune - idRecordprovincia='.$idRecordProvincia;
  118.             $path.='/'.$aliasProvincia;
  119.             ($idRecord,$alias)=editRecord($title, $comune, $codiceComune, $path, $idRecordProvincia)           
  120.         }
  121.  
  122.         when ('istituto') {
  123.             my ($comune, $idRecordComune, $istituto, $codiceIstituto,$aliasComune)=@_;
  124.             my $title='Scuole_'.$comune;
  125.             'target istituto - idRecordComune='.$idRecordComune;
  126.             $path.='/'.$aliasComune;
  127.             ($idRecord,$alias)=editRecord($title, $istituto, $codiceIstituto, $path, $idRecordComune)          
  128.         }
  129.     }
  130.     return ($idRecord,$alias)
  131. }
  132.  
  133. sub editRecord {
  134.     my $title=shift;
  135.     my $optionName=shift;
  136.     my $optionValue=shift;
  137.     my $path=shift;
  138.     my $idSelectParent=shift;
  139.  
  140.     (my $alias=$title) =~ s/ /_/g;
  141.  
  142.     #verifica esistenza select
  143.     my ($idRecord,$jsonData)=$dbh->selectrow_array(
  144.         'SELECT id, params
  145.         FROM '.$dbTable.'
  146.         WHERE title=\''.$title.'\''
  147.     );
  148.  
  149.     if ($idRecord) {
  150.         # la select esiste, verifica se inserire $optionName
  151.         my $param=$json->decode($jsonData);
  152.  
  153.         if ($param->{'select_options'} !~ /$optionName/) {
  154.             # fare query update previo inserimento in params della regione $valueCell
  155.  
  156.             #say '--> OPTION PROVINCIA inserimento di '.$optionName;
  157.  
  158.             $param->{'select_options'}.="\n".$optionValue.'|'.$optionName;
  159.             $jsonData=$json->encode($param);
  160.  
  161.             dbUpdate ($jsonData,$idRecord)
  162.         }
  163.     } else {
  164.         my $parentGroup=12; # gruppo insegnante scuola pubblica
  165.         my $level=2; # profondità rispetto al nodo origine
  166.         my $edit=1;
  167.  
  168.         my $param=setJsonParam();
  169.         $param->{'select_options'}.="\n".$optionValue.'|'.$optionName;
  170.        
  171.         $jsonData=$json->encode($param);
  172.         $idRecord=dbInsert($parentGroup, $level, $path, $title, $alias, $edit, $jsonData);
  173.        
  174.         # set del param del parent!!! non dell'attuale, i dati che passo mi occorrono per leggerne i valori e modificarlo tramite update
  175.         if ($idSelectParent ne '') {
  176.             setDipendence($idSelectParent,$title)
  177.         } # regione ha solo child
  178.    
  179.     }
  180.     say "ALIAS ".$alias;
  181.     return ($idRecord,$alias)
  182. }
  183.  
  184. sub dbInsert {
  185.     my ($parent, $level, $path, $title, $alias, $edit, $params)=@_;
  186.    
  187.     my $sth = $dbh->prepare("
  188.         INSERT INTO ".$dbTable." (
  189.             parent_id,
  190.             level,
  191.             path,
  192.             title,
  193.             alias,
  194.             published,
  195.             type,
  196.             core,
  197.             required,
  198.             profile,
  199.             edit,
  200.             register,
  201.             search,
  202.             checked_out,
  203.             checked_out_time,
  204.             access,
  205.             accessview,
  206.             params,
  207.             created_user_id,
  208.             version
  209.         )
  210.         VALUES (?,?,?,?,?,'1','selectlist','0','1','1',?,'1','0','0','0000-00-00 00:00:00','1','1',?,'42','1')
  211.     ") or die $_;
  212.     $sth->execute($parent, $level, $path, $title, $alias, $edit, $params) or die $_;
  213.     my $idRecord=$sth->{mysql_insertid};
  214.     say 'IDRECORD '.$idRecord;
  215.    
  216.     $dbh->do("ALTER TABLE lw5pv_jsn_users ADD $alias VARCHAR(255) NULL");
  217.  
  218.     $dbh->commit;
  219.     return $idRecord
  220. }
  221.  
  222. sub dbUpdate {
  223.     my ($jsonData,$idRecord)=@_;
  224.    
  225.     my $sth=$dbh->prepare('
  226.         UPDATE '.$dbTable.'
  227.         SET params=?
  228.         WHERE id=?
  229.     ') or die $_;
  230.     $sth->execute($jsonData,$idRecord) or die $dbh->errstr;
  231.     $dbh->commit
  232. }
  233.  
  234. sub dbConnect {
  235.     my ($dbUser,$dbPass,$dbName,$dbHost)=@_;
  236.    
  237.     my $dsn="DBI:mysql:database=$dbName;host=$dbHost";
  238.     my $dbh=DBI->connect(
  239.         $dsn,
  240.         $dbUser,
  241.         $dbPass,
  242.         {RaiseError => 1}
  243.     ) or die DBI->errstr;
  244.  
  245.     $dbh->{AutoCommit}=0;
  246.     return $dbh
  247. }
  248.  
  249. sub setDipendence {
  250.     my $idSelectParent=shift; # record da modificare
  251.     my $title=shift; # sua option da modificare
  252.    
  253.     # 1 fai la select e preleva il valore della colonna params del record parent
  254.         my $jsonData=$dbh->selectrow_array(
  255.             'SELECT params
  256.             FROM '.$dbTable.'
  257.             WHERE id=\''.$idSelectParent.'\''
  258.         );
  259.  
  260.     # 2 converti da json ad hash il valore della colonna params
  261.         my $param=$json->decode($jsonData);
  262.    
  263.     # 3 identifica il valore $optionValue della option parent (che ha il nome $title)
  264.         my @optionName=split(/_/,$title);
  265.         my $optionName=pop(@optionName);
  266.         my $optionValue;
  267.        
  268.         my @paramOption=split(/\n/,$param->{'select_options'});
  269.         foreach my $option(@paramOption) {
  270.             if ($option =~ /\|/) {# se esiste il separatore usato dall'applicazione per la coppia value|name
  271.                 my @optionDb=split(/\|/,$option);
  272.                 if ($optionName eq $optionDb[1]) {
  273.                     $optionValue=$optionDb[0];
  274.                     last
  275.                 }
  276.             }
  277.         }
  278.  
  279.     # 4 verifica quante condizioni sono già state inserite nella select e aggiungi un valore con la condizione di dipendenza della select child
  280.     #    usare la prima uguale a zero, se sono tutte diverse da 0, aggiunge una (l'indice ce lo hai)
  281.         my ($conditionCounter,$okCondition)=('',0);
  282.         for (grep /^condition_operator*/, sort(keys($param))) {
  283.             if ($param->{$_} == 0) {
  284.                 #say $_.'-'.$param->{$_};
  285.                 $okCondition=1;
  286.                 last
  287.             }
  288.             $conditionCounter++
  289.         }
  290.         if (! $okCondition) {
  291.             $conditionCounter++
  292.         }
  293.         $param->{'condition_operator'.$conditionCounter}='1'; # uguale a...
  294.         $param->{'condition_field'.$conditionCounter}='_custom'; # un valore di una sua option
  295.         $param->{'condition_custom'.$conditionCounter}=$optionValue; # con valore.
  296.         $param->{'condition_hide'.$conditionCounter}=[$title]; # nome della select dipendente
  297.         $param->{'condition_action'.$conditionCounter}='show'; # azione (mostrala se la condizione è verificata)
  298.        
  299.         say 'TARGET OPTION NAME '.$title;
  300.  
  301.     # 5 effettua update della cella param in mysql dopo aver riconvertito in json i dati
  302.         $jsonData=$json->encode($param);
  303.        
  304.         dbUpdate ($jsonData,$idSelectParent);
  305. }
  306.  
  307. sub setJsonParam {
  308.     my $param={
  309.         hidetitle => '0',
  310.         privacy => '0',
  311.         privacy_default => '0',
  312.  
  313.         condition_operator => '0',
  314.         condition_field => '_custom',
  315.         condition_custom => '',
  316.         condition_hide => '',
  317.         condition_action => 'show',
  318.  
  319.         condition_operator1 => '0',
  320.         condition_field1 => '_custom',
  321.         condition_custom1 => '',
  322.         condition_action1 => 'hide',
  323.  
  324.         condition_operator2 => '0',
  325.         condition_field2 => '_custom',
  326.         condition_custom2 => '',
  327.         condition_action2 => 'hide',
  328.  
  329.         condition_operator3 => '0',
  330.         condition_field3 => '_custom',
  331.         condition_custom3 => '',
  332.         condition_action3 => 'hide',
  333.  
  334.         condition_operator4 => '0',
  335.         condition_field4 => '_custom',
  336.         condition_custom4 => '',
  337.         condition_action4 => 'hide',
  338.  
  339.         condition_operator5 => '0',
  340.         condition_field5 => '_custom',
  341.         condition_custom5 => '',
  342.         condition_action5 => 'hide',
  343.  
  344.         condition_operator6 => '0',
  345.         condition_field6 => '_custom',
  346.         condition_custom6 => '',
  347.         condition_action6 => 'hide',
  348.  
  349.         condition_operator7 => '0',
  350.         condition_field7 => '_custom',
  351.         condition_custom7 => '',
  352.         condition_action7 => 'hide',
  353.  
  354.         condition_operator8 => '0',
  355.         condition_field8 => '_custom',
  356.         condition_custom8 => '',
  357.         condition_action8 => 'hide',
  358.  
  359.         condition_operator9 => '0',
  360.         condition_field9 => '_custom',
  361.         condition_custom9 => '',
  362.         condition_action9 => 'hide',
  363.  
  364.         checkbox_defaultvalue => '',
  365.         checkbox_options => '',
  366.         checkbox_inline => '0',
  367.         date_default => '',
  368.         date_format => 'Y-m-d',
  369.         date_type => '0',
  370.         date_viewmode => 'days',
  371.         date_span => '0',
  372.         date_span_year => '0',
  373.         date_span_month => '0',
  374.         date_span_day => '0',
  375.         email_defaultvalue => '',
  376.         email_placeholder => '',
  377.         filetype_ext => 'pdf|zip|doc|docx',
  378.         filetype_label => '',
  379.         gmap_showmap => '1',
  380.         gmap_radius => '10',
  381.         gmap_unit => 'Km',
  382.         hide_defaultvalue => '',
  383.         image_width => '200',
  384.         image_height => '200',
  385.         image_thumbwidth => '75',
  386.         image_thumbheight => '75',
  387.         image_defaultvalue => '',
  388.         image_alt => '',
  389.         image_class => '',
  390.         image_cropwebcam => '0',
  391.         link_defaultvalue => '',
  392.         link_placeholder => '',
  393.         link_maxlength => '255',
  394.         numeric_defaultvalue => '',
  395.         numeric_max => '999999',
  396.         numeric_min => '0',
  397.         numeric_step => '1',
  398.         phone_defaultvalue => '',
  399.         phone_placeholder => '',
  400.         radio_defaultvalue => '',
  401.         radio_options => '',
  402.         radio_inline => '0',
  403.  
  404.         select_defaultvalue => '',
  405.         select_options => '',
  406.         select_multiple => '0',
  407.         select_dbopttable => '',
  408.         select_dboptvalue => '',
  409.         select_dbopttext => '',
  410.         select_dboptwhere => '',
  411.         text_defaultvalue => '',
  412.         text_placeholder => '',
  413.         text_maxlength => '255',
  414.         text_regex => '',
  415.         text_customregex => '',
  416.         text_readonly => '0',
  417.         textarea_defaultvalue => '',
  418.         textarea_type => 'textarea',
  419.         video_defaultvalue => '',
  420.         video_placeholder => '',
  421.         video_maxlength => '255'
  422.     };
  423.     return $param
  424. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!