#!/usr/bin/php -q
<?php

include "config.php";

/*
+----------+-------------------------------------------+------+-----+------------+----------------+
| Field    | Type                                      | Null | Key | Default    | Extra          |
+----------+-------------------------------------------+------+-----+------------+----------------+
| id       | int(11)                                   |      | PRI | NULL       | auto_increment |
| name     | varchar(32)                               |      | MUL |            |                |
| ext      | varchar(8)                                |      |     |            |                |
| app      | varchar(16)                               |      |     |            |                |
| source   | varchar(16)                               |      |     |            |                |
| category | varchar(16)                               |      |     |            |                |
| us       | enum('yes','no')                          |      |     | no         |                |
| nl       | enum('yes','no')                          |      |     | no         |                |
| fr       | enum('yes','no')                          |      |     | no         |                |
| de       | enum('yes','no')                          |      |     | no         |                |
| us_text  | text                                      |      |     |            |                |
| nl_text  | text                                      |      |     |            |                |
| fr_text  | text                                      |      |     |            |                |
| de_text  | text                                      |      |     |            |                |
| remarks  | text                                      |      |     |            |                |
| status   | enum('production','requested','approved') | YES  |     | production |                |
+----------+-------------------------------------------+------+-----+------------+----------------+

CREATE TABLE soundfile (
 id serial,
 company integer NOT NULL,
 name varchar(50) NOT NULL,
 description varchar(255) NOT NULL default '',
 language varchar(3) NOT NULL default 'nl',
 FOREIGN KEY (company) REFERENCES company (id),
 PRIMARY KEY  (id)
);

*/

  echo "Please note: this program does NOT run on astiums, and in fact ONLY runs from the compile server\n";
  $cms = new DB_CMS;

  echo "Issuing query...\n";
  $q = "select * from astium_sound where us = 'yes' or nl = 'yes'";
  $cms->query($q);
  printf("..returned %d rows\n", $cms->num_rows());
  if ($cms->num_rows() == 0) {
    echo "nothing found\n";
    exit(1);
  }

  echo "Opening outputfile...\n";
  $fp = fopen("soundfile.sql", "w");
  if (!$fp) {
    echo "could not create soundfile.sql\n";
    exit(1);
  }
  fwrite($fp, "-- generated by gen_pgsound\n");
  fwrite($fp, "delete from soundfile where company = 2;\n\n");
  $q = "select * from astium_sound where (us = 'yes' or nl = 'yes') and (source = 'asterisk' or source = 'neonova')";
  $cms->query($q);
  while ($cms->next_record()) {
    $name = $cms->f('name');
    $us_text = $cms->f('us_text');
    $nl_text = $cms->f('nl_text');

    $out  = sprintf("insert into soundfile(company, name, description, language) \n");
    $out .= sprintf("  values('2', '%s', '%s', 'us');\n", $name, mysql_escape_string($us_text));
    $out .= sprintf("insert into soundfile(company, name, description, language) \n");
    $out .= sprintf("  values('2', '%s', '%s', 'nl');\n", $name, mysql_escape_string($nl_text));
    fwrite($fp, $out);
  }
  fclose($fp);
  echo "Done..\n";
?>

