1:  LPARAMETERS cfile, olog
   2:   
   3:  LOCAL citems, cparts, citem, cpart, citemprev, calias, cfilen, cfilennew, nrec, nrecd, nafld, nprevsel, lcsubject, lcbody, lcbodyitems, lcbodyparts
   4:   
   5:  ccr= CHR(13)+CHR(10)
   6:   
   7:  nprevsel = SELECT()
   8:  cfile = LOWER(cfile)
   9:   
  10:  sns = ALLTRIM(SYS(2))
  11:  simpfile = "imppl"+sns+".dbf"
  12:  simpalias = "imppl"+sns
  13:  SET SAFETY OFF
  14:  SET DEFAULT TO SYS(2023)
  15:  IF FILE(simpfile)
  16:     IF USED(simpalias)
  17:        USE IN (simpalias)
  18:     ENDIF
  19:     DELETE FILE (simpfile)
  20:  ENDIF
  21:   
  22:  calias = SUBSTR(cfile, RAT('\',cfile)+1, RAT('.',cfile)- RAT('\',cfile)-1)
  23:  cfilenxls = calias + '.xls'
  24:  cfilep = JUSTPATH(cfile)
  25:  cpath=SET("PATH")
  26:  SET PATH TO (cfilep) ADDITIVE
  27:  IMPORT FROM (cfilenxls) TYPE XLS
  28:  SET PATH TO (cpath)
  29:   
  30:  cfilen = UPPER(calias) + '.dbf'
  31:  calias = CHRTRAN(ALLTRIM(ALIAS()),[-],[_])
  32:  IF EMPTY(calias)
  33:     calias = SUBSTR(cfile, RAT('\',cfile)+1, RAT('.',cfile)- RAT('\',cfile)-1)
  34:  ENDIF
  35:   
  36:  IF USED(calias)
  37:     USE IN (calias)
  38:  ENDIF
  39:   
  40:  RENAME (cfilen) TO (simpfile)
  41:   
  42:  USE (simpfile) EXCLUSIVE ALIAS (simpalias)
  43:  AFIELDS(afld, simpalias)
  44:  nafld=ALEN(afld,1)
  45:  IF nafld>1
  46:     olog.addmess("Please check XLS file; it seems that too many fields are present, Update abandoned!!!")
  47:     RETURN .F.
  48:  ENDIF
  49:  ALTER TABLE (simpalias) RENAME COLUMN &afld[1,1] TO 'item'
  50:  SELECT (simpalias)
  51:  GO TOP
  52:  DELETE NEXT 1
  53:  DELETE ALL FOR EMPTY(ALLTRIM(ITEM))
  54:  REPLACE ALL ITEM WITH ALLTRIM(ITEM)
  55:  PACK
  56:  nrec = RECCOUNT(simpalias)
  57:  SELECT DISTINCT ITEM FROM (simpalias) INTO CURSOR cstemp
  58:  nrecd = RECCOUNT('csTemp')
  59:  USE IN cstemp
  60:  IF nrecd = 0
  61:     olog.addmess('No items imported from XLS file. Update abandoned!!!')
  62:     RETURN .F.
  63:  ENDIF
  64:  IF nrec != nrecd
  65:     olog.addmess('XLS file contains duplicates. Update abandoned!!!')
  66:     RETURN .F.
  67:  ENDIF
  68:   
  69:  SELECT DISTINCT ITEM FROM (simpalias) INTO CURSOR csitems
  70:  SELECT csitems
  71:  lcsubject = "HarnParUpd: there are items not present in BomStructure !!!"
  72:  lcbody = "The following items are not present in BomStructure SQL Table:" + "<br/>" + ccr + "<br/>" + ccr
  73:  citems = ''
  74:  SCAN
  75:     citem = ALLTRIM(csitems.ITEM)
  76:     IF !omainform.misinsysprobom(citem)
  77:        citems = citems + IIF(!EMPTY(citems),',','') + ALLTRIM(citem)
  78:        lcbody = lcbody + "<br/>" + csitems.ITEM + "<br/>"+CHR(13)+CHR(10)
  79:     ENDIF
  80:  ENDSCAN
  81:  IF !EMPTY(citems)
  82:     olog.addmess('The following Items: ' + ALLTRIM(citems) + [ aren't in BomStructure SQL table. Update abandoned!!!])
  83:     lcbody = lcbody + ccr + ccr
  84:     omainform.osmtp.iwsendsmtp(cemailnotallitemsinsysprobom, lcsubject, lcbody, .T.)
  85:     RETURN .F.
  86:  ENDIF
  87:   
  88:  IF USED('csBomStru')
  89:   
  90:     IF USED('csMarkItemsList')
  91:        USE IN csmarkitemslist
  92:     ENDIF
  93:     SELECT DISTINCT parentpart AS ITEM FROM csbomstru INTO CURSOR csmarkitemslist
  94:     SELECT csmarkitemslist
  95:     SCAN
  96:        = omainform.obizobj.oharn_par.mupditemxxxx(ALLTRIM(csmarkitemslist.ITEM))
  97:     ENDSCAN
  98:     USE IN csmarkitemslist
  99:   
 100:     IF USED('csUpdList')
 101:        USE IN csupdlist
 102:     ENDIF
 103:     SELECT UPPER(parentpart) as parentpart, UPPER(part_no) as part_no, SUM(qty) AS qty FROM csbomstru WHERE ALLTRIM(route)="0" GROUP BY 1,2 ORDER BY 1,2 INTO CURSOR csupdlist
 104:     USE IN csbomstru
 105:     SELECT csupdlist
 106:     cparts = ""
 107:     cprevitem = ""
 108:     SCAN
 109:        citem = ALLTRIM(csupdlist.parentpart)
 110:        IF citem == cprevitem
 111:           cpart = ALLTRIM(csupdlist.part_no)
 112:           IF AT(cpart, cparts)=0
 113:              IF !omainform.misinpartlib(cpart)
 114:                 cparts = cparts + IIF(!EMPTY(cparts),',','') + ALLTRIM(cpart)
 115:              ENDIF
 116:           ENDIF
 117:        ELSE
 118:           IF !EMPTY(cprevitem)
 119:              IF !omainform.misinpartlib(cprevitem)
 120:                 lcmess = 'For StockCode: ' + cprevitem + ' Following Parts Must Be Added To The Part Library: ' + cprevitem + IIF(!EMPTY(cparts),',','') + ALLTRIM(cparts)
 121:              ELSE
 122:                 IF !EMPTY(cparts)
 123:                    lcmess = 'For StockCode: ' + cprevitem + ' Following Parts Must Be Added To The Part Library: ' + cparts
 124:                 ELSE
 125:                    lcmess = ""
 126:                 ENDIF
 127:              ENDIF
 128:              IF !EMPTY(lcmess)
 129:                 olog.addmess(lcmess)
 130:                 lcsubject = "Syspro Bom Export to FoxPro PL for " + cprevitem
 131:                 lcbody = "<br/>" + STRTRAN(lcmess,',',ccr) + ccr
 132:                 omainform.osmtp.iwsendsmtp(cemailmissingparts, lcsubject, lcbody, .T.)
 133:              ELSE
 134:                 nprevsel = SELECT()
 135:                 IF USED('csUpd')
 136:                    USE IN csupd
 137:                 ENDIF
 138:                 SELECT * FROM csupdlist WHERE ALLTRIM(parentpart)==cprevitem INTO CURSOR csupd
 139:                 SELECT csupd
 140:                 SCAN
 141:                    nrecno = 0
 142:                    cppart = ALLTRIM(csupd.parentpart)
 143:                    WITH omainform.obizobj.oharn_par
 144:                       .citem = cppart
 145:                       .cpart_no = ALLTRIM(csupd.part_no)
 146:                       .nqty = csupd.qty
 147:                       .calt_pt_for = ''
 148:                       .csub_part = ''
 149:                       .minsert(@nrecno)
 150:                    ENDWITH
 151:                    WITH omainform.obizobj.opllog
 152:                       .citem = cppart
 153:                       .cuserid = cuser
 154:                       .ddatechange = DATETIME()
 155:                       .cchangetype = 'U'
 156:                       .cchange = 'Updated Item ' + ALLTRIM(csupd.parentpart)+' Part No:'+ALLTRIM(csupd.part_no) + ' With data From BomStructure SQL table'
 157:                       .minsert()
 158:                    ENDWITH
 159:                 ENDSCAN
 160:                 olog.addmess('Update of [' + cppart + '] was successful.')
 161:                 SELECT (nprevsel)
 162:              ENDIF
 163:              cparts = ""
 164:           ENDIF
 165:           cprevitem = citem
 166:           cpart = ALLTRIM(csupdlist.part_no)
 167:           IF AT(cpart, cparts)=0
 168:              IF !omainform.misinpartlib(cpart)
 169:                 cparts = cparts + IIF(!EMPTY(cparts),',','') + ALLTRIM(cpart)
 170:              ENDIF
 171:           ENDIF
 172:        ENDIF
 173:     ENDSCAN
 174:     IF !omainform.misinpartlib(cprevitem)
 175:        lcmess = 'Following Stockodes Must Be Added To The Part Library: ' + cprevitem + IIF(!EMPTY(cparts),',','') + ALLTRIM(cparts)
 176:     ELSE
 177:        IF !EMPTY(cparts)
 178:           lcmess = 'Following Stockodes Must Be Added To The Part Library: ' + cparts
 179:        ELSE
 180:           lcmess = ""
 181:        ENDIF
 182:     ENDIF
 183:     IF !EMPTY(lcmess)
 184:        olog.addmess(lcmess)
 185:        lcsubject = "Syspro Bom Export to FoxPro PL for " + cprevitem
 186:        lcbody = "<br/>" + STRTRAN(lcmess,',',ccr) + ccr
 187:        omainform.osmtp.iwsendsmtp(cemailmissingparts, lcsubject, lcbody, .T.)
 188:     ELSE
 189:        nprevsel = SELECT()
 190:        IF USED('csUpd')
 191:           USE IN csupd
 192:        ENDIF
 193:        SELECT * FROM csupdlist WHERE ALLTRIM(parentpart)==cprevitem INTO CURSOR csupd
 194:        SELECT csupd
 195:        SCAN
 196:           nrecno = 0
 197:           cppart = ALLTRIM(csupd.parentpart)
 198:           WITH omainform.obizobj.oharn_par
 199:              .citem = cppart
 200:              .cpart_no = ALLTRIM(csupd.part_no)
 201:              .nqty = csupd.qty
 202:              .calt_pt_for = ''
 203:              .csub_part = ''
 204:              .minsert(@nrecno)
 205:           ENDWITH
 206:           WITH omainform.obizobj.opllog
 207:              .citem = cppart
 208:              .cuserid = cuser
 209:              .ddatechange = DATETIME()
 210:              .cchangetype = 'U'
 211:              .cchange = 'Updated Item ' + ALLTRIM(csupd.parentpart)+' Part No:'+ALLTRIM(csupd.part_no) + ' With data From BomStructure SQL table'
 212:              .minsert()
 213:           ENDWITH
 214:        ENDSCAN
 215:        olog.addmess('Update of [' + cppart + '] was successful.')
 216:        SELECT (nprevsel)
 217:     ENDIF
 218:  ENDIF
 219:   
 220:  IF USED(simpalias)
 221:     USE IN (simpalias)
 222:  ENDIF
 223:   
 224:  DELETE FILE (simpfile)
 225:   
 226:  SELECT (nprevsel)