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.Globalization;

namespace CheckSheet
{
    public partial class XR_chart_input_check_number : System.Web.UI.Page
    {
           
        [System.Web.Services.WebMethod]
        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;

        }


        [System.Web.Services.WebMethod]
        public static string calxrchart(string point, string nvalue, string xvalue, string readtime, string rvalue, string valuepern,string pubequid)
        {
            string charttype = point;
            string n = nvalue;
      

            try
            {
                double tmtread = 0;
                double nv1 = 0;
                double nv2 = 0;
                double nv3 = 0;
                double nv4 = 0;
                double nv5 = 0;
                double nv6 = 0;
                double nv7 = 0;
                double nv8 = 0;
                double nv9 = 0;
                double nv10 = 0;
                double xsum = 0;
                double rsum = 0;

                if (valuepern.IndexOf("|") != -1)
                {
                    string[] arr = valuepern.Split('|');
                    try
                    {
                        nv1 = double.Parse(arr[0]);

                    }
                    catch { };
                    try
                    {
                        nv2 = double.Parse(arr[1]);

                    }
                    catch { };
                    try
                    {
                        nv3 = double.Parse(arr[2]);

                    }
                    catch { };
                    try
                    {
                        nv4 = double.Parse(arr[3]);

                    }
                    catch { };
                    try
                    {
                        nv5 = double.Parse(arr[4]);

                    }
                    catch { };
                    try
                    {
                        nv6 = double.Parse(arr[5]);

                    }
                    catch { };
                    try
                    {
                        nv7 = double.Parse(arr[6]);

                    }
                    catch { };
                    try
                    {
                        nv8 = double.Parse(arr[7]);

                    }
                    catch { };
                    try
                    {
                        nv9 = double.Parse(arr[8]);

                    }
                    catch { };
                    try
                    {
                        nv10 = double.Parse(arr[9]);

                    }
                    catch { };


                    double totalsum = (nv1 + nv2 + nv3 + nv4 + nv5 + nv6 + nv7 + nv8 + nv9 + nv10);

                    xsum = totalsum / double.Parse(n);
                    using (FcSql.Function fc = new FcSql.Function())
                    {
                        string _id = string.Empty;
                        string _q = string.Format(" select max(convert(float,id)) as id from XR_Value ");
                        DataTable dtgenid = fc.SQL_DataTable(_q.ToString());
                        if (dtgenid.Rows.Count == 0)
                        {
                            _id = "1";
                        }
                        else
                        {
                            foreach (DataRow dr in dtgenid.Rows)
                            {
                                if (dr[0].ToString() == "")
                                {
                                    _id = "1";
                                }
                                else
                                {
                                    _id = (int.Parse(dr[0].ToString()) + 1).ToString();
                                }
                            }
                        }


                        int icheck = 1;
                        foreach (string str in arr)
                        {
                            string str_xrvalue = string.Format("insert into XR_Value (id,xr_date, chart_type, n_value, n_readseq, result, x_sum, r_sum,total_sum,equid) values ({0},'{1}','{2}',{3},{4},{5},{6},{7},{8},'{9}') ", _id, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", new CultureInfo("en-US")), point, n, icheck.ToString(), str.Replace(",", ""), xsum.ToString(), 0, totalsum.ToString(),pubequid);
                            string cmdinst = fc.ExecSQLStatement(str_xrvalue.ToString());
                            icheck++;
                        }

                        double dmin = 0;
                        double dmax = 0;
                        string find_min_max = string.Format("select min(result) as v_min,max(result) as v_max from XR_Value Where id={0}", _id);
                        DataTable dt = fc.SQL_DataTable(find_min_max.ToString());
                        if (dt.Rows.Count > 0)
                        {
                            try
                            {
                                dmin = double.Parse(dt.Rows[0]["v_min"].ToString());
                                dmax = double.Parse(dt.Rows[0]["v_max"].ToString());
                            }
                            catch { };
                            rsum = dmax - dmin;

                            string up_rsum = string.Format("update XR_Value  set r_sum={0} Where id={1}", rsum.ToString(), _id);
                            string cmdup = fc.ExecSQLStatement(up_rsum.ToString());


                        }

                        fc.closeconnection();
                    }
                }



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

                    string str_get_xr = string.Format("select sum(x_sum) as x_sum_total,sum(r_sum) as r_sum_total,count(a.id) as counttime from XR_VAlue as a inner join (select max(n_readseq) as n_readseq,id from XR_VAlue group by id) as b on a.id=b.id and a.n_readseq=b.n_readseq	 Where   month(xr_date)=month(GETDATE()) and year(xr_date)=year(GETDATE()) and equid='{0}'",pubequid);
                    DataTable dtxr = fc.SQL_DataTable(str_get_xr);

                    if (dtxr.Rows.Count > 0)
                    {
                        xvalue = dtxr.Rows[0]["x_sum_total"].ToString();
                        rvalue = dtxr.Rows[0]["r_sum_total"].ToString();
                        tmtread = double.Parse(dtxr.Rows[0]["counttime"].ToString());

                    }
                

                    double x_tmt = double.Parse(xvalue.ToString().Replace(",", ""));
                    double r_tmt = double.Parse(rvalue.ToString().Replace(",", ""));
                    double d4 = 0;
                    double d3 = 0;
                    double a2 = 0;

                    double rvalue1 = 0;
                    double rvalue2 = 0;
                    double rvalue3 = 0;

                    double xvalue1 = 0;
                    double xvalue2 = 0;
                    double xvalue3 = 0;


                    double xm1 = x_tmt / tmtread;
                    double rm1 = r_tmt / tmtread;

                    string str_lookup = string.Format("select n_value,a_2,d_3,d_4 from XR_Table_value	Where n_value={0}", n);
                    DataTable dt = fc.SQL_DataTable(str_lookup);
                    if (dt.Rows.Count > 0)
                    {
                        try
                        {
                            d4 = double.Parse(dt.Rows[0]["d_4"].ToString()) * rm1;
                            d3 = double.Parse(dt.Rows[0]["d_3"].ToString()) * rm1;

                        }
                        catch { };

                        rvalue1 = Math.Round(d4, 1);
                        rvalue2 = Math.Round(rm1, 1);
                        rvalue3 = Math.Round(d3, 1);

                        try
                        {
                            a2 = double.Parse(dt.Rows[0]["a_2"].ToString()) * rm1;
                            xvalue1 = Math.Round(xm1 + a2, 1);
                            xvalue2 = Math.Round(xm1, 1);
                            xvalue3 = Math.Round(xm1 - a2, 1);



                        }
                        catch { };

                        string _id = string.Empty;
                        string _q = string.Format(" select max(convert(real,id)) as id from XR_Chart ");
                        DataTable dtgenid = fc.SQL_DataTable(_q.ToString());
                        if (dtgenid.Rows.Count == 0)
                        {
                            _id = "1";
                        }
                        else
                        {
                            foreach (DataRow dr in dtgenid.Rows)
                            {
                                if (dr[0].ToString() == "")
                                {
                                    _id = "1";
                                }
                                else
                                {
                                    _id = (int.Parse(dr[0].ToString()) + 1).ToString();
                                }
                            }
                        }

                        string str_inst = string.Format("insert into XR_Chart (id, chart_type, n_value, chart_date_time, ucl_value_x, x_value, lcl_value_x, ucl_value_r, r_value, lcl_value_r,equid) values ('{0}','{1}',{2},'{3}',{4},{5},{6},{7},{8},{9},'{10}')", _id, charttype, n.ToString(), DateTime.Now.ToString("yyyy-MM-dd", new CultureInfo("en-US")), xvalue1.ToString(), xvalue2.ToString(), xvalue3.ToString(), rvalue1.ToString(), rvalue2.ToString(), rvalue3.ToString(),pubequid);
                        string cmd = fc.ExecSQLStatement(str_inst);


                    }




                }

                 //select * from 	 XR_Chart  where  month(chart_date_time)  =  month(DATEADD(month,-1,GETDATE())) and  year(chart_date_time)  =  year(DATEADD(month,-1,GETDATE())) 
            }
            catch { };
            return "";
        }



        [System.Web.Services.WebMethod]
        public static string getdata(string parahd)
        {

            DataSet ds = new DataSet();
            string cmdhd = string.Empty;
            string inspidid = string.Empty;
            string userapprove = string.Empty;
            using (FcSql.Function fc = new FcSql.Function())
            {
                byte[] datahd = System.Convert.FromBase64String(parahd);
                string base64Decoded = System.Text.ASCIIEncoding.UTF8.GetString(datahd);
                string[] strmt = base64Decoded.Split('|');
                try
                {
                    string equid = strmt[0].ToString();
                    string inspectid = strmt[1].ToString();


                    string str_getinput = string.Format("select inspidid,resultstatus,resultvalue, insp_piz,notice from InspectionInput Where equid='{0}' and inspectid='{1}' and inspectiontype='2'  and inspectiondate between '{2} 00:00:00' and '{2} 23:59:59' ", equid, inspectid, DateTime.Now.ToString("yyyy-MM-dd", new CultureInfo("en-US")));
                    ds = fc.SQL_DataSet(str_getinput);
                    fc.closeconnection();

                }
                catch { }
            }
            return ds.GetXml();
        }


        [System.Web.Services.WebMethod]
        public static string save(string parahd)
        {
            string cmdhd = string.Empty;
            string inspidid = string.Empty;
            string userapprove = string.Empty;
            using (FcSql.Function fc = new FcSql.Function())
            {
                byte[] datahd = System.Convert.FromBase64String(parahd);
                string base64Decoded = System.Text.ASCIIEncoding.UTF8.GetString(datahd);
                string[] strmt = base64Decoded.Split('|');
                try
                {
                    string equid = strmt[1].ToString();
                    string inspectid = strmt[2].ToString();
                    string itemname = strmt[3].ToString();

                    string chkid = string.Format("select inspidid from InspectionInput Where equid='{0}' and inspectid='{1}' and itemname='{2}' and inspectiontype='2'  and inspectiondate between '{3} 08:00:00' and '{4} 07:59:59' ", equid, inspectid, itemname, DateTime.Now.ToString("yyyy-MM-dd", new CultureInfo("en-US")), DateTime.Now.AddDays(1).ToString("yyyy-MM-dd", new CultureInfo("en-US")));
                    DataTable dtchkid = fc.SQL_DataTable(chkid.ToString());
                    if (dtchkid.Rows.Count > 0)
                    {
                        inspidid = dtchkid.Rows[0]["inspidid"].ToString();

                    }




                    if (inspidid.Length == 0)
                    {

                        string _pv = string.Empty;
                        string _id = string.Empty;
                        string _q = string.Format(" select max(convert(real,inspidid)) as id from InspectionInput ");
                        DataTable dtgenid = fc.SQL_DataTable(_q.ToString());
                        if (dtgenid.Rows.Count == 0)
                        {
                            _id = "1";
                        }
                        else
                        {
                            foreach (DataRow dr in dtgenid.Rows)
                            {
                                if (dr[0].ToString() == "")
                                {
                                    _id = "1";
                                }
                                else
                                {
                                    _id = (int.Parse(dr[0].ToString()) + 1).ToString();
                                }
                            }
                        }

                        inspidid = _id;


                    }

                    string itemno = string.Empty;
                    string partnumber = string.Empty;
                    string str_inspect = string.Format("select itemno,partnumber from InspectionItem as a inner join  EquipmentList  as b on a.equid=b.equid Where a.itemname='{0}'", itemname);
                    DataTable dtinspect = fc.SQL_DataTable(str_inspect);
                    if (dtinspect.Rows.Count > 0)
                    {

                        itemno = dtinspect.Rows[0]["itemno"].ToString();
                        partnumber = dtinspect.Rows[0]["partnumber"].ToString();
                    }
                    string type = strmt[4].ToString();
                    string result = strmt[5].ToString();
                    string notic = strmt[6].ToString();
                    string user = strmt[7].ToString();
                    string piz = strmt[8].ToString();

                    if(result.IndexOf("*")!=-1)
                    {  
                        string[] arrresult = result.Split('*');
                        double sum = 0;
                        double numroud = 1;
                        foreach (string str in arrresult)
                        {
                            if (str.Length > 0)
                            {
                                sum = sum + double.Parse(str.Replace(",", ""));
                                numroud++;
                            }
                        }

                        result = (sum/numroud).ToString();


                    }

                    string crdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", new CultureInfo("en-US"));

                    string shift_id = string.Empty;
                    string shift_name = string.Empty;


                    string str_shift = string.Format("select  shift_id,shift_name from Work_shift as a inner join EquipmentList as b on a.linegroupid=b.linegroupid  Where b.equid='{0}'	and '{1}' between shift_timestart and shift_timeend", equid, DateTime.Now.ToString("HH:mm:ss", new CultureInfo("en-US")));
                    DataTable dtshift = fc.SQL_DataTable(str_shift);
                    if (dtshift.Rows.Count > 0)
                    {
                        shift_id = dtshift.Rows[0]["shift_id"].ToString();
                        shift_name = dtshift.Rows[0]["shift_name"].ToString();

                    }

                    if (shift_id.Length == 0)
                    {
                        shift_id = "1";
                        shift_name = "Day";

                    }


                    if (piz.Length == 0)
                    {
                        string str_lastpiz = string.Format("select top 1 insp_piz from InspectionInput Where inspectiontype='2' and equid='{0}' and inspectid='{1}' order by create_datetime desc", equid, inspectid);
                        DataTable dtlastpiz = fc.SQL_DataTable(str_lastpiz);
                        if (dtlastpiz.Rows.Count > 0)
                        {
                            piz = dtlastpiz.Rows[0][0].ToString();

                        }

                    }


                    string inspection_date = string.Empty;
                    int chktime = DateTime.Now.Hour;
                    if (chktime >= 0 && chktime < 8)
                    {
                       
                        
                        inspection_date = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd", new CultureInfo("en-US")) + " " + DateTime.Now.ToString("HH:mm:ss", new CultureInfo("en-US"));




                    }
                    else
                    {


                        inspection_date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", new CultureInfo("en-US"));



                    }
                    


                    string inst_mt = string.Format(" insert into InspectionInput (inspidid, equid,inspectid, itemno,itemname, inspectiondate, inspectiontype, resultvalue, notice, flagdel, create_user, create_datetime,insp_piz,partnumber,shift_id,shift_name,insp_status) VALUES ('" + inspidid + "','" + equid + "','" + inspectid + "','" + itemno + "','" + itemname + "','" + inspection_date + "','2'," + result + ",'" + notic + "',0,'" + user + "','" + crdate + "','" + piz + "','" + partnumber + "'," + shift_id + ",'" + shift_name + "','W')");
                    string up_mt = string.Format(" update InspectionInput SET equid= '" + equid + "',inspectid='" + inspectid + "', itemno= '" + itemno + "', itemname= '" + itemname + "',inspectiondate= '" + inspection_date + "',inspectiontype= '2',resultvalue= " + result + ",notice= '" + notic + "',flagdel=0,Update_user= '" + user + "',Update_Datetime= '" + crdate + "',insp_piz='" + piz + "',partnumber='" + partnumber + "',shift_id="+shift_id+",shift_name='"+shift_name+"' Where inspidid= '" + inspidid + "' ");

                    cmdhd = fc.ExecSQLStatement(inst_mt.ToString());
                    if (cmdhd.ToLower().IndexOf("duplicate") != -1)
                    {
                        cmdhd = fc.ExecSQLStatement(up_mt.ToString());
                    }
                    fc.closeconnection();

                }
                catch { };

            }

            //string cmdsendmail = string.Empty;
            //clsControlUI.sendemail("1", "Scrap document waiting for approval.", userapprove);

            //cmdhd = inspidid + "|" + cmdhd;

            return cmdhd;


        }


        [System.Web.Services.WebMethod]
        public static string getinspectiondetail(string equid, string inspectionid)
        {
            DataSet ds;
            using (CheckSheet.FcSql.Function fc = new CheckSheet.FcSql.Function())
            {
                string query = string.Empty;
                query = string.Format("select c.* from EquipmentList as a inner join MachineMaster as b on a.machineid=b.machineid inner join InspectionItem as c on a.equid=c.equid Where a.equid='{0}' and c.inspectid='{1}'", equid, inspectionid);
                ds = fc.SQL_DataSet(query.ToString());
                fc.closeconnection();
            }
            return ds.GetXml();


        }


        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnrequestapprove_Click(object sender, EventArgs e)
        {
            Response.Redirect("XR_check_item");
        }
    }
}