public string TM_DAOCHU(string data) { string token = AppClass.GetSession("token").ToString(); int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID")); DaoRuMsg msg = new DaoRuMsg(); try { MES_TM_TMINFO_LIST[] model = Newtonsoft.Json.JsonConvert.DeserializeObject <MES_TM_TMINFO_LIST[]>(data); FileStream file = new FileStream(Server.MapPath("~") + @"/Areas/WMS/ExportFile/成品货物标识导出模板.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = new XSSFWorkbook(file); ISheet worksheet1 = workbook.GetSheet("成品货物标识"); if (worksheet1 == null) { msg.Msg = "err"; msg.Info = "工作薄中没有工作表"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } //worksheet1.AddMergedRegion(new CellRangeAddress(1, 2, 3, 4)); int row1 = 1; for (int i = 0; i < model.Length; i++) { NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1); row.CreateCell(0).SetCellValue(model[i].TM); row.CreateCell(1).SetCellValue(model[i].WLH); row.CreateCell(2).SetCellValue(model[i].WLMS); row.CreateCell(3).SetCellValue(model[i].PC); row.CreateCell(4).SetCellValue(model[i].NOBILL); row.CreateCell(5).SetCellValue(model[i].NOBILLMX); row.CreateCell(6).SetCellValue(Convert.ToDouble(model[i].DCSLBS.ToString())); row.CreateCell(7).SetCellValue(Convert.ToDouble(model[i].DCSLMBSL.ToString())); row.CreateCell(8).SetCellValue(Convert.ToDouble(model[i].ALLBOXCOUNT.ToString())); row.CreateCell(9).SetCellValue(model[i].GC); row.CreateCell(10).SetCellValue(model[i].KCDDNAME); row.CreateCell(11).SetCellValue(model[i].WLPZND); row.CreateCell(12).SetCellValue(model[i].WLPZBH); row.CreateCell(13).SetCellValue(model[i].WLPZHXMH); row.CreateCell(14).SetCellValue(Convert.ToDouble(model[i].RESDUESL.ToString())); row.CreateCell(15).SetCellValue(Convert.ToDouble(model[i].SL.ToString())); row1++; } worksheet1.ForceFormulaRecalculation = true; string now = DateTime.Now.ToString("yyyy-MM"); FileStream file1 = new FileStream(string.Format(@"{0}/Areas/WMS/ExportFile/{1}.xlsx", Server.MapPath("~"), now), FileMode.Create); workbook.Write(file1); file1.Close(); msg.Msg = now; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } catch (Exception e) { msg.Msg = "err"; msg.Info = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string TQGZ_DAOCHU(string data) { string token = AppClass.GetSession("token").ToString(); int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID")); DaoRuMsg msg = new DaoRuMsg(); try { WMS_JPD_JHD_TQGZ[] model = Newtonsoft.Json.JsonConvert.DeserializeObject <WMS_JPD_JHD_TQGZ[]>(data); FileStream file = new FileStream(Server.MapPath("~") + @"/Areas/WMS/ExportFile/过账授权导出模板.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = new XSSFWorkbook(file); ISheet worksheet1 = workbook.GetSheet("过账授权"); if (worksheet1 == null) { msg.Msg = "err"; msg.Info = "工作薄中没有工作表"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } //worksheet1.AddMergedRegion(new CellRangeAddress(1, 2, 3, 4)); int row1 = 1; for (int i = 0; i < model.Length; i++) { int count = 0; NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1); row.CreateCell(count++).SetCellValue(model[i].JHD); row.CreateCell(count++).SetCellValue(model[i].XSZZ); row.CreateCell(count++).SetCellValue(model[i].XSZZNAME); row.CreateCell(count++).SetCellValue(model[i].SDF); row.CreateCell(count++).SetCellValue(model[i].SDFNAME); row.CreateCell(count++).SetCellValue(model[i].KFZT == 1 ? "未开放" : "已开放"); row.CreateCell(count++).SetCellValue(model[i].CJTIME); row.CreateCell(count++).SetCellValue(model[i].CJRNAME); row.CreateCell(count++).SetCellValue(model[i].XGTIME); row.CreateCell(count++).SetCellValue(model[i].XGRNAME); row1++; } worksheet1.ForceFormulaRecalculation = true; string now = DateTime.Now.ToString("yyyy-MM"); FileStream file1 = new FileStream(string.Format(@"{0}/Areas/WMS/ExportFile/{1}.xlsx", Server.MapPath("~"), now), FileMode.Create); workbook.Write(file1); file1.Close(); msg.Msg = now; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } catch (Exception e) { msg.Msg = "err"; msg.Info = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string Data_FileUpload_Staff_DaoChu(string data) { token = appclass.CRM_Gettoken(); try { CRM_HG_STAFFList[] model = JsonConvert.DeserializeObject <CRM_HG_STAFFList[]>(data); FileStream file = new FileStream(FileSavePath + @"\ExcelTemplate/人员导出模版.xls", FileMode.Open, FileAccess.Read); IWorkbook workbook = new HSSFWorkbook(file); ISheet worksheet1 = workbook.GetSheet("人员"); if (worksheet1 == null) { return("工作薄中没有工作表"); } int row1 = 0; for (int i = 0; i < model.Length; i++) { NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1 + 1); row.CreateCell(0).SetCellValue(model[i].STAFFID); string STAFFLXDES = crmModels.HG_DICT.ReadByDICID(model[i].STAFFLX, token).DICNAME; row.CreateCell(1).SetCellValue(STAFFLXDES); //switch (model[i].STAFFLX) //{ // case 1: // row.CreateCell(1).SetCellValue("驻外业务员"); // break; // case 2: // row.CreateCell(1).SetCellValue("办公室人员"); // break; // case 3: // row.CreateCell(1).SetCellValue("直营卖场促销员"); // break; // case 4: // row.CreateCell(1).SetCellValue("客户业务员"); // break; //} row.CreateCell(2).SetCellValue(model[i].STAFFNAME); row.CreateCell(3).SetCellValue(model[i].STAFFNO); string DEPNAME = crmModels.HG_DEPT.ReadByDEPID(model[i].DEPID, token).DEPNAME; row.CreateCell(4).SetCellValue(DEPNAME); string STAFFZZMMDES = crmModels.HG_DICT.ReadByDICID(model[i].STAFFZZMM, token).DICNAME; row.CreateCell(5).SetCellValue(STAFFZZMMDES); switch (model[i].STAFFSEX) { case 1: row.CreateCell(6).SetCellValue("男"); break; case 2: row.CreateCell(6).SetCellValue("女"); break; } string STAFFXLDES = crmModels.HG_DICT.ReadByDICID(model[i].STAFFXL, token).DICNAME; row.CreateCell(7).SetCellValue(STAFFXLDES); switch (model[i].STAFFHYZK) { case 1: row.CreateCell(8).SetCellValue("未婚"); break; case 2: row.CreateCell(8).SetCellValue("已婚"); break; } row.CreateCell(9).SetCellValue(model[i].STAFFEMAIL); row.CreateCell(10).SetCellValue(model[i].STAFFTEL); row.CreateCell(11).SetCellValue(model[i].STAFFJG); row.CreateCell(12).SetCellValue(model[i].STAFFSFZH); row.CreateCell(13).SetCellValue(model[i].STAFFSR); row.CreateCell(14).SetCellValue(model[i].STAFFRZSJ); row.CreateCell(15).SetCellValue(model[i].STAFFMPSJ); row.CreateCell(16).SetCellValue(model[i].STAFFCZDZ); row.CreateCell(17).SetCellValue(model[i].STAFFBYYX); string STAFFZWJBDES = crmModels.HG_DICT.ReadByDICID(model[i].STAFFZWJB, token).DICNAME; row.CreateCell(18).SetCellValue(STAFFZWJBDES); string STAFFGWDES = crmModels.HG_DICT.ReadByDICID(model[i].STAFFGW, token).DICNAME; row.CreateCell(19).SetCellValue(STAFFGWDES); string BZNAME = crmModels.HG_BZGZSJ.ReadByBZID(model[i].BZID, token).BZNAME; row.CreateCell(20).SetCellValue(BZNAME); string MS = crmModels.KQ_GZRL.Read("", model[i].BBID, token)[0].MS; row.CreateCell(21).SetCellValue(MS); row.CreateCell(22).SetCellValue(model[i].STAFFWORK); row.CreateCell(23).SetCellValue(model[i].STAFFMEMO); row1++; } worksheet1.ForceFormulaRecalculation = true; string now = DateTime.Now.ToString("yyyyMMddHHmmss"); FileStream file1 = new FileStream(FileSavePath + @"\upload\" + now + ".xls", FileMode.Create); workbook.Write(file1); file1.Close(); DaoRuMsg msg = new DaoRuMsg(); msg.Msg = now + ".xls"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } catch //(Exception e) { DaoRuMsg msg = new DaoRuMsg(); msg.Msg = "err"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
//<summary> //批量修改人员主数据 //</summary> //<returns></returns> public string Data_DaoRu_Staff_Update() { token = appclass.CRM_Gettoken(); var file = Request.Files[0]; //string date = DateTime.Now.ToString("yyyyMMddHHmmss"); //string fileName = date + "_" + KHID; string year = DateTime.Now.Year.ToString(); string month = DateTime.Now.Month.ToString(); string gotname = file.FileName; //string[] name = gotname.Split('.'); //int count = name.Length - 1; //string savePath = @"E:\CRM\Areas\CRM\upload\" + year + @"\" + month + @"\" + fileName + "." + name[count]; string savePath = FileSavePath + @"\excel\" + year + @"\" + month + @"\" + gotname; file.SaveAs(savePath); FileInfo fi = new FileInfo(savePath); if (fi.Exists == true) { string[] sheet = { "人员" }; DaoRuMsg msg = new DaoRuMsg(); //开始做数据校验 DataTable data1 = ExcelToDataTable(savePath, sheet[0], true); //人员 #region if (data1 != null) { if (data1.Columns.Contains("人员类型") == false || data1.Columns.Contains("姓名") == false || data1.Columns.Contains("人员编号") == false) { msg.Msg = "请使用人员修改模版!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { //做数据验证 if (data1.Rows.Count > 0) { for (int i = 0; i < data1.Rows.Count; i++) { if (data1.Rows[i]["人员编号"].ToString() == "") { msg.Msg = "人员第" + i + 2 + "行人员编号不能为空!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } int STAFFID = crmModels.HG_STAFF.ReadBySTAFFID(Convert.ToInt32(data1.Rows[i]["人员编号"]), token).STAFFID; if (STAFFID == 0) { msg.Msg = "人员第" + i + 2 + "行人员编号不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (data1.Rows[i]["人员类型"].ToString() == "") { msg.Msg = "人员第" + i + 2 + "行人员类型不能为空!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } int STAFFLX = crmModels.HG_DICT.ReadByDICNAME(data1.Rows[i]["人员类型"].ToString(), 33, token); if (STAFFLX == 0) //if (data1.Rows[i]["人员类型"].ToString() != "业务员" && data1.Rows[i]["人员类型"].ToString() != "办公室人员" && data1.Rows[i]["人员类型"].ToString() != "直营卖场促销员" && data1.Rows[i]["人员类型"].ToString() != "客户业务员" && data1.Rows[i]["人员类型"].ToString() != "管理人员") { msg.Msg = "人员第" + i + 2 + "行人员类型错误!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (data1.Rows[i]["性别"].ToString() != "男" && data1.Rows[i]["性别"].ToString() != "女") { msg.Msg = "人员第" + i + 2 + "行性别错误!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (data1.Rows[i]["婚姻状况"].ToString() != "未婚" && data1.Rows[i]["婚姻状况"].ToString() != "已婚") { msg.Msg = "人员第" + i + 2 + "行婚姻状况错误!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } int DEPID = crmModels.HG_DEPT.ReadByName(data1.Rows[i]["部门"].ToString(), token).DEPID; if (DEPID == 0) { msg.Msg = "人员第" + i + 2 + "行部门不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } int BZID = crmModels.HG_BZGZSJ.ReadByBZNAME(data1.Rows[i]["班次"].ToString(), token).BZID; if (BZID == 0) { msg.Msg = "人员第" + i + 2 + "行班次不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } int BBID = crmModels.KQ_GZRL.Read(data1.Rows[i]["日历版本"].ToString(), 0, token)[0].BBID; if (BBID == 0) { msg.Msg = "人员第" + i + 2 + "行日历版本不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } int STAFFZZMM = crmModels.HG_DICT.ReadByDICNAME(data1.Rows[i]["政治面貌"].ToString(), 15, token); if (STAFFZZMM == 0) { msg.Msg = "人员第" + i + 2 + "行政治面貌不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } int STAFFXL = crmModels.HG_DICT.ReadByDICNAME(data1.Rows[i]["学历"].ToString(), 16, token); if (STAFFXL == 0) { msg.Msg = "人员第" + i + 2 + "行学历不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } //int STAFFZWJB = crmModels.HG_DICT.ReadByDICNAME(data1.Rows[i]["职务"].ToString(), 13, token); //if (STAFFZWJB == 0) //{ // msg.Msg = "人员第" + i + 2 + "行职务不存在!"; // return Newtonsoft.Json.JsonConvert.SerializeObject(msg); //} int STAFFGW = crmModels.HG_DICT.ReadByDICNAME(data1.Rows[i]["岗位"].ToString(), 14, token); if (STAFFGW == 0) { msg.Msg = "人员第" + i + 2 + "行岗位不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } } } #endregion //能到这儿就说明数据是没问题的了...大概,然后才进行数据库写入 #region for (int i = 0; i < data1.Rows.Count; i++) { CRM_HG_STAFF model = new CRM_HG_STAFF(); model = crmModels.HG_STAFF.ReadBySTAFFID(Convert.ToInt32(data1.Rows[i]["人员编号"]), token); model.STAFFLX = crmModels.HG_DICT.ReadByDICNAME(data1.Rows[i]["人员类型"].ToString(), 33, token); //switch (data1.Rows[i]["人员类型"].ToString()) //{ // case "驻外业务员": // model.STAFFLX = 1; // break; // case "办公室人员": // model.STAFFLX = 2; // break; // case "直营卖场促销员": // model.STAFFLX = 3; // break; // case "客户业务员": // model.STAFFLX = 4; // break; //} model.STAFFNAME = data1.Rows[i]["姓名"].ToString(); model.STAFFNO = data1.Rows[i]["工号"].ToString(); model.DEPID = crmModels.HG_DEPT.ReadByName(data1.Rows[i]["部门"].ToString(), token).DEPID; model.STAFFZZMM = crmModels.HG_DICT.ReadByDICNAME(data1.Rows[i]["政治面貌"].ToString(), 15, token); switch (data1.Rows[i]["性别"].ToString()) { case "男": model.STAFFSEX = 1; break; case "女": model.STAFFSEX = 2; break; } model.STAFFXL = crmModels.HG_DICT.ReadByDICNAME(data1.Rows[i]["学历"].ToString(), 16, token); switch (data1.Rows[i]["婚姻状况"].ToString()) { case "未婚": model.STAFFHYZK = 1; break; case "已婚": model.STAFFHYZK = 2; break; } model.STAFFEMAIL = data1.Rows[i]["邮箱地址"].ToString(); model.STAFFTEL = data1.Rows[i]["联系电话"].ToString(); model.STAFFJG = data1.Rows[i]["籍贯"].ToString(); model.STAFFSFZH = data1.Rows[i]["身份证号"].ToString(); model.STAFFSR = data1.Rows[i]["生日"].ToString(); model.STAFFRZSJ = data1.Rows[i]["入职时间"].ToString(); model.STAFFMPSJ = data1.Rows[i]["调入民品部时间"].ToString(); model.STAFFCZDZ = data1.Rows[i]["常住地址"].ToString(); model.STAFFBYYX = data1.Rows[i]["毕业院校"].ToString(); model.STAFFZWJB = crmModels.HG_DICT.ReadByDICNAME(data1.Rows[i]["职位"].ToString(), 13, token); model.STAFFGW = crmModels.HG_DICT.ReadByDICNAME(data1.Rows[i]["岗位"].ToString(), 14, token); model.BZID = crmModels.HG_BZGZSJ.ReadByBZNAME(data1.Rows[i]["班次"].ToString(), token).BZID; model.BBID = crmModels.KQ_GZRL.Read(data1.Rows[i]["日历版本"].ToString(), 0, token)[0].BBID; model.STAFFWORK = data1.Rows[i]["工作内容"].ToString(); model.STAFFMEMO = data1.Rows[i]["备注"].ToString(); //model.ISACTIVE = 1; int id = crmModels.HG_STAFF.Update(model, token); if (id <= 0) { msg.Msg = "人员的第" + i + 2 + "行出现问题,请记录当前报错信息并联系管理员"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } #endregion msg.Msg = "修改成功!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { return("{}"); } }
public string TMPrint_DAOCHU(string data) { string token = AppClass.GetSession("token").ToString(); int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID")); DaoRuMsg msg = new DaoRuMsg(); try { MES_TM_TMINFO_TSDY[] model = Newtonsoft.Json.JsonConvert.DeserializeObject <MES_TM_TMINFO_TSDY[]>(data); FileStream file = new FileStream(Server.MapPath("~") + @"/Areas/WMS/ExportFile/箱码数据查询打印导出模板.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = new XSSFWorkbook(file); ISheet worksheet1 = workbook.GetSheet("箱码数据"); if (worksheet1 == null) { msg.Msg = "err"; msg.Info = "工作薄中没有工作表"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } //worksheet1.AddMergedRegion(new CellRangeAddress(1, 2, 3, 4)); int row1 = 1; for (int i = 0; i < model.Length; i++) { int count = 0; NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1); row.CreateCell(count++).SetCellValue(model[i].ERPNO); row.CreateCell(count++).SetCellValue(model[i].WLH); row.CreateCell(count++).SetCellValue(model[i].WLMS); row.CreateCell(count++).SetCellValue(model[i].NOBILL); row.CreateCell(count++).SetCellValue(model[i].NOBILLMX); row.CreateCell(count++).SetCellValue(model[i].PC); row.CreateCell(count++).SetCellValue(model[i].KHWLH); row.CreateCell(count++).SetCellValue(model[i].KHWLMS); row.CreateCell(count++).SetCellValue(model[i].KHWLGG); row.CreateCell(count++).SetCellValue(model[i].TM); row.CreateCell(count++).SetCellValue(model[i].XCTM); row.CreateCell(count++).SetCellValue(model[i].SCDATE); row.CreateCell(count++).SetCellValue(model[i].XZS.ToString()); row.CreateCell(count++).SetCellValue(model[i].UNITSNAME); row.CreateCell(count++).SetCellValue(model[i].XBNAME); row.CreateCell(count++).SetCellValue(model[i].CJRNAME); row.CreateCell(count++).SetCellValue(model[i].CJTIME); row1++; } worksheet1.ForceFormulaRecalculation = true; string now = DateTime.Now.ToString("yyyy-MM"); FileStream file1 = new FileStream(string.Format(@"{0}/Areas/WMS/ExportFile/{1}.xlsx", Server.MapPath("~"), now), FileMode.Create); workbook.Write(file1); file1.Close(); msg.Msg = now; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } catch (Exception e) { msg.Msg = "err"; msg.Info = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string Data_Daochu_KaoQinSummaryAndSXB(string data, string starttime, string endtime) { token = appClass.CRM_Gettoken(); try { CRM_KQ_COLLECTList[] model = JsonConvert.DeserializeObject <CRM_KQ_COLLECTList[]>(data); FileStream file = new FileStream(FileSavePath + @"\ExcelTemplate/考勤报表.xls", FileMode.Open, FileAccess.Read); IWorkbook workbook = new HSSFWorkbook(file); ISheet worksheet1 = workbook.GetSheet("汇总"); ISheet worksheet2 = workbook.GetSheet("正常上下班"); ISheet worksheet3 = workbook.GetSheet("缺勤记录"); if (worksheet1 == null || worksheet2 == null || worksheet3 == null) { return("工作薄中没有工作表"); } //worksheet1.AddMergedRegion(new CellRangeAddress(1, 2, 3, 4)); int row1 = 1, row2 = 1, row3 = 1; for (int i = 0; i < model.Length; i++) { NPOI.SS.UserModel.IRow row_hz = worksheet1.CreateRow(row1); row_hz.CreateCell(0).SetCellValue(model[i].STAFFNAME); row_hz.CreateCell(1).SetCellValue(model[i].STAFFNO); row_hz.CreateCell(2).SetCellValue(model[i].DEPNAME); row_hz.CreateCell(3).SetCellValue(model[i].DAYCOUNTS); row_hz.CreateCell(4).SetCellValue(model[i].ZCDAYCOUNTS); row_hz.CreateCell(5).SetCellValue(model[i].CCDAYCOUNTS); row_hz.CreateCell(6).SetCellValue(model[i].QJDAYCOUNTS); row_hz.CreateCell(7).SetCellValue(model[i].YCDAYCOUNTS); row_hz.CreateCell(8).SetCellValue(model[i].QQDAYCOUNTS); row1++; CRM_KQ_QDList[] QDdata = crmModels.CRM_KQ_Report.CRM_KQ_Detail_QD(model[i].STAFFID, starttime, endtime, token); for (int j = 0; j < QDdata.Length; j++) { NPOI.SS.UserModel.IRow row_sxb = worksheet2.CreateRow(row2); row_sxb.CreateCell(0).SetCellValue(QDdata[j].QDGSIDDES); row_sxb.CreateCell(1).SetCellValue(QDdata[j].QDSJ); string sxbdes = ""; if (QDdata[j].SXB == 1 || QDdata[j].SXB == 10) { sxbdes = "上班"; } else { sxbdes = "下班"; } row_sxb.CreateCell(2).SetCellValue(sxbdes); row_sxb.CreateCell(3).SetCellValue(QDdata[j].QDWZ); row_sxb.CreateCell(4).SetCellValue(QDdata[j].KQJL); string isactivedes = ""; if (QDdata[j].ISACTIVE == 1) { isactivedes = "正常"; } else { isactivedes = "不在范围内"; } row_sxb.CreateCell(5).SetCellValue(isactivedes); row2++; } CRM_KQ_QQList[] QQdata = crmModels.CRM_KQ_Report.CRM_KQ_Detail_QQ(model[i].STAFFID, starttime, endtime, token); for (int j = 0; j < QQdata.Length; j++) { NPOI.SS.UserModel.IRow row_qq = worksheet3.CreateRow(row3); row_qq.CreateCell(0).SetCellValue(QQdata[j].STAFFNAME); row_qq.CreateCell(1).SetCellValue(QQdata[j].RQ); row_qq.CreateCell(2).SetCellValue(QQdata[j].SBQD == 1 ? "正常" : "无"); row_qq.CreateCell(3).SetCellValue(QQdata[j].XBQD == 1 ? "正常" : "无"); row3++; } } worksheet1.ForceFormulaRecalculation = true; worksheet2.ForceFormulaRecalculation = true; worksheet3.ForceFormulaRecalculation = true; string now = DateTime.Now.ToString("yyyyMMddHHmmss"); FileStream file1 = new FileStream(FileSavePath + @"\upload\" + now + ".xls", FileMode.Create); workbook.Write(file1); file1.Close(); DaoRuMsg msg = new DaoRuMsg(); msg.Msg = now + ".xls"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } catch (Exception e) { DaoRuMsg msg = new DaoRuMsg(); msg.Msg = "err:" + e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string Data_DaoRu_PXRY(int PXZTID) { int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID")); string token = AppClass.GetSession("token").ToString(); DaoRuMsg msg = new DaoRuMsg(); try { var file = Request.Files[0]; string year = DateTime.Now.Year.ToString(); string month = DateTime.Now.Month.ToString(); string gotname = file.FileName; string savePath = HRFile + @"\upload\excel\" + year + @"\" + month + @"\" + gotname; if (Directory.Exists(HRFile + @"\upload\excel\" + year + @"\" + month) == false) { Directory.CreateDirectory(HRFile + @"\upload\excel\" + year + @"\" + month); } file.SaveAs(savePath); FileInfo fi = new FileInfo(savePath); if (fi.Exists == true) { string[] sheet = { "培训人员导入" }; //开始做数据校验 DataTable data1 = ExcelToDataTable(savePath, 0, true); #region if (data1 != null) { if (data1.Columns.Contains("工号") == false) { msg.Msg = "请使用新增模版!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { try { //做数据验证 if (data1.Rows.Count > 0) { for (int i = 0; i < data1.Rows.Count; i++) { if (data1.Rows[i]["工号"].ToString() != "") { if (data1.Rows[i]["工号"].ToString().Length != 5) { msg.Msg = "工会导入第" + (i + 2) + "行工号格式不正确!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } HR_RY_RYINFO model = new HR_RY_RYINFO(); model.GH = data1.Rows[i]["工号"].ToString(); HR_RY_RYINFO_SELECT staffdata = hrmodels.RY_RYINFO.SELECT(model, token); if (staffdata.HR_RY_RYINFO_LIST.Length == 0) { msg.Msg = "工会导入第" + (i + 2) + "行工号不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } } } catch (Exception e) { msg.Msg = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } #endregion //能到这儿就说明数据是没问题的了...大概,然后才进行数据库写入 #region for (int i = 0; i < data1.Rows.Count; i++) { #region if (data1.Rows[i]["工号"].ToString() != "") { HR_RY_RYINFO insert_model = new HR_RY_RYINFO(); insert_model.GH = data1.Rows[i]["工号"].ToString(); HR_RY_RYINFO_SELECT staffdata = hrmodels.RY_RYINFO.SELECT(insert_model, token); HR_PX_PXZT model = new HR_PX_PXZT(); model.RYID = staffdata.HR_RY_RYINFO_LIST[0].RYID; model.PXZTID = PXZTID; model.CJR = STAFFID; MES_RETURN_UI result = hrmodels.PX_PXZT.INSERT_PXZTMX(model, token); if (result.TYPE == "E") { msg.Msg = "培训人员导入的第" + (i + 2) + "行出现问题,请记录当前报错信息并联系管理员"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } #endregion } #endregion msg.Msg = "新增成功!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { msg.Msg = "文件读取失败!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } catch (Exception e) { msg.Msg = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string Data_DaoRu_Invoice() { token = appClass.CRM_Gettoken(); DaoRuMsg msg = new DaoRuMsg(); try { var file = Request.Files[0]; //string date = DateTime.Now.ToString("yyyyMMddHHmmss"); //string fileName = date + "_" + KHID; string year = DateTime.Now.Year.ToString(); string month = DateTime.Now.Month.ToString(); string gotname = file.FileName; //string[] name = gotname.Split('.'); //int count = name.Length - 1; //string savePath = @"E:\CRM\Areas\CRM\upload\" + year + @"\" + month + @"\" + fileName + "." + name[count]; string savePath = FileSavePath + @"\excel\" + year + @"\" + month + @"\" + gotname; if (Directory.Exists(FileSavePath + @"\excel\" + year + @"\" + month) == false) { Directory.CreateDirectory(FileSavePath + @"\excel\" + year + @"\" + month); } file.SaveAs(savePath); FileInfo fi = new FileInfo(savePath); if (fi.Exists == true) { string[] sheet = { "发票" }; //开始做数据校验 DataTable data1 = ExcelToDataTable(savePath, sheet[0], true); //发票 #region if (data1 != null) { if (data1.Columns.Contains("发票号码") == false || data1.Columns.Contains("发票数量") == false || data1.Columns.Contains("快递单号") == false) { msg.Msg = "请使用发票新增模版!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { try { //做数据验证 if (data1.Rows.Count > 0) { for (int i = 0; i < data1.Rows.Count; i++) { bool sap404 = false; if (data1.Rows[i]["客户SAP编号"].ToString() != "" && crmModels.KH_KH.ReadBySAPSN(data1.Rows[i]["客户SAP编号"].ToString(), token).KHID == 0) { sap404 = true; } if (sap404) { msg.Msg = "发票第" + (i + 2) + "行客户SAP编号不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (Regex.IsMatch(data1.Rows[i]["发票数量"].ToString(), @"^\d+$") == false) { msg.Msg = "发票第" + (i + 2) + "行发票数量必须为全数字!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (data1.Rows[i]["寄送日期"].ToString() != "") { try { DateTime myDT = Convert.ToDateTime(data1.Rows[i]["寄送日期"].ToString()); } catch { msg.Msg = "发票第" + (i + 2) + "行寄送日期格式错误!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } } } catch (Exception e) { msg.Msg = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } #endregion //能到这儿就说明数据是没问题的了...大概,然后才进行数据库写入 #region //发票 for (int i = 0; i < data1.Rows.Count; i++) { #region CRM_KH_KH KHmodel = crmModels.KH_KH.ReadBySAPSN(data1.Rows[i]["客户SAP编号"].ToString(), token); CRM_MSG_INVOICE model = new CRM_MSG_INVOICE(); model.KHID = KHmodel.KHID; model.FPHM = data1.Rows[i]["发票号码"].ToString().Trim(); model.FPSL = Convert.ToInt32(data1.Rows[i]["发票数量"].ToString().Trim()); model.KDDH = data1.Rows[i]["快递单号"].ToString().Trim(); model.JSRQ = Convert.ToDateTime(data1.Rows[i]["寄送日期"].ToString()).ToString("yyyy-MM-dd"); model.BEIZ = data1.Rows[i]["备注"].ToString().Trim(); model.ISACTIVE = 1; model.CJR = appClass.CRM_GetStaffid(); int id = crmModels.MSG_INVOICE.Create(model, token); if (id <= 0) { msg.Msg = "新增发票的第" + (i + 2) + "行出现问题,请记录当前报错信息并联系管理员"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } #endregion } #endregion msg.Msg = "新增成功!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { msg.Msg = "文件读取失败!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } catch (Exception e) { msg.Msg = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string Data_Daochu_BaiFangSummaryAndMX(string cxdata, string starttime, string endtime) { token = appClass.CRM_Gettoken(); try { CRM_BF_REPORT_BYSTAFF_PARAMS data = Newtonsoft.Json.JsonConvert.DeserializeObject <CRM_BF_REPORT_BYSTAFF_PARAMS>(cxdata); CRM_BF_REPORT_BYSTAFF_SUMMARYTOTAL[] model = crmModels.BF_BF.ReadKHBF_ReportByStaff_SummaryTotal(data, token); FileStream file = new FileStream(FileSavePath + @"\ExcelTemplate/拜访报表-人员.xls", FileMode.Open, FileAccess.Read); IWorkbook workbook = new HSSFWorkbook(file); ISheet worksheet1 = workbook.GetSheet("汇总"); ISheet worksheet2 = workbook.GetSheet("各类型拜访次数"); ISheet worksheet3 = workbook.GetSheet("拜访明细"); if (worksheet1 == null || worksheet2 == null || worksheet3 == null) { return("工作薄中没有工作表"); } //worksheet1.AddMergedRegion(new CellRangeAddress(1, 2, 3, 4)); int row1 = 1, row2 = 1, row3 = 1; for (int i = 0; i < model.Length; i++) { NPOI.SS.UserModel.IRow row_hz = worksheet1.CreateRow(row1); row_hz.CreateCell(0).SetCellValue(model[i].STAFFNAME); row_hz.CreateCell(1).SetCellValue(model[i].STAFFNO); row_hz.CreateCell(2).SetCellValue(model[i].DEPNAME); row_hz.CreateCell(3).SetCellValue(model[i].TOTAL_COUNT); row1++; CRM_BF_REPORT_BYSTAFF_PARAMS cxmodel = new CRM_BF_REPORT_BYSTAFF_PARAMS(); cxmodel.STAFFID = model[i].STAFFID; cxmodel.BEGINDATE = starttime; cxmodel.ENDDATE = endtime; CRM_BF_REPORT_BYSTAFF_SUMMARY[] BFCSdata = crmModels.BF_BF.ReadKHBF_ReportByStaff_Summary(cxmodel, token); for (int j = 0; j < BFCSdata.Length; j++) { NPOI.SS.UserModel.IRow row_bfcs = worksheet2.CreateRow(row2); row_bfcs.CreateCell(0).SetCellValue(model[i].STAFFNAME); row_bfcs.CreateCell(1).SetCellValue(model[i].STAFFNO); row_bfcs.CreateCell(2).SetCellValue(model[i].DEPNAME); row_bfcs.CreateCell(3).SetCellValue(BFCSdata[j].BFLXDES); row_bfcs.CreateCell(4).SetCellValue(BFCSdata[j].FINISHCOUNTS); row2++; CRM_BF_REPORT_BYSTAFF_PARAMS cxmodel2 = new CRM_BF_REPORT_BYSTAFF_PARAMS(); cxmodel2.BEGINDATE = starttime; cxmodel2.ENDDATE = endtime; cxmodel2.STAFFID = model[i].STAFFID; cxmodel2.BFLX = BFCSdata[j].BFLX; CRM_BF_BFDJList[] MXdata = crmModels.BF_BF.ReadKHBF_ReportByStaff_Detail(cxmodel2, token); for (int k = 0; k < MXdata.Length; k++) { NPOI.SS.UserModel.IRow row_mx = worksheet3.CreateRow(row3); row_mx.CreateCell(0).SetCellValue(MXdata[k].BFLXDES); row_mx.CreateCell(1).SetCellValue(MXdata[k].BFJHMC); row_mx.CreateCell(2).SetCellValue(MXdata[k].KHMC); row_mx.CreateCell(3).SetCellValue(MXdata[k].CRMID); row_mx.CreateCell(4).SetCellValue(MXdata[k].KHLXDES); row_mx.CreateCell(5).SetCellValue(MXdata[k].STAFFNAME); row_mx.CreateCell(6).SetCellValue(MXdata[k].JHBFJSSJ); row_mx.CreateCell(7).SetCellValue(MXdata[k].BFDZ); row_mx.CreateCell(8).SetCellValue(MXdata[k].LXR); row_mx.CreateCell(9).SetCellValue(MXdata[k].LXRTEL); row_mx.CreateCell(10).SetCellValue(MXdata[k].LXRZWDES); row_mx.CreateCell(11).SetCellValue(MXdata[k].BFMDDES); row_mx.CreateCell(12).SetCellValue(MXdata[k].BFJGDES); row_mx.CreateCell(13).SetCellValue(MXdata[k].QTXX); row_mx.CreateCell(14).SetCellValue(MXdata[k].KQJL + "米"); row_mx.CreateCell(15).SetCellValue(MXdata[k].KQISACTIVE == 1 ? "是" : "否"); row3++; } } } worksheet1.ForceFormulaRecalculation = true; worksheet2.ForceFormulaRecalculation = true; worksheet3.ForceFormulaRecalculation = true; string now = DateTime.Now.ToString("yyyyMMddHHmmss"); FileStream file1 = new FileStream(FileSavePath + @"\upload\" + now + ".xls", FileMode.Create); workbook.Write(file1); file1.Close(); DaoRuMsg msg = new DaoRuMsg(); msg.Msg = now + ".xls"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } catch (Exception e) { DaoRuMsg msg = new DaoRuMsg(); msg.Msg = "err:" + e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string Data_DaoRu_ProductGroup() { token = appClass.CRM_Gettoken(); DaoRuMsg msg = new DaoRuMsg(); try { var file = Request.Files[0]; //string date = DateTime.Now.ToString("yyyyMMddHHmmss"); //string fileName = date + "_" + KHID; string year = DateTime.Now.Year.ToString(); string month = DateTime.Now.Month.ToString(); string gotname = file.FileName; //string[] name = gotname.Split('.'); //int count = name.Length - 1; //string savePath = @"E:\CRM\Areas\CRM\upload\" + year + @"\" + month + @"\" + fileName + "." + name[count]; string savePath = FileSavePath + @"\excel\" + year + @"\" + month + @"\" + gotname; if (Directory.Exists(FileSavePath + @"\excel\" + year + @"\" + month) == false) { Directory.CreateDirectory(FileSavePath + @"\excel\" + year + @"\" + month); } file.SaveAs(savePath); FileInfo fi = new FileInfo(savePath); if (fi.Exists == true) { string[] sheet = { "产品", "产品组", "客户产品组" }; //开始做数据校验 DataTable data1 = ExcelToDataTable(savePath, sheet[0], true); //产品 #region if (data1 != null) { if (data1.Columns.Contains("产品类型") == false || data1.Columns.Contains("产品系列") == false || data1.Columns.Contains("产品型号") == false) { msg.Msg = "请使用产品新增模版!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { try { //做数据验证 if (data1.Rows.Count > 0) { for (int i = 0; i < data1.Rows.Count; i++) { int cplx = crmModels.HG_DICT.ReadByDICNAMEandFID(data1.Rows[i]["产品类型"].ToString(), 53, 0, token); if (cplx == 0) { msg.Msg = "产品第" + (i + 2) + "行产品类型不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } int cpxl = crmModels.HG_DICT.ReadByDICNAMEandFID(data1.Rows[i]["产品系列"].ToString(), 54, cplx, token); if (cpxl == 0) { msg.Msg = "产品第" + (i + 2) + "行产品系列不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } int cpxh = crmModels.HG_DICT.ReadByDICNAMEandFID(data1.Rows[i]["产品型号"].ToString(), 55, cpxl, token); if (cpxh == 0) { msg.Msg = "产品第" + (i + 2) + "行产品型号不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (data1.Rows[i]["产品名称"].ToString() == "") { msg.Msg = "产品第" + (i + 2) + "行产品名称不可为空!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (data1.Rows[i]["产品图片"].ToString() == "") { msg.Msg = "产品第" + (i + 2) + "行产品图片不可为空!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } } catch (Exception e) { msg.Msg = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } #endregion DataTable data2 = ExcelToDataTable(savePath, sheet[1], true); //产品组 #region if (data2 != null) { if (data2.Columns.Contains("产品组") == false || data2.Columns.Contains("产品品号") == false) { msg.Msg = "请使用产品新增模版!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { try { //做数据验证 if (data2.Rows.Count > 0) { for (int i = 0; i < data2.Rows.Count; i++) { int cpz = crmModels.PRODUCT_GROUP.ReadByIDGroupName(data2.Rows[i]["产品组"].ToString(), token); if (cpz == 0) { msg.Msg = "产品组第" + (i + 2) + "行产品组不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } CRM_PRODUCT_PRODUCT cx = new CRM_PRODUCT_PRODUCT(); cx.CPPH = data2.Rows[i]["产品品号"].ToString(); CRM_PRODUCT_PRODUCT[] cp = crmModels.PRODUCT_PRODUCT.ReadByParam(cx, token); if (cp.Length == 0) { msg.Msg = "产品组第" + (i + 2) + "行产品品号不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (cp.Length != 1) { msg.Msg = "产品组第" + (i + 2) + "行产品品号存在复数目标!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } } catch (Exception e) { msg.Msg = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } #endregion DataTable data3 = ExcelToDataTable(savePath, sheet[2], true); //客户产品组 #region if (data3 != null) { if (data3.Columns.Contains("产品组") == false || data3.Columns.Contains("客户编号") == false || data3.Columns.Contains("客户SAP编号") == false) { msg.Msg = "请使用产品新增模版!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { try { //做数据验证 if (data3.Rows.Count > 0) { for (int i = 0; i < data3.Rows.Count; i++) { int cpz = crmModels.PRODUCT_GROUP.ReadByIDGroupName(data3.Rows[i]["产品组"].ToString(), token); if (cpz == 0) { msg.Msg = "客户产品组第" + (i + 2) + "行产品组不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (data3.Rows[i]["客户编号"].ToString() == "" && data3.Rows[i]["客户SAP编号"].ToString() == "") { msg.Msg = "客户产品组第" + (i + 2) + "行客户编号不可为空!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (data3.Rows[i]["客户编号"].ToString() != "") { CRM_KH_KH kh_model = crmModels.KH_KH.ReadByCRMID(data3.Rows[i]["客户编号"].ToString(), token); if (kh_model.KHID == 0) { msg.Msg = "客户产品组第" + (i + 2) + "行客户编号不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } if (data3.Rows[i]["客户SAP编号"].ToString() != "") { CRM_KH_KH kh_model = crmModels.KH_KH.ReadBySAPSN(data3.Rows[i]["客户SAP编号"].ToString(), token); if (kh_model.KHID == 0) { msg.Msg = "客户产品组第" + (i + 2) + "行客户SAP编号不存在!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } } } catch (Exception e) { msg.Msg = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } #endregion //能到这儿就说明数据是没问题的了...大概,然后才进行数据库写入 #region //产品 for (int i = 0; i < data1.Rows.Count; i++) { #region CRM_PRODUCT_PRODUCT model = new CRM_PRODUCT_PRODUCT(); model.CPLX = crmModels.HG_DICT.ReadByDICNAMEandFID(data1.Rows[i]["产品类型"].ToString().Trim(), 53, 0, token); model.CPXL = crmModels.HG_DICT.ReadByDICNAMEandFID(data1.Rows[i]["产品系列"].ToString().Trim(), 54, model.CPLX, token); model.CPXH = crmModels.HG_DICT.ReadByDICNAMEandFID(data1.Rows[i]["产品型号"].ToString().Trim(), 55, model.CPXL, token); model.CPPH = data1.Rows[i]["产品品号"].ToString().Trim(); model.CPMC = data1.Rows[i]["产品名称"].ToString().Trim(); model.CODE = data1.Rows[i]["条形码"].ToString().Trim(); model.DDDW = data1.Rows[i]["订单单位"].ToString().Trim(); model.BZDW = data1.Rows[i]["标准单位"].ToString().Trim(); model.RATE = Convert.ToInt32(data1.Rows[i]["转换率"].ToString().Trim()); model.ML = FileSavePath + @"\upload\img" + @"\CP\" + data1.Rows[i]["产品图片"].ToString().Trim(); model.ML_MOB = ""; model.BEIZ = ""; model.ISACTIVE = 1; model.CJR = appClass.CRM_GetStaffid(); int id = crmModels.PRODUCT_PRODUCT.Create(model, token); if (id <= 0) { msg.Msg = "新增产品的第" + (i + 2) + "行出现问题,请记录当前报错信息并联系管理员"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } #endregion } //产品组 for (int i = 0; i < data2.Rows.Count; i++) { #region CRM_PRODUCT_PRODUCT cx = new CRM_PRODUCT_PRODUCT(); cx.CPPH = data2.Rows[i]["产品品号"].ToString(); CRM_PRODUCT_PRODUCT[] cp = crmModels.PRODUCT_PRODUCT.ReadByParam(cx, token); int cpz = crmModels.PRODUCT_GROUP.ReadByIDGroupName(data2.Rows[i]["产品组"].ToString(), token); int id = crmModels.PRODUCT_PRODUCTGROUP.Create(cp[0].PRODUCTID, cpz, token); if (id <= 0) { msg.Msg = "新增产品组的第" + (i + 2) + "行出现问题,请记录当前报错信息并联系管理员"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } #endregion } //客户产品组 for (int i = 0; i < data3.Rows.Count; i++) { #region int cpz = crmModels.PRODUCT_GROUP.ReadByIDGroupName(data3.Rows[i]["产品组"].ToString(), token); CRM_KH_KH kh_model = new CRM_KH_KH(); if (data3.Rows[i]["客户编号"].ToString() != "") { kh_model = crmModels.KH_KH.ReadByCRMID(data3.Rows[i]["客户编号"].ToString(), token); } else { kh_model = crmModels.KH_KH.ReadBySAPSN(data3.Rows[i]["客户SAP编号"].ToString(), token); } int id = crmModels.PRODUCT_KHGROUP.Create(kh_model.KHID, cpz, token); if (id <= 0) { msg.Msg = "新增客户产品组的第" + (i + 2) + "行出现问题,请记录当前报错信息并联系管理员"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } #endregion } #endregion msg.Msg = "新增成功!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { msg.Msg = "文件读取失败!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } catch (Exception e) { msg.Msg = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string DR_ElectricInvoice() { token = appClass.CRM_Gettoken(); DaoRuMsg mr = new DaoRuMsg(); // DaoRuMsg mr = new DaoRuMsg(); var file = Request.Files[0]; string year = DateTime.Now.Year.ToString(); string month = DateTime.Now.Month.ToString(); string gotname = file.FileName; string savePath = FileSavePath + @"\excel\" + year + @"\" + month + @"\" + gotname; if (Directory.Exists(FileSavePath + @"\excel\" + year + @"\" + month) == false) { Directory.CreateDirectory(FileSavePath + @"\excel\" + year + @"\" + month); } file.SaveAs(savePath); FileInfo fi = new FileInfo(savePath); if (fi.Exists == true) { DataTable data = ExcelToDataTable(savePath, 0, true); for (int i = 0; i < data.Rows.Count; i++) { if (data.Rows[i]["发票"].ToString() == "") { mr.Info = "E"; mr.Msg = "第" + (i + 1) + "行发票代码不能为空"; return(Newtonsoft.Json.JsonConvert.SerializeObject(mr)); } if (data.Rows[i]["发票"].ToString().Trim().Length != 20) { mr.Info = "E"; mr.Msg = "第" + (i + 1) + "行发票代码长度不正确"; return(Newtonsoft.Json.JsonConvert.SerializeObject(mr)); } FICO_FM_ElectricInvoice model = new FICO_FM_ElectricInvoice(); model.FPHM = data.Rows[i]["发票"].ToString().Trim().Substring(0, 12); model.FPDM = data.Rows[i]["发票"].ToString().Trim().Substring(12); int x = crmModels.FM_ElectricInvoice.ReadBySCAN(model, token); if (x > 0) { mr.Info = "E"; mr.Msg = "第" + (i + 1) + "行发票重复,请检查数据."; return(Newtonsoft.Json.JsonConvert.SerializeObject(mr)); } } for (int i = 0; i < data.Rows.Count; i++) { if (data.Rows[i]["发票"].ToString().Trim().Length != 20) { mr.Info = "E"; mr.Msg = "发票格式不正确"; return(Newtonsoft.Json.JsonConvert.SerializeObject(mr)); } else { FICO_FM_ElectricInvoice model = new FICO_FM_ElectricInvoice(); model.KPRQ = data.Rows[i]["开票日期"].ToString().Trim().Replace(".", "-"); model.BXR = data.Rows[i]["经办人"].ToString().Trim(); model.FPDM = data.Rows[i]["发票"].ToString().Trim().Substring(0, 12); model.FPHM = data.Rows[i]["发票"].ToString().Trim().Substring(12); model.JYM = ""; model.ISACTIVE = 1; model.BEIZ = "期初数据,含税金额" + data.Rows[i]["金额"].ToString().Trim(); model.CJR = appClass.CRM_GetStaffid(); model.XGR = appClass.CRM_GetStaffid(); int j = crmModels.FM_ElectricInvoice.Create(model, token); if (j < 0) { mr.Msg = "err"; mr.Info = "存入数据错误,请联系管理员"; return(Newtonsoft.Json.JsonConvert.SerializeObject(mr)); } } } mr.Info = "S"; mr.Msg = "导入成功"; return(Newtonsoft.Json.JsonConvert.SerializeObject(mr)); } mr.Msg = "S"; mr.Info = "导入成功"; return(Newtonsoft.Json.JsonConvert.SerializeObject(mr)); }
public string FileExport_INVOICE(string cxdata) { token = appClass.CRM_Gettoken(); DaoRuMsg msg = new DaoRuMsg(); // int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID")); MES_RETURN_UI rst = new MES_RETURN_UI(); try { FICO_FM_ElectricInvoice cxmodel = Newtonsoft.Json.JsonConvert.DeserializeObject <FICO_FM_ElectricInvoice>(cxdata); FICO_FM_ElectricInvoice[] model = crmModels.FM_ElectricInvoice.ReadByParam(cxmodel, token); FileStream file = new FileStream(FileSavePath + @"\ExcelTemplate/电子发票.xls", FileMode.Open, FileAccess.Read); IWorkbook workbook = new HSSFWorkbook(file); ISheet worksheet1 = workbook.GetSheetAt(0); if (worksheet1 == null) { msg.Msg = "errr"; msg.Info = "Excel中没有工作表"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } int row1 = 0; for (int i = 0; i < model.Length; i++) { int cellIndex = 0; NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1 + 1); row.CreateCell(cellIndex++).SetCellValue(model[i].FPDM); row.CreateCell(cellIndex++).SetCellValue(model[i].FPHM); row.CreateCell(cellIndex++).SetCellValue(model[i].KPRQ); row.CreateCell(cellIndex++).SetCellValue(model[i].JYM); row.CreateCell(cellIndex++).SetCellValue((model[i].AMOUNT).ToString()); row.CreateCell(cellIndex++).SetCellValue(model[i].SELLERID); row.CreateCell(cellIndex++).SetCellValue(model[i].PZBH); row.CreateCell(cellIndex++).SetCellValue(model[i].BXR); row.CreateCell(cellIndex++).SetCellValue(model[i].CJSJ); row.CreateCell(cellIndex++).SetCellValue(model[i].CJRDES); row.CreateCell(cellIndex++).SetCellValue(model[i].KJND); row.CreateCell(cellIndex++).SetCellValue(model[i].BEIZ); row1++; } for (int columnNum = 0; columnNum <= model.Length; columnNum++) { int columnWidth = worksheet1.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= worksheet1.LastRowNum; rowNum++) { IRow currentRow; //当前行未被使用过 if (worksheet1.GetRow(rowNum) == null) { currentRow = worksheet1.CreateRow(rowNum); } else { currentRow = worksheet1.GetRow(rowNum); } if (currentRow.GetCell(columnNum) != null) { ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.Default.GetBytes(currentCell.ToString()).Length; if (columnWidth < length) { columnWidth = length; } } } worksheet1.SetColumnWidth(columnNum, columnWidth * 256); } worksheet1.ForceFormulaRecalculation = true; string now = DateTime.Now.ToString("yyyyMMddHHmmss.fff"); FileStream file1 = new FileStream(FileSavePath + @"\upload\" + now + ".xls", FileMode.Create); workbook.Write(file1); file1.Close(); rst.TYPE = "S"; rst.MESSAGE = now + ".xls"; return(Newtonsoft.Json.JsonConvert.SerializeObject(rst)); } catch (Exception e) { msg.Msg = "err"; msg.Info = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string WLDZB_DAOCHU(string data) { string token = AppClass.GetSession("token").ToString(); int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID")); DaoRuMsg msg = new DaoRuMsg(); try { MES_TM_TMINFO_LIST[] model = Newtonsoft.Json.JsonConvert.DeserializeObject <MES_TM_TMINFO_LIST[]>(data); FileStream file = new FileStream(Server.MapPath("~") + @"/Areas/WMS/ExportFile/车间物流对账表导出模板.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = new XSSFWorkbook(file); ISheet worksheet1 = workbook.GetSheet("物流对账表"); if (worksheet1 == null) { msg.Msg = "err"; msg.Info = "工作薄中没有工作表"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } //worksheet1.AddMergedRegion(new CellRangeAddress(1, 2, 3, 4)); int row1 = 1; for (int i = 0; i < model.Length; i++) { int count = 0; NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1); row.CreateCell(count++).SetCellValue(model[i].TM); row.CreateCell(count++).SetCellValue(model[i].ZHNO); row.CreateCell(count++).SetCellValue(model[i].WLH); row.CreateCell(count++).SetCellValue(model[i].WLMS); row.CreateCell(count++).SetCellValue(model[i].PC); row.CreateCell(count++).SetCellValue(model[i].GC); row.CreateCell(count++).SetCellValue(model[i].KCDDNAME); row.CreateCell(count++).SetCellValue(model[i].CKNO); row.CreateCell(count++).SetCellValue(model[i].AREANO); row.CreateCell(count++).SetCellValue(model[i].LGPLA); row.CreateCell(count++).SetCellValue(model[i].HJCM); row.CreateCell(count++).SetCellValue(model[i].RESDUESL.ToString()); row.CreateCell(count++).SetCellValue(model[i].JHZ); row.CreateCell(count++).SetCellValue(model[i].SOBKZ); row.CreateCell(count++).SetCellValue(model[i].SONUM); row.CreateCell(count++).SetCellValue(model[i].ISZC == 0 ? "否" : "是"); row.CreateCell(count++).SetCellValue(model[i].GZZXBH); row.CreateCell(count++).SetCellValue(model[i].JLTIME); row.CreateCell(count++).SetCellValue(model[i].XGTIME); row.CreateCell(count++).SetCellValue(model[i].WLPZBH); row.CreateCell(count++).SetCellValue(model[i].WLPZHXMH); row.CreateCell(count++).SetCellValue(model[i].WLPZND); row.CreateCell(count++).SetCellValue(model[i].SL.ToString()); row.CreateCell(count++).SetCellValue(model[i].JZ.ToString()); row.CreateCell(count++).SetCellValue(model[i].CKDJH); string RKZT = ""; switch (model[i].RKZT) { case 1: RKZT = "车间发出"; break; case 2: RKZT = "物流入库"; break; default: RKZT = ""; break; } row.CreateCell(count++).SetCellValue(RKZT); row1++; } worksheet1.ForceFormulaRecalculation = true; string now = DateTime.Now.ToString("yyyy-MM"); FileStream file1 = new FileStream(string.Format(@"{0}/Areas/WMS/ExportFile/{1}.xlsx", Server.MapPath("~"), now), FileMode.Create); workbook.Write(file1); file1.Close(); msg.Msg = now; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } catch (Exception e) { msg.Msg = "err"; msg.Info = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string Data_DaoRu_MDBS() { token = appClass.CRM_Gettoken(); DaoRuMsg msg = new DaoRuMsg(); msg.Info = "E"; try { var file = Request.Files[0]; string year = DateTime.Now.Year.ToString(); string month = DateTime.Now.Month.ToString(); string gotname = file.FileName; string savePath = FileSavePath + @"\excel\" + year + @"\" + month + @"\" + gotname; if (Directory.Exists(FileSavePath + @"\excel\" + year + @"\" + month) == false) { Directory.CreateDirectory(FileSavePath + @"\excel\" + year + @"\" + month); } file.SaveAs(savePath); FileInfo fi = new FileInfo(savePath); if (fi.Exists == true) { string[] sheet = { "过账授权" }; DataTable data1 = ExcelToDataTable(savePath, sheet[0], true); //过账授权 System.IO.File.Delete(savePath); #region 数据校验 if (data1 != null) { if (data1.Columns.Contains("交货单") == false || data1.Columns.Contains("开放状态") == false) { msg.Msg = "请使用指定的导入模版!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { try { //做数据验证 if (data1.Rows.Count > 0) { for (int i = 0; i < data1.Rows.Count; i++) { if (data1.Rows[i]["交货单"].ToString().Length != 8) { msg.Msg = "过账授权第" + (i + 2) + "行交货单格式不正确!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } if (data1.Rows[i]["开放状态"].ToString() != "已开放" && data1.Rows[i]["开放状态"].ToString() != "未开放") { msg.Msg = "过账授权第" + (i + 2) + "行开放状态内容不正确!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } } catch (Exception e) { msg.Msg = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } } #endregion #region 过账授权 List <WMS_JPD_JHD_TQGZ> modellist = new List <WMS_JPD_JHD_TQGZ>(); for (int i = 0; i < data1.Rows.Count; i++) { WMS_JPD_JHD_TQGZ temp = new WMS_JPD_JHD_TQGZ(); temp.JHD = data1.Rows[i]["交货单"].ToString(); switch (data1.Rows[i]["开放状态"].ToString()) { case "已开放": temp.KFZT = 2; break; case "未开放": temp.KFZT = 1; break; default: temp.KFZT = 0; break; } modellist.Add(temp); } string datastring = Newtonsoft.Json.JsonConvert.SerializeObject(modellist); string model = sHttp.SApiPost("WMS", "WMS/WMS_JPD/CREATE_JHD_TQGZ", datastring); msg.Info = "S"; msg.data = model; #endregion return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } else { msg.Msg = "文件读取失败!"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } } catch (Exception e) { msg.Msg = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string TM_EXPORT(string data) { string token = AppClass.GetSession("token").ToString(); int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID")); DaoRuMsg msg = new DaoRuMsg(); try { token = appClass.CRM_Gettoken(); var url = section + "api/WMS/BC_TM/READ"; var restClient = new RestClient(url); var restRequest = new RestRequest(Method.POST); restRequest.AddHeader("Sonluk-Token", token); restRequest.RequestFormat = DataFormat.Json; restRequest.AddBody(data); var response = restClient.Execute(restRequest); var jsonData = (JObject)JsonConvert.DeserializeObject(response.Content); var result = jsonData["data"]; FileStream file = new FileStream(Server.MapPath("~") + @"/Areas/WMS/ExportFile/套标码导出模板.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = new XSSFWorkbook(file); ISheet worksheet1 = workbook.GetSheet("套标码"); if (worksheet1 == null) { msg.Msg = "err"; msg.Info = "工作薄中没有工作表"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } //worksheet1.AddMergedRegion(new CellRangeAddress(1, 2, 3, 4)); int row1 = 1; for (int i = 0; i < result.Count(); i++) { NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1); row.CreateCell(0).SetCellValue(result[i]["TPM"].ToString()); row.CreateCell(1).SetCellValue(result[i]["DXM"].ToString()); row.CreateCell(2).SetCellValue(result[i]["NHM"].ToString()); row1++; } for (int columnNum = 0; columnNum <= result.Count(); columnNum++) { int columnWidth = worksheet1.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= worksheet1.LastRowNum; rowNum++) { IRow currentRow; //当前行未被使用过 if (worksheet1.GetRow(rowNum) == null) { currentRow = worksheet1.CreateRow(rowNum); } else { currentRow = worksheet1.GetRow(rowNum); } if (currentRow.GetCell(columnNum) != null) { ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.Default.GetBytes(currentCell.ToString()).Length; if (columnWidth < length) { columnWidth = length; } } } worksheet1.SetColumnWidth(columnNum, columnWidth * 256); } worksheet1.ForceFormulaRecalculation = true; string now = DateTime.Now.ToString("yyyy-MM"); FileStream file1 = new FileStream(string.Format(@"{0}/Areas/WMS/ExportFile/{1}.xlsx", Server.MapPath("~"), now), FileMode.Create); workbook.Write(file1); file1.Close(); msg.Msg = now; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } catch (Exception e) { msg.Msg = "err"; msg.Info = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }
public string JHZC_DAOCHU(string data) { string token = AppClass.GetSession("token").ToString(); int STAFFID = Convert.ToInt32(AppClass.GetSession("STAFFID")); DaoRuMsg msg = new DaoRuMsg(); try { WMS_BC_TM_JH_JHZC[] model = Newtonsoft.Json.JsonConvert.DeserializeObject <WMS_BC_TM_JH_JHZC[]>(data); FileStream file = new FileStream(Server.MapPath("~") + @"/Areas/WMS/ExportFile/内销交货转储查询导出模板.xlsx", FileMode.Open, FileAccess.Read); IWorkbook workbook = new XSSFWorkbook(file); ISheet worksheet1 = workbook.GetSheet("交货转储"); if (worksheet1 == null) { msg.Msg = "err"; msg.Info = "工作薄中没有工作表"; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } //worksheet1.AddMergedRegion(new CellRangeAddress(1, 2, 3, 4)); int row1 = 1; for (int i = 0; i < model.Length; i++) { int count = 0; NPOI.SS.UserModel.IRow row = worksheet1.CreateRow(row1); row.CreateCell(count++).SetCellValue(model[i].JHNO); row.CreateCell(count++).SetCellValue(model[i].JHMXNO); row.CreateCell(count++).SetCellValue(model[i].JPDNO); row.CreateCell(count++).SetCellValue(model[i].JPDMXNO); row.CreateCell(count++).SetCellValue(model[i].WLH); row.CreateCell(count++).SetCellValue(model[i].WLMS); row.CreateCell(count++).SetCellValue(model[i].TPM); row.CreateCell(count++).SetCellValue(model[i].DXM); row.CreateCell(count++).SetCellValue(model[i].NHM); row.CreateCell(count++).SetCellValue(model[i].QXBS); row.CreateCell(count++).SetCellValue(model[i].LTBS); row.CreateCell(count++).SetCellValue(model[i].SDF); row.CreateCell(count++).SetCellValue(model[i].SDFNAME); row.CreateCell(count++).SetCellValue(model[i].SDFADDRESS); row.CreateCell(count++).SetCellValue(model[i].FHSL.ToString()); row.CreateCell(count++).SetCellValue(model[i].DW); row.CreateCell(count++).SetCellValue(model[i].JHZ); row.CreateCell(count++).SetCellValue(model[i].TSKCBJ); row.CreateCell(count++).SetCellValue(model[i].TSKC); row.CreateCell(count++).SetCellValue(model[i].CHARG); row.CreateCell(count++).SetCellValue(model[i].GC); row.CreateCell(count++).SetCellValue(model[i].KCDDNAME); row1++; } worksheet1.ForceFormulaRecalculation = true; string now = DateTime.Now.ToString("yyyy-MM"); FileStream file1 = new FileStream(string.Format(@"{0}/Areas/WMS/ExportFile/{1}.xlsx", Server.MapPath("~"), now), FileMode.Create); workbook.Write(file1); file1.Close(); msg.Msg = now; return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } catch (Exception e) { msg.Msg = "err"; msg.Info = e.ToString(); return(Newtonsoft.Json.JsonConvert.SerializeObject(msg)); } }