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