public ICollection <ReplaceReport> GetReplaceListByDateAndDistributionPoint(DateTime date, int distributionPoint)
{
try
{
CommandObj.CommandText = "UDSP_RptGetReplaceListByDateAndDistributionPoint";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@DistributionPoint", distributionPoint);
CommandObj.Parameters.AddWithValue("@Date", date);
List <ReplaceReport> models = new List <ReplaceReport>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
models.Add(new ReplaceReport
{
ClientName = reader["ClientName"].ToString(),
TransactionRef = reader["TransactionRef"].ToString(),
ProductName = reader["ProductName"].ToString(),
DeliveryDateTime = Convert.ToDateTime(reader["DeliveryDate"]),
ReplaceForBarcode = reader["ReplaceFor"].ToString(),
EntryDateTime = Convert.ToDateTime(reader["ReceiveDatetime"]),
Quantity = Convert.ToInt32(reader["Quantity"])
});
}
reader.Close();
return(models);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not collect delivered Replace list by branch Id and date", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
CommandObj.Parameters.Clear();
}
}
public ICollection <ReplaceReport> GetAllReplaceListByBranchId(int branchId)
{
try
{
CommandObj.CommandText = "UDSP_RptGetAllReplaceListByBranchId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@BranchId", branchId);
List <ReplaceReport> models = new List <ReplaceReport>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
models.Add(new ReplaceReport
{
ClientName = reader["ClientName"].ToString(),
Barcode = reader["ProductBarcode"].ToString(),
TransactionRef = reader["TransactionRef"].ToString(),
ProductName = reader["ProductName"].ToString(),
SystemDateTime = Convert.ToDateTime(reader["SysDateTime"]),
ReplaceForBarcode = reader["ReplaceFor"].ToString(),
EntryDateTime = Convert.ToDateTime(reader["ReceiveDatetime"]),
ReceiveId = Convert.ToInt64(reader["ReceiveId"])
});
}
reader.Close();
return(models);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not collect delivered Replace list by branch Id", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <ClientAttachment> GetClientAttachments()
{
try
{
CommandObj.CommandText = "UDSP_GetClientAttachments";
CommandObj.CommandType = CommandType.StoredProcedure;
List <ClientAttachment> attachments = new List <ClientAttachment>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
attachments.Add(new ClientAttachment
{
AttachmentName = reader["AttachmentName"].ToString(),
ClientId = Convert.ToInt32(reader["ClientId"]),
FileExtension = reader["FileExtension"].ToString(),
FilePath = reader["FilePath"].ToString(),
UploadedByUserId = Convert.ToInt32(reader["UploadedByUserId"]),
Id = Convert.ToInt32(reader["AttachmentId"])
});
}
reader.Close();
return(attachments);
}
catch (SqlException sqlException)
{
throw new Exception("Unable to collect Clients attachment due to sql exception", sqlException);
}
catch (Exception exception)
{
throw new Exception("Unable to collect Client attachment", exception);
}
finally
{
CommandObj.Parameters.Clear();
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public StudentViewModel GetStudentInformationById(int id)
{
try
{
CommandObj.CommandText = "spGetStudentInformationById";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@Id", id);
StudentViewModel student = null;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
student = new StudentViewModel
{
Id = Convert.ToInt32(reader["Id"].ToString()),
RegNo = reader["RegNo"].ToString(),
Name = reader["Name"].ToString(),
Email = reader["Email"].ToString(),
ContactNo = reader["ContactNo"].ToString(),
RegisterationDate = Convert.ToDateTime(reader["RegisterationDate"].ToString()),
Address = reader["Address"].ToString(),
Department = reader["Department"].ToString()
};
}
reader.Close();
return(student);
}
catch (Exception exception)
{
throw new Exception("Unable to get student information", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public IEnumerable <TempClassSchedule> GetAllClassSchedulesByDeparmentId(int departmentId, int courseId)
{
try
{
List <TempClassSchedule> scheduleList = new List <TempClassSchedule>();
CommandObj.CommandText = "SELECT * FROM ScheduleOfClass WHERE DepartmentId='" + departmentId + "' AND CourseId='" + courseId + "' AND AllocationStatus='" + 1 + "'";
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
TempClassSchedule schedule = new TempClassSchedule
{
DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString()),
CourseCode = reader["Code"].ToString(),
CourseName = reader["Name"].ToString(),
RoomName = reader["Room_Name"].ToString(),
DayName = reader["Day_Name"].ToString(),
StartTime = Convert.ToDateTime(reader["StartTime"].ToString()),
EndTime = Convert.ToDateTime(reader["EndTime"].ToString()),
Status = Convert.ToBoolean(reader["AllocationStatus"])
};
scheduleList.Add(schedule);
}
reader.Close();
//ConnectionObj.Close();
return(scheduleList);
}
catch (Exception exception)
{
throw new Exception("Unable to collect class schedule", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
}
}
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 <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 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 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 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 <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 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 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 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 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 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 <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 <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 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 <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 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 <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 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 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 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();
}
}
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 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();
}
}
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 <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 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();
}
}