private void BtnAlert_Click(object sender, EventArgs e)
{
SetEnable(false, false, false, false, false, "当前操作模式：商品告急目录", false, "确定", "other", false);
string sql = "select Inventory.ProductID as '商品编号',ProductName as '商品名称',CategoryID as'供应商编号',Quantity as '库存',ThrValue as '阈值' from Products left join Inventory on Products.ProductID=Inventory.ProductID where Products.ThrValue > Inventory.Quantity";
DGVInventory.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
TextBoxEmpty();
}
private void Query()
{
string sql = "select Inventory.ProductID as '商品编号',ProductName as '商品名称',CategoryID as'供应商编号',Quantity as '库存',ThrValue as '阈值' from Products left join Inventory on Products.ProductID=Inventory.ProductID where 1=1";
if (TbProID.Text != "")
{
sql += " and Inventory.ProductID like'%" + TbProID.Text.Trim() + "%'";
}
if (TbProName.Text != "")
{
sql += " and ProductName like'%" + TbProName.Text.Trim() + "%'";
}
if (TbSupID.Text != "")
{
sql += " and CategoryID like'%" + TbSupID.Text.Trim() + "%'";
}
DGVInventory.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void ProductInfomation_Load(object sender, EventArgs e)
{
{
string sql = "select SupplierID,CompanyName from dbo.Suppliers";
tableSuppliers = GuGuGuHelper.ExecuteReaderDataTable(sql);
GuGuGuHelper.BindCombo(this.CbxCompanyName, tableSuppliers, "SupplierID", "CompanyName", true);
}
{
string sql = "select CategoryID,CategoryName from dbo.Categories";
tableCategories = GuGuGuHelper.ExecuteReaderDataTable(sql);
GuGuGuHelper.BindCombo(this.CbxCategoryName, tableCategories, "CategoryID", "CategoryName", true);
}
ProRefresh();
DGVProduct.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
DGVProduct.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
DGVProduct.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
SetEnable(false, false, false, false, false, false, false, "请选择操作模式", false, "确定", false, "other");
}
private void Query()
{
string sql = "select CategoryID as '商品类别编号',CategoryName as '类别名',Description as '类别描述' from Categories where 1 = 1";
if (TbID.Text != "")
{
sql += " and CategoryID like'%" + TbID.Text.Trim() + "%'";
}
if (TbName.Text != "")
{
sql += " and CategoryName like'%" + TbName.Text.Trim() + "%'";
}
if (TbDe.Text != "")
{
sql += " and Description like'%" + TbDe.Text.Trim() + "%'";
}
//MessageBox.Show(sql);
DGVCategories.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void Search()
{
string sql = "select OrderID as '订单号',CustomerID as '顾客号',EmployeeID as '员工号',OrderDate as '订单日期',Freight as '配送费',ToTalPrice as '总金额',ShipName as '配送商',ShipAddress as '配送地址' from Orders where 1 = 1";
if (TbOrdID.Text.Trim() != "")
{
sql += " and OrderID like '%" + TbOrdID.Text.Trim() + "%'";
}
if (TimePickerEnd.Value.Date >= TimePickerStart.Value.Date)
{
//MessageBox.Show(TimePickerStart.Value.Date.ToString("yyyy-MM-dd"));
//MessageBox.Show(TimePickerEnd.Value.Date.ToString("yyyy-MM-dd"));
sql += " and OrderDate >= '" + TimePickerStart.Value.Date.ToString("yyyy-MM-dd") + "' and OrderDate <= '" + TimePickerEnd.Value.Date.ToString("yyyy-MM-dd") + "'";
}
if (TbCusID.Text.Trim() != "")
{
sql += " and CustomerID like%'" + TbCusID.Text.Trim() + "%'";
}
//MessageBox.Show(sql);
DGVOrder.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void Query()
{
string sql = "select ProductID as '商品号',ProductName as '商品名称',CompanyName as '供应商名',categoryname as '类别名',UnitPrice as '单价',ThrValue as '阈值', Discont as '折扣' from Products Left join Categories on categories.categoryID = Products.CategoryID Left join Suppliers on suppliers.supplierID = products.supplierID where 1 = 1";
if (TbProductID.Text != "")
{
sql += "and productID like '%" + TbProductID.Text.Trim() + "%'";
}
if (TbProductName.Text != "")
{
sql += "and productName like '%" + TbProductName.Text.Trim() + "%'";
}
if (CbxCompanyName.SelectedIndex > 0)
{
sql += "and Products.SupplierID = '" + CbxCompanyName.SelectedValue + "'";
}
if (CbxCategoryName.SelectedIndex > 0)
{
sql += "and Products.CategoryID = '" + CbxCategoryName.SelectedValue + "'";
}
if (TbUnitPrice.Text != "")
{
sql += "and UnitPrice = '" + TbUnitPrice.Text.Trim() + "'";
}
if (TbValue.Text != "")
{
sql += "and ThrValue ='" + TbValue.Text.Trim() + "'";
}
if (RbOK.Checked)
{
sql += "and Discont = 'true'";
}
if (RbNO.Checked)
{
sql += "and Discont = 'false'";
}
DGVProduct.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void InfoView()
{
string sql = "select Inventory.ProductID as '商品编号',ProductName as '商品名称' from Products left join Inventory on Products.ProductID=Inventory.ProductID where Products.ThrValue > Inventory.Quantity";
if (!GuGuGuHelper.ExcuteExist(sql))
{
LbInfo.Text = "商品告急信息：当前暂无商品缺货";
LbProTip.Text = "";
return;
}
else
{
LbProTip.Text = "";
LbInfo.Text = "商品告急信息：以下商品库存紧急，详细信息请到”库存信息 > 商品库存查询“处查看";
}
{
DataTable table = GuGuGuHelper.ExecuteReaderDataTable(sql);
for (int i = 0; i < table.Rows.Count; i++)
{
LbProTip.Text += "商品编号：" + table.Rows[i]["商品编号"].ToString() + " 商品名称：" + table.Rows[i]["商品名称"].ToString() + "\r\n\r\n";
}
}
}
private void ShopRefresh()
{
string sql = "select ProductID as '商品号',ProductName as '商品名',UnitPrice as '单价',Description as '描述',Discont as '折扣'from Products";
DGVProView.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void TbSearch_TextChanged(object sender, EventArgs e)
{
string sql = "select ProductID as '商品号',ProductName as '商品名',UnitPrice as '单价',Description as '描述',Discont as '折扣'from Products where ProductID like'%" + TbSearch.Text.Trim() + "%' or ProductName like'%" + TbSearch.Text.Trim() + "%'";
DGVProView.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void StockReFresh()
{
string sql = "select Inventory.ProductID as '商品编号',ProductName as '商品名称',CategoryID as'供应商编号',Quantity as '库存' from Products left join Inventory on Products.ProductID=Inventory.ProductID";
DGVInventory.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void OrderRefresh()
{
string sql = "select OrderID as '订单号',CustomerID as '顾客号',EmployeeID as '员工号',OrderDate as '订单日期',Freight as '配送费',ToTalPrice as '总金额',ShipName as '配送商',ShipAddress as '配送地址' from Orders";
DGVOrder.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void ProRefresh()
{
string sql = @"select ProductID as '商品号',ProductName as '商品名称',CompanyName as '供应商名',categoryname as '类别名',UnitPrice as '单价',ThrValue as '阈值', Discont as '折扣' from Products Left join Categories on categories.categoryID = Products.CategoryID Left join Suppliers on suppliers.supplierID = products.supplierID";
DGVProduct.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
/// <summary>
/// 查询语句结果是否存在行
/// </summary>
/// <param name="sql">要查询的语句</param>
/// <param name="paraList">变量列表Sqlparameters(可选)</param>
/// <param name="commType">要查询语句的类型</param>
/// <returns>返回bool值</returns>
public static bool ExcuteExist(string sql, List <SqlParameter> paraList = null, CommandType commType = CommandType.Text)
{
DataTable data = GuGuGuHelper.ExecuteReaderDataTable(sql, paraList, commType);
return((data.Rows.Count > 0)? true : false);
}
/// <summary>
/// 离线查询数据DataTable(返回表的第一行DataRow)
/// </summary>
/// <param name="sql">要查询的语句</param>
/// <param name="paraList">变量列表Sqlparameters(可选)</param>
/// <param name="commType">要查询语句的类型</param>
/// <returns>返回查询结果的第一行DataRow</returns>
public static DataRow ExecuteReaderDataRow(string sql, List <SqlParameter> paraList = null, CommandType commType = CommandType.Text)
{
DataTable data = GuGuGuHelper.ExecuteReaderDataTable(sql, paraList, commType);
return((data.Rows.Count > 0) ? data.Rows[0] : null);
}
private void OrderDetailsRefresh()
{
string sql = string.Format("select Products.ProductID as '商品编号', Products.ProductName as '商品名',\"Order Details\".UnitPrice as '单价',Quantity as '数量',Discount as '折扣' from \"Order Details\" join Products on Products.ProductID = \"Order Details\".ProductID where OrderID = '{0}'", DGVOrder.Rows[selectRow].Cells["订单号"].Value.ToString());
DGVOrderDetails.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void ProRefresh()
{
string sql = "select CustomerID as '客户编号',CustomerName as '客户名',Address as'地址',Phone as '电话',Credits as '积分',Notes as '客户类别' from Customers ";
CustomerMain.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void CateRefresh()
{
string sql = "select CategoryID as '商品类别编号',CategoryName as '类别名',Description as '类别描述' from Categories";
DGVCategories.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}