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));
}
}
