public static Ui.DataGrid GetContract_CustomerGrid(int ContractID, string orderBy, long startRowIndex, int pageSize)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[ContractID]=@ContractID");
parameters.Add(new SqlParameter("@ContractID", ContractID));
string fieldList = "[Contract_Customer].*";
string Statement = " from [Contract_Customer] where " + string.Join(" and ", conditions.ToArray());
var list = GetList <Contract_Customer>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
if (list.Length > 0)
{
var chargeList = ChargeSummary.GetChargeSummaries().ToArray();
list = list.Select(p =>
{
var myChargeList = chargeList.Where(q => p.ChargeIDList.Contains(q.ID)).ToArray();
if (myChargeList.Length > 0)
{
p.ChargeName = string.Join(",", myChargeList.Select(q => q.Name).ToArray());
}
return(p);
}).ToArray();
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
var list = ChargeSummary.GetChargeSummaries().Where(p => p.IsReading).ToArray();
var items = list.Select(p =>
{
var item = new { id = p.ID, name = p.Name };
return(item);
}).ToList();
this.hdChargeIDList.Value = JsonConvert.SerializeObject(items);
}
}
private void importgongtan(HttpContext context)
{
HttpFileCollection uploadFiles = context.Request.Files;
if (uploadFiles.Count == 0)
{
context.Response.Write("请选择一个文件");
return;
}
if (string.IsNullOrEmpty(uploadFiles[0].FileName))
{
context.Response.Write("请选择一个文件");
return;
}
string msg = string.Empty;
int CompanyID = WebUtil.GetCompanyID(context);
int CreatorID = WebUtil.GetUser(context).UserID;
string AddMan = WebUtil.GetUser(context).RealName;
bool ImportFailed = false;
var summarylist = ChargeSummary.GetChargeSummaries().ToArray();
titleList = Foresight.DataAccess.TableColumn.GetTableColumnByPageCode("roomfeesource", true).Where(p => !p.ColumnName.Equals("选择按钮")).ToArray();
using (SqlHelper helper = new SqlHelper())
{
try
{
helper.BeginTransaction();
#region 导入处理
for (int j = 0; j < uploadFiles.Count; j++)
{
HttpPostedFile postedFile = uploadFiles[j];
string filepath = HttpContext.Current.Server.MapPath("~/upload/ImportChaoBiao/" + DateTime.Now.ToString("yyyyMMdd"));
if (!System.IO.Directory.Exists(filepath))
{
System.IO.Directory.CreateDirectory(filepath);
}
string filename = DateTime.Now.ToLocalTime().ToString("yyyyMMddHHmmss") + "_" + postedFile.FileName;
string fullpath = Path.Combine(filepath, filename);
postedFile.SaveAs(fullpath);
DataTable table = ExcelExportHelper.NPOIReadExcel(fullpath);
for (int i = 0; i < table.Rows.Count; i++)
{
Project project = null;
object Value, FullName, RoomName;
if (GetColumnValue("房源信息", table, i, out FullName) && GetColumnValue("房间号", table, i, out RoomName))
{
project = Project.GetProjectByFullName(RoomName.ToString(), FullName.ToString(), CompanyID, helper);
}
if (project == null)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：房间资源不存在</p>";
ImportFailed = true;
break;
}
ChargeSummary summary = null;
if (GetColumnValue("收费项目", table, i, out Value))
{
summary = summarylist.FirstOrDefault(p => p.CompanyID == CompanyID && p.Name.Equals(Value.ToString()));
}
if (summary == null)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：收费项目项目不存在</p>";
ImportFailed = true;
break;
}
int ID = 0;
int.TryParse(table.Rows[i]["账单ID"].ToString(), out ID);
Foresight.DataAccess.ImportFee importFee = null;
if (ID > 0)
{
importFee = Foresight.DataAccess.ImportFee.GetOrCreateImportFeeByID(ID, helper, CanCreate: false);
}
if (importFee == null)
{
importFee = new Foresight.DataAccess.ImportFee();
importFee.ChargeStatus = 0;
importFee.AddTime = DateTime.Now;
importFee.ChargeID = summary.ID;
importFee.RoomID = project.ID;
importFee.ChargeStatus = 2;
}
if (importFee.ChargeStatus == 1)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：该费用已收取</p>";
ImportFailed = true;
break;
}
if (GetColumnValue("上次读数", table, i, out Value))
{
importFee.StartPoint = GetDecimalValue(Value);
}
if (GetColumnValue("本次读数", table, i, out Value))
{
importFee.EndPoint = GetDecimalValue(Value);
}
decimal totalpoint = decimal.MinValue;
if (GetColumnValue("用量", table, i, out Value))
{
totalpoint = GetDecimalValue(Value);
}
if (totalpoint == decimal.MinValue)
{
totalpoint = (importFee.EndPoint == decimal.MinValue ? 0 : importFee.EndPoint) - (importFee.StartPoint == decimal.MinValue ? 0 : importFee.StartPoint);
}
totalpoint = totalpoint < 0 ? 0 : totalpoint;
importFee.TotalPoint = totalpoint;
if (GetColumnValue("单价", table, i, out Value))
{
importFee.UnitPrice = GetDecimalValue(Value);
}
if (GetColumnValue("系数", table, i, out Value))
{
importFee.ImportCoefficient = GetDecimalValue(Value);
}
decimal totalprice = decimal.MinValue;
if (GetColumnValue("金额", table, i, out Value))
{
totalprice = GetDecimalValue(Value);
}
if (totalprice == decimal.MinValue)
{
totalprice = (importFee.TotalPoint == decimal.MinValue ? 0 : importFee.TotalPoint) * (importFee.ImportCoefficient == decimal.MinValue ? 0 : importFee.ImportCoefficient) * (importFee.UnitPrice == decimal.MinValue ? 0 : importFee.UnitPrice);
}
importFee.TotalPrice = totalprice;
if (GetColumnValue("收费状态", table, i, out Value))
{
importFee.ChargeStatus = Value.ToString().Equals("已收") ? 1 : (Value.ToString().Equals("未收") ? 0 : 2);
}
if (GetColumnValue("账单日期", table, i, out Value))
{
importFee.WriteDate = GetDateTimeValue(Value);
}
if (GetColumnValue("计费开始日期", table, i, out Value))
{
importFee.StartTime = GetDateTimeValue(Value);
}
if (GetColumnValue("计费结束日期", table, i, out Value))
{
importFee.EndTime = GetDateTimeValue(Value);
}
string ImportBiaoCategory = string.Empty;
string ImportBiaoName = project.Name;
string ImportChargeRoomNo = string.Empty;
if (GetColumnValue("表种类", table, i, out Value))
{
ImportBiaoCategory = Value.ToString();
}
if (GetColumnValue("表名称", table, i, out Value))
{
ImportBiaoName = Value.ToString();
}
if (GetColumnValue("缴费户号", table, i, out Value))
{
ImportChargeRoomNo = Value.ToString();
}
Foresight.DataAccess.ImportFee lastimportFee = Foresight.DataAccess.ImportFee.GetImportFeeByRoomID(project.ID, null, importFee.ChargeID, helper);
if (string.IsNullOrEmpty(ImportBiaoCategory))
{
ImportBiaoCategory = summary.BiaoCategory;
if (lastimportFee != null && !string.IsNullOrEmpty(lastimportFee.ImportBiaoCategory))
{
ImportBiaoCategory = lastimportFee.ImportBiaoCategory;
}
}
if (string.IsNullOrEmpty(ImportBiaoName))
{
ImportBiaoName = project.Name;
if (lastimportFee != null && !string.IsNullOrEmpty(lastimportFee.ImportBiaoName))
{
ImportBiaoName = lastimportFee.ImportBiaoName;
}
}
if (string.IsNullOrEmpty(ImportChargeRoomNo))
{
if (lastimportFee != null && !string.IsNullOrEmpty(lastimportFee.ImportChargeRoomNo))
{
ImportChargeRoomNo = lastimportFee.ImportChargeRoomNo;
}
}
importFee.ImportBiaoCategory = ImportBiaoCategory;
importFee.ImportBiaoName = ImportBiaoName;
importFee.ImportChargeRoomNo = ImportChargeRoomNo;
importFee.Save(helper);
if (importFee.ChargeStatus == 0)
{
SaveRoomFee(importFee, summary.ID, helper);
}
else if (importFee.ChargeStatus == 1)
{
SaveRoomHistoryFee(importFee, summary.ID, helper, AddMan);
}
}
}
#endregion
if (!ImportFailed)
{
helper.Commit();
msg += "<p>导入完成</p>";
}
else
{
helper.Rollback();
msg += "<p>导入失败</p>";
}
}
catch (Exception ex)
{
LogHelper.WriteError("ImportGongTanHandler", "visit: importgongtan", ex);
helper.Rollback();
}
context.Response.Write(msg);
}
}
