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;
using System.Collections;


namespace CheckSheet.checkinput
{
    public partial class Machine_check_item : System.Web.UI.Page
    {


        [System.Web.Services.WebMethod]
        public static string totalitem(string partnumber, string machineid)
        {
            DataSet ds = new DataSet();
            using (CheckSheet.FcSql.Function fc = new CheckSheet.FcSql.Function())
            {
                string query = string.Format("select count(itemno) as totalitem 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 1=1 and inspection_type='1' and a.partnumber='{0}' and a.machineid='{1}' and c.FlagDel=0 ", partnumber, machineid);
                ds = fc.SQL_DataSet(query.ToString());

              
                fc.closeconnection();



            }
            return ds.GetXml();


        }

        [System.Web.Services.WebMethod]
        public static string sendrequest(string partnumber)
        {
            string cmd = string.Empty; ;
            using (CheckSheet.FcSql.Function fc = new CheckSheet.FcSql.Function())
            {
                string str_sendrequest = string.Format("update InspectionInput SET insp_status='W' Where inspectiontype='1'  and inspectiondate between '{0} 00:00:00' and '{0} 23:59:59' ", DateTime.Now.ToString("yyyy-MM-dd", new CultureInfo("en-US")));
                cmd = fc.ExecSQLStatement(str_sendrequest);
                
                fc.closeconnection();



                try
                {
                    string userapprove = clsControlUI.getuserapprove("1", "1", partnumber);
                    clsControlUI.sendemail("1", "Check sheet document waiting for approval.", userapprove, "1");
                }
                catch { };

            }
            return cmd;


        }


        [System.Web.Services.WebMethod]
        public static string getItem(string currentpage, string filterstring,string partnumber,string machineid)
        {

            int pagesize = 10;

            String val_dayoff = string.Empty;


            DataSet ds = new DataSet();
            using (CheckSheet.FcSql.Function fc = new CheckSheet.FcSql.Function())
            {
                for (int i = 0; i <= 10; i++)
                {
                    string str_chkdayoff = string.Format("select dayoff from (select dayoff from dayoff  where flagdel=0 union " +
                      " select * from dbo.listweekend_of_year('{0}') union " +
                      " select dayoff from CalendarPlan Where FlagDel=0 and check_type='1') as temp Where dayoff = '{1}'", DateTime.Now.ToString("yyyy0101", new CultureInfo("en-US")), DateTime.Now.AddDays(i).ToString("yyyy-MM-dd", new CultureInfo("en-US")));
                    DataTable dtdayoff = fc.SQL_DataTable(str_chkdayoff);
                    if (dtdayoff.Rows.Count > 0)
                    {
                        if (val_dayoff.Length == 0)
                        {
                            val_dayoff = "'" + DateTime.Parse(dtdayoff.Rows[0]["dayoff"].ToString()).ToString("yyyy-MM-dd", new CultureInfo("en-US")) + "'";

                        }
                        else
                        {
                            val_dayoff = val_dayoff + ",'" + DateTime.Parse(dtdayoff.Rows[0]["dayoff"].ToString()).ToString("yyyy-MM-dd", new CultureInfo("en-US")) + "'";

                        }


                    }
                    else if (i > 0 && dtdayoff.Rows.Count == 0)
                    {
                        break;

                    }

                }
                //if (dtdayoff.Rows.Count == 0)
                //{

                //    status_dayoff = "N";

                //}



                string query = string.Empty;
                if(machineid.Length>0)
                {

                    query = string.Format("select * from (Select  ROW_NUMBER() OVER (order by convert(real,inspectid))  as RowNum,  itemno, itemname,input_type,a.equid,inspectid,'' as item_color 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 1=1 and inspection_type='1' and a.partnumber='{3}' and a.machineid='{4}' and c.FlagDel=0 {0}  ) as temp WHERE RowNum BETWEEN ({1} - 1) * {2} + 1 AND {1} * {2}     ", filterstring, currentpage, pagesize, partnumber, machineid);

                }
                else
                {
                    query = string.Format("select * from (Select  ROW_NUMBER() OVER (order by convert(real,inspectid))  as RowNum,  itemno,item_main_name+'  '+itemname as itemname,input_type,a.equid,inspectid,'' as item_color from EquipmentList as a  inner join InspectionItem as c on a.equid=c.equid Where 1=1 and inspection_type='1' and report_type='7' and a.partnumber='{3}'  and c.FlagDel=0 {0}  ) as temp WHERE RowNum BETWEEN ({1} - 1) * {2} + 1 AND {1} * {2}     ", filterstring, currentpage, pagesize, partnumber, machineid);


                }
                ds = fc.SQL_DataSet(query.ToString());
                foreach (DataRow dr in ds.Tables[0].Rows)
                {

                    string str_chkresult = string.Format("select itemno,itemname,resultstatus,resultvalue from InspectionInput Where inspectiondate between '{0} 08:00:00' and '{1} 07:59:59' and  inspectid='{2}' and inspectiontype='1' and equid='{3}' ", DateTime.Now.ToString("yyyy-MM-dd", new CultureInfo("en-US")), DateTime.Now.AddDays(1).ToString("yyyy-MM-dd", new CultureInfo("en-US")), dr["inspectid"].ToString(), dr["equid"].ToString());
                    DataTable dt_chkresult = fc.SQL_DataTable(str_chkresult);
                    if (dt_chkresult.Rows.Count > 0)
                    {
                        if (dt_chkresult.Rows[0]["resultstatus"].ToString().ToLower() == "ok" || dt_chkresult.Rows[0]["resultvalue"].ToString().ToLower() != "")
                        {
                            dr["item_color"] = "#77A9F9";

                        }
                        else if (dt_chkresult.Rows[0]["resultstatus"].ToString().ToLower() == "ng")
                        {
                            dr["item_color"] = "#F8183D";

                        }

                    }
                    else
                    {
                        if (val_dayoff.IndexOf(DateTime.Now.ToString("yyyy-MM-dd", new CultureInfo("en-US"))) == -1)
                        {
                            string str_inspect0 = string.Format("select a.inspectid,a.equid,a.itemno,a.itemname from InspectionItem as a inner join mst_Frequency as b on a.freqid=b.freqid   Where a.flagdel=0 and  b.freqtype='1' and a.inspectid='{0}' and a.equid='{1}' and a.inspection_type='1' ", dr["inspectid"].ToString(), dr["equid"].ToString());
                            DataTable dt_inspect0 = fc.SQL_DataTable(str_inspect0);
                            if (dt_inspect0.Rows.Count > 0)
                            {
                                dr["item_color"] = "#E3BC08";

                            }


                        }
                        else
                        {
                            string str_inspect1 = string.Format("select a.inspectid,a.equid,a.itemno,a.itemname,b.*,c.listday from InspectionItem as a inner join mst_Frequency as b on a.freqid=b.freqid inner join dbo.listdate_of_year('{0}') as c on b.freqdate=c.listdayname   Where  a.flagdel=0 and a.inspection_type='1' and a.inspectid='{2}' and a.equid='{3}' and b.freqtype='2' and c.listday>=getdate() and c.listday in ({1}) " +
                            " union " +
                            " select a.inspectid,a.equid,a.itemno,a.itemname,b.*,c.listday from InspectionItem as a inner join mst_Frequency as b on a.freqid=b.freqid inner join dbo.listdate_of_year('{0}') as c on convert(int,b.freqvalue) = day(c.listday)   Where a.flagdel=0 and  a.inspection_type='1' and   a.inspectid='{2}' and a.equid='{3}' and b.freqtype='3' and c.listday>=getdate() and c.listday in ({1}) ", DateTime.Now.ToString("yyyy0101", new CultureInfo("en-US")), val_dayoff, dr["inspectid"].ToString(), dr["equid"].ToString());
                            DataTable dt_inspect1 = fc.SQL_DataTable(str_inspect1);
                            if (dt_inspect1.Rows.Count > 0)
                            {
                                dr["item_color"] = "#E3BC08";

                            }


                        }
                    }




                    




                }
                fc.closeconnection();
            }
            return ds.GetXml();


        }


        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnback_Click(object sender, EventArgs e)
        {
            Response.Redirect("../CheckItemSelect?para="+Request["para"].ToString());
        }
    }
}