public static int CountData(ItemSearchDTO inputDto) { int count = 0; using (MySqlConnection con = new MySqlConnection(constr)) { using (MySqlCommand cmd = new MySqlCommand()) { string sql = " SELECT COUNT(i.`id`)"; if (inputDto.category_id != null) { sql += @"FROM `product_item` i JOIN (SELECT c.`id`, c.`parent_id`, cpp.`name` AS parent_name, c.`code`, c.`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`) c ON i.`category_id` = c.`id` WHERE TRUE "; } else { sql += @" FROM product_item AS i left JOIN product_category c on c.`id` = i.`category_id` 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 OR i.`category_id` IN (SELECT cate.`id` FROM product_category cate WHERE cate.`parent_id` = @category_id ))"; cmd.Parameters.AddWithValue("@category_id", inputDto.category_id); } #endregion con.Open(); cmd.Connection = con; cmd.CommandText = sql; count = int.Parse(cmd.ExecuteScalar().ToString()); } } return(count); }
public int CountData(ItemSearchDTO updateDto) { return(ItemDAO.CountData(updateDto)); }
public static CommonData.ReturnCode SearchData(ItemSearchDTO 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 = string.Empty; sql = @"SELECT i.`id`, i.`code`, i.`name`, c.`name` as category_name, c.`category_parent_name`, i.`discontinued_datetime`, i.`dangerous` "; if (inputDto.id != null) { sql += @", i.`inventory_measure_id`, i.`inventory_expired`, i.`inventory_standard_cost`, i.`specification`, i.`description` , 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.`category_id` , i.`manufacture_class`, i.`manufacture_color` "; } if (inputDto.category_id != null) { sql += @"FROM `product_item` i left JOIN (SELECT c.`id`, cpp.`name` AS category_parent_name, c.`name` FROM product_category c Left JOIN (SELECT cp.`name`, cp.`id` FROM product_category cp ) cpp on cpp.`id` = c.`parent_id`) c ON i.`category_id` = c.`id` WHERE TRUE "; } else { sql += @" FROM product_item AS i left JOIN (SELECT c.`id`, cpp.`name` AS category_parent_name, c.`name` FROM product_category c Left JOIN (SELECT cp.`name`, cp.`id` FROM product_category cp ) cpp on cpp.`id` = c.`parent_id`) c ON i.`category_id` = c.`id` 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 OR i.`category_id` IN (SELECT cate.`id` FROM product_category cate WHERE cate.`parent_id` = @category_id ))"; cmd.Parameters.AddWithValue("@category_id", inputDto.category_id); } #endregion if (inputDto.category_id == null) { sql += " ORDER BY i.`updated_datetime` DESC"; } else { sql += " ORDER BY i.`category_id`"; } sql += " LIMIT @start, 20"; cmd.Parameters.AddWithValue("@start", 20 * (inputDto.page - 1)); cmd.Connection = con; cmd.CommandText = sql; using (MySqlDataAdapter sda = new MySqlDataAdapter()) { DataTable dt = new DataTable(); sda.SelectCommand = cmd; sda.Fill(dt); if (dt.Rows.Count != 0) { foreach (DataRow item in dt.Rows) { #region get data ItemDTO dto = new ItemDTO(); dto.id = item["id"].ToString().ParseInt32(); dto.code = item["code"].ToString(); dto.name = item["name"].ToString(); dto.category_name = item["category_name"].ToString(); dto.category_parent_name = item["category_parent_name"].ToString(); dto.discontinued_datetime = item["discontinued_datetime"].ParseDateTime(); dto.dangerous = item["dangerous"].ToString().ParseBool(); if (inputDto.id != null) { dto.specification = item["specification"].ToString(); dto.description = item["description"].ToString(); dto.category_id = item["category_id"].ToString().ParseInt32(); dto.inventory_list_price = item["inventory_list_price"].ToString().ParseInt32(); 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().ParseDecimal(); dto.inventory_list_price = item["inventory_list_price"].ToString().ParseDecimal(); dto.manufacture_day = item["manufacture_day"].ToString().ParseDecimal(); 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(); } #endregion list.Add(dto); } returnCode = 0; } else { returnCode = CommonData.ReturnCode.UnSuccess; } } } using (MySqlCommand cmd = new MySqlCommand()) { } } return(returnCode); }