public GetOrderMainDataTable ( int restaurantId = null, string type = null ) : |
||
restaurantId | int | |
type | string | |
Résultat |
public DataTable GetOrderMainDataTable(int? restaurantId = null, string type = null)
{
string sql = @"SELECT A.*,B.RESTAURANT_NAME,
ISNULL(C.ALLORDER,0) ALLORDER,
ISNULL(D.FINISH,0) FINISH,
ISNULL(E.CONFIRM,0) CONFIRM,
ISNULL(F.UNCONFIRM,0) UNCONFIRM
FROM ORDERMAIN A
LEFT JOIN RESTAURANT B ON A.RESTAURANT_ID = B.RESTAURANT_ID
LEFT JOIN (SELECT ORDER_ID,SUM([COUNT]) ALLORDER FROM ORDERLIST
WHERE (CANCEL_TIME = '' OR CANCEL_TIME IS NULL)
GROUP BY ORDER_ID) C ON A.ORDER_ID = C.ORDER_ID
LEFT JOIN (SELECT ORDER_ID,SUM([COUNT]) FINISH FROM ORDERLIST
WHERE (CANCEL_TIME = '' OR CANCEL_TIME IS NULL)
AND (FINISH_TIME <> '' AND FINISH_TIME IS NOT NULL)
GROUP BY ORDER_ID) D ON A.ORDER_ID = D.ORDER_ID
LEFT JOIN (SELECT ORDER_ID,SUM([COUNT]) CONFIRM FROM ORDERLIST
WHERE (CANCEL_TIME = '' OR CANCEL_TIME IS NULL)
AND (CONFIRM_TIME <> '' AND CONFIRM_TIME IS NOT NULL)
AND (FINISH_TIME = '' OR FINISH_TIME IS NULL)
GROUP BY ORDER_ID) E ON A.ORDER_ID = E.ORDER_ID
LEFT JOIN (SELECT ORDER_ID,SUM([COUNT]) UNCONFIRM FROM ORDERLIST
WHERE (CANCEL_TIME = '' OR CANCEL_TIME IS NULL)
AND (CONFIRM_TIME = '' OR CONFIRM_TIME IS NULL)
AND (FINISH_TIME = '' OR FINISH_TIME IS NULL)
GROUP BY ORDER_ID) F ON A.ORDER_ID = F.ORDER_ID
WHERE 1=1 ";
if (restaurantId != null)
sql += " AND B.RESTAURANT_ID = @RESTAURANT_ID ";
if (!string.IsNullOrEmpty(type))
sql += " AND A.ORDER_FLAG = @ORDER_FLAG ";
using (DbCommand dc = Db.GetSqlStringCommand(sql))
{
if (restaurantId != null)
Db.AddInParameter(dc, "@RESTAURANT_ID", DbType.Int32, restaurantId);
if (!string.IsNullOrEmpty(type))
Db.AddInParameter(dc, "@ORDER_FLAG", DbType.AnsiString, type);
return Db.ExecuteDataTable(dc);
}
}
private void BindTableData() { var type = this.hOrderStatus.Value; if (string.IsNullOrEmpty(type)) type = "1"; this.lType.InnerText = GetTypeName(type); if (type == "0") type = null; int? restaurantId = base.ParseInt(this.ddlRestaurant.SelectedValue); if (restaurantId == 0) restaurantId = null; IOrder biz = new OrderBiz(); DataTable dt = (bool)base.CurEmployeeEntity.IS_ADMIN ? biz.GetOrderMainDataTable(restaurantId, type) : biz.GetOrderMainDataTable(base.CurEmployeeEntity.RESTAURANT_ID, type); repeater1.DataSource = GetPagedDataSource(dt.DefaultView); repeater1.DataBind(); }