Archive

Archive for September, 2009

MySQL – tips

September 12, 2009 Leave a comment

> SQL ? there is basic and there is advanced level for everything, right? here few:

CREATE FUNCTION `f_srrate`(v_Agentid varchar(3), v_Destno varchar(100)) RETURNS varchar(20)
begin

declare v_temprate1 varchar(20);
declare v_code1 varchar(10);
declare v_code2 varchar(10);
declare v_code3 varchar(10);
declare v_code4 varchar(10);
declare v_code5 varchar(10);
declare v_code6 varchar(10);
declare v_code7 varchar(10);
declare v_code8 varchar(10);
declare v_code9 varchar(10);
declare v_code10 varchar(10);
declare v_Rate varchar(20);

select left(v_Destno, 1) into v_code1;
select left(v_Destno, 2) into v_code2;
select left(v_Destno, 3) into v_code3;
select left(v_Destno, 4) into v_code4;
select left(v_Destno, 5) into v_code5;
select left(v_Destno, 6) into v_code6;
select left(v_Destno, 7) into v_code7;
select left(v_Destno, 8) into v_code8;
select left(v_Destno, 9) into v_code9;
select left(v_Destno, 10) into v_code10;

select rateid into v_temprate1 from srcomp
where srcomp.Agentid=v_Agentid;

select concat(collecting,”,”,setup,”,”,rate) into v_Rate from srrate use index(jony)
where Code in (v_code1, v_code2, v_code3, v_code4, v_code5, v_code6, v_code7, v_code8, v_code9, v_code10) and
rateid=v_temprate1 and
agentid=v_Agentid
order by srrate.code_len desc limit 1;

IF (v_Rate is NULL)
THEN
select concat(0,”,”,0,”,”,0) into v_Rate;
END IF;

return v_Rate;

End

insert ignore into slavecdr (agentid,compid,duration,destno,buycollecting,buysetup,buyrate,buycost)
select agentid,compid,duration,destno,
@b1:=substring(substring_index(@v1:=f_srrate(cdr.agentid,cdr.destno),’,’,@n:=1 ),character_length(substring_index( @v1, ‘,’ ,@n-1 ) ) +1 + if ( @n=1,0,1) ) BuyCollecting,
@b2:=substring(substring_index(@v1,’,’,@n:=@n+1),character_length(substring_index(@v1,’,’,@n-1))+1+if(@n=1,0,1)) BuySetup,
@b3:=substring(substring_index(@v1,’,’,@n:=@n+1),character_length(substring_index(@v1,’,’,@n-1))+1+if(@n=1,0,1)) BuyRate,
concat((@b3+@b1)*duration/60+@b2) as BuyCost
from cdr where date=’2007-03-20′ and duration>0;

Cake SQL – How about this, a invoice just ready to surve!

select
@a:=(
select
case when sum(invoice_items.eenheidsprijs) is null then 0 else sum(invoice_items.eenheidsprijs) end from invoice,invoice_items where invoice.creditnota_or_invoice=’factuur’ and invoice_items.invoiceid=invoice.id and invoice.contactid=’$contactid’
) as invoice,
@discounts:=(
select
case when sum(invoice.discount) is null then 0 else sum(invoice.discount) end from invoice where invoice.creditnota_or_invoice=’factuur’ and invoice.contactid=’$contactid’
) as discounts,
@vats:=(
select
case when sum(invoice.extras) is null then 0 else sum(invoice.extras) end from invoice where invoice.creditnota_or_invoice=’factuur’ and invoice.contactid=’$contactid’
) as vats,
@creditnotas:=(
select
case when sum(invoice_items.eenheidsprijs) is null then 0 else sum(invoice_items.eenheidsprijs) end from invoice,invoice_items where invoice.creditnota_or_invoice=’creditnota’ and invoice_items.invoiceid=invoice.id and invoice.contactid=’$contactid’
) as creditnota,
@payments:=(
select
case when sum(amount) is null then 0 else sum(amount) end  from invoice,invoice_payments where invoice_payments.invoiceid=invoice.id and invoice.contactid=’$contactid’
) as payments,
(@a-@discounts)+( (@a-@discounts)*(@vats/100) )  -(@creditnotas+@payments)

And this

select * from (
(

select bladid,naam,lasteblad,bladimg,bladpdf from bladeninfo a where a.naam=’E-MAGAZINE’ and
date(lasteblad)< ‘$date’
group by a.naam
)
union
(
select bladid,naam,lasteblad,bladimg,bladpdf from bladeninfo b where b.naam=’MECHANIC BeLux’ and
date(lasteblad)<‘$date’
group by b.naam
)
union
(
select bladid,naam,lasteblad,bladimg,bladpdf from bladeninfo c where c.naam=’INSTRUMENT BeLux’ and
date(lasteblad)<‘$date’
group by c.naam
)
union
(
select bladid,naam,lasteblad,bladimg,bladpdf from bladeninfo d where d.naam=’INSTALLATION BeLux’ and
date(lasteblad)<‘$date’
group by d.naam
)
union
(
select bladid,naam,lasteblad,bladimg,bladpdf from bladeninfo e where e.naam=’INSTALLATION BeLux’ and
date(lasteblad)<‘$date’
group by e.naam
)
union
(
select bladid,naam,lasteblad,bladimg,bladpdf from bladeninfo f where f.naam=’INDUSTRY BeLux’ and
date(lasteblad)<‘$date’
group by f.naam
)
union
(
select bladid,naam,lasteblad,bladimg,bladpdf from bladeninfo g where g.naam=’INDUSTRY NL’ and
date(lasteblad)<‘$date’
group by g.naam
)
) as abc

Also this?


— works

select * from (
select
orders.orderid,
bla.bladid,
bla.naam,
bla.maandnl,
bla.afsluiting,
edities.annoncenr,

— subquery

(
select
count(*)
from
aanvragen
where
aanvragen.annoncenr=edities.annoncenr and
aanvragen.bladid=edities.bladid
) as antwoord,
—  jaar to apply
bla.jaar
from
orders
left join edities on edities.orderid=orders.orderid
left join bladeninfo bla on bla.bladid=edities.bladid
where
orders.contact=’$contactid’   and
edities.annoncenr is not null  and
edities.bladid is not null

— two table join as one query

union

select
orders.orderid,
blb.bladid,
blb.naam,
blb.maandnl,
blb.afsluiting,
emagazine.id,

— subquery

(
select
count(*)
from
aanvragen
where
aanvragen.annoncenr=emagazine.id and
aanvragen.bladid=emagazine.bladid
) as antwoord,
— suggested jaar to apply
blb.jaar
from
— another table
orders
left join emagazine on emagazine.orderid=orders.orderid
left join bladeninfo blb on blb.bladid=emagazine.bladid
where
orders.contact=’$contactid’  and
emagazine.id is not null   and
emagazine.bladid is not null

— problem is order by now.

) as baz
order by
baz.jaar desc

Advertisements
Categories: MySQL

JavaScript – ajax image/file upload

September 7, 2009 Leave a comment

Ajax image upload ( basic to advanced)

Main Theory: <form target=nameof_Iframe

<body>
	<!-- Iframe to handle the form -->
	<iframe id="trgID" name="uploadTrg"  frameborder="0"  src="javascript: '<html> </html>'; "> </iframe>
	<!-- Form to submit -->
	<form     name="fileForm"     id="fileForm"
		enctype="multipart/form-data"
		action="include/add_edit_del.php?oper=add&model=file_upload"
		method="post" target="uploadTrg" >

		<input type="file" name="files" >
		<input name="submit" type="submit" id="submit_btn" >
	</form>
</body>
</html>

<!-- ajax file solution -->
<script type="text/javascript">

	$('#submit_btn').click(function(){
		$('#fileForm').submit();
	});

</script>

This will be not a ajax method of file/upload. Because not applied form target.

<body>
	<!-- Iframe to handle the form -->
	<iframe id="trgID" name="uploadTrg"  frameborder="0"  src="javascript: '<html> </html>'; "> </iframe>
	<!-- Form to submit -->
	<form     name="fileForm"     id="fileForm"
		enctype="multipart/form-data"
		action="include/add_edit_del.php?oper=add&model=file_upload"
		method="post"  >

		<input type="file" name="files" >
		<input name="submit" type="submit" id="submit_btn" >
	</form>
</body>
</html>

<!-- ajax file solution -->
<script type="text/javascript">

	$('#submit_btn').click(function(){
		$('#fileForm').submit();
	});

</script>
Categories: Ajax, CSS, JavaScript, PHP