/// <summary>
/// 仓库插入
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool InsertStorage(StorageModel model, out int IndexIDentity)
{
IndexIDentity = 0;
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
//定义返回变量
bool isSucc = false;
/*
* 定义日志内容变量
* 增删改相关的日志，需要输出操作日志，该类型日志插入到数据库
* 其他的 如出现异常时，需要输出系统日志，该类型日志保存到日志文件
*/
//获取公司代码
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
//执行删除操作
try
{
//执行更新
isSucc = StorageDBHelper.InsertStorage(model, out IndexIDentity);
}
catch (Exception ex)
{
//输出日志
WriteSystemLog(userInfo, ex);
}
//定义变量
string remark;
//成功时
if (isSucc)
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_SUCCESS;
}
else
{
//设置操作成功标识
remark = ConstUtil.LOG_PROCESS_FAILED;
}
//操作日志
LogInfoModel logModel = InitLogInfo(model.StorageNo);
//涉及关键元素 这个需要根据每个页面具体设置，本页面暂时设置为空
logModel.Element = ConstUtil.LOG_PROCESS_INSERT;
//设置操作成功标识
logModel.Remark = remark;
//登陆日志
LogDBHelper.InsertLog(logModel);
return(isSucc);
}
/// <summary>
/// 查询仓库信息
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetStorageListBycondition(StorageModel model, string orderby)
{
try
{
return(StorageDBHelper.GetStorageTableBycondition(model, orderby));
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 进销存汇分析(决策模式)
/// </summary>
/// <param name="StorageID"></param>
/// <param name="BatchNo"></param>
/// <param name="ProductName"></param>
/// <param name="StartDate"></param>
/// <param name="EndDate"></param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="OrderBy"></param>
/// <param name="dt"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataTable GetStorageInAndOutTotalInfo(string StorageID, string BatchNo, string ProductName, string StartDate, string EndDate, int pageIndex, int pageCount, string OrderBy, ref int totalCount)
{
try
{
return(StorageDBHelper.GetStorageInAndOutTotalInfo(StorageID, BatchNo, ProductName, StartDate, EndDate, pageIndex, pageCount, OrderBy, ref totalCount));
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 进销存汇总表
/// </summary>
/// <param name="model"></param>
/// <param name="DailyDate"></param>
/// <param name="EFIndex"></param>
/// <param name="EFDesc"></param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="OrderBy"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static DataTable GetAllStorageInAndOutInfo(ProductInfoModel model, string DailyDate, string EndDate, string BatchNo, string EFIndex, string EFDesc, int pageIndex, int pageCount, string OrderBy, out DataTable dt, ref int totalCount)
{
try
{
return(StorageDBHelper.GetAllStorageInAndOutInfo(model, DailyDate, EndDate, BatchNo, EFIndex, EFDesc, pageIndex, pageCount, OrderBy, out dt, ref totalCount));
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取仓库详细信息
/// </summary>
/// <returns>DataTable</returns>
public static DataTable GetStorageDetailInfo(string CompanyCD, int ID)
{
try
{
return(StorageDBHelper.GetStorage(CompanyCD, ID));
}
catch (System.Exception ex)
{
throw ex;
}
}
public static DataTable GetStorageListByRed(StorageModel model)
{
try
{
return(StorageDBHelper.GetStorageTableByRed(model));
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// 进销存日报表总计
/// </summary>
/// <param name="model"></param>
/// <param name="DailyDate"></param>
/// <param name="BatchNo"></param>
/// <param name="EFIndex"></param>
/// <param name="EFDesc"></param>
/// <returns></returns>
public static DataTable GetAllTotal(ProductInfoModel model, string DailyDate, string EndDate, string BatchNo, string EFIndex, string EFDesc, bool flag)
{
try
{
return(StorageDBHelper.GetAllTotal(model, DailyDate, EndDate, BatchNo, EFIndex, EFDesc, flag));
}
catch (System.Exception ex)
{
throw ex;
}
}
public static void BindStorateInfo(System.Web.UI.WebControls.DropDownList ddl, string companycd)
{
StorageDBHelper.BindStorateInfo(ddl, companycd);
}
public static bool IsBatchByProductNameAndNo(string ProductNo, string ProductName, string CompanyCD)
{
return(StorageDBHelper.IsBatchByProductNameAndNo(ProductNo, ProductName, CompanyCD));
}
public static string ValidateStorageCount(string[] ProductID, string[] StorageID, string[] BatchNo, string CompanyCD)
{
return(StorageDBHelper.ValidateStorageCount(ProductID, StorageID, BatchNo, CompanyCD));
}
public static DataTable GetStorageInCompare(int storageID, string begindate, string enddate, int ByTimeType)
{
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
return(StorageDBHelper.GetStorageInCompare(companyCD, storageID, begindate, enddate, ByTimeType));
}
public static DataSet GetStorageInfoFromExcel(string companycd, string fname, string tbname)
{
return(StorageDBHelper.GetStorageInfoFromExcel(companycd, fname, tbname));
}
public static int GetExcelToStorageInfo(string companycd, string usercode, string isbatchno, int creator)
{
return(StorageDBHelper.GetExcelToStorageInfo(companycd, usercode, isbatchno, creator));
}
/// <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));
}
public static DataTable GetStorageProductLossDetails(int storageID, string begindate, string enddate, int ByTimeType, int pageindex, int pagesize, ref int recordCount)
{
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
return(StorageDBHelper.GetStorageProductLossDetails(companyCD, storageID, begindate, enddate, ByTimeType, pageindex, pagesize, ref recordCount));
}
public static DataTable GetStorageProductLossAnalysis(int ProductID, string begindate, string enddate, int ByTimeType)
{
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
return(StorageDBHelper.GetStorageProductLossAnalysis(companyCD, ProductID, begindate, enddate, ByTimeType));
}
public static DataTable GetProductStorageDetails(int productID, int pageindex, int pagesize, ref int recordCount)
{
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
return(StorageDBHelper.GetProductStorageDetails(companyCD, productID, pageindex, pagesize, ref recordCount));
}
public static DataTable GetProductStorage(int StorageID)
{
string companyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
return(StorageDBHelper.GetProductStorage(companyCD, StorageID));
}
/// <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);
}
protected void LoadStorageInfo()
{
DataTable dt = StorageBus.GetStorageDetailInfo(companyCD, this.ID);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
this.lbStorageNo.Text = dr["StorageNo"] == null ? "" : dr["StorageNo"].ToString().Trim();
this.txtStorageName.Text = dr["StorageName"] == null ? "" : dr["StorageName"].ToString().Trim();
this.sltType.SelectedValue = dr["StorageType"] == null ? "" : dr["StorageType"].ToString().Trim();
this.txtRemark.Text = dr["Remark"] == null ? "" : dr["Remark"].ToString().Trim();
this.sltUsedStatus.Text = dr["UsedStatus"] == null ? "" : dr["UsedStatus"].ToString().Trim();
this.txtIndentityID.Value = dr["ID"] == null ? "" : dr["ID"].ToString().Trim();
//string userList = dr["CanViewUser"] == null ? "" : dr["CanViewUser"].ToString().Trim();
//if (string.IsNullOrEmpty(userList))
//{
// txtUserList.Text = "";
// txtUserListHidden.Value = "";
//}
//else
//{
// string[] sArray=Regex.Split(userList,"@@",RegexOptions.IgnoreCase);
// txtUserList.Text = sArray[1];
// txtUserListHidden.Value = sArray[0];
//}
txtUserListHidden.Value = dr["CanViewUser"] == null ? "" : dr["CanViewUser"].ToString().Trim();
if (!string.IsNullOrEmpty(txtUserListHidden.Value.Trim()))
{
DataTable dtName = StorageDBHelper.GetEmployeeNameByID(dr["CanViewUser"].ToString());
string [] temp = dr["CanViewUser"].ToString().Split(',');
string tmpName = string.Empty;
for (int a = 0; a < temp.Length; a++)
{
if (dtName.Rows.Count > 0)
{
for (int b = 0; b < dtName.Rows.Count; b++)
{
string id = dtName.Rows[b]["id"] == null ? "" : dtName.Rows[b]["id"].ToString();
if (int.Parse(id) == int.Parse(temp[a]))
{
if (a == (temp.Length - 1))
{
tmpName = tmpName + dtName.Rows[b]["EmployeeName"];
}
else
{
tmpName = tmpName + dtName.Rows[b]["EmployeeName"] + ",";
}
break;
}
else
{
continue;
}
}
}
}
txtUserList.Text = tmpName;
}
else
{
txtUserList.Text = string.Empty;
}
txtExecutorID.Value = dr["StorageAdmin"] == null ? "" : dr["StorageAdmin"].ToString().Trim();
UsertxtExecutor.Value = dr["StorageAdminName"] == null ? "" : dr["StorageAdminName"].ToString().Trim();
}
}
public static bool IsDeleteStorage(string ID, string CompanyCD)
{
return(StorageDBHelper.IsDeleteStorage(ID, CompanyCD));
}
public static bool ChargeProductInfo(string codename, string compid, string prodNo)
{
return(StorageDBHelper.ChargeProductInfo(codename, compid, prodNo));
}
public static DataTable GetStorageSetUp(string companycd, int storageID, int ProductID, string begindate, string enddate, int timeType, int ByTimeType)
{
return(StorageDBHelper.GetStorageSetUp(companycd, storageID, ProductID, begindate, enddate, timeType, ByTimeType));
}
public static DataSet ReadEexcel(string FilePath, string companycd)
{
return(StorageDBHelper.ReadEexcel(FilePath, companycd));
}
public static DataTable GetLitBycondition(StorageModel model, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
return(StorageDBHelper.GetLitBycondition(model, pageIndex, pageCount, ord, ref TotalCount));
}