/// <summary> /// BOM插入 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool InsertBom(BomModel model, 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.BomNo,0); logModel.Element = ConstUtil.LOG_PROCESS_INSERT; succ = BomDBHelper.InsertBom(model, 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> /// <param name="ParentCode">上级编码</param> /// <returns>大于0还有子节点,否则无子节点</returns> public static int ChildrenCount(BomModel model) { try { return BomDBHelper.ChildrenCount(model); } catch (Exception ex) { throw ex; } }
/// <summary> /// 加载BOM树 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetBomTree(BomModel model) { try { return BomDBHelper.GetBomTree(model); } catch (System.Exception ex) { throw ex; } }
/* 001 父件A(438), 子件a1(439),a2(440) 002 父件a1(439),子件A(438),a2(440) 建第二个BOM保存时需要做验证,避免物料计算时死循环. select count(*)as invalids from officedba.Bom a left join officedba.BomDetail b on a.BomNo=b.BomNo where a.CompanyCD='T0004' and b.ProductID=439 and a.ProductID in(438,440) */ public static int CountInvalid(BomModel model) { string sql = "select count(*)as invalids from officedba.Bom a left join officedba.BomDetail b on a.BomNo=b.BomNo where a.CompanyCD=@CompanyCD and b.ProductID=@ProductID and a.ProductID in("+model.DetProductID+")"; SqlParameter[] parms = new SqlParameter[2]; parms[0] = SqlHelper.GetParameter("@ProductID", model.ProductID); parms[1] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD); object obj = SqlHelper.ExecuteScalar(sql, parms); if (obj == null) { return 0; } else { return Convert.ToInt32(obj); } }
/// <summary> /// BOM控件查询Bom信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetBomControlList(BomModel model, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select * from ("); searchSql.AppendLine(" select a.CompanyCD,a.ID,a.BomNo,a.parentNo,isnull(c.BomNo,'') as ParentBom,a.Type,a.RouteID,a.UsedStatus,isnull(b.RouteName,'') as RouteName,d.ProductName,a.ModifiedDate "); searchSql.AppendLine(" from officedba.Bom a"); searchSql.AppendLine(" left outer join officedba.TechnicsRouting b on b.ID=a.RouteID"); searchSql.AppendLine(" left outer join officedba.Bom c on c.ID=a.ParentNo"); searchSql.AppendLine(" left join officedba.ProductInfo d on a.ProductID=d.ID"); if (model.ID > 0) { searchSql.AppendLine(" where a.ID not in (select ID from officedba.Bom where CompanyCD=@CompanyCD and ParentNo=@ID) and a.ID<>@ID"); } if (!string.IsNullOrEmpty(model.ProductID)) { if (int.Parse(model.ProductID) > 0) { searchSql.AppendLine(" where a.ProductID= @ProductID"); } } searchSql.AppendLine(")as info where CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); if (model.ID > 0) { comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString())); } if (!string.IsNullOrEmpty(model.ProductID)) { if (int.Parse(model.ProductID) > 0) { comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductID", model.ProductID)); } } if (!string.IsNullOrEmpty(model.UsedStatus)) { if (int.Parse(model.UsedStatus) > -1) { //启用状态 searchSql.AppendLine(" and UsedStatus=@UsedStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus)); } } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 通过检索条件查询Bom信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetBomListBycondition(BomModel model, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select * from ("); searchSql.AppendLine(" select a.CompanyCD,a.ID,a.BomNo,e.ProductName,a.parentNo,isnull(c.BomNo,'') as ParentBom,a.Type,a.RouteID,a.UsedStatus,isnull(b.RouteName,'') as RouteName,"); searchSql.AppendLine(" case when a.Type=0 then '工程Bom' when a.Type=1 then '生产Bom' when a.Type=2 then '销售Bom' when a.Type=3 then '成本Bom' end as strType,"); searchSql.AppendLine(" case when a.UsedStatus=0 then '停用' when a.UsedStatus=1 then '启用' end as strUsedStatus,a.ModifiedDate "); searchSql.AppendLine(" from officedba.Bom a"); searchSql.AppendLine(" left join officedba.TechnicsRouting b on b.ID=a.RouteID"); searchSql.AppendLine(" left join officedba.Bom c on c.ID=a.ParentNo"); searchSql.AppendLine(" left join officedba.ProductInfo e on a.ProductID=e.ID"); searchSql.AppendLine(")as info where CompanyCD=@CompanyCD"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); //Bom编码 if (!string.IsNullOrEmpty(model.BomNo)) { searchSql.AppendLine(" and BomNo like @BomNo "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BomNo", "%" + model.BomNo + "%")); } //Bom类型 if (!string.IsNullOrEmpty(model.Type)) { if (int.Parse(model.Type) > -1) { searchSql.AppendLine(" and Type=@Type "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Type", model.Type)); } } //工艺路线 if (model.RouteID > 0) { searchSql.AppendLine(" and RouteID=@RouteID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@RouteID", model.RouteID.ToString())); } if (!string.IsNullOrEmpty(model.UsedStatus)) { if (int.Parse(model.UsedStatus) > -1) { //启用状态 searchSql.AppendLine(" and UsedStatus=@UsedStatus "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@UsedStatus", model.UsedStatus)); } } //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, OrderBy, ref totalCount); }
/// <summary> /// 修改BOM和子件信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool UpdateBomInfo(BomModel model, string loginUserID, string UpdateID) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; //获取登陆用户ID ArrayList listADD = new ArrayList(); if (model.ID <= 0) { return false; } #region BOM修改SQL语句 StringBuilder sqlBom = new StringBuilder(); sqlBom.AppendLine("update officedba.Bom set BomNo=@BomNo"); sqlBom.AppendLine(" ,Subject=@Subject"); sqlBom.AppendLine(" ,ParentNo=@ParentNo"); sqlBom.AppendLine(" ,Type=@Type"); sqlBom.AppendLine(" ,Verson=@Verson"); sqlBom.AppendLine(" ,RouteID=@RouteID"); sqlBom.AppendLine(" ,Remark=@Remark"); sqlBom.AppendLine(" ,ProductID=@ProductID"); sqlBom.AppendLine(" ,UnitID=@UnitID"); if (userInfo.IsMoreUnit) { sqlBom.AppendLine(" ,UsedUnitID=@UsedUnitID"); sqlBom.AppendLine(" ,ExRate=@ExRate"); } sqlBom.AppendLine(" ,UsedStatus=@UsedStatus"); sqlBom.AppendLine(" ,ModifiedDate=getdate()"); sqlBom.AppendLine(" ,ModifiedUserID='" + loginUserID + "'"); sqlBom.AppendLine("where CompanyCD=@CompanyCD"); sqlBom.AppendLine("and ID=@ID"); SqlCommand comm = new SqlCommand(); comm.CommandText = sqlBom.ToString(); comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameter("@ID", model.ID)); comm.Parameters.Add(SqlHelper.GetParameter("@BomNo", model.BomNo)); comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject)); comm.Parameters.Add(SqlHelper.GetParameter("@ParentNo", model.ParentNo)); comm.Parameters.Add(SqlHelper.GetParameter("@Type", model.Type)); comm.Parameters.Add(SqlHelper.GetParameter("@Verson", model.Verson)); comm.Parameters.Add(SqlHelper.GetParameter("@RouteID", model.RouteID)); comm.Parameters.Add(SqlHelper.GetParameter("@Remark", model.Remark)); comm.Parameters.Add(SqlHelper.GetParameter("@ProductID", model.ProductID)); comm.Parameters.Add(SqlHelper.GetParameter("@UnitID", model.UnitID)); if (userInfo.IsMoreUnit) { comm.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", model.UsedUnitID)); comm.Parameters.Add(SqlHelper.GetParameter("@ExRate", model.ExRate)); } comm.Parameters.Add(SqlHelper.GetParameter("@UsedStatus", model.UsedStatus)); listADD.Add(comm); #endregion #region Bom子件信息更新语句 //先删除不在BOM子件中的 //更新子件中的ID //添加其它子件 #region 先删除不在BOM子件中的 if (!string.IsNullOrEmpty(UpdateID)) { StringBuilder sqlDel = new StringBuilder(); sqlDel.AppendLine("delete from officedba.BomDetail where CompanyCD=@CompanyCD and BomNo=@BomNo 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("@BomNo", model.BomNo)); listADD.Add(commDel); } #endregion #region 添加或更新操作 string[] updateID = UpdateID.Split(','); if (!string.IsNullOrEmpty(UpdateID) && updateID.Length > 0) { string[] subProductID = model.DetProductID.Split(','); string[] subProductType = model.DetProductType.Split(','); string[] subUnitID = model.DetUnitID.Split(','); string[] subQuota = model.DetQuota.Split(','); string[] subRateLoss = model.DetRateLoss.Split(','); string[] subIsMain = model.DetIsMain.Split(','); string[] subUsedStatus = model.DetUsedStatus.Split(','); string[] subSourceType = model.DetSourceType.Split(','); string[] subRemark = model.DetRemark.Split(','); for (int i = 0; i < updateID.Length; i++) { int intUpdateID = int.Parse(updateID[i].ToString()); if (intUpdateID > 0) { #region 更新子件中的ID StringBuilder sqlEdit = new StringBuilder(); sqlEdit.AppendLine("Update officedba.BomDetail"); sqlEdit.AppendLine("Set ProductID=@ProductID,"); sqlEdit.AppendLine(" ProductType=@ProductType,"); sqlEdit.AppendLine(" UnitID=@UnitID,"); if (subQuota[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subQuota[i].ToString().Trim())) { sqlEdit.AppendLine(" Quota=@Quota,"); } else { sqlEdit.AppendLine(" Quota=null,"); } } else { sqlEdit.AppendLine(" Quota=null,"); } if (subRateLoss[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subRateLoss[i].ToString().Trim())) { sqlEdit.AppendLine(" RateLoss=@RateLoss,"); } else { sqlEdit.AppendLine(" RateLoss=null,"); } } else { sqlEdit.AppendLine(" RateLoss=null,"); } sqlEdit.AppendLine(" IsMain=@IsMain,"); sqlEdit.AppendLine(" UsedStatus=@UsedStatus,"); if (subRemark[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subRemark[i].ToString().Trim())) { sqlEdit.AppendLine(" Remark=@Remark,"); } else { sqlEdit.AppendLine(" Remark=null,"); } } else { sqlEdit.AppendLine(" Remark=null,"); } sqlEdit.AppendLine(" SourceType=@SourceType"); sqlEdit.AppendLine("where CompanyCD=@CompanyCD"); sqlEdit.AppendLine("and ID=@ID"); SqlCommand commEdit = new SqlCommand(); commEdit.CommandText = sqlEdit.ToString(); commEdit.Parameters.Add(SqlHelper.GetParameter("@ProductID", subProductID[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@ProductType", subProductType[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@UnitID", subUnitID[i].ToString())); if (subQuota[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subQuota[i].ToString().Trim())) { commEdit.Parameters.Add(SqlHelper.GetParameter("@Quota", subQuota[i].ToString())); } } if (subRateLoss[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subRateLoss[i].ToString().Trim())) { commEdit.Parameters.Add(SqlHelper.GetParameter("@RateLoss", subRateLoss[i].ToString())); } } commEdit.Parameters.Add(SqlHelper.GetParameter("@IsMain", subIsMain[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@UsedStatus", subUsedStatus[i].ToString())); commEdit.Parameters.Add(SqlHelper.GetParameter("@SourceType", subSourceType[i].ToString())); if (subRemark[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subRemark[i].ToString().Trim())) { commEdit.Parameters.Add(SqlHelper.GetParameter("@Remark", subRemark[i].ToString())); } } commEdit.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commEdit.Parameters.Add(SqlHelper.GetParameter("@ID", updateID[i].ToString())); listADD.Add(commEdit); #endregion } else { #region 添加子件中的ID StringBuilder sqlIn = new StringBuilder(); sqlIn.AppendLine("insert into officedba.BomDetail("); sqlIn.AppendLine(" CompanyCD,"); sqlIn.AppendLine(" BomNo,"); sqlIn.AppendLine(" ProductID,"); sqlIn.AppendLine(" ProductType,"); sqlIn.AppendLine(" UnitID,"); if (subQuota[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subQuota[i].ToString().Trim())) { sqlIn.AppendLine(" Quota,"); } } if (subRateLoss[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subRateLoss[i].ToString().Trim())) { sqlIn.AppendLine(" RateLoss,"); } } sqlIn.AppendLine(" IsMain,"); sqlIn.AppendLine(" UsedStatus,"); sqlIn.AppendLine(" SourceType,"); sqlIn.AppendLine(" Remark)"); sqlIn.AppendLine("values( @CompanyCD,"); sqlIn.AppendLine(" @BomNo,"); sqlIn.AppendLine(" @ProductID,"); sqlIn.AppendLine(" @ProductType,"); sqlIn.AppendLine(" @UnitID,"); if (subQuota[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subQuota[i].ToString().Trim())) { sqlIn.AppendLine(" @Quota,"); } } if (subRateLoss[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subRateLoss[i].ToString().Trim())) { sqlIn.AppendLine(" @RateLoss,"); } } sqlIn.AppendLine(" @IsMain,"); sqlIn.AppendLine(" @UsedStatus,"); sqlIn.AppendLine(" @SourceType,"); sqlIn.AppendLine(" @Remark)"); SqlCommand commIn = new SqlCommand(); commIn.CommandText = sqlIn.ToString(); commIn.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); commIn.Parameters.Add(SqlHelper.GetParameter("@BomNo", model.BomNo)); commIn.Parameters.Add(SqlHelper.GetParameter("@ProductID", subProductID[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@ProductType", subProductType[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@UnitID", subUnitID[i].ToString())); if (subQuota[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subQuota[i].ToString().Trim())) { commIn.Parameters.Add(SqlHelper.GetParameter("@Quota", subQuota[i].ToString())); } } if (subRateLoss[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(subRateLoss[i].ToString().Trim())) { commIn.Parameters.Add(SqlHelper.GetParameter("@RateLoss", subRateLoss[i].ToString())); } } commIn.Parameters.Add(SqlHelper.GetParameter("@IsMain", subIsMain[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@UsedStatus", subUsedStatus[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@SourceType", subSourceType[i].ToString())); commIn.Parameters.Add(SqlHelper.GetParameter("@Remark", subRemark[i].ToString())); listADD.Add(commIn); #endregion } } } #endregion #endregion return SqlHelper.ExecuteTransWithArrayList(listADD); }
/// <summary> /// BOM子件详细信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetBomSubInfo(BomModel model) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select b.ID as DetailID,b.BomNo,b.ProductID,b.Remark,"); searchSql.AppendLine(" b.ProductType,c.CodeName as TypeName,b.UnitID,"); searchSql.AppendLine(" t.CodeName as UnitName,Convert(numeric(14," + userInfo.SelPoint + "),b.Quota) as Quota,Convert(numeric(14," + userInfo.SelPoint + "),b.RateLoss) as RateLoss,b.IsMain,"); searchSql.AppendLine(" b.UsedStatus,b.SourceType,p.ProductName,isnull(p.Specification,'') as Specification,isnull(cbt.TypeName,'') as ColorName,isnull(cbm.TypeName,'') as MaterialName "); searchSql.AppendLine("from officedba.BomDetail b"); searchSql.AppendLine("left join officedba.ProductInfo p on p.ID=b.ProductID"); searchSql.AppendLine("left join officedba.CodeProductType c on c.ID=b.ProductType"); searchSql.AppendLine("left join officedba.CodeUnitType t on t.ID=b.UnitID"); searchSql.AppendLine("left join officedba.CodePublicType cbt on p.ColorID=cbt.ID"); searchSql.AppendLine("left join officedba.CodePublicType cbm on p.Material=cbm.ID"); searchSql.AppendLine("where b.CompanyCD=@CompanyCD "); searchSql.AppendLine(" and BomNo=(select top 1 BomNo from officedba.Bom where CompanyCD=@CompanyCD and 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 = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// BOM详细信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetBomInfo(BomModel model) { #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select a.CompanyCD,a.ID,a.BomNo,a.Subject,a.ParentNo,a.[Type],"); searchSql.AppendLine(" a.Verson,a.Creator,a.RouteID,t.RouteName,e.EmployeeName,isnull( CONVERT(CHAR(10), a.CreateDate, 23),'') as CreateDate,"); searchSql.AppendLine(" a.Remark,p.ProductName,a.ProductID,a.UnitID,a.UsedUnitID,a.UsedStatus,"); searchSql.AppendLine(" c.CodeName as UnitName,d.CodeName as UsedUnitName,a.ExRate,b.BomNo as ParentBom"); searchSql.AppendLine("from officedba.Bom a"); searchSql.AppendLine("left outer join officedba.TechnicsRouting t on t.ID=a.RouteID"); searchSql.AppendLine("left join officedba.EmployeeInfo e on e.ID=a.Creator"); searchSql.AppendLine("left join officedba.ProductInfo p on p.ID=a.ProductID"); searchSql.AppendLine("left join officedba.CodeUnitType c on c.ID=a.UnitID"); searchSql.AppendLine("left join officedba.CodeUnitType d on d.ID=a.UsedUnitID"); searchSql.AppendLine("left outer join officedba.Bom b on b.ID=a.ParentNo"); searchSql.AppendLine("where a.ID=@ID"); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ID", model.ID.ToString())); //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 父件唯一性验证 /// </summary> /// <param name="ParentCode">上级编码</param> /// <returns>大于0:已经有父件引用该物品了,否则无父件引用该物品</returns> public static int ProductCount(BomModel model) { string sql = string.Empty; if (model.ID > 0) { sql = "select Count(ID) from officedba.Bom where CompanyCD=@CompanyCD and ProductID=@ProductID and ID<>@ID"; SqlParameter[] parms = new SqlParameter[3]; parms[0] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD); parms[1] = SqlHelper.GetParameter("@ProductID", model.ProductID); parms[2] = SqlHelper.GetParameter("@ID", model.ID); object obj = SqlHelper.ExecuteScalar(sql, parms); return Convert.ToInt32(obj); } else { sql = "select Count(ID) from officedba.Bom where CompanyCD=@CompanyCD and ProductID=@ProductID"; SqlParameter[] parms = new SqlParameter[2]; parms[0] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD); parms[1] = SqlHelper.GetParameter("@ProductID", model.ProductID); object obj = SqlHelper.ExecuteScalar(sql, parms); return Convert.ToInt32(obj); } }
/// <summary> /// 判断该结点下,是否还有子结点 /// </summary> /// <param name="ParentCode">上级编码</param> /// <returns>大于0还有子节点,否则无子节点</returns> public static int ChildrenCount(BomModel model) { string sql = "select count(ID) from officedba.Bom where CompanyCD=@CompanyCD and ParentNo=@ParentNo"; SqlParameter[] parms = new SqlParameter[2]; parms[0] = SqlHelper.GetParameter("@CompanyCD", model.CompanyCD); parms[1] = SqlHelper.GetParameter("@ParentNo", model.ParentNo); object obj = SqlHelper.ExecuteScalar(sql, parms); return Convert.ToInt32(obj); }
/// <summary> /// 物料清单插入 /// </summary> /// <param name="model"></param> /// <returns></returns> public static bool InsertBom(BomModel model, string loginUserID, out string ID) { UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"]; ArrayList listADD = new ArrayList(); bool result = false; //#region 传参 try { #region BOM添加SQL语句 StringBuilder sqlBom = new StringBuilder(); sqlBom.AppendLine("INSERT INTO officedba. Bom"); sqlBom.AppendLine("(CompanyCD,BomNo,Subject,ParentNo,[Type],Verson,RouteID,Creator,CreateDate,Remark,ProductID,UnitID,"); if (userInfo.IsMoreUnit) { sqlBom.AppendLine("UsedUnitID,ExRate,"); } sqlBom.AppendLine("UsedStatus,ModifiedDate,ModifiedUserID)"); sqlBom.AppendLine("VALUES "); sqlBom.AppendLine(" (@CompanyCD"); sqlBom.AppendLine(" ,@BomNo"); sqlBom.AppendLine(" ,@Subject"); sqlBom.AppendLine(" ,@ParentNo"); sqlBom.AppendLine(" ,@Type"); sqlBom.AppendLine(" ,@Verson"); sqlBom.AppendLine(" ,@RouteID"); sqlBom.AppendLine(" ,@Creator"); sqlBom.AppendLine(" ,@CreateDate"); sqlBom.AppendLine(" ,@Remark"); sqlBom.AppendLine(" ,@ProductID"); sqlBom.AppendLine(" ,@UnitID"); if (userInfo.IsMoreUnit) { sqlBom.AppendLine(" ,@UsedUnitID"); sqlBom.AppendLine(" ,@ExRate"); } sqlBom.AppendLine(" ,@UsedStatus"); sqlBom.AppendLine(" ,getdate()"); sqlBom.AppendLine(" ,'" + loginUserID + "') "); sqlBom.AppendLine("set @ID=@@IDENTITY"); SqlCommand comm = new SqlCommand(); comm.CommandText = sqlBom.ToString(); comm.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameter("@BomNo", model.BomNo)); comm.Parameters.Add(SqlHelper.GetParameter("@Subject", model.Subject)); comm.Parameters.Add(SqlHelper.GetParameter("@ParentNo", model.ParentNo)); comm.Parameters.Add(SqlHelper.GetParameter("@Type", model.Type)); comm.Parameters.Add(SqlHelper.GetParameter("@Verson", model.Verson)); comm.Parameters.Add(SqlHelper.GetParameter("@RouteID", model.RouteID)); 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("@ProductID", model.ProductID)); comm.Parameters.Add(SqlHelper.GetParameter("@UnitID", model.UnitID)); if (userInfo.IsMoreUnit) { comm.Parameters.Add(SqlHelper.GetParameter("@UsedUnitID", model.UsedUnitID)); comm.Parameters.Add(SqlHelper.GetParameter("@ExRate", model.ExRate)); } comm.Parameters.Add(SqlHelper.GetParameter("@UsedStatus", model.UsedStatus)); comm.Parameters.Add(SqlHelper.GetOutputParameter("@ID", SqlDbType.Int)); listADD.Add(comm); #endregion #region 子件信息添加SQL语句 if (!String.IsNullOrEmpty(model.DetProductID) && !String.IsNullOrEmpty(model.DetProductType) && !String.IsNullOrEmpty(model.DetUnitID)) { string[] dtProductID = model.DetProductID.Split(','); string[] dtProductType = model.DetProductType.Split(','); string[] dtUnitID = model.DetUnitID.Split(','); string[] dtQuota = model.DetQuota.Split(','); string[] dtRateLoss = model.DetRateLoss.Split(','); string[] dtIsMain = model.DetIsMain.Split(','); string[] dtUsedStatus = model.DetUsedStatus.Split(','); string[] dtSourceType = model.DetSourceType.Split(','); string[] dtRemark = model.DetRemark.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.BomDetail"); cmdsql.AppendLine("(CompanyCD,"); cmdsql.AppendLine("BomNo,"); cmdsql.AppendLine("ProductID,"); cmdsql.AppendLine("ProductType,"); cmdsql.AppendLine("UnitID,"); if (dtQuota[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(dtQuota[i].ToString().Trim())) { cmdsql.AppendLine("Quota,"); } } if (dtRateLoss[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(dtRateLoss[i].ToString().Trim())) { cmdsql.AppendLine("RateLoss,"); } } cmdsql.AppendLine("IsMain,"); cmdsql.AppendLine("UsedStatus,"); if (dtRemark[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(dtRemark[i].ToString().Trim())) { cmdsql.AppendLine("Remark,"); } } cmdsql.AppendLine("SourceType)"); cmdsql.AppendLine(" Values(@CompanyCD"); cmdsql.AppendLine(" ,@BomNo"); cmdsql.AppendLine(" ,@ProductID"); cmdsql.AppendLine(" ,@ProductType"); cmdsql.AppendLine(" ,@UnitID"); if (dtQuota[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(dtQuota[i].ToString().Trim())) { cmdsql.AppendLine(" ,@Quota"); } } if (dtRateLoss[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(dtRateLoss[i].ToString().Trim())) { cmdsql.AppendLine(" ,@RateLoss"); } } cmdsql.AppendLine(" ,@IsMain"); cmdsql.AppendLine(" ,@UsedStatus"); if (dtRemark[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(dtRemark[i].ToString().Trim())) { cmdsql.AppendLine(" ,@Remark"); } } cmdsql.AppendLine(" ,@SourceType)"); SqlCommand comms = new SqlCommand(); comms.CommandText = cmdsql.ToString(); comms.Parameters.Add(SqlHelper.GetParameter("@CompanyCD", model.CompanyCD)); comms.Parameters.Add(SqlHelper.GetParameter("@BomNo", model.BomNo)); comms.Parameters.Add(SqlHelper.GetParameter("@ProductID", dtProductID[i].ToString())); comms.Parameters.Add(SqlHelper.GetParameter("@ProductType", dtProductType[i].ToString())); comms.Parameters.Add(SqlHelper.GetParameter("@UnitID", dtUnitID[i].ToString())); if (dtQuota[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(dtQuota[i].ToString().Trim())) { comms.Parameters.Add(SqlHelper.GetParameter("@Quota", dtQuota[i].ToString())); } } if (dtRateLoss[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(dtRateLoss[i].ToString().Trim())) { comms.Parameters.Add(SqlHelper.GetParameter("@RateLoss", dtRateLoss[i].ToString())); } } comms.Parameters.Add(SqlHelper.GetParameter("@IsMain", dtIsMain[i].ToString())); comms.Parameters.Add(SqlHelper.GetParameter("@UsedStatus", dtUsedStatus[i].ToString())); if (dtRemark[i].ToString().Length > 0) { if (!string.IsNullOrEmpty(dtRemark[i].ToString().Trim())) { comms.Parameters.Add(SqlHelper.GetParameter("@Remark", dtRemark[i].ToString())); } } comms.Parameters.Add(SqlHelper.GetParameter("@SourceType", dtSourceType[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> /// BOM树 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetBomTree(BomModel model) { #region 查询语句 //查询SQL拼写 StringBuilder searchSql = new StringBuilder(); searchSql.AppendLine("select a.BomNo,a.ID ,b.ProductName from officedba.Bom a left join officedba.ProductInfo b on a.ProductID=b.ID where a.CompanyCD=@CompanyCD and a.ParentNo=@ParentNo "); #endregion //定义查询的命令 SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ParentNo", model.ParentNo.ToString())); //指定命令的SQL文 comm.CommandText = searchSql.ToString(); //执行查询 return SqlHelper.ExecuteSearch(comm); }
/// <summary> /// 设置是否合法 /// </summary> /// <param name="model"></param> /// <returns></returns> public static int CountInvalid(BomModel model) { try { return BomDBHelper.CountInvalid(model); } catch (Exception ex) { throw ex; } }
/// <summary> /// BOM控件查询Bom信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public static DataTable GetBomControlList(BomModel model, int pageIndex, int pageCount, string OrderBy, ref int totalCount) { try { return BomDBHelper.GetBomControlList(model,pageIndex, pageCount, OrderBy, ref totalCount); } catch (System.Exception ex) { throw ex; } }
/// <summary> /// 父件唯一性验证 /// </summary> /// <param name="ParentCode">上级编码</param> /// <returns>大于0:已经有父件引用该物品了,否则无父件引用该物品</returns> public static int ProductCount(BomModel model) { try { return BomDBHelper.ProductCount(model); } catch (Exception ex) { throw ex; } }