//档案归档
protected void btnSave_Click(object sender, EventArgs e)
{
if (this.drop_FileLibraryName.SelectedItem.Text == "")
{
new MessageBox(this.Page).Show("请选择档案库室！");
return;
}
ArrayList alsql = new ArrayList();
string sqlStr = ""; //移交记录归档
sqlStr = " update FileTransfer set sjzt=3,Gdr='" + LoginUser.GetUserName + "',Gdsj='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' where id='" + HiddenFieldyj.Value + "' ";
alsql.Add(sqlStr);
string strupdate = ""; //移交档案归档
strupdate = " update FileEnter set FileLibraryID='" + this.drop_FileLibraryName.SelectedValue + "',FileLibraryName='" + this.drop_FileLibraryName.SelectedItem.Text + "',FileLibraryData='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',FileTransferSJLX=2 where id='" + HiddenFieldda.Value + "' ";
alsql.Add(strupdate);
Boolean flag = TSQLServer.execTriggerAll(alsql);
if (flag == true)
{
new MessageBox(Page).ShowAndJump("执法档案归档成功!", "Manage_Center.aspx");
//操作日志
LogListDal.Insert(DateTime.Now, "归档执法档案" + this.HiddenFieldda.Value + "", LoginUser.GetUserId, LoginUser.GetUserName);
}
else
{
new MessageBox(Page).ShowAndJump("执法档案归档失败!", "Manage_Center.aspx");
}
}
/// <summary>
/// 获取案卷类别树结构
/// </summary>
/// <returns>返回一个DataTable数据集</returns>
public static DataTable GetFileClassStruct()
{
return(TSQLServer.ExecDt(@"
with T as
(SELECT *, lv=0, struct = CAST(id as nvarchar) from FileClass where parentFileID = 0
union all select a.*,lv=(b.lv + 1), struct =CAST(CAST(b.struct as nvarchar) + ',' + CAST(a.id as nvarchar) as nvarchar)
from FileClass a join T b on a.parentFileID = b.id
)
select * from T
OPTION(MAXRECURSION 0)
"));
}
//批量接收
protected void btnCheckSave_Click(object sender, EventArgs e)
{
string ids = "0";
for (int i = 0; i < rptList.Items.Count; i++)
{
int id = ((Label)rptList.Items[i].FindControl("lb_id")).Text.ToInt32();
CheckBox cb = (CheckBox)rptList.Items[i].FindControl("cb_id");
if (cb.Checked)
{
ids += "," + id;
}
}
DataTable dttf = daltf.GetDataTable(" and id in (" + ids + ") ");
ArrayList alsql = new ArrayList();
Boolean flag = false;
if (AccessDataSet.HasDataTable(dttf))
{
foreach (DataRow item in dttf.Rows)
{
string sqlStr = ""; //接收移交记录
sqlStr = " update FileTransfer set sjzt=2,Jssj='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',AcceptPeople='" + LoginUser.GetUserName + "',AcceptUnit='" + LoginUser.OrganizerName + "' where id='" + item["id"] + "' ";
alsql.Add(sqlStr);
string strupdate = ""; //接收更新档案状态
DataRow drdg = fedal.GetRow(item["FileEnterID"]);
if (drdg != null)
{
strupdate = " update FileEnter set ReceiptTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',oldEnteCountyId='" + drdg["EnteCountyId"] + "',oldEnteUserName='******',EnteCountyId='" + LoginUser.CountyId + "',EnteUserName='******',EnforcementID_new='" + LoginUser.OrganizerId + "',EnforcementName_new='" + LoginUser.OrganizerName + "',FileLibraryID_old='" + drdg["FileLibraryID"] + "',FileLibraryName_old='" + drdg["FileLibraryName"] + "',FileLibraryID='',FileLibraryName='',FileTransferSJLX=1,FileTransferID='" + item["id"] + "' where id='" + item["FileEnterID"] + "' ";
}
alsql.Add(strupdate);
}
flag = TSQLServer.execTriggerAll(alsql);
}
if (flag == true)
{
new MessageBox(Page).ShowAndJump("批量移交支队成功!", "Manage_Center.aspx");
//操作日志
LogListDal.Insert(DateTime.Now, "案卷档案[" + ids + "]批量移交支队", LoginUser.GetUserId, LoginUser.GetUserName);
}
else
{
new MessageBox(Page).ShowAndJump("批量移交支队失败!", "Manage_Center.aspx");
}
this.BindDaSource();
}
//批量移交支队
protected void lbtnFileTransfer_Click(object sender, EventArgs e)
{
string ids = "0";
for (int i = 0; i < rptyjzdList.Items.Count; i++)
{
int id = ((Label)rptyjzdList.Items[i].FindControl("lb_id")).Text.ToInt32();
CheckBox cb = (CheckBox)rptyjzdList.Items[i].FindControl("cb_id");
if (cb.Checked)
{
ids += "," + id;
}
}
DataTable dttf = dal.GetDataTableyjzd(" and id in (" + ids + ") ");
ArrayList alsql = new ArrayList();
Boolean flag = false;
if (AccessDataSet.HasDataTable(dttf))
{
foreach (DataRow item in dttf.Rows)
{
string sqlStr = ""; //添加移交记录
sqlStr = " insert into FileTransfer(FileEnterID,FileClassID,FileClassName,FileEnterName,TransferPeople,TransferUnit,AcceptSzqxdm,TransferDate,OperateTime,Remark,UserID,Username,sjzt,SZQXDM,Yjlx) values ('" + item["id"].ToString() + "','" + item["FileClassID"].ToString() + "','" + item["FName"].ToString() + "','" + item["FilesName"].ToString() + "','" + LoginUser.GetUserName + "','" + LoginUser.OrganizerName + "','420100','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','','" + LoginUser.GetUserId + "','" + LoginUser.GetUserName + "',0,'" + LoginUser.CountyId + "',1) ";
alsql.Add(sqlStr);
string strupdate = ""; //更新移交时间
strupdate = " update FileEnter set FileTransferDate='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',sjlx=3 where id='" + item["id"].ToString() + "' ";
alsql.Add(strupdate);
}
flag = TSQLServer.execTriggerAll(alsql);
}
if (flag == true)
{
new MessageBox(Page).ShowAndJump("批量移交支队成功!", "FileTransferyjzd.aspx");
//操作日志
LogListDal.Insert(DateTime.Now, "案卷档案[" + ids + "]批量移交支队", LoginUser.GetUserId, LoginUser.GetUserName);
}
else
{
new MessageBox(Page).ShowAndJump("批量移交支队失败!", "FileTransferyjzd.aspx");
}
this.BindDaSource();
}
//操作事件--接收档案
protected void LinkButtonALL_Command(object sender, CommandEventArgs e)
{
string CommandName;
string[] CurrentValueId;
CurrentValueId = e.CommandArgument.ToString().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
CommandName = e.CommandName;
//接收档案
if (CommandName.ToString() == "FileTransfer")
{
DataRow drdg = fedal.GetRow(CurrentValueId[1]);
if (drdg != null)
{
ArrayList alsql = new ArrayList();
string sqlStr = ""; //接收移交记录
sqlStr = " update FileTransfer set sjzt=2,Jssj='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',AcceptPeople='" + LoginUser.GetUserName + "',AcceptUnit='" + LoginUser.OrganizerName + "' where id='" + CurrentValueId[0] + "' ";
alsql.Add(sqlStr);
string strupdate = ""; //接收更新档案状态
strupdate = " update FileEnter set ReceiptTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',oldEnteCountyId='" + drdg["EnteCountyId"] + "',oldEnteUserName='******',EnteCountyId='" + LoginUser.CountyId + "',EnteUserName='******',EnforcementID_new='" + LoginUser.OrganizerId + "',EnforcementName_new='" + LoginUser.OrganizerName + "',FileLibraryID_old='" + drdg["FileLibraryID"] + "',FileLibraryName_old='" + drdg["FileLibraryName"] + "',FileLibraryID='',FileLibraryName='',FileTransferSJLX=1,FileTransferID='" + CurrentValueId[0] + "' where id='" + CurrentValueId[1] + "' ";
alsql.Add(strupdate);
Boolean flag = TSQLServer.execTriggerAll(alsql);
if (flag == true)
{
new MessageBox(Page).ShowAndJump("执法档案接收成功!", "Manage_Center.aspx");
//操作日志
LogListDal.Insert(DateTime.Now, "接收执法档案" + CurrentValueId[0] + "", LoginUser.GetUserId, LoginUser.GetUserName);
}
else
{
new MessageBox(Page).ShowAndJump("执法档案接收失败!", "Manage_Center.aspx");
}
}
else
{
new MessageBox(Page).ShowAndJump("执法档案接收失败!", "Manage_Center.aspx");
}
}
}
//单个移交支队
protected void rptyjzdList_ItemCommand(object source, RepeaterCommandEventArgs e)
{
Label cb_id = (Label)e.Item.FindControl("lb_id");
switch (e.CommandName.ToLower())
{
case "transfer":
DataRow drdg = dal.GetRow(cb_id.Text);
if (drdg != null)
{
ArrayList alsql = new ArrayList();
string sqlStr = ""; //添加移交记录
sqlStr = " insert into FileTransfer(FileEnterID,FileClassID,FileClassName,FileEnterName,TransferPeople,TransferUnit,AcceptSzqxdm,TransferDate,OperateTime,Remark,UserID,Username,sjzt,SZQXDM,Yjlx) values ('" + drdg["id"].ToString() + "','" + drdg["FileClassID"].ToString() + "','" + drdg["FName"].ToString() + "','" + drdg["FilesName"].ToString() + "','" + LoginUser.GetUserName + "','" + LoginUser.OrganizerName + "','420100','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','','" + LoginUser.GetUserId + "','" + LoginUser.GetUserName + "',0,'" + LoginUser.CountyId + "',1) ";
alsql.Add(sqlStr);
string strupdate = ""; //更新移交时间
strupdate = " update FileEnter set FileTransferDate='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',sjlx=3 where id='" + drdg["id"].ToString() + "' ";
alsql.Add(strupdate);
Boolean flag = TSQLServer.execTriggerAll(alsql);
if (flag == true)
{
new MessageBox(Page).ShowAndJump("执法档案" + drdg["FilesName"].ToString() + "移交支队成功!", "FileTransferyjzd.aspx");
//操作日志
LogListDal.Insert(DateTime.Now, "执法档案" + drdg["id"].ToString() + "+" + drdg["FilesName"].ToString() + "移交支队", LoginUser.GetUserId, LoginUser.GetUserName);
}
else
{
new MessageBox(Page).ShowAndJump("执法档案" + drdg["FilesName"].ToString() + "移交支队失败!", "FileTransferyjzd.aspx");
}
}
else
{
new MessageBox(Page).ShowAndJump("执法档案" + drdg["FilesName"].ToString() + "移交支队失败!", "FileTransferyjzd.aspx");
}
this.BindDaSource();
break;
}
}
//拒收
protected void btnCheckSavejs_Click(object sender, EventArgs e)
{
ArrayList alsql = new ArrayList();
string sqlStr = ""; //拒收移交记录
sqlStr = " update FileTransfer set sjzt=1,Jsyy='" + this.txt_jsyy.Text + "',AcceptPeople='" + LoginUser.GetUserId + "',AcceptUnit='" + LoginUser.OrganizerName + "' where id='" + this.HiddenFieldjs.Value + "' ";
alsql.Add(sqlStr);
string strupdate = ""; //拒收更新档案状态
strupdate = " update FileEnter set sjlx=0 where id='" + this.HiddenFieldjsda.Value + "' ";
alsql.Add(strupdate);
Boolean flag = TSQLServer.execTriggerAll(alsql);
if (flag == true)
{
new MessageBox(Page).ShowAndJump("执法档案拒收成功!", "Manage_Center.aspx");
//操作日志
LogListDal.Insert(DateTime.Now, "拒收执法档案" + this.HiddenFieldjsda.Value + "", LoginUser.GetUserId, LoginUser.GetUserName);
}
else
{
new MessageBox(Page).ShowAndJump("执法档案拒收失败!", "Manage_Center.aspx");
}
}
/// <summary>
/// 返回一个DataRow数据集合
/// </summary>
/// <param name="values">传递参数为id</param>
/// <returns>返回一个DataRow</returns>
public DataRow GetRow(params object[] values)
{
return(TSQLServer.ExecDr("select * from [SaveDeadline] where id =" + values[0]));
}
/// <summary>
/// 删除指定多行数据信息
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
public int DeleteAllIn(string values)
{
return(TSQLServer.ExecuteNonQuery("delete [SaveDeadline] where id in(" + values + ")"));
}
/// <summary>
/// 导入数据信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_Ok_Click(object sender, EventArgs e)
{
var f = Request.Files["FileUpload1"]; //获取导入文件路径
int fileclassID = this.GetRequestInt("fileclassID");
if (f.FileName == "")
{
new MessageBox(this).Show("请选择导入数据模板!");
return;
}
string getSheetName = "建审";
if (fileclassID == 12)
{
getSheetName = "验收";
}
if (fileclassID == 13)
{
getSheetName = "开业";
}
string path = Server.MapPath("~/tmp/" + Guid.NewGuid().ToString() + ".xls");
f.SaveAs(path);
FileInfo fi = new FileInfo(path);
DataTable dtS; //存储Excel读取数据信息
try
{
dtS = path.GetExcel(getSheetName).Tables[0];
}
catch
{
new MessageBox(this).Show("导入文件不是Excel文件或Excel文件版本不符!");
return;
}
finally
{
try
{
fi.Delete();
}
catch { }
}
#region 读取excel表格中数据进行字符串拼接
string sqlStr = ""; //定义ID变量
for (int i = 1; i < dtS.Rows.Count; i++)
{
if (dtS.Rows[i][1].ToString() != "")
{
sqlStr += @" if not exists(select * from [FileEnter] where FileClassID= " + fileclassID + " and FilesNum = '" + dtS.Rows[i][1] + "' and PicDocumentNo= '" + dtS.Rows[i][0] + "') ";
string[] fileName;
string BuildUnitName = string.Empty;
string BuildItemName = string.Empty;
switch (fileclassID)
{
case 11: //大队建审
fileName = dtS.Rows[i][2].ToStr().Split(new string[] { "+" }, StringSplitOptions.RemoveEmptyEntries);
if (fileName.Length == 1)
{
BuildUnitName = fileName[0];
BuildItemName = "";
}
else if (fileName.Length > 1)
{
BuildUnitName = fileName[0];
BuildItemName = fileName[1];
}
sqlStr += @" begin insert into [FileEnter](FilesName,PicDocumentNo,FilesNum,FileaddName,BuildAds,BuildUnitName,BuildArea,buildingHeight,BuildALicense,PicDicL,EngDroping,BuildItemName,
EnteCountyId,EnteUserName,EnforcementID,EnforcementName,FileClassID,SaveDeadlineID,FileDirectoryID,DepartmentDataTime,EnterPeople,YesUnit)
values('" + dtS.Rows[i][2].ToStr().Replace("+", "") + "','" + dtS.Rows[i][0].ToStr() + "','" + dtS.Rows[i][1].ToStr() + "','" + dtS.Rows[i][2].ToStr().Replace("+", "") + "','" + dtS.Rows[i][3].ToStr() + "', '" + BuildUnitName + "', '" + dtS.Rows[i][5].ToStr() + "','" + dtS.Rows[i][6].ToStr() + "','" + (dtS.Rows[i][7].ToStr() == "合格" ? 0 : 1) + "','"
+ dtS.Rows[i][8].ToStr() + "','" + dtS.Rows[i][9].ToStr() + "','" + BuildItemName
+ "','" + LoginUser.CountyId + "','" + LoginUser.GetUserName + "','" + LoginUser.OrganizerId + "','" + LoginUser.OrganizerName + "'," + fileclassID + ",1,1,'" + DateTime.Now + "','" + LoginUser.GetUserName + "','" + dtS.Rows[i][4].ToStr() + "') end ";
break;
case 12: //大队验收
fileName = dtS.Rows[i][3].ToStr().Split(new string[] { "+" }, StringSplitOptions.RemoveEmptyEntries);
if (fileName.Length == 1)
{
BuildUnitName = fileName[0];
BuildItemName = "";
}
else if (fileName.Length > 1)
{
BuildUnitName = fileName[0];
BuildItemName = fileName[1];
}
sqlStr += @" begin insert into [FileEnter](FilesName,PicDocumentNo,FilesNum,FileaddName,BuildAds,BuildUnitName,BuildArea,buildingHeight,PicDicL,BuildALicense,EngDroping,BuildItemName,
EnteCountyId,EnteUserName,EnforcementID,EnforcementName,FileClassID,SaveDeadlineID,FileDirectoryID,DepartmentDataTime,EnterPeople,YesUnit,CheckItemName)
values('" + dtS.Rows[i][3].ToStr().Replace("+", "") + "','" + dtS.Rows[i][0].ToStr() + "','" + dtS.Rows[i][1].ToStr() + "','" + dtS.Rows[i][3].ToStr().Replace("+", "") + "','" + dtS.Rows[i][5].ToStr() + "', '"
+ BuildUnitName + "', '" + dtS.Rows[i][6].ToStr() + "','" + dtS.Rows[i][7].ToStr() + "','" + dtS.Rows[i][9].ToStr() + "','"
+ (dtS.Rows[i][8].ToStr() == "合格" ? 0 : 1) + "','" + dtS.Rows[i][10].ToStr() + "','"
+ BuildItemName
+ "','" + LoginUser.CountyId + "','" + LoginUser.GetUserName + "','" + LoginUser.OrganizerId + "','" + LoginUser.OrganizerName + "'," + fileclassID + ",1,1,'" + DateTime.Now + "','" + LoginUser.GetUserName + "','" + dtS.Rows[i][4].ToStr() + "','" + dtS.Rows[i][2].ToStr() + "') end ";
break;
case 13: //大队开业
sqlStr += @" begin insert into [FileEnter](FilesName,PicDocumentNo,FilesNum,FileaddName,CheckAds,CheckResult,PicDicL,EngDroping,CheckUnitName,
EnteCountyId,EnteUserName,EnforcementID,EnforcementName,FileClassID,SaveDeadlineID,FileDirectoryID,DepartmentDataTime,EnterPeople)
values('" + dtS.Rows[i][2].ToStr() + "','" + dtS.Rows[i][0].ToStr() + "','" + dtS.Rows[i][1].ToStr() + "','" + dtS.Rows[i][2].ToStr() + "','" + dtS.Rows[i][3].ToStr() + "', '"
+ (dtS.Rows[i][4].ToStr() == "合格" ? 0 : 1) + "','" + dtS.Rows[i][5].ToStr() + "','" + dtS.Rows[i][6].ToStr() + "','"
+ dtS.Rows[i][2].ToStr()
+ "','" + LoginUser.CountyId + "','" + LoginUser.GetUserName + "','" + LoginUser.OrganizerId + "','" + LoginUser.OrganizerName + "'," + fileclassID + ",1,1,'" + DateTime.Now + "','" + LoginUser.GetUserName + "') end ";
break;
}
}
}
#endregion
int count = 0; //定义受影响的行数
try
{
count = TSQLServer.execTrigger(sqlStr);
}
catch
{
new MessageBox(this).Show("导入数据的格式不正确!");
return;
}
new MessageBox(Page).ShowAndJump("已经成功" + "导入" + (count > 0 ? count : 0) + "条数据！!", "FileEnterList.aspx");
//操作日志
LogListDal.Insert(DateTime.Now, "" + getSheetName + "导入数据", LoginUser.GetUserId, LoginUser.GetUserName);
}
/// <summary>
/// 返回一个DataRow数据集合
/// </summary>
/// <param name="values">传递参数为id</param>
/// <returns>返回一个DataRow</returns>
public DataRow GetRow(params object[] values)
{
return(TSQLServer.ExecDr("select * from [FileLibrary] where ID =" + values[0] + ""));
}
/// <summary>
/// 删除指定多行数据信息
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
public int DeleteAllIn(string values)
{
return(TSQLServer.ExecuteNonQuery("delete [PunishType] where id in(" + values + ")"));
}
/// <summary>
///删除指定一行数据信息
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
public int Delete(params object[] values)
{
return(TSQLServer.ExecuteNonQuery("delete [FileDirectory] where id=" + values[0] + ""));
}
/// <summary>
/// 获取56个民族信息
/// </summary>
/// <param name="strwhere">传递参数为id</param>
/// <returns>返回一个DataTable</returns>
public static DataTable GetDataMZTable()
{
return(TSQLServer.ExecDt(" select * from [NaTion] order by mzid "));
}
/// <summary>
/// 判断档案是否存在
/// </summary>
/// <param name="strwhere">传递参数为strwhere条件</param>
/// <returns>返回一个DataTable</returns>
public static DataTable GetDataDNJYTable(string FilesName, string FileClassID)
{
return(TSQLServer.ExecDt(" select * from [FileEnter] where FileClassID='" + FileClassID + "' and FilesName='" + FilesName + "' "));
}
/// <summary>
/// 返回一个DataTable数据集合
/// </summary>
/// <param name="strwhere">传递参数为id</param>
/// <returns>返回一个DataTable</returns>
public static DataTable GetDataQxmcTable(string strWhere)
{
return(TSQLServer.ExecDt(" select * from [QXMCB] where csdm=420100 " + strWhere + " order by plh "));
}
//批量归档
protected void btnSave_Click(object sender, EventArgs e)
{
if (this.drop_FileLibraryName.SelectedItem.Text == "")
{
new MessageBox(this.Page).Show("请选择档案库室！");
return;
}
string ids = "0";
for (int i = 0; i < rptList.Items.Count; i++)
{
int id = ((Label)rptList.Items[i].FindControl("lb_id")).Text.ToInt32();
CheckBox cb = (CheckBox)rptList.Items[i].FindControl("cb_id");
if (cb.Checked)
{
ids += "," + id;
}
}
if (ids == "0")
{
new MessageBox(this.Page).Show("请选择需要归档的档案！");
return;
}
//int line = dal.UpdateAllGD(this.drop_FileLibraryName.SelectedValue, this.drop_FileLibraryName.SelectedItem.Text, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ids);
DataTable dtgd = dal.GetDataTableyjzd(" and id in (" + ids + ")");
if (AccessDataSet.HasDataTable(dtgd))
{
ArrayList alsql = new ArrayList();
foreach (DataRow item in dtgd.Rows)
{
if (item["FileTransferSJLX"].ToString() == "1" && item["FileTransferID"].ToString() != "")
{
string sqlStr = ""; //移交记录归档
sqlStr = " update FileTransfer set sjzt=3,Gdr='" + LoginUser.GetUserName + "',Gdsj='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' where id='" + item["FileTransferID"] + "' ";
alsql.Add(sqlStr);
string strupdate = ""; //移交档案归档
strupdate = " update FileEnter set FileLibraryID='" + this.drop_FileLibraryName.SelectedValue + "',FileLibraryName='" + this.drop_FileLibraryName.SelectedItem.Text + "',FileLibraryData='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',FileTransferSJLX=2 where id='" + item["ID"] + "' ";
alsql.Add(strupdate);
}
else
{
string strupdate = ""; //移交档案归档
strupdate = " update FileEnter set FileLibraryID='" + this.drop_FileLibraryName.SelectedValue + "',FileLibraryName='" + this.drop_FileLibraryName.SelectedItem.Text + "',FileLibraryData='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' where id='" + item["ID"] + "' ";
alsql.Add(strupdate);
}
}
Boolean flag = TSQLServer.execTriggerAll(alsql);
if (flag == true)
{
new MessageBox(Page).ShowAndJump("执法档案批量归档成功!", "FileEnterList.aspx");
//操作日志
LogListDal.Insert(DateTime.Now, "归档执法档案" + ids + "批量归档", LoginUser.GetUserId, LoginUser.GetUserName);
}
else
{
new MessageBox(Page).ShowAndJump("执法档案批量归档失败!", "FileEnterList.aspx");
}
}
else
{
new MessageBox(Page).ShowAndJump("执法档案批量归档失败!", "FileEnterList.aspx");
}
this.BindDaSource();
}
/// <summary>
/// 导入数据信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btn_Ok_Click(object sender, EventArgs e)
{
var f = Request.Files["FileUpload1"]; //获取导入文件路径
int fileclassID = this.GetRequestInt("fileclassID");
if (f.FileName == "")
{
new MessageBox(this).Show("请选择导入数据模板!");
return;
}
string getSheetName = "行政许可";
if (fileclassID == 12)
{
getSheetName = "验收";
}
string path = Server.MapPath("~/tmp/" + Guid.NewGuid().ToString() + ".xls");
f.SaveAs(path);
FileInfo fi = new FileInfo(path);
DataTable dtS; //存储Excel读取数据信息
try
{
dtS = path.GetExcel(getSheetName).Tables[0];
}
catch
{
new MessageBox(this).Show("导入文件不是Excel文件或Excel文件版本不符!");
return;
}
finally
{
try
{
fi.Delete();
}
catch { }
}
#region 读取excel表格中数据进行字符串拼接
string sqlStr = ""; //定义ID变量
for (int i = 1; i < dtS.Rows.Count; i++)
{
if (dtS.Rows[i][0].ToStrTrim() != "")
{
string fileName = dtS.Rows[i][4].ToStr();
sqlStr += @" if not exists(select * from [FileEnter] where FileClassID= " + fileclassID + " and FilesNum = '" + dtS.Rows[i][1] + "' and PicDocumentNo= '" + dtS.Rows[i][0] + "') ";
sqlStr += @" begin insert into [FileEnter](PicDocumentNo,FilesNum,DepartmentUnit,DepartmentDataTime,FilesName ,
BuildAds,BuildCheckT,YesUnit,BuildArea,buildingHeight,FormalApp,BuildALicense,BuildRemark,PicDicL,PicDicG,PicDicJ,AJDic,EnterPeople,FileClassID,FileDirectoryID,FileFondsNo,SaveDeadlineID,BuildUnitName,BuildItemName,EnteCountyId,EnteUserName,EnforcementID,EnforcementName)
values('" + dtS.Rows[i][0].ToStr() + "','" + dtS.Rows[i][1].ToStr() + "','" + dtS.Rows[i][2].ToStr() + "','" + DateTime.Now + "','" + fileName
+ "', '" + dtS.Rows[i][5].ToStr() + "','" + dtS.Rows[i][6].ToStr() + "','" + dtS.Rows[i][7].ToStr() + "','" + dtS.Rows[i][8].ToStr()
+ "','" + dtS.Rows[i][9].ToStr() + "','" + dtS.Rows[i][10].ToStr() + "','" + (dtS.Rows[i][11].ToStr() == "同意" ? 0 : 1) + "','" + dtS.Rows[i][12].ToStr() + "','" + dtS.Rows[i][13].ToStr()
+ "','" + dtS.Rows[i][14].ToStr() + "','" + dtS.Rows[i][15].ToStr() + "','" + dtS.Rows[i][16].ToStr() + "',''," + fileclassID + ",1,'',1,'" + fileName + "','" + fileName + "','" + LoginUser.CountyId + "','" + LoginUser.GetUserName + "','" + LoginUser.OrganizerId + "','" + LoginUser.OrganizerName + "') end ";
}
}
#endregion
int count = 0; //定义受影响的行数
try
{
count = TSQLServer.ExecuteNonQuery(sqlStr);
}
catch
{
new MessageBox(this).Show("导入数据的格式不正确!");
return;
}
new MessageBox(Page).ShowAndJump("已经成功" + "导入" + (count > 0 ? count : 0) + "条数据！!", "FileEnterList.aspx");
//操作日志
LogListDal.Insert(DateTime.Now, "" + getSheetName + "导入数据", LoginUser.GetUserId, LoginUser.GetUserName);
}
/// <summary>
///删除指定一行数据信息
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
public int Delete(params object[] values)
{
return(TSQLServer.ExecuteNonQuery("delete [SaveDeadline] where id =" + values[0]));
}
/// <summary>
/// 删除指定多行数据信息
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
public int DeleteAllIn(string values)
{
return(TSQLServer.ExecuteNonQuery("delete [FileDirectory] where id in(" + values + ")"));
}
/// <summary>
/// 返回一个DataTable数据集合用户登录
/// </summary>
/// <param name="strwhere">传递参数为用户名和密码</param>
/// <returns>返回一个DataTable</returns>
public DataTable GetUserDataTable(string strwhere)
{
return(TSQLServer.ExecDt(" select * from UserList us inner join QXMCB qx on us.userqxdm=qx.qxdm where 1=1 " + strwhere + ""));
}
//@tbname varchar(255), --表名
//@Fields varchar(1000)='*', --返回字段
//@OrderField varchar(255), --排序的字段名
//@PageSize int=15, --页尺寸
//@PageCurrent int=1, --页码
//@OrderType bit=0, --排序类型,1是升序,0是降序
//@Where varchar(1500)= '', --查询条件
//@TotalPage int output --返回总记录数
public static PeterPages GetPageLists(PageInfoNew pageinfo)
{
SqlParameter[] Parameter = new SqlParameter[8];
Parameter[0] = new SqlParameter();
Parameter[0].DbType = DbType.String;
Parameter[0].ParameterName = "@tbname";
Parameter[0].Value = pageinfo.Tablename;
Parameter[0].Direction = ParameterDirection.Input;
Parameter[1] = new SqlParameter();
Parameter[1].DbType = DbType.String;
Parameter[1].Size = 1000;
Parameter[1].ParameterName = "@FieldShow";
Parameter[1].Value = pageinfo.Fields;
Parameter[1].Direction = ParameterDirection.Input;
Parameter[2] = new SqlParameter();
Parameter[2].DbType = DbType.String;
Parameter[2].Size = 1500;
Parameter[2].ParameterName = "@Where";
Parameter[2].Value = pageinfo.Sqlwhere;
Parameter[2].Direction = ParameterDirection.Input;
Parameter[3] = new SqlParameter();
Parameter[3].DbType = DbType.Int16;
Parameter[3].ParameterName = "@PageSize";
Parameter[3].Value = pageinfo.PageSize;
Parameter[3].Direction = ParameterDirection.Input;
Parameter[4] = new SqlParameter();
Parameter[4].DbType = DbType.Int16;
Parameter[4].ParameterName = "@PageCurrent";
Parameter[4].Value = pageinfo.PageIndex;
Parameter[4].Direction = ParameterDirection.Input;
Parameter[5] = new SqlParameter();
Parameter[5].DbType = DbType.String;
Parameter[5].ParameterName = "@FieldKey";
Parameter[5].Value = pageinfo.Fieldkey;
Parameter[5].Direction = ParameterDirection.Input;
Parameter[6] = new SqlParameter();
Parameter[6].DbType = DbType.String;
Parameter[6].Size = 50;
Parameter[6].ParameterName = "@FieldOrder";
Parameter[6].Value = pageinfo.Orderfield;
Parameter[6].Direction = ParameterDirection.Input;
Parameter[7] = new SqlParameter();
Parameter[7].DbType = DbType.Int32;
Parameter[7].Size = 200;
Parameter[7].ParameterName = "@RecordCount";
Parameter[7].Direction = ParameterDirection.Output;
Parameter[7].Value = 0;
//存储过程sp_PageView进行分页
DataSet ds = TSQLServer.RunProcedure("sp_PageView", Parameter, "pages");
Int32 recordcount = (Int32)Parameter[7].Value;
PeterPages pages = new PeterPages(pageinfo.PageSize, pageinfo.PageIndex, recordcount, ds);
return(pages);
}
/// <summary>
/// 返回一个DataRow数据集合
/// </summary>
/// <param name="values">传递参数为id</param>
/// <returns>返回一个DataRow</returns>
public DataRow GetRow(params object[] values)
{
return(TSQLServer.ExecDr("select * from [Area] where id =" + values[0] + ""));
}
/// <summary>
///删除指定一行数据信息
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
public int Delete(params object[] values)
{
return(TSQLServer.ExecuteNonQuery("delete [PunishType] where id =" + values[0] + ""));
}
/// <summary>
///删除指定一行数据信息
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
public int Delete(params object[] values)
{
return(TSQLServer.ExecuteNonQuery("delete [UserList] where id =" + values[0]));
}
/// <summary>
/// 返回一个DataTable数据集合用户登录
/// </summary>
/// <param name="strwhere">传递参数为用户名和密码</param>
/// <returns>返回一个DataTable</returns>
public DataTable GetUserLogin(string strusername, string struserpwd)
{
return(TSQLServer.ExecDt("select * from [UserList] where 1=1 and userName='******' and passWord='******' "));
}
/// <summary>
/// 删除指定多行数据信息
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
public int DeleteAllIn(string values)
{
return(TSQLServer.ExecuteNonQuery("delete [FileLibrary] where ID in(" + values + ")"));
}
/// <summary>
/// 返回一个DataTable数据集合
/// </summary>
/// <param name="strwhere">传递参数为id</param>
/// <returns>返回一个DataTable</returns>
public DataTable GetDataTable(string strwhere)
{
return(TSQLServer.ExecDt("select * from [Area] where 1=1 " + strwhere));
}
/// <summary>
/// 用户登录
/// </summary>
/// <param name="strwhere">传递参数为用户名和密码</param>
/// <returns>返回一个DataTable</returns>
public DataTable GetUserLoginInfor(string strusername, string struserpwd)
{
return(TSQLServer.ExecDt("select us.id,us.userName,us.passWord,us.userqxdm,us.userPost,og.id as ogid,og.OrganizerName,us.userState from [UserList] us inner join [Organizer] og on us.userqxdm=og.OrganizerQxdm where 1=1 and us.userName='******' and us.passWord='******' "));
}
