protected void Page_Load(object sender, EventArgs e) { if (!base.IsPostBack) { if (base.Request["Id"] != null) { this.btnAdd.Visible = false; Out_BankFlow model = this.outBankSer.GetModel(Convert.ToInt32(base.Request["Id"])); this.lblNumber.Text = model.Number; this.lblReferenceNumber.Text = model.ReferenceNumber; this.ddlOutType.Text = model.OutType; this.txtProNo.Text = model.ProNo; this.txtOutTotal.Text = model.OutTotal.ToString(); this.txtRemark.Text = model.Remark; } else { lblReferenceNumber.Text = Request["TransactionReferenceNumber"].ToString(); this.btnUpdate.Visible = false; } BankFlowService bandFlowSer = new BankFlowService(); var list = bandFlowSer.GetListArray(string.Format("TransactionReferenceNumber='{0}'", lblReferenceNumber.Text)); if (list.Count > 0) { lblInPayeeName.Text = list[0].InPayeeName; lblTradeAmount.Text = System.Math.Abs(list[0].TradeAmount).ToString(); lblTime.Text = list[0].TransactionDate.ToString(); string sql = string.Format("select ISNULL(SUM(OutTotal),0) AS SUMOutTotal FROM Out_BankFlow where ReferenceNumber='{0}'", lblReferenceNumber.Text); //if (base.Request["Id"] != null) //{ // sql += " and id<>" + Request["Id"]; //} var total = Convert.ToDecimal(DBHelp.ExeScalar(sql)); lblLastTotal.Text = (System.Math.Abs(list[0].TradeAmount) - total).ToString(); var outList = outBankSer.GetListArray(string.Format(" ReferenceNumber='{0}'", lblReferenceNumber.Text)); var time = Convert.ToDateTime(lblTime.Text); foreach (var m in outList) { m.Time = time; } gvLiuShui.DataSource = outList; gvLiuShui.DataBind(); } } }
protected void Page_Load(object sender, EventArgs e) { if (!base.IsPostBack) { if (base.Request["Id"] != null) { this.btnAdd.Visible = false; In_BankFlow model = this.inBankSer.GetModel(Convert.ToInt32(base.Request["Id"])); this.lblNumber.Text = model.Number; this.lblReferenceNumber.Text = model.ReferenceNumber; this.ddlInType.Text = model.InType; this.txtFPNo.Text = model.FPNo; this.txtFPTotal.Text = model.FPTotal.ToString(); this.txtRemark.Text = model.Remark; if (ddlInType.Text == "发票回款") { txtFPNo.Enabled = true; } else { txtFPNo.Enabled = false; } } else { lblReferenceNumber.Text = Request["TransactionReferenceNumber"].ToString(); this.btnUpdate.Visible = false; } BankFlowService bandFlowSer = new BankFlowService(); var list = bandFlowSer.GetListArray(string.Format("TransactionReferenceNumber='{0}'", lblReferenceNumber.Text)); if (list.Count > 0) { lblOutPayerName.Text = list[0].OutPayerName; } } }
private void LoadExcel(string fileAddress) { string strConn; strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileAddress + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataTable sheetNames = conn.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); int allSheets = 0; foreach (DataRow rowTable in sheetNames.Rows) { string sheetName = rowTable["TABLE_NAME"].ToString(); conn = new OleDbConnection(strConn); conn.Open(); OleDbCommand objCommand = new OleDbCommand(string.Format("select * from [" + sheetName + "]"), conn); int rowIndex = 1; BankFlowService bandFlowService = new BankFlowService(); PetitionsService petSer = new PetitionsService(); CG_POOrderService POSer = new CG_POOrderService(); CAI_POCaiService pocaiSer = new CAI_POCaiService(); using (SqlConnection sqlconn = DBHelp.getConn()) { sqlconn.Open(); SqlCommand sqlCommand = sqlconn.CreateCommand(); using (OleDbDataReader dataReader = objCommand.ExecuteReader()) { while (dataReader.Read()) { //if (rowIndex > 1) //{ // Petitions model = new Petitions(); // model.Number = dataReader[0].ToString(); // model.Type = dataReader[1].ToString(); // model.OldIndex = dataReader[2].ToString(); // var ojb = dataReader[3]; // if (ojb != null && ojb != DBNull.Value) // { // model.SignDate = Convert.ToDateTime(ojb); // } // model.PoNo = dataReader[4].ToString(); // model.Summary = dataReader[5].ToString(); // ojb = dataReader[6]; // if (ojb != null && ojb != DBNull.Value) // { // model.SumPages = Convert.ToInt32(ojb); // } // model.Local = dataReader[7].ToString(); // model.L_Year = Convert.ToInt32(dataReader[8]); // model.L_Month = Convert.ToInt32(dataReader[9]); // model.Remark = dataReader[10].ToString(); // model.SumCount = 1; // model.BCount = 1; // model.Handler = "王汉中"; // var poList = POSer.GetSimpListArray(string.Format("and pono='{0}'", model.PoNo), sqlCommand); // if (poList.Count > 0) // { // CG_POOrder poorderModel = poList[0]; // model.AE = poorderModel.AE; // model.GuestName = poorderModel.GuestName; // model.Name = poorderModel.POName; // //var supplierList = pocaiSer.GetLastSupplier(string.Format("'{0}'", model.PoNo), sqlCommand); // //model.SalesUnit = string.Join(",", supplierList.Select(t => t.Supplier).ToArray()); // //model.Total = supplierList.Sum(t => t.Total); // } // petSer.Add(model,sqlCommand); //} if (rowIndex > 8) { BankFlow model = new BankFlow(); model.TransactionType = dataReader[0].ToString(); if (model.TransactionType == "") { continue; } model.BusinessType = dataReader[1].ToString(); model.PayerAccountBank = dataReader[3].ToString(); model.DebitAccountNo = dataReader[4].ToString(); model.OutPayerName = dataReader[5].ToString(); model.BeneficiaryAccountBank = dataReader[7].ToString(); model.PayeeAccountNumber = dataReader[8].ToString(); model.InPayeeName = dataReader[9].ToString(); DateTime dt = DateTime.ParseExact(dataReader[10].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture); model.TransactionDate = Convert.ToDateTime(dt.ToString("yyyy-MM-dd") + " " + dataReader[11]); model.TradeCurrency = dataReader[12].ToString(); model.TradeAmount = Convert.ToDecimal(dataReader[13]); model.AfterTransactionBalance = Convert.ToDecimal(dataReader[14]); model.TransactionReferenceNumber = dataReader[17].ToString(); model.VoucherType = dataReader[20].ToString(); model.Reference = dataReader[23].ToString(); model.Purpose = dataReader[24].ToString(); model.Remark = dataReader[25].ToString(); model.Remarks = dataReader[26].ToString(); //判断是否重复 string sql = string.Format("SELECT top 1 ID FROM [BankFlow] WHERE TransactionReferenceNumber='{0}'", model.TransactionReferenceNumber); sqlCommand.CommandText = sql; var resultId = sqlCommand.ExecuteScalar(); if (!(resultId is DBNull) && resultId != null) { model.Id = Convert.ToInt32(resultId); bandFlowService.Update(model, sqlCommand); } else { bandFlowService.Add(model, sqlCommand); } } rowIndex++; } } sqlconn.Close(); } } }