private List <RoomOrderDetailModel> GetRoomOrderDetail(string orderID)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" RoomType = case RoomType when 0 then 'King Room' when 1 then 'Queen Room' end, CheckinDate, CheckoutDate, ");
sql.AppendLine(" Price, Discount, Quantity, TotalPrice, ");
sql.AppendLine(" AdultCount, KidsCount, AdditionalDetail");
sql.AppendLine(" FROM [RoomOrderDetail] WITH(NOLOCK)");
sql.AppendLine(" WHERE OrderID = @OrderID");
sql.AppendLine(" order by RoomOrderDetailID asc");
SqlParameter[] paras = new SqlParameter[1];
paras[0] = new SqlParameter("@OrderID", Convert.ToInt32(orderID));
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
List <RoomOrderDetailModel> detail = new List <Models.RoomOrderDetailModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
detail = DataMappingHelper <RoomOrderDetailModel> .DataTableToList(ds.Tables[0]);
}
return(detail);
}
public List <RoomRateDetailModel> GetDataByRateType(int rateType)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" RoomRateID, RateName, RateType,");
sql.AppendLine(" BasicRate, DiscountRate, DiscountDays, IsDelete");
sql.AppendLine(" FROM RoomRate WITH(NOLOCK)");
sql.AppendLine(" WHERE IsDelete = 0 and RateType=@RateType");
SqlParameter[] paras = new SqlParameter[1];
paras[0] = new SqlParameter("@RateType", rateType);
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
List <RoomRateDetailModel> roomRate = new List <RoomRateDetailModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
roomRate = DataMappingHelper <RoomRateDetailModel> .DataTableToList(ds.Tables[0]);
}
return(roomRate);
}
private List <LogModel> GetLog(string orderID)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" LogContent, ErrorMessage, LogTime, System, SystemModel");
sql.AppendLine(" FROM [LogInfo] WITH(NOLOCK)");
sql.AppendLine(" WHERE ReferenceID = @OrderID");
sql.AppendLine(" order by LogTime desc");
SqlParameter[] paras = new SqlParameter[1];
paras[0] = new SqlParameter("@OrderID", Convert.ToInt32(orderID));
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
List <LogModel> log = new List <Models.LogModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
log = DataMappingHelper <LogModel> .DataTableToList(ds.Tables[0]);
}
return(log);
}
public List <RoomRateResultModel> GetSearchData(RoomRateSearchModel search)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" RoomRateID, RateName, RateType,");
sql.AppendLine(" RateTypeName = case RateType when 0 then 'Basic Rate' when 1 then 'Discount Rate' end, ");
sql.AppendLine(" BasicRate, DiscountRate, DiscountDays, IsDelete");
sql.AppendLine(" FROM RoomRate WITH(NOLOCK)");
sql.AppendLine(" WHERE 1=1");
if (!string.IsNullOrEmpty(search.RateName))
{
sql.AppendLine(" and RateName like '%' + @RateName + '%'");
}
if (search.RateType != 9)
{
sql.AppendLine(" and RateType = @RateType");
}
if (search.IsDelete != "9")
{
sql.AppendLine(" and IsDelete = @IsDelete");
}
SqlParameter[] paras = new SqlParameter[3];
if (!string.IsNullOrEmpty(search.RateName))
{
paras[0] = new SqlParameter("@RateName", search.RateName);
}
if (search.RateType != 9)
{
paras[1] = new SqlParameter("@RateType", search.RateType);
}
if (search.IsDelete != "9")
{
paras[2] = new SqlParameter("@IsDelete", search.IsDelete);
}
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
List <RoomRateResultModel> roomRate = new List <RoomRateResultModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
roomRate = DataMappingHelper <RoomRateResultModel> .DataTableToList(ds.Tables[0]);
}
return(roomRate);
}
public MenuModule GetMenu(int userID)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" m.MenuID, m.Menuname, m.Action as ActionName, m.Control as ControllerName, m.ParentMenuID");
sql.AppendLine(" FROM user_menu us with(Nolock)");
sql.AppendLine(" left join menu m with(Nolock) on us.menuid = m.menuID");
sql.AppendLine(" WHERE us.userID = @UserID");
sql.AppendLine(" and IsSelected = 1");
sql.AppendLine(" and ParentMenuID <> 0");
sql.AppendLine(" order by m.menuID asc");
SqlParameter[] paras = new SqlParameter[1];
paras[0] = new SqlParameter("@UserID", Convert.ToInt32(userID));
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
MenuModule menu = new MenuModule();
menu.Menus = new List <MenuDetailModule>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
menu.Menus = DataMappingHelper <MenuDetailModule> .DataTableToList(ds.Tables[0]);
}
sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" m.MenuID, m.Menuname, m.Action as ActionName, m.Control as ControllerName, m.ParentMenuID");
sql.AppendLine(" FROM menu m with(Nolock)");
sql.AppendLine(" WHERE ParentMenuID = 0");
sql.AppendLine(" order by m.menuID asc");
menu.ParentMenus = new List <MenuDetailModule>();
DataSet dsParent = mySqlAccess.ExecuteAdapter(sql.ToString());
if (dsParent != null && dsParent.Tables.Count > 0 && dsParent.Tables[0].Rows.Count > 0)
{
menu.ParentMenus = DataMappingHelper <MenuDetailModule> .DataTableToList(dsParent.Tables[0]);
}
return(menu);
}
public List <ProductResultModel> GetSearch(ProductSearchModel search)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" C.WineCategoryName, ");
sql.AppendLine(" P.WineID, P.WineCategoryID, P.ProductYear, P.Price, P.Describle, P.IsDelete");
sql.AppendLine(" FROM WineProduct P WITH(NOLOCK)");
sql.AppendLine(" LEFT JOIN WineCategory C WITH(NOLOCK) ON P.WineCategoryID = C.WineCategoryID");
sql.AppendLine(" WHERE 1=1");
if (search.CategoryID != -1)
{
sql.AppendLine(" AND C.WineCategoryID = @WineCategoryID");
}
if (search.IsDelete != "9")
{
sql.AppendLine(" AND P.IsDelete = @IsDelete");
}
SqlParameter[] paras;
paras = new SqlParameter[2];
if (search.CategoryID != -1)
{
paras[0] = new SqlParameter("@WineCategoryID", search.CategoryID);
}
if (search.IsDelete != "9")
{
paras[1] = new SqlParameter("@IsDelete", search.IsDelete);
}
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
List <ProductResultModel> product = new List <ProductResultModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
product = DataMappingHelper <ProductResultModel> .DataTableToList(ds.Tables[0]);
}
return(product);
}
/// <summary>
/// offline 的 user管理的列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List <UserResultListModule> GetUserList(UserSearchModule model)
{
StringBuilder str = new StringBuilder();
str.AppendLine(" SELECT distinct");
str.AppendLine(" us.UserID, u.Username ");
str.AppendLine(" FROM User_Menu us with(Nolock)");
str.AppendLine(" left join LoginUser u with(Nolock) on us.UserID = u.UserID");
if (model.UserID != null)
{
str.AppendLine(" WHERE us.UserID = @UserID");
}
if (model.UserName != null)
{
str.AppendLine(" WHERE u.Username like '%' + @UserName + '%'");
}
str.AppendLine(" ORDER BY UserID ASC");
SqlParameter[] paras = new SqlParameter[3];
if (model.UserID != null)
{
paras[0] = new SqlParameter("@UserID", model.UserID);
}
if (model.UserName != null)
{
paras[1] = new SqlParameter("@UserName", model.UserName);
}
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(str.ToString(), paras);
List <UserResultListModule> userResultList = new List <UserResultListModule>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
userResultList = DataMappingHelper <UserResultListModule> .DataTableToList(ds.Tables[0]);
}
return(userResultList);
}
public List <ShippingResultModel> GetSearchData(ShippingSearchModel search)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" ShippingID, State, ShippingRate, IsDelete");
sql.AppendLine(" FROM Shipping WITH(NOLOCK)");
sql.AppendLine(" WHERE 1=1");
if (search.State != "9")
{
sql.AppendLine(" AND State = @AuState");
}
if (search.IsDelete != "9")
{
sql.AppendLine(" AND IsDelete = @IsDelete");
}
SqlParameter[] paras = new SqlParameter[2];
if (search.State != "9")
{
paras[0] = new SqlParameter("@AuState", search.State);
}
if (search.IsDelete != "9")
{
paras[1] = new SqlParameter("@IsDelete", search.IsDelete);
}
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
List <ShippingResultModel> shipping = new List <ShippingResultModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
shipping = DataMappingHelper <ShippingResultModel> .DataTableToList(ds.Tables[0]);
}
return(shipping);
}
public string GetShipping()
{
StringBuilder str = new StringBuilder();
str.AppendLine("select [State], ShippingRate from Shipping where IsDelete = 0");
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(str.ToString());
List <ShippingInfoModel> Shipping = new List <ShippingInfoModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
Shipping = DataMappingHelper <ShippingInfoModel> .DataTableToList(ds.Tables[0]);
JsonSerializer json = new JsonSerializer();
return(json.Serialize <List <ShippingInfoModel> >(Shipping));
}
return(string.Empty);
}
private List <AccomodationDiscountPolicy> GetAllDiscountRate()
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" RoomRateID, DiscountRate, DiscountDays");
sql.AppendLine(" FROM RoomRate WITH(NOLOCK)");
sql.AppendLine(" WHERE IsDelete = 0 and RateType = 1");
sql.AppendLine(" order by DiscountDays desc");
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString());
List <AccomodationDiscountPolicy> discountRate = new List <AccomodationDiscountPolicy>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
discountRate = DataMappingHelper <AccomodationDiscountPolicy> .DataTableToList(ds.Tables[0]);
}
return(discountRate);
}
public List <WineCategoryResultModel> GetSearchData(WineCategorySearchModel search)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" WineCategoryID, WineCategoryName, IsDelete");
sql.AppendLine(" FROM WineCategory WITH(NOLOCK)");
if (search.IsDelete != "9")
{
sql.AppendLine(" WHERE ");
sql.AppendLine(" IsDelete = @IsDelete");
}
SqlParameter[] paras;
paras = new SqlParameter[1];
if (search.IsDelete != "9")
{
paras[0] = new SqlParameter("@IsDelete", search.IsDelete);
}
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
List <WineCategoryResultModel> wineCategory = new List <WineCategoryResultModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
wineCategory = DataMappingHelper <WineCategoryResultModel> .DataTableToList(ds.Tables[0]);
}
return(wineCategory);
}
public List <WineImageModel> GetWineImageByWindID(string wineID)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" WineImageID, WineID, Url, ImageType ");
sql.AppendLine(" FROM WineImage WITH(NOLOCK)");
sql.AppendLine(" WHERE WineID = @WineID");
sql.AppendLine(" ORDER BY ImageType ASC");
SqlParameter[] paras = new SqlParameter[1];
paras[0] = new SqlParameter("@WineID", wineID);
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
List <WineImageModel> wineImage = new List <WineImageModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
wineImage = DataMappingHelper <WineImageModel> .DataTableToList(ds.Tables[0]);
}
return(wineImage);
}
public FaultOrderListModel GetFaultSearchResult(FaultOrderListModel model)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" O.OrderID, O.OrderNo,");
sql.AppendLine(" OrderTypeName = case O.OrderType when 0 then 'Room' when 1 then 'Wine' end, ");
sql.AppendLine(" ProcessTypeName = case O.ProcessType when 0 then 'Online' when 1 then 'Offline' end, ");
sql.AppendLine(" O.OrderDate, ");
sql.AppendLine(" O.OriginalPrice, O.Discount, O.Shipping, O.TotalPrice, O.Tax, ");
sql.AppendLine(" OrderStatusName = case O.OrderStatus when 0 then 'Init' when 1 then 'Success' when 2 then 'Fail' when 3 then 'Exception' end, ");
sql.AppendLine(" O.OrderDesc, ");
sql.AppendLine(" F.FaultMessage, F.IsDelete");
sql.AppendLine(" FROM FaultOrders F WITH(NOLOCK)");
sql.AppendLine(" left join [Order] O WITH(NOLOCK) on O.OrderID = F.OrderID");
sql.AppendLine(" WHERE 1=1");
if (!string.IsNullOrEmpty(model.FaultOrderListSearch.OrderNo))
{
sql.AppendLine(" AND O.OrderNo LIKE '%' + @OrderNo + '%'");
}
if (model.FaultOrderListSearch.OrderType != 9)
{
sql.AppendLine(" AND O.OrderType = @OrderType");
}
if (model.FaultOrderListSearch.ProcessType != 9)
{
sql.AppendLine(" AND O.ProcessType = @ProcessType");
}
if (model.FaultOrderListSearch.OrderStatus != 9)
{
sql.AppendLine(" AND O.OrderStatus = @OrderStatus");
}
if (model.FaultOrderListSearch.OrderDateStart != null)
{
sql.AppendLine(" AND O.OrderDate >= @OrderDateStart");
}
if (model.FaultOrderListSearch.OrderDateEnd != null)
{
sql.AppendLine(" AND O.OrderDate <= @OrderDateEnd");
}
if (model.FaultOrderListSearch.IsDelete != 9)
{
sql.AppendLine(" AND F.IsDelete = @IsDelete");
}
sql.AppendLine(" ORDER BY O.OrderDate, O.OrderStatus DESC ");
SqlParameter[] paras = new SqlParameter[7];
if (!string.IsNullOrEmpty(model.FaultOrderListSearch.OrderNo))
{
paras[0] = new SqlParameter("@OrderNo", model.FaultOrderListSearch.OrderNo);
}
if (model.FaultOrderListSearch.OrderType != 9)
{
paras[1] = new SqlParameter("@OrderType", model.FaultOrderListSearch.OrderType);
}
if (model.FaultOrderListSearch.ProcessType != 9)
{
paras[2] = new SqlParameter("@ProcessType", model.FaultOrderListSearch.ProcessType);
}
if (model.FaultOrderListSearch.OrderStatus != 9)
{
paras[3] = new SqlParameter("@OrderStatus", model.FaultOrderListSearch.OrderStatus);
}
if (model.FaultOrderListSearch.OrderDateStart != null)
{
paras[4] = new SqlParameter("@OrderDateStart", model.FaultOrderListSearch.OrderDateStart);
}
if (model.FaultOrderListSearch.OrderDateEnd != null)
{
paras[5] = new SqlParameter("@OrderDateEnd", Convert.ToDateTime(model.FaultOrderListSearch.OrderDateEnd).AddDays(1).AddSeconds(-1));
}
if (model.FaultOrderListSearch.IsDelete != 9)
{
paras[6] = new SqlParameter("@IsDelete", model.FaultOrderListSearch.IsDelete);
}
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
model.FaultOrderListResult = new List <Models.FaultOrderListResultModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
model.FaultOrderListResult = DataMappingHelper <FaultOrderListResultModel> .DataTableToList(ds.Tables[0]);
}
return(model);
}
/// <summary>
/// 获取User权限
/// </summary>
/// <param name="userID"></param>
/// <returns></returns>
public List <PrivilegeDetailModel> GetUserpribilegeByID(string userID)
{
//StringBuilder str = new StringBuilder();
StringBuilder str_usermenu = new StringBuilder();
//str.AppendLine(" SELECT ");
//str.AppendLine(" UserID, Username, IsDelete ");
//str.AppendLine(" FROM LoginUser with(Nolock)");
//str.AppendLine(" WHERE UserID = @UserID");
/*SqlParameter[] paras = new SqlParameter[1];
* paras[0] = new SqlParameter("@UserID", userID);
*
* SqlAccess mySqlAccess = new SqlAccess("WebsiteDBConnection");
* DataSet ds = mySqlAccess.ExecuteAdapter(str.ToString(), paras);
*
* UserUpdateModule user = new UserUpdateModule();
* if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
* {
* user.UserID = Convert.ToInt32(ds.Tables[0].Rows[0]["UserID"]);
* }
*/
str_usermenu.AppendLine(" SELECT ");
str_usermenu.AppendLine(" m.MenuID, m.menuname, um.UserID, um.MenuID, um.IsSelected ");
str_usermenu.AppendLine(" FROM Menu m with(Nolock) left join User_Menu um with(Nolock) on m.MenuID = um.MenuID and um.UserID = @UserID");
str_usermenu.AppendLine(" where m.ParentMenuID <> 0");
str_usermenu.AppendLine(" order by m.menuID asc");
SqlParameter[] paras = new SqlParameter[1];
paras[0] = new SqlParameter("@UserID", userID);
SqlAccess mySqlAccess = new SqlAccess();
DataSet dsUser = mySqlAccess.ExecuteAdapter(str_usermenu.ToString(), paras);
List <PrivilegeDetailModel> PrivilegeDetail = new List <PrivilegeDetailModel>();
if (dsUser != null && dsUser.Tables.Count > 0)
{
PrivilegeDetail = DataMappingHelper <PrivilegeDetailModel> .DataTableToList(dsUser.Tables[0]);
/*操作数据 一 一对应
* foreach (DataRow dw in dsUser.Tables[0].Rows)
* {
* PrivilegeDetailModel privilege = new PrivilegeDetailModel();
*
* privilege.UserID = Convert.ToInt32(dsUser.Tables[0].Rows[0]["UserID"]);
* privilege.MenuID = Convert.ToInt32(dsUser.Tables[0].Rows[0]["MenuID"]);
* privilege.IsSelected = Convert.ToBoolean(dsUser.Tables[0].Rows[0]["IsSelected"]);
* user.UserPrivilegeList.Add(privilege);
*
* }
*/
}
/*if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
* {
* user = DataMappingHelper<UserUpdateModule>.DataTableToEntity(ds.Tables[0]);
* }*/
return(PrivilegeDetail);
}
public List <RatePolicyResultModel> GetSearchData(RatePolicySearchModel search)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" RoomPolicyID, StartDate, EndDate, RoomType,");
sql.AppendLine(" RoomTypeName = case RoomType when 0 then 'King' when 1 then 'Queen' end, ");
sql.AppendLine(" IsDelete");
sql.AppendLine(" FROM RoomPolicy WITH(NOLOCK)");
sql.AppendLine(" WHERE 1=1");
if (search.StartDateFrom != null)
{
sql.AppendLine(" and StartDate >= @StartDateFrom");
}
if (search.StartDateTo != null)
{
sql.AppendLine(" and StartDate <= @StartDateTo");
}
if (search.EndDateFrom != null)
{
sql.AppendLine(" and EndDate >= @EndDateFrom");
}
if (search.EndDateTo != null)
{
sql.AppendLine(" and EndDate <= @EndDateTo");
}
if (search.RoomType != 9)
{
sql.AppendLine(" and RoomType = @RoomType");
}
if (search.IsDelete != "9")
{
sql.AppendLine(" and IsDelete = @IsDelete");
}
SqlParameter[] paras = new SqlParameter[3];
if (search.StartDateFrom != null)
{
paras[0] = new SqlParameter("@StartDateFrom", search.StartDateFrom);
}
if (search.StartDateTo != null)
{
paras[1] = new SqlParameter("@StartDateTo", search.StartDateFrom);
}
if (search.StartDateFrom != null)
{
paras[0] = new SqlParameter("@EndDateFrom", search.EndDateFrom);
}
if (search.StartDateTo != null)
{
paras[1] = new SqlParameter("@EndDateTo", search.EndDateTo);
}
if (search.RoomType != 9)
{
paras[1] = new SqlParameter("@RoomType", search.RoomType);
}
if (search.IsDelete != "9")
{
paras[2] = new SqlParameter("@IsDelete", search.IsDelete);
}
SqlAccess mySqlAccess = new SqlAccess();
DataSet ds = mySqlAccess.ExecuteAdapter(sql.ToString(), paras);
List <RatePolicyResultModel> ratePolicy = new List <RatePolicyResultModel>();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
ratePolicy = DataMappingHelper <RatePolicyResultModel> .DataTableToList(ds.Tables[0]);
}
return(ratePolicy);
}