public int Confirm_Imgr1(Imgr request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
Result = db.SqlScalar <int>("EXEC spi_Imgr_Confirm @TrxNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), UpdateBy = request.UserID });
if (Result != -1)
{
List <Imgr2_Receipt> Result1 = null;
Result1 = db.Select <Imgr2_Receipt>(
"select Imgr1.GoodsReceiptNoteNo,Imgr1.CustomerCode,Imgr2.LineItemNo,Imgr1.TrxNo from imgr1 join imgr2 on imgr1.TrxNo =imgr2.TrxNo where Imgr1.TrxNo = '" + request.TrxNo + "' "
);
if (Result1 != null && Result1.Count > 0)
{
for (int i = 0; i < Result1.Count; i++)
{
Result = db.SqlScalar <int>("Update Imgr2 Set MovementTrxNo=(Select TrxNo From Impm1 Where BatchNo=@GoodsReceiptNoteNo And BatchLineItemNo=@BatchLineItemNo And CustomerCode=@CustomerCode) Where TrxNo=@TrxNo And LineItemNo=@LineItemNo", new { GoodsReceiptNoteNo = Result1[i].GoodsReceiptNoteNo, BatchLineItemNo = Result1[i].LineItemNo, CustomerCode = Result1[i].CustomerCode, TrxNo = int.Parse(request.TrxNo), LineItemNo = Result1[i].LineItemNo });
}
}
}
}
}
catch { throw; }
return(Result);
}
public List <Imgr2_Putaway> Get_Imgr2_Putaway_List(Imgr request)
{
List <Imgr2_Putaway> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
//string strDefaultStoreNo = " IsNull(( Select Top 1 StoreNo from Impm1 Where Impm1.CustomerCode = 'UNITED' and Impm1.ProductTrxNo = 5 AND Impm1.TrxType = '1' and( case DimensionFlag when '1' then " +
//" isnull(BalancePackingQty, 0) when '2' then isnull(BalanceWholeQty, 0) else isnull(BalanceLooseQty, 0) end) > 0 Order By Impm1.ReceiptDate DEsc),'') as DefaultStoreNo ";
string strDefaultStoreNo = " IsNull(( Select Top 1 case (( case DimensionFlag when '1' then isnull(BalancePackingQty,0) when '2' then isnull(BalanceWholeQty,0) else " +
" isnull(BalanceLooseQty, 0) end )) when 0 then '' else StoreNo end AS DefaultStoreNo from Impm1 Where " +
" Impm1.CustomerCode =Imgr1.CustomerCode and Impm1.ProductTrxNo = imgr2.ProductTrxNo AND Impm1.TrxType = '1' Order By Impm1.ReceiptDate DEsc ),'') as DefaultStoreNo ";
string strSql = "Select Imgr2.TrxNo, Imgr2.LineItemNo, Imgr2.StoreNo," + strDefaultStoreNo + ",0 as ProductIndex," +
"isnull((Select StagingAreaFlag From Whwh2 Where WarehouseCode=Imgr2.WarehouseCode And StoreNo=Imgr2.StoreNo),'N') AS StagingAreaFlag," +
"IsNull(Imgr2.ProductCode,'') AS ProductCode, IsNull(Imgr2.ProductDescription,'') AS ProductDescription, IsNull(Imgr2.UserDefine1,'') AS UserDefine1," +
"(Case Imgr2.DimensionFlag When '1' Then Imgr2.PackingQty When '2' Then Imgr2.WholeQty Else Imgr2.LooseQty End) AS Qty,(Case Imgr2.DimensionFlag When '1' Then Imgr2.PackingQty When '2' Then Imgr2.WholeQty Else Imgr2.LooseQty End) AS ActualQty," + getBarCodeListSelect() +
"(Select Top 1 SerialNoFlag From Impr1 Where TrxNo=Imgr2.ProductTrxNo) AS SerialNoFlag," +
"0 AS ScanQty,Imgr2.DimensionFlag,Imgr2.PackingQty,Imgr2.WholeQty,Imgr2.LooseQty,Imgr1.GoodsReceiptNoteNo,'' As QtyStatus,'' AS NewBarCode,'' AS NewFlag " +
"From Imgr2 " +
"Left Join Imgr1 On Imgr2.TrxNo = Imgr1.TrxNo " +
"Where Imgr1.GoodsReceiptNoteNo='" + request.GoodsReceiptNoteNo + "'";
Result = db.Select <Imgr2_Putaway>(strSql);
}
}
catch { throw; }
return(Result);
}
public int Update_Imgr2_StoreNo(Imgr request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
Result = db.Update <Imgr2>(
new
{
StoreNo = request.StoreNo
},
p => p.TrxNo == int.Parse(request.TrxNo) && p.LineItemNo == int.Parse(request.LineItemNo)
);
Result = db.Update <Impm1>(
new
{
StoreNo = request.StoreNo
},
p => p.BatchNo == request.GoodsReceiptNoteNo && p.BatchLineItemNo == int.Parse(request.LineItemNo)
);
}
}
catch { throw; }
return(Result);
}
public List <Imgr2_Receipt> Get_Imgr2_Receipt_List(Imgr request)
{ // Tropolis
List <Imgr2_Receipt> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
List <Impa1> impa1 = db.Select <Impa1>("Select * from Impa1");
string strBarCodeFiled = impa1[0].BarCodeField;
string strSql = "Select Imgr2.*, " +
"(Select Top 1 " + strBarCodeFiled + " From Impr1 Where TrxNo=Imgr2.ProductTrxNo) AS BarCode,Imgr1.CustomerCode," +
//"(Select Top 1 SerialNoFlag From Impr1 Where TrxNo=Imgr2.ProductTrxNo) AS SerialNoFlag," +
" '' AS SerialNoFlag," + // Tropolis
"0 AS ScanQty , " +
"0 AS SumScanQty , " +
"0 AS SumAcutalQty , " +
" '' AS ScanedBarCodeFlag " +
"From Imgr2 " +
"Left Join Imgr1 On Imgr2.TrxNo = Imgr1.TrxNo " +
"Where Imgr1.TrxNo='" + request.TrxNo + "'";
Result = db.Select <Imgr2_Receipt>(strSql);
}
}
catch { throw; }
return(Result);
}
public int Confirm_Imgr1(Imgr request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
Result = db.SqlScalar <int>("EXEC spi_Imgr_Confirm @TrxNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), UpdateBy = request.UserID });
//List<int> results = db.SqlList<int>("EXEC spi_Imgr_Confirm @TrxNo @UpdateBy", new { TrxNo = request.TrxNo, UpdateBy = request.UserID });
//using (var cmd = db.SqlProc("spi_Imgr_Confirm", new { TrxNo = request.TrxNo, UpdateBy = request.UserID }))
//{
// Result = cmd.ConvertTo<int>();
//}
if (Result > -1)
{
string GoodsReceiptNoteNo = db.Scalar <string>("Select GoodsReceiptNoteNo From Imgr1 Where TrxNo=" + int.Parse(request.TrxNo));
db.Insert(
new Imsl1
{
DocNo = GoodsReceiptNoteNo,
Description = "TALLY",
StatusLogDateTime = DateTime.Now,
UserId = request.UserID,
StatusCode = "EXE",
UpdateBy = request.UserID,
UpdateDateTime = DateTime.Now
}
);
}
}
}
catch { throw; }
return(Result);
}
public List <Imgr2_Putaway> Get_Imgr2_Putaway_List(Imgr request)
{
List <Imgr2_Putaway> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
string strSql = "Select " +
"Imgr2.TrxNo, Imgr2.LineItemNo, Imgr2.ProductTrxNo," +
"IsNull(Imgr2.ProductCode,'') AS ProductCode, IsNull(Imgr2.ProductDescription,'') AS ProductDescription," +
"IsNull(Imgr2.SerialNo,'') AS SerialNo, IsNull(Imgr2.DimensionFlag,'') AS DimensionFlag," +
"IsNull(Imgr2.StoreNo,'') AS StoreNo," +
"(Select StagingAreaFlag From Whwh2 Where WarehouseCode=Imgr2.WarehouseCode And StoreNo=Imgr2.StoreNo) AS StagingAreaFlag," +
"Imgr2.PackingQty, Imgr2.WholeQty, Imgr2.LooseQty," +
"0 AS ScanQty " +
"From Imgr2 " +
"Left Join Imgr1 On Imgr2.TrxNo = Imgr1.TrxNo " +
"Where Imgr1.GoodsReceiptNoteNo='" + request.GoodsReceiptNoteNo + "'";
Result = db.Select <Imgr2_Putaway>(strSql);
}
}
catch { throw; }
return(Result);
}
public List <Imgr2> Get_Imgr2_List(Imgr request)
{
List <Imgr2> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
Result = db.Select <Imgr2>(
"Select " +
"Imgr2.TrxNo, Imgr2.LineItemNo," +
"IsNull(Imgr2.StoreNo,'') AS StoreNo, IsNull(Imgr2.WarehouseCode,'') AS WarehouseCode," +
"Imgr2.MovementTrxNo, Imgr2.ProductTrxNo," +
"Imgr2.ProductCode, Imgr2.DimensionFlag," +
"Imgr2.PackingQty, Imgr2.WholeQty, Imgr2.LooseQty," +
"Imgr2.Volume, Imgr2.Weight, Imgr2.SpaceArea," +
"Imgr2.UserDefine1 " +
"From Imgr2 " +
"Left Join Imgr1 On Imgr2.TrxNo = Imgr1.TrxNo " +
"Where Imgr1.GoodsReceiptNoteNo='" + request.GoodsReceiptNoteNo + "'"
);
}
}
catch { throw; }
return(Result);
}
public List <Imgr1> Get_Imgr1_List(Imgr request)
{
List <Imgr1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
if (!string.IsNullOrEmpty(request.CustomerCode))
{
if (string.IsNullOrEmpty(request.StatusCode))
{
Result = db.SelectParam <Imgr1>(
i => i.GoodsReceiptNoteNo != null && i.GoodsReceiptNoteNo != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.StatusCode != "CMP" && i.CustomerCode == request.CustomerCode
).OrderByDescending(i => i.ReceiptDate).ToList <Imgr1>();
}
else
{
Result = db.SelectParam <Imgr1>(
i => i.GoodsReceiptNoteNo != null && i.GoodsReceiptNoteNo != "" && i.StatusCode == request.StatusCode && i.CustomerCode == request.CustomerCode
).OrderByDescending(i => i.ReceiptDate).ToList <Imgr1>();
}
}
else if (!string.IsNullOrEmpty(request.GoodsReceiptNoteNo))
{
if (string.IsNullOrEmpty(request.StatusCode))
{
//Result = db.SelectParam<Imgr1>(
// i => i.GoodsReceiptNoteNo != null && i.GoodsReceiptNoteNo != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.StatusCode != "CMP" && i.GoodsReceiptNoteNo.StartsWith(request.GoodsReceiptNoteNo)
//);
Result = db.Select <Imgr1>(
"Select Imgr1.* From Imgr1 " +
"Where IsNUll(StatusCode,'')<>'DEL' And IsNUll(StatusCode,'')<>'EXE' And IsNUll(StatusCode,'')<>'CMP' " +
"And (Select count(*) from Imgr2 Where Imgr2.TrxNo=Imgr1.TrxNo) > 0 " +
"And IsNUll(GoodsReceiptNoteNo,'') LIKE '" + request.GoodsReceiptNoteNo + "%'"
);
}
else
{
//Result = db.SelectParam<Imgr1>(
// i => i.GoodsReceiptNoteNo != null && i.GoodsReceiptNoteNo != "" && i.StatusCode == request.StatusCode && i.GoodsReceiptNoteNo.StartsWith(request.GoodsReceiptNoteNo)
//);
Result = db.Select <Imgr1>(
"Select Imgr1.* From Imgr1 " +
"Where IsNUll(StatusCode,'')='" + request.StatusCode + "' " +
"And (Select count(*) from Imgr2 Where Imgr2.TrxNo=Imgr1.TrxNo) > 0 " +
"And IsNUll(GoodsReceiptNoteNo,'') LIKE '" + request.GoodsReceiptNoteNo + "%'"
);
}
}
}
}
catch { throw; }
return(Result);
}
//public string[] test(string[] s) {
// if (s == null)
// {
// return null;
// }
// else {
// return s;
// }
//}
public int Update_Imgr2_StoreNo(Imgr request)
{
int Result = -1;
try
{
string[] QtyRemarkDetail = { "" };
if (request.QtyRemarkList != null && request.NewFlagList.Trim() != "")
{
QtyRemarkDetail = request.QtyRemarkList.Split(',');
}
string[] LineItemNoDetail = request.LineItemNoList.Split(',');
string[] DimensionFlagDetail = request.DimensionFlagList.Split(',');
string[] NewFlagDetail = request.NewFlagList.Split(',');
string[] DimensionQtyDetail = request.DimensionQtyList.Split(',');
string[] StoreNoDetail = request.StoreNoList.Split(',');
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string UpdateNewFlag = "N";
if (request.NewFlagList == null || request.NewFlagList.Trim() == "")
{
Result = db.SqlScalar <int>("EXEC spi_Imgr2_Mobile @TrxNo,@LineItemNo,@NewFlag,@DimensionQty,@QtyRemark,@DimensionFlag,@StoreNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), LineItemNo = int.Parse(request.LineItemNoList), NewFlag = UpdateNewFlag, DimensionQty = int.Parse(request.DimensionQtyList), QtyRemark = request.QtyRemarkList, DimensionFlag = request.DimensionFlagList, StoreNo = request.StoreNoList, UpdateBy = request.UserID });
}
else
{
for (int i = 0; i < DimensionFlagDetail.Length; i++)
{
UpdateNewFlag = NewFlagDetail[i];
if (UpdateNewFlag != "Y")
{
UpdateNewFlag = "N";
}
Result = db.SqlScalar <int>("EXEC spi_Imgr2_Mobile @TrxNo,@LineItemNo,@NewFlag,@DimensionQty,@QtyRemark,@DimensionFlag,@StoreNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), LineItemNo = int.Parse(LineItemNoDetail[i]), NewFlag = UpdateNewFlag, DimensionQty = DimensionQtyDetail[i], QtyRemark = QtyRemarkDetail[i], DimensionFlag = DimensionFlagDetail[i], StoreNo = StoreNoDetail[i], UpdateBy = request.UserID });
}
}
Result = db.SqlScalar <int>("EXEC spi_Imgr_Confirm @TrxNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), UpdateBy = request.UserID });
if (Result != -1)
{
List <Imgr2_Receipt> Result1 = null;
Result1 = db.Select <Imgr2_Receipt>(
"select Imgr1.GoodsReceiptNoteNo,Imgr1.CustomerCode,Imgr2.LineItemNo,Imgr1.TrxNo from imgr1 join imgr2 on imgr1.TrxNo =imgr2.TrxNo where Imgr1.TrxNo = '" + request.TrxNo + "' "
);
if (Result1 != null && Result1.Count > 0)
{
for (int i = 0; i < Result1.Count; i++)
{
Result = db.SqlScalar <int>("Update Imgr2 Set MovementTrxNo=(Select top 1 TrxNo From Impm1 Where BatchNo=@GoodsReceiptNoteNo And BatchLineItemNo=@BatchLineItemNo And CustomerCode=@CustomerCode) Where TrxNo=@TrxNo And LineItemNo=@LineItemNo", new { GoodsReceiptNoteNo = Result1[i].GoodsReceiptNoteNo, BatchLineItemNo = Result1[i].LineItemNo, CustomerCode = Result1[i].CustomerCode, TrxNo = int.Parse(request.TrxNo), LineItemNo = Result1[i].LineItemNo });
}
}
}
}
}
catch { throw; }
return(Result);
}
public int Confirm_Imgr1(Imgr request)
{
Get_Impa1_List();
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
int intMaxLineItemNo = 1;
List <Sael1> list1 = db.Select <Sael1>("Select Max(LineItemNo) LineItemNo from Sael1 Where TableName = 'Imgr1' and PrimaryKeyName ='GoodsReceiptNoteNo' and PrimaryKeyValue='" + request.GoodsReceiptNoteNo + "'");
if (request.GoodsReceiptNoteNo != null && request.GoodsReceiptNoteNo != "")
{
if (list1 != null)
{
if (list1[0].LineItemNo > 0)
{
intMaxLineItemNo = list1[0].LineItemNo + 1;
}
}
db.Insert(new Sael1
{
TableName = "Imgr1",
PrimaryKeyName = "GoodsReceiptNoteNo",
PrimaryKeyValue = request.GoodsReceiptNoteNo,
DateTime = DateTime.Now,
UpdateDatetime = DateTime.Now,
LineItemNo = intMaxLineItemNo,
UpdateBy = request.UserID,
Description = "GOODS TALLIED"
});
}
if (AppTallyConfirmStatus == "EXE")
{
Result = db.SqlScalar <int>("EXEC spi_Imgr_Confirm @TrxNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), UpdateBy = request.UserID });
}
if (AppTallyConfirmStatus != "")
{
string str;
str = "TallyDateTime=getDate(),StatusCode='" + AppTallyConfirmStatus + "' ";
db.Update("Imgr1",
str,
" GoodsReceiptNoteNo='" + request.GoodsReceiptNoteNo + " '");
}
}
}
catch { throw; }
return(Result);
}
public int Confirm_Imgr1(Imgr request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
Result = db.SqlScalar <int>("EXEC spi_Imgr_Confirm @TrxNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), UpdateBy = request.UserID });
}
}
catch { throw; }
return(Result);
}
public List <Imgr2_Putaway> Imgr2CheckExist(Imgr request)
{
List <Imgr2_Putaway> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSql = "Select StatusCode From Imgr1 " +
"Where TrxNo='" + request.TrxNo + "'";
Result = db.Select <Imgr2_Putaway>(strSql);
}
}
catch { throw; }
return(Result);
}
public List <Imgr2> Get_Imgr2_List(Imgr request)
{
List <Imgr2> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
Result = db.Select <Imgr2>(
"Select Imgr2.* From Imgr2 " +
"Left Join Imgr1 On Imgr2.TrxNo = Imgr1.TrxNo " +
"Where Imgr1.GoodsReceiptNoteNo='" + request.GoodsReceiptNoteNo + "'"
);
}
}
catch { throw; }
return(Result);
}
public List <Imgr2_Transfer> Get_Imgr2_Transfer_List(Imgr request)
{
List <Imgr2_Transfer> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSql = "Select Imgr2.*, " +
"(Select Top 1 SerialNoFlag From Impr1 Where TrxNo=Imgr2.ProductTrxNo) AS SerialNoFlag " +
"From Imgr2 " +
"Where Imgr2.TrxNo=" + int.Parse(request.TrxNo) + " And Imgr2.LineItemNo=" + int.Parse(request.LineItemNo);
Result = db.Select <Imgr2_Transfer>(strSql);
}
}
catch { throw; }
return(Result);
}
public int Confirm_Imgr1(Imgr request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
Result = db.SqlScalar <int>("EXEC spi_Imgr_Confirm @TrxNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), UpdateBy = request.UserID });
//List<int> results = db.SqlList<int>("EXEC spi_Imgr_Confirm @TrxNo @UpdateBy", new { TrxNo = request.TrxNo, UpdateBy = request.UserID });
//using (var cmd = db.SqlProc("spi_Imgr_Confirm", new { TrxNo = request.TrxNo, UpdateBy = request.UserID }))
//{
// Result = cmd.ConvertTo<int>();
//}
}
}
catch { throw; }
return(Result);
}
public List <Imgr1> Get_Imgr1_List(Imgr request)
{
string strImgr = "";
List <Imgr1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
if (!string.IsNullOrEmpty(request.UserDefine01))
{
if (string.IsNullOrEmpty(request.StatusCode))
{
strImgr = "Select Top 10 Imgr1.* From Imgr1 " +
"Where IsNull(GoodsReceiptNoteNo,'')<>'' And IsNUll(StatusCode,'')<>'DEL' And IsNUll(StatusCode,'')<>'EXE' And IsNUll(StatusCode,'')<>'CMP' " +
"And (Select count(*) from Imgr2 Where Imgr2.TrxNo=Imgr1.TrxNo) > 0 " +
"And IsNUll(UserDefine01,'') = '" + request.UserDefine01 + "' " +
"Order By Imgr1.ReceiptDate Desc";
Result = db.Select <Imgr1>(
strImgr
);
}
}
else if (!string.IsNullOrEmpty(request.InvoiceNo))
{
if (string.IsNullOrEmpty(request.StatusCode))
{
strImgr = "Select Top 10 Imgr1.* From Imgr1 " +
"Where IsNUll(StatusCode,'')<>'DEL' And IsNUll(StatusCode,'')<>'EXE' And IsNUll(StatusCode,'')<>'CMP' " +
"And (Select count(*) from Imgr2 Where Imgr2.TrxNo=Imgr1.TrxNo) > 0 " +
"And IsNUll(CustGrnNo,'') LIKE '" + request.InvoiceNo + "%'";
Result = db.Select <Imgr1>(
strImgr
);
}
}
}
}
catch { throw; }
return(Result);
}
public List <Imgr2_Putaway> Get_Imgr2_Putaway_List(Imgr request)
{
List <Imgr2_Putaway> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSql = "Select Imgr2.TrxNo, Imgr2.LineItemNo, IsNull(Imgr2.StoreNo,'') AS StoreNo," +
"(Select StagingAreaFlag From Whwh2 Where WarehouseCode=Imgr2.WarehouseCode And StoreNo=Imgr2.StoreNo) AS StagingAreaFlag,Imgr1.CustomerCode," +
"IsNull(Imgr2.ProductCode,'') AS ProductCode, IsNull(Imgr2.ProductDescription,'') AS ProductDescription, IsNull(Imgr2.UserDefine1,'') AS UserDefine1," +
"(Case Imgr2.DimensionFlag When '1' Then Imgr2.PackingQty When '2' Then Imgr2.WholeQty Else Imgr2.LooseQty End) AS Qty " +
"From Imgr2 " +
"Left Join Imgr1 On Imgr2.TrxNo = Imgr1.TrxNo " +
"Where Imgr1.GoodsReceiptNoteNo='" + request.GoodsReceiptNoteNo + "'";
Result = db.Select <Imgr2_Putaway>(strSql);
}
}
catch { throw; }
return(Result);
}
public List <Imgr2_Receipt> Get_Imgr2_Receipt_List(Imgr request)
{
List <Imgr2_Receipt> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSql = "Select Imgr2.*,'' As QtyStatus, " +
"" + getBarCodeListSelect() +
"(Select Top 1 SerialNoFlag From Impr1 Where TrxNo=Imgr2.ProductTrxNo) AS SerialNoFlag," +
"0 AS ScanQty,GoodsReceiptNoteNo " +
"From Imgr2 " +
"Left Join Imgr1 On Imgr2.TrxNo = Imgr1.TrxNo " +
"Where Imgr1.GoodsReceiptNoteNo='" + Modfunction.SQLSafe(request.GoodsReceiptNoteNo) + "'";
Result = db.Select <Imgr2_Receipt>(strSql);
}
}
catch { throw; }
return(Result);
}
public List <Imgr2_Putaway> Get_Imgr2_Putaway_List(Imgr request)
{
List <Imgr2_Putaway> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
List <Impa1> impa1 = db.Select <Impa1>("Select * from Impa1");
string strBarCodeFiled = impa1[0].BarCodeField;
string strSql = "Select Imgr2.*, " +
"(Select Top 1 " + strBarCodeFiled + " From Impr1 Where TrxNo=Imgr2.ProductTrxNo) AS BarCode," +
"(Select StagingAreaFlag From Whwh2 Where WarehouseCode=Imgr2.WarehouseCode And StoreNo=Imgr2.StoreNo) AS StagingAreaFlag " +
"From Imgr2 " +
"Left Join Imgr1 On Imgr2.TrxNo = Imgr1.TrxNo " +
"Where Imgr1.GoodsReceiptNoteNo='" + request.GoodsReceiptNoteNo + "'";
Result = db.Select <Imgr2_Putaway>(strSql);
}
}
catch { throw; }
return(Result);
}
public List <Imgr2_Transfer> Get_Imgr2_Transfer_List(Imgr request)
{
List <Imgr2_Transfer> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSql = "Select Imgr2.TrxNo, Imgr2.LineItemNo, IsNull(Imgr2.StoreNo,'') AS StoreNo," +
"(Select StagingAreaFlag From Whwh2 Where WarehouseCode=Imgr2.WarehouseCode And StoreNo=Imgr2.StoreNo) AS StagingAreaFlag," +
"IsNull(Imgr2.ProductCode,'') AS ProductCode, IsNull(Imgr2.ProductDescription,'') AS ProductDescription," +
"(Case Imgr2.DimensionFlag When '1' Then Imgr2.PackingQty When '2' Then Imgr2.WholeQty Else Imgr2.LooseQty End) AS Balance," +
"0 AS Qty, '' AS NewStoreNo,'' As QtyStatus ," + getBarCodeListSelect() +
"From Imgr2 " +
"Left Join Imgr1 On Imgr2.TrxNo = Imgr1.TrxNo " +
"Where Imgr1.GoodsReceiptNoteNo='" + request.GoodsReceiptNoteNo + "'";
Result = db.Select <Imgr2_Transfer>(strSql);
}
}
catch { throw; }
return(Result);
}
public int Update_Imgr2_StoreNo(Imgr request)
{
int Result = -1;
try
{
string[] QtyRemarkDetail = request.QtyRemarkList.Split(',');
string[] LineItemNoDetail = request.LineItemNoList.Split(',');
string[] DimensionFlagDetail = request.DimensionFlagList.Split(',');
string[] NewFlagDetail = request.NewFlagList.Split(',');
string[] DimensionQtyDetail = request.DimensionQtyList.Split(',');
string[] StoreNoDetail = request.StoreNoList.Split(',');
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string UpdateNewFlag = "N";
if (request.NewFlagList.Trim() == "")
{
Result = db.SqlScalar <int>("EXEC spi_Imgr2_Mobile @TrxNo,@LineItemNo,@NewFlag,@DimensionQty,@QtyRemark,@DimensionFlag,@StoreNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), LineItemNo = int.Parse(request.LineItemNoList), NewFlag = UpdateNewFlag, DimensionQty = int.Parse(request.DimensionQtyList), QtyRemark = request.QtyRemarkList, DimensionFlag = request.DimensionFlagList, StoreNo = request.StoreNoList, UpdateBy = request.UserID });
}
else
{
for (int i = 0; i < DimensionFlagDetail.Length; i++)
{
UpdateNewFlag = NewFlagDetail[i];
if (UpdateNewFlag != "Y")
{
UpdateNewFlag = "N";
}
Result = db.SqlScalar <int>("EXEC spi_Imgr2_Mobile @TrxNo,@LineItemNo,@NewFlag,@DimensionQty,@QtyRemark,@DimensionFlag,@StoreNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), LineItemNo = int.Parse(LineItemNoDetail[i]), NewFlag = UpdateNewFlag, DimensionQty = DimensionQtyDetail[i], QtyRemark = QtyRemarkDetail[i], DimensionFlag = DimensionFlagDetail[i], StoreNo = StoreNoDetail[i], UpdateBy = request.UserID });
}
}
Result = db.SqlScalar <int>("EXEC spi_Imgr_Confirm @TrxNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), UpdateBy = request.UserID });
}
}
catch { throw; }
return(Result);
}
public int Update_Imgr2_QtyRemark(Imgr request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
if (request.QtyFieldName == "PackingQty")
{
Result = db.Update <Imgr2>(
new
{
PackingQty = request.QtyRemarkQty,
UpdateBy = request.UserID
},
p => p.TrxNo == int.Parse(request.TrxNo) && p.LineItemNo == int.Parse(request.LineItemNo)
);
Result = db.Update("Impm1", "BalancePackingQty = " + request.QtyRemarkQty
,
" BatchNo = " + Modfunction.SQLSafeValue(request.GoodsReceiptNoteNo) + " AND BatchLineItemNo = " + Modfunction.SQLSafeValue(request.LineItemNo)
);
}
else if (request.QtyFieldName == "WholeQty")
{
Result = db.Update <Imgr2>(
new
{
WholeQty = request.QtyRemarkQty,
UpdateBy = request.UserID
},
p => p.TrxNo == int.Parse(request.TrxNo) && p.LineItemNo == int.Parse(request.LineItemNo)
);
Result = db.Update("Impm1", "BalanceWholeQty = " + request.QtyRemarkQty
,
" BatchNo = " + Modfunction.SQLSafeValue(request.GoodsReceiptNoteNo) + " AND BatchLineItemNo = " + Modfunction.SQLSafeValue(request.LineItemNo)
);
}
else
{
Result = db.Update <Imgr2>(
new
{
LooseQty = request.QtyRemarkQty,
UpdateBy = request.UserID
},
p => p.TrxNo == int.Parse(request.TrxNo) && p.LineItemNo == int.Parse(request.LineItemNo)
);
Result = db.Update("Impm1", "BalanceLooseQty = " + request.QtyRemarkQty
,
" BatchNo = " + Modfunction.SQLSafeValue(request.GoodsReceiptNoteNo) + " AND BatchLineItemNo = " + Modfunction.SQLSafeValue(request.LineItemNo)
);
}
Result = db.Update <Imgr1>(" Remark=isnull(Remark,'') + (case isnull(Remark,'') when '' then '' else char(13)+char(10) end) + " + Modfunction.SQLSafeValue(request.QtyRemark) + ",UpdateDateTime = getdate(),UpdateBy = " + Modfunction.SQLSafeValue(request.UserID)
,
" TrxNo = " + request.TrxNo
);
}
}
catch { throw; }
return(Result);
}
//public string[] test(string[] s) {
// if (s == null)
// {
// return null;
// }
// else {
// return s;
// }
//}
public int Update_Imgr2_StoreNo(Imgr request)
{
Get_Impa1_List();
int Result = -1;
try
{
string[] QtyRemarkDetail = { "" };
if (request.QtyRemarkList != null && request.NewFlagList.Trim() != "")
{
QtyRemarkDetail = request.QtyRemarkList.Split(',');
}
string[] LineItemNoDetail = request.LineItemNoList.Split(',');
string[] DimensionFlagDetail = request.DimensionFlagList.Split(',');
string[] NewFlagDetail = request.NewFlagList.Split(',');
string[] DimensionQtyDetail = request.DimensionQtyList.Split(',');
string[] StoreNoDetail = request.StoreNoList.Split(',');
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string UpdateNewFlag = "N";
if (request.NewFlagList == null || request.NewFlagList.Trim() == "")
{
Result = db.SqlScalar <int>("EXEC spi_Imgr2_Mobile @TrxNo,@LineItemNo,@NewFlag,@DimensionQty,@QtyRemark,@DimensionFlag,@StoreNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), LineItemNo = int.Parse(request.LineItemNoList), NewFlag = UpdateNewFlag, DimensionQty = int.Parse(request.DimensionQtyList), QtyRemark = request.QtyRemarkList, DimensionFlag = request.DimensionFlagList, StoreNo = request.StoreNoList, UpdateBy = request.UserID });
}
else
{
for (int i = 0; i < DimensionFlagDetail.Length; i++)
{
UpdateNewFlag = NewFlagDetail[i];
if (UpdateNewFlag != "Y")
{
UpdateNewFlag = "N";
}
Result = db.SqlScalar <int>("EXEC spi_Imgr2_Mobile @TrxNo,@LineItemNo,@NewFlag,@DimensionQty,@QtyRemark,@DimensionFlag,@StoreNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), LineItemNo = int.Parse(LineItemNoDetail[i]), NewFlag = UpdateNewFlag, DimensionQty = DimensionQtyDetail[i], QtyRemark = QtyRemarkDetail[i], DimensionFlag = DimensionFlagDetail[i], StoreNo = StoreNoDetail[i], UpdateBy = request.UserID });
}
}
int intMaxLineItemNo = 1;
List <Sael1> list1 = db.Select <Sael1>("Select Max(LineItemNo) LineItemNo from Sael1 Where TableName = 'Imgr1' and PrimaryKeyName ='GoodsReceiptNoteNo' and PrimaryKeyValue='" + request.GoodsReceiptNoteNo + "'");
if (request.GoodsReceiptNoteNo != null && request.GoodsReceiptNoteNo != "")
{
if (list1 != null)
{
if (list1[0].LineItemNo > 0)
{
intMaxLineItemNo = list1[0].LineItemNo + 1;
}
}
db.Insert(new Sael1
{
TableName = "Imgr1",
PrimaryKeyName = "GoodsReceiptNoteNo",
PrimaryKeyValue = request.GoodsReceiptNoteNo,
DateTime = DateTime.Now,
UpdateDatetime = DateTime.Now,
LineItemNo = intMaxLineItemNo,
UpdateBy = request.UserID,
Description = "PUTAWAY"
});
}
if (AppPutawayConfirmStatus == "EXE")
{
Result = db.SqlScalar <int>("EXEC spi_Imgr_Confirm @TrxNo,@UpdateBy", new { TrxNo = int.Parse(request.TrxNo), UpdateBy = request.UserID });
if (Result != -1)
{
List <Imgr2_Receipt> Result1 = null;
Result1 = db.Select <Imgr2_Receipt>(
"select Imgr1.GoodsReceiptNoteNo,Imgr1.CustomerCode,Imgr2.LineItemNo,Imgr1.TrxNo from imgr1 join imgr2 on imgr1.TrxNo =imgr2.TrxNo where Imgr1.TrxNo = '" + request.TrxNo + "' "
);
if (Result1 != null && Result1.Count > 0)
{
for (int i = 0; i < Result1.Count; i++)
{
Result = db.SqlScalar <int>("Update Imgr2 Set MovementTrxNo=(Select top 1 TrxNo From Impm1 Where BatchNo=@GoodsReceiptNoteNo And BatchLineItemNo=@BatchLineItemNo And CustomerCode=@CustomerCode order by TrxNo) Where TrxNo=@TrxNo And LineItemNo=@LineItemNo", new { GoodsReceiptNoteNo = Result1[i].GoodsReceiptNoteNo, BatchLineItemNo = Result1[i].LineItemNo, CustomerCode = Result1[i].CustomerCode, TrxNo = int.Parse(request.TrxNo), LineItemNo = Result1[i].LineItemNo });
}
}
}
}
if (AppPutawayConfirmStatus != "")
{
string str;
str = "PutAwayDateTime=getDate() , StatusCode='" + AppPutawayConfirmStatus + "' ";
db.Update("Imgr1",
str,
" GoodsReceiptNoteNo='" + request.GoodsReceiptNoteNo + "' ");
}
}
}
catch { throw; }
return(Result);
}