public bool Load(string key, string svcode)
{
bool rtn = false;
string sql = "SELECT * FROM prd_ans WHERE id='" + key + "' OR (email='" + key + "' and svcode='" + svcode + "')";
YPSCS.Framework.Data.SQLiteData dc = new Framework.Data.SQLiteData();
dc.CommandText = sql;
dc.Connection.Open();
IDataReader dr = dc.ExecuteReader();
if (dr.Read())
{
SurveyCode = dr["svcode"].ToString();
Company = dr["company"].ToString();
Department = dr["department"].ToString();
Email = dr["email"].ToString();
Name = dr["name"].ToString();
People = dr["people"].ToString();
Tel = dr["tel"].ToString();
Title = dr["title"].ToString();
ID = dr["id"].ToString();
rtn = true;
}
dr.Close();
dr.Dispose();
sql = "SELECT * FROM prd_ansdetail WHERE svid='" + ID + "'";
dc.CommandText = sql;
SurveyData = dc.Execute().Tables[0];
dc.Connection.Close();
dc.Connection.Dispose();
return(rtn);
}
public void SaveDetail(int seqs, int seqe)
{
StringBuilder sb = new StringBuilder();
if (SurveyData != null && SurveyData.Rows.Count > 0)
{
YPSCS.Framework.Data.SQLiteData dc = new Framework.Data.SQLiteData();
dc.Parameter.Clear();
dc.Parameter.Add("svid", DbType.String).Value = ID;
sb.Append("DELETE FROM prd_ansdetail WHERE svid=:svid AND seq between '" + seqs.ToString() + "' AND '" + seqe.ToString() + "';");
for (int x = 0; x < SurveyData.Rows.Count; x++)
{
sb.Append("INSERT INTO prd_ansdetail (svid,seq,ans) VALUES (:svid,:seq" + x.ToString() + ",:ans" + x.ToString() + ");");
dc.Parameter.Add("seq" + x.ToString(), DbType.Int32).Value = int.Parse(SurveyData.Rows[x]["seq"].ToString());
dc.Parameter.Add("ans" + x.ToString(), DbType.String).Value = SurveyData.Rows[x]["ans"].ToString();
}
sb.Append("UPDATE prd_ans set modifydate=datetime('now','+8 Hours') WHERE id='" + ID + "';");
dc.CommandText = sb.ToString();
dc.Connection.Open();
dc.ExecuteNonQuery();
dc.Connection.Close();
dc.Connection.Dispose();
}
}
public static DataTable LoadQuestion(string svcode, int seqs, int seqe)
{
string sql = "SELECT * FROM prd_surveydetail WHERE svcode='" + svcode + "' and seq between '" + seqs.ToString() + "' and '" + seqe.ToString() + "'";
YPSCS.Framework.Data.SQLiteData dc = new Framework.Data.SQLiteData();
dc.CommandText = sql;
dc.Connection.Open();
DataTable dt = dc.Execute().Tables[0];
return(dt);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
YPSCS.Framework.Data.SQLiteData dc = new Framework.Data.SQLiteData();
string sql = "SELECT svcode,svname FROM prd_survey ORDER BY svcode";
dc.CommandText = sql;
dc.Connection.Open();
DataTable dt = dc.Execute().Tables[0];
dc.Connection.Close();
dc.Connection.Dispose();
this.Query_Code.DataSource = dt;
this.Query_Code.DataTextField = "svname";
this.Query_Code.DataValueField = "svcode";
this.Query_Code.DataBind();
this.Query_Code.Items.Insert(0, new System.Web.UI.WebControls.ListItem("- 未指定 -", "-NULL-"));
}
//QueryData();
}
private void ExportSurvey()
{
YPSCS.Framework.Data.SQLiteData dc = new Framework.Data.SQLiteData();
string sql = "SELECT X.svname,Y.maxseq FROM prd_survey X INNER JOIN (SELECT svcode,MAX(seq) maxseq FROM prd_surveydetail GROUP BY svcode) Y ON Y.svcode=X.svcode WHERE X.svcode='0000'";
string svname = "";
double maxseq = 0;
dc.CommandText = sql;
dc.Connection.Open();
IDataReader drarg = dc.ExecuteReader();
if (drarg.Read())
{
svname = drarg["svname"].ToString();
maxseq = int.Parse(drarg["maxseq"].ToString());
}
drarg.Close();
drarg.Dispose();
if (!String.IsNullOrEmpty(svname))
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet[] sheet = new NPOI.SS.UserModel.ISheet[(int)Math.Ceiling(maxseq / 256)];
sql = "SELECT seq,title FROM prd_surveydetail WHERE svcode='0000' ORDER BY seq";
dc.CommandText = sql;
DataTable dt = dc.Execute().Tables[0];
int rowidx = 0;
int sheetidx = 0;
for (int x = 0; x < maxseq; x++)
{
int idx = x % 256;
if (idx == 0)
{
sheetidx = (int)Math.Floor((double)x / 256);
sheet[sheetidx] = workbook.CreateSheet(svname + "_" + sheetidx.ToString());
sheet[sheetidx].CreateRow(0).CreateCell(idx).SetCellValue(dt.Rows[rowidx]["title"].ToString());
rowidx++;
}
else if ((x + 1).ToString() == dt.Rows[rowidx]["seq"].ToString())
{
sheet[sheetidx].GetRow(0).CreateCell(idx).SetCellValue(dt.Rows[rowidx]["title"].ToString());
rowidx++;
}
}
sql = "SELECT id,svcode,company,department,name,tel,title,email,people,language,modifydate FROM prd_ans ORDER BY modifydate";
dc.CommandText = sql;
dt.Clear();
dt = dc.Execute().Tables[0];
for (int x = 0; x < dt.Rows.Count; x++)
{
int ansidx = x + 1;
sheetidx = 0;
sheet[sheetidx].CreateRow(ansidx).CreateCell(0).SetCellValue(ansidx.ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(1).SetCellValue(dt.Rows[x]["email"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(2).SetCellValue(dt.Rows[x]["language"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(3).SetCellValue(dt.Rows[x]["company"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(4).SetCellValue(dt.Rows[x]["department"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(5).SetCellValue(dt.Rows[x]["name"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(6).SetCellValue(dt.Rows[x]["tel"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(7).SetCellValue(dt.Rows[x]["title"].ToString());
sheet[sheetidx].GetRow(ansidx).CreateCell(8).SetCellValue(dt.Rows[x]["people"].ToString());
if (dt.Rows[x]["svcode"].ToString() == "0000")
{
sheet[sheetidx].GetRow(ansidx).CreateCell(24).SetCellValue("中文");
}
else
{
sheet[sheetidx].GetRow(ansidx).CreateCell(24).SetCellValue("英文");
}
sheet[sheetidx].GetRow(ansidx).CreateCell(25).SetCellValue(dt.Rows[x]["modifydate"].ToString());
sql = "SELECT seq,ans FROM prd_ansdetail WHERE svid='" + dt.Rows[x]["id"].ToString() + "' ORDER BY seq";
dc.CommandText = sql;
IDataReader dr = dc.ExecuteReader();
while (dr.Read())
{
int seq = int.Parse(dr["seq"].ToString()) - 1;
int idx = seq % 256;
sheetidx = (int)Math.Floor((double)seq / 256);
if (idx == 0)
{
sheet[sheetidx].CreateRow(ansidx).CreateCell(idx).SetCellValue(dr["ans"].ToString());
}
else
{
if (sheet[sheetidx].GetRow(ansidx) != null)
{
sheet[sheetidx].GetRow(ansidx).CreateCell(idx).SetCellValue(dr["ans"].ToString());
}
else
{
sheet[sheetidx].CreateRow(ansidx).CreateCell(idx).SetCellValue("");
}
}
}
dr.Close();
dr.Dispose();
}
string filename = AppDomain.CurrentDomain.BaseDirectory + "\\" + svname + ".xls";
var file = new System.IO.FileStream(filename, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
System.Net.WebClient wc = new System.Net.WebClient();
byte[] bfile = null;
try
{
string sitepath = Request.Url.AbsoluteUri;
bfile = wc.DownloadData(filename);
}
catch (Exception ex)
{
Response.Write("<script>alert('找不到檔案!!')</script>");
return;
}
HttpContext.Current.Response.Clear();
//跳出視窗，讓用戶端選擇要儲存的地方 //使用Server.UrlEncode()編碼中文字才不會下載時，檔名為亂碼
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(svname + ".xls"));
//設定MIME類型為二進位檔案
HttpContext.Current.Response.ContentType = "application/octet-stream";
try
{
HttpContext.Current.Response.BinaryWrite(bfile);
}
catch (Exception ex)
{
HttpContext.Current.Response.Write("檔案輸出有誤，您可以在瀏覽器的URL網址貼上以下路徑嘗試看看。<br/>檔案路徑：" + filename + "<br/>錯誤訊息：" + ex.ToString());
return;
}
HttpContext.Current.Response.End();
}
}
public void Create()
{
if (!String.IsNullOrEmpty(SurveyCode) && !String.IsNullOrEmpty(Email))
{
YPSCS.Framework.Data.SQLiteData dc = new Framework.Data.SQLiteData();
dc.Parameter.Clear();
string sql = "DELETE FROM prd_ans WHERE id=:svid;";
if (!String.IsNullOrEmpty(ID))
{
dc.Parameter.Add("svid", DbType.String).Value = ID;
}
else
{
dc.Parameter.Add("svid", DbType.String).Value = DBNull.Value;
}
dc.CommandText = sql;
dc.Connection.Open();
dc.ExecuteNonQuery();
sql = "INSERT INTO prd_ans (id,svcode,email,company,department,name,people,tel,title,modifydate,language) VALUES (:svid,:svcode,:email,:company,:department,:name,:people,:tel,:title,datetime('now','+8 Hours'),:language)";
if (!String.IsNullOrEmpty(SurveyCode))
{
dc.Parameter.Add("svcode", DbType.String).Value = SurveyCode;
}
else
{
dc.Parameter.Add("svcode", DbType.String).Value = DBNull.Value;
}
if (!String.IsNullOrEmpty(Email))
{
dc.Parameter.Add("email", DbType.String).Value = Email;
}
else
{
dc.Parameter.Add("email", DbType.String).Value = DBNull.Value;
}
if (!String.IsNullOrEmpty(Company))
{
dc.Parameter.Add("company", DbType.String).Value = Company;
}
else
{
dc.Parameter.Add("company", DbType.String).Value = DBNull.Value;
}
if (!String.IsNullOrEmpty(Department))
{
dc.Parameter.Add("department", DbType.String).Value = Department;
}
else
{
dc.Parameter.Add("department", DbType.String).Value = DBNull.Value;
}
if (!String.IsNullOrEmpty(Name))
{
dc.Parameter.Add("name", DbType.String).Value = Name;
}
else
{
dc.Parameter.Add("name", DbType.String).Value = DBNull.Value;
}
if (!String.IsNullOrEmpty(People))
{
dc.Parameter.Add("people", DbType.Int32).Value = int.Parse(People.Trim());
}
else
{
dc.Parameter.Add("people", DbType.Int32).Value = DBNull.Value;
}
if (!String.IsNullOrEmpty(Tel))
{
dc.Parameter.Add("tel", DbType.String).Value = Tel;
}
else
{
dc.Parameter.Add("tel", DbType.String).Value = DBNull.Value;
}
if (!String.IsNullOrEmpty(Title))
{
dc.Parameter.Add("title", DbType.String).Value = Title;
}
else
{
dc.Parameter.Add("title", DbType.String).Value = DBNull.Value;
}
if (!String.IsNullOrEmpty(Language))
{
dc.Parameter.Add("language", DbType.String).Value = Language;
}
else
{
dc.Parameter.Add("language", DbType.String).Value = DBNull.Value;
}
dc.CommandText = sql;
dc.ExecuteNonQuery();
dc.Connection.Close();
dc.Connection.Dispose();
}
}
