public static void saveToActivity()
{
SqlConnection conn = new SqlConnection(strEBMSConn);
int nMaxSEQ = 1;
if (!string.IsNullOrEmpty(ReadyMSG.dept.UserId) && !string.IsNullOrEmpty(ReadyMSG.emsg.AcctCode))
{
try
{
conn.Open();
string strSQL0 = "SELECT COALESCE(MAX([CR031_DIARY_SEQ]),0) + 1 as MAXSEQ FROM [" + strCompDatabase + "].[dbo].[CR031_DIARY] WHERE CR031_EXT_ACCT_CODE=@EventAcctCode";
SqlCommand comm0 = new SqlCommand(strSQL0, conn);
comm0.Parameters.Add("@EventAcctCode", SqlDbType.VarChar, 8).Value = ReadyMSG.emsg.AcctCode;
SqlDataReader dr = comm0.ExecuteReader();
if (dr.Read() && dr.HasRows)
{
nMaxSEQ = int.Parse(dr["MAXSEQ"].ToString());
}
dr.Close();
string strSQL = " INSERT INTO [" + strCompDatabase + "].[dbo].[CR031_DIARY] ";
strSQL += " ([CR031_ORG_CODE] ,[CR031_EXT_ACCT_CODE], [CR031_DIARY_SEQ], [CR031_DIARY_SEQ_CONT], [CR031_OCCURENCE], [CR031_DIARY_TEXT], [CR031_DIARY_DATE], [CR031_DIARY_TIME], ";
strSQL += " [CR031_DIARY_REP_CODE], [CR031_DIARY_TYPE] , [CR031_DIARY_ENTRY_TYPE], [CR031_TRACE_PRIORITY], [CR031_PRIVILEGED] , [CR031_ENT_DATE], [CR031_ENT_USER_ID], ";
strSQL += " [CR031_TRACE_STS] , [CR031_DESIGNATION], [CR031_EVT_ID], [CR031_FUNC_ID] , [CR031_HTML_TEXT] , [CR031_ACT_STATUS], [CR031_ACT_CLASS], [CR031_SOURCE]) ";
strSQL += " VALUES ";
strSQL += " ('10', @DiaryExtAcctCode, @DiarySeq,0 ,0 ,@DiaryText, GETDATE(), GETDATE(), @UserId, 'A', @amendmenttype, 'R', 'N', GETDATE(), 'AZHENG', 'N', 'C', @DiaryEventId, @DiaryFuncId, @DiaryHTMLText, 'N', 'T', 'SYS') ";
SqlCommand comm = new SqlCommand(strSQL, conn);
comm.Parameters.Add("@DiaryExtAcctCode", SqlDbType.VarChar, 8).Value = ReadyMSG.emsg.AcctCode;
comm.Parameters.Add("@DiarySeq", SqlDbType.Int).Value = nMaxSEQ;
comm.Parameters.Add("@DiaryText", SqlDbType.VarChar).Value = ReadyMSG.emsg.MSGText;
//comm.Parameters.Add("@DiaryText", SqlDbType.Text).Value = "Event Amendments";
comm.Parameters.Add("@UserId", SqlDbType.VarChar, 8).Value = ReadyMSG.dept.UserId;
comm.Parameters.Add("@amendmenttype", SqlDbType.VarChar, 3).Value = ReadyMSG.emsg.MessageType;
comm.Parameters.Add("@DiaryEventId", SqlDbType.Int).Value = ReadyMSG.emsg.EventId;
comm.Parameters.Add("@DiaryFuncId", SqlDbType.Int).Value = ReadyMSG.emsg.FuncId;
comm.Parameters.Add("@DiaryHTMLText", SqlDbType.VarChar).Value = ReadyMSG.emsg.MSGHTML;
comm.CommandTimeout = nCommandTimeOut;
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
AMP_Common.sendException(ex, System.Reflection.MethodBase.GetCurrentMethod().Name, "*****@*****.**", "*****@*****.**", ex.Message);
}
finally
{
conn.Close();
}
}
}
public static List <Notification_Dep_user> getDepartments()
{
List <Notification_Dep_user> depts = new List <Notification_Dep_user>();
SqlConnection conn = new SqlConnection(strEBMSConn);
try
{
conn.Open();
string strSQL = "SELECT Noti_Dep_Code,Noti_Dep_Desc, Dep_User_Id, Noti_Method, EmailAddress FROM Noti_Dept dep where Status='1' and (Dep_User_Id<>'' or EmailAddress<>'')";
SqlCommand comm = new SqlCommand(strSQL, conn);
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)
{
if (dr["Noti_Dep_Code"] != DBNull.Value && (dr["Dep_User_Id"] != DBNull.Value || dr["EmailAddress"] != DBNull.Value))
{
Notification_Dep_user Noti = new Notification_Dep_user();
Noti.DepartmentCode = dr["Noti_Dep_Code"].ToString();
Noti.DepartmentDesc = dr["Noti_Dep_Desc"].ToString();
Noti.NotifiMethod = dr["Noti_Method"].ToString();
Noti.UserId = dr["Dep_User_Id"].ToString();
Noti.EmailAddress = dr["EmailAddress"].ToString();
depts.Add(Noti);
}
}
}
}
catch (Exception ex)
{
AMP_Common.sendException(ex, System.Reflection.MethodBase.GetCurrentMethod().Name, strEmailFrom, strEmailTo, ex.Message);
}
finally
{
conn.Close();
}
return(depts);
}
public static Notification_Dep_user getDepartment(string strDeptCode)
{
Notification_Dep_user dept = new Notification_Dep_user();
SqlConnection conn = new SqlConnection(strEBMSConn);
try
{
conn.Open();
string strSQL = "SELECT Noti_Dep_Code,Noti_Dep_Desc, Dep_User_Id, Noti_Method, EmailAddress FROM Noti_Dept where (Dep_User_Id<>'' or EmailAddress<>'') and Noti_Dep_Code=@departmentcode";
SqlCommand comm = new SqlCommand(strSQL, conn);
comm.Parameters.Add("@departmentcode", SqlDbType.VarChar, 20).Value = strDeptCode;
comm.CommandTimeout = nCommandTimeOut;
SqlDataReader dr = comm.ExecuteReader();
if (dr.Read())
{
if (dr["Noti_Dep_Code"] != DBNull.Value && (dr["Dep_User_Id"] != DBNull.Value || dr["EmailAddress"] != DBNull.Value))
{
dept.DepartmentCode = dr["Noti_Dep_Code"].ToString();
dept.DepartmentDesc = dr["Noti_Dep_Desc"] == DBNull.Value ? "" : dr["Noti_Dep_Desc"].ToString();
dept.NotifiMethod = dr["Noti_Method"].ToString();
dept.UserId = dr["Dep_User_Id"].ToString();
dept.EmailAddress = dr["EmailAddress"].ToString();
}
}
}
catch (Exception ex)
{
AMP_Common.sendException(ex, System.Reflection.MethodBase.GetCurrentMethod().Name, strEmailFrom, strEmailTo, ex.Message);
}
finally
{
conn.Close();
}
return(dept);
}
public static void SaveHistory()
{
SqlConnection conn = new SqlConnection(strEBMSConn);
try
{
conn.Open();
string strSQL2 = "INSERT INTO AMP_Logs (RuleCode ,RuleName ,nSnapshotPrevious ,nSnapshotCurrent ,DepartmentCode ,UserId ,EmailAddress ,AccountCode, EventId, DiarySeq ,DiaryText, DiaryHTML ,RunDate) VALUES ";
strSQL2 += " (@rulecode, @rulename, @nSnapshotPrevId, @nSnapshotCurrId, @deptcode, @userid, @emailaddress, @accountcode,@eventid, @diaryseq, @diarytext, @diaryhtml, @rundate)";
SqlCommand comm2 = new SqlCommand(strSQL2, conn);
comm2.Parameters.Add("@rulecode", SqlDbType.VarChar, 20).Value = ReadyMSG.rule.RuleId;
comm2.Parameters.Add("@rulename", SqlDbType.VarChar, 255).Value = ReadyMSG.rule.Rule_Name;
comm2.Parameters.Add("@nSnapshotPrevId", SqlDbType.Int).Value = ReadyMSG.nSnapshotPrev;
comm2.Parameters.Add("@nSnapshotCurrId", SqlDbType.Int).Value = ReadyMSG.nSnapshotCurr;
comm2.Parameters.Add("@deptcode", SqlDbType.VarChar, 20).Value = ReadyMSG.dept.DepartmentCode;
comm2.Parameters.Add("@userid", SqlDbType.VarChar, 50).Value = ReadyMSG.dept.UserId;
comm2.Parameters.Add("@emailaddress", SqlDbType.VarChar, 200).Value = ReadyMSG.dept.EmailAddress;
comm2.Parameters.Add("@accountcode", SqlDbType.VarChar, 8).Value = ReadyMSG.emsg.AcctCode;
comm2.Parameters.Add("@eventid", SqlDbType.Int).Value = ReadyMSG.emsg.EventId;
comm2.Parameters.Add("@diaryseq", SqlDbType.Int).Value = 0;
comm2.Parameters.Add("@diarytext", SqlDbType.NVarChar, -1).Value = ReadyMSG.emsg.MSGText;
comm2.Parameters.Add("@diaryhtml", SqlDbType.NVarChar, -1).Value = ReadyMSG.emsg.MSGHTML;
comm2.Parameters.Add("@rundate", SqlDbType.DateTime).Value = DateTime.Now;
comm2.CommandTimeout = nCommandTimeOut;
comm2.ExecuteNonQuery();
}
catch (Exception ex)
{
AMP_Common.sendException(ex, System.Reflection.MethodBase.GetCurrentMethod().Name, "*****@*****.**", "*****@*****.**", ex.Message);
}
finally
{
conn.Close();
}
}
/// <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 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);
}