/// <summary> /// 现有存量汇总 /// </summary> /// <param name="model"></param> /// <param name="pdtModel"></param> /// <param name="ProductCount1"></param> /// <param name="EFIndex"></param> /// <param name="EFDesc"></param> /// <param name="orderby"></param> /// <param name="BatchNo"></param> /// <returns></returns> public static string GetSumStorageInfo(StorageProductModel model, XBase.Model.Office.SupplyChain.ProductInfoModel pdtModel, string ProductCount1, string EFIndex, string EFDesc, string orderby, string BatchNo) { string rev = string.Empty; StringBuilder sql = new StringBuilder(); if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { sql.AppendLine("SELECT d.ExtField" + EFIndex + ",a.ID "); } else { sql.AppendLine("SELECT a.ID "); } sql.AppendLine(" ,ISNULL(b.StorageNo,'') as StorageNo,isnull(a.BatchNo,'') as BatchNo "); sql.AppendLine(" ,ISNULL(b.StorageName,'') as StorageName "); sql.AppendLine(" ,ISNULL(d.ProdNo,'') as ProductNo "); sql.AppendLine(" ,ISNULL(d.ProductName,'') as ProductName "); sql.AppendLine(" ,ISNULL(d.Specification,'') as Specification "); sql.AppendLine(" ,ISNULL(e.CodeName,'') as UnitID "); sql.AppendLine(" ,ISNULL(c.DeptName,'') as DeptName "); sql.AppendLine(" ,ISNULL(a.ProductCount,0) as ProductCount "); sql.AppendLine(" ,ISNULL(a.ProductCount,0)+ISNULL(a.RoadCount,0)+ISNULL(a.InCount,0)-ISNULL(a.OrderCount,0)-ISNULL(a.OutCount,0) as UseCount "); sql.AppendLine(" ,ISNULL(a.OrderCount,0) as OrderCount "); sql.AppendLine(" ,ISNULL(a.RoadCount,0) as RoadCount "); sql.AppendLine(" ,ISNULL(a.OutCount,0) as OutCount,a.ProductID,g.TypeName as ColorName "); sql.AppendLine("FROM officedba.StorageProduct a "); sql.AppendLine("left join officedba.StorageInfo b on a.StorageID=b.ID "); sql.AppendLine("left join officedba.DeptInfo c on c.ID=a.DeptID "); sql.AppendLine("left join officedba.ProductInfo d on d.ID=a.ProductID "); sql.AppendLine("left join officedba.CodeUnitType e on e.ID=d.UnitID left outer join officedba.CodePublicType g on d.ColorID=g.ID "); sql.AppendLine(" where a.CompanyCD='" + model.CompanyCD + "'"); SqlCommand comm = new SqlCommand(); //添加公司代码参数 comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD)); if (!string.IsNullOrEmpty(model.StorageID)) { sql.AppendLine(" and a.StorageID = @StorageID "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID)); } else { string ListID = StorageDBHelper.GetStorageIDStr(((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID, model.CompanyCD); sql.AppendLine(" and a.StorageID in(" + ListID + ")"); } if (!string.IsNullOrEmpty(model.ProductCount)) { sql.AppendLine(" and a.ProductCount >= @ProductCount "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductCount", model.ProductCount)); } if (!string.IsNullOrEmpty(ProductCount1)) { sql.AppendLine(" and a.ProductCount <= @ProductCount1 "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductCount1", ProductCount1)); } if (!string.IsNullOrEmpty(pdtModel.ProdNo)) { sql.AppendLine(" and d.ProdNo=@ProdNo"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", pdtModel.ProdNo)); } if (!string.IsNullOrEmpty(pdtModel.ColorID)) { sql.AppendLine(" and d.ColorID =@ColorID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ColorID", pdtModel.ColorID)); } if (!string.IsNullOrEmpty(pdtModel.TypeID)) { sql.AppendLine(" and d.TypeID =@TypeID"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@TypeID", pdtModel.TypeID)); } if (!string.IsNullOrEmpty(pdtModel.ProductName)) { sql.AppendLine(" and d.ProductName like '%' + @ProductName + '%'"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", pdtModel.ProductName)); } if (!string.IsNullOrEmpty(pdtModel.BarCode)) { sql.AppendLine(" and d.BarCode like '%' + @BarCode + '%'"); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", pdtModel.BarCode)); } if (!string.IsNullOrEmpty(pdtModel.Manufacturer)) { sql.AppendLine(" and d.Manufacturer like '%' + @Manufacturer + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Manufacturer", pdtModel.Manufacturer)); } if (!string.IsNullOrEmpty(pdtModel.Specification)) { sql.AppendLine(" and d.Specification like '%' + @Specification + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Specification", pdtModel.Specification)); } if (!string.IsNullOrEmpty(pdtModel.FromAddr)) { sql.AppendLine(" and d.FromAddr like '%' + @FromAddr + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@FromAddr", pdtModel.FromAddr)); } if (!string.IsNullOrEmpty(pdtModel.Material)) { sql.AppendLine(" and d.Material=@Material + '%' "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@Material", pdtModel.Material)); } ////过滤单据:显示当前用户拥有权限查看的单据 //int empid = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).EmployeeID; //sql.AppendLine(" and ( charindex('," + empid + ",' , ','+b.CanViewUser+',')>0 or b.StorageAdmin=" + empid + " OR b.CanViewUser='' OR b.CanViewUser is null) "); //扩展属性 if (!string.IsNullOrEmpty(EFIndex) && !string.IsNullOrEmpty(EFDesc)) { sql.AppendLine(" and d.ExtField" + EFIndex + " like @EFDesc "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@EFDesc", "%" + EFDesc + "%")); } if (BatchNo != "0") { if (BatchNo == "未设置批次") { sql.AppendLine(" and (a.BatchNo is null or a.BatchNo='') "); } else { sql.AppendLine(" and a.BatchNo=@BatchNo "); comm.Parameters.Add(SqlHelper.GetParameterFromString("@BatchNo", BatchNo)); } } string ResulSQL = "select sum(isnull(StoreCount,0)) as StoreCount,sum(isnull(ProductCount,0)) as ProductCount from ( select a.*,ProductCount/ExRate as StoreCount from ( select a.*,isnull(h.ExRate,1) as ExRate,isnull(j.CodeName,'') as CodeName from (" + sql.ToString() + ") a left outer join officedba.ProductInfo g on a.ProductID=g.ID left outer join Officedba.UnitGroup p on (g.GroupUnitNo=p.GroupUnitNo and p.CompanyCD='" + model.CompanyCD + "') LEFT OUTER JOIN Officedba.UnitGroupDetail h ON (p.GroupUnitNo=h.GroupUnitNo and h.CompanyCD='" + model.CompanyCD + "' and g.StockUnitID=h.UnitID) left outer join officedba.CodeUnitType j on h.UnitID=j.ID ) a ) a "; comm.CommandText = ResulSQL; DataTable dt = SqlHelper.ExecuteSearch(comm); if (dt.Rows.Count > 0) { rev = dt.Rows[0]["StoreCount"].ToString() + "|" + dt.Rows[0]["ProductCount"].ToString(); } return(rev); }
/// <summary> /// 获取指定人员可查看仓库的ID串(以逗号隔开形式) /// add by hexw 2010-3-10 /// </summary> /// <param name="empid">当前登录人ID</param> /// <param name="strCompanyCD">公司编码</param> /// <returns>仓库id串</returns> public static string GetStorageIDStr(int empid, string strCompanyCD) { return(StorageDBHelper.GetStorageIDStr(empid, strCompanyCD)); }