public System.Data.DataTable SearchAsDataTable(TransferInStatus transferinstatus, DateTime OrderStartDate, DateTime OrderEndDate, DateTime PostingStartDate, DateTime PostingEndDate, string SearchKey, string SortField, SortOption SortOrder) { try { if (SortField == string.Empty || SortField == null) SortField = "TransferInID"; string SQL = SQLSelect() + "WHERE Status = @Status AND (TransferInNo LIKE @SearchKey or TransferInDate LIKE @SearchKey or SupplierCode LIKE @SearchKey " + "or SupplierContact LIKE @SearchKey or BranchCode LIKE @SearchKey or RequiredDeliveryDate LIKE @SearchKey) "; if (OrderStartDate != DateTime.MinValue) SQL += "AND TransferInDate >= @OrderStartDate "; if (OrderEndDate != DateTime.MinValue) SQL += "AND TransferInDate <= @OrderEndDate "; if (PostingStartDate != DateTime.MinValue) SQL += "AND TransferInDate >= @PostingStartDate "; if (PostingEndDate != DateTime.MinValue) SQL += "AND TransferInDate <= @PostingEndDate "; SQL += "ORDER BY " + SortField; if (SortOrder == SortOption.Ascending) SQL += " ASC"; else SQL += " DESC"; MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = SQL; cmd.Parameters.AddWithValue("@Status", transferinstatus.ToString("d")); cmd.Parameters.AddWithValue("@SearchKey", "%" + SearchKey + "%"); if (OrderStartDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@OrderStartDate", OrderStartDate.ToString("yyyy-MM-dd HH:mm:ss")); if (OrderEndDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@OrderEndDate", OrderEndDate.ToString("yyyy-MM-dd HH:mm:ss")); if (PostingStartDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@PostingStartDate", PostingStartDate.ToString("yyyy-MM-dd HH:mm:ss")); if (PostingEndDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@PostingEndDate", PostingEndDate.ToString("yyyy-MM-dd HH:mm:ss")); string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); return dt; } catch (Exception ex) { throw base.ThrowException(ex); } }
public MySqlDataReader List(TransferInStatus transferinstatus, DateTime StartDate, DateTime EndDate) { try { string SQL = SQLSelect() + "WHERE Status = @Status AND DeliveryDate BETWEEN @StartDate AND @EndDate ORDER BY TransferInID ASC"; MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = SQL; MySqlParameter prmStartDate = new MySqlParameter("@StartDate",MySqlDbType.DateTime); prmStartDate.Value = StartDate.ToString("yyyy-MM-dd HH:mm:ss"); cmd.Parameters.Add(prmStartDate); MySqlParameter prmEndDate = new MySqlParameter("@EndDate",MySqlDbType.DateTime); prmEndDate.Value = EndDate.ToString("yyyy-MM-dd HH:mm:ss"); cmd.Parameters.Add(prmEndDate); MySqlParameter prmStatus = new MySqlParameter("@Status",MySqlDbType.Int16); prmStatus.Value = transferinstatus.ToString("d"); cmd.Parameters.Add(prmStatus); MySqlDataReader myReader = base.ExecuteReader(cmd); return myReader; } catch (Exception ex) { throw base.ThrowException(ex); } }
public System.Data.DataTable SearchAsDataTable(TransferInStatus transferinstatus, string SearchKey, string SortField, SortOption SortOrder) { try { if (SortField == string.Empty || SortField == null) SortField = "TransferInID"; string SQL = SQLSelect() + "WHERE Status = @Status AND (TransferInNo LIKE @SearchKey or TransferInDate LIKE @SearchKey or SupplierCode LIKE @SearchKey " + "or SupplierContact LIKE @SearchKey or BranchCode LIKE @SearchKey or RequiredDeliveryDate LIKE @SearchKey) " + "ORDER BY " + SortField; if (SortOrder == SortOption.Ascending) SQL += " ASC"; else SQL += " DESC"; MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = SQL; MySqlParameter prmStatus = new MySqlParameter("@Status",MySqlDbType.Int16); prmStatus.Value = transferinstatus.ToString("d"); cmd.Parameters.Add(prmStatus); MySqlParameter prmSearchKey = new MySqlParameter("@SearchKey",MySqlDbType.String); prmSearchKey.Value = "%" + SearchKey + "%"; cmd.Parameters.Add(prmSearchKey); string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); return dt; } catch (Exception ex) { throw base.ThrowException(ex); } }
public MySqlDataReader Search(TransferInStatus transferinstatus, string SearchKey, string SortField, SortOption SortOrder) { try { if (SortField == string.Empty || SortField == null) SortField = "TransferInID"; string SQL = SQLSelect() + "WHERE Status = @Status AND (TransferInNo LIKE @SearchKey or TransferInDate LIKE @SearchKey or SupplierCode LIKE @SearchKey " + "or SupplierContact LIKE @SearchKey or BranchCode LIKE @SearchKey or RequiredDeliveryDate LIKE @SearchKey) " + "ORDER BY " + SortField; if (SortOrder == SortOption.Ascending) SQL += " ASC"; else SQL += " DESC"; MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = SQL; MySqlParameter prmStatus = new MySqlParameter("@Status",MySqlDbType.Int16); prmStatus.Value = transferinstatus.ToString("d"); cmd.Parameters.Add(prmStatus); MySqlParameter prmSearchKey = new MySqlParameter("@SearchKey",MySqlDbType.String); prmSearchKey.Value = "%" + SearchKey + "%"; cmd.Parameters.Add(prmSearchKey); MySqlDataReader myReader = base.ExecuteReader(cmd); return myReader; } catch (Exception ex) { throw base.ThrowException(ex); } }
public MySqlDataReader List(TransferInStatus transferinstatus, long SupplierID, string SortField, SortOption SortOrder) { try { if (SortField == string.Empty || SortField == null) SortField = "TransferInID"; string SQL = SQLSelect() + "WHERE Status =@Status AND SupplierID = @SupplierID ORDER BY " + SortField; if (SortOrder == SortOption.Ascending) SQL += " ASC"; else SQL += " DESC"; MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = SQL; MySqlParameter prmStatus = new MySqlParameter("@Status",MySqlDbType.Int16); prmStatus.Value = transferinstatus.ToString("d"); cmd.Parameters.Add(prmStatus); MySqlParameter prmSupplierID = new MySqlParameter("@SupplierID",MySqlDbType.Int64); prmSupplierID.Value = SupplierID; cmd.Parameters.Add(prmSupplierID); MySqlDataReader myReader = base.ExecuteReader(cmd); return myReader; } catch (Exception ex) { throw base.ThrowException(ex); } }
public System.Data.DataTable ListAsDataTable(TransferInStatus transferinstatus, DateTime OrderStartDate, DateTime OrderEndDate, DateTime PostingStartDate, DateTime PostingEndDate, string SortField, SortOption SortOrder) { if (SortField == string.Empty || SortField == null) SortField = "TransferInID"; string SQL = SQLSelect() + "WHERE Status = @Status "; if (OrderStartDate != DateTime.MinValue) SQL += "AND TransferInDate >= @OrderStartDate "; if (OrderEndDate != DateTime.MinValue) SQL += "AND TransferInDate <= @OrderEndDate "; if (PostingStartDate != DateTime.MinValue) SQL += "AND TransferInDate >= @PostingStartDate "; if (PostingEndDate != DateTime.MinValue) SQL += "AND TransferInDate <= @PostingEndDate "; SQL += "ORDER BY " + SortField; if (SortOrder == SortOption.Ascending) SQL += " ASC"; else SQL += " DESC"; MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = SQL; cmd.Parameters.AddWithValue("@Status", transferinstatus.ToString("d")); if (OrderStartDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@OrderStartDate", OrderStartDate.ToString("yyyy-MM-dd HH:mm:ss")); if (OrderEndDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@OrderEndDate", OrderEndDate.ToString("yyyy-MM-dd HH:mm:ss")); if (PostingStartDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@PostingStartDate", PostingStartDate.ToString("yyyy-MM-dd HH:mm:ss")); if (PostingEndDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@PostingEndDate", PostingEndDate.ToString("yyyy-MM-dd HH:mm:ss")); string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); return dt; }
public System.Data.DataTable ListAsDataTable(TransferInStatus transferinstatus, string SortField, SortOption SortOrder) { if (SortField == string.Empty || SortField == null) SortField = "TransferInID"; string SQL = SQLSelect() + "WHERE Status = @Status ORDER BY " + SortField; if (SortOrder == SortOption.Ascending) SQL += " ASC"; else SQL += " DESC"; MySqlCommand cmd = new MySqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = SQL; MySqlParameter prmStatus = new MySqlParameter("@Status",MySqlDbType.Int16); prmStatus.Value = transferinstatus.ToString("d"); cmd.Parameters.Add(prmStatus); string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName); base.MySqlDataAdapterFill(cmd, dt); return dt; }