X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=dimensions%2Fincludes%2Fdimensions_db.inc;h=13c91fd41c3956ad194114d386f95cc1013b9dfa;hb=a3616fe8abf36ba9a2ba9ba63689475b1c8978b8;hp=1a4448de9da584dcbf4c2fccc480bbc8984a869d;hpb=af78fbb535a6fedbc2eb70a26ddc39739be2b986;p=fa-stable.git diff --git a/dimensions/includes/dimensions_db.inc b/dimensions/includes/dimensions_db.inc index 1a4448de..13c91fd4 100644 --- a/dimensions/includes/dimensions_db.inc +++ b/dimensions/includes/dimensions_db.inc @@ -20,9 +20,8 @@ function add_dimension($reference, $name, $type_, $date_, $due_date, $memo_) $sql = "INSERT INTO ".TB_PREF."dimensions (reference, name, type_, date_, due_date) VALUES (".db_escape($reference).", ".db_escape($name).", ".db_escape($type_) - .", ".db_escape($date_).", ".db_escape($due_date).")"; + .", '$date', '$duedate')"; db_query($sql, "could not add dimension"); - $id = db_insert_id(); add_comments(ST_DIMENSION, $id, $date_, $memo_); @@ -43,8 +42,8 @@ function update_dimension($id, $name, $type_, $date_, $due_date, $memo_) $sql = "UPDATE ".TB_PREF."dimensions SET name=".db_escape($name).", type_ = ".db_escape($type_).", - date_=".db_escape($date_).", - due_date=".db_escape($due_date)." + date_='$date', + due_date='$duedate' WHERE id = ".db_escape($id); db_query($sql, "could not update dimension"); @@ -123,10 +122,11 @@ function dimension_has_deposits($id) function dimension_has_payments($id) { - $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE dimension_id = ".db_escape($id); + $sql = "SELECT COUNT(*) FROM ".TB_PREF."gl_trans WHERE dimension_id = ".db_escape($id) + . " OR dimension2_id = ".db_escape($id); $res = db_query($sql, "Transactions could not be calculated"); $row = db_fetch_row($res); - return ($row[0] != 0.0); + return ($row[0] > 0); } function dimension_is_closed($id) @@ -147,8 +147,80 @@ function close_dimension($id) function reopen_dimension($id) { - $sql = "UPDATE ".TB_PREF."dimensions SET closed='0' WHERE id = $id"; + $sql = "UPDATE ".TB_PREF."dimensions SET closed='0' WHERE id = ".db_escape($id); db_query($sql, "could not reopen dimension"); } -?> \ No newline at end of file +//-------------------------------------------------------------------------------------- + +function get_dimension_balance_all($id, $from, $to) +{ + $from = date2sql($from); + $to = date2sql($to); + $sql = "SELECT account, coa.account_name, sum(amount) AS amt + FROM " + .TB_PREF."gl_trans trans," + .TB_PREF."chart_master coa + WHERE + trans.account = coa.account_code + AND (dimension_id = ".db_escape($id)." OR dimension2_id = ".db_escape($id).") + AND tran_date >= '$from' AND tran_date <= '$to' GROUP BY account"; + return db_query($sql, "Transactions could not be calculated"); +} + +//-------------------------------------------------------------------------------------- + +function get_dimension_balance($id, $from, $to) +{ + $id = db_escape($id); + $sql = "SELECT SUM(amount) + FROM ".TB_PREF."gl_trans + WHERE + tran_date >= '" . date2sql($from) . "' + AND tran_date <= '" . date2sql($to) . "' + AND (dimension_id = $id OR dimension2_id = $id)"; + $res = db_query($sql, "Sum of transactions could not be calculated"); + $row = db_fetch_row($res); + + return $row[0]; +} + +//-------------------------------------------------------------------------------------- + +function get_sql_for_search_dimensions($dim, $from, $to, $order='', $type = -1, $open=false, $overdue = false) +{ + $sql = "SELECT dim.id, + dim.reference, + dim.name, + dim.type_, + dim.date_, + dim.due_date, + dim.closed + FROM ".TB_PREF."dimensions as dim WHERE id > 0"; + + if ($order) + { + $sql .= " AND reference LIKE ".db_escape("%". $order . "%"); + } else { + + if ($dim == 1) + $sql .= " AND type_=1"; + + if ($open) + $sql .= " AND closed=0"; + + if ($type > 0) + $sql .= " AND type_=".db_escape($type); + + if ($overdue) + { + $today = date2sql(Today()); + + $sql .= " AND due_date < '$today'"; + } + + $sql .= " AND date_ >= '" . date2sql($from) . "' + AND date_ <= '" . date2sql($to) . "'"; + } + return $sql; +}