/// <summary>
/// 主生产计划单插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertMasterProductSchedule(MasterProductScheduleModel model, Hashtable ht, out string ID)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
ID = "0";
try
{
bool succ = false;
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
LogInfoModel logModel = InitLogInfo(model.PlanNo, 0);
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
succ = MasterProductScheduleDBHelper.InsertMasterProductSchedule(model, ht,loginUserID, out ID);
if (!succ)
logModel.Remark = ConstUtil.LOG_PROCESS_FAILED;
else
logModel.Remark = ConstUtil.LOG_PROCESS_SUCCESS;
LogDBHelper.InsertLog(logModel);
return succ;
}
catch (Exception ex)
{
WriteSystemLog(userInfo, 0, ex);
return false;
}
}
/// <summary>
/// 获取主生产计划单主表信息
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetMasterProductScheduleInfo(MasterProductScheduleModel model)
{
try
{
return MasterProductScheduleDBHelper.GetMasterProductScheduleInfo(model);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 取消确认
/// </summary>
/// <param name="CompanyCD"></param>
/// <param name="BillTypeFlag"></param>
/// <param name="BillTypeCode"></param>
/// <param name="BillID"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static bool CancelConfirmOperate(MasterProductScheduleModel model,int BillTypeFlag,int BillTypeCode,string loginUserID)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
ArrayList listADD = new ArrayList();
//#region 传参
try
{
DataTable dbMaster = GetMasterProductScheduleDetailInfo(model);
if (dbMaster.Rows.Count > 0)
{
int BillStatus = int.Parse(dbMaster.Rows[0]["BillStatus"].ToString());
if (BillStatus == 2)
{
#region 撤消审批流程
#region 撤消审批处理逻辑描述
//可参见撤消审批的存储过程[FlowApproval_Update],个别的判断去掉
//--1.往流程任务历史记录表（officedba.FlowTaskHistory）插1条处理记录，
//--记录的步骤序号为0（表示返回到流程提交人环节)，审批状态为撤销审批
//Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate)
//Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate())
//--2.更新流程任务处理表（officedba.FlowTaskList）中的流程步骤序号为0（表示返回到流程提交人环节）
//Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID
//Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID
//--3更新流程实例表（officedba.FlowInstance）中的流程状态为“撤销审批”
//Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID
//Where CompanyCD=@CompanyCD
//and FlowNo=@tempFlowNo
//and BillTypeFlag=@BillTypeFlag
//and BillTypeCode=@BillTypeCode
//and BillID=@BillID
#endregion
DataTable dtFlowInstance = Common.FlowDBHelper.GetFlowInstanceInfo(model.CompanyCD, BillTypeFlag, BillTypeCode, model.ID);
if (dtFlowInstance.Rows.Count > 0)
{
//提交审批了的单据
string FlowInstanceID = dtFlowInstance.Rows[0]["FlowInstanceID"].ToString();
string FlowStatus = dtFlowInstance.Rows[0]["FlowStatus"].ToString();
string FlowNo = dtFlowInstance.Rows[0]["FlowNo"].ToString();
#region 往流程任务历史记录表
StringBuilder sqlHis = new StringBuilder();
sqlHis.AppendLine("Insert into officedba.FlowTaskHistory(CompanyCD,FlowInstanceID,FlowNo,BillTypeID,BillID,StepNo,State,operateUserId,operateDate)");
sqlHis.AppendLine("Values(@CompanyCD,@tempFlowInstanceID,@tempFlowNo,@BillTypeFlag,@BillID,0,2,@ModifiedUserID,getdate())");
SqlCommand commHis = new SqlCommand();
commHis.CommandText = sqlHis.ToString();
commHis.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commHis.Parameters.Add(SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID));
commHis.Parameters.Add(SqlHelper.GetParameter("@tempFlowNo", FlowNo));
commHis.Parameters.Add(SqlHelper.GetParameter("@BillTypeFlag", BillTypeFlag));
commHis.Parameters.Add(SqlHelper.GetParameter("@BillID", model.ID));
commHis.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID));
listADD.Add(commHis);
#endregion
#region 更新流程任务处理表
StringBuilder sqlTask = new StringBuilder();
sqlTask.AppendLine("Update officedba.FlowTaskList Set StepNo=0,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID");
sqlTask.AppendLine("Where CompanyCD=@CompanyCD and FlowInstanceID=@tempFlowInstanceID");
SqlCommand commTask = new SqlCommand();
commTask.CommandText = sqlTask.ToString();
commTask.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commTask.Parameters.Add(SqlHelper.GetParameter("@tempFlowInstanceID", FlowInstanceID));
commTask.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID));
listADD.Add(commTask);
#endregion
#region 更新流程实例表
StringBuilder sqlIns = new StringBuilder();
sqlIns.AppendLine("Update officedba.FlowInstance Set FlowStatus=5,ModifiedDate=getdate(),ModifiedUserID=@ModifiedUserID ");
sqlIns.AppendLine("Where CompanyCD=@CompanyCD ");
sqlIns.AppendLine("and FlowNo=@tempFlowNo ");
sqlIns.AppendLine("and BillTypeFlag=@BillTypeFlag ");
sqlIns.AppendLine("and BillTypeCode=@BillTypeCode ");
sqlIns.AppendLine("and BillID=@BillID");
SqlCommand commIns = new SqlCommand();
commIns.CommandText = sqlIns.ToString();
commIns.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commIns.Parameters.Add(SqlHelper.GetParameter("@tempFlowNo", FlowNo));
commIns.Parameters.Add(SqlHelper.GetParameter("@BillTypeFlag", BillTypeFlag));
commIns.Parameters.Add(SqlHelper.GetParameter("@BillTypeCode", BillTypeCode));
commIns.Parameters.Add(SqlHelper.GetParameter("@BillID", model.ID));
commIns.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID));
listADD.Add(commIns);
#endregion
}
#endregion
#region 处理自己的业务逻辑
//更新销售订单中的计划生产数量
#region 回写销售订单数据
DataTable dtDetail = new DataTable();
dtDetail = GetMasterProductScheduleDetailInfoList(model);
if (dtDetail.Rows.Count > 0)
{
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(dtDetail.Rows[i]["FromBillID"].ToString()))
{
int FromBillID = int.Parse(dtDetail.Rows[i]["FromBillID"].ToString());
int FromLineNo = int.Parse(dtDetail.Rows[i]["FromLineNo"].ToString());
Decimal ProduceCount = Decimal.Round(Decimal.Parse(dtDetail.Rows[i]["ProduceCount"].ToString()));
if (userInfo.IsMoreUnit)
{
ProduceCount = Decimal.Round(Decimal.Parse(dtDetail.Rows[i]["UsedUnitCount"].ToString()));
}
if (FromBillID > 0 && FromLineNo > 0)
{
//更新
// update officedba.SellOrderDetail set PlanProductCount=isnull(PlanProductCount,0)+@ProduceCount
// where CompanyCD=@CompanyCD and ID=@FromBillID and SortNo=@FromLineNo
#region 更新销售订单明细的计划生产数量
StringBuilder sqlSell = new StringBuilder();
sqlSell.AppendLine("update officedba.SellOrderDetail set PlanProductCount=isnull(PlanProductCount,0)-@ProduceCount where CompanyCD=@CompanyCD and SortNo=@FromLineNo and OrderNo=(select OrderNo from officedba.SellOrder where ID=@FromBillID)");
SqlCommand commSell = new SqlCommand();
commSell.CommandText = sqlSell.ToString();
commSell.Parameters.Add(SqlHelper.GetParameter("@ProduceCount", ProduceCount));
commSell.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commSell.Parameters.Add(SqlHelper.GetParameter("@FromBillID", FromBillID));
commSell.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", FromLineNo));
listADD.Add(commSell);
#endregion
}
}
}
}
#endregion
StringBuilder sqlUn = new StringBuilder();
sqlUn.AppendLine(" UPDATE officedba.MasterProductSchedule SET");
sqlUn.AppendLine(" Confirmor = null,");
sqlUn.AppendLine(" ConfirmDate = null,");
sqlUn.AppendLine(" ModifiedDate = getdate(),");
sqlUn.AppendLine(" BillStatus = 1,");
sqlUn.AppendLine(" ModifiedUserID = '" + loginUserID + "'");
sqlUn.AppendLine(" Where ID=@ID");
SqlCommand commUn = new SqlCommand();
commUn.CommandText = sqlUn.ToString();
commUn.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
listADD.Add(commUn);
#endregion
return SqlHelper.ExecuteTransWithArrayList(listADD);
}
}
return false;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 确认或结单
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool ConfirmOrCompleteMasterProductSchedule(MasterProductScheduleModel model, string loginUserID, int OperateType)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
ArrayList listADD = new ArrayList();
if (OperateType == 1)
{
DataTable dbMaster = GetMasterProductScheduleDetailInfo(model);
if (dbMaster.Rows.Count > 0)
{
int BillStatus = int.Parse(dbMaster.Rows[0]["BillStatus"].ToString());
if (BillStatus == 1)
{
//防止多用户登录该页面，同时操作确认操作，需要判断当前单据是否还是制单状态
//单据确认后，同时更新对应销售订单中对应明细的计划生产数量。
#region 更新对应销售订单中的计划生产数量
DataTable dtDetail = new DataTable();
dtDetail = GetMasterProductScheduleDetailInfoList(model);
if (dtDetail.Rows.Count > 0)
{
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(dtDetail.Rows[i]["FromBillID"].ToString()))
{
int FromBillID = int.Parse(dtDetail.Rows[i]["FromBillID"].ToString());
int FromLineNo = int.Parse(dtDetail.Rows[i]["FromLineNo"].ToString());
Decimal ProduceCount = Decimal.Round(Decimal.Parse(dtDetail.Rows[i]["ProduceCount"].ToString()));
if (userInfo.IsMoreUnit)
{
ProduceCount = Decimal.Round(Decimal.Parse(dtDetail.Rows[i]["UsedUnitCount"].ToString()));
}
if (FromBillID > 0 && FromLineNo > 0)
{
//更新
// update officedba.SellOrderDetail set PlanProductCount=isnull(PlanProductCount,0)+@ProduceCount
// where CompanyCD=@CompanyCD and ID=@FromBillID and SortNo=@FromLineNo
#region 更新销售订单明细的计划生产数量
StringBuilder sqlSell = new StringBuilder();
sqlSell.AppendLine("update officedba.SellOrderDetail set PlanProductCount=isnull(PlanProductCount,0)+@ProduceCount where CompanyCD=@CompanyCD and SortNo=@FromLineNo and OrderNo=(select OrderNo from officedba.SellOrder where ID=@FromBillID)");
SqlCommand commSell = new SqlCommand();
commSell.CommandText = sqlSell.ToString();
commSell.Parameters.Add(SqlHelper.GetParameter("@ProduceCount", ProduceCount));
commSell.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commSell.Parameters.Add(SqlHelper.GetParameter("@FromBillID", FromBillID));
commSell.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", FromLineNo));
listADD.Add(commSell);
#endregion
}
}
}
}
#endregion
#region 更新确认人
StringBuilder sqlMaster = new StringBuilder();
sqlMaster.AppendLine(" UPDATE officedba.MasterProductSchedule SET");
sqlMaster.AppendLine(" Confirmor = @Confirmor,");
sqlMaster.AppendLine(" ConfirmDate = @ConfirmDate,");
sqlMaster.AppendLine(" ModifiedDate = getdate(),");
sqlMaster.AppendLine(" BillStatus = 2,");
sqlMaster.AppendLine(" ModifiedUserID = '" + loginUserID + "'");
sqlMaster.AppendLine(" Where ID=@ID");
SqlCommand commMaster = new SqlCommand();
commMaster.CommandText = sqlMaster.ToString();
commMaster.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
commMaster.Parameters.Add(SqlHelper.GetParameter("@Confirmor", model.Confirmor));
commMaster.Parameters.Add(SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate));
listADD.Add(commMaster);
#endregion
}
}
}
else if (OperateType == 2)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" UPDATE officedba.MasterProductSchedule SET");
sql.AppendLine(" Closer = @Closer,");
sql.AppendLine(" CloseDate = @CloseDate,");
sql.AppendLine(" BillStatus = 4,");
sql.AppendLine(" ModifiedDate = getdate(),");
sql.AppendLine(" ModifiedUserID = '" + loginUserID + "'");
sql.AppendLine(" Where ID=@ID");
SqlCommand commMaster = new SqlCommand();
commMaster.CommandText = sql.ToString();
commMaster.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
commMaster.Parameters.Add(SqlHelper.GetParameter("@Closer", model.Closer));
commMaster.Parameters.Add(SqlHelper.GetParameter("@CloseDate", model.CloseDate));
listADD.Add(commMaster);
}
else
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(" update officedba.MasterProductSchedule set Closer=null,CloseDate=null,ModifiedDate=getdate(),BillStatus=2,ModifiedUserID = '" + loginUserID + "'");
sql.AppendLine(" Where ID=@ID");
SqlCommand commMaster = new SqlCommand();
commMaster.CommandText = sql.ToString();
commMaster.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
listADD.Add(commMaster);
}
return SqlHelper.ExecuteTransWithArrayList(listADD);
}
/// <summary>
/// 修改退料单和各明细信息
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <returns></returns>
public static bool UpdateMasterproductSchedule(MasterProductScheduleModel model, Hashtable htExtAttr, string DetailUpdate,string loginUserID)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//获取登陆用户ID
ArrayList listADD = new ArrayList();
if (model.ID <= 0)
{
return false;
}
#region 主生产计划单修改SQL语句
StringBuilder sqlEdit = new StringBuilder();
sqlEdit.AppendLine(" UPDATE officedba.MasterProductSchedule SET ");
sqlEdit.AppendLine(" Subject =@Subject,");
sqlEdit.AppendLine(" Principal =@Principal,");
sqlEdit.AppendLine(" CountTotal =@CountTotal,");
sqlEdit.AppendLine(" Remark =@Remark,");
sqlEdit.AppendLine(" DeptID =@DeptID,");
sqlEdit.AppendLine(" ModifiedDate =getdate(),");
sqlEdit.AppendLine(" ModifiedUserID = '" + loginUserID + "'");
sqlEdit.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sqlEdit.ToString();
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject));
comm.Parameters.Add(SqlHelper.GetParameter("@Principal", model.Principal));
comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID));
listADD.Add(comm);
#endregion
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
#region 主生产计划单明细处理
if (DetailUpdate.Length > 0)
{
string[] updateID = DetailUpdate.Split(',');
if (!string.IsNullOrEmpty(DetailUpdate) && updateID.Length > 0)
{
#region 有更新记录的
//先删除不在工序明细中的ID
//更新工序明细中的ID
//添加其余工序明细信息
//先删除不在工序明细中的ID
#region 先删除不在主生产计划明细中的ID
StringBuilder sqlDel = new StringBuilder();
sqlDel.AppendLine("Delete From officedba.MasterProductScheduleDetail where CompanyCD=@CompanyCD and PlanNo=@PlanNo and ID not in(" + DetailUpdate + ")");
SqlCommand commDel = new SqlCommand();
commDel.CommandText = sqlDel.ToString();
commDel.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
commDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commDel.Parameters.Add(SqlHelper.GetParameter("@PlanNo", model.PlanNo));
listADD.Add(commDel);
#endregion
for (int i = 0; i < updateID.Length; i++)
{
System.Text.StringBuilder cmdsql = new System.Text.StringBuilder();
int intUpdateID = int.Parse(updateID[i].ToString());
if (intUpdateID > 0)
{
#region 更新主生产计划明细
cmdsql.AppendLine("Update officedba.MasterProductScheduleDetail ");
cmdsql.AppendLine(" Set ");
cmdsql.AppendLine(" SortNo=@SortNo, ");
cmdsql.AppendLine(" ProductID=@ProductID, ");
cmdsql.AppendLine(" UnitID=@UnitID, ");
if (model.DetProductCount.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(" ProductCount=@ProductCount, ");
}
else
{
cmdsql.AppendLine(" ProductCount=null, ");
}
cmdsql.AppendLine(" ProduceCount=@ProduceCount, ");
cmdsql.AppendLine(" StartDate=@StartDate, ");
cmdsql.AppendLine(" EndDate=@EndDate, ");
if (model.DetFromBillID.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(" FromBillID=@FromBillID, ");
}
if (model.DetFromBillNo.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(" FromBillNo=@FromBillNo, ");
}
if (model.DetFromLineNo.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(" FromLineNo=@FromLineNo, ");
}
cmdsql.AppendLine(" Remark=@Remark,");
if (userInfo.IsMoreUnit)
{
cmdsql.AppendLine(" UsedUnitID=@UsedUnitID,");
cmdsql.AppendLine(" UsedUnitCount=@UsedUnitCount,");
cmdsql.AppendLine(" ExRate=@ExRate,");
}
cmdsql.AppendLine(" ModifiedDate= getdate(), ");
cmdsql.AppendLine(" ModifiedUserID='" + loginUserID + "' ");
cmdsql.AppendLine(" Where CompanyCD=@CompanyCD and PlanNo=@PlanNo and ID=@ID ");
SqlCommand commSql = new SqlCommand();
commSql.CommandText = cmdsql.ToString();
commSql.Parameters.Add(SqlHelper.GetParameter("@ID", intUpdateID));
commSql.Parameters.Add(SqlHelper.GetParameter("@PlanNo", model.PlanNo));
commSql.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commSql.Parameters.Add(SqlHelper.GetParameter("@SortNo", model.DetSortNo.Split(',')[i].ToString()));
commSql.Parameters.Add(SqlHelper.GetParameter("@ProductID", model.DetProductID.Split(',')[i].ToString()));
commSql.Parameters.Add(SqlHelper.GetParameter("@UnitID", model.DetUnitID.Split(',')[i].ToString()));
if (model.DetProductCount.Split(',')[i].ToString().Trim().Length > 0)
{
commSql.Parameters.Add(SqlHelper.GetParameter("@ProductCount", model.DetProductCount.Split(',')[i].ToString()));
}
commSql.Parameters.Add(SqlHelper.GetParameter("@ProduceCount", model.DetProduceCount.Split(',')[i].ToString()));
commSql.Parameters.Add(SqlHelper.GetParameter("@StartDate", model.DetStartDate.Split(',')[i].ToString()));
commSql.Parameters.Add(SqlHelper.GetParameter("@EndDate", model.DetEndDate.Split(',')[i].ToString()));
if (model.DetFromBillID.Split(',')[i].ToString().Trim().Length > 0)
{
commSql.Parameters.Add(SqlHelper.GetParameter("@FromBillID", model.DetFromBillID.Split(',')[i].ToString().Trim()));
}
if (model.DetFromBillNo.Split(',')[i].ToString().Trim().Length > 0)
{
commSql.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", model.DetFromBillNo.Split(',')[i].ToString().Trim()));
}
if (model.DetFromLineNo.Split(',')[i].ToString().Trim().Length > 0)
{
commSql.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", model.DetFromLineNo.Split(',')[i].ToString().Trim()));
}
commSql.Parameters.Add(SqlHelper.GetParameter("@Remark", model.DetRemark.Split(',')[i].ToString().Trim()));
if (userInfo.IsMoreUnit)
{
commSql.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", model.DetUsedUnitID.Split(',')[i].ToString().Trim()));
commSql.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", model.DetUsedUnitCount.Split(',')[i].ToString().Trim()));
commSql.Parameters.Add(SqlHelper.GetParameter("@ExRate", model.DetExRate.Split(',')[i].ToString().Trim()));
}
listADD.Add(commSql);
#endregion
}
else
{
#region 添加主生产计划明细
//添加
cmdsql.AppendLine(" Insert into officedba.MasterProductScheduleDetail( CompanyCD, ");
cmdsql.AppendLine(" PlanNo, ");
cmdsql.AppendLine(" SortNo, ");
cmdsql.AppendLine(" ProductID, ");
cmdsql.AppendLine(" UnitID, ");
if (model.DetProductCount.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(" ProductCount, ");
}
cmdsql.AppendLine(" ProduceCount, ");
cmdsql.AppendLine(" StartDate, ");
cmdsql.AppendLine(" EndDate, ");
if (model.DetFromBillID.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(" FromBillID, ");
}
if (model.DetFromBillNo.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(" FromBillNo, ");
}
if (model.DetFromLineNo.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(" FromLineNo, ");
}
cmdsql.AppendLine(" Remark, ");
if (userInfo.IsMoreUnit)
{
cmdsql.AppendLine(" UsedUnitID, ");
cmdsql.AppendLine(" UsedUnitCount, ");
cmdsql.AppendLine(" ExRate, ");
}
cmdsql.AppendLine(" ModifiedDate, ");
cmdsql.AppendLine(" ModifiedUserID) ");
cmdsql.AppendLine(" Values( @CompanyCD ");
cmdsql.AppendLine(", @PlanNo ");
cmdsql.AppendLine(", @SortNo ");
cmdsql.AppendLine(" , @ProductID ");
cmdsql.AppendLine(" , @UnitID ");
if (model.DetProductCount.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(" , @ProductCount ");
}
cmdsql.AppendLine(", @ProduceCount ");
cmdsql.AppendLine(", @StartDate ");
cmdsql.AppendLine(", @EndDate ");
if (model.DetFromBillID.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(", @FromBillID ");
}
if (model.DetFromBillNo.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(", @FromBillNo ");
}
if (model.DetFromLineNo.Split(',')[i].ToString().Trim().Length > 0)
{
cmdsql.AppendLine(", @FromLineNo ");
}
cmdsql.AppendLine(", @Remark ");
if (userInfo.IsMoreUnit)
{
cmdsql.AppendLine(", @UsedUnitID ");
cmdsql.AppendLine(", @UsedUnitCount ");
cmdsql.AppendLine(", @ExRate ");
}
cmdsql.AppendLine(", getdate(), ");
cmdsql.AppendLine(" '" + loginUserID + "') ");
SqlCommand commSql = new SqlCommand();
commSql.CommandText = cmdsql.ToString();
commSql.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commSql.Parameters.Add(SqlHelper.GetParameter("@PlanNo", model.PlanNo));
commSql.Parameters.Add(SqlHelper.GetParameter("@SortNo", model.DetSortNo.Split(',')[i].ToString()));
commSql.Parameters.Add(SqlHelper.GetParameter("@ProductID", model.DetProductID.Split(',')[i].ToString()));
commSql.Parameters.Add(SqlHelper.GetParameter("@UnitID", model.DetUnitID.Split(',')[i].ToString()));
if (model.DetProductCount.Split(',')[i].ToString().Trim().Length > 0)
{
commSql.Parameters.Add(SqlHelper.GetParameter("@ProductCount", model.DetProductCount.Split(',')[i].ToString()));
}
commSql.Parameters.Add(SqlHelper.GetParameter("@ProduceCount", model.DetProduceCount.Split(',')[i].ToString()));
commSql.Parameters.Add(SqlHelper.GetParameter("@StartDate", model.DetStartDate.Split(',')[i].ToString()));
commSql.Parameters.Add(SqlHelper.GetParameter("@EndDate", model.DetEndDate.Split(',')[i].ToString()));
if (model.DetFromBillID.Split(',')[i].ToString().Trim().Length > 0)
{
commSql.Parameters.Add(SqlHelper.GetParameter("@FromBillID", model.DetFromBillID.Split(',')[i].ToString().Trim()));
}
if (model.DetFromBillNo.Split(',')[i].ToString().Trim().Length > 0)
{
commSql.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", model.DetFromBillNo.Split(',')[i].ToString().Trim()));
}
if (model.DetFromLineNo.Split(',')[i].ToString().Trim().Length > 0)
{
commSql.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", model.DetFromLineNo.Split(',')[i].ToString().Trim()));
}
commSql.Parameters.Add(SqlHelper.GetParameter("@Remark", model.DetRemark.Split(',')[i].ToString().Trim()));
if (userInfo.IsMoreUnit)
{
commSql.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", model.DetUsedUnitID.Split(',')[i].ToString().Trim()));
commSql.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", model.DetUsedUnitCount.Split(',')[i].ToString().Trim()));
commSql.Parameters.Add(SqlHelper.GetParameter("@ExRate", model.DetExRate.Split(',')[i].ToString().Trim()));
}
listADD.Add(commSql);
#endregion
}
}
#endregion
}
else
{
#region 没有要更新,删除所有明细
StringBuilder sqlDelAll = new StringBuilder();
sqlDelAll.AppendLine("Delete From officedba.MasterProductScheduleDetail where CompanyCD=@CompanyCD and PlanNo=@PlanNo ");
SqlCommand commDelAll = new SqlCommand();
commDelAll.CommandText = sqlDelAll.ToString();
commDelAll.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commDelAll.Parameters.Add(SqlHelper.GetParameter("@PlanNo", model.PlanNo));
listADD.Add(commDelAll);
#endregion
}
}
else
{
#region 无明细记录
StringBuilder sqlDelAll = new StringBuilder();
sqlDelAll.AppendLine("Delete From officedba.MasterProductScheduleDetail where CompanyCD=@CompanyCD and PlanNo=@PlanNo ");
SqlCommand commDelAll = new SqlCommand();
commDelAll.CommandText = sqlDelAll.ToString();
commDelAll.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commDelAll.Parameters.Add(SqlHelper.GetParameter("@PlanNo", model.PlanNo));
listADD.Add(commDelAll);
#endregion
}
#endregion
return SqlHelper.ExecuteTransWithArrayList(listADD);
}
/// <summary>
/// 主生产计划单明细详细信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetMasterProductScheduleDetailInfoList(MasterProductScheduleModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
#region 查询语句
//查询SQL拼写
StringBuilder detSql = new StringBuilder();
detSql.AppendLine("select a.ID as DetailID,a.CompanyCD,a.PlanNo,a.SortNo,a.ProductID,b.ProdNo,b.ProductName,b.Specification,a.UsedUnitID,d.CodeName as UsedUnitName,Convert(numeric(14," + userInfo.SelPoint + "),a.UsedUnitCount) as UsedUnitCount,a.ExRate,a.UnitID,c.CodeName as CodeName,");
detSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),a.ProductCount) as ProductCount,Convert(numeric(14," + userInfo.SelPoint + "),a.ProduceCount) as ProduceCount,");
detSql.AppendLine(" isnull( CONVERT(CHAR(10), a.StartDate, 23),'') as StartDate,isnull( CONVERT(CHAR(10), a.EndDate, 23),'') as EndDate,");
detSql.AppendLine(" a.FromBillID,a.FromBillNo,a.FromLineNo,Convert(numeric(14," + userInfo.SelPoint + "),a.PlanCount) as PlanCount,Convert(numeric(14," + userInfo.SelPoint + "),a.ProcessedCount) as ProcessedCount,");
detSql.AppendLine(" a.Remark as DetailRemark ");
detSql.AppendLine("from officedba.MasterProductScheduleDetail a");
detSql.AppendLine("left join officedba.ProductInfo b on a.ProductID=b.ID");
detSql.AppendLine("left join officedba.CodeUnitType c on a.UnitID=c.ID");
detSql.AppendLine("left join officedba.CodeUnitType d on a.UsedUnitID=d.ID");
detSql.AppendLine("where a.CompanyCD=@CompanyCD");
detSql.AppendLine("and a.PlanNo=(select top 1 PlanNo from officedba.MasterProductSchedule where ID=@ID)");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString()));
//指定命令的SQL文
comm.CommandText = detSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 主生产计划单主表信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetMasterProductScheduleInfo(MasterProductScheduleModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
#region 查询语句
//查询SQL拼写
StringBuilder infoSql = new StringBuilder();
infoSql.AppendLine("select * from");
infoSql.AppendLine("(");
infoSql.AppendLine(" select a.CompanyCD,a.ID,a.PlanNo,a.DeptID,a.Subject,a.Principal,");
infoSql.AppendLine(" Convert(numeric(22," + userInfo.SelPoint+ "),a.CountTotal) as CountTotal,a.Creator,");
infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.CreateDate, 23),'') as CreateDate,isnull( CONVERT(CHAR(10), a.ModifiedDate, 23),'') as ModifiedDate,a.ModifiedUserID,");
infoSql.AppendLine(" case when a.BillStatus=1 then '制单' when a.BillStatus=2 then '执行' when a.BillStatus=4 then '手工结单' when a.BillStatus=5 then '自动结单' end as strBillStatusText,");
infoSql.AppendLine(" a.BillStatus,a.Confirmor,isnull( CONVERT(CHAR(10), a.ConfirmDate, 23),'') as ConfirmDate,");
infoSql.AppendLine(" a.Closer,isnull( CONVERT(CHAR(10), a.CloseDate, 23),'') as CloseDate,a.Remark,b.EmployeeName as CreatorReal,");
infoSql.AppendLine(" c.EmployeeName as PrincipalReal ,d.EmployeeName as ConfirmorReal,e.EmployeeName as CloserReal,f.DeptName, ");
infoSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10 ");
infoSql.AppendLine(" from officedba.MasterProductSchedule as a");
infoSql.AppendLine(" left join officedba.EmployeeInfo AS B ON a.Creator=b.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo AS c on a.Principal=c.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo AS d on a.Confirmor=d.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo AS e on a.Closer=e.ID");
infoSql.AppendLine(" left join officedba.DeptInfo As f on f.ID=a.DeptID");
infoSql.AppendLine(")as info");
infoSql.AppendLine("where ID=@ID");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString()));
//指定命令的SQL文
comm.CommandText = infoSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 主生产计划单插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertMasterProductSchedule(MasterProductScheduleModel model, Hashtable htExtAttr, string loginUserID, out string ID)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
ArrayList listADD = new ArrayList();
bool result = false;
#region 传参
#region 主生产计划单添加SQL语句
StringBuilder sqlMaster = new StringBuilder();
sqlMaster.AppendLine("INSERT INTO officedba. MasterProductSchedule");
sqlMaster.AppendLine("(CompanyCD,PlanNo,Subject,Principal,DeptID,CountTotal,Creator,CreateDate,BillStatus,Remark,ModifiedDate,ModifiedUserID)");
sqlMaster.AppendLine("VALUES ");
sqlMaster.AppendLine(" (@CompanyCD");
sqlMaster.AppendLine(" ,@PlanNo");
sqlMaster.AppendLine(" ,@Subject");
sqlMaster.AppendLine(" ,@Principal");
sqlMaster.AppendLine(" ,@DeptID");
sqlMaster.AppendLine(" ,@CountTotal");
sqlMaster.AppendLine(" ,@Creator");
sqlMaster.AppendLine(" ,@CreateDate");
sqlMaster.AppendLine(" ,@BillStatus");
sqlMaster.AppendLine(" ,@Remark");
sqlMaster.AppendLine(" ,getdate() ");
sqlMaster.AppendLine(" ,'" + loginUserID + "') ");
sqlMaster.AppendLine("set @ID=@@IDENTITY");
SqlCommand comm = new SqlCommand();
comm.CommandText = sqlMaster.ToString();
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@PlanNo", model.PlanNo));
comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject));
comm.Parameters.Add(SqlHelper.GetParameter("@Principal", model.Principal));
comm.Parameters.Add(SqlHelper.GetParameter("@DeptID", model.DeptID));
comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate));
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int));
listADD.Add(comm);
#endregion
#region 主生产计划单明细
try
{
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
if (!String.IsNullOrEmpty(model.DetSortNo) && !String.IsNullOrEmpty(model.DetProductID))
{
#region 主生产计划单明细添加语句
string[] detSortNo = model.DetSortNo.Split(',');
string[] detProductID = model.DetProductID.Split(',');
string[] detUnitID = model.DetUnitID.Split(',');
string[] detStartDate = model.DetStartDate.Split(',');
string[] detEndDate = model.DetEndDate.Split(',');
string[] detFromBiilID = model.DetFromBillID.Split(',');
string[] detFromBillNo = model.DetFromBillNo.Split(',');
string[] detFromLineNo = model.DetFromLineNo.Split(',');
string[] detProductCount = model.DetProductCount.Split(',');
string[] detProduceCount = model.DetProduceCount.Split(',');
string[] detRemark = model.DetRemark.Split(',');
string[] detUsedUnitID = model.DetUsedUnitID.Split(',');
string[] detUsedUnitCount = model.DetUsedUnitCount.Split(',');
string[] detExRate = model.DetExRate.Split(',');
//页面上这些字段都是必填，数组的长度必须是相同的
if (detSortNo.Length >= 1)
{
for (int i = 0; i < detSortNo.Length; i++)
{
System.Text.StringBuilder cmdsql = new System.Text.StringBuilder();
cmdsql.AppendLine("insert into officedba. MasterProductScheduleDetail");
cmdsql.AppendLine("(CompanyCD,");
cmdsql.AppendLine("PlanNo,");
cmdsql.AppendLine("SortNo,");
cmdsql.AppendLine("ProductID,");
cmdsql.AppendLine("UnitID,");
if (detProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(detProductCount[i].ToString().Trim()))
{
cmdsql.AppendLine("ProductCount,");
}
}
cmdsql.AppendLine("ProduceCount,");
cmdsql.AppendLine("StartDate,");
cmdsql.AppendLine("EndDate,");
cmdsql.AppendLine("FromBillID,");
cmdsql.AppendLine("FromBillNo,");
cmdsql.AppendLine("FromLineNo,");
cmdsql.AppendLine("Remark,");
if (userInfo.IsMoreUnit)
{
cmdsql.AppendLine("UsedUnitID,");
cmdsql.AppendLine("UsedUnitCount,");
cmdsql.AppendLine("ExRate,");
}
cmdsql.AppendLine("ModifiedUserID)");
cmdsql.AppendLine(" Values(@CompanyCD");
cmdsql.AppendLine(" ,@PlanNo");
cmdsql.AppendLine(" ,@SortNo");
cmdsql.AppendLine(" ,@ProductID");
cmdsql.AppendLine(" ,@UnitID");
if (detProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(detProductCount[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@ProductCount");
}
}
cmdsql.AppendLine(" ,@ProduceCount");
cmdsql.AppendLine(" ,@StartDate");
cmdsql.AppendLine(" ,@EndDate");
cmdsql.AppendLine(" ,@FromBillID");
cmdsql.AppendLine(" ,@FromBillNo");
cmdsql.AppendLine(" ,@FromLineNo");
cmdsql.AppendLine(" ,@Remark");
if (userInfo.IsMoreUnit)
{
cmdsql.AppendLine(" ,@UsedUnitID");
cmdsql.AppendLine(" ,@UsedUnitCount");
cmdsql.AppendLine(" ,@ExRate");
}
cmdsql.AppendLine(" ,@ModifiedUserID)");
SqlCommand comms = new SqlCommand();
comms.CommandText = cmdsql.ToString();
comms.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comms.Parameters.Add(SqlHelper.GetParameter("@PlanNo", model.PlanNo));
comms.Parameters.Add(SqlHelper.GetParameter("@SortNo", detSortNo[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ProductID", detProductID[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@UnitID", detUnitID[i].ToString()));
if (detProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(detProductCount[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@ProductCount", detProductCount[i].ToString()));
}
}
comms.Parameters.Add(SqlHelper.GetParameter("@ProduceCount", detProduceCount[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@StartDate", detStartDate[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@EndDate", detEndDate[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@FromBillID", detFromBiilID[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", detFromBillNo[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", detFromLineNo[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@Remark", detRemark[i].ToString()));
if (userInfo.IsMoreUnit)
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", detUsedUnitID[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", detUsedUnitCount[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ExRate", detExRate[i].ToString()));
}
comms.Parameters.Add(SqlHelper.GetParameter("@ModifiedUserID", loginUserID));
listADD.Add(comms);
}
}
#endregion
}
if (SqlHelper.ExecuteTransWithArrayList(listADD))
{
ID = comm.Parameters["@ID"].Value.ToString();
result = true;
}
else
{
ID = "0";
}
return result;
}
catch (Exception ex)
{
throw ex;
}
#endregion
#endregion
}
/// <summary>
/// 主生产计划单详细信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetMasterProductScheduleDetailInfo(MasterProductScheduleModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
#region 查询语句
//查询SQL拼写
StringBuilder infoSql = new StringBuilder();
infoSql.AppendLine("select * from (");
infoSql.AppendLine(" select a.CompanyCD,a.ID,a.PlanNo,a.DeptID,a.Subject,a.Principal,");
infoSql.AppendLine(" Convert(numeric(22,"+userInfo.SelPoint+"),a.CountTotal) as CountTotal,a.Creator,");
infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.CreateDate, 23),'') as CreateDate,isnull( CONVERT(CHAR(10), a.ModifiedDate, 23),'') as ModifiedDate,a.ModifiedUserID,");
infoSql.AppendLine(" a.BillStatus,a.Confirmor,isnull( CONVERT(CHAR(10), a.ConfirmDate, 23),'') as ConfirmDate,");
infoSql.AppendLine(" a.Closer,isnull( CONVERT(CHAR(10), a.CloseDate, 23),'') as CloseDate,a.Remark,b.EmployeeName as CreatorReal,");
infoSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,");
infoSql.AppendLine(" c.EmployeeName as PrincipalReal ,d.EmployeeName as ConfirmorReal,e.EmployeeName as CloserReal,f.DeptName ");
infoSql.AppendLine(" from officedba.MasterProductSchedule as a ");
infoSql.AppendLine(" left join officedba.EmployeeInfo AS B ON a.CompanyCD=@CompanyCD and a.Creator=b.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo AS c on a.Principal=c.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo AS d on a.Confirmor=d.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo AS e on a.Closer=e.ID");
infoSql.AppendLine(" left join officedba.DeptInfo As f on f.ID=a.DeptID");
infoSql.AppendLine(" ) as m ");
infoSql.AppendLine("left outer join (");
infoSql.AppendLine(" select d.ID as DetailID,d.PlanNo,d.SortNo,d.ProductID,d.UnitID,");
infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),d.ProductCount) as ProductCount,");
infoSql.AppendLine(" Convert(numeric(14," + userInfo.SelPoint + "),d.UsedUnitCount) as UsedUnitCount,");
infoSql.AppendLine(" isnull( CONVERT(CHAR(10), d.StartDate, 23),'') as StartDate,");
infoSql.AppendLine(" isnull( CONVERT(CHAR(10), d.EndDate, 23),'') as EndDate,");
infoSql.AppendLine(" d.FromBillID,d.FromBillNo,d.FromLineNo,Convert(numeric(14,"+userInfo.SelPoint+"),d.PlanCount) as PlanCount,");
infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),d.ProcessedCount) as ProcessedCount,");
infoSql.AppendLine(" Convert(numeric(14,"+userInfo.SelPoint+"),d.ProduceCount) as ProduceCount,d.Remark as DetailRemark,");
infoSql.AppendLine(" p.ProductName,p.ProdNo,p.Specification,t.CodeName");
infoSql.AppendLine(" from officedba. MasterProductScheduleDetail d");
infoSql.AppendLine(" inner join officedba.ProductInfo p on p.ID=d.ProductID");
infoSql.AppendLine(" inner join officedba.CodeUnitType t on t.ID=p.UnitID where d.CompanyCD=@CompanyCD");
infoSql.AppendLine(") as info ");
infoSql.AppendLine("on info.PlanNo=m.PlanNo");
infoSql.AppendLine("where m.ID=@ID");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString()));
//指定命令的SQL文
comm.CommandText = infoSql.ToString();
//执行查询
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(MasterProductScheduleModel model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.MasterProductSchedule 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)
{ }
}
/// <summary>
/// 查询主生产计划单信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable GetMasterProductScheduleListBycondition(MasterProductScheduleModel model, int FromBillID, int FlowStatus, string BillTypeFlag, string BillTypeCode, string EFIndex,string EFDesc,int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("select * from (");
searchSql.AppendLine(" select a.CompanyCD,a.ID,a.PlanNo,a.Subject,a.Principal,Convert(numeric(22,"+userInfo.SelPoint+"),a.CountTotal) as TotalCounts,a.ModifiedDate,");
searchSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,");
searchSql.AppendLine(" a.DeptID,isnull(a.Confirmor,'')as Confirmor,isnull(c.EmployeeName,'')as ConfirmorReal,a.BillStatus,isnull(d.EmployeeName,'')as PrincipalReal,");
searchSql.AppendLine(" isnull( CONVERT(CHAR(10), a.ConfirmDate, 23),'') as ConfirmDate,isnull(e.DeptName,'')as DeptName,isnull(b.FlowStatus,'0')as FlowStatus,");
searchSql.AppendLine(" case when a.BillStatus=1 then '制单' when a.BillStatus=2 then '执行' when a.BillStatus=3 then '变更' when a.BillStatus=4 then '手工结单' when a.BillStatus=5 then '自动结单' end as strBillStatus,");
searchSql.AppendLine(" case when b.FlowStatus=1 then '待审批' when b.FlowStatus=2 then '审批中' when b.FlowStatus=3 then '审批通过' when b.FlowStatus=4 then '审批不通过' when b.FlowStatus=5 then '撤消审批' end as strFlowStatus ");
searchSql.AppendLine(" from officedba.MasterProductSchedule a");
searchSql.AppendLine(" left join officedba.FlowInstance b ON a.ID=b.BillID");
searchSql.AppendLine(" and b.BillTypeFlag=@BillTypeFlag");
searchSql.AppendLine(" and b.BillTypeCode=@BillTypeCode");
searchSql.AppendLine(" and b.ID =( ");
searchSql.AppendLine(" select max(ID)");
searchSql.AppendLine(" from officedba.FlowInstance H");
searchSql.AppendLine(" where H.CompanyCD = A.CompanyCD");
searchSql.AppendLine(" and H.BillID = A.ID");
searchSql.AppendLine(" and H.BillTypeFlag =@BillTypeFlag");
searchSql.AppendLine(" and H.BillTypeCode = @BillTypeCode)");
searchSql.AppendLine(" left join officedba.EmployeeInfo c on c.ID=a.Confirmor");
searchSql.AppendLine(" left join officedba.EmployeeInfo d on d.ID=a.Principal");
searchSql.AppendLine(" left join officedba.DeptInfo e on a.DeptID=e.ID");
searchSql.AppendLine(")as info");
searchSql.AppendLine(" where CompanyCD=@CompanyCD");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeFlag", BillTypeFlag.ToString()));
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeCode", BillTypeCode.ToString()));
//单据编号
if (!string.IsNullOrEmpty(model.PlanNo))
{
searchSql.AppendLine(" and PlanNo like @PlanNo ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PlanNo", "%" + model.PlanNo + "%"));
}
//单据主题
if (!string.IsNullOrEmpty(model.Subject))
{
searchSql.AppendLine(" and Subject like @Subject");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Subject", "%" + model.Subject + "%"));
}
//负责人
if (model.Principal > 0)
{
searchSql.AppendLine(" and Principal=@Principal ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Principal", model.Principal.ToString()));
}
//部门
if (model.DeptID > 0)
{
searchSql.AppendLine(" and DeptID=@DeptID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.DeptID.ToString()));
}
//销售订单
if (FromBillID > 0)
{
searchSql.AppendLine(" and PlanNo in(select PlanNo from officedba.MasterProductScheduleDetail where FromBillID=@FromBillID group by PlanNo) ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromBillID", FromBillID.ToString()));
}
//单据状态
if (!string.IsNullOrEmpty(model.BillStatus))
{
if (int.Parse(model.BillStatus) > 0)
{
searchSql.AppendLine("and BillStatus=@BillStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillStatus", model.BillStatus));
}
}
if (FlowStatus >-1)
{
searchSql.AppendLine(" and FlowStatus=@FlowStatus ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@FlowStatus", FlowStatus.ToString()));
}
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
if (int.Parse(EFIndex) > 0)
{
searchSql.AppendLine(" and ExtField" + EFIndex + " LIKE @EFDesc");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%"));
}
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount);
}
/// <summary>
/// 查询主生产计划单信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable GetMasterProductScheduleListBycondition(MasterProductScheduleModel model, int FromBillID, int FlowStatus, string BillTypeFlag, string BillTypeCode, string EFIndex,string EFDesc,int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
return MasterProductScheduleDBHelper.GetMasterProductScheduleListBycondition(model, FromBillID, FlowStatus, BillTypeFlag, BillTypeCode, EFIndex, EFDesc, pageIndex, pageCount, OrderBy, ref totalCount);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 取消确认
/// </summary>
/// <param name="model"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool CancelConfirmMasterProductSchedule(MasterProductScheduleModel model,int BillTypeFlag,int BillTypeCode)
{
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
return MasterProductScheduleDBHelper.CancelConfirmOperate(model, BillTypeFlag, BillTypeCode, loginUserID);
}
/// <summary>
/// 确认或结单
/// </summary>
/// <param name="model"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool ConfirmOrCompleteMasterProductSchedule(MasterProductScheduleModel model, int OperateType)
{
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
//string loginUserID = "admin";//[待修改]
return MasterProductScheduleDBHelper.ConfirmOrCompleteMasterProductSchedule(model, loginUserID, OperateType);
}
