/*
* try
* {
* _conn.Open();
* if (_cmd.ExecuteNonQuery() > 0)
*
*
* OleDbConnection _conn = new OleDbConnection(_connectionString);
*
* // add parameters in the order they appear in the update command
* OleDbCommand _cmd = new OleDbCommand(_sqlCmd, _conn);
* _cmd.Parameters.Add(new OleDbParameter { Value = ItemTypeID });
* _cmd.Parameters.Add(new OleDbParameter { Value = Math.Round(QuantityOrdered,2) });
* _cmd.Parameters.Add(new OleDbParameter { Value = PackagingID });
* _cmd.Parameters.Add(new OleDbParameter { Value = OrderID });
*
* try
* {
* _conn.Open();
* if (_cmd.ExecuteNonQuery() > 0)
* return false;
* }
* catch (OleDbException ex)
* {
* string _ErrStr = ex.Message;
* return _ErrStr == "";
* }
* finally
* {
* _conn.Close();
* }
*
* return true;
* }
*/
public bool InsertOrderDetails(Int64 CustomerID, DateTime OrderDate, DateTime RoastDate, Int32 ToBeDeliveredBy,
DateTime RequiredByDate, Boolean Confirmed, Boolean Done, String Notes,
double QuantityOrdered, Int32 PackagingID, Int32 ItemTypeID)
{
string _sqlCmd = "INSERT INTO OrdersTbl (CustomerId, OrderDate, RoastDate, RequiredByDate, ToBeDeliveredBy, Confirmed, Done, Notes, " +
" ItemTypeID, QuantityOrdered, PackagingID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
//1 2 3 4 5 6 7 8 9 10 11
bool _Success = false;
// check if the item is a group item then get the next group.
TrackerTools _TT = new TrackerTools();
ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(CustomerID, ItemTypeID, RequiredByDate);
TrackerDb _TDB = new TrackerDb();
// first summary data
_TDB.AddParams(CustomerID, DbType.Int64, "@CustomerID");
_TDB.AddParams(OrderDate, DbType.Date, "@OrderDate");
_TDB.AddParams(RoastDate, DbType.Date, "@RoastDate");
_TDB.AddParams(RequiredByDate, DbType.Date, "@RequiredByDate");
_TDB.AddParams(ToBeDeliveredBy, DbType.Int32, "@ToBeDeliveredBy");
_TDB.AddParams(Confirmed, DbType.Boolean, "@Confirmed");
_TDB.AddParams(Done, DbType.Boolean, "@Done");
_TDB.AddParams(Notes, DbType.String, "@Notes");
// Now line data
_TDB.AddParams(ItemTypeID, DbType.Int32, "@ItemTypeID");
_TDB.AddParams(Math.Round(QuantityOrdered, 2), DbType.Double, "@QuantityOrdered");
_TDB.AddParams(PackagingID, DbType.Int32, "@PackagingID");
_Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_sqlCmd));
_TDB.Close();
return(_Success);
}
private bool LogARepair(RepairsTbl pRepair, bool pCalcOrderData)
{
bool _Success = false;
// create a new order for delivery [RoastDate]
OrderCls _OrderData = new OrderCls();
DateTime _RequiredByDate = DateTime.Now.Date.AddDays(7);
// add the default data to order
_OrderData.HeaderData.CustomerID = pRepair.CustomerID;
_OrderData.HeaderData.Notes = "Collect/Swop out Machine for Service";
List <OrderDetailData> orderDetailDatas = new List <OrderDetailData>();
OrderDetailData orderDetailData = new OrderDetailData();
orderDetailData.ItemTypeID = ItemTypeTbl.CONST_SERVICEITEMID;
orderDetailData.QuantityOrdered = 1;
// Calculate the Data from the customer details
if (pCalcOrderData)
{
TrackerTools _TT = new TrackerTools();
_OrderData.HeaderData.RoastDate = _TT.GetNextRoastDateByCustomerID(pRepair.CustomerID, ref _RequiredByDate);
TrackerTools.ContactPreferedItems _ContactPreferedItems = _TT.RetrieveCustomerPrefs(pRepair.CustomerID);
_OrderData.HeaderData.OrderDate = DateTime.Now.Date;
_OrderData.HeaderData.RequiredByDate = _RequiredByDate;
if (_ContactPreferedItems.RequiresPurchOrder)
{
_OrderData.HeaderData.PurchaseOrder = TrackerTools.CONST_POREQUIRED;
}
_OrderData.HeaderData.ToBeDeliveredBy = _ContactPreferedItems.DeliveryByID;
}
else
{
_OrderData.HeaderData.RoastDate = _OrderData.HeaderData.OrderDate = DateTime.Now.Date;
_OrderData.HeaderData.RequiredByDate = _RequiredByDate;
}
// save the data to the orders
OrderTbl _Order = new OrderTbl();
_Order.InsertNewOrderLine(_OrderData);
pRepair.RelatedOrderID = _Order.GetLastOrderAdded(_OrderData.HeaderData.CustomerID, _OrderData.HeaderData.OrderDate, ItemTypeTbl.CONST_SERVICEITEMID);
return(_Success);
}
/// <summary>
/// Execute the SQL statement returns the result to the DataSet
/// </summary>
/// <param Name="strSQL"> </ param>
/// <returns> DataSet </ returns>
public DataSet ReturnDataSet(string strSQL)
{
DataSet _DataSet = null;
try
{
_TrackerDbConn.Open();
_DataSet = new DataSet();
OdbcDataAdapter _OdbcDA = new OdbcDataAdapter(strSQL, _TrackerDbConn);
if (_Parameters != null)
{
foreach (OdbcParameter param in _Parameters)
{
_OdbcDA.SelectCommand.Parameters.Add(param);
}
}
if (_WhereParameters != null)
{
foreach (OdbcParameter param in _WhereParameters)
{
_OdbcDA.SelectCommand.Parameters.Add(param);
}
}
_OdbcDA.Fill(_DataSet, "objDataSet");
}
catch (OdbcException _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
if (_DataSet != null)
{
_DataSet.Dispose();
}
throw;
}
finally
{
_TrackerDbConn.Close();
}
return(_DataSet);
}
public OdbcDataReader ReturnDataReader(string strSQL)
{
OdbcDataReader _DataReader = null;
try
{
_TrackerDbConn.Open();
OdbcCommand _cmd = new OdbcCommand(strSQL, TrackerDbConn);
if (_Parameters != null)
{
foreach (OdbcParameter param in _Parameters)
{
_cmd.Parameters.Add(param);
}
}
if (_WhereParameters != null)
{
foreach (OdbcParameter param in _WhereParameters)
{
_cmd.Parameters.Add(param);
}
}
//OdbcDataReader odbcDataReader = _cmd.ExecuteReader();
_DataReader = _cmd.ExecuteReader();
}
catch (OdbcException _ex)
{
// Handle exception.
TrackerTools _Tools = new TrackerTools();
_Tools.SetTrackerSessionErrorString(_ex.Message);
if (_DataReader != null)
{
_DataReader.Dispose();
_DataReader = null;
}
}
finally
{
///
}
return(_DataReader);
}
/*
* OleDbConnection _conn = new OleDbConnection(_connectionString);
* // custoemr ZZname = 9 is a general customer name so condition must be to that
* string _sqlCmd = "SELECT [ItemTypeID], [QuantityOrdered], [PackagingID], [OrderID] FROM [OrdersTbl] WHERE ";
* 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 });
* }
* try
* {
* _conn.Open();
* OleDbDataReader _DataReader = _cmd.ExecuteReader();
* while (_DataReader.Read())
* {
* OrderDetailData od = new OrderDetailData();
*
* od.ItemTypeID = (_DataReader["ItemTypeID"] == DBNull.Value) ? 0 : (Int32)_DataReader["ItemTypeID"];
* od.PackagingID = (_DataReader["PackagingID"] == DBNull.Value) ? 0 : (Int32)_DataReader["PackagingID"];
* od.OrderID = (Int32)_DataReader["OrderId"]; // this is the PK cannot be null
* od.QuantityOrdered = (_DataReader["QuantityOrdered"] == DBNull.Value) ? 1 : Math.Round(Convert.ToDouble(_DataReader["QuantityOrdered"]),2);
*
* oDetails.Add(od);
* }
* _DataReader.Close();
* }
* catch (Exception _ex)
* {
* lastError = _ex.Message;
* }
* finally
* {
* _conn.Close();
* }
* return oDetails;
* }
*/
/// <summary>
/// Update Order Details, using the orderID update the line info.
/// </summary>
/// <param name="ItemTypeID"></param>
/// <param name="QuantityOrdered"></param>
/// <param name="PackagingID"></param>
/// <param name="OrderID"></param>
/// <returns></returns>
public bool UpdateOrderDetails(Int64 OrderID, Int64 CustomerID, Int32 ItemTypeID, DateTime DeliveryDate, double QuantityOrdered, Int32 PackagingID)
{
bool _Success = false;
string _sqlCmd = "UPDATE OrdersTbl SET ItemTypeID = ?, QuantityOrdered = ?, PackagingID = ? WHERE (OrderId = ?)";
TrackerDb _TDB = new TrackerDb();
// check if the item is a group item then get the next group.
TrackerTools _TT = new TrackerTools();
ItemTypeID = _TT.ChangeItemIfGroupToNextItemInGroup(CustomerID, ItemTypeID, DeliveryDate);
_TDB.AddParams(ItemTypeID, DbType.Int32, "@ItemTypeID");
_TDB.AddParams(Math.Round(QuantityOrdered, 2), DbType.Double, "@QuantityOrdered");
_TDB.AddParams(PackagingID, DbType.Int32, "@PackagingID");
_TDB.AddWhereParams(OrderID, DbType.Int64, "@OrderID");
_Success = String.IsNullOrEmpty(_TDB.ExecuteNonQuerySQL(_sqlCmd));
_TDB.Close();
return(_Success);
}
