public void CreateReceivedDeducted(ReceivedDeducted rd)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", rd.CompanyId),
SqlUtilities.GenerateInputIntParameter("@client_id", rd.Client.Id),
SqlUtilities.GenerateInputNVarcharParameter("@sr_encode", 50, rd.SrEncode),
SqlUtilities.GenerateInputNVarcharParameter("@ar_encode", 50, rd.ArEncode),
SqlUtilities.GenerateInputNVarcharParameter("@ar_account", 50, rd.ArAccount),
SqlUtilities.GenerateInputIntParameter("@ar_user_id", rd.ArUserId),
SqlUtilities.GenerateInputParameter("@money", SqlDbType.Decimal, rd.Money),
SqlUtilities.GenerateInputDateTimeParameter("@create_time", rd.CreateTime)
};
string sql = "INSERT INTO received_deducted(company_id, client_id, sr_encode, ar_encode, money, create_time, ar_account, ar_user_id) VALUES(@company_id, @client_id, @sr_encode, @ar_encode, @money, @create_time, @ar_account, @ar_user_id)";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public PaginationQueryResult<ReceivedDeducted> GetReceivedDeductedByParameter(PaginationQueryCondition condition, int compId, DateTime startDate, DateTime endDate)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@company_id", compId),
SqlUtilities.GenerateInputDateTimeParameter("@start_date", startDate),
SqlUtilities.GenerateInputDateTimeParameter("@end_date", endDate)
};
DateTime minTime = new DateTime(1999, 1, 1);
string sqlParam = " ";
if (startDate > minTime && endDate > minTime)
{
sqlParam += " AND create_time BETWEEN @start_date AND @end_date";
}
else if (startDate > minTime && endDate <= minTime)
{
sqlParam += " AND create_time >= @start_date ";
}
else if (startDate <= minTime && endDate > minTime)
{
sqlParam += " AND create_time <= @end_date";
}
PaginationQueryResult<ReceivedDeducted> result = new PaginationQueryResult<ReceivedDeducted>();
string sql = "SELECT TOP " + condition.PageSize + " id, company_id, client_id, sr_encode, ar_encode, money, create_time, ar_account, ar_user_id FROM received_deducted WHERE company_id = @company_id AND is_delete = 0" + sqlParam;
if (condition.CurrentPage > 1)
{
sql += " AND id< (SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " id FROM received_deducted WHERE company_id = @company_id AND is_delete = 0" + sqlParam + " ORDER BY id DESC) AS R )";
}
sql += " ORDER BY id DESC; SELECT COUNT(*) FROM received_deducted WHERE company_id = @company_id AND is_delete = 0" + sqlParam;
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
ReceivedDeducted rd = new ReceivedDeducted();
rd.Id = dr.GetInt32(0);
rd.CompanyId = dr.GetInt32(1);
Client client = new Client();
client = new ClientDAL().GetClientById(dr.GetInt32(2));
rd.Client = client;
rd.SrEncode = dr.GetString(3);
rd.ArEncode = dr.GetString(4);
rd.Money = dr.GetDecimal(5);
rd.CreateTime = dr.GetDateTime(6);
rd.ArAccount = dr.GetString(7);
rd.ArUserId = dr.GetInt32(8);
result.Results.Add(rd);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public void UpdateReceivedDeducted(ReceivedDeducted rd)
{
SqlParameter[] param=new SqlParameter[]{
SqlUtilities.GenerateInputIntParameter("@id", rd.Id),
SqlUtilities.GenerateInputParameter("@money", SqlDbType.Decimal, rd.Money)
};
string sql = "UPDATE received_deducted SET money = @money WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public ReceivedDeducted GetReceivedDeductedBySrEncode(string srEncode)
{
ReceivedDeducted rd = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@sr_encode", 50, srEncode)
};
string sql = "SELECT id, company_id, client_id, sr_encode, ar_encode, money, create_time, ar_account, ar_user_id FROM received_deducted WHERE sr_encode = @sr_encode";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
rd = new ReceivedDeducted();
rd.Id = dr.GetInt32(0);
rd.CompanyId = dr.GetInt32(1);
Client client = new Client();
client = new ClientDAL().GetClientById(dr.GetInt32(2));
rd.Client = client;
rd.SrEncode = dr.GetString(3);
rd.ArEncode = dr.GetString(4);
rd.Money = dr.GetDecimal(5);
rd.CreateTime = dr.GetDateTime(6);
rd.ArAccount = dr.GetString(7);
rd.ArUserId = dr.GetInt32(8);
}
}
return rd;
}
public static void CreateReceivedDeducted(ReceivedDeducted rd)
{
dal.CreateReceivedDeducted(rd);
}
public static void UpdateReceivedDeducted(ReceivedDeducted rd)
{
dal.UpdateReceivedDeducted(rd);
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string clientName = Request.Form[txtClientName.ID].Trim();
string invoice = Request.Form[txtInvoice.ID].Trim();
string remark = Request.Form[txtRemark.ID].Trim();
string strReceivedTime = Request.Form[txtReceivedTime.ID].Trim();
DateTime receivedTime = new DateTime(1999, 1, 1);
if (string.IsNullOrEmpty(strReceivedTime) || !DateTime.TryParse(strReceivedTime, out receivedTime))
{
lblMsg.Text = "收款时间不能为空，且只能为时间格式！";
return;
}
if (string.IsNullOrEmpty(invoice) || Validator.IsMatchLessThanChineseCharacter(invoice, INVOICE_NUMBER_LENGTH))
{
lblMsg.Text = "发票号码不能为空，且长度不能超过" + INVOICE_NUMBER_LENGTH + "个字符！";
return;
}
if (string.IsNullOrEmpty(clientName) || clientName == "请输入客户姓名拼音的首字母" || Validator.IsMatchLessThanChineseCharacter(clientName, CLIENT_NAME_LENGTH))
{
lblMsg.Text = "客户姓名不能为空，且长度不能超过" + CLIENT_NAME_LENGTH + "个字符！";
return;
}
Client client = ClientOperation.GetClientByRealName(clientName);
if (client == null)
{
lblMsg.Text = "客户不存在！";
return;
}
if (!string.IsNullOrEmpty(remark) && Validator.IsMatchLessThanChineseCharacter(remark, REMARK_LENGTH))
{
lblMsg.Text = "备注长度不能超过" + REMARK_LENGTH + "个字符！";
return;
}
decimal paid = 0;
decimal exchangeRate = 1;
decimal money = 0;
if (ddlCurrencyType.SelectedItem.Value == "2")
{
if (!decimal.TryParse(Request.Form[txtClientPaid.ID].Trim(), out paid))
{
lblMsg.Text = "客户付款金额只能为数字！";
return;
}
if (!decimal.TryParse(Request.Form[txtExchangeRate.ID].Trim(), out exchangeRate))
{
lblMsg.Text = "当前汇率只能为数字！";
return;
}
string strActualReceived = Request.Form["txtActualReceived"];
if (!decimal.TryParse(strActualReceived, out money))
{
return;
}
}
else
{
if (!decimal.TryParse(Request.Form[txtReceivedMoney.ID], out money))
{
lblMsg.Text = "收款金额只能为数字！";
return;
}
}
if (money <= 0)
{
lblMsg.Text = "付款金额必须大于0！";
return;
}
Recharge recharge = new Recharge();
recharge.Account = ddlReceiveAccount.SelectedItem.Text;
recharge.ClientId = client.Id;
recharge.CreateTime = DateTime.Now;
recharge.UserId = user.Id;
recharge.CurrencyType = EnumConvertor.ConvertToCurrencyType(byte.Parse(ddlCurrencyType.SelectedItem.Value));
recharge.Encode = StringHelper.GetEncodeNumber("SK");
recharge.Invoice = invoice;
recharge.ExchangeRate = exchangeRate;
recharge.Money = money;
recharge.Paid = paid;
recharge.ReceiveTime = receivedTime;
recharge.Remark = remark;
RechargeOperation.CreateRecharge(recharge);
decimal balance = client.Balance + money;
client.Balance = balance;
ClientOperation.UpdateClientBalance(client);
List<ShouldReceive> result = ShouldReceiveOperation.GetShouldReceiveByClientId(client.Id);
if (result.Count > 0)
{
foreach (ShouldReceive sr in result)
{
if (sr.Money <= money)
{
sr.Status = true;
sr.Type = "订单已收";
sr.ReceiveTime = DateTime.Now;
ShouldReceiveOperation.UpdateShouldReceive(sr);
ReceivedDeducted rd = new ReceivedDeducted();
rd.Money = sr.Money;
rd.Client = client;
rd.ArEncode = recharge.Encode;
rd.ArAccount = recharge.Account;
rd.ArUserId = recharge.UserId;
rd.SrEncode = sr.Encode;
rd.CreateTime = DateTime.Now;
rd.CompanyId = client.CompanyId;
ShouldReceiveOperation.CreateReceivedDeducted(rd);
money = money - sr.Money;
}
else if (money > 0)
{
sr.Money = sr.Money - money;
ShouldReceiveOperation.UpdateShouldReceive(sr);
ReceivedDeducted rd = new ReceivedDeducted();
rd.Money = money;
rd.Client = client;
rd.ArEncode = recharge.Encode;
rd.ArAccount = recharge.Account;
rd.ArUserId = recharge.UserId;
rd.SrEncode = sr.Encode;
rd.CreateTime = DateTime.Now;
rd.CompanyId = client.CompanyId;
ShouldReceiveOperation.CreateReceivedDeducted(rd);
sr.Money = money;
sr.Status = true;
sr.Type = "订单已收";
sr.ReceiveTime = DateTime.Now;
ShouldReceiveOperation.CreateOrderShouldReceive(sr);
money = 0;
MailSend(client, recharge.Money);
}
}
}
MailSend(client, recharge.Money);
}