public IEnumerable <TElement> ExecuteSQL <TElement>(string commandText, params object[] parameters)
{
try
{
StandardEngEntities context = BaseContext.GetDbContext();
if (parameters != null && parameters.Length > 0)
{
for (int i = 0; i <= parameters.Length - 1; i++)
{
var p = parameters[i] as DbParameter;
if (p == null)
{
throw new Exception("Not support parameter type");
}
commandText += i == 0 ? " " : ", ";
commandText += "@" + p.ParameterName;
if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
{
////output parameter
commandText += " output";
}
}
}
return(context.Database.SqlQuery <TElement>(commandText, parameters));
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// Function to insert entity.
/// </summary>
/// <param name="entity">entity to be inserted</param>
public string Insert(T entity)
{
try
{
using (StandardEngEntities context = BaseContext.GetDbContext())
{
DbSet <T> table = context.Set <T>();
table.Add(entity);
context.SaveChanges();
return(string.Empty);
}
}
catch (DbEntityValidationException dbEx)
{
string messages = String.Empty;
foreach (DbEntityValidationResult entityErr in dbEx.EntityValidationErrors)
{
foreach (DbValidationError error in entityErr.ValidationErrors)
{
//Console.WriteLine("Error Property Name {0} : Error Message: {1}",
// error.PropertyName, error.ErrorMessage);
messages = messages + "<br/>" + string.Join("<br/>", error.ErrorMessage);
}
}
return(messages);
}
catch (Exception ex)
{
return(CommonHelper.GetErrorMessage(ex));
}
}
/// <summary>
/// Function to delete entity whose primary key is passes.
/// </summary>
/// <param name="pk">Primary key of entity to be deleted</param>
/// <param name="context">context to maintain transaction</param>
public void Delete(object pk, StandardEngEntities context)
{
DbSet <T> table = context.Set <T>();
T existing = table.Find(pk);
context.Entry(existing).State = EntityState.Deleted;
}
/// <summary>
/// Function to update entity.
/// </summary>
/// <param name="entity">entity to be updated</param>
/// <param name="context">context to maintain transaction</param>
public void Update(T entity, StandardEngEntities context)
{
DbSet <T> table = context.Set <T>();
table.Attach(entity);
context.Entry(entity).State = EntityState.Modified;
}
public static void GetListOfWarrantyExpiry()
{
List <GetWarrantyExpiryCustomerList_Result> listExpitry = new List <GetWarrantyExpiryCustomerList_Result>();
using (StandardEngEntities context = BaseContext.GetDbContext())
{
listExpitry = context.GetWarrantyExpiryCustomerList().Where(m => m.IsReadyForAMC == false).ToList();
if (listExpitry.Count > 0)
{
string bodyTemplate = System.IO.File.ReadAllText(System.Web.Hosting.HostingEnvironment.MapPath("~/Template/WarrantyExpiryReminder.html"));
foreach (GetWarrantyExpiryCustomerList_Result obj in listExpitry)
{
tblCommissioning commisioningObj = new tblCommissioning();
bodyTemplate = bodyTemplate.Replace("[@CustomerName]", obj.CustomerName);
bodyTemplate = bodyTemplate.Replace("[@MachineModel]", obj.MachineName);
bodyTemplate = bodyTemplate.Replace("[@MachineType]", obj.MachineTypeName);
bodyTemplate = bodyTemplate.Replace("[@MachineSerialNo]", obj.MachineSerialNo);
bodyTemplate = bodyTemplate.Replace("[@ExpiryDate]", obj.WarrantyExpireDate);
Task task = new Task(() => EmailHelper.SendAsyncEmail(obj.CustomerEmail, "Machine Warranty Expiry Reminder", bodyTemplate, true));
task.Start();
commisioningObj = context.tblCommissioning.Where(m => m.CommissioningId == obj.CommissioningId).FirstOrDefault();
commisioningObj.IsReadyForAMC = true;
context.Entry(commisioningObj).State = EntityState.Modified;
context.SaveChanges();
}
}
}
}
/// <summary>
/// Function to select entity by primary key
/// </summary>
/// <param name="pk">Primary key of entity to fetch</param>
/// <returns></returns>
public T SelectById(object pk)
{
using (StandardEngEntities context = BaseContext.GetDbContext())
{
DbSet <T> table = context.Set <T>();
return(table.Find(pk));
}
}
public static List <tblAccessoriesType> AccessoriesTypeList()
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
List <tblAccessoriesType> result = dbContext.tblAccessoriesType.OrderBy(m => m.AccessoriesTypeId).ToList();
return(result);
}
}
public static List <tblCustomerContactPersons> ContactPersonsList()
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
List <tblCustomerContactPersons> result = dbContext.tblCustomerContactPersons.OrderBy(m => m.CustomerId).ToList();
return(result);
}
}
public static List <tblCustomer> CustomerList()
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
List <tblCustomer> result = dbContext.tblCustomer.OrderBy(m => m.CustomerName).ToList();
return(result);
}
}
public static List <tblMachineType> MachineTypeList()
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
List <tblMachineType> result = dbContext.tblMachineType.OrderBy(m => m.MachineTypeName).ToList();
return(result);
}
}
public static List <tblCity> CityList()
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
List <tblCity> result = dbContext.tblCity.OrderBy(m => m.CityName).ToList();
return(result);
}
}
public static List <tblGSTMaster> GSTPercentageList()
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
List <tblGSTMaster> result = dbContext.tblGSTMaster.OrderBy(m => m.Id).ToList();
return(result);
}
}
public static List <tblRole> RoleList()
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
List <tblRole> result = dbContext.tblRole.OrderBy(m => m.RoleName).ToList();
return(result);
}
}
public static List <tblUser> ServiceEngineerList()
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
List <tblUser> result = dbContext.tblUser.OrderBy(m => m.UserId).ToList();
return(result);
}
}
/// <summary>
/// Function to create entity instance
/// </summary>
/// <returns>New instance of entity</returns>
public T CreateEntity()
{
using (StandardEngEntities context = BaseContext.GetDbContext())
{
DbSet <T> table = context.Set <T>();
return(table.Create());
}
}
public static bool IsRegionNameExists(string Name, int Id, int CityId)
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
bool result = dbContext.tblRegion.Any(m => m.Name == Name && m.CityId == CityId && m.Id != Id);
return(result);
}
}
public static bool IsStateNameExists(string StateName, int StateId)
{
using (StandardEngEntities context = BaseContext.GetDbContext())
{
bool result = context.tblState.Any(m => m.StateName.ToLower().Trim() == StateName.ToLower().Trim() && m.StateId != StateId);
return(result);
}
}
public static bool IsCountryNameExists(string CountryName, int CountryId)
{
using (StandardEngEntities context = BaseContext.GetDbContext())
{
bool result = context.tblCountry.Any(m => m.CountryName.ToLower().Trim() == CountryName.ToLower().Trim() && m.CountryId != CountryId);
return(result);
}
}
public static List <tblMachineAccessories> MachineAccessoriesList()
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
List <tblMachineAccessories> result = dbContext.tblMachineAccessories.OrderBy(m => m.MachineAccessoriesId).ToList();
return(result);
}
}
public static bool CheckRoleExistsOrNot(string RoleName, int RoleId)
{
using (StandardEngEntities dbContext = BaseContext.GetDbContext())
{
bool result = dbContext.tblRole.Any(m => m.RoleName == RoleName && m.RoleId != RoleId);
return(result);
}
}
public static bool IsUserCodeExists(string UserCode, int UserId)
{
using (StandardEngEntities context = BaseContext.GetDbContext())
{
bool result = context.tblUser.Any(m => m.UserCode.ToLower().Trim() == UserCode.ToLower().Trim() && m.UserId != UserId);
return(result);
}
}
public static bool IsPCAccessorySerialAlreadyExists(int PreCommissioningId, string AccessoriesSerialNo, int PCAccessoriesId)
{
using (StandardEngEntities context = BaseContext.GetDbContext())
{
bool result = context.tblPreCommissioningAccessories.Any(m => m.AccessoriesSerialNo.Trim().ToLower() == AccessoriesSerialNo.Trim().ToLower() && m.PreCommissioningId == PreCommissioningId && m.PCAccessoriesId != PCAccessoriesId);
return(result);
}
}
public static bool IsCommissioningReportServiceNoExists(int CommissioningId, string ReportServiceNo)
{
using (StandardEngEntities context = BaseContext.GetDbContext())
{
bool result = context.tblCommissioning.Any(m => m.ReportServiceNo == ReportServiceNo &&
m.CommissioningId != CommissioningId);
return(result);
}
}
public static List <GetPerformaInvoieList_Result> GetPerformaInvoieList()
{
List <GetPerformaInvoieList_Result> dataList;
using (StandardEngEntities context = BaseContext.GetDbContext())
{
dataList = context.GetPerformaInvoieList().ToList();
}
return(dataList.OrderByDescending(m => m.QuotationDate).ToList());
}
public static List <GetCommissioningList_Result> GetCommissioningList()
{
List <GetCommissioningList_Result> dataList;
using (StandardEngEntities context = BaseContext.GetDbContext())
{
dataList = context.GetCommissioningList().ToList();
}
return(dataList.OrderByDescending(m => m.CommissioningDate).ToList());
}
public static StandardEngEntities GetDbContext()
{
StandardEngEntities context = new StandardEngEntities();
context.Configuration.ProxyCreationEnabled = false;
ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
objectContext.CommandTimeout = int.MaxValue;
return(context);
}
public static List <AssignRoleList_Result> GetUserRoleRights(int?roleId = null)
{
List <AssignRoleList_Result> dataList;
using (StandardEngEntities context = BaseContext.GetDbContext())
{
dataList = context.AssignRoleList(roleId).ToList();
}
return(dataList.OrderBy(m => m.MenuName).ToList());
}
public static List <Get_UserAccessPermissions_Result> UserAccessPermissions(int?roleId = null, bool isSuperAdmin = false)
{
List <Get_UserAccessPermissions_Result> dataList;
using (StandardEngEntities context = BaseContext.GetDbContext())
{
dataList = context.Get_UserAccessPermissions(roleId, isSuperAdmin).ToList();
}
return(dataList.OrderBy(m => m.MenuName).ToList());
}
public static List <GetPreCommisioningListDetail_Result> GetPreCommisioningListDetail(int PreCommissionId)
{
List <GetPreCommisioningListDetail_Result> dataList;
using (StandardEngEntities context = BaseContext.GetDbContext())
{
dataList = context.GetPreCommisioningListDetail(PreCommissionId).ToList();
}
return(dataList.OrderByDescending(m => m.PCDetailId).ToList());
}
public static bool IsAMCQuotationWithSameAmountExists(int MachineTypeId, int MachineModelId, string MachineSerialNo, int CustomerId, decimal ActualAmount, decimal GSTPercentage, int Id)
{
using (StandardEngEntities context = BaseContext.GetDbContext())
{
bool result = context.tblAMCQuotation.Any(m => m.MachineTypeId == MachineTypeId && m.MachineModelId == MachineModelId &&
m.MachineSerialNo.Trim() == MachineSerialNo.Trim() &&
m.CustomerId == CustomerId && m.ActualAmount == ActualAmount && m.GSTPercentage == GSTPercentage &&
m.Id != Id);
return(result);
}
}
