public List <MyOrderDetail> getMyOrderDetailListData(string MemberCardNo, string OrderId, string Type = null)
{
List <MyOrderDetail> list = null;
try
{
IParameterMapper ipmapper = new getMyOrderDetailDataParameterMapper();
DataAccessor <MyOrderDetail> tableAccessor;
// string strSql = @"
//select o.DiningDate,o.PersonCount,o.id,p.id as proId,o.status,
//o.ContactName,o.ContactPhone,od.ProductCount,od.UnitPrice,
//p.ProductName,r.Name,r.Address,r.ContactPhone as RstPhone,o.Remark,
//(select SUM(s.UnitPrice)from OrderDetails s where s.OrderId=o.Id)
// as total from Orders o ,OrderDetails od , Restaurant r ,Product p
////where o.Id=od.OrderId and o.RstId=r.Id and od.ProductId=p.Id and o.MemberCardNo=@MemberCardNo and o.id=@OrderId
//and dateAdd(hh,5,o.DiningDate)>=getdate()and o.status=1 order by o.id ";
string strSql = null;
if (!string.IsNullOrEmpty(Type) && Type == "FastFood")
{
strSql = @"
select a.DiningDate,a.PersonCount,a.id,p.id as proId, os.OrderStatus as status, p.MemberPrice,
a.ContactName,a.ContactPhone,od.ProductCount,od.UnitPrice,od.UseState,
p.ProductName,a.Name,a.Address,a.RstPhone,a.Remark,a.total,a.RstType
from
(select o.DiningDate,o.PersonCount,o.id,o.status, r.RstType, o.ContactName,o.ContactPhone,r.Name,r.Address,r.ContactPhone as RstPhone,o.Remark,
(select SUM(isnull(s.UnitPrice,0)*isnull(s.ProductCount,0)) from OrderDetails s where s.OrderId=o.Id) as total
from Orders o,Restaurant r where o.RstId=r.Id
and o.id=@OrderId) a
left join OrderDetails od on a.Id=od.OrderId
left join OrderStatus os on os.OrderId = a.Id
left join Product p on od.ProductId=p.Id
";//o.status=0
}
else
{
strSql = @"select a.DiningDate,a.PersonCount,a.id,p.id as proId, os.OrderStatus as status, p.MemberPrice,
a.ContactName,a.ContactPhone,od.ProductCount,od.UnitPrice,od.UseState,
p.ProductName,a.Name,a.Address,a.RstPhone,a.Remark,a.total,a.RstType
from
(select o.DiningDate,o.PersonCount,o.id,o.status, r.RstType,
o.ContactName,o.ContactPhone,r.Name,r.Address,r.ContactPhone as RstPhone,o.Remark,
(select SUM(isnull(s.UnitPrice,0)*isnull(s.ProductCount,0)) from OrderDetails s where s.OrderId=o.Id) as total
from Orders o,Restaurant r where o.RstId=r.Id
and o.id=@OrderId) a
left join OrderDetails od on a.Id=od.OrderId
left join OrderStatus os on os.OrderId = a.Id
left join Product p on od.ProductId=p.Id
";
}
tableAccessor = db.CreateSqlStringAccessor(strSql, ipmapper, MapBuilder <MyOrderDetail> .MapAllProperties()
.Map(t => t.DiningDate).ToColumn("DiningDate")
.Map(t => t.address).ToColumn("address")
.Map(t => t.RstType).ToColumn("RstType")
.Map(t => t.PersonCount).ToColumn("PersonCount")
.Map(t => t.RstName).ToColumn("name")
.Map(t => t.ContactName).ToColumn("ContactName")
.Map(t => t.ContactPhone).ToColumn("ContactPhone")
.Map(t => t.ProductCount).ToColumn("ProductCount")
.Map(t => t.RstPhone).ToColumn("RstPhone")
.Map(t => t.Remark).ToColumn("Remark")
.Map(t => t.UnitPrice).ToColumn("UnitPrice")
.Map(t => t.ProductName).ToColumn("ProductName")
.Map(t => t.OrderId).ToColumn("id")
.Map(t => t.total).ToColumn("total")
.Map(t => t.proId).ToColumn("proId")
.Map(t => t.status).ToColumn("status")
.Map(t => t.UseState).ToColumn("UseState")
.Build());
list = tableAccessor.Execute(new string[] { OrderId }).ToList();
return(list);
}
catch (Exception ex)
{
Logger.Log(ex);
return(null);
}
}
public List<MyOrderDetail> getMyOrderDetailListData(string MemberCardNo, string OrderId, string Type = null)
{
List<MyOrderDetail> list = null;
try
{
IParameterMapper ipmapper = new getMyOrderDetailDataParameterMapper();
DataAccessor<MyOrderDetail> tableAccessor;
// string strSql = @"
//select o.DiningDate,o.PersonCount,o.id,p.id as proId,o.status,
//o.ContactName,o.ContactPhone,od.ProductCount,od.UnitPrice,
//p.ProductName,r.Name,r.Address,r.ContactPhone as RstPhone,o.Remark,
//(select SUM(s.UnitPrice)from OrderDetails s where s.OrderId=o.Id)
// as total from Orders o ,OrderDetails od , Restaurant r ,Product p
////where o.Id=od.OrderId and o.RstId=r.Id and od.ProductId=p.Id and o.MemberCardNo=@MemberCardNo and o.id=@OrderId
//and dateAdd(hh,5,o.DiningDate)>=getdate()and o.status=1 order by o.id ";
string strSql = null;
if (!string.IsNullOrEmpty(Type) && Type == "FastFood")
{
strSql = @"
select a.DiningDate,a.PersonCount,a.id,p.id as proId, os.OrderStatus as status, p.MemberPrice,
a.ContactName,a.ContactPhone,od.ProductCount,od.UnitPrice,od.UseState,
p.ProductName,a.Name,a.Address,a.RstPhone,a.Remark,a.total,a.RstType
from
(select o.DiningDate,o.PersonCount,o.id,o.status, r.RstType, o.ContactName,o.ContactPhone,r.Name,r.Address,r.ContactPhone as RstPhone,o.Remark,
(select SUM(isnull(s.UnitPrice,0)*isnull(s.ProductCount,0)) from OrderDetails s where s.OrderId=o.Id) as total
from Orders o,Restaurant r where o.RstId=r.Id
and o.id=@OrderId) a
left join OrderDetails od on a.Id=od.OrderId
left join OrderStatus os on os.OrderId = a.Id
left join Product p on od.ProductId=p.Id
";//o.status=0
}
else
{
strSql = @"select a.DiningDate,a.PersonCount,a.id,p.id as proId, os.OrderStatus as status, p.MemberPrice,
a.ContactName,a.ContactPhone,od.ProductCount,od.UnitPrice,od.UseState,
p.ProductName,a.Name,a.Address,a.RstPhone,a.Remark,a.total,a.RstType
from
(select o.DiningDate,o.PersonCount,o.id,o.status, r.RstType,
o.ContactName,o.ContactPhone,r.Name,r.Address,r.ContactPhone as RstPhone,o.Remark,
(select SUM(isnull(s.UnitPrice,0)*isnull(s.ProductCount,0)) from OrderDetails s where s.OrderId=o.Id) as total
from Orders o,Restaurant r where o.RstId=r.Id
and o.id=@OrderId) a
left join OrderDetails od on a.Id=od.OrderId
left join OrderStatus os on os.OrderId = a.Id
left join Product p on od.ProductId=p.Id
";
}
tableAccessor = db.CreateSqlStringAccessor(strSql, ipmapper, MapBuilder<MyOrderDetail>.MapAllProperties()
.Map(t => t.DiningDate).ToColumn("DiningDate")
.Map(t => t.address).ToColumn("address")
.Map(t => t.RstType).ToColumn("RstType")
.Map(t => t.PersonCount).ToColumn("PersonCount")
.Map(t => t.RstName).ToColumn("name")
.Map(t => t.ContactName).ToColumn("ContactName")
.Map(t => t.ContactPhone).ToColumn("ContactPhone")
.Map(t => t.ProductCount).ToColumn("ProductCount")
.Map(t => t.RstPhone).ToColumn("RstPhone")
.Map(t => t.Remark).ToColumn("Remark")
.Map(t => t.UnitPrice).ToColumn("UnitPrice")
.Map(t => t.ProductName).ToColumn("ProductName")
.Map(t => t.OrderId).ToColumn("id")
.Map(t => t.total).ToColumn("total")
.Map(t => t.proId).ToColumn("proId")
.Map(t => t.status).ToColumn("status")
.Map(t => t.UseState).ToColumn("UseState")
.Build());
list = tableAccessor.Execute(new string[] { OrderId }).ToList();
return list;
}
catch (Exception ex)
{
Logger.Log(ex);
return null;
}
}
