SQL Snippets: Series Index

Microsoft SQL ServerThis post is the series index for the series on SQL Snippets.

This series will build up through time as I post snippets of SQL Script; I’ll also go back and update some of the previously posted SQL snippets to bring them together into one index.

SQL Snippets
Manage Data Containing an Apostrophe
Create and Use Database Role to Restrict Access
SELECT INTO
Select Primary Keys for All Tables in Database
Select All Fields for All Tables In Database

Validate and Insert/Update Vendor Emails in Microsoft Dynamics GP from a Text File

Microsoft Dynamics GPI’ve previously posted a script which could be used to update emails in Microsoft Dynamics GP from a tab delimited text file. I’ve recently been working on a project with a client and created a variation on that script which works only for vendor emails, but validates the data before inserting or updating to ensure that the data is valid.

The original script took it on trust that the data was correct, but this tie we had some concerns about the quality of data being provided so I added validation to ensure the vendor and vendor address both existed before any data was inserted or updated; if there were any errors found, the errors were presented to the user to fix and then rerun the script.

The highlighted section is the path to the text file being uploaded; regardless of where you are running SSMS, this path and file must exist on the SQL Server itself.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
/* CREATE TEMP ERROR TABLE */ CREATE TABLE #Errors ( Error VARCHAR(1000) ,ROW_ID INT IDENTITY ) GO /* CREATE TEMP TABLE FOR DATA */ CREATE TABLE #SY01200_IMPORT ( VENDORID VARCHAR(100) ,ADRSCODE VARCHAR(100) ,EmailToAddress VARCHAR(1000) ,EmailCcAddress VARCHAR(1000) ,EmailBccAddress VARCHAR(1000) ) GO /* BULK INSERT */ BULK INSERT #SY01200_IMPORT FROM 'c:\temp\email.txt' WITH ( FIELDTERMINATOR = '\t' ,ROWTERMINATOR = '\n' ,FIRSTROW = 2 ) GO /* VALIDATE DATA */ -- validate creditors INSERT INTO #Errors (Error) ( SELECT DISTINCT 'Vendor does not exist: ' + CAST(SYI.VENDORID AS VARCHAR(100)) FROM #SY01200_IMPORT AS SYI LEFT JOIN PM00200 AS ['PM Creditor Master File'] ON ['PM Creditor Master File'].VENDORID = SYI.VENDORID WHERE ['PM Creditor Master File'].VENDORID IS NULL ) GO --validate creditor addresses INSERT INTO #Errors (Error) ( SELECT DISTINCT 'Vendor address does not exist: ' + CAST(SYI.VENDORID AS VARCHAR(100)) + ' / ' + CAST(SYI.ADRSCODE AS VARCHAR(100)) FROM #SY01200_IMPORT AS SYI LEFT JOIN PM00300 AS ['PM Creditor Addresses'] ON ['PM Creditor Addresses'].VENDORID = SYI.VENDORID AND ['PM Creditor Addresses'].ADRSCODE = SYI.ADRSCODE WHERE ['PM Creditor Addresses'].VENDORID IS NULL ) GO /* UPDATE EXISTING DATA IF NO ERRORS */ IF (SELECT COUNT(*) FROM #Errors) = 0 UPDATE SY SET SY.EmailToAddress = ISNULL(SY_I.EmailToAddress, '') ,SY.EmailCcAddress = ISNULL(SY_I.EmailCcAddress, '') ,SY.EmailBccAddress = ISNULL(SY_I.EmailBccAddress, '') FROM SY01200 SY INNER JOIN #SY01200_IMPORT AS SY_I ON SY_I.VENDORID = SY.Master_ID AND SY.Master_Type = 'VEN' AND SY.ADRSCODE = SY_I.ADRSCODE GO /* INSERT NEW DATA IF NO ERRORS */ IF (SELECT COUNT(*) FROM #Errors) = 0 INSERT INTO SY01200 ( Master_Type ,Master_ID ,ADRSCODE ,EmailToAddress ,EmailCcAddress ,EmailBccAddress ,INETINFO ) ( SELECT 'VEN' ,VENDORID ,ADRSCODE ,ISNULL(EmailToAddress, '') ,ISNULL(EmailCcAddress, '') ,ISNULL(EmailBccAddress, '') ,'' FROM #SY01200_IMPORT WHERE (SELECT COUNT(Master_ID) FROM SY01200 WHERE Master_Type = 'VEN' AND Master_ID = #SY01200_IMPORT.VENDORID AND ADRSCODE = #SY01200_IMPORT.ADRSCODE) = 0 ) GO /* OUTPUT ERRORS */ IF (SELECT COUNT(*) FROM #Errors) > 0 SELECT Error FROM #Errors ORDER BY ROW_ID GO /* DROP TEMP TABLES */ DROP TABLE #SY01200_IMPORT GO DROP TABLE #Errors GO

Upcoming Microsoft Dynamics GP Webinars from ISC Software

ISC Software SolutionsEvery month at ISC Software I present a webinar on Microsoft Dynamics GP and related products. We typically have the next three upcoming monthly webinars I’ll be delivering scheduled.

We run these webinars on a monthly basis, with occasional extra webinars added to the schedule so it is worth checking the Webinar Schedule page every so often.

The upcoming webinars are:

Lesser Used Modules in Dynamics GP
In July is Lesser Used Modules in Dynamics GP; Explore some of the lesser used modules of Microsoft Dynamics GP and how they can improve processes.

Tue, July 20th, 2021 4:00 PM – 4:45 PM BST

This webinar will look at the modules included in the Microsoft Dynamics GP Starter and Extended Packs, picking out some of the modules which can save time but which aren’t as commonly used.

Register Here

Microsoft Dynamics GP Workflow
In August is Microsoft Dynamics GP Workflow; Discover how workflow can be used to require documents and master record changes to be approved, and enforce segregation of duties.

Tue, August 17th, 2021 4:00 PM – 4:45 PM BST

This webinar will look at the workflow approval types available in Dynamics GP and how they can be used to require documents or master record changes, or document submission to be approved. It will then delve further into the types of steps and routings available within workflow.

Register Here

Getting More From Dynamics GP
In September is Getting More From Dynamics GP; Learn how to get more out of Microsoft Dynamics GP.

Tue, September 14th, 2021 4:00 PM – 4:45 PM BST

Watch while an experienced consultant shows you some tips and tricks to improve your use of Dynamics GP using standard functions and features available now.

Register Here

ClassicPress Plugin Development: Create Submenu for a Custom Post Type

ClassicPress PluginsThis post is part of the ClassicPress Plugin Development series in which I am going to look at both best practice for developing plugins and how I approach some requirements as well as some of the functions I commonly use.

If you’ve created a plugin with a custom post type you can add additional submenus to the custom post types top level menu. I typically use this to add the options page of the plugin. This is basically done as a variation on the theme of adding a submenu to custom top level menu.

This is an example from my From Twitter plugin which has a custom post type called Tweet; the key difference between this and adding a submenu to the Settings menu is the $tag which is the first argument.

/**
 * Add to menu.
 *
 * @since 1.0.0
 *
 */
function azrcrv_ft_create_admin_menu(){
	
	add_submenu_page(
						'edit.php?post_type=tweet'
						,esc_html__('From Twitter Settings', 'from-twitter')
						,esc_html__('Settings', 'from-twitter')
						,'manage_options'
						,'azrcrv-ft'
						,'azrcrv_ft_display_options'
					);
	
}

You can find the tag to use by hovering the mouse over the top level menu of the custom post type.

Click to show/hide the ClassicPress Plugin Development Series Index

ClassicPress Plugin Development: Create Custom Post Type

ClassicPress PluginsThis post is part of the ClassicPress Plugin Development series in which I am going to look at both best practice for developing plugins and how I approach some requirements as well as some of the functions I commonly use.

ClassicPress is intended to be extended and customised and has numerous ways this can be done. Shortcodes are a common way of doing this, but another way is to create a custom post type.

A post type in ClassicPress is a type of content. A single item is called a post, but this is also the name of a standard post type called posts which are used in the blog part of ClassicPress.

The default post types which ship with ClassicPress are:

  • Posts
  • Pages
  • Attachments
  • Revisions
  • Navigation Menus
  • Custom CSS
  • Changesets

You would a custom post type for an additional type of content which does not fit within the existing post types. For example, if you wanted to create a archive of tweets from Twitter, as I did with my From Twitter plugin, create a Tweet custom post type.

Post types can support any number of built-in core features such as meta boxes, custom fields, post thumbnails, post statuses, comments, and more. See the $supports argument, below, for a complete list of supported features.

Custom post types are created usig the register_post_type function:

register_post_type(string $post_type, array|string $args = array())

Parameters

$post_type (string) (Required) Post type key. Must not exceed 20 characters and may only contain lowercase alphanumeric characters, dashes, and underscores. See sanitize_key(). $args (array|string) (Optional) Array or string of arguments for registering a post type.
  • 'label' (string) Name of the post type shown in the menu. Usually plural. Default is value of $labels['name'].
  • 'labels' (string[]) An array of labels for this post type. If not set, post labels are inherited for non-hierarchical types and page labels for hierarchical ones. See get_post_type_labels() for a full list of supported labels.
  • 'description' (string) A short descriptive summary of what the post type is.
  • 'public' (bool) Whether a post type is intended for use publicly either via the admin interface or by front-end users. While the default settings of $exclude_from_search, $publicly_queryable, $show_ui, and $show_in_nav_menus are inherited from public, each does not rely on this relationship and controls a very specific intention. Default false.
  • 'hierarchical' (bool) Whether the post type is hierarchical (e.g. page). Default false.
  • 'exclude_from_search' (bool) Whether to exclude posts with this post type from front end search results. Default is the opposite value of $public.
  • 'publicly_queryable' (bool) Whether queries can be performed on the front end for the post type as part of parse_request(). Endpoints would include:
    • ?post_type={post_type_key}
    • ?{post_type_key}={single_post_slug}
    • ?{post_type_query_var}={single_post_slug}
    If not set, the default is inherited from $public.
  • 'show_ui' (bool) Whether to generate and allow a UI for managing this post type in the admin. Default is value of $public.
  • 'show_in_menu' (bool|string) Where to show the post type in the admin menu. To work, $show_ui must be true. If true, the post type is shown in its own top level menu. If false, no menu is shown. If a string of an existing top level menu (eg. 'tools.php' or 'edit.php?post_type=page'), the post type will be placed as a sub-menu of that. Default is value of $show_ui.
  • 'show_in_nav_menus' (bool) Makes this post type available for selection in navigation menus. Default is value of $public.
  • 'show_in_admin_bar' (bool) Makes this post type available via the admin bar. Default is value of $show_in_menu.
  • 'show_in_rest' (bool) Whether to include the post type in the REST API. Set this to true for the post type to be available in the block editor.
  • 'rest_base' (string) To change the base url of REST API route. Default is $post_type.
  • 'rest_controller_class' (string) REST API Controller class name. Default is 'WP_REST_Posts_Controller'.
  • 'menu_position' (int) The position in the menu order the post type should appear. To work, $show_in_menu must be true. Default null (at the bottom).
  • 'menu_icon' (string) The url to the icon to be used for this menu. Pass a base64-encoded SVG using a data URI, which will be colored to match the color scheme -- this should begin with 'data:image/svg+xml;base64,'. Pass the name of a Dashicons helper class to use a font icon, e.g. 'dashicons-chart-pie'. Pass 'none' to leave div.wp-menu-image empty so an icon can be added via CSS. Defaults to use the posts icon.
  • 'capability_type' (string) The string to use to build the read, edit, and delete capabilities. May be passed as an array to allow for alternative plurals when using this argument as a base to construct the capabilities, e.g. array('story', 'stories'). Default 'post'.
  • 'capabilities' (string[]) Array of capabilities for this post type. $capability_type is used as a base to construct capabilities by default. See get_post_type_capabilities().
  • 'map_meta_cap' (bool) Whether to use the internal default meta capability handling. Default false.
  • 'supports' (array) Core feature(s) the post type supports. Serves as an alias for calling add_post_type_support() directly. Core features include 'title', 'editor', 'comments', 'revisions', 'trackbacks', 'author', 'excerpt', 'page-attributes', 'thumbnail', 'custom-fields', and 'post-formats'. Additionally, the 'revisions' feature dictates whether the post type will store revisions, and the 'comments' feature dictates whether the comments count will show on the edit screen. A feature can also be specified as an array of arguments to provide additional information about supporting that feature. Example: array('my_feature', array('field' => 'value')). Default is an array containing 'title' and 'editor'.
  • 'register_meta_box_cb' (callable) Provide a callback function that sets up the meta boxes for the edit form. Do remove_meta_box() and add_meta_box() calls in the callback. Default null.
  • 'taxonomies' (string[]) An array of taxonomy identifiers that will be registered for the post type. Taxonomies can be registered later with register_taxonomy() or register_taxonomy_for_object_type().
  • 'has_archive' (bool|string) Whether there should be post type archives, or if a string, the archive slug to use. Will generate the proper rewrite rules if $rewrite is enabled. Default false.
  • 'rewrite' (bool|array) Triggers the handling of rewrites for this post type. To prevent rewrite, set to false. Defaults to true, using $post_type as slug. To specify rewrite rules, an array can be passed with any of these keys:
    • 'slug' (string) Customize the permastruct slug. Defaults to $post_type key.
    • 'with_front' (bool) Whether the permastruct should be prepended with WP_Rewrite::$front. Default true.
    • 'feeds' (bool) Whether the feed permastruct should be built for this post type. Default is value of $has_archive.
    • 'pages' (bool) Whether the permastruct should provide for pagination. Default true.
    • 'ep_mask' (int) Endpoint mask to assign. If not specified and permalink_epmask is set, inherits from $permalink_epmask. If not specified and permalink_epmask is not set, defaults to EP_PERMALINK.
  • 'query_var' (string|bool) Sets the query_var key for this post type. Defaults to $post_type key. If false, a post type cannot be loaded at ?{query_var}={post_slug}. If specified as a string, the query ?{query_var_string}={post_slug} will be valid.
  • 'can_export' (bool) Whether to allow this post type to be exported. Default true.
  • 'delete_with_user' (bool) Whether to delete posts of this type when deleting a user.
    • If true, posts of this type belonging to the user will be moved to Trash when the user is deleted.
    • If false, posts of this type belonging to the user will *not* be trashed or deleted.
    • If not set (the default), posts are trashed if post type supports the 'author' feature. Otherwise posts are not trashed or deleted. Default null.
  • 'template' (array) Array of blocks to use as the default initial state for an editor session. Each item should be an array containing block name and optional attributes.
  • 'template_lock' (string|false) Whether the block template should be locked if $template is set.
    • If set to 'all', the user is unable to insert new blocks, move existing blocks and delete blocks.
    • If set to 'insert', the user is able to move existing blocks but is unable to insert new blocks and delete blocks. Default false.
  • '_builtin' (bool) FOR INTERNAL USE ONLY! True if this post type is a native or "built-in" post_type. Default false.
  • '_edit_link' (string) FOR INTERNAL USE ONLY! URL segment to use for edit link of this post type. Default 'post.php?post=%d'. Default value: array()

Return

(WP_Post_Type|WP_Error) The registered post type object on success, WP_Error object on failure.

Below is an example of the registration of a custom post type in my Call-out Boxes plugin; this custom post type is not public as it only does output within the plugins shortcode and does not allow for searching:

add_action('init', 'azrcrv_cob_create_custom_post_type');

/**
 * Create custom snippet post type.
 *
 * @since 1.0.0
 *
 */
function azrcrv_cob_create_custom_post_type(){
	register_post_type('call-out-box',
		array(
				'labels' => array(
									'name' => __('Templates', 'call-out-boxes'),
									'singular_name' => __('Template', 'call-out-boxes'),
									'menu_name' => __( 'Call-out Boxes', 'call-out-boxes' ),
									'name_admin_bar' => __( 'Call-out Box Template', 'call-out-boxes' ),
									'all_items' => __('All Templates', 'call-out-boxes'),
									'add_new' => __('Add New Template', 'call-out-boxes'),
									'add_new_item' => __('Add New Call-out Box Template', 'call-out-boxes'),
									'edit' => __('Edit Template', 'call-out-boxes'),
									'edit_item' => __('Edit Call-out Box Template', 'call-out-boxes'),
									'new_item' => __('New Call-out Box Template', 'call-out-boxes'),
									'view' => __('View Template', 'call-out-boxes'),
									'view_item' => __('View Call-out Box Template', 'call-out-boxes'),
									'search_items' => __('Search Call-out Box Templates', 'call-out-boxes'),
									'not_found' => __('No Call-out Box Templates found', 'call-out-boxes'),
									'not_found_in_trash' => __('No Call-out Box Templates found in Trash', 'call-out-boxes'),
									'parent' => __('Parent Call-out Box', 'call-out-boxes')
								),
			'public' => false,
			'exclude_from_search' => true,
			'publicly_queryable' => false,
			'menu_position' => 50,
			'supports' => array('title'),
			'taxonomies' => array(''),
			'menu_icon' => 'dashicons-testimonial',
			'has_archive' => false,
			'show_ui' => true,
			'show_in_menu' => true,
			'show_in_admin_bar' => true,
			'show_in_nav_menus' => false,
			'show_in_rest' => false,
		)
	);
}

Click to show/hide the ClassicPress Plugin Development Series Index