Vision Global: Difference between revisions
No edit summary |
No edit summary |
||
| Line 22: | Line 22: | ||
-- check to verify no records returned in following query. If so, do not run anything below <br/> | -- check to verify no records returned in following query. If so, do not run anything below <br/> | ||
------------------------------------------------------------------------------ <br/> | ------------------------------------------------------------------------------ <br/> | ||
SELECT * <br/> | |||
FROM TicketSales <br/> | FROM TicketSales <br/> | ||
WHERE distid >= 2147483647 <br/> | WHERE distid >= 2147483647 <br/> | ||
| Line 30: | Line 30: | ||
FROM TicketEventCodes <br/> | FROM TicketEventCodes <br/> | ||
WHERE CAST(CreatedDate AS DATE) = CAST(getdate() as date) <br/> | WHERE CAST(CreatedDate AS DATE) = CAST(getdate() as date) <br/> | ||
) <br/> | |||
------------------------------------------------------------------------------ <br/> | ------------------------------------------------------------------------------ <br/> | ||
-- Run top query and copy sponsor ids into second query <br/> | -- Run top query and copy sponsor ids into second query <br/> | ||
| Line 37: | Line 37: | ||
-- aka update sponsor of distIDs in first query to their sponsor's sponsor <br/> | -- aka update sponsor of distIDs in first query to their sponsor's sponsor <br/> | ||
------------------------------------------------------------------------------ <br/> | ------------------------------------------------------------------------------ <br/> | ||
select * <br/> | |||
from DistributorDetail <br/> | from DistributorDetail <br/> | ||
where sponsorid >= 2147483647 <br/> | where sponsorid >= 2147483647 <br/> | ||
and distid < 2147483647 <br/> | and distid < 2147483647 <br/> | ||
<br/> | <br/> | ||
select DistID, SponsorID, * <br/> | |||
from distributordetail <br/> | from distributordetail <br/> | ||
where distid in () <br/> | where distid in () <br/> | ||
<br/> | <br/> | ||
update DistributorDetail <br/> | |||
set SponsorID = , UplineDID = <br/> | set SponsorID = , UplineDID = <br/> | ||
where distid = <br/> | where distid = <br/> | ||
------------------------------------------------------------------------------ <br/> | ------------------------------------------------------------------------------ <br/> | ||
-- Run queries and change "select *" to "Delete" and run <br/> | -- Run queries and change "select *" to "Delete" and run <br/> | ||
------------------------------------------------------------------------------ <br/> | ------------------------------------------------------------------------------ <br/> | ||
select * <br/> | |||
from Payment <br/> | from Payment <br/> | ||
where orderid in <br/> | where orderid in <br/> | ||
| Line 61: | Line 61: | ||
from orders <br/> | from orders <br/> | ||
where distid >= 2147483647 <br/> | where distid >= 2147483647 <br/> | ||
) | |||
select * <br/> | |||
from orderlines <br/> | from orderlines <br/> | ||
where orderid in <br/> | where orderid in <br/> | ||
| Line 70: | Line 70: | ||
from orders <br/> | from orders <br/> | ||
where distid >= 2147483647 <br/> | where distid >= 2147483647 <br/> | ||
) | |||
select * <br/> | |||
from orders <br/> | from orders <br/> | ||
where distid >= 2147483647 <br/> | where distid >= 2147483647 <br/> | ||
select * <br/> | |||
from DistributorDetail <br/> | from DistributorDetail <br/> | ||
where distid >= 2147483647 <br/> | where distid >= 2147483647 <br/> | ||
select * <br/> | |||
from Distributor <br/> | from Distributor <br/> | ||
where distid >= 2147483647 | where distid >= 2147483647 | ||
Revision as of 18:52, 8 March 2023
Customizations:
Imports:
Vision Global has two imports they will request, they will send you files for both imports.
Data Dump import and Ticket Sales import.
Ticket Sales Import:
The ticket sales import will require you to save the files to the .33 server in "c$\temp" folder you will rename the files to "TicketSales_JAN 23" and "eventDetail_JAN 23" OR the specified month for example "eventDetail_FEB 23" --MAKE SURE THE MONTH IS 3 CHARACTERS AND NOT LONGER--
Proceed to the .35, pull files you saved from .33 to .35
Once you have pulled the files from .35 head to C:\Inetpub\wwwroot\Prodigix Services\Ticket Import\Upload, and place both files in at the same time.
The files will be moved and processed at the same time, you will then run the script's below to make sure the data was imported correct on the SQL server, 173. VisionGLobal_Live, once you have ran the scripts the import will be successful.
-- check to verify the ticket import completed succesfully, change date to the day you run the import
( SELECT EventCode
FROM TicketEventCodes
WHERE CAST(CreatedDate AS DATE) = '10/17/2022'
)
-- Change date to date of most recent ticket import
-- check to verify no records returned in following query. If so, do not run anything below
SELECT *
FROM TicketSales
WHERE distid >= 2147483647
AND EventCode IN
(
SELECT EventCode
FROM TicketEventCodes
WHERE CAST(CreatedDate AS DATE) = CAST(getdate() as date)
)
-- Run top query and copy sponsor ids into second query
-- Update distIDs in first query to have sponsor of current sponsor. Usually returns 3 distIDs.
-- aka update sponsor of distIDs in first query to their sponsor's sponsor
select *
from DistributorDetail
where sponsorid >= 2147483647
and distid < 2147483647
select DistID, SponsorID, *
from distributordetail
where distid in ()
update DistributorDetail
set SponsorID = , UplineDID =
where distid =
-- Run queries and change "select *" to "Delete" and run
select *
from Payment
where orderid in
( select OrderID
from orders
where distid >= 2147483647
)
select *
from orderlines
where orderid in
(
select OrderID
from orders
where distid >= 2147483647
)
select *
from orders
where distid >= 2147483647
select *
from DistributorDetail
where distid >= 2147483647
select *
from Distributor
where distid >= 2147483647