protected void Page_Load(object sender, EventArgs e) { if (!WebUtils.CheckAccess(Response, Session)) { return; } if (Request.QueryString[FILE_PATH_STRING] != null) { DBAESEncryptStringFieldAttribute decrypt = new DBAESEncryptStringFieldAttribute(Session.SessionID); string tmpFilePath = decrypt.fromDB(Request.QueryString[FILE_PATH_STRING]).ToString(); if (File.Exists(tmpFilePath)) { string filename = string.Empty; if (Request.QueryString[FILE_PATH_STRING] != null) { filename = decrypt.fromDB(Request.QueryString[FILE_NAME_STRING]).ToString(); } else { filename = new FileInfo(tmpFilePath).Name; } bool blnRemoveAfterDownload = false; if (Request.QueryString[REMOVE_FILE_STRING] != null) { blnRemoveAfterDownload = (Request.QueryString[REMOVE_FILE_STRING]).ToString().Equals("Y", StringComparison.CurrentCultureIgnoreCase); } WebUtils.TransmitFile(Response, tmpFilePath, filename, blnRemoveAfterDownload); } } }
public static DataTable DataTableSortingAndPaging(DataTable sourceTable, ListInfo info) { DataTable table = sourceTable.Copy(); if (info != null) { if (!string.IsNullOrEmpty(info.orderby)) { string[] orderbyList = info.orderby.Split(new char[] { ',' }); string actualOrderString = string.Empty; foreach (string orderBy in orderbyList) { DBAESEncryptStringFieldAttribute.decode(table, orderBy.Trim()); string orderString = orderBy + (info.order ? "" : " DESC"); if (string.IsNullOrEmpty(actualOrderString)) { actualOrderString = orderString; } else { actualOrderString += "," + orderString; } } DataView resultView = new DataView(table); resultView.Sort = actualOrderString; table = resultView.ToTable(); } if (info.recordPerPage > 0) { info.numRecord = table.Rows.Count; info.numPage = info.numRecord / info.recordPerPage; if (info.numRecord % info.recordPerPage > 0) { info.numPage++; } if (info.page == info.numPage && info.numPage > 0) { info.page--; } int startIndex = info.recordPerPage * (info.page); int endIndex = info.recordPerPage * (info.page + 1) - 1; for (int i = table.Rows.Count - 1; i >= 0; i--) { if (i < startIndex || i > endIndex) { table.Rows.Remove(table.Rows[i]); } } } else { info.numRecord = table.Rows.Count; } } return(table); }
// Start 0000044, Miranda, 2014-05-09 //protected string getNextEmpNo(string empNoFormat) //{ // if (empNoFormat != null) // { // string empNoFlt = ""; // bool isStartWithNumber = false; // int firstNumIndex = empNoFormat.IndexOf('9'); // if (firstNumIndex == 0)// start with number // { // isStartWithNumber = true; // empNoFlt = empNoFormat.Substring(empNoFormat.LastIndexOf('9') + 1); // } // else // { // empNoFlt = empNoFormat.Substring(0, firstNumIndex); // } // SearchBinding empNoBinding = new SearchBinding(dbConn, db); // empNoBinding.init(DecryptedRequest, null); // DBFilter filter = empNoBinding.createFilter(); // string select = "c.EmpNo "; // string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] c "; // DataTable table = filter.loadData(dbConn, null, select, from); // DBAESEncryptStringFieldAttribute.decode(table, "EmpNo"); // DataView view = new DataView(table); // view.RowFilter = " EmpNo like '" + (isStartWithNumber ? ("%" + empNoFlt) : (empNoFlt + "%")) + "' "; // view.Sort = "EmpNo DESC"; // table = view.ToTable(); // String zeroStr = ""; // int nextEmpNum = 1; // if (table.Rows.Count > 0) // { // int rowCount = table.Rows.Count; // string maxEmpNo = null; // for (int i = 0; i < rowCount; i++) // { // maxEmpNo = (string)table.Rows[i]["EmpNo"]; // if (isAlignToDefinedFormat(maxEmpNo, empNoFormat)) // { // break; // } // } // if (maxEmpNo != null) // { // string numStr = ""; // if (isStartWithNumber) // { // numStr = maxEmpNo.Substring(0, maxEmpNo.Length - empNoFlt.Length); // } // else // { // numStr = maxEmpNo.Substring(empNoFlt.Length); // } // nextEmpNum = 1 + Convert.ToInt32(numStr); // } // else // { // nextEmpNum = rowCount + 1; // } // } // int j = empNoFormat.Length - (Convert.ToString(nextEmpNum).Length + empNoFlt.Length); // for (int i = 0; i < j; i++) // { // zeroStr += "0"; // } // return isStartWithNumber ? (zeroStr + Convert.ToString(nextEmpNum) + empNoFlt) : (empNoFlt + zeroStr + Convert.ToString(nextEmpNum)); // } // else // { // return ""; // } //} protected string getNextEmpNo(string empNoFormat) { if (empNoFormat != null) { SearchBinding empNoBinding = new SearchBinding(dbConn, db); empNoBinding.init(DecryptedRequest, null); DBFilter filter = empNoBinding.createFilter(); string select = "c.EmpNo "; string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] c "; DataTable table = filter.loadData(dbConn, null, select, from); DBAESEncryptStringFieldAttribute.decode(table, "EmpNo"); DataView view = new DataView(table); view.RowFilter = " EmpNo <= '" + empNoFormat + "' "; view.Sort = "EmpNo DESC"; table = view.ToTable(); string m_prefix = empNoFormat.Replace("9", ""); string m_currentEmpNo; string m_currentNo; string m_currentPrefix = ""; int m_currentNoValue = 0; // numeric value of m_currentNo string m_nextEmpNo = ""; int m_newNoValue = 0; if (table.Rows.Count > 0) { m_currentEmpNo = table.Rows[0][0].ToString(); } else { m_currentEmpNo = empNoFormat.Replace("9", "0"); } string m_numberFormat = empNoFormat.Substring(m_prefix.Length).Replace("9", "0"); m_currentPrefix = m_currentEmpNo.Substring(0, m_prefix.Length); m_currentNo = m_currentEmpNo.Substring(m_prefix.Length); int.TryParse(m_currentNo, out m_currentNoValue); if (isAlignToDefinedFormat(m_currentEmpNo, empNoFormat)) { m_newNoValue = m_currentNoValue + 1; } else { m_newNoValue = 1; } m_nextEmpNo = m_prefix + (m_newNoValue).ToString(m_numberFormat); return(m_nextEmpNo); } else { return(""); } }
//public DataTable FilterEncryptedEmpInfoField(DataTable table) //{ // return FilterEncryptedEmpInfoField(table, null); //} public DataTable FilterEncryptedEmpInfoField(DataTable table, ListInfo info) { //binding.add(new LikeSearchBinder(EmpNo, "EmpNo")); //binding.add(new LikeSearchBinder(EmpEngSurname, "EmpEngSurname")); //binding.add(new LikeSearchBinder(EmpEngOtherName, "EmpEngOtherName")); //binding.add(new LikeSearchBinder(EmpChiFullName, "EmpChiFullName")); //binding.add(new LikeSearchBinder(EmpAlias, "EmpAlias")); //binding.add(new DropDownVLSearchBinder(EmpGender, "EmpGender", Values.VLGender).setLocale(ci)); if (!string.IsNullOrEmpty(EmpNo.Text)) { DBAESEncryptStringFieldAttribute.decode(table, "EmpNo"); DataView view = new DataView(table); view.RowFilter = "EmpNo like '%" + EmpNo.Text.Trim().Replace("'", "''") + "%' "; table = view.ToTable(); } if (!string.IsNullOrEmpty(EmpEngSurname.Text)) { DBAESEncryptStringFieldAttribute.decode(table, "EmpEngSurname"); DataView view = new DataView(table); view.RowFilter = "EmpEngSurname like '%" + EmpEngSurname.Text.Trim().Replace("'", "''") + "%' "; table = view.ToTable(); } if (!string.IsNullOrEmpty(EmpEngOtherName.Text)) { DBAESEncryptStringFieldAttribute.decode(table, "EmpEngOtherName"); DataView view = new DataView(table); view.RowFilter = "EmpEngOtherName like '%" + EmpEngOtherName.Text.Trim().Replace("'", "''") + "%' "; table = view.ToTable(); } if (!string.IsNullOrEmpty(EmpChiFullName.Text)) { DBAESEncryptStringFieldAttribute.decode(table, "EmpChiFullName"); DataView view = new DataView(table); view.RowFilter = "EmpChiFullName like '%" + EmpChiFullName.Text.Trim().Replace("'", "''") + "%' "; table = view.ToTable(); } if (!string.IsNullOrEmpty(EmpAlias.Text)) { DBAESEncryptStringFieldAttribute.decode(table, "EmpAlias"); DataView view = new DataView(table); view.RowFilter = "EmpAlias like '%" + EmpAlias.Text.Trim().Replace("'", "''") + "%' "; table = view.ToTable(); } if (!string.IsNullOrEmpty(EmpGender.SelectedValue)) { DBAESEncryptStringFieldAttribute.decode(table, "EmpGender"); DataView view = new DataView(table); view.RowFilter = "EmpGender = '" + EmpGender.SelectedValue.Replace("'", "''") + "' "; table = view.ToTable(); } if (!string.IsNullOrEmpty(EmpHKID.Text)) { DBAESEncryptStringFieldAttribute.decode(table, "EmpHKID"); DataView view = new DataView(table); view.RowFilter = "EmpHKID like '%" + EmpHKID.Text.Trim().Replace("'", "''") + "%' "; table = view.ToTable(); } if (!string.IsNullOrEmpty(EmpPassportNo.Text)) { DBAESEncryptStringFieldAttribute.decode(table, "EmpPassportNo"); DataView view = new DataView(table); view.RowFilter = "EmpPassportNo like '%" + EmpPassportNo.Text.Trim().Replace("'", "''") + "%' "; table = view.ToTable(); } if (!string.IsNullOrEmpty(info.orderby)) { if (info.orderby.Equals("EmpEngFullName", StringComparison.CurrentCultureIgnoreCase)) { if (!table.Columns.Contains("EmpEngFullName")) { table.Columns.Add("EmpEngFullName", typeof(string)); foreach (System.Data.DataRow row in table.Rows) { if (row["EmpID"] != DBNull.Value) { EEmpPersonalInfo empInfo = new EEmpPersonalInfo(); empInfo.EmpID = (int)row["EmpID"]; if (EEmpPersonalInfo.db.select(dbConn, empInfo)) { row["EmpEngFullName"] = empInfo.EmpEngFullName; } } } } } } return(WebUtils.DataTableSortingAndPaging(table, info)); }
protected DataView loadData(ListInfo info, DBManager db, Repeater repeater) { DBFilter filter = detailBinding.createFilter(); if (info != null && info.orderby != null && !info.orderby.Equals("")) { filter.add(info.orderby, info.order); } else { filter.add("emp.EmpNo", true); } filter.add(new Match("d.BatchID", CurID)); //filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true)); /**************************************************************************** * SELECT d.BatchID, d.DetailID, emp.EmpNo, emp.EmpEngSurname, emp.EmpEngOthername, emp.EmpAlias, d.SchemeCode, d.Capacity, d.CurrentPoint, d.NewPoint * FROM [PS_SalaryIncrementBatchDetail] AS d * INNER JOIN [EmpPersonalInfo] AS emp ON d.EmpID = emp.EmpID ****************************************************************************/ string select = "d.BatchID, d.DetailID, emp.EmpID, emp.EmpNo, emp.EmpEngSurname, emp.EmpEngOthername, emp.EmpAlias, d.SchemeCode, d.Capacity, d.CurrentPoint, d.NewPoint "; string from = "FROM [PS_SalaryIncrementBatchDetail] AS d " + "INNER JOIN [EmpPersonalInfo] AS emp ON d.EmpID = emp.EmpID "; DataTable table = filter.loadData(dbConn, null, select, from); foreach (DataColumn col in table.Columns) { if (col.DataType.Equals(typeof(string))) { DBAESEncryptStringFieldAttribute.decode(table, col.ColumnName); } } //table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info); view = new DataView(table); ListFooter.Refresh(); if (repeater != null) { repeater.DataSource = view; repeater.DataBind(); } // get BackpayProcess ESalaryIncrementBatch m_process = ESalaryIncrementBatch.GetObject(dbConn, CurID); if (m_process != null) { EUser m_user; int m_id; UploadBy.Text = ""; ConfirmBy.Text = ""; if (m_process.UploadBy > 0) { m_user = EUser.GetObject(dbConn, m_process.UploadBy); if (m_user != null) { UploadBy.Text = m_user.UserName; } } if (m_process.ConfirmBy > 0) { m_user = EUser.GetObject(dbConn, m_process.ConfirmBy); if (m_user != null) { ConfirmBy.Text = m_user.UserName; } } } return(view); }
public static DataTable GetDataTableFromSelectQueryWithFilter(DatabaseConnection dbConn, string select, string from, DBFilter filter, ListInfo info) { DBFilter queryFilter = new DBFilter(); ArrayList afterFilterList = new ArrayList(); foreach (DBTerm term in filter.terms()) { if (term is Match) { Match match = (Match)term; if (match.value is string && match.op.Equals("Like", StringComparison.CurrentCultureIgnoreCase)) { afterFilterList.Add(term); } else { queryFilter.add(term); } } else { queryFilter.add(term); } } DataTable table = null; if (dbConn != null) { table = queryFilter.loadData(dbConn, null, select, from, null); } //else //{ // dbConn = DatabaseConnection.GetDatabaseConnection(); // table = queryFilter.loadData(dbConn, null, select, from); //} foreach (Match match in afterFilterList) { DBAESEncryptStringFieldAttribute.decode(table, match.name); DataView view = new DataView(table); view.RowFilter = match.name + " " + (string.IsNullOrEmpty(match.op) ? "=" : match.op) + " '" + ((string)match.value).Replace("'", "''") + "' "; table = view.ToTable(); } if (info != null) { //if (!string.IsNullOrEmpty(info.orderby)) // if (info.orderby.Equals("EmpEngFullName", StringComparison.CurrentCultureIgnoreCase)) // { // if (!table.Columns.Contains("EmpEngFullName")) // { // table.Columns.Add("EmpEngFullName", typeof(string)); // foreach (System.Data.DataRow row in table.Rows) // { // EEmpPersonalInfo empInfo = new EEmpPersonalInfo(); // empInfo.EmpID = (int)row["EmpID"]; // if (EEmpPersonalInfo.db.select(dbConn, empInfo)) // row["EmpEngFullName"] = empInfo.EmpEngFullName; // } // } // } return(DataTableSortingAndPaging(table, info)); } else { return(table); } }