public static Project_Biao[] GetProject_BiaoListByBiaoID(List <int> RoomIDList, List <int> ProjectIDList, int ChargeID = 0, int BiaoID = 0, string BiaoCategory = "", string BiaoName = "", string BiaoGuiGe = "", int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[ProjectID]");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
Project_Biao[] list = new Project_Biao[] { };
if (ChargeID > 0)
{
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
conditions.Add("[BiaoID] in (select ChargeBiaoID from ChargeSummary_Biao where ChargeID=@ChargeID)");
}
if (BiaoID > 0)
{
parameters.Add(new SqlParameter("@BiaoID", BiaoID));
conditions.Add("[BiaoID]=@BiaoID");
}
if (!string.IsNullOrEmpty(BiaoCategory))
{
parameters.Add(new SqlParameter("@BiaoCategory", BiaoCategory));
conditions.Add("[BiaoCategory]=@BiaoCategory");
}
if (!string.IsNullOrEmpty(BiaoName))
{
parameters.Add(new SqlParameter("@BiaoName", BiaoName));
conditions.Add("[BiaoName]=@BiaoName");
}
if (!string.IsNullOrEmpty(BiaoGuiGe))
{
parameters.Add(new SqlParameter("@BiaoGuiGe", BiaoGuiGe));
conditions.Add("[BiaoGuiGe]=@BiaoGuiGe");
}
list = GetList <Project_Biao>("select * from [Project_Biao] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
return(list);
}
public static Project_Biao[] GetProject_BiaoListByID(List <int> IDList)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
string cmdwhere = string.Empty;
if (IDList.Count > 0)
{
conditions.Add("[ID] in (" + string.Join(",", IDList.ToArray()) + ")");
}
Project_Biao[] list = new Project_Biao[] { };
string cmdText = "select * from [Project_Biao] where " + string.Join(" and ", conditions.ToArray());
list = GetList <Project_Biao>(cmdText, parameters).ToArray();
return(list);
}
private void importtaizhang(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;
bool ImportFailed = false;
int TotalNumber = 0;
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++)
{
int ID = 0;
int.TryParse(table.Rows[i]["表台帐ID"].ToString(), out ID);
Foresight.DataAccess.Project_Biao projectbiao = null;
if (ID > 0)
{
projectbiao = Foresight.DataAccess.Project_Biao.GetProject_Biao(ID, helper);
}
if (projectbiao == null)
{
msg += "<p>第" + (i + 2) + "行上传失败。原因：表台帐不存在</p>";
ImportFailed = true;
break;
}
projectbiao.BiaoName = table.Rows[i]["表名称"].ToString();
projectbiao.BiaoCategory = table.Rows[i]["表种类"].ToString();
projectbiao.BiaoGuiGe = table.Rows[i]["表规格"].ToString();
projectbiao.ChargeRoomNo = table.Rows[i]["缴费户号"].ToString();
decimal Rate = 0;
decimal.TryParse(table.Rows[i]["倍率"].ToString(), out Rate);
Rate = Rate > 0 ? Rate : 0;
projectbiao.Rate = Rate;
decimal StartPoint = 0;
decimal.TryParse(table.Rows[i]["上次读数"].ToString(), out StartPoint);
StartPoint = StartPoint > 0 ? StartPoint : 0;
projectbiao.StartPoint = StartPoint;
decimal EndPoint = 0;
decimal.TryParse(table.Rows[i]["本次读数"].ToString(), out EndPoint);
EndPoint = EndPoint > 0 ? EndPoint : 0;
projectbiao.EndPoint = EndPoint;
decimal ReducePoint = 0;
decimal.TryParse(table.Rows[i]["扣减用量"].ToString(), out ReducePoint);
ReducePoint = ReducePoint > 0 ? ReducePoint : 0;
projectbiao.ReducePoint = ReducePoint;
decimal TotalPoint = 0;
decimal.TryParse(table.Rows[i]["本次用量"].ToString(), out TotalPoint);
if (TotalPoint <= 0)
{
TotalPoint = (EndPoint - StartPoint) * (Rate > 0 ? Rate : 0) - (ReducePoint > 0 ? ReducePoint : 0);
}
TotalPoint = TotalPoint > 0 ? TotalPoint : 0;
projectbiao.TotalPoint = TotalPoint;
projectbiao.Remark = table.Rows[i]["备注"].ToString();
projectbiao.IsActive = table.Rows[i]["作废状态"].ToString().Equals("生效") ? true : false;
decimal ImportCoefficient = 0;
decimal.TryParse(table.Rows[i]["系数"].ToString(), out ImportCoefficient);
if (ImportCoefficient > 0)
{
projectbiao.Coefficient = ImportCoefficient;
}
decimal UnitPrice = 0;
decimal.TryParse(table.Rows[i]["单价"].ToString(), out UnitPrice);
if (UnitPrice > 0)
{
projectbiao.UnitPrice = UnitPrice;
}
projectbiao.Save(helper);
string cmdtex_2 = string.Empty;
if (projectbiao.Coefficient > 0)
{
cmdtex_2 += ",[ImportCoefficient]='" + projectbiao.Coefficient + "'";
}
if (projectbiao.UnitPrice > 0)
{
cmdtex_2 += ",[UnitPrice]='" + projectbiao.UnitPrice + "'";
}
string cmdtext = "update [ImportFee] set [ImportBiaoCategory]='" + projectbiao.BiaoCategory + "',[ImportBiaoName]='" + projectbiao.BiaoName + "',[StartPoint]='" + StartPoint + "',[EndPoint]='" + EndPoint + "',[TotalPoint]='" + TotalPoint + "' " + cmdtex_2 + " where ID in (select top 1 ID from [ImportFee] where [ProjectBiaoID]=" + projectbiao.ID + " and RoomID ='" + projectbiao.ProjectID + "' order by [WriteDate] desc);";
helper.Execute(cmdtext, CommandType.Text, new List <SqlParameter>());
TotalNumber = i;
}
}
#endregion
if (!ImportFailed)
{
helper.Commit();
msg += "<p>导入完成</p>";
}
else
{
helper.Rollback();
msg += "<p>导入失败</p>";
}
}
catch (Exception ex)
{
LogHelper.WriteError("ImportGongTanHandler", "visit: importtaizhang", ex);
helper.Rollback();
msg += "<p>第" + (TotalNumber + 2) + "行上传失败。原因：" + ex.Message + "</p>";
}
context.Response.Write(msg);
}
}
