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_CusInv : 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;
        CusInvDAL dal = new CusInvDAL();
        CustomerDAL cus_dal = new CustomerDAL();
        InventoryDAL i_dal = new InventoryDAL();
        CurrencyDAL crc_dal = new CurrencyDAL();
        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;
            if (dr[2] == null || dr[2].ToString().Equals("")) break;
            CusInv m = new CusInv();
            string main_msg = "";
            try
            {
                string cus_code = dr[0].ToString();
                if (!cus_code.Equals(""))
                {
                    if (!cus_dal.CheckExist(cus_code))
                    {
                        msg += "第" + row + "行 导入失败！\n原因：客户编码：" + cus_code + "不存在!\n\n";
                        continue;
                    }
                    m.cus_id = cus_dal.GetIdByCode(cus_code);//客户id
                }
                string i_code = dr[2].ToString();
                if (!i_code.Equals(""))
                {
                    if (!i_dal.CheckExist(i_code))
                    {
                        msg += "第" + row + "行 导入失败！\n原因：存货编码：" + i_code + "不存在!\n\n";
                        continue;
                    }
                    m.i_id = i_dal.GetIdByCode(i_code);//存货id
                    Inventory inv = i_dal.Get(m.i_id);
                    m.free1 = dr[8].ToString();//自由项1
                    m.free2 = dr[9].ToString();//自由项2
                    if (inv.on_free1 == 0 && !m.free1.Equals(""))
                    {
                        msg += "第" + row + "行 " + i_code + "导入失败！\n原因：存货未启用自由项1但填写了自由项1。\n\n";
                        continue;
                    }
                    if (inv.on_free2 == 0 && !m.free2.Equals(""))
                    {
                        msg += "第" + row + "行 " + i_code + "导入失败！\n原因：存货未启用自由项2但填写了自由项2。\n\n";
                        continue;
                    }
                }
                main_msg = "客户编码：" + cus_code + " 存货编码：" + i_code + " ";
                m.cus_i_name = dr[4].ToString();//存货别名
                
                m.cus_price = XTools.DBNullToDecimal(dr[5]);//客户报价
                string crc_code = dr[6].ToString();
                if (!crc_code.Equals(""))
                {
                    if (!crc_dal.CheckCodeExist(crc_code))
                    {
                        msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：币种编码：" + crc_code + "不存在!\n\n";
                        continue;
                    }
                    m.crc_id = crc_dal.GetIdByCode(crc_code);//币种id
                }
                m.tax_rate = XTools.DBNullToDecimal(dr[7]);//税率

                
                m.sdefine1 = dr[10].ToString();
                m.sdefine2 = dr[11].ToString();
                m.sdefine3 = dr[12].ToString();
                m.sdefine4 = dr[13].ToString();
                m.sdefine5 = dr[14].ToString();
                m.ndefine1 = XTools.DBNullToDecimal(dr[15]);
                m.ndefine2 = XTools.DBNullToDecimal(dr[16]);
                m.ndefine3 = XTools.DBNullToDecimal(dr[17]);
                m.ndefine4 = XTools.DBNullToDecimal(dr[18]);
                m.ndefine5 = XTools.DBNullToDecimal(dr[19]);
                


                
                if (dal.CheckExist(m.cus_id, m.i_id, m.free1, m.free2, m.crc_id))
                {
                    exist_rows ++;
                    //msg += "第" + row + "行 导入失败！" + main_msg + "已存在!\n\n";
                    continue;
                }



                dal.Add(m);
                //msg += "第" + row + "行 导入成功！" + main_msg + "\n";
                success_rows++;
            }
            catch(Exception err)
            {
                msg += "第" + row + "行 导入失败！" + main_msg + "\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;
    }

}