#!/usr/bin/php
<?php
echo "truncate cc;
truncate inv;
truncate clockin;
truncate calllogs;
truncate ccoffline;
truncate myorder;
truncate detailorders;
truncate gift;
truncate giftlog;
truncate myuser;
truncate myuserinfo;
truncate category;
truncate item;
truncate coption;
truncate myoptions;
truncate itemoption;
truncate seat;
truncate happyhour;
truncate hisinv;
truncate intels;
truncate myday;
truncate myalert;
truncate online;
truncate onlinemsg;
truncate sms;
truncate review;
truncate upsale;
insert into myuser set uid=1,gid=1,active=1,uname='kwickpos',upass='3309';
insert into myuser set uid=2,gid=1,active=1,uname='jinpos',upass='jin123';
insert into myuser set uid=10,gid=1,active=1,uname='boss',upass='000';
insert into myuser set uid=11,gid=2,active=1,uname='manager',upass='111';
insert into myuser set uid=12,gid=3,active=1,uname='cashier',upass='222';
insert into myuser set uid=13,gid=4,active=1,uname='server',upass='333';
delete from customerinfo where cid > 100;
";
$users['kwickpos']=1;
$users['jinpos']=1;
$users['boss']=1;
$users['manager']=1;
$users['cashier']=1;
$users['server']=1;
$passs['3309']=1;
$passs['jin123']=1;
$passs['000']=1;
$passs['111']=1;
$passs['222']=1;
$passs['333']=1;

$file=$argv[1];
$out=preg_split("/\n/",`mdb-export $file GeneralSettings`);
$csv=preg_split("/,/",$out[1]);

$name=$csv[0];
$address=$csv[2];
$zip=$csv[3];
$tax=sprintf('%.04f',trim($csv[22],'"')/100);
$phone_p=trim($csv[84],'"');
$phone_f=trim($csv[83],'"');
$phone_f=preg_replace("/$phone_p/",'',$phone_f);

$q="update mystore set store_name='$name',store_address1='$address',store_zip='$zip',store_tax=$tax,store_phoneprefix='$phone_p',store_phone='$phone_f',fix_theme=4,store_submenu=8,store_seat=0,seatmode=0,store_fontsize=100";
$q=preg_replace('/\"/','',$q);
echo "$q;\n";

$cat=array();
$menu=array();
$out=preg_split("/\n/",`mdb-export $file MenuGroups`);
foreach ( $out as $k=>$v ) {
	if ( $k == 0 || $v == '' ) continue;
	$csv=preg_split("/,/",$v);
	$cid=$csv[0];
	$cname=$csv[1];
	$did=$csv[2];
	$live=trim($csv[3],'"');
	if ( $live == 1 ) continue;
	$cat[$cid]=array($cname,$did);
}
foreach ( $cat as $k=>$v ) {
	$n=$v[0];
	$d=$v[1];
	$q="insert into category set id=$k,display_id=$d,category='$n'";
	$q=preg_replace('/\"/','',$q);
	echo "$q;\n";
}

#MenuItemID,MenuItemText,MenuCategoryID,MenuGroupID,DisplayIndex,DefaultUnitPrice,6MenuItemDescription,MenuItemNotification,8MenuItemInActive,MenuItemInStock,10MenuItemTaxable,MenuItemDiscountable,MenuItemType,MenuItemPopUpHeaderID,MenuItemPopUpChoiceText,15HasModifierPopUps,16SecLangMenuItemText,SecLangPopUpChoiceText
$items=array();
$out=preg_split("/\n/",`mdb-export $file MenuItems`);
foreach ( $out as $k=>$v ) {
	if ( $k == 0 || $v == '' ) continue;
	$csv=preg_split("/,/",$v);
	//$iid=trim($csv[0],'"');
	//$did=trim($csv[4],'"')+2;
	//$iname=$csv[1];
	//$cname=$csv[16];
	$cid=trim($csv[3],'"');
	$items[$cid][]=$csv;
}

ksort($items);

foreach ( $items as $cid => $item ) {
   $mdid=0;
   $dids=array();
   foreach ( $item as $k=>$csv ) {
	$iid=trim($csv[0],'"');
	$did=trim($csv[4],'"');
	if ( $did > $mdid ) $mdid=$did;
	$dids[$did]=1;
	$iname=$csv[1];
	$cname=$csv[16];
	$cid=trim($csv[3],'"');
	$price=sprintf('%.2f',$csv[5]);
	$desc=$csv[6];
	if ( trim($csv[8],'"') == 0 ) $live=1;
	else $live=0;
	$tax=$csv[10];
	$dis=$csv[11];
	$mtype=trim($csv[12],'"');
	if ( $live == 0 && $mtype > 1 ) $live=1;

	$piid=trim($csv[13],'"');
	$weight=trim($csv[39],'"');
	$unit=0;
	if ( $weight == 1 ) $unit=2;
	$open=trim($csv[70],'"');
	if ( $open == 1 ) $unit=7;
	$open2=trim($csv[71],'"');
	if ( $open2 == 1 ) $unit=6;

	$ebt=$csv[28];
	$p0=sprintf('%.2f',$csv[33]);
	$pp=sprintf('%.2f',$csv[34]);
	$p1=sprintf('%.2f',$csv[35]);
	$p2=sprintf('%.2f',$csv[36]);
	$p3=sprintf('%.2f',$csv[37]);
	if ( $p1 == $price ) $p1=0;
	if ( $p2 == $price ) $p2=0;
	if ( $p3 == $price ) $p3=0;
	$oids=0;
	if ( $piid != '' ) {
		$q="insert into itemoption set item_id=$piid, name='$iname',ch_name='$cname',description='$desc',price='$price'";
		$q=preg_replace('/\"/','',$q);
		echo "$q;\n";
		$oids=1;
		continue;
	}
	if ( $iid > 0 && isset($cat[$cid]) ) {
		$display=$did;
		$q="insert into item set item_id=$iid, display_id=$display,name='$iname',c_name='$cname',description='$desc',price='$price',price2='$p1',price3='$p2',price4='$p3',category_id=$cid,item_live=$live,item_taxable=$tax,item_discountable=$dis,option_ids=$oids,item_unit=$unit";
		$q=preg_replace('/\"/','',$q);
		echo "$q;\n";
	} 
    }
    for($i=1;$i<$mdid;$i++) {
	if ( !isset($dids[$i]) ) {
		$q="INSERT INTO item SET display_id=$i,name='',price=0,category_id=$cid";
		echo "$q;\n";
	}
    }
}


#EmployeeID,FirstName,MiddleInitial,LastName,SocialSecurityNumber,MailingAddress,MailingZipCode,DateHired,DateReleased,EmployeeInActive,JobTitleID,SecurityLevel,AccessCode
$out=preg_split("/\n/",`mdb-export $file EmployeeFiles`);
foreach ( $out as $k=>$v ) {
	if ( $k == 0 || $v == '' ) continue;
	$csv=preg_split("/,/",$v);
	$eid=$csv[0];
	$ename=$csv[2];
	if ( $csv[3] != '' ) $ename.=$csv[3];

	$grp=3;
	$egrp=$csv[11];
	if ( preg_match('/5/',$egrp)) $grp=1;
	if ( preg_match('/4/',$egrp)) $grp=2;

	$epin=$csv[12];
	if ( $epin != '' ) {
		if ( isset($users[$ename]) ) $ename.='2';
		if ( isset($passs[$epin]) ) $epin.='0';
		$users[$ename]=1;
		$passs[$epin]=1;

		$q="insert into myuser set gid=$grp, uname='$ename',upass='$epin',active=1";
		$q=preg_replace('/\"/','',$q);
		echo "$q;\n";
	}
}

#GiftCertificateID,IssuedDateTime,InternalSequenceNumber,EmployeeID,IssuedToName,PresentedByName,IssuedAmount,ExpirationDate,GiftCertificateStatus,GCVoidReason,ScanCode
$gift=array();
$id2g=array();

$out=preg_split("/\n/",`mdb-export $file GiftCertificates`);
foreach ( $out as $k=>$v ) {
	if ( $k == 0 || $v == '' ) continue;
	$csv=preg_split("/,/",$v);
	$id=$csv[0];
	$date=$csv[1];
	$gid=$csv[2];
	$gid=preg_replace('/\"/','',$gid);
	$id2g[$id]=$gid;

	$amt=sprintf('%.2f',$csv[6]);
	$code=$csv[10];
	$gift[$gid]=array('date'=>$date,'oamount'=>$amt,'vamount'=>$amt);
	//echo "$id $date $gid $name $code\n";
}

#root@kpdc1:~# mdb-export /tmp/basil.mdb GiftCertificateUsages
#GiftCertificateID,OrderID,GiftCertificateAmountUsed,UsageDateTime,CashierID,NonCashierEmployeeID,EmployeeComp,AutoID,EditTimestamp,RemoteSiteNumber,RemoteOrigRowID,GlobalID,RowVer,SynchVer,StoreNumber,EDCTransID,HQRowID,LastRowHash,RowOwner,RowGUID
$out=preg_split("/\n/",`mdb-export $file GiftCertificateUsages`);
foreach ( $out as $k=>$v ) {
	if ( $k == 0 || $v == '' ) continue;
	$csv=preg_split("/,/",$v);
	$id=$csv[0];
	$id=preg_replace('/\"/','',$id);
	$amt=$csv[2];
	if ( isset($id2g[$id]) && $id2g[$id] != '' ) {
		$gid=$id2g[$id];
		if ( isset($gift[$gid]) ) $gift[$gid]['vamount']-=$amt;
	}
}

foreach ( $gift as $k=>$v ) {
	$date=$v['date'];
	$va=$v['vamount'];
	$oa=$v['oamount'];
	$k=preg_replace('/\"/','',$k);
	if ( is_numeric($k) && preg_match('/\d{4,}$/',$k) && $oa >= 0 && $va >= 0 ) {
		$q="insert into gift set giftid=$k,oamount=$oa,vamount=$va,date='$date',bdate='$date'";
		$q=preg_replace('/\"/','',$q);
		echo "$q;\n";
	}
}


#root@kpdc1:~# mdb-export /tmp/basil.mdb MenuModifiers |less
#MenuModifierID,MenuModifierText,AdditionalCost,MenuModifierInActive,SecLangModifierText,PictureName,ShowCaption,PizzaCrust,PizzaTopping,BarMixer,EditTimestamp,RemoteSiteNumber,RemoteOrigRowID,GlobalID,RowVer,SynchVer,StoreNumber,PizzaSize,ScreenName,ScreenAltName,ReceiptName,ReceiptAltName,ChitName,ChitAltName,PizzaSauce,LCPLU,ActionType,ActionBaseModifierID,HQRowID,LastRowHash,RowOwner,RowGUID
$i=0;
$seen=array();
$out=preg_split("/\n/",`mdb-export $file MenuModifiers`);
foreach ( $out as $k=>$v ) {
	if ( $k == 0 || $v == '' ) continue;
	$csv=preg_split("/,/",$v);
	$id=$csv[0];
	$name=trim($csv[1],'"');
	$name=preg_replace('/No /i','',$name);
	$name=preg_replace('/Add /i','',$name);
	$name=preg_replace('/Extra /i','',$name);
	$name=preg_replace('/Exchange /i','',$name);
	$name=preg_replace('/More /i','',$name);
	$name=preg_replace('/Less /i','',$name);
	$name=preg_replace('/Only /i','',$name);
	$name=preg_replace('/ on the side/i','',$name);
	$name=ucfirst(strtolower($name));
	if ( preg_match('/\W/',$name) ) continue;
	if ( preg_match('/\s+$/',$name) ) continue;
	if ( preg_match('/\w{1,2}\s+/',$name) ) continue;

	if ( isset($seen[$name]) ) continue;
	else $seen[$name]=1;

	$price=sprintf('%.2f',trim($csv[2],'"'));
	$act=trim($csv[3],'"');
	$cname=trim($csv[4],'"');
	if ( $act != 1 ) {
		$i++;
		$q="insert into myoptions set myoptions_id=$i,display_id=$i,name='$name',ch_name='$cname',price=$price";
		echo "$q;\n";
	}
}

#root@kpdc1:~# mdb-export /tmp/basil.mdb CustomerFiles |less
#CustomerID,CustomerName,CustomerNotes,DeliveryAddress,CrossStreet,ZipCode,DeliveryRemarks,DeliveryCharge,DeliveryComp,ScanCode,AccountCode,EditTimestamp,PhoneNumber,RemoteSiteNumber,RemoteOrigRowID,GlobalID,RowVer,SynchVer,StoreNumber,CompanyName,EmailAddress,VIPType,PictureName,AnniversaryMonth,BirthMonth,CustomerSinceDate,TotalSpent,TotalCount,AverageOrder,LastOrderDate,LastOrderAmount,HQRowID,LastRowHash,RowOwner,RowGUID
$customer=array();
$out=preg_split("/\n/",`mdb-export $file CustomerFiles`);
foreach ( $out as $k=>$v ) {
	if ( $k == 0 || $v == '' ) continue;
	$csv=preg_split("/,/",$v);
	if ( isset($csv[12]) && $csv[12] != '' ) {
		$id=$csv[0];
		$name=$csv[1];
		
		$name=preg_replace('/\W/','',$name);
		$name=preg_replace('/\s*=.*/','',$name);
		$name=preg_replace('/\s*NMBR/','',$name);
		$lname='';
		if ( preg_match('/(.*)\s+(.*)/',$name,$m) ) {
			$lname=$m[2];
			$name=$m[1];
		}
		
		$addr=$csv[3];
		$addr=preg_replace('/\W/',' ',$addr);
		$addr=preg_replace('/\s+/',' ',$addr);
		$addr=preg_replace('/^\s+/','',$addr);
		$addr=preg_replace('/s+$/','',$addr);
		$phone=$csv[12];
		$note=$csv[2];
		$note=preg_replace('/\W/',' ',$note);
		$note=preg_replace('/\s+/',' ',$note);
		$note=preg_replace('/^\s+/','',$note);
		$note=preg_replace('/\s+$/','',$note);
		$misc=$csv[6];
		$misc=preg_replace('/\W/',' ',$misc);
		$misc=preg_replace('/\s+/',' ',$misc);
		$misc=preg_replace('/^\s+/','',$misc);
		$misc=preg_replace('/\s+$/','',$misc);
		$charge=sprintf('%.2f',$csv[7]);
		if ( $addr != '' ) {
			$customer[]=array('name'=>$name,'addr'=>$addr,'phone'=>$phone,'note'=>$note,'misc'=>$misc,'charge'=>$charge);
			$q="insert into customerinfo set firstname='$name',lastname='$lname',home_phone='$phone',home_address1='$addr',charge=$charge,misc='$misc',note='$note'";
			$q=preg_replace('/\"/','',$q);
			echo "$q;\n";
		}
	}
}


$room=array();
$tbls=array();
$out=preg_split("/\n/",`mdb-export $file DineInTableGroups`);
foreach ( $out as $k=>$v ) {
	if ( $k == 0 || $v == '' ) continue;
	$csv=preg_split("/,/",$v);
	$id=$csv[0];
	$name=$csv[1];
	$room[$id]=preg_replace('/\W/','',$name);
}
$out=preg_split("/\n/",`mdb-export $file DineInTables`);
foreach ( $out as $k=>$v ) {
	if ( $k == 0 || $v == '' ) continue;
	$csv=preg_split("/,/",$v);
	$tbl=$csv[1];
	$rm=$csv[2];
	$id=trim($csv[3],'"')+2;
	$g=$csv[5];
	$tbls[$rm][]=array('name'=>$tbl,'id'=>$id,'guest'=>$g);
}
$i=0;
foreach ( $room as $k=>$v ) {
	$i++;
	$j=$i*100 - 100;
	$h=0;
	for($j;$j<=$i*100;$j++) {
		$h++;
		$found=0;
		foreach ($tbls[$k] as $k2=>$v2 ) {
			$n=$v2['name'];
			$g=$v2['guest'];
			if ( $g == '' ) $g=2;
			$lo=floor($v2['id']/9);
			$lm=$v2['id']%9;
			$ho=floor($h/10);
			$hm=$h%10;
			if ( $ho == $lo && $lm == $hm ) {
				$q="INSERT into seat set seat_id=$j,room='$v',name='$n',guests=$g";
				$q=preg_replace('/\"/','',$q);
				echo "$q;\n";
				$found=1;
			}
		}
		if ( $found == 0 ) {
			$q="insert into seat set seat_id=$j,room='$v'";
			echo "$q;\n";
		}
	}
}