public List <Imgi2_Picking> Get_Imgi2_Picking_List(Imgi request) { List <Imgi2_Picking> 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 RowNum = ROW_NUMBER() OVER (ORDER BY Imgi2.StoreNo ASC), " + "Imgi2.*, " + "(Select Top 1 UserDefine1 From Impm1 Where TrxNo=Imgi2.ReceiptMovementTrxNo) AS SerialNo," + "(Select Top 1 " + strBarCodeFiled + " From Impr1 Where TrxNo=Imgi2.ProductTrxNo) AS BarCode," + "(Select Top 1 SerialNoFlag From Impr1 Where TrxNo=Imgi2.ProductTrxNo) AS SerialNoFlag," + "(CASE Imgi2.DimensionFlag When '1' THEN Imgi2.PackingQty When '2' THEN Imgi2.WholeQty ELSE Imgi2.LooseQty END) AS Qty, " + "0 AS QtyBal, 0 AS ScanQty ,IsNull(Imgi1.DeliveryToCode,'') AS DeliveryToCode,(Select Top 1 UserDefine1 From Impm1 Where TrxNo=Imgi2.ReceiptMovementTrxNo) AS UserDefine1 " + "From Imgi2 " + "Left Join Imgi1 On Imgi2.TrxNo=Imgi1.TrxNo " + "Where IsNull(Imgi1.StatusCode,'')='USE' And Imgi1.GoodsIssueNoteNo='" + request.GoodsIssueNoteNo + "'"; Result = db.Select <Imgi2_Picking>(strSql); } } catch { throw; } return(Result); }
public List <Imgi2_Verify> Get_Imgi2_Verify_List(Imgi request) { List <Imgi2_Verify> Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { string strSql = ""; strSql = "Select RowNum = ROW_NUMBER() OVER (ORDER BY Imgi2.StoreNo ASC), " + "Imgi2.*, " + "(Select Top 1 UserDefine1 From Impm1 Where TrxNo=Imgi2.ReceiptMovementTrxNo) AS SerialNo," + "" + getBarCodeListSelect() + "(Select Top 1 SerialNoFlag From Impr1 Where TrxNo=Imgi2.ProductTrxNo) AS SerialNoFlag," + "(CASE Imgi2.DimensionFlag When '1' THEN Imgi2.PackingQty When '2' THEN Imgi2.WholeQty ELSE Imgi2.LooseQty END) AS Qty, " + "0 AS QtyBal, 0 AS ScanQty,ReceiptMovementTrxNo " + "From Imgi2 " + "Left Join Imgi1 On Imgi2.TrxNo=Imgi1.TrxNo " + "Where (IsNull(Imgi1.StatusCode,'')='USE' Or IsNull(Imgi1.StatusCode,'')='CMP') And Imgi1.GoodsIssueNoteNo='" + Modfunction.SQLSafe(request.GoodsIssueNoteNo) + "'"; Result = db.Select <Imgi2_Verify>(strSql); } } catch { throw; } return(Result); }
public List <Imgi2_Picking> Get_Imgi2_Picking_List(Imgi request) { List <Imgi2_Picking> Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection()) { string strSql = "Select RowNum = ROW_NUMBER() OVER (ORDER BY Imgi2.StoreNo ASC), " + "Imgi2.TrxNo, Imgi2.LineItemNo, Imgi2.ProductTrxNo," + "IsNull(Imgi2.StoreNo,'') AS StoreNo, IsNull(Imgi2.ProductCode,'') AS ProductCode," + "IsNull(Imgi2.ProductDescription,'') AS ProductDescription," + "IsNull(Imgi2.PackingNo,'') AS PackingNo," + "(Select Top 1 SerialNo From Impm1 Where TrxNo=Imgi2.ReceiptMovementTrxNo) AS SerialNo," + "(CASE Imgi2.DimensionFlag When '1' THEN Imgi2.PackingQty When '2' THEN Imgi2.WholeQty ELSE Imgi2.LooseQty END) AS Qty, " + "0 AS QtyBal, 0 AS ScanQty " + "From Imgi2 " + "Left Join Imgi1 On Imgi2.TrxNo=Imgi1.TrxNo " + "Where Imgi1.GoodsIssueNoteNo='" + request.GoodsIssueNoteNo + "'"; Result = db.Select <Imgi2_Picking>(strSql); } } catch { throw; } return(Result); }
public int Confirm_Imgi1(Imgi request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { db.Insert( new Imsl1 { DocNo = request.GoodsIssueNoteNo, Description = "VERIFIED", StatusLogDateTime = DateTime.Now, UserId = request.UserID, StatusCode = "EXE", UpdateBy = request.UserID, UpdateDateTime = DateTime.Now } ); Result = 0; } } catch { throw; } return(Result); }
public List <Imgi1> Get_Imgi1_List(Imgi request) { List <Imgi1> Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (!string.IsNullOrEmpty(request.CustomerCode)) { Result = db.SelectParam <Imgi1>( i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.StatusCode != "CMP" && i.CustomerCode == request.CustomerCode ).OrderByDescending(i => i.IssueDateTime).ToList <Imgi1>(); } else if (!string.IsNullOrEmpty(request.GoodsIssueNoteNo)) { Result = db.SelectParam <Imgi1>( i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.StatusCode != "CMP" && i.GoodsIssueNoteNo.StartsWith(request.GoodsIssueNoteNo) ).OrderByDescending(i => i.IssueDateTime).ToList <Imgi1>(); } } } catch { throw; } return(Result); }
public int Update_Imgi1_Status(Imgi request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { //Result = db.Update<Imgi1>( // new // { // StatusCode = request.StatusCode, // CompleteBy = request.UserID, // CompleteDate = DateTime.Now, // UpdateBy= request.UserID, // UpdateDateTime= DateTime.Now // }, // p => p.TrxNo == int.Parse(request.TrxNo) //); string strImgi = "Update Imgi1 set StatusCode ='" + request.StatusCode + "' , " + " CompleteBy='" + request.UserID + "' , " + " CompleteDate = '" + DateTime.Now + "', " + " UpdateBy = '" + request.UserID + "' , " + " UpdateDateTime = '" + DateTime.Now + "'" + " Where TrxNo = " + request.TrxNo + ""; db.ExecuteSql(strImgi); } } catch { throw; } return(Result); }
public List <Imgi1> Get_Imgi1_List(Imgi request) { List <Imgi1> Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { if (!string.IsNullOrEmpty(request.CustomerCode)) { if (!string.IsNullOrEmpty(request.StatusCode)) { Result = db.Select <Imgi1>("Select * from imgi1 where StatusCode = 'EXE' AND Convert(Char(10), IssueDateTime, 20) = Convert(Char(10), getdate(), 20) AND CustomerCode = " + Modfunction.SQLSafeValue(request.CustomerCode)); //Result = db.SelectParam<Imgi1>( // i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.CustomerCode == request.CustomerCode //).OrderByDescending(i => i.IssueDateTime).ToList<Imgi1>(); } else { Result = db.SelectParam <Imgi1>( i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.StatusCode != "CMP" && i.CustomerCode == request.CustomerCode ).OrderByDescending(i => i.IssueDateTime).ToList <Imgi1>(); } } else if (!string.IsNullOrEmpty(request.GoodsIssueNoteNo)) { if (!string.IsNullOrEmpty(request.StatusCode)) { Result = db.Select <Imgi1>("Select * from imgi1 where StatusCode = 'EXE' AND Convert(Char(10), IssueDateTime, 20) = Convert(Char(10), getdate(), 20) AND GoodsIssueNoteNo like '" + Modfunction.SQLSafe(request.GoodsIssueNoteNo) + "%'"); //Result = db.SelectParam<Imgi1>( // i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.GoodsIssueNoteNo.StartsWith(request.GoodsIssueNoteNo) //).OrderByDescending(i => i.IssueDateTime).ToList<Imgi1>(); } else { Result = db.SelectParam <Imgi1>( i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.StatusCode != "CMP" && i.GoodsIssueNoteNo.StartsWith(request.GoodsIssueNoteNo) ).OrderByDescending(i => i.IssueDateTime).ToList <Imgi1>(); } } } } catch { throw; } return(Result); }
public List <Imgi1> Get_Imgi1_List(Imgi request) { List <Imgi1> Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { if (!string.IsNullOrEmpty(request.CustomerCode)) { //if (!string.IsNullOrEmpty(request.StatusCode)) //{ // Result = db.Select<Imgi1>("Select * from imgi1 where StatusCode = 'EXE' AND Convert(Char(10), IssueDateTime, 20) = Convert(Char(10), getdate(), 20) AND CustomerCode = " + Modfunction.SQLSafeValue(request.CustomerCode)); // //Result = db.SelectParam<Imgi1>( // // i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.CustomerCode == request.CustomerCode // //).OrderByDescending(i => i.IssueDateTime).ToList<Imgi1>(); //} //else //{ // Result = db.SelectParam<Imgi1>( // i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.StatusCode != "CMP" && i.CustomerCode == request.CustomerCode // ).OrderByDescending(i => i.IssueDateTime).ToList<Imgi1>(); //} } else if (!string.IsNullOrEmpty(request.CustGINNo)) { //string strImgi = ""; //strImgi = "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(InvoiceNo,'') LIKE '" + request.CustGINNo + "%'"; //Result = db.Select<Imgi1>( // strImgi //); Result = db.SelectParam <Imgi1>( i => i.CustomerCode != null && i.CustomerCode != "" && i.StatusCode != null && i.StatusCode != "DEL" && i.StatusCode != "EXE" && i.StatusCode != "CMP" && i.CustGINNo.StartsWith(request.CustGINNo) ).OrderByDescending(i => i.IssueDateTime).ToList <Imgi1>(); } } } catch { throw; } return(Result); }
public int Update_Imgi2_PackingNo(Imgi request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { Result = db.Update <Imgi2>( new { UserDefine2 = request.PackingNo }, p => p.TrxNo == int.Parse(request.TrxNo) && p.LineItemNo == int.Parse(request.LineItemNo) ); } } catch { throw; } return(Result); }
public int Update_Imgi1_Status(Imgi request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { Result = db.Update <Imgi1>( new { StatusCode = request.StatusCode, CompleteBy = request.UserID, CompleteDate = DateTime.Now }, p => p.TrxNo == int.Parse(request.TrxNo) ); } } catch { throw; } return(Result); }
public int Complete_Imgi1(Imgi request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { /* * Result = db.Update<Imgi1>( * new * { * StatusCode = "CMP", * CompleteBy = request.UserID, * CompleteDate = DateTime.Now * }, * p => p.GoodsIssueNoteNo == request.GoodsIssueNoteNo * ); * if (Result > -1) * { * */ db.Insert( new Imsl1 { DocNo = request.GoodsIssueNoteNo, Description = "PICKED", StatusLogDateTime = DateTime.Now, UserId = request.UserID, StatusCode = "CMP", UpdateBy = request.UserID, UpdateDateTime = DateTime.Now } ); Result = 0; } } catch { throw; } return(Result); }
public int Update_Imgi2_PackingNo(Imgi request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { foreach (Imgi2_Picking i2 in request.Imgi2s) { Result = db.Update <Imgi2>( new { PackingNo = i2.PackingNo }, p => p.TrxNo == i2.TrxNo && p.LineItemNo == i2.LineItemNo ); } } } catch { throw; } return(Result); }
public int Update_verify_imgi1(Imgi request) { Get_Impa1_List(); int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { if (Modfunction.CheckNull(AppIssueVerifyStatus) != "") { Result = db.Update <Imgi1>( new { StatusCode = AppIssueVerifyStatus }, p => p.TrxNo == int.Parse(request.TrxNo) ); } } } catch { throw; } return(Result); }
public int Update_Imgi2_QtyRemark(Imgi request) { Update_Imgi2_PackingNo(request); int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { if (request.QtyFieldName == "PackingQty") { Result = db.Update <Imgi2>( 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 = BalancePackingQty + " + request.QtyRemarkBackQty , " TrxNo = " + Modfunction.SQLSafeValue(request.ReceiptMovementTrxNo) ); } else if (request.QtyFieldName == "WholeQty") { Result = db.Update <Imgi2>( 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 = BalanceWholeQty + " + request.QtyRemarkBackQty , " TrxNo = " + Modfunction.SQLSafeValue(request.ReceiptMovementTrxNo) ); } else { Result = db.Update <Imgi2>( 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 = BalanceLooseQty + " + request.QtyRemarkBackQty , " TrxNo = " + Modfunction.SQLSafeValue(request.ReceiptMovementTrxNo) ); } Result = db.Update <Imgi1>(" 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 int Update_Imgi1_Status(Imgi 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 = 'Imgi1' and PrimaryKeyName ='GoodsIssueNoteNo' and PrimaryKeyValue='" + request.GoodsIssueNoteNo + "'"); if (request.GoodsIssueNoteNo != null && request.GoodsIssueNoteNo != "") { if (list1 != null) { if (list1[0].LineItemNo > 0) { intMaxLineItemNo = list1[0].LineItemNo + 1; } } db.Insert(new Sael1 { TableName = "Imgi1", PrimaryKeyName = "GoodsIssueNoteNo", PrimaryKeyValue = request.GoodsIssueNoteNo, DateTime = DateTime.Now, UpdateDatetime = DateTime.Now, LineItemNo = intMaxLineItemNo, UpdateBy = request.UserID, Description = "PICKED" }); db.Insert(new Imsl1 { DocNo = request.GoodsIssueNoteNo, DocType = "WH", Description = "COMPLETE", StatusLogDateTime = DateTime.Now, UserId = request.UserID, StatusCode = request.StatusCode, UpdateBy = request.UserID, UpdateDateTime = DateTime.Now, }); } if (AppPickConfirmStatus != "") { Result = db.Update <Imgi1>( new { StatusCode = AppPickConfirmStatus, CompleteBy = request.UserID, CompleteDate = DateTime.Now, PickDateTime = DateTime.Now, AppPickConfirmStatus = AppPickConfirmStatus }, p => p.TrxNo == int.Parse(request.TrxNo) ); } } } catch { throw; } return(Result); }
public int Comfirm_Picking_Imgi3(Imgi request) { int Result = -1; try { if (request.TrxNo != null && request.TrxNo.Trim() != "") { string[] LineItemNoList = request.LineItemNoList.Split(','); string [] ProductCodeList = request.ProductCodeList.Split(','); string[] QtyList = request.QtyList.Split(','); string[] PackingNoList = request.PackingNoList.Split(','); string[] ProductDescriptionList = request.ProductDescriptionList.Split(','); string[] ProductTrxNoList = request.ProductTrxNoList.Split(','); using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { string strPackingListDone = ""; string strCurrentPackingNo = ""; int PackingQty = 0, WholeQty = 0, LooseQty = 0; for (int i = 1; i < LineItemNoList.Length; i++) { if (strPackingListDone != "" && strPackingListDone.IndexOf(PackingNoList[i] + ',' + ProductCodeList[i]) > 0) { continue; } strCurrentPackingNo = PackingNoList[i] + ',' + ProductCodeList[i]; int intQty = int.Parse(QtyList[i]); for (int j = i + 1; j < LineItemNoList.Length; j++) { if (strCurrentPackingNo == PackingNoList[j] + ',' + ProductCodeList[j]) { intQty = intQty + int.Parse(QtyList[j]); } } if (request.DimensionFlag != null && request.DimensionFlag != "") { switch (request.DimensionFlag) { case "1": PackingQty = intQty; break; case "2": WholeQty = intQty; break; case "3": LooseQty = intQty; break; } } int intMaxLineItemNo = 1; List <Imgi3> list1 = db.Select <Imgi3>("Select Max(LineItemNo) LineItemNo from Imgi3 Where TrxNo = " + Modfunction.SQLSafeValue(request.TrxNo)); if (list1 != null) { if (list1[0].LineItemNo > 0) { intMaxLineItemNo = list1[0].LineItemNo + 1; } } db.ExecuteSql("insert into imgi3 (TrxNo, LineItemNo, PackingNo,ProductTrxNo, ProductDescription,PackingQty, WholeQty, LooseQty,Volume) values (" + Modfunction.SQLSafe(request.TrxNo) + "," + intMaxLineItemNo + "," + Modfunction.SQLSafeValue(PackingNoList[i].ToString()) + "," + int.Parse(ProductTrxNoList[i].ToString()) + " ," + Modfunction.SQLSafeValue(ProductDescriptionList[i].ToString()) + "," + PackingQty + "," + WholeQty + "," + LooseQty + " ,0 )"); strPackingListDone = strPackingListDone + ',' + strCurrentPackingNo; } // 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); }