/// <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 List <OrderHeaderData> LoadOrderHeader(Int32 pCustomerID, DateTime pPrepDate)
{
List <OrderHeaderData> ohDetails = new List <OrderHeaderData>();
string _sqlCmd = CONST_ORDERSHEADER_SELECT;
OleDbConnection _conn = new OleDbConnection(_connectionString);
OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
_cmd.Parameters.Add(new OleDbParameter {
Value = pCustomerID
});
_cmd.Parameters.Add(new OleDbParameter {
Value = pPrepDate
});
_conn.Open();
OleDbDataReader objReader = _cmd.ExecuteReader();
while (objReader.Read())
{
OrderHeaderData ohDetail = new OrderHeaderData();
ohDetail.CustomerID = (Int32)objReader["CustID"];
// ohDetail.CompanyName = (string)objReader["CompanyName"];
// ohDetail.Abreviation = (string)objReader["Abreviation "];
ohDetail.ToBeDeliveredBy = (long)objReader["PersonsID"];
ohDetail.Notes = (string)objReader["Notes"];
ohDetail.OrderDate = (DateTime)objReader["OrderDate"];
ohDetail.RoastDate = (DateTime)objReader["RoastDate"];
ohDetail.RequiredByDate = (DateTime)objReader["RequiredByDate"];
ohDetail.Confirmed = (bool)objReader["Confirmed"];
ohDetail.Done = (bool)objReader["Done"];
ohDetails.Add(ohDetail);
}
objReader.Close();
_conn.Close();
return(ohDetails);
}
public OrderCls()
{
_Header = new OrderHeaderData();
List <OrderDetailData> _Items = new List <OrderDetailData>();
}
/// <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];
_TrackerDbConn.Open();
OleDbTransaction _myTrans = _TrackerDbConn.BeginTransaction();
// UPDATE order CustomerId = ?, OrderDate= ?, RoastDate= ?, RequiredByDate= ?, ToBeDeliveredBy= ?, Confirmed= ?, Done= ?, Notes = ? WHERE (OrderId = ?)";
OleDbCommand _command = new OleDbCommand(_strSQL, _TrackerDbConn, _myTrans);
// add parameters in the order of the SQL command
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.CustomerID
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.OrderDate
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.RoastDate
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.RequiredByDate
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.ToBeDeliveredBy
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.Confirmed
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.Done
});
_command.Parameters.Add(new OleDbParameter {
Value = pOrderHeader.Notes
});
// _command.Parameters.Add(new OleDbParameter { Value =pOrderId});
try
{
_command.ExecuteNonQuery();
_myTrans.Commit();
_resultState = true;
}
catch
{
_myTrans.Rollback();
_resultState = false;
}
finally
{
_TrackerDbConn.Close();
}
return(_resultState);
}
public List <OrderHeaderData> LoadOrderSummary(Int32 CustomerId, DateTime DeliveryDate, String Notes, int MaximumRows, int StartRowIndex)
{
List <OrderHeaderData> ohSummarys = new List <OrderHeaderData>();
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();
OleDbDataReader _DataReader = _cmd.ExecuteReader();
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();
return(ohSummarys);
}