public static void SaveErrorLog(AMP_Rules rule, EventInfo evt, Function_Info finfo, Notification_Dep_user dep, int nPrevSnapshotId, int nCurrSnapshotId, string strFunctionName, string logText) { SqlConnection conn = new SqlConnection(strEBMSConn); try { conn.Open(); string strSQL = " insert into AMP_Error_Logs (RuleCode ,RuleName ,SnapshotPreviousId ,SnapshotCurrentId ,EventId ,Function_Id ,DepartmentCode ,DepartmentDesc ,Dep_User_Id ,EmailAddress ,RunDate ,Function_Name ,logMessage) "; strSQL += " values "; strSQL += " (@rulecode, @rulename,@prevsnapshotid, @currsnapshotid,@eventid, @functionid, @deptcode,@deptdesc, @deptuser, @emailaddress, @rundate,@funcname, @errmsg ) "; SqlCommand comm = new SqlCommand(strSQL, conn); comm.Parameters.Add("@rulecode", SqlDbType.VarChar, 20).Value = rule.RuleId; comm.Parameters.Add("@rulename", SqlDbType.VarChar, 255).Value = rule.Rule_Name; comm.Parameters.Add("@prevsnapshotid", SqlDbType.Int).Value = nPrevSnapshotId; comm.Parameters.Add("@currsnapshotid", SqlDbType.Int).Value = nCurrSnapshotId; comm.Parameters.Add("@eventid", SqlDbType.Int).Value = evt.EventId; if (finfo != null) { comm.Parameters.Add("@functionid", SqlDbType.Int).Value = finfo.FuncId; } else { comm.Parameters.Add("@functionid", SqlDbType.Int).Value = 0; } if (dep != null) { comm.Parameters.Add("@deptcode", SqlDbType.VarChar, 20).Value = dep.DepartmentCode; comm.Parameters.Add("@deptdesc", SqlDbType.VarChar, 255).Value = dep.DepartmentDesc; comm.Parameters.Add("@deptuser", SqlDbType.VarChar, 20).Value = dep.UserId; comm.Parameters.Add("@emailaddress", SqlDbType.VarChar, 200).Value = dep.EmailAddress; } else { comm.Parameters.Add("@deptcode", SqlDbType.VarChar, 20).Value = ""; comm.Parameters.Add("@deptdesc", SqlDbType.VarChar, 255).Value = ""; comm.Parameters.Add("@deptuser", SqlDbType.VarChar, 20).Value = ""; comm.Parameters.Add("@emailaddress", SqlDbType.VarChar, 200).Value = ""; } comm.Parameters.Add("@rundate", SqlDbType.DateTime).Value = DateTime.Now; comm.Parameters.Add("@funcname", SqlDbType.VarChar, 50).Value = strFunctionName; comm.Parameters.Add("@errmsg", SqlDbType.NText).Value = logText; comm.CommandTimeout = nCommandTimeOut; comm.ExecuteNonQuery(); } catch (Exception ex) { sendException(ex, "SaveErrorLog", "*****@*****.**", "*****@*****.**", "EBMS Amendment Runtime Error"); } finally { conn.Close(); } }
public static void sendErrorException(string strEmailFrom, string strEmailTo, string strSubject, AMP_Rules rule, EventInfo evt, Function_Info finfo, Notification_Dep_user dep, int nPrevSnapshotId, int nCurrSnapshotId, string strFunctionName, string logText) { SaveErrorLog(rule, evt, finfo, dep, nPrevSnapshotId, nCurrSnapshotId, strFunctionName, logText); string strBody = ""; strBody = "There's error with function " + strFunctionName + ". <br/><br/> "; strBody = "Department: " + dep.DepartmentDesc + " (" + dep.DepartmentCode + ") <br/> "; strBody = "Department User: "******" /" + dep.EmailAddress + "<br/> "; strBody = "Rule: " + rule.Rule_Name + " (" + rule.RuleId + ") <br/> "; strBody = "Snapshot Previous:" + getSnapshotDateTimeFromId(nPrevSnapshotId) + " (" + nPrevSnapshotId.ToString() + ") <br/>"; strBody = "Snapshot Current:" + getSnapshotDateTimeFromId(nCurrSnapshotId) + " (" + nCurrSnapshotId.ToString() + ") <br/>"; strBody = "Event: " + evt.EventDesc + " (" + evt.EventId.ToString() + ") <br/>"; strBody = "Function: " + finfo.FuncDesc + " <br/><br/>"; strBody += "Error Messages:<br/> " + logText; AMP_Notification.sendEmail(strEmailFrom, strEmailTo, strSubject, strBody); }
/// <summary> /// withinseconds =1200 means from the nextrun time till now, if it's within 1200 seconds (20 minutes, still can run) /// </summary> /// <param name="strDeptCode"></param> /// <param name="checktime"></param> /// <param name="withinseconds"></param> /// <returns></returns> public static List <AMP_Rules> getEventRules(string strDeptCode, DateTime checktime, int withinseconds = 1200) { List <AMP_Rules> lstrule = new List <AMP_Rules>(); SqlConnection conn = new SqlConnection(strEBMSConn); try { conn.Open(); string strSQL = "SELECT RuleId, RuleName, RuleType, ShortLeadStatusList, CreatedBy, CreatedDate, Status, TriggerMinutes,EventStatusList, LastRun, NextRun, Noti_Dep_Code, EventNotifyDaysFrom, EventNotifyDaysTo, EventStatusFrom, EventStatusTo, EmailSubject,ShowFuncId, ShowSpaceCode, ShowHierarchyFuncDesc, ShowPackageItemDateTime,ShowFuncSignage, NotesLength "; strSQL += " FROM AMP_New_Rules where Status='A' "; strSQL += " and ruletype='EVENT' and (datediff(second, NextRun, @checkDateTime) >0 or NextRun is null ) and Noti_Dep_Code=@deptcode"; SqlCommand comm = new SqlCommand(strSQL, conn); comm.Parameters.Add("@deptcode", SqlDbType.VarChar, 20).Value = strDeptCode; comm.Parameters.Add("@checkDateTime", SqlDbType.DateTime).Value = checktime; comm.Parameters.Add("@withinseconds", SqlDbType.Int).Value = withinseconds; comm.CommandTimeout = nCommandTimeOut; SqlDataAdapter da = new SqlDataAdapter(comm); DataTable dt = new DataTable(); da.Fill(dt); da.FillSchema(dt, SchemaType.Source); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { AMP_Rules rule = new AMP_Rules(); rule.RuleId = dr["RuleId"].ToString(); rule.Rule_Name = dr["RuleName"].ToString(); rule.Rule_Type = dr["RuleType"].ToString(); rule.ShortLeadStatusList = dr["ShortLeadStatusList"].ToString(); rule.Created_By = dr["CreatedBy"].ToString(); rule.Created_Date = dr["CreatedDate"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dr["CreatedDate"].ToString()); rule.Rule_Status = dr["Status"].ToString(); rule.TriggerMinutes = dr["TriggerMinutes"] == DBNull.Value ? 1 : int.Parse(dr["TriggerMinutes"].ToString()); rule.Next_Run = dr["NextRun"] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr["NextRun"].ToString()); rule.Last_Run = dr["LastRun"] == DBNull.Value ? rule.Next_Run.AddMinutes(-rule.TriggerMinutes) : DateTime.Parse(dr["LastRun"].ToString()); rule.Notify_Dept_Code = dr["Noti_Dep_Code"].ToString(); rule.Notify_EventDay_From = dr["EventNotifyDaysFrom"] == DBNull.Value ? 0 : int.Parse(dr["EventNotifyDaysFrom"].ToString()); rule.Notify_EventDay_To = dr["EventNotifyDaysTo"] == DBNull.Value ? 0 : int.Parse(dr["EventNotifyDaysTo"].ToString()); rule.EventStatusFrom = dr["EventStatusFrom"].ToString(); rule.EventStatusTo = dr["EventStatusTo"].ToString(); string strS = null; rule.EventStatusList = dr["EventStatusList"] == DBNull.Value ? strS : dr["EventStatusList"].ToString(); rule.EmailSubject = dr["EmailSubject"].ToString(); rule.ShowFuncId = false; if (dr["ShowFuncId"] != DBNull.Value) { bool isShowFuncId = false; if (bool.TryParse(dr["ShowFuncId"].ToString(), out isShowFuncId)) { isShowFuncId = bool.Parse(dr["ShowFuncId"].ToString()); if (isShowFuncId) { rule.ShowFuncId = true; } } } rule.ShowSpaceCode = false; if (dr["ShowSpaceCode"] != DBNull.Value) { bool isShowSpaceCode = false; if (bool.TryParse(dr["ShowSpaceCode"].ToString(), out isShowSpaceCode)) { isShowSpaceCode = bool.Parse(dr["ShowSpaceCode"].ToString()); if (isShowSpaceCode) { rule.ShowSpaceCode = true; } } } rule.ShowHierarchyFuncDesc = false; if (dr["ShowHierarchyFuncDesc"] != DBNull.Value) { bool isShowHierarchyFuncDesc = false; if (bool.TryParse(dr["ShowHierarchyFuncDesc"].ToString(), out isShowHierarchyFuncDesc)) { isShowHierarchyFuncDesc = bool.Parse(dr["ShowHierarchyFuncDesc"].ToString()); if (isShowHierarchyFuncDesc) { rule.ShowHierarchyFuncDesc = true; } } } rule.ShowPackageItemDateTime = false; if (dr["ShowPackageItemDateTime"] != DBNull.Value) { bool isShowPackageItemDateTime = false; if (bool.TryParse(dr["ShowPackageItemDateTime"].ToString(), out isShowPackageItemDateTime)) { isShowPackageItemDateTime = bool.Parse(dr["ShowPackageItemDateTime"].ToString()); if (isShowPackageItemDateTime) { rule.ShowPackageItemDateTime = true; } } } rule.ShowFunctionSignageChange = false; if (dr["ShowFuncSignage"] != DBNull.Value) { bool isShowFuncSignage = false; if (bool.TryParse(dr["ShowFuncSignage"].ToString(), out isShowFuncSignage)) { isShowFuncSignage = bool.Parse(dr["ShowFuncSignage"].ToString()); if (isShowFuncSignage) { rule.ShowFunctionSignageChange = true; } } } rule.NotesLength = 200; if (dr["NotesLength"] != DBNull.Value) { int nNotesLength = 200; if (int.TryParse(dr["NotesLength"].ToString(), out nNotesLength)) { nNotesLength = int.Parse(dr["NotesLength"].ToString()); rule.NotesLength = nNotesLength; } } lstrule.Add(rule); } } } catch (Exception ex) { AMP_Common.sendException(ex, System.Reflection.MethodBase.GetCurrentMethod().Name, strEmailFrom, strEmailTo, ex.Message); } finally { conn.Close(); } return(lstrule); }
public static void checkAmendmentManually(string strDepartmentCode, int nPrevSnapshotId, int nCurrSnapshotId, string strEventCode = "All") { Notification_Dep_user dep = AMP_DepartmentDAL.getDepartment(strDepartmentCode); DateTime dtSnapshotCurrent, dtSnapshotPrevious; dtSnapshotPrevious = AMP_Common.getSnapshotDateTimeFromId(nPrevSnapshotId); dtSnapshotCurrent = AMP_Common.getSnapshotDateTimeFromId(nCurrSnapshotId); DateTime dtNow = DateTime.Now; int nTryTimes = 0; bool isSnapshotFinish = false; isSnapshotFinish = AMP_Common.isSnapshotFinished(dtNow); while (!isSnapshotFinish) { //delay for 30 seconds then get the status again. System.Threading.Thread.Sleep(1000 * 30); isSnapshotFinish = AMP_Common.isSnapshotFinished(dtNow); nTryTimes += 1; //if tried for 40 times (20 minutes) still fail, then fail. if (nTryTimes >= 40) { break; } } if (isSnapshotFinish) { AMP_Rules common_rule = AMP_RulesDAL.getCommonEventRule(); AMP_EventDAL.dep = dep; AMP_EventDAL.rule = common_rule; AMP_RulesDAL.rule = common_rule; AMP_FunctionDAL.rule = common_rule; AMP_OrderDAL.rule = common_rule; AMP_EventDAL.dtSnapshotCurrent = dtSnapshotCurrent; AMP_EventDAL.dtSnapshotPrevious = dtSnapshotPrevious; AMP_EventDAL.nSnapshotCurrentID = nCurrSnapshotId; AMP_EventDAL.nSnapshotPreviousID = nPrevSnapshotId; AMP_RulesDAL.nSnapshotCurrentID = nCurrSnapshotId; AMP_RulesDAL.nSnapshotPreviousID = nPrevSnapshotId; AMP_RulesDAL.dtSnapshotCurrent = dtSnapshotCurrent; AMP_RulesDAL.dtSnapshotPrevious = dtSnapshotPrevious; List <EventInfo> lstEvent = new List <EventInfo>(); if (strEventCode == "All") { lstEvent = AMP_EventDAL.getAllEventsFromRule(); } else { EventInfo evt = AMP_EventDAL.getEventInfo(int.Parse(strEventCode)); lstEvent.Add(evt); } CheckEvent_Amendment(lstEvent, dep, nCurrSnapshotId, nPrevSnapshotId); AMP_Notification.sendEmail("*****@*****.**", "*****@*****.**", "status of running for " + (DateTime.Now.Subtract(dtNow).TotalSeconds), " seconds. Start at:" + dtNow.ToString("dd/MM/yyyy HH:mm") + " finish at " + DateTime.Now.ToString("dd/MM/yyyy HH:mm")); } else { AMP_Notification.sendEmail("*****@*****.**", "*****@*****.**", "Snapshot is hanging the process. Start from:" + dtNow.ToString("dd/MM/yyyy HH:mm") + " finish at " + DateTime.Now.ToString("dd/MM/yyyy HH:mm"), "Tried very hard for 4 times, the process of snapshot is still not finished yet. I gave up. You can you do it, no can no bibi."); } }
public static AMP_Rules getCommonEventRule() { AMP_Rules rule = new AMP_Rules(); SqlConnection conn = new SqlConnection(strEBMSConn); try { conn.Open(); string strSQL = "SELECT RuleId, RuleName, RuleType, ShortLeadStatusList, CreatedBy, CreatedDate, Status, TriggerMinutes,EventStatusList, LastRun, NextRun, Noti_Dep_Code, EventNotifyDaysFrom, EventNotifyDaysTo, EventStatusFrom, EventStatusTo, EmailSubject,ShowFuncId, ShowSpaceCode, ShowHierarchyFuncDesc, ShowPackageItemDateTime,ShowFuncSignage, NotesLength "; strSQL += " FROM AMP_New_Rules where Status='A' "; strSQL += " and ruletype='EVENT' and Noti_Dep_Code is null"; SqlCommand comm = new SqlCommand(strSQL, conn); comm.CommandTimeout = nCommandTimeOut; SqlDataAdapter da = new SqlDataAdapter(comm); SqlDataReader dr = comm.ExecuteReader(); if (dr.Read() && dr.HasRows) { rule.RuleId = dr["RuleId"].ToString(); rule.Rule_Name = dr["RuleName"].ToString(); rule.Rule_Type = dr["RuleType"].ToString(); rule.ShortLeadStatusList = dr["ShortLeadStatusList"].ToString(); rule.Created_By = dr["CreatedBy"].ToString(); rule.Created_Date = dr["CreatedDate"] == DBNull.Value ? DateTime.MinValue : DateTime.Parse(dr["CreatedDate"].ToString()); rule.Rule_Status = dr["Status"].ToString(); rule.TriggerMinutes = dr["TriggerMinutes"] == DBNull.Value ? 1 : int.Parse(dr["TriggerMinutes"].ToString()); rule.Next_Run = dr["NextRun"] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr["NextRun"].ToString()); rule.Last_Run = dr["LastRun"] == DBNull.Value ? rule.Next_Run.AddMinutes(-rule.TriggerMinutes) : DateTime.Parse(dr["LastRun"].ToString()); rule.Notify_Dept_Code = dr["Noti_Dep_Code"].ToString(); rule.Notify_EventDay_From = dr["EventNotifyDaysFrom"] == DBNull.Value ? 0 : int.Parse(dr["EventNotifyDaysFrom"].ToString()); rule.Notify_EventDay_To = dr["EventNotifyDaysTo"] == DBNull.Value ? 0 : int.Parse(dr["EventNotifyDaysTo"].ToString()); rule.EventStatusFrom = dr["EventStatusFrom"].ToString(); rule.EventStatusTo = dr["EventStatusTo"].ToString(); string strS = null; rule.EventStatusList = dr["EventStatusList"] == DBNull.Value ? strS : dr["EventStatusList"].ToString(); rule.EmailSubject = dr["EmailSubject"].ToString(); if (dr["ShowFuncId"] == DBNull.Value) { rule.ShowFuncId = false; } else { rule.ShowFuncId = false; bool isShowFuncId = false; if (bool.TryParse(dr["ShowFuncId"].ToString(), out isShowFuncId)) { isShowFuncId = bool.Parse(dr["ShowFuncId"].ToString()); if (isShowFuncId) { rule.ShowFuncId = true; } } } if (dr["ShowSpaceCode"] == DBNull.Value) { rule.ShowSpaceCode = false; } else { rule.ShowSpaceCode = false; bool isShowSpaceCode = false; if (bool.TryParse(dr["ShowSpaceCode"].ToString(), out isShowSpaceCode)) { isShowSpaceCode = bool.Parse(dr["ShowSpaceCode"].ToString()); if (isShowSpaceCode) { rule.ShowSpaceCode = true; } } } if (dr["ShowHierarchyFuncDesc"] == DBNull.Value) { rule.ShowHierarchyFuncDesc = false; } else { rule.ShowHierarchyFuncDesc = false; bool isShowHierarchyFuncDesc = false; if (bool.TryParse(dr["ShowHierarchyFuncDesc"].ToString(), out isShowHierarchyFuncDesc)) { isShowHierarchyFuncDesc = bool.Parse(dr["ShowHierarchyFuncDesc"].ToString()); if (isShowHierarchyFuncDesc) { rule.ShowHierarchyFuncDesc = true; } } } rule.ShowPackageItemDateTime = false; if (dr["ShowPackageItemDateTime"] != DBNull.Value) { bool isShowPackageItemDateTime = false; if (bool.TryParse(dr["ShowPackageItemDateTime"].ToString(), out isShowPackageItemDateTime)) { isShowPackageItemDateTime = bool.Parse(dr["ShowPackageItemDateTime"].ToString()); if (isShowPackageItemDateTime) { rule.ShowPackageItemDateTime = true; } } } rule.ShowFunctionSignageChange = false; if (dr["ShowFuncSignage"] != DBNull.Value) { bool isShowFuncSignage = false; if (bool.TryParse(dr["ShowFuncSignage"].ToString(), out isShowFuncSignage)) { isShowFuncSignage = bool.Parse(dr["ShowFuncSignage"].ToString()); if (isShowFuncSignage) { rule.ShowFunctionSignageChange = true; } } } rule.NotesLength = 200; if (dr["NotesLength"] != DBNull.Value) { int nNotesLength = 200; if (int.TryParse(dr["NotesLength"].ToString(), out nNotesLength)) { nNotesLength = int.Parse(dr["NotesLength"].ToString()); rule.NotesLength = nNotesLength; } } } } catch (Exception ex) { AMP_Common.sendException(ex, System.Reflection.MethodBase.GetCurrentMethod().Name, strEmailFrom, strEmailTo, ex.Message); } finally { conn.Close(); } return(rule); }