protected IDataReader Get_Additional_Items()
{
IDataReader idr = null;
if (Request.QueryString["gid"] == null)
{
iDB.Communicate dbComm = new iDB.Communicate();
string query = string.Format("select *, \"Amount\" = Case When addtionalTransAmountDependent = 1 Then Cast(additionalTransAmount as varchar) + '%' Else Cast(additionalTransAmount as varchar) End From fin_ModuleItems{0}", string.Format(" where moduleId={0}", moduleId));
query += string.Format(" and addtionalTransIsOfficial={0}", isOfficial);
query += " and (moduleIParameterId=0 or moduleIParameterId is null)";
//if (isOfficial == 1)
//{
// query += " and additionalTransIsOfficial=1";
//}
idr = dbComm.SelectCMD(query);
}
return(idr);
}
protected void Load_Related_Data(int groupID)
{
bool hasTrans = false;
IDataReader idr = null;
iDB.Communicate dbComm = new iDB.Communicate();
string query = string.Format("select *, (userFirstName + ' ' + userLastName) as CreatedBy, (Convert(varchar, IsNull(transGroupPrefixNo, 0)) + transGroupPrefixString + Convert(varchar, IsNull(transGroupForeNumber, 0))) as voucharNo from vw_page_transactions where transLinkedToGroup={0}", groupID);
idr = dbComm.SelectCMD(query);
if (idr != null)
{
while (idr.Read())
{
hasTrans = true;
}
}
idr = dbComm.SelectCMD(query);
lvGridRelated.DataSource = idr;
lvGridRelated.DataBind();
rdPanelRelated.Visible = hasTrans;
}
private void Load_Data()
{
IDataReader idr = new iDB.Communicate().SelectCMD("select * from fin_Taxes");
lvGrid.DataSource = idr;
lvGrid.DataBind();
}
public IDataReader Select_Branches()
{
iDB.Communicate dbComm = new iDB.Communicate();
IDataReader idr = dbComm.SelectCMD("select * from fin_Branches");
return(idr);
}
protected void Load_Entities()
{
iDB.Communicate dbComm = new iDB.Communicate();
IDataReader idr = dbComm.SelectCMD("select * from vw_Entities");
lvGrid.DataSource = idr;
lvGrid.DataBind();
}
protected string Imbalanced_Vouchars()
{
iDB.Communicate dbComm = new iDB.Communicate();
iBiz.FinPro.Transactions.Groups bTransG = new iBiz.FinPro.Transactions.Groups();
iBiz.FinPro.Transactions.Groups.objGroup objTransG = new iBiz.FinPro.Transactions.Groups.objGroup();
iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact();
iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
IDataReader idr = dbComm.SelectCMD("Select transGroupID, Sum(transAmount) as transAmount, max(transNarration) as transNarration, max(transCreatedOn) as transCreatedOn from fin_Transactions where transGroupID in (select transGroupID from fin_TransGroups) group by transGroupID");
string output = "";
if (idr != null)
{
while (idr.Read())
{
int transGroupID = Convert.ToInt32(idr["transGroupID"]);
objTransG = new iBiz.FinPro.Transactions.Groups.objGroup();
objTransG = bTransG.Select(transGroupID);
decimal crTotal = 0, drTotal = 0, OCrTotal = 0, ODrTotal = 0;
if (objTransG != null)
{
if (objTransG.Get_Transactions() != null)
{
foreach (var item in objTransG.Get_Transactions())
{
if (item.transDrAccount == null)
{
OCrTotal += item.transAmount;
}
else
{
ODrTotal += item.transAmount;
}
}
}
if (ODrTotal == OCrTotal)
{
//output += string.Format("<td>Balanced<td>");
}
else
{
if (!string.IsNullOrEmpty(output))
{
output += ",";
}
output += transGroupID.ToString();
}
}
}
}
return(output);
}
public IDataReader Get_Preselected_Items(int moduleId, int moduleIParameterId, bool isOfficial)
{
iDB.Communicate dbComm = new iDB.Communicate();
IDataReader idr = null;
string query = string.Format("exec SP_fin_Preselected_Vouchar_Accounts @moduleId={0}, @moduleParameterId={1}, @moduleIsOfficial={2}", moduleId, moduleIParameterId, isOfficial);
idr = dbComm.SelectCMD(query);
return(idr);
}
protected void Payments()
{
iDB.Communicate dbComm = new iDB.Communicate();
iBiz.FinPro.Transactions.Groups bTransG = new iBiz.FinPro.Transactions.Groups();
iBiz.FinPro.Transactions.Groups.objGroup objTransG = new iBiz.FinPro.Transactions.Groups.objGroup();
iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact();
iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
IDataReader idr = dbComm.SelectCMD("Select transGroupID, Sum(transAmount) as transAmount, max(transNarration) as transNarration, max(transCreatedOn) as transCreatedOn from fin_Transactions where transType=3 group by transGroupID");
if (idr != null)
{
while (idr.Read())
{
int transGroupId = Convert.ToInt32(idr["transGroupId"]);
objTransG.transGroupCreatedBy = 1;
objTransG.transGroupCreatedOn = Convert.ToDateTime(idr["transCreatedOn"]);
objTransG.transGroupForeNumber = bTransG.New_Group_Fore_Number(2, "INV", DateTime.Now);
objTransG.transGroupID = transGroupId;
objTransG.transGroupPrefixNo = 2;
objTransG.transGroupPrefixString = "INV";
objTransG.transGroupStatus = 1;
objTransG.transGroupTitle = "Invoice Created on " + objTransG.transGroupCreatedOn.ToString("MMM dd, yyyy");
objTransG.transGroupTotalAmount = Convert.ToDecimal(idr["transAmount"]);
objTransG.transTransCount = 0;
bTransG.Add(objTransG);
}
}
idr = null;
idr = dbComm.SelectCMD("SELECT * FROM [fin_Transactions] where (transType=3) and Not (transDrAccount is Null or transCrAccount is Null)");
if (idr != null)
{
while (idr.Read())
{
int transId = Convert.ToInt32(idr["transId"]);
iBiz.FinPro.Transactions.Transact.objTransaction objTempTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans = bTrans.Select(transId);
objTempTrans = bTrans.Select(transId);
objTrans.transCrAccount = null;
//objTrans.transType = 2;
objTempTrans.transDrAccount = null;
//objTempTrans.transType = 2;
bTrans.Update(objTrans);
bTrans.Add(objTempTrans);
}
}
}
protected void Fix_Inv()
{
iDB.Communicate dbComm = new iDB.Communicate();
IDataReader idr = dbComm.SelectCMD("select transGroupID, sum(transAmount) as transAmount, max(transCreatedOn) as transCreatedOn from fin_transactions where (transDrAccount=84 or transCRAccount=84) and transAmount>0 and transSystemIndex=2 and Not transGroupID in (select transGroupID from fin_transactions where transDrAccount=84 and transType=44) group by transGroupID");
iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact();
if (idr != null)
{
while (idr.Read())
{
int transGroupID = Convert.ToInt32(idr["transGroupID"]);
decimal transTotalAmount = Convert.ToDecimal(idr["transAmount"]);
DateTime transCreatedOn = Convert.ToDateTime(idr["transCreatedOn"]);
iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans.transSystemIndex = 2;
objTrans.transParticipantID = 8;
objTrans.transDrAccount = 84;
objTrans.transNarration = "Inventory Sold from DHA Shop";
objTrans.transAmount = transTotalAmount;
objTrans.transCreatedBy = 1;
objTrans.transCreatedOn = transCreatedOn;
objTrans.transUpdatedBy = 1;
objTrans.transUpdatedOn = DateTime.Now;
objTrans.transStatus = 1;
objTrans.transSystemRestrict = false;
objTrans.transGroupID = transGroupID;
objTrans.transIsCompound = true;
objTrans.transType = 44;
bTrans.Add(objTrans);
objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans.transSystemIndex = 2;
objTrans.transParticipantID = 8;
objTrans.transCrAccount = 16;
objTrans.transNarration = "Inventory Sold from DHA Shop";
objTrans.transAmount = transTotalAmount;
objTrans.transCreatedBy = 1;
objTrans.transCreatedOn = transCreatedOn;
objTrans.transUpdatedBy = 1;
objTrans.transUpdatedOn = DateTime.Now;
objTrans.transStatus = 1;
objTrans.transSystemRestrict = false;
objTrans.transGroupID = transGroupID;
objTrans.transIsCompound = true;
objTrans.transType = 44;
bTrans.Add(objTrans);
}
}
}
public IDataReader Select_By_Type(int AccountCategoryID, int accountParent)
{
IDataReader idr = null;
iDB.Communicate dbComm = new iDB.Communicate();
string query = "select * from fin_Accounts where accountParent=" + accountParent;
query += " and accountType=" + AccountCategoryID.ToString();
idr = dbComm.SelectCMD(query);
return(idr);
}
protected IDataReader Get_Grid_Data()
{
int maxRows = 0;
iDB.Communicate dbComm = new iDB.Communicate();
IDataReader idr = null;
string query = "";
query = string.Format("select *, (userFirstName + ' ' + userLastName) as CreatedBy, (Convert(varchar, IsNull(transGroupPrefixNo, 0)) + transGroupPrefixString + Convert(varchar, IsNull(transGroupForeNumber, 0))) as voucharNo, ROW_NUMBER() Over (Order by transGroupCreatedOn desc) as RowNo from vw_page_transactions where transGroupId in ({0})", Imbalanced_Vouchars());
idr = dbComm.SelectCMD(query);
return(idr);
}
public IDataReader Select(bool isOfficial)
{
IDataReader idr = null;
iDB.Communicate dbComm = new iDB.Communicate();
string query = "select * from fin_Accounts where accountSystemIndex in (0, 1) order by accountTitle asc";
if (isOfficial)
{
query = "select * from fin_Accounts where accountSystemIndex in (1, 2) order by accountTitle asc";
}
idr = dbComm.SelectCMD(query);
return(idr);
}
public IDataReader Select_By_Type(int AccountCategoryID, bool isOfficial)
{
IDataReader idr = null;
iDB.Communicate dbComm = new iDB.Communicate();
string query = "select * from fin_Accounts where accountSystemIndex in (0, 1)";
if (isOfficial)
{
query = "select * from fin_Accounts where accountSystemIndex in (1, 2)";
}
query += " and accountType=" + AccountCategoryID.ToString();
idr = dbComm.SelectCMD(query);
return(idr);
}
public int GetAccountId(string accountTitle)
{
int accountId = 0;
IDataReader idr = null;
iDB.Communicate dbComm = new iDB.Communicate();
string query = "select * from fin_Accounts where accountTitle='" + accountTitle.Trim() + "'";
idr = dbComm.SelectCMD(query);
if (idr != null)
{
while (idr.Read())
{
accountId = Convert.ToInt32(idr["accountId"]);
}
}
return(accountId);
}
protected IDataReader Get_Grid_Data()
{
iDB.Communicate dbComm = new iDB.Communicate();
IDataReader idr = null;
string query = "";
query = string.Format("select *, (userFirstName + ' ' + userLastName) as CreatedBy, (Convert(varchar, IsNull(transGroupPrefixNo, 0)) + transGroupPrefixString + Convert(varchar, IsNull(transGroupForeNumber, 0))) as voucharNo, ROW_NUMBER() Over (Order by transGroupCreatedOn desc) as RowNo from vw_page_transactions where transGroupPrefixNo={0}", moduleId);
if (isOfficial == 1)
{
query = string.Format("select *, IsNull(transGroupOfficialTotalAmount, 0) as groupTotal, (userFirstName + ' ' + userLastName) as CreatedBy, (Convert(varchar, IsNull(transGroupPrefixNo, 0)) + transGroupPrefixString + Convert(varchar, IsNull(transGroupForeNumber, 0))) as voucharNo, ROW_NUMBER() Over (Order by transGroupCreatedOn desc) as RowNo from vw_page_transactions where not transGroupStatus=3 and transGroupPrefixNo={0}", moduleId);
//query += " and transGroupIsOfficial=1"; // Official Condition
DateTime?nullDate = null;
tbFromDate_CalendarExtender.SelectedDate = tbFromDate.Text.Trim().Length > 0 ? Convert.ToDateTime(tbFromDate.Text) : nullDate;
tbToDate_CalendarExtender.SelectedDate = tbToDate.Text.Trim().Length > 0 ? Convert.ToDateTime(tbToDate.Text) : nullDate;
query += tbFromDate_CalendarExtender.SelectedDate.HasValue && !tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and transGroupCreatedOn>='{0}'", Convert.ToDateTime(tbFromDate.Text)) : "";
query += !tbFromDate_CalendarExtender.SelectedDate.HasValue && tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and transGroupCreatedOn<='{0}'", Convert.ToDateTime(tbToDate.Text)) : "";
query += !tbFromDate_CalendarExtender.SelectedDate.HasValue && tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and (transGroupCreatedOn between '{0}' and '{1}')", Convert.ToDateTime(tbFromDate.Text), Convert.ToDateTime(tbToDate.Text)) : "";
query += tbDescription.Text.Trim().Length >= 0 ? string.Format(" and transGroupTitle like '%{0}%'", tbDescription.Text.Trim()) : "";
}
else
{
query = string.Format("select *, transGroupTotalAmount as groupTotal, (userFirstName + ' ' + userLastName) as CreatedBy, (Convert(varchar, IsNull(transGroupPrefixNo, 0)) + transGroupPrefixString + Convert(varchar, IsNull(transGroupForeNumber, 0))) as voucharNo, ROW_NUMBER() Over (Order by transGroupCreatedOn desc) as RowNo from vw_page_transactions where not transGroupStatus=3 and transGroupPrefixNo={0}", moduleId);
DateTime?nullDate = null;
tbFromDate_CalendarExtender.SelectedDate = tbFromDate.Text.Trim().Length > 0 ? Convert.ToDateTime(tbFromDate.Text) : nullDate;
tbToDate_CalendarExtender.SelectedDate = tbToDate.Text.Trim().Length > 0 ? Convert.ToDateTime(tbToDate.Text) : nullDate;
query += tbFromDate_CalendarExtender.SelectedDate.HasValue && !tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and transGroupCreatedOn>='{0}'", Convert.ToDateTime(tbFromDate.Text)) : "";
query += !tbFromDate_CalendarExtender.SelectedDate.HasValue && tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and transGroupCreatedOn<='{0}'", Convert.ToDateTime(tbToDate.Text)) : "";
query += !tbFromDate_CalendarExtender.SelectedDate.HasValue && tbToDate_CalendarExtender.SelectedDate.HasValue ? string.Format(" and (transGroupCreatedOn between '{0}' and '{1}')", Convert.ToDateTime(tbFromDate.Text), Convert.ToDateTime(tbToDate.Text)) : "";
query += tbDescription.Text.Trim().Length >= 0 ? string.Format(" and transGroupTitle like '%{0}%'", tbDescription.Text.Trim()) : "";
}
idr = dbComm.SelectCMD("select IsNull(max(RowNo), 0) as maxRows from (" + query + ") as Results");
if (idr != null)
{
while (idr.Read())
{
maxRows = Convert.ToInt32(idr["maxRows"]);
}
}
int recordNoFrom = (pageSize * currentPage) - pageSize;
int recordNoTo = recordNoFrom + pageSize;
query = string.Format("select * from (" + query + ") as results where RowNo>={0} and RowNo<{1}", recordNoFrom, recordNoTo);
idr = dbComm.SelectCMD(query);
Do_Pagination(null, null);
return(idr);
}
protected void btnCreate_Click(object sender, EventArgs e)
{
iDB.Communicate dbComm = new iDB.Communicate();
DataTable dt = new DataTable();
IDataReader idr = null;
SortedList <string, object> srt = new SortedList <string, object>();
string moduleName = ".FinPro";
string bizModuleName = ".FinPro";
string bizPath = @"D:\Projects\FinPro\FinPro\iBiz\FinPro\Modules\" + tbClassFileName.Text + ".cs";
string idbPath = @"D:\Projects\FinPro\FinPro\iDB\FinPro\Modules\" + tbClassFileName.Text + ".cs";
//dt = dbComm.SelectExecute(iDB.Payroll.Communicate.StoredProcedures.coder, srt);
//idr = dbComm.SelectCMD("sp_");
using (SqlConnection Cn = new SqlConnection("Data Source=localhost;Initial Catalog=FinApp; User Id=sa; Password=Aa1Bb2!@"))
{
SqlCommand Cm = new System.Data.SqlClient.SqlCommand();
SqlDataAdapter Adp = new SqlDataAdapter();
DataSet ds = new DataSet();
Cm.Connection = Cn;
Cm.CommandText = string.Format("sp_help '{0}'", tbname.Text);
Cm.Connection.Open();
//Adp.FillSchema(ds, SchemaType.Source);
//Cm.ExecuteNonQuery();
//idr = Cm.ExecuteReader();
Adp.SelectCommand = Cm;
Adp.Fill(ds);
dt = ds.Tables[1];
//return idr;
}
if (dt != null)
{
// Create Procedure
string proc = "Create Procedure SP_" + tbprocname.Text + "(<br />";
string structProc = string.Empty;
string insertVar = string.Empty;
string updateVar = string.Empty;
string primaryCol = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
string colName = dt.Rows[i]["Column_name"].ToString();
string colType = dt.Rows[i]["Type"].ToString();
string colLength = dt.Rows[i]["Length"].ToString();
string colNullable = dt.Rows[i]["Nullable"].ToString();
string colPrec = dt.Rows[i]["Prec"].ToString();
string colScale = dt.Rows[i]["Scale"].ToString();
string colLengthAdd = "";
if (colType.ToLower() == "decimal")
{
colLengthAdd = "(" + colPrec + ", " + colScale + ")";
}
else if (colType.ToLower() == "varchar" || colType.ToLower() == "nvarchar")
{
colLengthAdd = "(" + colLength + ")";
}
colType += " " + colLengthAdd;
colName = "@" + colName;
if (i != 0)
{
if (i != dt.Rows.Count - 1)
{
insertVar += colName + ",";
updateVar += colName.Replace("@", "") + "=" + colName + ",";
}
else
{
insertVar += colName;
updateVar += colName.Replace("@", "") + "=" + colName;
}
}
else
{
primaryCol = colName;
}
structProc += colName + " " + colType + "=null,<br />";
}
structProc += "@Flg int";
string Creater = "Create Procedure " + tbprocname.Text + "(<br />";
string closeCreater = ") as Begin <br />";
dt.TableName = tbname.Text;
string insertedID = String.Format("Declare @lastID int<br />Set @lastID=IsNull((select max({0}) from {1}), 0)<br />Set {2}=@lastID+1<br />", primaryCol.Replace("@", ""), dt.TableName, primaryCol);
string flg1 = String.Format("if @Flg=1<br />Begin<br />{1}<br />insert into " + dt.TableName + " output inserted.{0} values(" + insertVar + ") <br />End<br />", primaryCol.Replace("@", ""), insertedID);
string flg2 = "if @Flg=2<br />Begin<br />update " + dt.TableName + " set " + updateVar + " where " + primaryCol.Replace("@", "") + "=" + primaryCol + " <br />End<br />";
string flg3 = "if @Flg=3<br />Begin<br />delete from " + dt.TableName + " where " + primaryCol.Replace("@", "") + "=" + primaryCol + " <br />End<br />";
string flg4 = "if @Flg=4<br />Begin<br /> select * from " + dt.TableName + " <br />End<br />";
string flg5 = "if @Flg=5<br />Begin<br /> select * from " + dt.TableName + " where " + primaryCol.Replace("@", "") + "=" + primaryCol + "<br />End<br />";
string hugeSeprator = "<br /><br />";
string dbCreater = Creater + structProc + closeCreater + flg1 + flg2 + flg3 + flg4 + flg5 + "<br /> End" + hugeSeprator;
//dbComm.Execute(dbCreater.Replace("<br />", " "));
//Create DB Class
string dbCommClass = "public class " + tbname.Text + "<br />{<br />Communicate dbComm = new Communicate();<br />SortedList" + Server.HtmlEncode("<string, object>") + " srt = new " + Server.HtmlEncode("SortedList<string, object>()") + ";<br />";
string dbAdd = "public void Add(";
string dbUpdate = "public void Update(";
string dbDelete = "public void Delete(";
string dbSelect = "public IDataReader Select()<br />";
string dbSelectThis = "public IDataReader Select(";
string dbAllAttribs = "";
string firstAttrib = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
string colName = dt.Rows[i]["Column_name"].ToString();
string colType = dt.Rows[i]["Type"].ToString();
string colLength = dt.Rows[i]["Length"].ToString();
string colNullable = dt.Rows[i]["Nullable"].ToString();
string colPrec = dt.Rows[i]["Prec"].ToString();
string colScale = dt.Rows[i]["Scale"].ToString();
string colTypeCSharpe = "";
if (colType.ToLower().Contains("varchar"))
{
colTypeCSharpe = "string";
}
else if (colType.ToLower() == "int" || colType.ToLower() == "tinyint")
{
colTypeCSharpe = "int";
}
else if (colType.ToLower() == "decimal" || colType.ToLower() == "money")
{
colTypeCSharpe = "decimal";
}
else if (colType.ToLower() == "datetime")
{
colTypeCSharpe = "DateTime";
}
else if (colType.ToLower() == "bit")
{
colTypeCSharpe = "bool";
}
if (colNullable.ToLower() == "yes" && colTypeCSharpe != "string")
{
colTypeCSharpe += "? ";
}
else
{
colTypeCSharpe += " ";
}
dbAllAttribs += "srt.Add(\"" + colName + "\", " + colName + ");<br />";
if (i == 0)
{
dbAdd += colTypeCSharpe + colName;
dbUpdate += colTypeCSharpe + colName;
dbDelete += colTypeCSharpe + colName;
dbSelectThis += colTypeCSharpe + colName;
firstAttrib += "srt.Add(\"" + colName + "\", " + colName + ");<br />";
}
else
{
dbAdd += ", " + colTypeCSharpe + colName;
dbUpdate += ", " + colTypeCSharpe + colName;
}
}
dbAdd += "){<br />srt.Clear();<br />" + dbAllAttribs + "<br />srt.Add(\"Flg\", 1);<br />dbComm.Execute(srt, Communicate.StoredProcedures." + tbproc.Text + ");<br />}<br />";
dbUpdate += "){<br />srt.Clear();<br />" + dbAllAttribs + "<br />srt.Add(\"Flg\", 2);<br />dbComm.Execute(srt, Communicate.StoredProcedures." + tbproc.Text + ");<br />}<br />";
dbDelete += "){<br />srt.Clear();<br />" + firstAttrib + "<br />srt.Add(\"Flg\", 3);<br />dbComm.Execute(srt, Communicate.StoredProcedures." + tbproc.Text + ");<br />}<br />";
dbSelect += "{<br />srt.Clear();<br />IDataReader idr = null;<br />" + "srt.Add(\"Flg\", 4);<br />idr = dbComm.SelectIDR(Communicate.StoredProcedures." + tbproc.Text + ", srt);<br />return idr;<br />}<br />";
dbSelectThis += "){<br />srt.Clear();<br />IDataReader idr = null;<br />" + firstAttrib + "<br />srt.Add(\"Flg\", 5);<br />idr = dbComm.SelectIDR(Communicate.StoredProcedures." + tbproc.Text + ", srt);<br />return idr;<br />}<br />";
dbCommClass += dbAdd + dbUpdate + dbDelete + dbSelect + dbSelectThis;
dbCommClass += "<br />}<br />";
// Create Business Object
string bizObject = "public class obj" + tbname.Text + "<br />{<br />";
for (int i = 0; i < dt.Rows.Count; i++)
{
string colName = dt.Rows[i]["Column_name"].ToString();
string colType = dt.Rows[i]["Type"].ToString();
string colLength = dt.Rows[i]["Length"].ToString();
string colNullable = dt.Rows[i]["Nullable"].ToString();
string colPrec = dt.Rows[i]["Prec"].ToString();
string colScale = dt.Rows[i]["Scale"].ToString();
string colTypeCSharpe = "";
if (colType.ToLower().Contains("varchar"))
{
colTypeCSharpe = "string";
}
else if (colType.ToLower() == "int" || colType.ToLower() == "tinyint")
{
colTypeCSharpe = "int";
}
else if (colType.ToLower() == "decimal" || colType.ToLower() == "money")
{
colTypeCSharpe = "decimal";
}
else if (colType.ToLower() == "datetime")
{
colTypeCSharpe = "DateTime";
}
else if (colType.ToLower() == "bit")
{
colTypeCSharpe = "bool";
}
if (colNullable.ToLower() == "yes" && colTypeCSharpe != "string")
{
colTypeCSharpe += "? ";
}
else
{
colTypeCSharpe += " ";
}
bizObject += "public " + colTypeCSharpe + colName + " { get; set; }<br />";
}
bizObject += "<br />}<br />";
//Create Business Class
string bizClass = "public class " + tbname.Text + "<br />{<br />iDB" + moduleName + "." + tbname.Text + " db = new iDB" + moduleName + "." + tbname.Text + "();<br />";
string addRoutine = "public void Add(obj" + tbname.Text + " i)<br />{<br />db.Add(";
string updateRoutine = "public void Update(obj" + tbname.Text + " i)<br />{<br />db.Update(";
string deleteRoutine = "public void Delete(int **0**)<br />{<br />db.Delete(**0**);<br />}<br />";
string selectRoutine = "public IDataReader Select()<br />{<br />return db.Select();<br />}<br />";
string selectThisRoutine = "public obj" + tbname.Text + " Select(int **0**)<br />{<br />IDataReader idr = db.Select(**0**);<br />return Select_Obj(idr);<br />}<br />";
string selectObjPrivate = "private obj" + tbname.Text + " Select_Obj(IDataReader idr)<br />{<br />obj" + tbname.Text + " o = new obj" + tbname.Text + "();<br />bool rtNull = true;<br />if (idr != null){<br />while (idr.Read())<br />{<br />rtNull = false;<br />";
for (int i = 0; i < dt.Rows.Count; i++)
{
string colName = dt.Rows[i]["Column_name"].ToString();
string colType = dt.Rows[i]["Type"].ToString();
string colLength = dt.Rows[i]["Length"].ToString();
string colNullable = dt.Rows[i]["Nullable"].ToString();
string colPrec = dt.Rows[i]["Prec"].ToString();
string colScale = dt.Rows[i]["Scale"].ToString();
string colTypeCSharpe = "";
if (colType.ToLower().Contains("varchar"))
{
colTypeCSharpe = "string";
}
else if (colType.ToLower() == "int" || colType.ToLower() == "tinyint")
{
colTypeCSharpe = "int32";
}
else if (colType.ToLower() == "decimal" || colType.ToLower() == "money")
{
colTypeCSharpe = "decimal";
}
else if (colType.ToLower() == "datetime")
{
colTypeCSharpe = "DateTime";
}
else if (colType.ToLower() == "bit")
{
colTypeCSharpe = "bool";
}
TextInfo tInfo = new CultureInfo("en-US", false).TextInfo;
selectObjPrivate += "if (idr[\"" + colName + "\"] != DBNull.Value){<br />o." + colName + " = Convert.To" + tInfo.ToTitleCase(colTypeCSharpe).Replace("Bool", "Boolean").Replace("Datetime", "DateTime") + "(idr[\"" + colName + "\"]);<br />}<br />";
if (colNullable.ToLower() == "yes" && colTypeCSharpe != "string")
{
colTypeCSharpe += "? ";
}
else
{
colTypeCSharpe += " ";
}
if (i == 0)
{
addRoutine += "i." + colName;
updateRoutine += "i." + colName;
deleteRoutine = deleteRoutine.Replace("**0**", colName);
selectThisRoutine = selectThisRoutine.Replace("**0**", colName);
}
else
{
addRoutine += ", i." + colName;
updateRoutine += ", i." + colName;
}
}
addRoutine += ");<br />}<br />";
updateRoutine += ");<br />}<br />";
deleteRoutine += "<br />";
selectThisRoutine += "<br />";
selectObjPrivate += "}<br />}<br /> if (rtNull){<br />o = null;<br />}<br />return o; <br />}<br />";
bizClass += addRoutine + updateRoutine + deleteRoutine + selectRoutine + selectThisRoutine + selectObjPrivate;
bizClass += "<br /><br />" + bizObject + "<br />}<br />";
string importChunks = "using System;<br />using System.Collections.Generic;<br />using System.Linq;<br />using System.Web;<br />using System.Data;<br />";
bizClass = importChunks + "<br />namespace iBiz" + bizModuleName + "{<br />" + bizClass + "<br />}";
dbCommClass = importChunks + "<br />namespace iDB" + moduleName + "{<br />" + dbCommClass + "<br />}";
Edit_Communicate(tbproc.Text, tbprocname.Text);
string[] bizFile = { Server.HtmlDecode(bizClass).Replace("<br />", "\r\n") };
System.IO.File.WriteAllLines(bizPath, bizFile);
string[] dbFile = { Server.HtmlDecode(dbCommClass).Replace("<br />", "\r\n") };
System.IO.File.WriteAllLines(idbPath, dbFile);
Response.Write(dbCreater + bizClass + dbCommClass);
//Response.Write(dbCreater + dbAdd + hugeSeprator + dbUpdate + hugeSeprator + dbDelete + hugeSeprator + dbSelect + hugeSeprator + dbSelectThis + hugeSeprator + allObj + hugeSeprator + objDBBind + hugeSeprator + bizAdd + hugeSeprator + bizUpdate + hugeSeprator + bizDelete + hugeSeprator + bizSelectObj + hugeSeprator + allControls);
}
}
protected void Vouchar_Verify()
{
iDB.Communicate dbComm = new iDB.Communicate();
iBiz.FinPro.Transactions.Groups bTransG = new iBiz.FinPro.Transactions.Groups();
iBiz.FinPro.Transactions.Groups.objGroup objTransG = new iBiz.FinPro.Transactions.Groups.objGroup();
iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact();
iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
IDataReader idr = dbComm.SelectCMD("Select transGroupID, Sum(transAmount) as transAmount, max(transNarration) as transNarration, max(transCreatedOn) as transCreatedOn from fin_Transactions where transGroupID in (select transGroupID from fin_TransGroups) group by transGroupID");
string output = "<table>";
if (idr != null)
{
while (idr.Read())
{
int transGroupID = Convert.ToInt32(idr["transGroupID"]);
objTransG = new iBiz.FinPro.Transactions.Groups.objGroup();
objTransG = bTransG.Select(transGroupID);
decimal crTotal = 0, drTotal = 0, OCrTotal = 0, ODrTotal = 0;
output += "<tr><td>" + transGroupID.ToString() + "</td>";
if (objTransG != null)
{
if (objTransG.Get_Transactions(true) != null)
{
foreach (var item in objTransG.Get_Transactions(true))
{
if (item.transDrAccount == null)
{
OCrTotal += item.transAmount;
}
else
{
ODrTotal += item.transAmount;
}
}
}
if (ODrTotal == OCrTotal)
{
output += string.Format("<td>Balanced<td>");
}
else
{
output += string.Format("<td>Imbalanced<td>");
}
if (objTransG.Get_Transactions(false) != null)
{
foreach (var item in objTransG.Get_Transactions(false))
{
if (item.transDrAccount == null)
{
crTotal += item.transAmount;
}
else
{
drTotal += item.transAmount;
}
}
}
if (drTotal == crTotal)
{
output += string.Format("<td>Balanced<td>");
}
else
{
output += string.Format("<td>Imbalanced<td>");
}
}
output += "</tr>";
}
}
output += "</table>";
Response.Write(output);
}
protected void Balance_Fabric_For_Sales()
{
iDB.Communicate dbComm = new iDB.Communicate();
iBiz.FinPro.Transactions.Groups bTransG = new iBiz.FinPro.Transactions.Groups();
iBiz.FinPro.Transactions.Groups.objGroup objTransG = new iBiz.FinPro.Transactions.Groups.objGroup();
iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact();
iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
IDataReader idr = dbComm.SelectCMD("Select transGroupID from fin_Transactions where transCrAccount=16 order by transGroupID desc");
if (idr != null)
{
while (idr.Read())
{
int transGroupId = Convert.ToInt32(idr["transGroupId"]);
decimal officialTotal = 0;
decimal unofficialTotal = 0;
IDataReader idrr = dbComm.SelectCMD(string.Format("select IsNull(sum(transAmount), 0) from (select distinct(transAmount) from fin_Transactions where transGroupID={0} and not transCrAccount=16 and transSystemIndex=2) as resultBox", transGroupId));
if (idrr != null)
{
while (idrr.Read())
{
officialTotal = Convert.ToDecimal(idrr[0]);
}
}
idrr = dbComm.SelectCMD(string.Format("select IsNull(sum(transAmount), 0) from (select distinct(transAmount) from fin_Transactions where transGroupID={0} and not transCrAccount=16 and transSystemIndex=1) as resultBox", transGroupId));
if (idrr != null)
{
while (idrr.Read())
{
unofficialTotal = Convert.ToDecimal(idrr[0]);
unofficialTotal += officialTotal;
}
}
idrr = dbComm.SelectCMD(string.Format("select * from fin_Transactions where transGroupID={0} and transCrAccount=16", transGroupId));
if (idrr != null)
{
while (idrr.Read())
{
int transID = Convert.ToInt32(idrr["transID"]);
objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans = bTrans.Select(transID);
if (objTrans != null)
{
objTrans.transSystemIndex = 1;
objTrans.transAmount = unofficialTotal;
bTrans.Update(objTrans);
}
objTrans = bTrans.Select(transID);
if (objTrans != null)
{
iBiz.FinPro.Transactions.Transact.objTransaction objTranss = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans.transSystemIndex = 2;
objTrans.transAmount = officialTotal;
objTrans.transSystemRestrict = true;
objTranss.transParticipantID = objTrans.transParticipantID;
objTranss.transCreatedOn = objTrans.transCreatedOn;
bTrans.Add(objTrans);
if (objTranss.transParticipantID.Value == 8)
{
objTranss.transDrAccount = 84;
}
else if (objTranss.transParticipantID.Value == 13)
{
objTranss.transDrAccount = 94;
}
else
{
objTranss.transDrAccount = null;
}
objTranss.transAmount = officialTotal;
objTranss.transCrAccount = null;
objTranss.transCreatedBy = 1;
objTranss.transCreatedOn = DateTime.Now;
objTranss.transGroupID = transGroupId;
objTranss.transNarration = "Inventory Sold";
objTranss.transStatus = 1;
objTranss.transSystemIndex = 2;
objTranss.transSystemRestrict = true;
objTranss.transType = 44;
objTranss.transUpdatedBy = 1;
objTranss.transUpdatedOn = DateTime.Now;
bTrans.Add(objTranss);
}
}
}
objTransG = new iBiz.FinPro.Transactions.Groups.objGroup();
objTransG = bTransG.Select(transGroupId);
if (objTransG != null)
{
objTransG.transGroupTotalAmount = unofficialTotal;
objTransG.transGroupOfficialTotalAmount = officialTotal;
bTransG.Update(objTransG);
}
}
}
}
protected void Import_Sales()
{
iDB.Communicate dbComm = new iDB.Communicate();
iBiz.FinPro.Transactions.Groups bTransG = new iBiz.FinPro.Transactions.Groups();
iBiz.FinPro.Transactions.Groups.objGroup objTransG = new iBiz.FinPro.Transactions.Groups.objGroup();
iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact();
iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
IDataReader idr = dbComm.SelectCMD("Select transGroupID, Sum(transAmount) as transAmount, max(transNarration) as transNarration, max(transCreatedOn) as transCreatedOn, max(transParticipantID) as transParticipantID from fin_Transactions where (transParticipantID=8 or transParticipantID=13) group by transGroupID");
if (idr != null)
{
while (idr.Read())
{
int transGroupId = Convert.ToInt32(idr["transGroupId"]);
string branch = "";
if (idr["transParticipantID"].ToString().Trim() == "8")
{
branch = "DHA - ";
}
else
{
branch = "Gujrat - ";
}
objTransG.transGroupCreatedBy = 1;
objTransG.transGroupCreatedOn = Convert.ToDateTime(idr["transCreatedOn"]);
objTransG.transGroupForeNumber = bTransG.New_Group_Fore_Number(4, "SAL", DateTime.Now);
objTransG.transGroupID = transGroupId;
objTransG.transGroupPrefixNo = 4;
objTransG.transGroupPrefixString = "SAL";
objTransG.transGroupStatus = 1;
objTransG.transGroupTitle = branch + "Sales Made on " + objTransG.transGroupCreatedOn.ToString("MMM dd, yyyy");
objTransG.transGroupTotalAmount = Convert.ToDecimal(idr["transAmount"]);
objTransG.transTransCount = 0;
bTransG.Add(objTransG);
}
}
idr = null;
idr = dbComm.SelectCMD("SELECT * FROM [fin_Transactions] where (transParticipantID=8 or transParticipantID=13) and Not (transDrAccount is Null or transCrAccount is Null)");
if (idr != null)
{
while (idr.Read())
{
int transId = Convert.ToInt32(idr["transId"]);
iBiz.FinPro.Transactions.Transact.objTransaction objTempTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans = bTrans.Select(transId);
objTempTrans = bTrans.Select(transId);
objTrans.transCrAccount = null;
objTrans.transType = 44;
objTempTrans.transDrAccount = null;
objTempTrans.transType = 44;
bTrans.Update(objTrans);
bTrans.Add(objTempTrans);
}
}
idr = null;
idr = dbComm.SelectCMD("SELECT * FROM [fin_Transactions] where (transParticipantID=8 or transParticipantID=13)");
if (idr != null)
{
while (idr.Read())
{
int transId = Convert.ToInt32(idr["transId"]);
iBiz.FinPro.Transactions.Transact.objTransaction objTempTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans = bTrans.Select(transId);
objTrans.transType = 44;
bTrans.Update(objTrans);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
iBiz.FinPro.Transactions.Transact bTrans = new iBiz.FinPro.Transactions.Transact();
iBiz.FinPro.Transactions.Transact.objTransaction objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
iBiz.FinPro.Transactions.Transact.objTransaction objDrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
iBiz.FinPro.Transactions.Transact.objTransaction objCrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
iBiz.FinPro.Transactions.Groups bTGroup = new iBiz.FinPro.Transactions.Groups();
iBiz.FinPro.Transactions.Groups.objGroup objTGroup = new iBiz.FinPro.Transactions.Groups.objGroup();
iDB.Communicate dbComm = new iDB.Communicate();
IDataReader idr = dbComm.SelectCMD("SELECT * FROM [fin_Transactions] where transDrAccount=294 and transGroupId not in (select transGroupId from fin_TransGroups where transGroupPrefixString='AUT')");
if (idr != null)
{
while (idr.Read())
{
int transId = Convert.ToInt32(idr["transId"]);
objTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objTrans = bTrans.Select(transId);
if (objTrans != null)
{
int transGroupId = 0;
objTGroup = new iBiz.FinPro.Transactions.Groups.objGroup();
objTGroup.transGroupTitle = "Credit Card Department Bank UBL DHA";
objTGroup.transGroupCreatedOn = objTrans.Get_Group().transGroupCreatedOn;
objTGroup.transGroupCreatedBy = 1;
objTGroup.transGroupTotalAmount = objTrans.transAmount;
objTGroup.transTransCount = 2;
objTGroup.transGroupPrefixNo = 7;
objTGroup.transGroupPrefixString = "AUT";
objTGroup.transGroupForeNumber = objTrans.transGroupID;
objTGroup.transGroupStatus = 1;
objTGroup.transLinkedToGroup = objTrans.transGroupID;
objTGroup.transGroupIsOfficial = 0;
transGroupId = bTGroup.Add(objTGroup).Value;
objDrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objDrTrans.deptId = objTrans.deptId;
objDrTrans.transAmount = objTrans.transAmount;
objDrTrans.transAttachedFiles = "";
objDrTrans.transCrAccount = null;
objDrTrans.transCreatedBy = objTrans.transCreatedBy;
objDrTrans.transCreatedOn = objTrans.Get_Group().transGroupCreatedOn;
objDrTrans.transDrAccount = 301;
objDrTrans.transGroupID = transGroupId;
objDrTrans.transInvoiceID = 0;
objDrTrans.transIsCompound = true;
objDrTrans.transNarration = "Credit Card Sales UBL DHA";
objDrTrans.transParticipantID = 1;
objDrTrans.transRefID = "0";
objDrTrans.transStatus = 1;
objDrTrans.transSystemIndex = 2;
objDrTrans.transSystemRestrict = false;
objDrTrans.transType = 3;
objDrTrans.transUpdatedBy = 1;
objDrTrans.transUpdatedOn = objTrans.Get_Group().transGroupCreatedOn;
bTrans.Add(objDrTrans);
objCrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objCrTrans.deptId = objTrans.deptId;
objCrTrans.transAmount = objTrans.transAmount;
objCrTrans.transAttachedFiles = "";
objCrTrans.transCrAccount = 294;
objCrTrans.transCreatedBy = objTrans.transCreatedBy;
objCrTrans.transCreatedOn = objTrans.Get_Group().transGroupCreatedOn;
objCrTrans.transDrAccount = null;
objCrTrans.transGroupID = transGroupId;
objCrTrans.transInvoiceID = 0;
objCrTrans.transIsCompound = true;
objCrTrans.transNarration = "Credit Card Sales UBL DHA";
objCrTrans.transParticipantID = 1;
objCrTrans.transRefID = "0";
objCrTrans.transStatus = 1;
objCrTrans.transSystemIndex = 2;
objCrTrans.transSystemRestrict = false;
objCrTrans.transType = 3;
objCrTrans.transUpdatedBy = 1;
objCrTrans.transUpdatedOn = objTrans.Get_Group().transGroupCreatedOn;
bTrans.Add(objCrTrans);
}
}
}
idr = dbComm.SelectCMD("SELECT * FROM [fin_Transactions] where transDrAccount=296 and transGroupId not in (select transGroupId from fin_TransGroups where transGroupPrefixString='AUT')");
if (idr != null)
{
while (idr.Read())
{
int transId = Convert.ToInt32(idr["transId"]);
objTrans = bTrans.Select(transId);
if (objTrans != null)
{
int transGroupId = 0;
objTGroup = new iBiz.FinPro.Transactions.Groups.objGroup();
objTGroup.transGroupTitle = "Credit Card Department Bank UBL Gujrat";
objTGroup.transGroupCreatedOn = objTrans.Get_Group().transGroupCreatedOn;
objTGroup.transGroupCreatedBy = 1;
objTGroup.transGroupTotalAmount = objTrans.transAmount;
objTGroup.transTransCount = 2;
objTGroup.transGroupPrefixNo = 7;
objTGroup.transGroupPrefixString = "AUT";
objTGroup.transGroupForeNumber = objTrans.transGroupID;
objTGroup.transGroupStatus = 1;
objTGroup.transLinkedToGroup = objTrans.transGroupID;
objTGroup.transGroupIsOfficial = 0;
transGroupId = bTGroup.Add(objTGroup).Value;
objDrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objDrTrans.deptId = objTrans.deptId;
objDrTrans.transAmount = objTrans.transAmount;
objDrTrans.transAttachedFiles = "";
objDrTrans.transCrAccount = null;
objDrTrans.transCreatedBy = objTrans.transCreatedBy;
objDrTrans.transCreatedOn = objTrans.Get_Group().transGroupCreatedOn;
objDrTrans.transDrAccount = 303;
objDrTrans.transGroupID = transGroupId;
objDrTrans.transInvoiceID = 0;
objDrTrans.transIsCompound = true;
objDrTrans.transNarration = "Credit Card Sales UBL Gujrat";
objDrTrans.transParticipantID = 1;
objDrTrans.transRefID = "0";
objDrTrans.transStatus = 1;
objDrTrans.transSystemIndex = 2;
objDrTrans.transSystemRestrict = false;
objDrTrans.transType = 3;
objDrTrans.transUpdatedBy = 1;
objDrTrans.transUpdatedOn = objTrans.Get_Group().transGroupCreatedOn;
bTrans.Add(objDrTrans);
objCrTrans = new iBiz.FinPro.Transactions.Transact.objTransaction();
objCrTrans.deptId = objTrans.deptId;
objCrTrans.transAmount = objTrans.transAmount;
objCrTrans.transAttachedFiles = "";
objCrTrans.transCrAccount = 296;
objCrTrans.transCreatedBy = objTrans.transCreatedBy;
objCrTrans.transCreatedOn = objTrans.Get_Group().transGroupCreatedOn;
objCrTrans.transDrAccount = null;
objCrTrans.transGroupID = transGroupId;
objCrTrans.transInvoiceID = 0;
objCrTrans.transIsCompound = true;
objCrTrans.transNarration = "Credit Card Sales UBL DHA";
objCrTrans.transParticipantID = 1;
objCrTrans.transRefID = "0";
objCrTrans.transStatus = 1;
objCrTrans.transSystemIndex = 2;
objCrTrans.transSystemRestrict = false;
objCrTrans.transType = 3;
objCrTrans.transUpdatedBy = 1;
objCrTrans.transUpdatedOn = objTrans.Get_Group().transGroupCreatedOn;
bTrans.Add(objCrTrans);
}
}
}
}
