public static CrmAreaInfo GetAreaInfo(out string msg, int areaId)
{
msg = string.Empty;
lock (sync)
{
foreach (CrmAreaInfo area in AreaList)
{
if (area.AreaId == areaId)
{
return(area);
}
}
DbConnection conn = DbConnManager.GetDbConnection("CRMDB");
DbCommand cmd = conn.CreateCommand();
StringBuilder sql = new StringBuilder();
sql.Append("select CRMIP,CRMPORT,CRMURL from CRMQZJCFG where QYID = ").Append(areaId);
cmd.CommandText = sql.ToString();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
try
{
DbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
CrmAreaInfo area = new CrmAreaInfo();
AreaList.Add(area);
area.AreaId = areaId;
area.ServerIp = DbUtils.GetString(reader, 0);
area.ServerPort = DbUtils.GetInt(reader, 1);
area.ServiceUrl = DbUtils.GetString(reader, 2);
reader.Close();
return(area);
}
reader.Close();
}
catch (Exception e)
{
throw new MyDbException(e.Message, cmd.CommandText);
}
}
finally
{
conn.Close();
}
}
msg = string.Format("区域ID {0} 还没有在 CRM 定义服务配置", areaId);
return(null);
}
public static void CheckTablesForRecordsAffectedAfterExecSql(out string msg)
{
msg = string.Empty;
DbConnection conn = DbConnManager.GetDbConnection("CRMDB");
DbCommand cmd = conn.CreateCommand();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
msg = "CRMDB 连接失败：" + e.Message;
return;
}
StringBuilder sb = new StringBuilder();
sb.Append("<br><br> 检查一些表在执行update语句后，返回的影响行数是否有问题：");
DbTransaction dbTrans = conn.BeginTransaction();
try
{
cmd.Transaction = dbTrans;
ExecSqlToCheckRecordsAffected(cmd, "HYK_JFZH", "update HYK_JFZH set HYID = HYID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYK_MDJF", "update HYK_MDJF set HYID = HYID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYK_JEZH", "update HYK_JEZH set HYID = HYID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYK_YHQZH", "update HYK_YHQZH set HYID = HYID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYK_YEBD", "update HYK_YEBD set HYID = HYID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYK_JYCL", "update HYK_JYCL set JYID = JYID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYK_XFJL", "update HYK_XFJL set XFJLID = XFJLID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYK_XFJL_SP", "update HYK_XFJL_SP set XFJLID = XFJLID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYK_XFJL_SP_YQFT", "update HYK_XFJL_SP_YQFT set XFJLID = XFJLID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYXFJL_BSK", "update HYXFJL_BSK set XFJLID = XFJLID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYK_XFLJDFQ", "update HYK_XFLJDFQ set HYID = HYID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "HYK_THKQCE", "update HYK_THKQCE set HYID = HYID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "YHQCODE", "update YHQCODE set YHQCODE = YHQCODE where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "CXHD_FQZJE", "update CXHD_FQZJE set CXID = CXID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "CXHD_ZSLPSL_DAY", "update CXHD_ZSLPSL_DAY set CXID = CXID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "YHKFQZJE", "update YHKFQZJE set CXID = CXID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "YHKFQZJE_DAY", "update YHKFQZJE_DAY set CXID = CXID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "YHKFQJE_CARD", "update YHKFQJE_CARD set CXID = CXID where 1 = 2", sb);
ExecSqlToCheckRecordsAffected(cmd, "YHKFQJE_CARD_DAY", "update YHKFQJE_CARD_DAY set CXID = CXID where 1 = 2", sb);
}
finally
{
dbTrans.Commit();
}
msg = sb.ToString();
}
finally
{
conn.Close();
}
}
private CrmPromPayCouponRule CreateRule(int ruleId)
{
CrmPromPayCouponRule rule = null;
DbConnection conn = DbConnManager.GetDbConnection("CRMDB");
DbCommand cmd = conn.CreateCommand();
StringBuilder sql = new StringBuilder();
sql.Append("select YQBL_XFJE,YQBL_YHQJE,ZDYQJE,BJ_TY from YHQSYGZ where YHQSYGZID = ").Append(ruleId);
cmd.CommandText = sql.ToString();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
try
{
DbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
rule = new CrmPromPayCouponRule();
rule.RuleId = ruleId;
rule.SaleMoneyBase = DbUtils.GetDouble(reader, 0);
rule.PayLimitMoneyBase = DbUtils.GetDouble(reader, 1);
rule.MaxPayMoney = DbUtils.GetDouble(reader, 2);
rule.IsValid = (!DbUtils.GetBool(reader, 3));
}
reader.Close();
}
catch (Exception e)
{
if (e is MyDbException)
{
throw e;
}
else
{
throw new MyDbException(e.Message, cmd.CommandText);
}
}
}
finally
{
conn.Close();
}
return(rule);
}
private CrmCentMoneyMultiRule CreateRule(int ruleID)
{
CrmCentMoneyMultiRule rule = null;
DbConnection conn = DbConnManager.GetDbConnection("CRMDB");
DbCommand cmd = conn.CreateCommand();
StringBuilder sql = new StringBuilder();
sql.Append("select GZMC from JFBSGZ where GZID = ").Append(ruleID);
cmd.CommandText = sql.ToString();
try
{
conn.Open();
DbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
rule = new CrmCentMoneyMultiRule();
rule.RuleID = ruleID;
rule.RuleName = DbUtils.GetString(reader, 0);
reader.Close();
sql.Length = 0;
sql.Append("select XSJE,BS from JFBSGZITEM where GZID = ").Append(ruleID);
sql.Append(" and XSJE > 0 order by XSJE desc ");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
rule.AddItem(DbUtils.GetDouble(reader, 0), DbUtils.GetDouble(reader, 1));
}
reader.Close();
}
reader.Close();
}
finally
{
conn.Close();
}
return(rule);
}
private CrmSpecialDiscBill CreateRuleBill(int billId)
{
CrmSpecialDiscBill bill = null;
DbConnection conn = DbConnManager.GetDbConnection("CRMDB");
DbCommand cmd = conn.CreateCommand();
StringBuilder sql = new StringBuilder();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
try
{
sql.Append("select KSRQ,JSRQ from HYTDJFDYD where JLBH = ").Append(billId);
cmd.CommandText = sql.ToString();
DbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
bill = new CrmSpecialDiscBill();
bill.BillId = billId;
bill.BeginTime = DbUtils.GetDateTime(reader, 0);
bill.EndTime = DbUtils.GetDateTime(reader, 1);
reader.Close();
sql.Length = 0;
sql.Append("select GZBH,JFBS,BSFS,GRPID from HYTDJFDYD_GZSD where JLBH = ").Append(billId);
sql.Append(" order by GZBH");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
if (bill.Items == null)
{
bill.Items = new List <CrmSpecialDiscBillItem>();
}
CrmSpecialDiscBillItem item = new CrmSpecialDiscBillItem();
bill.Items.Add(item);
item.Inx = DbUtils.GetInt(reader, 0);
item.DiscRate = DbUtils.GetDouble(reader, 1);
item.DiscCombinationMode = DbUtils.GetInt(reader, 2);
item.VipGroup.GroupId = DbUtils.GetInt(reader, 3);
}
reader.Close();
if (bill.Items != null)
{
foreach (CrmSpecialDiscBillItem item in bill.Items)
{
if (item.VipGroup.GroupId > 0)
{
CrmPubUtils.GetVipGroup(cmd, sql, item.VipGroup);
}
}
}
return(bill);
}
else
{
reader.Close();
}
}
catch (Exception e)
{
if (e is MyDbException)
{
throw e;
}
else
{
throw new MyDbException(e.Message, cmd.CommandText);
}
}
}
finally
{
conn.Close();
}
return(null);
}
protected override void GetRuleBillDetail(ChangYi.Crm.Rule.CrmRuleBill bill)
{
DbConnection conn = DbConnManager.GetDbConnection("CRMDB");
DbCommand cmd = conn.CreateCommand();
DbDataReader reader = null;
StringBuilder sql = new StringBuilder();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
try
{
sql.Length = 0;
sql.Append("select INX,KSRQ,JSRQ,SD from HYKZKDYD_FD where JLBH = ").Append(bill.BillId);
sql.Append(" order by INX");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
if (bill.SubBillList == null)
{
bill.SubBillList = new List <CrmRuleSubBill>();
}
CrmRuleSubBill subBill = new CrmRuleSubBill();
bill.SubBillList.Add(subBill);
subBill.Inx = DbUtils.GetInt(reader, 0);
if (!reader.IsDBNull(1))
{
subBill.BeginTime = DbUtils.GetDateTime(reader, 1);
}
else
{
subBill.BeginTime = bill.BeginTime;
}
if (!reader.IsDBNull(2))
{
subBill.EndTime = DbUtils.GetDateTime(reader, 2);
}
else
{
subBill.EndTime = bill.EndTime;
}
if (reader.IsDBNull(3))
{
for (int i = 0; i < 48; i++)
{
subBill.Period[i] = 0xFF;
}
}
else
{
reader.GetBytes(3, 0, subBill.Period, 0, 48);
}
}
reader.Close();
if ((bill.SubBillList != null) && (bill.SubBillList.Count() > 0))
{
List <int> listInt = new List <int>();
List <CrmRuleValue> listRuleValue = new List <CrmRuleValue>();
DbParameter param = null;
sql.Length = 0;
sql.Append("select SHSPID,BJ_CJ,ZKL,ZSZKL,CLFS_JD from HYKZKDYD_SP where JLBH = ").Append(bill.BillId);
DbUtils.SpellSqlParameter(conn, sql, " and ", "INX", "=");
sql.Append(" order by SHSPID");
cmd.CommandText = sql.ToString();
cmd.Parameters.Clear();
param = DbUtils.AddIntInputParameter(cmd, "INX");
foreach (CrmRuleSubBill subBill in bill.SubBillList)
{
listInt.Clear();
listRuleValue.Clear();
param.Value = subBill.Inx;
reader = cmd.ExecuteReader();
while (reader.Read())
{
listInt.Add(DbUtils.GetInt(reader, 0));
CrmRuleValue ruleValue = new CrmRuleValue();
listRuleValue.Add(ruleValue);
ruleValue.IsJoined = DbUtils.GetBool(reader, 1);
ruleValue.DoubleValue = DbUtils.GetDouble(reader, 2);
ruleValue.DoubleValue2 = DbUtils.GetDouble(reader, 3);
ruleValue.DoubleDigits = DbUtils.GetInt(reader, 4);
ruleValue.Bill = bill;
ruleValue.SubBill = subBill;
}
reader.Close();
if (listInt.Count() > 0)
{
subBill.ArticleIds = listInt.ToArray <int>();
subBill.ArticleRuleValues = listRuleValue.ToArray <CrmRuleValue>();
}
}
sql.Length = 0;
sql.Append("select GZBH,ZKL,ZSZKL,CLFS_JD,BJ_CJ,CLFS_BM,CLFS_SPFL,CLFS_SPSB,CLFS_HT,CLFS_HYZ,CLFS_SP from HYKZKDYD_GZSD where JLBH = ").Append(bill.BillId);
DbUtils.SpellSqlParameter(conn, sql, " and ", "INX", "=");
sql.Append(" order by GZBH");
cmd.CommandText = sql.ToString();
cmd.Parameters.Clear();
param = DbUtils.AddIntInputParameter(cmd, "INX");
foreach (CrmRuleSubBill subBill in bill.SubBillList)
{
param.Value = subBill.Inx;
reader = cmd.ExecuteReader();
while (reader.Read())
{
if (subBill.Items == null)
{
subBill.Items = new List <CrmRuleBillItem>();
}
CrmRuleBillItem ruleItem = new CrmRuleBillItem();
subBill.Items.Add(ruleItem);
ruleItem.Inx = DbUtils.GetInt(reader, 0);
ruleItem.RuleValue.DoubleValue = DbUtils.GetDouble(reader, 1);
ruleItem.RuleValue.DoubleValue2 = DbUtils.GetDouble(reader, 2);
ruleItem.RuleValue.DoubleDigits = DbUtils.GetInt(reader, 3);
ruleItem.RuleValue.IsJoined = DbUtils.GetBool(reader, 4);
ruleItem.ExistDept = (DbUtils.GetInt(reader, 5) > 0);
ruleItem.ExistCategory = (DbUtils.GetInt(reader, 6) > 0);
ruleItem.ExistBrand = (DbUtils.GetInt(reader, 7) > 0);
ruleItem.ExistContract = (DbUtils.GetInt(reader, 8) > 0);
ruleItem.ExistVipGroup = (DbUtils.GetInt(reader, 9) > 0);
ruleItem.ExistArticle = (DbUtils.GetInt(reader, 10) > 0);
ruleItem.RuleValue.Bill = bill;
ruleItem.RuleValue.SubBill = subBill;
ruleItem.RuleValue.BillItem = ruleItem;
}
reader.Close();
}
cmd.Parameters.Clear();
List <string> listStr = new List <string>();
foreach (CrmRuleSubBill subBill in bill.SubBillList)
{
if (subBill.Items != null)
{
foreach (CrmRuleBillItem ruleItem in subBill.Items)
{
if (ruleItem.ExistDept)
{
listStr.Clear();
sql.Length = 0;
sql.Append("select b.BMDM from HYKZKDYD_GZITEM a, SHBM b where a.SJLX = 1 and a.SJNR = b.SHBMID and a.SJNR > 0 and a.JLBH = ").Append(bill.BillId);
sql.Append(" and a.INX = ").Append(subBill.Inx);
sql.Append(" and a.GZBH = ").Append(ruleItem.Inx);
sql.Append(" order by b.BMDM");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
listStr.Add(DbUtils.GetString(reader, 0).Trim());
}
reader.Close();
if (listStr.Count == 0)
{
ruleItem.ExistDept = false;
}
else
{
ruleItem.DeptCodes = listStr.ToArray <string>();
}
}
if (ruleItem.ExistContract)
{
listInt.Clear();
sql.Length = 0;
sql.Append("select a.SJNR from HYKZKDYD_GZITEM a where a.SJLX = 2 and a.SJNR > 0 and a.JLBH = ").Append(bill.BillId);
sql.Append(" and a.INX = ").Append(subBill.Inx);
sql.Append(" and a.GZBH = ").Append(ruleItem.Inx);
sql.Append(" order by a.SJNR");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
listInt.Add(DbUtils.GetInt(reader, 0));
}
reader.Close();
if (listInt.Count == 0)
{
ruleItem.ExistContract = false;
}
else
{
ruleItem.ContractIds = listInt.ToArray <int>();
}
}
if (ruleItem.ExistCategory)
{
listStr.Clear();
sql.Length = 0;
sql.Append("select b.SPFLDM from HYKZKDYD_GZITEM a,SHSPFL b where a.SJLX = 3 and a.SJNR = b.SHSPFLID and a.SJNR > 0 and a.JLBH = ").Append(bill.BillId);
sql.Append(" and a.INX = ").Append(subBill.Inx);
sql.Append(" and a.GZBH = ").Append(ruleItem.Inx);
sql.Append(" order by b.SPFLDM");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
listStr.Add(DbUtils.GetString(reader, 0).Trim());
}
reader.Close();
if (listStr.Count == 0)
{
ruleItem.ExistCategory = false;
}
else
{
ruleItem.CategoryCodes = listStr.ToArray <string>();
}
}
if (ruleItem.ExistBrand)
{
listInt.Clear();
sql.Length = 0;
sql.Append("select a.SJNR from HYKZKDYD_GZITEM a where a.SJLX = 4 and a.SJNR > 0 and a.JLBH = ").Append(bill.BillId);
sql.Append(" and a.INX = ").Append(subBill.Inx);
sql.Append(" and a.GZBH = ").Append(ruleItem.Inx);
sql.Append(" order by a.SJNR");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
listInt.Add(DbUtils.GetInt(reader, 0));
}
reader.Close();
if (listInt.Count == 0)
{
ruleItem.ExistBrand = false;
}
else
{
ruleItem.BrandIds = listInt.ToArray <int>();
}
}
if (ruleItem.ExistVipGroup)
{
listInt.Clear();
sql.Length = 0;
sql.Append("select a.SJNR from HYKZKDYD_GZITEM a where a.SJLX = 5 and a.SJNR > 0 and a.JLBH = ").Append(bill.BillId);
sql.Append(" and a.INX = ").Append(subBill.Inx);
sql.Append(" and a.GZBH = ").Append(ruleItem.Inx);
sql.Append(" order by a.SJNR");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
listInt.Add(DbUtils.GetInt(reader, 0));
}
reader.Close();
if (listInt.Count == 0)
{
ruleItem.ExistVipGroup = false;
}
else
{
ruleItem.VipGroups = new List <CrmVipGroup>();
ChangYi.Crm.Server.CrmPubUtils.GetVipGroups(cmd, sql, listInt, ruleItem.VipGroups);
}
}
if (ruleItem.ExistArticle)
{
listInt.Clear();
sql.Length = 0;
sql.Append("select a.SJNR from HYKZKDYD_GZITEM a where a.SJLX = 6 and a.SJNR > 0 and a.JLBH = ").Append(bill.BillId);
sql.Append(" and a.INX = ").Append(subBill.Inx);
sql.Append(" and a.GZBH = ").Append(ruleItem.Inx);
sql.Append(" order by a.SJNR");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
listInt.Add(DbUtils.GetInt(reader, 0));
}
reader.Close();
if (listInt.Count == 0)
{
ruleItem.ExistArticle = false;
}
else
{
ruleItem.ArticleIds = listInt.ToArray <int>();
}
}
}
}
}
}
}
catch (Exception e)
{
if (e is MyDbException)
{
throw e;
}
else
{
throw new MyDbException(e.Message, cmd.CommandText);
}
}
}
finally
{
conn.Close();
}
}
private CrmPromOfferCouponRule CreateRule(int ruleId)
{
CrmPromOfferCouponRule rule = null;
DbConnection conn = DbConnManager.GetDbConnection("CRMDB");
DbCommand cmd = conn.CreateCommand();
StringBuilder sql = new StringBuilder();
sql.Append("select FFXE,BJ_BFQGZ,BJ_TY,FFQDJE from YHQFFGZ where YHQFFGZID = ").Append(ruleId);
cmd.CommandText = sql.ToString();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
try
{
DbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
rule = new CrmPromOfferCouponRule();
rule.RuleId = ruleId;
rule.MaxOfferCouponMoney = DbUtils.GetDouble(reader, 0);
rule.IsNoOfferCoupon = ((DbUtils.GetBool(reader, 1)) || (DbUtils.GetBool(reader, 2)));
rule.MinSaleMoney = DbUtils.GetDouble(reader, 3);
reader.Close();
sql.Length = 0;
sql.Append("select XSJE,FQJE,LPDM from YHQFFGZITEM where YHQFFGZID = ").Append(ruleId);
sql.Append(" and XSJE > 0 and FQJE >= 0 order by XSJE desc ");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
CrmPromOfferCouponRuleItem ruleItem = new CrmPromOfferCouponRuleItem();
rule.Items.Add(ruleItem);
ruleItem.SaleMoney = DbUtils.GetDouble(reader, 0);
ruleItem.OfferCouponMoney = DbUtils.GetDouble(reader, 1);
ruleItem.GiftCode = DbUtils.GetString(reader, 2);
}
reader.Close();
}
reader.Close();
}
catch (Exception e)
{
if (e is MyDbException)
{
throw e;
}
else
{
throw new MyDbException(e.Message, cmd.CommandText);
}
}
}
finally
{
conn.Close();
}
return(rule);
}
public void AutoRollBackTrans()
{
while (true)
{
DateTime time1 = DateTime.Now;
DbConnection conn = null;
DbCommand cmd = null;
try
{
conn = DbConnManager.GetDbConnection("CRMDB");
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
try
{
string dbSysName = DbUtils.GetDbSystemName(conn);
cmd = conn.CreateCommand();
DateTime serverTime = DbUtils.GetDbServerTime(cmd);
List <int> transIds = null;
StringBuilder sql = new StringBuilder();
sql.Append("select JYID from HYK_JYCL_ZBZT ");
DbUtils.SpellSqlParameter(conn, sql, " where ", "ZBSJ", "<");
DbUtils.AddDatetimeInputParameterAndValue(cmd, "ZBSJ", serverTime.AddMinutes(-10));
cmd.CommandText = sql.ToString();
DbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
transIds = new List <int>();
transIds.Add(DbUtils.GetInt(reader, 0));
while (reader.Read())
{
transIds.Add(DbUtils.GetInt(reader, 0));
}
}
reader.Close();
cmd.Parameters.Clear();
if (transIds != null)
{
foreach (int transId in transIds)
{
int transType = 0;
bool isCashCard = false;
bool isVipCoupon = false;
bool isCodedCoupon = false;
sql.Length = 0;
sql.Append("select JYLX,BJ_CZK,BJ_YHQ from HYK_JYCL where JYZT = 1 and JYID = ").Append(transId);
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
if (reader.Read())
{
transType = DbUtils.GetInt(reader, 0);
isCashCard = DbUtils.GetBool(reader, 1);
int couponFlag = DbUtils.GetInt(reader, 2);
isVipCoupon = (couponFlag == 1);
isCodedCoupon = (couponFlag == 2);
}
reader.Close();
if (transType > 0)
{
serverTime = DbUtils.GetDbServerTime(cmd);
}
DbTransaction dbTrans = conn.BeginTransaction();
try
{
cmd.Transaction = dbTrans;
if (transType > 0)
{
sql.Length = 0;
sql.Append("update HYK_JYCL set JYZT = 3 ");
DbUtils.SpellSqlParameter(conn, sql, ",", "QXSJ", "=");
sql.Append(" where JYID = ").Append(transId);
sql.Append(" and JYZT = 1");
cmd.CommandText = sql.ToString();
DbUtils.AddDatetimeInputParameterAndValue(cmd, "QXSJ", serverTime);
if (cmd.ExecuteNonQuery() != 0)
{
cmd.Parameters.Clear();
if (isCashCard)
{
#region 取支付明细
List <CrmCashCardPayment> paymentList = new List <CrmCashCardPayment>();
sql.Length = 0;
sql.Append("select HYID,JE from HYK_JYCLITEM_CZK ");
sql.Append("where JYID = ").Append(transId);
sql.Append(" and JE > 0 ");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
CrmCashCardPayment payment = new CrmCashCardPayment();
paymentList.Add(payment);
payment.CardId = DbUtils.GetInt(reader, 0);
payment.PayMoney = DbUtils.GetDouble(reader, 1);
}
reader.Close();
#endregion
#region 取消每张卡的冻结金额
double balance = 0;
foreach (CrmCashCardPayment payment in paymentList)
{
sql.Length = 0;
sql.Append("update HYK_JEZH set YE = YE + ").Append(payment.PayMoney.ToString("f2"));
sql.Append(" , JYDJJE = ").Append(DbUtils.GetIsNullFuncName(dbSysName)).Append("(JYDJJE,0) - ").Append(payment.PayMoney.ToString("f2"));
sql.Append(" where HYID = ").Append(payment.CardId);
sql.Append(" and ").Append(DbUtils.GetIsNullFuncName(dbSysName)).Append("(JYDJJE,0) >= ").Append(payment.PayMoney.ToString("f2"));
cmd.CommandText = sql.ToString();
if (cmd.ExecuteNonQuery() == 0)
{
throw new Exception(string.Format("储值帐户冻结金额不足, CardId={0}", payment.CardId));
}
PosProc.EncryptBalanceOfCashCard(cmd, sql, payment.CardId, serverTime, out balance);
}
#endregion
}
else if (isVipCoupon)
{
#region 取支付明细
List <CrmCouponPayment> paymentList = new List <CrmCouponPayment>();
sql.Length = 0;
sql.Append("select HYID,YHQID,CXID,JSRQ,MDFWDM,JE from HYK_JYCLITEM_YHQ ");
sql.Append("where JYID = ").Append(transId);
sql.Append(" and JE > 0 ");
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
CrmCouponPayment payment = new CrmCouponPayment();
paymentList.Add(payment);
payment.VipId = DbUtils.GetInt(reader, 0);
payment.CouponType = DbUtils.GetInt(reader, 1);
payment.PromId = DbUtils.GetInt(reader, 2);
payment.ValidDate = DbUtils.GetDateTime(reader, 3);
payment.StoreScope = DbUtils.GetString(reader, 4).Trim();
payment.PayMoney = DbUtils.GetDouble(reader, 5);
}
reader.Close();
#endregion
#region 取消每张卡的优惠券冻结金额
foreach (CrmCouponPayment payment in paymentList)
{
cmd.Parameters.Clear();
DbUtils.AddDatetimeInputParameterAndValue(cmd, "JSRQ", payment.ValidDate);
sql.Length = 0;
sql.Append("update HYK_YHQZH set JE = JE + ").Append(payment.PayMoney.ToString("f2"));
sql.Append(" , JYDJJE = ").Append(DbUtils.GetIsNullFuncName(dbSysName)).Append("(JYDJJE,0) - ").Append(payment.PayMoney.ToString("f2"));
sql.Append(" where YHQID = ").Append(payment.CouponType);
sql.Append(" and HYID = ").Append(payment.VipId);
sql.Append(" and CXID = ").Append(payment.PromId);
DbUtils.SpellSqlParameter(conn, sql, " and ", "JSRQ", "=");
if (payment.StoreScope.Length == 0)
{
sql.Append(" and MDFWDM = ' '");
}
else
{
sql.Append(" and MDFWDM = '").Append(payment.StoreScope).Append("'");
}
sql.Append(" and ").Append(DbUtils.GetIsNullFuncName(dbSysName)).Append("(JYDJJE,0) >= ").Append(payment.PayMoney.ToString("f2"));
cmd.CommandText = sql.ToString();
if (cmd.ExecuteNonQuery() == 0)
{
cmd.Parameters.Clear();
throw new Exception(string.Format("优惠券帐户冻结金额不足, VipId={0}, CouponType={1}", payment.VipId, payment.CouponType));
}
cmd.Parameters.Clear();
}
#endregion
}
else if (isCodedCoupon)
{
#region 取交易的代码券
List <string> couponCodeList = new List <string>();
sql.Length = 0;
sql.Append("select YHQCODE from HYK_JYCLITEM_YHQDM ");
sql.Append("where JYID = ").Append(transId);
cmd.CommandText = sql.ToString();
reader = cmd.ExecuteReader();
while (reader.Read())
{
couponCodeList.Add(DbUtils.GetString(reader, 0));
}
reader.Close();
#endregion
#region 取消每张代码券的冻结状态
foreach (string couponCode in couponCodeList)
{
sql.Length = 0;
sql.Append("update YHQCODE set STATUS = 1,MDID_YQ = null,YQSJ = null ");
sql.Append(" where YHQCODE = '").Append(couponCode).Append("'");
sql.Append(" and STATUS = 2 ");
cmd.CommandText = sql.ToString();
if (cmd.ExecuteNonQuery() == 0)
{
throw new Exception(string.Format("代码券 {0} 状态不对", couponCode));
}
}
#endregion
}
}
cmd.Parameters.Clear();
}
sql.Length = 0;
sql.Append("delete from HYK_JYCL_ZBZT where JYID = ").Append(transId);
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
dbTrans.Commit();
}
catch (Exception e)
{
dbTrans.Rollback();
CrmServerPlatform.WriteErrorLog("\r\n Auto rollback trans (id=" + transId.ToString() + "), sql：" + cmd.CommandText + "\r\n error: " + e.Message);
}
}
}
}
catch (Exception e)
{
if (cmd == null)
{
CrmServerPlatform.WriteErrorLog("\r\n Auto rollback trans error: " + e.Message);
}
else
{
CrmServerPlatform.WriteErrorLog("\r\n Auto rollback trans, sql：" + cmd.CommandText + "\r\n error: " + e.Message);
}
}
}
finally
{
if (conn != null)
{
conn.Close();
}
}
DateTime time2 = DateTime.Now;
int interval = 300000 - MathUtils.Truncate(time2.Subtract(time1).TotalMilliseconds);
if (interval < 1000)
{
interval = 1000;
}
Thread.Sleep(interval); //5分钟后再来
}
}
public static void InitiateData()
{
if (!isInitiated)
{
DbCommand cmd = null;
try
{
string str = ConfigurationManager.AppSettings["ShowErrorDetail"];
Config.ShowErrorDetail = ((str != null) && (str.ToLower().Equals("true")));
str = ConfigurationManager.AppSettings["LoadBalance"];
Config.LoadBalance = ((str != null) && (str.ToLower().Equals("true")));
str = ConfigurationManager.AppSettings["CrmDbCharSetIsNotChinese"];
Config.DbCharSetIsNotChinese = ((str != null) && (str.ToLower().Equals("true")));
str = ConfigurationManager.AppSettings["WXUrl"];
if (str != null)
{
Config.WXUrl = str;
}
string logPath = ConfigurationManager.AppSettings["bfcrm.log.path"];
DataLogFileWriter = new DailyLogFileWriter(logPath, "bfcrm");
ErrorLogFileWriter = new LogFileWriter(logPath);//, );
if (!ErrorLogFileWriter.SetFileName("bfcrmerror.log"))
{
for (int i = 1; i < 100; i++)
{
if (ErrorLogFileWriter.SetFileName(string.Format("bfcrmerror_{0}.log", i.ToString().PadLeft(2, '0'))))
{
break;
}
}
}
DbConnection conn = DbConnManager.GetDbConnection("CRMDB");
cmd = conn.CreateCommand();
StringBuilder sql = new StringBuilder();
sql.Append("select JLBH,CUR_VAL from BFCONFIG where JLBH in (0,520000102,520000103,520000112,520000113,520000115,520000120,520000121,520000123,520000128,520000129)");
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
cmd.CommandText = sql.ToString();
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int jlbh = DbUtils.GetInt(reader, 0);
int curVal = DbUtils.GetInt(reader, 1);
switch (jlbh)
{
case 0:
CurrentDbId = curVal;
break;
case 520000102:
Config.NoNegativeValidCent = (curVal != 0);
break;
case 520000103:
Config.IsSingleCompany = (curVal != 0);
break;
case 520000112:
Config.LengthVerifyOfCashCard = curVal;
//Config.LengthVerifyOfVipCard = curVal;
break;
case 520000113:
Config.LengthVerifyOfVipCard = curVal;
//Config.LengthVerifyOfCashCard = curVal;
break;
case 520000115:
Config.NoNegativeYearCent = (curVal != 0);
break;
case 520000120:
Config.NoCentWhenPayCoupon = (curVal != 0);
break;
case 520000121:
Config.NoOfferCouponWhenPayCoupon = (curVal != 0);
break;
case 520000123:
Config.PayCashCardWithArticle = (curVal != 0);
break;
case 520000128: //卡磁道内容是否二次加密
Config.DesEncryptVipCardTrackSecondly = (curVal != 0);
Config.DesEncryptCashCardTrackSecondly = (curVal != 0);
break;
case 520000129:
Config.IsUpgradedProject2013 = (curVal != 0);
break;
}
}
reader.Close();
cmd.CommandText = "select USER_DM,USER_PSW,HTTPOK,WSOK,WCFOK from CRMUSER";
reader = cmd.ExecuteReader();
while (reader.Read())
{
CrmAccessUser user = new CrmAccessUser();
AccessUserList.Add(user);
user.UserId = DbUtils.GetString(reader, 0);
user.Password = DbUtils.GetString(reader, 1);
user.HttpHandlerAccess = DbUtils.GetBool(reader, 2);
user.WebServiceAccess = DbUtils.GetBool(reader, 3);
user.WcfAccess = DbUtils.GetBool(reader, 4);
}
reader.Close();
cmd.CommandText = "select IPADDR from CRMCLIENT where WSOK = 1 ";
reader = cmd.ExecuteReader();
while (reader.Read())
{
WebServiceClientIpList.Add(DbUtils.GetString(reader, 0));
}
reader.Close();
}
finally
{
conn.Close();
}
}
catch (Exception e)
{
if (cmd == null)
{
CrmServerPlatform.WriteErrorLog("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + " System Init Error: " + e.Message.ToString());
}
else
{
CrmServerPlatform.WriteErrorLog("\r\n" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + " System Init, sql: " + cmd.CommandText + "\r\n Error: " + e.Message.ToString());
}
}
ChangYi.Crm.Server.BackgroundProc backgroundProc = new ChangYi.Crm.Server.BackgroundProc();
autoRollBackTransThread = new Thread(new ThreadStart(backgroundProc.AutoRollBackTrans));
autoRollBackTransThread.IsBackground = true;
autoRollBackTransThread.Start();
isInitiated = true;
}
}
public static bool CheckCrmUser(out string msg, int accessMode, string userId, string password)
{
msg = string.Empty;
return(true);
DbConnection conn = DbConnManager.GetDbConnection("CRMDB");
DbCommand cmd = conn.CreateCommand();
StringBuilder sql = new StringBuilder();
sql.Append("select USER_PSW from CRMUSER where USER_DM = '").Append(userId).Append("'");
switch (accessMode)
{
case 1:
sql.Append(" and HTTPOK = 1 ");
break;
case 2:
sql.Append(" and WSOK = 1 ");
break;
case 3:
sql.Append(" and WCFOK = 1 ");
break;
default:
sql.Append(" and 1 = 2 ");
break;
}
cmd.CommandText = sql.ToString();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
DbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
string pass = DbUtils.GetString(reader, 0);
reader.Close();
if (!password.Equals(pass))
{
msg = "登录BFCRM的用户密码不正确";
return(false);
}
}
else
{
reader.Close();
msg = "登录BFCRM的用户代码不存在";
return(false);
}
}
finally
{
conn.Close();
}
return(true);
if ((accessMode < 1) || (accessMode > 3))
{
msg = "accessMode 值只能是 1,2,3";
return(false);
}
foreach (CrmAccessUser user in AccessUserList)
{
if (user.UserId.Equals(userId))
{
if (!user.Password.Equals(password))
{
msg = "登录BFCRM的用户密码不正确";
return(false);
}
switch (accessMode)
{
case 1:
if (!user.HttpHandlerAccess)
{
msg = "该用户不能通过HttpHandler方式接入";
return(false);
}
break;
case 2:
if (!user.WebServiceAccess)
{
msg = "该用户不能通过Web Service方式接入";
return(false);
}
break;
case 3:
if (!user.WcfAccess)
{
msg = "该用户不能通过WCF方式接入";
return(false);
}
break;
}
return(true);
}
}
msg = "登录BFCRM的用户代码(" + userId + ")不存在";
return(false);
}
public static bool GetStoreInfo(out string msg, CrmStoreInfo storeInfo)
{
msg = string.Empty;
storeInfo.Company = string.Empty;
storeInfo.StoreId = -1;
lock (sync)
{
foreach (CrmStoreInfo store in StoreList)
{
if (store.StoreCode.Equals(storeInfo.StoreCode))
{
storeInfo.Company = store.Company;
storeInfo.StoreId = store.StoreId;
return(true);
}
}
DbConnection conn = DbConnManager.GetDbConnection("CRMDB");
DbCommand cmd = conn.CreateCommand();
StringBuilder sql = new StringBuilder();
sql.Append("select SHDM,MDID from MDDY where MDDM = '").Append(storeInfo.StoreCode).Append("'");
cmd.CommandText = sql.ToString();
try
{
try
{
conn.Open();
}
catch (Exception e)
{
throw new MyDbException(e.Message, true);
}
try
{
DbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
CrmStoreInfo store2 = new CrmStoreInfo();
StoreList.Add(store2);
store2.StoreCode = storeInfo.StoreCode;
store2.Company = DbUtils.GetString(reader, 0).Trim();
store2.StoreId = DbUtils.GetInt(reader, 1);
storeInfo.Company = store2.Company;
storeInfo.StoreId = store2.StoreId;
}
reader.Close();
}
catch (Exception e)
{
if (e is MyDbException)
{
throw e;
}
else
{
throw new MyDbException(e.Message, cmd.CommandText);
}
}
}
finally
{
conn.Close();
}
}
if (storeInfo.StoreId <= 0)
{
msg = string.Format("门店代码 {0} 还没有上传到 CRM 库", storeInfo.StoreCode);
return(false);
}
return(true);
}
