/// <summary> /// 生成销售汇总(按单品汇总) /// </summary> /// <param name="BeginDate">统计开始时间</param> /// <param name="EndDate">统计终止时间</param> /// <returns>DataSet</returns> public DataSet GetSaleGather(string BeginDate, string EndDate) { StringBuilder strSql = new StringBuilder(); strSql.Append("select Product_ID,ProductName,SpecName,UnitName,shortname,Name,sum(Quantity) as totalQty from SaleDefinite where saledate between '" + BeginDate + "' and '" + Convert.ToDateTime(EndDate).AddDays(1) + "' group by Product_ID,ProductName,SpecName,UnitName,shortname,Name"); return(ExecuteSql.ExeComSqlForDataSet(strSql.ToString())); }
/// <summary> /// 销售明细表 /// </summary> /// <param name="BeginDate"></param> /// <param name="EndDate"></param> /// <returns></returns> public DataSet GetSaleDefinite(string BeginDate, string EndDate) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from SaleDefinite where saledate between '" + BeginDate + "' and '" + Convert.ToDateTime(EndDate).AddDays(1) + "'"); return(ExecuteSql.ExeComSqlForDataSet(strSql.ToString())); }
/// <summary> /// 绑定折扣 /// </summary> /// <param name="cb"></param> public static void BindDiscount(ComboBox cb) { DataTable dt = ExecuteSql.ExeComSqlForDataSet("SELECT DiscountRate,DisName FROM Discount WHERE Available = 1").Tables[0]; cb.DisplayMember = "DisName"; cb.ValueMember = "DiscountRate"; cb.DataSource = dt; }
/// <summary> /// 绑定客户 /// </summary> /// <param name="cb">ComboBox控件</param> public static void BindCustomer(ComboBox cb) { DataTable dt = ExecuteSql.ExeComSqlForDataSet("SELECT Customer_ID,Name FROM Customer ").Tables[0]; cb.DisplayMember = "Name"; cb.ValueMember = "Customer_ID"; cb.DataSource = dt; }
/// <summary> /// 绑定仓库 /// </summary> /// <param name="cb">ComboBox控件</param> public static void BindStoreHouse(ComboBox cb) { DataTable dt = ExecuteSql.ExeComSqlForDataSet("SELECT StoreHouse_ID,Address FROM StoreHouse ").Tables[0]; cb.DisplayMember = "Address"; cb.ValueMember = "StoreHouse_ID"; cb.DataSource = dt; }
/// <summary> /// 绑定人员 /// </summary> /// <param name="cb">ComboBox控件</param> public static void BindEmployee(ComboBox cb) { DataTable dt = ExecuteSql.ExeComSqlForDataSet("SELECT Employee_ID,Name FROM Employee ").Tables[0]; cb.DisplayMember = "Name"; cb.ValueMember = "Employee_ID"; cb.DataSource = dt; }
/// <summary> /// 生成销售汇总(按单号汇总) /// </summary> /// <param name="BeginDate"></param> /// <param name="EndDate"></param> /// <returns></returns> public DataSet GetGatherByList(string BeginDate, string EndDate) { StringBuilder strSql = new StringBuilder(); strSql.Append("select Sale_ID, TradeNo,StoreAdd,count(TradeNo) as TrCount, sum(Price*Quantity) as totalPrice,GatheringWay,Account,Address,SaleDate "); strSql.Append("from dbo.SaleDefinite where SaleDate between '" + BeginDate + "' and '" + Convert.ToDateTime(EndDate).AddDays(1) + "' group by Sale_ID, TradeNo,StoreAdd,Address,SaleDate,GatheringWay,Account"); return(ExecuteSql.ExeComSqlForDataSet(strSql.ToString())); }
/// <summary> /// 销售单明细 /// </summary> /// <param name="Sale_ID"></param> /// <returns></returns> public DataSet GetDetailAll(Int32 Sale_ID) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT Product_ID AS ProId,Name AS ProName,SupplierName,SpecName,UnitName,Quantity,Price AS UnitPrice,SaleOrder_ID,Discount,0 AS PaidInAmount,0 AS PaidIn1 FROM v_saledetail "); strSql.Append("WHERE Sale_ID = " + Sale_ID); return(ExecuteSql.ExeComSqlForDataSet(strSql.ToString())); }
/// <summary> /// 绑定部门(门店) /// </summary> /// <param name="cb">ComboBox控件</param> public static void BindDept(ComboBox cb) { /*List<string[]> liWhere = new List<string[]>(); * List<string[]> liOrder = new List<string[]>(); * liWhere.Add(new string[] { "1 = '{0}'", "1" }); * liOrder.Add(new string[] { "VehOrder", "ASC" });*/ DataTable dt = ExecuteSql.ExeComSqlForDataSet("SELECT Dept_ID,Name,Remark FROM Dept ").Tables[0]; cb.DisplayMember = "Name"; cb.ValueMember = "Dept_ID"; cb.DataSource = dt; }
/// <summary> /// 绑定大分类 /// </summary> /// <param name="cb">ComboBox控件</param> public static void BindProductClass(ComboBox cb) { DataTable dt = ExecuteSql.ExeComSqlForDataSet("SELECT ProductClass_ID,Name FROM ProductClass").Tables[0]; cb.DisplayMember = "Name"; cb.ValueMember = "ProductClass_ID"; DataRow dr = dt.NewRow(); dr["ProductClass_ID"] = 0; dr["Name"] = "<—全部—>"; dt.Rows.InsertAt(dr, 0); cb.DataSource = dt; cb.SelectedIndex = 0; }
private void LoadInfo(Int32 SaleID) { //DataGridViewComboBoxColumn dgvComboBoxColumn = dataGridView1.Columns["Discount"] as DataGridViewComboBoxColumn; DataGridViewComboBoxColumn dgvComboBoxColumn = (DataGridViewComboBoxColumn)this.dataGridView1.Columns["Discount"]; dgvComboBoxColumn.DataPropertyName = "Discount"; dgvComboBoxColumn.DataSource = ExecuteSql.ExeComSqlForDataSet("SELECT DiscountRate,DisName FROM Discount WHERE Available = 1").Tables[0]; dgvComboBoxColumn.DisplayMember = "DisName"; dgvComboBoxColumn.ValueMember = "DiscountRate"; //string strSql = "SELECT[Name],[ProductListName],[SpecName],[UnitName],[SupplierName],[spell],[shortname],[Price],[Offering_Price],[Product_ID],[ProductList_ID],[s_spell] FROM [ProInfoList]"; if (SaleID != 0) { /*this.Text = SaleID.ToString(); * SaleInfo.FindbyPK(SaleID.ToString()); * lbTradeNo.Text = SaleInfo.TradeNo; * tbxOrderNo.Text = SaleInfo.OrderNo; * tbxCustomer.Text = SaleInfo.Customer.ToString(); * tbxCustomer.ReadOnly = true; * tbxStoreHouse_ID.Text = SaleInfo.StoreHouse_ID.ToString(); * lbSaleDate.Text = SaleInfo.SaleDate.ToString("yyyy-MM-dd HH:mm:ss"); * * List<string[]> where = new List<string[]>(); * List<string[]> order = new List<string[]>(); * where.Add(new string[] { "SaleOrder_ID = {0}", SaleID.ToString() }); * order.Add(new string[] { "Sale_Detail_ID", "ASC" }); * DataTable dt = Product.GetDataSet(strSql, where, order); * dataGridView1.DataSource = dt;*/ DataTable dt = BLL_sDtl.GetDetailAll(SaleID).Tables[0]; dataGridView1.DataSource = dt; } else { DataTable dt = BLL_sDtl.GetDetailAll(SaleID).Tables[0]; dataGridView1.DataSource = dt; } ((DataTable)dataGridView1.DataSource).Columns["PaidInAmount"].Expression = "Convert(UnitPrice*Quantity*Discount, 'System.Decimal')"; ((DataTable)dataGridView1.DataSource).Columns["PaidIn1"].Expression = "Convert(UnitPrice*Discount, 'System.Decimal')"; }