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