Пример #1
0
        /// <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);
        }
Пример #2
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);
        }
Пример #3
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);
        }
Пример #4
0
 /// <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);
 }
Пример #5
0
        /// <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);
            }
        }