using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Data;
using System.Globalization;
using System.IO;


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




        [System.Web.Services.WebMethod]
        public static string calldata(string ctl,string colid,string colname, string tbl,string currentpage,string pagesize,string filter)
        {

            FcSql.Function fc = new FcSql.Function();
            string query = string.Empty;
            string colparent = string.Empty;
            string filtermain = string.Empty;

            if (filter.IndexOf('*') != -1)
            {
                string[] arr = filter.Split('*');
                filter = arr[0];
                filtermain = arr[1];

            }


            if (filter.IndexOf("$and") != -1)
            {
                filtermain = filter;
                filter = "";
                

            }


            if (filter.IndexOf("<>") != -1)
            {




            }
            else if (filter.IndexOf(">") != -1)
            {

                filter = filter.Replace("'", "");


            }
            else if (filter.IndexOf("<") != -1)
            {

                filter = filter.Replace("'", "");


            }

            if(filter.Length>0)
            {
                
                filter = string.Format(" and ({0} like '%{2}%' or {1} like '%{2}%')", colid, colname, filter);

            }

            if(filtermain.Length>0)
            {
                filter = filter + filtermain.Replace("$", "");


            }
            DataSet ds = new DataSet();
            if (tbl == "InspectionItem")
            {
                try
                {
                    query = string.Format(" Select * from (Select *,ROW_NUMBER() OVER (order by ValueID)  as RowNum from (Select distinct {2} as ValueID,  {5} as ValueName,'' as resultstatus from {3} Where flagdel=0 {4}) as subtemp) as temp WHERE RowNum BETWEEN ({0} - 1) * {1} + 1 AND {0} * {1}   ", currentpage, pagesize, colid, tbl, filter, colname);
                    ds = fc.SQL_DataSet(query.ToString());
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        string str_status = string.Format("select result1 from InspectionInput Where itemno='{0}' and inspectiontype='4' and inspectiondate between '{1} 00:00:00' and '{1} 23:59:59' ", dr["ValueID"].ToString(), DateTime.Now.ToString("yyyy-MM-dd", new CultureInfo("en-US")));
                        DataTable dt = fc.SQL_DataTable(str_status);
                        if (dt.Rows.Count > 0)
                        {
                            dr["resultstatus"] = dt.Rows[0]["result1"].ToString();

                        }


                    }
                    if(ds.Tables[0].Rows.Count==0)
                    {
                        query = string.Format(" Select * from (Select *,ROW_NUMBER() OVER (order by ValueID)  as RowNum from (Select distinct {2} as ValueID,  {5} as ValueName from {3} Where flagdel=0 {4}) as subtemp) as temp WHERE RowNum BETWEEN ({0} - 1) * {1} + 1 AND {0} * {1}   ", currentpage, pagesize, colid, tbl, filter, colname);
                        ds = fc.SQL_DataSet(query.ToString());

                    }
                }
                catch                
                {

                    query = string.Format(" Select * from (Select *,ROW_NUMBER() OVER (order by convert(decimal(18,2),ValueID))  as RowNum from (Select distinct {2} as ValueID,  {5} as ValueName,'' as resultstatus from {3} Where flagdel=0) as subtemp) as temp WHERE RowNum BETWEEN ({0} - 1) * {1} + 1 AND {0} * {1}   ", currentpage, pagesize, colid, tbl, filter, colname);
                    ds = fc.SQL_DataSet(query.ToString());
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        string str_status = string.Format("select result1 from InspectionInput Where itemno='{0}' and inspectiontype='4' and inspectiondate between '{1} 00:00:00' and '{1} 23:59:59' ", dr["ValueID"].ToString(), DateTime.Now.ToString("yyyy-MM-dd", new CultureInfo("en-US")));
                        DataTable dt = fc.SQL_DataTable(str_status);
                        if (dt.Rows.Count > 0)
                        {
                            dr["resultstatus"] = dt.Rows[0]["result1"].ToString();

                        }


                    }
                
                
                
                };

            }
            else if (tbl == "view_UserControl")
            {

                query = string.Format(" Select * from (Select *,ROW_NUMBER() OVER (order by ValueName)  as RowNum from (Select distinct {2} as ValueID,  {5} as ValueName from {3} Where flagdel=0 {4}) as subtemp) as temp WHERE RowNum BETWEEN ({0} - 1) * {1} + 1 AND {0} * {1}   ", currentpage, pagesize, colid, tbl, filter, colname);
                ds = fc.SQL_DataSet(query.ToString());


            }
            else
            {
                query = string.Format(" Select * from (Select *,ROW_NUMBER() OVER (order by ValueID)  as RowNum from (Select distinct {2} as ValueID,  {5} as ValueName from {3} Where flagdel=0 {4}) as subtemp) as temp WHERE RowNum BETWEEN ({0} - 1) * {1} + 1 AND {0} * {1}   ", currentpage, pagesize, colid, tbl, filter, colname);
                ds = fc.SQL_DataSet(query.ToString());

            }

         


            return ds.GetXml();







        }


        protected void Page_Load(object sender, EventArgs e)
        {

        }
    }
}