Пример #1
0
        public HttpResponseBase ImportVendorBank()
        {

            string json = "{success:false}";
            try
            {
                if (Request.Files["importFile"] != null && Request.Files["importFile"].ContentLength > 0)
                {
                    HttpPostedFileBase excelFile = Request.Files["importFile"];
                    FileManagement fileManagement = new FileManagement();
                    string i = excelFile.FileName;
                    string newExcelName = Server.MapPath(excelPath_export) + "供應商銀行代碼" + fileManagement.NewFileName(excelFile.FileName);//處理文件名,獲取新的文件名
                    string oldExcelName = excelFile.FileName.Split('\\').LastOrDefault();
                    excelFile.SaveAs(newExcelName);//上傳文件
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
                    dt = helper.SheetData();
                    string s = dt.Rows[0][0].ToString();
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        _vendorMgr = new VendorMgr(connectionString);
                        string result = _vendorMgr.ImportVendorBank(dt);
                        if (result == "")
                        {
                            json = "{success:true,error:\"0\"}";
                        }
                        else
                        {
                            json = "{success:true,error:\"" + result + "\"}";
                        }
                    }
                    FileInfo file = new FileInfo(newExcelName);
                    file.Delete();
                }

            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,error:\"" + BLL.gigade.Common.CommonFunction.MySqlException(ex) + "\"}";

            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Пример #2
0
 public HttpResponseBase ImportExcel()
 {
     string json = string.Empty;
     string excelPath = "../ImportUserIOExcel/";
     try
     {
         if (Request.Files.Count > 0)
         {
             int group_id = Convert.ToInt32(Request.Params["group_id"]);
             HttpPostedFileBase excelFile = Request.Files["ImportExcel"];
             FileManagement fileManagement = new FileManagement();
             string newExcelName = Server.MapPath(excelPath) + "email_group" + fileManagement.NewFileName(excelFile.FileName);
             excelFile.SaveAs(newExcelName);
             NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
             DataTable _dt = helper.SheetData();
             _emailGroupMgr = new EmailGroupMgr(mySqlConnectionString);
             _newDt.Clear();
             int totalCount = 0;
             _newDt = _emailGroupMgr.ImportEmailList(_dt, group_id, out totalCount);//匯入失敗的數據
             if (_newDt.Rows.Count > 0 && _newDt != null)
             {
                 int totalCountData = totalCount;
                 int wrongCount = _newDt.Rows.Count;
                 json = "{success:true,totalCount:'" + totalCountData + "',wrongCount:'" + wrongCount + "'}";
             }
             else
             {
                 json = "{success:true,wrongCount:'" + 0 + "'}";
             }
           
         }
     }
     catch (Exception ex)
     {
         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
         log.Error(logMessage);
         json = "{success:false}";
     }
     this.Response.Clear();
     this.Response.Write(json);
     this.Response.End();
     return this.Response;
 }
Пример #3
0
        public HttpResponseBase SaveVipUserGroup()
        {
            string json = string.Empty;
            try
            {
                VipUserGroupQuery query = new VipUserGroupQuery();

                if (!string.IsNullOrEmpty(Request.Params["group_id"]))
                {
                    query.group_id = Convert.ToUInt32(Request.Params["group_id"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["group_name"]))
                {
                    query.group_name = Request.Params["group_name"];
                }
                if (!string.IsNullOrEmpty(Request.Params["eng_name"]))
                {
                    query.eng_name = Request.Params["eng_name"];
                }
                if (!string.IsNullOrEmpty(Request.Params["tax_id"]))
                {
                    query.tax_id = Request.Params["tax_id"];
                }
                if (!string.IsNullOrEmpty(Request.Params["group_code"]))
                {
                    query.group_code = Request.Params["group_code"];
                }
                if (!string.IsNullOrEmpty(Request.Params["group_capital"]))
                {
                    query.group_capital = Convert.ToInt32(Request.Params["group_capital"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["check_iden"]))
                {
                    query.check_iden = Convert.ToInt32(Request.Params["check_iden"]);
                }
                
                if (!string.IsNullOrEmpty(Request.Params["group_emp_number"]))
                {
                    query.group_emp_number = Convert.ToInt32(Request.Params["group_emp_number"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["group_emp_age"]))
                {
                    query.group_emp_age = Request.Params["group_emp_age"];
                }
                if (!string.IsNullOrEmpty(Request.Params["group_emp_gender"]))
                {
                    query.group_emp_gender = Convert.ToInt32(Request.Params["group_emp_gender"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["group_benefit_type"]))
                {
                    query.group_benefit_type = Request.Params["group_benefit_type"];
                }
                if (!string.IsNullOrEmpty(Request.Params["group_benefit_desc"]))
                {
                    query.group_benefit_desc = Request.Params["group_benefit_desc"].Replace("\\", "\\\\"); ;
                }
                if (!string.IsNullOrEmpty(Request.Params["group_subsidiary"]))
                {
                    query.group_subsidiary = Convert.ToInt32(Request.Params["group_subsidiary"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["group_hq_name"]))
                {
                    query.group_hq_name = Request.Params["group_hq_name"];
                }
                if (!string.IsNullOrEmpty(Request.Params["group_hq_code"]))
                {
                    query.group_hq_code = Request.Params["group_hq_code"];
                }
                if (!string.IsNullOrEmpty(Request.Params["group_committe_name"]))
                {
                    query.group_committe_name = Request.Params["group_committe_name"];
                }
                if (!string.IsNullOrEmpty(Request.Params["group_committe_code"]))
                {
                    query.group_committe_code = Request.Params["group_committe_code"];
                }
                if (!string.IsNullOrEmpty(Request.Params["group_committe_chairman"]))
                {
                    query.group_committe_chairman = Request.Params["group_committe_chairman"];
                }
                if (!string.IsNullOrEmpty(Request.Params["group_committe_phone"]))
                {
                    query.group_committe_phone = (Request.Params["group_committe_phone"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["group_committe_mail"]))
                {
                    query.group_committe_mail = (Request.Params["group_committe_mail"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["group_committe_promotion"]))
                {
                    query.group_committe_promotion = (Request.Params["group_committe_promotion"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["group_committe_other"]))
                {
                    query.group_committe_other = (Request.Params["group_committe_other"]);
                }
                if (!string.IsNullOrEmpty(Request.Params["group_committe_desc"]))
                {
                    query.group_committe_desc = (Request.Params[@"group_committe_desc"]).Replace("\\", "\\\\");
                }
                DataTable _dt=new DataTable();
                if (Request.Files.Count > 0)
                {
                    HttpPostedFileBase excelFile = Request.Files["employee_list"];
                    query.file_name= excelFile.FileName;
                    FileManagement fileManagement = new FileManagement();
                    string newExcelName = Server.MapPath(excelPath) + "vip_user_group" + fileManagement.NewFileName(excelFile.FileName);
                    excelFile.SaveAs(newExcelName);
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
                    _dt = helper.SheetData();
               }

                _vipUserGroup = new VipUserGroupMgr(mySqlConnectionString);
                json = _vipUserGroup.SaveVipUserGroup(query, _dt);
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,msg:'0'}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
 public string EditUpload()
 {
     ProductItemMap m = new ProductItemMap();
     int channel_id = int.Parse(Request.Form["channel"]);
     int Ins = 0; int Upd = 0;
     #region 讀取excel文件
     string fileName = Request.Files[0].FileName;
     string filePath = "";
     string fName = "";
     string fExtension = "";
     try
     {
         FileOperator fo = new FileOperator(fileName);
         fExtension = fo.Extension;
         if (fExtension != ".xls" && fExtension != ".xlsx")
         {
             return "{success:false,msg:'" + Resources.ProductItemMap.EXCEL_FORMAT_ERROR + "'}";
         }
         if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
         {
             System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
         }
         fName = DateTime.Now.ToString("yyyyMMddhhmmss") + fo.Extension;
         filePath = Server.MapPath(excelPath) + "/" + fName;
     }
     catch (Exception ex)
     {
         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
         log.Error(logMessage);
         return "{success:false,msg:'" + Resources.ProductItemMap.EXCEL_FORMAT_ERROR + "'}";
     }
     try
     {
         Request.Files[0].SaveAs(filePath);
     }
     catch (Exception ex)
     {
         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
         log.Error(logMessage);
     }
     #endregion
     try
     {
         Resource.CoreMessage = new CoreResource("ProductItemMap");
         //讀取excel裡賣弄的內容
         NPOI4ExcelHelper fm = new NPOI4ExcelHelper(filePath);
         _ProductItemMapMgr = new ProductItemMapMgr(connectionString);
         DataTable dt = fm.SheetData();
         if (dt.Rows.Count > 0)
         {
             for (int i = 0; i < dt.Rows.Count; i++)
             {
                 if (_ProductItemMapMgr.Selrepeat(dt.Rows[i]["外站商品編號"].ToString()) > 0)
                 {//編輯
                     try
                     {
                         m.channel_detail_id = dt.Rows[i]["外站商品編號"].ToString();
                         m.product_name = dt.Rows[i]["外站商品名稱"].ToString();
                         m.product_price = Int32.Parse(dt.Rows[i]["外站商品售價"].ToString());
                         _ProductItemMapMgr.UpdatePIM(m);
                         Upd++;
                     }
                     catch (Exception ex)
                     {
                         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                         log.Error(logMessage);
                     }
                 }
                 else
                 {//保存 未知price_master_id
                     try
                     {
                         m.channel_id = UInt32.Parse(channel_id.ToString());
                         m.channel_detail_id = dt.Rows[i]["外站商品編號"].ToString();
                         m.product_name = dt.Rows[i]["外站商品名稱"].ToString();
                         try
                         {
                             m.product_id = UInt32.Parse(dt.Rows[i]["商品編號(5碼)"].ToString());
                         }
                         catch
                         {
                             m.product_id = 0;
                         }
                         try
                         {
                             m.item_id = UInt32.Parse(dt.Rows[i]["商品細項編號(6碼)"].ToString());
                         }
                         catch
                         {
                             m.item_id = 0;
                         }
                         //m.product_cost = Int32.Parse(dt.Rows[i]["外站商品成本"].ToString());
                         try
                         {
                             m.product_price = Int32.Parse(dt.Rows[i]["外站商品售價"].ToString());
                         }
                         catch
                         {
                             m.product_price = 0;
                         }
                         _ProductItemMapMgr.Save(m);
                         Ins++;
                     }
                     catch (Exception ex)
                     {
                         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                         log.Error(logMessage);
                     }
                 }
             }
             return "{success:true,msg:'新增成功" + Ins + "條,更新成功" + Upd + "條'}";
         }
         else
             return "{success:false,msg:'Excel no data'}";
     }
     catch (Exception ex)
     {
         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
         log.Error(logMessage);
         return "{success:false,msg:'" + Resources.ProductItemMap.IMPORT_FAIL + "'}";
     }
     //return "{success:true,msg:'" + string.Format(Resources.ProductItemMap.IMPORT_SUCCESS_TOTAL, Ins+Upd) + "'}";
 } 
Пример #5
0
        public HttpResponseBase HuiruPiciAddwuliufei()
        {
            string json = string.Empty;//json字符串
            string shipment = Request.Params["shipment"].ToString();
            //int total = 0;
            try
            {
                if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
                {
                    HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
                    FileManagement fileManagement = new FileManagement();//實例化 FileManagement
                    string fileLastName = excelFile.FileName;
                    string newExcelName = Server.MapPath(excelPath) + "PiCiAddWuliufei" + fileManagement.NewFileName(excelFile.FileName);//處理文件名,獲取新的文件名
                    excelFile.SaveAs(newExcelName);//上傳文件
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
                    dt = helper.SheetData();
                    DataRow[] dr = dt.Select(); //定义一个DataRow数组,读取ds里面所有行
                    int rowsnum = dt.Rows.Count;
                    if (rowsnum > 0)//判斷是否是這個表
                    {
                        DeliverMasterQuery dmQuery = new DeliverMasterQuery();
                        StringBuilder str = new StringBuilder();
                        DataTable dtMaster = new DataTable();
                        DataTable ExcelDt = new DataTable();
                        string filenameExcel = string.Empty;
                        ExcelDt.Columns.Add("訂單編號", typeof(String));
                        ExcelDt.Columns.Add("物流單號", typeof(String));
                        ExcelDt.Columns.Add("物流費", typeof(String));
                        ExcelDt.Columns.Add("應收帳款", typeof(String));
                        ExcelDt.Columns.Add("異常信息", typeof(String));
                        int i = 0; int j = 0; int x = 0; string y = string.Empty;
                        int successcount = 0;
                        int failcount = 0;
                        string create_dtim = CommonFunction.DateTimeToString(DateTime.Now);       //創建時間
                        int create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
                        _DeliverMsterMgr = new DeliverMasterMgr(mySqlConnectionString);
                        #region 循環excel表中的數據 并判斷是否滿足條件和失敗的個數
                        for (int z = 0; z < dr.Length; z++)
                        {
                            x = Convert.ToInt32(dr[z][0].ToString());//訂單編號
                            y = dr[z][1].ToString();//托運單號
                            i = Convert.ToInt32(dr[z][2].ToString());//運送金額
                            j = Convert.ToInt32(dr[z][3].ToString());//代收金額--也就是購物應該付款總金額
                            dmQuery.delivery_code = y;//托運單號

                            dtMaster = _DeliverMsterMgr.GetMessageByDeliveryCode(dmQuery);
                            string errorstring = string.Empty;
                            bool behavior = true;
                            if (dtMaster.Rows.Count <= 0)
                            {
                                errorstring = "物流單號不存在";
                                behavior = false;
                            }
                            else if (Convert.ToInt32(dtMaster.Rows[0]["delivery_store"]) != Convert.ToInt32(shipment))//如果物流方式不對應
                            {
                                errorstring = "物流廠商不相符,此物流單對應物流為:" + dtMaster.Rows[0]["parameterName"].ToString();
                                behavior = false;
                            }
                            //else if (y != dtMaster.Rows[0]["delivery_code"].ToString())
                            //{
                            //    errorstring = "物流單號不相符";
                            //    behavior = false;
                            //}
                            else if (dtMaster.Rows[0]["order_id"].ToString() != x.ToString())
                            {
                                errorstring = "定單編號不相符,此物流單對應的定單編號為:" + dtMaster.Rows[0]["order_id"].ToString();
                                behavior = false;
                            }
                            else if (dtMaster.Rows.Count > 1)
                            {
                                errorstring = "物流單號重複";
                                behavior = false;
                            }
                            //10表示黑貓貨到付款     order_amount購物應付總金額(加運費,扣除扺用紅利等金額)
                            else if (Convert.ToInt32(dtMaster.Rows[0]["delivery_store"]) == 10 && j != Convert.ToInt32(dtMaster.Rows[0]["order_amount"]))
                            {
                                errorstring = "應收帳款金額不符";
                                behavior = false;
                            }
                            //4表示已出貨 9 表示待取貨
                            else if (Convert.ToInt32(dtMaster.Rows[0]["order_status"]) != 4 && Convert.ToInt32(dtMaster.Rows[0]["order_status"]) != 9)
                            {
                                _ptersrc = new ParameterMgr(mySqlConnectionString);
                                int types = Convert.ToInt32(dtMaster.Rows[0]["order_status"]);
                                string endresult = _ptersrc.GetOrderStatus(types);
                                errorstring = "出貨狀態異常:出貨狀態為" + endresult;
                                behavior = false;
                            }
                            if (behavior == true)//如果數據不存在這些問題   如果failcount大於0就表示匯入信息有不正確的
                            {
                                successcount = successcount + 1;
                                //DataRow Execldr = ExcelDt.NewRow();
                                //Execldr[0] = x;
                                //Execldr[1] = y;
                                //Execldr[2] = i;
                                //Execldr[3] = dtMaster.Rows[0]["order_amount"];
                                //Execldr[4] = "數據正常";
                                //ExcelDt.Rows.Add(Execldr);
                                str.AppendFormat(" set sql_safe_updates = 0;update deliver_master set delivery_freight_cost='{0}',creator='{1}',modified='{2}' where delivery_code='{3}' ;set sql_safe_updates = 1;", i, create_user, create_dtim, y);
                            }
                            else
                            {
                                failcount = failcount + 1;
                                DataRow Execldr = ExcelDt.NewRow();
                                Execldr[0] = x;
                                Execldr[1] = y;
                                Execldr[2] = i;
                                Execldr[3] = j;//dtMaster.Rows[0]["order_amount"];
                                Execldr[4] = errorstring;
                                ExcelDt.Rows.Add(Execldr);
                                //此次上傳資料有異請下載差異檔
                            }
                        }
                        #endregion

                        #region 判斷失敗個數 成功個數 當失敗個數大於0時 直接匯出excel
                        if (failcount > 0)//存在失敗的情況 直接匯出數據
                        {
                            string fileName = DateTime.Now.ToString("匯出批次上傳物流費不規則數據_yyyyMMddHHmm") + ".xls";
                            MemoryStream ms = ExcelHelperXhf.ExportDT(ExcelDt, "匯出批次上傳物流費不規則數據");
                            Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
                            Response.BinaryWrite(ms.ToArray());
                            return this.Response;
                        }
                        else if (failcount == 0 && successcount > 0)
                        {
                            if (_DeliverMsterMgr.Updatedeliveryfreightcost(str) > 0)
                            {
                                json = "{success:true,total:" + successcount + ",msg:\"" + "匯入成功" + "\"}";
                                this.Response.Clear();
                                this.Response.Write(json);
                                this.Response.End();
                                return this.Response;
                            }
                            else
                            {
                                json = "{success:false}";
                                this.Response.Clear();
                                this.Response.Write(json);
                                this.Response.End();
                                return this.Response;
                            }
                        }
                        else
                        {
                            json = "{success:true,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
                            this.Response.Clear();
                            this.Response.Write(json);
                            this.Response.End();
                            return this.Response;
                        }
                        #endregion
                    }
                    else
                    {
                        json = "{success:true,total:0,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
                        this.Response.Clear();
                        this.Response.Write(json);
                        this.Response.End();
                        return this.Response;
                    }
                }
                else//當直接點擊時會產生,
                {
                    json = "{success:true,msg:\"" + "請匯入批次上傳物流費表" + "\"}";
                    this.Response.Clear();
                    this.Response.Write(json);
                    this.Response.End();
                    return this.Response;
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,msg:\"" + ex.ToString() + "\"}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Пример #6
0
        /// <summary>
        /// 讀取Excel文件
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="channel_id"></param>
        /// <param name="fExtension"></param>
        /// <param name="isHeaderError"></param>
        /// <param name="errorPm"></param>
        /// <returns></returns>
        public List<ProductItemMapCustom> ReadFile(string filePath, int channel_id, string fExtension, ref bool isHeaderError, List<ProductItemMapCustom> errorPm)
        {
            try
            {
                NPOI4ExcelHelper fm = new NPOI4ExcelHelper(filePath);
                DataTable dt = fm.SheetData();
                if (dt == null) { return null; }

                List<ProductItemMapCustom> result = new List<ProductItemMapCustom>();

                Regex RegxProductId = new Regex("^\\d{5}$");
                Regex RegxItemId = new Regex("^\\d{6}$");
                Regex RegxMoney = new Regex("^\\d{1,9}$");

                for (int i = 0, j = dt.Rows.Count; i < j; i++)
                {
                    bool bl = true;
                    bool el = true;
                    bool isNull = false;
                    bool Pro_id = false;//記錄商品編號是否為空
                    bool Itm_id = false;//記錄商品細項編號是否為空
                    PriceMaster prm = new PriceMaster();
                    ProductItemMapCustom pm = new ProductItemMapCustom();
                    pm.channel_id = Convert.ToUInt32(channel_id);
                    for (int m = 0, n = dt.Columns.Count; m < n; m++)
                    {
                        string valStr = dt.Rows[i][m].ToString();
                        switch (dt.Columns[m].ToString().Trim())
                        {
                            case "商品編號(5碼)":
                                if (!string.IsNullOrEmpty(valStr))
                                {
                                    uint _ProductId = 0;
                                    uint.TryParse(valStr ?? "0", out _ProductId);
                                    pm.product_id = _ProductId;
                                    prm.product_id = _ProductId;
                                    if (!RegxProductId.IsMatch(pm.product_id.ToString()))
                                    {
                                        bl = false;
                                    }
                                }
                                else
                                {
                                    Pro_id = true;
                                    isNull = true;
                                    break;
                                }
                                break;
                            case "商品細項編號(6碼)":
                                if (!string.IsNullOrEmpty(valStr))
                                {
                                    if (valStr.Split(',').Length > 1)
                                    {
                                        for (int k = 0; k < valStr.Split(',').Length; k++)
                                        {
                                            if (!RegxItemId.IsMatch(valStr.Split(',')[k]))
                                            {
                                                bl = false;
                                            }
                                        }
                                        pm.group_item_id = CommonFunction.Rank_ItemId(valStr);
                                    }
                                    else
                                    {
                                        uint _itemId = 0;
                                        uint.TryParse(valStr ?? "0", out _itemId);
                                        pm.item_id = _itemId;
                                        pm.group_item_id = _itemId.ToString();
                                        if (!RegxItemId.IsMatch(pm.item_id.ToString()))
                                        {
                                            bl = false;
                                        }
                                    }
                                }
                                else
                                {
                                    Itm_id = true;
                                    isNull = true;
                                    break;
                                    //bl = false;
                                };
                                break;
                            case "外站商品名稱":
                                if (!string.IsNullOrEmpty(valStr))
                                {
                                    pm.product_name = valStr;
                                }
                                else
                                {
                                    bl = false;
                                };
                                break;
                            case "外站商品編號":
                                if (!string.IsNullOrEmpty(valStr))
                                {
                                    pm.channel_detail_id = valStr;
                                }
                                else
                                {
                                    bl = false;
                                }; break;
                            case "外站商品成本":
                                if (!string.IsNullOrEmpty(valStr))
                                {
                                    if (RegxMoney.IsMatch(valStr))
                                    {
                                        pm.product_cost = int.Parse(valStr);
                                    }
                                    else
                                    {
                                        bl = false;
                                    }
                                }
                                else
                                {
                                    bl = false;
                                }
                                ; break;
                            case "外站商品售價":
                                if (!string.IsNullOrEmpty(valStr))
                                {
                                    if (RegxMoney.IsMatch(valStr))
                                    {
                                        pm.product_price = int.Parse(valStr);
                                    }
                                    else
                                    {
                                        bl = false;
                                    }
                                }
                                else
                                {
                                    bl = false;
                                }
                                ; break;
                            case "組合中之數量(0為照組合中之設定)":
                                if (!string.IsNullOrEmpty(valStr))
                                {
                                    if (RegxMoney.IsMatch(valStr))
                                    {
                                        pm.set_num = uint.Parse(valStr);
                                    }
                                    else
                                    {
                                        bl = false;
                                    }
                                }
                                else
                                {
                                    bl = false;
                                } break;
                            case "user_email"://郵箱
                                if (!string.IsNullOrEmpty(valStr))
                                {
                                    if (_callerMgr.Login(valStr) != null)
                                    {
                                        prm.user_id = uint.Parse(_callerMgr.Login(valStr).user_id.ToString());
                                    }
                                }
                                break;
                            case "site_id"://站臺
                                if (!string.IsNullOrEmpty(valStr))
                                {
                                    if (RegxMoney.IsMatch(valStr))
                                    {
                                        prm.site_id = uint.Parse(valStr);
                                    }
                                    else
                                    {
                                        bl = false;
                                    }
                                }
                                else
                                {
                                    bl = false;
                                }
                                break;
                            case "user_level"://站臺等級
                                if (!string.IsNullOrEmpty(valStr))
                                {
                                    if (RegxMoney.IsMatch(valStr))
                                    {
                                        prm.user_level = uint.Parse(valStr);
                                    }
                                    else
                                    {
                                        bl = false;
                                    }
                                }
                                else
                                {
                                    bl = false;
                                }
                                break;
                            default:
                                el = false;
                                break;
                        }
                        //if (isNull)
                        //{
                        //    break;
                        //}
                    }

                    //判斷Excel表頭格式
                    if (!el)
                    {
                        isHeaderError = true;
                        break;
                    }
                    else
                    {
                        isHeaderError = false;
                    }

                    //若單一商品可以無商品編號,若固定組合可以無商品細項編號。其餘類型出現為空數據,則此行記錄無效,不做處理
                    if (Itm_id)
                    {
                        if (_productItemMapDao.CombinationQuery(pm).FirstOrDefault() != null && _productItemMapDao.CombinationQuery(pm).FirstOrDefault().combination == 2)
                        {
                            List<ProductComboMap> pMc = _productItemMapDao.QueryItemId(pm.product_id);
                            if (pMc != null && pMc.Where(e => e.product_spec == 0).Count() == pMc.Count())
                            {
                                string strItem = "";
                                for (int Itemi = 0, Itemj = pMc.Count(); Itemi < Itemj; Itemi++)
                                {
                                    if (Itemi > 0)
                                    {
                                        strItem += ",";
                                    }
                                    strItem += pMc[Itemi].item_id;
                                }
                                
                                pm.group_item_id = CommonFunction.Rank_ItemId(strItem);
                                isNull = false;
                            }
                        }
                    }
                    //單一商品邏輯 add by hufeng0813w 2013/12/23
                    if (Pro_id)
                    {
                        ProductItem pIm = new ProductItem();
                        pIm.Item_Id = uint.Parse(pm.item_id.ToString());
                        //pm.product_id = _productItemDao.Query(pIm).FirstOrDefault().Product_Id;
                        ProductItem itemResult = _productItemDao.Query(pIm).FirstOrDefault();
                        if (itemResult != null)
                        {
                            prm.product_id = pm.product_id = itemResult.Product_Id;
                            ProductMapCustom mapcusResult = _productItemMapDao.CombinationQuery(pm).FirstOrDefault();
                            if (mapcusResult != null && mapcusResult.combination == 1)
                            {
                                isNull = false;
                            }
                        }

                    }
                    if (isNull)
                    {
                        continue;
                    }
                    //獲取price_master_id
                    if (prm.product_id != 0)
                    {
                        pm.price_master_id = _priceMasterMgr.QueryPriceMasterId(prm);
                    }
                    else
                    {
                        bl = false;
                    }

                    //判斷內容格式
                    if (!bl)
                    {
                        pm.msg = Resource.CoreMessage.GetResource("ERROR_FORMAT");
                    }
                    else
                    {
                        ProductMapCustom map = _productItemMapDao.CombinationQuery(pm).FirstOrDefault();
                        if (map != null)
                        {
                            uint Combination = map.combination;
                            if (Combination != 2)
                            {
                                ProductItem pi = new ProductItem();
                                pi.Item_Id = uint.Parse(pm.item_id.ToString());
                                if (_productItemMgr.Query(pi).Count == 0)
                                {
                                    pm.msg = Resource.CoreMessage.GetResource("ITEMID_ID_NOT_EXISTS");
                                    bl = false;
                                }
                                else
                                {
                                    if (_productItemMapDao.Exist(pm) > 0)
                                    {
                                        pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
                                        bl = false;
                                    }
                                    else
                                    {
                                        ProductItemMapCustom existItem = result.Where(m => m.item_id == pm.item_id).FirstOrDefault();
                                        if (existItem != null)
                                        {
                                            pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
                                            bl = false;
                                        }
                                    }
                                }
                            }
                            else
                            {
                                #region
                                for (int l = 0; l < pm.group_item_id.Split(',').Length; l++)
                                {
                                    if (_productItemMapDao.Comb_Compare(pm.product_id, uint.Parse(pm.group_item_id.Split(',')[l])) == 0)
                                    {
                                        pm.msg = Resource.CoreMessage.GetResource("ITEMID_ID_NOT_COMBINATION");
                                        bl = false;
                                    }
                                    else
                                    {
                                        #region
                                        if (l == 0)
                                        {
                                            if (_productItemMapDao.Comb_Exist(pm) > 0)
                                            {
                                                pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
                                                bl = false;
                                            }
                                            else
                                            {
                                                ProductItemMapCustom existItem = result.Where(m => m.group_item_id == pm.group_item_id).FirstOrDefault();
                                                if (existItem != null)
                                                {
                                                    pm.msg = Resource.CoreMessage.GetResource("COMPARE_EXISTS");
                                                    bl = false;
                                                }
                                            }
                                        }
                                        #endregion
                                    }
                                }
                                #endregion
                            }
                        }
                        else
                        {
                            bl = false;
                            pm.msg = Resource.CoreMessage.GetResource("PRODUCT_NOT_EXIST");
                        }
                    }

                    if (!bl)
                    {
                        errorPm.Add(pm);
                    }
                    else
                    {
                        result.Add(pm);
                    }
                }
                return result; // _access.getObjByTable<ProductItemMap>(dt);
            }
            catch (Exception ex)
            {
                throw new Exception("ProductItemMapExcelMgr-->ReadFile-->" + ex.Message, ex);
            }
        } 
Пример #7
0
        public void OutExcel2()
        {
            try
            {
                FileManagement fileManagement = new FileManagement();//實例化 FileManagement 
                HttpPostedFileBase excelFile = Request.Files[0];//獲取文件流
                string fileLastName = excelFile.FileName.Substring((excelFile.FileName).LastIndexOf('.')).ToLower().Trim();
                if (fileLastName.Equals(".xlsx") || fileLastName.Equals(".xls"))
                {
                    string newExcelName = Server.MapPath(excelPath) + fileManagement.NewFileName(excelFile.FileName);//處理文件名,獲取新的文件名 
                    excelFile.SaveAs(newExcelName);//上傳文件 
                    //string fileName = DateTime.Now.ToString("6727_yyyyMMddHHmm") + ".xls";
                    string fileName = DateTime.Now.ToString("6727_yyyyMMddHHmm") + ".csv";
                    
                    Stream stream = excelFile.InputStream;
                    NPOI4ExcelHelper npoiHelper = new NPOI4ExcelHelper(newExcelName);
                    DataTable dtMain = npoiHelper.SheetData();
                    DataTable new_Table = new DataTable();
                    #region new_Table表頭
                    new_Table.Columns.Add(new DataColumn("廠商流水號"));
                    new_Table.Columns.Add(new DataColumn("網站別流水號"));
                    new_Table.Columns.Add(new DataColumn("廠商康迅商品代碼"));
                    new_Table.Columns.Add(new DataColumn("商品名稱"));
                    new_Table.Columns.Add(new DataColumn("付款期數"));
                    new_Table.Columns.Add(new DataColumn("商品售價"));
                    #endregion
                    for (int i = 0; i < dtMain.Rows.Count; i++)
                    {
                        DataRow dr1 = new_Table.NewRow();
                        dr1["廠商流水號"] = "6727";
                        dr1["網站別流水號"] = "6037";
                        dr1["廠商康迅商品代碼"] = dtMain.Rows[i]["外站商品編號"].ToString();
                        dr1["商品名稱"] = dtMain.Rows[i]["外站商品名稱"].ToString();
                        dr1["付款期數"] = "0";
                        dr1["商品售價"] = dtMain.Rows[i]["外站商品售價"].ToString();
                        new_Table.Rows.Add(dr1);
                    }
                    //MemoryStream ms = ExcelHelperXhf.ExportDT(new_Table, "");
                    //Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(fileName));
                    //Response.BinaryWrite(ms.ToArray());

                    StringWriter sw = ExcelHelperXhf.SetCsvFromData(new_Table, fileName);
                    Response.Clear();
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(fileName));
                    Response.ContentType = "application/ms-excel";
                    Response.ContentEncoding = Encoding.Default;
                    Response.Write(sw);
                    Response.End();
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
            }
        }
Пример #8
0
        public HttpResponseBase IplasUploadExcel()
        {
            string newName = string.Empty;
            string json = string.Empty;
            List<IplasQuery> store = new List<IplasQuery>();
            try
            {

                DTIplasExcel.Clear();
                DTIplasExcel.Columns.Clear();

                DTIplasExcel.Columns.Add("商品細項編號", typeof(String));
                DTIplasExcel.Columns.Add("主料位", typeof(String));
                DTIplasExcel.Columns.Add("不能匯入的原因", typeof(String));
                int result = 0;
                int count = 0;//總匯入數
                int errorcount = 0;//數據異常個數
                int comtentcount = 0;//內容不符合格式
                int create_user = (Session["caller"] as Caller).user_id;
                int item_idcount = 0;//商品細項編號不存在
                int item_id_have_locid = 0;//商品細項編號已存在主料位
                int locid_lock = 0;//商品料位已經被鎖定
                StringBuilder strsql = new StringBuilder();
                if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
                {
                    HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
                    //FileManagement fileManagement = new FileManagement();
                    newName = Server.MapPath(excelPath) + excelFile.FileName;
                    excelFile.SaveAs(newName);
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
                    dt = helper.SheetData();
                    if (dt.Rows.Count > 0)
                    {
                        _IiplasMgr = new IplasMgr(mySqlConnectionString);
                        #region 循環Excel的數據
                        Iloc ic = new BLL.gigade.Model.Iloc();
                        Iplas ips = new Iplas();
                        int i = 0;
                        foreach (DataRow dr in dt.Rows)
                        {
                            i++;
                            try
                            {
                                if (!string.IsNullOrEmpty(dr[0].ToString()) && Regex.IsMatch(dr[0].ToString(), @"^\d{6}$") && !string.IsNullOrEmpty(dr[1].ToString()) && Regex.IsMatch(dr[1].ToString(), @"^[A-Z]{2}\d{3}[A-Z]\d{2}$"))
                                {
                                    ic.loc_id = dr[1].ToString();
                                    ic.lsta_id = "F";
                                    ic.lcat_id = "S";
                                    ic.create_dtim = DateTime.Now;
                                    ic.change_dtim = DateTime.Now;
                                    ic.create_user = create_user;
                                    ic.change_user = create_user;
                                    ic.loc_status = 1;
                                    ips.item_id = Convert.ToUInt32(dr[0]);
                                    ips.loc_id = dr[1].ToString();
                                    ips.change_user = create_user;
                                    ips.create_user = create_user;
                                    ips.create_dtim = DateTime.Now;
                                    ips.change_dtim = DateTime.Now;
                                    //根據商品編號查看是否存在主料位
                                    int item_id_exsit = _IiplasMgr.YesOrNoExist(Convert.ToInt32(dr[0]));//檢查item_id是否存在主料位
                                    int loc_id_exsit = _IiplasMgr.YesOrNoLocIdExsit(dr[1].ToString());//判斷料位是否存在
                                    int loc_id_lock = _IiplasMgr.GetLocCount(ic);//判斷料位是否鎖定
                                    if (_IiplasMgr.IsTrue(ips) == "false")//首先判斷item_id是否存在
                                    {
                                        DataRow drtwo = DTIplasExcel.NewRow();
                                        drtwo[0] = dr[0].ToString();
                                        drtwo[1] = dr[1].ToString();
                                        drtwo[2] = "商品細項編號不存在";
                                        DTIplasExcel.Rows.Add(drtwo);
                                        errorcount++;
                                        item_idcount++;
                                        continue;
                                    }
                                    else//如果存在item_id
                                    {
                                        if (item_id_exsit > 0)//表示已經存在主料位
                                        {
                                            DataRow drtwo = DTIplasExcel.NewRow();
                                            drtwo[0] = dr[0].ToString();
                                            drtwo[1] = dr[1].ToString();
                                            drtwo[2] = "商品細項編號已存在主料位";
                                            DTIplasExcel.Rows.Add(drtwo);
                                            errorcount++;
                                            item_id_have_locid++;
                                            continue;
                                        }
                                        #region
                                        if (loc_id_exsit > 0)//如果料位存在
                                        {
                                            if (loc_id_lock > 0)//如果料位存在並且沒有被鎖定
                                            {

                                                strsql.AppendFormat("Insert into iplas (dc_id,whse_id,loc_id,change_dtim,change_user,create_dtim,create_user,lcus_id,luis_id,item_id,prdd_id,loc_rpln_lvl_uoi,loc_stor_cse_cap,ptwy_anch,flthru_anch,pwy_loc_cntl) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}');", ips.dc_id, ips.whse_id, ips.loc_id.ToString().ToUpper(), CommonFunction.DateTimeToString(ips.change_dtim), ips.change_user, CommonFunction.DateTimeToString(ips.create_dtim), ips.create_user, ips.lcus_id, ips.luis_id, ips.item_id, ips.prdd_id, ips.loc_rpln_lvl_uoi, ips.loc_stor_cse_cap, ips.ptwy_anch, ips.flthru_anch, ips.pwy_loc_cntl);//插入數據到表iplas表中
                                                strsql.AppendFormat(@" set sql_safe_updates = 0; update iloc set lsta_id='{0}',change_user='******',change_dtim='{2}' where loc_id='{3}';set sql_safe_updates = 1; ", "A", ips.change_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ips.change_dtim), ips.loc_id.ToString().ToUpper());
                                                count++;
                                            }
                                            else
                                            {
                                                DataRow drtwo = DTIplasExcel.NewRow();
                                                drtwo[0] = dr[0].ToString();
                                                drtwo[1] = dr[1].ToString();
                                                drtwo[2] = "主料位已經被鎖定";
                                                DTIplasExcel.Rows.Add(drtwo);
                                                errorcount++;
                                                locid_lock++;
                                                continue;
                                            }
                                        }
                                        else//料位不存在
                                        {
                                            strsql.AppendFormat(@"insert into iloc(dc_id,whse_id,loc_id,llts_id,bkfill_loc,ldes_id,
                                             ldim_id,x_coord,y_coord,z_coord,bkfill_x_coord,bkfill_y_coord,
                                             bkfill_z_coord,lsta_id,sel_stk_pos,sel_seq_loc,sel_pos_hgt,rsv_stk_pos,
                                             rsv_pos_hgt,stk_lmt,stk_pos_wid,lev,lhnd_id,ldsp_id,
                                             create_user,create_dtim,comingle_allow,change_user,change_dtim,lcat_id,
                                             space_remain,max_loc_wgt,loc_status,stk_pos_dep
                    ) values ('{0}','{1}','{2}','{3}','{4}','{5}',
                              '{6}','{7}','{8}','{9}','{10}','{11}',
                              '{12}','{13}','{14}','{15}','{16}','{17}',
                              '{18}','{19}','{20}','{21}','{22}','{23}',
                              '{24}','{25}','{26}','{27}','{28}','{29}',
                              '{30}','{31}','{32}','{33}');",
                                 ic.dc_id, ic.whse_id, ic.loc_id, ic.llts_id, ic.bkfill_loc, ic.ldes_id,
                                 ic.ldim_id, ic.x_coord, ic.y_coord, ic.z_coord, ic.bkfill_x_coord, ic.bkfill_y_coord,
                                 ic.bkfill_z_coord, ic.lsta_id, ic.sel_stk_pos, ic.sel_seq_loc, ic.sel_pos_hgt, ic.rsv_stk_pos,
                                 ic.rsv_pos_hgt, ic.stk_lmt, ic.stk_pos_wid, ic.lev, ic.lhnd_id, ic.ldsp_id,
                                 ic.create_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ic.create_dtim), ic.comingle_allow, ic.change_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ic.change_dtim), ic.lcat_id,
                                 ic.space_remain, ic.max_loc_wgt, ic.loc_status, ic.stk_pos_dep
                                 );
                                            strsql.AppendFormat("Insert into iplas (dc_id,whse_id,loc_id,change_dtim,change_user,create_dtim,create_user,lcus_id,luis_id,item_id,prdd_id,loc_rpln_lvl_uoi,loc_stor_cse_cap,ptwy_anch,flthru_anch,pwy_loc_cntl) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}');", ips.dc_id, ips.whse_id, ips.loc_id.ToString().ToUpper(), CommonFunction.DateTimeToString(ips.change_dtim), ips.change_user, CommonFunction.DateTimeToString(ips.create_dtim), ips.create_user, ips.lcus_id, ips.luis_id, ips.item_id, ips.prdd_id, ips.loc_rpln_lvl_uoi, ips.loc_stor_cse_cap, ips.ptwy_anch, ips.flthru_anch, ips.pwy_loc_cntl);//插入數據到表iplas表中
                                            strsql.AppendFormat(@" set sql_safe_updates = 0; update iloc set lsta_id='{0}',change_user='******',change_dtim='{2}' where loc_id='{3}';set sql_safe_updates = 1; ", "A", ips.change_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ips.change_dtim), ips.loc_id.ToString().ToUpper());
                                            count++;
                                        }
                                        #endregion
                                    }
                                }
                                else
                                {
                                    DataRow drtwo = DTIplasExcel.NewRow();
                                    drtwo[0] = dr[0].ToString();
                                    drtwo[1] = dr[1].ToString();
                                    drtwo[2] = "商品細項編號或者主料位不符合格式";
                                    DTIplasExcel.Rows.Add(drtwo);
                                    errorcount++;
                                    comtentcount++;
                                    continue;
                                }
                            }
                            catch
                            {
                                DataRow drtwo = DTIplasExcel.NewRow();
                                drtwo[0] = dr[0].ToString();
                                drtwo[1] = dr[1].ToString();
                                drtwo[2] = "數據異常";
                                DTIplasExcel.Rows.Add(drtwo);
                                errorcount++;
                                continue;
                            }
                        }
                        #endregion
                        if (strsql.ToString().Trim() != "")
                        {
                            result = _IiplasMgr.ExcelImportIplas(strsql.ToString());
                            if (result > 0)
                            {
                                json = "{success:true,total:" + count + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
                            }
                            else
                            {
                                json = "{success:false}";
                            }
                        }
                        else
                        {
                            json = "{success:true,total:" + 0 + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
                        }
                    }
                    else
                    {
                        json = "{success:true,total:" + 0 + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
                    }
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,data:" + "" + "}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Пример #9
0
        //商品指定主料位-互搬
        public HttpResponseBase IplasUploadExcelEnter()
        {
            string newName = string.Empty;
            string json = string.Empty;
            List<IplasQuery> store = new List<IplasQuery>();
            try
            {

                DTIplasEnterExcel.Clear();
                DTIplasEnterExcel.Columns.Clear();

                DTIplasEnterExcel.Columns.Add("商品細項編號", typeof(String));
                DTIplasEnterExcel.Columns.Add("原料位", typeof(String));
                DTIplasEnterExcel.Columns.Add("新料位", typeof(String));
                DTIplasEnterExcel.Columns.Add("不能搬移的原因", typeof(String));
                int result = 0;
                int count = 0;//總匯入數
                int errorcount = 0;//數據異常個數
                int comtentcount = 0;//內容不符合格式
                int create_user = (Session["caller"] as Caller).user_id;
                int item_idcount = 0;//商品細項編號不存在
                int item_id_have_locid = 0;//商品細項編號已存在主料位
                int locid_lock = 0;//商品料位已經被鎖定
                StringBuilder strsql = new StringBuilder();
                if (Request.Files["IplasImportExcelFile"] != null && Request.Files["IplasImportExcelFile"].ContentLength > 0)
                {
                    HttpPostedFileBase excelFile = Request.Files["IplasImportExcelFile"];
                    //FileManagement fileManagement = new FileManagement();
                    newName = Server.MapPath(excelPath) + excelFile.FileName;
                    excelFile.SaveAs(newName);
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
                    dt = helper.SheetData();

                    if (dt.Rows.Count > 0)
                    {
                        _IiplasMgr = new IplasMgr(mySqlConnectionString);



                        #region 測試
                        #region 循環Excel的數據
                        Iloc ic = new BLL.gigade.Model.Iloc();
                        Iplas ips = new Iplas();
                        int i = 0;
                        if (dt.Columns.Count < 3)
                        {
                            DataRow drtwo = DTIplasEnterExcel.NewRow();
                            drtwo[0] = "這個是商品細項編號";
                            drtwo[1] = "這個是商品原料位";
                            drtwo[2] = "這個是商品新料位";
                            drtwo[3] = "請匯入足夠的列數";
                            DTIplasEnterExcel.Rows.Add(drtwo);
                            errorcount++;
                        }
                        else
                        {
                            foreach (DataRow dr in dt.Rows)
                            {
                                if (string.IsNullOrEmpty(dr[0].ToString()) && string.IsNullOrEmpty(dr[1].ToString()) && string.IsNullOrEmpty(dr[2].ToString()))
                                {
                                    continue;
                                }

                                i++;
                                try
                                {
                                    if (!string.IsNullOrEmpty(dr[0].ToString()) && Regex.IsMatch(dr[0].ToString(), @"^\d{6}$") && !string.IsNullOrEmpty(dr[1].ToString()) && Regex.IsMatch(dr[1].ToString(), @"^[A-Z]{2}\d{3}[A-Z]\d{2}$") && !string.IsNullOrEmpty(dr[2].ToString()) && Regex.IsMatch(dr[2].ToString(), @"^[A-Z]{2}\d{3}[A-Z]\d{2}$"))
                                    {
                                        ic.loc_id = dr[2].ToString();
                                        //ic.lsta_id = "F";
                                        //ic.lcat_id = "S";
                                        //ic.create_dtim = DateTime.Now;
                                        //ic.change_dtim = DateTime.Now;
                                        //ic.create_user = 2;
                                        //ic.change_user = 2;
                                        //ic.loc_status = 1;
                                        ips.item_id = Convert.ToUInt32(dr[0]);
                                        ips.loc_id = dr[1].ToString();


                                        //根據商品編號查看是否存在主料位
                                        int item_id_exsit = _IiplasMgr.YesOrNoExist(Convert.ToInt32(dr[0]));//檢查item_id是否存在
                                        int loc_id_exsit = _IiplasMgr.YesOrNoLocIdExsit(dr[1].ToString());//判斷原料位是否存在
                                        int item_loc_id = _IiplasMgr.YesOrNoLocIdExsit(Convert.ToInt32(dr[0]), dr[1].ToString());//判斷原料位是否為該商品的主料位
                                        int New_loc_id_exsit = _IiplasMgr.YesOrNoLocIdExsit(dr[2].ToString());//判斷新料位是否存在
                                        int loc_id_lock = _IiplasMgr.GetLocCount(ic);//判斷新料位是否鎖定

                                        if (_IiplasMgr.IsTrue(ips) == "false")//首先判斷item_id是否存在
                                        {
                                            DataRow drtwo = DTIplasEnterExcel.NewRow();
                                            drtwo[0] = dr[0].ToString();
                                            drtwo[1] = dr[1].ToString();
                                            drtwo[2] = dr[2].ToString();
                                            drtwo[3] = "商品細項編號不存在";
                                            DTIplasEnterExcel.Rows.Add(drtwo);
                                            errorcount++;
                                            item_idcount++;
                                            continue;
                                        }
                                        else//如果存在item_id
                                        {
                                            if (loc_id_exsit <= 0)//表示原料為不存在料位//------------------------------
                                            {
                                                DataRow drtwo = DTIplasEnterExcel.NewRow();
                                                drtwo[0] = dr[0].ToString();
                                                drtwo[1] = dr[1].ToString();
                                                drtwo[2] = dr[2].ToString();
                                                drtwo[3] = "商品原料位不存在";
                                                DTIplasEnterExcel.Rows.Add(drtwo);
                                                errorcount++;
                                                locid_lock++;
                                                continue;
                                            }
                                            if (item_loc_id <= 0)//表示原料位不是此商品的原料位//------------------------------
                                            {
                                                DataRow drtwo = DTIplasEnterExcel.NewRow();
                                                drtwo[0] = dr[0].ToString();
                                                drtwo[1] = dr[1].ToString();
                                                drtwo[2] = dr[2].ToString();
                                                drtwo[3] = "原料位不是此商品的料位";
                                                DTIplasEnterExcel.Rows.Add(drtwo);
                                                errorcount++;
                                                locid_lock++;
                                                continue;
                                            }
                                            if (New_loc_id_exsit <= 0)//表示新料位不存在//------------------------------
                                            {
                                                DataRow drtwo = DTIplasEnterExcel.NewRow();
                                                drtwo[0] = dr[0].ToString();
                                                drtwo[1] = dr[1].ToString();
                                                drtwo[2] = dr[2].ToString();
                                                drtwo[3] = "新料位不存在";
                                                DTIplasEnterExcel.Rows.Add(drtwo);
                                                errorcount++;
                                                item_id_have_locid++;
                                                continue;
                                            }
                                            else
                                            {
                                                if (loc_id_lock > 0)//如果新料位存在並且沒有被鎖定--plas新增,iloc更改狀態
                                                {

                                                    ips = _IiplasMgr.getplas(ips);
                                                    ips.loc_id = dr[2].ToString();
                                                    ips.change_user = (Session["caller"] as Caller).user_id;
                                                    ips.change_dtim = DateTime.Now;
                                                    ips.item_id = Convert.ToUInt32(dr[0]);
                                                    if (_IiplasMgr.UpIplas(ips) > 0)
                                                    {
                                                        strsql.Append(ips.loc_id);

                                                        //strsql.AppendFormat("Insert into iplas (dc_id,whse_id,loc_id,change_dtim,change_user,create_dtim,create_user,lcus_id,luis_id,item_id,prdd_id,loc_rpln_lvl_uoi,loc_stor_cse_cap,ptwy_anch,flthru_anch,pwy_loc_cntl) Values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}');", ips.dc_id, ips.whse_id, ips.loc_id.ToString().ToUpper(), CommonFunction.DateTimeToString(ips.change_dtim), ips.change_user, CommonFunction.DateTimeToString(ips.create_dtim), ips.create_user, ips.lcus_id, ips.luis_id, ips.item_id, ips.prdd_id, ips.loc_rpln_lvl_uoi, ips.loc_stor_cse_cap, ips.ptwy_anch, ips.flthru_anch, ips.pwy_loc_cntl);//插入數據到表iplas表中
                                                        //strsql.AppendFormat(@" set sql_safe_updates = 0; update iloc set lsta_id='{0}',change_user='******',change_dtim='{2}' where loc_id='{3}';set sql_safe_updates = 1; ", "A", ips.change_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ips.change_dtim), ips.loc_id.ToString().ToUpper());
                                                        count++;
                                                        result++;
                                                    }
                                                    else
                                                    {
                                                        DataRow drtwo = DTIplasEnterExcel.NewRow();
                                                        drtwo[0] = dr[0].ToString();
                                                        drtwo[1] = dr[1].ToString();
                                                        drtwo[2] = dr[2].ToString();
                                                        drtwo[3] = "未知原因導入失敗";
                                                        DTIplasEnterExcel.Rows.Add(drtwo);
                                                        errorcount++;
                                                        continue;
                                                    }
                                                }
                                                else
                                                {
                                                    DataRow drtwo = DTIplasEnterExcel.NewRow();
                                                    drtwo[0] = dr[0].ToString();
                                                    drtwo[1] = dr[1].ToString();
                                                    drtwo[2] = dr[2].ToString();
                                                    drtwo[3] = "新料位已經被鎖定或非主料位";
                                                    DTIplasEnterExcel.Rows.Add(drtwo);
                                                    errorcount++;
                                                    item_id_have_locid++;
                                                    continue;
                                                }
                                            }


                                        }
                                    }
                                    else
                                    {
                                        DataRow drtwo = DTIplasEnterExcel.NewRow();
                                        drtwo[0] = dr[0].ToString();
                                        drtwo[1] = dr[1].ToString();
                                        drtwo[2] = dr[2].ToString();
                                        drtwo[3] = "商品細項編號或者料位不符合格式";
                                        DTIplasEnterExcel.Rows.Add(drtwo);
                                        errorcount++;
                                        comtentcount++;
                                        continue;
                                    }
                                }
                                catch
                                {
                                    DataRow drtwo = DTIplasEnterExcel.NewRow();
                                    drtwo[0] = dr[0].ToString();
                                    drtwo[1] = dr[1].ToString();
                                    drtwo[2] = dr[2].ToString();
                                    drtwo[3] = "數據異常";
                                    DTIplasEnterExcel.Rows.Add(drtwo);
                                    errorcount++;
                                    continue;
                                }
                            }
                        }
                        #endregion


                        #endregion

                        if (strsql.ToString().Trim() != "")
                        {
                            //result = _IiplasMgr.ExcelImportIplas(strsql.ToString());
                            if (result > 0)
                            {
                                json = "{success:true,total:" + count + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
                            }
                            else
                            {
                                json = "{success:false}";
                            }
                        }
                        else
                        {
                            json = "{success:true,total:" + 0 + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
                        }
                    }
                    else
                    {
                        json = "{success:true,total:" + 0 + ",error:" + errorcount + ",item_idcount:" + item_idcount + ",item_id_have_locid:" + item_id_have_locid + ",comtentcount:" + comtentcount + ",locid_lock:" + locid_lock + "}";
                    }
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,data:" + "" + "}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Пример #10
0
        /// <summary>
        /// 匯入會計入帳時間
        /// </summary>
        /// <returns></returns>
        public HttpResponseBase OrderMasterImport()
        {
            string json = string.Empty;//json字符串
            //string shipment = Request.Params["shipment"].ToString();
            //int total = 0;
            try
            {
                if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
                {
                    HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
                    FileManagement fileManagement = new FileManagement();//實例化 FileManagement
                    string fileLastName = excelFile.FileName;
                    string newExcelName = Server.MapPath(excelPath) + "會計賬款實收時間" + fileManagement.NewFileName(excelFile.FileName);//處理文件名,獲取新的文件名
                    excelFile.SaveAs(newExcelName);//上傳文件
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
                    dt = helper.SheetData();
                    List<OrderAccountCollection> oacli = new List<OrderAccountCollection>();
                    string errorStr = string.Empty;
                    Int64[] orderArr = new Int64[dt.Rows.Count];
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        OrderAccountCollection model = new OrderAccountCollection();
                        uint order_id = 0;
                        if (!string.IsNullOrEmpty(dt.Rows[j][0].ToString()))
                        {
                            if (uint.TryParse(dt.Rows[j][0].ToString(), out  order_id))
                            {
                                model.order_id = order_id;
                            }
                            else
                            {
                                errorStr += (j + 2) + ",";
                                continue;
                            }
                        }
                        else
                        {
                            errorStr += (j + 2) + ",";
                            continue;
                        }
                        if (!string.IsNullOrEmpty(dt.Rows[j][1].ToString()))
                        {
                            DateTime st;
                            if (DateTime.TryParse(dt.Rows[j][1].ToString(), out st))
                            {
                                model.account_collection_time = st;
                            }
                            else
                            {
                                string strtime = Regex.Replace(dt.Rows[j][1].ToString().Trim(), "/(\\s+)|(,)|(-)|(,)|(.)/g", "/");
                                if (DateTime.TryParse(strtime, out st))
                                {
                                    model.account_collection_time = st;
                                }
                                else
                                {
                                    string[] str = dt.Rows[j][1].ToString().Split('/');
                                    int year = 0;
                                    if (str.Length == 3)
                                    {
                                        if (str[2].Length == 2)
                                        {
                                            year = Convert.ToInt32("20" + str[2]);
                                        }
                                        else
                                        {
                                            year = Convert.ToInt32(str[2]);
                                        }
                                        int month = Convert.ToInt32(str[0]);
                                        int day = Convert.ToInt32(str[1]);
                                        if (DateTime.TryParse(year + "/" + month + "/" + day, out st))
                                        {
                                            model.account_collection_time = st;
                                        }
                                        else
                                        {
                                            errorStr += (j + 2) + ",";
                                            continue;
                                        }
                                    }
                                    else
                                    {
                                        errorStr += (j + 2) + ",";
                                        continue;
                                    }
                                }
                            }

                            if (!string.IsNullOrEmpty(dt.Rows[j][2].ToString()))
                            {
                                int account_collection_money = 0;
                                if (int.TryParse(dt.Rows[j][2].ToString(), out account_collection_money))
                                {
                                    model.account_collection_money = account_collection_money;
                                }
                                else
                                {
                                    errorStr += (j + 2) + ",";
                                    continue;
                                }
                            }
                            else
                            {
                                errorStr += (j + 2) + ",";
                                continue;
                            }
                            if (!string.IsNullOrEmpty(dt.Rows[j][3].ToString()))
                            {
                                int poundage = 0;
                                if (int.TryParse(dt.Rows[j][3].ToString(), out poundage))
                                {
                                    model.poundage = poundage;
                                }
                                else
                                {
                                    errorStr += (j + 2) + ",";
                                    continue;
                                }
                            }
                            else
                            {
                                errorStr += (j + 2) + ",";
                                continue;
                            }
                        }

                        if (!string.IsNullOrEmpty(dt.Rows[j][4].ToString()))
                        {
                            DateTime streturn;
                            if (DateTime.TryParse(dt.Rows[j][4].ToString(), out streturn))
                            {
                                model.return_collection_time = streturn;
                            }
                            else
                            {
                                string strtimeR = Regex.Replace(dt.Rows[j][4].ToString().Trim(), "/(\\s+)|(,)|(-)|(,)|(.)/g", "/");
                                if (DateTime.TryParse(strtimeR.ToString(), out streturn))
                                {
                                    model.return_collection_time = streturn;
                                }
                                else
                                {
                                    string[] strR = dt.Rows[j][4].ToString().Split('/');
                                    int yearR = 0;
                                    if (strR.Length == 3)
                                    {
                                        if (strR[2].Length == 2)
                                        {
                                            yearR = Convert.ToInt32("20" + strR[2]);
                                        }
                                        else
                                        {
                                            yearR = Convert.ToInt32(strR[2]);
                                        }
                                        int monthR = Convert.ToInt32(strR[0]);
                                        int dayR = Convert.ToInt32(strR[1]);
                                        if (DateTime.TryParse(yearR + "/" + monthR + "/" + dayR, out streturn))
                                        {
                                            model.return_collection_time = streturn;
                                        }
                                        else
                                        {
                                            errorStr += (j + 2) + ",";
                                            continue;
                                        }
                                    }
                                    else
                                    {
                                        errorStr += (j + 2) + ",";
                                        continue;
                                    }
                                }
                            }
                            if (!string.IsNullOrEmpty(dt.Rows[j][5].ToString()))
                            {
                                int return_collection_money = 0;
                                if (int.TryParse(dt.Rows[j][5].ToString(), out return_collection_money))
                                {
                                    model.return_collection_money = return_collection_money;
                                }
                                else
                                {
                                    errorStr += (j + 2) + ",";
                                    continue;
                                }
                            }
                            else
                            {
                                errorStr += (j + 2) + ",";
                                continue;
                            }
                            if (!string.IsNullOrEmpty(dt.Rows[j][6].ToString()))
                            {
                                int return_poundage = 0;
                                if (int.TryParse(dt.Rows[j][6].ToString(), out return_poundage))
                                {
                                    model.return_poundage = return_poundage;
                                }
                                else
                                {
                                    errorStr += (j + 2) + ",";
                                    continue;
                                }
                            }
                            else
                            {
                                errorStr += (j + 2) + ",";
                                continue;
                            }
                        }
                        if (!string.IsNullOrEmpty(dt.Rows[j][7].ToString()))
                        {
                            DateTime st;
                            if (DateTime.TryParse(dt.Rows[j][7].ToString(), out st))
                            {
                                model.invoice_date_manual = st;
                            }
                            else
                            {
                                string strtime = Regex.Replace(dt.Rows[j][1].ToString().Trim(), "/(\\s+)|(,)|(-)|(,)|(.)/g", "/");
                                if (DateTime.TryParse(strtime, out st))
                                {
                                    model.invoice_date_manual = st;
                                }
                                else
                                {
                                    string[] str = dt.Rows[j][7].ToString().Split('/');
                                    int year = 0;
                                    if (str.Length == 3)
                                    {
                                        if (str[2].Length == 2)
                                        {
                                            year = Convert.ToInt32("20" + str[2]);
                                        }
                                        else
                                        {
                                            year = Convert.ToInt32(str[2]);
                                        }
                                        int month = Convert.ToInt32(str[0]);
                                        int day = Convert.ToInt32(str[1]);
                                        if (DateTime.TryParse(year + "/" + month + "/" + day, out st))
                                        {
                                            model.invoice_date_manual = st;
                                        }
                                        else
                                        {
                                            errorStr += (j + 2) + ",";
                                            continue;
                                        }
                                    }
                                    else
                                    {
                                        errorStr += (j + 2) + ",";
                                        continue;
                                    }
                                }
                            }

                            if (!string.IsNullOrEmpty(dt.Rows[j][8].ToString()))
                            {
                                int invoice_sale_manual = 0;
                                if (int.TryParse(dt.Rows[j][8].ToString(), out invoice_sale_manual))
                                {
                                    model.invoice_sale_manual = invoice_sale_manual;
                                }
                                else
                                {
                                    errorStr += (j + 2) + ",";
                                    continue;
                                }
                            }
                            else
                            {
                                errorStr += (j + 2) + ",";
                                continue;
                            }
                            if (!string.IsNullOrEmpty(dt.Rows[j][9].ToString()))
                            {
                                int invoice_tax_manual = 0;
                                if (int.TryParse(dt.Rows[j][9].ToString(), out invoice_tax_manual))
                                {
                                    model.invoice_tax_manual = invoice_tax_manual;
                                }
                                else
                                {
                                    errorStr += (j + 2) + ",";
                                    continue;
                                }
                            }
                            else
                            {
                                errorStr += (j + 2) + ",";
                                continue;
                            }
                        }
                        model.remark = dt.Rows[j][10].ToString();
                        if (model != null && !(model.account_collection_time == model.return_collection_time && model.account_collection_time == model.invoice_date_manual && model.return_collection_time == DateTime.MinValue))
                        {
                            if (!orderArr.Contains(model.order_id))
                            {
                                orderArr[j] = order_id;
                                oacli.Add(model);
                            }
                            else
                            {
                                errorStr += (j + 2) + ",";
                            }
                        }
                    }
                    int rowsnum = oacli.Count;
                    if (!string.IsNullOrEmpty(errorStr))
                    {
                        errorStr = errorStr.Remove(errorStr.Length - 1);
                    }
                    if (rowsnum > 0)//判斷是否是這個表
                    {
                        _OrderMasterMgr = new OrderMasterMgr(connectionString);

                        int i = _OrderMasterMgr.OrderMasterImport(oacli);
                        if (i > 0)
                        {
                            if (i == 99999)
                            {
                                if (!string.IsNullOrEmpty(errorStr))
                                {
                                    json = "{success:true,msg:\"" + "無數據可匯入!另文件第" + errorStr + "行數據異常\"}";
                                }
                                else
                                {
                                    json = "{success:true,msg:\"" + "無數據可匯入!\"}";
                                }
                            }
                            else
                            {
                                if (!string.IsNullOrEmpty(errorStr))
                                {
                                    json = "{success:true,msg:\"" + "匯入成功!另文件第" + errorStr + "行數據異常\"}";
                                }
                                else
                                {
                                    json = "{success:true,msg:\"" + "匯入成功!\"}";
                                }
                            }
                        }
                        else
                        {
                            if (!string.IsNullOrEmpty(errorStr))
                            {
                                json = "{success:true,msg:\"" + "操作失敗!另文件第" + errorStr + "行數據異常\"}";
                            }
                            else
                            {
                                json = "{success:true,msg:\"" + "操作失敗!\"}";
                            }
                        }
                    }
                    else
                    {
                        json = "{success:true,total:0,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
                        this.Response.Clear();
                        this.Response.Write(json);
                        this.Response.End();
                        return this.Response;
                    }
                }
                else//當直接點擊時會產生,
                {
                    json = "{success:true,msg:\"" + "操作失敗!" + "\"}";
                    this.Response.Clear();
                    this.Response.Write(json);
                    this.Response.End();
                    return this.Response;
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:true,msg:\"" + "操作失敗!" + "\"}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Пример #11
0
        /// <summary>
        /// 料位匯入,通過Excel
        /// </summary>
        /// <returns></returns>
        public HttpResponseBase UploadExcel()
        {
            string newName = string.Empty;
            string json = string.Empty;
            List<IupcQuery> store = new List<IupcQuery>();
            _IparasrcMgr = new ParameterMgr(mySqlConnectionString);
            StringBuilder codeType1 = new StringBuilder();
            string codeTypeStr1 = string.Empty;
            try
            {

                List<BLL.gigade.Model.Parametersrc> codeTypeList = _IparasrcMgr.GetElementType("iupc_type");
                foreach (var codeTypeModel in codeTypeList)
                {
                    codeType1.AppendFormat("{0}:{1}, ", codeTypeModel.ParameterCode, codeTypeModel.parameterName);
                }
                codeTypeStr1 = codeType1.ToString().Substring(0, codeType1.Length - 2);

                DTIupcExcel.Clear();
                DTIupcExcel.Columns.Clear();

                DTIupcExcel.Columns.Add("商品細項編號", typeof(String));
                DTIupcExcel.Columns.Add("條碼編號", typeof(String));
                DTIupcExcel.Columns.Add("條碼類型(" + codeTypeStr1 + ")", typeof(String));
                DTIupcExcel.Columns.Add("不能匯入的原因", typeof(String));
                DTIupcExcel.Columns.Add("匯入失敗數據的行號", typeof(String));

                DataTable DTIupcImportSucceed = new DataTable();
                DTIupcImportSucceed.Columns.Add("商品細項編號", typeof(String));
                DTIupcImportSucceed.Columns.Add("條碼編號", typeof(String));
                DTIupcImportSucceed.Columns.Add("條碼類型", typeof(String));
                DTIupcImportSucceed.Columns.Add("行號", typeof(String));

                int result = 0;
                int count = 0;//總匯入數
                int errorCount = 0;//異常數據數量
                int create_user = (Session["caller"] as Caller).user_id;
                int itemIdNotExistCount = 0;//商品細項編號不存在數量
                //int iupcTypeNotExistCount = 0;//條碼類型不存在數量
                int repeatCount = 0;//商品條碼重複數量
                StringBuilder strsql = new StringBuilder();
                if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
                {
                    HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
                    //FileManagement fileManagement = new FileManagement();
                    newName = Server.MapPath(excelPath) + excelFile.FileName;
                    excelFile.SaveAs(newName);
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
                    dt = helper.SheetData();

                    if (dt.Rows.Count > 0)
                    {
                        _IiupcMgr = new IupcMgr(mySqlConnectionString);
                        #region 循環Excel的數據
                        
                        int i = 0;
                        for (int k = 0; k < dt.Rows.Count; k++)
                        {
                            i++;
                            try
                            {
                                bool b0 = string.IsNullOrEmpty(dt.Rows[k][0].ToString().Trim());
                                bool b1 = string.IsNullOrEmpty(dt.Rows[k][1].ToString().Trim());
                                bool b2 = string.IsNullOrEmpty(dt.Rows[k][2].ToString().Trim());

                                if (b0 || b1 || b2)//如果數據有一個欄位為空
                                {
                                    DataRow drtwo = DTIupcExcel.NewRow();
                                    drtwo[0] = dt.Rows[k][0].ToString();
                                    drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
                                    drtwo[2] = dt.Rows[k][2].ToString();
                                    drtwo[3] = "這條數據有欄位為空,請確認";
                                    drtwo[4] = k + 2;//匯入失敗數據的行號,Excel表行號
                                    DTIupcExcel.Rows.Add(drtwo);
                                    errorCount++;
                                    continue;
                                }
                                if (dt.Rows[k][1].ToString().Trim().Length <= 25)
                                {

                                    int a = Convert.ToInt32(dt.Rows[k][0]);//商品細項編號
                                    string b = dt.Rows[k][1].ToString().Trim();//條碼編號
                                    int c = Convert.ToInt32(dt.Rows[k][2]);//條碼類型
                                    int flag = _IiupcMgr.Yesornoexist(a, b);

                                    if (flag == 1)//等於1表示商品細項表里面沒有此商品細項編號
                                    {
                                        DataRow drtwo = DTIupcExcel.NewRow();
                                        drtwo[0] = dt.Rows[k][0].ToString();
                                        drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
                                        drtwo[2] = dt.Rows[k][2].ToString();
                                        drtwo[3] = "在數據庫商品表中,不存在此商品細項編號";
                                        drtwo[4] = k + 2;//匯入失敗數據的行號,Excel表行號
                                        DTIupcExcel.Rows.Add(drtwo);
                                        itemIdNotExistCount++;
                                        continue;
                                    }

                                    if (flag == 2)//等於2表示條碼表裡面已存在此條碼
                                    {
                                        DataRow drtwo = DTIupcExcel.NewRow();
                                        drtwo[0] = dt.Rows[k][0].ToString();
                                        drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
                                        drtwo[2] = dt.Rows[k][2].ToString();
                                        drtwo[3] = "在數據庫中,該條碼已經存在";
                                        drtwo[4] = k + 2;
                                        DTIupcExcel.Rows.Add(drtwo);
                                        repeatCount++;
                                        continue;
                                    }

                                    if (flag == 0)//當存在此商品細項編號並且該條碼不存在時進行添加數據
                                    {

                                        bool xunhuan = true;
                                        for (int j = 0; j < i - 1; j++)
                                        {
                                            if (dt.Rows[j][1].ToString() == dt.Rows[k][1].ToString())//如果匯入的Excel條碼重複
                                            {
                                                xunhuan = false;
                                                DataRow drtwo = DTIupcExcel.NewRow();
                                                drtwo[0] = dt.Rows[k][0].ToString();
                                                drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
                                                drtwo[2] = dt.Rows[k][2].ToString();
                                                drtwo[3] = "該商品條碼與此表中(行號: " + (j + 2) + " )的商品細項編號:" + dt.Rows[j][0].ToString() + "的條碼重複";
                                                drtwo[4] = k + 2;
                                                DTIupcExcel.Rows.Add(drtwo);
                                                repeatCount++;
                                                break;
                                            }
                                        }
                                        if (xunhuan)
                                        {
                                            string codeTypeStr = string.Empty;
                                            StringBuilder codeType = new StringBuilder();
                                            bool haveCodeType = false;
                                            foreach (var codeTypeModel in codeTypeList)
                                            {
                                                if (Convert.ToString(dt.Rows[k][2]).Trim() == codeTypeModel.ParameterCode)
                                                {
                                                    haveCodeType = true;
                                                }
                                                codeType.AppendFormat("{0}:{1}, ", codeTypeModel.ParameterCode, codeTypeModel.parameterName);
                                            }
                                            codeTypeStr = codeType.ToString().Substring(0, codeType.Length - 2);
                                            if (!haveCodeType)//此條碼類型是否在參數表t_parameterSrc(parameterType="iupc_type")中存在
                                            {
                                                DataRow drtwo = DTIupcExcel.NewRow();
                                                drtwo[0] = dt.Rows[k][0].ToString();
                                                drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
                                                drtwo[2] = dt.Rows[k][2].ToString();
                                                drtwo[3] = "在數據庫參數表中,此條碼類型不存在(" + codeTypeStr + ")";
                                                drtwo[4] = k + 2;
                                                DTIupcExcel.Rows.Add(drtwo);
                                                errorCount++;
                                                continue;
                                            }
                                            //如果條碼類為 1 時,判斷該商品是否在Iupc表中已經存在國際條碼
                                            if (_IiupcMgr.upc_num(Convert.ToInt32(dt.Rows[k][0])) > 0 && dt.Rows[k][2].ToString() == "1")
                                            {
                                                DataRow drtwo = DTIupcExcel.NewRow();
                                                drtwo[0] = dt.Rows[k][0].ToString();
                                                drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
                                                drtwo[2] = dt.Rows[k][2].ToString();
                                                drtwo[3] = "在數據庫中,該商品已經存在國際條碼";
                                                drtwo[4] = k + 2;
                                                DTIupcExcel.Rows.Add(drtwo);
                                                repeatCount++;
                                                continue;
                                            }
                                            else
                                            {
                                                bool skip = false;
                                                for (int index = 0; index < DTIupcImportSucceed.Rows.Count; index++)
                                                {
                                                    bool m1 = dt.Rows[k][0].ToString().Trim() == DTIupcImportSucceed.Rows[index][0].ToString().Trim();
                                                    bool m2 = dt.Rows[k][2].ToString().Trim() == "1";
                                                    bool m3 = DTIupcImportSucceed.Rows[index][2].ToString().Trim() == "1";

                                                    if (m1 && m2 && m3)//在已經成功匯入的數據中,判斷該商品是否存在國際條碼 
                                                    {
                                                        skip = true;
                                                        DataRow drtwo1 = DTIupcExcel.NewRow();
                                                        drtwo1[0] = dt.Rows[k][0].ToString();
                                                        drtwo1[1] = " " + dt.Rows[k][1].ToString().Trim();
                                                        drtwo1[2] = dt.Rows[k][2].ToString();
                                                        drtwo1[3] = "在已經成功匯入的數據中(行號: " + DTIupcImportSucceed.Rows[index][3].ToString() + "),該商品已經存在國際條碼";
                                                        drtwo1[4] = k + 2;
                                                        DTIupcExcel.Rows.Add(drtwo1);
                                                        repeatCount++;
                                                        break;
                                                    }
                                                }
                                                if (skip)
                                                {
                                                    continue;
                                                }
                                                DataRow drtwo = DTIupcImportSucceed.NewRow();
                                                drtwo[0] = dt.Rows[k][0].ToString();
                                                drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
                                                drtwo[2] = dt.Rows[k][2].ToString();
                                                drtwo[3] = k + 2;
                                                DTIupcImportSucceed.Rows.Add(drtwo);


                                                count++;
                                                string dataTimeNow = CommonFunction.DateTimeToString(DateTime.Now);
                                                strsql.AppendFormat(@"insert into iupc(upc_id,item_id,suppr_upc,lst_ship_dte,lst_rct_dte,create_dtim,create_user,upc_type_flg)
                    values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');", b, a, "", dataTimeNow, dataTimeNow, dataTimeNow, create_user, c);
                                                continue;
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    DataRow drtwo = DTIupcExcel.NewRow();
                                    drtwo[0] = dt.Rows[k][0].ToString();
                                    drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
                                    drtwo[2] = dt.Rows[k][2].ToString();
                                    drtwo[3] = "條碼不符合格式(0-25位)";
                                    drtwo[4] = k + 2;
                                    DTIupcExcel.Rows.Add(drtwo);
                                    errorCount++;
                                    continue;
                                }
                            }
                            catch
                            {
                                DataRow drtwo = DTIupcExcel.NewRow();
                                drtwo[0] = dt.Rows[k][0].ToString();
                                drtwo[1] = " " + dt.Rows[k][1].ToString().Trim();
                                drtwo[2] = dt.Rows[k][2].ToString();
                                drtwo[3] = "數據異常";
                                drtwo[4] = k + 2;
                                DTIupcExcel.Rows.Add(drtwo);
                                errorCount++;
                                continue;
                            }
                        }
                        #region 註釋的代碼foreach
                        //                        foreach (DataRow dr in dt.Rows)
                        //                        {
                        //                            i++;
                        //                            try
                        //                            {
                        //                                if (!string.IsNullOrEmpty(dr[1].ToString()) && dr[1].ToString().Length >= 8 && dr[1].ToString().Length <= 25)
                        //                                {

                        //                                    int a = Convert.ToInt32(dr[0]);//商品細項編號
                        //                                    string b = dr[1].ToString();//條碼編號
                        //                                    int c = Convert.ToInt32(dr[2]);//條碼類型
                        //                                    int flag = _IiupcMgr.Yesornoexist(a, b);

                        //                                    if (flag == 1)//等於1表示商品細項表里面沒有此商品細項編號
                        //                                    {
                        //                                        DataRow drtwo = DTIupcExcel.NewRow();
                        //                                        drtwo[0] = dr[0].ToString();
                        //                                        drtwo[1] = " " + dr[1].ToString();
                        //                                        drtwo[2] = dr[2].ToString();
                        //                                        drtwo[3] = "商品表不存在此商品細項編號";
                        //                                        DTIupcExcel.Rows.Add(drtwo);
                        //                                        bucunzaicount++;
                        //                                        continue;
                        //                                    }

                        //                                    if (flag == 2)//等於2表示條碼表裡面已存在此條碼
                        //                                    {
                        //                                        DataRow drtwo = DTIupcExcel.NewRow();
                        //                                        drtwo[0] = dr[0].ToString();
                        //                                        drtwo[1] = " " + dr[1].ToString();
                        //                                        drtwo[2] = dr[2].ToString();
                        //                                        drtwo[3] = "該條碼已經存在";
                        //                                        DTIupcExcel.Rows.Add(drtwo);
                        //                                        chongfucount++;
                        //                                        continue;
                        //                                    }

                        //                                    if (flag == 0)//當存在此商品細項編號並且該條碼不存在時進行添加數據
                        //                                    {

                        //                                        bool xunhuan = true;
                        //                                        for (int j = 0; j < i - 1; j++)
                        //                                        {
                        //                                            if (dt.Rows[j][1].ToString() == dr[1].ToString())//如果匯入的Excel條碼重複
                        //                                            {
                        //                                                xunhuan = false;
                        //                                                DataRow drtwo = DTIupcExcel.NewRow();
                        //                                                drtwo[0] = dr[0].ToString();
                        //                                                drtwo[1] = " " + dr[1].ToString();
                        //                                                drtwo[2] = dr[2].ToString();
                        //                                                drtwo[3] = "該商品條碼與在此表中的商品細項編號:" + dt.Rows[j][0].ToString() + "條碼重複";
                        //                                                DTIupcExcel.Rows.Add(drtwo);
                        //                                                chongfucount++;
                        //                                                break;
                        //                                            }
                        //                                        }
                        //                                        if (xunhuan)
                        //                                        {
                        //                                            string codeTypeStr = string.Empty;
                        //                                            StringBuilder codeType = new StringBuilder();
                        //                                            bool haveCodeType = false;
                        //                                            foreach (var codeTypeModel in codeTypeList)
                        //                                            {
                        //                                                if (Convert.ToString(dr[2]) == codeTypeModel.ParameterCode)
                        //                                                {
                        //                                                    haveCodeType = true;                                                  
                        //                                                }
                        //                                                codeType.AppendFormat("{0}:{1}, ", codeTypeModel.ParameterCode, codeTypeModel.parameterName);
                        //                                            }
                        //                                            codeTypeStr = codeType.ToString().Substring(0, codeType.Length - 2);
                        //                                            if (!haveCodeType)
                        //                                            {
                        //                                                DataRow drtwo = DTIupcExcel.NewRow();
                        //                                                drtwo[0] = dr[0].ToString();
                        //                                                drtwo[1] = " " + dr[1].ToString();
                        //                                                drtwo[2] = dr[2].ToString();
                        //                                                drtwo[3] = "此條碼類型不存在(" + codeTypeStr + ")";
                        //                                                DTIupcExcel.Rows.Add(drtwo);
                        //                                                errorcount++;
                        //                                                continue;
                        //                                            }

                        //                                            if (_IiupcMgr.upc_num(Convert.ToInt32(dr[0])) > 0)
                        //                                            {
                        //                                                DataRow drtwo = DTIupcExcel.NewRow();
                        //                                                drtwo[0] = dr[0].ToString();
                        //                                                drtwo[1] = " " + dr[1].ToString();
                        //                                                drtwo[2] = dr[2].ToString();
                        //                                                drtwo[3] = "該商品已經存在國際條碼";
                        //                                                DTIupcExcel.Rows.Add(drtwo);
                        //                                                chongfucount++;
                        //                                                break;
                        //                                            }
                        //                                            else
                        //                                            {
                        //                                                count++;
                        //                                                strsql.AppendFormat(@"insert into iupc(upc_id,item_id,create_dtim,create_user,upc_type_flg)
                        //                    values('{0}','{1}','{2}','{3}','{4}');", b, a, CommonFunction.DateTimeToString(DateTime.Now), create_user,c);//默認匯入 的是國際條碼
                        //                                                continue;
                        //                                            }
                        //                                        }
                        //                                    }
                        //                                }
                        //                                else
                        //                                {
                        //                                    DataRow drtwo = DTIupcExcel.NewRow();
                        //                                    drtwo[0] = dr[0].ToString();
                        //                                    drtwo[1] = " " + dr[1].ToString();
                        //                                    drtwo[2] = dr[2].ToString();
                        //                                    drtwo[3] = "條碼不符合格式";
                        //                                    DTIupcExcel.Rows.Add(drtwo);
                        //                                    errorcount++;
                        //                                    continue;
                        //                                }
                        //                            }
                        //                            catch
                        //                            {
                        //                                DataRow drtwo = DTIupcExcel.NewRow();
                        //                                drtwo[0] = dr[0].ToString();
                        //                                drtwo[1] = " " + dr[1].ToString();
                        //                                drtwo[2] = dr[2].ToString();
                        //                                drtwo[3] = "數據異常";
                        //                                DTIupcExcel.Rows.Add(drtwo);
                        //                                errorcount++;
                        //                                continue;
                        //                            }
                        // 
                        #endregion                        }
                        #endregion
                        if (strsql.ToString().Trim() != "")
                        {
                            result = _IiupcMgr.ExcelImportIupc(strsql.ToString());
                            if (result > 0)
                            {
                                json = "{success:true,total:" + count + ",error:" + errorCount + ",repeat:" + repeatCount + ",NoItem:" + itemIdNotExistCount + "}";
                            }
                            else
                            {
                                json = "{success:false}";
                            }
                        }
                        else
                        {
                            json = "{success:true,total:" + 0 + ",error:" + errorCount + ",repeat:" + repeatCount + ",NoItem:" + itemIdNotExistCount + "}";
                        }
                    }
                    else
                    {
                        json = "{success:true,total:" + 0 + ",error:" + 0 + ",repeat:" + 0 + ",NoItem:" + 0 + ",NoType:" + 0 + "}";
                    }
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false }";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Пример #12
0
        public HttpResponseBase ImportMarketCategory()
        {
            int j = 0;
            string json = string.Empty;//json字符串

            try
            {
                if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
                {
                    HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
                    FileManagement fileManagement = new FileManagement();//實例化 FileManagement
                    string fileLastName = excelFile.FileName;
                    string newExcelName = Server.MapPath(excelPath) + "MarketCategory" + fileManagement.NewFileName(excelFile.FileName);//處理文件名,獲取新的文件名
                    excelFile.SaveAs(newExcelName);//上傳文件
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
                    dt = helper.SheetData();
                    DataRow[] dr = dt.Select("market_category <> ''"); //定义一个DataRow数组,读取ds里面所有行
                    int rowsnum = dt.Rows.Count;

                    if (rowsnum == 0)
                    {
                        json = "{success:false,msg:0}";//此表內沒有數據或數據有誤,請檢查后再次匯入!
                        this.Response.Clear();
                        this.Response.Write(json);
                        this.Response.End();
                        return this.Response;
                    }
                    else
                    {

                        _marketCategoryMgr = new MarketCategoryMgr(mySqlConnectionString);
                        j = _marketCategoryMgr.MarketCategoryImport(dr);
                        if (j > 0)
                        {
                            json = "{success:true,msg:1}";//操作成功共匯入total條數據
                        }
                        else
                        {
                            json = "{success:false,msg:2}";//匯入失敗,請檢查是否重複匯入!
                        }

                    }
                }
                else//當直接點擊時會產生,
                {
                    json = "{success:true,msg:3}";//未找到該文件
                    this.Response.Clear();
                    this.Response.Write(json);
                    this.Response.End();
                    return this.Response;

                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,msg:\"" + ex.ToString() + "\"}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Пример #13
0
 /// <summary>
 /// 導入Excel數據
 /// </summary>
 /// <returns></returns>
 public HttpResponseBase AppCategoryUpExcel()
 {
     string json = string.Empty;//json字符串
     int successcount = 0;
     int failcount = 0;
     int typeerror = 0;//要匯入的數據類型錯誤
     try
     {
         if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
         {
             HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
             FileManagement fileManagement = new FileManagement();//實例化 FileManagement
             StringBuilder str = new StringBuilder();
             string fileLastName = excelFile.FileName;
             string newExcelName = Server.MapPath(excelPath) + "App功能管理" + fileManagement.NewFileName(excelFile.FileName);//處理文件名,獲取新的文件名
             excelFile.SaveAs(newExcelName);//上傳文件
             DataTable dt = new DataTable();
             NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
             dt = helper.SheetData();//根據這個表,插入到數據庫中
             DataRow[] dr = dt.Select(); //定义一个DataRow数组,读取ds里面所有行
             _iappcategoryMgr = new AppcategoryMgr(mySqlConnectionString);
             if (dt.Rows.Count > 0)
             {
                 for (int i = 0; i < dt.Rows.Count; i++)
                 {
                     Appcategory appCategory = new Appcategory();
                     try
                     {
                         appCategory.brand_id = Convert.ToInt32(dt.Rows[i]["brand_id"]);
                         appCategory.brand_name = dt.Rows[i]["brand_name"].ToString();
                         appCategory.category = dt.Rows[i]["category"].ToString();
                         appCategory.category1 = dt.Rows[i]["category1"].ToString();
                         appCategory.category2 = dt.Rows[i]["category2"].ToString();
                         appCategory.category3 = dt.Rows[i]["category3"].ToString();
                         appCategory.product_id = Convert.ToInt32(dt.Rows[i]["product_id"]);
                         appCategory.property = dt.Rows[i]["property"].ToString();
                     }
                     catch (Exception ex)
                     {
                         str.Append(i + 2 + " ");
                         failcount++;
                         continue;
                     }
                     int results=  _iappcategoryMgr.AppcategorySave(appCategory);
                     if (results > 0)
                     {
                         successcount++;
                     }
                     else
                     {
                         str.Append(i + 2 + " ");
                         failcount++;
                     }
                 }
                 if (str.Length > 1)
                 {
                     str.Length -= 1;
                     json = "{success:true,total:" + successcount + ",fail:" + failcount + ",errorRow:\"" + str.ToString() + "\"}";
                 }
                 else
                 {
                     json = "{success:true,total:" + successcount + ",fail:" + failcount + "}";
                 }
             }
             else
             {
                 json = "{success:false,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
             }
           
         }
         else
         {
             json = "{success:false,msg:\"" + "請選擇要匯入的Excel表" + "\"}";
         }
     }
     catch (Exception ex)
     {
         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
         log.Error(logMessage);
         json = "{success:false,msg:\"" + ex.ToString() + "\"}";
     }
     this.Response.Clear();
     this.Response.Write(json);
     this.Response.End();
     return this.Response;
 }
Пример #14
0
 public HttpResponseBase KeyWordUploadExcel()
 {
     string newName = string.Empty;
     string json = string.Empty;
     List<SphinxKeywordQuery> store = new List<SphinxKeywordQuery>();
     HashEncrypt hashpt = new HashEncrypt();
     try
     {
         DTIlocExcel.Clear();
         DTIlocExcel.Columns.Clear();
         DTIlocExcel.Columns.Add("keyword", typeof(String));
         DTIlocExcel.Columns.Add("foodkeyword(1:是食安關鍵字,0不是食安關鍵字)", typeof(String));
         DTIlocExcel.Columns.Add("failureMessage", typeof(String));
         int result = 0;
         int count = 0;//總匯入數
         //int entercount = 0;//插入失敗個數
         int errorcount = 0;//數據異常個數
         int repeat = 0;//數據已存在個數
         string create_user = (Session["caller"] as Caller).user_username;
         if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
         {
             HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
             newName = Server.MapPath(excelPath) + excelFile.FileName;
             excelFile.SaveAs(newName);
             DataTable dt = new DataTable();
             NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
             dt = helper.SheetData();
             if (dt.Rows.Count > 0)
             {
                 swMgr = new SystemKeyWordMgr(SqlConnectionString);
                 int i = 0;
                 foreach (DataRow dr in dt.Rows)
                 {
                     StringBuilder strsql = new StringBuilder();
                     SphinxKeywordQuery query = new BLL.gigade.Model.Query.SphinxKeywordQuery();
                     i++;
                     try
                     {
                         if (!string.IsNullOrEmpty(dr[0].ToString()) && !string.IsNullOrEmpty(dr[1].ToString()))
                         {
                             int row_id_exsit = swMgr.CheckKeyWordExsit(dr[0].ToString());//判斷關鍵字是否存在
                             query.user_name = create_user;
                             query.key_word = dr[0].ToString();
                             query.flag = dr[1].ToString();
                             query.operateType = 1;
                             if (row_id_exsit > 0)
                             {
                                 DataRow drtwo = DTIlocExcel.NewRow();
                                 drtwo[0] = dr[0].ToString();
                                 drtwo[1] = dr[1].ToString();
                                 drtwo[2] = "系統關鍵字已存在";
                                 DTIlocExcel.Rows.Add(drtwo);
                                 repeat++;
                                 continue;
                             }
                             else//關鍵字不存在
                             {
                                 if (query.flag == "0" || query.flag == "1")
                                 {
                                     byte[] strBt = Encoding.Unicode.GetBytes(query.key_word);
                                     //關鍵字最多為25個中文字或50個英文字
                                     if (strBt.Length <= 50)
                                     {
                                         result = swMgr.SaveSystemKeyWord(query);
                                         if (result > 0)
                                         {
                                             count++;
                                             continue;
                                         }
                                         else
                                         {
                                             DataRow drtwo = DTIlocExcel.NewRow();
                                             drtwo[0] = dr[0].ToString();
                                             drtwo[1] = dr[1].ToString();
                                             drtwo[2] = "關鍵字插入數據庫時失敗";
                                             DTIlocExcel.Rows.Add(drtwo);
                                             errorcount++;
                                             continue;
                                         }
                                     }
                                     else
                                     {
                                         DataRow drtwo = DTIlocExcel.NewRow();
                                         drtwo[0] = dr[0].ToString();
                                         drtwo[1] = dr[1].ToString();
                                         drtwo[2] = "關鍵字最多為25個中文字或50個英文字";
                                         DTIlocExcel.Rows.Add(drtwo);
                                         errorcount++;
                                         continue;
                                     }
                                 }
                                 else
                                 {
                                     DataRow drtwo = DTIlocExcel.NewRow();
                                     drtwo[0] = dr[0].ToString();
                                     drtwo[1] = dr[1].ToString();
                                     drtwo[2] = "'foodkeyword的值只能為0或1'";
                                     DTIlocExcel.Rows.Add(drtwo);
                                     errorcount++;
                                     continue;
                                 }
                             }
                         }
                         else
                         {
                             DataRow drtwo = DTIlocExcel.NewRow();
                             drtwo[0] = dr[0].ToString();
                             drtwo[1] = dr[1].ToString();
                             drtwo[2] = "keyword或foodkeyword不符合格式";
                             DTIlocExcel.Rows.Add(drtwo);
                             errorcount++;
                             continue;
                         }
                     }
                     catch
                     {
                         DataRow drtwo = DTIlocExcel.NewRow();
                         drtwo[0] = dr[0].ToString();
                         drtwo[1] = dr[1].ToString();
                         drtwo[2] = "數據異常";
                         DTIlocExcel.Rows.Add(drtwo);
                         errorcount++;
                         continue;
                     }
                 }
                 if (count > 0)
                 {
                     json = "{success:true,total:" + count + ",error:" + errorcount + ",repeat:" + repeat+"}";
                 }
                 else
                 {
                     json = "{success:true,total:" + 0 + ",error:" + errorcount + ",repeat:" + repeat+"}";
                 }
             }
             else
             {
                 json = "{success:true,total:" + 0 + ",error:" + 0 + ",repeat" + repeat + "}";
             }
         }
     }
     catch (Exception ex)
     {
         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
         log.Error(logMessage);
         json = "{success:false,data:" + "" + "}";
     }
     this.Response.Clear();
     this.Response.Write(json);
     this.Response.End();
     return this.Response;
 }
Пример #15
0
        //excel
        public HttpResponseBase RedirectUploadExcel()
        {
            string newName = string.Empty;
            string json = string.Empty;
            _redirectMgr = new RedirectMgr(mySqlConnectionString);
            try
            {
                DTExcels.Clear();
                DTExcels.Columns.Clear();
                DTExcels.Columns.Add("連結名稱", typeof(String));
                DTExcels.Columns.Add("目的連結", typeof(String));
                DTExcels.Columns.Add("連結狀態", typeof(String));//1表示正常2表示停用 其他都為正常
                int count = 0;//總匯入數
                int errorcount = 0;//數據異常個數
                int create_user = (Session["caller"] as Caller).user_id;
                if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
                {
                    HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
                    newName = Server.MapPath(excelPath) + excelFile.FileName;
                    excelFile.SaveAs(newName);
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
                    dt = helper.SheetData();
                    if (dt.Rows.Count > 0 && !string.IsNullOrEmpty(dt.Rows[0][0].ToString()) && !string.IsNullOrEmpty(dt.Rows[0][1].ToString()))
                    {
                            foreach (DataRow dr in dt.Rows)
                            {
                                if (!string.IsNullOrEmpty(dr[0].ToString()) && !string.IsNullOrEmpty(dr[1].ToString()))
                                {
                                    #region 匯入數據
                                    RedirectQuery rd = new RedirectQuery();
                                    rd.group_id = Convert.ToUInt32(Request.Params["group_id"]);//得到群組
                                    try
                                    {
                                        int linkstatus = 0;
                                        if (!int.TryParse(dr[2].ToString(), out linkstatus))
                                        {
                                            linkstatus = 1;
                                        }
                                        if (linkstatus == 2)
                                        {
                                            linkstatus = 2;
                                            rd.redirect_name = dr[0].ToString();//連接名稱
                                            rd.redirect_url = dr[1].ToString();//目的連接
                                            rd.redirect_status = Convert.ToUInt32(linkstatus);//連接狀態
                                        }
                                        else
                                        {
                                            linkstatus = 1;
                                            rd.redirect_name = dr[0].ToString();
                                            rd.redirect_url = dr[1].ToString();
                                            rd.redirect_status = Convert.ToUInt32(linkstatus);
                                        }
                                        rd.redirect_createdate = Convert.ToUInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
                                        rd.redirect_updatedate = Convert.ToUInt32(CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
                                        rd.redirect_ipfrom = CommonFunction.GetClientIP();
                                        //獲取Serial裡面的redirect_id
                                        _serialMgr = new SerialMgr(mySqlConnectionString);
                                        rd.redirect_id = uint.Parse((_serialMgr.GetSerialById(4).Serial_Value + 1).ToString());
                                    }
                                    catch (Exception ex)
                                    {//不是正常數據時候直接帶1(正常)
                                        int linkstatus = 1;
                                        rd.redirect_name = dr[0].ToString();
                                        rd.redirect_url = dr[1].ToString();
                                        rd.redirect_status = Convert.ToUInt32(linkstatus);
                                    }
                                    int result = 0;//如果獲取到的連結名稱和url為空就不新增
                                    if (!string.IsNullOrEmpty(rd.redirect_name) && !string.IsNullOrEmpty(rd.redirect_url))//&& Regex.IsMatch(rd.redirect_url,'')
                                    {

                                        result = _redirectMgr.EnterInotRedirect(rd);
                                    }
                                    if (result > 0)
                                    {//插入成功一條并修改serial表數據
                                        count++;
                                        Serial serial = new Serial();
                                        serial.Serial_id = 4;
                                        serial.Serial_Value = rd.redirect_id;
                                        _serialMgr.Update(serial);
                                    }
                                    else
                                    {
                                        DataRow drs = DTExcels.NewRow();
                                        drs[0] = dr[0].ToString();
                                        drs[1] = dr[1].ToString();
                                        drs[2] = dr[2].ToString();
                                        DTExcels.Rows.Add(drs);
                                        errorcount++;//插入失敗一條
                                    }
                                    #endregion
                                }
                                json = "{success:true,count:" + count + ",errorcount:" + errorcount + "}";
                            }
                    }                
                    else
                    {
                        json = "{success:true,total:" + 0 + ",error:" + 0 + ",entercount:" + 0 + "}";
                    }
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,data:" + "" + "}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Пример #16
0
        public HttpResponseBase IlocUploadExcel()
        {
            string newName = string.Empty;
            string json = string.Empty;
            List<IlocQuery> store = new List<IlocQuery>();
            HashEncrypt hashpt = new HashEncrypt();
            try
            {

                DTIlocExcel.Clear();
                DTIlocExcel.Columns.Clear();
                DTIlocExcel.Columns.Add("料位編號", typeof(String));
                DTIlocExcel.Columns.Add("料位類型", typeof(String));
                DTIlocExcel.Columns.Add("所在層數");
                DTIlocExcel.Columns.Add("不能匯入的原因", typeof(String));
                int result = 0;
                int count = 0;//總匯入數
                int entercount = 0;//插入失敗個數
                int errorcount = 0;//數據異常個數
                int create_user = (Session["caller"] as Caller).user_id;
                if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
                {
                    HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
                    newName = Server.MapPath(excelPath) + excelFile.FileName;
                    excelFile.SaveAs(newName);
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
                    dt = helper.SheetData();
                    if (dt.Rows.Count > 0)
                    {
                        _IlocMgr = new IlocMgr(mySqlConnectionString);
                        _IiplasMgr = new IplasMgr(mySqlConnectionString);

                        int i = 0;
                        foreach (DataRow dr in dt.Rows)
                        {
                            StringBuilder strsql = new StringBuilder();
                            Iloc ic = new BLL.gigade.Model.Iloc();
                            i++;
                            try
                            {
                                if (!string.IsNullOrEmpty(dr[0].ToString()) && Regex.IsMatch(dr[0].ToString(), @"^[A-Z]{2}\d{3}[A-Z]\d{2}$") && !string.IsNullOrEmpty(dr[1].ToString()) && (dr[1].ToString() == "S" || dr[1].ToString() == "R"))
                                {
                                    int loc_id_exsit = _IiplasMgr.YesOrNoLocIdExsit(dr[0].ToString());//判斷料位是否存在
                                    ic.loc_id = dr[0].ToString();
                                    ic.lsta_id = "F";
                                    ic.lcat_id = dr[1].ToString();
                                    ic.create_dtim = DateTime.Now;
                                    ic.change_dtim = DateTime.Now;
                                    ic.create_user = create_user;
                                    ic.change_user = create_user;
                                    ic.loc_status = 1;
                                    ic.lev = GetIntByString(dr[2].ToString());
                                    if (loc_id_exsit > 0)
                                    {
                                        DataRow drtwo = DTIlocExcel.NewRow();
                                        drtwo[0] = dr[0].ToString();
                                        drtwo[1] = dr[1].ToString();
                                        drtwo[2] = dr[2].ToString();
                                        drtwo[3] = "該料位已存在";
                                        DTIlocExcel.Rows.Add(drtwo);
                                        errorcount++;
                                        continue;
                                    }
                                    else//料位不存在
                                    {
                                        string has = hashpt.Md5Encrypt(ic.loc_id, "16");

                                        strsql.AppendFormat(@"insert into iloc(dc_id,whse_id,loc_id,llts_id,bkfill_loc,ldes_id,
                                             ldim_id,x_coord,y_coord,z_coord,bkfill_x_coord,bkfill_y_coord,
                                             bkfill_z_coord,lsta_id,sel_stk_pos,sel_seq_loc,sel_pos_hgt,rsv_stk_pos,
                                             rsv_pos_hgt,stk_lmt,stk_pos_wid,lev,lhnd_id,ldsp_id,
                                             create_user,create_dtim,comingle_allow,change_user,change_dtim,lcat_id,
                                             space_remain,max_loc_wgt,loc_status,stk_pos_dep,hash_loc_id
                    ) values ('{0}','{1}','{2}','{3}','{4}','{5}',
                              '{6}','{7}','{8}','{9}','{10}','{11}',
                              '{12}','{13}','{14}','{15}','{16}','{17}',
                              '{18}','{19}','{20}','{21}','{22}','{23}',
                              '{24}','{25}','{26}','{27}','{28}','{29}',
                              '{30}','{31}','{32}','{33}','{34}');",
                             ic.dc_id, ic.whse_id, ic.loc_id, ic.llts_id, ic.bkfill_loc, ic.ldes_id,
                             ic.ldim_id, ic.x_coord, ic.y_coord, ic.z_coord, ic.bkfill_x_coord, ic.bkfill_y_coord,
                             ic.bkfill_z_coord, ic.lsta_id, ic.sel_stk_pos, ic.sel_seq_loc, ic.sel_pos_hgt, ic.rsv_stk_pos,
                             ic.rsv_pos_hgt, ic.stk_lmt, ic.stk_pos_wid, ic.lev, ic.lhnd_id, ic.ldsp_id,
                             ic.create_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ic.create_dtim), ic.comingle_allow, ic.change_user, BLL.gigade.Common.CommonFunction.DateTimeToString(ic.change_dtim), ic.lcat_id,
                             ic.space_remain, ic.max_loc_wgt, ic.loc_status, ic.stk_pos_dep, has
                             );
                                        result = _IlocMgr.SaveBySql(strsql.ToString());
                                        if (result > 0)
                                        {
                                            count++;
                                            continue;
                                        }
                                        else
                                        {
                                            DataRow drtwo = DTIlocExcel.NewRow();
                                            drtwo[0] = dr[0].ToString();
                                            drtwo[1] = dr[1].ToString();
                                            drtwo[2] = dr[2].ToString();
                                            drtwo[3] = "料位插入數據庫時失敗";
                                            DTIlocExcel.Rows.Add(drtwo);
                                            entercount++;
                                            continue;
                                        }
                                    }
                                }
                                else
                                {
                                    DataRow drtwo = DTIlocExcel.NewRow();
                                    drtwo[0] = dr[0].ToString();
                                    drtwo[1] = dr[1].ToString();
                                    drtwo[2] = dr[2].ToString();
                                    drtwo[3] = "料位編號或者料位類型或者所在層數不符合格式";
                                    DTIlocExcel.Rows.Add(drtwo);
                                    errorcount++;
                                    continue;
                                }
                            }
                            catch
                            {
                                DataRow drtwo = DTIlocExcel.NewRow();
                                drtwo[0] = dr[0].ToString();
                                drtwo[1] = dr[1].ToString();
                                drtwo[2] = dr[2].ToString();
                                drtwo[3] = "數據異常";
                                DTIlocExcel.Rows.Add(drtwo);
                                errorcount++;
                                continue;
                            }
                        }
                        if (count > 0)
                        {
                            json = "{success:true,total:" + count + ",error:" + errorcount + ",entercount:" + entercount + "}";
                        }
                        else
                        {
                            json = "{success:true,total:" + 0 + ",error:" + errorcount + ",entercount:" + entercount + "}";
                        }
                    }
                    else
                    {
                        json = "{success:true,total:" + 0 + ",error:" + 0 + ",entercount" + 0 + "}";
                    }
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,data:" + "" + "}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Пример #17
0
        /// <summary>
        /// 上傳文件,匯入到datatable里
        /// </summary>
        /// <returns></returns>
        public HttpResponseBase UploadExcel()
        {
            string newName = string.Empty;
            string json = string.Empty;
            productStockStores = new List<ProductStockQuery>();
            try
            {
                if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
                {
                    HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
                    //FileManagement fileManagement = new FileManagement();
                    newName = Server.MapPath(excelPath) + excelFile.FileName;
                    excelFile.SaveAs(newName);
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
                    dt = helper.SheetData();
                    if (dt.Rows.Count > 0)
                    {
                        ProductStockQuery ps;
                        _pStockMgr = new ProductStockImportMgr(connectionString);
                        foreach (DataRow dr in dt.Rows)
                        {
                            ps = new ProductStockQuery();
                            ps.item_id = dr["細項編號"].ToString();
                            ps.product_id = dr["商品編號"].ToString();
                            ps.item_stock = dr["庫存"].ToString();
                            ps.item_alarm = dr["警告值"].ToString();
                            ps.product_name = dr["商品名稱"].ToString();
                            ps.spec_name1 = dr["規格1"].ToString();
                            ps.spec_name2 = dr["規格2"].ToString();
                            ps.product_mode = dr["出貨方式"].ToString();
                            ps.prepaid = dr["已買斷商品"].ToString();
                            ps.spec_status = dr["規格1顯示狀態"].ToString();
                            ps.spec_status2 = dr["規格2顯示狀態"].ToString();
                            //if (ps.spec_status == "顯示")
                            //{
                            //    ps.spec_status = "1";
                            //}
                            //else if (ps.spec_status == "隱藏")
                            //{
                            //    ps.spec_status = "0";
                            //}
                            //else {
                            //    ps.spec_status = string.Empty;
                            //}
                            //if (ps.spec_status2 == "顯示")
                            //{
                            //    ps.spec_status2 = "1";
                            //}
                            //else if (ps.spec_status2 == "隱藏")
                            //{
                            //    ps.spec_status2 = "0";
                            //}
                            //else
                            //{
                            //    ps.spec_status2 = string.Empty;
                            //}
                            ps.remark = dr["備註"].ToString();

                            if (dr[11].ToString() == "寄倉" || dr[12].ToString() == "是")
                            {
                                ps.type = 1;
                            }
                            productStockStores.Add(ps);
                        }
                    }
                    json = "{success:true,data:" + JsonConvert.SerializeObject(productStockStores, Formatting.Indented) + "}";
                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,data:" + "" + "}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
Пример #18
0
 public HttpResponseBase InsertkucunMessage()
 {
     int j = 0;
     string json = string.Empty;//json字符串
     int iialgtotal = 0;
     int iinvdtotal = 0;
     try
     {
         if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
         {
             HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
             FileManagement fileManagement = new FileManagement();//實例化 FileManagement
             //string fileLastName = excelFile.FileName.Substring((excelFile.FileName).LastIndexOf('.')).ToLower().Trim();
             string fileLastName = excelFile.FileName;
             string newExcelName = Server.MapPath(excelPath) + "Kucuntiaozheng" + fileManagement.NewFileName(excelFile.FileName);//處理文件名,獲取新的文件名
             excelFile.SaveAs(newExcelName);//上傳文件
             DataTable dt = new DataTable();
             NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
             dt = helper.SheetData();
             DataRow[] dr = dt.Select(); //定义一个DataRow数组,读取ds里面所有行
             int rowsnum = dt.Rows.Count;
             if (rowsnum == 0)
             {
                 json = "{success:true,iialgtotal:\"" + 0 + "\",iinvdtotal:\"" + 0 + "\",msg:\"" + "匯入盤點報表中沒有數據" + "\"}";
             }
             if (dr[0][0].ToString().Trim() == "編號" && dr[0][1].ToString().Trim() == "料位" && dr[0][2].ToString().Trim() == "料位狀態" && dr[0][11].ToString().Trim() == "有效日期" && dt.Columns.Count == 12)
             {
                 _iagMgr = new IialgMgr(mySqlConnectionString);
                 j = _iagMgr.HuiruInsertiialg(dr, out iialgtotal, out iinvdtotal);
                 if (j > 0)
                 {
                     json = "{success:true,iialgtotal:\"" + iialgtotal + "\",iinvdtotal:\"" + iinvdtotal + "\",msg:\"" + "匯入盤點報表對照表成功!" + "\"}";
                 }
                 else
                 {
                     json = "{success:true,iialgtotal:\"" + iialgtotal + "\",iinvdtotal:\"" + iinvdtotal + "\",msg:\"" + "匯入數據標準不對,請嚴格按照模板匯入!" + "\"}";
                 }
             }
             else
             {
                 json = "{success:true,iialgtotal:\"" + 0 + "\",iinvdtotal:\"" + 0 + "\",msg:\"" + "匯入盤點報表格式不正確!" + "\"}";
             }
         }
     }
     catch (Exception ex)
     {
         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
         log.Error(logMessage);
         json = "{success:false,msg:\"" + ex.ToString() + "\"}";
     }
     this.Response.Clear();
     this.Response.Write(json);
     this.Response.End();
     return this.Response;
 }
Пример #19
0
        //public DataSet ExecleDs(string filenameurl, string table)
        //{
        //    try //如果存在問題 比如說隨便輸入一個execl檔
        //    {
        //        string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
        //        OleDbConnection conn = new OleDbConnection(strConn);
        //        conn.Open();
        //        DataSet ds = new DataSet();
        //        string tablename = "象印";
        //        //這裡表可能是漢字.解決方法("select * from ["+ tablename+"$]")
        //        OleDbDataAdapter odda = new OleDbDataAdapter("select * from [" + tablename + "$]", conn);
        //        //OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
        //        odda.Fill(ds, table);
        //        return ds;
        //    }
        //    catch(Exception ex)
        //    {
        //        DataSet dsSet = new DataSet();
        //        DataTable dtTable = new DataTable();
        //        dtTable = dsSet.Tables.Add();
        //        return dsSet;
        //    }     
        //}
        #endregion

        public HttpResponseBase InsertBoilerMessage()
        {
            int j = 0;
            string json = string.Empty;//json字符串
            int total=0;
            try
            {
                if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
                {
                    HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
                    FileManagement fileManagement = new FileManagement();//實例化 FileManagement
                    //string fileLastName = excelFile.FileName.Substring((excelFile.FileName).LastIndexOf('.')).ToLower().Trim();
                    string fileLastName = excelFile.FileName;
                    string newExcelName = Server.MapPath(excelPath) + "BoilerRelation" + fileManagement.NewFileName(excelFile.FileName);//處理文件名,獲取新的文件名
                    excelFile.SaveAs(newExcelName);//上傳文件
                    DataTable dt = new DataTable();
                    NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
                    dt = helper.SheetData();
                    //DataSet ds = ExecleDs(newExcelName, fileLastName);//调用自定义方法
                    ////excelHelper = new NPOI4ExcelHelper(newExcelName);
                    DataRow[] dr = dt.Select(); //定义一个DataRow数组,读取ds里面所有行
                    int rowsnum = dt.Rows.Count;
                    if(rowsnum !=0)//判斷是否是這個表
                    {
                     if (dr[0][2].ToString().Trim() != "外鍋型號 (依款式&字母順序排列)" && dr[0][3].ToString().Trim() != "內鍋型號" && dr[0][4].ToString().Trim() != "對應安康內鍋型號" && dr[0][5].ToString().Trim() != "備註")
                    {
                        rowsnum = 0;
                    }
                    }
                   
                    if (rowsnum == 0)
                    {
                        json = "{success:true,total:0,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
                        this.Response.Clear();
                        this.Response.Write(json);
                        this.Response.End();
                        return this.Response;
                    }
                    else
                    {
                        _boillationMgr = new BoilerrelationMgr(mySqlConnectionString);
                        j = _boillationMgr.GetintoBoilerrelation(dr,out total);
                        if (j > 0)
                        {
                            json = "{success:true,total:\"" + total + "\",msg:\"" + "匯入安康內鍋型號對照表成功!" + "\"}";
                        }
                        else
                        {
                            json = "{success:true,total:\"" + total + "\",msg:\"" + "匯入數據標準不對,請嚴格按照模板匯入!" + "\"}";
                        }
                    
                    }
                }
                else//當直接點擊時會產生,
                {
                    json = "{success:true,msg:\"" + "請匯入安康內鍋型號對照表" + "\"}";
                    this.Response.Clear();
                    this.Response.Write(json);
                    this.Response.End();
                    return this.Response;

                }
            }
            catch (Exception ex)
            {
                Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
                logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
                logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
                log.Error(logMessage);
                json = "{success:false,msg:\"" + ex.ToString() + "\"}";
            }
            this.Response.Clear();
            this.Response.Write(json);
            this.Response.End();
            return this.Response;
        }
 public HttpResponseBase ImportExcel()
 {
     string newName = string.Empty;
     string json = string.Empty;
     InspectionReportQuery query = new InspectionReportQuery();
     CertificateCategoryQuery ccquery = new CertificateCategoryQuery();
     List<CertificateCategoryQuery> CClist = new List<CertificateCategoryQuery>();
     List<CertificateCategoryQuery> CClistTwo = new List<CertificateCategoryQuery>();
     _inspectionReport = new BLL.gigade.Mgr.InspectionReportMgr(mySqlConnectionString);
     try
     {
         if (Request.Files["ImportExcelFile"] != null && Request.Files["ImportExcelFile"].ContentLength > 0)
         {
             HttpPostedFileBase excelFile = Request.Files["ImportExcelFile"];
             newName = Server.MapPath(excelPath) + excelFile.FileName;
             excelFile.SaveAs(newName);
             DataTable dt = new DataTable();
             NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newName);
             dt = helper.SheetData();
             uint brandID = 0;
             int truenum = 0;
             json = "{success:true,msg:0,total:0}";
             if (dt.Rows.Count > 0)
             {
                 #region 循環數據判斷是否符合要求
                 foreach (DataRow dr in dt.Rows)
                 {
                     if (string.IsNullOrEmpty(dr[0].ToString()))
                     {
                         continue;
                     }
                     uint num = 0;
                     if (uint.TryParse(dr[0].ToString(), out num))
                     {
                         query.product_id = num;
                         if (_inspectionReport.GetProductById(query))//判斷商品id是否存在
                         {
                             brandID= _inspectionReport.GetBrandId(query.product_id);
                             ccquery.certificate_categorycode = dr[1].ToString();//大類code是否存在
                             CClist = _inspectionReport.CheckBigCode(ccquery);
                             if (CClist != null && CClist.Count > 0)
                             {
                                 query.certificate_type1 = CClist[0].rowID.ToString();
                                 ccquery.frowID = CClist[0].rowID;
                                 ccquery.certificate_category_childcode = dr[2].ToString();
                                 CClistTwo = _inspectionReport.GetLsit(ccquery);
                                 if (CClistTwo != null && CClistTwo.Count > 0)//小類code是否存在
                                 {
                                     query.certificate_type2 = CClistTwo[0].rowID.ToString();
                                     DateTime dtime = DateTime.MinValue;
                                     if (DateTime.TryParse(dr[3].ToString(), out dtime))//判斷有校時間格式是否正確
                                     {
                                         query.certificate_expdate = dtime;
                                         query.certificate_desc = dr[4].ToString();
                                         query.certificate_filename=brandID.ToString()+"-"+query.product_id.ToString()+"-"+ccquery.certificate_categorycode+"-"+ccquery.certificate_category_childcode+".jpg";
                                         if (!string.IsNullOrEmpty(dr[5].ToString())&&query.certificate_filename==dr[5].ToString())//判斷檔案是否為空,對應的檔案名稱是否正確
                                         {
                                             query.certificate_filename = dr[5].ToString();
                                             query.k_user = (Session["caller"] as Caller).user_id;
                                             query.m_user = (Session["caller"] as Caller).user_id;
                                             query.k_date = DateTime.Now;
                                             query.m_date = DateTime.Now;
                                             if (!_inspectionReport.CheckInspectionReport(query))//判斷數據是否已存在
                                             {
                                                 if (_inspectionReport.InsertInspectionReport(query))//新增數據
                                                 {
                                                     truenum++;//一共新增成功多少條數據
                                                 }
                                             }
                                             //
                                             //else
                                             //{
                                             //    if (_inspectionReport.UpdateInspectionReport(query))//新增數據
                                             //    {
                                             //        truenum++;//一共新增成功多少條數據
                                             //    }
                                             //    else
                                             //    {
                                             //        json = "{success:false}";
                                             //    }
                                             //}
                                             //
                                         }
                                     }
                                 }
                             }
                         }
                     }
                 }
                 #endregion
                 json = "{success:true,msg:1,total:" + truenum + "}";
             }
             else
             {
                 json = "{success:true,msg:3:total:0}";
             }
         }
     }
     catch (Exception ex)
     {
         Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
         logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
         logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
         log.Error(logMessage);
         json = "{success:false}";
     }
     this.Response.Clear();
     this.Response.Write(json);
     this.Response.End();
     return this.Response;
 }