New Report Based on Bin would show which items are needed for Production based on LOGIC

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well

Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show “Give Production”
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show “Give production order for STOCK”
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.

Please help me with the below code, the problem being faced is this 

  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 tabBin.item_code as “IC:Link/Item:150”,
 tabItem.description as “Description::250”,
 tabItem.re_order_level as “ROL::40”,
 tabBin.warehouse as “WH::80”,
 tabBin.reserved_qty as “SO::40”,
 tabBin.ordered_qty as “PO::40”,
 tabBin.planned_qty as “PLAN::40”,
 tabBin.actual_qty as “QTY::40”

from
 tabItem
 left join tabBin on (
   tabItem.name=tabBin.item_code
 )
where
 tabBin.actual_qty <>“”

order by tabBin.item_code asc



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/rdtcbdpPvXIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:

https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well

Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show “Give Production”
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show “Give production order for STOCK”
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.

Please help me with the below code, the problem being faced is this 

  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 tabBin.item_code as “IC:Link/Item:150”,
 tabItem.description as “Description::250”,
 tabItem.re_order_level as “ROL::40”,
 tabBin.warehouse as “WH::80”,
 tabBin.reserved_qty as “SO::40”,
 tabBin.ordered_qty as “PO::40”,
 tabBin.planned_qty as “PLAN::40”,
 tabBin.actual_qty as “QTY::40”

from
 tabItem
 left join tabBin on (
   tabItem.name=tabBin.item_code
 )
where
 tabBin.actual_qty <>“”

order by tabBin.item_code asc



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/7ykAxrKTOZ0J.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:

Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)
4. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.

On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:

https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:
Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well

Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show “Give Production”
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show “Give production order for STOCK”
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.

Please help me with the below code, the problem being faced is this 

  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 tabBin.item_code as “IC:Link/Item:150”,
 tabItem.description as “Description::250”,
 tabItem.re_order_level as “ROL::40”,
 tabBin.warehouse as “WH::80”,
 tabBin.reserved_qty as “SO::40”,
 tabBin.ordered_qty as “PO::40”,
 tabBin.planned_qty as “PLAN::40”,
 tabBin.actual_qty as “QTY::40”

from
 tabItem
 left join tabBin on (
   tabItem.name=tabBin.item_code
 )
where
 tabBin.actual_qty <>“”

order by tabBin.item_code asc



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/doTxGBXHQvAJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.

select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:
Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:

Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)
4. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.

On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:
I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:

https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:
Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well

Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show “Give Production”
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show “Give production order for STOCK”
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.

Please help me with the below code, the problem being faced is this 

  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 tabBin.item_code as “IC:Link/Item:150”,
 tabItem.description as “Description::250”,
 tabItem.re_order_level as “ROL::40”,
 tabBin.warehouse as “WH::80”,
 tabBin.reserved_qty as “SO::40”,
 tabBin.ordered_qty as “PO::40”,
 tabBin.planned_qty as “PLAN::40”,
 tabBin.actual_qty as “QTY::40”

from
 tabItem
 left join tabBin on (
   tabItem.name=tabBin.item_code
 )
where
 tabBin.actual_qty <>“”

order by tabBin.item_code asc



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Hi Aditya,

You might need to take a second look at the labels you have used. They should be exactly as that in the query.

Try using accented quotes in outer query when referring to labels. For eg. `1D:Currency:50`

Also there could be an issue in case syntax.

Without the whole query's context, this is as far as I can guess.

Hope this helps. :)

Thanks,
Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.

select t.*,
       ("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50"
       ) as tot1,
       (case when "1D:Currency:50" + "2B:Currency:50" < "SO:Currency:50"
             then '1 CORD'
             when ("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50" < "SO:Currency:50" + "ROL:Currency:50"
             then  "2 CSTK"
         end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:
Hi,

Finally I have been able to get what I wanted in the report now just a few todo's are left. First the code is on the gist. And the comments on the code are:

Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)
4. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.

On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:
I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:

https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:
Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well

Now what is this report, this report is:

  • Based on "BIN" table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.

Please help me with the below code, the problem being faced is this

  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select
`tabBin`.item_code as "IC:Link/Item:150",
`tabItem`.description as "Description::250",
`tabItem`.re_order_level as "ROL::40",
`tabBin`.warehouse as "WH::80",
`tabBin`.reserved_qty as "SO::40",
`tabBin`.ordered_qty as "PO::40",
`tabBin`.planned_qty as "PLAN::40",
`tabBin`.actual_qty as "QTY::40"

from
`tabItem`
left join `tabBin` on (
`tabItem`.name=`tabBin`.item_code
)
where
`tabBin`.actual_qty <>""

order by `tabBin`.item_code asc



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.






You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Well the  full query is though posted in the gist is being pasted here, now since I am not a programmer I don’t know after which line would my subquery would start.

select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”


 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc


On Saturday, January 12, 2013 9:46:18 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

You might need to take a second look at the labels you have used. They should be exactly as that in the query.

Try using accented quotes in outer query when referring to labels. For eg. `1D:Currency:50`

Also there could be an issue in case syntax.

Without the whole query's context, this is as far as I can guess.

Hope this helps. :)

Thanks,
Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.

select t.*,
       ("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50"
       ) as tot1,
       (case when "1D:Currency:50" + "2B:Currency:50" < "SO:Currency:50"
             then '1 CORD'
             when ("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50" < "SO:Currency:50" + "ROL:Currency:50"
             then  "2 CSTK"
         end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:
Hi,

Finally I have been able to get what I wanted in the report now just a few todo's are left. First the code  is on the gist. And the comments on the code are:

Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)
4. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.

On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:
I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:

https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:
Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well

Now what is this report, this report is:

  • Based on "BIN" table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.

Please help me with the below code, the problem being faced is this 

  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/_NmZBDbzWJ8J.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.
`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Currency:50`
) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,
Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.

select t.*,
       ("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50"
       ) as tot1,
       (case when "1D:Currency:50" + "2B:Currency:50" < "SO:Currency:50"
             then '1 CORD'
             when ("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50" < "SO:Currency:50" + "ROL:Currency:50"
             then  "2 CSTK"
         end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:
Hi,

Finally I have been able to get what I wanted in the report now just a few todo's are left. First the code is on the gist. And the comments on the code are:

Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)
4. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.

On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:
I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:

https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:
Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well

Now what is this report, this report is:

  • Based on "BIN" table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.

Please help me with the below code, the problem being faced is this

  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select
`tabBin`.item_code as "IC:Link/Item:150",
`tabItem`.description as "Description::250",
`tabItem`.re_order_level as "ROL::40",
`tabBin`.warehouse as "WH::80",
`tabBin`.reserved_qty as "SO::40",
`tabBin`.ordered_qty as "PO::40",
`tabBin`.planned_qty as "PLAN::40",
`tabBin`.actual_qty as "QTY::40"

from
`tabItem`
left join `tabBin` on (
`tabItem`.name=`tabBin`.item_code
)
where
`tabBin`.actual_qty <>""

order by `tabBin`.item_code asc



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.






You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
  File “…/lib/webnotes/handler.py”, line 159, in handle
    execute_cmd(cmd)
  File “…/lib/webnotes/handler.py”, line 192, in execute_cmd
    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run
    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(select t.,\n (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Cu’ at line 45")

The error is coming right before we start the sub-query

The code being used is this:

select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”

 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc

 (select t.
,
 (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’) as tot1,
 (case when ‘1D:Currency:50’ + ‘2B:Currency:50’ < SO:Currency:50’ then “1 CORD” when (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’ < ‘SO:Currency:50’ + ‘ROL:Currency:50’ then  “2 CSTK” end)
from (
select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”

 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>”"
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc
) t


On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.
`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Currency:50`
       ) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,
Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.

select t.*,
       ("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50"
       ) as tot1,
       (case when "1D:Currency:50" + "2B:Currency:50" < "SO:Currency:50"
             then '1 CORD'
             when ("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50" < "SO:Currency:50" + "ROL:Currency:50"
             then  "2 CSTK"
         end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:
Hi,

Finally I have been able to get what I wanted in the report now just a few todo's are left. First the code  is on the gist. And the comments on the code are:

Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)
4. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.

On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:
I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:

https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:
Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well

Now what is this report, this report is:

  • Based on "BIN" table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.

Please help me with the below code, the problem being faced is this 

  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Hi Aditya,

Use accented quotes and not single quotes.
Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,
Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
File "../lib/webnotes/handler.py", line 159, in handle
execute_cmd(cmd)
File "../lib/webnotes/handler.py", line 192, in execute_cmd
ret = call(method, webnotes.form_dict)
File "../lib/webnotes/handler.py", line 211, in call
return fn(**newargs)
File "../lib/webnotes/widgets/query_report.py", line 44, in run
result = [list(t) for t in webnotes.conn.sql(query)]
File "../lib/webnotes/db.py", line 111, in sql
raise e
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n ('1D:Currency:50' + '2B:Currency:50' + 'BRG:Currency:50' + 'BHT:Cu' at line 45")

The error is coming right before we start the sub-query

The code being used is this:

select
`tabItem`.name as "Item Code:Link/Item:150",
`tabItem`.description as "Description::300",
ifnull(`tabItem`.re_order_level,0) as "ROL:Currency:50",
ifnull(`tabBin`.reserved_qty,0) as "SO:Currency:50",
ifnull(`tabBin`.ordered_qty,0) as "PO:Currency:50",

ifnull(min(case when `tabBin`.warehouse="DEL20A" then `tabBin`.actual_qty end),0) as "1D:Currency:50",

ifnull(min(case when `tabBin`.warehouse="BGH655" then `tabBin`.actual_qty end),0) as "2B:Currency:50",

ifnull(min(case when `tabBin`.warehouse="RG-BGH655" then `tabBin`.actual_qty end),0) as "BRG:Currency:50",

ifnull(min(case when `tabBin`.warehouse="HT-BGH655" then `tabBin`.actual_qty end),0) as "BHT:Currency:50",

ifnull(min(case when `tabBin`.warehouse="FG-BGH655" then `tabBin`.actual_qty end),0) as "BFG:Currency:50",

ifnull(min(case when `tabBin`.warehouse="SLIT-DEL20A" then `tabBin`.actual_qty end),0) as "DSL:Currency:50",

ifnull(min(case when `tabBin`.warehouse="RG-DEL20A" then `tabBin`.actual_qty end),0) as "DRG:Currency:50",

ifnull(min(case when `tabBin`.warehouse="FG-DEL20A" then `tabBin`.actual_qty end),0) as "DFG:Currency:50",

ifnull(min(case when `tabBin`.warehouse="TEST-DEL20A" then `tabBin`.actual_qty end),0) as "DTS:Currency:50",

ifnull(min(case when `tabBin`.warehouse="REJ-DEL20A" then `tabBin`.actual_qty end),0) as "DRJ:Currency:50",

ifnull(min(case when `tabBin`.warehouse="RM-DEL20A" then `tabBin`.actual_qty end),0) as "DRM:Currency:50",

ifnull(min(case when `tabBin`.warehouse="RM-BGH655" then `tabBin`.actual_qty end),0) as "BRM:Currency:50"

from
`tabItem`
left join `tabBin` on
`tabItem`.name = `tabBin`.item_code
where
`tabBin`.item_code <>""
and `tabBin`.item_code NOT REGEXP '^CN'
and `tabBin`.item_code NOT REGEXP '^J'
and `tabBin`.item_code = `tabItem`.name

group by `tabItem`.name
order by `tabItem`.name asc

(select t.*,
('1D:Currency:50' + '2B:Currency:50' + 'BRG:Currency:50' + 'BHT:Currency:50') as tot1,
(case when '1D:Currency:50' + '2B:Currency:50' < SO:Currency:50' then "1 CORD" when ('1D:Currency:50' + '2B:Currency:50' + 'BRG:Currency:50' + 'BHT:Currency:50' < 'SO:Currency:50' + 'ROL:Currency:50' then "2 CSTK" end)
from (
select
`tabItem`.name as "Item Code:Link/Item:150",
`tabItem`.description as "Description::300",
ifnull(`tabItem`.re_order_level,0) as "ROL:Currency:50",
ifnull(`tabBin`.reserved_qty,0) as "SO:Currency:50",
ifnull(`tabBin`.ordered_qty,0) as "PO:Currency:50",

ifnull(min(case when `tabBin`.warehouse="DEL20A" then `tabBin`.actual_qty end),0) as "1D:Currency:50",

ifnull(min(case when `tabBin`.warehouse="BGH655" then `tabBin`.actual_qty end),0) as "2B:Currency:50",

ifnull(min(case when `tabBin`.warehouse="RG-BGH655" then `tabBin`.actual_qty end),0) as "BRG:Currency:50",

ifnull(min(case when `tabBin`.warehouse="HT-BGH655" then `tabBin`.actual_qty end),0) as "BHT:Currency:50",

ifnull(min(case when `tabBin`.warehouse="FG-BGH655" then `tabBin`.actual_qty end),0) as "BFG:Currency:50",

ifnull(min(case when `tabBin`.warehouse="SLIT-DEL20A" then `tabBin`.actual_qty end),0) as "DSL:Currency:50",

ifnull(min(case when `tabBin`.warehouse="RG-DEL20A" then `tabBin`.actual_qty end),0) as "DRG:Currency:50",

ifnull(min(case when `tabBin`.warehouse="FG-DEL20A" then `tabBin`.actual_qty end),0) as "DFG:Currency:50",

ifnull(min(case when `tabBin`.warehouse="TEST-DEL20A" then `tabBin`.actual_qty end),0) as "DTS:Currency:50",

ifnull(min(case when `tabBin`.warehouse="REJ-DEL20A" then `tabBin`.actual_qty end),0) as "DRJ:Currency:50",

ifnull(min(case when `tabBin`.warehouse="RM-DEL20A" then `tabBin`.actual_qty end),0) as "DRM:Currency:50",

ifnull(min(case when `tabBin`.warehouse="RM-BGH655" then `tabBin`.actual_qty end),0) as "BRM:Currency:50"

from
`tabItem`
left join `tabBin` on
`tabItem`.name = `tabBin`.item_code
where
`tabBin`.item_code <>""
and `tabBin`.item_code NOT REGEXP '^CN'
and `tabBin`.item_code NOT REGEXP '^J'
and `tabBin`.item_code = `tabItem`.name

group by `tabItem`.name
order by `tabItem`.name asc
) t



On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.
`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Currency:50`
) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,
Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50"
) as tot1,
(case when "1D:Currency:50" + "2B:Currency:50" < "SO:Currency:50"
then '1 CORD'
when ("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50" < "SO:Currency:50" + "ROL:Currency:50"
then "2 CSTK"
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo's are left. First the code is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on "BIN" table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select
`tabBin`.item_code as "IC:Link/Item:150",
`tabItem`.description as "Description::250",
`tabItem`.re_order_level as "ROL::40",
`tabBin`.warehouse as "WH::80",
`tabBin`.reserved_qty as "SO::40",
`tabBin`.ordered_qty as "PO::40",
`tabBin`.planned_qty as "PLAN::40",
`tabBin`.actual_qty as "QTY::40"

from
`tabItem`
left join `tabBin` on (
`tabItem`.name=`tabBin`.item_code
)
where
`tabBin`.actual_qty <>""

order by `tabBin`.item_code asc



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.






You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.






You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

:frowning:

Sorry for my bad in using single quotes, I have changed the quote to accented quotes but still getting the same error:

[10:38:28.569] Traceback (innermost last):
  File “…/lib/webnotes/handler.py”, line 159, in handle
    execute_cmd(cmd)
  File “…/lib/webnotes/handler.py”, line 192, in execute_cmd
    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run
    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(select t.*,\n (1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + `BHT:Cu’ at line 45”)


On Saturday, January 12, 2013 10:35:31 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

Use accented quotes and not single quotes.
Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,
Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 159, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 192, in execute_cmd
    ret = call(method, webnotes.form_dict)
  File "../lib/webnotes/handler.py", line 211, in call
    return fn(**newargs)
  File "../lib/webnotes/widgets/query_report.py", line 44, in run
    result = [list(t) for t in webnotes.conn.sql(query)]
  File "../lib/webnotes/db.py", line 111, in sql
    raise e
 ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n ('1D:Currency:50' + '2B:Currency:50' + 'BRG:Currency:50' + 'BHT:Cu' at line 45")

The error is coming right before we start the sub-query

The code being used is this:

select
 `tabItem`.name as "Item Code:Link/Item:150",
 `tabItem`.description as "Description::300",
 ifnull(`tabItem`.re_order_level,0) as "ROL:Currency:50",
 ifnull(`tabBin`.reserved_qty,0) as "SO:Currency:50",
 ifnull(`tabBin`.ordered_qty,0) as "PO:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="DEL20A" then `tabBin`.actual_qty end),0) as "1D:Currency:50",
 
 ifnull(min(case when `tabBin`.warehouse="BGH655" then `tabBin`.actual_qty end),0) as "2B:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="RG-BGH655" then `tabBin`.actual_qty end),0) as "BRG:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="HT-BGH655" then `tabBin`.actual_qty end),0) as "BHT:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="FG-BGH655" then `tabBin`.actual_qty end),0) as "BFG:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="SLIT-DEL20A" then `tabBin`.actual_qty end),0) as "DSL:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="RG-DEL20A" then `tabBin`.actual_qty end),0) as "DRG:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="FG-DEL20A" then `tabBin`.actual_qty end),0) as "DFG:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="TEST-DEL20A" then `tabBin`.actual_qty end),0) as "DTS:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="REJ-DEL20A" then `tabBin`.actual_qty end),0) as "DRJ:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="RM-DEL20A" then `tabBin`.actual_qty end),0) as "DRM:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="RM-BGH655" then `tabBin`.actual_qty end),0) as "BRM:Currency:50"

 from
 `tabItem`
 left join `tabBin` on
 `tabItem`.name = `tabBin`.item_code
where
 `tabBin`.item_code <>""
 and `tabBin`.item_code NOT REGEXP '^CN'
 and `tabBin`.item_code NOT REGEXP '^J'
 and `tabBin`.item_code = `tabItem`.name

group by `tabItem`.name
order by `tabItem`.name asc

 (select t.*,
 ('1D:Currency:50' + '2B:Currency:50' + 'BRG:Currency:50' + 'BHT:Currency:50') as tot1,
 (case when '1D:Currency:50' + '2B:Currency:50' < SO:Currency:50' then "1 CORD" when ('1D:Currency:50' + '2B:Currency:50' + 'BRG:Currency:50' + 'BHT:Currency:50' < 'SO:Currency:50' + 'ROL:Currency:50' then  "2 CSTK" end)
from (
select
 `tabItem`.name as "Item Code:Link/Item:150",
 `tabItem`.description as "Description::300",
 ifnull(`tabItem`.re_order_level,0) as "ROL:Currency:50",
 ifnull(`tabBin`.reserved_qty,0) as "SO:Currency:50",
 ifnull(`tabBin`.ordered_qty,0) as "PO:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="DEL20A" then `tabBin`.actual_qty end),0) as "1D:Currency:50",
 
 ifnull(min(case when `tabBin`.warehouse="BGH655" then `tabBin`.actual_qty end),0) as "2B:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="RG-BGH655" then `tabBin`.actual_qty end),0) as "BRG:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="HT-BGH655" then `tabBin`.actual_qty end),0) as "BHT:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="FG-BGH655" then `tabBin`.actual_qty end),0) as "BFG:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="SLIT-DEL20A" then `tabBin`.actual_qty end),0) as "DSL:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="RG-DEL20A" then `tabBin`.actual_qty end),0) as "DRG:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="FG-DEL20A" then `tabBin`.actual_qty end),0) as "DFG:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="TEST-DEL20A" then `tabBin`.actual_qty end),0) as "DTS:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="REJ-DEL20A" then `tabBin`.actual_qty end),0) as "DRJ:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="RM-DEL20A" then `tabBin`.actual_qty end),0) as "DRM:Currency:50",

 ifnull(min(case when `tabBin`.warehouse="RM-BGH655" then `tabBin`.actual_qty end),0) as "BRM:Currency:50"

 from
 `tabItem`
 left join `tabBin` on
 `tabItem`.name = `tabBin`.item_code
where
 `tabBin`.item_code <>""
 and `tabBin`.item_code NOT REGEXP '^CN'
 and `tabBin`.item_code NOT REGEXP '^J'
 and `tabBin`.item_code = `tabItem`.name

group by `tabItem`.name
order by `tabItem`.name asc
) t



On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.
`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Currency:50`
       ) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,
Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this 


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ovOFkimy_skJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Dear Aditya,

I have forked your gist and updated the existing query with a syntactically correct one.

This is working fine for me.

Thanks,
Anand.

PS: for a gist, always give an extension. In your case, you should use items_for_production.sql - github will use syntax highlighting as per SQL and it becomes very easy to find mistakes :)


On Sat, Jan 12, 2013 at 10:42 AM, Aditya Duggal <ad...@gmail.com> wrote:
:(

Sorry for my bad in using single quotes, I have changed the quote to accented quotes but still getting the same error:


[10:38:28.569] Traceback (innermost last):


File "…/lib/webnotes/handler.py", line 159, in handle
execute_cmd(cmd)
File "…/lib/webnotes/handler.py", line 192, in execute_cmd

ret = call(method, webnotes.form_dict)<br>  File &quot;../lib/webnotes/handler.py&quot;, line 211, in call<br>    return fn(**newargs)<br>  File &quot;../lib/webnotes/widgets/query_report.py&quot;, line 44, in run<br>

result = [list(t) for t in webnotes.conn.sql(query)]<br>  File &quot;../lib/webnotes/db.py&quot;, line 111, in sql<br>    raise e<br> ProgrammingError: (1064, &quot;You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;(select t.*,\n (`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Cu&#39; at line 45&quot;)<br>



On Saturday, January 12, 2013 10:35:31 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

Use accented quotes and not single quotes.


Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,
Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
File "../lib/webnotes/handler.py", line 159, in handle
execute_cmd(cmd)
File "../lib/webnotes/handler.py", line 192, in execute_cmd
ret = call(method, webnotes.form_dict)<br>  File &quot;../lib/webnotes/handler.py&quot;, line 211, in call<br>    return fn(**newargs)<br>  File &quot;../lib/webnotes/widgets/<u></u>query_report.py&quot;, line 44, in run<br>


result = [list(t) for t in webnotes.conn.sql(query)]<br>  File &quot;../lib/webnotes/db.py&quot;, line 111, in sql<br>    raise e<br> ProgrammingError: (1064, &quot;You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;(select t.*,\n (&#39;1D:Currency:50&#39; + &#39;2B:Currency:50&#39; + &#39;BRG:Currency:50&#39; + &#39;BHT:Cu&#39; at line 45&quot;)<br>


The error is coming right before we start the sub-query

The code being used is this:

select
tabItem.name as "Item Code:Link/Item:150",
tabItem.description as "Description::300",

ifnull(tabItem.re_order_level,0) as "ROL:Currency:50",
ifnull(tabBin.reserved_qty,0) as "SO:Currency:50",
ifnull(tabBin.ordered_qty,0) as "PO:Currency:50",


ifnull(min(case when tabBin.warehouse="DEL20A" then tabBin.actual_qty end),0) as "1D:Currency:50",


ifnull(min(case when tabBin.warehouse="BGH655" then tabBin.actual_qty end),0) as "2B:Currency:50",

ifnull(min(case when tabBin.warehouse="RG-BGH655" then tabBin.actual_qty end),0) as "BRG:Currency:50",


ifnull(min(case when tabBin.warehouse="HT-BGH655" then tabBin.actual_qty end),0) as "BHT:Currency:50",

ifnull(min(case when tabBin.warehouse="FG-BGH655" then tabBin.actual_qty end),0) as "BFG:Currency:50",


ifnull(min(case when tabBin.warehouse="SLIT-DEL20A" then tabBin.actual_qty end),0) as "DSL:Currency:50",

ifnull(min(case when tabBin.warehouse="RG-DEL20A" then tabBin.actual_qty end),0) as "DRG:Currency:50",


ifnull(min(case when tabBin.warehouse="FG-DEL20A" then tabBin.actual_qty end),0) as "DFG:Currency:50",

ifnull(min(case when tabBin.warehouse="TEST-DEL20A" then tabBin.actual_qty end),0) as "DTS:Currency:50",


ifnull(min(case when tabBin.warehouse="REJ-DEL20A" then tabBin.actual_qty end),0) as "DRJ:Currency:50",

ifnull(min(case when tabBin.warehouse="RM-DEL20A" then tabBin.actual_qty end),0) as "DRM:Currency:50",


ifnull(min(case when tabBin.warehouse="RM-BGH655" then tabBin.actual_qty end),0) as "BRM:Currency:50"

from
tabItem
left join tabBin on
tabItem.name = tabBin.item_code

where
tabBin.item_code <>""
and tabBin.item_code NOT REGEXP '^CN'
and tabBin.item_code NOT REGEXP '^J'
and tabBin.item_code = tabItem.name

group by tabItem.name

order by tabItem.name asc

(select t.*,
('1D:Currency:50' + '2B:Currency:50' + 'BRG:Currency:50' + 'BHT:Currency:50') as tot1,
(case when '1D:Currency:50' + '2B:Currency:50' < SO:Currency:50' then "1 CORD" when ('1D:Currency:50' + '2B:Currency:50' + 'BRG:Currency:50' + 'BHT:Currency:50' < 'SO:Currency:50' + 'ROL:Currency:50' then "2 CSTK" end)

from (
select
tabItem.name as "Item Code:Link/Item:150",
tabItem.description as "Description::300",
ifnull(tabItem.re_order_level,0) as "ROL:Currency:50",
ifnull(tabBin.reserved_qty,0) as "SO:Currency:50",

ifnull(tabBin.ordered_qty,0) as "PO:Currency:50",

ifnull(min(case when tabBin.warehouse="DEL20A" then tabBin.actual_qty end),0) as "1D:Currency:50",

ifnull(min(case when tabBin.warehouse="BGH655" then tabBin.actual_qty end),0) as "2B:Currency:50",


ifnull(min(case when tabBin.warehouse="RG-BGH655" then tabBin.actual_qty end),0) as "BRG:Currency:50",

ifnull(min(case when tabBin.warehouse="HT-BGH655" then tabBin.actual_qty end),0) as "BHT:Currency:50",


ifnull(min(case when tabBin.warehouse="FG-BGH655" then tabBin.actual_qty end),0) as "BFG:Currency:50",

ifnull(min(case when tabBin.warehouse="SLIT-DEL20A" then tabBin.actual_qty end),0) as "DSL:Currency:50",


ifnull(min(case when tabBin.warehouse="RG-DEL20A" then tabBin.actual_qty end),0) as "DRG:Currency:50",

ifnull(min(case when tabBin.warehouse="FG-DEL20A" then tabBin.actual_qty end),0) as "DFG:Currency:50",


ifnull(min(case when tabBin.warehouse="TEST-DEL20A" then tabBin.actual_qty end),0) as "DTS:Currency:50",

ifnull(min(case when tabBin.warehouse="REJ-DEL20A" then tabBin.actual_qty end),0) as "DRJ:Currency:50",


ifnull(min(case when tabBin.warehouse="RM-DEL20A" then tabBin.actual_qty end),0) as "DRM:Currency:50",

ifnull(min(case when tabBin.warehouse="RM-BGH655" then tabBin.actual_qty end),0) as "BRM:Currency:50"


from
tabItem
left join tabBin on
tabItem.name = tabBin.item_code
where
tabBin.item_code <>""
and tabBin.item_code NOT REGEXP '^CN'
and tabBin.item_code NOT REGEXP '^J'

and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc
) t


On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.
`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Currency:50`
) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,
Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50"
) as tot1,
(case when "1D:Currency:50" + "2B:Currency:50" < "SO:Currency:50"
then '1 CORD'
when ("1D:Currency:50" + "2B:Currency:50" + "BRG:Currency:50" + "BHT:Currency:50" < "SO:Currency:50" + "ROL:Currency:50"
then "2 CSTK"
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo's are left. First the code is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on "BIN" table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select
`tabBin`.item_code as "IC:Link/Item:150",
`tabItem`.description as "Description::250",
`tabItem`.re_order_level as "ROL::40",
`tabBin`.warehouse as "WH::80",
`tabBin`.reserved_qty as "SO::40",
`tabBin`.ordered_qty as "PO::40",
`tabBin`.planned_qty as "PLAN::40",
`tabBin`.actual_qty as "QTY::40"

from
`tabItem`
left join `tabBin` on (
`tabItem`.name=`tabBin`.item_code
)
where
`tabBin`.actual_qty <>""

order by `tabBin`.item_code asc



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.






You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ovOFkimy_skJ.


For more options, visit https://groups.google.com/groups/opt_out.





You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Thanks for the help.

On Saturday, January 12, 2013 11:35:15 AM UTC+5:30, Anand Doshi wrote:

Dear Aditya,

I have forked your gist and updated the existing query with a syntactically correct one.


This is working fine for me.

Thanks,
Anand.

PS: for a gist, always give an extension. In your case, you should use items_for_production.sql - github will use syntax highlighting as per SQL and it becomes very easy to find mistakes :)


On Sat, Jan 12, 2013 at 10:42 AM, Aditya Duggal <ad...@gmail.com> wrote:
:(

Sorry for my bad in using single quotes, I have changed the quote to accented quotes but still getting the same error:


[10:38:28.569] Traceback (innermost last):


  File “…/lib/webnotes/handler.py”, line 159, in handle
    execute_cmd(cmd)
  File “…/lib/webnotes/handler.py”, line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(select t.*,\n (1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + `BHT:Cu’ at line 45”)



On Saturday, January 12, 2013 10:35:31 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

Use accented quotes and not single quotes.


Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,
Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 159, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Cu’ at line 45”)


The error is coming right before we start the sub-query

The code being used is this:

select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,

 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,

 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”

 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code

where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name

order by tabItem.name asc

 (select t.*,
 (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’) as tot1,
 (case when ‘1D:Currency:50’ + ‘2B:Currency:50’ < SO:Currency:50’ then “1 CORD” when (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’ < ‘SO:Currency:50’ + ‘ROL:Currency:50’ then  “2 CSTK” end)

from (
select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,

 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”


 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’

 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc
) t


On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.
`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Currency:50`
       ) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,
Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this 


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ovOFkimy_skJ.


For more options, visit https://groups.google.com/groups/opt_out.
 
 



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/QT90m4lZXdcJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Hi Anand,

The code that you provided is working beautifully, but for one thing. The problem is that when we create a new Item Code then for that Item a BIN is not created in the BIN table and hence that item is not shown in the report even if there are pending orders for that report.

Now the easiest way would be to have a LEFT JOIN of Item table with the BIN table, which as per my limited knowledge would show all the distinct values of Item Table, that is, Item Code.

But now the more perplexing question is that I was under the impression that I have already done a LEFT JOIN with Item Table on left side with the BIN table and if that is the case then can you let me know why am I missing some item codes (especially which don’t have any entry in the BIN table.

I am kind of confused.

On Saturday, January 12, 2013 12:20:29 PM UTC+5:30, Aditya Duggal wrote:

Thanks for the help.

On Saturday, January 12, 2013 11:35:15 AM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

I have forked your gist and updated the existing query with a syntactically correct one.


This is working fine for me.

Thanks,
Anand.

PS: for a gist, always give an extension. In your case, you should use items_for_production.sql - github will use syntax highlighting as per SQL and it becomes very easy to find mistakes :)


On Sat, Jan 12, 2013 at 10:42 AM, Aditya Duggal <ad...@gmail.com> wrote:
:(

Sorry for my bad in using single quotes, I have changed the quote to accented quotes but still getting the same error:


[10:38:28.569] Traceback (innermost last):


  File “…/lib/webnotes/handler.py”, line 159, in handle
    execute_cmd(cmd)
  File “…/lib/webnotes/handler.py”, line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(select t.*,\n (1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + `BHT:Cu’ at line 45”)



On Saturday, January 12, 2013 10:35:31 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

Use accented quotes and not single quotes.


Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,
Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 159, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Cu’ at line 45”)


The error is coming right before we start the sub-query

The code being used is this:

select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,

 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,

 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”

 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code

where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name

order by tabItem.name asc

 (select t.*,
 (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’) as tot1,
 (case when ‘1D:Currency:50’ + ‘2B:Currency:50’ < SO:Currency:50’ then “1 CORD” when (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’ < ‘SO:Currency:50’ + ‘ROL:Currency:50’ then  “2 CSTK” end)

from (
select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,

 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”


 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’

 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc
) t


On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.
`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Currency:50`
       ) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,
Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this 


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ovOFkimy_skJ.


For more options, visit https://groups.google.com/groups/opt_out.
 
 



You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/T5yV1eefx7AJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Dear Aditya,

Currently, if a stock transaction is done for that item, only the a BIN is created for that new item.

Hence, if there is no stock transaction against the item, it doesn't need a bin and you can ignore it.

Thanks,
Anand.

On 15-Jan-2013, at 12:26 PM, Aditya Duggal <ad...@gmail.com> wrote:

Hi Anand,

The code that you provided is working beautifully, but for one thing. The problem is that when we create a new Item Code then for that Item a BIN is not created in the BIN table and hence that item is not shown in the report even if there are pending orders for that report.

Now the easiest way would be to have a LEFT JOIN of Item table with the BIN table, which as per my limited knowledge would show all the distinct values of Item Table, that is, Item Code.

But now the more perplexing question is that I was under the impression that I have already done a LEFT JOIN with Item Table on left side with the BIN table and if that is the case then can you let me know why am I missing some item codes (especially which don't have any entry in the BIN table.

I am kind of confused.

On Saturday, January 12, 2013 12:20:29 PM UTC+5:30, Aditya Duggal wrote:
Thanks for the help.

On Saturday, January 12, 2013 11:35:15 AM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

I have forked your gist and updated the existing query with a syntactically correct one.

This is working fine for me.

Thanks,
Anand.

PS: for a gist, always give an extension. In your case, you should use items_for_production.sql - github will use syntax highlighting as per SQL and it becomes very easy to find mistakes :)


On Sat, Jan 12, 2013 at 10:42 AM, Aditya Duggal <ad...@gmail.com> wrote:
:(

Sorry for my bad in using single quotes, I have changed the quote to accented quotes but still getting the same error:


[10:38:28.569] Traceback (innermost last):


  File “…/lib/webnotes/handler.py”, line 159, in handle
    execute_cmd(cmd)
  File “…/lib/webnotes/handler.py”, line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(select t.*,\n (1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + `BHT:Cu’ at line 45”)



On Saturday, January 12, 2013 10:35:31 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

Use accented quotes and not single quotes.



Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,

Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 159, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Cu’ at line 45”)


The error is coming right before we start the sub-query

The code being used is this:

select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,

 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,

 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”

 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code

where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name

order by tabItem.name asc

 (select t.*,
 (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’) as tot1,
 (case when ‘1D:Currency:50’ + ‘2B:Currency:50’ < SO:Currency:50’ then “1 CORD” when (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’ < ‘SO:Currency:50’ + ‘ROL:Currency:50’ then  “2 CSTK” end)

from (
select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,

 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”


 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’

 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc
) t


On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.

1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + BHT:Currency:50

       ) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,

Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this 


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ovOFkimy_skJ.


For more options, visit https://groups.google.com/groups/opt_out.
 
 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/T5yV1eefx7AJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Dear Anand,

I guess you I have not explained the problem properly. Let me try again:

The intention of making this report in discussion is that it show all items irrespective of whether they have a BIN transaction (that is whether we have done a stock transaction or not). The reason for that is as follows:

  1. If we get an order for an item for which we don’t have an item code, then what anyone would do is first create a ITEM CODE. Now understand the real life situation, that there is a SO or order booked in name of this item.
  2. Now till now there is no stock transaction, hence there is nothing about this item in BIN table.
  3. But since this report is more of a STOCK LEVEL report which would also show items on the ORDER, hence we need to show all items in the ITEM table, irrespective of the fact that there is any BIN entry or not.
  4. So in present form the report generally tends to miss the newly created items since there is no BIN transaction.
  5. What I want is that as soon as an item code is generated in the system and if we refresh this report, then the item code should show up in this report.
  6. For the above to be true, I said that we need a left join with item table on left side but I guess either the join is not proper or there is something else which I am not aware of.


If any of you have time, I could show you how this report works and I guess this would be a good report for a STANDARD product, infact I have seen the STOCK LEVEL report and I this report is more useful than that report and also it tells me which items to produce and when. Now there are some small glitches left which don’t render this report useless or less important.



On Tuesday, January 15, 2013 12:28:12 PM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

Currently, if a stock transaction is done for that item, only the a BIN is created for that new item.

Hence, if there is no stock transaction against the item, it doesn’t need a bin and you can ignore it.

Thanks,
Anand.

On 15-Jan-2013, at 12:26 PM, Aditya Duggal <ad…@gmail.com> wrote:

Hi Anand,

The code that you provided is working beautifully, but for one thing. The problem is that when we create a new Item Code then for that Item a BIN is not created in the BIN table and hence that item is not shown in the report even if there are pending orders for that report.

Now the easiest way would be to have a LEFT JOIN of Item table with the BIN table, which as per my limited knowledge would show all the distinct values of Item Table, that is, Item Code.

But now the more perplexing question is that I was under the impression that I have already done a LEFT JOIN with Item Table on left side with the BIN table and if that is the case then can you let me know why am I missing some item codes (especially which don’t have any entry in the BIN table.

I am kind of confused.

On Saturday, January 12, 2013 12:20:29 PM UTC+5:30, Aditya Duggal wrote:
Thanks for the help.

On Saturday, January 12, 2013 11:35:15 AM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

I have forked your gist and updated the existing query with a syntactically correct one.


This is working fine for me.

Thanks,
Anand.

PS: for a gist, always give an extension. In your case, you should use items_for_production.sql - github will use syntax highlighting as per SQL and it becomes very easy to find mistakes :)


On Sat, Jan 12, 2013 at 10:42 AM, Aditya Duggal <ad...@gmail.com> wrote:
:(

Sorry for my bad in using single quotes, I have changed the quote to accented quotes but still getting the same error:


[10:38:28.569] Traceback (innermost last):


  File “…/lib/webnotes/handler.py”, line 159, in handle
    execute_cmd(cmd)
  File “…/lib/webnotes/handler.py”, line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(select t.*,\n (1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + `BHT:Cu’ at line 45”)



On Saturday, January 12, 2013 10:35:31 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

Use accented quotes and not single quotes.



Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,

Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 159, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Cu’ at line 45”)


The error is coming right before we start the sub-query

The code being used is this:

select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,

 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,

 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”

 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code

where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name

order by tabItem.name asc

 (select t.*,
 (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’) as tot1,
 (case when ‘1D:Currency:50’ + ‘2B:Currency:50’ < SO:Currency:50’ then “1 CORD” when (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’ < ‘SO:Currency:50’ + ‘ROL:Currency:50’ then  “2 CSTK” end)

from (
select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,

 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”


 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’

 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc
) t


On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.

1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + BHT:Currency:50

       ) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,

Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this 


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ovOFkimy_skJ.


For more options, visit https://groups.google.com/groups/opt_out.
 
 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/T5yV1eefx7AJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/fwg5-4IxsvAJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Is there a way to define a VARIABLE in the query reports?


I am trying to define a variable from the columns, basically to shorten my code but I am unable to define the variable.

On Tuesday, January 15, 2013 2:37:13 PM UTC+5:30, Aditya Duggal wrote:
Dear Anand,

I guess you I have not explained the problem properly. Let me try again:

The intention of making this report in discussion is that it show all items irrespective of whether they have a BIN transaction (that is whether we have done a stock transaction or not). The reason for that is as follows:

  1. If we get an order for an item for which we don’t have an item code, then what anyone would do is first create a ITEM CODE. Now understand the real life situation, that there is a SO or order booked in name of this item.
  2. Now till now there is no stock transaction, hence there is nothing about this item in BIN table.
  3. But since this report is more of a STOCK LEVEL report which would also show items on the ORDER, hence we need to show all items in the ITEM table, irrespective of the fact that there is any BIN entry or not.
  4. So in present form the report generally tends to miss the newly created items since there is no BIN transaction.
  5. What I want is that as soon as an item code is generated in the system and if we refresh this report, then the item code should show up in this report.
  6. For the above to be true, I said that we need a left join with item table on left side but I guess either the join is not proper or there is something else which I am not aware of.


If any of you have time, I could show you how this report works and I guess this would be a good report for a STANDARD product, infact I have seen the STOCK LEVEL report and I this report is more useful than that report and also it tells me which items to produce and when. Now there are some small glitches left which don’t render this report useless or less important.



On Tuesday, January 15, 2013 12:28:12 PM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

Currently, if a stock transaction is done for that item, only the a BIN is created for that new item.

Hence, if there is no stock transaction against the item, it doesn’t need a bin and you can ignore it.

Thanks,
Anand.

On 15-Jan-2013, at 12:26 PM, Aditya Duggal <ad…@gmail.com> wrote:

Hi Anand,

The code that you provided is working beautifully, but for one thing. The problem is that when we create a new Item Code then for that Item a BIN is not created in the BIN table and hence that item is not shown in the report even if there are pending orders for that report.

Now the easiest way would be to have a LEFT JOIN of Item table with the BIN table, which as per my limited knowledge would show all the distinct values of Item Table, that is, Item Code.

But now the more perplexing question is that I was under the impression that I have already done a LEFT JOIN with Item Table on left side with the BIN table and if that is the case then can you let me know why am I missing some item codes (especially which don’t have any entry in the BIN table.

I am kind of confused.

On Saturday, January 12, 2013 12:20:29 PM UTC+5:30, Aditya Duggal wrote:
Thanks for the help.

On Saturday, January 12, 2013 11:35:15 AM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

I have forked your gist and updated the existing query with a syntactically correct one.


This is working fine for me.

Thanks,
Anand.

PS: for a gist, always give an extension. In your case, you should use items_for_production.sql - github will use syntax highlighting as per SQL and it becomes very easy to find mistakes :)


On Sat, Jan 12, 2013 at 10:42 AM, Aditya Duggal <ad...@gmail.com> wrote:
:(

Sorry for my bad in using single quotes, I have changed the quote to accented quotes but still getting the same error:


[10:38:28.569] Traceback (innermost last):


  File “…/lib/webnotes/handler.py”, line 159, in handle
    execute_cmd(cmd)
  File “…/lib/webnotes/handler.py”, line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(select t.*,\n (1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + `BHT:Cu’ at line 45”)



On Saturday, January 12, 2013 10:35:31 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

Use accented quotes and not single quotes.



Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,

Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 159, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Cu’ at line 45”)


The error is coming right before we start the sub-query

The code being used is this:

select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,

 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,

 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”

 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code

where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name

order by tabItem.name asc

 (select t.*,
 (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’) as tot1,
 (case when ‘1D:Currency:50’ + ‘2B:Currency:50’ < SO:Currency:50’ then “1 CORD” when (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’ < ‘SO:Currency:50’ + ‘ROL:Currency:50’ then  “2 CSTK” end)

from (
select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,

 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”


 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’

 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc
) t


On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.

1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + BHT:Currency:50

       ) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,

Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this 


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ovOFkimy_skJ.


For more options, visit https://groups.google.com/groups/opt_out.
 
 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/T5yV1eefx7AJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

To post to this group, send email to er...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ecwSeDg6pzEJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Aditya,

You should use scripted reports (new feature) for this. Can you pastebin / gist what you are trying to acheive?

best,
Rushabh

W: https://erpnext.com
T: @rushabh_mehta

On 25-Feb-2013, at 9:28 AM, Aditya Duggal <ad...@gmail.com> wrote:

Is there a way to define a VARIABLE in the query reports?

I am trying to define a variable from the columns, basically to shorten my code but I am unable to define the variable.

On Tuesday, January 15, 2013 2:37:13 PM UTC+5:30, Aditya Duggal wrote:
Dear Anand,

I guess you I have not explained the problem properly. Let me try again:

The intention of making this report in discussion is that it show all items irrespective of whether they have a BIN transaction (that is whether we have done a stock transaction or not). The reason for that is as follows:

  1. If we get an order for an item for which we don't have an item code, then what anyone would do is first create a ITEM CODE. Now understand the real life situation, that there is a SO or order booked in name of this item.
  2. Now till now there is no stock transaction, hence there is nothing about this item in BIN table.
  3. But since this report is more of a STOCK LEVEL report which would also show items on the ORDER, hence we need to show all items in the ITEM table, irrespective of the fact that there is any BIN entry or not.
  4. So in present form the report generally tends to miss the newly created items since there is no BIN transaction.
  5. What I want is that as soon as an item code is generated in the system and if we refresh this report, then the item code should show up in this report.
  6. For the above to be true, I said that we need a left join with item table on left side but I guess either the join is not proper or there is something else which I am not aware of.


If any of you have time, I could show you how this report works and I guess this would be a good report for a STANDARD product, infact I have seen the STOCK LEVEL report and I this report is more useful than that report and also it tells me which items to produce and when. Now there are some small glitches left which don't render this report useless or less important.



On Tuesday, January 15, 2013 12:28:12 PM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

Currently, if a stock transaction is done for that item, only the a BIN is created for that new item.

Hence, if there is no stock transaction against the item, it doesn't need a bin and you can ignore it.

Thanks,
Anand.

On 15-Jan-2013, at 12:26 PM, Aditya Duggal <ad...@gmail.com> wrote:

Hi Anand,

The code that you provided is working beautifully, but for one thing. The problem is that when we create a new Item Code then for that Item a BIN is not created in the BIN table and hence that item is not shown in the report even if there are pending orders for that report.

Now the easiest way would be to have a LEFT JOIN of Item table with the BIN table, which as per my limited knowledge would show all the distinct values of Item Table, that is, Item Code.

But now the more perplexing question is that I was under the impression that I have already done a LEFT JOIN with Item Table on left side with the BIN table and if that is the case then can you let me know why am I missing some item codes (especially which don't have any entry in the BIN table.

I am kind of confused.

On Saturday, January 12, 2013 12:20:29 PM UTC+5:30, Aditya Duggal wrote:
Thanks for the help.

On Saturday, January 12, 2013 11:35:15 AM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

I have forked your gist and updated the existing query with a syntactically correct one.

This is working fine for me.

Thanks,
Anand.

PS: for a gist, always give an extension. In your case, you should use items_for_production.sql - github will use syntax highlighting as per SQL and it becomes very easy to find mistakes :)


On Sat, Jan 12, 2013 at 10:42 AM, Aditya Duggal <ad...@gmail.com> wrote:
:(

Sorry for my bad in using single quotes, I have changed the quote to accented quotes but still getting the same error:


[10:38:28.569] Traceback (innermost last):


  File “…/lib/webnotes/handler.py”, line 159, in handle
    execute_cmd(cmd)
  File “…/lib/webnotes/handler.py”, line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(select t.*,\n (1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + `BHT:Cu’ at line 45”)



On Saturday, January 12, 2013 10:35:31 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

Use accented quotes and not single quotes.



Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,

Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 159, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Cu’ at line 45”)


The error is coming right before we start the sub-query

The code being used is this:

select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,

 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,

 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”

 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code

where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name

order by tabItem.name asc

 (select t.*,
 (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’) as tot1,
 (case when ‘1D:Currency:50’ + ‘2B:Currency:50’ < SO:Currency:50’ then “1 CORD” when (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’ < ‘SO:Currency:50’ + ‘ROL:Currency:50’ then  “2 CSTK” end)

from (
select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,

 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”


 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’

 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc
) t


On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.

1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + BHT:Currency:50

       ) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,

Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this 


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ovOFkimy_skJ.


For more options, visit https://groups.google.com/groups/opt_out.
 
 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/T5yV1eefx7AJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 





You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To post to this group, send email to er…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ecwSeDg6pzEJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

To post to this group, send email to er...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Here is the report gist

Now if you notice I am trying to define a total variable which is being referenced many a times in the code. But I can’t seem to do that may be due to some syntax error or there is some restriction on the query reports since they should only start with Select Command.

Btw I am not sure how to create a script report, let me ponder over this option as well.

On Monday, February 25, 2013 11:45:37 AM UTC+5:30, rushabh wrote:

Aditya,

You should use scripted reports (new feature) for this. Can you pastebin / gist what you are trying to acheive?

best,
Rushabh

W: https://erpnext.com
T: @rushabh_mehta

On 25-Feb-2013, at 9:28 AM, Aditya Duggal <ad...@gmail.com> wrote:

Is there a way to define a VARIABLE in the query reports?

I am trying to define a variable from the columns, basically to shorten my code but I am unable to define the variable.

On Tuesday, January 15, 2013 2:37:13 PM UTC+5:30, Aditya Duggal wrote:
Dear Anand,

I guess you I have not explained the problem properly. Let me try again:

The intention of making this report in discussion is that it show all items irrespective of whether they have a BIN transaction (that is whether we have done a stock transaction or not). The reason for that is as follows:

  1. If we get an order for an item for which we don't have an item code, then what anyone would do is first create a ITEM CODE. Now understand the real life situation, that there is a SO or order booked in name of this item.
  2. Now till now there is no stock transaction, hence there is nothing about this item in BIN table.
  3. But since this report is more of a STOCK LEVEL report which would also show items on the ORDER, hence we need to show all items in the ITEM table, irrespective of the fact that there is any BIN entry or not.
  4. So in present form the report generally tends to miss the newly created items since there is no BIN transaction.
  5. What I want is that as soon as an item code is generated in the system and if we refresh this report, then the item code should show up in this report.
  6. For the above to be true, I said that we need a left join with item table on left side but I guess either the join is not proper or there is something else which I am not aware of.


If any of you have time, I could show you how this report works and I guess this would be a good report for a STANDARD product, infact I have seen the STOCK LEVEL report and I this report is more useful than that report and also it tells me which items to produce and when. Now there are some small glitches left which don't render this report useless or less important.



On Tuesday, January 15, 2013 12:28:12 PM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

Currently, if a stock transaction is done for that item, only the a BIN is created for that new item.

Hence, if there is no stock transaction against the item, it doesn't need a bin and you can ignore it.

Thanks,
Anand.

On 15-Jan-2013, at 12:26 PM, Aditya Duggal <ad...@gmail.com> wrote:

Hi Anand,

The code that you provided is working beautifully, but for one thing. The problem is that when we create a new Item Code then for that Item a BIN is not created in the BIN table and hence that item is not shown in the report even if there are pending orders for that report.

Now the easiest way would be to have a LEFT JOIN of Item table with the BIN table, which as per my limited knowledge would show all the distinct values of Item Table, that is, Item Code.

But now the more perplexing question is that I was under the impression that I have already done a LEFT JOIN with Item Table on left side with the BIN table and if that is the case then can you let me know why am I missing some item codes (especially which don't have any entry in the BIN table.

I am kind of confused.

On Saturday, January 12, 2013 12:20:29 PM UTC+5:30, Aditya Duggal wrote:
Thanks for the help.

On Saturday, January 12, 2013 11:35:15 AM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

I have forked your gist and updated the existing query with a syntactically correct one.

This is working fine for me.

Thanks,
Anand.

PS: for a gist, always give an extension. In your case, you should use items_for_production.sql - github will use syntax highlighting as per SQL and it becomes very easy to find mistakes :)


On Sat, Jan 12, 2013 at 10:42 AM, Aditya Duggal <ad...@gmail.com> wrote:
:(

Sorry for my bad in using single quotes, I have changed the quote to accented quotes but still getting the same error:


[10:38:28.569] Traceback (innermost last):


  File “…/lib/webnotes/handler.py”, line 159, in handle
    execute_cmd(cmd)
  File “…/lib/webnotes/handler.py”, line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(select t.*,\n (1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + `BHT:Cu’ at line 45”)



On Saturday, January 12, 2013 10:35:31 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

Use accented quotes and not single quotes.



Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,

Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 159, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Cu’ at line 45”)


The error is coming right before we start the sub-query

The code being used is this:

select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,

 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,

 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”

 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code

where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name

order by tabItem.name asc

 (select t.*,
 (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’) as tot1,
 (case when ‘1D:Currency:50’ + ‘2B:Currency:50’ < SO:Currency:50’ then “1 CORD” when (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’ < ‘SO:Currency:50’ + ‘ROL:Currency:50’ then  “2 CSTK” end)

from (
select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,

 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”


 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’

 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc
) t


On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.

1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + BHT:Currency:50

       ) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,

Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this 


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ovOFkimy_skJ.


For more options, visit https://groups.google.com/groups/opt_out.
 
 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/T5yV1eefx7AJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 





You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ecwSeDg6pzEJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

To post to this group, send email to er...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/dTqOsFDGCAoJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 

Aditya,

This is way too advanced for me :)... If you want you can remove the "select" validation - but creates a security hole in the system.

Script reports are still WIP - we will start moving all old reports to script reports soon, then you can have a look.

best,
Rushabh


W: https://erpnext.com
T: @rushabh_mehta

On 25-Feb-2013, at 12:00 PM, Aditya Duggal <ad...@gmail.com> wrote:

Here is the report gist

Now if you notice I am trying to define a total variable which is being referenced many a times in the code. But I can't seem to do that may be due to some syntax error or there is some restriction on the query reports since they should only start with Select Command.

Btw I am not sure how to create a script report, let me ponder over this option as well.

On Monday, February 25, 2013 11:45:37 AM UTC+5:30, rushabh wrote:
Aditya,

You should use scripted reports (new feature) for this. Can you pastebin / gist what you are trying to acheive?

best,
Rushabh

W: https://erpnext.com
T: @rushabh_mehta

On 25-Feb-2013, at 9:28 AM, Aditya Duggal <ad...@gmail.com> wrote:

Is there a way to define a VARIABLE in the query reports?

I am trying to define a variable from the columns, basically to shorten my code but I am unable to define the variable.

On Tuesday, January 15, 2013 2:37:13 PM UTC+5:30, Aditya Duggal wrote:
Dear Anand,

I guess you I have not explained the problem properly. Let me try again:

The intention of making this report in discussion is that it show all items irrespective of whether they have a BIN transaction (that is whether we have done a stock transaction or not). The reason for that is as follows:

  1. If we get an order for an item for which we don't have an item code, then what anyone would do is first create a ITEM CODE. Now understand the real life situation, that there is a SO or order booked in name of this item.
  2. Now till now there is no stock transaction, hence there is nothing about this item in BIN table.
  3. But since this report is more of a STOCK LEVEL report which would also show items on the ORDER, hence we need to show all items in the ITEM table, irrespective of the fact that there is any BIN entry or not.
  4. So in present form the report generally tends to miss the newly created items since there is no BIN transaction.
  5. What I want is that as soon as an item code is generated in the system and if we refresh this report, then the item code should show up in this report.
  6. For the above to be true, I said that we need a left join with item table on left side but I guess either the join is not proper or there is something else which I am not aware of.


If any of you have time, I could show you how this report works and I guess this would be a good report for a STANDARD product, infact I have seen the STOCK LEVEL report and I this report is more useful than that report and also it tells me which items to produce and when. Now there are some small glitches left which don't render this report useless or less important.



On Tuesday, January 15, 2013 12:28:12 PM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

Currently, if a stock transaction is done for that item, only the a BIN is created for that new item.

Hence, if there is no stock transaction against the item, it doesn't need a bin and you can ignore it.

Thanks,
Anand.

On 15-Jan-2013, at 12:26 PM, Aditya Duggal <ad...@gmail.com> wrote:

Hi Anand,

The code that you provided is working beautifully, but for one thing. The problem is that when we create a new Item Code then for that Item a BIN is not created in the BIN table and hence that item is not shown in the report even if there are pending orders for that report.

Now the easiest way would be to have a LEFT JOIN of Item table with the BIN table, which as per my limited knowledge would show all the distinct values of Item Table, that is, Item Code.

But now the more perplexing question is that I was under the impression that I have already done a LEFT JOIN with Item Table on left side with the BIN table and if that is the case then can you let me know why am I missing some item codes (especially which don't have any entry in the BIN table.

I am kind of confused.

On Saturday, January 12, 2013 12:20:29 PM UTC+5:30, Aditya Duggal wrote:
Thanks for the help.

On Saturday, January 12, 2013 11:35:15 AM UTC+5:30, Anand Doshi wrote:
Dear Aditya,

I have forked your gist and updated the existing query with a syntactically correct one.

This is working fine for me.

Thanks,
Anand.

PS: for a gist, always give an extension. In your case, you should use items_for_production.sql - github will use syntax highlighting as per SQL and it becomes very easy to find mistakes :)


On Sat, Jan 12, 2013 at 10:42 AM, Aditya Duggal <ad...@gmail.com> wrote:
:(

Sorry for my bad in using single quotes, I have changed the quote to accented quotes but still getting the same error:


[10:38:28.569] Traceback (innermost last):


  File “…/lib/webnotes/handler.py”, line 159, in handle
    execute_cmd(cmd)
  File “…/lib/webnotes/handler.py”, line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(select t.*,\n (1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + `BHT:Cu’ at line 45”)



On Saturday, January 12, 2013 10:35:31 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

Use accented quotes and not single quotes.



Accented quotes can be found in most keyboards to the left of 1 key.

Thanks,

Anand.

On Jan 12, 2013 10:30 AM, "Aditya Duggal" <ad...@gmail.com> wrote:
Hi Anand,

The error which is coming is same and is as below:

[10:28:40.778] Traceback (innermost last):
  File "../lib/webnotes/handler.py", line 159, in handle
    execute_cmd(cmd)
  File "../lib/webnotes/handler.py", line 192, in execute_cmd

    ret = call(method, webnotes.form_dict)
  File “…/lib/webnotes/handler.py”, line 211, in call
    return fn(**newargs)
  File “…/lib/webnotes/widgets/query_report.py”, line 44, in run

    result = [list(t) for t in webnotes.conn.sql(query)]
  File “…/lib/webnotes/db.py”, line 111, in sql
    raise e
 ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Cu’ at line 45”)


The error is coming right before we start the sub-query

The code being used is this:

select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,

 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,
 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,

 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”

 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code

where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’
 and tabBin.item_code = tabItem.name

group by tabItem.name

order by tabItem.name asc

 (select t.*,
 (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’) as tot1,
 (case when ‘1D:Currency:50’ + ‘2B:Currency:50’ < SO:Currency:50’ then “1 CORD” when (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’ < ‘SO:Currency:50’ + ‘ROL:Currency:50’ then  “2 CSTK” end)

from (
select
 tabItem.name as “Item Code:Link/Item:150”,
 tabItem.description as “Description::300”,
 ifnull(tabItem.re_order_level,0) as “ROL:Currency:50”,
 ifnull(tabBin.reserved_qty,0) as “SO:Currency:50”,

 ifnull(tabBin.ordered_qty,0) as “PO:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“DEL20A” then tabBin.actual_qty end),0) as “1D:Currency:50”,
 
 ifnull(min(case when tabBin.warehouse=“BGH655” then tabBin.actual_qty end),0) as “2B:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-BGH655” then tabBin.actual_qty end),0) as “BRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“HT-BGH655” then tabBin.actual_qty end),0) as “BHT:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“FG-BGH655” then tabBin.actual_qty end),0) as “BFG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“SLIT-DEL20A” then tabBin.actual_qty end),0) as “DSL:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RG-DEL20A” then tabBin.actual_qty end),0) as “DRG:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“FG-DEL20A” then tabBin.actual_qty end),0) as “DFG:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“TEST-DEL20A” then tabBin.actual_qty end),0) as “DTS:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“REJ-DEL20A” then tabBin.actual_qty end),0) as “DRJ:Currency:50”,


 ifnull(min(case when tabBin.warehouse=“RM-DEL20A” then tabBin.actual_qty end),0) as “DRM:Currency:50”,

 ifnull(min(case when tabBin.warehouse=“RM-BGH655” then tabBin.actual_qty end),0) as “BRM:Currency:50”


 from
 tabItem
 left join tabBin on
 tabItem.name = tabBin.item_code
where
 tabBin.item_code <>“”
 and tabBin.item_code NOT REGEXP ‘^CN’
 and tabBin.item_code NOT REGEXP ‘^J’

 and tabBin.item_code = tabItem.name

group by tabItem.name
order by tabItem.name asc
) t


On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:

Hi Aditya,

When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.

Eg.

1D:Currency:50 + 2B:Currency:50 + BRG:Currency:50 + BHT:Currency:50

       ) as tot1

Give this a try and if no/different error comes, then atleast part of the problem is solved.

Thanks,

Anand.

On Jan 12, 2013 9:01 AM, "Aditya Duggal" <ad...@gmail.com> wrote:

I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.

To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.


select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t


Can someone shed some light on how to use this in correct syntax for my code.


On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:

Hi,

Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code  is on the gist. And the comments on the code are:


Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:

1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)

  1. This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.

    My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.


On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:

I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:


https://gist.github.com/c7b6539f088c454f189a

On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:

Hi,

I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well


Now what is this report, this report is:

  • Based on “BIN” table
  • BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
  • This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
  • Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
  • Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
  • Projected Quantities would be = D1 + D2 + B + C -A
  • Another column we could add which would be like a Calculation column, which could suggest the person what to do
    • Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
    • If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
    • Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.


Please help me with the below code, the problem being faced is this 


  • I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
  • The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
  • But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.

So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be



select 
 `tabBin`.item_code as "IC:Link/Item:150",
 `tabItem`.description as "Description::250",
 `tabItem`.re_order_level as "ROL::40",
 `tabBin`.warehouse as "WH::80",
 `tabBin`.reserved_qty as "SO::40",
 `tabBin`.ordered_qty as "PO::40",
 `tabBin`.planned_qty as "PLAN::40",
 `tabBin`.actual_qty as "QTY::40"

from
 `tabItem`
 left join `tabBin` on (
   `tabItem`.name=`tabBin`.item_code
 )
where
 `tabBin`.actual_qty <>""

order by `tabBin`.item_code asc




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/wwDgzhdgMQkJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.


To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ovOFkimy_skJ.


For more options, visit https://groups.google.com/groups/opt_out.
 
 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/T5yV1eefx7AJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 





You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To post to this group, send email to erpnext-dev…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/ecwSeDg6pzEJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 





You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un…@googlegroups.com.

To post to this group, send email to er…@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/dTqOsFDGCAoJ.

For more options, visit https://groups.google.com/groups/opt_out.

 

 




You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

To post to this group, send email to er...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.