// GET api/<controller>
//[ApiAuthorize]
public UserInfo Get()
{
string query = "select * from userinfo";
DataSet aSet = aGateway.Select(query);
List<UserInfo> userInfos = new List<UserInfo>();
UserInfo aUserInfo = new UserInfo();
foreach (DataRow dataRow in aSet.Tables[0].Rows)
{
aUserInfo.Id = Convert.ToInt32(dataRow["ID"].ToString());
aUserInfo.UserId = Convert.ToInt32(dataRow["UserID"].ToString());
aUserInfo.UserName = dataRow["UserName"].ToString();
aUserInfo.FullName = dataRow["FullName"].ToString();
aUserInfo.FatherName = dataRow["FatherName"].ToString();
aUserInfo.MotherName = dataRow["MotherName"].ToString();
aUserInfo.PhoneNumber = dataRow["CellNumber"].ToString();
aUserInfo.BirthDate = (DateTime)dataRow["BirthDay"];
aUserInfo.CurrentAddress = dataRow["CurrentAddress"].ToString();
aUserInfo.PermanentAddress = dataRow["PermanentAddress"].ToString();
aUserInfo.BirthCertificateID = dataRow["BirthCertificateID"].ToString();
}
query = "select * from childInfo where ParentID=1";
DataSet anotherDataSet = aGateway.Select(query);
List<ChildInfo> childList = new List<ChildInfo>();
foreach (DataRow dataRow in anotherDataSet.Tables[0].Rows)
{
ChildInfo aChildInfo = new ChildInfo();
aChildInfo.ID = Convert.ToInt32(dataRow["ID"].ToString());
aChildInfo.ParentID = Convert.ToInt32(dataRow["ParentID"].ToString());
aChildInfo.Name = dataRow["Name"].ToString();
aChildInfo.BirthCertificateID = dataRow["BirthCertificateID"].ToString();
aChildInfo.BirthDate = (DateTime)dataRow["Birthdate"];
childList.Add(aChildInfo);
}
aUserInfo.Childs = childList;
return aUserInfo;
}
// [ApiAuthorize]
// GET api/customers
public PagedResult<UserInfo> Get(int pageNo = 1, int pageSize = 50, [FromUri] string[] sort = null, string search = null)
{
TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
// search = textInfo.ToTitleCase(search);
// Determine the number of records to skip
int skip = (pageNo - 1) * pageSize;
UserRoleManager aRoleManager=new UserRoleManager();
int roleId=aRoleManager.GetRoleID();
List<UserRole> rolesList=new List<UserRole>();
rolesList=aRoleManager.GetRoleLevels(roleId);
string inClause =string.Empty;
int i =1;
int j = rolesList.Count;
foreach (UserRole aRole in rolesList)
{
if (i < j)
{
inClause += aRole.Id + ",";
}
else { inClause += aRole.Id; }
i++;
}
string query = "SELECT * FROM `userInfo` LIMIT 1000;";
string query1 = "select * from userInfo where userID in (select id from users where userroleid in ("+inClause+"))";
DataSet aSet = aGateway.Select(query1);
//IQueryable<Customer> queryable;
List<UserInfo> aUsersList = new List<UserInfo>();
//UserInfo aUserInfo = new UserInfo();
foreach (DataRow dataRow in aSet.Tables[0].Rows)
{
UserInfo aUserInfo = new UserInfo();
aUserInfo.Id = Convert.ToInt32(dataRow["ID"].ToString());
aUserInfo.UserId = Convert.ToInt32(dataRow["UserID"].ToString());
aUserInfo.UserName = dataRow["UserName"].ToString();
aUserInfo.FullName = dataRow["FullName"].ToString();
aUserInfo.FatherName = dataRow["FatherName"].ToString();
aUserInfo.MotherName = dataRow["MotherName"].ToString();
aUserInfo.PhoneNumber = dataRow["CellNumber"].ToString();
aUserInfo.BirthDate = (DateTime)dataRow["BirthDay"];
aUserInfo.CurrentAddress = dataRow["CurrentAddress"].ToString();
aUserInfo.PermanentAddress = dataRow["PermanentAddress"].ToString();
aUserInfo.BirthCertificateID = dataRow["BirthCertificateID"].ToString();
aUsersList.Add(aUserInfo);
}
IQueryable<UserInfo> queryable = aUsersList.AsQueryable();
// Apply the search
if (!String.IsNullOrEmpty(search))
{
string[] searchElements = search.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string searchElement in searchElements)
{
string element = searchElement;
queryable = queryable.Where(c => c.UserName.ToLower().Contains(element.ToLower()) || c.FullName.ToLower().Contains(element.ToLower()) || c.FatherName.ToLower().Contains(element.ToLower()) || c.MotherName.ToLower().Contains(element.ToLower()) || c.PhoneNumber.ToLower().Contains(element.ToLower()));
}
pageNo = 1;
}
// Add the sorting
if (sort != null)
{
queryable = queryable.ApplySorting(sort);
pageNo = 1;
}
else
queryable = queryable.OrderBy(c => c.Id);
List<UserInfo> aList = new List<UserInfo>();
// Get the total number of records
int totalItemCount = queryable.Count();
var users = aList;
// Retrieve the customers for the specified page
if (String.IsNullOrEmpty(search))
{
users = queryable
.Skip(skip)
.Take(pageSize)
.ToList();
}
else
{
users = queryable
//.Skip(0)
//.Take(pageSize)
.ToList();
if (users.Count >= skip)
{
users = users.Skip(skip).Take(pageSize).ToList();
}
else { users = users.Skip(0).Take(pageSize).ToList(); }
}
// Return the paged results
return new PagedResult<UserInfo>(users, pageNo, pageSize, totalItemCount);
}
public UserInfo Get(int id)
{
string query = "select * from userinfo where id=@id;";
string query1 = "select userinfo.ID,userinfo.UserID,userinfo.UserName,userinfo.FullName,userinfo.FatherName,userinfo.MotherName,userinfo.CellNumber,userinfo.BirthDay,userinfo.CurrentAddress,userinfo.PermanentAddress,userinfo.BirthCertificateID,users.UserRoleId from userinfo,users where userinfo.id=@id and users.ID=userInfo.UserId;";
Hashtable aTable = new Hashtable() { { "id", id } };
DataSet aSet = aGateway.Select(query1, aTable);
List<UserInfo> userInfos = new List<UserInfo>();
UserInfo aUserInfo = new UserInfo();
foreach (DataRow dataRow in aSet.Tables[0].Rows)
{
aUserInfo.Id = Convert.ToInt32(dataRow["ID"].ToString());
aUserInfo.UserId = Convert.ToInt32(dataRow["UserID"].ToString());
aUserInfo.UserName = dataRow["UserName"].ToString();
aUserInfo.FullName = dataRow["FullName"].ToString();
aUserInfo.FatherName = dataRow["FatherName"].ToString();
aUserInfo.MotherName = dataRow["MotherName"].ToString();
aUserInfo.PhoneNumber = dataRow["CellNumber"].ToString();
aUserInfo.BirthDate = (DateTime)dataRow["BirthDay"];
aUserInfo.CurrentAddress = dataRow["CurrentAddress"].ToString();
aUserInfo.PermanentAddress = dataRow["PermanentAddress"].ToString();
aUserInfo.BirthCertificateID = dataRow["BirthCertificateID"].ToString();
aUserInfo.UserRole = Convert.ToInt32(dataRow["UserRoleId"].ToString());
}
query = "select * from childInfo where ParentID=1";
DataSet anotherDataSet = aGateway.Select(query);
List<ChildInfo> childList = new List<ChildInfo>();
foreach (DataRow dataRow in anotherDataSet.Tables[0].Rows)
{
ChildInfo aChildInfo = new ChildInfo();
aChildInfo.ID = Convert.ToInt32(dataRow["ID"].ToString());
aChildInfo.ParentID = Convert.ToInt32(dataRow["ParentID"].ToString());
aChildInfo.Name = dataRow["Name"].ToString();
aChildInfo.BirthCertificateID = dataRow["BirthCertificateID"].ToString();
aChildInfo.BirthDate = (DateTime)dataRow["Birthdate"];
childList.Add(aChildInfo);
}
aUserInfo.Childs = childList;
return aUserInfo;
}
// POST api/<controller>
public HttpResponseMessage Put(UserInfo aUser)
{
if (ModelState.IsValid)
{
string query = "UPDATE `tikaappdb`.`userinfo` SET `UserName`=@userName, `FullName`=@fullName, `FatherName`=@fathersName, `MotherName`=@mothersName, `CellNumber`=@phoneNumber, `BirthDay`=@birthdate, `CurrentAddress`=@currentAddress, `PermanentAddress`=@permanentAddress, `BirthCertificateID`=@birthCertificate WHERE `ID`=@id;";
Hashtable aHashtable = new Hashtable();
aHashtable.Add("fullName", aUser.FullName);
aHashtable.Add("id", aUser.Id);
aHashtable.Add("userName", aUser.UserName);
aHashtable.Add("fathersName", aUser.FatherName);
aHashtable.Add("mothersName", aUser.MotherName);
aHashtable.Add("phoneNumber", aUser.PhoneNumber);
aHashtable.Add("birthdate", aUser.BirthDate);
aHashtable.Add("birthCertificate", aUser.BirthCertificateID);
aHashtable.Add("currentAddress", aUser.CurrentAddress);
aHashtable.Add("permanentAddress", aUser.PermanentAddress);
aGateway.Update(query, aHashtable);
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, aUser);
return response;
}
else
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
}
}
// PUT api/<controller>/5
public HttpResponseMessage Post(UserInfo aUser)
{
if (ModelState.IsValid)
{
string phoneQuery = "Select * from userinfo where CellNumber=" + aUser.PhoneNumber + " ;";
string userNameQuery = "Select * from users where UserName='******' ;";
DataSet aDataSet = aGateway.Select(phoneQuery);
DataSet anotherDataSet = aGateway.Select(userNameQuery);
if (aDataSet.Tables[0].Rows.Count > 0)
{
var message = string.Format("Duplicate Phone Number");
HttpError err = new HttpError(message);
return Request.CreateResponse(HttpStatusCode.NotAcceptable, err);
}
else if (anotherDataSet.Tables[0].Rows.Count > 0)
{
var message = string.Format("Duplicate UserName");
HttpError err = new HttpError(message);
return Request.CreateResponse(HttpStatusCode.NotAcceptable, err);
}
else
{
string usersQuery = "insert into users(UserName,UserCellNumber,UserRoleId) values (@UserName,@CellNumber,"+aUser.UserRole+") ";
string userInfoQuery =
"INSERT INTO `tikaappdb`.`userinfo` (`UserId`, `UserName`, `FullName`, `FatherName`, `MotherName`, `CellNumber`, `BirthDay`, `CurrentAddress`, `PermanentAddress`, `BirthCertificateID`) VALUES ((select ID from users where UserName='******'), @UserName, @FullName, @FatherName, @MotherName, @CellNumber, @BirthDay, @CurrentAddress, @PermanentAddress, @BirthCertificateID);";
Hashtable aHashtable = new Hashtable();
aHashtable.Add("id", aUser.Id);
// aHashtable.Add("UserID", 101);
aHashtable.Add("UserName", aUser.UserName);
aHashtable.Add("FullName", aUser.FullName);
aHashtable.Add("FatherName", aUser.FatherName);
aHashtable.Add("MotherName", aUser.MotherName);
aHashtable.Add("CellNumber", aUser.PhoneNumber);
aHashtable.Add("BirthDay", aUser.BirthDate);
aHashtable.Add("CurrentAddress", aUser.CurrentAddress);
aHashtable.Add("PermanentAddress", aUser.PermanentAddress);
aHashtable.Add("BirthCertificateID", aUser.BirthCertificateID);
aGateway.Insert(usersQuery, aHashtable);
aGateway.Insert(userInfoQuery, aHashtable);
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, aUser);
return response;
}
}
else
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
}
}