public static DataTable LoadDept()
{
string sql = "Select * from " + tb_name_dept + " Order by Name";
DataTable dt = ClassMain.ExecuteComandTable(sql);
return(dt);
}
public static ResultEN Insert_Equip(EquiptmentEN en)
{
// SqlCommand sqlCmd = new SqlCommand();
// sqlCmd.CommandText = "insert into " + tb_name_type +" ()";
List <ClassFieldValue> fields = new List <ClassFieldValue>();
fields.Add(new ClassFieldValue("Name", en.Name));
fields.Add(new ClassFieldValue("Fullname", en.Fullname));
fields.Add(new ClassFieldValue("Equip_type_id", en.Equip_type_id));
fields.Add(new ClassFieldValue("Number", en.EquipNo));
fields.Add(new ClassFieldValue("SN", en.SN));
fields.Add(new ClassFieldValue("SupplierName", en.SupplierName));
fields.Add(new ClassFieldValue("ReceiptTax", en.ReceiptTax));
fields.Add(new ClassFieldValue("BuyDate", en.BuyDate));
fields.Add(new ClassFieldValue("CostBuy", en.CostBuy));
fields.Add(new ClassFieldValue("CostRent", en.CostRent));
fields.Add(new ClassFieldValue("ExpireDate", en.ExpireDate));
fields.Add(new ClassFieldValue("CreatedBy", HttpContext.Current.User.Identity.Name));
fields.Add(new ClassFieldValue("CreatedDate", DateTime.Now));
ResultEN res = new ResultEN();
res = ClassMain.Insert(tb_name_master, fields);
return(res);
}
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 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 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 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 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 ResultEN Update_Equip(EquiptmentEN en)
{
List <ClassFieldValue> fields = new List <ClassFieldValue>();
fields.Add(new ClassFieldValue("Name", en.Name));
fields.Add(new ClassFieldValue("Fullname", en.Fullname));
fields.Add(new ClassFieldValue("Equip_type_id", en.Equip_type_id));
fields.Add(new ClassFieldValue("Number", en.EquipNo));
fields.Add(new ClassFieldValue("SN", en.SN));
fields.Add(new ClassFieldValue("SupplierName", en.SupplierName));
fields.Add(new ClassFieldValue("ReceiptTax", en.ReceiptTax));
fields.Add(new ClassFieldValue("BuyDate", en.BuyDate));
fields.Add(new ClassFieldValue("CostBuy", en.CostBuy));
fields.Add(new ClassFieldValue("CostRent", en.CostRent));
fields.Add(new ClassFieldValue("ExpireDate", en.ExpireDate));
fields.Add(new ClassFieldValue("UpdatedBy", HttpContext.Current.User.Identity.Name));
fields.Add(new ClassFieldValue("UpdatedDate", DateTime.Now));
ResultEN res = new ResultEN();
List <ClassFieldValue> fieldWhere = new List <ClassFieldValue>();
fieldWhere.Add(new ClassFieldValue("ID", en.ID));
res = ClassMain.Update(tb_name_master, fields, fieldWhere);
return(res);
}
public static ResultEN Delete(int id)
{
ResultEN res = new ResultEN();
string sql = "DELETE From Employees Where id=" + id;
res.result = ClassMain.ExecuteQuery(sql);
return(res);
}
public static ResultEN Delete(string C_ID)
{
ResultEN res = new ResultEN();
string sql = "DELETE From Customer Where C_ID='" + C_ID + "'";
res.result = ClassMain.ExecuteQuery(sql);
return(res);
}
public static string DeleteUser(string username)
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "delete from dbo.Users where username ='******'";
string res = ClassMain.strExecuteComand(sqlCmd);
return(res);
}
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()
{
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "select * from " + tb_name + " order by Position";
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 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);
}
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 ResultEN Delete_Type(EquiptmentEN en)
{
ResultEN res = new ResultEN();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "Delete From " + tb_name_master + " WHERE ID = " + en.ID;
sqlCmd.CommandType = CommandType.Text;
res.result = ClassMain.ExecuteComand(sqlCmd);
return(res);
}
public static ResultEN Insert_Equipment_SET(ref Equipment_SET en)
{
ResultEN res = new ResultEN();
// int? _Id = GetID("Equipment_SET", "SET_ID");
List <ClassFieldValue> fields = new List <ClassFieldValue>();
//en.SET_ID = _Id.Value;
fields = SetField_SET(fields, en);
res = ClassMain.Insert("Equipment_SET", fields, "SET_ID");
return(res);
}
public static string ChangePassword(string username, string password)
{
// EnCryptPassword
string salt = System.Configuration.ConfigurationManager.AppSettings["salt"];
string strEnCrypt = SHA256.EcryptPassword(password, salt);
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "update dbo.Users set Password = '******' where username =LOWER('" + username + "')";
string res = ClassMain.strExecuteComand(sqlCmd);
return(res);
}
public static ResultEN CreateUser(string username, string password, int role_id, int branch_id)
{
ResultEN res = new ResultEN();
username = username.Trim().ToLower();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "select username from Users where username ='******'";
// check duplicate username
int count = ClassMain.intExecuteComand(sqlCmd);
if (count > 0)
{
res.result = false;
res.returnValue = "Duplicate Username";
return(res);
}
string strEnCrypt = SHA256.EcryptPassword(password, salt);
StringBuilder Sql = new StringBuilder();
Sql.AppendLine("INSERT INTO [dbo].[Users]");
Sql.AppendLine(" ([Username]");
Sql.AppendLine(" ,[Password]");
Sql.AppendLine(" ,[Role_id]");
Sql.AppendLine(" ,[CreatedDate]");
Sql.AppendLine(" ,[CreatedBy]");
Sql.AppendLine(")");
Sql.AppendLine(" VALUES");
Sql.AppendLine(" ('" + username + "'");
Sql.AppendLine(" ,'" + strEnCrypt + "'");
Sql.AppendLine(" ," + role_id);
Sql.AppendLine(" ,getdate()");
Sql.AppendLine(" ,'" + HttpContext.Current.User.Identity.Name + "')");
sqlCmd = new SqlCommand();
sqlCmd.CommandText = Sql.ToString();
res.result = ClassMain.ExecuteComand(sqlCmd);
if (res.result)
{
res.returnValue = "Create Username Success.";
}
return(res);
}
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 ResultEN InsertPosition_Emp(JobPosition_EmpEN en)
{
ResultEN res = new ResultEN();
List <ClassFieldValue> fields = new List <ClassFieldValue>();
fields.Add(new ClassFieldValue("Emp_id", en.empid));
fields.Add(new ClassFieldValue("Pos_id", en.posid));
fields.Add(new ClassFieldValue("cost", en.cost));
fields.Add(new ClassFieldValue("CreatedDate", DateTime.Now));
fields.Add(new ClassFieldValue("CreatedBy", HttpContext.Current.User.Identity.Name));
res = ClassMain.Insert(tb_name_jopEmp, fields);
return(res);
}
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 ResultEN Insert(CustomerEN en)
{
ResultEN res = new ResultEN();
// Gen Employee code
string C_ID = GetC_ID();
List <ClassFieldValue> fields = new List <ClassFieldValue>();
en.C_ID = C_ID;
//fields.Add(new ClassFieldValue("C_ID", C_ID));
fields.Add(new ClassFieldValue("CreatedBy", HttpContext.Current.User.Identity.Name));
fields.Add(new ClassFieldValue("CreatedDate", DateTime.Now));
SetField(fields, en);
res = ClassMain.Insert(tb_name, fields);
return(res);
}
public static ResultEN Update(CustomerEN en)
{
ResultEN res = new ResultEN();
List <ClassFieldValue> fields = new List <ClassFieldValue>();
fields.Add(new ClassFieldValue("UpdatedBy", HttpContext.Current.User.Identity.Name));
fields.Add(new ClassFieldValue("UpdatedDate", DateTime.Now));
SetField(fields, en);
List <ClassFieldValue> fieldscon = new List <ClassFieldValue>();
fieldscon.Add(new ClassFieldValue("C_ID", en.C_ID));
res = ClassMain.Update(tb_name, fields, fieldscon);
return(res);
}
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 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 ResultEN Delete_Type(EquipmentTypeEN en)
{
ResultEN res = new ResultEN();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "Delete From " + tb_name_type + " WHERE ParentID = " + en.ID;
sqlCmd.CommandType = CommandType.Text;
// delete child
res.result = ClassMain.ExecuteComand(sqlCmd);
// delete parent
sqlCmd.CommandText = "Delete From " + tb_name_type + " WHERE ID = " + en.ID;
res.result = ClassMain.ExecuteComand(sqlCmd);
return(res);
}
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);
}
}
