/// <summary>
/// Fill subscriber datas
/// </summary>
/// <param name="subscriberNo"></param>
public void FillDatas(string subscriberNo = null)
{
int totalBlackList = 0;
FileParser fileParser = new FileParser();
PaymentProvider paymentProvider = new PaymentProvider();
string targetPath = @"Datas";
Directory.CreateDirectory(targetPath);
string[] filesindirectory = Directory.GetFiles(targetPath);
List <Subscriber> subscribers = new List <Subscriber>();
ImportData importData;
foreach (var item in filesindirectory)
{
importData = paymentProvider.ImportPayment(FileParser.ParseFile(item));
subscribers.AddRange(importData.Subscribers);
totalBlackList += importData.BlackList;
}
if (subscriberNo != string.Empty && subscriberNo != null)
{
subscribers = subscribers.Where(i => i.No == subscriberNo).ToList();
}
List <PaymentData> paymentDatas = new List <PaymentData>();
List <InvoiceData> invoiceDatas = new List <InvoiceData>();
foreach (var item in subscribers)
{
foreach (var payment in item.Payments)
{
paymentDatas.Add(new PaymentData
{
Amount = payment.Amount,
SubscriberNo = payment.Subscriber.No,
Date = payment.Date
});
}
lblPayment.Text = paymentDatas.Sum(i => i.Amount).ToString();
foreach (var invoice in item.Invoices)
{
invoiceDatas.Add(new InvoiceData
{
DueDate = invoice.DueDate,
InvoiceNo = invoice.No,
Period = invoice.Period,
SubscriberNo = invoice.Subscriber.No
});
}
}
DataTable dt = new DataTable();
ConvertDataTable convertDataTable = new ConvertDataTable();
dt = convertDataTable.ConvertToDataTable <InvoiceData>(invoiceDatas);
dViewInvoices.DataSource = dt;
DataTable dt2 = new DataTable();
ConvertDataTable convertDataTable2 = new ConvertDataTable();
dt2 = convertDataTable.ConvertToDataTable <PaymentData>(paymentDatas);
dViewPayments.DataSource = dt2;
if (totalBlackList > 0)
{
MessageBox.Show($"{totalBlackList } datas could not be added to the system.");
}
}
/// <summary>
/// 投资统计接口
/// </summary>
/// <param name="t"></param>
/// <param name="token"></param>
/// <param name="startdate"></param>
/// <param name="enddate"></param>
/// <param name="page"></param>
/// <param name="pagesize"></param>
/// <returns></returns>
public string Tongji_Invest(HttpRequestBase Request, string t = "1", string token = "", string startdate = "", string enddate = "", int page = 1, int pagesize = 10)
{
try
{
if (CheckTimeOut(t))
{
return("{\"code\":1008,\"msg\":\"请求失效\"}");
}
if (string.IsNullOrEmpty(token))
{
return("{\"code\":1008,\"msg\":\"参数token错误\"}");
}
string localToken = Utils.MD5(Utils.MD5(t) + _client_secret); //EncryptHelper.GetMd5Str32(EncryptHelper.GetMd5Str32(t, "x") + _client_secret, "x");
if (localToken != token)
{
return("{\"code\":1008,\"msg\":\"请求被篡改\"}");
}
//var retData = from item in ef.hx_member_table
// join channel in ef.hx_Channel
// on item.channel_invitedcode equals channel.Invitedcode
// join invest in ef.hx_Bid_records
// on item.registerid equals invest.investor_registerid
// where channel.ChannelName == "xicai"
// select new
// {
// username = item.username,
// invest_time = invest.invest_time,
// targetid = invest.targetid,
// investor_registerid = invest.investor_registerid,
// borrower_registerid = invest.borrower_registerid,
// bid_records_id = invest.bid_records_id,
// investment_amount = invest.investment_amount,
// mobile = item.mobile
// };
StringBuilder sql = new StringBuilder(@"
select
row_number() over (order by c.registration_time asc) as RowNumber,
'' url,
0 commision,
e.targetid pid,
e.invest_time [datetime],
[money] Earnings,
bid_records_id id,
investment_amount [money],
c.mobile phone,
f.annual_interest_rate rate,
case f.unit_day when 1 then ISNULL(life_of_loan,0)*30 else life_of_loan end life_cycle,
c.username username
from hx_member_table c
join hx_Channel d
on c.channel_invitedcode = d.Invitedcode
join hx_Bid_records e
on c.registerid = e.investor_registerid
join hx_borrowing_target f
on e.targetid = f.targetid
left join
(select sum(interestpayment) [money],
investor_registerid,
borrower_registerid,
targetid
from hx_income_statement
group by investor_registerid,borrower_registerid,targetid) g
on e.investor_registerid = g.investor_registerid and e.borrower_registerid = g.borrower_registerid and e.targetid = g.targetid
where d.ChannelName = 'xicai'" );
StringBuilder sqlCount = new StringBuilder(@"
select count(*)
from hx_member_table c
join hx_Channel d
on c.channel_invitedcode = d.Invitedcode
join hx_Bid_records e
on c.registerid = e.investor_registerid
join hx_borrowing_target f
on e.targetid = f.targetid
left join
(select sum(interestpayment) [money],
investor_registerid,
borrower_registerid,
targetid
from hx_income_statement
group by investor_registerid,borrower_registerid,targetid) g
on e.investor_registerid = g.investor_registerid and e.borrower_registerid = g.borrower_registerid and e.targetid = g.targetid
where d.ChannelName = 'xicai'" );
if (!string.IsNullOrWhiteSpace(startdate))
{
DateTime sTime = DateTime.Parse(startdate);
//retData = from item in retData
// where item.invest_time >= sTime
// select item;
sql.Append(" and e.invest_time >=");
sql.Append(sTime.ToString("yyyy-MM-dd"));
sqlCount.Append(" and e.invest_time >=");
sqlCount.Append(sTime.ToString("yyyy-MM-dd"));
}
if (!string.IsNullOrWhiteSpace(enddate))
{
DateTime eTime = DateTime.Parse(enddate);
//retData = from item in retData
// where item.invest_time <= eTime
// select item;
sql.Append(" and e.invest_time <=");
sql.Append(eTime.ToString("yyyy-MM-dd"));
sqlCount.Append(" and e.invest_time <=");
sqlCount.Append(eTime.ToString("yyyy-MM-dd"));
}
//var resData = from item in retData
// join target in ef.hx_borrowing_target
// on item.targetid equals target.targetid
// join earning in
// (from item in ef.hx_income_statement
// group item by new
// {
// investor = item.investor_registerid,
// borrower = item.borrower_registerid,
// targetID = item.targetid
// } into g
// select new
// {
// investor = g.Key.investor,
// borrower = g.Key.borrower,
// targetID = g.Key.targetID,
// money = g.Sum(c => c.interestpayment ?? 0)
// })
// on new { investor = item.investor_registerid, borrower = item.borrower_registerid, targetID = item.targetid }
// equals new { investor = earning.investor, borrower = earning.borrower, targetID = earning.targetID }
// select new
// {
// datetime = item.invest_time,
// Earnings = earning.money,
// id = item.bid_records_id,
// money = item.investment_amount,
// phone = item.mobile,
// pid = item.targetid,
// rate = target.annual_interest_rate,
// term = target.unit_day == 1 ? (target.life_of_loan ?? 0) * 30 : (target.life_of_loan ?? 0),
// username = item.username
// };
if (page <= 0)
{
page = 1;
}
if (pagesize > 50)
{
pagesize = 50;
}
if (pagesize <= 0)
{
pagesize = 1;
}
DataTable dtCount = DbHelperSQL.GET_DataTable_List(sqlCount.ToString());
string filnalSql = "select top " + pagesize + @" * from (" + sql.ToString() + ")as temp where RowNumber > " + (pagesize * (page - 1));
DataTable dtRecord = DbHelperSQL.GET_DataTable_List(filnalSql);
//var totalCount = resData.Count();
//var cc = resData.OrderBy(c => c.datetime).Skip((page - 1) * pagesize).Take(pagesize);
//var res = (from item in cc.ToList()
// select new tj_data
// {
// url = Request.Url.GetLeftPart(UriPartial.Authority) + "/invest_borrow_" + item.pid + ".html",
// commision = 0,
// pid = (item.pid ?? 0).ToString(),
// datetime = (Convert.ToDateTime(item.datetime).ToString("yyyy-MM-dd HH:mm:ss")),
// Earnings = item.Earnings,
// id = item.id.ToString(),
// money = item.money ?? 0,
// phone = item.phone,
// rate = item.rate ?? 0,
// life_cycle = item.term,
// username = item.username
// }).ToList();
List <tj_data> res = new List <tj_data>();
res = ConvertDataTable <tj_data> .ConvertToList(dtRecord);
int totalCount = Convert.ToInt32(dtCount.Rows[0][0]);
string r = "{\"list\":" + JsonConvert.SerializeObject(res, Formatting.Indented, timeFormat) + ",\"total\":" + totalCount + ",\"code\":0}";
return(r);
}
catch (Exception ex)
{
LogInfo.WriteLog("投资统计接口，异常消息：" + ex.Message + ",Trace:" + ex.StackTrace + ex.InnerException.StackTrace);
return("操作异常，请稍后重试");
}
}
/// <summary>
/// 创建订单时记录打折活动信息
/// </summary>
/// <param name="requestList"></param>
/// <returns></returns>
public static async Task <bool> SaveCreateOrderDiscountInfo(List <SalePromotionActivityDiscountOrder> orderInfoList)
{
bool result = true;
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Gungnir"].ConnectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
using (var cmd = new SqlCommand("", conn, tran))
{
try
{
var orderInfoTmp = orderInfoList.Select(item => new
{
UserId = item.UserId,
OrderId = item.OrderId,
Pid = item.Pid,
Num = item.Num,
ActivityId = item.ActivityId
});
DataTable orderInfoDT = ConvertDataTable.ToDataTable(orderInfoTmp);
cmd.CommandText = @"CREATE TABLE #orderInfoTmp([UserId] [nvarchar](100) NOT NULL,
[OrderId] [int] NULL,
[Pid] [nvarchar](100) NOT NULL,
[Num] [int] NOT NULL,
[ActivityId] [nvarchar](100) NOT NULL);" ;
await cmd.ExecuteNonQueryAsync();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran))
{
bulkcopy.BulkCopyTimeout = 660;
bulkcopy.DestinationTableName = "#orderInfoTmp";
bulkcopy.WriteToServer(orderInfoDT);
bulkcopy.Close();
}
//若记录已存在,覆盖；不存在则插入
string sql = @"MERGE INTO [Activity].[dbo].[SalePromotionDiscountOrder] WITH ( ROWLOCK ) AS do
USING #orderInfoTmp AS temp
ON do.OrderId = temp.OrderId and do.UserId=temp.UserId
and do.Pid=temp.Pid and do.OrderStatus=1
WHEN MATCHED THEN
UPDATE SET do.Num=temp.Num,
do.ActivityId=temp.ActivityId,
do.LastUpdateDateTime=getdate()
WHEN NOT MATCHED THEN
INSERT ([UserId],[OrderId],[Pid],[Num],
[ActivityId],[OrderStatus],[CreateDateTime])
VALUES (temp.UserId,temp.OrderId,temp.Pid,temp.Num,
temp.ActivityId,1,getdate());";
cmd.CommandText = sql;
if ((await cmd.ExecuteNonQueryAsync()) != orderInfoList.Count)
{
result = false;
}
if (result)
{
tran.Commit();
var keys = new List <string>();
foreach (var item in orderInfoList)
{
keys.Add(item.ActivityId + "/" + item.Pid);
keys.Add(item.ActivityId + "/" + item.UserId);
keys.Add(item.OrderId.ToString());
}
DBReadOnlyFlagHelper.SetDBReadOnlyFlagCache(SalePromotionActivityConst.SalePromotionDiscountOrder, keys.ToList());
}
else
{
tran.Rollback();
Logger.Error($"SaveCreateOrderDiscountInfo插入订单折扣信息记录失败，订单id{orderInfoList[0].OrderId}");
}
}
catch (Exception ex)
{
result = false;
tran.Rollback();
Logger.Error($"SaveCreateOrderDiscountInfo插入订单折扣信息记录异常，订单id{orderInfoList[0].OrderId}，异常：{ex}");
}
}
}
}
return(result);
}
/// <summary>
/// 修改活动商品的已售数量
/// </summary>
/// <param name="orderInfoList"></param>
/// <returns></returns>
public static async Task <bool> UpdateProductSoldQuantity(List <ActivityPidSoldNumModel> orderInfoList)
{
bool result = true;
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Gungnir"].ConnectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
using (var cmd = new SqlCommand("", conn, tran))
{
try
{
var orderInfoTmp = orderInfoList.Select(item => new
{
Pid = item.Pid,
SoldQuantity = item.SoldNum >= 0 ? item.SoldNum : 0,
ActivityId = item.ActivityId
});
DataTable orderInfoDT = ConvertDataTable.ToDataTable(orderInfoTmp);
cmd.CommandText = @"CREATE TABLE #orderInfoTmp([Pid] [nvarchar](100) NOT NULL,
[SoldQuantity] [int] NOT NULL,
[ActivityId] [nvarchar](100) NOT NULL);";
await cmd.ExecuteNonQueryAsync();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran))
{
bulkcopy.BulkCopyTimeout = 660;
bulkcopy.DestinationTableName = "#orderInfoTmp";
bulkcopy.WriteToServer(orderInfoDT);
bulkcopy.Close();
}
string sql = @"MERGE INTO [Activity].[dbo].[SalePromotionActivityProduct] WITH ( ROWLOCK ) AS p
USING #orderInfoTmp AS temp
ON p.ActivityId=temp.ActivityId and p.Pid=temp.Pid and p.Is_Deleted=0
WHEN MATCHED THEN
UPDATE SET p.SoldQuantity=temp.SoldQuantity;";
cmd.CommandText = sql;
await cmd.ExecuteNonQueryAsync();
if (result)
{
tran.Commit();
}
else
{
tran.Rollback();
Logger.Error($"UpdateProductSoldQuantity更新活动打折商品已售数量异常，" +
$"活动id{string.Join(",",orderInfoList.Select(o=>o.ActivityId).Distinct())}");
}
}
catch (Exception ex)
{
result = false;
tran.Rollback();
Logger.Error($"UpdateProductSoldQuantity,更新活动打折商品已售数量异常," +
$"活动id{string.Join(",", orderInfoList.Select(o => o.ActivityId).Distinct())},ex：{ex}");
}
}
}
}
return(result);
}
protected void lknTerminar_Click(object sender, EventArgs e)
{
string mensaje = String.Empty;
AdminProductos aPro = new AdminProductos();
lblCambioTitulo.Text = "Cambio: ";
Double value;
if (txtRecibido.Text.Equals(""))
{
//txtRecibido.Text = "0";
mensaje = "Debe ingresar el valor entregado por el Cliente";
}
else
{
if (txtNombre.Text.Equals(""))
{
txtNombre.Text = "Usuario No Definido";
}
tblUsuarios usuario;
if (Session["user"] != null)
{
if (Session["ventas"] != null)
{
listaProductos = (List <ProductoVenta>)Session["ventas"];
}
updateTotal();
usuario = (tblUsuarios)Session["user"];
int factura = aPro.insertarFactura(txtNombre.Text, txtCelular.Text, txtDireccion.Text, valorFactura, usuario.nombres + " " + usuario.apellidos, usuario.id_sucursal.Value);
if (factura != 0)
{
if (Double.TryParse((Convert.ToDecimal(txtRecibido.Text) - valorFactura).ToString(), out value))
{
lblCambioTitulo.Text = lblCambioTitulo.Text + " " + String.Format(System.Globalization.CultureInfo.CurrentCulture, "{0:C2}", value);
}
foreach (ProductoVenta p in listaProductos)
{
aPro.insertarVenta(factura, p.IdProducto, (int)p.Cantidad, (double)p.Valor, (double)p.Total);
}
LocalReport localReport = new LocalReport();
localReport.ReportPath = "rptFactura.rdlc";
ReportParameter[] parameters = new ReportParameter[2];
parameters[0] = new ReportParameter("nombre", txtNombre.Text);
parameters[1] = new ReportParameter("facturaId", factura.ToString());
localReport.SetParameters(parameters);
ReportDataSource rds = new ReportDataSource("dsProductosVentas", ConvertDataTable.ConvertToDataTable(listaProductos));
localReport.DataSources.Add(rds);
ImprimirArchivos imp = new ImprimirArchivos();
imp.Export(localReport);
imp.Print();
}
}
}
}
