public ReturnMachineMtn UpdateCheckerResult_ByMachineMtnID(MachineMtn machineMtn)
{
ReturnMachineMtn returnMachineMtn = new ReturnMachineMtn();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tMachineMtn_Update_CheckerResult";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = machineMtn.ID;
cmd.Parameters.Add(new SqlParameter("@CheckerID", SqlDbType.Int)).Value = machineMtn.CheckerID;
cmd.Parameters.Add(new SqlParameter("@CheckerResult", SqlDbType.VarChar)).Value = machineMtn.CheckerResult;
cmd.ExecuteNonQuery();
returnMachineMtn.Code = "00";
returnMachineMtn.Message = "Cập nhật dữ liệu thành công.";
}
}
}
catch (Exception ex)
{
returnMachineMtn.Code = "99";
returnMachineMtn.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnMachineMtn.Total = 0;
returnMachineMtn.lstMachineMtn = null;
mylog4net.Error("", ex);
}
return(returnMachineMtn);
}
public ReturnMachineMtn DeleteByID(int _ID)
{
ReturnMachineMtn returnMachineMtn = new ReturnMachineMtn();
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_tMachineMtn_DeleteByID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = _ID;
//cmd.Parameters.Add("@RETURN_CODE", SqlDbType.Int).Direction = ParameterDirection.Output;
//int RETURN_CODE = Convert.ToInt32(cmd.Parameters["@RETURN_CODE"].Value);
cmd.ExecuteNonQuery();
returnMachineMtn.Code = "00";
returnMachineMtn.Message = "Cập nhật dữ liệu thành công.";
}
}
}
catch (Exception ex)
{
returnMachineMtn.Code = "99";
returnMachineMtn.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnMachineMtn.Total = 0;
returnMachineMtn.lstMachineMtn = null;
mylog4net.Error("", ex);
}
return(returnMachineMtn);
}
public ReturnMachineMtn CountMachineMtByMachineID(string machineID)
{
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_CountByMachineID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@MachineID", SqlDbType.VarChar)).Value = machineID;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
if (sqlDr.HasRows)
{
lstMachineMtn = new List <MachineMtn>();
while (sqlDr.Read())
{
returnMachineMtn.Total = Convert.ToInt16(sqlDr[0].ToString());
returnMachineMtn.UserID = MyShareInfo.ID;
returnMachineMtn.UserName = MyShareInfo.UserName;
}
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;
}
}
}
}
}
catch (Exception ex)
{
returnMachineMtn.Code = "99";
returnMachineMtn.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnMachineMtn.Total = 0;
returnMachineMtn.lstMachineMtn = null;
mylog4net.Error("", ex);
}
return(returnMachineMtn);
}
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 ReturnMachineMtn DeleteMachineMtnDetailByID(int _ID)
//{
// //ReturnMachineMtn returnMachineMtn = new ReturnMachineMtn();
// //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_tMachineMtn_DeleteByID";
// // cmd.CommandType = CommandType.StoredProcedure;
// // cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = _ID;
// // //cmd.Parameters.Add("@RETURN_CODE", SqlDbType.Int).Direction = ParameterDirection.Output;
// // //int RETURN_CODE = Convert.ToInt32(cmd.Parameters["@RETURN_CODE"].Value);
// // cmd.ExecuteNonQuery();
// // returnMachineMtn.Code = "00";
// // returnMachineMtn.Message = "Cập nhật dữ liệu thành công.";
// // }
// // }
// //}
// //catch (Exception ex)
// //{
// // returnMachineMtn.Code = "99";
// // returnMachineMtn.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
// // returnMachineMtn.Total = 0;
// // returnMachineMtn.lstMachineMtn = null;
// // mylog4net.Error("", ex);
// //}
// //return returnMachineMtn;
//}
public ReturnMachineMtn GetMachineMtnbyID(int ID)
{
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_SelectByID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = ID;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd))
{
//if (float.Parse(cmd.Parameters["P_RETURN_CODE"].Value.ToString()) > 0)
//{
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();
//get MachineName.
ReturnMachine returnMachine = (new MachineDB()).GetbyID(machineMtn.MachineID);
if (returnMachine.Code == "00")
{
machineMtn.MachineName = returnMachine.lstMachine[0].MachineName;
}
//get OperatorName.
int _operatorID = 0;
ReturnUser returnUser;
if (Int32.TryParse(sqlDr["OperatorID"].ToString(), out _operatorID))
{
returnUser = (new UserDB()).GetbyID(_operatorID);
if (returnUser.Code == "00")
{
machineMtn.OperatorName = returnUser.lstUser[0].UserName;
}
}
machineMtn.Shift = Convert.ToInt16(sqlDr["Shift"].ToString());
machineMtn.MaintenanceDate = Convert.ToDateTime(sqlDr["MaintenanceDate"].ToString());
if (!String.IsNullOrEmpty(sqlDr["FrequencyID"].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());
}
if (sqlDr["CheckerID"].ToString() != "")
{
machineMtn.CheckerID = Convert.ToInt16(sqlDr["CheckerID"].ToString());
//get name.
int _CheckerID = 0;
ReturnUser returnChecker;
if (Int32.TryParse(sqlDr["CheckerID"].ToString(), out _operatorID))
{
returnChecker = (new UserDB()).GetbyID(_CheckerID);
if (returnChecker.Code == "00")
{
machineMtn.CheckerName = returnChecker.lstUser[0].UserName;
}
}
}
if (sqlDr["CheckerResult"].ToString() != "")
{
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;
}
}
}
}
}
catch (Exception ex)
{
returnMachineMtn.Code = "99";
returnMachineMtn.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnMachineMtn.Total = 0;
returnMachineMtn.lstMachineMtn = null;
mylog4net.Error("", ex);
}
return(returnMachineMtn);
}
public ReturnMachineMtn Insert(MachineMtn machineMtn)
{
ReturnMachineMtn returnMachineMtn = new ReturnMachineMtn();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tMachineMtn_InsertUpdate";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = machineMtn.ID;
cmd.Parameters.Add(new SqlParameter("@MachineID", SqlDbType.VarChar)).Value = machineMtn.MachineID;
cmd.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.VarChar)).Value = machineMtn.OperatorID;
if (machineMtn.Shift != 0)
{
cmd.Parameters.Add(new SqlParameter("@Shift", SqlDbType.SmallInt)).Value = machineMtn.Shift;
}
else
{
cmd.Parameters.Add(new SqlParameter("@Shift", SqlDbType.SmallInt)).Value = DBNull.Value;
}
cmd.Parameters.Add(new SqlParameter("@MaintenanceDate", SqlDbType.DateTime)).Value = machineMtn.MaintenanceDate;
cmd.Parameters.Add(new SqlParameter("@FrequencyID", SqlDbType.Int)).Value = machineMtn.FrequencyID;
cmd.Parameters.Add(new SqlParameter("@Result", SqlDbType.VarChar)).Value = machineMtn.Result;
cmd.Parameters.Add(new SqlParameter("@ResultContents", SqlDbType.NVarChar)).Value = "";
if (machineMtn.Week != 0)
{
cmd.Parameters.Add(new SqlParameter("@Week", SqlDbType.Int)).Value = machineMtn.Week;
}
else
{
cmd.Parameters.Add(new SqlParameter("@Week", SqlDbType.Int)).Value = DBNull.Value;
}
if (machineMtn.Month != 0)
{
cmd.Parameters.Add(new SqlParameter("@Month", SqlDbType.SmallInt)).Value = machineMtn.Month;
}
else
{
cmd.Parameters.Add(new SqlParameter("@Month", SqlDbType.SmallInt)).Value = DBNull.Value;
}
if (machineMtn.Year != 0)
{
cmd.Parameters.Add(new SqlParameter("@Year", SqlDbType.Int)).Value = machineMtn.Year;
}
else
{
cmd.Parameters.Add(new SqlParameter("@Year", SqlDbType.Int)).Value = DBNull.Value;
}
cmd.Parameters.Add(new SqlParameter("@CheckerID", SqlDbType.Int)).Value = machineMtn.CheckerID;
cmd.Parameters.Add(new SqlParameter("@CheckerResult", SqlDbType.VarChar)).Value = machineMtn.CheckerResult;
cmd.Parameters.Add("@LastID", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
returnMachineMtn.Code = "00";
returnMachineMtn.Message = "Cập nhật dữ liệu thành công.";
//if (returnMachineMtn.Code == "00")
// returnMachineMtn.LastID = Convert.ToInt32(cmd.Parameters["@LastID"].Value);
if (returnMachineMtn.Code == "00")
{
returnMachineMtn.LastID = Convert.ToInt32(cmd.Parameters["@LastID"].Value);
}
}
}
}
catch (Exception ex)
{
returnMachineMtn.Code = "99";
returnMachineMtn.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnMachineMtn.Total = 0;
returnMachineMtn.lstMachineMtn = null;
mylog4net.Error("", ex);
}
return(returnMachineMtn);
}