public List <MVCModels.ExpenseEntity> GetExpenseEntity(string companyCode) { List <MVCModels.ExpenseEntity> lstExpenseEntity = new List <MVCModels.ExpenseEntity>(); try { SqlCommand command = new SqlCommand(SP_HDGETEXPENSEENTITY); command.CommandType = CommandType.StoredProcedure; _objSPData.AddParamToSqlCommand(command, "@CompanyCode", ParameterDirection.Input, SqlDbType.VarChar, 30, companyCode); _objData.OpenConnection(); using (sqlDataReader = _objData.ExecuteReader(command)) { while (sqlDataReader.Read()) { MVCModels.ExpenseEntity _objExpenseEntity = new MVCModels.ExpenseEntity(); _objExpenseEntity.Expense_Entity_Code = sqlDataReader["Expense_Entity_Code"].ToString(); _objExpenseEntity.Expense_Entity_Name = sqlDataReader["Expense_Entity_Name"].ToString(); lstExpenseEntity.Add(_objExpenseEntity); } } } catch (Exception ex) { DataControl.Impl.ExceptionHandler.WriteLog(ex: ex, dic: null); throw ex; } finally { _objData.CloseConnection(); } return(lstExpenseEntity); }
public string GetConfitValueForSize(string companyCode) { string fileDetails = ""; try { SqlCommand command = new SqlCommand(SP_HD_GETSTOSCONFIGVALUEFORSIZE); command.CommandType = CommandType.StoredProcedure; command.CommandTimeout = 400; AddParamToSqlCommand(command, "@companyCode", ParameterDirection.Input, SqlDbType.VarChar, 30, companyCode); _objData.OpenConnection(companyCode); using (SqlDataReader dataReader = _objData.ExecuteReader(command)) if (dataReader.HasRows) { while (dataReader.Read()) { fileDetails = dataReader["Result"].ToString(); } } } catch (Exception ex) { throw ex; } return(fileDetails); }
public string GetCpLastEnteredDate(string usercode, string cpcode, DateTime tp_date, string companyCode) { _objData = new Data(); string cpstatus = "no"; try { SqlCommand command = new SqlCommand(HD_GET_GETCPENTEREDDATE); command.CommandType = CommandType.StoredProcedure; command.CommandTimeout = 400; AddParamToSqlCommand(command, "@companyCode", ParameterDirection.Input, SqlDbType.VarChar, 30, companyCode); AddParamToSqlCommand(command, "@cp_code", ParameterDirection.Input, SqlDbType.VarChar, 50, cpcode); AddParamToSqlCommand(command, "@usercode", ParameterDirection.Input, SqlDbType.VarChar, 30, usercode); AddParamToSqlCommand(command, "@Tp_Date", ParameterDirection.Input, SqlDbType.Date, 30, tp_date); _objData.OpenConnection(companyCode); using (SqlDataReader dataReader = _objData.ExecuteReader(command)) if (dataReader.HasRows) { while (dataReader.Read()) { cpstatus = dataReader["status"].ToString(); } } } catch (Exception ex) { throw ex; } finally { _objData.CloseConnection(); } return(cpstatus); }
public List <MVCModels.DivisionModel> GetDivisions(string companyCode) { List <MVCModels.DivisionModel> lstDivisions = new List <MVCModels.DivisionModel>(); try { SqlCommand command = new SqlCommand(SP_HDGETALLDIVISIONS); command.CommandType = CommandType.StoredProcedure; _objSPData.AddParamToSqlCommand(command, "@CompanyCode", ParameterDirection.Input, SqlDbType.VarChar, 30, companyCode); _objData.OpenConnection(); using (sqlDataReader = _objData.ExecuteReader(command)) { while (sqlDataReader.Read()) { MVCModels.DivisionModel _objDivision = new MVCModels.DivisionModel(); _objDivision.Division_Code = sqlDataReader["Division_Code"].ToString(); _objDivision.Division_Name = sqlDataReader["Division_Name"].ToString(); _objDivision.Lineofbussiness = sqlDataReader["Lineofbussiness"].ToString(); _objDivision.Record_Status = sqlDataReader["Record_Status"].ToString(); _objDivision.divisionline = sqlDataReader["Line_Of_Business"].ToString(); lstDivisions.Add(_objDivision); } } } catch (Exception ex) { Impl.ExceptionHandler.WriteLog(ex: ex, dic: null); throw ex; } finally { _objData.CloseConnection(); } return(lstDivisions); }
public List <KYDModel> DuplicateValidationOnKYD(string company_Code, string key_Column, IEnumerable <KYDModel> IlstKYDModel) { List <KYDModel> lstKYD = new List <KYDModel>(); try { _objSPData = new SPData(); _objData = new Data(); string cmdText = SPHD_KYDCHECKDUPLICATEENTRY; SqlCommand command = new SqlCommand(cmdText); command.CommandType = CommandType.StoredProcedure; _objSPData.AddParamToSqlCommand(command, "@Company_Code", ParameterDirection.Input, SqlDbType.VarChar, 30, company_Code); _objSPData.AddParamToSqlCommand(command, "@Key_Column", ParameterDirection.Input, SqlDbType.VarChar, 20, key_Column); if (((List <KYDModel>)IlstKYDModel).Count == 0) { _objSPData.AddParamToSqlCommandWithTypeName(command, "@KYD_TEMP", ParameterDirection.Input, SqlDbType.Structured, null, KYD_TYPE_NAME); } else { _objSPData.AddParamToSqlCommandWithTypeName(command, "@KYD_TEMP", ParameterDirection.Input, SqlDbType.Structured, new KYDEnumurator(IlstKYDModel), KYD_TYPE_NAME); } _objData.OpenConnection(company_Code); // Execuete the data reader. using (SqlDataReader dataReader = _objData.ExecuteReader(command)) { while (dataReader.Read()) { KYDModel KYD = new KYDModel(); KYD.Doctor_Code = dataReader["Doctor_Code"].ToString(); KYD.Doctor_Region_Code = dataReader["Doctor_Region_Code"].ToString(); KYD.Registration_No = dataReader["Registration_No"].ToString(); KYD.Mobile = dataReader["Mobile"].ToString(); KYD.Email_Id = dataReader["Email"].ToString(); lstKYD.Add(KYD); } } _objData.CloseConnection(); // returns the list. return(lstKYD); } catch { _objData.CloseConnection(); throw; } }
public List <OrderProductDetails> GetProduct() { Data _objData = new Data(); try { // Creates Instance SPData _objSPData = new SPData(); List <OrderProductDetails> lstdcrProductModel = new List <OrderProductDetails>(); string cmdText = "SP_HD_V4_GetSaleProducts"; // Set Command object. SqlCommand command = new SqlCommand(cmdText); command.CommandType = CommandType.StoredProcedure; // Add the parameters to command object. _objSPData.AddParamToSqlCommand(command, "@Company_Code", ParameterDirection.Input, SqlDbType.VarChar, 30, objOrder.orderInputs.Company_Code); _objSPData.AddParamToSqlCommand(command, "@User_Code", ParameterDirection.Input, SqlDbType.VarChar, 30, ""); _objSPData.AddParamToSqlCommand(command, "@Region_Code", ParameterDirection.Input, SqlDbType.VarChar, 30, objOrder.orderInputs.Region_Code); // Opens the connection. _objData.OpenConnection(objOrder.orderInputs.Company_Code); // Execuete the data reader. using (SqlDataReader dataReader = _objData.ExecuteReader(command)) { // Converts the DataReader to User Products list. lstdcrProductModel = GetProductList(dataReader); } // returns the list. return(lstdcrProductModel); } finally { _objData.CloseConnection(); } }
public List <MVCModels.HiDoctor_Master.CustomerModel> GetCustomerDetails(string companyCode, string matchingString, string regionCode, string customerEntity) { List <MVCModels.HiDoctor_Master.CustomerModel> lstCust = new List <MVCModels.HiDoctor_Master.CustomerModel>(); try { _objData.OpenConnection(companyCode); using (IDataReader reader = _objData.ExecuteReader(SP_mhdGetEntityWiseCustomerList + " '" + companyCode + "','" + matchingString + "','" + regionCode + "','" + customerEntity + "'")) { lstCust = DataReaderMapToList <MVCModels.HiDoctor_Master.CustomerModel>(reader); } } catch { throw; } finally { _objData.CloseConnection(); } return(lstCust); }
public IEnumerable <MVCModels.AddUserLeave> AddNewLeaveType(string companyCode, string UserName, string NewLeaveType, string userTypeCode, DataTable dtUserCodes, string Effective_From, string Effective_To) { List <MVCModels.AddUserLeave> objAddLeave = new List <MVCModels.AddUserLeave>(); SqlConnection objSqlConnection = new SqlConnection(); Effective_From = Effective_From.Split('/')[2] + '-' + Effective_From.Split('/')[1] + '-' + Effective_From.Split('/')[0]; Effective_To = Effective_To.Split('/')[2] + '-' + Effective_To.Split('/')[1] + '-' + Effective_To.Split('/')[0]; _objSPData = new SPData(); _objData = new Data(); //int rowsAffected = 0; try { SqlCommand command = new SqlCommand(Usp_HD_AddNewLeaveType); command.CommandType = CommandType.StoredProcedure; SqlDataReader sqlReader; _objSPData.AddParamToSqlCommand(command, "@companyCode", ParameterDirection.Input, SqlDbType.VarChar, 30, companyCode); _objSPData.AddParamToSqlCommand(command, "@UserName", ParameterDirection.Input, SqlDbType.VarChar, 30, UserName); _objSPData.AddParamToSqlCommand(command, "@NewLeaveType", ParameterDirection.Input, SqlDbType.VarChar, 30, NewLeaveType); _objSPData.AddParamToSqlCommand(command, "@userTypeCode", ParameterDirection.Input, SqlDbType.VarChar, 30, userTypeCode); _objSPData.AddParamToSqlCommand(command, "@Effective_From", ParameterDirection.Input, SqlDbType.VarChar, 10, Effective_From); _objSPData.AddParamToSqlCommand(command, "@Effective_To", ParameterDirection.Input, SqlDbType.VarChar, 10, Effective_To); if (dtUserCodes.Rows.Count == 0) { _objSPData.AddParamToSqlCommandWithTypeName(command, "@TVP_LeaveUserCodes", ParameterDirection.Input, SqlDbType.Structured, null, "TVP_LeaveUserCodes"); } else { _objSPData.AddParamToSqlCommandWithTypeName(command, "@TVP_LeaveUserCodes", ParameterDirection.Input, SqlDbType.Structured, dtUserCodes, "TVP_LeaveUserCodes"); } //SqlParameter returnvalue = new SqlParameter("@Result", SqlDbType.VarChar); //returnvalue.Direction = ParameterDirection.Output; //returnvalue.Size = 500; //command.Parameters.Add(returnvalue); _objData.OpenConnection(companyCode); command.Connection = objSqlConnection; using (sqlReader = _objData.ExecuteReader(command)) { while (sqlReader.Read()) { MVCModels.AddUserLeave lstAddLeave = new MVCModels.AddUserLeave(); lstAddLeave.User_Code = sqlReader["User_Code"].ToString(); lstAddLeave.User_Name = sqlReader["User_Name"].ToString(); lstAddLeave.Leave_Type_Code = sqlReader["Leave_Type_Code"].ToString(); lstAddLeave.Leave_Type_Name = sqlReader["Leave_Type_Name"].ToString(); lstAddLeave.HiDoctor_Start_Date = sqlReader["HiDoctor_Start_Date"].ToString(); lstAddLeave.Effective_From = sqlReader["Effective_From"].ToString(); lstAddLeave.Effective_To = sqlReader["Effective_To"].ToString(); lstAddLeave.Remarks = sqlReader["Remarks"].ToString(); objAddLeave.Add(lstAddLeave); } } } catch (Exception ex) { throw ex; } finally { _objData.CloseConnection(); } return(objAddLeave); }
public IEnumerable <MVCModels.UserLeaveDetails> GetUserLeaveFilterDetails(string companyCode, DataTable dtLeaveTypeCodes, DataTable dtUserCodes, string Year) { List <MVCModels.UserLeaveDetails> obLeaveTypeDet = new List <MVCModels.UserLeaveDetails>(); SqlConnection objSqlConnection = new SqlConnection(); try { _objSPData = new SPData(); _objData = new Data(); SqlCommand command = new SqlCommand(Usp_hd_GetLeaveTypeFilterDetails); command.CommandType = CommandType.StoredProcedure; SqlDataReader sqlReader; _objSPData.AddParamToSqlCommand(command, "@CompanyCode", ParameterDirection.Input, SqlDbType.VarChar, 30, companyCode); _objSPData.AddParamToSqlCommand(command, "@Year", ParameterDirection.Input, SqlDbType.VarChar, 4, Year); if (dtUserCodes.Rows.Count == 0) { _objSPData.AddParamToSqlCommandWithTypeName(command, "@TVP_LeaveUserCodes", ParameterDirection.Input, SqlDbType.Structured, null, "TVP_LeaveUserCodes"); } else { _objSPData.AddParamToSqlCommandWithTypeName(command, "@TVP_LeaveUserCodes", ParameterDirection.Input, SqlDbType.Structured, dtUserCodes, "TVP_LeaveUserCodes"); } if (dtLeaveTypeCodes.Rows.Count == 0) { _objSPData.AddParamToSqlCommandWithTypeName(command, "@TVP_LeaveTypeCode", ParameterDirection.Input, SqlDbType.Structured, null, "TVP_LeaveTypeCode"); } else { _objSPData.AddParamToSqlCommandWithTypeName(command, "@TVP_LeaveTypeCode", ParameterDirection.Input, SqlDbType.Structured, dtLeaveTypeCodes, "TVP_LeaveTypeCode"); } _objData.OpenConnection(companyCode); command.Connection = objSqlConnection; using (sqlReader = _objData.ExecuteReader(command)) { while (sqlReader.Read()) { MVCModels.UserLeaveDetails lstUserLeave = new MVCModels.UserLeaveDetails(); lstUserLeave.User_Name = sqlReader["User_Name"].ToString(); lstUserLeave.User_Code = sqlReader["User_Code"].ToString(); lstUserLeave.Leave_Type_Name = sqlReader["Leave_Type_Name"].ToString(); lstUserLeave.Leave_Type_Code = sqlReader["Leave_Type_Code"].ToString(); lstUserLeave.Balance_CF = Convert.ToDecimal(sqlReader["Balance_CF"]); lstUserLeave.Leave_Eligible = Convert.ToDecimal(sqlReader["Leave_Eligible"]); lstUserLeave.Opening_Balance = Convert.ToDecimal(sqlReader["Opening_Balance"]); lstUserLeave.Leave_Taken = Convert.ToDecimal(sqlReader["Leave_Taken"]); lstUserLeave.Leave_Balance = Convert.ToDecimal(sqlReader["Leave_Balance"]); lstUserLeave.Lapsed = Convert.ToDecimal(sqlReader["Lapsed"]); lstUserLeave.Effective_From = sqlReader["Effective_From"].ToString(); lstUserLeave.Effective_To = sqlReader["Effective_To"].ToString(); lstUserLeave.User_Leave_Code = sqlReader["User_Leave_Code"].ToString(); obLeaveTypeDet.Add(lstUserLeave); } } } catch (Exception ex) { Dictionary <string, string> dicObj = new Dictionary <string, string>(); Impl.ExceptionHandler.WriteLog(ex: ex, dic: dicObj); return(null); } finally { _objData.CloseConnection(); } return(obLeaveTypeDet); }