/// <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);
}
