Untitled

set @refundsPaymentsOrdersEntityId = 96;
set @refundsEntityId = 97;
set @returnReceiptEntityTypeId = 231;
set @returnAdvanceEntityTypeId = 232;
set @addedStatusId = 1;
set @newStatusId = 219;
set @canceledStatusId = 220;
set @toAccountsDepartmentStatusId = 221;
set @sendingBankStatusId = 222;
set @sendingBankErrorStatusId = 223;
set @sendingBankDoneStatusId = 224;
set @returnedByBankStatusId = 225;

INSERT IGNORE INTO `entities_statuses` (`entities_id`, `statuses_id`) VALUES
    (@refundsPaymentsOrdersEntityId, @addedStatusId),
    (@refundsPaymentsOrdersEntityId, @newStatusId),
    (@refundsPaymentsOrdersEntityId, @canceledStatusId),
    (@refundsPaymentsOrdersEntityId, @toAccountsDepartmentStatusId),
    (@refundsPaymentsOrdersEntityId, @sendingBankStatusId),
    (@refundsPaymentsOrdersEntityId, @sendingBankErrorStatusId),
    (@refundsPaymentsOrdersEntityId, @sendingBankDoneStatusId),
    (@refundsPaymentsOrdersEntityId, @returnedByBankStatusId)
;

INSERT IGNORE INTO `status_maps` (`status_maps_name`, `entities_id`, `entities_types_id`) VALUES ('Платежное поручение на возврат прихода', @refundsPaymentsOrdersEntityId, @returnReceiptEntityTypeId);
set @statusMapReturnReceipt = last_insert_id();

INSERT IGNORE INTO `status_maps` (`status_maps_name`, `entities_id`, `entities_types_id`) VALUES ('Платежное поручение на возврат аванса', @refundsPaymentsOrdersEntityId, @returnAdvanceEntityTypeId);
set @statusMapReturnAdvance = last_insert_id();

INSERT IGNORE INTO `status_maps_links` (`status_maps_id`, `status_from_id`, `status_to_id`) VALUES
    (@statusMapReturnReceipt, @addedStatusId, @newStatusId),
    (@statusMapReturnReceipt, @newStatusId, @toAccountsDepartmentStatusId),
    (@statusMapReturnReceipt, @newStatusId, @canceledStatusId),
    (@statusMapReturnReceipt, @canceledStatusId, @newStatusId),
    (@statusMapReturnReceipt, @toAccountsDepartmentStatusId, @canceledStatusId),
    (@statusMapReturnReceipt, @toAccountsDepartmentStatusId, @sendingBankStatusId),
    (@statusMapReturnReceipt, @sendingBankStatusId, @sendingBankErrorStatusId),
    (@statusMapReturnReceipt, @sendingBankStatusId, @sendingBankDoneStatusId),
    (@statusMapReturnReceipt, @sendingBankDoneStatusId, @returnedByBankStatusId),
    (@statusMapReturnAdvance, @addedStatusId, @newStatusId),
    (@statusMapReturnAdvance, @newStatusId, @toAccountsDepartmentStatusId),
    (@statusMapReturnAdvance, @newStatusId, @canceledStatusId),
    (@statusMapReturnAdvance, @toAccountsDepartmentStatusId, @canceledStatusId),
    (@statusMapReturnAdvance, @toAccountsDepartmentStatusId, @sendingBankStatusId),
    (@statusMapReturnAdvance, @sendingBankStatusId, @sendingBankErrorStatusId),
    (@statusMapReturnAdvance, @sendingBankStatusId, @sendingBankDoneStatusId),
    (@statusMapReturnAdvance, @sendingBankDoneStatusId, @returnedByBankStatusId)
;

CREATE TABLE `refunds_payments_orders` (
    `refunds_payments_orders_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID платежного поручения',
    `parent_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Связь с родительским платежным поручением',
    `entities_types_id` INT(10) UNSIGNED NOT NULL DEFAULT '230' COMMENT 'Тип платежного поручения',
    `payment_order_number_1c` VARCHAR(255) NOT NULL DEFAULT '',
    `payment_order_date_1c` TIMESTAMP NULL DEFAULT NULL,
    `customers_id_added` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Сотрудник, акцептовавший возврат или добавивший поручение',
    `customers_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Пользователь, которому возвращаем средства',
    `refunds_payments_orders_data_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Связь со справочником платежной информации пользователя',
    `refunds_payments_orders_status` INT(10) UNSIGNED NOT NULL DEFAULT 1,
    `refunds_payments_orders_comment` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Комментарий о назначении платежа',
    `refunds_payments_orders_sum` DECIMAL(12,5) NOT NULL DEFAULT '0.00000' COMMENT 'Сумма платежного поручения',
    `date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `date_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`refunds_payments_orders_id`),
    INDEX `parent_id` (`parent_id`),
    INDEX `entities_types_id` (`entities_types_id`),
    INDEX `payment_order_number_1c` (`payment_order_number_1c`),
    INDEX `customers_id_added` (`customers_id_added`),
    INDEX `customers_id` (`customers_id`),
    INDEX `refunds_payments_orders_data_id` (`refunds_payments_orders_data_id`),
    INDEX `refunds_payments_orders_status` (`refunds_payments_orders_status`),
    INDEX `date_added` (`date_added`),
    INDEX `date_updated` (`date_updated`)
)
    COMMENT='Платежные поручения'
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
;


CREATE TABLE `refunds_payments_orders_status_history` (
    `refunds_payments_orders_status_history_id` INT(11) NOT NULL AUTO_INCREMENT,
    `object_id` INT(11) NOT NULL DEFAULT '0',
    `customers_id` INT(11) NOT NULL DEFAULT '0',
    `status_id` INT(11) NOT NULL DEFAULT '0',
    `date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `parent_id` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`refunds_payments_orders_status_history_id`),
    INDEX `object_id` (`object_id`),
    INDEX `status_id` (`status_id`),
    INDEX `date_added` (`date_added`)
)
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
;


CREATE TABLE `refunds_payments_orders_comment_history` (
    `refunds_payments_orders_comment_history_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `refunds_payments_orders_status_history_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `object_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `customers_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `status_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `sub_status_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `date_added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `comment` TEXT NULL,
    `is_important` TINYINT(4) NOT NULL DEFAULT '2' COMMENT '1 - true, 2 - false',
    `flag` TINYINT(4) NOT NULL DEFAULT '1',
    PRIMARY KEY (`refunds_payments_orders_comment_history_id`),
    INDEX `refunds_payments_orders_status_history_id` (`refunds_payments_orders_status_history_id`),
    INDEX `object_id` (`object_id`),
    INDEX `status_id` (`status_id`),
    INDEX `sub_status_id` (`sub_status_id`),
    INDEX `date_added` (`date_added`)
)
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
;

CREATE TABLE `refunds_payments_orders_data` (
    `refunds_payments_orders_data_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID набора платежной информации',
    `customers_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Id пользователя, которому возвращаем средства',
    `refunds_payments_orders_data_customer_lastname` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Фамилия получателя платежа',
    `refunds_payments_orders_data_customer_firstname` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Имя получателя платежа',
    `refunds_payments_orders_data_customer_surname` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Отчество получателя платежа',
    `refunds_payments_orders_data_bik` VARCHAR(9) NOT NULL DEFAULT '' COMMENT 'БИК',
    `refunds_payments_orders_data_bank_name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Наименование банка',
    `refunds_payments_orders_data_bank_city` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Населенный пункт (город)',
    `refunds_payments_orders_data_bank_cor_account` VARCHAR(64) NOT NULL DEFAULT '' COMMENT 'Корреспондентский счет',
    `refunds_payments_orders_data_customer_account` VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'Номер счета пользователя',
    `refunds_payments_orders_data_customer_card_number` VARCHAR(25) NOT NULL DEFAULT '' COMMENT 'Номер карты пользователя',
    `is_active` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Признак, что по данной информации платеж успешно проходил',
    PRIMARY KEY (`refunds_payments_orders_data_id`),
    INDEX `customers_id` (`customers_id`)
)
    COMMENT='Справочник платежной информации пользователей'
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
;

CREATE TABLE `refunds_payments_orders_to_objects` (
    `refunds_payments_orders_to_objects_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `refunds_payments_orders_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
    `object_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
    `entities_id` INT(11) UNSIGNED NOT NULL DEFAULT '97',
    `customers_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `date_added` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`refunds_payments_orders_to_objects_id`),
    INDEX `refunds_payments_orders_id` (`refunds_payments_orders_id`),
    INDEX `object_id_entities_id` (`object_id`, `entities_id`),
    INDEX `refunds_payments_orders_id_entities_id` (`refunds_payments_orders_id`, `entities_id`),
    INDEX `customers_id` (`customers_id`)
)
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
;