Ejemplo n.º 1
0
        //private bool IsKeyField(WidgetModel widgetModel, string measureExp)
        //{
        //    var replacementStrings = new string[6] { "sum", "count", "avg", "(", ")", "distinct" };
        //    var mTableName = GetTableNameOfMeasure(measureExp);
        //    var expression = measureExp;
        //    DerivedAssociation derivedAssociation = null;
        //    if (dashboard.TableAssociationHash.ContainsKey(widgetModel.TablesKey))
        //    {
        //        derivedAssociation = dashboard.TableAssociationHash[widgetModel.TablesKey];
        //    }
        //    //if (string.IsNullOrWhiteSpace(expression)) { continue; }
        //    foreach (var r in replacementStrings)
        //    {
        //        expression = expression.Replace(r, "");
        //    }
        //    expression = expression.Trim();
        //    bool isKeyField = false;

        //    if (null != derivedAssociation && measureExp.IndexOf("count") != -1) //Only for count
        //    {
        //        if (derivedAssociation.Relations.Where(r => r.Keys.Where(k => k.Contains(expression)).Count() > 0).Count() > 0)
        //        {
        //            if (derivedAssociation.TableName1 == mTableName)
        //            {
        //                if (derivedAssociation.Relations[0].Cardinality == Cardinality.OneToMany)
        //                {
        //                    isKeyField = true;
        //                }
        //            } else
        //            {
        //                var relation = derivedAssociation.Relations.Where(r => r.Keys.Where(k => k.Contains(expression)).Count() > 0).First();
        //                if(relation.Keys[0][0] == expression)
        //                {
        //                    if (relation.Cardinality == Cardinality.OneToMany)
        //                    {
        //                        isKeyField = true;
        //                    }
        //                } else
        //                {
        //                    if (relation.Cardinality == Cardinality.ManyToOne)
        //                    {
        //                        isKeyField = true;
        //                    }
        //                }
        //            }

        //        }
        //    }

        //    return isKeyField;
        //}


        private string GetDistinctExp(WidgetModel widgetModel, string measureExp)
        {
            if (measureExp.IndexOf("distinct") != -1)
            {
                return(measureExp);
            }
            var replacementStrings = new string[6] {
                "sum", "count", "avg", "(", ")", "distinct"
            };
            var mTableName = GetTableNameOfMeasure(measureExp);
            var expression = measureExp;
            DerivedAssociation derivedAssociation = null;

            if (dashboard.TableAssociationHash.ContainsKey(widgetModel.TablesKey))
            {
                derivedAssociation = dashboard.TableAssociationHash[widgetModel.TablesKey];
            }
            //if (string.IsNullOrWhiteSpace(expression)) { continue; }
            foreach (var r in replacementStrings)
            {
                expression = expression.Replace(r, "");
            }
            expression = expression.Trim();
            measureExp = measureExp.Replace(expression, "Distinct " + expression);

            return(measureExp);
        }
Ejemplo n.º 2
0
        private bool IsKeyField(WidgetModel widgetModel, string measureExp)
        {
            var replacementStrings = new string[6] {
                "sum", "count", "avg", "(", ")", "distinct"
            };
            var mTableName = GetTableNameOfMeasure(measureExp);
            var expression = measureExp;
            DerivedAssociation derivedAssociation = null;

            if (dashboard.TableAssociationHash.ContainsKey(widgetModel.TablesKey))
            {
                derivedAssociation = dashboard.TableAssociationHash[widgetModel.TablesKey];
            }
            //if (string.IsNullOrWhiteSpace(expression)) { continue; }
            foreach (var r in replacementStrings)
            {
                expression = expression.Replace(r, "");
            }
            expression = expression.Trim();
            bool isKeyField = false;

            if (null != derivedAssociation) // && measureExp.IndexOf("count") != -1
            {
                if (derivedAssociation.Relations.Where(r => r.Keys.Where(k => k.Contains(expression)).Count() > 0).Count() > 0)
                {
                    isKeyField = true;
                }
            }

            return(isKeyField);
        }
Ejemplo n.º 3
0
        private bool IsDistinctAppl(WidgetModel widgetModel, string expression)
        {
            bool appl = false;
            DerivedAssociation derivedAssociation = null;

            if (dashboard.TableAssociationHash.ContainsKey(widgetModel.TablesKey))
            {
                derivedAssociation = dashboard.TableAssociationHash[widgetModel.TablesKey];
            }
            if (null != derivedAssociation) // && measureExp.IndexOf("count") != -1
            {
                var tableName = GetTableNameOfMeasure(expression);
                var tableId   = this.dashboard.Tables.Where(t => t.Name == tableName).First().Id;
                //if(widgetModel.TablesKey.Split(',')[0] == tableId.ToString())
                //{
                //    expression = GetDistinctExp(widgetModel, expression);
                //}
                var tablesKeySplit = widgetModel.TablesKey.Split(',');
                for (var i = 0; i < tablesKeySplit.Count(); i++)
                {
                    var relationCount = i;
                    if (i == 0)
                    {
                        relationCount = 0;
                    }
                    else
                    {
                        relationCount = i - 1;
                    }
                    if (i < derivedAssociation.Relations.Count())
                    {
                        if (derivedAssociation.Relations[i].Cardinality == Cardinality.ManyToOne)
                        {
                            break;
                        }
                        if (derivedAssociation.TableName1 == tableName && (derivedAssociation.Relations[0].Cardinality == Cardinality.OneToOne || derivedAssociation.Relations[0].Cardinality == Cardinality.OneToMany))
                        {
                            appl = true;
                            break;
                        }
                        else if (derivedAssociation.Relations[i].TableName2 == tableName)
                        {
                            if (derivedAssociation.Relations[i].Cardinality == Cardinality.OneToOne)
                            {
                                appl = true;
                                break;
                            }
                        }
                    }
                    //if (tableId.ToString() == tablesKeySplit[i])
                    //{
                    //    //expression = GetDistinctExp(widgetModel, expression);
                    //    appl = true;
                    //}
                }
            }

            return(appl);
        }
Ejemplo n.º 4
0
        private bool IsCountToSum(WidgetModel widgetModel, string measureExp)
        {
            var replacementStrings = new string[6] {
                "sum", "count", "avg", "(", ")", "distinct"
            };
            var mTableName = GetTableNameOfMeasure(measureExp);
            var expression = measureExp;
            DerivedAssociation derivedAssociation = null;

            if (dashboard.TableAssociationHash.ContainsKey(widgetModel.TablesKey))
            {
                derivedAssociation = dashboard.TableAssociationHash[widgetModel.TablesKey];
            }
            //if (string.IsNullOrWhiteSpace(expression)) { continue; }
            foreach (var r in replacementStrings)
            {
                expression = expression.Replace(r, "");
            }
            expression = expression.Trim();
            bool isCountToSum = false;

            if (null != derivedAssociation && measureExp.IndexOf("count") != -1) //Only for count
            {
                if (derivedAssociation.Relations.Where(r => r.Keys.Where(k => k.Contains(expression)).Count() > 0).Count() > 0)
                {
                    var relation = derivedAssociation.Relations.Where(r => r.Keys.Where(k => k.Contains(expression)).Count() > 0).First();
                    if (relation.TableName2 == mTableName)
                    {
                        if (relation.Cardinality == Cardinality.OneToMany)
                        {
                            isCountToSum = true;
                        }
                    }
                    else if (derivedAssociation.TableName1 == mTableName)
                    {
                        if (derivedAssociation.Relations[0].Cardinality == Cardinality.ManyToOne)
                        {
                            isCountToSum = true;
                        }
                    }
                    //isCountToSum = false;
                }
                else if (derivedAssociation.TableName1 == mTableName)
                {
                    if (derivedAssociation.Relations[0].Cardinality == Cardinality.ManyToOne)
                    {
                        isCountToSum = true;
                    }
                }
                else
                {
                    var matchedRel = derivedAssociation.Relations.Where(r => r.TableName2 == mTableName).FirstOrDefault();
                    if (matchedRel.Cardinality == Cardinality.OneToMany)
                    {
                        isCountToSum = true;
                    }
                }
            }

            //return (measureExp.Replace(tableName, replaceByTableName));
            //return (replaceByTableName + ".\"" + measureExp + "\"");
            return(isCountToSum);
        }
Ejemplo n.º 5
0
        private string GetTablesAssociationQuery(WidgetModel widgetModel)
        {
            string             query                  = null;
            var                tablesKey              = (null != widgetModel.TablesKey)? widgetModel.TablesKey : GetTablesInvolved(widgetModel);
            DerivedAssociation derivedAssociation     = null;
            string             groupByAssociationKeys = null;

            if (dashboard.TableAssociationHash.ContainsKey(tablesKey))
            {
                derivedAssociation = dashboard.TableAssociationHash[tablesKey];

                if (null != derivedAssociation)
                {
                    query = " from  " + derivedAssociation.TableName1; // db.Query(derivedAssociation.TableName1);
                    foreach (var rel in derivedAssociation.Relations)
                    {
                        //var tableId = dashboard.Tables.Where(t => t.Name == rel.TableName2).FirstOrDefault().Id.ToString();
                        //if(!tablesKey.Contains(tableId)) { continue; }
                        //Todo: determine the joins based on filters on tables.
                        query += " full outer join " + rel.TableName2 + " on ";// + rel.Keys[0] + rel.Operation + rel.Keys[1] + " ";
                        //query = query.Join(rel.TableName2, rel.Keys[0], rel.Keys[1], rel.Operation, rel.Type);
                        foreach (var keys in rel.Keys)
                        {
                            query += keys[0] + rel.Operation + keys[1] + " and ";
                            groupByAssociationKeys += keys[0] + ",";
                        }
                        query = query.Substring(0, query.LastIndexOf("and"));
                    }
                    //if (null != groupByAssociationKeys)
                    //{
                    //    groupByAssociationKeys = groupByAssociationKeys.Substring(0, groupByAssociationKeys.LastIndexOf(","));
                    //}
                }
            }
            else
            {
                if (tablesKey.Split(',').Length == 1)
                {
                    var tableId   = tablesKey.Split(',')[0];
                    var tableName = dashboard.Tables.Where(t => t.Id.ToString() == tableId).First().Name;
                    //query = db.Query(tableName);
                    query = " from  " + tableName;
                }
            }

            var filters = widgetModel.FilterList;

            if (null != derivedAssociation && null != widgetModel.FilterList && widgetModel.FilterList.Count() > 0)
            {
                if (null != derivedAssociation)
                {
                    filters = widgetModel.FilterList.Where(f => derivedAssociation.TableName1 == f.TableName || derivedAssociation.Relations.Where(r => r.TableName2 == f.TableName).Count() > 0).ToList();
                }
            }

            if (filters.Count() > 0)
            {
                query += (query.Contains("where")) ? " " : " where ";
                foreach (var filter in widgetModel.FilterList)
                {
                    //query = query.WhereIn(filter.ColName, filter.Values);
                    var values = string.Empty;
                    foreach (var fValue in filter.Values)
                    {
                        values += "'" + fValue + "',";
                    }
                    values = values.Substring(0, values.LastIndexOf(","));
                    query += filter.ColName + " in (" + values + ") and ";
                }
                query = query.Substring(0, query.LastIndexOf("and"));
            }


            if (null != widgetModel.SearchList && widgetModel.SearchList.Count() > 0)
            {
                query += (null != filters && filters.Count() > 0) ? " and " : " where ";
                foreach (var search in widgetModel.SearchList)
                {
                    query += search.ColName + " like '%" + search.Value + "%' and ";
                }
                query = query.Substring(0, query.LastIndexOf("and"));
            }

            if (null != widgetModel.Dimension && widgetModel.Dimension.Length > 0 && null != widgetModel.Measure && widgetModel.Measure.Length > 0 && !widgetModel.IsForTotal) // && !widgetModel.IsForTotal)
            {
                //query = query.GroupBy(widgetModel.Dimension.Select(d => d.Name).ToArray<string>());

                query += " group by " + String.Join(",", widgetModel.Dimension.Select(d => d.Name));
                //if(null != groupByAssociationKeys)
                //{
                //    query += "," + groupByAssociationKeys;
                //}
            }

            if (widgetModel.EnablePagination && widgetModel.PageSize > 0 && !widgetModel.IsRecordCountReq && !widgetModel.IsForTotal)
            {
                query += " limit " + widgetModel.PageSize + " offset " + widgetModel.StartRowNum;
            }

            //query += " limit 1000" + " offset 0";

            return(query);
        }
Ejemplo n.º 6
0
        private string GetSelectQuery(WidgetModel widgetModel)
        {
            string selectQuery = null;
            var    measures    = new System.Text.StringBuilder();

            if (widgetModel.Type == "kpi" && (null == widgetModel.Measure || (null != widgetModel.Measure && widgetModel.Measure.Length == 0)))
            {
                return(null);
            }
            if (widgetModel.Type == "filter" && (null == widgetModel.Dimension || (null != widgetModel.Dimension && widgetModel.Dimension.Length == 0)))
            {
                return(null);
            }

            var measuresString = string.Empty;
            var dimString      = string.Empty;


            DerivedAssociation derivedAssociation = null;

            if (dashboard.TableAssociationHash.ContainsKey(widgetModel.TablesKey))
            {
                derivedAssociation = dashboard.TableAssociationHash[widgetModel.TablesKey];
            }
            if (null != widgetModel.Measure)
            {
                foreach (var measure in widgetModel.Measure)
                {
                    if (string.IsNullOrWhiteSpace(measure.Expression))
                    {
                        continue;
                    }
                    var isMeasureToBeConsidered = true;
                    if (widgetModel.TablesKey.Split(',').Where(tKey => measure.TableIds.Contains(Convert.ToInt32(tKey))).Count() == 0)
                    {
                        //This measure need not be used its tale dont have relation with other tables used in query.
                        isMeasureToBeConsidered = false;
                    }
                    var mTableName = GetTableNameOfMeasure(measure.Expression);
                    var expression = measure.Expression.Trim();

                    if (null != derivedAssociation && (expression.IndexOf("sum") != -1 || expression.IndexOf("count") != -1)) //Only for sum/count
                    {
                        if (!isMeasureToBeConsidered)
                        {
                            expression = "0";
                        }
                        else
                        {
                            //if (IsDistinctAppl(widgetModel, expression))
                            if (IsKeyField(widgetModel, expression) && IsDistinctAppl(widgetModel, expression)) //Temporary fix till we get column is measure or not
                            {
                                expression = GetDistinctExp(widgetModel, expression);
                            }
                            else

                            if (derivedAssociation.TableName1 == mTableName)
                            {
                                if (derivedAssociation.Relations[0].Cardinality == Cardinality.OneToMany && !IsKeyField(widgetModel, expression))
                                {
                                    var divisibleBy = "count(" + derivedAssociation.Relations[0].Keys[0][0] + ")";
                                    expression = "CASE WHEN " + divisibleBy + "=0 THEN " + expression + " ELSE (" + expression + "* count(distinct " + derivedAssociation.Relations[0].Keys[0][0] + "))/" + divisibleBy + " END "; //divide by count
                                }
                            }
                            else
                            {
                                var matchedRel = derivedAssociation.Relations.Where(r => r.TableName2 == mTableName).FirstOrDefault();
                                if (matchedRel.Cardinality == Cardinality.ManyToOne && !IsKeyField(widgetModel, expression))
                                {
                                    var divisibleBy = "count(" + matchedRel.Keys[0][0] + ")";
                                    //expression += expression + "/count(" + matchedRel.Keys[0][1] + ")"; //divide by count
                                    expression = "CASE WHEN " + divisibleBy + "=0 THEN " + expression + " ELSE (" + expression + "* count(distinct " + derivedAssociation.Relations[0].Keys[0][1] + "))/" + divisibleBy + " END "; //divide by count
                                }
                            }
                        }
                    }
                    //else if (null != derivedAssociation && expression.IndexOf("count") != -1) //Only for count
                    //{
                    //    if (!isMeasureToBeConsidered)
                    //    {
                    //        expression = "null";
                    //    }
                    //    else
                    //    {
                    //        ////if (IsKeyField(widgetModel, expression))
                    //        ////{
                    //        //    var tableName = GetTableNameOfMeasure(expression);
                    //        //    var tableId = this.dashboard.Tables.Where(t => t.Name == tableName).First().Id;
                    //        //    //if(widgetModel.TablesKey.Split(',')[0] == tableId.ToString())
                    //        //    //{
                    //        //    //    expression = GetDistinctExp(widgetModel, expression);
                    //        //    //}
                    //        //var tablesKeySplit = widgetModel.TablesKey.Split(',');
                    //        //for (var i = 0; i < tablesKeySplit.Count(); i++)
                    //        //{
                    //        //    var relationCount = i;
                    //        //    if (i == 0)
                    //        //    {
                    //        //        relationCount = 0;
                    //        //    }
                    //        //    else
                    //        //    {
                    //        //        relationCount = i - 1;
                    //        //    }
                    //        //    if (derivedAssociation.Relations[i].Cardinality == Cardinality.ManyToOne)
                    //        //    {
                    //        //        break;
                    //        //    }
                    //        //    if (tableId.ToString() == tablesKeySplit[i])
                    //        //    {
                    //        //        expression = GetDistinctExp(widgetModel, expression);
                    //        //    }
                    //        //}

                    //        if (IsDistinctAppl(widgetModel, expression))
                    //        {
                    //            expression = GetDistinctExp(widgetModel, expression);
                    //        }
                    //            //expression = GetDistinctExp(widgetModel, expression);
                    //       // }
                    //        //bool isCountToSum = IsCountToSum(widgetModel, expression);
                    //        //if (!isCountToSum)
                    //        //{
                    //        //    //add null condition i count is zero
                    //        //    //CASE WHEN count(orders.ordernumber) = 0 THEN null ELSE count(orders.ordernumber) END
                    //        //    expression = "CASE WHEN " + expression + "= 0 THEN null ELSE " + expression + " END ";
                    //        //}
                    //    }
                    //}

                    //measures.Append(string.Format("{0} ", measure.Expression.Trim()) + ", ");
                    if (!string.IsNullOrWhiteSpace(measure.DisplayName))
                    {
                        measures.Append(string.Format("{0} as \"{1}\" ", expression, measure.DisplayName.Trim()) + ", ");
                    }
                    else
                    {
                        measures.Append(string.Format("{0} as \"{1}\"", expression, measure.Expression.Trim()) + ", ");
                    }
                }

                measuresString = measures.ToString();
                if (string.IsNullOrWhiteSpace(measuresString))
                {
                    return(null);
                }
                measuresString = measuresString.Remove(measuresString.LastIndexOf(','), 1);
            }
            var dims = new System.Text.StringBuilder();

            if (null != widgetModel.Dimension && widgetModel.Dimension.Length > 0)
            {
                foreach (var dim in widgetModel.Dimension)
                {
                    if (!widgetModel.TablesKey.Split(',').Contains(dim.TableId.ToString()))
                    {
                        dims.Append(" null as \"" + dim.Name.Trim() + "\",");
                    }
                    else if (widgetModel.IsForTotal)
                    {
                        dims.Append(" null as \"" + dim.Name.Trim() + "\",");
                    }
                    else if (!string.IsNullOrWhiteSpace(dim.Name))
                    {
                        // var expDisplayName = !string.IsNullOrWhiteSpace(measure.DisplayName) ? measure.DisplayName : '[' + measure.Expression + ']';
                        dims.Append(dim.Name.Trim() + " as \"" + dim.Name.Trim() + "\",");
                        //dims.Append(dim.Name.Trim() + ",");
                    }
                }
                dimString = dims.ToString();
                dimString = dimString.Remove(dimString.LastIndexOf(','), 1);
            }
            var selectColumnsString = ((!string.IsNullOrWhiteSpace(dimString)) ? dimString : string.Empty) + ((!string.IsNullOrWhiteSpace(measuresString)) ? ((!string.IsNullOrWhiteSpace(dimString)) ? "," + measuresString : measuresString) : string.Empty);

            widgetModel.AllColumns = selectColumnsString.Split(',');
            //Todo: widgetModel.SqlTableName
            if (widgetModel.Type == "filter")
            {
                //query = query.Select(dimString).Distinct();
                selectQuery = "Select distinct " + dimString;
            }
            else //if (widgetModel.Type == "kpi")
            {
                if (!string.IsNullOrWhiteSpace(selectColumnsString))
                {
                    //query = query.SelectRaw(dimString);
                    //selectQuery = "Select distinct " + selectColumnsString;
                    selectQuery = "Select " + selectColumnsString;
                }

                //if (!string.IsNullOrWhiteSpace(dimString) && !string.IsNullOrWhiteSpace(measuresString))
                //{

                //    //query = query.GroupBy(widgetModel.Dimension.Select(d => d.Name).ToArray<string>());
                //}

                //data = db.Query("employee").SelectRaw(measuresString).Get();
            }

            return(selectQuery);
        }