surveyMntc.data

<?php
/**
 * File: surveyMntc.prg
 * Version 2 for survey maintenance and marketing messages using the favorite Kendo.
 */



/**
 * function saveIntro($isMarketing, $dbh, $Cu)
 * Data call to save the intro information for the survey.  (Only available for survey maintenance, not marketing messages.)
 *
 * @param boolean $isMarketing -- if true, exits with an error message.
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union code uppercase (from high above; it's like manna)
 * @return array --
 *      $error -- Array of errors (if one were to exist)
 *      $code -- zero if no errors; non-zero for the existence of an error
 *      $sql -- list of sqls used
 */
function saveIntro($isMarketing, $dbh, $Cu, $languageArray)
{
    $parameters= array();
    $sqls= array();

    try
    {
        if ($isMarketing)
            throw new exception("Operation not available for marketing messages!", 8);
        if ($languageArray == "")
            throw new exception("Language array is invalid!", 1);
        $languageArray= HCU_JsonDecode($languageArray);
        if (!is_array($languageArray))
            throw new exception("Language array is invalid!", 2);

        $validLanguages= array();
        // Requery here to check for existence
        $sql= "select language from culanguage";
        $sqls[]= $sql;

        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("Language query failed!", 5);


        for($i=0; $row=db_fetch_array($sth, $i); $i++)
        {
            $validLanguages[]= $row[0];
        }

        foreach($languageArray as $languageRow)
        {
            if (!HCU_array_key_exists("language", $languageRow) || !HCU_array_key_exists("surveyintro", $languageRow) || !HCU_array_key_exists("exists", $languageRow))
                throw new exception("Language array is invalid!", 3);
            if (!in_array($languageRow["exists"], array("Y", "N")))
                throw new exception("Exists variable is invalid!", 4);
            if (!in_array($languageRow["language"], $validLanguages))
                throw new exception("Language is invalid!", 6);
        }

        if (!db_work ($dbh, HOMECU_WORK_BEGIN))
            throw new exception("begin query failed!", 9);

        foreach($languageArray as $languageRow)
        {
            $language= trim($languageRow["language"]);
            $surveyintro= trim(strip_tags($languageRow["surveyintro"]));
            $exists= $languageRow["exists"] == "Y";

            if ($exists)
            {
                $sql= $surveyintro == "" ? "delete from cusurveyintro where cu='$Cu' and language='" . prep_save($language, 5) . "'"
                    : "update cusurveyintro set surveyintro='" . prep_save($surveyintro) . "' where cu='$Cu' and language='" . prep_save($language, 5) . "'";
                $sqls[]= $sql;
            }
            else if ($surveyintro != "")
            {
                $sql= "insert into cusurveyintro (cu, language, surveyintro) values ('$Cu', '" . prep_save($language, 5) . "', '" . prep_save($surveyintro) . "')";
                $sqls[]= $sql;
            }

            $sth= db_query($sql, $dbh);
            if (!$sth)
                throw new exception("surveyinto update failed!", 110);
        }

        if (!db_work($dbh, HOMECU_WORK_COMMIT))
            throw new exception("commit work failed!", 111);
    }
    catch(exception $e)
    {
        if ($e->getCode() >= 100)
            db_work($dbh, HOMECU_WORK_ROLLBACK); // Got greater problems if this fails.

        return array("error" => array($e->getMessage()), "code" => $e->getCode(), "sql" => $sqls);
    }
    return array("error" => array(), "code" => 0, "sql" => $sqls);
}

/**
 * function readIntro($isMarketing, $dbh, $Cu)
 * Reads the intro information from the database
 *
 * @param boolean $isMarketing -- if true, exits with an error message.
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union code uppercase (from high above; it's like manna)
 * @return array --
 *      $error -- Array of errors (if one were to exist)
 *      $code -- zero if no errors; non-zero for the existence of an error
 *      $sql -- list of sqls used
 *      $languageArray -- data for the tab strip
 */
function readIntro($isMarketing, $dbh, $Cu)
{
    $sqls= array();
    try
    {
        if ($isMarketing)
            throw new exception("Operation not available for marketing messages!", 2);
        $sql= "select l.description, l.language, i.surveyintro from culanguage l left join cusurveyintro i on l.language = i.language and i.cu='$Cu' order by l.language";
        $sqls[]= $sql;
        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("select query failed!", 1);
        $languageArray= array();
        for($i=0; $row= db_fetch_assoc($sth, $i); $i++)
        {
            $surveyintro= trim($row["surveyintro"]);
            $row["exists"]= $surveyintro != "";
            $row["surveyintro"]= $surveyintro;
            $row["description"]= isset($row["description"]) ? trim($row["description"]) : "";
            $row["language"]= isset($row["language"]) ? trim($row["language"]) : "";
            $row["blank"]= " ";
            $row["index"]= $i;
            $languageArray[]= $row;
        }
    }
    catch(exception $e)
    {
        return array("code" => $e->getCode(), "error" => array($e->getMessage()), "sql" => $sqls);
    }
    return array("code" => 0, "error" => array(), "sql" => $sqls, "languageArray" => $languageArray);
}

/**
 * function readResultDetails($isMarketing, $dbh, $Cu)
 * Reads the data for the result details (for if/when you wish to see which members selected such a stupid answer.)
 *
 * @param boolean $isMarketing -- if true, exits with an error message.
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union code uppercase (from high above; it's like manna)
 * @return array --
 *      $error -- Array of errors (if one were to exist)
 *      $code -- zero if no errors; non-zero for the existence of an error
 *      $sql -- list of sqls used
 *      $topData -- data for the survey id, Cu, and answer the page is for
 *      $bottomData -- the dirtbag report of all the members that selected such a stupid answer
 */
function readResultDetails($isMarketing, $dbh, $Cu, $surveyid, $language, $answerid)
{
    $sqls= array();

    $surveyid= isset($surveyid) ? intval($surveyid) : 0;
    $answerid= isset($answerid) ? intval($answerid) : 0;
    $language= isset($language) ? trim($language) : "";

    try
    {
        if ($isMarketing)
            throw new exception("Operation not available for marketing messages!", 2);

        $sql= "with subset as (select d.answerid, d.surveyid, d.language, c.cu as saysfound, c.user_id, c.user_name, c.optin, c.email, d.answertext as answer, q.question, m.surveyname,
              m.startdate, m.stopdate from cusurveydetail d
              inner join cusurveymaster m on d.surveyid = m.surveyid and d.cu = m.cu and d.surveyid= $surveyid and d.cu= '$Cu' and d.language= '" . prep_save($language, 5) . "'
              inner join cusurveyquest q on d.surveyid = q.surveyid and d.cu = q.cu and d.language = q.language
              left join (select s.cu, s.answerid, s.surveyid, s.language, u.user_name, u.egenl_flag as optin, u.email, u.user_id from cusurveysays s
                inner join ${Cu}user u on s.user_id= u.user_id) c on d.surveyid= c.surveyid and d.cu = c.cu and d.language = c.language and d.answerid= c.answerid)
              select a.*, coalesce(b.votes, 0) as votes, coalesce(c.totalvotes, 0) as totalvotes
              from (select * from subset where answerid= $answerid) a
              cross join (select count(saysfound) from subset where answerid= $answerid) b(votes)
              cross join (select count(saysfound) from subset) c(totalvotes)";
        $sqls[]= $sql;
        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("Subset query failed!", 1);

        $first= true;
        $topData= array();
        $bottomData= array();
        for($i=0; $row= db_fetch_assoc($sth, $i); $i++)
        {
            if ($first)
            {
                $startdate= new DateTime($row["startdate"]);
                $stopdate= new DateTime($row["stopdate"]);
                $row["range"]= $startdate->format("m/d/Y") . " - " . $stopdate->format("m/d/Y");
                $row["percentage"]= $row["totalvotes"] == 0 ? 0 : $row["votes"] / $row["totalvotes"];
                $topData[]= $row;

                unset($topData[0]["user_id"]);
                unset($topData[0]["user_name"]);
                unset($topData[0]["optin"]);
                unset($topData[0]["email"]);
                unset($topData[0]["startdate"]);
                unset($topData[0]["stopdate"]);
                $first= false;
            }

            if (isset($row["saysfound"]))
                $bottomData[]= array("user_id" => $row["user_id"], "user_name" => $row["user_name"], "optin" => trim($row["optin"]) == "Y", "email" => $row["email"]);
        }
    }
    catch (Exception $e)
    {
        return array("sql" => $sqls, "error" => array($e->getMessage()), "code" => $e->getCode());
    }
    return array("sql" => $sqls, "error" => array(), "code" => 0, "topData" => $topData, "bottomData" => $bottomData);
}

/**
 * function readResults($isMarketing, $dbh, $Cu)
 * Reads the data for the results of a particular survey or any other survey modes: "all", "past", "present", "future", "draft".
 *
 * @param boolean $isMarketing -- if true, exits with an error message.
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union code uppercase (from high above; it's like manna)
 * @return array --
 *      $error -- Array of errors (if one were to exist)
 *      $code -- zero if no errors; non-zero for the existence of an error
 *      $sql -- list of sqls used
 *      $data -- data in an usable format: Array of surveys which have an array of languages which have an array of answers
 */
function readResults($isMarketing, $dbh, $Cu, $mode, $surveyid)
{
    $parameters= array();
    $sqls= array();

    try
    {
        if ($isMarketing)
            throw new exception("Operation not available for marketing messages!", 3);

        $masterSubquery= getMasterSubquery($Cu, false, $mode, $surveyid);
        $sql= "select a.surveyid, a.surveyname, a.startdate, a.stopdate, l.description, l.language, b.answerid, b.count, q.question, b.answertext
        from ($masterSubquery) a
        inner join (select d.surveyid, d.answerid, d.language, d.answertext, count(s.oid) as count from cusurveydetail d
            left join cusurveysays s on d.cu = s.cu and d.surveyid = s.surveyid and d.answerid = s.answerid and d.language = s.language and d.cu='$Cu'
            group by d.surveyid, d.answerid, d.language, d.answertext) as b on a.surveyid = b.surveyid
        inner join culanguage l on b.language = l.language
        inner join cusurveyquest q on b.language = q.language and b.surveyid = q.surveyid and q.cu= '$Cu' order by a.startdate desc, a.stopdate desc, a.surveyid, l.language, b.answerid";
        $sqls[]= $sql;

        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("Count query failed!", 2);

        $map= array();
        $sort= 0;
        for($i=0; list($surveyid, $surveyname, $startdate, $stopdate, $description, $language, $answerid, $numVotes, $question, $answer) = db_fetch_array($sth, $i); $i++)
        {
            $details= array();

            $startdate= new DateTime($startdate);
            $stopdate= new DateTime($stopdate);
            $range= $startdate->format("m/d/Y") . " - " . $stopdate->format("m/d/Y");

            if (!isset($map[$surveyid]))
                $map[$surveyid]= array("surveyid" => $surveyid, "surveyname" => $surveyname, "range" => $range, "details" => array(), "sort" => $sort++);
            if (!isset($map[$surveyid]["details"][$language]))
                $map[$surveyid]["details"][$language]= array("description" => $description, "language" => $language, "total" => 0, "question" => $question, "answers" => array());
            $map[$surveyid]["details"][$language]["answers"][]= array("answerid" => $answerid, "text" => $answer, "count" => $numVotes);
            $map[$surveyid]["details"][$language]["total"]+= $numVotes;
        }

        $colorList= array('Green','Red','Blue','Orange','Purple');
        $clLength= count($colorList);
        foreach($map as $surveyid => $hRow)
        {
            foreach($hRow["details"] as $language => $iRow)
            {
                shuffle($colorList);
                foreach($iRow["answers"] as $j => $jRow)
                {
                    $map[$surveyid]["details"][$language]["answers"][$j]["percentage"]= $iRow["total"] == 0 ? 0 : round(floatval($jRow["count"]) / intval($iRow["total"]), 2);
                    $map[$surveyid]["details"][$language]["answers"][$j]["color"]= $colorList[$j % $clLength];
                }
            }
        }

        // Need to convert to arrays for display
        $map= array_values($map);
        for($i=0, $length=count($map); $i != $length; $i++)
        {
            $map[$i]["details"]= array_values($map[$i]["details"]);
        }
    }
    catch (Exception $e)
    {
        return array("sqls" => $sqls, "error" => array($e->getMessage()), "code" => $e->getCode());
    }

    return array("sqls" => $sqls, "error" => array(), "code" => 0, "data" => $map);
}

/**
 * function save($isMarketing, $dbh, $Cu, $extendDateOnly=false)
 * Saves the survey depending on several factors: surveyid: if zero, then it is a create; $isMarketing: surveytype is saved accordingly;
 *      $extendDateOnly: if true, then very little is being saved
 *
 * @param boolean $isMarketing -- If true, doesn't validate and save answers.  Also, affects the surveytype when creating a new one.
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union code uppercase (from high above; it's like manna)
 * @param boolean $extendDateOnly -- if true, will only save $startdate, $stopdate, $dontshow.  It also will not clear the cusurveysays table for the surveyid.
 * @return array --
 *      $error -- Array of errors (if one were to exist)
 *      $code -- zero if no errors; non-zero for the existence of an error
 *      $sql -- list of sqls used
 */
function save($isMarketing, $dbh, $Cu, $surveyid, $startdate, $stopdate, $surveyname, $surveytitle, $runstat, $employee, $incBalances, $dontshow, $questions, $answers, $extendDateOnly=false)
{
    $sqls= array();

    $surveyid= isset($surveyid) ? intval($surveyid) : 0;
    $startdate= isset($startdate) ? trim($startdate) : "";
    $stopdate= isset($stopdate) ? trim($stopdate) : "";
    $surveyname= isset($surveyname) ? trim($surveyname) : "";
    $surveytitle= isset($surveytitle) ? trim($surveytitle) : "";
    $employee= isset($employee) ? strtoupper(trim($employee)) : "";
    $incBalances= isset($incBalances) ? strtoupper(trim($incBalances)) : "";
    $dontshow= isset($dontshow) ? strtoupper(trim($dontshow)) : "";
    $questions= isset($questions) ? trim($questions) : "";
    $answers= isset($answers) ? trim($answers) : "";

    try
    {
        $dateA= DateTime::createFromFormat("Y-m-d", $startdate);
        if (!$dateA)
            throw new exception("Start date is invalid.", 1);
        if ($dateA->format("Y-m-d") != $startdate)
            throw new exception("Start date is invalid.", 2);
        $dateB= DateTime::createFromFormat("Y-m-d", $stopdate);
        if (!$dateB)
            throw new exception("Stop date is invalid.", 3);
        if ($dateB->format("Y-m-d") != $stopdate)
            throw new exception("Stop date is invalid.", 4);
        if (!in_array($dontshow, array("Y", "N")))
            throw new exception("Don't Show is invalid.", 9);

        if ($dateB < $dateA)
            throw new exception("Stop date need to be after start date.", 19);

        if (!$extendDateOnly)
        {
            if (!is_numeric($runstat))
                throw new exception("Runstat is invalid.", 5);
            if (!in_array($runstat, array(0,1)))
                throw new exception("Runstat is invalid.", 6);
            if (!in_array($employee, array("Y", "N")))
                throw new exception("Employee is invalid.", 7);
            if (!in_array($incBalances, array("Y", "N")))
                throw new exception("IncBalances is invalid.", 8);
        }

        $surveytype= $isMarketing ? 1 : 0;

        if (!db_work ($dbh, HOMECU_WORK_BEGIN))
            throw new exception("begin query failed.", 227);

        if ($surveyid == 0)
        {
            if ($extendDateOnly)
                throw new exception("Can only extend dates of the existing.", 118);

            $sql= "select nextval('cusurveymaster_surveyid_seq')";
            $sqls[]= $sql;

            $sth= db_query($sql, $dbh);
            if (!$sth)
                throw new exception("Nextval query failed.", 212);
            if (db_num_rows($sth) == 0)
                throw new exception("Nextval query failed.", 213);
            list($surveyid)= db_fetch_array($sth, 0);
            $sql= "insert into cusurveymaster (surveyid, cu, startdate, stopdate, surveyname, surveytitle, qstyle, runstat, employee, inc_balances, surveytype, dontshow)
                values ($surveyid, '$Cu', '$startdate','$stopdate','" .  prep_save($surveyname, 50) . "','" . prep_save($surveytitle) . "', 3, $runstat, '$employee', '$incBalances',
                $surveytype, '$dontshow')";
        }
        else
        {
            $sql= $extendDateOnly ? "update cusurveymaster set startdate= '$startdate', stopdate='$stopdate', dontshow='$dontshow', surveyname='" . prep_save($surveyname, 50) . "'
            where cu='$Cu' and surveyid=$surveyid"
            : "update cusurveymaster set startdate= '$startdate', stopdate='$stopdate', surveyname='" . prep_save($surveyname, 50) . "', surveytitle='" . prep_save($surveytitle)
            . "', runstat= $runstat, employee= '$employee', inc_balances='$incBalances', dontshow='$dontshow' where cu='$Cu' and surveyid=$surveyid";
        }

        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("insert/update cusurveymaster failed.", 228);

        if (!$extendDateOnly)
        {
            $validLanguages= array();
            if ($questions != "" || $answers != "")
            {
                // Requery here to check for existence
                $sql= "select language from culanguage";
                $sqls[]= $sql;

                $sth= db_query($sql, $dbh);
                if (!$sth)
                    throw new exception("Language query failed.", 214);


                for($i=0; $row=db_fetch_array($sth, $i); $i++)
                {
                    $validLanguages[]= $row[0];
                }
            }

            $questionExists= array("english" => false, "spanish" => false, "polish" => false);
            $questionsForSQL= array();
            if ($questions != "")
            {
                $questions= HCU_JsonDecode($questions);
                if (!is_array($questions))
                    throw new exception($isMarketing ? "Messages weren't properly encoded." : "Questions weren't properly encoded.", 110);
                foreach($questions as $questionRow)
                {
                    $question= trim($questionRow["question"]);
                    $language= trim($questionRow["language"]);
                    $question= $isMarketing ? strip_tags(str_replace(array("<!--", "-->"), "", $question), "<b><i><p><a><li><ol><ul><em><br><tt><strong><blockquote><img><span><div><style>")
                               : strip_tags($question);

                    if (!in_array($language, $validLanguages))
                        throw new exception("Language is not valid.", 115);
                    switch($language)
                    {
                        case "en_US":
                            $questionExists["english"]= $question != "";
                            break;
                        case "es_US":
                            $questionExists["spanish"]= $question != "";
                            break;
                        case "pl_US":
                            $questionExists["polish"]= $question != "";
                            break;
                    }

                    $questionsForSQL[]= "('" . prep_save($language, 5) . "', '" .  prep_save($question) . "')";
                }
            }

            $sql= "delete from cusurveyquest where cu='$Cu' and surveyid='$surveyid'";
            $sqls[]= $sql;
            $sth= db_query($sql, $dbh);
            if (!$sth)
                throw new exception("delete cusurveyquest failed.", 229);

            if (count($questionsForSQL) > 0)
            {
                $sql= "insert into cusurveyquest (cu, surveyid, language, question) select '$Cu', $surveyid, a.* from (values " . implode(", ", $questionsForSQL) . ") as a";
                $sqls[]= $sql;
                $sth= db_query($sql, $dbh);
                if (!$sth)
                    throw new exception("insert cusurveyquest failed.", 230);
            }


            if (!$questionExists["english"])
                throw new exception("Please include a " . ($isMarketing ? "message body" : "question") . " for English.", 121);

            if (!$isMarketing)
            {
                $answerExists= array("english" => false, "spanish" => false, "polish" => false);
                $answersForSQL= array();
                if ($answers != "")
                {
                    $answers= HCU_JsonDecode($answers);
                    if (!is_array($answers))
                        throw new exception("Answers weren't properly encoded.", 116);

                    $index= 1;
                    foreach($answers as $answerRow)
                    {
                        $english= trim($answerRow["english"]);
                        $polish= trim($answerRow["polish"]);
                        $spanish= trim($answerRow["spanish"]);

                        if ($english == "")
                        {
                            if ($polish == "" && $spanish == "")
                                continue; // Skip empty row
                            throw new exception("English answer must be populated if either the polish or spanish answers are populated.", 122);
                        }

                        $answersForSQL[]= "($index, 'en_US', '" . prep_save($english, 255) . "')";

                        if ($polish != "")
                        {
                            $answerExists["polish"]= true;
                            if (!$questionExists["polish"])
                                throw new exception("Polish question is required when there are one or more Polish answers.", 123);
                            $answersForSQL[]= "($index, 'pl_US', '" . prep_save($polish, 255) . "')";
                        }

                        if ($spanish != "")
                        {
                            $answerExists["spanish"]= true;
                            if (!$questionExists["spanish"])
                                throw new exception("Spanish question is required when there are one or more Spanish answers.", 124);
                            $answersForSQL[]= "($index, 'es_US', '" . prep_save($spanish, 255) . "')";
                        }
                        $index++;
                    }
                }
                $sql= "delete from cusurveydetail where cu='$Cu' and surveyid='$surveyid'";
                $sqls[]= $sql;
                $sth= db_query($sql, $dbh);
                if (!$sth)
                    throw new exception("delete cusurveydetail failed.", 231);

                if (count($answersForSQL) > 0)
                {
                    $sql= "insert into cusurveydetail (cu, surveyid, slidermin, slidermax, answerid, language, answertext)
                        select '$Cu', $surveyid, 0, 0, a.* from (values " . implode(", ", $answersForSQL) . ") as a";
                    $sqls[]= $sql;
                    $sth= db_query($sql, $dbh);
                    if (!$sth)
                        throw new exception("insert cusurveydetail failed!", 132);
                }


                if ($questionExists["spanish"] && !$answerExists["spanish"])
                    throw new exception("One or more Spanish answers are required when there is a Spanish question.", 125);
                if ($questionExists["polish"] && !$answerExists["polish"])
                    throw new exception("One or more Polish answers are required when there is a Polish question.", 126);
            }

            // Delete results
            $sql= "delete from cusurveysays where surveyid='$surveyid' and cu='$Cu'";
            $sqls[]= $sql;
            $sth= db_query($sql, $dbh);
            if (!$sth)
                throw new exception("delete cusurveysays failed!", 233);
        } // End !$extendDateOnly

        if (!db_work($dbh, HOMECU_WORK_COMMIT))
            throw new exception("commit work failed!", 234);
    }
    catch (Exception $e)
    {
        if ($e->getCode() >= 100)
            db_work($dbh, HOMECU_WORK_ROLLBACK); // Got greater problems if this fails.

        return array("sql" => $sqls, "error" => array($e->getCode >= 200 ? "Save failed with code: " . $e->getCode() . "." : $e->getMessage()), "code" => $e->getCode());
    }
    return array("sql" => $sqls, "error" => array(), "code" => 0);
}

/**
 * function getMasterSubquery($Cu, $isMarketing, $mode="all", $surveyid=0)
 * This will get the master subquery.  SQL and logic was copied in several places so I made this function
 *
 * @param string $Cu -- the credit union code uppercase (from high above; it's like manna)
 * @param boolean $isMarketing -- affects the surveytype limitation for the query
 * @param string $mode --
 *      "all" -- No additional limit (still have limits for the Cu and $isMarketing).  Function can be called like getMasterSubquery($Cu, $isMarketing).  In that case, this is assumed.
 *      "single" -- Filters to a particular surveyid.  Data calls don't have to specify the $mode parameter.  In that case, this is assumed.
 *      "past", "present", "future" -- If the survey is not a draft and the survey is past, present, or in the future.
 *      "draft" -- If the survey is a draft.
 * @param integer $surveyid -- Only used for $mode of single to limit the SQL.
 * @return string -- a select statement of the master table with the stipulations according to mode, Cu, and isMarketing.
 */
function getMasterSubquery($Cu, $isMarketing, $mode="all", $surveyid=0)
{
    $masterWhere= array("cu='$Cu'", $isMarketing ? "surveytype = 1" : "coalesce(surveytype, 0) = 0");
    switch($mode)
    {
        case "all":
            break;
        case "present":
            $masterWhere[]= "runstat = 1";
            $masterWhere[]= "now() between startdate and stopdate";
            break;
        case "past":
            $masterWhere[]= "runstat = 1";
            $masterWhere[]= "startdate <= stopdate"; // Don't want to get invalid surveys
            $masterWhere[]= "now() > stopdate";
            break;
        case "future":
            $masterWhere[]= "runstat = 1";
            $masterWhere[]= "startdate <= stopdate"; // Don't want to get invalid surveys
            $masterWhere[]= "now() < startdate";
            break;
        case "draft":
            $masterWhere[]= "runstat = 0";
            break;
        case "single":
        case "":
            if (intval($surveyid) == 0)
                throw new exception("Survey needs to be defined.", 2);
            $masterWhere[]= "surveyid = $surveyid";
            break;
        default:
            throw new exception("Mode is invalid.", 1);
    }

    return "select surveyid, startdate, stopdate, surveyname, runstat, employee, dontshow, inc_balances, surveytitle from cusurveymaster"
    . (count($masterWhere) > 0 ? " where " . implode(" and ", $masterWhere) : "");
}

/**
 * function readView($isMarketing, $dbh, $Cu, $includeMasterData=false)
 * This will read the view (For the surveyView popup and extend dates)
 *
 * @param boolean $isMarketing -- Affects the surveytype limitation and if true, removes the surveyintro and answers from the query and resulting data.
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union code uppercase (from high above; it's like manna)
 * @param boolean $includeMasterData -- if true, more data will be extracted from the master table plus the output will be different
 * @return array --
 *      $error-- array of zero or one errors
 *      $code-- zero if there is an error; non-zero if there are no errors.
 *      $sql-- array of sqls used
 *      $data-- If $includeMasterData, then it will return #2.  Otherwise, it will return #1.  If $isMarketing, answers array is not added/populated.
 * 1) {languages: [{language, description, surveyintro, title, surveys: [{surveyid, question, answers: [{id, text}, ...]}, ...]}, ...]}
 * 2) {surveys: [{surveyid, question, startdate, stopdate, surveytitle, surveyname, isDraft, affectsEmployees, isPopup, dontShow,
 *      languages: [{language, description, surveyintro, question, answers: [{id, text}, ...]}, ...]}, ...]}
 */
function readView($isMarketing, $dbh, $Cu, $surveyid, $mode, $includeMasterData=false)
{
    $sqls= array();

    $surveyid= isset($surveyid) ? intval($surveyid) : 0;
    $mode= isset($mode) ? trim($mode) : "";

    try
    {
        $masterSubquery= getMasterSubquery($Cu, $isMarketing, $mode, $surveyid);
        $sql= "select a.pname, q.question, l.language, l.description, " . (!$isMarketing ? "i.surveyintro, d.answerid, d.answertext, " : "")
           . ($includeMasterData ? "m.* " : "m.surveyid ") . "from cuadmin a inner join culanguage l on a.cu= '$Cu' cross join ($masterSubquery) m
           left join cusurveyquest q on a.cu = q.cu and m.surveyid = q.surveyid and l.language = q.language "
           . (!$isMarketing ? "left join cusurveydetail d on a.cu = d.cu and l.language = d.language and m.surveyid = d.surveyid
           left join cusurveyintro i on a.cu= i.cu and l.language = i.language " : "")
           . "order by l.language, m.stopdate, m.surveyid" . (!$isMarketing ? ", d.answerid" : "");
        $sqls[]= $sql;
        $sth= db_query($sql, $dbh);

        if (!$sth)
            throw new exception("Query failed!", 1);

        $data= array();
        $first= true;
        for($i=0; $row= db_fetch_assoc($sth, $i); $i++)
        {
            $language= isset($row["language"]) ? trim($row["language"]) : "";
            $description= isset($row["description"]) ? trim($row["description"]) : "";
            $surveyintro= isset($row["surveyintro"]) ? trim($row["surveyintro"]) : "";
            $question= isset($row["question"]) ? trim($row["question"]) : "";
            $answer= isset($row["answertext"]) ? trim($row["answertext"]) : "";

            if ($includeMasterData)
            {
                if (!isset($data[$row["surveyid"]]))
                {
                    $surveyRow= array("surveyid" => $row["surveyid"], "question" => trim($row["question"]), "languages" => array(), "startdate" => $row["startdate"],
                        "surveytitle" => $row["surveytitle"], "surveyname" => $row["surveyname"], "stopdate" => $row["stopdate"], "runstat" => $row["runstat"],
                        "employee" => $row["employee"], "dontshow" => $row["dontshow"], "inc_balances" => $row["inc_balances"]);
                    modifyMasterData($surveyRow);
                    $data[$row["surveyid"]]= $surveyRow;
                }
                if ($question != "" || $answer != "")
                {
                    if (!isset($data[$row["surveyid"]]["languages"][$row["language"]]))
                        $data[$row["surveyid"]]["languages"][$row["language"]]= array("language" => $language, "description" => $description, "surveyintro" => $surveyintro,
                            "answers" => array(), "question" => $question);
                    if (!$isMarketing && isset($row["answerid"]))
                        $data[$row["surveyid"]]["languages"][$row["language"]]["answers"][]= array("id" => $row["answerid"], "text" => $answer);
                }
            }
            else
            {
                if (!isset($data[$row["language"]]))
                    $data[$row["language"]]= array("language" => $language, "description" => $description, "surveyintro" => $surveyintro,
                        "title" => $language == "en_US" ? "" : $description, "surveys" => array());
                if ($question != "" || $answer != "")
                {
                    if (!isset($data[$row["language"]]["surveys"][$row["surveyid"]]))
                    {
                        $surveyRow= array("surveyid" => $row["surveyid"], "question" => $question, "answers" => array());
                        $data[$row["language"]]["surveys"][$row["surveyid"]]= $surveyRow;
                    }
                    if (!$isMarketing && isset($row["answerid"]))
                        $data[$row["language"]]["surveys"][$row["surveyid"]]["answers"][]= array("id" => $row["answerid"], "text" => $answer);
                }
            }
        }

        $data= array_values($data);
        for($i=0, $iCount= count($data); $i != $iCount; $i++)
        {
            if ($includeMasterData)
                $data[$i]["languages"]= array_values($data[$i]["languages"]);
            else
                $data[$i]["surveys"]= array_values($data[$i]["surveys"]);
        }
    }
    catch(exception $e)
    {
        return array("error" => array($e->getMessage()), "code" => $e->getCode(), "sql" => $sqls);
    }
    return array("error" => array(), "code" => 0, "sql" => $sqls, "data" => $data);
}

/**
 * function readNew($isMarketing, $dbh, $Cu)
 * This is called on the edit page when the surveyid is zero to create.  It needs only to get the languages from the database.
 *
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union code uppercase (from high above; it's like manna)
 * @return array --
 *      $error-- array of zero or one errors
 *      $code-- zero if there is an error; non-zero if there are no errors.
 *      $sql-- array of sqls used
 *      $questionData-- array of languages and an empty string for the question in that language.
 */
function readNew($dbh, $Cu)
{
    try
    {
        $sql= "select l.description, l.language, '' as question from culanguage l order by l.language";
        $sqls[]= $sql;

        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("Survey question query failed!", 3);
        $questionData= array();
        for($i=0; $row= db_fetch_assoc($sth, $i); $i++)
        {
            $row["exists"]= false;
            $questionData[]= $row;
        }
    }
    catch(exception $e)
    {
        return array("sql" => $sqls, "error" => array($e->getMessage()), "code" => $e->getCode());
    }
    return array("sql" => $sqls, "error" => array(), "code" => 0, "questionData" => $questionData);
}

/**
 * function modifyMasterData(&$row)
 * Changes the boolean values to actual boolean values for display.
 *
 * @param {} $row -- Represents the master row and is directly updated.
 */
function modifyMasterData(&$row)
{
    $row["isDraft"]= intval($row["runstat"]) == 0;
    $row["affectsEmployees"]= trim($row["employee"]) == "Y";
    $row["isPopup"]= trim($row["inc_balances"]) != "Y";
    $row["dontShow"]= trim($row["dontshow"]) == "Y";
    unset($row["runstat"]);
    unset($row["employee"]);
    unset($row["inc_balances"]);
    unset($row["dontshow"]);
}

/**
 * function readEdit($isMarketing, $dbh, $Cu, $includeMainInfo=true, $includeHasResults=true)
 * Reads the data of a surveyid dependent on $isMarketing
 *
 * @param boolean $isMarketing -- Affects the surveytype limitation.  If false, queries for the answers to the survey.  Affects what is returned.
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union code uppercase (from high above; it's like manna)
 * @return array --
 *      $error-- array of zero or one errors
 *      $code-- zero if there is an error; non-zero if there are no errors.
 *      $sql-- array of sqls used
 *      $questionData-- array of languages and the question in that language.  If there isn't a record for the question in that language, then the question will be an empty string.
 *      $masterData-- The top-level data for the surveyid.
 *      $hasResults-- If $isMarketing, then this is if the survey is not a draft.  If !$isMarketing, then this is if there is a record in the cusurveysays table for the survey.
 *      $answerData-- Array of the possible answers (only for !$isMarketing)
 *      $nextAnswerRowid-- the next rowid when creating a new answer (only for !$isMarketing)
 */
function readEdit($isMarketing, $dbh, $Cu, $surveyid, $includeMainInfo=true, $includeHasResults=true)
{
    $sqls= array();
    $surveyid= trim($surveyid) ? intval($surveyid) : 0;

    $hasResults= false;
    try
    {
        if ($includeMainInfo)
        {
            $sql= getMasterSubquery($Cu, $isMarketing, "single", $surveyid);
            $sqls[]= $sql;

            $sth= db_query($sql, $dbh);
            if (!$sth)
                throw new exception("Survey master query failed.", 101);
            if (db_num_rows($sth) == 0)
                throw new exception("Survey master record not found.", 102);

            $masterData= db_fetch_assoc($sth, 0);
            if ($includeHasResults && $isMarketing)
                $hasResults= intval($masterData["runstat"]) != 0;
            modifyMasterData($masterData);
        }

        $sql= "select trim(l.description) as description, trim(l.language) as language, trim(coalesce(q.question, '')) as question, q.cu from culanguage l
        left join cusurveyquest q on l.language = q.language and q.cu= '$Cu' and q.surveyid= '$surveyid' order by l.language";
        $sqls[]= $sql;

        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("Survey question query failed.", 103);

        $hasSpanish=false;
        $hasPolish=false;
        $questionData= array();
        for($i=0; $row= db_fetch_assoc($sth, $i); $i++)
        {
            $row["exists"]= isset($row["cu"]);
            unset($row["cu"]);

            switch(trim($row["language"]))
            {
                case "pl_US":
                    $hasPolish= $row["exists"] && $row["question"] != "";
                    break;
                case "es_US":
                    $hasSpanish= $row["exists"] && $row["question"] != "";
                    break;
            }
            $questionData[]= $row;
        }


        if (!$isMarketing)
        {
            $sql= "select d.answerid, d.answertext, d.language from cusurveydetail d where d.surveyid='$surveyid' and cu='$Cu' order by d.answerid";
            $sqls[]= $sql;

            $sth= db_query($sql, $dbh);
            if (!$sth)
                throw new exception("Survey detail query failed.", 104);

            $answerData= array();
            for($i=0; $row= db_fetch_assoc($sth, $i); $i++)
            {
                $answerid= intval($row["answerid"]);
                $text= trim($row["answertext"]);
                if (!isset($answerData[$answerid]))
                    $answerData[$answerid]= array("rowid" => $i+1, "polish" => "", "spanish" => "", "english" => "");
                switch($row["language"])
                {
                    case "pl_US":
                        $answerData[$answerid]["polish"]= $text;
                        $hasPolish= true;
                        break;
                    case "es_US":
                        $answerData[$answerid]["spanish"]= $text;
                        $hasSpanish= true;
                        break;
                    case "en_US":
                    case "":
                        $answerData[$answerid]["english"]= $text;
                        break;
                    default:
                        throw new exception("Only English, Polish, and Spanish are allowed.", 6);
                }
            }
            $nextAnswerRowid= $i+1;

            if ($includeHasResults)
            {
                $sql= "select count(*) from cusurveysays s where s.surveyid= $surveyid and cu='$Cu'";
                $sth= db_query($sql, $dbh);
                if (!$sth)
                    throw new exception("Count query failed!", 5);
                $hasResults= intval(db_fetch_array($sth, 0)[0]) > 0;
            }
        }
    }
    catch (Exception $e)
    {
        return array("sql" => $sqls, "error" => array($e->getCode() >= 200 ? "Display failed with code: " . $e->getCode() . "." : $e->getMessage()), "code" => $e->getCode());
    }
    $returnArray= array("sql" => $sqls, "error" => array(), "code" => 0, "masterData" => $masterData, "questionData" => $questionData, "hasResults" => $includeHasResults && $hasResults,
        "hasSpanish" => $hasSpanish, "hasPolish" => $hasPolish);
    if (!$isMarketing)
    {
        $returnArray["answerData"]= array_values($answerData);
        $returnArray["nextAnswerRowid"]= $nextAnswerRowid;
    }
    return $returnArray;
}

/**
 * function delete($isMarketing, $dbh, $Cu)
 * Deletes the survey or marketing message
 *
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union in uppercase
 * @return array --
 *      $error-- array of zero or one errors
 *      $code-- zero if there is an error; non-zero if there are no errors.
 *      $sql-- array of sqls used
 */
function delete($dbh, $Cu, $surveyid)
{
    $sqls= array();

    $surveyid= trim($surveyid) ? intval($surveyid) : 0;

    if (!db_work ($dbh, HOMECU_WORK_BEGIN))
        throw new exception("begin query failed.", 1);

    try
    {
        $sql= "delete from cusurveymaster where surveyid='$surveyid' and cu='$Cu'";
        $sqls[]= $sql;
        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("cusurveymaster delete failed.", 102);

        $sql= "delete from cusurveydetail where surveyid='$surveyid' and cu='$Cu'";
        $sqls[]= $sql;
        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("cusurveydetail delete failed.", 103);

        $sql= "delete from cusurveysays where surveyid='$surveyid' and cu='$Cu'";
        $sqls[]= $sql;
        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("cusurveysays delete failed.", 104);

        $sql= "delete from cusurveyquest where surveyid='$surveyid' and cu='$Cu'";
        $sqls[]= $sql;
        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("cusurveyquest delete failed.", 105);

        if (!db_work($dbh, HOMECU_WORK_COMMIT))
            throw new exception("commit work failed.", 106);
    }
    catch(exception $e)
    {
        if ($e->getCode() >= 100)
            db_work($dbh, HOMECU_WORK_ROLLBACK); // Got greater problems if this fails.

        return array("code" => $e->getCode(), "error" => array("Delete failed with code: " . $e->getCode() . "."), "sql" => $sqls);
    }
    return array("code" => 0, "error" => array(), "sql" => $sqls);
}

/**
 * function mainCmp($a, $b)
 * Compare function for sorting main read.  Sorts the status first and then by the $startdate and then by the $stopdate.
 *
 * @param {} $a -- the first one to compare
 * @param {} $b -- the second one to compare
 * @return If > 0, $a is sorted higher than $b.  If < 0, $b is sorted higher than $a.  Otherwise, no resort happens.
 */
function mainCmp($a, $b)
{
    $statusPriority= array_flip(array("", "Invalid", "Draft", "Present", "Future", "Past"));
    $statusCompare= intval($statusPriority[$b["status"]]) - intval($statusPriority[$a["status"]]);
    if ($statusCompare != 0)
        return $statusCompare;
    $startCompare= $a["startMs"] - $b["startMs"];
    if ($startCompare != 0)
        return $startCompare;
    $stopCompare= $a["stopMs"] - $b["stopMs"];
    return $stopCompare;
}

/**
 * function readMain($isMarketing, $dbh, $Cu)
 * This function reads for the main page
 *
 * @param boolean $isMarketing -- affects the surveytype limitation
 * @param integer $dbh -- the database connection
 * @param string $Cu -- the credit union in uppercase
 * @return array --
 *      $error-- array of zero or one errors
 *      $code-- zero if there is an error; non-zero if there are no errors.
 *      $sql-- array of sqls used
 *      $record-- array of all the surveyids that are limited by surveytype.
 */
function readMain($isMarketing, $dbh, $Cu)
{
    $sqls= array();
    try
    {
        $sql= getMasterSubquery($Cu, $isMarketing);
        $sqls[]= $sql;

        $sth= db_query($sql, $dbh);
        if (!$sth)
            throw new exception("Read query failed!", 1);
        $records= array();
        $now= new DateTime();
        $now= DateTime::createFromFormat("Y-m-d", $now->format("Y-m-d"));
        for($i=0; $record= db_fetch_assoc($sth, $i); $i++)
        {
            $start= DateTime::createFromFormat("Y-m-d", $record["startdate"]);
            $stop= DateTime::createFromFormat("Y-m-d", $record["stopdate"]);

            $record["startMs"]= intval($start->format("U"));
            $record["stopMs"]= intval($start->format("U"));

            $record["range"]= $start->format("m/d/y") . " - " . $stop->format("m/d/y");

            if ($record["runstat"] == 0)
                $record["status"]= "Draft";
            else if ($stop < $start)
                $record["status"]= "Invalid";
            else if ($now < $start)
                $record["status"]= "Future";
            else if ($now > $stop)
                $record["status"]= "Past";
            else
                $record["status"]= "Present";

            $record["surveyType"]= $record["inc_balances"] == "Y" ? "Balances" : "Popup";

            unset($record["runstat"]);
            unset($record["datestat"]);
            unset($record["inc_balances"]);
            $records[]= $record;
        }

        // Sort:
        usort($records, "mainCmp");
        $sortid= 1;
        for($i=0, $length=count($records); $i!= $length; $i++)
        {
            $records[$i]["sortid"]= $sortid++;
            unset($records[$i]["startMs"]);
            unset($records[$i]["stopMs"]);
        }
    }
    catch(exception $e)
    {
        return array("code" => $e->getCode(), "error" => array($e->getMessage()), "sql" => $sqls);
    }
    return array("code" => 0, "error" => array(), "record" => $records, "sql" => $sqls);
}