/// <summary>
/// 获取发放记录信息
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void GETFFJLMODEL(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
int id = 0;
int.TryParse(P1, out id);
SZHL_XZ_JL xzjl = new SZHL_XZ_JLB().GetEntity(d => d.ID == id);
msg.Result = xzjl;
}
/// <summary>
/// 发放记录列表
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void GETFFJLLIST(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
int page = 0;
int pagecount = 8;
int.TryParse(context.Request("p") ?? "1", out page); //页码
int.TryParse(context.Request("pagecount") ?? "8", out pagecount); //页数
page = page == 0 ? 1 : page;
int recordCount = 0;
string strWhere = string.Format(" ComId={0} ", UserInfo.User.ComId, UserInfo.User.UserName);
string strContent = context.Request("Content") ?? "";
strContent = strContent.TrimEnd();
if (strContent != "")
{
strWhere += string.Format(" And ( title like '%{0}%' )", strContent);
}
var dt = new SZHL_XZ_JLB().Db.Queryable <SZHL_XZ_JL>().Where(strWhere).OrderBy(it => it.CRDate, OrderByType.Desc).ToPageList(page, pagecount, ref recordCount);
msg.Result = dt;
msg.Result1 = recordCount;
}
/// <summary>
/// 最近上传的4条工资单
/// </summary>
/// <param name="context"></param>
/// <param name="msg"></param>
/// <param name="P1"></param>
/// <param name="P2"></param>
/// <param name="UserInfo"></param>
public void GETGZTLAST(JObject context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
{
var dt = new SZHL_XZ_JLB().GetEntities(it => it.CRUser == UserInfo.User.UserName).OrderByDescending(d => d.CRDate).Take(4);
msg.Result = dt;
}
/// <summary>
/// 导入薪资单
/// </summary>
/// <param name="msg"></param>
/// <param name="stream"></param>
/// <param name="suffix"></param>
public void EXCELTOTABLEXZ(Msg_Result msg, Stream stream, string suffix)
{
try
{
string str2 = "";
DataTable dt = new DataTable();
ArrayList al1 = new ArrayList();
ArrayList al2 = new ArrayList();
string headrow = "2";//头部开始行下标
if (suffix == "xls" || suffix == "xlsx")
{
IWorkbook workbook = null;
if (suffix == "xlsx") // 2007版本
{
workbook = new XSSFWorkbook(stream);
}
else if (suffix == "xls") // 2003版本
{
workbook = new HSSFWorkbook(stream);
}
//获取excel的第一个sheet
ISheet sheet = workbook.GetSheetAt(0);
//获取sheet的第一行
IRow headerRow = sheet.GetRow(int.Parse(headrow));
IRow header0Row = sheet.GetRow(1);
//一行最后一个方格的编号 即总的列数
int cellCount = headerRow.LastCellNum;
//最后一列的标号 即总的行数
int rowCount = sheet.LastRowNum;
if (rowCount <= int.Parse(headrow))
{
msg.ErrorMsg = "文件中无数据! ";
}
else
{
CommonHelp ch = new CommonHelp();
string[] yz = { "姓名", "部门", "用户编码" };
//列名
for (int i = 0; i < cellCount; i++)
{
string strlm = string.Empty;
if (headerRow.GetCell(i).IsMergedCell)
{
strlm = sheet.GetRow(int.Parse(headrow) - 1).GetCell(i).ToString().Trim();
}
else
{
strlm = headerRow.GetCell(i).ToString().Trim();
}
if (string.IsNullOrWhiteSpace(strlm))
{
strlm = "第" + (i + 1) + "列";
}
dt.Columns.Add(strlm);//添加列名
}
#region 必填字段在文件中存不存在验证
foreach (var v in yz)
{
if (!dt.Columns.Contains(v))
{
if (string.IsNullOrEmpty(str2))
{
str2 = "当前导入的必填字段：【" + v + "】";
}
else
{
str2 = str2 + "、【" + v + "】";
}
}
}
if (!string.IsNullOrEmpty(str2))
{
str2 = str2 + " 在文件中不存在!<br>";
}
#endregion
for (int i = (sheet.FirstRowNum + int.Parse(headrow) + 1); i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool bl = false;
IRow row = sheet.GetRow(i);
for (int j = row.FirstCellNum; j < cellCount; j++)
{
string strsj = exportsheet(row.GetCell(j)).Trim();
if (strsj != "")
{
bl = true;
}
dr[j] = strsj;
}
if (bl)
{
dt.Rows.Add(dr);
}
}
int ykindex = 0;
for (int n = 0; n < dt.Columns.Count; n++)
{
if (header0Row.GetCell(n).ToString().Contains("应扣"))
{
ykindex = n;
break;
}
}
for (int n = 0; n < dt.Columns.Count; n++)
{
var name = dt.Columns[n].ColumnName;
if (n > 2 && n < ykindex - 1)
{
al1.Add(name);
}
//得跳过应发合计列
if (n > ykindex - 1 && n < dt.Columns.Count - 2)
{
al2.Add(name);
}
//需要跳过应扣合计和实发合计
}
dt.Columns.Add("YF", Type.GetType("System.Object"));
dt.Columns.Add("YK", Type.GetType("System.Object"));
foreach (DataRow dr in dt.Rows)
{
JObject obj1 = new JObject();
JObject obj2 = new JObject();
foreach (var str in al1)
{
obj1.Add(str.ToString(), dr[str.ToString()].ToString());
}
foreach (var str in al2)
{
obj2.Add(str.ToString(), dr[str.ToString()].ToString());
}
dr["YF"] = obj1;
dr["YK"] = obj2;
}
foreach (var str in al1)
{
dt.Columns.Remove(str.ToString());
}
foreach (var str in al2)
{
dt.Columns.Remove(str.ToString());
}
msg.Result = dt;
var jl = new SZHL_XZ_JLB().GetALLEntities().OrderByDescending(d => d.CRDate).Take(1);
msg.Result1 = jl;
msg.ErrorMsg = str2;
}
sheet = null;
workbook = null;
}
}
catch (Exception)
{
msg.ErrorMsg = "导入失败！";
}
}
