Ejemplo n.º 1
0
        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();
                }
            }
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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();
            }
        }
Ejemplo n.º 5
0
        /// <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);
        }
Ejemplo n.º 6
0
        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);
        }