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.

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);
}

.divWithSlightShadow {
  background: #FFFFFF;
    border-radius: 6px;
    border: 1px solid #EBEBEB; // <*** This is the key!
    width: 356px;
    box-shadow: 0 4px 8px 0 rgb(0 0 0 / 4%);
    padding: 24px;
}

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)

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            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)            
) 

<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>

#!/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

var limit = null; //Integer or null
wipeEmployeeIDHRProfile(); // Remove employee Number for 'resetting' profiles for full pull
updateEmployeeNumbersHRProfile(limit); // Set employee number - for coalesce change on transform maps

function updateEmployeeNumbersHRProfile(limit) {
	var count = 0;
	var gr = new GlideRecord('sys_user');
	if(limit) gr.setLimit(limit);
	gr.query();
	while (gr.next()) {
		var employeeID = gr.user_name.toString(); // 'ND23858';
		if(isValid(employeeID)) {
			var firstTwo = employeeID.substring(0,2);
			var lastChars = employeeID.substring(2);

			if(isAlphaOrParen(firstTwo) && isNumeric(lastChars)) {
				updateEmployeeNumberByUser(gr.sys_id, lastChars);
				count++;
			}
			//else gs.info('EMPID | Bad Employee ID: ' + employeeID);
		} //else gs.info('EMPID | Bad Employee ID (not valid): ' + employeeID);

	}
	gs.info('EMPID | Total Count: ' + count);
}

function updateEmployeeNumberByUser(user, employeeID) {
	var gr = new GlideRecord("sn_hr_core_profile");
	gr.addQuery("user", user);
	gr.query();
	if (gr.next()) {
		gr.employee_number = employeeID;
		gr.autoSysFields(false);
		gr.setWorkflow(false);
		gr.update();
	}
}

function wipeEmployeeIDHRProfile() {
	var count = 0;
	var gr = new GlideRecord("sn_hr_core_profile");
	gr.addEncodedQuery("employee_number!=NULL");
	gr.query();
	while (gr.next()) {
		gr.employee_number = null;
		gr.autoSysFields(false);
		gr.setWorkflow(false);
		gr.update();
		count++;
	}
	gs.info('EMPID | Wiped HR Profile Employee IDs: ' + count);
}

function isAlphaOrParen(str) {
	return /^[a-zA-Z()]+$/.test(str);
}

function isNumeric(str) {
	if (typeof str != "string") return false; // we only process strings!  
	return !isNaN(str) && !isNaN(parseFloat(str)); // ...and ensure strings of whitespace fail
}

function isValid(str){
	if(str.indexOf('-') != -1 || str.indexOf('.') != -1)
		return false;
	else return true;
}

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

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

​//Search a value with incorrect spaces.. 

var input = 'gu ara nteed rate';
var table = 'core_company';
var field = 'name';

var searchName = input.replace(/\s/g, '').toLowerCase();
var gr = new GlideRecord(table);
gr.addEncodedQuery(field + 'ISNOTEMPTY');
gr.query();
while (gr.next()) {
  	var fieldString = gr.getValue(field).replace(/\s/g, '').toLowerCase();
 
    if (fieldString.indexOf(searchName) != -1)
        gs.info('FOUND: "' + gr[field].getDisplayValue() + '"');
}
/*
Example:

2021-03-15T18:05:40.674Z: FOUND: "Guaranteed Rate Insurance"
2021-03-15T18:05:40.677Z: FOUND: "Guaranteed Rate, Inc2"
2021-03-15T18:05:40.678Z: FOUND: "Guaranteed Rate, Inc."
2021-03-15T18:05:40.680Z: FOUND: "Guaranteed Rate, Inc"
2021-03-15T18:05:40.682Z: FOUND: "Guaranteed Rate Affinity"

*/

new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne('first_name')
    .ifPresent(function (user) {
       gs.info(user.first_name);
   });
// Fred

// Find user if they exist, otherwise return user with first_name 'Nobody'
var user = new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne('first_name')
    .orElse({ first_name: 'Nobody' });

// Find user, otherwise throw an Error that the user couldn't be found
var user = new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne('first_name')
    .get(); // this method can throw if no record is found

// Does a given user exist? Assign boolean to userExists
var userExists = new GlideQuery('sys_user')
   .where('last_name', 'Luddy')
    .selectOne('first_name')
    .isPresent();