Simple db / web service to sync data from API and then report on differences

Completed Posted 5 years ago Paid on delivery
Completed Paid on delivery

We run an IT service company and we bill a monthly flat rate based on devices and computers at our clients. We use software called IT Glue to document and track this infrastructure. We need a database created that tracks the devices and computers that we are actually billing for (along with a few other manual billing items). This database will be populated by querying the IT Glue API, and then allow our billing staff to review the changes before updating the invoice amounts stored in the database.

For our database, this schema should do the trick:

CUSTOMERS

ID (syncs from ITGlue API, pk)

Name (syncs from ITGlue API)

tax (calculate sales tax or not - t/f we assign this in database, default to true)

CONFIGURATIONS (this is what IT Glue calls computers, servers, and devices)

ID (syncs from ITGlue API, pk)

customer ID (syncs from ITGlue API, fk to CUSTOMERS)

Status (pre-deploy, active, decommissioned, deleted, syncs from ITGlue API)

name (syncs from ITGlue API)

type (syncs from ITGlue API - computer, device, server, etc)

PRODUCTS (these are the kinds of things we invoice for: devices, computers, 3rd party apps, etc.)

ID (pk)

Description (examples include "computers" "devices" "Antivirus licenses" "Backup 100GB increment", etc.)

type (service, rental, subscription)

Rate (this is in dollars)

LINE_ITEMS

ID

Customer (fk)

product (fk)

Units (how many of the product)

Detail (comma separated list of "configurations" included in "units" as of last update)

Updated (timestamp)

~~

Interface loads to just two buttons:

[sync glue] [billing report]

SYNC GLUE does this:

Pull list of clients from ITGlue API > populate to our database if there are any chnages

Pull list of configurations > populate our database with any changes

Result page loads with results from sync event

:: Customers: x records unchanged, x records updated > list of records updated;

:: Configurations: x records unchanged, x records changed or added > list of records updated

BILLING REPORT

Loop through all customer IDs (sorted by name)

For each customer:

Pull line item for computers. Print it to the screen. Explode "detail" into array variable of computer IDs - this is the list of computers that were billed as of last update.

Query: select all configurations of the "computer" types (servers, computers, laptops), and status "active." Print current billing list vs current real-world list (two columns) with formatting to highlight discrepancies. Summarize with "line item" quantity difference and a link to accept changes.

Do the same thing for devices line item.

Pull the other line items (backup 100GB, Antivirus seats, MS Office subscription, etc.) and print them to the screen.

Add up all line items for bill total including subtotals by product type (service, rental, subscription). Calculate sales tax depending on tax flag in customer table. Display another line for grand total including tax.

Repeat this for each customer. This can all be just dumped onto one big report page that just scrolls.

At the bottom, summarize company-wide totals by category (service, rental, subscription, tax) and then grand total for monthly billing.

Database Programming MySQL PHP Software Architecture SQL

Project ID: #17302235

About the project

32 proposals Remote project Active 5 years ago

Awarded to:

rinsadsl

Hi There, I am good in API integration and I have integrated different types of API for different sites. I can work with IT Glue API. Looking forward Thank You

$500 USD in 14 days
(1154 Reviews)
9.0

32 freelancers are bidding on average $554 for this job

meet2amitvw

Okay i can update db and API as you asked but i need to first review the current setup of it...can we discuss more on freelancer inbox to get detail understanding about project ? I have relevant skills and experienc More

$666 USD in 10 days
(175 Reviews)
9.3
rajeshsonisl

Hello! Do you have the API documentation for "IT Glue"? I'll need access to your server and can get started immediately. I have a 99% project completion rate and a 4.99 reputation (out of max 5.0 from more than More

$333 USD in 3 days
(1011 Reviews)
8.8
SigmaVisual

Hi, I have experience of working on similar projects, I can help to develop automated script for sync data from api. Please let me know about time when you would be available so we can discuss further. Please see More

$250 USD in 7 days
(279 Reviews)
8.2
extreamcode

HI There, I can assist you with Simple db / web service to sync data from API. I am very passionate about quality of my work, You will be pleased working with me. Let's have a quick chat to discuss it further. You More

$555 USD in 10 days
(88 Reviews)
7.7
mauricemarinus

Hi there. While there are quite a few good devs bidding i think that my experiece will set me apart and in good stead with this project. Ive been coding for 18 years professionally and about 25 years in total. But le More

$250 USD in 10 days
(105 Reviews)
7.1
michealperfect

Hello friend, I am so interested in developing web service to sync data from API and then report, I am an expert web developer, having years of experience. It would be ideal if you Feel allowed to get in touch with m More

$250 USD in 5 days
(177 Reviews)
6.9
fabest

Dear, we are Team of French + US. I checked your project description, and I confirm I can develop your program. I will focus on user friendly interface. As you can see I have very good rate, you can be sure I am ser More

$277 USD in 10 days
(66 Reviews)
7.2
jenishfaldu

Hello, I have reviewed your description that you are looking for a PHP developer to create web service to sync data from API and then report on differences. => Prestashop / E-commerce Expert. => 7+ Years of Experi More

$666 USD in 10 days
(96 Reviews)
6.6
novepi

Hi, Your requirement are quite clear thanks to nicely layed out db schema. Pretty much every field is already stored within glue and all we have to do is get it through API. Though I'm not sure how you want to modif More

$500 USD in 5 days
(56 Reviews)
6.2
logicpowered

We are very interested in this project. We have read your project details and are very much confident in completing all these requirements. We are working in IT industry since last 7 years. We are providing service More

$555 USD in 10 days
(26 Reviews)
6.7
smartstepsco

Hi I'm c# developer with 12 years experience on .net framework and related tools. I'll create application with c# .net to sync sql database from api and and generate some reports. please send me a message to have di More

$700 USD in 10 days
(15 Reviews)
5.3
arzooshaikh

Hi client, see my portfolio for more PHP project information. I am PHP developer having 6 years of experience in software development. please start your discussion with me. thanks.

$250 USD in 15 days
(16 Reviews)
4.6
aniljks

Hi, I am an experienced .net c# developer and worked on Windows application, asp.net Web application, MVC web application, web services and web APIs. You can see reviews in my profile for completed projects and More

$555 USD in 10 days
(8 Reviews)
3.5
Garima6892

I have extensive 5 years of experience in database programming. My work won't disappoint you. Relevant Skills and Experience Mysql, oracle, plsql, webservic

$666.666667 USD in 10 days
(0 Reviews)
0.0