SHARE
TWEET

Untitled

a guest Feb 26th, 2020 83 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. const oo = require('openorange');
  2. const cm = oo.classmanager;
  3.  
  4. let Description = {
  5.     filename: __filename,
  6.     name: 'OfidirectReport',
  7.     title: 'Ofidirect Report',
  8.     inherits: 'Report',
  9.     params: {
  10.         Date:{type: "period"},
  11.         ItemGroup:{type: "string"},
  12.         ItemsWithSales: {type: "boolean"},
  13.     },
  14.     form: [
  15.         {type:"vuecomponent",component:"PeriodComponent",colspan:6,field:"Date",label:"Date"},
  16.         {field: "ItemGroup", label: "Grupo de Articulos", pastewindow:"ItemGroupPasteWindow"},
  17.         {field: "ItemsWithSales", name: "ItemsWithSales", label: "Items Without Sales", editor: "checkbox"},
  18.     ]
  19. };
  20.  
  21. let Parent = cm.SuperClass(Description);
  22. class OfidirectReport extends Parent {
  23.  
  24.     async defaults() {
  25.         let specs = this.getRecord();
  26.         specs.Date[0] = null;
  27.         specs.Date[1] = null;
  28.         specs.ItemsWithSales = true;
  29.     }
  30.  
  31.     async check() {
  32.         let spec = this.getRecord();
  33.         if (!spec.Date[0]) {
  34.             return this.getRecord().fieldErrorResponse("NONBLANKERR", 'Date');
  35.         }
  36.         if (!spec.Date[1]) {
  37.             return this.getRecord().fieldErrorResponse("NONBLANKERR", 'Date');
  38.         }
  39.         return true;
  40.     }
  41.  
  42.     getQuery() {
  43.  
  44.         let specs = this.getRecord();
  45.    
  46.         let queryGR = oo.query.select(["i.Code AS Code", "i.Name AS Name", "SUM(gri.UnitQty) AS TotalReceived", "IFNULL(null,0) TotalSales", "i.Unit AS ArtUnit"])
  47.             .from("Item", "i")
  48.             .join(new oo.query.LeftJoinBuilder("GoodsReceiptItemRow","gri").on({"gri.ArtCode": new oo.query.Column('i.Code')}))
  49.             .join(new oo.query.InnerJoinBuilder("GoodsReceipt","gr").on({"gri.masterId": new oo.query.Column('gr.internalId')}))
  50.             .where({"gr.TransDate__between": [specs.Date[0].format("YYYY-MM-DD"), specs.Date[1].format("YYYY-MM-DD")]})
  51.             .groupby(["i.Code"]);
  52.    
  53.         let querySO = oo.query.select(["i.Code AS Code", "i.Name AS Name", "IFNULL(null,0) TotalReceived", "SUM(soi.UnitQty) AS TotalSales", "i.Unit AS ArtUnit"])
  54.             .from("Item", "i")
  55.             .join(new oo.query.LeftJoinBuilder("SalesOrderItemRow","soi").on({"soi.ArtCode": new oo.query.Column('i.Code')}))
  56.             .join(new oo.query.InnerJoinBuilder("SalesOrder","so").on({"soi.masterId": new oo.query.Column('so.internalId')}))
  57.             .where({"so.TransDate__between": [specs.Date[0].format("YYYY-MM-DD"), specs.Date[1].format("YYYY-MM-DD")]})
  58.             .groupby(["i.Code"]);
  59.    
  60.         let queryQty = queryGR.union(querySO);
  61.    
  62.         let tableQuery = oo.query.select(["tab.Code", "tab.Name AS Name", "SUM(TotalReceived) AS TotalReceived","SUM(tab.TotalSales) AS TotalSales","tab.ArtUnit"])
  63.             .from(queryQty, "tab")
  64.             .groupby(["tab.Code"]);
  65.             console.log(tableQuery.previewSQL());
  66.         return tableQuery;
  67.    
  68.     }
  69.  
  70.     async getProcessedData() {
  71.         let query = await this.getQuery().fetch();
  72.         return query;
  73.     }
  74.  
  75.     buildHeader(builder){
  76.         let header = [
  77.             {col:'Code', datacol: 'Code', label: 'ArtCode'},
  78.             {col:'Name', datacol: 'Name', label: 'Name'},
  79.             {col:'TotalReceived', datacol: 'TotalReceived', label: 'TotalReceived'},
  80.             {col:'TotalSales', datacol: 'TotalSales', label: 'TotalSales'},
  81.             {col:'Unity', datacol: 'Unity', label: 'Unity'},
  82.         ];        
  83.         builder.headerRow(header);
  84.     }
  85.  
  86.     buildColumns(builder){
  87.         builder
  88.             .column("Code" , {linkto: {record: 'Item', window: 'ItemWindow'}})
  89.             .column("Name")
  90.             .column("TotalReceived", {
  91.                 cssClass: (row) => 'align-left'
  92.             })
  93.             .column("TotalSales", {
  94.                 cssClass: (row) => 'align-left'
  95.             })
  96.             .column("Unity");
  97.     }
  98.  
  99.     async getData(){
  100.         let builder =  this.newDataBuilder();
  101.         builder.table(OfidirectReport);
  102.         let data = await this.getProcessedData();
  103.         this.buildColumns(builder);
  104.         this.buildHeader(builder);        
  105.         for (let row of data){
  106.             builder.row(
  107.                 {
  108.                     Code: row.Code,
  109.                     Name: row.Name,
  110.                     TotalReceived: row.TotalReceived,
  111.                     TotalSales: row.TotalSales,
  112.                     Unity: row.ArtUnit,
  113.                 }
  114.             );
  115.         }
  116.         return builder;
  117.     }
  118. }
  119.  
  120. module.exports = OfidirectReport.initClass(Description);
  121.  
  122.    
  123. // getQuery(){
  124. //     const specs = this.getRecord();
  125. //     let
  126. //     let query  = oo.query.raw();
  127. //     query.sql = "SELECT i.Code AS Code, i.Name AS Name, IFNULL(SUM(gri.Qty), 0) TotalReceived, i.Unit,"
  128. //     query.sql += "IFNULL((SELECT SUM(soi.Qty)"
  129. //     query.sql += "FROM SalesOrderItemRow soi"
  130. //     query.sql += "INNER JOIN SalesOrder so on soi.masterId = so.internalId  "
  131. //     query.sql += "WHERE soi.ArtCode = i.Code  "
  132. //     query.sql += `AND so.TransDate BETWEEN  '${record.Date[0].format('YYYY-MM-DD')}' AND  '${record.Date[1].format('YYYY-MM-DD')}' `
  133. //     query.sql += "AND so.Status = 1),0) TotalSales"
  134. //     query.sql += "FROM Item i  "
  135. //     query.sql += "LEFT JOIN GoodsReceiptItemRow gri on gri.ArtCode = i.Code  "
  136. //     query.sql += "LEFT JOIN GoodsReceipt gr on gri.masterId = gr.internalId"
  137. //     if query.sql += `WHERE gr.TransDate BETWEEN  '${record.Date[0].format('YYYY-MM-DD')}' AND  '${record.Date[1].format('YYYY-MM-DD')}' `
  138. //     query.sql += "AND gr.Status = 1 AND gr.Invalid = 0   "
  139. //     if (specs.ItemGroup) query.sql += `AND i.ItemGroup = '${record.ItemGroup}' `
  140. //     query.sql += "GROUP BY i.Code"
  141. //     query.sql += "HAVING TotalSales = 0"
  142.  
  143. //     // let query = oo.query
  144. //     //     .select(["so.TransDate AS Fecha","i.Code as Code", "i.Name AS Name", "IFNULL(SUM(soi.Qty), 0) TotalSales",
  145. //     //         "IFNULL(SUM(gri.Qty), 0) TotalReceived", "i.Unit AS Unity"])
  146. //     //     .from("Item", "i")
  147. //     //     .join(new oo.query.LeftJoinBuilder("SalesOrderItemRow", "soi").on({"soi.ArtCode": new oo.query.Column("i.Code")}))
  148. //     //     .join(new oo.query.LeftJoinBuilder("SalesOrder", "so").on({"so.internalId": new oo.query.Column("soi.masterId")}))
  149. //     //     .join(new oo.query.LeftJoinBuilder("GoodsReceiptItemRow", "gri").on({"gri.ArtCode": new oo.query.Column("i.Code")}))
  150. //     //     .join(new oo.query.LeftJoinBuilder("GoodsReceipt", "gr").on({"gr.internalId": new oo.query.Column("gri.masterId")}))
  151. //     //     .where(new oo.query.ConditionBuilder({'so.Status': 1}))
  152. //     // //if(Date) query.and({"so.TransDate__between": [specs.Date[0].format("YYYY-MM-DD"), specs.Date[1].format("YYYY-MM-DD")]})
  153. //     // if(specs.ItemsWithSales) query.and(new oo.query.ConditionBuilder({'soi.Qty': null}));
  154. //     // if(specs.ItemGroup) query.and({"i.ItemGroup": specs.ItemGroup});
  155. //     // query.or(new oo.query.ConditionBuilder({'gr.Status': 1}));
  156. //     // query.groupby("i.Code");
  157. //     // query.orderby("i.Code");
  158. //     // return query;
  159. // }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top