public List <ConsumerDataModel> GetConsumerInvoiceDetails(string tc)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
InvoiceDetails = new List <ConsumerDataModel>();
sqlCom = new SqlCommand(@"SELECT consumer_bill_id,consumer_bill_creationdate,
consumer_bill_duedate,consumer_bill_amount,consumer_bill_ispaid
FROM [PaymentSystem_DB].[dbo].[consumer_bill] CB
LEFT JOIN [PaymentSystem_DB].[dbo].[consumers] C
ON CB.consumer_bill_consumer_id=C.consumer_id
where C.consumer_tc_id='" + tc + "' AND C.consumer_isactive=1", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
InvoiceDetails.Add(new ConsumerDataModel()
{
bill_id = sqlDr.GetInt32(0),
creationdate = sqlDr.GetDateTime(1),
duedate = sqlDr.GetDateTime(2),
billamount = sqlDr.GetValue(3).ToString(),
ispaid = sqlDr.GetBoolean(4)
});
}
}
return(InvoiceDetails);
}
public List <ConsumerDataModel> GetConsumerBillRawData()
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
ConsumerBillRawData = new List <ConsumerDataModel>();
sqlCom = new SqlCommand(@"SELECT consumer_bill_id,consumer_bill_creationdate,
consumer_bill_duedate,consumer_bill_amount,consumer_bill_ispaid,consumer_tc_id
FROM [PaymentSystem_DB].[dbo].[consumer_bill] CB
LEFT JOIN [PaymentSystem_DB].[dbo].[consumers] C
ON CB.consumer_bill_consumer_id=C.consumer_id WHERE consumer_isactive=1", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
ConsumerBillRawData.Add(new ConsumerDataModel()
{
bill_id = sqlDr.GetInt32(0),
creationdate = sqlDr.GetDateTime(1),
duedate = sqlDr.GetDateTime(2),
billamount = sqlDr.GetValue(3).ToString(),
ispaid = sqlDr.GetBoolean(4),
tc_id = sqlDr.GetString(5)
});
}
}
return(ConsumerBillRawData);
}
public List <EnterpriseDataModel> GetEnterpriseBillRawData()
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
EnterpriseBillRawData = new List <EnterpriseDataModel>();
sqlCom = new SqlCommand(@"SELECT enterprise_bill_id,enterprise_bill_createdate,enterprise_bill_duedate,
enterprise_amount,enterprise_ispaid,enterprise_tax_number
FROM [PaymentSystem_DB].[dbo].[enterprise_bill] EB
LEFT JOIN [PaymentSystem_DB].[dbo].[enterprises] E
ON EB.enterprise_bill_enterprise_id=E.enterprise_id WHERE E.enterprise_isactive=1", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
EnterpriseBillRawData.Add(new EnterpriseDataModel()
{
bill_id = sqlDr.GetInt32(0),
creationdate = sqlDr.GetDateTime(1),
duedate = sqlDr.GetDateTime(2),
billamount = sqlDr.GetValue(3).ToString(),
ispaid = sqlDr.GetBoolean(4),
tax_num = sqlDr.GetString(5)
});
}
}
return(EnterpriseBillRawData);
}
public List <EnterpriseDataModel> GetEnterpriseInvoiceDetails(string tax)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
InvoiceDetails = new List <EnterpriseDataModel>();
sqlCom = new SqlCommand(@"SELECT enterprise_bill_id,enterprise_bill_createdate,enterprise_bill_duedate,enterprise_amount,enterprise_ispaid
FROM [PaymentSystem_DB].[dbo].[enterprise_bill] EB
LEFT JOIN [PaymentSystem_DB].[dbo].[enterprises] E
ON EB.enterprise_bill_enterprise_id=E.enterprise_id
WHERE E.enterprise_tax_number='" + tax + "' AND E.enterprise_isactive=1", sqlConn);
sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
InvoiceDetails.Add(new EnterpriseDataModel()
{
bill_id = sqlDr.GetInt32(0),
creationdate = sqlDr.GetDateTime(1),
duedate = sqlDr.GetDateTime(2),
billamount = sqlDr.GetValue(3).ToString(),
ispaid = sqlDr.GetBoolean(4)
});
}
}
return(InvoiceDetails);
}
public List <ConsumerDataModel> GetConsumerDetailsData(string tc)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
ConsumerDetails = new List <ConsumerDataModel>();
sqlCom = new SqlCommand(@"SELECT consumer_id,consumer_username,consumer_name,consumer_surname,consumer_tc_id,consumer_phone,consumer_email FROM [PaymentSystem_DB].[dbo].[consumers] WHERE consumer_tc_id='" + tc + "' AND consumer_isactive=1", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
ConsumerDetails.Add(new ConsumerDataModel()
{
consumer_id = sqlDr.GetInt32(0),
username = sqlDr.GetString(1),
name = sqlDr.GetString(2),
surname = sqlDr.GetString(3),
tc_id = sqlDr.GetString(4),
phone = sqlDr.GetString(5),
email = sqlDr.GetString(6)
});
}
}
return(ConsumerDetails);
}
public void GetEnterprisePayInvoice(int id)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@"UPDATE [PaymentSystem_DB].[dbo].[enterprise_bill] SET enterprise_ispaid=1 WHERE enterprise_bill_id='" + id + "'", sqlConn);
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public void GetConsumerPayInvoice(int id)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@"UPDATE [PaymentSystem_DB].[dbo].[consumer_bill] SET consumer_bill_ispaid=1 WHERE consumer_bill_id='" + id + "'", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public void GetEnterprisePayDeposit(string tax)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@"UPDATE [PaymentSystem_DB].[dbo].[enterprise_deposit] SET [enterprise_deposit_ispaid]=1 WHERE [enterprise_deposit_enterprise_tax]='" + tax + "'", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public void GetConsumerPayDeposit(string tc)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@"UPDATE [PaymentSystem_DB].[dbo].[consumer_deposit] SET consumer_deposit_ispaid=1 WHERE consumer_deposit_consumer_tc='" + tc + "'", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public void GetConsumerAccountClose(string tc)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@"UPDATE [PaymentSystem_DB].[dbo].[consumers] SET consumer_isactive=0 WHERE consumer_tc_id='" + tc + "'", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public void GetEnterpriseAccountClose(string tax)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@"UPDATE [PaymentSystem_DB].[dbo].[enterprises] SET enterprise_isactive=0 WHERE enterprise_tax_number='" + tax + "'", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public void AddNewEnterpriseInDeposit(string tax)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@"INSERT INTO [PaymentSystem_DB].[dbo].[enterprise_deposit](enterprise_deposit_amount,enterprise_deposit_ispaid,enterprise_deposit_enterprise_tax,enterprise_deposit_paid_date)
VALUES('600','0','" + tax + "',null)", sqlConn);
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public void AddNewConsumerInDeposit(string tc)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@" INSERT INTO [PaymentSystem_DB].[dbo].[consumer_deposit](consumer_deposit_amount,consumer_deposit_ispaid,consumer_deposit_consumer_tc,consumer_deposit_paid_date)
VALUES('400','0','" + tc + "',null)", sqlConn);
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public void AddNewBillforConsumers(DateTime creation_date, DateTime due_date, string amount, bool ispaid, int cons_id)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@"INSERT INTO [dbo].[consumer_bill] ([consumer_bill_creationdate] ,[consumer_bill_duedate] ,[consumer_bill_amount] ,[consumer_bill_ispaid] ,[consumer_bill_consumer_id])
VALUES('" + creation_date + "','" + due_date + "','" + amount + "','" + ispaid + "','" + cons_id + "')", sqlConn);
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public void AddNewEnterprise(string username2, string entname, string tax, string entphone, string entemail, string entpass, bool entisactive)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@"INSERT INTO [PaymentSystem_DB].[dbo].[enterprises]
(enterprise_username,enterprise_name,enterprise_tax_number,enterprise_phone,enterprise_email,enterprise_pass,enterprise_isactive)
VALUES('" + username2 + "','" + entname + "','" + tax + "','" + entphone + "','" + entemail + "','" + entpass + "','" + entisactive + "')", sqlConn);
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public void AddNewConsumer(string username1, string conname, string surname, string tc, string conphone, string conemail, string conpass, bool conisactive)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
sqlCom = new SqlCommand(@"INSERT INTO [PaymentSystem_DB].[dbo].[consumers](consumer_username,consumer_name,
consumer_surname,consumer_tc_id,consumer_phone,consumer_email,consumer_pass,consumer_isactive)
VALUES('" + username1 + "', '" + conname + "', '" + surname + "', '" + tc + "', '" + conphone + "', '" + conemail + "', '" + conpass + "', '" + conisactive + "')", sqlConn);
sqlCom.ExecuteReader();
SqlConnection.ClearPool(sqlConn);
sqlConn.Close();
}
}
public List <AdminDataModel> GetAdminData()
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
AdminData = new List <AdminDataModel>();
sqlCom = new SqlCommand(@"select * from [PaymentSystem_DB].[dbo].[admins]", sqlConn);
sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
AdminData.Add(new AdminDataModel()
{
admin_id = sqlDr.GetInt32(0),
admin_name = sqlDr.GetString(1),
admin_surname = sqlDr.GetString(2),
admin_user = sqlDr.GetString(3),
admin_pass = sqlDr.GetString(4)
});
}
}
return(AdminData);
}
public List <ConsumerDataModel> GetConsumerData()
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
ConsumerData = new List <ConsumerDataModel>();
sqlCom = new SqlCommand(@"SELECT [consumer_username],[consumer_tc_id],[consumer_pass],[consumer_isactive] FROM [PaymentSystem_DB].[dbo].[consumers]", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
ConsumerData.Add(new ConsumerDataModel()
{
username = sqlDr.GetString(0),
tc_id = sqlDr.GetValue(1).ToString(),
pass = sqlDr.GetValue(2).ToString(),
isactive = sqlDr.GetBoolean(3)
});
}
}
return(ConsumerData);
}
public List <EnterpriseDataModel> GetEnterpriseData()
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
EnterpriseData = new List <EnterpriseDataModel>();
sqlCom = new SqlCommand(@"SELECT [enterprise_username],[enterprise_tax_number], [enterprise_pass],[enterprise_isactive] FROM [PaymentSystem_DB].[dbo].[enterprises]", sqlConn);
sqlCom.CommandTimeout = 60 * 5;
sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
EnterpriseData.Add(new EnterpriseDataModel()
{
username = sqlDr.GetString(0),
tax_num = sqlDr.GetValue(1).ToString(),
pass = sqlDr.GetValue(2).ToString(),
isactive = sqlDr.GetBoolean(3)
});
}
}
return(EnterpriseData);
}
public List <EnterpriseDataModel> GetEnterpriseDetailsData(string tax)
{
using (sqlConn = PaymentDBStaticModel.ConnectionInfo())
{
sqlConn.Open();
EnterpriseDetails = new List <EnterpriseDataModel>();
sqlCom = new SqlCommand(@"SELECT enterprise_id,enterprise_username,enterprise_name,enterprise_tax_number,enterprise_phone,enterprise_email FROM [PaymentSystem_DB].[dbo].[enterprises] WHERE enterprise_tax_number='" + tax + "' AND enterprise_isactive=1", sqlConn);
sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
EnterpriseDetails.Add(new EnterpriseDataModel()
{
enterprise_id = sqlDr.GetInt32(0),
username = sqlDr.GetString(1),
name = sqlDr.GetString(2),
tax_num = sqlDr.GetString(3),
phone = sqlDr.GetString(4),
email = sqlDr.GetString(5)
});
}
}
return(EnterpriseDetails);
}