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_Inventory : 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;
        InventoryDAL dal = new InventoryDAL();
        InventoryClassDAL ic_dal = new InventoryClassDAL();
        VendorDAL vd_dal = new VendorDAL();
        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;
            Inventory m = new Inventory();
            try
            {
                m.i_code = dr[0].ToString();
                m.i_name = dr[1].ToString();
                string ic_code = dr[2].ToString();
                m.ic_id = ic_dal.GetIdByCode(ic_code);
                m.standard = dr[3].ToString();
                m.munit = dr[4].ToString();
                m.aunit = dr[5].ToString();
                m.convert_rate = XTools.DBNullToDecimal(dr[6]);
                m.on_free1 = dr[7] == DBNull.Value ? 0 : 1;
                m.on_free2 = dr[8] == DBNull.Value ? 0 : 1;
                m.on_batch = dr[9] == DBNull.Value ? 0 : 1;
                m.is_sale = dr[10] == DBNull.Value ? 0 : 1;
                m.is_purchase = dr[11] == DBNull.Value ? 0 : 1;
                m.is_self = dr[12] == DBNull.Value ? 0 : 1;
                m.is_outsource = dr[13] == DBNull.Value ? 0 : 1;
                string vd_code = dr[14].ToString();
                m.vd_id = vd_dal.GetIdByCode(vd_code);
                string wh_code = dr[15].ToString();
                m.wh_id = wh_dal.GetIdByCode(wh_code);
                m.safety_stock = XTools.DBNullToDecimal(dr[16]);
                m.min_stock = XTools.DBNullToDecimal(dr[17]);
                m.max_stock = XTools.DBNullToDecimal(dr[18]);
                m.purchase_batch = XTools.DBNullToDecimal(dr[19]);
                m.new_tax_unit_price = XTools.DBNullToDecimal(dr[20]);
                m.new_nat_unit_price = XTools.DBNullToDecimal(dr[21]);
                m.sdefine1 = dr[22].ToString();
                m.sdefine2 = dr[23].ToString();
                m.sdefine3 = dr[24].ToString();
                m.sdefine4 = dr[25].ToString();
                m.sdefine5 = dr[26].ToString();
                m.ndefine1 = XTools.DBNullToDecimal(dr[27]);
                m.ndefine2 = XTools.DBNullToDecimal(dr[28]);
                m.ndefine3 = XTools.DBNullToDecimal(dr[29]);
                m.ndefine4 = XTools.DBNullToDecimal(dr[30]);
                m.ndefine5 = XTools.DBNullToDecimal(dr[31]);

                string main_msg = "存货编码：" + m.i_code + " 存货名称：" + m.i_name + " ";
                if (dal.CheckExist(m.i_code))
                {
                    exist_rows ++;
                    //msg += "第" + row + "行 导入失败！存货编码：" + m.i_code + "已存在!\n\n";
                    continue;
                }
                if (!ic_dal.CheckExist(ic_code))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货分类编码：" + ic_code + "不存在!\n\n";
                    continue;
                }
                if (ic_dal.CheckHasChild(m.ic_id))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货分类" + ic_code + "非末级，不允许新增存货!\n\n";
                    continue;
                }
                if (!vd_code.Equals("") && !vd_dal.CheckExist(vd_code))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：默认供应商编码：" + vd_code + "不存在!\n\n";
                    continue;
                }
                if (!wh_code.Equals("") && !wh_dal.CheckExist(wh_code))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：默认仓库编码：" + wh_code + "不存在!\n\n";
                    continue;
                }
                dal.Add(m);
                //msg += "第" + row + "行 导入成功！存货编码：" + m.i_code + " 存货名称：" + m.i_name + "\n";
                success_rows++;
            }
            catch(Exception err)
            {
                msg += "第" + row + "行 导入失败！存货编码：" + m.i_code + " 存货名称：" + m.i_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;
    }

}