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);
}
