public static CommonData.ReturnCode InsertData(CategoryDTO dto)
{
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
using (MySqlConnection connect = new MySqlConnection(constr))
{
using (MySqlCommand command = new MySqlCommand(
@"INSERT INTO product_category(`code`, `parent_id`, `name`,
`description`, `created_datetime`, `created_by`,
`updated_datetime`, `updated_by`)
VALUES
(@code, @parentID, @name, @description,
SYSDATE(), @created_by,
SYSDATE(), @updated_by)", connect))
{
command.Parameters.AddWithValue("@code", dto.code);
command.Parameters.AddWithValue("@parentID", dto.parent_id);
command.Parameters.AddWithValue("@name", dto.name);
command.Parameters.AddWithValue("@description", dto.decription);
command.Parameters.AddWithValue("@created_by", dto.created_by);
command.Parameters.AddWithValue("@updated_by", dto.updated_by);
connect.Open();
command.ExecuteNonQuery();
}
}
return(returnCode);
}
public static CommonData.ReturnCode InsertData(MeasureDTO dto)
{
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
try
{
using (MySqlConnection connect = new MySqlConnection(constr))
{
using (MySqlCommand command = new MySqlCommand(
@"INSERT INTO product_measure(
`code`, `name`, `description`
, `created_datetime`, `created_by`,`updated_datetime`,`updated_by`
) VALUES (
@code, @name, @description
, SYSDATE(), @created_by, SYSDATE(), @updated_by)", connect))
{
command.Parameters.AddWithValue("@code", dto.code);
command.Parameters.AddWithValue("@name", dto.name);
command.Parameters.AddWithValue("@description", dto.description);
command.Parameters.AddWithValue("@created_by", dto.created_by);
command.Parameters.AddWithValue("@updated_by", dto.updated_by);
connect.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
returnCode = CommonData.ReturnCode.UnSuccess;
}
return(returnCode);
}
public static CommonData.ReturnCode UpdateData(ItemDTO dto)
{
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
using (MySqlConnection connect = new MySqlConnection(constr))
{
using (MySqlCommand command = new MySqlCommand(
@"Update product_item Set
`name` = @name, `specification` = @specification, `description` = @description,
`category_id` = @category_id, `discontinued_datetime` = @discontinued_datetime,
`dangerous` = @dangerous, `inventory_measure_id` = @inventory_measure_id,
`inventory_expired` = @inventory_expired, `inventory_standard_cost` = @inventory_standard_cost,
`inventory_list_price` = @inventory_list_price, `manufacture_day` = @manufacture_day,
`manufacture_make` = @manufacture_make, `manufacture_tool` = @manufacture_tool,
`manufacture_finished_goods` = @manufacture_finished_goods,
`manufacture_size` = @manufacture_size, `manufacture_size_measure_id` = @manufacture_size_measure_id,
`manufacture_weight` = @manufacture_weight, `manufacture_weight_measure_id` = @manufacture_weight_measure_id,
`manufacture_style` = @manufacture_style,
`manufacture_class` = @manufacture_class, `manufacture_color` = @manufacture_color,
`updated_datetime` = SYSDATE(), `updated_by` = @updated_by Where `id` = @ID", connect))
{
try
{
command.Parameters.AddWithValue("@name", dto.name);
command.Parameters.AddWithValue("@specification", dto.specification);
command.Parameters.AddWithValue("@description", dto.description);
command.Parameters.AddWithValue("@category_id", dto.category_id);
command.Parameters.AddWithValue("@discontinued_datetime", dto.discontinued_datetime);
command.Parameters.AddWithValue("@dangerous", dto.dangerous);
command.Parameters.AddWithValue("@inventory_measure_id", dto.inventory_measure_id);
command.Parameters.AddWithValue("@inventory_expired", dto.inventory_expired);
command.Parameters.AddWithValue("@inventory_standard_cost", dto.inventory_standard_cost);
command.Parameters.AddWithValue("@inventory_list_price", dto.inventory_list_price);
command.Parameters.AddWithValue("@manufacture_day", dto.manufacture_day);
command.Parameters.AddWithValue("@manufacture_make", dto.manufacture_make);
command.Parameters.AddWithValue("@manufacture_tool", dto.manufacture_tool);
command.Parameters.AddWithValue("@manufacture_finished_goods", dto.manufacture_finished_goods);
command.Parameters.AddWithValue("@manufacture_size", dto.manufacture_size);
command.Parameters.AddWithValue("@manufacture_size_measure_id", dto.manufacture_size_measure_id);
command.Parameters.AddWithValue("@manufacture_weight", dto.manufacture_weight);
command.Parameters.AddWithValue("@manufacture_weight_measure_id", dto.manufacture_weight_measure_id);
command.Parameters.AddWithValue("@manufacture_style", dto.manufacture_style);
command.Parameters.AddWithValue("@manufacture_class", dto.manufacture_class);
command.Parameters.AddWithValue("@manufacture_color", dto.manufacture_color);
command.Parameters.AddWithValue("@updated_by", dto.updated_by);
command.Parameters.AddWithValue("@ID", dto.id);
connect.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
returnCode = CommonData.ReturnCode.UnSuccess;
}
}
}
return(returnCode);
}
public static CommonData.ReturnCode UpdateData(MeasureDTO dto)
{
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
using (MySqlConnection connect = new MySqlConnection(constr))
{
using (MySqlCommand command = new MySqlCommand(
@"Update product_measure Set
`name` = @name, `description` = @description,
`updated_datetime` = SYSDATE(), `updated_by` = @updated_by Where `id` = @ID", connect))
{
try
{
command.Parameters.AddWithValue("@name", dto.name);
command.Parameters.AddWithValue("@description", dto.description);
command.Parameters.AddWithValue("@updated_by", dto.updated_by);
command.Parameters.AddWithValue("@ID", dto.id);
connect.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
returnCode = CommonData.ReturnCode.UnSuccess;
}
}
}
return(returnCode);
}
public static CommonData.ReturnCode UpdateData(CategoryDTO dto)
{
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
using (MySqlConnection connect = new MySqlConnection(constr))
{
using (MySqlCommand command = new MySqlCommand(
@"UPDATE product_category pc SET pc.`code` = @code,
pc.`parent_id` = @parentID,
pc.`name` = @name,
pc.`description` = @description,
pc.`updated_datetime` = SYSDATE(),
pc.`updated_by` = @updated_by WHERE pc.`id` = @ID", connect))
{
try
{
command.Parameters.AddWithValue("@code", dto.code);
command.Parameters.AddWithValue("@parentID", dto.parent_id);
command.Parameters.AddWithValue("@name", dto.name);
command.Parameters.AddWithValue("@description", dto.decription);
command.Parameters.AddWithValue("@updated_by", dto.updated_by);
command.Parameters.AddWithValue("@ID", dto.id);
connect.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
returnCode = CommonData.ReturnCode.UnSuccess;
}
}
}
return(returnCode);
}
public static CommonData.ReturnCode SelectSimpleData(out List <MeasureDropdownlistDTO> list)
{
list = new List <MeasureDropdownlistDTO>();
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("SELECT id, code, name FROM product_measure", con))
{
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count != 0)
{
foreach (DataRow item in dt.Rows)
{
MeasureDropdownlistDTO dto = new MeasureDropdownlistDTO();
dto.id = int.Parse(item["id"].ToString());
dto.code = item["code"].ToString();
dto.name = item["name"].ToString();
list.Add(dto);
}
}
}
}
}
return(returnCode);
}
public ActionResult UpdateItem(ItemDTO item, string layout)
{
if (ModelState.IsValid)
{
item.created_by = 123;
item.updated_by = 123;
ItemBL itemBL = new ItemBL();
CommonData.ReturnCode returnCode = itemBL.UpdateData(item);
if (returnCode == CommonData.ReturnCode.Success)
{
TempData["Success"] = "Update Successfully!";
}
else
{
TempData["Error"] = "Update fail";
}
Session["model.Item"] = null;
return(RedirectToAction("Item"));
}
else
{
CheckRegex();
}
if (!layout.IsNotNullOrEmpty())
{
ViewBag.Layout = "~/Views/Shared/_Layout.cshtml";
}
return(View("Update", LoadItemAddForm(item)));
}
public static CommonData.ReturnCode DeleteData(int id)
{
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
using (MySqlConnection connect = new MySqlConnection(constr))
{
using (MySqlCommand command = new MySqlCommand(
@"Delete FROM product_category Where `id` = @ID;
Update product_category set parent_id = 0 Where `id` = @ID;
Update product_item set category_id = 0 Where category_id = @ID;", connect))
{
try
{
connect.Open();
command.Parameters.AddWithValue("@ID", id);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
returnCode = CommonData.ReturnCode.UnSuccess;
}
}
}
return(returnCode);
}
public static CommonData.ReturnCode SelectSimpleData(out List <CategoryDetailDTO> list)
{
list = new List <CategoryDetailDTO>();
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("SELECT id, name FROM product_category", con))
{
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count != 0)
{
foreach (DataRow category in dt.Rows)
{
CategoryDetailDTO dto = new CategoryDetailDTO();
dto.id = int.Parse(category["id"].ToString());
dto.name = category["name"].ToString();
list.Add(dto);
}
}
}
}
}
return(returnCode);
}
public ActionResult UpdateMeasure(MeasureDTO measure)
{
try
{
if (ModelState.IsValid)
{
measure.created_by = 123;
measure.updated_by = 123;
CommonData.ReturnCode returnCode = measureBL.UpdateData(measure);
if (returnCode == CommonData.ReturnCode.Success)
{
TempData["Success"] = "Update Successfully!";
}
else
{
TempData["Error"] = "Update fail";
}
Session["model.Measure"] = null;
return(RedirectToAction("Measure"));
}
}
catch (DataException dex)
{
ModelState.AddModelError("", "Lỗi không xác định");
return(RedirectToAction("SubmissionFailed", measure));
}
return(View("Update", measure));
}
public ActionResult AddItem(ItemDTO item)
{
try
{
if (ModelState.IsValid)
{
if (!item.code.Contains(" "))
{
item.created_by = 123;
ItemBL itemBL = new ItemBL();
int count = itemBL.CountData(new ItemSearchDTO()
{
code_key = item.code
});
if (count == 0)
{
CommonData.ReturnCode returnCode = itemBL.InsertData(item);
if (returnCode == CommonData.ReturnCode.Success)
{
TempData["Success"] = "Inserted Successfully!";
}
else
{
TempData["Error"] = "Insert fail";
}
Session["model.Item"] = null;
}
else
{
ModelState.AddModelError("code", "The Code already is existed! ");
return(RedirectToAction("Add"));
}
return(RedirectToAction("Item"));
}
else
{
ModelState.AddModelError("code", "The code must not have spaces ");
}
}
else
{
CheckRegex();
}
}
catch (DataException dex)
{
ModelState.AddModelError("", "Lỗi không xác định");
}
return(View("Add", LoadItemAddForm(item)));
}
public ActionResult AddCategory(CategoryDTO category)
{
try
{
if (ModelState.IsValid)
{
if (!category.code.Contains(" "))
{
int count = categoryBL.CountData(new CategoryDTO()
{
code_key = category.code
});
if (count == 0)
{
category.created_by = 123;
category.updated_by = 123;
CommonData.ReturnCode returnCode = categoryBL.InsertData(category);
if (returnCode == CommonData.ReturnCode.Success)
{
TempData["Success"] = "Inserted Successfully!";
}
else
{
TempData["Error"] = "Insert fail";
}
Session["model.Category"] = null;
return(RedirectToAction("Category"));
}
else
{
ModelState.AddModelError("code", "The Code already is existed!");
return(RedirectToAction("Add"));
}
}
else
{
ModelState.AddModelError("code", "The code must not have spaces ");
}
}
else
{
}
}
catch (DataException dex)
{
ModelState.AddModelError("", "Lỗi không xác định");
}
SelectList listCategory = new SelectList(categoryBL.SelectDropdownData(), "id", "name");
ViewBag.ListCategory = listCategory;
return(View("Add", category));
}
public ActionResult AddMeasure(MeasureDTO measure)
{
try
{
if (ModelState.IsValid)
{
if (!measure.code.Contains(" "))
{
int count = measureBL.CountData(new MeasureDTO()
{
code_key = measure.code
});
if (count == 0)
{
measure.created_by = 123;
measure.updated_by = 123;
CommonData.ReturnCode returnCode = measureBL.InsertData(measure);
if (returnCode == CommonData.ReturnCode.Success)
{
TempData["Success"] = "Inserted Successfully!";
}
else
{
TempData["Error"] = "Insert fail";
}
Session["model.Measure"] = null;
return(RedirectToAction("Measure"));
}
else
{
ModelState.AddModelError("code", "The Code already is existed!");
return(RedirectToAction("Add"));
}
}
else
{
ModelState.AddModelError("code", "The code must not have spaces ");
}
}
else
{
}
}
catch (DataException dex)
{
ModelState.AddModelError("", "Lỗi không xác định");
}
return(View("Add", measure));
}
public ActionResult DeleteItem(string id)
{
if (!string.IsNullOrEmpty(id))
{
ItemBL itemBL = new ItemBL();
CommonData.ReturnCode returnCode = itemBL.DeleteData(id.ParseInt32());
if (CommonData.ReturnCode.Success == returnCode)
{
TempData["Success"] = "Delete Successful";
Session["model.Item"] = null;
return(RedirectToAction("Category"));
}
}
return(RedirectToAction("Item"));
}
public ActionResult DeleteMeasure(string id)
{
//List<string> lstMsg = new List<string>();
if (!string.IsNullOrEmpty(id))
{
CommonData.ReturnCode returnCode = measureBL.DeleteData(int.Parse(id));
if (CommonData.ReturnCode.Success == returnCode)
{
TempData["Success"] = "Deleted Successfully!";
Session["model.Measure"] = null;
return(RedirectToAction("Measure"));
}
}
TempData["Error"] = "Delete fail";
return(RedirectToAction("Measure"));
}
public ActionResult UpdateCategory(CategoryDTO category)
{
try
{
if (ModelState.IsValid)
{
category.created_by = 123;
category.updated_by = 123;
if (category.id == category.parent_id)
{
ModelState.AddModelError("parent_id", "Parent Category is duplicated with current category");
}
else
{
CommonData.ReturnCode returnCode = categoryBL.UpdateData(category);
if (returnCode == CommonData.ReturnCode.Success)
{
TempData["Success"] = "Update Successfully!";
}
else
{
TempData["Error"] = "Update fail";
}
Session["model.Category"] = null;
return(RedirectToAction("Category"));
}
}
}
catch (DataException dex)
{
ModelState.AddModelError("", "Lỗi không xác định");
return(RedirectToAction("SubmissionFailed", category));
}
SelectList listCategory = new SelectList(categoryBL.SelectDropdownData(), "id", "name");
ViewBag.ListCategory = listCategory;
return(View("Update", category));
}
public static CommonData.ReturnCode DeleteData(int id)
{
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection connect = new MySqlConnection(constr))
{
using (MySqlCommand command = new MySqlCommand(
@"DELETE FROM product_category WHERE id = @ID", connect))
{
try
{
command.Parameters.AddWithValue("@ID", id);
connect.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
returnCode = CommonData.ReturnCode.UnSuccess;
}
}
}
return(returnCode);
}
public static CommonData.ReturnCode SearchData(ItemDTO inputDto, out List <ItemDTO> list)
{
list = new List <ItemDTO>();
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand())
{
string sql = @"SELECT i.`id`, i.`code`, i.`name`, i.`specification`, i.`description`, i.`category_id`, i.`discontinued_datetime`
, i.`dangerous`, i.`inventory_measure_id`, i.`inventory_expired`, i.`inventory_standard_cost`
, i.`inventory_list_price`, i.`manufacture_day`, i.`manufacture_make`, i.`manufacture_tool`
, i.`manufacture_finished_goods`, i.`manufacture_size`, i.`manufacture_size_measure_id`
, i.`manufacture_weight`, i.`manufacture_weight_measure_id`, i.`manufacture_style`
, i.`manufacture_class`, i.`manufacture_color` "
+ " FROM product_item AS i "
+ " WHERE TRUE ";
#region Where Clause
if (inputDto.id != null)
{
sql += " AND i.`id` = @ID ";
cmd.Parameters.AddWithValue("@ID", inputDto.id);
}
if (inputDto.code_key.IsNotNullOrEmpty())
{
sql += " AND i.`code` = @Code ";
cmd.Parameters.AddWithValue("@Code", inputDto.code_key);
}
if (inputDto.code.IsNotNullOrEmpty())
{
sql += " AND i.`code` LIKE CONCAT('%',@Code,'%') ";
cmd.Parameters.AddWithValue("@Code", inputDto.code);
}
if (inputDto.name.IsNotNullOrEmpty())
{
sql += " AND i.`name` LIKE CONCAT('%',@Name,'%') ";
cmd.Parameters.AddWithValue("@Name", inputDto.name);
}
if (inputDto.category_id != null)
{
sql += " AND i.`category_id` = @category_id ";
cmd.Parameters.AddWithValue("@category_id", inputDto.category_id);
}
#endregion
sql += " ORDER BY i.`updated_datetime` DESC";
sql += " LIMIT @start, 20";
if (inputDto.page > 1)
{
cmd.Parameters.AddWithValue("@start", 20 * (inputDto.page - 1));
}
else
{
cmd.Parameters.AddWithValue("@start", 0);
}
cmd.Connection = con;
cmd.CommandText = sql;
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count != 0)
{
foreach (DataRow item in dt.Rows)
{
ItemDTO dto = new ItemDTO();
dto.id = int.Parse(item["id"].ToString());
dto.code = item["code"].ToString();
dto.name = item["name"].ToString();
dto.specification = item["specification"].ToString();
dto.description = item["description"].ToString();
dto.category_id = item["category_id"].ToString().ParseInt32();
dto.category_name = item["name"].ToString();
dto.discontinued_datetime = item["discontinued_datetime"].ParseDateTime();
dto.inventory_list_price = item["inventory_list_price"].ToString().ParseInt32();
dto.dangerous = (item["dangerous"].ToString()).Parsebool();
dto.inventory_measure_id = item["inventory_measure_id"].ToString().ParseInt32();
dto.inventory_expired = item["inventory_expired"].ToString().ParseInt32();
dto.inventory_standard_cost = item["inventory_standard_cost"].ToString().ParseInt32();
dto.inventory_list_price = item["inventory_list_price"].ToString().ParseInt32();
dto.manufacture_day = item["manufacture_day"].ToString().ParseInt32();
dto.manufacture_make = (item["manufacture_make"].ToString()).Parsebool();
dto.manufacture_tool = (item["manufacture_tool"].ToString()).Parsebool();
dto.manufacture_finished_goods = (item["manufacture_finished_goods"].ToString()).Parsebool();
dto.manufacture_size = item["manufacture_size"].ToString();
dto.manufacture_size_measure_id = item["manufacture_size_measure_id"].ToString().ParseInt32();
dto.manufacture_weight = item["manufacture_weight"].ToString();
dto.manufacture_weight_measure_id = item["manufacture_weight_measure_id"].ToString().ParseInt32();
dto.manufacture_style = item["manufacture_style"].ToString();
dto.manufacture_class = item["manufacture_class"].ToString();
dto.manufacture_color = item["manufacture_color"].ToString();
list.Add(dto);
}
returnCode = 0;
}
else
{
returnCode = CommonData.ReturnCode.UnSuccess;
}
}
}
}
return(returnCode);
}
public CommonData.ReturnCode SearchData(IDTO searchDto, out List <CategoryDTO> list)
{
CommonData.ReturnCode returnCode = CategoryDAO.SearchData(searchDto as CategoryDTO, out list);
return(returnCode);
}
public CommonData.ReturnCode UpdateData(IDTO updateDto)
{
CommonData.ReturnCode returnCode = CategoryDAO.UpdateData(updateDto as CategoryDTO);
return(returnCode);
}
public CommonData.ReturnCode UpdateData(IDTO updateDto)
{
CommonData.ReturnCode returnCode = ItemDAO.UpdateData(updateDto as ItemDTO);
return(returnCode);
}
public CommonData.ReturnCode SearchData(IDTO searchDto, out List <ItemDTO> list)
{
CommonData.ReturnCode returnCode = ItemDAO.SearchData(searchDto as ItemSearchDTO, out list);
return(returnCode);
}
public CommonData.ReturnCode InsertData(IDTO insertDto)
{
CommonData.ReturnCode returnCode = ItemDAO.InsertData(insertDto as ItemDTO);
return(returnCode);
}
public CommonData.ReturnCode DeleteData(int id)
{
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
returnCode = ItemDAO.DeleteData(id);
return(returnCode);
}
public CommonData.ReturnCode InsertData(IDTO insertDto)
{
CommonData.ReturnCode returnCode = CategoryDAO.InsertData(insertDto as CategoryDTO);
return(returnCode);
}
public static CommonData.ReturnCode SearchData(CategoryDTO inputDto, out List <CategoryDTO> list)
{
list = new List <CategoryDTO>();
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand())
{
string sql = "SELECT c.`id`, c.`parent_id`, c.`code`, c.`name`, cpp.`name` AS parent_name, c.`description` FROM `product_category` c " +
" LEFT JOIN (SELECT cp.`name`, cp.`id` FROM product_category cp ) cpp on cpp.`id` = c.`parent_id` WHERE TRUE ";
#region Clause
if (inputDto.id != null)
{
sql += " AND c.`id` = @ID ";
cmd.Parameters.AddWithValue("@ID", inputDto.id);
}
if (inputDto.parent_id != null)
{
sql += " AND c.`parent_id` = @Parent_ID ";
cmd.Parameters.AddWithValue("@Parent_ID", inputDto.parent_id);
}
if (inputDto.code.IsNotNullOrEmpty())
{
sql += " AND c.`code` LIKE CONCAT('%',@Code,'%') ";
cmd.Parameters.AddWithValue("@Code", inputDto.code);
}
if (inputDto.name.IsNotNullOrEmpty())
{
sql += " AND c.`name` LIKE CONCAT('%',@Name,'%') ";
cmd.Parameters.AddWithValue("@Name", inputDto.name);
}
#endregion
sql += " ORDER BY c.`updated_datetime` DESC";
sql += " LIMIT @start,20";
if (inputDto.page > 1)
{
cmd.Parameters.AddWithValue("@start", 20 * (inputDto.page - 1));
}
else
{
cmd.Parameters.AddWithValue("@start", 0);
}
cmd.Connection = con;
cmd.CommandText = sql;
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count != 0)
{
foreach (DataRow category in dt.Rows)
{
CategoryDTO dto = new CategoryDTO();
dto.id = int.Parse(category["id"].ToString());
dto.parent_id = int.Parse(category["parent_id"].ToString());
dto.parent_name = category["parent_name"].ToString();
dto.code = category["code"].ToString();
dto.name = category["name"].ToString();
dto.decription = category["description"].ToString();
list.Add(dto);
}
returnCode = 0;
}
else
{
returnCode = CommonData.ReturnCode.UnSuccess;
}
}
}
}
return(returnCode);
}
public CommonData.ReturnCode DeleteData(int id)
{
CommonData.ReturnCode returnCode = CategoryDAO.DeleteData(id);
return(returnCode);
}
public static CommonData.ReturnCode InsertData(ItemDTO dto)
{
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
try
{
using (MySqlConnection connect = new MySqlConnection(constr))
{
using (MySqlCommand command = new MySqlCommand(
@"INSERT INTO product_item(
`code`, `name`, `specification`, `description`, `category_id`, `discontinued_datetime`
, `dangerous`, `inventory_measure_id`, `inventory_expired`, `inventory_standard_cost`
, `inventory_list_price`, `manufacture_day`,`manufacture_make`, `manufacture_tool`
, `manufacture_finished_goods`, `manufacture_size`, `manufacture_size_measure_id`
, `manufacture_weight`, `manufacture_weight_measure_id`, `manufacture_style`
, `manufacture_class`, `manufacture_color`
, `created_datetime`, `created_by`,`updated_datetime`,`updated_by`
) VALUES (
@code, @name, @specification, @description, @category_id, discontinued_datetime
, @dangerous, @inventory_measure_id, @inventory_expired, @inventory_standard_cost
, @inventory_list_price, @manufacture_day, @manufacture_make, @manufacture_tool
, @manufacture_finished_goods, @manufacture_size, @manufacture_size_measure_id
, @manufacture_weight, @manufacture_weight_measure_id, @manufacture_style
, @manufacture_class, @manufacture_color
, SYSDATE(), @created_by, SYSDATE(), @updated_by)", connect))
{
command.Parameters.AddWithValue("@code", dto.code);
command.Parameters.AddWithValue("@name", dto.name);
command.Parameters.AddWithValue("@specification", dto.specification);
command.Parameters.AddWithValue("@description", dto.description);
command.Parameters.AddWithValue("@category_id", dto.category_id);
command.Parameters.AddWithValue("@discontinued_datetime", dto.discontinued_datetime);
command.Parameters.AddWithValue("@dangerous", dto.dangerous);
command.Parameters.AddWithValue("@inventory_measure_id", dto.inventory_measure_id);
command.Parameters.AddWithValue("@inventory_expired", dto.inventory_expired);
command.Parameters.AddWithValue("@inventory_standard_cost", dto.inventory_standard_cost);
command.Parameters.AddWithValue("@inventory_list_price", dto.inventory_list_price);
command.Parameters.AddWithValue("@manufacture_day", dto.manufacture_day);
command.Parameters.AddWithValue("@manufacture_make", dto.manufacture_make);
command.Parameters.AddWithValue("@manufacture_tool", dto.manufacture_tool);
command.Parameters.AddWithValue("@manufacture_finished_goods", dto.manufacture_finished_goods);
command.Parameters.AddWithValue("@manufacture_size", dto.manufacture_size);
command.Parameters.AddWithValue("@manufacture_size_measure_id", dto.manufacture_size_measure_id);
command.Parameters.AddWithValue("@manufacture_weight", dto.manufacture_weight);
command.Parameters.AddWithValue("@manufacture_weight_measure_id", dto.manufacture_weight_measure_id);
command.Parameters.AddWithValue("@manufacture_style", dto.manufacture_style);
command.Parameters.AddWithValue("@manufacture_class", dto.manufacture_class);
command.Parameters.AddWithValue("@manufacture_color", dto.manufacture_color);
command.Parameters.AddWithValue("@created_by", dto.created_by);
command.Parameters.AddWithValue("@updated_by", dto.updated_by);
connect.Open();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
returnCode = CommonData.ReturnCode.UnSuccess;
}
return(returnCode);
}
public static CommonData.ReturnCode SearchData(MeasureDTO inputDto, out List <MeasureDTO> list)
{
CommonData.ReturnCode returnCode = CommonData.ReturnCode.Success;
list = new List <MeasureDTO>();
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand())
{
string sql = @"
SELECT m.`id`, m.`code`, m.`name`, m.`description`
FROM product_measure m
WHERE TRUE ";
#region Where Clause
if (inputDto.id != null)
{
sql += " AND m.`id` = @ID ";
cmd.Parameters.AddWithValue("@ID", inputDto.id);
}
if (inputDto.code_key.IsNotNullOrEmpty())
{
sql += " AND m.`code` = @Code ";
cmd.Parameters.AddWithValue("@Code", inputDto.code_key);
}
if (inputDto.code.IsNotNullOrEmpty())
{
sql += " AND m.`code` LIKE CONCAT('%',@Code,'%') ";
cmd.Parameters.AddWithValue("@Code", inputDto.code);
}
if (inputDto.name.IsNotNullOrEmpty())
{
sql += " AND m.`name` LIKE CONCAT('%',@Name,'%') ";
cmd.Parameters.AddWithValue("@Name", inputDto.name);
}
#endregion
sql += " ORDER BY m.`updated_datetime` DESC";
sql += " LIMIT @start, 20";
if (inputDto.page > 1)
{
cmd.Parameters.AddWithValue("@start", 20 * (inputDto.page - 1));
}
else
{
cmd.Parameters.AddWithValue("@start", 0);
}
cmd.Connection = con;
cmd.CommandText = sql;
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
con.Open();
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count != 0)
{
foreach (DataRow item in dt.Rows)
{
MeasureDTO dto = new MeasureDTO();
dto.id = int.Parse(item["id"].ToString());
dto.code = item["code"].ToString();
dto.name = item["name"].ToString();
dto.description = item["description"].ToString();
list.Add(dto);
}
}
else
{
}
}
}
}
return(returnCode);
}
public CommonData.ReturnCode UpdateData(IDTO updateDto)
{
CommonData.ReturnCode returnCode = MeasureDAO.UpdateData(updateDto as MeasureDTO);
return(returnCode);
}
