/// <summary>
/// This method creates new Data source
/// </summary>
/// <param name="resourceGroupName">Resource group name.</param>
/// <param name="resourceName">Resource name.</param>
/// <param name="dataSourceInput">Data source input</param>
/// <param name="dataSourceName">data source name</param>
internal bool CreateDataSource(string resourceGroupName,
string resourceName,
DataSourceInput dataSourceInput,
string dataSourceName,
out string message)
{
// Create a payload to make the call to create data source.
string json = JsonConvert.SerializeObject(dataSourceInput);
return(_serviceHelper.CreateDataStore(resourceGroupName, resourceName, json, dataSourceName, out message));
}
public DataSource(DataSourceInput input)
{
this.Input = input;
}
//[WebApi(Route = "api/data/query", Authorized = false, Method = WebApiMethod.Post)]
public dynamic Generate(QueryInputParamater input)
{
using (IDbConnection conn = new SqlConnection(_configuration.GetValue <string>("ConnectionString:SQLConnection")))
{
try
{
IEnumerable <dynamic> result;
conn.Open();
var args1 = new DynamicParameters();
args1.Add("@P1", input.Code);
DataSourceInput data = conn.QuerySingle <DataSourceInput>("SELECT Top 1 * FROM ADM.ADM_DATA_SOURCES WHERE DSRC_COD=@P1", args1);
if (string.IsNullOrEmpty(data.DSRC_JSO_FIELDS))
{
throw new InvalidOperationException("Invalid columns data");
}
if (data != null)
{
var dbParams = new DynamicParameters();
var sql = new StringBuilder();
// View And Tables
if (data.DSRC_TYP_SRC == DataSourceType.Table || data.DSRC_TYP_SRC == DataSourceType.View)
{
//TODO: remove sql injection
if (input.Groups.Any() && input.GroupsKeys.Count != input.Groups.Count)
{
input.Columns[0] = "[" + input.Groups[input.GroupsKeys.Count] + "]";
}
sql.AppendFormat("SELECT {0},overall_count = COUNT(*) OVER() FROM (SELECT ", input.Columns.Count > 0 ? String.Join(",", input.Columns) : "*");
var json = JsonConvert.DeserializeObject <IEnumerable <dynamic> >(data.DSRC_JSO_FIELDS);
var list = json.Where(c => input.Groups.Contains((string)c.alias));
if (input.Groups.Any() && input.GroupsKeys.Count != input.Groups.Count)
{
sql.AppendFormat("distinct ");
foreach (var item in list)
{
if (input.Groups[input.GroupsKeys.Count] == (string)item.alias)
{
var isField = input.Sort.Find(x => x.Field == (string)item.alias);
if (isField == null)
{
input.Sort.Add(new QuerySortItems
{
Field = item.alias,
Sort = "asc"
});
}
sql.AppendFormat("[{0}] AS [{1}],", item.name, item.alias);
}
}
}
else
{
foreach (var item in json)
{
//if (input.Columns.Count > 0 && !input.Columns.Contains(String.Format("{0}", item.alias)))
// continue;
sql.AppendFormat("[{0}] AS [{1}],", item.name, item.alias);
}
}
sql.Length--;
sql.AppendFormat(" FROM {0} ", data.DSRC_SRC);
// Query Params
if (!string.IsNullOrWhiteSpace(data.DSRC_QRY_PARAMS))
{
sql.AppendFormat(" WHERE ( {0} ", data.DSRC_QRY_PARAMS);
var matches = Regex.Matches(data.DSRC_QRY_PARAMS, "@[a-zA-Z0-9_]+");
foreach (Match p in matches)
{
var name = p.Value.Substring(1);
var item = input.Filters.FirstOrDefault(c => c.FieldName.Equals(name, StringComparison.OrdinalIgnoreCase));
if (item != null)
{
dbParams.Add(p.Value, item.Value == null ? null : item.Value.ToString());
}
else
{
dbParams.Add(p.Value, null);
}
}
sql.Append(")");
foreach (QueryFilterItems q in input.Filters)
{
if ((q.Value != null) || (q.Value == null && (q.Operator == "is-empty" || q.Operator == "is-not-empty")))
{
string[] jsonField = new string[3];
var type = "";
var Name = "";
bool isNewParam = true;
foreach (var db in dbParams.ParameterNames)
{
if (String.Equals(db, q.FieldName, StringComparison.OrdinalIgnoreCase))
{
isNewParam = false;
}
}
if (isNewParam)
{
if (q.FieldName.Substring(0, 1) == "$")
{
jsonField = q.FieldName.Split('.');
q.FieldName = jsonField[1];
}
foreach (var it in json)
{
if (it.alias == q.FieldName)
{
type = it.type;
Name = it.name;
}
}
switch (type)
{
case "string":
switch (q.Operator.ToLower())
{
case "contains":
if (q.Value.GetType().Name == "String")
{
sql.AppendFormat(" AND ( [{0}] LIKE @{0} )", Name);
dbParams.Add('@' + Name, '%' + (q.Value == null ? null : q.Value.ToString()) + '%');
break;
}
else
{
sql.AppendFormat(" AND ( [{0}] IN ({1}) )", Name, String.Join(',', ((JArray)q.Value)
.ToObject <List <string> >()
.Select(c =>
String.Format("'{0}'", c.Trim().Replace("'", "''")))
));
break;
}
case "end-with":
sql.AppendFormat(" AND ( [{0}] LIKE @{0} )", Name);
dbParams.Add('@' + Name, '%' + (q.Value == null ? null : q.Value.ToString()));
break;
case "equal":
sql.AppendFormat(" AND ( [{0}] LIKE @{0} )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "is-empty":
sql.AppendFormat(" AND ( [{0}] is NULL )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "is-not-empty":
sql.AppendFormat(" AND ( [{0}] IS NOT NULL )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "not-contains":
sql.AppendFormat(" AND ( [{0}] IS NOT @{0} )", Name);
dbParams.Add('@' + Name, '%' + (q.Value == null ? null : q.Value.ToString()) + '%');
break;
case "not-equal":
sql.AppendFormat(" AND ( [{0}] NOT LIKE @{0} )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "start-with":
sql.AppendFormat(" AND ( [{0}] LIKE @{0} )", Name);
dbParams.Add('@' + Name, (q.Value == null ? null : q.Value.ToString()) + '%');
break;
case "In":
// sql.AppendFormat(" AND ( {0} IN (@{0}) )", Name);
// dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
}
break;
case "Number":
switch (q.Operator.ToLower())
{
case "equal":
sql.AppendFormat(" AND ( [{0}] = @{0} )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "not-equal":
sql.AppendFormat(" AND ( [{0}] <> @{0} )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "greater-than":
sql.AppendFormat(" AND ( [{0}] > @{0} )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "greater-than-equal":
sql.AppendFormat(" AND ( [{0}] >= @{0} )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "less-than":
sql.AppendFormat(" AND ( [{0}] < @{0} )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "less-than-equal":
sql.AppendFormat(" AND ( [{0}] <= @{0} )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "is-empty":
sql.AppendFormat(" AND ( [{0}] is NULL )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "is-not-empty":
sql.AppendFormat(" AND ( [{0}] IS NOT NULL )", Name);
dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "Between":
// sql.AppendFormat(" AND ( {0} BETWEEN @1{0} AND @2{0} )", Name);
// dbParams.Add('@'+'1'+ Name, q.Value == null ? null : q.Value.ToString());
// dbParams.Add('@' + '2' + Name, q.Value == null ? null : q.Value.ToString());
break;
case "In":
// sql.AppendFormat(" AND ( {0} IN (@{0}) )", Name);
// dbParams.Add('@' + Name, q.Value == null ? null : q.Value.ToString());
break;
}
break;
case "dateTime":
case "date":
case "dateTime2":
// if (q.Operator== "between")
if (String.Equals("between", q.Operator, StringComparison.OrdinalIgnoreCase))
{
DateTime v1;
DateTime v2;
DateTime.TryParse((((Newtonsoft.Json.Linq.JContainer)q.Value).First)?.ToString(), out v1);
DateTime.TryParse((((Newtonsoft.Json.Linq.JContainer)q.Value).Last)?.ToString(), out v2);
sql.AppendFormat(" AND ( [{0}] BETWEEN @{0}1 AND @{0}2 )", Name);
dbParams.Add('@' + Name + '1', v1);
dbParams.Add('@' + Name + '2', v2);
break;
}
else
{
DateTime v1;
DateTime.TryParse((q.Value)?.ToString(), out v1);
sql.AppendFormat(" AND ( [{0}] = @{0} )", Name);
dbParams.Add('@' + Name, v1);
break;
}
case "json":
switch (q.Operator.ToLower())
{
case "contains":
sql.AppendFormat(" AND ( JSON_VALUE( [{0}] , '$.{1}' ) IN ({2}) )", Name, jsonField[2], String.Join(',', ((JArray)q.Value)
.ToObject <List <string> >()
.Select(c =>
String.Format("'{0}'", c.Trim().Replace("'", "''")))
));
break;
}
break;
case "":
return("your Filter Parameters is not Defined");
}
}
}
}
}
if (input.Groups.Any() && input.GroupsKeys.Count == input.Groups.Count)
{
foreach (var item in list)
{
var N = "";
N = item.name;
sql.AppendFormat(" AND ( [{0}] = @{0} )", item.name);
int ind = input.Groups.FindIndex(a => a == (string)item.alias);
dbParams.Add('@' + N, input.GroupsKeys[ind].ToString());
}
}
else if (input.Groups.Any() && input.GroupsKeys.Count != input.Groups.Count)
{
for (int i = 0; i < input.GroupsKeys.Count; i++)
{
var l = list.Where(c => (string)c.alias == input.Groups[i]);
foreach (var itm in l)
{
var N = "";
N = itm.name;
sql.AppendFormat(" AND ( [{0}] = @{0} )", itm.name);
int ind = input.Groups.FindIndex(a => a == (string)itm.alias);
dbParams.Add('@' + N, input.GroupsKeys[ind].ToString());
}
}
}
sql.Append(") T");
// Where Clause
string orderBY = null;
if (input.Sort.Count > 0)
{
foreach (var item in input.Sort)
{
orderBY = orderBY + " [" + item.Field + "] " + item.Sort + " ,";
}
orderBY = orderBY.Substring(0, orderBY.Length - 1);
}
else
{
if (!string.IsNullOrWhiteSpace(data.DSRC_ORDER))
{
orderBY = data.DSRC_ORDER;
}
}
//if (!string.IsNullOrWhiteSpace(data.DSRC_ORDER))
//{
// orderBY = data.DSRC_ORDER;
//}
// PAGING
if (input.Take > 0)
{
if (!string.IsNullOrEmpty(orderBY))
{
sql.AppendFormat(" ORDER BY {0} ", orderBY);
sql.AppendFormat("OFFSET ({0}) ROWS ", input.Skip.GetValueOrDefault(0));
sql.AppendFormat("FETCH NEXT {0} ROWS ONLY", input.Take);
}
}
else
{
if (!string.IsNullOrEmpty(orderBY))
{
sql.AppendFormat(" ORDER BY {0} ", orderBY);
}
}
result = conn.Query <dynamic>(sql.ToString(), dbParams);
long totalCount = result.LongCount() > 0 ? result.Select(x => x.overall_count).ToList()[0] : 0;
//(IDictionary<String, Object>)result).Remove("Name");
//return result;
return(new
{
items = result,
totalCount
});
}
}
throw new InvalidOperationException();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
/// <summary>
/// Resolves the lambda ZipFile code.
/// </summary>
/// <param name="writer">The writer.</param>
/// <param name="runtime">The runtime.</param>
/// <param name="cloudFormationResource">The cloud formation resource.</param>
/// <param name="attributes">The attributes.</param>
/// <param name="mapping">The mapping.</param>
/// <param name="inputs">The list of input variables and data sources.</param>
private static void ResolveLambdaZipCode(
TextWriter writer,
string runtime,
ITemplateObject cloudFormationResource,
JObject attributes,
ResourceMapping mapping,
IList <InputVariable> inputs)
{
var traits = LambdaTraits.FromRuntime(runtime);
var dirName = Path.Combine("lambda", cloudFormationResource.Name);
var fileName = Path.Combine(dirName, $"index{traits.ScriptFileExtension}");
var zipName = Path.Combine(dirName, $"{cloudFormationResource.Name}_deployment_package.zip");
// Create zipper resource
var zipperResource = $"{cloudFormationResource.Name}_deployment_package";
var archiveDataSource = new DataSourceInput(
"archive_file",
zipperResource,
new Dictionary <string, string>
{
{ "type", "zip" },
{ "source_file", fileName.Replace("\\", "/") },
{ "output_path", zipName.Replace("\\", "/") }
});
inputs.Add(archiveDataSource);
// Fix up state file.
// Find handler function
var script = File.ReadAllText(fileName);
var m = traits.HandlerRegex.Match(script);
if (m.Success)
{
UpdatePropertyValue(
"handler",
cloudFormationResource.Template,
attributes,
mapping,
inputs,
$"index.{m.Groups["handler"].Value}");
}
UpdatePropertyValue(
"filename",
cloudFormationResource.Template,
attributes,
mapping,
inputs,
new DataSourceReference(
"archive_file",
$"{cloudFormationResource.Name}_deployment_package",
"output_path",
false));
UpdatePropertyValue(
"source_code_hash",
cloudFormationResource.Template,
attributes,
mapping,
inputs,
new DataSourceReference(
"archive_file",
$"{cloudFormationResource.Name}_deployment_package",
"output_base64sha256",
false));
}
public DataSource(DataSourceInput input)
{
this.Input = input;
}
