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); }
private string generateOnhandNo() { ONHAND_D pair; var prefixRules = ""; var runningNo = ""; using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { try { List <sanm1> sanm1 = db.Select <sanm1>("SELECT Prefix, NextNo FROM sanm1 WHERE NumberType = 'OMOH'"); prefixRules = Modfunction.CheckNull(sanm1[0].Prefix); runningNo = Modfunction.CheckNull(sanm1[0].NextNo); pair = generateTransactionNo(prefixRules, runningNo); string strSql = ""; strSql = "NextNo=" + Modfunction.SQLSafeValue(pair.NextNo) + ""; db.Update("sanm1", strSql, "numbertype='OMOH' "); } catch (Exception ex) { throw ex; } } return(pair.TrxNo); }
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 int Update_Saal(Saal request) { int Result = -1; int intResult = -1; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { if (request.PrimaryKeyLineItemNo > 0) { intResult = db.Scalar <int>( "Select Max(lineItemNo) from saal1 where Tablename = {0} AND PrimaryKeyName = {1} AND PrimaryKeyValue = {2} and PrimaryKeyLineItemNo = {3}", request.TableName, request.PrimaryKeyName, request.PrimaryKeyValue, request.PrimaryKeyLineItemNo ); if (request.TableName == "Imgr2" || request.TableName == "Imgi2") { db.ExecuteSql("Update " + request.TableName + " Set " + request.FieldName + " = " + Modfunction.SQLSafeValue(request.NewValue) + " Where TrxNo = " + Modfunction.SQLSafeValue(request.PrimaryKeyValue) + " AND LineItemNo = " + Modfunction.SQLSafeValue(request.PrimaryKeyLineItemNo.ToString())); } } else { intResult = db.Scalar <int>( "Select Max(lineItemNo) from saal1 where Tablename = {0} AND PrimaryKeyName = {1} AND PrimaryKeyValue = {2} ", request.TableName, request.PrimaryKeyName, request.PrimaryKeyValue ); } if (intResult > 0) { intResult = intResult + 1; } else { intResult = 1; } if (request.PrimaryKeyLineItemNo > 0) { db.ExecuteSql("insert into saal1 (Tablename, PrimaryKeyName, PrimaryKeyValue,PrimaryKeyLineItemNo ,LineItemNo, FieldName, NewValue, OldValue) values ('" + Modfunction.SQLSafe(request.TableName) + "','" + Modfunction.SQLSafe(request.PrimaryKeyName) + "'," + Modfunction.SQLSafeValue(request.PrimaryKeyValue) + "," + Modfunction.SQLSafeValue(request.PrimaryKeyLineItemNo.ToString()) + "," + intResult.ToString() + ",'" + Modfunction.SQLSafe(request.FieldName) + "'," + Modfunction.SQLSafeValue(request.NewValue) + "," + Modfunction.SQLSafeValue(request.OldValue) + ")"); } else { db.ExecuteSql("insert into saal1 (Tablename, PrimaryKeyName, PrimaryKeyValue,PrimaryKeyLineItemNo ,LineItemNo, FieldName, NewValue, OldValue) values ('" + Modfunction.SQLSafe(request.TableName) + "','" + Modfunction.SQLSafe(request.PrimaryKeyName) + "'," + Modfunction.SQLSafeValue(request.PrimaryKeyValue) + "," + Modfunction.SQLSafeValue(request.PrimaryKeyLineItemNo.ToString()) + "," + intResult.ToString() + ",'" + Modfunction.SQLSafe(request.FieldName) + "'," + Modfunction.SQLSafeValue(request.NewValue) + "," + Modfunction.SQLSafeValue(request.OldValue) + ")"); } } } catch { throw; } return(Result); }
public int Comfirm_Smar1(Smar request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (request.UpdateAllString != null && request.UpdateAllString != "") { JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString); if (ja != null) { for (int i = 0; i < ja.Count(); i++) { string strSql = ""; string PhoneNumber = ja[i]["PhoneNumber"].ToString(); string PassWord = ja[i]["PassWord"].ToString(); string YogaStudioName = ja[i]["YogaStudioName"].ToString(); string Sex = ja[i]["Sex"].ToString(); string Remark = ja[i]["Remark"].ToString(); string AreaCode = ja[i]["AreaCode"].ToString(); strSql = "insert into Samr1( " + " YogaStudioName," + " AreaCode," + " Sex," + " Remark ," + " PhoneNumber ," + " PassWord ," + " CreateDateTime," + " UpdateDateTime" + " )" + "values( " + Modfunction.SQLSafeValue(YogaStudioName) + "," + Modfunction.SQLSafeValue(AreaCode) + "," + Modfunction.SQLSafeValue(Sex) + "," + Modfunction.SQLSafeValue(Remark) + "," + Modfunction.SQLSafeValue(PhoneNumber) + "," + Modfunction.SQLSafeValue(PassWord) + "," + "GETDATE()," + "GETDATE()" + ") "; db.ExecuteSql(strSql); } Result = 1; } } } } catch { throw; } return(Result); }
private void grd_sasr1_SelectionChanged(object sender, EventArgs e) { if (grd_sasr1.RowCount > 0) { if (grd_sasr1.CurrentRow != null) { { txt_RequestDes.Text = Modfunction.CheckNull(grd_sasr1.Rows[grd_sasr1.CurrentRow.Index].Cells["RequestDescription"].Value).ToString().Replace("\n", "\r\n"); txt_TestSummary.Text = grd_sasr1.Rows[grd_sasr1.CurrentRow.Index].Cells["TestSummary"].Value.ToString().Replace("\n", "\r\n"); this.txt_RequestDescription.Text = grd_sasr1.Rows[grd_sasr1.CurrentRow.Index].Cells["RequestDescription"].Value.ToString().Replace("\n", "\r\n"); txt_ProgramingSummary.Text = Modfunction.CheckNull(grd_sasr1.Rows[grd_sasr1.CurrentRow.Index].Cells["ProgrammingSummary"].Value).ToString().Replace("\n", "\r\n"); } } } }
public int ConfirmAll_Jmjm1(Jmjm request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (request.confirmAllString != null && request.confirmAllString != "") { JArray ja = (JArray)JsonConvert.DeserializeObject(request.confirmAllString); if (ja != null) { for (int i = 0; i < ja.Count(); i++) { string strJobNo = ""; string strActualArrivalDate = ""; string strDeliveryDate = ""; if (ja[i]["JobNo"] != null || ja[i]["JobNo"].ToString() != "") { strJobNo = ja[i]["JobNo"].ToString(); } strActualArrivalDate = ja[i]["ActualArrivalDate"].ToString(); strDeliveryDate = ja[i]["DeliveryDate"].ToString(); if (strJobNo != "") { if (strActualArrivalDate != "") { db.Update("Jmjm3", " DateTime = '" + Modfunction.SQLSafe(strActualArrivalDate) + "'", " JobNo='" + strJobNo + "' and Description = 'ACTUAL ARRIVAL DATE'"); } if (strDeliveryDate != "") { db.Update("Jmjm1", " DeliveryDateTime = '" + Modfunction.SQLSafe(strDeliveryDate) + "'", " JobNo='" + strJobNo + "'"); } } } Result = 1; } } } } catch { throw; } return(Result); }
public void SaveInsertGroupNameEvent(string strInsertNextGroupCode, int intLineItem, string strJobNo, string strUserId) { List <Jmjm3> ResultJmjm3 = null; List <Jmeg2> ResultJmeg2 = null; try { using (var db = DbConnectionFactory.OpenDbConnection("TMS")) { int intMaxLineItemNo = 0; string strSelectJmjm3 = "Select Max(LineItemNo) as LineItemNo from Jmjm3 Where JobNo = '" + strJobNo + "'"; ResultJmjm3 = db.Select <Jmjm3>(strSelectJmjm3); if (ResultJmjm3.Count > 0) { intMaxLineItemNo = ResultJmjm3[0].LineItemNo + 1; } string strSelectJmeg2 = "Select EventCode from Jmeg2 Where GroupCode = '" + strInsertNextGroupCode + "'"; ResultJmeg2 = db.Select <Jmeg2>(strSelectJmeg2); if (ResultJmeg2.Count > 0) { if (intLineItem > 0) { string StrUpdateJmjm7 = "Update Jmjm7 Set JobLineItemNo = JobLineItemNo + " + ResultJmeg2.Count + " Where JobNo = '" + strJobNo + "' AND JobLineItemNo > " + intLineItem; db.ExecuteSql(StrUpdateJmjm7); string StrUpdateJmjm4 = "Update Jmjm4 Set JobLineItemNo = JobLineItemNo + " + ResultJmeg2.Count + " Where JobNo = '" + strJobNo + "' AND JobLineItemNo > " + intLineItem; db.ExecuteSql(StrUpdateJmjm4); string StrUpdateJmjm3 = "update Jmjm3 Set LineItemNo = LineItemNo + " + ResultJmeg2.Count + " Where JobNo = '" + strJobNo + "' AND LineItemNo > " + intLineItem; db.ExecuteSql(StrUpdateJmjm3); intMaxLineItemNo = intLineItem + 1; } for (int i = 0; i < ResultJmeg2.Count; i++) { string StrInsertJmjm3 = "INSERT INTO Jmjm3 (JobNo, LineItemNo,EventCode, Description, ShowETrackFlag,Remark,UpdateBy, UpdateDateTime) " + "Select '" + strJobNo + "'," + intMaxLineItemNo + ",EventCode,Description,EtrackFlag,Remark,'" + strUserId + "', getdate() from Jmje1 Where EventCode= '" + ResultJmeg2[i].EventCode + "'"; db.ExecuteSql(StrInsertJmjm3); UpdateEventListToJmjm4(Modfunction.CheckNull(ResultJmeg2[i].EventCode), intMaxLineItemNo, strJobNo); intMaxLineItemNo = intMaxLineItemNo + 1; } } } } catch { throw; } }
public Impr1 Get_Impr1_Single(Impr request) { Impr1 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(','); if (strBarCodeList.Length <= 1) { string strSql = "Select * From Impr1 Where IsNull(ProductCode,'')<>'' And IsNull(StatusCode,'')<>'DEL' And " + strBarCodeFiled + "='" + Modfunction.SQLSafe(request.BarCode) + "'"; Result = db.QuerySingle <Impr1>(strSql); } else { string strBarCodeFilter = ""; for (int i = 0; i < strBarCodeList.Length; i++) { if (strBarCodeFilter == "") { strBarCodeFilter = strBarCodeList[i] + "='" + Modfunction.SQLSafe(request.BarCode) + "'"; } else { strBarCodeFilter = strBarCodeFilter + " OR " + strBarCodeList[i] + "='" + Modfunction.SQLSafe(request.BarCode) + "'"; } } if (strBarCodeFilter != "") { string strSql = "Select * From Impr1 Where IsNull(ProductCode,'')<>'' And IsNull(StatusCode,'')<>'DEL' And (" + strBarCodeFilter + ")"; Result = db.QuerySingle <Impr1>(strSql); } else { string strSql = "Select top 0 * From Impr1 "; Result = db.QuerySingle <Impr1>(strSql); } } } } catch { throw; } return(Result); }
public List <Impa1> Get_Impa1_List() { try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { string strSQL = "Select isNull(ShowCycleCountAppFlag,'N') as ShowCycleCountAppFlag,BarCodeField ,isnull(AppTallyConfirmStatus,'') as AppTallyConfirmStatus, isnull(AppPutawayConfirmStatus,'') as AppPutawayConfirmStatus ,isnull(AppPickConfirmStatus,'') as AppPickConfirmStatus ,isnull(AppIssueVerifyStatus,'') as AppIssueVerifyStatus from Impa1"; ResultImpa1 = db.Select <Impa1>(strSQL); AppTallyConfirmStatus = Modfunction.CheckNull(ResultImpa1[0].AppTallyConfirmStatus); AppPutawayConfirmStatus = Modfunction.CheckNull(ResultImpa1[0].AppPutawayConfirmStatus); AppPickConfirmStatus = Modfunction.CheckNull(ResultImpa1[0].AppPickConfirmStatus); AppIssueVerifyStatus = Modfunction.CheckNull(ResultImpa1[0].AppIssueVerifyStatus); } } catch { throw; } return(ResultImpa1); }
public int Confirm_Imit1(Imit request) { int Result = -1; Boolean blnSameCustomer = false; string OldCustomerCode = "", CustomerCode = "", CustomerName = ""; if (request.NewStoreNoList != null || request.NewStoreNoList != "") { string[] NewSotreNoDetail = request.NewStoreNoList.Split(','); string[] LineItemNoDetail = request.LineItemNoList.Split(','); string[] Impm1TrxNoDetail = request.Impm1TrxNoList.Split(','); string[] QtyDetail = request.QtyList.Split(','); for (int intI = 0; intI < NewSotreNoDetail.Length; intI++) { Result = Insert_Imit2Detail(int.Parse(Impm1TrxNoDetail[intI]), int.Parse(request.TrxNo), int.Parse(LineItemNoDetail[intI]), int.Parse(QtyDetail[intI]), NewSotreNoDetail[intI], request.UpdateBy, ref CustomerCode, ref CustomerName); if (OldCustomerCode == "" && CustomerCode != "") { OldCustomerCode = CustomerCode; blnSameCustomer = true; } else if (OldCustomerCode != CustomerCode) { blnSameCustomer = false; } } if (blnSameCustomer) { } } try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { //string strSql = "EXEC spi_Imit_Confirm " + int.Parse(request.TrxNo) + ",'" + request.UpdateBy + "'"; 'yicong 20161019 string strCustoemrUpdate = ""; if (blnSameCustomer) { strCustoemrUpdate = ",CustomerCode = " + Modfunction.SQLSafeValue(CustomerCode); } string strSql = "Update Imit1 set StatusCode='EXE',UpdateBy='" + request.UpdateBy + "'" + strCustoemrUpdate + " where TrxNo='" + request.TrxNo + "' "; Result = db.SqlScalar <int>(strSql); } } catch { throw; } return(Result); }
public int LoginCheck(WmsLogin request) { int Result = -1; try { string com = "SELECT count(*) FROM saus1 WHERE UserId =" + Modfunction.SQLSafeValue(request.UserName) + " And Password="******""; using (var db = DbConnectionFactory.OpenDbConnection()) { Result = db.Scalar <int>(com); } } catch { Result = -1; } return(Result); }
public int confirm_Tobk1(Tobk request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { string str; str = " Note = " + Modfunction.SQLSafeValue(request.Remark) + ",OnBehalfName = " + Modfunction.SQLSafeValue(request.OnBehalfName) + ",StatusCode = 'POD'"; db.Update(request.TableName, str, " BookingNo='" + request.Key + "'"); } } 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 string GoodsTransfer(string strAsn, string strConfirmBy) { string Result = ""; string strBarCode = GetBarCode("UserDefine01"); DataTable dtWhScan = GetSQLCommandReturnDT("Select *,Impr1.TrxNo AS PrductTrxNo,Impr1.CustomerCode AS ProductCustomerCode from WhScan join Impr1 On Impr1." + strBarCode + " = WhScan.BarCode Where AsnNo = " + Modfunction.SQLSafeValue(strAsn) + " AND ComfirmBy = " + Modfunction.SQLSafeValue(strConfirmBy)); Boolean blnSameCustomer = true; if (dtWhScan != null && dtWhScan.Rows.Count > 0) { string strCustomer = Modfunction.CheckNull(dtWhScan.Rows[0]["ProductCustomerCode"]); for (int i = 1; i < dtWhScan.Rows.Count; i++) { if (strCustomer != Modfunction.CheckNull(dtWhScan.Rows[0]["ProductCustomerCode"])) { blnSameCustomer = false; break; } } Result = SaveImit(dtWhScan, blnSameCustomer); } return(Result); }
public string Insert_Aemt1(List <Pid_AEMT1> objAemt1) { try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { string KeyMAwbNo = setKeyMAwbNo(Modfunction.CheckNull(objAemt1[0].MAwbNo)); for (int i = 0; i < objAemt1.Count; i++) { string strSql = ""; string TallyById = ""; string MAwbNo = Modfunction.CheckNull(objAemt1[i].MAwbNo); string PID_NO = Modfunction.CheckNull(objAemt1[i].PID_NO); string LOC_CODE = Modfunction.CheckNull(objAemt1[i].LOC_CODE); strSql = "insert into Aemt1( " + " KeyMAwbNo ," + " MAwbNo," + " PID_NO, " + " LOC_CODE, " + " TallyById," + " TallyByDateTime" + " )" + "values( " + Modfunction.SQLSafeValue(KeyMAwbNo) + ", " + Modfunction.SQLSafeValue(MAwbNo) + " , " + Modfunction.SQLSafeValue(PID_NO) + " , " + Modfunction.SQLSafeValue(LOC_CODE) + " , " + Modfunction.SQLSafeValue(TallyById) + "," + " GetDate()" + ") "; db.ExecuteSql(strSql); } ; } } catch { throw; } return(""); }
public int InsertResult(WhScan request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { string strSQLCommand = ""; if (request.ClearData == "Y") { strSQLCommand = "Delete WhScan Where ComfirmBy = " + Modfunction.SQLSafeValue(request.UserID) + " AND ConfrimType = " + Modfunction.SQLSafeValue(request.ConfrimType); Result = db.Scalar <int>(strSQLCommand); } strSQLCommand = "insert into whscan(AsnNo,BarCode,ConfirmBy,CustomerCode,GoodsIssueNoteNo,ConfrimType,OldStoreNo,ProductCode,Qty,StoreNo,SerialNo,WarehouseCode,OldWarehouseCode,IssueNoteLineItemNo) Values(" + Modfunction.SQLSafeValue(request.AsnNo) + "," + Modfunction.SQLSafeValue(request.BarCode) + "," + Modfunction.SQLSafeValue(request.UserID) + "," + Modfunction.SQLSafeValue(request.CustomerCode) + "," + Modfunction.SQLSafeValue(request.GoodsIssueNoteNo) + "," + Modfunction.SQLSafeValue(request.ConfrimType) + "," + Modfunction.SQLSafeValue(request.OldStoreNo) + "," + Modfunction.SQLSafeValue(request.ProductCode) + "," + Modfunction.SQLSafeValue(request.Qty) + "," + Modfunction.SQLSafeValue(request.StoreNo) + "," + Modfunction.SQLSafeValue(request.SerialNo) + "," + Modfunction.SQLSafeValue(request.WarehouseCode) + "," + Modfunction.SQLSafeValue(request.OldWarehouseCode) + "," + Modfunction.SQLSafeValue(request.IssueNoteLineItemNo) + ")"; Result = db.Scalar <int>(strSQLCommand); } } catch { Result = -1; } 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 confirm_Aemp1WithAido1(Aemp_Aido request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (request.TableName == "Aemp1") { db.Update(request.TableName, " Remark = '" + request.Remark + "',StatusCode = 'POD',ActualPickUpDateTime=getdate(),SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " TrxNo='" + request.Key + "'"); } else if (request.TableName == "Sido1") { db.Update(request.TableName, " Remark = '" + request.Remark + "',StatusCode = 'POD',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " TrxNo='" + request.Key + "'"); } else { db.Update(request.TableName, " Remark = '" + request.Remark + "',StatusCode = 'POD',ActualDeliveryDateTime=getdate(),SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " DeliveryOrderNo='" + request.Key + "'"); } } } catch { throw; } return(Result); }
public List <Todr1> GetTodr1(Tms_Login request) //20160511 { List <Todr1> Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection("TMS")) { var strSQL = ""; if (BlnContactNo == false) { strSQL = "select isnull(DriverCode,'') as DriverCode,isnull(DriverName,'') as DriverName from todr1 where ContactNo2=" + Modfunction.SQLSafeValue(request.ContactNo); Result = db.Select <Todr1>(strSQL); } else { strSQL = "select isnull(DriverCode,'') as DriverCode,isnull(DriverName,'') as DriverName from todr1 where ContactNo1=" + Modfunction.SQLSafeValue(request.ContactNo); Result = db.Select <Todr1>(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 int LoginCheck(Wms_Login request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { if (request.UpdateAllString != null && request.UpdateAllString != "") { JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString); if (ja != null) { for (int i = 0; i < ja.Count(); i++) { string strSqlSaus1 = ""; string strUserId = ja[i]["UserId"].ToString(); string strPassword = ja[i]["Password"].ToString(); //strSqlSaus1 = "Select count(*) From Saus1 Where UserId='" + Modfunction.CheckNull(strUserId) + "' And Password='******'"; strSqlSaus1 = "Select count(*) From Saus1 Where UserId='" + Modfunction.CheckNull(strUserId) + "'"; Result = db.Scalar <int>(strSqlSaus1); } } } } } catch { throw; } return(Result); }
public string GetUserInfo(Event_Login request) { string Result = ""; try { using (var db = DbConnectionFactory.OpenDbConnection("TMS")) { Result = db.QuerySingle <string>("Select Top 1 ISNULL(DriverName,'') From Jmjm4 Where PhoneNumber=" + Modfunction.SQLSafeValue(request.PhoneNumber)); } } 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 Impr1 GetList(List_Impr1 request) { Impr1 Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection("WMS")) { Result = db.QuerySingle <Impr1>( "Select * From Impr1 Where IsNull(ProductCode,'')<>'' And IsNull(StatusCode,'')<>'DEL' And UserDefine01=" + Modfunction.SQLSafeValue(request.BarCode) ); } } catch { throw; } return(Result); }
public int UpdateAll_Aemp1WithAido1(Aemp_Aido request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (request.UpdateAllString != null && request.UpdateAllString != "") { JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString); if (ja != null) { for (int i = 0; i < ja.Count(); i++) { if (ja[i]["TableName"] == null || ja[i]["TableName"].ToString() == "") { continue; } string strKey = ja[i]["Key"].ToString(); string strTableName = ja[i]["TableName"].ToString(); string strRemark = ""; string strStatusCode = ""; if (ja[i]["Remark"] != null || ja[i]["Remark"].ToString() != "") { strRemark = ja[i]["Remark"].ToString(); } if (ja[i]["StatusCode"] != null || ja[i]["StatusCode"].ToString() != "") { strStatusCode = ja[i]["StatusCode"].ToString(); } if (strStatusCode.ToLower() == "cancel") { string strJobNo = ""; if (ja[i]["JobNo"] != null || ja[i]["JobNo"].ToString() != "") { strJobNo = ja[i]["JobNo"].ToString(); } if (strJobNo != "") { int intMaxLineItemNo = 1; List <Jmjm3> list1 = db.Select <Jmjm3>("Select Max(LineItemNo) LineItemNo from Jmjm3 Where JobNo = " + Modfunction.SQLSafeValue(strJobNo)); if (list1 != null) { if (list1[0].LineItemNo > 0) { intMaxLineItemNo = list1[0].LineItemNo + 1; } } db.Insert(new Jmjm3 { JobNo = strJobNo, DateTime = DateTime.Now, UpdateDatetime = DateTime.Now, LineItemNo = intMaxLineItemNo, AutoFlag = "N", StatusCode = "CANCEL", UpdateBy = ja[0]["DriverCode"] == null ? "" : Modfunction.SQLSafe(ja[0]["DriverCode"].ToString()), Remark = Modfunction.SQLSafe(strRemark), Description = ja[0]["CancelDescription"] == null ? "" : Modfunction.SQLSafe(ja[0]["CancelDescription"].ToString()) }); if (strTableName == "Aemp1") { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "'", " TrxNo='" + strKey + "'"); } else if (strTableName == "Sido1") { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "'", " TrxNo='" + strKey + "'"); } else { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "'", " DeliveryOrderNo='" + strKey + "'"); } } } else { if (strTableName == "Aemp1") { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "',StatusCode = '" + strStatusCode + "',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " TrxNo='" + strKey + "'"); } else if (strTableName == "Sido1") { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "',StatusCode = '" + strStatusCode + "',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " TrxNo='" + strKey + "'"); } else { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "',StatusCode = '" + strStatusCode + "',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " DeliveryOrderNo='" + strKey + "'"); } } } Result = 1; } } } } catch { throw; } return(Result); }
public int ConfirmAll_Imcc2(imcc request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (request.UpdateAllString != null && request.UpdateAllString != "") { JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString); if (ja != null) { for (int i = 0; i < ja.Count(); i++) { int TrxNo; int LineItemNo; int ProductTrxNo = 0; int PackingQty; int WholeQty; int LooseQty; if (ja[i]["TrxNo"] != null || ja[i]["TrxNo"].ToString() != "") { if (ja[i]["LineItemNo"] != null || ja[i]["LineItemNo"].ToString() != "") { TrxNo = int.Parse(ja[i]["TrxNo"].ToString()); LineItemNo = int.Parse(ja[i]["LineItemNo"].ToString()); if (ja[i]["ProductTrxNo"] != null || ja[i]["ProductTrxNo"].ToString() != "") { ProductTrxNo = int.Parse(ja[i]["ProductTrxNo"].ToString()); } if (ja[i]["PackingQtyTempValue"].ToString() == "") { PackingQty = 0; } else { PackingQty = int.Parse(ja[i]["PackingQtyTempValue"].ToString()); } if (ja[i]["WholeQtyTempValue"].ToString() == "") { WholeQty = 0; } else { WholeQty = int.Parse(ja[i]["WholeQtyTempValue"].ToString()); } if (ja[i]["LooseQtyTempValue"].ToString() == "") { LooseQty = 0; } else { LooseQty = int.Parse(ja[i]["LooseQtyTempValue"].ToString()); } db.ExecuteSql("insert into impm1 (CustomerCode, WarehouseCode, StoreNo,ProductCode ,Description, DimensionFlag, PackingQty, WholeQty,LooseQty,ProductTrxNo,UpdateBy) values (" + Modfunction.SQLSafeValue(ja[i]["CustomerCode"].ToString()) + "," + Modfunction.SQLSafeValue(ja[i]["WarehouseCode"].ToString()) + "," + Modfunction.SQLSafeValue(ja[i]["StoreNo"].ToString()) + "," + Modfunction.SQLSafeValue(ja[i]["ProductCode"].ToString()) + "," + Modfunction.SQLSafeValue(ja[i]["Description"].ToString()) + "," + Modfunction.SQLSafeValue(ja[i]["DimensionFlag"].ToString()) + "," + PackingQty + "," + WholeQty + "," + LooseQty + "," + ProductTrxNo + "," + Modfunction.SQLSafeValue(ja[i]["UserId"].ToString()) + ")"); string str; string strTableName = "imcc2"; str = " PackingQty = " + PackingQty + ",WholeQty = " + WholeQty + ",LooseQty = " + LooseQty + ", UpdateBy=" + Modfunction.SQLSafeValue(ja[i]["UserId"].ToString()) + ""; db.Update(strTableName, str, " TrxNo=" + TrxNo + " and LineItemNo=" + LineItemNo + ""); } } } Result = 1; } } } } 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 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 int UpdateAll_tjms2(Tobk request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (request.UpdateAllString != null && request.UpdateAllString != "") { JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString); if (ja != null) { for (int i = 0; i < ja.Count(); i++) { int ChargeBerthQty; int ChargeLiftingQty; if (ja[i]["TrxNo"] == null || ja[i]["TrxNo"].ToString() == "") { continue; } string strTrxNo = ja[i]["TrxNo"].ToString(); string strLineItemNo = ja[i]["LineItemNo"].ToString(); string SignedByName = ja[i]["SignedByName"].ToString(); string SignedByNric = ja[i]["SignedByNric"].ToString(); string SignedByDesignation = ja[i]["SignedByDesignation"].ToString(); string CompanyName = ja[i]["CompanyName"].ToString(); string strDateCompleted = ja[i]["DateCompleted"].ToString(); string OfficeInChargeName = ja[i]["OfficeInChargeName"].ToString(); if (ja[i]["ChargeBerthQty"].ToString() == "") { ChargeBerthQty = 0; } else { ChargeBerthQty = int.Parse(ja[i]["ChargeBerthQty"].ToString()); } if (ja[i]["ChargeLiftingQty"].ToString() == "") { ChargeLiftingQty = 0; } else { ChargeLiftingQty = int.Parse(ja[i]["ChargeLiftingQty"].ToString()); } string ChargeOther = ja[i]["ChargeOther"].ToString(); DateTime dt = DateTime.Now; if (strDateCompleted != "" && strDateCompleted != null) { strDateCompleted = strDateCompleted + " " + dt.GetDateTimeFormats('t')[0].ToString(); } string str; if (strLineItemNo != "0") { str = " SignedByName = " + Modfunction.SQLSafeValue(SignedByName) + ",SignedByNric= " + Modfunction.SQLSafeValue(SignedByNric) + ",SignedByDesignation= " + Modfunction.SQLSafeValue(SignedByDesignation) + ",DateCompleted=" + Modfunction.SQLSafeValue(strDateCompleted) + ",ChargeBerthQty=" + ChargeBerthQty + ",ChargeLiftingQty=" + ChargeLiftingQty + ",ChargeOther=" + Modfunction.SQLSafeValue(ChargeOther) + ""; db.Update("tjms1", str, " TrxNo='" + strTrxNo + "' "); str = "OfficeInChargeName= " + Modfunction.SQLSafeValue(OfficeInChargeName) + ""; db.Update("tjms2", str, " TrxNo='" + strTrxNo + "' "); str = " CompanyName = " + Modfunction.SQLSafeValue(CompanyName) + ""; db.Update("saco1", str ); } } Result = 1; } } } } catch { throw; } return(Result); }