/// <summary>
/// 用户注册或登陆后获取用户某个字段信息 </summary>
/// <param name="ComText">SQL语句</param>
/// <returns>根据sql语句得到的字段信息</returns>
public static string GetFieldValue(string ComText)
{
SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings");
SqlCommand cmd = new SqlCommand(ComText, sqlConn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
openConn(sqlConn);
sda.Fill(ds, "table");
closeConn(sqlConn);
string i;
if (ds.Tables[0].Rows.Count > 0)
{
i = (ds.Tables[0].Rows[0][0].ToString());
}
else
{
i = "";
}
return(i);
}
//驳回修改
protected void btnbacklast_onserverclick(object sender, EventArgs e)
{
int a = SaveControl();
if (a == 1)
{
Response.Write("<script>alert('请选择“同意”或者“不同意”再提交！！！')</script>");
return;
}
List <string> list = new List <string>();
string sql = "update OM_SP set ";
if (asd.dt.Rows[0]["SPJB"].ToString() == "1")
{
if (asd.username == asd.dt.Rows[0]["SPR1"].ToString())
{
sql += " SPR1_SJ='" + lbSPR1_SJ.Text.Trim() + "',SPR1_JY='" + "已驳回，" + txtSPR1_JY.Text + "',";
if (rblSPR1_JL.SelectedValue == "n")
{
sql += " SPZT='0'";
}
else
{
Response.Write("<script>alert('该操作仅用于将数据驳回到前一级修改！')</script>");
return;
}
}
}
else if (asd.dt.Rows[0]["SPJB"].ToString() == "2")
{
if (asd.username == asd.dt.Rows[0]["SPR1"].ToString())
{
sql += " SPR1_SJ='" + lbSPR1_SJ.Text.Trim() + "',SPR1_JY='" + "已驳回，" + txtSPR1_JY.Text + "',";
if (rblSPR1_JL.SelectedValue == "n")
{
sql += " SPZT='0'";
}
else
{
Response.Write("<script>alert('该操作仅用于将数据驳回到前一级修改！')</script>");
return;
}
}
else if (asd.username == asd.dt.Rows[0]["SPR2"].ToString())
{
sql += " SPR1_JL='',SPR2_SJ='" + lbSPR2_SJ.Text.Trim() + "',SPR2_JY='" + "已驳回，" + txtSPR2_JY.Text + "',";
if (rblSPR2_JL.SelectedValue == "n")
{
sql += " SPZT='1'";
}
else
{
Response.Write("<script>alert('该操作仅用于将数据驳回到前一级修改！')</script>");
return;
}
}
}
else if (asd.dt.Rows[0]["SPJB"].ToString() == "3")
{
if (asd.username == asd.dt.Rows[0]["SPR1"].ToString())
{
sql += " SPR1_SJ='" + lbSPR1_SJ.Text.Trim() + "',SPR1_JY='" + "已驳回，" + txtSPR1_JY.Text + "',";
if (rblSPR1_JL.SelectedValue == "n")
{
sql += " SPZT='0'";
}
else
{
Response.Write("<script>alert('该操作仅用于将数据驳回到前一级修改！')</script>");
return;
}
}
else if (asd.username == asd.dt.Rows[0]["SPR2"].ToString())
{
sql += " SPR1_JL='',SPR2_SJ='" + lbSPR2_SJ.Text.Trim() + "',SPR2_JY='" + "已驳回，" + txtSPR2_JY.Text + "',";
if (rblSPR2_JL.SelectedValue == "n")
{
sql += " SPZT='1'";
}
else
{
Response.Write("<script>alert('该操作仅用于将数据驳回到前一级修改！')</script>");
return;
}
}
else if (asd.username == asd.dt.Rows[0]["SPR3"].ToString())
{
sql += " SPR2_JL='',SPR3_SJ='" + lbSPR3_SJ.Text.Trim() + "',SPR3_JY='" + "已驳回，" + txtSPR3_JY.Text + "',";
if (rblSPR3_JL.SelectedValue == "n")
{
sql += " SPZT='1y'";
}
else
{
Response.Write("<script>alert('该操作仅用于将数据驳回到前一级修改！')</script>");
return;
}
}
}
sql += " where SPFATHERID='" + asd.sjid + "'";
list.Add(sql);
try
{
DBCallCommon.ExecuteTrans(list);
}
catch
{
Response.Write("<script>alert('审批的sql语句出现问题，请与管理员联系！！！')</script>");
return;
}
}
private List <string> checklist()
{
List <string> list = new List <string>();
string sql = "update OM_SP set ";
if (asd.dt.Rows[0]["SPJB"].ToString() == "1")
{
if (asd.username == asd.dt.Rows[0]["SPR1"].ToString())
{
sql += " SPR1_JL='" + rblSPR1_JL.SelectedValue + "',SPR1_SJ='" + lbSPR1_SJ.Text.Trim() + "',SPR1_JY='" + txtSPR1_JY.Text + "',";
if (rblSPR1_JL.SelectedValue == "n")
{
sql += " SPZT='n'";
}
else
{
sql += " SPZT='y'";
}
}
}
else if (asd.dt.Rows[0]["SPJB"].ToString() == "2")
{
if (asd.username == asd.dt.Rows[0]["SPR1"].ToString())
{
sql += " SPR1_JL='" + rblSPR1_JL.SelectedValue + "',SPR1_SJ='" + lbSPR1_SJ.Text.Trim() + "',SPR1_JY='" + txtSPR1_JY.Text + "',";
if (rblSPR1_JL.SelectedValue == "n")
{
sql += " SPZT='n'";
}
else
{
sql += " SPZT='1y'";
//邮件提醒
string sprid = "";
string sptitle = "";
string spcontent = "";
sprid = hidSPR2ID.Value.Trim();
sptitle = "年度培训计划审批";
spcontent = "有年度培训计划需要您审批，请登录查看！";
DBCallCommon.SendEmail(DBCallCommon.GetEmailAddressByUserID(sprid), new List <string>(), new List <string>(), sptitle, spcontent);
}
}
else if (asd.username == asd.dt.Rows[0]["SPR2"].ToString())
{
sql += " SPR2_JL='" + rblSPR2_JL.SelectedValue + "',SPR2_SJ='" + lbSPR2_SJ.Text.Trim() + "',SPR2_JY='" + txtSPR2_JY.Text + "',";
if (rblSPR2_JL.SelectedValue == "n")
{
sql += " SPZT='n'";
}
else
{
sql += " SPZT='y'";
}
}
}
else if (asd.dt.Rows[0]["SPJB"].ToString() == "3")
{
if (asd.username == asd.dt.Rows[0]["SPR1"].ToString())
{
sql += " SPR1_JL='" + rblSPR1_JL.SelectedValue + "',SPR1_SJ='" + lbSPR1_SJ.Text.Trim() + "',SPR1_JY='" + txtSPR1_JY.Text + "',";
if (rblSPR1_JL.SelectedValue == "n")
{
sql += " SPZT='n'";
}
else
{
sql += " SPZT='1y'";
//邮件提醒
string sprid = "";
string sptitle = "";
string spcontent = "";
sprid = hidSPR2ID.Value.Trim();
sptitle = "年度培训计划审批";
spcontent = "有年度培训计划需要您审批，请登录查看！";
DBCallCommon.SendEmail(DBCallCommon.GetEmailAddressByUserID(sprid), new List <string>(), new List <string>(), sptitle, spcontent);
}
}
else if (asd.username == asd.dt.Rows[0]["SPR2"].ToString())
{
sql += " SPR2_JL='" + rblSPR2_JL.SelectedValue + "',SPR2_SJ='" + lbSPR2_SJ.Text.Trim() + "',SPR2_JY='" + txtSPR2_JY.Text + "',";
if (rblSPR2_JL.SelectedValue == "n")
{
sql += " SPZT='n'";
}
else
{
sql += " SPZT='2y'";
//邮件提醒
string sprid = "";
string sptitle = "";
string spcontent = "";
sprid = hidSPR3ID.Value.Trim();
sptitle = "年度培训计划审批";
spcontent = "有年度培训计划需要您审批，请登录查看！";
DBCallCommon.SendEmail(DBCallCommon.GetEmailAddressByUserID(sprid), new List <string>(), new List <string>(), sptitle, spcontent);
}
}
else if (asd.username == asd.dt.Rows[0]["SPR3"].ToString())
{
sql += " SPR3_JL='" + rblSPR3_JL.SelectedValue + "',SPR3_SJ='" + lbSPR3_SJ.Text.Trim() + "',SPR3_JY='" + txtSPR3_JY.Text + "',";
if (rblSPR3_JL.SelectedValue == "n")
{
sql += " SPZT='n'";
}
else
{
sql += " SPZT='y'";
}
}
}
sql += " where SPFATHERID='" + asd.sjid + "'";
list.Add(sql);
return(list);
}
protected void btnSave_onserverclick(object sender, EventArgs e)
{
string stname = txtSPR1.Text.Trim();
string stid = hidSPR1ID.Value.Trim();
int a = SaveControl();
if (a == 1)
{
Response.Write("<script>alert('请选择“同意”或者“不同意”再提交！！！')</script>");
return;
}
else if (a == 2)
{
Response.Write("<script>alert('请至少填写一项培训计划再提交！！！')</script>");
return;
}
else if (a == 3)
{
Response.Write("<script> if (confirm('您已删除了该单据的全部培训计划，该单据将被删除！！！')) {return true;}else {return false;}</script>");
}
stname = txtSPR1.Text.Trim();
stid = hidSPR1ID.Value.Trim();
if (asd.action == "add")
{
try
{
List <string> list = addlist();
DBCallCommon.ExecuteTrans(list);
}
catch
{
Response.Write("<script>alert('新增的sql语句出现问题，请与管理员联系！！！')</script>");
return;
}
btnSave.Visible = false;
btnbacklast.Visible = false;
Response.Write("<script>alert('您已保存成功！！！')</script>");
}
else if (asd.action == "alter")
{
try
{
List <string> list = alterlist();
DBCallCommon.ExecuteTrans(list);
}
catch
{
Response.Write("<script>alert('修改的sql语句出现问题，请与管理员联系！！！')</script>");
return;
}
}
else if (asd.action == "check")
{
try
{
List <string> list = checklist();
DBCallCommon.ExecuteTrans(list);
}
catch
{
Response.Write("<script>alert('审批的sql语句出现问题，请与管理员联系！！！')</script>");
return;
}
Response.Redirect("OM_NDPXJH_GL.aspx");
}
stname = txtSPR1.Text.Trim();
stid = hidSPR1ID.Value.Trim();
}
protected void btnSubmit_onserverclick(object sender, EventArgs e)
{
if (asd.action == "add" || asd.action == "alter")
{
string sql1 = "select count(SPID) from OM_SP where SPFATHERID='" + asd.sjid + "' and SPLX='NDPXJH'";
DataTable dt = DBCallCommon.GetDTUsingSqlText(sql1);
if (dt.Rows[0][0].ToString() == "0")
{
Response.Write("<script>alert('请先点击”保存“再提交审批！！！')</script>");
return;
}
string sql = "update OM_SP set SPZT='1',SPJB='" + rblSPJB.SelectedValue + "'";
if (rblSPJB.SelectedValue == "1")
{
if (txtSPR1.Text.Trim() == "")
{
Response.Write("<script>alert('请先选择审批人再提交审批！！！')</script>");
return;
}
sql += ",SPR1='" + txtSPR1.Text.Trim() + "',SPR1ID='" + hidSPR1ID.Value + "'";
}
else if (rblSPJB.SelectedValue == "2")
{
if (txtSPR2.Text.Trim() == "" || txtSPR1.Text.Trim() == "")
{
Response.Write("<script>alert('请先选择审批人再提交审批！！！')</script>");
return;
}
sql += ",SPR1='" + txtSPR1.Text.Trim() + "',SPR1ID='" + hidSPR1ID.Value + "'";
sql += ",SPR2='" + txtSPR2.Text.Trim() + "',SPR2ID='" + hidSPR2ID.Value + "'";
}
else if (rblSPJB.SelectedValue == "3")
{
if (txtSPR2.Text.Trim() == "" || txtSPR1.Text.Trim() == "" || txtSPR3.Text.Trim() == "")
{
Response.Write("<script>alert('请先选择审批人再提交审批！！！')</script>");
return;
}
sql += ",SPR1='" + txtSPR1.Text.Trim() + "',SPR1ID='" + hidSPR1ID.Value + "'";
sql += ",SPR2='" + txtSPR2.Text.Trim() + "',SPR2ID='" + hidSPR2ID.Value + "'";
sql += ",SPR3='" + txtSPR3.Text.Trim() + "',SPR3ID='" + hidSPR3ID.Value + "'";
}
sql += " where SPFATHERID='" + asd.sjid + "' and SPLX='NDPXJH'";
try
{
DBCallCommon.ExeSqlText(sql);
//邮件提醒
string sprid = "";
string sptitle = "";
string spcontent = "";
sprid = hidSPR1ID.Value.Trim();
sptitle = "年度培训计划审批";
spcontent = "有年度培训计划需要您审批，请登录查看！";
DBCallCommon.SendEmail(DBCallCommon.GetEmailAddressByUserID(sprid), new List <string>(), new List <string>(), sptitle, spcontent);
}
catch
{
Response.Write("<script>alert('提交审批的sql语句出现问题，请与管理员联系！！！')</script>");
return;
}
Response.Redirect("OM_NDPXJH_GL.aspx");
}
}
/// <summary>
/// 使用sql语句获取SqlDataReader的参考代码
/// </summary>
/// <param name="MySQL"></param>
/// <returns></returns>
public SqlDataReader GetSqlReader(String MySQL)
{
//// 数据库连接参数(对客户端应用程序配置文件的访问)
//string strConn = @ConfigurationManager.ConnectionStrings["connectionStrings"].ConnectionString;
//// 创建连接数据库的一个打开连接
//SqlConnection MyConn = new SqlConnection(strConn);
SqlConnection MyConn = new SqlConnection();
//SqlCommand sqlCmd = new SqlCommand();
MyConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings");
try // 正常运行
{
// 使用 ConnectionString 所指定的属性设置打开数据库连接
MyConn.Open();
// 数据库SQL语句
String strSQL = @MySQL.Trim().ToString();
// 要对数据库执行的一个SQL语句或存储过程
SqlCommand MyComm = new SqlCommand(strSQL, MyConn);
// 提供一种从数据库读取只进的行流的一种方式
SqlDataReader MyReader = MyComm.ExecuteReader();
// 读取数据,判断是否有数据
if (MyReader.HasRows)
{
// 返回成功
return(MyReader);
}
else
{
Console.Write("<script language=JavaScript>");
Console.Write("alert('系统提示：数据读取失败或网络忙，请稍后再试！');");
Console.Write("</script>");
if ((MyReader != null) & (MyReader.IsClosed != true))
{
// 关闭
MyReader.Close();
}
// 判断数据库连接
if (MyConn.State == ConnectionState.Open)
{
// 关闭数据库连接
MyConn.Close();
}
// 返回失败
return(null);
}
}
catch (SqlException) // 数据库操作异常处理
{
Console.Write("<script language=JavaScript>");
Console.Write("alert('系统提示：当前数据库操作失败或网络忙，请稍后再试！');");
Console.Write("</script>");
if (MyConn.State == ConnectionState.Open)
{
// 关闭数据库连接
MyConn.Close();
}
// 返回失败
return(null);
}
catch // 异常处理
{
if (MyConn.State == ConnectionState.Open)
{
// 关闭数据库连接
MyConn.Close();
}
// 返回失败
return(null);
}
finally // 执行完毕清除在try块中分配的任何资源
{
if (MyConn.State == ConnectionState.Open)
{
// 关闭数据库连接
MyConn.Close();
}
}
}
/// <summary>
/// 批量导出BY sqltext（设置行数）
/// </summary>
/// <param name="sqltext"></param>
/// <param name="filename"></param>
/// <param name="filestandard"></param>
/// <param name="row_hang"></param>
/// <param name="ifwidth"></param>
/// <param name="ifmyownstyle"></param>
/// <param name="ifxuhao"></param>
public static void exporteasy(string sqltext, string filename, string filestandard, int row_hang, bool ifwidth, bool ifmyownstyle, bool ifxuhao)
{
System.Data.DataTable dt = DBCallCommon.GetDTUsingSqlText(sqltext);
exporteasy(dt, filename, filestandard, row_hang, ifwidth, ifmyownstyle, ifxuhao);
}
/// <summary>
/// 生成记录ID号（类别+日期+序号+版本号，共16位） </summary>
/// <param name="type">类别:用以判断对哪个表进行操作</param>
/// <param name="existID">已有ID:修改版本号时传值;新建ID时传空值</param>
public static string CreateID(string type, string existID)
{
if (existID.Trim() != "" && existID.Trim().Length != 16)
{
return("error");
}
string oldNO, oldNum, oldEditNum, newNO; //最近ID号、原序号、原版本号、新ID号
string oldYear, oldMonth, oldDay; //最近ID的年、月、日
int newYear, newMonth, newDay; //现在的年、月、日
string newNum, newEditNum; //新序号、版本号
string sqlText;
string tableName = "";
switch (type)
{
case "xm":
{
tableName = "proj_info";
break;
}
case "xl":
{
tableName = "stage_xl";
break;
}
case "kj":
{
tableName = "stage_kj";
break;
}
case "sp":
{
tableName = "stage_sp";
break;
}
case "sq":
{
tableName = "stage_sq";
break;
}
case "sy":
{
tableName = "stage_sy";
break;
}
case "gy":
{
tableName = "process";
break;
}
case "sj":
{
tableName = "design";
break;
}
case "bm":
{
tableName = "bom_info";
break;
}
case "cq":
{
tableName = "m_confirm_info";
break;
}
case "sc":
{
tableName = "stage_sc";
break;
}
default:
return("error");
}
oldYear = "2009";
oldMonth = "01";
oldDay = "01";
oldNO = ""; //原ID号
oldNum = "000"; //原序号
oldEditNum = "000"; //原版本号
newNO = type; //从前缀开始拼ID号
newYear = System.DateTime.Now.Year;
newMonth = System.DateTime.Now.Month;
newDay = System.DateTime.Now.Day;
if (newYear.ToString().Length != 4)
{
return("error");
}
if (existID.Trim() == "")
{
//取最近添加的一条记录
sqlText = "select top 1 id from " + tableName + " order by substring(id,3,14) desc";
SqlDataReader dr = DBCallCommon.GetDRUsingSqlText(sqlText);
if (dr.Read())
{
oldNO = dr["id"].ToString();
}
dr.Close();
}
else
{
oldNO = existID;
}
if (oldNO != "")//不是增加第一条记录时
{
oldYear = oldNO.Substring(2, 4);
oldMonth = oldNO.Substring(6, 2);
oldDay = oldNO.Substring(8, 2);
oldNum = oldNO.Substring(10, 3);
oldEditNum = oldNO.Substring(13, 3);
}
//生成新序号，版本号
if (existID.Trim() != "")//仅生成新版本
{
newNO += oldNO.Substring(2, 8);
newNum = oldNum;
newEditNum = NumToString(Convert.ToInt32(oldEditNum) + 1, 3);
}
else//生成新ID号
{
//同一天增加
if (Convert.ToInt32(oldYear) == newYear && Convert.ToInt32(oldMonth) == newMonth && Convert.ToInt32(oldDay) == newDay)
{
newNO += oldNO.Substring(2, 8);
newNum = NumToString(Convert.ToInt32(oldNum) + 1, 3);
}
else
{
newNO += newYear.ToString() + NumToString(newMonth, 2) + NumToString(newDay, 2);
newNum = "001";
}
newEditNum = "000";
}
newNO += newNum + newEditNum;
return(newNO);
}