public static List<DocumentStatus> GetFileStatus_SSC(List<int> fileNums)
{
if (fileNums.Count() == 0)
return new List<DocumentStatus>();
using (var db = new SSCDbContext())
{
SqlParameter p1 = new SqlParameter("@DocumentNumber", string.Join(",", fileNums));
var lstResult = db.Database.SqlQuery<DocumentStatus>("EXEC spGetStatusByDocNumber @DocumentNumber", p1).ToList();
return lstResult;
}
}
private static void UpdateDocRemoveStatus(WsFile wsFile)
{
//For Server 2
using (var db = new S2DbContext())
{
SqlParameter p1 = new SqlParameter("@DocumentNumber", wsFile.Number);
try
{
db.Database.ExecuteSqlCommand("EXEC [S2CLR].spUpdateDRMSRemoveStatus @DocumentNumber", p1);
}
catch(SqlException ex)
{
ex = null;
}
}
//For SSC
using (var db = new SSCDbContext())
{
SqlParameter p1 = new SqlParameter("@DocNumber", wsFile.Number);
SqlParameter p2 = new SqlParameter("@IsRET", wsFile.WsFileType == WsFileType.Ret);
SqlParameter p3 = new SqlParameter("@UserId", AmUtil.GetCurrentUser);
try
{
db.Database.ExecuteSqlCommand("EXEC spRemoveDRMSDocument @DocNumber, @IsRET, @UserId", p1, p2, p3);
}
catch (SqlException ex)
{
ex = null;
}
}
//if (wsFile.IsServer2)
//{
// using (var db = new S2DbContext())
// {
// SqlParameter p1 = new SqlParameter("@DocumentNumber", wsFile.Number);
// db.Database.ExecuteSqlCommand("EXEC [S2CLR].spUpdateDRMSRemoveStatus @DocumentNumber", p1);
// }
//}
//else
//{
// using (var db = new SSCDbContext())
// {
// SqlParameter p1 = new SqlParameter("@DocNumber", wsFile.Number);
// SqlParameter p2 = new SqlParameter("@IsRET", wsFile.WsFileType == WsFileType.Ret);
// SqlParameter p3 = new SqlParameter("@UserId", AmUtil.GetCurrentUser);
// db.Database.ExecuteSqlCommand("EXEC spRemoveDRMSDocument @DocNumber, @IsRET, @UserId", p1, p2, p3);
// }
//}
}
public static void SurveyRequest(WsSurveyModel wsSurveyModel)
{
using (var db = new SSCDbContext())
{
var lstParamName = new List<string>();
var lstSqlParameter = new List<SqlParameter>();
var lstSqlParam = new List<KeyValuePair<string, SqlParameter>>();
foreach (PropertyDescriptor property in TypeDescriptor.GetProperties(wsSurveyModel.GetType()))
{
var altPropName = property.Attributes.OfType<AltPropName>().FirstOrDefault();
if (altPropName != null)
{
lstParamName.Add(altPropName.Name);
lstSqlParameter.Add(new SqlParameter(altPropName.Name, property.GetValue(wsSurveyModel) ?? DBNull.Value));
lstSqlParam.Add(new KeyValuePair<string, SqlParameter>(
altPropName.Name,
new SqlParameter(altPropName.Name, property.GetValue(wsSurveyModel) ?? DBNull.Value)
));
}
}
lstParamName.Add("@ActivityList");
DataTable tblActivities = new DataTable();
tblActivities.Columns.Add("ActivityName", typeof(string));
tblActivities.Columns.Add("WorkbookNum", typeof(int));
tblActivities.Columns.Add("WorkbookName", typeof(string));
tblActivities.Columns.Add("RequiredDate", typeof(DateTime));
if (wsSurveyModel.SurveyRequestType == SurveyRequestType.RF)
{
//Error - More than one RF requested.
tblActivities.Rows.Add(new object[] { wsSurveyModel.SurveyRequestType.ToString(), 1, "", wsSurveyModel.RequiredDate });
}
else if (wsSurveyModel.SurveyRequestType == SurveyRequestType.RET)
{
//Error - Workbook Name cannot be null/blank
int idx = 1;
wsSurveyModel.WorkBooks.Split(',').ToList().ForEach
(x => tblActivities.Rows.Add(new object[] { "PDF", idx++, x, wsSurveyModel.RequiredDate }));
}
var sqlParamActivityList = new SqlParameter("@ActivityList", SqlDbType.Structured);
sqlParamActivityList.Value = tblActivities;
sqlParamActivityList.TypeName = "udtTblSurveyActivityList";
lstSqlParameter.Add(sqlParamActivityList);
lstSqlParam.Add(new KeyValuePair<string, SqlParameter>(
"@ActivityList",
sqlParamActivityList
));
//
lstParamName.Add("@Comments");
//Comments
//SequenceNumber
DataTable tblComments = new DataTable();
tblComments.Columns.Add("SequenceNumber", typeof(int));
tblComments.Columns.Add("Comments", typeof(string));
if (wsSurveyModel.SurveyRequestType == SurveyRequestType.RF)
{
if (wsSurveyModel.IsRFInDiffWF.ToBool<char>())
{
tblComments.Rows.Add(new object[] { 1, "IsRFInDiffWF:CHANGE OF WORKFLOW – ADD “FOR GDC USE” IN THE FILE NAME" });
}
if (wsSurveyModel.IsPartilaRF.ToBool<char>())
{
tblComments.Rows.Add(new object[] { 2, "IsPartilaRF:PARTIAL RF – [" + wsSurveyModel.RFModificationType + "]" });
}
if (wsSurveyModel.IsSawEng)
{
tblComments.Rows.Add(new object[] { 3, "IsSawEng:SAW Engagement – Yes" });
}
else
{
tblComments.Rows.Add(new object[] { 3, "IsSawEng:SAW Engagement – No" });
}
}
var sqlParamComments = new SqlParameter("@Comments", SqlDbType.Structured);
sqlParamComments.Value = tblComments;
sqlParamComments.TypeName = "udtTblSurveyComments";
lstSqlParameter.Add(sqlParamComments);
lstSqlParam.Add(new KeyValuePair<string, SqlParameter>(
"@Comments",
sqlParamComments
));
//
//SELECT @NSTID = ISNULL(MAX(SurveyRowId) + 1, '1') FROM Survey (NOLOCK)
//INSERT INTO SurveyImport
//SELECT @SurveyRowId = @@IDENTITY
//SELECT @ActvityId = ActivityId FROM Activities WHERE ActivityType = 'SURVEYIMPORT' AND Enabled = 'Y';
//SELECT @EmailActvityId = ActivityId FROM Activities WHERE ActivityType = 'SURVEYPDFEMAIL' AND Enabled = 'Y';
//SELECT @EmailActvityId = ActivityId FROM Activities WHERE ActivityType = 'SURVEYRFEMAIL' AND Enabled = 'Y';
//INSERT INTO FileActivities --Insert SURVEYIMPORT Activity for the Survey Records
//INSERT INTO FileActivities --Insert SURVEY RF/PDF EMAIL Activity for the Survey Record
//INSERT INTO Survey
//INSERT INTO SurveyImportHistory
//DELETE FROM SurveyImport
//RET--
//INSERT INTO SurveyWorkbooks -- Check if WoorkName exists for SurveyId - and increment the WB number
//Exec spProcessSIActivity @SurveyRowId, @SurveyRowId -- Crazy
//db.Database.ExecuteSqlCommand("EXEC spRequestSurvey " + string.Join(",", lstParamName), lstSqlParameter.ToArray());
//db.Database.ExecuteSqlCommand("EXEC uspRequestSurvey " + string.Join(",", lstParamName), lstSqlParameter.ToArray());
db.Database.ExecuteSqlCommand("EXEC uspRequestSurvey " + string.Join(",", lstSqlParam.Select(x => x.Key)), lstSqlParam.Select(x => x.Value).ToArray());
}
}
//if (retDocNum == 0)
// {
// //SELECT CAST(FA.FileNumber AS FLOAT) EngDocNum, CAST(DRMS.DocNumber AS FLOAT) RetDocNum FROM dbo.FileActivities FA (NOLOCK)
// //JOIN DRMSPDFs DRMS (NOLOCK) ON FA.FAId = DRMS.FAId WHERE DRMS.FAId = 457847
// var result = db.Database.SqlQuery<AuditNonAuditModel>(
// "SELECT CAST(FA.FileNumber AS FLOAT) EngDocNum, CAST(DRMS.DocNumber AS FLOAT) RetDocNum " +
// "FROM dbo.FileActivities FA (NOLOCK) " +
// "JOIN DRMSPDFs DRMS (NOLOCK) " +
// "ON FA.FAId = DRMS.FAId " +
// "WHERE DRMS.FAId = @FAID",
// p2
// ).ToList();
// return result;
// }
// else
// {
// //SELECT CAST(FA.FileNumber AS FLOAT) EngDocNum FROM dbo.FileActivities FA (NOLOCK)
// //JOIN DRMSPDFs DRMS (NOLOCK) ON FA.FAId = DRMS.FAId WHERE DRMS.DocNumber = 488270
// var result = db.Database.SqlQuery<AuditNonAuditModel>(
// "SELECT CAST(FA.FileNumber AS FLOAT) EngDocNum FROM dbo.FileActivities FA (NOLOCK) " +
// "JOIN DRMSPDFs DRMS (NOLOCK) " +
// "ON FA.FAId = DRMS.FAId " +
// "WHERE DRMS.DocNumber = @DocNum",
// p1
// ).ToList();
// return result;
// }
public static List<double?> GetRelated_SSC_YrEnd_Audit_EngDoc_Num(double retFileNum)
{
using (var db = new SSCDbContext())
{
var result = db.FileActivity.Join(db.DRMSPDF.Where(x => x.DocNumber == retFileNum), t1 => t1.FAId, t2 => t2.FAId, (t1, t2) => t1.FileNumber).ToList();
return result;
}
}
private static List<FileActivityModel> GetActivity_SSC(DateTime? fDate, DateTime tDate, ActivityFilterType activityFilterType, string engNums)
{
using (var db = new SSCDbContext())
{
SqlParameter p1;
if (activityFilterType == ActivityFilterType.ALL)
{
p1 = new SqlParameter("@ActivityType", DBNull.Value);
}
else
{
p1 = new SqlParameter("@ActivityType", activityFilterType == ActivityFilterType.RET ? activityFilterType.ToEnumDesc() : activityFilterType.ToString());
}
SqlParameter p2 = new SqlParameter("@EngagementNumber", engNums);
SqlParameter p3 = new SqlParameter("@From_Dt", fDate.GetValueOrDefault().ToUTCAdjustment());
SqlParameter p4 = new SqlParameter("@To_Dt", tDate.ToUTCAdjustment());
var result = db.Database.SqlQuery<FileActivityModel>("EXEC spGetAllActivitiesByEngagements @ActivityType, @EngagementNumber, @From_Dt, @To_Dt",
p1, p2, p3, p4).ToList();
return result;
}
}
public static string UpdateFileActivity(FileActivity_UpdateModel activityUpdateModel)
{
string returnStatus = null;
if (activityUpdateModel.FileIn == FileIn.S2)
{
using (var db = new S2DbContext())
{
if (activityUpdateModel.WsActivityType == WsActivityType.Activity_Reprocess)
{
WsActivity.CallEmailActivities("RepreocessServer2RETFile", "ReprocessRequested_14",
activityUpdateModel.FileUniqueId, 0, activityUpdateModel.Comment);
}
if (activityUpdateModel.WsActivityType == WsActivityType.Activity_Acknowledge && activityUpdateModel.NonAuditFlag.HasValue
? activityUpdateModel.NonAuditFlag.Value : false)
{
var relatedEngFiles = WsOperation.GetRelated_S2_YrEnd_Audit_EngDoc_Num(activityUpdateModel.FileNum);
relatedEngFiles.ForEach(x => Workspace.Move_YrEnd_Audit_RET_N_ENG_To_ElecWp(activityUpdateModel.FileNum, x, activityUpdateModel.EngNum));
}
SqlParameter p1 = new SqlParameter("@WorkbookReviewId", activityUpdateModel.FileUniqueId);
SqlParameter p2 = new SqlParameter("@Status", (activityUpdateModel.WsActivityType == WsActivityType.Activity_Acknowledge)
? "Acknowledged" : "Removed");
SqlParameter p3 = new SqlParameter("@UpdatedBy", string.IsNullOrWhiteSpace(activityUpdateModel.logAs) ? AmUtil.GetCurrentUser : activityUpdateModel.logAs);
SqlParameter p4;
if (activityUpdateModel.NonAuditFlag.HasValue)
{ p4 = new SqlParameter("@NonAuditFlag", activityUpdateModel.NonAuditFlag.Value); }
else
{ p4 = new SqlParameter("@NonAuditFlag", DBNull.Value); }
SqlParameter p5 = new SqlParameter("@docNum", activityUpdateModel.FileNum);
db.Database.ExecuteSqlCommand("EXEC [S2CLR].spUpdateReviewStatus @WorkbookReviewId, @Status, @UpdatedBy, @NonAuditFlag, @docNum", p1, p2, p3, p4, p5);
}
var docStatus = AuditManager.Rep.Workspace.GetFileStatus_S2(new List<int> { (int)activityUpdateModel.FileNum });
returnStatus = docStatus.FirstOrDefault().Status;
}
else if (activityUpdateModel.FileIn == FileIn.SSC)
{
if (activityUpdateModel.WsActivityType == WsActivityType.Activity_Acknowledge
|| activityUpdateModel.WsActivityType == WsActivityType.Activity_Reprocess)
{
using (var db = new SSCDbContext())
{
SqlParameter p1 = new SqlParameter("@FAId", activityUpdateModel.FileUniqueId);
SqlParameter p2 = new SqlParameter("@success", (activityUpdateModel.WsActivityType == WsActivityType.Activity_Acknowledge));
SqlParameter p3;
if (string.IsNullOrWhiteSpace(activityUpdateModel.Comment))
{ p3 = new SqlParameter("@comments", DBNull.Value); }
else
{ p3 = new SqlParameter("@comments", activityUpdateModel.Comment); }
SqlParameter p4 = new SqlParameter("@activityEndDate", DateTime.Now.ToUniversalTime());
SqlParameter p5 = new SqlParameter("@userId", string.IsNullOrWhiteSpace(activityUpdateModel.logAs) ? AmUtil.GetCurrentUser : activityUpdateModel.logAs);
SqlParameter p6 = new SqlParameter("@docNum", activityUpdateModel.FileNum);
db.Database.ExecuteSqlCommand("spCloseActivity @FAId, @success, @comments, @activityEndDate, @userId", p1, p2, p3, p4, p5);
}
}
else if (activityUpdateModel.WsActivityType == WsActivityType.Activity_Remove)
{
using (var db = new SSCDbContext())
{
SqlParameter p1 = new SqlParameter("@FAId", activityUpdateModel.FileUniqueId);
SqlParameter p2;
if (string.IsNullOrWhiteSpace(activityUpdateModel.Comment))
{ p2 = new SqlParameter("@comments", DBNull.Value); }
else
{ p2 = new SqlParameter("@comments", activityUpdateModel.Comment); }
SqlParameter p3 = new SqlParameter("@userId", string.IsNullOrWhiteSpace(activityUpdateModel.logAs) ? AmUtil.GetCurrentUser : activityUpdateModel.logAs);
SqlParameter p4 = new SqlParameter("@docNum", activityUpdateModel.FileNum);
db.Database.ExecuteSqlCommand("spRemoveActivity @FAId, @comments, @userId", p1, p2, p3);
}
}
var docStatus = AuditManager.Rep.Workspace.GetFileStatus_SSC(new List<int> { (int)activityUpdateModel.FileNum });
returnStatus = docStatus.FirstOrDefault().Status;
}
IM.Mgr.WsUtility.SaveActivityInfo(activityUpdateModel, returnStatus);
return returnStatus;
}