public static EmployeeEN SearchEmp(int id)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select * from dbo.Employees");
sql.AppendLine("where id = " + id);
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
EmployeeEN en = new EmployeeEN();
if (dt.Rows.Count > 0)
{
DataRow dr = (DataRow)dt.Rows[0];
en.ID = Convert.ToInt32(dr["id"]);
en.Emp_id = Convert.ToString(dr["Emp_id"]);
en.AccName = Convert.ToString(dr["AccName"]);
en.AccNo = Convert.ToString(dr["AccNo"]);
en.Firstname = Convert.ToString(dr["Firstname"]);
en.Lastname = Convert.ToString(dr["Lastname"]);
en.Nickname = Convert.ToString(dr["Nickname"]);
en.Phone = Convert.ToString(dr["Phone"]);
en.IdenNumber = Convert.ToString(dr["IdenNumber"]);
en.Address = Convert.ToString(dr["Address"]);
en.Email = Convert.ToString(dr["Email"]);
en.salary = (Decimal)dr["salary"];
en.BirthDate = Convert.ToDateTime(Utilities.SetDefaultValue(dr["BirthDate"], DateTime.MinValue));
en.StartWorkDate = Convert.ToDateTime(Utilities.SetDefaultValue(dr["StartWorkDate"], DateTime.MinValue));
en.DeptId = Convert.ToInt16(Utilities.SetDefaultValue(dr["DeptId"], 0));
}
return(en);
}
public static string GetC_ID()
{
string result = "";
string strY = DateTime.Now.Year.ToString(new System.Globalization.CultureInfo("en-US"));
StringBuilder sql = new StringBuilder();
sql.AppendLine("select SUBSTRING(C_ID,2,4) -- CAST( right(C_ID,2)as int) as C_ID from dbo.Customer");
sql.AppendLine("from dbo.Customer");
sql.AppendLine("where SUBSTRING(C_ID,2,4) = '" + strY + "'");
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
int YearDB = Convert.ToInt32(dt.Rows[0][0]);
int Year = DateTime.Now.Year;
if (YearDB != Year)
{
result = "C" + strY + "01";
}
else
{
string no = Get_EXT();
result = "C" + strY + no;
}
}
else
{
result = "C" + strY + "01";
}
return(result);
}
public static CustomerEN SearchEmp(string C_ID)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select * from dbo.Customer");
sql.AppendLine("where C_ID = '" + C_ID + "'");
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
CustomerEN en = new CustomerEN();
if (dt != null && dt.Rows.Count > 0)
{
DataRow dr = (DataRow)dt.Rows[0];
en.C_ID = Convert.ToString(dr["C_ID"]);
en.Name = Convert.ToString(dr["Name"]);
en.Tel = Convert.ToString(dr["Tel"]);
en.Email = Convert.ToString(dr["Email"]);
en.Fax = Convert.ToString(dr["Fax"]);
en.Name_Company = Convert.ToString(dr["Name_Company"]);
en.Address = Convert.ToString(dr["Address"]);
en.Tel_Company = Convert.ToString(dr["Tel_Company"]);
en.Tax_Number = Convert.ToString(dr["Tax_Number"]);
en.UpdatedBy = Convert.ToString(dr["UpdatedBy"]);
en.CreatedBy = Convert.ToString(dr["CreatedBy"]);
en.UpdatedDate = Convert.ToDateTime(Utilities.SetDefaultValue(dr["UpdatedDate"], DateTime.MinValue));
en.CreatedDate = Convert.ToDateTime(Utilities.SetDefaultValue(dr["CreatedDate"], DateTime.MinValue));
}
return(en);
}
public static DataTable LoadDept()
{
string sql = "Select * from " + tb_name_dept + " Order by Name";
DataTable dt = ClassMain.ExecuteComandTable(sql);
return(dt);
}
public static void GetEQForDDL_All(out ListItem[] lt)
{
lt = null;
string strY = DateTime.Now.Year.ToString(new System.Globalization.CultureInfo("en-US"));
StringBuilder sql = new StringBuilder();
sql.AppendLine("select a.TypeName + ' => ' + b.TypeName + ' => ' + c.Name as Name ,c.ID");
sql.AppendLine("from Equipment_Type a");
sql.AppendLine("inner join Equipment_Type b on a.ID = b.ParentID");
sql.AppendLine("inner join Equipment c on b.ID = c.Equip_type_id");
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
List <ListItem> lstlt = new List <ListItem>();
foreach (DataRow item in dt.Rows)
{
ListItem en = new ListItem();
en.Text = item["ID"].ToString();
en.Value = item["Name"].ToString();
lstlt.Add(en);
}
if (lstlt != null && lstlt.Any())
{
lt = lstlt.ToArray();
}
}
}
public static void LoadType(out Equipment_Type[] objects, int Level)
{
objects = null;
string strY = DateTime.Now.Year.ToString(new System.Globalization.CultureInfo("en-US"));
StringBuilder sql = new StringBuilder();
sql.AppendLine("select * FROM [dbo].[Equipment_Type] ");
sql.AppendLine("where ParentID = " + Level);
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
List <Equipment_Type> lstEquipment_Type = new List <Equipment_Type>();
foreach (DataRow item in dt.Rows)
{
Equipment_Type en = new Equipment_Type();
en.ID = int.Parse(item["Id"].ToString());
en.TypeName = item["TypeName"].ToString();
en.ParentID = int.Parse(item["ParentID"].ToString());
en.CreatedDate = item["CreatedDate"] != DBNull.Value ? Convert.ToDateTime(item["CreatedDate"].ToString()) : (DateTime?)null;
en.CreatedBy = item["CreatedBy"].ToString();
en.UpdatedDate = item["UpdatedDate"] != DBNull.Value ? Convert.ToDateTime(item["UpdatedDate"].ToString()) : (DateTime?)null;
en.UpdatedBy = item["UpdatedBy"].ToString();
lstEquipment_Type.Add(en);
}
if (lstEquipment_Type != null && lstEquipment_Type.Any())
{
objects = lstEquipment_Type.ToArray();
}
}
}
public static DataTable LoadData()
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "select * from " + tb_name + " order by Position";
DataTable dt = ClassMain.ExecuteComandTable(sqlCmd);
return(dt);
}
public static DataTable LoadUser(string username)
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "select * from dbo.Users where username='******' and username <> LOWER('system')";
DataTable dt = ClassMain.ExecuteComandTable(sqlCmd);
return(dt);
}
public static DataTable LoadData()
{
string strY = DateTime.Now.Year.ToString(new System.Globalization.CultureInfo("en-US"));
StringBuilder sql = new StringBuilder();
sql.AppendLine("select top 50 * from Customer");
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
return(dt);
}
public static DataTable SearchPosiionByEmp(string empid)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" select je.pos_id, j.Position, je.cost ");
sql.AppendLine("From " + tb_name_jopEmp + " je ");
sql.AppendLine("inner join " + tb_name + " j on j.ID = je.pos_id");
sql.AppendLine("where je.Emp_id ='" + empid + "'");
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
return(dt);
}
public static void LoadData(out CustomerEN[] Objects)
{
Objects = null;
string strY = DateTime.Now.Year.ToString(new System.Globalization.CultureInfo("en-US"));
StringBuilder sql = new StringBuilder();
sql.AppendLine("select top 50 * from Customer");
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
SetData(out Objects, dt);
}
protected void btnSignin_Click(object sender, EventArgs e)
{
// EnCryptPassword
string salt = System.Configuration.ConfigurationManager.AppSettings["salt"];
string strEnCrypt = SHA256.EcryptPassword(inputPassword.Text, salt);
SqlCommand sql = new SqlCommand();
sql.CommandText = "select ID from dbo.Users where Username =LOWER('" + inputUsername.Text + "') and Password = '******'";
DataTable dt = new DataTable();
int userId = 0;
try
{
userId = ClassMain.intExecuteComand(sql);
dt = ClassMain.ExecuteComandTable(sql);
switch (userId)
{
case 0:
//Login1.FailureText = "Username and/or password is incorrect.";
lblResult.Visible = true;
lblResult.Text = "Username and/or password is incorrect.";
break;
case -1:
//Login1.FailureText = "Username and/or password is incorrect.";
lblResult.Visible = true;
lblResult.Text = "Username and/or password is incorrect.";
break;
case -2:
//Login1.FailureText = "Account has not been activated.";
lblResult.Visible = true;
lblResult.Text = "Account has not been activated.";
break;
default:
FormsAuthentication.RedirectFromLoginPage(inputUsername.Text, true);
Session["userlogin"] = inputUsername.Text;
Response.Redirect("~/Default.aspx");
break;
}
}
catch (Exception)
{
throw;
}
}
public static JobPositionEN SearchByPosid(int id)
{
string sql = "Select * from " + tb_name + " Where id = " + id.ToString();
DataTable dt = ClassMain.ExecuteComandTable(sql);
JobPositionEN en = new JobPositionEN();
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
en.id = Convert.ToInt32(dr["ID"]);
en.position = Convert.ToString(dr["position"]);
en.cost = Convert.ToDecimal(dr["cost"]);
}
return(en);
}
public static int?GetID(string TB_Name, string Id)
{
int? no = null;
string strY = DateTime.Now.Year.ToString(new System.Globalization.CultureInfo("en-US"));
StringBuilder sql = new StringBuilder();
sql.AppendLine("select ISNULL(MAX(a." + Id + "),1) as runno from dbo." + TB_Name + " a");
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
no = Convert.ToInt32(dt.Rows[0][0]);
}
return(no);
}
public static string Get_EXT()
{
//string strY = DateTime.Now.Year.ToString(new System.Globalization.CultureInfo("en-US"));
StringBuilder sql = new StringBuilder();
sql.AppendLine("select SUBSTRING( MAX(C_ID),6,2) from dbo.Customer");
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
int no = Convert.ToInt32(dt.Rows[0][0]);
no += 1;
return(no.ToString("00"));
}
return("01");
}
public static DataTable LoadData_Master()
{
StringBuilder Sql = new StringBuilder();
Sql.AppendLine("select e.*, t.TypeName");
Sql.AppendLine("from " + tb_name_master + " e ");
Sql.AppendLine("inner join " + tb_name_type + " t on e.Equip_type_id = t.ID");
Sql.AppendLine("");
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = Sql.ToString();
DataTable dt = ClassMain.ExecuteComandTable(sqlCmd);
return(dt);
}
public static EquiptmentEN SearchEquipByID(int ID)
{
StringBuilder Sql = new StringBuilder();
Sql.AppendLine("select e.*, t.ID as TypeID, t.TypeName");
Sql.AppendLine("from " + tb_name_master + " e ");
Sql.AppendLine("inner join " + tb_name_type + " t on e.Equip_type_id = t.ID");
Sql.AppendLine("");
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = Sql.ToString();
DataTable dt = ClassMain.ExecuteComandTable(sqlCmd);
EquiptmentEN en = new EquiptmentEN();
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
en.ID = Convert.ToInt32(dr["ID"]);
en.EquipNo = Convert.ToString(dr["Number"]);
en.Name = Convert.ToString(dr["Name"]);
en.Fullname = Convert.ToString(dr["Fullname"]);
en.CostBuy = Convert.ToDecimal(dr["CostBuy"]);
en.CostRent = Convert.ToDecimal(dr["CostRent"]);
en.BuyDate = Convert.ToDateTime(dr["BuyDate"]);
en.Equip_type_id = Convert.ToInt32(dr["Equip_type_id"]);
en.ExpireDate = Convert.ToDateTime(dr["ExpireDate"]);
en.ReceiptTax = Convert.ToString(dr["ReceiptTax"]);
en.SupplierName = Convert.ToString(dr["SupplierName"]);
en.SN = Convert.ToString(dr["SN"]);
en.equipType = new EquipmentTypeEN();
en.equipType.ID = Convert.ToInt32(dr["TypeID"]);
en.equipType.TypeName = Convert.ToString(dr["TypeName"]);
return(en);
}
else
{
return(null);
}
}
public static string GetRunno()
{
string strY = DateTime.Now.Year.ToString(new System.Globalization.CultureInfo("en-US"));
StringBuilder sql = new StringBuilder();
sql.AppendLine("select ISNULL(MAX(CAST( right(Emp_id,2)as int)),0) runno from dbo.Employees");
sql.AppendLine("where SUBSTRING(Emp_id,2,4) = '" + strY + "'");
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
if (dt.Rows.Count > 0)
{
int no = Convert.ToInt32(dt.Rows[0][0]);
no += 1;
return(no.ToString("00"));
}
return("01");
}
public static UserEN FindUser(string username)
{
string sql = "select * from dbo.Users inner join Roles on Roles.id = Users.Role_ID ";
sql += "where Username=LOWER('" + username + "')";
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = sql;
DataTable dt = ClassMain.ExecuteComandTable(sqlCmd);
UserEN useren = new UserEN();
if (dt.Rows.Count > 0)
{
useren.username = Convert.ToString(dt.Rows[0]["username"]);
useren.rolename = Convert.ToString(dt.Rows[0]["rolename"]);
useren.role_id = Convert.ToInt32(dt.Rows[0]["role_id"]);
}
return(useren);
}
public static EquipmentTypeEN SearchByID(int ID)
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "select * from " + tb_name_type + " Where ID =" + ID.ToString();
DataTable dt = ClassMain.ExecuteComandTable(sqlCmd);
EquipmentTypeEN en = new EquipmentTypeEN();
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
en.ID = Convert.ToInt32(dr["ID"]);
en.TypeName = Convert.ToString(dr["TypeName"]);
en.ParentID = Convert.ToInt32(dr["ParentID"]);
return(en);
}
else
{
return(null);
}
}
protected void btnSignin_Click(object sender, EventArgs e)
{
// EnCryptPassword
string salt = System.Configuration.ConfigurationManager.AppSettings["salt"];
string strEnCrypt = SHA256.EcryptPassword(inputPassword.Text, salt);
SqlCommand sql = new SqlCommand();
sql.CommandText = "select ID from dbo.Users where Username =LOWER('" + inputUsername.Text + "') and Password = '******'";
DataTable dt = new DataTable();
int userId = 0;
try
{
userId = ClassMain.intExecuteComand(sql);
dt = ClassMain.ExecuteComandTable(sql);
switch (userId)
{
case 0:
//Login1.FailureText = "Username and/or password is incorrect.";
lblResult.Visible = true;
lblResult.Text = "Username and/or password is incorrect.";
break;
case -1:
//Login1.FailureText = "Username and/or password is incorrect.";
lblResult.Visible = true;
lblResult.Text = "Username and/or password is incorrect.";
break;
case -2:
//Login1.FailureText = "Account has not been activated.";
lblResult.Visible = true;
lblResult.Text = "Account has not been activated.";
break;
default:
FormsAuthentication.RedirectFromLoginPage(inputUsername.Text, true);
Session["userlogin"] = inputUsername.Text;
bool isAuthenticated = true;
if (isAuthenticated == true)
{
// generate authentication ticket
FormsAuthenticationTicket authTicket = new FormsAuthenticationTicket(1, // version
inputUsername.Text, // username
DateTime.Now, // creation
DateTime.Now.AddMinutes(60), // Expiration
false, // Persistent
"" //No additional data supplied
);
// Encrypt the ticket.
string encryptedTicket = FormsAuthentication.Encrypt(authTicket);
// Create a cookie and add the encrypted ticket to the cookie
HttpCookie authCookie = new HttpCookie(FormsAuthentication.FormsCookieName,
encryptedTicket);
Response.Cookies.Add(authCookie);
// Redirect the user to the originally requested page
Response.Redirect(FormsAuthentication.GetRedirectUrl(inputUsername.Text, false));
}
break;
}
}
catch (Exception)
{
throw;
}
//// TEST
//Session["test"] = "1";
//Response.Redirect("~/Default.aspx");
}
//public static void LoadTypeEquipment(out Equipment[] objects, int? Equip_type_id, int? _id = null)
//{
// objects = null;
// string condition = "";
// string strY = DateTime.Now.Year.ToString(new System.Globalization.CultureInfo("en-US"));
// StringBuilder sql = new StringBuilder();
// sql.AppendLine(" select * FROM [dbo].[Equipment] ");
// //sql.AppendLine("where Equip_type_id = " + Equip_type_id);
// if (Equip_type_id != null)
// {
// if (condition == "")
// condition = condition + "WHERE\n ";
// else
// condition = condition + " AND ";
// condition = condition + "Equip_type_id = " + Equip_type_id + "\n";
// }
// if (_id != null)
// {
// if (condition == "")
// condition = condition + "WHERE\n ";
// else
// condition = condition + " AND ";
// condition = condition + " id = " + _id + "\n";
// }
// sql.AppendLine(condition);
// DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
// if (dt != null && dt.Rows.Count > 0)
// {
// List<Equipment> lstEquipment = new List<Equipment>();
// foreach (DataRow item in dt.Rows)
// {
// Equipment en = new Equipment();
// en.ID = int.Parse(item["Id"].ToString());
// en.Equip_type_id = int.Parse(item["Equip_type_id"].ToString());
// en.CreatedDate = item["CreatedDate"] != DBNull.Value ? Convert.ToDateTime(item["CreatedDate"].ToString()) : (DateTime?)null;
// en.CreatedBy = item["CreatedBy"].ToString();
// en.UpdatedDate = item["UpdatedDate"] != DBNull.Value ? Convert.ToDateTime(item["UpdatedDate"].ToString()) : (DateTime?)null;
// en.UpdatedBy = item["UpdatedBy"].ToString();
// en.Fullname = item["Fullname"].ToString();
// en.SN = item["SN"].ToString();
// en.Number = item["Number"].ToString();
// en.CostBuy = item["CostBuy"] != DBNull.Value ? Convert.ToDecimal(item["CostBuy"].ToString()) : (decimal?)null;//int.Parse(item["CostBuy"].ToString());
// en.CostRent = item["CostRent"] != DBNull.Value ? Convert.ToDecimal(item["CostRent"].ToString()) : (decimal?)null;// int.Parse(item["CostRent"].ToString());
// en.SupplierName = item["SupplierName"].ToString();
// en.BuyDate = item["BuyDate"] != DBNull.Value ? Convert.ToDateTime(item["BuyDate"].ToString()) : (DateTime?)null;
// en.ReceiptTax = item["ReceiptTax"].ToString();
// en.ExpireDate = item["ExpireDate"] != DBNull.Value ? Convert.ToDateTime(item["ExpireDate"].ToString()) : (DateTime?)null;
// lstEquipment.Add(en);
// }
// if (lstEquipment != null && lstEquipment.Any())
// {
// objects = lstEquipment.ToArray();
// }
// }
//}
public static void LoadTypeEquipment(out Equipment[] objects, int?Equip_type_id, int?[] _id = null)
{
objects = null;
string condition = "";
string strY = DateTime.Now.Year.ToString(new System.Globalization.CultureInfo("en-US"));
StringBuilder sql = new StringBuilder();
sql.AppendLine(" select * FROM [dbo].[Equipment] ");
//sql.AppendLine("where Equip_type_id = " + Equip_type_id);
if (Equip_type_id != null)
{
if (condition == "")
{
condition = condition + "WHERE\n ";
}
else
{
condition = condition + " AND ";
}
condition = condition + "Equip_type_id = " + Equip_type_id + "\n";
}
if (_id != null)
{
if (condition == "")
{
condition = condition + "WHERE\n ";
}
else
{
condition = condition + " AND ";
}
condition = condition + "id in (";
string str_id = "";
foreach (var id in _id)
{
if (!string.IsNullOrEmpty(str_id))
{
str_id += ",";
}
str_id = str_id + id;
}
condition = condition + " " + str_id + " ) \n";
}
sql.AppendLine(condition);
DataTable dt = ClassMain.ExecuteComandTable(sql.ToString());
if (dt != null && dt.Rows.Count > 0)
{
List <Equipment> lstEquipment = new List <Equipment>();
foreach (DataRow item in dt.Rows)
{
Equipment en = new Equipment();
en.ID = int.Parse(item["Id"].ToString());
en.Equip_type_id = int.Parse(item["Equip_type_id"].ToString());
en.CreatedDate = item["CreatedDate"] != DBNull.Value ? Convert.ToDateTime(item["CreatedDate"].ToString()) : (DateTime?)null;
en.CreatedBy = item["CreatedBy"].ToString();
en.UpdatedDate = item["UpdatedDate"] != DBNull.Value ? Convert.ToDateTime(item["UpdatedDate"].ToString()) : (DateTime?)null;
en.UpdatedBy = item["UpdatedBy"].ToString();
en.Fullname = item["Fullname"].ToString();
en.SN = item["SN"].ToString();
en.Number = item["Number"].ToString();
en.CostBuy = item["CostBuy"] != DBNull.Value ? Convert.ToDecimal(item["CostBuy"].ToString()) : (decimal?)null; //int.Parse(item["CostBuy"].ToString());
en.CostRent = item["CostRent"] != DBNull.Value ? Convert.ToDecimal(item["CostRent"].ToString()) : (decimal?)null; // int.Parse(item["CostRent"].ToString());
en.SupplierName = item["SupplierName"].ToString();
en.BuyDate = item["BuyDate"] != DBNull.Value ? Convert.ToDateTime(item["BuyDate"].ToString()) : (DateTime?)null;
en.ReceiptTax = item["ReceiptTax"].ToString();
en.ExpireDate = item["ExpireDate"] != DBNull.Value ? Convert.ToDateTime(item["ExpireDate"].ToString()) : (DateTime?)null;
lstEquipment.Add(en);
}
if (lstEquipment != null && lstEquipment.Any())
{
objects = lstEquipment.ToArray();
}
}
}