///<summary>Inserts one DisplayField into the database.  Provides option to use the existing priKey.</summary>
 internal static long Insert(DisplayField displayField,bool useExistingPK)
 {
     if(!useExistingPK && PrefC.RandomKeys) {
         displayField.DisplayFieldNum=ReplicationServers.GetKey("displayfield","DisplayFieldNum");
     }
     string command="INSERT INTO displayfield (";
     if(useExistingPK || PrefC.RandomKeys) {
         command+="DisplayFieldNum,";
     }
     command+="InternalName,ItemOrder,Description,ColumnWidth,Category,ChartViewNum) VALUES(";
     if(useExistingPK || PrefC.RandomKeys) {
         command+=POut.Long(displayField.DisplayFieldNum)+",";
     }
     command+=
          "'"+POut.String(displayField.InternalName)+"',"
         +    POut.Int   (displayField.ItemOrder)+","
         +"'"+POut.String(displayField.Description)+"',"
         +    POut.Int   (displayField.ColumnWidth)+","
         +    POut.Int   ((int)displayField.Category)+","
         +    POut.Long  (displayField.ChartViewNum)+")";
     if(useExistingPK || PrefC.RandomKeys) {
         Db.NonQ(command);
     }
     else {
         displayField.DisplayFieldNum=Db.NonQ(command,true);
     }
     return displayField.DisplayFieldNum;
 }

        /// <param name="catalog">The name of the database on which queries are being executed to.</param>
        public static IEnumerable<DisplayField> GetDisplayFields(string catalog)
        {
            List<DisplayField> displayFields = new List<DisplayField>();

            const string sql = "SELECT employee_type_id AS key, employee_type_code || ' (' || employee_type_name || ')' as value FROM hrm.employee_types;";
            using (NpgsqlCommand command = new NpgsqlCommand(sql))
            {
                using (DataTable table = DbOperation.GetDataTable(catalog, command))
                {
                    if (table?.Rows == null || table.Rows.Count == 0)
                    {
                        return displayFields;
                    }

                    foreach (DataRow row in table.Rows)
                    {
                        if (row != null)
                        {
                            DisplayField displayField = new DisplayField
                            {
                                Key = row["key"].ToString(),
                                Value = row["value"].ToString()
                            };

                            displayFields.Add(displayField);
                        }
                    }
                }
            }

            return displayFields;
        }

		///<summary>Converts a DataTable to a list of objects.</summary>
		public static List<DisplayField> TableToList(DataTable table){
			List<DisplayField> retVal=new List<DisplayField>();
			DisplayField displayField;
			for(int i=0;i<table.Rows.Count;i++) {
				displayField=new DisplayField();
				displayField.DisplayFieldNum= PIn.Long  (table.Rows[i]["DisplayFieldNum"].ToString());
				displayField.InternalName   = PIn.String(table.Rows[i]["InternalName"].ToString());
				displayField.ItemOrder      = PIn.Int   (table.Rows[i]["ItemOrder"].ToString());
				displayField.Description    = PIn.String(table.Rows[i]["Description"].ToString());
				displayField.ColumnWidth    = PIn.Int   (table.Rows[i]["ColumnWidth"].ToString());
				displayField.Category       = (OpenDentBusiness.DisplayFieldCategory)PIn.Int(table.Rows[i]["Category"].ToString());
				displayField.ChartViewNum   = PIn.Long  (table.Rows[i]["ChartViewNum"].ToString());
				retVal.Add(displayField);
			}
			return retVal;
		}

        /// <summary>
        /// Displayfields provide a minimal name/value context for data binding the row collection of hrm.leave_applications.
        /// </summary>
        /// <returns>Returns an enumerable name and value collection for the table hrm.leave_applications</returns>
        /// <exception cref="UnauthorizedException">Thown when the application user does not have sufficient privilege to perform this action.</exception>
        public IEnumerable<DisplayField> GetDisplayFields()
        {
            List<DisplayField> displayFields = new List<DisplayField>();

            if (string.IsNullOrWhiteSpace(this._Catalog))
            {
                return displayFields;
            }

            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    this.Validate(AccessTypeEnum.Read, this._LoginId, false);
                }
                if (!this.HasAccess)
                {
                    Log.Information("Access to get display field for entity \"LeaveApplication\" was denied to the user with Login ID {LoginId}", this._LoginId);
                    throw new UnauthorizedException("Access is denied.");
                }
            }

            const string sql = "SELECT leave_application_id AS key, leave_application_id as value FROM hrm.leave_applications;";
            using (NpgsqlCommand command = new NpgsqlCommand(sql))
            {
                using (DataTable table = DbOperation.GetDataTable(this._Catalog, command))
                {
                    if (table?.Rows == null || table.Rows.Count == 0)
                    {
                        return displayFields;
                    }

                    foreach (DataRow row in table.Rows)
                    {
                        if (row != null)
                        {
                            DisplayField displayField = new DisplayField
                            {
                                Key = row["key"].ToString(),
                                Value = row["value"].ToString()
                            };

                            displayFields.Add(displayField);
                        }
                    }
                }
            }

            return displayFields;
        }

		///<summary>Updates one DisplayField in the database.  Uses an old object to compare to, and only alters changed fields.  This prevents collisions and concurrency problems in heavily used tables.  Returns true if an update occurred.</summary>
		public static bool Update(DisplayField displayField,DisplayField oldDisplayField){
			string command="";
			if(displayField.InternalName != oldDisplayField.InternalName) {
				if(command!=""){ command+=",";}
				command+="InternalName = '"+POut.String(displayField.InternalName)+"'";
			}
			if(displayField.ItemOrder != oldDisplayField.ItemOrder) {
				if(command!=""){ command+=",";}
				command+="ItemOrder = "+POut.Int(displayField.ItemOrder)+"";
			}
			if(displayField.Description != oldDisplayField.Description) {
				if(command!=""){ command+=",";}
				command+="Description = '"+POut.String(displayField.Description)+"'";
			}
			if(displayField.ColumnWidth != oldDisplayField.ColumnWidth) {
				if(command!=""){ command+=",";}
				command+="ColumnWidth = "+POut.Int(displayField.ColumnWidth)+"";
			}
			if(displayField.Category != oldDisplayField.Category) {
				if(command!=""){ command+=",";}
				command+="Category = "+POut.Int   ((int)displayField.Category)+"";
			}
			if(displayField.ChartViewNum != oldDisplayField.ChartViewNum) {
				if(command!=""){ command+=",";}
				command+="ChartViewNum = "+POut.Long(displayField.ChartViewNum)+"";
			}
			if(command==""){
				return false;
			}
			command="UPDATE displayfield SET "+command
				+" WHERE DisplayFieldNum = "+POut.Long(displayField.DisplayFieldNum);
			Db.NonQ(command);
			return true;
		}

        /// <summary>
        /// Parses the existing fields.
        /// </summary>
        /// <param name="doc">The document.</param>
        /// <returns>IEnumerable{DisplayField}.</returns>
        private static IEnumerable<DisplayField> ParseExistingFields(XElement doc)
        {
            var result = new List<DisplayField>();

            foreach (var element in doc.Elements("ExistingRecordsFields").Elements())
            {
                var displayField =
                    new DisplayField(
                        element.With(e => e.Attribute("FieldName")).Return(a => a.Value, string.Empty),
                        element.With(e => e.Attribute("FullPath")).Return(a => a.Value, string.Empty),
                        SafeTypeConverter.Convert<double>(element.With(e => e.Attribute("Order")).Return(a => a.Value, "0")),
                        SafeTypeConverter.Convert<bool>(element.With(e => e.Attribute("IsBuiltIn")).Return(a => a.Value, "false"))
                    );

                result.Add(displayField);
            }

            return result;
        }