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

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

        }

        protected string UploadFolderPath = "~/Uploads/";

        private void _fSort(string _file)
        {


            ArrayList arr = new ArrayList();
            ArrayList arrcpn = new ArrayList();
            string sError = string.Empty;

            string FullPath = _file;
            string MyPath = System.IO.Path.GetDirectoryName(FullPath);
            string myFilename = System.IO.Path.GetFileName(FullPath);


            string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
              "Data Source=" + FullPath +
              ";Extended Properties='Excel 8.0;'";
            string CmdText = "select * from [Sheet1$]";





            OleDbConnection Con = new OleDbConnection(ConnStr);
            OleDbDataAdapter adptr = new OleDbDataAdapter(CmdText, Con);
            DataTable dt = new DataTable();
            adptr.Fill(dt);
            ArrayList ardate = new ArrayList();
            string _date = string.Empty;
            string cmdhd = string.Empty;

            using (FcSql.Function fc = new FcSql.Function())
            {
                foreach (DataRow dr in dt.Rows)
                {


                    try
                    {

                        string linegroupid = string.Empty;
                        string id = dr[0].ToString();
                        string equid = dr[1].ToString();
                        string itemno = dr[2].ToString();
                        if (itemno.Length > 0)
                        {
                            string itemname = dr[3].ToString();
                            string whoid = dr[4].ToString();
                            string whoname = dr[5].ToString();
                            string freqid = dr[6].ToString();
                            string freqname = dr[7].ToString();
                            string howcheck = dr[8].ToString();
                            string resulttext = dr[9].ToString();
                            string min = dr[10].ToString();
                            string max = dr[11].ToString();
                            string unitid = dr[12].ToString();
                            string unitname = dr[13].ToString();
                            //string flagdel ="0";
                            string userid = Request.Cookies["userid"].Value;
                            string crdate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", new CultureInfo("en-US"));
                            string inputmethod = dr[19].ToString();
                            string inputx = dr[20].ToString();
                            string inputtype = dr[21].ToString();
                            string inspectiontype = dr[22].ToString();
                            string report_type = dr[23].ToString();


                            string inspectid = string.Empty;


                            string chkid = string.Format("select inspectid from InspectionItem Where inspectid='{0}' ", id);
                            DataTable dtchkid = fc.SQL_DataTable(chkid.ToString());
                            if (dtchkid.Rows.Count > 0)
                            {
                                inspectid = dtchkid.Rows[0]["inspectid"].ToString();

                            }
                            else
                            {





                                inspectid = clsControlUI.create_master_id("InspectionItem", "inspectid");


                            }



                            string inst_mt = string.Format(" insert into InspectionItem (inspectid, equid, itemno, itemname, whoid, whochecklist, freqid, frequency, howcheck, criteriatext, criteriamin, criteriamax, unitid, unit, flagdel, create_user, create_datetime, input_method, input_x, input_type, inspection_type, report_type) VALUES " +
                                " ('" + inspectid + "','" + equid + "','" + itemno + "','" + itemname + "','" + whoid + "','" + whoname + "','" + freqid + "','" + freqname + "','" + howcheck + "','" + resulttext + "'," + min + "," + max + ",'" + unitid + "','" + unitname + "',0,'" + userid + "','" + crdate + "','" + inputmethod + "','" + inputx + "','" + inputtype + "','" + inspectiontype + "','" + report_type + "')");
                            string up_mt = string.Format(" update InspectionItem SET equid='" + equid + "', itemno='" + itemno + "', itemname='" + itemname + "', whoid='" + whoid + "', whochecklist='" + whoname + "', freqid='" + freqid + "', frequency='" + freqname + "', howcheck='" + howcheck + "', criteriatext='" + resulttext + "', criteriamin=" + min + ", criteriamax=" + max + ", unitid='" + unitid + "', unit='" + unitname + "', update_user='" + userid + "', update_datetime='" + crdate + "', input_method='" + inputmethod + "', input_x='" + inputx + "', input_type='" + inputtype + "', inspection_type='" + inspectiontype + "', report_type='" + report_type + "' Where inspectid= '" + inspectid + "' ");

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

                            fc.closeconnection();

                        }
                    }
                    catch { };

                }



            }


        }

        protected void AsyncFileUpload1_UploadComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
        {
            string filename = "inspect_" + DateTime.Now.ToString("yyyyMMddHHmmss", new CultureInfo("en-US"));
            AsyncFileUpload2.SaveAs(Server.MapPath(this.UploadFolderPath) + filename);

            string path = this.Server.MapPath(@"Uploads").Replace("\\setting", "");

            _fSort(path + "\\" + filename);
        }

    }
}