public List <Client> GetClientDetailsAsync(int Id)
{
try
{
SqlDataReader reader = null;
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_ClientGet";
sqlCmd.Parameters.Add(new SqlParameter("@Id", Id));
sqlCmd.Connection = myconn;
reader = sqlCmd.ExecuteReader();
List <Client> lst = new List <Client>();
if (!reader.HasRows)
{
lst.Add(new Client());
lst[0].statusType = stypeService.GetStatusTypeDetailsAsync(0);
}
else
{
while (reader.Read())
{
lst.Add(new Client()
{
Id = Convert.ToInt32(reader.GetValue(0)),
clientName = reader.GetValue(1).ToString(),
companyId = Convert.ToInt32(reader.GetValue(2)),
companyName = reader.GetValue(3).ToString(),
email = reader.GetValue(4).ToString(),
address = reader.GetValue(5).ToString(),
phoneNumber = reader.GetValue(6).ToString(),
createdDate = Convert.ToDateTime(reader.GetValue(7)),
modifiedDate = reader.GetValue(8) == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader.GetValue(8)),
statusId = Convert.ToInt32(reader.GetValue(9)),
statusName = reader.GetValue(10).ToString(),
userId = Convert.ToInt32(reader.GetValue(11)),
});
}
lst[0].statusType = stypeService.GetStatusTypeDetailsAsync(0);
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <ProjectRates> GetProjectRatesDetailsAsync(int ID, int projID)
{
try
{
SqlDataReader reader = null;
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_ProjectRatesGet";
sqlCmd.Parameters.Add(new SqlParameter("@ID", ID));
sqlCmd.Parameters.Add(new SqlParameter("@projID", projID));
sqlCmd.Connection = myconn;
reader = sqlCmd.ExecuteReader();
List <ProjectRates> lst = new List <ProjectRates>();
while (reader.Read())
{
lst.Add(new ProjectRates()
{
Id = Convert.ToInt32(reader.GetValue(0)),
ProjId = Convert.ToInt32(reader.GetValue(1)),
SORTypeId = Convert.ToInt32(reader.GetValue(2)),
SORTypeName = reader.GetValue(3).ToString(),
subSORTypeId = Convert.ToInt32(reader.GetValue(4)),
subSORTypeName = reader.GetValue(5).ToString(),
SORCode = reader.GetValue(6).ToString(),
description = reader.GetValue(7).ToString(),
unitOfMeasure = reader.GetValue(8).ToString(),
unit = Convert.ToInt32(reader.GetValue(9)),
unitPrice = Convert.ToDecimal(reader.GetValue(10)),
cost = Convert.ToDecimal(reader.GetValue(11)),
statusId = Convert.ToInt32(reader.GetValue(12)),
statusName = reader.GetValue(13).ToString(),
expiryDate = Convert.ToDateTime(reader.GetValue(14)),
createdDate = Convert.ToDateTime(reader.GetValue(15)),
modifiedDate = reader.GetValue(16) == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader.GetValue(16)),
userId = Convert.ToInt32(reader.GetValue(17))
});
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <MenuRolesRelation> GetMenuRolesRelationDetailsAsync(int ID, int roleId)
{
try
{
SqlDataReader reader = null;
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_MenuRolesRelationGet";
sqlCmd.Parameters.Add(new SqlParameter("@ID", ID));
sqlCmd.Parameters.Add(new SqlParameter("@roleId", roleId));
sqlCmd.Connection = myconn;
reader = sqlCmd.ExecuteReader();
List <MenuRolesRelation> lst = new List <MenuRolesRelation>();
while (reader.Read())
{
lst.Add(new MenuRolesRelation()
{
Id = Convert.ToInt32(reader.GetValue(0)),
menuId = Convert.ToInt32(reader.GetValue(1)),
menuName = reader.GetValue(2).ToString(),
parentId = Convert.ToInt32(reader.GetValue(3)),
parentMenuName = reader.GetValue(4).ToString(),
roleId = Convert.ToInt32(reader.GetValue(5)),
roles = reader.GetValue(6).ToString(),
userRights = reader.GetValue(7).ToString(),
createdDate = Convert.ToDateTime(reader.GetValue(8)),
modifiedDate = reader.GetValue(9) == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader.GetValue(9)),
userId = Convert.ToInt32(reader.GetValue(10))
});
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <Roles> GetRolesDetailsAsync(int RolesID)
{
try
{
DataTable dtRoles = new DataTable();
SqlDataAdapter da;
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_RolesGet";
sqlCmd.Parameters.Add(new SqlParameter("@ID", RolesID));
sqlCmd.Connection = myconn;
da = new SqlDataAdapter(sqlCmd);
dtRoles.Clear();
da.Fill(dtRoles);
List <Roles> lst = new List <Roles>();
foreach (DataRow item in dtRoles.Rows)
{
lst.Add(new Roles()
{
Id = Convert.ToInt32(item[0]),
rolesName = item[1].ToString(),
createdDate = Convert.ToDateTime(item[2]),
modifiedDate = item[3] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(item[3]),
userId = Convert.ToInt32(item[4]),
menuRolesList = mrr.GetMenuRolesRelationDetailsAsync(0, Convert.ToInt32(item[0]))
});
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <ProjectSubContractors> GetProjectSubContractorsDetailsAsync(int ID, int projID)
{
try
{
SqlDataReader reader = null;
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_ProjectSubContractorsGet";
sqlCmd.Parameters.Add(new SqlParameter("@ID", ID));
sqlCmd.Parameters.Add(new SqlParameter("@projID", projID));
sqlCmd.Connection = myconn;
reader = sqlCmd.ExecuteReader();
List <ProjectSubContractors> lst = new List <ProjectSubContractors>();
while (reader.Read())
{
lst.Add(new ProjectSubContractors()
{
Id = Convert.ToInt32(reader.GetValue(0)),
projId = Convert.ToInt32(reader.GetValue(1)),
subcontractorId = Convert.ToInt32(reader.GetValue(2)),
subconName = reader.GetValue(3).ToString(),
statusId = Convert.ToInt32(reader.GetValue(4)),
statusName = reader.GetValue(5).ToString(),
createdDate = Convert.ToDateTime(reader.GetValue(6)),
modifiedDate = reader.GetValue(7) == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader.GetValue(7)),
userId = Convert.ToInt32(reader.GetValue(8))
});
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <Location> GetLocationDetailsAsync(int ID)
{
try
{
SqlDataReader reader = null;
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_LocationGet";
sqlCmd.Parameters.Add(new SqlParameter("@ID", ID));
sqlCmd.Connection = myconn;
reader = sqlCmd.ExecuteReader();
List <Location> lst = new List <Location>();
while (reader.Read())
{
lst.Add(new Location()
{
ID = Convert.ToInt32(reader.GetValue(0)),
name = reader.GetValue(1).ToString(),
streetNo = reader.GetValue(2).ToString(),
street = reader.GetValue(3).ToString(),
suburb = reader.GetValue(4).ToString(),
state = reader.GetValue(5).ToString(),
pincode = reader.GetValue(6).ToString(),
createdDate = Convert.ToDateTime(reader.GetValue(7)),
modifiedDate = reader.GetValue(8) == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader.GetValue(8)),
userId = Convert.ToInt32(reader.GetValue(9))
});
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <Company> GetCompanyDetailsAsync(int companyID)
{
try
{
SqlDataReader reader = null;
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_CompanyGet";
sqlCmd.Parameters.Add(new SqlParameter("@companyID", companyID));
sqlCmd.Connection = myconn;
reader = sqlCmd.ExecuteReader();
List <Company> lst = new List <Company>();
while (reader.Read())
{
lst.Add(new Company
{
companyID = Convert.ToInt32(reader.GetValue(0)),
companyName = reader.GetValue(1).ToString(),
email = reader.GetValue(2).ToString(),
phoneNumber = reader.GetValue(3).ToString(),
createdDate = Convert.ToDateTime(reader.GetValue(4)),
modifiedDate = reader.GetValue(5) == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader.GetValue(5)),
statusID = Convert.ToInt32(reader.GetValue(6))
});
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <LoginDetails> GetLoginDetailsAsync(int ID)
{
try
{
SqlDataReader reader = null;
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_LoginGet";
sqlCmd.Parameters.Add(new SqlParameter("@ID", ID));
sqlCmd.Connection = myconn;
reader = sqlCmd.ExecuteReader();
List <LoginDetails> lst = new List <LoginDetails>();
while (reader.Read())
{
lst.Add(new LoginDetails
{
ID = Convert.ToInt32(reader.GetValue(0)),
userId = Convert.ToInt32(reader.GetValue(1).ToString()),
loginDate = Convert.ToDateTime(reader.GetValue(2)),
logoutDate = reader.GetValue(3) == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader.GetValue(3))
});
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <Roles> GetRoles(int Id)
{
try
{
SqlDataReader reader = null;
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_RolesGet";
sqlCmd.Parameters.Add(new SqlParameter("@Id", Id));
sqlCmd.Connection = myconn;
reader = sqlCmd.ExecuteReader();
List <Roles> lst = new List <Roles>();
while (reader.Read())
{
lst.Add(new Roles()
{
Id = Convert.ToInt32(reader.GetValue(0)),
rolesName = reader.GetValue(1).ToString()
});
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <SubContractor> GetSubContractorDetailsAsync(int Id, char mode)
{
try
{
DataTable dt = new DataTable();
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_SubContractorGet";
sqlCmd.Parameters.Add(new SqlParameter("@Id", Id));
sqlCmd.Connection = myconn;
SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
dt.Clear();
da.Fill(dt);
List <SubContractor> lst = new List <SubContractor>();
if (mode == 'I')
{
if (dt.Rows.Count == 0)
{
lst.Add(new SubContractor());
lst[0].skillSelect = GetSubContractorSkillSetsDetailsAsync(0);
}
else
{
foreach (DataRow item in dt.Rows)
{
lst.Add(new SubContractor
{
Id = Convert.ToInt32(item[0]),
subconName = item[1].ToString(),
email = item[2].ToString(),
address = item[3].ToString(),
phoneNumber = item[4].ToString(),
statusId = Convert.ToInt32(item[5]),
statusName = item[6].ToString(),
createdDate = Convert.ToDateTime(item[7]),
modifiedDate = item[8] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(item[8]),
userId = Convert.ToInt32(item[9])
});
}
lst[0].skillSelect = GetSubContractorSkillSetsDetailsAsync(0);
}
}
else
{
foreach (DataRow item in dt.Rows)
{
lst.Add(new SubContractor
{
Id = Convert.ToInt32(item[0]),
subconName = item[1].ToString(),
email = item[2].ToString(),
address = item[3].ToString(),
phoneNumber = item[4].ToString(),
statusId = Convert.ToInt32(item[5]),
statusName = item[6].ToString(),
createdDate = Convert.ToDateTime(item[7]),
modifiedDate = item[8] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(item[8]),
userId = Convert.ToInt32(item[9])
});
}
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <Project> GetProjectDetailsAsync(int ID, int clientID)
{
try
{
DataTable dtProject = new DataTable();
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_ProjectGet";
sqlCmd.Parameters.Add(new SqlParameter("@ID", ID));
sqlCmd.Parameters.Add(new SqlParameter("@clientID", clientID));
sqlCmd.Connection = myconn;
SqlDataAdapter daProject = new SqlDataAdapter(sqlCmd);
dtProject.Clear();
daProject.Fill(dtProject);
List <Project> lst = new List <Project>();
if (dtProject.Rows.Count == 0)
{
lst.Add(new Project());
lst[0].members = pm.GetProjectMembersDetailsAsync(0, 0);
lst[0].subContractors = ps.GetProjectSubContractorsDetailsAsync(0, 0);
lst[0].location = location.GetLocationDetailsAsync(0);
lst[0].client = client.GetClientDetailsAsync(0);
lst[0].sortype = sortype.GetSORTypeDetailsAsync(0);
lst[0].subsortype = subsortype.GetSubSORTypeDetailsAsync(0);
lst[0].team = team.GetTeamDetailsAsync(0, 'I');
lst[0].subcontractor = subcontractor.GetSubContractorDetailsAsync(0, 'I');
lst[0].statusType = statusType.GetStatusTypeDetailsAsync(0);
}
else
{
foreach (DataRow item in dtProject.Rows)
{
lst.Add(new Project()
{
Id = Convert.ToInt32(item[0]),
projName = item[1].ToString(),
locId = Convert.ToInt32(item[2]),
locName = item[3].ToString(),
clientId = Convert.ToInt32(item[4]),
clientName = item[5].ToString(),
startDate = Convert.ToDateTime(item[6]),
endDate = Convert.ToDateTime(item[7]),
statusId = Convert.ToInt32(item[8]),
statusName = item[9].ToString(),
createdDate = Convert.ToDateTime(item[10]),
modifiedDate = item[11] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(item[11]),
userId = Convert.ToInt32(item[12]),
projRates = pr.GetProjectRatesDetailsAsync(0, Convert.ToInt32(item[0])),
projMembers = pm.GetProjectMembersDetailsAsync(0, Convert.ToInt32(item[0])),
projSubContractors = ps.GetProjectSubContractorsDetailsAsync(0, Convert.ToInt32(item[0])),
projJobTypes = pj.GetProjectJobTypesDetailsAsync(0, Convert.ToInt32(item[0]))
});
}
lst[0].members = pm.GetProjectMembersDetailsAsync(0, 0);
lst[0].subContractors = ps.GetProjectSubContractorsDetailsAsync(0, 0);
lst[0].location = location.GetLocationDetailsAsync(0);
lst[0].client = client.GetClientDetailsAsync(0);
lst[0].sortype = sortype.GetSORTypeDetailsAsync(0);
lst[0].subsortype = subsortype.GetSubSORTypeDetailsAsync(0);
lst[0].team = team.GetTeamDetailsAsync(0, 'I');
lst[0].subcontractor = subcontractor.GetSubContractorDetailsAsync(0, 'I');
lst[0].statusType = statusType.GetStatusTypeDetailsAsync(0);
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <Members> GetMembersDetailsAsync(int membersID, char mode)
{
try
{
DataTable dt = new DataTable();
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_MembersGet";
sqlCmd.Parameters.Add(new SqlParameter("@Id", membersID));
sqlCmd.Connection = myconn;
SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
dt.Clear();
da.Fill(dt);
List <Members> lst = new List <Members>();
if (mode == 'I')
{
if (dt.Rows.Count == 0)
{
lst.Add(new Members());
lst[0].skillSelect = GetMembersSkillSetsDetailsAsync(0);
}
else
{
foreach (DataRow item in dt.Rows)
{
lst.Add(new Members
{
Id = Convert.ToInt32(item[0]),
firstName = item[1].ToString(),
lastName = item[2].ToString(),
email = item[3].ToString(),
phoneNumber = item[4].ToString(),
address = item[5].ToString(),
isTeamLead = Convert.ToBoolean(item[6]),
lastLoginDate = item[7] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(item[7]),
statusId = Convert.ToInt32(item[8]),
statusName = item[9].ToString(),
createdDate = Convert.ToDateTime(item[10]),
modifiedDate = item[11] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(item[11]),
userId = Convert.ToInt32(item[12])
});
}
lst[0].skillSelect = GetMembersSkillSetsDetailsAsync(0);
}
}
else
{
foreach (DataRow item in dt.Rows)
{
lst.Add(new Members
{
Id = Convert.ToInt32(item[0]),
firstName = item[1].ToString(),
lastName = item[2].ToString(),
email = item[3].ToString(),
phoneNumber = item[4].ToString(),
address = item[5].ToString(),
isTeamLead = Convert.ToBoolean(item[6]),
lastLoginDate = item[7] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(item[7]),
statusId = Convert.ToInt32(item[8]),
statusName = item[9].ToString(),
createdDate = Convert.ToDateTime(item[10]),
modifiedDate = item[11] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(item[11]),
userId = Convert.ToInt32(item[12]),
skillSelect = GetMembersSkillSetsDetailsAsync(Convert.ToInt32(item[0]))
});
}
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}
public List <Team> GetTeamDetailsAsync(int Id, char mode)
{
try
{
DataTable dt = new DataTable();
if (myconn.State != ConnectionState.Open)
{
myconn.Open();
}
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SP_TeamGet";
sqlCmd.Parameters.Add(new SqlParameter("@Id", Id));
sqlCmd.Connection = myconn;
SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
dt.Clear();
da.Fill(dt);
List <Team> lst = new List <Team>();
if (mode == 'I')
{
if (dt.Rows.Count == 0)
{
lst.Add(new Team());
lst[0].memberMapping = GetTeamMemberMappingDetailsAsync(0);
}
else
{
foreach (DataRow item in dt.Rows)
{
lst.Add(new Team
{
Id = Convert.ToInt32(item[0]),
teamName = item[1].ToString(),
statusId = Convert.ToInt32(item[2]),
statusName = item[3].ToString(),
createdDate = Convert.ToDateTime(item[4]),
modifiedDate = item[5] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(item[5]),
userId = Convert.ToInt32(item[6])
});
}
lst[0].memberMapping = GetTeamMemberMappingDetailsAsync(0);
}
}
else
{
foreach (DataRow item in dt.Rows)
{
lst.Add(new Team
{
Id = Convert.ToInt32(item[0]),
teamName = item[1].ToString(),
statusId = Convert.ToInt32(item[2]),
statusName = item[3].ToString(),
createdDate = Convert.ToDateTime(item[4]),
modifiedDate = item[5] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(item[5]),
userId = Convert.ToInt32(item[6]),
memberMapping = GetTeamMemberMappingDetailsAsync(Convert.ToInt32(item[0]))
});
}
}
if (myconn.State != ConnectionState.Closed)
{
myconn.Close();
}
return(lst);
}
catch (Exception ex)
{
gs.LogData(ex);
throw ex;
}
}