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_InOut : 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;
        InOutsDAL dal = new InOutsDAL();
        WarehouseDAL wh_dal = new WarehouseDAL();
        DepartmentDAL dep_dal = new DepartmentDAL();
        InventoryDAL i_dal = new InventoryDAL();
        int total_rows = dt.Rows.Count;//总记录数
        int success_rows = 0;//成功导入数
        int io_id = Convert.ToInt32(Request["id"]);
        string wh_code = "";
        string dep_code = "";
        string i_code = "";
        dal.DeleteByOrder(io_id);//先清空表体
        foreach (DataRow dr in dt.Rows)
        {
            row++;
            if (dr[0] == null || dr[0].ToString().Equals("")) break;
            InOuts m = new InOuts();
            try
            {
                m.io_id = io_id;
                wh_code = dr[0].ToString();
                m.d_wh_id = wh_dal.GetIdByCode(wh_code);
                dep_code = dr[1].ToString();
                m.d_dep_id = dep_dal.GetIdByCode(dep_code);
                i_code = dr[2].ToString();
                m.i_id = i_dal.GetIdByCode(i_code);
                m.free1 = dr[3].ToString();
                m.free2 = dr[4].ToString();
                m.batch = dr[5].ToString();
                m.quantity = XTools.DBNullToDecimal(dr[6]);
                m.num = XTools.DBNullToDecimal(dr[7]);
                m.convert_rate = XTools.DBNullToDecimal(dr[8]);//换算率
                m.unit_price = XTools.DBNullToDecimal(dr[9]);//无税单价
                m.total_price = XTools.DBNullToDecimal(dr[10]);//无税金额
                m.tax_unit_price = XTools.DBNullToDecimal(dr[11]);//含税单价
                m.tax_total_price = XTools.DBNullToDecimal(dr[12]);//含税金额
                m.tax = m.tax_total_price - m.total_price;//税额
                m.d_remark = dr[14].ToString();//备注
                m.d_sdefine1 = dr[15].ToString();//字符自定义项1
                m.d_sdefine2 = dr[16].ToString();//字符自定义项2
                m.d_sdefine3 = dr[17].ToString();//字符自定义项3
                m.d_sdefine4 = dr[18].ToString();//字符自定义项4
                m.d_sdefine5 = dr[19].ToString();//字符自定义项5

                m.d_ndefine1 = XTools.DBNullToDecimal(dr[20]);//数值自定义项1
                m.d_ndefine2 = XTools.DBNullToDecimal(dr[21]);//数值自定义项2
                m.d_ndefine3 = XTools.DBNullToDecimal(dr[22]);//数值自定义项3
                m.d_ndefine4 = XTools.DBNullToDecimal(dr[23]);//数值自定义项4
                m.d_ndefine5 = XTools.DBNullToDecimal(dr[24]);//数值自定义项5

                string main_msg = "仓库编码：" + wh_code + " 存货编码：" + i_code + " ";
                main_msg = "";
                if (!dep_code.Equals("") && !dep_dal.CheckExist(dep_code))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：部门编码：" + dep_code + "不存在!\n\n";
                    continue;
                }
                if (!wh_code.Equals("") && !wh_dal.CheckExist(wh_code))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：仓库编码：" + wh_code + "不存在!\n\n";
                    continue;
                }
                if (!i_code.Equals("") && !i_dal.CheckExist(i_code))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货编码：" + i_code + "不存在!\n\n";
                    continue;
                }
                Inventory inv = i_dal.Get(m.i_id);
                if (inv.on_free1 == 1 && m.free1.Equals(""))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货启用了自由项1但未填写自由项1。\n\n";
                    continue;
                }
                if (inv.on_free2 == 1 && m.free2.Equals(""))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货启用了自由项2但未填写自由项2。\n\n";
                    continue;
                }
                if (inv.on_batch == 1 && m.batch.Equals(""))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货启用了批次但未填写批次。\n\n";
                    continue;
                }

                if (inv.on_free1 == 0 && !m.free1.Equals(""))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货未启用自由项1但填写了自由项1。\n\n";
                    continue;
                }
                if (inv.on_free2 == 0 && !m.free2.Equals(""))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货未启用自由项2但填写了自由项2。\n\n";
                    continue;
                }
                if (inv.on_batch == 0 && !m.batch.Equals(""))
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货未启用批次但填写了批次。\n\n";
                    continue;
                }

                if (!inv.aunit.Equals("") && m.num == null)
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货启用了辅计量单位但未填写件数。\n\n";
                    continue;
                }
                if (inv.aunit.Equals("") && m.num != null)
                {
                    msg += "第" + row + "行 " + main_msg + "导入失败！\n原因：存货未启用辅计量单位但填写了件数。\n\n";
                    continue;
                }
                if (m.d_dep_id == 0)
                {
                    m.d_dep_id = null;
                }
                dal.Add(m);
                success_rows++;
            }
            catch(Exception err)
            {
                msg += "第" + row + "行 导入失败！存货编码：" + i_code + " 仓库编码：" + wh_code + "\n出错信息:" + err.Message + "\n\n";
            }
        }
        msg = "需要导入数:" + total_rows.ToString() + "行，成功导入" + success_rows.ToString() + "行.\n\n" + msg;
        er.xls_conn.Close();//关闭Excel连接
        return true;
    }

}