public static Result DB2131108(DbConnections pDB)
{
Result res = new Result();
DbConnection con = null;
try
{
con = pDB.BeginTransaction("core", "Start");
#region [ INSERT ]
string sql = @"insert into pledgemain_Hist select * from pledgemain where status=1";
res = pDB.ExecuteQuery("core", sql, enumCommandType.INSERT, "DB2131108", null);
if (res.ResultNo != 0)
{
return(res);
}
sql = @"insert into pledgedoc_Hist select * from pledgedoc where status=1";
res = pDB.ExecuteQuery("core", sql, enumCommandType.INSERT, "DB2131108", null);
if (res.ResultNo != 0)
{
return(res);
}
#endregion
#region [ DELETE ]
sql = @"delete from pledgemain where status=1";
res = pDB.ExecuteQuery("core", sql, enumCommandType.DELETE, "DB2131108", null);
if (res.ResultNo != 0)
{
return(res);
}
sql = @"delete from pledgedoc where status=1";
res = pDB.ExecuteQuery("core", sql, enumCommandType.DELETE, "DB2131108", null);
return(res);
#endregion
}
catch (Exception ex)
{
res.ResultNo = 9110001;
res.ResultDesc = "Датабааз руу хандахад алдаа гарлаа" + ex.Message;
return(res);
}
finally
{
if (res.ResultNo == 0)
{
con.Commit();
}
else
{
con.Rollback();
}
}
return(res);
}
public Result FN105003(ClientInfo ci, RequestInfo ri, DbConnections db, ref Log lg)
{
Result r = null;
DbConnection conn = null;
try
{
#region Preparing Parameters
string tableprefix = (string)ri.ReceivedParam[0];
ulong recordid = (ulong)ri.ReceivedParam[1];
int objectid = (int)ri.ReceivedParam[2];
int prowno = (int)ri.ReceivedParam[3];
int rowno = (int)ri.ReceivedParam[4];
#endregion
conn = db.BeginTransaction("core", "FN105003");
#region Delete Row from Fixed table
r = Main.DB105010(db, tableprefix, recordid, objectid, prowno, rowno);
if (r.ResultNo != 0)
{
return(r);
}
#endregion
#region Delete Rows from Dynamic Records
r = Main.DB105009(db, tableprefix, recordid, objectid, prowno, rowno);
#endregion
}
catch (Exception ex)
{
r = new Result(9, ex.ToString());
}
finally
{
if (r != null && conn != null)
{
if (r.ResultNo == 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
OnExit:
return(r);
}
/// <summary>
/// string tableprefix = Static.ToStr(ri.ReceivedParam[0]);
/// ulong key = (ulong)ri.ReceivedParam[1];
/// object[] rows = (object[])ri.ReceivedParam[2];
/// ulong id = (ulong)Static.ToLong(row[0]);
/// int type = Static.ToInt(row[1]);
/// string value = Static.ToStr(row[2]);
/// </summary>
/// <param name="ci"></param>
/// <param name="ri"></param>
/// <param name="db"></param>
/// <param name="lg"></param>
/// <returns></returns>
public Result FN104002(ClientInfo ci, RequestInfo ri, DbConnections db, ref Log lg)
{
Result r = null;
DbConnection conn = null;
try
{
string tableprefix = Static.ToStr(ri.ReceivedParam[0]); // Table name prefix of Dynamic Data
ulong key = (ulong)ri.ReceivedParam[1]; // Dynamic data Record Key
object[] rows = (object[])ri.ReceivedParam[2]; // Dynamic Data Rows
conn = db.BeginTransaction("core", "104002");
foreach (object[] row in rows)
{
ulong id = (ulong)Static.ToLong(row[0]);
int type = Static.ToInt(row[1]);
string value = Static.ToStr(row[2]);
ulong attachid = (ulong)Static.ToLong(row[3]);
r = Main.DB104002(db, tableprefix, key, id, value, attachid);
if (r.ResultNo != 0)
{
break;
}
}
}
catch (Exception ex)
{
r = new Result(9, ex.ToString());
}
finally
{
if (r != null)
{
if (conn != null && r.ResultNo == 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
return(r);
}
public Result FN105002(ClientInfo ci, RequestInfo ri, DbConnections db, ref Log lg)
{
Result r = null;
DataTable dt1 = null;
DbConnection conn = null;
int START_INDEX_DYNAMIC_COL = 23;
try
{
#region Preparing Parameters
string tableprefix = (string)ri.ReceivedParam[0];
ulong recordid = (ulong)ri.ReceivedParam[1];
int objectid = (int)ri.ReceivedParam[2];
int prowno = (int)ri.ReceivedParam[3];
bool showfixed = (bool)ri.ReceivedParam[4];
dt1 = (DataTable)ri.ReceivedParam[5];
#endregion
conn = db.BeginTransaction("core", "FN105002");
int rn;
foreach (DataRow row in dt1.Rows)
{
rn = ISM.Lib.Static.ToInt(row["rowno"]);
if (showfixed)
{
#region Collectiong Values
int status = ISM.Lib.Static.ToInt(row["status"]);
DateTime startdate = ISM.Lib.Static.ToDate(row["startdate"]);
DateTime enddate = ISM.Lib.Static.ToDate(row["enddate"]);
int feetype = ISM.Lib.Static.ToInt(row["feetype"]);
decimal feerate = ISM.Lib.Static.ToDecimal(row["feerate"]);
decimal feeamount = ISM.Lib.Static.ToDecimal(row["feeamount"]);
string feecurcode = ISM.Lib.Static.ToStr(row["feecurcode"]);
decimal discountrate = ISM.Lib.Static.ToDecimal(row["discountrate"]);
decimal discountamount = ISM.Lib.Static.ToDecimal(row["discountamount"]);
string discountcurcode = ISM.Lib.Static.ToStr(row["discountcurcode"]);
decimal currate = ISM.Lib.Static.ToDecimal(row["currate"]);
decimal estimateamount = ISM.Lib.Static.ToDecimal(row["estimateamount"]);
string estimatecurcode = ISM.Lib.Static.ToStr(row["estimatecurcode"]);
int optionid = ISM.Lib.Static.ToInt(row["optionid"]);
int selectstatus = ISM.Lib.Static.ToInt(row["selectstatus"]);
int feediscounttype = ISM.Lib.Static.ToInt(row["feediscounttype"]);
decimal feediscountamount = ISM.Lib.Static.ToDecimal(row["feediscountamount"]);
decimal feediscountrate = ISM.Lib.Static.ToDecimal(row["feediscountrate"]);
decimal calcamount = ISM.Lib.Static.ToDecimal(row["calcamount"]);
decimal calcrate = ISM.Lib.Static.ToDecimal(row["calcrate"]);
int unoptionid = ISM.Lib.Static.ToInt(row["unoptionid"]);
decimal claimamount = ISM.Lib.Static.ToDecimal(row["claimamount"]);
decimal marketvalue = ISM.Lib.Static.ToDecimal(row["marketvalue"]);
#endregion
#region Saving Fixed Values
// Update Fixed Values
r = Main.DB105005(db, tableprefix, recordid, objectid, prowno, rn
, status, startdate, enddate, feetype, feerate, feeamount, feecurcode
, discountrate, discountamount, discountcurcode, currate, estimateamount, estimatecurcode, optionid, selectstatus, feediscounttype, feediscountamount, feediscountrate, calcamount, calcrate, unoptionid, claimamount, marketvalue);
if (r.ResultNo != 0)
{
goto OnExit;
}
if (r.AffectedRows <= 0)
{
// If there is no updated rows, then insert new row
r = Main.DB105006(db, tableprefix, recordid, objectid, prowno, rn
, status, startdate, enddate, feetype, feerate, feeamount, feecurcode
, discountrate, discountamount, discountcurcode, currate, estimateamount, estimatecurcode, optionid, selectstatus, feediscounttype, feediscountamount, feediscountrate, calcamount, calcrate, unoptionid, claimamount, marketvalue);
if (r.ResultNo != 0)
{
goto OnExit;
}
}
#endregion
}
#region Saving Dynamic Values
int startindex = showfixed ? START_INDEX_DYNAMIC_COL : 0;
for (int c = startindex; c < dt1.Columns.Count; c++)
{
int itemid = ISM.Lib.Static.ToInt(dt1.Columns[c].ColumnName.Substring(1));
string value = ISM.Lib.Static.ToStr(row[c]);
// Update Dynamic Values
r = Main.DB105007(db, tableprefix, recordid, objectid, prowno, rn, itemid, value);
if (r.ResultNo != 0)
{
goto OnExit;
}
if (r.AffectedRows <= 0)
{
// If there is no update rows, then insert new row
r = Main.DB105008(db, tableprefix, recordid, objectid, prowno, rn, itemid, value);
if (r.ResultNo != 0)
{
goto OnExit;
}
}
}
#endregion
}
}
catch (Exception ex)
{
r = new Result(9, ex.ToString());
}
finally
{
if (r != null && conn != null)
{
if (r.ResultNo == 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
OnExit:
return(r);
}
public Result Txn601001(ClientInfo ci, RequestInfo ri, DbConnections db, ref Log lg)
{
Result res = null;
DbConnection conn = null;
try
{
#region Prepare parameters
string pledgeno = Static.ToStr(ri.ReceivedParam[0]);
int typeid = Static.ToInt(ri.ReceivedParam[1]);
string docno = Static.ToStr(ri.ReceivedParam[2]);
decimal custno = Static.ToDecimal(ri.ReceivedParam[3]);
string custname = Static.ToStr(ri.ReceivedParam[4]);
string phone = Static.ToStr(ri.ReceivedParam[5]);
string memo = Static.ToStr(ri.ReceivedParam[6]);
#endregion
#region New autonum for registeration
IPos.Core.AutoNumEnum enums = new IPos.Core.AutoNumEnum();
enums.Y = Static.ToStr(Static.ToDate(DateTime.Now).Year);
res = IPos.Core.SystemProp.gAutoNum.GetNextNumber(db, 8, enums);
if (res.ResultNo != 0)
{
if (string.IsNullOrEmpty(res.ResultDesc))
{
res.ResultNo = 9110068;
res.ResultDesc = "Автомат дугаарлалтын хөрвүүлэлт дээр алдаа гарлаа. [ID:12][";
res.Param = null;
res.Data = null;
}
goto OnExit;
}
#endregion
#region Барьцааг pledgedoc тэйбэлд бүртгэх
pledgeno = res.ResultDesc;
conn = db.BeginTransaction("core", "Txn601001");
string sql = @"insert into pledgedoc
(pledgeno,doctype,docno,holddate,holduser,status,custno,custname,contact,memo)
values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)";
object[] param = new object[] { pledgeno, typeid, docno, DateTime.Now, ri.UserNo, 0, custno, custname, phone, memo };
res = conn.ExecuteQuery(sql, enumCommandType.INSERT, "Txn601001", param);
if (res.ResultNo != 0)
{
goto OnExit;
}
#endregion
#region Барьцааны үндсэн харилцагчийг холбоотой үйлчлүүлэгчийн тэйбэлд оруулах
sql = "insert into pledgemain(custno,pledgeno) values(:1,:2)";
param = new object[] { custno, pledgeno };
res = conn.ExecuteQuery(sql, enumCommandType.INSERT, "Txn601001", param);
if (res.ResultNo == 0)
{
res.ResultDesc = pledgeno;
}
#endregion
}
catch (Exception ex)
{
res = new Result(9110002, "Програм руу нэвтрэхэд алдаа гарлаа" + ex.Message);
}
finally
{
if (conn != null)
{
if (res != null && res.ResultNo != 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
OnExit:
return(res);
} //Барьцаа шинээр үүсгэх
} //Барьцааны хамаарах түрээсийн хэрэгслүүд
public Result Txn601005(ClientInfo ci, RequestInfo ri, DbConnections db, ref Log lg)
{
#region Тооцоолол хийх дүрмийг заавал унш! бас бөглө!
/**************************************************************
* ТООЦООЛОЛ ХИЙХ ДҮРЭМ
* Шалгалт:
* 1. Хэрэв харилцагч барьцаанд холбогдсон л бол PledgeMain тэйбэлд бичлэг үүснэ.
* 2. Мөн CustomerIdDevice тэйбэлд Харилцагчийн бичлэгийг давхар үүсгэнэ.
* Энэ нь харилцагч ерөнхийдөө өөр барьцаанд давхар хамаарч байгаа эсэхийг шалгах, таг холбох зэргийг тэмдэглэнэ.
* 3. Харилцагчийг барьцаанаас салгах үед CustomerIdDevice тэйблээс бичлэгийг устгана.
*
* Үйлдэл:
* 1. pledgemain тэйбэлд харилцагчийн нэмэх
* 2. CustomerIdDevice тэйбэлд уг харилцагчийн дугаараар шинээр харилцагч нэмэх ба өмнө нь бсн эсэхийг шалгана.
*
*
**************************************************************/
#endregion
Result res = null;
DbConnection conn = null;
try
{
#region Prepare parameters
string pledgeno = Static.ToStr(ri.ReceivedParam[0]);
decimal custno = Static.ToDecimal(ri.ReceivedParam[1]);
#endregion
conn = db.BeginTransaction("core", "Txn601005");
#region Үйлдэл1
string sql = @"insert into pledgemain(custno,pledgeno) values(:1,:2)";
object[] param = new object[] { custno, pledgeno };
res = conn.ExecuteQuery(sql, enumCommandType.INSERT, "Txn601005", param);
if (res.ResultNo != 0)
{
if (res.ResultNo == 1)
{
res.ResultNo = 6010051;
res.ResultDesc = string.Format("{0} дугаартай харилцагч холбоотой байна!", custno);
}
goto OnExit;
}
#endregion
#region Үйлдэл2
sql = @"insert into customeriddevice(custno) values(:1)";
param = new object[] { custno, pledgeno };
res = conn.ExecuteQuery(sql, enumCommandType.INSERT, "Txn601005", param);
if (res.ResultNo != 0)
{
if (res.ResultNo == 1)
{
res.ResultNo = 6010052;
res.ResultDesc = string.Format("{0} дугаартай харилцагч өөр барьцаанд холбоотой байна!", custno);
}
goto OnExit;
}
#endregion
}
catch (Exception ex)
{
res = new Result(9110002, "Програм руу нэвтрэхэд алдаа гарлаа" + ex.Message);
}
finally
{
if (conn != null)
{
if (res != null && res.ResultNo != 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
OnExit:
return(res);
} //Барьцаанд үйлчлүүлэгч холбох
/// <summary>
/// Бичиг баримт барьцаалах
///
/// </summary>
/// <param name="db"></param>
/// <param name="pagenumber"></param>
/// <param name="pagecount"></param>
/// <param name="param"> custno, pledgeno, UserNo, docno, doctype</param>
/// <returns></returns>
public static Result DB500011(DbConnections db, object[] param)
{
DbConnection conn = null;
Result res = null;
try
{
#region Prepare parameters
decimal custno = Static.ToDecimal(param[0]);
string pledgeno = Static.ToStr(param[1]);
int userno = Static.ToInt(param[2]);
string docno = Static.ToStr(param[3]);
int doctype = Static.ToInt(param[4]);
#endregion
#region Prepare query
string sql1 = @"
insert into pledgedoc(pledgeno,docno,doctype,holduser,holddate,status)
values(:1,:2,:3,:4,sysdate,0)
";
string sql2 = @"
merge into pledgemain a
using (select :1 custno,:2 pledgeno from dual) b
on (a.custno=b.custno and a.pledgeno=b.pledgeno)
when matched then update set a.status=0
when not matched then insert (custno,pledgeno,createdate,status) values(b.custno,b.pledgeno,sysdate,0)
";
#endregion
#region Execute query
conn = db.BeginTransaction("core", "DB500011");
res = db.ExecuteQuery("core", sql1, enumCommandType.UPDATE, "DB500011", pledgeno, docno, doctype, userno);
if (res.ResultNo == 0)
{
res = db.ExecuteQuery("core", sql2, enumCommandType.UPDATE, "DB500011", custno, pledgeno);
}
#endregion
}
catch (Exception ex)
{
ISM.Lib.Static.WriteToLogFile("Error.log", ex.Message + ex.Source + ex.StackTrace);
res = new Result();
res.ResultNo = 9110001;
res.ResultDesc = "Датабааз руу хандахад алдаа гарлаа" + ex.Message;
}
finally
{
if (conn != null)
{
if (res.ResultNo == 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
return(res);
}
/// <summary>
/// Барьцаалсан бичиг баримтыг олгох
///
/// </summary>
/// <param name="db"></param>
/// <param name="pagenumber"></param>
/// <param name="pagecount"></param>
/// <param name="param"></param>
/// <returns></returns>
public static Result DB500012(DbConnections db, object[] param)
{
DbConnection conn = null;
Result res = null;
try
{
#region Prepare parameters
decimal custno = Static.ToDecimal(param[0]);
string pledgeno = Static.ToStr(param[1]);
string rowid = Static.ToStr(param[2]);
int userno = Static.ToInt(param[3]);
#endregion
#region Prepare query
string sql1 = @"
update pledgedoc set status=1,unholduser=:2,unholddate=sysdate
where rowid=:1
";
string sql2 = @"
update pledgemain pm set status =
(
select min(pd.status) status
from pledgedoc pd
where pd.pledgeno=:2
)
where pm.custno=:1 and pm.pledgeno=:2
";
#endregion
#region Execute query
conn = db.BeginTransaction("core", "DB500012");
res = db.ExecuteQuery("core", sql1, enumCommandType.UPDATE, "DB500012", rowid, userno);
if (res.ResultNo == 0)
{
res = db.ExecuteQuery("core", sql2, enumCommandType.UPDATE, "DB500012", custno, pledgeno);
}
#endregion
}
catch (Exception ex)
{
ISM.Lib.Static.WriteToLogFile("Error.log", ex.Message + ex.Source + ex.StackTrace);
res = new Result();
res.ResultNo = 9110001;
res.ResultDesc = "Датабааз руу хандахад алдаа гарлаа" + ex.Message;
}
finally
{
if (conn != null)
{
if (res.ResultNo == 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
return(res);
}
} //Таг салгах
public Result Txn601010(ClientInfo ci, RequestInfo ri, DbConnections db, ref Log lg)
{
#region Тооцоолол хийх дүрмийг заавал унш! бас бөглө!
/**************************************************************
* ТООЦООЛОЛ ХИЙХ ДҮРЭМ
* Шалгалт:
* 1. Холбоотой харилцагчид дотор таг уягдсан бол чөлөөлөхгүй. PledgeMain, CustomerIdDevice-ээс шалгана.
* 2. Түрээсийн хэрэгслүүд бүгд буцаагдсан, эсвэл төлбөр хийгдсэн бх
* 3. Борлуулалтын Төлбөр нь бүрэн хийгдсэн бх.
*
* Үйлдэл:
* 1. pledgedoc тэйбэлд төлвийг чөлөөлсөн болгох. Status=1
* 2. CustomerIdDevice тэйбэлд үүссэн холбоотой харилцагчдийг цэвэрлэх.
* 3. pledgemain тэйбэлд бга холбоостой харилцагчдийг цэвэрлэх.
*
*
**************************************************************/
#endregion
Result res = null;
DbConnection conn = null;
string sql = null;
DataTable dtSales = null;
try
{
#region Prepare parameters
string pledgeno = Static.ToStr(ri.ReceivedParam[0]);
#endregion
#region Шалгалт1 - Төлөв шалгах
res = PledgeStatusCheck(db, pledgeno);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
#endregion
#region Шалгалт2 - Таг холбоотойг шалгах
sql = @"select count(p.custno) tagcount
from pledgemain p
left join customeriddevice d on d.custno=p.custno
where p.pledgeno=:1 and d.serialno is not null";
object[] param = new object[] { pledgeno };
res = db.ExecuteQuery("core", sql, enumCommandType.SELECT, "Txn601010", param);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
DataTable dt = res.Data.Tables[0];
int tagcount = Static.ToInt(dt.Rows[0]["tagcount"]);
if (tagcount > 0)
{
res.ResultNo = 6010101;
res.ResultDesc = string.Format("Бүртгэлд {0} ширхэг таг холбоотой үлдсэн байна!", tagcount);
res.Data = null;
goto OnExit;
}
#endregion
#region Борлуулалтын дугааруудыг олох
sql = @"select distinct s.salesno
,decode(c.classcode,1,c.corporatename, c.lastname||' '||c.firstname) custname
from sales s
left join customer c on c.customerno=s.custno
where custno in (
select custno from pledgedoc where pledgeno=:1
union all
select custno from pledgemain where pledgeno=:1
)";
res = db.ExecuteQuery("core", sql, enumCommandType.SELECT, "Txn601010", pledgeno);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
if (res.AffectedRows > 0)
{
dtSales = res.Data.Tables[0];
}
#endregion
#region Шалгалт3 - Борлуулалтын төлбөр бүрэн төлөгдсөн эсэх
if (dtSales != null)
{
Sales CSales = new Sales();
foreach (DataRow r in dtSales.Rows)
{
string salesno = Static.ToStr(r["SALESNO"]);
string custname = Static.ToStr(r["CUSTNAME"]);
res = CSales.GetPaymentBalance(db, salesno);
if (res.ResultNo != 0)
{
goto OnExit;
}
if (res.AffectedRows > 0)
{
dt = res.Data.Tables[0];
decimal ta = Static.ToDecimal(dt.Rows[0]["TOTALAMOUNT"]);
decimal pa = Static.ToDecimal(dt.Rows[0]["PAID"]);
if (ta > 0 && ta > pa)
{
res = new Result(6010102, string.Format("[{0}] Борлуулалтын төлбөр бүрэн төлөгдөөгүй байна.\r\nҮйлчлүүлэгч={1}\r\nҮлдэгдэл={2}", salesno, custname, ta - pa));
goto OnExit;
}
}
}
}
#endregion
#region Шалгалт4 - Түрээсийн торгууль төлөгдсөн эсэх
sql = @"select * from (
select p.pledgeno,p.custno,sr.salesno,sr.rentstatus status,sr.itemno,sr.prodno,im.name
,decode(c.classcode,1,c.corporatename, c.lastname||' '||c.firstname) custname
,case when nvl(sr.rentstatus,0)=2 and
((nvl(sr.servicetime,0)>0 and round((sr.rentendtime-sr.rentstarttime)*(24*60),2)-nvl(sr.servicetime,0)>0)
or sr.damagetype is not null)
then 1 else 0 end fined
,case when nvl(sr.servicetime,0)>0 and (sr.rentendtime-sr.rentstarttime)*(24*60)-nvl(sr.servicetime,0)>0 then
round((sr.rentendtime-sr.rentstarttime)*(24*60)-nvl(sr.servicetime,0),2) else 0 end rentminutes
from pledgemain p
left join customeriddevice d on d.custno=p.custno
inner join sales s on s.custno=p.custno
left join salesrent sr on sr.salesno=s.salesno
left join invmain im on im.invid=sr.prodno
left join customer c on c.customerno=sr.custno
where p.pledgeno=:1
) a where a.fined=1 or a.status=1";
param = new object[] { pledgeno };
res = db.ExecuteQuery("core", sql, enumCommandType.SELECT, "Txn601010", param);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
if (res.AffectedRows > 0)
{
res = new Result(6010103, "Түрээсийн хэрэгсэлийг бүрэн хүлээж аваагүй эсвэл торгууль төлөгдөөгүй байна.");
goto OnExit;
}
#endregion
#region Холбогдсон харилцагчдийн бичлэгийг устгах
conn = db.BeginTransaction("core", "Txn601010");
sql = @"delete from pledgemain where pledgeno=:1";
param = new object[] { pledgeno };
res = db.ExecuteQuery("core", sql, enumCommandType.DELETE, "Txn601010", param);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
if (res.AffectedRows <= 0)
{
//res = new Result(6010091, string.Format("[{0}] дугаартай тагийн мэдээлэл олдсонгүй!", tagno));
}
#endregion
#region Барьцааны бичлэгийн төлвийг чөлөөлсөн төлөвт оруулах
sql = @"update pledgedoc set status=1,unholduser=:2,unholddate=:3 where pledgeno=:1";
param = new object[] { pledgeno, ri.UserNo, DateTime.Now };
res = db.ExecuteQuery("core", sql, enumCommandType.UPDATE, "Txn601010", param);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
#endregion
}
catch (Exception ex)
{
res = new Result(9110002, "Програм руу нэвтрэхэд алдаа гарлаа" + ex.Message);
}
finally
{
if (conn != null)
{
if (res != null && res.ResultNo == 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
OnExit:
return(res);
} //Барьцааг чөлөөлөх
} //Барьцааны хамаарах түрээсийн хэрэгслүүд
public Result Txn601005(ClientInfo ci, RequestInfo ri, DbConnections db, ref Log lg)
{
#region Тооцоолол хийх дүрмийг заавал унш! бас бөглө!
/**************************************************************
* ТООЦООЛОЛ ХИЙХ ДҮРЭМ
* Шалгалт:
* 1. Харилцагч барьцаанд холбогдсон л бол CustomerIdDevice тэйбэлд бичлэг үүснэ.
* 2. Харилцагчийг барьцаанаас салгах үед CustomerIdDevice тэйблээс бичлэгийг устгана.
*
* Үйлдэл:
* 1. CustomerIdDevice тэйбэлд харилцагчийн холбоос бичлэг нэмэх. CustNo
*
*
**************************************************************/
#endregion
Result res = null;
DbConnection conn = null;
try
{
#region Prepare parameters
string pledgeno = Static.ToStr(ri.ReceivedParam[0]);
decimal pledgecustno = Static.ToDecimal(ri.ReceivedParam[1]);
decimal custno = Static.ToDecimal(ri.ReceivedParam[2]);
#endregion
#region Шалгалт1 - Төлөв шалгах
res = PledgeStatusCheck(db, pledgeno, pledgecustno);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
#endregion
#region Шалгалт2 - Харилцагч өөр этгээдэд холбогдсон эсэх
string sql = @"select p.custno,p.pledgeno
,decode(c.classcode,1,c.corporatename, c.lastname||' '||c.firstname) custname
from pledgemain p
left join customer c on c.customerno=p.custno
where p.custno=:1";
object[] param = new object[] { custno };
res = db.ExecuteQuery("core", sql, enumCommandType.SELECT, "Txn601005", param);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
if (res.AffectedRows > 0)
{
DataTable dt = res.Data.Tables[0];
decimal parentno = Static.ToDecimal(dt.Rows[0]["CUSTNO"]);
string custname = Static.ToStr(dt.Rows[0]["CUSTNAME"]);
string serialno = Static.ToStr(dt.Rows[0]["PLEDGENO"]);
res.ResultNo = 6010051;
res.ResultDesc = string.Format("[{0}-{1}] харилцагч [{2}] дугаартай барьцаан дээр холбоотой байна! ", custno, custname, pledgeno);
res.Data = null;
goto OnExit;
}
#endregion
conn = db.BeginTransaction("core", "Txn601005");
#region Үйлдэл1
//sql = @"insert into customeriddevice(custno, parentno) values(:1, :2)";
sql = @"insert into pledgemain(custno, pledgeno) values(:1, :2)";
param = new object[] { custno, pledgeno };
res = conn.ExecuteQuery(sql, enumCommandType.INSERT, "Txn601005", param);
if (res != null && res.ResultNo != 0)
{
if (res.ResultNo == 1)
{
res.ResultNo = 6010052;
res.ResultDesc = string.Format("{0} дугаартай харилцагч өөр харилцагч дээр холбоотой байна!", custno);
res.Data = null;
}
goto OnExit;
}
#endregion
}
catch (Exception ex)
{
res = new Result(9110002, "Програм руу нэвтрэхэд алдаа гарлаа" + ex.Message);
}
finally
{
if (conn != null)
{
if (res != null && res.ResultNo == 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
OnExit:
return(res);
} //Барьцаанд үйлчлүүлэгч холбох
} //Барьцаа хайж жагсаалт авах
public Result Txn601001(ClientInfo ci, RequestInfo ri, DbConnections db, ref Log lg)
{
Result res = null;
DbConnection conn = null;
try
{
#region Prepare parameters
string pledgeno = Static.ToStr(ri.ReceivedParam[0]);
int typeid = Static.ToInt(ri.ReceivedParam[1]);
string docno = Static.ToStr(ri.ReceivedParam[2]);
decimal custno = Static.ToDecimal(ri.ReceivedParam[3]);
string custname = Static.ToStr(ri.ReceivedParam[4]);
string phone = Static.ToStr(ri.ReceivedParam[5]);
string memo = Static.ToStr(ri.ReceivedParam[6]);
#endregion
#region Шалгалт1
string sql = @"select p.pledgeno, p.custname, p.contact from pledgedoc p where p.custno=:1 and status=0";
object[] param = new object[] { custno };
res = db.ExecuteQuery("core", sql, enumCommandType.SELECT, "Txn601001", param);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
if (res.AffectedRows > 0)
{
DataTable dt = res.Data.Tables[0];
pledgeno = Static.ToStr(dt.Rows[0]["pledgeno"]);
custname = Static.ToStr(dt.Rows[0]["custname"]);
res.ResultNo = 6010011;
res.ResultDesc = string.Format("[{0}] харилцагч [{1}-{2}] барьцаан дээр холбоотой байна! ", custno, pledgeno, custname);
res.Data = null;
goto OnExit;
}
#endregion
#region Барьцааны шинэ дугаарлалт авах
IPos.Core.AutoNumEnum enums = new IPos.Core.AutoNumEnum();
enums.Y = Static.ToStr(Static.ToDate(DateTime.Now).Year);
enums.L = Static.ToStr(typeid);
res = IPos.Core.SystemProp.gAutoNum.GetNextNumber(db, 8, "", enums);
if (res != null && res.ResultNo != 0)
{
if (string.IsNullOrEmpty(res.ResultDesc))
{
res.ResultNo = 9110068;
res.ResultDesc = "Автомат дугаарлалтын хөрвүүлэлт дээр алдаа гарлаа. [ID:12][";
res.Param = null;
res.Data = null;
}
goto OnExit;
}
pledgeno = res.ResultDesc;
#endregion
#region Барьцааг pledgedoc тэйбэлд бүртгэх
conn = db.BeginTransaction("core", "Txn601001");
sql = @"insert into pledgedoc
(pledgeno,doctype,docno,holddate,holduser,status,custno,custname,contact,memo)
values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)";
param = new object[] { pledgeno, typeid, docno, DateTime.Now, ri.UserNo, 0, custno, custname, phone, memo };
res = conn.ExecuteQuery(sql, enumCommandType.INSERT, "Txn601001", param);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
#endregion
#region Харилцагчийг pledgemain тэйбэлд бүртгэх
sql = @"insert into pledgemain (custno,pledgeno) values(:1,:2)";
param = new object[] { custno, pledgeno };
res = conn.ExecuteQuery(sql, enumCommandType.INSERT, "Txn601001", param);
if (res != null && res.ResultNo != 0)
{
goto OnExit;
}
#endregion
#region Барьцааны үндсэн харилцагчийг холбоотой үйлчлүүлэгчийн тэйбэлд оруулах
////sql = "insert into pledgemain(custno,pledgeno) values(:1,:2)";
//sql = "insert into customeriddevice(custno,parentno) values(:1,:2)";
//param = new object[] { custno, custno };
//res = conn.ExecuteQuery(sql, enumCommandType.INSERT, "Txn601001", param);
//if (res.ResultNo == 1)
//{
// //Хэрэв өмнө нь бичлэг үүссэн бол шууд ашиглана.
// res.ResultNo = 0;
// res.ResultDesc = "";
//}
if (res.ResultNo == 0)
{
res.ResultDesc = pledgeno;
}
#endregion
}
catch (Exception ex)
{
res = new Result(9110002, "Програм руу нэвтрэхэд алдаа гарлаа" + ex.Message);
}
finally
{
if (conn != null)
{
if (res != null && res.ResultNo == 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
OnExit:
return(res);
} //Барьцаа шинээр үүсгэх
public static Result DB2131109(DbConnections pDB)
{
Result res = new Result();
DbConnection con = null;
try
{
con = pDB.BeginTransaction("core", "Start");
#region [ INSERT ]
string sql = @"insert into ContractMain_Hist
select *
from ContractMain
where ValidEndDate<sysdate ";
res = pDB.ExecuteQuery("core", sql, enumCommandType.INSERT, "DB2131109", null);
if (res.ResultNo != 0)
{
return(res);
}
sql = @"insert into ContractProd_Hist
select P.*
from ContractProd P
left join ContractMain M on M.ContractNo=P.ContractNo
where M.ValidEndDate<sysdate ";
res = pDB.ExecuteQuery("core", sql, enumCommandType.INSERT, "DB2131109", null);
if (res.ResultNo != 0)
{
return(res);
}
sql = @"insert into ContractAcnt_Hist
select A.*
from ContractAcnt A
left join ContractMain M on M.ContractNo=A.ContractNo
where M.ValidEndDate<sysdate";
res = pDB.ExecuteQuery("core", sql, enumCommandType.INSERT, "DB2131109", null);
if (res.ResultNo != 0)
{
return(res);
}
sql = @"insert into DepSchedule_Hist
select A.*
from DepSchedule A
left join ContractMain M on M.ContractNo=A.ContractNo
where M.ValidEndDate<sysdate";
res = pDB.ExecuteQuery("core", sql, enumCommandType.INSERT, "DB2131109", null);
if (res.ResultNo != 0)
{
return(res);
}
#endregion
#region [ DELETE ]
sql = @"Delete from ContractProd where contractno in (select contractno from ContractMain where ValidEndDate<sysdate)";
res = pDB.ExecuteQuery("core", sql, enumCommandType.DELETE, "DB2131109", null);
if (res.ResultNo != 0)
{
return(res);
}
sql = @"Delete from ContractAcnt where contractno in (select contractno from ContractMain where ValidEndDate<sysdate)";
res = pDB.ExecuteQuery("core", sql, enumCommandType.DELETE, "DB2131109", null);
if (res.ResultNo != 0)
{
return(res);
}
sql = @"Delete from DepSchedule where contractno in (select contractno from ContractMain where ValidEndDate<sysdate)";
res = pDB.ExecuteQuery("core", sql, enumCommandType.DELETE, "DB2131109", null);
if (res.ResultNo != 0)
{
return(res);
}
sql = @"Delete from ContractMain where ValidEndDate<sysdate";
res = pDB.ExecuteQuery("core", sql, enumCommandType.DELETE, "DB2131109", null);
return(res);
#endregion
}
catch (Exception ex)
{
res.ResultNo = 9110001;
res.ResultDesc = "Датабааз руу хандахад алдаа гарлаа" + ex.Message;
return(res);
}
finally
{
if (res.ResultNo == 0)
{
con.Commit();
}
else
{
con.Rollback();
}
}
return(res);
}
private Result Analyze(DbConnections db, int reportpriv)
{//Done. 2011/07/06
Result r = null;
DbConnection conn = null;
try
{
string rid = string.Format("R{0}", reportpriv);
#region Create Instance of WorkBook
IWorkbook book = OpenWorkBook(reportpriv);
if (book == null)
{
r = new Result(9, string.Format("Report file is not openned! Report id = {0}", rid));
return(r);
}
#endregion
#region Declare Bind Arrays
List <string> rids = new List <string>();
List <int> rows = new List <int>();
List <int> cols = new List <int>();
List <int> types = new List <int>();
List <string> vals = new List <string>();
List <string> dtypes = new List <string>();
List <int> dvalues = new List <int>();
List <string> branches = new List <string>();
List <string> curs = new List <string>();
List <string> tocurs = new List <string>();
List <int> rounds = new List <int>();
List <string> rids1 = new List <string>();
List <string> rids2 = new List <string>();
List <DateTime> chgd = new List <DateTime>();
#endregion
#region Loop for each cell
Hashtable nodes = new Hashtable(); // include report names of depending sub reports
for (int row = 1; row <= book.Worksheets[1].UsedRange.Rows.Count; row++)
{
for (int col = 1; col <= book.Worksheets[1].UsedRange.Columns.Count; col++)
{
#region Keep cell parameters
string commFormula = "";
string commTermType = "";
string commTermValue = "";
string commBranch = "";
string commFromCur = "";
string commToCur = "";
string commRound = "";
IComment comment = book.Worksheets[1].UsedRange[row, col].Comment;
if (comment != null)
{
Hashtable ht = GetCommentHashTable(comment.Text);
commFormula = GetCommentValue(ht, "Formula", "N");
commTermType = GetCommentValue(ht, "TermType", "M");
commTermValue = GetCommentValue(ht, "TermValue", "0");
commBranch = GetCommentValue(ht, "Branch", "");
commFromCur = GetCommentValue(ht, "FromCurrency", "MNT");
commToCur = GetCommentValue(ht, "ToCurrency", "MNT");
commRound = GetCommentValue(ht, "Round", "1");
}
if (commFormula == "Y" || commFormula == "y")
{
string cellvalue = Convert.ToString(book.Worksheets[1].UsedRange[row, col].Value);
//Match match = Regex.Match(cellvalue, "(^|[-+%*/ ])([YQMD][ODCB][EY][0-9]+)($?)");
//if (match.Success)
if (!string.IsNullOrEmpty(cellvalue))
{
rids.Add(rid);
rows.Add(book.Worksheets[1].UsedRange.Row + row - 1);
cols.Add(book.Worksheets[1].UsedRange.Column + col - 1);
types.Add(1);
vals.Add(cellvalue);
dtypes.Add(commTermType);
dvalues.Add(ISM.Lib.Static.ToInt(commTermValue));
branches.Add(commBranch);
curs.Add(commFromCur);
tocurs.Add(commToCur);
rounds.Add(ISM.Lib.Static.ToInt(commRound));
}
#region Keep sub reports
Regex re = new Regex(@"([A-Z0-9]+)![A-Z]+\d+", RegexOptions.IgnoreCase);
MatchCollection mc = re.Matches(cellvalue, 0);
DateTime now = DateTime.Now;
foreach (Match m in mc)
{
if (!nodes.ContainsKey(m.Groups[1].Value))
{
nodes.Add(m.Groups[1].Value, null);
rids1.Add(rid);
rids2.Add(m.Groups[1].Value);
chgd.Add(now);
}
}
#endregion
}
#endregion
}
}
#endregion
#region Save formula cells into db
#region Begin Db transaction
conn = db.BeginTransaction("core", "Chart Report Update");
#endregion
#region Delete old records
r = Main.DB107102(db, rid);
if (r == null || r.ResultNo != 0)
{
return(r);
}
#endregion
#region Insert Cell values
if (rids.Count > 0)
{
r = Main.DB107101(db
, rids.ToArray()
, rows.ToArray()
, cols.ToArray()
, types.ToArray()
, vals.ToArray()
, dtypes.ToArray()
, dvalues.ToArray()
, branches.ToArray()
, curs.ToArray()
, tocurs.ToArray()
, rounds.ToArray()
);
}
#endregion
#endregion
#region Save sub reports into db
#region Begin Db transaction
conn = db.BeginTransaction("core", "Chart Report Update");
#endregion
#region Delete old records
r = Main.DB107105(db, rid);
if (r == null || r.ResultNo != 0)
{
return(r);
}
#endregion
#region Insert sub report names
if (rids1.Count > 0)
{
r = Main.DB107104(db
, rids1.ToArray()
, rids2.ToArray()
, chgd.ToArray()
);
}
#endregion
#endregion
r.Data = null;
r.Param = null;
}
catch (Exception ex)
{
r = new Result(9, ex.ToString());
}
finally
{
if (conn != null)
{
if (r != null && r.ResultNo == 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
return(r);
}
public Result FN108303(ClientInfo ci, RequestInfo ri, DbConnections db, ref Log lg)
{
Result r = null;
DbConnection conn = null;
try
{
#region Param
int pTypeCode = ISM.Lib.Static.ToInt(ri.ReceivedParam[0]);
ulong pTypeId = ISM.Lib.Static.ToULong(ri.ReceivedParam[1]);
int pStepId = ISM.Lib.Static.ToInt(ri.ReceivedParam[2]);
int pStepItemId = ISM.Lib.Static.ToInt(ri.ReceivedParam[3]);
int pOwner = ISM.Lib.Static.ToInt(ri.ReceivedParam[4]);
decimal pPerformance = ISM.Lib.Static.ToDecimal(ri.ReceivedParam[5]);
DateTime pStarted = ISM.Lib.Static.ToDateTime(ri.ReceivedParam[6]);
DateTime pFinished = ISM.Lib.Static.ToDateTime(ri.ReceivedParam[7]);
int pStatus = ISM.Lib.Static.ToInt(ri.ReceivedParam[8]);
DateTime pSysDate = DateTime.Now; //ISM.Lib.Static.ToDateTime(ri.ReceivedParam[9]);
decimal pProgress = ISM.Lib.Static.ToDecimal(ri.ReceivedParam[10]);
string pComment = ISM.Lib.Static.ToStr(ri.ReceivedParam[11]);
int pNewOwner = ISM.Lib.Static.ToInt(ri.ReceivedParam[12]);
#endregion
int owner = pNewOwner != 0 ? pNewOwner : pOwner;
if (pPerformance == 0)
{
owner = 0;
pStarted = DateTime.MinValue;
pFinished = DateTime.MinValue;
pStatus = 0;
}
conn = db.BeginTransaction("core", "FN108303");
r = Main.DB108303(db, pTypeCode, pTypeId, pStepId, pStepItemId, owner, pPerformance, pStarted, pFinished, pStatus);
if (r.ResultNo != 0)
{
goto OnExit;
}
if (pProgress != 0 || !string.IsNullOrEmpty(pComment) || pNewOwner != 0)
{
ulong txnid = EServ.Interface.Sequence.NextByVal("StepTxnId");
r = Main.DB108403(db, txnid, pTypeCode, pTypeId, pStepItemId, owner, pStarted, ri.UserNo, pSysDate, pProgress, pPerformance, pComment);
if (r.ResultNo != 0)
{
goto OnExit;
}
}
//r = Main.DB108303(db, pTypeCode, pTypeId, pStepId, pStepItemId, owner, 0, pStarted, DateTime.MinValue, 0);
//if (r.ResultNo != 0) goto OnExit;
}
catch (Exception ex)
{
r = new Result(9, ex.ToString());
}
OnExit:
if (r != null && conn != null)
{
if (r.ResultNo == 0)
{
conn.Commit();
}
else
{
conn.Rollback();
}
}
return(r);
}