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);
}
