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 <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 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 <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 <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 <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 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;
}
}
}
// 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;
}
}
}
//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;
}
}
}
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 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> 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 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 string GetIsDifferentTo(int transMasterId)
{
string returnValue = "-1";
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT IsdifferentTo FROM transactionmaster WHERE TranMasterId=@trnsMaster";
command.Parameters.Add("@trnsMaster", MySqlDbType.Int32).Value = transMasterId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
MySqlDataAdapter dataAdap = new MySqlDataAdapter(command);
_dtResult = new DataTable("TRANS_MASTER");
dataAdap.Fill(_dtResult);
if (_dtResult != null && _dtResult.Rows.Count > 0)
{
returnValue = _dtResult.Rows[0]["IsdifferentTo"].ToString();
}
return(returnValue);
}
catch (Exception exp)
{
_logger.Log(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 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 StatusDTO <TransactionMasterDTO> Select(int rowId)
{
StatusDTO <TransactionMasterDTO> status = new StatusDTO <TransactionMasterDTO>();
status.IsException = false;
status.IsSuccess = false;
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT TranMasterId, TransactionType, IsPenalty, Frequency, DayToRun, YearlyDayToRun, IsdifferentTo FROM transactionmaster WHERE TranMasterId=@trMaster";
command.Parameters.Add("@trMaster", MySqlDbType.Int32).Value = rowId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
MySqlDataAdapter dataAdap = new MySqlDataAdapter(command);
_dtResult = new DataTable("TRANS_MASTER");
dataAdap.Fill(_dtResult);
if (_dtResult != null && _dtResult.Rows.Count > 0)
{
status.IsSuccess = true;
status.ReturnObj = new TransactionMasterDTO();
status.ReturnObj.TranMasterId = rowId;
status.ReturnObj.TransactionType = _dtResult.Rows[0]["TransactionType"].ToString();
status.ReturnObj.IsPenalty = string.Equals(_dtResult.Rows[0]["IsPenalty"].ToString(), "1") ? true : false;
status.ReturnObj.IsDiffTo = _dtResult.Rows[0]["IsdifferentTo"].ToString();
}
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 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 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 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 <TransactionRuleDTO> GetStandardSectionLevelRules(int transactionMasterId, int standardId, int?sectionId = null)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT * FROM (SELECT trans.TranRuleId, std.StandardId, std.StandardName, sec.SectionId, sec.SectionName, (select TransactionName from transactionmaster where tranmasterid=@nameSelect) as TransactionName, trans.RuleName, trans.ActualAmount, trans.TranMasterId, stdsec.Active as stdSecActive, std.Active as stdActive, sec.Active as secActive, trans.IsDifferentTo FROM standardsectionmap stdsec LEFT OUTER JOIN standard std ON stdsec.StandardId=std.StandardId LEFT OUTER JOIN section sec ON stdsec.SectionId=sec.SectionId LEFT OUTER JOIN (SELECT tr.TranRuleId, tr.RuleName, tr.ActualAmount, tr.TranMasterId, tm.IsDifferentTo,tr.StandardId,tr.SectionId FROM transactionrule tr LEFT JOIN transactionmaster tm ON tr.TranMasterId=tm.TranMasterId AND tr.TranMasterId=@tranMaster AND tm.IsdifferentTo='SECTION') trans ON (trans.StandardId=stdsec.StandardId AND trans.SectionId=stdsec.SectionId) WHERE std.Active=1 AND stdsec.Active=1 AND sec.Active=1 " + (standardId > 0? "AND stdsec.StandardId=@stdId)":")") + " res";
command.Parameters.Add("@nameSelect", MySqlDbType.Int32).Value = transactionMasterId;
command.Parameters.Add("@tranMaster", MySqlDbType.Int32).Value = transactionMasterId;
if (standardId > 0)
{
command.Parameters.Add("@stdId", MySqlDbType.Int32).Value = standardId;
}
if (sectionId != null && sectionId.Value > 0)
{
command.CommandText += " WHERE res.SectionId=@secId";
command.Parameters.Add("@secId", MySqlDbType.Int32).Value = sectionId.Value;
}
command.CommandText = command.CommandText + " order by res.StandardId, res.SectionId";
command.Connection = dbSvc.GetConnection() as MySqlConnection;
MySqlDataAdapter dataAdap = new MySqlDataAdapter(command);
_dtResult = new DataTable("TRANS_RULE");
dataAdap.Fill(_dtResult);
List <TransactionRuleDTO> lstRules = null;
if (_dtResult != null && _dtResult.Rows.Count > 0)
{
lstRules = new List <TransactionRuleDTO>();
TransactionRuleDTO rule = null;
foreach (DataRow dr in _dtResult.Rows)
{
rule = new TransactionRuleDTO();
if (!string.IsNullOrEmpty(dr["TranRuleId"].ToString()))
{
rule.TranRuleId = (int)dr["TranRuleId"];
}
else
{
rule.TranRuleId = -1;
}
rule.Standard = new StandardDTO();
rule.Standard.StandardName = dr["StandardName"].ToString();
rule.Section = new SectionDTO();
rule.Section.SectionName = dr["SectionName"].ToString();
rule.TranMaster = new TransactionMasterDTO();
rule.TranMaster.TransactionName = dr["TransactionName"].ToString();
rule.RuleName = dr["RuleName"].ToString();
rule.ActualAmount = string.IsNullOrEmpty(dr["ActualAmount"].ToString()) ? 0.0 : double.Parse(dr["ActualAmount"].ToString());
lstRules.Add(rule);
}
}
return(lstRules);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public bool IsDuplicate(int trnsMasterId, int standardId, int sectionId, int classTypeId, int userMasterId, string isDiffTo, string mode, int ruleId)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT TranRuleId FROM TransactionRule WHERE TranMasterId=@tranMaster";
command.Parameters.Add("@tranMaster", MySqlDbType.Int32).Value = trnsMasterId;
if (string.Equals(isDiffTo, "CLASS-TYPE", StringComparison.OrdinalIgnoreCase))
{
command.CommandText = command.CommandText + " AND ClassTypeId=@classType";
command.Parameters.Add("@classType", MySqlDbType.Int32).Value = classTypeId;
}
else if (string.Equals(isDiffTo, "STANDARD", StringComparison.OrdinalIgnoreCase))
{
command.CommandText = command.CommandText + " AND StandardId=@standardId";
command.Parameters.Add("@standardId", MySqlDbType.Int32).Value = standardId;
}
else if (string.Equals(isDiffTo, "SECTION", StringComparison.OrdinalIgnoreCase))
{
command.CommandText = command.CommandText + " AND StandardId=@standardId AND SectionId=@sectionId";
command.Parameters.Add("@standardId", MySqlDbType.Int32).Value = standardId;
command.Parameters.Add("@sectionId", MySqlDbType.Int32).Value = sectionId;
}
else if (string.Equals(isDiffTo, "USER", StringComparison.OrdinalIgnoreCase))
{
command.CommandText = command.CommandText + " AND UserMasterId=@userMasterId";
command.Parameters.Add("@userMasterId", MySqlDbType.Int32).Value = userMasterId;
}
command.Connection = dbSvc.GetConnection() as MySqlConnection;
MySqlDataAdapter dataAdap = new MySqlDataAdapter(command);
_dtResult = new DataTable("TRANS_RULE");
dataAdap.Fill(_dtResult);
if (string.Equals(mode, "ADD", StringComparison.OrdinalIgnoreCase))
{
if (_dtResult != null && _dtResult.Rows.Count > 0)
{
return(true);
}
return(false);
}
if (_dtResult != null && _dtResult.Rows.Count == 1)
{
if (string.Equals(_dtResult.Rows[0]["TranRuleId"].ToString(), ruleId.ToString(), StringComparison.OrdinalIgnoreCase))
{
return(false);
}
return(true);
}
return(true);
}
catch (Exception exp)
{
_logger.Log(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 List <TransactionRuleDTO> GetClassTypeLevelRules(int transactionMasterId, int?classTypeRowId = null)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT * FROM (SELECT trans.TranRuleId, ct.ClassTypeId, ct.ClassTypeName, (select TransactionName from transactionmaster where tranmasterid=@nameSelect) as TransactionName, trans.RuleName, trans.ActualAmount, trans.TranMasterId, ct.Active, trans.IsDifferentTo FROM classtype ct LEFT OUTER JOIN (SELECT tr.TranRuleId, tr.RuleName, tr.ActualAmount, tr.TranMasterId, tm.IsDifferentTo, tr.ClassTypeId FROM transactionrule tr LEFT JOIN transactionmaster tm ON tr.TranMasterId=tm.TranMasterId AND tm.TranMasterId=@tranMaster AND tm.IsdifferentTo='CLASS-TYPE') trans ON trans.ClassTypeId=ct.ClassTypeId AND ct.Active=1) res";
command.Parameters.Add("@nameSelect", MySqlDbType.Int32).Value = transactionMasterId;
command.Parameters.Add("@tranMaster", MySqlDbType.Int32).Value = transactionMasterId;
if (classTypeRowId != null && classTypeRowId.Value > 0)
{
command.CommandText += " WHERE res.ClassTypeId=@classType";
command.Parameters.Add("@classType", MySqlDbType.Int32).Value = classTypeRowId.Value;
}
command.Connection = dbSvc.GetConnection() as MySqlConnection;
MySqlDataAdapter dataAdap = new MySqlDataAdapter(command);
_dtResult = new DataTable("TRANS_RULE");
dataAdap.Fill(_dtResult);
List <TransactionRuleDTO> lstRules = null;
if (_dtResult != null && _dtResult.Rows.Count > 0)
{
lstRules = new List <TransactionRuleDTO>();
TransactionRuleDTO rule = null;
foreach (DataRow dr in _dtResult.Rows)
{
rule = new TransactionRuleDTO();
if (!string.IsNullOrEmpty(dr["TranRuleId"].ToString()))
{
rule.TranRuleId = (int)dr["TranRuleId"];
}
else
{
rule.TranRuleId = -1;
}
rule.ClassType = new ClassTypeDTO();
rule.ClassType.ClassTypeId = (int)dr["ClassTypeId"];
rule.ClassType.ClassTypeName = dr["ClassTypeName"].ToString();
rule.TranMaster = new TransactionMasterDTO();
rule.TranMaster.TransactionName = dr["TransactionName"].ToString();
rule.RuleName = dr["RuleName"].ToString();
rule.ActualAmount = string.IsNullOrEmpty(dr["ActualAmount"].ToString()) ? 0.0 : double.Parse(dr["ActualAmount"].ToString());
lstRules.Add(rule);
}
}
return(lstRules);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
public List <TransactionRuleDTO> GetUserLevelRules(int transactionMasterId, int userRowId)
{
using (IDbSvc dbSvc = new DbSvc(_configSvc))
{
try
{
MySqlCommand command = new MySqlCommand();
command.CommandText = "SELECT * FROM (SELECT tr.TranRuleId, u.UserMasterId, u.FName, u.MName, u.LName, (select TransactionName from transactionmaster where tranmasterid=@nameSelect) as TransactionName, tr.RuleName, tr.ActualAmount, u.Active, tr.TranMasterId, tm.IsDifferentTo FROM usermaster u LEFT OUTER JOIN transactionrule tr ON u.UserMasterId=tr.UserMasterId LEFT OUTER JOIN transactionmaster tm ON tr.TranMasterId=tm.TranMasterId) res WHERE (res.TranMasterId=@tranMaster OR res.TranMasterId IS NULL) AND res.UserMasterId=@userMaster AND (res.IsDifferentTo='USER' OR res.IsDifferentTo IS NULL)";
command.Parameters.Add("@nameSelect", MySqlDbType.Int32).Value = transactionMasterId;
command.Parameters.Add("@tranMaster", MySqlDbType.Int32).Value = transactionMasterId;
command.Parameters.Add("@userMaster", MySqlDbType.Int32).Value = userRowId;
command.Connection = dbSvc.GetConnection() as MySqlConnection;
MySqlDataAdapter dataAdap = new MySqlDataAdapter(command);
_dtResult = new DataTable("TRANS_RULE");
dataAdap.Fill(_dtResult);
List <TransactionRuleDTO> lstRules = null;
if (_dtResult != null && _dtResult.Rows.Count > 0)
{
lstRules = new List <TransactionRuleDTO>();
TransactionRuleDTO rule = null;
foreach (DataRow dr in _dtResult.Rows)
{
rule = new TransactionRuleDTO();
if (!string.IsNullOrEmpty(dr["TranRuleId"].ToString()))
{
rule.TranRuleId = (int)dr["TranRuleId"];
}
else
{
rule.TranRuleId = -1;
}
rule.UserDTO = new UserMasterDTO();
rule.UserDTO.UserMasterId = (int)dr["UserMasterId"];
rule.UserDTO.FName = dr["FName"].ToString();
rule.UserDTO.MName = dr["MName"].ToString();
rule.UserDTO.LName = dr["LName"].ToString();
rule.TranMaster = new TransactionMasterDTO();
rule.TranMaster.TransactionName = dr["TransactionName"].ToString();
rule.RuleName = dr["RuleName"].ToString();
rule.ActualAmount = string.IsNullOrEmpty(dr["ActualAmount"].ToString()) ? 0.0 : double.Parse(dr["ActualAmount"].ToString());
lstRules.Add(rule);
}
}
return(lstRules);
}
catch (Exception exp)
{
_logger.Log(exp);
throw exp;
}
}
}
