using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Collections;
using System.Globalization;
using System.Web.Caching;
using System.IO;
using System.Threading;
using Microsoft.Reporting.WebForms;

namespace CheckSheet
{
    public partial class rptReport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {


            if (!Page.IsPostBack)
            {

                try
                {
                    if (Page.Request["type"].ToString()=="4")
                    {
                        _genReportxr(Page.Request["filter"].ToString(), Page.Request["type"].ToString(), Page.Request["week"].ToString(), Page.Request["point"].ToString());

                      

                    }
                    else
                    {
                        _genReport(Page.Request["filter"].ToString(), Page.Request["type"].ToString(), Page.Request["week"].ToString(), Page.Request["month"].ToString(), Page.Request["year"].ToString());



                    }
                }
                catch { };


            }
        }


       
        private void _genReportxr(string filter, string type, string week,string point)
        {

            // Set the processing mode for the ReportViewer to Local
            rptview.ProcessingMode = ProcessingMode.Local;
            LocalReport rep = rptview.LocalReport;
            if(point.IndexOf("POINT")!=-1)
            {
                rep.ReportPath = "rptXRChartMain.rdlc";
                // Create a report data source for the sales order data

            }
            else
            {
                rep.ReportPath = "rptXRChartMain1.rdlc";
                // Create a report data source for the sales order data

            }

            ReportDataSource dsMaintenanceDS = new ReportDataSource();
            dsMaintenanceDS.Name = "dsRpt";
            rptview.LocalReport.EnableExternalImages = true;
            DataTable dt = _genDataSourcexr(filter, type, week);
            dsMaintenanceDS.Value = dt;
            rep.DataSources.Clear();

           
            string inspector = string.Empty;
            string checker = string.Empty;
            string approve = string.Empty;


            if (dt.Rows.Count > 0)
            {

                inspector = _getuaser_data(dt.Rows[0]["createuserid"].ToString());
                checker = _getuaser_data(dt.Rows[0]["checkuserid"].ToString());
                approve = _getuaser_data(dt.Rows[0]["approveuserid"].ToString());
            

            }

            try
            {

                ReportParameter param0 = new ReportParameter("inspector", inspector);
                rep.SetParameters(new ReportParameter[] { param0 });
            }
            catch { };

            try
            {
                ReportParameter param1 = new ReportParameter("checker", checker);
                rep.SetParameters(new ReportParameter[] { param1 });
            }
            catch { };


            try
            {
                ReportParameter param2 = new ReportParameter("approve", approve);
                rep.SetParameters(new ReportParameter[] { param2 });
            }
            catch { };

            if (point.IndexOf("POINT") != -1)
            {
                string ctpath = string.Empty;
                if (point == "POINT A")
                {
                    ctpath = "point_a.jpg";

                }
                else if (point == "POINT B")
                {
                    ctpath = "point_b.jpg";

                }
                else if (point == "POINT C")
                {
                    ctpath = "point_c.jpg";

                }
                else if (point == "POINT D")
                {
                    ctpath = "point_d.jpg";

                }
                string fpath = HttpContext.Current.Server.MapPath("image/" + ctpath).Replace("\\setting", "");


                string imagePath1 = new Uri(fpath).AbsoluteUri;
                ReportParameter param3 = new ReportParameter("img_chart_left", imagePath1);
                rptview.LocalReport.SetParameters(param3);
            }

            rep.DataSources.Add(dsMaintenanceDS);
            rep.Refresh();


            Byte[] results = rptview.LocalReport.Render("PDF");
            Response.ContentType = "Application/pdf";
            Response.OutputStream.Write(results, 0, results.Length);
            Response.End();

        }



        private void _genReport(string filter,string type,string week,string month,string year)
        {

            // Set the processing mode for the ReportViewer to Local
            rptview.ProcessingMode = ProcessingMode.Local;
            LocalReport rep = rptview.LocalReport;
            if (type == "1")
            {
                rep.ReportPath = "rptInspection.rdlc";
            }
            else if (type == "2")
            {
                rep.ReportPath = "rptInspection1.rdlc";
            }
            else if (type == "3")
            {
                rep.ReportPath = "rptInspection2.rdlc";
            }
            else if (type == "4")
            {
                rep.ReportPath = "rptXRChartMain.rdlc";
            }
            else if (type == "5")
            {
                rep.ReportPath = "rptJig.rdlc";
            }
            else if (type == "7")
            {
                rep.ReportPath = "rptTorque.rdlc";
            }
            else if (type == "6")
            {
                rep.ReportPath = "rptTool.rdlc";
            }
            else if (type == "8")
            {
                rep.ReportPath = "rptMachineCheckSheet.rdlc";
            }
            else if (type == "11")
            {
                rep.ReportPath = "rpt4M.rdlc";
            }

            // Create a report data source for the sales order data
            ReportDataSource dsMaintenanceDS = new ReportDataSource();
            dsMaintenanceDS.Name = "dsRpt";
            rptview.LocalReport.EnableExternalImages = true;
            DataTable dt = _genDataSource(filter,type,week);
            dsMaintenanceDS.Value = dt;
            rep.DataSources.Clear();

            string monthfilter = string.Empty;
            string yearfilter = string.Empty;
            if(dt.Rows.Count>0)
            {
                monthfilter = dt.Rows[0]["hd1"].ToString();
                if (monthfilter.IndexOf("/") != -1)
                {
                    string[] arrd = monthfilter.Split('/');
                    monthfilter = arrd[1] + "/" + arrd[2];
                    yearfilter = arrd[2];

                }

            }
            string inspector = string.Empty;
            string checker = string.Empty;
            string approve = string.Empty;

            if(monthfilter.Length==0)
            {

                monthfilter = month + "/" + year;
                yearfilter = year;

            }

            if(dt.Rows.Count>0)
            {

                inspector = _getuaser_data(dt.Rows[0]["create_user"].ToString());
                checker = "";
                approve = "";

            }

            if(type=="11")
            {

                ReportParameter param0 = new ReportParameter("strmonth", monthfilter);
                rep.SetParameters(new ReportParameter[] { param0 });

                ReportParameter param1 = new ReportParameter("inspector", inspector);
                rep.SetParameters(new ReportParameter[] { param1 });

                ReportParameter param2 = new ReportParameter("checker", checker);
                rep.SetParameters(new ReportParameter[] { param2 });

                ReportParameter param3 = new ReportParameter("approve", approve);
                rep.SetParameters(new ReportParameter[] { param3 });


                ReportParameter param4 = new ReportParameter("stryear", yearfilter);
                rep.SetParameters(new ReportParameter[] { param4 });

            }
            if (type == "7")
            {

                ReportParameter param0 = new ReportParameter("strmonth", monthfilter);
                rep.SetParameters(new ReportParameter[] { param0 });

                ReportParameter param1 = new ReportParameter("inspector", inspector);
                rep.SetParameters(new ReportParameter[] { param1 });

                ReportParameter param2 = new ReportParameter("checker", checker);
                rep.SetParameters(new ReportParameter[] { param2 });

                ReportParameter param3 = new ReportParameter("approve", approve);
                rep.SetParameters(new ReportParameter[] { param3 });


                ReportParameter param4 = new ReportParameter("inspector_date", "");
                rep.SetParameters(new ReportParameter[] { param4 });

                ReportParameter param5 = new ReportParameter("checker_date", "");
                rep.SetParameters(new ReportParameter[] { param5 });

                ReportParameter param6 = new ReportParameter("approve_date", "");
                rep.SetParameters(new ReportParameter[] { param6 });

            } 
            else if (type == "8" || type=="6")
            {

                ReportParameter param0 = new ReportParameter("strmonth", monthfilter);
                rep.SetParameters(new ReportParameter[] { param0 });

                ReportParameter param1 = new ReportParameter("inspector", inspector);
                rep.SetParameters(new ReportParameter[] { param1 });

                ReportParameter param2 = new ReportParameter("checker", checker);
                rep.SetParameters(new ReportParameter[] { param2 });

                ReportParameter param3 = new ReportParameter("approve", approve);
                rep.SetParameters(new ReportParameter[] { param3 });



                ReportParameter param4 = new ReportParameter("stryear", yearfilter);
                rep.SetParameters(new ReportParameter[] { param4 });

                ReportParameter param5 = new ReportParameter("inspector_date", "");
                rep.SetParameters(new ReportParameter[] { param5 });

                ReportParameter param6 = new ReportParameter("checker_date", "");
                rep.SetParameters(new ReportParameter[] { param6});

                ReportParameter param7 = new ReportParameter("approve_date", "");
                rep.SetParameters(new ReportParameter[] { param7 });



            }
            else
            {
                ReportParameter param0 = new ReportParameter("strmonth", monthfilter);
                rep.SetParameters(new ReportParameter[] { param0 });

                ReportParameter param1 = new ReportParameter("inspector", inspector);
                rep.SetParameters(new ReportParameter[] { param1 });

                ReportParameter param2 = new ReportParameter("checker", checker);
                rep.SetParameters(new ReportParameter[] { param2 });

                ReportParameter param3 = new ReportParameter("approve", approve);
                rep.SetParameters(new ReportParameter[] { param3 });




            }

            
            rep.DataSources.Add(dsMaintenanceDS);
            rep.Refresh();


            Byte[] results = rptview.LocalReport.Render("PDF");
            Response.ContentType = "Application/pdf";
            Response.OutputStream.Write(results, 0, results.Length);
            Response.End();
            
        }
        private string _getuaser_data(string usercode)
        {
            string username = string.Empty;
            using(FcSql.Function fc =new FcSql.Function())
            {
                string str_user = string.Format("select firstname from Users	Where usercode='{0}' ", usercode);
                DataTable dt = fc.SQL_DataTable(str_user);
                if(dt.Rows.Count>0)
                {

                    username = dt.Rows[0]["firstname"].ToString();

                }



                fc.closeconnection();
            }

            return username;
        }
        private DataTable _genDataSource(string filter,string type,string week)
        {
            DataTable dt = new DataTable();
            string chkdate = string.Empty;



            string sqlquery = string.Empty;
            using (FcSql.Function fc = new FcSql.Function())
            {


                string filmaster = string.Empty;
                if(filter.IndexOf("and")!=-1)
                {

                    string[] arr = filter.Split(' ');
                    filmaster = arr[1] +" "+ arr[2] +" "+ arr[3] +" "+ arr[4];


                    string y = arr[8];
                    try
                    {
                        string[] arry = arr[8].Split('=');
                        string year = arry[1];
                        string qweek = string.Format("select min(listday) as mindate,max(listday) as maxdate  from listdate_of_year('{0}-01-01') Where weekno={1}", year, week);
                        DataTable dtweek = fc.SQL_DataTable(qweek.ToString());
                        if (dtweek.Rows.Count > 0)
                        {
                            filter = filter + " and inspectiondate between '" + DateTime.Parse(dtweek.Rows[0]["mindate"].ToString()).ToString("yyyy-MM-dd", new CultureInfo("en-US")) + " 00:00:00' and '" + DateTime.Parse(dtweek.Rows[0]["maxdate"].ToString()).ToString("yyyy-MM-dd", new CultureInfo("en-US")) + " 23:59:59'";

                        }
                    }
                    catch { };

                }

                if(type=="1")
                {
                    filmaster = filmaster + " and report_type='1' ";
                    filter = filter + " and report_type='1' ";
                }
                else if(type=="2")
                {
                    filmaster = filmaster + " and report_type='2' ";
                    filter = filter + " and report_type='3' ";

                }
                else if(type=="3")
                {
                    filmaster = filmaster + " and report_type='3' ";
                    filter = filter + " and report_type='3' ";

                }
                else if (type == "5")
                {
                    filmaster = filmaster + " and report_type='5' ";


                }
                else if (type == "6")
                {
                    filmaster = filmaster + " and report_type='6' ";


                }
                else if (type == "7")
                {
                    filmaster = filmaster + " and report_type='7' ";
               

                }
                else if (type == "8")
                {
                    filmaster = filmaster + " and report_type='8' ";


                }
                else if (type == "11")
                {
                    filmaster = filmaster + " and charindex('.',a.itemno)=0 and  a.inspectiontype='4' ";
                  

                }

                string strquery = string.Empty;
                if (type == "11")
                {

                   strquery = string.Format(" select '' as input_type, '' as processname, '' as piz1,'' as piz2,'' as piz3,'' as piz4, 0.00 as value_x,0.00 as value_r, '' as std_qty,'' as add_qty,'' as dip_qty, '' as customerno,'' as customername, '' as partno_hd,'' as partname_hd,'' as icsno,'' as icsname ,lamp, case when  charindex('LHD',model)>0 then 'LHD' else 'RHD' end as modeltype, b.linegroupcode,b.linename,b.model,b.partnumber,'' as machineid,'' as machinename,a.*,  '' as hd1,'' as hd2,'' as hd3,'' as hd4,'' as hd5,'' as hd6,'' as hd7,'' as hd8,'' as hd9,'' as hd10, '' as hd11,'' as hd12,'' as hd13,'' as hd14,	'' as hd15,'' as hd16,'' as hd17,'' as hd18,'' as hd19,'' as hd20,'' as hd21,'' as hd22,'' as hd23,'' as hd24,'' as hd25,'' as hd26,'' as hd27,'' as hd28,'' as hd29,'' as hd30,'' as hd31,  '' as d1,'' as d2,'' as d3,'' as d4,'' as d5,'' as d6,'' as d7,'' as d8,'' as d9,'' as d10, '' as d11,'' as d12,'' as d13,'' as d14,'' as d15,'' as d16,'' as d17,'' as d18,'' as d19,'' as d20, '' as d21,'' as d22,'' as d23,'' as d24,'' as d25,'' as d26,'' as d27,'' as d28,'' as d29,'' as d30,'' as d31,  '' as n1,'' as n2,'' as n3,'' as n4,'' as n5,'' as n6,'' as n7,'' as n8,'' as n9,'' as n10, '' as n11,'' as n12,'' as n13,'' as n14,'' as n15,'' as n16,'' as n17,'' as n18,'' as n19,'' as n20, '' as n21, '' as n22, '' as n23, '' as n24, '' as n25, '' as n26, '' as n27, '' as n28, '' as n29, '' as n30, '' as n31,   '' as chkd1,'' as chkd2,'' as chkd3,'' as chkd4,'' as chkd5,'' as chkd6,'' as chkd7,'' as chkd8,'' as chkd9,'' as chkd10,  '' as chkd11,'' as chkd12,'' as chkd13,'' as chkd14,'' as chkd15,'' as chkd16,'' as chkd17,'' as chkd18,'' as chkd19,'' as chkd20,'' as chkd21,'' as chkd22,'' as chkd23,'' as chkd24,'' as chkd25,'' as chkd26,'' as chkd27,'' as chkd28,'' as chkd29,'' as chkd30,'' as chkd31,  '' as chkn1,'' as chkn2,'' as chkn3,'' as chkn4,'' as chkn5,'' as chkn6,'' as chkn7,'' as chkn8,'' as chkn9,'' as chkn10, '' as chkn11,'' as chkn12,'' as chkn13,'' as chkn14,'' as chkn15,'' as chkn16,'' as chkn17,'' as chkn18,'' as chkn19,'' as chkn20,'' as chkn21,'' as chkn22,'' as chkn23,'' as chkn24,'' as chkn25,'' as chkn26,'' as chkn27,'' as chkn28,'' as chkn29,'' as chkn30,'' as chkn31,  '' as appd1,'' as  appd2,'' as  appd3,'' as  appd4,'' as  appd5,'' as  appd6,'' as  appd7,'' as  appd8,'' as  appd9,'' as appd10,'' as appd11,'' as appd12,'' as appd13,'' as appd14,'' as appd15,'' as appd16,'' as appd17,'' as appd18,'' as appd19,'' as appd20,'' as appd21,'' as appd22,'' as appd23,'' as appd24,'' as appd25,'' as appd26,'' as appd27,'' as appd28,'' as appd29,'' as appd30,'' as appd31,  '' as  appn1,'' as  appn2,'' as  appn3,'' as  appn4,'' as  appn5,'' as  appn6,'' as  appn7,'' as  appn8,'' as  appn9,'' as  appn10, '' as appn11,'' as appn12,'' as appn13,'' as appn14,'' as appn15,'' as appn16,'' as appn17,'' as appn18,'' as appn19,'' as appn20,'' as appn21,'' as appn22,'' as appn23,'' as appn24,'' as appn25,'' as appn26,'' as appn27,'' as appn28,'' as appn29,'' as appn30,'' as appn31,'' as changepoint,'' as result1,'' as action1,'' as result2,'' as action2,'' as remark,'' as toolname,'' as toolid  from  InspectionInput as a inner join LineGroupMaster as b on a.partnumber = b.partnumber  Where 1=1  {0} ", filmaster);


                }
                else if(type=="7")
                {
                    strquery = string.Format(" select pizlink,  '' as input_type, '' as processname,'' as piz1,'' as piz2,'' as piz3,'' as piz4, 0.00 as value_x,0.00 as value_r, '' as std_qty,'' as add_qty,'' as dip_qty, '' as customerno,'' as customername, '' as partno_hd,'' as partname_hd,'' as icsno,'' as icsname ,lamp, case when  charindex('LHD',model)>0 then 'LHD' else 'RHD' end as modeltype, b.linegroupcode,b.linename,b.model,b.partnumber,'' as machineid,'' as machinename,a.*,  '' as hd1,'' as hd2,'' as hd3,'' as hd4,'' as hd5,'' as hd6,'' as hd7,'' as hd8,'' as hd9,'' as hd10, '' as hd11,'' as hd12,'' as hd13,'' as hd14,	'' as hd15,'' as hd16,'' as hd17,'' as hd18,'' as hd19,'' as hd20,'' as hd21,'' as hd22,'' as hd23,'' as hd24,'' as hd25,'' as hd26,'' as hd27,'' as hd28,'' as hd29,'' as hd30,'' as hd31,  '' as d1,'' as d2,'' as d3,'' as d4,'' as d5,'' as d6,'' as d7,'' as d8,'' as d9,'' as d10, '' as d11,'' as d12,'' as d13,'' as d14,'' as d15,'' as d16,'' as d17,'' as d18,'' as d19,'' as d20, '' as d21,'' as d22,'' as d23,'' as d24,'' as d25,'' as d26,'' as d27,'' as d28,'' as d29,'' as d30,'' as d31,  '' as n1,'' as n2,'' as n3,'' as n4,'' as n5,'' as n6,'' as n7,'' as n8,'' as n9,'' as n10, '' as n11,'' as n12,'' as n13,'' as n14,'' as n15,'' as n16,'' as n17,'' as n18,'' as n19,'' as n20, '' as n21, '' as n22, '' as n23, '' as n24, '' as n25, '' as n26, '' as n27, '' as n28, '' as n29, '' as n30, '' as n31,   '' as chkd1,'' as chkd2,'' as chkd3,'' as chkd4,'' as chkd5,'' as chkd6,'' as chkd7,'' as chkd8,'' as chkd9,'' as chkd10,  '' as chkd11,'' as chkd12,'' as chkd13,'' as chkd14,'' as chkd15,'' as chkd16,'' as chkd17,'' as chkd18,'' as chkd19,'' as chkd20,'' as chkd21,'' as chkd22,'' as chkd23,'' as chkd24,'' as chkd25,'' as chkd26,'' as chkd27,'' as chkd28,'' as chkd29,'' as chkd30,'' as chkd31,  '' as chkn1,'' as chkn2,'' as chkn3,'' as chkn4,'' as chkn5,'' as chkn6,'' as chkn7,'' as chkn8,'' as chkn9,'' as chkn10, '' as chkn11,'' as chkn12,'' as chkn13,'' as chkn14,'' as chkn15,'' as chkn16,'' as chkn17,'' as chkn18,'' as chkn19,'' as chkn20,'' as chkn21,'' as chkn22,'' as chkn23,'' as chkn24,'' as chkn25,'' as chkn26,'' as chkn27,'' as chkn28,'' as chkn29,'' as chkn30,'' as chkn31,  '' as appd1,'' as  appd2,'' as  appd3,'' as  appd4,'' as  appd5,'' as  appd6,'' as  appd7,'' as  appd8,'' as  appd9,'' as appd10,'' as appd11,'' as appd12,'' as appd13,'' as appd14,'' as appd15,'' as appd16,'' as appd17,'' as appd18,'' as appd19,'' as appd20,'' as appd21,'' as appd22,'' as appd23,'' as appd24,'' as appd25,'' as appd26,'' as appd27,'' as appd28,'' as appd29,'' as appd30,'' as appd31,  '' as  appn1,'' as  appn2,'' as  appn3,'' as  appn4,'' as  appn5,'' as  appn6,'' as  appn7,'' as  appn8,'' as  appn9,'' as  appn10, '' as appn11,'' as appn12,'' as appn13,'' as appn14,'' as appn15,'' as appn16,'' as appn17,'' as appn18,'' as appn19,'' as appn20,'' as appn21,'' as appn22,'' as appn23,'' as appn24,'' as appn25,'' as appn26,'' as appn27,'' as appn28,'' as appn29,'' as appn30,'' as appn31,'' as changepoint,'' as result1,'' as action1,'' as result2,'' as action2,'' as remark,'' as toolname,'' as toolid  from  InspectionItem as a  inner join EquipmentList as c on a.equid=c.equid  inner join LineGroupMaster as b on c.partnumber = b.partnumber  Where 1=1  {0} ", filmaster);


                }
                else if (type == "8")
                {
                    strquery = string.Format(" select pizlink,  '' as input_type, 'ASSEMBLY' as processname,'' as piz1,'' as piz2,'' as piz3,'' as piz4, 0.00 as value_x,0.00 as value_r, '' as std_qty,'' as add_qty,'' as dip_qty, '' as customerno,'' as customername, '' as partno_hd,'' as partname_hd,'' as icsno,'' as icsname ,lamp, case when  charindex('LHD',model)>0 then 'LHD' else 'RHD' end as modeltype, b.linegroupcode,b.linename,b.model,b.partnumber,c.machineid, machinename,a.*,  '' as hd1,'' as hd2,'' as hd3,'' as hd4,'' as hd5,'' as hd6,'' as hd7,'' as hd8,'' as hd9,'' as hd10, '' as hd11,'' as hd12,'' as hd13,'' as hd14,	'' as hd15,'' as hd16,'' as hd17,'' as hd18,'' as hd19,'' as hd20,'' as hd21,'' as hd22,'' as hd23,'' as hd24,'' as hd25,'' as hd26,'' as hd27,'' as hd28,'' as hd29,'' as hd30,'' as hd31,  '' as d1,'' as d2,'' as d3,'' as d4,'' as d5,'' as d6,'' as d7,'' as d8,'' as d9,'' as d10, '' as d11,'' as d12,'' as d13,'' as d14,'' as d15,'' as d16,'' as d17,'' as d18,'' as d19,'' as d20, '' as d21,'' as d22,'' as d23,'' as d24,'' as d25,'' as d26,'' as d27,'' as d28,'' as d29,'' as d30,'' as d31,  '' as n1,'' as n2,'' as n3,'' as n4,'' as n5,'' as n6,'' as n7,'' as n8,'' as n9,'' as n10, '' as n11,'' as n12,'' as n13,'' as n14,'' as n15,'' as n16,'' as n17,'' as n18,'' as n19,'' as n20, '' as n21, '' as n22, '' as n23, '' as n24, '' as n25, '' as n26, '' as n27, '' as n28, '' as n29, '' as n30, '' as n31,   '' as chkd1,'' as chkd2,'' as chkd3,'' as chkd4,'' as chkd5,'' as chkd6,'' as chkd7,'' as chkd8,'' as chkd9,'' as chkd10,  '' as chkd11,'' as chkd12,'' as chkd13,'' as chkd14,'' as chkd15,'' as chkd16,'' as chkd17,'' as chkd18,'' as chkd19,'' as chkd20,'' as chkd21,'' as chkd22,'' as chkd23,'' as chkd24,'' as chkd25,'' as chkd26,'' as chkd27,'' as chkd28,'' as chkd29,'' as chkd30,'' as chkd31,  '' as chkn1,'' as chkn2,'' as chkn3,'' as chkn4,'' as chkn5,'' as chkn6,'' as chkn7,'' as chkn8,'' as chkn9,'' as chkn10, '' as chkn11,'' as chkn12,'' as chkn13,'' as chkn14,'' as chkn15,'' as chkn16,'' as chkn17,'' as chkn18,'' as chkn19,'' as chkn20,'' as chkn21,'' as chkn22,'' as chkn23,'' as chkn24,'' as chkn25,'' as chkn26,'' as chkn27,'' as chkn28,'' as chkn29,'' as chkn30,'' as chkn31,  '' as appd1,'' as  appd2,'' as  appd3,'' as  appd4,'' as  appd5,'' as  appd6,'' as  appd7,'' as  appd8,'' as  appd9,'' as appd10,'' as appd11,'' as appd12,'' as appd13,'' as appd14,'' as appd15,'' as appd16,'' as appd17,'' as appd18,'' as appd19,'' as appd20,'' as appd21,'' as appd22,'' as appd23,'' as appd24,'' as appd25,'' as appd26,'' as appd27,'' as appd28,'' as appd29,'' as appd30,'' as appd31,  '' as  appn1,'' as  appn2,'' as  appn3,'' as  appn4,'' as  appn5,'' as  appn6,'' as  appn7,'' as  appn8,'' as  appn9,'' as  appn10, '' as appn11,'' as appn12,'' as appn13,'' as appn14,'' as appn15,'' as appn16,'' as appn17,'' as appn18,'' as appn19,'' as appn20,'' as appn21,'' as appn22,'' as appn23,'' as appn24,'' as appn25,'' as appn26,'' as appn27,'' as appn28,'' as appn29,'' as appn30,'' as appn31,'' as changepoint,'' as result1,'' as action1,'' as result2,'' as action2,'' as remark,'' as toolname,'' as toolid  from  InspectionItem as a  inner join EquipmentList as c on a.equid=c.equid  inner join LineGroupMaster as b on c.partnumber = b.partnumber  inner join MachineMaster as m on c.machineid=m.machineid   Where 1=1  {0} ", filmaster);


                }
                else if (type == "5")
                {
                    strquery = string.Format(" select pizlink,  '' as input_type, 'ASSEMBLY' as processname,'' as piz1,'' as piz2,'' as piz3,'' as piz4, 0.00 as value_x,0.00 as value_r, std_qty,add_qty,dip_qty,customerno,customername, '' as partno_hd,partname as partname_hd, icsno,icsname ,lamp, case when  charindex('LHD',model)>0 then 'LHD' else 'RHD' end as modeltype, b.linegroupcode,b.linename,b.model,b.partnumber,c.machineid, machinename,a.*,  '' as hd1,'' as hd2,'' as hd3,'' as hd4,'' as hd5,'' as hd6,'' as hd7,'' as hd8,'' as hd9,'' as hd10, '' as hd11,'' as hd12,'' as hd13,'' as hd14,	'' as hd15,'' as hd16,'' as hd17,'' as hd18,'' as hd19,'' as hd20,'' as hd21,'' as hd22,'' as hd23,'' as hd24,'' as hd25,'' as hd26,'' as hd27,'' as hd28,'' as hd29,'' as hd30,'' as hd31,  0 as d1,0 as d2,0 as d3,0 as d4,0 as d5,0 as d6,0 as d7,0 as d8,0 as d9,0 as d10, '' as d11,'' as d12,'' as d13,'' as d14,'' as d15,'' as d16,'' as d17,'' as d18,'' as d19,'' as d20, '' as d21,'' as d22,'' as d23,'' as d24,'' as d25,'' as d26,'' as d27,'' as d28,'' as d29,'' as d30,'' as d31,  '' as n1,'' as n2,'' as n3,'' as n4,'' as n5,'' as n6,'' as n7,'' as n8,'' as n9,'' as n10, '' as n11,'' as n12,'' as n13,'' as n14,'' as n15,'' as n16,'' as n17,'' as n18,'' as n19,'' as n20, '' as n21, '' as n22, '' as n23, '' as n24, '' as n25, '' as n26, '' as n27, '' as n28, '' as n29, '' as n30, '' as n31,   '' as chkd1,'' as chkd2,'' as chkd3,'' as chkd4,'' as chkd5,'' as chkd6,'' as chkd7,'' as chkd8,'' as chkd9,'' as chkd10,  '' as chkd11,'' as chkd12,'' as chkd13,'' as chkd14,'' as chkd15,'' as chkd16,'' as chkd17,'' as chkd18,'' as chkd19,'' as chkd20,'' as chkd21,'' as chkd22,'' as chkd23,'' as chkd24,'' as chkd25,'' as chkd26,'' as chkd27,'' as chkd28,'' as chkd29,'' as chkd30,'' as chkd31,  '' as chkn1,'' as chkn2,'' as chkn3,'' as chkn4,'' as chkn5,'' as chkn6,'' as chkn7,'' as chkn8,'' as chkn9,'' as chkn10, '' as chkn11,'' as chkn12,'' as chkn13,'' as chkn14,'' as chkn15,'' as chkn16,'' as chkn17,'' as chkn18,'' as chkn19,'' as chkn20,'' as chkn21,'' as chkn22,'' as chkn23,'' as chkn24,'' as chkn25,'' as chkn26,'' as chkn27,'' as chkn28,'' as chkn29,'' as chkn30,'' as chkn31,  '' as appd1,'' as  appd2,'' as  appd3,'' as  appd4,'' as  appd5,'' as  appd6,'' as  appd7,'' as  appd8,'' as  appd9,'' as appd10,'' as appd11,'' as appd12,'' as appd13,'' as appd14,'' as appd15,'' as appd16,'' as appd17,'' as appd18,'' as appd19,'' as appd20,'' as appd21,'' as appd22,'' as appd23,'' as appd24,'' as appd25,'' as appd26,'' as appd27,'' as appd28,'' as appd29,'' as appd30,'' as appd31,  '' as  appn1,'' as  appn2,'' as  appn3,'' as  appn4,'' as  appn5,'' as  appn6,'' as  appn7,'' as  appn8,'' as  appn9,'' as  appn10, '' as appn11,'' as appn12,'' as appn13,'' as appn14,'' as appn15,'' as appn16,'' as appn17,'' as appn18,'' as appn19,'' as appn20,'' as appn21,'' as appn22,'' as appn23,'' as appn24,'' as appn25,'' as appn26,'' as appn27,'' as appn28,'' as appn29,'' as appn30,'' as appn31,'' as changepoint,'' as result1,'' as action1,'' as result2,'' as action2,'' as remark,'' as toolname,'' as toolid  from  InspectionItem as a  inner join EquipmentList as c on a.equid=c.equid  inner join LineGroupMaster as b on c.partnumber = b.partnumber  inner join MachineMaster as m on c.machineid=m.machineid   Where 1=1  {0} ", filmaster);


                }
                else if (type == "6")
                {
                    strquery = string.Format(" select pizlink,  '' as input_type, 'ASSEMBLY' as processname,'' as piz1,'' as piz2,'' as piz3,'' as piz4, 0.00 as value_x,0.00 as value_r, std_qty,add_qty,dip_qty,customerno,customername, '' as partno_hd,partname as partname_hd, icsno,icsname ,b.lamp, case when  charindex('LHD',b.model)>0 then 'LHD' else 'RHD' end as modeltype, b.linegroupcode,b.linename,b.model,b.partnumber,c.machineid, machinename,a.*,  '' as hd1,'' as hd2,'' as hd3,'' as hd4,'' as hd5,'' as hd6,'' as hd7,'' as hd8,'' as hd9,'' as hd10, '' as hd11,'' as hd12,'' as hd13,'' as hd14,	'' as hd15,'' as hd16,'' as hd17,'' as hd18,'' as hd19,'' as hd20,'' as hd21,'' as hd22,'' as hd23,'' as hd24,'' as hd25,'' as hd26,'' as hd27,'' as hd28,'' as hd29,'' as hd30,'' as hd31,  '' as d1,'' as d2,'' as d3,'' as d4,'' as d5,'' as d6,'' as d7,'' as d8,'' as d9,'' as d10, '' as d11,'' as d12,'' as d13,'' as d14,'' as d15,'' as d16,'' as d17,'' as d18,'' as d19,'' as d20, '' as d21,'' as d22,'' as d23,'' as d24,'' as d25,'' as d26,'' as d27,'' as d28,'' as d29,'' as d30,'' as d31,  '' as n1,'' as n2,'' as n3,'' as n4,'' as n5,'' as n6,'' as n7,'' as n8,'' as n9,'' as n10, '' as n11,'' as n12,'' as n13,'' as n14,'' as n15,'' as n16,'' as n17,'' as n18,'' as n19,'' as n20, '' as n21, '' as n22, '' as n23, '' as n24, '' as n25, '' as n26, '' as n27, '' as n28, '' as n29, '' as n30, '' as n31,   '' as chkd1,'' as chkd2,'' as chkd3,'' as chkd4,'' as chkd5,'' as chkd6,'' as chkd7,'' as chkd8,'' as chkd9,'' as chkd10,  '' as chkd11,'' as chkd12,'' as chkd13,'' as chkd14,'' as chkd15,'' as chkd16,'' as chkd17,'' as chkd18,'' as chkd19,'' as chkd20,'' as chkd21,'' as chkd22,'' as chkd23,'' as chkd24,'' as chkd25,'' as chkd26,'' as chkd27,'' as chkd28,'' as chkd29,'' as chkd30,'' as chkd31,  '' as chkn1,'' as chkn2,'' as chkn3,'' as chkn4,'' as chkn5,'' as chkn6,'' as chkn7,'' as chkn8,'' as chkn9,'' as chkn10, '' as chkn11,'' as chkn12,'' as chkn13,'' as chkn14,'' as chkn15,'' as chkn16,'' as chkn17,'' as chkn18,'' as chkn19,'' as chkn20,'' as chkn21,'' as chkn22,'' as chkn23,'' as chkn24,'' as chkn25,'' as chkn26,'' as chkn27,'' as chkn28,'' as chkn29,'' as chkn30,'' as chkn31,  '' as appd1,'' as  appd2,'' as  appd3,'' as  appd4,'' as  appd5,'' as  appd6,'' as  appd7,'' as  appd8,'' as  appd9,'' as appd10,'' as appd11,'' as appd12,'' as appd13,'' as appd14,'' as appd15,'' as appd16,'' as appd17,'' as appd18,'' as appd19,'' as appd20,'' as appd21,'' as appd22,'' as appd23,'' as appd24,'' as appd25,'' as appd26,'' as appd27,'' as appd28,'' as appd29,'' as appd30,'' as appd31,  '' as  appn1,'' as  appn2,'' as  appn3,'' as  appn4,'' as  appn5,'' as  appn6,'' as  appn7,'' as  appn8,'' as  appn9,'' as  appn10, '' as appn11,'' as appn12,'' as appn13,'' as appn14,'' as appn15,'' as appn16,'' as appn17,'' as appn18,'' as appn19,'' as appn20,'' as appn21,'' as appn22,'' as appn23,'' as appn24,'' as appn25,'' as appn26,'' as appn27,'' as appn28,'' as appn29,'' as appn30,'' as appn31,'' as changepoint,'' as result1,'' as action1,'' as result2,'' as action2,'' as remark,jm.toolname,jm.serial as toolid  from  InspectionItem as a  inner join EquipmentList as c on a.equid=c.equid  inner join LineGroupMaster as b on c.partnumber = b.partnumber  inner join MachineMaster as m on c.machineid=m.machineid inner join JigMaster as jm on b.lamp=jm.lamp and b.model=jm.model and b.linename=jm.linename   Where 1=1  {0} ", filmaster);


                }
                else
                {
                    strquery = string.Format(" select '' as processname, '' as piz1,'' as piz2,'' as piz3,'' as piz4, 0.00 as value_x,0.00 as value_r, std_qty,add_qty,dip_qty, '' as customerno,'' as customername, '' as partno_hd,'' as partname_hd,'' as icsno,'' as icsname ,lamp, case when  charindex('LHD',model)>0 then 'LHD' else 'RHD' end as modeltype, b.linegroupcode,b.linename,b.model,b.partnumber,c.machineid,c.machinename,b.lamp,a.*, " +
     " '' as hd1,'' as hd2,'' as hd3,'' as hd4,'' as hd5,'' as hd6,'' as hd7,'' as hd8,'' as hd9,'' as hd10, '' as hd11,'' as hd12,'' as hd13,'' as hd14,	'' as hd15,'' as hd15,'' as hd17,'' as hd18,'' as hd19,'' as hd20,'' as hd21,'' as hd22,'' as hd23,'' as hd24,'' as hd25,'' as hd26,'' as hd27,'' as hd28,'' as hd29,'' as hd30,'' as hd31, " +
    " '' as d1,'' as d2,'' as d3,'' as d4,'' as d5,'' as d6,'' as d7,'' as d8,'' as d9,'' as d10, '' as d11,'' as d12,'' as d13,'' as d14,'' as d15,'' as d16,'' as d17,'' as d18,'' as d19,'' as d20, '' as d21,'' as d22,'' as d23,'' as d24,'' as d25,'' as d26,'' as d27,'' as d28,'' as d29,'' as d30,'' as d31, " +
    " '' as n1,'' as n2,'' as n3,'' as n4,'' as n5,'' as n6,'' as n7,'' as n8,'' as n9,'' as n10, '' as n11,'' as n12,'' as n13,'' as n14,'' as n15,'' as n16,'' as n17,'' as n18,'' as n19,'' as n20, '' as n21, '' as n22, '' as n23, '' as n24, '' as n25, '' as n26, '' as n27, '' as n28, '' as n29, '' as n30, '' as n31,  " +
    " '' as chkd1,'' as chkd2,'' as chkd3,'' as chkd4,'' as chkd5,'' as chkd6,'' as chkd7,'' as chkd8,'' as chkd9,'' as chkd10,  '' as chkd11,'' as chkd12,'' as chkd13,'' as chkd14,'' as chkd15,'' as chkd16,'' as chkd17,'' as chkd18,'' as chkd19,'' as chkd20,'' as chkd21,'' as chkd22,'' as chkd23,'' as chkd24,'' as chkd25,'' as chkd26,'' as chkd27,'' as chkd28,'' as chkd29,'' as chkd30,'' as chkd31, " +
    " '' as chkn1,'' as chkn2,'' as chkn3,'' as chkn4,'' as chkn5,'' as chkn6,'' as chkn7,'' as chkn8,'' as chkn9,'' as chkn10, '' as chkn11,'' as chkn12,'' as chkn13,'' as chkn14,'' as chkn15,'' as chkn16,'' as chkn17,'' as chkn18,'' as chkn19,'' as chkn20,'' as chkn21,'' as chkn22,'' as chkn23,'' as chkn24,'' as chkn25,'' as chkn26,'' as chkn27,'' as chkn28,'' as chkn29,'' as chkn30,'' as chkn31, " +
    " '' as appd1,'' as  appd2,'' as  appd3,'' as  appd4,'' as  appd5,'' as  appd6,'' as  appd7,'' as  appd8,'' as  appd9,'' as appd10,'' as appd11,'' as appd12,'' as appd13,'' as appd14,'' as appd15,'' as appd16,'' as appd17,'' as appd18,'' as appd19,'' as appd20,'' as appd21,'' as appd22,'' as appd23,'' as appd24,'' as appd25,'' as appd26,'' as appd27,'' as appd28,'' as appd29,'' as appd30,'' as appd31, " +
    " '' as  appn1,'' as  appn2,'' as  appn3,'' as  appn4,'' as  appn5,'' as  appn6,'' as  appn7,'' as  appn8,'' as  appn9,'' as  appn10, '' as appn11,'' as appn12,'' as appn13,'' as appn14,'' as appn15,'' as appn16,'' as appn17,'' as appn18,'' as appn19,'' as appn20,'' as appn21,'' as appn22,'' as appn23,'' as appn24,'' as appn25,'' as appn26,'' as appn27,'' as appn28,'' as appn29,'' as appn30,'' as appn31,'' as changepoint,'' as result1,'' as action1,'' as result2,'' as action2,'' as remark,'' as toolname,'' as toolid " +
    " from InspectionItem as a inner join EquipmentList as e on a.equid=e.equid inner join MachineMaster as c on e.machineid=c.machineid inner join LineGroupMaster as b on e.linegroupid=b.linegroupid  Where 1=1 {0} ", filmaster);
                }
                dt = fc.SQL_DataTable(strquery.ToString());
               
                int icol=1;
                foreach (DataRow dr in dt.Rows)
                {
                    if (type == "11")
                    {
                        string str_inspect = string.Format("select shift_id,a.equid,a.itemno,a.itemname,inspectiondate,b.linegroupid,b.partnumber,'' as machineid, convert(varchar(10),inspectiondate,103) as  inspect_date,a.inspectid,case when resultvalue is NULL then resultstatus else convert(varchar(20),resultvalue) end result,notice,confirm1_status,confirm1_notice,confirm1_date,confirm1_userid,check1_status,check1_notice,check1_date,check1_userid,approve1_status,approve1_notice,approve1_date,approve1_userid,a.create_user,a.idrefno,a. detailchangepoint, a.result1, a.action1, a.result2, a.action2,a.lotno, a.orderqty, a.wasteqty  from  InspectionInput as a inner join LineGroupMaster as b on a.partnumber=b.partnumber  Where 1=1  and inspectiontype='4' and  a.equid='0' and itemno='{0}' {1}   order by convert(float,a.itemno)   ", dr["itemno"].ToString(), filter);
                        DataTable dtinst = fc.SQL_DataTable(str_inspect);
                        if (dtinst.Rows.Count > 0)
                        {
                            if(dtinst.Rows[0]["itemno"].ToString().IndexOf(".")==-1)
                            {

                                string str_inspect_sub = string.Format("select a.resultstatus,shift_id,a.equid,a.itemno,a.itemname,inspectiondate,b.linegroupid,b.partnumber,'' as machineid, convert(varchar(10),inspectiondate,103) as  inspect_date,a.inspectid,case when resultvalue is NULL then resultstatus else convert(varchar(20),resultvalue) end result,notice,confirm1_status,confirm1_notice,confirm1_date,confirm1_userid,check1_status,check1_notice,check1_date,check1_userid,approve1_status,approve1_notice,approve1_date,approve1_userid,a.create_user,a.idrefno,a. detailchangepoint, a.result1, a.action1, a.result2, a.action2,a.lotno, a.orderqty, a.wasteqty,c.input_x  from  InspectionInput as a inner join LineGroupMaster as b on a.partnumber=b.partnumber inner join (select input_x,itemno from InspectionItem Where inspection_type='4' and equid='0' ) as c on a.itemno=c.itemno  Where 1=1  and inspectiontype='4' and  a.equid='0' and idrefno='{0}' and charindex('.',a.itemno)>0    order by convert(float,a.itemno) ", dtinst.Rows[0]["idrefno"].ToString());
                                DataTable dtinst_sub = fc.SQL_DataTable(str_inspect_sub);
                                foreach (DataRow drsub in dtinst_sub.Rows)
                                {
                                    dr["chkd" + drsub["input_x"].ToString()] = drsub["resultstatus"].ToString();


                                }
                                dr["create_user"] = _getuaser_data(dtinst.Rows[0]["create_user"].ToString());
                                dr["hd1"] = dtinst.Rows[0]["inspect_date"].ToString();
                                dr["hd2"] = dr["model"] + "  " + dtinst.Rows[0]["partnumber"].ToString();
                                dr["changepoint"] = dtinst.Rows[0]["detailchangepoint"].ToString();
                                dr["action2"] = dtinst.Rows[0]["action2"].ToString();
                                dr["action1"] = dtinst.Rows[0]["action1"].ToString();
                                dr["input_type"] = dtinst.Rows[0]["itemno"].ToString();
                                dr["chkd18"] = dtinst.Rows[0]["lotno"].ToString();
                                dr["chkd19"] = dtinst.Rows[0]["orderqty"].ToString();
                                dr["chkd20"] = dtinst.Rows[0]["wasteqty"].ToString();
                                dr["result1"] = dtinst.Rows[0]["result1"].ToString();
                                try
                                {
                                    string[] arr = dtinst.Rows[0]["inspect_date"].ToString().Split('/');

                                    dr["d" + arr[0]] = "/";

                                }
                                catch { };




                            }
                            
                           

                        }

                    }
                    else if (type=="7")
                    {
                         
                        string fpath = HttpContext.Current.Server.MapPath("image/torque/" + dr["pizlink"].ToString()+".jpg").Replace("\\setting", "");
                        string imagePath1 = new Uri(fpath).AbsoluteUri;
                        dr["piz1"] = imagePath1;
                        string str_inspect = string.Format("select shift_id,a.equid,a.itemno,a.itemname,inspectiondate,b.linegroupid,b.partnumber,b.machineid, convert(varchar(10),inspectiondate,103) as  inspect_date,a.inspectid,case when resultvalue is NULL then resultstatus else convert(varchar(20),resultvalue) end result,notice,confirm1_status,confirm1_notice,confirm1_date,confirm1_userid,check1_status,check1_notice,check1_date,check1_userid,approve1_status,approve1_notice,approve1_date,approve1_userid,a.create_user from  InspectionInput as a inner join EquipmentList as b on a.equid=b.equid inner join (select inspectid,report_type from InspectionItem) as c on a.inspectid=c.inspectid  Where 1=1 and a.inspectid='{0}' and  a.equid='{1}' and itemno='{2}' {3}   order by inspectiondate  ", dr["inspectid"].ToString(), dr["equid"].ToString(), dr["itemno"].ToString(), filter);
                        DataTable dtinst = fc.SQL_DataTable(str_inspect);
                        if (dtinst.Rows.Count > 0)
                        {
                            dr["createuserid"] = dtinst.Rows[0]["create_user"].ToString();
                            if (chkdate.Length == 0)
                            {
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            else if (chkdate != dtinst.Rows[0]["inspect_date"].ToString())
                            {
                                icol++;
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            dr["hd" + icol.ToString()] = dtinst.Rows[0]["inspect_date"].ToString();
                            if (dtinst.Rows[0]["shift_id"].ToString() == "1")
                            {
                                dr["d" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkd" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appd" + icol.ToString()] = "Y";

                                }

                            }
                            else
                            {

                                dr["n" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkn" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appn" + icol.ToString()] = "Y";

                                }
                            }


                        }




                    }
                    else if (type == "8")
                    {

                        string fpath = HttpContext.Current.Server.MapPath("image/checkweekmachine/" + dr["pizlink"].ToString() + ".jpg").Replace("\\setting", "");
                        string imagePath1 = new Uri(fpath).AbsoluteUri;
                        dr["piz1"] = imagePath1;
                        string str_inspect = string.Format("select shift_id,a.equid,a.itemno,a.itemname,inspectiondate,b.linegroupid,b.partnumber,b.machineid, convert(varchar(10),inspectiondate,103) as  inspect_date,a.inspectid,case when resultvalue is NULL then resultstatus else convert(varchar(20),resultvalue) end result,notice,confirm1_status,confirm1_notice,confirm1_date,confirm1_userid,check1_status,check1_notice,check1_date,check1_userid,approve1_status,approve1_notice,approve1_date,approve1_userid,a.create_user from  InspectionInput as a inner join (select a.*,b.linename,b.model,b.lamp from EquipmentList as a inner join LineGroupMaster as b  on a.partnumber=b.partnumber) as b on a.equid=b.equid inner join (select inspectid,report_type from InspectionItem) as c on a.inspectid=c.inspectid  Where 1=1 and a.inspectid='{0}' and  a.equid='{1}' and itemno='{2}' {3}   order by inspectiondate  ", dr["inspectid"].ToString(), dr["equid"].ToString(), dr["itemno"].ToString(), filter);
                        DataTable dtinst = fc.SQL_DataTable(str_inspect);
                        if (dtinst.Rows.Count > 0)
                        {
                            dr["createuserid"] = dtinst.Rows[0]["create_user"].ToString();
                            if (chkdate.Length == 0)
                            {
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            else if (chkdate != dtinst.Rows[0]["inspect_date"].ToString())
                            {
                                icol++;
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            dr["hd" + icol.ToString()] = dtinst.Rows[0]["inspect_date"].ToString();
                            if (dtinst.Rows[0]["shift_id"].ToString() == "1")
                            {
                                dr["d" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkd" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appd" + icol.ToString()] = "Y";

                                }

                            }
                            else
                            {

                                dr["n" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkn" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appn" + icol.ToString()] = "Y";

                                }
                            }


                        }




                    }
                    else if (type == "5")
                    {

                        string fpath = HttpContext.Current.Server.MapPath("image/jig/" + dr["pizlink"].ToString() + ".jpg").Replace("\\setting", "");
                        string imagePath1 = new Uri(fpath).AbsoluteUri;
                        dr["piz1"] = imagePath1;
                        string str_inspect = string.Format("select shift_id,a.equid,a.itemno,a.itemname,inspectiondate,b.linegroupid,b.partnumber,b.machineid, convert(varchar(10),inspectiondate,103) as  inspect_date,a.inspectid,case when resultvalue is NULL then resultstatus else convert(varchar(20),resultvalue) end result,notice,confirm1_status,confirm1_notice,confirm1_date,confirm1_userid,check1_status,check1_notice,check1_date,check1_userid,approve1_status,approve1_notice,approve1_date,approve1_userid,a.create_user from  InspectionInput as a inner join (select a.*,b.linename,b.model,b.lamp from EquipmentList as a inner join LineGroupMaster as b  on a.partnumber=b.partnumber) as b on a.equid=b.equid inner join (select inspectid,report_type from InspectionItem) as c on a.inspectid=c.inspectid  Where 1=1 and a.inspectid='{0}' and  a.equid='{1}' and itemno='{2}' {3}   order by inspectiondate  ", dr["inspectid"].ToString(), dr["equid"].ToString(), dr["itemno"].ToString(), filter);
                        DataTable dtinst = fc.SQL_DataTable(str_inspect);
                        if (dtinst.Rows.Count > 0)
                        {
                            dr["createuserid"] = dtinst.Rows[0]["create_user"].ToString();
                            if (chkdate.Length == 0)
                            {
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            else if (chkdate != dtinst.Rows[0]["inspect_date"].ToString())
                            {
                                icol++;
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            dr["hd" + icol.ToString()] = dtinst.Rows[0]["inspect_date"].ToString();
                            if (dtinst.Rows[0]["shift_id"].ToString() == "1")
                            {
                                dr["d" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkd" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appd" + icol.ToString()] = "Y";

                                }

                            }
                            else
                            {

                                dr["n" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkn" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appn" + icol.ToString()] = "Y";

                                }
                            }


                        }




                    }
                    else if (type == "6")
                    {

                      
                        string str_inspect = string.Format("select shift_id,a.equid,a.itemno,a.itemname,inspectiondate,b.linegroupid,b.partnumber,b.machineid, convert(varchar(10),inspectiondate,103) as  inspect_date,a.inspectid,case when resultvalue is NULL then resultstatus else convert(varchar(20),resultvalue) end result,notice,confirm1_status,confirm1_notice,confirm1_date,confirm1_userid,check1_status,check1_notice,check1_date,check1_userid,approve1_status,approve1_notice,approve1_date,approve1_userid,a.create_user from  InspectionInput as a inner join (select a.*,b.linename,b.model,b.lamp from EquipmentList as a inner join LineGroupMaster as b  on a.partnumber=b.partnumber) as b on a.equid=b.equid inner join (select inspectid,report_type from InspectionItem) as c on a.inspectid=c.inspectid  Where 1=1 and a.inspectid='{0}' and  a.equid='{1}' and itemno='{2}' {3}   order by inspectiondate  ", dr["inspectid"].ToString(), dr["equid"].ToString(), dr["itemno"].ToString(), filter);
                        DataTable dtinst = fc.SQL_DataTable(str_inspect);
                        if (dtinst.Rows.Count > 0)
                        {
                            dr["createuserid"] = dtinst.Rows[0]["create_user"].ToString();
                            if (chkdate.Length == 0)
                            {
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            else if (chkdate != dtinst.Rows[0]["inspect_date"].ToString())
                            {
                                icol++;
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            dr["hd" + icol.ToString()] = dtinst.Rows[0]["inspect_date"].ToString();
                            if (dtinst.Rows[0]["shift_id"].ToString() == "1")
                            {
                                dr["d" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkd" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appd" + icol.ToString()] = "Y";

                                }

                            }
                            else
                            {

                                dr["n" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkn" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appn" + icol.ToString()] = "Y";

                                }
                            }


                        }




                    }
                    else if (type == "3")
                    {
                        string fpath = HttpContext.Current.Server.MapPath("image/mark/" + dr["pizlink"].ToString() + ".jpg").Replace("\\setting", "");
                        string imagePath1 = new Uri(fpath).AbsoluteUri;
                        dr["piz1"] = imagePath1;

                        string str_inspect = string.Format("select shift_id,a.equid,a.itemno,a.itemname,inspectiondate,b.linegroupid,b.partnumber,b.machineid, convert(varchar(10),inspectiondate,103) as  inspect_date,a.inspectid,case when resultvalue is NULL then resultstatus else convert(varchar(20),resultvalue) end result,notice,confirm1_status,confirm1_notice,confirm1_date,confirm1_userid,check1_status,check1_notice,check1_date,check1_userid,approve1_status,approve1_notice,approve1_date,approve1_userid,a.create_user from  InspectionInput as a inner join (select a.*,b.linename,b.model,b.lamp from EquipmentList as a inner join LineGroupMaster as b  on a.partnumber=b.partnumber) as b on a.equid=b.equid inner join (select inspectid,report_type from InspectionItem) as c on a.inspectid=c.inspectid  Where 1=1 and a.inspectid='{0}' and  a.equid='{1}' and itemno='{2}' {3}   order by inspectiondate  ", dr["inspectid"].ToString(), dr["equid"].ToString(), dr["itemno"].ToString(), filter);
                        DataTable dtinst = fc.SQL_DataTable(str_inspect);
                        if (dtinst.Rows.Count > 0)
                        {
                            dr["createuserid"] = dtinst.Rows[0]["create_user"].ToString();
                            if (chkdate.Length == 0)
                            {
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            else if (chkdate != dtinst.Rows[0]["inspect_date"].ToString())
                            {
                                icol++;
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            dr["hd" + icol.ToString()] = dtinst.Rows[0]["inspect_date"].ToString();
                            if (dtinst.Rows[0]["shift_id"].ToString() == "1")
                            {
                                dr["d" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkd" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appd" + icol.ToString()] = "Y";

                                }

                            }
                            else
                            {

                                dr["n" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkn" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appn" + icol.ToString()] = "Y";

                                }
                            }


                        }




                    }
                    else
                    {
                        string str_inspect = string.Format("select shift_id,a.equid,a.itemno,a.itemname,inspectiondate,b.linegroupid,b.partnumber,b.machineid, convert(varchar(10),inspectiondate,103) as  inspect_date,a.inspectid,case when resultvalue is NULL then resultstatus else convert(varchar(20),resultvalue) end result,notice,confirm1_status,confirm1_notice,confirm1_date,confirm1_userid,check1_status,check1_notice,check1_date,check1_userid,approve1_status,approve1_notice,approve1_date,approve1_userid,a.create_user from  InspectionInput as a inner join EquipmentList as b on a.equid=b.equid inner join (select inspectid,report_type from InspectionItem) as c on a.inspectid=c.inspectid  Where 1=1 and a.inspectid='{0}' and  a.equid='{1}' and itemno='{2}' {3}   order by inspectiondate  ", dr["inspectid"].ToString(), dr["equid"].ToString(), dr["itemno"].ToString(), filter);
                        DataTable dtinst = fc.SQL_DataTable(str_inspect);
                        if (dtinst.Rows.Count > 0)
                        {
                            dr["createuserid"] = dtinst.Rows[0]["create_user"].ToString();
                            if (chkdate.Length == 0)
                            {
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            else if (chkdate != dtinst.Rows[0]["inspect_date"].ToString())
                            {
                                icol++;
                                chkdate = dtinst.Rows[0]["inspect_date"].ToString();

                            }
                            dr["hd" + icol.ToString()] = dtinst.Rows[0]["inspect_date"].ToString();
                            if (dtinst.Rows[0]["shift_id"].ToString() == "1")
                            {
                                dr["d" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkd" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appd" + icol.ToString()] = "Y";

                                }

                            }
                            else
                            {

                                dr["n" + icol.ToString()] = dtinst.Rows[0]["result"].ToString();
                                if (dtinst.Rows[0]["check1_status"].ToString() == "Y")
                                {
                                    dr["chkn" + icol.ToString()] = "Y";

                                }
                                if (dtinst.Rows[0]["approve1_status"].ToString() == "Y")
                                {
                                    dr["appn" + icol.ToString()] = "Y";

                                }
                            }


                        }



                    }



                }




                fc.closeconnection();

            }
            return dt;





        }
        private DataTable _genDataSourcexr(string filter, string type, string week)
        {
            DataTable dt = new DataTable();
            string chkdate = string.Empty;



            string sqlquery = string.Empty;
            using (FcSql.Function fc = new FcSql.Function())
            {


                string[] arrx = new string[40];
                string[] arr_dx = new string[40];
                string[] arr_uclx = new string[40];
                string[] arr_x = new string[40];
                string[] arr_lclx = new string[40];

                string[] arrr = new string[40];
                string[] arr_dr = new string[40];
                string[] arr_uclr = new string[40];
                string[] arr_r = new string[40];
                string[] arr_lclr = new string[40];


                string strquery = string.Empty;
                strquery = string.Format("select d.create_user as createuserid,d.check1_userid as checkuserid,d.approve1_userid as approveuserid, 0.00 as uclx,0.00 as x,0.00 lclx,0.00 as uclr,0.00 as r,0.00 lclr,b.lamp,b.linegroupid,b.linename,case when charindex('RHD',b.model)>0 then 'RHD' else 'LHD' end as modeltype,  b.model,b.partnumber,'ASSEMBLY' as dept,case when charindex('POINT',a.chart_type)>0 then 'GREASE' else 'HOT MELT' end as operation,case when charindex('POINT',a.chart_type)>0 then 'WEIGHT' else 'น้ำหนักกาว' end as cate,case when charindex('POINT',a.chart_type)>0 then '26 +5, -0  mg.' else '39.0+2, -0 g' end as es,'' as partno,'' as partname,'' as icsno,'' as icsname,  a.* from XR_Value as a inner join EquipmentList as c on a.equid=c.equid inner join LineGroupMaster  as b on   c.linegroupid=b.linegroupid  inner join InspectionInput as d on replace(convert(varchar(10),a.xr_date,102),'.','-')=replace(convert(varchar(10),d.inspectiondate,102),'.','-') and a.chart_type=d.itemname and a.equid=d.equid     Where 1=1 {0}	order by a.xr_date", filter);
                dt = fc.SQL_DataTable(strquery.ToString());
                if(dt.Rows.Count>0)
                {
                   string xvalue =  get_xrchart_value(dt.Rows[0]["equid"].ToString(), dt.Rows[0]["chart_type"].ToString(), dt.Rows[0]["n_value"].ToString(), "x");
                   string rvalue = get_xrchart_value(dt.Rows[0]["equid"].ToString(), dt.Rows[0]["chart_type"].ToString(), dt.Rows[0]["n_value"].ToString(), "r");

                   if (xvalue.IndexOf("|") != -1)
                   {
                       
                       arrx = xvalue.Split('|');
                       arr_dx = arrx[0].Split(',');
                       arr_uclx = arrx[1].Split(',');
                       arr_x = arrx[2].Split(',');
                       arr_lclx = arrx[3].Split(',');

                   }
                   if (rvalue.IndexOf("|") != -1)
                   {
                       arrr = rvalue.Split('|');
                       arr_dr = arrr[0].Split(',');
                       arr_uclr = arrr[1].Split(',');
                       arr_r = arrr[2].Split(',');
                       arr_lclr = arrr[3].Split(',');
                   }


                }

               
                string idchk = string.Empty;
                int inum = 0;
                foreach (DataRow dr in dt.Rows)
                {

                    if(idchk.Length==0)
                    {
                        idchk = dr["id"].ToString();
                        inum = 0;

                    }
                    else
                    {
                        if(idchk!=dr["id"].ToString())
                        {
                            inum++;
                            idchk = dr["id"].ToString();


                        }



                    }

                    try
                    {
                        dr["uclx"] = double.Parse(arr_uclx[inum].ToString());
                    }
                    catch { }

                    try
                    {
                        dr["x"] = double.Parse(arr_x[inum].ToString());
                    }
                    catch { }

                    try
                    {
                        dr["lclx"] = double.Parse(arr_lclx[inum].ToString());
                    }
                    catch { }


                    try
                    {
                        dr["uclr"] = double.Parse(arr_uclr[inum].ToString());
                    }
                    catch { }

                    try
                    {
                        dr["r"] = double.Parse(arr_r[inum].ToString());
                    }
                    catch { }

                    try
                    {
                        dr["lclr"] = double.Parse(arr_lclr[inum].ToString());
                    }
                    catch { }


                    if(dr["chart_type"].ToString().IndexOf("POINT")!=-1)
                    {
                        dr["partno"] = "ALL TYPE";
                        dr["icsno"] = "ALL TYPE";
                        dr["icsname"] = "ALL TYPE";
                        try
                        {
                            string part = dr["model"].ToString();
                            string[] arr = part.Split(' ');
                            dr["partname"] = arr[0] + " " + arr[1];
                        }
                        catch { };


                    }
                    else
                    {

                        dr["partno"] = "1194075701\r\n518665801";
                        dr["icsno"] = "-";
                        dr["icsname"] = "W7541L/COMB HL\r\nW7541R/COMB HL";
                        try
                        {
                            string part = dr["model"].ToString();
                            string[] arr = part.Split(' ');
                            dr["partname"] = arr[0] + " " + arr[1] + "(" + dr["modeltype"].ToString() + ")";
                        }
                        catch { };

                    }

                }
                

                




                fc.closeconnection();

            }
            return dt;





        }
        public static string get_xrchart_value(string equid, string point, string nvalue, string type)
        {
            string value = string.Empty;
            string xvalue = string.Empty;
            string yvalue0 = string.Empty;
            string yvalue1 = string.Empty;
            string yvalue2 = string.Empty;
            string topic = string.Empty;


            using (FcSql.Function fc = new FcSql.Function())
            {

                string uclx = "0";
                string lclx = "0";
                string uclr = "0";
                string lclr = "0";

                string str_topic = string.Format("select c.lamp, c.model, c.partnumber,linename,machinename from 	EquipmentList as a inner join MachineMaster as b on a.MachineID=b.MachineID inner join LineGroupMaster as c on a.linegroupid=c.linegroupid		Where equid='{0}'", equid);
                DataTable dttopic = fc.SQL_DataTable(str_topic);
                if (dttopic.Rows.Count > 0)
                {
                    topic = "Lamp : " + dttopic.Rows[0]["lamp"].ToString() + " Line : " + dttopic.Rows[0]["linename"].ToString() + " Model : " + dttopic.Rows[0]["model"].ToString() + " Machine : " + dttopic.Rows[0]["machinename"].ToString();

                }

                string str_value = string.Format("select a.ucl_value_x,a.lcl_value_x,ucl_value_r,lcl_value_r   from XR_Chart as a inner join (select max(chart_date_time) as chart_date_time,chart_type,equid,n_value from 	 XR_Chart  where  month(chart_date_time)  =  month(DATEADD(month,-1,GETDATE())) and  year(chart_date_time)  =  year(DATEADD(month,-1,GETDATE())) and chart_type='{1}' and equid='{0}'  and n_value='{2}'  group by chart_type,equid,n_value) as b on a.chart_date_time=b.chart_date_time and a.chart_type=b.chart_type and a.equid=b.equid and a.n_value=b.n_value", equid, point, nvalue);
                DataTable dt = fc.SQL_DataTable(str_value.ToString());
                if (dt.Rows.Count > 0)
                {

                    uclx = dt.Rows[0]["ucl_value_x"].ToString();
                    lclx = dt.Rows[0]["lcl_value_x"].ToString();

                    uclr = dt.Rows[0]["ucl_value_r"].ToString();
                    lclr = dt.Rows[0]["lcl_value_r"].ToString();


                    //max0 = (double.Parse(uclx) + 1).ToString();
                    //min0 = (double.Parse(lclx) - 1).ToString();

                }

                else
                {

                    str_value = string.Format("select ucl_value_x,lcl_value_x,ucl_value_r,lcl_value_r   from XR_Chart Where chart_type='{1}' and equid='{0}'  and n_value='{2}' ", "all", point, nvalue);
                    dt = fc.SQL_DataTable(str_value.ToString());
                    if (dt.Rows.Count > 0)
                    {

                        uclx = dt.Rows[0]["ucl_value_x"].ToString();
                        lclx = dt.Rows[0]["lcl_value_x"].ToString();

                        uclr = dt.Rows[0]["ucl_value_r"].ToString();
                        lclr = dt.Rows[0]["lcl_value_r"].ToString();

                    }

                }

                string str_result = string.Format("select convert(varchar(5),month(chart_date_time)) as caption, x_value,r_value,{3} as xucl_value,{4} as xlcl_value,{5} as rucl_value,{6} as rlcl_value  from XR_Chart	 where  month(chart_date_time)  =  month(GETDATE()) and  year(chart_date_time)  =  year(GETDATE()) and chart_type='{1}' and equid='{0}' and n_value='{2}'", equid, point, nvalue, uclx, lclx, uclr, lclr);
                DataTable dtv = fc.SQL_DataTable(str_result);
                int numcount = 1;
                foreach (DataRow dr in dtv.Rows)
                {
                    if (xvalue.Length == 0)
                    {
                        xvalue = dr["caption"].ToString() + "-" + numcount.ToString();

                    }
                    else
                    {
                        xvalue = xvalue + "," + dr["caption"].ToString() + "-" + numcount.ToString();

                    }
                    numcount++;
                    if (yvalue0.Length == 0)
                    {
                        if (type == "x")
                        {
                            yvalue0 = dr["xucl_value"].ToString();
                        }
                        else
                        {

                            yvalue0 = dr["rucl_value"].ToString();

                        }

                    }
                    else
                    {
                        if (type == "x")
                        {
                            yvalue0 = yvalue0 + "," + dr["xucl_value"].ToString();
                        }
                        else
                        {
                            yvalue0 = yvalue0 + "," + dr["rucl_value"].ToString();

                        }

                    }

                    if (yvalue1.Length == 0)
                    {
                        if (type == "x")
                        {
                            yvalue1 = dr["x_value"].ToString();
                        }
                        else
                        {

                            yvalue1 = dr["r_value"].ToString();
                        }

                    }
                    else
                    {
                        if (type == "x")
                        {
                            yvalue1 = yvalue1 + "," + dr["x_value"].ToString();
                        }
                        else
                        {
                            yvalue1 = yvalue1 + "," + dr["r_value"].ToString();

                        }

                    }

                    if (yvalue2.Length == 0)
                    {
                        if (type == "x")
                        {
                            yvalue2 = dr["xlcl_value"].ToString();
                        }
                        else
                        {

                            yvalue2 = dr["rlcl_value"].ToString();
                        }

                    }
                    else
                    {
                        if (type == "x")
                        {
                            yvalue2 = yvalue2 + "," + dr["xlcl_value"].ToString();
                        }
                        else
                        {
                            yvalue2 = yvalue2 + "," + dr["rlcl_value"].ToString();

                        }
                    }


                }





            }


            return xvalue + "|" + yvalue0 + "|" + yvalue1 + "|" + yvalue2 + "|" + topic + "|" + point;

        }

    }
}