protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
PurchaseApplyModel PurchaseApplyM = new PurchaseApplyModel();
PurchaseApplyM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
PurchaseApplyM.ApplyNo = txtapplyno.Value;
PurchaseApplyM.Title = txttitle.Value;
PurchaseApplyM.ApplyUserID = hidApplyID.Value;
PurchaseApplyM.ApplyDeptID = hidDeptID.Value;
PurchaseApplyM.TypeID = ddlTypeID.SelectedValue;
PurchaseApplyM.FromType = ddlfromtype.Value;
PurchaseApplyM.StartApplyDate = txtstartapplydate.Value;
PurchaseApplyM.EndApplyDate = txtendapplydate.Value;
PurchaseApplyM.BillStatus = ddlBillStatus.Value;
PurchaseApplyM.FlowStatus = ddlFlowStatus.Value;
PurchaseApplyM.EFIndex = GetBillExAttrControl1.GetExtIndexValue;
PurchaseApplyM.EFDesc = GetBillExAttrControl1.GetExtTxtValue;
string OrderBy = hidOrderBy.Value;
DataTable dt = PurchaseApplyBus.SelectPurchaseApply(PurchaseApplyM, OrderBy);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "申请单编号", "申请单主题", "采购类别", "源单类型", "申请人", "申请部门", "申请时间", "单据状态", "审批状态" },
new string[] { "ApplyNo", "Title", "TypeName", "FromTypeName", "ApplyUserName", "ApplyDeptName", "ApplyDate", "BillStatusName", "FlowStatusName" },
"采购申请列表");
}
public static SqlCommand UpdatePrimary(PurchaseApplyModel PurchaseApplyM)
{
SqlCommand comm = new SqlCommand();
#region SQL文
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE officedba.PurchaseApply ");
sql.AppendLine(" SET Title =@Title ");
sql.AppendLine(" ,FromType =@FromType ");
sql.AppendLine(" ,TypeID =@TypeID ");
sql.AppendLine(" ,ApplyUserID =@ApplyUserID ");
sql.AppendLine(" ,ApplyDeptID =@ApplyDeptID ");
sql.AppendLine(" ,Address =@Address ");
sql.AppendLine(" ,CountTotal =@CountTotal ");
sql.AppendLine(" ,ApplyDate =@ApplyDate ");
sql.AppendLine(" ,Remark =@Remark ");
sql.AppendLine(" ,BillStatus =@BillStatus ");
sql.AppendLine(" ,ModifiedUserID =@ModifiedUserID ");
sql.AppendLine(" ,ModifiedDate =getDate() ");
sql.AppendLine(" WHERE ID=@ID ");
#endregion
#region 传参
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", PurchaseApplyM.Title));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromType", PurchaseApplyM.FromType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", PurchaseApplyM.TypeID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyUserID", PurchaseApplyM.ApplyUserID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDeptID", PurchaseApplyM.ApplyDeptID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Address", PurchaseApplyM.Address.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CountTotal", PurchaseApplyM.CountTotal.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDate", PurchaseApplyM.ApplyDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", PurchaseApplyM.Remark.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", PurchaseApplyM.BillStatus.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", PurchaseApplyM.ModifiedUserID.ToString()));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", PurchaseApplyM.ModifiedDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", PurchaseApplyM.id.ToString()));
#endregion
comm.CommandText = sql.ToString();
return comm;
}
public static SqlCommand InsertPrimary(PurchaseApplyModel PurchaseApplyM)
{
SqlCommand comm = new SqlCommand();
#region SQL文
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO officedba.PurchaseApply");
sql.AppendLine(" (CompanyCD ");
sql.AppendLine(" ,ApplyNo ");
sql.AppendLine(" ,Title ");
sql.AppendLine(" ,FromType ");
sql.AppendLine(" ,TypeID ");
sql.AppendLine(" ,ApplyUserID ");
sql.AppendLine(" ,ApplyDeptID ");
sql.AppendLine(" ,Address ");
sql.AppendLine(" ,CountTotal ");
//sql.AppendLine(" ,Confirmor ");
//sql.AppendLine(" ,ConfirmDate ");
//sql.AppendLine(" ,Closer ");
//sql.AppendLine(" ,CloseDate ");
sql.AppendLine(" ,ApplyDate ");
sql.AppendLine(" ,Remark ");
sql.AppendLine(" ,BillStatus ");
sql.AppendLine(" ,Creator ");
sql.AppendLine(" ,CreateDate ");
sql.AppendLine(" ,ModifiedUserID ");
sql.AppendLine(" ,ModifiedDate) ");
sql.AppendLine(" VALUES ");
sql.AppendLine(" (@CompanyCD ");
sql.AppendLine(" ,@ApplyNo ");
sql.AppendLine(" ,@Title ");
sql.AppendLine(" ,@FromType ");
sql.AppendLine(" ,@TypeID ");
sql.AppendLine(" ,@ApplyUserID ");
sql.AppendLine(" ,@ApplyDeptID ");
sql.AppendLine(" ,@Address ");
sql.AppendLine(" ,@CountTotal ");
//sql.AppendLine(" ,@Confirmor ");
//sql.AppendLine(" ,@ConfirmDate ");
//sql.AppendLine(" ,@Closer ");
//sql.AppendLine(" ,@CloseDate ");
sql.AppendLine(" ,@ApplyDate ");
sql.AppendLine(" ,@Remark ");
sql.AppendLine(" ,@BillStatus ");
sql.AppendLine(" ,@Creator ");
sql.AppendLine(" ,getdate() ");
sql.AppendLine(" ,@ModifiedUserID ");
sql.AppendLine(" ,getdate() ) ");
sql.AppendLine("set @IndexID = @@IDENTITY");
#endregion
#region 传参
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", PurchaseApplyM.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyNo", PurchaseApplyM.ApplyNo));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", PurchaseApplyM.Title));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromType", PurchaseApplyM.FromType));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", PurchaseApplyM.TypeID.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyUserID", PurchaseApplyM.ApplyUserID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDeptID", PurchaseApplyM.ApplyDeptID));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Address", PurchaseApplyM.Address));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CountTotal", PurchaseApplyM.CountTotal));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDate", PurchaseApplyM.ApplyDate));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Remark", PurchaseApplyM.Remark.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", PurchaseApplyM.BillStatus.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Creator", PurchaseApplyM.Creator.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedUserID", PurchaseApplyM.ModifiedUserID));
//comm.Parameters.Add(SqlHelper.GetParameterFromString("@ModifiedDate", PurchaseApplyM.ModifiedDate));
SqlParameter IndexID = new SqlParameter("@IndexID", SqlDbType.Int);
IndexID.Direction = ParameterDirection.Output;
comm.Parameters.Add(IndexID);
#endregion
comm.CommandText = sql.ToString();
return comm;
}
public static DataTable SelectPrimary(PurchaseApplyModel PurchaseApplyM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
SqlCommand comm = new SqlCommand();
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT A.ID");
sql.AppendLine(" , isnull(A.CompanyCD ,'') AS CompanyCD ");
sql.AppendLine(" , isnull(A.ApplyNo ,'') AS ApplyNo ");
sql.AppendLine(" , isnull(A.Title ,'') AS Title ");
sql.AppendLine(" , isnull(A.FromType ,'') AS FromType ");
sql.AppendLine(" , case A.FromType when '0' then '无来源' when '1' then '销售订单' ");
sql.AppendLine(" when '2' then '物料需求计划' end as FromTypeName ");
sql.AppendLine(" , isnull(A.TypeID ,0) AS TypeID ");
sql.AppendLine(" , isnull(B.TypeName ,'') AS TypeName ");
sql.AppendLine(" , isnull(A.ApplyUserID ,0) AS ApplyUserID ");
sql.AppendLine(" , isnull(C.EmployeeName,'') AS ApplyUserName ");
sql.AppendLine(" , isnull(A.ApplyDeptID ,0) AS ApplyDeptID ");
sql.AppendLine(" , isnull(D.DeptName ,'') AS ApplyDeptName ");
sql.AppendLine(" , CONVERT(varchar(100), A.ApplyDate, 23) AS ApplyDate ");
sql.AppendLine(" , isnull(A.BillStatus ,'') AS BillStatus ");
sql.AppendLine(" , case A.BillStatus when '1' then '制单' when '2' then '执行' when '3' then '变更'");
sql.AppendLine(" when '4' then '手工结单' when '5' then '自动结单' end AS BillStatusName ");
sql.AppendLine(" ,isnull(E.BillID,0) as BillID ");
sql.AppendLine(" ,isnull(E.FlowStatus,'') as FlowStatus ");
sql.AppendLine(" ,case E.FlowStatus when '1' then '待审批' when '2' then '审批中' when '3' then '审批通过'");
sql.AppendLine(" when '4' then '审批不通过' when '5' then '撤销审批' else '' end as FlowStatusName,isnull(A.ModifiedDate,'') AS ModifiedDate");
sql.AppendLine(" FROM officedba.PurchaseApply AS A ");
sql.AppendLine(" LEFT JOIN officedba.CodePublicType AS B ON A.TypeID = B.ID ");
sql.AppendLine(" INNER JOIN officedba.EmployeeInfo AS C ON A.ApplyUserID = C.ID ");
sql.AppendLine(" INNER JOIN officedba.DeptInfo AS D ON A.ApplyDeptID = D.ID ");
sql.AppendLine(" LEFT JOIN officedba.FlowInstance AS E ON A.id = E.BillID AND E.BillTypeFlag = 6 AND E.BillTypeCode = 1 ");
sql.AppendLine(" AND E.ID=(select max(ID) from officedba.FlowInstance AS F where A.id = F.BillID AND F.BillTypeFlag = 6 AND F.BillTypeCode = 1) ");
sql.AppendLine("WHERE A.CompanyCD = @CompanyCD");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD));
if (PurchaseApplyM.ApplyNo != "" && PurchaseApplyM.ApplyNo != null)
{
sql.AppendLine(" AND A.ApplyNo like '%'+ @ApplyNo +'%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyNo",PurchaseApplyM.ApplyNo));
}
if (PurchaseApplyM.Title != "" && PurchaseApplyM.Title != null)
{
sql.AppendLine(" AND A.Title like '%'+ @Title +'%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title",PurchaseApplyM.Title));
}
if (PurchaseApplyM.ApplyUserID != "" && PurchaseApplyM.ApplyUserID != null)
{
sql.AppendLine(" AND A.ApplyUserID = @ApplyUserID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyUserID",PurchaseApplyM.ApplyUserID));
}
if (PurchaseApplyM.ApplyDeptID != "" && PurchaseApplyM.ApplyDeptID != null)
{
sql.AppendLine(" AND A.ApplyDeptID = @ApplyDeptID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ApplyDeptID", PurchaseApplyM.ApplyDeptID));
}
if (PurchaseApplyM.TypeID != "" && PurchaseApplyM.TypeID != null)
{
sql.AppendLine(" AND A.TypeID = @TypeID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID",PurchaseApplyM.TypeID));
}
if (PurchaseApplyM.FromType != "" && PurchaseApplyM.FromType != null)
{
sql.AppendLine(" AND A.FromType = @FromType ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromType",PurchaseApplyM.FromType));
}
if (PurchaseApplyM.StartApplyDate != "")
{
sql.AppendLine(" AND A.ApplyDate >= @StartApplyDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartApplyDate",PurchaseApplyM.StartApplyDate));
}
if (PurchaseApplyM.EndApplyDate != "")
{
sql.AppendLine(" AND A.ApplyDate <= @EndApplyDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndApplyDate", PurchaseApplyM.EndApplyDate));
}
if (PurchaseApplyM.BillStatus != "0")
{
sql.AppendLine(" AND A.BillStatus = @BillStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", PurchaseApplyM.BillStatus));
}
if (PurchaseApplyM.FlowStatus != "0" )
{
if (PurchaseApplyM.FlowStatus == "")
{
sql.AppendLine(" AND E.FlowStatus is null");
}
else
{
sql.AppendLine(" AND E.FlowStatus = @FlowStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FlowStatus", PurchaseApplyM.FlowStatus));
}
}
if (!string.IsNullOrEmpty(PurchaseApplyM.EFIndex) && !string.IsNullOrEmpty(PurchaseApplyM.EFDesc))
{
if (Convert .ToInt32 ( PurchaseApplyM.EFIndex) != -1)
{
sql.AppendLine(" and a.ExtField" + PurchaseApplyM.EFIndex + " LIKE @EFDesc");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + PurchaseApplyM.EFDesc + "%"));
}
}
comm.CommandText = sql.ToString();
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount);
//return SqlHelper.ExecuteSearch(comm);
}
public static bool InsertPurchaseApply(PurchaseApplyModel PurchaseApplyM, List<PurchaseApplyDetailSourceModel> PurchaseApplyDetailSourceMList
, List<PurchaseApplyDetailModel> PurchaseApplyDetailMList, out int IndexIDentity, Hashtable htExtAttr)
{
ArrayList lstAdd = new ArrayList();
//插入主表
SqlCommand AddPri = PurchaseApplyDBHelper.InsertPrimary(PurchaseApplyM);
lstAdd.Add(AddPri);
string ApplyNo = PurchaseApplyM.ApplyNo;
foreach (PurchaseApplyDetailSourceModel PurchaseApplyDetailSourceM in PurchaseApplyDetailSourceMList)
{
SqlCommand AddDtlS = PurchaseApplyDBHelper.InsertDtlS(PurchaseApplyDetailSourceM, ApplyNo);
lstAdd.Add(AddDtlS);
}
foreach (PurchaseApplyDetailModel PurchaseApplyDetailM in PurchaseApplyDetailMList)
{
SqlCommand AddDtl = PurchaseApplyDBHelper.InsertDtl(PurchaseApplyDetailM, ApplyNo);
lstAdd.Add(AddDtl);
}
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(PurchaseApplyM, htExtAttr, cmd);
if (htExtAttr.Count > 0)
lstAdd.Add(cmd);
#endregion
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//定义返回变量
bool isSucc = false;
/*
* 定义日志内容变量
* 增删改相关的日志，需要输出操作日志，该类型日志插入到数据库
* 其他的 如出现异常时，需要输出系统日志，该类型日志保存到日志文件
*/
//执行插入操作
try
{
isSucc = SqlHelper.ExecuteTransWithArrayList(lstAdd);
}
catch (Exception ex)
{
//输出日志
WriteSystemLog(userInfo, ex);
}
//定义变量
string remark;
//成功时
if (isSucc)
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
IndexIDentity = int.Parse(((SqlCommand)AddPri).Parameters["@IndexID"].Value.ToString());
}
else
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
IndexIDentity = 0;
}
LogInfoModel logModel = InitLogInfo(ApplyNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return isSucc;
}
/// <summary>
/// 修改采购申请
/// </summary>
/// <param name="PurchaseApplyM">主表</param>
/// <param name="str">明细来源</param>
/// <param name="start"></param>
/// <param name="end">从start--end需要更新，end以后要插入</param>
/// <param name="str2">明细信息</param>
/// <param name="ApplyNo"></param>
/// <returns></returns>
public static bool UpdatePurchaseApply(PurchaseApplyModel PurchaseApplyM, List<PurchaseApplyDetailSourceModel> PurchaseApplyDetailSourceMList
, List<PurchaseApplyDetailModel> PurchaseApplyDetailMList, Hashtable htExtAttr)
{
try
{
ArrayList lstUpdate = new ArrayList();
//更新主表
SqlCommand UpdatePri = PurchaseApplyDBHelper.UpdatePrimary(PurchaseApplyM);
lstUpdate.Add(UpdatePri);
string ApplyNo = PurchaseApplyM.ApplyNo;
string FromType = PurchaseApplyM.FromType;
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(PurchaseApplyM, htExtAttr, cmd);
if (htExtAttr.Count > 0)
lstUpdate.Add(cmd);
#endregion
//删除原来明细来源
SqlCommand DelDtlS = PurchaseApplyDBHelper.DeleteDtlS(ApplyNo);
lstUpdate.Add(DelDtlS);
//插入现在的明细来源
foreach (PurchaseApplyDetailSourceModel PurchaseApplyDetailSourceM in PurchaseApplyDetailSourceMList)
{
SqlCommand AddDtlS = PurchaseApplyDBHelper.InsertDtlS(PurchaseApplyDetailSourceM, ApplyNo);
lstUpdate.Add(AddDtlS);
}
//删除原来明细
SqlCommand DelDtl = PurchaseApplyDBHelper.DeleteDtl(ApplyNo);
lstUpdate.Add(DelDtl);
//插入现在的明细
foreach (PurchaseApplyDetailModel PurchaseApplyDetailM in PurchaseApplyDetailMList)
{
SqlCommand AddDtl = PurchaseApplyDBHelper.InsertDtl(PurchaseApplyDetailM, ApplyNo);
lstUpdate.Add(AddDtl);
}
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//定义返回变量
bool isSucc = false;
/*
* 定义日志内容变量
* 增删改相关的日志，需要输出操作日志，该类型日志插入到数据库
* 其他的 如出现异常时，需要输出系统日志，该类型日志保存到日志文件
*/
//执行插入操作
try
{
isSucc = SqlHelper.ExecuteTransWithArrayList(lstUpdate);
}
catch (Exception ex)
{
//输出日志
WriteSystemLog(userInfo, ex);
}
//定义变量
string remark;
//成功时
if (isSucc)
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
else
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
}
LogInfoModel logModel = InitLogInfo(ApplyNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return isSucc;
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable SelectPurchaseApply(PurchaseApplyModel PurchaseApplyM, string OrderBy)
{
try
{
DataTable dt = PurchaseApplyDBHelper.SelectPrimary(PurchaseApplyM, OrderBy);
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable SelectPurchaseApply(PurchaseApplyModel PurchaseApplyM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
DataTable dt = PurchaseApplyDBHelper.SelectPrimary(PurchaseApplyM, pageIndex, pageCount, OrderBy, ref totalCount);
//dt.Columns.Add("IsCite");
//dt.Columns.Add("IsCiteName");
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// string ID = dt.Rows[i]["ID"].ToString();
// bool IsCite = PurchaseApplyBus.IsCitePurApply(ID);
// dt.Rows[i]["IsCite"] = IsCite;
// dt.Rows[i]["IsCiteName"] = IsCite ? '是' : '否';
//}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(PurchaseApplyModel model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.PurchaseApply set ";
foreach (DictionaryEntry de in htExtAttr)// fileht为一个Hashtable实例
{
strSql += de.Key.ToString().Trim() + "=@" + de.Key.ToString().Trim() + ",";
cmd.Parameters.AddWithValue("@" + de.Key.ToString().Trim(), de.Value.ToString().Trim());
}
int iLength = strSql.Length - 1;
strSql = strSql.Substring(0, iLength);
strSql += " where CompanyCD = @CompanyCD AND ApplyNo = @ApplyNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
cmd.Parameters.AddWithValue("@ApplyNo", model.ApplyNo);
cmd.CommandText = strSql;
}
catch (Exception)
{ }
}