katao/config/schema.yml

propel:
# catalyz
  node_home:
    id:
    node_id:                    { type: integer, foreignTable: node, foreignReference: id, onDelete: cascade }
    edito_title:                { type: varchar(255) }
    edito_content:              { type: longvarchar }
  node_page:
    id:
    node_id:                    { type: integer, foreignTable: node, foreignReference: id, onDelete: cascade }
    content:                    { type: longvarchar }
  node_news:
    id:
    node_id:                    { type: integer, foreignTable: node, foreignReference: id, onDelete: cascade }
    published_at:               { type: timestamp }
    introduction:               { type: longvarchar }
    content:                    { type: longvarchar }
    picture:                    { type: varchar(255) }
  node_network:
    id:
    node_id:                    { type: integer, foreignTable: node, foreignReference: id, onDelete: cascade }
    introduction:               { type: longvarchar }

# admin
  katao_config:
    id:
    katao_member_id:            { type: integer, foreignTable: katao_member, foreignReference: id, onDelete: cascade }
    name:                       { type: varchar, size: 255,  required: true }
    address:                    { type: longvarchar }
    phone:                      { type: varchar, size: 255,  required: true }
    email:                      { type: varchar, size: 255,  required: true }
    siret_number:               { type: char, size: 14,  required: true }
    tva_number:                 { type: char, size: 13,  required: true }
    rcs_number:                 { type: varchar, size: 255,  required: true }
    capital:                    { type: integer, required: true }
    initial_transactions_count:       { type: integer, default: 0 }
    initial_transactions_sum:         { type: float, default: 0 }
    default_product_picture:          { type: varchar(255) }
    default_product_margin:           { type: integer, default: 15 }
    default_product_sol_percent:      { type: integer, default: 5 }
    mailing_list_email:               { type: varchar, size: 255 }
    cart_validation_days_number:      { type: integer, required: true, default: 1 }
    accounting_code_prefix_purchase:  { type: varchar(12), required: true }
    accounting_code_prefix_sell:      { type: varchar(12), required: true }
    accounting_code_bank:             { type: varchar(12), required: true }
    accounting_code_bank_sol:         { type: varchar(12), required: true }
    accounting_code_credit:           { type: varchar(12), required: true }
    accounting_code_error_adjustment_positive: { type: varchar(12), required: true }
    accounting_code_error_adjustment_negative: { type: varchar(12), required: true }
    accounting_code_sol_discount:     { type: varchar(12), required: true }
    accounting_code_fees_shipping:    { type: varchar(12), required: true }
    accounting_code_fees_billing:     { type: varchar(12), required: true }
    accounting_code_discount:         { type: varchar(12), required: true }
    member_fee:                       { type: float, default: 0 }
    additional_currency_name:         { type: varchar(255) }
    debug_email:                      { type: varchar(255) }
    bank_name:                        { type: varchar(255) }
    bank_account:                     { type: varchar(255) }
  katao_node:
    id:
    city:           { type: varchar, size: 255,  required: true }
    responsible:    { type: integer, foreignTable: katao_user, foreignReference: id, required: false, onDelete: setnull }
    begin_at:       { type: timestamp, required: true }
    is_default:     { type: integer, size: 1, default: 0 }
    created_at:
    updated_at:
  katao_period:
    id:
    name:           { type: varchar, size: 255,  required: true }
    begin_at:       { type: timestamp, required: true }
    finish_at:      { type: timestamp, required: true }
    order_ended_at: { type: timestamp, required: true }
    status:         { type: integer, required: true, default: 1 } # 1 : provisoire, 2 : active, 3 : terminée
    exported:       { type: integer, size: 1, default: 0 }
    created_at:
    updated_at:
  katao_product_category:
    id:
    name:                   { type: varchar, size: 255, required: true }
    url_identifier:         { type: varchar, size: 255, required: true }
    accounting_code_suffix: { type: varchar(12), required: true }
    sort_order:             { type: integer, default: 0 }
    created_at:
    updated_at:
  katao_product_family:
    id:
    katao_product_category_id: { type: integer, foreignTable: katao_product_category, foreignReference: id, required: true, onDelete: cascade }
    name:           { type: varchar, size: 255, required: true }
    url_identifier: { type: varchar, size: 255, required: true }
    created_at:
    updated_at:
  katao_product:
    id:
    reference:                { type: varchar(255) }
    name:                     { type: varchar, size: 255,  required: true }
    url_identifier:           { type: varchar, size: 255, required: true }
    description:              { type: longvarchar }
    katao_product_family_id:  { type: integer, foreignTable: katao_product_family, foreignReference: id, required: true, onDelete: cascade }
    katao_supplier_id:        { type: integer, foreignTable: katao_supplier, foreignReference: id, required: true, onDelete: cascade }
    max_sol_amount:           { type: integer }
    unit_price_euro:          { type: float, required: true }
    margin:                   { type: float }
    tva_rate:                 { type: float, default: 0 }
    min_order_number:         { type: integer, required: true, default: 1 }
    picture:                  { type: varchar(255) }
    delivery_delay:           { type: integer }
    is_archived:              { type: integer, size: 1, default: 0 }
    accounting_code_purchase: { type: varchar(12), required: true }
    accounting_code_sell:     { type: varchar(12), required: true }
    created_at:
    updated_at:
  katao_user:
    id:
    katao_member_id:   { type: integer, foreignTable: katao_member, foreignReference: id, onDelete: cascade }
    katao_supplier_id: { type: integer, foreignTable: katao_supplier, foreignReference: id, onDelete: cascade }
    status:         { type: integer, required: true, default: 2 } # 1 : contact, 2 : actif, 3 : inactif
    email:          { type: varchar, size: 255 }
    address1:       { type: varchar, size: 255,  required: true }
    address2:       { type: varchar, size: 255 }
    zip:            { type: varchar, size: 255 }
    city:           { type: varchar, size: 255,  required: true }
    phone:          { type: varchar, size: 255 }
    fax:            { type: varchar, size: 255 }
    gmap_lng:       { type: double }
    gmap_lat:       { type: double }
    login:          { type: varchar, size: 255,  required: true }
    password:       { type: varchar, size: 255,  required: true }
    is_seo:         { type: integer, size: 1, default: 0 }
    is_admin:       { type: integer, size: 1, default: 0 }
    created_at:
    updated_at:
  katao_member:
    id:
    katao_index:         { type: integer, required: true }
    katao_node_id:       { type: integer, foreignTable: katao_node, foreignReference: id, required: true, onDelete: cascade }
    next_katao_node_id:  { type: integer, foreignTable: katao_node, foreignReference: id, onDelete: setnull }
    katao_member_id:     { type: integer, foreignTable: katao_member, foreignReference: id, onDelete: setnull }
    first_name:          { type: varchar, size: 255,  required: true }
    last_name:           { type: varchar, size: 255,  required: true }
    accounting_code:     { type: char, size: 6, required: true }
    accounting_code_sol: { type: char, size: 6, required: true }
    initial_amount_euro: { type: float, default: 0 }
    initial_amount_sol:  { type: integer, default: 0 }
    is_referer:          { type: integer, size: 1, default: 0 }
    is_delegate:         { type: integer, size: 1, default: 0 }
    is_anonymous:        { type: integer, size: 1, default: 0 }
    is_member:           { type: integer, size: 1, default: 1 }
    card_number_sol:     { type: char, size: 8 }
    user_situation:      {  type: varchar, size: 255 }
    user_birthday:       { type: date }
    parrain_name:        { type: varchar, size: 255 }
#    member_group:        { type: integer, foreignTable: katao_member_group, foreignReference: id }
    created_at:
    updated_at:

  katao_supplier:
    id:
    katao_index:         { type: integer, required: true }
    member_id:           { type: integer, foreignTable: katao_member, foreignReference: id, onDelete: cascade }
    referer_id:          { type: integer, foreignTable: katao_member, foreignReference: id, onDelete: cascade }
    name:                { type: varchar, size: 255,  required: true }
    url_identifier:      { type: varchar, size: 255, required: true }
    presentation:        { type: longvarchar }
    email:               { type: varchar, size: 255 }
    address1:            { type: varchar, size: 255,  required: true }
    address2:            { type: varchar, size: 255 }
    zip:                 { type: varchar, size: 255 }
    city:                { type: varchar, size: 255,  required: true }
    phone:               { type: varchar, size: 255 }
    fax:                 { type: varchar, size: 255 }
    website:             { type: varchar, size: 255 }
    gmap_lng:            { type: double }
    gmap_lat:            { type: double }
    accounting_code:     { type: char, size: 6, required: true }
    accounting_code_sol: { type: char, size: 6, required: true }
    siret_number:        { type: char, size: 14,  required: true, index: unique }
    is_global:           { type: integer, size: 1, default: 0 }
    order_min_amount:    { type: float, default: 0 }
    created_at:
    updated_at:
  katao_supplier_node:
    katao_supplier_id:   { type: integer, primaryKey: true, foreignReference: id, foreignTable: katao_supplier, onDelete: cascade, onUpdate: cascade, required: true }
    katao_node_id:       { type: integer, primaryKey: true, foreignReference: id, foreignTable: katao_node, onDelete: cascade, onUpdate: cascade, required: true }
  katao_node_product:
    id:
    katao_node_id:        { type: integer, foreignTable: katao_node, foreignReference: id, required: true, onDelete: cascade }
    katao_period_id:      { type: integer, foreignTable: katao_period, foreignReference: id, required: true, onDelete: cascade }
    katao_product_id:     { type: integer, foreignTable: katao_product, foreignReference: id, required: true, onDelete: cascade }
  katao_node_supplier:
    id:
    katao_node_id:        { type: integer, foreignTable: katao_node, foreignReference: id, required: true, onDelete: cascade }
    katao_period_id:      { type: integer, foreignTable: katao_period, foreignReference: id, required: true, onDelete: cascade }
    katao_supplier_id:    { type: integer, foreignTable: katao_supplier, foreignReference: id, required: true, onDelete: cascade }
    include_all_products: { type: integer, size: 1, default: 0, required: true }
  katao_period_product:
    id:
    katao_period_id:      { type: integer, foreignTable: katao_period, foreignReference: id, required: true, onDelete: cascade }
    katao_product_id:     { type: integer, foreignTable: katao_product, foreignReference: id, required: true, onDelete: cascade }
  katao_period_supplier:
    id:
    katao_period_id:      { type: integer, foreignTable: katao_period, foreignReference: id, required: true, onDelete: cascade }
    katao_supplier_id:    { type: integer, foreignTable: katao_supplier, foreignReference: id, required: true, onDelete: cascade }
    include_all_products: { type: integer, size: 1, default: 0, required: true }
  katao_cart:
    id:
    katao_user_id:        { type: integer, foreignTable: katao_user, foreignReference: id, required: true, onDelete: cascade }
    katao_period_id:      { type: integer, foreignTable: katao_period, foreignReference: id, required: true, onDelete: cascade }
    katao_node_id:        { type: integer, foreignTable: katao_node, foreignReference: id, required: true, onDelete: cascade }
    status:               { type: integer, required: true, default: 1 } # 1 : en cours, 2 : validé, 3 : archivé
    created_at:
    updated_at:
  katao_cart_product:
    id:
    katao_cart_id:          { type: integer, foreignTable: katao_cart, foreignReference: id, required: true, onDelete: cascade }
    katao_product_id:       { type: integer, foreignTable: katao_product, foreignReference: id, required: true, onDelete: cascade }
    product_reference:      { type: varchar(255) }
    product_name:           { type: varchar, size: 255,  required: true }
    product_price_euro:     { type: float, required: true }
    product_margin:         { type: float }
    product_tva_rate:       { type: float, default: 0 }
    quantity:               { type: integer, required: true, default: 1 }
    quantity_adjusted:      { type: integer, default: 0 }
    has_been_adjusted:      { type: integer, size: 1, default: 0 }
    quantity_delivered:     { type: integer, default: 0 }
    katao_order_product_id: { type: integer, foreignTable: katao_order_product, foreignReference: id, onDelete: setnull }
    created_at:
    updated_at:
  katao_order:
    id:
    katao_supplier_id:   { type: integer, foreignTable: katao_supplier, foreignReference: id, required: true, onDelete: cascade }
    katao_period_id:     { type: integer, foreignTable: katao_period, foreignReference: id, required: true, onDelete: cascade }
    katao_node_id:       { type: integer, foreignTable: katao_node, foreignReference: id, required: true, onDelete: cascade }
    status:              { type: integer, required: true, default: 1 } # 1 : générée, 2 : ajustée, 3 : envoyée, 4 : réceptionnée, 5 : distribuée
    is_simulated:        { type: integer, size: 1, default: 0 }
    supplier_name:       { type: varchar, size: 255,  required: true }
    supplier_email:      { type: varchar, size: 255,  required: true }
    supplier_address1:   { type: varchar, size: 255,  required: true }
    supplier_address2:   { type: varchar, size: 255 }
    supplier_zip:        { type: varchar, size: 255 }
    supplier_city:       { type: varchar, size: 255,  required: true }
    katao_name:          { type: varchar, size: 255,  required: true }
    katao_address:       { type: longvarchar }
    katao_siret_number:  { type: char, size: 14,  required: true }
    katao_rcs_number:    { type: varchar, size: 255,  required: true }
    katao_capital:       { type: integer, required: true }
    delivery_address:    { type: longvarchar }
    created_at:
    updated_at:
  katao_order_product:
    id:
    katao_order_id:      { type: integer, foreignTable: katao_order, foreignReference: id, onDelete: cascade }
    katao_product_id:    { type: integer, foreignTable: katao_product, foreignReference: id, onDelete: cascade }
    product_reference:   { type: varchar(255) }
    product_name:        { type: varchar, size: 255,  required: true }
    product_price_euro:  { type: float, required: true }
    product_margin:      { type: float }
    product_tva_rate:    { type: float, default: 0 }
    quantity:            { type: integer, required: true, default: 1 }
    quantity_received:   { type: integer, default: 0 }
    quantity_delivered:  { type: integer, default: 0 }
    has_been_received:   { type: integer, size: 1, default: 0 }
    has_been_delivered:  { type: integer, size: 1, default: 0 }
    created_at:
    updated_at:
  katao_invoice:
    id:
    katao_member_id:     { type: integer, foreignTable: katao_member, foreignReference: id, required: true, onDelete: cascade }
    katao_period_id:     { type: integer, foreignTable: katao_period, foreignReference: id, required: true, onDelete: cascade }
    katao_node_id:       { type: integer, foreignTable: katao_node, foreignReference: id, required: true, onDelete: cascade }
    katao_cart_id:       { type: integer, foreignTable: katao_cart, foreignReference: id, onDelete: cascade }
    status:              { type: integer, required: true, default: 1 } # 1 : générée, 2 : distribuée, 3 : validée, 4 : envoyée
    member_first_name:   { type: varchar, size: 255,  required: true }
    member_last_name:    { type: varchar, size: 255,  required: true }
    member_email:        { type: varchar, size: 255,  required: true }
    member_address1:     { type: varchar, size: 255,  required: true }
    member_address2:     { type: varchar, size: 255 }
    member_zip:          { type: varchar, size: 255 }
    member_city:         { type: varchar, size: 255,  required: true }
    katao_name:          { type: varchar, size: 255,  required: true }
    katao_address:       { type: longvarchar }
    katao_siret_number:  { type: char, size: 14,  required: true }
    katao_rcs_number:    { type: varchar, size: 255,  required: true }
    katao_capital:       { type: integer, required: true }
    sol_amount:          { type: integer, default: 0 }
    created_at:
    updated_at:
  katao_invoice_product:
    id:
    katao_invoice_id:       { type: integer, foreignTable: katao_invoice, foreignReference: id, required: true, onDelete: cascade }
    katao_product_id:       { type: integer, foreignTable: katao_product, foreignReference: id, required: true, onDelete: cascade }
    katao_cart_product_id:  { type: integer, foreignTable: katao_cart_product, foreignReference: id, required: true, onDelete: cascade }
    katao_order_product_id: { type: integer, foreignTable: katao_order_product, foreignReference: id, required: true, onDelete: cascade }
    product_reference:      { type: varchar(255) }
    product_name:           { type: varchar, size: 255,  required: true }
    product_price_euro:     { type: float, required: true }
    product_margin:         { type: float }
    product_tva_rate:       { type: float, default: 0 }
    quantity:               { type: integer, required: true, default: 1 }
    quantity_delivered:     { type: integer, default: 0 }
    sol_amount:             { type: integer, default: 0 }
    created_at:
    updated_at:
  katao_member_deposit:
    id:
    katao_member_id:     { type: integer, foreignTable: katao_member, foreignReference: id, required: true, onDelete: cascade }
    amount:              { type: float, required: true }
    comment:             { type: longvarchar }
    currency:            { type: integer, required: true, default: 1 } # 1 : euro, 2 : sol
    payment_mode:        { type: integer, default: 0 } # 0 : autre,  1 : espèce, 2 : chèque, 3 : virement, 4 : avoir
    check_number:        { type: varchar, size: 255 }
    check_bank:          { type: varchar, size: 255 }
    check_deposit:       { type: varchar, size: 255 }
    status:              { type: integer, required: true, default: 1 } # 1 : validé, 2 : provisoire
    valued_at:           { type: timestamp }
    valid_until:         { type: timestamp }
    created_at:
    updated_at:
  katao_member_node:
    id:
    katao_member_id:     { type: integer, foreignTable: katao_member, foreignReference: id, required: true, onDelete: cascade }
    katao_node_id:       { type: integer, foreignTable: katao_node, foreignReference: id, required: true, onDelete: cascade }
    created_at:
  katao_supplier_invoice:
    id:
    katao_supplier_id:   { type: integer, foreignTable: katao_supplier, foreignReference: id, required: true, onDelete: cascade }
    katao_period_id:     { type: integer, foreignTable: katao_period, foreignReference: id, required: true, onDelete: cascade }
    status:              { type: integer, required: true, default: 1 } # 1 : reçue, 2 : payée
    supplier_name:       { type: varchar, size: 255,  required: true }
    supplier_email:      { type: varchar, size: 255,  required: true }
    supplier_address1:   { type: varchar, size: 255,  required: true }
    supplier_address2:   { type: varchar, size: 255 }
    supplier_zip:        { type: varchar, size: 255 }
    supplier_city:       { type: varchar, size: 255,  required: true }
    katao_name:          { type: varchar, size: 255,  required: true }
    katao_address:       { type: longvarchar }
    katao_siret_number:  { type: char, size: 14,  required: true }
    katao_rcs_number:    { type: varchar, size: 255,  required: true }
    katao_capital:       { type: integer, required: true }
    reference:           { type: varchar(255) }
    invoice_date:        { type: timestamp, required: true }
    due_date:            { type: timestamp, required: true }
    payment_date:        { type: timestamp }
    payment_mode:        { type: integer, required: true, default: 1 } # 1 : virement, 2 : CB, 3 : chèque
    payment_details:     { type: varchar, size: 255 }
    payment_amount_euro: { type: float, default: 0 }
    payment_amount_sol:  { type: float, default: 0 }
    taux0_bases_ht:      { type: float, default: 0 } # Taux de TVA : 0
    taux0_discount_ht:   { type: float, default: 0 }
    taux0_amount:        { type: float, default: 0 }
    taux1_bases_ht:      { type: float, default: 0 } # Taux de TVA : 19.6%
    taux1_discount_ht:   { type: float, default: 0 }
    taux1_amount:        { type: float, default: 0 }
    taux2_bases_ht:      { type: float, default: 0 } # Taux de TVA : 5.5%
    taux2_discount_ht:   { type: float, default: 0 }
    taux2_amount:        { type: float, default: 0 }
    total_ht:            { type: float, default: 0 }
    total_tva:           { type: float, default: 0 }
    comment:             { type: longvarchar }
    fees_shipping_ht:    { type: float, default: 0 }
    fees_shipping_rate:  { type: float, default: 0 }
    fees_shipping_tva:   { type: float, default: 0 }
    fees_billing_ht:     { type: float, default: 0 }
    fees_billing_rate:   { type: float, default: 0 }
    fees_billing_tva:    { type: float, default: 0 }
    created_at:
    updated_at:
  katao_supplier_invoice_product:
    id:
    katao_supplier_invoice_id: { type: integer, foreignTable: katao_supplier_invoice, foreignReference: id, onDelete: cascade }
    katao_product_id:          { type: integer, foreignTable: katao_product, foreignReference: id, onDelete: cascade }
    product_reference:         { type: varchar(255) }
    product_name:              { type: varchar, size: 255,  required: true }
    product_price_euro:        { type: float, required: true }
    product_margin:            { type: float }
    product_tva_rate:          { type: float, default: 0 }
    quantity:                  { type: integer, required: true, default: 1 }
    product_price_total:       { type: float, default: 0 }
    created_at:
    updated_at:
  katao_supplier_invoice_order:
    katao_supplier_invoice_id: { type: integer, primaryKey: true, foreignReference: id, foreignTable: katao_supplier_invoice, onDelete: cascade, onUpdate: cascade, required: true }
    katao_order_id:            { type: integer, primaryKey: true, foreignReference: id, foreignTable: katao_order, onDelete: cascade, onUpdate: cascade, required: true }
  katao_supplier_product_category:
    katao_supplier_id:         { type: integer, primaryKey: true, foreignReference: id, foreignTable: katao_supplier, onDelete: cascade, onUpdate: cascade, required: true }
    katao_product_category_id: { type: integer, primaryKey: true, foreignReference: id, foreignTable: katao_product_category, onDelete: cascade, onUpdate: cascade, required: true }
  katao_member_group:
    id:
    katao_member_group_name: { type: varchar(255) }
  katao_assoc_member_group:
    member_id: { type: integer, primaryKey: true, foreignReference: id, foreignTable: katao_member, required: true, onDelete: cascade }
    group_id: { type: integer, primaryKey: true, foreignReference: id, foreignTable: katao_member_group, required: true, onDelete: cascade }
  katao_member_adhesion:
    id:
    katao_user_id: { type: integer, foreignReference: id, foreignTable: katao_user, required: true }
    year: { type: date, required: true }
    amount: { type: float, required: true }
    type: { type: integer, size: 2 }
    payment_kind: { type: integer, required: true }
    check_no: { type: varchar(255) }