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_machine_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())
            {
            foreach (DataRow dr in dt.Rows)
            {

              
                    try
                    {
                    
                        
                    string lamp = dr[0].ToString();
                    if (lamp.Length > 0)
                    {
                        string linename = dr[1].ToString();
                        string model = dr[2].ToString();
                        string machinename = dr[3].ToString();
                        machinename = machinename.Replace("&amp;", "&");
                        string userid = Request.Cookies["userid"].Value;
                        string piz = "";




                        string linegroup = string.Empty;
                        string partnumber = string.Empty;
                        string linegroupcode = string.Empty;

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


                        }


                        string _idmachine = string.Empty;
                        string _qmachine = string.Format("select max(num) as numid,linegroupcode from (select Row_Number() over (Order by machineid) as num,linegroupcode from MachineMaster Where linegroupcode in (select linegroupcode from LineGroupMaster Where model='{0}' and lamp='{1}')) as temp group by linegroupcode ", model, lamp);
                        DataTable dtgenidmachine = fc.SQL_DataTable(_qmachine.ToString());
                        if (dtgenidmachine.Rows.Count == 0)
                        {
                            _idmachine = "1";
                        }
                        else
                        {
                            foreach (DataRow drmachine in dtgenidmachine.Rows)
                            {
                                if (drmachine[0].ToString() == "")
                                {
                                    _idmachine = "1";
                                }
                                else
                                {
                                    _idmachine = (int.Parse(drmachine[0].ToString()) + 1).ToString();
                                }
                            }
                        }

                        if (dtgenidmachine.Rows.Count > 0)
                        {
                            linegroupcode = dtgenidmachine.Rows[0]["linegroupcode"].ToString();

                        }
                        string machineno = linegroupcode + "" + _idmachine;


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

                        string inst_mt = string.Format(" insert into MachineMaster (linegroupcode,machineid, machinename, flagdel, create_user, create_datetime,machine_piz) VALUES ('" + linegroupcode + "','" + machineno + "','" + machinename + "',0,'" + userid + "','" + crdate + "','" + piz + "')");
                        string up_mt = string.Format(" update MachineMaster SET machinename= '" + machinename + "',update_user='" + userid + "', update_datetime= '" + crdate + "',machine_piz='" + piz + "',linegroupcode='" + linegroupcode + "' Where machineid= '" + machineno + "' ");

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


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

                        }




                        if (equid.Length == 0)
                        {

                            string _pv = string.Empty;
                            string _id = string.Empty;
                            string _q = string.Format(" select max(convert(real,equid)) as id from EquipmentList ");
                            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();
                                    }
                                }
                            }

                            equid = _id;


                        }




                        inst_mt = string.Format(" insert into EquipmentList (equid, partnumber, linegroupid, machineid, flagdel, create_user, create_datetime) VALUES ('" + equid + "','" + partnumber + "','" + linegroup + "','" + machineno + "',0,'" + userid + "','" + crdate + "')");
                        up_mt = string.Format(" update EquipmentList SET partnumber='" + partnumber + "', linegroupid= '" + linegroup + "', machineid= '" + machineno + "',Update_user= '" + userid + "',Update_Datetime= '" + crdate + "' 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);
        }
    }
}

