How to Force a Cache Update for UI Scripts - Developer Community - Article - ServiceNow Community

User Object Cheat Sheet
N

o matter what system you’re working in, it is always critical to be able to identify information about the user who is accessing that system. Being able to identify who the user is, what their groups and/or roles are, and what other attributes their user record has are all important pieces of information that allow you to provide that user with a good experience (without giving them information they don’t need to have or shouldn’t have). ServiceNow gives administrators some pretty simple ways to identify this information in the form of a couple of user objects and corresponding methods. This article describes the functions and methods you can use to get information about the users accessing your system.



GlideSystem User Object

The GlideSystem (gs) user object is designed to be used in any server-side JavaScript (Business rules, UI Actions, System security, etc.). The following table shows how to use this object and its corresponding functions and methods.

Function/Method	Return Value	Usage
gs.getUser()	Returns a reference to the user object for the currently logged-in user.	var userObject = gs.getUser();
gs.getUserByID()	Returns a reference to the user object for the user ID (or sys_id) provided.	var userObject = gs.getUser().getUserByID('employee');
gs.getUserName()	Returns the User ID (user_name) for the currently logged-in user.
e.g. 'employee'	var user_name = gs.getUserName();
gs.getUserDisplayName()	Returns the display value for the currently logged-in user.
e.g. 'Joe Employee'	var userDisplay = gs.getUserDisplayName();
gs.getUserID()	Returns the sys_id string value for the currently logged-in user.	var userID = gs.getUserID();
getFirstName()	Returns the first name of the currently logged-in user.	var firstName = gs.getUser().getFirstName();
getLastName()	Returns the last name of the currently logged-in user.	var lastName = gs.getUser().getLastName();
getEmail()	Returns the email address of the currently logged-in user.	var email = gs.getUser().getEmail();
getDepartmentID()	Returns the department sys_id of the currently logged-in user.	var deptID = gs.getUser().getDepartmentID();
getCompanyID()	Returns the company sys_id of the currently logged-in user.	var companyID = gs.getUser().getCompanyID();
getCompanyRecord()	Returns the company GlideRecord of the currently logged-in user.	var company = gs.getUser().getCompanyRecord();
getLanguage()	Returns the language of the currently logged-in user.	var language = gs.getUser().getLanguage();
getLocation()	Returns the location of the currently logged-in user.	var location = gs.getUser().getLocation();
getDomainID()	Returns the domain sys_id of the currently logged-in user (only used for instances using domain separation).	var domainID = gs.getUser().getDomainID();
getDomainDisplayValue()	Returns the domain display value of the currently logged-in user (only used for instances using domain separation).	var domainName = gs.getUser().getDomainDisplayValue();
getManagerID()	Returns the manager sys_id of the currently logged-in user.	var managerID = gs.getUser().getManagerID();
getMyGroups()	Returns a list of all groups that the currently logged-in user is a member of.	var groups = gs.getUser().getMyGroups();
isMemberOf()	Returns true if the user is a member of the given group, false otherwise.	Takes either a group sys_id or a group name as an argument.

if(gs.getUser().isMemberOf(current.assignment_group)){
//Do something...
}

var isMember = gs.getUser().isMemberOf('Hardware');

To do this for a user that isn't the currently logged-in user...

var user = 'admin';
var group = "Hardware";
if (gs.getUser().getUserByID(user).isMemberOf(group)){
gs.log( gr.user_name + " is a member of " + group);
}

else{
gs.log( gr.user_name + " is NOT a member of " + group);
}
gs.hasRole()	Returns true if the user has the given role, false otherwise.	if(gs.hasRole('itil')){ //Do something... }
gs.hasRole()	Returns true if the user has one of the given roles, false otherwise.	if(gs.hasRole('itil,admin')){
//If user has 'itil' OR 'admin' role then Do something...
}
hasRoles()	Returns true if the user has any roles at all, false if the user has no role (i.e. an ess user).	if(!gs.getUser().hasRoles()){
//User is an ess user...
}
It is also very simple to get user information even if the attribute you want to retrieve is not listed above by using a ‘gs.getUser().getRecord()’ call as shown here…

//This script gets the user's title
gs.getUser().getRecord().getValue('title');


g_user User Object

The g_user object can be used only in UI policies and Client scripts. Contrary to its naming, it is not truly a user object. g_user is actually just a handful of cached user properties that are accessible to client-side JavaScript. This eliminates the need for most GlideRecord queries from the client to get user information (which can incur a fairly significant performance hit if not used judiciously).

g_user Property or Method	Return value
g_user.userName	User name of the current user e.g. employee
g_user.firstName	First name of the current user e.g. Joe
g_user.lastName	Last name of the current user e.g. Employee
g_user.userID	sys_id of the current user e.g. 681ccaf9c0a8016400b98a06818d57c7
g_user.hasRole()	True if the current user has the role specified, false otherwise. ALWAYS returns true if the user has the 'admin' role.

Usage: g_user.hasRole('itil')
g_user.hasRoleExactly()	True if the current user has the exact role specified, false otherwise, regardless of 'admin' role.

Usage: g_user.hasRoleExactly('itil')
g_user.hasRoles()	True if the current user has at least one role specified, false otherwise.

Usage: g_user.hasRoles('itil','admin')
It is often necessary to determine if a user is a member of a given group from the client as well. Although there is no convenience method for determining this from the client, you can get the information by performing a GlideRecord query. Here’s an example…

//Check to see if assigned to is a member of selected group
var grpName = 'YOURGROUPNAMEHERE';
var usrID = g_form.userID; //Get current user ID
var grp = new GlideRecord('sys_user_grmember');
grp.addQuery('group.name', grpName);
grp.addQuery('user', usrID);
grp.query(groupMemberCallback);
   
function groupMemberCallback(grp){
//If user is a member of selected group
    if(grp.next()){
        //Do something
        alert('Is a member');
    }
    else{
        alert('Is not a member');
    }
}
To get any additional information about the currently logged-in user from a client-script or UI policy, you need to use a GlideRecord query. If at all possible, you should use a server-side technique described above since GlideRecord queries can have performance implications when initiated from a client script. For the situations where there’s no way around it, you could use a script similar to the one shown below to query from the client for more information about the currently logged in user.

//This script gets the user's title
var gr = new GlideRecord('sys_user');
gr.get(g_user.userID);
var title = gr.title;
alert(title);


Useful Scripts

There are quite a few documented examples of some common uses of these script methods. These scripts can be found on the ServiceNow wiki.

/* This will come in the body at XML but the header will be text/plain, which is why  
all this is happening manually to get this read from the stream.  
*/  
var reqHeaders = request.headers;
gs.info("CPSNS: reqHeaders content-type" + reqHeaders['content-type']);
if (reqHeaders['content-type'] == "text/plain; charset=UTF-8") {
    var stream = request.body.dataStream;
    var reader = new GlideTextReader(stream);
    var input = "";
    var ln = "";
    while ((ln = reader.readLine()) != null) {
        input += ln;
    }
    gs.info("CPSNS: SUB" + input);
} else {
    var body = {};
    var data = request.body.data;

    gs.info("CPSNS: req.body data" + data);

}

new GlideQuery('sys_user')
    .select('company$DISPLAY')
    .forEach(function (user) {
        gs.info(user.company$DISPLAY);
    });

// ACME North America
// ServiceNow
// ...

Fear Not; There is a Better Workaround
...and I do mean workaround. This is by no means to be considered a fix. The secret is in the browser cookies. We know that many plugins contain UI Scripts. These applications are updated from time to time (including the UI Scripts) and that works fine. What's the difference? Activating a plugin triggers a forced cache update for the users' browsers. The difference is that updating a UI Script doesn't trigger this. Not even a cache.do triggers it. It simply forces the server to update cache. Installing/updating plugins seems to be the sole trigger that forces the browser to ask the server for updated cache for UI Scripts.

So how do you force this cache update?
The check for building new cache in this instance is controlled by a cookie on the client browser that ServiceNow creates. The cookie is controlled by a property in ServiceNow called glide.lastupdate. To force this update to happen, all you have to do is set that property to a newer dateTime than it currently is (e.g. the current timestamp). You can do this manually, but that can also be a form of kicking the can down the road. You will be back here soon. At Walmart, we wrote a Business Rule to update this whenever a ui_script is updated:

(function executeRule(current, previous /*null when async*/) {
	
	var gdt = new Date();
	var gdtString = gdt.toString().replace(/ /g,'_').replace(/:/g,'_');
	var timeZone = gdtString.replace(/.*[(](.*)[)].*/,'$1');
	var dateFinal = gdtString.slice(0,25) + timeZone;
	gs.addInfoMessage('System Property glide.lastplugin updated. ' + 
					  'Please be sure to push this updated property with the rest of your code. ' +
					  'This will ensure that the JavaScript cache is updated on the client browser.');
	gs.setProperty('glide.lastplugin', dateFinal);
	
})(current, previous);
I have attached an export of this business rule for any who are interested. The info message reminds developers to ensure this updated property is included in their update set, since business rules are not triggered on update commit.

SetTitleMatchMode 2
DetectHiddenText, on
Process, priority, AutoHotkey.exe, high
Process, priority, tn525032.exe, high

SetBatchLines, 20ms
SetKeyDelay, 10ms
SetMouseDelay, 10ms
SetWinDelay, 10ms
SetControlDelay, 10ms
SetWorkingDir , %A_ScriptDir%

#NoEnv
#include DDE.ahk
#include RumbaDDE.ahk
#include Table.ahk
#singleinstance force

Apptitle = AS/400 Pull

secvarorg = 2  ;Number of seconds to wait till autoconnecting to AS400
	
GUI:
Gui, Color, 0xbbd5f4
Gui, Margin, X
Gui, Font, norm
Gui, Font, s7
Gui, Add, Button, gddecount W130, Please Wait...
Gui, Add, Button, gRunIAFEP W130, Please Wait...
Gui, Add, Button, gSetup W130, Run Rumba Setup
Gui, Add, Button, gBUTTONSTOP W130, STOP
;Gui, Add, Button, gddecount W130, count

Gui, Add, GroupBox, YM x+20 section W150 H130, AS/400 Session Select
Gui, Add, Radio, xs+10  ys+20 vRumbaA  gButtonConnect checked	, Session A
Gui, Add, Radio, xs+10  ys+40 vRumbaB  gButtonConnect 			, Session B
Gui, Add, Radio, xs+10  ys+60 vRumbaC  gButtonConnect 			, Session C
Gui, Add, Radio, xs+10  ys+80 vRumbaD  gButtonConnect 			, Session D
Gui, Add, Radio, xs+10  ys+100 vRumbaT gButtonConnect 			, Session Test

Gui, Add, Text, Ym x+70 W100,Auto-Connecting
Gui, Add, Text,  W100,( In %secvarorg% Seconds )
Gui, Add, Text,  W100,
Gui, Add, Text,  W100,
Gui, Add, Text,  W100,

Gui, Show, , %Apptitle%

loop, %secvarorg%
{
sleep,1000
secvarorg--
seccountdown := "( In " secvarorg " Seconds )"
GuiControl,, Static2 , %seccountdown%
}

If ( RumbaB > 0 || RumbaC > 0 || RumbaD > 0 || RumbaT > 0)
{
return
}

goto buttonConnect
return
;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`


;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
ButtonStop:
DDE_Kill()
Return
;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`


;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
buttonConnect:
Gui, Submit, nohide
Start := A_TickCount
SetTimer, ActivateWinUM, 100

if RumbaA = 1
{
Rumbawsf :="A.WSF"
}
if RumbaB = 1
{
Rumbawsf :="B.WSF"
}
if Rumba C = 1
{
Rumbawsf :="C.WSF"
}
if RumbaD = 1
{
Rumbawsf :="D.WSF"
}
if RumbaT = 1
{
Rumbawsf :="TEST.WSF"
}

DDE_Connect("Rumbawsf",Rumbawsf)
DDE_Kill()
Sleep,250
DDE_Connect("Rumbawsf",Rumbawsf)

SetTimer, ActivateWinUM, off
if DDEErrorCount > 2
{
GuiControl,, Static1 , % "Failed Connection"
GuiControl,, Static2 , % "Timeout After: "
GuiControl,, Static3 , % round(connecttimercount) " Seconds"
Return
}
Else
{
ToolTip,||||||||||||||||||||||||||||||||`n||||||||||||||||||||||||||||||||`n||||||||||||||||||||||||||||||||`n`nDDE CONNECTED to %Rumbawsf%`n`n||||||||||||||||||||||||||||||||`n||||||||||||||||||||||||||||||||`n||||||||||||||||||||||||||||||||
SetTimer, RemoveToolTip, 1000
GuiControl,, Static1 , % "Connected"
GuiControl,, Static2 , To: %Rumbawsf%
GuiControl,, Static3 , % "In: " connecttimercount " Seconds"
GuiControl,, Button1, % "Run Create List" 
GuiControl,, Button2, % "Run Delete List" 

return
}
Return

ActivateWinUM:
MouseGetPos,,, WinUMID
ToolTip,Please wait `n`nDDE Connecting to %Rumbawsf%

GuiControl,, Static1 , % "Connecting to"
GuiControl,, Static2 , % "To: " Rumbawsf
connecttimercount := SubStr("0" Floor((A_TickCount - Start) / 1000 - Floor((A_TickCount - Start) / 60000) * 60), -1, 2) "." SubStr("0" Mod(A_TickCount - Start, 1000), -1, 2)
GuiControl,, Static3 , % "For: " connecttimercount " Seconds"

if connecttimercount >= 15
{
DDE_Kill("Rumbawsf",Rumbawsf)
SetTimer, ActivateWinUM, off
ToolTip
msgbox, Make sure the appropriate Rumba Session is Open.
}
return

RemoveToolTip:
SetTimer, RemoveToolTip, Off
ToolTip
return
;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`


;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`
Setup:
Rumba := "ahk_class WdPageFrame"
winactivate, %Rumba%
winwaitactive, ahk_class WdPageFrame
IfWinNotActive, %Rumba%, , WinActivate, %Rumba%,	;activates a window if it is not already active
WinWaitActive, %Rumba%, 		
;---Options>Edit
WinMenuSelectItem, %Rumba%,, Options , Edit 										;opens a windows menu
winwait, Edit Options
;---Options>Edit>Features
Control, Check,, button1, Edit Options
sleep, 50
Control, Uncheck,, button2, Edit Options
sleep, 50
Control, Uncheck,, button3, Edit Options
sleep, 50
;---Options>Edit>Parsing
SendMessage, 0x1330, 2,, SysTabControl321, Edit Options
sleep, 50
Control, check,, button2, Edit Options
sleep, 50
WinWait, Edit Options, 
IfWinNotActive, Edit Options, , WinActivate, Edit Options, 
WinWaitActive, Edit Options, 
;---Options>Edit>Ok Button on Parsing Tab
control, check,, button13, Edit Options
;---Options>Display
WinMenuSelectItem, %Rumba%,, Options , Display
winwait, Display Options
sleep, 50
Control, Uncheck,, button8, Display Options ;Input Error Dialog
WinWait, Display Options, 
IfWinNotActive, Display Options, , WinActivate, Display Options, 
WinWaitActive, Display Options, 
;---Options>Display>Ok Button on Display Tab
control, check,, button12, Display Options
SetTitleMatchMode, fast
return
;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`

RunIAFEP:
WaitStringUni("IND2762")
Loop 16
{
Cashier := AutoBlockUni((8+a_index),8)
RemoveInString(Cashier,,"C N R T") 
;---
fesls := AutoBlockUni((8+a_index),47)
RemoveInString(fesls,,"C N R T")
;---
avgodr := AutoBlockUni((8+a_index),58)
RemoveInString(avgodr,,"C N R T")
;---
MonPerSgnOnHR := AutoBlockUni((8+a_index),71)
RemoveInString(MonPerSgnOnHR,,"C N R T")
;---
MPH := AutoBlockUni((8+a_index),83)
RemoveInString(MPH,,"C N R T")
;---
SgnOffPerNumTans := AutoBlockUni((8+a_index),97)
RemoveInString(SgnOffPerNumTans,,"C N R T")
;---
IPM := AutoBlockUni((8+a_index),107)
RemoveInString(IPM,,"C N R T")
;---
ScanPercent := AutoBlockUni((8+a_index),117)
RemoveInString(ScanPercent,,"C N R T")
;---
ItemsPerMemb := AutoBlockUni((8+a_index),129)
RemoveInString(ItemsPerMemb,,"C N R T")

PGOne .= cashier a_tab fesls a_tab avgodr a_tab MonPerSgnOnHR a_tab MPH a_tab SgnOffPerNumTans a_tab IPM a_tab ScanPercent a_tab ItemsPerMemb "`n"
}
SendDDEUni("@k")
Loop 16
{
InaTime := AutoBlockUni((8+a_index),42)
RemoveInString(InaTime,,"C N R T") 
;---
NumOur := AutoBlockUni((8+a_index),52)
RemoveInString(NumOur,,"C N R T")
;---
Pickups := AutoBlockUni((8+a_index),62)
RemoveInString(Pickups,,"C N R T")
;---
VoidTrans := AutoBlockUni((8+a_index),72)
RemoveInString(VoidTrans,,"C N R T")
;---
FERew := AutoBlockUni((8+a_index),84)
RemoveInString(FERew,,"C N R T")
;---
NumOfMembs := AutoBlockUni((8+a_index),96)
RemoveInString(NumOfMembs,,"C N R T")
;---
PercentKeyed := AutoBlockUni((8+a_index),105)
RemoveInString(PercentKeyed,,"C N R T")
;---
SignOffs := AutoBlockUni((8+a_index),117)
RemoveInString(SignOffs,,"C N R T")
;---
NumItems := AutoBlockUni((8+a_index),129)
RemoveInString(NumItems,,"C N R T")

PGTwo .= InaTime a_tab NumOur a_tab Pickups a_tab VoidTrans a_tab FERew a_tab NumOfMembs a_tab PercentKeyed a_tab SignOffs a_tab NumItems "`n"
Joined := Table_Append( PGOneLeft, PGOneRight,1)
}
SendDDEUni("@k")
/*Loop 16
{
InaTimePerMem := AutoBlockUni((8+a_index),42)
RemoveInString(InaTime,,"C N R T") 
;---
TendTimePerMem := AutoBlockUni((8+a_index),52)
RemoveInString(TendTimePerMem,,"C N R T")
;---
RingTimePerMem := AutoBlockUni((8+a_index),62)
RemoveInString(RingTimePerMem,,"C N R T")
;---
NonSlsTimePerMem := AutoBlockUni((8+a_index),72)
RemoveInString(NonSlsTimePerMem,,"C N R T")
;---
TotTimePerMem := AutoBlockUni((8+a_index),84)
RemoveInString(TotTimePerMem,,"C N R T")
;---
TotSgnOnTime := AutoBlockUni((8+a_index),96)
RemoveInString(TotSgnOnTime,,"C N R T")

PGThree .= InaTimePerMem a_tab TendTimePerMem a_tab RingTimePerMem a_tab NonSlsTimePerMem a_tab TotTimePerMem a_tab TotSgnOnTime "`n"
}
*/
;clipboard := PGO
msgbox % Joined
clipboard := Joined
return
;~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`~,~`

#Persistent
;
CapsLock:: 
	Keywait, CapsLock
	If (A_PriorKey="CapsLock")
		return
Return
#if, GetKeyState("CapsLock", "P")
;CLEANUP
p::FileRecycleEmpty				;Capslock + P		empty recycle bin
;
;CLIPBOARD
u::Run %clipboard%				;Capslock + U		Start URL in clipboard
v::							;Capslock + V		Paste as plain text
ClipSaved := ClipboardAll
Clipboard = %Clipboard%
SendInput, ^v
Sleep, 250
Clipboard := ClipSaved
VarSetCapacity(ClipSaved, 0)
return
s::							;Capslock + S		search Startpage, text in clipboard
Send, ^{vk43} ;Ctrl C
Sleep 50
Run, https://www.startpage.com/do/search?q=%clipboard%
return
t::							;Capslock + T		translate clipboard with DeepL
Send, ^{vk43} ;Ctrl C
Sleep 50
Run, https://www.deepl.com/translator
Sleep 1000
Send ^{vk56} ;Ctrl V
;
return
;WINDOWS
c::							;Capslock + C		centre active window
	WinGetPos,,, Width, Height, A
	WinMove, A,, (A_ScreenWidth/2)-(Width/2), (A_ScreenHeight/2)-(Height/2)
return
;FAVORITE TOOLS
numpad1::Run, D:\EverEdit\EverEdit.exe				;Capslock + NumPad1	EverEdit
numpad4::Run, D:\FreeCommander\FreeCommander.exe	;Capslock + NumPad4	FreeCommander
;
;**********					;End Capslock hotkeys
#If
;**********					;End Capslock hotkeys

#NoEnv
SendMode Input
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
;keyhistory
;#InstallMouseHook
#installkeybdhook
SetNumLockState,On
SetNumLockState,AlwaysOn

SetScrollLockState,Off
SetScrollLockState,AlwaysOff
return
;

CapsLock::
	KeyWait, CapsLock
	If (A_PriorKey="CapsLock")
		SetCapsLockState, % GetKeyState("CapsLock","T") ? "Off" : "On"
Return

#If, GetKeyState("CapsLock", "P") ;Your CapsLock hotkeys go below

;i::+Tab
i::Up
j::Left
;k::Tab
k::Down
l::Right ;u::Send {Home}
u::-  ;o::Send {End}
;u::Send {End}
o::Run C:\A Steel Trap\Duo\best ahk\done\move_from_photos_to_end_of_title.ahk
;o::Send {End} ;p::Send {PgUp}
p::Run C:\A Steel Trap\Duo\best ahk\done\item_specifics.ahk  ;p::Send {Home}
[::Send {End}
]::Send {PgDn}
=::Send {PgUp}
`;::Send {BS}
Space::Send {Return}
0::Send {PgUp}
1::6
2::7
3::8
4::9
5::
SC029::-

WheelLeft::^PgUp
WheelRight::^PgDn
;XButton2::Home
;XButton1::End


sc045::Send {down 9}
Wheeldown::Down
Wheelup::Up
!m::Run C:\A Steel Trap\Duo\best ahk\search_master_list_box.ahk
^F7::Run C:\A Steel Trap \Duo\best ahk\close firefox tabs to the right.ahk
^PgUp::Run C:/A Steel Trap/Duo/best ahk/done/photo_gallery.ahk
^PgDn::Run C:/A Steel Trap/Duo/best ahk/done/photo_gallery_open_double_including_previous_photo.ahk
Numlock::Run C:\A Steel Trap\Duo\best ahk\6bitStart.ahk

LButton::Run C:\A Steel Trap\Duo\best ahk\activatesixbit.ahk
;RButton::Run C:\A Steel Trap\Duo\best ahk\ahk to do\clickdragphotos.ahk
RButton::Run C:\A Steel Trap\Duo\best ahk\done\organize_photo_order_click.ahk
MButton::Run C:\A Steel Trap\Duo\best ahk\done\organize_photo_order_shoes-dbltagmove_click.ahk
Enter::^v
PgUp::Volume_Up
PgDn::Volume_Down
Right::Media_Next
Left::Media_Prev
Down::Media_Play_Pause
q::y
;w::u
w::Run C:\A Steel Trap\Duo\best ahk\done\google.ahk
;e::i
e::!d
r::o
t::p
y::send t-strap `

;a::h
;s::j
;d::k
;f::l
a::Run C:\A Steel Trap\Duo\best ahk\done\firefox.ahk
s::f4
d::^w
f::^t


g::send t-shirt `
h::Run C:\A Steel Trap\Duo\best ahk\clickwind_pending_pics.ahk

z::b
x::n
c::m
v::send v-neck`  ;v::Run C:\A Steel Trap\Duo\best ahk\done\save_and_exit.ahk ;b::send pullover ` ;n::Run C:\A Steel Trap\Duo\best ahk\done\centermouse&click.ahk
b::send t-strap `
;n::Up
;n::+Tab
n::Send .5`
;m::Down
;m::Send {-}`
m::+Tab
;Send {-}`
;m::send {#}` ;Tab::Send {Tab 5}
;Tab::Send {Tab 3} ;
Tab::Run C:\A Steel Trap\Duo\best ahk\timenowdate.ahk
End::Run C:\A Steel Trap\Duo\best ahk\done\saveclose2.ahk
;^F11::Run C:\A Steel Trap\Duo\best ahk\ahk to do\clickwind_delete_photos.ahk
;,::Send .5`  ;,::Send {!}`  
,::Send /P`
.::Send .5`
;.::Run C:\A Steel Trap\Duo\best ahk\done\new_item.ahk
F12::Send e-mail address
/::Run C:\A Steel Trap\Duo\best ahk\done\saveclose2.ahk
;Backspace::Run C:\A Steel Trap\Duo\best ahk\ahk to do\clickdragphotos.ahk
Backspace::Run C:\A Steel Trap\Duo\best ahk\done\photo_gallery_close_all_photos.ahk
;Backspace::Run C:\A Steel Trap\Duo\best ahk\done\organize_photo_order_click.ahk
;Run C:\A Steel Trap\Duo\best ahk\music\copy.ahk
;Left::+Tab
;Delete::Run C:\A Steel Trap\Duo\best ahk\music\copy.ahk
^c::Run C:\A Steel Trap\Duo\best ahk\clickwind_pending_pics.ahk

;
ScrollLock::
	KeyWait, ScrollLock
	If (A_PriorKey="ScrollLock")
		SetScrollLockState, % GetKeyState("ScrollLock","T") ? "Off" : "On"
Return

#If, GetKeyState("ScrollLock", "P") ;Your ScrollLock hotkeys go below

;i::+Tab
i::s
a::Run C:\A Steel Trap\Duo\best ahk\done\new_item_clear_photos.ahk
s::Run C:\A Steel Trap\Duo\best ahk\done\organize_photo_order.ahk
;d::Run C:\A Steel Trap\Duo\best ahk\done\organize_photo_order_shoes-dbltagmove.ahk
Tab::f4
Numpad0::
Numpad1::
;Numpad2::Send {Down 9}
Numpad2::Tab
Numpad3::
Numpad4::Left
;Numpad5::Down
Numpad5::+Tab
Numpad6::Right
Numpad7::
Numpad8::Up
Numpad9::Run C:\A Steel Trap\Duo\best ahk\click_win_sold_items_search_when_shipping.ahk
NumpadEnter::
t::Send personal email`
;w::^w
w::^t
f::Run C:\A Steel Trap\Duo\best ahk\done\firefox.ahk
g::Run C:\A Steel Trap\Duo\best ahk\done\google.ahk



Up::Send {Up 5}
;

NumLock::
	KeyWait, NumLock
		
#If, GetKeyState("NumLock", "P")

Numpad0::Space
Numpad1::Run C:\A Steel Trap\Duo\best ahk\done\saveclose2.ahk
Numpad2::Tab
Numpad3::Send {Tab 6}
Numpad4::Left
;Numpad5::Run C:\A Steel Trap\Duo\best ahk\click_win_sold_items_search_when_shipping.ahk
Numpad5::+Tab
Numpad6::Right
Numpad7::Down
Numpad8::Send phone number`
NumpadDot::Run C:\A Steel Trap\Duo\best ahk\done\numpad_half.ahk
;NumpadSub::SC028  ;this is for the single quotation character
NumpadDiv::Run C:\A Steel Trap\Duo\best ahk\done\saveclose2.ahk
;NumpadMult::SC01A
NumpadMult::Run C:\A Steel Trap\Duo\best ahk\done\brackets.ahk
NumpadSub::SC01B
NumpadEnter::Space
Numpad9::Run C:\A Steel Trap\Duo\best ahk\done\numpad_quarter.ahk
NumpadAdd::Run C:\A Steel Trap\Duo\best ahk\done\99cents.ahk


e::+Tab
d::Tab
a::Run C:\A Steel Trap\Duo\best ahk\6bitStart.ahk
s::Run C:\A Steel Trap\Duo\best ahk\done\item_specifics.ahk
q::Run C:\A Steel Trap\Duo\best ahk\done\saveclose2.ahk
;f::Run C:\A Steel Trap\Duo\best ahk\done\add_single_listing.ahk
f::send c108`
d::Send Does not Apply`
c::Send logininfo{Tab}`
r::Run C:\A Steel Trap\Duo\best ahk\done\move_mouse_to_shipping.ahk
w::Run C:\A Steel Trap\Duo\best ahk\done\change_price.ahk
v::Run C:\A Steel Trap\Duo\best ahk\done\new_item.ahk
Tab::Send {Tab 5}
4::Send 14-14.5 - S mens `
5::Send 15-15.5 - M mens `
6::Send 16-16.5 - L mens `
7::Send 17-17.5 - XL mens `
8::Send 18-18.5 - 2XL mens `
Space::Send {Space 7}
t::Send otheremail`
i::+Tab
k::Tab
`;::Send {BS}
j::Send {Tab 7}

Return

;

I have seen few questions wherein users require a VIP Icon for catalog variable when the user is a VIP user..

Use case: Consider you have a Variable which refers to "sys_user" table. When the Request Item form loads or Catalog Task form loads and if the User variable holds a VIP User then vip icon should be visible besides the variable.

Approach:

1) Create an onLoad Catalog Client Script which is set to "Applies on Requested Items" and "Applies on Catalog Tasks"

2) Use Script mentioned below; ensure you give question value in script inside the Contains filter and not the name. Using g_form.getControl() doesn't work on sc_req_item and sc_task tables.

3) Ensure Isolate Script field is set to False for the above Catalog Client Script; this field can be edited from List

Script:

function onLoad() {
	//Type appropriate comment here, and begin script below
	var user = g_form.getReference('user', callBackMethod);
}

function callBackMethod(user){

	if(user.vip.toString() == 'true'){
		var variableQuestionValue = 'User';
		$j("<img src='images/icons/vip.gif'>" ).insertAfter("span.sn-tooltip-basic:contains(" + variableQuestionValue + ")");
	}
}
Screenshots:

1) Variable Question with Value as User being used inside the script



2) Catalog Client Script: Isolate Script -> False



3) RITM Form when User is VIP showing the icon



4) TASK Form when User is VIP showing the icon



5) User "Abel Tuter" is VIP User



Note: If you would like to test this; try changing the value to a user who is not VIP and the icon should not show up

Additionally you can also highlight the background in red/orange color as below

g_form.getDisplayBox('user').style.backgroundColor =  'orange';
You can also show the text in red color as below

g_form.getDisplayBox('user').style.color= 'red';
Thanks for reading the blog and do provide your inputs/suggestions if any.

$Pause:: ; The § should prevent Pause from triggering itself
KeyWait, W, D T3 ; T3 gives you three seconds to press W
if (Errorlevel)
  Send {Pause} ; By the way, this is really just necessary if you wanna preserve the original pause functionality
else
  MsgBox You pressed Pause+W!
return

#!/bin/bash
# Bash Menu Script Example

PS3='Please enter your choice: '
options=("Option 1" "Option 2" "Option 3" "Quit")
select opt in "${options[@]}"
do
    case $opt in
        "Option 1")
            echo "you chose choice 1"
            ;;
        "Option 2")
            echo "you chose choice 2"
            ;;
        "Option 3")
            echo "you chose choice $REPLY which is $opt"
            ;;
        "Quit")
            break
            ;;
        *) echo "invalid option $REPLY";;
    esac
done

<script data-cookieconsent="ignore">
    window.dataLayer = window.dataLayer || [];
    function gtag() {
        dataLayer.push(arguments);
    }
    gtag("consent", "default", {
        ad_storage: "denied",
        analytics_storage: "denied",
        functionality_storage: "denied",
        personalization_storage: "denied",
        security_storage: "granted",
        wait_for_update: 2000,
    });
    gtag("set", "ads_data_redaction", true);
</script>

with            order_base as 
(                
select           soi.*
                ,1/cast( coalesce(cardinality(array_distinct(transform(kpi_categories, x -> slice(x, 1, 1)))), 1) as double) as l1_factor
                ,array_distinct(transform(kpi_categories, x -> slice(x, 1, 1))) level1_kpi_categories
                ,array_distinct(transform(kpi_categories, x -> slice(x, 1, 2))) level2_kpi_categories
                ,array_distinct(transform(kpi_categories, x -> slice(x, 1, 3))) level3_kpi_categories
                ,date(cast(soi.create_datetime as timestamp)) as order_place_date
                ,case when lpp.model_id is not null then 'LPP' else 'Others' end as LPPFlag
from             mp_order.dwd_order_item_all_ent_df__vn_s0_live soi 
left join        vnbi_bd.stg_dwd_lpp_order_item lpp
on               soi.order_id = lpp.order_id
                 and lpp.item_id = soi.item_id 
                 and lpp.model_id = soi.model_id 
                 and lpp.group_id = soi.group_id 
                 and lpp.bundle_order_item_id = soi.bundle_order_item_id        
where            --lpp.model_id is null
                 soi.grass_date >= date'2021-01-01'
                 and soi.is_bi_excluded = 0
                 and date(cast(soi.create_datetime as timestamp)) >= date'2021-01-01'
),
                 order_base_add_factor as 
(
select            ob.*
                ,split_part(kpi_cat.kpi_category[1],':', 1) as l1_kpi_category_id
                ,split_part(kpi_cat.kpi_category[1],':', 2) as l1_kpi_category
                ,split_part(kpi_cat.kpi_category[2],':', 1) as l2_kpi_category_id
                ,split_part(kpi_cat.kpi_category[2],':', 2) as l2_kpi_category
                ,split_part(try(kpi_cat.kpi_category[3]),':', 1) as l3_kpi_category_id
                ,split_part(try(kpi_cat.kpi_category[3]),':', 2) as l3_kpi_category
                ,l1_factor
                ,(1/cast(coalesce(cardinality(array_distinct(filter(level2_kpi_categories, x -> contains(x, kpi_cat.kpi_category[1]) ))), 1) as double)) * l1_factor as l2_factor
                ,(1/cast(coalesce(cardinality(array_distinct(filter(level3_kpi_categories, x -> contains(x, kpi_cat.kpi_category[2]) ))), 1) as double)) * (1 / cast(coalesce(cardinality(array_distinct(filter(level2_kpi_categories, x -> contains(x, kpi_cat.kpi_category[1]) ))), 1) as double))* l1_factor as l3_factor
from             order_base ob 
left join unnest (ob.level3_kpi_categories) as kpi_cat(kpi_category) on true    
),
                ordermart as 
(
select           o.order_id
                ,c.cluster
                ,o.l1_kpi_category
                ,o.l2_kpi_category
                ,o.l3_kpi_category
                ,o.item_id
                ,i.name as item_name
                ,o.model_id
                ,o.shop_id
                ,o.is_cb_shop
                ,o.LPPFlag
                ,i.mtsku_item_id
                ,o.order_place_date
                ,o.is_flash_sale 
                ,o.order_fraction*l3_factor as order_fraction
                ,o.gmv*l3_factor as gmv
                ,o.gmv_usd*l3_factor as gmv_usd
                ,o.item_amount*l3_factor as item_amount
                ,o.item_price_before_discount_pp_usd *l3_factor as item_price_before_discount_pp_usd
                ,o.item_price_before_discount_pp*l3_factor as item_price_before_discount_pp
                ,o.item_input_price_pp*l3_factor as item_input_price_pp
                ,o.item_input_price_pp_usd*l3_factor as item_input_price_pp_usd
                ,o.item_rebate_by_seller_amt*l3_factor as item_rebate_by_seller_amt
                ,o.item_rebate_by_seller_amt_usd*l3_factor as item_rebate_by_seller_amt_usd
                ,o.item_rebate_by_shopee_amt*l3_factor as item_rebate_by_shopee_amt
                ,o.item_rebate_by_shopee_amt_usd*l3_factor as item_rebate_by_shopee_amt_usd
                ,o.merchandise_subtotal_amt*l3_factor as merchandise_subtotal_amt
                ,o.merchandise_subtotal_amt_usd*l3_factor as merchandise_subtotal_amt_usd
                ,i.price as current_price
from            order_base_add_factor o     
inner join      mp_item.dim_item__vn_s0_live i
on              i.item_id = o.item_id
left join       vnbi_bd.shopee_vn_bi_team__kpi_category_cluster_mapping c 
on              c.l1_kpi_category_recat = o.l1_kpi_category
and c.ingestion_timestamp = (select max(ingestion_timestamp) from vnbi_bd.shopee_vn_bi_team__kpi_category_cluster_mapping)
where           i.grass_date = date_add('day',-1,current_date)            
) 

with                base as 
(
select
                    date(from_unixtime(ctime - 3600)) as grass_date,
                    shop_id,
                    status,
                    item_id,
                    data.fields as fields
from                shopee_vn.shopee_item_audit_log_db__item_audit_tab
where               country = 'VN'
                    and from_unixtime(ctime - 3600) >= date'2021-06-01'
), 
                    unnested as 
(
select
                    grass_date,
                    base.shop_id,
                    status,
                    item_id,
                    f.name,
                    f.old,
                    f.new
from                base
left join unnest   (base.fields) as f(name, old, new) on true
)
select              * 
from                unnested
where               name = 'price_before_discount'

WITH fx AS
(SELECT
    *
FROM
    order_mart_dim_exchange_rate
WHERE
    grass_region = 'VN')

, flash_sale AS
(SELECT
    *
FROM
    shopee_vn_bi_team__airpay_sponsor_flashsale_data
WHERE
    ingestion_timestamp = (SELECT MAX(ingestion_timestamp) FROM shopee_vn_bi_team__airpay_sponsor_flashsale_data)
UNION
(SELECT
    *
FROM
    shopee_vn_bi_team__airpay_sponsor_flashsale_new_batch
WHERE
    ingestion_timestamp = (SELECT MAX(ingestion_timestamp) FROM shopee_vn_bi_team__airpay_sponsor_flashsale_new_batch)
AND
    product_id IS NOT NULL)
UNION
(SELECT
    *
FROM
    shopee_vn_bi_team__airpay_sponsor_flashsale_new_scheme
WHERE
    ingestion_timestamp = (SELECT MAX(ingestion_timestamp) FROM shopee_vn_bi_team__airpay_sponsor_flashsale_new_scheme)
AND
    product_id IS NOT NULL)
)

, evoucher_deal AS
(SELECT DISTINCT
	*
FROM
	shopee_vn_bi_team__monthly_voucher_deal_airpay f
WHERE
	ingestion_timestamp = (SELECT MAX(ingestion_timestamp) FROM shopee_vn_bi_team__monthly_voucher_deal_airpay)
)

, collection_data AS
(SELECT
*
FROM
    shopee_vn_bi_team__airpay_collection_sponsor_data
WHERE
    ingestion_timestamp = (SELECT MAX(ingestion_timestamp) FROM shopee_vn_bi_team__airpay_collection_sponsor_data)
AND
    status = 'Confirmed')

, fs_airpay_raw AS (
(
      SELECT
        order_id
      , o.item_id
      , model_id
      , group_id
      , bundle_order_item_id
      , 'FS' as price_point
      FROM
        (((
         SELECT DISTINCT *
         FROM
           flash_sale
      )  f
      INNER JOIN shopee_vn.order_mart_dwd_order_item_all_event_final_status_df o ON (((TRY_CAST(f.product_id AS bigint) = o.item_id) AND (TRY_CAST(f.date AS date) = "date"("split"(o.create_datetime, ' ')[1]))) AND (o.item_promotion_source = 'flash_sale')))
      LEFT JOIN fx f ON (f.grass_date = "date"("split"(o.create_datetime, ' ')[1])))
      WHERE "date"("split"(o.create_datetime, ' ')[1]) BETWEEN date'2020-01-01' and current_date - interval '1' day AND (o.payment_method_id IN (28, 30, 5))   ) UNION (
      SELECT
        order_id
      , o.item_id
      , model_id
      , group_id
      , bundle_order_item_id
      , 'Evoucher' as price_point
      FROM
        (((
         SELECT DISTINCT *
         FROM
           evoucher_deal
      )  f
      INNER JOIN shopee_vn.order_mart_dwd_order_item_all_event_final_status_df o ON (TRY_CAST(f.itemid AS bigint) = o.item_id))
      LEFT JOIN fx f ON (f.grass_date = "date"("split"(o.create_datetime, ' ')[1])))
   ) UNION (
      SELECT
        order_id
      , o.item_id
      , model_id
      , group_id
      , bundle_order_item_id
      , 'Col' as price_point
      FROM
        (((
         SELECT DISTINCT *
         FROM
           collection_data
      )  c
      INNER JOIN shopee_vn.order_mart_dwd_order_item_all_event_final_status_df o ON (((TRY_CAST(c.itemid AS bigint) = o.item_id) AND (TRY_CAST(c.grass_date AS date) = "date"("split"(o.create_datetime, ' ')[1]))) AND (item_promotion_source <> 'flash_sale')))
      LEFT JOIN fx f ON (f.grass_date = "date"("split"(o.create_datetime, ' ')[1])))
      WHERE "date"("split"(o.create_datetime, ' ')[1]) BETWEEN date'2020-01-01' and current_date - interval '1' day AND (o.payment_method_id IN (28, 30, 5))
   ) )


, sellers AS
(SELECT
    shop_id
    , SUM(order_fraction) / 30 AS ado
    , SUM(gmv_usd) / 30 AS adgmv
FROM
    order_mart_dwd_order_item_all_event_final_status_df
WHERE
    DATE(SPLIT(create_datetime, ' ')[1]) BETWEEN DATE_ADD('day',-29,date_trunc('day', from_iso8601_date('2020-07-01') - interval '1' day)) AND from_iso8601_date('2020-07-01')
AND
    shop_id NOT IN (134344983 , 145418026 , 23921767 )
GROUP BY
    1
)

, seller_type AS
(SELECT
    s.shop_id,
    CASE
        WHEN u.is_cb_shop = 1 THEN 'Cross-border'
        WHEN u.is_official_shop = 1 THEN 'Mall'
        WHEN s.ado >= 20 THEN 'Short-tail'
        WHEN s.ado >= 5 AND s.ado < 20 THEN 'Mid-tail'
        WHEN s.ado < 5 THEN 'Long-tail'
    ELSE 'Other' END AS seller_type
FROM
    sellers s
LEFT JOIN
    user_mart_dim_shop u ON s.shop_id = u.shop_id
WHERE
    u.grass_date = current_date - interval '1' day
)

, base AS (
SELECT
    o.*
    , ARRAY_DISTINCT(TRANSFORM(kpi_categories, x -> SLICE(x, 1, 1))) level1_kpi_categories
    , ARRAY_DISTINCT(TRANSFORM(kpi_categories, x -> SLICE(x, 1, 2))) level2_kpi_categories
    , ARRAY_DISTINCT(TRANSFORM(kpi_categories, x -> SLICE(x, 1, 3))) level3_kpi_categories
    , 1/ CAST(CARDINALITY(ARRAY_DISTINCT(TRANSFORM(kpi_categories, x -> SLICE(x, 1, 1)))) AS DOUBLE) l1_factor
FROM
    shopee_vn.order_mart_dwd_order_item_all_event_final_status_df o
WHERE
    DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13')
    AND is_bi_excluded = 0
)

, order_mart AS (
 SELECT
    b.*

    , SPLIT_PART(kpi_cat.kpi_category[1],':', 1) l1_kpi_category_id
    , SPLIT_PART(kpi_cat.kpi_category[1],':', 2) l1_kpi_category

    , SPLIT_PART(kpi_cat.kpi_category[2],':', 1) l2_kpi_category_id
    , SPLIT_PART(kpi_cat.kpi_category[2],':', 2) l2_kpi_category

    , SPLIT_PART(TRY(kpi_cat.kpi_category[3]),':', 1) l3_kpi_category_id
    , SPLIT_PART(try(kpi_cat.kpi_category[3]),':', 2) l3_kpi_category

    , (1 / CAST(COALESCE(CARDINALITY(ARRAY_DISTINCT(FILTER(level2_kpi_categories, x -> CONTAINS(x, kpi_cat.kpi_category[1]) ))), 1) AS DOUBLE)) * l1_factor as l2_factor
    , (1 / CAST(COALESCE(CARDINALITY(ARRAY_DISTINCT(FILTER(level3_kpi_categories, x -> CONTAINS(x, kpi_cat.kpi_category[2]) ))), 1) AS DOUBLE)) * (1 / CAST(COALESCE(CARDINALITY(ARRAY_DISTINCT(FILTER(level2_kpi_categories, x -> CONTAINS(x, kpi_cat.kpi_category[1]) ))), 1) AS DOUBLE)) * l1_factor as l3_factor
FROM
    base b
CROSS JOIN
    unnest (b.kpi_categories) as kpi_cat(kpi_category)
)

, fs_list AS
(
SELECT
    DISTINCT
    TRY_CAST(grass_date AS DATE) grass_date,
    batch,
    TRY_CAST(itemid AS BIGINT) item_id,
    TRY_CAST(modelid AS BIGINT) model_id,
    TRY_CAST(start_time AS TIMESTAMP) start_time,
    TRY_CAST(end_time AS TIMESTAMP) end_time,
    TRY_CAST(promo_price AS BIGINT) promo_price,
    price_point,
    TRY_CAST(stock AS BIGINT) stock
FROM shopee_vn_bi_team__lpp_new_deal_list_v1
WHERE
format_type IS NULL
AND ingestion_timestamp != 'a'
),
col_list_before AS
(
SELECT
    DISTINCT
    TRY_CAST(o.grass_date AS DATE) grass_date,
    TRY_CAST(itemid AS BIGINT) item_id,
    TRY_CAST(modelid AS BIGINT) model_id,
    TRY_CAST(stock AS BIGINT) stock
FROM shopee_vn_bi_team__lpp_collection_list_v1 o
WHERE
TRY_CAST(o.grass_date AS DATE) = DATE '2021-03-03'
AND ingestion_timestamp != 'a'
),
col_list_after AS
(
SELECT
    DISTINCT
    TRY_CAST(o.grass_date AS DATE) grass_date,
    TRY_CAST(itemid AS BIGINT) item_id,
    TRY_CAST(stock AS BIGINT) stock
FROM shopee_vn_bi_team__lpp_collection_list_v1 o
WHERE
TRY_CAST(o.grass_date AS DATE) >= DATE '2021-03-08'
AND ingestion_timestamp NOT IN (SELECT exclude_timestamp FROM shopee_vn_bi_team__lpp_exclude_issue)
),
col_nb AS
(
SELECT
    DISTINCT
    TRY_CAST(o.grass_date AS DATE) grass_date,
    TRY_CAST(itemid AS BIGINT) item_id,
    TRY_CAST(stock AS BIGINT) stock
FROM shopee_vn_bi_team__collection_deals_nb o
WHERE
ingestion_timestamp != 'a'
),
lpp_fs_orders_before AS
(
SELECT
    DISTINCT
    order_id,
    f.item_id,
    f.model_id,
    group_id,
    bundle_order_item_id,
    price_point
FROM order_mart_dwd_order_item_all_event_final_status_df o
JOIN fs_list f
ON DATE(SPLIT(o.create_datetime, ' ')[1]) = DATE(f.grass_date) AND o.item_id = f.item_id AND o.model_id = f.model_id AND FROM_UNIXTIME(create_timestamp - 3600) BETWEEN start_time AND end_time
WHERE
    f.item_id IS NOT NULL
AND item_promotion_source = 'flash_sale'
AND DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN DATE '2021-02-28' AND DATE '2021-03-07'
AND item_rebate_by_shopee_amt > 0
),
lpp_fs_orders_after AS
(
SELECT
    DISTINCT
    order_id,
    f.item_id,
    f.model_id,
    group_id,
    bundle_order_item_id,
    price_point
FROM order_mart_dwd_order_item_all_event_final_status_df o
JOIN fs_list f
ON DATE(SPLIT(o.create_datetime, ' ')[1]) = DATE(f.grass_date) AND o.item_id = f.item_id AND o.model_id = f.model_id AND FROM_UNIXTIME(create_timestamp - 3600) BETWEEN start_time AND end_time
WHERE
    f.item_id IS NOT NULL
AND item_promotion_source = 'flash_sale'
AND DATE(SPLIT(o.create_datetime, ' ')[1]) >= DATE '2021-03-08'
),
lpp_col_orders_before AS
(
SELECT
    DISTINCT
    order_id,
    c.item_id,
    c.model_id,
    group_id,
    bundle_order_item_id,
    'Collection' AS price_point
FROM order_mart_dwd_order_item_all_event_final_status_df o
JOIN col_list_before c
ON o.item_id = c.item_id AND o.model_id = c.model_id AND DATE(SPLIT(o.create_datetime, ' ')[1]) = c.grass_date
WHERE
    DATE(SPLIT(o.create_datetime, ' ')[1]) = DATE '2021-03-03'
AND (item_promotion_source != 'flash_sale' OR item_promotion_source IS NULL)
AND item_rebate_by_shopee_amt > 0
),
lpp_col_orders_after AS
(
SELECT
    DISTINCT
    order_id,
    c.item_id,
    model_id,
    group_id,
    bundle_order_item_id,
    'Collection' AS price_point
FROM order_mart_dwd_order_item_all_event_final_status_df o
JOIN col_list_after c
ON o.item_id = c.item_id AND DATE(SPLIT(o.create_datetime, ' ')[1]) = c.grass_date
WHERE
    DATE(SPLIT(o.create_datetime, ' ')[1]) >= DATE '2021-03-08'
AND (item_promotion_source != 'flash_sale' OR item_promotion_source IS NULL)
),
lpp_col_orders_nb AS
(
SELECT
    DISTINCT
    order_id,
    c.item_id,
    model_id,
    group_id,
    bundle_order_item_id,
    'NB' AS price_point
FROM order_mart_dwd_order_item_all_event_final_status_df o
JOIN col_nb c
ON o.item_id = c.item_id AND DATE(SPLIT(o.create_datetime, ' ')[1]) = c.grass_date
WHERE
    DATE(SPLIT(o.create_datetime, ' ')[1]) >= DATE '2021-05-18'
AND (item_promotion_source != 'flash_sale' OR item_promotion_source IS NULL)
),
lpp_fs_orders AS
(
SELECT * FROM lpp_fs_orders_before
UNION SELECT * FROM lpp_fs_orders_after
UNION SELECT * FROM lpp_col_orders_before
UNION SELECT * FROM lpp_col_orders_after
UNION SELECT * FROM lpp_col_orders_nb
)
, nonbd_orders AS
(
SELECT * FROM lpp_fs_orders
UNION
SELECT * FROM fs_airpay_raw
)

, list AS
(
SELECT
    o.shop_id
    , o.l1_kpi_category
    , CAST(SUM(o.order_fraction * l3_factor) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_gross_ado
    , CAST(COUNT(DISTINCT o.order_id) AS DOUBLE)/(DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_gross_ado_distinct
	, CAST(SUM(o.gmv_usd * l3_factor) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_gross_daily_gmv
    , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.item_amount ELSE 0 END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS l30d_net_qty

    , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.order_fraction * l3_factor END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_net_ado
    , CAST(COUNT(DISTINCT(CASE WHEN is_net_order = 1 THEN o.order_id END)) AS DOUBLE)/(DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_net_ado_distinct
    , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.gmv_usd * l3_factor END) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS fashion_net_daily_gmv

    , COUNT(DISTINCT o.item_id) AS skus
    , SUM(o.item_amount) AS qty

    , sum(case when is_net_order = 1 and regexp_like(pv_voucher_code,'^(MA|WA|KID|FASHION|FA|MS|SO|WB|WS|WBS|WTCH).*') then pv_rebate_by_shopee_amt_usd + sv_rebate_by_shopee_amt_usd end) bd_voucher_direct
    , sum(case when is_net_order = 1 and regexp_like(pv_voucher_code,'^(MA|WA|KID|FASHION|FA|MS|SO|WB|WS|WBS|WTCH).*') then pv_coin_earn_by_shopee_amt_usd + sv_coin_earn_by_shopee_amt_usd end) bd_coin_direct
    , sum(case when is_net_order = 1 and not regexp_like(pv_voucher_code,'^(MA|WA|KID|FASHION|FA|MS|SO|WB|WS|WBS|WTCH).*') then pv_rebate_by_shopee_amt_usd + sv_rebate_by_shopee_amt_usd end) non_bd_voucher_direct
    , sum(case when is_net_order = 1 and not regexp_like(pv_voucher_code,'^(MA|WA|KID|FASHION|FA|MS|SO|WB|WS|WBS|WTCH).*') then pv_coin_earn_by_shopee_amt_usd + sv_coin_earn_by_shopee_amt_usd end) non_bd_coin_direct
    , sum(case when is_net_order = 1 and non_o.order_id is null then item_rebate_by_shopee_amt_usd * l3_factor end) rebate
FROM
    order_mart o
LEFT JOIN nonbd_orders non_o
ON o.order_id = non_o.order_id AND o.item_id = non_o.item_id AND o.model_id = non_o.model_id AND o.group_id = non_o.group_id AND o.bundle_order_item_id = non_o.bundle_order_item_id
WHERE
    l1_kpi_category IN ('Fashion Accessories','Women Clothes','Men Bags','Women Bags','Women Shoes','Men Shoes','Men Clothes','Kid Fashion','Watches','Sport & Outdoor')
GROUP BY 1,2
)

, all_cluster AS
(
SELECT
    o.shop_id
    , o.l1_kpi_category
    , CASE
        WHEN l1_kpi_category IN ('Health','Beauty','Pets','Home care','Moms, Kids & Babies','Grocery') THEN 'FMCG'
        WHEN l1_kpi_category IN ('Fashion Accessories','Women Clothes','Men Bags','Women Bags','Women Shoes','Men Shoes','Men Clothes','Kid Fashion','Watches','Sport & Outdoor') THEN 'Fashion'
        WHEN l1_kpi_category IN ('Home Appliances','Computer & Accessories','Cameras','Consumer Electronics','Mobile & Gadgets','Tickets, Vouchers & Services') THEN 'ELHA'
        WHEN l1_kpi_category IN ('Home & Living','Books','Automotive','Toys') THEN 'Lifestyle'
        ELSE 'Unknown'
    END AS group_category
    , CAST(SUM(o.order_fraction * l3_factor) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS platform_gross_ado
	, CAST(SUM(o.gmv_usd * l3_factor) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS platform_gross_daily_gmv
	, CAST(SUM(CASE WHEN is_net_order = 1 THEN o.gmv_usd * l3_factor END) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS platform_net_daily_gmv
    , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.order_fraction * l3_factor END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS l30d_net_ado
    , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.item_amount ELSE 0 END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS l30d_net_qty
    , COUNT(DISTINCT o.item_id) AS sale_sku
FROM
    order_mart o
GROUP BY 1,2,3
)

, ranking AS
(
SELECT
    shop_id
    , group_category
    , l1_kpi_category
    , ROW_NUMBER() OVER (PARTITION BY shop_id ORDER BY l30d_net_ado DESC, sale_sku DESC, l30d_net_qty DESC) AS ranking
FROM
    all_cluster
)

, new_cat AS
(
SELECT
    shop_id
    , MIN_BY(l1_kpi_category,ranking) AS main_cat
    , MIN_BY(group_category,ranking) AS group_cat
FROM
    ranking
GROUP BY 1
)

, gross AS
(
SELECT
	  n.shop_id
	, CAST(SUM(IF(DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13'),o.order_fraction * l3_factor,0)) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS gross_ado
	, CAST(SUM(IF(DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13'),o.gmv_usd * l3_factor,0)) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS gross_daily_gmv
FROM
    new_cat n
LEFT JOIN
    order_mart o ON n.shop_id = o.shop_id
GROUP BY 1
)

, net AS
(
SELECT
	  n.shop_id
	, CAST(SUM(IF(DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13'), CASE WHEN o.is_net_order = 1 THEN o.order_fraction * l3_factor END,0)) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS net_ado
	, CAST(SUM(IF(DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01') AND from_iso8601_date('2020-07-13'), CASE WHEN o.is_net_order = 1 THEN o.gmv_usd * l3_factor END,0)) AS DOUBLE)/ (DATE_DIFF('day',from_iso8601_date('2020-07-01'),from_iso8601_date('2020-07-13')) + 1) AS net_daily_gmv
FROM
    new_cat n
LEFT JOIN
    order_mart o ON n.shop_id = o.shop_id
GROUP BY 1
)

, main_subcat AS
(
SELECT
      r.shop_id
    , r.main_cat
    , o.l2_kpi_category
    , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.order_fraction * l3_factor ELSE 0 END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'), from_iso8601_date('2020-07-13')) + 1) AS subcat_l30d_net_ado
    , CAST(SUM(CASE WHEN is_net_order = 1 THEN o.item_amount ELSE 0 END) AS DOUBLE) / (DATE_DIFF('day',from_iso8601_date('2020-07-01'), from_iso8601_date('2020-07-13')) + 1) AS subcat_l30d_net_qty
    , COUNT(DISTINCT o.item_id) AS subcat_sale_sku
FROM
    new_cat r
JOIN
    order_mart o ON r.shop_id = o.shop_id AND r.main_cat = o.l1_kpi_category
AND DATE(SPLIT(o.create_datetime, ' ')[1]) BETWEEN from_iso8601_date('2020-07-01')  AND from_iso8601_date('2020-07-13')
AND l1_kpi_category IN ('Fashion Accessories','Women Clothes','Men Bags','Women Bags','Women Shoes','Men Shoes','Men Clothes','Kid Fashion','Watches','Sport & Outdoor')
GROUP BY 1,2,3
)

, subcat_ranking AS
(
SELECT
    shop_id
    , main_cat
    , l2_kpi_category
    , ROW_NUMBER() OVER (PARTITION BY shop_id ORDER BY subcat_l30d_net_ado DESC, subcat_sale_sku DESC, subcat_l30d_net_qty DESC) AS ranking
FROM
    main_subcat
)

, new_subcat AS
(
SELECT
    shop_id
    , MIN_BY(main_cat,ranking) AS main_cat
    , MIN_BY(l2_kpi_category,ranking) AS sub_cat
FROM
    subcat_ranking
GROUP BY 1
)

, all AS
(SELECT DISTINCT
    "date_parse"(effective_dt, '%Y%m%d') cdate,
    log_id,
    shop_id,
    point
FROM shopee_event_data__penalty_fact_tidb_penalty_history_log_tab_v2_da t
WHERE country = 'VN'
    AND metrics_type = 0
    AND metrics_id = 101
    ANd manual_status = 1
    AND auto_status = 1
    AND "date_parse"(effective_dt, '%Y%m%d') >= IF(date_trunc('quarter', current_date - interval '1' day) > date_trunc('week',date_trunc('quarter',current_date - interval '1' day))
                                                                    , date_trunc('week',date_trunc('quarter',current_date - interval '1' day)) + interval '7' day
                                                                    , date_trunc('week',date_trunc('quarter',current_date - interval '1' day))
                                                                    )
                )

, penalty_table AS
(SELECT
    shop_id,
    SUM(point) as penalty_point
FROM
    all
GROUP BY 1)

, nfr_table AS
(
SELECT
    shop_id
    , nfr
FROM
    shopee_event_data__penalty_aggr_nfr_7_res_v4_da
WHERE
    utc_date = date_trunc('week',current_date) - interval '1' day
)

, lsr_table AS
(
SELECT
    shop_id
    , lsr
FROM
    shopee_event_data__penalty_aggr_lsr_7_res_v4_da
WHERE
    utc_date = date_trunc('week',current_date) - interval '1' day
)

, preorder AS
(
SELECT
    shopid,
    SUM(if(grass_date = DATE_TRUNC('week',CURRENT_DATE),"preorder_listing_%",0)) as preorder_percent
FROM shopee_bi_vn_preorder_listings_shop
WHERE grass_date >= DATE_TRUNC('week',CURRENT_DATE) - interval '30' day
AND grass_date <= DATE_TRUNC('week',CURRENT_DATE)
GROUP BY 1
)

, ps_week_ago AS
(
SELECT DISTINCT shop_id
FROM user_mart_dim_shop
WHERE grass_date = date_trunc('week',date_trunc('week',current_date) - interval '1' day)
AND is_preferred_shop = 1
)

, raw AS (
SELECT
      l.shop_id
    , u.user_name
    , n.group_cat
    , n.main_cat
    , l.l1_kpi_category AS multiple_fashion_cat
    , COALESCE(l.fashion_gross_ado, 0) AS multiple_fashion_cat_gross_ado
    , COALESCE(l.fashion_gross_ado_distinct,0) AS multiple_fashion_cat_distinct_ado
    , COALESCE(l.fashion_gross_daily_gmv, 0) AS multiple_fashion_cat_gross_daily_gmv
    , COALESCE(g.gross_ado, 0) AS platform_gross_ado
    , COALESCE(g.gross_daily_gmv, 0) AS platform_gross_daily_gmv
    , COALESCE(l.fashion_net_ado, 0) AS multiple_fashion_cat_net_ado
    , COALESCE(l.fashion_net_ado_distinct,0) AS multiple_fashion_cat_net_distinct_ado
    , COALESCE(l.fashion_net_daily_gmv, 0) AS multiple_fashion_cat_net_daily_gmv
    , COALESCE(net.net_ado, 0) AS platform_net_ado
    , COALESCE(net.net_daily_gmv, 0) AS platform_net_daily_gmv
    , COALESCE(l.skus, 0) AS total_selling_skus
    , COALESCE(l.qty, 0) AS quantity_sold
    , COALESCE(l.bd_voucher_direct, 0) AS bd_voucher_direct
    , COALESCE(l.bd_coin_direct, 0) AS bd_coin_direct
    , COALESCE(l.non_bd_voucher_direct, 0) AS non_bd_voucher_direct
    , COALESCE(l.non_bd_coin_direct, 0) AS non_bd_coin_direct
    , COALESCE(l.rebate, 0) AS rebate
    , u.is_official_shop AS is_mall
    , u.is_cb_shop AS is_cb
    , CASE
        WHEN u.status = 0 THEN 'Delete'
        WHEN u.status = 1 THEN 'Normal'
        WHEN u.status = 2 THEN 'Banned'
        WHEN u.status = 3 THEN 'Frozen'
        ELSE 'Null'
    END AS seller_status
    , CASE WHEN s.shop_id IS NOT NULL THEN s.seller_type
            WHEN s.shop_id IS NULL AND u.is_cb_shop = 1 THEN 'Cross-border'
            WHEN s.shop_id IS NULL AND u.is_official_shop = 1 THEN 'Mall'
            ELSE 'Long-tail' END AS seller_type
    , sub_cat AS main_subcat
    , nfr.nfr
    , lsr.lsr
    , p.penalty_point
    , pre.preorder_percent
    , CASE
		WHEN ps_week_ago.shop_id IS NULL AND u.is_preferred_shop = 0 THEN 'NO PS'
		WHEN ps_week_ago.shop_id IS NULL AND u.is_preferred_shop = 1 THEN 'NEW PS'
		WHEN ps_week_ago.shop_id IS NOT NULL AND u.is_preferred_shop = 0 THEN 'OFF PS'
		WHEN ps_week_ago.shop_id IS NOT NULL AND u.is_preferred_shop = 1 THEN 'CURRENT PS'
	  end as is_PS
FROM list l
LEFT JOIN new_cat n ON l.shop_id = n.shop_id
LEFT JOIN new_subcat r2 ON l.shop_id = r2.shop_id
LEFT JOIN seller_type s ON l.shop_id = s.shop_id
LEFT JOIN user_mart_dim_shop u ON l.shop_id = u.shop_id
LEFT JOIN penalty_table p ON l.shop_id = p.shop_id
LEFT JOIN nfr_table nfr ON l.shop_id = nfr.shop_id
LEFT JOIN lsr_table lsr ON l.shop_id = lsr.shop_id
LEFT JOIN preorder pre ON l.shop_id = pre.shopid
LEFT JOIN ps_week_ago ON ps_week_ago.shop_id = l.shop_id
LEFT JOIN gross g ON l.shop_id = g.shop_id
LEFT JOIN net ON l.shop_id = net.shop_id
WHERE u.grass_date = current_date - interval '1' day
    AND u.is_cb_shop IN (0)
        AND u.is_official_shop IN (0)
)

SELECT DISTINCT * FROM raw
WHERE shop_id IN (0) OR 0 IN (0)

function myFunction() {
  let sheetData = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName("Data")
    .getDataRange()
    .getValues();

  for (let i = 1; i < sheetData.length; i++) {
    Logger.log(sheetData[i][0]);
  }

  SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName("Data")
    .getDataRange()
    .setValues(sheetData);
}