private void btnSave_Click(object sender, EventArgs e)
{
try
{
try
{
gridView1.FocusedRowHandle -= 1;
gridView1.FocusedRowHandle += 1;
}
catch { }
string sErr = "";
if (gridView1.RowCount == 0)
{
throw new Exception("请加载数据");
}
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSQL = "SELECT * FROM dbo.GL_mend WHERE iyear = aaaaaa AND iperiod = bbbbbb";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iMonth.ToString());
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("获得模块状态失败");
}
if (BaseFunction.ReturnBool(dt.Rows[0]["bflag"]))
{
throw new Exception("总账已结账");
}
int iCount = 0;
for (int i = 0; i < gridView1.RowCount; i++)
{
bool b = BaseFunction.ReturnBool(gridView1.GetRowCellValue(i, gridColbChoose));
if (!b)
{
continue;
}
//int i_id_from = BaseFunction.ReturnInt(gridView1.GetRowCellValue(i, gridColi_id));
string scsign = gridView1.GetRowCellValue(i, gridColcsign).ToString().Trim();
int ino_id = BaseFunction.ReturnInt(gridView1.GetRowCellValue(i, gridColino_id));
sSQL = "select * from {4}..GL_accvouch where csign = '{0}' and ino_id = '{1}' and iyear = {2} and iPeriod = {3} ";
sSQL = string.Format(sSQL, scsign, ino_id, iYear, iMonth, sDataFrom);
DataTable dtGL_accvouch = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
for (int j = 0; j < dtGL_accvouch.Rows.Count; j++)
{
// , , , , , ccheck, cbook, ibook, ccashier, iflag, ,
//iBG_OverFlag, cBG_Auditor, dBG_AuditTime, cBG_AuditOpinion, bWH_BgFlag, ssxznum, CErrReason, BG_AuditRemark,
//cBudgetBuffer, iBG_ControlResult, NCVouchID, daudit_date, RowGuid, cBankReconNo, , , wllqDate,
//wllqPeriod, tvouchtime, cblueoutno_id,
Model.GL_accvouch mod = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.GL_accvouch();
mod.iperiod = BaseFunction.ReturnInt(dtGL_accvouch.Rows[j]["iperiod"]);
mod.csign = dtGL_accvouch.Rows[j]["csign"].ToString().Trim();
mod.isignseq = BaseFunction.ReturnInt(dtGL_accvouch.Rows[j]["isignseq"]);
mod.ino_id = BaseFunction.ReturnInt(dtGL_accvouch.Rows[j]["ino_id"]);
mod.inid = BaseFunction.ReturnInt(dtGL_accvouch.Rows[j]["inid"]);
mod.dbill_date = BaseFunction.ReturnDate(dtGL_accvouch.Rows[j]["dbill_date"]);
mod.idoc = BaseFunction.ReturnInt(dtGL_accvouch.Rows[j]["idoc"]);
mod.cbill = dtGL_accvouch.Rows[j]["cbill"].ToString().Trim();
mod.ibook = 0;
mod.iflag = BaseFunction.ReturnInt(dtGL_accvouch.Rows[j]["iflag"]);
mod.ctext1 = dtGL_accvouch.Rows[j]["ctext1"].ToString().Trim();
mod.ctext2 = dtGL_accvouch.Rows[j]["ctext2"].ToString().Trim();
mod.cdigest = dtGL_accvouch.Rows[j]["cdigest"].ToString().Trim();
mod.ccode = dtGL_accvouch.Rows[j]["ccode"].ToString().Trim();
sSQL = @"
SELECT cast(0 as bit) as choose,b.iID
,a.ccode as 会计科目,a.ccode_name, b.对照科目,'edit' as iSave,b.Remark
FROM code a LEFT JOIN [_科目对照] b ON a.ccode = b.会计科目
WHERE ISNULL(bend ,0) = 1 and a.iyear = {0} and b.对照科目 = '{1}'
ORDER BY a.ccode,b.iID
";
sSQL = string.Format(sSQL, iYear, mod.ccode);
DataTable dtCode = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCode == null || dtCode.Rows.Count == 0)
{
throw new Exception("请设置科目对照:" + mod.ccode);
}
mod.ccode = dtCode.Rows[0]["会计科目"].ToString().Trim();
mod.cexch_name = dtGL_accvouch.Rows[j]["cexch_name"].ToString().Trim();
mod.md = BaseFunction.ReturnDecimal(dtGL_accvouch.Rows[j]["md"]);
mod.mc = BaseFunction.ReturnDecimal(dtGL_accvouch.Rows[j]["mc"]);
mod.md_f = BaseFunction.ReturnDecimal(dtGL_accvouch.Rows[j]["md_f"]);
mod.mc_f = BaseFunction.ReturnDecimal(dtGL_accvouch.Rows[j]["mc_f"]);
mod.nfrat = BaseFunction.ReturnDecimal(dtGL_accvouch.Rows[j]["nfrat"]);
mod.nd_s = BaseFunction.ReturnDecimal(dtGL_accvouch.Rows[j]["nd_s"]);
mod.nc_s = BaseFunction.ReturnDecimal(dtGL_accvouch.Rows[j]["nc_s"]);
mod.csettle = dtGL_accvouch.Rows[j]["csettle"].ToString().Trim();
mod.cn_id = dtGL_accvouch.Rows[j]["cn_id"].ToString().Trim();
mod.dt_date = BaseFunction.ReturnDate(dtGL_accvouch.Rows[j]["dt_date"]);
mod.iyear = BaseFunction.ReturnInt(dtGL_accvouch.Rows[j]["iyear"]);
mod.iYPeriod = BaseFunction.ReturnInt(dtGL_accvouch.Rows[j]["iYPeriod"]);
mod.cDefine15 = BaseFunction.ReturnInt(dtGL_accvouch.Rows[j]["i_id"]);
mod.ccodeexch_equal = dtGL_accvouch.Rows[j]["ccodeexch_equal"].ToString().Trim();
mod.ccode_equal = dtGL_accvouch.Rows[j]["ccode_equal"].ToString().Trim();
//, , , , , , , , , , , , , , cdept_id, cperson_id,
//ccus_id, csup_id, citem_id, citem_class, cname, , iflagbank, iflagPerson, bdelete, coutaccset, ioutyear,
//coutsysname, coutsysver, doutbilldate, ioutperiod, coutsign, coutno_id, doutdate, coutbillsign, coutid, bvouchedit,
//bvouchAddordele, bvouchmoneyhold, bvalueedit, bcodeedit, ccodecontrol, bPCSedit, bDeptedit, bItemedit,
//bCusSupInput, cDefine1, cDefine2, cDefine3, cDefine4, cDefine5, cDefine6, cDefine7, cDefine8, cDefine9, cDefine10,
//cDefine11, cDefine12, cDefine13, cDefine14, , cDefine16, dReceive, cWLDZFlag, dWLDZTime, bFlagOut,
UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch();
sSQL = dal.Add(mod);
iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
// sSQL = @"select * from dsign where csign = '{0}'";
// sSQL = string.Format(sSQL, "记");
// DataTable dtdsign = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
// if (dtdsign == null || dtdsign.Rows.Count == 0)
// {
// sErr = sErr + "凭证类别不正确\n";
// }
// if (ino_id == 0)
// {
// sSQL = @"
//select isnull(max(ino_id),0) as ino_id from GL_accvouch where csign = '{0}' and iyear = {1} and iperiod = {2}
//";
// sSQL = string.Format(sSQL, scsign, iYear, iMonth);
// DataTable dtTemp = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
// if (dtTemp == null || dtTemp.Rows.Count == 0)
// {
// ino_id = 0;
// }
// else
// {
// ino_id = BaseFunction.ReturnInt(dtTemp.Rows[0]["ino_id"]);
// }
}
if (sErr.Length != 0)
{
throw new Exception(sErr);
}
tran.Commit();
MessageBox.Show("保存成功");
btnSel_Click(null, null);
}
catch (Exception ee)
{
tran.Rollback();
throw new Exception(ee.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
catch (Exception ee)
{
FrmMsgBox f = new FrmMsgBox();
f.Text = "提示";
f.richTextBox1.Text = ee.Message;
f.ShowDialog();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
try
{
gridView1.FocusedRowHandle -= 1;
gridView1.FocusedRowHandle += 1;
}
catch { }
string sErr = "";
if (gridView1.RowCount == 0)
{
throw new Exception("请加载数据");
}
DataTable dtErr = new DataTable();
DataColumn dc = new DataColumn();
dc.ColumnName = "iID";
dtErr.Columns.Add(dc);
dc = new DataColumn();
dc.ColumnName = "sErr";
dtErr.Columns.Add(dc);
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
for (int i = 0; i < gridView1.RowCount; i++)
{
bool b = BaseFunction.ReturnBool(gridView1.GetRowCellValue(i, gridColbChoose));
if (!b)
{
continue;
}
bool bUsed = BaseFunction.ReturnBool(gridView1.GetRowCellValue(i, gridColbUsed));
if (bUsed)
{
continue;
}
string sDate = gridView1.GetRowCellValue(i, gridColBLDAT).ToString().Trim();
DateTime dtmDate = BaseFunction.ReturnDate(sDate.Substring(0, 4) + "-" + sDate.Substring(4, 2) + "-" + sDate.Substring(6, 2));
if (dtmDate < Convert.ToDateTime("2018-01-01"))
{
sErr = sErr + "行" + (i + 1).ToString() + "日期不正确\n";
continue;
}
int iYear = dtmDate.Year;
int iMonth = dtmDate.Month;
string sSQL = "SELECT * FROM dbo.GL_mend WHERE iyear = aaaaaa AND iperiod = bbbbbb";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iMonth.ToString());
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("获得模块状态失败");
}
if (BaseFunction.ReturnBool(dt.Rows[0]["bflag"]))
{
throw new Exception("总账已结账");
}
string scsign = "";
int ino_id = 0;
int iRow = 0;
int inid = 0;
int iCount = 0;
sSQL = @"select * from dsign where csign = '{0}'";
sSQL = string.Format(sSQL, "记");
DataTable dtdsign = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtdsign == null || dtdsign.Rows.Count == 0)
{
sErr = sErr + "凭证类别不正确\n";
}
if (ino_id == 0)
{
sSQL = @"
select isnull(max(ino_id),0) as ino_id from GL_accvouch where csign = '{0}' and iyear = {1} and iperiod = {2}
";
sSQL = string.Format(sSQL, scsign, iYear, iMonth);
DataTable dtTemp = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtTemp == null || dtTemp.Rows.Count == 0)
{
ino_id = 0;
}
else
{
ino_id = BaseFunction.ReturnInt(dtTemp.Rows[0]["ino_id"]);
}
}
ino_id += 1;
#region 第一行
inid += 1;
Model.GL_accvouch model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.GL_accvouch();
model.iperiod = iMonth;
model.csign = scsign;
model.isignseq = BaseFunction.ReturnInt(dtdsign.Rows[0]["isignseq"]);
model.ino_id = ino_id;
iRow = 1;
model.inid = iRow;
model.dbill_date = dtmDate;
model.idoc = -1;
model.cbill = sUserName;
model.ibook = 0;
model.cdigest = gridView1.GetRowCellValue(i, gridColSGTXT).ToString().Trim();;
sSQL = @"
select b.*
from [_科目对照] a
inner join code b on a.会计科目 = b.ccode and b.iyear = {1}
where 对照科目 = '{0}'
";
sSQL = string.Format(sSQL, gridView1.GetRowCellValue(i, gridColNEWKO), iYear);
DataTable dtCode = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCode != null && dtCode.Rows.Count == 1)
{
sErr = sErr + "行" + (i + 1).ToString() + "会计科目不正确，请设置对照档案\n";
continue;
}
if (!BaseFunction.ReturnBool(dtCode.Rows[0]["bend"]))
{
sErr = sErr + "行" + (i + 1).ToString() + "会计科目不是末级科目，请设置对照档案\n";
continue;
}
model.ccode = dtCode.Rows[0]["会计科目"].ToString().Trim();
int NEWBS = BaseFunction.ReturnInt(gridView1.GetRowCellValue(i, gridColNEWBS).ToString().Trim());
//借方
if (NEWBS == 40)
{
model.md = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColWRBTR));
model.md_f = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColWRBTR));
model.nfrat = 1;
model.nd_s = 0;
model.nc_s = 0;
}
else
{
model.mc = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColWRBTR));
model.mc_f = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColWRBTR));
model.nfrat = 1;
model.nd_s = 0;
model.nc_s = 0;
}
bool bperson = BaseFunction.ReturnBool(dtCode.Rows[0]["bperson"]);
if (bperson)
{
sErr = sErr + "行" + (i + 1).ToString() + "必须有人员\n";
continue;
}
bool bcus = BaseFunction.ReturnBool(dtCode.Rows[0]["bcus"]);
if (bcus)
{
sErr = sErr + "行" + (i + 1).ToString() + "必须有客户\n";
continue;
}
bool bsup = BaseFunction.ReturnBool(dtCode.Rows[0]["bsup"]);
if (bsup)
{
sErr = sErr + "行" + (i + 1).ToString() + "必须有供应商\n";
continue;
}
bool bdept = BaseFunction.ReturnBool(dtCode.Rows[0]["bdept"]);
if (bsup)
{
sErr = sErr + "行" + (i + 1).ToString() + "必须有部门\n";
continue;
}
model.bdelete = false;
model.doutbilldate = model.dbill_date;
model.bvouchedit = true;
model.bvouchAddordele = false;
model.bvouchmoneyhold = false;
model.bvalueedit = true;
model.bcodeedit = true;
model.bPCSedit = true;
model.bDeptedit = true;
model.bItemedit = true;
model.bCusSupInput = false;
model.bFlagOut = false;
model.RowGuid = Guid.NewGuid().ToString();
model.iyear = iYear;
model.iYPeriod = iMonth;
model.tvouchtime = DateTime.Now;
DAL.GL_accvouch dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch();
sSQL = dal.Add(model);
iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
gridView1.SetRowCellValue(i, gridColbUsed, true);
#endregion
string XBLNR = gridView1.GetRowCellValue(i, gridColXBLNR).ToString().Trim();
for (int j = i + 1; j < gridView1.RowCount; j++)
{
string XBLNR_2 = gridView1.GetRowCellValue(j, gridColXBLNR).ToString().Trim();
if (XBLNR != XBLNR_2)
{
continue;
}
model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.GL_accvouch();
model.iperiod = iMonth;
model.csign = scsign;
model.isignseq = BaseFunction.ReturnInt(dtdsign.Rows[0]["isignseq"]);
model.ino_id = ino_id;
iRow += 1;
model.inid = iRow;
model.dbill_date = dtmDate;
model.idoc = -1;
model.cbill = sUserName;
model.ibook = 0;
model.cdigest = gridView1.GetRowCellValue(j, gridColSGTXT).ToString().Trim();;
sSQL = @"
select b.*
from [_科目对照] a
inner join code b on a.会计科目 = b.ccode and b.iyear = {1}
where 对照科目 = '{0}'
";
sSQL = string.Format(sSQL, gridView1.GetRowCellValue(j, gridColNEWKO), iYear);
dtCode = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtCode != null && dtCode.Rows.Count == 1)
{
sErr = sErr + "行" + (j + 1).ToString() + "会计科目不正确，请设置对照档案\n";
continue;
}
if (!BaseFunction.ReturnBool(dtCode.Rows[0]["bend"]))
{
sErr = sErr + "行" + (j + 1).ToString() + "会计科目不是末级科目，请设置对照档案\n";
continue;
}
model.ccode = dtCode.Rows[0]["会计科目"].ToString().Trim();
NEWBS = BaseFunction.ReturnInt(gridView1.GetRowCellValue(j, gridColNEWBS).ToString().Trim());
//借方
if (NEWBS == 40)
{
model.md = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridColWRBTR));
model.md_f = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridColWRBTR));
model.nfrat = 1;
model.nd_s = 0;
model.nc_s = 0;
}
else
{
model.mc = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridColWRBTR));
model.mc_f = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridColWRBTR));
model.nfrat = 1;
model.nd_s = 0;
model.nc_s = 0;
}
bperson = BaseFunction.ReturnBool(dtCode.Rows[0]["bperson"]);
if (bperson)
{
sErr = sErr + "行" + (j + 1).ToString() + "必须有人员\n";
continue;
}
bcus = BaseFunction.ReturnBool(dtCode.Rows[0]["bcus"]);
if (bcus)
{
sErr = sErr + "行" + (j + 1).ToString() + "必须有客户\n";
continue;
}
bsup = BaseFunction.ReturnBool(dtCode.Rows[0]["bsup"]);
if (bsup)
{
sErr = sErr + "行" + (j + 1).ToString() + "必须有供应商\n";
continue;
}
bdept = BaseFunction.ReturnBool(dtCode.Rows[0]["bdept"]);
if (bsup)
{
sErr = sErr + "行" + (j + 1).ToString() + "必须有部门\n";
continue;
}
model.bdelete = false;
model.doutbilldate = model.dbill_date;
model.bvouchedit = true;
model.bvouchAddordele = false;
model.bvouchmoneyhold = false;
model.bvalueedit = true;
model.bcodeedit = true;
model.bPCSedit = true;
model.bDeptedit = true;
model.bItemedit = true;
model.bCusSupInput = false;
model.bFlagOut = false;
model.RowGuid = Guid.NewGuid().ToString();
model.iyear = iYear;
model.iYPeriod = iMonth;
model.tvouchtime = DateTime.Now;
dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch();
sSQL = dal.Add(model);
iCount += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
gridView1.SetRowCellValue(j, gridColbUsed, true);
}
}
if (sErr.Length != 0)
{
throw new Exception(sErr);
}
tran.Commit();
MessageBox.Show("保存成功");
btnSel_Click(null, null);
}
catch (Exception ee)
{
tran.Rollback();
throw new Exception(ee.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
catch (Exception ee)
{
FrmMsgBox f = new FrmMsgBox();
f.Text = "提示";
f.richTextBox1.Text = ee.Message;
f.ShowDialog();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
string[] s = lookUpEditPeriod.Text.Trim().Split('.');
int iYear = BaseFunction.ReturnInt(s[0]);
int iPeriod = BaseFunction.ReturnInt(s[1]);
string sVoucher = "";
string sErr = "";
int iCount = 0;
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSQL = "SELECT * FROM dbo.GL_mend WHERE iyear = aaaaaa AND iperiod = bbbbbb";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("Failed to obtain module status");
}
if (BaseFunction.ReturnBool(dt.Rows[0]["bflag"]))
{
throw new Exception("General ledger");
}
string sino_id = "";
long i_id = -1;
sSQL = @"
select bflag from gl_mend where iyear = aaaaaa and iperiod = bbbbbb
";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
DataTable dtTemp = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (BaseFunction.ReturnBool(dtTemp.Rows[0]["bflag"]))
{
throw new Exception("The current date has been checked out");
}
sSQL = @"
select *
from _AmountOfExchangeProfitAndLoss
where iyear = aaaaaa and iperiod = bbbbbb
";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
if (DialogResult.Yes != MessageBox.Show("The document has been generated. Will it continue?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk))
{
throw new Exception("User cancelled");
}
i_id = BaseFunction.ReturnLong(dt.Rows[0]["i_id"]);
sSQL = @"
delete _AmountOfExchangeProfitAndLoss where iyear = aaaaaa and iperiod = bbbbbb
";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
if (sino_id != "")
{
sSQL = @"
select *
from GL_accvouch
where csign = 'AP' and iyear = aaaaaa and iperiod = bbbbbb and ino_id = 'cccccc' and isnull(iflag,0) = 0 and i_id = 'dddddd'
";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
sSQL = sSQL.Replace("cccccc", sino_id);
sSQL = sSQL.Replace("dddddd", i_id.ToString());
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
throw new Exception("Accounting vouchers already exist");
}
}
for (int i = 0; i < gridView1.RowCount; i++)
{
Model._AmountOfExchangeProfitAndLoss mod = new UFIDA.U8.UAP.CustomApp.ControlForm.Model._AmountOfExchangeProfitAndLoss();
mod.iYear = iYear;
mod.iPeriod = iPeriod;
mod.AutoID = BaseFunction.ReturnLong(gridView1.GetRowCellValue(i, gridColAutoID));
mod.cCode = gridView1.GetRowCellValue(i, gridColcCode).ToString().Trim();
mod.irowno = BaseFunction.ReturnLong(gridView1.GetRowCellValue(i, gridColirowno));
mod.cExch_Name = gridView1.GetRowCellValue(i, gridColcExch_Name).ToString().Trim();
mod.dDate = BaseFunction.ReturnDate(gridView1.GetRowCellValue(i, gridColdDate));
mod.cInvCode = gridView1.GetRowCellValue(i, gridColcInvCode).ToString().Trim();
mod.cInvName = gridView1.GetRowCellValue(i, gridColcInvName).ToString().Trim();
mod.cInvStd = gridView1.GetRowCellValue(i, gridColcInvStd).ToString().Trim();
mod.iOriSum = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColiOriSum));
mod.nflat = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColnflat));
mod.nflat2 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColnflat2));
mod.AmountOfExchangeProfitAndLoss = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridColAmountOfExchangeProfitAndLoss));
mod.i_ID = -1;
mod.redi_ID = -1;
if (mod.nflat == 0 || mod.nflat2 == 0)
{
throw new Exception("Please set exchange rate");
}
DAL._AmountOfExchangeProfitAndLoss dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL._AmountOfExchangeProfitAndLoss();
sSQL = dal.Add(mod);
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
if (MessageBox.Show("Whether to generate accounting vouchers?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes)
{
sSQL = @"
select * from _Code where VouchType = 'Exchange gain or loss'
";
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("Please set aounting subject [_Code.Exchange gain or loss]");
}
string sKMD = dt.Rows[0]["Debtor"].ToString().Trim();
string sKMC = dt.Rows[0]["Creditor"].ToString().Trim();
string sZY = dt.Rows[0]["Remark"].ToString().Trim();
if (sKMD == "" || sKMC == "")
{
throw new Exception("Please set aounting subject [_Code.Exchange gain or loss]");
}
sSQL = @"
select sum(AmountOfExchangeProfitAndLoss) as AmountOfExchangeProfitAndLoss ,rd.cVenCode
from _AmountOfExchangeProfitAndLoss a
left join RdRecord01 rd on a.cCode = rd.cCode
where a.iyear = aaaaaa and a.iperiod = bbbbbb
group by rd.cVenCode
";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
DataTable dtSum = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dtSum == null || dtSum.Rows.Count == 0)
{
throw new Exception("No data");
}
sSQL = "select isnull(max(ino_id),0) from GL_accvouch where iyear = aaaaaa AND iperiod = bbbbbb and csign = 'AP'";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
DataTable dtinoid = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
int ino_id = BaseFunction.ReturnInt(dtinoid.Rows[0][0]) + 1;
decimal dSum = 0;
#region 生成凭证
sVoucher = sVoucher + "AP-" + ino_id.ToString().PadLeft(4, '0') + "\n";
#region 借方
for (int i = 0; i < dtSum.Rows.Count; i++)
{
decimal dMoney = BaseFunction.ReturnDecimal(dtSum.Rows[i]["AmountOfExchangeProfitAndLoss"], 2);
dSum = dSum + dMoney;
}
Model.GL_accvouch model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.GL_accvouch();
model.iperiod = iPeriod;
model.csign = "AP";
model.isignseq = 2;
model.ino_id = ino_id;
model.inid = 1;
model.dbill_date = BaseFunction.ReturnDate(sLogDate);
model.idoc = -1;
model.cbill = sUserName;
model.ibook = 0;
model.cdigest = sZY;
model.ccode = sKMD;
//model.cDefine1 = sDocment2;
model.md = dSum;
model.mc = 0;
model.md_f = 0;
model.mc_f = 0;
model.nfrat = 0;
model.nd_s = 0;
model.nc_s = 0;
//model.csettle = ""; //结算方式
//model.cn_id
//model.dt_date =
model.ccode_equal = sKMC;;
model.bdelete = false;
//model.doutbilldate = model.dbill_date;
model.bvouchedit = true;
model.bvouchAddordele = false;
model.bvouchmoneyhold = false;
model.bvalueedit = true;
model.bcodeedit = true;
model.bPCSedit = true;
model.bDeptedit = true;
model.bItemedit = true;
model.bCusSupInput = false;
model.bFlagOut = false;
model.RowGuid = Guid.NewGuid().ToString();
model.iyear = iYear;
model.iYPeriod = BaseFunction.ReturnInt(BaseFunction.ReturnDate(iYear.ToString() + "-" + iPeriod.ToString() + "-01").ToString("yyyyMM"));
model.tvouchtime = DateTime.Now;
model.ccodeexch_equal = sKMC;
model.csup_id = dtSum.Rows[0]["cVenCode"].ToString().Trim();
UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch dalGL = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch();
sSQL = dalGL.Add(model);
iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
#endregion
for (int i = 0; i < dtSum.Rows.Count; i++)
{
#region 贷方
model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.GL_accvouch();
model.iperiod = iPeriod;
model.csign = "AP";
model.isignseq = 2;
model.ino_id = ino_id;
model.inid = 2;
model.dbill_date = BaseFunction.ReturnDate(sLogDate);
model.idoc = -1;
model.cbill = sUserName;
model.ibook = 0;
model.cdigest = sZY;
model.ccode = sKMC;
//model.cDefine1 = sDocment2;
model.md = 0;
model.mc = BaseFunction.ReturnDecimal(dtSum.Rows[i]["AmountOfExchangeProfitAndLoss"], 2);
model.md_f = 0;
model.mc_f = 0;
model.nfrat = 0;
model.nd_s = 0;
model.nc_s = 0;
//model.csettle = ""; //结算方式
//model.cn_id
//model.dt_date =
model.ccode_equal = sKMD;;
model.bdelete = false;
model.doutbilldate = model.dbill_date;
model.bvouchedit = true;
model.bvouchAddordele = false;
model.bvouchmoneyhold = false;
model.bvalueedit = true;
model.bcodeedit = true;
model.bPCSedit = true;
model.bDeptedit = true;
model.bItemedit = true;
model.bCusSupInput = false;
model.bFlagOut = false;
model.RowGuid = Guid.NewGuid().ToString();
model.iyear = iYear;
model.iYPeriod = BaseFunction.ReturnInt(BaseFunction.ReturnDate(iYear.ToString() + "-" + iPeriod.ToString() + "-01").ToString("yyyyMM"));
model.tvouchtime = DateTime.Now;
model.ccodeexch_equal = sKMD;
dalGL = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch();
sSQL = dalGL.Add(model);
iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
#endregion
}
#endregion
sSQL = @"
select i_id from GL_accvouch where iyear = {0} and iperiod = {1} and csign = '{2}' and ino_id = {3}
";
sSQL = string.Format(sSQL, model.iyear, model.iperiod, model.csign, model.ino_id);
dtTemp = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
i_id = BaseFunction.ReturnLong(dtTemp.Rows[0]["i_id"]);
sSQL = @"
update _AmountOfExchangeProfitAndLoss set csign = 'AP',i_id = 'dddddddd',ino_id = 'eeeeee'
where iyear = bbbbbbbb and iperiod = cccccccc
";
sSQL = sSQL.Replace("bbbbbbbb", model.iyear.ToString());
sSQL = sSQL.Replace("cccccccc", model.iperiod.ToString());
sSQL = sSQL.Replace("dddddddd", i_id.ToString().Trim());
sSQL = sSQL.Replace("eeeeee", model.ino_id.ToString().Trim());
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
tran.Commit();
MessageBox.Show("Sucess\n" + sVoucher);
label1.Text = "";
gridControl1.DataSource = null;
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
FrmMsgBox f = new FrmMsgBox();
f.Text = "Err";
f.richTextBox1.Text = ee.Message;
f.ShowDialog();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
try
{
gridView1.FocusedRowHandle -= 1;
gridView1.FocusedRowHandle += 1;
}
catch { }
int iCount = 0;
decimal d借sum = 0;
decimal d贷sum = 0;
string sErr = "";
int iYear = BaseFunction.ReturnInt(txtYear.Text.Trim());
int iMonth = BaseFunction.ReturnInt(txtMonth.Text.Trim());
int i年期间 = BaseFunction.ReturnInt(Convert.ToDateTime(iYear.ToString() + "-" + iMonth.ToString() + "-01").ToString("yyyyMM"));
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
if (gridView1.RowCount == 0)
{
throw new Exception("请查询可导入凭证");
}
DataTable dt币种 = new DataTable();
string sSQL = "";
for (int i = 0; i < gridView1.RowCount; i++)
{
bool b = BaseFunction.ReturnBool(gridView1.GetRowCellValue(i, gridCol选择));
if (!b)
{
continue;
}
d借sum = d借sum + BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol借方));
d贷sum = d贷sum + BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol贷方));
bool bCheck = BaseFunction.ReturnBool(gridView1.GetRowCellValue(i, gridCol供应商核算));
if (bCheck)
{
string sCheck = gridView1.GetRowCellValue(i, gridCol供应商编码).ToString().Trim();
if (sCheck == "")
{
sErr = sErr + "行" + (i + 1).ToString() + " 供应商不能为空\n";
}
}
bCheck = BaseFunction.ReturnBool(gridView1.GetRowCellValue(i, gridCol客户核算));
if (bCheck)
{
string sCheck = gridView1.GetRowCellValue(i, gridCol客户编码).ToString().Trim();
if (sCheck == "")
{
sErr = sErr + "行" + (i + 1).ToString() + " 客户不能为空\n";
}
}
bCheck = BaseFunction.ReturnBool(gridView1.GetRowCellValue(i, gridCol部门核算));
if (bCheck)
{
string sCheck = gridView1.GetRowCellValue(i, gridCol部门编码).ToString().Trim();
if (sCheck == "")
{
sErr = sErr + "行" + (i + 1).ToString() + " 部门不能为空\n";
}
}
decimal d外币借方 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol外币借方));
decimal d外币贷方 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol外币贷方));
decimal d汇率 = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(i, gridCol汇率));
string s币种 = gridView1.GetRowCellValue(i, gridCol币种).ToString().Trim();
if (d外币借方 != 0 || d外币贷方 != 0)
{
if (s币种 == "")
{
sErr = sErr + "行" + (i + 1).ToString() + " 币种不能为空\n";
}
if (d汇率 == 0)
{
sErr = sErr + "行" + (i + 1).ToString() + " 汇率不能为0\n";
}
}
}
if (d借sum != d贷sum)
{
throw new Exception("借贷不平");
}
if (sErr.Trim() != "")
{
throw new Exception(sErr);
}
DataTable dt凭证 = (DataTable)gridControl1.DataSource;
sSQL = "select isnull(max(ino_id),0) from GL_accvouch where iyear = aaaaaa AND iperiod = bbbbbb";
sSQL = sSQL.Replace("aaaaaa", txtYear.Text.ToString());
sSQL = sSQL.Replace("bbbbbb", txtMonth.Text.ToString());
DataTable dtinoid = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
int ino_id = BaseFunction.ReturnInt(dtinoid.Rows[0][0]);
ArrayList aList = new ArrayList();
for (int j = 0; j < gridView1.RowCount; j++)
{
Model.GL_accvouch model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.GL_accvouch();
model.iperiod = BaseFunction.ReturnInt(txtMonth.Text.Trim());
string sSignSeq = gridView1.GetRowCellValue(j, gridCol凭证类别).ToString().Trim();
model.csign = gridView1.GetRowCellValue(j, gridCol凭证类别).ToString().Trim();
if (sSignSeq == "现")
{
model.isignseq = 1;
}
if (sSignSeq == "银")
{
model.isignseq = 2;
}
if (sSignSeq == "转")
{
model.isignseq = 3;
}
if (sSignSeq == "记")
{
model.isignseq = 4;
}
model.ino_id = BaseFunction.ReturnInt(gridView1.GetRowCellValue(j, gridCol凭证号));
model.inid = BaseFunction.ReturnInt(gridView1.GetRowCellValue(j, gridCol行号));;
model.dbill_date = BaseFunction.ReturnDate(gridView1.GetRowCellValue(j, gridCol制单日期));
model.idoc = -1;
model.cbill = gridView1.GetRowCellValue(j, gridCol制单人).ToString().Trim();
model.ibook = 0;
model.cdigest = gridView1.GetRowCellValue(j, gridCol摘要).ToString().Trim();
model.ccode = gridView1.GetRowCellValue(j, gridCol会计科目).ToString().Trim();
model.md = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridCol借方));
model.mc = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridCol贷方));
model.md_f = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridCol外币借方));
model.mc_f = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridCol外币贷方));
model.nfrat = BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(j, gridCol汇率));
model.nd_s = 0;
model.nc_s = 0;
//model.csettle = ""; //结算方式
//model.cn_id
//model.dt_date =
DataRow[] dr凭证;
if (model.md > 0)
{
string s = "贷方>0 and 凭证类别 = '" + model.csign + "' and 凭证号 = '" + gridView1.GetRowCellValue(j, gridCol凭证号).ToString() + "'";
dr凭证 = dt凭证.Select(s);
}
else
{
string s = "借方>0 and 凭证类别 = '" + model.csign + "' and 凭证号 = '" + gridView1.GetRowCellValue(j, gridCol凭证号).ToString() + "'";
dr凭证 = dt凭证.Select(s);
}
string s对方科目 = "";
for (int k = 0; k < dr凭证.Length; k++)
{
if (s对方科目 == "")
{
s对方科目 = dr凭证[k]["会计科目"].ToString().Trim();
}
else
{
if (s对方科目.IndexOf(dr凭证[k]["会计科目"].ToString().Trim()) < 0)
{
s对方科目 = s对方科目 + "," + dr凭证[k]["会计科目"].ToString().Trim();
}
}
}
if (s对方科目.Length <= 50)
{
model.ccode_equal = s对方科目;
}
model.bdelete = false;
model.doutbilldate = model.dbill_date;
model.bvouchedit = true;
model.bvouchAddordele = false;
model.bvouchmoneyhold = false;
model.bvalueedit = true;
model.bcodeedit = true;
model.bPCSedit = true;
model.bDeptedit = true;
model.bItemedit = true;
model.bCusSupInput = false;
model.bFlagOut = false;
model.RowGuid = Guid.NewGuid().ToString();
model.iyear = BaseFunction.ReturnInt(txtYear.Text.Trim());
model.iYPeriod = i年期间;
model.tvouchtime = DateTime.Now;
//if (s对方科目.Length <= 100)
//{
model.ccodeexch_equal = s对方科目;
//}
if (gridView1.GetRowCellValue(j, gridCol客户编码).ToString() != "")
{
model.ccus_id = gridView1.GetRowCellValue(j, gridCol客户编码).ToString();
}
if (gridView1.GetRowCellValue(j, gridCol供应商编码).ToString() != "")
{
model.csup_id = gridView1.GetRowCellValue(j, gridCol供应商编码).ToString();
}
DAL.GL_accvouch dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch();
sSQL = dal.Add(model);
iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
if (sErr.Length > 0)
{
throw new Exception(sErr);
}
if (iCount > 0)
{
tran.Commit();
MessageBox.Show("保存成功");
}
else
{
throw new Exception("没有数据");
}
}
catch (Exception ee)
{
tran.Rollback();
throw new Exception(ee.Message);
}
}
catch (Exception ee)
{
FrmMsgBox f = new FrmMsgBox();
f.Text = "提示";
f.richTextBox1.Text = ee.Message;
f.ShowDialog();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
try
{
gridView6.FocusedRowHandle -= 1;
gridView6.FocusedRowHandle += 1;
}
catch { }
int iCount = 0;
decimal d借sum = 0;
decimal d贷sum = 0;
string sErr = "";
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
if (gridView6.RowCount == 0)
{
throw new Exception("请查询可导入凭证");
}
ArrayList aList_Doc = new ArrayList();
for (int i = 0; i < gridView6.RowCount; i++)
{
bool b = BaseFunction.ReturnBool(gridView6.GetRowCellValue(i, gridCol选择));
if (!b)
{
continue;
}
string sDocNO = gridView6.GetRowCellDisplayText(i, gridColDocumentNo).ToString().Trim();
if (sDocNO == "DBSRMB00793")
{
}
bool bExists = false;
for (int j = 0; j < aList_Doc.Count; j++)
{
if (sDocNO == aList_Doc[j].ToString().Trim())
{
bExists = true;
break;
}
}
if (bExists)
{
continue;
}
aList_Doc.Add(sDocNO);
decimal dC = 0;
decimal dD = 0;
for (int j = i; j < gridView6.RowCount; j++)
{
string sDocNO2 = gridView6.GetRowCellDisplayText(j, gridColDocumentNo).ToString().Trim();
if (sDocNO == sDocNO2)
{
dC = dC + BaseFunction.ReturnDecimal(gridView6.GetRowCellValue(j, gridCol借方), 6);
dD = dD + BaseFunction.ReturnDecimal(gridView6.GetRowCellValue(j, gridCol贷方), 6);
}
}
if (dC != dD)
{
sErr = sErr + "行 " + (i + 1).ToString() + " DocumentNo " + sDocNO + " 借贷不平\n";
continue;
}
}
if (sErr != "")
{
throw new Exception(sErr);
}
DataTable dt币种 = new DataTable();
string sSQL = "SELECT * FROM dbo.GL_mend WHERE iyear = aaaaaa AND iperiod = bbbbbb";
sSQL = sSQL.Replace("aaaaaa", dateEdit1.DateTime.Year.ToString());
sSQL = sSQL.Replace("bbbbbb", dateEdit1.DateTime.Month.ToString());
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("获得模块状态失败");
}
if (BaseFunction.ReturnBool(dt.Rows[0]["bflag"]))
{
throw new Exception("总账已结账");
}
for (int i = 0; i < gridView6.RowCount; i++)
{
bool b = BaseFunction.ReturnBool(gridView6.GetRowCellValue(i, gridCol选择));
if (!b)
{
continue;
}
d借sum = d借sum + BaseFunction.ReturnDecimal(gridView6.GetRowCellValue(i, gridCol借方));
d贷sum = d贷sum + BaseFunction.ReturnDecimal(gridView6.GetRowCellValue(i, gridCol贷方));
}
if (d借sum != d贷sum)
{
throw new Exception("借贷不平");
}
DataTable dt凭证 = (DataTable)gridControl6.DataSource;
sSQL = "select isnull(max(ino_id),0) from GL_accvouch where iyear = aaaaaa AND iperiod = bbbbbb and csign = '记'";
sSQL = sSQL.Replace("aaaaaa", dateEdit1.DateTime.Year.ToString());
sSQL = sSQL.Replace("bbbbbb", dateEdit1.DateTime.Month.ToString());
DataTable dtinoid = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
int ino_id = BaseFunction.ReturnInt(dtinoid.Rows[0][0]);
ArrayList aList = new ArrayList();
for (int i = 0; i < gridView6.RowCount; i++)
{
bool b = BaseFunction.ReturnBool(gridView6.GetRowCellValue(i, gridCol选择));
if (!b)
{
continue;
}
if (BaseFunction.ReturnDate(gridView6.GetRowCellValue(i, gridCol制单日期)).Year != dateEdit1.DateTime.Year)
{
sErr = sErr + "行" + (i + 1).ToString() + "制单日期不在账期内\n";
}
if (BaseFunction.ReturnDate(gridView6.GetRowCellValue(i, gridCol制单日期)).Month != dateEdit1.DateTime.Month)
{
sErr = sErr + "行" + (i + 1).ToString() + "制单日期不在账期内\n";
}
string sDocment = gridView6.GetRowCellValue(i, gridColDocumentNo).ToString().ToLower().Trim();
bool bExists = false;
for (int j = 0; j < aList.Count; j++)
{
if (sDocment == aList[j].ToString().ToLower().Trim())
{
bExists = true;
break;
}
}
if (bExists)
{
continue;
}
aList.Add(sDocment);
int inid = 0;
ino_id = ino_id + 1;
for (int j = 0; j < gridView6.RowCount; j++)
{
string sDocment2 = gridView6.GetRowCellValue(j, gridColDocumentNo).ToString().Trim().ToLower();
if (sDocment != sDocment2)
{
continue;
}
inid += 1;
Model.GL_accvouch model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.GL_accvouch();
model.iperiod = dateEdit1.DateTime.Month;
model.csign = gridView6.GetRowCellValue(j, gridCol凭证类别).ToString().Trim();
model.isignseq = 4;
model.ino_id = ino_id;
model.inid = inid;
model.dbill_date = BaseFunction.ReturnDate(gridView6.GetRowCellValue(j, gridCol制单日期));
model.idoc = -1;
model.cbill = gridView6.GetRowCellValue(j, gridCol制单人).ToString().Trim();
model.ibook = 0;
string s摘要 = gridView6.GetRowCellValue(j, gridCol摘要).ToString().Trim();
if (s摘要.Length > 60)
{
s摘要 = s摘要.Substring(0, 60);
}
model.cdigest = s摘要;
model.ccode = gridView6.GetRowCellValue(j, gridCol会计科目).ToString().Trim();
model.cDefine1 = sDocment2;
model.md = BaseFunction.ReturnDecimal(gridView6.GetRowCellValue(j, gridCol借方));
model.mc = BaseFunction.ReturnDecimal(gridView6.GetRowCellValue(j, gridCol贷方));
model.md_f = BaseFunction.ReturnDecimal(gridView6.GetRowCellValue(j, gridCol外币借方));
model.mc_f = BaseFunction.ReturnDecimal(gridView6.GetRowCellValue(j, gridCol外币贷方));
model.nfrat = BaseFunction.ReturnDecimal(gridView6.GetRowCellValue(j, gridCol汇率));
model.nd_s = 0;
model.nc_s = 0;
//model.csettle = ""; //结算方式
//model.cn_id
//model.dt_date =
DataRow[] dr凭证;
if (model.md > 0)
{
string s = "贷方>0 and 凭证类别 = '" + model.csign + "' and DocumentNo = '" + gridView6.GetRowCellValue(j, gridColDocumentNo).ToString() + "'";
dr凭证 = dt凭证.Select(s);
}
else
{
string s = "借方>0 and 凭证类别 = '" + model.csign + "' and DocumentNo = '" + gridView6.GetRowCellValue(j, gridColDocumentNo).ToString() + "'";
dr凭证 = dt凭证.Select(s);
}
string s对方科目 = "";
for (int k = 0; k < dr凭证.Length; k++)
{
if (s对方科目 == "")
{
s对方科目 = dr凭证[k]["会计科目"].ToString().Trim();
}
else
{
string sTemp = s对方科目 + "," + dr凭证[k]["会计科目"].ToString().Trim();
if (sTemp.Length < 50)
{
if (s对方科目.IndexOf(dr凭证[k]["会计科目"].ToString().Trim()) < 0)
{
s对方科目 = s对方科目 + "," + dr凭证[k]["会计科目"].ToString().Trim();
}
}
}
}
if (s对方科目.Length > 50)
{
s对方科目 = s对方科目.Substring(0, 50);
}
model.ccode_equal = s对方科目;
model.bdelete = false;
model.doutbilldate = model.dbill_date;
model.bvouchedit = true;
model.bvouchAddordele = false;
model.bvouchmoneyhold = false;
model.bvalueedit = true;
model.bcodeedit = true;
model.bPCSedit = true;
model.bDeptedit = true;
model.bItemedit = true;
model.bCusSupInput = false;
model.bFlagOut = false;
model.RowGuid = Guid.NewGuid().ToString();
model.iyear = dateEdit1.DateTime.Year;
model.iYPeriod = BaseFunction.ReturnInt(dateEdit1.DateTime.ToString("yyyyMM"));
model.tvouchtime = DateTime.Now;
model.ccodeexch_equal = s对方科目;
if (gridView6.GetRowCellValue(j, gridCol客户编码).ToString() != "")
{
model.ccus_id = gridView6.GetRowCellValue(j, gridCol客户编码).ToString();
}
if (gridView6.GetRowCellValue(j, gridCol供应商编码).ToString() != "")
{
model.csup_id = gridView6.GetRowCellValue(j, gridCol供应商编码).ToString();
}
DAL.GL_accvouch dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch();
sSQL = dal.Add(model);
iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
}
if (sErr.Length > 0)
{
throw new Exception(sErr);
}
if (iCount > 0)
{
tran.Commit();
MessageBox.Show("保存成功");
btnSel_Click(null, null);
}
else
{
throw new Exception("没有数据");
}
}
catch (Exception ee)
{
tran.Rollback();
throw new Exception(ee.Message);
}
}
catch (Exception ee)
{
FrmMsgBox f = new FrmMsgBox();
f.Text = "提示";
f.richTextBox1.Text = ee.Message;
f.ShowDialog();
}
}
private void btnCreateRedVoucher_Click(object sender, EventArgs e)
{
try
{
int iRow = gridView1.FocusedRowHandle;
if (iRow < 0)
{
throw new Exception("Please choose data");
}
int iCou = 0;
int iYearSource = BaseFunction.ReturnInt(gridView1.GetRowCellValue(iRow, gridColiYear));
int iPeriodSource = BaseFunction.ReturnInt(gridView1.GetRowCellValue(iRow, gridColiPeriod));
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string[] s = lLoginPeriod.Text.Trim().Split('.');
int iYear = BaseFunction.ReturnInt(s[0]);
int iPeriod = BaseFunction.ReturnInt(s[1]);
string sSQL = @"
SELECT bflag
FROM dbo.GL_mend
WHERE iyear = aaaaaa AND iperiod = bbbbbb";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("Failed to obtain module status");
}
if (BaseFunction.ReturnBool(dt.Rows[0]["bflag"]))
{
throw new Exception("The current date has been checked out");
}
#region 生成红冲凭证
sSQL = @"
select *
from GL_accvouch
where iyear = aaaaaa and iperiod = bbbbbb and isnull(iflag,0) = 0
and ino_id = 'cccccc' and csign = 'dddddd'
";
sSQL = sSQL.Replace("aaaaaa", iYearSource.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriodSource.ToString());
string sGLVoucherNO = gridView1.GetRowCellValue(iRow, gridColGLVoucherNO).ToString().Trim();
if (sGLVoucherNO == "")
{
throw new Exception("Please choose source");
}
string[] sGlSource = sGLVoucherNO.Split('-');
sSQL = sSQL.Replace("cccccc", sGlSource[1]);
sSQL = sSQL.Replace("dddddd", sGlSource[0]);
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
MessageBox.Show("No data.Please manually generate the red document");
}
else
{
string sGLRedVouch = gridView1.GetRowCellValue(iRow, gridColRedGLVoucherNO).ToString().Trim();
if (sGLRedVouch != "")
{
sGlSource = sGLRedVouch.Split('-');
sSQL = @"
select *
from GL_accvouch
where csign = 'AP' and iyear = aaaaaa and iperiod = bbbbbb and isnull(iflag,0) = 0
and ino_id = 'cccccc' and csign = 'dddddd'
";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
sSQL = sSQL.Replace("cccccc", sGlSource[1]);
sSQL = sSQL.Replace("dddddd", sGlSource[0]);
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
throw new Exception("Accounting vouchers already exist");
}
}
sSQL = "select isnull(max(ino_id),0) as ino_id from GL_accvouch where iyear = aaaaaa AND iperiod = bbbbbb and csign = 'AP'";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
DataTable dtinoid = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
int ino_id = BaseFunction.ReturnInt(dtinoid.Rows[0]["ino_id"]);
ino_id += 1;
string sVoucher = "AP-" + ino_id.ToString().PadLeft(4, '0') + "\n";
for (int i = 0; i < dt.Rows.Count; i++)
{
Model.GL_accvouch mod = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.GL_accvouch();
DAL.GL_accvouch dal = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch();
mod = dal.DataRowToModel(dt.Rows[i]);
mod.ino_id = ino_id;
mod.iyear = iYear;
mod.iperiod = iPeriod;
mod.iYPeriod = BaseFunction.ReturnInt(BaseFunction.ReturnDate(iYearSource.ToString() + "-" + iPeriod.ToString() + "-01").ToString("yyyyMM"));
mod.md = -1 * mod.md;
mod.mc = -1 * mod.mc;
mod.md_f = -1 * mod.md_f;
mod.md_f = -1 * mod.md_f;
sSQL = dal.Add(mod);
iCou += DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
if (iCou > 0)
{
sSQL = @"
select *
from GL_accvouch
where iyear = {0} and iPeriod = {1} and csign = '{2}' and ino_id = {3}
";
sSQL = string.Format(sSQL, iYear.ToString(), iPeriod.ToString(), "AP", ino_id);
DataTable dtGL = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
sSQL = @"
update [dbo].[_AmountOfExchangeProfitAndLoss] set [RedcSign] = 'aaaaaaaa',[Redi_id] = bbbbbbbb,[Redino_id] = 'iiiiiiii'
,[SourceiYear] =cccccccc ,[SourceiPeriod] = dddddddd,[SourcecSign] = 'eeeeeeee',[Sourceino_id] = ffffffff
where iYear = gggggggg and iPeriod = hhhhhhhh
";
sSQL = sSQL.Replace("aaaaaaaa", sGlSource[0]);
sSQL = sSQL.Replace("bbbbbbbb", dtGL.Rows[0]["i_id"].ToString());
sSQL = sSQL.Replace("cccccccc", iYearSource.ToString());
sSQL = sSQL.Replace("dddddddd", iPeriodSource.ToString());
sSQL = sSQL.Replace("eeeeeeee", sGlSource[0]);
sSQL = sSQL.Replace("ffffffff", sGlSource[1]);
sSQL = sSQL.Replace("gggggggg", iYear.ToString());
sSQL = sSQL.Replace("hhhhhhhh", iPeriod.ToString());
sSQL = sSQL.Replace("iiiiiiii", BaseFunction.ReturnInt(sVoucher.Split('-')[1]).ToString().Trim());
int iCount = DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
if (iCount == 0)
{
throw new Exception("Please CreateVoucher first");
}
}
}
#endregion
if (iCou > 0)
{
tran.Commit();
MessageBox.Show("Sucess");
btnQuery_Click(null, null);
}
else
{
throw new Exception("Err:no data");
}
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
FrmMsgBox f = new FrmMsgBox();
f.Text = "Err";
f.richTextBox1.Text = ee.Message;
f.ShowDialog();
}
}
private void btnCreateVoucher_Click(object sender, EventArgs e)
{
try
{
int iRow = gridView1.FocusedRowHandle;
if (iRow < 0)
{
throw new Exception("Please choose data");
}
int iYear = BaseFunction.ReturnInt(gridView1.GetRowCellValue(iRow, gridColiYear));
int iPeriod = BaseFunction.ReturnInt(gridView1.GetRowCellValue(iRow, gridColiPeriod));
string sVoucher = "";
int iCount = 0;
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
//启用事务
SqlTransaction tran = conn.BeginTransaction();
try
{
string sSQL = "SELECT bflag FROM dbo.GL_mend WHERE iyear = aaaaaa AND iperiod = bbbbbb";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
DataTable dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("Failed to obtain module status");
}
if (BaseFunction.ReturnBool(dt.Rows[0]["bflag"]))
{
throw new Exception("The current date has been checked out");
}
sSQL = @"
select *
from GL_accvouch gl inner join _AmountOfExchangeProfitAndLoss b
on gl.iyear = b.iYear and gl.iperiod = b.iPeriod and gl.i_id = b.i_id and gl.csign = b.csign
where gl.csign = 'AP' and gl.iyear = aaaaaa and gl.iperiod = bbbbbb and isnull(gl.iflag,0) = 0
";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
throw new Exception("Accounting vouchers already exist");
}
sSQL = @"
select * from _Code where VouchType = 'Exchange gain or loss'
";
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("Please set aounting subject [_Code.Exchange gain or loss]");
}
string sKMD = dt.Rows[0]["Debtor"].ToString().Trim();
string sKMC = dt.Rows[0]["Creditor"].ToString().Trim();
string sZY = dt.Rows[0]["Remark"].ToString().Trim();
if (sKMD == "" || sKMC == "")
{
throw new Exception("Please set aounting subject [_Code.Exchange gain or loss]");
}
sSQL = "select isnull(max(ino_id),0) from GL_accvouch where iyear = aaaaaa AND iperiod = bbbbbb and csign = 'AP'";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
DataTable dtinoid = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
int ino_id = BaseFunction.ReturnInt(dtinoid.Rows[0][0]) + 1;
sSQL = @"
select sum(AmountOfExchangeProfitAndLoss) as AmountOfExchangeProfitAndLoss ,rd.cVenCode
from _AmountOfExchangeProfitAndLoss a
left join RdRecord01 rd on a.cCode = rd.cCode
where a.iyear = aaaaaa and a.iperiod = bbbbbb
group by rd.cVenCode
";
sSQL = sSQL.Replace("aaaaaa", iYear.ToString());
sSQL = sSQL.Replace("bbbbbb", iPeriod.ToString());
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
if (dt == null || dt.Rows.Count == 0)
{
throw new Exception("Please do the math first");
}
decimal dSum = 0;// BaseFunction.ReturnDecimal(gridView1.GetRowCellValue(iRow, gridColAmountOfExchangeProfitAndLoss), 2);
for (int i = 0; i < dt.Rows.Count; i++)
{
dSum = dSum + BaseFunction.ReturnDecimal(dt.Rows[i]["AmountOfExchangeProfitAndLoss"], 2);
}
sVoucher = sVoucher + "AP-" + ino_id.ToString().PadLeft(4, '0') + "\n";
#region 生成凭证
#region 借方
Model.GL_accvouch model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.GL_accvouch();
model.iperiod = iPeriod;
model.csign = "AP";
model.isignseq = 2;
model.ino_id = ino_id;
model.inid = 1;
model.dbill_date = BaseFunction.ReturnDate(sLogDate);
model.idoc = -1;
model.cbill = sUserName;
model.ibook = 0;
model.cdigest = sZY;
model.ccode = sKMD;
//model.cDefine1 = sDocment2;
model.md = BaseFunction.ReturnDecimal(dSum, 2);
model.mc = 0;
model.md_f = 0;
model.mc_f = 0;
model.nfrat = 0;
model.nd_s = 0;
model.nc_s = 0;
//model.csettle = ""; //结算方式
//model.cn_id
//model.dt_date =
model.ccode_equal = sKMC;;
model.bdelete = false;
//model.doutbilldate = model.dbill_date;
model.bvouchedit = true;
model.bvouchAddordele = false;
model.bvouchmoneyhold = false;
model.bvalueedit = true;
model.bcodeedit = true;
model.bPCSedit = true;
model.bDeptedit = true;
model.bItemedit = true;
model.bCusSupInput = false;
model.bFlagOut = false;
model.RowGuid = Guid.NewGuid().ToString();
model.iyear = iYear;
model.iYPeriod = BaseFunction.ReturnInt(BaseFunction.ReturnDate(iYear.ToString() + "-" + iPeriod.ToString() + "-01").ToString("yyyyMM"));
model.tvouchtime = DateTime.Now;
model.ccodeexch_equal = sKMC;
UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch dalGL = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch();
sSQL = dalGL.Add(model);
iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
#endregion
#region 贷方
for (int i = 0; i < dt.Rows.Count; i++)
{
model = new UFIDA.U8.UAP.CustomApp.ControlForm.Model.GL_accvouch();
model.iperiod = iPeriod;
model.csign = "AP";
model.isignseq = 2;
model.ino_id = ino_id;
model.inid = 2;
model.dbill_date = BaseFunction.ReturnDate(sLogDate);
model.idoc = -1;
model.cbill = sUserName;
model.ibook = 0;
model.cdigest = sZY;
model.ccode = sKMC;
//model.cDefine1 = sDocment2;
model.md = 0;
model.mc = BaseFunction.ReturnDecimal(dt.Rows[i]["AmountOfExchangeProfitAndLoss"], 2);
model.md_f = 0;
model.mc_f = 0;
model.nfrat = 0;
model.nd_s = 0;
model.nc_s = 0;
//model.csettle = ""; //结算方式
//model.cn_id
//model.dt_date =
model.ccode_equal = sKMD;;
model.bdelete = false;
model.doutbilldate = model.dbill_date;
model.bvouchedit = true;
model.bvouchAddordele = false;
model.bvouchmoneyhold = false;
model.bvalueedit = true;
model.bcodeedit = true;
model.bPCSedit = true;
model.bDeptedit = true;
model.bItemedit = true;
model.bCusSupInput = false;
model.bFlagOut = false;
model.RowGuid = Guid.NewGuid().ToString();
model.iyear = iYear;
model.iYPeriod = BaseFunction.ReturnInt(BaseFunction.ReturnDate(iYear.ToString() + "-" + iPeriod.ToString() + "-01").ToString("yyyyMM"));
model.tvouchtime = DateTime.Now;
model.ccodeexch_equal = sKMD;
model.csup_id = dt.Rows[i]["cVenCode"].ToString().Trim();
dalGL = new UFIDA.U8.UAP.CustomApp.ControlForm.DAL.GL_accvouch();
sSQL = dalGL.Add(model);
iCount = iCount + DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
}
#endregion
#endregion
sSQL = @"
select *
from GL_accvouch
where iyear = {0} and iPeriod = {1} and csign = '{2}' and ino_id = {3}
";
sSQL = string.Format(sSQL, model.iyear, model.iperiod, model.csign, model.ino_id);
DataTable dtGL = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
long i_id = BaseFunction.ReturnLong(dtGL.Rows[0]["i_id"]);
sSQL = @"
update _AmountOfExchangeProfitAndLoss set csign = 'AP',i_id = 'dddddddd',ino_id = 'eeeeee'
where iyear = bbbbbbbb and iperiod = cccccccc
";
sSQL = sSQL.Replace("bbbbbbbb", model.iyear.ToString());
sSQL = sSQL.Replace("cccccccc", model.iperiod.ToString());
sSQL = sSQL.Replace("dddddddd", i_id.ToString().Trim());
sSQL = sSQL.Replace("eeeeee", model.ino_id.ToString().Trim());
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
tran.Commit();
MessageBox.Show("Sucess\n" + sVoucher);
btnQuery_Click(null, null);
}
catch (Exception error)
{
tran.Rollback();
throw new Exception(error.Message);
}
}
catch (Exception ee)
{
FrmMsgBox f = new FrmMsgBox();
f.Text = "Err";
f.richTextBox1.Text = ee.Message;
f.ShowDialog();
}
}
