watercanal mysql: period.sh

#!/bin/bash
. $(dirname "$0")"/common.sh"

# +---------------------+------------------+------+-----+---------+----------------+
# | Field               | Type             | Null | Key | Default | Extra          |
# +---------------------+------------------+------+-----+---------+----------------+
# | ID                  | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
# | UF_BEGIN_DATE       | date             | YES  |     | NULL    |                |
# | UF_DATE_END         | date             | YES  |     | NULL    |                |
# | UF_PHISICAL_STATUS  | int(18)          | YES  |     | NULL    |                |
# | UF_JURIDICAL_STATUS | int(18)          | YES  |     | NULL    |                |
# | UF_JOOMLA_ID        | int(11)          | YES  | MUL | NULL    |                |
# | UF_UNIQUE           | text             | YES  |     | NULL    |                |
# +---------------------+------------------+------+-----+---------+----------------+

# +-----------------+----------+------+-----+---------------------+----------------+
# | Field           | Type     | Null | Key | Default             | Extra          |
# +-----------------+----------+------+-----+---------------------+----------------+
# | ID              | int(11)  | NO   | PRI | NULL                | auto_increment |
# | DateBegin       | datetime | NO   | MUL | NULL                |                |
# | DateEnd         | datetime | NO   |     | NULL                |                |
# | PhisicalStatus  | int(11)  | NO   |     | NULL                |                |
# | JuridicalStatus | int(11)  | NO   |     | NULL                |                |
# | DateCreated     | datetime | NO   |     | 2013-01-23 00:00:00 |                |
# | DateUpdated     | datetime | YES  |     | NULL                |                |
# +-----------------+----------+------+-----+---------------------+----------------+

execute_sql 'period' <<EOF
UPDATE period
  INNER JOIN jm_period ON jm_period.ID = period.UF_JOOMLA_ID
SET
  period.UF_BEGIN_DATE = jm_period.DateBegin,
  period.UF_DATE_END = jm_period.DateEnd,
  period.UF_PHISICAL_STATUS = jm_period.PhisicalStatus,
  period.UF_JURIDICAL_STATUS = jm_period.JuridicalStatus,
  period.UF_JOOMLA_ID = jm_period.ID
;

INSERT INTO period (
  UF_BEGIN_DATE,
  UF_DATE_END,
  UF_PHISICAL_STATUS,
  UF_JURIDICAL_STATUS,
  UF_JOOMLA_ID
)
SELECT
  DateBegin,
  DateEnd,
  PhisicalStatus,
  JuridicalStatus,
  ID
FROM jm_period
WHERE NOT EXISTS (
  SELECT NULL FROM period WHERE period.UF_JOOMLA_ID = jm_period.ID
);
EOF