Compound Menu/Category

V5 Request
9

I manage to put a script together to Clone a menu :grinning:

Its probably much more cluttered than needed as couldn’t work out a more streamlined SQL qry but it works.

This script takes the category and its menu names you want to clone from and the menu name you want to clone to.

USE WITH CAUTION - THIS MAKES CHANGES DIRECTLY TO THE DATABASE - ALWAYS BACKUP FIRST

function cloneCategoryToMenu(categoryName,fromMenuName,toMenuName) {
	if (fromMenuName == toMenuName) {
		var response					= 'Category can only be cloned to diferent menu with this function'; 
		} else {
		var fromMenuId					= menuNameId(fromMenuName);
		var fromCategoryId				= categoryNameId(categoryName,fromMenuId);
		var toMenuId					= menuNameId(toMenuName);
		var toCategoryExists			= checkCategoryNameExists(categoryName,toMenuId);
		if (toCategoryExists > 0) {
			var toCategoryId			= categoryNameId(categoryName,toMenuId);
			delectCategoryItems(toCategoryId)
			delectCategory(toCategoryId)
			}
		var fromCategoryValues			= categoryDetailsList(fromCategoryId);
		insertCategoryRow(fromCategoryValues,toMenuId)
		var toCategoryId				= categoryNameId(categoryName,toMenuId);
		var fromCategoryItemCount		= categoryItemsCount(fromCategoryId);
		var fromCategoryItemIdList		= categoryItemsIdList(fromCategoryId)
		for (r = 0; r < fromCategoryItemCount ; r++) {
			var loopItemId				= fromCategoryItemIdList[r];
			var loopItemRowValues		= categoryItemRowList(loopItemId);
			var loopInsert				= insertItemRow(loopItemRowValues,toCategoryId)
			}
		var response					= 'Category: '+categoryName+' [ID:'+fromCategoryId+'] has been cloned from Menu: '+fromMenuName+' [ID: '+fromMenuId+'] to Menu: '+toMenuName+' [ID: '+toMenuId+'] as Category: '+categoryName+' [ID: '+toCategoryId+']';
		}
	return response
}

function menuNameId(inputMenuName) {
	var qry = "SELECT [Id] FROM [ScreenMenus] WHERE [Name] = '"+inputMenuName+"'";
	var menuId = sql.Query(qry).First;
	return menuId
}

function categoryNameId(inputCategoryName,inputMenuId) {
	var qry = "SELECT [Id] FROM [ScreenMenuCategories] WHERE [Name] = '"+inputCategoryName+"' AND [ScreenMenuId] = '"+inputMenuId+"'";
	var categoryId = sql.Query(qry).First;
	return categoryId
}

function checkCategoryNameExists(inputCategoryName,inputMenuId) {
	var qry = "SELECT COUNT([Id]) AS CT FROM [ScreenMenuCategories] WHERE [Name] = '"+inputCategoryName+"' AND [ScreenMenuId] = '"+inputMenuId+"'";
	var categoryCount = sql.Query(qry).First;
	return categoryCount
}

function categoryItemsCount(inputCategoryId) {
	var qry = "SELECT COUNT([Id]) AS CT FROM [ScreenMenuItems] WHERE [ScreenMenuCategoryId] = '"+inputCategoryId+"'";
	var categoryItemCount = sql.Query(qry).First;
	return categoryItemCount
}

function categoryItemsIdList(inputCategoryId) {
	var qry = "SELECT [Id] FROM [ScreenMenuItems] WHERE [ScreenMenuCategoryId] = '"+inputCategoryId+"'";
	var categoryItemsIdList = sql.Query(qry).Delimit('~').All;
	return categoryItemsIdList
}

function categoryItemRowList(categoryItemId) {
	var qry = "SELECT [Name],[Header],[Appearance],[MenuItemId],[SortOrder],[AutoSelect],[ButtonColor],[Quantity],[ImagePath],[FontSize],[SubMenuTag],[ItemPortion],[OrderTags],[OrderStates],[AutomationCommand],[AutomationCommandValue] ";
	qry += "FROM [ScreenMenuItems] WHERE [Id] = '"+categoryItemId+"'";
	var categoryItemRow = sql.Query(qry).Delimit('~').First;
	var rowarray	= categoryItemRow.split('~');
	var rowcount	= rowarray.length;
	var rowValuesList	= '';
	for (i = 0; i < rowcount ; i++) {
		if (!rowarray[i] && i != 10) {
			rowValuesList += "NULL,";
			} else {
			rowValuesList += "'"+rowarray[i]+"',";
			}
		}
	var responce = rowValuesList.slice(0,-1)
	return responce
}

function insertItemRow(inputValues,inputScreenCategoryId) {
	var qry = "INSERT INTO [ScreenMenuItems]";
	qry += "([Name],[Header],[Appearance],[MenuItemId],[SortOrder],[AutoSelect],[ButtonColor],[Quantity],[ImagePath],[FontSize],[SubMenuTag],[ItemPortion],[OrderTags],[OrderStates],[AutomationCommand],[AutomationCommandValue],[ScreenMenuCategoryId])";
	qry += "VALUES ("+inputValues+",'"+inputScreenCategoryId+"')";
	sql.ExecSql(qry)
}

function insertCategoryRow(inputValues,inputScreenMenuId) {
	var qry = "INSERT INTO [ScreenMenuCategories]";
	qry += "([Name],[Header],[Appearance],[SortOrder],[MostUsedItemsCategory],[ColumnCount],[MenuItemButtonHeight],[MenuItemButtonColor],[MenuItemFontSize],[WrapText],[PageCount],[SortAlphabetically],[MainButtonHeight],[MainButtonColor],[MainFontSize],[SubButtonHeight],[SubButtonRows],[SubButtonColorDef],[NumeratorType],[NumeratorValues],[AlphaButtonValues],[ImagePath],[NumberPadPercent],[MaxItems],[ScreenMenuId])";
	qry += "VALUES ("+inputValues+",'"+inputScreenMenuId+"')";
	sql.ExecSql(qry)
}

function delectCategoryItems(inputScreenCategory) {
	var qry = "DELETE FROM [ScreenMenuItems] WHERE [ScreenMenuCategoryId] = '"+inputScreenCategory+"'";
	sql.ExecSql(qry)
}

function delectCategory(inputScreenCategoryId) {
	var qry = "DELETE FROM [ScreenMenuCategories] WHERE [Id] = '"+inputScreenCategoryId+"'";
	sql.ExecSql(qry)
}

function categoryDetailsList(categoryId) {
	var qry = "SELECT [Name],[Header],[Appearance],[SortOrder],[MostUsedItemsCategory],[ColumnCount],[MenuItemButtonHeight],[MenuItemButtonColor],[MenuItemFontSize],[WrapText],[PageCount],[SortAlphabetically],[MainButtonHeight],[MainButtonColor],[MainFontSize],[SubButtonHeight],[SubButtonRows],[SubButtonColorDef],[NumeratorType],[NumeratorValues],[AlphaButtonValues],[ImagePath],[NumberPadPercent],[MaxItems] ";
	qry += "FROM [ScreenMenuCategories] WHERE [Id] = '"+categoryId+"'";
	var categoryItemRow = sql.Query(qry).Delimit('~').First;
	var rowarray	= categoryItemRow.split('~');
	var rowcount	= rowarray.length;
	var rowValuesList	= '';
	for (i = 0; i < rowcount ; i++) {
		if (!rowarray[i] && i != 17) {
			rowValuesList += "NULL,";
			} else {
			rowValuesList += "'"+rowarray[i]+"',";
			}
		}
	var responce = rowValuesList.slice(0,-1)
	return responce
}
1 Like