public List <SpoilDto> GetRangeUnsyncedSpoil(String count, int status, bool syncReady)
{
String tsql = $@"SELECT TOP {count} spl.* FROM [Sync].[Spoils] spl
WHERE spl.SyncStatus = @p0 AND spl.IsSyncReady = @p1";
List <SpoilDto> txnSpoilList = new List <SpoilDto>();
try
{
// Structure Parameter Array
var colParameters = new DBHelper.Parameters[]
{
new DBHelper.Parameters("@p0", status),
new DBHelper.Parameters("@p1", syncReady)
};
using (IDataReader drSalesTrans = this.ExecuteReader(CommandType.Text, tsql, colParameters))
{
while (drSalesTrans.Read())
{
var spoiltDto = new SpoilDto();
spoiltDto.Id = drSalesTrans.GetGuid(0);
spoiltDto.TransactionRefNo = drSalesTrans.GetString(1);
spoiltDto.StockRefNo = drSalesTrans.GetInt32(2);
spoiltDto.StockDetails = drSalesTrans.IsDBNull(3) ? null : drSalesTrans.GetString(3);
spoiltDto.StockUnit = drSalesTrans.GetInt32(4);
spoiltDto.StockUnitLeft = drSalesTrans.IsDBNull(5) ? null : (Int32?)drSalesTrans.GetInt32(5);
spoiltDto.IsSyncReady = drSalesTrans.GetBoolean(6);
spoiltDto.SyncStatus = drSalesTrans.GetInt32(7);
spoiltDto.SpoilDetails = drSalesTrans.IsDBNull(8) ? null : drSalesTrans.GetString(8);
spoiltDto.ReasonSyncFailed = drSalesTrans.IsDBNull(9) ? null : drSalesTrans.GetString(9);
spoiltDto.ReportedBy = drSalesTrans.IsDBNull(10) ? null : drSalesTrans.GetString(10);
spoiltDto.SyncRefCreatedOn = drSalesTrans.IsDBNull(11) ? null : (DateTime?)drSalesTrans.GetDateTime(11);
spoiltDto.SyncRefModifiedOn = drSalesTrans.IsDBNull(12) ? null : (DateTime?)drSalesTrans.GetDateTime(12);
spoiltDto.MachineName = drSalesTrans.IsDBNull(13) ? null : drSalesTrans.GetString(13);
spoiltDto.SyncRefNo = drSalesTrans.IsDBNull(14) ? null : drSalesTrans.GetString(14);
spoiltDto.SyncDate = drSalesTrans.IsDBNull(15) ? null : (DateTime?)drSalesTrans.GetDateTime(15);
spoiltDto.SyncFailedCount = drSalesTrans.GetInt32(16);
spoiltDto.RefCreatedDate = drSalesTrans.IsDBNull(17) ? null : (DateTime?)drSalesTrans.GetDateTime(17);
spoiltDto.RefModifiedDate = drSalesTrans.IsDBNull(18) ? null : (DateTime?)drSalesTrans.GetDateTime(18);
spoiltDto.Cost = drSalesTrans.IsDBNull(19) ? 0 : drSalesTrans.GetDouble(19);
spoiltDto.CreatedOnUtc = drSalesTrans.GetDateTime(20);
spoiltDto.ModifiedOnUtc = drSalesTrans.IsDBNull(21) ? null : (DateTime?)drSalesTrans.GetDateTime(21);
//drSalesTrans.GetBytes(23, 0, spoiltDto.RowVersion, 0, 18);
txnSpoilList.Add(spoiltDto);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(txnSpoilList);
}
public IList <SpoilDto> RetrieveUnSyncedSpoils(string createdDate)
{
IList <SpoilDto> sList = null;
SpoilDto spoilTransaction = null;
DBHelper.Parameters[] colParameters = null;
try
{
colParameters = new DBHelper.Parameters[]
{
new DBHelper.Parameters("@EntryDate", createdDate),
};
using (IDataReader spoilTrans = this.ExecuteReader(CommandType.StoredProcedure, "[Sync].[Sp_retrieveunsyncedspoils]", colParameters))
{
sList = new List <SpoilDto>();
//Reading the reader one by one and setting into requestTypeInfo.
while (spoilTrans.Read())
{
spoilTransaction = new SpoilDto();
spoilTransaction.TransactionRefNo = spoilTrans.IsDBNull(0) ? null : spoilTrans.GetString(0);
spoilTransaction.StockRefNo = spoilTrans.IsDBNull(1) ? 0 : (int)spoilTrans.GetInt32(1);
spoilTransaction.SpoilDetails = spoilTrans.IsDBNull(2) ? null : spoilTrans.GetString(2);
spoilTransaction.StockDetails = spoilTrans.IsDBNull(3) ? null : spoilTrans.GetString(3);
spoilTransaction.ReportedBy = spoilTrans.IsDBNull(4) ? null : spoilTrans.GetString(4);
spoilTransaction.StockUnit = spoilTrans.IsDBNull(5) ? 0 : (int)spoilTrans.GetInt32(5);
spoilTransaction.IsDeleted = spoilTrans.IsDBNull(6) ? false : spoilTrans.GetBoolean(6);
spoilTransaction.RefCreatedDate = spoilTrans.IsDBNull(7) ? null : (DateTime?)spoilTrans.GetDateTime(7);
spoilTransaction.RefModifiedDate = spoilTrans.IsDBNull(8) ? null : (DateTime?)spoilTrans.GetDateTime(8);
spoilTransaction.Cost = spoilTrans.IsDBNull(9) ? null : (Double?)spoilTrans.GetDecimal(9);
sList.Add(spoilTransaction);
}
}
return(sList);
}
catch (Exception ex)
{
throw ex;
}
finally
{
colParameters = null;
}
}
public void UpdateSyncedSpoil(SpoilDto txn)
{
String tsql = @"[Sync].[Sp_UpdateSyncSpoil]";
try
{
var colParameters = new DBHelper.Parameters[]
{
new DBHelper.Parameters("@IsSyncReady", txn.IsSyncReady),
new DBHelper.Parameters("@SyncDate", txn.SyncDate),
new DBHelper.Parameters("@SyncRefNo", txn.SyncRefNo ?? String.Empty),
new DBHelper.Parameters("@SyncRefCreatedOn", txn.SyncRefCreatedOn),
new DBHelper.Parameters("@SyncRefModifiedOn", txn.SyncRefModifiedOn ?? txn.SyncRefCreatedOn),
new DBHelper.Parameters("@ReasonSyncFailed", txn.ReasonSyncFailed ?? string.Empty),
new DBHelper.Parameters("@SyncFailedCount", txn.SyncFailedCount),
new DBHelper.Parameters("@Id", txn.Id)
};
this.ExecuteNonQuery(CommandType.StoredProcedure, tsql, colParameters);
}
catch (DBConcurrencyException)
{
}
}
private void SyncSpoilChanges(SpoilDto spoilDto, TableDependency.Enums.ChangeType changeType)
{
var unsyncedSpoil = GetUnSyncSpoilBy(spoilDto.SpoilId);
if (unsyncedSpoil == null && changeType != ChangeType.Delete)
{
return;
}
switch (changeType)
{
case ChangeType.Delete:
{
String spoilId = spoilDto.SpoilId.ToString();
var existingSpoil = _spoilSyncSvc.FirstOrDefault(t => t.TransactionRefNo == spoilId);
if (existingSpoil != null)
{
existingSpoil.IsDeleted = true;
Spoil.Extend(unsyncedSpoil, existingSpoil);
_spoilSyncSvc.Update(existingSpoil);
}
break;
}
case TableDependency.Enums.ChangeType.Insert:
{
unsyncedSpoil.RefModifiedDate = unsyncedSpoil.RefCreatedDate;
var dbTrt = _spoilSyncSvc.FirstOrDefault(t => t.TransactionRefNo == unsyncedSpoil.TransactionRefNo);
if (dbTrt == null)
{
unsyncedSpoil.ModifiedOnUtc = unsyncedSpoil.CreatedOnUtc = DateTime.UtcNow;
_spoilSyncSvc.Add(unsyncedSpoil);
}
else
{
Spoil.Extend(unsyncedSpoil, dbTrt);
_spoilSyncSvc.Update(dbTrt);
}
break;
}
case TableDependency.Enums.ChangeType.Update:
{
if (spoilDto.IsDeleted)
{
goto case ChangeType.Delete;
}
var dbTrt = _spoilSyncSvc.FirstOrDefault(t => t.TransactionRefNo == unsyncedSpoil.TransactionRefNo);
if (dbTrt == null)
{
unsyncedSpoil.CreatedOnUtc = DateTime.UtcNow;
unsyncedSpoil.ModifiedOnUtc = DateTime.UtcNow;
Log.Warning(String.Format("Spoil with TransactionRefNo ({0}) couldn't be retreived for update.", unsyncedSpoil.TransactionRefNo));
Log.Information(String.Format("Adding Spoil with TransactionRefNo ({0}) to sync table as newly created.", unsyncedSpoil.StockRefNo));
_spoilSyncSvc.Add(unsyncedSpoil);
}
else
{
Spoil.Extend(unsyncedSpoil, dbTrt);
_spoilSyncSvc.Update(dbTrt);
}
break;
}
case TableDependency.Enums.ChangeType.None:
default:
break;
}
}
public static Spoil Create(SpoilDto spoilDto)
{
if (spoilDto == null)
{
throw new ArgumentNullException();
}
var spoil = new Spoil();
if (String.IsNullOrEmpty(spoilDto.ReportedBy))
{
spoil.ReasonSyncFailed = "ReportedBy is missing which is required to sync this transaction.; ";
spoil.IsSyncReady = false;
spoil.SyncStatus = (Int32)Ipos.Sync.Core.Models.Enums.SyncStatus.REJECTED;
}
if (spoilDto.StockRefNo <= 0)
{
spoil.ReasonSyncFailed += "StockRefNo is missing which is required to sync this transaction.; ";
spoil.IsSyncReady = false;
spoil.SyncStatus = (Int32)Ipos.Sync.Core.Models.Enums.SyncStatus.REJECTED;
}
if (String.IsNullOrEmpty(spoilDto.StockDetails))
{
spoil.ReasonSyncFailed = "StockDetails is missing which is required to sync this transaction.; ";
spoil.IsSyncReady = false;
spoil.SyncStatus = (Int32)Ipos.Sync.Core.Models.Enums.SyncStatus.REJECTED;
}
if (spoilDto.StockUnit <= 0)
{
spoil.ReasonSyncFailed = "StockUnit must be greater than zero which is required to sync this transaction.; ";
spoil.IsSyncReady = false;
spoil.SyncStatus = (Int32)Ipos.Sync.Core.Models.Enums.SyncStatus.REJECTED;
}
if (!spoilDto.RefCreatedDate.HasValue)
{
spoil.ReasonSyncFailed = "RefCreatedDate was not supplied or invalid.";
spoil.SyncStatus = (Int32)Ipos.Sync.Core.Models.Enums.SyncStatus.REJECTED;
spoil.IsSyncReady = false;
}
spoil.ReportedBy = spoilDto.ReportedBy;
spoil.StockDetails = spoilDto.StockDetails;
spoil.StockRefNo = spoilDto.StockRefNo;
spoil.StockUnit = spoilDto.StockUnit;
spoil.StockUnitLeft = spoilDto.StockUnitLeft;
spoil.SpoilDetails = spoilDto.SpoilDetails;
spoil.IsDeleted = spoilDto.IsDeleted;
spoil.TransactionRefNo = spoilDto.TransactionRefNo;
spoil.RefCreatedDate = spoilDto.RefCreatedDate;
spoil.RefModifiedDate = spoilDto.RefModifiedDate;
spoil.Cost = spoilDto.Cost;
spoil.Id = Guid.NewGuid();
if (spoil.SyncStatus != (Int32)Ipos.Sync.Core.Models.Enums.SyncStatus.REJECTED)
{
spoil.IsSyncReady = true;
spoil.SyncStatus = (Int32)Ipos.Sync.Core.Models.Enums.SyncStatus.PENDING;
}
return(spoil);
}