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
{
    public partial class Upload_operator_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 approveid = string.Empty;

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


                    try
                    {

                        string empid = string.Empty;
                        string lamp = dr[0].ToString();
                        string linename = dr[1].ToString();
                        
                        string model = dr[2].ToString();
                        string usercode = dr[3].ToString();
                        string username = dr[4].ToString();
                        string password = dr[5].ToString();
                        string firstname = dr[6].ToString();
                        string lastname = dr[7].ToString();
                        string position = dr[8].ToString();
                        string emailaddress = dr[9].ToString();
                        string position_approve = dr[10].ToString();
                        string position_approve_id = string.Empty;
                        string lineid = string.Empty;
                        string partnumber = string.Empty;
                        string approve_seq = "1";


                        if (linename.Length > 0)
                        {
                            approveid = "";

                            if (position_approve == "Inspector")
                            {
                                position_approve_id = "0";
                            }
                            else if (position_approve == "Checker")
                            {
                                position_approve_id = "1";

                            }
                            else if (position_approve == "Approvol")
                            {
                                position_approve_id = "2";
                            }



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

                            string inst_mt = string.Empty;
                            string up_mt = string.Empty;


                            string qchkemp = string.Format("select usercode,userid from Users Where usercode='{0}'", usercode);
                            DataTable dtemp = fc.SQL_DataTable(qchkemp.ToString());
                            if (dtemp.Rows.Count == 0)
                            {


                                inst_mt = string.Format(" insert into Users ( usercode, username, password, firstname, lastname, position, emailaddress, flagdel, create_user, create_datetime) VALUES ('" + usercode + "','" + username + "','" + password + "','" + firstname + "','" + lastname + "','" + position + "','" + emailaddress + "',0,'" + userid + "','" + crdate + "')");
                                cmdhd = fc.ExecSQLStatement(inst_mt.ToString());


                            }
                            else
                            {

                                empid = dtemp.Rows[0]["userid"].ToString();
                                up_mt = string.Format(" update Users SET usercode='" + usercode + "', username='" + username + "', password='" + password + "', firstname='" + firstname + "', lastname='" + lastname + "', position='" + position + "', emailaddress='" + emailaddress + "',update_user='" + userid + "',update_datetime='" + crdate + "' Where userid= " + empid);
                                cmdhd = fc.ExecSQLStatement(up_mt.ToString());

                            }




                           
                          

                            qchkemp = string.Format("select usercode,userid from Users Where usercode='{0}'", usercode);
                            dtemp = fc.SQL_DataTable(qchkemp.ToString());
                            if (dtemp.Rows.Count > 0)
                            {


                                empid = dtemp.Rows[0]["userid"].ToString();


                            }


                            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)
                            {
                                //approveid = dtchkid.Rows[0]["approve_id"].ToString();
                                lineid = dtchkid.Rows[0]["linegroupid"].ToString();
                                partnumber = dtchkid.Rows[0]["partnumber"].ToString();

                            }


                            string appseq = string.Format("select max(approve_seq) as seq from mst_Approve  Where linename='{0}' and partnumber='{1}' and approve_positionid='{2}'", linename, partnumber, position_approve_id);
                            DataTable dtappseq = fc.SQL_DataTable(appseq);
                            if (dtappseq.Rows.Count > 0)
                            {
                                approve_seq = dtappseq.Rows[0]["seq"].ToString();
                                if (approve_seq.Length == 0)
                                {
                                    approve_seq = "1";

                                }

                            }


                            chkid = string.Format("select approve_id,linename,partnumber,linegroupid from mst_Approve Where linename='{0}' and partnumber='{1}' and approve_userid='{2}' ", linename, partnumber,empid);
                            dtchkid = fc.SQL_DataTable(chkid.ToString());
                            if (dtchkid.Rows.Count > 0)
                            {
                                approveid = dtchkid.Rows[0]["approve_id"].ToString();
                               

                            }


                            if (approveid.Length == 0)
                            {

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

                                approveid = _id;
                                inst_mt = string.Format(" insert into mst_Approve (approve_id, approve_userid, approve_username, approve_positionid, approve_position, approve_level, approve_email, approve_seq, inspectiontype, flagdel, create_user, create_datetime, linegroupid, linename, partnumber) VALUES ('" + approveid + "','" + empid + "','" + firstname + "','" + position_approve_id + "','" + position_approve + "'," + position_approve_id + ",'" + emailaddress + "'," + approve_seq + ",'1',0,'" + userid + "','" + crdate + "','" + lineid + "','" + linename + "','" + partnumber + "')");


                            }
                            else
                            {
                                string del = string.Format("delete from mst_Approve  Where approve_id='{0}'", approveid);
                                cmdhd = fc.ExecSQLStatement(del.ToString());
                                inst_mt = string.Format(" insert into mst_Approve (approve_id, approve_userid, approve_username, approve_positionid, approve_position, approve_level, approve_email, approve_seq, inspectiontype, flagdel, create_user, create_datetime, linegroupid, linename, partnumber) VALUES ('" + approveid + "','" + empid + "','" + firstname + "','" + position_approve_id + "','" + position_approve + "'," + position_approve_id + ",'" + emailaddress + "'," + approve_seq + ",'1',0,'" + userid + "','" + crdate + "','" + lineid + "','" + linename + "','" + partnumber + "')");


                            }





                            cmdhd = fc.ExecSQLStatement(inst_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);
        }
    }
}

