#CLEANED IT UP A LITTLE
delete from payments;
delete from invoice_items;
delete from invoices;
delete from clients;
delete from products;
ALTER TABLE ninj646
.products
CHANGE COLUMN public_id
public_id
INT(10) NULL ;
##import products
insert into products (product_key, cost, notes,tax_rate1,tax_rate2, account_id, user_id )
SELECT distinct trim(ucase(c4data)) as product_key, ‘0’ as cost, ‘’ as notes,
‘8.25’ as tax_rate1, ‘0’ as tax_rate2, ‘1’ as account_id, ‘1’ as user_id
FROM ninj646.ices_inv inner join ninj646.ices_det on ices_det.invno = ices_inv.INVNUMBER
where invno >= ‘21715’ and INVPRINTED = ‘T’ and INVVOIDED = ‘F’ and
INVDELETED = ‘F’ and ISESTIMATE = ‘F’ and c4data is not null;
update products set public_id = id;
ALTER TABLE ninj646
.products
CHANGE COLUMN public_id
public_id
INT(10) UNSIGNED NOT NULL ;
clients or customers
##list clients
#select distinct inv.BTCOMPANY as name
from ices_inv as inv where inv.INVDATE >= ‘20140101’ and inv.INVPRINTED = ‘T’ and inv.INVVOIDED = ‘F’ and inv.INVDELETED = ‘F’ and inv.ISESTIMATE = ‘F’ and inv.btcompany is not null order by inv.btcompany, inv.invdate desc;
ALTER TABLE ninj646
.clients
CHANGE COLUMN public_id
public_id
INT(10) NULL ;
##import clients
insert into clients (user_id, account_id, name
, address1, address2, city, state,
postal_code,work_phone, private_notes,is_deleted )
SELECT ‘1’ as user_id, ‘1’ as account_id, trim(ucase(c1.btcompany)) as name
,
c1.BTADDRESS1 as address1, c1.BTADDRESS2 as address2, c1.BTCITY as city,
c1.BTSTATE as state, c1.btzip as postal_code, ‘’ as work_phone, ‘’ as private_phone,
‘0’ as is_deleted
FROM i_inv AS c1
JOIN ( SELECT btcompany, MAX(invnumber) AS invnumber
FROM i_inv GROUP BY btcompany ) AS c2 USING (btcompany, invnumber)
where c1.INVDATE >= ‘20140101’ and c1.INVPRINTED = ‘T’ and c1.INVVOIDED = ‘F’ and
c1.INVDELETED = ‘F’ and c1.ISESTIMATE = ‘F’ and
c1.btcompany is not null order by c1.btcompany, c1.invdate desc;
update clients set public_id = id, id_number = id ;
ALTER TABLE ninj646
.clients
CHANGE COLUMN public_id
public_id
INT(10) UNSIGNED NOT NULL ;
ALTER TABLE ninj646
.invoices
CHANGE COLUMN public_id
public_id
INT(10) NULL ;
##import invoices
insert into invoices (client_id, user_id, account_id,invoice_status_id, invoice_number,
po_number, invoice_date, terms, public_notes,frequency_id,tax_name1,tax_rate1,
amount,balance, custom_text_value1, custom_text_value2, tax_rate2)
select clients.id as client_id, ‘1’ as user_id, ‘1’ as account_id,
‘5’ as invoice_status_id, INVNUMBER as invoice_number,
if(isnull(gicol1con),‘NA’,gicol1con) as po_number,
concat(substring(invdate,1,4),’-’,substring(invdate,5,2),’-’,substring(invdate,7,2)) as invoice_date,
if(isnull(gicol2con),’’,gicol2con) as terms,
if(isnull(gicol6con) ,’’,gicol6con) as public_notes,
‘0’ as frequency_id,‘TAX’ as tax_name1,‘8.25’ as tax_rate1,
‘0’ as amount,‘0’ as balance,
if(
concat(
if(isnull(stcompany),’’,concat(stcompany,’\r\n’)),
if(isnull(staddress1),’’, concat(staddress1,’\r\n’)),
if(isnull(staddress2),’’, concat(staddress2,’\r\n’)),
if(isnull(stcity),’’, concat(stcity,’,’)),
if(isnull(ststate),’’, concat(ststate,’ ‘)),
if(isnull(stzip),’’, concat(stzip,’’))
) is null,
concat(
if(isnull(btcompany),’’,concat(btcompany,’\r\n’)),
if(isnull(btaddress1),’’, concat(btaddress1,’\r\n’)),
if(isnull(btaddress2),’’, concat(btaddress2,’\r\n’)),
if(isnull(btcity),’’, concat(btcity,’,’)),
if(isnull(btstate),’’, concat(btstate,’ ‘)),
if(isnull(btzip),’’, concat(btzip,’’))),
concat(
if(isnull(stcompany),’’,concat(stcompany,’\r\n’)),
if(isnull(staddress1),’’, concat(staddress1,’\r\n’)),
if(isnull(staddress2),’’, concat(staddress2,’\r\n’)),
if(isnull(stcity),’’, concat(stcity,’,’)),
if(isnull(ststate),’’, concat(ststate,’ ‘)),
if(isnull(stzip),’’, concat(stzip,’’)))
) as custom_text_value1,
‘’ as custom_text_value2,‘0’ as tax_rate2
from i_inv as c1
inner join clients on clients.name
= trim(ucase(BTCOMPANY))
where c1.INVDATE >= ‘20140101’ and c1.INVPRINTED = ‘T’ and
c1.INVVOIDED = ‘F’ and c1.INVDELETED = ‘F’ and
c1.ISESTIMATE = ‘F’ and c1.btcompany is not null
order by c1.btcompany, c1.invdate desc;
update invoices set public_id = id;
ALTER TABLE ninj646
.invoices
CHANGE COLUMN public_id
public_id
INT(10) UNSIGNED NOT NULL ;
ALTER TABLE ninj646
.invoice_items
CHANGE COLUMN public_id
public_id
INT(10) NULL ;
##import invoice_items
insert into invoice_items (account_id, user_id, invoice_id, product_key, notes, cost,
qty,tax_name1, tax_rate1 , tax_rate2, discount)
select ‘1’ as account_id, ‘1’ as user_id, id as invoice_id, trim(ucase(c4data)) as product_key,
‘’ as notes, c6data as cost, c5data as qty,‘TAX’ as tax_name1,
if(c8data=‘T’,‘8.25’,‘0’) as tax_rate1 , ‘0’ as tax_rate2, ‘0’ as discount
from ices_det
inner join invoices on invoice_number = invno
where invoice_number is not null and c4data is not null;
update invoice_items set public_id = id;
ALTER TABLE ninj646
.invoice_items
CHANGE COLUMN public_id
public_id
INT(10) UNSIGNED NOT NULL ;
ALTER TABLE ninj646
.payments
CHANGE COLUMN public_id
public_id
INT(10) NULL ;
##import payments
insert into payments (invoice_id, account_id, client_id, user_id, payment_type_id,
amount, payment_date, refunded) select invoices.id as invoice_id,
‘1’ as account_id, invoices.client_id as client_id, ‘1’ as user_id,
‘16’ as payment_type_id,cashamount as amount,
concat(substring(invdate,1,4),’-’,substring(invdate,5,2),’-’,substring(invdate,7,2)) as payment_date,
‘0’ as refunded from e_pay inner join invoices on invoice_number = invnumber
where invoice_number is not null;
update payments set public_id = id;
ALTER TABLE ninj646
.payments
CHANGE COLUMN public_id
public_id
INT(10) UNSIGNED NOT NULL ;
##update invoices amount and balance
#select amount from payments
##update customers payed_to_date and balance
#select amount from payments