HEGII.ss.mes.web/wwwroot/mes/mst/CommonModelGood/CommonModel_importGOODS.ashx

<%@ WebHandler Language="C#" Class="testform_importBarCode" %>

using System;
using System.Web;
using System.Web.SessionState;
using System.Data;
using DK.XuWei.WebMes;
using Curtain.DataAccess;
using Curtain.Log;

//EXCEL模板，要求

//...
public class testform_importBarCode : IHttpHandler, IReadOnlySessionState
{

    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";

        //上传文件
        string filePath = "/mes/upload/"+DateTime.Now.ToString("yyyy-MM-dd");
        System.IO.Directory.CreateDirectory(context.Server.MapPath(filePath));
        string fileName = filePath + "/常用产品" + DateTime.Now.ToString("yyyy-MM-dd");
        fileName += System.IO.Path.GetExtension(context.Request.Files[0].FileName);
        string diskFileName = context.Server.MapPath(fileName);
        if (System.IO.File.Exists(diskFileName)) System.IO.File.Delete(diskFileName);
        context.Request.Files[0].SaveAs(diskFileName);

        //读excel
        DataTable dt = Import.ExcelToDataTable(diskFileName);

        //Logger.Debug(dt.Rows.Count.ToString());

        //写入数据
        using (IDataAccess conn = DataAccess.Create())
        {
            int errorLineNo = 0;
            conn.BeginTransaction();
            try
            {
                #region 删除实验单所有产品
                object a = context.Request["testformguidMaster"];
                //int result0 = conn.ExecuteNonQuery(@"
                //    DELETE 
                //        TP_PM_TESTFORM2_GOODS 
                //    WHERE 
                //        TESTFORMGUID = @TESTFORMGUID@ 
                //    ",
                //    new CDAParameter("TESTFORMGUID", context.Request["testformguidMaster"])
                //);
                #endregion
                //判断字段包含
                string barCodeStr = "";
                for (int i=0;i<dt.Rows.Count;i++)
                {
                    if (dt.Rows[i]["产品"] + "" == "") {
                        continue;
                    }
                    if (dt.Rows[i]["商标"] + "" == "") {
                        continue;
                    }
                    #region 校验重复
                    object isExists = conn.ExecuteScalar(
                        @"SELECT 1 FROM TP_MST_COMMONMODEL WHERE GOODSCODE = @GOODSCODE@ AND LOGOID =  (SELECT LOGOID FROM TP_MST_LOGO WHERE LOGONAME = @LOGONAME@)",
                        new CDAParameter("GOODSCODE", dt.Rows[i]["产品"]),
                        new CDAParameter("LOGONAME", dt.Rows[i]["商标"]),
                        new CDAParameter("COMMONGOODSID", context.Request["testformguidMaster"])
                    );
                    if (isExists != null)
                    {
                        continue;
                    }
                    #endregion

                    //记录行号
                    errorLineNo = i;

                    int result = conn.ExecuteNonQuery(@"
                        INSERT INTO TP_MST_COMMONMODEL 
                    (GOODSCODE,GOODSID,LOGOID,OUTLETDISTANCE,MATERIALCODE,PLATELIMITNUM1,PLATELIMITNUM2,COMMONFLAG,EFFECTIVETIME,ENDTIME,CREATEUSERID,UPDATEUSERID,ACCOUNTID)
                    VALUES
	                (
	                @GOODSCODE@,
                    (SELECT GOODSID FROM TP_MST_GOODS WHERE GOODSCODE = @GOODSCODE@),
	                (SELECT LOGOID FROM TP_MST_LOGO WHERE LOGONAME = @LOGONAME@),
	                @OUTLETDISTANCE@,
	                @MATERIALCODE@,
	                @PLATELIMITNUM1@,
	                @PLATELIMITNUM2@,
	                @COMMONFLAG@,
	                @EFFECTIVETIME@,
	                @ENDTIME@,
	                @CREATEUSERID@,
	                @UPDATEUSERID@,
	                @ACCOUNTID@
	                )",
                    new CDAParameter("GOODSCODE", dt.Rows[i]["产品"]),
                    new CDAParameter("LOGONAME", dt.Rows[i]["商标"]),
                    new CDAParameter("OUTLETDISTANCE", dt.Rows[i]["坑距"]),
                    new CDAParameter("MATERIALCODE", dt.Rows[i]["物料号"]),
                    new CDAParameter("PLATELIMITNUM1", dt.Rows[i]["装板数量1"]),
                    new CDAParameter("PLATELIMITNUM2", dt.Rows[i]["装板数量2"]),
                    new CDAParameter("COMMONFLAG", dt.Rows[i]["常用标识"]),
                    new CDAParameter("EFFECTIVETIME", dt.Rows[i]["生效时间"], DataType.DateTime),
                    new CDAParameter("ENDTIME", dt.Rows[i]["截止时间"], DataType.DateTime),
                    new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                    new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                    new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                    );
                }
                conn.Commit();
                context.Response.Write(new JsonResult(JsonStatus.success).ToJson());
            }
            catch(Exception e)
            {
                Logger.Error(e);
                conn.Rollback();
                context.Response.Write(new JsonResult(JsonStatus.otherError) { message = "行号：" + errorLineNo + " 数据错误，请检查！" }.ToJson());
            }

        }


    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}