static void test1() { DAL dal = new DAL(); string[] str = dal.RunSqlStrings("SELECT [用户名],[密码] FROM [WebApp].[dbo].[DL_用户名]"); if (str == null) { Console.WriteLine("错误提示:" + dal.ErrorMsg); } else { Console.WriteLine("用户:{0} 密码:{1} ", str[0], str[1]); } Console.ReadLine(); }
/// <summary> /// 区间统计 修正值合计数据 /// </summary> /// <param name="Gstart">结束</param> /// <param name="Gend">起始</param> /// <returns>修正值模型</returns> private xzz getZDregXzz(CW_每班统计 Gstart, CW_每班统计 Gend) { string SQL = @"SELECT isnull(sum([总电峰]),0) as 总电峰 ,isnull(sum([总电平]),0) as 总电平 ,isnull(sum([总电谷]),0) as 总电谷 ,isnull(sum([商电峰]),0) as 商电峰 ,isnull(sum([商电平]),0) as 商电平 ,isnull(sum([商电谷]),0) as 商电谷 ,isnull(sum([正无功]),0) as 正无功 ,isnull(sum([反无功]),0) as 反无功 ,isnull(sum([普通水]),0) as 普通水 ,isnull(sum([消防水]),0) as 消防水 ,isnull(sum([流量计]),0) as 流量计 FROM [CNGQT].[dbo].[CW_每班修正] where 日期 BETWEEN @起始日期 AND @结束日期 AND 站点=@站点 "; DAL dal = new DAL(); xzz xz = new xzz(); xz.init(); SqlParameter par1 = new SqlParameter("@站点", Gstart.SX_站点); SqlParameter par2 = new SqlParameter("@起始日期", Gstart.SX_日期.AddDays(1)); SqlParameter par3 = new SqlParameter("@结束日期", Gend.SX_日期); string[] val = dal.RunSqlStrings(SQL, new SqlParameter[] { par1, par2, par3 }); if (val != null) { xz.zdf = double.Parse(val[0]); xz.zdp = double.Parse(val[1]); xz.zdg = double.Parse(val[2]); xz.sdf = double.Parse(val[3]); xz.sdp = double.Parse(val[4]); xz.sdg = double.Parse(val[5]); xz.zwg = double.Parse(val[6]); xz.fwg = double.Parse(val[7]); xz.pts = double.Parse(val[8]); xz.xfs = double.Parse(val[9]); xz.llj = double.Parse(val[10]); } return xz; }
/// <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 getBZgas(HttpContext context) { gastop top = new gastop(); bool ISyeban = Convert.ToBoolean(context.Request["bbyb"]); DateTime dt = DateTime.Parse(context.Request["date"]); //日期 int zd = Convert.ToInt32(context.Request["zd"].ToString());//站点 int n = 0; int bz = 0; string topbz = ISyeban ? "夜班" : "白班"; string topbt = ""; switch (zd) {//初始化 枪数目 case 2: n = 6; bz = ZDGAS.getBZno(dt, zd, ISyeban); topbt = "南高CNG站"; break; case 3: n = 12; bz = ZDGAS.getBZno(dt, zd, ISyeban); topbt = "江阳西路CNG站"; break; case 4: n = 4; bz = ZDGAS.getBZno(dt); topbt = "麻柳沱CNG站"; break; default: break; } top.班组 = bz.ToString() + "班" + topbz; top.日期 = dt.ToString("yyyy年MM月dd日"); topbt += ": " + dt.ToString("yyyy年MM月dd日 dddd") + " " + top.班组; List<ZDGAS.gunSum> gunS = new List<ZDGAS.gunSum>(); List<ZDGAS.gunGas> gunG = new List<ZDGAS.gunGas>(); DAL dal = new DAL(); for (int i = 0; i < n; i++) //计算每枪数据 { ZDGAS.gunSum gs = new ZDGAS.gunSum(); ZDGAS.gunGas gg = new ZDGAS.gunGas(); string SQL1 = "", SQL2 = ""; if (zd == 4) { SQL1 = ZDGAS.getBZstarttime(dt, i + 1, true); SQL2 = ZDGAS.getBZendtime(dt, i + 1, true); } else { SQL1 = ZDGAS.getBZstarttime(dt, i + 1, zd, ISyeban, true); SQL2 = ZDGAS.getBZendtime(dt, i + 1, zd, ISyeban, true); } DataTable dt1 = dal.RunSqlDataTable(SQL1);//接班数据 DataTable dt2 = dal.RunSqlDataTable(SQL2);//交班数据 gs.枪号 = i + 1; gg.枪号 = i + 1; gg.站点 = zd; if (dt1.Rows.Count <= 0) { gs.错误信息 = "未接班"; gg.接班时间 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); } else { gg.接班时间 = dt1.Rows[0]["time"].ToString(); gs.停止起 = Convert.ToDouble(dt1.Rows[0]["stopsumgas"].ToString()); gs.接班总气量 = Convert.ToDouble(dt1.Rows[0]["SuMGas"].ToString()); gs.接班总金额 = Convert.ToDouble(dt1.Rows[0]["SuMMOney"].ToString()); } if (dt2.Rows.Count <= 0) { gs.错误信息 += "未交班"; gg.交班时间 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); } else { gg.交班时间 = dt2.Rows[0]["time"].ToString(); gs.停止止 = Convert.ToDouble(dt2.Rows[0]["stopsumgas"].ToString()); gs.交班总气量 = Convert.ToDouble(dt2.Rows[0]["SuMGas"].ToString()); gs.交班总金额 = Convert.ToDouble(dt2.Rows[0]["SuMMOney"].ToString()); } gg.FF_计算数据(); gs.系统班累气量 = Math.Round(gg.记帐刷卡气量 + gg.现金消费气量 + gg.预购刷卡气量, 2); gs.系统班累金额 = Math.Round(gg.记帐刷卡金额 + gg.现金消费金额 + gg.预购刷卡金额, 2); gs.FF_计算数值(); if (gs.错误信息 != "") { switch (gs.错误信息) { case "未交班": gg.交班时间 = "未交班"; break; case "未接班": gg.接班时间 = "未接班"; break; case "未接班未交班": gg.接班时间 = "未接班"; gg.交班时间 = "未交班"; break; default: break; } } gunG.Add(gg); gunS.Add(gs); } string dataFh = "{\"yxdx\":\"影响对象\",\"dbsj\":当班数据,\"top\":头对象,\"sum\":累计对象,\"gas\":销售对象,\"gasjz\":记帐对象,\"btdx\":\"标题对象\",\"char1\":图表对象1,\"char2\":图表对象2,\"dqBZ\":\"【当前班组】\"} ";// 最后返回的 json对象 string rowtext = "{\"total\":12,\"rows\":行数据对象,\"footer\":脚对象}"; string temp = ""; // 统计记帐单位数据 string SQL = "SELECT [记账单位名称] as 记帐单位,[刷卡记账气量] as 刷卡,[手工记账气量] as 手工 FROM [CNGQT].[dbo].[SC_记账明细] where 站点=" + zd.ToString() + " AND 班组=" + bz.ToString() + " AND 日期='" + dt.ToShortDateString() + "'"; DataTable jzdw = dal.RunSqlDataTable(SQL); double[] jz = new double[3]; foreach (DataRow i in jzdw.Rows) //统计合计数 { jz[0] += Convert.ToDouble(i["刷卡"].ToString()); jz[1] += Convert.ToDouble(i["手工"].ToString()); } string jdx = Json.TableToJson(jzdw); if (jdx == "") { jdx = "[]"; } temp = rowtext.Replace("行数据对象", jdx); temp = temp.Replace("脚对象", string.Format("[{{\"记帐单位\":\"合计:\",\"刷卡\":{0:f},\"手工\":{1:f} }}]", jz[0], jz[1])); dataFh = dataFh.Replace("记帐对象", temp);//生成 记帐对象json double[] z1 = new double[6]; foreach (ZDGAS.gunGas i in gunG)//计算销售合计值 { z1[0] += i.现金消费气量; z1[1] += i.现金消费金额; z1[2] += i.记帐刷卡气量; z1[3] += i.记帐刷卡金额; z1[4] += i.预购刷卡气量; z1[5] += i.预购刷卡金额; } double[] z2 = new double[12]; foreach (ZDGAS.gunSum i in gunS)//计算累计合计值 { z2[0] += i.接班总气量; z2[1] += i.接班总金额; z2[2] += i.交班总气量; z2[3] += i.交班总金额; z2[4] += i.机器班累气量; z2[5] += i.机器班累金额; z2[6] += i.系统班累气量; z2[7] += i.系统班累金额; z2[8] += i.误差; z2[9] += i.停止起; z2[10] += i.停止止; z2[11] += i.停止总累; } string foot2 = string.Format("[{{\"枪号\":\"合计:\",\"接班总气量\":{0:f},\"接班总金额\":{1:f},\"交班总气量\":{2:f},\"交班总金额\":{3:f},\"机器班累气量\":{4:f},\"机器班累金额\":{5:f},\"系统班累气量\":{6:f},\"系统班累金额\":{7:f},\"误差\":{8:f},\"停止起\":{9:f},\"停止止\":{10:f},\"停止总累\":{11:f} }}]", z2[0], z2[1], z2[2], z2[3], z2[4], z2[5], z2[6], z2[7], z2[8], z2[9], z2[10], z2[11]); string foot1 = string.Format("[{{\"交班时间\":\"合计:\",\"现金消费气量\":{0:f},\"现金消费金额\":{1:f},\"记帐刷卡气量\":{2:f},\"记帐刷卡金额\":{3:f},\"预购刷卡气量\":{4:f},\"预购刷卡金额\":{5:f} }}]", z1[0], z1[1], z1[2], z1[3], z1[4], z1[5]); //行数据 temp = rowtext.Replace("行数据对象", Json.Json_转换到json(gunG)); temp = temp.Replace("脚对象", foot1); dataFh = dataFh.Replace("销售对象", temp);//生成销售对象json temp = rowtext.Replace("行数据对象", Json.Json_转换到json(gunS)); temp = temp.Replace("脚对象", foot2); dataFh = dataFh.Replace("累计对象", temp); //头对象 top.交班底数 = z2[2]; top.接班底数 = z2[0]; top.生产气量 = z2[2] - z2[0]; top.预购刷卡气量 = z1[4]; top.刷卡记帐气量 = jz[0]; top.手工记帐气量 = jz[1]; SQL = "SELECT [报损气量],[现金消费金额] FROM [CNGQT].[dbo].[SC_班销售统计] where 站点=" + zd.ToString() + " AND 班组=" + bz.ToString() + " AND 日期='" + dt.ToShortDateString() + "'"; string[] xfsz = dal.RunSqlStrings(SQL); if (xfsz == null) { top.报损气量 = 0; top.交款金额 = 0; } else { top.报损气量 = Convert.ToDouble(xfsz[0]); top.交款金额 = Convert.ToDouble(xfsz[1]); } top.交款气量 = top.生产气量 - top.预购刷卡气量 - top.刷卡记帐气量 - top.手工记帐气量 - top.报损气量; temp = "{{ \"total\": 1, \"rows\": [{{ \"班组\": \"{0}\",\"日期\":\"{1}\",\"交班底数\":{2:f},\"接班底数\":{3:f},\"生产气量\":{4:f},\"预购刷卡气量\":{5:f},\"刷卡记帐气量\":{6:f},\"手工记帐气量\":{7:f},\"报损气量\":{8:f},\"交款气量\":{9:f},\"交款金额\":\"{10:C2}\" }}]}}"; temp = string.Format(temp, top.班组, top.日期, top.交班底数, top.接班底数, top.生产气量, top.预购刷卡气量, top.刷卡记帐气量, top.手工记帐气量, top.报损气量, top.交款气量, top.交款金额); dataFh = dataFh.Replace("头对象", temp); dataFh = dataFh.Replace("标题对象", topbt); temp = getGasSc(dt, zd, bz, ISyeban, top.生产气量); dataFh = dataFh.Replace("当班数据", temp); temp = getGastubiao(dt, zd, bz, ISyeban); dataFh = dataFh.Replace("图表对象1", temp); temp = string.Format("{{ \"val\":[ {0:f},{1:f},{2:f},{3:f} ],\"sum\":{4:f} }}", top.交款气量, top.预购刷卡气量, top.刷卡记帐气量 + top.手工记帐气量, top.报损气量, top.生产气量); dataFh = dataFh.Replace("图表对象2", temp); temp = getGasStop(dt, zd, bz, ISyeban); dataFh = dataFh.Replace("影响对象", temp); dataFh = dataFh.Replace("【当前班组】", bz.ToString()); Sys.DebugMes(dataFh); context.Response.Write(dataFh); }
/// <summary> /// 获取基本信息 /// </summary> private void get_jbxx() { string SQL = @"SELECT a.[姓名],a.[拼音] ,a.[所属部门] as 部门编号, CONVERT(varchar(20), a.[更新时间] , 120) as 日期,b.部门 FROM [CNGTZ].[dbo].[TZ_人员] a LEFT JOIN [CNGTZ].dbo.TZ_部门 b ON a.所属部门=b.部门编号 where ID=@ID"; SqlParameter par1 = new SqlParameter("@ID", id); DAL dal = new DAL(); string[] val = dal.RunSqlStrings(SQL, new SqlParameter[] { par1 }); _姓名 = val[0]; _拼音 = val[1]; //_是否在职 = val[0]; _部门编号 = val[2]; _更新时间 = val[3]; _部门 = val[4]; #region 基本信息 SQL = @"SELECT isnull([性别],'') as 性别 ,isnull(convert(varchar(10), [出生年月],120),'') as 出生年月 ,isnull([民族],'') as 民族 ,isnull([籍贯],'') as 籍贯 ,isnull([户口],'') as 户口 ,isnull([婚姻],'') as 婚姻 ,isnull([学历],'') as 学历 ,isnull([政治面貌],'') as 政治面貌 ,isnull([身份证号],'') as 身份证号 ,isnull([相片ID],'0') as 相片ID FROM [CNGTZ].[dbo].[TZ_人员_基本信息] where ID=@ID"; string[] val1 = dal.RunSqlStrings(SQL, new SqlParameter[] { par1 }); _基本信息.性别 = val1[0]; _基本信息.出生年月 = val1[1].Trim(); _基本信息.民族 = val1[2]; _基本信息.籍贯 = val1[3]; _基本信息.户口 = val1[4]; _基本信息.婚姻 = val1[5]; _基本信息.学历 = val1[6]; _基本信息.政治面貌 = val1[7]; _基本信息.身份证号 = val1[8]; _基本信息.相片ID = val1[9]; #endregion #region 档案1 SQL = @"SELECT isnull([行政级别],'') as 行政级别 ,isnull([职务],'') as 职务 ,isnull([职称],'') as 职称 ,isnull([岗位工种],'') as 岗位工种 ,isnull([用工形式],'') as 用工形式 ,isnull([工资等级],'') as 工资等级 ,isnull([手机],'') as 手机 ,isnull([联系电话],'') as 联系电话 ,isnull([电子邮件],'') as 电子邮件 ,isnull([家庭住址],'') as 家庭住址 ,isnull([邮编],'') as 邮编 ,isnull([发卡银行],'') as 发卡银行 ,isnull([银行卡号],'') as 银行卡号 FROM [CNGTZ].[dbo].[TZ_人员_档案1] where ID=@ID"; string[] val2 = dal.RunSqlStrings(SQL, new SqlParameter[] { par1 }); _档案1.行政级别 = val2[0]; _档案1.职务 = val2[1]; _档案1.职称 = val2[2]; _档案1.岗位工种 = val2[3]; _档案1.用工形式 = val2[4]; _档案1.工资等级 = val2[5]; _档案1.手机 = val2[6]; _档案1.联系电话 = val2[7]; _档案1.电子邮件 = val2[8]; _档案1.家庭住址 = val2[9]; _档案1.邮编 = val2[10]; _档案1.发卡银行 = val2[11]; _档案1.银行卡号 = val2[12]; #endregion #region 档案2 SQL = @"SELECT isnull([毕业院校],'') as 毕业院校 ,isnull(convert(varchar(10), [毕业时间],120),'') as 毕业时间 ,isnull([专业],'') as 专业 ,isnull(convert(varchar(10), [入工会日期],120),'') as 入工会日期 ,isnull(convert(varchar(10), [入党日期],120),'') as 入党日期 ,isnull(convert(varchar(10), [入团日期],120),'') as 入团日期 ,isnull(convert(varchar(10), [入职日期],120),'') as 入职日期 ,isnull(convert(varchar(10), [离职日期],120),'') as 离职日期 ,isnull([离职原因],'') as 离职原因 ,isnull(convert(varchar(10), [参加工作日期],120),'') as 参加工作日期 ,isnull([入职前工龄],0) as 入职前工龄 FROM [CNGTZ].[dbo].[TZ_人员_档案2] where ID=@ID"; string[] val3 = dal.RunSqlStrings(SQL, new SqlParameter[] { par1 }); _档案2.毕业院校 = val3[0]; _档案2.毕业时间 = val3[1]; _档案2.专业 = val3[2]; _档案2.入工会日期 = val3[3]; _档案2.入党日期 = val3[4]; _档案2.入团日期 = val3[5]; _档案2.入职日期 = val3[6]; _档案2.离职日期 = val3[7]; _档案2.离职原因 = val3[8]; _档案2.参加工作日期 = val3[9]; _档案2.入职前工龄 = val3[10]; #endregion }
/// <summary> /// 获取一条数据信息 /// </summary> public void get(string code) { id = int.Parse(code); string SQL = @"SELECT [自分类] ,[作业类别] ,[作业代号] ,[资格类型] ,[项目名称] ,[准操项目] ,[工种] ,[证书编号] ,isnull(convert(varchar(10), [发证时间],120),'') as 发证时间 ,isnull(convert(varchar(10), [有效时间],120),'') as 有效时间 ,[发证单位] ,[备注] ,[更新时间] FROM [CNGTZ].[dbo].[TZ_人员_资质证书] where ID=" + code; DAL dal = new DAL(); string[] val = dal.RunSqlStrings(SQL); _自分类 = val[0]; _作业类别 = val[1]; _作业代号 = val[2]; _资格类型 = val[3]; _项目名称 = val[4]; _准操项目 = val[5]; _工种 = val[6]; _证书编号 = val[7]; _发证时间 = val[8]; _有效时间 = val[9]; _发证单位 = val[10]; _备注 = val[11]; }
/// <summary> /// 找到站点数据,中心数据库数据,充装检查数据 /// </summary> /// <param name="context"></param> public void Checkzddate(HttpContext context) { int zd = int.Parse(context.Request["zd"]);//站点 string kh = context.Request["kh"];//卡号 string lsh = context.Request["lsh"];//流水号 string rq = context.Request["rq"];//日期 string zdIP = "", zdName = "", km = "", cp = ""; string json = "{\"日期\":\"@日期@\",\"卡号\":@卡号@,\"卡名\":\"@卡名@\",\"车牌\":\"@车牌@\",\"站点\":\"@站点@\",\"站点ok\":@站点ok@,\"中心ok\":@中心ok@,\"充装ok\":@充装ok@}"; bool zdok = false, zxok = false, czok = false; switch (zd) { case 2: zdIP = "192.168.173.3"; zdName = "南高"; break;//南高 case 3: zdIP = "192.168.172.23"; zdName = "江阳西路"; break;//江阳西路 case 4: zdIP = "192.168.171.3"; zdName = "麻柳沱"; break; //麻柳沱 case 5: zdIP = "192.168.174.3"; zdName = "华阳"; break; //华阳 default: break; } string SQL = "SELECT GunNo FROM [cngms].[dbo].[PayGas] where TradeDate='" + rq + "' AND [TradeNo]=" + lsh + " AND UserCardNo=" + kh; DAL dal = new DAL(); DataTable dt = null; //检查站点数据 DAL Zdal = new DAL(zdIP);//用于连接站点数据库 if (Zdal.TestCon(2))//先测试连接 { dt = Zdal.RunSqlDataTable(SQL); if (dt != null && dt.Rows.Count == 1) { zdok = true; } } json = json.Replace("@站点ok@", zdok.ToString().ToLower()); //检查中心数据库数据 SQL = "SELECT [GunNO]FROM [CSERVER].[dbo].[GAS] where StationNO=" + zd.ToString() + " AND TradeDate='" + rq + "' AND TradeNO=" + lsh + " AND UserCardNo=" + kh; dt = dal.RunSqlDataTable(SQL); if (dt != null && dt.Rows.Count == 1) { zxok = true; } else { SQL = "SELECT [GunNO]FROM [CSERVER].[dbo].[TEMPGAS] where StationNO=" + zd.ToString() + " AND TradeDate='" + rq + "' AND TradeNO=" + lsh + " AND UserCardNo=" + kh; dt = dal.RunSqlDataTable(SQL); if (dt != null && dt.Rows.Count == 1) { zxok = true; dal.ExecuteRowCount = 0; } } json = json.Replace("@中心ok@", zxok.ToString().ToLower()); //检查 充装记录数据 SQL = "SELECT [枪号] FROM [CNGQT].[dbo].[CNG_充装检查] where 站点=" + zd.ToString() + " and 日期='" + rq + "' AND 流水号=" + lsh + " AND 用户号=" + kh; dt = dal.RunSqlDataTable(SQL); if (dt != null && dt.Rows.Count == 1) { czok = true; } json = json.Replace("@充装ok@", czok.ToString().ToLower()); //取的卡 正确数据信息 SQL = "SELECT TOP 1 a.[UserCardNo],a.[CarNo],b.ICardName FROM [CSERVER].[dbo].[UserCards] a left JOIN [CSERVER].dbo.ICardType b ON a.ICardType=b.ICardType where a.UserCardNo=" + kh; string[] temp = dal.RunSqlStrings(SQL); if (temp != null) { km = temp[2].Trim(); cp = temp[1].Trim(); } json = json.Replace("@卡名@", km); json = json.Replace("@车牌@", cp); json = json.Replace("@卡号@", kh); json = json.Replace("@站点@", zdName); json = json.Replace("@日期@", rq); Sys.DebugMes(json); context.Response.Write(json); }
/// <summary> /// 获取财务数据 /// </summary> public void getCWData() { DAL dal = new DAL(); #region 第一步 分站 统计 各项气量和金额 统计 月度汇总气量 //分站统计 string SQL = @"SELECT [站点] ,sum([生产气量]) as 生产合计 ,sum([预购刷卡气量]) as 刷卡气量合计 ,sum([记账刷卡气量]) as 刷卡记账合计 ,sum([手工记账气量]) as 手工记账合计 ,sum([现金消费气量]) as 现金气量合计 ,sum([现金消费金额]) as 现金合计 ,sum([报损气量]) as 报损合计 FROM [CNGQT].[dbo].[SC_班销售统计] where 日期 between '@起始日期@' and '@结束日期@' group by 站点 order by 站点"; SQL = SQL.Replace("@起始日期@", Str.ToString("yyyy-MM-dd")); SQL = SQL.Replace("@结束日期@", End.ToString("yyyy-MM-dd")); DataTable dt = dal.RunSqlDataTable(SQL); foreach (DataRow row in dt.Rows) { int n = (int)row["站点"]; for (int i = 0; i < zds; i++) { if (n == myData[i].站点) { myData[i].生产气量 = double.Parse(row["生产合计"].ToString()); myData[i].预购刷卡气量 = double.Parse(row["刷卡气量合计"].ToString()); myData[i].刷卡记账气量 = double.Parse(row["刷卡记账合计"].ToString()); myData[i].手工记账气量 = double.Parse(row["手工记账合计"].ToString()); myData[i].交款气量 = double.Parse(row["现金气量合计"].ToString()); myData[i].交款金额 = double.Parse(row["现金合计"].ToString()); myData[i].报损气量 = double.Parse(row["报损合计"].ToString()); } } } //汇总统计 SQL = @"SELECT sum ([预购刷卡气量]) as 预购刷卡气量, sum ([预购刷卡金额]) as 预购刷卡金额, sum ([现金消费气量]) as 现金消费气量, sum ([现金消费金额]) as 现金消费金额 FROM [CNGQT].[dbo].[SC_班销售统计] where 日期 BETWEEN '@起始时间@' AND '@结束时间@' "; SQL = SQL.Replace("@起始时间@", Str.ToString("yyyy-MM-dd")); SQL = SQL.Replace("@结束时间@", End.ToString("yyyy-MM-dd")); dt = dal.RunSqlDataTable(SQL); 月度汇总.预购刷卡气量 = double.Parse(dt.Rows[0]["预购刷卡气量"].ToString()); 月度汇总.刷卡金额 = double.Parse(dt.Rows[0]["预购刷卡金额"].ToString()); 月度汇总.交款气量 = double.Parse(dt.Rows[0]["现金消费气量"].ToString()); 月度汇总.交款金额 = double.Parse(dt.Rows[0]["现金消费金额"].ToString()); #endregion #region 第二部 区间统计 修正值,库存,普通水,电量,购进(无修正) foreach (CW_月度统计Mod my in myData) { int zd = my.站点; int bz_str = 0, bz_end = 0; string ID_str = "0", ID_end = "0"; double ljxz = 0; //存放累计修正值 //第一步 取 区间 的班组号 if (zd == 4) { bz_str = ZDGAS.getBZno(Str.AddDays(-1)); bz_end = ZDGAS.getBZno(End); } else { bz_str = ZDGAS.getBZno(Str.AddDays(-1), zd, true); bz_end = ZDGAS.getBZno(End, zd, true); } CW_每班统计 modstart; //起始数据 起始班 的上一个夜班 CW_每班统计 modend; //结束数据 CW_每班统计 modsum; //累计数据 XzzMod zdxz = new XzzMod(); //区间修正值累计 //第二步 取得 班组数据所在数据库的ID SQL = "SELECT ID FROM [CNGQT].[dbo].[CW_每班统计] where 站点=@站点 AND 日期=@日期 AND 班组=@班组"; SqlParameter par1 = new SqlParameter("@站点", zd); SqlParameter par2 = new SqlParameter("@日期", Str.AddDays(-1)); SqlParameter par3 = new SqlParameter("@班组", bz_str); ID_str = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 }); par1 = new SqlParameter("@站点", zd); par2 = new SqlParameter("@日期", End); par3 = new SqlParameter("@班组", bz_end); ID_end = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 }); if (ID_end==null || ID_str==null) { Exception e = new Exception("交接班数据不完整,请检查!"); throw e; } //第三步 取得实体对象 修正值对象 modstart = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_str)); modend = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_end)); zdxz = getZDregXzz(modstart, modend); ljxz += zdxz.llj; //第四步 取得所需数据 my.购进数 = Math.Round((modend.SX_进站止数 - modstart.SX_进站止数) - (modend.SX_再生止数 - modstart.SX_再生止数), 2); //无修正值购进数 my.电量总峰 = Math.Round(modend.SX_电量总峰 - modstart.SX_电量总峰 + zdxz.zdf, 2); my.电量总平 = Math.Round(modend.SX_电量总平 - modstart.SX_电量总平 + zdxz.zdp, 2); my.电量总谷 = Math.Round(modend.SX_电量总谷 - modstart.SX_电量总谷 + zdxz.zdg, 2); my.修正值 = zdxz.llj; my.普通水 = Math.Round(modend.SX_普通水 - modstart.SX_普通水 + zdxz.pts, 2); my.库存数 = Math.Round(modend.SX_罐储气 - modstart.SX_罐储气, 2); } #endregion #region 第三步 将月度数据写入数据库 foreach (CW_月度统计Mod my in myData) { SQL = @"SELECT ID,锁定 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年@ and 月=@月@ and 站点=@站点@"; SQL = SQL.Replace("@年@", year.ToString()); SQL = SQL.Replace("@月@", yue.ToString()); SQL = SQL.Replace("@站点@", my.站点.ToString()); string[] data = dal.RunSqlStrings(SQL); if (data[1] == "True") {//数据如果锁定,就不更新数据 return; } SQL = @"UPDATE [CNGQT].[dbo].[CW_月度统计] SET [购进数] = @购进数@ ,[生产气量] = @生产气量@ ,[刷卡金额] = @刷卡金额@ ,[充值金额] = @充值金额@ ,[交款金额] = @交款金额@ ,[手工记账气量] = @手工记账气量@ ,[刷卡记账气量] = @刷卡记账气量@ ,[预购刷卡气量] = @预购刷卡气量@ ,[交款气量] = @交款气量@ ,[报损气量] = @报损气量@ ,[库存数] = @库存数@ ,[电量总峰] = @电量总峰@ ,[电量总平] = @电量总平@ ,[电量总谷] = @电量总谷@ ,[普通水] = @普通水@ ,[停业时间] = @停业时间@ ,[更新时间] = getdate() WHERE ID=@ID@"; SQL = SQL.Replace("@购进数@", my.购进数.ToString()); SQL = SQL.Replace("@生产气量@", my.生产气量.ToString()); SQL = SQL.Replace("@刷卡金额@", my.刷卡金额.ToString()); SQL = SQL.Replace("@充值金额@", my.充值金额.ToString()); SQL = SQL.Replace("@交款金额@", my.交款金额.ToString()); SQL = SQL.Replace("@手工记账气量@", my.手工记账气量.ToString()); SQL = SQL.Replace("@刷卡记账气量@", my.刷卡记账气量.ToString()); SQL = SQL.Replace("@预购刷卡气量@", my.预购刷卡气量.ToString()); SQL = SQL.Replace("@交款气量@", my.交款气量.ToString()); SQL = SQL.Replace("@报损气量@", my.报损气量.ToString()); SQL = SQL.Replace("@库存数@", my.库存数.ToString()); SQL = SQL.Replace("@电量总峰@", my.电量总峰.ToString()); SQL = SQL.Replace("@电量总平@", my.电量总平.ToString()); SQL = SQL.Replace("@电量总谷@", my.电量总谷.ToString()); SQL = SQL.Replace("@普通水@", my.普通水.ToString()); SQL = SQL.Replace("@停业时间@", my.停业时间.ToString()); SQL = SQL.Replace("@ID@", data[0]); dal.RunSql(SQL); } #endregion #region 第四步 统计单位记账数据 合计数据 SQL = @"SELECT a.[记账单位名称] ,isnull ( b.刷卡气量,0) as 刷卡 ,isnull ( b.手工气量,0) as 手工 ,isnull ( c.普通价格,0) as 价格 FROM [CNGQT].[dbo].[SC_记账单位名称] a LEFT JOIN ( SELECT [记账单位名称] ,sum([刷卡记账气量]) as 刷卡气量 ,sum([手工记账气量]) as 手工气量 FROM [CNGQT].[dbo].[SC_记账明细] where 日期 BETWEEN '@起始时间@' and '@结束时间@' GROUP BY 记账单位名称 ) b ON a.记账单位名称=b.记账单位名称 LEFT JOIN ( select aa.单位名称,aa.普通价格 from [CNGQT].[dbo].SC_记账单位执行价格 aa where 起始时间 = ( select top 1 起始时间 from [CNGQT].[dbo].SC_记账单位执行价格 where 单位名称 = aa.单位名称 and 起始时间<='@起始时间@' ORDER BY aa.起始时间 DESC ) ) c ON c.单位名称=a.记账单位名称 ORDER BY a.编号"; SQL = SQL.Replace("@起始时间@", Str.ToShortDateString()); SQL = SQL.Replace("@结束时间@", End.ToShortDateString()); DataTable dt1 = dal.RunSqlDataTable(SQL); for (int i = 0; i < dt1.Rows.Count; i++) { 记账单位合计[i] = new CW_JiZhangDanWen(); 记账单位合计[i].单位名称 = dt1.Rows[i]["记账单位名称"].ToString(); 记账单位合计[i].手工 = double.Parse(dt1.Rows[i]["手工"].ToString()); 记账单位合计[i].刷卡 = double.Parse(dt1.Rows[i]["刷卡"].ToString()); 记账单位合计[i].价格 = double.Parse(dt1.Rows[i]["价格"].ToString()); } #endregion }
/// <summary> /// 通过 单号 初始化数据 /// </summary> public void init() { string SQL = @"SELECT a.[维修编号] ,a.[站点] ,a.[站点负责人] ,a.[发现人] ,a.[接报人] ,a.[接报时间] ,a.[记录人] ,a.[参与维修人员] ,a.[维修内容] ,a.[维修责任人] ,a.[维修起始时间] ,a.[维修结束时间] ,a.[派工编号] ,a.[维修单日期] ,a.[记录填写时间] ,a.[审核状态] ,a.[是否外包] ,b.站点名 FROM [CNGCL].[dbo].[WX_维修单记录] a LEFT JOIN [CNGCL].[dbo].[WX_站点] b ON a.站点=b.编号 WHERE 维修编号='" + _维修编号 + "'"; DAL dal = new DAL(); string[] r = dal.RunSqlStrings(SQL); if (r == null) { return; } _站点编号 = Int32.Parse(r[1]); _站点负责人 = r[2]; _发现人 = r[3]; _接报人 = r[4]; _接报时间 = (r[5] == "") ? "" : DateTime.Parse(r[5]).ToString("yyyy-MM-dd hh:mm"); _记录人 = r[6]; _参与维修人员 = r[7]; _维修内容 = r[8]; _维修责任人 = r[9]; _维修起始时间 = (r[10] == "") ? "" : DateTime.Parse(r[10]).ToString("yyyy-MM-dd hh:mm"); _维修结束时间 = (r[11] == "") ? "" : DateTime.Parse(r[11]).ToString("yyyy-MM-dd hh:mm"); _派工编号 = r[12]; _维修单日期 = (r[13] == "") ? "" : DateTime.Parse(r[13]).ToString("yyyy-MM-dd"); _记录填写时间 = (r[14] == "") ? "" : DateTime.Parse(r[14]).ToString("yyyy-MM-dd"); _审核状态 = r[15]; _是否外包 = bool.Parse(r[16]); _站点 = r[17]; if (_派工编号 != "") { SQL = @"SELECT [维修人员] ,[出发时间] ,[派工人] ,[维修班] FROM [CNGCL].[dbo].[WX_派工记录] where 派工编号='" + _派工编号 + "'"; r = dal.RunSqlStrings(SQL); _派工_安排人员 = r[0]; _派工_出发时间 = (r[1] == "") ? "" : DateTime.Parse(r[1]).ToString("yyyy-MM-dd hh:mm"); _派工_批准人 = r[2]; _派工_维修人 = r[3]; } SQL = @"SELECT [ID] ,[维修单号] ,[设备名称] ,[现象] ,[是否计划] ,[维修组件] FROM [CNGCL].[dbo].[WX_设备记录] where 维修单号='" + _维修编号 + "' ORDER BY 序号"; _维修设备 = dal.RunSqlDataTable(SQL); _维修设备.TableName = "维修设备"; SQL = @"SELECT [ID] ,[序号] ,[维修单号] ,[材料编号] ,[材料名称] ,[规格] ,[数量] ,[单位] ,[用于设备] as 用于 FROM [CNGCL].[dbo].[WX_维修用料] where 维修单号='" + _维修编号 + "' ORDER BY 序号"; _维修材料 = dal.RunSqlDataTable(SQL); _维修材料.TableName = "维修材料"; SQL = @"SELECT [ID] ,[维修单号] ,[审核内容1] ,[审核内容2] ,[审核内容3] ,[审核内容4] ,[审核1] ,[审核2] ,[审核3] ,[审核4] FROM [CNGCL].[dbo].[WX_审核] where 维修单号='" + _维修编号 + "'"; _审核 = dal.RunSqlDataTable(SQL); _审核.TableName = "审核"; }
/// <summary> /// 设置审核状态 /// </summary> /// <param name="code">单号</param> private void WX_Setshenhe(string code) { string shzt = ""; string SQL = "SELECT[是否外包] FROM [CNGCL].[dbo].[WX_维修单记录] where 维修编号='" + code + "'"; DAL dal = new DAL(); string ISwb = dal.RunSqlGetID(SQL); //是否外包 SQL = "SELECT [审核1],[审核2],[审核3],[审核4] FROM [CNGCL].[dbo].[WX_审核] where 维修单号='" + code + "'"; string[] sh = dal.RunSqlStrings(SQL);//审核签名 int n = 0; if (ISwb == "1") { for (int i = 0; i < 4; i++) { if (sh[i] != "") { n++; } } switch (n) { case 0: shzt = "未审核"; break; case 4: shzt = "审核完成"; break; default: shzt = "审核进行中"; break; } } else { for (int i = 0; i < 2; i++) { if (sh[i] != "") { n++; } } switch (n) { case 0: shzt = "未审核"; break; case 2: shzt = "审核完成"; break; default: shzt = "审核进行中"; break; } } SQL = "UPDATE [CNGCL].[dbo].[WX_维修单记录] SET [审核状态] ='" + shzt + "' WHERE 维修编号='" + code + "'"; dal.RunSql(SQL); }
//获取答卷的文本格式 public void sjText(HttpContext context) { int ID = int.Parse(context.Request["ID"].ToString());//答卷ID string SQL = "SELECT [试题],[正确答案],[所属试卷名] FROM [CNGTZ].[dbo].[TK_答题试卷] where ID=" + ID; DAL dal = new DAL(); string[] x = dal.RunSqlStrings(SQL); //x[0] = x[0].Substring(1, x[0].Length - 2); //x[1] = x[1].Substring(1, x[1].Length - 2); List<int> tmID = Json.Json_转换到对象<List<int>>(x[0]); List<int[]> tmDA = Json.Json_转换到对象<List<int[]>>("[" + x[1] + "]"); string retext = "《" + x[2] + "》\r\n\r\n"; for (int i = 0; i < tmID.Count; i++) { string tm = dal.RunSqlGetID("SELECT [题目] FROM [CNGTZ].[dbo].[TK_题目] where ID=" + tmID[i].ToString()); DataTable dt = dal.RunSqlDataTable("SELECT [内容] FROM [CNGTZ].[dbo].[TK_选项] where 所属ID=" + tmID[i].ToString() + " ORDER BY 排序"); string xx = ""; for (int j = 0; j < dt.Rows.Count; j++) { char ABC = (char)(65 + j); xx += ABC + ". " + dt.Rows[j]["内容"] + "\r\n"; } retext += (i + 1).ToString() + ") " + tm + "\r\n" + xx + "\r\n"; } string zqda = "\r\n\r\n正确答案:\r\n"; for (int i = 0; i < tmDA.Count; i++) { zqda += "(" + (i + 1).ToString() + "):"; for (int j = 0; j < tmDA[i].Length; j++) { if (tmDA[i][j] == 1) { zqda += (char)(65 + j) + " "; } } zqda += " "; } retext += "\r\n\r\n" + zqda; Sys.DebugMes(retext); context.Response.Write(retext); }
/// <summary> /// 获取试卷信息 /// </summary> /// <param name="context"></param> public void sjInfo(HttpContext context) { string ID = context.Request["ID"]; string retext = "{{ \"ok\":{0},\"msg\":\"{1}\",\"data\":[] }}"; string SQL = "SELECT [分类ID],[难度配置],[名称] FROM [CNGTZ].[dbo].[TK_试卷] where ID=" + ID; DAL dal = new DAL(); bool ok = false; string msg = ""; string[] x = dal.RunSqlStrings(SQL); x[0] = x[0].Substring(1, x[0].Length - 2); int all = 0;//题库总数 int sts = 0;//抽取试题数 try { SQL = "SELECT COUNT(*) as 总数 FROM [CNGTZ].[dbo].[TK_题目] where 所属分类 IN (" + x[0] + ")"; all = int.Parse(dal.RunSqlGetID(SQL)); List<int> nd = Json.Json_转换到对象<List<int>>(x[1]); sts = nd[0] + nd[1] + nd[2];//试题数 msg = "试卷名:" + x[2] + "<br>涉及题目数量:" + all.ToString() + "<br>抽取题目数量:" + sts.ToString(); ok = true; } catch (Exception e) { ok = false; msg = e.Message; } retext = string.Format(retext, ok.ToString().ToLower(), msg); Sys.DebugMes(retext); context.Response.Write(retext); }
//随机生成试题 private string _sjTextAdd(string ID) { string SQL = "SELECT [分类ID],[难度配置] FROM [CNGTZ].[dbo].[TK_试卷] where ID=" + ID; DAL dal = new DAL(); string[] x = dal.RunSqlStrings(SQL); x[0] = x[0].Substring(1, x[0].Length - 2); x[1] = x[1].Substring(1, x[1].Length - 2); DataTable dt1 = dal.RunSqlDataTable("SELECT [ID] FROM [CNGTZ].[dbo].[TK_题目] where 难度=1 and 所属分类 IN(" + x[0] + ") ");//简单题组 DataTable dt2 = dal.RunSqlDataTable("SELECT [ID] FROM [CNGTZ].[dbo].[TK_题目] where 难度=2 and 所属分类 IN(" + x[0] + ") ");//适中题组 DataTable dt3 = dal.RunSqlDataTable("SELECT [ID] FROM [CNGTZ].[dbo].[TK_题目] where 难度=3 and 所属分类 IN(" + x[0] + ") ");//困难题组 DataTable[] dt = new DataTable[] { dt1, dt2, dt3 }; string[] nds = x[1].Split(Convert.ToChar(",")); int[] nd = new int[3]; nd[0] = int.Parse(nds[0]); nd[1] = int.Parse(nds[1]); nd[2] = int.Parse(nds[2]); Random ra = new Random(unchecked((int)DateTime.Now.Ticks)); for (int i = 0; i < nd.Length; i++) { if (dt[i].Rows.Count > nd[i])//当试题组 题目数量>难度配置 题目数量 { int xhcs = dt[i].Rows.Count - nd[i]; for (int j = 0; j < xhcs; j++)//随机删除 多余行数 { int h = ra.Next(0, dt[i].Rows.Count); if (h >= 0 && h < dt[i].Rows.Count) { dt[i].Rows.RemoveAt(h); } } } } DataTable dtx = null; try { dal.TranBegin(); dal.ExeSql("create table #testst (id int)");//创建临时表 for (int i = 0; i < dt.Length; i++) { foreach (DataRow row in dt[i].Rows)//将 3组 (普通,适中,困难) 题目ID写入临时表 { SQL = "INSERT INTO #testst (id) VALUES(" + row["ID"].ToString() + ")"; dal.ExeSql(SQL); } } dtx = dal.ExeSqlDataTable("SELECT [ID] FROM [CNGTZ].[dbo].[TK_题目] where ID IN (SELECT id FROM #testst) ORDER BY 题类型,NEWID()");//随机排序后 生成DataTable dal.ExeSql("DROP TABLE #testst"); dal.TranCommit(); } catch (Exception) { dal.TranRollBack(); } string retext = ""; if (dtx != null) { foreach (DataRow row in dtx.Rows) { retext += row["id"].ToString() + ","; } retext = "[" + retext.Substring(0, retext.Length - 1) + "]"; } Sys.DebugMes(retext); return retext; }
/// <summary> /// 初始化数据 /// </summary> public void init(DateTime daystr, DateTime dayend, int days) { DAL dal = new DAL(); switch (Zd)//初始化 枪数 { case 2: Guns = (int)jiCuShuJu.ZdGuns.南高; break; case 3: Guns = (int)jiCuShuJu.ZdGuns.西路; break; case 4: Guns = (int)jiCuShuJu.ZdGuns.麻柳沱; break; } GunYuGou = new CW_YuGou_GunMod[Guns]; GongJiao = new CW_GongJiaoMod[days]; JiZhang = new CW_JiZhangDanWen[jiCuShuJu.记账单位数]; #region 第一步 统计站点 每枪的预购数据 //统计出 每枪的 预购数据 string SQL = @"select sum([NowGas]) as 合计气量,sum([NowMoney]) as 合计金额 from( SELECT * FROM [CSERVER].[dbo].[GAS] where [StationNO]=@站点@ and [GunNO]=@枪号@ and Payway='预购金额' and [TradeDate] between '@起始时间@' and '@结束时间@' union SELECT * FROM [CSERVER].[dbo].[TEMPGAS] where [StationNO]=@站点@ and [GunNO]=@枪号@ and Payway='预购金额' and [TradeDate] between '@起始时间@' and '@结束时间@' ) test"; double h1 = 0;//用于计算合计金额 double h2 = 0;//用于计算合计气量 for (int i = 0; i < Guns; i++) { //根据枪号、站点 取交接班时间 string str = ""; string end = ""; GunYuGou[i] = new CW_YuGou_GunMod(); if (Zd == 4) { str = ZDGAS.getBZstarttime(daystr, i + 1); end = ZDGAS.getBZendtime(dayend, i + 1); } if (Zd == 2 || Zd == 3) { str = ZDGAS.getBZstarttime(daystr, i + 1, Zd, false); end = ZDGAS.getBZendtime(dayend, i + 1, Zd, true); } if (str=="" || end=="") { Exception e = new Exception("交接班时间无法获取!"); throw e; } string sq = SQL.Replace("@站点@", Zd.ToString()); sq = sq.Replace("@枪号@", (i + 1).ToString()); sq = sq.Replace("@起始时间@", str); sq = sq.Replace("@结束时间@", end); string[] ss = dal.RunSqlStrings(sq); GunYuGou[i].GunStr = str; GunYuGou[i].GunEnd = end; GunYuGou[i].预购金额 = double.Parse(ss[1]); GunYuGou[i].预购气量 = double.Parse(ss[0]); h1 += GunYuGou[i].预购金额; h2 += GunYuGou[i].预购气量; } 合计预购金额 = Math.Round(h1, 2); 合计预购气量 = Math.Round(h2, 2); #endregion #region 第二部 统计站点 充值金额 SQL = @"SELECT sum(money) as 合计金额 FROM [CSERVER].[dbo].[Addmoney] where [StationNO]=@站点@ and [PurchaseDate] between '@起始时间@' and '@结束时间@'"; string s2 = SQL.Replace("@站点@", Zd.ToString()); s2 = s2.Replace("@起始时间@", daystr.ToShortDateString() + " 0:00:00"); s2 = s2.Replace("@结束时间@", dayend.ToShortDateString() + " 23:59:59"); 充值金额 = double.Parse(dal.RunSqlGetID(s2)); #endregion #region 第三步 统计出 公交公司每日明细 SQL = @"SELECT a.[日期],ISNULL(b.刷卡,0) as 刷卡,ISNULL(b.手工,0) as 手工,ISNULL(c.停业时间,0) as 停业 FROM [CNGQT].[dbo].[Temp_日期] a LEFT JOIN ( SELECT 日期,sum([刷卡记账气量]) as 刷卡,sum([手工记账气量]) as 手工 FROM [CNGQT].[dbo].[SC_记账明细] where 站点=@站点@ and 日期 between '@起始时间@' and '@结束时间@' AND 记账单位名称='公交公司' group BY 日期 ) b ON a.日期=b.日期 LEFT JOIN( SELECT [日期],SUM([累计小时]) as 停业时间 FROM [CNGQT].[dbo].[SC_停业时间] where 站点=@站点@ and 日期 between '@起始时间@' and '@结束时间@' group by 日期 ) c ON a.日期=c.日期"; string s3 = SQL.Replace("@站点@", Zd.ToString()); s3 = s3.Replace("@起始时间@", daystr.ToShortDateString()); s3 = s3.Replace("@结束时间@", dayend.ToShortDateString()); DataTable dt1 = dal.RunSqlDataTable(s3); for (int i = 0; i < dt1.Rows.Count; i++) { GongJiao[i] = new CW_GongJiaoMod(); GongJiao[i].日期 = DateTime.Parse(dt1.Rows[i]["日期"].ToString()).ToString("MM月dd日"); GongJiao[i].手工记账 = double.Parse(dt1.Rows[i]["手工"].ToString()); GongJiao[i].刷卡记账 = double.Parse(dt1.Rows[i]["刷卡"].ToString()); GongJiao[i].停业时间 = double.Parse(dt1.Rows[i]["停业"].ToString()); } #endregion #region 第四步 统计单位记账数据 SQL = @"SELECT a.[记账单位名称] ,isnull ( b.刷卡气量,0) as 刷卡 ,isnull ( b.手工气量,0) as 手工 ,isnull ( c.普通价格,0) as 价格 FROM [CNGQT].[dbo].[SC_记账单位名称] a LEFT JOIN ( SELECT [记账单位名称] ,sum([刷卡记账气量]) as 刷卡气量 ,sum([手工记账气量]) as 手工气量 FROM [CNGQT].[dbo].[SC_记账明细] where 站点=@站点@ AND 日期 BETWEEN '@起始时间@' and '@结束时间@' GROUP BY 记账单位名称 ) b ON a.记账单位名称=b.记账单位名称 LEFT JOIN ( select aa.单位名称,aa.普通价格 from [CNGQT].[dbo].SC_记账单位执行价格 aa where 起始时间 = ( select top 1 起始时间 from [CNGQT].[dbo].SC_记账单位执行价格 where 单位名称 = aa.单位名称 and 起始时间<='@起始时间@' ORDER BY aa.起始时间 DESC ) ) c ON c.单位名称=a.记账单位名称 ORDER BY a.编号"; string s4 = SQL.Replace("@站点@", Zd.ToString()); s4 = s4.Replace("@起始时间@", daystr.ToShortDateString()); s4 = s4.Replace("@结束时间@", dayend.ToShortDateString()); dt1 = dal.RunSqlDataTable(s4); for (int i = 0; i < dt1.Rows.Count; i++) { JiZhang[i] = new CW_JiZhangDanWen(); JiZhang[i].单位名称 = dt1.Rows[i]["记账单位名称"].ToString(); JiZhang[i].手工 = double.Parse(dt1.Rows[i]["手工"].ToString()); JiZhang[i].刷卡 = double.Parse(dt1.Rows[i]["刷卡"].ToString()); JiZhang[i].价格 = double.Parse(dt1.Rows[i]["价格"].ToString()); } #endregion #region 取 停业时间 SQL = @"SELECT ISNULL(sum([累计小时]),0) as 停业时间 FROM [CNGQT].[dbo].[SC_停业时间] where 站点=@站点@ and 日期 between '@起始时间@' and '@结束时间@'"; string s5 = SQL.Replace("@站点@", Zd.ToString()); s5 = s5.Replace("@起始时间@", daystr.ToShortDateString()); s5 = s5.Replace("@结束时间@", dayend.ToShortDateString()); string tysj = dal.RunSqlGetID(s5); 停业时间 = double.Parse(tysj); #endregion }
/// <summary> /// 返回 车辆信息 /// </summary> /// <param name="context"></param> public void GetCarInfo(HttpContext context) { string code = context.Request["code"]; string retext = "{\"carinfo\":@车辆对象@,\"qpinfo\":@气瓶对象@}"; string SQL = @"SELECT TOP 1 [ID] ,[车牌号] ,[使用登记证] ,[安装气瓶数量] ,[发证单位] ,convert(varchar(10),[发证时间],120) as 发证时间 ,[应用类型] ,[厂牌型号] ,[所属单位] ,[负责人] ,[车主姓名] ,[联系电话] ,[邮编] ,[联系地址] ,[发动机号] ,[车架号] ,[汽车厂家] ,[最后更新] FROM [CNGQT].[dbo].[DZ_全市车辆] where 车牌号='' AND 负责人<> '手工录入' ORDER BY 发证时间"; DAL dal = new DAL(); CarInfo car = new CarInfo(); string[] info = dal.RunSqlStrings(SQL); if (info != null) { car.车牌号 = info[1]; car.使用登记证号 = info[2]; car.发证单位 = info[4]; car.发证时间 = info[5]; car.应用类型 = info[6]; car.厂牌型号 = info[7]; car.所属单位 = info[8]; car.负责人 = info[9]; car.车主姓名 = info[10]; car.联系电话 = info[11]; car.邮编 = info[12]; car.联系地址 = info[13]; car.发动机号 = info[14]; car.车架号 = info[15]; car.汽车厂家 = info[16]; } SQL = @"SELECT [所属车辆] ,isnull([气瓶使用编号],'') as 气瓶使用编号 ,isnull([生产厂家],'') as 生产厂家 ,isnull([气瓶出厂编号],'') as 气瓶出厂编号 ,isnull([气瓶类型],'') as 气瓶类型 ,isnull([生产年月],'') as 生产年月 ,isnull([型号],'') as 型号 ,isnull([使用状态],'') as 使用状态 ,isnull(convert(varchar(10),[下次检验日期],120),'') as 下次检验日期 ,isnull(convert(varchar(10),[最后检验日期],120),'') as 最后检验日期 ,isnull(convert(varchar(10),[开始投用日期],120),'') as 开始投用日期 ,isnull(convert(varchar(10),[安装日期],120),'') as 安装日期 ,isnull([使用年限],0) as 使用年限 ,isnull([可充装次数],0) as 可充装次数 ,isnull([已充装次数],0) as 气瓶使用编号 ,isnull([检验单位],'') as 检验单位 ,isnull([检验状态],'') as 检验状态 ,isnull([安装单位],'') as 安装单位 ,isnull([安装许可],'') as 安装许可 ,isnull([安装监检单位],'') as 安装监检单位 ,isnull([安装监检许可],'') as 安装监检许可 ,isnull([重量],0) as 重量 ,isnull([容积],0) as 容积 ,isnull([壁厚],0) as 壁厚 ,isnull([压力],0) as 压力 ,isnull([材质],'') as 材质 ,isnull([安装位置],'') as 安装位置 ,[最后更新] FROM [CNGQT].[dbo].[DZ_全市气瓶] where 所属车辆='@所属车辆@'"; string rows = "{\"total\":28,\"rows\":[]}"; if (car.车牌号 != "") { SQL = SQL.Replace("@所属车辆@", car.车牌号); var dt = dal.RunSqlDataTable(SQL); if (dt != null) { rows = rows.Replace("[]", Json.TableToJson(dt)); } } retext = retext.Replace("@车辆对象@", Json.Json_转换到json(car)); retext = retext.Replace("@气瓶对象@", rows); context.Response.Write(retext); }
/// <summary> /// 更改错误数据 /// </summary> /// <param name="context"></param> public void Check_err(HttpContext context) { int zd = int.Parse(context.Request["zd"]);//站点 string kh = context.Request["kh"];//卡号 string lsh = context.Request["lsh"];//流水号 string rq = context.Request["rq"];//日期 string name = context.Request["name"];//操作用户 string zdIP = "", zdName = "", km = "", cp = ""; switch (zd) { case 2: zdIP = "192.168.173.3"; zdName = "南高"; break;//南高 case 3: zdIP = "192.168.172.23"; zdName = "江阳西路"; break;//江阳西路 case 4: zdIP = "192.168.171.3"; zdName = "麻柳沱"; break; //麻柳沱 case 5: zdIP = "192.168.174.3"; zdName = "华阳"; break; //华阳 default: break; } DAL dal = new DAL(); string SQL = "SELECT TOP 1 a.[UserCardNo],a.[CarNo],b.ICardName FROM [CSERVER].[dbo].[UserCards] a left JOIN [CSERVER].dbo.ICardType b ON a.ICardType=b.ICardType where a.UserCardNo=" + kh; string[] temp = dal.RunSqlStrings(SQL); if (temp != null) { km = temp[2].Trim();//取得卡名 cp = temp[1].Trim();//取的车牌号 } else { context.Response.Write("未找到对应车牌信息!"); return; } SQL = "UPDATE [cngms].[dbo].[PayGas] SET [CardName] = '" + km + "',[CarNo] = '" + cp + "' WHERE TradeDate='" + rq + "' AND [TradeNo]=" + lsh + " AND UserCardNo=" + kh; //更改站点数据 DAL Zdal = new DAL(zdIP);//用于连接站点数据库 if (Zdal.TestCon(2))//先测试连接 { Zdal.RunSql(SQL); } else { context.Response.Write("连接站点数据库错误! 更改失败!"); return; } //更改心数据库数据 SQL = "UPDATE [CSERVER].[dbo].[GAS] SET [CardName] ='" + km + "',[CarNO] = '" + cp + "' where StationNO=" + zd.ToString() + " AND TradeDate='" + rq + "' AND TradeNO=" + lsh + " AND UserCardNo=" + kh; dal.RunSql(SQL); SQL = "UPDATE [CSERVER].[dbo].[TEMPGAS] SET [CardName] ='" + km + "',[CarNO] = '" + cp + "' where StationNO=" + zd.ToString() + " AND TradeDate='" + rq + "' AND TradeNO=" + lsh + " AND UserCardNo=" + kh; dal.RunSql(SQL); //更改 充装记录数据 SQL = "UPDATE [CNGQT].[dbo].[CNG_充装检查] SET [卡名] ='" + km + "',[车牌号] ='" + cp + "',[是否完成]=0 where 站点=" + zd.ToString() + " and 日期='" + rq + "' AND 流水号=" + lsh + " AND 用户号=" + kh; dal.RunSql(SQL); string tt = "站点[{0}] 日期[{1}] 卡号[{2}] 流水号[{3}]"; tt = string.Format(tt, zd.ToString(), rq, kh, lsh); Sys.addSteps(name, tt, "修改错误数据"); context.Response.Write("所有数据更改完成,请检查数据是否成功更改!"); }
/// <summary> /// 调岗操作 /// </summary> /// <param name="context"></param> public void DG_code(HttpContext context) { string code = context.Request["code"]; string name = context.Request["name"]; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("@code", code); SqlParameter par2 = new SqlParameter("@name", name); string SQL = "SELECT [ID],[所属部门] FROM [CNGTZ].[dbo].[TZ_人员] where 姓名=@name"; string[] dqcode = dal.RunSqlStrings(SQL, new SqlParameter[] { par2 }); SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员] SET [所属部门] =@code,[更新时间] = getdate() WHERE 姓名=@name"; dal.RunSql(SQL, new SqlParameter[] { par1, par2 }); if (dqcode[1] == "02") {//如果是离职人员 调岗操作 SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_档案2] SET [离职日期] =null,[离职原因] ='',[更新时间] = getdate() WHERE ID=" + dqcode[0]; dal.RunSql(SQL); } if (dal.ExecuteRowCount > 0) { if (dqcode[1] == "02") {//如果是离职人员 调岗操作 SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_档案2] SET [离职日期] =null,[离职原因] ='',[更新时间] = getdate() WHERE ID=" + dqcode[0]; dal.RunSql(SQL); } //调岗成功 解除相关人员的IC卡绑定 SQL = "UPDATE [CNGTZ].[dbo].[IC_绑定] SET [人员] = '',[更新时间]=getdate() WHERE [人员]='" + name + "'"; dal.RunSql(SQL); context.Response.Write("true"); } else { context.Response.Write("false"); } }
//统计区间,分站点,每日数据 /// <summary> /// 分站点,每日分站点数据 /// </summary> public void CountBzDay() { string SQL = @"SELECT a.*,isnull(b.刷卡,0) as 公司刷卡,isnull(b.手工,0) as 公司手工,isnull(b.合计,0) as 公司合计 FROM ( SELECT [日期] ,sum([生产气量]) as 合计气量,sum([预购刷卡气量]) as IC刷卡,sum([记账刷卡气量]) as 刷卡记账,sum([手工记账气量]) as 手工记账,sum([现金消费气量])as 现金气量,sum([报损气量]) as 报损 FROM [CNGQT].[dbo].[SC_班销售统计] where 日期 between @起始时间 and @结束时间 and 站点=@站点 group by 日期 ) a LEFT JOIN ( SELECT [日期],sum([刷卡记账气量]) as 刷卡,sum([手工记账气量]) as 手工,sum([刷卡记账气量]+[手工记账气量]) as 合计 FROM [CNGQT].[dbo].[SC_记账明细] where 日期 between @起始时间 and @结束时间 and 站点=@站点 and [记账单位名称]='华润兴泸' group by [日期] ) b on a.[日期]=b.[日期] order by a.日期"; int zd = 0; SqlParameter str = new SqlParameter("起始时间", Str); SqlParameter end = new SqlParameter("结束时间", End); DAL dal = new DAL(); Sys.DebugMes(SQL); #region 第一步:生成每个站点的统计数据 for (int i = 0; i < zds; i++) //循环统计站点数据 { zd = MyDayGas[i].Zd; SqlParameter par1 = new SqlParameter("站点", MyDayGas[i].Zd); DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { str, end, par1 }); for (int j = 0; j < dt.Rows.Count; j++) { //MyDayGas[i].DayGas[j] = new CountZdGasMod();//初始化对象 if (dt.Rows[j]["日期"].ToString() != "") { MyDayGas[i].DayGas[j].日期 = DateTime.Parse(dt.Rows[j]["日期"].ToString()); } if (dt.Rows[j]["合计气量"].ToString() != "") { MyDayGas[i].DayGas[j].总生产气量 = double.Parse(dt.Rows[j]["合计气量"].ToString()); } if (dt.Rows[j]["IC刷卡"].ToString() != "") { MyDayGas[i].DayGas[j].IC卡刷卡 = double.Parse(dt.Rows[j]["IC刷卡"].ToString()); } if (dt.Rows[j]["刷卡记账"].ToString() != "") { MyDayGas[i].DayGas[j].IC卡记账 = double.Parse(dt.Rows[j]["刷卡记账"].ToString()); } if (dt.Rows[j]["手工记账"].ToString() != "") { MyDayGas[i].DayGas[j].手工记账 = double.Parse(dt.Rows[j]["手工记账"].ToString()); } if (dt.Rows[j]["现金气量"].ToString() != "") { MyDayGas[i].DayGas[j].现金气量 = double.Parse(dt.Rows[j]["现金气量"].ToString()); } if (dt.Rows[j]["报损"].ToString() != "") { MyDayGas[i].DayGas[j].维修损耗 = double.Parse(dt.Rows[j]["报损"].ToString()); } if (dt.Rows[j]["公司刷卡"].ToString() != "") { MyDayGas[i].DayGas[j].公司刷卡 = double.Parse(dt.Rows[j]["公司刷卡"].ToString()); } if (dt.Rows[j]["公司合计"].ToString() != "") { MyDayGas[i].DayGas[j].公司合计 = double.Parse(dt.Rows[j]["公司合计"].ToString()); } if (dt.Rows[j]["公司手工"].ToString() != "") { MyDayGas[i].DayGas[j].公司手工 = double.Parse(dt.Rows[j]["公司手工"].ToString()); } if (zd == 3) {//西路 扣除 公司内部用气 MyDayGas[i].DayGas[j].手工记账 = MyDayGas[i].DayGas[j].手工记账 - MyDayGas[i].DayGas[j].公司手工; MyDayGas[i].DayGas[j].IC卡记账 = MyDayGas[i].DayGas[j].IC卡记账 - MyDayGas[i].DayGas[j].公司刷卡; MyDayGas[i].DayGas[j].内部用气 = MyDayGas[i].DayGas[j].公司合计; } } } #endregion #region 第二步: 生成月度 数据 //SQL1:取月度计划数, 修正原因 SQL2:取累计购进,销售,SQL3:上年销售 SQL4:停业时间 SQL5:分类停业时间 string SQL1 = "SELECT 计划,修正值 FROM [CNGQT].[dbo].[CW_月度统计] where 年=" + year.ToString() + " and 月=" + yue.ToString() + " and 站点=@站点"; string SQL2 = "SELECT sum([购进数] + [修正值]) as 购进累计,sum([生产气量] - [报损气量]) as 生产累计 FROM [CNGQT].[dbo].[CW_月度统计] where 年=" + year.ToString() + "and 月 between 1 and " + yue.ToString() + " and 站点=@站点"; string SQL3 = "SELECT sum([生产气量] - [报损气量]) as 生产累计 FROM [CNGQT].[dbo].[CW_月度统计] where 年=" + (year - 1).ToString() + "and 月= " + yue.ToString() + " and 站点=@站点"; string SQL4 = "select sum(累计小时)as 停业累计 FROM [CNGQT].[dbo].[SC_停业时间] where 日期 BETWEEN @起始时间 AND @结束时间 and 站点=@站点"; string SQL5 = "select 分类,sum(累计小时)as 停业累计 FROM [CNGQT].[dbo].[SC_停业时间] where 日期 BETWEEN @起始时间 AND @结束时间 and 站点=@站点 GROUP BY 分类"; for (int i = 0; i < MyYueGas.Length; i++) { SqlParameter par1 = new SqlParameter("站点", MyYueGas[i].Zd); string[] jg = dal.RunSqlStrings(SQL1, new SqlParameter[] { par1 }); if (jg != null) { MyYueGas[i].YueGas.月度计划 = (jg[0] != "") ? double.Parse(jg[0]) : 0; MyYueGas[i].YueGas.累计修正 = (jg[1] != "") ? double.Parse(jg[1]) : 0; } jg = dal.RunSqlStrings(SQL2, new SqlParameter[] { par1 }); if (jg != null) { MyYueGas[i].YueGas.累计购进 = (jg[0] != "") ? double.Parse(jg[0]) : 0; MyYueGas[i].YueGas.累计销售 = (jg[1] != "") ? double.Parse(jg[1]) : 0; } jg = dal.RunSqlStrings(SQL3, new SqlParameter[] { par1 }); if (jg != null) { MyYueGas[i].YueGas.上年销售 = (jg[0] != "") ? double.Parse(jg[0]) : 0; } SqlParameter par2 = new SqlParameter("起始时间", Str); SqlParameter par3 = new SqlParameter("结束时间", End); jg = dal.RunSqlStrings(SQL4, new SqlParameter[] { par1, par2, par3 }); if (jg != null) { MyYueGas[i].YueGas.停运时间 = (jg[0] != "") ? double.Parse(jg[0]) : 0; } DataTable dt = dal.RunSqlDataTable(SQL5, new SqlParameter[] { par1, par2, par3 }); if (jg != null) { string temp = ""; foreach (DataRow row in dt.Rows) { temp += row["分类"].ToString() + "[" + row["停业累计"].ToString() + "];"; } MyYueGas[i].YueGas.备注 = temp; } } #endregion #region 第三步:生成电量,水量,购进气量 CountDayGoujinDianSui();//生成电量,水量,购进气量 #endregion }