31.10.2017 06:04
talkon
 
Иногда по причине неправильной работы сторонней программы для отправки чеков в ОФД зависают чеки в таблицах CurrentChequeHead, CurrentChequePos, CurrentChequePay ... Сейчас для дальнейшей работы кассы удаляем строку из CurrentChequeHead и перебиваем чек заново что очень неудобно. Как можно переотправить чек заново из Current таблиц? Я так полагаю в хранимых процедурах для этого есть какая то процедура? Если да то продскажите какая, или подскажите как можно переотправить зависший чек.

Задавал этот вопрос в Кристал-сервис, ответили:
Стандартным функционалом SetRetail не предусмотрена переотправка чеков из указанных таблиц. По работе с базами данных Кристалл-Сервис консультаций не оказывает. Вы можете обратиться к менеджеру для уточнения возможности доработки ПО под Ваши требования.
08.11.2017 12:32
talkon
 
Может кто-нибудь подскажет варианты как можно чек посадить из Current таблиц
08.11.2017 16:48
talkon
 
Кажется нашел что то похожее на то что нужно, подскажите эта процедура выгрузит чек из таблиц CurrentChequeHead, CurrentChequePos, CurrentChequePay ... и закроет его?


SQL код:
USE [POS]
GO
/****** Object:  StoredProcedure [dbo].[StoreCheque]    Script Date: 11/08/2017 19:42:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE 
[dbo].[StoreCheque]
@
CashNo smallint,
@
DoReturn int 0
@
CurChequeHeadId int OUTPUT
AS
/*
13.07.2011 - добавлена фикстивная оплата, если нет оплат (для корректной проверки смены на магазине)
20.07.2011 - DateIn
*/
BEGIN
SET NOCOUNT ON 
SET XACT_ABORT ON 

    
DECLARE
        @
LastClosedGang int,
        @
Operation      varchar(1),
        @
FRNumber        varchar(20),
        @
DateOperation  datetime,
        @
ControlTape    int,
        @
ChequeNo       int,
        @
Cassir         varchar(10),
        @
Summa          decimal(192),
        @
SumNSP         decimal(192),
        @
Disc_Sum       decimal(192),
        @
MC_Date        datetime,
        @
MC_Number      int,
        @
MC_Depart      int,
        @
NewStatusFiscal smallint,
        @
ReturnNumber   int,
        @
CashKey        int,
        @
BuyerSum       decimal(192),
        @
DiscPercent    decimal(96),
        @
NewChequeNo    int,
        @
NPaySum        decimal(192),
        @
CS             smallint,
        @
SmenaNo        int,
        @
ZerroedCheque  tinyint,
        @
WasRegistered  smallint,
        @
ChequeId       int

    SELECT TOP 1
        
@CurChequeHeadId    ID
        @
CS                    Status,
        @
ZerroedCheque        = CASE WHEN Status 0 THEN 1 ELSE 0 END,
        @
WasRegistered        StatusFiscal,
        @
SmenaNo        NSmena,
        @
FRNumber        FRNumber
    FROM 
        CurrentChequeHead 
    ORDER BY 
        Id

    
IF @SmenaNo IS NULL
        EXEC dbo
.GetLastGang @CashNo, @FRNumber, @LastClosedGang OUTPUT, @SmenaNo OUTPUT

BEGIN TRAN

IF @CS!=4
BEGIN
    
IF @ZerroedCheque 1
    BEGIN
        SELECT    
            
@Operation       Operation
            @
FRNumber     FRNumber,
            @
DateOperation   DateOperation
            @
ControlTape     ControlTape
            @
ChequeNo        Ck_Number,
            @
Cassir          Cassir
            @
Summa           Summa
            @
SumNSP          SumNSP
            @
Disc_Sum        Disc_Sum
            @
MC_Date         MC_Date
            @
MC_Number       MC_Number
            @
MC_Depart       MC_Depart
            @
NewStatusFiscal StatusFiscal
            @
ReturnNumber    ReturnNumber
            @
CashKey         ID
            @
BuyerSum        BuyerSum
            @
DiscPercent     DiscPercent
        FROM  
            CurrentChequeHead
        WHERE 
            Id 
= @CurChequeHeadId

        EXECUTE Dbo
.CntChequeHeadInsert
            
@CashNo,
            @
DateOperation,
            @
SmenaNo,
            @
FRNumber,
            @
ChequeNo,
            @
Operation,
            @
ControlTape,
            @
Cassir,
            @
Summa,
            @
SumNSP,
            @
MC_Date,
            @
MC_Number,
            @
MC_Depart,
            @
Disc_Sum,
            @
CS,
            @
NewStatusFiscal,
            @
ReturnNumber,
            @
BuyerSum,
            @
DiscPercent,
            @
CashKey,
            
0,
            
0,
            @
ChequeId OUTPUT

        
IF EXISTS (SELECT FROM CurrentCkBanks)
            
EXEC dbo.StoreCkBanks
    END 
    
ELSE
    IF @
WasRegistered 1
    BEGIN
   
--------------------------------------chequehead ---------------------------------------
    
/* Запись заголовков чека*/
        
INSERT ChequeHead 
        
(
            
Operation,        DateOperation,        Ck_Number,        ControlTape,        Cassir,
            
Cash_Code,        FRNumber,            Summa,            SumNSP,                NSmena,
            
MC_Date,        MC_Number,            MC_Depart,        Disc_Sum,            Status,
            
StatusFiscal,    ReturnNumber,        CashKey,        BuyerSum,            DiscPercent,
            
FsDocNumber,    DefDiscount
        
)
        
SELECT
            h
.Operation,    h.DateOperation,     h.Ck_Number,    h.ControlTape,         h.Cassir
            
h.Cash_Code,     h.FRNumber,            h.Summa,         h.SumNSP,               h.NSmena,
            
h.MC_Date,         h.MC_Number,         h.MC_Depart,    h.Disc_Sum
            CASE 
WHEN p.ChequeId IS NULL THEN 1 ELSE 2 END,
            
h.StatusFiscal,    h.ReturnNumber,     h.ID,             h.BuyerSum,         h.DiscPercent
            
h.fsDocNumber,     /*h.DefDiscount*/ dsave.DiscSumm
        FROM  
            CurrentChequeHead h
            LEFT JOIN 
(SELECT DISTINCT ChequeID FROM CurrentChequePay WHERE DocNumber 'back'p ON h.ID p.ChequeId
            LEFT JOIN 
(
                
SELECT 
                    SUM
(d.DiscSummDiscSummpos.ChequeId
                FROM 
                    CurrentChequeDisc d 
                    JOIN CurrentChequePos pos ON pos
.Id d.PosId
                WHERE
                    d
.ApplyToPos in (128,129)
                
GROUP BY pos.ChequeId
                
dsave ON dsave.ChequeId h.Id
        WHERE 
            h
.ID = @CurChequeHeadId

        SET 
@ChequeID SCOPE_IDENTITY()

        
INSERT ChequePay
        
(
            
ChequeId,        DocType,            DocDate,            DocNumber,            DocSumm,
            
Status,            PayType,            CashKey,            PaySumCur,            CurCode,
            
PayDocRate,         FuelCode
        
)
        
SELECT  
            
@ChequeId,        DocType,            @DateOperation,        CASE WHEN (PayType >= 79 AND PayType !=99THEN DocNumber ELSE dbo.MaskCardNumber(DocNumberEND,            SUM(DocSumm),
            
Status,            PayType,            NULL,                SUM(PaySumCur),        CurCode,
            
PayDocRate,        IsNull(FuelCode,'')
        
FROM 
            CurrentChequePay
        WHERE 
            ChequeID 
= @CurChequeHeadId
        GROUP BY 
            DocType
PayTypeDocNumberCurCodeStatusPayDocRateIsNull(FuelCode,'')
        
HAVING
            SUM
(DocSumm) <> 0
            
        
/* Фикстивная оплата, если нет оплат */
        
IF @@ROWCOUNT 
            INSERT ChequePay
            
(
                
ChequeId,        DocType,            DocDate,            DocNumber,            DocSumm,
                
Status,            PayType,            CashKey,            PaySumCur,            CurCode,
                
PayDocRate,         FuelCode
            
)
            
SELECT
                
@ChequeId,           1,             @DateOperation,     '',                 0,
                
0,                   0,             NULL,               0,                  '',
                
NULL,           ''
        

        
INSERT ChequePos
        
(    
            
ChequeId,            Ch_Pos,             SalesMan,            Quant,                Price,
            
Ck_Curs,            Ck_CurAbr,            GrCode,                Code,                NDSx1,
            
NDSx2,                Summa,                SumNSP,                NSPPrice,            BarCode,
            
BarCode1,            Disc_Summ,            Disc_SummNSP,        CashKey,            SizeScale,
            
SizeScaleValue,        PosSign,            PosFound,            PriceWithoutDisc,    NSPPriceWithoutDisc,
            
PriceCur,            DiscPercent,        GoodsUnit,            GoodsName,            PlaceNumber,
            
ReservoirNo,        NozzleNo,            FreeMode,            SessionID,          Datein
        
)
        
SELECT     
            
@ChequeId,            Ch_Pos,                SalesMan,            Quant,                Price,
            
Ck_Curs,            Ck_CurAbr,            GrCode,                Code,                NDSx1,
            
NDSx2,                Summa,                SumNSP,                NSPPrice,            BarCode,
            
BarCode1,            Disc_Summ,            Disc_SummNSP,        id,                    SizeScale,
            
SizeScaleValue,        PosSign,            PosFound,            PriceWithoutDisc,    NSPPriceWithoutDisc,
            
PriceCur,            DiscPercent,        GoodsUnit,
            
CONVERT(VARCHAR(30), GoodsName),
            
PlaceNumber,
            
ReservoirNo,        NozzleNo,            FreeMode,            SessionID,          Datein
        FROM 
            CurrentChequePos
        WHERE 
            ChequeID 
= @CurChequeHeadId

        INSERT INTO ChequeDisc 
        
(
            
PosId,                 DiscType,             DiscId,            CardNumber,             
            
DiscSumm
            
DiscSummNSP,         CashKey,             Quantity,         DiscQueue,                ApplyToPos
            
DiscPercent,         Price,                NSPPrice,     CardName,     DiscAttribut,     DiscAttributType
        
)
        
SELECT    
            pc
.ID,                d.DiscType,         d.DiscId,         CASE WHEN ccp.ChequeId IS NOT NULL THEN dbo.MaskCardNumber(d.CardNumber) ELSE d.CardNumber END,
            
d.DiscSumm
            
d.DiscSummNSP,         d.id,                d.Quantity,        d.DiscQueue,             d.ApplyToPos
            
d.DiscPercent,         d.Price,             d.NSPPrice,         d.CardName,             d.DiscAttribut,     d.DiscAttributType
        FROM 
            CurrentChequeDisc d
            JOIN CurrentChequePos p ON d
.PosId p.id 
            JOIN ChequePos pc ON pc
.ch_pos p.Ch_Pos AND pc.chequeid = @ChequeID
            LEFT JOIN 
(SELECT DISTINCT DocNumberChequeIdPayType FROM CurrentChequePayccp ON ccp.ChequeId = @CurChequeHeadId AND ccp.DocNumber d.CardNumber AND ccp.PayType 1
        WHERE 
            p
.chequeid = @CurChequeHeadId AND 
            
ApplyToPos in (0,1)
            

        
INSERT INTO ChequeDiscSave 
        
(
            
PosId,                 DiscType,             DiscId,            CardNumber,             
            
DiscSumm
            
DiscSummNSP,         CashKey,             Quantity,         DiscQueue,                ApplyToPos
            
DiscPercent,         Price,                NSPPrice
        
)
        
SELECT    
            pc
.ID,                d.DiscType,         d.DiscId,         CASE WHEN ccp.ChequeId IS NOT NULL THEN dbo.MaskCardNumber(d.CardNumber) ELSE d.CardNumber END,
            
d.DiscSumm
            
d.DiscSummNSP,         d.id,                d.Quantity,        d.DiscQueue,             d.ApplyToPos
            
d.DiscPercent,         d.Price,             d.NSPPrice
        FROM 
            CurrentChequeDisc d
            JOIN CurrentChequePos p ON d
.PosId p.id 
            JOIN ChequePos pc ON pc
.ch_pos p.Ch_Pos AND pc.chequeid = @ChequeID
            LEFT JOIN 
(SELECT DISTINCT DocNumberChequeIdPayType FROM CurrentChequePayccp ON ccp.ChequeId = @CurChequeHeadId AND ccp.DocNumber d.CardNumber AND ccp.PayType 1
        WHERE 
            p
.chequeid = @CurChequeHeadId AND 
            
ApplyToPos in (128,129)
            
        -- 
Нулевые скидки пишем все для скидок по терминалу Петрол+ --
        --AND ((
DiscSumm!=AND DiscType!=256) OR DiscType=256
        --
select DocNumber from CurrentChequePay
        
--------------------------------------------------------------
        
INSERT INTO ChequePosReturn
        
(  
            
CashNo,             ChequeID,             Ck_Number,             Pos_Number
            
Quant,                 Price,                 Summa,                 IdOriginalCheque
        
)
        
SELECT    
            h
.Cash_Code,         @ChequeID,             h.Ck_Number,         p.Ch_Pos
            
ABS(p.Quant),         p.Price,             ABS(p.Summa),         ch.ID
        FROM 
            CurrentChequeHead h 
            JOIN CurrentChequePos p ON h
.id p.chequeid
            JOIN 
(    
                
SELECT 
                    max
(id) AS idCash_CodeNSmenaCk_Number 
                FROM 
                    ChequeHead 
                GROUP BY 
                    Cash_Code
NSmenaCk_Numberch ON 
                        ch
.Ck_Number h.ReturnNumber AND 
                        
ch.NSmena h.ReturnNumberSmena AND 
                        
ch.Cash_Code h.Cash_Code
        WHERE 
            h
.Cash_Code = @CashNo AND 
            
h.Operation 'R' AND 
            
h.ReturnNumber IS NOT NULL

        EXEC dbo
.StoreCkBanks
    END 
END

    
IF (@DoReturn in (1,2)) AND (@WasRegistered 1)
    
BEGIN
        EXEC dbo
.GetChequeNumber @CashNo, @FRNumber, @NewChequeNo OUTPUT

        UPDATE 
            CurrentChequeHead
        SET
            ReturnNumber        
Ck_Number,
            
ReturnNumberSmena    NSmena,
            
Ck_Number            = @NewChequeNo,
            
NSmena                = @SmenaNo,
            
Cassir                = (SELECT TOP 1 RTRIM(LastUserFROM LastState WHERE CashNo= @CashNo),
            
Cash_Code            = @CashNo,
            
DateOperation        GETDATE(),
            
Operation            'R',
            
Summa                = - Summa,
            
SumNSP                = - SumNSP,
            
Disc_Sum            0,                
            
StatusFiscal        0,
            
BuyerSum = -Summa
                  
Status 3
        WHERE
            Id 
= @CurChequeHeadId

    UPDATE CurrentChequePos
    SET    Quant                
= -Quant
        
Summa                = -Summa
        
SumNSP                = -SumNSP
        
Disc_Summ            = -Disc_Summ
        
Disc_SummNSP        = -Disc_SummNSP,
        
FreeMode            = CASE @DoReturn WHEN 1 THEN -2 WHEN 2 THEN 4 END,
        
StatusPaymentDose    0
        WHERE ChequeId 
= @CurChequeHeadId

    UPDATE disc
    SET disc
.DiscSumm = - disc.DiscSumm
        
disc.Quantity = - disc.Quantity 
        FROM CurrentChequePos pos
    JOIN CurrentChequeDisc disc ON disc
.PosId pos.Id
    WHERE pos
.ChequeId = @CurChequeHeadId

    SELECT 
@NPaySum SUM(DocSummFROM CurrentChequePay WHERE PayType 0

    UPDATE CurrentChequePay
    SET DocSumm 
= -DocSummPaySumCur = -PaySumCur 
    WHERE PayType 
0

    DELETE FROM CurrentChequePay WHERE DocNumber 
'back'

    
UPDATE CurrentChequePay
    SET DocSumm 
= -@NPaySumPaySumCur = -@NPaySum
    WHERE PayType 
AND ID = (SELECT MIN(IDFROM CurrentChequePay WHERE PayType 0)

    
DELETE FROM CurrentChequePay WHERE PayType AND ID <> (SELECT MIN(IDFROM CurrentChequePay WHERE PayType 0)

        
EXEC tp_CreateTransferCmd @TransferType 201, @DataId = @CurChequeHeadId, @Operation 0
    END 
    
ELSE
        
DELETE FROM CurrentChequeHead 

COMMIT 
END 

Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.