public String GetGasUserBill(string userId, string companyId, string month) { OlbGasFeeBill gasFeeBill = null; WCFServiceProxy <IChaoBiao> proxy = null; try { proxy = new WCFServiceProxy <IChaoBiao>(); View_UserMeterHistory thisRecord = proxy.getChannel.GetMonthLastRecord(userId, companyId, month); string thisMonth = month + "-01"; string lastMonth = Convert.ToDateTime(thisMonth).AddMonths(-1).ToString("yyyy-MM"); View_UserMeterHistory lastRecord = proxy.getChannel.GetMonthLastRecord(userId, companyId, lastMonth); if (thisRecord != null && lastRecord != null) { gasFeeBill = new OlbGasFeeBill(); gasFeeBill.ChaoBiaoTime = (DateTime)thisRecord.ReadDate; gasFeeBill.UserID = thisRecord.UserID; gasFeeBill.UserName = thisRecord.UserName; gasFeeBill.LastNum = (decimal)lastRecord.TotalAmount; gasFeeBill.ThisNum = (decimal)thisRecord.TotalAmount; gasFeeBill.GasNum = (decimal)thisRecord.TotalAmount - (decimal)lastRecord.TotalAmount; gasFeeBill.GasFee = (decimal)lastRecord.RemainingAmount - (decimal)thisRecord.RemainingAmount; } } catch (Exception ex) { } finally { if (proxy != null) { proxy.CloseChannel(); } } return(JsToJson.SerializeToJsonString(gasFeeBill)); }
public View_UserMeterHistory GetMonthLastRecord(string userId, string companyId, string month) { View_UserMeterHistory record = null; string thisMonth = month + "-01"; string nextMonth = Convert.ToDateTime(thisMonth).AddMonths(1).ToString("yyyy-MM-dd"); string sql = "select top 1 * from View_UserMeterHistory where UserID='" + userId + "' and CompanyID='" + companyId + "' and ReadDate >'" + thisMonth + "' and ReadDate<'" + nextMonth + "' order by ReadDate desc "; DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.SchuleConnection, CommandType.Text, sql); List <View_UserMeterHistory> list = new List <View_UserMeterHistory>(); if (ds.Tables[0].Rows.Count > 0) { record = new View_UserMeterHistory(); record.CompanyID = ds.Tables[0].Rows[0]["CompanyID"].ToString(); record.UserID = ds.Tables[0].Rows[0]["UserID"].ToString(); record.UserName = ds.Tables[0].Rows[0]["UserName"].ToString(); record.State = Convert.ToChar(ds.Tables[0].Rows[0]["State"].ToString()); record.Address = ds.Tables[0].Rows[0]["Address"].ToString(); record.Street = ds.Tables[0].Rows[0]["Street"].ToString(); record.Community = ds.Tables[0].Rows[0]["Community"].ToString(); record.Door = ds.Tables[0].Rows[0]["Door"].ToString(); record.MeterNo = ds.Tables[0].Rows[0]["MeterNo"].ToString(); record.MeterType = ds.Tables[0].Rows[0]["MeterType"].ToString(); record.ValveState = Convert.ToChar(ds.Tables[0].Rows[0]["ValveState"].ToString()); record.LastTotal = decimal.Parse(ds.Tables[0].Rows[0]["LastTotal"].ToString()); record.TotalAmount = decimal.Parse(ds.Tables[0].Rows[0]["TotalAmount"].ToString()); record.RemainingAmount = decimal.Parse(ds.Tables[0].Rows[0]["RemainingAmount"].ToString()); record.InstallDate = DateTime.Parse(ds.Tables[0].Rows[0]["InstallDate"].ToString()); record.ReadDate = DateTime.Parse(ds.Tables[0].Rows[0]["ReadDate"].ToString()); } return(record); }
protected void Page_Load(object sender, EventArgs e) { WCFServiceProxy <IChaoBiao> proxy = new WCFServiceProxy <IChaoBiao>(); try { string TimeKind = Request.QueryString["Time"]; string UserKind = Request.QueryString["User"]; string CompanyID = Request.QueryString["CompanyID"]; string Where = "1=1 and CompanyID='" + CompanyID + "'"; if (UserKind != "*" && UserKind != "") { Where += "AND charindex(','+CAST(Community AS NVARCHAR(10))+',',','+'" + UserKind + "'+',')>=1"; } if (TimeKind != "*") { Where += "and CONVERT(varchar(100), ReadDate, 23)='" + TimeKind + "'"; } List <View_UserMeterHistory> listAll = proxy.getChannel.GetModelList(Where); List <View_UserMeterHistory> listEXP = new List <View_UserMeterHistory>(); var newModel = (from n in listAll orderby n.Community, n.ReadDate descending select new { n.UserID }).Distinct().ToList(); if (TimeKind == "*") {//如果汇出说有数据,执行以下(筛选出最新一笔的数据) foreach (var item in newModel) { View_UserMeterHistory Model = new View_UserMeterHistory(); Model = (from n in listAll where n.UserID == item.UserID orderby n.ReadDate descending select n).FirstOrDefault(); listEXP.Add(Model); } } else {//如果汇出指定日期的数据,执行以下(筛选出指定日期下的第一笔数据) //foreach (var item in newModel) //{ //View_UserMeterDayFirstHistory Models = new View_UserMeterDayFirstHistory(); List <View_UserMeterDayFirstHistory> list = proxy.getChannel.GetModelLists(Where); //Model = (from n in list // where n.UserID == item.UserID // orderby n.ReadDate ascending // select n).FirstOrDefault(); foreach (View_UserMeterDayFirstHistory Model in list) { View_UserMeterHistory ModelAdd = new View_UserMeterHistory() { CompanyID = Model.CompanyID, UserID = Model.UserID, UserName = Model.UserName, State = Model.State, Address = Model.Address, Street = Model.Street, Community = Model.Community, Door = Model.Door, MeterNo = Model.MeterNo, MeterType = Model.MeterType, ValveState = Model.ValveState, LastTotal = Model.LastTotal, TotalAmount = Model.TotalAmount, RemainingAmount = Model.RemainingAmount, ReadDate = Model.ReadDate, InstallDate = Model.InstallDate, Gas = Model.Gas, }; listEXP.Add(ModelAdd); } //} } listAll = listEXP; Workbook workbook = new Workbook(); workbook.Worksheets.Add("未下载数据"); Worksheet sheet = (Worksheet)workbook.Worksheets[0]; //Worksheet sheet1 = (Worksheet)workbook.Worksheets[1]; sheet.Cells["A1"].PutValue("户号"); sheet.Cells["B1"].PutValue("户名"); sheet.Cells["C1"].PutValue("地址"); sheet.Cells["D1"].PutValue("表号"); sheet.Cells["E1"].PutValue("阀状态"); sheet.Cells["F1"].PutValue("总用量"); sheet.Cells["G1"].PutValue("余额(元)"); sheet.Cells["H1"].PutValue("抄表时间"); int RowNo = 2; for (int i = 0; i < listAll.Count; i++) { sheet.Cells["A" + RowNo].PutValue(listAll[i].UserID); sheet.Cells["B" + RowNo].PutValue(listAll[i].UserName); sheet.Cells["C" + RowNo].PutValue(listAll[i].Address); sheet.Cells["D" + RowNo].PutValue(listAll[i].MeterNo); if (listAll[i].ValveState == '0') { sheet.Cells["E" + RowNo].PutValue("阀开"); } else { sheet.Cells["E" + RowNo].PutValue("阀关"); } sheet.Cells["F" + RowNo].PutValue(listAll[i].TotalAmount.ToString()); sheet.Cells["G" + RowNo].PutValue(listAll[i].RemainingAmount.ToString()); sheet.Cells["H" + RowNo].PutValue(listAll[i].ReadDate.ToString()); RowNo++; } String filename = string.Format("{0}{1}.xls", "抄表记录", Convert.ToDateTime(DateTime.Now).ToString("yyyyMMdd")); Response.ContentType = "application/ms-excel;charset=utf-8"; Response.AddHeader("content-disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.GetEncoding("utf-8"))); System.IO.MemoryStream memStream = workbook.SaveToStream(); Response.BinaryWrite(memStream.ToArray()); Response.End(); } catch (Exception) { } finally { if (proxy != null) { proxy.CloseChannel(); } } }
public List <View_UserMeterHistory> GetModelList(string where) { //CommandType a=new CommandType() ; string sql = "select *from View_UserMeterHistory where "; sql = sql + where; //string strConnect = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]; DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.SchuleConnection, CommandType.Text, sql); List <View_UserMeterHistory> listOTB_SYS_ModuleList = new List <View_UserMeterHistory>(); if (ds.Tables[0].Rows.Count > 0) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { View_UserMeterHistory model = new View_UserMeterHistory(); if (ds.Tables[0].Rows[i]["CompanyID"] != null && ds.Tables[0].Rows[i]["CompanyID"].ToString() != "") { model.CompanyID = ds.Tables[0].Rows[i]["CompanyID"].ToString(); } if (ds.Tables[0].Rows[i]["UserID"] != null && ds.Tables[0].Rows[i]["UserID"].ToString() != "") { model.UserID = ds.Tables[0].Rows[i]["UserID"].ToString(); } if (ds.Tables[0].Rows[i]["UserName"] != null && ds.Tables[0].Rows[i]["UserName"].ToString() != "") { model.UserName = ds.Tables[0].Rows[i]["UserName"].ToString(); } if (ds.Tables[0].Rows[i]["State"] != null && ds.Tables[0].Rows[i]["State"].ToString() != "") { model.State = Convert.ToChar(ds.Tables[0].Rows[i]["State"].ToString()); } if (ds.Tables[0].Rows[i]["Address"] != null && ds.Tables[0].Rows[i]["Address"].ToString() != "") { model.Address = ds.Tables[0].Rows[i]["Address"].ToString(); } if (ds.Tables[0].Rows[i]["Street"] != null && ds.Tables[0].Rows[i]["Street"].ToString() != "") { model.Street = ds.Tables[0].Rows[i]["Street"].ToString(); } if (ds.Tables[0].Rows[i]["Community"] != null && ds.Tables[0].Rows[i]["Community"].ToString() != "") { model.Community = ds.Tables[0].Rows[i]["Community"].ToString(); } if (ds.Tables[0].Rows[i]["Door"] != null && ds.Tables[0].Rows[i]["Door"].ToString() != "") { model.Door = ds.Tables[0].Rows[i]["Door"].ToString(); } if (ds.Tables[0].Rows[i]["MeterNo"] != null && ds.Tables[0].Rows[i]["MeterNo"].ToString() != "") { model.MeterNo = ds.Tables[0].Rows[i]["MeterNo"].ToString(); } if (ds.Tables[0].Rows[i]["MeterType"] != null && ds.Tables[0].Rows[i]["MeterType"].ToString() != "") { model.MeterType = ds.Tables[0].Rows[i]["MeterType"].ToString(); } if (ds.Tables[0].Rows[i]["ValveState"] != null && ds.Tables[0].Rows[i]["ValveState"].ToString() != "") { model.ValveState = Convert.ToChar(ds.Tables[0].Rows[i]["ValveState"].ToString()); } if (ds.Tables[0].Rows[i]["LastTotal"] != null && ds.Tables[0].Rows[i]["LastTotal"].ToString() != "") { model.LastTotal = decimal.Parse(ds.Tables[0].Rows[i]["LastTotal"].ToString()); } if (ds.Tables[0].Rows[i]["TotalAmount"] != null && ds.Tables[0].Rows[i]["TotalAmount"].ToString() != "") { model.TotalAmount = decimal.Parse(ds.Tables[0].Rows[i]["TotalAmount"].ToString()); } if (ds.Tables[0].Rows[i]["RemainingAmount"] != null && ds.Tables[0].Rows[i]["RemainingAmount"].ToString() != "") { model.RemainingAmount = decimal.Parse(ds.Tables[0].Rows[i]["RemainingAmount"].ToString()); } if (ds.Tables[0].Rows[i]["InstallDate"] != null && ds.Tables[0].Rows[i]["InstallDate"].ToString() != "") { model.InstallDate = DateTime.Parse(ds.Tables[0].Rows[i]["InstallDate"].ToString()); } if (ds.Tables[0].Rows[i]["ReadDate"] != null && ds.Tables[0].Rows[i]["ReadDate"].ToString() != "") { model.ReadDate = DateTime.Parse(ds.Tables[0].Rows[i]["ReadDate"].ToString()); } listOTB_SYS_ModuleList.Add(model); } return(listOTB_SYS_ModuleList); } else { return(null); } }