public IEnumerable <ViewRegion> GetRegionListWithDistrictInfo()
{
try
{
CommandObj.CommandText = "UDSP_GetRegionListWithDistrictInfo";
CommandObj.CommandType = CommandType.StoredProcedure;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <ViewRegion> regions = new List <ViewRegion>();
while (reader.Read())
{
regions.Add(new ViewRegion
{
RegionDetailsId = Convert.ToInt32(reader["RegionDetailsId"]),
RegionId = Convert.ToInt32(reader["RegionId"]),
RegionName = reader["RegionName"].ToString(),
DivisionId = Convert.ToInt32(reader["DivisionId"]),
IsAssigned = reader["IsAssigned"].ToString(),
IsCurrent = reader["IsCurrent"].ToString(),
SysDateTime = Convert.ToDateTime(reader["SysDateTime"]),
DistrictId = Convert.ToInt32(reader["DistrictId"]),
District = new District
{
DistrictId = Convert.ToInt32(reader["DistrictId"]),
DistrictName = reader["DistrictName"].ToString()
}
});
}
reader.Close();
return(regions);
}
catch (Exception e)
{
throw new Exception("Could not Collect regions", e);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public int SaveBarCodes(ViewCreateBarCodeModel model)
{
ConnectionObj.Open();
SqlTransaction sqlTransaction = ConnectionObj.BeginTransaction();
try
{
CommandObj.Transaction = sqlTransaction;
CommandObj.CommandText = "UDSP_AddBarCodeMaster";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@ProductId", model.ProductId);
CommandObj.Parameters.AddWithValue("@LineNumber", model.ProductionLineId.ToString("D2"));
CommandObj.Parameters.AddWithValue("@Quantity", model.BarCodes.Count);
CommandObj.Parameters.AddWithValue("@GenerateByUserId", model.GenerateByUserId);
CommandObj.Parameters.Add("@BarCodeMasterId", SqlDbType.BigInt);
CommandObj.Parameters["@BarCodeMasterId"].Direction = ParameterDirection.Output;
CommandObj.ExecuteNonQuery();
var barCodeMasterId = Convert.ToInt64(CommandObj.Parameters["@BarCodeMasterId"].Value);
int result = AddBarCode(model, barCodeMasterId);
if (result > 0)
{
sqlTransaction.Commit();
}
return(result);
}
catch (SqlException sqlException)
{
sqlTransaction.Rollback();
throw new Exception("Could not Save Order due to sql exception", sqlException);
}
catch (Exception exception)
{
sqlTransaction.Rollback();
throw new Exception("Could not Save Order", exception);
}
finally
{
CommandObj.Parameters.Clear();
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public ICollection <ViewReplaceModel> GetAllDeliveredReplaceListByBranchAndCompany(int branchId, int companyId)
{
try
{
CommandObj.CommandText = "UDSP_GetAllDeliveredReplaceListByBranchAndCompany";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@BranchId", branchId);
CommandObj.Parameters.AddWithValue("@CompanyId", companyId);
List <ViewReplaceModel> models = new List <ViewReplaceModel>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
models.Add(new ViewReplaceModel
{
ReplaceId = Convert.ToInt64(reader["ReplaceId"]),
ClientCode = reader["ClientCode"].ToString(),
ClientId = Convert.ToInt32(reader["ClientId"]),
ClientName = reader["ClientName"].ToString(),
ClientAddress = reader["ClientAddress"].ToString(),
EntryDate = Convert.ToDateTime(reader["SysDateTime"]),
Quantity = Convert.ToInt32(reader["ReplaceQuantity"]),
ReplaceRef = reader["ReplaceRef"].ToString(),
BranchId = Convert.ToInt32(reader["BranchId"]),
CompanyId = Convert.ToInt32(reader["CompanyId"])
});
}
reader.Close();
return(models);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not collect delivered Replace list by branch and Company Id", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
CommandObj.Parameters.Clear();
}
}
public Discount GetCurrentDiscountByClientTypeId(int clientTypeId)
{
try
{
CommandObj.CommandText = "spGetCurrentDiscountByClientTypeId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@ClientTypeId", clientTypeId);
ConnectionObj.Open();
Discount discount = new Discount();
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
discount = new Discount
{
DiscountId = Convert.ToInt32(reader["DiscountId"]),
DiscountPercent = Convert.ToDecimal(reader["DiscountPercent"]),
TerritoryId = Convert.ToInt32(reader["TerritoryId"]),
ClientTypeId = Convert.ToInt32(reader["TerritoryId"]),
ProductTypeId = Convert.ToInt32(reader["ProductTypeId"]),
ApprovedByUserId = Convert.ToInt32(reader["ApproveByUserId"]),
UpdateDate = Convert.ToDateTime(reader["UpdateDate"]),
UpdateByUserId = Convert.ToInt32(reader["UpdateByUserId"]),
EntryStatus = reader["EntryStatus"].ToString(),
IsCurrent = reader["IsCurrent"].ToString(),
SysDateTime = Convert.ToDateTime(reader["SysDateTime"]),
ApprovedDate = Convert.ToDateTime(reader["ApproveDate"])
};
reader.Close();
}
return(discount);
}
catch (Exception exception)
{
throw new Exception("Could not collect Current discount by Client type Id", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public List <Religion> GetAllReligion()
{
string getReligion = "SELECT * FROM tbl_religion";
ConnectionObj.Open();
CommandObj.CommandText = getReligion;
SqlDataReader reader = CommandObj.ExecuteReader();
var upazilaList = new List <Religion>();
while (reader.Read())
{
Religion aReligion = new Religion();
aReligion.Id = Convert.ToInt32(reader["id"].ToString());
aReligion.ReligionName = reader["religion"].ToString();
upazilaList.Add(aReligion);
}
reader.Close();
ConnectionObj.Close();
return(upazilaList);
}
public List <District> GetAllDistrict()
{
string getDistrict = "SELECT * FROM tbl_district Order By district_name ASC";
ConnectionObj.Open();
CommandObj.CommandText = getDistrict;
SqlDataReader reader = CommandObj.ExecuteReader();
var districtList = new List <District>();
while (reader.Read())
{
District aDistrict = new District();
aDistrict.Id = Convert.ToInt32(reader["id"].ToString());
aDistrict.DistrictName = reader["district_name"].ToString();
districtList.Add(aDistrict);
}
reader.Close();
ConnectionObj.Close();
return(districtList);
}
public int SaveCourse(Course aCourse)
{
string query = "INSERT Course_tbl (Code,Name,Credit,Description,DepartmentId,SemesterId) VALUES(@code,@name,@credit,@description,@department,@semester)";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("code", aCourse.Code);
CommandObj.Parameters.AddWithValue("name", aCourse.Name);
CommandObj.Parameters.AddWithValue("credit", aCourse.Credit);
CommandObj.Parameters.AddWithValue("description", aCourse.Description);
CommandObj.Parameters.AddWithValue("department", aCourse.DepartmentId);
CommandObj.Parameters.AddWithValue("semester", aCourse.SemesterId);
ConnectionObj.Open();
int rowAffact = CommandObj.ExecuteNonQuery();
ConnectionObj.Close();
return(rowAffact);
}
public int SaveCourseAssignToTeacherInfo(CourseAssignToTeacher courseAssign)
{
ConnectionObj.Open();
CommandObj.CommandText = "INSERT INTO CourseAssignToTeacher_tbl (DepartmentId,TeacherId,CourseId,IsAssign) VALUES(@deptId,@teacherId,@courseId,@status)";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@deptId", courseAssign.DepartmentId);
CommandObj.Parameters.AddWithValue("@teacherId", courseAssign.TeacherId);
CommandObj.Parameters.AddWithValue("@courseId", courseAssign.CourseId);
CommandObj.Parameters.AddWithValue("@status", 1);
int rowAffected = CommandObj.ExecuteNonQuery();
int updateResult = UpdateTeacher(courseAssign);
CommandObj.Dispose();
ConnectionObj.Close();
return(rowAffected);
}
public List <Upazila> GetAllUpazila(int districtId)
{
string getUpazila = "SELECT * FROM tbl_upazila where district_id = '" + districtId + "' Order By upazila_name ASC";
ConnectionObj.Open();
CommandObj.CommandText = getUpazila;
SqlDataReader reader = CommandObj.ExecuteReader();
var upazilaList = new List <Upazila>();
while (reader.Read())
{
Upazila aUpazila = new Upazila();
aUpazila.Id = Convert.ToInt32(reader["id"].ToString());
aUpazila.UpazilaName = reader["upazila_name"].ToString();
upazilaList.Add(aUpazila);
}
reader.Close();
ConnectionObj.Close();
return(upazilaList);
}
public List <BloodGroup> GetAllBloodGroup()
{
string getBloodGroup = "SELECT * FROM tbl_blood";
ConnectionObj.Open();
CommandObj.CommandText = getBloodGroup;
SqlDataReader reader = CommandObj.ExecuteReader();
var upazilaList = new List <BloodGroup>();
while (reader.Read())
{
BloodGroup aBloodGroup = new BloodGroup();
aBloodGroup.Id = Convert.ToInt32(reader["id"].ToString());
aBloodGroup.GroupName = reader["blood_name"].ToString();
upazilaList.Add(aBloodGroup);
}
reader.Close();
ConnectionObj.Close();
return(upazilaList);
}
public ICollection <ViewDispatchModel> GetDispatchDetailsByDispatchId(long dispatchId)
{
try
{
CommandObj.CommandText = "UDSP_GetDispatchDetailsByDispatchId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@DispatchId", dispatchId);
ConnectionObj.Open();
List <ViewDispatchModel> dispatchModels = new List <ViewDispatchModel>();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
dispatchModels.Add(new ViewDispatchModel
{
ProductName = reader["ProductName"].ToString(),
ToBranchId = Convert.ToInt32(reader["ToBranchId"]),
Quantity = Convert.ToInt32(reader["Quantity"]),
DispatchItemId = Convert.ToInt64(reader["DispatchItemsId"]),
SubSubSubAccountCode = reader["SubSubSubAccountCode"].ToString(),
ProductCategory = new ProductCategory
{
ProductCategoryId = Convert.ToInt32(reader["CategoryId"]),
ProductCategoryName = reader["ProductCategoryName"].ToString(),
}
});
}
reader.Close();
return(dispatchModels);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not get dispatch deatils Info by dispatchId", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public DispatchModel GetDispatchByDispatchId(long dispatchId)
{
try
{
CommandObj.CommandText = "UDSP_GetDispatchByDispatchId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@DispatchId", dispatchId);
DispatchModel dispatch = null;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
dispatch = new DispatchModel
{
DispatchId = dispatchId,
CompanyId = Convert.ToInt32(reader["CompanyId"]),
TransactionRef = reader["TransactionRef"].ToString(),
TripId = Convert.ToInt64(reader["TripId"]),
DispatchByUserId = Convert.ToInt32(reader["DispatchByUserId"]),
DispatchDate = Convert.ToDateTime(reader["DispatchDate"]),
SystemDateTime = Convert.ToDateTime(reader["SystemDateTime"]),
DispatchRef = reader["DispatchRef"].ToString(),
IsCanclled = reader["IsCancelled"].ToString(),
Quantity = Convert.ToInt32(reader["Quantity"]),
Remarks = DBNull.Value.Equals(reader["Remarks"])?null:reader["Remarks"].ToString()
};
}
reader.Close();
return(dispatch);
}
catch (Exception exception)
{
throw new Exception("Could not get dispatch Info by dispatchId", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public ICollection <Discount> GetAll()
{
try
{
CommandObj.CommandText = "UDSP_GetAllDiscounts";
CommandObj.CommandType = CommandType.StoredProcedure;
List <Discount> discounts = new List <Discount>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
discounts.Add(new Discount
{
DiscountId = Convert.ToInt32(reader["DiscountId"]),
DiscountPercent = Convert.ToDecimal(reader["DiscountPercent"]),
ClientTypeId = Convert.ToInt32(reader["ClientTypeId"]),
ProductId = Convert.ToInt32(reader["ProductId"]),
ApprovedDate = Convert.ToDateTime(reader["ApproveDate"]),
ClientType = new ClientType
{
ClientTypeName = reader["ClientTypeName"].ToString(),
ClientTypeId = Convert.ToInt32(reader["ClientTypeId"]),
DiscountPercent = Convert.ToDecimal(reader["DiscountPercent"])
}
});
}
reader.Close();
return(discounts);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not collect Discounts", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public List <Profile> GetInformationByUserId(int userId)
{
string selectSql = "select stu_name,class_name,class_code,stu_section,stu_session,stu_gender,stu_dob,stu_address,district_name,upazila_name, blood_name,religion,stu_contact,stu_father_name,stu_mother_name,father_contact,stu_guardian_name,stu_guardian_profession,stu_guardian_contact,stu_img from tbl_student JOIN tbl_class ON tbl_class.id = tbl_student.stu_class_id JOIN tbl_district ON tbl_district.id = tbl_student.district_id JOIN tbl_upazila ON tbl_upazila.id = tbl_student.upazila_id JOIN tbl_blood ON tbl_blood.id = tbl_student.blood_group_id JOIN tbl_religion ON tbl_religion.id = tbl_student.religion_id where user_id = '" + userId + "' ";
ConnectionObj.Open();
CommandObj.CommandText = selectSql;
SqlDataReader reader = CommandObj.ExecuteReader();
var userList = new List <Profile>();
while (reader.Read())
{
var aProfile = new Profile
{
Name = reader["stu_name"].ToString(),
ClassName = reader["class_name"].ToString(),
ClassCode = reader["class_code"].ToString(),
Section = reader["stu_section"].ToString(),
Session = reader["stu_session"].ToString(),
Gender = reader["stu_gender"].ToString(),
DateOfBirth = reader["stu_dob"].ToString(),
Address = reader["stu_address"].ToString(),
DistrictName = reader["district_name"].ToString(),
UpazilaName = reader["upazila_name"].ToString(),
BloodGroup = reader["blood_name"].ToString(),
Religion = reader["religion"].ToString(),
Contact = reader["stu_contact"].ToString(),
FathersName = reader["stu_father_name"].ToString(),
MothersName = reader["stu_mother_name"].ToString(),
FathersContact = reader["father_contact"].ToString(),
GuardianName = reader["stu_guardian_name"].ToString(),
GuardianProfession = reader["stu_guardian_profession"].ToString(),
GuardianContact = reader["stu_guardian_contact"].ToString(),
Image = reader["stu_img"].ToString()
};
userList.Add(aProfile);
}
reader.Close();
ConnectionObj.Close();
return(userList);
}
public IEnumerable <ViewProduct> GetDeliveredProductsByDeliveryIdAndProductId(long deliveryId, int productId)
{
try
{
CommandObj.CommandText = "spGetDeliveredProductsByDeliveryIdAndProductId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@DeliveryId", deliveryId);
CommandObj.Parameters.AddWithValue("@productId", productId);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <ViewProduct> products = new List <ViewProduct>();
while (reader.Read())
{
ViewProduct aModel = new ViewProduct
{
ProductId = productId,
ProductName = reader["ProductName"].ToString(),
ProductBarCode = reader["ProductBarCode"].ToString(),
CategoryId = Convert.ToInt32(reader["CategoryId"]),
ProductCategoryName = reader["ProductCategoryName"].ToString()
};
products.Add(aModel);
}
reader.Close();
return(products);
}
catch (SqlException exception)
{
throw new Exception("Could not Collect Delivered products due to Db Exception", exception);
}
catch (Exception exception)
{
throw new Exception("Could not Collect Delivered products Orders", exception);
}
finally
{
CommandObj.Parameters.Clear();
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public int Update(Employee anEmployee)
{
try
{
CommandObj.CommandText = "spUpdateEmployeeInformation";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@EmployeeTypeId", anEmployee.EmployeeTypeId);
CommandObj.Parameters.AddWithValue("@DesignationId", anEmployee.DesignationId);
CommandObj.Parameters.AddWithValue("@DepartmentId", anEmployee.DepartmentId);
CommandObj.Parameters.AddWithValue("@BranchId", anEmployee.BranchId);
CommandObj.Parameters.AddWithValue("@EmployeeNo", anEmployee.EmployeeNo);
CommandObj.Parameters.AddWithValue("@EmployeeName", anEmployee.EmployeeName);
CommandObj.Parameters.AddWithValue("@Gender", anEmployee.Gender);
CommandObj.Parameters.AddWithValue("@PresentAddress", anEmployee.PresentAddress);
CommandObj.Parameters.AddWithValue("@Phone", anEmployee.Phone);
CommandObj.Parameters.AddWithValue("@Email", anEmployee.Email);
CommandObj.Parameters.AddWithValue("@AlternatePhone", anEmployee.AlternatePhone ?? anEmployee.AlternatePhone ?? (object)DBNull.Value);
CommandObj.Parameters.AddWithValue("@EmployeeImage", anEmployee.EmployeeImage ?? anEmployee.EmployeeImage ?? (object)DBNull.Value);
CommandObj.Parameters.AddWithValue("@EmployeeSignature", anEmployee.EmployeeSignature ?? anEmployee.EmployeeSignature ?? (object)DBNull.Value);
CommandObj.Parameters.AddWithValue("@EmployeeNationalIdNo", anEmployee.NationalIdNo ?? anEmployee.NationalIdNo ?? (object)DBNull.Value);
CommandObj.Parameters.AddWithValue("@JoiningDate", anEmployee.JoiningDate);
CommandObj.Parameters.AddWithValue("@DoB", anEmployee.DoB ?? anEmployee.DoB ?? (object)DBNull.Value);
CommandObj.Parameters.AddWithValue("@EmployeeId", anEmployee.EmployeeId);
CommandObj.Parameters.Add("@RowAffected", SqlDbType.Int);
CommandObj.Parameters["@RowAffected"].Direction = ParameterDirection.Output;
ConnectionObj.Open();
CommandObj.ExecuteNonQuery();
int rowAffected = Convert.ToInt32(CommandObj.Parameters["@RowAffected"].Value);
return(rowAffected);
}
catch (Exception exception)
{
throw new Exception("Could not Update Employee Information", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public int SaveScrap(ScrapModel model)
{
ConnectionObj.Open();
SqlTransaction sqlTransaction = ConnectionObj.BeginTransaction();
try
{
CommandObj.Parameters.Clear();
CommandObj.Transaction = sqlTransaction;
CommandObj.CommandText = "UDSP_SaveScrapProduct";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@UserId", model.UserId);
CommandObj.Parameters.AddWithValue("@BranchId", model.BranchId);
CommandObj.Parameters.AddWithValue("@CompanyId", model.CompanyId);
CommandObj.Parameters.AddWithValue("@TransactionRef", model.TransactionRef);
CommandObj.Parameters.AddWithValue("@TransactionType", model.TransactionType);
CommandObj.Parameters.AddWithValue("@TransactionDate", model.TransactionDate);
CommandObj.Parameters.Add("@MasterId", SqlDbType.BigInt);
CommandObj.Parameters["@MasterId"].Direction = ParameterDirection.Output;
CommandObj.ExecuteNonQuery();
long masterId = Convert.ToInt32(CommandObj.Parameters["@MasterId"].Value);
var rowAffected = SaveScrapProductToScrapInventory(model, masterId);
if (rowAffected > 0)
{
sqlTransaction.Commit();
}
return(rowAffected);
}
catch (Exception exception)
{
sqlTransaction.Rollback();
Log.WriteErrorLog(exception);
throw new Exception("Could not save Scrap product Info", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
CommandObj.Parameters.Clear();
}
}
public List <Classes> GetAllClasses()
{
string getClasses = "SELECT * FROM tbl_class";
ConnectionObj.Open();
CommandObj.CommandText = getClasses;
SqlDataReader reader = CommandObj.ExecuteReader();
var districtList = new List <Classes>();
while (reader.Read())
{
Classes aClasses = new Classes();
aClasses.Id = Convert.ToInt32(reader["id"].ToString());
aClasses.ClassName = reader["class_name"].ToString();
aClasses.ClassCode = reader["class_code"].ToString();
districtList.Add(aClasses);
}
reader.Close();
ConnectionObj.Close();
return(districtList);
}
public List <Department> GetallDepartmentName()
{
ConnectionObj.Open();
string select = String.Format("SELECT * FROM tblDept");
CommandObj.Parameters.Clear();
CommandObj.CommandText = select;
SqlDataReader reader = CommandObj.ExecuteReader();
List <Department> departments = new List <Department>();
while (reader.Read())
{
Department aDepartment = new Department();
aDepartment.DeptId = Convert.ToInt32(reader[0]);
aDepartment.DeptName = reader[1].ToString();
departments.Add(aDepartment);
}
ConnectionObj.Close();
return(departments);
}
public int SaveTeacher(Teacher teacher)
{
string query = "INSERT INTO Teacher_tbl(Name,Address,Email,Contact,DesignationId,DepartmentId,CreditToBeTaken,CreditTaken) VALUES(@Name,@Address,@Email,@Contact, @DesignationId,@DepartmentId,@CreditTobeTaken,@RemainingCredit)";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@Name", teacher.Name);
CommandObj.Parameters.AddWithValue("@Address", teacher.Address);
CommandObj.Parameters.AddWithValue("@Email", teacher.Email.ToLower());
CommandObj.Parameters.AddWithValue("@Contact", teacher.Contact);
CommandObj.Parameters.AddWithValue("@DesignationId", teacher.DesignationId);
CommandObj.Parameters.AddWithValue("@DepartmentId", teacher.DepartmentId);
CommandObj.Parameters.AddWithValue("@CreditTobeTaken", teacher.CreditTobeTaken);
CommandObj.Parameters.AddWithValue("@RemainingCredit", 0);
ConnectionObj.Open();
int rowAffact = CommandObj.ExecuteNonQuery();
ConnectionObj.Close();
CommandObj.Dispose();
return(rowAffact);
}
public int InsertClassRoom(ClassRoom room)
{
string query =
"INSERT INTO AllocateClassRoom_tbl (DepartmentId,CourseId,RoomId,DayId,StartTime,EndTime,AllocationStatus) VALUES(@deptId,@courseId,@roomId,@dayId,@StartTime,@endTime,@allocationStatus)";
CommandObj.CommandText = query;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@deptId", room.DepartmentId);
CommandObj.Parameters.AddWithValue("@courseId", room.CourseId);
CommandObj.Parameters.AddWithValue("@roomId", room.RoomId);
CommandObj.Parameters.AddWithValue("@dayId", room.DayId);
CommandObj.Parameters.AddWithValue("@startTime", room.StartTime.ToShortTimeString());
CommandObj.Parameters.AddWithValue("@endTime", room.Endtime.ToShortTimeString());
CommandObj.Parameters.AddWithValue("@allocationStatus", 1);
ConnectionObj.Open();
int rowAffected = CommandObj.ExecuteNonQuery();
CommandObj.Dispose();
ConnectionObj.Close();
return(rowAffected);
}
public ICollection <Territory> GetAll()
{
try
{
CommandObj.CommandText = "spGetAllTerritories";
CommandObj.CommandType = CommandType.StoredProcedure;
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(),
RegionId = Convert.ToInt32(reader["RegionId"]),
Region = new Region
{
RegionId = Convert.ToInt32(reader["RegionId"]),
RegionName = reader["RegionName"].ToString()
},
UpazillaList = _upazillaGateway.GetAssignedUpazillaLsitByTerritoryId(Convert.ToInt32(reader["TerritoryId"])).ToList()
});
}
reader.Close();
return(territories);
}
catch (Exception e)
{
throw new Exception("Could not Collect territories", e);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public ViewRegion GetRegionDetailsById(int regionDetailsId)
{
try
{
CommandObj.CommandText = "UDSP_GetRegionDetailsById";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@RegionDetailsId", regionDetailsId);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
ViewRegion region = null;
if (reader.Read())
{
region = new ViewRegion
{
RegionDetailsId = regionDetailsId,
RegionId = Convert.ToInt32(reader["RegionId"]),
RegionName = reader["RegionName"].ToString(),
DivisionId = Convert.ToInt32(reader["DivisionId"]),
DistrictId = Convert.ToInt32(reader["DistrictId"]),
District = new District
{
DistrictId = Convert.ToInt32(reader["DistrictId"]),
DistrictName = reader["DistrictName"].ToString()
}
};
}
reader.Close();
return(region);
}
catch (Exception e)
{
throw new Exception("Could not Collect regions", e);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <ViewBranch> GetAllBranches()
{
try
{
CommandObj.CommandText = "UDSP_GetAllBranch";
CommandObj.CommandType = CommandType.StoredProcedure;
List <ViewBranch> branches = new List <ViewBranch>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
branches.Add(new ViewBranch
{
BranchId = Convert.ToInt32(reader["BranchId"]),
BranchName = reader["BranchName"].ToString(),
BranchAddress = reader["BranchAddress"].ToString(),
BranchEmail = reader["Email"].ToString(),
BranchPhone = reader["Phone"].ToString(),
SubSubSubAccountCode = reader["SubSubSubAccountCode"].ToString(),
BranchOpenigDate = Convert.ToDateTime(reader["BranchOpenigDate"]),
Title = reader["Title"].ToString(),
//Clients = _clientManager.GetClientByBranchId(Convert.ToInt32(reader["BranchId"])).ToList(),
//RegionList = _regionManager.GetAssignedRegionListToBranchByBranchId(Convert.ToInt32(reader["BranchId"])),
//Orders =_orderManager.GetOrdersByBranchId(Convert.ToInt32(reader["BranchId"])).ToList()
});
}
reader.Close();
return(branches);
}
catch (Exception exception)
{
throw new Exception("Coluld not collect branch", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <Branch> GetAssignedBranchByUserId(int userId)
{
try
{
CommandObj.CommandText = "spGetAssignedBranchToUserByUserId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@UserId", userId);
List <Branch> branchList = new List <Branch>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
Branch aBranch = new Branch
{
BranchId = Convert.ToInt32(reader["BranchId"]),
BranchAddress = reader["BranchAddress"].ToString(),
BranchEmail = reader["Email"].ToString(),
BranchPhone = reader["Phone"].ToString(),
SubSubSubAccountCode = reader["SubSubSubAccountCode"].ToString(),
//RegionId=Convert.ToInt32(reader["RegionId"]),
BranchOpenigDate = Convert.ToDateTime(reader["BranchOpenigDate"]),
BranchName = reader["BranchName"].ToString()
};
branchList.Add(aBranch);
}
reader.Close();
return(branchList);
}
catch (Exception exception)
{
Log.WriteErrorLog(exception);
throw new Exception("Could not collect assigned branches", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public Vat GetCurrentVatByProductId(int productId)
{
try
{
CommandObj.CommandText = "spGetCurrentVatByProductId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@ProductId", productId);
ConnectionObj.Open();
Vat aVat = new Vat();
SqlDataReader reader = CommandObj.ExecuteReader();
if (reader.Read())
{
aVat = new Vat
{
VatId = Convert.ToInt32(reader["VatId"]),
VatAmount = Convert.ToDecimal(reader["VatAmount"]),
ProductId = Convert.ToInt32(reader["ProductId"]),
ApprovedByUserId = Convert.ToInt32(reader["ApproveByUserId"]),
UpdateDate = Convert.ToDateTime(reader["UpdateDate"]),
UpdateByUserId = Convert.ToInt32(reader["UpdateByUserId"]),
EntryStatus = reader["EntryStatus"].ToString(),
IsCurrent = reader["IsCurrent"].ToString(),
SysDateTime = Convert.ToDateTime(reader["SysDateTime"]),
ApprovedDate = Convert.ToDateTime(reader["ApproveDate"])
};
reader.Close();
}
return(aVat);
}
catch (Exception exception)
{
throw new Exception("Could not collect Current Vat by Product Id", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <Vat> GetAllPendingVats()
{
try
{
List <Vat> vats = new List <Vat>();
CommandObj.CommandText = "UDSP_GetAllPendingVats";
CommandObj.CommandType = CommandType.StoredProcedure;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
vats.Add(new Vat
{
VatId = Convert.ToInt32(reader["VatId"]),
ProductId = Convert.ToInt32(reader["ProductId"]),
Product = new Product
{
ProductId = Convert.ToInt32(reader["ProductId"]),
ProductName = reader["ProductName"].ToString(),
SubSubSubAccountCode = reader["SubSubSubAccountCode"].ToString()
},
VatAmount = Convert.ToDecimal(reader["VatAmount"]),
UpdateDate = Convert.ToDateTime(reader["UpdateDate"]),
UpdateByUserId = Convert.ToInt32(reader["UpdateByUserId"])
});
}
reader.Close();
return(vats);
}
catch (Exception exception)
{
throw new Exception("Could not collect pending Vat info", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <ViewClientSummaryModel> GetClientSummary()
{
try
{
CommandObj.CommandText = "UDSP_RptGetClientSummary";
CommandObj.CommandType = CommandType.StoredProcedure;
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
List <ViewClientSummaryModel> summary = new List <ViewClientSummaryModel>();
while (reader.Read())
{
summary.Add(new ViewClientSummaryModel
{
ClientId = Convert.ToInt32(reader["ClientId"]),
ClientName = reader["Name"].ToString(),
CommercialName = reader["CommercialName"].ToString(),
Debit = Convert.ToDecimal(reader["DebitAmount"]),
Credit = Convert.ToDecimal(reader["CreditAmount"]),
Outstanding = Convert.ToDecimal(reader["OutStanding"]),
TotalOrder = Convert.ToInt32(reader["TotalOrder"])
});
}
return(summary);
}
catch (SqlException sqlException)
{
throw new Exception("Could not upload client summary due to sql Exception", sqlException);
}
catch (Exception exception)
{
throw new Exception("Could not upload client summary", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}
public IEnumerable <ClientAttachment> GetClientAttachmentsByClientId(int clientId)
{
try
{
CommandObj.CommandText = "UDSP_GetClientAttachmentsByClientId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@ClientId", clientId);
List <ClientAttachment> attachments = new List <ClientAttachment>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
attachments.Add(new ClientAttachment
{
AttachmentName = reader["AttachmentName"].ToString(),
ClientId = 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 by Client Id due to sql exception", sqlException);
}
catch (Exception exception)
{
throw new Exception("Unable to collect Client attachment by Client Id", exception);
}
finally
{
CommandObj.Parameters.Clear();
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public IEnumerable <InvoiceDetails> GetInvoicedOrderDetailsByInvoiceId(int invoiceId)
{
try
{
CommandObj.CommandText = "spGetInvoicedOrderDetailsByInvoiceId";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.AddWithValue("@InvoiceId", invoiceId);
List <InvoiceDetails> invoiceDetails = new List <InvoiceDetails>();
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
InvoiceDetails invoice = new InvoiceDetails
{
InvoiceDetailsId = Convert.ToInt32(reader["InvoiceDetailsId"]),
InvoiceId = Convert.ToInt32(reader["InvoiceId"]),
ProductId = Convert.ToInt32(reader["ProductId"]),
ProductName = reader["ProductName"].ToString(),
Quantity = Convert.ToInt32(reader["Quantity"]),
ProductCategoryName = reader["ProductCategoryName"].ToString(),
UnitPrice = Convert.ToDecimal(reader["UnitPrice"]),
SalePrice = Convert.ToDecimal(reader["SalePrice"])
};
invoiceDetails.Add(invoice);
}
reader.Close();
return(invoiceDetails);
}
catch (Exception exception)
{
throw new Exception("Could not get Invoiced orders by branch and company id", exception);
}
finally
{
ConnectionObj.Close();
CommandObj.Dispose();
CommandObj.Parameters.Clear();
}
}