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);
}
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();
}
}
}
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);
}
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);
}