functions.php

<?php

function get_message($mid) {

    $messages = array(
        0 => "Valitsemasi paikat on lisätty onnistuneesti. Muistathan lisätä käyttäjäkohtaiset tiedot!",
        1 => "Tietojen muokkauksessa tapahtui virhe tai et tehnyt mitään muutoksia merkintään. Tarkistathan tietueen sisällön jota olit muokkaamassa. "
    );
    return $messages[$mid];
}

function get_users_by_leader($db, $uid) {
    $rows = $db->fetchAll("
        SELECT USER.ID, USER.EDITED, USER.ZIP, USER.PHONE, USER.NICK, USER.EMAIL, 
        USER.ADDRESS, USER.FEMALE, USER.NAME, USER.TOWN, USER.PAID, USER.PASSIVE, 
        USER.TYPE, USER.DISCOUNT, USER.CODE, USER.PRICE, USER.BIRTHYEAR FROM USERGROUP_USER
        LEFT JOIN USER 
        ON USERGROUP_USER.members_ID = USER.ID
        WHERE groups_ID = (SELECT ID FROM USERGROUP WHERE LEADER_ID = '$uid' LIMIT 1)");
   if ( count($rows) != 0) {
       return $rows;
   } else {
       return NULL;
   }
}

function get_group_users($db, $gid) {
    $rows = $db->fetchAll("
        SELECT USER.ID, USER.EDITED, USER.ZIP, USER.PHONE, USER.NICK, USER.EMAIL, 
        USER.ADDRESS, USER.FEMALE, USER.NAME, USER.TOWN, USER.PAID USER.PASSIVE, 
        USER.TYPE, USER.DISCOUNT, USER.CODE, USER.PRICE FROM USERGROUP_USER
        LEFT JOIN USER 
        ON USERGROUP_USER.members_ID = USER.ID
        WHERE groups_ID = '$gid'
        ORDER BY USER.CREATED");
    if ( count($rows) != 0) {
        return $rows;
    } else {
        return NULL;
    }
}

function get_user_by_id($db, $uid) {
    $rows = $db->fetchAll("
        SELECT USER.ID, USER.EDITED, USER.ZIP, USER.PHONE, USER.NICK, USER.EMAIL, 
        USER.ADDRESS, USER.FEMALE, USER.NAME, USER.TOWN, USER.PAID, USER.PASSIVE, 
        USER.TYPE, USER.DISCOUNT, USER.CODE, USER.PRICE,  USER.BIRTHYEAR FROM USER
        WHERE USER.ID = '$uid'
        ORDER BY USER.CREATED");
    if ( count($rows) != 0) {
        return $rows;
    } else {
        return NULL;
    }
}

function get_user_type($db, $uid) {
    $row = $db->fetchRow("SELECT TYPE FROM USER WHERE ID = '$uid'");
    $types = array(
        0 => "Tavallinen konepaikka", 
        1 => "Kävijä",
        2 => "PRO-konepaikka",
        10 => "CoreOrganisaattori",
        11 => "Organisaattori",
        12 => "Yhteistyökumppani"
    );

    if ($types[$row['TYPE']]) {
        return $types[$row['TYPE']];
    } else {
        return NULL;
    }
}

function translate_sex($sexid) {
    $sex = array("Mies", "Nainen");
    return $sex[$sexid];    
}

function translate_paid($paystatus, $withimg=FALSE) {
    if($withimg) {
        $status = array(
            0 => "<img src='/template/img/thumbdown.png'>",
            1 => "<img src='/template/img/thumbup.png'>",
            2 => "<img src='/template/img/thumbdown.png'>"
        );
    } else {
        $status = array(
            0 => "Ei maksettu",
            1 => "Maksettu",
            2 => "Lasku lähetetty"
        );
    }
    if ($paystatus == NULL) {
        $paystatus = 0;
    }
    return $status[$paystatus];
}


function viitenumero($laskunro){
        $vuosi = 2009;
        $laskunro = sprintf("$vuosi%07d", $laskunro);
        if(strlen($laskunro) > 19) return 0;
        $kertoimet = array('7','3','1','7','3','1','7','3','1','7','3','1','7','3','1','7','3','1','7');
        $tarkiste = 0;
        $j = 0;
        $tmp = $laskunro;
        settype($tmp, "string"); // Muutetaan stringiksi
        for($i=strlen($tmp)-1; $i>-1; $i--){
                $tarkiste = $tarkiste + $kertoimet[$j++] * intval(substr($tmp, $i, 1));
        }
        $tarkiste = ceil($tarkiste / 10) * 10 - $tarkiste;
        $viite = "$laskunro$tarkiste";
        return $viite;
}

function get_leaders_group($db, $uid) {
    $gid = $db->fetchOne("SELECT ID FROM USERGROUP WHERE LEADER_ID = '$uid'"); 
    return $gid;
}

function get_leaders_group_name($db, $uid) {
    $name = $db->fetchOne("SELECT NAME FROM USERGROUP WHERE LEADER_ID = '$uid'");
    return $name;
}

function has_free_place($db, $uid, $type=0) {
    $gidrivi = $db->fetchRow("SELECT ID FROM USERGROUP WHERE LEADER_ID = '$uid'");
    if ($gidrivi) {
        $gid = $gidrivi['ID'];
        $groups_places = $db->fetchOne("SELECT COUNT(PLACE) FROM PLACE WHERE GROUP_ID = '$gid' AND TYPE = $type");
        $paid_places = $db->fetchOne("  SELECT count(USER.PAID) 
                                        FROM `USERGROUP_USER` 
                                        LEFT JOIN USER 
                                        ON USERGROUP_USER.members_ID = USER.ID 
                                        WHERE USERGROUP_USER.groups_ID = '$gid' 
                                        AND USER.PAID = 1
                                        AND USER.TYPE = $type");
        $erotus = $paid_places - $groups_places;
        if ($erotus > 0) {
            return TRUE;
        } else {
            return FALSE;
        }

    } else {
        # user is single user, check only this user's status
        if ( $db->fetchOne("SELECT PAID FROM USER WHERE ID = '$uid' AND TYPE = $type limit 1") == 1) { # IF it's paid
            # and not used yet
            $r = $db->fetchOne("SELECT COUNT(ID) FROM PLACE WHERE USER_ID = '$uid' AND TYPE = $type");
            if($r == 0) {
                return TRUE;
            } else {
                return FALSE;
            }
        } else {
            return FALSE;
        }
    }
}

function get_placetype($db, $place) {
    $place = $db->quote($place);
    $placetype = $db->fetchOne("SELECT TYPE FROM PLACE WHERE PLACE = $place LIMIT 1");
    if($placetype !== FALSE) {
        return $placetype;
    } else {
        return FALSE;
    }

}


function is_group_leader($db, $uid) {
    $gid = $db->fetchOne("SELECT ID FROM USERGROUP WHERE LEADER_ID = '$uid'");
    if ($gid) {
        return $gid;
    } else {
        return FALSE;
    }
}

function note($legend, $text, $class='info') {
    $a .= "<fieldset class='$class'>";
    $a .= "<legend class='$class'>$legend</legend>";
    $a .= $text;
    $a .= "</fieldset>";
    return $a;
}


function free_places($db, $type=0) {
    $total = $db->fetchOne("SELECT count(ID) FROM PLACE WHERE TYPE = $type");
    $paid = $db->fetchOne("SELECT count(ID) FROM USER WHERE TYPE = $type AND PAID = 1");
    return $total - $paid;
}

function total_places($db, $type=0) {
    return $db->fetchOne("SELECT count(ID) FROM PLACE WHERE TYPE = $type");
}



function reserved_tickets($db, $id, $type=0, $isgroup=FALSE) {
    if($isgroup) {
        $id = $db->quote($id);
        $type = $db->quote($type);
        $val = $db->fetchOne("SELECT COUNT(USER.ID) FROM USERGROUP_USER LEFT JOIN USER ON USERGROUP_USER.members_ID = USER.ID WHERE USERGROUP_USER.groups_ID = $id AND USER.TYPE = $type");
    } else {
        $val = $db->fetchOne("SELECT COUNT(ID) FROM USER WHERE ID = $id AND TYPE = $type");
    }
    return $val;
}
function paid_tickets($db, $id, $type=0, $isgroup=FALSE) {
    if($isgroup) {
        $id = $db->quote($id);
        $type = $db->quote($type);
        $val = $db->fetchOne("SELECT COUNT(USER.ID) FROM USERGROUP_USER LEFT JOIN USER ON USERGROUP_USER.members_ID = USER.ID WHERE USERGROUP_USER.groups_ID = $id AND USER.TYPE = $type AND USER.PAID = 1");
    } else {
        $val = $db->fetchOne("SELECT COUNT(ID) FROM USER WHERE ID = $id AND TYPE = $type AND USER.PAID = 1");
    }
    return $val;
}

function unlocked_tickets($db, $id, $type=0, $isgroup=FALSE) {
    if($isgroup) {
        $id = $db->quote($id);
        $type = $db->quote($type);
        $val = $db->fetchOne("SELECT COUNT(USER.ID) FROM USERGROUP_USER LEFT JOIN USER ON USERGROUP_USER.members_ID = USER.ID WHERE USERGROUP_USER.groups_ID = $id AND USER.TYPE = $type AND USER.PAID IS NULL AND USER.PRICE IS NULL");
    } else {
        $val = $db->fetchOne("SELECT COUNT(ID) FROM USER WHERE ID = $id AND TYPE = $type AND USER.PAID = 0 AND PRICE IS NULL");
    }
    return $val;
}

function locked_tickets($db, $id, $isgroup=FALSE) {
    $id = $db->quote($id);
    if($isgroup) {
        $val = $db->fetchOne("  SELECT USER.ID FROM USERGROUP_USER 
                                LEFT JOIN USER 
                                ON USERGROUP_USER.members_ID = USER.ID 
                                WHERE USERGROUP_USER.groups_ID = $id 
                                AND USER.PAID IS NULL 
                                AND USER.PRICE IS NOT NULL");
    } else {
        $val = $db->fetchOne("  SELECT ID FROM USER 
                                WHERE USER.ID = $id
                                AND USER.PRICE IS NOT NULL");
    }
    echo $val;
    if(!$val) {
        return FALSE;
    } else {
        return TRUE;
    }
}

function place_is_free($db, $place) {
    $place = $db->quote($place);
    $val = $db->fetchOne("SELECT ID FROM PLACE WHERE PLACE = $place AND GROUP_ID IS NULL AND USER_ID IS NULL");
    if ($val !== FALSE) {
        return TRUE;
    } else {
        return FALSE;
    }
}

function is_own_place($db, $place, $id, $isgroup) {
    $place = $db->quote($place);
    $r = $db->fetchOne("SELECT GROUP_ID, USER_ID FROM PLACE WHERE PLACE = $place");
    if ( $isgroup === TRUE AND $r['GROUP_ID'] == $id) {
        return TRUE;
    } elseif ( $isgroup === FALSE AND $r['USER_ID'] == $id) {
        return TRUE;
    } else {
        return FALSE;
    }
}

function get_unpaid($db, $id, $isgroup) {
    $id = $db->quote($id);
    if($isgroup) {
        $sum = $db->fetchOne("  SELECT SUM(PRICE) FROM USERGROUP_USER
                                LEFT JOIN USER
                                ON USERGROUP_USER.members_ID = USER.ID
                                WHERE USERGROUP_USER.groups_id = $id
                                AND USER.PAID IS NULL
                                AND USER.PRICE IS NOT NULL");
    } else {
        $sum = $db->fetchOne("  SELECT SUM(PRICE) FROM USER
                                WHERE USER.ID = $id
                                AND USER.PAID IS NULL
                                AND USER.PRICE IS NOT NULL");
    }
    return $sum;
}

function mailaa($to, $subject, $message) {
    $headers .= 'From: Insomnia lippukauppa <laskutus@insomnia.fi>' . "\r\n";
    $headers .= 'Content-type: text/plain; charset="UTF-8"' . "\r\n";
    $headers .= 'BCC: laskutus@insomnia.fi' . "\r\n";
    mail($to, $subject, $message, $headers);

}

function is_in_leaders_group($db, $leaderid, $uid) {
    $gid = get_leaders_group($db, $leaderid);
    $uid = $db->quote($uid);
    if($db->fetchOne("SELECT members_ID FROM USERGROUP_USER WHERE members_ID = $uid AND groups_ID = $gid")) {
        return TRUE;
    } else {
        return FALSE;
    }
    
}

function ticket_is_locked($db, $uid) {
    $uid = $db->quote($uid);
    if($db->fetchOne("SELECT ID FROM USER WHERE ID = $uid AND PRICE IS NOT NULL LIMIT 1")) {
        return TRUE;
    } else {
        return FALSE;
    }
}

function ticket_is_paid($db, $uid) {
    $uid = $db->quote($uid);
    if($db->query("SELECT ID FROM USER WHERE ID = $uid AND PAID IS NULL")) {
        return TRUE;
    } else {
        return FALSE;
    }
}
?>