public async Task CreateTraining(Training training)
{
await connectionProvider.EstablishConnection();
connectionProvider.Execute(
"INSERT INTO training(name, description, datetime, createdBy) VALUES(@name, @description, @datetime, @createdBy)",
new {
name = training.Name,
description = training.Description,
datetime = training.DateTime,
createdBy = training.CreatedBy
});
connectionProvider.Close();
}
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 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 SaveBarCodeItem(whbi1 item)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
int barcodeItemCnt = connection.ExecuteScalar <int>(qryBarCodeItem.SelectTrxNoCnt,
new
{
TrxNo = item.TrxNo,
TablePrefix = "GRN"
});
if (barcodeItemCnt > 0)
{
connection.Execute(qryBarCodeItem.DeleteBarcodeItem,
new
{
TrxNo = item.TrxNo,
TablePrefix = "GRN"
});
}
item.CreateBy = ApiService.UserId;
item.CreateDateTime = ApiService.ClientDate;
afRecCnt = connection.Execute(qryBarCodeItem.InsertBarcodeItem, item);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt > 0 ? true : false);
}
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 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 bool deletePickList(int TrxNo, int Type)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
afRecCnt = connection.Execute(qryPickList.deletePickList,
new
{
TrxNo,
Type
});
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt > 0 ? true : false);
}
public static bool savePickListDetail(whpl2 pickListDetail)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
int pickListDetailCnt = connection.ExecuteScalar <int>(qryPickList.selectPickListDetailCnt,
new
{
TrxNo = pickListDetail.TrxNo,
LineItemNo = pickListDetail.LineItemNo
}, null, null, CommandType.Text);
string storeProcName;
if (pickListDetailCnt <= 0)
{
// insert
storeProcName = qryPickList.insertPickListDetail;
}
else
{
// update
storeProcName = qryPickList.updatePickListDetail;
}
var param = connection.GetStoreProcParams(storeProcName, pickListDetail);
afRecCnt = connection.Execute(storeProcName, param, null, null, CommandType.StoredProcedure);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt > 0 ? true : false);
}
public static int SaveGrnDetail(whgr2 GrnDetail)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
int grnDetailCnt = connection.ExecuteScalar <int>(qryGoodsReceiptNote.selectGrnDetailCount,
new
{
TrxNo = GrnDetail.TrxNo,
LineItemNo = GrnDetail.LineItemNo
});
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);
afRecCnt = connection.Execute(storeProcName, param, null, null, CommandType.StoredProcedure);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt);
}
public static bool SavePoDetail(whpo2 poDetail)
{
int afRecCnt = 0;
string storeProcName;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
int poDetailCnt = connection.ExecuteScalar <int>(qryPurchaseOrder.selectPoDetailCount,
new
{
TrxNo = poDetail.TrxNo,
LineItemNo = poDetail.LineItemNo
});
if (poDetailCnt > 0)
{
storeProcName = qryPurchaseOrder.updatePoDetail;
}
else
{
storeProcName = qryPurchaseOrder.insertPoDetail;
}
var myParams = connection.GetStoreProcParams(storeProcName, poDetail);
afRecCnt = connection.Execute(storeProcName, myParams, null, null, CommandType.StoredProcedure);
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
return(afRecCnt > 0 ? true : false);
}
public static string AssignBinNo(int TrxNo, int LineItemNo)
{
int afRecCnt = 0;
whgr1 myGrn = GetGrn(TrxNo);
whgr2 myGrnDetail = GetGrnDetail(TrxNo, LineItemNo);
string WarehouseCode = myGrn.WarehouseCode;
decimal?StoreSpace = myGrnDetail.SpaceArea;
string BinNo;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
// get bin no
BinNo = connection.ExecuteScalar <string>(qryGoodsReceiptNote.selectAvalBinNo,
new
{
WarehouseCode,
StoreSpace
}, null, null, CommandType.StoredProcedure);
if (!string.IsNullOrEmpty(BinNo))
{
// update bin no
afRecCnt = connection.Execute(qryGoodsReceiptNote.updateBinNo,
new
{
TrxNo,
LineItemNo,
BinNo,
UpdateBy = ApiService.UserId
});
myGrnDetail.BinNo = BinNo;
}
}
catch (Exception) { throw; }
finally { connection.Close(); }
}
// update inventory
if (afRecCnt > 0)
{
var config = new MapperConfiguration(cfg => cfg.CreateMap <whgr2, whiv1>()
.ForMember(dest => dest.BatchLineItemNo, opt => opt.MapFrom(src => src.LineItemNo))
.ForMember(dest => dest.TrxNo, opt => opt.Ignore())
);
var mapper = config.CreateMapper();
var myInv = mapper.Map <whiv1>(myGrnDetail);
myInv.BatchNo = myGrn.GoodsReceiptNoteNo;
myInv.WarehouseCode = myGrn.WarehouseCode;
myInv.WorkStation = ApiService.HostName;
myInv.CreateBy = ApiService.UserId;
myInv.CreateDateTime = ApiService.ClientDate;
myInv.UpdateBy = ApiService.UserId;
myInv.UpdateDateTime = ApiService.ClientDate;
InventoryHelper.SaveInv(myInv);
}
return(BinNo);
}