gl/includes/db/gl_db_trans.inc

   1 <?php
   2
   3 //--------------------------------------------------------------------------------
   4
   5 // Base function for adding a GL transaction
   6 // $date_ is display date (non-sql)
   7 // $amount is in $currency currency
   8 // if $currency is not set, then defaults to no conversion
   9
  10 function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension2, $memo_,
  11         $amount, $currency=null, $person_type_id=null, $person_id=null, $err_msg="")
  12 {
  13         $date = date2sql($date_);
  14         if ($currency != null)
  15                 $amount_in_home_currency = to_home_currency($amount, $currency, $date_);
  16         else
  17                 $amount_in_home_currency = $amount;
  18         if ($dimension == null || $dimension < 0)
  19                 $dimension = 0;
  20         if ($dimension2 == null || $dimension2 < 0)
  21                 $dimension2 = 0;
  22         $sql = "INSERT INTO ".TB_PREF."gl_trans ( type, type_no, tran_date,
  23                 account, dimension_id, dimension2_id, memo_, amount";
  24
  25         if ($person_type_id != null)
  26                 $sql .= ", person_type_id, person_id";
  27
  28         $sql .= ") ";
  29
  30         $sql .= "VALUES ($type, $trans_id, '$date',
  31                 $account, $dimension, $dimension2, '$memo_', $amount_in_home_currency";
  32
  33         if ($person_type_id != null)
  34                 $sql .= ", $person_type_id, '$person_id'";
  35
  36         $sql .= ") ";
  37
  38         if ($err_msg == "")
  39                 $err_msg = "The GL transaction could not be inserted";
  40
  41         return db_query($sql, $err_msg);
  42 }
  43
  44 //--------------------------------------------------------------------------------
  45
  46 // GL Trans for standard costing, always home currency regardless of person
  47 // $date_ is display date (non-sql)
  48 // $amount is in HOME currency
  49
  50 function add_gl_trans_std_cost($type, $trans_id, $date_, $account, $dimension, $dimension2,
  51         $memo_, $amount, $person_type_id=null, $person_id=null, $err_msg="")
  52 {
  53         if ($amount != 0)
  54                 return add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension2, $memo_,
  55                         $amount, null, $person_type_id, $person_id, $err_msg);
  56         else
  57                 return null;
  58 }
  59
  60 //--------------------------------------------------------------------------------
  61
  62 function get_gl_transactions($from_date, $to_date, $trans_no=0,
  63         $account=null, $dimension=0, $dimension2=0, $filter_type=null)
  64 {
  65         $from = date2sql($from_date);
  66         $to = date2sql($to_date);
  67
  68         $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, ".TB_PREF."chart_master
  69                 WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account
  70                 AND tran_date >= '$from'
  71                 AND tran_date <= '$to'";
  72         if ($trans_no > 0)
  73                 $sql .= " AND ".TB_PREF."gl_trans.type_no LIKE '%$trans_no'";
  74
  75         if ($account != null)
  76                 $sql .= " AND ".TB_PREF."gl_trans.account = $account";
  77
  78         if ($dimension > 0)
  79                 $sql .= " AND ".TB_PREF."gl_trans.dimension_id = $dimension";
  80
  81         if ($dimension2 > 0)
  82                 $sql .= " AND ".TB_PREF."gl_trans.dimension2_id = $dimension2";
  83
  84         if ($filter_type != null AND is_numeric($filter_type))
  85                 $sql .= " AND ".TB_PREF."gl_trans.type= $filter_type";
  86
  87         $sql .= " ORDER BY tran_date";
  88
  89         return db_query($sql, "The transactions for could not be retrieved");
  90 }
  91
  92
  93 //--------------------------------------------------------------------------------
  94
  95 function get_gl_trans($type, $trans_id)
  96 {
  97         $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, ".TB_PREF."chart_master
  98                 WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account
  99                 AND ".TB_PREF."gl_trans.type=$type AND ".TB_PREF."gl_trans.type_no=$trans_id";
 100
 101         return db_query($sql, "The gl transactions could not be retrieved");
 102 }
 103
 104 //--------------------------------------------------------------------------------
 105
 106 function get_gl_balance_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0)
 107 {
 108         $from = date2sql($from_date);
 109         $to = date2sql($to_date);
 110
 111     $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans
 112                 WHERE account='" . $account . "'";
 113         if ($from_date != "")
 114                 $sql .= "  AND tran_date > '$from'";
 115         if ($to_date != "")
 116                 $sql .= "  AND tran_date < '$to'";
 117         if ($dimension > 0)
 118                 $sql .= " AND dimension_id = $dimension";
 119         if ($dimension2 > 0)
 120                 $sql .= " AND dimension2_id = $dimension2";
 121
 122         $result = db_query($sql, "The starting balance for account $account could not be calculated");
 123
 124         $row = db_fetch_row($result);
 125         return $row[0];
 126 }
 127
 128 //--------------------------------------------------------------------------------
 129
 130 function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0)
 131 {
 132         $from = date2sql($from_date);
 133         $to = date2sql($to_date);
 134
 135     $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans
 136                 WHERE account='" . $account . "'";
 137         if ($from_date != "")
 138                 $sql .= " AND tran_date >= '$from'";
 139         if ($to_date != "")
 140                 $sql .= " AND tran_date <= '$to'";
 141         if ($dimension > 0)
 142                 $sql .= " AND dimension_id = $dimension";
 143         if ($dimension2 > 0)
 144                 $sql .= " AND dimension2_id = $dimension2";
 145
 146         $result = db_query($sql, "Transactions for account $account could not be calculated");
 147
 148         $row = db_fetch_row($result);
 149         return $row[0];
 150 }
 151
 152 //--------------------------------------------------------------------------------
 153
 154 function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $dimension2=0)
 155 {
 156
 157         $from = date2sql($from_date);
 158         $to = date2sql($to_date);
 159
 160         $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans
 161                 WHERE account=$account ";
 162         if ($from_date != "")
 163                 $sql .= " AND tran_date >= '$from' ";
 164         if ($to_date != "")
 165                 $sql .= " AND tran_date <= '$to' ";
 166         if ($dimension > 0)
 167                 $sql .= " AND dimension_id = $dimension";
 168         if ($dimension2 > 0)
 169                 $sql .= " AND dimension2_id = $dimension2";
 170         $result = db_query($sql,"No budget accounts were returned");
 171
 172         $row = db_fetch_row($result);
 173         return $row[0];
 174 }
 175
 176 //--------------------------------------------------------------------------------
 177
 178 function add_journal_entries($items, $date_, $ref, $reverse, $memo_=null)
 179 {
 180         begin_transaction();
 181
 182         $trans_type = systypes::journal_entry();
 183         $trans_id = get_next_trans_no($trans_type);
 184
 185         foreach ($items as $journal_item)
 186         {
 187                 $is_bank_to = is_bank_account($journal_item->code_id);
 188
 189                 add_gl_trans($trans_type, $trans_id, $date_, $journal_item->code_id,
 190                         $journal_item->dimension_id, $journal_item->dimension2_id,
 191                         $journal_item->reference, $journal_item->amount);
 192         if ($is_bank_to)
 193         {
 194                 add_bank_trans($trans_type, $trans_id, $journal_item->code_id, $ref,
 195                         $date_, 3, $journal_item->amount,
 196                         0, "", get_company_currency(),
 197                         "Cannot insert a destination bank transaction");
 198         }
 199         }
 200
 201         add_comments($trans_type, $trans_id, $date_, $memo_);
 202
 203         references::save($trans_type, $trans_id, $ref);
 204
 205         if ($reverse)
 206         {
 207
 208         $reversingDate = date(user_date_display(),
 209                 Mktime(0,0,0,get_month($date_)+1,1,get_year($date_)));
 210
 211         $trans_id_reverse = get_next_trans_no($trans_type);
 212
 213         foreach ($items as $journal_item)
 214         {
 215                         $is_bank_to = is_bank_account($journal_item->code_id);
 216
 217                 add_gl_trans($trans_type, $trans_id_reverse, $reversingDate,
 218                         $journal_item->code_id, $journal_item->dimension_id, $journal_item->dimension2_id,
 219                         $journal_item->reference, -$journal_item->amount);
 220                 if ($is_bank_to)
 221                 {
 222                         add_bank_trans($trans_type, $trans_id_reverse, $journal_item->code_id, $ref,
 223                                 $reversingDate, 3, $journal_item->amount,
 224                                 0, "", get_company_currency(),
 225                                 "Cannot insert a destination bank transaction");
 226                 }
 227         }
 228
 229         add_comments($trans_type, $trans_id_reverse, $reversingDate, $memo_);
 230
 231         references::save($trans_type, $trans_id_reverse, $ref);
 232         }
 233
 234         commit_transaction();
 235
 236         return $trans_id;
 237 }
 238
 239 //--------------------------------------------------------------------------------------------------
 240
 241 function exists_gl_trans($type, $trans_id)
 242 {
 243         $sql = "SELECT type_no FROM ".TB_PREF."gl_trans WHERE type=$type AND type_no=$trans_id";
 244         $result = db_query($sql, "Cannot retreive a gl transaction");
 245
 246     return (db_num_rows($result) > 0);
 247 }
 248
 249 //--------------------------------------------------------------------------------------------------
 250
 251 function void_gl_trans($type, $trans_id, $nested=false)
 252 {
 253         if (!$nested)
 254                 begin_transaction();
 255
 256         $sql = "UPDATE ".TB_PREF."gl_trans SET amount=0 WHERE type=$type AND type_no=$trans_id";
 257
 258         db_query($sql, "could not void gl transactions for type=$type and trans_no=$trans_id");
 259
 260         if (!$nested)
 261                 commit_transaction();
 262 }
 263
 264 //--------------------------------------------------------------------------------------------------
 265
 266 ?>