public ReturnUser DeleteByID(int _ID)
{
ReturnUser returnUser = new ReturnUser();
try
{
// Gọi vào DB để lấy dữ liệu.
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tUser_DeleteByID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = _ID;
cmd.ExecuteNonQuery();
returnUser.Code = "00";
returnUser.Message = "Cập nhật dữ liệu thành công.";
}
}
}
catch (Exception ex)
{
returnUser.Code = "99";
returnUser.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnUser.Total = 0;
returnUser.lstUser = null;
mylog4net.Error("", ex);
}
return(returnUser);
}
public ReturnUser UserAll()
{
List <User> lstUser = null;
User user = null;
ReturnUser returnUser = new ReturnUser();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tUser_SelectAll";
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
//if (float.Parse(cmd.Parameters["P_RETURN_CODE"].Value.ToString()) > 0)
//{
if (sqlDr.HasRows)
{
lstUser = new List <User>();
while (sqlDr.Read())
{
user = new User();
user.ID = int.Parse(sqlDr["ID"].ToString());
user.FullName = sqlDr["FullName"].ToString();
user.UserName = sqlDr["UserName"].ToString();
user.PassWord = sqlDr["PassWord"].ToString();
user.MobileNumber = sqlDr["MobileNumber"].ToString();
user.FactoryID = sqlDr["FactoryID"].ToString();
user.isActive = int.Parse(sqlDr["isActive"].ToString());
//user.RoleID = int.Parse(sqlDr["RoleID"].ToString());
lstUser.Add(user);
}
returnUser.Code = "00";
returnUser.Message = "Lấy dữ liệu thành công.";
returnUser.lstUser = lstUser;
}
else
{
returnUser.Code = "01";
returnUser.Message = "Không tồn tại bản ghi nào.";
returnUser.Total = 0;
returnUser.lstUser = null;
}
}
}
}
}
catch (Exception ex)
{
returnUser.Code = "99";
returnUser.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnUser.Total = 0;
returnUser.lstUser = null;
mylog4net.Error("", ex);
}
return(returnUser);
}
public ReturnUser SearchByUserName(string where)
{
List <User> lstUser = null;
User user = null;
ReturnUser returnUser = new ReturnUser();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tUserMenu_SearchByUserName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@where", SqlDbType.NVarChar)).Value = where;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
//if (float.Parse(cmd.Parameters["P_RETURN_CODE"].Value.ToString()) > 0)
//{
if (sqlDr.HasRows)
{
lstUser = new List <User>();
while (sqlDr.Read())
{
user = new User();
user.ID = int.Parse(sqlDr["UserID"].ToString());
user.UserName = sqlDr["UserName"].ToString();
lstUser.Add(user);
}
returnUser.Code = "00";
returnUser.Message = "Lấy dữ liệu thành công.";
returnUser.lstUser = lstUser;
}
else
{
returnUser.Code = "01";
returnUser.Message = "Không tồn tại bản ghi nào.";
returnUser.Total = 0;
returnUser.lstUser = null;
}
}
}
}
}
catch (Exception ex)
{
returnUser.Code = "99";
returnUser.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnUser.Total = 0;
returnUser.lstUser = null;
mylog4net.Error("", ex);
}
return(returnUser);
}
public ReturnUser Insert(User user)
{
ReturnUser returnUser = new ReturnUser();
if (user.MobileNumber == null)
{
user.MobileNumber = "";
}
try
{
//user.PassWord = SMCommon.MD5Endcoding(user.PassWord).ToLower();
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tUser_InsertUpdate";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = user.ID;
cmd.Parameters.Add(new SqlParameter("@FullName", SqlDbType.NVarChar)).Value = user.FullName;
cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar)).Value = user.UserName;
cmd.Parameters.Add(new SqlParameter("@PassWord", SqlDbType.VarChar)).Value = user.PassWord;
cmd.Parameters.Add(new SqlParameter("@MobileNumber", SqlDbType.NVarChar)).Value = user.MobileNumber;
cmd.Parameters.Add(new SqlParameter("@FactoryID", SqlDbType.NVarChar)).Value = user.FactoryID;
cmd.Parameters.Add(new SqlParameter("@isActive", SqlDbType.Int)).Value = user.isActive;
//cmd.Parameters.Add(new SqlParameter("@RoleID", SqlDbType.Int)).Value = user.RoleID;
cmd.ExecuteNonQuery();
returnUser.Code = "00";
returnUser.Message = "Cập nhật dữ liệu thành công.";
}
}
}
catch (Exception ex)
{
returnUser.Code = "99";
returnUser.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnUser.Total = 0;
returnUser.lstUser = null;
mylog4net.Error("", ex);
}
return(returnUser);
}
public ReturnMachineMtn SelectByPage(string where, int pageNumber, int pageSize)
{
List <MachineMtn> lstMachineMtn = null;
MachineMtn machineMtn = null;
ReturnMachineMtn returnMachineMtn = new ReturnMachineMtn();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tMachineMtn_SearchBypage";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@where", SqlDbType.NVarChar)).Value = where;
//paging
cmd.Parameters.Add(new SqlParameter("@pageNumber", SqlDbType.Int)).Value = pageNumber;
cmd.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int)).Value = pageSize;
cmd.Parameters.Add("@totalRow", SqlDbType.Int).Direction = ParameterDirection.Output;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
if (sqlDr.HasRows)
{
lstMachineMtn = new List <MachineMtn>();
while (sqlDr.Read())
{
machineMtn = new MachineMtn();
machineMtn.ID = Convert.ToInt16(sqlDr["ID"].ToString());
machineMtn.MachineID = sqlDr["MachineID"].ToString();
machineMtn.MachineName = sqlDr["MachineName"].ToString();
machineMtn.OperatorID = sqlDr["OperatorID"].ToString();
machineMtn.OperatorName = sqlDr["UserName"].ToString(); // UserName in Sql = operatorName in C#
if (!String.IsNullOrEmpty(sqlDr["Shift"].ToString()))
{
machineMtn.Shift = Convert.ToInt16(sqlDr["Shift"].ToString());
}
machineMtn.MaintenanceDate = Convert.ToDateTime(sqlDr["MaintenanceDate"].ToString());
machineMtn.FrequencyID = Convert.ToInt16(sqlDr["FrequencyID"].ToString());
machineMtn.Result = sqlDr["Result"].ToString();
machineMtn.ResultContents = sqlDr["ResultContents"].ToString();
if (!String.IsNullOrEmpty(sqlDr["Month"].ToString()))
{
machineMtn.Month = Convert.ToInt16(sqlDr["Month"].ToString());
}
if (!String.IsNullOrEmpty(sqlDr["Year"].ToString()))
{
machineMtn.Year = Convert.ToInt16(sqlDr["Year"].ToString());
}
if (!String.IsNullOrEmpty(sqlDr["Week"].ToString()))
{
machineMtn.Week = Convert.ToInt16(sqlDr["Week"].ToString());
}
machineMtn.CheckerID = Convert.ToInt16(sqlDr["CheckerID"].ToString());
//get Checker name.
int _checkerID = 0;
machineMtn.CheckerName = "";
if (Int32.TryParse(sqlDr["CheckerID"].ToString(), out _checkerID))
{
ReturnUser returnUser = (new UserDB()).GetbyID(_checkerID);
if (returnUser.Code == "00")
{
machineMtn.CheckerName = returnUser.lstUser[0].UserName;
}
}
machineMtn.CheckerResult = sqlDr["CheckerResult"].ToString();
lstMachineMtn.Add(machineMtn);
}
returnMachineMtn.Code = "00";
returnMachineMtn.Message = "Lấy dữ liệu thành công.";
returnMachineMtn.lstMachineMtn = lstMachineMtn;
returnMachineMtn.UserID = MyShareInfo.ID;
returnMachineMtn.UserName = MyShareInfo.UserName;
}
else
{
returnMachineMtn.Code = "01";
returnMachineMtn.Message = "Không tồn tại bản ghi nào.";
returnMachineMtn.Total = 0;
returnMachineMtn.lstMachineMtn = null;
returnMachineMtn.UserID = MyShareInfo.ID;
returnMachineMtn.UserName = MyShareInfo.UserName;
}
}
//paging.
if (returnMachineMtn.Code == "00")
{
returnMachineMtn.Total = Convert.ToInt32(cmd.Parameters["@totalRow"].Value);
}
}
}
}
catch (Exception ex)
{
returnMachineMtn.Code = "99";
returnMachineMtn.Message = "Lỗi xử lý dữ liệu/Error: " + ex.ToString();
returnMachineMtn.Total = 0;
returnMachineMtn.lstMachineMtn = null;
mylog4net.Error("public ReturnMachineMtn SelectByCondition(string where, int pageSize) ", ex);
}
return(returnMachineMtn);
}
public ReturnUser CheckLogin(string username, string password)
{
User user = null;
List <User> lstUser;
ReturnUser returnUser = new ReturnUser();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
//cmd.Connection = _sqlConnection;
cmd.CommandText = "sp_tUser_CheckLogin";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar)).Value = username;
cmd.Parameters.Add(new SqlParameter("@password", SqlDbType.VarChar)).Value = password;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd, sqlConnection))
{
if (sqlDr.HasRows)
{
lstUser = new List <User>();
while (sqlDr.Read())
{
user = new User();
user.ID = int.Parse(sqlDr["ID"].ToString());
user.FullName = sqlDr["FullName"].ToString();
user.UserName = sqlDr["UserName"].ToString();
user.PassWord = sqlDr["PassWord"].ToString();
user.MobileNumber = sqlDr["MobileNumber"].ToString();
user.FactoryID = sqlDr["FactoryID"].ToString();
user.isActive = int.Parse(sqlDr["isActive"].ToString());
// user.RoleID = int.Parse(sqlDr["RoleID"].ToString());
lstUser.Add(user);
}
returnUser.Code = "00";
returnUser.Message = "Lấy dữ liệu thành công.";
returnUser.lstUser = lstUser;
}
else
{
returnUser.Code = "01";
returnUser.Message = "Không tồn tại bản ghi nào.";
returnUser.Total = 0;
returnUser.lstUser = null;
}
}
}
}
}
catch (Exception ex)
{
returnUser.Code = "99";
returnUser.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnUser.Total = 0;
returnUser.lstUser = null;
mylog4net.Error("", ex);
}
return(returnUser);
}
//MachinetMtnReport step
public ReturnMachineMtnReportData getData(MachineMtnReportDataSearch machineMtnReportDataSearch)
{
ReturnMachineMtnReportData returnMachineMtnReportData = new ReturnMachineMtnReportData();
if (String.IsNullOrEmpty(machineMtnReportDataSearch.MachineID))
{
returnMachineMtnReportData.Code = "99";
returnMachineMtnReportData.Message = "Không có MachineID/Input MachineID! ";
returnMachineMtnReportData.Total = 0;
returnMachineMtnReportData.lstMachineMtnReportData = new List <MachineMtnReportData>();
return(returnMachineMtnReportData);
}
List <MachineMtnReportData> lstMachineMtnReportData = null;
MachineMtnReportData MachineMtnReportData = null;
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tMachineMtnReportDatas";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MachineID", machineMtnReportDataSearch.MachineID);
cmd.Parameters.AddWithValue("@OperatorID", machineMtnReportDataSearch.OperatorID);
cmd.Parameters.AddWithValue("@Shift", machineMtnReportDataSearch.Shift);
cmd.Parameters.AddWithValue("@FrequencyID ", machineMtnReportDataSearch.FrequencyID);
if (machineMtnReportDataSearch.MaintenanceDate != DateTime.MinValue)
{
cmd.Parameters.Add(new SqlParameter("@MaintenanceDate", SqlDbType.DateTime)).Value = machineMtnReportDataSearch.MaintenanceDate;
}
cmd.Parameters.Add(new SqlParameter("@FromDate", SqlDbType.DateTime)).Value = machineMtnReportDataSearch.FromDate;
cmd.Parameters.Add(new SqlParameter("@ToDate", SqlDbType.DateTime)).Value = machineMtnReportDataSearch.ToDate;
cmd.Parameters.AddWithValue("@FromInt", machineMtnReportDataSearch.FromInt);
cmd.Parameters.AddWithValue("@ToInt", machineMtnReportDataSearch.ToInt);
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
if (sqlDr.HasRows)
{
lstMachineMtnReportData = new List <MachineMtnReportData>();
while (sqlDr.Read())
{
MachineMtnReportData = new MachineMtnReportData();
//MachineMtnReportData.MachineID = sqlDr["MachineID"].ToString();
//MachineMtnReportData.MachineName = sqlDr["MachineName"].ToString();
MachineMtnReportData.OperatorID = sqlDr["OperatorID"].ToString();
//MachineMtnReportData.OperatorName = sqlDr["UserName"].ToString(); // UserName in Sql = operatorName in C#
if (!String.IsNullOrEmpty(sqlDr["Shift"].ToString()))
{
MachineMtnReportData.Shift = Convert.ToInt16(sqlDr["Shift"].ToString());
}
MachineMtnReportData.MaintenanceDate = Convert.ToDateTime(sqlDr["MaintenanceDate"].ToString());
//MachineMtnReportData.FrequencyID = Convert.ToInt16(sqlDr["FrequencyID"].ToString());
MachineMtnReportData.TotalResult = sqlDr["TotalResult"] != null ? sqlDr["TotalResult"].ToString() : "";
//if (!String.IsNullOrEmpty(sqlDr["TotalResultContents"].ToString()))
// MachineMtnReportData.TotalResultContents = sqlDr["TotalResultContents"].ToString();
if (!String.IsNullOrEmpty(sqlDr["Month"].ToString()))
{
MachineMtnReportData.Month = Convert.ToInt16(sqlDr["Month"].ToString());
}
if (!String.IsNullOrEmpty(sqlDr["Year"].ToString()))
{
MachineMtnReportData.Year = Convert.ToInt16(sqlDr["Year"].ToString());
}
if (!String.IsNullOrEmpty(sqlDr["Week"].ToString()))
{
MachineMtnReportData.Week = Convert.ToInt16(sqlDr["Week"].ToString());
}
MachineMtnReportData.CheckerID = Convert.ToInt16(sqlDr["CheckerID"].ToString());
if (!String.IsNullOrEmpty(sqlDr["MachinePartID"].ToString()))
{
MachineMtnReportData.MachinePartID = Convert.ToInt16(sqlDr["MachinePartID"].ToString());
MachineMtnReportData.MachinePartName = sqlDr["MachinePartName"].ToString();
}
MachineMtnReportData.ContentMtn = sqlDr["ContentMtn"].ToString();
MachineMtnReportData.ToolMtn = sqlDr["ToolMtn"].ToString();
MachineMtnReportData.MethodMtn = sqlDr["MethodMtn"].ToString();
MachineMtnReportData.Standard = sqlDr["Standard"].ToString();
MachineMtnReportData.MtnDetailResult = sqlDr["MtnDetailResult"].ToString();
MachineMtnReportData.MtnDetailResultContents = sqlDr["MtnDetailResultContents"].ToString();
//get Checker name.
int _checkerID = 0;
MachineMtnReportData.CheckerName = "";
if (Int32.TryParse(sqlDr["CheckerID"].ToString(), out _checkerID))
{
ReturnUser returnUser = (new UserDB()).GetbyID(_checkerID);
if (returnUser.Code == "00")
{
MachineMtnReportData.CheckerName = returnUser.lstUser[0].UserName;
}
}
MachineMtnReportData.CheckerResult = sqlDr["CheckerResult"].ToString();
lstMachineMtnReportData.Add(MachineMtnReportData);
}
returnMachineMtnReportData.Code = "00";
returnMachineMtnReportData.Message = "Lấy dữ liệu thành công.";
returnMachineMtnReportData.lstMachineMtnReportData = lstMachineMtnReportData;
}
else
{
returnMachineMtnReportData.Code = "01";
returnMachineMtnReportData.Message = "Không tồn tại bản ghi nào.";
returnMachineMtnReportData.Total = 0;
returnMachineMtnReportData.lstMachineMtnReportData = null;
}
}
//paging.
//if (returnMachineMtnReportData.Code == "00")
// returnMachineMtnReportData.Total = Convert.ToInt32(cmd.Parameters["@totalRow"].Value);
}
}
}
catch (Exception ex)
{
returnMachineMtnReportData.Code = "99";
returnMachineMtnReportData.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnMachineMtnReportData.Total = 0;
returnMachineMtnReportData.lstMachineMtnReportData = null;
mylog4net.Error("public ReturnMachineMtnReportData SelectByCondition(string where, int pageSize) ", ex);
}
return(returnMachineMtnReportData);
}