public bool update_form_status(int id, int status, int function_code)
{
H5Objects o = GData.GetObject(function_code);
StringBuilder strsql = new StringBuilder();
List <SqlParameter> list_para = new List <SqlParameter>();
strsql.Append("update " + o.HO_TABLE_NAME + " set " + o.HO_STAUTS_FIELD + "=@" + o.HO_STAUTS_FIELD + " where " + o.HO_ID_FIELD + "=@" + o.HO_ID_FIELD);
list_para.Add(new SqlParameter("@" + o.HO_STAUTS_FIELD, status));
list_para.Add(new SqlParameter("@" + o.HO_ID_FIELD, id));
SqlConnection cn = comm_fun.get_cn();
try
{
return(comm_fun.ExecuteNonQuery(strsql.ToString(), cn, list_para));
}
catch (Exception ex)
{
comm_fun.WriteLog(ex.ToString());
return(false);
}
finally
{
comm_fun.CloseConnection(cn);
}
}
/// <summary>
/// 1审批表单主表处理
/// </summary>
/// <param name="item_function">业务类型</param>
private void work_follow_master(int item_function)
{
//【待审核通知】查询出所有状态为0或者3（待审核,待终审）的数据
H5Objects o = GData.GetObject(item_function);
StringBuilder strsql = new StringBuilder();
strsql.Append("select ");
strsql.Append(o.HO_ID_FIELD + " as id");
strsql.Append("," + o.HO_USER_FIELD + " as user_id");
strsql.Append("," + o.HO_MASTER_FIELD + " as master");
strsql.Append(" from " + o.HO_TABLE_NAME);
strsql.Append(" where " + o.HO_STAUTS_FIELD + " in (0,3)");
DataTable dt = GetDataTable(strsql.ToString(), null);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
int id = Convert.ToInt32(dr["id"]); //数据ID
int user_id = Convert.ToInt32(dr["user_id"]); //申请用户
string master = dr["master"].ToString(); //核心字段的数据
work_follow_detail_waitting(item_function, id, user_id, master);
}
}
//【已审核通知】 查询出所有状态为1或者2（审核通过,驳回）的数据
StringBuilder strsql1 = new StringBuilder();
strsql1.Append("select ");
strsql1.Append(o.HO_ID_FIELD + " as id");
strsql1.Append("," + o.HO_USER_FIELD + " as user_id");
strsql1.Append("," + o.HO_MASTER_FIELD + " as master");
strsql1.Append("," + o.HO_STAUTS_FIELD + " as status");
strsql1.Append(" from " + o.HO_TABLE_NAME);
strsql1.Append(" where " + o.HO_STAUTS_FIELD + " in (1,2)");
DataTable dt1 = GetDataTable(strsql1.ToString(), null);
if (dt1.Rows.Count > 0)
{
foreach (DataRow dr1 in dt1.Rows)
{
int id = Convert.ToInt32(dr1["id"]); //数据ID
int user_id = Convert.ToInt32(dr1["user_id"]); //申请用户
string master = dr1["master"].ToString();
int status = Convert.ToInt32(dr1["status"]);
work_follow_detail_done(item_function, id, user_id, master, status);
}
}
}
public string get_work_form_waitting()
{
string function_code = Request["function_code"];
if (string.IsNullOrEmpty(function_code))
{
return(new AjaxErrorResult("function_code不能为空").ToString());
}
FunctionCode = Convert.ToInt32(function_code);
SqlConnection cn_count = comm_fun.get_cn();
string count = "0";
try
{
StringBuilder str_count = new StringBuilder();
StringBuilder str_where = new StringBuilder();
H5Objects o = GData.GetObject(Convert.ToInt32(function_code));
List <SqlParameter> list_para = new List <SqlParameter>();
str_where.Append(" and " + o.HO_STAUTS_FIELD + " in (0,3) ");//待审核或者待终审
str_where.Append(" and " + o.HO_USER_FIELD + "=@" + o.HO_USER_FIELD);
list_para.Add(new SqlParameter("@" + o.HO_USER_FIELD, Cookies.UserCode));
str_count.Append("select " + "count(1)" + " from " + o.HO_TABLE_NAME + " where 1=1 " + str_where);
count = comm_fun.ExecuteScalar(str_count.ToString(), cn_count, list_para);
}
catch (Exception ex)
{
comm_fun.WriteLog(ex.ToString());
count = "0";
}
finally
{
comm_fun.CloseConnection(cn_count);
}
if (Convert.ToInt32(count) > 99)
{
count = "99";
}
return(new AjaxSuccessResult(count).ToString());
}
/// <summary>
/// 1.1有审批结果的通知
/// </summary>
public void work_follow_detail_done(int item_function, int id, int user, string master, int status)
{
//获取最后一条审批记录，WF_TIMES<100，通知用户然后WF_TIMES加上100
H5Objects o = GData.GetObject(item_function);
IQueryable <SP_WORK_FOLLOW> list = null;
list = db.SP_WORK_FOLLOW.Where(item => item.WF_BUSINESS_TYPE == o.HO_BUSINESS_TYPE);
list = list.Where(item => item.WF_BUSINESS_KEY == id);
list = list.Where(item => item.WF_STATUS == 1);
list = list.Where(item => item.WF_TIMES < 100);
if (list.ToList().Count > 0)
{
work_follow_end_notice_user(user, master, o.HO_NAME, status);
foreach (SP_WORK_FOLLOW one_item in list.ToList())
{
if (one_item != null)
{
one_item.WF_TIMES += 100;
}
}
db.SaveChanges();
}
}
public string get_approve_work_form_list()
{
string function_code_list = Request["function_code_list"];
if (string.IsNullOrEmpty(function_code_list))
{
return(new AjaxErrorResult("function_code_list不能为空").ToString());
}
string type = Request["type"];
string page = Request["page"];
StringBuilder str_table = new StringBuilder();
StringBuilder str_table_column = new StringBuilder();
List <string> list = function_code_list.Split("_".ToCharArray()).ToList();
for (int i = 0; i < list.Count; i++)
{
string function_code = list[i];
H5Objects o = GData.GetObject(Convert.ToInt32(function_code));
List <H5Columns> list_column = GData.GetColumn(Convert.ToInt32(function_code));
if (i > 0)
{
str_table_column.Append(" union all ");
}
str_table_column.Append(" select ");
str_table_column.Append(o.HO_ID_FIELD + " as id");
str_table_column.Append("," + o.HO_USER_FIELD + " as user_id");
str_table_column.Append(",dbo.fn_convert_datetime(" + o.HO_DATE_FIELD + ") as date");
str_table_column.Append("," + o.HO_STAUTS_FIELD + " as status");
str_table_column.Append("," + o.HO_MASTER_FIELD + " as master");
str_table_column.Append("," + o.HO_DATE_FIELD + " as create_date ");
str_table_column.Append("," + o.HO_BUSINESS_TYPE + " as business");
str_table_column.Append("," + "'" + o.HO_NAME + "'" + " as business_text");
str_table_column.Append(" from " + o.HO_TABLE_NAME + "");
}
str_table.Append("(" + str_table_column.ToString() + ") as sp_view");
//H5Objects o = GData.GetObject(Convert.ToInt32(function_code));
//List<H5Columns> list_column = GData.GetColumn(Convert.ToInt32(function_code));
List <SqlParameter> list_para = new List <SqlParameter>();
List <SqlParameter> list_para_count = new List <SqlParameter>();
StringBuilder strsql = new StringBuilder();
StringBuilder str_column = new StringBuilder();
StringBuilder str_where = new StringBuilder();
StringBuilder str_order = new StringBuilder();
StringBuilder str_count = new StringBuilder();
//我的发起
if (type == "me")
{
str_where.Append(" and user_id=@user_id");
list_para.Add(new SqlParameter("@user_id", Cookies.UserCode));
list_para_count.Add(new SqlParameter("@user_id", Cookies.UserCode));
}
//我的审批
if (type == "other")
{
str_table.Append(" inner join SP_WORK_FOLLOW on WF_BUSINESS_TYPE=business and WF_BUSINESS_KEY=id ");
str_where.Append(" and WF_TO_USER=@WF_TO_USER");
list_para.Add(new SqlParameter("@WF_TO_USER", Cookies.UserCode));
list_para_count.Add(new SqlParameter("@WF_TO_USER", Cookies.UserCode));
}
str_order.Append(" order by create_date desc");
SqlConnection cn_count = comm_fun.get_cn();
string count = "0";
try
{
str_count.Append("select " + "count(1)" + " from " + str_table.ToString() + " where 1=1 " + str_where);
count = comm_fun.ExecuteScalar(str_count.ToString(), cn_count, list_para_count);
}
catch (Exception ex)
{
comm_fun.WriteLog(ex.ToString());
count = "0";
}
finally
{
comm_fun.CloseConnection(cn_count);
}
int SAO_PAGE_SIZE = 20;
int SAO_CURRENT_PAGE = Convert.ToInt32(page);
str_column.Append("id");
str_column.Append(",user_id");
str_column.Append(",date");
str_column.Append(",status");
str_column.Append(",master");
str_column.Append(",business");
str_column.Append(",business_text");
if (type == "other")
{
str_column.Append(",WF_STATUS");
}
strsql.Append("select top " + SAO_PAGE_SIZE + " " + str_column.ToString() + " from " + str_table.ToString() + " where 1=1");
strsql.Append(" and id not in (select top " + (SAO_CURRENT_PAGE * SAO_PAGE_SIZE).ToString() + " id " + " from " + str_table.ToString() + " where 1=1 " + str_where.ToString() + " " + str_order.ToString() + ")" + str_where.ToString() + " " + str_order.ToString());
DataTable dt = new DataTable();
SqlConnection cn = comm_fun.get_cn();
try
{
dt = comm_fun.GetDatatable(strsql.ToString(), cn, list_para);
if (dt.Rows.Count > 0)
{
dt.Columns.Add("user_name");
dt.Columns.Add("user_image");
dt.Columns.Add("status_name");
dt.Columns.Add("status_class");
foreach (DataRow dr in dt.Rows)
{
dr["user_name"] = get_user_name(dr["user_id"].ToString());
dr["user_image"] = GData.get_user_image(dr["user_id"].ToString());
dr["status_name"] = GData.get_status_name(dr["status"].ToString());
dr["status_class"] = GData.get_status_class(dr["status"].ToString());
}
}
}
catch (Exception ex)
{
comm_fun.WriteLog(ex.ToString());
}
if (dt.Rows.Count > 0)
{
return(new AjaxDataResult(dt).ToString());
}
else
{
return(new AjaxErrorResult("").ToString());
}
}
public string get_work_form_list()
{
string function_code = Request["function_code"];
if (string.IsNullOrEmpty(function_code))
{
return(new AjaxErrorResult("function_code不能为空").ToString());
}
FunctionCode = Convert.ToInt32(function_code);
string page = Request["page"];
if (string.IsNullOrEmpty(page))
{
return(new AjaxErrorResult("page不能为空").ToString());
}
H5Objects o = GData.GetObject(Convert.ToInt32(function_code));
List <H5Columns> list_column = GData.GetColumn(Convert.ToInt32(function_code));
List <SqlParameter> list_para = new List <SqlParameter>();
StringBuilder strsql = new StringBuilder();
StringBuilder str_column = new StringBuilder();
StringBuilder str_where = new StringBuilder();
StringBuilder str_order = new StringBuilder();
StringBuilder str_count = new StringBuilder();
str_where.Append(" and " + o.HO_USER_FIELD + "=@" + o.HO_USER_FIELD);
str_order.Append(" order by " + o.HO_DATE_FIELD + " desc");
list_para.Add(new SqlParameter("@" + o.HO_USER_FIELD, Cookies.UserCode));
SqlConnection cn_count = comm_fun.get_cn();
string count = "0";
try
{
str_count.Append("select " + "count(1)" + " from " + o.HO_TABLE_NAME + " where 1=1 " + str_where);
count = comm_fun.ExecuteScalar(str_count.ToString(), cn_count, list_para);
}
catch (Exception ex)
{
comm_fun.WriteLog(ex.ToString());
count = "0";
}
finally
{
comm_fun.CloseConnection(cn_count);
}
int SAO_PAGE_SIZE = 20;
int SAO_CURRENT_PAGE = Convert.ToInt32(page);
str_column.Append(o.HO_ID_FIELD + " as id");
str_column.Append("," + o.HO_USER_FIELD + " as user_id");
str_column.Append(",dbo.fn_convert_datetime(" + o.HO_DATE_FIELD + ") as date");
str_column.Append("," + o.HO_STAUTS_FIELD + " as status");
str_column.Append("," + o.HO_MASTER_FIELD + " as master");
strsql.Append("select top " + SAO_PAGE_SIZE + " " + str_column.ToString() + " from " + o.HO_TABLE_NAME + " where 1=1");
strsql.Append(" and " + o.HO_ID_FIELD + " not in (select top " + (SAO_CURRENT_PAGE * SAO_PAGE_SIZE).ToString() + " " + o.HO_ID_FIELD + " " + " from " + o.HO_TABLE_NAME + " where 1=1 " + str_where.ToString() + " " + str_order.ToString() + ")" + str_where.ToString() + " " + str_order.ToString());
DataTable dt = new DataTable();
SqlConnection cn = comm_fun.get_cn();
try
{
dt = comm_fun.GetDatatable(strsql.ToString(), cn, list_para);
if (dt.Rows.Count > 0)
{
dt.Columns.Add("user_name");
dt.Columns.Add("user_image");
dt.Columns.Add("status_name");
dt.Columns.Add("status_class");
foreach (DataRow dr in dt.Rows)
{
dr["user_name"] = get_user_name(dr["user_id"].ToString());
dr["user_image"] = GData.get_user_image(dr["user_id"].ToString());
dr["status_name"] = GData.get_status_name(dr["status"].ToString());
dr["status_class"] = GData.get_status_class(dr["status"].ToString());
}
}
}
catch (Exception ex)
{
comm_fun.WriteLog(ex.ToString());
}
if (dt.Rows.Count > 0)
{
return(new AjaxDataResult(dt).ToString());
}
else
{
return(new AjaxErrorResult("").ToString());
}
}
public string add_work_form()
{
string function_code = Request["function_code"];
if (string.IsNullOrEmpty(function_code))
{
return(new AjaxErrorResult("function_code不能为空").ToString());
}
H5Objects o = GData.GetObject(Convert.ToInt32(function_code));
List <H5Columns> list_column = GData.GetColumn(Convert.ToInt32(function_code));
StringBuilder strsql = new StringBuilder();
StringBuilder strsql_field = new StringBuilder();
StringBuilder strsql_para = new StringBuilder();
List <SqlParameter> list_para = new List <SqlParameter>();
foreach (H5Columns item_column in list_column)
{
strsql_field.Append("," + item_column.HC_NAME);
strsql_para.Append(",@" + item_column.HC_NAME);
list_para.Add(new SqlParameter("@" + item_column.HC_NAME, GetControlValue(item_column)));
}
strsql_field.Append("," + o.HO_USER_FIELD);
strsql_para.Append(",@" + o.HO_USER_FIELD);
list_para.Add(new SqlParameter("@" + o.HO_USER_FIELD, Cookies.UserCode));
strsql_field.Append("," + o.HO_DATE_FIELD);
strsql_para.Append(",@" + o.HO_DATE_FIELD);
list_para.Add(new SqlParameter("@" + o.HO_DATE_FIELD, DateTime.Now));
strsql_field.Append("," + o.HO_STAUTS_FIELD);
strsql_para.Append(",@" + o.HO_STAUTS_FIELD);
list_para.Add(new SqlParameter("@" + o.HO_STAUTS_FIELD, "0"));
strsql.Append("insert into " + o.HO_TABLE_NAME);
strsql.Append("(" + strsql_field.ToString().Substring(1) + ")");
strsql.Append("values");
strsql.Append("(" + strsql_para.ToString().Substring(1) + ")");
strsql.Append(" " + "select @ID=@@identity");
SqlParameter para_id = new SqlParameter("@ID", SqlDbType.Int, 8);
para_id.Direction = ParameterDirection.Output;
list_para.Add(para_id);
SqlConnection cn = comm_fun.get_cn();
try
{
bool b_reslut = comm_fun.ExecuteNonQuery(strsql.ToString(), cn, list_para);
if (b_reslut)
{
int id = Convert.ToInt32(para_id.Value);
add_work_follow(Convert.ToInt32(Cookies.UserCode), Convert.ToInt32(Request["to_user"]), Convert.ToInt32(function_code), id, string.Empty);
return(new AjaxSuccessResult("保存成功" + id.ToString()).ToString());
}
else
{
return(new AjaxErrorResult("执行失败").ToString());
}
}
catch
{
return(new AjaxExceptionResult("执行异常").ToString());
}
finally
{
comm_fun.CloseConnection(cn);
}
}
/// <summary>
/// 1.2无审批结果的通知
/// </summary>
public void work_follow_detail_waitting(int item_function, int id, int user, string master)
{
//0为没有任何提醒，1=10分钟后提示一次，2=4小时提示一次，n=每天9点提醒一次，n>5停止提醒，n=6通知申请客户
H5Objects o = GData.GetObject(item_function);
IQueryable <SP_WORK_FOLLOW> list = null;
list = db.SP_WORK_FOLLOW.Where(item => item.WF_BUSINESS_TYPE == o.HO_BUSINESS_TYPE);
list = list.Where(item => item.WF_BUSINESS_KEY == id);
list = list.Where(item => item.WF_STATUS == 0);
//10分钟提醒
DateTime dt1 = DateTime.Now.AddMinutes(-10);
IQueryable <SP_WORK_FOLLOW> list1 = list.Where(item => item.WF_CREATE_DATE < dt1 && item.WF_TIMES == 0);
foreach (SP_WORK_FOLLOW item in list1.ToList())
{
work_follow_star_notice_user(user, master, o.HO_NAME, item.WF_TO_USER);
item.WF_TIMES = 1;
}
db.SaveChanges();
//4小时提醒
DateTime dt2 = DateTime.Now.AddHours(-4);
IQueryable <SP_WORK_FOLLOW> list2 = list.Where(item => item.WF_CREATE_DATE < dt2 && item.WF_TIMES == 1);
foreach (SP_WORK_FOLLOW item in list2.ToList())
{
work_follow_star_notice_user(user, master, o.HO_NAME, item.WF_TO_USER);
item.WF_TIMES = 2;
}
db.SaveChanges();
//n = 每天9点提醒一次，n > 5停止提醒
for (int i = 0; i < 3; i++)
{
if (DateTime.Now.Hour >= 9 && DateTime.Now.Hour <= 18)
{
DateTime dt_begin = Convert.ToDateTime(DateTime.Now.AddDays(-(i + 1)).ToString("yyyy-MM-dd"));
DateTime dt_end = Convert.ToDateTime(DateTime.Now.AddDays(-i).ToString("yyyy-MM-dd"));
IQueryable <SP_WORK_FOLLOW> listn = list.Where(item => item.WF_CREATE_DATE > dt_begin && item.WF_CREATE_DATE < dt_end && item.WF_TIMES == (2 + i));
foreach (SP_WORK_FOLLOW item in listn.ToList())
{
work_follow_star_notice_user(user, master, o.HO_NAME, item.WF_TO_USER);
item.WF_TIMES = item.WF_TIMES + 1;
}
db.SaveChanges();
}
}
//n = 6通知申请客户
DateTime dt6 = Convert.ToDateTime(DateTime.Now.AddDays(-5).ToString("yyyy-MM-dd"));
IQueryable <SP_WORK_FOLLOW> list6 = list.Where(item => item.WF_TIMES < 6);
list6 = list6.Where(item => item.WF_CREATE_DATE < dt6);
foreach (SP_WORK_FOLLOW item in list6.ToList())
{
work_follow_stop_notice_user(user, master, o.HO_NAME, item.WF_TO_USER);
item.WF_TIMES = 6;
}
db.SaveChanges();
}