public static List<ContractTemplate> QueryContractTemplate()
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
List<ContractTemplate> conTemps = new List<ContractTemplate>();
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = QUERY_CONTRACT_TEMPLATE_STR;
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
ContractTemplate conTemp = new ContractTemplate();
conTemp.TempId = int.Parse(sqlRead["id"].ToString());
conTemp.Name = sqlRead["name"].ToString();
conTemp.CreateDate = sqlRead["createdate"].ToString();
// // 5个栏目信息
// // conTemp.ColumnCount = 5;
// List<String> columns = new List<String>();
// /*
// columns.Add(sqlRead["column1"].ToString());
// columns.Add(sqlRead["column2"].ToString());
// columns.Add(sqlRead["column3"].ToString());
// columns.Add(sqlRead["column4"].ToString());
// columns.Add(sqlRead["column5"].ToString());
//*/
// for (int cnt = 1; cnt <= 5; cnt++)
// {
// String strColumn = "column" + cnt.ToString();
// columns.Add(sqlRead[strColumn].ToString());
// }
// conTemp.ColumnNames = columns;
// // 8个签字人信息
// // conTemp.SignCount = 8;
// List<SignatureTemplate> signatures = new List<SignatureTemplate>();
// for (int cnt = 1; cnt <= 8; cnt++)
// {
// String strSignInfo = "signinfo" + cnt.ToString();
// String strSignId = "signId" + cnt.ToString();
// SignatureTemplate signDatas = new SignatureTemplate();
// signDatas.SignInfo = sqlRead[strSignInfo].ToString();
// signDatas.SignId = int.Parse(sqlRead[strSignId].ToString());
// signatures.Add(signDatas);
// }
// conTemp.SignDatas = signatures;
conTemps.Add(conTemp);
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return conTemps;
}
public static bool ModifyContractTemplate(ContractTemplate conTemp)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
int count = -1;
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = MODIFY_CONTRACT_TEMPLATE_STR;
cmd.Parameters.AddWithValue("@Id", conTemp.TempId);
cmd.Parameters.AddWithValue("@Name", conTemp.Name);
/// 5个栏目信息
/*
cmd.Parameters.AddWithValue("@Column_1", conTemp.ColumnDatas[0]);
cmd.Parameters.AddWithValue("@Column_2", conTemp.ColumnDatas[1]);
cmd.Parameters.AddWithValue("@Column_3", conTemp.ColumnDatas[2]);
cmd.Parameters.AddWithValue("@Column_4", conTemp.ColumnDatas[3]);
cmd.Parameters.AddWithValue("@Column_5", conTemp.ColumnDatas[4]);
*/
for (int cnt = 0; cnt < 5; cnt++)
{
String strColumn = "@Column_" + (cnt + 1).ToString( );
cmd.Parameters.AddWithValue(strColumn, conTemp.ColumnNames[cnt]);
}
/// 8项签字信息
/*
cmd.Parameters.AddWithValue("@SignInfo_1", conTemp.SignDatas[0].SignInfo);
cmd.Parameters.AddWithValue("@SignId_1", conTemp.SignDatas[0].SignId);
cmd.Parameters.AddWithValue("@SignInfo_2", conTemp.SignDatas[1].SignInfo);
cmd.Parameters.AddWithValue("@SignId_2", conTemp.SignDatas[1].SignId);
cmd.Parameters.AddWithValue("@SignInfo_3", conTemp.SignDatas[2].SignInfo);
cmd.Parameters.AddWithValue("@SignId_3", conTemp.SignDatas[2].SignId);
cmd.Parameters.AddWithValue("@SignInfo_4", conTemp.SignDatas[3].SignInfo);
cmd.Parameters.AddWithValue("@SignId_4", conTemp.SignDatas[3].SignId);
cmd.Parameters.AddWithValue("@SignInfo_5", conTemp.SignDatas[4].SignInfo);
cmd.Parameters.AddWithValue("@SignId_5", conTemp.SignDatas[4].SignId);
cmd.Parameters.AddWithValue("@SignInfo_6", conTemp.SignDatas[5].SignInfo);
cmd.Parameters.AddWithValue("@SignId_6", conTemp.SignDatas[5].SignId);
cmd.Parameters.AddWithValue("@SignInfo_7", conTemp.SignData[6].SignInfo);
cmd.Parameters.AddWithValue("@SignId_7", conTemp.SignData[6].SignId);
cmd.Parameters.AddWithValue("@SignInfo_8", conTemp.SignData[7].SignInfo);
cmd.Parameters.AddWithValue("@SignId_8", conTemp.SignData[7].SignId);
*/
for (int cnt = 0; cnt < 8; cnt++)
{
String strSignInfo = "@SignInfo_" + (cnt + 1).ToString();
String strSignId = "@SignId_" + (cnt + 1).ToString();
String strSignLevel = @"SignLevel_" + (cnt + 1).ToString();
String strCanView = "@CanView_" + (cnt + 1).ToString();
String strCanDownload = "@CanDownload_" + (cnt + 1).ToString();
cmd.Parameters.AddWithValue(strSignInfo, conTemp.SignDatas[cnt].SignInfo);
cmd.Parameters.AddWithValue(strSignId, conTemp.SignDatas[cnt].SignEmployee.Id);
cmd.Parameters.AddWithValue(strSignLevel, conTemp.SignDatas[cnt].SignLevel);
cmd.Parameters.AddWithValue(strCanView, conTemp.SignDatas[cnt].CanView);
cmd.Parameters.AddWithValue(strCanDownload, conTemp.SignDatas[cnt].CanDownload);
}
count = cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
if (count == 1)
{
Console.WriteLine("修改会签单信息" + conTemp.TempId.ToString() + "成功");
return true;
}
else
{
Console.WriteLine("修改会签单信息" + conTemp.TempId.ToString() + "失败");
return false;
}
}
/// <summary>
/// 插入员工信息
/// </summary>
/// <param name="conTemp">待插入的会签单模版</param>
/// <returns></returns>
public static bool InsertContractTemplate(ContractTemplate conTemp)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
int count = -1; // 受影响行数
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = INSERT_CONTRACT_TEMPLATE_STR;
cmd.Parameters.AddWithValue("@Name", conTemp.Name);
cmd.Parameters.AddWithValue("@CreateDate", System.DateTime.Now);
// cmd.Parameters.AddWithValue("@CreateDate", System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
/// 5个栏目信息
/*
cmd.Parameters.AddWithValue("@Column_1", conTemp.ColumnDatas[0]);
cmd.Parameters.AddWithValue("@Column_2", conTemp.ColumnDatas[1]);
cmd.Parameters.AddWithValue("@Column_3", conTemp.ColumnDatas[2]);
cmd.Parameters.AddWithValue("@Column_4", conTemp.ColumnDatas[3]);
cmd.Parameters.AddWithValue("@Column_5", conTemp.ColumnDatas[4]);
*/
for (int cnt = 0; cnt < 5; cnt++)
{
String strColumn = "@Column_" + (cnt + 1).ToString( );
cmd.Parameters.AddWithValue(strColumn, conTemp.ColumnNames[cnt]);
}
/// 8项签字信息
/*
cmd.Parameters.AddWithValue("@SignInfo_1", conTemp.SignDatas[0].SignInfo);
cmd.Parameters.AddWithValue("@SignId_1", conTemp.SignDatas[0].SignId);
cmd.Parameters.AddWithValue("@SignInfo_2", conTemp.SignDatas[1].SignInfo);
cmd.Parameters.AddWithValue("@SignId_2", conTemp.SignDatas[1].SignId);
cmd.Parameters.AddWithValue("@SignInfo_3", conTemp.SignDatas[2].SignInfo);
cmd.Parameters.AddWithValue("@SignId_3", conTemp.SignDatas[2].SignId);
cmd.Parameters.AddWithValue("@SignInfo_4", conTemp.SignDatas[3].SignInfo);
cmd.Parameters.AddWithValue("@SignId_4", conTemp.SignDatas[3].SignId);
cmd.Parameters.AddWithValue("@SignInfo_5", conTemp.SignDatas[4].SignInfo);
cmd.Parameters.AddWithValue("@SignId_5", conTemp.SignDatas[4].SignId);
cmd.Parameters.AddWithValue("@SignInfo_6", conTemp.SignDatas[5].SignInfo);
cmd.Parameters.AddWithValue("@SignId_6", conTemp.SignDatas[5].SignId);
cmd.Parameters.AddWithValue("@SignInfo_7", conTemp.SignData[6].SignInfo);
cmd.Parameters.AddWithValue("@SignId_7", conTemp.SignData[6].SignId);
cmd.Parameters.AddWithValue("@SignInfo_8", conTemp.SignData[7].SignInfo);
cmd.Parameters.AddWithValue("@SignId_8", conTemp.SignData[7].SignId);
*/
for (int cnt = 0; cnt < 8; cnt++)
{
String strSignInfo = "@SignInfo_" + (cnt + 1).ToString();
String strSignId = "@SignId_" + (cnt + 1).ToString();
String strSignLevel = @"SignLevel_" + (cnt + 1).ToString();
String strCanView = "@CanView_" + (cnt + 1).ToString();
String strCanDownload = "@CanDownload_" + (cnt + 1).ToString();
cmd.Parameters.AddWithValue(strSignInfo, conTemp.SignDatas[cnt].SignInfo);
cmd.Parameters.AddWithValue(strSignId, conTemp.SignDatas[cnt].SignEmployee.Id);
cmd.Parameters.AddWithValue(strSignLevel, conTemp.SignDatas[cnt].SignLevel);
cmd.Parameters.AddWithValue(strCanView, conTemp.SignDatas[cnt].CanView);
cmd.Parameters.AddWithValue(strCanDownload, conTemp.SignDatas[cnt].CanDownload);
}
count = cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
con.Dispose();
if (count == 1) // 插入成功后的受影响行数为1
{
Console.WriteLine("插入会签单模版成功");
return true;
}
else
{
Console.WriteLine("插入会签单模版失败");
return false;
}
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
/// <summary>
/// 获取到名称为conTempName的模版的信息
/// </summary>
/// <param name="conTempName"></param>
/// <returns></returns>
public static ContractTemplate GetContractTemplate(String conTempName)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
ContractTemplate conTemp = null;
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = GET_CONTRACT_TEMPLATE_ID_STR;
cmd.Parameters.AddWithValue("@Name", conTempName); // 员工编号
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
if (sqlRead.Read())
{
conTemp = new ContractTemplate();
conTemp.TempId = int.Parse(sqlRead["id"].ToString());
conTemp.Name = sqlRead["name"].ToString();
// 5个栏目信息
// conTemp.ColumnCount = 5;
List<String> columns = new List<String>();
/*
columns.Add(sqlRead["column1"].ToString());
columns.Add(sqlRead["column2"].ToString());
columns.Add(sqlRead["column3"].ToString());
columns.Add(sqlRead["column4"].ToString());
columns.Add(sqlRead["column5"].ToString());
*/
for (int cnt = 1; cnt <= 5; cnt++)
{
String strColumn = "column" + cnt.ToString();
columns.Add(sqlRead[strColumn].ToString());
}
conTemp.ColumnNames = columns;
// 8个签字人信息
// conTemp.SignCount = 8;
List<SignatureTemplate> signatures = new List<SignatureTemplate>();
for (int cnt = 1; cnt <= 8; cnt++)
{
String strSignInfo = "signinfo" + cnt.ToString();
String strSignId = "signid" + cnt.ToString();
String strSignName = "signname" + cnt.ToString();
String strDepartmentId = "department" + cnt.ToString();
String strDepartmentName = "department" + cnt.ToString();
String strCanView = "canview" + cnt.ToString();
String strCanDownload = "candownload" + cnt.ToString();
SignatureTemplate signDatas = new SignatureTemplate();
signDatas.SignInfo = sqlRead[strSignInfo].ToString();
Employee employee = new Employee();
employee.Id = int.Parse(sqlRead[strSignId].ToString());
employee.Name = sqlRead[strSignName].ToString();
Department department = new Department();
department.Id = int.Parse(sqlRead[strDepartmentId].ToString());
department.Name = sqlRead[strDepartmentName].ToString();
employee.Department = department;
signDatas.SignEmployee = employee;
signDatas.CanView = int.Parse(sqlRead[strCanView].ToString());
signDatas.CanDownload = int.Parse(sqlRead[strCanDownload].ToString());
signatures.Add(signDatas);
}
conTemp.SignDatas = signatures;
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return conTemp;
}
private int m_totalCaptial; // 累计申请资金额度（元）
#endregion Fields
#region Constructors
/* 以下字段考虑是用员工数类表示还是用员工表的主键来表示
private int m_reqDepartProId; // 申请单位项目负责人的员工编号
public int ReqDepartProId
{
get { return this.m_reqDepartProId; }
set { this.m_reqDepartProId = value; }
}
private int m_reqDepartId; // 申请单位负责人的员工编号
public int ReqDepartId
{
get { return this.m_reqDepartId; }
set { this.m_reqDepartId = value; }
}
private int m_conDepartProId; // 养护主管部门项目负责人（需要签字）
public int ConDepartProId
{
get { return this.m_conDepartProId; }
set { this.m_conDepartProId = value; }
}
private int m_conDepartId; // 养护主管部门负责人（需要签字）
public int ConDepartId
{
get { return this.m_conDepartProId; }
set { this.m_conDepartProId = value; }
}
private int m_planDepartId; // 计划科负责人（需要签字）
public int PlanDepartI
{
get { return this.m_planDepartId; }
set { this.m_planDepartId = value; }
}
private int m_finaDepartId; // 财务科负责人（需要签字）
public int FinalDepartId
{
get { return this.m_finaDepartId; }
set { this.m_finaDepartId = value; }
}
private int m_ ·// 副局长
private int m_director // 局长
*/
/// <summary>
/// 构造函数
/// </summary>
/*public YHJLHXMBKContract()
:this()
{
}*/
/// <summary>
/// 构造函数
/// </summary>
/// <param name="conTemp"></param>
public YHJLHXMBKContract(ContractTemplate conTemp)
{
this.m_conTemp = conTemp; // 模版类信息
}
// keyi
//public static HDJContract ViewHDJContract(String contractId)
//{
// DALHDJContract.GetHDJContactRefuse(contractId);
//}
public static HDJContract GetHDJContactRefuse(String contractId)
{
MySqlConnection con = DBTools.GetMySqlConnection();
MySqlCommand cmd;
HDJContract contract = new HDJContract();
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = GET_HDJCONTRACT_REFUSE_STR;
cmd.Parameters.AddWithValue("@Id", contractId);
MySqlDataReader sqlRead = cmd.ExecuteReader();
cmd.Dispose();
while (sqlRead.Read())
{
contract.Id = sqlRead["id"].ToString();
contract.Name = sqlRead["name"].ToString();
ContractTemplate conTemp = new ContractTemplate();
conTemp.TempId = int.Parse(sqlRead["contempid"].ToString());
conTemp.Name = sqlRead["contempname"].ToString();
// 5个栏目信息
// conTemp.ColumnCount = 5;
List<String> columnnames = new List<String>();
List<String> columndatas = new List<String>();
for (int cnt = 1; cnt <= 5; cnt++)
{
String strColumnname = "columnname" + cnt.ToString();
String strColumnData = "columndata" + cnt.ToString();
columnnames.Add(sqlRead[strColumnname].ToString());
columndatas.Add(sqlRead[strColumnData].ToString());
}
conTemp.ColumnNames = columnnames;
contract.ColumnDatas = columndatas;
// 8个签字人信息
// conTemp.SignCount = 8;
List<SignatureTemplate> signatures = new List<SignatureTemplate>();
List<int> signResults = new List<int>();
List<String > signRemarks = new List<String>();
for (int cnt = 1; cnt <= 8; cnt++)
{
String strSignInfo = "signinfo" + cnt.ToString();
String strSignId = "signid" + cnt.ToString();
String strSignName = "signname" + cnt.ToString();
String strDepartmentId = "departmentid" + cnt.ToString();
String strDepartmentName = "departmentname" + cnt.ToString();
String strSignLevel = "signlevel" + cnt.ToString();
String strCanView = "canview" + cnt.ToString();
String strCanDownload = "candownload" + cnt.ToString();
String strSignResult = "result" + cnt.ToString();
String strSignRemark = "remark" + cnt.ToString();
SignatureTemplate signDatas = new SignatureTemplate();
signDatas.SignInfo = sqlRead[strSignInfo].ToString();
signDatas.SignLevel = int.Parse(sqlRead[strSignLevel].ToString());
signDatas.CanView = int.Parse(sqlRead[strCanView].ToString());
signDatas.CanDownload = int.Parse(sqlRead[strCanDownload].ToString());
Employee employee = new Employee();
employee.Id = int.Parse(sqlRead[strSignId].ToString());
employee.Name = sqlRead[strSignName].ToString();
Department department = new Department();
department.Id = int.Parse(sqlRead[strDepartmentId].ToString());
department.Name = sqlRead[strDepartmentName].ToString();
employee.Department = department;
signDatas.SignEmployee = employee;
// 8个人的签字结果
signResults.Add(int.Parse(sqlRead[strSignResult].ToString()));
signRemarks.Add(sqlRead[strSignRemark].ToString());
signatures.Add(signDatas);
}
conTemp.SignDatas = signatures;
contract.ConTemp = conTemp;
contract.SignResults = signResults;
contract.SignRemarks = signRemarks;
}
con.Close();
con.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
return contract;
}