/// <summary>
/// 对象编辑
/// </summary>
/// <param name="tOaPartAcceptedlist_UpdateSet">UpdateSet用户对象</param>
/// <param name="tOaPartAcceptedlist_UpdateWhere">UpdateWhere用户对象</param>
/// <returns>是否成功</returns>
public bool Edit(TOaPartAcceptedlistVo tOaPartAcceptedlist_UpdateSet, TOaPartAcceptedlistVo tOaPartAcceptedlist_UpdateWhere)
{
string strSQL = SqlHelper.BuildUpdateExpress(tOaPartAcceptedlist_UpdateSet, TOaPartAcceptedlistVo.T_OA_PART_ACCEPTEDLIST_TABLE);
strSQL += this.BuildWhereStatement(tOaPartAcceptedlist_UpdateWhere);
return(SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL) > 0 ? true : false);
}
/// <summary>
/// 对象编辑
/// </summary>
/// <param name="tOaPartAcceptedlist">用户对象</param>
/// <returns>是否成功</returns>
public bool Edit(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
string strSQL = SqlHelper.BuildUpdateExpress(tOaPartAcceptedlist, TOaPartAcceptedlistVo.T_OA_PART_ACCEPTEDLIST_TABLE);
strSQL += string.Format(" where ID='{0}' ", tOaPartAcceptedlist.ID);
return(SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL) > 0 ? true : false);
}
/// <summary>
/// 获取对象DataTable
/// </summary>
/// <param name="tOaPartAcceptedlist">对象</param>
/// <param name="iIndex">起始页码</param>
/// <param name="iCount">每页数目</param>
/// <returns>返回结果</returns>
public DataTable SelectByTable(TOaPartAcceptedlistVo tOaPartAcceptedlist, int iIndex, int iCount)
{
string strSQL = " select * from T_OA_PART_ACCEPTEDLIST {0} ";
strSQL = String.Format(strSQL, BuildWhereStatement(tOaPartAcceptedlist));
return(SqlHelper.ExecuteDataTable(BuildPagerExpress(strSQL, iIndex, iCount)));
}
/// <summary>
/// 对象删除
/// </summary>
/// <param name="Id">ID</param>
/// <returns>是否成功</returns>
public bool Delete(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
string strSQL = "delete from T_OA_PART_ACCEPTEDLIST ";
strSQL += this.BuildWhereStatement(tOaPartAcceptedlist);
return(SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL) > 0 ? true : false);
}
/// <summary>
/// 根据对象构造条件语句
/// </summary>
/// <param name="tOaPartAcceptedlist"></param>
/// <returns></returns>
public string BuildWhereStatement(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
StringBuilder strWhereStatement = new StringBuilder(" Where 1=1 ");
if (null != tOaPartAcceptedlist)
{
//编号
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.ID.ToString().Trim()))
{
strWhereStatement.Append(string.Format(" AND ID = '{0}'", tOaPartAcceptedlist.ID.ToString()));
}
//验收清单ID
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.REQUST_LST_ID.ToString().Trim()))
{
strWhereStatement.Append(string.Format(" AND REQUST_LST_ID = '{0}'", tOaPartAcceptedlist.REQUST_LST_ID.ToString()));
}
//采购申请清单ID
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.ACCEPTED_ID.ToString().Trim()))
{
strWhereStatement.Append(string.Format(" AND ACCEPTED_ID = '{0}'", tOaPartAcceptedlist.ACCEPTED_ID.ToString()));
}
//备注1
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.REMARK1.ToString().Trim()))
{
strWhereStatement.Append(string.Format(" AND REMARK1 = '{0}'", tOaPartAcceptedlist.REMARK1.ToString()));
}
//备注2
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.REMARK2.ToString().Trim()))
{
strWhereStatement.Append(string.Format(" AND REMARK2 = '{0}'", tOaPartAcceptedlist.REMARK2.ToString()));
}
//备注3
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.REMARK3.ToString().Trim()))
{
strWhereStatement.Append(string.Format(" AND REMARK3 = '{0}'", tOaPartAcceptedlist.REMARK3.ToString()));
}
//备注4
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.REMARK4.ToString().Trim()))
{
strWhereStatement.Append(string.Format(" AND REMARK4 = '{0}'", tOaPartAcceptedlist.REMARK4.ToString()));
}
//备注5
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.REMARK5.ToString().Trim()))
{
strWhereStatement.Append(string.Format(" AND REMARK5 = '{0}'", tOaPartAcceptedlist.REMARK5.ToString()));
}
}
return(strWhereStatement.ToString());
}
/// <summary>
/// 获取采购计划清单与验收清单
/// </summary>
/// <param name="tOaPartAcceptedlist"></param>
/// <param name="iIndex"></param>
/// <param name="iCount"></param>
/// <returns></returns>
public DataTable SelectUnionByTable(TOaPartAcceptedlistVo tOaPartAcceptedlist, TOaPartInfoVo tOaPartInfor, int iIndex, int iCount)
{
string strSQL = @"SELECT A.ID,A.REQUST_LST_ID,A.ACCEPTED_ID,B.REQUST_ID,B.STATUS,C.PART_ID,C.NEED_QUANTITY,C.USERDO,C.ENTERPRISE_NAME,
C.RANGE,C.STANDARD,C.PRICE,C.AMOUNT,CONVERT(DATETIME, CONVERT(VARCHAR(100), C.RECIVEPART_DATE,23),111) AS RECIVEPART_DATE,CONVERT(DATETIME, CONVERT(VARCHAR(100), C.CHECK_DATE,23),111) AS CHECK_DATE,C.CHECK_RESULT,C.CHECK_USERID,C.REMARK1,D.PART_NAME,
D.PART_CODE,D.PART_TYPE,D.UNIT,D.MODELS,D.INVENTORY,D.MEDIUM,D.PURE,D.ALARM,D.USEING,D.REQUEST,D.NARURE,
E.REAL_NAME,E.USER_NAME,F.DICT_TEXT AS PARTTYPE,G.DICT_TEXT AS CHECKRESULT
FROM dbo.T_OA_PART_ACCEPTEDLIST A
LEFT JOIN dbo.T_OA_PART_BUY_REQUST_LST B ON A.REQUST_LST_ID=B.ID
LEFT JOIN dbo.T_OA_PART_ACCEPTED C ON A.ACCEPTED_ID=C.ID
LEFT JOIN dbo.T_OA_PART_INFO D ON D.ID=C.PART_ID
LEFT JOIN dbo.T_SYS_USER E ON E.ID=C.CHECK_USERID
LEFT JOIN dbo.T_SYS_DICT F ON F.DICT_CODE=D.PART_TYPE AND F.DICT_TYPE='PART_TYPE'
LEFT JOIN dbo.T_SYS_DICT G ON G.DICT_CODE=C.CHECK_RESULT AND G.DICT_TYPE='CheckResult'
WHERE 1=1 AND D.IS_DEL='0'";
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.REQUST_LST_ID))
{
strSQL += String.Format(" AND A.REQUST_LST_ID='{0}'", tOaPartAcceptedlist.REQUST_LST_ID);
}
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.ACCEPTED_ID))
{
strSQL += String.Format(" AND A.ACCEPTED_ID='{0}'", tOaPartAcceptedlist.ACCEPTED_ID);
}
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.ID))
{
strSQL += String.Format(" AND A.ID IN ('{0}')", tOaPartAcceptedlist.ID);
}
if (!String.IsNullOrEmpty(tOaPartInfor.PART_CODE))
{
strSQL += String.Format(" AND D.PART_CODE LIKE '%{0}%'", tOaPartInfor.PART_CODE);
}
if (!String.IsNullOrEmpty(tOaPartInfor.PART_NAME))
{
strSQL += String.Format(" AND D.PART_NAME LIKE '%{0}%'", tOaPartInfor.PART_NAME);
}
if (!String.IsNullOrEmpty(tOaPartInfor.PART_TYPE))
{
strSQL += String.Format(" AND D.PART_TYPE in ({0})", tOaPartInfor.PART_TYPE);
}
if (!String.IsNullOrEmpty(tOaPartAcceptedlist.REMARK4) && !String.IsNullOrEmpty(tOaPartAcceptedlist.REMARK5))
{
strSQL += " AND CONVERT(DATETIME, CONVERT(VARCHAR(100), C.CHECK_DATE,23),111) ";
strSQL += String.Format(" BETWEEN CONVERT(DATETIME, CONVERT(varchar(100), '{0}',23),111) AND CONVERT(DATETIME, CONVERT(varchar(100), '{1}',23),111)", tOaPartAcceptedlist.REMARK4, tOaPartAcceptedlist.REMARK5);
}
return(SqlHelper.ExecuteDataTable(BuildPagerExpress(strSQL, iIndex, iCount)));
}
/// <summary>
/// 对象编辑
/// </summary>
/// <param name="tOaPartAcceptedlist">用户对象</param>
/// <returns>是否成功</returns>
public bool Edit(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
return(access.Edit(tOaPartAcceptedlist));
}
/// <summary>
/// 对象添加
/// </summary>
/// <param name="sysRole">对象</param>
/// <returns>是否成功</returns>
public bool Create(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
return(access.Create(tOaPartAcceptedlist));
}
/// <summary>
/// 对象添加
/// </summary>
/// <param name="tOaPartAcceptedlist">对象</param>
/// <returns>是否成功</returns>
public bool Create(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
string strSQL = SqlHelper.BuildInsertExpress(tOaPartAcceptedlist, TOaPartAcceptedlistVo.T_OA_PART_ACCEPTEDLIST_TABLE);
return(SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL) > 0 ? true : false);
}
/// <summary>
/// 获取对象DataTable
/// </summary>
/// <param name="tOaPartAcceptedlist">对象</param>
/// <param name="iIndex">起始页码</param>
/// <param name="iCount">每页数目</param>
/// <returns>返回结果</returns>
public DataTable SelectByTable(TOaPartAcceptedlistVo tOaPartAcceptedlist, int iIndex, int iCount)
{
return(access.SelectByTable(tOaPartAcceptedlist, iIndex, iCount));
}
/// <summary>
/// 对象明细
/// </summary>
/// <param name="tOaPartAcceptedlist">对象条件</param>
/// <returns>对象</returns>
public TOaPartAcceptedlistVo Details(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
return(access.Details(tOaPartAcceptedlist));
}
public TOaPartAcceptedlistLogic(TOaPartAcceptedlistVo _tOaPartAcceptedlist)
{
tOaPartAcceptedlist = _tOaPartAcceptedlist;
access = new TOaPartAcceptedlistAccess();
}
/// <summary>
/// 返回符合条件的采购计划与验收清单
/// </summary>
/// <param name="tOaPartAcceptedlist"></param>
/// <param name="iIndex"></param>
/// <param name="iCount"></param>
/// <returns></returns>
public DataTable SelectUnionByTable(TOaPartAcceptedlistVo tOaPartAcceptedlist, TOaPartInfoVo tOaPartInfor, int iIndex, int iCount)
{
return(access.SelectUnionByTable(tOaPartAcceptedlist, tOaPartInfor, iIndex, iCount));
}
/// <summary>
/// 获得查询结果总行数，用于分页
/// </summary>
/// <param name="tOaPartAcceptedlist">对象</param>
/// <returns>返回行数</returns>
public int GetSelectResultCount(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
string strSQL = "select Count(*) from T_OA_PART_ACCEPTEDLIST " + this.BuildWhereStatement(tOaPartAcceptedlist);
return(Convert.ToInt32(SqlHelper.ExecuteScalar(strSQL)));
}
/// <summary>
/// 对象编辑
/// </summary>
/// <param name="tOaPartAcceptedlist_UpdateSet">UpdateSet用户对象</param>
/// <param name="tOaPartAcceptedlist_UpdateWhere">UpdateWhere用户对象</param>
/// <returns>是否成功</returns>
public bool Edit(TOaPartAcceptedlistVo tOaPartAcceptedlist_UpdateSet, TOaPartAcceptedlistVo tOaPartAcceptedlist_UpdateWhere)
{
return(access.Edit(tOaPartAcceptedlist_UpdateSet, tOaPartAcceptedlist_UpdateWhere));
}
/// <summary>
/// 对象删除
/// </summary>
/// <param name="Id">ID</param>
/// <returns>是否成功</returns>
public bool Delete(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
return(access.Delete(tOaPartAcceptedlist));
}
/// <summary>
/// 对象明细
/// </summary>
/// <param name="tOaPartAcceptedlist">对象条件</param>
/// <returns>对象</returns>
public TOaPartAcceptedlistVo Details(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
string strSQL = String.Format("select * from T_OA_PART_ACCEPTEDLIST " + this.BuildWhereStatement(tOaPartAcceptedlist));
return(SqlHelper.ExecuteObject(new TOaPartAcceptedlistVo(), strSQL));
}
/// <summary>
/// 返回符合条件的采购计划与验收清单总记录数
/// </summary>
/// <param name="tOaPartAcceptedlist"></param>
/// <param name="iIndex"></param>
/// <param name="iCount"></param>
/// <returns></returns>
public int GetSelectUnionByTableCount(TOaPartAcceptedlistVo tOaPartAcceptedlist, TOaPartInfoVo tOaPartInfor)
{
return(access.GetSelectUnionByTableCount(tOaPartAcceptedlist, tOaPartInfor));
}
/// <summary>
/// 获取对象List
/// </summary>
/// <param name="tOaPartAcceptedlist">对象</param>
/// <param name="iIndex">起始页码</param>
/// <param name="iCount">每页数目</param>
/// <returns>返回结果</returns>
public List <TOaPartAcceptedlistVo> SelectByObject(TOaPartAcceptedlistVo tOaPartAcceptedlist, int iIndex, int iCount)
{
string strSQL = String.Format("select * from T_OA_PART_ACCEPTEDLIST " + this.BuildWhereStatement(tOaPartAcceptedlist));
return(SqlHelper.ExecuteObjectList(tOaPartAcceptedlist, BuildPagerExpress(strSQL, iIndex, iCount)));
}
/// <summary>
/// 获得查询结果总行数，用于分页
/// </summary>
/// <param name="tOaPartAcceptedlist">对象</param>
/// <returns>返回行数</returns>
public int GetSelectResultCount(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
return(access.GetSelectResultCount(tOaPartAcceptedlist));
}
/// <summary>
/// 根据对象获取全部数据，用Table承载
/// 数据量较小时使用【不推荐】
/// </summary>
/// <param name="tOaPartAcceptedlist"></param>
/// <returns></returns>
public DataTable SelectByTable(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
string strSQL = "select * from T_OA_PART_ACCEPTEDLIST " + this.BuildWhereStatement(tOaPartAcceptedlist);
return(SqlHelper.ExecuteDataTable(strSQL));
}
/// <summary>
/// 获取对象List
/// </summary>
/// <param name="tOaPartAcceptedlist">对象</param>
/// <param name="iIndex">起始页码</param>
/// <param name="iCount">每页数目</param>
/// <returns>返回结果</returns>
public List <TOaPartAcceptedlistVo> SelectByObject(TOaPartAcceptedlistVo tOaPartAcceptedlist, int iIndex, int iCount)
{
return(access.SelectByObject(tOaPartAcceptedlist, iIndex, iCount));
}
/// <summary>
/// 根据对象特征获取单一对象
/// </summary>
/// <param name="tOaPartAcceptedlist">对象</param>
/// <returns></returns>
public TOaPartAcceptedlistVo SelectByObject(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
return(access.SelectByObject(tOaPartAcceptedlist));
}
/// <summary>
/// 根据对象获取全部数据，用Table承载
/// 数据量较小时使用【不推荐】
/// </summary>
/// <param name="tOaPartAcceptedlist"></param>
/// <returns></returns>
public DataTable SelectByTable(TOaPartAcceptedlistVo tOaPartAcceptedlist)
{
return(access.SelectByTable(tOaPartAcceptedlist));
}
protected void btnExport_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string strType = this.hidGrid.Value.ToString();
string strPartBuyLstId = this.hidExportDate.Value.ToString();
if (!String.IsNullOrEmpty(strType))
{
//导出采购计划明细
if (strType == "1")
{
TOaPartBuyRequstLstVo objItems = new TOaPartBuyRequstLstVo();
TOaPartInfoVo objItemPart = new TOaPartInfoVo();
TOaPartBuyRequstVo objItemBy = new TOaPartBuyRequstVo();
objItemBy.STATUS = "9";
if (!String.IsNullOrEmpty(strPartBuyLstId))
{
objItems.ID = strPartBuyLstId.Replace(",", "','");
}
objItems.STATUS = "0";
dt = new TOaPartBuyRequstLstLogic().SelectUnionPartByTable(objItems, objItemPart, objItemBy, 0, 0);
DataView dv = new DataView();
dv = dt.DefaultView;
if (dt.Rows.Count > 0)
{
dv.Sort = "DELIVERY_DATE";
}
FileStream file = new FileStream(HttpContext.Current.Server.MapPath("template/PartPlanDetailSheet.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheet("Sheet1");
sheet.GetRow(2).GetCell(0).SetCellValue("生成日期：" + DateTime.Now.ToString("yyyy-MM-dd"));
DataTable dtTemp = new DataTable();
dtTemp = dv.ToTable().Copy();;
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.GetRow(i + 4).GetCell(0).SetCellValue(dtTemp.Rows[i]["PART_NAME"].ToString());
sheet.GetRow(i + 4).GetCell(1).SetCellValue(dtTemp.Rows[i]["PART_CODE"].ToString());
sheet.GetRow(i + 4).GetCell(2).SetCellValue(dtTemp.Rows[i]["APPLY_TITLE"].ToString());
sheet.GetRow(i + 4).GetCell(3).SetCellValue(dtTemp.Rows[i]["DEPT_NAME"].ToString());
sheet.GetRow(i + 4).GetCell(4).SetCellValue(dtTemp.Rows[i]["REAL_NAME"].ToString());
sheet.GetRow(i + 4).GetCell(5).SetCellValue(dtTemp.Rows[i]["MODELS"].ToString());
sheet.GetRow(i + 4).GetCell(6).SetCellValue(dtTemp.Rows[i]["NEED_QUANTITY"].ToString());
sheet.GetRow(i + 4).GetCell(7).SetCellValue(dtTemp.Rows[i]["DELIVERY_DATE"].ToString());
sheet.GetRow(i + 4).GetCell(8).SetCellValue("￥" + dtTemp.Rows[i]["BUDGET_MONEY"].ToString());
sheet.GetRow(i + 4).GetCell(9).SetCellValue(dtTemp.Rows[i]["REQUEST"].ToString());
sheet.GetRow(i + 4).GetCell(10).SetCellValue(dtTemp.Rows[i]["USEING"].ToString());
}
using (MemoryStream stream = new MemoryStream())
{
hssfworkbook.Write(stream);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("物料采购计划明细表-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(stream.GetBuffer());
curContext.Response.End();
}
}
//导出验收明细
if (strType == "2")
{
TOaPartAcceptedlistVo objItems = new TOaPartAcceptedlistVo();
if (!String.IsNullOrEmpty(strPartBuyLstId))
{
objItems.ID = strPartBuyLstId.Replace(",", "','");
}
TOaPartInfoVo objItemPart = new TOaPartInfoVo();
dt = new TOaPartAcceptedlistLogic().SelectUnionByTable(objItems, objItemPart, 0, 0);
DataView dv = new DataView();
dv = dt.DefaultView;
if (dt.Rows.Count > 0)
{
dv.Sort = "CHECK_DATE DESC";
}
FileStream file = new FileStream(HttpContext.Current.Server.MapPath("template/PartAcceptDetailSheet.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheet("Sheet1");
DataTable dtTemp = new DataTable();
dtTemp = dv.ToTable().Copy();;
for (int i = 0; i < dt.Rows.Count; i++)
{
sheet.GetRow(i + 3).GetCell(0).SetCellValue(dtTemp.Rows[i]["PARTTYPE"].ToString());
sheet.GetRow(i + 3).GetCell(1).SetCellValue(dtTemp.Rows[i]["PART_NAME"].ToString() + "(" + dtTemp.Rows[i]["PART_CODE"].ToString() + ")");
sheet.GetRow(i + 3).GetCell(2).SetCellValue(dtTemp.Rows[i]["ENTERPRISE_NAME"].ToString());
sheet.GetRow(i + 3).GetCell(3).SetCellValue(dtTemp.Rows[i]["NEED_QUANTITY"].ToString());
sheet.GetRow(i + 3).GetCell(4).SetCellValue("￥" + dtTemp.Rows[i]["PRICE"].ToString());
sheet.GetRow(i + 3).GetCell(5).SetCellValue("￥" + dtTemp.Rows[i]["AMOUNT"].ToString());
sheet.GetRow(i + 3).GetCell(6).SetCellValue(dtTemp.Rows[i]["CHECK_DATE"].ToString());
sheet.GetRow(i + 3).GetCell(7).SetCellValue(dtTemp.Rows[i]["RECIVEPART_DATE"].ToString());
sheet.GetRow(i + 3).GetCell(8).SetCellValue(dtTemp.Rows[i]["CHECKRESULT"].ToString());
sheet.GetRow(i + 3).GetCell(9).SetCellValue(dtTemp.Rows[i]["REAL_NAME"].ToString());
sheet.GetRow(i + 3).GetCell(10).SetCellValue(dtTemp.Rows[i]["REMARK1"].ToString());
}
using (MemoryStream stream = new MemoryStream())
{
hssfworkbook.Write(stream);
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode("物料验收明细表-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls", Encoding.UTF8));
curContext.Response.BinaryWrite(stream.GetBuffer());
curContext.Response.End();
}
}
}
}