public async Task <bool> AddEmployeeWithDetails(Guid BusinessID, EmployeeModel employeeModel)
{
var businessID = new Npgsql.NpgsqlParameter("@thebusinessid", BusinessID);
var empName = new Npgsql.NpgsqlParameter("@theempfullname", employeeModel.EmployeeName);
var empGender = new Npgsql.NpgsqlParameter("@theempgender", employeeModel.EmployeeGender);
var empSSN = new Npgsql.NpgsqlParameter("@theempssn", employeeModel.EmployeeSSN);
var empDepartmentID = new Npgsql.NpgsqlParameter("@thedeptid", employeeModel.DepartmentID);
var empDetails = new Npgsql.NpgsqlParameter("@theempdetails", NpgsqlDbType.Json)
{
Direction = ParameterDirection.InputOutput,
Value = employeeModel.EmployeeDetails != null ? employeeModel.EmployeeDetails : (object)DBNull.Value
};
EmployeeModel employeeModel1 = await Task.Run(() => _projectContextDB.EmployeeModel.FromSqlRaw("select * from addemployee(@thebusinessid,@theempfullname," +
"@theempgender,@theempssn,@thedeptid,@theempdetails) as emp_id", businessID, empName, empGender, empSSN, empDepartmentID, empDetails)
.Select(e => new EmployeeModel()
{
EmployeeID = e.EmployeeID
}).FirstOrDefault());
if (employeeModel1.EmployeeID != new Guid())
{
return(true);
}
return(false);
}
public async Task <object> AddNewBusiness(BusinessModel businessModel)
{
try
{
var banme = new Npgsql.NpgsqlParameter("@thebusinessname", businessModel.BusinessName);
var blogo = new Npgsql.NpgsqlParameter("@thelogo", businessModel.BusinessLogo);
var bcurrency = new Npgsql.NpgsqlParameter("@thedefaultcurrency", businessModel.BusinessDefaultCurrency);
var bAddressDetails = new Npgsql.NpgsqlParameter("@theaddressdetails", NpgsqlDbType.Json)
{
Direction = ParameterDirection.InputOutput,
Value = businessModel.BusinessAddressDetails != null ? businessModel.BusinessAddressDetails : (object)DBNull.Value
};
var bContactDetails = new Npgsql.NpgsqlParameter("@thecontactdetails", NpgsqlDbType.Json)
{
Direction = ParameterDirection.InputOutput,
Value = businessModel.BusinessContactDetails != null ? businessModel.BusinessContactDetails : (Object)DBNull.Value
};
var bOwner = new Npgsql.NpgsqlParameter("@thebusinessowner", businessModel.BusinessOwner);
await Task.Run(() => _projectContextDB.Database.ExecuteSqlRaw("call addbusinesswithdetailsandcreateschema(@thebusinessname,@thelogo,@thedefaultcurrency,@theaddressdetails,@thecontactdetails,@thebusinessowner)", banme, blogo, bcurrency, bAddressDetails, bContactDetails,
bOwner));
return(true);
}
catch (Exception ex)
{
return(null);
}
}
protected override ValidationResult IsValid(object value, ValidationContext validationContext)
{
var db = new FinancesContext();
Object obj = validationContext.ObjectType;
System.Diagnostics.Debug.WriteLine(obj.ToString());
var className = obj.ToString().Split('.').Last();
var propertyName = validationContext.MemberName;
var parameterName = string.Format("@{0}", propertyName);
var sql = string.Format("SELECT COUNT(*) FROM {0} WHERE {1}={2}",
className.ToLower() + "s", propertyName, parameterName);
var parameters = new Npgsql.NpgsqlParameter(parameterName, value);
var result = db.Database.SqlQuery <Int64>(sql, parameters).Single();
if (result > 0)
{
return(new ValidationResult(String.Format(ErrorMessage, value), new List <string>()
{
propertyName
}));
}
return(null);
}
public void AddParameter(string name, object value, System.Data.DbType paramtype)
{
Npgsql.NpgsqlParameter param = new Npgsql.NpgsqlParameter(name, paramtype);
param.Value = value;
comm.Parameters.Add(param);
}
/// <summary>
/// converte o parametro para o tipo esperado
/// </summary>
/// <param name="copy">Se true, copia os valores deste objeto</param>
/// <returns></returns>
internal DbParameter ToParameterType(bool copy = true)
{
DbParameter result = null;
#region Detecta o tipo de parametro
switch (Configuration.DataGenericSettings.Settings.DatabaseType)
{
case DatabaseType.PostgreSQL:
result = new Npgsql.NpgsqlParameter();
break;
case DatabaseType.SQLite:
result = new System.Data.SQLite.SQLiteParameter();
break;
default:
throw new Exceptions.DatabaseTypeNotDefined();
}
#endregion
#region Copia o objeto
if (copy)
{
result.ParameterName = ParameterName;
result.DbType = DbType;
result.SourceColumn = SourceColumn;
result.Value = Value;
}
#endregion
return(result);
}
private void SetQueueOptions(QueueConsumerConfiguration obj, int orderId)
{
var SqlParam = new Npgsql.NpgsqlParameter("@OrderID", orderId);
obj.AddUserParameter(SqlParam);
obj.SetUserWhereClause("(q.OrderID = @OrderID)");
}
public async Task <object> ShowDeductionsByEmployeeId(Guid businessID, Guid employeeID)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var empoyeeId = new Npgsql.NpgsqlParameter("@theemployeeid", employeeID);
List <DeductionResponseInJson> deductionResponseInJsons = await Task.Run(() => _projectContextDb.DeductionResponseInJson.
FromSqlRaw("select * from showdeductionsbyemployeeid(@thebusinessid,@theemployeeid)", businessId, empoyeeId).
Select(e => new DeductionResponseInJson()
{
DeductionsDetails = e.DeductionsDetails
}).ToList());
foreach (DeductionResponseInJson dd in deductionResponseInJsons)
{
if (dd.DeductionsDetails != null)
{
List <DeductionResponse> deductionResponse = JsonConvert.DeserializeObject <List <DeductionResponse> >(deductionResponseInJsons.FirstOrDefault().DeductionsDetails);
var response = new { Success = "OK", deductionResponse };
return(response);
}
}
return(null);
}
public void AddParameter(string name, object value, System.Data.DbType paramtype)
{
Npgsql.NpgsqlParameter param = new Npgsql.NpgsqlParameter(name, paramtype);
param.Value = value;
comm.Parameters.Add(param);
}
public async Task <object> ShowAttendanceByDate(Guid businessID, string theFromDate, string theToDate)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var fromDate = new Npgsql.NpgsqlParameter("@fromdate", NpgsqlDbType.Date)
{
Direction = ParameterDirection.InputOutput,
Value = !string.IsNullOrEmpty(theFromDate) ? Convert.ToDateTime(theFromDate) : (object)DBNull.Value
};
var toDate = new Npgsql.NpgsqlParameter("@todate", NpgsqlDbType.Date)
{
Direction = ParameterDirection.InputOutput,
Value = !string.IsNullOrEmpty(theToDate) ? Convert.ToDateTime(theToDate) : (object)DBNull.Value
};
List <AttendanceResponse> attendanceResponse = await Task.Run(() => projectContextDB.AttendanceResponse
.FromSqlRaw("select * from showattendancebydate(@thebusinessid,@fromdate,@todate)", businessId, fromDate, toDate)
.Select(e => new AttendanceResponse()
{
AttendanceID = e.AttendanceID,
EmployeeID = e.EmployeeID,
Attendance = e.Attendance,
AttendanceDate = Convert.ToString(e.AttendanceDate)
}).ToList());
if (attendanceResponse.Count > 0)
{
var Response = new { Success = "OK", attendanceResponse = attendanceResponse };
return(Response);
}
return(null);
}
public async Task <Guid[]> AddDeduction(Guid businessID, DeductionModel deductionModel)
{
try
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var deductionDetails = new Npgsql.NpgsqlParameter("@thedeductiondetails", NpgsqlDbType.Json)
{
Direction = ParameterDirection.InputOutput,
Value = deductionModel.DeductionDetails != null ? deductionModel.DeductionDetails : (object)DBNull.Value
};
DeductionAddingButEmployeeIDsNotPresent deductionAddingButEmployeeIDsNotPresent = await Task.Run(() => _projectContextDb.DeductionAddingButEmployeeIDsNotPresent.FromSqlRaw("select * from adddeduction(@thebusinessid,@thedeductiondetails)", businessId, deductionDetails)
.Select(e => new DeductionAddingButEmployeeIDsNotPresent()
{
NotFoundEmployeesIds = e.NotFoundEmployeesIds
}).FirstOrDefault());
if (deductionAddingButEmployeeIDsNotPresent.NotFoundEmployeesIds.Length == 0)
{
return(null);
}
return(deductionAddingButEmployeeIDsNotPresent.NotFoundEmployeesIds);
}
catch (Exception ex)
{
return(null);
}
}
public async Task <object> ShowDeductionById(Guid businessID, Guid deductionID)
{
try
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var deductionId = new Npgsql.NpgsqlParameter("@thedeductionid", deductionID);
DeductionResponseInJson deductionResponseInJson = await Task.Run(() => _projectContextDb.DeductionResponseInJson.FromSqlRaw("select * from showdeductionbyid(@thebusinessid,@thedeductionid)", businessId, deductionId)
.Select(e => new DeductionResponseInJson()
{
DeductionsDetails = e.DeductionsDetails
}).FirstOrDefault());
if (deductionResponseInJson.DeductionsDetails != null)
{
DeductionResponse deductionResponse = JsonConvert.DeserializeObject <DeductionResponse>(deductionResponseInJson.DeductionsDetails);
var response = new { Success = "OK", deductionResponse };
return(response);
}
return(null);
}
catch (Exception)
{
return(null);
}
}
public async Task <object> ShowIncrementsByemployeeId(Guid businessID, Guid employeeID)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var employeeId = new Npgsql.NpgsqlParameter("@theemployeeid", employeeID);
List <IncrementResponseInJson> incrementResponseInJson = await Task.Run(() => _projectContextDB.IncrementResponseInJson.
FromSqlRaw("select * from showincrementsbyemployeeid(@thebusinessid,@theemployeeid)", businessId, employeeId).
Select(e => new IncrementResponseInJson()
{
IncrementDetails = e.IncrementDetails
}).ToList());
foreach (IncrementResponseInJson ir in incrementResponseInJson)
{
if (ir.IncrementDetails != null)
{
List <IncrementsResponse> incrementsResponse = JsonConvert.DeserializeObject <List <IncrementsResponse> >(incrementResponseInJson.FirstOrDefault().IncrementDetails);
var response = new { Success = "OK", incrementsResponse };
return(response);
}
}
return(null);
}
public async Task <object> ShowIncrementById(Guid businessID, Guid incrementID)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var incrementId = new Npgsql.NpgsqlParameter("@theincrementid", incrementID);
IncrementResponseInJson incrementResponseInJson = await Task.Run(() => _projectContextDB.IncrementResponseInJson.
FromSqlRaw("select * from showincrementbyid(@thebusinessid,@theincrementid)", businessId, incrementId).
Select(e => new IncrementResponseInJson()
{
IncrementDetails = e.IncrementDetails
}).FirstOrDefault());
if (incrementResponseInJson.IncrementDetails != null)
{
IncrementsResponse incrementsResponse = JsonConvert.DeserializeObject <IncrementsResponse>(incrementResponseInJson.IncrementDetails);
var response = new { Success = "OK", incrementsResponse };
return(response);
}
return(null);
}
public async Task <bool> UpdateEmployeeWithDetailsById(Guid BusinessID, Guid employeeId, UpdateEmployeeModel updateEmployeeModel)
{
var businessID = new Npgsql.NpgsqlParameter("@thebusinessid", BusinessID);
var employeeID = new Npgsql.NpgsqlParameter("@theempid", employeeId);
var empName = new Npgsql.NpgsqlParameter("@theempfullname", updateEmployeeModel.EmployeeName);
var empGender = new Npgsql.NpgsqlParameter("@theempgender", updateEmployeeModel.EmployeeGender);
var empSSN = new Npgsql.NpgsqlParameter("@theempssn", updateEmployeeModel.EmployeeSSN);
var empDepartmentID = new Npgsql.NpgsqlParameter("@thedeptid", updateEmployeeModel.DepartmentID);
var empBps = new Npgsql.NpgsqlParameter("@bps", updateEmployeeModel.BPS);
var empDesignation = new Npgsql.NpgsqlParameter("@theempdesignation", updateEmployeeModel.EmpDesignation);
var empPaCountry = new Npgsql.NpgsqlParameter("@theemppacountry", updateEmployeeModel.PACountry);
var empPaCity = new Npgsql.NpgsqlParameter("@theemppacity", updateEmployeeModel.PACity);
var empPaProvince = new Npgsql.NpgsqlParameter("@theemppaprovince", updateEmployeeModel.PAProvince);
var empPaZip = new Npgsql.NpgsqlParameter("@theemppazip", updateEmployeeModel.PAZip);
var empMaCountry = new Npgsql.NpgsqlParameter("@theempmacountry", updateEmployeeModel.MACountry);
var empMaCity = new Npgsql.NpgsqlParameter("@theempmacity", updateEmployeeModel.MACity);
var empMaProvince = new Npgsql.NpgsqlParameter("@theempmaprovince", updateEmployeeModel.MAProvince);
var empMaZip = new Npgsql.NpgsqlParameter("@theempmazip", updateEmployeeModel.MAZip);
var empEmail = new Npgsql.NpgsqlParameter("@theempemail", updateEmployeeModel.EmpEmail);
var empJoiningDate = new Npgsql.NpgsqlParameter("@theempjoiningdate", NpgsqlDbType.Date)
{
Direction = ParameterDirection.InputOutput,
Value = !string.IsNullOrEmpty(updateEmployeeModel.EmpJoiningDate) ? Convert.ToDateTime(updateEmployeeModel.EmpJoiningDate) : (object)DBNull.Value
};
var empAppDate = new Npgsql.NpgsqlParameter("@theempappointmentdate", NpgsqlDbType.Date)
{
Direction = ParameterDirection.InputOutput,
Value = !string.IsNullOrEmpty(updateEmployeeModel.EmpAppointmentDate) ? Convert.ToDateTime(updateEmployeeModel.EmpAppointmentDate) : (object)DBNull.Value
};
var empDesc = new Npgsql.NpgsqlParameter("@thedescription", updateEmployeeModel.Description);
var empPhoto = new Npgsql.NpgsqlParameter("@thephoto", updateEmployeeModel.Photo);
var empSkype = new Npgsql.NpgsqlParameter("@theskypeusername", updateEmployeeModel.SkypeUserName);
var empOfficePhoneNo = new Npgsql.NpgsqlParameter("@theempofficephoneno", updateEmployeeModel.EmpOfficeNo);
var empCellNo = new Npgsql.NpgsqlParameter("@theempcellno", updateEmployeeModel.EmpCellNo);
UpdateEmployeeModel updateEmployeeModel1 = await Task.Run(() => _projectContextDB.UpdateEmployeeModel.FromSqlRaw("select * from updateemployee(@thebusinessid,@theempid,@theempfullname," +
"@theempgender,@theempssn,@thedeptid,@bps,@theempdesignation,@theemppacountry,@theemppacity,@theemppaprovince,@theemppazip," +
"@theempmacountry,@theempmacity,@theempmaprovince,@theempmazip,@theempemail,@theempjoiningdate,@theempappointmentdate, " +
"@thedescription,@thePhoto,@theskypeusername,@theempofficephoneno,@theempcellno) as emp_id ", businessID, employeeID, empName, empGender, empSSN, empDepartmentID, empBps, empDesignation,
empPaCountry, empPaCity, empPaProvince, empPaZip, empMaCountry, empMaCity, empMaProvince, empMaZip, empEmail, empJoiningDate, empAppDate,
empDesc, empPhoto, empSkype, empOfficePhoneNo, empCellNo)
.Select(e => new UpdateEmployeeModel()
{
EmployeeID = e.EmployeeID
}).FirstOrDefault());
if (updateEmployeeModel1.EmployeeID != new Guid())
{
return(true);
}
return(false);
}
public async Task UpdateDeductionByID(Guid businessID, Guid deductionID, UpdateDeductionModel updateDeductionModel)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var deductionId = new Npgsql.NpgsqlParameter("@thedeductid", deductionID);
var amount = new Npgsql.NpgsqlParameter("@theamount", updateDeductionModel.Amount);
var description = new Npgsql.NpgsqlParameter("@thedescription", updateDeductionModel.Description);
await Task.Run(() => _projectContextDb.Database.ExecuteSqlRaw("call updatededuction(@thebusinessid,@thedeductid,@theamount,@thedescription)", businessId, deductionId, amount, description));
}
public async Task UpdateIncrementByID(Guid businessID, Guid incrementID, IncrementsUpdateModel incrementsUpdateModel)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var incrementId = new Npgsql.NpgsqlParameter("@theincrementid", incrementID);
var amount = new Npgsql.NpgsqlParameter("@theamount", incrementsUpdateModel.IncrementAmount);
var description = new Npgsql.NpgsqlParameter("@thedescription", incrementsUpdateModel.Description);
await Task.Run(() => _projectContextDB.Database.ExecuteSqlRaw("call updateincrementbyid(@thebusinessid,@theincrementid,@theamount,@thedescription)", businessId, incrementId, amount, description));
}
public async Task <object> ShowAllDepartments(Guid BusinessID)
{
var theBusinessID = new Npgsql.NpgsqlParameter("@thebusinessid", BusinessID);
return(await Task.Run(() => _projectContextDB.departmentResponse.FromSqlRaw("select * from showalldepartments(@thebusinessid)", theBusinessID)
.Select(e => new DepartmentResponse()
{
DepartmentID = e.DepartmentID,
DepartmentName = e.DepartmentName
}).ToList()));
}
public async Task <bool> CheckBusinessID(Guid businessID)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from checkbusinessid(@thebusinessid) as checkbusinessid", businessId)
.Select(e => new CheckStatusModel()
{
BusinessIDStatus = e.BusinessIDStatus
}).FirstOrDefault());
return(checkStatusModel.BusinessIDStatus);
}
public async Task <bool> ActivateUserById(Guid userID)
{
var userId = new Npgsql.NpgsqlParameter("@theuserid", userID);
CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from activateuser(@theuserid)", userId)
.Select(e => new CheckStatusModel()
{
Status = e.Status
}).FirstOrDefault());
return(checkStatusModel.Status);
}
public async void IntegerAsync(int check)
{
var param = new Npgsql.NpgsqlParameter[] {
new Npgsql.NpgsqlParameter("p_integer", NpgsqlTypes.NpgsqlDbType.Integer)
{
Value = check
},
};
var query = await Function.IntegerAsync("public.return_number", param, this.Common.ConnectionString, this.Cancellation.Token);
Assert.Equal(check + 1, query);
}
public async void BooleanAsync(bool check)
{
var param = new Npgsql.NpgsqlParameter[] {
new Npgsql.NpgsqlParameter("p_boolean", NpgsqlTypes.NpgsqlDbType.Boolean)
{
Value = check
},
};
var query = await Function.BooleanAsync("public.return_boolean", param, this.Common.ConnectionString, this.Cancellation.Token);
Assert.Equal(!check, query);
}
public void Boolean(bool check)
{
var param = new Npgsql.NpgsqlParameter[] {
new Npgsql.NpgsqlParameter("p_boolean", NpgsqlTypes.NpgsqlDbType.Boolean)
{
Value = check
},
};
var query = Function.Boolean("public.return_boolean", param, this.Common.ConnectionString);
Assert.Equal(!check, query);
}
public async Task <bool> ActivateEmployeeById(Guid BusinessID, Guid employeeId)
{
var businessID = new Npgsql.NpgsqlParameter("@thebusinessid", BusinessID);
var employeeID = new Npgsql.NpgsqlParameter("@theempid", employeeId);
CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from activateemployeebyid(@thebusinessid,@theempid)", businessID, employeeID)
.Select(e => new CheckStatusModel()
{
Status = e.Status
}).FirstOrDefault());
return(checkStatusModel.Status);
}
public async Task <bool> CheckDeductionID(Guid businessID, Guid deductionID)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var deductionId = new Npgsql.NpgsqlParameter("@thedeductionid", deductionID);
CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from checkdeductionid(@thebusinessid,@thedeductionid)", businessId, deductionId)
.Select(e => new CheckStatusModel()
{
DeductionIDStatus = e.DeductionIDStatus
}).FirstOrDefault());
return(checkStatusModel.DeductionIDStatus);
}
public async Task <bool> ActivateBusinessUserById(Guid businessID, Guid businesUserID)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var businesUserId = new Npgsql.NpgsqlParameter("@thebuid", businesUserID);
CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from activatebusinessuser(@thebusinessid,@thebuid) as status", businessId, businesUserId)
.Select(e => new CheckStatusModel()
{
Status = e.Status
}).FirstOrDefault());
return(checkStatusModel.Status);
}
public async Task <bool> CheckIfEmployeeHasAlreaySaleryAssigned(Guid businessID, Guid employeeID)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var employeeId = new Npgsql.NpgsqlParameter("@theempid", employeeID);
CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from checkemployeeidhavingsaleryassigned(@thebusinessid,@theempid) as checkemployeeid", businessId, employeeId)
.Select(e => new CheckStatusModel()
{
EmployeeIDStatus = e.EmployeeIDStatus
}).FirstOrDefault());
return(checkStatusModel.EmployeeIDStatus);
}
public async Task <bool> CheckIncrementID(Guid businessID, Guid incrementID)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var incrementId = new Npgsql.NpgsqlParameter("@theincrementid", incrementID);
CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from checkincrementid(@thebusinessid,@theincrementid) ", businessId, incrementId)
.Select(e => new CheckStatusModel()
{
IncrementIDStatus = e.IncrementIDStatus
}).FirstOrDefault());
return(checkStatusModel.IncrementIDStatus);
}
/// <summary>
/// get Update Sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="expression"></param>
/// <returns></returns>
public static (string, List <object>) GetSqlUpdate <T>(IQueryable <T> query, DbContext context, Type type, Expression <Func <T, T> > expression) where T : class
{
(string sql, string tableAlias, string tableAliasSufixAs, string topStatement, string leadingComments, IEnumerable <object> innerParameters) = GetBatchSql(query, context, isUpdate: true);
var createUpdateBodyData = new BatchUpdateCreateBodyData(sql, context, innerParameters, query, type, tableAlias, expression);
CreateUpdateBody(createUpdateBodyData, expression.Body);
var sqlParameters = ReloadSqlParameters(context, createUpdateBodyData.SqlParameters); // Sqlite requires SqliteParameters
var sqlColumns = (createUpdateBodyData.DatabaseType == DbServer.SQLServer)
? createUpdateBodyData.UpdateColumnsSql
: createUpdateBodyData.UpdateColumnsSql.Replace($"[{tableAlias}].", "");
var resultQuery = $"{leadingComments}UPDATE {topStatement}{tableAlias}{tableAliasSufixAs} SET {sqlColumns} {sql}";
if (resultQuery.Contains("ORDER") && resultQuery.Contains("TOP"))
{
string tableAliasPrefix = "[" + tableAlias + "].";
resultQuery = $"WITH C AS (SELECT {topStatement}*{sql}) UPDATE C SET {sqlColumns.Replace(tableAliasPrefix, "")}";
}
if (resultQuery.Contains("ORDER") && !resultQuery.Contains("TOP")) // When query has ORDER only without TOP(Take) then it is removed since not required and to avoid invalid Sql
{
resultQuery = resultQuery.Split("ORDER", StringSplitOptions.None)[0];
}
var databaseType = SqlAdaptersMapping.GetDatabaseType(context);
if (databaseType == DbServer.PostgreSQL)
{
resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery);
var npgsqlParameters = new List <object>();
foreach (var param in sqlParameters)
{
var npgsqlParam = new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value);
string paramName = npgsqlParam.ParameterName.Replace("@", "");
var propertyType = type.GetProperties().SingleOrDefault(a => a.Name == paramName)?.PropertyType;
if (propertyType == typeof(System.Text.Json.JsonElement) || propertyType == typeof(System.Text.Json.JsonElement?))
{
npgsqlParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb;
}
npgsqlParameters.Add(npgsqlParam);
}
sqlParameters = npgsqlParameters;
}
return(resultQuery, sqlParameters);
}
public async Task <bool> AddEmployeeSalary(Guid businessID, SalaryModel salaryModel)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var employeeId = new Npgsql.NpgsqlParameter("@theempid", salaryModel.EmployeeID);
var salary = new Npgsql.NpgsqlParameter("@thesalary", salaryModel.Salary);
CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from addsalary(@thebusinessid,@theempid," +
"@thesalary)", businessId, employeeId, salary)
.Select(e => new CheckStatusModel()
{
Status = e.Status
}).FirstOrDefault());
return(checkStatusModel.Status);
}
// FUTURE: Do not depend on Npgsql. Too npgsql specific.
public static DbParameter AddJsonParameterWithValue(this DbCommand command, string parameterName, object value)
{
if (command == null)
{
throw new ArgumentNullException(nameof(command));
}
var parameter = new Npgsql.NpgsqlParameter(parameterName, NpgsqlTypes.NpgsqlDbType.Jsonb)
{
Value = value ?? DBNull.Value
};
command.Parameters.Add(parameter);
return(parameter);
}
public async Task <bool> UpdateEmployeeSalaryBySalaryID(Guid businessID, Guid SalaryID, UpdateSalaryModel updateSalaryModel)
{
var businessId = new Npgsql.NpgsqlParameter("@thebusinessid", businessID);
var salaryId = new Npgsql.NpgsqlParameter("@thesalaryid", SalaryID);
var salary = new Npgsql.NpgsqlParameter("@thesalary", updateSalaryModel.Salary);
CheckStatusModel checkStatusModel = await Task.Run(() => _projectContextDB.CheckStatusModel.FromSqlRaw("select * from updatesalarybyid(@thebusinessid,@thesalaryid," +
"@thesalary) as salaryupdatastatus", businessId, salaryId, salary)
.Select(e => new CheckStatusModel()
{
salaryUpdateStatus = e.salaryUpdateStatus
}).FirstOrDefault());
return(checkStatusModel.salaryUpdateStatus);
}
public IDbDataParameter CreateParameter()
{
var name = this.Name;
switch (this.DbType.DatabaseServer)
{
case DatabaseServer.SqlServer:
{
var p = new System.Data.SqlClient.SqlParameter(name, this.DbType.SqlServerDbType.Value);
p.Direction = ParameterDirection.Input;
p.Value = GetParameterValue(this.DbType.SqlServerDbType.Value);
return p;
}
case DatabaseServer.Oracle:
{
var p = new Oracle.DataAccess.Client.OracleParameter(name, this.DbType.OracleServerDbType.Value);
p.Direction = ParameterDirection.Input;
//p.Value = GetParameterValue(this.OracleServerDbType.Value);
return p;
}
case DatabaseServer.MySql:
{
var p = new MySql.Data.MySqlClient.MySqlParameter(name, this.DbType.MySqlServerDbType.Value);
p.Direction = ParameterDirection.Input;
p.Value = GetParameterValue(this.DbType.MySqlServerDbType.Value);
return p;
}
case DatabaseServer.PostgreSql:
{
var p = new Npgsql.NpgsqlParameter(name, this.DbType.PostgreSqlServerDbType.Value);
p.Direction = ParameterDirection.Input;
//p.Value = GetParameterValue(this.MySqlDbType.Value);
return p;
}
default: throw new InvalidOperationException();
}
}
