public ActionResult ItemDetails(CasContractEntity entity, string id)
{
if (!string.IsNullOrEmpty(id))
{
string sqlstring = $@"SELECT CONTRACT_ID,CONTRACT_SERIAL_NO,STATUS,ORIGINAL_CONTRACT_ID,SUPPLIER,CONTRACT_GROUP,MODIFICATION_POINTS,CONTRACT_TYPE_ID,CONTRACT_TYPE_NAME,NEED_COMMENT,NOT_DISPLAY_IN_MY_SUPPORT,IS_TEMPLATE_CONTRACT,CONTRACT_NAME,CONTRACT_TERM,CONTRACT_OWNER,CONTRACT_INITIATOR,FERRERO_ENTITY,COUNTERPARTY_EN,COUNTERPARTY_CN,EFFECTIVE_DATE,EXPIRATION_DATE,IS_MASTER_AGREEMENT,TAX,CONTRACT_PRICE,ESTIMATED_PRICE,CURRENCY,CAPEX,SUPPLEMENTARY,BUDGET_AMOUNT,INTERNAL_INVERTMENT_ORDER,REFERENCE_CONTRACT,PREPAYMENT_AMOUNT,PREPAYMENT_PERCENTAGE,CONTRACT_KEY_POINTS,BUDGET_TYPE,CASE contract.IS_TEMPLATE_CONTRACT
when 1
then contract.TEMPLATE_NO
else
(select template.TEMPLATE_NO from CAS_CONTRACT template
where contract.TEMPLATE_NO = template.CONTRACT_ID)
end TEMPLATE_NO,TEMPLATE_NAME,TEMPLATE_TERM,TEMPLATE_OWNER,TEMPLATE_INITIATOR,SCOPE_OF_APPLICATION,APPLY_DATE,IS_DELETED,CREATED_BY,CREATE_TIME,LAST_MODIFIED_BY,LAST_MODIFIED_TIME FROM CAS_CONTRACT contract WHERE contract.CONTRACT_ID='{id}'";
entity = DataAccess.Select <CasContractEntity>(sqlstring).FirstOrDefault();
//entity = BusinessDataService.ContractApplayService.GetById<CasContractEntity>(id);
CasContractTypeEntity casContractTypeEntity = BusinessDataService.ContractTypeManagementService.GetById <CasContractTypeEntity>(entity.ContractTypeId);
ViewBag.ContractType = casContractTypeEntity;
List <CasAttachmentEntity> list = BusinessDataService.CommonHelperService.GetFilesBySourceId(id);
var mineFileIds = list.Where(f => f.AttachmentType == 1).ToList();
var originalFileIds = list.Where(f => f.AttachmentType == 2).ToList();
ViewBag.MineFiles = mineFileIds;
ViewBag.OriginalFiles = originalFileIds;
#region 合同审批结果
//获取审批结果信息
DataTable approvalResultDt = BusinessDataService.CommonHelperService.GetApprovalResultDt(entity.ContractId);
JsonSerializerSettings setting = new JsonSerializerSettings()
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
};
var ret = JsonConvert.SerializeObject(approvalResultDt, setting);
ViewBag.ApprovalResult = ret;
#endregion
}
return(View(entity));
}
public override void Run(Hashtable parameters)
{
POBLL poBLL = new POBLL();
ContractManagementBLL contractManagementBLL = new ContractManagementBLL();
List <CasUserEntity> listUsers = poBLL.GetAllHaveApplyPOUsers();
foreach (CasUserEntity user in listUsers)
{
List <CasContractFilingEntity> listAllApplyPO = poBLL.GetAllApplyPOs(user.UserId);
//邮件标题
string mailTitle = $@"您有{listAllApplyPO.Count}条PO未审批({DateTime.Now.ToString("yyyy年MM月dd日")})";
string mailContent = $@"Dear {user.ChineseName},<br/>您的未审批PO清单如下：<br/><table style='font-family:verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse:collapse;'><thead><tr><th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>合同名称</th><th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>PR号</th></tr></thead><tbody>";
foreach (CasContractFilingEntity applyPO in listAllApplyPO)
{
CasContractEntity contract = contractManagementBLL.GetById <CasContractEntity>(applyPO.ContractId);
mailContent += $@"<tr><td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{contract.ContractName}</td><td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{applyPO.PrNo}</td></tr>";
}
mailContent += "</tbody></table></br>Best Regards";
#if DEBUG
string reciever = "*****@*****.**";
#else
string reciever = approver.Email;
#endif
SendEmail.Send(reciever, mailTitle, mailContent);
}
}
public ActionResult UploadStampContract(CasContractEntity entity, string id)
{
string strError = "";
if (!IsPost)
{
entity = BusinessDataService.ContractApplayService.CreateContractEntity("MDM");
if (!string.IsNullOrEmpty(id))
{
entity = BusinessDataService.ContractApplayService.GetById <CasContractEntity>(id);
//编辑
ViewBag.EditType = "0";
}
else
{
//新增
ViewBag.EditType = "1";
}
}
else
{
if (BusinessDataService.ContractApplayService.SaveContractAttachment(entity, ref strError))
{
return(RedirectToAction("StampContract"));
}
strError = "Update failed";
}
ViewBag.strError = strError;
return(View(entity));
}
public bool POReject(string contractFilingId, string remark, ref string strError)
{
CasContractFilingEntity casContractFilingEntity = GetById <CasContractFilingEntity>(contractFilingId);
CasUserEntity userEntity = GetById <CasUserEntity>(casContractFilingEntity.CreatedBy);
CasContractEntity contractEntity = GetById <CasContractEntity>(casContractFilingEntity.ContractId);
if (casContractFilingEntity == null || string.IsNullOrEmpty(contractFilingId))
{
strError = "PR不存在";
return(false);
}
casContractFilingEntity.Remark = remark;
casContractFilingEntity.Status = ContractFilingEnum.Reject.GetHashCode();
casContractFilingEntity.LastModifiedTime = DateTime.Now;
casContractFilingEntity.LastModifiedBy = WebCaching.UserId;
#region PO审批驳回时候发邮件通知
//var contractEntity = GetById<CasContractEntity>(casContractFilingEntity.ContractId);
var title = $@"e-Approval – Contract PR Rejected";
var content = $@"Dear:{userEntity.EnglishName},</br>尊敬的：{userEntity.ChineseName}</br></br> The following PR is rejected:</br>以下PR被拒绝：</br></br> Contract Name 合同名称:{contractEntity.ContractName} {contractEntity.TemplateName}</br>Ferrero Entity 费列罗方:{contractEntity.FerreroEntity}</br> Counter Party 相对方:{contractEntity.CounterpartyEn} {contractEntity.CounterpartyCn}</br> PR No. PR号码:{casContractFilingEntity.PrNo} </br>Reject Reason 拒绝理由:{remark}</br></br>Please review your PR and resubmit the request.</br>请检查PR相关内容并重新提交。</br></br>Ferrero China Contract Approval System</br> 费列罗中国合同审批系统";
var cc = "*****@*****.**";
var reciever = GetUserEmail(casContractFilingEntity.CreatedBy);
SendEmail.Send(reciever, cc, title, content);
#endregion
return(Update(casContractFilingEntity));
}
public ActionResult CloseContractByContractID(CasContractEntity entity, string id)
{
var a = CloseContractById(id);
return(Json(AjaxResult.Success()));
//return RedirectToAction("Index");
}
public JsonResult Save(CasContractEntity entity, string saveType, string fileIds_mine, string fileIds_original)
{
string msg = "";
entity.ContractGroup = ContractGroupEnum.HistoryContract.GetHashCode();
bool flag = BusinessDataService.ContractApplayService.Save(entity, saveType, fileIds_mine, fileIds_original, ref msg);
return(Json(flag ? AjaxResult.Success(msg) : AjaxResult.Error(msg)));;
}
public ActionResult TemplateReviewProcessReport(string id)
{
CasContractEntity entity = new CasContractEntity();
if (!string.IsNullOrEmpty(id))
{
var sql = @"
SELECT CAS_CONTRACT.CONTRACT_ID,CAS_CONTRACT.TEMPLATE_NO, CAS_CONTRACT.FERRERO_ENTITY,
CAS_CONTRACT.TEMPLATE_NAME,CAS_CONTRACT.CONTRACT_TYPE_NAME,
CAS_CONTRACT.EFFECTIVE_DATE, CAS_CONTRACT.EXPIRATION_DATE,
CAS_CONTRACT.TEMPLATE_OWNER,CAS_CONTRACT.APPLY_DATE,CAS_CONTRACT.CREATE_TIME,
CAS_CONTRACT.TEMPLATE_INITIATOR,CAS_CONTRACT.SCOPE_OF_APPLICATION
FROM CAS_CONTRACT
WHERE CAS_CONTRACT.CONTRACT_ID = @CONTRACT_ID";
DataAccessParameterCollection param = new DataAccessParameterCollection();
ColumnInfo columnInfo = new ColumnInfo("CONTRACT_ID", "CONTRACT_ID", true, typeof(string));
param.AddWithValue(columnInfo, id);
List <CasContractEntity> list = null;
try
{
list = DataAccess.Select <CasContractEntity>(sql, param);
}
catch (Exception)
{
}
if (list != null && list.Any())
{
entity = list[0];
//CasContractTypeEntity casContractTypeEntity = BusinessDataService.ContractTypeManagementService.GetById<CasContractTypeEntity>(entity.ContractTypeId);
//ViewBag.ContractType = casContractTypeEntity;
//List<CasAttachmentEntity> list = BusinessDataService.CommonHelperService.GetFilesBySourceId(id);
//var mineFileIds = list.Where(f => f.AttachmentType == 1).ToList();
//var originalFileIds = list.Where(f => f.AttachmentType == 2).ToList();
//ViewBag.MineFiles = mineFileIds;
//ViewBag.OriginalFiles = originalFileIds;
#region 合同审批结果
//获取审批结果信息
DataTable approvalResultDt = BusinessDataService.CommonHelperService.GetReportApprovalResultDt(entity.ContractId);
JsonSerializerSettings setting = new JsonSerializerSettings()
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
};
var ret = JsonConvert.SerializeObject(approvalResultDt, setting);
ViewBag.ApprovalResult = ret;
#endregion
}
}
return(View(entity));
}
public ActionResult Edit(CasContractEntity entity, string id)
{
if (!string.IsNullOrEmpty(id))
{
entity = BusinessDataService.ContractApplayService.GetById <CasContractEntity>(id);
List <CasAttachmentEntity> list = BusinessDataService.CommonHelperService.GetFilesBySourceId(id);
string mineFileIds = "";
string originalFileIds = "";
list.Where(f => f.AttachmentType == 1).ToList().ForEach(f => mineFileIds += f.AttachmentId + ",");
list.Where(f => f.AttachmentType == 2).ToList().ForEach(f => originalFileIds += f.AttachmentId + ",");
ViewBag.MineFiles = mineFileIds.TrimEnd(',');
ViewBag.OriginalFiles = originalFileIds.TrimEnd(',');
}
return(PartialView(entity));
}
/// <summary>
/// 删除历史合同，且只能是Draft(未提交)的合同
/// </summary>
/// <param name="entity"></param>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult DeleteHistoryContract(CasContractEntity entity, string id)
{
if (string.IsNullOrWhiteSpace(id))
{
return(Json(AjaxResult.Error()));
}
else
{
var strsql = new StringBuilder();
strsql.AppendFormat("DELETE FROM CAS_CONTRACT WHERE CONTRACT_ID= {0} AND STATUS='1'",
Utils.ToSQLStr(id).Trim());
var val = DataAccess.SelectScalar(strsql.ToString());
return(Json(AjaxResult.Success()));
}
}
public ActionResult Edit(string id)
{
//合同信息
CasContractEntity entity = new CasContractEntity();
if (!string.IsNullOrEmpty(id))
{
//获取合同正文信息
entity = BusinessDataService.ContractApplayService.GetById <CasContractEntity>(id);
CasContractTypeEntity casContractTypeEntity = BusinessDataService
.ContractTypeManagementService
.GetById <CasContractTypeEntity>(entity.ContractTypeId);
ViewBag.ContractType = casContractTypeEntity;
//获取合同附件
List <CasAttachmentEntity> list = BusinessDataService.CommonHelperService.GetFilesBySourceId(entity.ContractId);
var mineFiles = list.Where(f => f.AttachmentType == 1).ToList();
ViewBag.MineFiles = mineFiles;
var originalFiles = new List <CasAttachmentEntity>();
if (!string.IsNullOrEmpty(entity.OriginalContractId))
{
originalFiles = BusinessDataService.CommonHelperService.GetFilesBySourceId(entity.OriginalContractId).Where(f => f.AttachmentType == 1).ToList();
}
else
{
originalFiles = list.Where(f => f.AttachmentType == 2).ToList();
}
ViewBag.OriginalFiles = originalFiles;
//获取模板编号合同数据
CasContractEntity templateNoContract = new CasContractEntity();
if (!(bool)casContractTypeEntity.IsTemplateContract && (bool)casContractTypeEntity.TemplateNo)
{
templateNoContract = BusinessDataService.ContractApplayService.GetById <CasContractEntity>(entity.TemplateNo);
}
ViewBag.templateNoContract = templateNoContract;
}
return(View(entity));
}
/// <summary>
/// 跳转到审批页
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ActionResult Approval(string id)
{
//合同信息
CasContractEntity entity = new CasContractEntity();
if (!string.IsNullOrEmpty(id))
{
//获取审批人信息
CasContractApproverEntity casContractApproverEntity = new CasContractApproverEntity {
ContractApproverId = id
};
casContractApproverEntity = BusinessDataService.CommonHelperService.GetApprover(casContractApproverEntity);
//获取合同正文信息
entity = BusinessDataService.ContractApplayService.GetById <CasContractEntity>(casContractApproverEntity.ContractId);
//获取合同类型信息
CasContractTypeEntity casContractTypeEntity = BusinessDataService
.ContractTypeManagementService
.GetById <CasContractTypeEntity>(entity.ContractTypeId);
ViewBag.ContractType = casContractTypeEntity;
//获取审批结果信息
DataTable approvalResultDt = BusinessDataService.CommonHelperService.GetApprovalResultDt(casContractApproverEntity.ContractId);
JsonSerializerSettings setting = new JsonSerializerSettings()
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
};
var ret = JsonConvert.SerializeObject(approvalResultDt, setting);
ViewBag.ApprovalResult = ret;
ViewBag.ContractApproverId = casContractApproverEntity.ContractApproverId;
List <CasAttachmentEntity> list = BusinessDataService.CommonHelperService.GetFilesBySourceId(entity.ContractId);
var mineFileIds = list.Where(f => f.AttachmentType == 1).ToList();
var originalFileIds = list.Where(f => f.AttachmentType == 2).ToList();
ViewBag.MineFiles = mineFileIds;
ViewBag.OriginalFiles = originalFileIds;
}
return(View(entity));
}
/// <summary>
/// 审批人审批
/// </summary>
/// <param name="comment"></param>
/// <param name="contractApproverId"></param>
/// <returns></returns>
public JsonResult Submit(int approvalType, string contractApproverId, string comment)
{
bool flag = false;
string msg = "";
//获取审批人信息
CasContractApproverEntity casContractApproverEntity = new CasContractApproverEntity {
ContractApproverId = contractApproverId
};
casContractApproverEntity = BusinessDataService.CommonHelperService.GetApprover(casContractApproverEntity);
if (casContractApproverEntity != null)
{
CasContractEntity casContractEntity = BusinessDataService.CommonHelperService.GetById <CasContractEntity>(casContractApproverEntity.ContractId);
CasUserEntity userEntity = BusinessDataService.CommonHelperService.GetById <CasUserEntity>(casContractEntity.CreatedBy);
if (casContractEntity.Status != ContractStatusEnum.WaitApproval.GetHashCode() && casContractEntity.Status != ContractStatusEnum.Resubmit.GetHashCode())
{
flag = false;
msg = "Approve failed: the contract is not in review process.";
return(Json(AjaxResult.Error(msg)));
}
if (casContractApproverEntity.Status == ContractApproverStatusEnum.WaitApproval.GetHashCode() || casContractApproverEntity.Status == ContractApproverStatusEnum.OverTime.GetHashCode())
{
CasContractApprovalResultEntity casContractApprovalResultEntity = new CasContractApprovalResultEntity
{
ContractApprovalResultId = Guid.NewGuid().ToString(),
ApprovalResult = approvalType, //审批结果
ApproverType = casContractApproverEntity.ApproverType, //审批用户类型
ApprovalOpinions = comment,
ApproverId = CurrentUser.CasUserEntity.UserId,
ContractApprovalStepId = casContractApproverEntity.ContractApprovalStepId,
ContractId = casContractApproverEntity.ContractId,
CreatedBy = CurrentUser.CasUserEntity.UserId,
CreateTime = DateTime.Now,
ApprovalTime = DateTime.Now,
LastModifiedBy = CurrentUser.CasUserEntity.UserId,
LastModifiedTime = DateTime.Now
};
flag = BusinessDataService.ContractApprovalService.Approval(casContractApprovalResultEntity, casContractApproverEntity);
if (flag && approvalType == 4)
{
var title = $@"e-Approval – Contract Reject";
var content = $@"Dear:{userEntity.EnglishName},</br>尊敬的：{userEntity.ChineseName}</br></br>The following contract is rejected:</br>以下合同被拒绝：</br></br>Contract Name 合同名称:{casContractEntity.ContractName} {casContractEntity.TemplateName}</br>Ferrero Entity 费列罗方 ：{casContractEntity.FerreroEntity}</br>Counter Party 相对方:{casContractEntity.CounterpartyEn} {casContractEntity.CounterpartyCn}</br>Rejected by 拒绝审批人:{ CurrentUser.CasUserEntity.EnglishName}{ CurrentUser.CasUserEntity.ChineseName}</br>Reject Reason 拒绝理由:{comment}</br></br>Please review your contract and resubmit or close the request.</br>请检查合同相关内容，重新提交或关闭合同。</br></br>Ferrero China Contract Approval System</br>费列罗中国合同审批系统";
var cc = "*****@*****.**";
var reciever = userEntity.Email;
SendEmail.Send(reciever, cc, title, content);
}
}
else
{
flag = false;
msg = "Approve failed: the contract is already approved.";
}
}
else
{
flag = false;
msg = "Operation failed: cannot find the approver's info.";
}
return(Json(flag ? AjaxResult.Success() : AjaxResult.Error(msg)));
}
public ActionResult ContractReviewProcessReport(string id)
{
CasContractEntity entity = new CasContractEntity();
if (!string.IsNullOrEmpty(id))
{
var sql = @"
SELECT CAS_CONTRACT.CONTRACT_ID, CAS_CONTRACT.CONTRACT_NO, CAS_CONTRACT.FERRERO_ENTITY,
CAS_CONTRACT.CONTRACT_TYPE_NAME, CAS_CONTRACT.COUNTERPARTY_EN,
CAS_CONTRACT.COUNTERPARTY_CN, CAS_CONTRACT.CONTRACT_NAME,
CAS_CONTRACT.EFFECTIVE_DATE, CAS_CONTRACT.EXPIRATION_DATE,
CAS_CONTRACT.CONTRACT_OWNER, CAS_CONTRACT.CONTRACT_INITIATOR,
CAS_CONTRACT.APPLY_DATE,CAS_CONTRACT.CREATE_TIME,
--CASE CAS_CONTRACT.Capex WHEN 1 THEN 'YES' ELSE 'NO' END AS CapexResult,
Capex,
--CASE CAS_CONTRACT.IS_MASTER_AGREEMENT WHEN 1 THEN 'YES'
-- ELSE 'NO' END AS ISMASTERAGREEMENTResult,
IS_MASTER_AGREEMENT,
--CASE CAS_CONTRACT.SUPPLEMENTARY
-- WHEN 1 THEN 'YES' ELSE 'NO'
-- END AS SUPPLEMENTARYResult,
SUPPLEMENTARY,
-- case CAS_CONTRACT.BUDGET_TYPE WHEN 0 THEN 'Overheads'
-- WHEN 1 THEN 'Non-overheads'
-- WHEN 2 THEN 'industrial'
-- ELSE 'Error'
--END AS BUDGET_TYPE,
BUDGET_TYPE,
CAS_CONTRACT.TEMPLATE_NO, CAS_CONTRACT.CONTRACT_PRICE,
CAS_CONTRACT.CURRENCY,
-- case CAS_CONTRACT.CURRENCY
-- WHEN 1 THEN N'CNY'
-- WHEN 2 THEN N'USD'
-- WHEN 3 THEN N'EURO'
-- WHEN 4 THEN N'SGD'
-- WHEN 5 THEN N'HKD'
-- WHEN 6 THEN N'GBP'
-- WHEN 7 THEN N'CAD'
-- WHEN 8 THEN N'AUD'
-- WHEN 99 THEN CAS_CONTRACT.CURRENCY
-- ELSE ''
--END AS CURRENCY,
CAS_CONTRACT.PREPAYMENT_AMOUNT, CAS_CONTRACT.PREPAYMENT_PERCENTAGE,
CAS_CONTRACT.CONTRACT_KEY_POINTS
FROM CAS_CONTRACT
WHERE CAS_CONTRACT.CONTRACT_ID = @CONTRACT_ID";
DataAccessParameterCollection param = new DataAccessParameterCollection();
ColumnInfo columnInfo = new ColumnInfo("CONTRACT_ID", "CONTRACT_ID", true, typeof(string));
param.AddWithValue(columnInfo, id);
List <CasContractEntity> list = null;
try
{
list = DataAccess.Select <CasContractEntity>(sql, param);
}
catch (Exception)
{
}
if (list != null && list.Any())
{
entity = list[0];
//CasContractTypeEntity casContractTypeEntity = BusinessDataService.ContractTypeManagementService.GetById<CasContractTypeEntity>(entity.ContractTypeId);
//ViewBag.ContractType = casContractTypeEntity;
//List<CasAttachmentEntity> list = BusinessDataService.CommonHelperService.GetFilesBySourceId(id);
//var mineFileIds = list.Where(f => f.AttachmentType == 1).ToList();
//var originalFileIds = list.Where(f => f.AttachmentType == 2).ToList();
//ViewBag.MineFiles = mineFileIds;
//ViewBag.OriginalFiles = originalFileIds;
#region 合同审批结果
//获取审批结果信息
DataTable approvalResultDt = BusinessDataService.CommonHelperService.GetReportApprovalResultDt(entity.ContractId);
JsonSerializerSettings setting = new JsonSerializerSettings()
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
};
var ret = JsonConvert.SerializeObject(approvalResultDt, setting);
ViewBag.ApprovalResult = ret;
#endregion
}
}
return(View(entity));
}
/// <summary>
/// 导出合同列表时查询条件
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public DataTable ExportData(CasContractEntity entity)
{
var sql = $@" SELECT DISTINCT CON.CONTRACT_NAME AS 'Contract Name',CON.CONTRACT_TYPE_NAME AS 'Contract Type',
(case when CON.CONTRACT_GROUP=1 and CON.IS_TEMPLATE_CONTRACT=0 then CON.CONTRACT_NO else CONTRACT_SERIAL_NO end ) AS 'Contract Number',
(CASE CON.CONTRACT_GROUP WHEN '1' THEN N'Other contract' WHEN '2' THEN N'History contract'else N'Errot' END ) AS 'Contract Group',
CON.FERRERO_ENTITY AS 'Ferrero Party',CON.COUNTERPARTY_EN AS 'Counterparty-EN',COUNTERPARTY_CN AS 'Counterparty-CN',
CONTRACT_OWNER AS 申请部门,CON.CONTRACT_INITIATOR AS 经办人,(CASE CON.STATUS WHEN '1' THEN N'Draft' WHEN '2' THEN N'In review process'WHEN '3' THEN N'Approved' WHEN '4' THEN N'Rejected' WHEN '5' THEN N'Completed' WHEN '6' THEN N'Closed' WHEN '7' THEN N'Terminated' WHEN '8' THEN N'In review process'ELSE N'Error' END ) AS 'Status',
USR.ENGLISH_NAME AS 'Contract Initiator',CON.CONTRACT_PRICE AS 'Contract Price with Tax' ,
CON.CURRENCY AS 'Currency',PREPAYMENT_AMOUNT AS 'Prepayment Amount', CON.PREPAYMENT_PERCENTAGE AS 'Prepayment Percentage',
CON.EFFECTIVE_DATE AS 'Validate From',CON.EXPIRATION_DATE AS 'End To',CON.MODIFICATION_POINTS AS 'Summary of revision',
CON.CONTRACT_KEY_POINTS AS 'Key points of contract',TEMPLATE_NO AS 'Template No',CON.TEMPLATE_NAME AS 'Template Name',
TEMPLATE_OWNER AS 模板合同申请部门 ,CON.TEMPLATE_INITIATOR AS 模板合同经办人,CON.SCOPE_OF_APPLICATION AS 'Scope of Application',
CON.APPLY_DATE AS 'Submission Date',USR1.ENGLISH_NAME AS 'Creator',CON.CREATE_TIME AS 'Create Time',USR2.ENGLISH_NAME AS 修改人 ,CON.LAST_MODIFIED_TIME AS 'Modify Time',
(CASE CON.IS_MASTER_AGREEMENT WHEN 'True' THEN N'Yes' WHEN 'False' THEN N'No'else N'Other' END ) AS 'Master agreement or not',
(CASE CON.CAPEX WHEN 'True' THEN N'Yes' WHEN 'False' THEN N'No'else N'Other' END ) AS 'Capex or not',
(CASE CON.SUPPLEMENTARY WHEN 'True' THEN N'Yes' WHEN 'False' THEN N'No'else N'Other' END ) AS 'Supplementary or not',
(CASE WHEN CON.STATUS IN('3','5') THEN CAR.APPROVAL_TIME ELSE NULL END) AS 完成审批日期,ATT.UploadTime AS 上传盖章合同时间,
STUFF ((SELECT ','+ CCF.PR_NO FROM CAS_CONTRACT_FILING CCF
WHERE CCF.CONTRACT_ID =CON.CONTRACT_ID AND CCF.STATUS <> {ContractFilingEnum.Save.GetHashCode()}
FOR XML PATH ('')),1,1,'') AS 'PR Number',
STUFF ((SELECT ','+ CCF.PO_NO FROM CAS_CONTRACT_FILING CCF
where CCF.CONTRACT_ID =CON.CONTRACT_ID AND CCF.STATUS <> {ContractFilingEnum.Save.GetHashCode()}
FOR XML PATH ('')),1,1,'') AS 'PO Number'
FROM CAS_CONTRACT CON INNER JOIN dbo.CAS_USER USR ON CON.SUPPLIER=USR.USER_ID INNER JOIN dbo.CAS_USER USR1 ON CON.CREATED_BY=USR1.USER_ID INNER JOIN dbo.CAS_USER USR2 ON CON.CREATED_BY=USR2.USER_ID
LEFT JOIN (SELECT CONTRACT_ID,MAX(APPROVAL_TIME) AS APPROVAL_TIME FROM dbo.CAS_CONTRACT_APPROVAL_RESULT WHERE APPROVAL_RESULT IN ('2','3') GROUP BY CONTRACT_ID) CAR ON CON.CONTRACT_ID=CAR.CONTRACT_ID
LEFT JOIN (SELECT SOURCE_ID,MAX(CREATE_TIME) AS UploadTime FROM dbo.CAS_ATTACHMENT WHERE ATTACHMENT_TYPE='3' GROUP BY SOURCE_ID) ATT ON ATT.SOURCE_ID = CON.CONTRACT_ID
WHERE 1=1 " ;
if (!string.IsNullOrWhiteSpace(entity.ContractName))
{
sql = sql + $@" AND CONTRACT_NAME LIKE N'%{ entity.ContractName.Trim()}%' ";
}
if (!string.IsNullOrWhiteSpace(entity.ContractNo))
{
sql = sql + $@" AND CONTRACT_NO LIKE N'%{entity.ContractNo.Trim()}%'";
}
if (!string.IsNullOrWhiteSpace(entity.FerreroEntity))
{
sql = sql + $@" AND FERRERO_ENTITY LIKE N'%{ entity.FerreroEntity.Trim()}%' ";
}
if (!string.IsNullOrWhiteSpace(entity.ContractInitiator))
{
sql = sql + $@" AND CONTRACT_INITIATOR LIKE N'%{ entity.ContractInitiator.Trim()}%' ";
}
if (!string.IsNullOrWhiteSpace(entity.CounterpartyCn))
{
sql = sql + $@" AND OUNTERPARTY_CN LIKE N'%{ entity.CounterpartyCn}%' ";
}
DateTime dt = DateTime.Parse("1970-01-01 00:00:00");
if (entity.EffectiveDate > dt)
{
sql = sql + $@" AND EFFECTIVE_DATE='{ entity.EffectiveDate}' ";
}
if (entity.ExpirationDate > dt)
{
sql = sql + $@" AND EXPIRATION_DATE='{ entity.ExpirationDate}' ";
}
if (entity.Status != 0)
{
sql = sql + $@" AND STATUS='{ entity.Status}' ";
}
if (entity.ContractGroup != 0)
{
sql = sql + $@" AND CONTRACT_GROUP='{ entity.ContractGroup}' ";
}
if (entity.ExportTypeData == "FTS")//全部合同FTS导出
{
sql = sql + $@" AND FERRERO_ENTITY='Ferrero Trading (Shanghai) Co., Ltd.' ";
}
if (entity.ExportTypeData == "FFH")//全部合同FFH导出
{
sql = sql + $@" AND FERRERO_ENTITY='Ferrero Food (Hangzhou) Co., Ltd.' ";
}
if (entity.ExportTypeData == "IApplied")//我发起的合同
{
sql = sql + $@" AND SUPPLIER='{WebCaching.UserId}' ";
}
if (entity.ExportTypeData == "ApprovalByMe")//我审批的合同
{
sql = sql.Replace("WHERE 1=1", " INNER JOIN dbo.CAS_CONTRACT_APPROVAL_RESULT CAR ON CON.CONTRACT_ID = CAR.CONTRACT_ID WHERE 1=1 ");
sql = sql + $@" AND CAR.APPROVER_ID='{WebCaching.UserId}' ";
}
if (entity.ExportTypeData == "MyDepart")//我的部门合同
{
sql = sql.Replace("WHERE 1=1", " INNER JOIN dbo.CAS_USER CUS ON CON.SUPPLIER = CUS.USER_ID WHERE 1=1");
sql = sql + $@" AND CUS.DEPARMENT_CODE=(SELECT DEPARMENT_CODE FROM dbo.CAS_USER WHERE USER_ID='{WebCaching.UserId}') ";
}
if (entity.ExportTypeData == "ISupport")//我支持的合同
{
sql = sql.Replace("WHERE 1=1", " INNER JOIN dbo.CAS_PO_APPROVAL_SETTINGS PAS ON CON.CONTRACT_TYPE_ID=PAS.CONTRACT_TYPE_ID INNER JOIN dbo.CAS_CONTRACT_FILING CCF ON CON.CONTRACT_ID = CCF.CONTRACT_ID WHERE 1=1 ");
sql = sql + $@" AND CCF.STATUS <> {ContractFilingEnum.Save.GetHashCode()} AND PAS.USER_ID='{WebCaching.UserId}'";
if (entity.PO != "")
{
sql = sql + $@" AND CCF.PO_NO LIKE N'%{entity.PO}%'";
}
if (entity.PR != "")
{
sql = sql + $@" AND CCF.PR_NO LIKE N'%{entity.PR}%'";
}
}
var tableValue = DataAccess.SelectDataSet(sql).Tables[0];
return(tableValue);
}
public ActionResult ExportFileList()
{
CasContractEntity entity = new CasContractEntity();
entity.ExportTypeData = Request["ExportTypeData"];//导出的时候区分网页用
entity.ContractName = Request["CONTRACT_NAME"];
entity.ContractNo = Request["CONTRACT_NO"];
entity.CounterpartyCn = Request["OUNTERPARTY_CN"];
entity.FerreroEntity = Request["FERRERO_ENTITY"];
entity.ContractInitiator = Request["CONTRACT_INITIATOR"];
entity.PO = Request["PO"];
entity.PR = Request["PR"];
entity.EffectiveDate = Request["EFFECTIVE_DATE"] != ""?Convert.ToDateTime(Request["EFFECTIVE_DATE"]): DateTime.Parse("1970-01-01 00:00:00");
entity.ExpirationDate = Request["EXPIRATION_DATE"] != ""? Convert.ToDateTime(Request["EXPIRATION_DATE"]): DateTime.Parse("1970-01-01 00:00:00");
entity.Status = Request["STATUS"] != ""?Convert.ToInt32(Request["STATUS"]):0;
entity.ContractGroup = Request["CONTRACT_GROUP"] != ""? Convert.ToInt32(Request["CONTRACT_GROUP"]):0;
DataTable contractList = BusinessDataService.ContractManagementService.ExportData(entity);
DataTable dt = contractList;//获取需要导出的datatable数据
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//row1.RowStyle.FillBackgroundColor = "";
for (int i = 0; i < dt.Columns.Count; i++)
{
//if (dt.Columns[i].DataType == typeof(Boolean))
//{
// dt.Columns[i].DataType = typeof(String);
//}
row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
row1.Cells[i].CellStyle.Alignment = HorizontalAlignment.CENTER;
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
//if (dt.Rows[i][j].ToString().Trim()=="True")
//{
// dt.Rows[i][j] = "是";
//}
//if (dt.Rows[i][j].ToString().Trim() == "False")
//{
// dt.Rows[i][j] = "否";
//}
rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
}
}
string strdate = DateTime.Now.ToString("yyyyMMddhhmmss");//获取当前时间
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd.ms-excel", strdate + "Excel.xls"));
}
public ActionResult CloseContractByAdminFTS(CasContractEntity entity, string id)
{
var a = BusinessDataService.ContractManagementService.CloseContractByAdmin(id);
return(Json(AjaxResult.Success()));
}
public override void Run(Hashtable parameters)
{
ContractApprovalBLL bll = new ContractApprovalBLL();
ContractManagementBLL contractManagementBLL = new ContractManagementBLL();
List <CasUserEntity> listApprovers = bll.GetAllPendingApprovalAndOvertimeContractsApprovers();
foreach (CasUserEntity approver in listApprovers)
{
//待审批和超时合同
List <CasContractApproverEntity> listPendingApprovalAndOverTimeContractApprovers = bll.GetPendingApprovalAndOvertimeContractApprovers(approver.UserId);
//待审批合同
List <CasContractApproverEntity> listPendingApprovalContractApprovers = listPendingApprovalAndOverTimeContractApprovers.Where(p => p.Status == ContractApproverStatusEnum.WaitApproval.GetHashCode()).ToList();
//超时合同
List <CasContractApproverEntity> listOverTimeContractApprovers = listPendingApprovalAndOverTimeContractApprovers.Where(p => p.Status == ContractApproverStatusEnum.OverTime.GetHashCode()).ToList();
//邮件标题
string mailTitle = $@"e-Approval - Contract Approval List";
//邮件正文
string mailContent = $@"Dear {approver.EnglishName},<br/>尊敬的：{approver.ChineseName}";
if (listPendingApprovalContractApprovers.Count > 0)
{
mailContent += $@"<br/>The following contracts have been submitted for your approval(please kindly confirm your approval or rejection within 3 working days):<br/>以下合同申请需要您的审核（请在3个工作日内批准或拒绝申请）：<br/>
</br><table style='font-family:verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse:collapse;'><thead>
<tr><th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>Initiator 申请人</th>
<th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>Contract Owner 合同经办部门</th>
<th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>Contract Name 合同名称</th>
<th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>Ferrero Entity 费列罗方</th>
<th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>Counter Party 相对方</th>
</tr></thead><tbody>";
foreach (var pendingApproval in listPendingApprovalContractApprovers)
{
CasContractEntity contract = contractManagementBLL.GetById <CasContractEntity>(pendingApproval.ContractId);
CasUserEntity userEntity = contractManagementBLL.GetById <CasUserEntity>(contract.CreatedBy);
mailContent += $@"<tr>
<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{userEntity.ChineseName}</td>
<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{contract.ContractOwner}{contract.TemplateOwner}</td>
<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{contract.ContractName}{contract.TemplateName}</td>
<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{contract.FerreroEntity}</td>
<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{contract.CounterpartyEn}{contract.CounterpartyCn}</td>
</tr>";
}
mailContent += $@"</tbody></table></br>";
}
if (listOverTimeContractApprovers.Count > 0)
{
mailContent += $@"<br/>Note below contracts are overdue, please review the contracts at your earliest convenient.<br/>请注意以下合同已超期，请尽快进行审批。<br/>
</br><table style='font-family:verdana,arial,sans-serif;font-size:11px;color:#333333;border-width: 1px;border-color: #666666;border-collapse:collapse;'><thead>
<tr><th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>Initiator 申请人</th>
<th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>Contract Owner 合同经办部门</th>
<th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>Contract Name 合同名称</th>
<th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>Ferrero Entity 费列罗方</th>
<th style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #dedede;'>Counter Party 相对方</th>
</tr></thead><tbody>";
foreach (var listOverTime in listOverTimeContractApprovers)
{
CasContractEntity contract = contractManagementBLL.GetById <CasContractEntity>(listOverTime.ContractId);
CasUserEntity userEntity = contractManagementBLL.GetById <CasUserEntity>(contract.CreatedBy);
mailContent += $@"<tr>
<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{userEntity.ChineseName}</td>
<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{contract.ContractOwner}{contract.TemplateOwner}</td>
<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{contract.ContractName}{contract.TemplateName}</td>
<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{contract.FerreroEntity}</td>
<td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{contract.CounterpartyEn}{contract.CounterpartyCn}</td>
</tr>";
}
mailContent += $@"</tbody></table></br>";
}
//foreach (CasContractApproverEntity casContractApproverEntity in listPendingApprovalAndOverTimeContractApprovers)
//{
// CasContractEntity contract = contractManagementBLL.GetById<CasContractEntity>(casContractApproverEntity.ContractId);
// string statusName = "";
// switch (casContractApproverEntity.Status)
// {
// case (2): statusName = "待审批"; break;
// case (4): statusName = "超时"; break;
// default: statusName = "待审批"; break;
// }
// string approveTypeName = "";
// switch (casContractApproverEntity.ApproverType)
// {
// case (1): approveTypeName = "领导"; break;
// case (2): approveTypeName = "大区总监"; break;
// case (3): approveTypeName = "部门总监"; break;
// case (4): approveTypeName = "审批部门"; break;
// default: approveTypeName = "审批部门"; break;
// }
// mailContent += $@"<tr><td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{contract.ContractName}</td><td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{approveTypeName}</td><td style='border-width: 1px;padding: 8px;border-style: solid;border-color: #666666;background-color: #ffffff;'>{statusName}</td></tr>";
//}
mailContent += "</br></br>Ferrero China Contract Approval System</br>费列罗中国合同审批系统";
var cc = "*****@*****.**";
#if DEBUG
string reciever = "*****@*****.**";
#else
string reciever = approver.Email;
#endif
SendEmail.Send(reciever, mailTitle, mailContent);
}
}
