sqlsugar/Src/Asp.Net/SqlSugar/Realization/PostgreSQL/SqlBuilder/PostgreSQLInsertBuilder.cs

using System;
using System.Linq;
using System.Text;

namespace SqlSugar
{
    public class PostgreSQLInsertBuilder : InsertBuilder
    {
        public override string SqlTemplate
        {
            get
            {
                if (IsReturnIdentity)
                {
                    return @"INSERT INTO {0} 
           ({1})
     VALUES
           ({2}) returning $PrimaryKey";
                }
                else
                {
                    return @"INSERT INTO {0} 
           ({1})
     VALUES
           ({2}) ;";

                }
            }
        }
        public override string SqlTemplateBatch => "INSERT INTO {0} ({1})";
        public override string SqlTemplateBatchUnion => " VALUES ";

        public override string SqlTemplateBatchSelect => " {0} ";

        public override Func<string, string, string> ConvertInsertReturnIdFunc { get; set; } = (name, sql) =>
        {
            return sql.Trim().TrimEnd(';')+ $"returning {name} ";
        };
        public override string ToSqlString()
        {
            if (IsNoInsertNull)
            {
                DbColumnInfoList = DbColumnInfoList.Where(it => it.Value != null).ToList();
            }
            var groupList = DbColumnInfoList.GroupBy(it => it.TableId).ToList();
            var isSingle = groupList.Count() == 1;
            string columnsString = string.Join(",", groupList.First().Select(it => Builder.GetTranslationColumnName(it.DbColumnName)));
            if (isSingle)
            {
                string columnParametersString = string.Join(",", this.DbColumnInfoList.Select(it =>base.GetDbColumn(it, Builder.SqlParameterKeyWord + it.DbColumnName)));
                ActionMinDate();
                return GetIgnoreSql(string.Format(SqlTemplate, GetTableNameString, columnsString, columnParametersString));
            }
            else
            {
                StringBuilder batchInsetrSql = new StringBuilder();
                int pageSize = 200;
                int pageIndex = 1;
                if (IsNoPage&&IsReturnPkList) 
                {
                    pageSize = groupList.Count;
                }
                int totalRecord = groupList.Count;
                int pageCount = (totalRecord + pageSize - 1) / pageSize;
                while (pageCount >= pageIndex)
                {
                    batchInsetrSql.AppendFormat(SqlTemplateBatch, GetTableNameString, columnsString);
                    int i = 0;
                    foreach (var columns in groupList.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList())
                    {
                        var isFirst = i == 0;
                        if (isFirst)
                        {
                            batchInsetrSql.Append(SqlTemplateBatchUnion);
                        }
                        batchInsetrSql.Append("\r\n ( " + string.Join(",", columns.Select(it =>
                        {
                            if (it.InsertServerTime || it.InsertSql.HasValue()||it.SqlParameterDbType is Type|| it?.PropertyType?.Name=="DateOnly" || it?.PropertyType?.Name == "TimeOnly")
                            {
                                return GetDbColumn(it, null);
                            }
                            object value = null;
                            if (it.Value is DateTime)
                            {
                                var date = ((DateTime)it.Value);
                                value = date.ToString("O");
                                if (date==DateTime.MaxValue) 
                                {
                                    value = "9999-12-31T23:59:59.999999";
                                }
                            }
                            else if (it.Value  is DateTimeOffset)
                            {
                                return FormatDateTimeOffset(it.Value);
                            }
                            else if (it.IsArray&&it.Value!=null) 
                            {
                                return FormatValue(it.Value,it.PropertyName,i,it);
                            }
                            else if (it.Value is byte[])
                            {
                                return FormatValue(it.Value, it.PropertyName, i, it);
                            }
                            else
                            {
                                value = it.Value;
                            }
                            if (value == null||value==DBNull.Value)
                            {
                                return string.Format(SqlTemplateBatchSelect, "NULL");
                            }
                            return string.Format(SqlTemplateBatchSelect, "'" + value.ObjToStringNoTrim().ToSqlFilter() + "'");
                        })) + "),");
                        ++i;
                    }
                    pageIndex++;
                    batchInsetrSql.Remove(batchInsetrSql.Length - 1,1).Append("\r\n;\r\n");
                }
                return GetIgnoreSql(batchInsetrSql.ToString());
            }
        }

        public object FormatValue(object value, string name, int i, DbColumnInfo columnInfo)
        {
            if (value == null)
            {
                return "NULL";
            }
            else
            {
                var type = value.GetType();
                if (type == UtilConstants.ByteArrayType||type == UtilConstants.DateType || columnInfo.IsArray || columnInfo.IsJson)
                {
                    var parameterName = this.Builder.SqlParameterKeyWord + name+"_" + i;
                    var paramter = new SugarParameter(parameterName, value);
                    if (columnInfo.IsJson)
                    {
                        paramter.IsJson = true;
                    }
                    if (columnInfo.IsArray)
                    {
                        paramter.IsArray = true;
                    }
                    this.Parameters.Add(paramter);
                    return parameterName;
                }
                else if (type == UtilConstants.ByteArrayType)
                {
                    string bytesString = "0x" + BitConverter.ToString((byte[])value);
                    return bytesString;
                }
                else if (type.IsEnum())
                {
                    if (this.Context.CurrentConnectionConfig.MoreSettings?.TableEnumIsString == true)
                    {
                        return value.ToSqlValue();
                    }
                    else
                    {
                        return Convert.ToInt64(value);
                    }
                }
                else if (type == UtilConstants.DateTimeOffsetType)
                {
                    return FormatDateTimeOffset(value);
                }
                else if (type == UtilConstants.BoolType)
                {
                    return value.ObjToBool() ? "1" : "0";
                }
                else if (type == UtilConstants.StringType || type == UtilConstants.ObjType)
                {
                    return "'" + value.ToString().ToSqlFilter() + "'";
                }
                else
                {
                    return "'" + value.ToString() + "'";
                }
            }
        }
        public override object FormatValue(object value)
        {
            var N = string.Empty; 
            if (value == null)
            {
                return "NULL";
            }
            else
            {
                var type = UtilMethods.GetUnderType(value.GetType());
                if (type == UtilConstants.DateType)
                {
                    var date = value.ObjToDate();
                    if (date < UtilMethods.GetMinDate(this.Context.CurrentConnectionConfig))
                    {
                        date = UtilMethods.GetMinDate(this.Context.CurrentConnectionConfig);
                    }
                    return "'" + date.ToString("yyyy-MM-dd HH:mm:ss.fff") + "'";
                }
                else if (type == UtilConstants.ByteArrayType)
                {
                    string bytesString = "0x" + BitConverter.ToString((byte[])value).Replace("-", "");
                    return bytesString;
                }
                else if (type.IsEnum())
                {
                    if (this.Context.CurrentConnectionConfig.MoreSettings?.TableEnumIsString == true)
                    {
                        return value.ToSqlValue();
                    }
                    else
                    {
                        return Convert.ToInt64(value);
                    }
                }
                else if (type == UtilConstants.BoolType)
                {
                    return value.ObjToBool() ? "1" : "0";
                }
                else if (type == UtilConstants.StringType || type == UtilConstants.ObjType)
                {
                    return N + "'" + value.ToString().ToSqlFilter() + "'";
                }
                else if (type == UtilConstants.DateTimeOffsetType)
                {
                    return FormatDateTimeOffset(value);
                }
                else if (type == UtilConstants.FloatType)
                {
                    return N + "'" + Convert.ToDouble(value).ToString() + "'";
                }
                else
                {
                    return N + "'" + value.ToString() + "'";
                }
            }
        }
        public override string FormatDateTimeOffset(object value)
        {
            return "'" + ((DateTimeOffset)value).ToString("o") + "'";
        }
         
        private string GetIgnoreSql(string sql)
        {
            if (this.ConflictNothing?.Any() == true)
            {
                sql = sql.Replace(";", $"  ON CONFLICT ({string.Join(",", this.ConflictNothing)}) DO NOTHING;");
            }

            return sql;
        }
    }
}