/// <summary>
        ///     Gets the data from reporting DB.
        /// </summary>
        /// <param name="spWeb">The sp web.</param>
        /// <param name="data">The data.</param>
        /// <returns></returns>
        private static IEnumerable <DataTable> GetDataFromReportingDb(
            SPWeb spWeb,
            string data)
        {
            Guard.ArgumentIsNotNull(spWeb, nameof(spWeb));

            SqlDateTime dateTimeFrom;
            SqlDateTime dateTimeTo;
            bool        getCompletedItems;
            var         listIdQuery = ListIdQuery(data, out dateTimeFrom, out dateTimeTo, out getCompletedItems);

            Guid myWorkListId;
            var  theWeb    = spWeb;
            var  lockedWeb = CoreFunctions.getLockedWeb(theWeb);

            using (var configWeb = Utils.GetConfigWeb(theWeb, lockedWeb))
            {
                myWorkListId = configWeb.Lists[MyWorkText].ID;
            }

            var       tables = new List <DataTable>();
            DataTable myWorkDataTable;
            DataTable fieldsTable;
            DataTable flagsTable;
            var       siteId = spWeb.Site.ID;

            using (var myWorkReportData = new MyWorkReportData(siteId))
            {
                MapCompleteField(spWeb, myWorkReportData);

                string sql;
                var    currentUser = CurrentUser(spWeb, listIdQuery, getCompletedItems, siteId, dateTimeFrom, dateTimeTo, out sql);

                IEnumerable <string>    columns;
                IList <string>          lists;
                IEnumerable <DataTable> enumerable = null;

                if (ProcessDataTables(myWorkReportData, sql, tables, myWorkListId, out myWorkDataTable, out columns, out lists, ref enumerable))
                {
                    return(enumerable);
                }

                sql =
                    $@"SELECT ColumnName, InternalName, SharePointType, RPTListId AS ListId 
                                 FROM dbo.RPTColumn WHERE (ColumnName IN ({string.Join(Comma, columns.ToArray())})) AND (RPTListId IN ({string.Join(Comma, lists.ToArray())}))";
                fieldsTable = myWorkReportData.ExecuteSql(sql);

                sql =
                    $@"SELECT ListId, ItemId, Value FROM dbo.PERSONALIZATIONS WHERE [Key] = 'Flag' AND UserId = N'{currentUser.LoginName.ToSqlCompliant()}'";
                flagsTable            = myWorkReportData.ExecuteEpmLiveSql(sql);
                flagsTable.PrimaryKey = new[] { flagsTable.Columns[ListId], flagsTable.Columns[ItemId] };
            }

            AddTables(spWeb, myWorkDataTable, fieldsTable, flagsTable, tables);

            return(tables);
        }
        public void ExecuteSql_ReturnsDataTable()
        {
            // Act
            _testObj.ExecuteSql(Sql);

            // Assert
            Assert.IsTrue(_adoShims.IsCommandCreated(Sql));
            Assert.IsTrue(_adoShims.IsCommandExecuted(Sql));
            Assert.IsTrue(_adoShims.IsDataReaderCreatedForCommand(Sql));
            Assert.IsTrue(_adoShims.IsDataReaderDisposedForCommand(Sql));
        }
Пример #3
0
        /// <summary>
        ///     Maps the complete field.
        /// </summary>
        /// <param name="spWeb">The sp web.</param>
        /// <param name="myWorkReportData">My work report data.</param>
        private static void MapCompleteField(SPWeb spWeb, MyWorkReportData myWorkReportData)
        {
            Guard.ArgumentIsNotNull(spWeb, nameof(spWeb));
            Guard.ArgumentIsNotNull(myWorkReportData, nameof(myWorkReportData));

            using (var spSite = new SPSite(spWeb.Site.ID))
            {
                using (var web = spSite.OpenWeb(CoreFunctions.getLockedWeb(spWeb)))
                {
                    bool added;
                    bool.TryParse(CoreFunctions.getConfigSetting(web, EpmLiveMyWorkCompleteFieldAdded), out added);

                    if (added)
                    {
                        return;
                    }

                    var query =
                        $@"SELECT DISTINCT   dbo.RPTList.RPTListId AS ListId, dbo.RPTColumn.InternalName AS Col 
                                     FROM       dbo.RPTList INNER JOIN dbo.RPTColumn ON dbo.RPTList.RPTListId = dbo.RPTColumn.RPTListId
                                     WHERE      (dbo.RPTList.ListName = N'My Work') AND (dbo.RPTList.SiteId = '{spWeb.Site.ID}')";

                    var table = myWorkReportData.ExecuteSql(query);
                    table.PrimaryKey = new[] { table.Columns[ListId], table.Columns[Col] };

                    var listId = table.Rows[0][ListId];

                    var row = table.Rows.Find(new[] { listId, Complete });

                    if (row == null)
                    {
                        try
                        {
                            var columns = new List <ColumnDef>
                            {
                                new ColumnDef(Complete, Complete, Complete, SPFieldType.Boolean, SqlDbType.Bit)
                            };

                            myWorkReportData.InsertListColumns((Guid)listId, columns);
                            myWorkReportData.AddColumns(LstMyWork, columns);
                        }
                        catch (Exception exception)
                        {
                            Trace.WriteLine(exception);
                            throw new APIException(2014, UnableToMapReportingDatabase);
                        }
                    }

                    CoreFunctions.setConfigSetting(web, EpmLiveMyWorkCompleteFieldAdded, true.ToString());
                }
            }
        }
Пример #4
0
        private Dictionary <string, object> GetItemFieldValueFromDB(string listId, string itemId)
        {
            var       res = new Dictionary <string, object>();
            DataTable dt  =
                _myWorkReportData.ExecuteSql("SELECT * FROM LSTMyWork WHERE [ListId] = '" + listId + "' AND [ItemId] = " +
                                             itemId + " AND [AssignedToID] != -99");

            try
            {
                res.Add("AssignedToIDs", dt.Rows[0]["AssignedToID"]);
                res.Add("Work", dt.Rows[0]["Work"]);
                res.Add("StartDate", dt.Rows[0]["StartDate"]);
                res.Add("DueDate", dt.Rows[0]["DueDate"]);
            }
            catch { }

            if (dt.Rows.Count <= 1)
            {
                return(res);
            }

            string sIds = dt.Rows.Cast <DataRow>().Aggregate("", (current, r) => current + (r["AssignedToID"] + ","));

            res["AssignedToIDs"] = sIds;

            double sum = 0;

            foreach (DataRow r in dt.Rows)
            {
                double n = 0;
                try
                {
                    n = Convert.ToDouble(r["Work"].ToString());
                }
                catch { }
                sum += n;
            }

            res["Work"] = sum.ToString(CultureInfo.InvariantCulture);

            return(res);
        }
        private static bool ProcessDataTables(
            MyWorkReportData myWorkReportData,
            string sql,
            IList <DataTable> tables,
            Guid myWorkListId,
            out DataTable myWorkDataTable,
            out IEnumerable <string> columns,
            out IList <string> lists,
            ref IEnumerable <DataTable> enumerable)
        {
            Guard.ArgumentIsNotNull(myWorkReportData, nameof(myWorkReportData));
            Guard.ArgumentIsNotNull(tables, nameof(tables));

            var dataTable = myWorkReportData.ExecuteSql(sql);

            myWorkDataTable = new DataTable();

            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                myWorkDataTable.Columns.Add(dataColumn.ColumnName, dataColumn.DataType);
            }

            IEnumerable <DataTable> dataTables = null;

            if (DataFromReportingDb(dataTable, myWorkDataTable, tables, ref dataTables))
            {
                {
                    enumerable = dataTables;
                    columns    = null;
                    lists      = null;
                    return(true);
                }
            }

            columns = from DataColumn dataColumn in myWorkDataTable.Columns
                      select $@"N'{dataColumn.ColumnName}'";

            lists = new List <string>
            {
                $@"N'{myWorkListId.ToString()
                   .Replace(OpenCurlyBrace, string.Empty)
                   .Replace(ClosedCurlyBrace, string.Empty)}'"
            };

            foreach (DataRow dataRow in myWorkDataTable.Rows)
            {
                var listId = dataRow[ListId].ToString();
                var lstId  = $@"N'{listId}'";

                if (!lists.Contains(lstId))
                {
                    lists.Add(lstId);
                }

                var uniqueId = (listId + dataRow[WebIdText] + dataRow[SiteId]).Md5();

                if (!GetMyWorkParams.WorkTypes.ContainsKey(uniqueId))
                {
                    GetMyWorkParams.WorkTypes.Add(uniqueId, dataRow[WorkType].ToString());
                }
            }

            return(false);
        }