VERSION = 3.00dataenvironmentdataenvironmentDataenvironmentJLeft = 1 Top = 220 Width = 520 Height = 200 Name = "Dataenvironment" 11t_form..\libs\t_base.vcxform frmInvoicing-Height = 329 Width = 375 DoCreate = .T. AutoCenter = .T. Caption = "Invoicing" ccompanytoinvoice = dinvoicemonth = {} ninvoicemonth = 1 dinvoicemonthplus2 = {} lfirstinvoice = .T. dinvoicedate = {} dinvoicedateplus2 = {} lcheckforproductupdates = .T. cinvcode = Name = "frmInvoicing" jPROCEDURE formatinvoicedate LOCAL temp1, temp2 * Change starting invoice date to first of the selected month in the YYYYMMDD format temp1 = ALLTRIM(STR(YEAR(DATE()))) + ',' + ALLTRIM(STR(thisform.nInvoiceMonth)) + ',01' * Change invoice dates to correct Y2K format and add 2 months thisform.dInvoiceDate = DATE(&Temp1) thisform.dInvoiceDatePlus2 = GOMONTH(DATE(&temp1), + 2) * get the first 3 letters of the billing months. 1 = JAN, 2 = FEB thisform.cInvoicemonth = LEFT(CMONTH(thisform.dInvoiceDate), 3) thisform.cInvoiceMonthPlus2 = LEFT(CMONTH(thisform.dInvoiceDatePlus2), 3) * Change date formats to MM/YY format thisform.dInvoiceMonth = thisform.mth_yr_format(thisform.dInvoiceDate) thisform.dInvoiceMonthPlus2 = thisform.mth_yr_format(thisform.dInvoiceDatePlus2) * get current system date and change to MM/YY format thisform.cCurrentSystemDate = thisform.mth_yr_format(DATE()) * format string for date comparison when selecting invoice line items thisform.sInvoiceMonth = "InvoicableSales.sa_b" + thisform.cInvoicemonth thisform.sInvoiceMonthPlus2 = "InvoicableSales.sa_b" + thisform.cInvoiceMonthPlus2 ENDPROC PROCEDURE doinvoicing IF ThisForm.chkCheckForProductUpdates.value DO PR_DOCHG.PRG ENDIF thisform.FormatInvoiceDate thisform.GetInvoicableSalesRecords IF thisform.lContinue thisform.OpenDatabases thisform.GetInvoiceLineItems IF thisform.lContinue thisform.GetInvoiceHeaderInfo thisform.GetRemittoInfo thisform.GetServiceCharges thisform.GetTotalExtended thisform.GetTaxTotals thisform.GetTotalInvoiceAmount thisform.CloseDatabases ELSE MESSAGEBOX("Of the potential invoiceable records, none were eligible.",64,"Invoicing") ENDIF ELSE MESSAGEBOX("No records were found to Invoice.",64,"Invoicing") ENDIF THISFORM.RELEASE() ENDPROC PROCEDURE mth_yr_format PARAMETER tempdate PRIVATE tmonth, tyear, rvalue tmonth = MONTH(tempdate) tyear = YEAR(tempdate) rvalue = ALLTRIM(STR(tmonth)) + '/' + ALLTRIM(STR(tyear)) RETURN(rvalue) ENDPROC PROCEDURE getinvoicablesalesrecords * Get Sales records for appropriate months PRIVATE SQLMonth, SQLMonth_2, cCompany, nPosition, cCompanyCode set dele on SQLcurmth = "sales.sa_b" + thisform.cInvoiceMonth SQLcurmth_2 = "sales.sa_b" + thisform.cInvoiceMonthPlus2 * This is used to convert the company name to the company code nPosition = ASCAN(thisform.aCompanyCode, ThisForm.cboCompanyCode.value) && Search for company IF nPosition != 0 nPosition = nPosition + 2 *** Company found, get company code *** cCompanyCode = thisform.aCompanyCode[nPosition] ENDIF * Select appropriate sales records SELECT *; FROM trackerdb!sales; WHERE Sales.sa_status = 'A'; AND Sales.sa_company = cCompanyCode; AND (&SQLcurmth = .T. OR &SQLcurmth_2 = .T.); AND inlist(LEFT(Sales.sa_pr_id, 1),'A','S') ; INTO TABLE tmp\InvoicableSales.dbf * If the there are no records found then we should stop IF RECCOUNT() > 0 thisform.lContinue = .T. INDEX ON sa_cu_id TAG sa_cu_id ELSE thisform.lContinue = .F. ENDIF USE SELECT sales USE ENDPROC PROCEDURE opendatabases OPEN DATABASE trackerdb IF USED("InvoicableSales.dbf") SELECT InvoicableSales.dbf SET ORDER TO sa_cu_id ELSE SELECT 0 USE (LOCFILE("tmp\InvoicableSales.dbf","DBF","Where is InvoicableSales ?")); AGAIN ALIAS InvoicableSales SET ORDER TO sa_cu_id ENDIF IF USED("products") SELECT products SET ORDER TO TAG prodnum ELSE SELECT 0 USE (LOCFILE("products.dbf","DBF","Where is products ?")); AGAIN ALIAS PRODUCTS SET ORDER TO TAG prodnum ENDIF IF USED("customer") SELECT customer SET ORDER TO TAG cu_id ELSE SELECT 0 USE (LOCFILE("customer.dbf","DBF","Where is customer ?")); AGAIN ALIAS customer SET ORDER TO TAG cu_id ENDIF IF USED("convert") SELECT CONVERT SET ORDER TO TAG co_code ELSE SELECT 0 USE (LOCFILE("convert.dbf","DBF","Where is convert ?")); AGAIN ALIAS CONVERT SET ORDER TO TAG co_code ENDIF IF USED("remitto") SELECT remitto SET ORDER TO TAG re_id ELSE SELECT 0 USE (LOCFILE("remitto.dbf","DBF","Where is remitto ?")); AGAIN ALIAS remitto SET ORDER TO TAG re_id ENDIF SELECT InvoicableSales SET RELATION TO InvoicableSales.sa_pr_id INTO PRODUCTS ADDITIVE SET RELATION TO InvoicableSales.sa_cu_id INTO CUSTOMER ADDITIVE SELECT customer SET RELATION TO customer.cu_convrat INTO CONVERT ADDITIVE SET RELATION TO customer.cu_remitto INTO remitto ADDITIVE *!* Changes made 04/07/2005 *!* *!* Check to see if inv_data and inv_head are open, *!* if so, close them. If Used("inv_data") Use In inv_data EndIf IF USED("inv_head") Use In inv_head EndIf *!* Open inv_data exclusive in order to delete and pack table *!* Then reopen inv_data in shared mode. Select 0 Use (LocFile("inv_data.dbf","DBF","Where is inv_data ?")) Exclusive ; AGAIN ALIAS inv_data Wait Window "Deleting and Packing inv_data file" Nowait Delete All Pack Wait Clear Use In inv_data Select 0 Use (LocFile("inv_data.dbf","DBF","Where is inv_data ?")) Share ; AGAIN ALIAS inv_data *!* Open inv_head exclusive in order to delete and pack table *!* Then reopen inv_head in shared mode. Select 0 Use (LocFile("inv_head.dbf","DBF","Where is inv_data ?")) Exclusive ; AGAIN ALIAS inv_head Wait Window "Deleting and Packing inv_head file" Nowait Delete All Pack Wait Clear Use In inv_head Select 0 Use (LocFile("inv_head.dbf","DBF","Where is inv_data ?")) Share ; AGAIN ALIAS inv_head *!* *!* End of changes made 04/07/2005 *!********************************************************************************************* *!* Old code replace on 04/07/2005 *!* *!* IF USED("inv_data") *!* SELECT inv_data *!* ELSE *!* SELECT 0 *!* USE (LOCFILE("inv_data.dbf","DBF","Where is inv_data ?")) EXCLUSIVE ; *!* AGAIN ALIAS inv_data *!* ENDIF *!* WAIT WINDOW "Deleting and Packing inv_data file" NOWAIT *!* DELETE ALL *!* PACK *!* WAIT CLEAR *!* IF USED("inv_head") *!* SELECT inv_head *!* ELSE *!* SELECT 0 *!* USE (LOCFILE("inv_head.dbf","DBF","Where is inv_head ?")) EXCLUSIVE ; *!* AGAIN ALIAS inv_head *!* ENDIF *!* WAIT WINDOW "Deleting and Packing inv_head file" NOWAIT *!* DELETE ALL *!* PACK *!* WAIT CLEAR *!* *!* End of old code SET CONSOLE OFF ENDPROC PROCEDURE getinvoicelineitems PRIVATE nRecordsRead, nInvoiceCount, cPrev_cu_id nRecordsRead = 0 nInvoiceCount = 0 cPrev_cu_id = '' * For progress thermometer #DEFINE c_thermtitle 'Invoice Extract' #DEFINE c_thermmssg 'Processing Sales file' _elibarcon = -1 _runmancon = 3 _iconpostion = 5 SELECT InvoicableSales GOTO TOP DO therm WITH RECCOUNT(), nRecordsRead, 'Invoicing', 'Processing Sales file', '' DO WHILE NOT EOF("InvoicableSales") AND LASTKEY() <> 27 ***< SelectInvoiceLineItem() figures out if it should be billed invwhatmth = thisform.SelectInvoiceLineItem() IF NOT EMPTY(invwhatmth) SELECT inv_data SCATTER MEMVAR BLANK * If it's a new customer, get a new invoice number IF cPrev_cu_id <> InvoicableSales.sa_cu_id thisform.GetInvoiceNumber cPrev_cu_id = InvoicableSales.sa_cu_id ELSE thisform.cDetailInvoiceNumber = thisform.cPreviousInvoiceNumber ENDIF thisform.GetSubscriptionPeriod(invwhatmth) thisform.PrintInvoiceLineItem() SELECT inv_data APPEND BLANK m.d_invnum = thisform.cDetailInvoiceNumber GATHER MEMVAR thisform.cPreviousInvoiceNumber = thisform.cDetailInvoiceNumber SELECT InvoicableSales nInvoiceCount = nInvoiceCount + 1 ENDIF SKIP 1 IN InvoicableSales nRecordsRead = nRecordsRead + 1 DO therm WITH RECCOUNT(), nRecordsRead, 'Invoicing', 'Processing Sales file', '' ENDDO DO therm WITH RECCOUNT(), nRecordsRead, 'Invoicing', 'Processing Sales file', '' SELECT InvoicableSales USE SELECT Products USE IF RECCOUNT("inv_data") > 0 thisform.lContinue = .T. ELSE thisform.lContinue = .F. ENDIF RETURN ENDPROC PROCEDURE getinvoiceheaderinfo PRIVATE recs_read recs_read = 0 _elibarcon = -1 _runmancon = 3 _iconpostion = 5 set exact on * Get a list of distinct customer IDs from the invoice data file and put them in * a temporary database SELECT Inv_data GOTO TOP SELECT DISTINCT Inv_data.d_cuid, Inv_data.d_invnum; FROM Inv_data; INTO TABLE tmp\tmp_cust.dbf * Use the list of distinct customer IDs and look them up in the customer file. * Copy the information need to create an invoice from the customer file to the * invoice header file. SELECT Customer SET ORDER TO TAG Customer_I SELECT tmp_cust GOTO TOP DO therm WITH RECCOUNT('tmp_cust'), recs_read, 'Invoicing', 'Matching sales and customer information', '' DO WHILE NOT EOF('tmp_cust') SELECT inv_head SCATTER MEMVAR BLANK SELECT customer SEEK tmp_cust.d_cuid IF FOUND() * MESSAGEBOX(tmp_cust.d_cuid + " Customer detail data found", 48 ,"Error") m.h_bname = customer.cu_bname m.h_sname = customer.cu_sname m.h_baddr1 = customer.cu_baddr1 m.h_saddr1 = customer.cu_saddr1 m.h_baddr2 = customer.cu_baddr2 m.h_saddr2 = customer.cu_saddr2 m.h_baddr3 = customer.cu_baddr3 m.h_saddr3 = customer.cu_saddr3 m.h_bcity = customer.cu_bcity m.h_scity = customer.cu_scity m.h_bstate = customer.cu_bstate m.h_sstate = customer.cu_sstate m.h_bcounty = customer.cu_bcounty m.h_scounty = customer.cu_scounty m.h_bzip = customer.cu_bzip m.h_szip = customer.cu_szip m.h_cu_id = customer.customer_id m.h_linvdt = DATE() m.h_acctno = customer.cu_acctno m.h_region = customer.cu_region m.h_market = customer.cu_market m.h_convrat = thisform.GetConversionRate() m.h_convcty = customer.cu_convrat m.h_taxrate = customer.cu_taxrate m.h_shipchg = customer.cu_shipchg m.h_remitto = customer.cu_remitto m.h_invcode = thisform.cInvCode m.h_invnum = tmp_cust.d_invnum IF NOT EMPTY(customer.cu_remitto) m.h_rname = remitto.re_name m.h_raddr1 = remitto.re_addr1 m.h_raddr2 = remitto.re_addr2 m.h_raddr3 = remitto.re_addr3 m.h_rcity = remitto.re_city m.h_rstate = remitto.re_state m.h_rcounty = remitto.re_country m.h_rzip = remitto.re_zip ENDIF SELECT inv_head APPEND BLANK GATHER MEMVAR ELSE MESSAGEBOX(tmp_cust.d_cuid + " Customer detail data not found", 48 ,"Error") ENDIF SKIP 1 IN tmp_cust recs_read = recs_read + 1 DO therm WITH RECCOUNT('tmp_cust'), recs_read, c_thermtitle, 'Matching sales and customer information', '' ENDDO DO therm WITH RECCOUNT('tmp_cust'), recs_read, c_thermtitle, 'Matching sales and customer information', '' ENDPROC PROCEDURE getservicecharges WAIT WINDOW "Getting Service Charges" NOWAIT SELECT DISTINCT Inv_data.d_cuid, Inv_data.d_invnum, Inv_data.d_invfee ; FROM Inv_data; WHERE Inv_data.d_invfee > 0; INTO TABLE tmp\tmp_serv.dbf IF RECCOUNT('tmp_serv') > 0 INDEX ON d_invnum TAG D_invnum ADDITIVE SELECT inv_head SET ORDER TO TAG D_invnum OF tmp\tmp_serv.cdx IN Tmp_serv SET RELATION TO Inv_head.h_invnum INTO Tmp_serv ADDITIVE REPLACE ALL inv_head.h_serchg WITH Tmp_serv.d_invfee ; FOR Inv_head.h_invnum = Tmp_serv.d_invnum ENDIF WAIT CLEAR ENDPROC PROCEDURE getinvoicecodes WAIT WINDOW "Getting Invoice Codes" NOWAIT SELECT DISTINCT Inv_data.d_cuid, Inv_data.d_invnum, Inv_data.d_invcode, Inv_data.d_linvdt; FROM Inv_data; INTO TABLE tmp\tmp_code.dbf IF RECCOUNT('tmp_code') > 0 INDEX ON d_invnum TAG D_invnum ADDITIVE SELECT inv_head SET ORDER TO TAG D_invnum OF tmp\tmp_code.cdx IN Tmp_Code SET RELATION TO Inv_head.h_invnum INTO Tmp_code ADDITIVE REPLACE ALL inv_head.h_Invcode WITH Tmp_code.d_Invcode ; FOR Inv_head.h_invnum = Tmp_code.d_invnum REPLACE ALL inv_head.h_linvdt WITH Tmp_code.d_linvdt ; FOR Inv_head.h_invnum = Tmp_code.d_invnum ENDIF WAIT CLEAR RETURN ENDPROC PROCEDURE countbillingperiods PRIVATE period_cnt period_cnt = 0 IF InvoicableSales.sa_bjan = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_bfeb = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_bmar = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_bapr = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_bmay = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_bjun = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_bjul = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_baug = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_bsep = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_boct = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_bnov = .T. period_cnt = period_cnt + 1 ENDIF IF InvoicableSales.sa_bdec = .T. period_cnt = period_cnt + 1 ENDIF RETURN(period_cnt) ENDPROC PROCEDURE getinvoicenumber PRIVATE v_invpre, v_invnum IF EMPTY(thisform.cCurrentInvoiceNumber) DEFINE WINDOW invnum ; AT 0.000, 0.000 ; SIZE 3.538,48.200 ; FONT "MS Sans Serif", 8 ; NOFLOAT ; NOCLOSE ; NOMINIMIZE ; NONE ; COLOR RGB(,,,255,0,0) MOVE WINDOW invnum CENTER ACTIVATE WINDOW invnum @ 1.154,2.500 SAY "Invoice Number:" ; FONT "MS Sans Serif", 10 ; STYLE "BT" @ 1.154,26.700 GET v_invname ; SIZE 1.000,10.500 ; DEFAULT " " ; VALID NOT EMPTY(v_invname) AND LEN(ALLTRIM(v_invname)) = 9 ; ERROR "Invoice Number Cannot Contain Blanks" ; FONT "MS Sans Serif", 10 ; STYLE "B" ; PICTURE "@K! A99AA9999" ; MESSAGE 'Enter starting invoice number.' @ 0.308,1.000 TO 3.231,47.000 ; PEN 2, 8 @ 3.077,1.200 TO 3.077,46.800 ; PEN 2, 8 ; STYLE "1" ; COLOR RGB(255,255,255,255,255,255) @ 0.385,46.600 TO 3.231,46.600 ; PEN 2, 8 ; COLOR RGB(255,255,255,255,255,255) READ MODAL CLEAR READ v_invpre = LEFT(v_invname, 5) v_invnum = VAL(SUBSTR(v_invname, 6, 4)) v_invnum = TRANSFORM(v_invnum,"@L ####") thisform.cCurrentInvoiceNumber = v_invpre + v_invnum prev_cu_id = InvoicableSales.sa_cu_id ELSE thisform.lFirstInvoice = .F. v_invpre = LEFT(thisform.cCurrentInvoiceNumber, 5) v_invnum = VAL(SUBSTR(thisform.cCurrentInvoiceNumber, 6, 4)) + 1 v_invnum = TRANSFORM(v_invnum,"@L ####") thisform.cCurrentInvoiceNumber = v_invpre + v_invnum ENDIF thisform.cCurrentInvoiceNumber = v_invpre + v_invnum thisform.cHeaderInvoiceNumber = thisform.cCurrentInvoiceNumber thisform.cDetailInvoiceNumber = thisform.cCurrentInvoiceNumber IF thisform.lFirstInvoice thisform.cPreviousInvoiceNumber = thisform.cCurrentInvoiceNumber ENDIF DEACTIVATE WINDOW invnum RETURN ENDPROC PROCEDURE selectinvoicelineitem PRIVATE rvalue, sInvoiceMonth, sInvoiceMonthPlus2, BillPdCt, BillPdLen rvalue = "" sInvoiceMonth = thisform.sInvoiceMonth && "InvoicableSales.sa_b" + thisform.cInvoicemonth = "InvoicableSales.sa_bJune" sInvoiceMonthPlus2 = thisform.sInvoiceMonthPlus2 && Same as above but add 2 months to it lastinvdt = thisform.mth_yr_format(InvoicableSales.sa_linvdt) BillPdCt = thisform.CountBillingPeriods() ***< Use the number of billing periods to figure out how often it should be billed do case case BillPdCt = 1 BillPdLen = 360 case BillPdCt = 2 BillPdLen = 180 case BillPdCt = 4 BillPdLen = 90 case BillPdCt = 12 BillPdLen = 28 otherwise BillPdLen = 0 endcase do case ***< Never been billed before and it's marked to be billed case &sInvoiceMonth and empty(sa_finvdt) rvalue = thisform.dInvoiceDate ***< Annual billing, it's time for the 2nd billing, 10 months out from the first billing case &sInvoiceMonth and LEFT(sa_pr_id, 1) = "A" and lastinvdt <> thisform.cCurrentSystemDate ; and sa_linvdt = sa_finvdt and ; between(date()-sa_linvdt, 290,330) rvalue = thisform.dInvoiceDate ***< Marked to bill this month, product type=A and not already billed this month and it's due ***< This gets all other Annual billing case &sInvoiceMonth and LEFT(sa_pr_id, 1) = "A" and lastinvdt <> thisform.cCurrentSystemDate ; and sa_linvdt <> sa_finvdt ; and (date()- sa_linvdt) > BillPdLen rvalue = thisform.dInvoiceDate ***< Billmonth = This month + 2, product type=A and not already billed this month case &sInvoiceMonthPlus2 and LEFT(sa_pr_id, 1) = "A" and lastinvdt <> thisform.cCurrentSystemDate rvalue = thisform.dInvoiceDatePlus2 **< Billmonth = This Month + 2, product type=S and NEVER billed before case &sInvoiceMonthPlus2 and LEFT(sa_pr_id, 1) = "S" and EMPTY(sa_finvdt) rvalue = thisform.dInvoiceDatePlus2 endcase **< The code below is commented out. cps 6-4-2000 if .f. IF &sInvoiceMonth && If it is marked to be invoiced in this month IF LEFT(InvoicableSales.sa_pr_id, 1) = "A" && <<>> lastinvdt = thisform.mth_yr_format(InvoicableSales.sa_linvdt) IF lastinvdt <> thisform.cCurrentSystemDate rvalue = thisform.dInvoiceDate ENDIF (lastinvdt <> thisform.cCurrentSystemDate) ELSE IF LEFT(InvoicableSales.sa_pr_id, 1) = "S" IF EMPTY(InvoicableSales.sa_linvdt) rvalue = thisform.dInvoiceDate ENDIF ENDIF ENDIF ELSE IF &sInvoiceMonthPlus2 = .T. IF LEFT(InvoicableSales.sa_pr_id, 1) = "A" lastinvdt = thisform.mth_yr_format(InvoicableSales.sa_linvdt) IF lastinvdt <> thisform.cCurrentSystemDate rvalue = thisform.dInvoiceDatePlus2 ENDIF ELSE IF LEFT(InvoicableSales.sa_pr_id, 1) = "S" IF EMPTY(InvoicableSales.sa_linvdt) rvalue = thisform.dInvoiceDatePlus2 ENDIF ENDIF ENDIF (LEFT(InvoicableSales.sa_pr_id, 1) = "A") ENDIF (&sInvoiceMonthPlus2 = .T.) ENDIF (&sInvoiceMonth if this month is the invoice month) endif (.f.) RETURN(rvalue) ENDPROC PROCEDURE printinvoicelineitem PRIVATE discount num_months = thisform.CountBillingPeriods() discount = ROUND(InvoicableSales.sa_disc / 100, 6) m.d_disc = discount m.d_descrip = products.description m.d_qty = InvoicableSales.sa_qty && This IF was added 06/24/97 to account for multiple year subscriptions IF products.SubLength > 1 *!* m.d_list = products.pr_list / num_months / products.pr_years m.d_list = InvoicableSales.sa_SalesPr / num_months / products.SubLength ELSE *!* m.d_list = products.pr_list / num_months m.d_list = InvoicableSales.sa_SalesPr / num_months ENDIF IF discount > 0 m.d_netprice = m.d_list * (1 - discount) ELSE m.d_netprice = m.d_list ENDIF m.d_linettl = ROUND(m.d_qty * m.d_netprice, 2) && Does a service charge need to be added && These lines were commented out 09/03/97 to add new code for the addition of the && sa_invfee field. &&IF num_months > 1 AND ; && ((MONTH(DATE()) <> MONTH(sales.sa_finvdt)) OR sales.sa_date >= {05/01/94}) && m.in_serchg = serchrg() &&ELSE && m.in_serchg = 0 &&ENDIF && The memory variable m.srvcchrg is used to catch any line item in an invoice with a service && charge. That charge will be applied to the invoice. IF NOT EMPTY(InvoicableSales.sa_invfee) * m.prev_srvcchrg = m.srvcchrg * m.srvcchrg = sales.sa_invfee m.d_invfee = InvoicableSales.sa_invfee ENDIF mdate = DTOC(InvoicableSales.sa_date) IF (VAL(SYS(1)) - VAL(SYS(11, mdate))) > 360 m.d_renewal = '--- RENEWAL ---' ENDIF m.d_cuid = InvoicableSales.sa_cu_id m.d_prid = InvoicableSales.sa_pr_id m.d_salesid = InvoicableSales.sales_id m.d_startdt = InvoicableSales.sa_date m.d_enddt = InvoicableSales.sa_enddt m.d_linvdt = DATE() m.d_linvnum = InvoicableSales.sa_linvnum m.d_invcode = products.invcode thisform.cInvCode = products.invcode IF EMPTY(InvoicableSales.sa_finvdt) m.d_finvdt = DATE() ENDIF ENDPROC PROCEDURE getsubscriptionperiod PARAMETER billingmonth PRIVATE periods, begindate, enddate, billingmonth periods = thisform.CountBillingPeriods() * begindate = CTOD(ALLTRIM(STR(MONTH(billingmonth))) + "/01/" + ALLTRIM(STR(YEAR(billingmonth)))) begindate = DATE(YEAR(billingmonth), MONTH(billingmonth), 01) periods = (12/periods) - 1 enddate = GOMONTH(begindate, periods) m.d_subdate = ALLTRIM(CMONTH(begindate)) + " " + ALLTRIM(STR(YEAR(begindate))) + ; " - " + ALLTRIM(CMONTH(enddate)) + " " + ALLTRIM(STR(YEAR(enddate))) ENDPROC PROCEDURE getconversionrate PRIVATE rvalue rvalue = 1 IF NOT EMPTY(customer.cu_convrat) rvalue = convert.co_rate ENDIF RETURN(rvalue) ENDPROC PROCEDURE getremittoinfo PRIVATE cTmpInvNum, cInvSuffix, nRecordsRead * For progress thermometer _elibarcon = -1 _runmancon = 3 _iconpostion = 5 nRecordsRead = 0 cTmpInvNum = '' cInvSuffix = '' IF USED("inv_data") SELECT inv_data ELSE SELECT 0 USE (LOCFILE("inv_data.dbf","DBF","Where is inv_data ?")) EXCLUSIVE ; AGAIN ALIAS inv_data ENDIF IF USED("inv_head") SELECT inv_head ELSE SELECT 0 USE (LOCFILE("inv_head.dbf","DBF","Where is inv_head ?")) EXCLUSIVE ; AGAIN ALIAS inv_head ENDIF *Intialize progress thermometer DO therm WITH RECCOUNT(), nRecordsRead, 'Invoicing', 'Creating Invoice Number', '' SELECT inv_head GOTO TOP DO WHILE !EOF('inv_head') DO CASE CASE inv_head.h_remitto = "COM" cInvSuffix = "A" CASE inv_head.h_remitto = "REN" cInvSuffix = "R" CASE inv_head.h_remitto = "SOM" cInvSuffix = "S" CASE inv_head.h_remitto = 'MOM' OR ; inv_head.h_remitto = 'ENG' OR ; inv_head.h_remitto = 'AUD' OR ; inv_head.h_remitto = 'AUS' cInvSuffix = 'M' OTHERWISE cInvSuffix = 'Z' ENDCASE IF LEFT(inv_head.h_acctno, 1) = 'U' cInvSuffix = 'M' ENDIF cTmpInvNum = ALLTRIM(inv_head.h_invnum) REPLACE Inv_Head.h_invnum WITH cTmpInvNum + cInvSuffix nRecordsRead = nRecordsRead + 1 DO therm WITH RECCOUNT(), nRecordsRead, 'Invoicing', 'Creating Invoice Number', '' SELECT inv_data REPLACE ALL inv_data.d_invnum WITH cTmpInvNum + cInvSuffix FOR Inv_data.d_invnum = cTmpInvNum SELECT inv_head SKIP 1 IN inv_head ENDDO DO therm WITH RECCOUNT(), nRecordsRead, 'Invoicing', 'Creating Invoice Number', '' RETURN ENDPROC PROCEDURE closedatabases IF USED("InvoicableSales.dbf") SELECT InvoicableSales.dbf USE ENDIF IF USED("products") SELECT products USE ENDIF IF USED("customer") SELECT customer USE ENDIF IF USED("convert") SELECT CONVERT USE ENDIF IF USED("remitto") SELECT remitto USE ENDIF IF USED("inv_data") SELECT inv_data USE ENDIF IF USED("inv_head") SELECT inv_head USE ENDIF IF USED("sysinfo") SELECT sysinfo USE ENDIF IF USED("tmp_cust") SELECT tmp_cust USE ENDIF IF USED("tmp_serv") SELECT tmp_serv USE ENDIF ENDPROC PROCEDURE gettaxtotals * Calculate tax amount Private nTaxAmount, nRecordsRead, nTotalRecords nTaxAmount = 0 nTotalRecords = 0 nRecordsRead = 0 _elibarcon = -1 _runmancon = 3 _iconpostion = 5 SELECT Inv_head nTotalRecords = RECCOUNT() GOTO TOP DO therm WITH nTotalRecords, nRecordsRead, 'Invoicing', 'Calculating Tax Amount', '' DO WHILE NOT EOF('Inv_head') IF inv_head.h_taxrate > 0 nTaxAmount = ROUND((Inv_head.h_taxrate / 100) * Inv_head.h_TTLInv, 2) ELSE nTaxAmount = 0 ENDIF REPLACE Inv_head.h_TaxAmount WITH nTaxAmount SKIP 1 IN 'Inv_head' nRecordsRead = nRecordsRead + 1 DO therm WITH nTotalRecords, nRecordsRead, 'Invoicing', 'Calculating Tax Amount', '' ENDDO DO therm WITH nTotalRecords, nRecordsRead, 'Invoicing', 'Calculating Tax Amount', '' ENDPROC PROCEDURE gettotalextended * This procedure sums the line item $ amounts in inv_data. Processing the Inv_data file * is the slow way to do this since you could sum the same invoice multiple times.(Once * for each line item) But, it does check that all line items have a coresponding header record. PRIVATE nTotalLineAmount, cCurrentInvoiceNumber, nCurrentRecordNumber, nRecordsRead, nTotalRecords nTotalLineAmount = 0 cCurrentInvoiceAmount = '' nCurrentRecordNumber = 0 nTotalRecords = 0 nRecordsRead = 0 _elibarcon = -1 _runmancon = 3 _iconpostion = 5 SELECT Inv_head SET ORDER TO TAG h_invnum SELECT Inv_data nTotalRecords = RECCOUNT() GOTO TOP nCurrentRecordNumber = RECNO() DO therm WITH nTotalRecords, nRecordsRead, 'Invoicing', 'Totaling Invoice Line Items', '' DO WHILE NOT EOF('Inv_data') cCurrentInvoiceNumber = Inv_data.d_invnum SUM Inv_data.d_linettl FOR cCurrentInvoiceNumber = Inv_data.d_invnum TO nTotalLineAmount SELECT Inv_head SEEK cCurrentInvoiceNumber IF FOUND() REPLACE Inv_head.h_ttlinv WITH nTotalLineAMount ELSE MESSAGEBOX("No Invoice Header record was found for " + cCurrentInvoiceNumber,64,"Invoicing") ENDIF * This is because the sum function moves the record pointer to the EOF SELECT Inv_data GOTO nCurrentRecordNumber SKIP 1 IN 'Inv_data' nCurrentRecordNumber = RECNO() nRecordsRead = nRecordsRead + 1 DO therm WITH nTotalRecords, nRecordsRead, 'Invoicing', 'Totaling Invoice Line Items', '' ENDDO DO therm WITH nTotalRecords, nRecordsRead, 'Invoicing', 'Totaling Invoice Line Items', '' ENDPROC PROCEDURE gettotalinvoiceamount * Calculate total invoice amount Private nTotalInvoiceAmount, nTotalConvertAmount, nRecordsRead, nTotalRecords nTotalInvoiceAmount = 0 nTotalConvertAmount = 0 nTotalRecords = 0 nRecordsRead = 0 _elibarcon = -1 _runmancon = 3 _iconpostion = 5 SELECT Inv_head nTotalRecords = RECCOUNT() GOTO TOP DO therm WITH nTotalRecords, nRecordsRead, 'Invoicing', 'Calculating Total Invoice Amount', '' DO WHILE NOT EOF('Inv_head') nTotalInvoiceAmount = Inv_head.h_ttlinv + Inv_head.h_TaxAmount + Inv_head.h_ShipChg + Inv_head.h_SerChg nTotalCovertAmount = ROUND((nTotalInvoiceAmount * Inv_head.h_convrat), 4) REPLACE Inv_head.h_TotalExtended WITH nTotalInvoiceAmount Replace Inv_head.h_TTLConvertAmount WITH nTotalCovertAmount SKIP 1 IN 'Inv_head' nRecordsRead = nRecordsRead + 1 DO therm WITH nTotalRecords, nRecordsRead, 'Invoicing', 'Calculating Total Invoice Amount', '' ENDDO DO therm WITH nTotalRecords, nRecordsRead, 'Invoicing', 'Calculating Total Invoice Amount', '' ENDPROC PROCEDURE Init SET PATH TO PROGS, FORMS, LIBS, MENUS, DATA, ; REPORTS, INCLUDES, HELP, BITMAPS * ON ERROR DO progs\errhand WITH ; * ERROR( ), MESSAGE( ), MESSAGE(1), PROGRAM( ), LINENO( ), SELECT(), RECNO() * Build array for company codes * this.lIgnoreErrors = .t. SELECT Company.co_name, Company.company_id, Company.co_code; FROM trackerdb!company; ORDER BY Company.co_name; INTO ARRAY thisform.aCompanyCode USE ThisForm.cboCompanyCode.NumberOfElements = ALEN(thisform.aCompanyCode) ThisForm.chkCheckForProductUpdates.value = .T. ThisForm.cboCompanyCode.value = 'Baker & Taylor' ENDPROC   %ShbU CUTHISFORMRELEASEClick,11#) '%UF%C  ?R,Company is emptyUTHISFORMCCOMPANYTOINVOICE DOINVOICINGClick,11A1{)!Arial, 0, 9, 5, 15, 12, 32, 3, 0 Top = 89 Left = 24 AutoSize = .T. Caption = "Check for product updates" Value = 0 ControlSource = "lCheckForProductUpdates" Name = "chkCheckForProductUpdates"  frmInvoicingchkCheckForProductUpdatescheckbox..\libs\t_base.vcx t_checkboxeCaption = "Invoice What Company" Height = 16 Left = 60 Top = 209 Width = 144 Name = "T_label2"  frmInvoicingT_label2label..\libs\t_base.vcxt_labelqAlignment = 2 Caption = "Invoice What Month" Height = 16 Left = 204 Top = 7 Width = 162 Name = "T_label1"  frmInvoicingT_label1label..\libs\t_base.vcxt_labelColumnCount = (ALEN(aCompanyCode,2)) RowSourceType = 5 RowSource = "thisform.aCompanyCode" DisplayValue = ControlSource = "thisform.cCompanyToInvoice" FirstElement = 1 Height = 24 Left = 60 NumberOfElements = 0 Style = 2 Top = 228 Width = 228 Name = "cboCompanyCode"  frmInvoicingcboCompanyCodecombobox..\libs\t_base.vcx t_combobox.PROCEDURE Click thisform.release() ENDPROC VTop = 288 Left = 168 Height = 24 Width = 75 Caption = "\ ?T @ AT B8CT D %C A T EFGT HFIT JFKT LFMT NFOT PFQT RFST TFUF _?=C  Customer detail data not found0Errorx H Tc Ctmp_custNInvoice Extract'Matching sales and customer informationc Ctmp_custNInvoice Extract'Matching sales and customer informationUV RECS_READ _ELIBARCON _RUNMANCON _ICONPOSTIONINV_DATADISTINCTD_CUIDD_INVNUMTMPCUSTOMER CUSTOMER_ITMP_CUSTTHERMINV_HEADH_BNAMECU_BNAMEH_SNAMECU_SNAMEH_BADDR1 CU_BADDR1H_SADDR1 CU_SADDR1H_BADDR2 CU_BADDR2H_SADDR2 CU_SADDR2H_BADDR3 CU_BADDR3H_SADDR3 CU_SADDR3H_BCITYCU_BCITYH_SCITYCU_SCITYH_BSTATE CU_BSTATEH_SSTATE CU_SSTATE H_BCOUNTY CU_BCOUNTY H_SCOUNTY CU_SCOUNTYH_BZIPCU_BZIPH_SZIPCU_SZIPH_CU_ID CUSTOMER_IDH_LINVDTH_ACCTNO CU_ACCTNOH_REGION CU_REGIONH_MARKET CU_MARKET H_CONVRATTHISFORMGETCONVERSIONRATE H_CONVCTY CU_CONVRAT H_TAXRATE CU_TAXRATE H_SHIPCHG CU_SHIPCHG H_REMITTO CU_REMITTO H_INVCODECINVCODEH_INVNUMH_RNAMEREMITTORE_NAMEH_RADDR1RE_ADDR1H_RADDR2RE_ADDR2H_RADDR3RE_ADDR3H_RCITYRE_CITYH_RSTATERE_STATE H_RCOUNTY RE_COUNTRYH_RZIPRE_ZIP "R,:Getting Service ChargesMoInv_data1tmp\tmp_serv.dbf%Ctmp_servN& F)G((D_invnumtmp\tmp_serv.cdxG-($> R U DISTINCTINV_DATAD_CUIDD_INVNUMD_INVFEETMPINV_HEADTMP_SERVH_INVNUMALLH_SERCHGFOR. R,:Getting Invoice CodesIoInv_data1tmp\tmp_code.dbf%Ctmp_codeN& F)G((D_invnumtmp\tmp_code.cdxG-( $>  $>  R BUDISTINCTINV_DATAD_CUIDD_INVNUM D_INVCODED_LINVDTTMPINV_HEADTMP_CODEH_INVNUMALL H_INVCODEFORH_LINVDT5 T%a7T%a^T%aT%aT%aT%aT%a!T% aHT% aoT% aT% aT% aT BU PERIOD_CNTINVOICABLESALESSA_BJANSA_BFEBSA_BMARSA_BAPRSA_BMAYSA_BJUNSA_BJULSA_BAUGSA_BSEPSA_BOCTSA_BNOVSA_BDEC 5%CSs,纉330@ MS Sans Serif B z,t,El'Invoice Number:@ MS Sans Serif ABTl'3 @ MS Sans Serif AB?%@ @K! A99AA9999*C CC>  Enter starting invoice number.$Invoice Number Cannot Contain Blanks'N(";/CS33 B(.A1CLb癙. B(";癙.C9TC=TCC\gTC@L ####_TTAT -TC=TCC\gTC@L ####_TTT T % T u,BU V_INVPREV_INVNUMTHISFORMCCURRENTINVOICENUMBERINVNUM V_INVNAME PREV_CU_IDINVOICABLESALESSA_CU_ID LFIRSTINVOICECHEADERINVOICENUMBERCDETAILINVOICENUMBERCPREVIOUSINVOICENUMBER5 TTTTC TC Hy Th  T  TZ   T2 T H0case &sInvoiceMonth and empty(sa_finvdt)XT case &sInvoiceMonth and LEFT(sa_pr_id, 1) = "A" and lastinvdt <> thisform.cCurrentSystemDate and sa_linvdt = sa_finvdt and between(date()-sa_linvdt, 290,330)T case &sInvoiceMonth and LEFT(sa_pr_id, 1) = "A" and lastinvdt <> thisform.cCurrentSystemDate and sa_linvdt <> sa_finvdt and (date()- sa_linvdt) > BillPdLenT icase &sInvoiceMonthPlus2 and LEFT(sa_pr_id, 1) = "A" and lastinvdt <> thisform.cCurrentSystemDate=T Qcase &sInvoiceMonthPlus2 and LEFT(sa_pr_id, 1) = "S" and EMPTY(sa_finvdt)T  %-IF &sInvoiceMonth p%C =A TC %T l%C =Sh%C dT j$IF &sInvoiceMonthPlus2 = .T.E%C =ATC %T A%C =S=%C 9T %ENDIF (&sInvoiceMonthPlus2 = .T.) =ENDIF (&sInvoiceMonth if this month is the invoice month)  BURVALUE SINVOICEMONTHSINVOICEMONTHPLUS2BILLPDCT BILLPDLENTHISFORM LASTINVDT MTH_YR_FORMATINVOICABLESALES SA_LINVDTCOUNTBILLINGPERIODS DINVOICEDATEDINVOICEDATEPLUS2SA_PR_IDCCURRENTSYSTEMDATE`5TCTCd TT T  T  % T   T  %T  T  T C T%C @T TC*"%CC]gCC ]ghT --- RENEWAL ---T T T T T T C$T !T "#T$#%C%YT &C$U'DISCOUNT NUM_MONTHSTHISFORMCOUNTBILLINGPERIODSINVOICABLESALESSA_DISCD_DISC D_DESCRIPPRODUCTS DESCRIPTIOND_QTYSA_QTY SUBLENGTHD_LIST SA_SALESPR D_NETPRICE D_LINETTL SA_INVFEED_INVFEEMDATESA_DATE D_RENEWALD_CUIDSA_CU_IDD_PRIDSA_PR_ID D_SALESIDSALES_ID D_STARTDTD_ENDDTSA_ENDDTD_LINVDT D_LINVNUM SA_LINVNUM D_INVCODEINVCODECINVCODE SA_FINVDTD_FINVDT45TCTCCiCH$T  TC@T CC! CCCiZ - CC! CCCiZU BILLINGMONTHPERIODS BEGINDATEENDDATETHISFORMCOUNTBILLINGPERIODS D_SUBDATEH5 T%C 7T BURVALUECUSTOMER CU_CONVRATCONVERTCO_RATE5T T T T T T%Cinv_data|FF;QC inv_data.dbfDBFWhere is inv_data ?%Cinv_headF4F;QC inv_head.dbfDBFWhere is inv_head ?BCN InvoicingCreating Invoice NumberF#)+Cinv_head+ o H  COMTA  RENTR  SOMTSJ  MOM ENG  AUD  AUS sTM2TZ%C =UTMTC > TBCN InvoicingCreating Invoice NumberF">  F HBCN InvoicingCreating Invoice NumberBU CTMPINVNUM CINVSUFFIX NRECORDSREAD _ELIBARCON _RUNMANCON _ICONPOSTIONINV_DATAINV_HEADTHERM H_REMITTOH_ACCTNOH_INVNUMALLD_INVNUMFOR"%CInvoicableSales.dbf0 FQ%CproductsVFQ%Ccustomer|FQ%CconvertFQ%CremittoFQ%Cinv_dataFQ%Cinv_headFQ%Csysinfo7FQ%Ctmp_cust]F Q%Ctmp_servF QU INVOICABLESALESDBFPRODUCTSCUSTOMERCONVERTREMITTOINV_DATAINV_HEADSYSINFOTMP_CUSTTMP_SERV5 T T TT T TF TCN#)@ InvoicingCalculating Tax Amount+CInv_head+ %!TCd  T T> HInv_headT@ InvoicingCalculating Tax Amount@ InvoicingCalculating Tax AmountU NTAXAMOUNT NRECORDSREAD NTOTALRECORDS _ELIBARCON _RUNMANCON _ICONPOSTIONINV_HEADTHERM H_TAXRATEH_TTLINV H_TAXAMOUNT5 T T T T TT T TF G((h_invnumF TCN#) TCOE  InvoicingTotaling Invoice Line Items+CInv_data+ NT K ( F E %C4s> FC'No Invoice Header record was found for @ InvoicingxF #HInv_data TCOTE  InvoicingTotaling Invoice Line ItemsE  InvoicingTotaling Invoice Line ItemsUNTOTALLINEAMOUNTCCURRENTINVOICENUMBERNCURRENTRECORDNUMBER NRECORDSREAD NTOTALRECORDSCCURRENTINVOICEAMOUNT _ELIBARCON _RUNMANCON _ICONPOSTIONINV_HEADH_INVNUMINV_DATATHERMD_INVNUM D_LINETTLH_TTLINV 5 T T T TT T TF TCN#)J Invoicing Calculating Total Invoice Amount+CInv_head+ %T    T CT>> HInv_headTJ Invoicing Calculating Total Invoice AmountJ Invoicing Calculating Total Invoice AmountUNTOTALINVOICEAMOUNTNTOTALCONVERTAMOUNT NRECORDSREAD NTOTALRECORDS _ELIBARCON _RUNMANCON _ICONPOSTIONINV_HEADTHERMH_TTLINV H_TAXAMOUNT H_SHIPCHGH_SERCHGNTOTALCOVERTAMOUNT H_CONVRATH_TOTALEXTENDEDH_TTLCONVERTAMOUNTKG)(BPROGS, FORMS, LIBS, MENUS, DATA, REPORTS, INCLUDES, HELP, BITMAPSDotrackerdb!companyQT CT a!T Baker & TaylorU PROGSCOMPANYCO_NAME COMPANY_IDCO_CODE TRACKERDBTHISFORM ACOMPANYCODECBOCOMPANYCODENUMBEROFELEMENTSCHKCHECKFORPRODUCTUPDATESVALUEformatinvoicedate, doinvoicingr mth_yr_formatgetinvoicablesalesrecords opendatabases4getinvoicelineitemsgetinvoiceheaderinfogetservicechargesgetinvoicecodescountbillingperiodsgetinvoicenumberK selectinvoicelineitem$printinvoicelineitemV+getsubscriptionperiodS/getconversionrate[0getremittoinfo0closedatabases15 gettaxtotals,7gettotalextended9gettotalinvoiceamount =Init@1c211AAAA2q3raRA ABrA3"ArqArqAbqAbqAr11q11vArAQAQQAQa2sQqaC1ArQ!Q2qAArAqAAB4qbtQsu1qQqaq"!!!!!!!!!!!!!!!!!!!1!!!!!!2!!!!!!!!ArQQA2A23!r1CAQ4r1CCBQB2q"A!A!A!A!A!A!A!A!A!A!A!A4!9T r4RR!qAQAAB111AB3qqAv W AqBA!AAAAqAA!AAAQA4q"!CbAA?#A#"A"!!!!!!1"A2q1b!3q2A4rqArqA#rQqrrAA"B!r!rB"B2!AArqAArqAAbqAAbqAArqAArqAAbqAArqAArqAA3rQAArB5urrQRqaAsqQBQ42rQRrB3LB1w %B4,+I A@m h*4*, -//3?3:`;^G-GNOQ=QQQ6X[XZ@.Z]tF]di=d1hLhj)BJ