/// <summary>
/// Execute the SQL statement does not return results, such as: delete, update, insert operation
/// </summary>
/// <param name="strSQL">SQL String of a non Query Type</param>
/// <returns>success or failure</returns>
public bool UpdateOrderHeader(OrderHeaderData pOrderHeader, List <string> pOrders)
{
bool _resultState = false;
string _strSQL = CONST_ORDERUPDATEHEADER_SQL + " WHERE ";
// for all the OrderIds passed create a where clause
for (int i = 0; i < pOrders.Count - 1; i++)
{
_strSQL += " OrderID = " + pOrders[i] + " OR";
}
_strSQL += " OrderID = " + pOrders[pOrders.Count - 1];
TrackerDb _TDB = new TrackerDb();
_TDB.AddParams(pOrderHeader.CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(pOrderHeader.OrderDate, DbType.Date, "@OrderDate");
_TDB.AddParams(pOrderHeader.RoastDate, DbType.Date, "@RoastDate");
_TDB.AddParams(pOrderHeader.ToBeDeliveredBy, DbType.Int64, "@ToBeDeliveredBy");
_TDB.AddParams(pOrderHeader.RequiredByDate, DbType.Date, "@RequiredByDate");
_TDB.AddParams(pOrderHeader.Confirmed, DbType.Boolean, "@Confirmed");
_TDB.AddParams(pOrderHeader.Done, DbType.Boolean, "@Done");
_TDB.AddParams(pOrderHeader.InvoiceDone, DbType.Boolean, "@InvoiceDone");
_TDB.AddParams(pOrderHeader.PurchaseOrder, DbType.String, "@PurchaseOrder");
_TDB.AddParams(pOrderHeader.Notes, DbType.String, "@Notes");
_resultState = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_strSQL));
_TDB.Close();
return(_resultState);
}
public OrderCls()
{
_Header = new OrderHeaderData();
// List<OrderDetailData>
_Items = new List <OrderDetailData>();
}
public List <OrderHeaderData> LoadOrderSummary(Int32 CustomerId, DateTime DeliveryDate, String Notes, int MaximumRows, int StartRowIndex)
{
List <OrderHeaderData> ohSummarys = new List <OrderHeaderData>();
TrackerDb _TDB = new TrackerDb();
string _sqlCmd = CONST_ORDERSSUMMARY_SELECT;
//OleDbConnection _conn = new OleDbConnection(_connectionString);
//OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
//_cmd.Parameters.Add(new OleDbParameter { Value = CustomerId });
//_cmd.Parameters.Add(new OleDbParameter { Value = PrepDate } );
//OleDbConnection _conn = new OleDbConnection(_connectionString);
// customer ZZname = 9 is a general customer name so condition must be to that
if (CustomerId == 9)
{
_sqlCmd += "([CustomerId] = 9) AND ([RequiredByDate] = ?) AND ([Notes] = ?)";
}
else
{
_sqlCmd += "([CustomerId] = ?) AND ([RequiredByDate] = ?)";
}
// attach the command
//OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
//if (CustomerId == 9)
//{
// _cmd.Parameters.Add(new OleDbParameter { Value = DeliveryDate });
// _cmd.Parameters.Add(new OleDbParameter { Value = Notes });
//}
//else
//{
// _cmd.Parameters.Add(new OleDbParameter { Value = CustomerId });
// _cmd.Parameters.Add(new OleDbParameter { Value = DeliveryDate });
//}
//_conn.Open();
if (CustomerId == 9)
{
_TDB.AddWhereParams(DeliveryDate, DbType.Date);
_TDB.AddWhereParams(Notes, DbType.String);
}
else
{
_TDB.AddWhereParams(CustomerId, DbType.Int32);
_TDB.AddWhereParams(DeliveryDate, DbType.Date);
}
// OleDbDataReader _DataReader = _cmd.ExecuteReader();
IDataReader _DataReader = _TDB.ExecuteSQLGetDataReader(_sqlCmd);
while (_DataReader.Read())
{
OrderHeaderData ohSummary = new OrderHeaderData();
ohSummary.CustomerID = (Int32)_DataReader["CustomerId"];
ohSummary.OrderDate = (DateTime)_DataReader["OrderDate"];
ohSummary.RoastDate = (DateTime)_DataReader["RoastDate"];
ohSummary.RequiredByDate = (DateTime)_DataReader["RequiredByDate"];
ohSummary.ToBeDeliveredBy = (_DataReader["ToBeDeliveredBy"] == DBNull.Value) ? 3 : (Int32)_DataReader["ToBeDeliveredBy"]; // 3 is the default
ohSummary.Confirmed = (bool)_DataReader["Confirmed"];
ohSummary.Done = (bool)_DataReader["Done"];
ohSummary.Notes = (_DataReader["Notes"] == DBNull.Value) ? "" : (string)_DataReader["Notes"];
ohSummarys.Add(ohSummary);
}
_DataReader.Close();
//_conn.Close();
_TDB.Close();
return(ohSummarys);
}