public string ExportReport(string reportName, string outputPath)
{
ds = new DataSet();
conn = new DBConn.DBConn();
string sqlQuery = "";
string fileName = "";
if (reportName == "SurveyStatistics")
{
fileName = "SurveyStatistics";
sqlQuery = "spViewSurveyStatistics";
}
else if (reportName == "SurveyPercentage")
{
fileName = "SurveyPercentage";
sqlQuery = "spViewSurveyPercentage";
}
sqlQuery = "EXEC " + sqlQuery;
fileName = fileName + DateTime.Now.ToString("MMddyyyy_HHmmss") + ".csv";
ds = conn.execSQLCommand(sqlQuery);
DataTabletoCSV(ds.Tables[0], outputPath + fileName);
return(fileName);
}
private bool SaveSurvey(Survey survey, int applicantID) {
string param = "EXEC spSaveSurvey '" + applicantID + "', ";
param = param + "'" + survey.QuestionID + "', '" + survey.RateID + "','" + survey.Comment + "'";
conn = new DBConn.DBConn();
conn.execSQLCommand(param);
return true;
}
private bool SaveSurvey(Survey survey, int applicantID)
{
string param = "EXEC spSaveSurvey '" + applicantID + "', ";
param = param + "'" + survey.QuestionID + "', '" + survey.RateID + "','" + survey.Comment + "'";
conn = new DBConn.DBConn();
conn.execSQLCommand(param);
return(true);
}
private int SaveApplicant(Applicant applicant) {
ds = new DataSet();
conn = new DBConn.DBConn();
ds = conn.execSQLCommand("EXEC spSaveApplicant '" + applicant.FirstName + "', '" + applicant.LastName + "'");
if (Convert.ToInt32(ds.Tables[0].Rows.Count) > 0)
return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
else
return 0;
}
private int SaveApplicant(Applicant applicant)
{
ds = new DataSet();
conn = new DBConn.DBConn();
ds = conn.execSQLCommand("EXEC spSaveApplicant '" + applicant.FirstName + "', '" + applicant.LastName + "'");
if (Convert.ToInt32(ds.Tables[0].Rows.Count) > 0)
{
return(Convert.ToInt32(ds.Tables[0].Rows[0][0]));
}
else
{
return(0);
}
}
private int SaveApplicant(Applicant applicant)
{
ds = new DataSet();
conn = new DBConn.DBConn();
ds = conn.execSQLCommand(String.Format("EXEC spSaveApplicant '{0}', '{1}', '{2}' ", applicant.firstName, applicant.lastName, applicant.position));
if (Convert.ToInt32(ds.Tables[0].Rows.Count) > 0)
{
return(Convert.ToInt32(ds.Tables[0].Rows[0][0]));
}
else
{
return(0);
}
}
public List <string> GetReports(string reportDB, string FolderName)
{
conn = new DBConn.DBConn();
string _sql = string.Format("SELECT Name FROM [{0}].[dbo].catalog WHERE Path Like '{1}%'", reportDB, FolderName);
DataSet ds = conn.execSQLCommand(_sql);
List <string> _results = new List <string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
_results.Add(row["Name"].ToString());
}
return(_results);
}
public string GetLocalPath()
{
ds = new DataSet();
conn = new DBConn.DBConn();
string path = "";
List <ImageDetail> imageDetail = new List <ImageDetail>();
ds = conn.execSQLCommand("SELECT * FROM ImageDirectory");
if (ds.Tables[0].Rows.Count != 0)
{
path = Convert.ToString(ds.Tables[0].Rows[0][0]);
}
return(path);
}
public List<ImageDetail> GetImages() {
ds = new DataSet();
conn = new DBConn.DBConn();
List<ImageDetail> imageDetail = new List<ImageDetail>();
ds = conn.execSQLCommand("SELECT * FROM ImageDetail WHERE Active=1 ORDER BY ImageID ASC");
foreach (DataRow row in ds.Tables[0].Rows) {
imageDetail.Add(new ImageDetail {
ImageID = Convert.ToInt32(row["ImageID"]),
Location = Convert.ToString(row["Location"]),
Comment = Convert.ToString(row["Comment"]),
Width = Convert.ToInt32(row["Width"]),
Height = Convert.ToInt32(row["Height"])
});
}
return imageDetail;
}
public List<Question> GetQuestions() {
List<Question> questions = new List<Question>();
ds = new DataSet();
conn = new DBConn.DBConn();
ds = conn.execSQLCommand("SELECT * FROM ViewQuestions");
foreach (DataRow row in ds.Tables[0].Rows)
{
questions.Add(new Question {
QuestionID = Convert.ToInt32( row["questionID"]),
QuestionDescription = Convert.ToString(row["questionDescription"]),
QuestionTypeID = Convert.ToInt32(row["questionTypeID"])
});
}
return questions;
}
public List <Rate> GetRates()
{
ds = new DataSet();
conn = new DBConn.DBConn();
List <Rate> rates = new List <Rate>();
ds = conn.execSQLCommand("SELECT * FROM Rate ORDER BY rateID DESC");
foreach (DataRow row in ds.Tables[0].Rows)
{
rates.Add(new Rate {
RateID = Convert.ToInt32(row["rateID"]),
RateDescription = Convert.ToString(row["rateDescription"]),
ImageLocation = Convert.ToString(row["imageLocation"])
});
}
return(rates);
}
public List <Question> GetQuestions()
{
List <Question> questions = new List <Question>();
ds = new DataSet();
conn = new DBConn.DBConn();
ds = conn.execSQLCommand("SELECT * FROM ViewQuestions");
foreach (DataRow row in ds.Tables[0].Rows)
{
questions.Add(new Question {
QuestionID = Convert.ToInt32(row["questionID"]),
QuestionDescription = Convert.ToString(row["questionDescription"]),
QuestionTypeID = Convert.ToInt32(row["questionTypeID"])
});
}
return(questions);
}
public List <ImageDetail> GetImages()
{
ds = new DataSet();
conn = new DBConn.DBConn();
List <ImageDetail> imageDetail = new List <ImageDetail>();
ds = conn.execSQLCommand("SELECT * FROM ImageDetail WHERE Active=1 ORDER BY ImageID ASC");
foreach (DataRow row in ds.Tables[0].Rows)
{
imageDetail.Add(new ImageDetail {
ImageID = Convert.ToInt32(row["ImageID"]),
Location = Convert.ToString(row["Location"]),
Comment = Convert.ToString(row["Comment"]),
Width = Convert.ToInt32(row["Width"]),
Height = Convert.ToInt32(row["Height"])
});
}
return(imageDetail);
}
public string ExportReport(string reportName, string outputPath)
{
ds = new DataSet();
conn = new DBConn.DBConn();
string sqlQuery = "";
string fileName = "";
if (reportName == "SurveyStatistics") {
fileName = "SurveyStatistics";
sqlQuery = "spViewSurveyStatistics";
}
else if (reportName == "SurveyPercentage") {
fileName = "SurveyPercentage";
sqlQuery = "spViewSurveyPercentage";
}
sqlQuery = "EXEC " + sqlQuery;
fileName = fileName + DateTime.Now.ToString("MMddyyyy_HHmmss") + ".csv";
ds = conn.execSQLCommand(sqlQuery);
DataTabletoCSV(ds.Tables[0], outputPath + fileName);
return fileName;
}
public string GetLocalPath()
{
ds = new DataSet();
conn = new DBConn.DBConn();
string path = "";
List<ImageDetail> imageDetail = new List<ImageDetail>();
ds = conn.execSQLCommand("SELECT * FROM ImageDirectory");
if (ds.Tables[0].Rows.Count != 0) {
path = Convert.ToString(ds.Tables[0].Rows[0][0]);
}
return path;
}
private int SaveApplicant(Applicant applicant)
{
ds = new DataSet();
conn = new DBConn.DBConn();
ds = conn.execSQLCommand(String.Format("EXEC spSaveApplicant '{0}', '{1}', '{2}' ", applicant.firstName, applicant.lastName, applicant.position));
if (Convert.ToInt32(ds.Tables[0].Rows.Count) > 0)
return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
else
return 0;
}
public List<string> GetReports(string reportDB, string FolderName)
{
conn = new DBConn.DBConn();
string _sql = string.Format("SELECT Name FROM [{0}].[dbo].catalog WHERE Path Like '{1}%'", reportDB, FolderName);
DataSet ds = conn.execSQLCommand(_sql);
List<string> _results = new List<string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
_results.Add(row["Name"].ToString());
}
return _results;
}
public List<Rate> GetRates()
{
ds = new DataSet();
conn = new DBConn.DBConn();
List<Rate> rates = new List<Rate>();
ds = conn.execSQLCommand("SELECT * FROM Rate ORDER BY rateID DESC");
foreach (DataRow row in ds.Tables[0].Rows) {
rates.Add(new Rate {
rateID = Convert.ToInt32(row["rateID"]),
rateDescription = Convert.ToString(row["rateDescription"]),
imageLocation = Convert.ToString(row["imageLocation"])
});
}
return rates;
}