/// <summary>
/// 获得已充值过的公司汇总
/// </summary>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageIndex">当前页索引</param>
/// <param name="recordCount">总记录数</param>
/// <param name="companyName">公司名称 为空时不做为查询条件</param>
/// <returns></returns>
public virtual IList <EyouSoft.Model.SMSStructure.AccountDetailInfo> GetAllPayedCompanys(int pageSize, int pageIndex, ref int recordCount, string companyName)
{
IList <EyouSoft.Model.SMSStructure.AccountDetailInfo> items = new List <EyouSoft.Model.SMSStructure.AccountDetailInfo>();
StringBuilder strSql = new StringBuilder();
string tableName = "view_SMS_AllPayedCompany";
string fields = "CompanyID,CompanyName,ContactName,ContactTel,ContactMobile,ContactMQ,AccountMoney,AccountSMSNumber";
string orderByString = "AccountSMSNumber,AccountMoney,CompanyID";
string primaryKey = "CompanyID";
StringBuilder cmdQuery = new StringBuilder();
if (!string.IsNullOrEmpty(companyName))
{
cmdQuery.AppendFormat("CompanyName LIKE '%{0}%'", companyName);
}
using (IDataReader rdr = DbHelper.ExecuteReader(base.SMSStore, pageSize, pageIndex, ref recordCount, tableName, primaryKey, fields, cmdQuery.ToString(), orderByString))
{
while (rdr.Read())
{
EyouSoft.Model.SMSStructure.AccountDetailInfo item = new EyouSoft.Model.SMSStructure.AccountDetailInfo();
item.CompanyId = rdr.GetString(rdr.GetOrdinal("CompanyID"));
item.CompanyName = rdr["CompanyName"].ToString();
item.ContactName = rdr["ContactName"].ToString();
item.Tel = rdr["ContactTel"].ToString();
item.Mobile = rdr["ContactMobile"].ToString();
item.MQId = rdr["ContactMQ"].ToString();
item.AccountMoney = Convert.ToDecimal(rdr["AccountMoney"].ToString());
//item.AccountSMSNumber = Convert.ToInt32(rdr["AccountSMSNumber"].ToString());
items.Add(item);
}
}
return(items);
}
/// <summary>
/// 短信剩余统计
/// </summary>
/// <param name="pageSize">每页显示记录数量</param>
/// <param name="pageIndex">当前页索引</param>
/// <param name="recordCount">总记录数</param>
/// <param name="expression">剩余金额的数值表达式</param>
/// <param name="provinceId">省份编号 为null时不做为查询条件</param>
/// <param name="cityId">城市编号 为null时不做为查询条件</param>
/// <param name="companyName">公司名称 为null时不做为查询条件</param>
/// <param name="userAreas">用户分管的区域范围 为null时不做为查询条件</param>
/// <returns></returns>
public virtual IList <EyouSoft.Model.SMSStructure.AccountDetailInfo> RemnantStats(int pageSize, int pageIndex, ref int recordCount, double expression, int?provinceId, int?cityId, string companyName, string userAreas)
{
IList <EyouSoft.Model.SMSStructure.AccountDetailInfo> items = new List <EyouSoft.Model.SMSStructure.AccountDetailInfo>();
StringBuilder strSql = new StringBuilder();
string tableName = "view_SMS_AllPayedCompany";
string fields = "CompanyID,CompanyName,ContactName,ContactTel,ContactMobile,ContactMQ,AccountMoney,AccountSMSNumber,ProvinceId,CityId,ContactQQ";
string orderByString = "AccountSMSNumber,AccountMoney,CompanyID";
string primaryKey = "CompanyID";
StringBuilder cmdQuery = new StringBuilder();
cmdQuery.AppendFormat(" AccountMoney<={0} ", expression);
if (provinceId.HasValue)
{
cmdQuery.AppendFormat(" AND ProvinceId={0} ", provinceId.Value);
}
if (cityId.HasValue)
{
cmdQuery.AppendFormat(" AND CityId={0} ", cityId.Value);
}
if (!string.IsNullOrEmpty(companyName))
{
cmdQuery.AppendFormat(" AND CompanyName LIKE '%{0}%' ", companyName);
}
if (!string.IsNullOrEmpty(userAreas))
{
cmdQuery.AppendFormat(" AND CityId IN({0}) ", userAreas);
}
using (IDataReader rdr = DbHelper.ExecuteReader(base.SMSStore, pageSize, pageIndex, ref recordCount, tableName, primaryKey, fields, cmdQuery.ToString(), orderByString))
{
while (rdr.Read())
{
EyouSoft.Model.SMSStructure.AccountDetailInfo item = new EyouSoft.Model.SMSStructure.AccountDetailInfo();
item.CompanyId = rdr.GetString(rdr.GetOrdinal("CompanyID"));
item.CompanyName = rdr["CompanyName"].ToString();
item.ContactName = rdr["ContactName"].ToString();
item.Tel = rdr["ContactTel"].ToString();
item.Mobile = rdr["ContactMobile"].ToString();
item.MQId = rdr["ContactMQ"].ToString();
item.AccountMoney = Convert.ToDecimal(rdr["AccountMoney"].ToString());
//item.AccountSMSNumber = Convert.ToInt32(rdr["AccountSMSNumber"].ToString());
item.PrivinceId = rdr.GetInt32(rdr.GetOrdinal("ProvinceId"));
item.CityId = rdr.GetInt32(rdr.GetOrdinal("CityId"));
item.QQ = rdr["ContactQQ"].ToString();
items.Add(item);
}
}
return(items);
}