private string uploadFile() { string res = "读取文件失败!"; DataTable dt = ExcelHelperV2_0.Import(Request); if (dt != null) { dt.Columns.Add("Proposer"); dt.Columns.Add("CreatingTime"); for (int i = 0; i < dt.Rows.Count; i++) { if (!dt.Rows[i]["事件日期"].ToString().Contains("/") && !dt.Rows[i]["事件日期"].ToString().Contains("-")) { dt.Rows[i]["事件日期"] = DateTime.FromOADate(double.Parse(dt.Rows[i]["事件日期"].ToString())).ToString(); } dt.Rows[i]["Proposer"] = GetUserInfo().userName; dt.Rows[i]["CreatingTime"] = DateTime.Now.ToString(); } dt.Columns["被申请人"].ColumnName = "Target"; dt.Columns["B积分"].ColumnName = "Bpoint"; dt.Columns["事件"].ColumnName = "Event"; dt.Columns["事件类型"].ColumnName = "Type"; dt.Columns["事件日期"].ColumnName = "EffectiveTime"; res = SqlHelper.Exce(SqlHelper.GetInsertIgnoreString(dt, "accumulate_points")); } return(res); }
//protected string exportExcel() //{ // string applystarttm = Request.Params["applystarttm"]; // string applyendtm = Request.Params["applyendtm"]; // string starttm = Request.Params["starttm"]; // string endtm = Request.Params["endtm"]; // string applyName = Request.Params["apply_name"]; // string depart = Request.Params["depart"]; // string fee_depart = Request.Params["fee_depart"]; // string fee_detail = Request.Params["fee_detail"]; // string account_status = Request.Form["account_status"]; // DataTable dt = ReimbursementManage.findByCond(applystarttm, applyendtm, starttm, endtm, applyName, depart, fee_depart, fee_detail, account_status, null); // JObject resultJObject = new JObject(); // if (dt == null || dt.Columns.Count == 0) // { // resultJObject.Add("msg", "暂无数据导出"); // } // else // { // string[] chineseHeaders = new string[] {"序号", "编号", "提交日期", "审批日期", "财务审批日期", // "提交人", "部门", "费用归属部门", "产品", "费用明细", "金额", "实报金额", "状态", "审批人", "财务审批人", "抄送人", "备注", "审批意见", "审批结果"}; // ExcelHelperV2_0.ExportByWeb(dt, "财务审批单据信息", "财务审批单据信息.xlsx", chineseHeaders); // resultJObject.Add("msg", "导出成功"); // } // return resultJObject.ToString(); //} //private string uploadBudgetFile() //{ // string res = "读取文件失败!"; // DataTable dt = ExcelHelperV2_0.Import(Request); // if (dt != null) // { // res=ReimbursementManage.uploadBudgetFile(dt); // } // return res; //} private string uploadFile() { string res = "上传成功!"; DataTable dt = ExcelHelperV2_0.Import(Request); UserInfo user = (UserInfo)Session["user"]; string sql = ""; if (dt != null) { List <Dictionary <string, object> > list = new List <Dictionary <string, object> >(); foreach (DataRow row in dt.Rows) { Dictionary <string, object> dict = new Dictionary <string, object>(); dict.Add("code", row[0].ToString()); dict.Add("actual_fee_amount", row[1].ToString()); dict.Add("approver", user.userName.ToString()); dict.Add("actual_fee_amount_time", DateTime.Now); list.Add(dict); } string msg = ReimbursementManage.updateActualFee(list); } return(res); }
private string uploadFile() { string res = "读取文件失败!"; DataTable dt = ExcelHelperV2_0.Import(Request); if (dt != null) { dt.Columns.Add("companyId"); dt.Columns.Add("departmentId"); dt.Columns.Add("department"); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["companyId"] = GetUserInfo().companyId; dt.Rows[i]["departmentId"] = "1"; dt.Rows[i]["department"] = "业力集团"; string[] values = dt.Rows[i]["毕业学校"].ToString().Split('-'); if (values.Length > 1)// { dt.Rows[i]["毕业学校"] = values[0]; dt.Rows[i]["专业"] = values[1]; } } res = UserInfoManage.InsertInfos(dt); } return(res); }
private string uploadFile() { string res = ""; try { HttpFileCollection httpFileCollection = Request.Files; HttpPostedFile file = null; if (httpFileCollection.Count <= 0) { res = "文件上传未成功"; } else { file = httpFileCollection[0]; //Excel读取 DataTable dt = ExcelHelperV2_0.Import(file.InputStream); UserInfo user = (UserInfo)Session["user"]; DataTable newDt = EnterStockInfoManage.InsertInfos(dt, user.companyId.ToString()); res = JsonHelper.DataTableToJsonForEasyUiDataGridLoadDataMethod(newDt.Rows.Count, newDt); } } catch (Exception ex) { res = ex.ToString(); } return(res); }
private string uploadFile() { string res = "读取文件失败!"; DataTable dt = ExcelHelperV2_0.Import(Request); if (dt != null) { res = ProductInfoManage.InsertAliasData(dt); } return(res); }
private string uploadFile() { string res = "读取文件失败!"; DataTable dt = ExcelHelperV2_0.Import(Request); if (dt != null) { res = CostSharingInfoManage.InsertInfos(dt); } return(res); }
private string uploadFile() { string res = "读取文件失败!"; DataTable dt = ExcelHelperV2_0.Import(Request); if (dt != null) { res = OrganizationInfoManage.SaveInfos(dt); } return(res); }
private string Insert() { string res = "F"; DataTable dt = ExcelHelperV2_0.Import(Request); if (dt != null) { res = LeaveStockInfoManage.InsertInfos(ref dt, Request.Form["dataType"]); if (res.IndexOf("error:") != 0) { res = JsonHelper.DataTableToJsonForEasyUiDataGridLoadDataMethod(dt.Rows.Count, dt); } } return(res); }
public static DataTable Import(HttpRequest request, int headerIndex) { DataTable dt = null; HttpFileCollection httpFileCollection = request.Files; HttpPostedFile file = null; if (httpFileCollection.Count > 0) { file = httpFileCollection[0]; //Excel读取 dt = ExcelHelperV2_0.Import(file.InputStream, 0, headerIndex); } return(dt); // return null; }
private string uploadFile() { string res = "读取文件失败!"; DataTable dt = ExcelHelperV2_0.Import(Request); if (dt != null) { dt.Columns.Add("companyId"); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["companyId"] = GetUserInfo().companyId; } res = ProductInfoManage.SaveInfos(dt); } return(res); }
private string ShowFile() { string res = "F"; DataTable dt = ExcelHelperV2_0.Import(Request); if (dt != null) { dt.Columns.Add("状态"); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["状态"] = "未导入"; } res = JsonHelper.DataTableToJsonForEasyUiDataGridLoadDataMethod(dt.Rows.Count, dt); } return(res); }
public static DataTable Import(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex, Boolean flag) { XSSFWorkbook wbXssf = null; HSSFWorkbook wbHssf = null; ISheet sheet = null; // JObject jObject = new JObject(); DataTable dt = new DataTable(); try { wbXssf = new XSSFWorkbook(ExcelFileStream); sheet = wbXssf.GetSheetAt(SheetIndex); } catch (Exception ex) { if (ex.ToString().Contains("Wrong Local header signature")) { wbHssf = new HSSFWorkbook(ExcelFileStream); sheet = wbHssf.GetSheetAt(SheetIndex); } } finally { dt = ExcelHelperV2_0.Import(sheet, HeaderRowIndex); dt = CostSharingHelper.importCostSharing(dt, flag); //string result = CostSharingHelper.importCostSharing(dt, true); //if (result.Contains("成功")) //{ // jObject.Add("ErrCode", 0); // jObject.Add("ErrMsg", "操作成功"); //} //else //{ // jObject.Add("ErrCode", 1); // jObject.Add("ErrMsg", "操作失败"); //} ExcelFileStream.Close(); } return(dt); }
private string CheckRepitition() { string res = "F"; DataTable dt = ExcelHelperV2_0.Import(Request); if (dt != null) { dt.Columns.Add("状态"); dt.Columns["状态"].SetOrdinal(0);//调整到第一列 for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["状态"] = "未导入"; } dt = CheckData(dt); res = JsonHelper.DataTableToJsonForEasyUiDataGridLoadDataMethod(dt.Rows.Count, dt); } return(res); }
private string ShowFile() { string res = "F"; DataTable dt = ExcelHelperV2_0.Import(Request); if (dt != null) { //补齐空缺的或省略的cell,第一行为空除外 //dt = LeaveStockInfoManage.FillBlankCell(dt); dt.Columns.Add("状态"); dt.Columns["状态"].SetOrdinal(0);//调整到第一列 for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["状态"] = "未导入"; } res = JsonHelper.DataTableToJsonForEasyUiDataGridLoadDataMethod(dt.Rows.Count, dt); } return(res); }
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(); }
protected string exportExcel() { DataTable dt = PinYinHelper.SortByPinYin(Getdata3(), Session["sort3"].ToString(), Session["order3"].ToString()); dt.Columns.Remove(dt.Columns[10]); JObject resultJObject = new JObject(); string[] chineseHeaders = new string[] { "姓名", "部门", "月度积分", "季度积分", "年度积分", "总积分", "月度加分次数", "月度加分数", "月度扣分次数", "月度扣分数" }; if (dt == null || dt.Columns.Count == 0) { resultJObject.Add("msg", "暂无数据导出"); } else { ExcelHelperV2_0.ExportByWeb(dt, "部门积分信息", "部门积分信息.xls", chineseHeaders); resultJObject.Add("msg", "导出成功"); } return(resultJObject.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 importFlow() { string year = Request.Form["wage-year"]; string month = Request.Form["wage-month"]; JObject res = new JObject(); DataTable dtExl = ExcelHelperV2_0.Import(Request, 1); if (dtExl.Rows.Count == 0) { res.Add("ErrCode", 1); res.Add("ErrMsg", "导入数据失败:数据为空!"); return(res.ToString()); } UserInfo user = (UserInfo)Session["user"]; DataTable dt = new DataTable(); dt.Columns.Add("ReportDepartmentName"); dt.Columns.Add("FeeName"); dt.Columns.Add("FeeAmount"); dt.Columns.Add("Budget"); dt.Columns.Add("Year"); dt.Columns.Add("Month"); dt.Columns.Add("CreateTime"); dt.Columns.Add("UserId"); foreach (DataRow iRow in dtExl.Rows) { DataRow row = dt.NewRow(); string depart = iRow["部门"].ToString(); if (depart == "部门") { continue; } // 判断部门是否存在 DataTable tempDt = SqlHelper.Find(string.Format("select 1 from report_department where name = '{0}'", depart)).Tables[0]; if (tempDt.Rows.Count == 0) { res.Add("ErrCode", 0); res.Add("ErrMsg", "'" + depart + "'不存在,请确定该部门是否正确"); return(res.ToString()); } double flow = double.Parse(iRow["流向"].ToString()); double netSales = double.Parse(iRow["纯销"].ToString()); double grossProfit = double.Parse(iRow["毛利"].ToString()); row["ReportDepartmentName"] = depart; row["FeeName"] = "流向"; row["Year"] = year; row["Month"] = month; row["CreateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); row["UserId"] = user.userId; row["FeeAmount"] = flow; row["Budget"] = 0; dt.Rows.Add(row); row = dt.NewRow(); row["ReportDepartmentName"] = depart; row["FeeName"] = "纯销"; row["Year"] = year; row["Month"] = month; row["CreateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); row["UserId"] = user.userId; row["FeeAmount"] = netSales; row["Budget"] = 0; dt.Rows.Add(row); row = dt.NewRow(); row["ReportDepartmentName"] = depart; row["FeeName"] = "毛利"; row["Year"] = year; row["Month"] = month; row["CreateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); row["UserId"] = user.userId; row["FeeAmount"] = grossProfit; row["Budget"] = 0; dt.Rows.Add(row); } //string sql = string.Format("delete from yl_reimburse_other where year = '{0}' " + // " and month='{1}'\r\n;", year, month); string sql = SqlHelper.GetInsertString(dt, "yl_reimburse_other"); SqlExceRes r = new SqlExceRes(SqlHelper.Exce(sql)); if (r.Result == SqlExceRes.ResState.Success) { res.Add("ErrCode", 0); res.Add("ErrMsg", r.ExceMsg); } else { res.Add("ErrCode", 4); res.Add("ErrMsg", r.ExceMsg); } return(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()); }
private void import() { DataTable dtExl = ExcelHelperV2_0.Import(Request); if (dtExl.Rows.Count == 0) { Response.Write("导入数据失败:数据为空!"); return; } List <int> CheckList = new List <int>(); foreach (DataRow row in dtExl.Rows) { int val = Convert.ToInt32(row["Id"]); if (CheckList.Contains(val)) { Response.Write("导入数据失败:有重复Id!"); return; } } DateTime now = DateTime.Now; DateTime next = now.AddMonths(1); string sql = string.Format("delete from import_budget where CreateTime between " + "'{0}-{1}-1 00:00:00 ' and '{2}-{3}-1 00:00:00';", now.Year, now.Month, next.Year, next.Month); SqlHelper.Exce(sql); int startId = 0; sql = "select max(Id) from import_budget"; object obj = SqlHelper.Scalar(sql); if (obj != null) { startId = Convert.ToInt32(obj) + 1; } DataTable dt = new DataTable(); dt.Columns.Add("Id"); dt.Columns.Add("DepartmentId"); dt.Columns.Add("Budget"); dt.Columns.Add("FeeDetail"); dt.Columns.Add("ParentId"); dt.Columns.Add("CreateTime"); Dictionary <int, int> dictId = new Dictionary <int, int>(); foreach (DataRow row in dtExl.Rows) { DataRow r = dt.NewRow(); int ParentId = Convert.ToInt32(row["父节点Id"]); if (ParentId == -1) { dictId.Add(Convert.ToInt32(row["Id"]), startId); r["ParentId"] = -1; } else { r["ParentId"] = dictId[ParentId]; } r["Id"] = startId++; r["DepartmentId"] = row["部门Id"]; r["Budget"] = row["预算金额"]; r["FeeDetail"] = row["费用明细"]; r["CreateTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); dt.Rows.Add(r); } sql = SqlHelper.GetInsertString(dt, "import_budget"); Response.Write(SqlHelper.Exce(sql)); }
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(); } } }
private string uploadInterestExcel() { DataTable dtExl = ExcelHelperV2_0.Import(Request, 2); DataTable dt = new DataTable(); if (dtExl.Rows.Count == 0) { return(JsonHelper.DataTable2Json(dt)); } // 再新增 int startId = 0; string sql = "select max(Id) from tax;"; object obj = SqlHelper.Scalar(sql); if (obj != null) { startId = Convert.ToInt32(obj) + 1; } dt.Columns.Add("Id"); dt.Columns.Add("DocCode"); dt.Columns.Add("Level"); dt.Columns.Add("Status"); dt.Columns.Add("Company"); dt.Columns.Add("Name"); dt.Columns.Add("LoanDate"); dt.Columns.Add("DueDate"); dt.Columns.Add("Frequency"); dt.Columns.Add("Amount"); dt.Columns.Add("YearRate"); dt.Columns.Add("MonthRate"); dt.Columns.Add("PaidInterest"); dt.Columns.Add("Proviston"); dt.Columns.Add("NotProviston"); dt.Columns.Add("PaidPrincipal"); dt.Columns.Add("ProvistonDate"); dt.Columns.Add("Remark"); dt.Columns.Add("LastPrincipal"); UserInfo user = (UserInfo)Session["user"]; string year = Request.Form["year"]; string month = Request.Form["month"]; string company = Request.Form["company"]; string docCode = GenerateDocCode.generateRandomDocCode(); foreach (DataRow dr in dtExl.Rows) { //if (string.IsNullOrEmpty(dr["税种"].ToString()) || dr["税种"].ToString().Trim().Contains("合 计") || dr["税种"].ToString().Contains("总经理")) // continue; DataRow r = dt.NewRow(); r["SubmitterId"] = user.userId.ToString(); r["Year"] = year; r["Month"] = month; r["CreateTime"] = DateTime.Now; r["Company"] = company; r["Id"] = startId++; r["DocCode"] = docCode; r["Level"] = 1; r["Status"] = "审批中"; r["Name"] = dr["名称"]; r["LoanDate"] = dr["借款时间"]; r["DueDate"] = dr["到期时间"]; r["Frequency"] = dr["利息支付频率"]; r["Amount"] = dr["借款金额"]; r["YearRate"] = dr["年利率"]; r["MonthRate"] = dr["月利息"]; r["PaidInterest"] = dr["已付银行利息"]; r["Proviston"] = dr["已计提"]; r["NotProviston"] = dr["还应计提"]; r["PaidPrincipal"] = dr["已还本金"]; r["ProvistonDate"] = dr["利息计提期间"]; r["Remark"] = dr["摘要"]; r["LastPrincipal"] = dr["期末结余本金"]; dt.Rows.Add(r); } return(JsonHelper.DataTable2Json(dt)); }
private string uploadOuterWageExcel() { DataTable dtExl = ExcelHelperV2_0.Import(Request, 2); DataTable dt = new DataTable(); if (dtExl.Rows.Count == 0) { return(JsonHelper.DataTable2Json(dt)); } // 再新增 int startId = 0; string sql = "select max(Id) from tax;"; object obj = SqlHelper.Scalar(sql); if (obj != null) { startId = Convert.ToInt32(obj) + 1; } dt.Columns.Add("Id"); dt.Columns.Add("DocCode"); dt.Columns.Add("Level"); dt.Columns.Add("Status"); dt.Columns.Add("Name"); dt.Columns.Add("Company"); dt.Columns.Add("Department"); dt.Columns.Add("Position"); dt.Columns.Add("WorkingHour"); dt.Columns.Add("WorkingPrice"); dt.Columns.Add("WorkingAmount"); dt.Columns.Add("TotalSalary"); dt.Columns.Add("ActualPerformance"); dt.Columns.Add("Bonus"); dt.Columns.Add("TotalPerformanceBonus"); dt.Columns.Add("RubbishFine"); dt.Columns.Add("ProductFine"); dt.Columns.Add("Fine"); dt.Columns.Add("MutualFund"); dt.Columns.Add("OtherFund"); dt.Columns.Add("TotalFineAndFund"); dt.Columns.Add("TotalPay"); dt.Columns.Add("SocialInsurance"); dt.Columns.Add("PublicFund"); dt.Columns.Add("PersonalTax"); dt.Columns.Add("Rent"); dt.Columns.Add("ActualPay"); dt.Columns.Add("ActualPay2Decimal"); dt.Columns.Add("Remark"); dt.Columns.Add("SubmitterId"); dt.Columns.Add("Year"); dt.Columns.Add("Month"); dt.Columns.Add("CreateTime"); UserInfo user = (UserInfo)Session["user"]; string year = Request.Form["year"]; string month = Request.Form["month"]; string company = Request.Form["company"]; string docCode = GenerateDocCode.generateRandomDocCode(); foreach (DataRow dr in dtExl.Rows) { if (string.IsNullOrEmpty(dr["序号"].ToString()) || dr["序号"].ToString().Contains("合计")) { continue; } DataRow r = dt.NewRow(); r["SubmitterId"] = user.userId.ToString(); r["Year"] = year; r["Month"] = month; r["CreateTime"] = DateTime.Now; r["Company"] = company; r["Id"] = startId++; r["DocCode"] = docCode; r["Level"] = 1; r["Status"] = "审批中"; r["Name"] = dr["姓名"]; r["Department"] = dr["部门"]; r["Position"] = dr["职位"]; r["WorkingHour"] = dr["出勤小时"]; r["WorkingPrice"] = dr["计件标准(元/件)"]; r["WorkingAmount"] = dr["计件数"]; r["TotalSalary"] = dr["计件工资合计"]; r["ActualPerformance"] = dr["实发绩效"]; r["Bonus"] = dr["奖励金额"]; r["TotalPerformanceBonus"] = dr["绩效及奖励小计"]; r["RubbishFine"] = dr["残次品扣款"]; r["ProductFine"] = dr["产品扣罚"]; r["Fine"] = dr["罚款"]; r["MutualFund"] = dr["代扣基金"]; r["OtherFund"] = dr["其它"]; r["TotalFineAndFund"] = dr["工资减项小计"]; r["TotalPay"] = dr["应发合计"]; r["SocialInsurance"] = dr["社保"]; r["PublicFund"] = dr["公积金"]; r["PersonalTax"] = dr["个人所得税"]; r["Rent"] = dr["代扣房租"]; r["ActualPay"] = dr["实发工资"]; r["ActualPay2Decimal"] = dr["保留两位"]; r["Remark"] = dr["备注"]; dt.Rows.Add(r); } return(JsonHelper.DataTable2Json(dt)); }
private string uploadAmortizeExcel() { DataTable dtExl = ExcelHelperV2_0.Import(Request, 2); DataTable dt = new DataTable(); if (dtExl.Rows.Count == 0) { return(JsonHelper.DataTable2Json(dt)); } // 再新增 int startId = 0; string sql = "select max(Id) from tax;"; object obj = SqlHelper.Scalar(sql); if (obj != null) { startId = Convert.ToInt32(obj) + 1; } dt.Columns.Add("Id"); dt.Columns.Add("DocCode"); dt.Columns.Add("Level"); dt.Columns.Add("Status"); dt.Columns.Add("Company"); dt.Columns.Add("Type"); dt.Columns.Add("Project"); dt.Columns.Add("OriginValue"); dt.Columns.Add("CurrentA"); dt.Columns.Add("AccumulatedA"); dt.Columns.Add("FinalValue"); dt.Columns.Add("Remark"); dt.Columns.Add("SubmitterId"); dt.Columns.Add("Year"); dt.Columns.Add("Month"); dt.Columns.Add("CreateTime"); UserInfo user = (UserInfo)Session["user"]; string year = Request.Form["year"]; string month = Request.Form["month"]; string company = Request.Form["company"]; string docCode = GenerateDocCode.generateRandomDocCode(); foreach (DataRow dr in dtExl.Rows) { if (string.IsNullOrEmpty(dr["类型"].ToString()) || dr["类型"].ToString().Trim().Contains("合计") || dr["类型"].ToString().Contains("总经理")) { continue; } DataRow r = dt.NewRow(); r["SubmitterId"] = user.userId.ToString(); r["Year"] = year; r["Month"] = month; r["CreateTime"] = DateTime.Now; r["Company"] = company; r["Id"] = startId++; r["DocCode"] = docCode; r["Level"] = 1; r["Status"] = "审批中"; r["Type"] = dr["类型"]; r["Project"] = dr["项目"]; r["OriginValue"] = dr["原值"]; r["CurrentA"] = dr["本月摊销"]; r["AccumulatedA"] = dr["累计摊销"]; r["FinalValue"] = dr["期末余额"]; r["Remark"] = dr["备注"]; dt.Rows.Add(r); } return(JsonHelper.DataTable2Json(dt)); }
private string uploadWageExcel() { DataTable dtExl = ExcelHelperV2_0.Import(Request, 3); DataTable dt = new DataTable(); if (dtExl.Rows.Count == 0) { return(JsonHelper.DataTable2Json(dt)); } // 再新增 int startId = 0; string sql = "select max(Id) from wages;"; object obj = SqlHelper.Scalar(sql); if (obj != null) { startId = Convert.ToInt32(obj) + 1; } dt.Columns.Add("Id"); dt.Columns.Add("DocCode"); dt.Columns.Add("Level"); dt.Columns.Add("Status"); dt.Columns.Add("Name"); dt.Columns.Add("Company"); dt.Columns.Add("Department"); dt.Columns.Add("Position"); dt.Columns.Add("AttendanceDay"); dt.Columns.Add("AbsenceDay"); dt.Columns.Add("PositionSalary"); dt.Columns.Add("SecretSalary"); dt.Columns.Add("LevelSalary"); dt.Columns.Add("TechnicalSalary"); dt.Columns.Add("StableSalary"); dt.Columns.Add("OtherSalary"); dt.Columns.Add("EducationAllowance"); dt.Columns.Add("OtherAllowance"); dt.Columns.Add("TotalStableSalary"); dt.Columns.Add("MonthlyPerformance"); dt.Columns.Add("QuarterlyPerformance"); dt.Columns.Add("YearlyPerformance"); dt.Columns.Add("ActualPerformance"); dt.Columns.Add("CSalesBonus"); dt.Columns.Add("QSalesBonus"); dt.Columns.Add("HeadBonus"); dt.Columns.Add("ManageBonus"); dt.Columns.Add("UnusualBonus"); dt.Columns.Add("TesterBonus"); dt.Columns.Add("OvertimeBonus"); dt.Columns.Add("ProductBonus"); dt.Columns.Add("GMBonus"); dt.Columns.Add("RDBonus"); dt.Columns.Add("OtherBonus1"); dt.Columns.Add("OtherBonus2"); dt.Columns.Add("TotalBonus"); dt.Columns.Add("TotalPerformanceBonus"); dt.Columns.Add("AttendaceFine"); dt.Columns.Add("SanitaryFine"); dt.Columns.Add("AbsenceFine"); dt.Columns.Add("ExpireFine"); dt.Columns.Add("ViolationFine"); dt.Columns.Add("OtherFine1"); dt.Columns.Add("OtherFine2"); dt.Columns.Add("TotalFine"); dt.Columns.Add("MutualFund"); dt.Columns.Add("OtherFund"); dt.Columns.Add("TotalFineAndFund"); dt.Columns.Add("TotalPay"); dt.Columns.Add("TotalPay1"); dt.Columns.Add("TotalPay2"); dt.Columns.Add("SocialInsurance"); dt.Columns.Add("SocialInsurance1"); dt.Columns.Add("PublicFund"); dt.Columns.Add("PublicFund1"); dt.Columns.Add("PersonalTax"); dt.Columns.Add("PersonalTax1"); dt.Columns.Add("Rent"); dt.Columns.Add("PersonalTaxAdjust"); dt.Columns.Add("PersonalTaxAdjust1"); dt.Columns.Add("ActualPay"); dt.Columns.Add("ActualPay1"); dt.Columns.Add("ActualPay2"); dt.Columns.Add("ActualPay2Decimal"); dt.Columns.Add("Remark"); dt.Columns.Add("SubmitterId"); dt.Columns.Add("Year"); dt.Columns.Add("Month"); dt.Columns.Add("CreateTime"); UserInfo user = (UserInfo)Session["user"]; string year = Request.Form["year"]; string month = Request.Form["month"]; string company = Request.Form["company"]; string docCode = GenerateDocCode.generateRandomDocCode(); foreach (DataRow dr in dtExl.Rows) { if (string.IsNullOrEmpty(dr["序号"].ToString()) || dr["序号"].ToString().Contains("合计")) { continue; } DataRow r = dt.NewRow(); r["SubmitterId"] = user.userId.ToString(); r["Year"] = year; r["Month"] = month; r["CreateTime"] = DateTime.Now; r["Company"] = company; r["Id"] = startId++; r["DocCode"] = docCode; r["Level"] = 1; r["Status"] = "审批中"; r["Name"] = dr["姓名"]; r["Department"] = dr["中心"].ToString() + dr["部门"] + dr["战区"] + dr["大区"] + dr["地区"]; r["Position"] = dr["职务"]; r["AttendanceDay"] = dr["考勤"]; r["AbsenceDay"] = dr["缺勤天数"]; r["PositionSalary"] = dr["职位工资"]; r["SecretSalary"] = dr["保密工资"]; r["LevelSalary"] = dr["档位工资"]; r["TechnicalSalary"] = dr["技衔工资"]; r["OtherSalary"] = dr["其他工资"]; r["StableSalary"] = dr["固定工资小计"]; r["EducationAllowance"] = dr["学历津贴"]; r["OtherAllowance"] = dr["其它津贴"]; r["TotalStableSalary"] = dr["固定工资合计"]; r["MonthlyPerformance"] = dr["月度绩效"]; r["QuarterlyPerformance"] = dr["季度绩效"]; r["YearlyPerformance"] = dr["年度绩效"]; r["ActualPerformance"] = dr["实发绩效"]; r["CSalesBonus"] = dr["纯销代表奖金"]; r["QSalesBonus"] = dr["渠道代表奖金"]; r["HeadBonus"] = dr["主管奖金"]; r["ManageBonus"] = dr["管理层奖金"]; r["UnusualBonus"] = dr["异样考核"]; r["TesterBonus"] = dr["实验奖金"]; r["OvertimeBonus"] = dr["加班"]; r["ProductBonus"] = dr["生产产能"]; r["GMBonus"] = dr["总经理特别贡献"]; r["RDBonus"] = dr["研发项目奖金"]; r["OtherBonus1"] = dr["其他奖励1"]; r["OtherBonus2"] = dr["其他奖励2"]; r["TotalBonus"] = dr["奖励合计"]; r["TotalPerformanceBonus"] = dr["绩效及奖励小计"]; r["AttendaceFine"] = dr["考勤扣款"]; r["SanitaryFine"] = dr["卫生检查罚款"]; r["AbsenceFine"] = dr["出勤扣款"]; r["ExpireFine"] = dr["产品过期罚款"]; r["ViolationFine"] = dr["违反制度罚款"]; r["OtherFine1"] = dr["其他罚款1"]; r["OtherFine2"] = dr["其他罚款2"]; r["TotalFine"] = dr["罚款合计"]; r["MutualFund"] = dr["代扣基金"]; r["OtherFund"] = dr["其它减项"]; r["TotalFineAndFund"] = dr["工资减项小计"]; r["TotalPay"] = dr["应发合计"]; r["TotalPay1"] = dr["应发1"]; r["TotalPay2"] = dr["应发2"]; r["SocialInsurance"] = dr["社保"]; r["SocialInsurance1"] = dr["社保1"]; r["PublicFund"] = dr["公积金"]; r["PublicFund1"] = dr["公积金1"]; r["PersonalTax"] = dr["个人所得税"]; r["PersonalTax1"] = dr["个人所得税1"]; r["Rent"] = dr["代扣房租"]; r["PersonalTaxAdjust"] = dr["个税调整项"]; r["PersonalTaxAdjust1"] = dr["个税调整项1"]; r["ActualPay"] = dr["实发合计工资"]; r["ActualPay1"] = dr["实发1"]; r["ActualPay2"] = dr["实发2"]; r["ActualPay2Decimal"] = dr["实发合计保留两位"]; r["Remark"] = dr["备注"]; dt.Rows.Add(r); } return(JsonHelper.DataTable2Json(dt)); }