public static string UpdateInfos(Dictionary <string, string> dict, string id)
{
string res = UserInfoSrv.UpdateInfos(dict, id);
SqlExceRes sqlRes = new SqlExceRes(res);
return(sqlRes.GetResultString("修改成功", ""));
}
/// <summary>
/// 删除分组信息
/// </summary>
/// <param name="userId">创建者id</param>
/// <param name="groupName">分组名称</param>
/// <returns></returns>
public static string DeleteGroup(string userId, string groupName)
{
JObject res = new JObject();
if (string.IsNullOrEmpty(userId) || string.IsNullOrEmpty(groupName))
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "参数缺少或为空");
return(res.ToString());
}
string sql = string.Format("delete from yl_email_group where UserId='{0}' and GroupName='{1}'"
, userId, groupName);
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(sql));
if (r.Result == SqlExceRes.ResState.Success)
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", "删除成功");
}
else
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", r.ExceMsg);
}
return(res.ToString());
}
public static string updateActualFee(ArrayList list)
{
ArrayList codeList = new ArrayList();
ArrayList actualFeeList = new ArrayList();
foreach (Dictionary <string, string> dict in list)
{
if (dict != null)
{
codeList.Add(dict["Id"].ToString());
actualFeeList.Add(dict["ApprovalNumber"].ToString());
}
else
{
codeList.Add("");
actualFeeList.Add("");
}
}
string res = OperationDeliverSrv.updateActualFee(list);
OperationDeliverSrv.UpdateOperationApprovalTime(codeList);
// 插入一条数据到销售日表中
OperationDeliverSrv.insertSalesData(codeList);
string[] msgs = res.Split(';');
DataTable dtUser = ReimbursementSrv.GetUserNameAndWxUserId();
WxCommon wx = new WxCommon("DeliverApplyReport",
"vvsnJs9JYf8AisLWOE4idJbdR1QGc7roIcUtN6P2Lhc",
"1000009",
"");
for (int i = 0; i < msgs.Length - 1; i++)
{
SqlExceRes sqlRes = new SqlExceRes(msgs[i]);
if (sqlRes.Result == SqlExceRes.ResState.Success)
{
Dictionary <string, string> dict = (Dictionary <string, string>)list[i];
string WxUserId = "";
foreach (DataRow row in dtUser.Rows)
{
if (row["userName"].ToString() == dict["ApprovalName"])
{
WxUserId = row["wechatUserId"].ToString();
break;
}
}
if (!string.IsNullOrEmpty(WxUserId))
{
// 发送审批的消息给提交者
wx.SendWxMsg(WxUserId, "审批通知", "您编号为" + codeList[i] + "的发货单据已被运营部进行实发数量复审,审批人为:"
+ dict["ApprovalName"] + "，实发数量为" + actualFeeList[i] + "，请知悉"
, "http://yelioa.top//mDeliverApplyReportAppRoval.aspx?type=0&docCode=" + codeList[i]);
}
}
}
return(res);
}
public static string Delete(string id)
{
string res = UserInfoSrv.Delete(id);
SqlExceRes sqlRes = new SqlExceRes(res);
return(sqlRes.GetResultString("操作成功", ""));
}
public static string AddTree(Dictionary <string, string> dict)
{
string res = UserInfoSrv.AddTree(dict);
SqlExceRes sqlRes = new SqlExceRes(res);
return(sqlRes.GetResultString("新建成功", "部门名称有重复，请重新输入"));
}
/// <summary>
/// 提交单据
/// </summary>
/// <param name="fieldList">单据所有字段相关数据</param>
/// <param name="userId">提交人UserId</param>
/// <param name="n">遇到错误时提交次数（两个或以上人员同时提交单据时会报错）</param>
/// <returns></returns>
private static JObject LeafDepartmentSubmit(JArray fieldList, string userId, int n)
{
JObject res = new JObject();
if (n > 0)
{
string docCode = "0";
string ErrMsg = "";
DataSet ds = NewBranchRegistrationSrv.GetMaxDocCode(ref ErrMsg);
if (ds == null)
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", ErrMsg);
}
else
{
List <string> sqlList = new List <string>();
docCode = CreateDocCode(ds.Tables[0].Rows[0][0].ToString());
JObject DocJObject = new JObject();
DocJObject.Add("Code", docCode); //单据号
DocJObject.Add("Level", "1"); //下一审批级别
DocJObject.Add("SubmitterUserId", userId); //提交人userId
DocJObject.Add("InsertOrUpdate", "0"); //0表示新增
DocJObject.Add("State", "审批中");
DocJObject.Add("CreateTime", DateTime.Now);
string departmentId = SearchDepartment(fieldList);
JArray approverList = GetNextApprover(ds.Tables[1], 1, departmentId, 0);
string approverUserId = JArrayToString(approverList, "userId"); //审批人userId
string approverWechatUserId = JArrayToString(approverList, "wechatUserId"); //审批人wechatUserId
DocJObject.Add("ApproverUserId", approverUserId);
sqlList.Add(SqlHelper.GetInsertString(DocJObject, "cost_sharing_record"));
sqlList.Add(GetDetailInsertSQL(fieldList, "0", userId, docCode));
SqlExceRes msg = new SqlExceRes(SqlHelper.Exce(sqlList.ToArray()));
if (msg.Result == 0)
{
//发信息给提交者还有下级审批人
res.Add("ErrCode", 0);
res.Add("ErrMsg", "操作成功");
}
else//错误，等待0.2秒重新提交
{
Thread.Sleep(200);
res = LeafDepartmentSubmit(fieldList, userId, n - 1);
}
}
}
else
{
res.Add("ErrCode", 3);
res.Add("ErrMsg", "网络超时，请重新提交！");
}
return(res);
}
public static string InsertInfos(Dictionary <string, string> dict)
{
string res = CostSharingInfoSrv.InsertInfos(dict);
SqlExceRes sqlRes = new SqlExceRes(res);
return(sqlRes.GetResultString("新建成功", "网点信息有重复，请重新输入"));
}
public static string returnDocument(string table, string docCode, UserInfo user)
{
String res = "";
DataSet ds = ApprovalFlowSrv.GetDocumentInfo(table, docCode, ref res);
if (ds == null)//出错，返回错误信息
{
return("获取单据信息出错，错误信息：" + res);
}
if (Convert.ToInt32(ds.Tables[3].Rows[0]["Level"]) > 1)
{
return("该单据已被审批，无法进行撤回");
}
//保存单据撤回记录
SqlExceRes sqlRes = new SqlExceRes(ApprovalFlowSrv.returnDocument(user, ds));
res = sqlRes.GetResultString("撤回成功！", "该单据已被审批，无法进行撤回！");
if (res != "撤回成功！")
{
return("撤回单据出错，错误信息：" + res);
}
// 更新表单状态
ApprovalFlowSrv.UpdateDocStatus(table, docCode, "未提交", 0);
//清除审批人相关信息
ApprovalFlowSrv.ClearCurrentApproverInfo(table, docCode);
return(res);
}
/// <summary>
/// 标记未读为已读，或标记已读为未读
/// </summary>
/// <param name="emailId">邮件ID</param>
/// <param name="status">已读、未读</param>
/// <returns></returns>
public static string ChangeStatusToRead(string emailId, string status)
{
JObject res = new JObject();
if (string.IsNullOrEmpty(emailId))
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "参数缺少");
return(res.ToString());
}
Dictionary <string, string> dict = new Dictionary <string, string>();
if (status == "未读")
{
dict.Add("Status", "已读");
}
else if (status == "已读")
{
dict.Add("Status", "未读");
}
string sql = SqlHelper.GetUpdateString(dict, "yl_email_recipient", "where emailId=" + emailId);
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(sql));
if (r.Result == SqlExceRes.ResState.Success)
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", "操作成功");
}
else
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", r.ExceMsg);
}
return(res.ToString());
}
public static string DeleteTemplate(string type)
{
string res = LeaveStockInfoSrv.DeleteTemplate(type);
SqlExceRes sqlRes = new SqlExceRes(res);
return(sqlRes.GetResultString("删除成功！", ""));
}
/*以下是提交或审批的相关方法*/
private static JObject SubmitDoc(string userId, string docId, JArray jarray, int n)
{
JObject res = new JObject();
if (n > 0)
{
string docCode = "0";
string ErrMsg = "";
DataSet ds = CostSharingDeleteSrv.GetMaxDocCode(ref ErrMsg);
if (ds == null)
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", ErrMsg);
}
else
{
List <string> sqlList = new List <string>();
docCode = CreateDocCode(ds.Tables[0].Rows[0][0].ToString());
JObject DocJObject = new JObject();
DocJObject.Add("Code", docCode); //单据号
DocJObject.Add("Level", "1"); //下一审批级别
DocJObject.Add("SubmitterUserId", userId); //提交人userId
DocJObject.Add("InsertOrUpdate", "3"); //3表示丢失
DocJObject.Add("State", "审批中");
DocJObject.Add("NewCostSharingId", docId); //new_cost_sharing的Id
DocJObject.Add("CreateTime", DateTime.Now);
DocJObject.Add("ApproverUserId", "100000142"); //公司总经理--吕正和
sqlList.Add(SqlHelper.GetInsertString(DocJObject, "cost_sharing_record"));
sqlList.Add(GetDetailInsertSQL(jarray, "0", userId, docCode));
SqlExceRes msg = new SqlExceRes(SqlHelper.Exce(sqlList.ToArray()));
if (msg.Result == 0)
{
//发信息给提交者还有下级审批人
res.Add("ErrCode", 0);
res.Add("ErrMsg", "操作成功");
}
else//错误，等待0.2秒重新提交
{
Thread.Sleep(200);
res = SubmitDoc(userId, docId, jarray, n - 1);
}
}
}
else
{
res.Add("ErrCode", 3);
res.Add("ErrMsg", "网络超时，请重新提交！");
}
return(res);
}
public static string SubmitDocument(string table, string docCode, UserInfo user, string url1, string url2,
string appSecret, string thisAppName, string agentId)
{
string res = "";
DataSet ds = ApprovalFlowSrv.GetDocumentInfo(table, docCode, ref res);
if (ds == null)//出错，返回错误信息
{
return("获取单据信息出错，错误信息：" + res);
}
if (ds.Tables[0].Rows.Count > 0 && ds.Tables[2].Rows.Count > 0)
{
return("该单据已提交，请勿重复提交");
}
//保存审批记录信息
Dictionary <string, string> dict = new Dictionary <string, string>();
SqlExceRes sqlRes = new SqlExceRes(ApprovalFlowSrv.SubmitDocumentSaveRecord(user, ds, ref dict));
res = sqlRes.GetResultString("提交成功！", "该单据已提交，请勿重复提交！");
if (res != "提交成功！")
{
return("提交单据出错，错误信息：" + res);
}
//保存所有审批人信息
JArray listApproverInfo = null;//所有审批人信息
ApprovalFlowSrv.SaveAllApproverInfo(user, ds, dict, ref listApproverInfo);
//更新单据状态,state="审批中",level=1
res = ApprovalFlowSrv.UpdateDocStatus(table, docCode, "审批中", 1);
DataSet userDs = ApprovalFlowSrv.GetUserInfoByUserId(
ApprovalFlowSrv.GetApproverIddByLevel(1, listApproverInfo));
string approverIds = "";
foreach (DataTable dt in userDs.Tables)
{
approverIds += dt.Rows[0]["wechatUserId"].ToString() + "|";
}
approverIds = approverIds.Substring(0, approverIds.Length - 1);
WxNetSalesHelper wxNetSalesHelper = new WxNetSalesHelper(appSecret, thisAppName, agentId);
//// 给待审批人发送消息
//wxNetSalesHelper.GetJsonAndSendWxMsg(approverIds, "请及时审批 提交人为:" + user.userName
// + "的单据,谢谢!", url2, agentId);
//// 给提交人发送消息
wxNetSalesHelper.GetJsonAndSendWxMsg(user.wechatUserId, "您的审批单据已提交 请耐心等待审批人审批", url1, agentId);
return(res);
}
//public static DataTable getCommonPurchaseData(int year, int month)
//{
// DataSet ds = ImportPurchaseSrv.getCommonPurchaseData(year, month);
// if (ds == null)
// return null;
// return ds.Tables[0];
//}
public static string insertOrUpdateCommonPurchaseData(List <Dictionary <string, string> > dictList, string year, string month)
{
List <string> sqls = new List <string>();
foreach (Dictionary <string, string> dict in dictList)
{
dict.Add("year", year);
dict.Add("month", month);
string sql = ImportPurchaseSrv.insertOrUpdateCommonPurchaseData(dict);
sqls.Add(sql);
}
string msg = SqlHelper.Exce(sqls.ToArray());
SqlExceRes sqlExceRes = new SqlExceRes(msg);
return(sqlExceRes.GetResultString("保存成功", "保存失败"));
}
/// <summary>
/// 审批流程--创建表单。
/// 需要在dict当中添加"DocumentTableName"的key，用于保存表单表名
/// </summary>
/// <param name="dict"></param>
/// <returns></returns>
public static string CreateDocument(Dictionary <string, string> dict)
{
//单据草稿为可编辑状态
if (!dict.Keys.Contains("Editable"))
{
dict.Add("Editable", "1");
}
else
{
dict.Remove("Editable");
dict.Add("Editable", "1");
}
SqlExceRes sqlRes = new SqlExceRes(ApprovalFlowSrv.CreateDocument(dict));
return(sqlRes.GetResultString("提交成功！", "该单据已创建，请勿重复创建！"));
}
/// <summary>
/// 创建分组
/// </summary>
/// <param name="userId">创建者id</param>
/// <param name="membersString">分组成员id集合string</param>
/// <param name="groupName">分组名称</param>
/// <returns></returns>
public static string CreateGroup(string userId, string membersString, string groupName)
{
JObject res = new JObject();
if (string.IsNullOrEmpty(userId) || string.IsNullOrEmpty(membersString) || string.IsNullOrEmpty(groupName))
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "参数缺少或为空");
return(res.ToString());
}
//string[] members = membersString.Split(',');
//foreach(string member in members)
//{
//}
string sql = string.Format("select GroupName from yl_email_group where GroupName='{0}'", groupName);
object resTemp = SqlHelper.Scalar(sql);
if (Object.Equals(resTemp, null))
{
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("UserId", userId);
dict.Add("GroupName", groupName);
dict.Add("GroupMember", membersString);
sql = SqlHelper.GetInsertString(dict, "yl_email_group");
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(sql));
if (r.Result == SqlExceRes.ResState.Success)
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", "分组创建成功");
}
else
{
res.Add("ErrCode", 3);
res.Add("ErrMsg", r.ExceMsg);
}
}
else
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", "分组名称已存在");
}
return(res.ToString());
}
public static string saveRecord(List <Dictionary <string, string> > dictList, string name)
{
List <string> sqls = new List <string>();
foreach (Dictionary <string, string> dict in dictList)
{
string sql = ImportPurchaseSrv.saveReocrd(dict, name);
if (sql != null)
{
sqls.Add(sql);
}
}
string msg = SqlHelper.Exce(sqls.ToArray());
SqlExceRes sqlExceRes = new SqlExceRes(msg);
return(sqlExceRes.GetResultString("保存成功", "保存失败"));
}
private static JObject Approve(string userId, JArray jarray, string docCode)
{
JObject res = new JObject();
List <string> sqlList = new List <string>();
string level = "0";
JObject DocJObject = new JObject();
if (userId == "100000142")
{
level = "1";
DocJObject.Add("Level", "2"); //下一审批级别
DocJObject.Add("ApproverUserId", "100000225"); //企管部--程丹凤
}
else
{
level = "2";
DocJObject.Add("Level", "3");//下一审批级别
DocJObject.Add("State", "已审批");
DocJObject.Add("ApproverUserId", "");
}
sqlList.Add(SqlHelper.GetUpdateString(DocJObject, "cost_sharing_record", "where Code='" + docCode + "'"));
sqlList.Add(GetDetailInsertSQL(jarray, level, userId, docCode));
SqlExceRes msg = new SqlExceRes(SqlHelper.Exce(sqlList.ToArray()));
if (msg.Result == 0)
{
//发信息给提交者还有下级审批人
res.Add("ErrCode", 0);
res.Add("ErrMsg", "操作成功");
}
else
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", msg.ExceMsg);
}
return(res);
}
/// <summary>
/// 保存分组
/// </summary>
/// <param name="userId">创建者id</param>
/// <param name="membersString">分组成员id集合string</param>
/// <param name="groupName">分组名称</param>
/// <returns></returns>
public static string SaveGroup(string userId, string membersString, string groupName, string groupId)
{
JObject res = new JObject();
if (string.IsNullOrEmpty(userId) || string.IsNullOrEmpty(membersString) || string.IsNullOrEmpty(groupName) ||
string.IsNullOrEmpty(groupId))
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "参数缺少或为空");
return(res.ToString());
}
string sql = string.Format("select GroupName from yl_email_group where Id={0}", groupId);
object resTemp = SqlHelper.Scalar(sql);
if (!Object.Equals(resTemp, null))
{
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("UserId", userId);
dict.Add("GroupName", groupName);
dict.Add("GroupMember", membersString);
sql = SqlHelper.GetUpdateString(dict, "yl_email_group",
string.Format(" where Id={0}", groupId));
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(sql));
if (r.Result == SqlExceRes.ResState.Success)
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", "分组保存成功");
}
else
{
res.Add("ErrCode", 3);
res.Add("ErrMsg", r.ExceMsg);
}
}
else
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", "分组名称不存在");
}
return(res.ToString());
}
/// <summary>
/// 添加附件
/// </summary>
/// <param name="emailId">邮件ID</param>
/// <param name="fileName">文件名称</param>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
public static string InsertAttachment(string emailId, string fileName, string filePath)
{
string fileCode = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ") + ValideCodeHelper.GetRandomCode(128);
JObject res = new JObject();
if (string.IsNullOrEmpty(emailId) || string.IsNullOrEmpty(fileName) || string.IsNullOrEmpty(filePath))
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "参数缺少");
return(res.ToString());
}
List <string> listSql = new List <string>();
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("EmailId", emailId);
dict.Add("FileName", fileName);
dict.Add("FilePath", filePath);
dict.Add("FileCode", fileCode);
dict.Add("CreateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
dict["FilePath"] = dict["FilePath"].Replace("\\", "\\\\");
listSql.Add(SqlHelper.GetInsertString(dict, "yl_email_attachment"));
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add("Attachment", "1");
listSql.Add(SqlHelper.GetUpdateString(dict, "yl_email", "where emailId=" + emailId));
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(listSql.ToArray()));
if (r.Result == SqlExceRes.ResState.Success)
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", "操作成功");
res.Add("FileCode", fileCode);
}
else
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", r.ExceMsg);
}
return(res.ToString());
}
private string Submit()
{
JObject res = new JObject();
string msg = "";
string sql = "SELECT * FROM `v_outlet1` LIMIT 1";
DataSet ds = SqlHelper.Find(sql, ref msg);
if (ds != null)
{
Dictionary <string, string> dict = new Dictionary <string, string>();
foreach (DataColumn c in ds.Tables[0].Columns)
{
if (c.ColumnName == "产品" || c.ColumnName == "医院" || c.ColumnName == "代表" || c.ColumnName == "区域经理" || c.ColumnName == "主管" ||
c.ColumnName == "销售负责人" || c.ColumnName == "部门" || !StringTools.HasChinese(c.ColumnName))
{
continue;
}
string val = Request.Form[c.ColumnName].ToString();
dict.Add(c.ColumnName, val);
}
sql = SqlHelper.GetUpdateString(dict, "new_cost_sharing", string.Format(" where Id={0}", Request.Form["Id"]));
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(sql));
if (r.Result == SqlExceRes.ResState.Success)
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", "操作成功！");
}
else
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", r.ExceMsg);
}
}
else
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", msg);
}
return(res.ToString());
}
public static string UpdateTemplate(ArrayList list, string type)
{
string res = "";
string[] res1 = LeaveStockInfoSrv.UpdateTemplate(list, type).Split(';');
for (int i = 0; i < list.Count; i++)
{
SqlExceRes sqlRes = new SqlExceRes(res1[i]);
if (sqlRes.Result == SqlExceRes.ResState.Error)
{
res += sqlRes.ExceMsg;
}
}
if (string.IsNullOrEmpty(res))
{
return("提交成功！");
}
else
{
return(res);
}
}
public static string DataArchive(DataRow dataRow, int year, int month)
{
string feeDetail = dataRow["feedetail"].ToString();
string relatSector = dataRow["feearea"].ToString();
float money = float.Parse(dataRow["money"].ToString());
// 找到数据库中保存的报销项目
string feeAccount = "";
DataTable dt = getRelativeAccount(feeDetail);
if (dt == null || dt.Rows.Count == 0)
{
return("");
}
else
{
feeAccount = dt.Rows[0]["feeAccount"].ToString();
if ("开发费用金额".Equals(feeAccount))
{
feeAccount = "DevelopmentCost";
}
else if ("销售总监费用".Equals(feeAccount))
{
feeAccount = "SalesDirectorCost";
}
else if ("市场学术费".Equals(feeAccount))
{
feeAccount = "MarketCost";
}
else if ("市场调节基金".Equals(feeAccount))
{
feeAccount = "MarketReadjustmentCost";
}
else if ("区域中心费用".Equals(feeAccount))
{
feeAccount = "RegionalCenterCost";
}
else if ("区域中心费用VIP".Equals(feeAccount))
{
feeAccount = "RegionalCenterVipCost";
}
else if ("商务费用金额".Equals(feeAccount))
{
feeAccount = "BusinessCost";
}
else if ("产品发展基金".Equals(feeAccount))
{
feeAccount = "ProductDevelopmentFundCost";
}
else if ("实验费（TF）金额".Equals(feeAccount))
{
feeAccount = "TfCost";
}
}
// 找到数据库中对应的盈利中心
string sector = "";
dt = getRelativeSector(relatSector);
if (dt == null || dt.Rows.Count == 0)
{
return("");
}
else
{
sector = dt.Rows[0]["sector"].ToString();
}
DataTable oldMoneyDt = ItemSettingManage.getOldMoney(feeAccount, year, month, sector);
float oldmoney = 0;
if (oldMoneyDt != null && oldMoneyDt.Rows.Count != 0)
{
object feeAccountObj = oldMoneyDt.Rows[0][feeAccount];
if (feeAccountObj != null)
{
oldmoney = float.Parse(feeAccountObj.ToString());
}
}
SqlExceRes sqlRes = new SqlExceRes(ItemSettingInfoSrc.saveOrUpdateFinancialData(year, month, feeAccount, money + oldmoney, sector));
return(sqlRes.GetResultString("提交成功！", "提交失败", "提交失败"));
}
public static string saveOrUpdateFinancialData(int year, int month, string itemnm, float num, string sector)
{
SqlExceRes sqlRes = new SqlExceRes(ItemSettingInfoSrc.saveOrUpdateFinancialData(year, month, itemnm, num, sector));
return(sqlRes.GetResultString("提交成功！", "提交失败"));
}
public static string UpdateInfo(Dictionary <string, string> dict, string id)
{
SqlExceRes res = new SqlExceRes(OrganizationInfoSrv.UpdateInfo(dict, id));
return(res.GetResultString("提交成功！", ""));
}
private string importFlow()
{
string year = Request.Form["wage-year"];
string month = Request.Form["wage-month"];
JObject res = new JObject();
DataTable dtExl = ExcelHelperV2_0.Import(Request, 1);
if (dtExl.Rows.Count == 0)
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "导入数据失败：数据为空！");
return(res.ToString());
}
UserInfo user = (UserInfo)Session["user"];
DataTable dt = new DataTable();
dt.Columns.Add("ReportDepartmentName");
dt.Columns.Add("FeeName");
dt.Columns.Add("FeeAmount");
dt.Columns.Add("Budget");
dt.Columns.Add("Year");
dt.Columns.Add("Month");
dt.Columns.Add("CreateTime");
dt.Columns.Add("UserId");
foreach (DataRow iRow in dtExl.Rows)
{
DataRow row = dt.NewRow();
string depart = iRow["部门"].ToString();
if (depart == "部门")
{
continue;
}
// 判断部门是否存在
DataTable tempDt = SqlHelper.Find(string.Format("select 1 from report_department where name = '{0}'", depart)).Tables[0];
if (tempDt.Rows.Count == 0)
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", "'" + depart + "'不存在，请确定该部门是否正确");
return(res.ToString());
}
double flow = double.Parse(iRow["流向"].ToString());
double netSales = double.Parse(iRow["纯销"].ToString());
double grossProfit = double.Parse(iRow["毛利"].ToString());
row["ReportDepartmentName"] = depart;
row["FeeName"] = "流向";
row["Year"] = year;
row["Month"] = month;
row["CreateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
row["UserId"] = user.userId;
row["FeeAmount"] = flow;
row["Budget"] = 0;
dt.Rows.Add(row);
row = dt.NewRow();
row["ReportDepartmentName"] = depart;
row["FeeName"] = "纯销";
row["Year"] = year;
row["Month"] = month;
row["CreateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
row["UserId"] = user.userId;
row["FeeAmount"] = netSales;
row["Budget"] = 0;
dt.Rows.Add(row);
row = dt.NewRow();
row["ReportDepartmentName"] = depart;
row["FeeName"] = "毛利";
row["Year"] = year;
row["Month"] = month;
row["CreateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
row["UserId"] = user.userId;
row["FeeAmount"] = grossProfit;
row["Budget"] = 0;
dt.Rows.Add(row);
}
//string sql = string.Format("delete from yl_reimburse_other where year = '{0}' " +
// " and month='{1}'\r\n;", year, month);
string sql = SqlHelper.GetInsertString(dt, "yl_reimburse_other");
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(sql));
if (r.Result == SqlExceRes.ResState.Success)
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", r.ExceMsg);
}
else
{
res.Add("ErrCode", 4);
res.Add("ErrMsg", r.ExceMsg);
}
return(res.ToString());
}
public static String updateNetSalesAndStockAfterApproval(String docCode)
{
String sql = String.Format("select Hospital, Product, Sales, NetSalesNumber, CorrespondingTime from " +
"v_net_sales where docCode = '{0}'", docCode);
DataSet ds = SqlHelper.Find(sql);
if (ds == null)
{
return(null);
}
string netSalesNum = ds.Tables[0].Rows[0]["NetSalesNumber"].ToString();
string hospital = ds.Tables[0].Rows[0]["Hospital"].ToString();
string product = ds.Tables[0].Rows[0]["Product"].ToString();
string sales = ds.Tables[0].Rows[0]["Sales"].ToString();
string correspondingTime = ds.Tables[0].Rows[0]["CorrespondingTime"].ToString();
string[] time = correspondingTime.Split(new char[1] {
'-'
});
int year = Int32.Parse(time[0]);
String monthStr = time[1];
if (monthStr.Length == 1)
{
monthStr = monthStr.Substring(1, 1);
}
int month = Int32.Parse(monthStr) - 1;
string updateNetSaleSql = string.Format("update flow_statistics set NetSales = {0} where Hospital = '{1}' and Product = '{2}' and sales = '{3}'" +
" and Year = {4} and Month = {5}", netSalesNum, hospital, product, sales, year, month);
SqlExceRes res1 = new SqlExceRes(SqlHelper.Exce(updateNetSaleSql));
string netSalesRes = res1.GetResultString("更新纯销成功", "更新纯销失败", "更新纯销失败");
string queryStockSql = string.Format("select * from flow_statistics where Hospital = '{0}' and Product = '{1}' and sales = '{2}'" +
" and Year = {3} and Month = {4}", hospital, product, sales, year, month);
ds = SqlHelper.Find(queryStockSql);
if (ds == null)
{
return(null);
}
int stockLastMonth = Int32.Parse(ds.Tables[0].Rows[0]["StockLastMonth"].ToString());
int flowSales = Int32.Parse(ds.Tables[0].Rows[0]["FlowSales"].ToString());
int NetSales = Int32.Parse(ds.Tables[0].Rows[0]["NetSales"].ToString());
int stockThisMonth = stockLastMonth + flowSales - NetSales;
string updateStockSql = string.Format("update flow_statistics set StockThisMonth = {0} where Hospital = '{1}' and Product = '{2}' and sales = '{3}'" +
" and Year = {4} and Month = {5}", stockThisMonth, hospital, product, sales, year, month);
//List<string> list = new List<string>();
//list.Add(updateNetSaleSql);
//list.Add(updateStockSql);
SqlExceRes res2 = new SqlExceRes(SqlHelper.Exce(updateStockSql));
string stockRes = res1.GetResultString("更新库存成功", "更新库存失败", "更新库存失败");
if ("更新纯销成功".Equals(netSalesRes) && "更新库存成功".Equals(stockRes))
{
return("更新成功");
}
else
{
return("更新失败");
}
}
public static Dictionary <String, String> SaveNetSales(string hospital, string product, string sales, string netSalesNumber, string docCode, string time)
{
List <string> list = new List <string>();
string sql = string.Format("select Id from organization where name = '{0}'", hospital);
list.Add(sql);
sql = string.Format("select Id from products where name = '{0}'", product);
list.Add(sql);
sql = string.Format("select userId from users where userName = '******'", sales);
list.Add(sql);
string[] Ids = SqlHelper.Scalar(list.ToArray());
Dictionary <string, string> dict = new Dictionary <string, string>();
if (string.IsNullOrEmpty(Ids[0]))
{
dict.Add("returnMsg", "未找到医院信息");
return(dict);
}
else if (string.IsNullOrEmpty(Ids[1]))
{
dict.Add("returnMsg", "未找到产品信息");
return(dict);
}
else if (string.IsNullOrEmpty(Ids[2]))
{
dict.Add("returnMsg", "未找到业务员信息");
return(dict);
}
list.Clear();
sql = string.Format("select ManagerId from cost_sharing where HospitalId = {0} "
+ "and ProductId = {1} and SalesId={2}", Ids[0], Ids[1], Ids[2]);
String ManagerId = "";
object obj = SqlHelper.Scalar(sql);
if (obj != null)
{
ManagerId = obj.ToString();
}
else
{
dict.Add("returnMsg", "未找到网点销售经理信息");
return(dict);
}
SqlExceRes res = null;
// 判断是否之前有相同的单据，有则更新，没有则新增
if (docCode == null || "".Equals(docCode))
{
docCode = GenerateDocCode.getDocCode();
dict.Add("HospitalId", Ids[0]);
dict.Add("ProductId", Ids[1]);
dict.Add("SalesId", Ids[2]);
dict.Add("NetSalesNumber", netSalesNumber.ToString());
//dict.Add("ApproverId", ManagerId);
//DateTime date = DateTime.Now.AddMonths(-1);
dict.Add("CorrespondingTime", time);
dict.Add("CreateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
dict.Add("State", "未提交");
dict.Add("Editable", "1");
dict.Add("DocCode", docCode);
String insertSql = SqlHelper.GetInsertIgnoreString(dict, "net_sales");
res = new SqlExceRes(SqlHelper.Exce(insertSql));
}
else
{
// 根据docCode来更新纯销数量
String updateSql = String.Format("update net_sales set NetSalesNumber = {0}, CorrespondingTime = '{1}' where DocCode = {2}", netSalesNumber.ToString(), time, docCode);
res = new SqlExceRes(SqlHelper.Exce(updateSql));
}
dict.Clear();
dict.Add("docCode", docCode);
dict.Add("returnMsg", res.GetResultString("success", "单据已保存，请勿重复保存", "ErrorMsg:"));
return(dict);
}
/// <summary>
/// 发送邮件
/// </summary>
/// <param name="emailId">邮件ID</param>
/// <returns></returns>
public static string SendEmail(string emailId)
{
JObject res = new JObject();
if (string.IsNullOrEmpty(emailId))
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "参数缺少");
return(res.ToString());
}
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("Status", "已发送");
dict.Add("SendTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(SqlHelper.GetUpdateString(dict, "yl_email", string.Format(" where Id={0}", emailId))));
if (r.Result == SqlExceRes.ResState.Success)
{
string msg = "";
List <string> sqlList = new List <string>();
sqlList.Add(string.Format("select ye.*,u.userName as SendName from yl_email as ye LEFT JOIN users as u on ye.Sender=u.wechatUserId where Id={0}", emailId));
sqlList.Add(string.Format("select UserId from yl_email_recipient where EmailId={0}", emailId));
DataSet ds = SqlHelper.Find(sqlList.ToArray(), ref msg);
if (ds == null)
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", msg);
}
else if (ds.Tables[0].Rows.Count == 0)
{
res.Add("ErrCode", 4);
res.Add("ErrMsg", "不存在该邮件！");
}
else if (ds.Tables[1].Rows.Count == 0)
{
res.Add("ErrCode", 4);
res.Add("ErrMsg", "没有设置收件人！");
}
else
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", "发送成功");
res.Add("SendName", ds.Tables[0].Rows[0]["SendName"].ToString());
res.Add("Subject", ds.Tables[0].Rows[0]["Subject"].ToString());
string recipientId = "";
foreach (DataRow row in ds.Tables[1].Rows)
{
recipientId += row["UserId"].ToString() + "|";
}
recipientId = recipientId.Substring(0, recipientId.Length - 1);
res.Add("recipientId", recipientId);
}
}
else
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", r.ExceMsg);
}
return(res.ToString());
}
/// <summary>
/// 删除邮件或者将邮件丢入垃圾箱
/// </summary>
/// <param name="emailIds">要被操作的邮件ID表</param>
/// <param name="isDeleteEntirely">true表示删除，false表示丢入垃圾箱</param>
/// <param name="ReceiveOrSender">将要操作的邮件是收件箱的还是发件箱（此处发件箱包括草稿箱,为垃圾箱时isDeleteEntirely一定为true）的</param>
/// <returns></returns>
public static string DeleteEmail(List <string> emailIds, bool isDeleteEntirely, string ReceiveOrSender)
{
JObject res = new JObject();
if (emailIds.Count == 0)
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "参数缺少");
return(res.ToString());
}
Dictionary <string, string> dict = new Dictionary <string, string>();
if (isDeleteEntirely || "delete".Equals(ReceiveOrSender))
{
dict.Add("Status", "彻底删除");
}
else
{
dict.Add("Status", "已删除");
}
List <string> sqlList = new List <string>();
if (ReceiveOrSender == "receive" || ReceiveOrSender == "delete")
{
string condition = "where emailId in('";
foreach (string emaild in emailIds)
{
condition += emaild + "','";
}
condition = condition.Substring(0, condition.Length - 2);
condition += ")";
//sqlList.Add(string.Format("delete from yl_email where Id={0}", emailId));
//sqlList.Add(string.Format("delete from yl_email_recipient where EmailId={0}", emailId));
//sqlList.Add(string.Format("delete from yl_email_attachment where EmailId={0}", emailId));
sqlList.Add(SqlHelper.GetUpdateString(dict, "yl_email_recipient", condition));
}
else
{
dict["Status"] = "彻底删除";
string condition = "where Id in('";
foreach (string emaild in emailIds)
{
condition += emaild + "','";
}
condition = condition.Substring(0, condition.Length - 2);
condition += ")";
//Dictionary<string, string> dict = new Dictionary<string, string>();
//dict.Add("Status", "已删除");
sqlList.Add(SqlHelper.GetUpdateString(dict, "yl_email", condition));
}
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(sqlList.ToArray()));
if (r.Result == SqlExceRes.ResState.Success)
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", "删除成功");
}
else
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", r.ExceMsg);
}
return(res.ToString());
}
/// <summary>
/// 保存草稿
/// </summary>
/// <param name="emailId">邮件ID</param>
/// <param name="subject">邮件主题</param>
/// <param name="text">邮件正文</param>
/// <param name="recipients">收件人</param>
/// <returns></returns>
public static string SaveDraft(string emailId, string subject, string text
, string[] recipients)
{
JObject res = new JObject();
if (string.IsNullOrEmpty(emailId))
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "参数缺少");
return(res.ToString());
}
List <string> sqlList = new List <string>();
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("Subject", subject);
dict.Add("Text", text);
dict.Add("LMT", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqlList.Add(SqlHelper.GetUpdateString(dict, "yl_email", string.Format(" where Id={0}", emailId)));
if (recipients.Length > 0)
{
sqlList.Add(string.Format("delete from yl_email_recipient where EmailId={0}", emailId));
ArrayList list = new ArrayList();
foreach (string recpient in recipients)
{
if (string.IsNullOrEmpty(recpient))
{
continue;
}
if (recpient.Length <= 5)
{
DataSet ds = UserInfoSrv.getInfos("1", recpient);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
foreach (DataTable dt in ds.Tables)
{
foreach (DataRow row in dt.Rows)
{
Boolean flag = true;
foreach (Dictionary <string, string> dc in list)
{
if (dc["UserId"].ToString() == row["userId"].ToString())
{
flag = false;
break;
}
}
if (flag)
{
dict = new Dictionary <string, string>();
dict.Add("EmailId", emailId);
dict.Add("UserId", row["userId"].ToString());
list.Add(dict);
sqlList.Add(string.Format("insert into `yl_email_recipient` (userid,emailId) values" +
" ((select t.wechatUserId from users t where userId = '{0}'),'{1}');", row["userId"].ToString(), emailId));
}
}
}
}
else if (ds == null)
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "连接数据库出现问题");
return(res.ToString());
}
}
else if (recpient.Length <= 8)
{
int id = (Convert.ToInt32(recpient) - 1000000) / 1000;
DataSet ds = SqlHelper.Find("select GroupMember from yl_email_group where Id=" + id);
JArray GroupJarray = JArray.Parse(ds.Tables[0].Rows[0][0].ToString());
foreach (JObject jobject in GroupJarray)
{
Boolean flag = true;
foreach (Dictionary <string, string> dc in list)
{
if (dc["UserId"].ToString() == jobject["UserId"].ToString())
{
flag = false;
break;
}
}
if (flag)
{
dict = new Dictionary <string, string>();
dict.Add("EmailId", emailId);
dict.Add("UserId", jobject["UserId"].ToString());
list.Add(dict);
sqlList.Add(string.Format("insert into `yl_email_recipient` (userid,emailId) values" +
" ((select t.wechatUserId from users t where userId = '{0}'),'{1}');", jobject["UserId"].ToString(), emailId));
}
}
}
else
{
Boolean flag = true;
foreach (Dictionary <string, string> dc in list)
{
if (dc["UserId"].ToString() == recpient)
{
flag = false;
break;
}
}
if (flag)
{
dict = new Dictionary <string, string>();
dict.Add("EmailId", emailId);
dict.Add("UserId", recpient);
list.Add(dict);
sqlList.Add(string.Format("insert into `yl_email_recipient` (userid,emailId) values" +
" ((select t.wechatUserId from users t where userId = '{0}'),'{1}');", recpient, emailId));
}
}
}
}
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(sqlList.ToArray()));
if (r.Result == SqlExceRes.ResState.Success)
{
res.Add("ErrCode", "0");
res.Add("ErrMsg", "操作成功");
}
else
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", r.ExceMsg);
}
return(res.ToString());
}
