307 lines
15 KiB
C#
307 lines
15 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using WMS.IData;using Oracle.ManagedDataAccess.Client;
|
|||
|
|
using System.Data;
|
|||
|
|
|
|||
|
|
using WMS.DBUtility;
|
|||
|
|
using WMS.Model.Base;
|
|||
|
|
using WMS.IData.IBase;
|
|||
|
|
using System.Text.RegularExpressions;
|
|||
|
|
|
|||
|
|
namespace WMS.SqlServerData.BaseData
|
|||
|
|
{
|
|||
|
|
public class StorageAreaData : IStorageArea
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取库区信息结果集
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="areaInfoModel">库区信息</param>
|
|||
|
|
/// <returns>库区信息结果集</returns>
|
|||
|
|
public DataTable GetAreaInfoDS(StorageAreaModel areaInfoModel, string parentAreaName)
|
|||
|
|
{
|
|||
|
|
StringBuilder sqlBuilder = new StringBuilder();
|
|||
|
|
|
|||
|
|
sqlBuilder.AppendLine("select ");
|
|||
|
|
sqlBuilder.AppendLine(" a.storage_area_id, --库区编号 ");
|
|||
|
|
sqlBuilder.AppendLine(" a.storage_area_name, --库区名称 ");
|
|||
|
|
sqlBuilder.AppendLine(" a.storage_area_short_name, --库区简称 ");
|
|||
|
|
sqlBuilder.AppendLine(" a.py_name, --拼音简称 ");
|
|||
|
|
sqlBuilder.AppendLine(" a.storage_id, --所属仓库编号 ");
|
|||
|
|
sqlBuilder.AppendLine(" a.sort, --排序 ");
|
|||
|
|
sqlBuilder.AppendLine(" ( case a.status when '1' then '停用' when '0' then '正常' else'其他' end) status, --Status 0：正常 1：停用 ");
|
|||
|
|
sqlBuilder.AppendLine(" ISNULL(a.remark,' ') remark, --备注 ");
|
|||
|
|
sqlBuilder.AppendLine(" a.version, --版本号 ");
|
|||
|
|
sqlBuilder.AppendLine(" b.STORAGE_NAME, --所属仓库 ");
|
|||
|
|
sqlBuilder.AppendLine(" a.PARENT_ID, --父编号 ");
|
|||
|
|
sqlBuilder.AppendLine(" a.operatorid, --操作人 ");
|
|||
|
|
sqlBuilder.AppendLine(" a.operatedate, --操作日期 ");
|
|||
|
|
sqlBuilder.AppendLine(" a.edit --可否编辑 ");
|
|||
|
|
sqlBuilder.AppendLine("from ");
|
|||
|
|
sqlBuilder.AppendLine(" T_BASE_STORAGE_AREA a left join t_base_storageinfo b ");
|
|||
|
|
sqlBuilder.AppendLine(" on a.STORAGE_id = b.STORAGE_id ");
|
|||
|
|
sqlBuilder.AppendLine("where 1 = 1 ");
|
|||
|
|
|
|||
|
|
if (areaInfoModel != null)
|
|||
|
|
{
|
|||
|
|
if (!string.IsNullOrEmpty(areaInfoModel.STORAGE_ID))
|
|||
|
|
{
|
|||
|
|
sqlBuilder.AppendLine(" and a.STORAGE_ID = '" + areaInfoModel.STORAGE_ID + "'");
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(areaInfoModel.STORAGE_AREA_ID))
|
|||
|
|
{
|
|||
|
|
sqlBuilder.AppendLine(" and a.storage_area_id = '" + areaInfoModel.STORAGE_AREA_ID + "'");
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(areaInfoModel.STORAGE_AREA_NAME))
|
|||
|
|
{
|
|||
|
|
sqlBuilder.AppendLine(" and a.storage_area_name = '" + areaInfoModel.STORAGE_AREA_NAME + "'");
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(areaInfoModel.PARENT_ID))
|
|||
|
|
{
|
|||
|
|
sqlBuilder.AppendLine(" and a.PARENT_ID = '" + areaInfoModel.PARENT_ID + "'");
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(areaInfoModel.EDIT))
|
|||
|
|
{
|
|||
|
|
sqlBuilder.AppendLine(" and a.edit = '" + areaInfoModel.EDIT + "'");
|
|||
|
|
}
|
|||
|
|
if (!string.IsNullOrEmpty(areaInfoModel.STATUS))
|
|||
|
|
{
|
|||
|
|
sqlBuilder.AppendLine(" and a.STATUS = '" + areaInfoModel.STATUS + "'");
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//sqlBuilder.AppendLine(" order by a.SORT,PARENT_ID,a.storage_area_id");
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(sqlBuilder.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 获取库区信息结果集(编号、名称)
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="areaInfoModel">库区信息</param>
|
|||
|
|
/// <returns>库区信息结果集(编号、名称)</returns>
|
|||
|
|
public DataTable GetAreaInfoDSIdName(StorageAreaModel areaInfoModel)
|
|||
|
|
{
|
|||
|
|
StringBuilder sqlBuilder = new StringBuilder();
|
|||
|
|
|
|||
|
|
sqlBuilder.AppendLine("select ");
|
|||
|
|
sqlBuilder.AppendLine(" storage_area_id, --库区编号 ");
|
|||
|
|
sqlBuilder.AppendLine(" storage_area_name, --库区名称 ");
|
|||
|
|
sqlBuilder.AppendLine("from ");
|
|||
|
|
sqlBuilder.AppendLine(" T_BASE_STORAGE_AREA ");
|
|||
|
|
sqlBuilder.AppendLine("where 1=1 ");
|
|||
|
|
|
|||
|
|
if (!string.IsNullOrEmpty(areaInfoModel.STORAGE_ID))
|
|||
|
|
{
|
|||
|
|
sqlBuilder.AppendLine(" and STORAGE_ID = '" + areaInfoModel.STORAGE_ID + "'");
|
|||
|
|
}
|
|||
|
|
sqlBuilder.AppendLine(" order by SORT");
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(sqlBuilder.ToString());
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 新增或修改库区信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="areaInfoModel"></param>
|
|||
|
|
/// <param name="flag">flag 操作标示符 0：新增 1:修改</param>
|
|||
|
|
public string InsOrUpdAreaInfo(StorageAreaModel areaInfoModel, int flag)
|
|||
|
|
{
|
|||
|
|
string str = "select NEXT VALUE FOR seq_BASE_STORAGE_AREA ";
|
|||
|
|
DataSet ds = SystemDataObject.Instance.GetDataSet(str);
|
|||
|
|
string seq = ds.Tables[0].Rows[0][0].ToString();
|
|||
|
|
|
|||
|
|
|
|||
|
|
//新增
|
|||
|
|
if (0 == flag)
|
|||
|
|
{
|
|||
|
|
if (string.IsNullOrEmpty(areaInfoModel.STORAGE_AREA_ID))
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
if (string.IsNullOrEmpty(areaInfoModel.STORAGE_ID))
|
|||
|
|
{
|
|||
|
|
areaInfoModel.STORAGE_AREA_ID = "KQ" + areaInfoModel.STORAGE_ID + seq;
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
areaInfoModel.STORAGE_AREA_ID = "KQ" + Regex.Replace(areaInfoModel.STORAGE_ID, "[a-z,A-Z]", "", RegexOptions.IgnoreCase) + seq;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
string sqlstr =@"insert into
|
|||
|
|
T_BASE_STORAGE_AREA (STORAGE_AREA_ID,STORAGE_AREA_NAME,STORAGE_AREA_SHORT_NAME,PY_NAME,STORAGE_ID,SORT,REMARK,PARENT_ID,OPERATORID)
|
|||
|
|
values
|
|||
|
|
('" + areaInfoModel.STORAGE_AREA_ID + "', '" + areaInfoModel.STORAGE_AREA_NAME + "','" + areaInfoModel.STORAGE_AREA_SHORT_NAME + "',"
|
|||
|
|
+ " '" + areaInfoModel.PY_NAME + "','" + areaInfoModel.STORAGE_ID + "' ,'" + areaInfoModel.SORT + "','" + areaInfoModel.REMARK + "',"
|
|||
|
|
+ " '" + areaInfoModel.PARENT_ID + "','" + areaInfoModel.OPERATORID + "')";
|
|||
|
|
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(sqlstr);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
//修改
|
|||
|
|
else if (1 == flag)
|
|||
|
|
{
|
|||
|
|
string sqlstr =@"
|
|||
|
|
update
|
|||
|
|
T_BASE_STORAGE_AREA
|
|||
|
|
set
|
|||
|
|
storage_area_name =:storage_area_name, --库区名称
|
|||
|
|
storage_area_short_name =:storage_area_short_name, --库区简称
|
|||
|
|
py_name =:py_name, --拼音简称
|
|||
|
|
storage_id =:storage_id, --所属仓库编号
|
|||
|
|
sort =:sort, --排序
|
|||
|
|
status =:status, --Status
|
|||
|
|
remark =:remark, --备注
|
|||
|
|
version =:version, --版本号
|
|||
|
|
PARENT_ID =:PARENT_ID, --父编号
|
|||
|
|
operatorid =:operatorid, --操作人
|
|||
|
|
operatedate =getdate(), --操作日期
|
|||
|
|
edit =:edit --可否编辑
|
|||
|
|
where
|
|||
|
|
storage_area_id =:storage_area_id --库区编号
|
|||
|
|
";
|
|||
|
|
OracleParameter[] parameter = new OracleParameter[]
|
|||
|
|
{
|
|||
|
|
new OracleParameter(":storage_area_name",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":storage_area_short_name",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":py_name",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":storage_id",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":sort",OracleDbType.Decimal),
|
|||
|
|
new OracleParameter(":status",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":remark",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":version",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":PARENT_ID",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":operatorid",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":edit",OracleDbType.Varchar2),
|
|||
|
|
new OracleParameter(":storage_area_id",OracleDbType.Varchar2),
|
|||
|
|
};
|
|||
|
|
parameter[0].Value = areaInfoModel.STORAGE_AREA_NAME;
|
|||
|
|
parameter[1].Value = areaInfoModel.STORAGE_AREA_SHORT_NAME;
|
|||
|
|
parameter[2].Value = areaInfoModel.PY_NAME;
|
|||
|
|
parameter[3].Value = areaInfoModel.STORAGE_ID;
|
|||
|
|
parameter[4].Value = areaInfoModel.SORT;
|
|||
|
|
parameter[5].Value = areaInfoModel.STATUS;
|
|||
|
|
parameter[6].Value = areaInfoModel.REMARK;
|
|||
|
|
parameter[7].Value = areaInfoModel.VERSION;
|
|||
|
|
parameter[8].Value = areaInfoModel.PARENT_ID;
|
|||
|
|
parameter[9].Value = areaInfoModel.OPERATORID;
|
|||
|
|
parameter[10].Value = areaInfoModel.EDIT;
|
|||
|
|
parameter[11].Value = areaInfoModel.STORAGE_AREA_ID;
|
|||
|
|
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return areaInfoModel.STORAGE_AREA_ID;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 删除库区信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strAreaInfoID"></param>
|
|||
|
|
public void DeleteAreaInfo(string strAreaInfoID)
|
|||
|
|
{
|
|||
|
|
string sqlstr =@"delete from T_BASE_STORAGE_AREA where storage_area_id =:storage_area_id";
|
|||
|
|
OracleParameter[] parameter = new OracleParameter[]{
|
|||
|
|
new OracleParameter(":storage_area_id",OracleDbType.Varchar2),
|
|||
|
|
};
|
|||
|
|
parameter[0].Value = strAreaInfoID;
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 删除属于某仓库的库区信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strStorageInfoID">某仓库编号</param>
|
|||
|
|
public void DeleteAreaInfoByStorageInfoID(string strStorageInfoID)
|
|||
|
|
{
|
|||
|
|
string sqlstr =@"delete from T_BASE_STORAGE_AREA where storage_id =:storage_id";
|
|||
|
|
OracleParameter[] parameter = new OracleParameter[]{
|
|||
|
|
new OracleParameter(":storage_id",OracleDbType.Varchar2),
|
|||
|
|
};
|
|||
|
|
parameter[0].Value = strStorageInfoID;
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 删除属于某仓库分类的库区信息
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strStorageTypeID">某仓库分类</param>
|
|||
|
|
public void DeleteAreaInfoByStorageTypeID(string strStorageTypeID)
|
|||
|
|
{
|
|||
|
|
string sqlstr =@"delete
|
|||
|
|
from
|
|||
|
|
T_BASE_STORAGE_AREA
|
|||
|
|
where
|
|||
|
|
storage_id
|
|||
|
|
in
|
|||
|
|
(
|
|||
|
|
select
|
|||
|
|
STORAGE_ID
|
|||
|
|
from
|
|||
|
|
T_BASE_STORAGEINFO
|
|||
|
|
where
|
|||
|
|
STORAGE_TYPE_ID =:STORAGE_TYPE_ID
|
|||
|
|
)";
|
|||
|
|
OracleParameter[] parameter = new OracleParameter[]{
|
|||
|
|
new OracleParameter(":STORAGE_TYPE_ID",OracleDbType.Varchar2),
|
|||
|
|
};
|
|||
|
|
parameter[0].Value = strStorageTypeID;
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据仓库编号停用或启用某库区
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="strStorageInfoID">某仓库编号</param>
|
|||
|
|
/// <param name="flag">0停用1正常</param>
|
|||
|
|
public void CloseAreaInfoByStorageInfoID(string strStorageInfoID, int flag)
|
|||
|
|
{
|
|||
|
|
string sqlstr =@"update T_BASE_STORAGE_AREA set status = '" + flag + "' where storage_id =:storage_id";
|
|||
|
|
OracleParameter[] parameter = new OracleParameter[]{
|
|||
|
|
new OracleParameter(":storage_id",OracleDbType.Varchar2),
|
|||
|
|
};
|
|||
|
|
parameter[0].Value = strStorageInfoID;
|
|||
|
|
SystemDataObject.Instance.ExecuteSqlCommit(sqlstr, parameter);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 通过用户信息 获得用户所属库区
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="model"></param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
public DataTable GetStorageAreaByUserIDandStorageID(UserDataModel model)
|
|||
|
|
{
|
|||
|
|
StringBuilder strSql = new StringBuilder();
|
|||
|
|
strSql.Append(@" select t.*,
|
|||
|
|
a.storage_area_name AREA_NAME,
|
|||
|
|
s.storage_id,
|
|||
|
|
s.storage_name,
|
|||
|
|
d.vc_dictionary_name WORKKIND_NAME
|
|||
|
|
from T_BASE_USERAREA t
|
|||
|
|
left join t_Base_Storage_Area a
|
|||
|
|
on t.area_id = a.storage_area_id
|
|||
|
|
left join t_Base_Userinfo u
|
|||
|
|
on t.user_id = u.user_id
|
|||
|
|
left join t_Base_Storageinfo s
|
|||
|
|
on u.storage_id = s.storage_id
|
|||
|
|
left join t_Sys_Dictionary_Tab d
|
|||
|
|
on t.workkind_id = d.vc_char_value
|
|||
|
|
where d.vc_dictionary_type = 'workkind_type'");
|
|||
|
|
strSql.Append(" and t.user_id = '" + model.USER_ID + "' ");
|
|||
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// 更新仓库的名称
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="stroAge_id"></param>
|
|||
|
|
/// <param name="STORAGE_AREA_NAME"></param>
|
|||
|
|
public void UpdateAreaDataName(string stroAge_id,string STORAGE_AREA_NAME)
|
|||
|
|
{
|
|||
|
|
string sqlStr = "update T_BASE_STORAGE_AREA set STORAGE_AREA_NAME='"+ STORAGE_AREA_NAME + "' where STORAGE_AREA_ID='" + stroAge_id + "'";
|
|||
|
|
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|