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;
namespace WMS.SqlServerData.BaseData
{
public class StorageInfoData : IStorageInfo
{
public void UpCompanyStorageInfo(StorageInfoModel storageInfoModel)
{
string sqlstr =@"update T_BASE_STORAGEINFO set
STORAGE_NAME =:STORAGE_NAME,
STORAGE_SHORT_NAME =:STORAGE_SHORT_NAME,
PY_NAME=:PY_NAME,
STORAGE_TYPE_ID=:STORAGE_TYPE_ID,
LINKMAN=:LINKMAN,
PHONE=:PHONE,
FAX=:FAX,
ADDRESS=:ADDRESS,
POSTCODE=:POSTCODE,
RFMODEL=:RFMODEL,
GOODSFLAG=:GOODSFLAG,
BATCHFLAG=:BATCHFLAG,
STATUS=:STATUS,
REMARK=:REMARK,
VERSION=:VERSION,
SORT=:SORT,
PROVINCE_ID=:PROVINCE_ID,
CITY_ID=:CITY_ID,
TOWN_ID=:TOWN_ID,
operatorid=:operatorid,
operatedate=getdate(),
AUTOSTS =:AUTOSTS , STORAGE_ID=:STORAGE_ID
where ID=:ID";
OracleParameter[] parameter = new OracleParameter[]
{
new OracleParameter(":STORAGE_NAME",OracleDbType.Varchar2),
new OracleParameter(":STORAGE_SHORT_NAME",OracleDbType.Varchar2),
new OracleParameter(":PY_NAME",OracleDbType.Varchar2),
new OracleParameter(":STORAGE_TYPE_ID",OracleDbType.Varchar2),
new OracleParameter(":LINKMAN",OracleDbType.Varchar2),
new OracleParameter(":PHONE",OracleDbType.Varchar2),
new OracleParameter(":FAX",OracleDbType.Varchar2),
new OracleParameter(":ADDRESS",OracleDbType.Varchar2),
new OracleParameter(":POSTCODE",OracleDbType.Varchar2),
new OracleParameter(":RFMODEL",OracleDbType.Char),
new OracleParameter(":GOODSFLAG",OracleDbType.Char),
new OracleParameter(":BATCHFLAG",OracleDbType.Char),
new OracleParameter(":STATUS",OracleDbType.Char),
new OracleParameter(":REMARK",OracleDbType.Varchar2),
new OracleParameter(":VERSION",OracleDbType.Varchar2),
new OracleParameter(":SORT",OracleDbType.Decimal),
new OracleParameter(":PROVINCE_ID",OracleDbType.Varchar2),
new OracleParameter(":CITY_ID",OracleDbType.Varchar2),
new OracleParameter(":TOWN_ID",OracleDbType.Varchar2),
new OracleParameter(":operatorid",OracleDbType.Varchar2),
new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
new OracleParameter(":AUTOSTS",OracleDbType.Int16),
new OracleParameter(":ID",OracleDbType.Int16)
};
parameter[0].Value = storageInfoModel.STORAGE_NAME;
parameter[1].Value = storageInfoModel.STORAGE_SHORT_NAME;
parameter[2].Value = storageInfoModel.PY_NAME;
parameter[3].Value = storageInfoModel.STORAGE_TYPE_ID;
parameter[4].Value = storageInfoModel.LINKMAN;
parameter[5].Value = storageInfoModel.PHONE;
parameter[6].Value = storageInfoModel.FAX;
parameter[7].Value = storageInfoModel.ADDRESS;
parameter[8].Value = storageInfoModel.POSTCODE;
parameter[9].Value = storageInfoModel.RFMODEL;
parameter[10].Value = storageInfoModel.GOODSFLAG;
parameter[11].Value = storageInfoModel.BATCHFLAG;
parameter[12].Value = storageInfoModel.STATUS;
parameter[13].Value = storageInfoModel.REMARK;
parameter[14].Value = storageInfoModel.VERSION;
parameter[15].Value = storageInfoModel.SORT;
parameter[16].Value = storageInfoModel.PROVINCE_ID;
parameter[17].Value = storageInfoModel.CITY_ID;
parameter[18].Value = storageInfoModel.TOWN_ID;
parameter[19].Value = storageInfoModel.OPERATORID;
parameter[20].Value = storageInfoModel.STORAGE_ID;
parameter[21].Value = storageInfoModel.AUTOSTS.ToString();
parameter[22].Value = storageInfoModel.ID;
SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
}
///
/// 获取仓库信息结果集
///
///
public DataTable GetStorageInfoDS(StorageInfoModel storageInfoModel)
{
//select STORAGE_ID, STORAGE_NAME, t1.STORAGE_SHORT_NAME,t1.PY_NAME,t1.STORAGE_TYPE_ID,t2.STORAGE_TYPE_NAME,
//LINKMAN,PHONE,FAX,ADDRESS,POSTCODE,decode(RFMODEL,'1','是','0','否','其他') RFMODEL,decode(GOODSFLAG,'1','是','0','否','其他') GOODSFLAG,
//decode(BATCHFLAG,'1','是','0','否','其他') BATCHFLAG,decode(t1.STATUS,'1','停用','0','正常','其他') STATUS,t1.REMARK,t1.VERSION
//from T_BASE_STORAGEINFO t1,T_BASE_STORAGE_TYPE t2 where t1.STORAGE_TYPE_ID=t2.STORAGE_TYPE_ID
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine("select ");
sqlBuilder.AppendLine(" a.STORAGE_ID, --仓库编号 ");
sqlBuilder.AppendLine(" a.STORAGE_NAME, --仓库名称 ");
sqlBuilder.AppendLine(" a.STORAGE_SHORT_NAME, --仓库简称 ");
sqlBuilder.AppendLine(" a.PY_NAME, --拼音简称 ");
//sqlBuilder.AppendLine(" a.STORAGE_TYPE_ID, --所属仓库分类 ");
sqlBuilder.AppendLine(" a.LINKMAN, --联系人 ");
sqlBuilder.AppendLine(" a.PHONE, --联系电话 ");
sqlBuilder.AppendLine(" a.FAX, --联系传真 ");
sqlBuilder.AppendLine(" a.ADDRESS, --地址 ");
sqlBuilder.AppendLine(" a.POSTCODE, --邮编 ");
sqlBuilder.AppendLine(" (case a.RFMODEL when '1' then '是' when '0' then '否' else '其他' end ) RFMODEL, --RF使用模式（是否支持RF手持设备）0否 1是");
sqlBuilder.AppendLine(" (case a.GOODSFLAG when '1' then '是' when '0' then '否' else '其他' end) GOODSFLAG, --物料混放 0：否 1：是 ");
sqlBuilder.AppendLine(" (case a.BATCHFLAG when '1' then '是' when '0' then '否' else '其他' end) BATCHFLAG, --批次混放 0：否 1：是 ");
sqlBuilder.AppendLine(" (case a.STATUS when '1' then '有货' when '0' then '正常' else '其他' end) STATUS, --Status 0：正常 1：停用 ");
sqlBuilder.AppendLine(" a.REMARK, --备注 ");
sqlBuilder.AppendLine(" a.VERSION, --版本号 ");
sqlBuilder.AppendLine(" a.SORT, --排序 ");
//sqlBuilder.AppendLine(" b.STORAGE_TYPE_NAME, --所属仓库分类 ");
sqlBuilder.AppendLine(" a.province_id, --省 ");
sqlBuilder.AppendLine(" a.city_id, --市 ");
sqlBuilder.AppendLine(" a.town_id, --县/市/区 ");
sqlBuilder.AppendLine(" f.province_name, --省 ");
sqlBuilder.AppendLine(" f.city_name, --市 ");
sqlBuilder.AppendLine(" f.town_name, --县/市/区 ");
sqlBuilder.AppendLine(" a.operatorid, --操作人 ");
sqlBuilder.AppendLine(" a.operatedate ,a.AUTOSTS --操作日期 ");
sqlBuilder.AppendLine("from ");
sqlBuilder.AppendLine(" T_BASE_STORAGEINFO a");
//sqlBuilder.AppendLine(" left join t_base_storage_type b ");
//sqlBuilder.AppendLine(" on a.storage_type_id = b.storage_type_id ");
sqlBuilder.AppendLine(" left join ");
sqlBuilder.AppendLine("(select--根据区ID得到该区属于哪个省市 ");
sqlBuilder.AppendLine(" c.add_name province_name, ");
sqlBuilder.AppendLine(" d.add_name city_name, ");
sqlBuilder.AppendLine(" e.add_name town_name, ");
sqlBuilder.AppendLine(" e.add_id town_id ");
sqlBuilder.AppendLine(" FROM ");
sqlBuilder.AppendLine(" t_base_addressarea c left join t_base_addressarea d ");
sqlBuilder.AppendLine(" on ");
sqlBuilder.AppendLine(" c.add_id = d.add_pid ");
sqlBuilder.AppendLine(" left join t_base_addressarea e ");
sqlBuilder.AppendLine(" on ");
sqlBuilder.AppendLine(" d.add_id = e.add_pid) f ");
sqlBuilder.AppendLine(" on a.town_id = f.town_id ");
sqlBuilder.AppendLine("where 1 = 1 ");
if (storageInfoModel != null)
{
if (!string.IsNullOrEmpty(storageInfoModel.STORAGE_TYPE_ID))
{
sqlBuilder.AppendLine(" and a.STORAGE_TYPE_ID = '" + storageInfoModel.STORAGE_TYPE_ID + "'");
}
if (!string.IsNullOrEmpty(storageInfoModel.STORAGE_ID))
{
sqlBuilder.AppendLine(" and a.STORAGE_ID = '" + storageInfoModel.STORAGE_ID + "'");
}
if (!string.IsNullOrEmpty(storageInfoModel.STORAGE_NAME))
{
sqlBuilder.AppendLine(" and a.STORAGE_NAME = '" + storageInfoModel.STORAGE_NAME + "'");
}
if (!string.IsNullOrEmpty(storageInfoModel.TOWN_ID))
{
sqlBuilder.AppendLine(" and a.TOWN_ID = '" + storageInfoModel.TOWN_ID + "'");
}
}
sqlBuilder.AppendLine(" order by a.SORT");
string sql2 = " select a.STORAGE_ID,a.STORAGE_NAME from T_BASE_STORAGEINFO a";
return SystemDataObject.Instance.GetDataTable(sql2.ToString());
}
///
/// 插入或更改仓库信息
///
///
/// flag 操作标示符 0：新增 1:修改
public string InsOrUpdStorageInfo(StorageInfoModel storageInfoModel, int flag)
{
//string str = "select NEXT VALUE FOR seq_BASE_STORAGEINFO ";
//DataSet ds = SystemDataObject.Instance.GetDataSet(str);
//storageInfoModel.STORAGE_ID = "CK" + ds.Tables[0].Rows[0][0].ToString();
//新增
if (0 == flag)
{
string sqlstr =@"insert into
T_BASE_STORAGEINFO
(
STORAGE_ID, --仓库编号
STORAGE_NAME, --仓库名称
STORAGE_SHORT_NAME, --仓库简称
PY_NAME, --拼音简称
STORAGE_TYPE_ID, --所属仓库分类
LINKMAN, --联系人
PHONE, --联系电话
FAX, --联系传真
ADDRESS, --地址
POSTCODE, --邮编
RFMODEL, --RF使用模式（是否支持RF手持设备）0否 1是
GOODSFLAG, --物料混放 0：否 1：是
BATCHFLAG, --批次混放 0：否 1：是
STATUS, --Status 0：正常 1：停用
REMARK, --备注
VERSION, --版本号
SORT, --排序
PROVINCE_ID, --省
CITY_ID, --市
TOWN_ID, --区
operatorid, --操作人
operatedate , --操作日期
AUTOSTS,
ID
)
values
(
:STORAGE_ID,
:STORAGE_NAME,
:STORAGE_SHORT_NAME,
:PY_NAME,
:STORAGE_TYPE_ID,
:LINKMAN,
:PHONE,
:FAX,
:ADDRESS,
:POSTCODE,
:RFMODEL,
:GOODSFLAG,
:BATCHFLAG,
:STATUS,
:REMARK,
:VERSION,
:SORT,
:PROVINCE_ID,
:CITY_ID,
:TOWN_ID,
:operatorid,
getdate() ,:AUTOSTS ,:ID
)";
OracleParameter[] parameter = new OracleParameter[]
{
new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
new OracleParameter(":STORAGE_NAME",OracleDbType.Varchar2),
new OracleParameter(":STORAGE_SHORT_NAME",OracleDbType.Varchar2),
new OracleParameter(":PY_NAME",OracleDbType.Varchar2),
new OracleParameter(":STORAGE_TYPE_ID",OracleDbType.Varchar2),
new OracleParameter(":LINKMAN",OracleDbType.Varchar2),
new OracleParameter(":PHONE",OracleDbType.Varchar2),
new OracleParameter(":FAX",OracleDbType.Varchar2),
new OracleParameter(":ADDRESS",OracleDbType.Varchar2),
new OracleParameter(":POSTCODE",OracleDbType.Varchar2),
new OracleParameter(":RFMODEL",OracleDbType.Char),
new OracleParameter(":GOODSFLAG",OracleDbType.Char),
new OracleParameter(":BATCHFLAG",OracleDbType.Char),
new OracleParameter(":STATUS",OracleDbType.Char),
new OracleParameter(":REMARK",OracleDbType.Varchar2),
new OracleParameter(":VERSION",OracleDbType.Varchar2),
new OracleParameter(":SORT",OracleDbType.Decimal),
new OracleParameter(":PROVINCE_ID",OracleDbType.Varchar2),
new OracleParameter(":CITY_ID",OracleDbType.Varchar2),
new OracleParameter(":TOWN_ID",OracleDbType.Varchar2),
new OracleParameter(":operatorid",OracleDbType.Varchar2),
new OracleParameter(":AUTOSTS",OracleDbType.Int16),
new OracleParameter(":ID",OracleDbType.Int64)
};
parameter[0].Value = storageInfoModel.STORAGE_ID;
parameter[1].Value = storageInfoModel.STORAGE_NAME;
parameter[2].Value = storageInfoModel.STORAGE_SHORT_NAME;
parameter[3].Value = storageInfoModel.PY_NAME;
parameter[4].Value = storageInfoModel.STORAGE_TYPE_ID;
parameter[5].Value = storageInfoModel.LINKMAN;
parameter[6].Value = storageInfoModel.PHONE;
parameter[7].Value = storageInfoModel.FAX;
parameter[8].Value = storageInfoModel.ADDRESS;
parameter[9].Value = storageInfoModel.POSTCODE;
parameter[10].Value = storageInfoModel.RFMODEL;
parameter[11].Value = storageInfoModel.GOODSFLAG;
parameter[12].Value = storageInfoModel.BATCHFLAG;
parameter[13].Value = storageInfoModel.STATUS;
parameter[14].Value = storageInfoModel.REMARK;
parameter[15].Value = storageInfoModel.VERSION;
parameter[16].Value = storageInfoModel.SORT;
parameter[17].Value = storageInfoModel.PROVINCE_ID;
parameter[18].Value = storageInfoModel.CITY_ID;
parameter[19].Value = storageInfoModel.TOWN_ID;
parameter[20].Value = storageInfoModel.OPERATORID;
parameter[21].Value =storageInfoModel.AUTOSTS.ToString();
parameter[22].Value = storageInfoModel.ID;
SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
}
//修改
else if (1 == flag)
{
string sqlstr =@"update T_BASE_STORAGEINFO set
STORAGE_NAME =:STORAGE_NAME,
STORAGE_SHORT_NAME =:STORAGE_SHORT_NAME,
PY_NAME=:PY_NAME,
STORAGE_TYPE_ID=:STORAGE_TYPE_ID,
LINKMAN=:LINKMAN,
PHONE=:PHONE,
FAX=:FAX,
ADDRESS=:ADDRESS,
POSTCODE=:POSTCODE,
RFMODEL=:RFMODEL,
GOODSFLAG=:GOODSFLAG,
BATCHFLAG=:BATCHFLAG,
STATUS=:STATUS,
REMARK=:REMARK,
VERSION=:VERSION,
SORT=:SORT,
PROVINCE_ID=:PROVINCE_ID,
CITY_ID=:CITY_ID,
TOWN_ID=:TOWN_ID,
operatorid=:operatorid,
operatedate=getdate(),
AUTOSTS =:AUTOSTS
where STORAGE_ID=:STORAGE_ID";
OracleParameter[] parameter = new OracleParameter[]
{
new OracleParameter(":STORAGE_NAME",OracleDbType.Varchar2),
new OracleParameter(":STORAGE_SHORT_NAME",OracleDbType.Varchar2),
new OracleParameter(":PY_NAME",OracleDbType.Varchar2),
new OracleParameter(":STORAGE_TYPE_ID",OracleDbType.Varchar2),
new OracleParameter(":LINKMAN",OracleDbType.Varchar2),
new OracleParameter(":PHONE",OracleDbType.Varchar2),
new OracleParameter(":FAX",OracleDbType.Varchar2),
new OracleParameter(":ADDRESS",OracleDbType.Varchar2),
new OracleParameter(":POSTCODE",OracleDbType.Varchar2),
new OracleParameter(":RFMODEL",OracleDbType.Char),
new OracleParameter(":GOODSFLAG",OracleDbType.Char),
new OracleParameter(":BATCHFLAG",OracleDbType.Char),
new OracleParameter(":STATUS",OracleDbType.Char),
new OracleParameter(":REMARK",OracleDbType.Varchar2),
new OracleParameter(":VERSION",OracleDbType.Varchar2),
new OracleParameter(":SORT",OracleDbType.Decimal),
new OracleParameter(":PROVINCE_ID",OracleDbType.Varchar2),
new OracleParameter(":CITY_ID",OracleDbType.Varchar2),
new OracleParameter(":TOWN_ID",OracleDbType.Varchar2),
new OracleParameter(":operatorid",OracleDbType.Varchar2),
new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
new OracleParameter(":AUTOSTS",OracleDbType.Int16)
};
parameter[0].Value = storageInfoModel.STORAGE_NAME;
parameter[1].Value = storageInfoModel.STORAGE_SHORT_NAME;
parameter[2].Value = storageInfoModel.PY_NAME;
parameter[3].Value = storageInfoModel.STORAGE_TYPE_ID;
parameter[4].Value = storageInfoModel.LINKMAN;
parameter[5].Value = storageInfoModel.PHONE;
parameter[6].Value = storageInfoModel.FAX;
parameter[7].Value = storageInfoModel.ADDRESS;
parameter[8].Value = storageInfoModel.POSTCODE;
parameter[9].Value = storageInfoModel.RFMODEL;
parameter[10].Value = storageInfoModel.GOODSFLAG;
parameter[11].Value = storageInfoModel.BATCHFLAG;
parameter[12].Value = storageInfoModel.STATUS;
parameter[13].Value = storageInfoModel.REMARK;
parameter[14].Value = storageInfoModel.VERSION;
parameter[15].Value = storageInfoModel.SORT;
parameter[16].Value = storageInfoModel.PROVINCE_ID;
parameter[17].Value = storageInfoModel.CITY_ID;
parameter[18].Value = storageInfoModel.TOWN_ID;
parameter[19].Value = storageInfoModel.OPERATORID;
parameter[20].Value = storageInfoModel.STORAGE_ID;
parameter[21].Value = storageInfoModel.AUTOSTS.ToString();
SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
}
return storageInfoModel.STORAGE_ID;
}
///
/// 删除仓库信息
///
///
public void DeleteStorageInfo(string strStorageInfoID)
{
string sqlstr =@"delete from T_BASE_STORAGEINFO where STORAGE_ID =:STORAGE_ID ";
OracleParameter[] parameter = new OracleParameter[]{
new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
};
parameter[0].Value = strStorageInfoID;
SystemDataObject.Instance.ExecuteSql(sqlstr, parameter);
}
///
/// 删除属于某仓库分类的仓库信息
///
/// 仓库分类编号
public void DeleteStorageInfoByStorageTypeID(string strStorageTypeID)
{
string sqlstr =@"delete 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);
}
}
}