private DataReportMerchandiseData BuildConsumeData(DataRow dr)
{
DataReportMerchandiseData data = new DataReportMerchandiseData();
data.ID = (string)dr["uuid"];
if (!string.IsNullOrEmpty(dr["code"].ToString()))
{
data.MerchID = (string)dr["code"];
}
if (!string.IsNullOrEmpty(dr["name"].ToString()))
{
data.MerchName = (string)dr["name"];
}
if (!string.IsNullOrEmpty(dr["ean"].ToString()))
{
data.Ean = (string)dr["ean"];
}
if (!string.IsNullOrEmpty(dr["units"].ToString()))
{
data.Uint = (string)dr["units"];
}
if (!string.IsNullOrEmpty(dr["price"].ToString()))
{
data.UintPrice = (decimal)dr["price"];
}
if (!string.IsNullOrEmpty(dr["category_name"].ToString()))
{
data.Category = (string)dr["category_name"];
}
if (!string.IsNullOrEmpty(dr["dt"].ToString()))
{
data.ConsumeDate = (DateTime)dr["dt"];
}
if (!string.IsNullOrEmpty(dr["num"].ToString()))
{
data.Amount = (float)dr["num"];
}
if (!string.IsNullOrEmpty(dr["total_amount"].ToString()))
{
data.TotalConsume = (decimal)dr["total_amount"];
}
if (!string.IsNullOrEmpty(dr["operator_id"].ToString()))
{
UserDAL dal = new UserDAL();
data.Operator = dal.QueryByOptrID((int)dr["operator_id"]).name;
}
return(data);
}
public List <DataReportMerchandiseData> QueryComsumeInfByLbGoodsInfo(string begin_date, string end_date,
string consume_type, string goods_name, string operator_name)
{
List <DataReportMerchandiseData> report_data_list = new List <DataReportMerchandiseData>();
StringBuilder temp = new StringBuilder();
List <MySqlParameter> parameters = new List <MySqlParameter>();
temp.Append(@"select sales_goods.uuid,sales_goods.goods_id,sales_goods.price,sales_goods.discount,sales_goods.num,
sales_goods.total_amount, sales_goods.discounted_value, sales.dt, sales.operator_id, item.code, item.name,
item.units,item.category, goods_category.category_name, item.ean
from sales_goods left join sales on sales_goods.order_id = sales.order_id
left join goods item on sales_goods.goods_id = item.goods_id
left join goods_category on item.category = goods_category.id
where (sales_goods.goods_id like @goods_id or item.name like @goods_name)
and sales.dt between @begin_date and @end_date ");
parameters.Add(new MySqlParameter("@goods_id", "%" + goods_name + "%"));
parameters.Add(new MySqlParameter("@goods_name", "%" + goods_name + "%"));
parameters.Add(new MySqlParameter("@begin_date", begin_date));
parameters.Add(new MySqlParameter("@end_date", end_date));
if (!("-1".Equals(consume_type) || string.IsNullOrEmpty(consume_type)))
{
temp.Append(" and item.category = @category");
parameters.Add(new MySqlParameter("@category", consume_type));
}
if (operator_name != "全部" && !string.IsNullOrEmpty(operator_name))
{
temp.Append(" and sales.operator_id = @operator");
UserDAL dal = new UserDAL();
parameters.Add(new MySqlParameter("@operator", dal.QueryByUserName(operator_name).OptrID));
}
temp.Append(" order by sales.dt desc ");
DataSet ds = ExecuteDataSet(temp.ToString(), parameters.ToArray());
if (null != ds && ds.Tables.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
DataReportMerchandiseData data = BuildConsumeData(dr);
report_data_list.Add(data);
}
}
return(report_data_list);
}
