Изменение времени документов для выстраивания их на временной оси| Изменение времени документов от времени начала до времени окончания для выстраивания их на временной оси используя хранимую процедуру MSSQL | | Автор статьи: mikecool | Редакторы:
Последняя редакция №6 от 23.06.06 | История
URL: http://kb.mista.ru/article.php?id=260 | |
Ключевые слова: Документ, время,хранимая, процедура, SQL
Однажды столкнулся с ситуацией: пользователь, обладающий административными правами в 1С, перенес документы за предыдущий день, время которых было в районе 23:51. Остальные пользователи, активно работающие с базой и создавая документы, успели внести порядка 500 документов пока не закончился день по меркам 1С. Это случилось в 13.15 дня. Естессно, пришлось быстро писать обработку меняющую время документов. Все это(написание обработки и исправление) заняло порядка получаса времени. Довольно много для розничной торговли с высоким товарооборотом. Поэтому подумал и решил, что если у меня будет процедурка, которая будет исправлять время документов и выстраивать их по новой с интервалом в 1 секунду - то это решение возникшей у меня проблемы.
Возникла единственная проблема: после использования процедуры атрибут ВремяДок содержит время 00:00:00
Пока эту проблему не решил. Может кто поможет...
Проблема почти разрешилась. Опытным путе выяснил, что все попытки установить время, начиная с первой секунды дня, почему то не удавались(так и не понял почему). Нормальная работа начиналась с 01:00:00 утра. Решил на это забить и принять время начала выстраивания за 9 часов утра. Выложил новый вариант процедуры( немного поправил код, удалив ненужные переменные)
Далее привожу текст процедуры(сильно ее здесь не описываю, ибо снабдил некоторыми комментариями, будут вопросы - обращайтесь)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
Процедура выполняет 'выстраивание' документов по времени, начиная
с даты @StartD и времени @StartT и заканчивая датой @EndD и
временем @EndT
Из личных наблюдений - не надо передавать время старта меньше, чем
01:00:00, ибо 1С после этого все документы относит на 00:00:00
*/
ALTER PROCEDURE ChangeTimeDocs (@StartD CHAR(10) = '', @EndD CHAR(10) = '',
@StartT CHAR(8) = '09:00:00', @EndT CHAR(8) = '23:59:59')
/*
Параметры
@StartD, @EndD - дата начала/конца правки, формат 'ГГГГ-ММ-ДД'
@StartT - начиная с этого времени будет устанавливаться ВремяДок
*/
AS
SET NOCOUNT ON
BEGIN
IF @StartD = '' SELECT @StartD = Left(GetDate(), 10)
IF @EndD = '' SELECT @EndD = @StartD
/*
@StartTimeI, @EndTimeI - время в секундах
@CharDate, @CharDate2 - представление даты выборки строкой в формате ГГГГММДД
*/
DECLARE @Year INT, @Month INT, @Day INT,
@YearC CHAR(4), @MonthC CHAR(2), @DayC CHAR(2),
@StartTimeI INT, @EndTimeI INT,
@CharDate CHAR(8), @CharDate2 CHAR(8)
-- для хождения циклом по диапазону дат показалось удобным иметь
-- следующие переменные
DECLARE @StartDate_Date DATETIME, @EndDate_Date DATETIME
-- переменные для рассчитываемых значений даты и времени
DECLARE @TempDate DATETIME, @TempTime INT
-- переменные для значений из курсора документов
DECLARE @Id_doc CHAR(9), @Id_DocDef INT, @DTIOld CHAR(23), @DTINew CHAR(23)
-- Всего документов, значение для увеличения времени
DECLARE @DocsCount INT, @Multiplier INT
-- количество секунд в дне
DECLARE @SecInDay INT
-- начальная инициализация
SELECT @StartDate_Date = CAST((@StartD) AS DATETIME)
SELECT @EndDate_Date = CAST((@EndD+' '+@EndT) AS DATETIME)
SELECT @TempDate = @StartDate_Date
SELECT @SecInDay = 23*3600+59*60+59
-- берем имена оборотных регистров, у которых есть поле 'Date_Time_IDDOC'
DECLARE Registers CURSOR STATIC FOR
SELECT o.[Name] FROM dbo.sysobjects as o
INNER JOIN dbo.syscolumns as c ON o.[ID] = c.[ID]
AND C.[NAME] = 'Date_Time_IDDOC' and Left(o.[NAME], 2) = 'RA'
OPEN Registers
DECLARE @RegName CHAR(30), @SQLText NVARCHAR(500)
/*
Теперь пробегаемся от даты старта до даты конца с интервалом в день
и внутри каждого дня выстраиваем документы, присваивая каждому новое
время, начиная с времени первого документа
В зависимости от количества документов, интервал секунда или доли секунды
*/
WHILE @TempDate <= @EndDate_Date
BEGIN
-- взяли составляющие части 'рассчетной' даты
SELECT @Year = DATEPART(YEAR, @TempDate)
SELECT @Month = DATEPART(MONTH, @TempDate)
SELECT @Day = DATEPART(DAY, @TempDate)
-- время начала рабочего дня
SELECT @StartTimeI = (CAST(LEFT(@StartT, 2) AS INT)*3600 +
CAST(SUBSTRING(@StartT, 4, 2) AS INT)*60 +
CAST(RIGHT(@StartT, 2) AS INT)) * 10000
-- время конца дня '23:59:59' или переданное время окончания
SELECT @EndTimeI = (CAST(LEFT(@EndT, 2) AS INT)*3600 +
CAST(SUBSTRING(@EndT, 4, 2) AS INT)*60 +
CAST(RIGHT(@EndT, 2) AS INT))*10000
-- значение рассчетного времени, которое будет использоваться для установки
-- нового времени
SELECT @TempTime = @StartTimeI
-- преобразуем момент начала-конца в символьный вид
SELECT @YearC = CAST(@Year AS CHAR)
SELECT @MonthC = CAST(@Month AS CHAR)
IF LEN(@MonthC) = 1
SELECT @MonthC = '0' + @MonthC
SELECT @DayC = CAST(@Day AS CHAR)
IF LEN(@DayC) = 1
SELECT @DayC = '0' + @DayC
SELECT @CharDate = @YearC+@MonthC+@DayC
-- следующий день (для ограничения выборки)
SELECT @Year = DATEPART(YEAR, @TempDate+1)
SELECT @Month = DATEPART(MONTH, @TempDate+1)
SELECT @Day = DATEPART(DAY, @TempDate+1)
SELECT @YearC = CAST(@Year AS CHAR)
SELECT @MonthC = CAST(@Month AS CHAR)
IF LEN(@MonthC) = 1
SELECT @MonthC = '0' + @MonthC
SELECT @DayC = CAST(@Day AS CHAR)
IF LEN(@DayC) = 1
SELECT @DayC = '0' + @DayC
SELECT @CharDate2 = @YearC+@MonthC+@DayC
-- Выбираем документы за период
-- курсор объявлен статическим, чтобы имелся слепок данных до обработки
-- хотя может статика тут и не нужна...
DECLARE Docs CURSOR STATIC FOR
SELECT j.IDDOC AS IDDOC, j.IDDocDef AS IDDOCDEF,
j.Date_Time_IDDOC AS DTI_Old
FROM _1sjourn as j (NOLOCK)
WHERE (j.Date_Time_IdDoc >= @CharDate) AND
(j.Date_Time_IdDoc < @CharDate2)
ORDER BY j.Date_Time_IDDOC
OPEN Docs
-- это количество документов, попавших в выборку
SELECT @DocsCount = @@CURSOR_ROWS
-- если мы упорядочиваем доки в пределах дня, то и число секунд
-- берем между временем начала и временем конца
IF DATEDIFF(DAY, @StartDate_Date, @EndDate_Date) = 0
SELECT @SecInDay = @EndTimeI/10000 - @StartTimeI/10000
-- вот на это значение будем домножать результирующие дату-время документа
-- для расположения документа на временной оси
-- отношение @SecInDay / @DocsCount ввел для универсальности - вдруг документов
-- окажется более 84000 в день...
SELECT @Multiplier = 10000
IF @DocsCount > @SecInDay
SELECT @Multiplier = @Multiplier * @SecInDay / @DocsCount
FETCH NEXT FROM Docs
INTO @Id_doc, @Id_DocDef, @DTIOld
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DTINew = LEFT(@DTIOld, 8) +
dbo.Convert10to36(@TempTime)+
RIGHT(@DTIOld, 9)
UPDATE _1sjourn
SET Date_Time_IDDOC = @DTINew
WHERE IDDOC = @Id_doc AND IDDocDef = @Id_DocDef
-- правим регитры оборотов
FETCH FIRST FROM Registers INTO @RegName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLText = 'UPDATE ' + @RegName + 'SET Date_Time_IDDOC = @DTINEW_
WHERE IDDOC = @Id_doc_ AND LEFT(Date_Time_IDDOC, 8) = LEFT(@DTINEW_, 8)'
EXEC sp_executesql @SQLText,
N'@DTINEW_ CHAR(23), @Id_doc_ CHAR(9)', @DTINEW_ = @DTINew, @Id_doc_ = @Id_doc
FETCH NEXT FROM Registers INTO @RegName
END
-- увеличим время на одну секунду(или долю секунды)
SELECT @TempTime = @TempTime + @Multiplier
-- выбираем след. документ
FETCH NEXT FROM Docs
INTO @Id_doc, @Id_DocDef, @DTIOld
END
CLOSE Docs
DEALLOCATE Docs
-- переходим в следующий день
SELECT @TempDate = DATEADD(DAY, 1, @TempDate)
END
DEALLOCATE Registers
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Используемые процедурой функции(правленные процедуры, заимствованные из http://www.sinor.ru/~my1c/knowhow/SQLcnvID.html):
-- 1-я функция
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER FUNCTION Convert10to36
(@Deci int )
RETURNS char(6)
AS
BEGIN
--SET NOCOUNT ON
DECLARE @j INT
declare @Res36 CHAR(9)
DECLARE @Arr36 CHAR(36)
SELECT @Arr36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT @Res36 = ''
SELECT @j = LOG(@Deci)/LOG(36) +1
while @j>0
begin
SELECT @Res36 = LTRIM(RTRIM(@Res36)) + SUBSTRING(@Arr36, @Deci/POWER(36,@j-1) +1 ,1)
SELECT @Deci = @Deci%POWER(36,@j-1)
SELECT @j =@j-1
end
return @Res36
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- 2-я функция
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION Convert36to10f
(@Res36 char(9) )
RETURNS int
AS
BEGIN
--SET NOCOUNT ON
DECLARE @j INT
declare @Deci int
DECLARE @Arr36 CHAR(36)
SELECT @Arr36 = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT @Deci = 0
SELECT @j = 1
while @j <= LEN(LTRIM(RTRIM(@Res36)))
begin
if @j <> 1
SELECT @Deci = @Deci*36
SELECT @Deci = @Deci + CHARINDEX(SUBSTRING(LTRIM(RTRIM(@Res36)), @j,1),@Arr36) -1
SELECT @j = @j+1
end
return @Deci
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|