Subversion Repositories SmartDukaan

#!/bin/bash
TOEMAIL="manish.sharma@shop2020.in"

rm /tmp/BadInventoryMahipalpur.tsv
rm /tmp/BadInventoryBhiwandi.tsv

mysql -u root -pshop2020 warehouse -e "Select badX.id as Item_Id,badX.category as Product_Category, badX.product_group as Product_Group,CONCAT_WS('', badX.brand,' ',badX.model_name,' ', badX.model_number) as Item_Info, CASE badX.physicalWarehouseId WHEN 7 THEN 'Mahipalpur-MMX' END as Physical_Warehouse, sum(badX.Bad_quantity) as Total_Bad_quantity, sum(Total_NLC_Val) as Total_NLC_Price,(sum(Total_NLC_Val)/sum(badX.Bad_quantity)) as Avg_NLC_Price from (select x.id, x.label as category,x.product_group,x.brand, x.model_name,x.model_number, (x.quantity-if(y.quantity is null,0,y.quantity)) as Bad_quantity,(x.Total_Nlc-if(y.Total_Nlc is null,0,y.Total_Nlc)) as Total_NLC_Val , x.physicalWarehouseId,x.purchaseId from (Select it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number, sum(s.quantity) as quantity, i.physicalWarehouseId, sum(s.quantity)*l.nlc as Total_Nlc,i.purchaseId from scanNew s join inventoryItem i on s.inventoryItemId = i.id join purchase p on i.purchaseId= p.id join lineitem l on (p.purchaseOrder_id=l.purchaseOrder_id and i.itemId=l.itemId) join catalog.item it on l.itemId= it.id join catalog.category ct on it.category = ct.id where s.type in ('MARKED_BAD','DOA_IN','SALE_RET_UNUSABLE') and i.physicalWarehouseId=7 group by i.itemId,i.purchaseId) as x left outer join (Select it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number, sum(s.quantity) as quantity, i.physicalWarehouseId, sum(s.quantity)*l.nlc as Total_Nlc, i.purchaseId from scanNew s join inventoryItem i on s.inventoryItemId = i.id join purchase p on i.purchaseId= p.id join lineitem l on (p.purchaseOrder_id=l.purchaseOrder_id and i.itemId=l.itemId) join catalog.item it on l.itemId= it.id join catalog.category ct on it.category = ct.id where s.type in ('DOA_OUT','BAD_SALE') and i.physicalWarehouseId=7 group by i.itemId,i.purchaseId) as y on x.id=y.id and x.purchaseId=y.purchaseId group by x.purchaseId having Bad_quantity>0) as badX group by badX.id, badX.physicalWarehouseId "> /tmp/BadInventoryMahipalpur.tsv

mysql -u root -pshop2020 warehouse -e "Select badX.id as Item_Id,badX.category as Product_Category, badX.product_group as Product_Group,CONCAT_WS('', badX.brand,' ',badX.model_name,' ', badX.model_number) as Item_Info, CASE badX.physicalWarehouseId WHEN 13 THEN 'Bhiwandi' END as Physical_Warehouse, sum(badX.Bad_quantity) as Total_Bad_quantity, sum(Total_NLC_Val) as Total_NLC_Price,(sum(Total_NLC_Val)/sum(badX.Bad_quantity)) as Avg_NLC_Price from (select x.id, x.label as category,x.product_group,x.brand, x.model_name,x.model_number, (x.quantity-if(y.quantity is null,0,y.quantity)) as Bad_quantity,(x.Total_Nlc-if(y.Total_Nlc is null,0,y.Total_Nlc)) as Total_NLC_Val , x.physicalWarehouseId,x.purchaseId from (Select it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number, sum(s.quantity) as quantity, i.physicalWarehouseId, sum(s.quantity)*l.nlc as Total_Nlc,i.purchaseId from scanNew s join inventoryItem i on s.inventoryItemId = i.id join purchase p on i.purchaseId= p.id join lineitem l on (p.purchaseOrder_id=l.purchaseOrder_id and i.itemId=l.itemId) join catalog.item it on l.itemId= it.id join catalog.category ct on it.category = ct.id where s.type in ('MARKED_BAD','DOA_IN','SALE_RET_UNUSABLE') and i.physicalWarehouseId=13 group by i.itemId,i.purchaseId) as x left outer join (Select it.id, ct.label, it.product_group,it.brand, it.model_name,it.model_number, sum(s.quantity) as quantity, i.physicalWarehouseId, sum(s.quantity)*l.nlc as Total_Nlc, i.purchaseId from scanNew s join inventoryItem i on s.inventoryItemId = i.id join purchase p on i.purchaseId= p.id join lineitem l on (p.purchaseOrder_id=l.purchaseOrder_id and i.itemId=l.itemId) join catalog.item it on l.itemId= it.id join catalog.category ct on it.category = ct.id where s.type in ('DOA_OUT','BAD_SALE') and i.physicalWarehouseId=13 group by i.itemId,i.purchaseId) as y on x.id=y.id and x.purchaseId=y.purchaseId group by x.purchaseId having Bad_quantity>0) as badX group by badX.id, badX.physicalWarehouseId"> /tmp/BadInventoryBhiwandi.tsv

s=`du /tmp/BadInventoryMahipalpur.csv | cut -c 1`
if [ $s != 0 ]
then
        sendEmail -f "build@shop2020.in" -s smtp.gmail.com:587 -xu "build@shop2020.in" -xp "cafe@nes" -t $TOEMAIL -o tls=yes -u "Bad Inventory Summarized Report" -m "Bad Inventory Summary files attached" -a /tmp/BadInventoryMahipalpur.tsv /tmp/BadInventoryBhiwandi.tsv
fi