using System.Collections.Generic;
using System.Data.Entity.Validation;
using System.Linq;
using System.Web;
using Warehouse.Models.mesModel;
using Z.EntityFramework.Plus;
using System;

namespace Warehouse.DAL.ShipmentsReportDal
{

    public class ShipmentsReportDal
    {
        public List<dynamic> ShipmentsReport(string StartDateTime, string EndDateTime, string WorkShop, string product_code, string power_grade,string containerno)
        {
            using (var context = new mesModel())
            {
                string sql = string.Empty;
                string strWhere = string.Empty;

                if (!string.IsNullOrEmpty(StartDateTime))
                {
                    strWhere += " and g.shipmenttime >= '" + StartDateTime + "'  ";
                }
                if (!string.IsNullOrEmpty(EndDateTime))
                {
                    strWhere += " and g.shipmenttime <= '" + EndDateTime + "' ";
                }
                if (!string.IsNullOrEmpty(WorkShop))
                {
                    strWhere += " and c.area_code = '" + WorkShop + "'    ";
                }
                //if (!string.IsNullOrEmpty(station))
                //{
                //    strWhere += "and JT.process_code = '" + station + "'   ";
                //}
                if (!string.IsNullOrEmpty(product_code))
                {
                    strWhere += " and a.product_code = '" + product_code + "'    ";
                }

                if (!string.IsNullOrEmpty(power_grade))
                {
                    strWhere += " and a.power_grade = '" + power_grade + "'    ";
                }

                if (!string.IsNullOrEmpty(containerno)&&containerno!= "''")
                {
                    strWhere += " and e.containerno in( " + containerno + " )   ";
                }

                sql = @"
SELECT CONVERT(varchar(100), g.shipmenttime, 120) as outtime,h.sale_order as orderid,f.descriptions as area_code,
a.product_code,a.power_grade,e.containerno,
g.materialcompany,g.billlading,g.plateno,g.shipmentcarton,
CONVERT(varchar(100), a.pack_date, 120) as pack_date,a.pallet_nbr,b.serial_nbr,d.voc,
d.isc,d.vpm,d.ipm,d.pmax,d.ff,a.current_grade
  FROM mes_main.dbo.pack_pallets a 
  left join mes_main.dbo.assembly_status b
  on a.pallet_nbr = b.pallet_nbr
  left join mes_main.dbo.config_workstation c
  on a.wks_id =c.wks_id
  left join mes_level2_iface.dbo.iv d
  on b.serial_nbr =d.serial_nbr
  left join wh.dbo.tcontainerpallet e 
  on a.pallet_nbr =e.palletno
  left join mes_main.dbo.df_areas f
  on c.area_code =f.area_code
  left join wh.dbo.tcontainer g
  on e.containerno =g.containerno
  left JOIN mes_main.dbo.wo_mfg h
  on h.workorder = b.workorder
  where 1=1 " + strWhere + @"";
                var res = context.DynamicListFromSql(sql, null);
                return res.ToList();
            }
        }

    }
}