/// <summary>
/// 得到一个对象实体
/// </summary>
public Eyousoft_yhq.Model.MSysDistrict GetSysDistrictModel(int ID)
{
Eyousoft_yhq.Model.MSysDistrict model = null;
string StrSql = "SELECT Id, Name,ProvinceId,CityId,HeaderLetter FROM tbl_SysDistrict WHERE Id=@Id";
DbCommand dc = this._db.GetSqlStringCommand(StrSql.ToString());
this._db.AddInParameter(dc, "Id", DbType.Int32, ID);
using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db))
{
if (dr.Read())
{
model = new Eyousoft_yhq.Model.MSysDistrict()
{
Id = dr.GetInt32(dr.GetOrdinal("Id")),
Name = dr.IsDBNull(dr.GetOrdinal("Name")) ? "" : dr.GetString(dr.GetOrdinal("Name")),
ProvinceId = dr.GetInt32(dr.GetOrdinal("ProvinceId")),
CityId = dr.GetInt32(dr.GetOrdinal("CityId")),
HeaderLetter = dr.IsDBNull(dr.GetOrdinal("HeaderLetter")) ? "" : dr.GetString(dr.GetOrdinal("HeaderLetter"))
};
}
};
return(model);
}
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="serModel"></param>
/// <returns></returns>
public IList <Eyousoft_yhq.Model.Comment> GetList(Eyousoft_yhq.Model.serComment serModel)
{
IList <Eyousoft_yhq.Model.Comment> list = new List <Eyousoft_yhq.Model.Comment>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ProductID,CommentID,PeopleID,CommentText,IssueTime,(select ContactName from tbl_member where userid=PeopleID) as PeopleName ");
strSql.Append(" from tbl_Comment where 1=1 ");
if (serModel != null)
{
if (serModel.sTime.HasValue)
{
strSql.AppendFormat(" AND datediff(day,'{0}',IssueTime)>=0 ", serModel.sTime.Value);
}
if (serModel.eTime.HasValue)
{
strSql.AppendFormat(" AND datediff(day,'{0}',IssueTime)<=0 ", serModel.eTime.Value);
}
}
strSql.Append(" order by IssueTime DESC ");
DbCommand cmd = this._db.GetSqlStringCommand(strSql.ToString());
using (IDataReader dr = DbHelper.ExecuteReader(cmd, this._db))
{
while (dr.Read())
{
Eyousoft_yhq.Model.Comment model = new Eyousoft_yhq.Model.Comment();
model.ProductID = dr.IsDBNull(dr.GetOrdinal("ProductID")) ? "" : dr.GetString(dr.GetOrdinal("ProductID"));;
model.CommentID = dr.IsDBNull(dr.GetOrdinal("CommentID")) ? "" : dr.GetString(dr.GetOrdinal("CommentID"));
model.PeopleID = dr.IsDBNull(dr.GetOrdinal("PeopleID")) ? "" : dr.GetString(dr.GetOrdinal("PeopleID"));
model.CommentText = dr.IsDBNull(dr.GetOrdinal("CommentText")) ? "" : dr.GetString(dr.GetOrdinal("CommentText"));
model.IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime"));
model.PeopleName = dr.IsDBNull(dr.GetOrdinal("PeopleName")) ? "" : dr.GetString(dr.GetOrdinal("PeopleName"));
list.Add(model);
}
}
return(list);
}
/// <summary>
/// 获取实体
/// </summary>
/// <param name="id">编号</param>
/// <returns></returns>
public Eyousoft_yhq.Model.CustomMsg GetModel(string id)
{
Eyousoft_yhq.Model.CustomMsg model = null;
string StrSql = "select * from tbl_Recommend where id=@id";
DbCommand dc = this._db.GetSqlStringCommand(StrSql);
this._db.AddInParameter(dc, "id", DbType.AnsiStringFixedLength, id);
using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db))
{
if (dr.Read())
{
model = new Eyousoft_yhq.Model.CustomMsg();
model.Id = dr.GetInt32(dr.GetOrdinal("id"));
model.OpenId = dr.GetString(dr.GetOrdinal("OpenId"));
model.NickName = dr.GetString(dr.GetOrdinal("NickName"));
model.Sex = dr.GetString(dr.GetOrdinal("Sex"));
model.IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime"));
model.CommendInfo = dr.GetString(dr.GetOrdinal("CommendInfo"));
}
}
return(model);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Eyousoft_yhq.Model.MSysProvince GetSysProvinceModel(int ID)
{
Eyousoft_yhq.Model.MSysProvince model = null;
string StrSql = "SELECT ID, CountryId,HeaderLetter,Name,AreaId,SortId FROM tbl_SysProvince WHERE ID=@ID";
DbCommand dc = this._db.GetSqlStringCommand(StrSql.ToString());
this._db.AddInParameter(dc, "ID", DbType.Int32, ID);
using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db))
{
if (dr.Read())
{
model = new Eyousoft_yhq.Model.MSysProvince()
{
ID = dr.GetInt32(dr.GetOrdinal("ID")),
CountryId = dr.GetInt32(dr.GetOrdinal("CountryId")),
HeaderLetter = dr.IsDBNull(dr.GetOrdinal("HeaderLetter")) ? "" : dr.GetString(dr.GetOrdinal("HeaderLetter")),
Name = dr.IsDBNull(dr.GetOrdinal("Name")) ? "" : dr.GetString(dr.GetOrdinal("Name")),
AreaId = dr.GetInt32(dr.GetOrdinal("AreaId")),
SortId = dr.GetInt32(dr.GetOrdinal("SortId"))
};
}
};
return(model);
}
/// <summary>
/// 获取公司配置信息
/// </summary>
public Eyousoft_yhq.Model.MCompanySetting GetCompanySetting()
{
var strSql = new StringBuilder();
strSql.Append(" select * from tbl_KV ");
DbCommand dc = _db.GetSqlStringCommand(strSql.ToString());
var model = new Eyousoft_yhq.Model.MCompanySetting();
using (IDataReader dr = DbHelper.ExecuteReader(dc, _db))
{
while (dr.Read())
{
if (!dr.IsDBNull(dr.GetOrdinal("K")) && !string.IsNullOrEmpty(dr.GetString(dr.GetOrdinal("K"))))
{
switch (dr.GetString(dr.GetOrdinal("K")))
{
case "CompanyIntroduce":
model.CompanyIntroduce = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "About":
model.About = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "Contact":
model.Contact = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "Join":
model.Join = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "LegalNotices":
model.LegalNotices = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "Copyright":
model.Copyright = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "Code":
model.Code = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "Description":
model.Description = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "Keywords":
model.Keywords = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "Title":
model.Title = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "Logo":
model.Logo = dr.IsDBNull(dr.GetOrdinal("V"))
? string.Empty
: dr.GetString(dr.GetOrdinal("V"));
break;
case "MsgNumber":
model.MsgNumber = dr.IsDBNull(dr.GetOrdinal("V"))
? 0
: Utils.GetInt(dr.GetString(dr.GetOrdinal("V")));
break;
}
}
}
}
return(model);
}
/// <summary>
/// 获取列表
/// </summary>
/// <param name="serModel"></param>
/// <returns></returns>
public IList <Eyousoft_yhq.Model.Product> GetList(Eyousoft_yhq.Model.SerProduct serModel)
{
IList <Eyousoft_yhq.Model.Product> list = new List <Eyousoft_yhq.Model.Product>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select ProductID,ProductName,ProductType,TourDate,MarketPrice,AppPrice,FavourCode,LinkTel,ProductDis,TourDis,SendTourKnow,ValidiDate,IsEveryDay,IsHot,CreateDate,ServiceQQ, ContractType,ControlPeople,ResidueNum,ProductOpState,ProductSdate,ZCodeViaDate,PType,XianLu ");
strSql.AppendFormat(" from view_Product where 1=1 and ProductState=0 AND datediff(day,'{0}',ValidiDate)>=0 ", DateTime.Now);
if (serModel != null)
{
if (serModel.PType > 0)
{
strSql.AppendFormat(" and PType = {0} ", serModel.PType);
}
if (!string.IsNullOrEmpty(serModel.PurductName))
{
strSql.AppendFormat(" and ProductName like '%{0}%' ", serModel.PurductName);
}
if (!string.IsNullOrEmpty(serModel.FavourCode))
{
strSql.AppendFormat(" and FavourCode = '{0}' ", serModel.FavourCode);
}
}
strSql.Append(" order by IsHot DESC, CreateDate DESC ");
DbCommand cmd = this._db.GetSqlStringCommand(strSql.ToString());
using (IDataReader dr = DbHelper.ExecuteReader(cmd, this._db))
{
while (dr.Read())
{
Eyousoft_yhq.Model.Product model = new Eyousoft_yhq.Model.Product();
model.ProductID = dr.GetString(dr.GetOrdinal("ProductID"));;
model.ProductName = dr.GetString(dr.GetOrdinal("ProductName"));
model.ProductType = dr.GetInt32(dr.GetOrdinal("ProductType"));
model.TourDate = dr.IsDBNull(dr.GetOrdinal("TourDate")) ? null : Utils.GetDateTimeNullable(dr.GetDateTime(dr.GetOrdinal("TourDate")).ToString());
model.MarketPrice = dr.GetDecimal(dr.GetOrdinal("MarketPrice"));
model.AppPrice = dr.GetDecimal(dr.GetOrdinal("AppPrice"));
model.FavourCode = dr.GetString(dr.GetOrdinal("FavourCode"));
model.LinkTel = dr.GetString(dr.GetOrdinal("LinkTel"));
model.ProductDis = dr.GetString(dr.GetOrdinal("ProductDis"));
model.TourDis = dr.GetString(dr.GetOrdinal("TourDis"));
model.SendTourKnow = dr.GetString(dr.GetOrdinal("SendTourKnow"));
model.ValidiDate = dr.GetDateTime(dr.GetOrdinal("ValidiDate"));
//model.ProductState = dr.GetInt32(dr.GetOrdinal("ProductState"));
model.IsEveryDay = dr.IsDBNull(dr.GetOrdinal("IsEveryDay")) ? false : GetBoolean(dr.GetString(dr.GetOrdinal("IsEveryDay")));
model.IsHot = dr.IsDBNull(dr.GetOrdinal("IsHot")) ? 0 : dr.GetByte(dr.GetOrdinal("IsHot"));
model.CreateDate = dr.GetDateTime(dr.GetOrdinal("CreateDate"));
model.ServiceQQ = dr.IsDBNull(dr.GetOrdinal("ServiceQQ")) ? "" : dr.GetString(dr.GetOrdinal("ServiceQQ"));
model.ContractType = (Eyousoft_yhq.Model.ContractType)dr.GetByte(dr.GetOrdinal("ContractType"));
model.ControlPeople = dr.GetInt32(dr.GetOrdinal("ControlPeople"));
model.ResidueNum = dr.GetInt32(dr.GetOrdinal("ResidueNum"));
model.ProductSdate = dr.IsDBNull(dr.GetOrdinal("ProductSdate")) ? DateTime.Now : dr.GetDateTime(dr.GetOrdinal("ProductSdate"));
model.ZCodeViaDate = dr.IsDBNull(dr.GetOrdinal("ZCodeViaDate")) ? DateTime.Now : dr.GetDateTime(dr.GetOrdinal("ZCodeViaDate"));
model.xianlu = (Eyousoft_yhq.Model.XianLu)dr.GetByte(dr.GetOrdinal("XianLu"));
list.Add(model);
}
}
return(list);
}
/// <summary>
/// 获得前几行数据集合
/// </summary>
/// <param name="Top">0:所有</param>
/// <param name="chaXun"></param>
/// <param name="filedOrder"></param>
/// <returns></returns>
public IList <Eyousoft_yhq.Model.MSysCity> GetSysCityList(int Top, Eyousoft_yhq.Model.MSysCity chaXun, string filedOrder)
{
IList <Eyousoft_yhq.Model.MSysCity> ResultList = null;
string StrSql = string.Format("SELECT {0} Id, ProvinceId,Name,CenterCityId,HeaderLetter,IsSite,DomainName,IsEnabled FROM tbl_SysCity WHERE 1=1 ", (Top > 0 ? " TOP " + Top + " " : ""));
if (chaXun != null)
{
if (chaXun.ProvinceId > -1)
{
StrSql = StrSql + string.Format(" AND ProvinceId={0} ", chaXun.ProvinceId);
}
if (!string.IsNullOrEmpty(chaXun.Name))
{
StrSql = StrSql + string.Format(" AND Name like '%{0}%'", chaXun.Name);
}
if (chaXun.CenterCityId > 0)
{
StrSql = StrSql + string.Format(" AND CenterCityId={0} ", chaXun.CenterCityId);
}
if (!string.IsNullOrEmpty(chaXun.HeaderLetter))
{
StrSql = StrSql + string.Format(" AND HeaderLetter like '%{0}%'", chaXun.HeaderLetter);
}
if (chaXun.IsSite)
{
StrSql = StrSql + string.Format(" AND IsSite='{0}'", this.GetBooleanToStr(chaXun.IsSite));
}
if (!string.IsNullOrEmpty(chaXun.DomainName))
{
StrSql = StrSql + string.Format(" AND DomainName like '%{0}%'", chaXun.DomainName);
}
if (chaXun.IsEnabled)
{
StrSql = StrSql + string.Format(" AND IsEnabled='{0}'", this.GetBooleanToStr(chaXun.IsEnabled));
}
}
StrSql = StrSql + (string.IsNullOrEmpty(filedOrder) ? "" : " ORDER BY " + filedOrder + " ASC ");
DbCommand dc = this._db.GetSqlStringCommand(StrSql.ToString());
using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db))
{
ResultList = new List <Eyousoft_yhq.Model.MSysCity>();
while (dr.Read())
{
Eyousoft_yhq.Model.MSysCity model = new Eyousoft_yhq.Model.MSysCity()
{
Id = dr.GetInt32(dr.GetOrdinal("Id")),
ProvinceId = dr.GetInt32(dr.GetOrdinal("ProvinceId")),
Name = dr.IsDBNull(dr.GetOrdinal("Name")) ? "" : dr.GetString(dr.GetOrdinal("Name")),
CenterCityId = dr.GetInt32(dr.GetOrdinal("CenterCityId")),
HeaderLetter = dr.IsDBNull(dr.GetOrdinal("HeaderLetter")) ? "" : dr.GetString(dr.GetOrdinal("HeaderLetter")),
IsSite = this.GetBoolean(dr.IsDBNull(dr.GetOrdinal("IsSite")) ? "" : dr.GetString(dr.GetOrdinal("IsSite"))),
DomainName = dr.IsDBNull(dr.GetOrdinal("DomainName")) ? "" : dr.GetString(dr.GetOrdinal("DomainName")),
IsEnabled = this.GetBoolean(dr.IsDBNull(dr.GetOrdinal("IsEnabled")) ? "" : dr.GetString(dr.GetOrdinal("IsEnabled")))
};
ResultList.Add(model);
model = null;
}
}
return(ResultList);
}
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="RecordCount"></param>
/// <param name="serModel"></param>
/// <returns></returns>
public IList <Eyousoft_yhq.Model.Order> GetList(Eyousoft_yhq.Model.MSearchOrder serModel)
{
IList <Eyousoft_yhq.Model.Order> list = new List <Eyousoft_yhq.Model.Order>();
StringBuilder query = new StringBuilder();
query.Append("select * from view_Order where 1=1");
if (serModel != null)
{
if (!string.IsNullOrEmpty(serModel.MemberID))
{
query.AppendFormat(" and MemberID = '{0}' ", serModel.MemberID);
}
if (!string.IsNullOrEmpty(serModel.OrderCode))
{
query.AppendFormat(" and OrderCode like '%{0}%' ", serModel.OrderCode);
}
if (!string.IsNullOrEmpty(serModel.ConfirmCode))
{
query.AppendFormat(" and ConfirmCode like '%{0}%' ", serModel.ConfirmCode);
}
if (serModel.OrderState.HasValue)
{
query.AppendFormat(" AND OrderState = '{0}' ", (int)serModel.OrderState.Value);
}
if (serModel.PaymentState.HasValue)
{
query.AppendFormat(" AND PayState = '{0}' ", (int)serModel.PaymentState.Value);
}
if (serModel.STime.HasValue)
{
query.AppendFormat(" AND IssueTime>='{0}' ", serModel.STime.Value);
}
if (serModel.ETime.HasValue)
{
query.AppendFormat(" AND IssueTime< '{0}' ", serModel.ETime.Value.AddDays(1));
}
if (!string.IsNullOrEmpty(serModel.RouteName))
{
query.AppendFormat(" AND ProductName like '%{0}%' ", serModel.RouteName);
}
if (serModel.OrderPrice != 0)
{
query.AppendFormat(" AND OrderPrice = '{0}' ", serModel.OrderPrice);
}
if (!string.IsNullOrEmpty(serModel.PromotionCode))
{
query.AppendFormat(" and (PollCode = '{0}' OR PromotionCode= '{0}' )", serModel.PromotionCode);
}
}
query.Append(" order by IssueTime DESC ");
DbCommand cmd = this._db.GetSqlStringCommand(query.ToString());
using (IDataReader dr = DbHelper.ExecuteReader(cmd, this._db))
{
while (dr.Read())
{
Eyousoft_yhq.Model.Order model = new Eyousoft_yhq.Model.Order();
model.OrderID = dr.GetString(dr.GetOrdinal("OrderId"));
model.ProductID = dr.IsDBNull(dr.GetOrdinal("ProductID")) ? "" : dr.GetString(dr.GetOrdinal("ProductID"));
model.ProductName = dr.IsDBNull(dr.GetOrdinal("ProductName")) ? "" : dr.GetString(dr.GetOrdinal("ProductName"));
model.OrderCode = dr.GetString(dr.GetOrdinal("OrderCode"));
model.MemberID = dr.GetString(dr.GetOrdinal("MemberID"));
model.MemberName = dr.GetString(dr.GetOrdinal("MemberName"));
model.MemberTel = dr.IsDBNull(dr.GetOrdinal("MemberTel")) ? "" : dr.GetString(dr.GetOrdinal("MemberTel"));
model.MemberSex = (Eyousoft_yhq.Model.sexType)dr.GetByte(dr.GetOrdinal("MemberSex"));
model.OrderState = (Eyousoft_yhq.Model.OrderState)dr.GetByte(dr.GetOrdinal("OrderState"));
model.PayState = (Eyousoft_yhq.Model.PaymentState)dr.GetByte(dr.GetOrdinal("PayState"));
model.IsCheck = this.GetBoolean(dr.GetString(dr.GetOrdinal("ProductName")));
model.ConfirmCode = dr.IsDBNull(dr.GetOrdinal("ConfirmCode")) ? "" : dr.GetString(dr.GetOrdinal("ConfirmCode"));
model.Remark = dr.IsDBNull(dr.GetOrdinal("Remark")) ? "" : dr.GetString(dr.GetOrdinal("Remark"));
model.IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime"));
model.OrderPrice = dr.GetDecimal(dr.GetOrdinal("OrderPrice"));
model.TourDate = dr.IsDBNull(dr.GetOrdinal("TourDate")) ? null : Utils.GetDateTimeNullable(dr.GetDateTime(dr.GetOrdinal("TourDate")).ToString());
model.FavourCode = dr.GetString(dr.GetOrdinal("FavourCode"));
model.isEvery = this.GetBoolean(dr.GetString(dr.GetOrdinal("isEvery")));
model.ProductType = dr.GetInt32(dr.GetOrdinal("ProductType"));
model.ContractType = dr.IsDBNull(dr.GetOrdinal("ContractType")) ? Eyousoft_yhq.Model.ContractType.国内合同 : (Eyousoft_yhq.Model.ContractType)dr.GetByte(dr.GetOrdinal("ContractType")); model.OrderPrice = dr.GetDecimal(dr.GetOrdinal("OrderPrice"));
model.PeopleNum = dr.GetInt32(dr.GetOrdinal("PeopleNum"));
model.ContractText = dr.IsDBNull(dr.GetOrdinal("ContractText")) ? "" : dr.GetString(dr.GetOrdinal("ContractText"));
model.IsealCheck = GetBoolean(dr.GetString(dr.GetOrdinal("IsealCheck")));
model.FYJE = dr.IsDBNull(dr.GetOrdinal("fyje")) ? 0 : dr.GetDecimal(dr.GetOrdinal("fyje"));
model.SendFile = this.GetAttachList(dr["ComAttachXML"].ToString(), model.OrderID);
model.AddressID = dr.IsDBNull(dr.GetOrdinal("AddressID")) ? "" : dr.GetString(dr.GetOrdinal("AddressID"));
model.RebackMoney = dr.IsDBNull(dr.GetOrdinal("RebackMoney")) ? 0 : dr.GetDecimal(dr.GetOrdinal("RebackMoney"));
model.backMoney = dr.IsDBNull(dr.GetOrdinal("backMoney")) ? 0 : dr.GetDecimal(dr.GetOrdinal("backMoney"));
model.ProductOpState = (Eyousoft_yhq.Model.ProductOp)dr.GetByte(dr.GetOrdinal("ProductOpState"));
model.ZCodeViaDate = dr.IsDBNull(dr.GetOrdinal("ZCodeViaDate")) ? DateTime.Now : dr.GetDateTime(dr.GetOrdinal("ZCodeViaDate"));
model.XiaoFei = (Eyousoft_yhq.Model.XFstate)dr.GetByte(dr.GetOrdinal("ConSumState"));
model.JIESUAN = (Eyousoft_yhq.Model.JSfangshi)dr.GetByte(dr.GetOrdinal("JState"));
list.Add(model);
}
};
return(list);
}
