using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using System.Drawing.Imaging;
using System.Drawing.Drawing2D;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using SysCore;
public partial class Base_ImportExcel_Import_Employee : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {

        }        
    }
    protected void btn_upload_Click(object sender, EventArgs e)
    {
        string [] arr = file_upload.FileName.Split('.');
        string ext = arr[arr.Length-1].ToLower();
        if (!(ext.Equals("xls")||ext.Equals("xlsx")))
        {
            msg = "请上传后缀为.xls的Excel格式!";
            return;
        }
        string db_id = new LoginInfo().db_id.ToString();
        string filename = XTools.GetRandom(6);
        string save_path_base = "../../Upload/" + db_id + "/Temp/ImportExcel";
        if(!Directory.Exists(Server.MapPath(save_path_base)))
        {
            Directory.CreateDirectory(Server.MapPath(save_path_base));
        }
        string save_path = save_path_base + "/" + filename;
        string real_save_path = Server.MapPath(save_path + "." + ext);
        file_upload.SaveAs(real_save_path);
        ImportExcel(real_save_path);
        File.Delete(real_save_path);
    }


    public string msg = "";
    public bool ImportExcel(string file_name)
    {
        ExcelReader er = new ExcelReader();
        DataTable dt = er.GetExcelTable(file_name, "Sheet1$");
        int row = 0;
        EmployeeDAL dal = new EmployeeDAL();
        DepartmentDAL dep_dal = new DepartmentDAL();
        int total_rows = dt.Rows.Count;//总记录数
        int exist_rows = 0;//已存在数
        int success_rows = 0;//成功导入数
        foreach (DataRow dr in dt.Rows)
        {
            row++;
            if (dr[0] == null || dr[0].ToString().Equals("")) break;
            Employee m = new Employee();
            try
            {
                
                m.ele_code = dr[0].ToString();
                m.ele_name = dr[1].ToString();
                string dep_code = dr[2].ToString();
                m.dep_id = dep_dal.GetIdByCode(dep_code);
                m.position = dr[3].ToString();//职务
                m.entry_date = XTools.DBNullToTime(dr[4]);
                
                m.birthday = XTools.DBNullToTime(dr[5]);
                if (dr[6].Equals("男"))
                {
                    m.sex = 1;
                }
                else if (dr[6].Equals("女"))
                {
                    m.sex = 0;
                }
                else
                {
                    m.sex = 2;//未知
                }
                m.native_place = dr[7].ToString();//籍贯
                m.degree = dr[8].ToString();
                m.id_card_no = dr[9].ToString();
                m.office_phone = dr[10].ToString();
                m.cellphone = dr[11].ToString();
                m.home_phone = dr[12].ToString();
                m.email = dr[13].ToString();
                m.qq = dr[14].ToString();
                m.address = dr[15].ToString();

                m.sdefine1 = dr[16].ToString();
                m.sdefine2 = dr[17].ToString();
                m.sdefine3 = dr[18].ToString();
                m.sdefine4 = dr[19].ToString();
                m.sdefine5 = dr[20].ToString();
                m.ndefine1 = XTools.DBNullToDecimal(dr[21]);
                m.ndefine2 = XTools.DBNullToDecimal(dr[22]);
                m.ndefine3 = XTools.DBNullToDecimal(dr[23]);
                m.ndefine4 = XTools.DBNullToDecimal(dr[24]);
                m.ndefine5 = XTools.DBNullToDecimal(dr[25]);

                string main_msg = "员工编码：" + m.ele_code + " 员工名称：" + m.ele_name + " ";
                if (dal.CheckExist(m.ele_code))
                {
                    exist_rows ++;
                    //msg += "第" + row + "行 导入失败！员工编码：" + m.ele_code + "已存在!\n\n";
                    continue;
                }
                if (!dep_dal.CheckExist(dep_code))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：部门编码：" + dep_code + "不存在!\n\n";
                    continue;
                }
                if (dep_dal.CheckHasChild((int)m.dep_id))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：部门" + dep_code + "非末级，不允许新增员工!\n\n";
                    continue;
                }

                dal.Add(m);
                //msg += "第" + row + "行 导入成功！员工编码：" + m.ele_code + " 员工名称：" + m.ele_name + "\n";
                success_rows++;
            }
            catch(Exception err)
            {
                msg += "第" + row + "行 导入失败！员工编码：" + m.ele_code + " 员工名称：" + m.ele_name + "\n出错信息:" + err.Message + "\n\n";
            }
        }
        msg = "需要导入数:" + total_rows.ToString() + "行，已存在" + exist_rows.ToString() + "行，成功导入" + success_rows.ToString() + "行.\n\n" + msg;
        er.xls_conn.Close();//关闭Excel连接
        return true;
    }

}