private static bool ValidateIsEngS2(string engNum)
{
using (var db = new S2DbContext())
{
return(db.MasterAuditFile.Where(x => x.EngagementNumber == engNum && x.IsActive).Count() > 0);
}
}
public static bool DeLinkMAF(Post_WsS2GuidModel post_WsS2GuidModel)
{
using (var db = new S2DbContext())
{
var original = db.MasterAuditFile.Find(post_WsS2GuidModel.MasterAuditFileId);
original.IsActive = false;
original.UpdateDate = DateTime.Now;
original.UpdatedBy = AmUtil.GetCurrentUser;
//original.EngagementNumber = null;//DBNull.Value;
db.SaveChanges();
}
WsActivity.CallEmailActivities("UnlinkingRequest", "UnlinkingRequest_10",
"0", post_WsS2GuidModel.MasterAuditFileId,
string.Format("{0}#%1#{1}#%1#{2}", post_WsS2GuidModel.Comment, post_WsS2GuidModel.EngNum, post_WsS2GuidModel.EngName));
var isEngS2 = ValidateIsEngS2(post_WsS2GuidModel.EngNum);
if (!isEngS2)
{
UpdateWs(post_WsS2GuidModel.WsId, false);
}
IM.Mgr.WsUtility.SaveGuidInfo_DeLink(post_WsS2GuidModel.MafGuid, post_WsS2GuidModel.EngNum,
post_WsS2GuidModel.Comment, post_WsS2GuidModel.MasterAuditFileId);
return(isEngS2);
}
public static bool LinkMAF(Post_WsS2GuidModel post_WsS2GuidModel)
{
using (var db = new S2DbContext())
{
SqlParameter p1 = new SqlParameter("@MAFGuid", post_WsS2GuidModel.MafGuid);
SqlParameter p2 = new SqlParameter("@EngagementNumber", post_WsS2GuidModel.EngNum);
SqlParameter p3 = new SqlParameter("@EngagementName", post_WsS2GuidModel.EngName);
SqlParameter p4 = new SqlParameter("@ClientNumber", post_WsS2GuidModel.Client);
SqlParameter p5 = new SqlParameter("@ClientName", post_WsS2GuidModel.ClientDesc);
SqlParameter p6 = new SqlParameter("@UpdatedBy", AmUtil.GetCurrentUser);
SqlParameter p7 = new SqlParameter("@PartnerEmailId", post_WsS2GuidModel.PartnerEmail);
SqlParameter p8 = new SqlParameter("@ManagerEmailId", post_WsS2GuidModel.ManagerEmail);
db.Database.ExecuteSqlCommand("S2CLR.spMapEngagementToGuid @MAFGuid, @EngagementNumber, @EngagementName, @ClientNumber, @ClientName, @UpdatedBy, @PartnerEmailId, @ManagerEmailId",
p1, p2, p3, p4, p5, p6, p7, p8);
}
if (!post_WsS2GuidModel.IsS2)
{
UpdateWs(post_WsS2GuidModel.WsId, true);
}
IM.Mgr.WsUtility.SaveGuidInfo_Link(post_WsS2GuidModel.MafGuid, post_WsS2GuidModel.EngNum);
return(true);
}
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 List <Mapped_WsS2GuidModel> GetMapped_WsS2Guid_ForEng(string engNum)
{
using (var db = new S2DbContext())
{
SqlParameter p1 = new SqlParameter("@EngagementNumber", engNum);
var result = db.Database.SqlQuery <Mapped_WsS2GuidModel>("EXEC S2CLR.spGetServer2Links @EngagementNumber", p1).ToList();
return(result);
}
}
public static List <int> GetRelated_S2_YrEnd_Audit_EngDoc_Num(double retFileNum)
{
using (var db = new S2DbContext())
{
var result = db.DRMSExportedFile.Where(x => x.WorkbookReviewId ==
(db.DRMSExportedFile.Where(y => y.DocumentNumber == retFileNum).Select(z => z.WorkbookReviewId).FirstOrDefault()))
.Where(x2 => x2.DRMSFolderPath == "2 - Period-end Audit >> ENG Files").Select(x3 => x3.DocumentNumber).ToList();
return(result);
}
}
private static string Get_Linked_Ws_ForMafGuid(Guid mafGuid)
{
using (var db = new S2DbContext())
{
var result = db.MasterAuditFile.Where(x => x.MAFGuid == mafGuid.ToString() &&
x.IsActive &&
!(x.EngagementNumber == null || x.EngagementNumber.Trim() == string.Empty))
.Select(y => y.EngagementNumber).FirstOrDefault();
return(result);
}
}
public static List <EngDocByRetModel> GetENGDocByRETDocNumber_2(double retFileNum)
{
using (var db = new S2DbContext())
{
SqlParameter p1 = new SqlParameter("@DocumentNumber", retFileNum);
var result = db.Database.SqlQuery <EngDocByRetModel>("EXEC [S2CLR].spGetENGDocByRETDocNumber @DocumentNumber"
, p1).ToList();
return(result);
}
}
public static List <DocumentStatus> GetFileStatus_S2(List <int> fileNums)
{
if (fileNums.Count() == 0)
{
return(new List <DocumentStatus>());
}
using (var db = new S2DbContext())
{
SqlParameter p1 = new SqlParameter("@DocumentNumber", string.Join(",", fileNums));
var lstResult = db.Database.SqlQuery <DocumentStatus>("EXEC [S2CLR].spGetStatusByDocNumber @DocumentNumber", p1).ToList();
return(lstResult);
}
}
//public static void CallEmailActivities(string activityName, string templateName, int workbookReviewId, int masterAuditFileID, string comment)
public static void CallEmailActivities(string activityName, string templateName, string workbookReviewId, int masterAuditFileID, string comment)
{
using (var db = new S2DbContext())
{
SqlParameter p1 = new SqlParameter("@ActivityName", activityName);
SqlParameter p2 = new SqlParameter("@WorkbookReviewId", workbookReviewId);
SqlParameter p3 = new SqlParameter("@MasterAuditFileID", masterAuditFileID);
SqlParameter p4 = new SqlParameter("@TemplateName", templateName);
SqlParameter p5 = new SqlParameter("@Priority", 1);
SqlParameter p6 = new SqlParameter("@Comments", comment);
SqlParameter p7 = new SqlParameter("@InsertBy", AmUtil.GetCurrentUser);
db.Database.ExecuteSqlCommand("EXEC [S2CLR].spAddEmailActivities @ActivityName, @WorkbookReviewId, @MasterAuditFileID, @TemplateName, @Priority, @Comments, @InsertBy"
, p1, p2, p3, p4, p5, p6, p7);
}
}
private static List <FileActivityModel> GetActivity_S2(DateTime?fDate, DateTime tDate, ActivityFilterType activityFilterType, string engNums)
{
using (var db = new S2DbContext())
{
SqlParameter p1 = new SqlParameter("@ActivityType", DBNull.Value);
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 [S2CLR].spGetS2CActivitiesByEngagements @ActivityType, @EngagementNumber, @From_Dt, @To_Dt",
p1, p2, p3, p4).ToList();
return(result);
}
}
public static dynamic GetS2NonClosedWb(string engNum)
{
using (var db = new S2DbContext())
{
var result = db.MasterAuditFile.Where(x => x.EngagementNumber == engNum)
.Join(db.Workbook, t1 => t1.MasterAuditFileId, t2 => t2.MasterAuditFileId,
(t1, t2) => new { t2.WorkbookId, t2.WorkbookName, t2.StatusId })
.Join(db.Status, t3 => t3.StatusId, t4 => t4.StatusId,
(t3, t4) => new { WorkbookName = t3.WorkbookName, Status = t4.Status1 })
.Where(y => y.Status != "Closed")
.ToList();
var dynamicResult = result.Select(x => x.ToDynamic()).ToList();
return(dynamicResult);
}
}
public static WsFile GetENGDocByRETDocNumber(double retFileNum)
{
using (var db = new S2DbContext())
{
SqlParameter p1 = new SqlParameter("@DocumentNumber", retFileNum);
var result = db.Database.SqlQuery <EngDocByRetModel>("EXEC [S2CLR].spGetENGDocByRETDocNumber @DocumentNumber"
, p1).ToList();
if (result != null && result.Count() > 0)
{
var engDocNum = result.FirstOrDefault().DocumentNumber;
var wsFile = IM.Mgr.WsOperation.GetWsFile(engDocNum);
return(wsFile);
}
else
{
return(null);
}
}
}
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);
}