/// <summary> /// 向ManuallyCheckBillDetail表插入一条数据 /// </summary> /// <param name="manuallyCheckBillDetailInfo">ManuallyCheckBillDetailInfo</param> /// <returns></returns> public bool AddManuallyCheckBillDetail(ManuallyCheckBillDetailInfo manuallyCheckBillDetailInfo) { string sql = "insert into [ManuallyCheckBill]([Id],[ManuallyCheckBillId],[SystemOrderNo],[ThirdOrderNo],[OrderTime],[MemberId],[SystemOrderAmount],[ThirdOrderAmount],[Balance],[ConfirmAmount],[ContactsReckoningDifference])values(@Id,@ManuallyCheckBillId,@SystemOrderNo,@ThirdOrderNo,@IsCheck,@IsReceipt,@OrderTime,@MemberId,@SystemOrderAmount,@ThirdOrderAmount,@Balance,@ConfirmAmount,@ContactsReckoningDifference)"; SqlParameter[] paras = PrepareCommandParameters(manuallyCheckBillDetailInfo); return(SqlHelper.ExecuteNonQuery(GlobalConfig.ERP_DB_NAME, false, sql, paras) > 0); }
/// <summary> /// 根据ManuallyCheckBillDetail表的Id字段更新数据 /// </summary> /// <param name="manuallyCheckBillDetailInfo">ManuallyCheckBillDetailInfo</param> /// <returns></returns> public bool UpdateManuallyCheckBillDetailById(ManuallyCheckBillDetailInfo manuallyCheckBillDetailInfo) { string sql = "update [ManuallyCheckBillDetail] set [ManuallyCheckBillId] = @ManuallyCheckBillId,[SystemOrderNo]=@SystemOrderNo,[ThirdOrderNo] = @ThirdOrderNo,[OrderTime] = @OrderTime,[MemberId] = @MemberId,[SystemOrderAmount] = @SystemOrderAmount,[ThirdOrderAmount] = @ThirdOrderAmount,[Balance] = @Balance,[ConfirmAmount] = @ConfirmAmount,[ContactsReckoningDifference] = @ContactsReckoningDifference where [Id] = @Id"; SqlParameter[] paras = PrepareCommandParameters(manuallyCheckBillDetailInfo); return(SqlHelper.ExecuteNonQuery(GlobalConfig.ERP_DB_NAME, false, sql, paras) > 0); }
/// <summary> /// 返回ManuallyCheckBillDetail表的所有数据 /// </summary> /// <returns></returns> public List <ManuallyCheckBillDetailInfo> GetAllManuallyCheckBillDetail() { List <ManuallyCheckBillDetailInfo> manuallyCheckBillDetailList = new List <ManuallyCheckBillDetailInfo>(); string sql = SQL_SELECT; var reader = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, sql, null); while (reader.Read()) { ManuallyCheckBillDetailInfo manuallyCheckBillDetail = new ManuallyCheckBillDetailInfo(reader); manuallyCheckBillDetailList.Add(manuallyCheckBillDetail); } reader.Close(); return(manuallyCheckBillDetailList); }
/// <summary> /// prepare parameters /// </summary> public static SqlParameter[] PrepareCommandParameters(ManuallyCheckBillDetailInfo manuallyCheckBillDetailInfo) { SqlParameter[] paras = { new SqlParameter("@Id", manuallyCheckBillDetailInfo.Id), new SqlParameter("@ManuallyCheckBillId", manuallyCheckBillDetailInfo.ManuallyCheckBillId), new SqlParameter("@SystemOrderNo", manuallyCheckBillDetailInfo.SystemOrderNo), new SqlParameter("@ThirdOrderNo", manuallyCheckBillDetailInfo.ThirdOrderNo), new SqlParameter("@OrderTime", manuallyCheckBillDetailInfo.OrderTime), new SqlParameter("@MemberId", manuallyCheckBillDetailInfo.MemberId), new SqlParameter("@SystemOrderAmount", manuallyCheckBillDetailInfo.SystemOrderAmount), new SqlParameter("@ThirdOrderAmount", manuallyCheckBillDetailInfo.ThirdOrderAmount), new SqlParameter("@Balance", manuallyCheckBillDetailInfo.Balance), new SqlParameter("@ConfirmAmount", manuallyCheckBillDetailInfo.ConfirmAmount), new SqlParameter("@ContactsReckoningDifference", manuallyCheckBillDetailInfo.ContactsReckoningDifference) }; return(paras); }
/// <summary> /// 根据ManuallyCheckBillDetail表的id字段返回数据 /// </summary> /// <param name="id">id</param> /// <returns></returns> public List <ManuallyCheckBillDetailInfo> GetManuallyCheckBillDetailById(Guid id) { List <ManuallyCheckBillDetailInfo> manuallyCheckBillDetailList = new List <ManuallyCheckBillDetailInfo>(); 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()) { ManuallyCheckBillDetailInfo manuallyCheckBillDetail = new ManuallyCheckBillDetailInfo(reader); manuallyCheckBillDetailList.Add(manuallyCheckBillDetail); } reader.Close(); return(manuallyCheckBillDetailList); }
//#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); } }