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 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);
}
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 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 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 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 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 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 static bool SaveInv(whiv1 Inv)
{
int afRecCnt = 0;
using (var connection = new ConnectionProvider(ApiService.Site).CreateDbConnection())
{
try
{
connection.Open();
string storeProcName;
if (Inv.TrxNo <= 0)
{
Inv.StatusCode = "USE";
Inv.WorkStation = ApiService.HostName;
Inv.CreateBy = ApiService.UserId;
Inv.CreateDateTime = ApiService.ClientDate;
Inv.UpdateBy = ApiService.UserId;
Inv.UpdateDateTime = ApiService.ClientDate;
storeProcName = qryInventory.insertInv;
}
else
{
Inv.WorkStation = ApiService.HostName;
Inv.UpdateBy = ApiService.UserId;
Inv.UpdateDateTime = ApiService.ClientDate;
storeProcName = qryInventory.updateInv;
}
var param = connection.GetStoreProcParams(storeProcName, Inv);
afRecCnt = connection.ExecuteScalar <int>(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 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 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 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 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);
}
protected override void ExecuteQuery(OracleCommand command)
{
RawData = ConnectionProvider.ExecuteScalar(command);
}
