/// <summary>
/// 增加一条数据
/// </summary>
public string Add(基础设置.Model.客户协议登记表 model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSql1 = new StringBuilder();
StringBuilder strSql2 = new StringBuilder();
if (model.GUID != null)
{
strSql1.Append("GUID,");
strSql2.Append("'" + model.GUID + "',");
}
if (model.iYear != null)
{
strSql1.Append("iYear,");
strSql2.Append("" + model.iYear + ",");
}
if (model.代理商 != null)
{
strSql1.Append("代理商,");
strSql2.Append("'" + model.代理商 + "',");
}
if (model.dDate1 != null)
{
strSql1.Append("dDate1,");
strSql2.Append("'" + model.dDate1 + "',");
}
if (model.dDate2 != null)
{
strSql1.Append("dDate2,");
strSql2.Append("'" + model.dDate2 + "',");
}
if (model.返点方式 != null)
{
strSql1.Append("返点方式,");
strSql2.Append("'" + model.返点方式 + "',");
}
if (model.品种 != null)
{
strSql1.Append("品种,");
strSql2.Append("'" + model.品种 + "',");
}
if (model.底价 != null)
{
strSql1.Append("底价,");
strSql2.Append("" + model.底价 + ",");
}
if (model.协议销量 != null)
{
strSql1.Append("协议销量,");
strSql2.Append("" + model.协议销量 + ",");
}
if (model.保证金 != null)
{
strSql1.Append("保证金,");
strSql2.Append("" + model.保证金 + ",");
}
if (model.M1 != null)
{
strSql1.Append("M1,");
strSql2.Append("" + model.M1 + ",");
}
if (model.M2 != null)
{
strSql1.Append("M2,");
strSql2.Append("" + model.M2 + ",");
}
if (model.M3 != null)
{
strSql1.Append("M3,");
strSql2.Append("" + model.M3 + ",");
}
if (model.M4 != null)
{
strSql1.Append("M4,");
strSql2.Append("" + model.M4 + ",");
}
if (model.M5 != null)
{
strSql1.Append("M5,");
strSql2.Append("" + model.M5 + ",");
}
if (model.M6 != null)
{
strSql1.Append("M6,");
strSql2.Append("" + model.M6 + ",");
}
if (model.M7 != null)
{
strSql1.Append("M7,");
strSql2.Append("" + model.M7 + ",");
}
if (model.M8 != null)
{
strSql1.Append("M8,");
strSql2.Append("" + model.M8 + ",");
}
if (model.M9 != null)
{
strSql1.Append("M9,");
strSql2.Append("" + model.M9 + ",");
}
if (model.M10 != null)
{
strSql1.Append("M10,");
strSql2.Append("" + model.M10 + ",");
}
if (model.M11 != null)
{
strSql1.Append("M11,");
strSql2.Append("" + model.M11 + ",");
}
if (model.M12 != null)
{
strSql1.Append("M12,");
strSql2.Append("" + model.M12 + ",");
}
if (model.CreateUid != null)
{
strSql1.Append("CreateUid,");
strSql2.Append("'" + model.CreateUid + "',");
}
if (model.CreateDate != null)
{
strSql1.Append("CreateDate,");
strSql2.Append("'" + model.CreateDate + "',");
}
if (model.AuditUid != null)
{
strSql1.Append("AuditUid,");
strSql2.Append("'" + model.AuditUid + "',");
}
if (model.AuditDate != null)
{
strSql1.Append("AuditDate,");
strSql2.Append("'" + model.AuditDate + "',");
}
strSql.Append("insert into 客户协议登记表(");
strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
strSql.Append(")");
strSql.Append(";select @@IDENTITY");
return(strSql.ToString());
}
/// <summary>
/// 保存
/// </summary>
private void btnSave()
{
try
{
try
{
gridView1.FocusedRowHandle -= 1;
gridView1.FocusedRowHandle += 1;
gridView2.FocusedRowHandle -= 1;
gridView2.FocusedRowHandle += 1;
}
catch { }
if (lookUpEditYear.Text.Trim() == "")
{
lookUpEditYear.Focus();
throw new Exception("请设置年度");
}
if (lookUpEdit代理商.EditValue == null || lookUpEdit代理商.Text.Trim() == "")
{
btnTxtDLS.Focus();
throw new Exception("请选择代理商");
}
string s代理商编码 = lookUpEdit代理商.EditValue.ToString().Trim();
//DateTime dtm1 = dateEdit1.DateTime;
//DateTime dtm2 = dateEdit2.DateTime;
string s返点方式 = lookUpEdit返点方式.Text.Trim();
if (s返点方式 == "")
{
lookUpEdit返点方式.Focus();
throw new Exception("请选择返点方式");
}
string sErr = "";
aList = new System.Collections.ArrayList();
bool b = false;
Guid sGuid = Guid.NewGuid();
sSQL = @"
select GUID from 客户协议登记表 where iYear = aaaaaaaa and 代理商 = 'bbbbbbbb'
";
sSQL = sSQL.Replace("aaaaaaaa", lookUpEditYear.Text.Trim());
sSQL = sSQL.Replace("bbbbbbbb", s代理商编码);
DataTable dtGuid = clsSQLCommond.ExecQuery(sSQL);
if (dtGuid != null && dtGuid.Rows.Count > 0)
{
if (dtGuid.Rows[0]["GUID"].ToString().Trim() != "")
{
sGuid = (Guid)dtGuid.Rows[0]["GUID"];
}
}
sSQL = @"
delete 客户协议登记表 where iYear = aaaaaaaa and 代理商 = 'bbbbbbbb'
";
sSQL = sSQL.Replace("aaaaaaaa", lookUpEditYear.Text.Trim());
sSQL = sSQL.Replace("bbbbbbbb", s代理商编码);
aList.Add(sSQL);
for (int i = 0; i < gridView1.RowCount; i++)
{
Model.客户协议登记表 mod = new 基础设置.Model.客户协议登记表();
string s编码 = gridView1.GetRowCellValue(i, gridCol编码).ToString().Trim();
if (s编码 == "")
{
continue;
}
sSQL = @"
select count(1)
from U8Inventory
where cInvCode = 'aaaaaaaa'
";
sSQL = sSQL.Replace("aaaaaaaa", s编码);
DataTable dt = clsSQLCommond.ExecQuery(sSQL);
if (dt == null || dt.Rows.Count == 0)
{
sErr = sErr + "行 " + (i + 1).ToString() + " 品种不存在\n";
continue;
}
mod.GUID = sGuid;
mod.iYear = ReturnInt(lookUpEditYear.Text);
mod.代理商 = s代理商编码;
if (gridView1.GetRowCellDisplayText(i, gridColdtmStart).ToString().Trim() == "")
{
sErr = sErr + "行" + (i + 1).ToString() + "协议开始日期不能为空\n";
}
mod.dDate1 = Convert.ToDateTime(gridView1.GetRowCellDisplayText(i, gridColdtmStart));
if (gridView1.GetRowCellDisplayText(i, gridColdtmEnd).ToString().Trim() == "")
{
sErr = sErr + "行" + (i + 1).ToString() + "协议结束日期不能为空\n";
}
mod.dDate2 = Convert.ToDateTime(gridView1.GetRowCellDisplayText(i, gridColdtmEnd));
mod.返点方式 = s返点方式;
mod.品种 = s编码;
//mod.或有条款 = gridView1.GetRowCellDisplayText(i, gridCol或有条款).ToString().Trim();
if (gridView1.GetRowCellValue(i, gridCol底价).ToString().Trim() != "")
{
mod.底价 = ReturnDecimal(gridView1.GetRowCellValue(i, gridCol底价), 2);
}
if (gridView1.GetRowCellValue(i, gridCol协议销量).ToString().Trim() != "")
{
mod.协议销量 = ReturnDecimal(gridView1.GetRowCellValue(i, gridCol协议销量), 2);
}
if (gridView1.GetRowCellValue(i, gridCol保证金).ToString().Trim() != "")
{
mod.保证金 = ReturnDecimal(gridView1.GetRowCellValue(i, gridCol保证金), 2);
}
if (gridView1.GetRowCellValue(i, gridColM1).ToString().Trim() != "")
{
mod.M1 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM1), 2);
}
if (gridView1.GetRowCellValue(i, gridColM2).ToString().Trim() != "")
{
mod.M2 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM2), 2);
}
if (gridView1.GetRowCellValue(i, gridColM3).ToString().Trim() != "")
{
mod.M3 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM3), 2);
}
if (gridView1.GetRowCellValue(i, gridColM4).ToString().Trim() != "")
{
mod.M4 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM4), 2);
}
if (gridView1.GetRowCellValue(i, gridColM5).ToString().Trim() != "")
{
mod.M5 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM5), 2);
}
if (gridView1.GetRowCellValue(i, gridColM6).ToString().Trim() != "")
{
mod.M6 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM6), 2);
}
if (gridView1.GetRowCellValue(i, gridColM7).ToString().Trim() != "")
{
mod.M7 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM7), 2);
}
if (gridView1.GetRowCellValue(i, gridColM8).ToString().Trim() != "")
{
mod.M8 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM8), 2);
}
if (gridView1.GetRowCellValue(i, gridColM9).ToString().Trim() != "")
{
mod.M9 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM9), 2);
}
if (gridView1.GetRowCellValue(i, gridColM10).ToString().Trim() != "")
{
mod.M10 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM10), 2);
}
if (gridView1.GetRowCellValue(i, gridColM11).ToString().Trim() != "")
{
mod.M11 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM11), 2);
}
if (gridView1.GetRowCellValue(i, gridColM12).ToString().Trim() != "")
{
mod.M12 = ReturnDecimal(gridView1.GetRowCellValue(i, gridColM12), 2);
}
mod.CreateUid = sUid;
mod.CreateDate = DateTime.Now;
DAL.客户协议登记表 dal = new 基础设置.DAL.客户协议登记表();
sSQL = dal.Add(mod);
aList.Add(sSQL);
b = true;
}
sSQL = @"
delete 客户协议登记表_业务员 where GUID = 'aaaaaaaaaa'
";
sSQL = sSQL.Replace("aaaaaaaaaa", sGuid.ToString());
aList.Add(sSQL);
for (int i = 0; i < gridView2.RowCount; i++)
{
string s业务员 = gridView2.GetRowCellValue(i, gridCol业务员编码).ToString().Trim();
if (s业务员 == "")
{
continue;
}
Model.客户协议登记表_业务员 mod = new 基础设置.Model.客户协议登记表_业务员();
mod.cPersonCode = s业务员;
mod.Remark = gridView2.GetRowCellValue(i, gridCol备注).ToString().Trim();
if (gridView2.GetRowCellValue(i, gridCol开始日期).ToString().Trim() != "")
{
mod.StartDate = ReturnDateTime(gridView2.GetRowCellValue(i, gridCol开始日期));
}
if (gridView2.GetRowCellValue(i, gridCol结束日期).ToString().Trim() != "")
{
mod.ENDDate = ReturnDateTime(gridView2.GetRowCellValue(i, gridCol结束日期));
}
mod.GUID = sGuid;
DAL.客户协议登记表_业务员 dal = new 基础设置.DAL.客户协议登记表_业务员();
sSQL = dal.Add(mod);
aList.Add(sSQL);
}
if (!b)
{
throw new Exception("没有需要保存的数据");
}
if (sErr.Trim() != "")
{
throw new Exception(sErr);
}
if (aList.Count > 0)
{
clsSQLCommond.ExecSqlTran(aList);
MessageBox.Show("保存成功");
btnRefresh();
}
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public string Update(基础设置.Model.客户协议登记表 model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update 客户协议登记表 set ");
if (model.dDate1 != null)
{
strSql.Append("dDate1='" + model.dDate1 + "',");
}
else
{
strSql.Append("dDate1= null ,");
}
if (model.dDate2 != null)
{
strSql.Append("dDate2='" + model.dDate2 + "',");
}
else
{
strSql.Append("dDate2= null ,");
}
if (model.返点方式 != null)
{
strSql.Append("返点方式='" + model.返点方式 + "',");
}
else
{
strSql.Append("返点方式= null ,");
}
if (model.品种 != null)
{
strSql.Append("品种='" + model.品种 + "',");
}
else
{
strSql.Append("品种= null ,");
}
if (model.底价 != null)
{
strSql.Append("底价=" + model.底价 + ",");
}
else
{
strSql.Append("底价= null ,");
}
if (model.协议销量 != null)
{
strSql.Append("协议销量=" + model.协议销量 + ",");
}
else
{
strSql.Append("协议销量= null ,");
}
if (model.保证金 != null)
{
strSql.Append("保证金=" + model.保证金 + ",");
}
else
{
strSql.Append("保证金= null ,");
}
if (model.M1 != null)
{
strSql.Append("M1=" + model.M1 + ",");
}
else
{
strSql.Append("M1= null ,");
}
if (model.M2 != null)
{
strSql.Append("M2=" + model.M2 + ",");
}
else
{
strSql.Append("M2= null ,");
}
if (model.M3 != null)
{
strSql.Append("M3=" + model.M3 + ",");
}
else
{
strSql.Append("M3= null ,");
}
if (model.M4 != null)
{
strSql.Append("M4=" + model.M4 + ",");
}
else
{
strSql.Append("M4= null ,");
}
if (model.M5 != null)
{
strSql.Append("M5=" + model.M5 + ",");
}
else
{
strSql.Append("M5= null ,");
}
if (model.M6 != null)
{
strSql.Append("M6=" + model.M6 + ",");
}
else
{
strSql.Append("M6= null ,");
}
if (model.M7 != null)
{
strSql.Append("M7=" + model.M7 + ",");
}
else
{
strSql.Append("M7= null ,");
}
if (model.M8 != null)
{
strSql.Append("M8=" + model.M8 + ",");
}
else
{
strSql.Append("M8= null ,");
}
if (model.M9 != null)
{
strSql.Append("M9=" + model.M9 + ",");
}
else
{
strSql.Append("M9= null ,");
}
if (model.M10 != null)
{
strSql.Append("M10=" + model.M10 + ",");
}
else
{
strSql.Append("M10= null ,");
}
if (model.M11 != null)
{
strSql.Append("M11=" + model.M11 + ",");
}
else
{
strSql.Append("M11= null ,");
}
if (model.M12 != null)
{
strSql.Append("M12=" + model.M12 + ",");
}
else
{
strSql.Append("M12= null ,");
}
if (model.CreateUid != null)
{
strSql.Append("CreateUid='" + model.CreateUid + "',");
}
else
{
strSql.Append("CreateUid= null ,");
}
if (model.CreateDate != null)
{
strSql.Append("CreateDate='" + model.CreateDate + "',");
}
else
{
strSql.Append("CreateDate= null ,");
}
if (model.AuditUid != null)
{
strSql.Append("AuditUid='" + model.AuditUid + "',");
}
else
{
strSql.Append("AuditUid= null ,");
}
if (model.AuditDate != null)
{
strSql.Append("AuditDate='" + model.AuditDate + "',");
}
else
{
strSql.Append("AuditDate= null ,");
}
int n = strSql.ToString().LastIndexOf(",");
strSql.Remove(n, 1);
strSql.Append(" where iID=" + model.iID + "");
return(strSql.ToString());
}
