public ReturnToolList DeleteByID(string _ID)
{
ReturnToolList returnToolList = new ReturnToolList();
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_tToolList_DeleteByID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ToolID", SqlDbType.VarChar)).Value = _ID;
//cmd.Parameters.Add("@RETURN_CODE", SqlDbType.Int).Direction = ParameterDirection.Output;
//int RETURN_CODE = Convert.ToInt32(cmd.Parameters["@RETURN_CODE"].Value);
cmd.ExecuteNonQuery();
returnToolList.Code = "00";
returnToolList.Message = "Cập nhật dữ liệu thành công.";
}
}
}
catch (Exception ex)
{
returnToolList.Code = "99";
returnToolList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnToolList.Total = 0;
returnToolList.lstToolList = null;
}
return(returnToolList);
}
public ReturnToolList Insert(ToolList toolList)
{
ReturnToolList returnToolList = new ReturnToolList();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tToolList_InsertUpdate";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ToolID", SqlDbType.VarChar)).Value = toolList.ToolID;
cmd.Parameters.Add(new SqlParameter("@UserID", SqlDbType.VarChar)).Value = toolList.UserID;
cmd.Parameters.Add(new SqlParameter("@ToolType", SqlDbType.VarChar)).Value = toolList.ToolType;
cmd.Parameters.Add(new SqlParameter("@ItemCode", SqlDbType.VarChar)).Value = toolList.ItemCode;
cmd.Parameters.Add(new SqlParameter("@Maker", SqlDbType.VarChar)).Value = toolList.Maker;
cmd.Parameters.Add(new SqlParameter("@Specification", SqlDbType.NVarChar)).Value = toolList.Specification;
cmd.Parameters.Add(new SqlParameter("@ReceiveDate", SqlDbType.DateTime)).Value = toolList.ReceiveDate;
cmd.Parameters.Add(new SqlParameter("@StartUsing", SqlDbType.DateTime)).Value = toolList.StartUsing;
cmd.Parameters.Add(new SqlParameter("@LifeTime", SqlDbType.VarChar)).Value = toolList.LifeTime;
cmd.Parameters.Add(new SqlParameter("@ExpireDate", SqlDbType.DateTime)).Value = toolList.ExpireDate;
cmd.Parameters.Add(new SqlParameter("@LineID", SqlDbType.VarChar)).Value = toolList.LineID;
cmd.Parameters.Add(new SqlParameter("@Status", SqlDbType.VarChar)).Value = toolList.Status;
cmd.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar)).Value = toolList.Remark;
cmd.Parameters.Add(new SqlParameter("@ImageUrl", SqlDbType.VarChar)).Value = String.IsNullOrEmpty(toolList.ImageUrl) ? "" : toolList.ImageUrl;
//cmd.Parameters.Add(new SqlParameter("@CreatedDate", SqlDbType.DateTime)).Value = toolList.CreatedDate;
//cmd.Parameters.Add(new SqlParameter("@isActive", SqlDbType.Int)).Value = toolList.isActive;
//cmd.Parameters.Add("@RETURN_CODE", SqlDbType.Int).Direction = ParameterDirection.Output;
//int RETURN_CODE = Convert.ToInt32(cmd.Parameters["@RETURN_CODE"].Value);
cmd.ExecuteNonQuery();
returnToolList.Code = "00";
returnToolList.Message = "Cập nhật dữ liệu thành công.";
}
}
}
catch (Exception ex)
{
returnToolList.Code = "99";
returnToolList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnToolList.Total = 0;
returnToolList.lstToolList = null;
}
return(returnToolList);
}
public ReturnToolList SelectByCondition(string where)
{
List <ToolList> lstToolList = null;
ToolList toolList = null;
ReturnToolList returnToolList = new ReturnToolList();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
//cmd.Connection = _sqlConnection;
cmd.CommandText = "sp_tToolList_SelectByCondition";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@where", SqlDbType.NVarChar)).Value = where;
using (SqlDataReader sqlDr = ConnectSQLCommon.ExecuteDataReader(cmd, sqlConnection))
{
if (sqlDr.HasRows)
{
lstToolList = new List <ToolList>();
while (sqlDr.Read())
{
toolList = new ToolList();
toolList.ToolID = sqlDr["ToolID"].ToString();
toolList.UserID = sqlDr["UserID"].ToString();
toolList.UserName = sqlDr["UserName"].ToString();
toolList.ToolType = sqlDr["ToolType"].ToString();
toolList.ItemCode = sqlDr["ItemCode"].ToString();
toolList.Maker = sqlDr["Maker"].ToString();
toolList.Specification = sqlDr["Specification"].ToString();
toolList.ReceiveDate = DateTime.Parse(sqlDr["ReceiveDate"].ToString());
toolList.StartUsing = DateTime.Parse(sqlDr["StartUsing"].ToString());
//toolList.ReceiveDate = sqlDr["ReceiveDate"].ToString();
//toolList.StartUsing = sqlDr["StartUsing"].ToString();
toolList.LifeTime = sqlDr["LifeTime"].ToString();
toolList.ExpireDate = DateTime.Parse(sqlDr["ExpireDate"].ToString());
//toolList.ExpireDate = sqlDr["ExpireDate"].ToString();
toolList.LineID = sqlDr["LineID"].ToString();
toolList.Status = sqlDr["Status"].ToString();
toolList.Remark = sqlDr["Remark"].ToString();
toolList.ImageUrl = sqlDr["ImageUrl"].ToString();
toolList.CreatedDate = DateTime.Parse(sqlDr["CreatedDate"].ToString());
toolList.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstToolList.Add(toolList);
}
returnToolList.Code = "00";
returnToolList.Message = "Lấy dữ liệu thành công.";
//_ReturnToolList.Total = Convert.ToInt32(cmd.Parameters["P_TOTAL"].Value.ToString());
returnToolList.lstToolList = lstToolList;
returnToolList.UserID = MyShareInfo.ID;
returnToolList.UserName = MyShareInfo.UserName;
//}
}
else
{
returnToolList.Code = "01";
returnToolList.Message = "Không tồn tại bản ghi nào.";
returnToolList.Total = 0;
returnToolList.lstToolList = null;
returnToolList.UserID = MyShareInfo.ID;
returnToolList.UserName = MyShareInfo.UserName;
}
}
}
}
}
catch (Exception ex)
{
returnToolList.Code = "99";
returnToolList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnToolList.Total = 0;
returnToolList.lstToolList = null;
}
return(returnToolList);
}
public ReturnToolList SearchTools(ReturnToolList searchToolLists, int pageSize)
{
List <ToolList> lstToolList = null;
ToolList toolList = null;
ReturnToolList returnToolList = new ReturnToolList();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tToolList_SearchTools";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ToolID", SqlDbType.VarChar)).Value = string.Format("%{0}%", searchToolLists.aToolList.ToolID);
cmd.Parameters.Add(new SqlParameter("@ItemCode", SqlDbType.VarChar)).Value = string.Format("%{0}%", searchToolLists.aToolList.ItemCode);
cmd.Parameters.Add(new SqlParameter("@Maker", SqlDbType.VarChar)).Value = string.Format("%{0}%", searchToolLists.aToolList.Maker);
if (searchToolLists.aToolList.ReceiveDate == DateTime.MinValue)
{
cmd.Parameters.Add(new SqlParameter("@ReceiveDate", SqlDbType.DateTime)).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add(new SqlParameter("@ReceiveDate", SqlDbType.DateTime)).Value = searchToolLists.aToolList.ReceiveDate;
}
if (searchToolLists.aToolList.StartUsing == DateTime.MinValue)
{
cmd.Parameters.Add(new SqlParameter("@StartUsing", SqlDbType.DateTime)).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add(new SqlParameter("@StartUsing", SqlDbType.DateTime)).Value = searchToolLists.aToolList.StartUsing;
}
if (searchToolLists.aToolList.ExpireDate == DateTime.MinValue)
{
cmd.Parameters.Add(new SqlParameter("@ExpireDate", SqlDbType.DateTime)).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add(new SqlParameter("@ExpireDate", SqlDbType.DateTime)).Value = searchToolLists.aToolList.ExpireDate;
}
cmd.Parameters.Add(new SqlParameter("@LineID", SqlDbType.VarChar)).Value = string.Format("%{0}%", searchToolLists.aToolList.LineID);
cmd.Parameters.Add(new SqlParameter("@pageNumber", SqlDbType.Int)).Value = searchToolLists.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 (float.Parse(cmd.Parameters["P_RETURN_CODE"].Value.ToString()) > 0)
//{
if (sqlDr.HasRows)
{
lstToolList = new List <ToolList>();
while (sqlDr.Read())
{
toolList = new ToolList();
toolList.ToolID = sqlDr["ToolID"].ToString();
toolList.UserID = sqlDr["UserID"].ToString();
toolList.UserName = sqlDr["UserName"].ToString();
toolList.ToolType = sqlDr["ToolType"].ToString();
if (!String.IsNullOrEmpty(toolList.ToolType))
{
toolList.ToolTypeName = (new ToolTypeListDB()).GetToolTypeNamebyID(toolList.ToolType);
}
else
{
toolList.ToolTypeName = "";
}
toolList.ItemCode = sqlDr["ItemCode"].ToString();
toolList.Maker = sqlDr["Maker"].ToString();
toolList.Specification = sqlDr["Specification"].ToString();
toolList.ReceiveDate = DateTime.Parse(sqlDr["ReceiveDate"].ToString());
toolList.StartUsing = DateTime.Parse(sqlDr["StartUsing"].ToString());
toolList.LifeTime = sqlDr["LifeTime"].ToString();
toolList.ExpireDate = DateTime.Parse(sqlDr["ExpireDate"].ToString());
toolList.LineID = sqlDr["LineID"].ToString();
toolList.Status = sqlDr["Status"].ToString();
toolList.Remark = sqlDr["Remark"].ToString();
toolList.ImageUrl = sqlDr["ImageUrl"].ToString();
toolList.CreatedDate = DateTime.Parse(sqlDr["CreatedDate"].ToString());
toolList.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstToolList.Add(toolList);
}
returnToolList.Code = "00";
returnToolList.Message = "Lấy dữ liệu thành công.";
returnToolList.lstToolList = lstToolList;
returnToolList.UserID = MyShareInfo.ID;
returnToolList.UserName = MyShareInfo.UserName;
returnToolList.UserName = MyShareInfo.UserName;
}
else
{
returnToolList.Code = "01";
returnToolList.Message = "Không tồn tại bản ghi nào.";
returnToolList.Total = 0;
returnToolList.lstToolList = null;
returnToolList.UserName = MyShareInfo.UserName;
returnToolList.UserName = MyShareInfo.UserName;
}
}
//get return Totalpage value.
if (returnToolList.Code == "00")
{
returnToolList.Total = Convert.ToInt32(cmd.Parameters["@totalRow"].Value);
}
}
}
}
catch (Exception ex)
{
returnToolList.Code = "99";
returnToolList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnToolList.Total = 0;
returnToolList.lstToolList = null;
}
return(returnToolList);
}
public ReturnToolList ListAll()
{
List <ToolList> lstToolList = null;
ToolList toolList = null;
ReturnToolList returnToolList = new ReturnToolList();
try
{
using (SqlConnection sqlConnection = ConnectSQLCommon.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand("", sqlConnection))
{
cmd.CommandText = "sp_tToolList_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)
{
lstToolList = new List <ToolList>();
while (sqlDr.Read())
{
toolList = new ToolList();
toolList.ToolID = sqlDr["ToolID"].ToString();
toolList.UserID = sqlDr["UserID"].ToString();
if (!String.IsNullOrEmpty(toolList.UserID))
{
toolList.UserName = (new UserDB()).getUserNameByID(Convert.ToInt16(toolList.UserID));
}
else
{
toolList.UserName = "";
}
toolList.ToolType = sqlDr["ToolType"].ToString();
if (!String.IsNullOrEmpty(toolList.ToolType))
{
toolList.ToolTypeName = (new ToolTypeListDB()).GetToolTypeNamebyID(toolList.ToolType);
}
else
{
toolList.ToolTypeName = "";
}
toolList.ItemCode = sqlDr["ItemCode"].ToString();
toolList.Maker = sqlDr["Maker"].ToString();
toolList.Specification = sqlDr["Specification"].ToString();
toolList.ReceiveDate = DateTime.Parse(sqlDr["ReceiveDate"].ToString());
toolList.StartUsing = DateTime.Parse(sqlDr["StartUsing"].ToString());
//toolList.ReceiveDate = sqlDr["ReceiveDate"].ToString();
//toolList.StartUsing = sqlDr["StartUsing"].ToString();
toolList.LifeTime = sqlDr["LifeTime"].ToString();
toolList.ExpireDate = DateTime.Parse(sqlDr["ExpireDate"].ToString());
//toolList.ExpireDate = sqlDr["ExpireDate"].ToString();
toolList.LineID = sqlDr["LineID"].ToString();
toolList.Status = sqlDr["Status"].ToString();
toolList.Remark = sqlDr["Remark"].ToString();
toolList.ImageUrl = sqlDr["ImageUrl"].ToString();
toolList.CreatedDate = DateTime.Parse(sqlDr["CreatedDate"].ToString());
toolList.isActive = SMCommon.ConvertToBoolean(sqlDr["isActive"].ToString());
lstToolList.Add(toolList);
}
returnToolList.Code = "00";
returnToolList.Message = "Lấy dữ liệu thành công.";
returnToolList.lstToolList = lstToolList;
returnToolList.UserID = MyShareInfo.ID;
returnToolList.UserName = MyShareInfo.UserName;
}
else
{
returnToolList.Code = "01";
returnToolList.Message = "Không tồn tại bản ghi nào.";
returnToolList.Total = 0;
returnToolList.lstToolList = null;
returnToolList.UserID = MyShareInfo.ID;
returnToolList.UserName = MyShareInfo.UserName;
}
}
}
}
}
catch (Exception ex)
{
returnToolList.Code = "99";
returnToolList.Message = "Lỗi xử lý dữ liệu: " + ex.ToString();
returnToolList.Total = 0;
returnToolList.lstToolList = null;
}
return(returnToolList);
}
