/**
* getRechnungen: Rechnungen auslesen
*
* @return Array()
*/
function getRechnungen($von, $bis, $tz, $istsoll)
{
$tax = getTax();
$sql = "SELECT ar.id,invnumber,amount,netamount,(amount-netamount) as mwst,ar.taxzone_id,name,customer.id as cid,country,ustid,ar.transdate ";
$sql .= "from ar left join customer on customer.id=customer_id where ";
if ($tz == 1) {
$sqlar = "ar.taxzone_id=1 and ";
} else {
if ($tz == 2) {
$sqlar = "ar.taxzone_id=2 and ";
} else {
if ($tz == 3) {
$sqlar = "ar.taxzone_id=3 and ";
} else {
if ($tz == "0") {
$sqlar = "ar.taxzone_id=0 and ";
}
}
}
}
if ($istsoll == 1) {
$bezug = 'datepaid';
} else {
$bezug = 'transdate';
}
$sqlar .= $bezug . " between '" . $von . "' and '" . $bis . "'";
//echo $sqlar;
$steuern = getSteuern($sqlar);
//$rs = $_SESSION["db"]->getAll($sql.$sqlar." order by invnumber",DB_FETCHMODE_ASSOC);
$rs = $_SESSION["db"]->getAll($sql . $sqlar . " order by transdate", DB_FETCHMODE_ASSOC);
if (!$rs) {
return false;
}
foreach ($rs as $row) {
$rechng[$row["id"]] = $row;
}
$steuersatz = array();
foreach ($steuern as $row) {
if ($rechng[$row["trans_id"]]["amount"] != $rechng[$row["trans_id"]]["netamount"]) {
$rechng[$row["trans_id"]][$tax[$row["chart_id"]]] = $row["amount"];
if (!in_array($tax[$row["chart_id"]], $steuersatz)) {
$steuersatz[] = $tax[$row["chart_id"]];
}
}
}
return array($rechng, $steuersatz);
}
function erp($db, $error, $divStd, $divVerm, $doordnr, $docustnr, $preordnr, $precustnr, $invbrne, $SHOPincl, $OEinsPart, $lager, $pricegroup, $staffel, $parent, $ERPusrID)
{
$this->db = $db;
$this->error = $error;
$this->pricegroup = is_numeric($pricegroup) ? $pricegroup : 0;
$this->staffel = $staffel;
$this->parent = $parent;
$this->employee_id = $ERPusrID;
$this->divStd = $divStd;
$this->divVerm = $divVerm;
$this->doordnr = $doordnr;
$this->preordnr = $preordnr;
$this->docustnr = $docustnr;
$this->precustnr = $precustnr;
$this->INVnetto = $invbrne == 1 ? true : false;
$this->SHOPincl = $SHOPincl == 1 ? true : false;
$this->OEinsPart = $OEinsPart == 1 ? true : false;
$this->lager = $lager ? $lager : 1;
$tmp = getTax($this->db);
$this->TAX = $tmp['TAX'];
$this->Bugru = $tmp['Bugru'];
if ($lager > 1) {
$sql = "SELECT warehouse_id from bin where id = " . $this->lager;
$rs = $this->db->getOne($sql);
if ($rs['warehouse_id'] > 0) {
$this->warehouse_id = $rs['warehouse_id'];
$sql = "SELECT id from transfer_type WHERE direction = 'in' and description = 'stock'";
$rs = $this->db->getOne($sql);
$this->transtype = $rs['id'];
} else {
$this->lager = 1;
}
}
$sql = "SELECT * FROM custom_variable_configs WHERE name = 'pepperkunde'";
$rs = $this->db->getOne($sql);
if (isset($rs['id'])) {
$this->cvarid = $rs['id'];
} else {
$this->cvarid = 0;
$this->error->write('erplib', 'Benutzerdefinierte Variable pepperkunde fehlt!');
}
$this->getcurr();
}
// auction price - tax
$unitpriceincl = vat($paysubtotal);
// auction price & tax
$subtotal = $payvalue;
// total invoice - tax
$totalinc = vat($payvalue);
// total invoice & tax
}
}
$totalvat = $totalinc - $subtotal;
$unitpriceincl = $totalinc / $data['qty'];
$unitexcl = $subtotal / $data['qty'];
// auction specific details
$template->assign_vars(array('AUCTION_TITLE' => strtoupper($title), 'ITEM_QUANTITY' => $data['qty'], 'UNIT_PRICE' => $system->print_money($unitexcl, true, false), 'UNIT_PRICE_WITH_TAX' => $system->print_money($unitpriceincl, true, false), 'TOTAL' => $system->print_money($subtotal, true, false), 'TOTAL_WITH_TAX' => $system->print_money($totalinc, true, false)));
} else {
$seller = getSeller($user->user_data['id']);
// used as user: ??
$vat = getTax(true, $seller['country']);
$winner_address = '';
$data['shipping_terms'] = '';
$shipping_cost = 0;
$title = $system->SETTINGS['sitename'] . ' - ' . $MSG['766'] . '#' . $data['id'];
$payvalue = $data['total'];
// create fee data ready for template & get totals
$totals = setfeetemplate($data);
// fee specific details
$template->assign_vars(array('TOTAL' => $system->print_money($totals[1], true, false), 'TOTAL_WITH_TAX' => $system->print_money($totals[0], true, false)));
}
$template->assign_vars(array('DOCDIR' => $DOCDIR, 'LOGO' => $system->SETTINGS['siteurl'] . 'themes/' . $system->SETTINGS['theme'] . '/' . $system->SETTINGS['logo'], 'CHARSET' => $CHARSET, 'LANGUAGE' => $language, 'SENDER' => $seller['nick'], 'WINNER_NICK' => $winner['nick'], 'WINNER_ADDRESS' => $winner_address, 'AUCTION_ID' => $data['auc_id'], 'SHIPPING_METHOD' => empty($data['shipping_terms']) ? strtoupper($MSG['000']) : $data['shipping_terms'], 'INVOICE_DATE' => gmdate('d/m/Y', $data['date'] + $system->tdiff), 'SALE_ID' => ($auction ? 'AUC' : 'FEE') . $data['id'], 'TAX' => $vat . '%', 'SHIPPING_COST' => $system->print_money($shipping_cost, true, false), 'VAT_TOTAL' => $system->print_money($totalvat, true, false), 'TOTAL_SUM' => $system->print_money($payvalue, true, false), 'YELLOW_LINE' => $system->SETTINGS['invoice_yellow_line'], 'THANKYOU' => $system->SETTINGS['invoice_thankyou'], 'B_INVOICE' => true, 'B_IS_AUCTION' => $auction));
$template->set_filenames(array('body' => 'order_invoice.tpl'));
$template->display('body');
$_SESSION['message'] = "Added {$_POST['qty']} units of {$code} to stock!!Ne stock is {$new}";
header("location:/views/purchase.php");
} else {
$_SESSION['message'] = "All fields marked * are mandatory!!!";
}
} else {
if (strlen($_POST['no']) && strlen($_POST['code']) && strlen($_POST['iname']) && strlen($_POST['qty']) && strlen($_POST['pp']) && strlen($_POST['profit']) && strlen($_POST['from'])) {
$code = strtolower($_POST['code']);
$pp = $_POST['pp'];
if ($_POST['profitmode'] == '1') {
$unitPrice = round($pp + $_POST['profit'], 2);
} else {
$unitPrice = round($pp + $pp * $_POST['profit'] / 100, 2);
}
$con->query("INSERT INTO item SET \n\t\t\tcode='{$code}',\n\t\t\tname='{$_POST['iname']}',\n\t\t\tmrp='{$_POST['mrp']}',\n\t\t\tunitPrice='{$unitPrice}',\n\t\t\trateOfTax='{$_POST['rot']}',\n\t\t\tpurchasingPrice='{$pp}',\n\t\t\tprofit='{$_POST['profit']}',\n\t\t\tprofitMode='{$_POST['profitmode']}',\n\t\t\ttotalStock='{$_POST['qty']}',\n\t\t\topeningStock='{$_POST['qty']}'");
$tax = getTax($_POST['rot'], $pp);
$con->query("INSERT INTO purchase SET\n\t\t\tinv_no='{$_POST['no']}',\n\t\t\tcode='{$code}',\n\t\t\tname='{$_POST['iname']}',\n\t\t\tqty='{$_POST['qty']}',\n\t\t\trot='{$_POST['rot']}',\n\t\t\tpp='{$pp}',\n\t\t\ttaxAmt='{$tax}',\n\t\t\t`from`='{$_POST['from']}',\n\t\t\tdate='{$_POST['date']}'");
$_SESSION['message'] = "Added {$_POST['qty']} units of {$code} to stock!!New stock is {$_POST['qty']}";
header("location:/views/purchase.php");
} else {
$_SESSION['message'] = "All fields marked * are mandatory!!!";
header("location:/views/purchase.php");
}
}
function getTax($rot, $pp)
{
include "{$_SERVER['DOCUMENT_ROOT']}/config/config.php";
if ($rot == 1) {
$rot = $tax1;
} else {
if ($rot == 2) {
<?php
require "inc/stdLib.php";
include 'inc/katalog.php';
$link = "";
$menu = $_SESSION['menu'];
if ($_POST['ok']) {
$artikel = getArtikel($_POST);
$tax = getTax();
$vorlage = prepTex();
$lastPG = '';
if (file_exists('tmp/katalog.pdf')) {
unlink('tmp/katalog.pdf');
}
if (file_exists('tmp/katalog.tex')) {
unlink('tmp/katalog.tex');
}
if (file_exists('tmp/tabelle.tex')) {
unlink('tmp/tabelle.tex');
}
$f = fopen('tmp/katalog.tex', 'w');
$rc = fputs($f, $vorlage['pre']);
$suche = array('&', '_', '"', '!', '#', '%', '(', ')');
$ersetze = array('\\&', '\\_', '\\"', ' : ', '\\#', '\\%', '\\{', '\\}');
if ($artikel) {
foreach ($artikel as $part) {
$line = $vorlage['artikel'];
if ($lastPG != $part['partsgroup']) {
$lastPG = $part['partsgroup'];
$val = str_replace($suche, $ersetze, $part['partsgroup']);
$line = preg_replace("/<%partsgroup%>/i", $val, $line);
$ok = true;
$dbP = new mydb($ERPhost, $ERPdbname, $ERPuser, $ERPpass, $ERPport, 'pgsql', $err, $debug);
if (!$dbP->db) {
$ok = false;
echo "Keine Verbindung zur ERP<br>";
$dbP = false;
unset($divStd['ID']);
unset($divVerm['ID']);
unset($minder['ID']);
unset($versand['ID']);
unset($nachn['ID']);
unset($paypal['ID']);
unset($treuhand['ID']);
unset($ERPusr['ID']);
} else {
$tmp = getTax($dbP);
$tax = $tmp['TAX'];
$sql = "SELECT id,description,unit,buchungsgruppen_id FROM parts where partnumber = '%s'";
$rs = $dbP->getOne(sprintf($sql, $divStd['NR']));
$divStd['ID'] = $rs['id'];
$divStd['Unit'] = $rs['unit'];
$divStd['BUGRU'] = $rs['buchungsgruppen_id'];
$divStd['TAX'] = $tax[4][$rs['buchungsgruppen_id']]['rate'];
$divStd['TXT'] = addslashes($rs['description']);
$rs = $dbP->getOne(sprintf($sql, $divVerm['NR']));
$divVerm['ID'] = $rs['id'];
$divVerm['Unit'] = $rs['unit'];
$divVerm['BUGRU'] = $rs['buchungsgruppen_id'];
$divVerm['TAX'] = $tax[4][$rs['buchungsgruppen_id']]['rate'];
$divVerm['TXT'] = addslashes($rs['description']);
$rs = $dbP->getOne(sprintf($sql, $versandS['NR']));
<?php
session_start();
include_once("includes/configs.php");
$db_connect = mysql_connect("localhost","root","");
$connect_db = mysql_select_db("billing_erp");
//,(sum(price)*)
$currentTax = getTax();
$query = mysql_query("SELECT *,sum(total_units) as units FROM usage_data group by consumer_id");
echo "<pre>";
while($row = mysql_fetch_assoc($query)) {
//print_r($row);
}
/*
if units are <= 100 then charge Rs 1 rupee/unit
if units are > 100 && <= 200 then charge Rs 2 rupee/unit
if units are > 200 && <= 300 then charge Rs 3 rupee/unit
if units are > 300then charge Rs 7 rupee/unit
function mkTTorder($id, $evids, $trans_id)
{
$tt = getOneTT($id, $false);
$vendcust = $tt['tab'] == 'C' ? 'customer' : 'vendor';
//Steuerzone ermitteln (0-3)
$sql = "SELECT taxzone_id FROM " . $vendcust . " WHERE id = " . $tt["fid"];
$rs = $_SESSION['db']->getOne($sql);
$tzid = $rs["taxzone_id"];
$TAX = getTax($tzid);
//Artikeldaten holen
$sql = "SELECT * FROM parts WHERE partnumber = '" . $_SESSION['ttpart'] . "'";
$part = $_SESSION['db']->getOne($sql);
$partid = $part["id"];
$sellprice = $part["sellprice"];
$unit = $part["unit"];
//Steuersatz ermitteln
$tax = $TAX[$part["buchungsgruppen_id"]];
$curr = getCurr(True);
//Events holen
$events = getTTEvents($id, false, $evids, True);
if (!$events) {
return ".:nothing to do:.";
}
if (!$evids) {
$evids = 'and t.id in (';
foreach ($events as $row) {
$tmp[] = $row['id'];
}
$evids .= implode(',', $tmp) . ') ';
}
$_SESSION['db']->begin();
if ($trans_id < 1) {
//Auftrag erzeugen
$sonumber = $tt['tab'] == 'C' ? nextNumber("sonumber") : nextNumber("ponumber");
if (!$sonumber) {
return ".:error:.";
}
$sql = "INSERT INTO oe (notes,transaction_description,ordnumber," . $vendcust . "_id,taxincluded,currency_id) ";
$sql .= "VALUES ('" . $tt["ttdescription"] . "','" . $tt["ttname"] . "',{$sonumber},'" . $tt["fid"] . "','f',";
$sql .= "coalesce((SELECT currency_id FROM " . $vendcust . " WHERE id = " . $tt['fid'] . "),{$curr}))";
$rc = $_SESSION['db']->query($sql, "newOE");
if (!$rc) {
$sql = "DELETE FROM oe WHERE ordnumber = '{$sonumber}'";
$rc = $_SESSION['db']->query($sql, "delOE");
return ".:error:. 0";
}
$sql = "SELECT id FROM oe WHERE ordnumber = '{$sonumber}'";
$rs = $_SESSION['db']->getOne($sql);
$trans_id = $rs["id"];
if ($trans_id <= 0) {
$sql = "DELETE FROM oe WHERE ordnumber = '{$sonumber}'";
$rc = $_SESSION['db']->query($sql, "delOE");
return ".:error:. 0";
}
$netamount = 0;
} else {
$sql = "SELECT * from oe WHERE id = " . $trans_id;
$rc = $_SESSION['db']->getOne($sql, '');
if (!$rc) {
return ".:error:. 00";
}
$netamount = $rc['netamount'];
}
//$sql_i = 'INSERT INTO orderitems (trans_id, parts_id, description, qty, sellprice, unit, ship, discount,serialnumber,reqdate) values (';
$fields = array('trans_id', 'parts_id', 'description', 'qty', 'sellprice', 'unit', 'ship', 'discount', 'serialnumber', 'reqdate');
foreach ($events as $row) {
if ($row["ttstop"] == "") {
$_SESSION['db']->rollback();
return ".:close event:.";
}
$t1 = strtotime($row["ttstart"]);
$t2 = strtotime($row["ttstop"]);
//Minuten
$diff = floor(($t2 - $t1) / 60);
//Abrechnungseinheiten
$time = floor($diff / $_SESSION['tttime']);
//Ist der Rest über der Tolleranz
if ($diff - $_SESSION['tttime'] * $time > $_SESSION['ttround']) {
$time++;
}
$price = $time * $sellprice;
//Orderitemseintrag
$rqdate = substr($row['ttstop'], 0, 10);
$values = array($trans_id, $partid, $row["ttevent"], $time, $sellprice, $unit, 0, 0, $diff, $rqdate);
//$sql = $sql_i."$trans_id,$partid,'".$row["ttevent"]."',$time,$sellprice,'$unit',0,0,'$diff','".substr($row['ttstop'],0,10)."')";
//$rc = $_SESSION['db']->query($sql);
$rc = $_SESSION['db']->insert('orderitems', $fields, $values);
if (!$rc) {
$_SESSION['db']->rollback();
return ".:error:. 1";
}
$netamount += $price;
$amount += $price * $tax;
$parts = getTTparts($row["id"]);
if ($parts) {
foreach ($parts as $part) {
$values = array($trans_id, $part['parts_id'], $part['parts_txt'], $part['qty'], $part['sellprice'], $part['unit'], 0, 0, Null, $rqdate);
$rc = $_SESSION['db']->insert('orderitems', $fields, $values);
if (!$rc) {
$_SESSION['db']->rollback();
return ".:error:. 2";
}
$netamount += $part['qty'] * $part['sellprice'];
$amount += $part['qty'] * $part['sellprice'] * $TAX[$part['buchungsgruppen_id']];
}
}
}
//OE-Eintrag updaten
$nun = date('Y-m-d');
//$amount = $netamount + $mwst;
$fields = array('transdate', 'amount', 'netamount', 'reqdate', 'notes', 'employee_id');
$values = array($nun, $amount, $netamount, $nun, $tt["ttdescription"], $_SESSION["loginCRM"]);
$rc = $_SESSION['db']->update('oe', $fields, $values, 'id = ' . $trans_id);
if (!$rc) {
$_SESSION['db']->rollback();
return ".:error:. 2";
} else {
//Events als Abgerechnet markieren.
$sql = "UPDATE tt_event t set cleared = {$trans_id} where t.ttid = {$id} {$evids}";
$rc = $_SESSION['db']->query($sql);
$_SESSION['db']->commit();
return ".:ok:.";
}
}
addData();
break;
case "readStock":
showData('stock');
break;
case "readIndustry":
getData('industry');
break;
case "update":
saveData();
break;
case "delete":
removeData();
break;
case "calcTax":
getTax();
break;
default:
echo "{failure:true}";
break;
}
//end switch
function showData($table)
{
global $taxRate;
/* By specifying the start/limit params in ds.load
* the values are passed here
* if using ScriptTagProxy the values will be in $_GET
* if using HttpProxy the values will be in $_POST (or $_REQUEST)
* the following two lines check either location, but might be more
* secure to use the appropriate one according to the Proxy being used
