Остатки на каждый день в запросе




Принцип обмена данными из 1С с сайтом (на MySQL) и выдачи (публикации) этих данных по запросу.
PHP-Скрипт автоматической загрузки данных из файла данных в формате CSV в базу данных сайта работающего на WordPress.

В продолжение моей темы: 1С:Альфа-Авто Автосалон Автосервис: обмен с сайтом.
С помощью данного скрипта можно загружать в автоматическом режиме, по расписанию, данные сервисных книжек (ремонтов авто) из 1С:Альфа-Авто Автосалон Автосервис.
Также можно загружать данные в ручном режиме: для этого делается скрытая страница, где размещается специальная кнопка.
Комментарии размещенные внутри скрипта разъяснят логику и порядок действия.
Комментарии с "/////    echo" использовались для отладки.
Дополнительно создана таблица для журналирования результатов загрузки данных.
Скрипт включает в себя защиту от SQL инъекций (думаю безопасность соблюдена в полной мере).
В кратце:
1. Пишется скрипт, который запускает этот.
2. Создается регламентное задание в WordPress, по которому запускается скрипт из п.1. 
3. Этот скрипт осуществляет проверку на существование файла обмена в папке.
4. Если данные не новые, загрузка не производится.
5. Если данные новые, очищается таблица сервисных книжек.
6. Загружаются новые данные.

Собственно сам скрипт:

<?php // Полная загрузка сервисных книжек, создан 2018-01-05 12:44:55

global $wpdb2;
global $failure;
global $file_hist;

/////  echo '<H2><b>Старт загрузки</b></H2><br>';

$failure=FALSE;
//подключаемся к базе
$wpdb2 = include_once 'connection.php'; ; // подключаемся к MySQL
// если не удалось подключиться, и нужно оборвать PHP с сообщением об этой ошибке
if (!empty($wpdb2->error))
{
/////   echo '<H2><b>Ошибка подключения к БД, завершение.</b></H2><br>';
$failure=TRUE;
wp_die( $wpdb2->error );
}

$m_size_file=0;
$m_mtime_file=0;
$m_comment='';
/////проверка существования файлов выгрузки из 1С
////файл выгрузки сервисных книжек
$file_hist = ABSPATH.'/_1c_alfa_exchange/AA_hist.csv';
if (!file_exists($file_hist))
{
/////   echo '<H2><b>Файл обмена с сервисными книжками не существует.</b></H2><br>';
$m_comment='Файл обмена с сервисными книжками не существует';
$failure=TRUE;
}

/////инициируем таблицу лога
/////если не существует файла то возврат и ничего не делаем
if ($failure){
///включает защиту от SQL инъекций и данные можно передавать как есть, например: $_GET['foo']
/////   echo '<H2><b>Попытка вставить запись в лог таблицу</b></H2><br>';
$insert_fail_zapros=$wpdb2->insert('vin_logs', array('time_stamp'=>time(),'last_mtime_upload'=>$m_mtime_file,'last_size_upload'=>$m_size_file,'comment'=>$m_comment));
wp_die();
/////    echo '<H2><b>Возврат в начало.</b></H2><br>';
return $failure;
}
/////проверка лога загрузки, что бы не загружать тоже самое
$masiv_data_file=stat($file_hist);   ////передаем в массив свойство файла
$m_size_file=$masiv_data_file[7];    ////получаем размер файла
$m_mtime_file=$masiv_data_file[9];   ////получаем дату модификации файла
////создаем запрос на получение последней удачной загрузки
////выбираем по штампу времени создания (редактирования) файла загрузки AA_hist.csv, $m_mtime_file

/////   echo '<H2><b>Размер файла: '.$m_size_file.'</b></H2><br>';
/////   echo '<H2><b>Штамп времени файла: '.$m_mtime_file.'</b></H2><br>';
/////   echo '<H2><b>Формирование запроса на выборку из лога</b></H2><br>';
////препарируем запрос
$text_zaprosa=$wpdb2->prepare("SELECT * FROM `vin_logs` WHERE `last_mtime_upload` = %s", $m_mtime_file);
$results=$wpdb2->get_results($text_zaprosa);

if ($results)
{   foreach ( $results as $r)
{
////если штамп времени и размер файла совпадают, возврат
if (($r->last_mtime_upload==$m_mtime_file) && ($r->last_size_upload==$m_size_file))
{////echo '<H2><b>Возврат в начало, т.к. найдена запись в логе.</b></H2><br>';
$insert_fail_zapros=$wpdb2->insert('vin_logs', array('time_stamp'=>time(),'last_mtime_upload'=>$m_mtime_file,'last_size_upload'=>$m_size_file,'comment'=>'Загрузка отменена, новых данных нет, т.к. найдена запись в логе.'));
wp_die();
return $failure;
}
}
}
////если данные новые, пишем в лог запись о начале загрузки
/////echo '<H2><b>Попытка вставить запись о начале загрузки в лог таблицу</b></H2><br>';
$insert_fail_zapros=$wpdb2->insert('vin_logs', array('time_stamp'=>time(),'last_mtime_upload'=>0, 'last_size_upload'=>$m_size_file, 'comment'=>'Начало загрузки'));

////очищаем таблицу
$clear_tbl_zap=$wpdb2->prepare("TRUNCATE TABLE %s", 'vin_history');
$clear_tbl_zap_repl=str_replace("'","`",$clear_tbl_zap);
$results=$wpdb2->query($clear_tbl_zap_repl);
/////   echo '<H2><b>Очистка таблицы сервисных книжек</b></H2><br>';
if (empty($results))
{
/////   echo '<H2><b>Ошибка очистки таблицы книжек, завершение.</b></H2><br>';
//// если очистка не удалась, возврат
$failure=TRUE;
wp_die();
return $failure;
}

////загружаем данные
$table='vin_history';         // Имя таблицы для импорта
//$file_hist Имя CSV файла, откуда берется информация     // (путь от корня web-сервера)
$delim=';';          // Разделитель полей в CSV файле
$enclosed='"';      // Кавычки для содержимого полей
$escaped='\

Originally posted 2017-05-16 12:05:42. Republished by Blog Post Promoter

18 Comments

  1. lrs

    Указанный запрос не работает!

    Виртуальную таблицу Остатков и оборотов Вы дополняете отсутствующими датами, но остатки на эти даты не дополняете!! Переделывайте)

    Reply
  2. arakelyan

    (1) lrs, Благодарю за комментарий. Скажите пожалуйста в какой конфигурации Вы использовали запрос? Мне очень интересна причина, по которой запрос у Вас не работает, но у меня пока недостаточно информации чтобы её понять. За прикрепленные снимки экрана тройная благодарность.

    Reply
  3. lrs

    Все очевидно, вы соединяете таблицы по дате, но за даты, за которые не было движений остаток отсутствует, поэтому в итоговой таблице у вас дырки (т.е. по сути нулевые остатки, которые на самом деле не нулевые)

    Для примера смотрите картинку, где слева ведомомсть по товарам на складах с разворотом по дням, а справа итог выполнения вашего запроса. (например, 2,3го числа конечный остаток соответствует остатку за 1е число, но ваша таблица показывает 0 за эти дни…)

    PS. Чтоб быстрее разобраться, сделайте отбор по одной номенклатуре, по одному складу. И выберите ту позицию для анализа, по которой хотябы один день не было движений.

    Reply
  4. arakelyan

    (3) lrs, понял что Вы имели в виду. Когда тестировал у себя такой проблемы не возникало. В приложении снимок моей ведомости и консоли, в запросе разумеется ничего не менял. Движение товаров также отсутствует за некоторые даты, но строк со значением NULL не появляется. Благодарю Вас за обратную связь, постараюсь разобраться с данной проблемой.

    Reply
  5. lrs

    (4) В итоге вы уже пришли ни к остаткам на каждый день, а просто к виртуальной таблице остатков и оборотов. Вы все дальше от решения поставленной задачи))

    Даже на титульной картинке у вас пропущены даты (в основном это выходные), когда не было движений)))

    Пропущенных дат в итоговой таблице Вы не видите, потому что делаете отбор по основному поставщику, обращаясь а элементам, которые отсутствуют в построенной таблице, либо перенесите отбор в условия таблицы остатков и оборотов (Вот сюда: РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(КОНЕЦПЕРИОДА(&Дата1), КОНЕЦПЕРИОДА(&Дата2), День, , Номенклатура.ОсновнойПоставщик = &ОсновнойПоставщик)), или уберите его на момент проверки..

    Reply
  6. arakelyan

    (5) lrs, запрос был переосмыслен. Новый запрос возвращает остатки на каждый день даже если не было движений.

    Reply
  7. schthaxe

    Добрый день! Решил переделать Ваш отчет так чтобы он показывал остатки по взаимозачетам на каждый месяц.

    Регистр — ВзаиморасчетыСКонтрагентамиПоДокументамРасчетов, Периодичность — Регистратор.

    Таблицу календаря беру Кросс-запросом.

    В итоге всё получилось, но выяснился прискорбный глюк, который я не смог устранить —

    Были движения по документу на НачалоПериода, то есть КонечныйОстаток и Оборот не равны нулю. То есть допустим НачалоПериода = 01.06.2016 00:00:00, и в точности в это же время прошла оплата по реализации ну скажем за апрель, то есть есть скажем ПКО или платежка за 01.06.2016 00:00:00.

    В этом случае у нас получается на 01.06.2016 00:00:00 два остатка которые 1С почему то складывает! Итог получается больше на размер остатка на НачалоПериода! Заставить 1С разделить остатки мне не удалось, поэтому я просто сдвинул НачалоПериода на секунду.

    В прикрепленной таблице — пример без группировки на суммы.

    Reply
  8. arakelyan

    (7) Здравствуйте, если Вам не сложно пришлите пожалуйста запрос, который получился в итоге. Я попробую у себя его исполнить и найти причину.

    Reply
  9. schthaxe
    |ВЫБРАТЬ
    | Движения.Контрагент,
    | Движения.ДоговорКонтрагента,
    | Движения.ДокументРасчетовСКонтрагентом,
    | ДНИ.ДатаКалендаря КАК ДатаКалендаря,
    | СУММА(ВЫБОР
    |   КОГДА Движения.Период <= &НачалоПериода
    |    ТОГДА Движения.СуммаВзаиморасчетовКонечныйОстаток
    |   ИНАЧЕ ВЫБОР
    |     КОГДА Движения.Период <= ДНИ.ДатаКалендаря
    |      ТОГДА Движения.СуммаВзаиморасчетовОборот
    |     ИНАЧЕ 0
    |    КОНЕЦ
    |  КОНЕЦ) КАК СуммаВзаиморасчетовКонечныйОстаток
    |ПОМЕСТИТЬ тВзаиморасчеты
    |ИЗ
    | ДНИ КАК ДНИ
    |  ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ВзаиморасчетыСКонтрагентамиПоДокументамРасчетов.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Регистратор, , {(ДоговорКонтрагента).*, (Сделка).*, (Контрагент).*, (Организация).*}) КАК Движения
    |  ПО (Движения.Период <= ДНИ.ДатаКалендаря)
    |
    |СГРУППИРОВАТЬ ПО
    | Движения.Контрагент,
    | Движения.ДоговорКонтрагента,
    | ДНИ.ДатаКалендаря,
    | Движения.ДокументРасчетовСКонтрагентом
    |;
    
    Причина то понятна, я вам ее уже описал. В принципе наверно ее можно как то отфильтровывать отдельным запросом на началопериода…
    вот таким например — если данный запрос не пуст, значит беда.
    ВЫБРАТЬ
    Вз.СуммаВзаиморасчетовНачальныйОстаток,
    Вз.СуммаВзаиморасчетовОборот,
    Вз.ДокументРасчетовСКонтрагентом
    ИЗ
    РегистрНакопления.ВзаиморасчетыСКонтрагентамиПоДокументамРасчетов.ОстаткиИОбороты(&НачалоПериода, &НачалоПериода, , , ) КАК Вз
    ГДЕ
    Вз.СуммаВзаиморасчетовНачальныйОстаток <> 0
    И Вз.СуммаВзаиморасчетовОборот <> 0

    Показать

    Reply
  10. arakelyan

    (9) Прогнал запрос у себя. Да есть такое дело. Решение Вы уже описали. Если нужны остатки взаиморасчетов по дням например с 2.01.2017 по 1.27.2017 тогда параметр НачалоПериода устанавливаем на конец предыдущего дня, то есть 1.01.2017 23:59:59. В таком случае в результирующей таблице мы будем видеть корректные остатки начиная с 2.01.2017.

    Это конечно не глюк, скорее специфика работы системы. Я когда-то разбирался, пытался понять из-за чего так происходит. Если память мне не изменяет, то такое поведение образуется из-за

    СУММА(ВЫБОР
    КОГДА Движения.Период = &НачалоПериода
    ТОГДА Движения.КоличествоКонечныйОстаток
    ИНАЧЕ ВЫБОР
    КОГДА Движения.Период <= ДНИ.ДатаКалендаря
    ТОГДА Движения.КоличествоОборот
    ИНАЧЕ 0
    КОНЕЦ
    КОНЕЦ)
    

    Показать

    В итоге у меня всё работает правильно и насколько я понял у Вас тоже, верно?

    Reply
  11. schthaxe

    Это однозначно глюк и вот почему. есть такое понятие как Граница. первый остаток дается на границу исключая данное время, а второй — включая. Система должна точно определить, если ей дается время — она остаток должна брать либо включая либо исключая, но никак не складывая одно с другим!

    К сожалению в данном случае граница в качестве периода мне не подходит, так как с ней не работают запросные функции работы с датами

    Reply
  12. amazonka

    интересный подход

    Reply
  13. arakelyan

    (12)Не могу не согласиться)

    Reply
  14. palsergeich

    Спрашивают этот запрос на каждом 2м собеседовании. Или его или срез последних на каждый день.

    Задача интересная, но чисто с точки зрения алгоритмики.

    Но нужно понимать что на подзапросе вылететь в Out of memory — ну очень просто.

    А есть еще вопрос со звездочкой: а что делать если нам постоянно надо обращаться к остаткам (оборотам) на каждый день (например реальная задача — план закупок для большой сети который актуализируется чуть ли не раз в 10 минут).

    Правильный ответ: Храните их расчитанными. Ибо как не получай эти данные — или запрос в цикле (хардкод в коде, неявные запросы в цикле при помощи СКД) или гигантские промежуточные результаты. Нельзя получить быстро, что не хранится. Да минусы у этого подхода: 1) рост базы 2) отставание актуальности данных на частоту выполнения регламентного задания. Плюс другой — при частом выполнении данного запроса будет очень сильная нагрузка на базу. И если база большая, или не большая, но период большой, то время выполнения данного запроса будет или не удовлетворительным или начнутся технологические проблемы.

    Из своей практики: В реальной жизни данный запрос получается объединением данных за прошлые числа из расчитанного регистра и данные вирт таблиц за сегодня. Все работает более чем быстро.

    Reply
  15. Lupeykin

    Остаток на каждый месяц по счету 02 для бух корп

    ВЫБРАТЬ РАЗЛИЧНЫЕ
    НАЧАЛОПЕРИОДА(РегламентированныйПроизводственныйКалендарь.Дата, МЕСЯЦ) КАК Месяц
    ПОМЕСТИТЬ ВТКалендарь
    ИЗ
    РегистрСведений.ДанныеПроизводственногоКалендаря КАК РегламентированныйПроизводственныйКалендарь
    ГДЕ
    РегламентированныйПроизводственныйКалендарь.Дата МЕЖДУ &Дата1 И &Дата2
    ;
    
    ////////////////////////////////////////////////////////////­////////////////////
    ВЫБРАТЬ
    ВТКалендарь.Месяц КАК Месяц,
    СУММА(ХозрасчетныйОстатки.СуммаОстатокКт) КАК СуммаОстатокНаНачало,
    ХозрасчетныйОстатки.Организация КАК Организация,
    ХозрасчетныйОстатки.Субконто1 КАК ОС
    ПОМЕСТИТЬ ВтОстатки
    ИЗ
    ВТКалендарь КАК ВТКалендарь,
    РегистрБухгалтерии.Хозрасчетный.Остатки(&Дата1, Счет В ИЕРАРХИИ (&Счет02), , Организация = &Организация) КАК ХозрасчетныйОстатки
    
    СГРУППИРОВАТЬ ПО
    ХозрасчетныйОстатки.Организация,
    ХозрасчетныйОстатки.Субконто1,
    ВТКалендарь.Месяц
    ;
    
    ////////////////////////////////////////////////////////////­////////////////////
    ВЫБРАТЬ
    ХозрасчетныйОбороты.Период КАК Период,
    ХозрасчетныйОбороты.Субконто1 КАК Субконто1,
    СУММА(ХозрасчетныйОбороты.СуммаОборот) КАК СуммаОборот
    ПОМЕСТИТЬ ВтОбороты
    ИЗ
    РегистрБухгалтерии.Хозрасчетный.Обороты(&Дата1, &Дата2, Месяц, Счет В ИЕРАРХИИ (&Счет02), , Организация = &Организация, , ) КАК ХозрасчетныйОбороты
    
    СГРУППИРОВАТЬ ПО
    ХозрасчетныйОбороты.Период,
    ХозрасчетныйОбороты.Субконто1
    ;
    
    ////////////////////////////////////////////////////////////­////////////////////
    ВЫБРАТЬ
    ВтОстатки.Месяц КАК Месяц,
    ВтОстатки.СуммаОстатокНаНачало КАК СуммаОстатокНаНачало,
    СУММА(isnull(ВтОбороты.СуммаОборот,0)) КАК СуммаОборот,
    ВтОстатки.Организация КАК Организация,
    ВтОстатки.ОС.код КАК ИнвНомер,
    ВтОстатки.ОС КАК ОС,
    
    ВтОстатки.СуммаОстатокНаНачало — (СУММА(isnull(ВтОбороты.СуммаОборот,0))*-1) как ОстаточнаяСтоимость
    ИЗ
    ВтОстатки КАК ВтОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ ВтОбороты КАК ВтОбороты
    ПО (ВтОстатки.Месяц >= ВтОбороты.Период)
    И (ВтОстатки.ОС = ВтОбороты.Субконто1)
    ГДЕ
    ВтОстатки.ОС = &ОС
    
    СГРУППИРОВАТЬ ПО
    ВтОстатки.Месяц,
    ВтОстатки.СуммаОстатокНаНачало,
    ВтОстатки.Организация,
    ВтОстатки.ОС

    Показать

    Reply
  16. user640247

    спасибо, вроде получилось

    Reply
  17. user1296016

    Для чего конструкция

    ВЫБОР

    КОГДА Движения.Период <= ДНИ.ДатаКалендаря

    ТОГДА Движения.КоличествоОборот

    ИНАЧЕ 0

    КОНЕЦ

    когда соединение идёт

    ПО (Движения.Период <= ДНИ.ДатаКалендаря)

    Может просто Движения.КоличествоОборот?

    Reply
  18. mih_mah

    Вот простой запрос по взаиморасчетам, все работает как надо

    ВЫБРАТЬ РАЗЛИЧНЫЕ
    НАЧАЛОПЕРИОДА(РегламентированныйПроизводственныйКалендарь.ДатаКалендаря, МЕСЯЦ) КАК ПериодРегистрации
    ПОМЕСТИТЬ ВТ_Периоды
    ИЗ
    РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
    ГДЕ
    РегламентированныйПроизводственныйКалендарь.ДатаКалендаря МЕЖДУ &ДатаНачала И &ДатаОкончания
    ;
    
    ////////////////////////////////////////////////////////////­////////////////////
    ВЫБРАТЬ
    ВзаиморасчетыССотрудникамиОстатки.Сотрудник,
    ВзаиморасчетыССотрудникамиОстатки.Подразделение,
    ВзаиморасчетыССотрудникамиОстатки.СуммаРеглОстаток,
    &ДатаНачала КАК ПериодРегистрации
    ПОМЕСТИТЬ ВТ_Взаиморасчеты
    ИЗ
    РегистрНакопления.ВзаиморасчетыССотрудниками.Остатки(&ДатаНачала, Сотрудник В (&МассивСотрДляОтбора)) КАК ВзаиморасчетыССотрудникамиОстатки
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
    ВзаиморасчетыССотрудникамиОбороты.Сотрудник,
    ВзаиморасчетыССотрудникамиОбороты.Подразделение,
    ВзаиморасчетыССотрудникамиОбороты.СуммаРеглОборот,
    ДОБАВИТЬКДАТЕ(ВзаиморасчетыССотрудникамиОбороты.Период, МЕСЯЦ, 1)
    ИЗ
    РегистрНакопления.ВзаиморасчетыССотрудниками.Обороты(&ДатаНачала, &ДатаОкончания, Месяц, Сотрудник В (&МассивСотрДляОтбора)) КАК ВзаиморасчетыССотрудникамиОбороты
    ;
    
    ////////////////////////////////////////////////////////////­////////////////////
    ВЫБРАТЬ
    ВТ_Периоды.ПериодРегистрации,
    ВТ_Взаиморасчеты.Сотрудник,
    ВТ_Взаиморасчеты.Подразделение,
    СУММА(ВТ_Взаиморасчеты.СуммаРеглОстаток) КАК Результат,
    «0. НачальныйОстаток» КАК ИмяВидаРасчета,
    «А» КАК Группа,
    NULL КАК Регистратор
    ИЗ
    ВТ_Периоды КАК ВТ_Периоды
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Взаиморасчеты КАК ВТ_Взаиморасчеты
    ПО (ВТ_Взаиморасчеты.ПериодРегистрации <= ВТ_Периоды.ПериодРегистрации)
    ГДЕ
    ВЫБОР
    КОГДА НЕ ВТ_Взаиморасчеты.Сотрудник.ДатаУвольнения = ДАТАВРЕМЯ(1, 1, 1)
    И ВТ_Взаиморасчеты.Сотрудник.ДатаУвольнения <= &ДатаОкончания
    ТОГДА ВТ_Взаиморасчеты.Сотрудник.ДатаУвольнения МЕЖДУ &ДатаНачала И &ДатаОкончания
    ИНАЧЕ ИСТИНА
    КОНЕЦ
    
    СГРУППИРОВАТЬ ПО
    ВТ_Взаиморасчеты.Сотрудник,
    ВТ_Периоды.ПериодРегистрации,
    ВТ_Взаиморасчеты.Подразделение

    Показать

    Reply

Добавить коментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *