2018年12月7日 星期五

MYSQL-Find last 5 po in each suppler in purchase order group max 5 recordid


SELECT `SUPPLIER`, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
   select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
  (@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
  ,CONTACT_NAME
  from PURCHASE_HEADER t
  CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
  order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) GROUP_5 GROUP BY CONTACT_NAME ORDER BY SUPPLIER;

/********************************************************************************/


 SET @MCOUNT=0;
 SELECT LEFT(VENDOR.NAME,20)
,ADDRESS_BOOK.NAME
,ADDRESS_BOOK.PHONE DIRECT_LINE
,VENDOR.PHONE_NO GENERAL_LINE
,ADDRESS_BOOK.EMAIL
,IF(IFNULL(Z3.COUNT_,0)>1,'*','') r
,VENDOR.COUNTER_ RANK
FROM
 (SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
  FROM (
   select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
   from
   (
      select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
      (@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
      ,CONTACT_NAME
     from PURCHASE_HEADER t
    CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
where po_date>=20171201
     order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
   ) as x
   where x.row_number <= 5
  ) Y GROUP BY CONTACT_NAME ORDER BY SUPPLIER
 ) Z
 LEFT JOIN
 (SELECT * FROM
 (SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
  FROM (
   select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
   from
   (
      select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
      (@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
      ,CONTACT_NAME
     from PURCHASE_HEADER t
     CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
where po_date>=20171201
     order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER

   ) as x

   where x.row_number <= 5

  ) Y GROUP BY SUPPLIER ORDER BY SUPPLIER

 ) Z)Z1

 ON Z.SUPPLIER=Z1.SUPPLIER AND Z.PO_DATE=Z1.PO_DATE

 LEFT JOIN

 (SELECT SUPPLIER, PO_DATE, COUNT(1) COUNT_ FROM (

 SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME

  FROM (

   select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME

   from

   (

      select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,

      (@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number

      ,CONTACT_NAME

     from PURCHASE_HEADER t

     CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
where po_date>=20171201
     order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER

   ) as x

   where x.row_number <= 5

  ) Y GROUP BY CONTACT_NAME ORDER BY SUPPLIER

 ) Z2 GROUP BY SUPPLIER) Z3
 ON Z.SUPPLIER=Z3.SUPPLIER AND Z.PO_DATE=Z3.PO_DATE
 LEFT JOIN
 (SELECT
VENDOR.NAME
,VENDOR.PHONE_NO
,VENDOR.TOTAL_AMOUNT_2018
,RECORDID
,@MCOUNT:=@MCOUNT+1 COUNTER_
 FROM VENDOR
 ORDER BY TOTAL_AMOUNT_2018
 DESC
 )
 VENDOR ON VENDOR.RECORDID=Z.SUPPLIER
 LEFT JOIN ADDRESS_BOOK ON ADDRESS_BOOK.RECORDID=Z.CONTACT_NAME
where not isnull(ADDRESS_BOOK.NAME)
order by VENDOR.name
;






/*****************************************************************************/
SELECT LEFT(VENDOR.NAME,20),ADDRESS_BOOK.NAME,ADDRESS_BOOK.EMAIL,IF(IFNULL(Z3.COUNT_,0)>1,'*','') FROM
(SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
   select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
  (@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
  ,CONTACT_NAME
  from PURCHASE_HEADER t
  CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
  order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) Y GROUP BY CONTACT_NAME ORDER BY SUPPLIER
) Z
LEFT JOIN
(SELECT * FROM
(SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
   select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
  (@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
  ,CONTACT_NAME
  from PURCHASE_HEADER t
  CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
  order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) Y GROUP BY SUPPLIER ORDER BY SUPPLIER
) Z)Z1
ON Z.SUPPLIER=Z1.SUPPLIER AND Z.PO_DATE=Z1.PO_DATE
LEFT JOIN
(SELECT SUPPLIER, PO_DATE, COUNT(1) COUNT_ FROM (
SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
   select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
  (@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
  ,CONTACT_NAME
  from PURCHASE_HEADER t
  CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
  order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) Y GROUP BY CONTACT_NAME ORDER BY SUPPLIER
) Z2 GROUP BY SUPPLIER) Z3
ON Z.SUPPLIER=Z3.SUPPLIER AND Z.PO_DATE=Z3.PO_DATE
LEFT JOIN
VENDOR ON VENDOR.RECORDID=Z.SUPPLIER
LEFT JOIN ADDRESS_BOOK ON ADDRESS_BOOK.RECORDID=Z.CONTACT_NAME
;

2018年1月23日 星期二

javascript - jquery colResize , cline remove element

var onSampleResized = function(e){ 
$('#tableName0_61 colgroup').remove();
$('#gridCaption colgroup').clone().appendTo('#tableName0_61');

/*
var table = $(e.currentTarget);
$("#gridCaption").find('tr').first().children().each(function(i, e)
{
tdWidth = $(this).width();
var $col = $('#tableName0_61 colgroup col');
$col.eq(i).width(tdWidth+'px');
});
*/
}; 
 $("#gridCaption").colResizable({
liveDrag:true,
gripInnerHtml:"<div class='grip'></div>",
draggingClass:"dragging",
onResize:onSampleResized
}); 

2017年7月20日 星期四

generator-combo-adoption





Get data from table with where dynamic

{object}
0.700,SALES_ORDER_HEAD.SOLUTION_NAME,combo,60%,100,Solution Name,,
{onfocusUpdateCombo}
{getJson}
tableReadCombo?table=SETTINGS&limit=999999&fields=CODE,DESCRIPTION&order=CODE&where=where(CODE_PARENT:`'+_lookup('SALES_ORDER_HEAD.SOLUTION_TYPE')+'`) &format=S,S&callback=?{/getJson}
{endOfLoad}
if(OPERATION.indexOf(",H5,")>-1 | OPERATION.indexOf("|H5|")>-1 ){
$(".SALES_ORDER_HEAD_SYSTEM_TYPE").multiselect("enable");
}
{/endOfLoad}
{enableWhenPartial}
{/object}




Get data from table with where constant 


{object}
0.28,PRDTMSTR.CATEGORY,combo,10,40,Type,,
{spaceline}{draftMust}
{getJson}tableRead?table=SETTINGS&limit=999999&fields=RECORDID,description&where=where status:`D` AND DATA_GROUP:`itype`&format=S,S&callback=?{/getJson}
{/object}


{object}
0.60,VENDPROD.PM,combo,100%,20,Product Manager ,,
{spaceLine}
{getJson}
tableReadCombo?table=ADDRESS_BOOK&limit=999999&fields=RECORDID,NAME&order=NAME&where=where status:`A` and (true) &format=S,S&callback=?{/getJson}
{comboConstrain}PARENT_RECORDID=reference{/comboConstrain}
{reference}VENDPROD.VENDOR_NO{/reference}     
{onfocusUpdateCombo}
{/object}



Get combo data from Memory 
{object}
0.14,SALES_ORDER_HEAD.EXTERNAL_SALES,combo,58%:50%:98%,10,Sales Person,,
{combo},ALL,[EXTSALES]{/combo}
{draftMust}
{/object}

EXTSALES,CURS,UOMS,MANUS,STATUSS,PROPTS,CATS,PAYTS,MTYPES,STYPES,PAYMS



{object}
0.40,CODE.NATURE,combo,100%:200px:400px,50,NATURE,,{required}
{combo}
Accounts Payable,Accounts Payable
,Bank,Bank
,Cash Account,Cash Account
,Cost of Goods Sold,Cost of Goods Sold
,Current Liability,Current Liability
,Equity,Equity
,Expense,Expense
,Income,Income
,Long-term Liability,Long-term Liability
,Other Asset,Other Asset
,Other Income,Other Income
,Other Expense,Other Expense
,Payroll Liability,Payroll Liability
{/combo}
{spaceline}
{/object}


{object}
0.26,SALES_ORDER_HEAD.CUSTOMER_NO,combo,98%:50%:98%,15,Customer,,
{getJson} tableReadCombo?table=CUSTOMER&limit=999999&fields=RECORDID,NAME&order=NAME&where=where status:`A` and (true)  &format=S,S&callback=?{/getJson} 
{onfocusUpdateCombo}
{reference}SALES_ORDER_HEAD.CUSTOMER_NO{/reference}        
{comboConstrain}1=1{/comboConstrain}
{draftMust}
{onChangeAjax}
TABLE=CUSTOMER
,FIELDS=ADDRESS1&ADDRESS1&ADDRESS1
&ADDRESS2&ADDRESS2&ADDRESS2
&ADDRESS3&ADDRESS3&ADDRESS3
&ADDRESS4&ADDRESS4&ADDRESS4
&NAME&NAME&NAME
&CONTACT&PHONE_NO
&CONTACT&PHONE_NO
&CONTACT&PHONE_NO
&PAYMENT_TERMS_CODE&PAYMENT_METHOD_CODE
&CONTACT&CONTACT&EMAIL&EMAIL
&EXTERNAL_SALES
&INTERNAL_SALES
&INTERNAL_SALES2
,UPDATES=
   this..SELL_TO_ADDRESS1&this..BILL_TO_ADDRESS1&this..DELIVERY_TO_ADDRESS1
&this..SELL_TO_ADDRESS2&this..BILL_TO_ADDRESS2&this..DELIVERY_TO_ADDRESS2
&this..SELL_TO_ADDRESS3&this..BILL_TO_ADDRESS3&this..DELIVERY_TO_ADDRESS3
&this..SELL_TO_ADDRESS4&this..BILL_TO_ADDRESS4&this..DELIVERY_TO_ADDRESS4
&this..SALES_TO_COMPANY&this..BILL_TO_COMPANY&this..SHIP_TO_COMPANY
&this..SALES_TO_NAME&this..SALES_TO_TEL
&this..CUSTOMER_CONTACT1&this..BILL_TO_TEL
&this..CUSTOMER_CONTACT1_NAME&this..CUSTOMER_CONTACT1_TELEPHONE
&this..PAYMENT_TERMS&this..PAYMENT_METHOD
&this..SALES_TO_NAME&this..CUSTOMER_CONTACT1&this..SELL_TO_EMAIL&this..BILL_TO_EMAIL
&this..EXTERNAL_SALES-
&this..INTERNAL_SALES-
&this..INTERNAL_SALES2-
{/onChangeAjax}
{onChange}
setTimeout(function() { 
var s0724=_lookup('SALES_ORDER_HEAD.PAYMENT_TERMS'); 
if(!s0724){_refreshv2('SALES_ORDER_HEAD.PAYMENT_TERMS','30 Days');} 
},100);
{/onChange}
{refresh}currentEntity=_lookup('this.id');{/refresh}
{/object}

2017年4月2日 星期日

java-string unknown number of space to 1 space


strVariable.replace("source1","target1").replace("source2","target2");

*********************************************************
unknown  number of space to 1 space

envelopeAddress1=envelopeAddress1.trim().replaceAll("\\s{2,}", " ");
envelopeAddress2=envelopeAddress2.trim().replaceAll("\\s{2,}", " ");
envelopeAddress3=envelopeAddress3.trim().replaceAll("\\s{2,}", " ");
envelopeAddress4=envelopeAddress4.trim().replaceAll("\\s{2,}", " ");



2016年9月30日 星期五

generator-udf generator-function




refresh('query.REASON_DESCRIPTION',uiDisplay(s.split(',')[1]));


lookupTable('SQD_FILE','SOPOQTY.SALES_ORDER',s1103x,'SQD_FILE<>"" and supplier='+s1111);

example :

{UDF1}
function afterRowColChange_(rowNumber){
var obj=document.getElementById(thisDBFName+'.IS_STATUS'+rowNumber);
if(obj){
if(document.getElementById('openApproveButton')){
var mStatus='P,Submitted';
var s0529a=(mStatus.indexOf(document.getElementById(thisDBFName+'.IS_STATUS'+rowNumber).value)>=0);
/*var s=document.getElementById(thisDBFName+'.ITEM_CONFIRM'+rowNumber).checked ;*/
var s=1;
if (s){document.getElementById('openApproveButton').disabled = !s0529a;}
}
if(document.getElementById('openEditButton')){
var mStatus='Reject,Revoked';
var s0529=(mStatus.indexOf(document.getElementById(thisDBFName+'.IS_STATUS'+rowNumber).value)<0);
document.getElementById('openEditButton').disabled = s0529;
}
}
}
{/UDF1}