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