public string DownLoadFeeAccount()
{
var employee = Marisfrolg.Public.Common.GetEmployeeInfo();
MemoryCachingClient M = new MemoryCachingClient();
List <PublicClass> dt = M.GetData(employee.EmployeeNo) as List <PublicClass>;
Workbook workbook = new Workbook(); //工作簿
Worksheet sheet = workbook.Worksheets[0]; //工作表
Cells cells = sheet.Cells; //单元格
List <string> Title = new List <string>()
{
"费用大类", "费用项", "审批岗", "是否可见", "是否考核", "科目号", "排序号", "审核人员"
};
int Colnum = Title.Count; //表格列数
int Rownum = dt.Count; //表格行数
//生成行2 列名行
for (int i = 0; i < Colnum; i++)
{
cells[0, i].PutValue(Title[i]);
cells.SetRowHeight(1, 25);
}
//生成数据行
for (int i = 0; i < Rownum; i++)
{
cells[1 + i, 0].PutValue(dt[i].c1);
cells[1 + i, 1].PutValue(dt[i].c2);
cells[1 + i, 2].PutValue(dt[i].c3);
cells[1 + i, 3].PutValue(dt[i].c4);
cells[1 + i, 4].PutValue(dt[i].c5);
cells[1 + i, 5].PutValue(dt[i].c6);
cells[1 + i, 6].PutValue(dt[i].c7);
cells[1 + i, 7].PutValue(dt[i].c8);
}
string NewlocalPath = Path.Combine(HttpRuntime.AppDomainAppPath, "Upload", "ExcelDownLoad");
if (!System.IO.Directory.Exists(NewlocalPath))
{
System.IO.Directory.CreateDirectory(NewlocalPath);
}
string filePathName = "费用科目明细" + '-' + DateTime.Now.ToString("yyMMddhhmmss") + ".xls";
workbook.Save(Path.Combine(NewlocalPath, filePathName));
return(filePathName);
}
public string GetFeeItems()
{
string sql = "select c.name as c1,c.code as c10, a.name as c2,b.name as c3, CASE when a.hide=1 then '都可见' when a.hide=0 then '总部可见片区不可见' else '片区可见总部不可见' end as c4,CASE when a.IS_MARKET=1 then '考核' else '不考核' end as c5,a.ACCOUNT as c6,a.sort||'' as c7,a.id||'' as c9 from fee_account a left join FEE_ACCOUNT_DICTIONARY b on a.WORKFLOW_CODE=b.code and b.brand='MF' left join fee_account_type c on a.account_type=c.code order by a.account_type";
var Database = DbContext.Database.SqlQuery <PublicClass>(sql).ToList();
var list = Database.GroupBy(c => c.c3).ToList();
foreach (var item in list)
{
var str = GetPostStaff(item.Key);
Database.Where(c => c.c3 == item.Key).ToList().ForEach(c => c.c8 = str);
}
var employee = Marisfrolg.Public.Common.GetEmployeeInfo();
MemoryCachingClient M = new MemoryCachingClient();
M.Remove(employee.EmployeeNo);
M.Add(employee.EmployeeNo, Database);
return(Public.JsonSerializeHelper.SerializeToJson(Database));
}
public string GetReportData(int billType, int Time, int departmentID, string operation, string StartTime = "", string EndTime = "")
{
var employee = Marisfrolg.Public.Common.GetEmployeeInfo();
//我搁置的单
if (operation == "1")
{
List <FeeBillModelRef> AllModel = new List <FeeBillModelRef>();
var Temp1 = new FeeBill().ReturnShelveNo(employee.EmployeeName);
var Temp2 = new NoticeBill().ReturnShelveNo(employee.EmployeeNo);
var Temp3 = new BorrowBill().ReturnShelveNo(employee.EmployeeNo);
var Temp4 = new RefundBill().ReturnShelveNo(employee.EmployeeNo);
foreach (var item1 in Temp1)
{
FeeBillModelRef TempModel = new FeeBillModelRef()
{
BillNo = item1.BillNo, PageName = "费用报销单", Creator = item1.Creator, Owner = item1.Owner, TotalMoney = item1.TotalMoney, StringTime = item1.CreateTime.ToString("yyyy-MM-dd"), CreateTime = item1.CreateTime, PersonInfo = new PersonInfo()
{
DepartmentCode = item1.PersonInfo.DepartmentCode, Department = item1.PersonInfo.Department
}
};
AllModel.Add(TempModel);
}
foreach (var item2 in Temp2)
{
FeeBillModelRef TempModel = new FeeBillModelRef()
{
BillNo = item2.BillNo, PageName = "付款通知书", Creator = item2.Creator, Owner = item2.Owner, TotalMoney = item2.TotalMoney, StringTime = item2.CreateTime.ToString("yyyy-MM-dd"), CreateTime = item2.CreateTime, PersonInfo = new PersonInfo()
{
DepartmentCode = item2.PersonInfo.DepartmentCode, Department = item2.PersonInfo.Department
}
};
AllModel.Add(TempModel);
}
foreach (var item3 in Temp3)
{
FeeBillModelRef TempModel = new FeeBillModelRef()
{
BillNo = item3.BillNo, PageName = "借款单", Creator = item3.Creator, Owner = item3.Owner, TotalMoney = item3.TotalMoney, StringTime = item3.CreateTime.ToString("yyyy-MM-dd"), CreateTime = item3.CreateTime, PersonInfo = new PersonInfo()
{
DepartmentCode = item3.PersonInfo.DepartmentCode, Department = item3.PersonInfo.Department
}
};
AllModel.Add(TempModel);
}
foreach (var item4 in Temp4)
{
FeeBillModelRef TempModel = new FeeBillModelRef()
{
BillNo = item4.BillNo, PageName = item4.RefundType.ToUpper() == "CASH" ? "现金还款" : "费用单还款", Creator = item4.Creator, Owner = item4.Owner, TotalMoney = item4.RealRefundMoney, StringTime = item4.CreateTime.ToString("yyyy-MM-dd"), CreateTime = item4.CreateTime, PersonInfo = new PersonInfo()
{
DepartmentCode = item4.PersonInfo.DepartmentCode, Department = item4.PersonInfo.Department
}
};
AllModel.Add(TempModel);
}
switch (billType)
{
case 0:
break;
case 1:
AllModel.RemoveAll(c => c.PageName.Contains("付款") || c.PageName.Contains("借款") || c.PageName.Contains("还款"));
break;
case 2:
AllModel.RemoveAll(c => c.PageName.Contains("费用报销") || c.PageName.Contains("借款") || c.PageName.Contains("还款"));
break;
case 3:
AllModel.RemoveAll(c => c.PageName.Contains("付款") || c.PageName.Contains("费用报销") || c.PageName.Contains("还款"));
break;
case 4:
AllModel.RemoveAll(c => c.PageName.Contains("付款") || c.PageName.Contains("借款") || c.PageName.Contains("费用报销"));
break;
default:
break;
}
if (AllModel.Count > 0)
{
var temp = TransformData(AllModel, 0);
MemoryCachingClient M = new MemoryCachingClient();
M.Remove(employee.EmployeeNo);
M.Add(employee.EmployeeNo, temp);
return(JsonSerializeHelper.SerializeToJson(temp));
}
return("{}");
}
object data = GetApprovalData(billType, Time, employee.EmployeeNo, departmentID.ToString(), StartTime, EndTime);
if (data != null)
{
var temp = TransformData(data, billType);
MemoryCachingClient M = new MemoryCachingClient();
M.Remove(employee.EmployeeNo);
M.Add(employee.EmployeeNo, temp);
return(JsonSerializeHelper.SerializeToJson(temp));
}
return("{}");
//ReportHelper helper = new ReportHelper();
//DataTable dt = helper.GetDataTable(@"select ID,NO,NAME,SHOPCODE,CREATOR,CHANGER,AVAILABLE,LEAVE,PASSWORD,DEPID from Employee where (DEPID=" + departmentID + " or 0=" + departmentID + ") and length(NO)>4 and ROWNUM<100");
//foreach (DataColumn item in dt.Columns)
//{
// if (item.ColumnName == "NAME")
// {
// item.ColumnName = "姓名";
// }
// if (item.ColumnName == "NO")
// {
// item.ColumnName = "工号";
// }
//}
//var temp = ReportHelper.ConvertDataTable(dt);
//return JsonSerializeHelper.SerializeToJson(temp);
}
