SQL Basics

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesSQL Basics

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old February 18th, 2013, 10:06 AM
emma21 emma21 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Feb 2013
Posts: 1 emma21 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 m 43 sec
Reputation Power: 0
Unhappy Please Help! Writing code to combine hours

Hi

I'm not sure if this is the correct place to post but wondering if anyone can help. We are currently customising an ERP system for use in workplace and are struggling to get this code to work correctly.

Basically we are trying to combine all hours in the sub assemblies into one set of hours for each operation. Although the code below works and there are no errors, we can not get it to take into account the quantity per parent for each sub assembly before the hours are added together (we need to times the hours by the quantity per parent first.)

The column name for the quantity per parent is jmaquantityperparent. I hope that someone can help.

If controls("chkJmpProductionComplete").Value = -1 Then

Call App.MessageBox("You cannot remove the operations on this job as it is marked as completed",vbExclamation,"Cannot proceed")

Else

Dim JobID, JobQty
JobID = Trim(controls("txtJmpJobID").Value)
JobQty = CDbl(controls("txtJmpProductionQuantity").Value)

Set rsGetExistingTimecards = CreateObject("ADODB.Recordset")
rsGetExistingTimecards.Open "Select * from joboperations where jmoJobID In (Select lmlJobID From TimecardLines) And jmoJobID = "+App.AddQuotes(JobID)+" ", Connection, adOpenStatic, adLockBatchOptimistic, adCmdText

If rsGetExistingTimecards.EOF = False Then

Call App.MessageBox("There are timecards that already exist for this Job so the Operations cannot be deleted!",vbExclamation,"Cannot remove JobOperations")

Else

Set rsGetExistingSubcontractPOs = CreateObject("ADODB.Recordset")
rsGetExistingSubcontractPOs.Open "Select * from joboperations where jmoJobID In (Select pmlJobID From PurchaseOrderLines WHERE pmlJobType = 2 ) And jmoJobID = "+App.AddQuotes(JobID)+" ", Connection, adOpenStatic, adLockBatchOptimistic, adCmdText

If rsGetExistingSubcontractPOs.EOF = False Then
Call App.MessageBox("There are PO's that already exist for this Job so the Operations cannot be deleted!",vbExclamation,"Cannot remove JobOperations")
Else
Set rsGetJobData = CreateObject("ADODB.Recordset")
rsGetJobData.Open "Select jmoJobID, jmoJobAssemblyID,row * 10 As jmoJobOperationID,jmoWorkCenterID, jmoProcessID, jmoProductionStandard, jmoStandardFactor,jmoProcessShortDescription ,jmoQuantityPerAssembly , " _
+" jmoOperationType, jmoMachineType, jmoSetupRate,jmoProductionRate, jmoOverheadRate, jmoInspectionType, jmoOperationQuantity FROM " _
+"(Select jmoJobID, 0 As jmoJobAssemblyID, jmoWorkCenterID, Row_Number() OVER(Order By jmoJobID Asc) As row, jmoProcessID, SetupAndProdStandard As jmoProductionStandard, jmoStandardFactor, " _
+" jmoProcessShortDescription,jmoQuantityPerAssembly, jmoOperationType,jmoMachineType, jmosetupRate, jmoProductionRate, jmoOverheadRate,jmoInspectionType, jmoOperationQuantity " _
+" FROM (Select jmojobid, jmoworkcenterid, jmoprocessid ,sum(jmosetupHours)+sum(jmoProductionStandard) As SetupAndProdStandard,'HP' as jmoStandardFactor, max(jmoJobOperationid) as JobOp, " _
+" Max(jmoProcessShortDescription) as jmoProcessShortDescription, max(jmoQuantityPerAssembly) as jmoQuantityPerAssembly, max(jmoOperationType) as jmoOperationType, " _
+" max(jmoMachineType) as jmoMachineType, max(jmoSetupRate) as jmoSetupRate, max(jmoProductionRate) as jmoProductionRate, Max(jmoOverheadRate) as jmoOverheadRate, max(jmoInspectionType) as jmoInspectionType, " _
+" sum(jmoOperationQuantity) as jmoOperationQuantity " _
+" from joboperations where jmojobid = "+App.AddQuotes(JobID)+" GROUP BY jmojobid, jmoworkcenterid, jmoprocessid) as DataTOReplace ) as JobOperationData Order by jmoJobOperationID", Connection, adOpenStatic, adLockBatchOptimistic, adCmdText

connection.Execute "DELETE jobOperations WHERE jmoJobID = "+App.AddQuotes(JobID)+" "
Dim JobAssemblyID, JobOperationID, StdFactor, ProductionStandard, WorkCenterID, ProcessID, ProcessShortDescription, QtyPerAssembly, OpType, MachineType, SetupRate, ProdRate, OverheadRate, InspectionType, OperationQty, Firm

If rsGetJobData.EOF = False Then
rsGetJobData.MoveFirst
Do While rsGetJobData.EOF = False

JobID = Trim(rsGetJobData.Fields("jmoJobID").Value)
JobAssemblyID = CDbl(rsGetJobData.Fields("jmoJobAssemblyID").Value )
JobOperationID = CDbl(rsGetJobData.Fields("jmoJobOperationID").Valu e)
StdFactor= Trim(rsGetJobData.Fields("jmoStandardFactor").Valu e)
ProductionStandard = CDbl(rsGetJobData.Fields("jmoProductionStandard"). Value)
WorkCenterID = Trim(rsGetJobData.Fields("jmoWorkCenterID").Value)
ProcessID = Trim(rsGetJobData.Fields("jmoProcessID").Value)
ProcessShortDescription = Trim(rsGetJobData.Fields("jmoProcessShortDescripti on").Value)
QtyPerAssembly = CDbl(rsGetJobData.Fields("jmoQuantityPerAssembly") .Value)
OpType = CDbl(rsGetJobData.Fields("jmoOperationType").Value )
MachineType = CDbl(rsGetJobData.Fields("jmoMachineType").Value)
SetupRate = CDbl(rsGetJobData.Fields("jmoSetupRate").Value)
ProdRate = CDbl(rsGetJobData.Fields("jmoProductionRate").Valu e)
OverheadRate = CDbl(rsGetJobData.Fields("jmoOverheadRate").Value)
InspectionType = Trim(rsGetJobData.Fields("jmoInspectionType").Valu e)
OperationQty = CDbl(rsGetJobData.Fields("jmoOperationQuantity").V alue)
Firm = -1
QtyPerAssembly = 1
' If JobQty <> 0 Then
' QtyPerAssembly = Round(OperationQty/JobQty,5)
' Else
' QtyPerAssembly = QtyPerAssembly
'
' End If

' Connection.Execute "INSERT INTO JobOperations (jmoJobID,jmoJobAssemblyID, jmoJobOperationID, jmoStandardFactor, jmoProductionStandard, jmoWorkCenterID, jmoProcessID, " _
' +" jmoProcessShortDescription, jmoQuantityPerAssembly, jmoOperationType, jmoMachineType, jmoSetupRate, jmoProductionRate, jmoOverheadRate, jmoInspectionType,jmoOperationQuantity,jmoFirm) " _
' +" VALUES ("+App.AddQuotes(JobID)+","+App.AddQuotes(JobAssem blyID)+","+App.AddQuotes(JobOperationID)+","+App.A ddQuotes(StdFactor)+","+App.AddQuotes(ProductionSt andard)+","+App.AddQuotes(WorkCenterID)+","+App.Ad dQuotes(ProcessID)+", " _
' +" "+App.AddQuotes(ProcessShortDescription)+","+App.A ddQuotes(QtyPerAssembly)+" ,"+App.AddQuotes(OpType)+","+App.AddQuotes(Machine Type)+","+App.AddQuotes(SetupRate)+","+App.AddQuot es(ProdRate)+","+App.AddQuotes(OverheadRate)+","+A pp.AddQuotes(InspectionType)+", "+App.AddQuotes(OperationQty)+",-1) "

Connection.Execute "INSERT INTO JobOperations (jmoJobID,jmoJobAssemblyID, jmoJobOperationID, jmoStandardFactor, jmoProductionStandard, jmoWorkCenterID, jmoProcessID, " _
+" jmoProcessShortDescription, jmoQuantityPerAssembly, jmoOperationType, jmoMachineType, jmoSetupRate, jmoProductionRate, jmoOverheadRate, jmoInspectionType,jmoOperationQuantity,jmoFirm) " _
+" VALUES ("+App.AddQuotes(JobID)+","+App.AddQuotes(JobAssem blyID)+","+App.AddQuotes(JobOperationID)+","+App.A ddQuotes(StdFactor)+","+App.AddQuotes(ProductionSt andard)+","+App.AddQuotes(WorkCenterID)+","+App.Ad dQuotes(ProcessID)+", " _
+" "+App.AddQuotes(ProcessShortDescription)+","+App.A ddQuotes(QtyPerAssembly)+" ,"+App.AddQuotes(OpType)+","+App.AddQuotes(Machine Type)+","+App.AddQuotes(SetupRate)+","+App.AddQuot es(ProdRate)+","+App.AddQuotes(OverheadRate)+","+A pp.AddQuotes(InspectionType)+", "+App.AddQuotes(JobQty)+",-1) "



rsGetJobData.MoveNext
Loop
End If

Call App.MessageBox("The Job Operations have now been replaced",vbInformation,"Job Operations Updated")
controls("M1DataControl").RefreshTree

End If

End If


''''' End stopping the user from deleting the operations
End If

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesSQL Basics > Please Help! Writing code to combine hours


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap