User:Gichumz/sandbox

Grv Capture
Program: nstrgrv

 onLoad  Select Top 1 * From GRVH
 * Connect to database
 * Setup PUD Header Memory data table:
 * DA.Fill(DS, "GRVH").Query:

Select Top 1 * From GRVD
 * DA.Fill(DS, "GRVD").Query:

Select Line=PDR_LINE_NO, Product=PDR_PRD_CODE," _ & " OSQty=PDR_OUTS_QTY, GRVNow=0.00e, Description=substring(PDR_DESC1,1,100)" _& " From PDR " _& " Where 1=2 " Select     EMP_CODE,     EMP_NAME,     convert(char(12),getdate,106),     EMP_AUT_LIMIT " _& "  From     EMP,     ZUSERS  Where     ZUSERS_ID = '" & Trim(mUSuid) & "' " _& "  And     EMP_CODE = ZUSERS_EMP_CODE
 * DA.Fill(DS, "GRVDDG1").Query:
 * Get User Information.Query:
 * Display Status on form
 * Show xOptions

 Button: Save H DR_GRVH.Item("GRVH_TRANSPORT") = "N" DR_GRVH.Item("GRVH_GL_POSTED") = "N" DR_GRVH.Item("GRVH_INPUT_DOC") = Trim(GRVH_INPUT_DOC.Text) GRVD_GRVH_DIV_CODE.Text = GRVH_DIV_CODE.Text GRVD_GRVH_DOC_NUMBR.Text = GRVH_DOC_NUMBR.Text DR_GRVD.Item("GRVD_GRVH_DIV_CODE") = DR_GRVH.Item("GRVH_DIV_CODE") DR_GRVD.Item("GRVD_GRVH_DOC_NUMBR") = DR_GRVH.Item("GRVH_DOC_NUMBR") LoadDetailRecord
 * Get xOptions selected index
 * Select Case xOptions.SelectedIndex
 * Validate GRVH_DOC_NUMBR.Text,GRVH_SUP_DNUM.Text,GRVH_DATE.Text,GRVH_ACC_MTH.Text,GRVH_ACC_YEAR.Text
 * Enable TabPage2
 * Enable GRVD_GRVH_DIV_CODE
 * GRVD_GRVH_DOC_NUMBR
 * If DS.Tables("GRVD").Rows.Count > 0 -button Accept Enable
 * else -OptionsD selected index = 0
 * Assign values to variables
 * If DS.Tables("GRVD").Rows.Count > 0 - button next = true - button previous = false

fldsd_disab

flds_disab Select Line=PUD_LINE_NO, Product=PUD_PRD_CODE,"_&" OSQty=PUD_OUTS_QTY, GRVNow=0.00e, Description=PRD_NAME, VatCode=PUD_VATCAT_CODE " _& " From PUD,PRD " _& " Where PUD_PUR_DIV_CODE = '" & Trim(nDIV)& "' " _& " and PUD_PUR_CODE = '" & Trim(nPUR) & "' " _& " and PUD_PRD_CODE = PRD_CODE " _& " and PUD_STS_CODE = '1' " _& " and round(PUD_OUTS_QTY,4) > 0 " _& " order by     PUD_LINE_NO " Select     Line=PDR_LINE_NO,    Product=PDR_PRD_CODE," _& "    OSQty=PDR_OUTS_QTY,    GRVNow=0.00e,    Description=substring(PDR_DESC1,1,100) " _& "  From     PDR " _& "  Where     PDR_PUR_DIV_CODE = '" & Trim(nDIV) & "' " _& "  and     PDR_PUR_CODE = '" & Trim(nPUR) & "' " _& "  and     round(PDR_OUTS_QTY,4) > 0 " _& "  and     PDR_STS_CODE = '1' " _& "  order by     PDR_LINE_NO "  Save Button L  DS.Tables("GRVD").Rows(updRow).Item("GRVD_QTY") = Math.Round(CDbl(GRVD_QTY.Text), 3) DS.Tables("GRVD").Rows(updRow).Item("GRVD_QTY_OUTS") = Math.Round(CDbl(GRVD_QTY.Text), 3) DS.Tables("GRVD").Rows(updRow).Item("GRVD_DEL_COMPL") = Trim(GRVD_DEL_COMPL.Text) DS.Tables("GRVD").Rows(updRow).Item("GRVD_COMMENT") = Trim(GRVD_COMMENT.Text) DS.Tables("GRVD").Rows(updRow).Item("GRVD_COST") = CDbl(GRVD_COST.Text) DS.Tables("GRVD").Rows(updRow).Item("GRVD_VAT_VALUE") = _((CDbl(GRVD_QTY.Text) / CDbl(nPPER))*CDbl(nCST))_* CDbl(1 - (nDIS / 100))_* CDbl(DS.Tables("GRVD").Rows(updRow).Item("GRVD_VATRATE") / 100) DS.Tables("GRVD").Rows(updRow).AcceptChanges DS.Tables("GRVD").AcceptChanges DS.Tables("GRVDDG1").Rows(updRow).Item("GRVNow") _= Math.Round(CDbl(GRVD_QTY.Text), 3) DS.Tables("GRVDDG1").Rows(updRow).AcceptChanges DS.Tables("GRVDDG1").AcceptChanges LoadDetailRecord
 * Enable options
 * Tab 2 enabled
 * Focus on tab 2
 * Hide Button Quit H
 * Hide Button Save H
 * Hide Button Save L
 * Show Button Quit D
 * If Trim(cOTP) = 1
 * Get PUD,PRD .Query:
 * Else
 * End If
 * Enable datagrid DG1
 * If xOptionsD.GetSelected(0) = True
 * Validate GRVD_QTY.Text, (nQte) > 0
 * End If
 * If xOptionsD.GetSelected(0) = True
 * Update Rows
 * Enable Datagrid DG1
 * Read Only Datagrid DG1
 * Show Datagrid DG1
 * End If
 * Load Details

fldsd_disab xOptionsD.Focus
 * Show Button Accept D
 * Hide Button Save L
 * Show Button Quit
 * Show xOptionsD
 * Disable Button Save L
 * Enable Button Quit L
 * Enable Button Accept D
 * Hide Button Quit L
 * If DS.Tables("GRVD").Rows.Count > 1
 * Enable Button Next (btnNext)
 * Disable Button Previous (btnPre)
 * End If

 Button Quit L  fldsd_clear
 * If xOptionsD.GetSelected(0) = True

fldsd_disab LoadDetailRecord fldsd_disab
 * Hide Button Quit L
 * Hide Button Save L
 * Show Button Quit D
 * Show Button Accept
 * If DS.Tables("GRVD").Rows.Count > 1
 * Display Label on form - (lblGRVDCount.Text)
 * Enable Button Next
 * Disable Button Previous
 * End If
 * If DS.Tables("GRVD").Rows.Count = 1
 * Display Label on form - (lblGRVDCount.Text)
 * Disable Button Next
 * Disable Button Previous
 * End If
 * Show xOptionsD
 * End If

 Button Quit H  Call Shell(Trim(mApps) & "vbexes\nstrgrv.exe " & mUGroup & " " & mUSuid & " " & mDBF & " " & mSource  & " " & mPassw & " " & mApps, 1)

 Button Previous  LoadDetailRecord
 * Row -1
 * GRVDCount
 * Display Label on form - (lblGRVDCount.Text) - Counting row of amount of total rows
 * If intRow = 0
 * Disable Button Previous
 * Button Next Enabled

 Button Next LoadDetailRecord fldsd_disab
 * Row + 1
 * Display Label on form - (lblGRVDCount.Text) - Counting row of amount of total rows


 * If intRow = DS.Tables("GRVD").Rows.Count - 1
 * Disable Button Next
 * Enable Button Previous

 Button 2  Call Shell(Trim(mApps) & "vbexes\nstrgrv.exe " & mUGroup & " " & mUSuid & " " & mDBF & " "  & mSource & " " & mPassw & " " & mApps, 1)
 * Restart Program with variables

Call Shell(Trim(mApps) & "vbexes\login.exe " & mUGroup & " " & mUSuid & " " & mDBF & " "  & mSource & " " & mPassw & " " & mApps, 1)
 * Restart Program with variables

 Button Accept D  Call Shell(Trim(mApps) & "vbexes\login.exe " & mUGroup & " " & mUSuid & " " & mDBF & " " & mSource & " " & mPassw & " " & mApps, 1) If CDbl(MyItem.Item("GRVNow")) = 0 Then mess = mess & Trim(CDbl(MyItem.Item("Line"))) & " " antw = "Y" End If      If Trim(antw) = "Y" Then If Trim(mess.Substring(0, 1)) <> "" Then Response = MsgBox("Line(s) " & Trim(mess) & " Not Captured !" & Chr(13) _    & "OK To Accept ?", 4) If Response = MsgBoxResult.No Then Exit Sub Else For Each MyItem As DataRow In DS.Tables("GRVD").Rows If CDbl(MyItem.Item("GRVD_QTY")) = 0 Then MyItem.Delete End If    Next DS.Tables("GRVD").AcceptChanges End If End If End If   If CDbl(MyItem.Item("GRVD_QTY")) = 0 Then MsgBox("Line " & CInt(MyItem.Item("GRVD_LINE_NO")) & " Not Captured !") Exit Sub End If  COM.CommandText = "           update                   NDOC " _& "                   set NDOC_GRV_N=NDOC_GRV_N+1 " _& "           where                   NDOC_DIV_CODE = '" & Trim(DR_GRVH.Item("GRVH_DIV_CODE")) & "' " _& "          select                   rtrim(NDOC_GRV_P)+ltrim(str(NDOC_GRV_N))           from                   NDOC " _& "           where                   NDOC_DIV_CODE = '" & Trim(DR_GRVH.Item("GRVH_DIV_CODE")) & "' "
 * If xOptions.GetSelected(0)= True
 * If DS.Tables("GRVD").Rows.Count = 0
 * Populate Message Box
 * Restart Program with variables
 * For Each MyItem As DataRow In DS.Tables("GRVDDG1").Rows
 * Next
 * For Each MyItem As DataRow In DS.Tables("GRVD").Rows
 * Next

CN.Open

CN.Close

strSQL = "     insert             GRVH values(0," & Trim(mUSuid) & ",getdate,'" _            & Trim(DR_GRVH.Item("GRVH_DIV_CODE")) & "','" _            & Trim(DR_GRVH.Item("GRVH_PUR_CODE")) & "','" _            & Trim(DR_GRVH.Item("GRVH_DOC_NUMBR")) & "','" _            & Trim(DR_GRVH.Item("GRVH_DEP_CODE")) & "','" _            & Trim(DR_GRVH.Item("GRVH_RPT_CODE")) & "','" _            & DR_GRVH.Item("GRVH_DATE") & "','" _            & Trim(DR_GRVH.Item("GRVH_SUP_DNUM")) & "'," _            & Trim(DR_GRVH.Item("GRVH_ACC_MTH")) & "," _            & Trim(DR_GRVH.Item("GRVH_ACC_YEAR")) & ",'" _            & Trim(DR_GRVH.Item("GRVH_INPUT_DOC")) & "','" _            & Trim(DR_GRVH.Item("GRVH_TRANSPORT")) & "','" _            & Trim(DR_GRVH.Item("GRVH_GL_POSTED")) & "') " cTRANSGRV = Trim(HNUM) & "t"
 * Next
 * Insert
 * For Each MyItem As DataRow In DS.Tables("GRVD").Rows

If (Trim(MyItem.Item(15)) = 6) Then strSQL = strSQL & "                  insert                     GRVD values(" _                    & Trim(MyItem.Item(0)) & "," _                    & Trim(MyItem.Item(1)) & ",'" _                    & Trim(MyItem.Item(2)) & "','" _                    & Trim(MyItem.Item(3)) & "','" _                    & Trim(MyItem.Item(4)) & "','" _                    & Trim(MyItem.Item(5)) & "'," _                    & Trim(MyItem.Item(6)) & ",'" _                    & Trim(MyItem.Item(7)) & "','" _                    & Trim(MyItem.Item(8)) & "','" _                    & Trim(MyItem.Item(9)) & "','" _                    & Trim(MyItem.Item(10)) & "'," _                    & Trim(MyItem.Item(11)) & "," _                    & Trim(MyItem.Item(12)) & ",'" _                    & Trim(MyItem.Item(13)) & "','" _                    & Trim(MyItem.Item(14)) & "'," _                    & Trim(MyItem.Item(15)) & "," _ & "0," _                   & "0,'" _                    & Trim(MyItem.Item(18)) & "'," _ & Trim(MyItem.Item(19)) & "," _ & Trim(MyItem.Item(20)) & ")"               Else                    strSQL = strSQL & "                    insert                     GRVD values(" _ & Trim(MyItem.Item(0)) & "," _ & Trim(MyItem.Item(1)) & ",'" _ & Trim(MyItem.Item(2)) & "','" _ & Trim(MyItem.Item(3)) & "','" _ & Trim(MyItem.Item(4)) & "','" _ & Trim(MyItem.Item(5)) & "'," _ & Trim(MyItem.Item(6)) & ",'" _ & Trim(MyItem.Item(7)) & "','" _ & Trim(MyItem.Item(8)) & "','" _ & Trim(MyItem.Item(9)) & "','" _ & Trim(MyItem.Item(10)) & "'," _ & Trim(MyItem.Item(11)) & "," _ & Trim(MyItem.Item(12)) & ",'" _ & Trim(MyItem.Item(13)) & "','" _ & Trim(MyItem.Item(14)) & "'," _ & Trim(MyItem.Item(15)) & "," _ & Trim(MyItem.Item(16)) & "," _ & Trim(MyItem.Item(17)) & ",'" _ & Trim(MyItem.Item(18)) & "'," _ & Trim(MyItem.Item(19)) & "," _ & Trim(MyItem.Item(20)) & ")"               End If

If Trim(cOTP) = "1" 'update STM strSQL = strSQL & " execute UPD_STM '" _ & Trim(DR_GRVH.Item("GRVH_DEP_CODE")) & "','" _ & Trim(MyItem.Item("GRVD_PRD_CODE")) & "','0'," _ & CInt(DR_GRVH.Item("GRVH_ACC_YEAR")) & "," _ & CInt(DR_GRVH.Item("GRVH_ACC_MTH")) & "," _ & CDbl(MyItem.Item("GRVD_QTY") * -1) & "," _ & CDbl(MyItem.Item("GRVD_QTY")) & "," _ & "0," & CDbl(MyItem.Item("GRVD_COST"))' update FIF strSQL = strSQL & "                 insert                     FIF values(0," & Trim(mUSuid) & ",getdate,'" _                    & Trim(DR_GRVH.Item("GRVH_DEP_CODE")) & "','" _                    & Trim(MyItem.Item("GRVD_PRD_CODE")) _                    & "',getdate,'" _                    & Trim(HNUM) & "'," _                    & CDbl(MyItem.Item("GRVD_QTY")) & "," _                    & CDbl(MyItem.Item("GRVD_COST")) / CDbl(MyItem.Item("GRVD_QTY")) & "," _                    & CDbl(MyItem.Item("GRVD_QTY")) & "," _                    & CDbl(MyItem.Item("GRVD_COST")) & ")" 'update PRC COM.CommandText = " Select count(*)                 from                     PRC " _& "                 Where                     PRC_PRD_CODE = '" _ & Trim(MyItem.Item("GRVD_PRD_CODE")) & "' " CN.Open sValue = COM.ExecuteScalar CN.Close If CInt(sValue) = 0 Then strSQL = strSQL & "                      insert                         PRC " _ & "values(0," & Trim(mUSuid) & ",getdate,'" _                       & Trim(MyItem.Item("GRVD_PRD_CODE")) & "'," _                        & CDbl(nCST) + CDbl(nTRANS) & ",0.00,0.00,0.00," _                        & CDbl(nCST) + CDbl(nTRANS) & ",0.00,0.00,0.00,0.00)" Else strSQL = strSQL & "                      update                         PRC set " _ & "PRC_L_COST = " & CDbl(MyItem.Item("GRVD_COST")) / CDbl(MyItem.Item("GRVD_QTY")) _& "                      where                         PRC_PRD_CODE = '" _ & Trim(MyItem.Item("GRVD_PRD_CODE")) & "' " End If                   'Update PRD strSQL = strSQL & "                  update                     PRD" _ & " set PRD_RPT_CODE = '1'," _ & "PRD_LAST_MOVE = getdate " _ & "                 where                     PRD_CODE = '" _ & Trim(MyItem.Item("GRVD_PRD_CODE")) & "' " 'updatePUD strSQL = strSQL & "                 update                     PUD " _& "set PUD_OUTS_QTY=PUD_OUTS_QTY-" _ & CDbl(MyItem.Item("GRVD_QTY")) If Trim(MyItem.Item("GRVD_DEL_COMPL")) = "Y" Then strSQL = strSQL & ",PUD_STS_CODE='2' " End If                     strSQL = strSQL & "                   where                       PUD_PUR_DIV_CODE = '" & Trim(nDIV) & "' " _& "                   and                       PUD_PUR_CODE = '" & Trim(nPUR) & "' " _& "                   and                       PUD_LINE_NO = " & CInt(MyItem.Item("GRVD_LINE_NO")) _& "                   and                       PUD_PRD_CODE = '" _& Trim(MyItem.Item("GRVD_PRD_CODE")) & "' " If CDbl(nTRANS) <> 0 Then strSQLt = strSQLt & "                     insert                         GRVD " _ & "values(0," & Trim(mUSuid) & ",getdate" _                       & ",'" & Trim(nDIV) & "'" _                        & ",'" & Trim(nPUR) & "'" _                        & ",'" & Trim(cTRANSGRV) & "'" _                        & "," & CInt(MyItem.Item("GRVD_LINE_NO")) _                        & ",'" & Trim(MyItem.Item("GRVD_PRD_CODE")) _                        & "','" & Trim(MyItem.Item("GRVD_GEN_CODE")) _                        & "','" & Trim(MyItem.Item("GRVD_COS_CODE")) _                        & "','" & Trim(MyItem.Item("GRVD_SUBLED")) _                        & "'," & CDbl(MyItem.Item("GRVD_QTY")) _                        & "," & CDbl(MyItem.Item("GRVD_QTY")) * CDbl(nTRANS) _                        & ",'N','Transport Cost'" _                        & "," & CInt(MyItem.Item("GRVD_VATCAT_CODE")) _                        & "," & CDbl(MyItem.Item("GRVD_VATRATE")) _ & "," & (CDbl(MyItem.Item("GRVD_QTY")) * CDbl(nTRANS)) * CDbl(MyItem.Item("GRVD_VATRATE")) / 100 _ & ",'N',0," & CDbl(MyItem.Item("GRVD_QTY")) _ & ") "                       strSQL = strSQL & "                       update                            GRVD " _                           & " set GRVD_COST=GRVD_COST-" & CDbl(MyItem.Item("GRVD_QTY")) * CDbl(nTRANS) _                           & " ,GRVD_VAT_VALUE=GRVD_VAT_VALUE-"                            & (CDbl(MyItem.Item("GRVD_QTY")) * CDbl(nTRANS)) *                            CDbl(MyItem.Item("GRVD_VATRATE")) / 100 _& "                       where GRVD_GRVH_DIV_CODE = '" & Trim(nDIV) & "' " _& "                       and                            GRVD_GRVH_PUR_CODE = '" & Trim(nPUR) & "' " _& "                       and                            GRVD_GRVH_DOC_NUMBR = '" & Trim(HNUM) & "' " _& "                       and                            GRVD_LINE_NO = " & CInt(MyItem.Item("GRVD_LINE_NO")) _& "                       and                            GRVD_PRD_CODE = '" & Trim(MyItem.Item("GRVD_PRD_CODE")) & "' " End If

Else 'update PUD strSQL = strSQL & "                  update                         PDR " _ & "set PDR_OUTS_QTY=PDR_OUTS_QTY-" _ & CDbl(MyItem.Item("GRVD_QTY")) If Trim(MyItem.Item("GRVD_DEL_COMPL")) = "Y" Then strSQL = strSQL & ",PDR_STS_CODE='2' " End If                       strSQL = strSQL & "                   where                         PDR_PUR_DIV_CODE = '" & Trim(nDIV) & "' " _& "                   and                         PDR_PUR_CODE = '" & Trim(nPUR) & "' " _& "                   and                        PDR_LINE_NO = " & CInt(MyItem.Item("GRVD_LINE_NO")) _& "                   and                         PDR_PRD_CODE = '" _& Trim(MyItem.Item("GRVD_PRD_CODE")) & "' " _& "                   and                         PDR_COS_CODE = '" & Trim(MyItem.Item("GRVD_COS_CODE")) & "'" _& "                   and                         PDR_GEN_CODE = '" & Trim(MyItem.Item("GRVD_GEN_CODE")) & "'" _& "                   and                         PDR_SUBLED = '" & Trim(MyItem.Item("GRVD_SUBLED")) & "'" If Trim(MyItem.Item("GRVD_GEN_CODE")) = "249097" Then strSQL = strSQL _& "                  insert                         PJTRN " _& "                   select                         0,1,getdate,PDR_PUR_DIV_CODE,PDR_SUBLED," _ & " substring(PDR_PJT_CODE,4,7),'1'," _ & " PDR_PRD_CODE,substring(PDR_DESC1,1,30),RPT_SHORT,'" _ & Trim(MyItem.Item("GRVD_GRVH_DOC_NUMBR")) & "' " _ & " ,PUR_CRD_SUP_CODE,getdate," _ & CDbl(MyItem.Item("GRVD_QTY")) _ & "," & CDbl(MyItem.Item("GRVD_COST")) _ & " ,SUP_NAME," _ & Trim(DR_GRVH.Item("GRVH_ACC_MTH")) & "," _ & Trim(DR_GRVH.Item("GRVH_ACC_YEAR")) & "," _ & " substring(PDR_PJT_CODE,1,3)" _& "                  from                         PDR,PUR,SUP,RPT " _& "                   where                         PDR_PUR_DIV_CODE = '" & Trim(nDIV) & "' " _& "                   and                         PDR_PUR_CODE = '" & Trim(nPUR) & "' " _& "                   and                         PDR_LINE_NO = " & CInt(MyItem.Item("GRVD_LINE_NO")) _& "                   and                         PDR_PRD_CODE = '" _& Trim(MyItem.Item("GRVD_PRD_CODE")) & "' " _& "                   and                         PDR_COS_CODE = '" & Trim(MyItem.Item("GRVD_COS_CODE")) & "'" _& "                   and                       PDR_GEN_CODE = '" & Trim(MyItem.Item("GRVD_GEN_CODE")) & "'" _& "                   and                        PDR_SUBLED = '" & Trim(MyItem.Item("GRVD_SUBLED")) & "'" _& "                   and                       PUR_DIV_CODE = PDR_PUR_DIV_CODE " _& " and PUR_CODE = PDR_PUR_CODE " _& " and PUR_CRD_SUP_CODE = SUP_CODE " _& " and RPT_CODE = '1' "                   End If                End If strSQL = strSQL _& " update PUR set PUR_STS_CODE = '2' " _& " where PUR_CODE = '" & Trim(nPUR) & "' " _& " and PUR_DIV_CODE = '" & Trim(nDIV) & "'" _& " and (                select                      count(*)                  from                      PUD " _& "                  where                      PUD_PUR_CODE  = '" & Trim(nPUR) & "'" _& "                  and                      PUD_PUR_DIV_CODE  = '" & Trim(nDIV) & "'" _& "                  and                      PUD_STS_CODE <> '2'                ) = 0 " strSQL = strSQL _& " update PUR set PUR_STS_CODE = '2' " _& " where PUR_CODE = '" & Trim(nPUR) & "' " _               & " and PUR_DIV_CODE = '" & Trim(nDIV) & "'" _                & " and (select count(*) from PDR " _                & " where PDR_PUR_CODE  = '" & Trim(nPUR) & "'" _                & " and PDR_PUR_DIV_CODE  = '" & Trim(nDIV) & "'" _                & " and PDR_STS_CODE <> '2') = 0 "            End If
 * Next
 * If Trim(cOTP) = "1"
 * Else