| | |
| | | /// <summary> |
| | | /// 产品操作 |
| | | /// </summary> |
| | | public class GwProductDao |
| | | public class GwProductDao : IDisposable |
| | | { |
| | | public void Dispose() |
| | | { |
| | | } |
| | | |
| | | private static GwProductDao _instance; |
| | | public static GwProductDao Instance |
| | |
| | | } |
| | | return _instance; |
| | | } |
| | | } |
| | | |
| | | |
| | | public List<GwProduct> LoadInfoList(int classes, string name, int pageSize, int PageIndex, out int recordcount) |
| | | { |
| | | |
| | | List<GwProduct> list = new List<GwProduct>(); |
| | | recordcount = 0; |
| | | try |
| | | { |
| | | StringBuilder builder = new StringBuilder(); |
| | | builder.Append("from GW_PRODUCT where 1=1"); |
| | | if (classes == -1) |
| | | { |
| | | builder.Append(" AND (CLASSES != " + classes + " OR CLASSES IS NULL) " ); |
| | | } |
| | | else |
| | | { |
| | | builder.Append(" AND CLASSES = " + classes + " "); |
| | | } |
| | | if ((name != null) && (name != "")) |
| | | { |
| | | builder.Append(" and name like '%" + name + "%'"); |
| | | } |
| | | using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + builder.ToString(), OracleHelper.Connection)) |
| | | { |
| | | while (reader.Read()) |
| | | { |
| | | recordcount = this.ReadCount(reader); |
| | | } |
| | | } |
| | | using (OracleDataReader reader2 = OracleHelper.ExecuteReader(PubConstant.doOracleSql(PageIndex, pageSize, recordcount, "select * " + builder.ToString()).ToString() + " order by path, sort ", OracleHelper.Connection )) |
| | | { |
| | | while (reader2.Read()) |
| | | { |
| | | GwProduct o = new GwProduct(); |
| | | if (this.ReadInfo(reader2, o)) |
| | | { |
| | | list.Add(o); |
| | | } |
| | | } |
| | | } |
| | | } |
| | | catch (Exception exception) |
| | | { |
| | | LogHelper.Error(exception); |
| | | return list; |
| | | } |
| | | return list; |
| | | |
| | | } |
| | | |
| | | /// <summary> |
| | | /// 获取产品及产品分类列表 |
| | | /// </summary> |
| | | /// <param name="clientID"></param> |
| | | /// <returns></returns> |
| | | public List<GwProduct> GwProductList(int classes, string name ) |
| | | { |
| | | List<GwProduct> list = new List<GwProduct>(); |
| | | //recordcount = 0; // |
| | | try |
| | | { |
| | | StringBuilder stringBuilder = new StringBuilder(); |
| | | stringBuilder.Append("SELECT * FROM GW_PRODUCT WHERE CLASSES=:CLASSES AND NAME=:NAME ORDER BY ID "); |
| | | OracleParameter[] oracleParameterArray = new OracleParameter[2] |
| | | { |
| | | new OracleParameter(":CLASSES", (object) classes), new OracleParameter(":NAME", (object) ("%" + name + "%")) |
| | | }; |
| | | |
| | | |
| | | //using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + builder.ToString(), OracleHelper.Connection, cmdParms)) |
| | | //{ |
| | | // while (reader.Read()) |
| | | // { |
| | | // recordcount = this.ReadCount(reader); |
| | | // } |
| | | //} |
| | | |
| | | using (OracleDataReader reader = OracleHelper.ExecuteReader(stringBuilder.ToString() +" order by path, sort ", OracleHelper.Connection, oracleParameterArray)) |
| | | { |
| | | while (((DbDataReader)reader).Read()) |
| | | { |
| | | GwProduct o = new GwProduct(); |
| | | if (this.ReadInfo(reader, o)) |
| | | list.Add(o); |
| | | } |
| | | } |
| | | } |
| | | catch (Exception ex) |
| | | { |
| | | LogHelper.Error(ex); |
| | | return list; |
| | | } |
| | | return list; |
| | | } |
| | | |
| | | //根据条件获取产品或产品分类的ID和名称 |
| | | public List<GwProduct> getAllList(GwProduct bean) |
| | | { |
| | | |
| | | List<GwProduct> list = new List<GwProduct>(); |
| | | try |
| | | { |
| | | StringBuilder builder = new StringBuilder(); |
| | | builder.Append("from GW_PRODUCT where 1=1"); |
| | | if (bean.Classes == -1) |
| | | { |
| | | builder.Append(" AND (CLASSES != " + bean.Classes + " OR CLASSES IS NULL) "); |
| | | } |
| | | else |
| | | { |
| | | builder.Append(" AND CLASSES = " + bean.Classes + " "); |
| | | } |
| | | |
| | | if (!string.IsNullOrEmpty(bean.ParentId) ) |
| | | { |
| | | builder.Append(" AND PARENT_ID = " + bean.ParentId + " "); |
| | | } |
| | | |
| | | if (bean.IsDefault == -1) |
| | | { |
| | | builder.Append(" AND (IS_DEFAULT != " + bean.IsDefault + " OR IS_DEFAULT IS NULL) "); |
| | | } |
| | | else |
| | | { |
| | | builder.Append(" AND IS_DEFAULT = " + bean.IsDefault + " "); |
| | | } |
| | | |
| | | if (bean.IsEnable == -1) |
| | | { |
| | | builder.Append(" AND (IS_ENABLE != " + bean.IsEnable + " OR IS_ENABLE IS NULL) "); |
| | | } |
| | | else |
| | | { |
| | | builder.Append(" AND IS_ENABLE = " + bean.IsEnable + " "); |
| | | } |
| | | |
| | | using (OracleDataReader reader = OracleHelper.ExecuteReader("select id, name, classes, is_enable, is_default " + builder.ToString() + " order by path, sort ", OracleHelper.Connection)) |
| | | { |
| | | while (reader.Read()) |
| | | { |
| | | GwProduct o = new GwProduct(); |
| | | if (this.ReadInfo(reader, o)) |
| | | { |
| | | list.Add(o); |
| | | } |
| | | } |
| | | } |
| | | } |
| | | catch (Exception exception) |
| | | { |
| | | LogHelper.Error(exception); |
| | | return list; |
| | | } |
| | | return list; |
| | | |
| | | } |
| | | |
| | | /// <summary> |
| | |
| | | } |
| | | return list; |
| | | } |
| | | |
| | | //添加产品或产品分类信息 |
| | | public bool Add(GwProduct o) |
| | | { |
| | | StringBuilder stringBuilder = new StringBuilder(); |
| | | stringBuilder.Append("INSERT INTO GW_PRODUCT ( "); |
| | | stringBuilder.Append(" ID, NAME, ICON, INSTRUCTIONS, CLASSES, PARENT_ID, PATH, IS_DEFAULT, IS_ENABLE, SORT, ROUTER_TYPE, CM_OP_ID, CU_OP_ID, CT_OP_ID "); |
| | | stringBuilder.Append(" , CM_GROUPID, CU_GROUPID, CT_GROUPID, CM_EXT_PARAMS, CU_EXT_PARAMS, CT_EXT_PARAMS, PRICE, GIVING_NUM, REMARK, CREATE_TIME, CREATOR " ); |
| | | //stringBuilder.Append(" , UPDATE_TIME, UPDATEOR "); |
| | | stringBuilder.Append(" ) "); |
| | | stringBuilder.Append(" VALUES (:ID, :NAME, :ICON, :INSTRUCTIONS, :CLASSES, :PARENT_ID, :PATH, :IS_DEFAULT, :IS_ENABLE, :SORT, :ROUTER_TYPE, :CM_OP_ID, :CU_OP_ID, :CT_OP_ID " ); |
| | | stringBuilder.Append(" , :CM_GROUPID, :CU_GROUPID, :CT_GROUPID, :CM_EXT_PARAMS, :CU_EXT_PARAMS, :CT_EXT_PARAMS, :PRICE, :GIVING_NUM, :REMARK, :CREATE_TIME, :CREATOR " ); |
| | | //stringBuilder.Append(" , :UPDATE_TIME, :UPDATEOR "); |
| | | stringBuilder.Append(" ) "); |
| | | |
| | | string sql = stringBuilder.ToString(); |
| | | |
| | | return OracleHelper.ExecuteSql(sql, |
| | | OracleHelper.Connection, |
| | | new OracleParameter(":ID", (object)o.Id), |
| | | new OracleParameter(":NAME", (object)o.Name), |
| | | new OracleParameter(":ICON", (object)o.Icon), |
| | | new OracleParameter(":INSTRUCTIONS", (object)o.Instructions), |
| | | new OracleParameter(":CLASSES", (object)o.Classes), |
| | | new OracleParameter(":PARENT_ID", (object)o.ParentId), |
| | | new OracleParameter(":PATH", (object)o.Path), |
| | | new OracleParameter(":IS_DEFAULT", (object)o.IsDefault), |
| | | new OracleParameter(":IS_ENABLE", (object)o.IsEnable), |
| | | new OracleParameter(":SORT", (object)o.Sort), |
| | | new OracleParameter(":ROUTER_TYPE", (object)o.RouterType), |
| | | new OracleParameter(":CM_OP_ID", (object)o.CmOpId), |
| | | new OracleParameter(":CU_OP_ID", (object)o.CuOpId), |
| | | new OracleParameter(":CT_OP_ID", (object)o.CtOpId), |
| | | new OracleParameter(":CM_GROUPID", (object)o.CmGroupid), |
| | | new OracleParameter(":CU_GROUPID", (object)o.CuGroupid), |
| | | new OracleParameter(":CT_GROUPID", (object)o.CtGroupid), |
| | | new OracleParameter(":CM_EXT_PARAMS", (object)o.CmExtParams), |
| | | new OracleParameter(":CU_EXT_PARAMS", (object)o.CuExtParams), |
| | | new OracleParameter(":CT_EXT_PARAMS", (object)o.CtExtParams), |
| | | new OracleParameter(":PRICE", (object)o.Price), |
| | | new OracleParameter(":GIVING_NUM", (object)o.GivingNum), |
| | | new OracleParameter(":REMARK", (object)o.Remark), |
| | | new OracleParameter(":CREATE_TIME", (object)o.CreateTime), |
| | | new OracleParameter(":CREATOR", (object)o.Creator) |
| | | //, new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime) |
| | | //, new OracleParameter(":UPDATEOR", (object)o.Updateor) |
| | | ) > 0; |
| | | } |
| | | |
| | | //删除 |
| | | public bool Delete(string id) |
| | | { |
| | | if (string.IsNullOrEmpty(id) ) |
| | | return false; |
| | | return OracleHelper.ExecuteSql("delete from GW_PRODUCT where ID=:ID", OracleHelper.Connection, new OracleParameter(":ID", (object)id)) > 0; |
| | | } |
| | | |
| | | //更新产品或产品分类信息 |
| | | public bool Update(GwProduct o) |
| | | { |
| | | OracleParameter[] cmdParms = new OracleParameter[] { |
| | | new OracleParameter(":ID", (object)o.Id), |
| | | new OracleParameter(":NAME", (object)o.Name), |
| | | new OracleParameter(":ICON", (object)o.Icon), |
| | | new OracleParameter(":INSTRUCTIONS", (object)o.Instructions), |
| | | new OracleParameter(":CLASSES", (object)o.Classes), |
| | | new OracleParameter(":PARENT_ID", (object)o.ParentId), |
| | | new OracleParameter(":PATH", (object)o.Path), |
| | | new OracleParameter(":IS_DEFAULT", (object)o.IsDefault), |
| | | new OracleParameter(":IS_ENABLE", (object)o.IsEnable), |
| | | new OracleParameter(":SORT", (object)o.Sort), |
| | | new OracleParameter(":ROUTER_TYPE", (object)o.RouterType), |
| | | new OracleParameter(":CM_OP_ID", (object)o.CmOpId), |
| | | new OracleParameter(":CU_OP_ID", (object)o.CuOpId), |
| | | new OracleParameter(":CT_OP_ID", (object)o.CtOpId), |
| | | new OracleParameter(":CM_GROUPID", (object)o.CmGroupid), |
| | | new OracleParameter(":CU_GROUPID", (object)o.CuGroupid), |
| | | new OracleParameter(":CT_GROUPID", (object)o.CtGroupid), |
| | | new OracleParameter(":CM_EXT_PARAMS", (object)o.CmExtParams), |
| | | new OracleParameter(":CU_EXT_PARAMS", (object)o.CuExtParams), |
| | | new OracleParameter(":CT_EXT_PARAMS", (object)o.CtExtParams), |
| | | new OracleParameter(":PRICE", (object)o.Price), |
| | | new OracleParameter(":GIVING_NUM", (object)o.GivingNum), |
| | | new OracleParameter(":REMARK", (object)o.Remark), |
| | | //new OracleParameter(":CREATE_TIME", (object)o.CreateTime), |
| | | //new OracleParameter(":CREATOR", (object)o.Creator), |
| | | new OracleParameter(":UPDATE_TIME", (object)o.UpdateTime), |
| | | new OracleParameter(":UPDATEOR", (object)o.Updateor) |
| | | }; |
| | | |
| | | StringBuilder stringBuilder = new StringBuilder(); |
| | | stringBuilder.Append("UPDATE GW_PRODUCT SET "); |
| | | stringBuilder.Append(" NAME=:NAME "); |
| | | stringBuilder.Append(" , ICON =:ICON "); |
| | | stringBuilder.Append(" , INSTRUCTIONS =:INSTRUCTIONS "); |
| | | stringBuilder.Append(" , CLASSES =:CLASSES "); |
| | | stringBuilder.Append(" , PARENT_ID =:PARENT_ID "); |
| | | stringBuilder.Append(" , PATH=:PATH, IS_DEFAULT=:IS_DEFAULT, IS_ENABLE=:IS_ENABLE, SORT=:SORT, ROUTER_TYPE=:ROUTER_TYPE "); |
| | | stringBuilder.Append(" , CM_OP_ID =:CM_OP_ID, CU_OP_ID=:CU_OP_ID, CT_OP_ID=:CT_OP_ID "); |
| | | stringBuilder.Append(" , CM_GROUPID=:CM_GROUPID, CU_GROUPID=:CU_GROUPID, CT_GROUPID=:CT_GROUPID, CM_EXT_PARAMS=:CM_EXT_PARAMS "); |
| | | stringBuilder.Append(" , CU_EXT_PARAMS =:CU_EXT_PARAMS, CT_EXT_PARAMS=:CT_EXT_PARAMS, PRICE=:PRICE, GIVING_NUM=:GIVING_NUM, REMARK=:REMARK "); |
| | | //stringBuilder.Append(" , CREATE_TIME =:CREATE_TIME, CREATOR=:CREATOR "); |
| | | stringBuilder.Append(" , UPDATE_TIME=:UPDATE_TIME, UPDATEOR=:UPDATEOR "); |
| | | stringBuilder.Append(" WHERE ID=:ID "); |
| | | |
| | | string sql = stringBuilder.ToString(); |
| | | return (OracleHelper.ExecuteSql(sql, OracleHelper.Connection, cmdParms) > 0); |
| | | } |
| | | |
| | | //获取信息 |
| | | public GwProduct Get(string productId) |
| | | { |
| | | GwProduct o = new GwProduct(); |
| | | if (string.IsNullOrEmpty(productId)) |
| | | return o; |
| | | using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from GW_PRODUCT where ID=:ID"), OracleHelper.Connection, new OracleParameter(":ID", (object)productId))) |
| | | { |
| | | if (((DbDataReader)reader).Read()) |
| | | { |
| | | this.ReadInfo(reader, o); |
| | | return o; |
| | | } |
| | | } |
| | | return o; |
| | | } |
| | | |
| | | //更新启用或停用 |
| | | public bool UpdateStatus(string id, string isEnable) |
| | | { |
| | | if (string.IsNullOrEmpty(id)) |
| | | return false; |
| | | return OracleHelper.ExecuteSql("UPDATE GW_PRODUCT SET IS_ENABLE=:IS_ENABLE where ID=:ID", OracleHelper.Connection, new OracleParameter(":IS_ENABLE", (object)isEnable), new OracleParameter(":ID", (object)id)) > 0; |
| | | } |
| | | |
| | | //更新关联的产品路径PATH |
| | | public bool UpdatePath(string id, string path) |
| | | { |
| | | if (string.IsNullOrEmpty(id)) |
| | | return false; |
| | | if (string.IsNullOrEmpty(path)) |
| | | return false; |
| | | |
| | | StringBuilder stringBuilder = new StringBuilder(); |
| | | stringBuilder.Append("UPDATE GW_PRODUCT SET PATH = :PATH||','||PARENT_ID "); |
| | | stringBuilder.Append(" WHERE ID IN ( "); |
| | | stringBuilder.Append(" SELECT ID FROM GW_PRODUCT START WITH ID = :ID CONNECT BY PRIOR ID = PARENT_ID "); |
| | | stringBuilder.Append(" ) "); |
| | | stringBuilder.Append(" AND ID NOT IN (:ID) "); |
| | | |
| | | return OracleHelper.ExecuteSql(stringBuilder.ToString(), OracleHelper.Connection, new OracleParameter(":PATH", (object)path), new OracleParameter(":ID", (object)id), new OracleParameter(":ID", (object)id)) > 0; |
| | | } |
| | | |
| | | //判断是否在产品中使用 |
| | | public int GwProductUseCount(string productId) |
| | | { |
| | | string str = string.Format("SELECT count(*) as Count from GW_PRODUCT where PARENT_ID=:PARENT_ID "); |
| | | List<OracleParameter> list = new List<OracleParameter>(); |
| | | int num = 0; |
| | | using (OracleDataReader reader = OracleHelper.ExecuteReader(str.ToString(), OracleHelper.Connection, new OracleParameter(":PARENT_ID", (object)productId)) ) |
| | | { |
| | | while (((DbDataReader)reader).Read()) |
| | | num = this.ReadCount(reader); |
| | | } |
| | | return num; |
| | | } |
| | | |
| | | //判断是否在客户账号中使用 |
| | | public int GwSpUseCount(string productId) |
| | | { |
| | | string str = string.Format("SELECT count(*) as Count from GW_SP where PRODUCT_ID=:PRODUCT_ID "); |
| | | List<OracleParameter> list = new List<OracleParameter>(); |
| | | int num = 0; |
| | | using (OracleDataReader reader = OracleHelper.ExecuteReader(str.ToString(), OracleHelper.Connection, new OracleParameter(":PRODUCT_ID", (object)productId))) |
| | | { |
| | | while (((DbDataReader)reader).Read()) |
| | | num = this.ReadCount(reader); |
| | | } |
| | | return num; |
| | | } |
| | | |
| | | //统计记录数 |
| | | private int ReadCount(OracleDataReader reader) |
| | | { |
| | | OracleReaderWrapper wrapper = new OracleReaderWrapper(reader); |
| | | return wrapper.GetInt("count", 0); |
| | | } |
| | | |
| | | //数据封装 |
| | | private bool ReadInfo(OracleDataReader reader, GwProduct o) |
| | | { |
| | | OracleReaderWrapper oracleReaderWrapper = new OracleReaderWrapper(reader); |
| | | o.Id = oracleReaderWrapper.GetString("ID", ""); |
| | | o.Name = oracleReaderWrapper.GetString("NAME", ""); |
| | | o.Icon = oracleReaderWrapper.GetString("ICON", ""); |
| | | o.Instructions = oracleReaderWrapper.GetString("INSTRUCTIONS", ""); |
| | | o.Classes = oracleReaderWrapper.GetInt("CLASSES", 0); |
| | | o.ParentId = oracleReaderWrapper.GetString("PARENT_ID", ""); |
| | | o.Path = oracleReaderWrapper.GetString("PATH", ""); |
| | | o.IsDefault = oracleReaderWrapper.GetInt("IS_DEFAULT", 0); |
| | | o.IsEnable = oracleReaderWrapper.GetInt("IS_ENABLE", 0); |
| | | o.Sort = oracleReaderWrapper.GetInt("SORT", 0); |
| | | o.RouterType = oracleReaderWrapper.GetInt("ROUTER_TYPE", 0); |
| | | o.CmOpId = oracleReaderWrapper.GetInt("CM_OP_ID", 0); |
| | | o.CuOpId = oracleReaderWrapper.GetInt("CU_OP_ID", 0); |
| | | o.CtOpId = oracleReaderWrapper.GetInt("CT_OP_ID", 0); |
| | | o.CmGroupid = oracleReaderWrapper.GetInt("CM_GROUPID", 0); |
| | | o.CuGroupid = oracleReaderWrapper.GetInt("CU_GROUPID", 0); |
| | | o.CtGroupid = oracleReaderWrapper.GetInt("CT_GROUPID", 0); |
| | | o.CmExtParams = oracleReaderWrapper.GetString("CM_EXT_PARAMS", ""); |
| | | o.CuExtParams = oracleReaderWrapper.GetString("CU_EXT_PARAMS", ""); |
| | | o.CtExtParams = oracleReaderWrapper.GetString("CT_EXT_PARAMS", ""); |
| | | o.Price = oracleReaderWrapper.GetInt("PRICE", 0); |
| | | o.GivingNum = oracleReaderWrapper.GetInt("GIVING_NUM", 0); |
| | | o.Remark = oracleReaderWrapper.GetString("REMARK", ""); |
| | | o.CreateTime = oracleReaderWrapper.GetDateTime("CREATE_TIME"); |
| | | o.Creator = oracleReaderWrapper.GetString("CREATOR", ""); |
| | | o.UpdateTime = oracleReaderWrapper.GetDateTime("UPDATE_TIME"); |
| | | o.Updateor = oracleReaderWrapper.GetString("UPDATEOR", ""); |
| | | return true; |
| | | } |
| | | |
| | | } |
| | | |
| | | } |