/// <summary>
/// 添加
/// </summary>
/// <param name="pDataObject"></param>
/// <param name="sqLiteTransaction"></param>
public void Add(Obj_YIJDF_ZZ_2 pDataObject, SQLiteTransaction sqLiteTransaction)
{
const string mySqlString =
"insert into YIJDF_ZZ_2 (DATE,NAME,GWGZ,XJGZ,ZWBT,JXGZ,TENPERCENT,XJ,JSJE,YKSJ,DFJS,YJDF,YJDFQZ,JFQZYF) values ($DATE,$NAME,$GWGZ,$XJGZ,$ZWBT,$JXGZ,$TENPERCENT,$XJ,$JSJE,$YKSJ,$DFJS,$YJDF,$YJDFQZ,$JFQZYF)";
using (SQLiteCommand myCommand = sqLiteTransaction.Connection.CreateCommand())
{
myCommand.CommandText = mySqlString;
myCommand.Parameters.Add(new SQLiteParameter("DATE", pDataObject.DATE));
myCommand.Parameters.Add(new SQLiteParameter("NAME", pDataObject.Name));
myCommand.Parameters.Add(new SQLiteParameter("GWGZ", pDataObject.GWGZ));
myCommand.Parameters.Add(new SQLiteParameter("XJGZ", pDataObject.XJGZ));
myCommand.Parameters.Add(new SQLiteParameter("ZWBT", pDataObject.ZWBT));
myCommand.Parameters.Add(new SQLiteParameter("JXGZ", pDataObject.JXGZ));
myCommand.Parameters.Add(new SQLiteParameter("TENPERCENT", pDataObject.TENPERCENT));
myCommand.Parameters.Add(new SQLiteParameter("XJ", pDataObject.XJ));
myCommand.Parameters.Add(new SQLiteParameter("JSJE", pDataObject.JSJE));
myCommand.Parameters.Add(new SQLiteParameter("YKSJ", pDataObject.YKSJ));
myCommand.Parameters.Add(new SQLiteParameter("DFJS", pDataObject.DFJS));
myCommand.Parameters.Add(new SQLiteParameter("YJDF", pDataObject.YJDF));
myCommand.Parameters.Add(new SQLiteParameter("YJDFQZ", pDataObject.YJDFQZ));
myCommand.Parameters.Add(new SQLiteParameter("JFQZYF", pDataObject.JFQZYF));
myCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="pDataObject"></param>
/// <param name="sqLiteTransaction"></param>
public void Update(Obj_YIJDF_ZZ_2 pDataObject, SQLiteTransaction sqLiteTransaction)
{
const string mySqlString =
"update YIJDF_ZZ_2 set GWGZ=$GWGZ,XJGZ=$XJGZ,ZWBT=$ZWBT,JXGZ=$JXGZ,TENPERCENT=$TENPERCENT,XJ=$XJ,JSJE=$JSJE,YKSJ=$YKSJ,DFJS=$DFJS,YJDF=$YJDF,YJDFQZ=$YJDFQZ,JFQZYF=$JFQZYF where NAME=$NAME and DATE=$DATE";
using (SQLiteCommand myCommand = sqLiteTransaction.Connection.CreateCommand())
{
myCommand.CommandText = mySqlString;
myCommand.Parameters.Add(new SQLiteParameter("DATE", pDataObject.DATE));
myCommand.Parameters.Add(new SQLiteParameter("NAME", pDataObject.Name));
myCommand.Parameters.Add(new SQLiteParameter("GWGZ", pDataObject.DFJS));
myCommand.Parameters.Add(new SQLiteParameter("XJGZ", pDataObject.DFJS));
myCommand.Parameters.Add(new SQLiteParameter("ZWBT", pDataObject.DFJS));
myCommand.Parameters.Add(new SQLiteParameter("JXGZ", pDataObject.DFJS));
myCommand.Parameters.Add(new SQLiteParameter("TENPERCENT", pDataObject.DFJS));
myCommand.Parameters.Add(new SQLiteParameter("XJ", pDataObject.DFJS));
myCommand.Parameters.Add(new SQLiteParameter("JSJE", pDataObject.DFJS));
myCommand.Parameters.Add(new SQLiteParameter("YKSJ", pDataObject.DFJS));
myCommand.Parameters.Add(new SQLiteParameter("DFJS", pDataObject.DFJS));
myCommand.Parameters.Add(new SQLiteParameter("YJDF", pDataObject.YJDF));
myCommand.Parameters.Add(new SQLiteParameter("YJDFQZ", pDataObject.YJDFQZ));
myCommand.Parameters.Add(new SQLiteParameter("JFQZYF", pDataObject.JFQZYF));
myCommand.ExecuteNonQuery();
}
}
private void Save2DBYIJDF_ZZ_2(DataSet dataSet)
{
SqlLiteCommon sqlLite = new SqlLiteCommon();
foreach (DataTable dt in dataSet.Tables)
{
if (!dt.TableName.Contains("退休") && !dt.TableName.Contains("补缴党费"))
{
ArrayList objArr = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
Obj_YIJDF_ZZ_2 obj = new Obj_YIJDF_ZZ_2();
if (!dt.Columns.Contains("姓名"))//当表头中不包含姓名时，重新绑定表头并删除第一行数据
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dr[i].ToString() != null && dr[i].ToString() != string.Empty)
dt.Columns[i].ColumnName = dr[i].ToString().Replace(" ", "").Replace(Convert.ToChar(10).ToString(), "");
}
dr.Delete();
}
else
{
if (dr["姓名"].ToString().Trim() != string.Empty)
{
try
{
string dateSpan = dr["缴费起止月份"].ToString();
string[] dateSpanArr = DateTrans(dateSpan);
if (dateSpanArr != null)
{
foreach (string date in dateSpanArr)
{
obj = new Obj_YIJDF_ZZ_2();
obj.JFQZYF = dateSpan;
obj.Name = dr["姓名"].ToString().Trim();
obj.GWGZ =
Convert.ToDouble(dr["岗位工资"].ToString() == string.Empty ? "0" : dr["岗位工资"].ToString());
obj.XJGZ = Convert.ToDouble(dr["薪级"].ToString() == string.Empty ? "0" : dr["薪级"].ToString());
obj.ZWBT =
Convert.ToDouble(dr["职务补贴"].ToString() == string.Empty ? "0" : dr["职务补贴"].ToString());
obj.JXGZ =
Convert.ToDouble(dr["绩效工资"].ToString() == string.Empty ? "0" : dr["绩效工资"].ToString());
obj.TENPERCENT =
Convert.ToDouble(dr["0.1"].ToString() == string.Empty ? "0" : dr["0.1"].ToString());
obj.XJ = Convert.ToDouble(dr["小计"].ToString() == string.Empty ? "0" : dr["小计"].ToString());
obj.YKSJ =
Convert.ToDouble(dr["应扣税金"].ToString() == string.Empty ? "0" : dr["应扣税金"].ToString());
obj.DFJS =
Convert.ToDouble(dr["党费基数"].ToString() == string.Empty ? "0" : dr["党费基数"].ToString());
obj.JSJE =
Convert.ToDouble(dr["计税金额"].ToString() == string.Empty ? "0" : dr["计税金额"].ToString());
obj.YJDF =
Convert.ToDouble(dr["月缴党费"].ToString() == string.Empty ? "0" : dr["月缴党费"].ToString());
obj.YJDFQZ =
Convert.ToDouble(dr["保留整数"].ToString() == string.Empty ? "0" : dr["保留整数"].ToString());
obj.DATE = Convert.ToDateTime(date);
objArr.Add(obj);
}
}
else
{
MessageBox.Show("文件 " + dataSet.DataSetName + " 的表 " + dt.TableName + "中的记录 '" +
obj.Name + "' '缴费起止月份' 有误! 未成功录入");
}
}
catch (Exception ex)
{
MessageBox.Show("文件 " + dataSet.DataSetName + " 的表 " + dt.TableName + "中的记录 '" +
obj.Name + "' 存在问题！\n" + ex.Message);
}
}
}
}
sqlLite.Add(objArr);
}
}
}