<?php
{
    ini_set("display_errors", "On");
    ini_set("mysql.trace_mode", "On");

    // init
    //
    // values which cannot be empty
    if (empty($_GET["fUpdCase"]))
    {
        echo "Please specify what kind of change you want to do.<br>";
        return;
    }
    if (empty($_GET["fIndex"]))
    {
        echo "Please give an index.<br>";
        return;
    }
    if (empty($_GET["fProgram"]))
    {
        echo "Please select a program.<br>";
        return;
    }
    if (empty($_GET["fKey1"]))
    {
        echo "Please select a key1.<br>";
        return;
    }
    if (empty($_GET["fValue"]) && $_GET["fValue"]!=0)
    {
        echo "Please insert a value.<br>";
        return;
    }
    if (empty($_GET["fDescription"]))
    {
        echo "Please insert a description.<br>";
        return;
    }
    if (empty($_GET["fType"]))
    {
        echo "type empty";
        return;
    }
    // values which may be empty
    if (empty($_GET["fKey2"]))
        $_GET["fKey2"]="";
    if (empty($_GET["fOriginalIndex"]))
        $_GET["fOriginalIndex"]="";
    if (empty($_GET["fMin"]) && $_GET["fMin"]!=0)
        $_GET["fMin"]="";
    if (empty($_GET["fMax"]) && $_GET["fMax"]!=0)
        $_GET["fMax"]="";

    if (empty($_SERVER['PHP_AUTH_USER']))
    {
        // be careful, this output is evaluated by UpdateRow() in po.js
        echo "user empty";
        return;
    }
    include("db.php");
    $db_id = mysqli_connect($host, $user, $pw, $db);
    if (mysqli_connect_errno())
    {
        printf("mysql_connect returned the following error: %s\n", mysqli_connect_error());
        die("");
    }

    //query old entry
    $query1="SELECT fValue, fProgram, fKey1, fDescription, fType, fMin, fMax, fKey2 from ProgramOption WHERE fIndex='".$_GET["fIndex"]."';";
    //compare new values to old values
    //insert only, if at least one values is new
    //  update counter only if value is updated
    if (!$result1=mysqli_query($db_id, $query1))
    {
        echo "Error sending query: ".$query1;
        return;
    }
    $row1 = mysqli_fetch_row($result1);

    if ($_GET["fUpdCase"]!=4
        && strcmp($row1[0],$_GET["fValue"])==0
        && strcmp($row1[1],$_GET["fProgram"])==0
        && strcmp($row1[2],$_GET["fKey1"])==0
        && strcmp($row1[7],$_GET["fKey2"])==0
        && strcmp($row1[3],$_GET["fDescription"])==0
        && strcmp($row1[4],$_GET["fType"])==0
        //&& strcmp($row1[5],$_GET["fMin"])==0
        //&& strcmp($row1[6],$_GET["fMax"])==0
       )
    {
        // be careful, this output is evaluated by UpdateRow() in po.js
        echo "no change";
        mysqli_free_result($result1);
        return;
    }
    else
        mysqli_free_result($result1);

    //check if key alread exists
    if (empty($_GET["fKey2"]))
        $query2="SELECT Count(*) from ProgramOption WHERE fKey1='".$_GET["fKey1"]."' AND ISNULL(fKey2)";
    else
        $query2="SELECT Count(*) from ProgramOption WHERE fKey1='".$_GET["fKey1"]."' AND fKey2='.".$_GET["fKey2"]."'";
    $query2.=" AND fCounter=(Select Max(fCounter) from History)";
    if (!$result2=mysqli_query($db_id, $query2))
    {
        echo "Error sending query: ".$query2;
        return;
    }
    $row2 = mysqli_fetch_row($result2);
    if (($_GET["fUpdCase"]==1 || $_GET["fUpdCase"]==5) && $row2[0]>0)
    {
        // be careful, this output is evaluated by UpdateRow() in po.js
        echo "key exists";
        mysqli_free_result($result2);
        return;
    }
    else
        mysqli_free_result($result2);

    //check if program alread exists
    $query3="SELECT Count(*) from ProgramOption WHERE fProgram='".$_GET["fProgram"]."'";
    $query3.=" AND fCounter=(Select Max(fCounter) from History)";
    if (!$result3=mysqli_query($db_id, $query3))
    {
        echo "Error sending query: ".$query3;
        return;
    }
    $row3 = mysqli_fetch_row($result3);
    if ($_GET["fUpdCase"]==5 && $row3[0]>0)
    {
        // be careful, this output is evaluated by UpdateRow() in po.js
        echo "prog exists";
        mysqli_free_result($result3);
        return;
    }
    else
        mysqli_free_result($result3);

    //note for fType:
    //SHOW COLUMNS FROM ProgramOption LIKE 'fType';

    //new entry in table ProgramOption
    //update only in case 3 if value is not changed
    if ($_GET["fUpdCase"]==6)
        $query0="DELETE FROM ProgramOption ";
    else
    {
        if (strcmp($row1[0],$_GET["fValue"])==0 && $_GET["fUpdCase"]==3)
            $query0="UPDATE ";
        else
            $query0="INSERT ";
        $query0.=" ProgramOption SET ";
        // set program, key, description, type
        $query0.="fProgram='".$_GET["fProgram"]."'";
        if (!empty($_GET["fOriginalIndex"]))
            $query0.=", fOriginalIndex='".$_GET["fOriginalIndex"]."'";
        if (!empty($_GET["fMin"]) || $_GET["fMin"]==0)
            $query0.=", fMin='".$_GET["fMin"]."'";
        if (!empty($_GET["fMax"]) || $_GET["fMax"]==0)
            $query0.=", fMax='".$_GET["fMax"]."'";
        $query0.=", fUser='".$_SERVER['PHP_AUTH_USER']."'";
        $query0.=", fKey1='".$_GET["fKey1"]."'";
        if (!empty($_GET["fKey2"]))
            $query0.=", fKey2='.".$_GET["fKey2"]."'";
        $query0.=", fDescription='".$_GET["fDescription"]."'";
        $query0.=", fType='".$_GET["fType"]."'";
        // set value
        // change value in case is was
        if ($_GET["fUpdCase"]==2 //inserted
            || ($_GET["fUpdCase"]==3 && strcmp($row1[0],$_GET["fValue"])!=0))// changed
            $query0.=", fValue='".$_GET["fValue"]."'";
        // set to NULL in case it was
        if ($_GET["fUpdCase"]==1 || $_GET["fUpdCase"]==5 //new insert
            || $_GET["fUpdCase"]==4) //delete
            $query0.=", fValue=NULL";
        //increase counter in case value was
        if ($_GET["fUpdCase"]==2//created
            || ($_GET["fUpdCase"]==3 && strcmp($row1[0],$_GET["fValue"])!=0)//changed
            || $_GET["fUpdCase"]==4)//deleted
        {
            $query0.=", fValidFrom=Now() ";
            $query0.=", fCounter= (Select Max(fCounter) from History)+1 ";
        }
        else//else set current counter
            $query0.=", fCounter= (Select Max(fCounter) from History) ";
    }
    //do update only for current row
    if ((strcmp($row1[0],$_GET["fValue"])==0 && $_GET["fUpdCase"]==3) || $_GET["fUpdCase"]==6)
        $query0.=" WHERE fIndex=".$_GET["fIndex"];
    if ($_GET["fUpdCase"]==6)
        $query0.=" AND fValidFrom='1970-01-01 00:00:00'";
    $query0.=";";

    //update counter in case value was 
    if ($_GET["fUpdCase"]==2 //created
        || ($_GET["fUpdCase"]==3 && strcmp($row1[0],$_GET["fValue"])!=0)//changed
       || $_GET["fUpdCase"]==4)//deleted
    {
        //update counter for other valid entries in ProgramOption
        $query0.=" UPDATE ProgramOption SET ";
        $query0.=" fCounter= (Select Max(fCounter) from History)+1 ";
        $query0.=" WHERE fCounter= (Select Max(fCounter) from History) ";
        if ($_GET["fUpdCase"]==3 || $_GET["fUpdCase"]==4)
            $query0.=" AND NOT fIndex=".$_GET["fIndex"];
        $query0.=" AND ((NOT ISNULL(fValue) AND NOT fValidFrom='1970-01-01 00:00:00') ";//normal entries, but not deleted ones
        $query0.=" OR (ISNULL(fValue) AND fValidFrom='1970-01-01 00:00:00')); ";//new entries

        //insert new counter to history
        $query0.=" INSERT History SET ";
        $query0.=" fCounter= (Select Max(fCounter) from ProgramOption) ";
        $query0.=", fValidFrom=Now() ";
    }

    $result0=mysqli_multi_query($db_id, $query0);
    if ($result0)
    {
        echo "Query \"" . $query0 . "\" was successful.";
        return 0;
    }
    else
    {
        echo "Query \"" . $query0 . "\" was not successful.";
        return 2;
    }

    mysqli_free_result($result0);
    mysqli_close($db_id);

    ini_set("display_errors", "Off");
    ini_set("mysql.trace_mode", "Off");
}
?>