public ImageRecord(DbString dbstring)
{
string[] parts = dbstring.Split(_DELIM);
HasBeenUsed = Boolean.Parse(parts[0]);
Name = parts[1];
Url = parts[2];
}
private static DbString AsDbString([CanBeNull] this string value, int maxLength, [CanBeNull] string defaultValue = "", bool?isAnsi = null, bool?isFixedLength = null)
{
var s = new DbString {
Length = maxLength, Value = value.ToMaxLength(maxLength) ?? defaultValue
};
if (isAnsi.HasValue)
{
s.IsAnsi = isAnsi.Value;
}
if (isFixedLength.HasValue)
{
s.IsFixedLength = isFixedLength.Value;
}
return(s);
}
public override string BuildLimits(string query, int limit, int offset)
{
if (offset == 0)
{
return(query.Insert(6, string.Concat(" TOP ", limit)));
}
int index = DbString.IndexOf(query, "ORDER BY");
string select = (index >= 0) ? query.Substring(0, index) : query;
string fields = select.Substring(7, select.IndexOf("FROM", StringComparison.OrdinalIgnoreCase) - 7);
string orderby = (index >= 0) ? query.Substring(index) : "ORDER BY Id";
select = select.Insert(6, " ROW_NUMBER() OVER(" + orderby + ") AS RowNum,");
return(string.Format(
@"WITH Buffer AS ({0}) SELECT {4} FROM Buffer WHERE RowNum BETWEEN {3} AND {3}+{2}-1 {1};",
select, orderby, limit, offset + 1, fields));
}
public void TestDefaultDbStringDbType()
{
var origDefaultStringDbType = DbString.IsAnsiDefault;
try
{
DbString.IsAnsiDefault = true;
var a = new DbString {
Value = "abcde"
};
var b = new DbString {
Value = "abcde", IsAnsi = false
};
Assert.True(a.IsAnsi);
Assert.False(b.IsAnsi);
}
finally
{
DbString.IsAnsiDefault = origDefaultStringDbType;
}
}
public static IEnumerable <Commit <byte[]> > Execute(DocumentTransaction tx, ReadStream command)
{
if (tx.SqlTransaction.IsolationLevel != IsolationLevel.Snapshot)
{
throw new InvalidOperationException("Reads from event store is best done in snapshot isolation so they don't block writes.");
}
var sql = $@"
SELECT Position, EventId, CommitId, @Id AS [StreamId], SequenceNumber, Name, Generation, Metadata, Data
FROM {tx.Store.Database.FormatTableNameAndEscape(command.Table.Name)}
WHERE StreamId = @Id AND SequenceNumber >= @fromStreamSeq AND Position <= @toPosition
ORDER BY SequenceNumber {(command.Direction == Direction.Forward ? "ASC" : "DESC")}";
// Using DbString over just string as a important performance optimization,
// see https://github.com/StackExchange/dapper-dot-net/issues/288
var idParameter = new DbString {
Value = command.StreamId, IsAnsi = false, IsFixedLength = false, Length = 850
};
return(tx.SqlConnection.Query <Row>(sql, new { Id = idParameter, command.FromStreamSeq, command.ToPosition }, tx.SqlTransaction, buffered: false).Batch());
}
public static DbString Coalesce(this DbFunctions dbFunctions, DbString value, params IDbFragment[] other)
{
return(new DbStringFunctionValue("coalesce", new[] { value }.Concat(other)));
}
private InterpolatedStatementParser(string format, params object[] arguments)
{
Parameters = new ParameterInfos();
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(format))
{
return;
}
var matches = _formattableArgumentRegex.Matches(format);
int lastPos = 0;
for (int i = 0; i < matches.Count; i++)
{
// unescape escaped curly braces
string sql = format.Substring(lastPos, matches[i].Index - lastPos).Replace("{{", "{").Replace("}}", "}");
lastPos = matches[i].Index + matches[i].Length;
// arguments[i] may not work because same argument can be used multiple times
int argPos = int.Parse(matches[i].Groups["ArgPos"].Value);
string argFormat = matches[i].Groups["Format"].Value;
List <string> argFormats = argFormat.Split(new char[] { ',', '|' }, StringSplitOptions.RemoveEmptyEntries).Select(f => f.Trim()).ToList();
object arg = arguments[argPos];
if (argFormats.Contains("raw")) // example: {nameof(Product.Name):raw} -> won't be parametrized, we just emit raw string!
{
sb.Append(sql);
sb.Append(arg);
continue;
}
else if (arg is FormattableString fsArg) //Support nested FormattableString
{
sb.Append(sql);
var nestedStatement = new InterpolatedStatementParser(fsArg);
string subSql = nestedStatement.Sql;
if (nestedStatement.Parameters.Any())
{
subSql = (Parameters.MergeParameters(nestedStatement.Parameters, nestedStatement.Sql) ?? subSql);
}
sb.Append(nestedStatement.Sql);
continue;
}
else if (arg is ICompleteCommand innerQuery) //Support nested QueryBuilder, CommandBuilder or FluentQueryBuilder
{
sb.Append(sql);
string innerSql = innerQuery.Sql;
// Convert back from @p0 @p1 etc to {0} {1} etc (FormattableString standard)
Regex matchParametersRegex = new Regex("(?:[,~=<>*/%+&|^-]|\\s|\\b|^)* " + "(" + DapperQueryBuilderOptions.DatabaseParameterSymbol + DapperQueryBuilderOptions.AutoGeneratedParameterName + "(?<Name>\\d*)" + ")" + " (?:[,~=<>*/%+&|^-]|\\s|\\b|$)",
RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled);
innerSql = matchParametersRegex.Replace(innerSql, match => {
Group parm = match.Groups[1];
int parmNum = int.Parse(match.Groups[2].Value);
string replace = "{" + parmNum + "}";
string ret = string.Format("{0}{1}{2}", match.Value.Substring(0, parm.Index - match.Index), replace, match.Value.Substring(parm.Index - match.Index + parm.Length));
return(ret);
});
var nestedStatement = new InterpolatedStatementParser(innerSql, innerQuery.Parameters.Select(p => p.Value.Value).ToArray());
string subSql = nestedStatement.Sql;
if (nestedStatement.Parameters.Any())
{
subSql = (Parameters.MergeParameters(nestedStatement.Parameters, nestedStatement.Sql) ?? subSql);
}
sb.Append(nestedStatement.Sql);
continue;
}
// If user passes " column LIKE '{variable}' ", we assume that he used single quotes incorrectly as if interpolated string was a sql literal
if (quotedVariableStart.IsMatch(sql) && quotedVariableEnd.IsMatch(format.Substring(lastPos)))
{
sql = sql.Substring(0, sql.Length - 1); // skip starting quote
lastPos++; // skip closing quote
}
sb.Append(sql);
var direction = System.Data.ParameterDirection.Input;
System.Data.DbType?dbType = null;
if (argFormats.Contains("out"))
{
direction = System.Data.ParameterDirection.Output;
}
System.Data.DbType parsedDbType;
Match m;
foreach (var f in argFormats)
{
if (arg is string && (m = regexDbTypeString.Match(f)) != null && m.Success) // String(maxlength) / nvarchar(maxlength) / String / nvarchar
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = false,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeAnsiString.Match(f)) != null && m.Success) // AnsiString(maxlength) / varchar(maxlength) / AnsiString / varchar
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = false,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeStringFixedLength.Match(f)) != null && m.Success) // StringFixedLength(length) / nchar(length) / StringFixedLength / nchar
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = true,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeAnsiStringFixedLength.Match(f)) != null && m.Success) // AnsiStringFixedLength(length) / char(length) / AnsiStringFixedLength / char
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = true,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeText.Match(f)) != null && m.Success) // text / varchar(MAX) / varchar(-1)
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = true,
Value = (string)arg,
Length = int.MaxValue
}
}
;
else if (arg is string && (m = regexDbTypeNText.Match(f)) != null && m.Success) // ntext / nvarchar(MAX) / nvarchar(-1)
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = true,
Value = (string)arg,
Length = int.MaxValue
}
}
;
else if (!(arg is DbString) && dbType == null && Enum.TryParse <System.Data.DbType>(value: f, ignoreCase: true, result: out parsedDbType))
{
dbType = parsedDbType;
}
//TODO: parse SqlDbTypes?
// https://stackoverflow.com/questions/35745226/net-system-type-to-sqldbtype
// https://gist.github.com/tecmaverick/858392/53ddaaa6418b943fa3a230eac49a9efe05c2d0ba
}
sb.Append(DapperQueryBuilderOptions.DatabaseParameterSymbol + Parameters.Add(arg, dbType, direction));
}
string lastPart = format.Substring(lastPos).Replace("{{", "{").Replace("}}", "}");
sb.Append(lastPart);
Sql = sb.ToString();
_parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => DapperQueryBuilderOptions.DatabaseParameterSymbol + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
}
public static DbString IIf(this DbFunctions dbFunctions, DbBool condition, DbString trueValue, DbString falseValue)
=> new DbStringFunctionValue("iif", new IDbFragment[] { condition, trueValue, falseValue });
private InterpolatedStatementParser(string format, params object[] arguments)
{
Parameters = new ParameterInfos();
StringBuilder sb = new StringBuilder();
if (string.IsNullOrEmpty(format))
{
return;
}
var matches = _formattableArgumentRegex.Matches(format);
int lastPos = 0;
for (int i = 0; i < matches.Count; i++)
{
// unescape escaped curly braces
string sql = format.Substring(lastPos, matches[i].Index - lastPos).Replace("{{", "{").Replace("}}", "}");
lastPos = matches[i].Index + matches[i].Length;
// arguments[i] may not work because same argument can be used multiple times
int argPos = int.Parse(matches[i].Groups["ArgPos"].Value);
string argFormat = matches[i].Groups["Format"].Value;
List <string> argFormats = argFormat.Split(new char[] { ',', '|' }, StringSplitOptions.RemoveEmptyEntries).Select(f => f.Trim()).ToList();
object arg = arguments[argPos];
if (argFormats.Contains("raw")) // example: {nameof(Product.Name):raw} -> won't be parametrized, we just emit raw string!
{
sb.Append(sql);
sb.Append(arg);
continue;
}
// If user passes " column LIKE '{variable}' ", we assume that he used single quotes incorrectly as if interpolated string was a sql literal
if (quotedVariableStart.IsMatch(sql) && quotedVariableEnd.IsMatch(format.Substring(lastPos)))
{
sql = sql.Substring(0, sql.Length - 1); // skip starting quote
lastPos++; // skip closing quote
}
sb.Append(sql);
var direction = System.Data.ParameterDirection.Input;
System.Data.DbType?dbType = null;
if (argFormats.Contains("out"))
{
direction = System.Data.ParameterDirection.Output;
}
System.Data.DbType parsedDbType;
Match m;
foreach (var f in argFormats)
{
if (arg is string && (m = regexDbTypeString.Match(f)) != null && m.Success) // String(maxlength) / nvarchar(maxlength) / String / nvarchar
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = false,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeAnsiString.Match(f)) != null && m.Success) // AnsiString(maxlength) / varchar(maxlength) / AnsiString / varchar
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = false,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["maxlength"].Value) ? Math.Max(DbString.DefaultLength, ((string)arg).Length) : int.Parse(m.Groups["maxlength"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeStringFixedLength.Match(f)) != null && m.Success) // StringFixedLength(length) / nchar(length) / StringFixedLength / nchar
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = true,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeAnsiStringFixedLength.Match(f)) != null && m.Success) // AnsiStringFixedLength(length) / char(length) / AnsiStringFixedLength / char
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = true,
Value = (string)arg,
Length = (string.IsNullOrEmpty(m.Groups["length"].Value) ? ((string)arg).Length : int.Parse(m.Groups["length"].Value))
}
}
;
else if (arg is string && (m = regexDbTypeText.Match(f)) != null && m.Success) // text / varchar(MAX) / varchar(-1)
{
arg = new DbString()
{
IsAnsi = false,
IsFixedLength = true,
Value = (string)arg,
Length = int.MaxValue
}
}
;
else if (arg is string && (m = regexDbTypeNText.Match(f)) != null && m.Success) // ntext / nvarchar(MAX) / nvarchar(-1)
{
arg = new DbString()
{
IsAnsi = true,
IsFixedLength = true,
Value = (string)arg,
Length = int.MaxValue
}
}
;
else if (!(arg is DbString) && dbType == null && Enum.TryParse <System.Data.DbType>(value: f, ignoreCase: true, result: out parsedDbType))
{
dbType = parsedDbType;
}
//TODO: parse SqlDbTypes?
// https://stackoverflow.com/questions/35745226/net-system-type-to-sqldbtype
// https://gist.github.com/tecmaverick/858392/53ddaaa6418b943fa3a230eac49a9efe05c2d0ba
}
sb.Append(Parameters.Add(arg, dbType, direction));
}
string lastPart = format.Substring(lastPos).Replace("{{", "{").Replace("}}", "}");
sb.Append(lastPart);
Sql = sb.ToString();
_parametersStr = string.Join(", ", Parameters.ParameterNames.ToList().Select(n => "@" + n + "='" + Convert.ToString(Parameters.Get <dynamic>(n)) + "'"));
}
private List <OrderReport> GetReportData(string jtSorting, List <Criterias> criteria)
{
List <OrderReport> lst = new List <OrderReport>();
os = new OrderSummary();
string userRestriction = UserRestriction.GetUserRestriction(User.Identity.GetUserName(), "OrderReport");
if (userRestriction == "")
{
return(lst);
}
string conStr = ConnectionStringManager.ProdConnectionString;
using (con = new NpgsqlConnection(conStr))
{
string query = "salesreport";
con.Open();
//ToDo : sorgu dinamik hale getirilecek.
string prQuery = "";
if (criteria.Count > 0)
{
prQuery = prQuery + "(1 = 0 ";
}
foreach (var item in criteria)
{
prQuery = prQuery + " OR (1=1";
if (item.startdate != null)
{
prQuery = prQuery + " AND f.PostedDate >= '" + item.startdate + " 12:00:00 AM'";
}
if (item.enddate != null)
{
prQuery = prQuery + " AND f.PostedDate <= '" + item.enddate + " 11:59:59 PM'";
}
if (item.asin != null)
{
prQuery = prQuery + " AND s.asin = '" + item.asin + "'";
}
if (item.sku != null)
{
prQuery = prQuery + " AND s.sku = '" + item.sku + "'";
}
if (item.vendor != null)
{
prQuery = prQuery + " AND s.vendor = '" + item.vendor + "'";
}
if (item.skutitle != null)
{
prQuery = prQuery + " AND s.title like '%" + item.skutitle + "%'";
}
if (item.fulfill != null)
{
prQuery = prQuery + " AND s.FulfillmentBy = '" + item.fulfill + "'";
}
prQuery = prQuery + ") ";
}
prQuery = prQuery + ")";
if (userRestriction != "")
{
prQuery = "(" + userRestriction + " AND " + prQuery + ")";
}
DbString str = new DbString();
str.Value = prQuery;
lst = con.Query <OrderReport>(query, new { parameterquery = str }, commandType: CommandType.StoredProcedure).ToList <OrderReport>();
long orderItems = 0;
long unitsOrdered = 0;
decimal orderProductSales = 0;
decimal avgUnitsOrderedItem = 0;
decimal avgSalesOrderedItem = 0;
decimal refundAmount = 0;
decimal profit = 0;
foreach (var item in lst)
{
orderItems = orderItems + item.OrderItem;
unitsOrdered = unitsOrdered + item.UnitsOrdered;
orderProductSales = orderProductSales + item.OrderUAmount;
refundAmount = refundAmount + item.RefundUAmount;
profit = profit + item.Profit;
}
if (lst.Count > 0)
{
avgUnitsOrderedItem = Convert.ToDecimal(orderItems) / Convert.ToDecimal(unitsOrdered);
avgSalesOrderedItem = Math.Round(orderProductSales / orderItems, 3);
os.AvgSalesOrderItem = Math.Round(avgSalesOrderedItem, 3);
os.AvgUnitsOrderItem = Math.Round(avgUnitsOrderedItem, 3);
os.OrderedProductSales = orderProductSales;
os.TotalOrderItems = orderItems;
os.UnitsOrdered = unitsOrdered;
os.RefundAmount = refundAmount;
os.Profit = profit;
}
else
{
os.AvgSalesOrderItem = 0;
os.AvgUnitsOrderItem = 0;
os.OrderedProductSales = 0;
os.Profit = 0;
os.RefundAmount = 0;
os.TotalOrderItems = 0;
os.UnitsOrdered = 0;
}
//TODO : diğer alanlarda konacak.
if (jtSorting != null && jtSorting != "")
{
lst.Sort(jtSorting);
}
}
return(lst);
}
public static DbString ToUpper(this DbString value)
{
return(new DbStringFunctionValue("upper", new[] { value }));
}
public static DbString Average(this DbFunctions dbFunctions, DbString value)
{
return(new DbStringFunctionValue("avg", new[] { value }));
}
//public static DbExpression RowNumber(this DbFunctions dbFunctions,IEnumerable<Column> partitionBy = null, IEnumerable<(Column column, Sort sort)> orderBy = null)
//{
// return new RowNumberFunctionDbExpression(partitionBy ?? Enumerable.Empty<Column>(), orderBy ?? Enumerable.Empty<(Column column, Sort sort)>());
//}
public static DbString Sum(this DbFunctions dbFunctions, DbString value)
{
return(new DbStringFunctionValue("sum", new[] { value }));
}
public static DbString Right(this DbFunctions dbFunctions, DbString dbString, int length)
=> new DbStringFunctionValue("right", new IDbFragment[] { dbString, length.DbValue() });
public static DbString Max(this DbFunctions dbFunctions, DbString value)
{
return(new DbStringFunctionValue("max", new[] { value }));
}
public static DbInt Len(this DbFunctions dbFunctions, DbString dbString)
=> new DbIntFunctionValue("len", new[] { dbString });
//public static TResult Cast<T, TResult>(this DbFunctions dbFunctions, T value, ColumnType<TResult> column)
//{
// throw new InvalidOperationException(Exceptions.DontInvokeDirectly);
//}
public static DbBool Like(this DbFunctions dbFunctions, DbString field, string pattern)
{
return(new DbBoolBinaryOperator(field, "like", pattern.DbValue()));
}
public static DbString Replicate(this DbFunctions dbFunctions, DbString dbString, DbInt length)
=> new DbStringFunctionValue("replicate", new IDbFragment[] { dbString, length });
