public void UpdateTable(string tableName, string data)
{
List <Period> list = JsonConvert.DeserializeObject <List <Period> >(data);
foreach (Period item in list)
{
PeriodBLL.Save(item);
}
;
//Common.UpdateTableVersion("CloudAsset.dbo.Period");
}
protected void btnDelete_Click(object sender, EventArgs e)
{
Int32 records = PeriodBLL.getInstance().delete(period_id);
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "confirmMessage", "$('#confirmMessage').modal('toggle');", true);
if (records > 0)
{
lblMessage.Text = "Tipo de periodo eliminado correctamente.";
}
loadData();
}
public ResponsePeriodPaidGet PeriodGet([FromBody] RequestPeriodPaidGet request)
{
try
{
PeriodBLL bll = new PeriodBLL();
return(bll.PeriodGet(request));
}
catch (Exception ex)
{
throw new HttpResponseException(
Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message));
}
}
protected void gvPeriod_RowEditing(object sender, GridViewEditEventArgs e)
{
unlockControls();
Int32 code = Convert.ToInt32(gvPeriod.Rows[e.NewEditIndex].Cells[0].Text);
Entities.Period oPeriod = PeriodBLL.getInstance().getPeriod(code);
txtCode.Text = oPeriod.code.ToString();
txtDescription.Text = oPeriod.name;
cboModality.SelectedValue = oPeriod.oPeriodType.code.ToString();
txtStartDate.Text = oPeriod.startDate.ToShortDateString();
txtFinishDate.Text = oPeriod.finalDate.ToShortDateString();
cboState.SelectedValue = oPeriod.state.ToString();
ScriptManager.RegisterStartupScript(this, this.GetType(), "redirect", "$('html, body').animate({ scrollTop: $('body').offset().top });", true);
}
protected void btnSave_Click(object sender, ImageClickEventArgs e)
{
Int32 records = 0;
if (validateData())
{
Entities.Period oPeriod = new Entities.Period();
Entities.PeriodType oPeriodType = new Entities.PeriodType();
oPeriod.code = Convert.ToInt32(txtCode.Text);
oPeriod.name = txtDescription.Text;
oPeriod.startDate = DateTime.Parse(txtStartDate.Text);
oPeriod.finalDate = DateTime.Parse(txtFinishDate.Text);
oPeriod.state = Convert.ToInt16(cboState.SelectedValue);
oPeriodType.code = Convert.ToInt32(cboModality.SelectedValue);
oPeriod.oPeriodType = oPeriodType;
if (PeriodBLL.getInstance().exists(oPeriod.code))
{
records = PeriodBLL.getInstance().modify(oPeriod);
}
else
{
records = PeriodBLL.getInstance().insert(oPeriod);
}
blockControls();
loadData();
if (records > 0)
{
lblMessage.Text = "Datos almacenados correactamente";
}
}
else
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "comboBox", "comboBox();", true);
}
}
public List <Period> GetList(TableObject table)
{
string CacheEnable = ConfigurationManager.AppSettings["CacheEnable"];
string CustomerLink = ConfigurationManager.AppSettings["CustomerLink"];
string key = CacheUtility.GetKey(table);
object obj = CacheUtility.Get(key);
if (obj != null)
{
return((List <Period>)obj);
}
string sqlStr = "";
string sqlAdd = "";
if (table.IsValid == 1)
{
sqlAdd = " AND (TotalCourseAmount>0 OR CommonCourseAmount>0 OR SpecialCourseAmount>0) ";
}
byte[] TimeStamp = new byte[8];
if (CacheUtility.GetCollectionKey(table.LastModified) == CacheUtility.GetCollectionKey(TimeStamp))
{
sqlStr = @"select * from [CloudAsset].[dbo].[Period] where LastModified>@LastModified and AssetID in
(
select AssetID from [CloudAsset].[dbo].[Asset] where [OwnerID]
in(
select CustomerID FROM " + CustomerLink + @"[CloudCustomer].[dbo].[CustomerSearch] ss where ss.XDSchoolID=@SchoolID " + sqlAdd + "))";
// AND (SS.TotalCourseAmount>0 OR SS.CommonCourseAmount>0 OR SS.SpecialCourseAmount>0)
}
else
{
sqlStr = @" select a.* from [CloudAsset].[dbo].[Period] a
join [dbo].[Asset] b on a.AssetID=b.AssetID where b.SubCompanyID=@BranchID and b.SchoolID=@SchoolID and a.LastModified>@LastModified";
}
List <SqlParameter> pms = new List <SqlParameter>();
pms.Add(new SqlParameter("BranchID", table.BranchID));
pms.Add(new SqlParameter("SchoolID", table.SchoolID));
pms.Add(new SqlParameter("LastModified", table.LastModified));
//if (!string.IsNullOrEmpty(table.StartDate))
//{
// sqlStr += " and AssetCreateDate>@StartDate";
// pms.Add(new SqlParameter("StartDate", table.StartDate));
//}
//if (!string.IsNullOrEmpty(table.EndDate))
//{
// sqlStr += " and AssetCreateDate<@EndDate";
// pms.Add(new SqlParameter("EndDate", table.EndDate));
//}
List <Period> list = PeriodBLL.Search(sqlStr, pms.ToArray());
if (list.Count > 0)
{
byte[] b = new byte[8];
if (CacheUtility.GetCollectionKey(table.LastModified) == CacheUtility.GetCollectionKey(b))
{
CacheUtility.Insert(key, list);
}
else
{
if (CacheEnable == "true")
{
CacheUtility.Insert(key, list);
}
}
}
return(list);
}
public ResponseJYGet JYGet(RequestJYGet request)
{
var res = new ResponseJYGet();
using (this.cnn = GetConnection())
{
var ts = cnn.BeginTransaction();
try
{
var user = this.UserInfoGet(request.Token, ts);
AccountSetBLL accountBLL = new AccountSetBLL(cnn);
var curPeriod = accountBLL.GetActivePeriod(request.AccountList ?? user.AccountId, ts);
curPeriod.Id = request.PeriodId;
//本月开票收入
//string sql = @"SELECT (sum(Money_Credit)-sum(Money_Debit)) as money
// FROM TKS_FAS_DocDetail where
// periodId=@PeriodId and subjectCode='5101'
string sql = @"SELECT CASE
WHEN SCredit_Debit = 0 THEN ABS(BWBDebitTotal - BWBCreditTotal)
ELSE ABS(BWBCreditTotal - BWBDebitTotal)
END AS money
FROM tks_fas_glBalance
WHERE periodId = @PeriodId
AND subjectCode = '5101'" ;
var kp = cnn.ExecuteScalar(sql, new { PeriodId = curPeriod.Id }, ts);
var kpMoney = decimal.Parse(kp == null?"0":kp.ToString());
//add by Hero.Zhang 小规模类型的账套，本季度开票收入
#region
decimal quarter_kpMoney = 0;
var data_Account = cnn.QueryFirstOrDefault <TKS_FAS_AccountInfo>(
@"select * from TKS_FAS_AccountInfo where Id=@Id",
new { Id = user.AccountId }, ts);
if (data_Account.AddedValueTaxType == 1)
{
var selPeriod = cnn.QueryFirstOrDefault <TKS_FAS_MonthPeriodInfo>(
@"select * from TKS_FAS_MonthPeriodInfo where Id=@Id",
new { Id = request.PeriodId }, ts);
var lstPeriod = cnn.Query <TKS_FAS_MonthPeriodInfo>(@"select * from TKS_FAS_MonthPeriodInfo where
AccountId=@AccountId and Year=@Year", new { AccountId = user.AccountId, Year = curPeriod.Year }, ts).ToList();
List <string> lstFirst = new List <string>();
List <string> lstSecond = new List <string>();
List <string> lstThird = new List <string>();
List <string> lstFour = new List <string>();
foreach (var item in lstPeriod)
{
if (item.Month <= 3)
{
lstFirst.Add(item.Id);
}
else if (item.Month > 3 && item.Month <= 6)
{
lstSecond.Add(item.Id);
}
else if (item.Month > 6 && item.Month <= 9)
{
lstThird.Add(item.Id);
}
else if (item.Month > 9 && item.Month <= 12)
{
lstFour.Add(item.Id);
}
}
var month = selPeriod.Month;
var Quarter = GetQuarterByMonth(month);
string sql_quarter = @"SELECT CASE
WHEN SCredit_Debit = 0 THEN ABS(BWBDebitTotal - BWBCreditTotal)
ELSE ABS(BWBCreditTotal - BWBDebitTotal)
END AS money
FROM tks_fas_glBalance
WHERE periodId = @PeriodId
AND subjectCode = '5101'" ;
if (Quarter == 1)
{
foreach (var item in lstFirst)
{
var kp_quarter = cnn.ExecuteScalar(sql_quarter, new { PeriodId = item }, ts);
var kpMoney_quarter = decimal.Parse(kp_quarter == null ? "0" : kp_quarter.ToString());
quarter_kpMoney += kpMoney_quarter;
}
}
if (Quarter == 2)
{
foreach (var item in lstSecond)
{
var kp_quarter = cnn.ExecuteScalar(sql_quarter, new { PeriodId = item }, ts);
var kpMoney_quarter = decimal.Parse(kp_quarter == null ? "0" : kp_quarter.ToString());
quarter_kpMoney += kpMoney_quarter;
}
}
if (Quarter == 3)
{
foreach (var item in lstThird)
{
var kp_quarter = cnn.ExecuteScalar(sql_quarter, new { PeriodId = item }, ts);
var kpMoney_quarter = decimal.Parse(kp_quarter == null ? "0" : kp_quarter.ToString());
quarter_kpMoney += kpMoney_quarter;
}
}
if (Quarter == 4)
{
foreach (var item in lstFour)
{
var kp_quarter = cnn.ExecuteScalar(sql_quarter, new { PeriodId = item }, ts);
var kpMoney_quarter = decimal.Parse(kp_quarter == null ? "0" : kp_quarter.ToString());
quarter_kpMoney += kpMoney_quarter;
}
}
}
#endregion
PeriodBLL periodBLL = new PeriodBLL(cnn);
//本年累计开票收入
decimal kpMoneyYear = 0;
sql = @"SELECT CASE
WHEN SCredit_Debit = 0 THEN ABS(BWBDebitTotal_Y - BWBCreditTotal_Y)
ELSE ABS(BWBCreditTotal_Y - BWBDebitTotal_Y)
END AS money
FROM tks_fas_glBalance
WHERE periodId = @PeriodId
AND subjectCode = '5101'" ;
var preKp = cnn.ExecuteScalar(sql, new { PeriodId = curPeriod.Id }, ts);
var preKPMoney = decimal.Parse(preKp == null ? "0" : preKp.ToString());
//kpMoneyYear = preKPMoney + kpMoney;
kpMoneyYear = preKPMoney;
//var prePeriod = periodBLL.GetPrePeriod(curPeriod, ts);
//if (prePeriod!=null)
//{
// //sql = @"select BWBCreditTotal from tks_fas_glBalance
// // where subjectCode='5101' and periodId=@PeriodId";
// sql = @"select BWBEndBAL from tks_fas_glBalance
// where subjectCode='5101' and periodId=@PeriodId";
// var preKp = cnn.ExecuteScalar(sql, new { PeriodId = prePeriod.Id }, ts);
// var preKPMoney = decimal.Parse(preKp == null ? "0" : preKp.ToString());
// //kpMoneyYear = preKPMoney + kpMoney;
// kpMoneyYear = preKPMoney;
//}
//else
//{
// if (!string.IsNullOrEmpty(request.AccountList))
// {
// res.IsSuccess = false;
// res.Message = "加载完成";
// return res;
// }
// else
// {
// throw new NormalException("当前账套无上一个期间");
// }
//}
//sql = @"select K1.SubjectCode,K1.name,( K1.BWBDebitTotal+K2.money) as BWBEndBAL from (
// SELECT A.SubjectCode,B.name, A.BWBDebitTotal
// FROM TKS_FAS_GLBalance A left join TKS_FAS_AccountSubject B
// on A.SubjectCode=B.Code and A.AccountId=B.AccountId
// where A.PeriodId=@PrePeriodId and B.SLevel=2
// and B.RootCode='1002') K1
// left join
// (
// select A.SubjectCode,B.Name,(sum(Money_Debit)- sum(Money_Credit)) as money from TKS_FAS_DocDetail A left join TKS_FAS_AccountSubject B
// on A.SubjectCode=B.Code and A.AccountId=B.AccountId
// where B.RootCode='1002' and B.SLevel=2 and A.PeriodId=@CurPeriodId
// group by A.SubjectCode,B.Name) K2 on K1.SubjectCode=K2.SubjectCode";
sql = @"SELECT A.SubjectCode,B.name, A.BWBEndBAL
FROM TKS_FAS_GLBalance A left join TKS_FAS_AccountSubject B
on A.SubjectCode=B.Code and A.AccountId=B.AccountId
where A.PeriodId=@CurPeriodId and B.SLevel=2
and B.RootCode='1002'";
var lsBank = cnn.Query <TKS_FAS_GLBalance>(sql,
new { CurPeriodId = curPeriod.Id }, ts).ToList();
List <SubjectMoney> banks = new List <SubjectMoney>();
decimal Bank_Main = 0;
foreach (var bank in lsBank)
{
banks.Add(new SubjectMoney
{
Item = "本月银行存款余额",
Credit_Debit = "借",
Money = bank.BWBEndBAL,
Subject = bank.SubjectCode + " " + bank.Name
});
Bank_Main += bank.BWBEndBAL;
}
//首页银行存款余额
var Bank_MainMoney = Bank_Main;
//本月应收账款余额
#region 本月应收账款余额
//sql = @"select k1.subjectCode,k1.CalValue1,k3.Name ,
// (k1.bwbEndBal_J+(case when k2.bwbEndBal is null then 0 else k2.bwbEndBal end )) as bwbEndBal from (
// select SubjectCode ,CalItem1,CalValue1,
// sum(case when ECredit_Debit=0 then bwbEndBal else 0 end) bwbEndBal_J,
// sum(case when ECredit_Debit=1 then BWBEndBAL else 0 end) bwbEndBal_D
// from TKS_FAS_FGLBalance where periodId=@PrePeriodId and SubjectCode='1131'
// group by SubjectCode ,CalItem1,CalValue1) k1
// left join (
// select SubjectCode,CalItem1,CalValue1,
// sum(Money_Debit)-sum(Money_Credit) as bwbEndBal
// from TKS_FAS_DocDetail
// where periodId=@CurPeriodId and SubjectCode='1131'
// group by SubjectCode ,CalItem1,CalValue1) k2
// on k1.CalValue1=k2.CalValue1
// left join TKS_FAS_CaculateHelperDetail k3
// on ( k1.CalValue1=k3.Code) and k1.CalItem1=k3.parentId order by k1.CalValue1";
sql = @"select k1.subjectCode,k1.CalValue1,k3.Name ,
(k1.bwbEndBal_J-k1.bwbEndBal_D) as bwbEndBal from (
select SubjectCode ,CalItem1,CalValue1,
sum(case when ECredit_Debit=0 then bwbEndBal else 0 end) bwbEndBal_J,
sum(case when ECredit_Debit=1 then BWBEndBAL else 0 end) bwbEndBal_D
from TKS_FAS_FGLBalance where periodId=@CurPeriodId and SubjectCode like'1131%'
group by SubjectCode ,CalItem1,CalValue1) k1
left join TKS_FAS_CaculateHelperDetail k3
on ( k1.CalValue1=k3.Code) and k1.CalItem1=k3.parentId order by k1.CalValue1";
var ls1131 = cnn.Query <TKS_FAS_FGLBalance>(sql,
new { CurPeriodId = curPeriod.Id }, ts).ToList();
List <SubjectMoney> ar = new List <SubjectMoney>();
decimal ar_Main = 0;
foreach (var item in ls1131)
{
ar.Add(new SubjectMoney {
Credit_Debit = "借",
Item = "本月应收账款余额",
Subject = "应收账款 " + "-" + item.Name,
Money = item.BWBEndBAL
});
ar_Main += item.BWBEndBAL;
}
//首页应收账款余额
var ar_MainMoney = ar_Main;
#endregion
#region 本月客户收款金额
//借方，银行存款
//贷方，应收账款客户
var lsKHMoney = CurMonthKHSK(curPeriod, ts);
#endregion
#region 本月应付账款余额
//sql = @"select k1.subjectCode,k1.CalValue1,k3.Name ,
// (k1.bwbEndBal_D+(case when k2.bwbEndBal is null then 0 else k2.bwbEndBal end )) as bwbEndBal from (
// select SubjectCode ,CalItem1,CalValue1,
// sum(case when ECredit_Debit=0 then bwbEndBal else 0 end) bwbEndBal_J,
// sum(case when ECredit_Debit=1 then BWBEndBAL else 0 end) bwbEndBal_D
// from TKS_FAS_FGLBalance where periodId=@PrePeriodId and SubjectCode='2121'
// group by SubjectCode ,CalItem1,CalValue1) k1
// left join (
// select SubjectCode,CalItem1,CalValue1,
// sum(Money_Credit)- sum(Money_Debit) as bwbEndBal
// from TKS_FAS_DocDetail
// where periodId=@CurPeriodId and SubjectCode='2121'
// group by SubjectCode ,CalItem1,CalValue1) k2
// on k1.CalValue1=k2.CalValue1
// left join TKS_FAS_CaculateHelperDetail k3
// on ( k1.CalValue1=k3.Code) and k1.CalItem1=k3.parentId order by k1.CalValue1";
sql = @"select k1.subjectCode,k1.CalValue1,k3.Name ,
(k1.bwbEndBal_D-bwbEndBal_J) as bwbEndBal from (
select SubjectCode ,CalItem1,CalValue1,
sum(case when ECredit_Debit=0 then bwbEndBal else 0 end) bwbEndBal_J,
sum(case when ECredit_Debit=1 then BWBEndBAL else 0 end) bwbEndBal_D
from TKS_FAS_FGLBalance where periodId=@CurPeriodId and SubjectCode like'2121%'
group by SubjectCode ,CalItem1,CalValue1) k1
left join TKS_FAS_CaculateHelperDetail k3
on ( k1.CalValue1=k3.Code) and k1.CalItem1=k3.parentId order by k1.CalValue1";
var ls2121 = cnn.Query <TKS_FAS_FGLBalance>(sql,
new { CurPeriodId = curPeriod.Id }, ts).ToList();
List <SubjectMoney> ap = new List <SubjectMoney>();
decimal ap_Main = 0;
foreach (var item in ls2121)
{
ap.Add(new SubjectMoney
{
Credit_Debit = "贷",
Item = "本月应付账款余额",
Subject = "应付账款-" + item.Name,
Money = item.BWBEndBAL
});
ap_Main += item.BWBEndBAL;
}
//首页应付账款余额
var ap_MainMoney = ap_Main;
#endregion
#region 本月支付供应商款项金额
var lsPaySupplier = PaySupplier(curPeriod, ts);
#endregion
#region 本月供应商采购金额
var lsCG = CG(curPeriod, ts);
#endregion
sql = @"select * from TKS_FAS_AccountInfo where id=@Id";
var account = cnn.QueryFirstOrDefault <TKS_FAS_AccountInfo>(sql, new { Id = request.AccountList ?? user.AccountId }, ts);
ts.Commit();
res.Account = account.QY_Name;
res.KPMoney = new SubjectMoney {
Subject = "本月开票收入", Credit_Debit = "贷", Money = kpMoney
};
if (data_Account.AddedValueTaxType == 1)
{
//小规模
res.Quarter_KPMoney = new SubjectMoney {
Subject = "本季度开票收入", Credit_Debit = "贷", Money = quarter_kpMoney
};
}
res.KPMoneyYear = new SubjectMoney {
Subject = "本年累计开票收入", Credit_Debit = "贷", Money = kpMoneyYear
};
res.BankDepositBal = banks;
res.ARAccountBal = ar;
res.KHMoney = lsKHMoney;
res.APAccountBal = ap;
res.PaySupplierMoney = lsPaySupplier;
res.CGSupplierMoney = lsCG;
res.BankDepositBal_Main = new SubjectMoney {
Subject = "银行存款", Money = Bank_MainMoney
};
res.ARAccountBal_Main = new SubjectMoney {
Subject = "应收账款", Money = ar_MainMoney
};
res.APAccountBal_Main = new SubjectMoney {
Subject = "应付账款", Money = ap_MainMoney
};
res.IsSuccess = true;
res.Message = "加载完成";
return(res);
}
catch (Exception ex)
{
ts.Rollback();
return(this.DealException(res, ex) as ResponseJYGet);
}
}
}
protected void loadData()
{
gvPeriod.DataSource = PeriodBLL.getInstance().getAll();
gvPeriod.DataBind();
}
protected void btnReport_Click(object sender, EventArgs e)
{
try
{
List <Entities.Period> listPeriod = PeriodBLL.getInstance().getAll();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
text::Document pdfDoc = new text::Document(text::PageSize.A4, 10, 10, 10, 10);
pdfDoc.SetPageSize(iTextSharp.text.PageSize.A4.Rotate());
PdfWriter.GetInstance(pdfDoc, memoryStream);
pdfDoc.Open();
String imagepath = Server.MapPath("../../images/page-icons");
iTextSharp.text.Image deas = iTextSharp.text.Image.GetInstance(imagepath + "/DEAS-logo.jpg");
deas.ScaleToFit(140f, 120f);
//Give space before image
deas.SpacingBefore = 10f;
//Give some space after the image
deas.SpacingAfter = 1f;
deas.Alignment = text::Element.ALIGN_LEFT;
pdfDoc.Add(deas);
text::Paragraph title = new text::Paragraph();
title.Font = text::FontFactory.GetFont("dax-black", 32, new text::BaseColor(0, 51, 102));
title.Alignment = text::Element.ALIGN_CENTER;
title.Add("\n\n Reporte de Períodos\n\n");
pdfDoc.Add(title);
PdfPTable oPTable = new PdfPTable(5);
oPTable.TotalWidth = 100;
oPTable.SpacingBefore = 20f;
oPTable.SpacingAfter = 30f;
oPTable.AddCell("Descripción");
oPTable.AddCell("Modalidad");
oPTable.AddCell("Inicio");
oPTable.AddCell("Fin");
oPTable.AddCell("Estado");
if (listPeriod.Count > 0)
{
foreach (Entities.Period pPeriod in listPeriod)
{
oPTable.AddCell(pPeriod.name);
oPTable.AddCell(pPeriod.oPeriodType.description);
oPTable.AddCell(pPeriod.startDate.ToShortDateString());
oPTable.AddCell(pPeriod.finalDate.ToShortDateString());
oPTable.AddCell((pPeriod.state == 1 ? "Activo" : "Inactivo"));
}
}
else
{
PdfPCell cell = new PdfPCell(new text::Phrase("No existen períodos registrados."));
cell.Colspan = 5;
cell.HorizontalAlignment = 1; //0=Left, 1=Centre, 2=Right
oPTable.AddCell(cell);
}
pdfDoc.Add(oPTable);
pdfDoc.Close();
byte[] bytes = memoryStream.ToArray();
memoryStream.Close();
Response.Clear();
Response.ContentType = "application/pdf";
Response.AddHeader("Content-Disposition", "attachment; filename=Periodo.pdf");
Response.ContentType = "application/pdf";
Response.Buffer = true;
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.BinaryWrite(bytes);
Response.End();
Response.Close();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
protected void btnNew_Click(object sender, ImageClickEventArgs e)
{
unlockControls();
txtCode.Text = PeriodBLL.getInstance().getNextCode().ToString();
}
