/// <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')";
}