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);
}