using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Resources;
using System.Threading;
using System.Web.UI;
using System.Web.UI.WebControls;
/*
 * Modified By      :   Manjula.P
 * Modified On      :   07-OCt-2013
 * Descriptions     :   Adding alert for import file when try to import yes/no entry in eligible columns in both CSV and XLS Files and its functions
 * Modified By      :   Manjula.P
 * Modified On      :   09-Oct-2013
 * Descriptions     :   Added by Manjula.P on 09-Oct-2013 for getting another datetime values while inserting Mantis : 0019383
 * */
public partial class System_Settings_ImportEmployee : Base.BasePage
{
    #region Variable Declaration.
    ImportData ObjImportData = new ImportData();
    ResourceManager rm;
    CultureInfo ci;
    string strDateOfBirth, gstrDateFormat = "dd/MM/yyyy", strDateOfJoining, Afterbirth_Record = "", isEnterprise = "0", strScript = "";
    cls_Common objCommon = new cls_Common();
    char chrImportDelimiter = ',';
    cls_master_levels class1 = new cls_master_levels();
    FileStream aFile;
    #endregion
    #region Page Load.
    protected override void Page_Load(object sender, EventArgs e)
    {
        base.Page_Load(sender, e);
        ObjImportData = new ImportData();
        gvImportMaster.AllowPaging = false;
        string source_Language = string.Empty;
        source_Language = Convert.ToString(Session["Session_ActiveLanguage"]);
        //grv_ImportMaster.Styles.CssFilePath = Convert.ToString(Session["GridCSS"]);
        rm = new ResourceManager(source_Language, System.Reflection.Assembly.Load("App_GlobalResources"));
        ci = Thread.CurrentThread.CurrentCulture;
        site.SiteMapProvider = Convert.ToString(Session["SiteMapProvider"]);
        LoadStrings(ci);
        if (Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["ImportDelimiter"]) != "")
        {
            chrImportDelimiter = Convert.ToChar(System.Configuration.ConfigurationManager.AppSettings["ImportDelimiter"]);
        }
        if (!IsPostBack)
        {
            objCommon.menu("EmployeeMaster", "104");
            loadGridData();
        }

    }
    private void UserRights()
    {
        Cls_CheckUser objCheckUser = new Cls_CheckUser();
        DataSet ds_Checkuser = new DataSet();
        ds_Checkuser = objCheckUser.checkRights(104, Convert.ToInt32(Session["userGroup_Id"]), "addPrv");
        if (ds_Checkuser.Tables[0].Rows.Count == 0)
        {
            Response.Redirect("SystemLandingPage.aspx");
        }
    }
    private void loadGridData()
    {
        DataSet ds = new DataSet();
        if (ViewState["ImportMasterData"] != null)
            ds = (DataSet)ViewState["ImportMasterData"];
        else
            ds = ObjImportData.SelectImportErrorData();
        if (ds.Tables.Count > 0)
            if (ds.Tables[0].Rows.Count > 0)
            {
                btnDelete.Visible = true;
                ViewState["ImportMasterData"] = ds;
                gvImportMaster.DataSource = ds;
                gvImportMaster.DataBind();
            }
            else
            {
                btnDelete.Visible = false;
            }
        else
        {
            btnDelete.Visible = false;
        }
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
    }
    #endregion
    #region Button Events
    /// <summary>
    /// Handles the Click event of the btn_Upload control.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btn_Upload_Click(object sender, EventArgs e)
    {
        try
        {
            if (fp_ImportFilePath.HasFile)
            {
                ObjImportData.deleteImportMasterData();
                string filename = fp_ImportFilePath.FileName;
                filename = filename.Substring(filename.LastIndexOf("\\") + 1);
                string fileType;
                fileType = System.IO.Path.GetExtension(filename);
                if (fileType == ".xls" || fileType == ".csv")
                {
                    string strFilePath = Server.MapPath("Uploads").ToString();
                    FileInfo fileInfo = new System.IO.FileInfo(strFilePath + "\\" + fp_ImportFilePath.FileName);
                    fp_ImportFilePath.SaveAs(strFilePath + "\\" + fp_ImportFilePath.FileName);
                    if (fp_ImportFilePath.PostedFile.ContentLength == fileInfo.Length)
                    {
                        int val = 0;
                        if (fileType == ".csv")
                        {
                            val = ImportMasterDataForCSV(fileInfo);
                        }
                        else
                        {
                            val = ImportMasterDataForXls(fileInfo);
                        }
                        if (val == 1)
                        {
                            Message(rm.GetString("Alert_FileUploadedandDataareimportedSucessfully", ci).Replace("\r", "").Replace("\n", ""));
                            if (aFile != null) aFile.Dispose();
                        }
                        else
                        {
                            Message(rm.GetString("Alert_ErrorinImportingEmployee", ci).Replace("\r", "").Replace("\n", ""));
                            if (aFile != null) aFile.Dispose();
                        }
                    }
                    else
                    {
                        Message(rm.GetString("Alert_ErrorinImportingEmployee", ci).Replace("\r", "").Replace("\n", ""));
                        if (aFile != null) aFile.Dispose();
                    }
                }
                else
                {
                    Message(rm.GetString("Alert_ErrorinImportingEmployee", ci).Replace("\r", "").Replace("\n", ""));
                    if (aFile != null) aFile.Dispose();
                }
            }
            else
                Message(rm.GetString("Alert_Youhavenotspecifiedafile", ci).Replace("\r", "").Replace("\n", ""));
        }
        catch (Exception ex)
        {
            string strErr = Convert.ToString(ex.Message);
            Message(strErr.Replace("\r", "").Replace("\n", ""));
        }
        finally
        {
            try
            {
                if (fp_ImportFilePath.HasFile)
                {
                    string StrFilePath = Convert.ToString(Server.MapPath("Uploads")) + "\\" + fp_ImportFilePath.FileName;
                    System.IO.File.Delete(StrFilePath);
                }
            }
            catch (Exception Ex)
            {
            }
            if (aFile != null)
                aFile.Dispose();
        }
    }
    protected void btnTemplate_Click(object sender, EventArgs e)
    {
        try
        {
            DataSet dsLevels = new DataSet();
            levelsEventData objClass = new levelsEventData();
            dsLevels = objClass.selectLevels();
            DataSet ds = new DataSet();
            string strFilePath = string.Empty;
            Afterbirth_Record = "DateOfBirth_Record";
            switch (rblType.SelectedValue)
            {
                case "1":
                    if (Convert.ToString(Session["Demo"]) == "Enterprise")
                    {
                        strFilePath = Server.MapPath("Uploads/TimecheckMasterDataTemplateEnterPrise.xls");
                        isEnterprise = "1";
                    }
                    else
                    {
                        strFilePath = Server.MapPath("Uploads/TimecheckMasterDataTemplateStandard.xls");
                        isEnterprise = "0";
                    }
                    break;
                default:
                    strFilePath = Server.MapPath("Uploads/TimecheckMasterDataTemplateSimple.xls");
                    if (Convert.ToString(Session["Demo"]) == "Enterprise")
                        isEnterprise = "1";
                    else
                        isEnterprise = "0";
                    break;
            }
            FileInfo fileInfo = new System.IO.FileInfo(strFilePath);
            if (!File.Exists(strFilePath))
            {
                base.Message("Template file is not available to download. Please contact stake holders.");
                return;
            }
            ds = ObjImportData.ReadExcelSheet(fileInfo);
            if (dsLevels.Tables.Count > 0 && dsLevels.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables.Count > 0)
                {
                    for (int i = 0; i < dsLevels.Tables[0].Rows.Count; i++)
                    {
                        if (i == 0)
                            ds.Tables[0].Columns.Add("*" + Convert.ToString(dsLevels.Tables[0].Rows[i]["levelName"]));
                        else
                            ds.Tables[0].Columns.Add(Convert.ToString(dsLevels.Tables[0].Rows[i]["levelName"]));
                    }
                }
            }
            gstrDateFormat = "dd-MMM-yyyy";
            Random rnmNumber = new Random();
            int Number = rnmNumber.Next(1, 10000);

            if (rblType.SelectedValue == "1")
            {
                if (ds.Tables[0].Rows.Count == 0)
                {
                    DataRow row = ds.Tables[0].NewRow();
                    row["Sl#No"] = "1";
                    row["*EmployeeFirstName"] = "Uma";
                    row["EmployeeMiddleName"] = "Maheswari";
                    row["EmployeeLastName"] = "Arivazhagan";
                    row["FatherName"] = "Arivazhagan";
                    row["MotherName"] = "Priya";
                    row["DateOfBirth"] = DateTime.Parse("1990/06/02").ToString(gstrDateFormat);
                    row["BloodGroup"] = "B-ve";
                    row["OfficialMailID"] = "uma@gmail.com";
                    row["ContactAddress"] = "Vadapazhani-Chennai";
                    row["PermanentAddress"] = "Vadapazhani-Chennai";
                    row["PhoneNumber"] = "9175555555";
                    row["MobileNumber"] = "9677734116";
                    row["*Gender"] = "Female";
                    row["MaritalStatus"] = "Single";
                    row["Nationality"] = "Indian";
                    row["ReligionName"] = "Hindu";
                    row["PersonalMailID"] = "Uma@gmail.com";
                    row["PassportNo"] = "31195855";
                    row["PassportExpiryDate"] = DateTime.Parse("2020/09/01").ToString(gstrDateFormat);
                    row["VisaNo"] = "371449635398431";
                    row["VisaExpiryDate"] = DateTime.Parse("2020/09/01").ToString(gstrDateFormat);
                    row["*EmployeeID"] = Number;
                    row["*SwipeCardNo"] = Number;
                    row["*DateOfJoining"] = DateTime.Parse("2011/03/05").ToString(gstrDateFormat);
                    row["QualificationonJoiningTime"] = "BE";
                    row["QualificationDuringEmployement"] = "BE";
                    row["CompOffEligible"] = 0;
                    row["OTEligible"] = 0;
                    row["Hostel"] = 0;
                    row["*Designation"] = "Developer";
                    if (isEnterprise == "1")
                    {
                        row["*GradeName"] = "Grade 4";
                        row["*GradeNo"] = "4";
                    }
                    row["*EmployeeType"] = "Permanant";
                    row["*EmployeeCategory"] = "Programmer";
                    row["BasicSalary"] = "12000";
                    if (Session["UserGroupName"] != null)
                    {
                        dsLevels = class1.getOrgLevelUpdated(0, Convert.ToString(Session["LevelRights"]), 0, Convert.ToString(Session["UserGroupName"]));
                        DataTable dtFirstLevel = new DataTable();
                        dsLevels.Tables[0].DefaultView.RowFilter = "ParentId = 0";
                        dtFirstLevel = dsLevels.Tables[0].DefaultView.ToTable();
                        if (dtFirstLevel.Rows.Count > 0)
                        {
                            if (Convert.ToString(dtFirstLevel.Rows[0]["name"]) != string.Empty)
                                row[37] = Convert.ToString(dtFirstLevel.Rows[0]["name"]);
                            else
                                row[37] = "Default First Level Hierarchy";
                        }
                        else
                        {
                            if (isEnterprise == "1")
                                row[39] = "Default First Level Hierarchy";
                            else if (isEnterprise == "0")
                                row[37] = "Default First Level Hierarchy";
                        }
                    }
                    ds.Tables[0].Rows.Add(row);
                }
            }
            else
            {
                if (ds.Tables[0].Rows.Count == 0)
                {
                    DataRow row = ds.Tables[0].NewRow();
                    row["Sl#No"] = "1";
                    row["*Emp_FirstName"] = "Karthikeyan";
                    row["*Card_Number"] = "956";
                    row["*Employee_ID(LoginID)"] = "956";
                    row["*DateOfJoining"] = DateTime.Parse("2015/01/01").ToString(gstrDateFormat);
                    row["*Designation"] = "Developer";
                    if (Session["UserGroupName"] != null)
                    {
                        dsLevels = class1.getOrgLevelUpdated(0, Convert.ToString(Session["LevelRights"]), 0, Convert.ToString(Session["UserGroupName"]));
                        DataTable dtFirstLevel = new DataTable();
                        dsLevels.Tables[0].DefaultView.RowFilter = "ParentId = 0";
                        dtFirstLevel = dsLevels.Tables[0].DefaultView.ToTable();
                        if (dtFirstLevel.Rows.Count > 0)
                        {
                            if (Convert.ToString(dtFirstLevel.Rows[0]["name"]) != string.Empty)
                                row[6] = Convert.ToString(dtFirstLevel.Rows[0]["name"]);
                            else
                                row[6] = "Default First Level Hierarchy";
                        }
                        else
                            row[6] = "Default First Level Hierarchy";
                    }
                    ds.Tables[0].Rows.Add(row);
                }
            }
            string strData = Export(ds);
            byte[] data = System.Text.ASCIIEncoding.ASCII.GetBytes(strData);
            Response.Clear();
            Response.AddHeader("Content-Type", "application/Excel");
            Response.AddHeader("Content-Disposition", "attachment;filename=TimecheckMasterData.csv");
            Response.BinaryWrite(data);
            Response.End();
        }
        catch (Exception ex)
        {
            Message(rm.GetString("Alert_UnableToReadFile", ci).Replace("\r", "").Replace("\n", ""));   //Import data Error Message when Excel is not installed in client machine
        }
    }
    protected void btn_UploadGrid_Click(object sender, EventArgs e)
    {
        try
        {
            foreach (GridViewRow grv in gvImportMaster.Rows)
            {
                Label txt_ID = (Label)grv.FindControl("lbl_Id");
                TextBox txt_Emp_FirstName = (TextBox)grv.FindControl("txt_Emp_FirstName");
                TextBox txt_Emp_MiddleName = (TextBox)grv.FindControl("txt_Emp_MiddleName");
                TextBox txt_Emp_LastName = (TextBox)grv.FindControl("txt_Emp_LastName");
                TextBox txt_Father_Name = (TextBox)grv.FindControl("txt_Father_Name");
                TextBox txt_Mother_Name = (TextBox)grv.FindControl("txt_Mother_Name");
                TextBox txt_DateOfBirth = (TextBox)grv.FindControl("txt_DateOfBirth");
                TextBox txt_DateOfBirth_Record = (TextBox)grv.FindControl("txt_DateOfBirth_Record");
                TextBox txt_Blood_Group = (TextBox)grv.FindControl("txt_Blood_Group");
                TextBox txt_Email_Id = (TextBox)grv.FindControl("txt_Email_Id");
                TextBox txt_Contact_Address = (TextBox)grv.FindControl("txt_Contact_Address");
                TextBox txt_Permanent_Address = (TextBox)grv.FindControl("txt_Permanent_Address");
                TextBox txt_Phone_Number = (TextBox)grv.FindControl("txt_Phone_Number");
                TextBox txt_Mobile_Number = (TextBox)grv.FindControl("txt_Mobile_Number");
                TextBox txt_Gender = (TextBox)grv.FindControl("txt_Gender");
                TextBox txt_Marital_Status = (TextBox)grv.FindControl("txt_Marital_Status");
                TextBox txt_Nationality = (TextBox)grv.FindControl("txt_Nationality");
                TextBox txt_Religion = (TextBox)grv.FindControl("txt_Religion");
                TextBox txt_Personal_EmailID = (TextBox)grv.FindControl("txt_Personal_EmailID");
                TextBox txt_Passport_No = (TextBox)grv.FindControl("txt_Passport_No");
                TextBox txt_Passport_ExpiryDate = (TextBox)grv.FindControl("txt_Passport_ExpiryDate");
                TextBox txt_Visa_No = (TextBox)grv.FindControl("txt_Visa_No");
                TextBox txt_Visa_ExpiryDate = (TextBox)grv.FindControl("txt_Visa_ExpiryDate");
                TextBox txt_Employee_Code = (TextBox)grv.FindControl("txt_Employee_Code");
                TextBox txt_DateOfJoining = (TextBox)grv.FindControl("txt_DateOfJoining");
                TextBox txt_Qualification_joiningtime = (TextBox)grv.FindControl("txt_Qualification_joiningtime");
                TextBox txt_Qualification_Employement = (TextBox)grv.FindControl("txt_Qualification_Employement");
                TextBox txt_CompOff_Eligible = (TextBox)grv.FindControl("txt_CompOff_Eligible");
                TextBox txt_OT_Eligible = (TextBox)grv.FindControl("txt_OT_Eligible");
                TextBox txt_Report_Exclude = (TextBox)grv.FindControl("txt_Report_Exclude");
                TextBox txt_Employee_Status = (TextBox)grv.FindControl("txt_Employee_Status");
                TextBox txt_IsDeleted = (TextBox)grv.FindControl("txt_IsDeleted");
                TextBox txt_Hostel = (TextBox)grv.FindControl("txt_Hostel");
                TextBox txt_Designation = (TextBox)grv.FindControl("txt_Designation");
                TextBox txt_Grade = (TextBox)grv.FindControl("txt_Grade");
                TextBox txt_GradeNo = (TextBox)grv.FindControl("txt_GradeNo");
                TextBox txt_Employee_Type = (TextBox)grv.FindControl("txt_Employee_Type");
                TextBox txt_Employee_Category = (TextBox)grv.FindControl("txt_Employee_Category");
                TextBox txt_BasicSalary = (TextBox)grv.FindControl("txt_BasicSalary");
                TextBox txt_Level1 = (TextBox)grv.FindControl("txt_Level1");
                TextBox txt_Level2 = (TextBox)grv.FindControl("txt_Level2");
                TextBox txt_Level3 = (TextBox)grv.FindControl("txt_Level3");
                TextBox txt_Level4 = (TextBox)grv.FindControl("txt_Level4");
                TextBox txt_Level5 = (TextBox)grv.FindControl("txt_Level5");
                TextBox txt_Level6 = (TextBox)grv.FindControl("txt_Level6");
                TextBox txt_Level7 = (TextBox)grv.FindControl("txt_Level7");
                TextBox txt_Level8 = (TextBox)grv.FindControl("txt_Level8");
                TextBox txt_Level9 = (TextBox)grv.FindControl("txt_Level9");
                TextBox txt_Level10 = (TextBox)grv.FindControl("txt_Level10");
                string GradeName = string.Empty;
                string GradeCode = string.Empty;
                if (Convert.ToString(Session["Demo"]) == "Enterprise")
                {
                    if (txt_Grade != null)
                        GradeName = txt_Grade.Text;
                    if (txt_GradeNo != null)
                        GradeCode = txt_GradeNo.Text;
                }
                else
                {
                    GradeName = "0";
                    GradeCode = "0";
                }
                ObjImportData.EditImportErrorData(txt_ID.Text, txt_Emp_FirstName.Text, txt_Emp_MiddleName.Text, txt_Emp_LastName.Text, txt_Father_Name.Text, txt_Mother_Name.Text, txt_DateOfBirth.Text, txt_DateOfBirth_Record.Text, txt_Blood_Group.Text, txt_Email_Id.Text, txt_Contact_Address.Text, txt_Permanent_Address.Text, txt_Phone_Number.Text, txt_Mobile_Number.Text, txt_Gender.Text, txt_Marital_Status.Text, txt_Nationality.Text, txt_Religion.Text, txt_Personal_EmailID.Text, txt_Passport_No.Text, txt_Passport_ExpiryDate.Text, txt_Visa_No.Text, txt_Visa_ExpiryDate.Text, txt_Employee_Code.Text, txt_DateOfJoining.Text, txt_Qualification_joiningtime.Text, txt_Qualification_Employement.Text, txt_CompOff_Eligible.Text, txt_OT_Eligible.Text, txt_Report_Exclude.Text, txt_Employee_Status.Text, txt_IsDeleted.Text, txt_Hostel.Text, txt_Designation.Text, GradeName, GradeCode, txt_Employee_Type.Text, txt_Employee_Category.Text, txt_BasicSalary.Text, txt_Level1.Text, txt_Level2.Text, txt_Level3.Text, txt_Level4.Text, txt_Level5.Text, txt_Level6.Text, txt_Level7.Text, txt_Level8.Text, txt_Level9.Text, txt_Level10.Text);
            }
            DataSet ds = new DataSet();
            ds = ObjImportData.SelectImportErrorData();
            ds.Tables[0].TableName = "TheData";
            StringWriter strWriteXML = new StringWriter();
            ds.WriteXml(strWriteXML);
            string strDataXML = Convert.ToString(strWriteXML);
            //Session["Demo"]
            ObjImportData.InsertGridMasterData(strDataXML, Convert.ToString(Session["Demo"]), 0);
            strScript = "";
            strScript = strScript + "<script language='javascript'>";
            strScript = strScript + "alert('" + rm.GetString("Alert_FileUploadedandDataareimportedSucessfully", ci).Replace("\r", "").Replace("\n", "") + "');";
            strScript = strScript + "</script>";
            Page.RegisterStartupScript("Alert", strScript);
            if (aFile != null) aFile.Dispose();
            loadGridData();
        }
        catch (Exception ex)
        {
            Message(ex.ToString());
        }
    }

    #endregion
    #region User Defined Functions
    /// <summary>
    /// Imports the data.
    /// </summary>
    /// <param name="fileInfo">The file info.</param>
    private int ImportMasterDataForCSV(FileInfo fileInfo)
    {
        string strScript = string.Empty;
        string validationMsg = string.Empty;
        string strLine;
        string[] strArray;
        char[] charArray = new char[] { chrImportDelimiter };
        DataSet ds = new DataSet();
        DataTable dt = ds.Tables.Add("TheData");
        aFile = new FileStream(fileInfo.FullName.ToString(), FileMode.Open);
        StreamReader sr = new StreamReader(aFile);
        strLine = sr.ReadLine();
        strLine = strLine.Replace(";", ",");
        strArray = strLine.Split(charArray);
        int totalColumnCount = strArray.Length;
        if (rblType.SelectedValue == "1" && totalColumnCount > 37)
        {
            if (strArray[36].ToUpper() != " BASICSALARY ")
            {
                validationMsg = "Please Upload valid Excel file. Compare to default template some fields are missing in your excel file";
                Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                sr.Dispose();
                aFile.Dispose();
                return 0;
            }
        }

        if (totalColumnCount > 16)
        {
            if (rblType.SelectedValue == "0")
            {
                validationMsg = "Please select simple import type option";
                Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                sr.Dispose();
                aFile.Dispose();
                return 0;
            }
        }
        else
        {
            if (rblType.SelectedValue == "1")
            {
                validationMsg = "Please select advanced import type option";
                Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                sr.Dispose();
                aFile.Dispose();
                return 0;
            }
        }
        string firstLevelColumnName = string.Empty;
        if (rblType.SelectedValue == "0")
        {
            for (int x = 0; x <= strArray.GetUpperBound(0); x++)
            {
                dt.Columns.Add(strArray[x].Trim().Replace("\"", "").Replace("*", "").Replace(" ", "").Replace(";", ",").Split('(')[0]);
                if (x > 0 && strArray[x - 1].Trim().Replace("\"", "").Replace("*", "").Replace(" ", "").Split('(')[0] == "BasicSalary")
                    firstLevelColumnName = strArray[x].Trim().Replace("\"", "").Replace("*", "").Replace(" ", "").Split('(')[0];
            }
        }
        else
        {
            if (Convert.ToString(Session["Demo"]) == "Enterprise")
            {
                for (int x = 0; x <= 36; x++)
                {
                    dt.Columns.Add(strArray[x].Trim().Replace("\"", "").Replace("*", "").Replace(" ", "").Replace(";", ",").Split('(')[0]);
                    if (x > 0 && strArray[x - 1].Trim().Replace("\"", "").Replace("*", "").Replace(" ", "").Split('(')[0] == "Basic Salary")
                    {
                        firstLevelColumnName = strArray[x].Trim().Replace("\"", "").Replace("*", "").Replace(" ", "").Split('(')[0];
                    }
                }
            }
            else
            {
                for (int x = 0; x <= 36; x++)
                {
                    dt.Columns.Add(strArray[x].Trim().Replace("\"", "").Replace("*", "").Replace(" ", "").Replace(";", ",").Split('(')[0]);
                    if (x > 0 && strArray[x - 1].Trim().Replace("\"", "").Replace("*", "").Replace(" ", "").Split('(')[0] == "BasicSalary")
                    {
                        firstLevelColumnName = strArray[x].Trim().Replace("\"", "").Replace("*", "").Replace(" ", "").Split('(')[0];
                    }
                }
            }
        }
        if (rblType.SelectedValue == "1")
        {
            firstLevelColumnName = "Level1";
            if (Convert.ToString(Session["Demo"]) == "Enterprise")
            {
                for (int i = 36; i <= 45; i++)
                {
                    dt.Columns.Add("Level" + (i - 35));
                }
            }
            else
            {
                for (int i = 36; i <= 45; i++)
                {
                    dt.Columns.Add("Level" + (i - 35));
                }
            }
        }
        else
        {
            for (int i = strArray.GetUpperBound(0) + 1; i <= 51; i++)
            {
                dt.Columns.Add("Field" + i);
            }
        }
        strLine = sr.ReadLine();
        //This Line Is Finished
        if (rblType.SelectedValue == "1")
        {
            while (strLine != null)
            {
                strLine = strLine.Replace(";", ",");
                strArray = strLine.Split(charArray);
                gstrDateFormat = "dd-MMM-yyyy";
                if (strArray.Length > totalColumnCount)
                {
                    validationMsg = rm.GetString("Alert_Commashouldnotbeusedinanyofthedata", ci);
                    Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                    sr.Dispose();
                    aFile.Dispose();
                    return 0;
                }
                if (strArray.Length <= 1)
                {
                    validationMsg = rm.GetString("ImportFiles_Separation", ci);
                    Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                    sr.Dispose();
                    aFile.Dispose();
                    return 0;
                }
                DataRow dr = dt.NewRow();
                for (int i = 0; i <= strArray.GetUpperBound(0); i++)
                {
                    dr[i] = strArray[i].Trim().Replace("\"", "");
                }
                if (Convert.ToString(Session["Demo"]) == "Enterprise")
                {
                    for (int i = strArray.GetUpperBound(0) + 1; i <= 45; i++)
                    {
                        dr[i] = "";
                    }
                }
                else
                {
                    for (int i = strArray.GetUpperBound(0) + 1; i <= 45; i++)
                    {
                        dr[i] = "";
                    }
                }
                dt.Rows.Add(dr);
                strLine = sr.ReadLine();
            }
            sr.Dispose();
            aFile.Dispose();
            // get active count from ta_tbl_employee     // add with ds row count and this sum  // will check against lic no of employee count
            if (ds.Tables[0].Rows.Count == 0)
            {
                validationMsg = rm.GetString("Alert_ThereisnorecordintheImportfile", ci);
                Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                sr.Dispose();
                aFile.Dispose();
                return 0;
            }
            StringWriter strWriteXML = new StringWriter();
            ds.WriteXml(strWriteXML);
            string strDataXML = Convert.ToString(strWriteXML);
            ObjImportData.InsertGridMasterData(strDataXML, Convert.ToString(Session["Demo"]), 0);
            loadGridData();
            return (ViewState["ImportMasterData"] != null) ? 0 : 1;
        }
        else
        {
            while (strLine != null)
            {
                gstrDateFormat = "dd-MMM-yyyy";
                strLine = strLine.Replace(";", ",");
                strArray = strLine.Split(charArray);
                if (strArray.Length > totalColumnCount)
                {
                    validationMsg = rm.GetString("Alert_Commashouldnotbeusedinanyofthedata", ci);
                    Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                    sr.Dispose();
                    aFile.Dispose();
                    return 0;
                }
                if (strArray.Length <= 1)
                {
                    validationMsg = rm.GetString("ImportFiles_Separation", ci);
                    Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                    sr.Dispose();
                    aFile.Dispose();
                    return 0;
                }
                try
                {
                    if (strArray[4].Trim().Replace("\"", "") != string.Empty)
                    {
                        strDateOfJoining = objCommon.DateConvertion(gstrDateFormat, strArray[4].Trim().Replace("\"", "").Replace(";", "").Replace("'", ""));
                        DateTime dateOfJoining = Convert.ToDateTime(strDateOfJoining);
                    }
                }
                catch (Exception ex)
                {
                    if (rblType.SelectedValue == "1")
                    {
                        validationMsg = rm.GetString("Alert_PleasechecktheDateOfjoiningFormat", ci);
                        Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                        sr.Dispose();
                        aFile.Dispose();
                        return 0;
                    }
                    else
                    {
                        validationMsg = rm.GetString("Alert_PleasechecktheDateOfjoiningFormat1", ci);
                        Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                        sr.Dispose();
                        aFile.Dispose();
                        return 0;
                    }
                }
                DataRow dr = dt.NewRow();
                for (int i = 0; i <= strArray.GetUpperBound(0); i++)
                {
                    if (i == 4)
                    {
                        dr[i] = strDateOfJoining;
                    }
                    else
                    {
                        dr[i] = strArray[i].Trim().Replace("\"", "");
                    }
                }
                for (int i = strArray.GetUpperBound(0) + 1; i <= 51; i++)
                {
                    dr[i] = "";
                }
                dt.Rows.Add(dr);
                strLine = sr.ReadLine();
            }
            sr.Dispose();
            aFile.Dispose();
            // get active count from ta_tbl_employee     // add with ds row count and this sum  // will check against lic no of employee count
            if (ds.Tables[0].Rows.Count == 0)
            {
                validationMsg = rm.GetString("Alert_ThereisnorecordintheImportfile", ci);
                Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                sr.Dispose();
                aFile.Dispose();
                return 0;
            }
            bool isAllowAdd = CheckNoOfEmployeeForLicense(ds);
            if (isAllowAdd)
            {
                //TOdo: Add Insert Functionality
                ds.Tables[0].DefaultView.RowFilter = "Emp_FirstName IS NULL OR Emp_FirstName = ''";
                if (ds.Tables[0].DefaultView.Count > 0)
                {
                    validationMsg = rm.GetString("Alert_PleasefillEmployeeFirstNameforallemployees", ci);
                }
                ds.Tables[0].DefaultView.RowFilter = "Card_Number IS NULL OR Card_Number = ''";
                if (ds.Tables[0].DefaultView.Count > 0)
                {
                    validationMsg = rm.GetString("Alert_PleasefillEmployeeCardIDforallemployees", ci);
                }
                ds.Tables[0].DefaultView.RowFilter = "Employee_Id IS NULL OR Employee_Id = ''";
                if (ds.Tables[0].DefaultView.Count > 0)
                {
                    validationMsg = rm.GetString("Alert_PleasefillEmployeeIDforallemployees", ci);
                }
                ds.Tables[0].DefaultView.RowFilter = "DateOfJoining IS NULL OR DateOfJoining = ''";
                if (ds.Tables[0].DefaultView.Count > 0)
                {
                    validationMsg = rm.GetString("Alert_PleasefillDateOfJoiningforallemployees", ci);
                }
                ds.Tables[0].DefaultView.RowFilter = "Designation IS NULL OR Designation = ''";
                if (ds.Tables[0].DefaultView.Count > 0)
                {
                    validationMsg = rm.GetString("Alert_PleasefillDesignationforallemployees", ci);
                }
                firstLevelColumnName = ds.Tables[0].Columns[6].ColumnName.ToString();
                ds.Tables[0].DefaultView.RowFilter = firstLevelColumnName.Trim() + " IS NULL OR " + firstLevelColumnName.Trim() + " = ''";
                if (ds.Tables[0].DefaultView.Count > 0 && validationMsg == string.Empty)
                {
                    validationMsg = rm.GetString("Alert_Pleasefill", ci) + " " + firstLevelColumnName + " " + rm.GetString("Alert_forallemployees", ci);
                }

                if (validationMsg != string.Empty)
                {
                    Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                    sr.Dispose();
                    aFile.Dispose();
                    return 0;
                }
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    ds.Tables[0].DefaultView.RowFilter = "Card_Number = '" + dr["Card_Number"].ToString() + "'";
                    if (ds.Tables[0].DefaultView.Count > 1)
                    {
                        Message("Card Number " + dr["Card_Number"] + " is duplicated");
                        sr.Dispose();
                        aFile.Dispose();
                        return 0;
                    }
                    ds.Tables[0].DefaultView.RowFilter = "Employee_ID = '" + dr["Employee_ID"].ToString() + "'";
                    if (ds.Tables[0].DefaultView.Count > 1)
                    {
                        Message("Employee ID " + dr["Employee_ID"] + " is duplicated");
                        return 0;
                    }

                }
                //Get Employee Details for the check employee code is already exits or not in employee master table. This line added by karthikeyan.r
                DataSet dsEmployeeDetails = new DataSet();
                dsEmployeeDetails = ObjImportData.GetEmployeeInformation();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    dsEmployeeDetails.Tables[0].DefaultView.RowFilter = "SwipeCardNo = '" + dr["Card_Number"].ToString() + "'";
                    if (dsEmployeeDetails.Tables[0].DefaultView.Count > 0)
                    {
                        Message("Card Number " + dr["Card_Number"] + " is duplicated");
                        sr.Dispose();
                        aFile.Dispose();
                        return 0;
                    }
                    dsEmployeeDetails.Tables[0].DefaultView.RowFilter = "Employee_Code_Id = '" + dr["Employee_ID"].ToString() + "'";
                    if (dsEmployeeDetails.Tables[0].DefaultView.Count > 0)
                    {
                        Message("Employee ID " + dr["Employee_ID"] + " is duplicated");
                        sr.Dispose();
                        aFile.Dispose();
                        return 0;
                    }
                }
                //simple template import insert method
                ObjImportData.InsertMasterDataEnterPrise(ds, Convert.ToBoolean(Session["ApplnMode"]), Convert.ToInt32(rblType.SelectedValue));
                return 1;
            }
            else
            {
                return 0;
            }
        }
    }
    private int ImportMasterDataForXls(FileInfo fileInfo)
    {
        string strScript = string.Empty;
        string validationMsg = string.Empty;
        DataSet ds = new DataSet();
        ds = ObjImportData.ReadExcelSheet(fileInfo);
        string firstLevelColumnName = string.Empty;
        if (ds.Tables[0].Rows.Count == 0)
        {
            validationMsg = rm.GetString("Alert_ThereisnorecordintheImportfile", ci);
            strScript = "<script language='javascript' type='text/javascript'>alert('" + validationMsg.Replace("\r", "").Replace("\n", "") + "');</script>";
            this.RegisterStartupScript("AlertErrorMsg", strScript);
            return 0;
        }
        if (rblType.SelectedValue == "1")
        {
            if (ds.Tables[0].Columns.Count < 36)
            {
                validationMsg = "Please select the Advanced import type templete file";
                Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                return 0;
            }
        }
        else
        {
            if (ds.Tables[0].Columns.Count > 10)
            {
                validationMsg = "Please select the simple import type templete file";
                Message(validationMsg.Replace("\r", "").Replace("\n", ""));
                return 0;
            }
        }

        DataTable dt = new DataTable();
        dt = ds.Tables[0];
        char TrimChar = '*';
        int intCount = 0;
        for (int incrementValue = 0; incrementValue < ds.Tables[0].Columns.Count; incrementValue++)
        {
            if (ds.Tables[0].Columns[incrementValue].ColumnName.ToString().Trim().Contains("*"))
            {
                ds.Tables[0].Columns[incrementValue].ColumnName = ds.Tables[0].Columns[incrementValue].ColumnName.ToString().Trim().TrimStart(TrimChar).Split('(')[0];
            }
            else
            {
                ds.Tables[0].Columns[incrementValue].ColumnName = ds.Tables[0].Columns[incrementValue].ColumnName.ToString().Trim().Split('(')[0];
            }
            if (rblType.SelectedValue == "1")
            {
                if (Convert.ToString(Session["Demo"]) == "Enterprise")
                {
                    if (incrementValue > 36)  //removed 2 columns in latest product 
                    {
                        intCount = intCount + 1;
                        ds.Tables[0].Columns[incrementValue].ColumnName = "Level" + Convert.ToString(intCount);
                    }
                }
                else
                {
                    if (incrementValue > 36)
                    {
                        intCount = intCount + 1;
                        ds.Tables[0].Columns[incrementValue].ColumnName = "Level" + Convert.ToString(intCount);
                    }
                }
            }
        }

        if (rblType.SelectedValue == "1")
        {
            firstLevelColumnName = "Level1";
            int intColumnCount = 49 - ds.Tables[0].Columns.Count;
            if (intColumnCount > 0)
            {
                for (int i = 0; i <= intColumnCount; i++)
                {
                    ds.Tables[0].Columns.Add("Level " + (intCount + i));
                }
            }
        }
        else
        {
            int intColumnCount = 52 - ds.Tables[0].Columns.Count;
            if (intColumnCount > 0)
            {
                for (int i = 0; i <= intColumnCount; i++)
                {
                    ds.Tables[0].Columns.Add("Column " + i);
                }
            }
        }


        bool isAllowAdd = CheckNoOfEmployeeForLicense(ds);
        if (rblType.SelectedValue == "1")
        {
            StringWriter strWriteXML = new StringWriter();
            ds.WriteXml(strWriteXML);
            string strDataXML = Convert.ToString(strWriteXML);
            ObjImportData.InsertGridMasterData(strDataXML, Convert.ToString(Session["Demo"]), 1);
            return 1;
        }
        else
        {

            if (isAllowAdd)
            {
                //TOdo: Add Insert Functionality
                ds.Tables[0].DefaultView.RowFilter = "Emp_FirstName IS NULL OR CONVERT(Emp_FirstName, System.String)  = ''";
                if (ds.Tables[0].DefaultView.Count > 0)
                {
                    validationMsg = rm.GetString("Alert_PleasefillEmployeeFirstNameforallemployees", ci);
                }
                ds.Tables[0].DefaultView.RowFilter = "Card_Number IS NULL OR CONVERT(Card_Number, System.String) = ''";
                if (ds.Tables[0].DefaultView.Count > 0)
                {
                    validationMsg = rm.GetString("Alert_PleasefillEmployeeCardIDforallemployees", ci);
                }
                ds.Tables[0].DefaultView.RowFilter = "Employee_Id IS NULL OR CONVERT(Employee_Id, System.String) = ''";
                if (ds.Tables[0].DefaultView.Count > 0)
                {
                    validationMsg = rm.GetString("Alert_PleasefillEmployeeIDforallemployees", ci);
                }
                ds.Tables[0].DefaultView.RowFilter = "DateOfJoining IS NULL OR CONVERT(DateOfJoining,System.String) = ''";
                if (ds.Tables[0].DefaultView.Count > 0)
                {
                    validationMsg = rm.GetString("Alert_PleasefillDateOfJoiningforallemployees", ci);
                }
                ds.Tables[0].DefaultView.RowFilter = "Designation IS NULL OR CONVERT(Designation,System.String) = ''";
                if (ds.Tables[0].DefaultView.Count > 0)
                {
                    validationMsg = rm.GetString("Alert_PleasefillDesignationforallemployees", ci);
                }
                firstLevelColumnName = ds.Tables[0].Columns[6].ColumnName.ToString();
                ds.Tables[0].DefaultView.RowFilter = firstLevelColumnName.Trim() + " IS NULL OR CONVERT( " + firstLevelColumnName.Trim() + " ,System.String) = ''";
                if (ds.Tables[0].DefaultView.Count > 0 && validationMsg == string.Empty)
                {
                    validationMsg = rm.GetString("Alert_Pleasefill", ci) + " " + firstLevelColumnName + " " + rm.GetString("Alert_forallemployees", ci).Replace("\r", "").Replace("\n", "");
                }
                if (validationMsg != string.Empty)
                {
                    strScript = "<script language='javascript' type='text/javascript'>alert('" + validationMsg.Replace("\r", "").Replace("\n", "") + "');</script>";
                    this.RegisterStartupScript("AlertErrorMsg", strScript);
                    return 0;
                }
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    try
                    {
                        gstrDateFormat = "dd-MMM-yyyy";
                        if (Session["MDateFormat"] != null)
                            gstrDateFormat = "dd-MMM-yyyy";
                        else
                            gstrDateFormat = "dd/MM/yyyy";

                        strDateOfJoining = objCommon.DateConvertion(gstrDateFormat, dr["DateOfJoining"].ToString());
                    }
                    catch (Exception ex)
                    {
                        if (rblType.SelectedValue == "1")
                        {
                            validationMsg = rm.GetString("Alert_PleasechecktheDateOfjoiningFormat", ci);
                            strScript = "<script language='javascript' type='text/javascript'>alert('" + validationMsg.Replace("\r", "").Replace("\n", "") + "');</script>";
                            this.RegisterStartupScript("AlertErrorMsg", strScript);
                            return 0;
                        }
                        else
                        {
                            validationMsg = rm.GetString("Alert_PleasechecktheDateOfjoiningFormat1", ci);
                            strScript = "<script language='javascript' type='text/javascript'>alert('" + validationMsg.Replace("\r", "").Replace("\n", "") + "');</script>";
                            this.RegisterStartupScript("AlertErrorMsg", strScript);
                            return 0;
                        }
                    }

                    //if (dr["Employee_ID"] == string.Empty || dr["Employee_ID"].ToString() == "")
                    //{
                    //    dr["Employee_ID"] = dr["Card_Number"].ToString();
                    //}
                    ds.Tables[0].DefaultView.RowFilter = "Card_Number = '" + dr["Card_Number"].ToString() + "'";
                    if (ds.Tables[0].DefaultView.Count > 1)
                    {
                        strScript = "<script language='javascript' type='text/javascript'>alert('Card Number " + dr["Card_Number"] + " is duplicated');</script>";
                        this.RegisterStartupScript("AlertErrorMsg", strScript);
                        return 0;
                    }
                    ds.Tables[0].DefaultView.RowFilter = "Employee_ID = '" + dr["Employee_ID"].ToString() + "'";
                    if (ds.Tables[0].DefaultView.Count > 1)
                    {
                        strScript = "<script language='javascript' type='text/javascript'>alert('Employee ID " + dr["Employee_ID"] + " is duplicated');</script>";
                        this.RegisterStartupScript("AlertErrorMsg", strScript);
                        return 0;
                    }


                }
                ObjImportData.InsertMasterDataEnterPrise(ds, Convert.ToBoolean(Session["ApplnMode"]), Convert.ToInt32(rblType.SelectedValue));
                return 1;
            }
            else
            {
                return 0;
            }
        }
    }
    private bool CheckNoOfEmployeeForLicense(DataSet dsImport)
    {
        DemoVersion objDemoVersion = new DemoVersion();
        bool allowEmployeeAdd = true;
        int importEmployee = 0;
        int existingEmployee = 0;
        string[] fileArr = Directory.GetFiles(Server.MapPath("../LicenseFile"), "*.lic");
        int arrCount = fileArr.Length;
        dsImport.Tables[0].TableName = "TheData";
        if (arrCount > 0)
        {
            string licenseFileName = fileArr[arrCount - 1].ToString();
            FileStream licenseFileStream = new FileStream(licenseFileName, FileMode.Open);
            StreamReader licenseFileStreamReader = new StreamReader(licenseFileStream);
            string encryptedLicFileInfo = licenseFileStreamReader.ReadToEnd();
            licenseFileStreamReader.Close();
            licenseFileStream.Close();
            string decryptedLicFileInfo = string.Empty;
            try
            {
                decryptedLicFileInfo = Encryption.Decrypt(encryptedLicFileInfo, "");
            }
            catch
            {
                string strScript = "<script language='javascript' type='text/javascript'>";
                strScript += "alert('" + rm.GetString("Alert_Licenseinformationarenotcorrect", ci) + "');";
                strScript += "location.href='../Index.aspx';";
                strScript += " </script>";
                this.RegisterStartupScript("AlertLicInfoNotCorrect", strScript);
                return false;
            }
            string[] decryptedLicFileInfoArr = decryptedLicFileInfo.Split('#');
            string basicLicInfo = decryptedLicFileInfoArr[0].ToString();
            string[] basicLicInfoArr = basicLicInfo.Split(',');
            int noOfEmployeeLicFile = Convert.ToInt32(basicLicInfoArr[4].ToString());
            existingEmployee = objDemoVersion.DVCheckEmployeeCreationImport("License", noOfEmployeeLicFile);
            if (dsImport != null && dsImport.Tables.Count > 0)
            {
                importEmployee = existingEmployee + dsImport.Tables["TheData"].Rows.Count;
            }
            if (importEmployee > noOfEmployeeLicFile)
            {
                allowEmployeeAdd = false;
            }
            else
            {
                allowEmployeeAdd = true;
            }
            if (allowEmployeeAdd)
            {
                return true;
            }
            else
            {
                Message(rm.GetString("Alert_LicensenotallowtoImportmoreEmployees", ci));
                return false;
            }
        }
        return true;
        #region Code Commented By A.K.T.MOORTHI. Bze In Demo Version User Can Import N Number Of User.
        #endregion
    }
    public string Export(DataSet ds)
    {
        string columnnames = "", body = "", record = "";
        foreach (DataColumn col in ds.Tables[0].Columns)
        {
            columnnames = columnnames + " " + col.ColumnName + " " + ",";
        }
        columnnames = columnnames.Substring(0, columnnames.Length - 1);
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            object[] arr = row.ItemArray;
            for (int i = 0; i <= arr.Length - 1; i++)
            {
                if (arr[i].ToString().IndexOf(",") > 0)
                {
                    record = record + " " + arr[i].ToString() + " " + ",";
                }
                else
                {
                    record = record + arr[i].ToString() + ",";
                }
            }
            body = body + record.Substring(0, record.Length - 1) + System.Environment.NewLine;
            record = "";
        }
        return (columnnames + System.Environment.NewLine + body);
    }
    /// <summary>
    /// Getting Information from resource file and display it in label
    /// </summary>
    /// <param name="ci"></param>
    private void LoadStrings(CultureInfo ci)
    {
        lbl_ImportEmployeeHeader.Text = rm.GetString("DataImport", ci);
        btn_Upload.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Upload", ci);
        lbl_ImportFilePath.Text = rm.GetString("SelectFile", ci);
        btnTemplate.Text = "&nbsp;&nbsp;" + rm.GetString("strDownloadTemplate", ci);
        lbl_Type.Text = rm.GetString("lbl_ImportType", ci);
        rblType.Items[0].Text = rm.GetString("rbt_Simple", ci);
        rblType.Items[1].Text = rm.GetString("rbt_Advanced", ci);
        lbl_IndicatesMandatoryFields.Text = rm.GetString("IndicatesMandatoryFields", ci);
        lblImportNote.Text = rm.GetString("Field_ImportantNote", ci);
        //btnDelete.Text = "&nbsp;&nbsp;" + rm.GetString("btn_Delete", ci);
        btnDelete.Text = "&nbsp;&nbsp;Clear";
    }
    #endregion
    #region Grid Events    
    public void GetEmptyText(object sender, EventArgs e)
    {
        Label lblNoRecords = (Label)sender;
        lblNoRecords.Text = rm.GetString("Alert_Norecordsfound", ci);
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        if (Page.IsValid)
        {
            ObjImportData.ToremoveImportErrorData();
            ViewState["ImportMasterData"] = null;
            gvImportMaster.DataSource = null;
            gvImportMaster.DataBind();
            btnDelete.Visible = false;
        }
    }
    #endregion
}