<commit_before><?php

class ImportShell extends AppShell {

    public $uses = array('License');
    public $dataPath = '/home/kiang/public_html/data.fda.gov.tw-list';
    public $mysqli = false;
    public $key2id = array();
    public $key2code = array();

    public function main() {
        //$this->dumpDbKeys();
        //$this->importDrug();
        //$this->importPrice();
        //$this->importImage();
        $this->importBox();
        //$this->importIngredients();
        //$this->importATC();
    }

    public function renameDrugImages() {
        $fh = fopen(__DIR__ . '/data/oldDrugId.csv', 'r');
        $map = array();
        while ($line = fgetcsv($fh, 512)) {
            $map[$line[0]] = $line[1];
        }
        $list = $this->License->find('list', array(
            'fields' => array('license_id', 'id'),
        ));
        foreach (glob(WWW_ROOT . 'img/drugs/*/*.jpg') AS $img) {
            $p = pathinfo($img);
            $uuid = $list[$map[$p['filename']]];
            $targetFile = WWW_ROOT . 'img/drugs/' . substr($uuid, 0, 8);
            if (!file_exists($targetFile)) {
                mkdir($targetFile, 0777, true);
            }
            exec("git mv {$img} {$targetFile}/{$uuid}.jpg");
        }
    }

    public function rKeys($arr = array(), $prefix = '') {
        foreach ($arr AS $category) {
            $newPrefix = $prefix . $category['Category']['name'];
            $this->key2id[$newPrefix] = $category['Category']['id'];
            $this->key2code[$newPrefix] = $category['Category']['code'];
            if (!empty($category['children'])) {
                $this->rKeys($category['children'], $newPrefix);
            }
        }
    }

    public function importATC() {
        $db = ConnectionManager::getDataSource('default');
        $this->mysqli = new mysqli($db->config['host'], $db->config['login'], $db->config['password'], $db->config['database']);
        $this->dbQuery('SET NAMES utf8mb4;');
        $this->rKeys($this->License->Category->find('threaded', array(
                    'fields' => array('id', 'parent_id', 'name', 'code'),
        )));
        $dbKeys = $valueStack = array();
        if (file_exists(__DIR__ . '/data/dbKeys.csv')) {
            $dbKeysFh = fopen(__DIR__ . '/data/dbKeys.csv', 'r');
            while ($line = fgetcsv($dbKeysFh, 1024)) {
                $dbKeys[$line[0]] = $line[1];
            }
            fclose($dbKeysFh);
        }
        $fields = array('許可證字號', '主或次項', '代碼', '英文分類名稱', '中文分類名稱', '-');
        /*
         * Array
          (
          [0] => 許可證字號
          [1] => 主或次項
          [2] => 代碼
          [3] => 英文分類名稱
          [4] => 中文分類名稱
          [5] => -
          )
         */
        $fh = fopen($this->dataPath . '/dataset/41.csv', 'r');
        $sn = 1;
        while ($line = fgetcsv($fh, 2048, "\t")) {
            foreach ($line AS $k => $v) {
                $line[$k] = trim($v);
            }
            $tree = explode(' / ', $line[3]);
            $treeCount = count($tree);
            $currentCount = 0;
            $currentKey = '';
            foreach ($tree AS $item) {
                ++$currentCount;
                $parentKey = $currentKey;
                if (empty($parentKey)) {
                    $parentId = '0';
                } else {
                    $parentId = $this->key2id[$parentKey];
                }
                $currentKey .= $item;
                if (!isset($this->key2id[$currentKey])) {
                    $this->License->Category->create();
                    $code = '';
                    $nameChinese = '';
                    if ($currentCount === $treeCount) {
                        $code = strtoupper($line[2]);
                        $nameChinese = $line[4];
                    }
                    $this->License->Category->save(array('Category' => array(
                            'parent_id' => $parentId,
                            'code' => $code,
                            'name' => $item,
                            'name_chinese' => $nameChinese,
                    )));
                    $this->key2id[$currentKey] = $this->License->Category->getInsertID();
                } elseif ($currentCount === $treeCount && empty($this->key2code[$currentKey])) {
                    $code = strtoupper($line[2]);
                    $this->dbQuery('UPDATE `categories` SET code = \'' . $code . '\' WHERE id = \'' . $this->key2id[$currentKey] . '\';');
                }
            }
            if (isset($dbKeys[$line[0]])) {
                $currentId = String::uuid();
                $valueStack[] = implode(',', array(
                    "('{$currentId}'", //id
                    $this->key2id[implode('', $tree)], //category_id
                    "'{$dbKeys[$line[0]]}'", //drug_id
                    "'{$line[1]}')", //type
                ));
                ++$sn;
                if ($sn > 50) {
                    $sn = 1;
                    $this->dbQuery('INSERT INTO `categories_drugs` VALUES ' . implode(',', $valueStack) . ';');
                    $valueStack = array();
                }
            }
        }
        if (!empty($valueStack)) {
            $this->dbQuery('INSERT INTO `categories_drugs` VALUES ' . implode(',', $valueStack) . ';');
        }
    }

    public function importIngredients() {
        $db = ConnectionManager::getDataSource('default');
        $this->mysqli = new mysqli($db->config['host'], $db->config['login'], $db->config['password'], $db->config['database']);
        $this->dbQuery('SET NAMES utf8mb4;');
        $fields = array('許可證字號', '處方標示', '成分名稱', '含量描述', '含量', '含量單位', '-');
        $dbKeys = $valueStack = array();

        if (file_exists(__DIR__ . '/data/dbKeys.csv')) {
            $dbKeysFh = fopen(__DIR__ . '/data/dbKeys.csv', 'r');
            while ($line = fgetcsv($dbKeysFh, 1024)) {
                $dbKeys[$line[0]] = $line[1];
            }
            fclose($dbKeysFh);
        }
        $fh = fopen($this->dataPath . '/dataset/43.csv', 'r');
        /*
         * Array
          (
          [0] => 許可證字號
          [1] => 處方標示
          [2] => 成分名稱
          [3] => 含量描述
          [4] => 含量
          [5] => 含量單位
          )
         */
        $sn = 1;
        while ($line = fgetcsv($fh, 2048, "\t")) {
            if (substr($line[4], 0, 1) === '-') {
                $line[4] = substr($line[4], 1);
            }
            if (!isset($dbKeys[$line[0]])) {
                continue;
            }
            for ($i = 1; $i <= 5; $i++) {
                $line[$i] = $this->mysqli->real_escape_string(trim($line[$i]));
            }
            $currentId = String::uuid();
            $valueStack[] = implode(',', array(
                "('{$currentId}'", //id
                "'{$dbKeys[$line[0]]}'", //drug_id
                "'{$line[1]}'", //remark
                "'{$line[2]}'", //name
                "'{$line[3]}'", //dosage_text
                "'{$line[4]}'", //dosage
                "'{$line[5]}')", //unit
            ));
            ++$sn;
            if ($sn > 50) {
                $sn = 1;
                $this->dbQuery('INSERT INTO `ingredients` VALUES ' . implode(',', $valueStack) . ';');<commit_msg>import ingredients<commit_after><?php

class ImportShell extends AppShell {

    public $uses = array('License');
    public $dataPath = '/home/kiang/public_html/data.fda.gov.tw-list';
    public $mysqli = false;
    public $key2id = array();
    public $key2code = array();

    public function main() {
        //$this->dumpDbKeys();
        //$this->importDrug();
        //$this->importPrice();
        //$this->importImage();
        //$this->importBox();
        $this->importIngredients();
        //$this->importATC();
    }

    public function renameDrugImages() {
        $fh = fopen(__DIR__ . '/data/oldDrugId.csv', 'r');
        $map = array();
        while ($line = fgetcsv($fh, 512)) {
            $map[$line[0]] = $line[1];
        }
        $list = $this->License->find('list', array(
            'fields' => array('license_id', 'id'),
        ));
        foreach (glob(WWW_ROOT . 'img/drugs/*/*.jpg') AS $img) {
            $p = pathinfo($img);
            $uuid = $list[$map[$p['filename']]];
            $targetFile = WWW_ROOT . 'img/drugs/' . substr($uuid, 0, 8);
            if (!file_exists($targetFile)) {
                mkdir($targetFile, 0777, true);
            }
            exec("git mv {$img} {$targetFile}/{$uuid}.jpg");
        }
    }

    public function rKeys($arr = array(), $prefix = '') {
        foreach ($arr AS $category) {
            $newPrefix = $prefix . $category['Category']['name'];
            $this->key2id[$newPrefix] = $category['Category']['id'];
            $this->key2code[$newPrefix] = $category['Category']['code'];
            if (!empty($category['children'])) {
                $this->rKeys($category['children'], $newPrefix);
            }
        }
    }

    public function importATC() {
        $db = ConnectionManager::getDataSource('default');
        $this->mysqli = new mysqli($db->config['host'], $db->config['login'], $db->config['password'], $db->config['database']);
        $this->dbQuery('SET NAMES utf8mb4;');
        $this->rKeys($this->License->Category->find('threaded', array(
                    'fields' => array('id', 'parent_id', 'name', 'code'),
        )));
        $dbKeys = $valueStack = array();
        if (file_exists(__DIR__ . '/data/dbKeys.csv')) {
            $dbKeysFh = fopen(__DIR__ . '/data/dbKeys.csv', 'r');
            while ($line = fgetcsv($dbKeysFh, 1024)) {
                $dbKeys[$line[0]] = $line[1];
            }
            fclose($dbKeysFh);
        }
        $fields = array('許可證字號', '主或次項', '代碼', '英文分類名稱', '中文分類名稱', '-');
        /*
         * Array
          (
          [0] => 許可證字號
          [1] => 主或次項
          [2] => 代碼
          [3] => 英文分類名稱
          [4] => 中文分類名稱
          [5] => -
          )
         */
        $fh = fopen($this->dataPath . '/dataset/41.csv', 'r');
        $sn = 1;
        while ($line = fgetcsv($fh, 2048, "\t")) {
            foreach ($line AS $k => $v) {
                $line[$k] = trim($v);
            }
            $tree = explode(' / ', $line[3]);
            $treeCount = count($tree);
            $currentCount = 0;
            $currentKey = '';
            foreach ($tree AS $item) {
                ++$currentCount;
                $parentKey = $currentKey;
                if (empty($parentKey)) {
                    $parentId = '0';
                } else {
                    $parentId = $this->key2id[$parentKey];
                }
                $currentKey .= $item;
                if (!isset($this->key2id[$currentKey])) {
                    $this->License->Category->create();
                    $code = '';
                    $nameChinese = '';
                    if ($currentCount === $treeCount) {
                        $code = strtoupper($line[2]);
                        $nameChinese = $line[4];
                    }
                    $this->License->Category->save(array('Category' => array(
                            'parent_id' => $parentId,
                            'code' => $code,
                            'name' => $item,
                            'name_chinese' => $nameChinese,
                    )));
                    $this->key2id[$currentKey] = $this->License->Category->getInsertID();
                } elseif ($currentCount === $treeCount && empty($this->key2code[$currentKey])) {
                    $code = strtoupper($line[2]);
                    $this->dbQuery('UPDATE `categories` SET code = \'' . $code . '\' WHERE id = \'' . $this->key2id[$currentKey] . '\';');
                }
            }
            if (isset($dbKeys[$line[0]])) {
                $currentId = String::uuid();
                $valueStack[] = implode(',', array(
                    "('{$currentId}'", //id
                    $this->key2id[implode('', $tree)], //category_id
                    "'{$dbKeys[$line[0]]}'", //drug_id
                    "'{$line[1]}')", //type
                ));
                ++$sn;
                if ($sn > 50) {
                    $sn = 1;
                    $this->dbQuery('INSERT INTO `categories_drugs` VALUES ' . implode(',', $valueStack) . ';');
                    $valueStack = array();
                }
            }
        }
        if (!empty($valueStack)) {
            $this->dbQuery('INSERT INTO `categories_drugs` VALUES ' . implode(',', $valueStack) . ';');
        }
    }

    public function importIngredients() {
        $db = ConnectionManager::getDataSource('default');
        $this->mysqli = new mysqli($db->config['host'], $db->config['login'], $db->config['password'], $db->config['database']);
        $this->dbQuery('SET NAMES utf8mb4;');
        $fields = array('許可證字號', '處方標示', '成分名稱', '含量描述', '含量', '含量單位', '-');
        $dbKeys = $valueStack = array();

        if (file_exists(__DIR__ . '/data/dbIds.csv')) {
            $dbKeysFh = fopen(__DIR__ . '/data/dbIds.csv', 'r');
            while ($line = fgetcsv($dbKeysFh, 1024)) {
                $dbKeys[$line[0]] = $line[1];
            }
            fclose($dbKeysFh);
        }
        $fh = fopen($this->dataPath . '/dataset/43.csv', 'r');
        /*
         * Array
          (
          [0] => 許可證字號
          [1] => 處方標示
          [2] => 成分名稱
          [3] => 含量描述
          [4] => 含量
          [5] => 含量單位
          )
         */
        $sn = 1;
        while ($line = fgetcsv($fh, 2048, "\t")) {
            if (substr($line[4], 0, 1) === '-') {
                $line[4] = substr($line[4], 1);
            }
            if (!isset($dbKeys[$line[0]])) {
                continue;
            }
            for ($i = 1; $i <= 5; $i++) {
                $line[$i] = $this->mysqli->real_escape_string(trim($line[$i]));
            }
            $currentId = String::uuid();
            $valueStack[] = implode(',', array(
                "('{$currentId}'", //id
                "'{$dbKeys[$line[0]]}'", //license_id
                "'{$line[1]}'", //remark
                "'{$line[2]}'", //name
                "'{$line[3]}'", //dosage_text
                "'{$line[4]}'", //dosage
                "'{$line[5]}')", //unit
            ));
            ++$sn;
            if ($sn > 50) {
                $sn = 1;
                $this->dbQuery('INSERT INTO `ingredients` VALUES ' . implode(',', $valueStack) . ';');
{'fraction_non_alphanumeric': 0.1971811524112201, 'fraction_numerical': 0.012850628713555341, 'mean_word_length': 1.4558873430607397, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 149, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '338912', 'n_tokens_mistral': 4639, 'n_tokens_neox': 4459, 'n_words': 761}