public void GETFORMFILEDS(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
var PDS = new Yan_WF_PDB().GetEntities(D => D.ComId == UserInfo.User.ComId);
JArray arrs = new JArray();
foreach (var item in PDS)
{
List <JH_Auth_ExtendMode> ExtendModes = new List <JH_Auth_ExtendMode>();
ExtendModes = new JH_Auth_ExtendModeB().GetEntities(D => D.ComId == UserInfo.User.ComId && D.PDID == item.ID).ToList();
JObject obj = JObject.FromObject(new
{
value = item.ID,
label = item.ProcessName,
children =
from p in ExtendModes
select new
{
value = p.TableFiledColumn,
label = p.TableFiledName,
}
});
arrs.Add(obj);
}
msg.Result = arrs;
}
/// <summary>
/// 流程审批添加
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void ADDPROCESS(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
Yan_WF_PD lcsp = JsonConvert.DeserializeObject <Yan_WF_PD>(P1);
if (lcsp.ProcessName.Trim() == "")
{
msg.ErrorMsg = "流程名称不能为空";
return;
}
if (lcsp.ID == 0)//如果Id为0，为添加操作
{
if (new Yan_WF_PDB().GetEntities(d => d.ComId == UserInfo.User.ComId && d.ProcessName == lcsp.ProcessName).Count() > 0)
{
msg.ErrorMsg = "已存在此流程";
return;
}
lcsp.lcstatus = 1;
lcsp.ComId = UserInfo.User.ComId;
if (lcsp.ManageRole != null)
{
lcsp.ManageRole.Trim(',');
}
lcsp.CRDate = DateTime.Now;
lcsp.CRUser = UserInfo.User.UserName;
lcsp.CRUserName = UserInfo.User.UserRealName;
new Yan_WF_PDB().Insert(lcsp); //添加流程表数据
}
else
{
//修改流程表数据
new Yan_WF_PDB().Update(lcsp);
}
//如果流程类型为固定流程并且固定流程内容不为空，添加固定流程数据
if (lcsp.ProcessType == "1" && !string.IsNullOrEmpty(P2))
{
List <Yan_WF_TD> tdList = JsonConvert.DeserializeObject <List <Yan_WF_TD> >(P2);
tdList.ForEach(d => d.ProcessDefinitionID = lcsp.ID);
tdList.ForEach(d => d.ComId = UserInfo.User.ComId);
tdList.ForEach(d => d.CRDate = DateTime.Now);
tdList.ForEach(d => d.CRUser = UserInfo.User.UserName);
tdList.ForEach(d => d.TDCODE = d.ProcessDefinitionID + "-" + d.Taskorder);
tdList.ForEach(d => d.AssignedRole = d.AssignedRole.Trim(','));
new Yan_WF_TDB().Delete(d => d.ProcessDefinitionID == tdList[0].ProcessDefinitionID);
List <string> ExtendModes = new List <string>();
ExtendModes = new JH_Auth_ExtendModeB().GetEntities(D => D.ComId == UserInfo.User.ComId && D.PDID == lcsp.ID).Select(D => D.TableFiledColumn).ToList();
tdList[0].WritableFields = ExtendModes.ListTOString(',');
new Yan_WF_TDB().Insert(tdList);
}
msg.Result = lcsp;
}
public void GETBDTJDATA(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
int pdid = 0;
int.TryParse(P1, out pdid);
string strSDate = context.Request("sdate") ?? DateTime.Now.AddYears(-20).ToString("yyyy-MM-dd");
string strEDate = context.Request("edate") ?? DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
List <JH_Auth_ExtendMode> ExtendModes = new List <JH_Auth_ExtendMode>();
ExtendModes = new JH_Auth_ExtendModeB().GetEntities(D => D.ComId == UserInfo.User.ComId && D.PDID == pdid).ToList();
string strWhere = "";
if (P2 != "")
{
JArray datas = (JArray)JsonConvert.DeserializeObject(P2);
foreach (JObject item in datas)
{
string filed = (string)item["filed"];
if (ExtendModes.Select(D => D.TableFiledColumn).ToList().Contains(filed))
{
string qtype = (string)item["qtype"];
string qvalue = (string)item["qvalue"];
strWhere = CommonHelp.CreateqQsql(filed, qtype, qvalue);
}
}
}
string strISGD = context.Request("isGD") ?? "";
if (strISGD != "")
{
strWhere = strWhere + " AND ISGD='" + strISGD.FilterSpecial() + "'";
}
string pdfields = context.Request("pdfields") ?? "";
if (pdfields != "")
{
ExtendModes = ExtendModes.Where(d => d.TableFiledColumn == pdfields).ToList();
}
if (ExtendModes.Count > 0)
{
string strTempSQL = new Yan_WF_PDB().GetDTHZL(ExtendModes.Select(D => D.TableFiledColumn).ToList().ListTOString(','), pdid.ToString());
string strSQL = strTempSQL + " WHERE CRDATE BETWEEN '" + strSDate + " 01:01:01' AND '" + strEDate + " 23:59:59' " + strWhere;
DataTable dt = new Yan_WF_PDB().GetDTByCommand(strSQL);
msg.Result = dt;
msg.Result1 = ExtendModes;
}
}
/// <summary>
/// 获取表单内的可管理字段
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void GETFORMFILED(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
int pdid = 0;
int.TryParse(P1, out pdid);
int tdid = 0;
int.TryParse(P2, out tdid);
List <JH_Auth_ExtendMode> ExtendModes = new List <JH_Auth_ExtendMode>();
ExtendModes = new JH_Auth_ExtendModeB().GetEntities(D => D.ComId == UserInfo.User.ComId && D.PDID == pdid).ToList();
msg.Result = ExtendModes;
if (tdid != 0)
{
Yan_WF_TD TD = new Yan_WF_TDB().GetEntity(d => d.ID == tdid && d.ComId == UserInfo.User.ComId);
msg.Result1 = TD.WritableFields;
}
}
/// <summary>
/// 下载模板excel(弃用)
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void DOWNLOADEXCEL(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
try
{
string strName = string.Empty;
if (P1 == "KHGL")
{
strName = "CRM_客户_导入模板.xls";
}
else if (P1 == "KHLXR")
{
strName = "CRM_客户联系人_导入模板.xls";
}
else if (P1 == "HTGL")
{
strName = "CRM_合同_导入模板.xls";
}
object curContext = object.Current;
string headrow = context.Request["headrow"] ?? "0";//头部开始行下标
string path = curContext.Server.MapPath(@"/ViewV5/base/" + strName);
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
string suffix = path.Substring(path.LastIndexOf(".") + 1).ToLower();
IWorkbook workbook = null;
if (suffix == "xlsx") // 2007版本
{
workbook = new XSSFWorkbook(file);
}
else if (suffix == "xls") // 2003版本
{
workbook = new HSSFWorkbook(file);
}
ISheet sheet = workbook.GetSheetAt(0);
IRow headerRow = sheet.GetRow(int.Parse(headrow));
IRow oneRow = sheet.GetRow(int.Parse(headrow) + 1);
int icolIndex = headerRow.Cells.Count;
DataTable dtExtColumn = new JH_Auth_ExtendModeB().GetExtColumnAll(UserInfo.QYinfo.ComId, P1);
foreach (DataRow drExt in dtExtColumn.Rows)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(drExt["TableFiledName"].ToString());
cell.CellStyle = headerRow.Cells[icolIndex - 1].CellStyle;
ICell onecell = oneRow.CreateCell(icolIndex);
onecell.SetCellValue("");
onecell.CellStyle = oneRow.Cells[icolIndex - 1].CellStyle;
icolIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
if (P1 == "KHGL")
{
//表头样式
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = BorderStyle.Thin;
HeadercellStyle.BorderLeft = BorderStyle.Thin;
HeadercellStyle.BorderRight = BorderStyle.Thin;
HeadercellStyle.BorderTop = BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
headerfont.FontHeightInPoints = 10;
cellStyle.SetFont(cellfont);
for (int i = 10; i < 15; i++)
{
string strZTName = string.Empty;
if (i == 10)
{
strZTName = "客户类型";
}
if (i == 11)
{
strZTName = "跟进状态";
}
if (i == 12)
{
strZTName = "客户来源";
}
if (i == 13)
{
strZTName = "所属行业";
}
if (i == 14)
{
strZTName = "人员规模";
}
ISheet sheet1 = workbook.CreateSheet(strZTName);
IRow headerRow1 = sheet1.CreateRow(0);
ICell cell1 = headerRow1.CreateCell(0);
cell1.SetCellValue(strZTName);
cell1.CellStyle = HeadercellStyle;
int rowindex1 = 1;
foreach (var l in new JH_Auth_ZiDianB().GetEntities(p => p.ComId == UserInfo.QYinfo.ComId && p.Class == i))
{
IRow DataRow = sheet1.CreateRow(rowindex1);
ICell cell = DataRow.CreateCell(0);
cell.SetCellValue(l.TypeName);
cell.CellStyle = cellStyle;
rowindex1++;
}
sheet1.AutoSizeColumn(0);
}
}
if (P1 == "HTGL")
{
//表头样式
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = BorderStyle.Thin;
HeadercellStyle.BorderLeft = BorderStyle.Thin;
HeadercellStyle.BorderRight = BorderStyle.Thin;
HeadercellStyle.BorderTop = BorderStyle.Thin;
HeadercellStyle.Alignment = HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
headerfont.FontHeightInPoints = 12;
HeadercellStyle.SetFont(headerfont);
//单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
headerfont.FontHeightInPoints = 10;
cellStyle.SetFont(cellfont);
for (int i = 16; i < 18; i++)
{
string strZTName = string.Empty;
if (i == 16)
{
strZTName = "合同类型";
}
if (i == 17)
{
strZTName = "付款方式";
}
ISheet sheet1 = workbook.CreateSheet(strZTName);
IRow headerRow1 = sheet1.CreateRow(0);
ICell cell1 = headerRow1.CreateCell(0);
cell1.SetCellValue(strZTName);
cell1.CellStyle = HeadercellStyle;
int rowindex1 = 1;
foreach (var l in new JH_Auth_ZiDianB().GetEntities(p => p.ComId == UserInfo.QYinfo.ComId && p.Class == i))
{
IRow DataRow = sheet1.CreateRow(rowindex1);
ICell cell = DataRow.CreateCell(0);
cell.SetCellValue(l.TypeName);
cell.CellStyle = cellStyle;
rowindex1++;
}
sheet1.AutoSizeColumn(0);
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
//object curContext = object.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strName, Encoding.UTF8));
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
workbook = null;
ms.Close();
ms.Dispose();
}
}
catch
{
msg.ErrorMsg = "下载失败！";
}
}
/// <summary>
/// 获取导入excel的字段
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public List <IMPORTYZ> GetTable(string code, int comid)
{
string json = string.Empty;
switch (code)
{
case "KHGL":
json = "[{\"Name\":\"客户名称\",\"Length\":\"50\",\"IsNull\":\"1\",\"IsRepeat\":\"SZHL_CRM_KHGL|KHName\"},{\"Name\":\"客户类型\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"电话\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"邮箱\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"传真\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"网址\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"地址\",\"Length\":\"500\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"邮编\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"跟进状态\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"客户来源\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"所属行业\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"人员规模\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"负责人\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"备注\",\"Length\":\"0\",\"IsNull\":\"0\"}]";
break;
case "KHLXR":
json = "[{\"Name\":\"姓名\",\"Length\":\"50\",\"IsNull\":\"1\"},{\"Name\":\"对应客户\",\"Length\":\"50\",\"IsNull\":\"0\",\"IsExist\":\"SZHL_CRM_KHGL|KHName\"},"
+ "{\"Name\":\"手机\",\"Length\":\"11\",\"IsNull\":\"1\"},{\"Name\":\"邮箱\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"传真\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"网址\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"电话\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"分机\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"QQ\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"微信\",\"Length\":\"100\",\"IsNull\":\"0\"},"
//+ "{\"Name\":\"学历\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"公司\",\"Length\":\"100\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"部门\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"职位\",\"Length\":\"100\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"地址\",\"Length\":\"200\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"邮编\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"性别\",\"Length\":\"10\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"生日\",\"Length\":\"10\",\"IsNull\":\"0\"},{\"Name\":\"备注\",\"Length\":\"0\",\"IsNull\":\"0\"}]";
break;
case "HTGL":
json = "[{\"Name\":\"合同标题\",\"Length\":\"2500\",\"IsNull\":\"1\"},{\"Name\":\"合同类型\",\"Length\":\"50\",\"IsNull\":\"1\"},"
+ "{\"Name\":\"合同总金额\",\"Length\":\"100\",\"IsNull\":\"1\"},{\"Name\":\"签约日期\",\"Length\":\"10\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"对应客户\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"开始时间\",\"Length\":\"10\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"结束时间\",\"Length\":\"10\",\"IsNull\":\"0\"},{\"Name\":\"合同状态\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"关联产品\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"付款方式\",\"Length\":\"100\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"付款说明\",\"Length\":\"1500\",\"IsNull\":\"0\"},{\"Name\":\"有效期\",\"Length\":\"100\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"我方签约人\",\"Length\":\"50\",\"IsNull\":\"0\"},{\"Name\":\"客户方签约人\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"合同编号\",\"Length\":\"100\",\"IsNull\":\"0\"},{\"Name\":\"负责人\",\"Length\":\"50\",\"IsNull\":\"0\"},"
+ "{\"Name\":\"备注\",\"Length\":\"0\",\"IsNull\":\"0\"}]";
break;
}
if (comid != 0)
{
json = json.Substring(0, json.Length - 1);
DataTable dtExtColumn = new JH_Auth_ExtendModeB().GetExtColumnAll(comid, code);
foreach (DataRow drExt in dtExtColumn.Rows)
{
json = json + ",{\"Name\":\"" + drExt["TableFiledName"].ToString() + "\",\"Length\":\"0\",\"IsNull\":\"0\"}";
}
json = json + "]";
}
List <IMPORTYZ> cls = JsonConvert.DeserializeObject <List <IMPORTYZ> >(json);
return(cls);
}
public ActionResult <string> EXPORTWORD()
{
var context = _accessor.HttpContext;
var tokenHeader = context.Request.Cookies["szhlcode"].ToString().Replace("Bearer ", "");
TokenModelJWT tokenModel = JwtHelper.SerializeJWT(tokenHeader);
JH_Auth_UserB.UserInfo UserInfo = new JH_Auth_UserB().GetUserInfo(10334, tokenModel.UserName);
string P1 = context.Request.Query["P1"].ToString();
string P2 = context.Request.Query["P2"].ToString();
int pdid = 0;
int.TryParse(P1, out pdid);
int piid = 0;
int.TryParse(P2, out piid);
Yan_WF_PD PD = new Yan_WF_PDB().GetEntity(d => d.ID == pdid && d.ComId == UserInfo.User.ComId);
Yan_WF_PI PI = new Yan_WF_PIB().GetEntity(d => d.ID == piid && d.ComId == UserInfo.User.ComId);
if (PD.ExportFile == null)
{
return("");
}
int fileID = int.Parse(PD.ExportFile);
FT_File MBFile = new FT_FileB().GetEntities(d => d.ID == fileID).FirstOrDefault();
Dictionary <string, string> dictSource = new Dictionary <string, string>();
List <JH_Auth_ExtendMode> ExtendModes = new List <JH_Auth_ExtendMode>();
ExtendModes = new JH_Auth_ExtendModeB().GetEntities(D => D.ComId == UserInfo.User.ComId && D.PDID == pdid).ToList();
foreach (JH_Auth_ExtendMode item in ExtendModes)
{
string strValue = new JH_Auth_ExtendDataB().GetFiledValue(item.TableFiledColumn, pdid, piid);
dictSource.Add("qj_" + item.TableFiledColumn, strValue);
}
dictSource.Add("qj_CRUser", PI.CRUserName);
dictSource.Add("qj_BranchName", PI.BranchName);
dictSource.Add("qj_CRDate", PI.CRDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));
dictSource.Add("qj_PINUM", PI.ID.ToString());
List <Yan_WF_TI> tiModels = new Yan_WF_TIB().GetEntities(d => d.PIID == piid).ToList();
for (int i = 0; i < tiModels.Count; i++)
{
dictSource.Add("qj_Task" + i + ".TaskUser", new JH_Auth_UserB().GetUserRealName(UserInfo.User.ComId.Value, tiModels[i].TaskUserID));
dictSource.Add("qj_Task" + i + ".TaskUserView", tiModels[i].TaskUserView);
if (tiModels[i].EndTime != null)
{
dictSource.Add("qj_Task" + i + ".EndTime", tiModels[i].EndTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
string strFileUrl = UserInfo.QYinfo.FileServerUrl + "/" + UserInfo.QYinfo.QYCode + "/document/" + MBFile.zyid;
Aspose.Words.Document doc = new Aspose.Words.Document(strFileUrl);
//使用文本方式替换
foreach (string name in dictSource.Keys)
{
doc.Range.Replace(name, dictSource[name]);
}
#region 使用书签替换模式
#endregion
string Filepath = hostingEnv.WebRootPath + "/Export/";
string strFileName = PD.ProcessName + DateTime.Now.ToString("yyMMddHHss") + ".doc";
doc.Save(Filepath + strFileName, Aspose.Words.Saving.DocSaveOptions.CreateSaveOptions(SaveFormat.Doc));
return(File("~/excels/report.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "report.xlsx"));
}
