public static void SaveAccount(int TransType, int iPK, string sLastName, string sFirstName, string sMiddleName,
string sEmail, int iUserType, string sUserName, string sPassword, int EmployeeKey,
string sDomainName = "", string sEntityCode = "", string sBUCode = "")
{
using (SqlConnection con = new SqlConnection(GlobalClass.SQLConnString()))
{
con.Open();
SqlCommand cmd = null;
string qry = "", _sLastName, _sFirstName, _sMiddleName, _sEmail, _sUserName,
_sPassword, _sDomainName, _sEntityCode, _sBUCode;
_sLastName = EncryptionClass.Encrypt(sLastName);
_sFirstName = EncryptionClass.Encrypt(sFirstName);
_sMiddleName = EncryptionClass.Encrypt(sMiddleName);
_sEmail = EncryptionClass.Encrypt(sEmail);
_sUserName = EncryptionClass.Encrypt(sUserName);
_sPassword = EncryptionClass.Encrypt(sPassword);
_sDomainName = EncryptionClass.Encrypt(sDomainName);
_sEntityCode = sEntityCode;
_sBUCode = sBUCode;
if (TransType == 1)
{
// Insert
qry = "INSERT INTO tbl_Users " +
" (Lastname, Firstname, Middlename, UserType, Username, Password, Email, DomainAccount, EmployeeKey) " +
" VALUES ('" + _sLastName + "', '" + _sFirstName + "', '" + _sMiddleName + "', " + iUserType + ", " +
" '" + _sUserName + "', '" + _sPassword + "', '" + _sEmail + "', '" + _sDomainName + "', " +
" " + EmployeeKey + ")";
}
if (TransType == 2)
{
// Edit
qry = "UPDATE tbl_Users " +
" SET EntityCode = '" + _sEntityCode + "', " +
" BUCode = '" + _sBUCode + "', " +
" DomainAccount = '" + _sDomainName + "' " +
" WHERE (PK = " + iPK + ")";
}
try
{
cmd = new SqlCommand(qry);
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();
GlobalClass.QueryError = "Account saved!";
}
catch (SqlException e)
{
con.Close();
GlobalClass.QueryError = e.ToString();
}
}
}
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);
}
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);
}
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);
}
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();
}