//below code will fetch only Standard LIst not based on classtype
public List <StandardDTO> Standard()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select StandardId,StandardName from standard where Active=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <StandardDTO> lstStandard = new List <StandardDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
StandardDTO standardDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
standardDTO = new StandardDTO();
standardDTO.StandardId = (int)dr["StandardId"];
standardDTO.StandardName = dr["StandardName"].ToString();
lstStandard.Add(standardDTO);
}
}
return(lstStandard);
}
catch (Exception exp)
{
throw exp;
}
}
}
// returns next standard section w.r.t the current standard
public List <StandardSectionMapDTO> NextStandardSection(int currentSerial)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select StandardSectionId,concat(StandardName,' ', SectionName) as StandardSectionDesc from standardsectionmap ssm join standard std on std.StandardId = ssm.StandardId join section sc on sc.SectionId = ssm.SectionId where ssm.Active = 1 AND ssm.Serial=(@currentSerial+1) order by StandardSectionDesc";
command.Parameters.Add("@currentSerial", MySqlDbType.Int32).Value = currentSerial;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <StandardSectionMapDTO> lstStandardSection = new List <StandardSectionMapDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
StandardSectionMapDTO standardSectionMapDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
standardSectionMapDTO = new StandardSectionMapDTO();
standardSectionMapDTO.StandardSectionId = (int)dr["StandardSectionId"];
standardSectionMapDTO.StandardSectionDesc = dr["StandardSectionDesc"].ToString();
lstStandardSection.Add(standardSectionMapDTO);
}
}
return(lstStandardSection);
}
catch (Exception exp)
{
throw exp;
}
}
}
public List <DesignationDTO> Designation()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select DesignationId,DesignationDescription from designation where Active=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <DesignationDTO> lstDesignation = new List <DesignationDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
DesignationDTO designationDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
designationDTO = new DesignationDTO();
designationDTO.DesignationId = (int)dr["DesignationId"];
designationDTO.DesignationDescription = dr["DesignationDescription"].ToString();
lstDesignation.Add(designationDTO);
}
}
return(lstDesignation);
}
catch (Exception exp)
{
throw exp;
}
}
}
public DataTable GetPendingTransactions(DateTime?runDate)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT LibraryTranId, UserMasterId, DueDate, IsRemindedSubmission, IsReturned, IsMovedToTransaction FROM LibraryTransaction WHERE Active=1 AND IsRemindedSubmission=1 AND IsReturned<>1 AND IsMovedToTransaction<>1 AND TransactionIdForDue IS NULL AND DueDate<@runDate";
command.Parameters.Add("@runDate", MySqlDbType.DateTime).Value = runDate.Value.Date;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtResult = new DataTable();
MySqlDataAdapter mDa = new MySqlDataAdapter(command);
mDa.Fill(_dtResult);
return(_dtResult);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public List <ClassTypeDTO> ClassType()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select ClassTypeId,ClassTypeName from classtype where Active=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <ClassTypeDTO> lstClassType = new List <ClassTypeDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
ClassTypeDTO classDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
classDTO = new ClassTypeDTO();
classDTO.ClassTypeId = (int)dr["ClassTypeId"];
classDTO.ClassTypeName = dr["ClassTypeName"].ToString();
lstClassType.Add(classDTO);
}
}
return(lstClassType);
}
catch (Exception exp)
{
throw exp;
}
}
}
public StatusDTO <UserMasterDTO> Delete(UserMasterDTO user)
{
StatusDTO <UserMasterDTO> status = null;
try
{
if (user != null && user.UserMasterId != 0)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.Connection = dbSvc.GetConnection() as MySqlConnection;
command.CommandText = "UPDATE usermaster SET Active=0 WHERE UserMasterId=@UserMasterId";
command.Parameters.Add("@UserMasterId", MySqlDbType.Int32).Value = user.UserMasterId;
if (command.ExecuteNonQuery() > 0)
{
status = new StatusDTO <UserMasterDTO>();
status.IsSuccess = true;
}
}
}
}
catch (Exception ex)
{
_logger.Log(ex);
throw ex;
}
return(status);
}
public StatusDTO <UserEntitlementDTO> InsertUserEntitlement(UserEntitlementDTO data)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "UserEntitlementMap";
command.CommandType = CommandType.StoredProcedure;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
command.Parameters.Add("@UserMasterId1", MySqlDbType.String).Value = data.UserDetails.UserMasterId;
command.Parameters.Add("@UserRoleId1", MySqlDbType.String).Value = data.RoleDetails.UserRoleId;
MySqlDataReader rdr = command.ExecuteReader(CommandBehavior.CloseConnection);
_dtData = new DataTable();
_dtData.Load(rdr);
StatusDTO <UserEntitlementDTO> status = new StatusDTO <UserEntitlementDTO>();
return(status);
}
catch (Exception exp)
{
throw exp;
}
}
}
public StatusDTO <UserEntitlementDTO> UpdateUserEntitlement(UserEntitlementDTO data)
{
StatusDTO <UserEntitlementDTO> status = new StatusDTO <UserEntitlementDTO>();
status.IsSuccess = false;
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "UPDATE userentitlement set UserRoleId=@UserRoleId1 WHERE UserEntitleMentId=@UserEntitleMentId";
command.Parameters.Add("@UserEntitleMentId", MySqlDbType.Int32).Value = data.RowId;
command.Parameters.Add("@UserRoleId1", MySqlDbType.Int32).Value = data.RoleDetails.UserRoleId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
if (command.ExecuteNonQuery() > 0)
{
status.IsSuccess = true;
}
return(status);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public List <ExamSubTypeDTO> getExamSubTypeList()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select ExamSubTypeId, ExamSubTypeDescription from examsubtype where Active=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <ExamSubTypeDTO> lstexamsubType = new List <ExamSubTypeDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
ExamSubTypeDTO examsubType = null;
foreach (DataRow dr in _dtData.Rows)
{
examsubType = new ExamSubTypeDTO();
examsubType.ExamSubTypeId = (int)dr["ExamSubTypeId"];
examsubType.ExamSubTypeDescription = dr["ExamSubTypeDescription"].ToString();
lstexamsubType.Add(examsubType);
}
}
return(lstexamsubType);
}
catch (Exception exp)
{
throw exp;
}
}
}
public List <HouseTypeDTO> House()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select HouseTypeId,HouseTypeDescription from housetype where Active=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <HouseTypeDTO> lstHouse = new List <HouseTypeDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
HouseTypeDTO houseDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
houseDTO = new HouseTypeDTO();
houseDTO.HouseTypeId = (int)dr["HouseTypeId"];
houseDTO.HouseTypeDescription = dr["HouseTypeDescription"].ToString();
lstHouse.Add(houseDTO);
}
}
return(lstHouse);
}
catch (Exception exp)
{
throw exp;
}
}
}
public List <TransactionRuleDTO> GetTransactionRules()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select TranRuleId, RuleName from transactionrule where Active=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <TransactionRuleDTO> lstTrRule = new List <TransactionRuleDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
TransactionRuleDTO trRule = null;
foreach (DataRow dr in _dtData.Rows)
{
trRule = new TransactionRuleDTO();
trRule.TranRuleId = (int)dr["TranRuleId"];
trRule.RuleName = dr["RuleName"].ToString();
lstTrRule.Add(trRule);
}
}
return(lstTrRule);
}
catch (Exception exp)
{
throw exp;
}
}
}
public List <EntitlementDTO> GetUserRole()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select UserRoleId,RoleName from dic_entitlement where RoleIsActive=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <EntitlementDTO> lstEntitlement = new List <EntitlementDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
EntitlementDTO entitlementDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
entitlementDTO = new EntitlementDTO();
entitlementDTO.UserRoleId = (int)dr["UserRoleId"];
entitlementDTO.RoleName = dr["RoleName"].ToString();
lstEntitlement.Add(entitlementDTO);
}
}
return(lstEntitlement);
}
catch (Exception exp)
{
throw exp;
}
}
}
public List <ActionDTO> GetActions()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select ActionId,ActionName from dic_action where Active=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <ActionDTO> lstAction = new List <ActionDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
ActionDTO actionDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
actionDTO = new ActionDTO();
actionDTO.RowId = (int)dr["ActionId"];
actionDTO.ActionName = dr["ActionName"].ToString();
lstAction.Add(actionDTO);
}
}
return(lstAction);
}
catch (Exception exp)
{
throw exp;
}
}
}
public List <BookCategoryDTO> BookCategry()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select BookCategoryId,BookCategory from bookcategory where Active=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <BookCategoryDTO> lstBookCategory = new List <BookCategoryDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
BookCategoryDTO bookcategoryDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
bookcategoryDTO = new BookCategoryDTO();
bookcategoryDTO.BookCategoryId = (int)dr["BookCategoryId"];
bookcategoryDTO.BookCategory = dr["BookCategory"].ToString();
lstBookCategory.Add(bookcategoryDTO);
}
}
return(lstBookCategory);
}
catch (Exception exp)
{
throw exp;
}
}
}
public StatusDTO <FacultyCourseMapDTO> InsertFacultyCourse(FacultyCourseMapDTO data)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "Teacher_Course_Map";
command.CommandType = CommandType.StoredProcedure;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
command.Parameters.Add("@EmployeeId", MySqlDbType.String).Value = data.Employee.EmployeeId;
command.Parameters.Add("@SubjectId", MySqlDbType.String).Value = data.Subject.SubjectId;
//if(Convert.ToInt32(data.CreatedBy.UserMasterId) > 0)
//{
// command.Parameters.Add("@CreatedBy", MySqlDbType.String).Value = data.CreatedBy.UserMasterId;
//}
//else
//{
command.Parameters.Add("@CreatedBy", MySqlDbType.String).Value = DBNull.Value;
//}
MySqlDataReader rdr = command.ExecuteReader(CommandBehavior.CloseConnection);
_dtData = new DataTable();
_dtData.Load(rdr);
StatusDTO <FacultyCourseMapDTO> status = new StatusDTO <FacultyCourseMapDTO>();
return(status);
}
catch (Exception exp)
{
throw exp;
}
}
}
public bool MoveLibTransToCashTrans(int libTrnsId, bool?IsMovedToTransaction, int?cashTrnsId)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "UPDATE LibraryTransaction SET IsMovedToTransaction=@isMoved, TransactionIdForDue=@trnsIdForDue WHERE LibraryTranId=@libTrnsId";
command.Parameters.Add("@isMoved", MySqlDbType.Bit).Value = IsMovedToTransaction;
command.Parameters.Add("@trnsIdForDue", MySqlDbType.Int32).Value = cashTrnsId;
command.Parameters.Add("@libTrnsId", MySqlDbType.Int32).Value = libTrnsId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
return(command.ExecuteNonQuery() > 0);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public StatusDTO <UserTransactionDTO> Delete(UserTransactionDTO data)
{
StatusDTO <UserTransactionDTO> status = new StatusDTO <UserTransactionDTO>();
status.IsSuccess = false;
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "DELETE FROM UserTransaction WHERE UserTransactionId=@userTrans";
command.Parameters.Add("@userTrans", MySqlDbType.Int32).Value = data.UserTransactionId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
if (command.ExecuteNonQuery() > 0)
{
status.IsSuccess = true;
}
return(status);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public StatusDTO <FacultyCourseMapDTO> UpdateFacultyCourseMap(FacultyCourseMapDTO data)
{
StatusDTO <FacultyCourseMapDTO> status = new StatusDTO <FacultyCourseMapDTO>();
status.IsSuccess = false;
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "UPDATE facultycoursemap set SubjectId=@SubjectId WHERE FacultyCourseMapId=@FacultyCourseMapId";
command.Parameters.Add("@FacultyCourseMapId", MySqlDbType.Int32).Value = data.FacultyCourseMapId;
command.Parameters.Add("@SubjectId", MySqlDbType.Int32).Value = data.Subject.SubjectId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
if (command.ExecuteNonQuery() > 0)
{
status.IsSuccess = true;
}
return(status);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public DataTable GetUserTransactions(DateTime?runDate)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT UT.UserTransactionId, UT.UserMasterId, UT.TranMasterId, UT.GraceAmountOn, UT.GraceAmount, UT.LastAutoTransactionOn, UT.NextAutoTransactionOn, UM.RoleId, UM.EmailId, SSM.StandardId, SSM.SectionId, SSM.StandardSectionId, S.ClassTypeId, UM.RoleId, TM.TransactionType FROM UserTransaction UT" +
" LEFT JOIN UserMaster UM ON UM.UserMasterId = UT.UserMasterId LEFT JOIN StudentInfo SI ON UM.UserMasterId=SI.UserMasterId LEFT JOIN StandardSectionMap SSM ON SI.StandardSectionId = SSM.StandardSectionId" +
" LEFT JOIN Standard S ON SSM.StandardId = S.StandardId LEFT JOIN transactionmaster TM ON UT.TranMasterId=TM.TranMasterId" +
" WHERE UT.Active=1 AND UM.Active=1 AND ((NextAutoTransactionOn IS NULL AND LastAutoTransactionOn IS NULL) OR NextAutoTransactionOn<=@runDate)";
command.Parameters.Add("@runDate", MySqlDbType.DateTime).Value = runDate.Value.Date;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtResult = new DataTable();
MySqlDataAdapter mDa = new MySqlDataAdapter(command);
mDa.Fill(_dtResult);
return(_dtResult);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public StatusDTO UpdateTransLastRunNextRun(UserTransactionDTO userTrans)
{
StatusDTO status = new StatusDTO();
status.IsSuccess = false;
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "UPDATE UserTransaction SET LastAutoTransactionOn=@lastTransOn, NextAutoTransactionOn=@nextAutoTransOn WHERE UserTransactionId=@userTrans";
command.Parameters.Add("@lastTransOn", MySqlDbType.Date).Value = userTrans.LastAutoTransactionOn;
command.Parameters.Add("@nextAutoTransOn", MySqlDbType.Date).Value = userTrans.NextAutoTransactionOn;
command.Parameters.Add("@userTrans", MySqlDbType.Int32).Value = userTrans.UserTransactionId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
if (command.ExecuteNonQuery() > 0)
{
status.IsSuccess = true;
}
return(status);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public bool ResetPassword(string newPassword, int UserMasterId)
{
bool isResetPassword = false;
string updateClause = null;
string whereClause = null;
try
{
if (!string.IsNullOrEmpty(newPassword) && UserMasterId != 0)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.Connection = dbSvc.GetConnection() as MySqlConnection;// establish connection
updateClause = "update usermaster set Password=@newPassword ";
whereClause = "WHERE UserMasterId=@UserMasterId AND Active=1";
command.CommandText = updateClause + whereClause;
command.Parameters.Add("@newPassword", MySqlDbType.String).Value = newPassword;
command.Parameters.Add("@UserMasterId", MySqlDbType.Int32).Value = UserMasterId;
if (command.ExecuteNonQuery() > 0)
{
isResetPassword = true;
}
}
}
}
catch (Exception ex)
{
}
return(isResetPassword);
}
public int?GetFirstDueAfterDays(int trRuleId)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
int?retValue = null;
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT FirstDueAfterDays FROM transactionrule WHERE TranRuleId=@ruleId";
command.Parameters.Add("@ruleId", MySqlDbType.Int32).Value = trRuleId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtResult = new DataTable();
MySqlDataAdapter mDa = new MySqlDataAdapter(command);
mDa.Fill(_dtResult);
int validator = 0;
if (_dtResult != null && _dtResult.Rows.Count > 0 && Int32.TryParse(_dtResult.Rows[0]["FirstDueAfterDays"].ToString(), out validator))
{
retValue = validator;
}
return(retValue);
}
catch (Exception exp)
{
throw exp;
}
}
}
public List <RoleDTO> Roles()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select RoleId,RoleDescription from roles where Active=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <RoleDTO> lstRole = new List <RoleDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
RoleDTO roleDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
roleDTO = new RoleDTO();
roleDTO.RoleId = (int)dr["RoleId"];
roleDTO.RoleDescription = dr["RoleDescription"].ToString();
lstRole.Add(roleDTO);
}
}
return(lstRole);
}
catch (Exception exp)
{
throw exp;
}
}
}
public StatusDTO <UserTransactionDTO> Update(UserTransactionDTO data)
{
StatusDTO <UserTransactionDTO> status = new StatusDTO <UserTransactionDTO>();
status.IsSuccess = false;
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "UPDATE UserTransaction SET TranMasterId=@tranMasterId, GraceAmountOn=@graceAmountOn, GraceAmount=@graceAmount WHERE UserTransactionId=@uTranId";
if (data.Transaction != null && data.Transaction.TranMasterId > 0)
{
command.Parameters.Add("@tranMasterId", MySqlDbType.Int32).Value = data.Transaction.TranMasterId;
}
else
{
command.Parameters.Add("@tranMasterId", MySqlDbType.Int32).Value = DBNull.Value;
}
if (!string.IsNullOrEmpty(data.GraceAmountIn) && !string.Equals(data.GraceAmountIn, "-1"))
{
command.Parameters.Add("@graceAmountOn", MySqlDbType.String).Value = data.GraceAmountIn;
}
else
{
command.Parameters.Add("@graceAmountOn", MySqlDbType.String).Value = DBNull.Value;
}
if (data.GraceAmount != null)
{
command.Parameters.Add("@graceAmount", MySqlDbType.String).Value = data.GraceAmount.Value;
}
else
{
command.Parameters.Add("@graceAmount", MySqlDbType.String).Value = DBNull.Value;
}
command.Parameters.Add("@uTranId", MySqlDbType.Int32).Value = data.UserTransactionId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
if (command.ExecuteNonQuery() > 0)
{
status.IsSuccess = true;
}
return(status);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public StatusDTO <TransactionRuleDTO> Select(int rowId)
{
StatusDTO <TransactionRuleDTO> status = new StatusDTO <TransactionRuleDTO>();
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.Connection = dbSvc.GetConnection() as MySqlConnection;
command.CommandText = "SELECT TranRuleId, Active, TranMasterId, UserMasterId, StandardId, SectionId, FirstDueAfterDays, DueDateIncreasesBy, PenaltyCalculatedIn, PenaltyAmount, ActualAmount, ClassTypeId, RuleName, PenaltyTransactionType, PenaltyTranRuleId from transactionrule where TranRuleId=@trRuleId";
command.Parameters.Add("@trRuleId", MySqlDbType.Int32).Value = rowId;
DataTable dtResult = new DataTable();
MySqlDataAdapter mDa = new MySqlDataAdapter(command);
mDa.Fill(dtResult);
if (dtResult != null && dtResult.Rows.Count > 0)
{
status.ReturnObj = new TransactionRuleDTO();
status.ReturnObj.TranRuleId = (int)dtResult.Rows[0]["TranRuleId"];
status.ReturnObj.Active = string.Equals(dtResult.Rows[0]["Active"].ToString(), "1") ? true : false;
status.ReturnObj.TranMaster = new TransactionMasterDTO();
status.ReturnObj.TranMaster.TranMasterId = string.IsNullOrEmpty(dtResult.Rows[0]["TranMasterId"].ToString()) ? -1 : int.Parse(dtResult.Rows[0]["TranMasterId"].ToString());
status.ReturnObj.UserDTO = new UserMasterDTO();
status.ReturnObj.UserDTO.UserMasterId = string.IsNullOrEmpty(dtResult.Rows[0]["UserMasterId"].ToString()) ? -1 : int.Parse(dtResult.Rows[0]["UserMasterId"].ToString());
status.ReturnObj.Standard = new StandardDTO();
status.ReturnObj.Standard.StandardId = string.IsNullOrEmpty(dtResult.Rows[0]["StandardId"].ToString()) ? -1 : int.Parse(dtResult.Rows[0]["StandardId"].ToString());
status.ReturnObj.Section = new SectionDTO();
status.ReturnObj.Section.SectionId = string.IsNullOrEmpty(dtResult.Rows[0]["SectionId"].ToString()) ? -1 : int.Parse(dtResult.Rows[0]["SectionId"].ToString());
status.ReturnObj.FirstDueAfterDays = string.IsNullOrEmpty(dtResult.Rows[0]["FirstDueAfterDays"].ToString()) ? -1 : int.Parse(dtResult.Rows[0]["FirstDueAfterDays"].ToString());
status.ReturnObj.DueDateIncreasesBy = string.IsNullOrEmpty(dtResult.Rows[0]["DueDateIncreasesBy"].ToString()) ? -1 : int.Parse(dtResult.Rows[0]["DueDateIncreasesBy"].ToString());
status.ReturnObj.PenaltyCalculatedIn = dtResult.Rows[0]["PenaltyCalculatedIn"].ToString();
status.ReturnObj.PenaltyAmount = string.IsNullOrEmpty(dtResult.Rows[0]["PenaltyAmount"].ToString()) ? 0.0 : double.Parse(dtResult.Rows[0]["PenaltyAmount"].ToString());
status.ReturnObj.ActualAmount = string.IsNullOrEmpty(dtResult.Rows[0]["ActualAmount"].ToString()) ? 0.0 : double.Parse(dtResult.Rows[0]["ActualAmount"].ToString());
status.ReturnObj.ClassType = new ClassTypeDTO();
status.ReturnObj.ClassType.ClassTypeId = string.IsNullOrEmpty(dtResult.Rows[0]["ClassTypeId"].ToString()) ? -1 : int.Parse(dtResult.Rows[0]["ClassTypeId"].ToString());
status.ReturnObj.RuleName = dtResult.Rows[0]["RuleName"].ToString();
status.ReturnObj.PenaltyTransactionType = dtResult.Rows[0]["PenaltyTransactionType"].ToString();
status.ReturnObj.PenaltyTransactionRule = new TransactionRuleDTO();
status.ReturnObj.PenaltyTransactionRule.TranRuleId = string.IsNullOrEmpty(dtResult.Rows[0]["PenaltyTranRuleId"].ToString()) ? -1 : int.Parse(dtResult.Rows[0]["PenaltyTranRuleId"].ToString());
}
return(status);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public List <UserTransactionDTO> GetUserTransactions(int trMasterId, int userMasterId)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT UserTransactionId, TranMasterId, GraceAmountOn, GraceAmount FROM UserTransaction WHERE UserMasterId=@umId AND TranMasterId=@tranMasterId AND Active=1";
command.Parameters.Add("@umId", MySqlDbType.Int32).Value = userMasterId;
command.Parameters.Add("@tranMasterId", MySqlDbType.Int32).Value = trMasterId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
MySqlDataAdapter mDa = new MySqlDataAdapter(command);
_dtResult = new DataTable();
mDa.Fill(_dtResult);
List <UserTransactionDTO> lstUTrans = null;
if (_dtResult != null && _dtResult.Rows.Count > 0)
{
lstUTrans = new List <UserTransactionDTO>();
UserTransactionDTO uTrans = null;
foreach (DataRow dr in _dtResult.Rows)
{
uTrans = new UserTransactionDTO();
uTrans.UserTransactionId = (int)dr["UserTransactionId"];
uTrans.Transaction = new TransactionMasterDTO();
uTrans.Transaction.TranMasterId = (int)dr["TranMasterId"];
uTrans.GraceAmountIn = string.IsNullOrEmpty(dr["GraceAmountOn"].ToString()) ? "-1" : dr["GraceAmountOn"].ToString();
if (string.IsNullOrEmpty(dr["GraceAmount"].ToString()))
{
uTrans.GraceAmount = null;
}
else
{
uTrans.GraceAmount = double.Parse(dr["GraceAmount"].ToString());
}
lstUTrans.Add(uTrans);
}
}
return(lstUTrans);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public StatusDTO <TransactionLogPaymentsDTO> Insert(TransactionLogPaymentsDTO data)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "INSERT INTO transactionlogpayments(transactionlogid, paymentdate, " +
"currentamount, paymentchequeno, paymentmode, currentadjustingamount) VALUES (@transLogId, @paymentDate, " +
"@curramount, @chqNo, @mode, @currentadjusting)";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
command.Parameters.Add("@transLogId", MySqlDbType.Int32).Value = data.TransactionLog.TransactionLogId;
command.Parameters.Add("@paymentDate", MySqlDbType.DateTime).Value = data.PaymentDate.Value;
command.Parameters.Add("@curramount", MySqlDbType.Decimal).Value = data.CurrentAmount.Value;
if (string.IsNullOrEmpty(data.PaymentChequeNo))
{
command.Parameters.Add("@chqNo", MySqlDbType.VarChar).Value = DBNull.Value;
}
else
{
command.Parameters.Add("@chqNo", MySqlDbType.VarChar).Value = data.PaymentChequeNo;
}
command.Parameters.Add("@mode", MySqlDbType.VarChar).Value = data.PaymentMode;
command.Parameters.Add("@currentadjusting", MySqlDbType.Decimal).Value = data.CurrentAdjustingAmount.Value;
StatusDTO <TransactionLogPaymentsDTO> status = new StatusDTO <TransactionLogPaymentsDTO>();
if (command.ExecuteNonQuery() > 0)
{
status.IsSuccess = true;
status.IsException = false;
return(status);
}
status.IsSuccess = false;
status.IsException = false;
status.FailureReason = "Insert failed";
return(status);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public List <UserMasterDTO> GetAllActiveUsers()
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "select UserMasterId,FName,MName,LName,UserName from usermaster where Active=1";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
_dtData = new DataTable();
MySqlDataAdapter msDa = new MySqlDataAdapter(command);
msDa.Fill(_dtData);
List <UserMasterDTO> lstUserMaster = new List <UserMasterDTO>();
if (_dtData != null && _dtData.Rows.Count > 0)
{
UserMasterDTO userMasterDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
userMasterDTO = new UserMasterDTO();
if (!string.IsNullOrEmpty(dr["FName"].ToString()))
{
userMasterDTO.FName = dr["FName"].ToString();
}
if (!string.IsNullOrEmpty(dr["MName"].ToString()))
{
userMasterDTO.FName = userMasterDTO.FName + " " + dr["MName"].ToString();
}
if (!string.IsNullOrEmpty(dr["LName"].ToString()))
{
userMasterDTO.FName = userMasterDTO.FName + " " + dr["LName"].ToString();
}
userMasterDTO.FName = userMasterDTO.FName + " - " + dr["UserName"].ToString();
userMasterDTO.UserMasterId = (int)dr["UserMasterId"];
lstUserMaster.Add(userMasterDTO);
}
}
return(lstUserMaster);
}
catch (Exception exp)
{
throw exp;
}
}
}
public StatusDTO <EntitlementActionDTO> Insert(EntitlementActionDTO data)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "User_Action_Role_mapping";
command.CommandType = CommandType.StoredProcedure;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
command.Parameters.Add("@ActionId", MySqlDbType.Int32).Value = data.ActionDetails.RowId;
command.Parameters.Add("@UserRoleId", MySqlDbType.Int32).Value = data.RoleDetails.UserRoleId;
if (data.UserMaster.CreatedBy != null)
{
command.Parameters.Add("@CreatedBy", MySqlDbType.Int32).Value = data.UserMaster.CreatedBy;
}
else
{
command.Parameters.Add("@CreatedBy", MySqlDbType.Int32).Value = DBNull.Value;
}
MySqlDataReader rdr = command.ExecuteReader(CommandBehavior.CloseConnection);
_dtData = new DataTable();
_dtData.Load(rdr);
StatusDTO <EntitlementActionDTO> status = new StatusDTO <EntitlementActionDTO>();
if (rdr.RecordsAffected > 0)
{
status.IsSuccess = true;
}
else
{
status.IsSuccess = false;
}
return(status);
}
catch (Exception exp)
{
throw exp;
}
}
}
//to get Faculty Course map
public List <FacultyCourseMapDTO> GetFacultyCourseMap(int employeeId)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
dbSvc.OpenConnection();
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT FacultyCourseMapId, EmployeeId, SubjectId FROM facultycoursemap WHERE EmployeeId=@employeeId";
command.Parameters.Add("@employeeId", MySqlDbType.Int32).Value = employeeId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
MySqlDataAdapter mDa = new MySqlDataAdapter(command);
_dtData = new DataTable();
mDa.Fill(_dtData);
List <FacultyCourseMapDTO> lstFCMap = null;
if (_dtData != null && _dtData.Rows.Count > 0)
{
lstFCMap = new List <FacultyCourseMapDTO>();
FacultyCourseMapDTO fcmpDTO = null;
foreach (DataRow dr in _dtData.Rows)
{
fcmpDTO = new FacultyCourseMapDTO();
fcmpDTO.Employee = new EmployeeDetailsDTO();
fcmpDTO.Subject = new SubjectDTO();
fcmpDTO.FacultyCourseMapId = (int)dr["FacultyCourseMapId"];
fcmpDTO.Employee.EmployeeId = (int)dr["EmployeeId"];
fcmpDTO.Subject.SubjectId = (int)dr["SubjectId"];
lstFCMap.Add(fcmpDTO);
}
}
return(lstFCMap);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
