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(); } }