public List <Impm1_Enquiry> Get_Impm1_Enquiry_List(Impm request)
{
List <Impm1_Enquiry> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSql = "Select IsNull(Impm1.ProductCode,'') AS ProductCode, IsNull(Impm1.ProductName,'') AS ProductName," +
"IsNull(Impm1.GoodsReceiveorIssueNo,'') AS GoodsReceiveorIssueNo, IsNull(Impm1.RefNo,'') AS RefNo," +
"IsNull(Impm1.StoreNo,'') AS StoreNo, " +
"(CASE Impm1.DimensionFlag When '1' THEN Impm1.BalancePackingQty When '2' THEN Impm1.BalanceWholeQty ELSE Impm1.BalanceLooseQty END) AS BalanceQty " +
"From Impm1 Where ";
string strFilter = " (CASE Impm1.DimensionFlag When '1' THEN Impm1.BalancePackingQty When '2' THEN Impm1.BalanceWholeQty ELSE Impm1.BalanceLooseQty END) >0 ";
if (!string.IsNullOrEmpty(request.ProductCode))
{
strFilter = strFilter + " AND ProductCode='" + Modfunction.SQLSafe(request.ProductCode) + "'";
}
else if (!string.IsNullOrEmpty(request.WarehouseCode) && !string.IsNullOrEmpty(request.StoreNo))
{
strFilter = strFilter + " AND WarehouseCode='" + Modfunction.SQLSafe(request.WarehouseCode) + "' And StoreNo='" + Modfunction.SQLSafe(request.StoreNo) + "'";
}
else if (!string.IsNullOrEmpty(request.ProductTrxNo))
{
strFilter = strFilter + " AND ProductTrxNo = " + int.Parse(request.ProductTrxNo);
}
Result = db.Select <Impm1_Enquiry>(strSql + strFilter);
}
}
catch { throw; }
return(Result);
}
public object Get_Impm1_Transfer_List(Impm request)
{
List <Impm1_Transfer_Tree> ResultTrees = new List <Impm1_Transfer_Tree>();
List <Impm1_Transfer> Results = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
if ((request.WarehouseCode != null && request.WarehouseCode != "") && (request.StoreNo != null && request.StoreNo != ""))
{
string strSql = "Select Impm1.TrxNo, Impm1.BatchLineItemNo, IsNull(ProductCode,'') AS name, IsNull(ProductCode,'') AS ProductCode," +
"IsNull(ProductName,'') AS ProductName, IsNull(GoodsReceiveorIssueNo,'') AS GoodsReceiveorIssueNo, IsNull(UserDefine1,'') AS UserDefine1," +
"b.QtyBal, '' AS FromToStoreNo, 0 AS ScanQty , '' as TreeLineItemNo ,''objectTrxNo " +
"From Impm1 Join (Select (Select top 1 Imit1.StatusCode from imit1 Where imit1.GoodsTransferNoteNo = a.GoodsReceiveorIssueNo) AS ImitStatus, a.TrxNo, " +
"(CASE a.DimensionFlag When '1' THEN a.BalancePackingQty When '2' THEN a.BalanceWholeQty ELSE a.BalanceLooseQty END) AS QtyBal From Impm1 a ) b on b.TrxNo = impm1.TrxNo " +
"Where WarehouseCode='" + request.WarehouseCode + "' And ( impm1.TrxType =1 or impm1.TrxType =3) And StoreNo='" + request.StoreNo + "' And (b.ImitStatus = 'EXE' or ImitStatus is null) And b.QtyBal>0 ";
if (request.CustomerCode != null && request.CustomerCode != "")
{
strSql = strSql + " AND CustomerCode = " + Modfunction.SQLSafeValue(request.CustomerCode);
}
strSql = strSql + " order by impm1.ProductCode ";
Results = db.Select <Impm1_Transfer>(strSql);
for (int i = 0; i < Results.Count; i++)
{
string BatchNo = Results[i].name;
Impm1_Transfer impm1 = Results[i];
bool blnExistBatchNo = false;
foreach (Impm1_Transfer_Tree ResultTree in ResultTrees)
{
if (ResultTree.name.Equals(BatchNo))
{
blnExistBatchNo = true;
impm1.TreeLineItemNo = ResultTree.tree.Count;
impm1.objectTrxNo = ResultTree.tree[0].objectTrxNo;
ResultTree.tree.Add(impm1);
}
}
if (!blnExistBatchNo)
{
Impm1_Transfer_Tree impm1_tree = new Impm1_Transfer_Tree();
impm1_tree.name = BatchNo;
impm1_tree.tree = new List <Impm1_Transfer>();
impm1.TreeLineItemNo = 0;
impm1.objectTrxNo = ResultTrees.Count;
impm1_tree.tree.Add(impm1);
ResultTrees.Add(impm1_tree);
}
}
}
}
}
catch { throw; }
return(ResultTrees);
}
public List <Impm1_UserDefine> Get_Impm1_List(Impm request)
{
List <Impm1_UserDefine> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
string strSql = "Select Top 10 Impm1.TrxNo, Impm1.UserDefine1 " +
"From Impm1 " +
"Where Impm1.UserDefine1 LIKE '" + request.UserDefine1 + "%' " +
"Order By Impm1.TrxNo ASC";
Result = db.Select <Impm1_UserDefine>(strSql);
}
}
catch { throw; }
return(Result);
}
public List <Impm1_UserDefine> Get_Impm1_List(Impm request)
{
List <Impm1_UserDefine> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("WMS"))
{
List <Impa1> impa1 = db.Select <Impa1>("Select * from Impa1");
string strBarCodeFiled = impa1[0].BarCodeField;
string[] strBarCodeList = strBarCodeFiled.Split(',');
string strSql = "";
string strSqlDetail = "";
if (strBarCodeList.Length <= 1)
{
strSqlDetail = "Select Cast(" + strBarCodeFiled + " AS nvarchar(2000)) AS UserDefine1,TrxNo from Impr1 Where " + strBarCodeFiled + " = '" + Modfunction.SQLSafe(request.UserDefine1) + "'";
}
else
{
for (int i = 0; i < strBarCodeList.Length; i++)
{
string strSqlNew = "Select Cast(" + strBarCodeList[i] + " AS nvarchar(2000)) AS UserDefine1,TrxNo from Impr1 Where " + strBarCodeList[i] + " = '" + Modfunction.SQLSafe(request.UserDefine1) + "'";
if (strSqlDetail == "")
{
strSqlDetail = strSqlNew;
}
else
{
strSqlDetail = strSqlDetail + " union all " + strSqlNew;
}
}
}
if (strSqlDetail != "")
{
strSql = "Select Top 10 * From (" + strSqlDetail + ") a " +
"Order By a.UserDefine1 ASC";
Result = db.Select <Impm1_UserDefine>(strSql);
}
}
}
catch { throw; }
return(Result);
}
public List <Impm1_Putaway> Get_Impm1_Putaway_List(Impm request)
{
List <Impm1_Putaway> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
string strSql = "Select Top 1 " +
"Impm1.TrxNo, Impm1.TrxType, IsNull(Impm1.BatchNo,'') AS BatchNo, Impm1.BatchLineItemNo, Impm1.ProductTrxNo," +
"IsNull(Impm1.ProductCode,'') AS ProductCode, IsNull(Impm1.ProductName,'') AS ProductDescription," +
"IsNull(Impm1.SerialNo,'') AS SerialNo, IsNull(Impm1.StoreNo,'') AS StoreNo," +
"IsNull((Select StagingAreaFlag From Whwh2 Where WarehouseCode=Impm1.WarehouseCode And StoreNo=Impm1.StoreNo),'') AS StagingAreaFlag," +
"0 AS ScanQty " +
"From Impm1 " +
"Where (Impm1.TrxType='1' Or Impm1.TrxType='3') And Impm1.SerialNo='" + request.SerialNo + "'";
Result = db.Select <Impm1_Putaway>(strSql);
}
}
catch { throw; }
return(Result);
}
public int Update_Impm1_StoreNo(Impm request)
{
int Result = -1;
int ResultTwo = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
string strSql = "Select Impm1.TrxNo, IsNUll(Impm1.BatchNo,'') AS BatchNo, IsNUll(Impm1.TrxType,'') AS TrxType, " +
"(Select Top 1 TrxNo From Imgr1 Where GoodsReceiptNoteNo=Impm1.BatchNo) AS ImgrTrxNo, Impm1.BatchLineItemNo, " +
"(Select Top 1 TrxNo From Imgi2 WHere Imgi2.ReceiptmovementTrxNo = Impm1.TrxNo) AS ImgiTrxNo," +
"(Select Top 1 LineItemNo From Imgi2 Where Imgi2.ReceiptmovementTrxNo = Impm1.TrxNo) AS ImgiLineItemNo," +
"(Select Top 1 GoodsIssueNoteNo From Imgi1 Left Join Imgi2 on Imgi1.TrxNo=Imgi2.TrxNo Where Imgi2.ReceiptmovementTrxNo = Impm1.TrxNo) AS ImgiGoodsIssueNoteNo " +
"From Impm1 " +
"Where Impm1.TrxNo=" + int.Parse(request.TrxNo);
List <Putaway_Update_ORM> impm1 = db.Select <Putaway_Update_ORM>(strSql);
if (impm1.Count > 0)
{
Result = db.Update <Impm1>(
new
{
StoreNo = request.StoreNo
},
p => p.TrxNo == int.Parse(request.TrxNo)
);
if (impm1[0].ImgrTrxNo > 0 && impm1[0].BatchLineItemNo > 0)
{
Result = db.Update <Imgr2>(
new
{
StoreNo = request.StoreNo
},
p => p.TrxNo == impm1[0].ImgrTrxNo && p.LineItemNo == impm1[0].BatchLineItemNo
);
}
if (impm1[0].ImgiTrxNo > 0 && impm1[0].ImgiLineItemNo > 0)
{
ResultTwo = db.Update <Imgi2>(
new
{
StoreNo = request.StoreNo
},
p => p.TrxNo == impm1[0].ImgiTrxNo && p.LineItemNo == impm1[0].ImgiLineItemNo
);
if (ResultTwo > -1 && !string.IsNullOrEmpty(impm1[0].ImgiGoodsIssueNoteNo))
{
Result = db.Update <Impm1>(
new
{
StoreNo = request.StoreNo
},
p => p.GoodsReceiveorIssueNo == impm1[0].ImgiGoodsIssueNoteNo && p.BatchLineItemNo == impm1[0].BatchLineItemNo && p.TrxType == "2"
);
}
}
}
}
}
catch { throw; }
return(Result);
}