public dynamic GetOpenPositionsDetails(int companyId)
{
try
{
StoredProcedureParameter OpenPositionsParam = new StoredProcedureParameter("@OpenPositions", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter OffersMadeParam = new StoredProcedureParameter("@OffersMade", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter OnBoardParam = new StoredProcedureParameter("@OnBoard", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter RejectedDeniedParam = new StoredProcedureParameter("@RejectedDenied", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter InProcessParam = new StoredProcedureParameter("@InProcess", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureHelper helper = new StoredProcedureHelper();
List <StoredProcedureParameter> parameters = new List <StoredProcedureParameter>()
{
new StoredProcedureParameter("@CompanyID", companyId, DbType.Int32, ParameterDirection.Input),
OpenPositionsParam,
OffersMadeParam,
OnBoardParam,
RejectedDeniedParam,
InProcessParam
};
var results = helper.MultiResultStoredProcedure(new StoredProcedureSettings(StoredProcedureNames.OpenPositions, parameters));
dynamic d = new ExpandoObject();
d.OpenPositions = OpenPositionsParam.Value;
d.OffersMade = OffersMadeParam.Value;
d.OnBoard = OnBoardParam.Value;
d.RejectedDenied = RejectedDeniedParam.Value;
d.InProcess = InProcessParam.Value;
return(d);
}
catch
{
throw;
}
}
private TreeNode[] getUnassignedTasks()
{
TaskPrioritiser tp = new TaskPrioritiser(StoredProcedureHelper.GetUnassginedTasksWithContracts());
List <KeyValuePair <TaskWithContract, float> > tasks = tp.GetTasksSortedByPriority();
TreeNode[] unassignedTasks = new TreeNode[tasks.Count];
Task item;
TreeNode nodeToAdd;
for (int i = 0; i < tasks.Count; i++)
{
//Stop the running thread if the form thread closes
if (stopThreads)
{
return(null);
}
item = tasks[i].Key.Task;
nodeToAdd = new TreeNode(item.Id + ", " + item.FK_ClientId + ", " + item.DateAdded.ToShortDateString() + ", " + tasks[i].Key.Contract + ", " + tasks[i].Value);
nodeToAdd.Tag = item;
nodeToAdd.Name = "Unassigned Task";
unassignedTasks[i] = nodeToAdd;
}
return(unassignedTasks);
}
public dynamic GetIndentTrackerInfo(string indentNumber)
{
StoredProcedureParameter NoOfPositionsParam = new StoredProcedureParameter("@NoOfPositions", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter OffersMadeParam = new StoredProcedureParameter("@OffersMade", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter OnBoardParam = new StoredProcedureParameter("@OnBoard", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter RejectedParam = new StoredProcedureParameter("@Rejected", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter OfferDeniedParam = new StoredProcedureParameter("@OfferDenied", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureParameter InProcessParam = new StoredProcedureParameter("@InProcess", 0, DbType.Int32, ParameterDirection.Output);
StoredProcedureHelper helper = new StoredProcedureHelper();
List <StoredProcedureParameter> parameters = new List <StoredProcedureParameter>()
{
new StoredProcedureParameter("@IndentNumber", indentNumber, DbType.String, ParameterDirection.Input),
NoOfPositionsParam,
OffersMadeParam,
OnBoardParam,
RejectedParam,
OfferDeniedParam,
InProcessParam
};
var results = helper.MultiResultStoredProcedure(new StoredProcedureSettings(StoredProcedureNames.IndentTrackerInfo, parameters));
dynamic d = new ExpandoObject();
d.NoOfPositions = NoOfPositionsParam.Value;
d.OfferedMade = OffersMadeParam.Value;
d.OnBoard = OnBoardParam.Value;
d.Rejected = RejectedParam.Value;
d.OfferDenied = OfferDeniedParam.Value;
d.InProcess = InProcessParam.Value;
return(d);
}
public List <ResumeSearchResult> SearchCandidates(string SearchType, string search, string FirstName, string LastName, string Email, string ContactNumber, int MinExperience, int ResumeSourceTypeID, string ResumeSourceDetail, string Skills, int Passport, int Visa, int TravelledOnsiteBefore, string Gender, string Certifications, string CandidateStatus, int pageNo, int pageSize, string sortColumn, string sortDirection)
{
try
{
StoredProcedureHelper helper = new StoredProcedureHelper();
List <StoredProcedureParameter> parameters = new List <StoredProcedureParameter>()
{
new StoredProcedureParameter("@PageNo", pageNo, DbType.Int32, ParameterDirection.Input),
new StoredProcedureParameter("@PageSize", pageSize, DbType.Int32, ParameterDirection.Input),
new StoredProcedureParameter("@SortColumn", sortColumn, DbType.String, ParameterDirection.Input),
new StoredProcedureParameter("@SortOrder", sortDirection, DbType.String, ParameterDirection.Input)
};
if (SearchType.Equals("Simple"))
{
parameters.Add(new StoredProcedureParameter("@SearchString", search, DbType.String, ParameterDirection.Input));
}
else
{
parameters.Add(new StoredProcedureParameter("@FirstName", FirstName, DbType.String, ParameterDirection.Input));
parameters.Add(new StoredProcedureParameter("@LastName", LastName, DbType.String, ParameterDirection.Input));
parameters.Add(new StoredProcedureParameter("@Email", Email, DbType.String, ParameterDirection.Input));
parameters.Add(new StoredProcedureParameter("@ContactNumber", ContactNumber, DbType.String, ParameterDirection.Input));
parameters.Add(new StoredProcedureParameter("@MinExperience", MinExperience, DbType.Int32, ParameterDirection.Input));
if (ResumeSourceTypeID > 0)
{
parameters.Add(new StoredProcedureParameter("@ResumeSourceTypeID", ResumeSourceTypeID, DbType.Int32, ParameterDirection.Input));
}
parameters.Add(new StoredProcedureParameter("@ResumeSourceDetail", ResumeSourceDetail, DbType.String, ParameterDirection.Input));
parameters.Add(new StoredProcedureParameter("@Skills", Skills, DbType.String, ParameterDirection.Input));
if (Passport > 0)
{
parameters.Add(new StoredProcedureParameter("@Passport", Passport, DbType.Boolean, ParameterDirection.Input));
}
if (Visa > 0)
{
parameters.Add(new StoredProcedureParameter("@Visa", Visa, DbType.Boolean, ParameterDirection.Input));
}
if (TravelledOnsiteBefore > 0)
{
parameters.Add(new StoredProcedureParameter("@TravelledOnsiteBefore", TravelledOnsiteBefore, DbType.Boolean, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(Gender))
{
parameters.Add(new StoredProcedureParameter("@Gender", Gender, DbType.String, ParameterDirection.Input));
}
parameters.Add(new StoredProcedureParameter("@Certifications", Certifications, DbType.String, ParameterDirection.Input));
parameters.Add(new StoredProcedureParameter("@CandidateStatus", CandidateStatus, DbType.String, ParameterDirection.Input));
}
var results = helper.MultiResultStoredProcedure(new StoredProcedureSettings(StoredProcedureNames.ResumeSearch, parameters));
return(helper.SerializeToResultObject <ResumeSearchResult>(results.First()).ToList());
}
catch
{
throw;
}
}
private bool UpdateProcedureItem(ListViewItem item, PLSQLProcedure procedure, bool reloadExisting)
{
if (reloadExisting || existingPackage == null)
{
GetExistingPackage(lastReadPLSQLSpec.PackageName);
}
StoredProcedure storedProc = StoredProcedureHelper.CreateStoredProcedure(procedure);
// Check if procedure is imported already
bool isImported = (existingPackage != null &&
storedProc != null &&
existingPackage.Procedures.Where(p => p.Equals(storedProc)).Count() == 1);
bool packageHasProcedureNameImported = (existingPackage != null &&
storedProc != null &&
existingPackage.Procedures.Where(p => p.ProcedureName == storedProc.ProcedureName).Count() > 0);
ImportStoredProcedureWrapperClass wrapper = new ImportStoredProcedureWrapperClass();
wrapper.StoredProcedure = procedure;
wrapper.IsImported = isImported;
item.Text = procedure.Name;
item.SubItems[1].Text = procedure.Status.ToString();
if (isImported)
{
item.SubItems[2].Text = "Imported";
}
else
{
if (packageHasProcedureNameImported)
{
item.SubItems[2].Text = "Overloaded/Updated";
}
else
{
item.SubItems[2].Text = "Not Imported";
}
}
item.Tag = wrapper;
if (procedure.Status != ProcedureStatus.Valid)
{
item.ForeColor = Color.OrangeRed;
}
else if (isImported)
{
item.ForeColor = Color.Green;
}
return(true);
}
public CallSimulator(int minIntervalSeconds, int maxIntervalSeconds)
{
calls = CallerQueue.GetInstance();
cellNumbers = StoredProcedureHelper.GetUniqueCellNumbers();
randomTimer = new Timer();
randomiser = new Random();
randomTimer.Tick += randomTimer_Tick;
this.minInterval = minIntervalSeconds;
this.maxInterval = maxIntervalSeconds;
}
private IEnumerable <string> validEmail(string email)
{
//Test for uniqueness of the email using a stored procedure
if (!email.Contains("@"))
{
yield return("Email must contain an '@' symbol");
}
else
if (!StoredProcedureHelper.IsUniqueEmail(email))
{
yield return("Email already exists");
}
}
public async Task <string> GetMODailyList(MODailyListInput input)
{
var count = context.GetMODailyList(input.MOInterId);
StoredProcedureHelper spHelper = new StoredProcedureHelper("DIME2Barcode");
Dictionary <string, object> param = new Dictionary <string, object>();
param.Add("@MOInterID", input.MOInterId);
var table = spHelper.Exec("GetMODailyList", param);
//无值
return(JsonConvert.SerializeObject(table));
}
public List <Tuple <string, int, int> > GetOfferJoiningRatio(int companyId)
{
StoredProcedureHelper helper = new StoredProcedureHelper();
List <StoredProcedureParameter> parameters = new List <StoredProcedureParameter>()
{
new StoredProcedureParameter("@CompanyID", companyId, DbType.Int32, ParameterDirection.Input)
};
var results = helper.MultiResultStoredProcedure(new StoredProcedureSettings(StoredProcedureNames.OfferJoiningRatio, parameters));
//return helper.SerializeToResultObject<IndentTrackerData>(results.First()).ToList();
List <Tuple <string, int, int> > sources = new List <Tuple <string, int, int> >();
foreach (DataRow dr in results.First().Rows)
{
sources.Add(Tuple.Create(dr["DepartmentName"].ToString(), Convert.ToInt32(dr["OffersMadeCount"]), Convert.ToInt32(dr["OnBoardCount"])));
}
return(sources);
}
public List <Indent> GetIndents(int companyId)
{
try
{
StoredProcedureHelper helper = new StoredProcedureHelper();
List <StoredProcedureParameter> parameters = new List <StoredProcedureParameter>()
{
new StoredProcedureParameter("@CompanyID", companyId, DbType.Int32, ParameterDirection.Input)
};
var results = helper.MultiResultStoredProcedure(new StoredProcedureSettings(StoredProcedureNames.Indents, parameters));
return(helper.SerializeToResultObject <Indent>(results.First()).ToList());
}
catch
{
throw;
}
}
public ExecuteIndentWorkFlowResult ExecuteIndentWorkflow(int indentId)
{
try
{
StoredProcedureHelper helper = new StoredProcedureHelper();
var results = helper.MultiResultStoredProcedure(new StoredProcedureSettings(
StoredProcedureNames.IndentWorkflow,
new List <StoredProcedureParameter>
{
new StoredProcedureParameter("@IndentID", indentId, DbType.Int32, ParameterDirection.Input)
}));
return(helper.SerializeToResultObject <ExecuteIndentWorkFlowResult>(results.First()).FirstOrDefault());
}
catch
{
throw;
}
}
private void initialise(Task task, bool insert)
{
InitializeComponent();
CenterToScreen();
this.task = task;
this.insert = insert;
task.DeepCopyInto(ref oldCopy);
taskStatuses = TaskStatus.Select();
taskTypes = TaskType.Select();
userEmails = StoredProcedureHelper.GetClientEmails();
createDataGridColumns();
bindFields(task);
if (task.Location != null)
{
updateLocationsTable();
setDataGridViewSelection(task.Location);
}
}
public InterviewScheduleResult GetInterviewSchedule(int candidateId, int round)
{
try
{
StoredProcedureHelper helper = new StoredProcedureHelper();
var results = helper.MultiResultStoredProcedure(new StoredProcedureSettings(
StoredProcedureNames.InterviewSchedule,
new List <StoredProcedureParameter>
{
new StoredProcedureParameter("@CandidateID", candidateId, DbType.Int32, ParameterDirection.Input),
new StoredProcedureParameter("@InverviewRound", round, DbType.Int32, ParameterDirection.Input)
}));
return(helper.SerializeToResultObject <InterviewScheduleResult>(results.First()).FirstOrDefault());
}
catch
{
throw;
}
}
private void initialise(Schedule schedule, bool insert)
{
InitializeComponent();
CenterToScreen();
nudCost.Maximum = decimal.MaxValue;
nudCost.Minimum = decimal.MinValue;
createDataGridColumns();
this.insert = insert;
this.schedule = schedule;
schedule.DeepCopyInto(ref oldCopy);
employees = ComplexQueryHelper.GetAllTechnicians();
tasks = StoredProcedureHelper.GetUnassginedTasksWithContracts();
//If a task has already been assigned and the task is in view mode, add the task to the list of available tasks
if (schedule.FK_TaskId != "" && !insert)
{
tasks.Add(new TaskWithContract("", schedule.Task));
}
bindFields(schedule);
setFieldsEnable(insert);
}
public string ExecuteInterviewWorkflow(int candidateId)
{
try
{
StoredProcedureHelper helper = new StoredProcedureHelper();
var results = helper.MultiResultStoredProcedure(new StoredProcedureSettings(
StoredProcedureNames.InterviewWorkflow,
new List <StoredProcedureParameter>
{
new StoredProcedureParameter("@CandidateID", candidateId, DbType.Int32, ParameterDirection.Input)
}));
if (results.First() != null && results.First().Rows.Count > 0 && results.First().Rows[0][0] != null)
{
return(results.First().Rows[0][0].ToString());
}
return("");
}
catch
{
throw;
}
}
private TreeNode[] getAssignedTasks()
{
List <Schedule> schedules = StoredProcedureHelper.GetScheduledTasksInProgress();
var groupedData = schedules.GroupBy(t => t.FK_EmployeeId);
TreeNode[] result = new TreeNode[groupedData.Count()];
TreeNode[] toAdd;
int lengthResult = 0;
int lengthToAdd = 0;
foreach (IGrouping <string, Schedule> item in groupedData)
{
//Stop the running thread if the form thread closes
if (stopThreads)
{
return(null);
}
toAdd = new TreeNode[item.Count()];
TreeNode nodeToAdd;
lengthToAdd = 0;
foreach (Schedule schedule in item)
{
nodeToAdd = new TreeNode(schedule.Id + ", " + schedule.Task.FK_ClientId + ", " + schedule.Task.DateAdded);
nodeToAdd.Tag = schedule;
nodeToAdd.Name = "Scheduled Task";
toAdd[lengthToAdd++] = nodeToAdd;
}
result[lengthResult++] = new TreeNode(item.Key, toAdd);
}
return(result);
}
private StoredProcedure GetStoredProcedureFromPLSQLProcedure(PLSQLProcedure procedure, string packageName)
{
StoredProcedure storedProc = null;
if (procedure != null)
{
// Check if there are any ref cursors in the procedure parameters
if (procedure.IsContainingRefCursor && !procedure.IsFunction)
{
// Create ref cursor stored procedure
RefCurStoredProcedure proc = RefCurStoredProcedure.Create(procedure.Name, packageName, mySchema.ConnectionString);
// Check if procedure is valid
if (proc.Status == RefCurStoredProcedureStatus.Valid)
{
// Convert the RefCurStoredProcedure to a StoredProcedure
storedProc = StoredProcedureHelper.CreateStoredProcedure(procedure, proc);
}
else
{
string errorText = string.Format("Error when trying to import procedure {0} that has a Ref Cursor parameter!" + Environment.NewLine + Environment.NewLine, proc.Name) +
string.Format("Status: {0}", proc.Status.ToString()) + Environment.NewLine + Environment.NewLine +
proc.ErrorText;
MessageBox.Show(errorText, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
// Convert the PLSQLProcedure to a StoredProcedure
storedProc = StoredProcedureHelper.CreateStoredProcedure(procedure);
}
}
return(storedProc);
}
public void SelectUnassignedTasks()
{
List <TaskWithContract> result = StoredProcedureHelper.GetUnassginedTasksWithContracts();
Assert.AreEqual(true, result.Count > 0);
}
public void SelectScheduledTasks()
{
List <Schedule> list = StoredProcedureHelper.GetScheduledTasksInProgress();
Assert.AreEqual(true, list.Count > 0);
}
private static async Task Cleanup()
{
StoredProcedureHelper storedProcedureHelper = new StoredProcedureHelper(new SqlQueryBuilder());
await storedProcedureHelper.DropStoredProceduresAsync();
}
private static StoredProcedureHelper CreateStoredProcedureHelper()
{
StoredProcedureHelper storedProcedureHelper = new StoredProcedureHelper(new SqlQueryBuilder());
return(storedProcedureHelper);
}
public void SelectClientEmails()
{
Dictionary <string, string> emails = StoredProcedureHelper.GetClientEmails();
Assert.AreEqual(true, emails.Count() > 0);
}
