/// <summary>
/// 增加一条数据
/// </summary>
public string Add(UFIDA.U8.UAP.CustomApp.ControlForm.Model._AmountOfExchangeProfitAndLoss model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.iYear != null)
{
strSql1.Append("iYear,");
strSql2.Append("" + model.iYear + ",");
}
if (model.iPeriod != null)
{
strSql1.Append("iPeriod,");
strSql2.Append("" + model.iPeriod + ",");
}
if (model.AutoID != null)
{
strSql1.Append("AutoID,");
strSql2.Append("" + model.AutoID + ",");
}
if (model.cCode != null)
{
strSql1.Append("cCode,");
strSql2.Append("'" + model.cCode + "',");
}
if (model.irowno != null)
{
strSql1.Append("irowno,");
strSql2.Append("" + model.irowno + ",");
}
if (model.cExch_Name != null)
{
strSql1.Append("cExch_Name,");
strSql2.Append("'" + model.cExch_Name + "',");
}
if (model.dDate != null)
{
strSql1.Append("dDate,");
strSql2.Append("'" + model.dDate + "',");
}
if (model.cInvCode != null)
{
strSql1.Append("cInvCode,");
strSql2.Append("'" + model.cInvCode + "',");
}
if (model.cInvName != null)
{
strSql1.Append("cInvName,");
strSql2.Append("'" + model.cInvName + "',");
}
if (model.cInvStd != null)
{
strSql1.Append("cInvStd,");
strSql2.Append("'" + model.cInvStd + "',");
}
if (model.iOriSum != null)
{
strSql1.Append("iOriSum,");
strSql2.Append("" + model.iOriSum + ",");
}
if (model.nflat != null)
{
strSql1.Append("nflat,");
strSql2.Append("" + model.nflat + ",");
}
if (model.nflat2 != null)
{
strSql1.Append("nflat2,");
strSql2.Append("" + model.nflat2 + ",");
}
if (model.AmountOfExchangeProfitAndLoss != null)
{
strSql1.Append("AmountOfExchangeProfitAndLoss,");
strSql2.Append("" + model.AmountOfExchangeProfitAndLoss + ",");
}
if (model.ino_id != null)
{
strSql1.Append("ino_id,");
strSql2.Append("'" + model.ino_id + "',");
}
if (model.cSign != null)
{
strSql1.Append("csign,");
strSql2.Append("'" + model.cSign + "',");
}
if (model.i_ID != null)
{
strSql1.Append("i_ID,");
strSql2.Append("" + model.i_ID + ",");
}
if (model.redi_ID != null)
{
strSql1.Append("redi_ID,");
strSql2.Append("" + model.redi_ID + ",");
}
strSql.Append("insert into _AmountOfExchangeProfitAndLoss(");
strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
strSql.Append(")");
return(strSql.ToString());
}
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();
}
}
