public static DataTable UserListTable()
        {
            DataTable dtTable = new DataTable();

            SqlConnection  cn  = new SqlConnection(GlobalClass.SQLConnString());
            DataTable      dt  = new DataTable();
            SqlCommand     cmd = null;
            SqlDataAdapter adp;

            cn.Open();

            if (dtTable.Columns.Count == 0)
            {
                //Columns for AspxGridview
                dtTable.Columns.Add("ID", typeof(string));
                dtTable.Columns.Add("NAME", typeof(string));
            }

            string qry = "SELECT PK, Lastname, Firstname FROM [dbo].[tbl_Users] ORDER BY Lastname, Firstname ";

            cmd            = new SqlCommand(qry);
            cmd.Connection = cn;
            adp            = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    DataRow dtRow = dtTable.NewRow();
                    dtRow["ID"]   = row["PK"].ToString();
                    dtRow["NAME"] = EncryptionClass.Decrypt(row["Lastname"].ToString()) + ",  " + EncryptionClass.Decrypt(row["Firstname"].ToString());
                    dtTable.Rows.Add(dtRow);
                }
            }
            dt.Clear();
            cn.Close();

            return(dtTable);
        }
        public static DataTable WorkflowMasterTable()
        {
            DataTable dtTable = new DataTable();

            SqlConnection  cn  = new SqlConnection(GlobalClass.SQLConnString());
            DataTable      dt  = new DataTable();
            SqlCommand     cmd = null;
            SqlDataAdapter adp;

            if (dtTable.Columns.Count == 0)
            {
                //Columns for AspxGridview
                dtTable.Columns.Add("PK", typeof(string));
                dtTable.Columns.Add("Ctrl", typeof(string));
                dtTable.Columns.Add("EffectDate", typeof(string));
                dtTable.Columns.Add("EntCode", typeof(string));
                dtTable.Columns.Add("EntCodeDesc", typeof(string));
                dtTable.Columns.Add("BUCode", typeof(string));
                dtTable.Columns.Add("BUCodeDesc", typeof(string));
                dtTable.Columns.Add("BUHead", typeof(string));
                dtTable.Columns.Add("BUHeadName", typeof(string));
                dtTable.Columns.Add("DateCreated", typeof(string));
            }

            cn.Open();
            string qry = "SELECT dbo.tbl_System_Workflow.PK, dbo.tbl_System_Workflow.Ctrl, dbo.tbl_System_Workflow.EffectDate, " +
                         " dbo.tbl_System_Workflow.EntCode, dbo.vw_AXEntityTable.NAME AS EntCodeDesc, dbo.tbl_System_Workflow.BUCode, " +
                         " dbo.vw_AXOperatingUnitTable.NAME AS BUCodeDesc, dbo.tbl_System_Workflow.BUHead, dbo.tbl_Users.Lastname, " +
                         " dbo.tbl_Users.Firstname, dbo.tbl_System_Workflow.LastModified, dbo.tbl_System_Workflow.DateCreated " +
                         " FROM  dbo.tbl_System_Workflow LEFT OUTER JOIN " +
                         " dbo.vw_AXOperatingUnitTable ON dbo.tbl_System_Workflow.BUCode = dbo.vw_AXOperatingUnitTable.OMOPERATINGUNITNUMBER LEFT OUTER JOIN " +
                         " dbo.vw_AXEntityTable ON dbo.tbl_System_Workflow.EntCode = dbo.vw_AXEntityTable.ID LEFT OUTER JOIN " +
                         " dbo.tbl_Users ON dbo.tbl_System_Workflow.BUHead = dbo.tbl_Users.PK";

            cmd            = new SqlCommand(qry);
            cmd.Connection = cn;
            adp            = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    DataRow dtRow = dtTable.NewRow();
                    dtRow["PK"]          = row["PK"].ToString();
                    dtRow["Ctrl"]        = row["Ctrl"].ToString();
                    dtRow["EffectDate"]  = Convert.ToDateTime(row["EffectDate"]).ToString("MM/dd/yyyy");
                    dtRow["EntCode"]     = row["EntCode"].ToString();
                    dtRow["EntCodeDesc"] = row["EntCodeDesc"].ToString();
                    dtRow["BUCode"]      = row["BUCode"].ToString();
                    dtRow["BUCodeDesc"]  = row["BUCodeDesc"].ToString();
                    dtRow["BUHead"]      = row["BUHead"].ToString();
                    dtRow["BUHeadName"]  = EncryptionClass.Decrypt(row["Lastname"].ToString()) + ",  " + EncryptionClass.Decrypt(row["Firstname"].ToString());
                    dtRow["DateCreated"] = Convert.ToDateTime(row["DateCreated"]).ToString();
                    dtTable.Rows.Add(dtRow);
                }
            }
            dt.Clear();
            cn.Close();

            return(dtTable);
        }
Esempio n. 3
0
        public static DataTable FinanceInventoryOfficerTable()
        {
            DataTable dtTable = new DataTable();

            SqlConnection  cn  = new SqlConnection(GlobalClass.SQLConnString());
            DataTable      dt  = new DataTable();
            SqlCommand     cmd = null;
            SqlDataAdapter adp;

            cn.Open();

            if (dtTable.Columns.Count == 0)
            {
                //Columns for AspxGridview
                dtTable.Columns.Add("PK", typeof(string));
                dtTable.Columns.Add("Ctrl", typeof(string));
                dtTable.Columns.Add("EffectDate", typeof(string));
                dtTable.Columns.Add("UserKey", typeof(string));
                dtTable.Columns.Add("UserCompleteName", typeof(string));
                dtTable.Columns.Add("StatusKey", typeof(string));
                dtTable.Columns.Add("StatusDesc", typeof(string));
                dtTable.Columns.Add("LastModified", typeof(string));
            }

            string qry = "SELECT dbo.tbl_System_FinanceInventoryOfficer.PK, dbo.tbl_System_FinanceInventoryOfficer.Ctrl, " +
                         " dbo.tbl_System_FinanceInventoryOfficer.EffectDate, dbo.tbl_System_FinanceInventoryOfficer.UserKey, " +
                         " dbo.tbl_Users.Lastname, dbo.tbl_Users.Firstname, dbo.tbl_System_FinanceInventoryOfficer.LastModified, " +
                         " dbo.tbl_System_FinanceInventoryOfficer.StatusKey " +
                         " FROM dbo.tbl_System_FinanceInventoryOfficer LEFT OUTER JOIN " +
                         " dbo.tbl_Users ON dbo.tbl_System_FinanceInventoryOfficer.UserKey = dbo.tbl_Users.PK";

            cmd            = new SqlCommand(qry);
            cmd.Connection = cn;
            adp            = new SqlDataAdapter(cmd);
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    DataRow dtRow = dtTable.NewRow();
                    dtRow["PK"]               = row["PK"].ToString();
                    dtRow["Ctrl"]             = row["Ctrl"].ToString();
                    dtRow["EffectDate"]       = Convert.ToDateTime(row["EffectDate"]).ToString("MM/dd/yyyy");
                    dtRow["UserKey"]          = row["UserKey"].ToString();
                    dtRow["UserCompleteName"] = EncryptionClass.Decrypt(row["Lastname"].ToString()) + ",  " + EncryptionClass.Decrypt(row["Firstname"].ToString());
                    dtRow["StatusKey"]        = row["StatusKey"].ToString();
                    if (Convert.ToInt32(row["StatusKey"]) == 1)
                    {
                        dtRow["StatusDesc"] = "Active";
                    }
                    else
                    {
                        dtRow["StatusDesc"] = "Inactive";
                    }
                    dtRow["LastModified"] = row["LastModified"].ToString();
                    dtTable.Rows.Add(dtRow);
                }
            }
            dt.Clear();
            cn.Close();

            return(dtTable);
        }
        public static DataTable UserList()
        {
            DataTable      dtTable = new DataTable();
            SqlCommand     cmd     = null;
            SqlDataAdapter adp;
            DataTable      dtable = new DataTable();

            if (dtTable.Columns.Count == 0)
            {
                dtTable.Columns.Add("PK", typeof(string));
                dtTable.Columns.Add("UserName", typeof(string));
                dtTable.Columns.Add("Password", typeof(string));
                dtTable.Columns.Add("UserType", typeof(string));
                dtTable.Columns.Add("UserTypeDesc", typeof(string));
                dtTable.Columns.Add("UserLevelKey", typeof(string));
                dtTable.Columns.Add("UserLevelDesc", typeof(string));
                dtTable.Columns.Add("LastName", typeof(string));
                dtTable.Columns.Add("FirstName", typeof(string));
                dtTable.Columns.Add("Gender", typeof(string));
                //dtTable.Columns.Add("MiddleName", typeof(string));
                dtTable.Columns.Add("CompleteName", typeof(string));
                dtTable.Columns.Add("Email", typeof(string));
                dtTable.Columns.Add("EntityCode", typeof(string));
                dtTable.Columns.Add("EntityCodeDesc", typeof(string));
                dtTable.Columns.Add("BUCode", typeof(string));
                dtTable.Columns.Add("BUCodeDesc", typeof(string));
                dtTable.Columns.Add("DomainAccount", typeof(string));
                dtTable.Columns.Add("StatusKey", typeof(string));
                dtTable.Columns.Add("StatusDesc", typeof(string));
                dtTable.Columns.Add("EmployeeKey", typeof(string));
            }
            dtTable.Clear();

            using (SqlConnection con = new SqlConnection(GlobalClass.SQLConnString()))
            {
                con.Open();
                string qry = "SELECT dbo.tbl_Users.PK, dbo.tbl_Users.UserType, dbo.tbl_UsersType.UserType AS UserTypeDesc, dbo.tbl_Users.UserLevelKey, dbo.tbl_UserLevel.UserLevel, dbo.tbl_Users.Username, dbo.tbl_Users.Password, dbo.tbl_Users.DomainAccount, dbo.tbl_Users.Lastname, dbo.tbl_Users.Firstname, dbo.tbl_Users.Email, dbo.tbl_Users.Gender, dbo.tbl_Users.EmployeeKey, dbo.tbl_Users.EntityCode, dbo.tbl_Users.BUCode, dbo.tbl_Users.Image, ISNULL(dbo.vw_AXEntityTable.NAME, '') AS EntityCodeDesc, ISNULL(dbo.vw_AXOperatingUnitTable.NAME, '') AS BUCodeDesc, dbo.tbl_Users.DomainAccount, dbo.tbl_Users.Active, dbo.tbl_Users.EmployeeKey FROM dbo.tbl_Users LEFT OUTER JOIN dbo.vw_AXOperatingUnitTable ON dbo.tbl_Users.BUCode = dbo.vw_AXOperatingUnitTable.OMOPERATINGUNITNUMBER LEFT OUTER JOIN  dbo.vw_AXEntityTable ON dbo.tbl_Users.EntityCode = dbo.vw_AXEntityTable.ID LEFT OUTER JOIN dbo.tbl_UsersType ON dbo.tbl_Users.UserType = dbo.tbl_UsersType.PK LEFT OUTER JOIN dbo.tbl_UserLevel ON dbo.tbl_Users.UserLevelKey = dbo.tbl_UserLevel.PK ORDER BY dbo.tbl_Users.Lastname, dbo.tbl_Users.Firstname";
                cmd            = new SqlCommand(qry);
                cmd.Connection = con;
                adp            = new SqlDataAdapter(cmd);
                adp.Fill(dtable);
                if (dtable.Rows.Count > 0)
                {
                    foreach (DataRow row in dtable.Rows)
                    {
                        DataRow rowAdd = dtTable.NewRow();
                        rowAdd["PK"]            = row["PK"].ToString();
                        rowAdd["UserName"]      = EncryptionClass.Decrypt(row["Username"].ToString());
                        rowAdd["Password"]      = EncryptionClass.Decrypt(row["Password"].ToString());
                        rowAdd["UserType"]      = row["UserType"].ToString();
                        rowAdd["UserTypeDesc"]  = row["UserTypeDesc"].ToString();
                        rowAdd["UserLevelKey"]  = row["UserLevelKey"].ToString();
                        rowAdd["UserLevelDesc"] = row["UserLevel"].ToString();
                        rowAdd["CompleteName"]  = EncryptionClass.Decrypt(row["Lastname"].ToString()) + ",  " + EncryptionClass.Decrypt(row["Firstname"].ToString()); //+ "  " + EncryptionClass.Decrypt(row["Middlename"].ToString());
                        rowAdd["LastName"]      = EncryptionClass.Decrypt(row["Lastname"].ToString());
                        rowAdd["FirstName"]     = EncryptionClass.Decrypt(row["Firstname"].ToString());
                        if (Convert.ToInt32(row["Gender"]) == 1)
                        {
                            rowAdd["Gender"] = "Male";
                        }
                        else
                        {
                            rowAdd["Gender"] = "Female";
                        }
                        //rowAdd["MiddleName"] = EncryptionClass.Decrypt(row["Middlename"].ToString());
                        rowAdd["Email"]          = EncryptionClass.Decrypt(row["Email"].ToString());
                        rowAdd["EntityCode"]     = row["EntityCode"].ToString();
                        rowAdd["EntityCodeDesc"] = row["EntityCodeDesc"].ToString();
                        rowAdd["BUCode"]         = row["BUCode"].ToString();
                        rowAdd["BUCodeDesc"]     = row["BUCodeDesc"].ToString();
                        if (row["DomainAccount"].ToString().Trim() == "")
                        {
                            rowAdd["DomainAccount"] = row["DomainAccount"].ToString();
                        }
                        else
                        {
                            rowAdd["DomainAccount"] = EncryptionClass.Decrypt(row["DomainAccount"].ToString());
                        }
                        rowAdd["StatusKey"] = row["Active"].ToString();
                        if (Convert.ToInt32(row["Active"]) == 1)
                        {
                            rowAdd["StatusDesc"] = "Active";
                        }
                        else
                        {
                            rowAdd["StatusDesc"] = "Inactive";
                        }

                        if (!DBNull.Value.Equals(row["EmployeeKey"]))
                        {
                            rowAdd["EmployeeKey"] = row["EmployeeKey"].ToString();
                        }
                        else
                        {
                            rowAdd["EmployeeKey"] = "0";
                        }
                        dtTable.Rows.Add(rowAdd);
                    }
                }
                dtable.Clear();
                con.Close();
            }

            return(dtTable);
        }
Esempio n. 5
0
        private static void MRP_Approve_SCM(string docNum, int MRPKey, DateTime dteCreated, int ApproveLine, string EntCode, string BuCode, int usrKey)
        {
            SqlConnection conn = new SqlConnection(GlobalClass.SQLConnString());
            string        qry = "";
            string        CreatorEmail = "", CreatorSubject = "", CreatorGreetings = "";
            var           sCreatorBody = new StringBuilder();
            string        sEmail = "", sSubject = "", sGreetings = "";
            var           sBody = new StringBuilder();

            int ApproveLineNext = ApproveLine + 1;

            SqlCommand     cmdIns = null;
            SqlCommand     cmdUp  = null;
            SqlCommand     cmd    = null;
            SqlDataAdapter adp;
            DataTable      dtable = new DataTable();

            SqlCommand     cmd1 = null;
            SqlDataAdapter adp1;
            DataTable      dtable1 = new DataTable();

            SqlCommand     cmd2 = null;
            SqlDataAdapter adp2;
            DataTable      dtable2 = new DataTable();

            SqlCommand     cmd3 = null;
            SqlDataAdapter adp3;
            DataTable      dtable3 = new DataTable();

            conn.Open();
            qry = "SELECT dbo.tbl_Users.Email, dbo.tbl_Users.Gender, dbo.tbl_Users.Lastname " +
                  " FROM dbo.tbl_MRP_List LEFT OUTER JOIN " +
                  " dbo.tbl_Users ON dbo.tbl_MRP_List.CreatorKey = dbo.tbl_Users.PK " +
                  " WHERE(dbo.tbl_MRP_List.PK = " + MRPKey + ")";
            cmd            = new SqlCommand(qry);
            cmd.Connection = conn;
            adp            = new SqlDataAdapter(cmd);
            adp.Fill(dtable);
            if (dtable.Rows.Count > 0)
            {
                foreach (DataRow row in dtable.Rows)
                {
                    CreatorEmail = EncryptionClass.Decrypt(row["Email"].ToString());
                    if (Convert.ToInt32(row["Gender"]) == 1)
                    {
                        CreatorGreetings = "Dear Mr. " + EncryptionClass.Decrypt(row["Lastname"].ToString());
                    }
                    else
                    {
                        CreatorGreetings = "Dear Ms. " + EncryptionClass.Decrypt(row["Lastname"].ToString());
                    }
                    CreatorSubject = "MOP DocNum " + docNum.ToString() + " status";

                    sCreatorBody.Append("<!DOCTYPE html>");
                    sCreatorBody.Append("<html>");
                    sCreatorBody.Append("<head>");
                    sCreatorBody.Append("</head>");
                    sCreatorBody.Append("<body>");
                    sCreatorBody.Append("<p style='font-family:Tahoma; font-size: 12px;'>" + CreatorGreetings + ",</p>");
                    sCreatorBody.Append("<p style='font-family:Tahoma; font-size: 12px;'>MOP Document # " + docNum.ToString() + " has been approved by Supply Chain Management Lead.</p>");
                    sCreatorBody.Append("<p style='font-family:Tahoma; font-size: 10px;font-style:italic;'>***This is a system-generated message. please do not reply to this email.***</p>");
                    sCreatorBody.Append("<p style='font-family:Tahoma; font-size: 10px;'>DISCLAIMER: This email is confidential and intended solely for the use of the individual to whom it is addressed. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of this email is strictly prohibited. If you have received this email in error please notify the sender or email [email protected], telephone number (082) 282-3662.</p>");
                    sCreatorBody.Append("</body>");
                    sCreatorBody.Append("</html>");
                }
            }
            dtable.Clear();

            //Update Approval
            qry = "UPDATE tbl_MRP_List_Approval " +
                  " SET Visible = 0, " +
                  " Status = 1 " +
                  " WHERE (MasterKey = " + MRPKey + ") " +
                  " AND (Line = " + ApproveLine + ")";
            cmdUp = new SqlCommand(qry, conn);
            cmdUp.ExecuteNonQuery();

            //bool msgSendToCreator = GlobalClass.IsMailSent(CreatorEmail, CreatorSubject, sCreatorBody.ToString());
            GlobalClass.CreateEmailNotification(CreatorEmail, CreatorGreetings, docNum, "has been approved by Supply Chain Management Lead", 4);

            //Check if Approve All
            qry = "SELECT COUNT(*) AS RecCnt " +
                  " FROM dbo.tbl_MRP_List_Approval " +
                  " WHERE(MasterKey = " + MRPKey + ") " +
                  " AND(Status = 0)";
            cmd            = new SqlCommand(qry);
            cmd.Connection = conn;
            adp            = new SqlDataAdapter(cmd);
            adp.Fill(dtable);
            if (dtable.Rows.Count > 0)
            {
                foreach (DataRow row in dtable.Rows)
                {
                    if (Convert.ToInt32(row["RecCnt"]) == 0)
                    {
                        qry = "UPDATE tbl_MRP_List " +
                              " SET StatusKey = 4 " +
                              " WHERE (PK = " + MRPKey + ")";
                        cmdUp = new SqlCommand(qry, conn);
                        cmdUp.ExecuteNonQuery();

                        qry = "UPDATE tbl_MRP_List_Workflow " +
                              " SET Visible = 0, " +
                              " Status = 1 " +
                              " WHERE (MasterKey = " + MRPKey + ") " +
                              " AND (Line = 4)";
                        cmdUp = new SqlCommand(qry, conn);
                        cmdUp.ExecuteNonQuery();
                    }
                }
            }
            dtable.Clear();
            conn.Close();
        }