public static string JxHandWriting(string fphm, string fpdm)
{
CookieContainer c = new CookieContainer();
Cookie cookie = new Cookie("JSESSIONIDFPCXQD121", "VPPRlRhifIxXE9pYTvA4pLCbOyjz1GEcc_IPowmuQx2VYB50_PWl2N-BiHBT2vQKDIw9evpl41IUvJLSdE3sM7XLFLqu9Eh9m0XTKA**");
cookie.Domain = "http://localhost";
c.Add(cookie);
cookie = new Cookie("AntiLeech", "2670161455");
cookie.Domain = "http://localhost";
c.Add(cookie);
var wc = new HttpClient(c);
string randomT = "0." + ValideCodeHelper.CreateNumberVerifyCode(16);
string vc = JxHandWritingValideCodeOrc(randomT, wc);
randomT = "0." + ValideCodeHelper.CreateNumberVerifyCode(16);
string url = "http://117.40.128.134:7002/fpcx/CxfpxxServlet?t=" + randomT;
NameValueCollection para = new NameValueCollection();
para.Add("fplx", "39");
para.Add("fpdm", fpdm);
para.Add("fphm", fphm);
para.Add("kprq", DateTime.Now.AddMonths(-1).ToString("yyyyMMdd"));
para.Add("kpmm", "");
para.Add("kjje", "100");
para.Add("yzm", vc);
string res = HttpHelper.Post(url, para, wc);
return(res);
}
public string GetSignature()
{
string noncestr = ValideCodeHelper.GetRandomCode(16);
TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
string timestamp = Convert.ToInt64(ts.TotalSeconds).ToString();
string jsapi_ticket = GetWxTicket();
if (jsapi_ticket.Contains("errcode"))
{
return(jsapi_ticket);
}
else
{
string string1 = "jsapi_ticket=" + jsapi_ticket + "&noncestr=" + noncestr + "×tamp=" + timestamp + "&url=" + RedirectUri;
SHA1 sh = new SHA1CryptoServiceProvider();
byte[] StrRes = Encoding.Default.GetBytes(string1);
HashAlgorithm iSHA = new SHA1CryptoServiceProvider();
StrRes = iSHA.ComputeHash(StrRes);
StringBuilder signature = new StringBuilder();
foreach (byte iByte in StrRes)
{
signature.AppendFormat("{0:x2}", iByte);
}
JObject jb = new JObject();
jb.Add("signature", signature.ToString());
jb.Add("noncestr", noncestr);
jb.Add("timestamp", timestamp);
jb.Add("appid", Corpid);
return(jb.ToString());
}
}
private void GetValideCode()
{
string vc = ValideCodeHelper.staticCreateVerifyCode(4);
//ImageButton1.ImageUrl = "~/ValideCode.aspx?code=" + vCode.Value;
Response.Write(vc);
}
protected void Page_Load(object sender, EventArgs e)
{
ValideCodeHelper vc = new ValideCodeHelper();
string code = Request.Params["code"];
vc.CreateImageOnPage(code, this.Context); // 输出图片
}
private static string JlQuotaInvoiceValideCodeOrc(WebClient wc)
{
string randomT = "0." + ValideCodeHelper.CreateNumberVerifyCode(17);
//string url = "https://etax.jilin.chinatax.gov.cn:10812/download.sword?ctrl=CxzxFplxcxCtrl_getCheckcode&0.19244537476612233";
string url = "https://etax.jilin.chinatax.gov.cn:10812/download.sword?ctrl=CxzxFplxcxCtrl_getCheckcode&" + randomT;
return(BaseInvoiceValideCodeOrc(wc, url));
}
// public string GetState(HttpContext context)
// {
// string randomString = ValideCodeHelper.GetRandomCode(16);
// context.Session["randomString"] = randomString;
// return randomString;
// }
public void GotoGetCode(HttpContext context)
{
string randomString = ValideCodeHelper.GetRandomCode(16);
context.Session["randomString"] = randomString;
string urlForGettingCode = string.Format("https://open.work.weixin.qq.com/wwopen/sso/qrConnect?appid={0}&agentid={1}&redirect_uri={2}&state={3}"
, Corpid, AgentId, HttpUtility.UrlEncode(RedirectUri), randomString);
context.Response.Redirect(urlForGettingCode, false);
}
private string HandWrite()
{
string token = "24.ca06f6e6acd341a33397a2aa5c91134f.2592000.1566457327.282335-10644534";
string url = string.Format("https://aip.baidubce.com/rest/2.0/ocr/v1/webimage?access_token=" + token);
string randomT = "0." + ValideCodeHelper.CreateNumberVerifyCode(16);
NameValueCollection para = new NameValueCollection();
//para.Add("url", "http://117.40.128.134:7002/fpcx//Validate?t=" + randomT);
para.Add("image", Request.Form["imageBase"]);
para.Add("language_type", "ENG");
string res = HttpHelper.Post(url, para);
return(res);
}
private void GotoGetCode(HttpContext context)
{
string randomString = ValideCodeHelper.GetRandomCode(16);
context.Session["randomString"] = randomString;
//
//string urlForGettingCode = string.Format(@"https://open.work.weixin.qq.com/wwopen/sso/qrConnect?appid={0}" +
// "&agentid={1}&redirect_uri={2}&state={3}",
// Corpid, AgentId, HttpUtility.UrlEncode(RedirectUri), randomString);
string urlForGettingCode = string.Format(@"https://open.weixin.qq.com/connect/oauth2/authorize?appid={0}"
+ "&redirect_uri={1}&response_type=code&scope=snsapi_base&agentid={2}&state={3}#wechat_redirect"
, Corpid, HttpUtility.UrlEncode(RedirectUri), AgentId, randomString);
context.Response.Redirect(urlForGettingCode, false);
}
public static string Login(ref UserInfo user, ref string token)
{
string ret = "";
string msg = "";
string sql = string.Format("select userName from users where userName='******' or mobilePhone='{1}'\r\n;", user.userName, user.mobilePhone);
sql += string.Format("select * from users where (userName='******' and passWord='******') or (mobilePhone='{2}' and passWord='******')",
user.userName, user.passWord, user.mobilePhone, user.passWord);
DataSet ds = SqlHelper.Find(sql, ref msg);
if (ds == null)
{
ret = msg;
}
else if (ds.Tables[0].Rows.Count == 0)
{
ret = "用户不存在！";
}
else if (ds.Tables[1].Rows.Count == 0)
{
ret = "用户名（手机号）或密码错误！";
}
else if (ds.Tables[1].Rows.Count > 0)
{
ret = "登录成功";
user = ModelHandler <UserInfo> .FillModel(ds.Tables[1].Rows[0]);
if (user.isValid != "在职")
{
ret = "用户无权限登录！";
}
//添加token
sql = "delete from login_info where UserId = " + user.userId + "\r\n;";
JObject obj = new JObject();
obj.Add("UserId", user.userId);
token = ValideCodeHelper.GetRandomCode(128);
obj.Add("Token", token);
obj.Add("LoginTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
string ip = getIp();
obj.Add("IpAddress", ip);
sql += SqlHelper.GetInsertString(obj, "login_info");
SqlHelper.Exce(sql);
}
return(ret);
}
private string JxHandWriting(string fphm, string fpdm)
{
//wc.BaseAddress = "http://117.40.128.134:7002";
//wc.Headers.Add(HttpRequestHeader.Cookie, "JSESSIONIDFPCXQD121=QPPRlRhifIxXE9pYTvA4pLCbOyjz1GEcc_IPowmuQx2VYB50_PWl2N-BiHBT2vQKDIw9evpl41IUvJLSdE3sM7XLFLqu9Eh9m0XTKA**");
//wc.Headers.Add(HttpRequestHeader.Cookie, "AntiLeech=2670161455");
//wc.Headers.Add(HttpRequestHeader.Referer, "http://117.40.128.134:7002/fpcx/");
CookieContainer c = new CookieContainer();
Cookie cookie = new Cookie("JSESSIONIDFPCXQD121", "VPPRlRhifIxXE9pYTvA4pLCbOyjz1GEcc_IPowmuQx2VYB50_PWl2N-BiHBT2vQKDIw9evpl41IUvJLSdE3sM7XLFLqu9Eh9m0XTKA**");
cookie.Domain = "http://localhost";
c.Add(cookie);
cookie = new Cookie("AntiLeech", "2670161455");
cookie.Domain = "http://localhost";
c.Add(cookie);
var wc = new HttpClient(c);
string randomT = "0." + ValideCodeHelper.CreateNumberVerifyCode(16);
string vc = JxHandWritingValideCodeOrc(randomT, wc);
//var aaa = HttpContext.Current.Session[""];
randomT = "0." + ValideCodeHelper.CreateNumberVerifyCode(16);
string url = "http://117.40.128.134:7002/fpcx/CxfpxxServlet?t=" + randomT;
NameValueCollection para = new NameValueCollection();
para.Add("fplx", "39");
para.Add("fpdm", "136011915331");
para.Add("fphm", "02031615");
para.Add("kprq", DateTime.Now.AddMonths(-1).ToString("yyyyMMdd"));
para.Add("kpmm", "");
para.Add("kjje", "100");
para.Add("yzm", vc);
//foreach(string key in para.Keys)
//{
// url += string.Format("&{0}={1}", key, para[key]);
//}
////paraString = paraString.Substring(0, paraString.Length - 1);
//string res = NetRecognizePic.PostWebRequest(url, "", Encoding.UTF8);
string res = HttpHelper.Post(url, para, wc);
return(res);
}
/// <summary>
/// 添加附件
/// </summary>
/// <param name="emailId">邮件ID</param>
/// <param name="fileName">文件名称</param>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
public static string InsertAttachment(string emailId, string fileName, string filePath)
{
string fileCode = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ") + ValideCodeHelper.GetRandomCode(128);
JObject res = new JObject();
if (string.IsNullOrEmpty(emailId) || string.IsNullOrEmpty(fileName) || string.IsNullOrEmpty(filePath))
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "参数缺少");
return(res.ToString());
}
List <string> listSql = new List <string>();
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("EmailId", emailId);
dict.Add("FileName", fileName);
dict.Add("FilePath", filePath);
dict.Add("FileCode", fileCode);
dict.Add("CreateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
dict["FilePath"] = dict["FilePath"].Replace("\\", "\\\\");
listSql.Add(SqlHelper.GetInsertString(dict, "yl_email_attachment"));
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add("Attachment", "1");
listSql.Add(SqlHelper.GetUpdateString(dict, "yl_email", "where emailId=" + emailId));
SqlExceRes r = new SqlExceRes(SqlHelper.Exce(listSql.ToArray()));
if (r.Result == SqlExceRes.ResState.Success)
{
res.Add("ErrCode", 0);
res.Add("ErrMsg", "操作成功");
res.Add("FileCode", fileCode);
}
else
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", r.ExceMsg);
}
return(res.ToString());
}
private void DownloadExcel()
{
UserInfo user = (UserInfo)Session["user"];
JObject res = new JObject();
string date = Request.Form["date"];
string company = Request.Form["company"];
DateTime dateStart = Convert.ToDateTime(date + "-4");
DateTime dateEnd = dateStart.AddMonths(1);
dateEnd = dateEnd.AddDays(0);
//dateEnd = dateEnd.AddDays(-6);
//string[] dates = date.Split('-');
//int year = Convert.ToInt32(dates[0]);
//int month = Convert.ToInt32(dates[1]);
string msg = "";
string sql = string.Format("select * from v_user_department_post where userId={0}\r\n;", user.userId);
sql += string.Format("SELECT r.*, rd.*,group_concat(r.project) concatProject FROM (select * from yl_reimburse where status='已审批'and name='{2}' " +
" and fee_company='{3}') AS r inner JOIN yl_reimburse_detail AS rd ON rd.Code LIKE concat('%', r.code, '%')" +
" where (rd.CreateTime between '{0} 00:00:00' and '{1} 23:59:59') and (rd.Status='同意' and r.status='已审批') and r.name='{2}' group by rd.id,rd.code ORDER BY r.code ASC"
, dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd"), user.userName, company);
//sql += string.Format("SELECT r.*, rd.* FROM yl_reimburse AS r RIGHT JOIN yl_reimburse_detail AS rd ON rd.Code LIKE concat('%', r.code, '%')" +
// " where (rd.CreateTime between '{0}' and '{1}') and (rd.Status='同意' and r.status='已审批' and r.account_result='同意') and r.name='{2}' ORDER BY r.code ASC"
// , dateStart.ToString("yyyy-MM-dd"), dateEnd.ToString("yyyy-MM-dd"), user.userName);
DataSet ds = SqlHelper.Find(sql, ref msg);
if (ds == null)
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", msg);
Response.Write(res.ToString());
return;
}
string fileName = "";
byte[] data = null;
bool IsSalesMan = false;
foreach (DataRow row in ds.Tables[0].Rows)
{
if (row["department"].ToString().Contains("营销中心"))
{
IsSalesMan = true;
break;
}
}
//IsSalesMan = true;//测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试测试
Dictionary <string, object> UserInfo = new Dictionary <string, object>();
UserInfo.Add("user", user);
UserInfo.Add("depart", ds.Tables[0].Rows[0]["department"].ToString().Replace("东森家园/", ""));
UserInfo.Add("dateStart", dateStart);
UserInfo.Add("dateEnd", dateEnd);
DataTable dtVal = null;
if (IsSalesMan)
{
fileName = string.Format("营销中心费用报销新套表-{0}{1}", user.userName, dateStart.ToString("yyyyMM"));
string path = Server.MapPath("~/Template/营销中心费用报销新套表.xls");
dtVal = CreateSalesmanReimbursementSlipCoverDetailValue(ds.Tables[1], ref UserInfo);
if (dtVal.Rows.Count > 15)
{
data = ExcelHelperV2_0.EditSalesmanExcel(GetRowsFromDataTable(dtVal, 0, 14), ref msg, path, UserInfo);
string filecode = ValideCodeHelper.GetRandomCode(64);
string newPath = Server.MapPath("~/tempExportFile");
newPath = newPath + @"\" + filecode + ".xls";
BytesToFile(newPath, data);
res.Add("ErrCode", 0);
res.Add("FileCount", 2);
res.Add("FileCode1", filecode);
res.Add("FileName1", fileName);
UserInfo.Remove("餐费");
UserInfo.Remove("市内交通费");
UserInfo.Remove("核销预付款");
UserInfo.Remove("税金");
data = ExcelHelperV2_0.EditSalesmanExcel(GetRowsFromDataTable(dtVal, 15, dtVal.Rows.Count - 1), ref msg, path, UserInfo);
filecode = ValideCodeHelper.GetRandomCode(64);
newPath = Server.MapPath("~/tempExportFile");
newPath = newPath + @"\" + filecode + ".xls";
BytesToFile(newPath, data);
res.Add("FileCode2", filecode);
res.Add("FileName2", fileName);
}
else
{
data = ExcelHelperV2_0.EditSalesmanExcel(dtVal, ref msg, path, UserInfo);
string filecode = ValideCodeHelper.GetRandomCode(64);
string newPath = Server.MapPath("~/tempExportFile");
newPath = newPath + @"\" + filecode + ".xls";
BytesToFile(newPath, data);
res.Add("ErrCode", 0);
res.Add("FileCount", 1);
res.Add("FileCode1", filecode);
res.Add("FileName1", fileName);
}
}
else
{
fileName = string.Format("职能线费用报销套表-{0}{1}", user.userName, dateStart.ToString("yyyyMM"));
string path = Server.MapPath("~/Template/职能线费用报销套表.xls");
//data = ExcelHelperV2_0.EditNoSalesmanExcel(ds.Tables[1], ref msg, path, UserInfo);
dtVal = CreateNoSalesmanReimbursementSlipCoverDetailValue(ds.Tables[1], ref UserInfo);
if (dtVal.Rows.Count > 15)
{
data = ExcelHelperV2_0.EditNoSalesmanExcel(GetRowsFromDataTable(dtVal, 0, 14), ref msg, path, UserInfo);
string filecode = ValideCodeHelper.GetRandomCode(64);
string newPath = Server.MapPath("~/tempExportFile");
newPath = newPath + @"\" + filecode + ".xls";
BytesToFile(newPath, data);
res.Add("ErrCode", 0);
res.Add("FileCount", 2);
res.Add("FileCode1", filecode);
res.Add("FileName1", fileName);
UserInfo.Remove("交通费");
UserInfo.Remove("核销预付款");
UserInfo.Remove("税金");
data = ExcelHelperV2_0.EditNoSalesmanExcel(GetRowsFromDataTable(dtVal, 15, dtVal.Rows.Count - 1), ref msg, path, UserInfo);
filecode = ValideCodeHelper.GetRandomCode(64);
newPath = Server.MapPath("~/tempExportFile");
newPath = newPath + @"\" + filecode + ".xls";
BytesToFile(newPath, data);
res.Add("FileCode2", filecode);
res.Add("FileName2", fileName);
}
else
{
data = ExcelHelperV2_0.EditNoSalesmanExcel(dtVal, ref msg, path, UserInfo);
string filecode = ValideCodeHelper.GetRandomCode(64);
string newPath = Server.MapPath("~/tempExportFile");
newPath = newPath + @"\" + filecode + ".xls";
BytesToFile(newPath, data);
res.Add("ErrCode", 0);
res.Add("FileCount", 1);
res.Add("FileCode1", filecode);
res.Add("FileName1", fileName);
}
}
if (string.IsNullOrEmpty(msg))
{
//Response.ContentType = "application/vnd.ms-excel";
//Response.ContentEncoding = Encoding.UTF8;
//Response.Charset = "";
//Response.AppendHeader("Content-Disposition", "attachment;filename="
// + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
//Response.BinaryWrite(data);
//string filecode = ValideCodeHelper.GetRandomCode(64);
//string path = Server.MapPath("~/tempExportFile");
//path = path + @"\" + filecode + ".xls";
//using (FileStream fs = new FileStream(path, FileMode.Create))
//{
// using (BinaryWriter bw = new BinaryWriter(fs))
// {
// bw.Write(data);
// }
//}
//res.Add("ErrCode", 0);
//res.Add("FileCode", filecode);
//res.Add("FileName", fileName);
}
else
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", msg);
}
Response.Write(res.ToString());
}
private string downLoadBudget()
{
string departmentId = "1";
int lastMonth, lastYear, nextMonth, nextYear;
string dateString = Request.Form["date"] + "-1";
JObject res = new JObject();
DateTime now = new DateTime();
if (!DateTime.TryParse(dateString, out now))
{
res.Add("ErrCode", 3);
res.Add("ErrMsg", "输入查询月份有误！请重新输入！");
return(res.ToString());
}
if (now.Month == 1)
{
lastMonth = 12;
lastYear = now.Year - 1;
nextMonth = now.Month + 1;
nextYear = now.Year;
}
else if (now.Month == 12)
{
lastMonth = now.Month - 1;
lastYear = now.Year;
nextMonth = 1;
nextYear = now.Year + 1;
}
else
{
lastMonth = now.Month - 1;
lastYear = now.Year;
nextMonth = now.Month + 1;
nextYear = now.Year;
}
//本月预算
string sql = string.Format("SELECT a.*,d.name as department FROM `import_budget` a LEFT JOIN department d on a.DepartmentId=d.Id " +
"where a.CreateTime between '{1}-{2}-1 00:00:00 ' and '{3}-{4}-1 00:00:00' order by a.Id;", departmentId, now.Year, now.Month, nextYear, nextMonth);
//string sql = string.Format("SELECT a.*, ifnull(sum(yr.fee_amount), 0) UsedAmount FROM `import_budget` a LEFT JOIN department d on a.DepartmentId=d.Id " +
// "left join yl_reimburse yr on yr.fee_department like CONCAT('%',d.`name`,'%') and a.FeeDetail = yr.fee_detail and yr.approval_time BETWEEN '{1}-{2}-26 00:00:00 '" +
// "AND '{3}-{4}-31 23:59:59' and yr.status = '已审批' where d.Id={0} and " +
// "a.CreateTime between '{1}-{2}-1 00:00:00 ' and '{3}-{4}-1 00:00:00' group by a.FeeDetail order by a.Id;", departmentId, lastYear, lastMonth, now.Year, now.Month);
//上月预算
sql += string.Format("SELECT a.*,0 as UsedAmount FROM `import_budget` a LEFT JOIN department d on a.DepartmentId=d.Id " +
"where d.Id={0} and a.CreateTime between '{1}-{2}-1 00:00:00 ' and '{3}-{4}-1 00:00:00' order by a.Id;", departmentId, lastYear, lastMonth, now.Year, now.Month);
sql += "select max(Id) from import_budget;";
sql += string.Format("select yr.fee_amount,yr.fee_detail,yr.fee_department from yl_reimburse yr " +
" where yr.approval_time BETWEEN '{1}-{2}-1 00:00:00 ' and '{3}-{4}-1 00:00:00' and yr.status = '已审批' and (yr.account_result is null or yr.account_result = '同意')"
, departmentId, now.Year, now.Month, nextYear, nextMonth);
string msg = "";
DataSet ds = SqlHelper.Find(sql, ref msg);
if (ds != null)
{
int count = Convert.ToInt32(ds.Tables[2].Rows[0][0]) + 1;
DataTable dt = new DataTable();
if (ds.Tables[0].Rows.Count == 0)//本月未提交预算，费用明细从上月取，预算全部赋值0
{
res.Add("ErrCode", 2);
res.Add("ErrMsg", "该月未查询到预算！");
}
else//本月已提交预算，预算和费用明细从本月取数据
{
dt = ds.Tables[0];
dt.Columns.Add("UsedAmount");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
string ParentName = "";
string condition = "";
if (Convert.ToInt32(row["ParentId"]) == -1)
{
condition = string.Format("fee_detail like '%{0}%' and fee_department like '%{1}%'", row["FeeDetail"].ToString(), row["department"].ToString());
}
else
{
DataRow[] rowss = dt.Select("Id=" + row["ParentId"].ToString());
if (rowss.Length > 0)
{
ParentName = rowss[0]["FeeDetail"].ToString();
condition = string.Format("fee_detail like '%{0}%' and fee_detail like '%{2}%' and fee_department like '%{1}%'"
, row["FeeDetail"].ToString(), row["department"].ToString(), ParentName);
}
}
double UsedAmount = 0;
DataRow[] rows = ds.Tables[3].Select(condition);
foreach (DataRow r in rows)
{
UsedAmount += Convert.ToDouble(r["fee_amount"]);
}
//for (int j = ds.Tables[3].Rows.Count - 1; j >= 0; j--)
//{
// DataRow r = ds.Tables[3].Rows[j];
// string ImFeeDetail = r["fee_detail"].ToString();
// string YrFeeDetail = row["FeeDetail"].ToString();
// if (string.IsNullOrEmpty(ParentName))
// {
// if (ImFeeDetail.Contains(YrFeeDetail)&&row["department"].ToString().Equals(r["fee_department"].ToString()))
// {
// try
// {
// UsedAmount += Convert.ToDouble(r["fee_amount"]);
// }
// catch { }
// finally
// {
// //ds.Tables[3].Rows.RemoveAt(j);
// }
// }
// }
// else
// {
// if ((ImFeeDetail.Contains(YrFeeDetail)) && ImFeeDetail.Contains(ParentName) && row["department"].ToString().Equals(r["fee_department"].ToString()))
// {
// try
// {
// UsedAmount += Convert.ToDouble(r["fee_amount"]);
// }
// catch { }
// finally
// {
// //ds.Tables[3].Rows.RemoveAt(j);
// }
// }
// }
//}
dt.Rows[i]["UsedAmount"] = UsedAmount;
}
string path = Server.MapPath("~/tempExportFile");
string filecode = ValideCodeHelper.GetRandomCode(64);
string fileName = string.Format("{0}年{1}月预算使用情况表", now.Year, now.Month);
path = path + @"\" + filecode + ".xls";
BytesToFile(ExcelHelperV2_0.Export(dt, fileName, (new List <string>()).ToArray()).GetBuffer(), path);
res.Add("ErrCode", 0);
res.Add("fileName", fileName);
res.Add("fileCode", filecode);
}
//List<BudgetTreeNode> tree = new BudgetTreeNodeHelper(dt).GetTree();
//string json = JsonConvert.SerializeObject(tree);
//if(dt.Rows.Count == 0)
//{
// res.Add("ErrCode", 2);
// res.Add("ErrMsg", "该月未查询到预算！");
//}
//else
//{
// string path = Server.MapPath("~/tempExportFile");
// string filecode = ValideCodeHelper.GetRandomCode(64);
// string fileName = string.Format("{0}年{1}月预算使用情况表", now.Year, now.Month);
// path = path + @"\" + filecode + ".xls";
// BytesToFile(ExcelHelperV2_0.Export(dt, fileName, (new List<string>()).ToArray()).GetBuffer(), path);
// res.Add("ErrCode", 0);
// res.Add("fileName", fileName);
// res.Add("fileCode", filecode);
//}
}
else
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", msg);
}
//Response.Write(res);
return(res.ToString());
}
protected void Page_Load(object sender, EventArgs e)
{
UserInfo user = (UserInfo)Session["user"];
JObject res = new JObject();
Response.Clear();
if (user == null)
{
res.Add("success", 0);
res.Add("msg", "用户登录超时或无用户信息！");
Response.Write(res.ToString());
Response.End();
}
else
{
string fileCode = Request.Params["fileCode"];
string path = Server.MapPath("~/tempExportFile");
//string path = "/tempExportFile";
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
if (string.IsNullOrEmpty(fileCode))
{
string data = Request.Form["data"];
string title = Request.Form["title"];
string headText = Request.Form["headText"];
string colNames = Request.Form["columnName"];
List <string> listHeadText = JsonHelper.DeserializeJsonToList <string>(headText);
List <string> listcolNames = JsonHelper.DeserializeJsonToList <string>(colNames);
DataTable dt = JsonHelper.Json2Dtb(data);
DataTable newDt = new DataTable();
foreach (string name in listcolNames)
{
newDt.Columns.Add(name);
}
foreach (DataRow row in dt.Rows)
{
DataRow newRow = newDt.NewRow();
for (int j = 0; j < listcolNames.Count; j++)
{
if (listcolNames[j] != "operate" && listcolNames[j] != "checkbox")
{
newRow[listcolNames[j]] = row[listcolNames[j]];
}
}
newDt.Rows.Add(newRow);
}
//for (int i = dt.Columns.Count - 1; i >= 0; i--)
//{
// DataColumn c = dt.Columns[i];
// bool isContain = false;
// for (int j = 0; j < listcolNames.Count; j++)
// {
// if (c.ColumnName == listcolNames[j])
// {
// isContain = true;
// break;
// }
// }
// if (!isContain)
// dt.Columns.RemoveAt(i);
//}
string filecode = ValideCodeHelper.GetRandomCode(64);
path = path + @"\" + filecode + ".xls";
BytesToFile(ExcelHelperV2_0.Export(newDt, title, listHeadText.ToArray()).GetBuffer(), path);
res.Add("success", 1);
res.Add("fileCode", filecode);
Response.Write(res.ToString());
Response.End();
}
else
{
string fileName = Request.Params["fileName"];
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = Encoding.UTF8;
Response.Charset = "";
Response.AppendHeader("Content-Disposition", "attachment;filename="
+ HttpUtility.UrlEncode(fileName, Encoding.UTF8));
path = path + @"\" + fileCode + ".xls";
Response.BinaryWrite(FileToBytes(path));
File.Delete(path);
Response.End();
}
}
}
public static string JlQuotaInvoice(string fpdm, string fphm)
{
CookieContainer c = new CookieContainer();
Cookie cookie = new Cookie("UM_distinctid", "16ccba01cd517-054bfb8084beb44-5d5e490e-1fa400-16ccba01cd62d6");
cookie.Domain = "http://localhost";
c.Add(cookie);
cookie = new Cookie("yfx_c_g_u_id_10003718", "_ck19082609524318055132734503131");
cookie.Domain = "http://localhost";
c.Add(cookie);
cookie = new Cookie("yfx_f_l_v_t_10003718", "f_t_1566784363802__r_t_1566784363802__v_t_1566784363802__r_c_0");
cookie.Domain = "http://localhost";
c.Add(cookie);
cookie = new Cookie("yfx_c_g_u_id_10003139", "ck19082614483910703332143517515");
cookie.Domain = "http://localhost";
c.Add(cookie);
cookie = new Cookie("yfx_f_l_v_t_10003139", "f_t_1566802119002__r_t_1566802119002__v_t_1566802119002__r_c_0");
cookie.Domain = "http://localhost";
c.Add(cookie);
cookie = new Cookie("_trs_uv", "jzs1j0g7_735_4xor");
cookie.Domain = "http://localhost";
c.Add(cookie);
cookie = new Cookie("DS_SESSION", "eC3M-ysj5RqGv-YQf2EqHSFkUbxGtW9-8DUIGHNcDUO95ZolxX0e!-675258273");
cookie.Domain = "http://localhost";
c.Add(cookie);
var wc = new HttpClient(c);
//var wc = new HttpClient();
string vc = JlQuotaInvoiceValideCodeOrc(wc);
//string url = "https://etax.jilin.chinatax.gov.cn:10812/ajax.sword?r=0.8563231255827108&rUUID=rgqtWhpHayARq6NXTzc5DbaPbOoiki2r";
string randomT = "0." + ValideCodeHelper.CreateNumberVerifyCode(16);
string uid = ValideCodeHelper.GetRandomCode(32);
string url = string.Format("https://etax.jilin.chinatax.gov.cn:10812/ajax.sword?r={0}&rUUID={1}", randomT, uid);
JObject param = new JObject();
param.Add("tid", "");
param.Add("ctrl", string.Format("CxzxFplxcxCtrl_initQueryFpxxList?rUUID={0}", uid));
param.Add("page", "");
JArray data = new JArray();
JObject dm = new JObject();
dm.Add("name", "fpDm");
dm.Add("value", fpdm);
dm.Add("sword", "attr");
data.Add(dm);
JObject hm = new JObject();
hm.Add("name", "fphm");
hm.Add("value", fphm);
hm.Add("sword", "attr");
data.Add(hm);
JObject cxfs = new JObject();
cxfs.Add("name", "cxfs");
cxfs.Add("value", "1");
cxfs.Add("sword", "attr");
data.Add(cxfs);
JObject SwordForm = new JObject();
SwordForm.Add("sword", "SwordForm");
SwordForm.Add("name", "fplgxxcxForm");
JObject SwordFormData = new JObject();
JObject dmVal = new JObject();
dmVal.Add("value", fpdm);
SwordFormData.Add("fpDm", dmVal);
JObject hmVal = new JObject();
hmVal.Add("value", fphm);
SwordFormData.Add("fphm", hmVal);
JObject cxfsVal = new JObject();
cxfsVal.Add("value", "1");
SwordFormData.Add("cxfs", cxfsVal);
JObject yzmVal = new JObject();
yzmVal.Add("value", "1");
SwordFormData.Add("yzm", vc);
SwordForm.Add("data", SwordFormData);
data.Add(SwordForm);
param.Add("data", data);
param.Add("bindParam", true);
NameValueCollection para = new NameValueCollection();
para.Add("postData", param.ToString());
string res = HttpHelper.Post(url, para, wc);
return(res);
}
private void DownloadExcel()
{
JObject res = new JObject();
string docId = Request.Form["docId"];
string tableName = Request.Form["formName"];
DataTable dt = SqlHelper.Find(string.Format("select * from wf_form_{0} where id = '{1}'", tableName, docId)).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "");
Response.Write(res.ToString());
return;
}
// 单据内容
DataRow dr = dt.Rows[0];
// 单据审批人详情
//DataTable approverDt = SqlHelper.Find(string.Format("select t1.userId, t2.userName from wf_approver t1 left join users t2 on t1.userId = t2.userId " +
// "where t1.tableName = '{0}' and t1.docId = '{1}'", tableName, docId)).Tables[0];
// 单据审批记录详情
DataTable recordDt = SqlHelper.Find(string.Format("select t1.userId, t2.userName,t1.approvalTime from wf_record t1 left join users t2 on t1.userId = t2.userId " +
"where t1.tableName = '{0}' and t1.docId = '{1}'", tableName, docId)).Tables[0];
string path = Server.MapPath(string.Format("~/Template/wf_form_{0}.xls", tableName));
UserInfo user = (UserInfo)Session["user"]; // 个人信息
string dateTime = DateTime.Now.ToString("yyyy年MM月dd日");
try
{
using (FileStream fs = new FileStream(path, FileMode.Open))
{
HSSFWorkbook wbHssf = new HSSFWorkbook(fs);
ISheet sheet = wbHssf.GetSheetAt(0);
sheet.ForceFormulaRecalculation = true;
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(4);
cell.SetCellValue(dateTime);
row = sheet.GetRow(2);
cell = row.GetCell(1);
cell.SetCellValue(dr["借款单位、个人"].ToString());
cell = row.GetCell(4);
cell.SetCellValue(dr["付款单位"].ToString());
row = sheet.GetRow(3);
cell = row.GetCell(1);
cell.SetCellValue(dr["收款户名、开户行"].ToString());
cell = row.GetCell(4);
cell.SetCellValue(dr["付款行"].ToString());
row = sheet.GetRow(4);
cell = row.GetCell(1);
cell.SetCellValue(dr["账号"].ToString());
cell = row.GetCell(4);
cell.SetCellValue(dr["付款方式"].ToString());
row = sheet.GetRow(5);
cell = row.GetCell(1);
cell.SetCellValue(dr["借款用途"].ToString());
cell = row.GetCell(4);
cell.SetCellValue(dr["还款期限"].ToString() + "之前");
row = sheet.GetRow(6);
cell = row.GetCell(5);
cell.SetCellValue("￥" + dr["借款金额"].ToString());
row = sheet.GetRow(7);
cell = row.GetCell(1);
// 审批流程
string msg = string.Empty;
foreach (DataRow recordDr in recordDt.Rows)
{
msg += recordDr[1].ToString() + "->";
}
cell.SetCellValue(msg);
row = sheet.GetRow(9);
cell = row.GetCell(1);
cell.SetCellValue(dr["备注"].ToString());
//填完数据后，统一更新公式计算
wbHssf.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
sheet.ProtectSheet("Yelioa123"); // 加密
string companyName = dr["付款单位"].ToString();
drawWaterRemarkPath(wbHssf, Server.MapPath(string.Format("~/resources/{0}.png", companyName)));
byte[] data = null;
using (MemoryStream ms = new MemoryStream())
{
wbHssf.Write(ms);
ms.Flush();
ms.Position = 0;
data = ms.GetBuffer();
}
wbHssf = null;
sheet = null;
string filecode = ValideCodeHelper.GetRandomCode(64);
string newPath = Server.MapPath("~/tempExportFile");
newPath = newPath + @"\" + filecode + ".xls";
BytesToFile(newPath, data);
res.Add("ErrCode", 0);
res.Add("FileCount", 1);
res.Add("FileCode1", filecode);
//res.Add("FileName1", fileName);
}
}
catch (Exception ex)
{
res.Add("ErrCode", 1);
res.Add("ErrMsg", "暂无对应模板！");
}
Response.Write(res.ToString());
}
