///////////////////////////////////////////////////////////////////////////////////////////
public static List<DcssServer> DBDCSServer()
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
var result = db.DcssServers.OrderByDescending(x => x.DcssId).ToList();
return result;
}
}
}
}
//spCloseClientActivity
//@FAId int,
//@success bit,
//@comments varchar(max),
//@activityEndDate datetime,
//@userId varchar(50),
//@newReportSuccess bit = 0,
//@newReportComments varchar(max) = NULL
public static void CloseClientActivity(CloseClientActivity closeClientActivity)
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
SqlParameter p1 = new SqlParameter("@FAId", closeClientActivity.FAId);
SqlParameter p2 = new SqlParameter("@success", closeClientActivity.Success);
SqlParameter p3 = new SqlParameter("@comments", closeClientActivity.Comment);
SqlParameter p4 = new SqlParameter("@activityEndDate", closeClientActivity.ActivityEndDate);
SqlParameter p5 = new SqlParameter("@userId", closeClientActivity.UserName);
SqlParameter p6 = new SqlParameter("@newReportSuccess", closeClientActivity.NewReportSuccess);
SqlParameter p7 = new SqlParameter("@newReportComments", closeClientActivity.NewReportComment);
db.Database.ExecuteSqlCommand("EXEC spCloseClientActivity @FAId, @success, @comments, @activityEndDate, @userId, @newReportSuccess, @newReportComments"
, p1, p2, p3, p4, p5, p6, p7);
}
}
}
public static List<RollingComment> AddComment(int surveyRowId, string comment)
{
var userName = AppNEnvConfig.GetUserName;
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
db.RollingComment.Add(new RollingComment
{
Comments = comment,
MachineName = string.Format("[{0}]-{1}", userName, AppNEnvConfig.GetMachineName),
SequenceNumber = db.RollingComment.Count(x => x.SurveyRowID == surveyRowId) + 1,
SurveyRowID = surveyRowId,
TimeStamp = DateTime.UtcNow
});
db.SaveChanges();
return GetComment(surveyRowId);
}
}
}
public static void LogSSCTechActivityInfo(SSCTechInfo sSCTechInfo)
{
var user = AppNEnvConfig.GetUserName;
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
db.SSCTechActivityInfo.Add(new SSCTechActivityInfo
{
Host = string.Format("{0}[{1}]", AppNEnvConfig.GetMachineName, AppNEnvConfig.AppVersion),
User = user,
SurveyId = sSCTechInfo.SurveyId,
FAId = sSCTechInfo.FAId,
SSCTechInfo = sSCTechInfo.Serialize(),
TimeUtc = DateTime.UtcNow
});
db.SaveChanges();
}
}
}
public static void LogError(SSCTechException ex)
{
var user = AppNEnvConfig.GetUserName;
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
db.SSCTechError.Add(new SSCTechError
{
Host = string.Format("{0}[{1}]", AppNEnvConfig.GetMachineName, AppNEnvConfig.AppVersion),
Type = ex.GetType().ToString(),
Message = ex.Message,
User = user,
ErrorXml = ex.Serialize(),
TimeUtc = DateTime.UtcNow
});
db.SaveChanges();
}
}
}
////UpdateCopyTimeByID
//public static void UpdateCopyTimeByID()
//{
//}
public static Survey GetSurvey(int surveyRowID)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
var result = db.Survey
.Where(x => x.SurveyRowID == surveyRowID)
.FirstOrDefault();
return result;
}
}
}
}
public static string GetSSCTechInfo(int id)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
var result = db.SSCTechActivityInfo.Where(x => x.Id == id).FirstOrDefault().SSCTechInfo;
return result;
}
}
}
}
public static RollForwardTransaction GetRollForwardTransaction(bool isRFRequest, int surveyRowId, int? fAId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
if (isRFRequest && fAId.HasValue)
{
SqlParameter p1 = new SqlParameter("@FAId", fAId.Value);
var result0 = db.Database.ExecuteSqlCommand("EXEC uspGetRFTransForTechMachine @FAId", p1);
}
var result = db.RollForwardTransactions
.Where(x => x.SurveyRowId == surveyRowId)
.FirstOrDefault();
return result;
}
}
}
}
public static List<SSCTechError> DBSSCTechError(string host, DateTime? from = null, DateTime? to = null)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
from = (from ?? DateTime.MinValue).ToUniversalTime();
to = ((to ?? DateTime.Now).AddDays(1)).ToUniversalTime();
List<SSCTechError> result = null;
if (string.IsNullOrEmpty(host))
{
result = db.SSCTechError
.Where(x =>
x.TimeUtc >= from && x.TimeUtc <= to
)
.OrderByDescending(x => x.Id).ToList();
}
else
{
var hostName = string.Format("{0}[{1}]", host, AppNEnvConfig.AppVersion);
result = db.SSCTechError
.Where(x =>
x.TimeUtc >= from && x.TimeUtc <= to
&& x.Host.Equals(hostName, StringComparison.OrdinalIgnoreCase)
)
.OrderByDescending(x => x.Id).ToList();
}
return result;
}
}
}
}
//KPMGOnly -
//a. Dbo.uspGetBusUnitGDCUsersByBU
//b. Dbo.uspGetBusUnitGDCUsersByFAId
//c. Dbo. uspGetBusUnitGDCUsersBySurveyRowId
public static List<GDCUser> GetGDCUser(string businessUnit)
{
if (string.IsNullOrWhiteSpace(businessUnit))
return null;
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
var result = db.BusinessUnits
.Where(x => x.BusinessUnitName.Equals(businessUnit, System.StringComparison.OrdinalIgnoreCase))
.Include(y => y.BusinessUnitToGDCUserMaps)
.Select(z => z.BusinessUnitToGDCUserMaps
.Where(a => a.IsActive && a.GDCUser.IsActive)
.Select(b => b.GDCUser)
).FirstOrDefault().ToList();
return result;
}
}
}
}
public static List<DcssServer> GetDCSServer()
{
//INSERT INTO DcssServers VALUES ('USEOMAPN12142', 1, 'AuditManager', GetDate(), null, null)
//INSERT INTO DcssServers VALUES ('USEOMAPN12143', 1, 'AuditManager', GetDate(), null, null)
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
var result = db.DcssServers.Where(x => x.IsActive == true || x.IsActive == null).ToList();
return result;
}
}
}
}
public static List<RollingComment> GetComment(int surveyRowId)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
//var result = db.Survey.Where(x => x.SurveyRowID.Equals(surveyRowId)).Take(1).Include(x => x.RollingComment).ToList().First();
//var comment = db.Survey.Where(x => x.SurveyRowID.Equals(surveyRowId)).Take(1).Select(x =>
// new {
// EngagementNumber = x.EngagementNumber,
// EngagementName = x.EngagementName,
// EngFileName = x.EngFileName,
// GroupOrMultiInfo = x.GroupOrMultiInfo,
// RollingComment = db.RollingComment.Where(y => y.SurveyRowID == surveyRowId).ToList(),
// }
// ).ToList();
var result = db.RollingComment.Where(x => x.SurveyRowID == surveyRowId).OrderByDescending(y => y.SequenceNumber).ToList();
return result;
}
}
}
}
public static void DeleteSSCTechActivityInfo()
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
db.SSCTechActivityInfo.RemoveRange(db.SSCTechActivityInfo);
db.SaveChanges();
}
}
}
public static void DeleteError()
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
db.SSCTechError.RemoveRange(db.SSCTechError);
db.SaveChanges();
}
}
}
//uspUpdateRFTransFromTechMachine
//@SurveyRowId INT,
//@MAFGuid VARCHAR(50),
//@OutputFileLocation VARCHAR(MAX),
//@IsNewRFAvailableInDCSS BIT,
//@DcssServerName VARCHAR(50) = NULL,
//@Comments VARCHAR(MAX) = NULL
public static void UpdateRollForwardTransactions(RequestInfo requestInfo)
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
SqlParameter p1 = new SqlParameter("@SurveyRowId", requestInfo.SSCTechActivity.SurveyRowID);
SqlParameter p2 = new SqlParameter("@MAFGuid", requestInfo.SSCTechActivity.OutputMAFGuid);
SqlParameter p3 = new SqlParameter("@OutputFileLocation", requestInfo.SSCTechActivity.RootUploadKDrivePath);
SqlParameter p4 = new SqlParameter("@IsNewRFAvailableInDCSS", requestInfo.IsDCSServerValidated);
SqlParameter p5 = new SqlParameter("@DcssServerName", requestInfo.SSCTechActivity.DCSServer);
SqlParameter p6 = new SqlParameter("@Comments", "");
db.Database.ExecuteSqlCommand("EXEC uspUpdateRFTransFromTechMachine @SurveyRowId, @MAFGuid, @OutputFileLocation, @IsNewRFAvailableInDCSS, @DcssServerName, @Comments"
, p1, p2, p3, p4, p5, p6);
}
}
}
//spGetCPActivitiesInProgressWB
//@processingMachine varchar(50)
//public static List<CPActivitiesInProgressWB> GetCPActivitiesInProgressWB()
public static List<SSCTechActivity> GetInProgressRequest()
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
SqlParameter p1 = new SqlParameter("@processingMachine", AppNEnvConfig.GetMachineName);
var result = db.Database.SqlQuery<SSCTechActivity>("EXEC spGetCPActivitiesInProgressWB @processingMachine"
, p1).ToList();
return result;
}
}
}
//if(SSCTechImp.CreateInstance().impersonateValidUser(AppNEnvConfig.SqlId, AppNEnvConfig.Domain, AppNEnvConfig.SqlPwd))
//{
//}
//else
//{
// return null;
//}
//spGetNextFileWB
//@processingMachine varchar(50),
//@processingUser varchar(50),
//@fileType varchar(50)
public static List<SSCTechActivity> GetNextRequest(FileType fileType)
{
var userName = AppNEnvConfig.GetUserName;
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
SqlParameter p1 = new SqlParameter("@processingMachine", AppNEnvConfig.GetMachineName);
SqlParameter p2 = new SqlParameter("@processingUser", userName);
SqlParameter p3 = new SqlParameter("@fileType", fileType.ToString());
var result = db.Database.SqlQuery<SSCTechActivity>("EXEC spGetNextFileWB @processingMachine, @processingUser, @fileType"
, p1, p2, p3).ToList();
return result;
}
}
}
public static List<MachineConfig> DBMachineConfig()
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
var result = db.MachineConfig.OrderByDescending(x => x.MachineName).ToList();
return result;
}
}
}
}
//public static List<SSCTechActivityInfo> DBSSCTechActivityInfo()
//{
// using (new TransactionScope(
// TransactionScopeOption.Required,
// new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
// {
// using (SSCTechImp.CreateInstance())
// {
// using (var db = new SSCTechClientDbCntx())
// {
// var result = db.SSCTechActivityInfo.OrderByDescending(x => x.Id).ToList();
// return result;
// }
// }
// }
//}
public static List<SSCTechActivityInfo> DBSSCTechActivityInfo(string engNo, DateTime? from = null, DateTime? to = null)
{
using (new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
using (SSCTechImp.CreateInstance())
{
using (var db = new SSCTechClientDbCntx())
{
from = (from ?? DateTime.MinValue).ToUniversalTime();
to = ((to ?? DateTime.Now).AddDays(1)).ToUniversalTime();
var result = db.SSCTechActivityInfo
.Where(x => x.TimeUtc >= from && x.TimeUtc <= to)
.OrderByDescending(x => x.Id).ToList();
return result;
}
}
}
}