public static MachineConfig AddMachine(string machineName, bool isKpmgOnly, bool isPreservation, string RackNo)
{
using (var db = new SSCDbContext())
{
var mc = new MachineConfig
{
MachineName = machineName,
KPMGOnly = (isKpmgOnly == true) ? "Y" : "N",
Preservation = (isPreservation == true) ? "Y" : "N",
RackNo = RackNo,
CreatedTS = System.DateTime.Now,
CreatedBy = System.Environment.UserDomainName + @"\" + System.Environment.UserName,
ModifiedTS = null,
ModifiedBy = null,
DeletedTS = null,
DeletedBy = null,
IsDeleted = false
};
db.MachineConfigs.Add(mc);
db.SaveChanges();
MachineConfigLog(machineName, null, null, null, isKpmgOnly, isPreservation, RackNo, MachineActionType.Insert);
return mc;
}
}
//uspDeleteMachineProfiles
public static ResultModel DeleteMachine(string machineName)
{
int surveyRowId;
if (IsMachineInProgress(machineName, out surveyRowId))
{
return new ResultModel
{
IsSuccess = false,
Msg = "Unable to delete machine detail. Machine is currently being used by NSTID: " + surveyRowId + ". Machine processing must be completed before any change can be made."
};
}
else
{
using (var db = new SSCDbContext())
{
var original = db.MachineConfigs.Find(machineName);
original.IsDeleted = true;
original.DeletedTS = DateTime.Now;
original.DeletedBy = System.Environment.UserDomainName + @"\" + System.Environment.UserName;
db.SaveChanges();
MachineConfigLog(machineName, original.KPMGOnly.StringToBool(), original.Preservation.StringToBool(), original.RackNo, null, null, null, MachineActionType.Deactivate);
}
return new ResultModel
{
IsSuccess = true,
};
}
}
public static List<SSC.Model.EFModel.MachineConfig> GetMachine()
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (var db = new SSCDbContext())
{
var result = db.MachineConfigs
.Where(x => x.IsDeleted == false)
.ToList();
return result;
}
}
}
public static void GetExtraData(List<FileActivityResult> fileActivityResult)
{
var surveyRowIds = fileActivityResult.Select(x => int.Parse(x.NSTID)).ToList();
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (var db = new SSCDbContext())
{
//var result = db.RollForwardTransactions.Join
// (
// surveyRowIds, a => a.SurveyRowId, b => b,
// (a, b) => new { SurveyRowId = a.SurveyRowId, ProjectCode = a.ProjectCode, DcssServer = a.DcssServer }
// )
// .ToList();
var result = db.RollForwardTransactions.Where(x => surveyRowIds.Contains(x.SurveyRowId))
.ToList();
fileActivityResult.ForEach(x => {
var match = result.Find(y => y.SurveyRowId.Equals(int.Parse(x.NSTID)));
if (match != null)
{
x.ProjectCode = match.ProjectCode;
x.DcssServer = match.DcssServer;
}
else
{
x.ProjectCode = string.Empty;
x.DcssServer = string.Empty;
}
if (string.IsNullOrWhiteSpace(x.MachineName))
{
var nstId = int.Parse(x.NSTID);
var machineName = db.FileActivities.Where(y => y.SurveyRowID.Equals(nstId) && y.MachineName != null && y.MachineName != string.Empty).ToList().FirstOrDefault();
x.MachineName = machineName == null ? string.Empty : machineName.MachineName;
}
});
}
}
}
public static List<SSC.Model.EFModel.RollingComment> GetComment(int surveyRowId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (var db = new SSCDbContext())
{
var result = db.RollingComments
.Where(x => x.SurveyRowID == surveyRowId)
.OrderBy(y => y.SequenceNumber)
.ToList();
return result;
}
}
}
public static List<MachineConfig> GetActiveMachines()
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (var db = new SSCDbContext())
{
var result = db.MachineConfigs
.Where(x => x.IsDeleted == false)
.OrderByDescending(x => x.CreatedTS)
.ToList();
return result;
}
}
}
public static List<SSC.Model.EFModel.RollingComment> AddComment(int surveyRowId, string comment)
{
using (var db = new SSCDbContext())
{
db.RollingComments.Add(new Model.EFModel.RollingComment
{
Comments = comment,
MachineName = string.Format("[{0}]-{1}", System.Environment.UserName, System.Environment.MachineName),
SequenceNumber = db.RollingComments.Count(x => x.SurveyRowID == surveyRowId) + 1,
SurveyRowID = surveyRowId,
TimeStamp = DateTime.UtcNow
});
db.SaveChanges();
return GetComment(surveyRowId);
}
}
public static List<FileActivityResult> GetActivity(ActivityFilter activityFilter)
{
using (var db = new SSCDbContext())
{
SqlParameter p1 = new SqlParameter("@Activity_Type", activityFilter.Activity_Type ?? "BOTH");
SqlParameter p2 = new SqlParameter("@NSTID", (object)activityFilter.NSTID ?? DBNull.Value);
SqlParameter p3 = new SqlParameter("@FAStatus", (object)activityFilter.FAStatus ?? DBNull.Value);
SqlParameter p4 = new SqlParameter("@EngagementNumber", (object)activityFilter.EngagementNumber ?? DBNull.Value);
SqlParameter p5 = new SqlParameter("@RequiredDate", (object)activityFilter.RequiredDate ?? DBNull.Value);
SqlParameter p6 = new SqlParameter("@From_Dt_Range", (object)activityFilter.From_Dt_Range ?? DBNull.Value);
SqlParameter p7 = new SqlParameter("@To_Dt_Range", (object)activityFilter.To_Dt_Range ?? DBNull.Value);
SqlParameter p8 = new SqlParameter("@FirstRecordOnly", (object)activityFilter.FirstRecordOnly ?? DBNull.Value);
SqlParameter p9 = new SqlParameter("@WBNumber", (object)activityFilter.WBNumber ?? DBNull.Value);
SqlParameter p10 = new SqlParameter("@FAId", (object)activityFilter.FAId ?? DBNull.Value);
var result = db.Database.SqlQuery<FileActivityResult>("EXEC uspGetWebAdmin @Activity_Type, @NSTID, @FAStatus, @EngagementNumber, @RequiredDate, @From_Dt_Range, @To_Dt_Range, @FirstRecordOnly, @WBNumber, @FAId"
, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10).ToList();
GetExtraData(result);
return result;
}
}
public static ResultModel UpdateSurvey(int surveyRowId, string requiredDate, DateTime? rfRequiredDate, DateTime? lastUpdate)
{
using (var db = new SSCDbContext())
{
SqlParameter p1 = new SqlParameter("@SurveyRowID", surveyRowId);
SqlParameter p2 = new SqlParameter("@RequestYear", requiredDate);
SqlParameter p3 = new SqlParameter("@RFRequiredDate", (object)rfRequiredDate ?? DBNull.Value);
SqlParameter p4 = new SqlParameter("@LastUpdate", (object)lastUpdate ?? DBNull.Value);
try
{
db.Database.ExecuteSqlCommand("EXEC uspUpdateSurveyRequestYear @SurveyRowID, @RequestYear, @RFRequiredDate, @LastUpdate", p1, p2, p3, p4);
return new ResultModel
{
IsSuccess = true,
CRUDOperationWithReturnValue = new KeyValuePair<CRUDOperation, dynamic>(CRUDOperation.Survey_Update_RequiredDate, requiredDate),
CRUDTimestamp = DateTime.UtcNow,
};
}
catch (SqlException ex)
{
return new ResultModel { IsSuccess = false, Msg = ex.Message };
}
}
}
public static ResultModel ResetSurvey(int surveyRowId, bool sendEmail, DateTime requiredDate)
{
using (var db = new SSCDbContext())
{
SqlParameter p1 = new SqlParameter("@SourceSurveyRowID", surveyRowId);
SqlParameter p2 = new SqlParameter("@SendEmail", sendEmail ? "Y" : "N");
SqlParameter p3 = new SqlParameter("@RequiredDate", requiredDate);
try
{
db.Database.ExecuteSqlCommand("EXEC uspResetSurvey @SourceSurveyRowID, @SendEmail, @RequiredDate", p1, p2, p3);
return new ResultModel
{
IsSuccess = true,
CRUDTimestamp = DateTime.UtcNow,
CRUDOperationWithReturnValue = new KeyValuePair<CRUDOperation, dynamic>(CRUDOperation.Survey_Reset_Survey, "")
};
}
catch (SqlException ex)
{
return new ResultModel { IsSuccess = false, Msg = ex.Message };
}
}
}
public static List<SSC.Model.EFModel.Survey_SP> GetSurvey_New_SP(int surveyRowId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (var db = new SSCDbContext())
{
SqlParameter p1 = new SqlParameter("@SurveyRowID", surveyRowId);
var result = db.Database.SqlQuery<SSC.Model.EFModel.Survey_SP>("EXEC uspGetSurveyInfo @SurveyRowID", p1).ToList();
if (result != null && result.Count > 0)
{
var currentResult = result[0];
foreach (PropertyInfo pinfo in currentResult.GetType().GetProperties())
{
if (pinfo.Name.StartsWith("Workbook", StringComparison.OrdinalIgnoreCase))
{
var pinfoValue = pinfo.GetValue(currentResult);
var pinfoValue2 = pinfoValue == null ? string.Empty : pinfoValue.ToString();
if (!string.IsNullOrWhiteSpace(pinfoValue2))
{
var pinfoIdx = pinfo.Name.Substring("Workbook".Length);
currentResult.SurveyWorkbooks.Add(
new Model.EFModel.SurveyWorkbook
{
WorkbookNum = Convert.ToInt32(pinfoIdx),
WorkbookName = pinfoValue2,
SurveyRowId = currentResult.SurveyRowID,
LastUpdateDate = currentResult.LastUpdate,
}
);
}
//else
//{
// break;
//}
}
else if (pinfo.Name.StartsWith("mmddyyWorkbook", StringComparison.OrdinalIgnoreCase))
{
var pinfoIdx = pinfo.Name.Substring("mmddyyWorkbook".Length);
currentResult.SurveyWorkbooks.Where(x => x.WorkbookNum.Equals(Convert.ToInt32(pinfoIdx))).ToList().ForEach(y => y.RequiredDate = Convert.ToDateTime(pinfo.GetValue(currentResult)));
}
}
}
return result;
}
}
}
public static List<SSC.Model.EFModel.Survey> GetSurvey_New_EF(int surveyRowId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (var db = new SSCDbContext())
{
List<SSC.Model.EFModel.Survey> result = null;
result = db.Surveys
.Where(x => x.SurveyRowID == surveyRowId)
.Include(x => x.SurveyWorkbooks)
.Include(x => x.FileActivities)
.Include(x => x.FileActivities.Select(y => y.Activity))
.ToList();
if (result != null && result.Count() > 0 && result.First().FileActivities != null && result.First().FileActivities.Count() > 0)
{
result.First().IsSurveyDeleted = result.First().FileActivities.First().Deleted.StringToBool();
if (result.First().IsSurveyDeleted)
{
result.First().CanAddNewWorkbooks = false;
}
else
{
var x1 = result.First().FileActivities
.GroupBy(x => new { x.SurveyRowID, x.ActivityId })
.Select(y => y.OrderByDescending(z => z.FAId))
.Select(a => a.First()).Select(x => x.FAId).ToList();
var x2 = result.First().FileActivities
.Where(k => x1.Contains(k.ParentFAId.GetValueOrDefault()))
.GroupBy(x => new { x.SurveyRowID, x.ActivityId })
.Select(y => y.OrderByDescending(z => z.ParentFAId))
.Select(a => a.First()).Select(x => x.ParentFAId).Distinct().ToList();
var x3 = result.First().FileActivities
.Where(k => x2.Contains(k.ParentFAId.GetValueOrDefault()) || !k.ParentFAId.HasValue)
.GroupBy(x => new { x.SurveyRowID, x.ActivityId })
.Select(y => y.OrderByDescending(z => z.FAId))
.Select(a => a.First()).Select(x => new { x.FAId, x.SurveyRowID, x.ActivityId, x.Activity.ActivityName }).ToList();
result.First().CanAddNewWorkbooks = x3.Any(x => x.ActivityName.StartsWith("PDFREVIEW", StringComparison.OrdinalIgnoreCase)) ? false : true;
}
}
return result;
}
}
}
public static ResultModelWithSurveyWorkbook DeleteSurveyWorkbook(int surveyRowId, int workbookNum)
{
using (var db = new SSCDbContext())
{
var original = db.Surveys.Find(surveyRowId);
if (AppConfig.ToUseSP)
{
SqlParameter p1 = new SqlParameter("@SurveyRowId", surveyRowId);
SqlParameter p2 = new SqlParameter("@WorkbookNum", workbookNum);
try
{
db.Database.ExecuteSqlCommand("EXEC uspDeleteWorkbook @SurveyRowID, @WorkbookNum", p1, p2);
return new ResultModelWithSurveyWorkbook
{
IsSuccess = true,
SurveyWorkbooks = original.SurveyWorkbooks.ToList(),
CRUDTimestamp = DateTime.UtcNow,
};
}
catch (SqlException ex)
{
return new ResultModelWithSurveyWorkbook { IsSuccess = false, Msg = ex.Message };
}
}
else
{
original.SurveyWorkbooks.Remove(db.SurveyWorkbooks.Find(surveyRowId, workbookNum));
db.SaveChanges();
}
return new ResultModelWithSurveyWorkbook { IsSuccess = true, SurveyWorkbooks = original.SurveyWorkbooks.ToList() };
}
}
//public static List<SSC.Model.EFModel.Survey> GetSurvey(int surveyRowId, bool toIncludeSurveyWorkbooks = true, bool fromComment = false)
//{
// if (!fromComment && AppConfig.ToUseSP_Survey)
// {
// var result = GetSurvey_SP(surveyRowId);
// var interimSurvey = GetSurvey_EF(surveyRowId, true);
// if (interimSurvey.FirstOrDefault() != null && result.FirstOrDefault() != null)
// {
// result[0].CanAddNewWorkbooks = interimSurvey[0].CanAddNewWorkbooks;
// result[0].IsSurveyDeleted = interimSurvey[0].IsSurveyDeleted;
// }
// return result;
// }
// else
// {
// return GetSurvey_EF(surveyRowId, toIncludeSurveyWorkbooks);
// }
//}
//public static List<SSC.Model.EFModel.Survey> GetSurvey_EF(int surveyRowId, bool toIncludeSurveyWorkbooks = true)
//{
// using (new TransactionScope(
// TransactionScopeOption.Required,
// new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
// {
// using (var db = new SSCDbContext())
// {
// //var result = db.Surveys
// // .Include(x => x.SurveyWorkbooks)
// // .Include(x => x.FileActivities)
// // .Include(x => x.FileActivities.Select(y => y.Activity))
// // .Include(x => x.FileActivities.Select(y => y.FileActivityStatus))
// // .Include(x => x.FileActivities.Select(y => y.File))
// // .Include(x => x.FileActivities.Select(y => y.File.Engagement))
// // .Include(x => x.FileActivities.Select(y => y.File.Engagement.EngagementState1))
// // .Include(x => x.FileActivities.Select(y => y.File.FileStage1))
// // .Where(x => x.SurveyRowID == surveyRowId).ToList();
// List<SSC.Model.EFModel.Survey> result = null;
// if (toIncludeSurveyWorkbooks)
// result = db.Surveys
// .Where(x => x.SurveyRowID == surveyRowId)
// .Include(x => x.SurveyWorkbooks)
// .Include(x => x.FileActivities)
// .Include(x => x.FileActivities.Select(y => y.Activity))
// //.Where(x => x.SurveyRowID == surveyRowId)
// .ToList();
// else
// result = db.Surveys
// .Where(x => x.SurveyRowID == surveyRowId).ToList();
// //result = db.Surveys
// // .Where(x => x.SurveyRowID == surveyRowId)
// // .Include(x => x.SurveyWorkbooks)
// // .Include(x => x.FileActivities.GroupBy(y => new { y.SurveyRowID, y.ActivityId }).Select(z => z.OrderByDescending(y => y.FAId)))
// // .Include(x => x.FileActivities.Select(y => y.Activity))
// // .ToList();
// //var a0 = result.First().FileActivities.GroupBy(x => new { x.SurveyRowID, x.ActivityId }).Select(z => new { zk = z.Key, zd = z }).ToList();
// //var a1 = result.First().FileActivities.GroupBy(x => new { x.SurveyRowID, x.ActivityId }).Select(y => y.OrderByDescending(z => z.FAId)).ToList();
// //var a2 = result.First().FileActivities.GroupBy(x => new { x.SurveyRowID, x.ActivityId }).Select(y => y.OrderByDescending(z => z.FAId)).Select(a => a.First()).ToList();
// //var a3 = result.First().FileActivities.GroupBy(x => new { x.SurveyRowID, x.ActivityId }).Select(y => y.OrderByDescending(z => z.FAId))
// // .Select(a => a.First()).Select(b => b.Activity.ActivityName)
// // .ToList();
// //var a4 = result.First().FileActivities.GroupBy(x => new { x.SurveyRowID, x.ActivityId }).Select(y => y.OrderByDescending(z => z.FAId))
// // .Select(a => a.First())
// // //.Select(b => b.Activity.ActivityName)
// // .Any(b => b.Activity.ActivityName.StartsWith("PDFREVIEW", StringComparison.OrdinalIgnoreCase));
// //var x1 = result.First().FileActivities
// // .GroupBy(x => new { x.SurveyRowID, x.ActivityId })
// // .Select(y => y.OrderByDescending(z => z.FAId))
// // .Select(a => a.First())
// // .Any(b => b.Activity.ActivityName.StartsWith("PDFREVIEW", StringComparison.OrdinalIgnoreCase)).ToString().StringToBool();
// if (result != null && result.Count() > 0 && result.First().FileActivities != null && result.First().FileActivities.Count() > 0)
// {
// result.First().IsSurveyDeleted = result.First().FileActivities.First().Deleted.StringToBool();
// if (result.First().IsSurveyDeleted)
// {
// result.First().CanAddNewWorkbooks = false;
// }
// else
// {
// var x1 = result.First().FileActivities
// .GroupBy(x => new { x.SurveyRowID, x.ActivityId })
// .Select(y => y.OrderByDescending(z => z.FAId))
// .Select(a => a.First()).Select(x => x.FAId).ToList();
// var x2 = result.First().FileActivities
// .Where(k => x1.Contains(k.ParentFAId.GetValueOrDefault()))
// .GroupBy(x => new { x.SurveyRowID, x.ActivityId })
// .Select(y => y.OrderByDescending(z => z.ParentFAId))
// .Select(a => a.First()).Select(x => x.ParentFAId).Distinct().ToList();
// var x3 = result.First().FileActivities
// .Where(k => x2.Contains(k.ParentFAId.GetValueOrDefault()) || !k.ParentFAId.HasValue)
// .GroupBy(x => new { x.SurveyRowID, x.ActivityId })
// .Select(y => y.OrderByDescending(z => z.FAId))
// .Select(a => a.First()).Select(x => new { x.FAId, x.SurveyRowID, x.ActivityId, x.Activity.ActivityName }).ToList();
// result.First().CanAddNewWorkbooks = x3.Any(x => x.ActivityName.StartsWith("PDFREVIEW", StringComparison.OrdinalIgnoreCase)) ? false : true;
// }
// //var x1 = result.First().FileActivities
// // .GroupBy(x => new { x.SurveyRowID, x.ActivityId })
// // .Select(y => y.OrderByDescending(z => z.FAId))
// // .Select(a => a.First()).Select(x => x.FAId).ToList();
// //var x2 = result.First().FileActivities
// // .Where(k => x1.Contains(k.ParentFAId.GetValueOrDefault()))
// // .GroupBy(x => new { x.SurveyRowID, x.ActivityId })
// // .Select(y => y.OrderByDescending(z => z.ParentFAId))
// // .Select(a => a.First()).Select(x => x.ParentFAId).Distinct().ToList();
// //var x3 = result.First().FileActivities
// // .Where(k => x2.Contains(k.ParentFAId.GetValueOrDefault()) || !k.ParentFAId.HasValue)
// // .GroupBy(x => new { x.SurveyRowID, x.ActivityId })
// // .Select(y => y.OrderByDescending(z => z.FAId))
// // .Select(a => a.First()).Select(x => new { x.FAId, x.SurveyRowID, x.ActivityId, x.Activity.ActivityName }).ToList();
// //result.First().CanAddNewWorkbooks = result.First().IsSurveyDeleted ? false :
// // x3.Any(x => x.ActivityName.StartsWith("PDFREVIEW", StringComparison.OrdinalIgnoreCase))
// // ? false : true;
// //result.First().CanAddNewWorkbooks = result.First().IsSurveyDeleted ? false :
// // result.First().FileActivities
// // .GroupBy(x => new { x.SurveyRowID, x.ActivityId })
// // .Select(y => y.OrderByDescending(z => z.FAId))
// // .Select(a => a.First())
// // .Any(b => b.Activity.ActivityName.StartsWith("PDFREVIEW", StringComparison.OrdinalIgnoreCase))
// // ? false : true;
// }
// return result;
// }
// }
//}
//public static List<SSC.Model.EFModel.Survey> GetSurvey_SP(int surveyRowId)
//{
// using (new TransactionScope(
// TransactionScopeOption.Required,
// new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
// {
// using (var db = new SSCDbContext())
// {
// SqlParameter p1 = new SqlParameter("@SurveyRowID", surveyRowId);
// var result = db.Database.SqlQuery<SSC.Model.EFModel.Survey>("EXEC uspGetSurveyInfo @SurveyRowID", p1).ToList();
// if (result != null && result.Count > 0)
// {
// var currentResult = result[0];
// foreach (PropertyInfo pinfo in currentResult.GetType().GetProperties())
// {
// if (pinfo.Name.StartsWith("Workbook", StringComparison.OrdinalIgnoreCase))
// {
// var pinfoValue = pinfo.GetValue(currentResult);
// var pinfoValue2 = pinfoValue == null ? string.Empty : pinfoValue.ToString();
// if (!string.IsNullOrWhiteSpace(pinfoValue2))
// {
// var pinfoIdx = pinfo.Name.Substring("Workbook".Length);
// currentResult.SurveyWorkbooks.Add(
// new Model.EFModel.SurveyWorkbook
// {
// WorkbookNum = Convert.ToInt32(pinfoIdx),
// WorkbookName = pinfoValue2,
// SurveyRowId = currentResult.SurveyRowID,
// LastUpdateDate = currentResult.LastUpdate,
// }
// );
// }
// //else
// //{
// // break;
// //}
// }
// else if (pinfo.Name.StartsWith("mmddyyWorkbook", StringComparison.OrdinalIgnoreCase))
// {
// var pinfoIdx = pinfo.Name.Substring("mmddyyWorkbook".Length);
// currentResult.SurveyWorkbooks.Where(x => x.WorkbookNum.Equals(Convert.ToInt32(pinfoIdx))).ToList().ForEach(y => y.RequiredDate = Convert.ToDateTime(pinfo.GetValue(currentResult)));
// }
// }
// }
// return result;
// }
// }
//}
public static ResultModel DeleteSurvey(int surveyRowId, DateTime? lastUpdate)
{
using (var db = new SSCDbContext())
{
SqlParameter p1 = new SqlParameter("@SurveyRowID", surveyRowId);
SqlParameter p2 = new SqlParameter("@LastUpdate", (object)lastUpdate ?? DBNull.Value);
try
{
db.Database.ExecuteSqlCommand("EXEC uspDeleteSurvey @SurveyRowID, @LastUpdate", p1, p2);
return new ResultModel
{
IsSuccess = true,
CRUDTimestamp = DateTime.UtcNow,
};
}
catch (SqlException ex)
{
return new ResultModel { IsSuccess = false, Msg = ex.Message };
}
}
}
//uspUpdateMachineProfiles
public static ResultModel UpdateMachine(string machineName, bool isKpmgOnly, bool isPreservation, string RackNo)
{
int surveyRowId;
if (IsMachineInProgress(machineName, out surveyRowId))
{
return new ResultModel
{
IsSuccess = false,
Msg = "Unable to update machine detail. Machine is currently being used by NSTID: " + surveyRowId + ". Machine processing must be completed before any change can be made."
};
}
else
{
using (var db = new SSCDbContext())
{
var original = db.MachineConfigs.Find(machineName);
var oldKPMGOnly = original.KPMGOnly;
var oldPreservation = original.Preservation;
var oldRackNo = original.RackNo;
original.KPMGOnly = isKpmgOnly ? "Y" : "N";
original.Preservation = isPreservation ? "Y" : "N";
original.RackNo = string.IsNullOrWhiteSpace(RackNo) ? string.Empty : RackNo;
original.ModifiedTS = DateTime.Now;
original.ModifiedBy = System.Environment.UserDomainName + @"\" + System.Environment.UserName;
db.SaveChanges();
MachineConfigLog(machineName, oldKPMGOnly.StringToBool(), oldPreservation.StringToBool(), oldRackNo, isKpmgOnly, isPreservation, RackNo, MachineActionType.Update);
}
return new ResultModel
{
IsSuccess = true,
};
}
}
public static ResultModel UpdateActivity(FileActivityResult fileActivityResult, KeyValuePair<CRUDOperation, dynamic> cRUDOperationWithData)
{
using (var db = new SSCDbContext())
{
SqlParameter p1 = null;
SqlParameter p2 = null;
SqlParameter p3 = null;
SqlParameter p4 = null;
SqlParameter p5 = null;
if (cRUDOperationWithData.Key == CRUDOperation.Activity_Update_MachineName)
{
p1 = new SqlParameter("@SurveyRowID", fileActivityResult.NSTID);
p2 = new SqlParameter("@ActivityId", fileActivityResult.hid_Activity_ID);
p3 = new SqlParameter("@TargetMachine", cRUDOperationWithData.Value ?? DBNull.Value);
p4 = new SqlParameter("@LastUpdateDate", (object)fileActivityResult.hid_SurveyLastUpdateDate ?? DBNull.Value);
}
else
{
p1 = new SqlParameter("@FAId", fileActivityResult.FAId);
p2 = new SqlParameter("@FAStatus", cRUDOperationWithData.Key == CRUDOperation.Activity_Update_Status ? cRUDOperationWithData.Value : fileActivityResult.hid_Status);
p3 = new SqlParameter("@Priority", cRUDOperationWithData.Key == CRUDOperation.Activity_Update_Priority ? cRUDOperationWithData.Value : fileActivityResult.Priority);
p4 = new SqlParameter("@userId", System.Environment.UserName);
p5 = new SqlParameter("@ActivityUpdateDate", (object)fileActivityResult.hid_SurveyLastUpdateDate ?? DBNull.Value);
}
try
{
if (cRUDOperationWithData.Key == CRUDOperation.Activity_Update_MachineName)
{
db.Database.ExecuteSqlCommand("EXEC uspUpdateTargetMachine @SurveyRowID, @ActivityId, @TargetMachine, @LastUpdateDate"
, p1, p2, p3, p4);
}
else
{
db.Database.ExecuteSqlCommand("EXEC uspWebAdminUpdate @FAId, @FAStatus, @Priority, @userId, @ActivityUpdateDate"
, p1, p2, p3, p4, p5);
}
return new ResultModel
{
IsSuccess = true,
CRUDTimestamp = DateTime.UtcNow,
};
}
catch (SqlException ex)
{
return new ResultModel { IsSuccess = false, Msg = ex.Message };
}
}
}
//uspThrashMachineProfiles
public static ResultModel ThrashMachine(string machineName)
{
using (var db = new SSCDbContext())
{
var original = db.MachineConfigs.Find(machineName);
db.MachineConfigs.Remove(original);
db.SaveChanges();
MachineConfigLog(machineName, original.KPMGOnly.StringToBool(), original.Preservation.StringToBool(), original.RackNo, null, null, null, MachineActionType.Delete);
return new ResultModel
{
IsSuccess = true,
};
}
}
private static void MachineConfigLog(string machineName, bool? isKpmgOnlyOld, bool? isPreservationOld, string RackNoOld,
bool? isKpmgOnlyNew, bool? isPreservationNew, string RackNoNew
, MachineActionType machineActionType)
{
using (var db = new SSCDbContext())
{
var mc = new MachineConfigLog
{
MachineName = machineName,
PreservationOld = isPreservationOld.BoolToString("YN"),
KPMGOnlyOld = isKpmgOnlyOld.BoolToString("YN"),
RackNoOld = RackNoOld,
Action = machineActionType.ToString(),
ActionBy = System.Environment.UserDomainName + @"\" + System.Environment.UserName,
ActionTS = System.DateTime.Now,
PreservationNew = isPreservationNew.BoolToString("YN"),
KPMGOnlyNew = isKpmgOnlyNew.BoolToString("YN"),
RackNoNew = RackNoNew
};
db.MachineConfigLogs.Add(mc);
db.SaveChanges();
}
}
private static bool IsMachineInProgress(string machineName, out int surveyRowId)
{
using (var db = new SSCDbContext())
{
var machineInProgress = db.FileActivities.Where(x => x.MachineName.Equals(machineName, StringComparison.OrdinalIgnoreCase) && x.FAStatus.Equals(2)).ToList();
surveyRowId = machineInProgress.Count > 0 ? machineInProgress.FirstOrDefault().SurveyRowID : 0;
return machineInProgress.Count > 0;
}
}
public static ResultModel UpdateSurveyWorkbook(SurveyWorkbook surveyWorkbook, KeyValuePair<CRUDOperation, dynamic> cRUDOperationWithData)
{
using (var db = new SSCDbContext())
{
SqlParameter p1 = new SqlParameter("@SurveyRowID", surveyWorkbook.SurveyRowId);
SqlParameter p2 = new SqlParameter("@WorkbookNum", surveyWorkbook.WorkbookNum);
SqlParameter p3 = new SqlParameter("@WorkbookName", cRUDOperationWithData.Value);
SqlParameter p4 = new SqlParameter("@RequiredDate", (object)surveyWorkbook.RequiredDate ?? DBNull.Value);
SqlParameter p5 = new SqlParameter("@LastUpdate", (object)surveyWorkbook.LastUpdateDate ?? DBNull.Value);
SqlParameter p6 = new SqlParameter("@UserId", System.Environment.UserName);
try
{
db.Database.ExecuteSqlCommand("EXEC uspUpdateWorkbook @SurveyRowID, @WorkbookNum, @WorkbookName, @RequiredDate, @LastUpdate, @UserId", p1, p2, p3, p4, p5, p6);
return new ResultModel
{
IsSuccess = true,
CRUDOperationWithReturnValue = new KeyValuePair<CRUDOperation, dynamic>(CRUDOperation.Survey_Update_WorkbookName, cRUDOperationWithData.Value),
CRUDTimestamp = DateTime.UtcNow,
};
}
catch (SqlException ex)
{
return new ResultModel { IsSuccess = false, Msg = ex.Message };
}
}
}
public static ResultModelWithSurveyWorkbook AddSurveyWorkbook(int surveyRowId, int workbookNum, string workbookName, DateTime requiredDate)
{
using (var db = new SSCDbContext())
{
var original = db.Surveys.Find(surveyRowId);
if (AppConfig.ToUseSP)
{
SqlParameter p1 = new SqlParameter("@SurveyRowId", surveyRowId);
SqlParameter p2 = new SqlParameter("@WorkbookNum", workbookNum);
SqlParameter p3 = new SqlParameter("@WorkbookName", workbookName);
SqlParameter p4 = new SqlParameter("@RequiredDate", requiredDate);
SqlParameter p5 = new SqlParameter("@UserId", System.Environment.UserName);
try
{
db.Database.ExecuteSqlCommand("EXEC uspAddNewWBInExistingSurvey @SurveyRowID, @WorkbookNum, @WorkbookName, @RequiredDate, @UserId", p1, p2, p3, p4, p5);
return new ResultModelWithSurveyWorkbook
{
IsSuccess = true,
SurveyWorkbooks = original.SurveyWorkbooks.ToList(),
CRUDTimestamp = DateTime.UtcNow,
};
}
catch (SqlException ex)
{
return new ResultModelWithSurveyWorkbook { IsSuccess = false, Msg = ex.Message };
}
}
else
{
var result = db.FileActivities
.Where(x => x.SurveyRowID == surveyRowId)
.Include(x => x.Activity)
//.Where(x => x.SurveyRowID == surveyRowId)
.ToList();
if(result.GroupBy(x => new { x.SurveyRowID, x.ActivityId }).Select(y => y.OrderByDescending(z => z.FAId)).Select(a => a.First()).Any(b => b.Activity.ActivityName.StartsWith("PDFREVIEW", StringComparison.OrdinalIgnoreCase)))
{
return new ResultModelWithSurveyWorkbook { IsSuccess = false, Msg = "Workbook can not be added as one of the workbook is already available for the User Acknowledgement", SurveyWorkbooks = original.SurveyWorkbooks.ToList() };
}
else
{
original.SurveyWorkbooks.Add(new SSC.Model.EFModel.SurveyWorkbook
{
SurveyRowId = surveyRowId,
WorkbookNum = workbookNum,
WorkbookName = workbookName,
RequiredDate = requiredDate,
InsertDate = DateTime.UtcNow,
InsertBy = System.Environment.UserName,
LastUpdateDate = DateTime.UtcNow,
LastUpdateBy = System.Environment.UserName
});
db.SaveChanges();
}
}
return new ResultModelWithSurveyWorkbook { IsSuccess = true, SurveyWorkbooks = original.SurveyWorkbooks.ToList() };
}
}
//uspUndeleteMachineProfiles
public static ResultModel UndeleteMachine(string machineName)
{
using (var db = new SSCDbContext())
{
var original = db.MachineConfigs.Find(machineName);
original.IsDeleted = false;
original.DeletedTS = null;
original.DeletedBy = string.Empty;
original.ModifiedTS = DateTime.Now;
original.ModifiedBy = System.Environment.UserDomainName + @"\" + System.Environment.UserName;
db.SaveChanges();
MachineConfigLog(machineName, original.KPMGOnly.StringToBool(), original.Preservation.StringToBool(), original.RackNo, null, null, null, MachineActionType.Activate);
}
return new ResultModel
{
IsSuccess = true,
};
}