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);
}
}
