[Koha-bugs] [Bug 7365] Koha uses MySQL

[bugzilla-daemon at bugs.koha-community.org]

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7365

stephane.delaune at biblibre.com changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
   Attachment #8439|0                           |1
        is obsolete|                            |

--- Comment #15 from stephane.delaune at biblibre.com ---
Created attachment 8534
  -->
http://bugs.koha-community.org/bugzilla3/attachment.cgi?id=8534&action=edit
proposed patch (updated)

as suggested by Ian Walls the patch replace "SHOW COLMUNS" in
ImportExportFramework.pm with $dbh->column_info(undef, undef, $table, "%");
now DBQ implement 2 subs : 
- dateSub (Subtract a time value (interval) from a date)
- ifNull (Returns $a if not null, else return $b)

they are use in sub GetLateOrders in C4/Acquisition.pm .

If db_scheme is mysql, the $query created by trhis sub is :
    SELECT aqbasket.basketno,
        aqorders.ordernumber,
        DATE(aqbasket.closedate)  AS orderdate,
        aqorders.rrp              AS unitpricesupplier,
        aqorders.ecost            AS unitpricelib,
        aqorders.claims_count     AS claims_count,
        aqorders.claimed_date     AS claimed_date,
        aqbudgets.budget_name     AS budget,
        borrowers.branchcode      AS branch,
        aqbooksellers.name        AS supplier,
        aqbooksellers.id          AS supplierid,
        biblio.author, biblio.title,
        biblioitems.publishercode AS publisher,
        biblioitems.publicationyear,

    aqorders.quantity - IFNULL(aqorders.quantityreceived, 0) AS quantity,
    (aqorders.quantity - IFNULL(aqorders.quantityreceived, 0)) * aqorders.rrp
AS subtotal,
    (CAST(now() AS date) - closedate) AS latesince

    FROM
        aqorders LEFT JOIN biblio     ON biblio.biblionumber         =
aqorders.biblionumber
        LEFT JOIN biblioitems         ON biblioitems.biblionumber    =
biblio.biblionumber
        LEFT JOIN aqbudgets           ON aqorders.budget_id          =
aqbudgets.budget_id,
        aqbasket LEFT JOIN borrowers  ON aqbasket.authorisedby       =
borrowers.borrowernumber
        LEFT JOIN aqbooksellers       ON aqbasket.booksellerid       =
aqbooksellers.id
        WHERE aqorders.basketno = aqbasket.basketno
        AND ( datereceived = ''
            OR datereceived IS NULL
            OR aqorders.quantityreceived < aqorders.quantity
        )
        AND (aqorders.datecancellationprinted IS NULL OR
aqorders.datecancellationprinted='0000-00-00')
     AND (closedate <= DATE_SUB(CAST(now() AS date), INTERVAL ? DAY))  AND
aqbasket.booksellerid = ?  AND borrowers.branchcode LIKE ?  
    HAVING quantity          <> 0
        AND unitpricesupplier <> 0
        AND unitpricelib      <> 0

ORDER BY latesince, basketno, borrowers.branchcode, supplier


If db_scheme is pg, the $query created by trhis sub is :
SELECT aqbasket.basketno,
        aqorders.ordernumber,
        DATE(aqbasket.closedate)  AS orderdate,
        aqorders.rrp              AS unitpricesupplier,
        aqorders.ecost            AS unitpricelib,
        aqorders.claims_count     AS claims_count,
        aqorders.claimed_date     AS claimed_date,
        aqbudgets.budget_name     AS budget,
        borrowers.branchcode      AS branch,
        aqbooksellers.name        AS supplier,
        aqbooksellers.id          AS supplierid,
        biblio.author, biblio.title,
        biblioitems.publishercode AS publisher,
        biblioitems.publicationyear,

    aqorders.quantity - COALESCE(aqorders.quantityreceived, 0) AS quantity,
    (aqorders.quantity - COALESCE(aqorders.quantityreceived, 0)) * aqorders.rrp
AS subtotal,
    (CAST(now() AS date) - closedate) AS latesince

    FROM
        aqorders LEFT JOIN biblio     ON biblio.biblionumber         =
aqorders.biblionumber
        LEFT JOIN biblioitems         ON biblioitems.biblionumber    =
biblio.biblionumber
        LEFT JOIN aqbudgets           ON aqorders.budget_id          =
aqbudgets.budget_id,
        aqbasket LEFT JOIN borrowers  ON aqbasket.authorisedby       =
borrowers.borrowernumber
        LEFT JOIN aqbooksellers       ON aqbasket.booksellerid       =
aqbooksellers.id
        WHERE aqorders.basketno = aqbasket.basketno
        AND ( datereceived = ''
            OR datereceived IS NULL
            OR aqorders.quantityreceived < aqorders.quantity
        )
        AND (aqorders.datecancellationprinted IS NULL OR
aqorders.datecancellationprinted='0000-00-00')
     AND (closedate <= CAST(now() AS date) - interval '? DAY')  AND
aqbasket.booksellerid = ?  AND borrowers.branchcode LIKE ?  
    HAVING quantity          <> 0
        AND unitpricesupplier <> 0
        AND unitpricelib      <> 0

ORDER BY latesince, basketno, borrowers.branchcode, supplier

-- 
You are receiving this mail because:
You are watching all bug changes.