protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
PurchasePlanModel PurchasePlanM = new PurchasePlanModel();
PurchasePlanM.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
PurchasePlanM.PlanNo = txtPlanNo.Value;
PurchasePlanM.Title = txtPlanTitle.Value;
PurchasePlanM.PlanUserID = PlanUserID.Value;
PurchasePlanM.PlanMoney = txtTotalMoneyMin.Value;
PurchasePlanM.TotalMoneyMax = txtTotalMoneyMax.Value;
PurchasePlanM.PlanDeptID = txtDeptID.Value;
PurchasePlanM.PlanDate = txtStartPlanDate.Value;
PurchasePlanM.EndPlanDate = txtEndPlanDate.Value;
PurchasePlanM.BillStatus = ddlFlowStatus.Value;
PurchasePlanM.FlowStatus = ddlFlowStatus.Value;
PurchasePlanM.EFIndex = GetBillExAttrControl1.GetExtIndexValue;
PurchasePlanM.EFDesc = GetBillExAttrControl1.GetExtTxtValue;
string OrderBy = hidOrderBy.Value;
//XElement dsXML = ConvertDataTableToXML(PurchasePlanBus.SelectPurchasePlan(PurchasePlanM));
DataTable dt = PurchasePlanBus.SelectPurchasePlan(PurchasePlanM, OrderBy);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "计划单编号", "计划单主题", "计划员", "预控金额", "计划时间", "单据状态", "审批状态" },
new string[] { "PlanNo", "PlanTitle", "PlanUserName", "PlanMoney", "PlanDate", "BillStatusName", "FlowStatusName" },
"采购计划列表");
}
public static DataTable SelectPurchasePlan(PurchasePlanModel PurchasePlanM, string OrderBy)
{
try
{
DataTable dt = PurchasePlanDBHelper.SelectPurchasePlanPrimary(PurchasePlanM, OrderBy);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 更新采购计划
/// </summary>
/// <param name="BigModel"></param>
/// <returns></returns>
public int UpdatePurchasePlan(PurchasePlanModel BigModel)
{
try
{
var ExtList = BigModel.ExtList;
var Item = BigModel.Item;
SqlParameter[] parms = new SqlParameter[]
{
new SqlParameter("@ContantName", Item.ContantName),
new SqlParameter("@ContantWay", Item.ContantWay),
new SqlParameter("@EndDateTime", Item.EndDateTime),
new SqlParameter("@HopePrice", Item.HopePrice),
new SqlParameter("@PName", Item.PName),
new SqlParameter("@PurchaseName", Item.PurchaseName),
new SqlParameter("@PurchaseType", Item.PurchaseType),
new SqlParameter("@Qty", Item.Qty),
new SqlParameter("@RequestContant", Item.RequestContant),
new SqlParameter("@RequestManufactureArea", Item.RequestManufactureArea),
new SqlParameter("@RequestReceiveArea", Item.RequestReceiveArea),
new SqlParameter("@Spec", Item.Spec),
new SqlParameter("@Title", Item.Title),
new SqlParameter("@Unit", Item.Unit),
new SqlParameter("@State", Item.State),
new SqlParameter("@PurchaseDetails", Item.PurchaseDetails),
new SqlParameter("@PPId", Item.PPId),
};
StringBuilder sb = new StringBuilder();
sb.Append(" UPDATE dbo.PurchasePlan ");
sb.Append(" SET Title=@Title,PName=@PName,State=@State,PurchaseDetails=@PurchaseDetails, ");
sb.Append(" Spec=@Spec,Qty=@Qty,Unit=@Unit,HopePrice=@HopePrice,PurchaseName=@PurchaseName, ");
sb.Append(" ContantName=@ContantName,ContantWay=@ContantWay,EndDateTime=@EndDateTime, ");
sb.Append(" PurchaseType=@PurchaseType,RequestManufactureArea=@RequestManufactureArea, ");
sb.Append(" RequestReceiveArea=@RequestReceiveArea,RequestContant=@RequestContant, ");
sb.Append(" LastUpdateDate=GETDATE(),LastUpdateUId=@LastUpdateUid ");
sb.Append(" WHERE PPId=@PPId ");
sb.Append(" DELETE dbo.ExtTable WHERE ContantId=@PPId and ContantTableName='PurchasePlan' ");
for (int i = 0; i < ExtList.Count; i++)
{
sb.Append(" INSERT INTO dbo.ExtTable( ContantId ,ContantTableName ,ExtName ,ExtValue , ");
sb.Append(" CreateDate ,CreateUid ,LastUpdateDate ,LastUpdateUId) ");
sb.AppendFormat(" Value(@PPId,'PurchasePlan','{0}','{1}',GETDATE(),@CreateUid,GETDATE(),@CreateUid) ", ExtList[i].ExtName, ExtList[i].ExtValue);
}
return(SQLHelper.ExcuteSQL(sb.ToString(), parms));
}
catch (Exception)
{
return(-1);
}
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(PurchasePlanModel model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.PurchasePlan 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 PlanNo = @PlanNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
cmd.Parameters.AddWithValue("@PlanNo", model.PlanNo);
cmd.CommandText = strSql;
}
catch (Exception)
{ }
}
public static DataTable SelectPurchasePlan(PurchasePlanModel PurchasePlanM, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
DataTable dt = PurchasePlanDBHelper.SelectPurchasePlanPrimary(PurchasePlanM, pageIndex, pageCount, OrderBy, ref totalCount);
//DataColumn JoinName = new DataColumn();
//dt.Columns.Add("IsCite");
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// string ID = dt.Rows[i]["ID"].ToString();
// bool IsCite = PurchasePlanDBHelper.IsCitePurPlan(ID);
// dt.Rows[i]["IsCite"] = IsCite;
//}
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 更新采购计划
/// </summary>
/// <param name="BigModel"></param>
/// <returns></returns>
public int UpdatePurchasePlan(PurchasePlanModel BigModel)
{
return(dal.UpdatePurchasePlan(BigModel));
}
/// <summary>
/// 采购计划新增
/// </summary>
/// <param name="PurchasePlanM">主表model</param>
/// <param name="PurchasePlanSourceMList">明细来源表model列表</param>
/// <param name="PurchasePlanDetailMList">明细表model列表</param>
/// <param name="IndexIDentity">主表ID</param>
/// <returns>bool</returns>
public static bool InsertPurchasePlanAll(PurchasePlanModel PurchasePlanM, List <PurchasePlanSourceModel> PurchasePlanSourceMList,
List <PurchasePlanDetailModel> PurchasePlanDetailMList, out int IndexIDentity, Hashtable htExtAttr)
{
ArrayList lstAdd = new ArrayList();
SqlCommand AddPri = PurchasePlanDBHelper.InsertPurchasePlanPrimary(PurchasePlanM);
lstAdd.Add(AddPri);
foreach (PurchasePlanSourceModel PurchasePlanSourceM in PurchasePlanSourceMList)
{
SqlCommand AddSource = PurchasePlanDBHelper.InsertPurchasePlanSource(PurchasePlanSourceM);
lstAdd.Add(AddSource);
}
foreach (PurchasePlanDetailModel PurchasePlanDetailM in PurchasePlanDetailMList)
{
SqlCommand AddDetail = PurchasePlanDBHelper.InsertPurchasePlanDetail(PurchasePlanDetailM);
lstAdd.Add(AddDetail);
}
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(PurchasePlanM, 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(PurchasePlanM.PlanNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return(isSucc);
}
/// <summary>
/// 修改采购计划
/// </summary>
/// <param name="PurchasePlanM">主表model</param>
/// <param name="PurchasePlanSourceMList">明细来源表model列表</param>
/// <param name="PurchasePlanDetailMList">明细表model列表</param>
/// <returns>bool</returns>
public static bool UpdatePurchasePlanAll(PurchasePlanModel PurchasePlanM, List <PurchasePlanSourceModel> PurchasePlanSourceMList,
List <PurchasePlanDetailModel> PurchasePlanDetailMList, Hashtable htExtAttr)
{
ArrayList lstUpdate = new ArrayList();
//更新主表
SqlCommand UpdatePri = PurchasePlanDBHelper.UpdatePurchasePlanPrimary(PurchasePlanM);
lstUpdate.Add(UpdatePri);
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(PurchasePlanM, htExtAttr, cmd);
if (htExtAttr.Count > 0)
{
lstUpdate.Add(cmd);
}
#endregion
//删除来源表
SqlCommand DeleteSource = PurchasePlanDBHelper.DeletePurchasePlanSource(PurchasePlanM.PlanNo);
lstUpdate.Add(DeleteSource);
//新增来源表
foreach (PurchasePlanSourceModel PurchasePlanSourceM in PurchasePlanSourceMList)
{
SqlCommand AddSource = PurchasePlanDBHelper.InsertPurchasePlanSource(PurchasePlanSourceM);
lstUpdate.Add(AddSource);
}
//删除明细表
SqlCommand DeleteDetail = PurchasePlanDBHelper.DeletePurchasePlanDetail(PurchasePlanM.PlanNo);
lstUpdate.Add(DeleteDetail);
//新增明细表
foreach (PurchasePlanDetailModel PurchasePlanDetailM in PurchasePlanDetailMList)
{
SqlCommand AddDetail = PurchasePlanDBHelper.InsertPurchasePlanDetail(PurchasePlanDetailM);
lstUpdate.Add(AddDetail);
}
//获取登陆用户信息
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(PurchasePlanM.PlanNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return(isSucc);
}
