/// <summary> /// 获取影院列表 /// </summary> /// <returns></returns> public IList <Cinema> getList(string name, string area) { string strSql = "select id, name, " + "areaID, (select z.name from AreaInfo z where z.id=areaID) as area, " + "roomNum, person, tel, address from Cinema "; if (name.Trim() != "" || area.Trim() != "") { strSql += "where "; } if (name.Trim() != "") { strSql += "name like '%" + name + "%' "; } if (area.Trim() != "") { if (name.Trim() != "") { strSql += "and "; } strSql += "areaID=" + area; } IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <Cinema> list = DynamicBuilder <Cinema> .ConvertToList(dr); return(list); }
/// <summary> /// 登录验证 /// </summary> /// <param name="userID">用户ID</param> /// <param name="pwd">密码</param> /// <returns></returns> public UserInfo checkUserID(string userID, string pwd) { UserInfo info = null; string strSql = "select id, userID, powergroupID, password, " + "(case state when 1 then '使用' when 0 then '禁止' end) as state, name, " + "(case sex when 1 then '男' when 2 then '女' end) as sex, card, tel, address, date " + "from UserInfo " + "where userID=@userID and password=@pwd"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@userID", userID), new SqlParameter("@pwd", pwd) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <UserInfo> list = DynamicBuilder <UserInfo> .ConvertToList(dr); if (list != null && list.Count > 0) { info = list[0]; } return(info); }
/// <summary> /// 获取用户列表 /// </summary> /// <param name="userID">用户名</param> /// <param name="beginDate">开始日期</param> /// <param name="endDate">结束日期</param> /// <returns></returns> public IList <UserInfo> getList(string userID, string beginDate, string endDate) { string strSql = "select id, userID, powergroupID, password, " + "(case state when 1 then '使用' when 0 then '禁止' end) as state, name, " + "(case sex when 1 then '男' when 2 then '女' end) as sex, card, tel, address, date " + "from UserInfo "; string where = ""; if (userID.Trim() != "") { where += " userID like '%" + userID + "%' "; } if (beginDate.Trim() != "" && endDate.Trim() != "") { if (userID.Trim() != "") { where += "or "; } where += " date between '" + beginDate + "' and '" + endDate + "'"; } if (!string.IsNullOrEmpty(where)) { strSql += "where" + where; } IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <UserInfo> list = DynamicBuilder <UserInfo> .ConvertToList(dr); return(list); }
/// <summary> /// 获取客户资料列表 /// </summary> /// <param name="en"></param> /// <returns></returns> public IList <CustomerInfo> getList(CustomerInfo en, string userID) { string strSql = "select a.id, a.name, " + "a.channelTypeID, (select z.name from ChannelType z where z.id=a.channelTypeID) as channelType, " + "a.person, a.tel, a.officeTel, a.email, a.fex, a.address, " + "a.salesmanID, (select z.name from UserInfo z where z.id=a.salesmanID) as salesman, a.memo, a.mDate, " + "a.stateID, (select z.state from CustomerState z where z.id=a.stateID) as state " + "from CustomerInfo a, ContractBinding b where a.salesmanID=b.personID and b.userID=@id "; if (en.name.Trim() != "") { strSql += " and a.name like '%" + en.name + "%' "; } if (en.channelTypeID != 0) { strSql += " and a.channelTypeID=" + en.channelTypeID + " "; } if (en.salesmanID != 0) { strSql += " and a.salesmanID=" + en.salesmanID + " "; } SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", userID) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <CustomerInfo> list = DynamicBuilder <CustomerInfo> .ConvertToList(dr); return(list); }
/// <summary> /// 搜索投放信息 /// </summary> /// <param name="cinema"></param> /// <param name="begin"></param> /// <param name="end"></param> /// <returns></returns> public IList <AdvListItem> getList(string cinema, string begin, string end) { string strSql = "select a.id, a.cinemaID, (select z.name from Cinema z where z.id=a.cinemaID) as cinemaName, " + "b.version, c.begintime, c.endtime, a.orderID, a.contractID, b.name as contractName " + "from PutinInfo a, ContractInfo b, OrderInfo c where a.contractID=b.contractID and b.contractID=c.contractID "; string where = ""; if (cinema.Trim() != "") { where += "a.cinemaID='" + cinema + "'"; } if (begin.Trim() != "" && end.Trim() != "") { if (where != "") { where += " and "; } where += "(c.endtime>='" + begin + "' and c.begintime<='" + end + "')"; } if (where != "") { strSql += "and " + where; } IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <AdvListItem> list = DynamicBuilder <AdvListItem> .ConvertToList(dr); return(list); }
/// <summary> /// 搜索各种到账类型列表 /// </summary> /// <returns></returns> public IList <PayList> getList(string contractID, string channel, string begin, string end, string person, int type, string userID) { string strSql = "select a.contractID, a.money, count(b.money) as total into #ddd " + "from ContractInfo a join ContractBinding c on a.personID=c.personID left join CustomerPay b on a.contractID=b.contractID where c.userID=@id "; if (contractID.Trim() != "") { strSql += " and a.contractID='" + contractID + "' "; } if (channel.Trim() != "") { strSql += " and a.channelID='" + channel + "' "; } if (person.Trim() != "") { strSql += " and a.personID='" + person + "' "; } if (begin.Trim() != "" && end.Trim() != "") { strSql += " and a.mDate between '" + begin + "' and '" + end + "' "; } strSql += " group by a.contractID, a.money "; strSql += "select a.contractID, a.name, a.money, " + "a.channelID, (select z.name from Channel z where z.id=a.channelID) as channelName, " + "a.personID, (select z.name from UserInfo z where z.id=a.personID) as personName, " + "b.money as payMoney, null as payPercent, b.date " + "from ContractInfo a, CustomerPay b, #ddd c where a.contractID=b.contractID and a.contractID=c.contractID "; switch (type) { case 0: strSql += " and c.total=0 "; break; case 1: strSql += " and c.money<>c.total "; break; case 2: strSql += " and c.money=c.total "; break; } strSql += " drop table #ddd "; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", userID) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <PayList> list = DynamicBuilder <PayList> .ConvertToList(dr); return(list); }
/// <summary> /// 根据编号获取信息 /// </summary> /// <param name="id">编号</param> /// <returns></returns> public OrderInfo getOrderInfo(string id) { string strSql = "select a.id, a.contractID, b.name as contractName, a.orderID, " + "(select z.target from ADCostTarget z where z.id=a.costTargetID) as costTargetName, a.costTargetID, " + "b.customerID, (select z.name from CustomerInfo z where x.id=b.customerID) as customerName, " + "a.roomNum, a.begintime, a.endtime, a.memo, " + "(select z.target from MakeCostTarget z where z.id=a.makeTargetID) as makeTargetName, a.makeTargetID, " + "playReport, reportTime, mdate from OrderInfo a, ContractInfo b where a.contractID=b.contractID and a.id=@id"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", id) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <OrderInfo> list = DynamicBuilder <OrderInfo> .ConvertToList(dr); if (list != null && list.Count > 0) { return(list[0]); } else { return(null); } }
/// <summary> /// 搜索结算列表 /// </summary> /// <returns></returns> public IList <MakeCost> getList(string id, string contractID, string channel, string state, string begin, string end) { string strSql = "select a.id, a.orderID, b.contractID, c.name, " + "(select z.name from Channel z where z.id=c.channelID) as channelName, " + "c.version, c.money as contractMoney, " + "a.money, (select z.name from AccountState z where z.id=a.state) as state, a.date from ADCostAccount a, OrderInfo b, ContractInfo c where a.orderID=b.orderID and b.contractID=c.contractID "; string where = ""; if (id.Trim() != "") { where += "a.orderID like '%" + id + "%'"; } if (contractID.Trim() != "") { if (where != "") { where += " and "; } where += "c.contractID like '%" + contractID + "%'"; } if (channel.Trim() != "") { if (where != "") { where += " and "; } where += "c.channelID=" + channel; } if (state.Trim() != "") { if (where != "") { where += " and "; } where += "a.state=" + state; } if (begin.Trim() != "" && end.Trim() != "") { if (where != "") { where += " and "; } where += "date between '" + begin + "' and '" + end + "'"; } if (where != "") { strSql += "and " + where; } IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <MakeCost> list = DynamicBuilder <MakeCost> .ConvertToList(dr); return(list); }
/// <summary> /// 根据合同编号获取合同信息 /// </summary> /// <param name="id">编号</param> /// <returns></returns> public ContractInfo getEntity(string id) { string strSql = "select id, contractID, name, customerID, (select z.name from CustomerInfo z where z.id=customerID) as customerName, " + "version, price, roomNum, makeCost, backMoney, money, " + "type, (select z.name from ContractType z where z.id=type) as typeName, " + "channelID, (select z.name from Channel z where z.id=channelID) as channelName, " + "begintime, endtime, ZQ, personID, (select z.name from UserInfo z where z.id=personID) as personName, " + "memo, mDate, billState, (select z.name from BillState z where z.id=billState) as billStateName, " + "state, (select z.name from ContractState z where z.id=state) as stateName, editTime from ContractInfo " + "where contractID=@id"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", id) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <ContractInfo> list = DynamicBuilder <ContractInfo> .ConvertToList(dr); if (list != null && list.Count > 0) { return(list[0]); } else { return(null); } }
/// <summary> /// 获取模块列表 /// </summary> /// <returns></returns> public IList <Module> getList() { string strSql = "select id, name, [check] from SystemModule"; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <Module> list = DynamicBuilder <Module> .ConvertToList(dr); return(list); }
/// <summary> /// 获取权限组列表 /// </summary> /// <returns></returns> public IList <PowerGroup> getList() { string strSql = "select id, name, memo from PowerGroup"; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <PowerGroup> list = DynamicBuilder <PowerGroup> .ConvertToList(dr); return(list); }
/// <summary> /// 搜索订单列表 /// </summary> /// <returns></returns> public IList <OrderInfo> getList(string orderID, string contractID, string begin, string end, string person, string adTarget, string makeTarget, string userID) { string strSql = "select a.id, a.contractID, b.name as contractName, a.orderID, " + "(select z.target from ADCostTarget z where z.id=a.costTargetID) as costTargetName, a.costTargetID, " + "b.customerID, (select z.name from CustomerInfo z where z.id=b.customerID) as customerName, " + "a.roomNum, a.begintime, a.endtime, a.memo, " + "(select z.target from MakeCostTarget z where z.id=a.makeTargetID) as makeTargetName, a.makeTargetID, " + "a.playReport, a.reportTime, a.mdate " + "from OrderInfo a, ContractInfo b, ContractBindingn c where a.contractID=b.contractID and b.personID=c.personID and c.userID=@id "; string where = ""; if (orderID.Trim() != "") { where += "and a.orderID like '%" + orderID + "%' "; } if (contractID.Trim() != "") { where += "and a.contractID like '%" + contractID + "%' "; } if (begin.Trim() != "" && end.Trim() != "") { where += "and (a.endtime>='" + begin + "' and a.begintime<='" + end + "') "; } if (person.Trim() != "") { where += "and b.personID='" + person + "' "; } if (adTarget.Trim() != "") { where += "and a.costTargetID='" + adTarget + "' "; } if (makeTarget.Trim() != "") { where += "and a.makeTargetID='" + makeTarget + "' "; } if (where != "") { strSql += where; } SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", userID) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <OrderInfo> list = DynamicBuilder <OrderInfo> .ConvertToList(dr); return(list); }
/// <summary> /// 获取发票状态列表 /// </summary> /// <returns></returns> public IList <BillState> getList() { string strSql = "select id, name from BillState"; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <BillState> list = DynamicBuilder <BillState> .ConvertToList(dr); return(list); }
/// <summary> /// 获取对象列表 /// </summary> /// <returns></returns> public IList <ADCostTarget> getList() { string strSql = "select id, target, memo from ADCostTarget"; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <ADCostTarget> list = DynamicBuilder <ADCostTarget> .ConvertToList(dr); return(list); }
/// <summary> /// 获取用户权限列表 /// </summary> /// <returns></returns> public IList <UserGroup> getList() { string strSql = "select a.userID, a.name as userName, a.powergroupID as groupID, b.name as groupName " + "from UserInfo a, PowerGroup b where a.powergroupID=b.id"; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <UserGroup> list = DynamicBuilder <UserGroup> .ConvertToList(dr); return(list); }
/// <summary> /// 获取影厅列表 /// </summary> /// <returns></returns> public IList <CinemaRoom> getList() { string strSql = "select id, cinemaID, (select z.name from Cinema z where z.id=cinemaID) as cinema, " + "room, typeID, (select z.type from CinemaRoomType z where z.id=typeID) as type, " + "memo from CinemaRoom"; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <CinemaRoom> list = DynamicBuilder <CinemaRoom> .ConvertToList(dr); return(list); }
/// <summary> /// 获取影院列表 /// </summary> /// <returns></returns> public IList <Cinema> getList() { string strSql = "select id, name, " + "areaID, (select z.name from AreaInfo z where z.id=areaID) as area, " + "roomNum, person, tel, address from Cinema"; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <Cinema> list = DynamicBuilder <Cinema> .ConvertToList(dr); return(list); }
/// <summary> /// 获取用户列表 /// </summary> /// <returns></returns> public IList <UserInfo> getList() { string strSql = "select id, userID, powergroupID, password, " + "(case state when 1 then '使用' when 0 then '禁止' end) as state, name, " + "(case sex when 1 then '男' when 2 then '女' end) as sex, card, tel, address, date " + "from UserInfo"; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <UserInfo> list = DynamicBuilder <UserInfo> .ConvertToList(dr); return(list); }
/// <summary> /// 获取结算列表 /// </summary> /// <returns></returns> public IList <MakeCost> getList() { string strSql = "select a.id, a.orderID, b.contractID, c.name, " + "(select z.name from Channel z where z.id=c.channelID) as channelName, " + "c.version, c.money as contractMoney, " + "a.money, (select z.name from AccountState z where z.id=a.state) as state, a.date from ADCostAccount a, OrderInfo b, ContractInfo c where a.orderID=b.orderID and b.contractID=c.contractID "; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <MakeCost> list = DynamicBuilder <MakeCost> .ConvertToList(dr); return(list); }
/// <summary> /// 获取发票列表 /// </summary> /// <returns></returns> public IList <Bill> getList() { string strSql = "select a.id, a.contractID, " + "(select z.name from ContractInfo z where z.contractID=a.contractID) as contractName, " + "(select z.money from ContractInfo z where z.contractID=a.contractID) as contractMoney, " + "a.type, (select z.name from BillType z where z.id=a.type) as typeName, a.money, a.date from Bill a"; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <Bill> list = DynamicBuilder <Bill> .ConvertToList(dr); return(list); }
/// <summary> /// 获取发票列表 /// </summary> /// <returns></returns> public IList <ReceiveBill> getList() { string strSql = "select a.id, a.orderID, b.contractID, " + "(select z.name from ContractInfo z where z.contractID=b.contractID) as contractName, " + "(select z.money from ContractInfo z where z.contractID=b.contractID) as contractMoney, " + "a.type, (select z.name from BillType z where z.id=a.type) as typeName, a.costTargetID as targetID, " + "(case when a.type=1 then (select z.target from ADCostTarget z where z.id=a.costTargetID) else (select z.target from MakeCostTarget z where z.id=a.costTargetID) end) as targetName, " + "a.money, a.date " + "from ReceiveBill a, OrderInfo b where a.orderID=b.orderID"; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql); IList <ReceiveBill> list = DynamicBuilder <ReceiveBill> .ConvertToList(dr); return(list); }
/// <summary> /// 获取影厅列表 /// </summary> /// <param name="id">影院ID</param> /// <returns></returns> public IList <CinemaRoom> getList(string id) { string strSql = "select id, cinemaID, (select z.name from Cinema z where z.id=cinemaID) as cinema, " + "room, typeID, (select z.type from CinemaRoomType z where z.id=typeID) as type, " + "memo from CinemaRoom where cinemaID=@id"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", id) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <CinemaRoom> list = DynamicBuilder <CinemaRoom> .ConvertToList(dr); return(list); }
/// <summary> /// 根据订单编号获取投放信息 /// </summary> /// <returns></returns> public IList <PutinInfo> getListByContract(string id) { string strSql = "select id, contractID, orderID, " + "cinemaID, (select z.name from Cinema z where z.id=cinemaID) as cinemaName, " + "cinemaRoomID, (select z.room from CinemaRoom z where z.id=cinemaRoomID) as cinemaRoomName, " + "roomTypeID, (select z.type from CinemaRoomType z where z.id=roomTypeID) as roomType, memo from PutinInfo where contractID=@id"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", id) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <PutinInfo> list = DynamicBuilder <PutinInfo> .ConvertToList(dr); return(list); }
/// <summary> /// 获取投放信息 /// </summary> /// <returns></returns> public IList <PutinListItem> getList(string page, string rows) { string strSql = "EXECUTE ProPageHelper 'PutinInfo a, ContractInfo b, OrderInfo c', " + "'a.id, a.cinemaID, (select z.name from Cinema z where z.id=a.cinemaID) as cinemaName, a.cinemaRoomID, (select z.room from CinemaRoom z where z.id=a.cinemaRoomID) as cinemaRoomName, b.version, c.begintime, a.orderID, a.contractID, a.roomTypeID, (select z.type from CinemaRoomType z where z.id=a.roomTypeID) as roomType, b.price, b.ZQ ', " + "'a.id', @rows, @page, 1, 0, 0, 'a.orderID=c.orderID and b.contractID=c.contractID' "; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@page", page), new SqlParameter("@rows", rows) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <PutinListItem> list = DynamicBuilder <PutinListItem> .ConvertToList(dr); return(list); }
/// <summary> /// 获取权限列表 /// </summary> /// <returns></returns> public IList <PowerGroupPower> getList(string id) { string strSql = "select a.id, a.groupID, a.moduleID, b.name as moduleName, a.power, " + "(case a.power when 0 then '禁止' when 1 then '可读' when 2 then '可写' end) as powerName " + "from PowerGroupPower a, SystemModule b " + "where a.moduleID = b.id and a.groupID = @group"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@group", id) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <PowerGroupPower> list = DynamicBuilder <PowerGroupPower> .ConvertToList(dr); return(list); }
/// <summary> /// 获取到账列表 /// </summary> /// <returns></returns> public IList <PayList> getListByContract(string id) { string strSql = "select a.contractID, a.name, a.money, " + "a.channelID, (select z.name from Channel z where z.id=a.channelID) as channelName, " + "a.personID, (select z.name from UserInfo z where z.id=a.personID) as personName, " + "b.money as payMoney, null as payPercent, b.date " + "from ContractInfo a, CustomerPay b where a.contractID=b.contractID and a.contractID=@id"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", id) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <PayList> list = DynamicBuilder <PayList> .ConvertToList(dr); return(list); }
/// <summary> /// 获取结算列表 /// </summary> /// <returns></returns> public IList <ADCost> getList(string id) { string strSql = "select a.orderID, b.contractID, c.name, " + "(select z.name from Channel z where z.id=c.channelID) as channelName, " + "(select z.name from UserInfo z where z.id=c.personID) as personName, " + "c.money as contractMoney, " + "(select z.target from ADCostTarget z where z.id=b.costTargetID) as costTargetName, " + "a.money, (select z.name from AccountState z where z.id=a.state) as state, a.date, a.memo from ADCostAccount a, OrderInfo b, ContractInfo c " + "where a.orderID=b.orderID and b.contractID=c.contractID and a.orderID=@id"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", id) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <ADCost> list = DynamicBuilder <ADCost> .ConvertToList(dr); return(list); }
/// <summary> /// 获取订单列表 /// </summary> /// <returns></returns> public IList <OrderInfo> getList(string userID) { string strSql = "select a.id, a.contractID, b.name as contractName, a.orderID, " + "(select z.target from ADCostTarget z where z.id=a.costTargetID) as costTargetName, a.costTargetID, " + "b.customerID, (select z.name from CustomerInfo z where z.id=b.customerID) as customerName, " + "a.roomNum, a.begintime, a.endtime, a.memo, " + "(select z.target from MakeCostTarget z where z.id=a.makeTargetID) as makeTargetName, a.makeTargetID, " + "a.playReport, a.reportTime, a.mdate " + "from OrderInfo a, ContractInfo b, ContractBinding c where a.contractID=b.contractID and b.personID=c.personID and c.userID=@id"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", userID) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <OrderInfo> list = DynamicBuilder <OrderInfo> .ConvertToList(dr); return(list); }
/// <summary> /// 搜索投放信息 /// </summary> /// <param name="cinema"></param> /// <param name="room"></param> /// <param name="version"></param> /// <param name="begin"></param> /// <param name="end"></param> /// <returns></returns> public IList <PutinListItem> getList(string cinema, string room, string version, string begin, string end, string page, string rows) { #region 条件部分 string where = "a.orderID=c.orderID and b.contractID=c.contractID"; if (cinema.Trim() != "") { where += " and a.cinemaID=''" + cinema + "''"; } if (room.Trim() != "") { where += " and a.cinemaRoomID=''" + room + "''"; } if (version.Trim() != "") { where += " and b.version=''" + version + "''"; } if (begin.Trim() != "" && end.Trim() != "") { where += " and (c.begintime between''" + begin + "'' and ''" + end + "'')"; } #endregion string strSql = "EXECUTE ProPageHelper 'PutinInfo a, ContractInfo b, OrderInfo c', " + "'a.id, a.cinemaID, (select z.name from Cinema z where z.id=a.cinemaID) as cinemaName, a.cinemaRoomID, (select z.room from CinemaRoom z where z.id=a.cinemaRoomID) as cinemaRoomName, b.version, c.begintime, a.orderID, a.contractID, a.roomTypeID, (select z.type from CinemaRoomType z where z.id=a.roomTypeID) as roomType, b.price, b.ZQ ', " + "'a.id', @rows, @page, 1, 0, 0, '" + where + "' "; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@page", page), new SqlParameter("@rows", rows) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <PutinListItem> list = DynamicBuilder <PutinListItem> .ConvertToList(dr); return(list); }
/// <summary> /// 获取合同列表 /// </summary> /// <returns></returns> public IList <ContractInfo> getList(string userID) { string strSql = "select a.id, a.contractID, a.name, a.customerID, (select z.name from CustomerInfo z where z.id=a.customerID) as customerName, " + "a.version, a.price, a.roomNum, a.makeCost, a.backMoney, a.money, " + "a.type, (select z.name from ContractType z where z.id=a.type) as typeName, " + "a.channelID, (select z.name from Channel z where z.id=a.channelID) as channelName, " + "a.begintime, a.endtime, a.ZQ, a.personID, (select z.name from UserInfo z where z.id=a.personID) as personName, " + "a.memo, a.mDate, a.billState, (select z.name from BillState z where z.id=a.billState) as billStateName, " + "a.state, (select z.name from ContractState z where z.id=a.state) as stateName, a.editTime " + "from ContractInfo a, ContractBinding b where a.personID=b.personID and b.userID=@id"; SqlParameter[] param = new SqlParameter[] { new SqlParameter("@id", userID) }; IDataReader dr = SqlHelper.ExecuteReader(BaseHelper.DBConnStr, CommandType.Text, strSql, param); IList <ContractInfo> list = DynamicBuilder <ContractInfo> .ConvertToList(dr); return(list); }