protected void Page_Load(object sender, EventArgs e) { try { string strOperationDate = DateTime.Now.ToString("yyyy-MM-dd"); strOperationDate = "2015-12-07"; string strSql = string.Format(@"select * from ( select a.DEVICE,a.TIMEPAI,a.TIMEWAN,a.DQBW,b.DATANAME,a.CHI_VESSEL,a.ENG_VESSEL,a.LOA,a.TON_DEAD,a.NAME,a.TRADENAME,a.GUOJI,a.YHY,TO_CHAR(a.DATEOFCREATE, 'YYYY-MM-DD') as DATEOFCREATE from LBGSDD.CVW_SCHEDULE_MOBILE a, LBGSDD.LVW_WORKTYPE2 b where a.WORKTYPE=b.DATACODE(+) order by DATEOFCREATE desc) where DATEOFCREATE='{0}'", strOperationDate); var dt = new Leo.Oracle.DataAccess(RegistryKey.KeyPathIport).ExecuteTable(strSql); if (dt.Rows.Count <= 0) { Json = JsonConvert.SerializeObject(new DicPackage(false, null, "暂无数据!").DicInfo()); return; } string[] strNameArray = { "拖轮", "开始时间", "结束时间", "泊位", "作业类型", "中文船名", "英文船名", "船长", "载重吨", "船代", "内外贸", "国籍", "引航员", "创建时间" }; //排序字符串 string strOrder = string.Empty; strOrder = strNameArray[0] + "+" + strNameArray[1] + "+" + strNameArray[2] + "+" + strNameArray[3] + "+" + strNameArray[4] + "+" + strNameArray[5] + "+" + strNameArray[6] + "+" + strNameArray[7] + "+" + strNameArray[8] + "+" + strNameArray[9] + "+" + strNameArray[10] + "+" + strNameArray[11] + "+" + strNameArray[12] + "+" + strNameArray[13]; Dictionary <string, string> info = new Dictionary <string, string>(); info.Add(strNameArray[0], Convert.ToString(dt.Rows[0]["DEVICE"])); info.Add(strNameArray[1], StringTool.ToDayNightForSql(Convert.ToString(dt.Rows[0]["TIMEPAI"]))); info.Add(strNameArray[2], StringTool.ToDayNightForSql(Convert.ToString(dt.Rows[0]["TIMEWAN"]))); info.Add(strNameArray[3], Convert.ToString(dt.Rows[0]["DQBW"])); info.Add(strNameArray[4], Convert.ToString(dt.Rows[0]["DATANAME"])); info.Add(strNameArray[5], Convert.ToString(dt.Rows[0]["CHI_VESSEL"])); info.Add(strNameArray[6], Convert.ToString(dt.Rows[0]["ENG_VESSEL"])); info.Add(strNameArray[7], Convert.ToString(dt.Rows[0]["LOA"])); info.Add(strNameArray[8], Convert.ToString(dt.Rows[0]["TON_DEAD"])); info.Add(strNameArray[9], Convert.ToString(dt.Rows[0]["NAME"])); info.Add(strNameArray[10], Convert.ToString(dt.Rows[0]["TRADENAME"])); info.Add(strNameArray[11], Convert.ToString(dt.Rows[0]["GUOJI"])); info.Add(strNameArray[12], Convert.ToString(dt.Rows[0]["YHY"])); info.Add(strNameArray[13], Convert.ToString(dt.Rows[0]["DATEOFCREATE"])); info.Add("Order", strOrder); Json = JsonConvert.SerializeObject(new DicPackage(true, info, null).DicInfo()); } catch (Exception ex) { Json = JsonConvert.SerializeObject(new DicPackage(false, null, string.Format("{0}:获取数据发生异常。{1}", ex.Source, ex.Message)).DicInfo()); } }
protected void Page_Load(object sender, EventArgs e) { //数据起始行 var startRow = Request.Params["StartRow"]; //行数 var count = Request.Params["Count"]; try { if (startRow == null || count == null) { string warning = string.Format("参数StartRow,Count不能为null!举例:http://218.92.115.55/M_Zslb/Service/Operation/GetMonthOperationDetails.aspx?StartRow=1&Count=14"); Json = JsonConvert.SerializeObject(new DicPackage(false, null, warning).DicInfo()); return; } DateTime curDate = DateTime.Now; DateTime startDate = new DateTime(curDate.Year, curDate.Month, 1); DateTime endDate = startDate.AddMonths(1).AddDays(-1); string strStartDate = startDate.ToString("yyyy-MM-dd"); string strEndDate = endDate.ToString("yyyy-MM-dd"); //strStartDate = "2015-11-01"; //strEndDate = "2015-11-30"; string[,] strArray = null; string strSql = string.Format(@"select * from ( select a.DEVICE,a.TIMEPAI,a.TIMEWAN,a.DQBW,b.DATANAME,a.CHI_VESSEL,a.ENG_VESSEL,a.LOA,a.TON_DEAD,a.NAME,a.TRADENAME,a.GUOJI,a.YHY,TO_CHAR(a.DATEOFCREATE, 'YYYY-MM-DD') as DATEOFCREATE from LBGSDD.CVW_SCHEDULE_MOBILE a, LBGSDD.LVW_WORKTYPE2 b where a.WORKTYPE=b.DATACODE(+)) where DATEOFCREATE>='{0}' and DATEOFCREATE<='{1}' order by DATEOFCREATE desc", strStartDate, strEndDate); var dt = new Leo.Oracle.DataAccess(RegistryKey.KeyPathIport).ExecuteTable(strSql, Convert.ToInt32(startRow), Convert.ToInt32(startRow) + Convert.ToInt32(count) - 1); if (dt.Rows.Count <= 0) { Json = JsonConvert.SerializeObject(new DicPackage(false, null, "暂无数据!").DicInfo()); return; } strArray = new string[dt.Rows.Count, 14]; for (int iRow = 0; iRow < dt.Rows.Count; iRow++) { strArray[iRow, 0] = Convert.ToString(dt.Rows[iRow]["DEVICE"]); strArray[iRow, 1] = StringTool.ToDayNightForSql(Convert.ToString(dt.Rows[0]["TIMEPAI"])); strArray[iRow, 2] = StringTool.ToDayNightForSql(Convert.ToString(dt.Rows[0]["TIMEWAN"])); strArray[iRow, 3] = Convert.ToString(dt.Rows[iRow]["DQBW"]); strArray[iRow, 4] = Convert.ToString(dt.Rows[iRow]["DATANAME"]); strArray[iRow, 5] = Convert.ToString(dt.Rows[iRow]["CHI_VESSEL"]); strArray[iRow, 6] = Convert.ToString(dt.Rows[iRow]["ENG_VESSEL"]); strArray[iRow, 7] = Convert.ToString(dt.Rows[iRow]["LOA"]); strArray[iRow, 8] = Convert.ToString(dt.Rows[iRow]["TON_DEAD"]); strArray[iRow, 9] = Convert.ToString(dt.Rows[iRow]["NAME"]); strArray[iRow, 10] = Convert.ToString(dt.Rows[iRow]["TRADENAME"]); strArray[iRow, 11] = Convert.ToString(dt.Rows[iRow]["GUOJI"]); strArray[iRow, 12] = Convert.ToString(dt.Rows[iRow]["YHY"]); strArray[iRow, 13] = Convert.ToString(dt.Rows[iRow]["DATEOFCREATE"]); } Json = JsonConvert.SerializeObject(new DicPackage(true, strArray, null).DicInfo()); } catch (Exception ex) { Json = JsonConvert.SerializeObject(new DicPackage(false, null, string.Format("{0}:获取数据发生异常。{1}", ex.Source, ex.Message)).DicInfo()); } }
protected void Page_Load(object sender, EventArgs e) { //委托编码 var cgno = Request.Params["Cgno"]; //公司编码 var codeDepartment = Request.Params["CodeDepartment"]; //白夜班 var dayNight = Request.Params["DayNight"]; //班组日期 var teamDate = Request.Params["TeamDate"]; //cgno = "1511099525"; //codeDepartment = "11"; //dayNight = "白班"; //teamDate = "2016-01-08"; try { if (cgno == null || codeDepartment == null || dayNight == null || teamDate == null) { string warning = string.Format("参数Cgno,CodeDepartment,TeamDate不能为null!举例:http://218.92.115.55/M_DL_Gwtxc/Service/Weighing/GetWeighting.aspx?Cgno=0114111611&CodeDepartment=11&DayNight=白班&TeamDate=2015-08-19"); Json = JsonConvert.SerializeObject(new DicPackage(false, null, warning).DicInfo()); return; } // 每日过磅统计。 string VwDailyCount = "select consign,department,departmentname,client,ship,billnumber1,cargo,weight,sum(weight2-weight1) netweight,count(truck) trucknumber from BALANCECENTER..vw_metages "; // 每日过磅统计(累计)。 string VwDailyCount2 = " select consign,sum(weight2-weight1) netweight,count(truck) trucknumber from BALANCECENTER..vw_metages "; //获取班次统计时间 string strSql = string.Format("select DayTime,NightTime from BALANCECENTER..RunConfig where Department = {0}", codeDepartment); var dt = new Leo.SqlServer.DataAccess(RegistryKey.KeyPathBc).ExecuteTable(strSql); string day = " 06:00"; string night = " 17:00"; if (dt.Rows.Count > 0) { day = StringTool.ToDayNightForSql(dt.Rows[0]["DayTime"].ToString()); night = StringTool.ToDayNightForSql(dt.Rows[0]["NightTime"].ToString()); } //拼接查询条件 string strFilter; if (dayNight == "白班")// { strFilter = string.Format( "department='{0}' and FinishTime is not null and finishtime<'{1}' and finishtime>='{2}' and consign='{3}'", codeDepartment, string.Concat(Convert.ToDateTime(teamDate).ToShortDateString(), night), string.Concat(Convert.ToDateTime(teamDate).ToShortDateString(), day), cgno); } else { strFilter = string.Format( "department='{0}' and FinishTime is not null and ((finishtime>='{1}' and finishtime<='{2}') or (finishtime>='{3}' and finishtime<'{4}')) and consign='{5}'", codeDepartment, string.Concat(Convert.ToDateTime(teamDate).ToShortDateString(), night), string.Concat(Convert.ToDateTime(teamDate).AddDays(1).ToShortDateString(), " 00:00"), string.Concat(Convert.ToDateTime(teamDate).AddDays(1).ToShortDateString(), " 00:00"), string.Concat(Convert.ToDateTime(teamDate).AddDays(1).ToShortDateString(), day), cgno); } strSql = string.Format( "{0} where {1} group by consign,department,departmentname,client,ship,billnumber1,cargo,weight", VwDailyCount, strFilter); var dt0 = new Leo.SqlServer.DataAccess(RegistryKey.KeyPathBc).ExecuteTable(strSql); strSql = string.Format(" {0} where consign='{1}' group by consign ", VwDailyCount2, cgno); var dt1 = new Leo.SqlServer.DataAccess(RegistryKey.KeyPathBc).ExecuteTable(strSql); if (dt1.Rows.Count <= 0) { Json = JsonConvert.SerializeObject(new DicPackage(false, null, "委托号不存在!").DicInfo()); return; } string[] strNameArray = { "委托号", "船名", "委托人", "货种", "计划重量", "当班量", "累计量", "当班车次", "累计车次", "提单号", "公司", "班组日期", "白夜班" }; //数组排序连接 string strOrderLink = StringTool.ArrayToString(strNameArray); Dictionary <string, object> info = new Dictionary <string, object>(); info.Add(strNameArray[0], Convert.ToString(dt0.Rows[0]["consign"])); info.Add(strNameArray[1], Convert.ToString(dt0.Rows[0]["ship"])); info.Add(strNameArray[2], Convert.ToString(dt0.Rows[0]["client"])); info.Add(strNameArray[3], Convert.ToString(dt0.Rows[0]["cargo"])); info.Add(strNameArray[4], Convert.ToString(dt0.Rows[0]["weight"])); info.Add(strNameArray[5], Convert.ToString(dt0.Rows[0]["netweight"])); info.Add(strNameArray[6], Convert.ToString(dt1.Rows[0]["NETWEIGHT"])); info.Add(strNameArray[7], Convert.ToString(dt0.Rows[0]["trucknumber"])); info.Add(strNameArray[8], Convert.ToString(dt1.Rows[0]["TRUCKNUMBER"])); info.Add(strNameArray[9], Convert.ToString(dt0.Rows[0]["billnumber1"])); info.Add(strNameArray[10], Convert.ToString(dt0.Rows[0]["departmentname"])); info.Add(strNameArray[11], teamDate); info.Add(strNameArray[12], dayNight); info.Add("Order", strOrderLink); Json = JsonConvert.SerializeObject(new DicPackage(true, info, null).DicInfo()); } catch (Exception ex) { Json = JsonConvert.SerializeObject(new DicPackage(false, null, string.Format("{0}:获取数据发生异常。{1}", ex.Source, ex.Message)).DicInfo()); } }
protected void Page_Load(object sender, EventArgs e) { //数据起始行 var startRow = Request.Params["StartRow"]; //行数 var count = Request.Params["Count"]; //用户编码 var codeUser = Request.Params["CodeUser"]; //理货日期 var tallyDate = Request.Params["TallyDate"]; //白夜班 var dayNight = Request.Params["DayNight"]; tallyDate = tallyDate == null ? string.Empty : tallyDate; dayNight = dayNight == null ? string.Empty : dayNight; dayNight = "白班"; //tallyDate = "2016-01-07"; codeUser = "******"; //startRow = "1"; //count = "15"; //cargo = "煤炭"; //taskNo = "北京弘帆"; try { if (startRow == null || count == null || codeUser == null) { string warning = string.Format("参数StartRow,Count,CodeUser不能为null!举例:http://218.92.115.55/M_DL_Gwtxc/Service/Slip/GetMissionCommand.aspx?StartRow=1&Count=14&CodeUser=71B4CD89E6654D84B8AED9116DDB1C4C"); Json = JsonConvert.SerializeObject(new DicPackage(false, null, warning).DicInfo()); return; } string strFilter = string.Empty; string strTable = "vw_ps_mission_ma a"; if (!string.IsNullOrWhiteSpace(codeUser)) { strFilter += string.Format("a.code_tallyman2='{0}' and ", codeUser); } if (!string.IsNullOrWhiteSpace(tallyDate)) { strFilter += string.Format("a.tallydate=to_date('{0}', 'yyyy/mm/dd') and ", Convert.ToDateTime(tallyDate).ToShortDateString()); } if (!string.IsNullOrWhiteSpace(dayNight)) { dayNight = dayNight == "白班" ? "1" : "2"; strFilter += string.Format("a.code_worktime='{0}' and ", dayNight); } if (!string.IsNullOrWhiteSpace(strFilter)) { strFilter = strFilter.Remove(strFilter.Length - 5, 5); strFilter = " where " + strFilter; } string strSql = string.Format(@"select a.pmno,a.cgno,a.client,a.taskno,a.cargo,a.operation,a.planweight,a.begintime,a.endtime,a.carrier1,a.carrier2 from {0}{1} order by a.tallydate desc", strTable, strFilter); var dt = new Leo.Oracle.DataAccess(RegistryKey.KeyPathNewHarbor).ExecuteTable(strSql, Convert.ToInt32(startRow), Convert.ToInt32(startRow) + Convert.ToInt32(count) - 1); if (dt.Rows.Count <= 0) { string strWarning = startRow == "1" ? "暂无数据!" : "暂无更多数据!"; Json = JsonConvert.SerializeObject(new DicPackage(false, null, strWarning).DicInfo()); return; } string[,] strArray = new string[dt.Rows.Count, 14]; for (int iRow = 0; iRow < dt.Rows.Count; iRow++) { strArray[iRow, 0] = Convert.ToString(dt.Rows[iRow]["pmno"]); strArray[iRow, 1] = Convert.ToString(dt.Rows[iRow]["cgno"]); strArray[iRow, 2] = string.Empty; strArray[iRow, 3] = Convert.ToString(dt.Rows[iRow]["client"]); strArray[iRow, 4] = Convert.ToString(dt.Rows[iRow]["taskno"]); strArray[iRow, 5] = Convert.ToString(dt.Rows[iRow]["cargo"]); strArray[iRow, 6] = Convert.ToString(dt.Rows[iRow]["operation"]); strArray[iRow, 7] = Convert.ToString(dt.Rows[iRow]["planweight"]); strArray[iRow, 8] = string.IsNullOrWhiteSpace(Convert.ToString(dt.Rows[iRow]["begintime"])) == true ? string.Empty : StringTool.ToDayNightForSql(Convert.ToString(dt.Rows[iRow]["begintime"])); strArray[iRow, 9] = string.IsNullOrWhiteSpace(Convert.ToString(dt.Rows[iRow]["endtime"])) == true ? string.Empty : StringTool.ToDayNightForSql(Convert.ToString(dt.Rows[iRow]["endtime"])); strArray[iRow, 10] = Convert.ToString(dt.Rows[iRow]["carrier1"]); strArray[iRow, 11] = Convert.ToString(dt.Rows[iRow]["carrier2"]); strArray[iRow, 12] = string.Empty; strArray[iRow, 13] = string.Empty; } Json = JsonConvert.SerializeObject(new DicPackage(true, strArray, null).DicInfo()); } catch (Exception ex) { Json = JsonConvert.SerializeObject(new DicPackage(false, null, string.Format("{0}:获取数据发生异常。{1}", ex.Source, ex.Message)).DicInfo()); } }
protected void Page_Load(object sender, EventArgs e) { //数据起始行 var startRow = Request.Params["StartRow"]; //行数 var count = Request.Params["Count"]; //公司编码 var codeDepartment = Request.Params["CodeDepartment"]; //白夜班 var dayNight = Request.Params["DayNight"]; //班组日期 var teamDate = Request.Params["TeamDate"]; codeDepartment = "11"; //startRow = "1"; //count = "30"; //dayNight = "白班"; //teamDate = "2015-09-22"; try { if (startRow == null || count == null || codeDepartment == null || dayNight == null || teamDate == null) { string warning = string.Format("参数StartRow,Count,CodeDepartment,TeamDate不能为null!举例:http://218.92.115.55/M_Lhgl/Service/Weighing/GetWeighting.aspx?StartRow=1&Count=14&CodeDepartment=11&DayNight=白班&TeamDate=2015-08-19"); Json = JsonConvert.SerializeObject(new DicPackage(false, null, warning).DicInfo()); return; } // 每日过磅统计。 string VwDailyCount = "select consign,department,departmentname,client,ship,billnumber1,cargo,weight,sum(weight2-weight1) netweight,count(truck) trucknumber from BALANCECENTER..vw_metages "; //获取班次统计时间 string strSql = string.Format("select DayTime,NightTime from BALANCECENTER..RunConfig where Department = {0}", codeDepartment); var dt = new Leo.SqlServer.DataAccess(RegistryKey.KeyPathBc).ExecuteTable(strSql); string day = " 06:00"; string night = " 17:00"; if (dt.Rows.Count > 0) { day = StringTool.ToDayNightForSql(dt.Rows[0]["DayTime"].ToString()); night = StringTool.ToDayNightForSql(dt.Rows[0]["NightTime"].ToString()); } //拼接查询条件 string strFilter; if (dayNight == "白班")// { strFilter = string.Format( "department='{0}' and FinishTime is not null and finishtime<'{1}' and finishtime>='{2}' ", codeDepartment, string.Concat(Convert.ToDateTime(teamDate).ToShortDateString(), night), string.Concat(Convert.ToDateTime(teamDate).ToShortDateString(), day)); } else { strFilter = string.Format( "department='{0}' and FinishTime is not null and ((finishtime>='{1}' and finishtime<='{2}') or (finishtime>='{3}' and finishtime<'{4}')) ", codeDepartment, string.Concat(Convert.ToDateTime(teamDate).ToShortDateString(), night), string.Concat(Convert.ToDateTime(teamDate).AddDays(1).ToShortDateString(), " 00:00"), string.Concat(Convert.ToDateTime(teamDate).AddDays(1).ToShortDateString(), " 00:00"), string.Concat(Convert.ToDateTime(teamDate).AddDays(1).ToShortDateString(), day)); } //获取当班数据、累计数据 拼入一个DataTab strSql = string.Format( "{0} where {1} group by consign,department,departmentname,client,ship,billnumber1,cargo,weight ", VwDailyCount, strFilter); dt = new Leo.SqlServer.DataAccess(RegistryKey.KeyPathBc).ExecuteTable(strSql, Convert.ToInt32(startRow), Convert.ToInt32(startRow) + Convert.ToInt32(count) - 1); string[,] strArray = new string[dt.Rows.Count, 6]; for (int iRow = 0; iRow < dt.Rows.Count; iRow++) { strArray[iRow, 0] = Convert.ToString(dt.Rows[iRow]["consign"]); strArray[iRow, 1] = Convert.ToString(dt.Rows[iRow]["ship"]); strArray[iRow, 2] = Convert.ToString(dt.Rows[iRow]["client"]); strArray[iRow, 3] = Convert.ToString(dt.Rows[iRow]["cargo"]); strArray[iRow, 4] = Convert.ToString(dt.Rows[iRow]["weight"]); strArray[iRow, 5] = Convert.ToString(dt.Rows[iRow]["netweight"]); } Json = JsonConvert.SerializeObject(new DicPackage(true, strArray, null).DicInfo()); } catch (Exception ex) { Json = JsonConvert.SerializeObject(new DicPackage(false, null, string.Format("{0}:获取数据发生异常。{1}", ex.Source, ex.Message)).DicInfo()); } }
protected void Page_Load(object sender, EventArgs e) { //用户编码 var codeUser = Request.Params["CodeUser"]; codeUser = "******"; //startRow = "1"; //count = "15"; try { if (codeUser == null) { string warning = string.Format("参数CodeUser不能为null!举例:http://218.92.115.55/M_ZJG_Dzqp/Service/Slip/GetUncommitedMachineCommand.aspx?CodeUser=2A2CD3375A394D8889E4A3C3A3817DC4"); Json = JsonConvert.SerializeObject(new DicPackage(false, null, warning).DicInfo()); return; } string strSql = string.Format(@"select pmno,cgno,client,taskno,cargo,operation,planweight,begintime,endtime,carrier1,carrier2,tallydate,code_tallyman2 from (select a.pmno,a.cgno,a.client,a.taskno,a.cargo,a.operation,a.planweight,a.begintime,a.endtime,a.carrier1,a.carrier2,a.tallydate,a.code_tallyman2 from vw_ps_mission_ma a where not exists(select * from vw_reg_tallybill b where a.pmno = b.pmno and a.cgno = b.cgno and b.mark_finish=1)) where code_tallyman2='{0}' order by tallydate desc", codeUser); var dt = new Leo.Oracle.DataAccess(RegistryKey.KeyPathZHarbor).ExecuteTable(strSql); if (dt.Rows.Count <= 0) { Json = JsonConvert.SerializeObject(new DicPackage(false, null, "暂无数据!").DicInfo()); return; } string[,] strArray = new string[dt.Rows.Count, 12]; for (int iRow = 0; iRow < dt.Rows.Count; iRow++) { strArray[iRow, 0] = Convert.ToString(dt.Rows[iRow]["pmno"]); strArray[iRow, 1] = Convert.ToString(dt.Rows[iRow]["cgno"]); strArray[iRow, 2] = string.Empty; strArray[iRow, 3] = Convert.ToString(dt.Rows[iRow]["client"]); strArray[iRow, 4] = Convert.ToString(dt.Rows[iRow]["taskno"]); strArray[iRow, 5] = Convert.ToString(dt.Rows[iRow]["cargo"]); strArray[iRow, 6] = Convert.ToString(dt.Rows[iRow]["operation"]); strArray[iRow, 7] = Convert.ToString(dt.Rows[iRow]["planweight"]); strArray[iRow, 8] = string.IsNullOrWhiteSpace(Convert.ToString(dt.Rows[iRow]["begintime"])) == true ? string.Empty : StringTool.ToDayNightForSql(Convert.ToString(dt.Rows[iRow]["begintime"])); strArray[iRow, 9] = string.IsNullOrWhiteSpace(Convert.ToString(dt.Rows[iRow]["endtime"])) == true ? string.Empty : StringTool.ToDayNightForSql(Convert.ToString(dt.Rows[iRow]["endtime"])); strArray[iRow, 10] = Convert.ToString(dt.Rows[iRow]["carrier1"]); strArray[iRow, 11] = Convert.ToString(dt.Rows[iRow]["carrier2"]); //strArray[iRow, 12] = string.Empty; //strArray[iRow, 13] = string.Empty; } Json = JsonConvert.SerializeObject(new DicPackage(true, strArray, null).DicInfo()); } catch (Exception ex) { Json = JsonConvert.SerializeObject(new DicPackage(false, null, string.Format("{0}:获取数据发生异常。{1}", ex.Source, ex.Message)).DicInfo()); } }