protected void Page_Load(object sender, EventArgs e)
{
GridMenu.DataSource = Burgerconn.Menus.ToList();
GridMenu.DataBind();
GridOrder.DataSource = Burgerconn.Orders.ToList();
GridOrder.DataBind();
}
protected void ordercomplete_Click(object sender, EventArgs e)
{
objburger.Orderemove(Convert.ToInt32(burgerid.Text));
GridMenu.DataSource = Burgerconn.Menus.ToList();
GridMenu.DataBind();
GridOrder.DataSource = Burgerconn.Orders.ToList();
GridOrder.DataBind();
orderup = 0;
}
protected void orderupdate_Click(object sender, EventArgs e)
{
objburger.OrderUpdate(Convert.ToInt32(burgerid.Text), BurgerName.Text, Order1.Text, Order2.Text, Order3.Text, OrderMobile.Text);
GridMenu.DataSource = Burgerconn.Menus.ToList();
GridMenu.DataBind();
GridOrder.DataSource = Burgerconn.Orders.ToList();
GridOrder.DataBind();
orderup = 0;
}
protected void Ordertake_Click(object sender, EventArgs e)
{
objburger.TAkeorder(BurgerName.Text, Order1.Text, Order2.Text, Order3.Text, OrderMobile.Text);
GridMenu.DataSource = Burgerconn.Menus.ToList();
GridMenu.DataBind();
GridOrder.DataSource = Burgerconn.Orders.ToList();
GridOrder.DataBind();
orderup = 0;
}
public ActionResult Search(long provinceId, long areaId, int current, int rowCount, string searchPhrase, FormCollection fc)
{
var user = ResSettings.SettingsInSession.User;
//----------------------------------------------------------
var t = APDBDef.ResUser;
var c = APDBDef.ResCompany;
APSqlOrderPhrase order = null;
APSqlWherePhrase where = t.Removed == false;
// 取排序
var co = GridOrder.GetSortDef(fc);
if (co != null)
{
switch (co.Id)
{
case "UserName": order = new APSqlOrderPhrase(t.UserName, co.Order); break;
case "RealName": order = new APSqlOrderPhrase(t.RealName, co.Order); break;
case "CompanyName": order = new APSqlOrderPhrase(c.CompanyName, co.Order); break;
case "RoleName": order = new APSqlOrderPhrase(APDBDef.ResRole.RoleId, co.Order); break;
case "Gender": order = new APSqlOrderPhrase(t.GenderPKID, co.Order); break;
case "Email": order = new APSqlOrderPhrase(t.Email, co.Order); break;
case "RegisterTime": order = new APSqlOrderPhrase(t.RegisterTime, co.Order); break;
case "LoginCount": order = new APSqlOrderPhrase(t.LoginCount, co.Order); break;
case "Actived": order = new APSqlOrderPhrase(t.Actived, co.Order); break;
}
}
// 默认排序
// if (order == null) order = new APSqlOrderPhrase(t.RegisterTime, APSqlOrderAccording.Desc);
// 按用户名或真实姓名过滤
if (searchPhrase != null)
{
searchPhrase = searchPhrase.Trim();
if (searchPhrase != "")
{
where &= (t.UserName.Match(searchPhrase) | t.RealName.Match(searchPhrase));
}
}
if (provinceId > 0)
{
where &= t.ProvinceId == provinceId;
}
if (areaId > 0)
{
where &= t.AreaId == areaId;
}
// 用户数据范围
if (user.ProvinceId > 0)
{
where &= t.ProvinceId == user.ProvinceId;
}
if (user.AreaId > 0)
{
where &= t.AreaId == user.AreaId;
}
if (user.CompanyId > 0)
{
where &= t.CompanyId == user.CompanyId;
}
int total;
var list = APBplDef.ResUserBpl.TolerantSearch(out total, current, rowCount, where, order);
//----------------------------------------------------------
if (Request.IsAjaxRequest())
{
return(Json(
new
{
rows = from res in list
select new
{
id = res.UserId,
res.UserName,
res.RealName,
res.CompanyName,
res.UserType,
res.Gender,
res.Email,
RegisterTime = res.RegisterTime.ToString("yyyy-MM-dd"),
res.LoginCount,
Actived = res.Actived ? "有效" : "无效"
},
current = current,
rowCount = rowCount,
total = total
}));
}
else
{
return(View(list));
}
}
private static IOrderedQueryable <TDbModel> GetOrderByDynamicQuery <TDbModel>(this IQueryable <TDbModel> query, GridOrder order)
where TDbModel : class =>
order.Order switch
{
public static IQueryable <TDbModel> ApplyDatabaseDataOrder <TDbModel>(this IQueryable <TDbModel> query, GridOrder order)
where TDbModel : class
{
if (order == null || !(typeof(TDbModel).GetProperties()
.FirstOrDefault(prop => string.Equals(prop.Name.ToLower(), order?.GetParentFieldName().ToLower(),
StringComparison.OrdinalIgnoreCase)) is var property) ||
property == null)
{
return(query);
}
if (order.IsNestedObject() && !order.CheckChildNodesAndSetLastChildFieldType(property, order.GetChildrenFieldsNames()))
{
return(query);
}
return(query.GetOrderByDynamicQuery(order));
}
private void GridOrder_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
GridOrder.CommitEdit(DataGridViewDataErrorContexts.Commit);
}
private void But_connect_Click(object sender, EventArgs e)
{
connection = new SqlConnection(connectString);
connection.Open();
//MessageBox.Show(connection.State.ToString());
ds = new DataSet("Northwind");
SqlCommand command;
daState = new SqlDataAdapter();
command = new SqlCommand("Select * From State", connection);
daState.SelectCommand = command;
dtState = ds.Tables.Add("State");
daState.Fill(dtState);
daCustomer = new SqlDataAdapter();
// - SELECT
command = new SqlCommand("Select ':)' as Etykieta,* From Customer", connection);
daCustomer.SelectCommand = command;
// - INSERT
command = new SqlCommand("INSERT INTO Customer (FirstName,LastName,Address,City,State,ZipCode,AccountNumber) VALUES (@FirstName,@LastName,@Address,@City,@State,@ZipCode,@AccountNumber)", connection);
//command.Parameters.Add("@id", SqlDbType.Int, 0, "id");
command.Parameters.Add("@FirstName", SqlDbType.NVarChar, 40, "FirstName");
command.Parameters.Add("@LastName", SqlDbType.NVarChar, 40, "LastName");
command.Parameters.Add("@Address", SqlDbType.NVarChar, 40, "Address");
command.Parameters.Add("@City", SqlDbType.NVarChar, 40, "City");
command.Parameters.Add("@State", SqlDbType.NVarChar, 40, "State");
command.Parameters.Add("@ZipCode", SqlDbType.NVarChar, 40, "ZipCode");
command.Parameters.Add("@AccountNumber", SqlDbType.NVarChar, 40, "AccountNumber");
daCustomer.InsertCommand = command;
// - UPDATE
command = new SqlCommand("UPDATE Customer SET FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, State = @State, ZipCode = @ZipCode, AccountNumber = @AccountNumber WHERE id = @oldid", connection);
//command.Parameters.Add("@id", SqlDbType.NChar, 5, "id");
command.Parameters.Add("@FirstName", SqlDbType.NVarChar, 40, "FirstName");
command.Parameters.Add("@LastName", SqlDbType.NVarChar, 40, "LastName");
command.Parameters.Add("@Address", SqlDbType.NVarChar, 40, "Address");
command.Parameters.Add("@City", SqlDbType.NVarChar, 40, "City");
command.Parameters.Add("@State", SqlDbType.NVarChar, 40, "State");
command.Parameters.Add("@ZipCode", SqlDbType.NVarChar, 40, "ZipCode");
command.Parameters.Add("@AccountNumber", SqlDbType.NVarChar, 40, "AccountNumber");
SqlParameter parameter = command.Parameters.Add("@oldid", SqlDbType.Int, 0, "id");
parameter.SourceVersion = DataRowVersion.Original;
daCustomer.UpdateCommand = command;
// - DELETE
command = new SqlCommand("DELETE FROM Customer WHERE id = @id", connection);
// Add the parameters for the DeleteCommand.
parameter = command.Parameters.Add("@id", SqlDbType.Int, 0, "id");
parameter.SourceVersion = DataRowVersion.Original;
daCustomer.DeleteCommand = command;
dtCustomer = ds.Tables.Add("Customer");
daCustomer.Fill(dtCustomer);
bsCustomer = new BindingSource();
bsCustomer.DataSource = dtCustomer;
//bsCustomer.CurrentChanged += new EventHandler(Current_Changed);
GridCustomer.AutoSize = false;
GridCustomer.DataSource = bsCustomer;
NavigatorCustomer.BindingSource = bsCustomer;
GridCustomer.Use(p =>
{
p.ColumnHeadersDefaultCellStyle.Font = new Font("Tahoma", 9, FontStyle.Bold);
p.ColumnHeadersDefaultCellStyle.ForeColor = Color.Black;
p.ColumnHeadersDefaultCellStyle.BackColor = Color.Yellow;
p.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
p.EnableHeadersVisualStyles = false;
p.AutoGenerateColumns = false;
p.Columns.Clear();
int iCol;
iCol = p.Columns.Add("col_FirstName", "Imię");
p.Columns[iCol].DataPropertyName = "FirstName";
iCol = p.Columns.Add("col_LastName", "Nazwisko");
p.Columns[iCol].DataPropertyName = "LastName";
iCol = p.Columns.Add("col_Address", "Ulica");
p.Columns[iCol].DataPropertyName = "Address";
iCol = p.Columns.Add("col_City", "Miejscowość");
p.Columns[iCol].DataPropertyName = "City";
//iCol = p.Columns.Add("col_State", "Województwo");
//p.Columns[iCol].DataPropertyName = "State";
DataGridViewComboBoxColumn cb = new DataGridViewComboBoxColumn();
cb.HeaderText = "Województwo";
cb.DataPropertyName = "State";
cb.Name = "col_State";
cb.MaxDropDownItems = 4;
cb.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
//cb.Items.Add("Śląskie");
//cb.Items.Add("Małopolskie");
cb.DataSource = dtState;
cb.ValueMember = "name";
cb.DisplayMember = "name";
p.Columns.Add(cb);
iCol = p.Columns.Add("col_ZipCode", "Kod pocztowy");
p.Columns[iCol].DataPropertyName = "ZipCode";
iCol = p.Columns.Add("col_AccountNumber", "Nr konta");
p.Columns[iCol].DataPropertyName = "AccountNumber";
//p.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
});
daOrder = new SqlDataAdapter();
// - SELECT
command = new SqlCommand("Select * From Orders", connection);
daOrder.SelectCommand = command;
// - INSERT
command = new SqlCommand("INSERT INTO ORDERS (CustomerId,OrderDate,Invoice,Paymend,Price) VALUES (@CustomerId,@OrderDate,@Invoice,@Paymend,@Price)", connection);
command.Parameters.Add("@CustomerId", SqlDbType.Int, 0, "CustomerId");
command.Parameters.Add("@OrderDate", SqlDbType.Date, 0, "OrderDate");
command.Parameters.Add("@Invoice", SqlDbType.NVarChar, 100, "Invoice");
command.Parameters.Add("@Paymend", SqlDbType.Int, 0, "Paymend");
command.Parameters.Add("@Price", SqlDbType.Money, 0, "Price");
daOrder.InsertCommand = command;
// - UPDATE
command = new SqlCommand("UPDATE ORDERS SET OrderDate = @OrderDate, Invoice = @Invoice, Paymend = @Paymend, Price = @Price WHERE id = @oldid", connection);
command.Parameters.Add("@OrderDate", SqlDbType.Date, 0, "OrderDate");
command.Parameters.Add("@Invoice", SqlDbType.NVarChar, 100, "Invoice");
command.Parameters.Add("@Paymend", SqlDbType.Int, 0, "Paymend");
command.Parameters.Add("@Price", SqlDbType.Money, 0, "Price");
parameter = command.Parameters.Add("@oldid", SqlDbType.Int, 0, "id");
parameter.SourceVersion = DataRowVersion.Original;
daOrder.UpdateCommand = command;
// - DELETE
command = new SqlCommand("DELETE FROM ORDERS WHERE id = @id", connection);
// Add the parameters for the DeleteCommand.
parameter = command.Parameters.Add("@id", SqlDbType.Int, 0, "id");
parameter.SourceVersion = DataRowVersion.Original;
daOrder.DeleteCommand = command;
dtOrder = ds.Tables.Add("Orders");
daOrder.Fill(dtOrder);
DataRelation relCustOrder = new DataRelation("CustomersOrders", dtCustomer.Columns["id"], dtOrder.Columns["customerID"]);
// Add the relation to the DataSet.
ds.Relations.Add(relCustOrder);
bsOrder = new BindingSource();
bsOrder.DataSource = bsCustomer;
bsOrder.DataMember = "CustomersOrders";
// --
NavigatorOrder.BindingSource = bsOrder;
GridOrder.Use(p =>
{
p.DataSource = bsOrder;
p.AutoSize = false;
p.ColumnHeadersDefaultCellStyle.Font = new Font("Tahoma", 9, FontStyle.Bold);
p.ColumnHeadersDefaultCellStyle.ForeColor = Color.Black;
p.ColumnHeadersDefaultCellStyle.BackColor = Color.Yellow;
p.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
p.EnableHeadersVisualStyles = false;
p.AutoGenerateColumns = false;
p.Columns.Clear();
//p.Columns.Add("OrderDate", "Data zamówienia");
DataGridViewColumn col1 = new DataGridViewTextBoxColumn();
col1.DataPropertyName = "Invoice";
col1.Name = "Invoice";
col1.HeaderText = "Nr paragonu";
col1.ValueType = typeof(string);
p.Columns.Add(col1);
col1.Dispose();
//DataGridViewColumn col2 = new DataGridViewTextBoxColumn();
CalendarColumn col2 = new CalendarColumn();
col2.DataPropertyName = "OrderDate";
col2.Name = "OrderDate";
col2.HeaderText = "Data zamówienia";
col2.ValueType = typeof(DateTime);
p.Columns.Add(col2);
col2.Dispose();
DataGridViewCheckBoxColumn chbox = new DataGridViewCheckBoxColumn();
chbox.DataPropertyName = "Paymend";
chbox.Name = "Paymend";
chbox.HeaderText = "Czy zapłacone";
chbox.ValueType = typeof(int);
p.Columns.Add(chbox);
DataGridViewTextBoxColumn col4 = new DataGridViewTextBoxColumn();
col4.DataPropertyName = "Price";
col4.Name = "Price";
col4.HeaderText = "Kwota";
col4.ValueType = typeof(float);
col4.DefaultCellStyle.Format = "c";
p.Columns.Add(col4);
DataGridViewImageColumn img = new DataGridViewImageColumn();
img.Name = "Image";
img.HeaderText = "Ikona";
img.ValuesAreIcons = true;
p.Columns.Add(img);
});
ControlsStat();
}
private void btn_Save_Click(object sender, EventArgs e)
{
Action_DbSave();
GridCustomer.Refresh();
GridOrder.Refresh();
}
public ActionResult Search(long activeId, long themeId,
long provinceId, long areaId, long deliveryStatus,
// long stateId,
long maxScore, long minScore, long isPass,
int current, int rowCount, string searchPhrase, FormCollection fc)
{
var user = ResSettings.SettingsInSession.User;
//----------------------------------------------------------
var t = APDBDef.CroResource;
var u = APDBDef.ResUser;
APSqlOrderPhrase order = null;
APSqlWherePhrase where = t.StatePKID != 10359 & t.ActiveId == ThisApp.CurrentActiveId; //TODO 只取2018
// 取排序
var co = GridOrder.GetSortDef(fc);
if (co != null)
{
switch (co.Id)
{
case "Title": order = new APSqlOrderPhrase(t.Title, co.Order); break;
case "Author": order = new APSqlOrderPhrase(u.RealName, co.Order); break;
case "CreatedTime": order = new APSqlOrderPhrase(t.CreatedTime, co.Order); break;
// case "State": order = new APSqlOrderPhrase(t.StatePKID, co.Order); break;
case "Score": order = new APSqlOrderPhrase(t.Score, co.Order); break;
case "WinLevel": order = new APSqlOrderPhrase(t.WinLevelPKID, co.Order); break;
}
}
// 按作品标题,内容，作者等过滤
if (searchPhrase != null)
{
searchPhrase = searchPhrase.Trim();
if (searchPhrase != "")
{
where &= t.Title.Match(searchPhrase) | t.Author.Match(searchPhrase) | t.Description.Match(searchPhrase) | t.AuthorCompany.Match(searchPhrase);
}
}
// 按照主题过滤
if (themeId > 0)
{
where &= t.ThemeId == themeId;
}
// 用户数据范围或搜索
if (user.ProvinceId > 0 || provinceId > 0)
{
where &= t.ProvinceId == (provinceId > 0 ? provinceId : user.ProvinceId);
}
if (user.AreaId > 0 || areaId > 0)
{
where &= t.AreaId == (areaId > 0 ? areaId : user.AreaId);
}
if (maxScore > 0)
{
where &= t.Score <= maxScore;
}
if (minScore > 0)
{
where &= t.Score >= minScore;
}
// 按项目数据过滤
if (activeId > 0)
{
where &= t.ActiveId == activeId;
}
// 按照报送状态过滤
if (deliveryStatus > -1)
{
where &= t.DeliveryStatus == deliveryStatus;
}
//if (stateId > 0)
// where &= t.StatePKID == stateId;
if (isPass > 0)
{
APDBDef.EvalResultTableDef er = APDBDef.EvalResult;
var subQuery = APQuery.select(er.ResourceId).from(er).where (er.EvalType == EvalGroupHelper.FirstTrial);
where &= t.CrosourceId.In(subQuery);
}
int total;
var list = APBplDef.CroResourceBpl.TolerantSearch(out total, current, rowCount, where, order);
if (Request.IsAjaxRequest())
{
return(Json(
new
{
rows = from cro in list
select new
{
id = cro.CrosourceId,
cro.Title,
cro.Author,
CreatedTime = cro.CreatedTime.ToString("yyyy-MM-dd"),
cro.Province,
cro.Area,
//cro.State,
cro.StatePKID,
cro.Score,
cro.WinLevel,
cro.WinLevelPKID,
cro.DeliveryStatusName,
cro.DeliveryStatus,
cro.Theme
},
current = current,
rowCount = rowCount,
total = total
}));
}
else
{
return(View(list));
}
}
public ActionResult Category(int current, int rowCount, string searchPhrase, FormCollection fc)
{
//----------------------------------------------------------
var t = APDBDef.CroResource;
var u = APDBDef.ResUser;
APSqlOrderPhrase order = null;
List <APSqlWherePhrase> conds = new List <APSqlWherePhrase>()
{
t.StatePKID != CroResourceHelper.StateDelete
};
// 取排序
var co = GridOrder.GetSortDef(fc);
if (co != null)
{
switch (co.Id)
{
case "Title": order = new APSqlOrderPhrase(t.Title, co.Order); break;
case "Author": order = new APSqlOrderPhrase(u.RealName, co.Order); break;
case "CreatedTime": order = new APSqlOrderPhrase(t.CreatedTime, co.Order); break;
case "State": order = new APSqlOrderPhrase(t.StatePKID, co.Order); break;
}
}
// 取过滤条件
foreach (string cond in fc.Keys)
{
switch (cond)
{
case "State": conds.Add(t.StatePKID == Int64.Parse(fc[cond])); break;
}
}
// 按关键字过滤
if (searchPhrase != null)
{
searchPhrase = searchPhrase.Trim();
if (searchPhrase != "")
{
conds.Add(t.Title.Match(searchPhrase));
}
}
int total;
var list = APBplDef.CroResourceBpl.TolerantSearch(out total, current, rowCount, new APSqlConditionAndPhrase(conds), order);
//----------------------------------------------------------
if (Request.IsAjaxRequest())
{
return(Json(
new
{
rows = from cro in list
select new
{
//id = cro.CrosourceId,
//cro.Title,
//cro.Author,
//cro.MediumType,
//CreatedTime = cro.CreatedTime.ToString("yyyy-MM-dd"),
//cro.State,
//cro.StatePKID,
//cro.EliteScore,
//cro.ViewCount,
//cro.DownCount,
//cro.FavoriteCount,
//cro.CommentCount,
//cro.StarTotal
},
current = current,
rowCount = rowCount,
total = total
}));
}
else
{
return(View(list));
}
}
private static IOrderedQueryable <TDbModel> GetOrderByDynamicQuery <TDbModel>(this IQueryable <TDbModel> query, GridOrder order, bool isFirstOrder = true)
where TDbModel : class
{
var command = order.Order switch
{
OrderChoice.Ascending when isFirstOrder => "OrderBy",
OrderChoice.Descending when isFirstOrder => "OrderByDescending",
OrderChoice.Ascending when !isFirstOrder => "ThenBy",
OrderChoice.Descending when !isFirstOrder => "ThenByDescending",
_ when isFirstOrder => "OrderBy",
_ when !isFirstOrder => "ThenBy"
};
GetExpressionPropertyWithParameter(typeof(TDbModel), order.OrderBy, out var type, out var parameter, out var propertyExpression);
var orderByExpression = Expression.Lambda(propertyExpression, parameter);
var resultExpression = Expression.Call(typeof(Queryable), command, new[] { type, order.GetLastChildrenFieldType() ?? propertyExpression.Type },
query.Expression, Expression.Quote(orderByExpression));
return((IOrderedQueryable <TDbModel>)query.Provider.CreateQuery <TDbModel>(resultExpression));
}
