/// <summary> /// Builds the SQL query string. /// </summary> /// <returns></returns> private string BuildSqlQueryString() { StringBuilder sql = new StringBuilder(); sql.Append(@" SELECT HeaderId, TxNumber, TxType, EffectDate, PM_TYPE, (CASE WHEN PriceManagementBatchHeader.ReasonId IS NOT NULL THEN (SELECT ReasonCode FROM PriceManagementReason WHERE ReasonId = PriceManagementBatchHeader.ReasonId) ELSE '' END) AS ReasonCode, Remarks, "); sql.Append(" CreatedOn, ModifiedOn, CreatedBy, ModifiedBy "); sql.Append(" FROM PriceManagementBatchHeader "); sql.Append(" WHERE 1 = 1 "); sql.Append(" AND PM_TYPE = '").Append(this.ListType.ToString().Substring(0, 1)).Append("'"); switch (SelectedViewIndex) { case 0: // Last 7 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 1: // Last 14 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 2: // Last 30 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 3: // Last 60 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 4: // Last 90 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 5: // All default: sql.Append(""); break; } if (!string.IsNullOrEmpty(SearchForText)) { sql.Append(" AND TxNumber LIKE '%").Append(SearchForText).Append("%'"); } if (SelectedStaff != System.Guid.Empty) { sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'"); } sql.Append(" ORDER BY TxNumber "); return(sql.ToString()); }
/// <summary> /// Builds the SQL query string. /// </summary> /// <returns></returns> private string BuildSqlQueryString() { StringBuilder sql = new StringBuilder(); sql.Append(" SELECT [HeaderId],[Status],[TxNumber],[TxDate],[TotalAmount],[DepositAmount],[StaffNumber],[WorkplaceCode] "); sql.Append(" ,[MemberNumber],[Reference],[Remarks],[CreatedOn],[ModifiedOn] "); sql.Append(" FROM [dbo].[vwDraftEPosHeaderList]"); sql.Append(" WHERE TxType = '").Append(this.SalesType.ToString().Substring(0, 3)).Append("'"); switch (cboView.SelectedIndex) { case 0: // Last 7 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 1: // Last 14 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 2: // Last 30 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 3: // Last 60 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 4: // Last 90 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 5: // All default: sql.Append(""); break; } if (!string.IsNullOrEmpty(SearchForText)) { sql.Append(" AND TxNumber LIKE '%").Append(SearchForText.Trim()).Append("%'"); } if (SelectedStaff != System.Guid.Empty) { sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'"); } sql.Append(" ORDER BY TxNumber "); return(sql.ToString()); }
private bool GetData() { Database.ParameterBuilder paramBuilder = new Database.ParameterBuilder(); paramBuilder.AddParameter(SqlDbType.SmallDateTime, "@Date", this._selectedDate); paramBuilder.AddParameter(SqlDbType.Int, "@WeekNo", this._timetableWeek); paramBuilder.AddParameter(SqlDbType.Int, "@DayNo", (int)this._selectedDate.DayOfWeek + 1); if (string.IsNullOrEmpty(this._searchTerm)) { paramBuilder.AddParameter(SqlDbType.VarChar, "@SearchTerm", ""); } else { paramBuilder.AddParameter(SqlDbType.VarChar, "@SearchTerm", this._searchTerm); } using (SqlDataReader dataReader = Database.Provider.ExecuteReader("/Sql/Elements/Datagrids/Summary/results.sql", paramBuilder.Parameters)) { if (dataReader.HasRows) { this._selectedStaff = new SelectedStaff(); while (dataReader.Read()) { StaffMember staffMember = new StaffMember(); staffMember.Id = (int)dataReader["StaffId"]; staffMember.Forename = dataReader["Forename"] as string; staffMember.Surname = dataReader["Surname"] as string; staffMember.HoldingName = dataReader["HoldingName"] as string; this._selectedStaff.Add(staffMember); this._searchCount++; } dataReader.NextResult(); this._periodsTable = Database.Provider.GetDataTable(dataReader); return(true); } else { return(false); } } }
private string BuildSqlQueryString() { StringBuilder sql = new StringBuilder(); sql.Append(@" SELECT HeaderId, ( CASE WHEN Status = 0 THEN 'HOLD' WHEN Status = 1 THEN 'POST' END ) AS Status, TxNumber, TxDate, StaffNumber, "); sql.Append(" Location, SupplierCode, Reference, Remarks, "); sql.Append(" CreatedOn, CreatedBy, ModifiedOn, ModifiedBy "); sql.Append(" FROM vwDraftCAPList "); sql.Append(" WHERE TxType = 'REJ' AND ReadOnly = 0 AND"); switch (SelectedViewIndex) { case 0: // Last 7 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 1: // Last 14 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 2: // Last 30 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 3: // Last 60 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 4: // Last 90 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 5: // All default: sql.Append(" 1 = 1 "); break; } if (!string.IsNullOrEmpty(SearchForText)) { sql.Append(" AND TxNumber LIKE '%").Append(SearchForText).Append("%'"); } if (SelectedStaff != System.Guid.Empty) { sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'"); } sql.Append("ORDER BY TxNumber DESC"); return(sql.ToString()); }
/// <summary> /// Builds the SQL query string. /// </summary> /// <returns>The joined Sql</returns> private string BuildSqlQueryString() { StringBuilder sql = new StringBuilder(); //// 2008.03.24 paulus: TxType is not that useful, changed to show the Header.Status //// sql.Append("SELECT HeaderId, TxType, TxNumber, TxDate, StaffNumber, "); sql.Append(@" SELECT OrderHeaderId, ( CASE WHEN Status = 0 THEN 'HOLD' WHEN Status = 1 THEN 'POST' END ) AS Status, OrderNumber, OrderOn, StaffNumber, "); sql.Append(" Location, SupplierCode, Remarks1, Remarks2, Remarks3, "); sql.Append(" CreatedOn, CreatedBy, ModifiedOn, ModifiedBy "); sql.Append(" FROM vwPurchaseOrderList "); sql.Append(" WHERE PostedBy = '00000000-0000-0000-0000-000000000000' AND "); switch (SelectedViewIndex) { case 0: //// Last 7 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 1: //// Last 14 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 2: //// Last 30 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 3: //// Last 60 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 4: //// Last 90 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 5: //// All default: sql.Append(" 1 = 1 "); break; } if (!string.IsNullOrEmpty(SearchForText)) { string objOrderNumber = PurchasingUtils.GenSafeChars(SearchForText); sql.Append(" AND OrderNumber LIKE '%").Append(objOrderNumber).Append("%'"); } if (SelectedStaff != System.Guid.Empty) { sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'"); } sql.Append("ORDER BY OrderNumber"); return(sql.ToString()); }
private string BuildSqlQueryString() { StringBuilder sql = new StringBuilder(); sql.Append(" SELECT AnalysisCodeId, ROW_NUMBER() OVER (ORDER BY AnalysisCode) AS rownum, "); sql.Append(" AnalysisType, AnalysisCode, CodeInitial, CodeName, CodeName_Chs, CodeName_Cht, Mandatory, "); sql.Append(" CreatedOn, ModifiedOn, CreatedBy, ModifiedBy "); sql.Append(" FROM PosAnalysisCode "); sql.Append(" WHERE 1 = 1 "); switch (SelectedViewIndex) { case 0: // Last 7 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 1: // Last 14 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 2: // Last 30 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 3: // Last 60 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 4: // Last 90 days sql.Append(" AND (CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME))"); break; case 5: // All default: sql.Append(""); break; } if (!string.IsNullOrEmpty(SearchForText)) { sql.Append(" AND (AnalysisCode LIKE '%").Append(SearchForText).Append("%'"); sql.Append(" OR CodeName LIKE '%").Append(SearchForText).Append("%')"); } if (SelectedStaff != System.Guid.Empty) { sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'"); } if (!(String.IsNullOrEmpty(AlphaSeacher)) && AlphaSeacher != "All") { sql.Append(String.Format(" AND ( SUBSTRING([AnalysisCode], 1, 1) = N'{0}' )", AlphaSeacher)); } sql.Append(" ORDER BY AnalysisCode "); return(sql.ToString()); }
private string BuildSqlQueryString() { StringBuilder sql = new StringBuilder(); sql.Append(" SELECT [StaffId],[StaffNumber],[StaffCode],[FullName],[FullName_Chs],[FullName_Cht]"); sql.Append(" ,[CreatedOn],[CreatedBy],[ModifiedOn],[ModifiedBy] "); sql.Append(" FROM [dbo].[vwStaffList] "); sql.Append(" WHERE Status >= 0 AND "); switch (SelectedViewIndex) { case 0: // Last 7 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 1: // Last 14 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 2: // Last 30 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 3: // Last 60 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 4: // Last 90 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 5: // All default: sql.Append(" 1 = 1 "); break; } if (!string.IsNullOrEmpty(SearchForText)) { sql.Append(" AND (StaffNumber LIKE '%").Append(SearchForText).Append("%' "); sql.Append(" OR StaffCode LIKE '%").Append(SearchForText).Append("%' "); sql.Append(" OR FullName LIKE '%").Append(SearchForText).Append("%')"); } if (SelectedStaff != System.Guid.Empty) { sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'"); } if (!(String.IsNullOrEmpty(AlphaSeacher)) && AlphaSeacher != "All") { sql.Append(String.Format(" AND ( SUBSTRING([StaffNumber], 1, 1) = N'{0}' )", AlphaSeacher)); } return(sql.ToString()); }
private string BuildSqlQueryString() { StringBuilder sql = new StringBuilder(); sql.Append("SELECT MemberId, MemberNumber, MemberInitial, ISNULL(FullName, '') AS FullName, ISNULL(FullName_Chs, '') AS FullName_Chs, "); sql.Append(" ISNULL(FullName_Cht, '') AS FullName_Cht, ISNULL(Remarks, '') AS Remarks, "); sql.Append(" CreatedOn, ModifiedOn, CreatedBy, ModifiedBy "); sql.Append(" FROM Member "); sql.Append(" WHERE "); switch (base.SelectedViewIndex) { case 0: // Last 7 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 1: // Last 14 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 2: // Last 30 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 3: // Last 60 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 4: // Last 90 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 5: // All default: sql.Append(" 1 = 1 "); break; } if (!string.IsNullOrEmpty(base.SearchForText)) { sql.Append(" AND (MemberNumber LIKE '%").Append(base.SearchForText).Append("%' "); sql.Append(" OR MemberInitial LIKE '%").Append(base.SearchForText).Append("%' "); sql.Append(" OR FullName LIKE '%").Append(base.SearchForText).Append("%')"); } if (SelectedStaff != System.Guid.Empty) { sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'"); } if (!(String.IsNullOrEmpty(AlphaSeacher)) && AlphaSeacher != "All") { sql.Append(String.Format(" AND ( SUBSTRING([MemberNumber], 1, 1) = N'{0}' )", AlphaSeacher)); } sql.Append(" ORDER BY MemberNumber, MemberInitial "); return(sql.ToString()); }
private string BuildSqlQueryString() { StringBuilder sql = new StringBuilder(); sql.Append(" SELECT MemberId, VipNumber, ISNULL(PhoneBook, '') AS PhoneBook, ISNULL(AddressTypeCode, '') AS AddressTypeCode, "); sql.Append(" ISNULL(Phone_W, '') AS Phone_W, ISNULL(Phone_H, '') AS Phone_H, ISNULL(Phone_P, '') AS Phone_P, ISNULL(Fax, '') AS Fax, ISNULL(Phone_Other, '') AS Phone_Other, "); sql.Append(" CreatedOn, CreatedBy, ModifiedOn, ModifiedBy, AddressTypeId "); sql.Append(" FROM vwPhonebookList "); sql.Append(" WHERE "); switch (base.SelectedViewIndex) { case 0: // Last 7 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 1: // Last 14 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 2: // Last 30 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 3: // Last 60 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 4: // Last 90 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 5: // All default: sql.Append(" 1 = 1 "); break; } if (!string.IsNullOrEmpty(base.SearchForText)) { sql.Append(" AND (VipNumber LIKE '%").Append(base.SearchForText).Append("%' "); sql.Append(" OR Phone_W LIKE '%").Append(base.SearchForText).Append("%' "); sql.Append(" OR Phone_P LIKE '%").Append(base.SearchForText).Append("%' "); sql.Append(" OR Phone_Other LIKE '%").Append(base.SearchForText).Append("%' "); sql.Append(" OR Phone_H LIKE '%").Append(base.SearchForText).Append("%')"); } if (SelectedStaff != System.Guid.Empty) { sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'"); } if (!(String.IsNullOrEmpty(AlphaSeacher)) && AlphaSeacher != "All") { sql.Append(String.Format(" AND ( SUBSTRING([VipNumber], 1, 1) = N'{0}' )", AlphaSeacher)); } sql.Append(" ORDER BY VipNumber, AddressTypeCode "); return(sql.ToString()); }
private string BuildSqlQueryString() { StringBuilder sql = new StringBuilder(); sql.Append("SELECT Top 1000 ProductId, (STKCODE + ' ' + APPENDIX1 + ' ' + APPENDIX2 + ' ' + APPENDIX3) AS ProductCode, "); sql.Append(" STKCODE, APPENDIX1, APPENDIX2, APPENDIX3, ProductName, ProductName_Chs, "); sql.Append(" ProductName_Cht, Nature, UOM, "); sql.Append(" Remarks, CreatedOn, ModifiedOn, CreatedBy, ModifiedBy "); sql.Append(" FROM vwProductList "); sql.Append(" WHERE "); switch (SelectedViewIndex) { case 0: // Last 7 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 1: // Last 14 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 2: // Last 30 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 3: // Last 60 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 4: // Last 90 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 5: // All default: sql.Append(" 1 = 1 "); break; } if (!string.IsNullOrEmpty(SearchForText)) { sql.Append(" AND ((STKCODE + APPENDIX1 + APPENDIX2 + APPENDIX3) LIKE '%").Append(SearchForText).Append("%'"); sql.Append(" OR ProductName LIKE '%").Append(SearchForText).Append("%')"); } if (SelectedStaff != System.Guid.Empty) { sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'"); } if (!(String.IsNullOrEmpty(AlphaSeacher)) && AlphaSeacher != "All") { sql.Append(String.Format(" AND ( SUBSTRING([STKCODE], 1, 1) = N'{0}' )", AlphaSeacher)); } sql.Append(" ORDER BY STKCODE, APPENDIX1, APPENDIX2, APPENDIX3 "); return(sql.ToString()); }
private string BuildSqlQueryString() { StringBuilder sql = new StringBuilder(); sql.Append("SELECT WorkplaceId, ROW_NUMBER() OVER (ORDER BY WorkplaceCode) AS rownum, "); sql.Append(" WorkplaceCode, WorkplaceInitial, WorkplaceName, WorkplaceName_Chs, "); sql.Append(" WorkplaceName_Cht, ISNULL(Email, ''), "); sql.Append(" CreatedOn, ModifiedOn, CreatedBy, ModifiedBy "); sql.Append(" FROM vwWorkplaceList "); sql.Append(" WHERE Status >= 0 AND "); switch (SelectedViewIndex) { case 0: // Last 7 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 1: // Last 14 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 2: // Last 30 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 3: // Last 60 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 4: // Last 90 days sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)"); sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)"); break; case 5: // All default: sql.Append(" 1 = 1 "); break; } if (!string.IsNullOrEmpty(SearchForText)) { sql.Append(" AND (WorkplaceCode LIKE '%").Append(SearchForText).Append("%' "); sql.Append(" OR WorkplaceInitial LIKE '%").Append(SearchForText).Append("%' "); sql.Append(" OR WorkplaceName LIKE '%").Append(SearchForText).Append("%')"); } if (SelectedStaff != System.Guid.Empty) { sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'"); } if (!(String.IsNullOrEmpty(AlphaSeacher)) && AlphaSeacher != "All") { sql.Append(String.Format(" AND ( SUBSTRING([WorkplaceCode], 1, 1) = N'{0}' )", AlphaSeacher)); } return(sql.ToString()); }