SoftOne_getDocs

SiteData.Items

Posted on By Konstantinos Kourentzes No Comments on SiteData.Items 
//// SiteData.Items
function checkClientId(Prms)
{
 try {
   return (Prms.clientID && Prms.clientID!="");
 }
 catch (e) {
   return true;
 }
}

function responseError(Prms)
{
 var resp={};

 resp.success=false;
 resp.count=-1;
 resp.error="Authenticate fails due to invalid credentials!";
 return resp;
}

function SQL2JSON(fSQL) {
 var resp={};

 fDs=X.GETSQLDATASET(fSQL, null);
 if (fDs.RECORDCOUNT>0) {
   resp.success=true;
	 resp.count=fDs.RECORDCOUNT;
   resp.data=eval(fDs.JSON);
   return resp;
 }
 else if (fDs.RECORDCOUNT==0) {
   resp.success=true;
	 resp.count=0;
   resp.error="result count = 0";
   return resp;
 }
 else {
   resp.success=false;
	 resp.count=0;
   resp.error="No data!";
   return resp;
 } 
}

function AddDateDayNoTime(fDateSQL)
{
 fDate=new Date(fDateSQL.substr(0,4), fDateSQL.substr(4,2)-1, fDateSQL.substr(6,2));
 fDate=fDate.setDate(fDate.getDate()+1);
 fDate=new Date(fDate);
 fDateSQL=''+fDate.getFullYear();
 if ((fDate.getMonth()+1)<10)
   fDateSQL=fDateSQL+'0';
 fDateSQL=fDateSQL+(fDate.getMonth()+1);
 if (fDate.getDate()<10)
   fDateSQL=fDateSQL+'0';
 fDateSQL=fDateSQL+fDate.getDate();

 return fDateSQL;
}

function PrmsToSQLFmt(fPrms,fFldType,fSQLFld)
{
 fSQLWhere='';
 if (fPrms!='' && fPrms!=undefined) {
   if (fFldType=='String' || fFldType=='Int') {
     if (fFldType=='String') {	 
       fPrms=fPrms.replace(/;/g,String.fromCharCode(39)+','+String.fromCharCode(39));
       fPrms=String.fromCharCode(39)+fPrms+String.fromCharCode(39);
     }
     else if (fFldType=='Int')
       fPrms=fPrms.replace(/;/g,',');

     fArrSQLFld=fSQLFld.split('_');
     fSQLFld=fArrSQLFld[0];
     fSQLFldOp=fArrSQLFld[1];

     if (fSQLFldOp=='L')
       fSQLWhere=' AND '+fSQLFld+'>='+fPrms;
     else if (fSQLFldOp=='H')
       fSQLWhere=' AND '+fSQLFld+'<='+fPrms;
     else {
       if (fPrms.indexOf(',')>-1)
         fPrms=' IN ('+fPrms+')';
       else
         fPrms='='+fPrms;

       fSQLWhere=' AND '+fSQLFld+fPrms;
     }
   }
   else if (fFldType=='Datetime') {
     if (fPrms.indexOf(';')==-1 && fPrms.indexOf(':')>-1) { //One date with time
       fPrms=fPrms.replace(/:/g,String.fromCharCode(39)+'+CHAR(58)+'+String.fromCharCode(39));
       fSQLWhere=' AND '+fSQLFld+'>=CAST('+String.fromCharCode(39)+fPrms+String.fromCharCode(39)+' AS DATETIME)';
     }
     else if (fPrms.indexOf(';')==-1 && fPrms.indexOf(':')==-1) { //One date without time
       fDateL=fPrms;
       fDateL=fDateL.replace(/:/g,String.fromCharCode(39)+'+CHAR(58)+'+String.fromCharCode(39));
       fDateH=AddDateDayNoTime(fPrms);
       fDateH=fDateH.replace(/:/g,String.fromCharCode(39)+'+CHAR(58)+'+String.fromCharCode(39));
       fSQLWhere=' AND '+fSQLFld+'>=CAST('+String.fromCharCode(39)+fDateL+String.fromCharCode(39)+' AS DATETIME) AND '+fSQLFld+'<CAST('+String.fromCharCode(39)+fDateH+String.fromCharCode(39)+' AS DATETIME)';
     }
     else { //Two dates
       fPos=fPrms.indexOf(';');
       fDateL=fPrms.substr(0,fPos);
       fDateL=fDateL.replace(/:/g,String.fromCharCode(39)+'+CHAR(58)+'+String.fromCharCode(39));
       fDateH=fPrms.substr(fPos+1,fPrms.length);
       fSQLWhere=' AND '+fSQLFld+'>=CAST('+String.fromCharCode(39)+fDateL+String.fromCharCode(39)+' AS DATETIME) AND '+fSQLFld+'<';

       if (fDateH.indexOf(':')==-1)
         fDateH=AddDateDayNoTime(fDateH);
       else
         fSQLWhere=fSQLWhere+'=';

       fDateH=fDateH.replace(/:/g,String.fromCharCode(39)+'+CHAR(58)+'+String.fromCharCode(39));
			 fSQLWhere=fSQLWhere+'CAST('+String.fromCharCode(39)+fDateH+String.fromCharCode(39)+' AS DATETIME)';
     }
   }
 }

 return fSQLWhere;
}
JavaScript

getCustomers

function getCustomers(Prms) {
 var resp={};

 if (checkClientId(Prms)) {
		  MultiSearch=Prms.MULTISEARCH;
			if (MultiSearch=='' || MultiSearch==undefined) {
			fSQL = '';
			}
			else {
			fSQL = " AND (T.AFM like '%"+MultiSearch+"%' or T.EMAIL like '%"+MultiSearch+"%' or T.PHONE01 like '%"+MultiSearch+"%' or T.PHONE02 like '%"+MultiSearch+"%' )";
			}
			
   fSQL='SELECT T.TRDR, T.CODE, T.AFM, T.NAME, T.PHONE01, T.PHONE02, T.EMAIL, T.UPDDATE '+
        'FROM TRDR T '+
        'WHERE T.COMPANY='+X.SYS.COMPANY+' '+
        'AND T.SODTYPE=13 '+
        'AND T.ISACTIVE=1'+fSQL;

   fSQL=fSQL.replace(/"/g,String.fromCharCode(39));
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDR,'Int','T.TRDR');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDR_L,'Int','T.TRDR_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDR_H,'Int','T.TRDR_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.CODE,'String','T.CODE');
   fSQL=fSQL+PrmsToSQLFmt(Prms.EMAIL,'String','T.EMAIL');
	 fSQL=fSQL+PrmsToSQLFmt(Prms.PHONE01,'String','T.PHONE01');
	 fSQL=fSQL+PrmsToSQLFmt(Prms.PHONE02,'String','T.PHONE02');	 
   fSQL=fSQL+PrmsToSQLFmt(Prms.AFM,'String','T.AFM');	 
   fSQL=fSQL+PrmsToSQLFmt(Prms.UPDDATE,'Datetime','T.UPDDATE');

   return SQL2JSON(fSQL);
 }
 else {
   return responseError(Prms);
 }
}

function getCustomersBranches(Prms) {
 var resp={};


 if (checkClientId(Prms)) { 
   fBranch	=Prms.BRANCH;
	 fShowSQL	=Prms.SQL;
	 var fSQL='';

   fSQL='SELECT B.TRDR, B.TRDBRANCH, B.CODE AS BCODE, B.NAME AS BNAME, B.PHONE1, B.ADDRESS, B.CITY, B.DISTRICT, B.ZIP, T.AFM, T.UPDDATE '+
        'FROM TRDR T INNER JOIN TRDBRANCH B ON B.TRDR=T.TRDR '+
                    'LEFT OUTER JOIN PRSN P ON P.PRSN=B.SALESMAN '+
        'WHERE T.COMPANY='+X.SYS.COMPANY+' '+
        'AND T.SODTYPE=13 '+
        'AND T.ISACTIVE=1 '+
        fSQL;

   fSQL=fSQL.replace(/"/g,String.fromCharCode(39));
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDR,'Int','T.TRDR');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDR_L,'Int','T.TRDR_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDR_H,'Int','T.TRDR_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TCODE,'String','T.CODE');
   fSQL=fSQL+PrmsToSQLFmt(Prms.BCODE,'String','B.CODE');
   fSQL=fSQL+PrmsToSQLFmt(Prms.PHONE1,'String','B.PHONE1');
   fSQL=fSQL+PrmsToSQLFmt(Prms.AFM,'String','T.AFM');
   fSQL=fSQL+PrmsToSQLFmt(Prms.UPDDATE,'Datetime','T.UPDDATE');

if (fShowSQL=='1')
	return fSQL;
	else
   return SQL2JSON(fSQL);
 }
 else {
   return responseError(Prms);
 }
}
JavaScript

getSuppliers

function getSuppliers(Prms) {
 var resp={};

 if (checkClientId(Prms)) {
   fBranch=Prms.BRANCH;
	 var fSQL='';

   fSQL='SELECT T.TRDR, T.CODE AS TCODE, T.NAME AS TNAME, T.PHONE01, T.ADDRESS, T.CITY, T.DISTRICT, T.ZIP, T.AFM, T.IRSDATA, T.UPDDATE '+
        'FROM TRDR T '+
        'WHERE T.COMPANY='+X.SYS.COMPANY+' '+
        'AND T.SODTYPE=12 '+
        'AND T.ISACTIVE=1 '+
        fSQL;

   fSQL=fSQL.replace(/"/g,String.fromCharCode(39));
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDR,'Int','T.TRDR');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDR_L,'Int','T.TRDR_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDR_H,'Int','T.TRDR_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.CODE,'String','T.CODE');
   fSQL=fSQL+PrmsToSQLFmt(Prms.AFM,'String','T.AFM');
   fSQL=fSQL+PrmsToSQLFmt(Prms.UPDDATE,'Datetime','T.UPDDATE');

   return SQL2JSON(fSQL);
 }
 else {
   return responseError(Prms);
 }
}
JavaScript

getMtrlot

function getMtrlot(Prms) {
 var resp={};

 if (checkClientId(Prms)) {
   fSQL='SELECT CODE, MTRLOT ' +
				'FROM MTRLOT ' +
        'WHERE COMPANY='+X.SYS.COMPANY+' '+
        'AND ISACTIVE=1 ';

   fSQL=fSQL.replace(/"/g,String.fromCharCode(39));
	 fSQL=fSQL+PrmsToSQLFmt(Prms.MTRLOT,'Int','MTRLOT');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL,'Int','MTRL');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDR,'Int','TRDR');	 
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL_L,'Int','MTRL_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL_H,'Int','MTRL_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.CODE,'String','CODE');
   fSQL=fSQL+PrmsToSQLFmt(Prms.FINALDATE,'Datetime','FINALDATE');
   fSQL=fSQL+PrmsToSQLFmt(Prms.UPDDATE,'Datetime','UPDDATE');	 

   return SQL2JSON(fSQL);
 }
 else {
   return responseError(Prms);
 }
}
JavaScript

getMTRUNIT

function getMTRUNIT(Prms) {
 var resp={};

 if (checkClientId(Prms)) {
 
		fSQL='SELECT M.MTRUNIT, M.SHORTCUT, M.NAME FROM MTRUNIT M WHERE M.COMPANY='+X.SYS.COMPANY+' ';
	
		fSQL=fSQL.replace(/"/g,String.fromCharCode(39));
		fSQL=fSQL+PrmsToSQLFmt(Prms.ISACTIVE,'Int','M.ISACTIVE');
	
	   return SQL2JSON(fSQL);
		 }
		 else {
		 return responseError(Prms);
		 }
}
JavaScript

getDocs

function getDocs(Prms) {
 var resp={};

 if (checkClientId(Prms)) {
 
 	SiteFilter=Prms.SITE;
	zSQL = '';
 
   fSQL='SELECT F.FINDOC, F.FINCODE, F.TRNDATE, F.SUMAMNT, T.CODE AS TRDRCODE, T.NAME AS TRDRNAME, '+
               'CASE WHEN F.ISCANCEL!=0                    THEN "Ακυρωμένη" '+
                    'WHEN LLS.DOCEXISTS=1 AND PENDEXISTS=1 THEN "Μερικώς απεσταλμένη" '+
                    'WHEN LLS.DOCEXISTS=1                  THEN "Απεσταλμένη" '+
                                                          'ELSE "Σε εξέλιξη" '+
               'END AS STATUS '+
							 ',COMMENTS, SK.NAME SHIPMENT, $NVL(F.BOOL01,0) as SENDUPDATE, F.FINSTATES, (select max(name) from finstates where finstates=f.finstates) FINSTATESTXT '+
        'FROM FINDOC F LEFT OUTER JOIN TRDR T ON T.TRDR=F.TRDR '+
				'LEFT OUTER JOIN SHIPMENT SK on SK.SHIPMENT=F.SHIPMENT and SK.COMPANY=F.COMPANY '+
                      'OUTER APPLY (SELECT TOP 1 1 AS DOCEXISTS '+
                                   'FROM MTRLINES LS INNER JOIN FINDOC FS ON FS.FINDOC=LS.FINDOC '+
                                   'WHERE LS.FINDOCS=F.FINDOC '+
                                   'AND FS.TFPRMS IN (101,102,103,131) '+
                                   'AND FS.ISCANCEL=0) LLS '+
                      'OUTER APPLY (SELECT TOP 1 1 AS PENDEXISTS '+
                                   'FROM MTRLINES L '+
                                   'WHERE L.FINDOC=F.FINDOC '+
                                   'AND L.PENDING=1 '+
                                   'AND $NVL(L.QTY1,0)-$NVL(L.QTY1COV,0)-$NVL(L.QTY1CANC,0)>0) LL '+
        'WHERE F.COMPANY='+X.SYS.COMPANY+' '+
        'AND F.SOSOURCE=1351 '+
        'AND F.TFPRMS=201 ' ;

   fSQL=fSQL.replace(/"/g,String.fromCharCode(39));
   fSQL=fSQL+PrmsToSQLFmt(Prms.FINDOC,'Int','F.FINDOC');
   fSQL=fSQL+PrmsToSQLFmt(Prms.FINDOC_L,'Int','F.FINDOC_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.FINDOC_H,'Int','F.FINDOC_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.FINCODE,'String','F.FINCODE');
   fSQL=fSQL+PrmsToSQLFmt(Prms.FINCODE_L,'String','F.FINCODE_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.FINCODE_H,'String','F.FINCODE_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDRCODE,'String','T.CODE');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDRCODE_L,'String','T.CODE_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.TRDRCODE_H,'String','T.CODE_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.UPDDATE,'Datetime','F.UPDDATE');
	 fSQL=fSQL+PrmsToSQLFmt(Prms.TRNDATE,'Datetime','F.TRNDATE');
	 
	 
   return SQL2JSON(fSQL);
 }
 else {
   return responseError(Prms);
 }
}
JavaScript

getItems

function getItems(Prms) {
 var resp={};

 if (checkClientId(Prms)) { 
	 var fSQL='';
	 var fexclude=' AND 1=1 ';
		
   fSQL="SELECT M.MTRL, M.CODE, M.NAME, U.MTRUNIT, U.NAME AS UNAME, M.UPDDATE, "+
				"MG.MTRGROUP, MG.NAME MTRGROUP_NAME, "+
				"MC.MTRCATEGORY, MC.NAME MTRCATEGORY_NAME, "+
				"MK.MTRMARK, MK.NAME MTRMARK_NAME, "+
				"SE.MTRSEASON, SE.NAME MTRSEASON_NAME "+
        "FROM MTRL M LEFT OUTER JOIN VAT V ON V.VAT=M.VAT "+
        "LEFT OUTER JOIN MTRUNIT U ON U.COMPANY=M.COMPANY AND U.MTRUNIT=M.MTRUNIT1 "+
        "left outer join MTREXTRA ME on ME.MTRL=M.MTRL "+
        "LEFT OUTER JOIN MTRGROUP MG ON MG.MTRGROUP = M.MTRGROUP AND MG.SODTYPE=M.SODTYPE AND MG.COMPANY=M.COMPANY "+
        "LEFT OUTER JOIN MTRCATEGORY MC ON MC.MTRCATEGORY = M.MTRCATEGORY AND MC.SODTYPE=M.SODTYPE AND MC.COMPANY=M.COMPANY "+
        "LEFT OUTER JOIN MTRMARK MK ON MK.MTRMARK=M.MTRMARK AND MK.COMPANY = M.COMPANY AND MK.SODTYPE=M.SODTYPE "+
        "LEFT OUTER JOIN MTRSEASON SE ON SE.MTRSEASON=M.MTRSEASON AND SE.COMPANY=M.COMPANY "+
        "WHERE M.COMPANY="+X.SYS.COMPANY+" "+
        "AND M.SODTYPE=51 "+
        "AND M.ISACTIVE=1 "+
				fexclude+" "+
        fSQL;

   fSQL=fSQL.replace(/"/g,String.fromCharCode(39));
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL,'Int','M.MTRL');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRGROUP,'Int','MG.MTRGROUP');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRCATEGORY,'Int','MC.MTRCATEGORY');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRMARK,'Int','MK.MTRMARK');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL_L,'Int','M.MTRL_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL_H,'Int','M.MTRL_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.NUM01,'Int','ME.NUM01');
   fSQL=fSQL+PrmsToSQLFmt(Prms.NUM01_L,'Int','ME.NUM01_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.NUM01_H,'Int','ME.NUM01_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.CODE,'String','M.CODE');
   fSQL=fSQL+PrmsToSQLFmt(Prms.UPDDATE,'Datetime','M.UPDDATE');

	if(Prms.SQL==1)
		return fSQL;
	else
   return SQL2JSON(fSQL);
 }
 else {
   return responseError(Prms);
 }
}
JavaScript

getItemsPrices

function getItemsPrices(Prms) {
 var resp={};

 if (checkClientId(Prms)) { 
	 var fSQL='';
	 var fexclude=' AND 1=1 ';
		
	fSQL = "SELECT M.MTRL, M.CODE, $nvl(M.PRICER,0) PRICER, $NVL(PRICEW,0) PRICEW, $nvl(M.PRICER01,0) PRICER01, $NVL(PRICEW01,0) PRICEW01,  M.UPDDATE "
				+"from MTRL M "+
				+"where M.SODTYPE=51 and M.COMPANY="+X.SYS.COMPANY+" and M.ISACTIVE=1 "
				+fexclude+" "+fSQL;

   fSQL=fSQL.replace(/"/g,String.fromCharCode(39));
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL,'Int','M.MTRL');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRGROUP,'Int','MG.MTRGROUP');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRCATEGORY,'Int','MC.MTRCATEGORY');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRMARK,'Int','MK.MTRMARK');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL_L,'Int','M.MTRL_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL_H,'Int','M.MTRL_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.CODE,'String','M.CODE');
   fSQL=fSQL+PrmsToSQLFmt(Prms.UPDDATE,'Datetime','M.UPDDATE');


	if(Prms.SQL==1)
		return fSQL;
	else
   return SQL2JSON(fSQL);
 }
 else {
   return responseError(Prms);
 }
}
JavaScript

getItemsBalance

function getItemsBalance(Prms) {
 var resp={};
 
 
 if (checkClientId(Prms)) {
 
 	SiteFilter=Prms.SITE;
	zSQL = '';

			
   fSQL='SELECT M.MTRL '+
              ',M.CODE '+
              ',$NVL(DD.QTY1,0)+$NVL(LL.MLQTY1,0) AS BALANCE '+
							',$NVL(DD.QTY1,0)  AS WHBALANCE '+
							',$NVL(LL.MLQTY1,0) AS ORDERED '+
        'FROM MTRL M LEFT OUTER JOIN MTREXTRA X ON X.MTRL=M.MTRL '+
				' LEFT OUTER JOIN MTRACN MC on MC.COMPANY=M.COMPANY and MC.SODTYPE=M.SODTYPE and MC.MTRACN = M.MTRACN '+
				' LEFT OUTER JOIN MTRCATEGORY MT on MT.COMPANY=M.COMPANY and MT.SODTYPE=M.SODTYPE and MT.MTRCATEGORY=M.MTRCATEGORY '+
				' LEFT OUTER JOIN MTRGROUP MG on MG.COMPANY=M.COMPANY and MG.SODTYPE=M.SODTYPE and MG.MTRGROUP=M.MTRGROUP '+
				
                    'OUTER APPLY (SELECT SUM($NVL(D.QTY1,0)) AS QTY1 '+
                                 'FROM MTRFINDATA D '+
                                 'WHERE D.COMPANY='+X.SYS.COMPANY+' '+
                                 'AND D.MTRL=M.MTRL '+
                                 'AND D.FISCPRD='+X.SYS.FISCPRD+' '+
                                 'AND D.WHOUSE IN (1000)) DD '+
                    'OUTER APPLY (SELECT SUM($NVL(($NVL(L.QTY1,0)-$NVL(L.QTY1COV,0)-$NVL(L.QTY1CANC,0))*CASE R.RESTCATEG WHEN 1 THEN  1 '+
                                                                                                                        'WHEN 2 THEN -1 '+
                                                                                                       'END,0)) AS MLQTY1 '+
                                 'FROM MTRLINES L WITH (INDEX=XI_MTRLINES_PENDING,NOLOCK) INNER JOIN RESTMODE R ON R.COMPANY=L.COMPANY '+
                                                                                                             'AND R.RESTMODE=L.RESTMODE '+
                                                                                         'INNER JOIN FINDOC F ON F.FINDOC=L.FINDOC '+
                                 'WHERE L.MTRL=M.MTRL '+
                                 'AND L.PENDING=1 '+
                                 'AND L.WHOUSE IN (1000) '+
                                 'AND R.RESTCATEG IN (1,2) '+ 
                                 'AND F.ISCANCEL=0) LL '+
        'WHERE M.COMPANY='+X.SYS.COMPANY+' '+
        'AND M.SODTYPE=51 '+zSQL+
        'AND M.ISACTIVE=1 ';

   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL,'Int','M.MTRL');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL_L,'Int','M.MTRL_L');
   fSQL=fSQL+PrmsToSQLFmt(Prms.MTRL_H,'Int','M.MTRL_H');
   fSQL=fSQL+PrmsToSQLFmt(Prms.CODE,'String','M.CODE');

   return SQL2JSON(fSQL);
 }
 else {
   return responseError(Prms);
 }
}
JavaScript

getTableData

function getTableData(Prms) {
var ftable;
	ftable=Prms.TABLE;

vsql = "SELECT "+
"  ISNULL((SELECT 1 FROM syscolumns WHERE name = 'SODTYPE' AND id = OBJECT_ID('"+ftable+"')), 0) AS SODTYPE, "+
"  ISNULL((SELECT 1 FROM syscolumns WHERE name = 'COMPANY' AND id = OBJECT_ID('"+ftable+"')), 0) AS COMPANY  "+
"";

FR = X.GETSQLDATASET(vsql,null);

where =' where 1=1 ';
if (FR.SODTYPE==1)
	where = where + ' and sodtype in (13,51) ';
if (FR.COMPANY==1)
	where = where + ' and company='+X.SYS.MAINCOMPANY;

 if (checkClientId(Prms)) {
		fSQL = 'Select * from '+ftable+ where;
	
		return SQL2JSON(fSQL);
 }
 else {
   return responseError(Prms);
 }	

}
JavaScript

getItemSales

function getItemSales(Prms) {
 var resp={};
 var vdays;
 var dimsql;
 
 if (Prms.DAYS>0) { vdays = Prms.DAYS; }
	else {vdays=0;}

 dimsql = 'M.CODE';

 if (Prms.ALLCODES==1) {
 dimsql = 'M.COMPANY, M.MTRL, M.CODE, M.CODE1, M.CODE2, M.NAME';
 }
 

 if (checkClientId(Prms)) {
   fSQL='select  '+dimsql+' '
				+',sum(SALQTY1) SALQTY1 '
				+'from MTRL M INNER JOIN VMTRSTAT V on V.MTRL = M.MTRL '
				+'where V.COMPANY='+X.SYS.COMPANY+' and V.SOSOURCE=1351 and M.SODTYPE in (51) '
				+'and V.TRNDATE>=dateadd(day,-'+vdays+',V.TRNDATE) '
				+'group by '+dimsql+' '
				+'having sum(V.SALQTY1)<>0 ';
				
				
		fSQL=fSQL.replace(/"/g,String.fromCharCode(39));

		return SQL2JSON(fSQL);
 }
 else {
   return responseError(Prms);
 }
}
JavaScript

printDocsForm

function printDocsForm(Prms) {
 var resp={};
  
 if (checkClientId(Prms)) {
	var fSQL;
	var orders = Prms.ORDERS;
  var url ='';
	
	if(orders != null){
		orders = orders.replace(/;/g, "','");
		orders = "'"+orders+"'";
		
		fSQL = 'SELECT MTRLINES.FINDOC, FF.FINCODE, B.FINCODE ORDERDOC, S.TEMPLATES '+
						'FROM MTRLINES MTRLINES '+
						'INNER JOIN FINDOC FF  ON FF.FINDOC=MTRLINES.FINDOC AND FF.COMPANY=MTRLINES.COMPANY '+
						'LEFT OUTER JOIN SERIES S ON S.SERIES = FF.SERIES AND S.SOSOURCE=FF.SOSOURCE AND S.COMPANY = FF.COMPANY '+
						'OUTER APPLY (SELECT M.FINDOC , FINCODE '+
															'FROM MTRLINES M '+
															'INNER JOIN FINDOC F ON F.FINDOC=M.FINDOC AND F.COMPANY=M.COMPANY '+
															'WHERE FINCODE IN ('+orders+')  '+
															'AND F.SOSOURCE=1351 '+
															'AND TFPRMS=201) B '+
						'WHERE MTRLINES.FINDOCS = B.FINDOC  '; 
						
		ds = X.GETSQLDATASET(fSQL, null);							
		
		if(ds.RECORDCOUNT >0)
		{
			var resp = {};
			resp.success=true;
			resp.data = [];
			while (!ds.EOF())
			{	
				url = X.PRINTDOCURL('SALDOC', '', ds.FINDOC, ds.TEMPLATES);
				url = ds.ORDERDOC+','+url;
				resp.data.push(url);
				ds.NEXT;
			}

		}
		 else {
			resp.success=true;
			resp.count=0;
			resp.error="result count = 0";
		 }
	 
	 } else {
		resp.success=false;
		resp.count=0;
    resp.error="Invalid parameters. Requested Parameter 'ORDERS'.";
	 }
	
   return (resp);
 }
 else {
   return responseError(Prms);
 }
}
JavaScript

Code Snippets Tags:, , , , , , , , ,

Related Posts

T-SQL Get Table Sizes Code Snippets
Capture Signature Code Snippets
T-SQL Index fragmentation Code Snippets
JS validEmail Code Snippets
Invisible Scheduled Tasks Invisible Scheduled Tasks Code Snippets
T-SQL List Open Transactions Code Snippets

Leave a Reply

Verified by MonsterInsights