/// <summary>
/// 更新BOM和子件信息
/// </summary>
/// <param name="model"></param>
/// <param name="UpdateID"></param>
/// <returns></returns>
public static bool UpdateManufactureTaskInfo(ManufactureTaskModel model, Hashtable ht, string UpdateID)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
try
{
bool succ = false;
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
LogInfoModel logModel = InitLogInfo(model.TaskNo, 0);
logModel.Element = ConstUtil.LOG_PROCESS_UPDATE;
succ = ManufactureTaskDBHelper.UpdateManufactureTaskInfo(model,ht, loginUserID, UpdateID);
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>
/// <param name="companycd"></param>
/// <returns></returns>
public static DataTable GetMTList(ManufactureTaskModel model)
{
string sql = "select ID,TaskNo,Subject as Title,ISNULL(CONVERT(VARCHAR(10),CreateDate,21),'') AS CreateDate from officedba.ManufactureTask where CompanyCD='" + model.CompanyCD + "' and BillStatus=2";
SqlCommand comm = new SqlCommand();
//添加公司代码参数
if (!string.IsNullOrEmpty(model.TaskNo))
{
sql += " and TaskNo like '%'+ @TaskNo +'%'";
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskNo", model.TaskNo));
}
if (!string.IsNullOrEmpty(model.Subject))
{
sql += " and Subject like '%'+ @Subject +'%'";
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Subject", model.Subject));
}
comm.CommandText = sql;
return SqlHelper.ExecuteSearch(comm);
}
/// <summary>
/// 获取生产任务单
/// </summary>
/// <param name="companycd"></param>
/// <returns></returns>
public static DataTable GetMTList(ManufactureTaskModel model)
{
try
{
return StorageInProcessDBHelper.GetMTList(model);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 生产任务单详细信息
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetTaskInfo(ManufactureTaskModel model)
{
try
{
return ManufactureTaskDBHelper.GetTaskInfo(model);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 确认或结单
/// </summary>
/// <param name="model"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool ConfirmOrCompleteManufactureTask(ManufactureTaskModel model, int OperateType)
{
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
//string loginUserID = "admin";//[待修改]
return ManufactureTaskDBHelper.ConfirmOrCompleteManufactureTask(model, loginUserID, OperateType);
}
/// <summary>
/// 取消确认
/// </summary>
/// <param name="model"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool CancelConfirmTask(ManufactureTaskModel model, int BillTypeFlag, int BillTypeCode)
{
string loginUserID = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).UserID;
return ManufactureTaskDBHelper.CancelConfirmOperate(model, BillTypeFlag, BillTypeCode, loginUserID);
}
/// <summary>
/// 生产任务单详细信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetTaskInfo(ManufactureTaskModel 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.TaskNo,a.FromType,a.Subject,a.Principal,g.EmployeeName as PricipalReal,a.DocumentURL,");
infoSql.AppendLine(" a.DeptID,f.DeptName,a.TaskType,a.ManufactureType,Convert(numeric(16,"+userInfo.SelPoint+"),a.CountTotal) as CountTotal ,");
infoSql.AppendLine(" case when a.ManufactureType=0 then '普通' when a.ManufactureType=1 then '返修' when a.ManufactureType=2 then '拆件' end as strManufactureType,");
infoSql.AppendLine(" case when a.FromType=0 then '无来源' when a.FromType=1 then '主生产计划' end as strFromType,");
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.Creator,b.EmployeeName as CreatorReal,isnull( CONVERT(CHAR(10), a.CreateDate, 23),'') as CreateDate,");
infoSql.AppendLine(" a.Confirmor,c.EmployeeName as ConfirmorReal,isnull( CONVERT(CHAR(10), a.ConfirmDate, 23),'') as ConfirmDate,");
infoSql.AppendLine(" a.Closer,d.EmployeeName as CloserReal,isnull( CONVERT(CHAR(10), a.CloseDate, 23),'') as CloseDate,a.Remark,");
infoSql.AppendLine(" a.ExtField1,a.ExtField2,a.ExtField3,a.ExtField4,a.ExtField5,a.ExtField6,a.ExtField7,a.ExtField8,a.ExtField9,a.ExtField10,");
infoSql.AppendLine(" isnull( CONVERT(CHAR(10), a.ModifiedDate, 23),'') as ModifiedDate,a.ModifiedUserID,h.EmployeeName as ModifiedUserReal,a.ProjectID,p.ProjectName ");
infoSql.AppendLine(" from officedba.ManufactureTask a");
infoSql.AppendLine(" left join officedba.EmployeeInfo as b on a.Creator=b.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo as c on a.Confirmor=c.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo as d on a.Closer=d.ID");
infoSql.AppendLine(" left join officedba.EmployeeInfo as g on a.Principal=g.ID");
infoSql.AppendLine(" left join officedba.DeptInfo as f on a.DeptID=f.ID");
infoSql.AppendLine(" left join officedba.UserInfo as i on a.ModifiedUserID=i.UserID");
infoSql.AppendLine(" left join officedba.EmployeeInfo h on i.EmployeeID=h.ID");
infoSql.AppendLine(" left join officedba.ProjectInfo p on a.ProjectID=p.ID");
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>
/// <returns>DataTable</returns>
public static DataTable GetManufactureTaskListBycondition(ManufactureTaskModel model, int FlowStatus, int BillTypeFlag, int BillTypeCode, string CreateDate, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
return ManufactureTaskDBHelper.GetManufactureTaskListBycondition(model, FlowStatus, BillTypeFlag, BillTypeCode, CreateDate, EFIndex, EFDesc, pageIndex, pageCount, OrderBy, ref totalCount);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 生产任务单插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertManufactureTask(ManufactureTaskModel model, Hashtable htExtAttr, string loginUserID, out string ID)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
ArrayList listADD = new ArrayList();
bool result = false;
//#region 传参
try
{
#region 生产任务单添加SQL语句
StringBuilder sqlTask = new StringBuilder();
sqlTask.AppendLine("INSERT INTO officedba.ManufactureTask ");
sqlTask.AppendLine(" (CompanyCD ");
sqlTask.AppendLine(" ,TaskNo ");
sqlTask.AppendLine(" ,Subject ");
sqlTask.AppendLine(" ,Principal ");
sqlTask.AppendLine(" ,DeptID ");
sqlTask.AppendLine(" ,FromType ");
sqlTask.AppendLine(" ,ManufactureType ");
sqlTask.AppendLine(" ,CountTotal ");
sqlTask.AppendLine(" ,BillStatus ");
sqlTask.AppendLine(" ,Creator ");
sqlTask.AppendLine(" ,CreateDate ");
sqlTask.AppendLine(" ,Remark ");
sqlTask.AppendLine(" ,DocumentURL ");
sqlTask.AppendLine(" ,ProjectID ");
sqlTask.AppendLine(" ,ModifiedDate ");
sqlTask.AppendLine(" ,ModifiedUserID) ");
sqlTask.AppendLine(" VALUES ");
sqlTask.AppendLine(" (@CompanyCD ");
sqlTask.AppendLine(" ,@TaskNo ");
sqlTask.AppendLine(" ,@Subject ");
sqlTask.AppendLine(" ,@Principal ");
sqlTask.AppendLine(" ,@DeptID ");
sqlTask.AppendLine(" ,@FromType ");
sqlTask.AppendLine(" ,@ManufactureType ");
sqlTask.AppendLine(" ,@CountTotal ");
sqlTask.AppendLine(" ,@BillStatus ");
sqlTask.AppendLine(" ,@Creator ");
sqlTask.AppendLine(" ,@CreateDate ");
sqlTask.AppendLine(" ,@Remark ");
sqlTask.AppendLine(" ,@DocumentURL ");
sqlTask.AppendLine(" ,@ProjectID ");
sqlTask.AppendLine(" ,getdate() ");
sqlTask.AppendLine(" ,'"+loginUserID+"') ");
sqlTask.AppendLine("set @ID=@@IDENTITY");
SqlCommand comm = new SqlCommand();
comm.CommandText = sqlTask.ToString();
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@TaskNo", model.TaskNo));
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("@FromType", model.FromType));
comm.Parameters.Add(SqlHelper.GetParameter("@ManufactureType", model.ManufactureType));
comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@BillStatus", model.BillStatus));
comm.Parameters.Add(SqlHelper.GetParameter("@Creator", model.Creator));
comm.Parameters.Add(SqlHelper.GetParameter("@CreateDate", model.CreateDate));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@DocumentURL", model.DocumentURL));
comm.Parameters.Add(SqlHelper.GetParameter("@ProjectID", model.ProjectID));
comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int));
listADD.Add(comm);
#endregion
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
#region 生产任务单明细信息添加SQL语句
if (!String.IsNullOrEmpty(model.DetSortNo) && !String.IsNullOrEmpty(model.DetProductID) && !String.IsNullOrEmpty(model.DetProductCount) && !String.IsNullOrEmpty(model.DetStartDate) && !String.IsNullOrEmpty(model.DetEndDate))
{
//SortNo,ProductID,ProductCount,BomID,RouteID,StartDate,EndDate,FromType,FromBillID,FrombIllNo,FromLineNo
string[] dtSortNo = model.DetSortNo.Split(',');
string[] dtProductID = model.DetProductID.Split(',');
string[] dtProductCount = model.DetProductCount.Split(',');
string[] dtBomID = model.DetBomID.Split(',');
string[] dtRouteID = model.DetRouteID.Split(',');
string[] dtStartDate = model.DetStartDate.Split(',');
string[] dtEndDate = model.DetEndDate.Split(',');
string[] dtFromType = model.DetFromType.Split(',');
string[] dtFromBillID = model.DetFromBillID.Split(',');
string[] dtFromBillNo = model.DetFromBillNo.Split(',');
string[] dtFromLineNo = model.DetFromLineNo.Split(',');
string[] dtUsedUnitID = model.DetUsedUnitID.Split(',');
string[] dtUsedUnitCount = model.DetUsedUnitCount.Split(',');
string[] dtExRate = model.DetExRate.Split(',');
//页面上这些字段都是必填，数组的长度必须是相同的
if (dtProductID.Length >= 1)
{
for (int i = 0; i < dtProductID.Length; i++)
{
System.Text.StringBuilder cmdsql = new System.Text.StringBuilder();
cmdsql.AppendLine("INSERT INTO officedba.ManufactureTaskDetail");
cmdsql.AppendLine(" (CompanyCD");
cmdsql.AppendLine(" ,TaskNo");
if (dtSortNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtSortNo[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,SortNo");
}
}
if (dtProductID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductID[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,ProductID");
}
}
if (dtProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductCount[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,ProductCount");
}
}
if (userInfo.IsMoreUnit)
{
cmdsql.AppendLine(" ,UsedUnitID");
cmdsql.AppendLine(" ,UsedUnitCount");
cmdsql.AppendLine(" ,ExRate");
}
if (dtBomID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtBomID[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,BomID");
}
}
if (dtRouteID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtRouteID[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,RouteID");
}
}
if (dtStartDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtStartDate[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,StartDate");
}
}
if (dtEndDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtEndDate[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,EndDate");
}
}
cmdsql.AppendLine(" ,FromType");
if (dtFromBillID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillID[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,FromBillID");
}
}
if (dtFromBillNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillNo[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,FromBillNo");
}
}
if (dtFromLineNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromLineNo[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,FromLineNo");
}
}
cmdsql.AppendLine(" ,ModifiedDate");
cmdsql.AppendLine(" ,ModifiedUserID)");
cmdsql.AppendLine(" VALUES");
cmdsql.AppendLine(" (@CompanyCD");
cmdsql.AppendLine(" ,@TaskNo");
if (dtSortNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtSortNo[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@SortNo");
}
}
if (dtProductID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductID[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@ProductID");
}
}
if (dtProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductCount[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@ProductCount");
}
}
if (userInfo.IsMoreUnit)
{
cmdsql.AppendLine(" ,@UsedUnitID");
cmdsql.AppendLine(" ,@UsedUnitCount");
cmdsql.AppendLine(" ,@ExRate");
}
if (dtBomID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtBomID[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@BomID");
}
}
if (dtRouteID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtRouteID[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@RouteID");
}
}
if (dtStartDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtStartDate[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@StartDate");
}
}
if (dtEndDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtEndDate[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@EndDate");
}
}
cmdsql.AppendLine(" ,@FromType");
if (dtFromBillID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillID[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@FromBillID ");
}
}
if (dtFromBillNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillNo[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@FromBillNo");
}
}
if (dtFromLineNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromLineNo[i].ToString().Trim()))
{
cmdsql.AppendLine(" ,@FromLineNo");
}
}
cmdsql.AppendLine(" ,getdate() ");
cmdsql.AppendLine(" ,'"+loginUserID+"')");
SqlCommand comms = new SqlCommand();
comms.CommandText = cmdsql.ToString();
comms.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comms.Parameters.Add(SqlHelper.GetParameter("@TaskNo", model.TaskNo));
if (dtSortNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtSortNo[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@SortNo", dtSortNo[i].ToString()));
}
}
if (dtProductID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductID[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@ProductID", dtProductID[i].ToString()));
}
}
if (dtProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductCount[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@ProductCount", dtProductCount[i].ToString()));
}
}
if (userInfo.IsMoreUnit)
{
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", dtUsedUnitID[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", dtUsedUnitCount[i].ToString()));
comms.Parameters.Add(SqlHelper.GetParameter("@ExRate", dtExRate[i].ToString()));
}
if (dtBomID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtBomID[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@BomID", dtBomID[i].ToString()));
}
}
if (dtRouteID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtRouteID[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@RouteID", dtRouteID[i].ToString()));
}
}
if (dtStartDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtStartDate[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@StartDate", dtStartDate[i].ToString()));
}
}
if (dtEndDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtEndDate[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@EndDate", dtEndDate[i].ToString()));
}
}
comms.Parameters.Add(SqlHelper.GetParameter("@FromType", dtFromType[i].ToString()));
if (dtFromBillID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillID[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@FromBillID", dtFromBillID[i].ToString()));
}
}
if (dtFromBillNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillNo[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", dtFromBillNo[i].ToString()));
}
}
if (dtFromLineNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromLineNo[i].ToString().Trim()))
{
comms.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", dtFromLineNo[i].ToString()));
}
}
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;
}
}
/// <summary>
/// 修改生产任务单和生产任务单明细信息
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <param name="UpdateID"></param>
/// <returns></returns>
public static bool UpdateManufactureTaskInfo(ManufactureTaskModel model, Hashtable htExtAttr, string loginUserID, string UpdateID)
{
//获取登陆用户ID
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
ArrayList listADD = new ArrayList();
if (model.ID <= 0)
{
return false;
}
#region 生产任务单修改SQL语句
StringBuilder sqlDet = new StringBuilder();
sqlDet.AppendLine("UPDATE officedba.ManufactureTask");
sqlDet.AppendLine(" SET CompanyCD = @CompanyCD");
sqlDet.AppendLine(" ,TaskNo = @TaskNo");
sqlDet.AppendLine(" ,FromType = @FromType");
sqlDet.AppendLine(" ,Subject = @Subject");
sqlDet.AppendLine(" ,Principal = @Principal");
sqlDet.AppendLine(" ,DeptID = @DeptID");
sqlDet.AppendLine(" ,ManufactureType = @ManufactureType");
sqlDet.AppendLine(" ,CountTotal = @CountTotal");
sqlDet.AppendLine(" ,Remark = @Remark");
sqlDet.AppendLine(" ,DocumentURL = @DocumentURL");
sqlDet.AppendLine(" ,ProjectID = @ProjectID");
sqlDet.AppendLine(" ,ModifiedDate = getdate()");
sqlDet.AppendLine(" ,ModifiedUserID = '"+loginUserID+"'");
sqlDet.AppendLine(" WHERE CompanyCD=@CompanyCD and ID=@ID");
SqlCommand comm = new SqlCommand();
comm.CommandText = sqlDet.ToString();
comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
comm.Parameters.Add(SqlHelper.GetParameter("@TaskNo", model.TaskNo));
comm.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
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("@ManufactureType", model.ManufactureType));
comm.Parameters.Add(SqlHelper.GetParameter("@CountTotal", model.CountTotal));
comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark));
comm.Parameters.Add(SqlHelper.GetParameter("@DocumentURL", model.DocumentURL));
comm.Parameters.Add(SqlHelper.GetParameter("@ProjectID", model.ProjectID));
listADD.Add(comm);
#endregion
#region 拓展属性
SqlCommand cmd = new SqlCommand();
GetExtAttrCmd(model, htExtAttr, cmd);
if (htExtAttr.Count > 0)
listADD.Add(cmd);
#endregion
#region 生产任务单元明细信息更新语句
//1.先删除不在生产任务单明细中的
//2.更新明细中的ID
//3.添加其它明细
#region 先删除不在生产任务单明细中的
if (!string.IsNullOrEmpty(UpdateID))
{
StringBuilder sqlDel = new StringBuilder();
sqlDel.AppendLine("delete from officedba.ManufactureTaskDetail where CompanyCD=@CompanyCD and TaskNo=@TaskNo and ID not in(" + UpdateID + ")");
SqlCommand commDel = new SqlCommand();
commDel.CommandText = sqlDel.ToString();
commDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commDel.Parameters.Add(SqlHelper.GetParameter("@TaskNo", model.TaskNo));
listADD.Add(commDel);
}
else
{
StringBuilder sqlDel = new StringBuilder();
sqlDel.AppendLine("delete from officedba.ManufactureTaskDetail where CompanyCD=@CompanyCD and TaskNo=@TaskNo");
SqlCommand commDel = new SqlCommand();
commDel.CommandText = sqlDel.ToString();
commDel.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commDel.Parameters.Add(SqlHelper.GetParameter("@TaskNo", model.TaskNo));
listADD.Add(commDel);
}
#endregion
#region 添加或更新操作
string[] updateID = UpdateID.Split(',');
if (!string.IsNullOrEmpty(UpdateID) && updateID.Length > 0)
{
if (!String.IsNullOrEmpty(model.DetSortNo) && !String.IsNullOrEmpty(model.DetProductID) && !String.IsNullOrEmpty(model.DetProductCount) && !String.IsNullOrEmpty(model.DetStartDate) && !String.IsNullOrEmpty(model.DetEndDate))
{
//SortNo,ProductID,ProductCount,BomID,RouteID,StartDate,EndDate,FromType,FromBillID,FrombIllNo,FromLineNo
string[] dtSortNo = model.DetSortNo.Split(',');
string[] dtProductID = model.DetProductID.Split(',');
string[] dtProductCount = model.DetProductCount.Split(',');
string[] dtBomID = model.DetBomID.Split(',');
string[] dtRouteID = model.DetRouteID.Split(',');
string[] dtStartDate = model.DetStartDate.Split(',');
string[] dtEndDate = model.DetEndDate.Split(',');
string[] dtFromType = model.DetFromType.Split(',');
string[] dtFromBillID = model.DetFromBillID.Split(',');
string[] dtFromBillNo = model.DetFromBillNo.Split(',');
string[] dtFromLineNo = model.DetFromLineNo.Split(',');
string[] dtUsedUnitID = model.DetUsedUnitID.Split(',');
string[] dtUsedUnitCount = model.DetUsedUnitCount.Split(',');
string[] dtExRate = model.DetExRate.Split(',');
for (int i = 0; i < updateID.Length; i++)
{
int intUpdateID = int.Parse(updateID[i].ToString());
if (intUpdateID > 0)
{
#region 更新MRP明细中的ID
StringBuilder sqlEdit = new StringBuilder();
sqlEdit.AppendLine("UPDATE officedba.ManufactureTaskDetail ");
sqlEdit.AppendLine("SET ");
if (dtSortNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtSortNo[i].ToString().Trim()))
{
sqlEdit.AppendLine(" SortNo = @SortNo ");
}
}
if (dtProductID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductID[i].ToString().Trim()))
{
sqlEdit.AppendLine(" ,ProductID = @ProductID ");
}
}
if (dtProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductCount[i].ToString().Trim()))
{
sqlEdit.AppendLine(" ,ProductCount = @ProductCount ");
}
}
if (userInfo.IsMoreUnit)
{
sqlEdit.AppendLine(" ,UsedUnitID = @UsedUnitID ");
sqlEdit.AppendLine(" ,UsedUnitCount = @UsedUnitCount ");
sqlEdit.AppendLine(" ,ExRate = @ExRate ");
}
if (dtBomID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtBomID[i].ToString().Trim()))
{
sqlEdit.AppendLine(" ,BomID = @BomID ");
}
}
else
{
sqlEdit.AppendLine(" ,BomID = null ");
}
if (dtRouteID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtRouteID[i].ToString().Trim()))
{
sqlEdit.AppendLine(" ,RouteID = @RouteID ");
}
}
else
{
sqlEdit.AppendLine(" ,RouteID = null ");
}
if (dtStartDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtStartDate[i].ToString().Trim()))
{
sqlEdit.AppendLine(" ,StartDate = @StartDate ");
}
}
if (dtEndDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtEndDate[i].ToString().Trim()))
{
sqlEdit.AppendLine(" ,EndDate = @EndDate ");
}
}
sqlEdit.AppendLine(" ,FromType = @FromType ");
if (dtFromBillID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillID[i].ToString().Trim()))
{
sqlEdit.AppendLine(" ,FromBillID = @FromBillID ");
}
}
if (dtFromBillNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillNo[i].ToString().Trim()))
{
sqlEdit.AppendLine(" ,FromBillNo = @FromBillNo ");
}
}
if (dtFromLineNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromLineNo[i].ToString().Trim()))
{
sqlEdit.AppendLine(" ,FromLineNo = @FromLineNo ");
}
}
sqlEdit.AppendLine(" ,ModifiedDate = getdate() ");
sqlEdit.AppendLine(" ,ModifiedUserID = '"+loginUserID+"' ");
sqlEdit.AppendLine(" WHERE CompanyCD=@CompanyCD and ID=@ID ");
SqlCommand commEdit = new SqlCommand();
commEdit.CommandText = sqlEdit.ToString();
if (dtSortNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtSortNo[i].ToString().Trim()))
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@SortNo", dtSortNo[i].ToString()));
}
}
if (dtProductID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductID[i].ToString().Trim()))
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@ProductID", dtProductID[i].ToString()));
}
}
if (dtProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductCount[i].ToString().Trim()))
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@ProductCount", dtProductCount[i].ToString()));
}
}
if (userInfo.IsMoreUnit)
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", dtUsedUnitID[i].ToString()));
commEdit.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", dtUsedUnitCount[i].ToString()));
commEdit.Parameters.Add(SqlHelper.GetParameter("@ExRate", dtExRate[i].ToString()));
}
if (dtBomID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtBomID[i].ToString().Trim()))
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@BomID", dtBomID[i].ToString()));
}
}
if (dtRouteID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtRouteID[i].ToString().Trim()))
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@RouteID", dtRouteID[i].ToString()));
}
}
if (dtStartDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtStartDate[i].ToString().Trim()))
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@StartDate", dtStartDate[i].ToString()));
}
}
if (dtEndDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtEndDate[i].ToString().Trim()))
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@EndDate", dtEndDate[i].ToString()));
}
}
commEdit.Parameters.Add(SqlHelper.GetParameter("@FromType", dtFromType[i].ToString()));
if (dtFromBillID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillID[i].ToString().Trim()))
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@FromBillID", dtFromBillID[i].ToString()));
}
}
if (dtFromBillNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillNo[i].ToString().Trim()))
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", dtFromBillNo[i].ToString()));
}
}
if (dtFromLineNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromLineNo[i].ToString().Trim()))
{
commEdit.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", dtFromLineNo[i].ToString()));
}
}
commEdit.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commEdit.Parameters.Add(SqlHelper.GetParameter("@ID", intUpdateID));
listADD.Add(commEdit);
#endregion
}
else
{
#region 添加MRP明细中的ID
//页面上这些字段都是必填，数组的长度必须是相同的
System.Text.StringBuilder sqlIn = new System.Text.StringBuilder();
sqlIn.AppendLine("INSERT INTO officedba.ManufactureTaskDetail");
sqlIn.AppendLine(" (CompanyCD");
sqlIn.AppendLine(" ,TaskNo");
if (dtSortNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtSortNo[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,SortNo");
}
}
if (dtProductID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductID[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,ProductID");
}
}
if (dtProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductCount[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,ProductCount");
}
}
if (userInfo.IsMoreUnit)
{
sqlIn.AppendLine(" ,UsedUnitID");
sqlIn.AppendLine(" ,UsedUnitCount");
sqlIn.AppendLine(" ,ExRate");
}
if (dtBomID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtBomID[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,BomID");
}
}
if (dtRouteID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtRouteID[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,RouteID");
}
}
if (dtStartDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtStartDate[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,StartDate");
}
}
if (dtEndDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtEndDate[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,EndDate");
}
}
sqlIn.AppendLine(" ,FromType");
if (dtFromBillID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillID[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,FromBillID");
}
}
if (dtFromBillNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillNo[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,FromBillNo");
}
}
if (dtFromLineNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromLineNo[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,FromLineNo");
}
}
sqlIn.AppendLine(" ,ModifiedDate");
sqlIn.AppendLine(" ,ModifiedUserID)");
sqlIn.AppendLine(" VALUES");
sqlIn.AppendLine(" (@CompanyCD");
sqlIn.AppendLine(" ,@TaskNo");
if (dtSortNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtSortNo[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,@SortNo");
}
}
if (dtProductID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductID[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,@ProductID");
}
}
if (dtProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductCount[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,@ProductCount");
}
}
if (userInfo.IsMoreUnit)
{
sqlIn.AppendLine(" ,@UsedUnitID");
sqlIn.AppendLine(" ,@UsedUnitCount");
sqlIn.AppendLine(" ,@ExRate");
}
if (dtBomID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtBomID[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,@BomID");
}
}
if (dtRouteID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtRouteID[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,@RouteID");
}
}
if (dtStartDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtStartDate[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,@StartDate");
}
}
if (dtEndDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtEndDate[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,@EndDate");
}
}
sqlIn.AppendLine(" ,@FromType");
if (dtFromBillID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillID[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,@FromBillID ");
}
}
if (dtFromBillNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillNo[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,@FromBillNo");
}
}
if (dtFromLineNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromLineNo[i].ToString().Trim()))
{
sqlIn.AppendLine(" ,@FromLineNo");
}
}
sqlIn.AppendLine(" ,getdate() ");
sqlIn.AppendLine(" ,'" + loginUserID + "')");
SqlCommand commIn = new SqlCommand();
commIn.CommandText = sqlIn.ToString();
commIn.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commIn.Parameters.Add(SqlHelper.GetParameter("@TaskNo", model.TaskNo));
if (dtSortNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtSortNo[i].ToString().Trim()))
{
commIn.Parameters.Add(SqlHelper.GetParameter("@SortNo", dtSortNo[i].ToString()));
}
}
if (dtProductID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductID[i].ToString().Trim()))
{
commIn.Parameters.Add(SqlHelper.GetParameter("@ProductID", dtProductID[i].ToString()));
}
}
if (dtProductCount[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtProductCount[i].ToString().Trim()))
{
commIn.Parameters.Add(SqlHelper.GetParameter("@ProductCount", dtProductCount[i].ToString()));
}
}
if (userInfo.IsMoreUnit)
{
commIn.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", dtUsedUnitID[i].ToString()));
commIn.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", dtUsedUnitCount[i].ToString()));
commIn.Parameters.Add(SqlHelper.GetParameter("@ExRate", dtExRate[i].ToString()));
}
if (dtBomID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtBomID[i].ToString().Trim()))
{
commIn.Parameters.Add(SqlHelper.GetParameter("@BomID", dtBomID[i].ToString()));
}
}
if (dtRouteID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtRouteID[i].ToString().Trim()))
{
commIn.Parameters.Add(SqlHelper.GetParameter("@RouteID", dtRouteID[i].ToString()));
}
}
if (dtStartDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtStartDate[i].ToString().Trim()))
{
commIn.Parameters.Add(SqlHelper.GetParameter("@StartDate", dtStartDate[i].ToString()));
}
}
if (dtEndDate[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtEndDate[i].ToString().Trim()))
{
commIn.Parameters.Add(SqlHelper.GetParameter("@EndDate", dtEndDate[i].ToString()));
}
}
commIn.Parameters.Add(SqlHelper.GetParameter("@FromType", model.FromType));
if (dtFromBillID[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillID[i].ToString().Trim()))
{
commIn.Parameters.Add(SqlHelper.GetParameter("@FromBillID", dtFromBillID[i].ToString()));
}
}
if (dtFromBillNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromBillNo[i].ToString().Trim()))
{
commIn.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", dtFromBillNo[i].ToString()));
}
}
if (dtFromLineNo[i].ToString().Length > 0)
{
if (!string.IsNullOrEmpty(dtFromLineNo[i].ToString().Trim()))
{
commIn.Parameters.Add(SqlHelper.GetParameter("@FromLineNo", dtFromLineNo[i].ToString()));
}
}
listADD.Add(commIn);
#endregion
}
}
}
}
#endregion
#endregion
return SqlHelper.ExecuteTransWithArrayList(listADD);
}
/// <summary>
/// 扩展属性保存操作
/// </summary>
/// <returns></returns>
private static void GetExtAttrCmd(ManufactureTaskModel model, Hashtable htExtAttr, SqlCommand cmd)
{
try
{
string strSql = string.Empty;
strSql = "UPDATE officedba.ManufactureTask 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 TaskNo = @TaskNo";
cmd.Parameters.AddWithValue("@CompanyCD", model.CompanyCD);
cmd.Parameters.AddWithValue("@TaskNo", model.TaskNo);
cmd.CommandText = strSql;
}
catch (Exception)
{ }
}
/// <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(ManufactureTaskModel model, int BillTypeFlag, int BillTypeCode, string loginUserID)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
ArrayList listADD = new ArrayList();
//#region 传参
try
{
DataTable dbTask = GetTaskInfo(model);
if (dbTask.Rows.Count > 0)
{
int BillStatus = int.Parse(dbTask.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 更新相关地方的数据
//注：单据确认后自动更新对应主生产计划明细中的已下达数量
// 更新对应的物品在分仓存量表（分仓存量表officedba.StorageProduct）中在途量（增加），
// 更新存量时，先根据物品从物品档案表中获取该物品对应的主放仓库ID，再更新分仓存量表中对应的物品+仓库的在途量
DataTable dtTaskDetail = new DataTable();
dtTaskDetail = GetTaskDetailInfo(model);
if (dtTaskDetail.Rows.Count > 0)
{
for (int i = 0; i < dtTaskDetail.Rows.Count; i++)
{
Decimal ProductCount = Decimal.Round(Decimal.Parse(dtTaskDetail.Rows[i]["ProductCount"].ToString()));
if (userInfo.IsMoreUnit)
{
ProductCount = Decimal.Round(Decimal.Parse(dtTaskDetail.Rows[i]["UsedUnitCount"].ToString()));
}
int ProductID = int.Parse(dtTaskDetail.Rows[i]["ProductID"].ToString());
if (!string.IsNullOrEmpty(dtTaskDetail.Rows[i]["FromBillID"].ToString()))
{
int FromBillID = int.Parse(dtTaskDetail.Rows[i]["FromBillID"].ToString());
string FromBillNo = dtTaskDetail.Rows[i]["FromBillNo"].ToString();
if (FromBillID > 0)
{
#region 更新主生产计划中已下达数量语句
//update officedba.MasterProductSchedule set PlanCount=PlanCount+@ProductCount where CompanyCD=@CompanyCD and PlanNo=@FromBillNo and ID=@FromBillID
StringBuilder sqlPlan = new StringBuilder();
sqlPlan.AppendLine("Update officedba.MasterProductScheduleDetail set PlanCount=isnull(PlanCount,0)-@ProductCount where CompanyCD=@CompanyCD and PlanNo=@FromBillNo and ID=@FromBillID");
SqlCommand commPlan = new SqlCommand();
commPlan.CommandText = sqlPlan.ToString();
commPlan.Parameters.Add(SqlHelper.GetParameter("@ProductCount", ProductCount));
commPlan.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commPlan.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", FromBillNo));
commPlan.Parameters.Add(SqlHelper.GetParameter("@FromBillID", FromBillID));
listADD.Add(commPlan);
#endregion
}
}
#region 更新分仓存量表里的在途量
//Update officedba.StorageProduct Set RoadCount=RoadCount+@ProductCount where CompanyCD=@CompanyCD and ProductID=@ProductID and StorageID=(select StorageID from officedba.ProductInfo where CompanyCD=@CompanyCD and ID=@ProductID)
StringBuilder sqlStoPro = new StringBuilder();
sqlStoPro.AppendLine("Update officedba.StorageProduct Set RoadCount=isnull(RoadCount,0)-@ProductCount where CompanyCD=@CompanyCD and ProductID=@ProductID and StorageID=(select StorageID from officedba.ProductInfo where CompanyCD=@CompanyCD and ID=@ProductID)");
SqlCommand commStoPro = new SqlCommand();
commStoPro.CommandText = sqlStoPro.ToString();
commStoPro.Parameters.Add(SqlHelper.GetParameter("@ProductCount", ProductCount));
commStoPro.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commStoPro.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID));
listADD.Add(commStoPro);
#endregion
}
}
#endregion
StringBuilder sqlConfirm = new StringBuilder();
sqlConfirm.AppendLine(" UPDATE officedba.ManufactureTask SET");
sqlConfirm.AppendLine(" Confirmor = null,");
sqlConfirm.AppendLine(" ConfirmDate = null,");
sqlConfirm.AppendLine(" ModifiedDate = getdate(),");
sqlConfirm.AppendLine(" BillStatus = 1,");
sqlConfirm.AppendLine(" ModifiedUserID = '" + loginUserID + "'");
sqlConfirm.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand commConfirm = new SqlCommand();
commConfirm.CommandText = sqlConfirm.ToString();
commConfirm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
commConfirm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
listADD.Add(commConfirm);
#endregion
}
}
return SqlHelper.ExecuteTransWithArrayList(listADD);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 确认或结单
/// </summary>
/// <param name="model"></param>
/// <param name="loginUserID"></param>
/// <param name="isConfirm"></param>
/// <returns></returns>
public static bool ConfirmOrCompleteManufactureTask(ManufactureTaskModel model, string loginUserID, int OperateType)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
ArrayList listADD = new ArrayList();
//1：确认 2:结单 3:取消结单
if (OperateType == 1)
{
DataTable dbTask = GetTaskInfo(model);
if (dbTask.Rows.Count > 0)
{
int BillStatus = int.Parse(dbTask.Rows[0]["BillStatus"].ToString());
if (BillStatus == 1)
{
#region 更新相关地方的数据
//注：单据确认后自动更新对应主生产计划明细中的已下达数量
// 更新对应的物品在分仓存量表（分仓存量表officedba.StorageProduct）中在途量（增加），
// 更新存量时，先根据物品从物品档案表中获取该物品对应的主放仓库ID，再更新分仓存量表中对应的物品+仓库的在途量
DataTable dtTaskDetail = new DataTable();
dtTaskDetail = GetTaskDetailInfo(model);
if (dtTaskDetail.Rows.Count > 0)
{
for (int i = 0; i < dtTaskDetail.Rows.Count; i++)
{
Decimal ProductCount = Decimal.Round(Decimal.Parse(dtTaskDetail.Rows[i]["ProductCount"].ToString()));
if (userInfo.IsMoreUnit)
{
ProductCount = Decimal.Round(Decimal.Parse(dtTaskDetail.Rows[i]["UsedUnitCount"].ToString()));
}
int ProductID = int.Parse(dtTaskDetail.Rows[i]["ProductID"].ToString());
if (!string.IsNullOrEmpty(dtTaskDetail.Rows[i]["FromBillID"].ToString()))
{
int FromBillID = int.Parse(dtTaskDetail.Rows[i]["FromBillID"].ToString());
string FromBillNo = dtTaskDetail.Rows[i]["FromBillNo"].ToString();
if (FromBillID > 0)
{
#region 更新主生产计划中已下达数量语句
//update officedba.MasterProductSchedule set PlanCount=PlanCount+@ProductCount where CompanyCD=@CompanyCD and PlanNo=@FromBillNo and ID=@FromBillID
StringBuilder sqlPlan = new StringBuilder();
sqlPlan.AppendLine("Update officedba.MasterProductScheduleDetail set PlanCount=isnull(PlanCount,0)+@ProductCount where CompanyCD=@CompanyCD and PlanNo=@FromBillNo and ID=@FromBillID");
SqlCommand commPlan = new SqlCommand();
commPlan.CommandText = sqlPlan.ToString();
commPlan.Parameters.Add(SqlHelper.GetParameter("@ProductCount", ProductCount));
commPlan.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commPlan.Parameters.Add(SqlHelper.GetParameter("@FromBillNo", FromBillNo));
commPlan.Parameters.Add(SqlHelper.GetParameter("@FromBillID", FromBillID));
listADD.Add(commPlan);
#endregion
}
}
#region 更新分仓存量表里的在途量
//Update officedba.StorageProduct Set RoadCount=RoadCount+@ProductCount where CompanyCD=@CompanyCD and ProductID=@ProductID and StorageID=(select StorageID from officedba.ProductInfo where CompanyCD=@CompanyCD and ID=@ProductID)
StringBuilder sqlStoPro = new StringBuilder();
sqlStoPro.AppendLine("Update officedba.StorageProduct Set RoadCount=isnull(RoadCount,0)+@ProductCount where CompanyCD=@CompanyCD and ProductID=@ProductID and StorageID=(select StorageID from officedba.ProductInfo where CompanyCD=@CompanyCD and ID=@ProductID)");
SqlCommand commStoPro = new SqlCommand();
commStoPro.CommandText = sqlStoPro.ToString();
commStoPro.Parameters.Add(SqlHelper.GetParameter("@ProductCount", ProductCount));
commStoPro.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
commStoPro.Parameters.Add(SqlHelper.GetParameter("@ProductID", ProductID));
listADD.Add(commStoPro);
#endregion
}
}
#endregion
#region 更新主表
StringBuilder sqlTask = new StringBuilder();
sqlTask.AppendLine(" UPDATE officedba.ManufactureTask SET");
sqlTask.AppendLine(" Confirmor = @Confirmor,");
sqlTask.AppendLine(" ConfirmDate = @ConfirmDate,");
sqlTask.AppendLine(" ModifiedDate = getdate(),");
sqlTask.AppendLine(" BillStatus = 2,");
sqlTask.AppendLine(" ModifiedUserID = '" + loginUserID + "'");
sqlTask.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand commTask = new SqlCommand();
commTask.CommandText = sqlTask.ToString();
commTask.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
commTask.Parameters.Add(SqlHelper.GetParameter("@Confirmor", model.Confirmor));
commTask.Parameters.Add(SqlHelper.GetParameter("@ConfirmDate", model.ConfirmDate));
commTask.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
listADD.Add(commTask);
#endregion
}
}
}
else if (OperateType == 2)
{
StringBuilder sqlTask = new StringBuilder();
sqlTask.AppendLine(" UPDATE officedba.ManufactureTask SET");
sqlTask.AppendLine(" Closer = @Closer,");
sqlTask.AppendLine(" CloseDate = @CloseDate,");
sqlTask.AppendLine(" BillStatus = 4,");
sqlTask.AppendLine(" ModifiedDate = getdate(),");
sqlTask.AppendLine(" ModifiedUserID = '" + loginUserID + "'");
sqlTask.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand commTask = new SqlCommand();
commTask.CommandText = sqlTask.ToString();
commTask.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
commTask.Parameters.Add(SqlHelper.GetParameter("@Closer", model.Closer));
commTask.Parameters.Add(SqlHelper.GetParameter("@CloseDate", model.CloseDate));
commTask.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
listADD.Add(commTask);
}
else
{
StringBuilder sqlTask = new StringBuilder();
sqlTask.AppendLine(" update officedba.ManufactureTask set Closer=null,CloseDate=null,ModifiedDate=getdate(),BillStatus=2,ModifiedUserID = '" + loginUserID + "'");
sqlTask.AppendLine(" Where CompanyCD=@CompanyCD and ID=@ID");
SqlCommand commTask = new SqlCommand();
commTask.CommandText = sqlTask.ToString();
commTask.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID));
commTask.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD));
listADD.Add(commTask);
}
return SqlHelper.ExecuteTransWithArrayList(listADD);
}
/// <summary>
/// 通过检索条件查询生产任务单信息
/// </summary>
/// <param name="model"></param>
/// <param name="BillTypeFlag"></param>
/// <param name="BillTypeCode"></param>
/// <returns></returns>
public static DataTable GetManufactureTaskListBycondition(ManufactureTaskModel model, int FlowStatus, int BillTypeFlag, int BillTypeCode, string CreateDate, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine("select a.CompanyCD,");
searchSql.AppendLine(" a.ID,");
searchSql.AppendLine(" a.TaskNo,");
searchSql.AppendLine(" a.Subject,");
searchSql.AppendLine(" a.Principal,isnull(b.EmployeeName,'') as PrincipalReal,");
searchSql.AppendLine(" a.DeptID,d.DeptName,");
searchSql.AppendLine(" a.ManufactureType,p.ProjectName,isnull(a.ProjectID,0) as ProjectID,");
searchSql.AppendLine(" case when a.ManufactureType=0 then '普通' when a.ManufactureType=1 then '返修' when a.ManufactureType=2 then '拆件' end as strManufactureType,");
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 e.FlowStatus=1 then '待审批' when e.FlowStatus=2 then '审批中' when e.FlowStatus=3 then '审批通过' when e.FlowStatus=4 then '审批不通过' when e.FlowStatus=5 then '撤消审批' end as strFlowStatus,");
searchSql.AppendLine(" isnull(e.FlowStatus,'0')as FlowStatus,");
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.Creator,isnull(c.EmployeeName,'') as CreatorReal,");
searchSql.AppendLine(" isnull( CONVERT(CHAR(10), a.CreateDate, 23),'') as CreateDate,");
searchSql.AppendLine(" a.BillStatus,a.ModifiedDate ");
searchSql.AppendLine("from officedba.ManufactureTask a");
searchSql.AppendLine("LEFT JOIN officedba.EmployeeInfo b on a.Principal=b.ID ");
searchSql.AppendLine("LEFT JOIN officedba.EmployeeInfo c on a.Creator=c.ID ");
searchSql.AppendLine("LEFT JOIN officedba.DeptInfo d on a.DeptID=d.ID ");
searchSql.AppendLine("LEFT JOIN officedba.ProjectInfo p on a.ProjectID=p.ID ");
searchSql.AppendLine("LEFT JOIN officedba.FlowInstance e ON a.ID=e.BillID ");
searchSql.AppendLine(" and e.BillTypeFlag=@BillTypeFlag");
searchSql.AppendLine(" and e.BillTypeCode=@BillTypeCode");
searchSql.AppendLine(" and e.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("where a.CompanyCD=@CompanyCD");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//BillTypeFlag
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeFlag", BillTypeFlag.ToString()));
//BillTypeCode
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BillTypeCode", BillTypeCode.ToString()));
//单据编号
if (!string.IsNullOrEmpty(model.TaskNo))
{
searchSql.AppendLine("and a.TaskNo like @TaskNo");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TaskNo", "%" + model.TaskNo + "%"));
}
//单据主题
if (!string.IsNullOrEmpty(model.Subject))
{
searchSql.AppendLine(" and a.Subject like @Subject");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Subject", "%" + model.Subject + "%"));
}
//加工类型
if (model.ManufactureType > -1)
{
searchSql.AppendLine(" and a.ManufactureType=@ManufactureType ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ManufactureType", model.ManufactureType.ToString()));
}
//负责人
if (model.Principal > 0)
{
searchSql.AppendLine(" and a.Principal=@Principal ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Principal", model.Principal.ToString()));
}
//部门
if (model.DeptID > 0)
{
searchSql.AppendLine(" and a.DeptID=@DeptID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@DeptID", model.DeptID.ToString()));
}
//单据状态
if (!string.IsNullOrEmpty(model.BillStatus))
{
if (int.Parse(model.BillStatus) > 0)
{
searchSql.AppendLine("and a.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(CreateDate))
{
searchSql.AppendLine(" and CreateDate=@CreateDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CreateDate", CreateDate));
}
if (model.ProjectID > 0)
{
searchSql.AppendLine(" and a.ProjectID=@ProjectID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProjectID", model.ProjectID.ToString()));
}
if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc))
{
if (int.Parse(EFIndex) > 0)
{
searchSql.AppendLine(" and a.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 GetTaskDetailInfo(ManufactureTaskModel model)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
#region 查询语句
//查询SQL拼写
StringBuilder detSql = new StringBuilder();
detSql.AppendLine("select * from (");
detSql.AppendLine(" select a.CompanyCD,a.ID as DetailID,a.TaskNo,a.SortNo,a.ProductID,b.ProdNo,b.ProductName,");
detSql.AppendLine(" b.Specification,b.UnitID,c.CodeName as UnitName,a.UsedUnitID,f.CodeName as UsedUnitName,Convert(numeric(14," + userInfo.SelPoint + "),a.UsedUnitCount) as UsedUnitCount,");
detSql.AppendLine(" Convert(numeric(14," + userInfo.SelPoint+ "),a.ProductCount) as ProductCount,Convert(numeric(14,"+userInfo.SelPoint+"),a.ProductedCount) as ProductedCount,");
detSql.AppendLine(" a.BomID,d.BomNo,a.RouteID,e.RouteNo,");
detSql.AppendLine(" isnull( CONVERT(CHAR(10), a.StartDate, 23),'') as StartDate,");
detSql.AppendLine(" isnull( CONVERT(CHAR(10), a.EndDate, 23),'') as EndDate,");
detSql.AppendLine(" a.FromType,a.FromBillID,a.FromBillNo,a.FromLineNo,Convert(numeric(14," + userInfo.SelPoint+ "),a.InCount) as InCount,Convert(numeric(14,"+userInfo.SelPoint+"),a.ApplyCheckCount) as ApplyCheckCount,Convert(numeric(14,"+userInfo.SelPoint+"),a.CheckedCount) as CheckedCount,Convert(numeric(14,"+userInfo.SelPoint+"),a.PassCount) as PassCount,Convert(numeric(14,"+userInfo.SelPoint+"),a.NotPassCount) as NotPassCount");
detSql.AppendLine(" from officedba.ManufactureTaskDetail a");
detSql.AppendLine(" left join officedba.ProductInfo b on b.ID=a.ProductID");
detSql.AppendLine(" left join officedba.CodeUnitType c on b.UnitID=c.ID");
detSql.AppendLine(" left join officedba.CodeUnitType f on a.UsedUnitID=f.ID");
detSql.AppendLine(" left outer join officedba.Bom d on a.BomID=d.ID");
detSql.AppendLine(" left outer join officedba.TechnicsRouting e on a.RouteID=e.ID");
detSql.AppendLine(" where a.CompanyCD=@CompanyCD and TaskNo = (select top 1 TaskNo from officedba.ManufactureTask where ID=@ID)");
detSql.AppendLine(") as info");
detSql.AppendLine("where CompanyCD=@CompanyCD ");
#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);
}
