コード例 #1
0
        public IList <string> Validate()
        {
            ClaimOrderDao claimOrderDao = new ClaimOrderDao();

            Dictionary <ClaimOrderUK, ClaimOrder> yesterdayClaimOrders =
                new Dictionary <ClaimOrderUK, ClaimOrder>(new ClaimOrderUKEqualityComparer());

            foreach (ClaimOrder yesterdayClaimOrder in claimOrderDao.FindYesterdaysClaimOrders())
            {
                ClaimOrderUK uk = new ClaimOrderUK();
                uk.claimNo     = yesterdayClaimOrder.claimNo;
                uk.decidedDate = yesterdayClaimOrder.decidedDate;
                uk.claimAmount = yesterdayClaimOrder.claimAmount;

                yesterdayClaimOrders[uk] = yesterdayClaimOrder;
            }

            IList <string> errorStrs = new List <string>();

            for (int i = 0; i < _claimOrders.Count; i++)
            {
                ClaimOrder   todayClaimOrder = _claimOrders[i];
                ClaimOrderUK uk = new ClaimOrderUK();
                uk.claimNo     = todayClaimOrder.claimNo;
                uk.decidedDate = todayClaimOrder.decidedDate;
                uk.claimAmount = todayClaimOrder.claimAmount;

                if (yesterdayClaimOrders.ContainsKey(uk))
                {
                    ClaimOrder yesterdayClaimOrder = yesterdayClaimOrders[uk];

                    string errorStr = string.Empty;
                    if (yesterdayClaimOrder.gateOutDate.Equals(todayClaimOrder.gateOutDate) == false)
                    {
                        if (!DateTimeUtils.IsEmpty(yesterdayClaimOrder.gateOutDate))
                        {
                            errorStr = String.Format("第{0}行数据与前一天的出库日期不符。索赔号:{1}。", (i + 1 /*zero base*/ + 1 /*excel header*/), yesterdayClaimOrder.claimNo);
                        }
                    }

                    if (yesterdayClaimOrder.informDate.Equals(todayClaimOrder.informDate) == false)
                    {
                        if (!DateTimeUtils.IsEmpty(yesterdayClaimOrder.informDate))
                        {
                            errorStr = String.Format("第{0}行数据与前一天的通知日期不符。索赔号:{1}。", (i + 1 /*zero base*/ + 1 /*excel header*/), yesterdayClaimOrder.claimNo);
                        }
                    }

                    if (!string.IsNullOrEmpty(errorStr))
                    {
                        errorStrs.Add(errorStr);
                    }
                }
            }

            return(errorStrs);
        }
コード例 #2
0
        public void Save(IList <ClaimOrder> claimOrders)
        {
            OleDbConnection  conn        = null;
            OleDbTransaction transaction = null;

            try
            {
                conn = DBConnections.NewConnection();
                conn.Open();
                transaction = conn.BeginTransaction();
                OleDbCommand cmd = conn.CreateCommand();
                cmd.Transaction = transaction;

                cmd.CommandText =
                    "INSERT INTO claim_order" +
                    "(RTV, claimNo, storeNo, lotNo, supplierNo, supplierName, dept, " +
                    "qty, pcs, claimAmount, claimReason, decidedDate, arriveRTVDate, " +
                    "informDate, informDays, withdrawDate, gateOutDate, gateOutType, " +
                    "destoryInformDate, destoryType, " +
                    "informDateIfOver50Days" +
                    ", creationDate" +
                    ")" +
                    "VALUES " +
                    "(" +
                    "@RTV, @claimNo, @storeNo, @lotNo, @supplierNo, @supplierName, @dept, " +
                    "@qty, @pcs, @claimAmount, @claimReason, @decidedDate, @arriveRTVDate, " +
                    "@informDate, @informDays, @withdrawDate, @gateOutDate, @gateOutType, " +
                    "@destoryInformDate, @destoryType, " +
                    "@informDateIfOver50Days" +
                    ", @creationDate" +
                    ")";

                for (int i = 0; i < claimOrders.Count; i++)
                {
                    ClaimOrder claimOrder = claimOrders[i];

                    OleDbParameter dateParam;
                    cmd.Parameters.AddWithValue("@RTV", StringUtils.EmptyIfNull(claimOrder.rtv));
                    cmd.Parameters.AddWithValue("@claimNo", StringUtils.EmptyIfNull(claimOrder.claimNo));
                    cmd.Parameters.AddWithValue("@storeNo", StringUtils.EmptyIfNull(claimOrder.storeNo));
                    cmd.Parameters.AddWithValue("@lotNo", StringUtils.EmptyIfNull(claimOrder.lotNo));
                    cmd.Parameters.AddWithValue("@supplierNo", StringUtils.EmptyIfNull(claimOrder.supplierNo));
                    cmd.Parameters.AddWithValue("@supplierName", StringUtils.EmptyIfNull(claimOrder.supplierName));
                    cmd.Parameters.AddWithValue("@dept", StringUtils.EmptyIfNull(claimOrder.dept));
                    cmd.Parameters.AddWithValue("@qty", claimOrder.qty);
                    cmd.Parameters.AddWithValue("@pcs", claimOrder.pcs);
                    cmd.Parameters.AddWithValue("@claimAmount", claimOrder.claimAmount);
                    cmd.Parameters.AddWithValue("@claimReason", StringUtils.EmptyIfNull(claimOrder.claimReason));

                    dateParam       = new OleDbParameter("@decidedDate", OleDbType.Date);
                    dateParam.Value = claimOrder.decidedDate == DateTime.MinValue ? (object)DBNull.Value : claimOrder.decidedDate;
                    cmd.Parameters.Add(dateParam);

                    dateParam       = new OleDbParameter("@arriveRTVDate", OleDbType.Date);
                    dateParam.Value = claimOrder.arriveRTVDate == DateTime.MinValue ? (object)DBNull.Value : claimOrder.arriveRTVDate;
                    cmd.Parameters.Add(dateParam);

                    dateParam       = new OleDbParameter("@informDate", OleDbType.Date);
                    dateParam.Value = claimOrder.informDate == DateTime.MinValue ? (object)DBNull.Value : claimOrder.informDate;
                    cmd.Parameters.Add(dateParam);

                    cmd.Parameters.AddWithValue("@informDays", claimOrder.informDays);

                    dateParam       = new OleDbParameter("@withdrawDate", OleDbType.Date);
                    dateParam.Value = claimOrder.withdrawDate == DateTime.MinValue ? (object)DBNull.Value : claimOrder.withdrawDate;
                    cmd.Parameters.Add(dateParam);

                    dateParam       = new OleDbParameter("@gateOutDate", OleDbType.Date);
                    dateParam.Value = claimOrder.gateOutDate == DateTime.MinValue ? (object)DBNull.Value : claimOrder.gateOutDate;
                    cmd.Parameters.Add(dateParam);

                    cmd.Parameters.AddWithValue("@gateOutType", StringUtils.EmptyIfNull(claimOrder.gateOutType));

                    dateParam       = new OleDbParameter("@destoryInformDate", OleDbType.Date);
                    dateParam.Value = claimOrder.destoryInformDate == DateTime.MinValue ? (object)DBNull.Value : claimOrder.destoryInformDate;
                    cmd.Parameters.Add(dateParam);

                    cmd.Parameters.AddWithValue("@destoryType", StringUtils.EmptyIfNull(claimOrder.destoryType));

                    dateParam       = new OleDbParameter("@informDateIfOver50Days", OleDbType.Date);
                    dateParam.Value = claimOrder.informDateIfOver50Days == DateTime.MinValue ? (object)DBNull.Value : claimOrder.informDateIfOver50Days;
                    cmd.Parameters.Add(dateParam);

                    dateParam       = new OleDbParameter("@creationDate", OleDbType.Date);
                    dateParam.Value = DateTime.Now;
                    cmd.Parameters.Add(dateParam);

                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                transaction.Commit();
            }
            catch (Exception e)
            {
                transaction.Rollback();
                MessageBox.Show("Message: " + e.Message + "\r\n" + "Stack Trace: " + e.StackTrace, "·ÃÎÊÊý¾Ý¿â´íÎó");
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }
コード例 #3
0
        private static ClaimOrder ReadClaimOrder(OleDbDataReader reader)
        {
            ClaimOrder claimOrder = new ClaimOrder();

            int ordinal = reader.GetOrdinal("oid");

            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.oid = reader.GetInt32(ordinal);
            }

            ordinal = reader.GetOrdinal("RTV");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.rtv = reader.GetString(ordinal);
            }

            ordinal = reader.GetOrdinal("claimNo");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.claimNo = reader.GetString(ordinal);
            }

            ordinal = reader.GetOrdinal("storeNo");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.storeNo = reader.GetString(ordinal);
            }

            ordinal = reader.GetOrdinal("lotNo");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.lotNo = reader.GetString(ordinal);
            }

            ordinal = reader.GetOrdinal("supplierNo");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.supplierNo = reader.GetString(ordinal);
            }

            ordinal = reader.GetOrdinal("supplierName");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.supplierName = reader.GetString(ordinal);
            }

            ordinal = reader.GetOrdinal("dept");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.dept = reader.GetString(ordinal);
            }

            ordinal = reader.GetOrdinal("qty");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.qty = reader.GetDouble(ordinal);
            }

            ordinal = reader.GetOrdinal("pcs");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.pcs = reader.GetDouble(ordinal);
            }

            ordinal = reader.GetOrdinal("claimAmount");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.claimAmount = reader.GetDouble(ordinal);
            }

            ordinal = reader.GetOrdinal("claimReason");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.claimReason = reader.GetString(ordinal);
            }

            ordinal = reader.GetOrdinal("decidedDate");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.decidedDate = reader.GetDateTime(ordinal);
            }

            ordinal = reader.GetOrdinal("arriveRTVDate");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.arriveRTVDate = reader.GetDateTime(ordinal);
            }

            ordinal = reader.GetOrdinal("informDate");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.informDate = reader.GetDateTime(ordinal);
            }

            ordinal = reader.GetOrdinal("informDays");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.informDays = reader.GetDouble(ordinal);
            }

            ordinal = reader.GetOrdinal("withdrawDate");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.withdrawDate = reader.GetDateTime(ordinal);
            }

            ordinal = reader.GetOrdinal("gateOutType");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.gateOutType = reader.GetString(ordinal);
            }

            ordinal = reader.GetOrdinal("destoryInformDate");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.destoryInformDate = reader.GetDateTime(ordinal);
            }

            ordinal = reader.GetOrdinal("destoryType");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.destoryType = reader.GetString(ordinal);
            }

            ordinal = reader.GetOrdinal("informDateIfOver50Days");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.informDateIfOver50Days = reader.GetDateTime(ordinal);
            }

            ordinal = reader.GetOrdinal("creationDate");
            if (!reader.IsDBNull(ordinal))
            {
                claimOrder.creationDate = reader.GetDateTime(ordinal);
            }

            return(claimOrder);
        }
コード例 #4
0
        public static List <ClaimOrder> ParseXLSX(string filePath)
        {
            List <ClaimOrder> claimOrders = new List <ClaimOrder>();

            if (!PreCheck(filePath))
            {
                return(claimOrders);
            }

            Worksheet       worksheet = Workbook.Load(filePath).Worksheets[0];
            WorkSheetReader reader    = new WorkSheetReader(worksheet);

            int    rowNo      = 2;
            string columnName = string.Empty;

            foreach (IDictionary <string, object> aRow in reader)
            {
                try
                {
                    ClaimOrder claimOrder = new ClaimOrder();

                    object value;
                    columnName = ClaimOrderTitleConstants.RTV;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.rtv = value.ToString();
                    }

                    columnName = ClaimOrderTitleConstants.WMT_CLAIM_NO;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.claimNo = value.ToString();
                    }

                    columnName = ClaimOrderTitleConstants.STORE_NO;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.storeNo = value.ToString();
                    }

                    columnName = ClaimOrderTitleConstants.LOT_NO;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.lotNo = value.ToString();
                    }

                    columnName = ClaimOrderTitleConstants.SUPPLIER_NO;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.supplierNo = value.ToString();
                    }

                    columnName = ClaimOrderTitleConstants.SUPPLIER_NAME;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.supplierName = value.ToString();
                    }

                    columnName = ClaimOrderTitleConstants.DEPT;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.dept = value.ToString();
                    }

                    columnName = ClaimOrderTitleConstants.QTY;
                    if (null != (value = aRow[columnName]))
                    {
                        Double.TryParse(value.ToString(), out claimOrder.qty);
                    }

                    columnName = ClaimOrderTitleConstants.PCS;
                    if (null != (value = aRow[columnName]))
                    {
                        Double.TryParse(value.ToString(), out claimOrder.pcs);
                    }

                    columnName = ClaimOrderTitleConstants.CLAIM_AMOUNT;
                    if (null != (value = aRow[columnName]))
                    {
                        Double.TryParse(value.ToString(), out claimOrder.claimAmount);
                    }

                    columnName = ClaimOrderTitleConstants.CLAIM_REASON;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.claimReason = value.ToString();
                    }

                    columnName = ClaimOrderTitleConstants.DECIDED_DATE;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.decidedDate = DateTime.FromOADate((double)value);
                    }

                    columnName = ClaimOrderTitleConstants.ARRIVE_RTV_DATE;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.arriveRTVDate = DateTime.FromOADate((double)value);
                    }

                    columnName = ClaimOrderTitleConstants.INFORM_DATE;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.informDate = DateTime.FromOADate((double)value);
                    }

                    columnName = ClaimOrderTitleConstants.WITHDRAW_DATE;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.withdrawDate = DateTime.FromOADate((double)value);
                    }

                    columnName = ClaimOrderTitleConstants.GATEOUT_DATE;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.gateOutDate = DateTime.FromOADate((double)value);
                    }

                    columnName = ClaimOrderTitleConstants.GATEOUT_TYPE;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.gateOutType = value.ToString();
                    }

                    columnName = ClaimOrderTitleConstants.DESTORY_INFORM_DATE;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.destoryInformDate = DateTime.FromOADate((double)value);
                    }

                    columnName = ClaimOrderTitleConstants.DESTORY_TYPE;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.destoryType = value.ToString();
                    }

                    columnName = ClaimOrderTitleConstants.INFORM_DATE_IF_OVER_50_DAYS;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.informDateIfOver50Days = DateTime.FromOADate((double)value);
                    }

                    columnName = ClaimOrderTitleConstants.INFORM_DAYS;
                    if (null != (value = aRow[columnName]))
                    {
                        claimOrder.informDays = (double)value;
                    }
//                    //cause there is bug of fragistics to calculate the value of this cell having a formula
//                    if (DateTimeUtils.IsEmpty(claimOrder.informDate))
//                    {
//                        claimOrder.informDays = 0;
//                    }
//                    else
//                    {
//                        if (DateTimeUtils.IsEmpty(claimOrder.gateOutDate) &&
//                            !DateTimeUtils.IsEmpty(claimOrder.destoryInformDate))
//                        {
//                            claimOrder.informDays = claimOrder.destoryInformDate.Subtract(claimOrder.informDate).Days;
//                        }
//                        else if (!DateTimeUtils.IsEmpty(claimOrder.gateOutDate) &&
//                                 DateTimeUtils.IsEmpty(claimOrder.destoryInformDate))
//                        {
//                            claimOrder.informDays = claimOrder.gateOutDate.Subtract(claimOrder.informDate).Days;
//                        }
//                        else if (!DateTimeUtils.IsEmpty(claimOrder.gateOutDate) &&
//                                 !DateTimeUtils.IsEmpty(claimOrder.destoryInformDate))
//                        {
//                            DateTime min = claimOrder.gateOutDate.CompareTo(claimOrder.destoryInformDate) < 0
//                                               ? claimOrder.gateOutDate
//                                               : claimOrder.destoryInformDate;
//                            claimOrder.informDays = min.Subtract(claimOrder.informDate).Days;
//                        }
//                        else
//                        {
//                            claimOrder.informDays = DateTime.Today.Subtract(claimOrder.informDate).Days;
//                        }
//                    }
                    rowNo++;
                    claimOrders.Add(claimOrder);
                }
                catch (InvalidCastException)
                {
                    MessageBox.Show(String.Format("第{0}行,列[{1}]的格式错误!", rowNo, columnName));
                    return(new List <ClaimOrder>());
                }
                catch (Exception e)
                {
                    string[] required = new string[] {
                        ClaimOrderTitleConstants.RTV,
                        ClaimOrderTitleConstants.WMT_CLAIM_NO,
                        ClaimOrderTitleConstants.STORE_NO,
                        ClaimOrderTitleConstants.LOT_NO,
                        ClaimOrderTitleConstants.SUPPLIER_NO,
                        ClaimOrderTitleConstants.SUPPLIER_NAME,
                        ClaimOrderTitleConstants.QTY,
                        ClaimOrderTitleConstants.PCS,
                        ClaimOrderTitleConstants.CLAIM_AMOUNT,
                        ClaimOrderTitleConstants.CLAIM_REASON,
                        ClaimOrderTitleConstants.DECIDED_DATE,
                        ClaimOrderTitleConstants.ARRIVE_RTV_DATE,
                        ClaimOrderTitleConstants.INFORM_DATE,
                        ClaimOrderTitleConstants.WITHDRAW_DATE,
                        ClaimOrderTitleConstants.GATEOUT_DATE,
                        ClaimOrderTitleConstants.GATEOUT_TYPE,
                        ClaimOrderTitleConstants.DESTORY_INFORM_DATE,
                        ClaimOrderTitleConstants.DESTORY_TYPE,
                        ClaimOrderTitleConstants.INFORM_DATE_IF_OVER_50_DAYS,
                        ClaimOrderTitleConstants.INFORM_DAYS
                    };
                    MessageBox.Show(e.Message + "\n需要:\n" + StringUtils.Join(", ", required) + "\n\n\n实际:\n" + StringUtils.Join(", ", aRow.Keys), "文件格式错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return(new List <ClaimOrder>());
                }
            }

            return(claimOrders);
        }