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_jig_CSV : 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;
            string equid = string.Empty;

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

                string jigid = string.Empty;
                try
                {

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

                    jigid = _id;

                }
                catch { };



                foreach (DataRow dr in dt.Rows)
                {

                    try{



                        string lamp = dr[0].ToString();
                        string linename = dr[1].ToString();
                        if (linename.Length > 0)
                        {
                            string model = dr[2].ToString();
                            string toolname = dr[3].ToString();
                            string serial = dr[4].ToString();
                            string expiredate = dr[5].ToString();
                            string userid = Request.Cookies["userid"].Value;
                            string cls = string.Format("delete from JigMaster Where  toolname='{0}' and serial='{1}' ", toolname, serial);
                            string cmd_cls = fc.ExecSQLStatement(cls);

                            string lineid = string.Empty;
                            string partnumber = string.Empty;
                            string chkid = string.Format("select linename,partnumber,linegroupid from LineGroupMaster Where linename='{0}' and model='{1}' and lamp='{2}'", linename, model, lamp);
                            DataTable dtchkid = fc.SQL_DataTable(chkid.ToString());
                            if (dtchkid.Rows.Count > 0)
                            {
                               
                                lineid = dtchkid.Rows[0]["linegroupid"].ToString();
                                partnumber = dtchkid.Rows[0]["partnumber"].ToString();

                            }




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

                            string inst_mt = string.Format(" insert into JigMaster (jigid, toolname, serial, expire_date, flagdel, create_user, create_datetime,lamp,linename,model,partnumber) VALUES ('" + jigid + "','" + toolname + "','" + serial + "','" + expiredate + "',0,'" + userid + "','" + crdate + "','"+lamp+"','"+linename+"','"+model+"','"+partnumber+"')");
                            string up_mt = string.Format(" update JigMaster SET toolname='" + toolname + "', serial='" + serial + "', expire_date='" + expiredate + "',update_user='" + userid + "',update_datetime='" + crdate + "',lamp='"+lamp+"',linename='"+linename+"',model='"+model+"',partnumber='"+partnumber+"'  Where jigid= '" + jigid + "' ");

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

                            chkid = string.Format("select equid, jigid,linegroupid,partnumber from EquipmentList Where  linegroupid='{0}' and partnumber='{1}' ", lineid,partnumber);
                            dtchkid = fc.SQL_DataTable(chkid.ToString());
                            if (dtchkid.Rows.Count > 0)
                            {
                                equid = dtchkid.Rows[0]["equid"].ToString();

                            }









                            up_mt = string.Format(" update EquipmentList SET jigid='" + jigid + "'  Where equid= '" + equid + "' ");

                            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 = "machine_" + 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);
        }
    }
}

