private String usersummarypop(string proc, string testcode, int nUnit)
{
BLL_UserInfo BLL = new BLL_UserInfo();
testcode = GetSelectTree(testcode, SelectedTestRoomCodes);
DataTable dt = BLL.GetProcDataTableChartsPara5(proc, StartDate, DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"), testcode, nUnit);
if (dt != null)
{
List <ChartModel> list = new List <ChartModel>();
foreach (DataRow dr in dt.Rows)
{
ChartModel trcs = new ChartModel();
trcs.Description = dr["testname"].ToString();
trcs.IntNumber = Int32.Parse(dr["ncount"].ToString());
trcs.Para1 = dr["Testcode"].ToString();
trcs.Para2 = dr["Companycode"].ToString();
trcs.Para3 = dr["company"].ToString();
trcs.IntNumberMarks = nUnit;
list.Add(trcs);
}
return(JsonConvert.SerializeObject(list));
}
else
{
return("");
}
}
/// <summary>
/// 人员情况
/// </summary>
/// <returns></returns>
public DataTable RYQK()
{
BLL_UserInfo BLL = new BLL_UserInfo();
DataTable dt = BLL.GetProcDataTable("spweb_userSummary", StartDate, DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"), SelectedTestRoomCodes, 0, 1, 100000, "标段名称", "ASC", out pageCount, out records);
dt.Columns.Remove("id");
dt.Columns.Remove("userid");
dt.Columns.Remove("试验室编码");
dt.Columns.Remove("num");
return(dt);
}
private String usersummarycharttogrid(String name, String fileds, String sNuit, String sModel, String sTestcode, String sClassname, String key, int ftype, out int pageCount, out int records)
{
String sqlwhere = "";
if (sNuit == "1")
{
sqlwhere = "and 单位编码='" + sModel + "' AND 试验室编码='" + sTestcode + "'";
}
if (sNuit == "2")
{
sqlwhere = "and 单位编码='" + sModel + "' AND 技术职称='" + sClassname + "'";
}
if (sNuit == "3")
{
sqlwhere = "and 单位编码='" + sModel + "' AND 学历='" + sClassname + "'";
}
if (sNuit == "4")
{
sqlwhere = "and 单位编码='" + sModel + "'";
if (sClassname == "1-5年")
{
sqlwhere += "and 工作年限>=1 and 工作年限<5 ";
}
if (sClassname == "5-10年")
{
sqlwhere += "and 工作年限>=5 and 工作年限<10 ";
}
if (sClassname == "10-15年")
{
sqlwhere += "and 工作年限>=10 and 工作年限<15 ";
}
if (sClassname == "15-20年")
{
sqlwhere += "and 工作年限>=15 and 工作年限<20 ";
}
if (sClassname == "20年以上")
{
sqlwhere += "and 工作年限>=20 ";
}
}
BLL_UserInfo BLL = new BLL_UserInfo();
DataTable dt = BLL.GetDataTablePager(name, fileds, sqlwhere, key, OrderField, OrderType, PageIndex, PageSize, out pageCount, out records);
if (dt != null)
{
return(JsonConvert.SerializeObject(dt));
}
else
{
return("");
}
}
private String usersummary(string proc, int ntype)
{
BLL_UserInfo BLL = new BLL_UserInfo();
DataTable dt = BLL.GetProcDataTableChartsPara5(proc, StartDate, DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"), SelectedTestRoomCodes, ntype);
if (dt != null)
{
List <ChartModel> list = new List <ChartModel>();
foreach (DataRow dr in dt.Rows)
{
ChartModel trcs = new ChartModel();
trcs.Description = dr["segment"].ToString() + "\n" + dr["companyname"].ToString();
trcs.IntNumber = Int32.Parse(dr["ncount"].ToString());
trcs.Para1 = dr["companycode"].ToString();
list.Add(trcs);
}
return(JsonConvert.SerializeObject(list));
}
else
{
return("");
}
}
/// <summary>
/// 人员
/// </summary>
/// <param name="proc"></param>
/// <param name="ftype"></param>
/// <param name="pageCount"></param>
/// <param name="records"></param>
/// <returns></returns>
private String usersummary(String proc, int ftype, out int pageCount, out int records)
{
BLL_UserInfo BLL = new BLL_UserInfo();
string sqlwhere = " AND 1=1 ";
if (!"NUM".RequestStr().IsNullOrEmpty())
{
sqlwhere += " and 试验室编码 in ('" + "NUM".RequestStr() + "') ";
}
else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and 试验室编码 in (" + SelectedTestRoomCodes + ") ";
}
//sqlwhere += "and CreatedTime>='" + StartDate + "' AND CreatedTime<'" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
#region For首页
if (!string.IsNullOrEmpty("RPNAME".RequestStr()))
{
switch ("RPNAME".RequestStr())
{
case "ADD": //新增
sqlwhere += " AND CreatedTime between '" + StartDate + "' and '" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' AND Status>0 ";
break;
case "DEL": //调减
sqlwhere += " AND CreatedTime between '" + StartDate + "' and '" + DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' AND Status=0 ";
break;
default:
sqlwhere += " AND Status>0 ";
break;
}
}
else
{
sqlwhere += " AND Status>0 ";
}
#endregion
#region 使用脚本分页
string Sql = @"
DECLARE @Page int
DECLARE @PageSize int
SET @Page = {1}
SET @PageSize = {2}
SET NOCOUNT ON
DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(50))
INSERT INTO @TempTable
(
_keyID
)
SELECT ID
FROM dbo.sys_document a JOIN dbo.v_bs_codeName b
ON a.ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'
{0}
AND a.TestRoomCode=b.试验室编码
JOIN dbo.Sys_Tree c ON LEFT(a.TestRoomCode,12)=c.NodeCode
SELECT ID,TestRoomCode,b.标段名称,b.单位名称, b.试验室名称
,Ext1 姓名,Ext2 性别,Ext3 年龄,Ext4 技术职称,Ext5 职务,Ext6 工作年限,Ext7 联系电话,Ext8 学历,Ext9 毕业学校,Ext10 专业,1 num
FROM dbo.sys_document a JOIN dbo.v_bs_codeName b
ON a.ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'
{0}
AND a.TestRoomCode=b.试验室编码
JOIN dbo.Sys_Tree c ON LEFT(a.TestRoomCode,12)=c.NodeCode
INNER JOIN @TempTable t ON a.ID = t._keyID
WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
Order By OrderID,TestRoomCode ASC
DECLARE @C int
select @C= count(ID) from dbo.sys_document a JOIN dbo.v_bs_codeName b
ON a.ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'
{0}
AND a.TestRoomCode=b.试验室编码
JOIN dbo.Sys_Tree c ON LEFT(a.TestRoomCode,12)=c.NodeCode
select @C
";
Sql = string.Format(Sql, sqlwhere, PageIndex, PageSize);
//DataSet DS = BLL.GetDataSet(Sql);
DataSet DSs = new DataSet();
using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
{
Conn.Open();
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
{
using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
{
Adp.Fill(DSs);
}
}
Conn.Close();
}
decimal Tempc = Math.Round(decimal.Parse(DSs.Tables[1].Rows[0][0].ToString()) / decimal.Parse(PageSize.ToString()), 2);
Tempc = Math.Ceiling(Tempc);
records = DSs.Tables[1].Rows[0][0].ToString().Toint();
pageCount = Tempc.ToString().Toint();
#endregion
//未使用
//DataTable dt = BLL.GetProcDataTable(proc, StartDate, DateTime.Parse(EndDate).AddDays(1).ToString("yyyy-MM-dd"), SelectedTestRoomCodes, ftype, PageIndex, PageSize, OrderField, OrderType, out pageCount, out records);
if (DSs.Tables[0] != null)
{
return(JsonConvert.SerializeObject(DSs.Tables[0]));
}
else
{
return("");
}
}
public void List()
{
string StartDate = "StartDate".RequestStr();
string EndDate = "EndDate".RequestStr().ToDateTime().ToString("yyyy-MM-dd");
string PageIndex = "page".RequestStr();
string PageSize = "rows".RequestStr();
string zglg = "zglg".RequestStr();
BLL_UserInfo BLL = new BLL_UserInfo();
string sqlwhere = " AND 1=1 ";
if (!"NUM".RequestStr().IsNullOrEmpty())
{
sqlwhere += " and 试验室编码 in ('" + "NUM".RequestStr() + "') ";
}
else if (!String.IsNullOrEmpty(SelectedTestRoomCodes))
{
sqlwhere += " and 试验室编码 in (" + SelectedTestRoomCodes + ") ";
}
//Ext21 到岗日 Ext22 离岗日期
if (zglg.Contains("1"))
{
sqlwhere += " AND Status>0 ";
}
if (zglg.Contains("0"))
{
sqlwhere += " AND Status=0 ";//(Ext22 <> '' OR Ext22 is not null ) AND
}
#region For首页
if (!string.IsNullOrEmpty("RPNAME".RequestStr()))
{
switch ("RPNAME".RequestStr())
{
case "ADD": //新增
sqlwhere += " AND CreatedTime between '" + StartDate + "' and '" + EndDate + "' AND Status>0 ";
break;
case "DEL": //调减
sqlwhere += " AND CreatedTime between '" + StartDate + "' and '" + EndDate + "' AND Status=0 ";
break;
default:
sqlwhere += " AND Status>0 ";
break;
}
}
#endregion
#region 使用脚本分页
string Sql = @"
DECLARE @Page int
DECLARE @PageSize int
SET @Page = {1}
SET @PageSize = {2}
SET NOCOUNT ON
DECLARE @TempTable TABLE (IndexId int identity, _keyID varchar(50))
INSERT INTO @TempTable
(
_keyID
)
SELECT ID
FROM dbo.sys_document a JOIN dbo.v_bs_codeName b
ON a.ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'
{0}
AND a.TestRoomCode=b.试验室编码
JOIN dbo.Sys_Tree c ON LEFT(a.TestRoomCode,12)=c.NodeCode
Order By c.OrderID ASC
SELECT ID,TestRoomCode,b.标段名称,b.单位名称, b.试验室名称
,Ext1 姓名,Ext2 性别,Ext3 年龄,Ext4 技术职称,Ext5 职务,Ext6 工作年限,Ext7 联系电话,Ext8 学历,Ext9 毕业学校,Ext10 专业,1 num
FROM dbo.sys_document a JOIN dbo.v_bs_codeName b
ON a.ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'
{0}
AND a.TestRoomCode=b.试验室编码
JOIN dbo.Sys_Tree c ON LEFT(a.TestRoomCode,12)=c.NodeCode
INNER JOIN @TempTable t ON a.ID = t._keyID
WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)
Order By OrderID,TestRoomCode ASC
DECLARE @C int
select @C= count(ID) from dbo.sys_document a JOIN dbo.v_bs_codeName b
ON a.ModuleID='08899BA2-CC88-403E-9182-3EF73F5FB0CE'
{0}
AND a.TestRoomCode=b.试验室编码
JOIN dbo.Sys_Tree c ON LEFT(a.TestRoomCode,12)=c.NodeCode
select @C
";
Sql = string.Format(Sql, sqlwhere, PageIndex, PageSize);
//DataSet DS = BLL.GetDataSet(Sql);
DataSet DSs = new DataSet();
using (System.Data.SqlClient.SqlConnection Conn = BLL.Connection as System.Data.SqlClient.SqlConnection)
{
Conn.Open();
using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(Sql, Conn))
{
using (System.Data.SqlClient.SqlDataAdapter Adp = new System.Data.SqlClient.SqlDataAdapter(Cmd))
{
Adp.Fill(DSs);
}
}
Conn.Close();
}
decimal Tempc = Math.Round(decimal.Parse(DSs.Tables[1].Rows[0][0].ToString()) / decimal.Parse(PageSize.ToString()), 2);
Tempc = Math.Ceiling(Tempc);
int records = DSs.Tables[1].Rows[0][0].ToString().Toint();
int pageCount = Tempc.ToString().Toint();
#endregion
string result = "{\"total\": \"" + pageCount + "\", \"page\": \"" + PageIndex.ToString() + "\", \"records\": \"" + records + "\", \"rows\" : " + Newtonsoft.Json.JsonConvert.SerializeObject(DSs.Tables[0]) + "}";
if (DSs.Tables[0] != null)
{
Response.Write(result);
Response.End();
}
else
{
Response.Write("");
Response.End();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
String id = Request.Params["id"];
if (!String.IsNullOrEmpty(id))
{
sys_line sysBaseLine = System.Web.HttpContext.Current.Session["SysBaseLine"] as sys_line;
if (sysBaseLine.IsActive == 0)
{
#region
BLL_UserInfo bll = new BLL_UserInfo();
DataTable dt = bll.GetUserInfo(id);
if (dt != null && dt.Rows.Count > 0)
{
lbl_xingming.Text = dt.Rows[0]["col_norm_D6"].ToString();
lbl_xingbie.Text = dt.Rows[0]["col_norm_G6"].ToString();
lbl_nianling.Text = dt.Rows[0]["col_norm_K6"].ToString();
lbl_jishuzhicheng.Text = dt.Rows[0]["col_norm_D7"].ToString();
lbl_zhiwu.Text = dt.Rows[0]["col_norm_G7"].ToString();
lbl_gongzuonianxian.Text = dt.Rows[0]["col_norm_K7"].ToString();
lbl_zhuanye.Text = dt.Rows[0]["col_norm_D8"].ToString();
lbl_xueli.Text = dt.Rows[0]["col_norm_G8"].ToString();
try
{
lbl_biyeshijian.Text = Convert.ToDateTime(dt.Rows[0]["col_norm_K8"].ToString()).ToShortDateString();
}
catch
{
lbl_biyeshijian.Text = dt.Rows[0]["col_norm_K8"].ToString();
}
lbl_lianxidianhua.Text = dt.Rows[0]["col_norm_D9"].ToString();
lbl_biyexuexiao.Text = dt.Rows[0]["col_norm_G9"].ToString();
//dt.Rows[0]["col_norm_L6"].ToString()
#region
StringBuilder sb1 = new StringBuilder();
if (!string.IsNullOrEmpty(dt.Rows[0]["col_norm_L6"].ToString()))
{
string pathname = dt.Rows[0]["ID"].ToString();
string fullPath = Server.MapPath("../userphoto/ ") + pathname + ".jpg";
if (!File.Exists(fullPath))
{
object o = dt.Rows[0]["col_norm_L6"];
var _tempMemoryStream = new MemoryStream((byte[])o);
System.Drawing.Image img = System.Drawing.Image.FromStream(_tempMemoryStream);
img.Save(fullPath);
_tempMemoryStream.Close();
}
else
{
}
sb1.Append("<img width=\"130px\" height=\"165px\" src=\"../userphoto/" + pathname + ".jpg\" />");
}
else
{
sb1.Append("<img width=\"130px\" height=\"165px\" src=\"../images/nohead.png\" />");
}
Literal3.Text = sb1.ToString();
#endregion
#region
if (!String.IsNullOrEmpty(dt.Rows[0]["col_norm_B14"].ToString()))
{
StringBuilder sb = new StringBuilder();
for (int i = 14; i < 28; i++)
{
if (!String.IsNullOrEmpty(dt.Rows[0]["col_norm_B" + i + ""].ToString()))
{
sb.Append("<tr><td colspan=\"2\">" + dt.Rows[0]["col_norm_B" + i + ""].ToString() + "</td>");
sb.Append("<td colspan=\"3\">" + dt.Rows[0]["col_norm_E" + i + ""].ToString() + "</td>");
sb.Append("<td>" + dt.Rows[0]["col_norm_K" + i + ""].ToString() + "</td>");
sb.Append("<td>" + dt.Rows[0]["col_norm_M" + i + ""].ToString() + "</td></tr>");
}
}
Literal1.Text = sb.ToString();
}
else
{
tr_gzjl.Style["display"] = "none";
tr_gzjl_mx.Style["display"] = "none";
}
if (!String.IsNullOrEmpty(dt.Rows[0]["col_norm_B31"].ToString()))
{
StringBuilder sb = new StringBuilder();
for (int i = 31; i < 37; i++)
{
if (!String.IsNullOrEmpty(dt.Rows[0]["col_norm_B" + i + ""].ToString()))
{
sb.Append("<tr><td colspan=\"2\">" + dt.Rows[0]["col_norm_B" + i + ""].ToString() + "</td>");
sb.Append("<td colspan=\"4\">" + dt.Rows[0]["col_norm_E" + i + ""].ToString() + "</td>");
try
{
string[] Temp = Newtonsoft.Json.JsonConvert.DeserializeObject <string[]>(dt.Rows[0]["col_norm_K" + i + ""].ToString());
sb.Append("<td>" + Temp[0] + "<img src='data:image/gif;base64," + Temp[1] + "' /></td></tr>");
}
catch
{
sb.Append("<td>" + dt.Rows[0]["col_norm_K" + i + ""].ToString() + "' /></td></tr>");
}
}
}
Literal2.Text = sb.ToString();
}
else
{
tr_pxjl.Style["display"] = "none";
tr_pxjl_mx.Style["display"] = "none";
}
#endregion
}
#endregion
}
else
{
string SQL = "SELECT * FROM dbo.sys_document WHERE ID='" + id + "'";
DataSet ds = DbHelperSQL.Query(SQL);
string str = ds.Tables[0].Rows[0]["Data"].ToString();
JZDocument doc = Newtonsoft.Json.JsonConvert.DeserializeObject <JZDocument>(str);
if (doc != null)
{
#region
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "D6") != null)
{
lbl_xingming.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "D6").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "G6") != null)
{
lbl_xingbie.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "G6").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K6") != null)
{
lbl_nianling.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K6").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "D7") != null)
{
lbl_jishuzhicheng.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "D7").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "G7") != null)
{
lbl_zhiwu.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "G7").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K7") != null)
{
lbl_gongzuonianxian.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K7").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "D8") != null)
{
lbl_zhuanye.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "D8").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "G8") != null)
{
lbl_xueli.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "G8").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K8") != null)
{
lbl_biyeshijian.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K8").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "D9") != null)
{
lbl_lianxidianhua.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "D9").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "G9") != null)
{
lbl_biyexuexiao.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "G9").ToString();
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "D10") != null)
{
lbl_idcard.Text = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "D10").ToString();
}
#endregion
#region
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "B14") != null)
{
StringBuilder sb = new StringBuilder();
for (int i = 14; i < 28; i++)
{
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "B" + i + "") != null)
{
sb.Append("<tr><td colspan=\"2\">" + JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "B" + i + "").ToString() + "</td>");
}
else
{
sb.Append("<tr><td colspan=\"2\"></td>");
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "E" + i + "") != null)
{
sb.Append("<td colspan=\"3\">" + JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "E" + i + "").ToString() + "</td>");
}
else
{
sb.Append("<td colspan=\"3\"></td>");
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K" + i + "") != null)
{
sb.Append("<td>" + JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K" + i + "").ToString() + "</td>");
}
else
{
sb.Append("<td></td>");
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "M" + i + "") != null)
{
sb.Append("<td>" + JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "M" + i + "").ToString() + "</td></tr>");
}
else
{
sb.Append("<td></td></tr>");
}
}
Literal1.Text = sb.ToString();
}
else
{
tr_gzjl.Style["display"] = "none";
tr_gzjl_mx.Style["display"] = "none";
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "B31") != null)
{
StringBuilder sb = new StringBuilder();
for (int i = 31; i < 37; i++)
{
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "B" + i + "") != null)
{
sb.Append("<tr><td colspan=\"2\">" + JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "B" + i + "").ToString() + "</td>");
}
else
{
sb.Append("<tr><td colspan=\"2\"></td>");
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "E" + i + "") != null)
{
sb.Append("<td colspan=\"4\">" + JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "E" + i + "").ToString() + "</td>");
}
else
{
sb.Append("<td colspan=\"4\"></td>");
}
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K" + i + "") != null)
{
try
{
string[] Temp = Newtonsoft.Json.JsonConvert.DeserializeObject <string[]>(JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K" + i + "").ToString());
System.Drawing.Image img = BizCommon.JZCommonHelper.StringToBitmap(Temp[1]);
sb.Append("<td>" + Temp[0] + " <a title=\"点击查看大图\" href=\"javascript:Show('" + id + "','" + doc.Sheets[0].ID.ToString() + "'," + i + ")\"><img src='data:image/gif;base64," + Temp[1] + "' height=\"30px;\" /></a></td>");
}
catch
{
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K" + i + "").ToString() != "[null,null,null]")
{
sb.Append("<td>" + JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "K" + i + "").ToString() + "</td>");
}
else
{
sb.Append("<td></td>");
}
}
}
else
{
sb.Append("<td></td>");
}
}
Literal2.Text = sb.ToString();
}
else
{
tr_pxjl.Style["display"] = "none";
tr_pxjl_mx.Style["display"] = "none";
}
#endregion
#region
StringBuilder sb1 = new StringBuilder();
if (JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "L6") != null)
{
string pathname = id;
string fullPath = Server.MapPath("../userphoto/ ") + pathname + ".jpg";
if (!File.Exists(fullPath))
{
object o = JZCommonHelper.GetCellValue(doc, doc.Sheets[0].ID, "L6");
System.Drawing.Image img = null;
byte[] bitmapBytes = System.Convert.FromBase64String(o.ToString());
using (MemoryStream memoryStream = new MemoryStream(bitmapBytes))
{
img = System.Drawing.Image.FromStream(memoryStream);
img.Save(fullPath);
memoryStream.Close();
}
}
//else
//{
// sb1.Append("<img width=\"130px\" height=\"165px\" src=\"../images/nohead.png\" />");
//}
sb1.Append("<img width=\"130px\" height=\"165px\" src=\"../userphoto/" + pathname + ".jpg\" />");
}
else
{
sb1.Append("<img width=\"130px\" height=\"165px\" src=\"../images/nohead.png\" />");
}
Literal3.Text = sb1.ToString();
#endregion
}
}
}
}
}
static void Main(string[] args)
{
//查询
Stopwatch swSearch = new Stopwatch();
swSearch.Start();
string sql = "select * from UserInfo";
var list = DapperSql.GetInfoList <UserInfo>(sql);
swSearch.Stop();
TimeSpan tsSearch = swSearch.Elapsed;
Console.WriteLine("查询整表总共花费{0}ms.", tsSearch.TotalMilliseconds);
//分页查询
Stopwatch swPage = new Stopwatch();
swPage.Start();
string sqlPage = "select * from UserInfo";
var listPage = DapperSql.GetPageList <UserInfo>(sqlPage, "order by ID", 3, 10);
swPage.Stop();
TimeSpan tsPage = swPage.Elapsed;
Console.WriteLine("分页查询总共花费{0}ms.", tsPage.TotalMilliseconds);
//添加
string sqlInsert = @"INSERT INTO [UserInfo]
([username]
,[email]
,[sex]
,[city]
,[sign]
,[experience]
,[ip]
,[logins]
,[joinTime])
VALUES
(@username
,@email
,@sex
,@city
,@sign
,@experience
,@ip
,@logins
,@joinTime)";
for (int i = 0; i < 20; i++)
{
var success = DapperSql.UpdateSql(sqlInsert, new UserInfo {
Username = "******", Email = "*****@*****.**", City = "重庆", Sign = "en"
});
Console.WriteLine(success);
}
//删除
string sqlDelete = "delete UserInfo where ID=@ID";
var successDel = DapperSql.UpdateSql(sqlDelete, new { ID = 123 });
//修改
for (int i = 50; i < 100; i++)
{
string sqlUpdate = "update UserInfo set username=@username where ID=@ID";
var successUpdate = DapperSql.UpdateSql(sqlUpdate, new { ID = i, username = "******" + i });
Console.WriteLine(successUpdate);
}
//批量增加
Stopwatch sw = new Stopwatch();
sw.Start();
List <UserInfo> listUser = new List <UserInfo>();
for (int i = 0; i < 1000000; i++)
{
listUser.Add(new UserInfo {
Username = "******", Email = "*****@*****.**", Sex = "1", City = "重庆", Sign = "en"
});
}
var s_1 = DapperSql.UpdateSql(sqlInsert, listUser);
Console.WriteLine(s_1);
sw.Stop();
TimeSpan ts2 = sw.Elapsed;
Console.WriteLine("批量新增总共花费{0}ms.", ts2.TotalMilliseconds);
//批量修改
Stopwatch sw1 = new Stopwatch();
sw1.Start();
var list_Update = DapperSql.GetInfoList <UserInfo>("select * from UserInfo where ID>0 and ID<60").ToList();
List <UserInfo> listExecUpdate = new List <UserInfo>();
for (int i = 0; i < list_Update.Count; i++)
{
UserInfo t = list_Update[i];
t.Username = "******";
listExecUpdate.Add(t);
}
string sqlUpdates = @"UPDATE [UserInfo]
SET [username] = @username
,[email] = @email
,[sex] = @sex
,[city] = @city
,[sign] = @sign
,[experience] = @experience
,[ip] = @ip
,[logins] = @logins
,[joinTime] = @joinTime where ID=@ID";
var s = DapperSql.UpdateSql(sqlUpdates, listExecUpdate);
sw1.Stop();
TimeSpan ts3 = sw1.Elapsed;
Console.WriteLine("批量修改总共花费{0}ms.", ts3.TotalMilliseconds);
//dapper实体增删改查方法，原生代码使用
using (SqlConnection cn = DapperSql.getCon())
{
cn.Open();
IList <ISort> sort = new List <ISort>();
sort.Add(new Sort {
PropertyName = "ID", Ascending = false
});
var person = cn.GetPage <UserInfo>(null, sort, 1, 10).ToList().ToList(); //分页查询
var person1 = person.Select(e => new { e.ID, e.Username }); //分页查询查询某列
var single = cn.Get <UserInfo>(1); //获取单个实体数据
cn.Insert <UserInfo>(new UserInfo {
Username = "******", Email = "*****@*****.**", Sex = "1", City = "重庆", Sign = "en"
}); //新增
cn.Update <UserInfo>(new UserInfo {
ID = 2, Username = "******"
}); //修改
cn.Delete <UserInfo>(new { ID = 12 }); //删除
cn.Close();
}
var dbset01 = ContextFactory.CreateDbSet <UserInfo>();
var listAll = dbset01.GetAll().ToList();
//多表事物操作
using (var tranScope = new TransactionScope())
{
try
{
UserInfo obj = new UserInfo {
Username = "******"
};
var list01 = dbset01.Insert(obj);
tranScope.Complete();
}
catch (Exception ex)
{
//任务出差 就不会提交 自动回滚
throw ex;
}
}
//Action BookAction = new Action(insert);
//var suc = ContextFactory.Submit(BookAction);
//dapper实体操作，封装代码调用
BLL_UserInfo bll = new BLL_UserInfo();
bll.Test();
return;
//var usr = DapperSql.GetById<UserInfo>(1);
}
