Dumped on 2004-08-27

Index of database - freetrade

• public
• address
• affiliate
• affiliate_global
• affiliate_numbers
• affiliate_unpaid_commission
• affiliates_commission
• attribute
• billing
• coupon
• coupon_user
• credit
• department
• department_item
• fee
• ftuser
• inventory_log
• invoice
• invoice_billing
• invoice_coupon
• invoice_fee
• invoice_log
• invoice_shipping
• invoice_sku
• invoice_sku_variation
• invoice_status
• item
• item_item
• localize
• news
• permission
• recdmaterial
• relationship
• rma
• rma_invoice_sku
• rma_invoice_status
• rma_status
• seed
• session
• session_sku
• session_sku_variation
• shipping
• sku
• sku_variation
• status
• status_log
• store
• tax
• user_permission
• user_sku
• user_sku_variation
• variation
• check_affiliate_numbers( integer )
• commission_end_date( )
• from_unixtime( integer )
• is_commission_mature( timestamp without time zone )
• returns_revoke_update( )
• returns_total_update( )
• returns_update( )
• sales_update( )
• ship_date( integer )
• unix_timestamp( timestamp without time zone )

Table: address

Tables referencing this one via Foreign Key Constraints:

• ftuser

• invoice_billing

• invoice_shipping

Index - Schema public

Table: affiliate

Tables referencing this one via Foreign Key Constraints:

• invoice_billing

Index - Schema public

Table: affiliate_global

Index - Schema public

Table: affiliate_numbers

Index - Schema public

View: affiliate_unpaid_commission

SELECT an.ftuser
, an.sales
, an.salestotal
, an."returns"
, an.returnstotal
, (an.salestotal - an.returnstotal) AS netsales
, an.commission
, an.months 
FROM affiliate_numbers an 
WHERE (
     (an.commissiondate IS NULL)
   AND is_commission_mature
     (
           (an.months)::timestamp without time zone
     )
);

Index - Schema public

View: affiliates_commission

SELECT a.ftuser AS affiliate
, u.login
, sum
(COALESCE
     (auc.sales
           , 0
     )
) AS sales
, sum
(COALESCE
     (auc.salestotal
           , (0)::numeric
     )
) AS salestotal
, sum
(COALESCE
     (auc."returns"
           , 0
     )
) AS "returns"
, sum
(COALESCE
     (auc.returnstotal
           , (0)::numeric
     )
) AS returnstotal
, sum
(
     (COALESCE
           (auc.salestotal
                 , (0)::numeric
           ) - COALESCE
           (auc.returnstotal
                 , (0)::numeric
           )
     )
) AS netsales
, sum
(COALESCE
     (commission
           , (0)::numeric
     )
) AS commission
, max
(months) AS enddate 
FROM (affiliate a 
LEFT JOIN affiliate_unpaid_commission auc 
    ON (
           (a.ftuser = auc.ftuser)
     )
)
, ftuser u 
WHERE (u.id = a.ftuser)
GROUP BY a.ftuser
, u.login;

Index - Schema public

Table: attribute

Tables referencing this one via Foreign Key Constraints:

• variation

Index - Schema public

Table: billing

Tables referencing this one via Foreign Key Constraints:

• invoice_billing

Index - Schema public

Table: coupon

Tables referencing this one via Foreign Key Constraints:

• coupon_user

• invoice_coupon

Index - Schema public

Table: coupon_user

Index - Schema public

Table: credit

Index - Schema public

Table: department

Tables referencing this one via Foreign Key Constraints:

• department

• department_item

Index - Schema public

Table: department_item

Index - Schema public

Table: fee

Tables referencing this one via Foreign Key Constraints:

• invoice_fee

Index - Schema public

Table: ftuser

Tables referencing this one via Foreign Key Constraints:

• affiliate

• affiliate_numbers

• coupon_user

• credit

• rma

• session

• user_permission

• user_sku

Index - Schema public

Table: inventory_log

Index - Schema public

Table: invoice

Tables referencing this one via Foreign Key Constraints:

• invoice_billing

• invoice_coupon

• invoice_fee

• invoice_shipping

• invoice_sku

• invoice_status

• rma

Index - Schema public

Table: invoice_billing

Index - Schema public

Table: invoice_coupon

Index - Schema public

Table: invoice_fee

Index - Schema public

Table: invoice_log

Index - Schema public

Table: invoice_shipping

Index - Schema public

Table: invoice_sku

Tables referencing this one via Foreign Key Constraints:

• rma_invoice_sku

Index - Schema public

Table: invoice_sku_variation

Index - Schema public

Table: invoice_status

Tables referencing this one via Foreign Key Constraints:

• recdmaterial

• rma

Index - Schema public

Table: item

Tables referencing this one via Foreign Key Constraints:

• department_item

• item_item

• sku

Index - Schema public

Table: item_item

Index - Schema public

Table: localize

Index - Schema public

Table: news

Index - Schema public

Table: permission

Tables referencing this one via Foreign Key Constraints:

• user_permission

Index - Schema public

Table: recdmaterial

Index - Schema public

Table: relationship

Tables referencing this one via Foreign Key Constraints:

• item_item

Index - Schema public

Table: rma

Tables referencing this one via Foreign Key Constraints:

• rma_invoice_sku

Index - Schema public

Table: rma_invoice_sku

Tables referencing this one via Foreign Key Constraints:

• recdmaterial

Index - Schema public

View: rma_invoice_status

(
     (
           (
                 (
                  SELECT rma
                       , invoicestatus 
                    FROM (recdmaterial m 
                          JOIN (
                              SELECT rma_invoice_sku.rma
                                   , rma_invoice_sku.id 
                                FROM rma_invoice_sku
                             ) y 
                            ON (
                                   (m.rmainvoicesku = y.id)
                             )
                       )
                GROUP BY rma
                       , invoicestatus
                 )
             UNION (
                  SELECT rma.id AS rma
                       , rma.issued AS invoicestatus 
                    FROM rma
                 )
           )
     )
 UNION (
      SELECT rma.id AS rma
           , COALESCE
           (rma.completed
                 , rma.revoked
           ) AS invoicestatus 
        FROM rma
     )
);

Index - Schema public

View: rma_status

SELECT i.id
, rma
, invoice
, status
, created
, description 
FROM (rma_invoice_status r 
  JOIN invoice_status i 
    ON (
           (r.invoicestatus = i.id)
     )
);

Index - Schema public

Table: seed

Index - Schema public

Table: session

Tables referencing this one via Foreign Key Constraints:

• session_sku

Index - Schema public

Table: session_sku

Index - Schema public

Table: session_sku_variation

Index - Schema public

Table: shipping

Tables referencing this one via Foreign Key Constraints:

• invoice_shipping

Index - Schema public

Table: sku

Tables referencing this one via Foreign Key Constraints:

• invoice_sku

• session_sku

• sku_variation

• user_sku

Index - Schema public

Table: sku_variation

Index - Schema public

Table: status

Tables referencing this one via Foreign Key Constraints:

• invoice_status

Index - Schema public

Table: status_log

Index - Schema public

Table: store

Index - Schema public

Table: tax

Index - Schema public

Table: user_permission

Index - Schema public

Table: user_sku

Index - Schema public

Table: user_sku_variation

Index - Schema public

Table: variation

Tables referencing this one via Foreign Key Constraints:

• sku_variation

Index - Schema public

Function: check_affiliate_numbers( integer )

Returns: boolean

Language: PLPGSQL

    BEGIN
        IF (SELECT COUNT(*)
            FROM affiliate_numbers
            WHERE Months = date_trunc('Month', NOW()) AND
                FTUser = $1) = 0 THEN
            INSERT INTO affiliate_numbers VALUES($1);
        END IF;

        RETURN TRUE;
    END;
    

Function: commission_end_date( )

Returns: timestamp without time zone

Language: SQL

        SELECT (NOW() - ReturnPeriod)::TIMESTAMP
        FROM affiliate_global;
    

Function: from_unixtime( integer )

Returns: abstime

Language: SQL

select $1::ABSTIME;

Function: is_commission_mature( timestamp without time zone )

Returns: boolean

Language: SQL

SELECT (NOW() - (SELECT ReturnPeriod FROM affiliate_global)) >
            (SELECT date_trunc('Month', $1) + '1 Month'::INTERVAL -
                '1 Day'::INTERVAL);

Function: returns_revoke_update( )

Returns: "trigger"

Language: PLPGSQL

    DECLARE
        affiliateID INTEGER;
        sku RECORD;
        total DECIMAL(11,2);
    BEGIN
        -- Work only on revoked RMAs and no change if ship date is older than
        -- configured interval.
        IF (OLD.Revoked IS NOT NULL) OR (NEW.Revoked IS NULL) OR
            (ship_date(NEW.Invoice) < commission_end_date()) THEN
            RETURN NULL;
        END IF;

        -- Initialize.
        SELECT INTO affiliateID Affiliate
        FROM invoice_billing
        WHERE Invoice = NEW.Invoice;

        -- Verify a record exists in affiliate_numbers before update.
        PERFORM check_affiliate_numbers(affiliateID);

        -- Update return count within affiliate_numbers.
        UPDATE affiliate_numbers
        SET Returns = Returns - 1
        WHERE Months = date_trunc('Month', NOW()) AND
            FTUser = affiliateID;

        FOR sku IN SELECT * FROM rma_invoice_sku WHERE RMA = NEW.ID LOOP
            -- Determine the total.
            SELECT INTO total (isk.SalePrice * sku.Quantity)
            FROM invoice_sku isk
            WHERE isk.ID = sku.InvoiceSKU;

            -- Update return total within affiliate_numbers.
            UPDATE affiliate_numbers
            SET ReturnsTotal = ReturnsTotal - total,
                Commission = Commission + (total *
                    (SELECT CommissionRate FROM affiliate_global))
            WHERE Months = date_trunc('Month', NOW()) AND
                FTUser = affiliateID;
        END LOOP;

        RETURN NULL;
    END;
    

Function: returns_total_update( )

Returns: "trigger"

Language: PLPGSQL

    DECLARE
        affiliateID INTEGER;
        invoiceID INTEGER;
        total DECIMAL(11,2);
    BEGIN
        -- No change if ship date is older than configured interval.
        IF ship_date((SELECT rma
                      FROM rma_invoice_sku
                      WHERE ID = New.InvoiceSKU)) < commission_end_date() THEN
            RETURN NULL;
        END IF;

        -- Initialize.
        SELECT INTO invoiceID Invoice
        FROM rma r, rma_invoice_sku ris
        WHERE r.ID = ris.RMA AND ris.ID = NEW.ID;
        SELECT INTO affiliateID Affiliate
        FROM invoice_billing
        WHERE Invoice = invoiceID;

        -- Verify a record exists in affiliate_numbers before update.
        PERFORM check_affiliate_numbers(affiliateID);

        -- Determine the total.
        SELECT INTO total (isk.SalePrice * NEW.Quantity)
        FROM invoice_sku isk
        WHERE isk.ID = NEW.InvoiceSKU;

        -- Update return total within affiliate_numbers.
        UPDATE affiliate_numbers
        SET ReturnsTotal = ReturnsTotal + total,
            Commission = Commission - (total *
                (SELECT CommissionRate FROM affiliate_global))
        WHERE Months = date_trunc('Month', NOW()) AND
            FTUser = affiliateID;

        RETURN NULL;
    END;
    

Function: returns_update( )

Returns: "trigger"

Language: PLPGSQL

    DECLARE
        affiliateID INTEGER;
    BEGIN
        -- No change if ship date is older than configured interval.
        IF ship_date(NEW.Invoice) < commission_end_date() THEN
            RETURN NULL;
        END IF;

        -- Initialize.
        SELECT INTO affiliateID Affiliate
        FROM invoice_billing
        WHERE Invoice = NEW.Invoice;

        -- Verify a record exists in affiliate_numbers before update.
        PERFORM check_affiliate_numbers(affiliateID);

        -- Update return count within affiliate_numbers.
        UPDATE affiliate_numbers
        SET Returns = Returns + 1
        WHERE Months = date_trunc('Month', NOW()) AND
            FTUser = affiliateID;

        RETURN NULL;
    END;
    

Function: sales_update( )

Returns: "trigger"

Language: PLPGSQL

    DECLARE
        affiliateID INTEGER;
        total DECIMAL(11,2);
    BEGIN
        -- Configure on whether to add or subtract from sales.
        IF NEW.Status = (SELECT ID FROM status WHERE name = 'Shipped') THEN
            -- Determine the total.
            SELECT INTO total SUM(SalePrice * Quantity)
            FROM invoice_sku
            WHERE Invoice = NEW.Invoice;

            -- Initialize.
            SELECT INTO affiliateID Affiliate
            FROM invoice_billing
            WHERE Invoice = NEW.Invoice;

            -- Verify a record exists in affiliate_numbers before update.
            PERFORM check_affiliate_numbers(affiliateID);

            -- Update sales count and total within affiliate_numbers.
            UPDATE affiliate_numbers
            SET Sales = Sales + 1,
                SalesTotal = SalesTotal + total,
                Commission = Commission + (total *
                    (SELECT CommissionRate FROM affiliate_global))
            WHERE Months = date_trunc('Month', NEW.Created) AND
                FTUser = affiliateID;
        END IF;

        RETURN NULL;
    END;
    

Function: ship_date( integer )

Returns: timestamp without time zone

Language: SQL

        SELECT date_trunc('Month', Created)
        FROM invoice_status
        WHERE Invoice = $1 AND
            Status = (SELECT ID
                      FROM status
                      WHERE name = 'Shipped');
    

Function: unix_timestamp( timestamp without time zone )

Returns: integer

Language: SQL

select date_part('epoch', $1)::INT4;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict