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