public Member(int MasterID, int CurrentUserID)
{
mCurrentUserID = CurrentUserID;
HomeAddress = new Address(CurrentUserID);
WorkAddress = new Address(CurrentUserID);
ID = MasterID;
string procName = "GET_MEMBERDETAIL_BY_MASTER_ID";
using (dbhMember = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhMember.AddParameter("@MemberMasterID", ID);
dbhMember.AddParameter("@CURRENTUSERID", @mCurrentUserID);
IDataReader reader = dbhMember.ExecuteReader(procName);
while (reader.Read())
{
LoadBasicInfo(reader);
if (reader.NextResult())
{
WorkAddress = new Address(reader);
if (reader.NextResult())
{
HomeAddress = new Address(reader);
}
}
}
}
}
public List<Tuple<int, string>> GetFamilyQuestions(int CurrentUserID)
{
List<Tuple<int, string>> lst = new List<Tuple<int, string>>();
using (dbhQuestion = new DBHelper(ConnectionStrings.DefaultDBConnection, CurrentUserID))
{
dbhQuestion.AddParameter("@CurrentUserID", CurrentUserID);
IDataReader reader = dbhQuestion.ExecuteReader("GET_FAMILY_QUESTIONS");
while (reader.Read())
{
lst.Add(Tuple.Create<int, string>(Convert.ToInt32(reader["ID"]), Convert.ToString(reader["QUESTION_CONTENT"])));
}
}
return lst;
}
public MemberInfo(string Subscriber_ID, int CurrentUserID)
{
SubscriberID = Subscriber_ID;
mCurrentUserID = CurrentUserID;
using (dbhMemberInfo = new DBHelper(ConnectionStrings.DefaultDBConnection, mCurrentUserID))
{
dbhMemberInfo.AddParameter("@SubscriberID", SubscriberID);
dbhMemberInfo.AddParameter("@CurrentUserID", mCurrentUserID);
IDataReader reader = dbhMemberInfo.ExecuteReader("GET_MEMBER_BY_SUBSCRIBER_ID");
if (reader.Read())
{
LoadMemberInfo(reader);
}
}
}
public Question GetQuestionById(Int64 id, int CurrentUserId)
{
Question obj = new Question(1);
string procName = "GET_QUESTION_BY_ID";
using (dbhQuestion = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhQuestion.AddParameter("@id", id);
dbhQuestion.AddParameter("@CURRENTUSERID", CurrentUserId);
IDataReader dr = dbhQuestion.ExecuteReader(procName);
while (dr.Read())
{
obj = (new Question(dr));
}
}
return obj;
}
public AssessmentGroup(int ID, int CurrentUserID)
{
mCurrentUserID = CurrentUserID;
const string procName = "GET_ASSESSMENT_GROUP_BY_ID";
using (dbhAssessmentGroup = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhAssessmentGroup.AddParameter("@ID", ID);
dbhAssessmentGroup.AddParameter("@CURRENTUSERID", mCurrentUserID);
IDataReader dr = dbhAssessmentGroup.ExecuteReader(procName);
while (dr.Read())
{
LoadReader(dr);
}
dbhAssessmentGroup.Dispose();
}
}
public QuestionGroup(int ID,int CurrentUserID)
{
mCurrentUserID = CurrentUserID;
const string procName = "GET_QUESTIONGROUPS_BY_ID";
using (dbhQuestionGroup = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhQuestionGroup.AddParameter("@id", ID);
dbhQuestionGroup.AddParameter("@CurrentUserID", mCurrentUserID);
IDataReader dr = dbhQuestionGroup.ExecuteReader(procName);
while (dr.Read())
{
LoadReader(dr);
}
dbhQuestionGroup.Dispose();
}
}
public List<Tuple<int, int>> GetFamilyHRAResponse(int MemberMasterID, int AssessmentId, int CurrentUserID)
{
List<Tuple<int, int>> lst = new List<Tuple<int, int>>();
mCurrentUserID = CurrentUserID;
using (dbhAssessmentResponse = new DBHelper(ConnectionStrings.DefaultDBConnection, mCurrentUserID))
{
dbhAssessmentResponse.AddParameter("@MemberMasterID", MemberMasterID);
dbhAssessmentResponse.AddParameter("@assessment_id", AssessmentId);
dbhAssessmentResponse.AddParameter("@CurrentUserID", mCurrentUserID);
IDataReader reader = dbhAssessmentResponse.ExecuteReader("GET_FAMILY_HRA_RESPONSES");
while (reader.Read())
{
lst.Add(Tuple.Create<int, int>(Convert.ToInt32(reader["MEMBER_MASTER_ID_REF"]), Convert.ToInt32(reader["FAM_QUESTION_ID_REF"])));
}
}
return lst;
}
public List<MenuItem> GetMenuForRole(Int64 roleID, int CurrentUserID)
{
DBHelper dbhMenuManager;
List<MenuItem> menuItems = new List<MenuItem>();
using (dbhMenuManager = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhMenuManager.AddParameter("@ROLEID", roleID.ToString());
dbhMenuManager.AddParameter("@CurrentUserID", CurrentUserID);
IDataReader dr = dbhMenuManager.ExecuteReader("GET_USER_SITEMAP");
while (dr.Read())
{
menuItems.Add(new MenuItem(dr));
}
dbhMenuManager.Dispose();
}
return menuItems;
}
public static Boolean IsThereChildMenu(Int32 SiteId, Int64 CurrentUserID)
{
DBHelper dbhMenuManager;
Boolean IsValid = false;
List<ModuleInfo> menuItems = new List<ModuleInfo>();
using (dbhMenuManager = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhMenuManager.AddParameter("@iSiteId", SiteId);
IDataReader dr = dbhMenuManager.ExecuteReader("IsthereChildMenu");
while (dr.Read())
{
IsValid = Convert.ToBoolean(Convert.ToInt32(dr["IsValid"].ToString()));
}
dbhMenuManager.Dispose();
}
return IsValid;
}
public List<QuestionGroup> GetQuestionGroups(string questionGroupName,char qGroupStatus, int CurrentUserID)
{
List<QuestionGroup> items = new List<QuestionGroup>();
const string procName = "GET_QUESTIONGROUPS";
using (DBHelper dbObj = new DBHelper(ConnectionStrings.DefaultDBConnection,1))
{
dbObj.AddParameter("@name",questionGroupName);
dbObj.AddParameter("@status", qGroupStatus);
dbObj.AddParameter("@CurrentUserID", CurrentUserID);
IDataReader dr = dbObj.ExecuteReader(procName);
while (dr.Read())
{
items.Add(new QuestionGroup(dr));
}
}
return items;
}
public List<Role> GetRoles(Int32 roleId, Int32 CurrentUserId)
{
List<Role> items = new List<Role>();
const string procName = "GET_ROLES";
using (dbhRole = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhRole.AddParameter("@RoleId", roleId);
dbhRole.AddParameter("@CurrentUserID", CurrentUserId);
IDataReader dr = dbhRole.ExecuteReader(procName);
while (dr.Read())
{
items.Add(new Role(dr));
}
dbhRole.Dispose();
}
return items;
}
public Assessment(int assessmentID, int CurrentUserID)
{
mCurrentUserID = CurrentUserID;
ID = assessmentID;
string procName = "GET_ASSESSMENT_BY_ID";
using(dbhAssessment = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhAssessment.AddParameter("@ID", ID);
dbhAssessment.AddParameter("@CURRENTUSERID", @mCurrentUserID);
IDataReader reader = dbhAssessment.ExecuteReader(procName);
if(reader.Read())
{
LoadReader(reader);
}
}
}
public Dictionary<int, string> GetSysCodeValues(int CodeType)
{
Dictionary<int, string> items = new Dictionary<int, string>();
using (dbhCodeManager = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhCodeManager.AddParameter("@CODE_TYPE_ID_REF", CodeType);
dbhCodeManager.AddParameter("@CurrentUserID", mCurrentUserID);
IDataReader reader = dbhCodeManager.ExecuteReader("GET_SYS_CODE_VALUES");
while (reader.Read())
{
items.Add(Int32.Parse(reader[0].ToString()), reader[1].ToString());
}
dbhCodeManager.Dispose();
}
return items;
}
public List<AssessmentGroup> GetAssessmentGroups(string assessmentGroupName, bool assessmentGroupStatus)
{
List<AssessmentGroup> items = new List<AssessmentGroup>();
const string procName = "GET_ASSESSMENT_GROUPS";
using (mAGL_DBHelper = new DBHelper(ConnectionStrings.DefaultDBConnection, mCurrentUserID))
{
mAGL_DBHelper.AddParameter("@name", assessmentGroupName);
mAGL_DBHelper.AddParameter("@status", (assessmentGroupStatus)?'A':'I');
mAGL_DBHelper.AddParameter("@CurrentUserID", 1);
IDataReader dr = mAGL_DBHelper.ExecuteReader(procName);
while(dr.Read())
{
items.Add(new AssessmentGroup(dr));
}
mAGL_DBHelper.Dispose();
}
return items;
}
public List<Question> GetAssessmentQuestions(int AssessmentId, int CurrentUserId)
{
List<Question> lst = new List<Question>();
const string procName = "GET_ASSESSMENT_QUESTIONS";
using (dbhAssessments = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhAssessments.AddParameter("@ASSESSMENT_ID", AssessmentId);
dbhAssessments.AddParameter("@CURRENTUSERID", CurrentUserId);
IDataReader dr = dbhAssessments.ExecuteReader(procName);
while (dr.Read())
{
lst.Add(new Question(dr));
}
dbhAssessments.Dispose();
}
return lst;
}
public List<Question> DeleteQuestions(int AssessmentID, int GROUPID, int QUESTIONID, int CurrentUserId)
{
List<Question> lst = new List<Question>();
using (dbhAssessments = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhAssessments.AddParameter("@ASSESSMENT_ID", AssessmentID);
dbhAssessments.AddParameter("@QUESTION_GROUP_ID", GROUPID);
dbhAssessments.AddParameter("@QUESTIONS_ID", QUESTIONID);
dbhAssessments.AddParameter("@CURRENTUSERID", CurrentUserId);
IDataReader reader = dbhAssessments.ExecuteReader("DELETE_ASSESSMENT_QUESTION");
while (reader.Read())
{
lst.Add(new Question(reader));
}
}
return lst;
}
public List<MemberInfo> GetMembers(string firstname, string lastname, string sex, string memberID)
{
List<MemberInfo> members = new List<MemberInfo>();
using (dbhMil = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhMil.AddParameter("@FIRSTNAME", firstname);
dbhMil.AddParameter("@LASTNAME", lastname);
dbhMil.AddParameter("@SEX", sex);
dbhMil.AddParameter("@MEMBERID", memberID);
dbhMil.AddParameter("@CURRENTUSERID", mCurrentUserID);
IDataReader reader = dbhMil.ExecuteReader("GET_MEMBERINFOLIST");
while (reader.Read())
{
members.Add(new MemberInfo(reader));
}
}
return members;
}
public static List <UserDTO> GetAllUsers()
{
using (DBHelper helper = new DBHelper())
{
var query = "Select * from dbo.Users Where IsActive = 1;";
var reader = helper.ExecuteReader(query);
List <UserDTO> list = new List <UserDTO>();
while (reader.Read())
{
var dto = FillDTO(reader);
if (dto != null)
{
list.Add(dto);
}
}
return(list);
}
}
/// <summary>
/// 获取全部网站信息
/// </summary>
/// <returns></returns>
public static List <WebInfo> GetAllWebInfo()
{
List <WebInfo> list = new List <WebInfo>();
SqlDataReader dr = DBHelper.ExecuteReader("Select_WebInfo", CommandType.StoredProcedure);
while (dr.Read())
{
WebInfo webinfo = new WebInfo()
{
Web_Date = dr["Web_Date"].ToString(),
Web_Index = dr["Web_Index"].ToString(),
Web_Name = dr["Web_Name"].ToString(),
Web_No = Convert.ToInt32(dr["Web_No"])
};
list.Add(webinfo);
}
dr.Close();
DBHelper.CloseCon();
return(list);
}
/// <summary>
/// 获取该会员首次注册期数
/// </summary>
/// <param name="number"></param>
/// <param name="orderId"></param>
/// <returns></returns>
public int GetRegisExce(string number)
{
//注册期数
int registExcept = 0;
string SQL = "select ExpectNum from memberInfo where Number=@number";
SqlParameter[] para =
{
new SqlParameter("@number", number)
};
SqlDataReader reader = DBHelper.ExecuteReader(SQL, para, CommandType.Text);
if (reader.Read())
{
registExcept = Convert.ToInt32(reader[0]);
}
reader.Close();
return(registExcept);
}
/// <summary>
/// 根据编号查询地址
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public List <string> ChoseArea(string code)
{
List <string> list = new List <string>();
string sql = "select country,province,city from city where cpccode=@code";
SqlParameter[] para =
{
new SqlParameter("@code", code)
};
SqlDataReader reader = DBHelper.ExecuteReader(sql, para, CommandType.Text);
if (reader.Read())
{
list.Add(reader["country"].ToString());
list.Add(reader["province"].ToString());
list.Add(reader["city"].ToString());
}
reader.Close();
return(list);
}
/// <summary>
/// 获得该保单的总pv
/// </summary>
/// <param name="orderID"></param>
/// <returns></returns>
public double GetTotalPV(string orderID, out string PayExpectNum)
{
PayExpectNum = null;
string SQL = "select totalPv , PayExpectNum from memberOrder where orderID=@orderID";
SqlParameter[] para =
{
new SqlParameter("@orderID", orderID)
};
SqlDataReader reader = DBHelper.ExecuteReader(SQL, para, CommandType.Text);
if (reader.Read())
{
PayExpectNum = reader[1].ToString();
return(Convert.ToDouble(reader[0]));
}
PayExpectNum = null;
reader.Close();
return(0);
}
/// <summary>
/// 根据条件查询问题库信息
/// </summary>
/// <returns></returns>
public static Issue_library GetIssue_libraryByConn(string demandType, string demandContent)
{
string sql = "Select * from Issue_library where " + demandType + " = @" + demandType;
Issue_library obj = new Issue_library();
SqlDataReader dr = DBHelper.ExecuteReader(sql, CommandType.Text, new SqlParameter[] {
new SqlParameter("@" + demandType, demandContent)
});
if (dr.Read())
{
obj = new Issue_library()
{
IssueI_Name = dr["IssueI_Name"].ToString(),
IssueI_No = Convert.ToInt32(dr["IssueI_No"])
};
}
dr.Close();
DBHelper.CloseCon();
return(obj);
}
/// <summary>
/// 根据条件查询文章类型信息
/// </summary>
/// <returns></returns>
public static ArticleType GetArticleTypeByConn(string demandType, string demandContent)
{
string sql = "Select * from ArticleType where " + demandType + " = @" + demandType;
ArticleType articleType = new ArticleType();
SqlDataReader dr = DBHelper.ExecuteReader(sql, CommandType.Text, new SqlParameter[] {
new SqlParameter("@" + demandType, demandContent)
});
if (dr.Read())
{
articleType = new ArticleType()
{
At_Name = dr["At_Name"].ToString(),
At_No = Convert.ToInt32(dr["At_No"])
};
}
dr.Close();
DBHelper.CloseCon();
return(articleType);
}
/// <summary>
/// 根据产品编号，得到此产品的剩余可用数量
/// </summary>
/// <param name="productID">产品编号</param>
/// <param name="productID">店铺编号</param>
/// <returns></returns>
public static int GetCertainProductLeftStoreCount(string productID, string storeId)
{
String sql = "Select (TotalIn-TotalOut) From Stock Where StoreID = @num And ProductID =@num1";
int count = 0;
SqlParameter[] spa1 = new SqlParameter[] {
new SqlParameter("@num", SqlDbType.NVarChar, 50),
new SqlParameter("@num1", SqlDbType.Int)
};
spa1[0].Value = storeId;
spa1[1].Value = productID;
SqlDataReader reader = DBHelper.ExecuteReader(sql, spa1, CommandType.Text);
while (reader.Read())
{
count = Convert.ToInt32(reader[0]);
}
reader.Close();
return(count);
}
//查询所有订单信息
public static List <OrderInfos> SelectOrdersAll()
{
List <OrderInfos> lo = new List <OrderInfos>();
string sql = "select top 200 * from OrderInfo";
SqlDataReader dr = DBHelper.ExecuteReader(sql);
while (dr.Read())
{
OrderInfos o = new OrderInfos();
o.TableId = (int)dr["TableId"];
o.OrderId = (string)dr["OrderId"];
o.FPrice = (decimal)dr["FPrice"];
o.FName = (string)dr["FName"];
o.FNumber = (int)dr["FNumber"];
lo.Add(o);
}
dr.Close();
DBHelper.Closecon();
return(lo);
}
/// <summary>
/// 搜索条件
/// </summary>
/// <param name="s">开始位置</param>
/// <param name="e">结束位置</param>
/// <param name="key">关键词</param>
/// <param name="state">状态</param>
/// <param name="orderby">排序方式,直接输入order by后面的语句</param>
/// <returns>IList<basic_huodong></returns>
public IList <basic_huodong> Search(int s, int e, string key, string state, string orderby)
{
string sql1 = "select top " + (e - s + 1).ToString() + " * from basic_huodong where ";
string sql2 = string.IsNullOrEmpty(key) ? " 1=1 " : " ( hdremark like '%" + key + "%' or hdname like '%" + key + "%' ) ";
string sql3 = string.IsNullOrEmpty(state) ? "" : " and hdtype= '" + state + "' ";
string sql4 = string.IsNullOrEmpty(orderby) ? " order by hdid desc " : " order by " + orderby;
string sql5 = s == 1 ? "" : " and hdid not in ( select top " + (s - 1).ToString() + " hdid from basic_huodong where " + sql2 + sql3 + sql4 + " ) ";
DBHelper.sqlstr = sql1 + sql2 + sql3 + sql5 + sql4;
List <basic_huodong> list = new List <basic_huodong>();
SqlDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
basic_huodong Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
/// <summary>
/// 通过paperTypeCode查询证件信息
/// </summary>
/// <param name="paperTypeCode"></param>
/// <returns></returns>
public List <string> GetCardType(string paperTypeCode)
{
List <string> list = new List <string>();
string sql = "select * from bsco_PaperType where paperTypeCode=@paperTypeCode";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@paperTypeCode", paperTypeCode)
};
SqlDataReader reader = DBHelper.ExecuteReader(sql, para, CommandType.Text);
if (reader.Read())
{
list.Add(reader["PaperType"].ToString());
list.Add(reader["PaperTypeCode"].ToString());
list.Add(reader["id"].ToString());
}
reader.Close();
return(list);
}
public static List <Order> SelectOrderInfo(string TableId)
{
List <Order> od = new List <Order>();
string sql = "select * from TableNowInfo t,TableInfo tt where tt.TableId=t.TableId and t.TableId='" + TableId + "'";
SqlDataReader dr = DBHelper.ExecuteReader(sql);
if (dr.Read())
{
Order ods = new Order();
ods.OrderId = (string)dr["OrderId"];
ods.IsPay = (bool)dr["IsPay"];
ods.Price = (decimal)dr["Price"];
ods.PayTime = (DateTime)dr["PayTime"];
ods.Remarks = (string)dr["Remarks"];
od.Add(ods);
}
dr.Close();
DBHelper.Closecon();
return(od);
}
/// <summary>
/// 获取全部回复信息
/// </summary>
/// <returns></returns>
public static List <Reply> GetAllReply()
{
List <Reply> list = new List <Reply>();
SqlDataReader dr = DBHelper.ExecuteReader("Select_Reply", CommandType.StoredProcedure);
while (dr.Read())
{
Reply reply = new Reply()
{
Com_No = Convert.ToInt32(dr["Com_No"]),
R_Content = dr["R_Content"].ToString(),
R_Datetime = dr["R_Datetime"].ToString(),
R_No = Convert.ToInt32(dr["R_No"])
};
list.Add(reply);
}
dr.Close();
DBHelper.CloseCon();
return(list);
}
//根据换货单号得到换货单详细
/// <summary>
/// 根据换货单号得到换货单详细
/// </summary>
/// <returns></returns>
public ReplacementModel GetReplacementlModelByDisplaceOrderID(string displaceOrderID)
{
ReplacementModel replacementModel = null;
string sql = "Select id,displaceorderid,storeid,storeorderid,refundmentorderid,OutStorageOrderID,makedocdate,makedocperson,auditperson,expectnum,outtotalmoney,outtotalpv,intotalmoney,intotalpv,inceptaddress,postalcode,telephone,stateflag,closeflag, remark,operateip,operatenum,inceptperson From Replacement Where DisplaceOrderID = @DisplaceOrderID ";
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@DisplaceOrderID", displaceOrderID) };
SqlDataReader dr = DBHelper.ExecuteReader(sql, para, CommandType.Text);
if (dr.Read())
{
replacementModel = new ReplacementModel();
replacementModel.DisplaceOrderID = dr.GetString(dr.GetOrdinal("displaceorderid"));
replacementModel.StoreID = dr.GetString(dr.GetOrdinal("storeid"));
replacementModel.StoreOrderID = dr.GetString(dr.GetOrdinal("storeorderid"));
replacementModel.RefundmentOrderID = dr.GetString(dr.GetOrdinal("refundmentorderid"));
replacementModel.OutStrageOrderID = dr.GetString(dr.GetOrdinal("OutStorageOrderID"));
replacementModel.MakeDocDate = dr.GetDateTime(dr.GetOrdinal("makedocdate"));
replacementModel.MakeDocPerson = dr.GetString(dr.GetOrdinal("makedocperson"));
// replacementModel.AuditingDate = dr.GetDateTime(dr.GetOrdinal("auditingdate"));
replacementModel.AuditPerson = dr.GetString(dr.GetOrdinal("auditperson"));
replacementModel.ExpectNum = dr.GetInt32(dr.GetOrdinal("expectnum"));
replacementModel.OutTotalMoney = double.Parse(dr["outtotalmoney"].ToString());
replacementModel.OutTotalPV = double.Parse(dr["outtotalpv"].ToString());
replacementModel.InTotalMoney = double.Parse(dr["intotalmoney"].ToString());
replacementModel.InTotalPV = double.Parse(dr["intotalpv"].ToString());
replacementModel.InceptAddress = dr.GetString(dr.GetOrdinal("inceptaddress"));
replacementModel.PostalCode = dr.GetString(dr.GetOrdinal("postalcode"));
replacementModel.Telephone = dr.GetString(dr.GetOrdinal("telephone"));
//replacementModel.StateFlag=int.Parse(dr["stateflag"].ToString());
//replacementModel.StateFlag = dr.GetInt32(dr.GetOrdinal("stateflag"));
//replacementModel.CloseFlag=int.Parse(dr["closeflag"].ToString());
//replacementModel.CloseFlag = dr.GetInt32(dr.GetOrdinal("closeflag"));
replacementModel.Remark = dr.GetString(dr.GetOrdinal("remark"));
replacementModel.OperateIP = dr.GetString(dr.GetOrdinal("operateip"));
replacementModel.OperateNum = dr.GetString(dr.GetOrdinal("operatenum"));
replacementModel.InceptPerson = dr.GetString(dr.GetOrdinal("inceptperson"));
}
dr.Close();
return(replacementModel);
}
/// <summary>
/// 模糊搜索
/// </summary>
/// <param name="key">关键词</param>
/// <param name="state_pay">店铺类型</param>
/// <param name="state_fahuo">发货状态</param>
/// <returns></returns>
public IList <shuadan_records> Search(string key, string state_pay, string state_fahuo)
{
string sql1 = "select * from shuadan_records where ";
string sql2 = string.IsNullOrEmpty(key) ? " 1=1 " : " ( sdgoodsname like '%" + key + "%' or sdgoodsurl like '%" + key + "%' or sdorderid like '%" + key + "%' or sdphone like '%" + key + "%' or sdvpn like '%" + key + "%' or sdaddress like '%" + key + "%' or sdwuliu like '%" + key + "%' or sdremark1 like '%" + key + "%' or sdremark3 like '%" + key + "%' or sdremark2 like '%" + key + "%' or sdremark4 like '%" + key + "%' or sdremark5 like '%" + key + "%' or sdremark6 like '%" + key + "%' )"; //删除无用字段，删除最后一个or
string sql3 = string.IsNullOrEmpty(state_pay) ? "" : " and sdstatepay= '" + state_pay + "' and sddptype='3' "; //状态字段，无用删除
string sql4 = string.IsNullOrEmpty(state_fahuo) ? "" : " and sdremark4= '" + state_fahuo + "' ";
string sql7 = " order by sddate asc ";
DBHelper.sqlstr = sql1 + sql2 + sql3 + sql4 + sql7;
List <shuadan_records> list = new List <shuadan_records>();
SqlDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
shuadan_records Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
/// <summary>
/// 根据条件查询审核信息
/// </summary>
/// <returns></returns>
public static Aduit GetAduitByConn(string demandType, string demandContent)
{
string parameter = "@" + demandType;
Aduit aduit = null;
string sql = "Select * from Aduit where " + demandType + "=" + parameter;
SqlDataReader dr = DBHelper.ExecuteReader(sql, CommandType.Text, new SqlParameter[] {
new SqlParameter(parameter, demandContent)
});
if (dr.Read())
{
aduit = new Aduit()
{
Aduit_Name = dr["Aduit_Name"].ToString(),
Aduit_No = Convert.ToInt32(dr["Aduit_No"])
};
}
dr.Close();
DBHelper.CloseCon();
return(aduit);
}
/////////////////////////////////////////////////////////////////////
public static FolderDTO getFolderById(int pid)
{
if (pid == 0)
{
return new FolderDTO {
name = "Root", CreatedBy = 0, CreatedOn = DateTime.Now, id = 0, IsActive = 1, ParentId = 0
}
}
;
String query = String.Format(@"SELECT * FROM dbo.Folder WHERE Id='{0}'", pid);
using (DBHelper helper = new DBHelper())
{
var reader = helper.ExecuteReader(query);
reader.Read();
var folder = FillDTO(reader);
return(folder);
}
}
/// <summary>
/// 获取仓库名(Outstock.aspx)
/// </summary>
/// <param name="storeOrderID"></param>
/// <returns></returns>
public static ListItem GetWareHouseName(string storeOrderID)
{
string sqlstr = "select wh.WareHouseName from StoreOrder so inner join WareHouse wh on so.WareHouseID=wh.WareHouseID where so.StoreOrderID=@storeOrderID";
SqlParameter[] param = new SqlParameter[] { new SqlParameter("@storeOrderID", storeOrderID) };
SqlDataReader dr = DBHelper.ExecuteReader(sqlstr, param, CommandType.Text);
if (dr.Read())
{
string _wareHouseName = dr["WareHouseName"].ToString();
dr.Close();
return(new ListItem(_wareHouseName, _wareHouseName));
}
else
{
return(new ListItem("无", "无"));
}
}
public static List <FileDTO> GetAllFiles(int parentID)
{
var query = String.Format("Select * from dbo.Files Where parentfolderid={0}", parentID);
using (DBHelper helper = new DBHelper())
{
var reader = helper.ExecuteReader(query);
List <FileDTO> list = new List <FileDTO>();
while (reader.Read())
{
var dto = FillDTO(reader);
if (dto != null)
{
list.Add(dto);
}
}
return(list);
}
}
/// <summary>
/// 根据店铺编号获取店铺信息
/// </summary>
/// <param name="storeId">店编号</param>
/// <returns></returns>
public StoreInfoModel GetStoreInfoByStoreId(string storeId)
{
StoreInfoModel storeInfo = null;
string sql = "select bank,bankcard from storeinfo where storeid=@num";
SqlParameter[] spa = new SqlParameter[] {
new SqlParameter("@num", SqlDbType.NVarChar, 50)
};
spa[0].Value = storeId;
SqlDataReader reader = DBHelper.ExecuteReader(sql, spa, CommandType.Text);
if (reader.Read())
{
storeInfo = new StoreInfoModel();
storeInfo.BankCode = reader[0].ToString();
storeInfo.BankCard = reader[1].ToString();
}
reader.Close();
return(storeInfo);
}
/// <summary>
/// 得到该店库存价格
/// </summary>
/// <param name="storeId"></param>
/// <returns>返回价格</returns>
public double GetStockMoney(string storeId)
{
//价格
double money = 0;
string SQL = @"select P.PreferentialPrice as Money,D.ActualStorage as Amount
from d_kucun as D,product as P where P.ProductID = D.ProductID and D.StoreID =@storeId";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@storeId", storeId)
};
SqlDataReader reader = DBHelper.ExecuteReader(SQL, para, CommandType.Text);
//循环累加
while (reader.Read())
{
money += Convert.ToDouble(reader[0]) * Convert.ToInt64(reader[1]);
}
reader.Close();
return(money);
}
/// <summary>
/// 查询所有用户
/// </summary>
/// <returns></returns>
public static List <Users> ChaXunAll()
{
List <Users> a = new List <Users>();
string sqlTest = "select * from Users";
SqlDataReader sdr = DBHelper.ExecuteReader(sqlTest, CommandType.Text, null);
if (sdr.HasRows)
{
while (sdr.Read())
{
Users pro = new Users();
//pro.L_NAME = int.Parse(sdr["id"].ToString());
pro.U_NAME = sdr["U_NAME"].ToString();
pro.U_PSSWORD = sdr["U_PSSWORD"].ToString();
//pro.Name = sdr["UserCompellation"].ToString();
a.Add(pro);
}
}
sdr.Close();
return(a);
}
/// <summary>
/// 获取所有供应商
/// </summary>
/// <returns></returns>
public static IList <WareHouseModel> GetWareHouses()
{
string sql = "select WareHouseID,WareControl,WareHouseName from WareHouse";
SqlDataReader dr = DBHelper.ExecuteReader(sql, CommandType.Text);
IList <WareHouseModel> ps = null;
if (dr.HasRows)
{
ps = new List <WareHouseModel>();
while (dr.Read())
{
WareHouseModel wm = new WareHouseModel();
wm.WareHouseID = dr.GetInt32(0);
wm.WareControl = dr.GetInt32(1);
wm.WareHouseName = dr.GetString(2);
ps.Add(wm);
}
}
dr.Close();
return(ps);
}
/// <summary>
/// 高级搜索
/// </summary>
/// <param name="s">起始位置</param>
/// <param name="e">结束为止</param>
/// <param name="key">关键词</param>
/// <param name="dpid">店铺ID</param>
/// <param name="caijidate">最后采集时间</param>
/// <param name="orderby">排序方式</param>
/// <returns>IList<opponent_dianpu_info></returns>
public IList <opponent_dianpu_info> Search(int s, int e, string key, string shopid, string caijidate, string orderby)
{
string sql1 = "select top " + (e - s + 1).ToString() + " * from opponent_dianpu_info where ";
string sql2 = string.IsNullOrEmpty(key) ? " 1=1 " : " ( odigoodsnum like '%" + key + "%' or odisell0 like '%" + key + "%' or odiSKUcount like '%" + key + "%' or odipricehigh like '%" + key + "%' or odipricelowset like '%" + key + "%' or odipriceaverage like '%" + key + "%' or odisellhigh like '%" + key + "%' or odiselllowset like '%" + key + "%' or odisellaverage like '%" + key + "%' or odisalescount like '%" + key + "%' or odisellcount like '%" + key + "%' or odidongxiao like '%" + key + "%' or odibaozhangjin like '%" + key + "%' or odiDSRmiaoshu like '%" + key + "%' or odiDSRzhiliang like '%" + key + "%' or odiDSRwuliu like '%" + key + "%' or oshopid like '%" + key + "%' or oremark1 like '%" + key + "%' or oremark2 like '%" + key + "%' or oremark3 like '%" + key + "%' ) ";
string sql3 = string.IsNullOrEmpty(shopid) ? "" : " and oshopid = '" + shopid + "' ";
string sql4 = string.IsNullOrEmpty(caijidate) ? "" : " and ocollectdate like '%" + caijidate + "%' ";
string sql8 = string.IsNullOrEmpty(orderby) ? " order by ocollectdate desc " : " order by " + orderby;
string sql7 = s == 1 ? "" : " and odiid not in ( select top " + (s - 1).ToString() + " odiid from opponent_dianpu_info where " + sql2 + sql3 + sql4 + sql8 + " ) ";
DBHelper.sqlstr = sql1 + sql2 + sql3 + sql4 + sql7 + sql8;
List <opponent_dianpu_info> list = new List <opponent_dianpu_info>();
SqlDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
opponent_dianpu_info Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
public List<Assessment> GetAssessments(string Name, string GroupId, string EffectiveFrom, string EffectiveTo, char Status, int CurrentUserId)
{
List<Assessment> items = new List<Assessment>();
const string procName = "GET_ASSESSMENTS";
using (dbhAssessments = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhAssessments.AddParameter("@NAME", Name);
dbhAssessments.AddParameter("@ASSESSMENT_GROUP_ID_REF", GroupId);
dbhAssessments.AddParameter("@EFFECTIVE_FROM", EffectiveFrom);
dbhAssessments.AddParameter("@EFFECTIVE_TO", EffectiveTo);
dbhAssessments.AddParameter("@STATUS", Status);
dbhAssessments.AddParameter("@CURRENTUSERID", CurrentUserId);
IDataReader dr = dbhAssessments.ExecuteReader(procName);
while (dr.Read())
{
items.Add(new Assessment(dr));
}
dbhAssessments.Dispose();
}
return items;
}
/// <summary>
/// 模糊搜索
/// </summary>
/// <param name="key">关键词</param>
/// <param name="state">状态</param>
/// <param name="id">int字段</param>
/// <param name="orderby">排序</param>
/// <returns>IList<Basic></returns>
public IList <Basic> Search(int s, int e, string key, string state, int id, string orderby)
{
string sql1 = "select top " + (e - s + 1).ToString() + " * from Basic where ";
string sql2 = string.IsNullOrEmpty(key) ? " 1=1 " : " ( Sign1 like '%" + key + "%' or Sign2 like '%" + key + "%' or Sign3 like '%" + key + "%' or Sign4 like '%" + key + "%' or State like '%" + key + "%' )"; //删除无用字段，删除最后一个or
string sql3 = string.IsNullOrEmpty(state) ? "" : " and State= '" + state + "' "; //状态字段，无用删除
string sql4 = id == 0 ? "" : " and id字段='" + id.ToString() + "' "; //Int字段，无用删除
string sql7 = string.IsNullOrEmpty(orderby) ? " order by ID desc " : " order by " + orderby;
string sql8 = s == 1 ? "" : " and ID not in ( select top " + (s - 1).ToString() + " ID from Basic where " + sql2 + sql3 + sql4 + sql7 + " ) ";
DBHelper.sqlstr = sql1 + sql2 + sql3 + sql4 + sql8 + sql7;
List <Basic> list = new List <Basic>();
SqlDataReader reader = DBHelper.ExecuteReader();
while (reader.Read())
{
Basic Obj = GetByReader(reader);
list.Add(Obj);
}
reader.Close();
return(list);
}
/// <summary>
/// 根据仓库id获取仓库名字
/// </summary>
/// <param name="wareHouseNameID"></param>
/// <returns></returns>
public static string GetWareHouseName(string wareHouseNameID)
{
string rt = "";
string sql = "select WareHouseName from WareHouse where warehouseid=@num";
SqlParameter[] sparams = new SqlParameter[]
{
new SqlParameter("@num", SqlDbType.Int)
};
sparams[0].Value = Convert.ToInt32(wareHouseNameID);
SqlDataReader sdr = DBHelper.ExecuteReader(sql, sparams, CommandType.Text);
if (sdr.Read())
{
rt = sdr["Warehousename"].ToString();
}
sdr.Close();
return(rt);
}
public List<Question> GetQuestions(string Content, int GroupId, int ResponseId, char status, char IsMandatory, int CurrentUserId)
{
List<Question> items = new List<Question>();
const string procName = "GET_QUESTIONS";
using (dbhQuestion = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhQuestion.AddParameter("@CONTENT", Content);
dbhQuestion.AddParameter("@QGROUP_ID", GroupId);
dbhQuestion.AddParameter("@RESPONSETYPE_ID", ResponseId);
dbhQuestion.AddParameter("@STATUS", status);
dbhQuestion.AddParameter("@IS_MANDATORY", IsMandatory);
dbhQuestion.AddParameter("@CURRENTUSERID", CurrentUserId);
IDataReader dr = dbhQuestion.ExecuteReader(procName);
while (dr.Read())
{
items.Add(new Question(dr));
}
dbhQuestion.Dispose();
}
return items;
}
/// <summary>
/// 根据产品id获取产品名字
/// </summary>
/// <param name="productID"></param>
/// <returns></returns>
public static string GetProductName(string productID)
{
string rt = "";
string Sql2 = "select Productname from product where productcode=@num";
SqlParameter[] sparams = new SqlParameter[]
{
new SqlParameter("@num", SqlDbType.VarChar, 100)
};
sparams[0].Value = productID;
SqlDataReader sdr2 = DBHelper.ExecuteReader(Sql2, sparams, CommandType.Text);
if (sdr2.Read())
{
rt = sdr2["productname"].ToString();
}
sdr2.Close();
return(rt);
}
public static MessageReciveModel getMessageRecive(int id)
{
string sql = "Update MESSAGERECEIVE set ReadFlag=1 where id=" + id;
DBHelper.ExecuteNonQuery(sql);
SqlDataReader reader = DBHelper.ExecuteReader("select messagesendId,LoginRole,Receive,SenderRole,Sender,SendDate,InfoTitle from messagereceive where id=" + id);
MessageReciveModel message = new MessageReciveModel();
if (reader.Read())
{
message.Messagesendid = Convert.ToInt32(reader["Messagesendid"]);
message.LoginRole = reader["LoginRole"].ToString();
message.Recive = reader["Receive"].ToString();
message.SenderRole = reader["SenderRole"].ToString();
message.Sender = reader["sender"].ToString();
message.Senddate = Convert.ToDateTime(reader["Senddate"]);
message.InfoTitle = reader["InfoTitle"].ToString();
}
reader.Close();
return(message);
}
/// <summary>
/// 检查某产品是否放生了业务 ---DS2012
/// </summary>
/// <param name="productID">产品ID</param>
/// <returns>当发生了业务，返回为真，否则返回假</returns>
public static bool CheckProductWheatherHasOperation(int productID)
{
SqlParameter[] sparams = new SqlParameter[]
{
new SqlParameter("@productID", SqlDbType.Int)
};
sparams[0].Value = productID;
SqlDataReader reader = DBHelper.ExecuteReader("CheckProductWheatherHasOperation", sparams, CommandType.StoredProcedure);
if (reader.Read())
{
reader.Close();
return(true);
}
else
{
reader.Close();
return(false);
}
}
/// <summary>
/// 获取对应库位xml字符串
/// </summary>
/// <returns></returns>
public static string GetDepotSeatXML(string idvalue)
{
string cmd = "select DepotSeatID,SeatName from dbo.DepotSeat where WareHouseID=@num";
SqlParameter[] sPara = new SqlParameter[] { new SqlParameter("@num", SqlDbType.Int) };
sPara[0].Value = Convert.ToInt32(idvalue);
SqlDataReader dr = DBHelper.ExecuteReader(cmd, sPara, CommandType.Text);
string xmlstr = "<?xml version='1.0' encoding='utf-8' ?><Root>";
while (dr.Read())
{
xmlstr = xmlstr + "<Row><DepotSeatID>" + dr["DepotSeatID"] + "</DepotSeatID><SeatName>" + dr["SeatName"] + "</SeatName></Row>";
}
xmlstr = xmlstr + "</Root>";
dr.Close();
return(xmlstr);
}
public MemberInfo(int MemberMasterID, int CurrentUserID)
{
mCurrentUserID = CurrentUserID;
using (dbhMemberInfo = new DBHelper(ConnectionStrings.DefaultDBConnection, mCurrentUserID))
{
dbhMemberInfo.AddParameter("@MemberMasterID", MemberMasterID);
dbhMemberInfo.AddParameter("@CurrentUserID", mCurrentUserID);
IDataReader reader = dbhMemberInfo.ExecuteReader("GET_MEMBER_BY_MASTER_ID");
if (reader.Read())
{
LoadMemberInfo(reader);
if (reader.NextResult())
{
while (reader.Read())
{
familyMembers.Add(new FamilyMember(reader));
}
}
}
}
}
public QuestionGroup GetQuestionGroup_By_ID(int ID)
{
QuestionGroup obj = new QuestionGroup(ID,1);
const string procName = "GET_QUESTIONGROUPS_BY_ID";
using (dbhQuestionGroup = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhQuestionGroup.AddParameter("@id", ID);
dbhQuestionGroup.AddParameter("@CURRENTUSERID", mCurrentUserID);
IDataReader dr = dbhQuestionGroup.ExecuteReader(procName);
while (dr.Read())
{
obj = (new QuestionGroup(dr));
}
dbhQuestionGroup.Dispose();
}
return obj;
}
public Assessment GetAssessmentsById(int CurrentUserId, int ID)
{
Assessment item = null;
const string procName = "GET_ASSESSMENT_BY_ID";
using (dbhAssessments = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhAssessments.AddParameter("@ID", ID);
dbhAssessments.AddParameter("@CURRENTUSERID", CurrentUserId);
IDataReader dr = dbhAssessments.ExecuteReader(procName);
while (dr.Read())
{
item = new Assessment(dr);
}
dbhAssessments.Dispose();
}
return item;
}
public List<Question> GetAssessmentQuestions(int AssessmentId, int CurrentUserId)
{
List<Question> lst = new List<Question>();
const string procName = "GET_ASSESSMENT_QUESTIONS";
using (dbhQuestionGroup = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhQuestionGroup.AddParameter("@ASSESSMENT_ID", AssessmentId);
dbhQuestionGroup.AddParameter("@CURRENTUSERID", CurrentUserId);
IDataReader dr = dbhQuestionGroup.ExecuteReader(procName);
while (dr.Read())
{
lst.Add(new Question(dr));
}
if (dr.NextResult())
{
while (dr.Read())
{
for (int i = 0; i < lst.Count; i++)
{
if (Convert.ToInt32(dr["QUESTION_ID"]) == lst[i].ID)
{
lst[i].Options.Add(Tuple.Create<int, string, string>(Convert.ToInt32(dr["QUESTION_ID"]), Convert.ToString(dr["OPTION"]), Convert.ToString(dr["VALUE"])));
}
}
}
}
dbhQuestionGroup.Dispose();
}
return lst;
}
public void SaveFamilyResponses()
{
String Response = GetFamilyResponseXML();
string procName = "INSERTUPDATE_FAMILY_RESPONSE";
using (dbhAssessmentResponse = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhAssessmentResponse.AddParameter("@CurrentUserID", this.mCurrentUserID);
dbhAssessmentResponse.AddParameter("@ResponseXML", Response);
dbhAssessmentResponse.AddParameter("@AssessmentID", this.AssessmentID);
dbhAssessmentResponse.AddParameter("@MemberMasterID", this.MemberMasterID);
IDataReader dr = dbhAssessmentResponse.ExecuteReader(procName);
dbhAssessmentResponse.Dispose();
}
}
public void Save()
{
using (dbhMember = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhMember.AddParameter("@ID", this.ID);
dbhMember.AddParameter("@SALUTATION", this.Salutation);
dbhMember.AddParameter("@FIRSTNAME", this.Firstname);
dbhMember.AddParameter("@MIDDLENAME", this.Middlename);
dbhMember.AddParameter("@LASTNAME", this.Lastname);
dbhMember.AddParameter("@SEX", this.Sex);
dbhMember.AddParameter("@MEMBER_ID", this.MemberID);
dbhMember.AddParameter("@HICN", this.HICN);
dbhMember.AddParameter("@SUBSCRIBER_ID", this.MemberID);
dbhMember.AddParameter("@DOB", this.DOB);
dbhMember.AddParameter("@EMAIL", this.Email);
dbhMember.AddParameter("@ETHNICITY", this.Ethnicity);
dbhMember.AddParameter("@MARITAL_STATUS", this.MaritalStatus);
dbhMember.AddParameter("@HANDEDNESS", this.Handedness);
dbhMember.AddParameter("@OCCUPATION", this.Occupation);
dbhMember.AddParameter("@HEIGHT_FEET", this.Height_Feet);
dbhMember.AddParameter("@HEIGHT_INCHES", this.Height_Inches);
dbhMember.AddParameter("@WEIGHT_LBS", this.Weight);
dbhMember.AddParameter("@WADDRESS1", this.WorkAddress.Address1);
dbhMember.AddParameter("@WADDRESS2", this.WorkAddress.Address2);
dbhMember.AddParameter("@WCITY", this.WorkAddress.City);
dbhMember.AddParameter("@WSTATE", this.WorkAddress.State);
dbhMember.AddParameter("@WZIPCODE", this.WorkAddress.Zipcode);
dbhMember.AddParameter("@HADDRESS1", this.HomeAddress.Address1);
dbhMember.AddParameter("@HADDRESS2", this.HomeAddress.Address2);
dbhMember.AddParameter("@HCITY", this.HomeAddress.City);
dbhMember.AddParameter("@HSTATE", this.HomeAddress.State);
dbhMember.AddParameter("@HZIPCODE", this.HomeAddress.Zipcode);
dbhMember.AddParameter("@CURRENTUSERID", mCurrentUserID);
IDataReader reader = dbhMember.ExecuteReader("INSERTUPDATE_MEMBER_MASTER_INFO");
while (reader.Read())
{
ID = Convert.ToInt32(reader[0].ToString());
//LoadBasicInfo(reader);
//reader.NextResult();
//WorkAddress = new Address(reader);
//reader.NextResult();
//HomeAddress = new Address(reader);
}
}
}
public List<Assessment> GetAssessmentsForMember(int MemberMasterID, int CurrentUserID)
{
List<Assessment> items = new List<Assessment>();
const string procName = "GET_ASSESSMENTS_FOR_MEMBER";
using (dbhAssessments = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhAssessments.AddParameter("@MemberMasterID", MemberMasterID);
dbhAssessments.AddParameter("@CURRENTUSERID", CurrentUserID);
IDataReader dr = dbhAssessments.ExecuteReader(procName);
while (dr.Read())
{
Assessment a = new Assessment(CurrentUserID);
a.LoadAssessmentInfoForMember(dr);
items.Add(a);
}
dbhAssessments.Dispose();
}
return items;
}
public void Save()
{
string procName = "INSERTUPDATE_QUESTIONGROUP";
using (dbhQuestionGroup = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhQuestionGroup.AddParameter("@id", this.ID);
dbhQuestionGroup.AddParameter("@name", this.Name);
dbhQuestionGroup.AddParameter("@description", this.Description);
dbhQuestionGroup.AddParameter("@status", this.Status);
dbhQuestionGroup.AddParameter("@CURRENTUSERID", mCurrentUserID);
IDataReader dr = dbhQuestionGroup.ExecuteReader(procName);
while (dr.Read())
{
LoadReader(dr);
}
dbhQuestionGroup.Dispose();
}
}
public void Save(int userid)
{
string procName = "INSERTUPDATE_QUESTIONS";
using (dbhQuestionGroup = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhQuestionGroup.AddParameter("@id", this.ID);
dbhQuestionGroup.AddParameter("@qgroupid_ref", this.QGroupId_Ref);
dbhQuestionGroup.AddParameter("@responsetypeid_ref", this.QResponseTypeId_Ref);
dbhQuestionGroup.AddParameter("@qcontent", this.Content);
dbhQuestionGroup.AddParameter("@options", this.ResponseText);
//dbhQuestionGroup.AddParameter("@displayorder", this.DisplayOrder);
dbhQuestionGroup.AddParameter("@gender", this.Gender);
//dbhQuestionGroup.AddParameter("@narrative", this.Narrative);
dbhQuestionGroup.AddParameter("@helptext", this.HelpText);
dbhQuestionGroup.AddParameter("@ismandatory", this.IsMandatory);
dbhQuestionGroup.AddParameter("@status", this.Status);
dbhQuestionGroup.AddParameter("@CURRENTUSERID", userid);
IDataReader dr = dbhQuestionGroup.ExecuteReader(procName);
while (dr.Read())
{
LoadQuestion(dr);
}
dbhQuestionGroup.Dispose();
}
}
public void Save()
{
using (dbhAssessment = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhAssessment.AddParameter("@ID",this.ID);
dbhAssessment.AddParameter("@NAME", this.Name);
dbhAssessment.AddParameter("@DESCRIPTION", this.Description);
dbhAssessment.AddParameter("@ASSESSMENT_GROUP_ID_REF", this.AssessmentGroupId);
dbhAssessment.AddParameter("@EFFECTIVE_FROM", this.EffectiveFrom);
dbhAssessment.AddParameter("@EFFECTIVE_TO", this.EffectiveTo);
dbhAssessment.AddParameter("@STATUS", this.Status);
dbhAssessment.AddParameter("@NARRATIVE_HEADER", this.NarrativeHeader);
dbhAssessment.AddParameter("@NARRATIVE_FOOTER", this.NarrativeFooter);
dbhAssessment.AddParameter("@CURRENTUSERID", mCurrentUserID);
IDataReader reader = dbhAssessment.ExecuteReader("INSERTUPDATE_ASSESSMENTS");
if(reader.Read())
{
LoadReader(reader);
}
}
}
public List<Question> SaveQuestions(int AssessmentID, int GROUPID, string QUESTIONLIST, string DISPLAYORDER, int CurrentUserId)
{
List<Question> lst = new List<Question>();
using (dbhAssessments = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
dbhAssessments.AddParameter("@ASSESSMENT_ID", AssessmentID);
dbhAssessments.AddParameter("@QUESTION_GROUP_ID", GROUPID);
dbhAssessments.AddParameter("@QUESTIONS_LIST", QUESTIONLIST);
dbhAssessments.AddParameter("@DISPLAY_ORDER", DISPLAYORDER);
dbhAssessments.AddParameter("@CURRENTUSERID", CurrentUserId);
IDataReader reader = dbhAssessments.ExecuteReader("INSERTUPDATE_ASSESSMENT_QUESTIONS");
while (reader.Read())
{
lst.Add(new Question(reader));
}
}
return lst;
}