public DataTable GetSetNextSSNDummy()
{ //rguidi 8/29/2013
DataTable result = null;
try
{
StoredProcedure storedProcedure = new StoredProcedure()
{
StoredProcedureName = schema + ".[Badging.GetNextNonUSCitSSN]"
};
result = storedProcedure.ExecuteDataSet();
}
catch (Exception ex)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("GetSetNextSSNDummy() Exception.Message: " + ex.Message);
sb.AppendLine("GetSetNextSSNDummy() Exception.InnerException: " + ex.InnerException);
sb.AppendLine("GetSetNextSSNDummy() Exception.Source: " + ex.Source);
sb.AppendLine("GetSetNextSSNDummy() Exception.StackTrace: " + ex.StackTrace);
log4net.ILog log = log4net.LogManager.GetLogger("AirportIQ.Web.Verbose");
log.Debug(sb.ToString());
throw ex;
}
return(result);
}
//JBienvenu 2013-01-11 19304 new
/// <summary>
/// Return the earliest and latest dates at which the specified agreement can start and end.
/// </summary>
/// <param name="agreementID"></param>
public AgreementHelper.DateLimits DateLimits(int?agreementID, int?newPrimeAgreementID)
{
var storedProcedure = new StoredProcedure()
{
StoredProcedureName = _Schema + ".[Agreements.DateLimits]"
};
storedProcedure.Parameters.Add(new StoredProcedureParameter("@AgreementID", ParameterType.DBInteger, agreementID ?? 0));
storedProcedure.Parameters.Add(new StoredProcedureParameter("@NewPrimeAgreementID", ParameterType.DBInteger, newPrimeAgreementID ?? 0));
DataTable tableReturned = storedProcedure.ExecuteDataSet();
if (tableReturned == null)
{
return(new AgreementHelper.DateLimits());
}
else if (tableReturned.Rows.Count == 0)
{
return(new AgreementHelper.DateLimits());
}
else if (tableReturned.Rows.Count == 1)
{
return(new AgreementHelper.DateLimits(tableReturned.Rows[0]));
}
else
{
throw new Exception(storedProcedure.StoredProcedureName + " returned more than 1 row.");
}
}
public bool SaveBadgingResults(BadgingResults badgingResults, int userID, bool CreateBackgroundCheck)
{
bool result = false;
try
{
StoredProcedure storedProcedure = new StoredProcedure()
{
StoredProcedureName = schema + ".[Badging.Appointment.Save]"
};
string countryCode = badgingResults.BiographicModel.CountryCode;
if (countryCode == null || countryCode == string.Empty)
{
badgingResults.BiographicModel.CountryCode = "USA";
}
string countrySubdivisionCode = badgingResults.BiographicModel.CountrySubdivisionCode;
if (countrySubdivisionCode == null)
{
badgingResults.BiographicModel.CountrySubdivisionCode = string.Empty;
}
string countryCode_Birth = badgingResults.BiographicModel.CountryCode_Birth;
if (countryCode_Birth == null || countryCode_Birth == string.Empty)
{
badgingResults.BiographicModel.CountryCode_Birth = "USA";
}
//JBienvenu 2013-01-09 new block
string countrySubdivisionCode_Birth = badgingResults.BiographicModel.CountrySubdivisionCode_Birth;
if (countrySubdivisionCode_Birth == null)
{
badgingResults.BiographicModel.CountrySubdivisionCode_Birth = string.Empty;
}
string strBadgingResults = XmlHelper.Serialize(badgingResults);
storedProcedure.Parameters.Add(new StoredProcedureParameter("@BadgingResults", ParameterType.DBString, strBadgingResults));
storedProcedure.Parameters.Add(new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID));
storedProcedure.Parameters.Add(new StoredProcedureParameter("@CreateBackgroundCheck", ParameterType.DBBoolean, CreateBackgroundCheck));
storedProcedure.ExecuteDataSet();
result = true;
}
catch (Exception ex)
{
result = false;
StringBuilder sb = new StringBuilder();
sb.AppendLine("SaveBadgingResults() Exception.Message: " + ex.Message);
sb.AppendLine("SaveBadgingResults() Exception.InnerException: " + ex.InnerException);
sb.AppendLine("SaveBadgingResults() Exception.Source: " + ex.Source);
sb.AppendLine("SaveBadgingResults() Exception.StackTrace: " + ex.StackTrace);
log4net.ILog log = log4net.LogManager.GetLogger("AirportIQ.Web.Verbose");
log.Debug(sb.ToString());
throw ex;
}
return(result);
}
/// <summary>
/// Given a note, add it to CS
/// </summary>
/// <param name="personID">Unique GUID identifier for an employee</param>
/// <returns>If the action was successful</returns>
public bool AddNote(Guid personID, string note)
{
ConnectionStringSettingsCollection connections = ConfigurationManager.ConnectionStrings;
ConnectionStringSettings constr = connections["ApplicationServices"];
using (var sqlConn = new SqlConnection(constr.ConnectionString))
{
try
{
sqlConn.Open();
var storedProcedure = new StoredProcedure()
{
StoredProcedureName = _Schema + ".[FISC.ADDtoCS.Note]"
};
storedProcedure.Parameters.Add(new StoredProcedureParameter("@GUID", ParameterType.DBString, personID.ToString()));
storedProcedure.Parameters.Add(new StoredProcedureParameter("@Note", ParameterType.DBString, note.ToString()));
DataTable result = storedProcedure.ExecuteDataSet();
}
catch (Exception ex)
{
throw ex;
//return false;
}
finally
{
sqlConn.Close();
}
}
return(true);
}
public DataTable SaveSelfAudit(int auditGroupID, int staffID_Responsible, string auditName, int userID)
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Self.Save]"
};
storedProcedure.CommandTimeOut = 3600;
var paramAuditGroupID =
new StoredProcedureParameter("@AuditGroupID", ParameterType.DBInteger, auditGroupID);
var paramStaffID_Responsible =
new StoredProcedureParameter("@StaffID_Responsible", ParameterType.DBInteger, staffID_Responsible);
var paramAuditName =
new StoredProcedureParameter("@AuditName", ParameterType.DBString, auditName);
var paramUserID =
new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID);
storedProcedure.Parameters.Add(paramAuditGroupID);
storedProcedure.Parameters.Add(paramStaffID_Responsible);
storedProcedure.Parameters.Add(paramAuditName);
storedProcedure.Parameters.Add(paramUserID);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public DataTable AuditEditorWorkItem(int userID, int?workID, int?auditSpecificationID)
{
if (workID == null && auditSpecificationID == null)
{
throw new Exception("When calling AuditEditorWorkItem workID or auditSpecificationID must have a valid (int) value.");
}
if (workID == null)
{
workID = -1;
}
if (auditSpecificationID == null)
{
auditSpecificationID = -1;
}
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Editor.WorkItem]"
};
var paramUserID = new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID);
var paramWorkID = new StoredProcedureParameter("@WorkID", ParameterType.DBInteger, workID);
var paramAuditSpecificationID = new StoredProcedureParameter("@AuditSpecificationID", ParameterType.DBInteger, auditSpecificationID);
storedProcedure.Parameters.Add(paramUserID);
storedProcedure.Parameters.Add(paramWorkID);
storedProcedure.Parameters.Add(paramAuditSpecificationID);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public DataTable PopulateNameListReport(int UserID, DataTable NameListEntryIDs)
{
DataTable result = null;
var storedProcedure = new StoredProcedure()
{
StoredProcedureName = _Schema + ".[NameList.NameListOnDemandMatchReport]"
};
var paramUserID = new StoredProcedureParameter()
{
Name = "@UserID",
DBValueType = ParameterType.DBInteger,
Value = UserID
};
storedProcedure.Parameters.Add(paramUserID);
var paramNameList = new StoredProcedureParameter
{
Name = "@NameListMatches",
DBValueType = ParameterType.Structured,
Value = NameListEntryIDs
};
storedProcedure.Parameters.Add(paramNameList);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public DataTable GetDojStatus(Guid personID)
{
ConnectionStringSettingsCollection connections = ConfigurationManager.ConnectionStrings;
ConnectionStringSettings constr = connections["ApplicationServices"];
DataTable result = null;
using (var sqlConn = new SqlConnection(constr.ConnectionString))
{
try
{
sqlConn.Open();
var storedProcedure = new StoredProcedure()
{
StoredProcedureName = _Schema + ".[FISC.GetDojStatus]"
};
storedProcedure.Parameters.Add(new StoredProcedureParameter("@GUID", ParameterType.DBString, personID.ToString()));
result = storedProcedure.ExecuteDataSet();
result.TableName = "DojStatus";
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
}
return(result);
}
protected void Page_Load(object sender, System.EventArgs e)
{
pagTitle.InnerText = ResourceManager.GetString("pagTitle");
string strPath;
// Clear any message
this.lblMessage.Text = "";
strPath = Server.MapPath(ConfigurationSettings.AppSettings["ImportToolBookSourceDirectory"]);
this.lblHintExample.Text = strPath;
this.lblHint.Text = strPath;
//business requirement 5
btnUpload.Attributes.Add("onclick", "javascript:return confirm('" + ResourceManager.GetString("btnUploadMsg") + "');");
if (!Page.IsPostBack)
{
DataSet SCOs;
try
{
using (StoredProcedure sp = new StoredProcedure("prcSCORMpublishedcontent"))
{
SCOs = sp.ExecuteDataSet();
}
}
catch (Exception)
{
throw;
}
lstUploads.DataSource = SCOs;
lstUploads.DataBind();
}
}
public int AuditManagerGroupSave(int auditGroupID, string facilityCode, string groupName, DateTime auditDate, string _Action, int userID)
{
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Manager.Group.Save]"
};
var paramAuditGroupID = new StoredProcedureParameter("@AuditGroupID", ParameterType.DBInteger, auditGroupID);
var paramFacilityCode = new StoredProcedureParameter("@FacilityCode", ParameterType.DBString, facilityCode);
var paramGroupName = new StoredProcedureParameter("@GroupName", ParameterType.DBString, groupName);
var paramAuditDate = new StoredProcedureParameter("@AuditDate", ParameterType.DBDateTime, auditDate);
var param_Action = new StoredProcedureParameter("@_Action", ParameterType.DBString, _Action);
var paramUserID = new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID);
storedProcedure.Parameters.Add(paramAuditGroupID);
storedProcedure.Parameters.Add(paramFacilityCode);
storedProcedure.Parameters.Add(paramGroupName);
storedProcedure.Parameters.Add(paramAuditDate);
storedProcedure.Parameters.Add(param_Action);
storedProcedure.Parameters.Add(paramUserID);
DataTable dt = storedProcedure.ExecuteDataSet();
int newIdentity = int.Parse(dt.Rows[0][0].ToString());
return(newIdentity);
}
/// <summary>
/// Get the an EBTS binary data for an employee that was fingerprinted
/// </summary>
/// <param name="personID">Unique GUID identifier for an employee</param>
/// <returns>Binary data of the employees EBTS file, for the LAST time they were fingerprinted</returns>
public Byte[] GetEBTS(Guid personID)
{
ConnectionStringSettingsCollection connections = ConfigurationManager.ConnectionStrings;
ConnectionStringSettings constr = connections["ApplicationServices"];
Byte[] ebtsBinary = null;
using (var sqlConn = new SqlConnection(constr.ConnectionString))
{
try
{
sqlConn.Open();
var storedProcedure = new StoredProcedure()
{
StoredProcedureName = _Schema + ".[FISC.GetEBTS]"
};
storedProcedure.Parameters.Add(new StoredProcedureParameter("@GUID", ParameterType.DBString, personID.ToString()));
DataTable result = storedProcedure.ExecuteDataSet();
ebtsBinary = (Byte[])result.Rows[0][0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
}
return(ebtsBinary);
}
public DataTable AuditPoliceInspectionsSave(int auditInspectionID, string facilityCode, DateTime auditDate, int watchNumber, string location, int officerBadgeID, int?officerBadgeID2, int?officerBadgeID3, string _Action, int userID)
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.PoliceInspections.Save]"
};
var paramAuditInspectionID = new StoredProcedureParameter("@AuditInspectionID", ParameterType.DBInteger, auditInspectionID);
var paramFacilityCode = new StoredProcedureParameter("@FacilityCode", ParameterType.DBString, facilityCode);
var paramAuditDate = new StoredProcedureParameter("@AuditDate", ParameterType.DBDateTime, auditDate);
var paramwWatchNumber = new StoredProcedureParameter("@WatchNumber", ParameterType.DBInteger, watchNumber);
var paramLocation = new StoredProcedureParameter("@Location", ParameterType.DBString, location);
var paramOfficerBadgeID = new StoredProcedureParameter("@OfficerBadgeID", ParameterType.DBInteger, officerBadgeID);
var paramOfficerBadgeID2 = new StoredProcedureParameter("@OfficerBadgeID2", ParameterType.DBInteger, officerBadgeID2 != null ? (object)officerBadgeID2.Value : DBNull.Value);
var paramOfficerBadgeID3 = new StoredProcedureParameter("@OfficerBadgeID3", ParameterType.DBInteger, officerBadgeID3 != null ? (object)officerBadgeID3.Value : DBNull.Value);
var param_Action = new StoredProcedureParameter("@_Action", ParameterType.DBInteger, _Action);
var paramUserID = new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID);
storedProcedure.Parameters.Add(paramAuditInspectionID);
storedProcedure.Parameters.Add(paramFacilityCode);
storedProcedure.Parameters.Add(paramAuditDate);
storedProcedure.Parameters.Add(paramwWatchNumber);
storedProcedure.Parameters.Add(paramLocation);
storedProcedure.Parameters.Add(paramOfficerBadgeID);
storedProcedure.Parameters.Add(paramOfficerBadgeID2);
storedProcedure.Parameters.Add(paramOfficerBadgeID3);
storedProcedure.Parameters.Add(param_Action);
storedProcedure.Parameters.Add(paramUserID);
result = storedProcedure.ExecuteDataSet();
return(result);
}
/// <summary>
/// Get all of the GUID's for employees that were fingerprinted over a time range
/// </summary>
/// <param name="start">Begin time</param>
/// <param name="end">End time</param>
/// <returns>A datatable of guids which represent employees that were fingerprinted</returns>
public DataTable GetFingerprints(DateTime start, DateTime end)
{
ConnectionStringSettingsCollection connections = ConfigurationManager.ConnectionStrings;
ConnectionStringSettings constr = connections["ApplicationServices"];
DataTable result = null;
using (var sqlConn = new SqlConnection(constr.ConnectionString))
{
try
{
sqlConn.Open();
var storedProcedure = new StoredProcedure()
{
StoredProcedureName = _Schema + ".[FISC.GetFingerprinted]"
};
storedProcedure.Parameters.Add(new StoredProcedureParameter("@StartTime", ParameterType.DBDateTime, start));
storedProcedure.Parameters.Add(new StoredProcedureParameter("@EndTime", ParameterType.DBDateTime, end));
result = storedProcedure.ExecuteDataSet();
result.TableName = "Fingerprints";
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
}
return(result);
}
public DataTable SaveSelfAuditOld(int auditGroupID, int divisionID, int staffID_Responsible, int percentage, string auditName, int userID)
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Self.Save.Old]"
};
var paramAuditGroupID =
new StoredProcedureParameter("@AuditGroupID", ParameterType.DBInteger, auditGroupID);
var paramDivisionID =
new StoredProcedureParameter("@DivisionId", ParameterType.DBInteger, divisionID);
var paramStaffID_Responsible =
new StoredProcedureParameter("@StaffID_Responsible", ParameterType.DBInteger, staffID_Responsible);
var paramPercentage =
new StoredProcedureParameter("@Percentage", ParameterType.DBInteger, percentage);
var paramAuditName =
new StoredProcedureParameter("@AuditName", ParameterType.DBString, auditName);
var paramUserID =
new StoredProcedureParameter("@UserID", ParameterType.DBInteger, userID);
storedProcedure.Parameters.Add(paramAuditGroupID);
storedProcedure.Parameters.Add(paramDivisionID);
storedProcedure.Parameters.Add(paramStaffID_Responsible);
storedProcedure.Parameters.Add(paramPercentage);
storedProcedure.Parameters.Add(paramAuditName);
storedProcedure.Parameters.Add(paramUserID);
result = storedProcedure.ExecuteDataSet();
return(result);
}
public DataTable DoorInfoByBadgeID_LocationID_ReaderID(int badgeID, int locationID, int readerID, string accessType = "%")
{
DataTable result = null;
var storedProcedure = new StoredProcedure()
{
StoredProcedureName = _Schema + ".[SAAU.ProblemBadge.DoorInfoByBadgeID_LocationID_ReaderID]"
};
StoredProcedureParameter paramBadgeID = new StoredProcedureParameter("@BadgeID", ParameterType.DBInteger, badgeID);
storedProcedure.Parameters.Add(paramBadgeID);
StoredProcedureParameter paramLocationID = new StoredProcedureParameter("@LocationID", ParameterType.DBInteger, locationID);
storedProcedure.Parameters.Add(paramLocationID);
StoredProcedureParameter paramReaderID = new StoredProcedureParameter("@ReaderID", ParameterType.DBInteger, readerID);
storedProcedure.Parameters.Add(paramReaderID);
StoredProcedureParameter paramAccessType = new StoredProcedureParameter("@AccessType", ParameterType.DBString, accessType);
storedProcedure.Parameters.Add(paramAccessType);
result = storedProcedure.ExecuteDataSet();
return(result);
}
static public DataTable selectUserId(string email)
{
SubSonic.Schema.StoredProcedure sp = new StoredProcedure("aspnet_Membership_GetUserId");
sp.Command.AddParameter("@Username", email, DbType.String);
return(sp.ExecuteDataSet().Tables[0]);
}
public static DataTable HasLocalAccount(string UserId)
{
SubSonic.Schema.StoredProcedure sp = new StoredProcedure("HasLocalAccount");
sp.Command.AddParameter("@UserId", UserId, DbType.String);
return(sp.ExecuteDataSet().Tables[0]);
}
public bool AuditFormSave(DataSet ds, int userID)
{
const int tblDivisions = 0;
const int tblBadges = 1;
StoredProcedure storedProcedure;
StoredProcedureParameter paramAuditBadgeTableType;
var paramAuditDivisionTable = new StoredProcedureParameter();
var paramUserID = new StoredProcedureParameter();
bool result;
try
{
storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Editor.Save]"
};
paramAuditDivisionTable = new StoredProcedureParameter
{
Name = "@AuditDivisionTable",
DBValueType = ParameterType.Structured,
Value = ds.Tables[tblDivisions]
};
//copy and drop the note date column which is not a part of the user defined table type
DataTable table = ds.Tables[tblBadges].Copy();
//if (table.Columns.Contains("NoteDate"))
// table.Columns.Remove("NoteDate");
paramAuditBadgeTableType = new StoredProcedureParameter
{
Name = "@AuditBadgeTable",
DBValueType = ParameterType.Structured,
Value = table
};
paramUserID = new StoredProcedureParameter
{
Name = "@UserID",
DBValueType = ParameterType.DBString,
Value = userID
};
storedProcedure.Parameters.Add(paramAuditDivisionTable);
storedProcedure.Parameters.Add(paramAuditBadgeTableType);
storedProcedure.Parameters.Add(paramUserID);
storedProcedure.ExecuteDataSet();
result = true;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
//JBienvenu 2013-01-10 new
public DataTable getStaff(string facilityCode)
{
DataTable ret = null;
var storedProcedure = new StoredProcedure();
storedProcedure.StoredProcedureName = schema + ".[Data.FacilityStaffList.Load]";
storedProcedure.Parameters.Add(new StoredProcedureParameter("@FacilityCode", ParameterType.DBString, facilityCode));
ret = storedProcedure.ExecuteDataSet();
return(ret);
}
public DataSet GetUserEventReportNew(int UserID, int OrganisationID)
{
using (StoredProcedure sp = new StoredProcedure("prcEvent_UserEventReport",
StoredProcedure.CreateInputParam("@UserID", SqlDbType.Int, UserID),
StoredProcedure.CreateInputParam("@OrganisationID", SqlDbType.Int, OrganisationID)
))
{
return(sp.ExecuteDataSet());
}
}
public DataTable LoadNotes(int divisionId)
{
var storedProcedure = new StoredProcedure();
storedProcedure.StoredProcedureName = _Schema + ".[Data.Division.Notes]";
storedProcedure.Parameters.Add(new StoredProcedureParameter("@DivisionID", ParameterType.DBInteger, divisionId));
return(storedProcedure.ExecuteDataSet());
}
public byte[] GetDocumentImage(int documentID)
{
var storedProcedure = new StoredProcedure();
storedProcedure.StoredProcedureName = schema + ".[Badging.CompanyDivision.PersonDocument]";
storedProcedure.Parameters.Add(new StoredProcedureParameter("@DocumentID", ParameterType.DBInteger, documentID));
var result = storedProcedure.ExecuteDataSet().Rows;
if (result.Count == 0)
{
return(null);
}
else
{
return(storedProcedure.ExecuteDataSet().Rows[0][0] as byte[]);
}
}
public DataTable getWorkLocations(string facilityCode)
{
DataTable ret = null;
var storedProcedure = new StoredProcedure();
storedProcedure.StoredProcedureName = schema + ".[Data.FacilityLocationsForm.Load]";
storedProcedure.Parameters.Add(new StoredProcedureParameter("@facilitycode", ParameterType.DBString, facilityCode));
ret = storedProcedure.ExecuteDataSet();
return(ret);
}
public DataTable GetCompanies(int userID)
{
var storedProcedure = new StoredProcedure();
storedProcedure.StoredProcedureName = schema + ".[Security.OASCTDisplayCompanies]";
storedProcedure.Parameters.Add(new StoredProcedureParameter("@userID", ParameterType.DBInteger, userID));
return(storedProcedure.ExecuteDataSet());
}
public DataTable GetCompanyList()
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Lists.Companies]"
};
result = storedProcedure.ExecuteDataSet();
return(result);
}
} // UploadToolbookXML
/// <summary>
/// This method previews the new XML content without loading it into the database.
/// It validates that the xml content is valid in compared to the existing salt database content.
/// </summary>
/// <remarks>
/// Assumptions: None
/// Notes:
/// Author: Peter Kneale 3/02/2004
/// Changes:
/// </remarks>
/// <param name="contentXml">A string containg the new xml content</param>
/// <param name="moduleID">The module for which this content is to be used.</param>
/// <returns>
/// DataSet containing the results of the attempted validation. See
/// See the stored procedure for more information
/// </returns>
public DataSet UploadContentObjectXMLPreview(string contentXml, int moduleID, int userID)
{
using (StoredProcedure sp = new StoredProcedure("prcToolbook_Preview",
StoredProcedure.CreateInputParam("@strXMLData", SqlDbType.Text, contentXml),
StoredProcedure.CreateInputParam("@intModuleID", SqlDbType.Int, moduleID),
StoredProcedure.CreateInputParam("@intUserID", SqlDbType.Int, userID)
))
{
return(sp.ExecuteDataSet());
}
} //UploadToolbookXMLPreview
//JBienvenu 18567 2013-01-24 new method
public DataTable AuditEditorLetterLoad(int auditSpecificationID, int divisionID)
{
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Editor.Letter.Load]"
};
storedProcedure.Parameters.Add(new StoredProcedureParameter("@AuditSpecificationID", ParameterType.DBInteger, auditSpecificationID));
storedProcedure.Parameters.Add(new StoredProcedureParameter("@DivisionID", ParameterType.DBInteger, divisionID));
return(storedProcedure.ExecuteDataSet());
}
public DataTable GetTotalBadgeInspec()
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.TotalAuditCount]"
};
result = storedProcedure.ExecuteDataSet();
return(result);
}
public DataTable GetAuditInspectionTypesList()
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.Lists.InspectionTypes]"
};
result = storedProcedure.ExecuteDataSet();
return(result);
}
public DataTable GetAuditSpecificationList()
{
DataTable result = null;
var storedProcedure = new StoredProcedure {
StoredProcedureName = _schema + ".[Audit.List.Specifications]"
};
result = storedProcedure.ExecuteDataSet();
return(result);
}
/// <summary>
/// Data retrieval
/// </summary>
/// <returns></returns>
private DataSet GenerateReturnSet()
{
DataSet result = null;
if(_url != null)
{
SqlQuery qry = null;
//Query q;
if(!String.IsNullOrEmpty(_url.TableName))
{
qry = new Select().From(_url.TableName);
IDataProvider provider = ProviderFactory.GetProvider();
ITable tbl = provider.FindTable(_url.TableName);
if(_url.PrimaryKey != null)
qry = qry.Where(tbl.PrimaryKey.Name).IsEqualTo(_url.PrimaryKey);
//q.WHERE(q.Schema.PrimaryKey.ParameterName, _url.PrimaryKey);
if(_url.Parameters != null)
{
IDictionaryEnumerator loopy = _url.Parameters.GetEnumerator();
IColumn column;
string paramName;
object paramValue;
while(loopy.MoveNext())
{
paramName = loopy.Key.ToString();
paramValue = loopy.Value;
int pageSize = 0;
int pageIndex = -1;
if(paramName.ToLowerInvariant() == "pagesize" || paramName.ToLowerInvariant() == "pageindex")
{
if(paramName.ToLowerInvariant() == "pagesize")
pageSize = int.Parse(paramValue.ToString());
if(paramName.ToLowerInvariant() == "pageindex")
pageIndex = int.Parse(paramValue.ToString());
if(pageSize > 0 && pageIndex > -1)
qry.Paged(pageIndex + 1, pageSize);
}
else
{
Comparison comp;
EvalComparison(paramName, out paramName, out comp);
column = tbl.GetColumn(paramName);
//if this column is a string, by default do a fuzzy search
if(comp == Comparison.Like || column.IsString)
{
comp = Comparison.Like;
paramValue = String.Concat("%", paramValue, "%");
qry = qry.Where(column.Name).Like(paramValue.ToString());
}
else if(paramValue.ToString().ToLower() == "null")
qry = qry.Where(column.Name).IsNull();
//q.WHERE(column.ColumnName, comp, paramValue);
}
}
}
result = provider.ExecuteDataSet(qry.GetCommand());
}
else if(!String.IsNullOrEmpty(_url.SpName))
{
StoredProcedure sp = new StoredProcedure(_url.SpName);
if(_url.Parameters != null)
{
IDictionaryEnumerator loopy = _url.Parameters.GetEnumerator();
while(loopy.MoveNext())
sp.Command.AddParameter(loopy.Key.ToString(), loopy.Value, DbType.AnsiString);
}
result = sp.ExecuteDataSet();
}
}
return result;
}
