public static IList <KeyValueVM> Get_Years(int UserId)
{
List <KeyValueVM> GetYears = new List <KeyValueVM>();
try
{
using (DBSqlCommand cmd = new DBSqlCommand(CommandType.StoredProcedure))
{
//cmd.AddParameters(UserId, CommonConstants.UserId, SqlDbType.Int);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Get_EmpLeaves);
while (ireader.Read())
{
var Years = new KeyValueVM
{
value = ireader.GetString(CommonColumns.Year),
key = ireader.GetInt32(CommonColumns.Year),
};
GetYears.Add(Years);
}
}
}
catch (Exception ex)
{
throw new ArgumentException("Exception in Get_Genders. Exception :" + ex.Message);
}
return(GetYears);
}
public static bool SaveProfileDetails(MyProfileMDL user, int businessId, int userId)
{
try
{
using (DBSqlCommand cmd = new DBSqlCommand())
{
cmd.AddParameters(userId, CommonConstants.UserId, System.Data.SqlDbType.Int);
cmd.AddParameters(businessId, CommonConstants.BusinessID, System.Data.SqlDbType.Int);
cmd.AddParameters(user.UserFirstName, CommonConstants.FirstName, System.Data.SqlDbType.VarChar);
cmd.AddParameters(user.UserLastName, CommonConstants.LastName, System.Data.SqlDbType.VarChar);
cmd.AddParameters(user.BusinessName, CommonConstants.BusinessName, System.Data.SqlDbType.VarChar);
cmd.AddParameters(user.Email, CommonConstants.Email, System.Data.SqlDbType.VarChar);
cmd.AddParameters(user.Fax, CommonConstants.BusinessFax, System.Data.SqlDbType.VarChar);
cmd.AddParameters(user.AddressLine1, CommonConstants.BusinessAddress, System.Data.SqlDbType.VarChar);
cmd.AddParameters(user.City, CommonConstants.City, System.Data.SqlDbType.VarChar);
cmd.AddParameters(1, CommonConstants.CityId, System.Data.SqlDbType.Int);
cmd.AddParameters(user.StateId, CommonConstants.StateId, System.Data.SqlDbType.Int);
cmd.AddParameters(user.CountryId, CommonConstants.CountryId, System.Data.SqlDbType.Int);
//cmd.AddParameters(user.LogMeOut, "@LogMeOut", System.Data.SqlDbType.Int);
cmd.AddParameters(user.LogMeOutId, "@LogMeOutId", System.Data.SqlDbType.Int);
cmd.AddParameters(user.Licences, "@Licences", System.Data.SqlDbType.Int);
cmd.ExecuteNonQuery(SqlProcedures.Update_MyAccountDetails);
return(true);
}
}
catch (Exception ex)
{
throw new ArgumentException("Exception in SaveProfileDetails. Exception :" + ex.Message);
}
}
public static List <ManageLeaveMDL> GetManageLeaveDetails(int businessId)
{
var manageleaveFields = new List <ManageLeaveMDL>();
using (var cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(businessId, CommonColumns.BusinessID, System.Data.SqlDbType.Int);
var ireader = cmd.ExecuteDataReader(SqlProcedures.usp_GetBusinessLeaveDetails);
while (ireader.Read())
{
var field = new ManageLeaveMDL
{
Leavevalue = ireader[CommonColumns.Leavevalue] != System.DBNull.Value ? ireader.GetFormatDecimal(ireader.GetOrdinal(CommonColumns.Leavevalue)) : 0
};
manageleaveFields.Add(field);
}
return(manageleaveFields);
}
catch (Exception ex)
{
return(null);
}
}
}
public static Company GetCompanyName(int BusinessId, int UserId)
{
using (var cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(UserId, CommonConstants.UserId, SqlDbType.Int);
var CompanyDetails = new Company();
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Get_CompanyName);
while (ireader.Read())
{
var GetCompany = new Company
{
BusinessId = ireader.GetInt32(CommonColumns.BusinessID),
BusinessName = ireader.GetString(CommonColumns.BusinessName),
MyprofileBusinessName = ireader.GetString(CommonColumns.MyprofileBusinessName),
ImagePath = ireader.GetString(CommonColumns.ImagePath),
};
CompanyDetails = GetCompany;
}
return(CompanyDetails);
}
catch (Exception ex)
{
return(null);
}
}
}
public static IList <KeyValueVM> Get_Locations()
{
List <KeyValueVM> GetEmployees = new List <KeyValueVM>();
try
{
using (DBSqlCommand cmd = new DBSqlCommand(CommandType.StoredProcedure))
{
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Get_Locations);
while (ireader.Read())
{
var Employees = new KeyValueVM
{
value = ireader.GetString(CommonColumns.LocationName),
key = ireader.GetInt32(CommonColumns.LocationId),
};
GetEmployees.Add(Employees);
}
}
}
catch (Exception ex)
{
throw new ArgumentException("Exception in Get_Genders. Exception :" + ex.Message);
}
return(GetEmployees);
}
public static IList <KeyValueVM> GetCompanyName(int BusinessId, int UserId)
{
List <KeyValueVM> GetCompany = new List <KeyValueVM>();
try
{
using (DBSqlCommand cmd = new DBSqlCommand(CommandType.StoredProcedure))
{
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(UserId, CommonConstants.UserId, SqlDbType.Int);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Get_CompanyName);
while (ireader.Read())
{
var Employees = new KeyValueVM
{
value = ireader.GetString(CommonColumns.BusinessName),
key = ireader.GetInt32(CommonColumns.BusinessID),
};
GetCompany.Add(Employees);
}
}
}
catch (Exception ex)
{
return(null);
}
return(GetCompany);
}
public static DashboardChart PurchasesByYear(int BusinessId, int Year)
{
using (var cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(Year, CommonConstants.Year, SqlDbType.SmallInt);
var PurchasesByYear = new DashboardChart();
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_PurchaseByYear);
while (ireader.Read())
{
var PurchasesByYearDet = new DashboardChart
{
TotalPurchase = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalPurchase)),
NumberOfPurchase = ireader.GetInt32(CommonColumns.PurchaseCount),
AveragePurchase = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.AveragePurchase)),
LargestPurchase = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.LargestPurchase))
};
PurchasesByYear = PurchasesByYearDet;
}
return(PurchasesByYear);
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in PurchasesByYear. Exception :" + ex.Message);
}
}
}
public static List <DashboardChart> DashboardExpensesAccounts(int BusinessId, int Year)
{
var ExpensesAccounts = new List <DashboardChart>();
using (var cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(Year, CommonConstants.Year, SqlDbType.SmallInt);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_DashboardExpenseAccounts);
while (ireader.Read())
{
var ExpensesAccountsDet = new DashboardChart
{
SNo = ireader.GetInt32(CommonColumns.Sno),
AccountName = ireader.GetString(CommonColumns.AccountName),
Amount = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.Amount))
};
ExpensesAccounts.Add(ExpensesAccountsDet);
}
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in DashboardExpensesAccounts. Exception :" + ex.Message);
}
}
return(ExpensesAccounts);
}
public static IList <Modules> GetPrivileges()
{
using (var cmd = new DBSqlCommand())
{
try
{
var ireader = cmd.ExecuteDataReader(SqlProcedures.Get_ModulesForGroupsAndRoles);
var listItems = new List <Modules>();
while (ireader.Read())
{
var item = new Modules
{
ModuleId = ireader.GetInt16(CommonColumns.ModuleId),
Module = ireader.GetString(CommonColumns.Module)
};
listItems.Add(item);
}
return(listItems);
}
catch (Exception ex)
{
return(null);
}
}
}
public static List <KeyValueVM> Rpt_BindYears()
{
List <KeyValueVM> YearsList = new List <KeyValueVM>();
try
{
using (DBSqlCommand cmd = new DBSqlCommand(CommandType.StoredProcedure))
{
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_BindYears);
while (ireader.Read())
{
var Year = new KeyValueVM
{
value = ireader.GetString(CommonColumns.Year),
key = Convert.ToInt32(ireader.GetString(CommonColumns.Year))
};
YearsList.Add(Year);
}
}
}
catch (Exception ex)
{
throw new ArgumentException("Exception in Rpt_BindYears. Exception :" + ex.Message);
}
return(YearsList);
}
public static List <ViewApplyLeave> Get_EmpLeaveDetailsList(ViewApplyLeave Data, int RoleId, int UserEmplyeeId)
{
List <ViewApplyLeave> GetEmpVacations = new List <ViewApplyLeave>();
//Data.Year = 2018;
using (DBSqlCommand cmd = new DBSqlCommand(CommandType.StoredProcedure))
{
try
{
cmd.AddParameters(Data.Year, CommonConstants.Year, SqlDbType.Int);
//cmd.AddParameters(Data.UserEmployeeId, CommonConstants.UserEmployeeId, SqlDbType.Int);
if (RoleId == 6)
{
cmd.AddParameters(UserEmplyeeId, CommonConstants.UserEmployeeId, SqlDbType.Int);
}
else
{
cmd.AddParameters(Data.UserEmployeeId, CommonConstants.UserEmployeeId, SqlDbType.Int);
}
cmd.AddParameters(Data.BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.GetEmployeeLeaveDeatails);
while (ireader.Read())
{
var EmpLeave = new ViewApplyLeave
{
EmployeeLeaveId = ireader.GetInt32(CommonColumns.EmployeeLeaveId),
UserEmployeeId = ireader.GetInt64(CommonColumns.UserEmployeeId),
BusinessId = ireader.GetInt32(CommonColumns.BusinessID),
EmployeeName = ireader.GetString(CommonColumns.EmployeeName),
LeaveStartDate = ireader.GetDateTime(CommonColumns.LeaveStartDate).ToShortDateString(),
LeaveEndDate = ireader.GetDateTime(CommonColumns.LeaveEndDate).ToShortDateString(),
NoOfLeaveDays = ireader.GetInt32(CommonColumns.NoOfLeaveDays),
LeaveType = ireader.GetString(CommonColumns.LeaveType),
LeaveReason = ireader.GetString(CommonColumns.LeaveReason),
Description = ireader.GetString(CommonColumns.Description),
//FileStatus = ireader.GetString(CommonColumns.FileStatus),
LeaveStatus = ireader.GetString(CommonColumns.LeaveStatus),
LeaveStatusId = ireader.GetInt32(CommonColumns.LeaveStatusId),
};
GetEmpVacations.Add(EmpLeave);
}
}
catch (Exception ex)
{
throw new ArgumentException("Exception in Get_EmpLeaveDetailsList. Exception :" + ex.Message);
}
}
return(GetEmpVacations);
}
public LoggedUser IsValidUser(string userName, string password)
{
//string encrptPWD = password.GetSHA1();
LoggedUser logedUser = null;// new LoggedUser() { IsActive = true, UserId = 2, BusinessId = 2, EmailId = "*****@*****.**", RoleName = "", RoleId = 1 };
//LoggedUser logedUser = new LoggedUser() { IsActive = true, UserId = 1, BusinessId = 1, EmailId = "*****@*****.**", RoleName = "Super Admin", RoleId = 2 };
using (DBSqlCommand cmd = new DBSqlCommand())
{
cmd.AddParameters(userName, CommonConstants.UserName, System.Data.SqlDbType.VarChar);
cmd.AddParameters(password, "@Password", System.Data.SqlDbType.VarChar);
IDataReader reader = cmd.ExecuteDataReader(SqlProcedures.User_Verification);
while (reader.Read())
{
logedUser = new LoggedUser();
logedUser.BusinessId = reader[CommonColumns.BusinessID] == null ? 0 : Convert.ToInt32(reader[CommonColumns.BusinessID]);
logedUser.UserId = reader[CommonColumns.UserId] == null ? 0 : Convert.ToInt32(reader[CommonColumns.UserId]);
logedUser.UserName = reader[CommonColumns.UserName].ToString();
logedUser.RoleId = reader[CommonColumns.RoleId] == null ? 0 : Convert.ToInt32(reader[CommonColumns.RoleId]);
logedUser.RoleName = reader[CommonColumns.RoleName].ToString();
logedUser.EmailId = userName;
logedUser.IsActive = (bool)reader[CommonColumns.UserIsActive];
//logedUser.BusinessStatusId = reader.GetInt32("BusinessStatusId");
logedUser.BusinessStatusId = Convert.ToInt32(reader[CommonColumns.BusinessStatuId]);
logedUser.BusinessStatus = reader[CommonColumns.BusinessStatus].ToString();
logedUser.Country = reader[CommonColumns.Country].ToString();
logedUser.CountryId = Convert.ToInt32(reader[CommonColumns.CountryId]);
logedUser.GroupId = Convert.ToInt32(reader[CommonColumns.GroupId]);
logedUser.StateId = Convert.ToInt32(reader[CommonColumns.StateId]);
logedUser.State = reader[CommonColumns.State].ToString();
logedUser.CustomersRename = reader[CommonColumns.CustomersRename].ToString();
logedUser.LogMeOutId = Convert.ToInt32(reader[CommonColumns.LogMeOutId]);
logedUser.RenewalDate = reader[CommonColumns.RenewalDate].ToString();
//logedUser.TimeLimit = Convert.ToInt32(reader[CommonColumns.TimeLimit]);
logedUser.AccountClosingDate = reader[CommonColumns.AccountClosingDate].ToString();
if (logedUser.RoleId == 6)
{
logedUser.UserSessionEmployeeId = reader[CommonColumns.UserSessionEmployeeId] == null ? 0 : Convert.ToInt32(reader[CommonColumns.UserSessionEmployeeId]);
}
return(logedUser);
}
}
//if (logedUser != null)
//{
// using (DBSqlCommand cmd = new DBSqlCommand())
// {
// var reader = cmd.ExecuteDataReader("");
// }
//}
return(logedUser);// as LoggedUser;
}
public void VerifyUser(string userId)
{
using (DBSqlCommand cmd = new DBSqlCommand(CommandType.StoredProcedure))
{
try
{
cmd.AddParameters(userId, CommonConstants.UserId, SqlDbType.Int);
cmd.ExecuteNonQuery(SqlProcedures.BusinessRegistrationVerification);
}
catch (Exception ex)
{
throw ex;
}
}
}
public static bool SaveGroupScreenPrivileges(Modules Modules)
{
using (var cmd = new DBSqlCommand())
{
cmd.AddParameters(Modules.LoginUserId, CommonConstants.LoginUserId, SqlDbType.Int);
cmd.AddParameters(Modules.BusinessID, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(Modules.GroupId, CommonConstants.GroupNameId, SqlDbType.Int);
cmd.AddParameters(Modules.ScreenId, CommonConstants.ScreenId, SqlDbType.Int);
cmd.AddParameters(Modules.PrivilegeIdList, CommonConstants.PrivilegeIdList, SqlDbType.VarChar);
cmd.AddParameters(Modules.IsChk, CommonConstants.IsChk, SqlDbType.Int);
cmd.ExecuteNonQuery(SqlProcedures.Insert_Update_GroupScreenPrivileges);
}
return(true);
}
public static CommonMessages SaveEmployeeLeave(LMS Data)
{
CommonMessages MessagesObj = new CommonMessages();
try
{
using (var Cmd = new DBSqlCommand())
{
Cmd.AddParameters(Data.UserEmployeeId, CommonConstants.UserEmployeeId, SqlDbType.Int);
Cmd.AddParameters(Data.FromDate, CommonConstants.FromDate, SqlDbType.DateTime);
Cmd.AddParameters(Data.ToDate, CommonConstants.ToDate, SqlDbType.DateTime);
Cmd.AddParameters(Data.LeaveTypeId, CommonConstants.LeaveTypeId, SqlDbType.Int);
Cmd.AddParameters(Data.LeaveReasonId, CommonConstants.LeaveReasonId, SqlDbType.Int);
Cmd.AddParameters(Data.BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
Cmd.AddParameters(Data.CurrentLeaveBalance, CommonConstants.CurrentLeaveBalance, SqlDbType.Int);
Cmd.AddParameters(Data.EffectiveLeave, CommonConstants.EffectiveLeave, SqlDbType.Int);
Cmd.AddParameters(Data.UserId, CommonConstants.UserId, SqlDbType.Int);
Cmd.AddParameters(Data.Description, CommonConstants.Description, SqlDbType.NVarChar);
Cmd.AddParameters(Data.FilePath, CommonConstants.FilePath, SqlDbType.NVarChar);
//Result = (int)Cmd.ExecuteScalar(SqlProcedures.Insert_EmployeeLeaves);
IDataReader ireader = Cmd.ExecuteDataReader(SqlProcedures.Insert_EmployeeLeaves);
while (ireader.Read())
{
var EmpDet = new CommonMessages
{
Message = ireader.GetString(CommonColumns.ErrorMessage),
Result = ireader.GetInt32(CommonColumns.EmployeeLeaveId),
ErrorSeverity = ireader.GetString(CommonColumns.ErrorSeverity),
ErrorState = ireader.GetString(CommonColumns.ErrorState)
};
MessagesObj = EmpDet;
}
}
return(MessagesObj);
}
catch (Exception Ex)
{
MessagesObj.Result = 0;
MessagesObj.Message = Ex.Message;
MessagesObj.ErrorSeverity = Ex.Message;
MessagesObj.ErrorState = Ex.Message;
return(MessagesObj);
}
}
public static void UpdateSessionDetails(int userId, int businessId, string sessionId)
{
try
{
using (DBSqlCommand cmd = new DBSqlCommand())
{
cmd.AddParameters(userId, CommonConstants.UserId, SqlDbType.Int);
cmd.AddParameters(businessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(sessionId, "@SessionId", SqlDbType.VarChar);
cmd.ExecuteNonQuery(SqlProcedures.Update_EndSession);
}
}
catch (Exception ex)
{
throw new ArgumentException("Exception Occured in LogSessionDetails" + ex.Message);
}
}
public static bool UpdatePassword(string newGuidPwd, int userID)
{
using (var cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(userID, CommonConstants.UserId, SqlDbType.VarChar);
cmd.AddParameters(newGuidPwd, "@UserPassword", SqlDbType.VarChar);
cmd.ExecuteNonQuery(SqlProcedures.Update_UserPassword);
return(true);
}
catch (Exception ex)
{
throw new ArgumentException("Exception while updating password: " + ex.Message);
}
}
}
public static int CheckPermission(int ScreenId, int GroupId)
{
using (var cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(ScreenId, CommonConstants.ScreenId, SqlDbType.Int);
cmd.AddParameters(GroupId, CommonConstants.GroupId, SqlDbType.Int);
cmd.AddParameters(232, CommonConstants.BusinessID, SqlDbType.Int);
var Result = cmd.ExecuteScalar(SqlProcedures.Validate_ScreenPrivilege);
return(Convert.ToInt32(Result));
}
catch
{
return(-1);
}
}
}
public static EMS.Entities.MyProfileMDL GetProfileDetails(int UserId, int BusinessId)
{
EMS.Entities.MyProfileMDL user = new EMS.Entities.MyProfileMDL();
using (DBSqlCommand cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(UserId, CommonConstants.UserId, System.Data.SqlDbType.Int);
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, System.Data.SqlDbType.Int);
System.Data.IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Get_MyAccountDetails);
while (ireader.Read())
{
var userDet = new EMS.Entities.MyProfileMDL
{
UserFirstName = ireader.GetString(CommonColumns.FirstName),
UserLastName = ireader.GetString(CommonColumns.LastName),
Email = ireader.GetString(CommonColumns.UserEmail),
Fax = ireader.GetString(CommonColumns.BusinessFax),
AddressLine1 = ireader.GetString(CommonColumns.UserAddressLine1),
City = ireader.GetString(CommonColumns.City),
State = ireader.GetString(CommonColumns.State),
Country = ireader.GetString(CommonColumns.Country),
//RenewalDate = ireader.GetDateTime(CommonColumns.UserAccountExpiryDate),
Licences = ireader.GetInt16(CommonColumns.Licences),
BusinessName = ireader.GetString(CommonColumns.BusinessName),
LogMeOutId = ireader.GetInt32(CommonColumns.LogMeOutId),
EndDate = ireader.GetNullLocalDateTime(CommonColumns.UserAccountExpiryDate),
CityId = ireader.GetInt32(CommonColumns.CityId),
StateId = ireader.GetInt32(CommonColumns.StateId),
CountryId = ireader.GetInt16(CommonColumns.CountryId),
ImagePath = ireader.GetString(CommonColumns.ImagePath)
};
user = userDet;
}
return(user);
}
catch (Exception ex)
{
return(null);
}
}
}
public static List <CountryStateCityDto> GetCountryStateCity(string searchKey, string column, int?itemId)
{
using (var cmd = new DBSqlCommand())
{
try
{
var ireader = cmd.ExecuteDataReader(SqlProcedures.Get_MasterAddress);
var listItems = new List <CountryStateCityDto>();
while (ireader.Read())
{
var item = new CountryStateCityDto
{
CountryId = ireader.GetInt16(CommonConstants.CountryId),
CountryName = ireader.GetString(CommonConstants.Country),
CityId = ireader.GetInt16(CommonConstants.CityId),
CityName = ireader.GetString(CommonConstants.City),
StateId = ireader.GetInt16(CommonConstants.StateId),
StateName = ireader.GetString(CommonConstants.State)
};
listItems.Add(item);
}
var retlist = listItems.GroupBy(x => x.CountryId).Select(g => g.First()).ToList <CountryStateCityDto>();
switch (column)
{
case "country":
var returnlist = retlist.Where(x => x.CountryName.ToUpper().StartsWith(searchKey.ToUpper())).ToList();
return(returnlist);
case "state":
return(listItems.Where(item => item.StateName.ToUpper().StartsWith(searchKey.ToUpper()) && item.CountryId.Equals(itemId)).Distinct().ToList());
case "city":
return(listItems.Where(item => item.CityName.ToUpper().StartsWith(searchKey.ToUpper()) && item.StateId.Equals(itemId)).ToList());
}
return(null);
}
catch (Exception ex)
{
return(null);
}
}
}
public static List <DashboardChart> CustomersOwing(int BusinessId, int Year)
{
var CustomersOwing = new List <DashboardChart>();
using (var cmd = new DBSqlCommand())
{
try
{
DateTime FromDate = new DateTime(Year, 1, 1);
DateTime ToDate = new DateTime(Year, 12, 31);
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
//cmd.AddParameters(Year, CommonConstants.Year, SqlDbType.SmallInt);
cmd.AddParameters(FromDate, CommonConstants.FromDate, SqlDbType.DateTime);
cmd.AddParameters(ToDate, CommonConstants.ToDate, SqlDbType.DateTime);
//IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_SalesOwingByCustomers);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_SalesDetailsByCustomers);
while (ireader.Read())
{
var CustomersOwingDet = new DashboardChart
{
//SNo = ireader.GetInt32(CommonColumns.Sno),
UserCustomerId = ireader.GetInt32(CommonColumns.UserCustomerId),
Customer = ireader.GetString(CommonColumns.Customer),
TotalOwing = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalOwing)),
};
CustomersOwing.Add(CustomersOwingDet);
}
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in CustomerOwing. Exception :" + ex.Message);
}
}
return(CustomersOwing);
}
public static List <DashboardChart> TopTenVendors(int BusinessId, int Year)
{
var TopTenVendors = new List <DashboardChart>();
using (var cmd = new DBSqlCommand())
{
try
{
DateTime FromDate = new DateTime(Year, 1, 1);
DateTime ToDate = new DateTime(Year, 12, 31);
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(FromDate, CommonConstants.FromDate, SqlDbType.DateTime);
cmd.AddParameters(ToDate, CommonConstants.ToDate, SqlDbType.DateTime);
cmd.AddParameters(10, CommonConstants.Top, SqlDbType.Int);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_PurchasesDetailsByVendors);
while (ireader.Read())
{
var TopTenVendorsDet = new DashboardChart
{
UserVendorId = ireader.GetInt16(CommonColumns.UserVendorId),
Vendor = ireader.GetString(CommonColumns.Vendor),
TotalPurchase = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalPurchases)),
TotalOwing = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalOwing)),
};
TopTenVendors.Add(TopTenVendorsDet);
}
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in TopTenVendors. Exception :" + ex.Message);
}
}
return(TopTenVendors);
}
public static List <DashboardChart> Quarters(int BusinessId, int Year, int QuarterNumber)
{
var Quarters = new List <DashboardChart>();
using (var cmd = new DBSqlCommand())
{
try
{
DateTime FromDate = new DateTime(Year, 1, 1);
DateTime ToDate = new DateTime(Year, 12, 31);
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(FromDate, CommonConstants.FromDate, SqlDbType.DateTime);
cmd.AddParameters(ToDate, CommonConstants.ToDate, SqlDbType.DateTime);
cmd.AddParameters(QuarterNumber, CommonConstants.QuarterNum, SqlDbType.SmallInt);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_SalesByMonth);
while (ireader.Read())
{
var QuartersDet = new DashboardChart
{
Quarter = ireader.GetString(CommonColumns.Quarter),
Month = ireader.GetString(CommonColumns.Month),
TotalSales = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalSales))
};
Quarters.Add(QuartersDet);
}
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in Quarters. Exception :" + ex.Message);
}
}
return(Quarters);
}
public static List <DashboardChart> QuartersPuchaseChart(int BusinessId, int Year)
{
var QuartersPuchaseChart = new List <DashboardChart>();
using (var cmd = new DBSqlCommand())
{
try
{
DateTime FromDate = new DateTime(Year, 1, 1);
DateTime ToDate = new DateTime(Year, 12, 31);
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(FromDate, CommonConstants.FromDate, SqlDbType.DateTime);
cmd.AddParameters(ToDate, CommonConstants.ToDate, SqlDbType.DateTime);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_PurchaseByMonth);
while (ireader.Read())
{
var QuartersPuchaseChartDet = new DashboardChart
{
Month = ireader.GetString(CommonColumns.Month),
TotalPurchase = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalPurchase)),
Color = "#B0DE09"
};
QuartersPuchaseChart.Add(QuartersPuchaseChartDet);
}
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in QuartersPuchaseChart. Exception :" + ex.Message);
}
}
return(QuartersPuchaseChart);
}
public static CommonMessages CancelLeave(ViewApplyLeave _CancelLeave)
{
using (DBSqlCommand cmd = new DBSqlCommand(CommandType.StoredProcedure))
{
CommonMessages MessagesObj = new CommonMessages();
try
{
cmd.AddParameters(_CancelLeave.BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(_CancelLeave.EmployeeLeaveId, CommonConstants.EmployeeLeaveId, SqlDbType.Int);
cmd.AddParameters(_CancelLeave.UserEmployeeId, CommonConstants.UserEmployeeId, SqlDbType.BigInt);
cmd.AddParameters(_CancelLeave.LeaveStatusId, CommonConstants.LeaveStatusId, SqlDbType.BigInt);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.usp_UpdateLeaveStatus);
while (ireader.Read())
{
var EmpDet = new CommonMessages
{
Message = ireader.GetString(CommonColumns.ErrorMessage),
Result = ireader.GetInt32(CommonColumns.Result),
ErrorSeverity = ireader.GetString(CommonColumns.ErrorSeverity),
ErrorState = ireader.GetString(CommonColumns.ErrorState)
};
MessagesObj = EmpDet;
}
return(MessagesObj);
}
catch (Exception ex)
{
MessagesObj.Result = 0;
MessagesObj.Message = ex.Message;
MessagesObj.ErrorSeverity = ex.Message;
MessagesObj.ErrorState = ex.Message;
return(MessagesObj);
}
}
}
public static int IsExistedEmail(string email)
{
var userID = 0;
using (var cmd = new DBSqlCommand())
{
try
{
//cmd.AddParameters(businessId, CommonConstants.BusinessID, SqlDbType.VarChar);
cmd.AddParameters(email, CommonConstants.Email, SqlDbType.VarChar);
var exists = cmd.ExecuteScalar(SqlProcedures.GetUserEmail_IfExisted);
if (Convert.ToInt32(exists) > 0)
{
userID = Convert.ToInt32(exists);
}
return(userID);
}
catch (Exception)
{ }
}
return(userID);
}
public static IList <KeyValueVM> Get_LogmeOut()
{
try
{
using (DBSqlCommand cmd = new DBSqlCommand())
{
var reader = cmd.ExecuteDataReader(SqlProcedures.Get_LogMeOut);
var keyValues = new List <KeyValueVM>();
while (reader.Read())
{
var keyValue = new KeyValueVM();
keyValue.value = reader.GetString("Description");
keyValue.key = reader.GetInt32("LogMeOutId");
keyValues.Add(keyValue);
}
return(keyValues);
}
}
catch (Exception ex)
{
throw new ArgumentException("Exception in Get_LogmeOut. Exception:" + ex.Message);
}
}
public static IList <Modules> GetAllSCreensPermissions(int BusinessId, int GroupId, int UserId)
{
var listItems = new List <Modules>();
using (var cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(UserId, CommonConstants.LoggInUserId, SqlDbType.SmallInt);
cmd.AddParameters(GroupId, CommonColumns.GroupNameId, SqlDbType.Int);
cmd.AddParameters(BusinessId, CommonColumns.BusinessID, SqlDbType.Int);
var ireader = cmd.ExecuteDataReader(SqlProcedures.usp_UserAccessPrivileges);
while (ireader.Read())
{
var item = new Modules
{
ScreenId = ireader.GetInt32(CommonColumns.ScreenId),
PrivilegeId = ireader.GetInt32(CommonColumns.PrivilegeId),
GroupId = ireader.GetInt32(CommonColumns.GroupNameId),
LoginUserId = ireader.GetInt32(CommonColumns.UserId),
Privilege = ireader.GetString(CommonColumns.Privilege),
ScreenName = ireader.GetString(CommonColumns.ScreenName)
};
listItems.Add(item);
}
}
catch
{
return(null);
}
}
return(listItems);
}
public static List <Modules> GetGroupScreenPrivileges(int ModuleId, int GroupId, int BusinessId)
{
var listItems = new List <Modules>();
using (var cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(ModuleId, CommonConstants.ModuleId, SqlDbType.SmallInt);
cmd.AddParameters(GroupId, CommonColumns.GroupId, SqlDbType.Int);
cmd.AddParameters(BusinessId, CommonColumns.BusinessID, SqlDbType.Int);
var ireader = cmd.ExecuteDataReader(SqlProcedures.Bind_ScreenPrivileges);
while (ireader.Read())
{
var item = new Modules
{
ScreenId = ireader.GetInt32(CommonColumns.ScreenId),
ScreenName = ireader.GetString(CommonColumns.ScreenName),
Module = ireader.GetString(CommonColumns.ModuleName),
ModuleId = ireader.GetInt32(CommonColumns.ModuleId),
Privilege = ireader.GetString(CommonColumns.PrivilegeName),
PrivilegeId = ireader.GetInt32(CommonColumns.PrivilegeId)
};
listItems.Add(item);
}
}
catch
{
return(null);
}
}
return(listItems);
}
public static DashboardDocs GetDashboardDocs(int BusinessId, int UserId, int RoleId, int UserSessionEmployeeId)
{
DashboardDocs _DashboardDocs = new DashboardDocs();
List <NoOfLeaves> _NoOfLeaves = new List <NoOfLeaves>();
List <PendingTimeSheets> _PendingTimeSheets = new List <PendingTimeSheets>();
List <NoOfResignations> _NoOfResignations = new List <NoOfResignations>();
List <NoOfNewJoinees> _NoOfNewJoinees = new List <NoOfNewJoinees>();
List <NoOfEmployessInfo> _NoOfEmployessInfo = new List <NoOfEmployessInfo>();
List <GrossSalaryInfo> _GrossSalaryInfo = new List <GrossSalaryInfo>();
List <DeductionsInfo> _DeductionsInfo = new List <DeductionsInfo>();
int UserEmployeeId = UserSessionEmployeeId;
using (var Cmd = new DBSqlCommand())
{
try
{
Cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
Cmd.AddParameters(UserId, CommonConstants.UserId, SqlDbType.Int);
Cmd.AddParameters(RoleId, CommonConstants.RoleId, SqlDbType.Int);
Cmd.AddParameters(UserEmployeeId, CommonConstants.UserEmployeeId, SqlDbType.Int);
DataSet ireader = Cmd.ExecuteDataDataSet(SqlProcedures.usp_EmployeeDashboardDetails);
DataSet ireader1 = Cmd.ExecuteDataDataSet(SqlProcedures.usp_HRDashboardDetails);
for (var i = 0; i < ireader.Tables[0].Rows.Count; i++)
{
_DashboardDocs.CountOfNoOfLeaves = (int)ireader.Tables[0].Rows[i]["CountOfNoOfLeaves"];
}
_DashboardDocs.NoOfLeaves = _NoOfLeaves;
for (var i = 0; i < ireader.Tables[0].Rows.Count; i++)
{
_DashboardDocs.CountOfPendingTimeSheets = (int)ireader.Tables[0].Rows[i]["CountOfPendingTimeSheets"];
}
_DashboardDocs.PendingTimeSheets = _PendingTimeSheets;
for (var i = 0; i < ireader.Tables[0].Rows.Count; i++)
{
_DashboardDocs.CountOfNoOfResignations = (int)ireader.Tables[0].Rows[i]["CountOfNoOfResignations"];
}
_DashboardDocs.NoOfResignations = _NoOfResignations;
for (var i = 0; i < ireader.Tables[0].Rows.Count; i++)
{
_DashboardDocs.CountOfNoOfNewJoinees = (int)ireader.Tables[0].Rows[i]["CountOfNoOfNewJoinees"];
}
_DashboardDocs.NoOfNewJoinees = _NoOfNewJoinees;
for (var i = 0; i < ireader1.Tables[0].Rows.Count; i++)
{
_DashboardDocs.NoOfEmployees = (int)ireader1.Tables[0].Rows[i]["EmployeeCount"];
}
_DashboardDocs.NoOfEmployessInfo = _NoOfEmployessInfo;
for (var i = 0; i < ireader1.Tables[1].Rows.Count; i++)
{
_DashboardDocs.GrossSalary = (decimal)ireader1.Tables[1].Rows[i]["GrossSalary"];
_DashboardDocs.TotalESIC = (decimal)ireader1.Tables[1].Rows[i]["TotalESIC"];
}
_DashboardDocs.GrossSalaryInfo = _GrossSalaryInfo;
for (var i = 0; i < ireader1.Tables[3].Rows.Count; i++)
{
_DashboardDocs.ProvidentFund = (decimal)ireader1.Tables[3].Rows[i]["ProvidentFund"];
_DashboardDocs.ProfessionTax = (int)ireader1.Tables[3].Rows[i]["ProfessionTax"];
_DashboardDocs.TotalDeductions = (decimal)ireader1.Tables[3].Rows[i]["TotalDeductions"];
}
_DashboardDocs.DeductionsInfo = _DeductionsInfo;
return(_DashboardDocs);
}
catch (Exception Ex)
{
throw Ex;
throw new ArgumentException("Exception in GetDashboardDocs. Exception :" + Ex.Message);
}
finally
{
_DashboardDocs = null;
}
}
}
