public MySqlDataReader List(SOStatus postatus, DateTime StartDate, DateTime EndDate)
{
try
{
string SQL = SQLSelect() + "WHERE Status = @Status AND DeliveryDate BETWEEN @StartDate AND @EndDate ORDER BY SOID 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 = postatus.ToString("d");
cmd.Parameters.Add(prmStatus);
MySqlDataReader myReader = base.ExecuteReader(cmd);
return myReader;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public MySqlDataReader List(SOStatus postatus, long CustomerID, string SortField, SortOption SortOrder)
{
try
{
if (SortField == string.Empty || SortField == null) SortField = "SOID";
string SQL = SQLSelect() + "WHERE Status =@Status AND CustomerID = @CustomerID 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 = postatus.ToString("d");
cmd.Parameters.Add(prmStatus);
MySqlParameter prmCustomerID = new MySqlParameter("@CustomerID",MySqlDbType.Int64);
prmCustomerID.Value = CustomerID;
cmd.Parameters.Add(prmCustomerID);
MySqlDataReader myReader = base.ExecuteReader(cmd);
return myReader;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public System.Data.DataTable SearchAsDataTable(SOStatus postatus, string SearchKey, string SortField, SortOption SortOrder)
{
try
{
if (SortField == string.Empty || SortField == null) SortField = "SOID";
string SQL = SQLSelect() + "WHERE Status = @Status AND (SONo LIKE @SearchKey or SODate LIKE @SearchKey or CustomerCode LIKE @SearchKey " +
"or CustomerContact 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 = postatus.ToString("d");
cmd.Parameters.Add(prmStatus);
MySqlParameter prmSearchKey = new MySqlParameter("@SearchKey",MySqlDbType.String);
prmSearchKey.Value = "%" + SearchKey + "%";
cmd.Parameters.Add(prmSearchKey);
System.Data.DataTable dt = new System.Data.DataTable("SO");
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public System.Data.DataTable ListAsDataTable(SOStatus sostatus, string SortField = "SOID", SortOption SortOrder = SortOption.Ascending)
{
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 = sostatus.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;
}