public ICollection <ViewAssignedUserRole> GetAssignedUserRolesByUserId(int userId)
{
try
{
CommandObj.CommandText = "UDSP_GetAssignedUserRolesByUserId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@UserId", userId);
ConnectionObj.Open();
List <ViewAssignedUserRole> roles = new List <ViewAssignedUserRole>();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
roles.Add(new ViewAssignedUserRole
{
ActiveStatus = Convert.ToInt32(reader["IsActive"]),
RoleId = Convert.ToInt32(reader["RoleId"]),
AssignedId = Convert.ToInt64(reader["AssignedRoleToUserId"]),
BranchId = Convert.ToInt32(reader["BranchId"]),
UserId = Convert.ToInt32(reader["UserId"]),
RoleName = reader["RoleName"].ToString(),
Alias = DBNull.Value.Equals(reader["Alias"])?null: reader["Alias"].ToString()
});
}
reader.Close();
return(roles);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not collecd assigned role by username", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <InvoiceDetails> GetInvoicedOrderDetailsByInvoiceRef(string invoiceRef)
{
try
{
CommandObj.CommandText = "spGetInvoicedOrderDetailsByInvoiceRef";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@InvoiceRef", invoiceRef);
List <InvoiceDetails> invoiceDetails = new List <InvoiceDetails>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
InvoiceDetails invoice = new InvoiceDetails
{
InvoiceRef = reader["InvoiceRef"].ToString(),
ProductId = Convert.ToInt32(reader["ProductId"]),
ProductName = reader["ProductName"].ToString(),
Quantity = Convert.ToInt32(reader["Quantity"]),
ProductCategoryName = reader["ProductCategoryName"].ToString(),
UnitPrice = Convert.ToDecimal(reader["UnitPrice"]),
InvoiceId = Convert.ToInt32(reader["InvoiceId"])
};
invoiceDetails.Add(invoice);
}
reader.Close();
return(invoiceDetails);
}
catch (Exception exception)
{
throw new Exception("Could not get Invoiced orders by invoice ref", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <ViewReferenceAccountModel> GetAllSubReferenceAccounts()
{
try
{
CommandObj.CommandText = "UDSP_GetAllSubReferenceAccountList";
CommandObj.CommandType = CommandType.StoredProcedure;
List <ViewReferenceAccountModel> accounts = new List <ViewReferenceAccountModel>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
accounts.Add(new ViewReferenceAccountModel
{
Id = Convert.ToInt32(reader["SubReferenceAccountId"]),
Code = reader["Code"].ToString().Trim(),
ReferenceAccountCode = reader["ReferenceAccountCode"].ToString(),
Name = reader["Name"].ToString(),
SysDateTime = Convert.ToDateTime(reader["SysDateTime"])
});
}
reader.Close();
return(accounts);
}
catch (SqlException sqlException)
{
throw new Exception("Could not collect sub reference accounts due to sql exception", sqlException.InnerException);
}
catch (Exception exception)
{
throw new Exception("Could not collect sub reference accounts", exception.InnerException);
}
finally
{
CommandObj.Dispose();
CommandObj.Parameters.Clear();
ConnectionObj.Close();
}
}
public IEnumerable <Course> GetCoursesTakeByaStudentByStudentId(int id)
{
try
{
List <Course> courses = new List <Course>();
CommandObj.CommandText = "spGetCoursesTakenByaStudent";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@StudentId", id);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
Course aCourse = new Course
{
Id = Convert.ToInt32(reader["Id"].ToString()),
Name = reader["Name"].ToString(),
Code = reader["Code"].ToString(),
Credit = Convert.ToDouble(reader["Credit"].ToString()),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
Description = reader["Descirption"].ToString(),
SemesterId = Convert.ToInt32(reader["SemesterId"].ToString())
};
courses.Add(aCourse);
}
reader.Close();
return(courses);
}
catch (Exception exception)
{
throw new Exception("Unable to collect Courses", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public List <PrintBarCodeModel> GetTodaysProductionProductList(DateTime date)
{
try
{
CommandObj.CommandText = "UDSP_GetBarCodeListByDate";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@Date", date);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <PrintBarCodeModel> models = new List <PrintBarCodeModel>();
while (reader.Read())
{
models.Add(new PrintBarCodeModel
{
BarCodeMasterId = Convert.ToInt64(reader["BarCodeMasterId"]),
ProductId = Convert.ToInt32(reader["ProductId"]),
ProductName = reader["ProductName"].ToString(),
SubSubSubAccountCode = reader["SubSubSubAccountCode"].ToString(),
CategoryId = Convert.ToInt32(reader["CategoryId"]),
CompanyId = Convert.ToInt32(reader["CompanyId"]),
ProductionLineNumber = reader["LineNumber"].ToString(),
});
}
reader.Close();
return(models);
}
catch (Exception exception)
{
throw new Exception("Colud not collect product list", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <ViewClient> GetTopClients()
{
try
{
CommandObj.CommandText = "UDSP_RptGetTopClients";
CommandObj.CommandType = CommandType.StoredProcedure;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <ViewClient> clients = new List <ViewClient>();
while (reader.Read())
{
clients.Add(new ViewClient
{
ClientId = Convert.ToInt32(reader["ClientId"]),
ClientName = reader["ClientName"].ToString(),
CommercialName = reader["CommercialName"].ToString(),
SubSubSubAccountCode = reader["SubSubSubAccountCode"].ToString(),
TotalDebitAmount = Convert.ToDecimal(reader["TotalDebitAmount"])
});
}
reader.Close();
return(clients);
}
catch (SqlException sqlException)
{
throw new Exception("Could not collect top clients due to Sql Exception", sqlException);
}
catch (Exception exception)
{
throw new Exception("Could not collect top clients", exception);
}
finally
{
CommandObj.Dispose();
CommandObj.Parameters.Clear();
ConnectionObj.Close();
}
}
public IEnumerable <BankBranch> GetAllBankBranch()
{
try
{
CommandObj.CommandText = "spGetAllBankBranch";
CommandObj.CommandType = CommandType.StoredProcedure;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <BankBranch> bankBranchList = new List <BankBranch>();
while (reader.Read())
{
bankBranchList.Add(new BankBranch
{
BankBranchId = Convert.ToInt32(reader["BankBranchId"]),
BankBranchName = reader["BankBranchName"].ToString(),
BankBranchAccountCode = reader["BankBranchAccountCode"].ToString(),
BankId = Convert.ToInt32(reader["BankId"]),
Bank = new Bank {
BankId = Convert.ToInt32(reader["BankId"]),
BankName = reader["BankName"].ToString(),
BankAccountCode = reader["BankAccountCode"].ToString()
}
});
}
reader.Close();
return(bankBranchList);
}
catch (Exception e)
{
throw new Exception("Could not Collect Bank Branch List", e);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public Teacher GetTeacherByEmailAddress(string email)
{
try
{
string query = "SELECT * FROM t_Teacher WHERE Email=@email";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@email", email);
Teacher teacher = null;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
teacher = new Teacher
{
Id = Convert.ToInt32(reader["Id"].ToString()),
Name = reader["Name"].ToString(),
Address = reader["Address"].ToString(),
Email = reader["Email"].ToString(),
Contact = reader["Contact"].ToString(),
DesignationId = Convert.ToInt32(reader["DesignationId"].ToString()),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
CreditTobeTaken = Convert.ToDouble(reader["CreditToBeTaken"].ToString())
};
}
reader.Close();
return(teacher);
}
catch (Exception exception)
{
throw new Exception("Unable to collect Teachers", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public int Insert(Country country)
{
try
{
string query = "INSERT INTO t_Country VALUES(@name,@about)";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@name", country.Name);
CommandObj.Parameters.AddWithValue("@about", country.About);
ConnectionObj.Open();
int rowAffected = CommandObj.ExecuteNonQuery();
return(rowAffected);
}
catch (Exception exception)
{
throw new Exception("Could not save.", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public string SaveStudent(List <Student> students)
{
ConnectionObj.Open();
foreach (Student astudent in students)
{
string insert = String.Format("INSERT INTO tblStudentInfo VALUES(@StudentName,@RollNo,@RegNo,@Age,@Id)");
CommandObj.Parameters.Clear();
CommandObj.CommandText = insert;
CommandObj.Parameters.Add("@StudentName", SqlDbType.VarChar);
CommandObj.Parameters["@StudentName"].Value = astudent.StudentName;
CommandObj.Parameters.Add("@RollNo", SqlDbType.Int);
CommandObj.Parameters["@RollNo"].Value = astudent.RollNo;
CommandObj.Parameters.Add("@RegNo", SqlDbType.Int);
CommandObj.Parameters["@RegNo"].Value = astudent.RegNo;
CommandObj.Parameters.Add("@Age", SqlDbType.Int);
CommandObj.Parameters["@Age"].Value = astudent.Age;
CommandObj.Parameters.Add("@Id", SqlDbType.Int);
CommandObj.Parameters["@Id"].Value = astudent.DDeptName.DeptId;
CommandObj.ExecuteNonQuery();
}
ConnectionObj.Close();
return("Student Information Save Successfully");
}
public List <ClassRoom> GetClassSchedulByStartAndEndingTime(int roomId, int dayId, DateTime startTime, DateTime endTime)
{
try
{
//CommandObj.CommandText = "Select * from t_AllocateClassRoom where StartTime BETWEEN CAST('" + startTime +"' As Time) AND CAST('" + endTime + "' As Time) AND RoomId ='" + roomId +
// "' AND DayId='" + dayId + "'";
CommandObj.CommandText = "Select * from t_AllocateClassRoom Where DayId=" + dayId + " AND RoomId=" + roomId + " AND AllocationStatus=" + 1;
List <ClassRoom> tempClassSchedules = new List <ClassRoom>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
ClassRoom temp = new ClassRoom
{
Id = Convert.ToInt32(reader["Id"].ToString()),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
CourseId = Convert.ToInt32(reader["CourseId"].ToString()),
RoomId = Convert.ToInt32(reader["RoomId"].ToString()),
DayId = Convert.ToInt32(reader["DayId"].ToString()),
StartTime = Convert.ToDateTime(reader["StartTime"].ToString()),
Endtime = Convert.ToDateTime(reader["EndTime"].ToString())
};
tempClassSchedules.Add(temp);
}
reader.Close();
return(tempClassSchedules);
}
catch (Exception exception)
{
throw new Exception("Unable to collect class schedule", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public List <Country> GetAll(int pageIndex, int pageSise, out int totalRecords)
{
//string cs = WebConfigurationManager.ConnectionStrings["countryInformaitonDb"].ConnectionString;
List <Country> countries = new List <Country>();
//using (SqlConnection connection = new SqlConnection(cs))
//{
SqlCommand command = new SqlCommand("spGetCityInformaiton", ConnectionObj);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@PageIndex", pageIndex);
command.Parameters.AddWithValue("@PageSize", pageSise);
SqlParameter totalrecord = new SqlParameter();
totalrecord.ParameterName = "@TotalRecord";
totalrecord.Direction = ParameterDirection.Output;
totalrecord.SqlDbType = SqlDbType.Int;
command.Parameters.Add(totalrecord);
ConnectionObj.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Country aCountry = new Country();
aCountry.Id = Convert.ToInt32(reader["Id"].ToString());
aCountry.Name = reader["Name"].ToString();
aCountry.About = reader["About"].ToString();
countries.Add(aCountry);
}
reader.Close();
totalRecords = (int)command.Parameters["@TotalRecord"].Value;
// }
ConnectionObj.Close();
return(countries);
}
public List <EducationalInfo> GetEducationalInfoByEmpId(int employeeId)
{
try
{
CommandObj.CommandText = "UDSP_GetEducationalInfoByEmpId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@EmployeeId", employeeId);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <EducationalInfo> educationalInfos = new List <EducationalInfo>();
while (reader.Read())
{
educationalInfos.Add(new EducationalInfo
{
QualificationName = reader["QualificationName"].ToString(),
BoardName = reader["BoardName"].ToString(),
InstituteName = reader["InstituteName"].ToString(),
Result = reader["Result"].ToString(),
GroupSubject = reader["GroupSubject"].ToString(),
PassingYear = Convert.ToInt32(reader["PassingYear"])
});
}
reader.Close();
return(educationalInfos);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not collect Employee educational Information by employee id", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <Territory> GetTerritoryListByRegionId(int regionId)
{
try
{
CommandObj.CommandText = "UDSP_GetTerritoryListByRegionId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@RegionId", regionId);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <Territory> territories = new List <Territory>();
while (reader.Read())
{
territories.Add(new Territory
{
TerritoryId = Convert.ToInt32(reader["TerritoryId"]),
TerritoryName = reader["TerritoryName"].ToString(),
Region = new Region
{
RegionId = Convert.ToInt32(reader["RegionId"]),
RegionName = reader["RegionName"].ToString()
}
});
}
reader.Close();
return(territories);
}
catch (Exception e)
{
throw new Exception("Could not Collect territories by branch id", e);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public ICollection <BarCodeModel> GetBarCodesBySearchCriteria(PrintBarCodeModel model)
{
try
{
CommandObj.CommandText = "UDSP_GetBarCodesBySearchCriteria";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@BarCodeMasterId", model.BarCodeMasterId);
CommandObj.Parameters.AddWithValue("@LineNumber", model.ProductionLineNumber);
CommandObj.Parameters.AddWithValue("@From", model.From);
CommandObj.Parameters.AddWithValue("@To", model.To);
List <BarCodeModel> barcodes = new List <BarCodeModel>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
barcodes.Add(new BarCodeModel
{
Barcode = reader["BarCode"].ToString(),
BarCodeModelId = Convert.ToInt64(reader["BarcodeId"]),
ProductName = reader["ProductName"].ToString()
});
}
reader.Close();
return(barcodes);
}
catch (Exception exception)
{
throw new Exception("Could not collect barcodes", exception.InnerException);
}
finally
{
CommandObj.Dispose();
CommandObj.Parameters.Clear();
ConnectionObj.Close();
}
}
public List <Region> GetAssignedRegionListToBranchByBranchId(int branchId)
{
try
{
CommandObj.CommandText = "spGetAssignedRegionListToBranchByBranchId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@BranchId", branchId);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <Region> regions = new List <Region>();
while (reader.Read())
{
regions.Add(new Region
{
RegionId = Convert.ToInt32(reader["RegionId"]),
RegionName = reader["RegionName"].ToString(),
Division = new Division
{
DivisionId = Convert.ToInt32(reader["DivisionId"]),
DivisionName = reader["DivisionName"].ToString()
}
});
}
reader.Close();
return(regions);
}
catch (Exception e)
{
throw new Exception("Could not Collect regions by Branch Id", e);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <Branch> GetAssignedBranchesToUserByUserId(int userId)
{
try
{
CommandObj.CommandText = "spGetAssignedBranchToUserByUserId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@UserId", userId);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <Branch> branches = new List <Branch>();
while (reader.Read())
{
branches.Add(new Branch
{
BranchId = Convert.ToInt32(reader["BranchId"]),
BranchName = reader["BranchName"].ToString(),
BranchEmail = reader["Email"].ToString(),
BranchAddress = reader["BranchAddress"].ToString(),
BranchPhone = reader["Phone"].ToString(),
BranchOpenigDate = Convert.ToDateTime(reader["BranchOpenigDate"]),
SubSubSubAccountCode = reader["SubSubSubAccountCode"].ToString()
});
}
reader.Close();
return(branches);
}
catch (Exception e)
{
throw new Exception("Could not Collect assigned Branch info by user id", e);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public List <Designation> GetAllDesignationByDepartmentId(int departmentId)
{
try
{
CommandObj.CommandText = "UDSP_GetAllDesignationByDepartmentId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@DepartmentId", departmentId);
List <Designation> designations = new List <Designation>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
designations.Add(new Designation
{
DesignationId = Convert.ToInt32(reader["DesignationId"]),
DesignationName = reader["DesignationName"].ToString(),
DesignationCode = reader["DesignationCode"].ToString()
});
}
reader.Close();
return(designations);
}
catch (SqlException sqlException)
{
throw new Exception("Could not collect designation by department id due to Sql Exception ", sqlException);
}
catch (Exception exception)
{
throw new Exception("Could not collect designation by department id ", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public Course GetCourseByName(string name)
{
try
{
string query = "SELECT * FROM t_Course WHERE Name=@name";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@name", name);
ConnectionObj.Open();
Course course = null;
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
course = new Course
{
Id = Convert.ToInt32(reader["Id"].ToString()),
Name = reader["Name"].ToString(),
Code = reader["Code"].ToString(),
Credit = Convert.ToDouble(reader["Credit"].ToString()),
Description = reader["Descirption"].ToString(),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
SemesterId = Convert.ToInt32(reader["SemesterId"].ToString())
};
}
reader.Close();
return(course);
}
catch (Exception exception)
{
throw new Exception("Unable to collect course", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public IEnumerable <BarCodeModel> GetBarcodeReportBySearchCriteria(SearchCriteria searchCriteria)
{
try
{
CommandObj.CommandText = "UDSP_RptGetBarcodeReportBySearchCriteria";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@FromDate", searchCriteria.StartDate);
CommandObj.Parameters.AddWithValue("@ToDate", searchCriteria.EndDate);
CommandObj.Parameters.AddWithValue("@ProductId", searchCriteria.ProductId);
List <BarCodeModel> barcodeList = new List <BarCodeModel>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
barcodeList.Add(new BarCodeModel
{
BatchCode = reader["BatchCode"].ToString(),
LineNumber = reader["LineNumber"].ToString(),
Barcode = reader["Barcode"].ToString(),
ProductName = reader["ProductName"].ToString(),
ProductionDateTime = Convert.ToDateTime(reader["ProductionDate"])
});
}
reader.Close();
return(barcodeList);
}
catch (Exception exception)
{
throw new Exception("Could not collect barcodes", exception.InnerException);
}
finally
{
CommandObj.Dispose();
CommandObj.Parameters.Clear();
ConnectionObj.Close();
}
}
public IEnumerable <ViewProduct> GetPopularBatteriesByBranchIdCompanyIdAndYear(int branchId, int companyId, int year)
{
try
{
CommandObj.CommandText = "UDSP_RptGetPopularsBatteriesByBranchIdCompanyIdAndYear";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@BranchId", branchId);
CommandObj.Parameters.AddWithValue("@CompanyId", companyId);
CommandObj.Parameters.AddWithValue("@Year", year);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <ViewProduct> batteries = new List <ViewProduct>();
while (reader.Read())
{
batteries.Add(new ViewProduct
{
ProductId = Convert.ToInt32(reader["ProductId"]),
ProductName = reader["ProductName"].ToString(),
SubSubSubAccountCode = reader["SubSubSubAccountCode"].ToString(),
TotalSoldQty = Convert.ToInt32(reader["TotalSoldQty"]),
ProductCategoryName = reader["ProductCategoryName"].ToString()
});
}
reader.Close();
return(batteries);
}
catch (Exception exception)
{
throw new Exception("Could not collect popular batteries by branch and Company Id and year", exception);
}
finally
{
CommandObj.Dispose();
CommandObj.Parameters.Clear();
ConnectionObj.Close();
}
}
public IEnumerable <Course> GetCourseByDepartmentId(int departmentId)
{
try
{
string query = "SELECT * FROM t_Course WHERE DepartmentId='" + departmentId + "'";
CommandObj.CommandText = query;
List <Course> courses = new List <Course>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
Course course = new Course
{
Id = Convert.ToInt32(reader["Id"].ToString()),
Name = reader["Name"].ToString(),
Code = reader["Code"].ToString(),
Credit = Convert.ToDouble(reader["Credit"].ToString()),
Description = reader["Descirption"].ToString(),
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
SemesterId = Convert.ToInt32(reader["SemesterId"].ToString())
};
courses.Add(course);
}
reader.Close();
return(courses);
}
catch (Exception exception)
{
throw new Exception("Unable to collect courses", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public ICollection <ViewProduct> GetDeliveredProductsByInvoiceRef(string invoiceRef)
{
try
{
List <ViewProduct> products = new List <ViewProduct>();
CommandObj.CommandText = "UDSP_GetDeliveredProductsByInvoiceRef";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@InvoiceRef", invoiceRef);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
products.Add(new ViewProduct
{
ProductName = reader["ProductName"].ToString(),
ProductId = Convert.ToInt32(reader["ProductId"]),
SubSubSubAccountCode = reader["SubSubSubAccountCode"].ToString(),
Quantity = Convert.ToInt32(reader["Quantity"])
});
}
reader.Close();
return(products);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not get Delivered Qty by invoice ref", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public List <CityViewerModel> GetCityInformationByCountryName(string countryName)
{
try
{
string query = "SELECT * FROM GetCountryInformation WHERE Country_Name LIKE '%" + countryName + "%'";
CommandObj.CommandText = query;
ConnectionObj.Open();
List <CityViewerModel> listOfCityViewerModels = new List <CityViewerModel>();
CityViewerModel cityViewerModel = null;
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
cityViewerModel = new CityViewerModel();
cityViewerModel.Id = Convert.ToInt32(reader["Id"].ToString());
cityViewerModel.CityName = reader["City_Name"].ToString();
cityViewerModel.AboutCity = reader["About_City"].ToString();
cityViewerModel.NoOfDewellers = Convert.ToInt64(reader["No_of_Dwellers"].ToString());
cityViewerModel.Location = reader["Location"].ToString();
cityViewerModel.Weather = reader["Weather"].ToString();
cityViewerModel.CountryName = reader["Country_Name"].ToString();
cityViewerModel.AboutCountry = reader["About_Country"].ToString();
listOfCityViewerModels.Add(cityViewerModel);
}
reader.Close();
ConnectionObj.Close();
return(listOfCityViewerModels);
}
catch (Exception exception)
{
throw new Exception("Unable to Get city information", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
private List <Region> GetRegionListByDivisionId(int divisionId)
{
try
{
List <Region> regions = new List <Region>();
CommandObj.CommandText = "spGetRegionListByDivisionId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@DivisionId", divisionId);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
regions.Add(new Region
{
RegionId = Convert.ToInt32(reader["RegionId"]),
RegionName = reader["RegionName"].ToString(),
DivisionId = divisionId
});
}
reader.Close();
foreach (Region region in regions)
{
region.Territories = _territoryGateway.GetTerritoryListByRegionId(region.RegionId).ToList();
}
return(regions);
}
catch (Exception e)
{
throw new Exception("Unable to collect regions by Division Id", e);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public int AssignRegionToBranch(Branch branch, ViewUser user)
{
ConnectionObj.Open();
SqlTransaction sqlTransaction = ConnectionObj.BeginTransaction();
try
{
int rowAffected = 0;
foreach (var item in branch.RegionList)
{
CommandObj.Transaction = sqlTransaction;
CommandObj.CommandText = "spAssignNewRegionToBranch";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@BranchId", branch.BranchId);
CommandObj.Parameters.AddWithValue("@RegionId", item.RegionId);
CommandObj.Parameters.AddWithValue("@UserId", user.UserId);
CommandObj.Parameters.Add("@RowAffected", SqlDbType.Int);
CommandObj.Parameters["@RowAffected"].Direction = ParameterDirection.Output;
CommandObj.ExecuteNonQuery();
rowAffected += Convert.ToInt32(CommandObj.Parameters["@RowAffected"].Value);
CommandObj.Parameters.Clear();
}
sqlTransaction.Commit();
return(rowAffected);
}
catch (Exception e)
{
sqlTransaction.Rollback();
throw new Exception("Could not assign division or dristict to regions", e);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public int Insert(StudentResult studentResult)
{
try
{
CommandObj.CommandText = "INSERT INTO t_StudentResult VALUES(@stId,@courseId,@grade,@isStudentActive)";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@stId", studentResult.StudentId);
CommandObj.Parameters.AddWithValue("@courseId", studentResult.CourseId);
CommandObj.Parameters.AddWithValue("@grade", studentResult.Grade);
CommandObj.Parameters.AddWithValue("@isStudentActive", 1);
ConnectionObj.Open();
int rowAffected = CommandObj.ExecuteNonQuery();
return(rowAffected);
}
catch (Exception exception)
{
throw new Exception("Could not save", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public City GetCityInformationByName(string cityName)
{
try
{
string query = "SELECT * FROM t_City WHERE Name=@name";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@name", cityName);
ConnectionObj.Open();
City city = null;
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
city = new City();
city.Id = Convert.ToInt32(reader["Id"].ToString());
city.Name = reader["Name"].ToString();
city.About = reader["About"].ToString();
city.NoOfDewellers = Convert.ToInt64(reader["No_Of_Dwellers"].ToString());
city.Location = reader["Location"].ToString();
city.Weather = reader["Weather"].ToString();
city.CountryId = Convert.ToInt32(reader["CountryId"].ToString());
}
reader.Close();
ConnectionObj.Close();
return(city);
}
catch (Exception exception)
{
throw new Exception("Unable to Get city information", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
public int Insert(EnrollStudentInCourse enrollStudent)
{
try
{
CommandObj.CommandText = "INSERT INTO t_StudentEnrollInCourse VALUES(@stId,@courseId,@enrollDate,@status)";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@stId", enrollStudent.StudentId);
CommandObj.Parameters.AddWithValue("@courseId", enrollStudent.CourseId);
CommandObj.Parameters.AddWithValue("@enrollDate", enrollStudent.EnrollDate.ToShortDateString());
CommandObj.Parameters.AddWithValue("@status", 1);
ConnectionObj.Open();
int rowAffected = CommandObj.ExecuteNonQuery();
return(rowAffected);
}
catch (Exception exception)
{
throw new Exception("Could not save", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public List <City> GetAll()
{
string query = "SELECT * FROM t_City";
CommandObj.CommandText = query;
List <City> cities = new List <City>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
City city = new City();
city.Name = reader["Name"].ToString();
city.About = reader["About"].ToString();
city.Location = reader["Location"].ToString();
city.Weather = reader["Weather"].ToString();
city.NoOfDewellers = Convert.ToInt64(reader["No_Of_Dwellers"].ToString());
cities.Add(city);
}
reader.Close();
ConnectionObj.Close();
return(cities);
}