DVERSION = 3.00dataenvironmentdataenvironmentDataenvironmentJLeft = 1 Top = 220 Width = 520 Height = 200 Name = "Dataenvironment" 11t_form..\libs\t_base.vcxform frmInvSummarygDoCreate = .T. AutoCenter = .T. Caption = "Invoice Summary" WindowType = 0 Name = "frmInvSummary" ,PROCEDURE updatesalesdb _elibarcon = -1 _runmancon = 3 _iconpostion = 5 nRecordsRead = 0 nRecordCount = 0 * Update sales and customer file 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("Sales") SELECT Sales ELSE SELECT 0 USE (LOCFILE("sales.dbf","DBF","Where is Sales ?")) EXCLUSIVE ; AGAIN ALIAS sales ENDIF SET ORDER TO TAG sales_id SELECT inv_data nRecordCount = RECCOUNT() GOTO TOP DO therm WITH nRecordCount, nRecordsRead, 'Invoicing Update', 'Updating Sales File', '' * Update the Sales file DO WHILE NOT EOF('inv_data') SELECT sales SEEK inv_data.d_SalesID IF FOUND() REPLACE sales.sa_linvdt WITH Inv_data.d_linvdt REPLACE sales.sa_linvnum WITH Inv_data.d_invnum IF EMPTY(sa_finvdt) REPLACE sales.sa_finvdt WITH inv_data.d_finvdt ENDIF ELSE MESSAGEBOX(inv_data.d_SalesID + " Not found in invoice update",16,"Sales File Update") ENDIF SKIP 1 IN inv_data nRecordsRead = nRecordsRead + 1 DO therm WITH nRecordCount, nRecordsRead, 'Invoicing Update', 'Updating Sales File', '' ENDDO DO therm WITH nRecordCount, nRecordsRead, 'Invoicing Update', 'Updating Sales File', '' SELECT Sales USE SELECT Inv_data USE *DO Update_AR *CLOSE ALL *SET TALK ON ENDPROC PROCEDURE updateinvsummarydb SELECT Inv_data.d_cuid, Inv_data.d_salesid, Inv_head.h_invnum,; Inv_head.h_linvdt, Inv_head.h_totalextended; FROM trackerdb!inv_head LEFT OUTER JOIN trackerdb!inv_data ; ON Inv_head.h_invnum = Inv_data.d_invnum; INTO CURSOR InvTmp SELECT 0 USE TrackerDB!Inv_Summary ; ALIAS Inv_Summary SELECT InvTmp GOTO TOP * INSERT INTO employee (emp_no, fname, lname, officeno) ; * VALUES (3022, "John", "Smith", 2101) DO WHILE !EOF('InvTmp') INSERT INTO Inv_Summary (Customer_id, ; Sales_id, ; inv_number, ; inv_date, ; inv_amount) ; VALUES ; (InvTmp.d_cuid, ; InvTmp.d_SalesID, ; InvTmp.h_Invnum, ; InvTmp.h_LInvDT, ; InvTmp.h_TotalExtended) SKIP 1 in InvTmp ENDDO SELECT InvTmp USE SELECT Inv_Summary USE SELECT inv_data USE SELECT inv_head USE ENDPROC PROCEDURE updatear PRIVATE invoice_cnt, shipping_chg, tax_amt, invoice_amt, nSrvCntr, nInvCode * Set up variables invoice_cnt = 0 shipping_chg = 0 tax_amt = 0 invoice_amt = 0 nRecordsRead = 0 nRecordCount = 0 DIMENSION aSrv_cntr(1) DIMENSION aInvCode(1) nSrvCntr = 0 nInvCode = 0 SET TALK OFF SELECT 0 USE trackerdb!recd_ar ALIAS recd_ar SET ORDER TO TAG Drab_regn IF RECCOUNT('recd_ar') > 0 outbuff = FCREATE('finance\invoice.dat') IF outbuff < 0 MESSAGEBOX('Could not open Invoice.dat file',16,"Error") CLOSE ALL SET TALK ON RETURN ENDIF && Gather unique service center codes SELECT DISTINCT Recd_ar.drab_regn; FROM Recd_ar; ORDER BY Recd_ar.drab_regn; INTO ARRAY aSrv_cntr nSrvCntr = ALEN(aSrv_cntr) && Gather unique invoice codes SELECT DISTINCT Recd_ar.drab_type; FROM Recd_ar; ORDER BY Recd_ar.drab_type; INTO ARRAY aInvCode nInvCode = ALEN(aInvCode) FOR i = 1 TO nSrvCntr FOR r = 1 TO nInvCode SELECT recd_ar COUNT FOR recd_ar.drab_regn = aSrv_cntr(i) AND ; recd_ar.drab_type = aInvCode(r) TO invoice_cnt IF invoice_cnt > 0 thisform.bld_recd_bt(aSrv_cntr(i), aInvCode(r)) ENDIF ENDFOR ENDFOR ENDIF USE = FCLOSE(outbuff) SET TALK ON RETURN ENDPROC PROCEDURE bld_recd_ar *! This procedure is used to format all the fields for an invoice record to be *! written to the invoice.dat file. Fromated fields are place in the temporary *! dbf file named recd_ar. *!***************************************************************************** _elibarcon = -1 _runmancon = 3 _iconpostion = 5 nRecordsRead = 0 nrecordCount = 0 SELECT 0 USE trackerdb!inv_head ALIAS inv_head nRecordCount = RECCOUNT() GOTO TOP SELECT 0 USE trackerdb!recd_ar ALIAS recd_ar EXCLUSIVE IF RECCOUNT() > 0 DELETE ALL PACK ENDIF DO therm WITH nRecordCount, nRecordsRead, 'AR File Creation', 'Building AR File', '' DO WHILE NOT EOF('inv_head') INSERT INTO recd_ar ; (dra_type, ; drab_type, ; drab_regn, ; drab_yyddd, ; dra_invnum, ; dra_accpre, ; dra_accnum, ; dra_ponum, ; dra_bd_mm, ; dra_bd_dd, ; dra_bd_yy, ; dra_ship, ; dra_tax, ; dra_invamt); VALUES ; ('AR' , ; inv_head.h_invcode , ; inv_head.h_region , ; thisform.yyddd(inv_head.h_linvdt), ; inv_head.h_invnum, ; LEFT(inv_head.h_acctno, 1), ; RIGHT(inv_head.h_acctno, 6), ; inv_head.h_ponum, ; PADL(ALLTRIM(STR(MONTH(inv_head.h_linvdt))), 2, '0') , ; PADL(ALLTRIM(STR(DAY(inv_head.h_linvdt))), 2, '0') , ; PADL(RIGHT(ALLTRIM(STR(YEAR(inv_head.h_linvdt))), 2), 2, '0') , ; PADL(thisform.z_fill(ALLTRIM(STR(inv_head.h_shipchg, 11, 2))), 11, '0'), ; PADL(thisform.z_fill(ALLTRIM(STR(inv_head.h_taxamount, 11, 2))), 8, '0') , ; PADL(thisform.z_fill(ALLTRIM(STR(inv_head.h_totalextended, 11, 2))), 11, '0')) SKIP 1 IN inv_head nRecordsRead = nRecordsRead + 1 DO therm WITH nRecordCount, nRecordsRead, 'AR File Creation', 'Building AR File', '' ENDDO DO therm WITH nRecordCount, nRecordsRead, 'AR File Creation', 'Building AR File', '' INDEX ON drab_regn + drab_type TAG Drab_regn ADDITIVE SELECT Inv_head USE Select recd_ar USE ENDPROC PROCEDURE bld_recd_bt PARAMETERS srv, invcode PRIVATE srv, srvfile, invcode && Calculate totals for selected service center and invoice code. SELECT recd_ar SUM VAL(recd_ar.dra_ship) FOR recd_ar.drab_regn = srv AND ; recd_ar.drab_type = invcode TO shipping_chg SUM VAL(recd_ar.dra_tax) FOR recd_ar.drab_regn = srv AND ; recd_ar.drab_type = invcode TO tax_amt SUM VAL(recd_ar.dra_invamt) FOR recd_ar.drab_regn = srv AND ; recd_ar.drab_type = invcode TO invoice_amt set order to drab_regn && Scan for invoice records for correct service center and invoice code && and write to invoice.dat file. SCAN FOR recd_ar.drab_regn = srv AND recd_ar.drab_type = invcode SCATTER MEMVAR = FWRITE(outbuff, 'AR') = FWRITE(outbuff, drab_type) = FWRITE(outbuff, drab_regn) = FWRITE(outbuff, drab_yyddd) = FWRITE(outbuff, dra_invnum) = FWRITE(outbuff, dra_accpre) = FWRITE(outbuff, dra_accnum) = FWRITE(outbuff, dra_ponum) = FWRITE(outbuff, dra_bd_mm) = FWRITE(outbuff, dra_bd_dd) = FWRITE(outbuff, dra_bd_yy) = FWRITE(outbuff, dra_ship) = FWRITE(outbuff, dra_tax) = FWRITE(outbuff,dra_invamt) = FPUT(outbuff, filler) ENDSCAN && Write totals for service center and invoice code to invoice.dat file = FWRITE(outbuff, 'AR') = FWRITE(outbuff, invcode) = FWRITE(outbuff, srv) = FWRITE(outbuff, thisform.yyddd(DATE())) = FWRITE(outbuff, PADL(thisform.z_fill(ALLTRIM(STR(invoice_cnt, 5, 0))), 5, '0')) = FWRITE(outbuff, PADL(thisform.z_fill(ALLTRIM(STR(shipping_chg, 13, 0))), 13, '0')) = FWRITE(outbuff, PADL(thisform.z_fill(ALLTRIM(STR(tax_amt, 13, 0))), 13, '0')) = FWRITE(outbuff, PADL(thisform.z_fill(ALLTRIM(STR(invoice_amt, 13, 0))), 13, '0')) = FPUTS(outbuff, SPACE(26)) RETURN ENDPROC PROCEDURE yyddd PARAMETERS cdate PRIVATE yyyy, ddd, rvalue rvalue = '' yyyy = YEAR(cdate) ddd = cdate - DATE(yyyy, 01, 01) rvalue = RIGHT(STR(yyyy, 4), 2) + PADL(ALLTRIM(STR(ddd)), 3, '0') RETURN(rvalue) ENDPROC PROCEDURE z_fill PARAMETER cInValue PRIVATE rvalue, cInValue rvalue = cInValue IF AT('.', rvalue) > 0 rvalue = STRTRAN(rvalue, '.') ENDIF RETURN(rvalue) ENDPROC PROCEDURE Init DODEFAULT() IF TYPE("oApp") <> "O" SET PATH TO PROGS, FORMS, LIBS, MENUS, DATA, ; REPORTS, INCLUDES, HELP, BITMAPS ENDIF ENDPROC  %Pe_U UTHISFORMRELEASEClick,12#) =%!R0Up%z%CJYou are about to update the Sales Files. Are you sure you want to do this?$Sales Database Updatexb%C2Have done a backup? This process is not reversibleSales Database Updatex %H  TC@^%i U THISFORM CHKSALESFILEVALUE UPDATESALESDB CHKARFILE BLD_RECD_ARUPDATEAR FORECOLORCHKUPDATESUMMARYUPDATEINVSUMMARYDBClick,14!AAA2B2A1j)!Arial, 0, 9, 5, 15, 12, 32, 3, 0 .PROCEDURE Click thisform.release ENDPROC ;Top = 216 Left = 192 Caption = "Exit" Name = "cmdExit"  frmInvSummarycmdExit commandbutton..\libs\t_base.vcxt_commandbuttonuPROCEDURE Click #DEFINE Yes 6 #DEFINE No 7 IF ThisForm.chkSalesFile.value IF MESSAGEBOX("You are about to update the Sales Files. Are you sure you want to do this?",292,"Sales Database Update")= Yes IF MESSAGEBOX("Have done a backup? This process is not reversible",260, "Sales Database Update") = Yes thisform.UpdateSalesDB ENDIF ENDIF ENDIF IF ThisForm.chkARFile.value Thisform.bld_recd_ar thisform.updatear ThisForm.chkARFile.forecolor = RGB(0,128,64) *ThisForm.chkARFile.forecolor = "0,128,64" ENDIF IF ThisForm.chkUpdateSummary.value Thisform.UpdateInvSummaryDB ENDIF ENDPROC BTop = 216 Left = 72 Caption = "Continue" Name = "cmdContinue"  frmInvSummary cmdContinue commandbutton..\libs\t_base.vcxt_commandbuttonTop = 96 Left = 24 Height = 17 Width = 216 Caption = "Update Invoice Summary Database" Value = .F. Name = "chkUpdateSummary"  frmInvSummarychkUpdateSummarycheckbox..\libs\t_base.vcx t_checkboxTop = 60 Left = 24 Height = 17 Width = 144 Caption = "Update AR File" Value = .F. ForeColor = 0,0,0 Name = "chkARFile"  frmInvSummary chkARFilecheckbox..\libs\t_base.vcx t_checkboxvTop = 24 Left = 24 Height = 17 Width = 168 Caption = "Update Sales Database" Value = .F. Name = "chkSalesFile"  frmInvSummary chkSalesFilecheckbox..\libs\t_base.vcx t_checkboxd*updatesalesdb *updateinvsummarydb *updatear *bld_recd_ar *bld_recd_bt *yyddd *z_fill p WW%UT T T T T%Cinv_data`FF;QC inv_data.dbfDBFWhere is inv_data ?%CSalesFF5QC sales.dbfDBFWhere is Sales ?G((sales_idF TCN#)DInvoicing UpdateUpdating Sales File+Cinv_data+ F E %C4>  >  %C>RFC  Not found in invoice updateSales File Updatex HTDInvoicing UpdateUpdating Sales FileDInvoicing UpdateUpdating Sales FileFQFQU _ELIBARCON _RUNMANCON _ICONPOSTION NRECORDSREAD NRECORDCOUNTINV_DATASALESSALES_IDTHERM D_SALESID SA_LINVDTD_LINVDT SA_LINVNUMD_INVNUM SA_FINVDTD_FINVDTZxotrackerdb!inv_headXtrackerdb!inv_data InvTmpF QTrackerDB!Inv_Summary F #)+CInvTmp+ 'Wr Inv_Summary         H F QF QFQFQUINV_DATAD_CUID D_SALESIDINV_HEADH_INVNUMH_LINVDTH_TOTALEXTENDED TRACKERDBD_INVNUMINVTMP INV_SUMMARY CUSTOMER_IDSALES_ID INV_NUMBERINV_DATE INV_AMOUNT5 T T T T T T    T TG2FQtrackerdb!recd_ar G(( Drab_regn%Crecd_arNw"T Cfinance\invoice.dat%  k6CCould not open Invoice.dat fileErrorxG2 B&oRecd_ar TC&oRecd_ar   TC (s(oF - C C  (%kCCC Q C G2 BU INVOICE_CNT SHIPPING_CHGTAX_AMT INVOICE_AMTNSRVCNTRNINVCODE NRECORDSREAD NRECORDCOUNT ASRV_CNTRAINVCODE TRACKERDBRECD_AR DRAB_REGNOUTBUFFDISTINCT DRAB_TYPEIRTHISFORM BLD_RECD_BT<T T T T TFQtrackerdb!inv_head TCN#)FQtrackerdb!recd_ar%CN3A AR File CreationBuilding AR File+Cinv_head+ Xrrecd_ar    ARCC=CRCCCCHZ0CCCC%Z0CCCCCiZR0CCCC! Z  0CCCC" Z 0CCCC# Z  0 HTA AR File CreationBuilding AR FileA AR File CreationBuilding AR File&  FQFQU$ _ELIBARCON _RUNMANCON _ICONPOSTION NRECORDSREAD NRECORDCOUNT TRACKERDBINV_HEADRECD_ARALLTHERMDRA_TYPE DRAB_TYPE DRAB_REGN DRAB_YYDDD DRA_INVNUM DRA_ACCPRE DRA_ACCNUM DRA_PONUM DRA_BD_MM DRA_BD_DD DRA_BD_YYDRA_SHIPDRA_TAX DRA_INVAMT H_INVCODEH_REGIONTHISFORMYYDDDH_LINVDTH_INVNUMH_ACCTNOH_PONUMZ_FILL H_SHIPCHG H_TAXAMOUNTH_TOTALEXTENDED 45F-K  (Cg-K  ( Cg-K  ( C gG(( drab_regn#~  ^C ARC C C C C C C C C C C C C C C ARC C C CC$(C CCCCZ0(C CCCC Z 0(C CCCC  Z 0(C CCCC  Z 0C CXBUSRVINVCODESRVFILERECD_ARDRA_SHIP DRAB_REGN DRAB_TYPE SHIPPING_CHGDRA_TAXTAX_AMT DRA_INVAMT INVOICE_AMTOUTBUFF DRAB_YYDDD DRA_INVNUM DRA_ACCPRE DRA_ACCNUM DRA_PONUM DRA_BD_MM DRA_BD_DD DRA_BD_YYFILLERTHISFORMYYDDDZ_FILL INVOICE_CNT45 TTCiTC$(TCCZRCCCZ0 BUCDATEYYYYDDDRVALUEZ4 5 T%C.ITC. BUCINVALUERVALUEs C%CoAppbOlKG)(BPROGS, FORMS, LIBS, MENUS, DATA, REPORTS, INCLUDES, HELP, BITMAPSUPROGS updatesalesdb,updateinvsummarydbupdatear bld_recd_arp bld_recd_bt5yydddz_fillInit1sqABqRArQBq111AaAABBrAqA:rQu{ArAqAqAqA2b!"aQaAAffrqqAAAAAaB3QQAAB2rAqA2s"3QAAA3r3qr1A3A1`-E? oi +9!G)W