var vSelectedDoc as String
var vData 	as String
var vHeader 	as String
var vRecord	as String
var vPartNo	as String
var vPartList	as String
var vPartData	as String
var vForm 	as String = "Kits"
var vSubform	as String = "Parts for Kit"
var vErrorList 	as String = ""
var vSrcList	as String = "Manufacturer;Vendor;Description;Our_Cost;Mark_Up;Sell_Price;Part_ID;lbs;kg"
var vCost	as Double
var vMarkup	as Double
var vMarkupTemp as Double
var vQty	as Int
var n 		as Int
var i		as Int
var vLineNo	as Int = 1
var Go 		as Int = 1
var loopGo	as Int = 1
var vCnt	as Int

//---------------------------------------------
FUNCTION @GetSiemens( vAllData as String ) as String

var vTemp	as String
var vPartData 	as String
var vCnt	as Int
var n		as Int

While ( vAllData <> "" )
{
	//cycle until siemens manufacturer is found in record
	//if none are found, just keep first record and return
	vTemp = Split( vAllData, @NL() )
	
	If ( ToLower( @AccessStringArray( vTemp, 2 ) ) = "siemens" ) OR ( vPartData = "" )
	{
		vPartData = vTemp
	}
}

RETURN vPartData

END FUNCTION

//----------------------------------------------

//get the directory location of the desired file
If ( @Mode() <> 0 )
{
	@MsgBox("Please switch to 'Add Kits' to use this functionality.", "", "")
	Go = 0
}
If ( Go = 1 )
{
	vSelectedDoc = @GetDocDirectory( "", "" )

	If ( vSelectedDoc = "error")
	{
		Go = 0
	}
}
If ( Go = 1 )	//check if input is valid
{
	//grab the header/first line
	vData = @Insert( vSelectedDoc )
	vHeader = Split( vData, @NL() )
	SetStringArraySeparator( "," )
}
If ( Go = 1 )	//confirm user wants to do import
{
	If Not ( @AskUser( "This will import the following file:", vSelectedDoc, "Are you sure you want to do this?" ) )
	{
		Go = 0
	}
}
If ( Go = 1 )	//start dumping in data
{
	//massage data 
	vData = @RemoveCommas_new( vData )
	vData = @RemovePriceFormatting( vData )

	//while rows aren't empty
	While ( vData <> "" )
	{
		vLineNo += 1
		vPartList = ""

		SetStringArraySeparator( "," )
		
		//isolate a single record from csv
		vRecord = Split( vData, @NL() )

		//split first 10 columns
		//if all info is present, create record
		If ( @AccessStringArray( vRecord, 1 ) = "" ) OR ( @AccessStringArray( vRecord, 2 ) = "" ) OR ( @AccessStringArray( vRecord, 3 ) = "" )
		{
			vErrorList = @AppendToList( vErrorList, "Incomplete record found on Line #" + vLineNo )
		}
		Else
		{
			//create new record
			n = @FormNewRecord( vForm )
			Company = @Replace( Split( vRecord, "," ), "~^~", "," )
			Kit_Name = @Replace( Split( vRecord, "," ), "~^~", "," )
			Kit_Info = @Replace( Split( vRecord, "," ), "~^~", "," )
			Sub1 = @Replace( Split( vRecord, "," ), "~^~", "," )
			Sub2 = @Replace( Split( vRecord, "," ), "~^~", "," )
			Sub3 = @Replace( Split( vRecord, "," ), "~^~", "," )
			Sub4 = @Replace( Split( vRecord, "," ), "~^~", "," )
			Sub5 = @Replace( Split( vRecord, "," ), "~^~", "," )
			Sub6 = @Replace( Split( vRecord, "," ), "~^~", "," )
			Notes = @Replace( Split( vRecord, "," ), "~^~", "," )

			GlobalValue("Part_ID", @Right("000000" + @TN(@Int(@GlobalValue("Part_ID")) + 1), 6))
			Part_ID = @GlobalValue("Part_ID")
	
			//while columns remain
			While ( vRecord <> "" )
			{
				loopGo = 1
				
				//split off cell
				vPartNo = @Replace( Split( vRecord, "," ), "~^~", "," )
	
				SetStringArraySeparator( ";" )			
	
				//if blank, skip to next column
				If ( vPartNo = "" )
				{
					loopGo = 0
				}
				If ( loopGo = 1 )
				{
					//search part no
					vPartData = @XLookupSourceListAll( @FN, vPartNo, "Parts!Part_No", vSrcList )
				
					If ( vPartData = "" ) //if 0 results, append error message to vErrorList
					{
						vErrorList += "In Kit #" + Part_ID + ": " + vPartNo + " was not found in the database.;"
						loopGo = 0
					}
				}
				If ( loopGo = 1 )
				{
					//if part number has been previously entered for this kit, add 1 to qty
					If ( @InStr( vPartList, vPartNo ) <> 0 )
					{
						//add 1 to qty
						vCnt = @FormResultSetTotal( vSubform )
						For i = 1 to vCnt
						//
							If ( @FormFieldValue( vSubform, "Part_No", i ) = vPartNo )
							{
								vQty = @TN( @FormFieldValue( vSubform, "Qty", i ) ) + 1
								vCost = @TN( @FormFieldValue( vSubform, "Cost", i ) )
								vMarkup = @TN( @FormFieldValue( vSubform, "Mark_Up", i ) )
								
								//---pricing bit because it didn't work in a subroutine for some reason-----

								vMarkupTemp = 1 - vMarkup
								If vMarkupTemp = 0 THEN vMarkupTemp = 1
								
								FormFieldValue( vSubform, "Qty", i, vQty )
								FormFieldValue( vSubform, "Cost", i, vCost )
								FormFieldValue( vSubform, "Ext_Cost", i, vQty * vCost )
								FormFieldValue( vSubform, "Mark_Up", i, vMarkup )
								FormFieldValue( vSubform, "Price", i, vCost / vMarkupTemp )
								FormFieldValue( vSubform, "Ext_Sell", i, vQty * ( vCost / vMarkupTemp ) )
								FormFieldValue( vSubform, "Profit", i, ( vQty * ( vCost / vMarkupTemp ) ) - ( vQty * vCost ) )

								//--------------------------------------------------------------------------
								
								FormCommit( vSubform )
							}
						//
						NEXT

						loopGo = 0
					}
				}
				If ( loopGo = 1 )
				{
					//append part number to part list
					vPartList += vPartNo + ";"
	
					SetStringArraySeparator( @NL() )
					vCnt = @CountStringArray( vPartData )
					SetStringArraySeparator( ";" )

					//if more than one result, prefer siemens vendor
					If ( vCnt > 1 )
					{
						vPartData = @GetSiemens( vPartData )
					}
						
					vQty = 1
					vCost = @TN( @AccessStringArray( vPartData, 4 ) )
					vMarkup = @TN( @AccessStringArray( vPartData, 5 ) )
					
					//insert part using vPartData
					i = @FormNewRecord( vSubform )
					FormFieldValue( vSubform, "Manufacturer", i, @AccessStringArray( vPartData, 1 ) )
					FormFieldValue( vSubform, "Vendor", i, @AccessStringArray( vPartData, 2 ) )
					FormFieldValue( vSubform, "Desc", i, @AccessStringArray( vPartData, 3 ) )
					FormFieldValue( vSubform, "Part_ID", i, @AccessStringArray( vPartData, 7 ) )
					FormFieldValue( vSubform, "lbs", i, @TN( @AccessStringArray( vPartData, 8 ) ) )
					FormFieldValue( vSubform, "kg", i, @TN( @AccessStringArray( vPartData, 9 ) ) )
					FormFieldValue( vSubform, "Part_No", i, vPartNo )
					FormFieldValue( vSubform, "Parent_ID", i, Part_ID )
					FormFieldValue( vSubform, "Record_ID", i, ASSIGN_KIT_PART_RECORD_ID() )	

					//---pricing bit because it didn't work in a subroutine for some reason-----

					vMarkupTemp = 1 - vMarkup
					If vMarkupTemp = 0 THEN vMarkupTemp = 1
					
					FormFieldValue( vSubform, "Qty", i, vQty )
					FormFieldValue( vSubform, "Cost", i, vCost )
					FormFieldValue( vSubform, "Ext_Cost", i, vQty * vCost )
					FormFieldValue( vSubform, "Mark_Up", i, vMarkup )
					FormFieldValue( vSubform, "Price", i, vCost / vMarkupTemp )
					FormFieldValue( vSubform, "Ext_Sell", i, vQty * ( vCost / vMarkupTemp ) )
					FormFieldValue( vSubform, "Profit", i, ( vQty * ( vCost / vMarkupTemp ) ) - ( vQty * vCost ) )

					//--------------------------------------------------------------------------

					FormCommit( vSubform )
				}
			}

		FormCommit( vForm )

		}
	}

	ResultSetCurrentPosition( 1 )
	If ( @FormFieldValue( vSubform, "Part_No", 1 ) = "" )
	{
		WarningLevel( 0 )
		FormDeleteRecord( vSubform, 1 )
	}

	If ( vErrorList <> "" )
	{
		WriteLn( @Replace( vErrorList, ";", @NL() ) )
	}
}