private void getTemplate()
{
DateTime now = DateTime.Now;
DateTime last = now.AddMonths(-1);
string sql = string.Format("SELECT import_budget.Id,department.`name`,import_budget.DepartmentId,import_budget.ParentId," +
"import_budget.FeeDetail,0 as budget FROM import_budget " +
"INNER JOIN department ON import_budget.DepartmentId = department.Id " +
"where import_budget.CreateTime between '{0}-{1}-1 00:00:00 ' and '{2}-{3}-1 00:00:00' order by import_budget.Id;"
, last.Year, last.Month, now.Year, now.Month);
string msg = "";
DataSet ds = SqlHelper.Find(sql, ref msg);
if (ds == null)
{
Response.Write(msg);
return;
}
//string path = Server.MapPath("~/Template");
//if (!Directory.Exists(path))
//{
// Directory.CreateDirectory(path);
//}
//path = path + @"\预算导入模板.xls";
//if (File.Exists(path))
// File.Delete(path);
List <string> listHeadText = new List <string>();
listHeadText.Add("Id");
listHeadText.Add("部门名称");
listHeadText.Add("部门Id");
listHeadText.Add("父节点Id");
listHeadText.Add("费用明细");
listHeadText.Add("预算金额");
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = Encoding.UTF8;
Response.Charset = "";
Response.AppendHeader("Content-Disposition", "attachment;filename="
+ HttpUtility.UrlEncode("预算导入模板.xls", Encoding.UTF8));
Response.BinaryWrite(ExcelHelperV2_0.Export(ds.Tables[0], "预算导入模板", listHeadText.ToArray()).GetBuffer());
Response.End();
}
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();
}
}
}