В качестве примеров:
1. Вот запрос за получение чеков
Код:
ТекстЗапроса = "SELECT Head.Cash_Code, Head.DateOperation, Head.Ck_Number, Head.Cassir, Head.Operation, Head.Summa AS SummaCheque, Pos.Ch_Pos, Pos.Code,
| Pos.BarCode, Pos.BarCode1, Pos.Quant, Pos.Price, Pos.Summa, Pos.NDSx1, CAST(Head.OperDay AS varchar(8)) AS OperDay, Head.NSmena,
| Pos.SizeScale, Pos.SizeScaleValue, Pos.Disc_Summ, Head.Id
| FROM v_ChequeHeadWA Head LEFT OUTER JOIN
| v_ChequePosWA Pos ON Head.Id = Pos.ChequeId
| WHERE (CONVERT(varchar(8), Head.DateOperation, 112) BETWEEN '" + НачДатаSQL + "' AND '" +
КонДатаSQL + "') AND Head.Cash_Code IN (" + СтрокаККМДляЗапроса + ")
| ORDER BY Head.DateOperation, Head.Cash_Code, Head.Ck_Number, Pos.Ch_Pos"; 2. Вот запрос на получение платежей в чеках (вызывается на каждый чек)
Код:
ТекстЗапроса = "SELECT Oper.DocType, Oper.DocSumm, Oper.DocNumber
| FROM "+СокрЛП(ИмяБазыКристалл)+".dbo.ChequePay AS Oper
| WHERE (DocNumber <> 'back') AND (ChequeId = "+Формат(ТИдЧека,"ЧРГ=; ЧН=; ЧГ=")+") AND DocType IN ("+ТСтрокаТиповОплат+")
| UNION ALL
| SELECT Arch.DocType, Arch.DocSumm, Arch.DocNumber
| FROM "+СокрЛП(ИмяАрхивнойБазыКристалл)+".dbo.ChequePay AS Arch
| WHERE (DocNumber <> 'back') AND (ChequeId = "+Формат(ТИдЧека,"ЧРГ=; ЧН=; ЧГ=")+") AND DocType IN ("+ТСтрокаТиповОплат+")"; 3. А вот запрос получения скидок из чеков (список типов скидок надо составить свой
Код:
ТекстЗапроса = "SELECT
| d.DiscType, d.DiscId, d.CardNumber, MAX(d.DiscPercent) AS Perc
| FROM ChequeHead h, ChequePos p, ChequeDisc d
| WHERE p.ChequeId = h.Id AND d.PosId = p.Id
| AND (CONVERT(varchar(8), h.DateOperation, 112) = '"+ДокДатаSQL+"')
| AND (d.DiscType IN (256, 512, 4194304,134217728,2097152,16384,32768,64,1,8,16))
| AND h.Cash_Code = "+Формат(НомерКассы,"ЧН=; ЧГ=0")+"
| AND h.Ck_Number = "+Формат(НомерЧека,"ЧН=; ЧГ=0")+"
| AND h.NSmena = "+Формат(НомерСмены,"ЧН=; ЧГ=0")+"
| GROUP BY d.DiscType, d.DiscId, d.CardNumber
| UNION ALL
| SELECT
| d.DiscType, d.DiscId, d.CardNumber, MAX(d.DiscPercent) AS Perc
| FROM SES_Archive.dbo.ChequeHead h, SES_Archive.dbo.ChequePos p, SES_Archive.dbo.ChequeDisc d
| WHERE p.ChequeId = h.Id AND d.PosId = p.Id
| AND (CONVERT(varchar(8), h.DateOperation, 112) = '"+ДокДатаSQL+"')
| AND (d.DiscType IN (256, 512, 4194304,134217728,2097152,16384,32768,64,1,8,16))
| AND h.Cash_Code = "+Формат(НомерКассы,"ЧН=; ЧГ=0")+"
| AND h.Ck_Number = "+Формат(НомерЧека,"ЧН=; ЧГ=0")+"
| AND h.NSmena = "+Формат(НомерСмены,"ЧН=; ЧГ=0")+"
| GROUP BY d.DiscType, d.DiscId, d.CardNumber";