public async Task <List <Training> > GetTrainingByUser(string createdBy)
{
await connectionProvider.EstablishConnection();
var trainings = connectionProvider.Query <Training>("SELECT id,name,description,datetime,createdBy FROM training where createdBy=@createdBy", new { @createdBy = createdBy });
connectionProvider.Close();
return(trainings.ToList());
}
public static async Task <IEnumerable <dynamic> > GetViewColumns(string ViewName, string ViewKey)
{
IEnumerable <dynamic> columns = null;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
string UserId = ApiService.UserId;
try
{
// get columns
connection.Open();
int recCnt = await connection.ExecuteScalarAsync <int>(qryViewMaster.selectColDefCnt, new { ViewName, UserId });
if (recCnt > 0)
{
columns = await connection.QueryAsync(qryViewMaster.selectViewColumns, new { ViewName, UserId });
}
else
{
columns = await connection.QueryAsync(qryViewMaster.selectDefaultColumns, new { ViewName });
}
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(columns);
}
public static bool SaveGinDetail(whgi2 ginDetail)
{
int afRecCnt = 0;
string storeProcName;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
int ginDetailCnt = connection.ExecuteScalar <int>(qryGoodsIssueNote.selectGinDetailCount,
new
{
TrxNo = ginDetail.TrxNo,
LineItemNo = ginDetail.LineItemNo
});
if (ginDetailCnt > 0)
{
storeProcName = qryGoodsIssueNote.updateGinDetail;
}
else
{
storeProcName = qryGoodsIssueNote.insertGinDetail;
}
var param = connection.GetStoreProcParams(storeProcName, ginDetail);
afRecCnt = connection.Execute(storeProcName, param, null, null, CommandType.StoredProcedure);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt > 0 ? true : false);
}
public static bool DeleteGin(int TrxNo, int Type)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
// delete gin
afRecCnt = connection.Execute(qryGoodsIssueNote.deleteGin,
new
{
TrxNo,
UpdateBy = ApiService.UserId,
Type
}, null, null, CommandType.StoredProcedure);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt > 0 ? true : false);
}
public static int SaveBarCodeItemDetail(whbi2 itemDetail)
{
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
itemDetail.LineItemNo = connection.ExecuteScalar <int>(qryBarCodeItem.SelectNewLineItemNo,
new
{
TrxNo = itemDetail.TrxNo,
TablePrefix = itemDetail.TablePrefix
});
itemDetail.WorkStation = ApiService.HostName;
itemDetail.CreateBy = ApiService.UserId;
itemDetail.CreateDateTime = ApiService.ClientDate;
connection.Execute(qryBarCodeItem.InsertBarcodeItemDetail, itemDetail);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(itemDetail.LineItemNo);
}
public static int DeleteGrnDetail(int TrxNo, int LineItemNo)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
// delete grn detail
afRecCnt += connection.Execute(qryGoodsReceiptNote.deleteGrnDetail,
new
{
TrxNo,
LineItemNo,
UpdateBy = ApiService.UserId
},
null, null, CommandType.StoredProcedure);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
var myGrn = GetGrn(TrxNo);
InventoryHelper.DeleteInvByBatch(myGrn.GoodsReceiptNoteNo, LineItemNo, myGrn.WarehouseCode);
return(afRecCnt);
}
public static int savePickList(whpl1 pickList)
{
int trxNo = 0;
string storeProcName;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
if (pickList.TrxNo > 0)
{
// update
storeProcName = qryPickList.updatePickList;
}
else
{
// insert
storeProcName = qryPickList.insertPickList;
}
connection.Open();
var param = connection.GetStoreProcParams(storeProcName, pickList);
trxNo = connection.ExecuteScalar <int>(storeProcName, param, null, null, System.Data.CommandType.StoredProcedure);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(trxNo);
}
/// <summary>
/// The dispose.
/// </summary>
/// <param name="disposing">
/// The disposing.
/// </param>
protected virtual void Dispose(bool disposing)
{
if (!WasDisposed && disposing && _connection != null)
{
ConnectionProvider.Close(_connection);
}
WasDisposed = true;
}
public static int SaveGrnDetails(List <whgr2> GrnDetails)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
// insert warehouse details
using (IDbTransaction transactionScope = connection.BeginTransaction(IsolationLevel.Serializable))
{
try
{
foreach (var grnDetail in GrnDetails)
{
int grnDetailCnt = connection.ExecuteScalar <int>(qryGoodsReceiptNote.selectGrnDetailCount,
new
{
TrxNo = grnDetail.TrxNo,
LineItemNo = grnDetail.LineItemNo
}, transactionScope, null, CommandType.Text);
string storeProcName;
if (grnDetailCnt <= 0)
{
// set insert store procedure
storeProcName = qryGoodsReceiptNote.insertGrnDetail;
}
else
{
// set update store procedure
storeProcName = qryGoodsReceiptNote.updateGrnDetail;
}
var param = connection.GetStoreProcParams(storeProcName, grnDetail, transactionScope);
afRecCnt += connection.Execute(storeProcName, param, transactionScope, null, CommandType.StoredProcedure);
}
transactionScope.Commit();
}
catch (Exception)
{
transactionScope.Rollback();
throw;
}
}
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt);
}
public static bool SavePoDetails(IEnumerable <whpo2> poDetails)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
using (IDbTransaction tranScope = connection.BeginTransaction(IsolationLevel.Serializable))
{
try
{
foreach (var poDetail in poDetails)
{
int poDetailCnt = connection.ExecuteScalar <int>(qryPurchaseOrder.selectPoDetailCount,
new
{
TrxNo = poDetail.TrxNo,
LineItemNo = poDetail.LineItemNo
}, tranScope);
string storeProcName;
if (poDetailCnt <= 0)
{
// set insert store procedure
storeProcName = qryPurchaseOrder.insertPoDetail;
}
else
{
// set update store procedure
storeProcName = qryPurchaseOrder.updatePoDetail;
}
var param = connection.GetStoreProcParams(storeProcName, poDetail, tranScope);
afRecCnt += connection.Execute(storeProcName, param, tranScope, null, CommandType.StoredProcedure);
}
tranScope.Commit();
}
catch (Exception)
{
tranScope.Rollback();
throw;
}
}
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt > 0 ? true : false);
}
public static bool SaveToInv(int TrxNo)
{
bool isDone = false;
// configure automapper
var config = new MapperConfiguration(cfg => cfg.CreateMap <whgi2, whiv1>()
.ForMember(dest => dest.BatchLineItemNo, opt => opt.MapFrom(src => src.LineItemNo))
.ForMember(dest => dest.TrxNo, opt => opt.Ignore())
);
var mapper = config.CreateMapper();
// get gin, gindetails
whgi1 gin = GetGin(TrxNo);
IEnumerable <whgi2> ginDetails = GetGinDetails(TrxNo);
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
foreach (var ginDetail in ginDetails)
{
// set minus values
ginDetail.Length = -1 * ginDetail.Length;
ginDetail.Width = -1 * ginDetail.Width;
ginDetail.Height = -1 * ginDetail.Height;
ginDetail.Weight = -1 * ginDetail.Weight;
ginDetail.Volume = -1 * ginDetail.Volume;
ginDetail.SpaceArea = -1 * ginDetail.SpaceArea;
ginDetail.Qty = -1 * ginDetail.Qty;
// map gin to inv
whiv1 myInv = mapper.Map <whiv1>(ginDetail);
myInv.BatchNo = gin.GoodsIssueNoteNo;
myInv.WarehouseCode = gin.WarehouseCode;
myInv.WorkStation = ApiService.HostName;
myInv.CreateBy = ApiService.UserId;
myInv.CreateDateTime = ApiService.ClientDate;
myInv.UpdateBy = ApiService.UserId;
myInv.UpdateDateTime = ApiService.ClientDate;
// save to inventory
isDone = InventoryHelper.SaveInv(myInv);
}
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(isDone);
}
public static whpo2 GetPoDetail(int TrxNo, int LineItemNo)
{
whpo2 poDetail = null;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
poDetail = connection.QuerySingleOrDefault <whpo2>(qryPurchaseOrder.selectPoDetail, new { TrxNo, LineItemNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(poDetail);
}
public static IEnumerable <whpo2> GetPoDetails(int TrxNo)
{
IEnumerable <whpo2> myPoDetails = null;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
myPoDetails = connection.Query <whpo2>(qryPurchaseOrder.selectPoDetails, new { TrxNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(myPoDetails);
}
public static string GetNewPoNo(DateTime PoDate)
{
string newPoNo;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
newPoNo = connection.ExecuteScalar <string>(qryPurchaseOrder.selectNewPoNo, new { PoDate });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(newPoNo);
}
public static dynamic GetPutAwayItem(string WarehouseCode, string SupplierCode)
{
dynamic myPutAwayItems = null;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
myPutAwayItems = connection.Query(qryGoodsReceiptNote.selectPutAwayItem, new { WarehouseCode, SupplierCode });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(myPutAwayItems);
}
public static whgi1 GetGin(int TrxNo)
{
whgi1 myGin = null;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
myGin = connection.QuerySingleOrDefault <whgi1>(qryGoodsIssueNote.selectGin, new { TrxNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(myGin);
}
public static string GetNewGinNo(DateTime IssueDate)
{
string newGinNo;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
newGinNo = connection.ExecuteScalar <string>(qryGoodsIssueNote.selectNewGinNo, new { IssueDate });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(newGinNo);
}
public static whpo1 GetPo(int TrxNo)
{
whpo1 myPo = null;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
myPo = connection.QuerySingleOrDefault <whpo1>(qryPurchaseOrder.selectPo, new { TrxNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(myPo);
}
/// <summary>
/// The dispose.
/// </summary>
/// <param name="disposing">
/// The disposing.
/// </param>
protected virtual void Dispose(bool disposing)
{
if (!_disposed && disposing)
{
if (_connection != null)
{
ConnectionProvider.Close(_connection);
}
if (Current == this)
{
Current = null;
}
}
_disposed = true;
}
public static whpl2 getPickListDetail(int TrxNo, int LineItemNo)
{
whpl2 pickListDetail;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
pickListDetail = connection.QuerySingle <whpl2>(qryPickList.selectPickListDetail, new { TrxNo, LineItemNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(pickListDetail);
}
public static string getNewPickNo(DateTime PickDate)
{
string myNewPickNo;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
myNewPickNo = connection.ExecuteScalar <string>(qryPickList.selectNewPickNo, new { PickDate });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(myNewPickNo);
}
public static whpl1 getPickList(int TrxNo)
{
whpl1 pickList;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
pickList = connection.QuerySingleOrDefault <whpl1>(qryPickList.selectPickList, new { TrxNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(pickList);
}
public static IEnumerable <whpl2> getPickListDetails(int TrxNo)
{
IEnumerable <whpl2> pickListDetails;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
pickListDetails = connection.Query <whpl2>(qryPickList.selectPickListDetails, new { TrxNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(pickListDetails);
}
public static bool DeleteGinDetail(int TrxNo, int LineItemNo)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
afRecCnt = connection.Execute(qryGoodsIssueNote.deleteGinDetail, new { TrxNo, LineItemNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt > 0 ? true : false);
}
public static IEnumerable <whgi2> GetGinDetails(int TrxNo)
{
IEnumerable <whgi2> ginDetails = null;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
ginDetails = connection.Query <whgi2>(qryGoodsIssueNote.selectGinDetails, new { TrxNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(ginDetails);
}
public static whgi2 GetGinDetail(int TrxNo, int LineItemNo)
{
whgi2 ginDetail = null;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
ginDetail = connection.QuerySingleOrDefault <whgi2>(qryGoodsIssueNote.selectGinDetail, new { TrxNo, LineItemNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(ginDetail);
}
public static List <whgr2> GetGrnDetails(int TrxNo)
{
List <whgr2> myGrnDetails = null;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
myGrnDetails = connection.Query <whgr2>(qryGoodsReceiptNote.selectGrnDetails, new { TrxNo }).ToList();
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(myGrnDetails);
}
public static bool DeleteBarcodeItemDetail(int TrxNo, string TablePrefix, int LineItemNo)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
afRecCnt = connection.Execute(qryBarCodeItem.DeleteBarcodeItemDetail,
new { TrxNo, TablePrefix, LineItemNo });
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt > 0 ? true : false);
}
public static int SaveGin(whgi1 gin)
{
int trxNo = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
string storeProcName;
if (gin.TrxNo <= 0)
{
// set audit values
gin.WorkStation = ApiService.HostName;
gin.CreateBy = ApiService.UserId;
gin.CreateDateTime = ApiService.ClientDate;
gin.UpdateBy = ApiService.UserId;
gin.UpdateDateTime = ApiService.ClientDate;
// set insert store procedure
storeProcName = qryGoodsIssueNote.insertGin;
}
else
{
// set audit values
gin.WorkStation = ApiService.HostName;
gin.UpdateBy = ApiService.UserId;
gin.UpdateDateTime = ApiService.ClientDate;
// set update store procedure
storeProcName = qryGoodsIssueNote.updateGin;
}
var myParam = connection.GetStoreProcParams(storeProcName, gin);
trxNo = connection.ExecuteScalar <int>(storeProcName, myParam, null, null, CommandType.StoredProcedure);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(trxNo);
}
public static int SavePo(whpo1 po)
{
int trxNo = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
string storeProcName;
if (po.TrxNo <= 0)
{
// set audit values
po.WorkStation = ApiService.HostName;
po.CreateBy = ApiService.UserId;
po.CreateDateTime = ApiService.ClientDate;
po.UpdateBy = ApiService.UserId;
po.UpdateDateTime = ApiService.ClientDate;
// set insert store procedure
storeProcName = qryPurchaseOrder.insertPo;
}
else
{
// set audit values
po.WorkStation = ApiService.HostName;
po.UpdateBy = ApiService.UserId;
po.UpdateDateTime = ApiService.ClientDate;
// set update store procedure
storeProcName = qryPurchaseOrder.updatePo;
}
connection.Open();
var param = connection.GetStoreProcParams(storeProcName, po);
trxNo = connection.ExecuteScalar <int>(storeProcName, param, null, null, CommandType.StoredProcedure);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(trxNo);
}
public void ServerDies()
{
TcpListener listener = new TcpListener(Dns.Resolve("localhost").AddressList[0], 6667);
listener.Start();
ConnectionProvider provider = new ConnectionProvider("localhost", 6667);
TcpClient client = listener.AcceptTcpClient();
try
{
client.Close();
provider.SendMessage("hello");
provider.SendMessage("world");
Assert.Fail();
}
catch(ConnectionProviderException exception)
{
Assert.AreEqual("Error writting message to server.", exception.Message);
}
finally
{
listener.Stop();
provider.Close();
}
}
