/// <summary>
/// 导出所有的单据列表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsmiAllToExcel_Click(object sender, EventArgs e)
{
List <SheetInfoModel> totalList = new List <SheetInfoModel>();
//条件重置
ShQueryParaModel qModel = new ShQueryParaModel()
{
SheetNo = "",
Creator = "",
UnitName = "",
StoreName = "",
StoreId = 0,
UnitId = 0,
GoodsName = "",
CheckPerson = "",
DealPerson = "",
IsChecked = -1,
};
for (int i = 1; i <= 3; i++)
{
qModel.ShType = i;
List <SheetInfoModel> list = RequestStar.GetSheetList(qModel, 1, 100000000).ReList;
if (list.Count > 0)
{
totalList.AddRange(list);
}
}
FormUtility.DataToExcel <SheetInfoModel>(totalList, dgvList.Columns, "所有单据" + ".xls", "所有单据", "所有单据信息", "导出单据信息");
}
/// <summary>
/// 分页查询单据列表
/// </summary>
/// <param name="paraModel"></param>
/// <param name="startIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PageModel <SheetInfoModel> GetSheetList(ShQueryParaModel paraModel, int startIndex, int pageSize)
{
DataSet ds = GetSheetListDs(paraModel, startIndex, pageSize);
string cols = "Id,SheetId,SheetNo,ShType,IsChecked,CreateTime,UnitId,UnitName,YHAmount,DealPerson,Creator,CheckPerson,CheckTime,StoreId";
PageModel <SheetInfoModel> pageModel = DsToPageModel <SheetInfoModel>(ds, cols);
return(pageModel);
}
/// <summary>
/// 分页查询单据列表
/// </summary>
/// <param name="paraModel"></param>
/// <param name="startIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PageModel <SheetInfoModel> GetSheetList(ShQueryParaModel paraModel, int startIndex, int pageSize)
{
PageModel <SheetInfoModel> pageModel = sheetDAL.GetSheetList(paraModel, startIndex, pageSize);
foreach (SheetInfoModel s in pageModel.ReList)
{
s.CheckState = GetCheckState(s.IsChecked);
s.ShTypeName = GetShTypeName(s.ShType);
}
return(pageModel);
}
/// <summary>
/// 导出查询的单据列表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tsmiQueryToExcel_Click(object sender, EventArgs e)
{
//获取查询条件并封装
ShQueryParaModel paraModel = GetParaModel();
//条件查询出来的所有数据
List <SheetInfoModel> sheetList = RequestStar.GetSheetList(paraModel, 1, 100000000).ReList;
string shTypeName = "采购入库单";
if (tvSheetType.SelectedNode != null)
{
shTypeName = tvSheetType.SelectedNode.Text;
}
FormUtility.DataToExcel <SheetInfoModel>(sheetList, dgvList.Columns, shTypeName + ".xls", shTypeName, shTypeName, "导出单据信息");
}
/// <summary>
/// 获取查询条件并封装到ShQueryParaModel
/// </summary>
/// <returns></returns>
private ShQueryParaModel GetParaModel()
{
//条件获取
string sheetNo = txtShNo.Text.Trim();
int storeId = 0;
string storeName = txtStore.Text.Trim();
if (store != null)
{
storeId = store.StoreId;
}
int unitId = 0;
string unitName = txtUnitName.Text.Trim();
if (unit != null)
{
unitId = unit.UnitId;
}
string goodsName = txtGoodsName.Text.Trim();
string checkPerson = txtCheckPerson.Text.Trim();
string creator = txtCreator.Text.Trim();
string dealPerson = txtDealPerson.Text.Trim();
int isChecked = cboChecked.SelectedValue.GetInt();
int shType = 1;
if (tvSheetType.SelectedNode != null)
{
shType = tvSheetType.SelectedNode.Name.GetInt();
}
//条件封装 ShQueryParaModel
ShQueryParaModel paraModel = new ShQueryParaModel()
{
SheetNo = sheetNo,
ShType = shType,
Creator = creator,
StoreId = storeId,
StoreName = storeName,
UnitId = unitId,
UnitName = unitName,
GoodsName = goodsName,
CheckPerson = checkPerson,
DealPerson = dealPerson,
IsChecked = isChecked
};
return(paraModel);
}
/// <summary>
/// 条件查询单据列表
/// </summary>
private void LoadSheetList()
{
ShQueryParaModel paraModel = GetParaModel();
//调用查询方法
PageModel <SheetInfoModel> pageModel = RequestStar.GetSheetList(paraModel, ucPager1.StartRecord, ucPager1.PageSize);
if (pageModel != null)
{
if (pageModel.ReList.Count > 0)
{
dgvList.AutoGenerateColumns = false;
dgvList.DataSource = pageModel.ReList;
ucPager1.Visible = true;
ucPager1.Record = pageModel.TotalCount;
}
else
{
dgvList.DataSource = null;
dgvList.AllowUserToAddRows = false;
ucPager1.Visible = false;
}
}
}
/// <summary>
/// 条件查询单据列表
/// </summary>
/// <param name="paraModel"></param>
/// <param name="startIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public DataSet GetSheetListDs(ShQueryParaModel paraModel, int startIndex, int pageSize)
{
string sql = "select ROW_NUMBER() over (order by SheetId) Id,* from ";
string sqlPer = "select distinct PerId SheetId, PerchaseNo SheetNo, 1 as ShType, IsChecked, CreateTime,UnitId, UnitName, YHAmount, DealPerson, Creator, CheckPerson, CheckTime,StoreId,StoreName from ViewPerGoodsQuery ";
string sqlSale = " select distinct SaleId SheetId, SaleOutNo SheetNo, 2 as ShType, IsChecked, CreateTime,UnitId, UnitName, YHAmount, DealPerson, Creator, CheckPerson, CheckTime,StoreId,StoreName from ViewSaleGoodsQuery";
string sqlStock = "select distinct StockId SheetId, StockNo SheetNo, 3 as ShType, IsChecked, CreateTime,null as UnitId, null as UnitName, null as YHAmount, DealPerson, Creator, CheckPerson, CheckTime,StoreId,StoreName from ViewStockGoodsQuery";
//条件sql的处理
string strWhereGoods = "";
if (paraModel != null)
{
switch (paraModel.ShType)
{
case 1:
sql += $"({sqlPer}) ";
strWhereGoods = "select PerId from PerchaseGoodsInfos";
break;
case 2:
sql += $"({sqlSale}) ";
strWhereGoods = "select SaleId from SaleGoodsInfos";
break;
case 3:
sql += $"({sqlStock}) ";
strWhereGoods = "select StockId from StStockGoodsInfos";
break;
}
sql += "as temp where 1=1";
if (paraModel.UnitId > 0)
{
sql += " and UnitId=@unitId";
}
else if (!string.IsNullOrEmpty(paraModel.UnitName))
{
sql += " and UnitName like @unitName";
}
if (paraModel.StoreId > 0)
{
sql += " and StoreId=@storeId";
}
else if (!string.IsNullOrEmpty(paraModel.StoreName))
{
sql += " and StoreName like @storeName";
}
if (!string.IsNullOrEmpty(paraModel.CheckPerson))
{
sql += " and CheckPerson like @checkPerson";
}
if (!string.IsNullOrEmpty(paraModel.Creator))
{
sql += " and Creator like @creator";
}
if (!string.IsNullOrEmpty(paraModel.DealPerson))
{
sql += " and DealPerson like @dealPerson";
}
if (!string.IsNullOrEmpty(paraModel.GoodsName))
{
sql += " and SheetId in (" + strWhereGoods + " where GoodsId in (select GoodsId from GoodsInfos where GoodsName like @goodsName))";
}
if (paraModel.IsChecked >= 0 && paraModel.IsChecked <= 3)
{
sql += " and IsChecked = @isChecked";
}
if (!string.IsNullOrEmpty(paraModel.SheetNo))
{
sql += " and SheetNo like @sheetNo";
}
}
//参数的定义
SqlParameter[] paras =
{
new SqlParameter("@goodsName", $"%{paraModel.GoodsName}%"),
new SqlParameter("@storeId", paraModel.StoreId),
new SqlParameter("@storeName", $"%{paraModel.StoreName}%"),
new SqlParameter("@unitId", paraModel.UnitId),
new SqlParameter("@unitName", $"%{paraModel.UnitName}%"),
new SqlParameter("@checkPerson", $"%{paraModel.CheckPerson}%"),
new SqlParameter("@creator", $"%{paraModel.Creator}%"),
new SqlParameter("@dealPerson", $"%{paraModel.DealPerson}%"),
new SqlParameter("@isChecked", paraModel.IsChecked),
new SqlParameter("@sheetNo", $"%{paraModel.SheetNo}%")
};
//sql +条件拼接
string sqlCount = "select count(1) from (" + sql + ") as temp2"; //返回总记录数
//最终要执行目标sql
string sql2 = sqlCount + ";select * from (" + sql + ") as temp3 where Id between " + startIndex + " and " + (startIndex + pageSize - 1);
DataSet ds = SqlHelper.GetDataSet(sql2, 1, paras);
return(ds);
}
