Кажется нашел что то похожее на то что нужно, подскажите эта процедура выгрузит чек из таблиц 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(19, 2),
@SumNSP decimal(19, 2),
@Disc_Sum decimal(19, 2),
@MC_Date datetime,
@MC_Number int,
@MC_Depart int,
@NewStatusFiscal smallint,
@ReturnNumber int,
@CashKey int,
@BuyerSum decimal(19, 2),
@DiscPercent decimal(9, 6),
@NewChequeNo int,
@NPaySum decimal(19, 2),
@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.DiscSumm) DiscSumm, pos.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 !=99) THEN DocNumber ELSE dbo.MaskCardNumber(DocNumber) END, SUM(DocSumm),
Status, PayType, NULL, SUM(PaySumCur), CurCode,
PayDocRate, IsNull(FuelCode,'')
FROM
CurrentChequePay
WHERE
ChequeID = @CurChequeHeadId
GROUP BY
DocType, PayType, DocNumber, CurCode, Status, PayDocRate, IsNull(FuelCode,'')
HAVING
SUM(DocSumm) <> 0
/* Фикстивная оплата, если нет оплат */
IF @@ROWCOUNT = 0
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 DocNumber, ChequeId, PayType FROM CurrentChequePay) ccp 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 DocNumber, ChequeId, PayType FROM CurrentChequePay) ccp ON ccp.ChequeId = @CurChequeHeadId AND ccp.DocNumber = d.CardNumber AND ccp.PayType = 1
WHERE
p.chequeid = @CurChequeHeadId AND
ApplyToPos in (128,129)
-- Нулевые скидки пишем все для скидок по терминалу Петрол+ --
--AND ((DiscSumm!=0 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 id, Cash_Code, NSmena, Ck_Number
FROM
ChequeHead
GROUP BY
Cash_Code, NSmena, Ck_Number) ch 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(LastUser) FROM 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(DocSumm) FROM CurrentChequePay WHERE PayType = 0
UPDATE CurrentChequePay
SET DocSumm = -DocSumm, PaySumCur = -PaySumCur
WHERE PayType > 0
DELETE FROM CurrentChequePay WHERE DocNumber = 'back'
UPDATE CurrentChequePay
SET DocSumm = -@NPaySum, PaySumCur = -@NPaySum
WHERE PayType = 0 AND ID = (SELECT MIN(ID) FROM CurrentChequePay WHERE PayType = 0)
DELETE FROM CurrentChequePay WHERE PayType = 0 AND ID <> (SELECT MIN(ID) FROM CurrentChequePay WHERE PayType = 0)
EXEC tp_CreateTransferCmd @TransferType = 201, @DataId = @CurChequeHeadId, @Operation = 0
END
ELSE
DELETE FROM CurrentChequeHead
COMMIT
END