public static decimal GetMonauditPay(string YearMonth)
{
decimal t = 0;
string sqlStr =
"select sum(Armon) as armon from dbo.BG_ApplyReimbur where Convert(varchar(7),ARTime,120)='{0}' and ARListSta='审核通过'";
sqlStr = string.Format(sqlStr, YearMonth);
try
{
t = ParToDecimal.ParToDel(DBUnity.ExecuteScalar(CommandType.Text, sqlStr, null));
if (t <= 0)
{
t = 0;
}
}
catch (System.Exception ex)
{
t = 0;
}
return(t);
}
public static DataTable GetAllBG_CaliberMon(List <int> listt, int year)
{
DataTable dt = null;
try
{
string sql = "";
if (listt.Count > 0)
{
if (listt.Count == 1)
{
sql = string.Format("select * from BG_Caliber left join (select * from BG_Amount where DepID={0} and BGAMYear={1}) as a On CBID =CaliberID left join (select[BGAMMon] as [BGAMLastMon],CBID from [dbo].[BG_Amount] where BGAMYear={1}-1 and DepID={0}) as b on b.CBID =CaliberID", listt[0], year);
}
else
{
string depidselect = "";
for (int i = 0; i < listt.Count; i++)
{
depidselect += listt[i] + ",";
}
depidselect = depidselect.TrimEnd(',');
sql = string.Format("select * from (select * from BG_Caliber left join (select CBID as CBIDa ,sum(BGAMMon) as BGAMMon ,sum(BGAMIncome) as BGAMIncome from BG_Amount where BGAMYear={0} and DepID in ({2}) group by CBID) as a{1} On a{1}.CBIDa =CaliberID left join (select CBID as CBIDb,sum([BGAMMon]) as [BGAMLastMon],CBID from [dbo].[BG_Amount] where BGAMYear={0}-1 and DepID in ({2}) group by CBID) as b{1} on b{1}.CBIDb =CaliberID ) as c{1} ", year, "i", depidselect);
for (int i = 0; i < listt.Count; i++)
{
sql += " left join ";
sql += string.Format(" (select * from BG_Amount where DepID={0} and BGAMYear={1}) as a{2} On a{2}.CBID =CaliberID left join (select[BGAMMon] as [BGAMLastMon],CBID from [dbo].[BG_Amount] where BGAMYear={1}-1 and DepID={0}) as b{2} on b{2}.CBID =CaliberID", listt[i], year, i);
}
}
}
sql += " order by CaliberID ";
dt = DBUnity.AdapterToTab(sql);
}
catch
{
dt = null;
}
return(dt);
}
public static BG_BudgetAllocation GetMonDT(int year, int PIID, int depID)
{
string sql = "SELECT * FROM BG_BudgetAllocation WHERE PIID = @PIID and DepID = @DepID and BAAYear=@BAAYear";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@DepID", depID),
new SqlParameter("@PIID", PIID),
new SqlParameter("@BAAYear", year)
};
DataTable dt = DBUnity.AdapterToTab(sql, para);
if (dt.Rows.Count > 0)
{
BG_BudgetAllocation bG_BudgetAllocation = new BG_BudgetAllocation();
bG_BudgetAllocation.BAAID = dt.Rows[0]["BAAID"] == DBNull.Value ? 0 : (int)dt.Rows[0]["BAAID"];
bG_BudgetAllocation.DepID = dt.Rows[0]["DepID"] == DBNull.Value ? 0 : (int)dt.Rows[0]["DepID"];
bG_BudgetAllocation.PIID = dt.Rows[0]["PIID"] == DBNull.Value ? 0 : (int)dt.Rows[0]["PIID"];
bG_BudgetAllocation.BAAMon = dt.Rows[0]["BAAMon"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["BAAMon"];
bG_BudgetAllocation.SuppMon = dt.Rows[0]["SuppMon"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["SuppMon"];
bG_BudgetAllocation.BAAYear = dt.Rows[0]["BAAYear"] == DBNull.Value ? 0 : (int)dt.Rows[0]["BAAYear"];
return(bG_BudgetAllocation);
}
else
{
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static bool ISApplyBackMon(string ARTime, int DepID)
{
bool flag = false;
try
{
string str = string.Format("select sum(ARMon) as ARMon from BG_ApplyReimbur where ppid={0} and DepId={1} and convert(varchar(7),ARTime,120)='{2}' and ARListSta='退回'", DepID, ARTime);
int t = common.IntSafeConvert(DBUnity.ExecuteScalar(CommandType.Text, str, null));
if (t > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch
{
flag = false;
}
return(flag);
}
public static BG_Amount GetBG_AmountByYear(int CBID, int Year, int depid)
{
string sql = string.Format("select * from BG_Amount where CBID=@CBID and BGAMYear=@BGAMYear and DepID=@DepID", CBID, Year, depid);
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@BGAMYear", Year),
new SqlParameter("@CBID", CBID),
new SqlParameter("@DepID", depid)
};
DataTable dt = DBUnity.AdapterToTab(sql, para);
if (dt.Rows.Count > 0)
{
BG_Amount bG_Amount = new BG_Amount();
bG_Amount.BGAMID = dt.Rows[0]["BGAMID"] == DBNull.Value ? 0 : (int)dt.Rows[0]["BGAMID"];
bG_Amount.BGAMMon = dt.Rows[0]["BGAMMon"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["BGAMMon"];
bG_Amount.BGAMYear = dt.Rows[0]["BGAMYear"] == DBNull.Value ? 0 : (int)dt.Rows[0]["BGAMYear"];
bG_Amount.DepID = dt.Rows[0]["DepID"] == DBNull.Value ? 0 : (int)dt.Rows[0]["DepID"];
bG_Amount.CBID = dt.Rows[0]["CBID"] == DBNull.Value ? 0 : (int)dt.Rows[0]["CBID"];
return(bG_Amount);
}
else
{
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static BG_Cashier GetBG_CashierByCashierid(int cashierid)
{
string sql = "SELECT * FROM BG_Cashier WHERE Cashierid = @Cashierid";
try
{
SqlParameter para = new SqlParameter("@Cashierid", cashierid);
DataTable dt = DBUnity.AdapterToTab(sql, para);
if (dt.Rows.Count > 0)
{
BG_Cashier bG_Cashier = new BG_Cashier();
bG_Cashier.Cashierid = dt.Rows[0]["Cashierid"] == DBNull.Value ? 0 : (int)dt.Rows[0]["Cashierid"];
bG_Cashier.Piid = dt.Rows[0]["Piid"] == DBNull.Value ? 0 : (int)dt.Rows[0]["Piid"];
bG_Cashier.BgMon = dt.Rows[0]["BgMon"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["BgMon"];
bG_Cashier.CZMon = dt.Rows[0]["CZMon"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["CZMon"];
bG_Cashier.QTMon = dt.Rows[0]["QTMon"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["QTMon"];
bG_Cashier.BQMon = dt.Rows[0]["BQMon"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["BQMon"];
bG_Cashier.DepID = dt.Rows[0]["DepID"] == DBNull.Value ? 0 : (int)dt.Rows[0]["DepID"];
bG_Cashier.CTime = dt.Rows[0]["CTime"] == DBNull.Value ? DateTime.MinValue : (DateTime)dt.Rows[0]["CTime"];
bG_Cashier.CashierBalance = dt.Rows[0]["CashierBalance"] == DBNull.Value ? 0 : (decimal)dt.Rows[0]["CashierBalance"];
return(bG_Cashier);
}
else
{
return(null);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw e;
}
}
public static bool DelByYear(string CurrentYear)
{
bool flag = false;
int t = 0;
string sql = string.Format("delete from BG_BudgetAllocation where BAAYear='{0}'", CurrentYear);
try
{
t = DBUnity.ExecuteNonQuery(CommandType.Text, sql, null);
if (t > 0)
{
flag = true;
}
else
{
flag = false;
}
}
catch (Exception)
{
flag = false;
}
return(flag);
}
private void RpDataBind()
{
string sqlstr = "select * from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=0";
DataTable dt = DBUnity.AdapterToTab(sqlstr);
dt.Columns.Add("Total");
name = "<th>部门名</th> <th>上年余额(元)</th> <th>本期金额(元)</th> ";
dt.Columns.Add("aaaa");
int sum = 0;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string JL = dt.Rows[i]["LSJL"].ToString();
if (JL != null)
{
int sum1 = JL.TrimEnd('@').Split('@').Length;
if (sum1 > sum)
{
sum = sum1;
}
}
}
}
for (int i = 0; i < dt.Rows.Count; i++)
{
decimal total = 0;
string JL = dt.Rows[i]["LSJL"].ToString();
if (JL != "" && JL != null)
{
int su = JL.TrimEnd('@').Split('@').Length;
for (int a = 0; a < sum - su; a++)
{
JL += "#@";
}
for (int j = 0; j < JL.TrimEnd('@').Split('@').Length; j++)
{
string content = JL.TrimEnd('@').Split('@')[j].ToString();
string[] str = content.Split('#');
if (str.Length != 1)
{
dt.Rows[i]["aaaa"] += "<td style=\"color:red;\">" + str[0] + "<br/>" + str[1] + "</td>";
total += ParseUtil.ToDecimal(str[0], 0);
}
else
{
for (int k = 0; k < sum; k++)
{
dt.Rows[i]["aaaa"] += "<td> </td>";
}
}
}
}
else
{
for (int x = 0; x < sum; x++)
{
dt.Rows[i]["aaaa"] += "<td> </td>";
}
}
total += ParseUtil.ToDecimal(dt.Rows[i]["SQJE"].ToString(), 0) + ParseUtil.ToDecimal(dt.Rows[i]["BQJE"].ToString(), 0);
dt.Rows[i]["Total"] = total;
dt.Rows[i]["KYJE"] = total;
}
for (int i = 0; i < sum; i++)
{
name = name + " <th>" + "追加金额" + (i + 1) + "</th>";
}
name = name + "<th>合计</th> ";
repBudMonYear.DataSource = dt;
repBudMonYear.DataBind();
}
protected void btnIn_Click(object sender, EventArgs e)
{
try
{
string fileName1 = this.fileSelect.PostedFile.FileName;
string name = this.GetFileName(fileName1);
string path = base.Server.MapPath("~/files") + @"\" + name;
this.fileSelect.SaveAs(path);
hidpath.Value = path;
string sqlstr = "select * from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=0";
DataTable dtsql = DBUnity.AdapterToTab(sqlstr);
if (dtsql.Rows.Count > 0)
{
var s = "if(confirm('点击确定追加金额！点击取消覆盖上一次的金额')){0};else {1};";
ScriptManager.RegisterStartupScript(this, typeof(cashier_keshiyewufei), "",
string.Format(s, this.ClientScript.GetPostBackEventReference(this, "是"), this.ClientScript.GetPostBackEventReference(this, "否")),
true);
}
else
{
DataTable dt = ImportExcelFile(path);
string sqlstr11 = "select * from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=0";
DataTable dtOld = DBUnity.AdapterToTab(sqlstr11);
DataTable dtNew = new DataTable();
dt.Columns.Add("aaaa");
for (int i = 0; i < dt.Rows.Count; i++)
{
RM_keshiyewufei rk = new RM_keshiyewufei();
rk.Depname = dt.Rows[i]["部门名"].ToString();
rk.SQJE = ParseUtil.ToDecimal(dt.Rows[i]["上年余额"].ToString(), 0);
rk.BQJE = ParseUtil.ToDecimal(dt.Rows[i]["本期金额"].ToString(), 0);
rk.ZJJE = ParseUtil.ToDecimal(dt.Rows[i]["追加金额"].ToString(), 0);
rk.LSJL = rk.ZJJE.ToString() + "#" + DateTime.Now.ToString("yyyy-MM-dd") + "@";
string aa = rk.LSJL.ToString();
decimal zj = 0;
for (int j = 0; j < aa.Trim('@').Split('@').Length; j++)
{
zj += ParseUtil.ToDecimal(aa.Trim('@').Split('@')[j].Split('#')[0], 0);
}
rk.KYJE = zj + rk.BQJE + rk.SQJE;
rk.Remark = 0;
rk.Remark1 = DateTime.Now.Year.ToString();
RM_keshiyewufeiManager.AddRM_keshiyewufei(rk);
}
MessageBox.Show(this, "导入成功");
RpDataBind();
}
}
catch
{
}
}
public void RaisePostBackEvent(string eventArgument)
{
DataTable dt = new DataTable();
dt = ImportExcelFile(hidpath.Value);
switch (eventArgument)
{
case "是":
decimal je = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
je += ParseUtil.ToDecimal(dt.Rows[i]["追加金额"].ToString(), 0);
}
string sqlstr11 = "select * from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=0";
DataTable dtOld = DBUnity.AdapterToTab(sqlstr11);
DataTable dtNew = new DataTable();
dt.Columns.Add("aaaa");
for (int i = 0; i < dt.Rows.Count; i++)
{
string depname = dt.Rows[i]["部门名"].ToString();
for (int j = 0; j < dtOld.Rows.Count; j++)
{
if (dtOld.Rows[j]["Depname"].ToString() == depname)
{
string zj = dtOld.Rows[j]["LSJL"].ToString();
if (je > 0)
{
zj += dt.Rows[i]["追加金额"].ToString() + "#" + DateTime.Now.ToString("yyyy-MM-dd") + "@";
}
dt.Rows[i]["aaaa"] = zj;
}
else
{
RM_keshiyewufei rk = new RM_keshiyewufei();
rk.Depname = dt.Rows[i]["部门名"].ToString();
rk.SQJE = ParseUtil.ToDecimal(dt.Rows[i]["上年余额"].ToString(), 0);
rk.BQJE = ParseUtil.ToDecimal(dt.Rows[i]["本期金额"].ToString(), 0);
rk.ZJJE = ParseUtil.ToDecimal(dt.Rows[i]["追加金额"].ToString(), 0);
string aa = dt.Rows[i]["aaaa"].ToString();
rk.LSJL = rk.ZJJE + "#" + DateTime.Now.ToString("yyyy-MM-dd") + "@";
rk.KYJE = rk.ZJJE + rk.BQJE + rk.SQJE;
rk.Remark = 0;
rk.Remark1 = DateTime.Now.Year.ToString();
RM_keshiyewufeiManager.AddRM_keshiyewufei(rk);
}
}
}
string strsql = "delete from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=0";
DBUnity.ExecuteNonQuery(CommandType.Text, strsql, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
RM_keshiyewufei rk = new RM_keshiyewufei();
rk.Depname = dt.Rows[i]["部门名"].ToString();
rk.SQJE = ParseUtil.ToDecimal(dt.Rows[i]["上年余额"].ToString(), 0);
rk.BQJE = ParseUtil.ToDecimal(dt.Rows[i]["本期金额"].ToString(), 0);
rk.ZJJE = ParseUtil.ToDecimal(dt.Rows[i]["追加金额"].ToString(), 0);
rk.LSJL = dt.Rows[i]["aaaa"].ToString();
string aa = dt.Rows[i]["aaaa"].ToString();
decimal zj = 0;
for (int j = 0; j < aa.Trim('@').Split('@').Length; j++)
{
zj += ParseUtil.ToDecimal(aa.Trim('@').Split('@')[j].Split('#')[0], 0);
}
rk.KYJE = zj + rk.BQJE + rk.SQJE;
rk.Remark = 0;
rk.Remark1 = DateTime.Now.Year.ToString();
RM_keshiyewufeiManager.AddRM_keshiyewufei(rk);
}
MessageBox.Show(this, "导入成功");
RpDataBind();
break;
case "否":
string sqlstr = "select * from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=0";
DataTable dtUpd = DBUnity.AdapterToTab(sqlstr);
dt.Columns.Add("newje");
for (int i = 0; i < dt.Rows.Count; i++)
{
string depname = dt.Rows[i]["部门名"].ToString();
for (int j = 0; j < dtUpd.Rows.Count; j++)
{
if (dtUpd.Rows[j]["Depname"].ToString() == depname)
{
string zj = dtUpd.Rows[j]["LSJL"].ToString().TrimEnd('@');
if (zj.Contains("@"))
{
zj = zj.Substring(0, zj.LastIndexOf('@'));
zj += dt.Rows[i]["追加金额"].ToString() + "#" + DateTime.Now.ToString("yyyy-MM-dd") + "@";
}
else
{
zj = dt.Rows[i]["追加金额"].ToString() + "#" + DateTime.Now.ToString("yyyy-MM-dd") + "@";
}
dt.Rows[i]["newje"] = zj;
}
}
}
string strsqlde = "delete from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=0";
DBUnity.ExecuteNonQuery(CommandType.Text, strsqlde, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
RM_keshiyewufei rk = new RM_keshiyewufei();
rk.Depname = dt.Rows[i]["部门名"].ToString();
rk.SQJE = ParseUtil.ToDecimal(dt.Rows[i]["上年余额"].ToString(), 0);
rk.BQJE = ParseUtil.ToDecimal(dt.Rows[i]["本期金额"].ToString(), 0);
rk.ZJJE = ParseUtil.ToDecimal(dt.Rows[i]["追加金额"].ToString(), 0);
rk.LSJL = dt.Rows[i]["newje"].ToString();
string aa = dt.Rows[i]["newje"].ToString();
decimal zj = 0;
for (int j = 0; j < aa.Trim('@').Split('@').Length; j++)
{
zj += ParseUtil.ToDecimal(aa.Trim('@').Split('@')[j].Split('#')[0], 0);
}
rk.KYJE = zj + rk.BQJE + rk.SQJE;
rk.Remark = 0;
rk.Remark1 = DateTime.Now.Year.ToString();
RM_keshiyewufeiManager.AddRM_keshiyewufei(rk);
}
MessageBox.Show(this, "导入成功");
RpDataBind();
break;
}
}
//public void btnrestore(string name)
//{
// string tmp = AdName.Text;
// tmp = name;
// SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ToString());
// string dbFileName = "";
// dbFileName = tmp;
// SqlCommand command = new SqlCommand("use master ;ALTER DATABASE [BudgetNetDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;restore database @name from disk=@path WITH REPLACE;", connection);
// connection.Open();
// string path = "";
// path = Server.MapPath("~\\DataFile");
// path = path + "\\" + @dbFileName;
// command.Parameters.AddWithValue("@name", "BudgetNetDB");
// command.Parameters.AddWithValue("@path", path);
// command.ExecuteNonQuery();
// connection.Close();
// X.Msg.Alert("系统提示", "还原成功！").Show();
//}
public void btnrestore(string dbid)
{
string dbname = DBUnity.connectionString.Split(';')[1].Split('=')[1];
string tmp = BG_Unit_DbbakService.GetBG_Unit_DbbakByDbID(Common.common.IntSafeConvert(dbid)).DbName;
string connectionStringserver = ConfigurationManager.ConnectionStrings["ConnectionStringserver"].ToString();
string sql = " DECLARE @sql NVARCHAR (MAX)"
+ " DECLARE @sql1 NVARCHAR (MAX)"
+ " DECLARE @sql2 NVARCHAR (MAX)"
+ " DECLARE @i int"
+ " DECLARE @tabname NVARCHAR (MAX)"
+ " set @i=1"
+ " while @i<=(select count(tab.name) from {1}.dbo.SysObjects as tab where xtype='u' and name not like '%_Unit_%' )"
+ " begin"
+ " set @tabname=(select top(1) * from (select top(@i) tab.name from {1}.dbo.SysObjects as tab where xtype='u' and name not like"
+ " '%_Unit_%' order by name asc) as a order by name desc)"
+ " SET @sql1 = 'select * into {1}.dbo.'+@tabname+' from ' +'{0}.[dbo].'+@tabname "
+ " SET @sql2 = 'drop table {1}.[dbo].'+@tabname +' ' + @sql1 "
+ " SET @sql = ''if object_id(N'{1}.[dbo].'+@tabname+'',N'U') is not null EXEC(@sql2) else EXEC(@sql1) "
+ " EXEC(@sql); "
+ " set @i= @i+1 "
+ " end ";
sql = string.Format(sql, tmp, dbname);
int t = DBUnity.ExecuteNonQuery(CommandType.Text, sql, null);
if (t > 0)
{
FileStream aFile = new FileStream(Server.MapPath("~\\DataFile") + "\\" + "存储过程.txt", FileMode.Open, FileAccess.Read);
StreamReader sr = new StreamReader(aFile, Encoding.UTF8);
string sqlccgc = sr.ReadToEnd();
sr.Close();
aFile.Close();
int c = 0;
try
{
string serversql = string.Format("EXEC sp_dropserver 'JZDBServer' , 'droplogins' EXEC sp_addlinkedserver 'JZDBServer','','SQLNCLI','{0}' EXEC sp_addlinkedsrvlogin 'JZDBServer','false',NULL,'sa','{1}'", connectionStringserver.Split(';')[0].Split('=')[1], connectionStringserver.Split(';')[3].Split('=')[1]);
DBUnity.ExecuteNonQuery(CommandType.Text, serversql, null);
string[] arr = System.Text.RegularExpressions.Regex.Split(sqlccgc.Replace("\r\n"," "), "GO");
try
{
for (int n = 0; n < arr.Length; n++)
{
string strsql = arr[n];
if (strsql.Trim().Length > 1)
{
DBUnity.ExecuteNonQuery(CommandType.Text, strsql, null);
}
}
c++;
}
catch
{
c = 0;
}
}
catch (Exception ex)
{
if (ex.ToString().Contains("已存在"))
{
c++;
}
}
if (c > 0)
{
X.Msg.Alert("系统提示", "还原成功！").Show();
}
else
{
X.Msg.Alert("系统提示", "还原失败！").Show();
}
}
}
