/**
* @brief Methode qui permet la creation d'une table
* @details
* @param name Nom de la table a creer
* @param params Array contenant la description a suivre pour la creation de la table
*/
public static function createTable($name, $params = array(), $table = array())
{
if (!empty($name)) {
$query = "CREATE TABLE IF NOT EXISTS " . $name . " (id mediumint(9) NOT NULL AUTO_INCREMENT, ";
foreach ($params as $fieldname => $val) {
$sqltype = DataAdapter::getSqlTypeFromStdType($val['type']);
if ($val['type'] == 'join') {
$query .= " {$fieldname} " . $sqltype . " NOT NULL, ";
} else {
if ($val['type'] == 'date') {
$query .= " {$fieldname} " . $sqltype . " NOT NULL, ";
} else {
if ($val['type'] == 'largetext') {
$query .= " {$fieldname} " . $sqltype . " NOT NULL, ";
} else {
if ($val['type'] == 'text') {
$query .= " {$fieldname} " . $sqltype . " NOT NULL, ";
} else {
$query .= " {$fieldname} " . $sqltype . " NOT NULL, ";
}
}
}
}
}
$query .= " PRIMARY KEY (id)) ENGINE=";
// Si le moteur de table est spécifié
if (isset($table['engine'])) {
$query .= $table['engine'];
} else {
$query .= "MyISAM";
}
$query .= " DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; ";
sql::query($query);
if (sql::errorNo()) {
echo sql::error();
}
if (sql::$display) {
echo "\n Table de travail : " . $name . " crée \n\n";
}
}
}
if (empty($rs)) {
$sql = "INSERT INTO coppers \n (scomp,ssolder,drlname,customer_id,plate_id,sizex,sizey) \n VALUES \n ('{$comp}','{$solder}','{$drillname}','{$customer_id}',\n '{$plate_id}','{$sizex}','{$sizey}')";
sql::query($sql) or die(sql::error(true));
} else {
$sql = "UPDATE coppers SET \n scomp='{$comp}', ssolder='{$solder}', drlname='{$drillname}', \n sizex='{$sizex}', sizey='{$sizey}' \n WHERE customer_id='{$customer_id}' AND plate_id='{$plate_id}'";
sql::query($sql) or die(sql::error(true));
}
// изменения в блоки
$sql = "SELECT id FROM blocks \n WHERE customer_id='{$customer_id}' AND blockname='{$board}'";
$rs = sql::fetchOne($sql);
if (empty($rs)) {
$sql = "INSERT INTO blocks \n (scomp,ssolder,drlname,customer_id,blockname,sizex,sizey) \n VALUES \n ('{$comp}','{$solder}','{$drillname}',\n '{$customer_id}','{$board}','{$sizex}','{$sizey}')";
sql::query($sql) or die(sql::error(true));
} else {
$plate_id = $rs["id"];
$sql = "UPDATE blocks SET \n scomp='{$comp}', ssolder='{$solder}', drlname='{$drillname}', \n sizex='{$sizex}', sizey='{$sizey}' WHERE id='{$plate_id}'";
sql::query($sql) or die(sql::error(true));
}
// а тепрерь созадидим фал копирования сверловок
$sql = "SELECT kdir FROM customers WHERE id='{$customer_id}'";
$rs = sql::fetchOne($sql);
if (empty($rs)) {
if ($customer == "Импульс") {
$rs[kdir] .= "\\{$drillname}";
$mpp = -1;
}
echo "mkdir k:\\" . $rs[0] . ($mpp != -1 ? "\\MPP" : "") . "\\\n";
echo "copy /Y .\\{$drillname}.mk2 k:\\" . $rs[0] . ($mpp != -1 ? "\\MPP" : "") . "\\\n";
echo "copy /Y .\\{$drillname}.mk4 k:\\" . $rs[0] . ($mpp != -1 ? "\\MPP" : "") . "\\\n";
echo "copy /Y .\\{$drillname}.frz k:\\" . $rs[0] . ($mpp != -1 ? "\\MPP" : "") . "\\\n";
}
public function getFileId($filename)
{
$filename = fileserver::normalizefile($filename);
$filename = addslashes($filename);
$sql = "SELECT id FROM filelinks WHERE file_link='{$filename}'";
$rs = sql::fetchOne($sql);
if (!empty($rs)) {
return $rs["id"];
} else {
$sql = "INSERT INTO filelinks (file_link) VALUES ('{$filename}')";
sql::query($sql) or die(sql::error(true));
return sql::lastId();
}
}
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;
}
if (isset(${'form_' . $processing_type})) {
extract(${'form_' . $processing_type});
}
ob_start();
if (isset($edit)) {
$sql = "SELECT * FROM users WHERE id='" . $edit . "'";
$rs = sql::fetchOne($sql);
$form = new Edit($processing_type);
$form->init();
$form->addFields(array(array("type" => CMSFORM_TYPE_TEXT, "name" => "nik", "label" => "Ник:", "value" => $rs["nik"]), array("type" => CMSFORM_TYPE_TEXT, "name" => "fullname", "label" => "Полное имя:", "value" => $rs["fullname"]), array("type" => CMSFORM_TYPE_TEXT, "name" => "position", "label" => "Должность:", "value" => $rs["position"]), array("type" => CMSFORM_TYPE_TEXT, "name" => "password1", "label" => "Пароль:", "value" => $rs["password"]), array("type" => CMSFORM_TYPE_TEXT, "name" => "password2", "label" => "Повтор пароля", "value" => $rs["password"])));
$form->show();
} elseif (isset($delete)) {
// удаление
$sql = "DELETE FROM users WHERE id='{$delete}'";
sql::query($sql);
sql::error(true);
echo "ok";
} else {
// вывести таблицу
// sql
$sql = "SELECT * FROM users " . (isset($find) ? "WHERE (nik LIKE '%{$find}%' OR fullname LIKE '%{$find}%' OR position LIKE '%{$find}%') " : "") . (!empty($order) ? "ORDER BY " . $order . " " : "ORDER BY nik ") . (isset($all) ? "" : "LIMIT 20");
//print $sql;
$cols[id] = "ID";
$cols[nik] = "Nik";
$cols[fullname] = "Fullname";
$cols[position] = "Position";
$table = new SqlTable($processing_type, $_SERVER[tableaction][$processing_type][next], $sql, $cols);
$table->addbutton = true;
$table->openfunc = 'openuser';
$table->show();
}
public function getPartyfile($rec)
{
extract($rec);
if ($dozap === true) {
$sql = "SELECT pos_in_tz_id AS posid\r\n FROM lanch\r\n WHERE lanch.id='{$posid}'";
//echo $sql;
$rs = sql::fetchOne($sql);
sql::error(true);
$rec[posid] = $posid = $rs[posid];
$rec[dozapnumbers] = $party;
// тут было записано сколько при дозапуске
$rec[party] = $party = -2;
} elseif ($dozap == "zadel") {
$rec[dozapnumbers] = $party;
$rec[party] = $party = -1;
}
// Получим идентификатор запуска, нового или уже сущечтвующего
// почти всегда, кажется, нового
// давай посмотрим варианты
// 1-запуск
// 2-дозапуск
// 3-использование задела
// при запуске могло использоваться, но теперь при удалении будет другое значение
// дозапуск не получится если не удален предыдущий запуск
// делаем всегда новое
/*
$sql = "SELECT * FROM lanch
WHERE pos_in_tz_id='{$posid}' AND part='{$party}'";
$rs = sql::fetchOne($sql);
if (empty($rs)) {
$sql = "INSERT INTO lanch
(ldate, user_id,pos_in_tz_id)
VALUES (NOW(),'" . Auth::getInstance()->getUser('userid') . "','{$posid}')";
sql::query($sql);
$lanch_id = sql::lastId();
} else {
$lanch_id = $rs["id"];
}
*/
// Определим идентификатор коментария
$comment_id = 1;
//пустой
$file_link_id = 1;
//пустой! обяязательно указать для нормальной работы внешних ключей
$sql = "INSERT INTO lanch\r\n (ldate, user_id,pos_in_tz_id,comment_id,file_link_id)\r\n VALUES (NOW(),'" . Auth::getInstance()->getUser('userid') . "','{$posid}','{$comment_id}','{$file_link_id}')";
sql::query($sql);
$lanch_id = sql::lastId();
// вернем вызывальщику
if (!empty($lanch_id)) {
$rec[lanch_id] = $lanch_id;
// заказчик и имя блока
$sql = "SELECT *\r\n FROM posintz\r\n JOIN (blocks,customers)\r\n ON (blocks.id=posintz.block_id AND blocks.customer_id=customers.id)\r\n WHERE posintz.id='{$posid}'";
$rs = sql::fetchOne($sql);
$rec[customer] = $customer = $rs[customer];
$rec[blockname] = $blockname = $rs[blockname];
$rec[block_id] = $rs[block_id];
return $rec;
} else {
return false;
}
}
/**
* Methode generique de save de tout module
*
* @param module Le nom du module a sauvegarder (table de destination)
* @param fields Liste de champs a sauvegarder ex: Array('nom','prenom',etc..);
* @param data Array contenant les donnés a sauvegarder ex: Array ('nom' => Jean, 'prenom'=>'Pierre')
* @return Array Renvoi un array contenant msg qui dit s'il y a eut ajout ou edition
* id representant l'id de l'enregistrement conserné
* query pour debug la chaine envoyé en mysql
*/
public function upsert($module, $fields, $data)
{
$nbr_fields = count($fields);
$data_string_array = array();
$fields_string = implode(',', $fields);
$data_string = "";
foreach ($fields as $field) {
if (isset($data[$field]) && !empty($data[$field])) {
$data_string_array[$field] = '"' . sql::escapeString($data[$field]) . '"';
} else {
$data_string_array[$field] = '""';
}
}
// SI id est set alors il s'agit d'un update
if (isset($data['id']) && $data['id'] != 0) {
foreach ($fields as $field) {
$data_string .= $field . '= ' . $data_string_array[$field] . ',';
}
$data_string = substr($data_string, 0, -1);
$query = 'UPDATE ' . $module . ' SET ' . $data_string . ' WHERE id=' . $data['id'];
//echo($query);
sql::query($query);
$response['msg'] = 'EDITED';
$response['id'] = $data['id'];
$response['query'] = $query;
} else {
// ID not set alors nouvelle création
$data_string = implode(',', $data_string_array);
$query = 'INSERT INTO ' . $module . ' (' . $fields_string . ') VALUES (' . $data_string . ');';
sql::query($query);
$lastid = sql::lastId();
if (sql::errorNo()) {
echo sql::error();
}
$response['msg'] = 'ADDED';
$response['id'] = $lastid;
$response['query'] = $query;
}
return $response;
}
public function setRecord($data)
{
extract($data);
$spr_id = $_SESSION[Auth::$lss][tovarid];
/*
* Актуализация цены
*/
//if ($this->sklad == "mat_" ) { // только для материала
// 02-12-2014 для всех
if ($type == 1) {
// приход
if ($price != 0) {
// цена указана
//if ($comment != "***" ) {
// мне лучше не знать, но наверное цена не правильная
$sql = "UPDATE {$this->db}sk_{$this->sklad}_spr SET spr_price = '{$price}' WHERE id='{$spr_id}'";
sql::query($sql);
//}
}
}
//}
//console::getInstance()->out(print_r($data,true));
// отредактировано
// найдем поставщика
if (!empty($supply_id)) {
$post_id = $supply_id;
} else {
$sql = "SELECT id FROM {$this->db}sk_{$this->sklad}_postav WHERE supply='{$supply}'";
$rs = sql::fetchOne($sql);
if (!empty($rs)) {
$post_id = $rs[id];
} else {
$sql = "INSERT INTO {$this->db}sk_{$this->sklad}_postav (supply) VALUES ('{$supply}')";
sql::query($sql);
if (sql::error(true) != null) {
return false;
}
$post_id = sql::lastId();
}
}
// Определим идентификатор коментария
$sql = "SELECT id FROM {$this->db}coments WHERE comment='{$comment}'";
$rs = sql::fetchOne($sql);
if (!empty($rs)) {
$comment_id = $rs["id"];
} else {
$sql = "INSERT INTO {$this->db}coments (comment) VALUES ('{$comment}')";
sql::query($sql);
sql::error();
$comment_id = sql::lastId();
}
list($numdf, $numyr) = explode("/", $numd);
if (empty($edit)) {
//добавление нового
$ddate = date("Y-m-d", mktime(0, 0, 0, substr($ddate, 3, 2), substr($ddate, 0, 2), substr($ddate, 6, 4)));
//$dyear."-".$dmonth."-".$dday;
$sql = "INSERT INTO {$this->db}sk_{$this->sklad}_dvizh (type,numd,numdf,docyr,spr_id,quant,ddate,post_id,comment_id,price) VALUES ('{$type}','{$numd}','{$numdf}','{$numyr}','{$spr_id}','{$quant}','{$ddate}','{$post_id}','{$comment_id}','{$price}')";
sql::query($sql);
sql::error(true);
$sql = "UPDATE {$this->db}sk_{$this->sklad}_ost SET ost=ost" . ($type ? "+" : "-") . abs($quant) . " WHERE spr_id='{$spr_id}'";
sql::query($sql);
sql::error(true);
} else {
// удалить старое движенеи
$sql = "SELECT * FROM {$this->db}sk_{$this->sklad}_dvizh WHERE id='{$edit}'";
$rs = sql::fetchOne($sql);
$sql = "UPDATE {$this->db}sk_{$this->sklad}_ost SET ost=ost" . ($rs["type"] ? "-" : "+") . abs($rs["quant"]) . " WHERE spr_id='" . $rs["spr_id"] . "'";
sql::query($sql);
$ddate = date("Y-m-d", mktime(0, 0, 0, substr($ddate, 3, 2), substr($ddate, 0, 2), substr($ddate, 6, 4)));
//$dyear."-".$dmonth."-".$dday;
$sql = "UPDATE {$this->db}sk_{$this->sklad}_dvizh SET type='{$type}',numd='{$numd}',numdf='{$numdf}',docyr='{$numyr}',spr_id='" . $rs["spr_id"] . "',quant='{$quant}',ddate='{$ddate}',post_id='{$post_id}',comment_id='{$comment_id}',price='{$price}' WHERE id='{$edit}'";
sql::query($sql);
$sql = "UPDATE {$this->db}sk_{$this->sklad}_ost SET ost=ost" . ($type ? "+" : "-") . abs($quant) . " WHERE spr_id='" . $rs["spr_id"] . "'";
sql::query($sql);
}
return true;
//sql::affected();
}