/// <summary>
/// 获取查询条件并封装
/// </summary>
/// <returns></returns>
private QueryParaModel GetParaModel()
{
//条件获取与封装
int unitId = 0;
if (unit != null)
{
unitId = unit.UnitId;
}
string unitName = txtCustomerName.Text.Trim();
string goodsName = txtGoodsName.Text.Trim();
string dealPerson = txtDealPerson.Text.Trim();
int storeId = 0;
string storeName = txtStore.Text.Trim();
if (store != null)
{
storeId = store.StoreId;
}
//封装查询条件实体
QueryParaModel pModel = new QueryParaModel()
{
StoreId = storeId,
StoreName = storeName,
UnitId = unitId,
UnitName = unitName,
GoodsName = goodsName,
DealPerson = dealPerson
};
return(pModel);
}
/// <summary>
/// 按商品统计销售数据
/// </summary>
/// <param name="paraModel"></param>
/// <param name="startIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PageModel <SaleQueryGoodsModel> GetSaleDataByGoods(QueryParaModel paraModel, int startIndex, int pageSize)
{
DataSet ds = vsgqDAL.GetSaleData(3, paraModel, startIndex, pageSize);
BQuery <SaleQueryGoodsModel> bq = new BQuery <SaleQueryGoodsModel>();
PageModel <SaleQueryGoodsModel> pageModel = bq.DsToPageModel <SaleQueryGoodsModel>(ds, "");
return(pageModel);
}
/// <summary>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsbtnOutToExcel_Click(object sender, EventArgs e)
{
QueryParaModel qModel = GetParaModel();
List <SaleQueryGoodsModel> saleData = RequestStar.GetSaleDataByGoods(qModel, 1, 100000000).ReList;
string fileName = "销售统计——按商品";
FormUtility.DataToExcel <SaleQueryGoodsModel>(saleData, dgvGoodsList.Columns, fileName + ".xls", fileName, fileName, "导出按商品统计销售数据");
}
/// <summary>
/// 按仓库统计采购数据
/// </summary>
/// <param name="paraModel"></param>
/// <param name="startIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PageModel <PerQueryStoreModel> GetPerDataByStore(QueryParaModel paraModel, int startIndex, int pageSize)
{
DataSet ds = vpgqDAL.GetPerData(2, paraModel, startIndex, pageSize);
BQuery <PerQueryStoreModel> bq = new BQuery <PerQueryStoreModel>();
PageModel <PerQueryStoreModel> pageModel = bq.DsToPageModel <PerQueryStoreModel>(ds, "");
return(pageModel);
}
/// <summary>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsbtnOutToExcel_Click(object sender, EventArgs e)
{
//获取查询条件并封装
QueryParaModel paraModel = GetParaModel();
//条件查询出来的所有数据
List <SaleQueryCustomerModel> saleData = RequestStar.GetSaleDataByCustomer(paraModel, 1, 100000000).ReList;
string fileName = "销售统计——按客户";
FormUtility.DataToExcel <SaleQueryCustomerModel>(saleData, dgvGoodsList.Columns, fileName + ".xls", fileName, fileName, "导出按客户销售统计数据");
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsbtnOutToExcel_Click(object sender, EventArgs e)
{
//获取查询条件并封装
QueryParaModel paraModel = GetParaModel();
//条件查询出来的所有数据
List <PerQueryStoreModel> perData = RequestStar.GetPerDataByStore(paraModel, 1, 100000000).ReList;
string fileName = "采购统计——按仓库";
FormUtility.DataToExcel <PerQueryStoreModel>(perData, dgvPerList.Columns, fileName + ".xls", fileName, fileName, "导出按仓库采购统计数据");
}
/// <summary>
/// 获取查询条件并封装
/// </summary>
/// <returns></returns>
private QueryParaModel GetParaModel()
{
//条件获取与封装
int unitId = 0;
if (unit != null)
{
unitId = unit.UnitId;
}
string unitName = txtCustomerName.Text.Trim();
int storeId = 0;
if (store != null)
{
storeId = store.StoreId;
}
string storeName = txtStore.Text.Trim();
int gTypeId = 0;
if (gtInfo != null)
{
gTypeId = gtInfo.GTypeId;
}
string goodsName = txtGoodsName.Text.Trim();
string dealPerson = txtDealPerson.Text.Trim();
int uTypeId = 0;
if (tvCustomerType.SelectedNode != null)
{
uTypeId = tvCustomerType.SelectedNode.Name.GetInt();
}
//封装查询条件实体
QueryParaModel pModel = new QueryParaModel()
{
UnitId = unitId,
UnitName = unitName,
StoreId = storeId,
StoreName = storeName,
GTypeId = gTypeId,
GoodsName = goodsName,
DealPerson = dealPerson,
UTypeId = uTypeId
};
return(pModel);
}
private void LoadSaleDataByCustomer()
{
QueryParaModel pModel = GetParaModel();
PageModel <SaleQueryCustomerModel> pageModel = RequestStar.GetSaleDataByCustomer(pModel, ucPager1.StartRecord, ucPager1.PageSize);
if (pageModel != null)
{
if (pageModel.ReList.Count > 0)
{
dgvGoodsList.AutoGenerateColumns = false;
dgvGoodsList.DataSource = pageModel.ReList;
ucPager1.Visible = true;
ucPager1.Record = pageModel.TotalCount;
}
else
{
dgvGoodsList.DataSource = null;
dgvGoodsList.AllowUserToAddRows = false;
ucPager1.Visible = false;
}
}
}
/// <summary>
/// 采购统计 分页
/// </summary>
/// <param name="typeId"></param>
/// <param name="paraModel"></param>
/// <returns></returns>
public DataSet GetPerData(int typeId, QueryParaModel paraModel, int startIndex, int pageSize)
{
string sql = " select row_number() over (order by ";
string orColName = "";
string selCols = "";
switch (typeId)
{
case 1:
orColName = "UnitId";
selCols = "UnitId,UnitName";
break;
case 2:
orColName = "StoreId";
selCols = "StoreId,StoreName";
break;
case 3:
orColName = "GoodsId";
selCols = "GoodsId,GoodsName,GoodsNo,GUnit";
break;
default: break;
}
sql += orColName + ") as Id," + selCols + " from (select distinct " + selCols + " from ViewPerGoodsQuery where 1=1";
//条件拼接
string strWhere = "";
if (paraModel != null)
{
if (!string.IsNullOrEmpty(paraModel.GoodsName))
{
strWhere += " and GoodsName like @goodsName";
}
if (paraModel.StoreId > 0)
{
strWhere += " and StoreId=@storeId";
}
else if (!string.IsNullOrEmpty(paraModel.StoreName))
{
strWhere += " and StoreName like @storeName";
}
if (paraModel.GTypeId > 0)
{
strWhere += " and (GTypeId in (select GTypeId from GoodsTypeInfos where GTypeId=@gTypeId or ParentId in (select GTypeId from GoodsTypeInfos where ParentId=@gTypeId or GTypeId=@gTypeId)))";
}
if (paraModel.UTypeId > 0)
{
strWhere += " and (UTypeId in (select UTypeId from UnitTypeInfos where UTypeId=@uTypeId or ParentId in (select UTypeId from UnitTypeInfos where ParentId=@uTypeId or UTypeId=@uTypeId)))";
}
if (paraModel.UnitId > 0)
{
strWhere += " and UnitId=@unitId";
}
else if (!string.IsNullOrEmpty(paraModel.UnitName))
{
strWhere += " and UnitName like @unitName";
}
if (!string.IsNullOrEmpty(paraModel.DealPerson))
{
strWhere += " and DealPerson like @dealPerson";
}
}
sql += strWhere;
sql += ") as temp ";
string sql1 = $"select count(1) from ({sql}) as temp2";//获取总记录数
string sql2 = $"{sql1};select * from ({sql}) as temp3 where Id between {startIndex} and {(startIndex + pageSize - 1)}";
//参数提供
SqlParameter[] paras =
{
new SqlParameter("@goodsName", $"%{paraModel.GoodsName}%"),
new SqlParameter("@storeId", paraModel.StoreId),
new SqlParameter("@storeName", $"%{paraModel.StoreName}%"),
new SqlParameter("@gTypeId", paraModel.GTypeId),
new SqlParameter("@uTypeId", paraModel.UTypeId),
new SqlParameter("@unitId", paraModel.UnitId),
new SqlParameter("@unitName", $"%{paraModel.UnitName}%"),
new SqlParameter("@dealPerson", $"%{paraModel.DealPerson}%"),
};
DataSet ds = SqlHelper.GetDataSet(sql2, 1, paras);
if (ds.Tables[1].Rows.Count > 0)
{
DataTable dt = ds.Tables[1];
//再次处理？？？---目前得到数据，没有进行采购统计
dt.Columns.Add("TotalCount", typeof(int));
dt.Columns.Add("TotalAmount", typeof(decimal));
if (typeId == 1 || typeId == 3)
{
dt.Columns.Add("AvgPrice", typeof(decimal));
}
if (typeId == 1)
{
dt.Columns.Add("TotalYHAmount", typeof(decimal));
}
string totalCols = "sum(Count) TotalCount,sum(Amount) TotalAmount";
if (typeId == 1 || typeId == 3)
{
totalCols += ",convert(decimal(18,2),sum(Amount)/Sum(Count)) as AvgPrice";
}
//if (typeId == 1)
// totalCols += ",sum(YHAmount) as YHAmount";
//统计查询 typeId id(unitId storeId GoodsId) totalCols strWhere paras
foreach (DataRow dr in dt.Rows)
{
int id = dr[orColName].ToString().GetInt();
DataTable dtData = GetPerTotal(id, typeId, totalCols, strWhere, paras);
if (dtData.Rows.Count > 0)
{
dr["TotalCount"] = dtData.Rows[0]["TotalCount"];
dr["TotalAmount"] = dtData.Rows[0]["TotalAmount"];
if (typeId == 1 || typeId == 3)
{
dr["AvgPrice"] = dtData.Rows[0]["AvgPrice"];
}
if (typeId == 1)
{
dr["TotalYHAmount"] = dtData.Rows[0]["TotalAmount"];
}
}
}
}
return(ds);
}
