/// <summary> /// 获取最近一小时站点信息 /// </summary> /// <returns></returns> public static DataTable getOnetimegas() { DAL dal = new DAL(); string gas = ""; DataTable dt = new DataTable(); dt.Columns.Add("站点"); dt.Columns.Add("销售气量"); dt.Columns.Add("当班班组"); int n = 0; for (int i = 0; i < 2; i++)//循环3次,统计3个站点数据 { DataRow row = dt.NewRow(); n = i + 2; if (n == 2) { row["站点"] = "南高"; } if (n == 3) { row["站点"] = "西路"; } if (n == 4) { row["站点"] = "麻柳沱"; } gas = dal.RunSqlGetID("SELECT sum([NowGas]) as 气量 FROM [CSERVER].[dbo].[tempGAS] where StationNO =" + n.ToString() + " and TradeDate > dateadd(hh,-1,getdate()) "); if (gas == null) { gas = "0"; } row["销售气量"] = gas; row["当班班组"] = dal.RunSqlGetID("SELECT top 1 [BZNO] FROM [CSERVER].[dbo].[DateSum] where StationNO=" + n.ToString() + " ORDER BY SxBTime DESC"); dt.Rows.Add(row); } return dt; }
/// <summary> /// 上一笔定购单 /// </summary> /// <param name="context"></param> public void CG_last(HttpContext context) { string retext = ""; string code = context.Request["code"]; string SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_采购订单] order by 单号"; DAL dal = new DAL(); string min = dal.RunSqlGetID(SQL); if (code == min) { retext = "{\"err\":{\"ISok\":false,\"msg\":\"已经是第一笔订单!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}"; context.Response.Write(retext); return; } SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_采购订单] where 单号<'" + code + "' order by 单号 desc"; code = dal.RunSqlGetID(SQL); retext = CG_json(code); context.Response.Write(retext); }
protected void Page_Load(object sender, EventArgs e) { DAL dal = new DAL(); string SQL = "SELECT TOP 1 convert(varchar(20),[时间],120) as 时间 FROM [CNGTZ].[dbo].[TZ_人员_考勤处理时间] ORDER BY 时间 DESC"; string val = dal.RunSqlGetID(SQL); if (val == null) { val = ""; } cxdate = val; }
//通过 日期 找订单数据 public void CG_date(HttpContext context) { string retext = "{\"err\":{\"ISok\":true,\"msg\":\"未找到订单数据!\"},\"data\":订单数据对象,\"minxi\":明细对象}"; string date = context.Request["date"]; string SQL = "SELECT TOP 1 [单号] FROM [CNGCL].[dbo].[CL_采购订单] where 制单日期>='" + date + "' ORDER BY 单号"; DAL dal = new DAL(); string code = dal.RunSqlGetID(SQL); if (code == null) { retext = "{\"err\":{\"ISok\":false,\"msg\":\"未找到订单数据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}"; context.Response.Write(retext); return; } retext = CG_json(code); context.Response.Write(retext); }
/// <summary> /// 验证用户签名 /// </summary> /// <param name="user">用户名</param> /// <param name="pw">签名密码</param> /// <returns></returns> public static bool UserQianMing(string user, string pw) { //用参数传入方式,可以防SQL注入 string sql = "SELECT [权限] FROM [WebApp].[dbo].[DL_用户名] where 用户名=@参数一 and 签名密码=@参数二"; SqlParameter par1 = new SqlParameter("@参数一", user.Trim()); SqlParameter par2 = new SqlParameter("@参数二", Sys.getMd5(pw.Trim())); SqlParameter[] par = { par1, par2 }; DAL dal = new DAL(); string qx = dal.RunSqlGetID(sql, par); if (qx == null) { qx = ""; return (false); } else { return (true); } }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; context.Response.Charset = "utf-8"; var file = context.Request.Files["Filedata"];//文件 对象 string userID = context.Request["userID"];//上传用户 string user = context.Request["user"];//上传用户 string imgname = context.Request["imgname"];//图片名 string imgID = context.Request["imgID"];//相片ID 如果为0 表示 没有上传记录 string savePaht = context.Request.PhysicalApplicationPath + "CNGTZ\\userimg\\" + imgname + ".jpg"; //略缩图储存路径 string sourcePaht = context.Request.PhysicalApplicationPath + "CNGTZ\\userimg\\source\\" + imgname + ".jpg"; //原图储存路径 //string xnpath =context.Request.Path; DAL dal = new DAL(); if (file != null) { file.SaveAs(sourcePaht); ImageClass.MakeThumbnail(sourcePaht, savePaht, 100, 130, "HW");//生成略缩图 FileItem fi = FileManager.GetItemInfo(savePaht); if (fi.Name.Length > 0)//判断略缩图是否创建完成 { string SQL1 = "", SQL2 = "", SQL3 = ""; if (imgID != "0") { SQL1 = "UPDATE [CNGTZ].[dbo].[TZ_图片]SET [类型] =@类型,[图片] =@图片,[备注] =@备注,[上传用户] =@上传用户,[所属用户] =@所属用户,[更新时间] = getdate() WHERE ID=" + imgID; } else { SQL1 = @"INSERT INTO [CNGTZ].[dbo].[TZ_图片]([类型],[图片],[备注],[上传用户],[所属用户],[更新时间])VALUES(@类型,@图片,@备注,@上传用户,@所属用户,getdate())"; SQL2 = @"SELECT [ID] FROM [CNGTZ].[dbo].[TZ_图片]where 类型='人员' AND [所属用户]='" + imgname + "'";//查询是否已经上传过相片 SQL3 = @"UPDATE [CNGTZ].[dbo].[TZ_人员_基本信息] SET [相片ID] ={0}, 更新时间 = getdate() WHERE ID=" + userID; } try { dal.TranBegin();//开始事务 SqlParameter par1 = new SqlParameter("@类型", "人员"); SqlParameter par2 = new SqlParameter("@图片", savePaht); SqlParameter par3 = new SqlParameter("@备注", ""); SqlParameter par4 = new SqlParameter("@上传用户", user); SqlParameter par5 = new SqlParameter("@所属用户", imgname); dal.ExeSql(SQL1, new SqlParameter[] { par1, par2, par3, par4, par5 });//执行语句 if (imgID == "0") { string ID = dal.RunSqlGetID(SQL2); SQL3 = string.Format(SQL3, ID); dal.ExeSql(SQL3); } dal.TranCommit();//提交事务 Sys.addSteps(user, "上传员工相片:[" + fi.Name + "]", "人员台账"); //下面这句代码缺少的话,上传成功后上传队列的显示不会自动消失 context.Response.Write("1"); } catch (Exception) { dal.TranRollBack();//回滚事务 context.Response.Write("0");//数据库操作失败 } } else { context.Response.Write("0"); //如果创建文件失败 } } else { context.Response.Write("0");//上传文件失败 } }
/// <summary> /// 源图片获取 /// </summary> /// <param name="context"></param> public void Img_getsource(HttpContext context) { string ID = context.Request["ID"];//图片ID DAL dal = new DAL(); string fiPaht = dal.RunSqlGetID("SELECT [图片] FROM [CNGTZ].[dbo].[TZ_图片] where ID=" + ID); if (fiPaht == null) { context.Response.Write("对不起,服务器,没有找到此文件!"); return; } fiPaht = fiPaht.Replace("userimg", "userimg\\source"); if (System.IO.File.Exists(fiPaht) == false) { context.Response.Write("对不起,服务器,没有找到此文件!"); return; } //string filename = "XX.jpg"; Byte[] imgbyte = System.IO.File.ReadAllBytes(fiPaht);//这里只要 得到图片的字节流就可以,如:FileStream f=new FileStream(path);这方法也可以实现 context.Response.ContentType = "image/jpeg"; context.Response.BinaryWrite(imgbyte); }
/// <summary> /// 新增员工 /// </summary> /// <param name="context"></param> public void user_new(HttpContext context) { string name = context.Request["name"]; name = name.Trim(); string py = PY.GetTop(name); string SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员]([姓名],[拼音],[是否在职],[所属部门],[更新时间]) VALUES(@name,@拼音,1,'03',getdate())"; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("@name", name); SqlParameter par2 = new SqlParameter("@拼音", py); dal.RunSql(SQL, new SqlParameter[] { par1, par2 }); if (dal.ExecuteRowCount == 1) { SQL = "SELECT [ID] FROM [CNGTZ].[dbo].[TZ_人员] where 姓名='" + name + "'"; string ID = dal.RunSqlGetID(SQL); SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_基本信息] ([ID],[更新时间])VALUES(" + ID + ",getdate())"; dal.RunSql(SQL); SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_档案1] ([ID],[更新时间])VALUES(" + ID + ",getdate())"; dal.RunSql(SQL); SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_档案2] ([ID],[更新时间])VALUES(" + ID + ",getdate())"; dal.RunSql(SQL); context.Response.Write("true"); } else { context.Response.Write("false"); } }
private string Chart_站点年度生产(string year ,string zd) { string retext = "{ \"ok\":true,\"msg\":\"\",\"sc1\":@替换对象1@,\"sc2\":@替换对象2@,\"sum\":@替换对象3@ }"; string SQL = @"SELECT sum(生产气量) as 生产气量, '输差率'= CASE sum([购进数]+[修正值]) when 0 then 0 else round((sum([购进数])+sum([修正值])-sum([生产气量])-sum([库存数]))/sum([购进数]+[修正值])*100,2) end FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@ GROUP BY 月 order by 月"; SQL = SQL.Replace("@年度@", year); SQL = SQL.Replace("@站点@", zd); DAL dal = new DAL(); DataTable dt = dal.RunSqlDataTable(SQL); if (dt == null) { retext = retext.Replace(":true", ":false"); return retext; } double[] sc1 = new double[12]; double[] sc2 = new double[12]; for (int i = 0; i < dt.Rows.Count; i++) { sc1[i] = double.Parse(dt.Rows[i]["生产气量"].ToString()); sc2[i] = double.Parse(dt.Rows[i]["输差率"].ToString()); } SQL = "SELECT sum(生产气量) as 生产气量 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@"; SQL = SQL.Replace("@年度@", year); SQL = SQL.Replace("@站点@", zd); string yearSum = dal.RunSqlGetID(SQL); string sc1Json = Json.Json_转换到json<double[]>(sc1); string sc2Json = Json.Json_转换到json<double[]>(sc2); retext = retext.Replace("@替换对象1@", sc1Json); retext = retext.Replace("@替换对象2@", sc2Json); retext = retext.Replace("@替换对象3@", yearSum); return retext; }
/// <summary> /// 根据 单号 查找出库数据 返回json对象 /// </summary> /// <param name="code"></param> /// <returns></returns> private string CK_json(string code) { string retext = "{\"err\":{\"ISok\":true,\"msg\":\"未找到单据数据!\"},\"data\":订单数据对象,\"minxi\":明细对象}"; string SQL = "SELECT [ID],[单号],[仓库],[来往单位],[制单日期],[经手人],[总金额],[备注],[领料人],[审核人]FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='出库' and 单据类型 ='物资出库' and 单号='" + code.Trim().ToUpper() + "'"; DAL dal = new DAL(); DataTable dt = dal.RunSqlDataTable(SQL); string temp = ""; if (dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; string weixiu = dr["来往单位"].ToString(); string data = "{{\"单号\":\"{0}\",\"仓库\":\"{1}\",\"出库分类\":\"{2}\",\"制单日期\":\"{3}\",\"经手人\":\"{4}\",\"总金额\":\"{5}\",\"备注\":\"{6}\",\"领料人\":\"{7}\",\"审核人\":\"{8}\",\"维修编号\":\"@维修编号@\" }}"; temp = string.Format(data, dr["单号"].ToString(), dr["仓库"].ToString(), dr["来往单位"].ToString(), DateTime.Parse(dr["制单日期"].ToString()).ToString("yyyy-MM-dd"), dr["经手人"].ToString(), dr["总金额"].ToString(), dr["备注"].ToString().Replace("\r\n", "\\r\\n"), dr["领料人"].ToString(), dr["审核人"].ToString()); retext = retext.Replace("订单数据对象", temp); if (weixiu == "维修维护") { SQL = "SELECT [维修编号] FROM [CNGCL].[dbo].[CL_维修出库] where 出库单号='" + code.Trim().ToUpper() + "'"; weixiu = dal.RunSqlGetID(SQL); retext = retext.Replace("@维修编号@", weixiu); } else { retext = retext.Replace("@维修编号@", ""); } SQL = "SELECT [ID],[物品名称],[物品编号],[分类名称],[规格] ,[单位],[数量],[单价],[小计],[批号],[备注] FROM [CNGCL].[dbo].[CL_仓库明细] where 单号='" + code.Trim().ToUpper() + "'"; dt = dal.RunSqlDataTable(SQL); temp = "{\"total\":100,\"rows\":[] }"; if (dt.Rows.Count > 0) { temp = temp.Replace("[]", Json.TableToJson(dt)); } retext = retext.Replace("明细对象", temp); return retext; } else { retext = "{\"err\":{\"ISok\":false,\"msg\":\"未找到单据数据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}"; return retext; } }
/// <summary> /// 检查数据是否上传 /// </summary> /// <param name="context"></param> public void scDate(HttpContext context) { string t1 = context.Request["time"]; string zd = context.Request["zd"]; DAL dal = new DAL(); string SQL = "SELECT top 1 convert(varchar(10), [日期], 120) as 日期 FROM [CNGQT].[dbo].[SC_班销售统计]where 站点=" + zd + " order by 日期 desc"; string t2 = dal.RunSqlGetID(SQL); if (DateTime.Parse(t1) > DateTime.Parse(t2)) { SQL = "最后上报数据日期为:【" + t2 + "】,统计数据可能不完整!<br>请确认是否要继续查询?"; } else { SQL = "yes"; } context.Response.Write(SQL); }
/// <summary> /// 取站点区间数据 /// </summary> /// <param name="context"></param> public void getZDreg(HttpContext context) { string temp = ""; string text = "{\"err\":{\"ok\":false,\"mes\":\"\"}}"; string retext = "{\"err\":{\"ok\":true,\"mes\":\"\"},\"CanShu\":返回参数对象,\"title\":\"头对象\",\"top1\":流量计对象,\"TingYe\":停业对象,\"gasFL\":销售分类对象,\"YaShoJi\":压缩机对象,\"YouLei\":油类对象,\"ShuiLei\":水类对象,\"DianLei\":电类对象,\"jizhang\":记账单位对象}"; DAL dal = new DAL(); DateTime dtstart = DateTime.Parse(context.Request["datestart"]); //起始日期 DateTime dtend = DateTime.Parse(context.Request["dateend"]); //结束日期 int day = (dtend - dtstart).Days + 1; int zd = Convert.ToInt32(context.Request["zd"].ToString());//站点 string title = dtstart.ToString("yyyy年MM月dd日") + " 至 " + dtend.ToString("yyyy年MM月dd日") + " 共:" + day.ToString() + "天"; if (dtstart > dtend) { text = "{\"err\":{\"ok\":false,\"mes\":\"起始日期在结束日期之后!\"}}"; context.Response.Write(text); return; } temp = "{\"zd\":" + zd.ToString() + ",\"time1\":\"" + dtstart.ToShortDateString() + "\",\"time2\":\"" + dtend.ToShortDateString() + "\"}"; retext = retext.Replace("返回参数对象", temp); dtstart = dtstart.AddDays(-1);//起始班 的上一天 int bz_str = 0, bz_end = 0; string ID_str = "0", ID_end = "0", SQL = ""; temp = ""; CW_每班统计 modstart; //起始数据 起始班 的上一个夜班 CW_每班统计 modend; //结束数据 CW_每班统计 modsum; //累计数据 xzz zdxz;//区间修正值累计 if (zd == 4) { bz_str = ZDGAS.getBZno(dtstart); bz_end = ZDGAS.getBZno(dtend); title = "区间统计:麻柳沱CNG站 " + title; } else { bz_str = ZDGAS.getBZno(dtstart, zd, true); bz_end = ZDGAS.getBZno(dtend, zd, true); if (zd == 2) { title = "区间统计:南高CNG站 " + title; } if (zd == 3) { title = "区间统计:江阳西路CNG站 " + title; } } retext = retext.Replace("头对象", title); SQL = "SELECT ID FROM [CNGQT].[dbo].[CW_每班统计] where 站点=@站点 AND 日期=@日期 AND 班组=@班组"; SqlParameter par1 = new SqlParameter("@站点", zd); SqlParameter par2 = new SqlParameter("@日期", dtstart); SqlParameter par3 = new SqlParameter("@班组", bz_str); ID_str = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 }); par1 = new SqlParameter("@站点", zd); par2 = new SqlParameter("@日期", dtend); par3 = new SqlParameter("@班组", bz_end); ID_end = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 }); if (ID_str == null) { text = "{\"err\":{\"ok\":false,\"mes\":\"没有找到起始日期数据!\"}}"; context.Response.Write(text); return; } if (ID_end == null) { text = "{\"err\":{\"ok\":false,\"mes\":\"没有找到结束日期数据!\"}}"; context.Response.Write(text); return; } modstart = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_str)); modend = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_end)); modsum = getZDregsum(modstart, modend); zdxz = getZDregXzz(modstart, modend); temp = jsonLiuLianJi(modstart, modend, modsum, zdxz); retext = retext.Replace("流量计对象", temp); temp = jsonGasFenLei(modsum, day); retext = retext.Replace("销售分类对象", temp); temp = jsonYouLei(modstart, modend, modsum); retext = retext.Replace("油类对象", temp); temp = jsonShuiLei(modstart, modend, modsum, day, zdxz); retext = retext.Replace("水类对象", temp); temp = jsonDianLei(modstart, modend, modsum, zdxz); retext = retext.Replace("电类对象", temp); temp = jsonYaShoJi(modstart, modend, modsum); retext = retext.Replace("压缩机对象", temp); temp = jsonJiZhang(modstart, modend); retext = retext.Replace("记账单位对象", temp); temp = jsonTingYe(modstart, modend); retext = retext.Replace("停业对象", temp); text = "{\"err\":{\"ok\":true,\"mes\":\"\"}}"; context.Response.Write(retext); }
/// <summary> /// 补签考勤操作 取签到时间 /// </summary> /// <param name="context"></param> public void KQ_XG_BuQiang(HttpContext context) { string index = context.Request["index"]; string ID = context.Request["ID"]; string SQL = "SELECT convert(varchar(20), @替换@,120) as 时间 FROM [CNGTZ].[dbo].[TZ_人员_考勤表] where ID ="; if (index == "4") { SQL = SQL.Replace("@替换@", "上班时间") + ID; } if (index == "5") { SQL = SQL.Replace("@替换@", "下班时间") + ID; } DAL dal = new DAL(); string retext = dal.RunSqlGetID(SQL); context.Response.Write(retext); }
//返回 最后处理考勤的时间 public void KQ_time(HttpContext context) { DAL dal = new DAL(); string SQL = "SELECT TOP 1 convert(varchar(20),[时间],120) as 时间 FROM [CNGTZ].[dbo].[TZ_人员_考勤处理时间] ORDER BY 时间 DESC"; string val = dal.RunSqlGetID(SQL); if (val==null) { val = ""; } context.Response.Write(val); }
/// <summary> /// 删除版本控制记录 /// </summary> /// <param name="context"></param> public void VerDel(HttpContext context) { string SQL = "DELETE FROM [WebApp].[dbo].[WEB_版本控制] WHERE 版本号=@版本号"; string ver = context.Request["ver"]; string conf = context.Server.MapPath("~/Public/CNGconfig.xml"); string oldver = Sys.xmlRead(conf, "缓存配置", "全局版本", "1.00");//读取版本号 string retext = "{{ \"ok\":{0},\"msg\":\"{1}\",\"data\":@替换对象@ }}"; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("版本号", ver); dal.RunSql(SQL, new SqlParameter[] { par1 }); if (dal.ExecuteRowCount == 1) { if (double.Parse(ver) >= double.Parse(oldver)) { SQL = "SELECT TOP 1 [版本号] FROM [WebApp].[dbo].[WEB_版本控制] ORDER BY 版本号 DESC"; oldver = dal.RunSqlGetID(SQL) ?? "1.00"; Sys.xmlAdd(conf, "缓存配置", "全局版本", oldver); } retext = string.Format(retext, "true", "更改数据成功,当前版本号为:" + oldver); retext = retext.Replace("@替换对象@", "0"); } else { retext = string.Format(retext, "false", "错误信息:" + dal.ErrorMsg); retext = retext.Replace("@替换对象@", "0"); } context.Response.Write(retext); }
/// <summary> /// 设置修正值 /// </summary> /// <param name="context"></param> public void SetXzz(HttpContext context) { int zd = int.Parse(context.Request["zd"]); int bz = int.Parse(context.Request["bz"]); DateTime day = DateTime.Parse(context.Request["day"]); string name = context.Request["name"]; double val = double.Parse(context.Request["val"]); DAL dal = new DAL(); string SQL = "SELECT [ID] FROM [CNGQT].[dbo].[CW_每班修正] where 日期=@日期 and 站点=@站点 and 班组=@班组"; SqlParameter par1 = new SqlParameter("日期", day); SqlParameter par2 = new SqlParameter("站点", zd); SqlParameter par3 = new SqlParameter("班组", bz); SqlParameter par4 = new SqlParameter("val", val); string id = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 }); if (id == null) { SQL = "INSERT INTO [CNGQT].[dbo].[CW_每班修正](日期,站点,班组,@参数@,更新时间)VALUES(@日期,@站点,@班组,@val,getdate())"; SQL = SQL.Replace("@参数@", name); dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4 }); } else { SQL = "UPDATE [CNGQT].[dbo].[CW_每班修正] SET @参数@ = @val,更新时间=getdate() WHERE ID=@ID"; SQL = SQL.Replace("@参数@", name); dal.RunSql(SQL, new SqlParameter[] { new SqlParameter("ID", id) ,par4}); } context.Response.Write("ok"); }
/// <summary> /// 库存明细查询 /// </summary> /// <param name="context"></param> public void KC_mingxi(HttpContext context) { int row = int.Parse(context.Request["rows"].ToString());//每页函数 int page = int.Parse(context.Request["page"].ToString());//第几页 string code = context.Request["code"]; DateTime date1 = DateTime.Parse(context.Request["date1"].ToString());//起始时间 DateTime date2 = DateTime.Parse(context.Request["date2"].ToString());//结束时间 string SQL = @"SELECT CONVERT(varchar(10), b.[制单日期], 120) as 日期 ,a.[单号] ,b.[单据类型] ,b.[进仓库] ,b.[出仓库] ,a.[物品编号] ,a.[物品名称] ,a.[规格] ,a.[单位] ,a.[数量] ,a.[单价] ,a.[小计] ,a.[备注] FROM [CNGCL].[dbo].[CL_仓库明细] a LEFT JOIN [CNGCL].[dbo].[CL_仓库单据] b ON a.单号=b.单号 where a.[物品编号]=@物品编号 and b.[制单日期] between @起始时间 and @结束时间 order by b.[制单日期]"; string SQL_rk = @"SELECT SUM(a.[数量]) AS 入库数 FROM [CNGCL].[dbo].[CL_仓库明细] a LEFT JOIN [CNGCL].[dbo].[CL_仓库单据] b ON a.单号=b.单号 where a.[物品编号]=@物品编号 and b.[单据类型]='采购收货' and b.[制单日期] between @起始时间 and @结束时间"; string SQL_ck = @"SELECT SUM(a.[数量]) AS 出库数 FROM [CNGCL].[dbo].[CL_仓库明细] a LEFT JOIN [CNGCL].[dbo].[CL_仓库单据] b ON a.单号=b.单号 where a.[物品编号]=@物品编号 and b.[单据类型]='物资出库' and b.[制单日期] between @起始时间 and @结束时间"; double sum_rk = 0, sum_ck = 0; string temp = "", rowdx = "[]", footdx = "[]"; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("@物品编号", code); SqlParameter par2 = new SqlParameter("@起始时间", date1); SqlParameter par3 = new SqlParameter("@结束时间", date2); DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2, par3 }); string rowtext = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":@行数据对象@,\"footer\":@脚对象@}"; if (dt.Rows.Count > 0) { rowdx = Json.TableToJson(BLL.tools.GetPagedTable(dt, page, row));// 行对象 temp = dal.RunSqlGetID(SQL_rk, new SqlParameter[] { par1, par2, par3 }); if (temp != null && temp != "") { sum_rk = double.Parse(temp);//入库合计数 } temp = dal.RunSqlGetID(SQL_ck, new SqlParameter[] { par1, par2, par3 }); if (temp != null && temp != "") { sum_ck = double.Parse(temp);//出库合计数 } footdx = string.Format("[{{ \"单号\":\"入库合计:\", \"单据类型\":{0:f}, \"出仓库\":\"出库合计:\", \"物品编号\":{1:f}, \"物品名称\":\"结存数:\", \"规格\":{2:f} }}]", sum_rk, sum_ck, sum_rk - sum_ck); } rowtext = rowtext.Replace("@行数据对象@", rowdx); rowtext = rowtext.Replace("@脚对象@", footdx); Sys.DebugMes(rowtext); context.Response.Write(rowtext); }
/// <summary> /// 取得当班对象 /// </summary> /// <param name="dt">日期</param> /// <param name="zd">站点</param> /// <param name="bz">班组</param> /// <param name="yb">夜班</param> /// <param name="scql">生产气量</param> /// <returns></returns> private string getGasSc(DateTime dt, int zd, int bz, bool yb, double scql) { DAL dal = new DAL(); CW_每班统计 dqBz = new CW_每班统计();//当班数据 CW_每班统计 syBz = new CW_每班统计();//上一班数据 DateTime sydt; //上一班日期 string retext = "{\"sc\":输差对象,\"s\":水对象,\"y\":油对象,\"d\":电对象,\"ysj\":压缩机对象}"; int sybz;//上一班 班组 gassc sc = new gassc(); string SQL = "SELECT ID FROM [CNGQT].[dbo].[CW_每班统计] where [站点]=" + zd.ToString() + " and [日期]='" + dt.ToString() + "' and [班组]='" + bz.ToString() + "'"; string id = dal.RunSqlGetID(SQL); if (id == null) { return "[]"; } dqBz = ZDGAS.GetModel_CW_每班统计(int.Parse(id)); if (zd == 4) { sydt = dt.AddDays(-1); sybz = bz - 1; if (sybz == 0) { sybz = 4; } } else { if (yb)//当前班是否是夜班 { sydt = dt; } else { sydt = dt.AddDays(-1); } sybz = bz - 1; if (sybz == 0) { sybz = 3; } } SQL = "SELECT ID FROM [CNGQT].[dbo].[CW_每班统计] where [站点]=" + zd.ToString() + " and [日期]='" + sydt.ToString() + "' and [班组]='" + sybz.ToString() + "'"; id = dal.RunSqlGetID(SQL); syBz = ZDGAS.GetModel_CW_每班统计(int.Parse(id));//上一班数据 //取当班 流量计修正值 SQL = "SELECT [总电峰],[总电平],[总电谷],[商电峰],[商电平],[商电谷],[正无功],[反无功],[普通水],[消防水],[流量计] FROM [CNGQT].[dbo].[CW_每班修正] where 日期='" + dt.ToString() + "' AND 站点=" + zd.ToString() + " AND 班组=" + bz.ToString(); xzz dbxzz = new xzz(); dbxzz.init(); string[] val = dal.RunSqlStrings(SQL); if (val != null) { dbxzz.zdf = double.Parse(val[0]); dbxzz.zdp = double.Parse(val[1]); dbxzz.zdg = double.Parse(val[2]); dbxzz.sdf = double.Parse(val[3]); dbxzz.sdp = double.Parse(val[4]); dbxzz.sdg = double.Parse(val[5]); dbxzz.zwg = double.Parse(val[6]); dbxzz.fwg = double.Parse(val[7]); dbxzz.pts = double.Parse(val[8]); dbxzz.xfs = double.Parse(val[9]); dbxzz.llj = double.Parse(val[10]); } sc.进站起 = dqBz.SX_进站起数; sc.进站止 = dqBz.SX_进站止数; sc.进站气量 = sc.进站止 - sc.进站起; sc.再生起 = dqBz.SX_再生起数; sc.再生止 = dqBz.SX_再生止数; sc.再生气量 = sc.再生止 - sc.再生起; sc.生产气量 = scql; sc.购进气量 = sc.进站气量 - sc.再生气量 + dbxzz.llj; sc.罐储气 = dqBz.SX_罐储气; sc.罐差 = dqBz.SX_罐储气 - syBz.SX_罐储气; sc.罐储气起 = syBz.SX_罐储气; sc.罐储气止 = dqBz.SX_罐储气; sc.输差 = sc.购进气量 - sc.生产气量 - sc.罐差; sc.修正值 = dbxzz.llj; if (sc.生产气量 == 0 || sc.购进气量 == 0) { sc.输差率 = 0; } else { sc.输差率 = sc.输差 / sc.购进气量; } string temp = "{{ \"total\": 1, \"rows\": [{{ \"进站起\": {0:f},\"进站止\":{1:f},\"再生起\":{2:f},\"再生止\":{3:f},\"购进气量\":{4:f},\"生产气量\":{5:f},\"罐储气\":{6:f},\"罐差\":{7:f},\"输差\":{8:f},\"输差率\":{9:f},\"修正\":{10:f} }}]}}"; temp = string.Format(temp, sc.进站起, sc.进站止, sc.再生起, sc.再生止, sc.购进气量, sc.生产气量, sc.罐储气, sc.罐差, sc.输差, sc.输差率 * 100, sc.修正值); retext = retext.Replace("输差对象", temp); temp = jsonYouLei(syBz, dqBz); retext = retext.Replace("油对象", temp); temp = jsonDianLei(syBz, dqBz, dbxzz); retext = retext.Replace("电对象", temp); temp = jsonShuiLei(syBz, dqBz, dbxzz); retext = retext.Replace("水对象", temp); temp = jsonYaShoJi(syBz, dqBz); retext = retext.Replace("压缩机对象", temp); Sys.DebugMes(temp); return retext; }
/// <summary> /// 下一笔入库单 /// </summary> /// <param name="context"></param> public void RK_next(HttpContext context) { string retext = ""; string code = context.Request["code"]; string SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='入库' and 单据类型 ='采购收货' order by 单号 desc"; DAL dal = new DAL(); string max = dal.RunSqlGetID(SQL); if (code == max) { retext = "{\"err\":{\"ISok\":false,\"msg\":\"已经是最后一笔单据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}"; context.Response.Write(retext); return; } SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='入库' and 单据类型 ='采购收货' and 单号>'" + code + "' order by 单号"; code = dal.RunSqlGetID(SQL); retext = RK_json(code); context.Response.Write(retext); }
/// <summary> /// 取 入库单据 json /// </summary> /// <param name="ISmx">是否明细</param> /// <returns></returns> private string getDJ_rk(bool ISmx, DateTime date1, DateTime date2, int row, int page) { string SQL = "", SQLsum = "", footdx = ""; if (ISmx) //判断是否是明细 { SQL = @"SELECT CONVERT(varchar(10), b.[制单日期], 120) as 日期 ,a.[单号] ,b.[来往单位] as 供货单位 ,b.[进仓库] as 入库仓库 ,a.[物品名称] ,a.[规格] ,a.[单位] ,a.[数量] ,a.[单价] ,a.[小计] ,b.[经手人] ,a.[备注] FROM [CNGCL].[dbo].[CL_仓库明细] a LEFT JOIN [CNGCL].[dbo].[CL_仓库单据] b ON a.单号=b.单号 where b.[制单日期] between @起始时间 and @结束时间 and [进出]='入库' order by a.[单号]"; SQLsum = "SELECT sum(a.[小计]) as 总金额 FROM [CNGCL].[dbo].[CL_仓库明细] a LEFT JOIN [CNGCL].[dbo].[CL_仓库单据] b ON a.单号=b.单号 where b.[制单日期] between @起始时间 and @结束时间 and [进出]='入库' "; footdx = "[{{ \"单价\":\"合计:\",\"小计\":{0:f} }}]"; } else { SQL = @"SELECT CONVERT(varchar(10),[制单日期],120) 日期 ,[单号] ,[来往单位] ,[进仓库] as 入库仓库 ,[总金额] ,[经手人] ,[备注] FROM [CNGCL].[dbo].[CL_仓库单据] where 单据类型='采购收货' and 制单日期 between @起始时间 and @结束时间 order by 制单日期"; SQLsum = "SELECT sum([总金额]) as 总金额 FROM [CNGCL].[dbo].[CL_仓库单据] where 单据类型='采购收货' and 制单日期 between @起始时间 and @结束时间"; footdx = "[{{ \"入库仓库\":\"合计:\",\"总金额\":{0:f} }}]"; } double Zsum = 0; string rowdx = "[]"; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("@起始时间", date1); SqlParameter par2 = new SqlParameter("@结束时间", date2); DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2 }); string rowtext = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":@行数据对象@,\"footer\":@脚对象@}"; if (dt.Rows.Count > 0) { Zsum = double.Parse(dal.RunSqlGetID(SQLsum, new SqlParameter[] { par1, par2 })); //求和 rowdx = Json.TableToJson(BLL.tools.GetPagedTable(dt, page, row));// 行对象 //rowtext = rowtext.Replace("@行数据对象@", Json.TableToJson(BLL.tools.GetPagedTable(dt, page, row))); } footdx = string.Format(footdx, Zsum); //脚对象 rowtext = rowtext.Replace("@行数据对象@", rowdx); rowtext = rowtext.Replace("@脚对象@", footdx); return rowtext; }
private string Chart_三站年度电量(string year) { string retext = "{ \"ok\":true,\"msg\":\"\",\"ds\":@替换对象1@,\"zd\":@替换对象2@,\"sum\":@替换对象3@ }"; string SQL = @"SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ GROUP BY 月 order by 月"; SQL = SQL.Replace("@年度@", year); DAL dal = new DAL(); DataTable dt = dal.RunSqlDataTable(SQL); if (dt == null) { retext = retext.Replace(":true", ":false"); return retext; } double[] ds = new double[12];//存储 三站点每月合计销售 for (int i = 0; i < dt.Rows.Count; i++) { ds[i] = double.Parse(dt.Rows[i]["耗电量"].ToString()); } 站点数据类型 zd = new 站点数据类型(0); SQL = @"SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@ GROUP BY 月 order by 月"; SQL = SQL.Replace("@年度@", year); string zdSQL = SQL.Replace("@站点@", "2"); dt = dal.RunSqlDataTable(zdSQL); for (int i = 0; i < dt.Rows.Count; i++) { zd.南高[i] = double.Parse(dt.Rows[i]["耗电量"].ToString()); } zdSQL = SQL.Replace("@站点@", "3"); dt = dal.RunSqlDataTable(zdSQL); for (int i = 0; i < dt.Rows.Count; i++) { zd.西路[i] = double.Parse(dt.Rows[i]["耗电量"].ToString()); } zdSQL = SQL.Replace("@站点@", "4"); dt = dal.RunSqlDataTable(zdSQL); for (int i = 0; i < dt.Rows.Count; i++) { zd.麻柳沱[i] = double.Parse(dt.Rows[i]["耗电量"].ToString()); } SQL = @"SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ "; SQL = SQL.Replace("@年度@", year); string yearSum = dal.RunSqlGetID(SQL); string dsJson = Json.Json_转换到json<double[]>(ds); string zdJson = Json.Json_转换到json<站点数据类型>(zd); retext = retext.Replace("@替换对象1@", dsJson); retext = retext.Replace("@替换对象2@", zdJson); retext = retext.Replace("@替换对象3@", yearSum); return retext; }
/// <summary> /// 查询 调拨单 明细 或 汇总,返回 json row数据 /// </summary> /// <returns></returns> private string HZ_mx_db(int row, int page, DateTime date1, DateTime date2, bool ISmx, string ckck, string rkck) { string SQL = "", SQLsum = "", footdx = "", rowdx = "[]"; if (ISmx) { SQL = @"SELECT CONVERT(varchar(10), b.[制单日期], 120) as 日期 ,a.[单号] ,b.[出仓库] as 出库仓库 ,b.[进仓库] as 入库仓库 ,a.[物品编号] ,a.[物品名称] ,a.[规格] ,a.[单位] ,a.[数量] ,a.[单价] ,a.[小计] ,b.[经手人] ,a.[备注] FROM [CNGCL].[dbo].[CL_仓库明细] a left join [CNGCL].[dbo].[CL_仓库单据] b on a.[单号]=b.[单号] where b.[单据类型]='物资调拨' and b.[进仓库]=@入库仓库名 and b.[出仓库]=@出库仓库名 and b.[制单日期] between @起始时间 and @结束时间 order by a.[单号] "; SQLsum = @"SELECT SUM(a.[小计]) as 合计 FROM [CNGCL].[dbo].[CL_仓库明细] a left join [CNGCL].[dbo].[CL_仓库单据] b on a.[单号]=b.[单号] where b.[单据类型]='物资调拨' and b.[进仓库]=@入库仓库名 and b.[出仓库]=@出库仓库名 and b.[制单日期] between @起始时间 and @结束时间 "; footdx = "[{{ \"物品名称\":\"合计金额:\",\"规格\":{0:f} }}]"; } else { SQL = @"SELECT a.[物品名称],a.[规格] ,a.[单位] ,sum(a.[数量]) as 累计数量 ,sum(a.[小计]) as 小计 FROM [CNGCL].[dbo].[CL_仓库明细] a left join [CNGCL].[dbo].[CL_仓库单据] b on a.[单号]=b.[单号] where b.[单据类型]='物资调拨' and b.[进仓库]=@入库仓库名 and b.[出仓库]=@出库仓库名 and b.[制单日期] between @起始时间 and @结束时间 GROUP BY a.物品名称,a.规格,a.单位 ORDER BY a.物品名称,a.规格"; SQLsum = @"SELECT SUM(a.[小计]) FROM [CNGCL].[dbo].[CL_仓库明细] a left join [CNGCL].[dbo].[CL_仓库单据] b on a.[单号]=b.[单号] where b.[单据类型]='物资调拨' and b.[进仓库]=@入库仓库名 and b.[出仓库]=@出库仓库名 and b.[制单日期] between @起始时间 and @结束时间 "; footdx = "[{{ \"累计数量\":\"合计金额:\",\"小计\":{0:f} }}]"; } DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("@入库仓库名", rkck.Trim()); SqlParameter par2 = new SqlParameter("@出库仓库名", ckck.Trim()); SqlParameter par3 = new SqlParameter("@起始时间", date1); SqlParameter par4 = new SqlParameter("@结束时间", date2); double mxSum = 0; DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2, par3, par4 }); string rowtext = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":@行数据对象@,\"footer\":@脚对象@}"; if (dt.Rows.Count > 0) { mxSum = double.Parse(dal.RunSqlGetID(SQLsum, new SqlParameter[] { par1, par2, par3, par4 })); rowdx = Json.TableToJson(BLL.tools.GetPagedTable(dt, page, row));// 行对象 } footdx = string.Format(footdx, mxSum); //脚对象 rowtext = rowtext.Replace("@行数据对象@", rowdx); rowtext = rowtext.Replace("@脚对象@", footdx); return rowtext; }
private string Chart_站点年度电量(string year, string zd) { string retext = "{ \"ok\":true,\"msg\":\"\",\"sc1\":@替换对象1@,\"sc2\":@替换对象2@,\"sum\":@替换对象3@ }"; string SQL = @"SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量 ,'单位电耗'= CASE sum([生产气量]) when 0 then 0 else round(sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) /sum([生产气量]),3) end FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@ GROUP BY 月 order by 月"; SQL = SQL.Replace("@年度@", year); SQL = SQL.Replace("@站点@", zd); DAL dal = new DAL(); DataTable dt = dal.RunSqlDataTable(SQL); if (dt == null) { retext = retext.Replace(":true", ":false"); return retext; } double[] sc1 = new double[12];//耗电量 double[] sc2 = new double[12];//单位电耗 for (int i = 0; i < dt.Rows.Count; i++) { sc1[i] = double.Parse(dt.Rows[i]["耗电量"].ToString()); sc2[i] = double.Parse(dt.Rows[i]["单位电耗"].ToString()); } SQL = "SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@"; SQL = SQL.Replace("@年度@", year); SQL = SQL.Replace("@站点@", zd); string yearSum = dal.RunSqlGetID(SQL); string sc1Json = Json.Json_转换到json<double[]>(sc1); string sc2Json = Json.Json_转换到json<double[]>(sc2); retext = retext.Replace("@替换对象1@", sc1Json); retext = retext.Replace("@替换对象2@", sc2Json); retext = retext.Replace("@替换对象3@", yearSum); return retext; }
//通过 日期 找入库单数据 public void CK_date(HttpContext context) { string retext = "{\"err\":{\"ISok\":true,\"msg\":\"未找到入库单数据!\"},\"data\":订单数据对象,\"minxi\":明细对象}"; string date = context.Request["date"]; string cksx = context.Request["cksx"];//仓库缩写 string SQL = "SELECT TOP 1 [单号] FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='出库' and 单据类型 ='物资出库' and 单号 like 'LL" + cksx + "%' and 制单日期>='" + date + "' ORDER BY 单号"; DAL dal = new DAL(); string code = dal.RunSqlGetID(SQL); if (code == null) { retext = "{\"err\":{\"ISok\":false,\"msg\":\"未找到单据数据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}"; context.Response.Write(retext); return; } retext = CK_json(code); context.Response.Write(retext); }
/// <summary> /// 通过用户ID 查询相片ID /// </summary> /// <param name="context"></param> public void Img_getID(HttpContext context) { string ID = context.Request["ID"];//用户ID string SQL = "SELECT isnull([相片ID],'0') as 相片ID FROM [CNGTZ].[dbo].[TZ_人员_基本信息] where ID=" + ID; DAL dal = new DAL(); string tpID = dal.RunSqlGetID(SQL); if (tpID == null) { tpID = "0"; } context.Response.Write(tpID); }
/// <summary> /// 最后一笔出库单 /// </summary> /// <param name="context"></param> public void CK_max(HttpContext context) { string retext = ""; string code = context.Request["code"]; string cksx = context.Request["cksx"];//仓库缩写 string SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='出库' and 单据类型 ='物资出库' and 单号 like 'LL" + cksx + "%' order by 单号 desc"; DAL dal = new DAL(); string max = dal.RunSqlGetID(SQL); if (code == max) { retext = "{\"err\":{\"ISok\":false,\"msg\":\"已经是最后一笔单据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}"; context.Response.Write(retext); return; } retext = CK_json(max); context.Response.Write(retext); }
/// <summary> /// 取站点积分公告 /// </summary> /// <param name="context"></param> public void JF_GG_get(HttpContext context) { string zd = context.Request["zd"]; string SQL = "SELECT [积分公告] FROM [CNGTZ].[dbo].[TZ_站点] where ID=" + zd; DAL dal = new DAL(); string txt = dal.RunSqlGetID(SQL); if (txt == null) { txt = "读取公告失败"; } context.Response.Write(txt); }
/// <summary> /// 第一笔定购单 /// </summary> /// <param name="context"></param> public void DB_min(HttpContext context) { string retext = ""; string code = context.Request["code"]; string SQL = "SELECT top 1 [单号] FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='调拨' and 单据类型 ='物资调拨' order by 单号"; DAL dal = new DAL(); string min = dal.RunSqlGetID(SQL); if (code == min) { retext = "{\"err\":{\"ISok\":false,\"msg\":\"已经是第一笔单据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}"; context.Response.Write(retext); return; } retext = DB_json(min); context.Response.Write(retext); }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; context.Response.Charset = "utf-8"; HttpPostedFile file = context.Request.Files["Filedata"];//文件 对象 string name = context.Request["name"];//上传用户 string title = context.Request["title"];//分类 string path = context.Request["path"];//文件路径 if (path == null) { path = ""; } string fuPaht = ""; string Suser = ""; switch (title) { case "公司文件": fuPaht = "E:\\CNGfile\\公司文件" + path; break; case "车用燃气事业部文档": fuPaht = "E:\\CNGfile\\车用燃气事业部" + path; break; case "用户个人文档": fuPaht = "E:\\CNGfile\\个人文档\\" + name + "\\" + path; Suser = name; break; default: break; } DAL dal = new DAL(); if (file != null) { if (!Directory.Exists(fuPaht)) { Directory.CreateDirectory(fuPaht); } file.SaveAs(fuPaht + "\\" + file.FileName); FileItem fi = FileManager.GetItemInfo(fuPaht + "\\" + file.FileName); if (fi.Name.Length > 0)//判断创建文件是否完成 { string SQL = "SELECT [ID] FROM [WebApp].[dbo].[FI_公司文件] where 路径='" + fi.FullName + "'"; string ID = dal.RunSqlGetID(SQL); if (ID != null) { dal.RunSql("DELETE FROM [WebApp].[dbo].[FI_公司文件] WHERE ID=" + ID); } SQL = "INSERT INTO [WebApp].[dbo].[FI_公司文件]([名称],[路径],[修改日期],[文件大小],[上传用户],[目录],[所属用户],[分类])VALUES(@名称,@路径,@修改日期,@文件大小,@上传用户,@目录,@所属用户,@分类)"; SqlParameter par1 = new SqlParameter("@名称", SqlDbType.VarChar, 200); par1.Value = fi.Name; SqlParameter par2 = new SqlParameter("@路径", SqlDbType.VarChar, 800); par2.Value = fi.FullName; SqlParameter par3 = new SqlParameter("@修改日期", fi.LastWriteDate); SqlParameter par4 = new SqlParameter("@文件大小", fi.Size); SqlParameter par5 = new SqlParameter("@上传用户", name); SqlParameter par6 = new SqlParameter("@目录", path); SqlParameter par7 = new SqlParameter("@所属用户", name); SqlParameter par8 = new SqlParameter("@分类", title); Sys.DebugMes(par6.Size.ToString()); dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4, par5, par6, par7, par8 }); Sys.addSteps(name, "上传:[" + fi.Name + "]", "文档操作"); //下面这句代码缺少的话,上传成功后上传队列的显示不会自动消失 context.Response.Write("1"); } } else { context.Response.Write("0"); } }
/// <summary> /// 签到时间生成 /// </summary> /// <param name="dt">结束日期</param> /// <param name="dd">处理天数</param> /// <param name="wc">指纹刷机误差小时</param> /// <param name="cdwc">迟到误差分钟</param> /// <param name="ztwc">早退误差分钟</param> public void KQ_签到处理(DateTime dt, int dd, int wc, int cdwc, int ztwc) { //dt = DateTime.Parse(dt.ToShortDateString()); DateTime 起始时间 = dt.AddDays(0 - dd); //签到处理 string SQL = "SELECT [ID],[姓名],[上班时间],[下班时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤表] where 上班签到 IS NULL and 日期 between @起始 and @结束"; SqlParameter par1 = new SqlParameter("起始", 起始时间); SqlParameter par2 = new SqlParameter("结束", dt); //Sys.DebugMes(起始时间.ToString() + " " + dt.ToString()); DAL dal = new DAL(); DataTable renName = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2 });//取得人员列表 foreach (DataRow row in renName.Rows) { DateTime t = DateTime.Parse(row["上班时间"].ToString()); SqlParameter p1 = new SqlParameter("起始", t.AddHours(0 - wc)); SqlParameter p2 = new SqlParameter("结束", t.AddHours(wc)); SqlParameter p3 = new SqlParameter("姓名", row["姓名"].ToString()); if (row["姓名"].ToString() == "陈兴华") { Sys.DebugMes("XXX"); } SQL = "SELECT top 1 [刷机时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤_刷机时间] where 姓名=@姓名 AND 刷机时间 BETWEEN @起始 AND @结束 ORDER BY 刷机时间"; string qdsj = dal.RunSqlGetID(SQL, new SqlParameter[] { p1, p2, p3 });//找到最早指纹签到时间 if (qdsj == null) { continue;//如果没有找到签到时间 就到下一条记录 } int cd = 0;//判断是否迟到 DateTime tt = DateTime.Parse(qdsj);//签到时间 TimeSpan temp = tt.Subtract(t); cd = temp.Minutes;//迟到分钟 cd = (cd > cdwc) ? 1 : 0; SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_考勤表] SET [上班签到] = @签到时间,[实到] =[实到]+ 1,[迟到]= " + cd.ToString() + " WHERE ID =@ID"; SqlParameter p4 = new SqlParameter("ID", int.Parse(row["ID"].ToString())); SqlParameter p5 = new SqlParameter("签到时间", DateTime.Parse(qdsj)); dal.RunSql(SQL, new SqlParameter[] { p4, p5 });//更新签到时间列表 } //进行签退处理 SQL = "SELECT [ID],[姓名],[上班时间],[下班时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤表] where 下班签退 IS NULL and 日期 between @起始 and @结束"; par1 = new SqlParameter("起始", 起始时间); par2 = new SqlParameter("结束", dt); renName = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2 });//取得人员列表 foreach (DataRow row in renName.Rows) { DateTime t = DateTime.Parse(row["下班时间"].ToString()); SqlParameter p1 = new SqlParameter("起始", t.AddHours(0 - wc)); SqlParameter p2 = new SqlParameter("结束", t.AddHours(wc)); SqlParameter p3 = new SqlParameter("姓名", row["姓名"].ToString()); SQL = "SELECT top 1 [刷机时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤_刷机时间] where 姓名=@姓名 AND 刷机时间 BETWEEN @起始 AND @结束 ORDER BY 刷机时间 desc"; string qdsj = dal.RunSqlGetID(SQL, new SqlParameter[] { p1, p2, p3 });//找到最后下班指纹签到时间 if (qdsj == null) { continue;//如果没有找到签到时间 就到下一条记录 } int zt = 0;//判断是否早退 DateTime tt = DateTime.Parse(qdsj); TimeSpan temp = t.Subtract(tt); zt = temp.Minutes; zt = (zt > ztwc) ? 1 : 0; SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_考勤表] SET [下班签退] = @签到时间,[实到] =[实到]+ 1,[早退]= " + zt.ToString() + " WHERE ID =@ID"; SqlParameter p4 = new SqlParameter("ID", int.Parse(row["ID"].ToString())); SqlParameter p5 = new SqlParameter("签到时间", DateTime.Parse(qdsj)); dal.RunSql(SQL, new SqlParameter[] { p4, p5 });//更新签到时间列表 } //记录处理时间 SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_考勤处理时间]([时间]) VALUES ('" + DateTime.Now.ToString() + "')"; dal.RunSql(SQL); if (this.SJ_指纹处理 != null) { Even_指纹系统处理 e = new Even_指纹系统处理(); e.mesg = "处理考勤签到【" + 起始时间.ToShortDateString() + " 至 " + dt.ToShortDateString() + "】到完成!"; //触发事件 this.SJ_指纹处理(this, e); } }