Untitled
unknown
plain_text
5 months ago
127 kB
1
Indexable
--IF NOT EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD')
--BEGIN
-- CREATE DATABASE RETAIL_CLOUD
--END
USE RETAIL_CLOUD
IF NOT EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'POS_DOWNLOAD')
BEGIN
CREATE TABLE POS_DOWNLOAD
(
CLOUD_DOCNUMBR VARCHAR(100),
RC VARCHAR(10),
RM VARCHAR(500),
POS_DOCNUMBR VARCHAR(100),
StoreCode VARCHAR(100),
Businessday VARCHAR(100),
POSID VARCHAR(100),
SHIFTID VARCHAR(100),
[CRUSRID] [varchar](18) NOT NULL,
[CREATDT] [datetime] NOT NULL,
[MDFUSRID] [varchar](18) NOT NULL,
[MODIFDT] [datetime] NOT NULL,
primary key(CLOUD_DOCNUMBR)
)
CREATE INDEX POS_DOWNLOAD_idx1 ON POS_DOWNLOAD(StoreCode,Businessday,POSID,SHIFTID)
CREATE INDEX POS_DOWNLOAD_idx2 ON POS_DOWNLOAD(POS_DOCNUMBR)
END
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_DOWNLOAD_SALES_TRANSACTION')
DROP PROC RETAIL_CLOUD_EY_DOWNLOAD_SALES_TRANSACTION
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_DISPLAY_SALES_MOBILE')
DROP PROC RETAIL_CLOUD_EY_DISPLAY_SALES_MOBILE
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_DISPLAY_SALES_MOBILE_ALL_PER_BATCH')
DROP PROC RETAIL_CLOUD_EY_DISPLAY_SALES_MOBILE_ALL_PER_BATCH
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_UPDATE_QUEUE_NO_SALES_ONLINE')
DROP PROC RETAIL_CLOUD_EY_UPDATE_QUEUE_NO_SALES_ONLINE
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_DISPLAY_SELECTED_SALES_MOBILE')
DROP PROC RETAIL_CLOUD_EY_DISPLAY_SELECTED_SALES_MOBILE
GO
CREATE PROC RETAIL_CLOUD_EY_DISPLAY_SELECTED_SALES_MOBILE
@POS_DOCNUMBR VARCHAR(100)
as
set nocount on
SELECT order_id,TrOrderH.creatdt as creatdt,SUBSTRING(CONVERT(VARCHAR,pick_up),1,5) as pick_up,is_manual_finish
FROM TrOrderH INNER JOIN POS_DOWNLOAD ON TrOrderH.order_id = POS_DOWNLOAD.CLOUD_DOCNUMBR
WHERE POS_DOCNUMBR = @POS_DOCNUMBR
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_UPDATE_QUEUE_NO_SALES_ONLINE
@CLOUD_DOCNUMBR VARCHAR(100),
@QUEUENO VARCHAR(100)
as
set nocount on
UPDATE TrOrderH SET queue_no = @QUEUENO WHERE order_id = @CLOUD_DOCNUMBR
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_DISPLAY_SALES_MOBILE_ALL_PER_BATCH
@StoreCode VARCHAR(100),
@POSID VARCHAR(100),
@SHIFTID VARCHAR(100),
@Businessday VARCHAR(100)
as
set nocount on
SELECT CLOUD_DOCNUMBR,RC,RM,POS_DOCNUMBR,StoreCode,Businessday,POSID,SHIFTID FROM
(
SELECT CLOUD_DOCNUMBR,RC,RM,POS_DOCNUMBR,StoreCode,Businessday,POSID,SHIFTID
FROM POS_DOWNLOAD
WHERE StoreCode = @StoreCode AND Businessday = @Businessday
AND POSID = @POSID AND SHIFTID = @SHIFTID
UNION
SELECT order_id,posstat,posmsg,POSReceipt,StoreCode,Businessday,POSID,SHIFTID
FROM TrSMUOrderH
WHERE StoreCode = @StoreCode AND Businessday = @Businessday
AND POSID = @POSID AND SHIFTID = @SHIFTID
AND posstat = 0 AND posmsg = 'SUCCESS'
UNION
SELECT order_id,LastDownloadRC,LastDownloadRM,POS_DOCNUMBR,StoreCode,Businessday,POSID,SHIFTID
FROM SALEOR_DOWNLOAD
WHERE StoreCode = @StoreCode AND Businessday = @Businessday
AND POSID = @POSID AND SHIFTID = @SHIFTID
AND IsDownloadToPOS = 0 AND LastDownloadRC = '200'
)X
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_DISPLAY_SALES_MOBILE
@StoreCode VARCHAR(100)
as
set nocount on
SELECT TOP 1 order_id,creatdt,pick_up,is_manual_finish,transtype
FROM
(
SELECT order_id,TrOrderH.creatdt as creatdt,SUBSTRING(CONVERT(VARCHAR,pick_up),1,5) as pick_up,is_manual_finish,transtype
FROM TrOrderH
LEFT OUTER JOIN POS_DOWNLOAD ON TrOrderH.Order_id = POS_DOWNLOAD.CLOUD_DOCNUMBR
WHERE CLOUD_DOCNUMBR IS NULL
AND ISNULL(posstat,0) = 0
AND transtype = 0
AND SUBSTRING(CONVERT(VARCHAR,GETDATE(),108),1,5) BETWEEN
SUBSTRING(CONVERT(VARCHAR,DATEADD(MINUTE,-15,pick_up)),1,5) AND
SUBSTRING(CONVERT(VARCHAR,DATEADD(MINUTE,15,pick_up)),1,5)
AND Outlet_Id = @StoreCode
AND CONVERT(VARCHAR,TrOrderH.creatdt,112) = CONVERT(VARCHAR,GETDATE(),112)
UNION
SELECT order_id,TrOrderH.creatdt as creatdt,SUBSTRING(CONVERT(VARCHAR,pick_up),1,5) as pick_up,is_manual_finish,transtype FROM TrOrderH
LEFT OUTER JOIN POS_DOWNLOAD ON TrOrderH.Order_id = POS_DOWNLOAD.CLOUD_DOCNUMBR
WHERE CLOUD_DOCNUMBR IS NULL
AND ISNULL(posstat,0) = 0
AND transtype = 1
AND Outlet_Id = @StoreCode
UNION
SELECT order_id,TrOrderH.creatdt as creatdt,SUBSTRING(CONVERT(VARCHAR,pick_up),1,5) as pick_up,is_manual_finish,transtype
FROM TrOrderH
LEFT OUTER JOIN POS_DOWNLOAD ON TrOrderH.Order_id = POS_DOWNLOAD.CLOUD_DOCNUMBR
WHERE CLOUD_DOCNUMBR IS NULL
AND ISNULL(posstat,0) = 0
AND transtype = 0
AND Outlet_Id = @StoreCode
AND CONVERT(VARCHAR,TrOrderH.creatdt,112) <> CONVERT(VARCHAR,GETDATE(),112)
UNION
SELECT order_id,TrOrderH.creatdt as creatdt,SUBSTRING(CONVERT(VARCHAR,pick_up),1,5) as pick_up,is_manual_finish,transtype
FROM TrOrderH
LEFT OUTER JOIN POS_DOWNLOAD ON TrOrderH.Order_id = POS_DOWNLOAD.CLOUD_DOCNUMBR
WHERE CLOUD_DOCNUMBR IS NULL
AND ISNULL(posstat,0) = 0
AND transtype = 0
AND Outlet_Id = @StoreCode
AND CONVERT(VARCHAR,TrOrderH.creatdt,112) = CONVERT(VARCHAR,GETDATE(),112)
AND SUBSTRING(CONVERT(VARCHAR,GETDATE(),108),1,5) >= SUBSTRING(CONVERT(VARCHAR,pick_up),1,5)
)SALES_ONLINE
ORDER BY creatdt ASC
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_DOWNLOAD_SALES_TRANSACTION
@COMPUTERIP VARCHAR(100),
@StoreCode VARCHAR(100),
@POSID VARCHAR(100),
@SHIFTID VARCHAR(100),
@Businessday VARCHAR(100),
@CLOUD_DOCNUMBR VARCHAR(100),
@SYS_USR VARCHAR(100),
@LSMEMBERSHIP varchar(100),
@COMPANYID varchar(100),
@COMPANYPCID varchar(100)
as
set nocount on
BEGIN TRY
IF EXISTS (SELECT '' FROM POS_DOWNLOAD WHERE CLOUD_DOCNUMBR = @CLOUD_DOCNUMBR)
BEGIN
UPDATE TrOrderH SET posstat = 1, posmsg = 'Sales is already downloaded.'
WHERE order_id = @CLOUD_DOCNUMBR
RAISERROR ('Sales is already downloaded.',16,1)
END
DECLARE @POSID_ONLINE AS VARCHAR(100) = ''
SET @POSID_ONLINE = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'POS_ID_ONLINE')
SET @POSID_ONLINE = ISNULL(@POSID_ONLINE,'')
IF @POSID <> @POSID_ONLINE
BEGIN
RAISERROR ('POS ID Online is invalid.',16,1)
END
IF EXISTS (SELECT '' FROM TrOrderD WHERE order_id = @CLOUD_DOCNUMBR AND article
NOT IN (SELECT ItemCode FROM RETAIL_MASTER.dbo.MItem))
BEGIN
UPDATE TrOrderH SET posstat = 1, posmsg = 'Item is not registered.'
WHERE order_id = @CLOUD_DOCNUMBR
RAISERROR ('Item is not registered.',16,1)
END
BEGIN TRY
BEGIN TRAN
DECLARE @MSG_ERR VARCHAR(8000) = ''
DECLARE @SALES_TAX as decimal(19,5)=0
SET @SALES_TAX = ISNULL((SELECT CONVERT(float,SETUPVALUE)+1 FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'SALES_TAX'),0)
DECLARE @order_num as int, @article as varchar(100), @qty as decimal(17,5)=0, @price as decimal(19,5) = 0, @notes as varchar(500)='',@disc as decimal(19,5)=0,@promocode as varchar(100) = ''
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesTenderT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesTenderTT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesAdditionalT WHERE COMPUTERIP = @COMPUTERIP
DECLARE CSR_DOWNLOAD CURSOR FOR
SELECT order_num,article,qty,price,notes,disc,promocode FROM TrOrderD WHERE Order_id = @CLOUD_DOCNUMBR
OPEN CSR_DOWNLOAD
FETCH NEXT FROM CSR_DOWNLOAD INTO @order_num,@article,@qty,@price,@notes,@disc,@promocode
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE
@Barcode varchar(100),
@ItemDescriptionLong varchar(500),
@ItemDescriptionShort varchar(50),
@ItemType varchar(20),
@TaxCode varchar(20),
@OpenPriceFlg bit,
@ItemValue as decimal(19,5)=0,
@ReturnAble as bit,
@MerchCat varchar(50),
@MerchCatDesc varchar(100),
@Category varchar(50),
@CategoryDesc varchar(100),
@Department varchar(50),
@DepartmentDesc varchar(100),
@Division varchar(50),
@DivisionDesc varchar(100),
@Tax decimal(19,5) = 0,
@Subtotal decimal(19,5) = 0,
@DPP decimal(19,5) = 0,
@Total decimal(19,5) = 0
SELECT TOP 1 @Barcode = Barcode, @ItemDescriptionLong = ItemDescriptionLong,
@ItemDescriptionShort = ItemDescriptionShort, @ItemType = ItemType,
@TaxCode = TaxCode, @OpenPriceFlg = OpenPriceFlg,
@ReturnAble = ReturnAble,
@MerchCat = MItem.MerchCat, @MerchCatDesc = MerchCatDesc,
@Category = Category, @CategoryDesc = CategoryDesc,
@Department = Department, @DepartmentDesc = DepartmentDesc,
@Division = Division, @DivisionDesc = DivisionDesc
FROM RETAIL_MASTER.dbo.MItem
LEFT JOIN RETAIL_MASTER.dbo.MMerchandiseCategory ON MItem.MerchCat = MMerchandiseCategory.MerchCat
where ItemCode = @article
AND TaxCode <> ''
IF @Barcode = ''
BEGIN
SET @MSG_ERR = @article + 'is not registered.'
RAISERROR (@MSG_ERR,16,1)
END
SET @Subtotal = @qty * (@price - @disc)
SET @Total = @Subtotal
IF @TaxCode <> 0
BEGIN
SET @Tax = ROUND(@Subtotal - (@Subtotal/@SALES_TAX),0)
END
SET @DPP = @Subtotal - @Tax
INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesT
(StoreCode,BusinessDay,POSID,SHIFTID,COMPUTERIP,LNITMSEQ,Barcode,ItemCode,ItemDescriptionLong,ItemDescriptionShort,ItemType,TaxCode,MerchCat,MerchCatDesc,
Category,CategoryDesc,Department,DepartmentDesc,Division,DivisionDesc,OpenPriceFlg,UnitPrice,Qty,Disc,Subtotal,DPP,Tax,Total,QtySales,SalesDisc,SalesSubtotal,SalesDPP,SalesTax,SalesTotal,QtyReturned,ReturnedDisc,ReturnedSubtotal,ReturnedDPP,ReturnedTax,ReturnedTotal,ItemValue,CRUSRID,CREATDT,MDFUSRID,MODIFDT,ReturnAble,PromoCode)
VALUES
(@StoreCode,@BusinessDay,@POSID,@SHIFTID,@COMPUTERIP,@order_num,@Barcode,
@article,@ItemDescriptionLong,@ItemDescriptionShort,@ItemType,@TaxCode,@MerchCat,@MerchCatDesc,
@Category,@CategoryDesc,@Department,@DepartmentDesc,@Division,@DivisionDesc,@OpenPriceFlg,@price,@qty,@disc,@Subtotal,@DPP,@Tax,@Total,@qty,@disc,@Subtotal,@DPP,@Tax,@Total,0,0,0,0,0,0,@ItemValue,@SYS_USR,GETDATE(),'','1900-01-01 00:00:00.000',@ReturnAble,@promocode)
INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesAdditionalT(COMPUTERIP,LNITMSEQ,USERDEFINEID,USERDEFINEVALUE,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES
(@COMPUTERIP,@order_num,@order_num,@notes,@SYS_USR,GETDATE(),'','1900-01-01 00:00:00.000')
FETCH NEXT FROM CSR_DOWNLOAD INTO @order_num,@article,@qty,@Price,@notes,@disc,@promocode
END
CLOSE CSR_DOWNLOAD
DEALLOCATE CSR_DOWNLOAD
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
CLOSE CSR_DOWNLOAD
DEALLOCATE CSR_DOWNLOAD
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesPromoT WHERE COMPUTERIP = @COMPUTERIP
INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesPromoT(COMPUTERIP,PromoCode,LimitQty)
SELECT @COMPUTERIP,PromoCode,Qty from RETAIL_CLOUD.dbo.TrOrderPromo
WHERE Order_id = @CLOUD_DOCNUMBR
DECLARE @paymentType varchar(100) = '',@PaidAmount DECIMAL(19,5) = 0, @ReferenceNo varchar(100) = ''
SELECT @paymentType =
CASE
WHEN paymentType = 'GOPAY' THEN
'GOPAY'
WHEN paymentType = 'OVO' THEN
'OVO P2P'
ELSE
paymentType
END, @PaidAmount = total_amount, @ReferenceNo = referenceNumber FROM TrOrderH WHERE Order_id = @CLOUD_DOCNUMBR
IF @paymentType = ''
BEGIN
SET @MSG_ERR = 'Payment Type is blank'
RAISERROR (@MSG_ERR,16,1)
END
EXEC RETAIL_TRANSACTION.dbo.TRX_EY_SAVE_SALES_TRANSACTION_TENDER_TEMP
@StoreCode, @BusinessDay, @POSID, @SHIFTID, @COMPUTERIP, @paymentType, @PaidAmount, @PaidAmount, @ReferenceNo, @SYS_USR
DECLARE @DPP_TOTAL as decimal(19,5) = 0, @Items_TOTAL as int = 0, @Tax_TOTAL as decimal(19,5) = 0
SET @DPP_TOTAL = (SELECT ISNULL(SUM(DPP),0) FROM RETAIL_TRANSACTION.dbo.TrSalesT
WHERE COMPUTERIP = @COMPUTERIP
AND TaxCode <> '0'
AND Barcode NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItemDonation))
SET @DPP_TOTAL = ISNULL(@DPP_TOTAL,0)
SELECT @Items_TOTAL = ISNULL(ItemCount,0),
@Tax_TOTAL = ISNULL(Tax,0)
FROM
(
SELECT ISNULL(SUM(Total),0) as Total,@DPP as DPP,ISNULL(SUM(Tax),0) as Tax,ISNULL(SUM(Subtotal),0) as Subtotal FROM RETAIL_TRANSACTION.dbo.TrSalesT
WHERE COMPUTERIP = @COMPUTERIP
)TOTAL
LEFT JOIN
(
SELECT CEILING(ISNULL(SUM(Qty),0)) as ItemCount
FROM RETAIL_TRANSACTION.dbo.TrSalesT WHERE COMPUTERIP = @COMPUTERIP
AND Barcode NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItemReceiptDisallow)
)ITEM_COUNT ON 1=1
EXEC RETAIL_TRANSACTION.dbo.TRX_EY_POST_SALES_TRANSACTION
@StoreCode,@BusinessDay,@POSID,@SHIFTID,@COMPUTERIP,@PaidAmount,@DPP_TOTAL,
@Tax_TOTAL,@PaidAmount,@Items_TOTAL,@PaidAmount,0,@PaidAmount,
@CLOUD_DOCNUMBR,'','',@SYS_USR,'','SALES TRANSACTION','',@LSMEMBERSHIP,@COMPANYID,@COMPANYPCID,
0,0,0
DECLARE @DOCNUMBR_POST VARCHAR(100) = (SELECT TOP 1 DOCNUMBR FROM RETAIL_TRANSACTION.dbo.TrSalesHH WHERE ORIDOCNUMBR = @CLOUD_DOCNUMBR ORDER BY CREATDT DESC)
EXEC RETAIL_TRANSACTION.dbo.TRX_EY_POST_AGE_GENDER_CUSTOMER
@StoreCode,
@BusinessDay,
@POSID,
@SHIFTID,
@SYS_USR,
@DOCNUMBR_POST,
'SALES TRANSACTION',
'FamilyMart ID Apps'
UPDATE RETAIL_TRANSACTION.dbo.TrKitchenHH SET TRXSOURCE = 'M' WHERE DOCNUMBR = @DOCNUMBR_POST
IF NOT EXISTS (SELECT '' FROM POS_DOWNLOAD WHERE CLOUD_DOCNUMBR = @CLOUD_DOCNUMBR)
BEGIN
INSERT INTO POS_DOWNLOAD(CLOUD_DOCNUMBR,RC,RM,POS_DOCNUMBR,StoreCode,Businessday,POSID,SHIFTID,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES (@CLOUD_DOCNUMBR,'0','SUCCESS',@DOCNUMBR_POST,@StoreCode,@Businessday,@POSID,@SHIFTID,@SYS_USR,GETDATE(),'','1900-01-01')
UPDATE TrOrderH SET posstat = 0, posmsg = 'SUCCESS.' WHERE Order_Id = @CLOUD_DOCNUMBR
END
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
GO
-----------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrOrderPromoT')
BEGIN
CREATE TABLE TrOrderPromoT
(
ipaddress varchar(50),
order_id varchar(100),
promocode varchar(50),
promodtl varchar(8000),
qty int,
primary key(ipaddress,order_id,promocode)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrOrderPromo')
BEGIN
CREATE TABLE TrOrderPromo
(
order_id varchar(100),
promocode varchar(50),
promodtl varchar(8000),
qty int,
primary key(order_id,promocode)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrOrderH')
BEGIN
CREATE TABLE TrOrderH
(
order_id varchar(100),
email varchar(100),
outlet_id varchar(100),
creatdt datetime,
total_amount decimal(19,5),
pick_up time,
paymentType varchar(100),
referenceNumber varchar(100),
queue_no varchar(100),
is_sent varchar(1),
is_manual_finish int,
transtype int,
posstat int,
posmsg varchar(8000)
primary key(order_id)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrOrderD')
BEGIN
CREATE TABLE TrOrderD
(
order_id varchar(100),
order_num varchar(100),
article varchar(100),
qty decimal(19,5),
price decimal(19,5),
disc decimal(19,5),
promocode varchar(50),
promodtl varchar(8000),
notes varchar(8000),
primary key(order_id,order_num)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrOrderHT')
BEGIN
CREATE TABLE TrOrderHT
(
ipaddress varchar(50),
order_id varchar(100),
email varchar(100),
outlet_id varchar(100),
creatdt datetime,
total_amount decimal(19,5),
pick_up time,
paymentType varchar(100),
referenceNumber varchar(100),
queue_no varchar(100),
is_sent varchar(1),
is_manual_finish int,
transtype int,
posstat int,
posmsg varchar(8000)
primary key(ipaddress,order_id)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrOrderDT')
BEGIN
CREATE TABLE TrOrderDT
(
ipaddress varchar(50),
order_id varchar(100),
order_num varchar(100),
article varchar(100),
qty decimal(19,5),
price decimal(19,5),
disc decimal(19,5),
promocode varchar(50),
promodtl varchar(8000),
notes varchar(8000),
primary key(ipaddress,order_id,order_num)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSMUOrderH')
BEGIN
CREATE TABLE TrSMUOrderH
(
order_id varchar(100),
outlet_id varchar(100),
order_date datetime,
total_amount decimal(19,5),
pick_up datetime,
queue_no varchar(100),
POSReceipt varchar(100),
StoreCode VARCHAR(100),
Businessday VARCHAR(100),
POSID VARCHAR(100),
SHIFTID VARCHAR(100),
is_sent varchar(1),
order_type varchar(10),
[user_id] varchar(100),
posstat int,
posmsg varchar(8000),
order_status int,
order_msg varchar(200),
order_sts_DT datetime,
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
primary key(order_id)
)
CREATE INDEX TrSMUOrderH_idx1 ON TrSMUOrderH(creatdt)
CREATE INDEX TrSMUOrderH_idx2 ON TrSMUOrderH([user_id])
CREATE INDEX TrSMUOrderH_idx3 ON TrSMUOrderH(POSReceipt)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSMUOrderD')
BEGIN
CREATE TABLE TrSMUOrderD
(
order_id varchar(100),
material_id varchar(100),
material_desc varchar(200),
ean11 varchar(100),
qty_order_in_base decimal(19,5),
gross_price decimal(19,5),
disc_price decimal(19,5),
net_price decimal(19,5),
Total decimal(19,5),
promol_no varchar(100),
additional_notes varchar(8000),
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
primary key(order_id,material_id,promol_no)
)
CREATE INDEX TrSMUOrderD_idx1 ON TrSMUOrderD(material_id)
CREATE INDEX TrSMUOrderD_idx2 ON TrSMUOrderD(promol_no)
CREATE INDEX TrSMUOrderD_idx3 ON TrSMUOrderD(ean11)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSMUOrderP')
BEGIN
CREATE TABLE TrSMUOrderP
(
order_id varchar(100),
payment_type varchar(100),
reference_no varchar(200),
amount decimal(19,5),
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
primary key(order_id,payment_type)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSMUOrderHT')
BEGIN
CREATE TABLE TrSMUOrderHT
(
COMPUTERIP VARCHAR(100),
order_id varchar(100),
outlet_id varchar(100),
order_date datetime,
total_amount decimal(19,5),
pick_up datetime,
queue_no varchar(100),
POSReceipt varchar(100),
is_sent varchar(1),
order_type varchar(10),
[user_id] varchar(100),
posstat int,
posmsg varchar(8000),
order_status int,
order_msg varchar(200),
order_sts_DT datetime,
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
primary key(COMPUTERIP,order_id)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSMUOrderDT')
BEGIN
CREATE TABLE TrSMUOrderDT
(
COMPUTERIP VARCHAR(100),
order_id varchar(100),
material_id varchar(100),
material_desc varchar(200),
ean11 varchar(100),
qty_order_in_base decimal(19,5),
gross_price decimal(19,5),
disc_price decimal(19,5),
net_price decimal(19,5),
Total decimal(19,5),
promol_no varchar(100),
additional_notes varchar(8000),
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
primary key(COMPUTERIP,order_id,material_id,promol_no)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSMUOrderPT')
BEGIN
CREATE TABLE TrSMUOrderPT
(
COMPUTERIP VARCHAR(100),
order_id varchar(100),
payment_type varchar(100),
reference_no varchar(200),
amount decimal(19,5),
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
primary key(COMPUTERIP,order_id,payment_type)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSaleorHT')
BEGIN
CREATE TABLE TrSaleorHT
(
COMPUTERIP VARCHAR(100),
id varchar(100),
order_id varchar(100),
outlet_id varchar(100),
point_amt DECIMAL(19,5),
point_reference varchar(100),
[status] varchar(100),
channel varchar(100),
local_id varchar(100),
local_name varchar(100),
ordered_at datetime,
payment_method varchar(100),
payment_status varchar(100),
total_price DECIMAL(19,5),
shipping_price DECIMAL(19,5),
discount_shipping DECIMAL(19,5),
channel_rebate DECIMAL(19,5),
discount_amount DECIMAL(19,5),
customer_info_id varchar(100),
customer_info_name varchar(100),
customer_info_email varchar(100),
customer_info_since datetime,
customer_address_address_1 varchar(500),
customer_address_address_2 varchar(500),
customer_address_city varchar(100),
customer_address_country varchar(100),
customer_address_name varchar(100),
customer_address_phone varchar(100),
customer_address_postal_code varchar(100),
customer_address_province varchar(100),
customer_address_province_code varchar(100),
customer_address_sub_district varchar(100),
customer_address_district varchar(100),
customer_address_coordinate varchar(100),
order_status varchar(100),
discount_type varchar(100),
order_type varchar(100),
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
PRIMARY KEY (COMPUTERIP,order_id)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSaleorDT')
BEGIN
CREATE TABLE TrSaleorDT
(
COMPUTERIP VARCHAR(100),
id varchar(100),
order_id varchar(100),
local_id varchar(100),
order_num int,
variant_sku varchar(100),
qty DECIMAL(17,5),
price DECIMAL(19,5),
sale_price DECIMAL(19,5),
total_price DECIMAL(19,5),
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
primary key (COMPUTERIP,order_id,order_num)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSaleorHW')
BEGIN
CREATE TABLE TrSaleorHW
(
id varchar(100),
order_id varchar(100),
outlet_id varchar(100),
point_amt DECIMAL(19,5),
point_reference varchar(100),
[status] varchar(100),
channel varchar(100),
local_id varchar(100),
local_name varchar(100),
ordered_at datetime,
payment_method varchar(100),
payment_status varchar(100),
total_price DECIMAL(19,5),
shipping_price DECIMAL(19,5),
discount_shipping DECIMAL(19,5),
channel_rebate DECIMAL(19,5),
discount_amount DECIMAL(19,5),
customer_info_id varchar(100),
customer_info_name varchar(100),
customer_info_email varchar(100),
customer_info_since datetime,
customer_address_address_1 varchar(500),
customer_address_address_2 varchar(500),
customer_address_city varchar(100),
customer_address_country varchar(100),
customer_address_name varchar(100),
customer_address_phone varchar(100),
customer_address_postal_code varchar(100),
customer_address_province varchar(100),
customer_address_province_code varchar(100),
customer_address_sub_district varchar(100),
customer_address_district varchar(100),
customer_address_coordinate varchar(100),
order_status varchar(100),
discount_type varchar(100),
order_type varchar(100),
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
PRIMARY KEY (order_id)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSaleorDW')
BEGIN
CREATE TABLE TrSaleorDW
(
id varchar(100),
order_id varchar(100),
local_id varchar(100),
order_num int,
variant_sku varchar(100),
qty DECIMAL(17,5),
price DECIMAL(19,5),
sale_price DECIMAL(19,5),
total_price DECIMAL(19,5),
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
primary key (order_id,order_num)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSaleorHH')
BEGIN
CREATE TABLE TrSaleorHH
(
id varchar(100),
order_id varchar(100),
outlet_id varchar(100),
point_amt DECIMAL(19,5),
point_reference varchar(100),
[status] varchar(100),
channel varchar(100),
local_id varchar(100),
local_name varchar(100),
ordered_at datetime,
payment_method varchar(100),
payment_status varchar(100),
total_price DECIMAL(19,5),
shipping_price DECIMAL(19,5),
discount_shipping DECIMAL(19,5),
channel_rebate DECIMAL(19,5),
discount_amount DECIMAL(19,5),
customer_info_id varchar(100),
customer_info_name varchar(100),
customer_info_email varchar(100),
customer_info_since datetime,
customer_address_address_1 varchar(500),
customer_address_address_2 varchar(500),
customer_address_city varchar(100),
customer_address_country varchar(100),
customer_address_name varchar(100),
customer_address_phone varchar(100),
customer_address_postal_code varchar(100),
customer_address_province varchar(100),
customer_address_province_code varchar(100),
customer_address_sub_district varchar(100),
customer_address_district varchar(100),
customer_address_coordinate varchar(100),
order_status varchar(100),
discount_type varchar(100),
order_type varchar(100),
payment_method_POS varchar(100),
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
PRIMARY KEY (order_id)
)
END
GO
IF NOT EXISTS (SELECT '' from sysobjects where name = 'TrSaleorDH')
BEGIN
CREATE TABLE TrSaleorDH
(
id varchar(100),
order_id varchar(100),
local_id varchar(100),
order_num int,
variant_sku varchar(100),
qty DECIMAL(17,5),
price DECIMAL(19,5),
sale_price DECIMAL(19,5),
total_price DECIMAL(19,5),
Price_after_voucher DECIMAL(19,5),
sale_price_voucher DECIMAL(19,5),
total_price_after_voucher DECIMAL(19,5),
CRUSRID varchar(100),
CREATDT datetime,
MDFUSRID Varchar(100),
MODIFDT datetime,
primary key (order_id,order_num)
)
END
GO
IF NOT EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'SALEOR_DOWNLOAD')
BEGIN
CREATE TABLE SALEOR_DOWNLOAD
(
order_id VARCHAR(100),
RC VARCHAR(10),
RM VARCHAR(500),
POS_DOCNUMBR VARCHAR(100),
StoreCode VARCHAR(100),
Businessday VARCHAR(100),
POSID VARCHAR(100),
SHIFTID VARCHAR(100),
IsDownloadToPOS int, --0 berarti sudah didownload, 1 berarti ready di download
LastDownloadDT datetime, --download time
LastDownloadRC varchar(100),
LastDownloadRM varchar(100),
IsUploadToCloud int, --0 berarti sudah di upload, 1 berarti ready di upload
LastUploadDT datetime, -- ready to upload time
LastUploadRC varchar(100), --Upload RC
LastUploadRM varchar(100), --Upload RM
LastUploadMSG varchar(100), --Upload Message
LastUploadRCDT datetime, --Upload time
[CRUSRID] [varchar](18) NOT NULL,
[CREATDT] [datetime] NOT NULL,
[MDFUSRID] [varchar](18) NOT NULL,
[MODIFDT] [datetime] NOT NULL,
primary key(order_id)
)
CREATE INDEX SALEOR_DOWNLOAD_idx1 ON SALEOR_DOWNLOAD(StoreCode,Businessday,POSID,SHIFTID)
CREATE INDEX SALEOR_DOWNLOAD_idx2 ON SALEOR_DOWNLOAD(POS_DOCNUMBR)
END
GO
IF NOT EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'SALEOR_ORDER_STATUS_T')
BEGIN
CREATE TABLE SALEOR_ORDER_STATUS_T
(
COMPUTERIP VARCHAR(100),
id varchar(100),
order_id varchar(100),
local_id varchar(100),
Order_status VARCHAR(100),
Order_DT datetime,
[CRUSRID] [varchar](18) NOT NULL,
[CREATDT] [datetime] NOT NULL,
[MDFUSRID] [varchar](18) NOT NULL,
[MODIFDT] [datetime] NOT NULL,
primary key(COMPUTERIP,order_id,Order_status)
)
END
GO
IF NOT EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'SALEOR_ORDER_STATUS')
BEGIN
CREATE TABLE SALEOR_ORDER_STATUS
(
id varchar(100),
order_id varchar(100),
local_id varchar(100),
Order_status VARCHAR(100),
Order_DT datetime,
[CRUSRID] [varchar](18) NOT NULL,
[CREATDT] [datetime] NOT NULL,
[MDFUSRID] [varchar](18) NOT NULL,
[MODIFDT] [datetime] NOT NULL,
primary key(order_id,Order_status)
)
END
GO
----------------------------------------PROCEDURES----------------------------------------------------
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_AS_ADD_ORDER_HEADER')
DROP PROC RETAIL_CLOUD_AS_ADD_ORDER_HEADER
GO
CREATE PROC RETAIL_CLOUD_AS_ADD_ORDER_HEADER
@order_id varchar(100),
@email varchar(100),
@outlet_id varchar(100),
@creatdt datetime,
@total_amount decimal(19,5),
@pick_up time,
@paymentType varchar(100),
@referenceNumber varchar(100),
@ipaddress varchar(50),
@im int,
@transtype int
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF NOT EXISTS(SELECT '' FROM TrOrderHT WHERE order_id = @order_id)
INSERT INTO TrOrderHT (ipaddress,order_id,email,outlet_id,creatdt,total_amount,Pick_up,paymentType,referenceNumber,queue_no,is_sent,is_manual_finish,transtype,posstat,posmsg)
VALUES (@ipaddress,@order_id,@email,@outlet_id,@creatdt,@total_amount,@pick_up,@paymentType,@referenceNumber,'',0,@im,@transtype,0,'')
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_AS_ADD_ORDER_DETAIL')
DROP PROC RETAIL_CLOUD_AS_ADD_ORDER_DETAIL
GO
CREATE PROC RETAIL_CLOUD_AS_ADD_ORDER_DETAIL
@order_id varchar(100),
@order_num varchar(100),
@article varchar(100),
@qty decimal(19,5),
@price decimal(19,5),
@notes varchar(8000),
@ipaddress varchar(50),
@disc decimal (19,5),
@pc varchar(50),
@pd varchar(8000)
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF NOT EXISTS(SELECT '' FROM TrOrderDT WHERE order_id = @order_id AND order_num = @order_num)
INSERT INTO TrOrderDT (ipaddress,order_id,order_num,article,qty,price,disc,promocode,promodtl,notes)
VALUES (@ipaddress,@order_id,@order_num,@article,@qty,@price,@disc,@pc,@pd,@notes)
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_AS_ADD_PROMO_DETAIL')
DROP PROC RETAIL_CLOUD_AS_ADD_PROMO_DETAIL
GO
CREATE PROC RETAIL_CLOUD_AS_ADD_PROMO_DETAIL
@ipaddress varchar(50),
@order_id varchar(100),
@pc varchar(50),
@pd varchar(8000),
@qty int
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF NOT EXISTS(SELECT '' FROM TrOrderPromoT WHERE order_id = @order_id AND promocode = @pc)
INSERT INTO TrOrderPromoT (ipaddress,order_id,promocode,promodtl,qty)
VALUES (@ipaddress,@order_id,@pc,@pd,@qty)
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_AS_CONFIRM_ORDER')
DROP PROC RETAIL_CLOUD_AS_CONFIRM_ORDER
GO
CREATE PROC RETAIL_CLOUD_AS_CONFIRM_ORDER
@ipaddress varchar(50)
as
set nocount on
BEGIN TRY
BEGIN TRAN
DECLARE @oi VARCHAR(100)
DECLARE oi_cursor CURSOR FOR
SELECT order_id FROM TrOrderHT WHERE ipaddress = @ipaddress
OPEN oi_cursor
FETCH NEXT FROM oi_cursor INTO @oi
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS ( SELECT '' FROM TrOrderH WHERE order_id = @oi)
BEGIN
INSERT INTO TrOrderH (order_id,email,outlet_id,creatdt,total_amount,Pick_up,paymentType,referenceNumber,queue_no,is_sent,is_manual_finish,transtype,posstat,posmsg)
SELECT order_id,email,outlet_id,creatdt,total_amount,Pick_up,paymentType,referenceNumber,queue_no,is_sent,is_manual_finish,transtype,posstat,posmsg FROM TrOrderHT WHERE ipaddress = @ipaddress AND order_id = @oi
INSERT INTO TrOrderD (order_id,order_num,article,qty,price,disc,promocode,promodtl,notes)
SELECT order_id,order_num,article,qty,price,disc,promocode,promodtl,notes FROM TrOrderDT WHERE ipaddress = @ipaddress AND order_id = @oi
INSERT INTO TrOrderPromo (order_id,promocode,promodtl,qty)
SELECT order_id,promocode,promodtl,qty FROM TrOrderPromoT WHERE ipaddress = @ipaddress AND order_id = @oi
END
FETCH NEXT FROM oi_cursor INTO @oi
END
CLOSE oi_cursor
DEALLOCATE oi_cursor
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_AS_DELETE_ORDER')
DROP PROC RETAIL_CLOUD_AS_DELETE_ORDER
GO
CREATE PROC RETAIL_CLOUD_AS_DELETE_ORDER
@ipaddress varchar(50)
as
set nocount on
BEGIN TRY
BEGIN TRAN
DELETE FROM TrOrderHT WHERE ipaddress = @ipaddress
DELETE FROM TrOrderDT WHERE ipaddress = @ipaddress
DELETE FROM TrOrderPromoT WHERE ipaddress = @ipaddress
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_AS_SEND_QUEUE')
DROP PROC RETAIL_CLOUD_AS_SEND_QUEUE
GO
CREATE PROC RETAIL_CLOUD_AS_SEND_QUEUE
@order_id VARCHAR(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
--DECLARE @order_id VARCHAR(100)
--SET @order_id = (SELECT TOP 1 order_id FROM TrOrderH WHERE queue_no IS NOT NULL AND is_sent = '0' ORDER BY creatdt ASC)
UPDATE TrOrderH SET is_sent = '1' WHERE order_id = @order_id
--SELECT TOP 1 order_id,queue_no FROM TrOrderH WHERE order_id = @order_id
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_AS_ROLLBACK_ISSENT')
DROP PROC RETAIL_CLOUD_AS_ROLLBACK_ISSENT
GO
CREATE PROC RETAIL_CLOUD_AS_ROLLBACK_ISSENT
@order_id VARCHAR(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
UPDATE TrOrderH SET is_sent = '0' WHERE order_id = @order_id
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_AS_QUEUE_TO_SEND')
DROP PROC RETAIL_CLOUD_AS_QUEUE_TO_SEND
GO
CREATE PROC RETAIL_CLOUD_AS_QUEUE_TO_SEND
as
set nocount on
BEGIN TRY
BEGIN TRAN
SELECT TOP 5 order_id,queue_no FROM TrOrderH WHERE queue_no <> '' AND is_sent = '0' ORDER BY creatdt ASC
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
-------------------------------------------------------SMU PROJECT--------------------------------------------------
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_CLEAR_ORDER_TEMP')
DROP PROC RETAIL_CLOUD_EY_SMU_CLEAR_ORDER_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_COPY_ORDER_TEMP_TO_HIST')
DROP PROC RETAIL_CLOUD_EY_SMU_COPY_ORDER_TEMP_TO_HIST
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_SAVE_ORDER_HEADER_TEMP')
DROP PROC RETAIL_CLOUD_EY_SMU_SAVE_ORDER_HEADER_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_SAVE_ORDER_DETAIL_TEMP')
DROP PROC RETAIL_CLOUD_EY_SMU_SAVE_ORDER_DETAIL_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_SAVE_ORDER_PAYMENT_TEMP')
DROP PROC RETAIL_CLOUD_EY_SMU_SAVE_ORDER_PAYMENT_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_DISPLAY_ORDER_TO_SEND')
DROP PROC RETAIL_CLOUD_EY_SMU_DISPLAY_ORDER_TO_SEND
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_UPDATE_UNSENT_ORDER')
DROP PROC RETAIL_CLOUD_EY_SMU_UPDATE_UNSENT_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_UPDATE_IS_SENT_ORDER')
DROP PROC RETAIL_CLOUD_EY_SMU_UPDATE_IS_SENT_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_DISPLAY_ORDER_TO_BE_PROCESSED')
DROP PROC RETAIL_CLOUD_EY_SMU_DISPLAY_ORDER_TO_BE_PROCESSED
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_EXECUTE_ORDER_TO_POS')
DROP PROC RETAIL_CLOUD_EY_SMU_EXECUTE_ORDER_TO_POS
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_UPDATE_QUEUE_NO_ORDER')
DROP PROC RETAIL_CLOUD_EY_SMU_UPDATE_QUEUE_NO_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_DISPLAY_SELECTED_ORDER')
DROP PROC RETAIL_CLOUD_EY_SMU_DISPLAY_SELECTED_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_UPDATE_STATUS_ORDER')
DROP PROC RETAIL_CLOUD_EY_SMU_UPDATE_STATUS_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_DISPLAY_ORDER_ALL')
DROP PROC RETAIL_CLOUD_EY_SMU_DISPLAY_ORDER_ALL
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_DISPLAY_SELECTED_ORDER_DETAIL')
DROP PROC RETAIL_CLOUD_EY_SMU_DISPLAY_SELECTED_ORDER_DETAIL
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_CHECK_IS_WINGSKITA')
DROP PROC RETAIL_CLOUD_EY_CHECK_IS_WINGSKITA
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_DISPLAY_TRX_UNFINNISHED')
DROP PROC RETAIL_CLOUD_EY_DISPLAY_TRX_UNFINNISHED
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SMU_DISPLAY_INCOMING_ORDER')
DROP PROC RETAIL_CLOUD_EY_SMU_DISPLAY_INCOMING_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_SAVE_HEADER_TEMP')
DROP PROC RETAIL_CLOUD_EY_SALEOR_SAVE_HEADER_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_SAVE_DETAIL_TEMP')
DROP PROC RETAIL_CLOUD_EY_SALEOR_SAVE_DETAIL_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_CLEAR_TEMP')
DROP PROC RETAIL_CLOUD_EY_SALEOR_CLEAR_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_SAVE_WORK_HIST')
DROP PROC RETAIL_CLOUD_EY_SALEOR_SAVE_WORK_HIST
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_SAVE_ORDER_STATUS_TEMP')
DROP PROC RETAIL_CLOUD_EY_SALEOR_SAVE_ORDER_STATUS_TEMP
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_UPDATE_ORDER_CLOUD')
DROP PROC RETAIL_CLOUD_EY_SALEOR_UPDATE_ORDER_CLOUD
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_TO_BE_PROCESSED')
DROP PROC RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_TO_BE_PROCESSED
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_EXECUTE_ORDER_TO_POS')
DROP PROC RETAIL_CLOUD_EY_SALEOR_EXECUTE_ORDER_TO_POS
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_POS')
DROP PROC RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_POS
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_POS_DETAIL')
DROP PROC RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_POS_DETAIL
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_ORI_DETAIL')
DROP PROC RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_ORI_DETAIL
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMIAPPS_GENERATE_ORDER_KITCHEN_JSON')
DROP PROC RETAIL_CLOUD_EY_FAMIAPPS_GENERATE_ORDER_KITCHEN_JSON
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMIAPPS_UPDATE_ORDER_KITCHEN_JSON')
DROP PROC RETAIL_CLOUD_EY_FAMIAPPS_UPDATE_ORDER_KITCHEN_JSON
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMIAPPS_ADD_SUPPLIES_ITEM')
DROP PROC RETAIL_CLOUD_EY_FAMIAPPS_ADD_SUPPLIES_ITEM
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMIAPPS_CONFIRMED_SUPPLIES_ITEM')
DROP PROC RETAIL_CLOUD_EY_FAMIAPPS_CONFIRMED_SUPPLIES_ITEM
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMIAPPS_DISPLAY_SUPPLIES_ITEM')
DROP PROC RETAIL_CLOUD_EY_FAMIAPPS_DISPLAY_SUPPLIES_ITEM
GO
CREATE PROC RETAIL_CLOUD_EY_DISPLAY_TRX_UNFINNISHED
as
set nocount on
BEGIN TRY
SELECT DOCNUMBR,DOCTYPE,SOURCETRX,WK_UserID,WK_Amount,WK_Businessday,JSONReq FROM RETAIL_TRANSACTION.dbo.TrWingsKitaTrxSync WHERE SyncStat = 0
END TRY
BEGIN CATCH
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_CHECK_IS_WINGSKITA
as
set nocount on
BEGIN TRY
SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'IsWINGSKITA'
END TRY
BEGIN CATCH
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_CLEAR_ORDER_TEMP
@COMPUTERIP VARCHAR(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
DELETE FROM TrSMUOrderHT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM TrSMUOrderDT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM TrSMUOrderPT WHERE COMPUTERIP = @COMPUTERIP
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_COPY_ORDER_TEMP_TO_HIST
@COMPUTERIP VARCHAR(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
CREATE TABLE #Order_to_process(order_id varchar(100))
INSERT INTO #Order_to_process
SELECT order_id FROM TrSMUOrderHT
WHERE order_id NOT IN (SELECT order_id FROM TrSMUOrderH)
AND COMPUTERIP = @COMPUTERIP
INSERT INTO TrSMUOrderP(order_id,payment_type,reference_no,amount,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT order_id,payment_type,reference_no,amount,CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM TrSMUOrderPT
WHERE COMPUTERIP = @COMPUTERIP
AND order_id IN (SELECT order_id FROM #Order_to_process)
INSERT INTO TrSMUOrderD(order_id,material_id,material_desc,ean11,qty_order_in_base,
gross_price,disc_price,net_price,Total,promol_no,additional_notes,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT order_id,material_id,material_desc,ean11,qty_order_in_base,
gross_price,disc_price,net_price,Total,promol_no,additional_notes,
CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM TrSMUOrderDT WHERE COMPUTERIP = @COMPUTERIP
AND order_id IN (SELECT order_id FROM #Order_to_process)
INSERT INTO TrSMUOrderH(order_id,outlet_id,order_date,total_amount,pick_up,queue_no,POSReceipt,Businessday,StoreCode,SHIFTID,POSID,
is_sent,order_type,[user_id],posstat,posmsg,order_status,order_msg,order_sts_DT,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT order_id,outlet_id,order_date,total_amount,pick_up,queue_no,POSReceipt,'','','','',
is_sent,order_type,[user_id],posstat,posmsg,order_status,order_msg,order_sts_DT,CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM TrSMUOrderHT WHERE COMPUTERIP = @COMPUTERIP
AND order_id IN (SELECT order_id FROM #Order_to_process)
SELECT order_id FROM #Order_to_process
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_SAVE_ORDER_HEADER_TEMP
@COMPUTERIP VARCHAR(100),
@order_id varchar(100),
@outlet_id varchar(100),
@order_date datetime,
@total_amount decimal(19,5),
@pick_up datetime,
@order_type varchar(10),
@user_id varchar(100),
@CRUSRID varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF NOT EXISTS (SELECT '' FROM TrSMUOrderHT WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id)
BEGIN
INSERT INTO TrSMUOrderHT(COMPUTERIP,order_id,outlet_id,order_date,total_amount,pick_up,queue_no,POSReceipt,
is_sent,order_type,[user_id],posstat,posmsg,order_status,order_msg,order_sts_DT,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES
(@COMPUTERIP,@order_id,@outlet_id,@order_date,@total_amount,@pick_up,'','',
0,@order_type,@user_id,1,'MENUNGGU DIPROSES',6,'ORDER SEDANG DIPROSES',GETDATE(),
@CRUSRID,GETDATE(),'','1900-01-01')
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_SAVE_ORDER_DETAIL_TEMP
@COMPUTERIP VARCHAR(100),
@order_id varchar(100),
@material_id varchar(100),
@material_desc varchar(200),
@ean11 varchar(100),
@qty_order_in_base decimal(19,5),
@gross_price decimal(19,5),
@disc_price decimal(19,5),
@net_price decimal(19,5),
@Total decimal(19,5),
@promol_no varchar(100),
@additional_notes varchar(8000),
@CRUSRID varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF NOT EXISTS (SELECT '' FROM TrSMUOrderDT WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id AND
material_id = @material_id AND promol_no = @promol_no)
BEGIN
INSERT INTO TrSMUOrderDT(COMPUTERIP,order_id,material_id,material_desc,ean11,
qty_order_in_base,gross_price,disc_price,net_price,Total,promol_no,additional_notes,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES
(@COMPUTERIP,@order_id,@material_id,@material_desc,@ean11,
@qty_order_in_base,@gross_price,@disc_price,@net_price,@Total,@promol_no,@additional_notes,
@CRUSRID,GETDATE(),'','1900-01-01')
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_SAVE_ORDER_PAYMENT_TEMP
@COMPUTERIP VARCHAR(100),
@order_id varchar(100),
@payment_type varchar(100),
@reference_no varchar(200),
@amount decimal(19,5),
@CRUSRID varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF NOT EXISTS (SELECT '' FROM TrSMUOrderPT WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id AND
payment_type = @payment_type)
BEGIN
INSERT INTO TrSMUOrderPT(COMPUTERIP,order_id,payment_type,reference_no,amount,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES
(@COMPUTERIP,@order_id,@payment_type,@reference_no,@amount,
@CRUSRID,GETDATE(),'','1900-01-01')
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_DISPLAY_ORDER_TO_SEND
as
set nocount on
BEGIN TRY
BEGIN TRAN
SELECT order_id,POSReceipt,order_status,order_msg,order_sts_DT,queue_no FROM TrSMUOrderH WHERE is_sent = 0
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_UPDATE_IS_SENT_ORDER
@order_id varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
UPDATE TrSMUOrderH SET is_sent = 1 WHERE order_id = @order_id
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_UPDATE_UNSENT_ORDER
@order_id varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
UPDATE TrSMUOrderH SET is_sent = 0 WHERE order_id = @order_id
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_DISPLAY_ORDER_TO_BE_PROCESSED
@StoreCode varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
declare @limit_min int = -15
declare @limit_max int = 15
IF EXISTS
(
SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'KITCHEN_LIMIT_TIME'
)
BEGIN
SET @limit_min = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'KITCHEN_LIMIT_TIME') * -1
SET @limit_max = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'KITCHEN_LIMIT_TIME')
END
ELSE
BEGIN
SET @limit_min = -15
SET @limit_max = 15
END
EXEC('
SELECT TOP 1 order_id,creatdt,SUBSTRING(CONVERT(VARCHAR,CONVERT(TIME,pick_up)),1,5) as pick_up,is_manual_finish,transtype FROM
(
SELECT order_id,order_date as creatdt,pick_up,0 as is_manual_finish,order_type as transtype,posmsg FROM TrSMUOrderH
WHERE POSReceipt = '''' AND posstat = 1 AND total_amount > 0 AND
order_type IN (''A'')
AND SUBSTRING(CONVERT(VARCHAR,GETDATE(),108),1,5) BETWEEN
SUBSTRING(CONVERT(VARCHAR,DATEADD(MINUTE,'+@limit_min+',CONVERT(TIME,pick_up,108))),1,5) AND
SUBSTRING(CONVERT(VARCHAR,DATEADD(MINUTE,'+@limit_max+',CONVERT(TIME,pick_up,108))),1,5)
AND outlet_id = '''+@StoreCode+'''
UNION
SELECT order_id,order_date as creatdt,pick_up,0 as is_manual_finish,order_type as transtype,posmsg FROM TrSMUOrderH
WHERE POSReceipt = '''' AND posstat = 1 AND total_amount > 0
AND order_type IN (''B'',''C'')
AND outlet_id = '''+@StoreCode+'''
UNION
SELECT order_id,order_date as creatdt,pick_up,0 as is_manual_finish,order_type as transtype,posmsg FROM TrSMUOrderH
WHERE POSReceipt = '''' AND posstat = 1 AND total_amount > 0
AND order_type IN (''A'')
AND CONVERT(DATE,GETDATE(),103) <> CONVERT(DATE,order_date,103)
AND outlet_id = '''+@StoreCode+'''
UNION
SELECT order_id,order_date as creatdt,pick_up,0 as is_manual_finish,order_type as transtype,posmsg FROM TrSMUOrderH
WHERE POSReceipt = '''' AND posstat = 1 AND total_amount > 0
AND order_type IN (''A'')
AND CONVERT(varchar,pick_up,112) + REPLACE(CONVERT(varchar,pick_up,108),'':'','''') < CONVERT(varchar,GETDATE(),112) + REPLACE(CONVERT(varchar,GETDATE(),108),'':'','''')
AND outlet_id = '''+@StoreCode+'''
)X
')
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_EXECUTE_ORDER_TO_POS
@COMPUTERIP VARCHAR(100),
@StoreCode VARCHAR(100),
@POSID VARCHAR(100),
@SHIFTID VARCHAR(100),
@Businessday VARCHAR(100),
@CLOUD_DOCNUMBR VARCHAR(100),
@SYS_USR VARCHAR(100),
@LSMEMBERSHIP varchar(100),
@COMPANYID varchar(100),
@COMPANYPCID varchar(100)
as
set nocount on
BEGIN TRY
IF EXISTS (SELECT '' FROM TrSMUOrderH WHERE order_id = @CLOUD_DOCNUMBR AND POSReceipt <> '')
BEGIN
RAISERROR ('Sales is already downloaded.',16,1)
END
DECLARE @POSID_ONLINE AS VARCHAR(100) = ''
SET @POSID_ONLINE = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'POS_ID_ONLINE')
SET @POSID_ONLINE = ISNULL(@POSID_ONLINE,'')
IF @POSID <> @POSID_ONLINE
BEGIN
RAISERROR ('POS ID Online is invalid.',16,1)
END
IF EXISTS (SELECT '' FROM TrSMUOrderD WHERE order_id = @CLOUD_DOCNUMBR AND FORMAT(CONVERT(INT,material_id), '000000000000000000')
NOT IN (SELECT ItemCode FROM RETAIL_MASTER.dbo.MItem))
BEGIN
DECLARE @NOT_REGISTERED_ITEM VARCHAR(8000) = (SELECT TOP 1 'Item is not registered.' + material_id + '|' + material_desc + '|' + ean11 FROM TrSMUOrderD WHERE order_id = @CLOUD_DOCNUMBR AND FORMAT(CONVERT(INT,material_id), '000000000000000000')
NOT IN (SELECT ItemCode FROM RETAIL_MASTER.dbo.MItem))
RAISERROR (@NOT_REGISTERED_ITEM,16,1)
END
BEGIN TRY
BEGIN TRAN
DECLARE @MSG_ERR VARCHAR(8000) = ''
DECLARE @SALES_TAX as decimal(19,5)=0
DECLARE @order_num as int,@Barcode varchar(100), @article as varchar(100), @qty as decimal(17,5)=0, @price as decimal(19,5) = 0, @notes as varchar(500)='',@disc as decimal(19,5)=0,@promocode as varchar(100) = ''
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesTenderT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesTenderTT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesAdditionalT WHERE COMPUTERIP = @COMPUTERIP
DECLARE CSR_DOWNLOAD CURSOR FOR
SELECT ROW_NUMBER() OVER(ORDER BY material_id ASC,promol_no ASC) as order_num,ean11,material_id,qty_order_in_base,gross_price,additional_notes,disc_price,promol_no FROM TrSMUOrderD WHERE Order_id = @CLOUD_DOCNUMBR
OPEN CSR_DOWNLOAD
FETCH NEXT FROM CSR_DOWNLOAD INTO @order_num,@Barcode,@article,@qty,@price,@notes,@disc,@promocode
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE
@ItemDescriptionLong varchar(500)='',
@ItemDescriptionShort varchar(50)='',
@ItemType varchar(20),
@TaxCode varchar(20),
@OpenPriceFlg bit,
@ItemValue as decimal(19,5)=0,
@ReturnAble as bit,
@MerchCat varchar(50),
@MerchCatDesc varchar(100),
@Category varchar(50),
@CategoryDesc varchar(100),
@Department varchar(50),
@DepartmentDesc varchar(100),
@Division varchar(50),
@DivisionDesc varchar(100),
@Tax decimal(19,5) = 0,
@Subtotal decimal(19,5) = 0,
@DPP decimal(19,5) = 0,
@Total decimal(19,5) = 0
SELECT TOP 1 @ItemDescriptionLong = ItemDescriptionLong,
@ItemDescriptionShort = ItemDescriptionShort, @ItemType = ItemType,
@TaxCode = TaxCode, @OpenPriceFlg = OpenPriceFlg,
@ReturnAble = ReturnAble,
@MerchCat = MItem.MerchCat, @MerchCatDesc = MerchCatDesc,
@Category = Category, @CategoryDesc = CategoryDesc,
@Department = Department, @DepartmentDesc = DepartmentDesc,
@Division = Division, @DivisionDesc = DivisionDesc
FROM RETAIL_MASTER.dbo.MItem
LEFT JOIN RETAIL_MASTER.dbo.MMerchandiseCategory ON MItem.MerchCat = MMerchandiseCategory.MerchCat
where Barcode = @Barcode
AND TaxCode <> ''
SET @SALES_TAX = ISNULL((SELECT TOP 1 CONVERT(float,TaxValue)+1 FROM RETAIL_SYSTEMS.dbo.MTaxSetup WHERE TaxCode = @TaxCode AND GETDATE() BETWEEN StartDT AND EndDT ORDER BY CREATDT DESC),0)
IF @Barcode = ''
BEGIN
SET @MSG_ERR = @article + 'is not registered.' + @article + ' ' + @Barcode
RAISERROR (@MSG_ERR,16,1)
END
SET @Subtotal = @qty * (@price - @disc)
SET @Total = @Subtotal
IF @TaxCode <> 0
BEGIN
SET @Tax = ROUND(@Subtotal - (@Subtotal/@SALES_TAX),0)
END
SET @DPP = @Subtotal - @Tax
INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesT
(StoreCode,BusinessDay,POSID,SHIFTID,COMPUTERIP,LNITMSEQ,Barcode,ItemCode,ItemDescriptionLong,ItemDescriptionShort,ItemType,TaxCode,MerchCat,MerchCatDesc,
Category,CategoryDesc,Department,DepartmentDesc,Division,DivisionDesc,OpenPriceFlg,UnitPrice,Qty,Disc,Subtotal,DPP,Tax,Total,QtySales,SalesDisc,SalesSubtotal,SalesDPP,SalesTax,SalesTotal,QtyReturned,ReturnedDisc,ReturnedSubtotal,ReturnedDPP,ReturnedTax,ReturnedTotal,ItemValue,CRUSRID,CREATDT,MDFUSRID,MODIFDT,ReturnAble,PromoCode)
VALUES
(@StoreCode,@BusinessDay,@POSID,@SHIFTID,@COMPUTERIP,@order_num,@Barcode,
@article,@ItemDescriptionLong,@ItemDescriptionShort,@ItemType,@TaxCode,@MerchCat,@MerchCatDesc,
@Category,@CategoryDesc,@Department,@DepartmentDesc,@Division,@DivisionDesc,@OpenPriceFlg,@price,@qty,@disc,@Subtotal,@DPP,@Tax,@Total,@qty,@disc,@Subtotal,@DPP,@Tax,@Total,0,0,0,0,0,0,@ItemValue,@SYS_USR,GETDATE(),'','1900-01-01 00:00:00.000',@ReturnAble,@promocode)
INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesAdditionalT(COMPUTERIP,LNITMSEQ,USERDEFINEID,USERDEFINEVALUE,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES
(@COMPUTERIP,@order_num,@order_num,@notes,@SYS_USR,GETDATE(),'','1900-01-01 00:00:00.000')
FETCH NEXT FROM CSR_DOWNLOAD INTO @order_num,@Barcode,@article,@qty,@Price,@notes,@disc,@promocode
END
CLOSE CSR_DOWNLOAD
DEALLOCATE CSR_DOWNLOAD
---------------------------------------PAYMENT PROCESSING----------------------------------------------
DECLARE @paymentType varchar(100) = '',@ReferenceNo varchar(100) = '',@PaidAmount DECIMAL(19,5) = 0
DECLARE @TotalPaidAmount DECIMAL(19,5)=0
DECLARE CSR_DOWNLOAD_PAYMENT CURSOR FOR SELECT payment_type,reference_no,amount FROM TrSMUOrderP
WHERE Order_id = @CLOUD_DOCNUMBR
OPEN CSR_DOWNLOAD_PAYMENT
FETCH NEXT FROM CSR_DOWNLOAD_PAYMENT INTO @paymentType,@ReferenceNo,@PaidAmount
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalPaidAmount = @TotalPaidAmount + @PaidAmount
EXEC RETAIL_TRANSACTION.dbo.TRX_EY_SAVE_SALES_TRANSACTION_TENDER_TEMP
@StoreCode, @BusinessDay, @POSID, @SHIFTID, @COMPUTERIP, @paymentType, @PaidAmount, @PaidAmount, @ReferenceNo, @SYS_USR
FETCH NEXT FROM CSR_DOWNLOAD_PAYMENT INTO @paymentType,@ReferenceNo,@PaidAmount
END
CLOSE CSR_DOWNLOAD_PAYMENT
DEALLOCATE CSR_DOWNLOAD_PAYMENT
DECLARE @DPP_TOTAL as decimal(19,5) = 0, @Items_TOTAL as int = 0, @Tax_TOTAL as decimal(19,5) = 0
SET @DPP_TOTAL = (SELECT ISNULL(SUM(DPP),0) FROM RETAIL_TRANSACTION.dbo.TrSalesT
WHERE COMPUTERIP = @COMPUTERIP
AND TaxCode <> '0'
AND Barcode NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItemDonation))
SET @DPP_TOTAL = ISNULL(@DPP_TOTAL,0)
SELECT @Items_TOTAL = ISNULL(ItemCount,0),
@Tax_TOTAL = ISNULL(Tax,0)
FROM
(
SELECT ISNULL(SUM(Total),0) as Total,@DPP as DPP,ISNULL(SUM(Tax),0) as Tax,ISNULL(SUM(Subtotal),0) as Subtotal FROM RETAIL_TRANSACTION.dbo.TrSalesT
WHERE COMPUTERIP = @COMPUTERIP
)TOTAL
LEFT JOIN
(
SELECT CEILING(ISNULL(SUM(Qty),0)) as ItemCount
FROM RETAIL_TRANSACTION.dbo.TrSalesT WHERE COMPUTERIP = @COMPUTERIP
AND Barcode NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItemReceiptDisallow)
)ITEM_COUNT ON 1=1
EXEC RETAIL_TRANSACTION.dbo.TRX_EY_POST_SALES_TRANSACTION
@StoreCode,@BusinessDay,@POSID,@SHIFTID,@COMPUTERIP,@TotalPaidAmount,@DPP_TOTAL,
@Tax_TOTAL,@TotalPaidAmount,@Items_TOTAL,@TotalPaidAmount,0,@TotalPaidAmount,
@CLOUD_DOCNUMBR,'','',@SYS_USR,'','SALES TRANSACTION','',@LSMEMBERSHIP,@COMPANYID,@COMPANYPCID,
0,0,0
DECLARE @DOCNUMBR_POST VARCHAR(100) = (SELECT TOP 1 DOCNUMBR FROM RETAIL_TRANSACTION.dbo.TrSalesHH WHERE ORIDOCNUMBR = @CLOUD_DOCNUMBR ORDER BY CREATDT DESC)
EXEC RETAIL_TRANSACTION.dbo.TRX_EY_POST_AGE_GENDER_CUSTOMER
@StoreCode,
@BusinessDay,
@POSID,
@SHIFTID,
@SYS_USR,
@DOCNUMBR_POST,
'SALES TRANSACTION',
'WingsKita'
UPDATE RETAIL_TRANSACTION.dbo.TrKitchenHH SET TRXSOURCE = RIGHT(@CLOUD_DOCNUMBR,1) WHERE DOCNUMBR = @DOCNUMBR_POST
UPDATE TrSMUOrderH SET POSReceipt = @DOCNUMBR_POST, StoreCode = @StoreCode, Businessday = @Businessday, POSID = @POSID, SHIFTID = @SHIFTID,
posstat = 0, posmsg = 'SUCCESS',Is_sent = 0,
MDFUSRID = @COMPUTERIP,MODIFDT = GETDATE()
WHERE Order_Id = @CLOUD_DOCNUMBR
COMMIT
END TRY
BEGIN CATCH
IF @@trancount > 0
BEGIN
ROLLBACK
END
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
CLOSE CSR_DOWNLOAD
DEALLOCATE CSR_DOWNLOAD
CLOSE CSR_DOWNLOAD_PAYMENT
DEALLOCATE CSR_DOWNLOAD_PAYMENT
END CATCH
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
UPDATE TrSMUOrderH SET posstat = 1, posmsg = @ERROR_ALL
WHERE order_id = @CLOUD_DOCNUMBR
RAISERROR(@ERROR_ALL,16,1)
END CATCH
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_UPDATE_QUEUE_NO_ORDER
@CLOUD_DOCNUMBR VARCHAR(100),
@QUEUENO VARCHAR(100)
as
set nocount on
DECLARE @order_type as varchar(10) = (SELECT order_type FROM TrSMUOrderH WHERE order_id = @CLOUD_DOCNUMBR)
IF @order_type = 'A'
BEGIN
IF @QUEUENO <> '???'
BEGIN
UPDATE TrSMUOrderH SET queue_no = @QUEUENO,is_sent = 0,
order_status = 12,
order_msg = 'NOMOR ANTRIAN ANDA : ' + @QUEUENO,
order_sts_DT = GETDATE()
WHERE order_id = @CLOUD_DOCNUMBR
END
END
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_DISPLAY_SELECTED_ORDER
@POS_DOCNUMBR VARCHAR(100)
as
set nocount on
SELECT order_id,TrSMUOrderH.order_date as creatdt,SUBSTRING(CONVERT(VARCHAR,CONVERT(TIME,pick_up)),1,5) as pick_up,0 as is_manual_finish,order_type
FROM TrSMUOrderH
WHERE POSReceipt = @POS_DOCNUMBR
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_UPDATE_STATUS_ORDER
@order_id varchar(100),
@order_status int,
@order_msg varchar(200)
as
set nocount on
UPDATE TrSMUOrderH SET order_status = @order_status, order_msg = @order_msg, order_sts_DT = GETDATE(),is_sent = 0
WHERE order_id = @order_id
set nocount off
GO
--ketika barista teken tombol centang ijo, maka update seperti ini :
--RETAIL_CLOUD_EY_SMU_UPDATE_STATUS_ORDER '2010051907.5908-A',10,'ORDER SELESAI'
--ketika barista teken tombol pick up order, maka update seperti ini :
--RETAIL_CLOUD_EY_SMU_UPDATE_STATUS_ORDER '2010051907.5908-B',13,'ORDER SIAP DIAMBIL DI TOKO'
--ketika barista teken tombol centang ijo, maka update seperti ini :
--RETAIL_CLOUD_EY_SMU_UPDATE_STATUS_ORDER '2010061331.5908-B',10,'ORDER SELESAI'
--RETAIL_CLOUD_EY_SMU_UPDATE_STATUS_ORDER '2010051907.5908-C',13,'ORDER SIAP DIAMBIL DI TOKO'
--RETAIL_CLOUD_EY_SMU_UPDATE_STATUS_ORDER '2010051907.5908-C',10,'ORDER SELESAI'
CREATE PROC RETAIL_CLOUD_EY_SMU_DISPLAY_ORDER_ALL
@StoreCode varchar(100),
@WHERECOND VARCHAR(8000)
as
set nocount on
EXEC('
SELECT [Order ID],[Order Date],[Order Type],[Order Amount],[Returned Amount],[Order Total],[POS Receipt No],[Status Code],[Status Message],[Last Updated],[Pick Up Time],[Queue No],[Processing Time (Hour)],[User ID]
FROM
(
SELECT order_id as [Order ID],CONVERT(DATE,order_date,103) as [Order Date],order_type as [Order Type],
total_amount as [Order Amount],ISNULL(ReturnedTotal*-1,0) as [Returned Amount],
total_amount - ISNULL(ReturnedTotal,0) as [Order Total],
POSReceipt as [POS Receipt No],
order_status [Status Code],order_msg as [Status Message],order_sts_DT as [Last Updated],
CASE WHEN order_type = ''A'' THEN
LEFT(CONVERT(VARCHAR,CONVERT(TIME,pick_up,108)),5)
ELSE
''''
END
as [Pick Up Time],
queue_no as [Queue No],CREATDT,
CASE
WHEN order_status = ''10'' OR order_status = ''14'' THEN DATEDIFF(hour,order_date,order_sts_DT)
ELSE
DATEDIFF(hour,order_date,GETDATE())
END as [Processing Time (Hour)],
[user_id] as [User ID]
FROM TrSMUOrderH
LEFT JOIN
(
SELECT DOCNUMBR,ISNULL(SUM(ReturnedTotal),0) as ReturnedTotal FROM RETAIL_TRANSACTION.dbo.TrSalesDH
GROUP BY DOCNUMBR
)DETAIL
ON TrSMUOrderH.POSReceipt = DETAIL.DOCNUMBR
WHERE DATEDIFF(day,order_date,GETDATE()) < 240
AND outlet_id = '''+@StoreCode+'''
AND POSReceipt <> ''''
)SALES
'+@WHERECOND+'
ORDER BY [Processing Time (Hour)] DESC')
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_DISPLAY_SELECTED_ORDER_DETAIL
@StoreCode varchar(100),
@order_id varchar(100)
as
set nocount on
SELECT [Order ID],[Order Date],[Order Type],[Order Amount],[Returned Amount],[Order Total],[POS Receipt No],[Status Code],[Status Message],[Last Updated],[Pick Up Time],[Queue No],[Processing Time (Hour)],[User ID]
FROM
(
SELECT order_id as [Order ID],order_date as [Order Date],order_type as [Order Type],
total_amount as [Order Amount],ISNULL(ReturnedTotal*-1,0) as [Returned Amount],
total_amount - ISNULL(ReturnedTotal,0) as [Order Total],
POSReceipt as [POS Receipt No],
order_status [Status Code],order_msg as [Status Message],order_sts_DT as [Last Updated],
LEFT(CONVERT(VARCHAR,CONVERT(TIME,pick_up,108)),5) as [Pick Up Time],
queue_no as [Queue No],CREATDT,
CASE
WHEN order_status = '10' THEN DATEDIFF(hour,order_date,order_sts_DT)
ELSE
DATEDIFF(hour,order_date,GETDATE())
END as [Processing Time (Hour)],
[user_id] as [User ID]
FROM TrSMUOrderH
LEFT JOIN
(
SELECT DOCNUMBR,ISNULL(SUM(ReturnedTotal),0) as ReturnedTotal FROM RETAIL_TRANSACTION.dbo.TrSalesDH
GROUP BY DOCNUMBR
)DETAIL
ON TrSMUOrderH.POSReceipt = DETAIL.DOCNUMBR
WHERE DATEDIFF(day,order_date,GETDATE()) < 240
AND outlet_id = @StoreCode
AND order_id = @order_id
AND POSReceipt <> ''
)SALES
DECLARE @POSReceiptNo VARCHAR(100) = (SELECT TOP 1 POSReceipt FROM TrSMUOrderH WHERE Order_id = @order_id)
SELECT ItemCode as [Item Code],Barcode as [Barcode],ItemDescriptionLong as [Item Description],Qty as [Qty Order],QtyReturned*-1 as [Qty Returned],Qty-QtyReturned as [Qty Total],(Qty-QtyReturned)*(UnitPrice-disc) as [Total Amount] FROM RETAIL_TRANSACTION.dbo.TrSalesDH WHERE DOCNUMBR = @POSReceiptNo
SELECT Payment_Type as [Tender ID], Reference_No as [Reference No],Amount as [Paid Amount],ReturnedAmount*-1 as [Returned Amount] FROM TrSMUOrderP
LEFT JOIN TrSMUOrderH ON TrSMUOrderP.order_id = TrSMUOrderH.order_id
LEFT JOIN RETAIL_TRANSACTION.dbo.TrSalesHH ON TrSMUOrderP.order_id = TrSalesHH.ORIDOCNUMBR
LEFT JOIN RETAIL_TRANSACTION.dbo.TrSalesTenderHH ON TrSalesHH.DOCNUMBR = TrSalesTenderHH.DOCNUMBR AND TrSalesTenderHH.TenderID = TrSMUOrderP.Payment_Type
WHERE
outlet_id = @StoreCode AND
TrSMUOrderP.order_id = @order_id
AND Amount>0
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SMU_DISPLAY_INCOMING_ORDER
@StoreCode varchar(100),
@WHERECOND VARCHAR(8000)
as
set nocount on
BEGIN TRY
BEGIN TRAN
declare @limit VARCHAR(100) = 15
IF EXISTS
(
SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'KITCHEN_LIMIT_TIME'
)
BEGIN
SET @limit = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'KITCHEN_LIMIT_TIME')
END
ELSE
BEGIN
SET @limit = 15
END
EXEC('
SELECT [Pick Up Time],[Item Description],[Qty],[Order ID],[Order Date],[Order Type],
[Barcode],[User ID]
FROM
(
SELECT TrSMUOrderH.order_id as [Order ID],order_date as [Order Date],SUBSTRING(CONVERT(VARCHAR,CONVERT(TIME,pick_up)),1,5) as [Pick Up Time],order_type as [Order Type],
material_desc as [Item Description],ean11 as [Barcode],qty_order_in_base as [Qty],User_id as [User ID]
FROM TrSMUOrderH
LEFT JOIN TrSMUOrderD ON TrSMUOrderH.order_id = TrSMUOrderD.order_id
WHERE
order_type IN (''A'')
AND DATEDIFF(MINUTE,GETDATE(),DATEADD(MINUTE,'+@limit+',pick_up)) > '+@limit+'
AND outlet_id = '''+@StoreCode+'''
AND POSReceipt = '''' AND posstat = 1
)X
' + @WHERECOND)
EXEC('
SELECT ISNULL(SUM([Qty]),0) AS [TOTAL QTY]
FROM
(
SELECT TrSMUOrderH.order_id as [Order ID],order_date as [Order Date],SUBSTRING(CONVERT(VARCHAR,CONVERT(TIME,pick_up)),1,5) as [Pick Up Time],order_type as [Order Type],
material_desc as [Item Description],ean11 as [Barcode],qty_order_in_base as [Qty],User_id as [User ID]
FROM TrSMUOrderH
LEFT JOIN TrSMUOrderD ON TrSMUOrderH.order_id = TrSMUOrderD.order_id
WHERE
order_type IN (''A'')
AND DATEDIFF(MINUTE,GETDATE(),DATEADD(MINUTE,'+@limit+',pick_up)) > '+@limit+'
AND outlet_id = '''+@StoreCode+'''
AND POSReceipt = '''' AND posstat = 1
)X
' + @WHERECOND)
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_SAVE_HEADER_TEMP
@COMPUTERIP VARCHAR(100),
@id varchar(100),
@order_id varchar(100),
@outlet_id varchar(100),
@point_amt DECIMAL(19,5),
@point_reference varchar(100),
@status varchar(100),
@channel varchar(100),
@local_id varchar(100),
@local_name varchar(100),
@ordered_at datetime,
@payment_method varchar(100),
@payment_status varchar(100),
@total_price DECIMAL(19,5),
@shipping_price DECIMAL(19,5),
@discount_shipping DECIMAL(19,5),
@channel_rebate DECIMAL(19,5),
@discount_amount DECIMAL(19,5),
@customer_info_id varchar(100),
@customer_info_name varchar(100),
@customer_info_email varchar(100),
@customer_info_since datetime,
@customer_address_address_1 varchar(500),
@customer_address_address_2 varchar(500),
@customer_address_city varchar(100),
@customer_address_country varchar(100),
@customer_address_name varchar(100),
@customer_address_phone varchar(100),
@customer_address_postal_code varchar(100),
@customer_address_province varchar(100),
@customer_address_province_code varchar(100),
@customer_address_sub_district varchar(100),
@customer_address_district varchar(100),
@customer_address_coordinate varchar(100),
@order_status varchar(100),
@discount_type varchar(100),
@order_type varchar(100),
@CRUSRID varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
INSERT INTO TrSaleorHT(COMPUTERIP,id,order_id,outlet_id,point_amt,point_reference,[status],channel,local_id,
local_name,ordered_at,payment_method,payment_status,total_price,shipping_price,discount_shipping,channel_rebate,discount_amount,
customer_info_id,customer_info_name,customer_info_email,customer_info_since,
customer_address_address_1,customer_address_address_2,customer_address_city,customer_address_country,customer_address_name,customer_address_phone,customer_address_postal_code,
customer_address_province,customer_address_province_code,customer_address_sub_district,customer_address_district,customer_address_coordinate,
order_status,discount_type,order_type,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES
(@COMPUTERIP,@id,@order_id,@outlet_id,@point_amt,@point_reference,@status,@channel,@local_id,
@local_name,@ordered_at,@payment_method,@payment_status,@total_price,@shipping_price,@discount_shipping,@channel_rebate,@discount_amount,
@customer_info_id,@customer_info_name,@customer_info_email,@customer_info_since,
@customer_address_address_1,@customer_address_address_2,@customer_address_city,@customer_address_country,@customer_address_name,@customer_address_phone,@customer_address_postal_code,
@customer_address_province,@customer_address_province_code,@customer_address_sub_district,@customer_address_district,@customer_address_coordinate,
@order_status,ISNULL(@discount_type,'voucher'),ISNULL(@order_type,'reguler'),@CRUSRID,GETDATE(),'','1900-01-01')
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_SAVE_DETAIL_TEMP
@COMPUTERIP VARCHAR(100),
@id varchar(100),
@order_id varchar(100),
@local_id varchar(100),
@order_num int,
@variant_sku varchar(100),
@qty DECIMAL(17,5),
@price DECIMAL(19,5),
@sale_price DECIMAL(19,5),
@total_price DECIMAL(19,5),
@CRUSRID varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
INSERT INTO TrSaleorDT(COMPUTERIP,id,order_id,local_id,order_num,variant_sku,qty,price,sale_price,total_price,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES
(@COMPUTERIP,@id,@order_id,@local_id,@order_num,@variant_sku,@qty,@price,@sale_price,@total_price,
@CRUSRID,GETDATE(),'','1900-01-01')
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_CLEAR_TEMP
@COMPUTERIP VARCHAR(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
DELETE FROM TrSaleorHT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM TrSaleorDT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM SALEOR_ORDER_STATUS_T WHERE COMPUTERIP = @COMPUTERIP
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_SAVE_WORK_HIST
@COMPUTERIP VARCHAR(100)
as
set nocount on
BEGIN TRY
CREATE TABLE #SYNC_ORDER_SALEOR (order_id varchar(100),Status_Updated varchar(100))
BEGIN TRAN
DECLARE @order_id_loop varchar(100)
DECLARE CSR_LOOP_TRX CURSOR FOR SELECT order_id FROM TrSaleorHT WHERE COMPUTERIP = @COMPUTERIP
OPEN CSR_LOOP_TRX
FETCH NEXT FROM CSR_LOOP_TRX INTO @order_id_loop
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
DECLARE @id varchar(100),
@order_id varchar(100),
@outlet_id varchar(100),
@point_amt DECIMAL(19,5),
@point_reference varchar(100),
@status varchar(100),
@channel varchar(100),
@local_id varchar(100),
@local_name varchar(100),
@ordered_at datetime,
@payment_method varchar(100),
@payment_status varchar(100),
@total_price DECIMAL(19,5),
@shipping_price DECIMAL(19,5),
@discount_shipping DECIMAL(19,5),
@channel_rebate DECIMAL(19,5),
@discount_amount DECIMAL(19,5),
@customer_info_id varchar(100),
@customer_info_name varchar(100),
@customer_info_email varchar(100),
@customer_info_since datetime,
@customer_address_address_1 varchar(500),
@customer_address_address_2 varchar(500),
@customer_address_city varchar(100),
@customer_address_country varchar(100),
@customer_address_name varchar(100),
@customer_address_phone varchar(100),
@customer_address_postal_code varchar(100),
@customer_address_province varchar(100),
@customer_address_province_code varchar(100),
@customer_address_sub_district varchar(100),
@customer_address_district varchar(100),
@customer_address_coordinate varchar(100),
@order_status varchar(100),
@discount_type varchar(100),
@order_type varchar(100)
DECLARE CSR_LOOP_H CURSOR FOR SELECT id,order_id,outlet_id,point_amt,point_reference,[status],channel,local_id,
local_name,ordered_at,payment_method,payment_status,total_price,shipping_price,discount_shipping,channel_rebate,discount_amount,
customer_info_id,customer_info_name,customer_info_email,customer_info_since,
customer_address_address_1,customer_address_address_2,customer_address_city,customer_address_country,customer_address_name,customer_address_phone,customer_address_postal_code,
customer_address_province,customer_address_province_code,customer_address_sub_district,customer_address_district,customer_address_coordinate,
order_status,discount_type,order_type FROM TrSaleorHT WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop
OPEN CSR_LOOP_H
FETCH NEXT FROM CSR_LOOP_H INTO @id,@order_id,@outlet_id,@point_amt,@point_reference,@status,@channel,@local_id,
@local_name,@ordered_at,@payment_method,@payment_status,@total_price,@shipping_price,@discount_shipping,@channel_rebate,@discount_amount,
@customer_info_id,@customer_info_name,@customer_info_email,@customer_info_since,
@customer_address_address_1,@customer_address_address_2,@customer_address_city,@customer_address_country,@customer_address_name,@customer_address_phone,@customer_address_postal_code,
@customer_address_province,@customer_address_province_code,@customer_address_sub_district,@customer_address_district,@customer_address_coordinate,
@order_status,@discount_type,@order_type
CLOSE CSR_LOOP_H
DEALLOCATE CSR_LOOP_H
IF NOT EXISTS (SELECT '' FROM SALEOR_DOWNLOAD WHERE order_id = @order_id_loop) --tidak terdownload
OR EXISTS (SELECT '' FROM SALEOR_DOWNLOAD WHERE order_id = @order_id_loop AND RC <> '200') --tidak ok
BEGIN
DELETE FROM TrSaleorHW WHERE order_id = @order_id_loop
DELETE FROM TrSaleorDW WHERE order_id = @order_id_loop
DELETE FROM TrSaleorHH WHERE order_id = @order_id_loop
DELETE FROM TrSaleorDH WHERE order_id = @order_id_loop
DELETE FROM SALEOR_ORDER_STATUS WHERE order_id = @order_id_loop
INSERT INTO TrSaleorHW(id,order_id,outlet_id,point_amt,point_reference,[status],channel,local_id,
local_name,ordered_at,payment_method,payment_status,total_price,shipping_price,discount_shipping,channel_rebate,discount_amount,
customer_info_id,customer_info_name,customer_info_email,customer_info_since,
customer_address_address_1,customer_address_address_2,customer_address_city,customer_address_country,customer_address_name,customer_address_phone,customer_address_postal_code,
customer_address_province,customer_address_province_code,customer_address_sub_district,customer_address_district,customer_address_coordinate,
order_status,discount_type,order_type,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT id,order_id,outlet_id,point_amt,point_reference,[status],channel,local_id,
local_name,ordered_at,payment_method,payment_status,total_price,shipping_price,@discount_shipping,channel_rebate,discount_amount,
customer_info_id,customer_info_name,customer_info_email,customer_info_since,
customer_address_address_1,customer_address_address_2,customer_address_city,customer_address_country,customer_address_name,customer_address_phone,customer_address_postal_code,
customer_address_province,customer_address_province_code,customer_address_sub_district,customer_address_district,customer_address_coordinate,
order_status,discount_type,order_type,CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM TrSaleorHT
WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop
INSERT INTO TrSaleorDW(id,order_id,local_id,order_num,variant_sku,qty,price,sale_price,total_price,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT id,order_id,local_id,order_num,variant_sku,qty,price,sale_price,total_price,
CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM TrSaleorDT
WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop
IF ISNULL(@discount_type,'voucher') = 'voucher' --discount amount split to line item, header discount amount = 0
BEGIN
INSERT INTO TrSaleorHH(id,order_id,outlet_id,point_amt,point_reference,[status],channel,local_id,
local_name,ordered_at,payment_method,payment_status,total_price,shipping_price,discount_shipping,channel_rebate,discount_amount,
customer_info_id,customer_info_name,customer_info_email,customer_info_since,
customer_address_address_1,customer_address_address_2,customer_address_city,customer_address_country,customer_address_name,customer_address_phone,customer_address_postal_code,
customer_address_province,customer_address_province_code,customer_address_sub_district,customer_address_district,customer_address_coordinate,
order_status,discount_type,order_type,payment_method_POS,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT id,order_id,outlet_id,point_amt,point_reference,[status],channel,local_id,
local_name,ordered_at,payment_method,payment_status,total_price,shipping_price,@discount_shipping,channel_rebate,0,
customer_info_id,customer_info_name,customer_info_email,customer_info_since,
customer_address_address_1,customer_address_address_2,customer_address_city,customer_address_country,customer_address_name,customer_address_phone,customer_address_postal_code,
customer_address_province,customer_address_province_code,customer_address_sub_district,customer_address_district,customer_address_coordinate,
order_status,discount_type,order_type,
CASE
WHEN payment_method = 'Kartu Kredit MASTERCARD' THEN 'Kartu Kredit MASTERCARD'
WHEN payment_method = 'Kartu Kredit VISA' THEN 'Kartu Kredit VISA'
WHEN payment_method = 'Kartu Kredit JCB' THEN 'Kartu Kredit JCB'
WHEN payment_method = 'Kartu Kredit' THEN 'Kartu Kredit'
WHEN payment_method like '%Virtual Account%' AND payment_method not like '%BCA%' THEN 'Virtual Account'
WHEN payment_method like '%Virtual Account%' AND payment_method like '%BCA%' THEN 'Virtual Account BCA'
WHEN payment_method = 'QRIS' THEN 'QRIS'
ELSE
'UNKNOWN'
END
as payment_method_POS,
CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM TrSaleorHT
WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop
DECLARE @TOTAL_TRX as DECIMAL(19,5) = (SELECT ISNULL(SUM(Qty*Price),0) FROM TrSaleorDT WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop)
INSERT INTO TrSaleorDH(id,order_id,local_id,order_num,variant_sku,qty,price,sale_price,total_price,
Price_after_voucher,sale_price_voucher,total_price_after_voucher,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT id,order_id,local_id,order_num,variant_sku,qty,price,sale_price,qty*price,
Price - (ROUND((price/(@TOTAL_TRX) * @discount_amount),0)),
ROUND((price/(@TOTAL_TRX) * @discount_amount),0),
(Qty*Price) - (ROUND((price/(@TOTAL_TRX) * @discount_amount),0)*Qty),
CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM TrSaleorDT
WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop
AND (variant_sku not like 'erp-%' AND variant_sku not like 'level-%' AND variant_sku <> 'null' AND variant_sku not like 'opt-%')
END
ELSE --discount amount go to footer, header discount amount stay
BEGIN
INSERT INTO TrSaleorHH(id,order_id,outlet_id,point_amt,point_reference,[status],channel,local_id,
local_name,ordered_at,payment_method,payment_status,total_price,shipping_price,discount_shipping,channel_rebate,discount_amount,
customer_info_id,customer_info_name,customer_info_email,customer_info_since,
customer_address_address_1,customer_address_address_2,customer_address_city,customer_address_country,customer_address_name,customer_address_phone,customer_address_postal_code,
customer_address_province,customer_address_province_code,customer_address_sub_district,customer_address_district,customer_address_coordinate,
order_status,discount_type,order_type,payment_method_POS,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT id,order_id,outlet_id,point_amt,point_reference,[status],channel,local_id,
local_name,ordered_at,payment_method,payment_status,total_price,shipping_price,@discount_shipping,channel_rebate,discount_amount,
customer_info_id,customer_info_name,customer_info_email,customer_info_since,
customer_address_address_1,customer_address_address_2,customer_address_city,customer_address_country,customer_address_name,customer_address_phone,customer_address_postal_code,
customer_address_province,customer_address_province_code,customer_address_sub_district,customer_address_district,customer_address_coordinate,
order_status,discount_type,order_type,
CASE
WHEN payment_method = 'Kartu Kredit MASTERCARD' THEN 'Kartu Kredit MASTERCARD'
WHEN payment_method = 'Kartu Kredit VISA' THEN 'Kartu Kredit VISA'
WHEN payment_method = 'Kartu Kredit JCB' THEN 'Kartu Kredit JCB'
WHEN payment_method = 'Kartu Kredit' THEN 'Kartu Kredit'
WHEN payment_method like '%Virtual Account%' AND payment_method not like '%BCA%' THEN 'Virtual Account'
WHEN payment_method like '%Virtual Account%' AND payment_method like '%BCA%' THEN 'Virtual Account BCA'
WHEN payment_method = 'QRIS' THEN 'QRIS'
ELSE
'UNKNOWN'
END
as payment_method_POS,
CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM TrSaleorHT
WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop
INSERT INTO TrSaleorDH(id,order_id,local_id,order_num,variant_sku,qty,price,sale_price,total_price,
Price_after_voucher,sale_price_voucher,total_price_after_voucher,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT id,order_id,local_id,order_num,variant_sku,qty,price,sale_price,qty*price,
Price,0,
qty*price,
CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM TrSaleorDT
WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop
AND (variant_sku not like 'erp-%' AND variant_sku not like 'level-%' AND variant_sku <> 'null' AND variant_sku not like 'opt-%')
END
--INSERT SHIPPING COST
DECLARE @ORDER_NUM_NEXT as int = (SELECT MAX(order_num) + 1 FROM TrSaleorDT WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop)
INSERT INTO TrSaleorDH(id,order_id,local_id,order_num,variant_sku,qty,price,sale_price,total_price,
Price_after_voucher,sale_price_voucher,total_price_after_voucher,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT TOP 1 id,order_id,local_id,@ORDER_NUM_NEXT,'Biaya Pengiriman',1,@shipping_price,@discount_shipping,ISNULL(@shipping_price-@discount_shipping,0) as total_price,
@shipping_price,@discount_shipping,ISNULL(@shipping_price-@discount_shipping,0) as total_price_after_voucher,
CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM TrSaleorDT
WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop
--UPDATE HEADER TOTAL
UPDATE TrSaleorHH SET total_price = ISNULL(DETAIL.TOTAL_DETAIL,0)
FROM
(
SELECT order_id,ISNULL(SUM(Total_Price_After_Voucher),0) as TOTAL_DETAIL
FROM TrSaleorDH
GROUP BY order_id
)DETAIL
WHERE TrSaleorHH.order_id = DETAIL.order_id
AND TrSaleorHH.order_id = @order_id_loop
INSERT INTO SALEOR_ORDER_STATUS(id,order_id,local_id,Order_status,Order_DT,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT id,order_id,local_id,Order_status,Order_DT,CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM SALEOR_ORDER_STATUS_T
WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop
IF NOT EXISTS (SELECT '' FROM SALEOR_DOWNLOAD WHERE order_id = @order_id_loop)
BEGIN
INSERT INTO SALEOR_DOWNLOAD(order_id,RC,RM,POS_DOCNUMBR,StoreCode,Businessday,POSID,SHIFTID,
IsDownloadToPOS,LastDownloadDT,LastDownloadRC,LastDownloadRM,
IsUploadToCloud,LastUploadDT,LastUploadRC,LastUploadRM,LastUploadMSG,LastUploadRCDT,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES (@order_id_loop,'200','SUCCESS','','','','','',
1,'1900-01-01','','',
1,GETDATE(),'','','','1900-01-01',
@COMPUTERIP,GETDATE(),'','1900-01-01')
END
ELSE
BEGIN
UPDATE SALEOR_DOWNLOAD SET RC = '200', RM = 'SUCCESS',
IsDownloadToPOS = 1, LastDownloadDT = '1900-01-01',
IsUploadToCloud =1, LastUploadDT = GETDATE(),
MDFUSRID = @COMPUTERIP, MODIFDT = GETDATE()
WHERE order_id = @order_id_loop
END
INSERT INTO #SYNC_ORDER_SALEOR(order_id,Status_Updated) VALUES (@order_id_loop,'add new order')
END
ELSE
BEGIN
IF EXISTS (SELECT '' FROM SALEOR_DOWNLOAD WHERE order_id = @order_id_loop AND RC='200') --sudah download & ok
BEGIN
DECLARE @Status_Old VARCHAR(100) = (SELECT order_status FROM TrSaleorHH WHERE order_id = @order_id_loop)
IF @Status_Old <> @order_status
BEGIN
UPDATE TrSaleorHH SET order_status = @order_status,MDFUSRID = @COMPUTERIP,MODIFDT = GETDATE() WHERE order_id = @order_id_loop
UPDATE TrSaleorHW SET order_status = @order_status,MDFUSRID = @COMPUTERIP,MODIFDT = GETDATE() WHERE order_id = @order_id_loop
DELETE FROM SALEOR_ORDER_STATUS WHERE order_id = @order_id_loop
INSERT INTO SALEOR_ORDER_STATUS(id,order_id,local_id,Order_status,Order_DT,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
SELECT id,order_id,local_id,Order_status,Order_DT,CRUSRID,CREATDT,MDFUSRID,MODIFDT
FROM SALEOR_ORDER_STATUS_T
WHERE COMPUTERIP = @COMPUTERIP AND order_id = @order_id_loop
IF NOT EXISTS (SELECT '' FROM SALEOR_DOWNLOAD WHERE order_id = @order_id_loop)
BEGIN
INSERT INTO SALEOR_DOWNLOAD(order_id,RC,RM,POS_DOCNUMBR,StoreCode,Businessday,POSID,SHIFTID,
IsDownloadToPOS,LastDownloadDT,LastDownloadRC,LastDownloadRM,
IsUploadToCloud,LastUploadDT,LastUploadRC,LastUploadRM,LastUploadMSG,LastUploadRCDT,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES (@order_id_loop,'200','SUCCESS','','','','','',
1,'1900-01-01','','',
1,GETDATE(),'','','','1900-01-01',
@COMPUTERIP,GETDATE(),'','1900-01-01')
END
ELSE
BEGIN
UPDATE SALEOR_DOWNLOAD SET RC = '200', RM = 'SUCCESS',
IsDownloadToPOS = 1, LastDownloadDT = '1900-01-01',
IsUploadToCloud =1, LastUploadDT = GETDATE(),
MDFUSRID = @COMPUTERIP, MODIFDT = GETDATE()
WHERE order_id = @order_id_loop
END
INSERT INTO #SYNC_ORDER_SALEOR(order_id,Status_Updated) VALUES (@order_id_loop,'order status updated')
END
ELSE
BEGIN
INSERT INTO #SYNC_ORDER_SALEOR(order_id,Status_Updated) VALUES (@order_id_loop,'reupdated cloud')
END
END
END
END TRY
BEGIN CATCH
DECLARE @ERROR_MESSAGE AS VARCHAR(500) = ERROR_MESSAGE()
IF NOT EXISTS (SELECT '' FROM SALEOR_DOWNLOAD WHERE order_id = @order_id_loop)
BEGIN
INSERT INTO SALEOR_DOWNLOAD(order_id,RC,RM,POS_DOCNUMBR,StoreCode,Businessday,POSID,SHIFTID,
IsDownloadToPOS,LastDownloadDT,LastDownloadRC,LastDownloadRM,
IsUploadToCloud,LastUploadDT,LastUploadRC,LastUploadRM,LastUploadMSG,LastUploadRCDT,
CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES (@order_id_loop,'400',@ERROR_MESSAGE,'','','','','',
0,'1900-01-01','','',
0,'1900-01-01','','','','1900-01-01',
@COMPUTERIP,GETDATE(),'','1900-01-01')
END
ELSE
BEGIN
UPDATE SALEOR_DOWNLOAD SET RC = '400', RM = @ERROR_MESSAGE,
IsDownloadToPOS = 1, LastDownloadDT = '1900-01-01',
IsUploadToCloud =1, LastUploadDT = GETDATE(),
MDFUSRID = @COMPUTERIP, MODIFDT = GETDATE()
WHERE order_id = @order_id_loop
END
END CATCH
FETCH NEXT FROM CSR_LOOP_TRX INTO @order_id_loop
END
CLOSE CSR_LOOP_TRX
DEALLOCATE CSR_LOOP_TRX
COMMIT
SELECT Order_id,Status_Updated FROM #SYNC_ORDER_SALEOR
SELECT Order_id,Status_Updated FROM #SYNC_ORDER_SALEOR WHERE order_id not in (SELECT order_id FROM TrSaleorHT WHERE COMPUTERIP = @COMPUTERIP)
EXEC RETAIL_CLOUD_EY_SALEOR_CLEAR_TEMP @COMPUTERIP
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_SAVE_ORDER_STATUS_TEMP
@COMPUTERIP VARCHAR(100),
@id varchar(100),
@order_id varchar(100),
@local_id varchar(100),
@OrderStatus Varchar(100),
@OrderStatusDT datetime,
@CRUSRID varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
INSERT INTO SALEOR_ORDER_STATUS_T(COMPUTERIP,id,order_id,local_id,Order_status,Order_DT,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES (@COMPUTERIP,@id,@order_id,@local_id,@OrderStatus,@OrderStatusDT,@CRUSRID,GETDATE(),'','1900-01-01')
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_UPDATE_ORDER_CLOUD
@order_id varchar(100),
@rc varchar(100),
@rm varchar(100),
@msg varchar(100),
@CRUSRID varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF @rc = 200
BEGIN
UPDATE SALEOR_DOWNLOAD
SET isUploadToCloud = 0,
LastUploadRC = @rc,
LastUploadRM = @rm,
LastUploadMSG = @msg,
LastUploadRCDT = GETDATE(),
MDFUSRID = @CRUSRID,
MODIFDT = GETDATE()
WHERE order_id = @order_id
END
ELSE
BEGIN
UPDATE SALEOR_DOWNLOAD
SET
LastUploadRC = @rc,
LastUploadRM = @rm,
LastUploadMSG = @msg,
LastUploadRCDT = GETDATE(),
MDFUSRID = @CRUSRID,
MODIFDT = GETDATE()
WHERE order_id = @order_id
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_TO_BE_PROCESSED
@StoreCode varchar(100)
as
set nocount on
BEGIN TRY
SELECT order_id FROM SALEOR_DOWNLOAD WHERE IsDownloadToPOS = 1 AND POS_DOCNUMBR = ''
END TRY
BEGIN CATCH
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_EXECUTE_ORDER_TO_POS
@COMPUTERIP VARCHAR(100),
@StoreCode VARCHAR(100),
@POSID VARCHAR(100),
@SHIFTID VARCHAR(100),
@Businessday VARCHAR(100),
@CLOUD_DOCNUMBR VARCHAR(100),
@SYS_USR VARCHAR(100),
@LSMEMBERSHIP varchar(100),
@COMPANYID varchar(100),
@COMPANYPCID varchar(100)
as
set nocount on
BEGIN TRY
IF EXISTS (SELECT '' FROM SALEOR_DOWNLOAD WHERE order_id = @CLOUD_DOCNUMBR AND POS_DOCNUMBR <> '')
BEGIN
RAISERROR ('Sales is already downloaded.',16,1)
END
DECLARE @POSID_ONLINE AS VARCHAR(100) = ''
SET @POSID_ONLINE = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'POS_ID_ONLINE')
SET @POSID_ONLINE = ISNULL(@POSID_ONLINE,'')
IF @POSID <> @POSID_ONLINE
BEGIN
RAISERROR ('POS ID Online is invalid.',16,1)
END
IF EXISTS (SELECT '' FROM TrSaleorDH WHERE order_id = @CLOUD_DOCNUMBR AND variant_sku
NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItem))
BEGIN
DECLARE @NOT_REGISTERED_ITEM VARCHAR(8000) = (SELECT TOP 1 'Item is not registered. (' + variant_sku + ')' FROM TrSaleorDH WHERE order_id = @CLOUD_DOCNUMBR
AND variant_sku NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItem))
SET @NOT_REGISTERED_ITEM = @CLOUD_DOCNUMBR + ' - ' + @NOT_REGISTERED_ITEM
RAISERROR (@NOT_REGISTERED_ITEM,16,1)
END
BEGIN TRY
BEGIN TRAN
DECLARE @MSG_ERR VARCHAR(8000) = ''
DECLARE @SALES_TAX as decimal(19,5)=0
SET @SALES_TAX = ISNULL((SELECT CONVERT(float,SETUPVALUE)+1 FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'SALES_TAX'),0)
DECLARE @order_num as int,@Barcode varchar(100), @qty as decimal(17,5)=0, @price as decimal(19,5) = 0,@disc as decimal(19,5)=0,@promocode as varchar(100) = ''
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesTenderT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesTenderTT WHERE COMPUTERIP = @COMPUTERIP
DELETE FROM RETAIL_TRANSACTION.dbo.TrSalesAdditionalT WHERE COMPUTERIP = @COMPUTERIP
DECLARE CSR_DOWNLOAD CURSOR FOR
SELECT order_num,variant_sku,qty,price,sale_price_voucher,
case when isnull(sale_price_voucher,0) > 0 THEN
'Voucher Saleor' ELSE '' END FROM TrSaleorDH WHERE Order_id = @CLOUD_DOCNUMBR
OPEN CSR_DOWNLOAD
FETCH NEXT FROM CSR_DOWNLOAD INTO @order_num,@Barcode,@qty,@price,@disc,@promocode
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE
@material_id varchar(100),
@ItemDescriptionLong varchar(500),
@ItemDescriptionShort varchar(50),
@ItemType varchar(20),
@TaxCode varchar(20),
@OpenPriceFlg bit,
@ItemValue as decimal(19,5)=0,
@ReturnAble as bit,
@MerchCat varchar(50),
@MerchCatDesc varchar(100),
@Category varchar(50),
@CategoryDesc varchar(100),
@Department varchar(50),
@DepartmentDesc varchar(100),
@Division varchar(50),
@DivisionDesc varchar(100),
@Tax decimal(19,5) = 0,
@Subtotal decimal(19,5) = 0,
@DPP decimal(19,5) = 0,
@Total decimal(19,5) = 0
SELECT TOP 1 @material_id = ItemCode,@ItemDescriptionLong = ItemDescriptionLong,
@ItemDescriptionShort = ItemDescriptionShort, @ItemType = ItemType,
@TaxCode = TaxCode, @OpenPriceFlg = OpenPriceFlg,
@ReturnAble = ReturnAble,
@MerchCat = MItem.MerchCat, @MerchCatDesc = MerchCatDesc,
@Category = Category, @CategoryDesc = CategoryDesc,
@Department = Department, @DepartmentDesc = DepartmentDesc,
@Division = Division, @DivisionDesc = DivisionDesc
FROM RETAIL_MASTER.dbo.MItem
LEFT JOIN RETAIL_MASTER.dbo.MMerchandiseCategory ON MItem.MerchCat = MMerchandiseCategory.MerchCat
where Barcode = @Barcode
IF @Barcode = ''
BEGIN
SET @MSG_ERR = @Barcode + 'is not registered.'
RAISERROR (@MSG_ERR,16,1)
END
IF @TaxCode = ''
BEGIN
SET @MSG_ERR = @Barcode + ' tax code is blank.'
RAISERROR (@MSG_ERR,16,1)
END
SET @Subtotal = @qty * (@price - @disc)
SET @Total = @Subtotal
IF @TaxCode <> 0
BEGIN
SET @Tax = ROUND(@Subtotal - (@Subtotal/@SALES_TAX),0)
END
SET @DPP = @Subtotal - @Tax
INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesT
(StoreCode,BusinessDay,POSID,SHIFTID,COMPUTERIP,LNITMSEQ,Barcode,ItemCode,ItemDescriptionLong,ItemDescriptionShort,ItemType,TaxCode,MerchCat,MerchCatDesc,
Category,CategoryDesc,Department,DepartmentDesc,Division,DivisionDesc,OpenPriceFlg,UnitPrice,Qty,Disc,Subtotal,DPP,Tax,Total,QtySales,SalesDisc,SalesSubtotal,SalesDPP,SalesTax,SalesTotal,QtyReturned,ReturnedDisc,ReturnedSubtotal,ReturnedDPP,ReturnedTax,ReturnedTotal,ItemValue,CRUSRID,CREATDT,MDFUSRID,MODIFDT,ReturnAble,PromoCode)
VALUES
(@StoreCode,@BusinessDay,@POSID,@SHIFTID,@COMPUTERIP,@order_num,@Barcode,
@material_id,@ItemDescriptionLong,@ItemDescriptionShort,@ItemType,@TaxCode,@MerchCat,@MerchCatDesc,
@Category,@CategoryDesc,@Department,@DepartmentDesc,@Division,@DivisionDesc,@OpenPriceFlg,@price,@qty,@disc,@Subtotal,@DPP,@Tax,@Total,@qty,@disc,@Subtotal,@DPP,@Tax,@Total,0,0,0,0,0,0,@ItemValue,@SYS_USR,GETDATE(),'','1900-01-01 00:00:00.000',@ReturnAble,@promocode)
--INSERT INTO RETAIL_TRANSACTION.dbo.TrSalesAdditionalT(COMPUTERIP,LNITMSEQ,USERDEFINEID,USERDEFINEVALUE,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
--VALUES
--(@COMPUTERIP,@order_num,@order_num,@notes,@SYS_USR,GETDATE(),'','1900-01-01 00:00:00.000')
FETCH NEXT FROM CSR_DOWNLOAD INTO @order_num,@Barcode,@qty,@Price,@disc,@promocode
END
CLOSE CSR_DOWNLOAD
DEALLOCATE CSR_DOWNLOAD
IF EXISTS (SELECT '' FROM TrSaleorHH WHERE order_id = @CLOUD_DOCNUMBR AND payment_method_POS = 'UNKNOWN')
BEGIN
DECLARE @NOT_REGISTERED_PAYMENT VARCHAR(8000) = (SELECT TOP 1 'Payment is not registered.' + payment_method FROM TrSaleorHH WHERE order_id = @CLOUD_DOCNUMBR)
RAISERROR (@NOT_REGISTERED_PAYMENT,16,1)
END
---------------------------------------PAYMENT PROCESSING----------------------------------------------
DECLARE @paymentType varchar(100) = '',@ReferenceNo varchar(100) = '',@PaidAmount DECIMAL(19,5) = 0
DECLARE @TotalPaidAmount DECIMAL(19,5)=0
DECLARE CSR_DOWNLOAD_PAYMENT CURSOR FOR SELECT payment_method_POS,payment_method,isnull(total_price,0)-isnull(discount_amount,0) FROM TrSaleorHH
WHERE Order_id = @CLOUD_DOCNUMBR
UNION
SELECT 'PROMO BANK','PROMO BANK',discount_amount FROM TrSaleorHH
WHERE Order_id = @CLOUD_DOCNUMBR AND discount_amount > 0
OPEN CSR_DOWNLOAD_PAYMENT
FETCH NEXT FROM CSR_DOWNLOAD_PAYMENT INTO @paymentType,@ReferenceNo,@PaidAmount
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TotalPaidAmount = @TotalPaidAmount + @PaidAmount
EXEC RETAIL_TRANSACTION.dbo.TRX_EY_SAVE_SALES_TRANSACTION_TENDER_TEMP
@StoreCode, @BusinessDay, @POSID, @SHIFTID, @COMPUTERIP, @paymentType, @PaidAmount, @PaidAmount, @ReferenceNo, @SYS_USR
FETCH NEXT FROM CSR_DOWNLOAD_PAYMENT INTO @paymentType,@ReferenceNo,@PaidAmount
END
CLOSE CSR_DOWNLOAD_PAYMENT
DEALLOCATE CSR_DOWNLOAD_PAYMENT
DECLARE @DPP_TOTAL as decimal(19,5) = 0, @Items_TOTAL as int = 0, @Tax_TOTAL as decimal(19,5) = 0
SET @DPP_TOTAL = (SELECT ISNULL(SUM(DPP),0) FROM RETAIL_TRANSACTION.dbo.TrSalesT
WHERE COMPUTERIP = @COMPUTERIP
AND TaxCode <> '0'
AND Barcode NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItemDonation))
SET @DPP_TOTAL = ISNULL(@DPP_TOTAL,0)
SELECT @Items_TOTAL = ISNULL(ItemCount,0),
@Tax_TOTAL = ISNULL(Tax,0)
FROM
(
SELECT ISNULL(SUM(Total),0) as Total,@DPP as DPP,ISNULL(SUM(Tax),0) as Tax,ISNULL(SUM(Subtotal),0) as Subtotal FROM RETAIL_TRANSACTION.dbo.TrSalesT
WHERE COMPUTERIP = @COMPUTERIP
)TOTAL
LEFT JOIN
(
SELECT CEILING(ISNULL(SUM(Qty),0)) as ItemCount
FROM RETAIL_TRANSACTION.dbo.TrSalesT WHERE COMPUTERIP = @COMPUTERIP
AND Barcode NOT IN (SELECT Barcode FROM RETAIL_MASTER.dbo.MItemReceiptDisallow)
)ITEM_COUNT ON 1=1
EXEC RETAIL_TRANSACTION.dbo.TRX_EY_POST_SALES_TRANSACTION
@StoreCode,@BusinessDay,@POSID,@SHIFTID,@COMPUTERIP,@TotalPaidAmount,@DPP_TOTAL,
@Tax_TOTAL,@TotalPaidAmount,@Items_TOTAL,@TotalPaidAmount,0,@TotalPaidAmount,
@CLOUD_DOCNUMBR,'','',@SYS_USR,'','SALES TRANSACTION','',@LSMEMBERSHIP,@COMPANYID,@COMPANYPCID,
0,0,0
DECLARE @DOCNUMBR_POST VARCHAR(100) = (SELECT TOP 1 DOCNUMBR FROM RETAIL_TRANSACTION.dbo.TrSalesHH WHERE ORIDOCNUMBR = @CLOUD_DOCNUMBR ORDER BY CREATDT DESC)
EXEC RETAIL_TRANSACTION.dbo.TRX_EY_POST_AGE_GENDER_CUSTOMER
@StoreCode,
@BusinessDay,
@POSID,
@SHIFTID,
@SYS_USR,
@DOCNUMBR_POST,
'SALES TRANSACTION',
'familymartshop.id'
--UPDATE RETAIL_TRANSACTION.dbo.TrKitchenHH SET TRXSOURCE = RIGHT(@CLOUD_DOCNUMBR,1) WHERE DOCNUMBR = @DOCNUMBR_POST
UPDATE SALEOR_DOWNLOAD SET POS_DOCNUMBR = @DOCNUMBR_POST, StoreCode = @StoreCode, Businessday = @Businessday,
POSID = @POSID, SHIFTID = @SHIFTID,
IsDownloadToPOS = 0, LastDownloadDT = GETDATE(), LastDownloadRC = '200',LastDownloadRM = 'SUCCESS',
MDFUSRID = @COMPUTERIP,MODIFDT = GETDATE()
WHERE Order_Id = @CLOUD_DOCNUMBR
DELETE FROM RETAIL_TRANSACTION.dbo.TrKitchenHH WHERE DOCNUMBR = @DOCNUMBR_POST
SELECT @DOCNUMBR_POST
COMMIT
END TRY
BEGIN CATCH
IF @@trancount > 0
BEGIN
ROLLBACK
END
DECLARE @ERROR AS VARCHAR(MAX)
SET @ERROR = ERROR_MESSAGE()
RAISERROR(@ERROR,16,1)
CLOSE CSR_DOWNLOAD
DEALLOCATE CSR_DOWNLOAD
CLOSE CSR_DOWNLOAD_PAYMENT
DEALLOCATE CSR_DOWNLOAD_PAYMENT
END CATCH
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
UPDATE SALEOR_DOWNLOAD SET
LastDownloadRC = '404',LastDownloadRM = @ERROR_ALL,
MDFUSRID = @COMPUTERIP,MODIFDT = GETDATE()
WHERE Order_Id = @CLOUD_DOCNUMBR
RAISERROR(@ERROR_ALL,16,1)
END CATCH
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_POS
@StoreCode varchar(100),
@WHERECOND varchar(8000)
as
set nocount on
BEGIN TRY
EXEC('
SELECT [Order ID],[Order DT],[POS Receipt],[Businessday],[Shift],[Channel Id],
[Payment],[Total],[Customer ID],[Customer Name],[Customer Email],[Customer Address],
[Sent to],[Sent to phone],[Order Status],[Order Type],
[POS DL],[POS DL DT],[RC],[RM]
FROM
(
SELECT SALEOR_DOWNLOAD.order_id as [Order ID],
POS_DOCNUMBR as [POS Receipt],
Businessday as [Businessday],
SHIFTID as [Shift],
IsDownloadToPOS as [POS DL],
LastDownloadDT as [POS DL DT],
LastDownloadRC as [RC],
LastDownloadRM as [RM],
local_id as [Channel Id],
ordered_at as [Order DT],
payment_method as [Payment],
total_price as [Total],
customer_info_id as [Customer ID],
customer_info_name as [Customer Name],
customer_info_email as [Customer Email],
customer_address_address_1 as [Customer Address],
customer_address_name as [Sent to],
customer_address_phone as [Sent to phone],
order_status as [Order Status],
order_type as [Order Type]
FROM SALEOR_DOWNLOAD
LEFT JOIN TrSaleorHH ON SALEOR_DOWNLOAD.order_id = TrSaleorHH.order_id
WHERE TrSaleorHH.outlet_id = '''+@StoreCode+'''
)SALES_ECOM
' + @WHERECOND
+ ' ' + 'ORDER BY [Order ID] DESC')
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_POS_DETAIL
@Order_ID varchar(100),
@StoreCode varchar(100)
as
set nocount on
BEGIN TRY
IF EXISTS (SELECT '' FROM SALEOR_DOWNLOAD WHERE order_id = @Order_ID AND StoreCode = @StoreCode AND POS_DOCNUMBR <> '')
BEGIN
SELECT [Order ID],[POS Receipt],[Businessday],[Shift],[Channel Id],
[Order DT],[Payment],[Total],[Customer ID],[Customer Name],[Customer Email],[Customer Address],
[Sent to],[Sent to phone],[Order Status],[Order Type],
[POS DL],[POS DL DT],[RC],[RM]
FROM
(
SELECT SALEOR_DOWNLOAD.order_id as [Order ID],
POS_DOCNUMBR as [POS Receipt],
Businessday as [Businessday],
SHIFTID as [Shift],
IsDownloadToPOS as [POS DL],
LastDownloadDT as [POS DL DT],
LastDownloadRC as [RC],
LastDownloadRM as [RM],
local_id as [Channel Id],
ordered_at as [Order DT],
payment_method as [Payment],
ISNULL(total_price,0) as [Total],
customer_info_id as [Customer ID],
customer_info_name as [Customer Name],
ISNULL(customer_info_email,'') as [Customer Email],
customer_address_address_1 as [Customer Address],
customer_address_name as [Sent to],
customer_address_phone as [Sent to phone],
order_status as [Order Status],
order_type as [Order Type]
FROM SALEOR_DOWNLOAD
LEFT JOIN TrSaleorHH ON SALEOR_DOWNLOAD.order_id = TrSaleorHH.order_id
WHERE SALEOR_DOWNLOAD.order_id = @Order_ID
)SALES
DECLARE @POSReceiptNo VARCHAR(100) = (SELECT TOP 1 POS_DOCNUMBR FROM SALEOR_DOWNLOAD WHERE Order_id = @order_id)
SELECT Barcode as [Barcode],ItemDescriptionLong as [Item Description],UnitPrice as [Unit Price], Disc as [Disc],
Qty as [Qty Order],QtyReturned*-1 as [Qty Returned],Qty-QtyReturned as [Qty Total],
(Qty-QtyReturned)*(UnitPrice-disc) as [Total Amount]
FROM RETAIL_TRANSACTION.dbo.TrSalesDH WHERE DOCNUMBR = @POSReceiptNo
SELECT TenderID as [Tender ID], ReferenceNo as [Reference No],TrSalesTenderHH.TenderedAmount as [Paid Amount],ReturnedAmount*-1 as [Returned Amount],
ISNULL(TrSalesTenderHH.TenderedAmount,0) - ISNULL(ReturnedAmount,0) as Total
FROM RETAIL_TRANSACTION.dbo.TrSalesTenderHH
LEFT JOIN RETAIL_TRANSACTION.dbo.TrSalesHH ON TrSalesTenderHH.DOCNUMBR = TrSalesHH.DOCNUMBR
LEFT JOIN TrSaleorHH ON TrSaleorHH.order_id = TrSalesHH.ORIDOCNUMBR
WHERE
outlet_id = @StoreCode AND
TrSaleorHH.order_id = @order_id
AND TOTAL>0
--UNION ALL
--SELECT TenderID as [Tender ID], ReferenceNo as [Reference No],TrSalesTenderHH.TenderedAmount as [Paid Amount],ReturnedAmount*-1 as [Returned Amount] FROM RETAIL_TRANSACTION.dbo.TrSalesTenderHH
--LEFT JOIN RETAIL_TRANSACTION.dbo.TrSalesHH X ON TrSalesTenderHH.DOCNUMBR = X.DOCNUMBR
--LEFT JOIN RETAIL_TRANSACTION.dbo.TrSalesHH Y ON X.RETURNDOCNUMBR = Y.DOCNUMBR
--LEFT JOIN TrSaleorHH ON TrSaleorHH.order_id = Y.ORIDOCNUMBR
--WHERE
--outlet_id = @StoreCode AND
--TrSaleorHH.order_id = @order_id
--AND TrSalesTenderHH.TenderedAmount>0
SELECT Order_status as [Status],Order_DT as [Status Date] FROM SALEOR_ORDER_STATUS WHERE order_id = @Order_ID
SELECT ISNULL(SUM(ReturnedAmount*-1),0) as ReturnedAmount
FROM RETAIL_TRANSACTION.dbo.TrSalesTenderHH
LEFT JOIN RETAIL_TRANSACTION.dbo.TrSalesHH ON TrSalesTenderHH.DOCNUMBR = TrSalesHH.DOCNUMBR
LEFT JOIN TrSaleorHH ON TrSaleorHH.order_id = TrSalesHH.ORIDOCNUMBR
WHERE
outlet_id = @StoreCode AND
TrSaleorHH.order_id = @order_id
AND TOTAL>0
SELECT ISNULL(SUM(ReturnedAmount),0) as ReturnNoteAmount
FROM RETAIL_TRANSACTION.dbo.TrSalesTenderHH
LEFT JOIN RETAIL_TRANSACTION.dbo.TrSalesHH ON TrSalesTenderHH.DOCNUMBR = TrSalesHH.DOCNUMBR
LEFT JOIN TrSaleorHH ON TrSaleorHH.order_id = TrSalesHH.ORIDOCNUMBR
WHERE
outlet_id = @StoreCode AND
TrSaleorHH.order_id = @order_id
AND TOTAL>0
ANd TenderID <> 'PROMO BANK'
END
ELSE
BEGIN
SELECT [Order ID],[POS Receipt],[Businessday],[Shift],[Channel Id],
[Order DT],[Payment],[Total],[Customer ID],[Customer Name],[Customer Email],[Customer Address],
[Sent to],[Sent to phone],[Order Status],[Order Type],
[POS DL],[POS DL DT],[RC],[RM]
FROM
(
SELECT SALEOR_DOWNLOAD.order_id as [Order ID],
POS_DOCNUMBR as [POS Receipt],
Businessday as [Businessday],
SHIFTID as [Shift],
IsDownloadToPOS as [POS DL],
LastDownloadDT as [POS DL DT],
LastDownloadRC as [RC],
LastDownloadRM as [RM],
local_id as [Channel Id],
ordered_at as [Order DT],
payment_method as [Payment],
ISNULL(total_price,0) as [Total],
customer_info_id as [Customer ID],
customer_info_name as [Customer Name],
ISNULL(customer_info_email,'') as [Customer Email],
customer_address_address_1 as [Customer Address],
customer_address_name as [Sent to],
customer_address_phone as [Sent to phone],
order_status as [Order Status],
order_type as [Order Type]
FROM SALEOR_DOWNLOAD
LEFT JOIN TrSaleorHH ON SALEOR_DOWNLOAD.order_id = TrSaleorHH.order_id
WHERE SALEOR_DOWNLOAD.order_id = @Order_ID
)SALES
SELECT variant_sku as Barcode,ItemDescriptionLong as [Item Description],price as [Unit Price],sale_price_voucher as [Disc],
qty as [Qty Order], 0 as [Qty Returned], qty as [Qty Total],
total_price_after_voucher as [Total Amount]
FROM TrSaleorDH
LEFT JOIN RETAIL_MASTER.dbo.MItem ON TrSaleorDH.variant_sku = MItem.Barcode
WHERE order_id = @Order_ID
ORDER BY TrSaleorDH.CREATDT ASC
SELECT payment_method_POS as [Tender ID], payment_method as [Reference No],total_price-discount_amount as [Paid Amount],0 as [Returned Amount],
total_price-discount_amount as [Total]
FROM TrSaleorHH WHERE order_id = @Order_ID
UNION
SELECT 'PROMO BANK','' as [Reference No],discount_amount as [Paid Amount],0 as [Returned Amount],
discount_amount as [Total]
FROM TrSaleorHH WHERE order_id = @Order_ID
AND discount_amount>0
SELECT Order_status as [Status],Order_DT as [Status Date] FROM SALEOR_ORDER_STATUS WHERE order_id = @Order_ID
SELECT 0 as ReturnedAmount
SELECT 0 as ReturnNoteAmount
END
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_SALEOR_DISPLAY_ORDER_ORI_DETAIL
@Order_ID varchar(100),
@StoreCode varchar(100)
as
set nocount on
BEGIN TRY
SELECT [Order ID],[POS Receipt],[Businessday],[Shift],[Channel Id],
[Order DT],[Payment],[Total],[Customer ID],[Customer Name],[Customer Email],[Customer Address],
[Sent to],[Sent to phone],[Order Status],[Order Type],
[POS DL],[POS DL DT],[RC],[RM]
FROM
(
SELECT SALEOR_DOWNLOAD.order_id as [Order ID],
POS_DOCNUMBR as [POS Receipt],
Businessday as [Businessday],
SHIFTID as [Shift],
IsDownloadToPOS as [POS DL],
LastDownloadDT as [POS DL DT],
LastDownloadRC as [RC],
LastDownloadRM as [RM],
local_id as [Channel Id],
ordered_at as [Order DT],
payment_method as [Payment],
ISNULL(total_price,0) as [Total],
customer_info_id as [Customer ID],
customer_info_name as [Customer Name],
ISNULL(customer_info_email,'') as [Customer Email],
customer_address_address_1 as [Customer Address],
customer_address_name as [Sent to],
customer_address_phone as [Sent to phone],
order_status as [Order Status],
order_type as [Order Type]
FROM SALEOR_DOWNLOAD
LEFT JOIN TrSaleorHH ON SALEOR_DOWNLOAD.order_id = TrSaleorHH.order_id
WHERE SALEOR_DOWNLOAD.order_id = @Order_ID
)SALES
SELECT variant_sku as Barcode,ItemDescriptionLong as [Item Description],price as [Unit Price],sale_price_voucher as [Disc],
qty as [Qty Order], 0 as [Qty Returned], qty as [Qty Total],
total_price_after_voucher as [Total Amount]
FROM TrSaleorDH
LEFT JOIN RETAIL_MASTER.dbo.MItem ON TrSaleorDH.variant_sku = MItem.Barcode
WHERE order_id = @Order_ID
ORDER BY TrSaleorDH.CREATDT ASC
SELECT payment_method_POS as [Tender ID], payment_method as [Reference No],total_price-discount_amount as [Paid Amount],0 as [Returned Amount],
total_price-discount_amount as [Total]
FROM TrSaleorHH WHERE order_id = @Order_ID
UNION
SELECT 'PROMO BANK','' as [Reference No],discount_amount as [Paid Amount],0 as [Returned Amount],
discount_amount as [Total]
FROM TrSaleorHH WHERE order_id = @Order_ID
AND discount_amount>0
SELECT Order_status as [Status],Order_DT as [Status Date] FROM SALEOR_ORDER_STATUS WHERE order_id = @Order_ID
SELECT 0 as ReturnedAmount
SELECT 0 as ReturnNoteAmount
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
---------------------------------------------------FAMI APPS------------------------------------------------------
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMI_APPS_DISPLAY_ORDER_ALL')
DROP PROC RETAIL_CLOUD_EY_FAMI_APPS_DISPLAY_ORDER_ALL
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMI_APPS_CHECK_NOTIF_POS')
DROP PROC RETAIL_CLOUD_EY_FAMI_APPS_CHECK_NOTIF_POS
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMI_APPS_DELETE_NOTIF_POS')
DROP PROC RETAIL_CLOUD_EY_FAMI_APPS_DELETE_NOTIF_POS
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMI_APPS_DISPLAY_SELECTED_ORDER')
DROP PROC RETAIL_CLOUD_EY_FAMI_APPS_DISPLAY_SELECTED_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_FINISH_ORDER')
DROP PROC RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_FINISH_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMI_APPS_CHECK_ODER_REQUEST_TO_KITCHEN')
DROP PROC RETAIL_CLOUD_EY_FAMI_APPS_CHECK_ODER_REQUEST_TO_KITCHEN
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_ORDER_SEND_TO_KITCHEN')
DROP PROC RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_ORDER_SEND_TO_KITCHEN
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMI_APPS_DISPLAY_REQUEST_PICKUP_DELIVERY_ORDER')
DROP PROC RETAIL_CLOUD_EY_FAMI_APPS_DISPLAY_REQUEST_PICKUP_DELIVERY_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_REQUEST_PICKUP_DELIVERY_ORDER')
DROP PROC RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_REQUEST_PICKUP_DELIVERY_ORDER
GO
IF EXISTS (SELECT '' FROM SYSOBJECTS WHERE NAME = 'RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_CALL_CSO')
DROP PROC RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_CALL_CSO
GO
CREATE PROC RETAIL_CLOUD_EY_FAMI_APPS_DELETE_NOTIF_POS
@POSID VARCHAR(100)
as
set nocount on
BEGIN TRY
DELETE FROM FAMI_APPS.dbo.famiordernotif --WHERE POSID = @POSID
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMI_APPS_CHECK_NOTIF_POS
@POSID VARCHAR(100)
as
set nocount on
BEGIN TRY
SELECT POSID FROM FAMI_APPS.dbo.famiordernotif WHERE POSID = @POSID
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMI_APPS_DISPLAY_ORDER_ALL
@StoreCode varchar(100),
@WHERECOND VARCHAR(8000)
as
set nocount on
BEGIN TRY
DECLARE @DAYSTOVIEW AS VARCHAR(100) = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'FAMIAPPS_DISPLAY_ORDER_DAYS')
SET @DAYSTOVIEW = ISNULL(@DAYSTOVIEW,30)
EXEC('
SELECT [Order ID],[Order Date],[Ready Time],[Queue No],[Is GRO],[Order Type],[Delivery by],[No Resi],[Customer],
[Status Code],[Status Message],
[Order Amount],[Is Returned],[Last Updated],[Call CSO]
FROM
(
SELECT famiorderS2HH.OrderID as [Order ID],CONVERT(DATE,ORDERDT,103) as [Order Date],OrderType as [Order Type],famiorderS2HH.ISGROCERY as [Is GRO],
CASE WHEN ShippingProvider = ''null null'' THEN ''''
ELSE
ShippingProvider
END as [Delivery by],
ShippingReferenceNo as [No Resi],
Total_Tendered as [Order Amount],
CASE WHEN ISNULL(famiorderS2FH.RTN_JSON,'''') = '''' THEN ''0'' ELSE ''1'' END as [Is Returned],
CASE WHEN OrderType = ''delivery'' THEN
CASE
WHEN OrderSts = ''4'' THEN ''undone''
WHEN OrderSts = ''3'' THEN ''undone''
WHEN OrderSts = ''2'' THEN ''undone''
WHEN OrderSts = ''1'' THEN ''done''
WHEN OrderSts = ''0'' THEN ''canceled''
END
WHEN OrderType = ''pickup'' THEN
CASE
WHEN OrderSts = ''3'' THEN ''undone''
WHEN OrderSts = ''2'' THEN ''undone''
WHEN OrderSts = ''1'' THEN ''done''
WHEN OrderSts = ''0'' THEN ''canceled''
END
WHEN OrderType = ''selfservice'' THEN
CASE
WHEN OrderSts = ''2'' THEN ''undone''
WHEN OrderSts = ''1'' THEN ''done''
END
END as [Status Code],
CASE WHEN OrderType = ''delivery'' THEN
CASE
WHEN OrderSts = ''4'' THEN ''pending''
WHEN OrderSts = ''3'' THEN ''diproses''
WHEN OrderSts = ''2'' THEN ''dikirim''
WHEN OrderSts = ''1'' THEN ''done''
WHEN OrderSts = ''0'' THEN ''batal''
END
WHEN OrderType = ''pickup'' THEN
CASE
WHEN OrderSts = ''3'' THEN ''pending''
WHEN OrderSts = ''2'' THEN ''pickup''
WHEN OrderSts = ''1'' THEN ''done''
WHEN OrderSts = ''0'' THEN ''batal''
END
WHEN OrderType = ''selfservice'' THEN
CASE
WHEN OrderSts = ''2'' THEN ''pending''
WHEN OrderSts = ''1'' THEN ''done''
END
END as [Status Message],
ORDERDT as [Last Updated],
CASE
WHEN OrderType = ''pickup'' THEN
LEFT(CONVERT(VARCHAR,CONVERT(TIME,PickUpTime,108)),5)
WHEN OrderType = ''delivery'' AND ShippingProvider like ''%INSTANT%'' THEN
FORMAT(DATEADD(minute,2,ORDERDT),''HH:mm'')
WHEN OrderType = ''delivery'' AND ShippingProvider like ''%SAME_DAY%'' THEN
''99:99''
WHEN OrderType = ''delivery'' AND ShippingProvider like ''%LALA%'' THEN
FORMAT(DATEADD(minute,0,ORDERDT),''HH:mm'')
ELSE
''99:99''
END
as [Ready Time],
CASE WHEN famiorderS2HH.QUEUENO = '''' THEN ''XXX''
ELSE
famiorderS2HH.QUEUENO
END as [Queue No],
[Phone] as [Phone],
account_name + RIGHT(Phone,4) as [Customer],
isCallCSO as [Call CSO]
FROM FAMI_APPS.dbo.famiorderS2HH
LEFT JOIN FAMI_APPS.dbo.famiorderS2FH ON famiorderS2HH.OrderID = famiorderS2FH.OrderID
WHERE DATEDIFF(day,ORDERDT,GETDATE()) < '+@DAYSTOVIEW+'
AND StoreCode = '''+@StoreCode+'''
)SALES
'+@WHERECOND+'
ORDER BY [Order Date] ASC,[Ready Time] ASC')
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMI_APPS_DISPLAY_SELECTED_ORDER
@OrderID varchar(100)
as
set nocount on
BEGIN TRY
SELECT QUEUENO,PickUpTime,OrderType,ShippingProvider,ORDERDT,RCRQPICKUP,
ISNULL(ShippingReferenceNo,'') as ShippingReferenceNo,TOTAL,OrderSts,RCKTCS2,
IsCALLCSO,OrderSts,ISNULL(RTNJSON,'') as RTNJSON, shippingTracking,KTCJSONS2
FROM FAMI_APPS.dbo.famiorderS2HH WHERE OrderID = @OrderID
SELECT famiorderS2DH.Barcode,ItemDescriptionLong as [Item Description],
--UnitPrice as [Price],
Qty,
--DISC as [Disc],TOTAL as [Total],
ISNULL(NOTE,'') +
CASE WHEN ISNULL(NOTE,'') <> '' AND (ISNULL(ADDITIONAL,'') <> '' OR ISNULL(TOPPING,'') <> '') THEN ', ' ELSE '' END
+ ISNULL(ADDITIONAL,'') +
CASE WHEN ISNULL(ADDITIONAL,'') <> '' AND ISNULL(TOPPING,'') <> '' THEN ', ' ELSE '' END
+ ISNULL(TOPPING,'') as [Order Notes],
CASE WHEN ISNULL(MItemKitchenModule.Barcode,'') = '' THEN
'1'
ELSE
'0'
END as [Is GRO]
FROM FAMI_APPS.dbo.famiorderS2DH
LEFT JOIN RETAIL_MASTER.dbo.MItemKitchenModule ON famiorderS2DH.Barcode = MItemKitchenModule.Barcode
WHERE OrderID = @OrderID
ORDER BY [Is GRO],[ItemDescriptionLong]
SELECT * FROM FAMI_APPS.dbo.famiorderAddonS2DH WHERE OrderID = @OrderID
SELECT * FROM FAMI_APPS.dbo.famiorderS2PH WHERE OrderID = @OrderID
SELECT MItemSuppliesS2Apps.Barcode,Img,MItemSuppliesS2Apps.ItemDescription as [Item Description],
'-' as [Minus],
ISNULL(famiorderS2SuppliesDH.Qty,0) as Qty,
'+' as [Plus]
FROM RETAIL_MASTER.dbo.MItemSuppliesS2Apps
LEFT JOIN RETAIL_MASTER.dbo.MItemTOPPic ON MItemSuppliesS2Apps.Barcode = MItemTOPPic.Barcode
LEFT JOIN FAMI_APPS.dbo.famiorderS2SuppliesDH
ON MItemSuppliesS2Apps.Barcode = famiorderS2SuppliesDH.Barcode
AND famiorderS2SuppliesDH.OrderID = @OrderID
SELECT OrderID,IsConfirmed FROM FAMI_APPS.dbo.famiorderS2SuppliesHH WHERE OrderID = @OrderID
SELECT old_barcode,
(SELECT ItemDescriptionLong FROM RETAIL_MASTER.dbo.MItem WHERE Barcode = old_barcode) as old_Item,
New_barcode,
(SELECT ItemDescriptionLong FROM RETAIL_MASTER.dbo.MItem WHERE Barcode = New_barcode) as new_Item,
new_qty as Qty
FROM FAMI_APPS.dbo.famiorders2subsH WHERE OrderID = @OrderID
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_FINISH_ORDER
@OrderID varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF EXISTS (SELECT '' FROM FAMI_APPS.dbo.famiorderS2HH WHERE OrderID = @OrderID AND OrderSts = 1)
BEGIN
RAISERROR('Order sudah terselesaikan sebelumnya.',16,1)
END
ELSE
BEGIN
UPDATE FAMI_APPS.dbo.famiorderS2HH SET OrderSts = 1,MODIFDT = GETDATE() WHERE OrderID = @OrderID
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMI_APPS_CHECK_ODER_REQUEST_TO_KITCHEN
@OrderID varchar(100),
@StoreCode varchar(100)
as
set nocount on
BEGIN TRY
declare @limit_min int = -30
declare @limit_max int = 30
IF EXISTS
(
SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'KITCHEN_LIMIT_TIME'
)
BEGIN
SET @limit_min = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'KITCHEN_LIMIT_TIME') * -1
SET @limit_max = (SELECT SETUPVALUE FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'KITCHEN_LIMIT_TIME')
END
ELSE
BEGIN
SET @limit_min = -30
SET @limit_max = 30
END
EXEC('
SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name FROM FAMI_APPS.dbo.famiorderS2HH where ISNULL(QUEUENO,'''') = ''''
AND OrderType = ''delivery'' AND StoreCode = '''+@StoreCode+'''
AND OrderID = '''+@OrderID+'''
UNION
SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name FROM FAMI_APPS.dbo.famiorderS2HH
where ISNULL(QUEUENO,'''') = '''' AND OrderType = ''pickup''
AND SUBSTRING(CONVERT(VARCHAR,GETDATE(),108),1,5) BETWEEN
SUBSTRING(CONVERT(VARCHAR,DATEADD(MINUTE,'+@limit_min+',CONVERT(TIME,PickUpTime,108))),1,5) AND
SUBSTRING(CONVERT(VARCHAR,DATEADD(MINUTE,'+@limit_max+',CONVERT(TIME,PickUpTime,108))),1,5)
AND StoreCode = '''+@StoreCode+'''
AND OrderID = '''+@OrderID+'''
UNION
SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name FROM FAMI_APPS.dbo.famiorderS2HH
where ISNULL(QUEUENO,'''') = '''' AND OrderType = ''pickup''
AND CONVERT(varchar,ORDERDT,112) + REPLACE(CONVERT(varchar,PickUpTime,108),'':'','''') < CONVERT(varchar,GETDATE(),112) + REPLACE(CONVERT(varchar,GETDATE(),108),'':'','''')
AND StoreCode = '''+@StoreCode+''' AND OrderID = '''+@OrderID+'''
UNION
SELECT OrderID,KTCJSONS2,OrderType,ISNULL(QUEUENO,'''') as QUEUENO,account_name FROM FAMI_APPS.dbo.famiorderS2HH
WHERE ISNULL(QUEUENO,'''') <> '''' AND RCKTCS2 NOT IN (''0'',''200'')
AND OrderID = '''+@OrderID+'''
')
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_ORDER_SEND_TO_KITCHEN
@StoreCode VARCHAR(100),
@OrderID VARCHAR(100),
@QUEUENO VARCHAR(100),
@RC VARCHAR(10),
@RM VARCHAR(8000)
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF EXISTS (SELECT '' FROM FAMI_APPS.dbo.famiorderS2HH WHERE OrderID = @OrderID AND StoreCode = @StoreCode AND QUEUENO <> '')
BEGIN
UPDATE FAMI_APPS.dbo.famiorderS2HH SET RCKTCS2 = @RC, RCKTCMSGS2 = @RM, RCKTCDTS2 = GETDATE()
WHERE OrderID = @OrderID AND StoreCode = @StoreCode
END
ELSE
BEGIN
UPDATE FAMI_APPS.dbo.famiorderS2HH SET QUEUENO = @QUEUENO,RCKTCS2 = @RC, RCKTCMSGS2 = @RM, RCKTCDTS2 = GETDATE()
WHERE OrderID = @OrderID AND StoreCode = @StoreCode
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMI_APPS_DISPLAY_REQUEST_PICKUP_DELIVERY_ORDER
@OrderID varchar(100),
@StoreCode varchar(100)
as
set nocount on
BEGIN TRY
SELECT OrderID,Shipping_reference_no_tarif,ShippingProvider FROM FAMI_APPS.dbo.famiorderS2HH where
RCRQPICKUP <> '200' AND OrderType = 'delivery' AND StoreCode = @StoreCode
AND OrderID = @OrderID
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_REQUEST_PICKUP_DELIVERY_ORDER
@StoreCode varchar(100),
@OrderID varchar(100),
@RCRQPICKUP varchar(10),
@RMRQPICKUP varchar(8000),
@JSONRQPICKUP varchar(8000),
@order_no_resi varchar(100),
@order_live_tracking_url varchar(8000),
@order_status varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
UPDATE FAMI_APPS.dbo.famiorderS2HH
SET RCRQPICKUP = @RCRQPICKUP,
RMRQPICKUP = @RMRQPICKUP,
DTRQPICKUP = GETDATE(),
JSONRQPICKUP = @JSONRQPICKUP,
ShippingReferenceNo = @order_no_resi,
ShippingTracking = @order_live_tracking_url,
ShippingDesc = @order_status
WHERE OrderID = @OrderID
AND StoreCode = @StoreCode
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMI_APPS_UPDATE_CALL_CSO
@StoreCode VARCHAR(100),
@OrderID VARCHAR(100),
@isCALLCSO VARCHAR(10),
@RC VARCHAR(10),
@RM VARCHAR(8000)
as
set nocount on
BEGIN TRY
BEGIN TRAN
UPDATE FAMI_APPS.dbo.famiorderS2HH SET
isCALLCSO = @isCALLCSO,CALLCSODT = GETDATE(),RCCALLCSO = @RC,RMCALLCSO = @RM
WHERE OrderID = @OrderID AND StoreCode = @StoreCode
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMIAPPS_GENERATE_ORDER_KITCHEN_JSON
@OrderID VARCHAR(100),
@StoreCode VARCHAR(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
DECLARE @JSONKITCHEN AS VARCHAR(8000) = ''
SET @JSONKITCHEN = (SELECT KTCJSONS2 FROM FAMI_APPS.dbo.famiorderS2HH WHERE OrderID = @OrderID AND StoreCode = @StoreCode)
IF ISNULL(@JSONKITCHEN,'')=''
BEGIN
SELECT UnitPrice as Price,ItemCode as ArticleNo,
RTRIM(ISNULL(ADDITIONAL,'') + CHAR(13) + CHAR(10) + ISNULL(TOPPING,'')) as OrderNotes,
ItemDescriptionLong as ArticleName,ROW_NUMBER() OVER(ORDER BY LNITMSEQ ASC) as OrderLineNo,QTY as Qty
FROM FAMI_APPS.dbo.famiorderS2DH WHERE OrderID = @OrderID
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMIAPPS_UPDATE_ORDER_KITCHEN_JSON
@OrderID VARCHAR(100),
@KTCJSONS2 VARCHAR(8000),
@StoreCode varchar(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
UPDATE famiorderS2HH SET KTCJSONS2 = @KTCJSONS2 WHERE OrderID = @OrderID AND StoreCode = @StoreCode
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMIAPPS_ADD_SUPPLIES_ITEM
@OrderID VARCHAR(100),
@Barcode VARCHAR(100),
@Qty DECIMAL(17,5),
@SYS_USR VARCHAR(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF EXISTS (SELECT '' FROM FAMI_APPS.dbo.famiorderS2SuppliesDH WHERE OrderID = @OrderID AND Barcode = @Barcode)
BEGIN
UPDATE FAMI_APPS.dbo.famiorderS2SuppliesDH SET
Qty = @Qty, MDFUSRID = @SYS_USR, MODIFDT = GETDATE() WHERE OrderID = @OrderID AND Barcode = @Barcode
END
ELSE
BEGIN
INSERT INTO FAMI_APPS.dbo.famiorderS2SuppliesDH
(OrderID,Barcode,Qty,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES
(@OrderID,@Barcode,@Qty,@SYS_USR,GETDATE(),'','1900-01-01')
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMIAPPS_CONFIRMED_SUPPLIES_ITEM
@OrderID VARCHAR(100),
@IsConfirmed int,
@JSONSUPP VARCHAR(8000),
@SYS_USR VARCHAR(100)
as
set nocount on
BEGIN TRY
BEGIN TRAN
IF EXISTS (SELECT '' FROM FAMI_APPS.dbo.famiorderS2SuppliesHH WHERE OrderID = @OrderID)
BEGIN
UPDATE FAMI_APPS.dbo.famiorderS2SuppliesHH SET
IsConfirmed = @IsConfirmed, MDFUSRID = @SYS_USR, MODIFDT = GETDATE() WHERE OrderID = @OrderID
END
ELSE
BEGIN
INSERT INTO FAMI_APPS.dbo.famiorderS2SuppliesHH
(OrderID,IsConfirmed,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES
(@OrderID,@IsConfirmed,@SYS_USR,GETDATE(),'','1900-01-01')
END
UPDATE FAMI_APPS.dbo.famiorderS2FH SET SUPLY_JSON = @JSONSUPP WHERE OrderID = @OrderID
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
CREATE PROC RETAIL_CLOUD_EY_FAMIAPPS_DISPLAY_SUPPLIES_ITEM
@OrderID VARCHAR(100)
as
set nocount on
BEGIN TRY
SELECT OrderID,Barcode,Qty FROM FAMI_APPS.dbo.famiorderS2SuppliesDH WHERE OrderID = @OrderID
AND Qty > 0
END TRY
BEGIN CATCH
DECLARE @ERROR_ALL AS VARCHAR(MAX)
SET @ERROR_ALL = ERROR_MESSAGE()
RAISERROR(@ERROR_ALL,16,1)
END CATCH
set nocount off
GO
------------------------------------------------------------------------------
IF EXISTS (SELECT '' FROM RETAIL_SYSTEMS.dbo.MSetup WHERE SETUPID = 'V_RETAIL_CLOUD')
BEGIN
UPDATE RETAIL_SYSTEMS.dbo.MSetup SET SETUPVALUE = 'V.51.0' WHERE SETUPID = 'V_RETAIL_CLOUD'
END
ELSE
BEGIN
INSERT INTO RETAIL_SYSTEMS.dbo.MSetup(SETUPID,SETUPVALUE,CRUSRID,CREATDT,MDFUSRID,MODIFDT)
VALUES ('V_RETAIL_CLOUD','V.51.0','SYSTEMS',GETDATE(),'','1900-01-01')
END
------------------------------------------------------------------------------Editor is loading...
Leave a Comment