protected override void Update(Dictionary <string, string> values)
{
var data = new CustomTimeLogDataModel();
if (values.ContainsKey(CustomTimeLogDataModel.DataColumns.CustomTimeLogId))
{
data.CustomTimeLogId = int.Parse(values[CustomTimeLogDataModel.DataColumns.CustomTimeLogId].ToString());
}
if (values.ContainsKey(CustomTimeLogDataModel.DataColumns.PersonId))
{
data.PersonId = int.Parse(values[CustomTimeLogDataModel.DataColumns.PersonId].ToString());
}
if (values.ContainsKey(CustomTimeLogDataModel.DataColumns.PromotedDate))
{
data.PromotedDate = DateTime.Parse(values[CustomTimeLogDataModel.DataColumns.PromotedDate].ToString());
}
if (values.ContainsKey(CustomTimeLogDataModel.DataColumns.Value))
{
data.Value = decimal.Parse(values[CustomTimeLogDataModel.DataColumns.Value].ToString());
}
CustomTimeLogDataManager.Update(data, SessionVariables.RequestProfile);
InlineEditingList.Data = GetData();
}
public static int Create(CustomTimeLogDataModel data, RequestProfile requestProfile)
{
var sql = Save(data, requestProfile, "Create");
var id = DBDML.RunScalarSQL("CustomTimeLog.Insert", sql, DataStoreKey);
return(Convert.ToInt32(id));
}
//public static DataSet SearchView(CustomTimeLogDataModel data, RequestProfile requestProfile)
//{
// // formulate SQL
// var sql = "EXEC dbo.CustomTimeLogViewSearch " +
// " " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
// ", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, requestProfile.ApplicationId) +
// ", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.CustomTimeLogId) +
// ", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.ExcludeItems) +
// ", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.PersonId) +
// ", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.CustomTimeLogStateId) +
// ", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.FromSearchDate) +
// ", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.ToSearchDate);
// var oDs = new DBDataSet("CustomTimeLog.Search", sql, DataStoreKey);
// return oDs.DBDataset;
//}
#endregion
#region Save
private static string Save(CustomTimeLogDataModel data, RequestProfile requestProfile, string action)
{
var sql = "EXEC ";
switch (action)
{
case "Create":
sql += "dbo.CustomTimeLogInsert " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, data.ApplicationId);
break;
case "Update":
sql += "dbo.CustomTimeLogUpdate " +
" " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
", " + ToSQLParameter(BaseDataModel.BaseDataColumns.ApplicationId, data.ApplicationId); // +
//", " + ToSQLParameter(BaseDataModel.BaseDataColumns.CreatedDate, data.CreatedDate);
break;
default:
break;
}
sql = sql + ", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.CustomTimeLogId) +
", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.PersonId) +
", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.PromotedDate) +
", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.CustomTimeLogKey) +
//", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.StartDate) +
//", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.EndDate) +
", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.Value) +
", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.CustomTimeCategoryId);
return(sql);
}
static public List <CustomTimeLogDataModel> GetEntityDetails(CustomTimeLogDataModel dataQuery, RequestProfile requestProfile, int returnAuditInfo = BaseDataManager.ReturnAuditInfoOnDetails)
{
const string sql = @"dbo.CustomTimeLogSearch ";
var parameters =
new
{
AuditId = requestProfile.AuditId
, ReturnAuditInfo = returnAuditInfo
, ApplicationId = dataQuery.ApplicationId
, CustomTimeLogId = dataQuery.CustomTimeLogId
, CustomTimeCategoryId = dataQuery.CustomTimeCategoryId
, FromSearchDate = dataQuery.FromSearchDate
, ToSearchDate = dataQuery.ToSearchDate
, PersonId = dataQuery.PersonId
, CustomTimeLogKey = dataQuery.CustomTimeLogKey
, PromotedDate = dataQuery.PromotedDate
};
List <CustomTimeLogDataModel> result;
using (var dataAccess = new DataAccessBase(DataStoreKey))
{
result = dataAccess.Connection.Query <CustomTimeLogDataModel>(sql, parameters, commandType: CommandType.StoredProcedure).ToList();
}
return(result);
}
public override int?Save(string action)
{
var data = new CustomTimeLogDataModel();
data.CustomTimeLogId = CustomTimeLogId;
data.CustomTimeLogKey = CustomTimeLogKey;
data.PersonId = PersonId;
data.ApplicationId = ApplicationId;
data.CustomTimeCategoryId = CustomTimeCategoryId;
data.PromotedDate = WorkDate;
data.Value = NoofFilesPromoted;
if (action == "Insert")
{
var dtCustomTimeLog = CustomTimeLogDataManager.DoesExist(data, SessionVariables.RequestProfile);
if (dtCustomTimeLog.Rows.Count == 0)
{
var customTimeLogId = CustomTimeLogDataManager.Create(data, SessionVariables.RequestProfile);
}
else
{
throw new Exception("Record with given ID already exists.");
}
}
else
{
CustomTimeLogDataManager.Update(data, SessionVariables.RequestProfile);
}
return(data.CustomTimeLogId);
}
public static DataTable Search(CustomTimeLogDataModel data, RequestProfile requestProfile)
{
var list = GetEntityDetails(data, requestProfile, 0);
var table = list.ToDataTable();
return(table);
}
public static DataTable GroupByKey(CustomTimeLogDataModel data)
{
var sql = "EXEC dbo.CustomTimeLogGroupByKey " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.CustomTimeLogId);
var oDt = new DBDataTable("CustomTimeLog.GroupByKey", sql, DataStoreKey);
return(oDt.DBTable);
}
public List <ResultSet> GetWorkCategoryDetails(int appUserId)
{
var appUser = new ScheduleDataModel();
appUser.PersonId = appUserId;
var category = string.Empty;
var result = new ArrayList();
var fromDateTime = new DateTime();
var toDateTime = new DateTime();
fromDateTime = Convert.ToDateTime(DateTimeHelper.FromUserDateFormatToApplicationDateFormat(oDateRange.FromDateTime));
toDateTime = Convert.ToDateTime(DateTimeHelper.FromUserDateFormatToApplicationDateFormat(oDateRange.ToDateTime));
var format = SessionVariables.UserDateFormat;
var fromDate = fromDateTime.ToString(format);
var toDate = toDateTime.ToString(format);
var scheduleDetails = new ScheduleDetailDataModel();
appUser.FromSearchDate = Convert.ToDateTime(fromDate);
appUser.ToSearchDate = Convert.ToDateTime(toDate);
var resultList = new List <ResultSet>();
var data = new CustomTimeLogDataModel();
data.PersonId = appUserId;
data.FromSearchDate = Convert.ToDateTime(fromDate);
data.ToSearchDate = Convert.ToDateTime(toDate);
var list = CustomTimeLogDataManager.GetWorkCategoryList(data, SessionVariables.RequestProfile);
var categoryDetails = list[0].Category;
var week = list[0].Week;
var value = list[0].Value;
var weekCount = 0;
for (var i = 0; i < list.Count; i++)
{
var resultSet = new ResultSet("", "", 0);
if (week.Equals(list[i].Week))
{
resultSet.Category = list[i].Category;
resultSet.Value = list[i].Value;
resultSet.Week = list[i].Week.ToString();
weekCount++;
}
else
{
weekCount = 0;
week = list[i].Week;
}
//result.Add(resultSet);
resultList.Add(resultSet);
//result = new ArrayList();
}
return(resultList);
}
protected override DataTable GetEntityData(int?entityKey)
{
var CustomTimeLogdata = new CustomTimeLogDataModel();
CustomTimeLogdata.CustomTimeLogId = entityKey;
var results = CustomTimeLogDataManager.Search(CustomTimeLogdata, SessionVariables.RequestProfile);
return(results);
}
public CustomTimeLogDataModel GetById(string value)
{
var dataQuery = new CustomTimeLogDataModel();
dataQuery.CustomTimeLogId = int.Parse(value);
var result = CustomTimeLogDataManager.GetEntityDetails(dataQuery, SessionVariables.RequestProfile, 1);
return(result[0]);
}
public static bool DoesExist(CustomTimeLogDataModel data, RequestProfile requestProfile)
{
var doesExistRequest = new CustomTimeLogDataModel();
doesExistRequest.CustomTimeLogKey = data.CustomTimeLogKey;
doesExistRequest.ApplicationId = data.ApplicationId;
doesExistRequest.PromotedDate = data.PromotedDate;
doesExistRequest.PersonId = data.PersonId;
var list = GetEntityDetails(doesExistRequest, requestProfile, 0);
return(list.Count > 0);
}
protected override void Clear()
{
base.Clear();
var data = new CustomTimeLogDataModel();
CustomTimeLogId = data.CustomTimeLogId;
CustomTimeLogKey = data.CustomTimeLogKey;
PersonId = data.PersonId;
ApplicationId = data.ApplicationId;
CustomTimeCategory = data.CustomTimeCategory;
NoofFilesPromoted = data.Value;
WorkDate = data.PromotedDate;
}
public static void Delete(CustomTimeLogDataModel dataQuery, RequestProfile requestProfile)
{
const string sql = @"dbo.CustomTimeLogDelete ";
var parameters =
new
{
AuditId = requestProfile.AuditId
,
CustomTimeLogId = dataQuery.CustomTimeLogId
};
using (var dataAccess = new DataAccessBase(DataStoreKey))
{
dataAccess.Connection.Execute(sql, parameters, commandType: CommandType.StoredProcedure);
}
}
public static List <WorkCategoryDataModel> GetWorkCategoryList(CustomTimeLogDataModel data, RequestProfile requestProfile)
{
var list = GetEntityDetails(data, requestProfile);
var result = new List <WorkCategoryDataModel>();
for (var i = 0; i < list.Count; i++)
{
var workData = new WorkCategoryDataModel();
workData.Category = list[i].CustomTimeLogKey;
workData.Week = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(Convert.ToDateTime(list[i].PromotedDate), CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Sunday);
workData.Value = (int)list[i].Value;
result.Add(workData);
}
return(result);
}
public List <BranchRecord> GetBranchSummaryReportData(string value1, string value2, string value3)
{
var applicationUserId = value1;
var fromSearchDate = value2;
var toSearchDate = value3;
var dateFormat = "dd.MM.yyyy";
var customTimeLogData = new CustomTimeLogDataModel();
customTimeLogData.PersonId = int.Parse(applicationUserId);
customTimeLogData.FromSearchDate = DateTime.ParseExact(fromSearchDate, dateFormat, System.Globalization.DateTimeFormatInfo.InvariantInfo);
customTimeLogData.ToSearchDate = DateTime.ParseExact(toSearchDate, dateFormat, System.Globalization.DateTimeFormatInfo.InvariantInfo);
var lstResult = CustomTimeLogDataManager.GetBranchSummaryReportData(customTimeLogData, SessionVariables.RequestProfile);
return(lstResult);
}
public void LoadData(int customTimeLogId, bool showId)
{
// clear UI
Clear();
var dataQuery = new CustomTimeLogDataModel();
dataQuery.CustomTimeLogId = customTimeLogId;
var items = CustomTimeLogDataManager.GetEntityDetails(dataQuery, SessionVariables.RequestProfile);
if (items.Count != 1)
{
return;
}
var item = items[0];
CustomTimeLogId = item.CustomTimeLogId;
CustomTimeLogKey = item.CustomTimeLogKey;
PersonId = item.PersonId;
ApplicationId = item.ApplicationId;
CustomTimeCategory = item.CustomTimeCategory;
WorkDate = item.PromotedDate;
NoofFilesPromoted = item.Value;
if (!showId)
{
txtCustomTimeLogId.Text = item.CustomTimeLogId.ToString();
// only show Audit History in case of Update page, not for Clone.
oHistoryList.Setup((int)SystemEntity.CustomTimeLog, customTimeLogId, "CustomTimeLog");
}
else
{
txtCustomTimeLogId.Text = String.Empty;
}
oUpdateInfo.LoadText(item.UpdatedDate, item.UpdatedBy, item.LastAction);
}
protected override DataTable UpdateData()
{
var UpdatedData = new DataTable();
var data = new CustomTimeLogDataModel();
UpdatedData = CustomTimeLogDataManager.Search(data, SessionVariables.RequestProfile).Clone();
for (var i = 0; i < SelectedData.Rows.Count; i++)
{
data.CustomTimeLogId =
Convert.ToInt32(SelectedData.Rows[i][CustomTimeLogDataModel.DataColumns.CustomTimeLogId].ToString());
data.PersonId = !string.IsNullOrEmpty(CheckAndGetRepeaterTextBoxValue(CustomTimeLogDataModel.DataColumns.PersonId))
? int.Parse(CheckAndGetRepeaterTextBoxValue(CustomTimeLogDataModel.DataColumns.PersonId).ToString())
: int.Parse(SelectedData.Rows[i][CustomTimeLogDataModel.DataColumns.PersonId].ToString());
data.PromotedDate =
!string.IsNullOrEmpty(CheckAndGetRepeaterTextBoxValue(CustomTimeLogDataModel.DataColumns.PromotedDate))
? DateTime.Parse(CheckAndGetRepeaterTextBoxValue(CustomTimeLogDataModel.DataColumns.PromotedDate).ToString())
: DateTime.Parse(SelectedData.Rows[i][CustomTimeLogDataModel.DataColumns.PromotedDate].ToString());
data.Value =
!string.IsNullOrEmpty(CheckAndGetRepeaterTextBoxValue(CustomTimeLogDataModel.DataColumns.Value))
? decimal.Parse(CheckAndGetRepeaterTextBoxValue(CustomTimeLogDataModel.DataColumns.Value).ToString())
: decimal.Parse(SelectedData.Rows[i][CustomTimeLogDataModel.DataColumns.Value].ToString());
CustomTimeLogDataManager.Update(data, SessionVariables.RequestProfile);
data = new CustomTimeLogDataModel();
data.CustomTimeLogId = Convert.ToInt32(SelectedData.Rows[i][CustomTimeLogDataModel.DataColumns.CustomTimeLogId].ToString());
var dt = CustomTimeLogDataManager.Search(data, SessionVariables.RequestProfile);
if (dt.Rows.Count == 1)
{
UpdatedData.ImportRow(dt.Rows[0]);
}
}
return(UpdatedData);
}
protected override DataTable GetData()
{
try
{
SuperKey = ApplicationCommon.GetSuperKey();
SetId = ApplicationCommon.GetSetId();
var selectedrows = new DataTable();
var CustomTimeLogdata = new CustomTimeLogDataModel();
selectedrows = CustomTimeLogDataManager.GetDetails(CustomTimeLogdata, SessionVariables.RequestProfile).Clone();
if (!string.IsNullOrEmpty(SuperKey))
{
var systemEntityTypeId = (int)PrimaryEntity;
var lstEntityKeys = ApplicationCommon.GetSuperKeyDetails(systemEntityTypeId, SuperKey);
foreach (var entityKey in lstEntityKeys)
{
CustomTimeLogdata.CustomTimeLogId = entityKey;
var result = CustomTimeLogDataManager.GetDetails(CustomTimeLogdata, SessionVariables.RequestProfile);
selectedrows.ImportRow(result.Rows[0]);
}
}
else
{
CustomTimeLogdata.CustomTimeLogId = SetId;
var result = CustomTimeLogDataManager.GetDetails(CustomTimeLogdata, SessionVariables.RequestProfile);
selectedrows.ImportRow(result.Rows[0]);
}
return(selectedrows);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
return(null);
}
//#region GetChildren
//private static DataSet GetChildren(CustomTimeLogDataModel data, RequestProfile requestProfile)
//{
// var sql = "EXEC dbo.CustomTimeLogChildrenGet " +
// " " + ToSQLParameter(BaseDataModel.BaseDataColumns.AuditId, requestProfile.AuditId) +
// ", " + ToSQLParameter(data, CustomTimeLogDataModel.DataColumns.CustomTimeLogId);
// var oDt = new DBDataSet("Get Children", sql, DataStoreKey);
// return oDt.DBDataset;
//}
//#endregion
//#region IsDeletable
//public static bool IsDeletable(CustomTimeLogDataModel data, RequestProfile requestProfile)
//{
// var isDeletable = true;
// var ds = GetChildren(data, requestProfile);
// if (ds != null && ds.Tables.Count > 0)
// {
// foreach (DataTable dt in ds.Tables)
// {
// if (dt.Rows.Count > 0)
// {
// isDeletable = false;
// break;
// }
// }
// }
// return isDeletable;
//}
//#endregion
//#region GetStatisticData
//public static decimal GetTotalTimeSpent(DataTable dt, int CustomTimeLogTimeSpentConstant)
//{
// var series = new decimal[dt.Rows.Count];
// var i = 0;
// foreach (DataRow item in dt.Rows)
// {
// var timeSpent = item[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString();
// var timeSpentValue = 0m;
// if (!Decimal.TryParse(timeSpent, out timeSpentValue))
// {
// timeSpentValue = CustomTimeLogTimeSpentConstant;
// }
// series[i++] = timeSpentValue;
// }
// var timeSpentForGroup = series.Sum();
// return timeSpentForGroup;
//}
//public static Statistic GetStatisticData(DataTable CustomTimeLogData, int CustomTimeLogTimeSpentConstant, string CustomTimeLogStatisticUnknown)
//{
// var dataItem = new Statistic();
// // totalTimeSpent1 calculates the time of 'UnKnown' value based on the ReleaseNotesTimeSpentConstant
// //decimal totalTimeSpent1 = CustomTimeLogData
// // .Where(z => (string.IsNullOrEmpty(z[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString()) || z[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() == CustomTimeLogStatisticUnknown))
// // .Count() * CustomTimeLogTimeSpentConstant;
// //// totalTimeSpent2 calculates the time of TimeSpent column <> 'UnKnown'
// //decimal totalTimeSpent2 = CustomTimeLogData
// // .Where(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() != CustomTimeLogStatisticUnknown)
// // .Sum(x => Convert.ToDecimal(x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]));
// //// calculates the count of records whose TimeSpent <> 'UnKnown'
// //var count = CustomTimeLogData
// // .Where(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() != CustomTimeLogStatisticUnknown)
// // .Select(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]).Count();
// //// calculates the total count of records
// //var totalCount = CustomTimeLogData
// // .Select(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]).Count();
// dataItem.Total = GetTotalTimeSpent(CustomTimeLogData, CustomTimeLogTimeSpentConstant);
// //dataItem.Total = totalTimeSpent1 + totalTimeSpent2;
// //dataItem.Count = totalCount;
// var totalCount = CustomTimeLogData.Rows.Count;
// dataItem.Count = totalCount;
// var dt = new DataTable();
// dt.Columns.Add(CustomTimeLogDataModel.DataColumns.TotalHoursWorked);
// dt.AcceptChanges();
// var rowT = dt.NewRow();
// var list = CustomTimeLogData.AsEnumerable()
// .Where(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() != CustomTimeLogStatisticUnknown);
// var list1 = CustomTimeLogData.AsEnumerable()
// .Where(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() == CustomTimeLogStatisticUnknown);
// var rows = from row in list1.AsEnumerable()
// select row;
// // takes care of the logic of TimeSpent column with UnKnown value to calculate average and median
// foreach (var row in rows)
// {
// if (Convert.ToInt16(row[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]) != 0)
// {
// if (row[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() == CustomTimeLogStatisticUnknown)
// {
// rowT = dt.NewRow();
// rowT[CustomTimeLogDataModel.DataColumns.TotalHoursWorked] = CustomTimeLogTimeSpentConstant;
// dt.Rows.Add(rowT);
// }
// }
// }
// var list2 = list.Concat(dt.AsEnumerable());
// var dataRows = list2 as DataRow[] ?? list2.ToArray();
// var rowItem = from row in dataRows.AsEnumerable() select row;
// if (rowItem.Any())
// {
// var excludeZero = dataRows.AsEnumerable().Where(c => c.Field<Decimal>(CustomTimeLogDataModel.DataColumns.TotalHoursWorked) != new Decimal(0.00));
// //calculates the average value
// dataItem.Average = excludeZero
// .Where(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() != CustomTimeLogStatisticUnknown)
// .Select(x => Convert.ToDecimal(x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked])).Average();
// //calculates the max and min values
// dataItem.Max = dataRows.Max(x => Convert.ToDecimal(x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]));
// dataItem.Min = dataRows.Min(x => Convert.ToDecimal(x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]));
// // gets the ordered list to find the median
// var orderedList = dataRows.OrderBy(p => Convert.ToDecimal(p[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]));
// // calculates median for even number list
// if ((totalCount % 2) == 0)
// {
// dataItem.Median = Convert.ToDecimal(orderedList.ElementAt(totalCount / 2)[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]) + Convert.ToDecimal(orderedList.ElementAt((totalCount - 1) / 2)[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]);
// dataItem.Median /= 2;
// }
// else
// {
// // calculating median for odd number list
// if (totalCount == 1)
// {
// dataItem.Median = Convert.ToDecimal(orderedList.ElementAt(totalCount - 1)[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]);
// }
// else
// {
// dataItem.Median = Convert.ToDecimal(orderedList.ElementAt(totalCount / 2)[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]);
// }
// }
// }
// return dataItem;
//}
//public static Dictionary<string, decimal> GetStatisticDataSummary(DataTable CustomTimeLogData, int CustomTimeLogTimeSpentConstant, string CustomTimeLogStatisticUnknown)
//{
// var lstResult = new Dictionary<string, decimal>();
// DataTable dt = new DataTable();
// dt.Clear();
// dt.Columns.Add(CustomTimeLogDataModel.DataColumns.TotalHoursWorked);
// DataRow rowT = dt.NewRow();
// decimal average = 0;
// //var totalTimeSpent1 = CustomTimeLogData
// // .Where(z => (string.IsNullOrEmpty(z[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString()) || z[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() == CustomTimeLogStatisticUnknown)).Count() * CustomTimeLogTimeSpentConstant;
// //// totalTimeSpent2 calculates the time of TimeSpent column <> 'UnKnown'
// //var totalTimeSpent2 = CustomTimeLogData
// // .Where(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() != CustomTimeLogStatisticUnknown)
// // .Sum(x => Convert.ToDecimal(x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]));
// var totalTimeSpent = GetTotalTimeSpent(CustomTimeLogData, CustomTimeLogTimeSpentConstant);
// //lstResult.Add("TotalHoursWorked", totalTimeSpent1 + totalTimeSpent2);
// lstResult.Add("TotalTimeSpent", totalTimeSpent);
// //finding total number o records
// var count = CustomTimeLogData.AsEnumerable()
// .Select(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]).Count();
// lstResult.Add("Count", count);
// if (count != 0)
// average = totalTimeSpent / count;
// //average = (totalTimeSpent1 + totalTimeSpent2) / count;
// var list = CustomTimeLogData.AsEnumerable()
// .Where(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() != CustomTimeLogStatisticUnknown);
// var list1 = CustomTimeLogData.AsEnumerable()
// .Where(x => x[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() == CustomTimeLogStatisticUnknown);
// var rows = from row in list1.AsEnumerable()
// select row;
// foreach (DataRow row in rows)
// {
// if (row[CustomTimeLogDataModel.DataColumns.TotalHoursWorked].ToString().ToLower() == CustomTimeLogStatisticUnknown)
// {
// rowT = dt.NewRow();
// rowT[CustomTimeLogDataModel.DataColumns.TotalHoursWorked] = CustomTimeLogTimeSpentConstant;
// dt.Rows.Add(rowT);
// }
// }
// var listMedian = list.Concat(dt.AsEnumerable());
// var orderedList = listMedian.OrderBy(p => (Decimal)(p[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]));
// decimal median = 0;
// if (count != 0)
// {
// // calculating median for even number list
// if ((count % 2) == 0)
// {
// median = (Decimal)(orderedList.ElementAt(count / 2)[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]) + (Decimal)(orderedList.ElementAt((count - 1) / 2)[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]);
// median /= 2;
// }
// else
// {
// // calculating median for odd number list
// if (count == 1)
// {
// median = (Decimal)(orderedList.ElementAt(count - 1)[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]);
// }
// else
// {
// median = (Decimal)(orderedList.ElementAt(count / 2)[CustomTimeLogDataModel.DataColumns.TotalHoursWorked]);
// }
// }
// }
// lstResult.Add("Average", average);
// lstResult.Add("Median", median);
// return lstResult;
//}
//#endregion
public static List <BranchRecord> GetBranchSummaryReportData(CustomTimeLogDataModel data, RequestProfile requestProfile)
{
var lstResult = new List <BranchRecord>();
var columnTotals = new List <int>();
var resultList = GetEntityDetails(data, requestProfile);
// perform Grouping
var resultGrouping = from l in resultList
group l by new
{
Week = CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(l.PromotedDate.Value, CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday),
l.CustomTimeLogKey
}
into gcs
select new
{
gcs.Key.Week,
gcs.Key.CustomTimeLogKey,
Count = gcs.Sum(x => x.Value)
};
var dt = resultGrouping.ToList().ToDataTable();
//currently assuming both will not be null
var startingWeek = CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(data.FromSearchDate.Value, CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday);
var endingWeek = CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(data.ToSearchDate.Value, CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday);
var lstWeeks = new List <string>();
// case of multiple year
var counter = startingWeek;
while (counter != endingWeek)
{
lstWeeks.Add(counter.ToString());
if (startingWeek > endingWeek && counter == 52)
{
counter = 0;
}
counter++;
}
lstWeeks.Add(endingWeek.ToString());
var distBranches = (from row in dt.AsEnumerable()
select row["CustomTimeLogKey"].ToString().Trim())
.Distinct(StringComparer.CurrentCultureIgnoreCase).ToList();
// add header row
var objBranchRecordHeader = new BranchRecord();
objBranchRecordHeader.Name = "Branch";
if (lstWeeks.Count > 0)
{
objBranchRecordHeader.Info = new SpecialData[lstWeeks.Count];
for (int i = 0; i < lstWeeks.Count; i++)
{
var week = lstWeeks[i];
var specialData = new SpecialData();
specialData.WeekNo = week;
objBranchRecordHeader.Info[i] = specialData;
columnTotals.Add(0);
}
}
lstResult.Add(objBranchRecordHeader);
for (int j = 0; j < distBranches.Count; j++)
{
var branch = distBranches[j];
var objBranchRecord = new BranchRecord();
var rowTotal = 0;
objBranchRecord.Name = branch;
if (lstWeeks.Count > 0)
{
objBranchRecord.Info = new SpecialData[lstWeeks.Count];
for (int i = 0; i < lstWeeks.Count; i++)
{
var week = lstWeeks[i];
var specialData = new SpecialData();
specialData.WeekNo = week;
var weekCount = 0;
try
{
weekCount = (from row in dt.AsEnumerable()
where row["CustomTimeLogKey"].ToString() == branch && row["Week"].ToString() == week
select Convert.ToInt32(row["Count"])).FirstOrDefault();
}
catch { }
specialData.Value = weekCount.ToString("0.00");
rowTotal += weekCount;
columnTotals[i] += weekCount;
objBranchRecord.Info[i] = specialData;
}
}
objBranchRecord.Total = rowTotal.ToString("0.00");
lstResult.Add(objBranchRecord);
}
// count grand total
var grandTotal = columnTotals.Sum();
// add total row
var objBranchRecordTotal = new BranchRecord();
objBranchRecordTotal.Name = "Total";
// add Percentage row
var objBranchRecordPercentage = new BranchRecord();
objBranchRecordPercentage.Name = "Percentage";
if (lstWeeks.Count > 0)
{
objBranchRecordTotal.Info = new SpecialData[lstWeeks.Count];
objBranchRecordPercentage.Info = new SpecialData[lstWeeks.Count];
for (int i = 0; i < lstWeeks.Count; i++)
{
var week = lstWeeks[i];
var specialData = new SpecialData();
specialData.WeekNo = week;
specialData.Value = columnTotals[i].ToString("0.00");
objBranchRecordTotal.Info[i] = specialData;
var specialDataPercentage = new SpecialData();
specialDataPercentage.WeekNo = week;
if (grandTotal != 0)
{
specialDataPercentage.Value = String.Format("{0:P2}", (((float)columnTotals[i]) / ((float)grandTotal)));
}
else
{
specialDataPercentage.Value = "0.00%";
}
objBranchRecordPercentage.Info[i] = specialDataPercentage;
}
}
objBranchRecordTotal.Total = grandTotal.ToString("0.00");
objBranchRecordPercentage.Total = grandTotal.ToString("0.00");
lstResult.Add(objBranchRecordTotal);
lstResult.Add(objBranchRecordPercentage);
return(lstResult);
}
public static void Update(CustomTimeLogDataModel data, RequestProfile requestProfile)
{
var sql = Save(data, requestProfile, "Update");
DBDML.RunSQL("CustomTimeLog.Update", sql, DataStoreKey);
}
public static string ToSQLParameter(CustomTimeLogDataModel data, string dataColumnName)
{
var returnValue = "NULL";
switch (dataColumnName)
{
case CustomTimeLogDataModel.DataColumns.CustomTimeLogId:
if (data.CustomTimeLogId != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NUMBER, CustomTimeLogDataModel.DataColumns.CustomTimeLogId, data.CustomTimeLogId);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.CustomTimeLogId);
}
break;
case CustomTimeLogDataModel.DataColumns.PromotedDate:
if (data.PromotedDate != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, CustomTimeLogDataModel.DataColumns.PromotedDate, data.PromotedDate);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.PromotedDate);
}
break;
case CustomTimeLogDataModel.DataColumns.PersonId:
if (data.PersonId != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NUMBER, CustomTimeLogDataModel.DataColumns.PersonId, data.PersonId);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.PersonId);
}
break;
case CustomTimeLogDataModel.DataColumns.CustomTimeCategoryId:
if (data.CustomTimeCategoryId != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NUMBER, CustomTimeLogDataModel.DataColumns.CustomTimeCategoryId, data.CustomTimeCategoryId);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.CustomTimeCategoryId);
}
break;
case CustomTimeLogDataModel.DataColumns.CustomTimeLogKey:
if (data.CustomTimeLogKey != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, CustomTimeLogDataModel.DataColumns.CustomTimeLogKey, data.CustomTimeLogKey);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.CustomTimeLogKey);
}
break;
case CustomTimeLogDataModel.DataColumns.CustomTimeCategory:
if (data.CustomTimeCategory != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, CustomTimeLogDataModel.DataColumns.CustomTimeCategory, data.CustomTimeCategory);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.CustomTimeCategory);
}
break;
case CustomTimeLogDataModel.DataColumns.Value:
if (data.Value != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NUMBER, CustomTimeLogDataModel.DataColumns.Value, data.Value);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.Value);
}
break;
case CustomTimeLogDataModel.DataColumns.FromSearchDate:
if (data.FromSearchDate != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, CustomTimeLogDataModel.DataColumns.FromSearchDate, data.FromSearchDate);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.FromSearchDate);
}
break;
case CustomTimeLogDataModel.DataColumns.ToSearchDate:
if (data.ToSearchDate != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, CustomTimeLogDataModel.DataColumns.ToSearchDate, data.ToSearchDate);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.ToSearchDate);
}
break;
case CustomTimeLogDataModel.DataColumns.Person:
if (data.Person != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, CustomTimeLogDataModel.DataColumns.Person, data.Person);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.Person);
}
break;
case CustomTimeLogDataModel.DataColumns.ApplicationId:
if (data.ApplicationId != null)
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NUMBER, CustomTimeLogDataModel.DataColumns.ApplicationId, data.ApplicationId);
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.ApplicationId);
}
break;
case CustomTimeLogDataModel.DataColumns.Application:
if (!string.IsNullOrEmpty(data.Application))
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_STRING_OR_DATE, CustomTimeLogDataModel.DataColumns.Application, data.Application.Trim());
}
else
{
returnValue = string.Format(SQL_TEMPLATE_PARAMETER_NULL, CustomTimeLogDataModel.DataColumns.Application);
}
break;
default:
returnValue = StandardDataManager.ToSQLParameter(data, dataColumnName);
break;
}
return(returnValue);
}
public static CustomTimeLogDataModel GetDetails(CustomTimeLogDataModel data, RequestProfile requestProfile)
{
var list = GetEntityDetails(data, requestProfile, 1);
return(list.FirstOrDefault());
}
