public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
$sql = "SELECT *,zadel.id AS zid,zadel.id, board_name AS plate\n FROM zadel\n JOIN (boards,customers)\n ON (zadel.board_id=boards.id\n AND boards.customer_id=customers.id) " . (!empty($find) ? "WHERE (board_name LIKE '%{$find}%'\n OR customers.customer LIKE '%{$find}%')" : "") . (!empty($order) ? " ORDER BY {$order} " : " ORDER BY zadel.id DESC ") . ($all ? "" : "LIMIT 20");
$ret = sql::fetchAll($sql);
return $ret;
}
public function delete($delete)
{
$affected = 0;
$sql = "DELETE FROM orders WHERE id='{$delete}'";
sql::query($sql);
$affected += sql::affected();
// удаление связей
$sql = "DELETE FROM files WHERE `table`='orders' AND rec_id='{$delete}'";
sql::query($sql);
$affected += sql::affected();
$sql = "SELECT * FROM tz WHERE order_id='{$delete}'";
$res = sql::fetchAll($sql);
foreach ($res as $rs) {
// удаление
$delete = $rs["id"];
$sql = "DELETE FROM tz WHERE id='{$delete}'";
sql::query($sql);
$affected += sql::affected();
// удаление связей
$sql = "SELECT * FROM posintz WHERE tz_id='{$delete}'";
$res1 = sql::fetchAll($sql);
foreach ($res1 as $rs1) {
$delete = $rs1["id"];
$sql = "DELETE FROM posintz WHERE id='{$delete}'";
sql::query($sql);
$affected += sql::affected();
}
}
return $affected;
}
/**
* Воззвращает массив данных из базы
* @param boolean $all Покказывать все
* @param string $order Наззвание столбца по которому сортировать
* @param string $find Подстрока поиска
* @param string $idstr строка идентификаторов, специальное использование, очень специальное
* @return array
*/
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
$sql = "SELECT * " . "FROM users__settings_types " . (!empty($find) ? "WHERE description LIKE '%{$find}%'\n OR key LIKE '%{$find}%'" : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY users__settings_types.id DESC ") . ($all ? "" : "LIMIT 20");
$ret = sql::fetchAll($sql);
return $ret;
}
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
$sql = "SELECT *\n FROM workers " . (!empty($find) ? "WHERE (fio LIKE '%{$find}%' OR DATE_FORMAT(dr,'%Y-%m-%d') LIKE '%{$find}%') " : "") . (!empty($order) ? "ORDER BY " . $order . " " : "ORDER BY fio ") . ($all ? "" : "LIMIT 20");
$ret = sql::fetchAll($sql);
return $ret;
}
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = parent::getData($all, $order, $find, $idstr);
$sql = "SELECT * \n FROM moneyfororder " . (!empty($find) ? "WHERE (`customer` LIKE '%{$find}%' OR `order` LIKE '%{$find}%' ) " : "") . "GROUP BY `customer`, `order`,`mater`,`trud` " . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY customer DESC ") . ($all ? "LIMIT 500" : "LIMIT 20");
$ret = sql::fetchAll($sql);
return $ret;
}
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
$sql = "SELECT *,conductors.id AS condid,boards.id AS plid,conductors.id\n FROM conductors\n JOIN (boards,customers)\n ON (conductors.board_id=boards.id\n AND boards.customer_id=customers.id )\n WHERE ready='0' " . (!empty($find) ? " WHERE (board_name LIKE '%{$find}%')" : "") . (!empty($order) ? " ORDER BY {$order} " : " ORDER BY conductors.id DESC ") . ($all ? "" : "LIMIT 20");
$ret = sql::fetchAll($sql);
return $ret;
}
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
$sql = "SELECT *,unix_timestamp(ts) AS uts, phototemplates.id AS ptid,phototemplates.id \n FROM phototemplates\n JOIN users\n ON phototemplates.user_id=users.id " . (!empty($find) ? "WHERE filenames LIKE '%{$find}%' OR DATE_FORMAT(ts,'%Y-%m-%d') LIKE '%{$find}%' " : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY ts DESC ") . ($all ? "LIMIT 150" : "LIMIT 20");
$ret = sql::fetchAll($sql);
return $ret;
}
public function getRecord($edit)
{
$rec = parent::getRecord($edit);
$rec[customer] = $this->getCustomer($rec[customer_id]);
$rec[customer] = $rec[customer][customer];
$sql = "SELECT * \n FROM blockpos \n JOIN boards ON boards.id=blockpos.board_id \n WHERE blockpos.block_id='{$edit}'";
$rec[blockpos] = sql::fetchAll($sql);
$param = multibyte::Json_decode(multibyte::Unescape($this->getComment($rec[comment_id])));
if (empty($param["class"])) {
$param["class"] = $rec[blockpos][0]["class"];
}
if (empty($param[basemat])) {
$basemat = explode("-", $rec[blockpos][0][textolite]);
$param[basemat] = $basemat[0];
}
$rec["comment"] = $param["coment"];
$wideandgaps = $param["wideandgaps"];
// если слои еще не заполнены заполним из wideandgaps
for ($i = 1; $i < 11; $i++) {
$sl1 = $wideandgaps[2 * $i - 2][0];
$sl2 = $wideandgaps[2 * $i - 1][0];
$pr1 = $wideandgaps[2 * $i - 2][1];
$pr2 = $wideandgaps[2 * $i - 1][1];
if (!isset($param["sl{$i}"]) || empty($param["sl{$i}"])) {
if (!empty($sl1)) {
$param["sl{$i}"] = $sl1 . "-" . $sl2;
$param["pr{$i}"] = sprintf("%5.3f/%5.3f", $pr1, $pr2);
}
}
}
$rec["param"] = $param;
$rec[files] = $this->getFilesForId('blocks', $edit);
return $rec;
}
/**
* Получет две даты и выдает данные за период
*/
public function getRangePeriod($sdate, $edate)
{
$ret = array();
//console::getInstance()->out("$sdate nnn $edate");
$sdate = date("Y-m-d", mktime(0, 0, 0, substr($sdate, 3, 2), substr($sdate, 0, 2), substr($sdate, 6, 4)));
//$dyear."-".$dmonth."-".$dday;
$edate = date("Y-m-d", mktime(0, 0, 0, substr($edate, 3, 2), substr($edate, 0, 2), substr($edate, 6, 4)));
//$dyear."-".$dmonth."-".$dday;
// выбор всех непустых названий
$sql = "SELECT *,sk_{$this->sklad}_spr.id FROM {$this->db}sk_{$this->sklad}_spr\n\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_ost ON sk_{$this->sklad}_ost.spr_id=sk_{$this->sklad}_spr.id\n\t\t\t\tWHERE nazv<>''\n\t\t\t\tORDER BY nazv";
$res = sql::fetchAll($sql);
foreach ($res as $rs) {
// Запрос тот же что и для прихода, но берем последний и смотрим только последний и его цену
$sql = "SELECT price as newprice FROM ({$this->db}sk_{$this->sklad}_dvizh)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='1'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tORDER BY ddate DESC LIMIT 1";
//echo $sql;
$res1 = sql::fetchOne($sql);
$newprice = $res1["newprice"];
// почти такой же запрос, толь до первой даты даст старую цену
$sql = "SELECT price as oldprice FROM ({$this->db}sk_{$this->sklad}_dvizh)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh.spr_id)\n\t\t\t\t\tWHERE ddate < '{$sdate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='1'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tORDER BY ddate DESC LIMIT 1";
//echo $sql;
$res1 = sql::fetchOne($sql);
$oldprice = $res1["oldprice"];
// если цены отичаются добавим в вывод
if ($newprice != $oldprice && $oldprice != 0 && $newprice != 0) {
// проверка на ноль, чтобы не делить на него при вычислении соотношения
$col[nazv] = $rs[nazv];
$col[newprice] = sprintf("%10.2f", $newprice);
$col[oldprice] = sprintf("%10.2f", $oldprice);
$col[ratio] = sprintf("%+10.2f", ($newprice - $oldprice) * 100 / $oldprice);
$ret[] = $col;
}
}
return $ret;
}
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
$sql = "SELECT *,if((krost>ost),'<span style=\"color:red\"><b>мало</b></span>','') as malo,sk_arc_{$this->sklad}_spr.id\n FROM {$this->db}`sk_arc_{$this->sklad}_spr`\n JOIN {$this->db}sk_arc_{$this->sklad}_ost\n ON sk_arc_{$this->sklad}_ost.spr_id=sk_arc_{$this->sklad}_spr.id\n WHERE nazv!='' " . (!empty($find) ? " AND nazv LIKE '%{$find}%' " : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY nazv ") . ($all ? "" : "LIMIT 20");
$ret = sql::fetchAll($sql);
return $ret;
}
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
$spr_id = $_SESSION[Auth::$lss][arctovarid];
$sql = "SELECT *,sk_arc_{$this->sklad}_dvizh.id\n FROM {$this->db}sk_arc_{$this->sklad}_dvizh\n JOIN ({$this->db}sk_{$this->sklad}_postav,{$this->db}coments)\n ON (sk_{$this->sklad}_postav.id=sk_arc_{$this->sklad}_dvizh.post_id\n AND coments.id=sk_arc_{$this->sklad}_dvizh.comment_id)\n WHERE spr_id='{$spr_id}'" . (!empty($find) ? " AND comment LIKE '%{$find}%' OR supply LIKE '%{$find}%'\n OR numd LIKE '%{$find}%'" : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY ddate DESC ") . ($all ? "" : "LIMIT 20");
$ret = sql::fetchAll($sql);
return $ret;
}
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
if (!empty($find)) {
}
$sql = "SELECT *, masterplate.id AS mpid,masterplate.id " . "FROM masterplate " . "JOIN (users,blocks,customers) " . "ON ( " . "masterplate.user_id=users.id " . "AND blocks.customer_id=customers.id " . "AND masterplate.block_id=blocks.id " . ") " . (!empty($find) ? "WHERE blockname LIKE '%{$find}%'\n OR customer LIKE '%{$find}%'" : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY masterplate.id DESC ") . ($all ? "LIMIT 50" : "LIMIT 20");
$ret = sql::fetchAll($sql);
return $ret;
}
/**
* Текстолит возвращает для ТЗ
* @param array $rec Массив $REQUEST из контроллера передается
* @return json
*/
public function textolite($rec)
{
$rec = multibyte::cp1251_to_utf8($rec);
extract($rec);
$out = '';
$sql = "SELECT * FROM `zaomppsklads`.`sk_mat__spr` ORDER BY nazv";
$res[textolite] = sql::fetchAll($sql);
$out .= json_encode($res, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP);
return $out;
}
public function getRecord($edit)
{
$rec = parent::getRecord($edit);
$sql = "SELECT * FROM blockpos JOIN boards ON boards.id=blockpos.board_id WHERE block_id={$rec[block_id]}";
$rec[boards] = sql::fetchAll($sql);
foreach ($rec[boards] as &$value) {
$value[filelinks] = $this->getFilesForId('boards', $value[board_id]);
}
return $rec;
}
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
$sql = "SELECT *\n FROM rtypes\n " . (!empty($find) ? "WHERE (type LIKE '%{$find}%' ) " : "") . (!empty($order) ? "ORDER BY " . $order . " " : "ORDER BY type ") . (isset($all) ? "" : "LIMIT 20");
$ret = sql::fetchAll($sql);
foreach ($ret as &$value) {
$value[what] = html_entity_decode($value[what]);
}
return $ret;
}
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
$sql = "SELECT *, todo.id\n FROM todo\n JOIN users\n ON users.id=u_id " . (!empty($find) ? "WHERE (what LIKE '%{$find}%' ) " : "") . ($all ? "" : (isset($find) ? " AND rt='0000-00-00 00:00:00' " : " WHERE rts='0000-00-00 00:00:00' ")) . (!empty($order) ? "ORDER BY " . $order . " " : "ORDER BY cts ") . (isset($all) ? "" : "LIMIT 20");
$ret = sql::fetchAll($sql);
foreach ($ret as &$value) {
$value[what] = html_entity_decode($value[what]);
}
return $ret;
}
public function getRecord($edit)
{
// поставщики список для выбора
$supply["0"] = "Новый";
$sql = "SELECT * FROM {$this->db}sk_{$this->sklad}_postav";
$res = sql::fetchAll($sql);
foreach ($res as $rs) {
$supply[$rs["id"]] = $rs["supply"];
}
$sql = "SELECT *,sk_{$this->sklad}_dvizh.id,sk_{$this->sklad}_postav.id AS supply_id\n FROM {$this->db}sk_{$this->sklad}_dvizh\n JOIN ({$this->db}sk_{$this->sklad}_postav,{$this->db}coments,{$this->db}sk_{$this->sklad}_spr)\n ON (sk_{$this->sklad}_postav.id=sk_{$this->sklad}_dvizh.post_id\n AND coments.id=sk_{$this->sklad}_dvizh.comment_id\n AND {$this->db}sk_{$this->sklad}_spr.id={$this->db}sk_{$this->sklad}_dvizh.spr_id)\n WHERE sk_{$this->sklad}_dvizh.id='{$edit}'";
$rec = sqltable_model::getRecord($sql);
$rec[supply] = $supply;
return $rec;
}
public function getRecord($edit)
{
$rec = parent::getRecord($edit);
$sql = "SELECT id,operation FROM operations WHERE block_type='{$this->blocktype}' OR block_type='both' ORDER BY priority,id";
$res = sql::fetchAll($sql);
foreach ($res as $value) {
$rec[operations][$value[id]] = $value[operation];
}
$operation = array_shift($res);
$operations = multibyte::Json_decode(sqltable_model::getComment($rec[coment_id]));
$coment_id = $operations[$operation[id]][comment_id];
$rec[comment] = sqltable_model::getComment($coment_id);
$rec[action_date] = $operations[$operation[id]][date];
return $rec;
}
public function delete($delete)
{
$affected = 0;
$sql = "DELETE FROM tz WHERE id='{$delete}'";
sql::query($sql);
$affected += sql::affected();
// удаление связей
$sql = "SELECT * FROM posintz WHERE tz_id='{$delete}'";
$res = sql::fetchAll($sql);
foreach ($res as $rs) {
$delete = $rs["id"];
$sql = "DELETE FROM posintz WHERE id='{$delete}'";
sql::query($sql);
$affected += sql::affected();
}
return $affected;
}
public function getData($all = false, $order = '', $find = '', $idstr = '')
{
$ret = array();
$order = strstr($order, 'files') ? '' : $order;
// не удается отсортировать по файлам
if (empty($_SESSION[customer_id])) {
$customer = "Выберите заказчика!!!";
$sql = "SELECT *, CONCAT(boards.sizex,'x',boards.sizey) AS size, \n boards.id AS boardid,boards.id\n FROM boards\n JOIN (customers)\n ON (customers.id=boards.customer_id ) " . (!empty($find) ? "WHERE board_name LIKE '%{$find}%' " : "") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY board_name DESC ") . ($all ? "LIMIT 50" : "LIMIT 20");
} else {
$cusid = $_SESSION[customer_id];
$customer = $_SESSION[customer];
// sql
$sql = "SELECT *, CONCAT(boards.sizex,'x',boards.sizey) AS size, \n boards.id AS boardid,boards.id\n FROM boards\n JOIN (customers)\n ON (customers.id=boards.customer_id ) " . (!empty($find) ? "WHERE board_name LIKE '%{$find}%' AND customers.id='{$_SESSION[customer_id]}' " : " WHERE customers.id='{$_SESSION[customer_id]}' ") . (!empty($order) ? "ORDER BY {$order} " : "ORDER BY board_name DESC ") . ($all ? "LIMIT 50" : "LIMIT 20");
}
$ret = sql::fetchAll($sql);
foreach ($ret as &$value) {
$files = $this->getFilesForId('boards', $value[boardid]);
$value[files] = $files[link];
}
return $ret;
}
public function delete($delete)
{
$affected = 0;
$sql = "DELETE FROM customers WHERE id='{$delete}'";
sql::query($sql);
$affected += sql::affected();
// удаление связей
// TODO: Удаллить блоки и позиции в блоках, а также платы
// удалить вязанные заказы и тз
$sql = "SELECT * FROM orders WHERE customer_id='{$delete}'";
$res = sql::fetchAll($sql);
foreach ($res as $rs) {
// удаление
$delete = $rs["id"];
$sql = "DELETE FROM orders WHERE id='{$delete}'";
sql::query($sql);
$affected += sql::affected();
// удаление связей
$sql = "SELECT * FROM tz WHERE order_id='{$delete}'";
$res1 = sql::fetchAll($sql);
foreach ($res1 as $rs1) {
// удаление
$delete = $rs1["id"];
$sql = "DELETE FROM tz WHERE id='{$delete}'";
sql::query($sql);
$affected += sql::affected();
// удаление связей
$sql = "SELECT * FROM posintz WHERE tz_id='{$delete}'";
$res2 = sql::fetchAll($sql);
foreach ($res2 as $rs2) {
$delete = $rs2["id"];
}
$sql = "DELETE FROM posintz WHERE id='{$delete}'";
sql::query($sql);
$affected += sql::affected();
}
}
return $affected;
}
public function getRights($userid)
{
$sql = "SELECT * FROM rtypes";
$res = sql::fetchAll($sql);
$sql = "SELECT * FROM rrtypes";
$res1 = sql::fetchAll($sql);
$out = array();
foreach ($res as $rs) {
$rec[type] = $rs[type];
$rec[name] = "r|{$rs["id"]}";
foreach ($res1 as $rs1) {
$sql = "SELECT * FROM rights WHERE type_id='{$rs["id"]}' AND u_id='{$userid}' AND rtype_id='{$rs1["id"]}'";
$rs2 = sql::fetchOne($sql);
$value[$rs1["id"]] = $rs2["right"] == 1 ? 1 : 0;
$values[$rs1["id"]] = $rs1["rtype"];
}
$rec[value] = $value;
$rec[values] = $values;
$out[types][] = $rec;
}
return $out;
}
public function getToday() {
$filename = $_SERVER[CACHE] . '/birhdays.html';
if(file_exists($filename) && (date('d',@filemtime($filename))==date('d'))) {
return file_get_contents($filename);
}
$dr = '';
$sql = "SELECT *, (YEAR(NOW())-YEAR(dr)) as let FROM workers WHERE DAYOFYEAR(dr)>= DAYOFYEAR(CURRENT_DATE()) AND DAYOFYEAR(dr)<= (DAYOFYEAR(CURRENT_DATE())+4) ORDER BY DAYOFYEAR(dr)";
$rs = sql::fetchAll($sql);
if (empty($rs)) {
//$calend = file_get_contents('http://www.calend.ru/img/export/calend.rss');
//$calend = file_get_contents(__DIR__ . '/sample.xml');
//return $this->xml2html($calend, __DIR_ . "/calend.xsl");
$filenamerss = $_SERVER[CACHE] . '/calend.xml';
$filetime = @filemtime($filenamerss);
if (!$filetime || (date('d',$filetime)!=date('d'))) {
$data = preg_replace("/\x01/","",file_get_contents('http://www.calend.ru/img/export/calend.rss'));
@file_put_contents ($filenamerss, $data);
}
$rss = simplexml_load_file($filenamerss);
$count = 0;
foreach ($rss->channel->item as $item) {
if ($count++ >= 3) break;
$text = explode(' ', $item->title.' - '.$item->description, 10);
array_pop($text);
$text = implode(' ', $text);
$dr .= "<a target=blank href={$item->link}><div>{$text}...</div></a>";
}
} else {
foreach ($rs as $res) {
$dr .= "<div>День рождения - {$res["fio"]} - {$res["dr"]} - {$res["let"]} лет</div>";
}
}
@file_put_contents ($filename, $dr);
return $dr;
}
public function getRangePeriod($sdate, $edate)
{
$ret = array();
console::getInstance()->out("{$sdate} nnn {$edate}");
$sdate = date("Y-m-d", mktime(0, 0, 0, substr($sdate, 3, 2), substr($sdate, 0, 2), substr($sdate, 6, 4)));
//$dyear."-".$dmonth."-".$dday;
$edate = date("Y-m-d", mktime(0, 0, 0, substr($edate, 3, 2), substr($edate, 0, 2), substr($edate, 6, 4)));
//$dyear."-".$dmonth."-".$dday;
$sql = "SELECT *,sk_{$this->sklad}_spr.id FROM {$this->db}sk_{$this->sklad}_spr\n\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_ost ON sk_{$this->sklad}_ost.spr_id=sk_{$this->sklad}_spr.id\n\t\t\t\tWHERE nazv<>''\n\t\t\t\tORDER BY nazv";
$res = sql::fetchAll($sql);
foreach ($res as $rs) {
$prih = 0;
$rash = 0;
$sql = "SELECT SUM(quant) as prihod FROM ({$this->db}sk_{$this->sklad}_dvizh)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='1'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tGROUP BY sk_{$this->sklad}_spr.id";
//echo $sql;
$res1 = sql::fetchAll($sql);
foreach ($res1 as $rs1) {
$prih += $rs1["prihod"];
}
$sql = "SELECT SUM(quant) as prihod FROM ({$this->db}sk_{$this->sklad}_dvizh_arc)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh_arc.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='1'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\t\t\tGROUP BY sk_{$this->sklad}_spr.id";
$res1 = sql::fetchAll($sql);
foreach ($res1 as $rs1) {
$prih += $rs1["prihod"];
}
$sql = "SELECT SUM(quant) as prihod FROM ({$this->db}sk_{$this->sklad}_dvizh)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='0'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tGROUP BY sk_{$this->sklad}_spr.id";
$rs1 = sql::fetchOne($sql);
if (!empty($rs1)) {
$rash += $rs1["prihod"];
}
$sql = "SELECT SUM(quant) as prihod FROM ({$this->db}sk_{$this->sklad}_dvizh_arc)\n\t\t\t\t\tJOIN {$this->db}sk_{$this->sklad}_spr ON (sk_{$this->sklad}_spr.id=sk_{$this->sklad}_dvizh_arc.spr_id)\n\t\t\t\t\tWHERE ddate >= '{$sdate}'\n\t\t\t\t\t\t\tAND ddate <= '{$edate}'\n\t\t\t\t\t\t\tAND sk_{$this->sklad}_spr.id='{$rs[id]}'\n\t\t\t\t\t\t\tAND type='0'\n\t\t\t\t\t\t\tAND numd<>'9999'\n\t\t\t\t\tGROUP BY sk_{$this->sklad}_spr.id";
$res1 = sql::fetchAll($sql);
foreach ($res1 as $rs1) {
$rash += $rs1["prihod"];
}
if ($prih != 0 || $rash != 0 || $rs["ost"] != 0) {
$cols[nazv] = $rs[nazv];
$cols[prihod] = sprintf("%10.2f", $prih);
$cols[rashod] = sprintf("%10.2f", $rash);
$cols[ost] = sprintf("%10.2f", $rs["ost"]);
$cols[edizm] = $rs[edizm];
$ret[] = $cols;
}
}
return $ret;
}
/**
* Возвращает по номеру позиции в ТЗ количество использованого задела
* @param int $id
* @return int
*/
public function getZadelByPosintzId($id)
{
$rec = array();
$sql = "SELECT\r\n zadel.number AS zadel,\r\n posintz.numbers AS zakaz,\r\n zadel.id AS zadelid\r\n FROM posintz\r\n LEFT JOIN (zadel,blocks,blockpos,boards)\r\n ON\r\n posintz.block_id=blocks.id\r\n AND blockpos.block_id=blocks.id\r\n AND boards.id=blockpos.board_id\r\n AND zadel.board_id=boards.id\r\n WHERE posintz.id='{$id}'\r\n ORDER BY zadel ASC";
// сорртирован по количеству чтоб удалять сначала те что меньше
$res = sql::fetchAll($sql);
foreach ($res as $zd) {
$rec[zadel] += $zd["zadel"];
$rec[zds] .= "{$zd[zadelid]}-{$zd["zadel"]},";
$rec[zakaz] = $zd[zakaz];
}
$rec["use"] = min($rec[zadel], $rec[zakaz]);
return $rec;
}
/**
* Функция обновляет с вставкой таблицу, можно использовать для
* массового update, возвращает affected rows
* @param string $table Таблица для обновления
* @param array $data Массив из массивов
* с данными, важно заметить, что имена полей для запроса берутся из
* первого подмассива
* @param array $exclude Список полей, не участвующих
* в обновлении (обычно ID — те поля, по которым будет контролироваться
* уникальность записей, как-то так)
* @param bool $log Тип лога, см. функцию query()
* @return int
*/
function insertUpdate($table, $data, $exclude = array("id"), $log = SQL_LOG)
{
if (!is_array($data) || count($data) < 1) {
return false;
}
$fieldsintable = sql::fetchAll("SHOW COLUMNS FROM `{$table}`");
foreach ($fieldsintable as $value) {
$fields[] = "`{$value["Field"]}`";
}
$fieldsintable = $fields;
$fields = $this->insert_getFields(reset($data), true);
sort($fields);
sort($fieldsintable);
$fields = array_intersect($fields, $fieldsintable);
$values = array();
foreach ($data as $dataRow) {
$values[] = $this->insert_prepare($dataRow, $fields);
}
$upd = array();
$sql = "INSERT INTO `{$table}` (" . implode(", ", $fields) . ") VALUES ";
foreach ($fields as $field) {
if (@(!in_array(substr($field, 1, -1), $exclude))) {
$upd[] = "{$field} = VALUES({$field})";
}
}
$upd = " ON DUPLICATE KEY UPDATE " . implode(", ", $upd);
$SQLs = array();
$n = 0;
foreach ($values as $value) {
if (mb_strlen($sql) + mb_strlen($SQLs[$n] . $value . ", ") + mb_strlen($upd) > $this->maxPacket()) {
$n++;
$this->log("insertUpdate(): Новый подзапрос №{$n}", SQL_TYPE_WARNING);
}
$SQLs[$n] .= $value . ", ";
}
foreach ($SQLs as $SQLn) {
$this->query($sql . substr($SQLn, 0, -2) . $upd, array(), $log);
}
// Оптимизируем, если нет ошибок
//if (!$this->error())
//$this->query("OPTIMIZE TABLE {$table}", array(), SQL_LOG_BLOCK);
// Cleanup
unset($data);
unset($SQLs);
unset($upd);
return $this->affected();
}
public function getCommentsForId($id, $table = '')
{
if (empty($table)) {
$table = $this->maintable;
}
$sql = "SELECT `time`, `nik` AS `author`, `comment`, comments.id as id FROM comments JOIN (users,coments) ON (comments.author_id = users.id AND comments.coment_id = coments.id ) WHERE forobject='{$table}' AND record_id='{$id}'";
$rec["comments"] = sql::fetchAll($sql);
$rec["table"] = $table;
$rec["id"] = $id;
return $rec;
}
public function arc()
{
// годовая архивация
// перенести движения
$sql = "INSERT INTO {$this->db}sk_{$this->sklad}_dvizh_arc\n (type,numd,numdf,docyr,spr_id,quant,ddate,post_id,comment_id,price)\n SELECT sk_{$this->sklad}_dvizh.type,sk_{$this->sklad}_dvizh.numd,sk_{$this->sklad}_dvizh.numdf,sk_{$this->sklad}_dvizh.docyr,sk_{$this->sklad}_dvizh.spr_id,sk_{$this->sklad}_dvizh.quant,sk_{$this->sklad}_dvizh.ddate,sk_{$this->sklad}_dvizh.post_id,sk_{$this->sklad}_dvizh.comment_id,sk_{$this->sklad}_dvizh.price\n FROM {$this->db}sk_{$this->sklad}_dvizh";
if (!sql::query($sql)) {
return false;
}
// очистить движения
$sql = "TRUNCATE TABLE {$this->db}sk_{$this->sklad}_dvizh";
if (!sql::query($sql)) {
return false;
}
$sql = "SELECT * FROM {$this->db}sk_{$this->sklad}_spr";
$res = sql::fetchAll($sql);
foreach ($res as $rs) {
$id = $rs["id"];
// получить остатки
$sql = "SELECT * FROM {$this->db}sk_{$this->sklad}_ost WHERE sk_{$this->sklad}_ost.spr_id='{$id}'";
$ost = mysql_fetch_array(mysql_query($sql));
$ost = $ost["ost"];
// создать архивное движение
// поставщик
$sql = "SELECT id FROM {$this->db}sk_{$this->sklad}_postav WHERE supply=''";
$rs1 = sql::fetchOne($sql);
if (!empty($rs1)) {
$post_id = $rs1["id"];
}
// коментарий
$sql = "SELECT id FROM {$this->db}coments WHERE comment='Передача остатка'";
$rs1 = sql::fetchOne($sql);
if (!empty($rs1)) {
$comment_id = $rs1["id"];
} else {
$sql = "INSERT INTO {$this->db}coments (comment) VALUES ('Передача остатка')";
sql::query($sql) or die(sql::error(true));
$comment_id = sql::lastId();
}
$numd = "9999";
$numdf = "9999";
$docyr = date("Y") - 1;
$ddate = date("Y-m-d", mktime(0, 0, 0, 12, 31, $docyr));
$sql = "INSERT INTO {$this->db}sk_{$this->sklad}_dvizh_arc (type,numd,numdf,docyr,spr_id,quant,ddate,post_id,comment_id,price)\n VALUES ('0','{$numd}','{$numdf}','{$docyr}','{$id}','{$ost}','{$ddate}','{$post_id}','{$comment_id}','0')";
echo $sql . "<br>";
if (!sql::query($sql)) {
return false;
}
// создадим первое движение года
// коментарий
$sql = "SELECT id FROM {$this->db}coments WHERE comment='Остаток на 31.12.{$docyr}'";
$rs1 = sql::fetchOne($sql);
if (!empty($rs1)) {
$comment_id = $rs1["id"];
} else {
$sql = "INSERT INTO {$this->db}coments (comment) VALUES ('Остаток на 31.12.{$docyr}')";
sql::query($sql) or die(sql::error(true));
$comment_id = sql::lastId();
}
$docyr = date("Y");
$ddate = date("Y-m-d", mktime(0, 0, 0, 1, 1, $docyr));
$sql = "INSERT INTO {$this->db}sk_{$this->sklad}_dvizh (type,numd,numdf,docyr,spr_id,quant,ddate,post_id,comment_id,price)\n VALUES ('1','{$numd}','{$numdf}','{$docyr}','{$id}','{$ost}','{$ddate}','{$post_id}','{$comment_id}','0')";
if (!sql::query($sql)) {
return false;
}
}
return true;
}
public function update_lanched()
{
$out = '';
$sql = "TRUNCATE TABLE `lanched`";
sql::query($sql);
$out .= sql::error() . "<br>";
$sql = "SELECT block_id, MAX( ldate ) AS md\n FROM lanch\n WHERE ldate >= DATE_SUB(NOW(),INTERVAL 1 MONTH)\n GROUP BY block_id\n ";
$rs = sql::fetchAll($sql);
foreach ($rs as $res) {
$sql = "INSERT INTO lanched SET block_id='{$res[block_id]}', lastdate='{$res[md]}'";
sql::query($sql);
$out .= sql::error() . "<br>";
}
return $out;
}
}
} elseif (isset($delete)) {
// удаление
$sql = "DELETE FROM orders WHERE id='{$delete}'";
sql::query($sql);
// удаление связей
$sql = "SELECT * FROM tz WHERE order_id='{$delete}'";
$res = sql::fetchAll($sql);
foreach ($res as $rs) {
// удаление
$delete = $rs["id"];
$sql = "DELETE FROM tz WHERE id='{$delete}'";
sql::query($sql);
// удаление связей
$sql = "SELECT * FROM posintz WHERE tz_id='{$delete}'";
$res1 = sql::fetchAll($sql);
foreach ($res1 as $rs1) {
$delete = $rs1["id"];
$sql = "DELETE FROM posintz WHERE id='{$delete}'";
sql::query($sql);
}
}
echo "ok";
} else {
// вывести таблицу
if (isset($_GET["all"])) {
$_SESSION[order_id] = '';
}
if (empty($_SESSION[customer_id])) {
$customer = "Выберите заказчика!!!";
$sql = "SELECT *,CONCAT(\"<a target=_blank href='http://" . $_SERVER["HTTP_HOST"] . UPLOAD_FILES_DIR . "/customers/\",SUBSTRING_INDEX(filelinks.file_link,'/',-1),\"'>\",SUBSTRING_INDEX(filelinks.file_link,'/',-1),\"</a>\") AS filename,orders.id FROM orders JOIN customers ON customers.id=customer_id LEFT JOIN filelinks ON orders.filelink=filelinks.id " . (isset($_GET["find"]) ? "WHERE (number LIKE '%{$_GET["find"]}%' OR orderdate LIKE '%{$_GET["find"]}%' ) " : "") . (isset($_GET["order"]) ? "ORDER BY {$_GET["order"]} " : "ORDER BY orders.orderdate DESC ") . (isset($_GET["all"]) ? "LIMIT 50" : "LIMIT 20");