/// <summary>
/// 从数据库获取用户名和密码
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public DataTable GetInfo(Entity entity)
{
string sql = "select PassWord,MemberName from Member where MemberName='" + entity.MbrName + "'";
try
{
db.Open();
dt = db.GetDataTableWithSQL(sql.ToString());
}
catch
{
dt = null;
}
finally {
db.Close();
}
return(dt);
}
public DataTable GetSideBarInfo(string children)
{
string sql = " select * from Department where Id in (" + children + ")";
try
{
db.Open();
dt = db.GetDataTableWithSQL(sql.ToString());
}
catch
{
dt = null;
}
finally
{
db.Close();
}
return(dt);
}
/// <summary>
/// 初始化现金推算表
/// </summary>
/// <returns></returns>
public DataTable InitFixedAssets(string mname, string equipmentname)
{
string sql = "select Id,DeptName,EquipmentName,isnull(EquipmentSpec,' ') as EquipmentSpec,isnull(EquipmentRemarks,' ') as EquipmentRemarks,UnitName,PurchaseDate,PurchaseNum,PurchasePrice,PurchaseMoney,ProductValue,DepreciationTime,DepreciationYear,DepreciationMonth,MDMoney,DepreciationEndTime,Is_end from T_FixedAssets where MemberName='" + mname + "' and EquipmentName like '%" + equipmentname.Trim() + "%'";
try
{
db.Open();
dt = db.GetDataTableWithSQL(sql.ToString());
}
catch
{
dt = null;
}
finally
{
db.Close();
}
return(dt);
}
/// <summary>
/// 初始化关键指标追赶表
/// </summary>
/// <returns></returns>
public DataTable InitKey(string mname, string year, string year2)
{
//string sql = "select Id,DeptCode,Isedit,cYear,cMonth,LastYear_ActualSales,LastYear_PlanSales,LastYear_CompletionRatio,cYear_ActualSalesP,cYear_PlanSalesP,cYear_CompletionRatioP,cYear_ActualSalesN,cYear_PlanSalesN,cYear_CompletionRatioN,StageTarget,cYear_Plan,PlanDifference,PlanBz,cYear_Now,NowDifference,NowBz,Sketch from Key_indicators where Operators='" + mname + "' and cYear='" + year + "'";
//string sql = "select Id,DeptCode,Isedit,cYear,cMonth,LastYear_ActualSales,LastYear_PlanSales,LastYear_CompletionRatio,cYear_ActualSalesP,cYear_PlanSalesP,cYear_CompletionRatioP,cYear_ActualSalesN,cYear_PlanSalesN,cYear_CompletionRatioN,StageTarget,cYear_Plan,PlanDifference,isnull(PlanBz,'') as PlanBz,cYear_Now,NowDifference,isnull(NowBz,'') as NowBz,isnull(Sketch,'') as Sketch from T_Key_indicators where Operators='" + mname + "' and cYear='" + year + "'";
// string sql = "select T1.*,T2.* from(select Id Id1,DeptCode DeptCode1,Isedit Isedit1,cYear cYear1,cMonth cMonth1 ,cYear_ActualSalesPlan cYear_ActualSalesPlan1,cYear_ActualSalesReal cYear_ActualSalesReal1,cYear_PlanSalesPlan cYear_PlanSalesPlan1,cYear_PlanSalesReal cYear_PlanSalesReal1,cYear_CompletionRatioPlan cYear_CompletionRatioPlan1,cYear_CompletionRatioReal cYear_CompletionRatioReal1,Sketch Sketch1 from T_Key_indicators where Operators='" + mname + "' and cYear='" + year + "') t1,(select Id Id2,DeptCode DeptCode2,Isedit Isedit2,cYear cYear2,cMonth cMonth2,cYear_ActualSalesPlan cYear_ActualSalesPlan2,cYear_ActualSalesReal cYear_ActualSalesReal2,cYear_PlanSalesPlan cYear_PlanSalesPlan2,cYear_PlanSalesReal cYear_PlanSalesReal2,cYear_CompletionRatioPlan cYear_CompletionRatioPlan2,cYear_CompletionRatioReal cYear_CompletionRatioReal2,Sketch Sketch2 from T_Key_indicators where Operators='" + mname + "' and cYear='" + year2 + "') t2 where t1.cMonth1=t2.cMonth2";
string sql = "select T2.Id2 as Id,T2.DeptCode2 as DeptCode,T2.Isedit2 as Isedit,T2.Isok2 as Isok,T2.cMonth2 as cMonth,T2.cYear_target2 as cYear_target,T1.cYear1,T1.cYear_ActualSalesPlan1,T1.cYear_ActualSalesReal1,T1.cYear_PlanSalesPlan1,T1.cYear_PlanSalesReal1,T1.cYear_CompletionRatioPlan1,T1.cYear_CompletionRatioReal1,T2.cYear2,T2.cYear_ActualSalesPlan2,T2.cYear_ActualSalesReal2,T2.cYear_PlanSalesPlan2,T2.cYear_PlanSalesReal2,T2.cYear_CompletionRatioPlan2,T2.cYear_CompletionRatioReal2,isnull(T2.Sketch2,'-') as Sketch from(select Id Id1,DeptCode DeptCode1,Isedit Isedit1,Isok Isok1,cYear cYear1,cMonth cMonth1 ,cYear_ActualSalesPlan cYear_ActualSalesPlan1,cYear_ActualSalesReal cYear_ActualSalesReal1,cYear_PlanSalesPlan cYear_PlanSalesPlan1,cYear_PlanSalesReal cYear_PlanSalesReal1,cYear_CompletionRatioPlan cYear_CompletionRatioPlan1,cYear_CompletionRatioReal cYear_CompletionRatioReal1,Sketch Sketch1,cYear_target cYear_target1 from T_Key_indicators where Operators='" + mname + "' and cYear='" + year + "') t1,(select Id Id2,DeptCode DeptCode2,Isedit Isedit2,Isok Isok2,cYear cYear2,cMonth cMonth2,cYear_ActualSalesPlan cYear_ActualSalesPlan2,cYear_ActualSalesReal cYear_ActualSalesReal2,cYear_PlanSalesPlan cYear_PlanSalesPlan2,cYear_PlanSalesReal cYear_PlanSalesReal2,cYear_CompletionRatioPlan cYear_CompletionRatioPlan2,cYear_CompletionRatioReal cYear_CompletionRatioReal2,Sketch Sketch2,cYear_target cYear_target2 from T_Key_indicators where Operators='" + mname + "' and cYear='" + year2 + "') t2 where t1.cMonth1=t2.cMonth2";
try
{
db.Open();
dt = db.GetDataTableWithSQL(sql.ToString());
}
catch
{
dt = null;
}
finally
{
db.Close();
}
return(dt);
}
/// <summary>
/// 初始化现金推算表
/// </summary>
/// <returns></returns>
public DataTable InitWages(string mname, string year)
{
string sql = "select Id,WagesClass,WagesName,WagesBz,cYear,JanPlan,FebPlan,MarPlan,AprPlan,MayPlan,JunPlan,JulPlan,AugPlan,SepPlan,OctPlan,NovPlan,DecPlan,Isedit,Isok from T_Wages where Operators='" + mname + "' and cYear='" + year + "'";
try
{
db.Open();
dt = db.GetDataTableWithSQL(sql.ToString());
}
catch
{
dt = null;
}
finally
{
db.Close();
}
return(dt);
}
/// <summary>
/// 获取咖啡追赶表信息
/// </summary>
/// <returns></returns>
public DataTable GetCafeChaseInfoOneYear(ListCafeEntity model)
{
string strHtml = "";
DataTable dt = new DataTable();
if (model.YearS == 0)
{
strHtml = " and cYear=datename(yyyy,getdate()) ";
}
else
{
strHtml = " and cYear=" + model.YearS;
}
string strSql = "SELECT Id,LaAllYear,LastMonthPlan,Adjust,dbo.GetRollYear(cYear,TheMonth) RollYear,StageGoal,OriPlan,Balance1,Balance2," +
"dbo.GetBalance1(Operators,cYear,TheMonth) BType1,Adjusted,dbo.GetBalance2(Operators,cYear,TheMonth) BType2," +
"Measure,right('00'+convert(varchar(5),TheMonth),2) TheMonth,cYear,MONTH(getdate()) NowMonth,datename(yyyy,getdate()) NowYear,cYear_Target, " +
"(select sum(LaAllYear) FROM T_CafeChase where Operators='" + model.UserCode.Trim() + "' " + strHtml.Trim() + ") sum1, " +
"(select sum(LastMonthPlan) FROM T_CafeChase where Operators='" + model.UserCode.Trim() + "' " + strHtml.Trim() + ") sum2, " +
"(select sum(Adjust) FROM T_CafeChase where Operators='" + model.UserCode.Trim() + "' " + strHtml.Trim() + ") sum3 " +
"FROM T_CafeChase where Operators='" + model.UserCode.Trim() + "' " + strHtml.Trim();
try
{
db.Open();
dt = db.GetDataTableWithSQL(strSql);
}
catch
{
dt = null;
}
finally
{
db.Close();
}
return(dt);
}
/// <summary>
/// 获取现金推算表信息
/// </summary>
/// <returns></returns>
public DataTable GetCashInfoOneYear(ListCafeEntity model)
{
string strHtml = " from T_Cash t1,T_Items t2 where t1.ItemsId=t2.ItemsId and t1.Operators='" + model.UserCode.Trim() + "' and t2.ItemsType=28 and t2.IsVisible=1 ";
if (model.YearS == 0)
{
strHtml += " and cYear=datename(yyyy,getdate()) ";
}
else
{
strHtml += " and cYear=" + model.YearS;
}
DataTable dt = new DataTable();
string strSql = "select t1.Id,t1.ItemsId,ItemsName,cYear,MONTH(getdate()) NowMonth,datename(yyyy,getdate()) NowYear," +
"JanPlan,FebPlan,MarPlan,AprPlan,MayPlan,JunPlan,JulPlan,AugPlan,SepPlan,OctPlan,NovPlan,DecPlan " + strHtml.Trim();
try
{
db.Open();
dt = db.GetDataTableWithSQL(strSql);
}
catch
{
dt = null;
}
finally
{
db.Close();
}
return(dt);
}
/// <summary>
/// 新增独立单位
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Add_Department(DepartmentEntity model)
{
SqlParameter[] parameters =
{
new SqlParameter("vmembername", SqlDbType.VarChar, 30),
new SqlParameter("vpassword", SqlDbType.VarChar, 50),
new SqlParameter("vdeptcode", SqlDbType.VarChar, 20),
new SqlParameter("vdeptname", SqlDbType.VarChar, 100),
new SqlParameter("vmanager", SqlDbType.VarChar, 30),
new SqlParameter("vtel", SqlDbType.VarChar, 40),
new SqlParameter("vposition", SqlDbType.VarChar, 40),
new SqlParameter("vmjordomo_id", SqlDbType.Int, 4),
new SqlParameter("vgrade", SqlDbType.Int, 4),
new SqlParameter("vauthority", SqlDbType.Int, 4)
};
parameters[0].Value = model.MemberName;
parameters[1].Value = model.PassWord;
parameters[2].Value = model.DeptCode;
parameters[3].Value = model.DeptName;
parameters[4].Value = model.Manager;
parameters[5].Value = model.Tel;
parameters[6].Value = model.Position;
parameters[7].Value = model.Mjordomo_id;
parameters[8].Value = model.Grade;
parameters[9].Value = model.Authority;
string commandText = "proc_AddDepartment";
int result = 0;
try
{
db.Open();
string strHtml0 = "select 1 from T_Department where MemberName='" + model.MemberName + "' ";
DataTable dt0 = new DataTable();
dt0 = db.GetDataTableWithSQL(strHtml0);
string strHtml = "select 1 from T_Department where DeptCode='" + model.DeptCode + "' and Grade=0";
DataTable dt = new DataTable();
dt = db.GetDataTableWithSQL(strHtml);
if (dt0.Rows.Count > 0)
{
result = 3;
}
else if (dt.Rows.Count > 0)
{
result = 2;
}
else
{
db.ExecuteNonQuery(commandText, CommandType.StoredProcedure, parameters);
result = 1;
}
}
catch //(InvalidOperationException ex)
{
result = 0;
}
finally
{
db.Close();
}
return(result);
}
/// <summary>
/// 获取预算表信息
/// </summary>
/// <returns></returns>
public DataTable GetBudgetInfoOneYear(ListBudgetEntity model)
{
string strHtml = " from T_Budget t1,T_Items t2 where t1.ItemsId=t2.ItemsId and t1.Operators='" + model.UserCode.Trim() + "' and t2.ItemsType=18 and t2.IsVisible=1 ";
if (model.YearS == 0)
{
strHtml += " and cYear=datename(yyyy,getdate()) ";
}
else
{
strHtml += " and cYear=" + model.YearS;
}
DataTable dt = new DataTable();
string strSql = "select (isJanPlan+isJanReal+isFebPlan+isFebReal+isMarPlan+isMarReal+isAprPlan+isAprReal+isMayPlan+isMayReal+isJunPlan+isJunReal+isJulPlan+isJulReal+isAugPlan+isAugReal+isSepPlan+isSepReal+isOctPlan+isOctReal+isNovPlan+isNovReal+isDecPlan+isDecReal) isCount," +
"(JanPlan+JanReal+FebPlan+FebReal+MarPlan+MarReal+AprPlan+AprReal+MayPlan+MayReal+JunPlan+JunReal+JulPlan+JulReal+AugPlan+AugReal+SepPlan+SepReal+OctPlan+OctReal+NovPlan+NovReal+DecPlan+DecReal) allCount," +
"t1.Id,t1.ItemsId,ItemsName,cYear,MONTH(getdate()) NowMonth,datename(yyyy,getdate()) NowYear,dbo.GetRollYearCount(" + model.YearS + ",'" + model.UserCode.Trim() + "',t1.ItemsId) RollYear,dbo.GetRollYearCount(" + model.YearS + ",'" + model.UserCode.Trim() + "',t1.ItemsId)/12 MonthAve," +
"JanPlan,JanReal,FebPlan,FebReal,MarPlan,MarReal,AprPlan,AprReal,MayPlan,MayReal,JunPlan,JunReal,JulPlan,JulReal,AugPlan,AugReal,SepPlan,SepReal,OctPlan,OctReal,NovPlan,NovReal,DecPlan,DecReal," +
"isJanPlan,isJanReal,isFebPlan,isFebReal,isMarPlan,isMarReal,isAprPlan,isAprReal,isMayPlan,isMayReal,isJunPlan,isJunReal,isJulPlan,isJulReal,isAugPlan,isAugReal,isSepPlan,isSepReal,isOctPlan,isOctReal,isNovPlan,isNovReal,isDecPlan,isDecReal " + strHtml.Trim();
try
{
db.Open();
dt = db.GetDataTableWithSQL(strSql);
}
catch
{
dt = null;
}
finally
{
db.Close();
}
return(dt);
}