接口
using DBUtility;
using ErpInterface.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using System.Configuration;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk;
using System.Net;
using System.Web.Script.Serialization;
namespace ErpInterface.Controllers
{
public class HomeController : Controller
{
DbHelper helper = new DbHelper("3");
public ActionResult Index()
{
//var query = helper.GetDataSet(@"select * from Users");
//if (query.Tables[0].Rows.Count > 0)
//{
// foreach (DataRow dr in query.Tables[0].Rows)
// {
// var headHref = Convert.ToString(dr["UserName"]);
// var userName = Convert.ToString(dr["PassWord"]);
// var trueName = Convert.ToString(dr["Email"]);
// }
// return View();
//}
return View();
}
/// <summary>
/// 查询代理商信息（agent_code为空返回所有）
/// agent_code：代理商编码
/// </summary>
/// <param name="agent_code"></param>
/// <returns></returns>
[HttpPost]
public ActionResult getAgentInfo(string agent_code)
{
//FileStream fs = new FileStream(@"d:\接口.txt", FileMode.OpenOrCreate, FileAccess.Write);
//StreamWriter sw = new StreamWriter(fs);
//sw.BaseStream.Seek(0, SeekOrigin.End);
//sw.WriteLine("日志开始" + DateTime.Now.ToString() + "编号：" + agent_code + "\n");
//sw.Flush();
//sw.Close();
//fs.Close();
AgentInfo agentInfo = new AgentInfo();
try
{
string sql = string.Empty;
if (string.IsNullOrWhiteSpace(agent_code))
sql = string.Format(@"select new_proxynumber,new_name from new_proxyservice");
else
sql = string.Format(@"select new_proxynumber,new_name from new_proxyservice where new_proxynumber='{0}'", agent_code);
var query = helper.GetDataSet(sql);
if (query.Tables[0].Rows.Count > 0)
{
agentInfo.result = "0";
agentInfo.message = "sucess";
List<Agent> listAgent = new List<Agent>();
foreach (DataRow dr in query.Tables[0].Rows)
{
Agent agent = new Agent();
agent.agent_code = dr["new_proxynumber"].ToString();
agent.agent_name = dr["new_name"].ToString();
listAgent.Add(agent);
}
agentInfo.agent = listAgent;
//return JsonConvert.SerializeObject(agentInfo, Formatting.Indented);
//return "{\"result\": \"1\",\"message\": \"error\",\"agent\": null}";
//return agentInfo;
return Json(agentInfo, JsonRequestBehavior.AllowGet);
}
else
{
agentInfo.result = "1";
agentInfo.message = "error";
agentInfo.agent = null;
//return JsonConvert.SerializeObject(agentInfo, Formatting.Indented);
//return "{\"result\": \"1\",\"message\": \"error\",\"agent\": null}";
return Json(agentInfo, JsonRequestBehavior.AllowGet);
}
}
catch (Exception e)
{
agentInfo.result = "2";
agentInfo.message = "systemError";
agentInfo.agent = null;
//return "{\"result\": \"1\",\"message\": \"error\",\"agent\": null}";
//return JsonConvert.SerializeObject(agentInfo, Formatting.Indented);
return Json(agentInfo, JsonRequestBehavior.AllowGet);
}
}
/// <summary>
/// 订单提交
/// </summary>
/// <param name="agent_code"></param>
/// <returns></returns>
[HttpPost]
public ActionResult setOrderInfo(string account_code, string agent_code, string product_code, string user_name, string user_card, string user_contact, string user_addr, string order_date, string order_type, string card_name, string card_type, string card_number, string bank_type, string bank_city, string bank_name, string bank_card, string bank_mobile, string bank_date, string pay_type, string pay_date, string pay_money, string pay_number, string voucher_money, string vouvher_code)
{
try
{
//string account_code = Request.Params["account_code"];//订单号：必填
if (string.IsNullOrWhiteSpace(account_code))
return Json(new { result = "1", message = "订单号不能为空", errcode = "10001" }, JsonRequestBehavior.AllowGet);
//string agent_code = Request.Params["agent_code"];//代理商编号：必选
if (string.IsNullOrWhiteSpace(agent_code))//代理商为空则默认小荷的代理商
agent_code = "8616";
//string product_code = Request.Params["product_code"];//商品编号：必选
if (string.IsNullOrWhiteSpace(product_code))
return Json(new { result = "1", message = "商品编号不能为空", errcode = "10002" }, JsonRequestBehavior.AllowGet);
//string user_name = Request.Params["user_name"];//客户真实姓名：必选
if (string.IsNullOrWhiteSpace(user_name))
return Json(new { result = "1", message = "客户真实姓名不能为空", errcode = "10003" }, JsonRequestBehavior.AllowGet);
//string user_card = Request.Params["user_card"];//客户身份证：必选
if (string.IsNullOrWhiteSpace(user_card))
return Json(new { result = "1", message = "客户身份证不能为空", errcode = "10004" }, JsonRequestBehavior.AllowGet);
//string user_contact = Request.Params["user_contact"];//联系方式：必选
if (string.IsNullOrWhiteSpace(user_contact))
return Json(new { result = "1", message = "联系方式不能为空", errcode = "10005" }, JsonRequestBehavior.AllowGet);
//string user_addr = Request.Params["user_addr"];//安装地址：必选
if (string.IsNullOrWhiteSpace(user_addr))
return Json(new { result = "1", message = "安装地址不能为空", errcode = "10006" }, JsonRequestBehavior.AllowGet);
//string order_date = Request.Params["order_date"];//签订日期：必选
if (string.IsNullOrWhiteSpace(order_date))
return Json(new { result = "1", message = "签订日期不能为空", errcode = "10007" }, JsonRequestBehavior.AllowGet);
//string order_type = Request.Params["order_type"];//套餐模式：必选（年付和月付）
if (string.IsNullOrWhiteSpace(order_type))
return Json(new { result = "1", message = "套餐模式不能为空", errcode = "10008" }, JsonRequestBehavior.AllowGet);
string orderTypeS = string.Empty;
if (order_type == "1")
{
//orderTypeS = "app支付";
order_type = "100000000";
}
if (order_type == "2")
{
//orderTypeS = "两年付";
order_type = "100000001";
}
if (order_type == "3")
{
order_type = "100000000";
//orderTypeS = "银行代扣";
}
//string card_name = Request.Params["card_name"];//持卡人姓名（中文）
//string card_type = Request.Params["card_type"];//证件类型
if (!string.IsNullOrWhiteSpace(card_type))
{
string card_typeS = string.Empty;
if (card_type == "1")
{
card_type = "100000000";
}
else
{
card_type = "100000000";
}
}
if (!string.IsNullOrWhiteSpace(bank_type))
{
string bank_typeS = string.Empty;
if (bank_type == "1")
{
bank_type = "100000000";
}
else
{
bank_type = "100000000";
}
}
card_type = "100000000";
bank_type = "100000000";
string existSql = string.Format(@"select * from new_userorder where new_name='{0}'", account_code);
//bool isExit = (helper.ExecuteSql(existSql) >= 1) ? true : false;//判断订单号是否存在
bool isExit = false;
DataSet ds = helper.GetDataSet(existSql);
if (ds.Tables[0].Rows.Count > 0)
{
isExit = true;
}
else
{
isExit = false;
}
string execuntSql = string.Empty;
if (isExit)
{
execuntSql = string.Format(@"update new_userorder set new_agentcode='{0}',new_productcode='{1}',new_username='{2}',new_usercard='{3}',new_usercontact='{4}',new_useraddr='{5}',new_orderdate='{6}',new_ordermodel='{7}',
new_cardname='{8}',new_cardtype={9},new_cardnumber='{10}',new_banktype={11},new_bankcity='{12}',new_bankname='{13}',new_bankcard='{14}',new_bankmobile='{15}',new_bankdate='{16}',new_paytype={17},new_paydate='{18}',new_paymoney='{19}',new_paynumber='{20}',new_vouchermoney='{21}',new_vouchercode='{22}' where new_name='{23}'", agent_code, product_code, user_name, user_card, user_contact, user_addr, order_date, order_type, card_name, card_type, card_number, bank_type, bank_city, bank_name, bank_card, bank_mobile, bank_date, pay_type, pay_date, pay_money, pay_number, voucher_money, vouvher_code, account_code);
}
else
{
var guid = Guid.NewGuid().ToString();
string userName = ConfigurationManager.AppSettings["userName"]; ;
string userId = string.Empty;
string ownId = string.Empty;
DataTable dtbUser = helper.GetDataSet("select systemuserid,domainname,businessunitid from systemuserbase where domainname='" + userName + "'").Tables[0];
if (dtbUser == null || dtbUser.Rows.Count == 0)
{
return Json(new { result = "1", message = "无法获取用户id和部门id", errcode = "10011" }, JsonRequestBehavior.AllowGet);
}
else
{
//foreach (DataRow dr in dtbUser.Rows)
//{
userId = dtbUser.Rows[0][0].ToString();
ownId = dtbUser.Rows[0][2].ToString();
//}
}
string bankDate = string.Empty;
string payDate = string.Empty;
if (string.IsNullOrWhiteSpace(bank_date))
{
bankDate = DateTime.Now.ToString();
}
else
{
bankDate = Convert.ToDateTime(bank_date).ToString();
}
if (string.IsNullOrWhiteSpace(pay_date))
{
payDate = DateTime.Now.ToString();
}
else
{
payDate = Convert.ToDateTime(pay_date).ToString();
}
string sql = string.Format(@"select TransactionCurrencyId from TransactionCurrency where CurrencyName='人民币'");
string transactionCurrencyId = string.Empty;
var query = helper.GetDataSet(@"select TransactionCurrencyId from TransactionCurrency where CurrencyName='人民币'");
if (query.Tables[0].Rows.Count > 0)
{
transactionCurrencyId = Convert.ToString(query.Tables[0].Rows[0]["TransactionCurrencyId"]);
}
Money voucher = new Money();
voucher.Value = Convert.ToDecimal(voucher_money);
Money pay = new Money();
pay.Value = Convert.ToDecimal(pay_money);
execuntSql = string.Format(@"insert into new_userorderBase(new_userorderId,CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,OwnerId,OwnerIdType,
OwningBusinessUnit,statecode,statuscode,new_ordernumber,new_agentcode,new_productcode,
new_username,new_usercard,new_usercontact,new_useraddr,new_orderdate,new_ordermodel,
new_cardname,new_cardtype,new_cardnumber,new_banktype,new_bankcity,new_bankname,
new_bankcard,new_bankmobile,new_bankdate,new_paytype,new_paydate,new_paymoney,
new_paynumber,new_vouchermoney,new_vouchercode,new_name,TransactionCurrencyId)
values('{0}','{1}','{2}','{3}','{4}','{5}',{6},
'{7}',{8},{9},'{10}','{11}','{12}',
'{13}','{14}','{15}','{16}','{17}',{18},
'{19}',{20},'{21}',{22},'{23}','{24}',
'{25}','{26}','{27}',{28},'{29}','{30}',
'{31}','{32}','{33}','{34}','{35}')", guid, DateTime.Now.AddHours(-8), userId, DateTime.Now.AddHours(-8), userId, userId, 8,
ownId, 0, 1, account_code, agent_code, product_code,
user_name, user_card, user_contact, user_addr, Convert.ToDateTime(order_date), order_type,
card_name, card_type, card_number, bank_type, bank_city, bank_name,
bank_card, bank_mobile, bankDate, pay_type, payDate, pay.Value,
pay_number, voucher.Value, vouvher_code, account_code, transactionCurrencyId);
}
int i = helper.ExecuteNonQuery(execuntSql);
if (i > 0)
{
return Json(new { result = "0", message = "sucess", errcode = "0" }, JsonRequestBehavior.AllowGet);
}
else
{
return Json(new { result = "1", message = "systemError", errcode = "10009" }, JsonRequestBehavior.AllowGet);
}
}
catch
{
return Json(new { result = "2", message = "systemError", errcode = "10010" }, JsonRequestBehavior.AllowGet);
}
return Json(new { result = "0", message = "sucess", errcode = "1000" }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 安装提交
/// </summary>
/// <param name="agent_code"></param>
/// <returns></returns>
[HttpPost]
public ActionResult setInstallInfo(string order_code, string install_date, string divece_code)
{
try
{
//string order_code = Request.Params["order_code"];//订单号：必填
if (string.IsNullOrWhiteSpace(order_code))
return Json(new { result = "1", message = "订单编号不能为空", errcode = "10001" }, JsonRequestBehavior.AllowGet);
//string install_date = Request.Params["install_date"];//代理商编号：必选
if (string.IsNullOrWhiteSpace(install_date))
return Json(new { result = "1", message = "安装日期不能为空", errcode = "10002" }, JsonRequestBehavior.AllowGet);
//string divece_code = Request.Params["divece_code"];//设备编号：必选
if (string.IsNullOrWhiteSpace(divece_code))
return Json(new { result = "1", message = "设备编号不能为空", errcode = "10003" }, JsonRequestBehavior.AllowGet);
string existSql = string.Format(@"select count(1) from new_userorder where new_name='{0}'", order_code);
//bool isExit = (helper.ExecuteSql(existSql) > 0) ? true : false;//判断订单号是否存在
bool isExit = false;
DataSet ds = helper.GetDataSet(existSql);
if (ds.Tables[0].Rows.Count > 0)
{
isExit = true;
}
else
{
isExit = false;
}
string execuntOrderSendSql = string.Empty;//交付申请状态
string execuntSql = string.Empty;//合同状态
string sql = string.Empty;
if (isExit)
{
//execuntOrderSendSql = string.Format(@"update new_odersend set new_installtime='{0}',new_installstatus={1} where new_contractoder in(select new_contract from new_userorder where new_name='{2}')", install_date, divece_code, order_code);
//execuntSql = string.Format(@"update new_writecontract set new_installtime='{0}',new_installstatus={1} where new_writecontractId in(select new_contract from new_userorder where new_name='{2}')", install_date, divece_code, order_code);
//sql = string.Format(@"update new_odersend set new_installtime='{0}',new_installstatus={1} where new_contractoder in(select new_contract from new_userorder where new_name='{2}');update new_writecontract set new_installtime='{3}',new_installstatus={4} where new_writecontractId in(select new_contract from new_userorder where new_name='{5}')", install_date, divece_code, order_code, install_date, divece_code, order_code);
execuntOrderSendSql = string.Format(@"update new_odersend set new_productdeviceid='{0}',new_installtime='{1}' where new_odernumber='{2}'", divece_code, Convert.ToDateTime(install_date), order_code);
}
else
{
return Json(new { result = "1", message = "订单号不存在", errcode = "10001" }, JsonRequestBehavior.AllowGet);
}
//int orderSend = helper.ExecuteNonQuery(execuntSql);//交付申请状态
//int execunt = helper.ExecuteNonQuery(execuntSql);//合同状态
int i = helper.ExecuteSql(execuntOrderSendSql);
//if (orderSend > 0 && execunt > 0)
if (i > 0)
{
return Json(new { result = "0", message = "sucess", errcode = "1000" }, JsonRequestBehavior.AllowGet);
}
else
{
return Json(new { result = "1", message = "systemError", errcode = "10001" }, JsonRequestBehavior.AllowGet);
}
}
catch
{
return Json(new { result = "2", message = "systemError", errcode = "10001" }, JsonRequestBehavior.AllowGet);
}
return Json(new { result = "0", message = "sucess", errcode = "1000" }, JsonRequestBehavior.AllowGet);
}
/// <summary>
///月支付提交信息
/// </summary>
/// <returns></returns>
[HttpPost]
public ActionResult setPayInfo(string bills_code, string pay_money, string pay_date, string pay_model, string pay_account, string pay_voucher, string voucher_code)
{
//string bills_code = Request.Params["bills_code"];//账单编号 必选
if (string.IsNullOrWhiteSpace(bills_code))
return Json(new { result = "1", message = "账单编号不能为空", errcode = "10001" }, JsonRequestBehavior.AllowGet);
//string pay_money = Request.Params["pay_money"];//支付金额 必选
if (string.IsNullOrWhiteSpace(pay_money))
return Json(new { result = "1", message = "支付金额不能为空", errcode = "10002" }, JsonRequestBehavior.AllowGet);
//string pay_date = Request.Params["pay_date"];//支付日期 必选
if (string.IsNullOrWhiteSpace(pay_date))
return Json(new { result = "1", message = "支付日期不能为空", errcode = "10003" }, JsonRequestBehavior.AllowGet);
//string pay_model = Request.Params["pay_model"];//支付方式
//string pay_account = Request.Params["pay_account"];//支付账户
//string pay_voucher = Request.Params["pay_voucher"];//优惠金额
//string voucher_code = Request.Params["voucher_code"];//优惠编码
string sql = string.Format(@"");
int execunt = helper.ExecuteNonQuery(sql);
if (execunt > 0)
{
return Json(new { result = "0", message = "sucess", errcode = "1000" }, JsonRequestBehavior.AllowGet);
}
else
{
return Json(new { result = "1", message = "error", errcode = "1001" }, JsonRequestBehavior.AllowGet);
}
return Json("", JsonRequestBehavior.AllowGet);
}
/// <summary>
/// crm访问接口
/// </summary>
/// <returns></returns>
public ActionResult CrmInterface()
{
System.ServiceModel.Description.ClientCredentials credential = new System.ServiceModel.Description.ClientCredentials();
string url = ConfigurationManager.AppSettings["url"];
Uri uri = new Uri(url);
credential.UserName.UserName = ConfigurationManager.AppSettings["userName"];
credential.UserName.Password = ConfigurationManager.AppSettings["userPwd"];
OrganizationServiceProxy proxy = new OrganizationServiceProxy(uri, null, credential, null);
ColumnSet colums = new ColumnSet("new_writecontractid");
colums.AddColumn("new_name");
colums.AddColumn("new_contractstart");
colums.AddColumn("new_billis");
colums.AddColumn("new_contractstatus");
colums.AddColumn("new_billprvtime");
FilterExpression filter = new FilterExpression(LogicalOperator.And);
filter.FilterOperator = LogicalOperator.And;
filter.AddCondition("new_contractstart", ConditionOperator.NotNull);
filter.AddCondition("new_contractstatus", ConditionOperator.Equal, 100000001);
QueryExpression queryExpress = new QueryExpression();
queryExpress.EntityName = "new_writecontract";
queryExpress.ColumnSet = colums;
queryExpress.Criteria.AddFilter(filter);
EntityCollection ec = proxy.RetrieveMultiple(queryExpress);
DateTime time = DateTime.Now;
ViewData["result"] = string.Empty;
if (ec.Entities.Count > 0 && ec.Entities != null)
{
ViewData["result"] = "共查询出" + ec.Entities.Count + "条结果！";
}
else
{
ViewData["result"] = "共查询出" + ec.Entities.Count + "条结果！";
}
if ((time.Day == 15))
{
Entity writecontract = new Entity("new_writecontract");
foreach (var item in ec.Entities)
{
DateTime date = (DateTime)item.Attributes["new_contractstart"];
DateTime prv = (DateTime)item.Attributes["new_billprvtime"];
if ((time.Year != prv.Year) || (time.Month != prv.Month) || (time.Day != prv.Day))
{
if ((date.Year != time.Year) || (date.Month != time.Month))
{
writecontract.Id = (Guid)item.Attributes["new_writecontractid"];
if ((date.Year == time.Year) && ((date.Month + 1) == time.Month))
{
DateTime d1;
if (time.Month == 1)
{
d1 = new DateTime(time.Year - 1, 12, 1);
}
else
{
d1 = new DateTime(time.Year, time.Month - 1, 1);
}
DateTime d2 = d1.AddMonths(1).AddDays(-1);
int daytotal = d2.Day;
double pice = 88.00 / daytotal;
double pay = (daytotal - date.Day + 1) * pice;
pay = pay + 88.00;
Money money = new Money();
money.Value = (decimal)pay;
writecontract["new_billmoney"] = money;
proxy.Update(writecontract);
}
writecontract["new_billis"] = true;
proxy.Update(writecontract);
}
}
}
}
return View();
}
/// <summary>
/// 未缴费超过30天产生滞纳金
/// ps：调试用3天
/// </summary>
/// <returns></returns>
public ActionResult GeneratePayment()
{
ViewData["result"] = string.Empty;
string sql = string.Format(@"select new_contract,new_billingrecordid,new_billingsum,new_billnumber,new_arrearagedate,new_chargefinish,new_arrearage,new_billstatus from new_billingrecord where new_billstatus='{0}' and new_chargefinish<'{1}'", "100000000", DateTime.Now.AddDays(-30));
var query = helper.GetDataSet(sql);
StringBuilder sb = new StringBuilder();
if (query.Tables[0].Rows.Count > 0)
{
ViewData["result"] = "共产生" + query.Tables[0].Rows.Count + "条滞纳金！";
foreach (DataRow dr in query.Tables[0].Rows)
{
//var arrearagedate = Convert.ToString(dr["new_arrearagedate"]);//滞纳天数
//var arrearage = Convert.ToString(dr["new_arrearage"]);//滞纳金
var id = Convert.ToString(dr["new_billingrecordid"]);
var contractId = Convert.ToString(dr["new_contract"]);//合同id
var chargefinish = Convert.ToString(dr["new_chargefinish"]);//账单时间
TimeSpan d = DateTime.Now - Convert.ToDateTime(chargefinish);
int day = d.Days - 30;
int arrearagedate = day;//滞纳天数
decimal arrearage = day * 1;//滞纳金额=滞纳天数*滞纳款
string updateSql = string.Format(@"update new_billingrecord set new_arrearagedate={0},new_arrearage={1} where new_billingrecordid='{2}';", day, arrearage, id);//更改账单明细表的状态为滞纳状态和滞纳金额和天数
string updateWritecontract = string.Format(@"update new_writecontract set new_billsstatus='{0}' where new_writecontractid='{1}';", "100000002", contractId);//更改合同状态为滞纳金状态
string updateOrderSend = string.Format(@"update new_odersend set new_billsstatus='{0}',new_billsdate={1} where new_odersendid in(select new_odersend from new_writecontract where new_writecontractid='{2}');", "100000002", day, contractId);//修改交售后服务的滞纳天数和账单状态
sb.Append(updateSql);
sb.Append(updateWritecontract);
sb.Append(updateOrderSend);
}
}
else
{
ViewData["result"] = "共产生" + 0 + "条滞纳金！";
}
int result = helper.ExecuteSql(sb.ToString());
if (result > 0)
{
return Json(new { result = "0", message = "成功", errcode = "1000" }, JsonRequestBehavior.AllowGet);
}
else
{
return Json(new { result = "0", message = "产生滞纳金失败", errcode = "10001" }, JsonRequestBehavior.AllowGet);
}
return View();
}
// public int BillesOutput(DataRow item, double billsMoney, double proxyMoney, double consultantMoney, DateTime billsdate, int billscount, Guid userid, Guid ownid, Guid moneyid)
// {
// string Sql;
// string str;
// Guid proxy = Guid.Empty;
// Guid rguid = Guid.Empty;
// Guid consultant = Guid.Empty;
// string inSql;
// int ret = 0;
// str = item["new_proxy"].ToString();
// proxy = (Guid)item["new_proxy"];
// if (str != "")
// {
// if (proxyMoney > 0)
// {
// try
// {
// Sql = "SELECT new_levelproxy FROM new_proxyservice WHERE new_proxyserviceid=" + "'" + item["new_proxy"] + "'";
// DataTable dt = helper.GetDataSet(Sql).Tables[0];
// if (dt != null && dt.Rows.Count != 0)
// {
// str = dt.Rows[0]["new_levelproxy"].ToString();
// if (str != "")
// {
// proxy = (Guid)dt.Rows[0]["new_levelproxy"];
// }
// rguid = Guid.NewGuid();
// inSql = string.Format(@"INSERT INTO new_proxypaymentBase(new_proxypaymentid,new_name,new_contract,new_proxy,new_user,new_product,new_productdevice,new_proxypaytype,new_paymentmoney,new_paymenttime,new_paystatus,
//CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,OwnerId,OwnerIdType,OwningBusinessUnit,statecode,statuscode,transactioncurrencyid)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},{8},'{9}',{10},
//'{11}','{12}','{13}','{14}','{15}',{16},'{17}',{18},{19},'{20}')",
// rguid, item["new_name"], item["new_writecontractid"], proxy, item["new_useraccount"], item["new_productnumber"], item["new_deviceid"], 100000000, proxyMoney, billsdate, 100000000,
// DateTime.Now.AddHours(-8), userid, DateTime.Now.AddHours(-8), userid, userid, 8, ownid, 0, 1, moneyid);
// ret = helper.ExecuteSql(inSql);
// }
// }
// catch (Exception e)
// {
// ret = -1;
// }
// }
// if (consultantMoney > 0)
// {
// try
// {
// Sql = "SELECT new_consultantid FROM new_proxyservice WHERE new_proxyserviceid=" + "'" + proxy + "'";
// DataTable dt = helper.GetDataSet(Sql).Tables[0];
// if (dt != null && dt.Rows.Count != 0)
// {
// str = dt.Rows[0]["new_consultantid"].ToString();
// if (str != "")
// {
// consultant = (Guid)dt.Rows[0]["new_consultantid"];
// rguid = Guid.NewGuid();
// inSql = string.Format(@"INSERT INTO new_profitsBase(new_profitsid,new_profitsno,new_contractid,new_proxyid,new_paymentmoney,new_paymentdate,new_paystatus,new_consultantid,
//CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,OwnerId,OwnerIdType,OwningBusinessUnit,statecode,statuscode,transactioncurrencyid)VALUES('{0}','{1}','{2}','{3}',{4},'{5}',{6},'{7}',
//'{8}','{9}','{10}','{11}','{12}',{13},'{14}',{15},{16},'{17}')",
// rguid, item["new_name"], item["new_writecontractid"], proxy, consultantMoney, billsdate, 100000000, consultant,
// DateTime.Now.AddHours(-8), userid, DateTime.Now.AddHours(-8), userid, userid, 8, ownid, 0, 1, moneyid);
// ret = helper.ExecuteSql(inSql);
// }
// }
// }
// catch (Exception e)
// {
// ret = -1;
// }
// }
// }
// if (billsMoney > 0)
// {
// try
// {
// inSql = "";
// str = "";
// string numberofbank = "null";
// str = item["new_numberofbank"].ToString();
// if (str != "")
// {
// numberofbank = "'" + item["new_numberofbank"].ToString() + "'";
// }
// rguid = Guid.NewGuid();
// inSql = string.Format(@"INSERT INTO new_billingrecordBase(new_billingrecordid,new_billnumber,new_contract,new_proxy,new_accountname,new_usercontent,new_productid,new_devicenumber,new_chargestart,new_payfrequency,new_billcount,
//new_billtype,new_billingsum,new_chargefinish,new_billstatus,new_arrearage,new_arrearagedate,new_cardname,new_numberofbank,new_bankcard,
//CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,OwnerId,OwnerIdType,OwningBusinessUnit,statecode,statuscode,transactioncurrencyid)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},{11},{12},'{13}',{14},{15},{16},'{17}',{18},'{19}',
//'{20}','{21}','{22}','{23}','{24}',{25},'{26}',{27},{28},'{29}')",
// rguid, item["new_name"], item["new_writecontractid"], item["new_proxy"], item["new_useraccount"], item["new_usercontact"], item["new_productnumber"], item["new_deviceid"], item["new_contractstart"], item["new_payfrequency"], billscount, 100000002, billsMoney, billsdate, 100000000, 0, 0, item["new_cardname"], numberofbank, item["new_cardbanknumber"],
// DateTime.Now.AddHours(-8), userid, DateTime.Now.AddHours(-8), userid, userid, 8, ownid, 0, 1, moneyid);
// ret = helper.ExecuteSql(inSql);
// }
// catch (Exception e)
// {
// ret = -1;
// }
// }
// if (ret > 0)
// {
// inSql = "";
// inSql = string.Format(@"UPDATE new_writecontract SET new_billprvtime = '{0}', new_billcount={1},new_billsstatus={2} WHERE new_writecontractid = '{3}'", billsdate, billscount, 100000000, item["new_writecontractid"]);
// ret = helper.ExecuteSql(inSql);
// }
// else
// {
// return -1;
// }
// return 0;
// }
public int BillesOutput(DataRow item, double billsMoney, double proxyMoney, double consultantMoney, DateTime billsdate, int billscount, Guid userid, Guid ownid, Guid moneyid)
{
string Sql;
string str;
Guid proxy = Guid.Empty;
Guid rguid = Guid.Empty;
Guid consultant = Guid.Empty;
string inSql;
int ret = 0;
str = item["new_proxy"].ToString();
proxy = (Guid)item["new_proxy"];
if (str != "")
{
if (proxyMoney > 0)
{
try
{
Sql = "SELECT new_levelproxy FROM new_proxyservice WHERE new_proxyserviceid=" + "'" + item["new_proxy"] + "'";
DataTable dt = helper.GetDataSet(Sql).Tables[0];
if (dt != null && dt.Rows.Count != 0)
{
str = dt.Rows[0]["new_levelproxy"].ToString();
if (str != "")
{
proxy = (Guid)dt.Rows[0]["new_levelproxy"];
}
rguid = Guid.NewGuid();
inSql = string.Format(@"INSERT INTO new_proxypaymentBase(new_proxypaymentid,new_name,new_contract,new_proxy,new_user,new_product,new_productdevice,new_proxypaytype,new_paymentmoney,new_paymenttime,new_paystatus,
CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,OwnerId,OwnerIdType,OwningBusinessUnit,statecode,statuscode,transactioncurrencyid)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7},{8},'{9}',{10},
'{11}','{12}','{13}','{14}','{15}',{16},'{17}',{18},{19},'{20}')",
rguid, item["new_name"], item["new_writecontractid"], proxy, item["new_useraccount"], item["new_productnumber"], item["new_deviceid"], 100000000, proxyMoney, billsdate, 100000000,
DateTime.Now.AddHours(-8), userid, DateTime.Now.AddHours(-8), userid, userid, 8, ownid, 0, 1, moneyid);
ret = helper.ExecuteSql(inSql);
}
}
catch (Exception e)
{
ret = -1;
}
}
if (consultantMoney > 0)
{
try
{
Sql = "SELECT new_consultantid FROM new_proxyservice WHERE new_proxyserviceid=" + "'" + proxy + "'";
DataTable dt = helper.GetDataSet(Sql).Tables[0];
if (dt != null && dt.Rows.Count != 0)
{
str = dt.Rows[0]["new_consultantid"].ToString();
if (str != "")
{
consultant = (Guid)dt.Rows[0]["new_consultantid"];
rguid = Guid.NewGuid();
inSql = string.Format(@"INSERT INTO new_profitsBase(new_profitsid,new_profitsno,new_contractid,new_proxyid,new_paymentmoney,new_paymentdate,new_paystatus,new_consultantid,
CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,OwnerId,OwnerIdType,OwningBusinessUnit,statecode,statuscode,transactioncurrencyid)VALUES('{0}','{1}','{2}','{3}',{4},'{5}',{6},'{7}',
'{8}','{9}','{10}','{11}','{12}',{13},'{14}',{15},{16},'{17}')",
rguid, item["new_name"], item["new_writecontractid"], proxy, consultantMoney, billsdate, 100000000, consultant,
DateTime.Now.AddHours(-8), userid, DateTime.Now.AddHours(-8), userid, userid, 8, ownid, 0, 1, moneyid);
ret = helper.ExecuteSql(inSql);
}
}
}
catch (Exception e)
{
ret = -1;
}
}
}
if (billsMoney > 0)
{
try
{
inSql = "";
str = "";
string numberofbank = "null";
str = item["new_numberofbank"].ToString();
if (str != "")
{
numberofbank = "'" + item["new_numberofbank"].ToString() + "'";
}
rguid = Guid.NewGuid();
inSql = string.Format(@"INSERT INTO new_billsbackBase(new_billsbackid,new_name,new_contract,new_billscount,new_billsmoney,new_billsdate,new_username,new_cardbank,new_cardnumber,
CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,OwnerId,OwnerIdType,OwningBusinessUnit,statecode,statuscode,transactioncurrencyid)VALUES('{0}','{1}','{2}',{3},{4},'{5}','{6}',{7},'{8}',
'{9}','{10}','{11}','{12}','{13}',{14},'{15}',{16},{17},'{18}')",
rguid, item["new_name"], item["new_writecontractid"], billscount, billsMoney, billsdate, item["new_cardname"], numberofbank, item["new_cardbanknumber"],
DateTime.Now.AddHours(-8), userid, DateTime.Now.AddHours(-8), userid, userid, 8, ownid, 0, 1, moneyid);
ret = helper.ExecuteSql(inSql);
rguid = Guid.NewGuid();
inSql = string.Format(@"INSERT INTO new_billingrecordBase(new_billingrecordid,new_billnumber,new_contract,new_proxy,new_accountname,new_usercontent,new_productid,new_devicenumber,new_chargestart,new_payfrequency,new_billcount,
new_billtype,new_billingsum,new_chargefinish,new_billstatus,new_arrearage,new_arrearagedate,new_cardname,new_numberofbank,new_bankcard,
CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,OwnerId,OwnerIdType,OwningBusinessUnit,statecode,statuscode,transactioncurrencyid)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',{10},{11},{12},'{13}',{14},{15},{16},'{17}',{18},'{19}',
'{20}','{21}','{22}','{23}','{24}',{25},'{26}',{27},{28},'{29}')",
rguid, item["new_name"], item["new_writecontractid"], item["new_proxy"], item["new_useraccount"], item["new_usercontact"], item["new_productnumber"], item["new_deviceid"], item["new_contractstart"], item["new_payfrequency"], billscount, 100000002, billsMoney, billsdate, 100000000, 0, 0, item["new_cardname"], numberofbank, item["new_cardbanknumber"],
DateTime.Now.AddHours(-8), userid, DateTime.Now.AddHours(-8), userid, userid, 8, ownid, 0, 1, moneyid);
ret = helper.ExecuteSql(inSql);
}
catch (Exception e)
{
ret = -1;
}
}
if (ret > 0)
{
inSql = "";
inSql = string.Format(@"UPDATE new_writecontract SET new_billprvtime = '{0}', new_billcount={1},new_billsstatus={2} WHERE new_writecontractid = '{3}'", billsdate, billscount, 100000000, item["new_writecontractid"]);
ret = helper.ExecuteSql(inSql);
}
else
{
return -1;
}
return 0;
}
/// <summary>
/// 生成账单接口
/// </summary>
/// <returns></returns>
public ActionResult CrmBillsInterface()
{
string GetSql;
DataTable ds;
Guid userid = Guid.Empty;
Guid ownid = Guid.Empty;
Guid moneyid = Guid.Empty;
int yeartotal = 0, yearsum = 0, yearcount = 0;
int monthtotal = 0, monthsum = 0, monthcont = 0;
string userName = ConfigurationManager.AppSettings["userName"]; ;
DataTable dtbUser = helper.GetDataSet("select systemuserid,domainname,businessunitid from systemuserbase where domainname='" + userName + "'").Tables[0];
if (dtbUser != null && dtbUser.Rows.Count != 0)
{
userid = (Guid)dtbUser.Rows[0][0];
ownid = (Guid)dtbUser.Rows[0][2];
}
else
{
ViewData["result"] = "用户名错误";
return View();
}
DataTable dtMoney = helper.GetDataSet("select TransactionCurrencyId from TransactionCurrency where CurrencyName='人民币'").Tables[0];
if (dtMoney != null && dtMoney.Rows.Count != 0)
{
moneyid = (Guid)dtMoney.Rows[0][0];
}
DateTime time = DateTime.Now;
///////////////////////////////////////年付记账
int contractstatus = 100000001;
int payfrequency = 100000001;
GetSql = string.Format(@"select new_writecontractid,new_name,new_billcount, new_contractstart,new_billprvtime,
new_payfrequency,new_payonce,new_payservice,new_payyear,
new_proxy,
new_useraccount,new_usercontact,new_cardbank,new_cardbankcity,new_cardbanknumber,new_cardname,new_cardnumber,new_numberofbank,
new_productnumber,new_deviceid from new_writecontract where new_payfrequency={0} and new_contractstatus={1} and new_contractstart is not null", payfrequency, contractstatus);
ds = helper.GetDataSet(GetSql).Tables[0];
foreach (DataRow item in ds.Rows)
{
DateTime start = (DateTime)item["new_contractstart"]; //记账开始时间
DateTime prv = (DateTime)item["new_billprvtime"]; //上次账单时间
yeartotal++;
if ((time.Year != start.Year) || (time.Month != start.Month))//不是本月安装
{
if ((time.Year != prv.Year) || (time.Month != prv.Month)) //本月未产生账单
{
BillsYearClass yearbills = new BillsYearClass();
yearbills.billcount = (int)item["new_billcount"];
yearbills.contractstart = (DateTime)item["new_contractstart"];
yearbills.billprvtime = (DateTime)item["new_billprvtime"];
yearbills.payonce = (decimal)item["new_payonce"];
yearbills.payservice = (int)item["new_payservice"];
yearbills.payyear = (decimal)item["new_payyear"];
yearbills.BillsCalculate();
int ret = BillesOutput(item, yearbills.billsMoney, yearbills.proxyMoney, yearbills.consultantMoney, yearbills.billsDate, yearbills.billsCount, userid, ownid, moneyid);
if (ret == 0)
{
yearsum++;
}
else
{
yearcount++;
}
}
}
}
////////////////////////////////////////////月付记账
payfrequency = 100000000;
GetSql = string.Format(@"select new_writecontractid,new_name,new_billcount, new_contractstart,new_billprvtime,
new_payfrequency,new_payfirst,new_payfirstyear,new_payfirst13,
new_proxy,
new_useraccount,new_usercontact,new_cardbank,new_cardbankcity,new_cardbanknumber,new_cardname,new_cardnumber,new_numberofbank,
new_productnumber,new_deviceid from new_writecontract where new_payfrequency={0} and new_contractstatus={1} and new_contractstart is not null", payfrequency, contractstatus);
ds = helper.GetDataSet(GetSql).Tables[0];
foreach (DataRow item in ds.Rows)
{
DateTime start = (DateTime)item["new_contractstart"]; //记账开始时间
DateTime prv = (DateTime)item["new_billprvtime"]; //上次账单时间
monthtotal++;
if ((time.Year != start.Year) || (time.Month != start.Month))//不是本月安装
{
if ((time.Year != prv.Year) || (time.Month != prv.Month)) //本月未产生账单
{
BillsMonthClass monthbills = new BillsMonthClass();
monthbills.billcount = (int)item["new_billcount"];
monthbills.contractstart = (DateTime)item["new_contractstart"];
monthbills.billprvtime = (DateTime)item["new_billprvtime"];
monthbills.payfirst = (decimal)item["new_payfirst"];
monthbills.payfirstyear = (decimal)item["new_payfirstyear"];
monthbills.payfirst13 = (decimal)item["new_payfirst13"];
monthbills.BillsCalculate();
int ret = BillesOutput(item, monthbills.billsMoney, monthbills.proxyMoney, monthbills.consultantMoney, monthbills.billsDate, monthbills.billsCount, userid, ownid, moneyid);
if (ret == 0)
{
monthsum++;
}
else
{
monthcont++;
}
}
}
}
ViewData["yresult"] = "年付总数:" + yeartotal.ToString() + "账单总数：" + yearsum.ToString() + "错误数:" + yearcount.ToString();
ViewData["mresult"] = "月付总数:" + monthtotal.ToString() + "账单总数：" + monthsum.ToString() + "错误数:" + monthcont.ToString();
return View();
}
/// <summary>
/// 账单推送
/// </summary>
/// <returns></returns>
public ActionResult BillPush()
{
int count = 0;
//系统时间为16号更改推送状态为0
if (DateTime.Now.Day == 16)
count = MethodInterface(0);
//系统时间为18号更改推送状态为1
if (DateTime.Now.Day == 18)
count = MethodInterface(1);
else
return Json("还没到系统账单推送时间！", JsonRequestBehavior.AllowGet);
ViewData["result"] = "共有" + count + "条账单推送！";
return Json("", JsonRequestBehavior.AllowGet);
}
public int MethodInterface(int stataus)
{
StringBuilder logs = new StringBuilder();
string sql = string.Format(@"select new_billingrecordid,new_cardname,new_bankcard,new_usercontent,new_billingsum,new_billcount,new_sendstatus from new_billingrecord where new_billstatus='{0}' and (new_sendstatus<1 or new_sendstatus is null)", "100000000");
var query = helper.GetDataSet(sql);
string url = ConfigurationManager.AppSettings["sendUrl"];
//string url = string.Format(@"http://123.56.92.117:8081/rest/v1/bill_send_message");//真实环境地址
//string url = string.Format(@"http://10.0.1.175:8080/rest/v1/bill_send_message");//测试环境地址
StringBuilder sb = new StringBuilder();
string updateSql = string.Empty;
if (query.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in query.Tables[0].Rows)
{
string id = dr["new_billingrecordid"].ToString();//账单id
string name = dr["new_cardname"].ToString();//持卡人联系人
string bankCode = dr["new_bankcard"].ToString();//银行卡号
string billcount = dr["new_billcount"].ToString();//账单期数
string phone = dr["new_usercontent"].ToString();//手机
string billingsum = dr["new_billingsum"].ToString();//金额
string code = string.Empty;
if (!string.IsNullOrWhiteSpace(bankCode) && bankCode.Length > 5)
code = bankCode.Remove(0, bankCode.Length - 4);
else
code = bankCode;
string postData = "phone_number=" + phone + "&user_name=" + name + "&bank_card=" + code + "&amount=" + billingsum + "&bills_count=" + billcount;
string result = SimpleUrlPost(url, postData);
JavaScriptSerializer Jss = new JavaScriptSerializer();
Dictionary<string, object> respDic = (Dictionary<string, object>)Jss.DeserializeObject(result);
//通过获取值
string resultValue = respDic["result"].ToString();
string message = respDic["message"].ToString();
logs.Append(phone + "-");
if (resultValue == "0")
{
updateSql = string.Format(@"update new_billingrecord set new_sendstatus={0} where new_billingrecordid='{1}';", stataus, id);
sb.Append(updateSql);
}
else
{
continue;
}
}
}
int resultCount=0;
if(!string.IsNullOrWhiteSpace(sb.ToString()))
resultCount = helper.ExecuteSql(sb.ToString());
WriteLog(logs.ToString());
return resultCount;
}
public string SimpleUrlPost(string sUrl, string sPostData)
{
Stream stream = null;
StreamReader reader = null;
//对参数中的中文进行转码
string sTemp = "";
string[] arrEncode;
int iPosition = 0;
ASCIIEncoding encoding = new ASCIIEncoding();
//byte[] data = encoding.GetBytes(sPostData);
byte[] data = Encoding.UTF8.GetBytes(sPostData);
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(sUrl);
request.ServicePoint.ConnectionLimit = 1000;
request.Method = "POST"; //数据提交方式
request.ContentType = "application/x-www-form-urlencoded;charset=utf-8";
request.ContentLength = data.Length;
request.UserAgent = "Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.9.0.1) Gecko/2008070208 Firefox/3.0.1";
request.Referer = "http://zq.9wee.com/";
request.Accept = "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8";
request.Headers.Add("Accept-Language", "zh-cn,zh;q=0.5");
request.AllowAutoRedirect = false;
try
{
Stream newStream = request.GetRequestStream();
newStream.Write(data, 0, data.Length);
newStream.Close();
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
//m_cc.Add(response.Cookies);
stream = response.GetResponseStream();
string buffer = "", line;
reader = new StreamReader(stream, Encoding.GetEncoding("utf-8"));
while ((line = reader.ReadLine()) != null)
{
buffer += line;
}
response.Close();
//httpResult.IsSuccess = true;
//httpResult.ResultString = buffer;
return buffer;
}
catch (Exception e)
{
//httpResult.IsSuccess = false;
//httpResult.ResultString = e.Message;
//return httpResult;
return "";
}
finally
{
if (reader != null)
{
reader.Close();
}
if (stream != null)
{
stream.Close();
}
}
}
private void WriteLog(string text)
{
string path = AppDomain.CurrentDomain.BaseDirectory;
path = System.IO.Path.Combine(path
, "Logs\\");
if (!System.IO.Directory.Exists(path))
{
System.IO.Directory.CreateDirectory(path);
}
string fileFullName = System.IO.Path.Combine(path
, string.Format("{0}.txt", DateTime.Now.ToString("yyyyMMdd-HHmm")));
using (StreamWriter output = System.IO.File.AppendText(fileFullName))
{
output.WriteLine(text);
output.Close();
}
}
}
}
