Ventas_sql.php

<?php

try
{
    //Open database connection
    $con = mysql_connect("localhost","root","itesm");
    mysql_select_db("sgv", $con);
    //Getting records (listAction)
    if($_GET["action"] == "list")
    {
        //Get record count
        $result = mysql_query("SELECT COUNT(*) AS RecordCount FROM ventas;");
        $row = mysql_fetch_array($result);
        $recordCount = $row['RecordCount'];

        //Get records from database
        $result = mysql_query("
            SELECT distinct ventas.ID_Venta, ventas.Fecha_Venta, ventas.Precio_Total, clientes.ID_Cliente, clientes.Nombre, monedas.*
            FROM ventas,clientes, monedas
            WHERE clientes.ID_Cliente=ventas.ID_Cliente
            AND monedas.ID_Moneda=ventas.ID_Moneda ORDER BY " . $_GET["jtSorting"] . " LIMIT " . $_GET["jtStartIndex"] . "," . $_GET["jtPageSize"] . ";");

        //Add all records to an array
        $rows = array();

        while($row = mysql_fetch_array($result))
        {
            $rows[] = $row;
        }

        //Return result to jTable
        $jTableResult = array();
        $jTableResult['Result'] = "OK";
        $jTableResult['TotalRecordCount'] = $recordCount;
        $jTableResult['Records'] = $rows;
        print json_encode($jTableResult);
    }
    //Creating a new record (createAction)
    else if($_GET["action"] == "create")
    {
        //Insert record into database
        $result = mysql_query("INSERT INTO ventas(ID_Cliente, Fecha_Venta, ID_Moneda,Precio_Total) VALUES('" . $_POST["DD_Cliente"] . "', '" . $_POST["Fecha_Venta"] . "','" . $_POST["DD_Moneda"] . "', '0');");

        //Get last inserted record (to return to jTable)
        $result = mysql_query("SELECT * FROM ventas WHERE ID_Venta = LAST_INSERT_ID();");
        $row = mysql_fetch_array($result);

        //Return result to jTable
        $jTableResult = array();
        $jTableResult['Result'] = "OK";
        $jTableResult['Record'] = $row;
        print json_encode($jTableResult);
    }
    //Updating a record (updateAction)
    else if($_GET["action"] == "update")
    {
        //Update record in database

        $result = mysql_query("UPDATE ventas SET ID_Cliente = '" . $_POST["DD_Cliente"] . "', Fecha_Venta= '" . $_POST["Fecha_Venta"] . "', ID_Moneda= '" . $_POST["DD_Moneda"] . "' WHERE ID_Venta = " . $_POST["ID_Venta"] . ";");

        //Return result to jTable
        $jTableResult = array();
        $jTableResult['Result'] = "OK";
        print json_encode($jTableResult);
    }
    //Deleting a record (deleteAction)
    else if($_GET["action"] == "delete")
    {
        //Delete from database
        $result = mysql_query("DELETE FROM ventas WHERE ID_Venta = " . $_POST["ID_Venta"] . ";");

        //Return result to jTable
        $jTableResult = array();
        $jTableResult['Result'] = "OK";
        print json_encode($jTableResult);
    }

    else if($_GET["action"] == "dropdown_cliente"){

        $result = mysql_query("SELECT ID_Cliente, Nombre FROM clientes ORDER BY ID_Cliente");
        //Add all records to an array
        $rows = array();
        while ($row = mysql_fetch_array($result)) {
            $rows[] = array('Value' => $row['ID_Cliente'],
            'DisplayText' => $row['Nombre']);
        }

    $result = array('Result' => 'OK',
            'Options' => $rows);
    print json_encode($result);

    }

    else if($_GET["action"] == "dropdown_moneda"){

        $result = mysql_query("SELECT ID_Moneda, Nombre_Moneda FROM monedas ORDER BY ID_Moneda");
        //Add all records to an array
        $rows = array();
        while ($row = mysql_fetch_array($result)) {
            $rows[] = array('Value' => $row['ID_Moneda'],
            'DisplayText' => $row['Nombre_Moneda']);
        }

        $result = array('Result' => 'OK',
            'Options' => $rows);
        print json_encode($result);

    }

    else if($_GET["action"] == "calculartotal"){

    $result = mysql_query("SELECT ID_Cliente, Nombre FROM clientes ORDER BY ID_Cliente");
    //Add all records to an array
    $rows = array();
    while ($row = mysql_fetch_array($result)) {
        $rows[] = array('Value' => $row['ID_Cliente'],
        'DisplayText' => $row['Nombre']);
    }

    $result = array('Result' => 'OK',
            'Options' => $rows);
    print json_encode($result);

    }

    //Close database connection
    mysql_close($con);

}
catch(Exception $ex)
{
    //Return error message
    $jTableResult = array();
    $jTableResult['Result'] = "ERROR";
    $jTableResult['Message'] = $ex->getMessage();
    print json_encode($jTableResult);
}

?>