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<ProductDetails> GetProducts(string searchCondition)
{
List<ProductDetails> lst = new List<ProductDetails>();
DBHelper helper = new DBHelper();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter(CONSTANT.PRODUCT_SEARCH_CRITERIA, searchCondition);
DataSet ds = helper.executeSelectQuery(PROCEDURES.PRODUCT_SEARCH, param);
if (ds != null)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
ProductDetails sub = new ProductDetails();
sub.ProductName = row["Prod_Name"].ToString();
sub.ProductImage = row["Prod_Img"].ToString();
sub.ProductMRP = row["MRP"].ToString();
sub.ProductPrice = row["Selling Price"].ToString();
sub.ProductQuanity = row["packed_quantity"].ToString();
sub.ProductId = row["Prod_Id"].ToString();
sub.ProductBrand = row["Brand_Name"].ToString();
lst.Add(sub);
}
}
return lst;
}
public List<ProductDetails> getSubCategory(string SubCategoryName)
{
List<ProductDetails> lst = new List<ProductDetails>();
DBHelper helper = new DBHelper();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter(CONSTANT.SUB_CAT_NAME, SubCategoryName);
DataSet ds = helper.executeSelectQuery(PROCEDURES.GET_SUB_CATEGORY_WISE_PRODUCT, param);
if (ds != null)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
ProductDetails sub = new ProductDetails();
sub.ProductName = row["Prod_Name"].ToString();
sub.ProductImage = row["Prod_Img"].ToString();
sub.ProductMRP = row["MRP"].ToString();
sub.ProductPrice = row["Selling Price"].ToString();
sub.ProductQuanity = row["packed_quantity"].ToString();
sub.ProductId = row["Prod_Id"].ToString();
sub.ProductBrand = row["Brand_Name"].ToString();
lst.Add(sub);
}
}
return lst;
}
public ProductDetails GetProductBasedOnID(string productId)
{
// List<ProductDetails> lst = new List<ProductDetails>();
DBHelper helper = new DBHelper();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter(CONSTANT.PRODUCT_ID, productId);
ProductDetails productDetails = new ProductDetails();
DataSet ds = helper.executeSelectQuery(PROCEDURES.GET_PRODUCT_ID_WISE_PRODUCT, param);
if (ds != null)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
productDetails.ProductName = row["Prod_Name"].ToString();
productDetails.ProductImage = row["Prod_Img"].ToString();
productDetails.ProductMRP = row["MRP"].ToString();
productDetails.ProductPrice = row["Selling Price"].ToString();
productDetails.ProductQuanity = row["packed_quantity"].ToString();
productDetails.ProductId = row["Prod_Id"].ToString();
productDetails.ProductBrand = row["Brand_Name"].ToString();
//lst.Add(productDetails);
}
}
return productDetails;
}
public List<ProductDetails> GetAllProducts()
{
List<ProductDetails> lst = new List<ProductDetails>();
DBHelper helper = new DBHelper();
DataSet ds = helper.executeSelectQuery(PROCEDURES.GET_ALL_PRODUCTS);
if (ds != null)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
ProductDetails sub = new ProductDetails();
sub.ProductName = row["Prod_Name"].ToString();
sub.ProductImage = row["Prod_Img"].ToString();
sub.ProductMRP = row["MRP"].ToString();
sub.ProductPrice = row["Selling Price"].ToString();
sub.ProductQuanity = row["packed_quantity"].ToString();
sub.ProductId = row["Prod_Id"].ToString();
sub.ProductBrand = row["Brand_Name"].ToString();
lst.Add(sub);
}
}
return lst;
}
public DataTable GetQResponseTypes()
{
DataTable dt = new DataTable();
string procName = "usp_GETRESPONSETYPES";
using (dbhQuestion = new DBHelper(ConnectionStrings.DefaultDBConnection))
{
DataSet ds = dbhQuestion.ExecuteDataSet(procName);
if (ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
}
return dt;
}
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 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 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 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 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<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<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 List<Tuple<int, string>> GetMemberHRAResponse(int MemberMasterID, int AssessmentId, int CurrentUserID)
{
List<Tuple<int, string>> lst = new List<Tuple<int, string>>();
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_MEMBER_HRA_RESPONSES");
while (reader.Read())
{
lst.Add(Tuple.Create<int, string>(Convert.ToInt32(reader["QUESTION_ID_REF"]), reader["RESPONSE"].ToString()));
}
}
return lst;
}
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 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<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<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 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 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 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<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 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;
}
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;
}
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 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 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 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 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();
}
}