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_Customer : 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;
        CustomerDAL dal = new CustomerDAL();
        CustomerClassDAL cc_dal = new CustomerClassDAL();
        EmployeeDAL ele_dal = new EmployeeDAL();
        WarehouseDAL wh_dal = new WarehouseDAL();
        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;
            Customer m = new Customer();
            try
            {
                m.is_stop = 0;//默认不停用
                m.cus_code = dr[0].ToString();
                m.cus_name = dr[1].ToString();
                m.cus_full_name = dr[2].ToString();
                string cc_code = dr[3].ToString();
                m.cc_id = cc_dal.GetIdByCode(cc_code);
                string ele_code = dr[4].ToString();//专管业务员编码
                m.ele_id = ele_dal.GetIdByCode(ele_code);
                m.address = dr[5].ToString();
                m.ship_address = dr[6].ToString();
                m.contact = dr[7].ToString();
                m.legal_person = dr[8].ToString();
                m.phone = dr[9].ToString();
                m.cellphone = dr[10].ToString();
                m.fax = dr[11].ToString();
                m.email = dr[12].ToString();
                m.qq = dr[13].ToString();
                m.bank_of_deposit = dr[14].ToString();
                m.banking_account = 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.cus_code + " 客户名称：" + m.cus_name + " ";
                if (dal.CheckExist(m.cus_code))
                {
                    exist_rows ++;
                    //msg += "第" + row + "行 导入失败！客户编码：" + m.cus_code + "已存在!\n\n";
                    continue;
                }
                if (!cc_dal.CheckExist(cc_code))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：客户分类编码：" + cc_code + "不存在!\n\n";
                    continue;
                }
                if (cc_dal.CheckHasChild(m.cc_id))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：客户分类" + cc_code + "非末级，不允许新增客户!\n\n";
                    continue;
                }
                if (!ele_code.Equals("") && !ele_dal.CheckExist(ele_code))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：默认业务员编码：" + ele_code + "不存在!\n\n";
                    continue;
                }
                dal.Add(m);
                //msg += "第" + row + "行 导入成功！客户编码：" + m.cus_code + " 客户名称：" + m.cus_name + "\n";
                success_rows++;
            }
            catch(Exception err)
            {
                msg += "第" + row + "行 导入失败！客户编码：" + m.cus_code + " 客户名称：" + m.cus_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;
    }

}