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