/// <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));
}
/// <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());
}
}
}
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);
}