.net编程

开发平台：

C#

tp_sys.sql：源码内容

using System;
using System.Data;
using System.Data.SqlClient;
using com.etong.SqlDataConnect;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Collections;
using System.Xml;
namespace com.etong.DAL.FC
{
/// <summary>
/// 系统维护(职工和组织机构)
/// </summary>
public class EtongFlyChou
{
private MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
public EtongFlyChou()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
///读取所有组织机构的数据
/// </summary>
/// <param name="sExcelFile">excel文件路径</param>
/// <param name="strXslSheet">sheet页名称</param>
/// <param name="SQLTableName">SQL中表名</param>
/// <param name="Arr">Excel中字段和SQL中表字段的对应,由ListItem组成的ArrayList</param>
public DataView ReadOrgData()
{
DataSet ds = new DataSet();
string strSQL = "select * from Tb_Sys_Organization order by OrgLevel,ParentOrgID,OrgID";
MSDA.Open();
MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
/// <summary>
/// 读取职工树数据
/// </summary>
/// <returns>dataview</returns>
public DataView ReadEmpData()
{
DataSet ds = new DataSet();
string strSQL = "select EmpID,EmpName,PrimaryOrgID,OrgLevel from Tb_Sys_Employee,Tb_Sys_Organization where OrgID=PrimaryOrgID order by PrimaryOrgID";
MSDA.Open();
MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
/// <summary>
/// 读取职工与组织机构的关系
/// </summary>
/// <returns></returns>
public DataView ReadEmpOrg()
{
DataSet ds = new DataSet();
string StrSQL = "select EmpName,OrgLevel,Tb_Sys_Organization.OrgID from Tb_Sys_Employee,Tb_Sys_EmpOrg,Tb_Sys_Organization where Tb_Sys_EmpOrg.EmpID=Tb_Sys_Employee.EmpID and Tb_Sys_Organization.OrgID=Tb_Sys_EmpOrg.OrgID order by PrimaryOrgID";
MSDA.Open();
MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
/// <summary>
/// 获取职工编号
/// </summary>
/// <returns>int 职工编号</returns>
public int GetMaxEmpID()
{
string MaxEmp = "0";
int iMaxEmp;
string StrSQL = "select max(EmpID) as MaxEmp from Tb_Sys_Employee";
MSDA.Open();
SqlDataReader dr = MSDA.ExecuteReader(CommandType.Text, StrSQL, null);
if (dr.Read())
{
if (dr["MaxEmp"] != null && dr["MaxEmp"].ToString().Trim() != "")
{
MaxEmp = dr["MaxEmp"].ToString().Trim();
}
else
{
MaxEmp = "0";
}
}
iMaxEmp = Convert.ToInt32(MaxEmp) + 1;
dr.Close();
MSDA.Close();
return iMaxEmp;
}
/// <summary>
/// 获取组织机构编号
/// </summary>
/// <returns> int 组织机构编号</returns>
public int GetMaxOrgzationID()
{
string SMaxID = "0";
int iMaxID;
string StrSQL = "select max(OrgID) as SMaxID from Tb_Sys_Organization";
MSDA.Open();
SqlDataReader dr = MSDA.ExecuteReader(CommandType.Text, StrSQL, null);
if (dr.Read())
{
if (dr["SMaxID"] != null && dr["SMaxID"].ToString().Trim() != "")
{
SMaxID = dr["SMaxID"].ToString().Trim();
}
else
{
SMaxID = "0";
}
}
iMaxID = Convert.ToInt32(SMaxID) + 1;
dr.Close();
MSDA.Close();
return iMaxID;
}
/// <summary>
/// 新建职工
/// </summary>
/// <param name="EmpName">职工姓名</param>
/// <param name="OrgID">所属组织机构代码</param>
/// <param name="Email">Email（电子邮件）</param>
/// <param name="PhNum">电话号码</param>
/// <param name="TmpFlag">是否是临时员工（0、雇佣；1、临时职工）</param>
/// <returns>true：保存成功！false：保存失败！</returns>
public bool AddEmpData(string EmpName, string OrgID, string Email, string PhNum, int TmpFlag,int iType,ArrayList al,int iPEmpID)
{
bool bl=false;
if (TmpFlag != 0 && TmpFlag != 1) return bl;
int iOrgID, iEmpID;
iOrgID = Convert.ToInt32(OrgID);
iEmpID = GetMaxEmpID();
DateTime EditDate = new DateTime();
EditDate = System.DateTime.Now;
string StrSQL = "insert into Tb_Sys_Employee values(" + iEmpID + ",'" + EmpName + "'," +
iOrgID + "," + iType + ",'" + Email + "','" + PhNum + "','" + EditDate + "'," + TmpFlag + "," + iPEmpID + ")";
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, StrSQL, null);
for (int i = 0; i < al.Count; i++)
{
StrSQL = "insert into Tb_Sys_EmpOpRole values(" + iEmpID + "," + Convert.ToInt32(al[i].ToString()) + ")";
MSDA.ExecuteNonQuery(CommandType.Text, StrSQL, null);
}
tran.Commit();
bl= true;
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
finally
{
MSDA.Close();
}
return bl;
}
/// <summary>
/// 等到所属组织机构代码
/// </summary>
/// <param name="sEmpID">职工代码</param>
/// <returns>string 组织机构代码</returns>
public string GetEmpParentOrgID(string sEmpID)
{
string StrParentOrg = "";
int iEmpID = 0;
iEmpID = Convert.ToInt32(sEmpID);
string StrSQL;
DataSet ds = new DataSet();
MSDA.Open();
if (iEmpID > 0)
{
StrSQL = "select PrimaryOrgID from Tb_Sys_Employee where EmpID=" + iEmpID;
ds.Clear();
MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
StrParentOrg = ds.Tables[0].Rows[0][0].ToString();
}
MSDA.Close();
return StrParentOrg;
}
/// <summary>
/// 得到组织机构的全路径名
/// </summary>
/// <param name="SOrgID">组织机构ID</param>
/// <returns>组织机构的全路径</returns>
public string GetAddressName(string SOrgID)
{
string StrOrgPath = "";
int OrgID = 0;
OrgID = Convert.ToInt32(SOrgID);
string StrSQL;
DataSet ds = new DataSet();
MSDA.Open();
for (; OrgID > 0; )
{
StrSQL = "select ParentOrgID,OrgName from Tb_Sys_Organization where OrgID=" + OrgID;
ds.Clear();
MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
OrgID = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
if (StrOrgPath == "")
{
StrOrgPath = ds.Tables[0].Rows[0][1].ToString();
}
else
{
StrOrgPath = ds.Tables[0].Rows[0][1].ToString() + "/" + StrOrgPath;
}
}
MSDA.Close();
return StrOrgPath;
}
/// <summary>
/// 得到组织机构名称
/// </summary>
/// <param name="SOrgID">组织机构ID</param>
/// <returns>组织机构名称</returns>
public string GetOrgzationName(string SOrgID)
{
string StrOrgName;
int OrgID;
OrgID = Convert.ToInt32(SOrgID);
string StrSQL = "select OrgName from Tb_Sys_Organization where OrgID=" + OrgID;
DataSet ds = new DataSet();
MSDA.Open();
MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
if (ds.Tables[0].Rows.Count > 0)
StrOrgName = ds.Tables[0].Rows[0][0].ToString();
else
StrOrgName = "未知";
MSDA.Close();
return StrOrgName;
}
/// <summary>
/// 同名检测
/// </summary>
/// <param name="StrEmpName">职工名称</param>
/// <returns>相同为true,不同为false</returns>
public bool HomonymyCheck(string StrEmpName)
{
string StrSQL = "select EmpID from Tb_Sys_Employee where EmpName='" + StrEmpName + "'";
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
if (ds.Tables[0].Rows.Count > 0)
{
return false;
}
return true;
}
/// <summary>
/// 的到父节点树的内容
/// </summary>
/// <param name="StrOrgID">父节点id</param>
/// <returns>string</returns>
public string GetParentNode(string StrOrgID)
{
int iOrgID;
string StrSQL, StrOrgName;
DataSet ds = new DataSet();
iOrgID = Convert.ToInt32(StrOrgID);
StrSQL = "select OrgPath from Tb_Sys_Organization where OrgID=" + iOrgID;
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
StrOrgName = ds.Tables[0].Rows[0][0].ToString();
return StrOrgName;
}
/// <summary>
/// 等到父节点树的内容
/// </summary>
/// <param name="StrOrgID">父节点id</param>
/// <returns>string</returns>
public string GetParentID(string StrOrgID)
{
int iOrgID;
string StrSQL, StrOrgName;
DataSet ds = new DataSet();
iOrgID = Convert.ToInt32(StrOrgID);
StrSQL = "select ParentOrgID from Tb_Sys_Organization where OrgID=" + iOrgID;
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
StrOrgName = ds.Tables[0].Rows[0][0].ToString();
return StrOrgName;
}
/// <summary>
/// 新增组织机构
/// </summary>
/// <param name="ParentID">父ID</param>
/// <param name="OrgName">名称</param>
/// <param name="OrgDesc">描述</param>
/// <param name="Level">级别</param>
/// <param name="OrgPath">全路径</param>
/// <returns>bool</returns>
public void AddOrgzation(string ParentID, string OrgName, string OrgDesc, string Level, string OrgPath,string strCID)
{
int iParentID, iOrgID, iLevel;
string OrgType = "1";
iParentID = Convert.ToInt32(ParentID);
iOrgID = GetMaxOrgzationID();
iLevel = Convert.ToInt32(Level) + 1;
DateTime EditDate = new DateTime();
EditDate = System.DateTime.Now;
OrgPath = OrgPath + "/" + Convert.ToString(iOrgID);
//string scid = GetOrgMaxCID();
string StrSQL = "insert into Tb_Sys_Organization values(" + iOrgID + "," + iParentID + ",'" +
OrgName + "','" + OrgDesc + "'," + 1 + "," + iLevel + ",'" + EditDate + "','" + OrgType + "','" +
OrgPath + "','" + strCID + "')";
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, StrSQL, null);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
finally
{
MSDA.Close();
}
}
/// <summary>
/// 获取职工全部内容
/// </summary>
/// <param name="SEmpID">职工ID</param>
/// <returns>DataView</returns>
public DataView GetEmp(string SEmpID)
{
int iEmpID;
iEmpID = Convert.ToInt32(SEmpID);
DataSet ds = new DataSet();
string StrSQL = "select * from Tb_Sys_Employee where EmpID=" + iEmpID;
MSDA.Open();
MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
/// <summary>
/// 修改职工信息
/// </summary>
/// <param name="iEmpID">职工ID</param>
/// <param name="EmpName">职工姓名</param>
/// <param name="PNum">电话号码</param>
/// <param name="Email">电子邮件</param>
/// <param name="iCheck">身份(临时或正式)</param>
/// <param name="iType">职工类型</param>
/// <returns>bool</returns>
public bool UpEmp(int iEmpID, string EmpName, string PNum, string Email, int iCheck,int iType,ArrayList al,int iPEmpID)
{
DateTime EditDate = new DateTime();
EditDate = System.DateTime.Now;
string StrSQL = "update Tb_Sys_Employee set EmpName='" + EmpName + "',EMail='" +
Email + "',PhoneNumber='" + PNum + "',EditDate='" + EditDate + "',TempFlag=" +
iCheck + ",EmpType=" + iType + ",ParentEmp=" + iPEmpID + " where EmpID = " + iEmpID;
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, StrSQL, null);
StrSQL = "delete from Tb_Sys_EmpOpRole where EmpID=" + iEmpID;
MSDA.ExecuteNonQuery(CommandType.Text, StrSQL, null);
for (int i = 0; i < al.Count; i++)
{
StrSQL = "insert into Tb_Sys_EmpOpRole values(" + iEmpID + "," + Convert.ToInt32(al[i].ToString()) + ")";
MSDA.ExecuteNonQuery(CommandType.Text, StrSQL, null);
}
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
finally
{
MSDA.Close();
}
}
/// <summary>
/// 通过组织机构ID获取组织机构全部信息
/// </summary>
/// <param name="sOrgID">组织机构ID</param>
/// <returns>DataView组织机构信息</returns>
public DataView GetOrganzation(string sOrgID)
{
int iOrgID;
iOrgID = Convert.ToInt32(sOrgID);
DataSet ds = new DataSet();
string StrSQL = "select * from Tb_Sys_Organization where OrgID=" + iOrgID;
MSDA.Open();
MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
/// <summary>
/// 修改组织机构信息
/// </summary>
/// <param name="iOrgID">组织机构ID</param>
/// <param name="OrgName">组织机构名称</param>
/// <param name="OrgDesc">组织机构描述</param>
/// <returns>bool</returns>
public void UpOrg(int iOrgID, string OrgName, string OrgDesc, string strCID)
{
DateTime EditDate = new DateTime();
EditDate = System.DateTime.Now;
string StrSQL = "update Tb_Sys_Organization set OrgName='" + OrgName + "',OrgDesc='" +
OrgDesc + "',OrgCID='" + strCID + "' where OrgID = " + iOrgID;
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, StrSQL, null);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
finally
{
MSDA.Close();
}
}
/// <summary>
/// 删除职工信息
/// </summary>
/// <param name="iEmpID">职工ID</param>
/// <returns>bool</returns>
public bool DelEmp(int iEmpID)
{
string StrSQL = "delete from Tb_Sys_Employee where EmpID=" + iEmpID;
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, StrSQL, null);
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
finally
{
MSDA.Close();
}
}
/// <summary>
/// 判断职工是否已被使用
/// </summary>
/// <param name="iEmpID">职工ID</param>
/// <returns>bool</returns>
public bool BlEmpUser(int iEmpID)
{
string StrSQL = "select * from Tb_Sys_User where EmpID=" + iEmpID;
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ds.Clear();
return false;
}
ds.Clear();
return true;
}
/// <summary>
/// 判断是否有子组织
/// </summary>
/// <param name="iOrgParent">组织机构ID</param>
/// <returns>bool</returns>
public bool BlOrgParent(int iOrgParent)
{
string StrSQL = "select * from Tb_Sys_Organization where ParentOrgID=" + iOrgParent;
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ds.Clear();
return false;
}
ds.Clear();
return true;
}
/// <summary>
/// 判断是否有子职工
/// </summary>
/// <param name="iEmpParent">所属组织机构代码</param>
/// <returns>bool</returns>
public bool BlEmpParent(int iEmpParent)
{
string StrSQL = "select * from Tb_Sys_Employee where PrimaryOrgID=" + iEmpParent;
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
if (ds.Tables[0].Rows.Count > 0)
{
ds.Clear();
return false;
}
ds.Clear();
return true;
}
/// <summary>
/// 删除组织机构信息
/// </summary>
/// <param name="iOrgID">组织机构ID</param>
/// <returns>bool</returns>
public bool DelOrg(int iOrgID)
{
string StrSQL = "delete from Tb_Sys_Organization where OrgID=" + iOrgID;
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, StrSQL, null);
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
finally
{
MSDA.Close();
}
}
/// <summary>
/// 职工转移组织机构
/// </summary>
/// <param name="iEmpID">职工ID</param>
/// <param name="iOrgID">组织机构ID</param>
/// <returns>bool</returns>
public bool UpEmpParent(int iEmpID, int iOrgID)
{
string StrSQL = "update Tb_Sys_Employee set PrimaryOrgID=" + iOrgID + " where EmpID=" + iEmpID;
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, StrSQL, null);
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
finally
{
MSDA.Close();
}
}
/// <summary>
/// 获取角色名
/// </summary>
/// <param name="iRoleID">角色ID</param>
/// <returns>角色名</returns>
public string GetRoleName(int iRoleID)
{
string StrSQL, sRoleName;
StrSQL = "select RoleName from Tb_Sys_Role where RoleID=" + iRoleID;
MSDA.Open();
DataSet ds = new DataSet();
ds = MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
if (ds.Tables[0].Rows.Count > 0)
{
sRoleName = ds.Tables[0].Rows[0][0].ToString();
}
else
{
sRoleName = "未知";
}
return sRoleName;
}
/// <summary>
/// 获取组织机构字符ID
/// </summary>
/// <returns>组织机构字符ID</returns>
public string GetOrgMaxCID()
{
string MaxCID;
int iMax;
string strSQL = "select max(OrgcID) as MaxCID from Tb_Sys_Organization where len(OrgcID) > 1";
MSDA.Open();
SqlDataReader dr = MSDA.ExecuteReader(CommandType.Text, strSQL, null);
if (dr.Read())
{
if (dr["MaxCID"] != null && dr["MaxCID"].ToString().Trim() != "")
{
MaxCID = dr["MaxCID"].ToString().Trim();
if (MaxCID.Substring(1, 1) == "z")
{
iMax = Convert.ToInt32(MaxCID.Substring(0, 1), 16) + 1;
MaxCID = iMax.ToString() + MaxCID.Substring(1, 1).ToString();
}
else
{
iMax = Convert.ToInt32(MaxCID.Substring(1, 1), 16) + 1;
//MaxCID =Convert.ToString( iMax & 0x00ff);
MaxCID = MaxCID.Substring(0, 1).ToString() + GetCharString(iMax);
}
dr.Close();
MSDA.Close();
return MaxCID;
}
else
{
dr.Close();
strSQL = "select max(OrgcID) as MaxCID from Tb_Sys_Organization";
dr = MSDA.ExecuteReader(CommandType.Text, strSQL, null);
if (dr.Read())
{
if (dr["MaxCID"] != null && dr["MaxCID"].ToString().Trim() != "")
{
MaxCID = dr["MaxCID"].ToString().Trim();
if (MaxCID == "z")
{
MaxCID = "aa";
}
else if (MaxCID == "0")
{
MaxCID = "a";
}
else
{
iMax = Convert.ToInt32(MaxCID, 16) + 1;
//MaxCID =Convert.ToString( iMax & 0x00ff);
MaxCID = GetCharString(iMax);
}
}
else
{
MaxCID = "a";
}
}
else
{
MaxCID = "a";
}//end read
dr.Close();
MSDA.Close();
return MaxCID;
}
}//end dr
dr.Close();
MSDA.Close();
return MaxCID="a";
}
/// <summary>
/// int转换字符
/// </summary>
/// <param name="i">int</param>
/// <returns>字符串</returns>
public string GetCharString(int i)
{
string s = "";
switch (i)
{
case 10:
s = "a";
break;
case 11:
s = "b";
break;
case 12:
s = "c";
break;
case 13:
s = "d";
break;
case 14:
s = "e";
break;
case 15:
s = "f";
break;
case 16:
s = "g";
break;
case 17:
s = "h";
break;
case 18:
s = "i";
break;
case 19:
s = "j";
break;
case 20:
s = "k";
break;
case 21:
s = "l";
break;
case 22:
s = "m";
break;
case 23:
s = "n";
break;
case 24:
s = "o";
break;
case 25:
s = "p";
break;
case 26:
s = "q";
break;
case 27:
s = "r";
break;
case 28:
s = "s";
break;
case 29:
s = "t";
break;
case 30:
s = "u";
break;
case 31:
s = "v";
break;
case 32:
s = "w";
break;
case 33:
s = "x";
break;
case 34:
s = "y";
break;
case 35:
s = "z";
break;
default:
s=i.ToString();
break;
}
return s;
}
public DataView GetAllEmp()
{
string strSQL = "select * from Tb_Sys_Employee ";
DataView dv = new DataView();
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv;
}
public DataView GetEmpType()
{
string strSQL = "select * from tb_Sys_Sort ";
DataView dv = new DataView();
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv;
}
public int GetEmpTypeEmp(int iEmpID)
{
string strSQL = "select EmpType from Tb_Sys_Employee where EmpID=" + iEmpID;
DataView dv = new DataView();
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv.Table.Rows.Count > 0 ? Convert.ToInt32(dv.Table.Rows[0][0].ToString()):-1;
}
}
/// <summary>
/// 工作流
/// </summary>
public class EtongFlyChouWF
{
private MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
private QueryParameterCollection Params = new QueryParameterCollection();
public EtongFlyChouWF()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 获取启动工作流的内容
/// </summary>
/// <returns>工作流的名称、描述、状态和地址dataview</returns>
public DataView GetWFStartData(int iEmpID)
{
DataSet ds = new DataSet();
string StrSQL = "select distinct(WFID),KindName,WFName,WFDesc,Status,PageUrl,activityid,s.MustChooseItem,StartUpType from tb_WF_Kind k,tb_workflow w,tb_WF_Activity a,tb_WF_StaticPage s";
StrSQL = StrSQL + " where w.Status=1 and k.KindID=w.KindID and a.workflowid=w.wfid and a.StartEnd=0 and s.StaticPageID=a.PageID and w.StartUpType in (";
StrSQL = StrSQL + "select OpID from Tb_Sys_EmpOpRole where EmpID =" + iEmpID + ")";
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
/// <summary>
/// 获取待办事宜和公共事宜等
/// 1、待办；2、公共；3、其他
/// </summary>
/// <param name="iUserID">用户ID</param>
/// <param name="iStatus">工作状态</param>
/// <returns>DataView工作流的事宜信息</returns>
public DataView GetWFWaitData(int UserIDflag, int iUserID, int Roleid, int iStatus0, int Status2,int iEmpType)
{
Params.Clear();
Params.Add("@Param_UserIDflag", UserIDflag);
Params.Add("@Param_UserID", iUserID);
Params.Add("@Param_RoleID", Roleid);
Params.Add("@Param_Status0", iStatus0);
Params.Add("@Param_Status2", Status2);
Params.Add("@Param_EmpType", iEmpType);
string strSQL = "P_GetWaitProject";
DataView dv = new DataView();
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.StoredProcedure, strSQL, Params);
MSDA.Close();
return dv;
}
/// <summary>
/// 获取工作流种类
/// </summary>
/// <returns>工作流种类编号和名称</returns>
public DataView GetWFKind()
{
string strSQL = "P_GetWFKind";
DataView dv = new DataView();
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.StoredProcedure, strSQL, null);
MSDA.Close();
return dv;
}
/// <summary>
/// 获取工作流页面
/// </summary>
/// <returns>页面编号和页面</returns>
public DataView GetWFStaticPage()
{
string strSQL = "P_GetWFStaticPage";
DataView dv = new DataView();
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.StoredProcedure, strSQL, null);
MSDA.Close();
return dv;
}
/// <summary>
/// 获取页面名称
/// </summary>
/// <param name="iPage">页面ＩＤ</param>
/// <returns>DataView</returns>
public string GetWFStaticPageName(int iPage)
{
string strSQL, strName;
strSQL = "select PageName from tb_WF_StaticPage where StaticPageID=" + iPage ;
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
if (ds.Tables[0].Rows.Count > 0)
{
strName = ds.Tables[0].Rows[0][0].ToString();
}
else
{
strName = "未知";
}
return strName;
}
/// <summary>
/// 更新工作流定义
/// </summary>
/// <param name="iWFID">工作流ID(-1:新增)</param>
/// <param name="sWFDesc">工作流描述</param>
/// <param name="sWFName">工作流名称</param>
/// <param name="iStatus">工作流状态</param>
/// <param name="sUnit">工作流使用单位</param>
/// <param name="iKindID">工作流所属种类</param>
/// <param name="iStartRouterID">起始页(-1:新增和修改)</param>
/// <param name="sStartDesc">关联表单</param>
/// <param name="iStartUpType">工作流启动类型</param>
/// <param name="iReadIncColonyID">同类型ID</param>
/// <param name="iReadtype">是否读取同类型ID</param>
/// <returns>bool</returns>
public bool InsertUpWF(int iWFID, string sWFDesc, string sWFName, int iStatus, string sUnit, int iKindID, int iStartRouterID, string sStartDesc, int iStartUpType, int iReadIncColonyID, int iReadtype)
{
DateTime LastUpdateDate = new DateTime();
DateTime CreateDate = new DateTime();
int OwnExcColonyID = 0, OwnIncColonyID =0, ReadExcColonyID = 0;
string sStartName="开始";
int PanelY = 0,PanelX=0;
string sCodeName="01";
int iX=0,iY =0;
string strSQL = "";
LastUpdateDate = System.DateTime.Now;
if (iWFID == -1) //insert
{
CreateDate = System.DateTime.Now;
strSQL = "insert into tb_WorkFlow values('" + LastUpdateDate + "','" + sWFDesc + "','" + sWFName + "'," +
iStatus + ",'" + CreateDate + "'," + iStartUpType + ",'" + sUnit + "'," + OwnExcColonyID + "," +
OwnIncColonyID + "," + ReadExcColonyID + "," + iReadIncColonyID + "," + iReadtype + "," + iKindID + ",'" +
sStartDesc + "','" + sStartName + "'," + iX + "," + iY + "," + iStartRouterID + "," +
PanelX + "," + PanelY + ",'" + sCodeName + "')";
}
else
{
if (iStartRouterID != -1)
{
strSQL = "update tb_WorkFlow set StartRouterID=" + iStartRouterID + " where WFID=" + iWFID;
}
else
{
strSQL = "update tb_WorkFlow set LastUpdateDate='" + LastUpdateDate + "',WFDesc='" + sWFDesc
+ "',WFName='" + sWFName + "',StartUpType=" + iStartUpType + ",Unit='" + sUnit + "',KindID="
+ iKindID + ",StartDesc='" + sStartDesc + "',Status=" + iStatus + ",StartX=" + iX
+ ",StartY=" + iY + " where WFID=" + iWFID;
}
}
if (strSQL == "" || strSQL == null)
{
return false;
}
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
finally
{
MSDA.Close();
}
}
/// <summary>
/// 返回关联表单
/// </summary>
/// <param name="scode">表单类型</param>
/// <returns>dataview</returns>
public DataView GetWFDataTable(string scode)
{
if (scode != "1" && scode != "0")
{
scode = "1";
}
Params.Clear();
Params.Add("@Param_IsCode", scode);
string strSQL = "P_GetDataTable";
DataView dv = new DataView();
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.StoredProcedure, strSQL, Params);
MSDA.Close();
return dv;
}
/// <summary>
/// 获取已存在的表单信息
/// </summary>
/// <param name="iDtID">表单ＩＤ</param>
/// <param name="scode">表单ＩＤ</param>
/// <returns>DataView</returns>
public DataView GetWFOldTable(int iDtID, string scode)
{
if (scode != "1" && scode != "0")
{
scode = "1";
}
Params.Clear();
Params.Add("@Param_IsCode", scode);
Params.Add("@Param_DTID", iDtID);
string strSQL = "P_GetOldTable";
DataView dv = new DataView();
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.StoredProcedure, strSQL, Params);
MSDA.Close();
return dv;
}
/// <summary>
/// 获取已存在工作流
/// </summary>
/// <returns>dataview工作流要素</returns>
public DataView GetWFNewData()
{
DataSet ds = new DataSet();
string StrSQL = "select WFID,KindName,WFName,WFDesc,Status from tb_WF_Kind,tb_workflow";
StrSQL = StrSQL + " where tb_workflow.KindID=tb_WF_Kind.KindID";
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, StrSQL, null, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
/// <summary>
/// 获取指定工作流信息
/// </summary>
/// <param name="iWFID"></param>
/// <returns></returns>
public DataView GetWFOldData(int iWFID)
{
string strSQL;
strSQL = "select WFID,KindName,WFName,WFDesc,Status,Unit,StartUpType,ReadIncColonyID,Readtype,StartDesc from tb_workflow,tb_WF_Kind";
strSQL = strSQL + " where tb_workflow.KindID=tb_WF_Kind.KindID and WFID=" + iWFID;
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
/// <summary>
/// 获取工作流活动
/// </summary>
/// <param name="iWFID">工作流ID</param>
/// <returns>Dataview</returns>
public DataView GetActivityContent(int iWFID)
{
Params.Clear();
Params.Add("@Param_WFID", iWFID);
string strSQL = "P_ZFGetWFTask";
DataView dv = new DataView();
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.StoredProcedure, strSQL, Params);
MSDA.Close();
return dv;
}
/// <summary>
/// 分页使用
/// </summary>
/// <param name="iWFID"></param>
/// <param name="PageNum"></param>
/// <param name="RowInPage"></param>
/// <returns></returns>
public DataView GetActivityContentPage(int iWFID, int PageNum, int RowInPage)
{
Params.Clear();
Params.Add("@Param_WFID", iWFID);
Params.Add("@page_num", PageNum);
Params.Add("@row_in_page", RowInPage);
string strSQL = "P_ZFGetWFTaskPage";
DataView dv = new DataView();
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.StoredProcedure, strSQL, Params);
MSDA.Close();
//for (int i = (PageNum - 1) * RowInPage; i > 0; i--)
//{
// dv.Delete(i-1);
//}
return dv;
}
/// <summary>
/// 获取活动名
/// </summary>
/// <param name="iActID">活动ID</param>
/// <returns>活动名称</returns>
public string GetActivityName(int iActID)
{
string strSQL,strName;
strSQL = "select ActName from tb_WF_Activity where ActivityID=" + iActID;
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
if (ds.Tables[0].Rows.Count > 0)
{
strName = ds.Tables[0].Rows[0][0].ToString();
}
else
{
strName = "";
}
return strName;
}
/// <summary>
/// 获取工作流任务的所有任务
/// </summary>
/// <param name="iWFID">工作流ID</param>
/// <param name="iMode">协调方式</param>
/// <returns></returns>
public DataView GetParentAcivity(int iWFID,int iMode)
{
Params.Clear();
Params.Add("@Param_WFID", iWFID);
Params.Add("@Param_ModeFlag", iMode);
string strSQL;
strSQL = "P_ZFGetParentTask2";
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.StoredProcedure, strSQL, Params, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
/// <summary>
/// 判断任务要素
/// </summary>
/// <param name="iWFID">工作流编号</param>
/// <param name="iStrat">任务标志</param>
/// <param name="sTableFiled">表字段</param>
/// <returns>true:已存在;false:不存在</returns>
public bool BlStartTask(int iWFID,int iStrat,string sTableFiled)
{
string strSQL = "select * from tb_WF_Activity where WorkFlowID=" +iWFID +" and " + sTableFiled + "=" + iStrat;
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
if (ds.Tables[0].Rows.Count > 0)
{
return true;
}
return false;
}
/// <summary>
/// 判断内容是否存在
/// </summary>
/// <param name="iNum">数</param>
/// <param name="sTableFiled">字段名</param>
/// <param name="sTable">表名</param>
/// <returns>true:已存在;false:不存在</returns>
public bool BlTask(int iNum, string sTableFiled,string sTable)
{
string strSQL;
strSQL = "select * from " + sTable + " where " + sTableFiled + "=" + iNum;
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
if (ds.Tables[0].Rows.Count > 0)
{
return true;
}
return false;
}
/// <summary>
/// 新增工作流活动
/// </summary>
/// <param name="iActID">活动ID</param>
/// <param name="sUnit">使用单位</param>
/// <param name="sActName">活动名称</param>
/// <param name="iWFID">工作流ID</param>
/// <param name="iPage">关联页面</param>
/// <param name="iStart">任务类型</param>
/// <param name="iBlock">阻塞方式</param>
/// <param name="iShunt">分流方式</param>
/// <param name="iMode">协调方式</param>
/// <param name="iRole">角色</param>
/// <param name="iParent">上级活动</param>
/// <returns></returns>
public bool InsertWFAct(int iActID,string sUnit,string sActName,int iWFID,int iPage,int iStart,int iBlock,int iShunt,int iMode,Int64 iRole,int[] iParent,string[] sDoc)
{
string strSQL;
strSQL = "insert into tb_WF_Activity values('" + sUnit + "',1,'','" + sActName + "',"
+ iWFID + "," + iPage + ",'',''," + iStart + "," + iBlock + ","
+ iShunt + "," + iMode + ")";
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
tran.Commit();
}
catch
{
tran.Rollback();
return false;
}
finally
{
MSDA.Close();
}
strSQL = "select max(ActivityID) as maxcode from tb_WF_Activity";
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
int iMaxCode;
try
{
iMaxCode = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
}
catch
{
iMaxCode = 1;
}
for (int i=0;i<iParent.Length;i++)
{
strSQL = "insert into tb_Colony_User values(" + iMaxCode + "," + iParent[i] + "," + iRole + ")";
MSDA.Open();
SqlTransaction trans = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
trans.Commit();
}
catch
{
trans.Rollback();
strSQL = "delete from tb_WF_Activity where ActivityID=" + iMaxCode;
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
return false;
}
finally
{
MSDA.Close();
}
}
for (int j = 0; j < sDoc.Length; j++)
{
if (sDoc[j].Length > 0)
{
strSQL = "insert into tb_Colony_FileName values(" + iMaxCode + ",'" + sDoc[j] + "',1,1" + ")";
MSDA.Open();
SqlTransaction trans = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
trans.Commit();
}
catch
{
trans.Rollback();
strSQL = "delete from tb_WF_Activity where ActivityID=" + iMaxCode;
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
return false;
}
finally
{
MSDA.Close();
}
}
}
return true;
}
public bool InsertWFAct1(int iActID, string sUnit, string sActName, int iWFID, int iPage, int iStart, int iBlock, int iShunt, int iMode, Int64 iRole, int[] iParent, string[] sDoc)
{
string strSQL;
strSQL = "insert into tb_WF_Activity values('" + sUnit + "',1,'','" + sActName + "',"
+ iWFID + "," + iPage + ",'',''," + iStart + "," + iBlock + ","
+ iShunt + "," + iMode + ")";
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
tran.Commit();
}
catch
{
tran.Rollback();
return false;
}
finally
{
MSDA.Close();
}
strSQL = "select max(ActivityID) as maxcode from tb_WF_Activity";
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
int iMaxCode;
try
{
iMaxCode = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
}
catch
{
iMaxCode = 1;
}
for (int i = 0; i < iParent.Length; i++)
{
strSQL = "insert into tb_Colony_User values(" + iMaxCode + "," + iParent[i] + "," + iRole + ")";
MSDA.Open();
SqlTransaction trans = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
trans.Commit();
}
catch
{
trans.Rollback();
strSQL = "delete from tb_WF_Activity where ActivityID=" + iMaxCode;
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
return false;
}
finally
{
MSDA.Close();
}
}
for (int j = 0; j < sDoc.Length; j++)
{
if (sDoc[j].Length > 0)
{
strSQL = "insert into tb_Colony_FileName1 values(" + iMaxCode + "," + Convert.ToInt32(sDoc[j]) + ")";
MSDA.Open();
SqlTransaction trans = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
trans.Commit();
}
catch
{
trans.Rollback();
strSQL = "delete from tb_WF_Activity where ActivityID=" + iMaxCode;
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
return false;
}
finally
{
MSDA.Close();
}
}
}
return true;
}
/// <summary>
/// 修改活动任务
/// </summary>
/// <param name="iActID">活动ID</param>
/// <param name="sUnit">使用单位</param>
/// <param name="sActName">活动名称</param>
/// <param name="iWFID">工作流ID</param>
/// <param name="iPage">关联页面</param>
/// <param name="iStart">任务类型</param>
/// <param name="iBlock">阻塞方式</param>
/// <param name="iShunt">分流方式</param>
/// <param name="iMode">协调方式</param>
/// <param name="iRole">角色</param>
/// <param name="iParent">上级活动</param>
/// <param name="flag">修改标志</param>
/// <returns></returns>
public bool UpWFAct(int iActID, string sUnit, string sActName, int iWFID, int iPage, int iStart, int iBlock, int iShunt, int iMode, Int64 iRole, int[] iParent, int flag,string[] sDoc)
{
string strSQL;
if (flag == 1)
{
strSQL = "update tb_WF_Activity set Unit='" + sUnit + "',ActName='" + sActName + "',PageID="
+ iPage + ",StartEnd=" + iStart + ",BlockFlag=" + iBlock + ",ShuntFlag="
+ iShunt + ",ModeFlag=" + iMode + " where WorkFlowID=" + iWFID + " and ActivityID=" + iActID;
}
else
{
strSQL = "";
}
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
strSQL = "update tb_Colony_User set RoleID=" + iRole + " where ActivityID=" + iActID;
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
strSQL = "delete from tb_Colony_FileName where ActivityID=" + iActID;
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
for (int j = 0; j < sDoc.Length; j++)
{
strSQL = "insert into tb_Colony_FileName values(" + iActID + ",'" + sDoc[j] + "',1,1" + ")";
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
}
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
finally
{
MSDA.Close();
}
}
public bool UpWFAct1(int iActID, string sUnit, string sActName, int iWFID, int iPage, int iStart, int iBlock, int iShunt, int iMode, Int64 iRole, int[] iParent, int flag, string[] sDoc)
{
string strSQL;
if (flag == 1)
{
strSQL = "update tb_WF_Activity set Unit='" + sUnit + "',ActName='" + sActName + "',PageID="
+ iPage + ",StartEnd=" + iStart + ",BlockFlag=" + iBlock + ",ShuntFlag="
+ iShunt + ",ModeFlag=" + iMode + " where WorkFlowID=" + iWFID + " and ActivityID=" + iActID;
}
else
{
strSQL = "";
}
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
strSQL = "update tb_Colony_User set RoleID=" + iRole + " where ActivityID=" + iActID;
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
strSQL = "delete from tb_Colony_FileName where ActivityID=" + iActID;
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
for (int j = 0; j < sDoc.Length; j++)
{
strSQL = "insert into tb_Colony_FileName1 values(" + iActID + "," +Convert.ToInt32(sDoc[j]) + ")";
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
}
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
finally
{
MSDA.Close();
}
}
/// <summary>
/// 获取活动模板
/// </summary>
/// <param name="iActID"></param>
/// <returns></returns>
public DataView GetActFName(int iActID)
{
string strSQL = "select SFileName from tb_Colony_FileName where ActivityID=" + iActID ;
DataView dv = new DataView();
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv;
}
/// <summary>
/// 获取已知活动内容
/// </summary>
/// <param name="iWFID">工作流ID</param>
/// <param name="iActID">活动ID</param>
/// <returns>Dataview</returns>
public DataView GetActivityEdit(int iWFID, int iActID)
{
string strSQL;
strSQL = "select * from tb_WF_Activity a,tb_Colony_User u where WorkFlowID=" + iWFID;
strSQL = strSQL + " and a.ActivityID=" + iActID + " and u.ActivityID=a.ActivityID";
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
/// <summary>
/// 获取活动名
/// </summary>
/// <param name="iActID">活动ID</param>
/// <returns>活动名称</returns>
public string GetActName(int iActID)
{
string strSQL, strName;
strSQL = "select ActName from tb_WF_Activity where ActivityID=" + iActID;
DataSet ds = new DataSet();
MSDA.Open();
MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
if (ds.Tables[0].Rows.Count < 1)
{
strName = "起始";
}
else
{
strName = ds.Tables[0].Rows[0][0].ToString();
}
return strName;
}
/// <summary>
/// 删除任务活动
/// </summary>
/// <param name="iWFID">工作流ID</param>
/// <param name="iActID">活动ID</param>
/// <returns>bool</returns>
public bool DelActivity(int iWFID, int iActID)
{
string strSQL, strSQL1;
strSQL = "delete from tb_Colony_User where ActivityID =" + iActID;
strSQL1 = "delete from tb_WF_Activity where ActivityID =" + iActID + " and WorkFlowID=" + iWFID;
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
MSDA.ExecuteNonQuery(CommandType.Text, strSQL1, null);
tran.Commit();
return true;
}
catch
{
tran.Rollback();
return false;
}
finally
{
MSDA.Close();
}
}
/// <summary>
/// 获取工作流分流条件种类
/// </summary>
/// <returns>获取工作流分流条件种类编号和名称</returns>
public DataView GetConditionWFKind()
{
string strSQL = "select * from tb_WorkFlowRouter";
DataView dv = new DataView();
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv;
}
/// <summary>
/// 查询工作流分流条件种类
/// </summary>
/// <param name="id">工作流分流条件种类ID</param>
/// <param name="sname">工作流分流条件种类名称</param>
/// <returns></returns>
public DataView GetConditionWFKindBy(int id,string sname)
{
string strSQL = "select * from tb_WorkFlowRouter";
if (id > -2)
{
strSQL = strSQL + " where RouID=" + id;
}
else if (sname.Length > 0)
{
strSQL = strSQL + " where RouName='" + sname + "'";
}
DataView dv = new DataView();
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv;
}
/// <summary>
/// 更新工作流分流条件种类
/// </summary>
/// <param name="id">工作流分流条件种类ID</param>
/// <param name="name">name</param>
/// <param name="iwf">wfid</param>
/// <returns></returns>
public void UpConditionWFKindBy(int id,string name,int iwf)
{
string strSQL = "update tb_WorkFlowRouter set RouName='" + name + "',wfid=" + iwf + " where rouid=" + id ;
try
{
MSDA.Open();
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
}
/// <summary>
/// 根据条件获取所有工作流
/// </summary>
/// <returns>工作流编号和名称</returns>
public DataView GetWFByCondition(int wfid)
{
string strSQL = "select * from tb_WorkFlow";
if (wfid > -1)
{
strSQL = strSQL + " where wfid<>" + wfid;
}
DataView dv = new DataView();
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv;
}
}
/// <summary>
/// 代码表内容
/// </summary>
public class EtongFlyChouCode
{
private MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
private QueryParameterCollection Params = new QueryParameterCollection();
public EtongFlyChouCode()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 获取所有种类方式
/// </summary>
/// <returns>DataView</returns>
public DataView BlockCodeAll(string sTableName)
{
string strSQL = "select * from " + sTableName;
DataSet ds = new DataSet();
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
return ds.Tables[0].DefaultView;
}
public DataView BStringSelect(string sTField, string sTable, int i)
{
string strSQL = "select " + sTField + " from " + sTable;
DataSet ds = new DataSet();
if (i == 2)
{
MSSqlDataAccess MSDA2 = new MSSqlDataAccess(i);
MSDA2.Open();
ds = MSDA2.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA2.Close();
}
else
{
MSDA.Open();
ds = MSDA.ExecuteDataset(CommandType.Text, strSQL, null, ds, "");
MSDA.Close();
}
return ds.Tables[0].DefaultView;
}
public string GetShuntByID(int iShunt)
{
string sret = "";
DataView dv = new DataView();
try
{
MSDA.Open();
string sSQL = "select ShuntName from tb_Shunt where ShuntID=" + iShunt;
dv = MSDA.ExecuteDataView(CommandType.Text, sSQL, null);
sret = dv.Table.Rows.Count > 0 ? dv.Table.Rows[0][0].ToString() : "未知";
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return sret;
}
public string GetSortByID(string iSort)
{
string sret = "";
DataView dv = new DataView();
try
{
MSDA.Open();
string sSQL = "select name from tb_Sys_Sort where id=" + iSort;
dv = MSDA.ExecuteDataView(CommandType.Text, sSQL, null);
sret = dv.Table.Rows.Count > 0 ? dv.Table.Rows[0][0].ToString() : "未知";
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return sret;
}
}
/// <summary>
/// 工作流流程分析
/// </summary>
public class EtongFlyChouWFTask
{
private int m_tID; //ActivityID
private int m_pID; //ParentActID
private string m_tName; //ActName
private int m_wID; //WFID
private int m_type; //startend
private string m_tUnit; //Unit
//任务代码
public int TaskID
{
set { m_tID = value; }
get { return m_tID; }
}
//流程代码
public int ProcessID
{
set { m_pID = value; }
get { return m_pID; }
}
//任务类型 0:起始任务；1：后续任务；2：最终任务
public int TaskType
{
set { m_type = value; }
get { return m_type; }
}
//任务名称
public string TaskName
{
set { m_tName = value; }
get { return m_tName; }
}
//使用单位
public string TaskUnit
{
set { m_tUnit = value; }
get { return m_tUnit; }
}
//所属工作流ID
public int TaskWFID
{
set { m_wID = value; }
get { return m_wID; }
}
//以下方法用于绘制流程图所用
//得到起始任务
public static EtongFlyChouWFTask GetInitTask(int iWFID)
{
string strSQL;
EtongFlyChouWFTask tsk = new EtongFlyChouWFTask();
MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
strSQL = "select * from tb_WF_Activity where StartEnd=0 and WorkFlowID=" + iWFID;
try
{
MSDA.Open();
SqlDataReader dr = MSDA.ExecuteReader(CommandType.Text, strSQL, null);
if (dr.Read() == true)
{
tsk.m_tID = Convert.ToInt32(dr["ActivityID"]);
tsk.m_wID = Convert.ToInt32(dr["WorkFlowID"]);
tsk.m_pID = -1;
tsk.m_tName = Convert.ToString(dr["ActName"]);
tsk.m_tUnit = Convert.ToString(dr["Unit"]);
tsk.m_type = Convert.ToInt32(dr["StartEnd"]);
}
dr.Close();
}
finally
{
MSDA.Close();
}
return tsk;
}
//得到下一步任务
public ArrayList GetNextTask()
{
ArrayList al = new ArrayList();
string strSQL;
EtongFlyChouWFTask tsk;
MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
strSQL = "select a.*,ParentActID from tb_WF_Activity a,tb_Colony_User u where a.ActivityID=u.ActivityID"
+ " and u.ParentActID=" + this.m_tID + " and a.WorkFlowID=" + this.m_wID;
try
{
MSDA.Open();
SqlDataReader dr = MSDA.ExecuteReader(CommandType.Text, strSQL, null);
while (dr.Read() == true)
{
tsk = new EtongFlyChouWFTask();
tsk.m_tID = Convert.ToInt32(dr["ActivityID"]);
tsk.m_wID = Convert.ToInt32(dr["WorkFlowID"]);
tsk.m_pID = Convert.ToInt32(dr["ParentActID"]);
tsk.m_tName = Convert.ToString(dr["ActName"]);
tsk.m_tUnit = Convert.ToString(dr["Unit"]);
tsk.m_type = Convert.ToInt32(dr["StartEnd"]);
al.Add(tsk);
}
dr.Close();
////由于SqlDataReader独占Connection
//for (i=0;i<al.Count;i++)
//{
// tsk = al[i] as EtongFlyChouWFTask;
//}
}
finally
{
MSDA.Close();
}
return al;
}
//得到上一步任务
public ArrayList GetPrevioueTasks()
{
ArrayList al = new ArrayList();
string strSQL;
EtongFlyChouWFTask tsk;
MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
strSQL = "select a.* from tb_WF_Activity a,tb_Colony_User u where u.ActivityID = " + this.m_tID + " and a.ActivityID = u.ActivityID";
try
{
MSDA.Open();
SqlDataReader dr = MSDA.ExecuteReader(CommandType.Text, strSQL, null);
while (dr.Read() == true)
{
tsk = new EtongFlyChouWFTask();
tsk.m_tID = Convert.ToInt32(dr["ActivityID"]);
tsk.m_wID = Convert.ToInt32(dr["WorkFlowID"]);
tsk.m_tName = Convert.ToString(dr["ActName"]);
tsk.m_tUnit = Convert.ToString(dr["Unit"]);
tsk.m_type = Convert.ToInt32(dr["StartEnd"]);
al.Add(tsk);
}
dr.Close();
}
finally
{
MSDA.Close();
}
return al;
}
}
/// <summary>
/// 项目查询
/// </summary>
public class EtongFlyChouWFPrj
{
//说明-----全部的值为-1 tb_Project
private int m_pID; //ProjectID
private string m_pName; //ProjectName
private string m_pLsh; //ProjcetLsh
private string m_pNum; //ProjectNumber
private DateTime m_pDate; //CreateDate
private string m_pEditUint; //EditUnit
private string m_pManager; //ProjectManager
private string m_pTran; //Transactor
private int m_pType; //ProjectType
private int m_pStatus; //Status
private decimal m_pPlan; //PlanCharge
private decimal m_pFact; //FactCharge
private DateTime m_pSucc; //SuccessedDate
//日志tb_Project_Log
private int m_lID; //LogID
private string m_lWriter; //Writer
private DateTime m_lDate; //WriteDate
private string m_lInfo; //WriteInfo
//实例tb_WF_Instance
private int m_iID; //WfInstanceID
private DateTime m_iDate; //CreateDate
private int m_iUser; //CreateUserID
private int m_iRole; //TargetRoleID
private int m_iStatus; //status(1、项目申请2、项目施工3、验收4、变更5、废旧物资6、汇总)
//实例活动tb_WF_ActIns
private int m_sID; //ActInsID
private int m_sUser; //UserID
private int m_sStatus; //Status
private string m_sBlockReason; //BlockReason
//工作流tb_WorkFlow
private int m_wID; //WFID
private string m_wName; //WFName
//权限
private int m_DeptID; //
private int m_EmpID; //
//业务角色
private string m_Must;
private int m_EmpType;
private string m_cDID;
private Int16 m_Psort;
private int m_Ndept;
private string m_OpName;
private int m_OpID;
private ArrayList m_OpPerm;
private string m_OpDesc;
//活动表tb_wf_activity
private int m_aID;
private int m_aStart;
//项目代码
public int PrjID
{
set { m_pID = value; }
get { return m_pID; }
}
//项目名称
public string PrjName
{
set { m_pName = value; }
get { return m_pName; }
}
//项目流水号
public string PrjLsh
{
set { m_pLsh = value; }
get { return m_pLsh; }
}
//项目编号
public string PrjNum
{
set { m_pNum = value; }
get { return m_pNum; }
}
//任务创建日期
public DateTime PrjCDate
{
set { m_pDate = value; }
get { return m_pDate; }
}
//项目编报单位
public string PrjUnit
{
set { m_pEditUint = value; }
get { return m_pEditUint; }
}
//项目负责人
public string PrjManager
{
set { m_pManager = value; }
get { return m_pManager; }
}
//项目经办人
public string PrjTran
{
set { m_pTran = value; }
get { return m_pTran; }
}
//项目所属类型
public int PrjType
{
set { m_pType = value; }
get { return m_pType; }
}
//项目状态
public int PrjStatus
{
set { m_pStatus = value; }
get { return m_pStatus; }
}
//项目计划费用
public decimal PrjPlan
{
set { m_pPlan = value; }
get { return m_pPlan; }
}
//项目实际费用
public decimal PrjFact
{
set { m_pFact = value; }
get { return m_pFact; }
}
//项目实际完成时间
public DateTime PrjSucc
{
set { m_pSucc = value; }
get { return m_pSucc; }
}
//日志代码
public int LogID
{
set { m_lID = value; }
get { return m_lID; }
}
//日志填写人
public string LogWriter
{
set { m_lWriter = value; }
get { return m_lWriter; }
}
//日志填写日期
public DateTime LogDate
{
set { m_lDate = value; }
get { return m_lDate; }
}
//日志信息
public string LogInfo
{
set { m_lInfo = value; }
get { return m_lInfo; }
}
//实例tb_WF_Instance
public int iID
{
set { m_iID = value; }
get { return m_iID; }
}
public DateTime iDate
{
set { m_iDate = value; }
get { return m_iDate; }
}
public int iUser
{
set { m_iUser = value; }
get { return m_iUser; }
}
public int iRole
{
set { m_iRole = value; }
get { return m_iRole; }
}
public int iStatus
{
set { m_iStatus = value; }
get { return m_iStatus; }
}
//实例活动tb_WF_ActIns
public int sID
{
set { m_sID = value; }
get { return m_sID; }
}
public int sUser
{
set { m_sUser = value; }
get { return m_sUser; }
}
public int sStatus
{
set { m_sStatus = value; }
get { return m_sStatus; }
}
public string sBlockReason
{
set { m_sBlockReason =value;}
get {return m_sBlockReason;}
}
//工作流tb_WorkFlow
public int wID
{
set { m_wID = value; }
get { return m_wID; }
}
public string wName
{
set { m_wName = value; }
get { return m_wName; }
}
//权限
public int DeptID
{
set {m_DeptID =value;}
get {return m_DeptID;}
}
public int EmpID
{
set {m_EmpID =value;}
get {return m_EmpID;}
}
public string Must
{
set { m_Must = value; }
get { return m_Must; }
}
public int EmpType
{
set {m_EmpType=value;}
get {return m_EmpType;}
}
public string CdID
{
set { m_cDID = value; }
get { return m_cDID; }
}
public Int16 Psort
{
set { m_Psort = value; }
get { return m_Psort; }
}
public int Ndept
{
set { m_Ndept = value; }
get { return m_Ndept; }
}
public string OpName
{
set { m_OpName = value; }
get { return m_OpName; }
}
public int OpID
{
set { m_OpID = value; }
get { return m_OpID; }
}
public ArrayList OpPerm
{
set { m_OpPerm = value; }
get { return m_OpPerm; }
}
public string OpDesc
{
set { m_OpDesc = value; }
get { return m_OpDesc; }
}
//活动表tb_wf_activity
public int aID
{
set { m_aID = value; }
get { return m_aID; }
}
public int aStart
{
set { m_aStart = value; }
get { return m_aStart; }
}
//创建新项目
public EtongFlyChouWFPrj CreateNewPrj()
{
return new EtongFlyChouWFPrj();
}
//项目查询根据状态和类型
public DataView PrjQuery()
{
DataView dv = new DataView();
string strSQL;
MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
if (this.m_pType < -1 || this.m_pType == 0)
{
this.m_pType = -212; //没有数据
}
if (this.m_pStatus < -1)
{
this.m_pStatus = -212; //没有数据
}
strSQL = "select * from tb_Project";
if (this.m_pType != -1)
strSQL = strSQL + " where ProjectType=" + this.m_pType;
if (this.m_pStatus != -1)
{
if (this.m_pType != -1)
strSQL = strSQL + " and Status=" + this.m_pStatus;
else
strSQL = strSQL + " where Status=" + this.m_pStatus;
}
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv;
}
//项目查询依据项目代码
public DataView PrjQureyData()
{
DataView dv = new DataView();
string strSQL;
MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
strSQL = "select * from tb_Project where ProjectID=" + this.m_pID;
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv;
}
//项目日志
public DataView LogQuery()
{
string strSQL;
DataView dv = new DataView();
MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
strSQL = "select * from tb_Project_Log where ProjectID=" + this.m_pID;
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv;
}
//项目日志新建
public void LogInsert()
{
string strSQL;
MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
strSQL = "insert into tb_Project_Log values('" + this.m_lWriter + "','" + this.m_lDate + "','"
+ this.m_lInfo + "'," + this.m_pID;
MSDA.Open();
SqlTransaction tran = MSDA.BeginTransaction();
try
{
MSDA.ExecuteNonQuery(CommandType.Text, strSQL, null);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
finally
{
MSDA.Close();
}
}
//已办工作查询
public DataView PrjQureyAlreadyDo()
{
string strSQL;
MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
QueryParameterCollection Params = new QueryParameterCollection();
DataView dv = new DataView();
strSQL = "P_ZFGetAlreadyDo";
Params.Clear();
Params.Add("@Param_User", this.m_sUser);
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.StoredProcedure, strSQL, Params);
}
catch (Exception ex)
{
throw ex;
}
finally
{
MSDA.Close();
}
return dv;
}
//工作流权限
public DataView PrjGetPermission()
{
int err =-1;
string strSQL = "select OpID from Tb_Sys_EmpOpRole where EmpID=" + this.m_EmpID;
DataView dv = new DataView();
MSSqlDataAccess MSDA = new MSSqlDataAccess(0);
try
{
MSDA.Open();
dv = MSDA.ExecuteDataView(CommandType.Text,strSQL,null);
}
catch (Exception ex)
{
err =1;
throw ex;
}
finally
{
MSDA.Close();
}
if (err ==1) return null;
ArrayList al = new ArrayList();
for (int i = 0; i < dv.Table.Rows.Count; i++)
{
DataView dv2 = new DataView();
try
{
strSQL = "select PermID from tb_Op_Role where OpID=" + Convert.ToInt32(dv.Table.Rows[i]["OpID"]);
MSDA.Open();
dv2 = MSDA.ExecuteDataView(CommandType.Text, strSQL, null);
for (int j = 0; j < dv2.Table.Rows.Count; j++)
{
int s=0;
for (int k = 0; k < al.Count; k++)
{
if (dv2.Table.Rows[j]["PermID"].ToString().Trim() == al[k].ToString()) s = 1;
}
if (s == 0) al.Add(dv2.Table.Rows[j]["PermID"].ToString());
}
}
catch (Exception ex)
{
err = 1;
throw ex;
}
finally
{
MSDA.Close();
}
if (err==1)return null;
}
DataView dv1 = new DataView();
if (al.Count > 0)