/// <summary> /// 向ManuallyCheckBill表插入一条数据 /// </summary> /// <param name="manuallyCheckBillInfo">manuallyCheckBillInfo</param> /// <returns></returns> public bool AddManuallyCheckBill(ManuallyCheckBillInfo manuallyCheckBillInfo) { string sql = "insert into [ManuallyCheckBill]([Id],[CheckBillPersonnelId],[SalePlatformId],[TradeCode],[CheckState],[ThirdOrderTotalAmount],[UnusualOrderQuantity],[ConfirmTotalAmount],[ReceiptState],[CheckBillDate],[State],[Memo])values(@Id,@CheckBillPersonnelId,@SalePlatformId,@TradeCode,@CheckState,@ThirdOrderTotalAmount,@UnusualOrderQuantity,@ConfirmTotalAmount,@ReceiptState,@CheckBillDate,@State,@Memo)"; SqlParameter[] paras = PrepareCommandParameters(manuallyCheckBillInfo); return(SqlHelper.ExecuteNonQuery(GlobalConfig.ERP_DB_NAME, false, sql, paras) > 0); }
/// <summary> /// 根据ManuallyCheckBill表的Id字段更新数据 /// </summary> /// <param name="manuallyCheckBillInfo">manuallyCheckBillInfo</param> /// <returns></returns> public bool UpdateManuallyCheckBillByBillId(ManuallyCheckBillInfo manuallyCheckBillInfo) { string sql = "update [ManuallyCheckBill] set [CheckBillPersonnelId] = @CheckBillPersonnelId,[SalePlatformId]=@SalePlatformId,[TradeCode] = @TradeCode,[CheckState] = @CheckState,[ThirdOrderTotalAmount] = @ThirdOrderTotalAmount,[UnusualOrderQuantity] = @UnusualOrderQuantity,[ConfirmTotalAmount] = @ConfirmTotalAmount,[ReceiptState] = @ReceiptState,[CheckBillDate] = @CheckBillDate,[State] = @State,[Memo] = @Memo where [Id] = @Id"; SqlParameter[] paras = PrepareCommandParameters(manuallyCheckBillInfo); return(SqlHelper.ExecuteNonQuery(GlobalConfig.ERP_DB_NAME, false, sql, paras) > 0); }
/// <summary> /// 返回ManuallyCheckBill表的所有数据 /// </summary> /// <returns></returns> public List <ManuallyCheckBillInfo> GetAllManuallyCheckBill() { List <ManuallyCheckBillInfo> manuallyCheckBillList = new List <ManuallyCheckBillInfo>(); string sql = SQL_SELECT; var reader = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, sql, null); while (reader.Read()) { ManuallyCheckBillInfo manuallyCheckBill = new ManuallyCheckBillInfo(reader); manuallyCheckBillList.Add(manuallyCheckBill); } reader.Close(); return(manuallyCheckBillList); }
/// <summary> /// 根据ManuallyCheckBill表的id字段返回数据 /// </summary> /// <param name="id">id</param> /// <returns></returns> public List <ManuallyCheckBillInfo> GetManuallyCheckBillById(Guid id) { List <ManuallyCheckBillInfo> manuallyCheckBillList = new List <ManuallyCheckBillInfo>(); string sql = SQL_SELECT + "where [Id] = @Id"; SqlParameter[] paras = { new SqlParameter("@Id", id) }; var reader = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, sql, paras); while (reader.Read()) { ManuallyCheckBillInfo manuallyCheckBill = new ManuallyCheckBillInfo(reader); manuallyCheckBillList.Add(manuallyCheckBill); } reader.Close(); return(manuallyCheckBillList); }
/// <summary> /// prepare parameters /// </summary> public static SqlParameter[] PrepareCommandParameters(ManuallyCheckBillInfo manuallyCheckBillInfo) { SqlParameter[] paras = { new SqlParameter("@Id", manuallyCheckBillInfo.Id), new SqlParameter("@CheckBillPersonnelId", manuallyCheckBillInfo.CheckBillPersonnelId), new SqlParameter("@SalePlatformId", manuallyCheckBillInfo.SalePlatformId), new SqlParameter("@TradeCode", manuallyCheckBillInfo.TradeCode), new SqlParameter("@CheckState", manuallyCheckBillInfo.CheckState), new SqlParameter("@ThirdOrderTotalAmount", manuallyCheckBillInfo.ThirdOrderTotalAmount), new SqlParameter("@UnusualOrderQuantity", manuallyCheckBillInfo.UnusualOrderQuantity), new SqlParameter("@ConfirmTotalAmount", manuallyCheckBillInfo.ConfirmTotalAmount), new SqlParameter("@ReceiptState", manuallyCheckBillInfo.ReceiptState), new SqlParameter("@CheckBillDate", manuallyCheckBillInfo.CheckBillDate), new SqlParameter("@State", manuallyCheckBillInfo.State), new SqlParameter("@Memo", manuallyCheckBillInfo.Memo) }; return(paras); }
//#region 列表显示辅助方法 ///// <summary> ///// 票据截止日期=申报日期+15天 ///// </summary> ///// <param name="reportDate">申报日期</param> ///// <returns></returns> //protected string GetFinallySubmitTicketDate(string reportDate) //{ // if (!reportDate.Equals("1900-01-01")) // { // DateTime date = Convert.ToDateTime(reportDate); // return date.Date.AddDays(15).ToString("yyyy-MM-dd"); // } // return string.Empty; //} ///// <summary> ///// 获取处理状态 ///// </summary> ///// <param name="reportState">状态</param> ///// <returns></returns> //protected string GetReportState(string reportState) //{ // if (string.IsNullOrEmpty(reportState)) // { // return "-"; // } // var state = int.Parse(reportState); // if (((int)CostReportState.AlreadyAuditing).Equals(state)) // { // return "待收款"; // } // if (((int)CostReportState.WaitVerify).Equals(state)) // { // return "待付款"; // } // return EnumAttribute.GetKeyName((CostReportState)state); //} //#endregion #endregion //导入对账原始表 protected void btn_Upload_Click(object sender, EventArgs e) { var excelName = UploadExcelName.Text; UploadExcelName.Text = string.Empty; #region 数据验证 if (string.IsNullOrEmpty(rcb_SalePlatform.SelectedValue) || string.IsNullOrEmpty(Hid_SaleFiliale.Value)) { MessageBox.Show(this, "请选择“销售平台”!"); return; } if (!UploadExcel.HasFile || string.IsNullOrEmpty(excelName)) { MessageBox.Show(this, "请选择格式为“.xls”文件!"); return; } var ext = Path.GetExtension(UploadExcel.FileName); if (ext != null && !ext.Equals(".xls")) { MessageBox.Show(this, "文件格式错误(.xls)!"); return; } #endregion try { #region 将上传文件保存至临时文件夹 string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ext; string folderPath = "~/UserDir/ManuallyCheckBill/InitialTable/ "; if (!Directory.Exists(Server.MapPath(folderPath))) { Directory.CreateDirectory(Server.MapPath(folderPath)); } string filePath = Server.MapPath(folderPath + fileName); UploadExcel.PostedFile.SaveAs(filePath); #endregion var excelDataTable = ExcelHelper.GetDataSet(filePath).Tables[0]; #region 获取数据之后删除临时文件 if (File.Exists(filePath)) { File.Delete(filePath); } #endregion List <ManuallyCheckBillDetailInfo> manuallyCheckBillDetailInfoList = new List <ManuallyCheckBillDetailInfo>(); StringBuilder errorMsg = new StringBuilder(); int index = 2; #region 对账原始表 for (int i = 0; i < excelDataTable.Rows.Count; i++) { StringBuilder rowMsg = new StringBuilder(); var systemOrderNo = excelDataTable.Rows[i]["系统订单号"].ToString(); var thirdOrderNo = excelDataTable.Rows[i]["第三方订单号"].ToString(); var isCheck = excelDataTable.Rows[i]["是否对账"].ToString(); var isReceipt = excelDataTable.Rows[i]["是否收款"].ToString(); var orderTime = excelDataTable.Rows[i]["下单日期"].ToString(); var memberId = excelDataTable.Rows[i]["会员名"].ToString(); var systemOrderAmount = excelDataTable.Rows[i]["系统金额"].ToString(); var thirdOrderAmount = excelDataTable.Rows[i]["第三方订单金额"].ToString(); var balance = excelDataTable.Rows[i]["差额"].ToString(); var confirmAmount = excelDataTable.Rows[i]["财务确认金额"].ToString(); var contactsReckoningDifference = excelDataTable.Rows[i]["往来差异"].ToString(); #region 验证数据空值 if (string.IsNullOrEmpty(thirdOrderNo)) { rowMsg.Append("第").Append(index).Append("行“第三方订单号”为空!").Append("\\n"); } if (string.IsNullOrEmpty(thirdOrderAmount)) { rowMsg.Append("第").Append(index).Append("行“第三方订单金额”为空!").Append("\\n"); } #endregion #region 验证数据格式 decimal tryThirdOrderAmount; if (!decimal.TryParse(thirdOrderAmount, out tryThirdOrderAmount)) { rowMsg.Append("第").Append(index).Append("行“第三方订单金额”格式错误!").Append("\\n"); } #endregion #region 验证销售平台 //var goodsOrderInfoList = OrderSao.GetGoodsOrderInfoByThirdOrderNo(new Guid(Hid_SaleFiliale.Value), thirdOrderNo); //if (goodsOrderInfoList.Count > 0) //{ // var goodsOrderInfo = goodsOrderInfoList.First(); // if (!goodsOrderInfo.SalePlatformId.Equals(new Guid(rcb_SalePlatform.SelectedValue))) // { // rowMsg.Append("第").Append(index).Append("行“" + thirdOrderNo + "”与所选销售平台不符!").Append("\\n"); // } //} //else //{ // rowMsg.Append("第").Append(index).Append("行“" + thirdOrderNo + "”与所选销售平台不符!").Append("\\n"); //} #endregion if (string.IsNullOrEmpty(rowMsg.ToString())) { #region 保存数据 var manuallyCheckBillDetailInfo = new ManuallyCheckBillDetailInfo { Id = Guid.NewGuid(), ManuallyCheckBillId = Guid.Empty, SystemOrderNo = string.Empty, ThirdOrderNo = thirdOrderNo, OrderTime = DateTime.Parse("1900-01-01"), MemberId = Guid.Empty, SystemOrderAmount = 0, ThirdOrderAmount = decimal.Parse(thirdOrderAmount), Balance = 0, ConfirmAmount = 0, ContactsReckoningDifference = 0 }; manuallyCheckBillDetailInfoList.Add(manuallyCheckBillDetailInfo); #endregion } else { errorMsg.Append(rowMsg); } index++; } #endregion if (!string.IsNullOrEmpty(errorMsg.ToString())) { MessageBox.Show(this, errorMsg.ToString()); return; } if (manuallyCheckBillDetailInfoList.Count > 0) { var manuallyCheckBillInfo = new ManuallyCheckBillInfo { Id = Guid.NewGuid(), CheckBillPersonnelId = Personnel.PersonnelId, SalePlatformId = new Guid(rcb_SalePlatform.SelectedValue), TradeCode = string.Empty, CheckState = 0, ThirdOrderTotalAmount = manuallyCheckBillDetailInfoList.Sum(p => p.ThirdOrderAmount), UnusualOrderQuantity = 0, ConfirmTotalAmount = 0, ReceiptState = 0, CheckBillDate = DateTime.Now, State = 0, Memo = Common.WebControl.RetrunUserAndTime("[【导入】:对账原始表;]") }; foreach (var item in manuallyCheckBillDetailInfoList) { item.ManuallyCheckBillId = manuallyCheckBillInfo.Id; } var result = _manuallyCheckBill.AddManuallyCheckBill(manuallyCheckBillInfo); if (result) { _manuallyCheckBillDetail.AddBatchManuallyCheckBillDetail(manuallyCheckBillDetailInfoList); GridDataBind(); } } } catch (Exception ex) { MessageBox.Show(this, ex.Message); } }